REM     ORACLE - Licence Management Services
REM              Oracle Review Lite Script
REM
REM     Usage:
REM        - Copy usage_based_v23.sql query into the working folder (make sure SQL*PLUS run from there)
REM
REM        - Use SQL*PLUS to connect to the database with APPS user
REM          Example:
REM               sqlplus apps/password@orcl1prod
REM
REM        - Run the script
REM               @usage_based_FY12Q1.sql
REM          
REM
REM     Change History
REM     Date:     Rev:    Author:           Description:
REM     MM/DD/YY
REM     04/05/10  2.2     sbaruta           Created
REM     10/15/10  2.3     sbaruta           Created
REM     10/19/10  2.3     esiytiu           Reviewed
REM     08/31/11  2.4     lpopa             Enhancement to OEEL, HR_E, HR_P, HR_Types, Removed iSupplier Portal
REM
REM
REM     This script checks usage based products of E-Business Suite

set pagesize 5000
set linesize 500

set lines 500
set pages 50000
set serveroutput on size 1000000

show user

set time off
set timi off

set termout on
set heading off
set feedback off
set verify off

col ts new_value ts
SELECT to_char(sysdate,'yyyy-mm-dd__hh-mm-ss') as ts FROM dual;

spool lst_usage_based_&ts..txt

prompt ==============Usage Based Query 2.4 =================================
prompt ======================Disclaimer=======================================

prompt *** The Usage Based Query is a standard tool and is not customized
prompt to only have certain scripts for a specific review. Therefore, only  
prompt script results that are relevant will be considered.  
prompt
prompt *** Some version specific scripts will generate an error when ran in 
prompt a different version of E-Business Suite. This does not affect the
prompt output and should be ignored.

prompt ==================================================================== 


COL EBS_version format a15
COL HOST_NAME     format a40 wrap
COL Instance_name format a15

prompt  
prompt
prompt ==============
prompt ***E-Business Suite Version

col C0 new_val EBS_version
SELECT release_name C0 FROM apps.fnd_product_groups;

prompt  
prompt
prompt ==============
prompt ***Instance name and Host name

-- Get host_name and instance_name
col C1 new_val INSTANCE_NAME
col C2 new_val HOST_NAME
-- Oracle7
SELECT name    C1 FROM v$database;
SELECT min(machine) C2 FROM v$session WHERE type = 'BACKGROUND';
-- Oracle8 and higher
SELECT instance_name C1, host_name C2 FROM v$instance;

col C3 new_val GREP_PREFIX noprint
--SELECT 'GREP'||'ME>>,&&HOST_NAME.,&&INSTANCE_NAME.,'||name as C3 FROM v$database;
SELECT 'GREP'||'ME>>' as C3 FROM dual;

prompt  
prompt
prompt ==============
prompt ***Database Version

select BANNER FROM V$VERSION;


REM ****    Oracle Corporation    ****
REM **** License Management Services ****
REM
REM   Script Name: Advanced Pricing
REM
REM   Script Creation Date: 	01/02/2008
REM
REM   Revised By:     Date:	Description
REM
REM   DESCRIPTION: This script shows the status of Advanced Pricing installation    
REM

prompt  
prompt  
prompt ==============
prompt ***Advanced Pricing installation status

define USAGE_QUERY=ADVANCED_PRICING

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	qp_util.get_qp_status  			||','
FROM 
	dual;


set time on
set timi on


REM ****    Oracle Corporation    ****
REM **** License Management Services ****
REM
REM   Script Name: Incentive Compensation
REM
REM   Script Creation Date: 	12/01/2009
REM
REM   Revised By:     Date:	Description
REM
REM   DESCRIPTION: This script shows the number of Compensated Individuals, 
REM		   according with COMPENSATED INDIVIDUAL metric.
REM

prompt  
prompt  
prompt ==============
prompt ***Total number of Compensated Individuals, measured for Incentive Compensation product

define USAGE_QUERY=INCENTIVE_COMPENSATION

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	COUNT(DISTINCT salesrep_id)		||','
FROM 
	cn_srp_plan_assigns_all
WHERE 
	end_date is null or end_date > sysdate;



REM ****    Oracle Corporation    ****
REM **** License Management Services ****
REM
REM   Script Name: iEXPENSES
REM
REM   Script Creation Date: 	18/05/2007
REM
REM   Revised By:     Date:	Description
REM
REM   DESCRIPTION: This script counts the total number of expense 
REM   reports processed by Internet Expense for each year.   
REM   date.  The default date format is DD-MON-YY.    
REM
REM   TABLE DESCRIPTION: AP_INVOICES_ALL stores current information 
REM   about all processed invoices.
REM			

prompt  
prompt  
prompt ==============
prompt ***Total number of Expense Reports processed by Internet Expenses for each month (last 24 months of usage)

define USAGE_QUERY=EXPENSE_REPORTS

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	to_char(creation_date,'YYYY-MM')	||','||
	COUNT(invoice_id)			||','
FROM 
	ap_invoices_all
WHERE 
	invoice_type_lookup_code='EXPENSE REPORT'
	AND source='SelfService'	
	AND (select max(creation_date) from ap_invoices_all 
			where invoice_type_lookup_code='EXPENSE REPORT'
			AND source='SelfService') <= 730 + creation_date
GROUP BY to_char(creation_date,'YYYY-MM')
ORDER BY to_char(creation_date,'YYYY-MM');



REM ***************************** License Management Services ************************************
REM
REM   Script Name: iPROCUREMENT
REM
REM   Script Creation Date: 	18/05/2007
REM
REM   DESCRIPTION: This script counts the total number of purchase line items processed by
REM   iProcurement, for each year.     
REM
REM   TABLE DESCRIPTION: 
REM   PO_REQUISITION_LINES_ALL stores current information about each purchase order line item.
REM   You need one row for each line.
REM
REM   PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row 
REM   for each requisition header you create.

prompt  
prompt  
prompt ==============
prompt ***Total number of purchase line items processed by iProcurement, for each month (last 24 months of usage)

define USAGE_QUERY=IPROCUREMENT

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	to_char(b.creation_date,'YYYY-MM')	||','||
	COUNT(b.line_num)			||',' 
FROM 
	po_requisition_headers_all a,  
	po_requisition_lines_all b
WHERE 
	a.requisition_header_id=b.requisition_header_id
	AND apps_source_code='POR' and a.authorization_status='APPROVED'
	AND (select max(creation_date) from po_requisition_lines_all
			where apps_source_code='POR' and authorization_status='APPROVED') <= 730 + b.creation_date
GROUP BY to_char(b.creation_date,'YYYY-MM')
ORDER BY to_char(b.creation_date,'YYYY-MM');



REM ***************************** License Management Services ************************************
REM
REM Script Name: OE
REM
REM Script Creation Date: 18/05/2007
REM
REM   DESCRIPTION: This script counts the total number of order entry line items processed by
REM   the ordering application, for each year.     
REM
REM   TABLE DESCRIPTION: OE_ORDER_LINES_ALL stores current information about each order entry line item.
REM   You need one row for each line. 

prompt  
prompt  
prompt ==============
prompt ***Total number of order entry line items processed by the ordering application, for each month (last 24 months of usage), measured for version 10.7

define USAGE_QUERY=OE_107

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	to_char(creation_date,'YYYY-MM')	||','|| 
	COUNT(line_id)				||',' 
FROM
	oe.so_lines_all
WHERE
	(select max(creation_date) from oe.so_lines_all) <= 730 + creation_date
GROUP BY to_char(creation_date,'YYYY-MM')
ORDER BY to_char(creation_date,'YYYY-MM');

prompt  
prompt  
prompt ==============
prompt ***Total number of order entry line items processed by the ordering application, for each month (last 24 months of usage), measured for version >= 11i

define USAGE_QUERY=OE_11i

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	to_char(creation_date,'YYYY-MM')	||','|| 
	COUNT(line_id)				||',' 
FROM
	oe_order_lines_all
WHERE
	(select max(creation_date) from oe_order_lines_all) <= 730 + creation_date
GROUP BY to_char(creation_date,'YYYY-MM')
ORDER BY to_char(creation_date,'YYYY-MM');



REM ***************************** License Management Services ************************************
REM
REM Script Name: OE_ELECTRONIC_LINES
REM
REM Script Creation Date: 18/05/2007
REM
REM   DESCRIPTION: This script counts the total number of order lines processed by 
REM   the ordering application, for each month, per order source. Only order sources used for 
REM   electronic orders input will be further considered.     
REM 
REM   TABLE DESCRIPTION:   OE_ORDER_LINES_ALL stores header information for orders in Order Entry/Shipping. 
REM                       

prompt  
prompt  
prompt ==============
prompt ***Total number of Order Lines processed by the ordering application, for each month (last 24 months of usage) per source, measured for version 10.7
prompt ***only part of this output with sources that are deemed to be electronic will be considered
 
define USAGE_QUERY=OEEL_107
 
SELECT
   	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
   	to_char(creation_date,'YYYY-MM')	||','||
 	Original_system_line_reference		||','||
 	COUNT(line_id)				||','
FROM
 	oe.so_lines_all
WHERE	
	(select max(creation_date) from oe.so_lines_all) <= 730 + creation_date
GROUP BY to_char(creation_date,'YYYY-MM'),Original_system_line_reference
ORDER BY to_char(creation_date,'YYYY-MM'),Original_system_line_reference; 

prompt  
prompt  
prompt ==============
prompt ***Total number of Order Lines processed by the ordering application, for each month (last 24 months of usage) per source, measured for version >= 11i
prompt ***only part of this output with sources that are deemed to be electronic will be considered

define USAGE_QUERY=OEEL_11i


SELECT 
  	'&&GREP_PREFIX.,&USAGE_QUERY.,'     ||
  	to_char(s.creation_date,'YYYY-MM')  ||','||
  	os.order_source_id      	    ||','||   
  	os.description                      ||','||
  	COUNT(s.line_id)                    ||','
FROM 
  	ont.oe_order_lines_all s, 
  	ont.oe_order_sources os 
WHERE 
  	s.order_source_id=os.order_source_id(+) 
  	AND (select max(creation_date) from ont.oe_order_lines_all) <= 730 + s.creation_date
GROUP BY to_char(s.creation_date,'YYYY-MM'), os.order_source_id, os.description
ORDER BY os.description, to_char(s.creation_date,'YYYY-MM');




REM ***************************** License Management Services ************************************
REM
REM Script Name: OE_ELECTRONIC_ORDERS
REM
REM Script Creation Date: 18/05/2007
REM
REM   DESCRIPTION: This script counts the total number of orders processed electronically by 
REM   the ordering application, for each year. The default date format is DD-MON-YY.  This  
REM   script is used for version 11i of Oracle Applications.     
REM 
REM   TABLE DESCRIPTION:   SO_HEADERS_ALL stores header information for orders in Order Entry/Shipping. 
REM                       
 
prompt  
prompt  
prompt ==============
prompt ***Total number of orders processed electronically by the ordering application, for each month (last 24 months of usage), measured for version 10.7
prompt ***only part of this output will be further considered (order sources used for electronic orders)
 
define USAGE_QUERY=OEEO_107
 
SELECT
   	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
   	to_char(s.creation_date,'YYYY-MM')	||','||
 	l.meaning				||','||
 	COUNT(s.header_id)			||','
FROM
 	oe.so_headers_all s, oe.so_lookups l
WHERE
	l.lookup_type = 'ORDER_CATEGORY'
	AND s.order_category = l.lookup_code
	AND (select max(creation_date) from oe.so_headers_all) <= 730 + s.creation_date
GROUP BY to_char(s.creation_date,'YYYY-MM'),l.meaning
ORDER BY to_char(s.creation_date,'YYYY-MM'),l.meaning;

prompt  
prompt  
prompt ==============
prompt ***Total number of orders processed electronically by the ordering application, for each month (last 24 months of usage), measured for version >= 11i
prompt ***only part of this output will be further considered (order sources used for electronic orders)
 
define USAGE_QUERY=OEEO_11i
 
SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	to_char(s.creation_date,'YYYY-MM')	||','||
	os.description				||','||
	COUNT(s.header_id)			||','
FROM 
      ont.oe_order_headers_all s, 
      ont.oe_order_sources os 
WHERE 
      s.order_source_id=os.order_source_id(+) 
      AND (select max(creation_date) from ont.oe_order_headers_all) <= 730 + s.creation_date
GROUP BY to_char(s.creation_date,'YYYY-MM'), os.description
ORDER BY os.description, to_char(s.creation_date,'YYYY-MM');



REM ***************************** License Management Services ************************************
REM
REM Script Name: iRECEIVABLES
REM
REM Script Creation Date: 18/05/2007
REM
REM   DESCRIPTION:  This script counts the total number of invoice line items processed by
REM   the iReceivables application, for each year.
REM   This query counts invoice lines accessed by iReceivables users in each business unit.
REM   The total number of invoice lines accessed by iReceivables can be obtained by
REM   adding up the invoice lines from the individual business units.
REM   
REM
REM   TABLE DESCRIPTION: RA_CUSTOMER_TRX_LINES_ALL stores current information about each AR entry line item.
REM   You need one row for each line. This is a child table to RA_CUSTOMER_TRX_ALL table.
REM
REM   RA_CUSTOMER_TRX_ALL is a parent table to the RA_CUSTOMER_TRX_LINES_ALL table.  A join is
REM   performed to increase the efficiency of the query.  The RA_CUSTOMER_TRX_LINES_ALL table is
REM   not an indexed table.

