[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
'1' JCODE,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) NRMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) NRWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) BNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) BNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) AM_ALMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) AM_ALWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) APMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) APWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) HMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) HWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) WNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) WNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) URMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_PEOPLE_EXTRA_INFO PPET,
PER_JOBS JOB,
HR_LOOKUPS HL
WHERE PAF.PERSON_ID = PPET.PERSON_ID
AND PAF.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
AND PPET.PEI_INFORMATION1 = '01'
AND PPET.PEI_INFORMATION1 IS NOT NULL
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ORGANIZATION_ID IN (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
GROUP BY
'1';
SELECT
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) BNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) BNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) AM_ALMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) AM_ALWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) A_PMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) A_PWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) HMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) HWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) WNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) WNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) URMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_PEOPLE_EXTRA_INFO PPET,
PER_JOBS JOB,
HR_LOOKUPS HL,
PER_PEOPLE_EXTRA_INFO PEI
WHERE PAF.PERSON_ID = PPET.PERSON_ID
AND PAF.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PEO.PER_INFORMATION1 = '13'
AND PEO.PERSON_ID = pei.person_id (+)
AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
AND not exists (
SELECT
1
FROM
PER_PEOPLE_EXTRA_INFO PEI2
WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
AND PEI2.PERSON_ID = PEI.PERSON_ID )
AND PEI.PERSON_EXTRA_INFO_ID = (
SELECT
MAX(PEI1.PERSON_EXTRA_INFO_ID)
FROM
PER_PEOPLE_EXTRA_INFO PEI1
WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
OR ( not exists (
SELECT
PERSON_EXTRA_INFO_ID
FROM
PER_PEOPLE_EXTRA_INFO PEI3
WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
AND PPET.PEI_INFORMATION1 = '01'
AND PPET.PEI_INFORMATION1 IS NOT NULL
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ORGANIZATION_ID IN (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
SELECT
'2' JCODE,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) NRMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) NRWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) BNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) BNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) AM_ALMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) AM_ALWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) APMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) APWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) HMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) HWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) WNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) WNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) URMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_PEOPLE_EXTRA_INFO PPET,
PER_JOBS JOB,
HR_LOOKUPS HL
WHERE PAF.PERSON_ID = PPET.PERSON_ID
AND PAF.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
AND PPET.PEI_INFORMATION1 IS NOT NULL
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ORGANIZATION_ID IN (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
GROUP BY
'2';
SELECT
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) BNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) BNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) AM_ALMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) AM_ALWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) A_PMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) A_PWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) HMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) HWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) WNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) WNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) URMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_PEOPLE_EXTRA_INFO PPET,
PER_JOBS JOB,
HR_LOOKUPS HL,
PER_PEOPLE_EXTRA_INFO PEI
WHERE PAF.PERSON_ID = PPET.PERSON_ID
AND PAF.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PEO.PER_INFORMATION1 = '13'
AND PEO.PERSON_ID = pei.person_id (+)
AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
AND not exists (
SELECT
1
FROM
PER_PEOPLE_EXTRA_INFO PEI2
WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
AND PEI2.PERSON_ID = PEI.PERSON_ID )
AND PEI.PERSON_EXTRA_INFO_ID = (
SELECT
MAX(PEI1.PERSON_EXTRA_INFO_ID)
FROM
PER_PEOPLE_EXTRA_INFO PEI1
WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
OR ( not exists (
SELECT
PERSON_EXTRA_INFO_ID
FROM
PER_PEOPLE_EXTRA_INFO PEI3
WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
AND PPET.PEI_INFORMATION1 IS NOT NULL
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ORGANIZATION_ID IN (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
SELECT
'3' JCODE,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) NRMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) NRWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) BNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) BNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) AM_ALMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) AM_ALWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) APMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) APWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) HMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) HWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) WNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) WNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) URMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
HR_LOOKUPS HL
WHERE PAF.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND not exists (
SELECT
PET.PERSON_ID
FROM
PER_PEOPLE_EXTRA_INFO PET
WHERE PET.PERSON_ID = PEO.PERSON_ID
AND PET.PEI_INFORMATION_CATEGORY in ( 'PQH_TENURE_STATUS' )
AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ORGANIZATION_ID IN (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
GROUP BY
'3';
SELECT
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) BNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) BNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) AM_ALMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) AM_ALWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) A_PMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) A_PWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) HMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) HWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) WNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) WNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) URMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
HR_LOOKUPS HL,
PER_PEOPLE_EXTRA_INFO PEI
WHERE PAF.PERSON_ID = PEO.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PEO.PER_INFORMATION1 = '13'
AND PEO.PERSON_ID = pei.person_id (+)
AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
AND not exists (
SELECT
1
FROM
PER_PEOPLE_EXTRA_INFO PEI2
WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
AND PEI2.PERSON_ID = PEI.PERSON_ID )
AND PEI.PERSON_EXTRA_INFO_ID = (
SELECT
MAX(PEI1.PERSON_EXTRA_INFO_ID)
FROM
PER_PEOPLE_EXTRA_INFO PEI1
WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
OR ( not exists (
SELECT
PERSON_EXTRA_INFO_ID
FROM
PER_PEOPLE_EXTRA_INFO PEI3
WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
AND not exists (
SELECT
PET.PERSON_ID
FROM
PER_PEOPLE_EXTRA_INFO PET
WHERE PET.PERSON_ID = PEO.PERSON_ID
AND PET.PEI_INFORMATION_CATEGORY in ( 'PQH_TENURE_STATUS' )
AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ORGANIZATION_ID IN (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
SELECT
HL.LOOKUP_CODE JCODE,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) NRMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) NRWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) BNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) BNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) AM_ALMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) AM_ALWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) APMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) APWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) HMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) HWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) WNHMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) WNHWMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)))) URMEN,
COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
,P_REPORT_DATE)
,NULL
,(DECODE(PEO.PER_INFORMATION1
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)))) URWMEN,
COUNT(DECODE(PEO.SEX
,'M'
,PEO.PERSON_ID
,NULL)) TOTMEN,
COUNT(DECODE(PEO.SEX
,'F'
,PEO.PERSON_ID
,NULL)) TOTWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
HR_LOOKUPS HL
WHERE PEO.PERSON_ID = PAF.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' , '4' , '12' )
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ORGANIZATION_ID in (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
GROUP BY
HL.LOOKUP_CODE;
SELECT
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) BNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'2'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) BNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) AM_ALMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'6'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) AM_ALWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) A_PMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'4'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'5'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) A_PWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) HMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'3'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,'9'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) HWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) WNHMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,'1'
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) WNHWMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'M'
,1
,NULL)
,NULL)) URMEN,
COUNT(DECODE(PEI.PEI_INFORMATION5
,NULL
,DECODE(PEO.SEX
,'F'
,1
,NULL)
,NULL)) URWMEN
FROM
PER_ALL_PEOPLE_F PEO,
PER_ALL_ASSIGNMENTS_F PAF,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
HR_LOOKUPS HL,
PER_PEOPLE_EXTRA_INFO PEI
WHERE PEO.PERSON_ID = PAF.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PEO.PER_INFORMATION1 = '13'
AND PEO.PERSON_ID = pei.person_id (+)
AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
AND not exists (
SELECT
1
FROM
PER_PEOPLE_EXTRA_INFO PEI2
WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
AND PEI2.PERSON_ID = PEI.PERSON_ID )
AND PEI.PERSON_EXTRA_INFO_ID = (
SELECT
MAX(PEI1.PERSON_EXTRA_INFO_ID)
FROM
PER_PEOPLE_EXTRA_INFO PEI1
WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
OR ( not exists (
SELECT
PERSON_EXTRA_INFO_ID
FROM
PER_PEOPLE_EXTRA_INFO PEI3
WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' , '4' , '12' )
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) = 'FR'
AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
AND P_REPORT_DATE
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND PAF.JOB_ID = JOB.JOB_ID
AND PAF.ASSIGNMENT_TYPE = 'E'
AND PAF.ORGANIZATION_ID in (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
AND HL.LOOKUP_CODE = C_LOOKUP_CODE;
INSERT INTO PAY_US_RPT_TOTALS
(SESSION_ID
,ATTRIBUTE1
,VALUE1
,VALUE2
,VALUE3
,VALUE4
,VALUE5
,VALUE6
,VALUE7
,VALUE8
,VALUE9
,VALUE10
,VALUE11
,VALUE12
,VALUE13
,VALUE14
,VALUE15
,VALUE16
,VALUE17)
VALUES (USERENV('sessionid')
,'IPED9'
,SC
,L_NR_MEN
,L_NR_WMEN
,L_BNH_MEN
,L_BNH_WMEN
,L_AMAI_MEN
,L_AMAI_WMEN
,L_AP_MEN
,L_AP_WMEN
,L_H_MEN
,L_H_WMEN
,L_WNH_MEN
,L_WNH_WMEN
,L_UR_MEN
,L_UR_WMEN
,L_TOT_MEN
,L_TOT_WMEN);
INSERT INTO PAY_US_RPT_TOTALS
(SESSION_ID
,ATTRIBUTE1
,VALUE1
,VALUE2
,VALUE3
,VALUE4
,VALUE5
,VALUE6
,VALUE7
,VALUE8
,VALUE9
,VALUE10
,VALUE11
,VALUE12
,VALUE13
,VALUE14
,VALUE15
,VALUE16
,VALUE17)
VALUES (USERENV('sessionid')
,'IPED9'
,SC
,L_NR_MEN
,L_NR_WMEN
,L_BNH_MEN
,L_BNH_WMEN
,L_AMAI_MEN
,L_AMAI_WMEN
,L_AP_MEN
,L_AP_WMEN
,L_H_MEN
,L_H_WMEN
,L_WNH_MEN
,L_WNH_WMEN
,L_UR_MEN
,L_UR_WMEN
,L_TOT_MEN
,L_TOT_WMEN);
INSERT INTO PAY_US_RPT_TOTALS
(SESSION_ID
,ATTRIBUTE1
,VALUE1
,VALUE2
,VALUE3
,VALUE4
,VALUE5
,VALUE6
,VALUE7
,VALUE8
,VALUE9
,VALUE10
,VALUE11
,VALUE12
,VALUE13
,VALUE14
,VALUE15
,VALUE16
,VALUE17)
VALUES (USERENV('sessionid')
,'IPED9'
,SC
,L_NR_MEN
,L_NR_WMEN
,L_BNH_MEN
,L_BNH_WMEN
,L_AMAI_MEN
,L_AMAI_WMEN
,L_AP_MEN
,L_AP_WMEN
,L_H_MEN
,L_H_WMEN
,L_WNH_MEN
,L_WNH_WMEN
,L_UR_MEN
,L_UR_WMEN
,L_TOT_MEN
,L_TOT_WMEN);
INSERT INTO PAY_US_RPT_TOTALS
(SESSION_ID
,ATTRIBUTE1
,VALUE1
,VALUE2
,VALUE3
,VALUE4
,VALUE5
,VALUE6
,VALUE7
,VALUE8
,VALUE9
,VALUE10
,VALUE11
,VALUE12
,VALUE13
,VALUE14
,VALUE15
,VALUE16
,VALUE17)
VALUES (USERENV('sessionid')
,'IPED9'
,SC
,L_NR_MEN
,L_NR_WMEN
,L_BNH_MEN
,L_BNH_WMEN
,L_AMAI_MEN
,L_AMAI_WMEN
,L_AP_MEN
,L_AP_WMEN
,L_H_MEN
,L_H_WMEN
,L_WNH_MEN
,L_WNH_WMEN
,L_UR_MEN
,L_UR_WMEN
,L_TOT_MEN
,L_TOT_WMEN);
'DELETE FROM pay_us_rpt_totals
WHERE attribute1 = ''IPED9''';