PER Terminations

Description
Categories: BI Publisher
Application: Human Resources
Source: Terminations Report (XML)
Short Name: PERRPRTM_XML
DB package: PER_PERRPRTM_XMLP_PKG
SELECT A.ORGANIZATION_ID ORGANIZATION_ID1,
	count(distinct
		decode(:P_LEAV_REASON1,null,null, &P_REASON_COLUMN,
		P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,NULL)) reason1_tot,
	count(distinct
		decode(:P_LEAV_REASON2,null,null,&P_REASON_COLUMN,
		P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,NULL)) reason2_tot,
	count(distinct
		decode(:P_LEAV_REASON3,null,null, &P_REASON_COLUMN,
		P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,NULL)) reason3_tot,
	count(distinct
		decode(:P_LEAV_REASON4,null,null,&P_REASON_COLUMN,
		P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,NULL)) reason4_tot,
	count(distinct
		decode(:P_LEAV_REASON5,null,null,&P_REASON_COLUMN,
		P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,NULL)) reason5_tot,
	count(distinct
		decode(:P_LEAV_REASON6,null,null,&P_REASON_COLUMN,
		P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,NULL)) reason6_tot,
           count(distinct
                   decode(&P_REASON_COLUMN,
                        decode(:P_LEAV_REASON1,null,'999',:P_LEAV_REASON1),
                                        P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,
                        decode(:P_LEAV_REASON2,null,'999',:P_LEAV_REASON2),
                                        P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,
                        decode(:P_LEAV_REASON3,null,'999',:P_LEAV_REASON3),
                                        P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,
                        decode(:P_LEAV_REASON4,null,'999',:P_LEAV_REASON4),
                                        P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,
                        decode(:P_LEAV_REASON5,null,'999',:P_LEAV_REASON5),
                                        P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,
                        decode(:P_LEAV_REASON5,null,'999',:P_LEAV_REASON5),
                                        P.PERSON_ID||P.ACTUAL_TERMINATION_DATE,
                                              null)) reasons_tot,
            count(distinct P.PERSON_ID||P.ACTUAL_TERMINATION_DATE) all_total
FROM   PER_PERIODS_OF_SERVICE P,
	PER_ASSIGNMENTS_F A
WHERE  P.PERIOD_OF_SERVICE_ID = A.PERIOD_OF_SERVICE_ID
	AND  A.ASSIGNMENT_TYPE = 'E'
	AND  P.ACTUAL_TERMINATION_DATE BETWEEN
		A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
	AND  P.ACTUAL_TERMINATION_DATE BETWEEN
		:P_DATE_FROM AND :P_DATE_TO
 and A.ORGANIZATION_ID=:org_id
group by A.ORGANIZATION_ID
Parameter Name SQL text Validation
Effective Date
 
Date
Organization Structure
 
LOV Oracle
Version
 
LOV Oracle
Parent Organization
 
LOV Oracle
Date From
 
Date
Date To
 
Date
Termination Reason 1
 
LOV Oracle
Termination Reason 2
 
LOV Oracle
Termination Reason 3
 
LOV Oracle
Termination Reason 4
 
LOV Oracle
Termination Reason 5
 
LOV Oracle
Termination Reason 6
 
LOV Oracle
Business Group Id
 
Number