prompt  
prompt  
prompt ==============
prompt ***Total number of invoice line items processed by the iReceivables application, for each month (last 24 months of usage)

define USAGE_QUERY=IRECEIVABLES

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	to_char(trx.creation_date,'YYYY-MM')	||','||
	fnr.responsibility_id			||','||
	fnr.responsibility_name			||','||
	mpu.organization_id			||','||
	mpu.name				||','||
	count(trx_l.customer_trx_line_id)	||','
FROM
	fnd_responsibility_vl fnr,
	fnd_profile_option_values fpo,
	hr_operating_units mpu,
	fnd_profile_options pro,
	fnd_menus_vl mnu,
	ra_customer_trx_all trx,
	ra_customer_trx_lines_all trx_l
WHERE
	mnu.menu_id = fnr.menu_id
AND     mnu.menu_name LIKE '%ARI_MENU_%TERNAL%'
AND     fpo.profile_option_id = pro.profile_option_id
AND     pro.profile_option_name = 'ORG_ID'
AND     fpo.level_id = '10003'
AND     to_char(fpo.profile_option_value) = to_char(mpu.organization_id)
AND     fpo.level_value(+) = fnr.responsibility_id
AND     trx.org_id = mpu.organization_id
AND     trx.customer_trx_id=trx_l.customer_trx_id
AND	(select max(creation_date) from ra_customer_trx_all) <= 730 + trx.creation_date
GROUP BY
	to_char(trx.creation_date,'YYYY-MM'),
	fnr.responsibility_id,
	fnr.responsibility_name,
	mpu.name,
	mpu.organization_id
ORDER BY   
	mpu.organization_id, to_char(trx.creation_date,'YYYY-MM');



REM ***************************** License Management Services ************************************
REM
REM   Script Name: PURCHINT
REM 
REM   DESCRIPTION:  		This script counts the total number of purchase line items processed by
REM                 		the Purchasing Intelligence application.  
REM 
REM   METRIC DEFINITION:      Purchase Line: is defined as the total number of purchase line items processed by 
REM 					the application during a 12 month period. Multiple purchase lines may be created 
REM 					on either a requisition or purchase order or may be automatically generated by 
REM 					other Oracle Application program.
REM					For Purchasing Intelligence, Purchase Lines are counted as the line items on 
REM 					purchase orders processed through this application. This does not include 
REM 					communication on the same P.O. 

prompt  
prompt  
prompt ==============
prompt ***Total number of purchase line items processed by the Purchasing Intelligence application, during last 12 months, measured for version >= 11i

define USAGE_QUERY=PURCHINT_11i

SELECT
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	count(distinct INSTANCE_FK_KEY||po_line_id) 
FROM  
	poa_edw_po_dist_f
WHERE  
	(select max(creation_date) from poa_edw_po_dist_f) <= 365 + creation_date;



REM ***************************** License Management Services ************************************
REM
REM Script Name: OTL
REM
REM Script Creation Date: 24/09/2010
REM
REM DESCRIPTION: This script counts the total number of persons recorded by the Oracle Time & Labor system. 
REM Persons include only active employees and contractors registered in HR.
REM
REM Person metric definition: 
REM For Time and Labor, a person is defined as an employee or contractor 
REM whose time or labor (piece work) or absences are managed by the system
REM 

prompt 
prompt 
prompt ==============
prompt ***Total number of active employees and contractors, recorded by the OTL system, measured for version > 11.5

define USAGE_QUERY=OTL

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,' 	||
	'OTL active employees and contractors: '||
	(SELECT COUNT(DISTINCT paf.party_id)
	FROM hxc_timecard_summary ts, hxc_time_building_blocks tb,
	--apply where condition to have resource_id within active employees and contractors records set
	(SELECT DISTINCT party_id, person_id
	   FROM per_all_people_f
	   WHERE
	     (upper(current_employee_flag)='Y' OR upper(current_npw_flag)='Y')
	     AND effective_start_date < sysdate 
	     AND sysdate < effective_end_date
	) paf
	WHERE ts.resource_id = tb.resource_id
	AND tb.scope = 'TIMECARD'
	AND ts.resource_id = paf.person_id) 	||',' 
FROM dual;



REM ***************************** License Management Services ************************************
REM
REM Script Name: OTA
REM
REM Script Creation Date: 18/05/2007
REM
REM   DESCRIPTION: This script counts the total number of trainees recorded by the system.  
REM   Trainees include internal trainees (active employees only) and external trainees.
REM   This script counts also the cancelled enrollments.
REM   If one person is doing a training and in the same time is sponsor (signs for other persons), 
REM   that person will be counted twice.
REM                   
REM   TABLE DESCRIPTION:   
REM   OTA_DELEGATE_BOOKINGS - A student booking (delegate booking) records an enrollment 
REM   onto a schedule, program or one time event. 
REM   OTA_BOOKINGS_STATUS_TYPES - Contains a list of user-defined enrollment statuses, 
REM   along with their corresponding system statuses
REM                       

prompt  
prompt  
prompt ==============
prompt ***Total number of trainees, both internal and external, recorded by the system

define USAGE_QUERY=OTA

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	'Internal learners: '||
	(SELECT COUNT(DISTINCT trainee) trainee
     	FROM
     	(SELECT    DISTINCT NVL(b.delegate_person_id, b.sponsor_person_id) trainee
           FROM      ota.ota_delegate_bookings b
           WHERE     (b.delegate_person_id IS NOT NULL OR b.sponsor_person_id IS NOT NULL)
           --apply where condition to have delegate_person_id within active employees records set
	   AND b.delegate_person_id in 
	   (SELECT distinct person_id employee
	       FROM per_all_people_f
	       WHERE
	          upper(current_employee_flag)='Y'
	          and effective_start_date < sysdate and sysdate < effective_end_date)
    	))					||','||
	', External learners: '||
	(SELECT COUNT(DISTINCT trainee) trainee
     	FROM
     	(SELECT    DISTINCT NVL(b.delegate_contact_id, b.contact_id) trainee
           FROM      ota.ota_delegate_bookings b
           WHERE     (b.delegate_contact_id IS NOT NULL OR b.contact_id IS NOT NULL)
	)) 					||',' 
FROM dual;



REM *****************    Oracle Corporation    *******************
REM ***************** License Management Services *****************
REM
REM   Script Name:    HR Employee
REM   Creation Date: 	11/12/2009
REM
REM   Version  Revised By          Date(MM/DD/YYYY)  Description
REM   1.0      Prakash Pulla,      11/12/2009        Original Script 
REM            Tusar Abedin        
REM   
REM   
REM   DESCRIPTION: 
REM     This script counts the number of licenses needed for the Employee 
REM     metric. The first section selects all 
REM     employees, while the seconds counts all contractors. Counting distinct 
REM     Party_ID eliminates double counting the same person across different 
REM     Business group.
REM
REM   EMPLOYEE metric:
REM     is defined as all of your full-time, part-time, temporary employees 
REM     and all of your agents, contractors and consultants. The quantity of the 
REM     licenses required is determined by the number of Employees and not the actual 
REM     number of users. In addition, if you elect to outsource any business function(s) 
REM     to another company, all of the company's full-time, part-time,temporary 
REM     employees and agents, contractors and consultants that are providing the 
REM     outsourcing services for you must be counted for the purposes of determining 
REM     the number of Employees.

prompt  
prompt  
prompt ==============
prompt ***Total number of employees, measured for version 10.7

define USAGE_QUERY=HR_E_107

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	COUNT(DISTINCT person_id)		||','
FROM 
	PER_ALL_PEOPLE_F P
WHERE 
	P.EFFECTIVE_START_DATE < SYSDATE AND SYSDATE < P.EFFECTIVE_END_DATE
   	AND current_employee_flag = 'Y';

prompt  
prompt  
prompt ==============
prompt ***Total number of employees, measured for version 11.0 and 11i(< 11.5.7)


define USAGE_QUERY=HR_E_11.0_and_11i(<11.5.7)

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	COUNT(*)				||','
FROM 
	(SELECT DISTINCT party_id
  	FROM PER_ALL_PEOPLE_F P
 	WHERE P.EFFECTIVE_START_DATE < SYSDATE AND SYSDATE < P.EFFECTIVE_END_DATE
   		AND current_employee_flag = 'Y'
	UNION
	SELECT distinct p.party_id
	FROM per_all_people_f p,
  		per_contracts_f c,
  		per_person_types ppt
	WHERE c.effective_start_date < sysdate AND sysdate < c.effective_end_date
 		AND p.effective_start_date < sysdate AND sysdate < p.effective_end_date
 		AND c.person_id = p.person_id
 		AND p.person_type_id = ppt.person_type_id
 		AND p.business_group_id = ppt.business_group_id
 		AND ppt.system_person_type = 'OTHER');

prompt  
prompt  
prompt ==============
prompt ***Total number of employees, measured for version >= 11.5.7 including R12

define USAGE_QUERY=HR_E_>=11.5.7_including_R12

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	COUNT(DISTINCT party_id)		||','
FROM 
	PER_ALL_PEOPLE_F P
WHERE 
 	P.EFFECTIVE_START_DATE < SYSDATE AND SYSDATE < P.EFFECTIVE_END_DATE
   	AND (current_npw_flag ='Y' OR current_employee_flag = 'Y');



REM *****************    Oracle Corporation    *******************
REM ***************** License Management Services *****************
REM
REM   Script Name:    HR Ex_Emp_WithBenefits
REM   Creation Date: 	11/12/2009
REM
REM   Version  Revised By          Date(MM/DD/YYYY)  Description
REM   2.0      Prakash Pulla,      02/12/2010        Revised Script 
REM            Tusar Abedin        
REM   
REM   
REM   DESCRIPTION: 
REM     HR_Person consists of two scripts, the first one counts the number of 
REM     employees (and contractors), while the second (below here) counts all
REM     ex_employees with active benefits. 
REM

prompt  
prompt  
prompt ==============
prompt ***Total number of ex-employees with active benefits in the system, measured for version 11.0, 11i and 12

define USAGE_QUERY=HR_P_11.0_11i_and_12

SELECT 
	'&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	COUNT(DISTINCT PARTY_ID)		||','
FROM 
	per_all_people_f p
WHERE 
	p.employee_number IS NOT NULL
	AND p.effective_start_date < sysdate AND  sysdate < p.effective_end_date
	AND p.current_employee_flag IS NULL
	AND EXISTS
  	(SELECT NULL
     	FROM ben_prtt_enrt_rslt_f ben
    	WHERE ben.effective_start_date < sysdate AND sysdate < ben.effective_end_date
  		---- check for live coverage
    		AND ((ben.enrt_cvg_strt_dt <= sysdate) AND (sysdate <= ben.enrt_cvg_thru_dt))
  		AND ben.prtt_enrt_rslt_stat_cd IS NULL
  		AND ben.ENRT_CVG_THRU_DT       >= ben.ENRT_CVG_STRT_DT
  		AND ben.person_id               = p.person_id
  		AND ben.business_group_id       = p.business_group_id
  		AND ben.enrt_cvg_strt_dt       <= ben.effective_end_date
  		AND ben.enrt_cvg_thru_dt       >= ben.effective_start_date); 



REM *****************    Oracle Corporation    *******************
REM ***************** License Management Services *****************
REM
REM   Script Name:    HR User Types
REM   Creation Date: 	11/12/2009
REM
REM   Version  Revised By          Date(MM/DD/YYYY)  Description
REM   1.0      Prakash Pulla,      11/12/2009        Original Script 
REM            Tusar Abedin,
REM            Sorin Baruta
REM   
REM   
REM   DESCRIPTION: 
REM     This script displays the different system person types and user person 
REM     types in the customer's environment and the number of employees under 
REM     each type.
REM

prompt  
prompt  
prompt ==============
prompt ***Total number of persons per each system_person_type and user_person_type, measured for version 11i(11.5.x) to 12

define USAGE_QUERY=HR_TYPES_11i(11.5.x)_to_12

SELECT '&&GREP_PREFIX.,&USAGE_QUERY.,'		||
	spt					||','||
	upt					||','||
	COUNT(DISTINCT party_id)		||','
FROM 
	(SELECT party_id, 
		TRIM(HR_PERSON_TYPE_USAGE_INFO.GET_USER_PERSON_TYPE(sysdate,paf.person_id)) upt,
       		TRIM(HR_PERSON_TYPE_USAGE_INFO.GETSYSTEMPERSONTYPE(paf.person_type_id)) spt
      	FROM per_all_people_f paf
      	WHERE effective_start_date < sysdate AND sysdate < effective_end_date)
GROUP BY 
	spt,upt
ORDER BY 
	spt,upt;



prompt ==============
	
set verify on
spool off

exit



