[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT '68' 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.PER_INFORMATION1 = '13'
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
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)
;
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.PER_INFORMATION1 = '13'
AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
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;
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'), 'IPED7', 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);
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'), 'IPED7', 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);
EXECUTE IMMEDIATE 'DELETE FROM PAY_US_RPT_TOTALS
WHERE ATTRIBUTE1 = ''IPED7''';