[Home] [Help]
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,
ASS.ASSIGNMENT_ID,
ASS.JOB_ID,
JOB.NAME JOB_NAME,
ASS.LOCATION_ID,
HL.LOCATION_CODE
FROM
PER_PEOPLE_F PEO,
PER_ASSIGNMENTS_F ASS,
PER_JOBS_VL JOB,
HR_LOCATIONS_ALL HL
WHERE PEO.PERSON_ID = ASS.PERSON_ID
AND PEO.PER_INFORMATION1 is not null
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
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
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 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,
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 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';
SELECT
COUNT('ass')
INTO L_REPORT_EMPS
FROM
PER_PEOPLE_F PEO,
PER_ASSIGNMENTS_F ASS,
PER_JOBS_VL JOB
WHERE PEO.PERSON_ID = ASS.PERSON_ID
AND PEO.PER_INFORMATION1 is not null
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
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
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 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,
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 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';
SELECT
COUNT('ass')
INTO L_COUNT_EMPS
FROM
PER_ASSIGNMENTS_F ASS,
PER_JOBS_VL 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,
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
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 L_DATE_START <= ASS.EFFECTIVE_END_DATE
AND L_DATE_END >= ASS.EFFECTIVE_START_DATE
AND HEADQUARTERS = 'N'
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;