The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
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
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';
SELECT
NVL(HLEI1.LEI_INFORMATION5
,DEFAULT_ACTIVITIES_LINE1),
NVL(HLEI1.LEI_INFORMATION6
,DEFAULT_ACTIVITIES_LINE2),
NVL(HLEI1.LEI_INFORMATION7
,DEFAULT_ACTIVITIES_LINE3),
NVL(HLEI1.LEI_INFORMATION8
,DEFAULT_ACTIVITIES_LINE4)
FROM
HR_LOCATION_EXTRA_INFO HLEI1,
PER_GEN_HIERARCHY_NODES PGHN
WHERE ( HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information' )
AND HLEI1.LOCATION_ID = PGHN.ENTITY_ID
AND PGHN.PARENT_HIERARCHY_NODE_ID = C_PARENT_NODE_ID
AND PGHN.HIERARCHY_NODE_ID = C_TOT_ACTFORMULA.EST_NODE_ID
AND PGHN.NODE_TYPE = 'EST';
SELECT
PEO.PERSON_ID,
PEO.LAST_NAME,
PEO.FIRST_NAME,
PEO.EMPLOYEE_NUMBER,
PEO.SEX,
--PEO.PER_INFORMATION1 ETHNIC, /* Commented for bug#11736960 */
ASS.ASSIGNMENT_ID,
ASS.JOB_ID,
JOB.NAME JOB_NAME,
ASS.LOCATION_ID,
HL.LOCATION_CODE
FROM
/* Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F and PER_JOBS_VL
with PER_JOBS - bug#11736960 */
--PER_PEOPLE_F PEO
PER_ALL_PEOPLE_F PEO,
PER_ASSIGNMENTS_F ASS,
--PER_JOBS_VL JOB,
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 L_DATE_START <= NVL(JOB.DATE_TO
,L_DATE_START)
AND L_DATE_END >= 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
-- Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F -bug#11736960
--PER_PEOPLE_F PEO1
PER_ALL_PEOPLE_F PEO1
WHERE L_DATE_START <= PEO1.EFFECTIVE_END_DATE
AND L_DATE_END >= 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 L_DATE_START <= ASS1.EFFECTIVE_END_DATE
AND L_DATE_END >= 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 = HL.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' ) )
*/
AND HEADQUARTERS = 'N'
-- 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
COUNT('X')
INTO L_REPORT_EMPS
FROM
--Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F and PER_JOBS_VL with PER_JOBS
--PER_PEOPLE_F PEO,
PER_ALL_PEOPLE_F PEO,
PER_ASSIGNMENTS_F ASS,
--PER_JOBS_VL JOB
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 PEO.PER_INFORMATION_CATEGORY = 'US'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND L_DATE_START <= NVL(JOB.DATE_TO
,L_DATE_START)
AND L_DATE_END >= 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
PER_ALL_PEOPLE_F PEO1
WHERE L_DATE_START <= PEO1.EFFECTIVE_END_DATE
AND L_DATE_END >= 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 L_DATE_START <= ASS1.EFFECTIVE_END_DATE
AND L_DATE_END >= 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 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' ) )
*/
AND HEADQUARTERS = 'N'
-- 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
COUNT('X')
INTO L_COUNT_EMPS
FROM
PER_ASSIGNMENTS_F ASS,
--Replaced PER_JOBS_VL with PER_JOBS -bug#11736960
--PER_JOBS_VL JOB
PER_JOBS JOB
WHERE JOB.JOB_INFORMATION_CATEGORY = 'US'
AND L_DATE_START <= NVL(JOB.DATE_TO
,L_DATE_END)
AND L_DATE_END >= JOB.DATE_FROM
AND JOB.JOB_INFORMATION1 is not null
AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
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 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 L_DATE_START <= ASS1.EFFECTIVE_END_DATE
AND L_DATE_END >= 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 L_DATE_START <= ASS.EFFECTIVE_END_DATE
AND L_DATE_END >= ASS.EFFECTIVE_START_DATE
AND HEADQUARTERS = 'N'
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' ) )
*/
;
SELECT
MAX(FCR.REQUEST_ID)
FROM
FND_CONCURRENT_REQUESTS FCR,
FND_CONCURRENT_PROGRAMS FCP
WHERE FCP.APPLICATION_ID = 800
AND FCP.CONCURRENT_PROGRAM_NAME = 'PERRPE1L'
AND FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID;