[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or '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(PGHN.HIERARCHY_NODE_ID)
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';
P_UPDATE VARCHAR2(1) := 'C';
SELECT
'U',
ORG_INFORMATION_ID
INTO P_UPDATE,L_ORG_INFORMATION_ID
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION1 = P_REPORT_YEAR
AND ORGANIZATION_ID = C_PARENT_ORG_ID -- Added for bug#12575094
AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
P_UPDATE := 'C';
IF P_UPDATE = 'U' THEN
/*SRW.MESSAGE('21'
,'p_update ' || P_UPDATE || ' org_id to update is ' || C_PARENT_ORG_ID)*/NULL;
,' org_information_id to delete for update is ' || L_ORG_INFORMATION_ID)*/NULL;
SELECT
OBJECT_VERSION_NUMBER
INTO L_OBJECT_VERSION_NUMBER
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_ID = L_ORG_INFORMATION_ID;
,'object version number to delete for update is ' || L_OBJECT_VERSION_NUMBER)*/NULL;
HR_ORGANIZATION_API.DELETE_ORG_MANAGER(P_VALIDATE => FALSE
,P_ORG_INFORMATION_ID => L_ORG_INFORMATION_ID
,P_OBJECT_VERSION_NUMBER => L_OBJECT_VERSION_NUMBER);
P_UPDATE := 'C';
IF P_UPDATE = 'C' THEN
/*SRW.MESSAGE('30'
,' ')*/NULL;
,'p_update ' || P_UPDATE || ' org id ' || C_PARENT_ORG_ID)*/NULL;
SELECT
count(*)
INTO L_EIT_COUNT
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
SELECT
MIN(ORG_INFORMATION1)
INTO L_MIN_YEAR
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
AND ORG_INFORMATION_CONTEXT = 'EEO_Archive';
SELECT
ORG_INFORMATION_ID,
OBJECT_VERSION_NUMBER
INTO L_ORG_INFORMATION_ID,L_OBJECT_VERSION_NUMBER
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION1 = L_MIN_YEAR
AND ORG_INFORMATION_CONTEXT = 'EEO_Archive'
AND ORGANIZATION_ID = C_PARENT_ORG_ID;
HR_ORGANIZATION_API.DELETE_ORG_MANAGER(P_VALIDATE => FALSE
,P_ORG_INFORMATION_ID => L_ORG_INFORMATION_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,
LOC.LOCATION_CODE,
JOB.NAME JOB_NAME,
ASS.JOB_ID
FROM
PER_ALL_PEOPLE_F PEO,
PER_ASSIGNMENTS_F ASS,
--PER_JOBS_VL JOB, - Replaced PER_JOBS_VL with PER_JOBS -bug#11736960
PER_JOBS JOB,
HR_LOCATIONS_ALL LOC
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,
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 ASS.LOCATION_ID = LOC.LOCATION_ID
AND ASS.LOCATION_ID in (
SELECT
ENTITY_ID
FROM
PER_GEN_HIERARCHY_NODES PGHN,
HR_LOCATION_EXTRA_INFO HLEI1,
HR_LOCATION_EXTRA_INFO HLEI2
WHERE PGHN.NODE_TYPE <> 'PAR'
AND HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
AND TO_CHAR(HLEI1.LOCATION_ID) = PGHN.ENTITY_ID
AND TO_CHAR(HLEI2.LOCATION_ID) = PGHN.ENTITY_ID
AND HLEI1.LOCATION_ID = HLEI2.LOCATION_ID
AND HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information'
AND HLEI2.INFORMATION_TYPE = 'Establishment Information'
AND HLEI2.LEI_INFORMATION_CATEGORY = 'Establishment Information' )
-- 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'
)
);