[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
The function can be called in three modes. BEFOREREPORT, UPDATE and AFTERREPORT
BEFOREREPORT - Takes care of Initialization part. Called from
PER_PERUSEO1_XMLP_PKG.BEFOREREPORT
UPDATE - Updates the data in PL/SQL table with the Establishment Employee Counts
Called from Q_1 Query of XML Data Template PERUSEO1 (Delivered in PERRPEO1_XML.xml)
AFTERREPORT - Saves the data in PL/SQL table to Location Extra Information*/
FUNCTION MAINTAIN_EST_EMP_COUNTS (C_MODE IN VARCHAR2,
C_EST_NODE_ID IN NUMBER,
C_TOT_CAT IN NUMBER,
C_TOT_WMALE IN NUMBER,
C_TOT_BMALE IN NUMBER,
C_TOT_HMALE IN NUMBER,
C_TOT_AMALE IN NUMBER,
C_TOT_IMALE IN NUMBER,
C_TOT_WFEMALE IN NUMBER,
C_TOT_BFEMALE IN NUMBER,
C_TOT_HFEMALE IN NUMBER,
C_TOT_AFEMALE IN NUMBER,
C_TOT_IFEMALE IN NUMBER,
C_TOT_HLMALE IN NUMBER,
C_TOT_HLFEMALE IN NUMBER,
C_TOT_TMRACESMALE IN NUMBER,
C_TOT_TMRACESFEMALE IN NUMBER)
RETURN NUMBER
IS
L_EST_NODE_ID NUMBER;
G_EST_COUNT_TABLE.DELETE;
IF C_MODE = 'UPDATE' THEN
IF G_EST_COUNT_TABLE.EXISTS(C_EST_NODE_ID) THEN
/*Add the latest counts to existing counts in PL/SQL table for the Establishment*/
G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_CAT := G_EST_COUNT_TABLE(C_EST_NODE_ID).TOT_CAT + C_TOT_CAT;
G_EST_COUNT_TABLE.DELETE;
SELECT
PGH.NAME,
PGV.VERSION_NUMBER,
PGN.ENTITY_ID,
PGN.HIERARCHY_NODE_ID
INTO C_HIERARCHY_NAME,C_HIERARCHY_VERSION_NUM,C_PARENT_ORG_ID,C_PARENT_NODE_ID
FROM
PER_GEN_HIERARCHY PGH,
PER_GEN_HIERARCHY_VERSIONS PGV,
PER_GEN_HIERARCHY_NODES PGN
WHERE PGH.HIERARCHY_ID = P_HIERARCHY_ID
AND PGH.HIERARCHY_ID = PGV.HIERARCHY_ID
AND PGV.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGN.HIERARCHY_VERSION_ID = PGV.HIERARCHY_VERSION_ID
AND PGN.NODE_TYPE = 'PAR';
SELECT
COUNT('h_node')
INTO C_NO_OF_ESTABLISHMENTS
FROM
PER_GEN_HIERARCHY_NODES PGHN
WHERE PGHN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGHN.NODE_TYPE = 'EST';
SELECT
null
INTO L_DUMMY
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
AND LOCATION_ID is not null;
SELECT
null
INTO L_DUMMY
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
AND ORG_INFORMATION_CONTEXT = 'EEO_Spec';
SELECT
null
INTO L_DUMMY
FROM
HR_LOCATION_EXTRA_INFO HLEI1,
HR_LOCATION_EXTRA_INFO HLEI2,
PER_GEN_HIERARCHY_NODES PGN,
HR_LOCATIONS_ALL ELOC
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGN.NODE_TYPE = 'EST'
AND ELOC.LOCATION_ID = PGN.ENTITY_ID
AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
AND HLEI2.LOCATION_ID = ELOC.LOCATION_ID
AND HLEI2.INFORMATION_TYPE = 'Establishment Information'
AND HLEI2.LEI_INFORMATION_CATEGORY = 'Establishment Information';
SELECT
ELOC.LOCATION_ID,
ELOC.LOCATION_CODE
INTO L_LOCATION_ID,L_LOCATION_CODE
FROM
HR_LOCATION_EXTRA_INFO HLEI1,
PER_GEN_HIERARCHY_NODES PGN,
HR_LOCATIONS_ALL ELOC
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGN.NODE_TYPE = 'EST'
AND ELOC.LOCATION_ID = PGN.ENTITY_ID
AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
AND HLEI1.INFORMATION_TYPE = 'EEO-1 Archive Information'
AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';
SELECT
'1',
ELOC.LOCATION_ID,
ELOC.LOCATION_CODE
INTO L_DUMMY,L_LOCATION_ID,L_LOCATION_CODE
FROM
HR_LOCATION_EXTRA_INFO HLEI1,
PER_GEN_HIERARCHY_NODES PGN,
HR_LOCATIONS_ALL ELOC
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGN.NODE_TYPE = 'EST'
AND ELOC.LOCATION_ID = PGN.ENTITY_ID
AND HLEI1.LOCATION_ID = ELOC.LOCATION_ID
AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
AND HLEI1.LEI_INFORMATION9 = 'Y';
SELECT
2
INTO L_DUMMY
FROM
HR_LOCATION_EXTRA_INFO
WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information';
SELECT
ORG_INFORMATION1,
ORG_INFORMATION2,
ORG_INFORMATION3,
ORG_INFORMATION4,
ORG_INFORMATION5,
ORG_INFORMATION6,
ORG_INFORMATION8,
ORG_INFORMATION9,
ORG_INFORMATION11,
ORG_INFORMATION12
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
AND ORG_INFORMATION_CONTEXT = 'VETS_EEO_Dup';
SELECT COUNT(PEO.PERSON_ID)
INTO L_COUNT_EMPS
FROM PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F ASS,
--PER_JOBS_VL JOB - Replaced PER_JOBS_VL with PER_JOBS - bug#11736960
PER_JOBS JOB
WHERE PEO.PERSON_ID = ASS.PERSON_ID
--
/* Commented for bug#11736960 starts
AND PEO.PER_INFORMATION1 is not NULL
Commented for bug#11736960 ends */
--
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO, P_PAYROLL_PERIOD_DATE_END_T)
AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
AND JOB.JOB_INFORMATION1 is not NULL
AND ASS.JOB_ID = JOB.JOB_ID
AND PEO.EFFECTIVE_START_DATE = (
SELECT
MAX(PEO1.EFFECTIVE_START_DATE)
FROM
--PER_PEOPLE_F PEO1 - Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F -bug#11736960
PER_ALL_PEOPLE_F PEO1
WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
AND PEO.PERSON_ID = PEO1.PERSON_ID
AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
AND ASS.EFFECTIVE_START_DATE = (
SELECT
MAX(ASS1.EFFECTIVE_START_DATE)
FROM
PER_ALL_ASSIGNMENTS_F ASS1,
PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
AND ASS.PERSON_ID = ASS1.PERSON_ID
AND ASS1.ASSIGNMENT_TYPE = 'E'
AND ASS1.PRIMARY_FLAG = 'Y'
AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
)
AND EXISTS (
SELECT
'X'
FROM HR_ORGANIZATION_INFORMATION HOI2
where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
)
/* Replaced this exists clasue with two seperate exists condition inorder to
avoid Merge Join Cartesian.
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
*/
AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
AND EXISTS (
SELECT 1
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ( PGN.HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID )
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
AND ASS.LOCATION_ID=PGN.ENTITY_ID
)
/* Replaced this in clause with the above exists clause.
AND ASS.LOCATION_ID in (
SELECT
DISTINCT
PGN.ENTITY_ID
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ( PGN.HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = C_TOT_EMPSFORMULA.EST_NODE_ID )
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
*/
-- Added for bug#11736960
AND EXISTS (SELECT 'X'
FROM PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PEO.PERSON_ID
AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
AND (PEI.PEI_INFORMATION1 = 'Y'
OR PEI.PEI_INFORMATION2 = 'Y'
OR PEI.PEI_INFORMATION3 = 'Y'
OR PEI.PEI_INFORMATION4 = 'Y'
OR PEI.PEI_INFORMATION5 = 'Y'
OR PEI.PEI_INFORMATION6 = 'Y'
OR PEI.PEI_INFORMATION7 = 'Y'
)
);
SELECT
NVL(HLEI.LEI_INFORMATION5
,C_DEF_ACTIV_1),
NVL(HLEI.LEI_INFORMATION6
,C_DEF_ACTIV_2),
NVL(HLEI.LEI_INFORMATION7
,C_DEF_ACTIV_3),
NVL(HLEI.LEI_INFORMATION8
,C_DEF_ACTIV_4)
FROM
HR_LOCATION_EXTRA_INFO HLEI,
PER_GEN_HIERARCHY_NODES PGHN
WHERE HLEI.INFORMATION_TYPE = 'EEO-1 Specific Information'
AND HLEI.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
AND HLEI.LOCATION_ID = PGHN.ENTITY_ID
AND PGHN.PARENT_HIERARCHY_NODE_ID = C_PARENT_NODE_ID
AND PGHN.HIERARCHY_NODE_ID = EST_NODE_ID
AND PGHN.NODE_TYPE = 'EST';
SELECT
COUNT('person'),
COUNT(DECODE(PEO.SEX
,'M'
,1
,NULL)),
COUNT(DECODE(PEO.SEX
,'F'
,1
,NULL))
INTO CP_HW_CAT,CP_HW_MALE,CP_HW_FEMALE
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F ASS,
--PER_JOBS_VL JOB - Replaced PER_JOBS_VL with PER_JOBS - bug#11736960
PER_JOBS JOB
WHERE PEO.PERSON_ID = ASS.PERSON_ID
--
/* Commented for bug#11736960 starts
AND PEO.PER_INFORMATION1 is not NULL
Commented for bug#11736960 ends */
--
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
,P_PAYROLL_PERIOD_DATE_END_T)
AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PEO.EFFECTIVE_START_DATE = (
SELECT
MAX(PEO1.EFFECTIVE_START_DATE)
FROM
PER_ALL_PEOPLE_F PEO1
WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
AND PEO.PERSON_ID = PEO1.PERSON_ID
AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
AND ASS.EFFECTIVE_START_DATE = (
SELECT
MAX(ASS1.EFFECTIVE_START_DATE)
FROM
PER_ALL_ASSIGNMENTS_F ASS1,
PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
AND ASS.PERSON_ID = ASS1.PERSON_ID
AND ASS1.ASSIGNMENT_TYPE = 'E'
AND ASS1.PRIMARY_FLAG = 'Y'
AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.PRIMARY_FLAG = 'Y'
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
)
AND EXISTS (
SELECT
'X'
FROM HR_ORGANIZATION_INFORMATION HOI2
where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
)
/* Replaced this exists clasue with two seperate exists condition inorder to
avoid Merge Join Cartesian.
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
*/
AND EXISTS (
SELECT 1
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
AND ASS.LOCATION_ID = PGN.ENTITY_ID
)
/* Replaced this in clause with the above exists clause.
AND ASS.LOCATION_ID in (
SELECT
DISTINCT
PGN.ENTITY_ID
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
AND PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
*/
-- Added for bug#11736960
AND EXISTS (SELECT 'X'
FROM PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PEO.PERSON_ID
AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
AND (PEI.PEI_INFORMATION1 = 'Y'
OR PEI.PEI_INFORMATION2 = 'Y'
OR PEI.PEI_INFORMATION3 = 'Y'
OR PEI.PEI_INFORMATION4 = 'Y'
OR PEI.PEI_INFORMATION5 = 'Y'
OR PEI.PEI_INFORMATION6 = 'Y'
OR PEI.PEI_INFORMATION7 = 'Y'
)
);
SELECT
LEI_INFORMATION3 P_TOTAL,
TO_NUMBER((LEI_INFORMATION4 + LEI_INFORMATION5 + LEI_INFORMATION6 + LEI_INFORMATION7 + LEI_INFORMATION8 + LEI_INFORMATION14 + LEI_INFORMATION16)) TOTALMALE,
TO_NUMBER((LEI_INFORMATION9 + LEI_INFORMATION10 + LEI_INFORMATION11 + LEI_INFORMATION12 + LEI_INFORMATION13 + LEI_INFORMATION15 + LEI_INFORMATION17)) TOTALFEM
INTO HW_AUD_TOT,HW_AUD_MALE,HW_AUD_FEMALE
FROM
HR_LOCATION_EXTRA_INFO LEI
WHERE LEI_INFORMATION1 = CP_PREV_YEAR_FILED
AND TO_CHAR(LEI.LOCATION_ID) in (
SELECT
DISTINCT
PGN.ENTITY_ID
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) );
SELECT
COUNT('X')
INTO L_COUNT_EMPS
FROM
PER_ALL_ASSIGNMENTS_F ASS,
PER_ALL_PEOPLE_F PEO,
--PER_JOBS_VL JOB - Replaced PER_JOBS_VL wth PER_JOBS - bug#11736960
PER_JOBS JOB
WHERE PEO.PERSON_ID = ASS.PERSON_ID
--
/* Commented for bug#11736960 starts
AND PEO.PER_INFORMATION1 is not NULL
Commented for bug#11736960 ends */
--
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
,P_PAYROLL_PERIOD_DATE_END_T)
AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
AND JOB.JOB_INFORMATION1 is not null
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
--
-- Added the check for businees_group_id and person effective start date
-- to eliminate count of duplicate record - bug#11736960
AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PEO.EFFECTIVE_START_DATE =
(SELECT MAX(PEO1.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PEO1
WHERE P_PAYROLL_PERIOD_DATE_START_T
<= PEO1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T
>= PEO1.EFFECTIVE_START_DATE
AND PEO.PERSON_ID = PEO1.PERSON_ID
AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y'
)
--
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.EFFECTIVE_START_DATE = (
SELECT
MAX(ASS1.EFFECTIVE_START_DATE)
FROM
PER_ALL_ASSIGNMENTS_F ASS1,
PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
AND ASS.PERSON_ID = ASS1.PERSON_ID
AND ASS1.ASSIGNMENT_TYPE = 'E'
AND ASS1.PRIMARY_FLAG = 'Y'
AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
)
AND EXISTS (
SELECT
'X'
FROM HR_ORGANIZATION_INFORMATION HOI2
where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
)
/* Replaced this exists clasue with two seperate exists condition inorder to
avoid Merge Join Cartesian.
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
*/
/* Commented as not requires - bug#11736960
AND P_PAYROLL_PERIOD_DATE_START_T <= ASS.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= ASS.EFFECTIVE_START_DATE
*/
AND EXISTS (
SELECT 1
FROM
PER_GEN_HIERARCHY_NODES
WHERE HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ASS.LOCATION_ID = ENTITY_ID)
/* Replaced in clause with exists clause - bug#11736960
AND ASS.LOCATION_ID in (
SELECT
DISTINCT
ENTITY_ID
FROM
PER_GEN_HIERARCHY_NODES
WHERE HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID )
*/
-- Added for bug#11736960
AND EXISTS (SELECT 'X'
FROM PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PEO.PERSON_ID
AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
AND (PEI.PEI_INFORMATION1 = 'Y'
OR PEI.PEI_INFORMATION2 = 'Y'
OR PEI.PEI_INFORMATION3 = 'Y'
OR PEI.PEI_INFORMATION4 = 'Y'
OR PEI.PEI_INFORMATION5 = 'Y'
OR PEI.PEI_INFORMATION6 = 'Y'
OR PEI.PEI_INFORMATION7 = 'Y'
)
);
P_UPDATE VARCHAR2(1) := 'C';
SELECT
ELOC.LOCATION_ID,
ELOC.LOCATION_CODE
INTO L_LOCATION_ID,L_LOCATION_CODE
FROM
PER_GEN_HIERARCHY_NODES PGN,
HR_LOCATIONS_ALL ELOC
WHERE ( HIERARCHY_NODE_ID = EST_NODE_ID
OR PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND PGN.NODE_TYPE = 'EST'
AND ELOC.LOCATION_ID = PGN.ENTITY_ID;
SELECT
'U',
LOCATION_EXTRA_INFO_ID
INTO P_UPDATE,L_LOCATION_EXTRA_INFO_ID
FROM
HR_LOCATION_EXTRA_INFO
WHERE LEI_INFORMATION1 = P_REPORT_YEAR
AND LEI_INFORMATION_CATEGORY = 'EEO-1 Archive Information'
AND LOCATION_ID = L_LOCATION_ID;
P_UPDATE := 'C';
IF P_UPDATE = 'U' THEN
/*SRW.MESSAGE('10'
,'p_update ' || P_UPDATE || ' location_id to update is ' || L_LOCATION_ID || ' ' || L_LOCATION_CODE)*/NULL;
,' p_location_extra_info_id to delete for update is ' || L_LOCATION_EXTRA_INFO_ID)*/NULL;
SELECT
OBJECT_VERSION_NUMBER
INTO L_OBJECT_VERSION_NUMBER
FROM
HR_LOCATION_EXTRA_INFO
WHERE LOCATION_EXTRA_INFO_ID = L_LOCATION_EXTRA_INFO_ID;
,'object version number to delete for update is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
P_UPDATE := 'C';
IF P_UPDATE = 'C' THEN
/*SRW.MESSAGE('21'
,'p_update ' || P_UPDATE || ' location_id ' || L_LOCATION_ID)*/NULL;
SELECT
count(*)
INTO L_EIT_COUNT
FROM
HR_LOCATION_EXTRA_INFO LEI
WHERE LOCATION_ID = L_LOCATION_ID
AND INFORMATION_TYPE = 'EEO-1 Archive Information';
SELECT
MIN(LEI_INFORMATION1)
INTO L_MIN_YEAR
FROM
HR_LOCATION_EXTRA_INFO LEI
WHERE LOCATION_ID = L_LOCATION_ID
AND INFORMATION_TYPE = 'EEO-1 Archive Information';
SELECT
LOCATION_EXTRA_INFO_ID,
OBJECT_VERSION_NUMBER
INTO L_LOCATION_EXTRA_INFO_ID,L_OBJECT_VERSION_NUMBER
FROM
HR_LOCATION_EXTRA_INFO LEI
WHERE LEI_INFORMATION1 = L_MIN_YEAR
AND INFORMATION_TYPE = 'EEO-1 Archive Information'
AND LOCATION_ID = L_LOCATION_ID;
HR_LOCATION_EXTRA_INFO_API.DELETE_LOCATION_EXTRA_INFO(P_VALIDATE => FALSE
,P_LOCATION_EXTRA_INFO_ID => L_LOCATION_EXTRA_INFO_ID
,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
SELECT
PEO.PERSON_ID,
PEO.FIRST_NAME,
PEO.LAST_NAME,
PEO.SEX,
-- PEO.PER_INFORMATION1 ETHNIC, /* Commented for bug#11736960 */
PEO.EMPLOYEE_NUMBER,
ASS.ASSIGNMENT_ID,
ASS.LOCATION_ID,
HL.LOCATION_CODE,
JOB.NAME JOB_NAME,
ASS.JOB_ID
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F ASS,
--PER_JOBS_VL JOB, - Replaced PER_JOBS_VL with PER_JOBS -bug#11736960
PER_JOBS JOB,
HR_LOCATIONS_ALL HL
WHERE PEO.PERSON_ID = ASS.PERSON_ID
--
/* Commented for bug#11736960 starts
AND PEO.PER_INFORMATION1 is not null
Commented for bug#11736960 ends */
--
AND PEO.PER_INFORMATION_CATEGORY = 'US'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND P_PAYROLL_PERIOD_DATE_START_T <= NVL(JOB.DATE_TO
,P_PAYROLL_PERIOD_DATE_END_T)
AND P_PAYROLL_PERIOD_DATE_END_T >= JOB.DATE_FROM
AND JOB.JOB_INFORMATION1 = CONS_JOB_CATEGORY_CODE
AND ASS.JOB_ID = JOB.JOB_ID
AND PEO.EFFECTIVE_START_DATE = (
SELECT
MAX(PEO1.EFFECTIVE_START_DATE)
FROM
--PER_PEOPLE_F PEO1 - Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F
PER_ALL_PEOPLE_F PEO1
WHERE P_PAYROLL_PERIOD_DATE_START_T <= PEO1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= PEO1.EFFECTIVE_START_DATE
AND PEO.PERSON_ID = PEO1.PERSON_ID
AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
AND ASS.EFFECTIVE_START_DATE = (
SELECT
MAX(ASS1.EFFECTIVE_START_DATE)
FROM
--PER_ASSIGNMENTS_F ASS1 - Replaced PER_ASSIGNMENTS_F with PER_ALL_ASSIGNMENTS_F
PER_ALL_ASSIGNMENTS_F ASS1,
PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
WHERE P_PAYROLL_PERIOD_DATE_START_T <= ASS1.EFFECTIVE_END_DATE
AND P_PAYROLL_PERIOD_DATE_END_T >= ASS1.EFFECTIVE_START_DATE
AND ASS.PERSON_ID = ASS1.PERSON_ID
AND ASS1.ASSIGNMENT_TYPE = 'E'
AND ASS1.PRIMARY_FLAG = 'Y'
AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
)
AND EXISTS (
SELECT
'X'
FROM HR_ORGANIZATION_INFORMATION HOI2
where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
)
/* Replaced this exists clasue with two seperate exists condition inorder to
avoid Merge Join Cartesian.
AND EXISTS (
SELECT
'X'
FROM
HR_ORGANIZATION_INFORMATION HOI1,
HR_ORGANIZATION_INFORMATION HOI2
WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
*/
AND HL.LOCATION_ID = ASS.LOCATION_ID
AND EXISTS (
SELECT 1
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
AND ASS.LOCATION_ID = PGN.ENTITY_ID
)
/* Replaced this in clause with the above exists clause.
AND ASS.LOCATION_ID in (
SELECT
DISTINCT
PGN.ENTITY_ID
FROM
PER_GEN_HIERARCHY_NODES PGN
WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
*/
-- Added for bug#11736960
AND EXISTS (SELECT 'X'
FROM PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PEO.PERSON_ID
AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
AND (PEI.PEI_INFORMATION1 = 'Y'
OR PEI.PEI_INFORMATION2 = 'Y'
OR PEI.PEI_INFORMATION3 = 'Y'
OR PEI.PEI_INFORMATION4 = 'Y'
OR PEI.PEI_INFORMATION5 = 'Y'
OR PEI.PEI_INFORMATION6 = 'Y'
OR PEI.PEI_INFORMATION7 = 'Y'
)
)
ORDER BY
LAST_NAME;