[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
2 LINE,
HL.LOOKUP_CODE JOBCODE,
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 ASS,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
PER_PAY_PROPOSALS PPP,
PER_PAY_BASES PPB,
HR_LOOKUPS HL
WHERE PEO.PERSON_ID = ASS.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) IN ( 'PR' )
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' )
AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (
SELECT
MAX(CHANGE_DATE)
FROM
PER_PAY_PROPOSALS PRO
WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE <= P_REPORT_DATE
AND PRO.APPROVED = 'Y' )
AND NVL(PPP.PROPOSED_SALARY_N
,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.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 ASS,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
PER_PAY_PROPOSALS PPP,
PER_PAY_BASES PPB,
HR_LOOKUPS HL,
PER_PEOPLE_EXTRA_INFO PEI
WHERE PEO.PERSON_ID = ASS.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 PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) IN ( 'PR' )
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' )
AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (
SELECT
MAX(CHANGE_DATE)
FROM
PER_PAY_PROPOSALS PRO
WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE <= P_REPORT_DATE
AND PRO.APPROVED = 'Y' )
AND NVL(PPP.PROPOSED_SALARY_N
,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.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;
SELECT
1 LINE,
'Faculty(Instruction/Research/Public Service)' DISP_NAME,
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)))) A_PMEN,
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)))) A_PWMEN,
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 ASS,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
PER_PAY_PROPOSALS PPP,
PER_PAY_BASES PPB,
HR_LOOKUPS HL
WHERE PEO.PERSON_ID = ASS.PERSON_ID
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) IN ( 'PR' )
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE IN ( '1' , '2' , '3' )
AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (
SELECT
MAX(CHANGE_DATE)
FROM
PER_PAY_PROPOSALS PRO
WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE <= P_REPORT_DATE
AND PRO.APPROVED = 'Y' )
AND NVL(PPP.PROPOSED_SALARY_N
,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.ORGANIZATION_ID in (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
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 ASS,
PER_ASSIGNMENT_STATUS_TYPES AST,
PER_JOBS JOB,
PER_PAY_PROPOSALS PPP,
PER_PAY_BASES PPB,
HR_LOOKUPS HL,
PER_PEOPLE_EXTRA_INFO PEI
WHERE PEO.PERSON_ID = ASS.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 PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
,CP_FR
,CP_FT
,CP_PR
,CP_PT) IN ( 'PR' )
AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
AND JOB.JOB_INFORMATION_CATEGORY = 'US'
AND HL.LOOKUP_CODE IN ( '1' , '2' , '3' )
AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND ASS.PRIMARY_FLAG = 'Y'
AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (
SELECT
MAX(CHANGE_DATE)
FROM
PER_PAY_PROPOSALS PRO
WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
AND PRO.CHANGE_DATE <= P_REPORT_DATE
AND PRO.APPROVED = 'Y' )
AND NVL(PPP.PROPOSED_SALARY_N
,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
AND ASS.JOB_ID = JOB.JOB_ID
AND ASS.ASSIGNMENT_TYPE = 'E'
AND ASS.ORGANIZATION_ID in (
SELECT
ORGANIZATION_ID
FROM
HR_ALL_ORGANIZATION_UNITS
WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
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
,VALUE18)
VALUES (USERENV('sessionid')
,'IPED11'
,LINE
,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
,ATTRIBUTE2
,VALUE1
,VALUE2
,VALUE3
,VALUE4
,VALUE5
,VALUE6
,VALUE7
,VALUE8
,VALUE9
,VALUE10
,VALUE11
,VALUE12
,VALUE13
,VALUE14
,VALUE15
,VALUE16
,VALUE17)
VALUES (USERENV('sessionid')
,'IPED11'
,SC
,LINE
,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 = ''IPED11''';