VIEW: APPS.PSP_REP_SSP_CHG1_V

Source

View Text - Preformatted

SELECT A.DISTRIBUTION_LINE_ID ,D.PAYROLL_ID , D.TIME_PERIOD_ID , C.PERSON_ID , C.ASSIGNMENT_ID , C.ELEMENT_TYPE_ID , A.SUSPENSE_ORG_ACCOUNT_ID , decode(C.DR_CR_FLAG,'C',-(A.DISTRIBUTION_AMOUNT), A.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT , A.SUSPENSE_REASON_CODE , F.ORGANIZATION_ID , S.PROJECT_ID ACTUAL_PROJECT_ID , S.EXPENDITURE_ORGANIZATION_ID ACTUAL_EXP_ORG_ID ,nvl (a.suspense_auto_exp_type, s.expenditure_type) actual_expenditure_type , S.TASK_ID ACTUAL_TASK_ID , S.AWARD_ID ACTUAL_AWARD_ID , nvl(a.suspense_auto_glccid, s.gl_code_combination_id) actual_gl_ccid , A.DISTRIBUTION_DATE , nvl(c.or_project_id, nvl(b.project_id, nvl(pdls.project_id, nvl(pod.project_id, pea.project_id)))) project_id , nvl(c.or_expenditure_org_id, nvl(b.expenditure_organization_id, nvl(pdls.expenditure_organization_id, nvl(pod.expenditure_organization_id, pea.expenditure_organization_id )))) expenditure_organization_id , nvl(to_char(c.or_expenditure_type) , nvl(b.expenditure_type, nvl(pdls.expenditure_type, nvl(pod.expenditure_type, pea.expenditure_type )))) expenditure_type , nvl(c.or_task_id, nvl(b.task_id, nvl(pdls.task_id, nvl(pod.task_id, pea.task_id )))) task_id , nvl(c.or_award_id, nvl(b.award_id, nvl(pdls.award_id, nvl(pod.award_id, pea.award_id )))) award_id , nvl(c.or_gl_code_combination_id, nvl(b.gl_code_combination_id, nvl(pdls.gl_code_combination_id, nvl(pod.gl_code_combination_id, pea.gl_code_combination_id )))) gl_code_combination_id , A.BUSINESS_GROUP_ID , A.SET_OF_BOOKS_ID , d.currency_code FROM PSP_DISTRIBUTION_LINES_HISTORY A, PSP_ORGANIZATION_ACCOUNTS pod, PSP_ELEMENT_TYPE_ACCOUNTS pea, PSP_DEFAULT_LABOR_SCHEDULES pdls, PSP_SCHEDULE_LINES B, PSP_ORGANIZATION_ACCOUNTS S, PSP_PAYROLL_SUB_LINES SB, PSP_PAYROLL_CONTROLS D, PSP_PAYROLL_LINES C, PER_ALL_ASSIGNMENTS_F E, HR_ALL_ORGANIZATION_UNITS F WHERE A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL AND A.REVERSAL_ENTRY_FLAG IS NULL AND A.SUSPENSE_ORG_ACCOUNT_ID =S.ORGANIZATION_ACCOUNT_ID AND A.PAYROLL_SUB_LINE_ID=SB.PAYROLL_SUB_LINE_ID AND SB.PAYROLL_LINE_ID=C.PAYROLL_LINE_ID AND C.PAYROLL_CONTROL_ID=D.PAYROLL_CONTROL_ID AND C.ASSIGNMENT_ID=E.ASSIGNMENT_ID AND A.distribution_date between e.effective_start_date and e.effective_end_date AND E.ORGANIZATION_ID=F.ORGANIZATION_ID AND A.SCHEDULE_LINE_ID=B.SCHEDULE_LINE_ID(+) AND A.DEFAULT_ORG_ACCOUNT_ID=pod.organization_account_id(+) AND A.ELEMENT_ACCOUNT_ID=pea.element_account_id(+) AND A.org_schedule_id=pdls.org_schedule_id(+) AND NOT EXISTS (SELECT '1' FROM PSP_ADJUSTMENT_LINES_HISTORY G WHERE G.ORIG_LINE_ID=A.DISTRIBUTION_LINE_ID AND G.ORIGINAL_LINE_FLAG='Y' AND G.ORIG_SOURCE_TYPE='D') UNION ALL SELECT A.PRE_GEN_DIST_LINE_ID DISTRIBUTION_LINE_ID, D.PAYROLL_ID, A.TIME_PERIOD_ID, A.PERSON_ID, A.ASSIGNMENT_ID, A.ELEMENT_TYPE_ID, A.SUSPENSE_ORG_ACCOUNT_ID, decode(A.DR_CR_FLAG,'C',-(A.DISTRIBUTION_AMOUNT), A.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT, A.SUSPENSE_REASON_CODE, B.ORGANIZATION_ID, s.PROJECT_ID ACTUAL_PROJECT_ID, s.EXPENDITURE_ORGANIZATION_ID ACTUAL_EXP_ORG_ID, nvl(a.suspense_auto_exp_type, s.EXPENDITURE_TYPE) ACTUAL_EXPENDITURE_TYPE, s.TASK_ID ACTUAL_TASK_ID, s.AWARD_ID ACTUAL_AWARD_ID, nvl(a.suspense_auto_glccid, s.gl_code_combination_id) actual_gl_ccid, A.DISTRIBUTION_DATE, A.PROJECT_ID, A.EXPENDITURE_ORGANIZATION_ID, A.EXPENDITURE_TYPE, A.TASK_ID, A.AWARD_ID, A.GL_CODE_COMBINATION_ID, A.BUSINESS_GROUP_ID, A.SET_OF_BOOKS_ID, ppc.currency_code FROM PSP_PRE_GEN_DIST_LINES_HISTORY A, HR_ALL_ORGANIZATION_UNITS B, PER_ALL_ASSIGNMENTS_F C, PER_TIME_PERIODS D, psp_organization_accounts s, psp_payroll_controls ppc WHERE A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL AND A.REVERSAL_ENTRY_FLAG IS NULL AND a.suspense_org_account_id = s.organization_account_id AND A.ASSIGNMENT_ID=C.ASSIGNMENT_ID AND A.distribution_date between C.effective_start_date and C.effective_end_date AND ppc.payroll_control_id = a.payroll_control_id AND C.ORGANIZATION_ID=B.ORGANIZATION_ID AND D.TIME_PERIOD_ID=A.TIME_PERIOD_ID AND NOT EXISTS (SELECT '1' FROM PSP_ADJUSTMENT_LINES_HISTORY B WHERE B.ORIG_LINE_ID=A.PRE_GEN_DIST_LINE_ID AND B.ORIGINAL_LINE_FLAG='Y' AND B.ORIG_SOURCE_TYPE='P')

View Text - HTML Formatted

SELECT A.DISTRIBUTION_LINE_ID
, D.PAYROLL_ID
, D.TIME_PERIOD_ID
, C.PERSON_ID
, C.ASSIGNMENT_ID
, C.ELEMENT_TYPE_ID
, A.SUSPENSE_ORG_ACCOUNT_ID
, DECODE(C.DR_CR_FLAG
, 'C'
, -(A.DISTRIBUTION_AMOUNT)
, A.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT
, A.SUSPENSE_REASON_CODE
, F.ORGANIZATION_ID
, S.PROJECT_ID ACTUAL_PROJECT_ID
, S.EXPENDITURE_ORGANIZATION_ID ACTUAL_EXP_ORG_ID
, NVL (A.SUSPENSE_AUTO_EXP_TYPE
, S.EXPENDITURE_TYPE) ACTUAL_EXPENDITURE_TYPE
, S.TASK_ID ACTUAL_TASK_ID
, S.AWARD_ID ACTUAL_AWARD_ID
, NVL(A.SUSPENSE_AUTO_GLCCID
, S.GL_CODE_COMBINATION_ID) ACTUAL_GL_CCID
, A.DISTRIBUTION_DATE
, NVL(C.OR_PROJECT_ID
, NVL(B.PROJECT_ID
, NVL(PDLS.PROJECT_ID
, NVL(POD.PROJECT_ID
, PEA.PROJECT_ID)))) PROJECT_ID
, NVL(C.OR_EXPENDITURE_ORG_ID
, NVL(B.EXPENDITURE_ORGANIZATION_ID
, NVL(PDLS.EXPENDITURE_ORGANIZATION_ID
, NVL(POD.EXPENDITURE_ORGANIZATION_ID
, PEA.EXPENDITURE_ORGANIZATION_ID )))) EXPENDITURE_ORGANIZATION_ID
, NVL(TO_CHAR(C.OR_EXPENDITURE_TYPE)
, NVL(B.EXPENDITURE_TYPE
, NVL(PDLS.EXPENDITURE_TYPE
, NVL(POD.EXPENDITURE_TYPE
, PEA.EXPENDITURE_TYPE )))) EXPENDITURE_TYPE
, NVL(C.OR_TASK_ID
, NVL(B.TASK_ID
, NVL(PDLS.TASK_ID
, NVL(POD.TASK_ID
, PEA.TASK_ID )))) TASK_ID
, NVL(C.OR_AWARD_ID
, NVL(B.AWARD_ID
, NVL(PDLS.AWARD_ID
, NVL(POD.AWARD_ID
, PEA.AWARD_ID )))) AWARD_ID
, NVL(C.OR_GL_CODE_COMBINATION_ID
, NVL(B.GL_CODE_COMBINATION_ID
, NVL(PDLS.GL_CODE_COMBINATION_ID
, NVL(POD.GL_CODE_COMBINATION_ID
, PEA.GL_CODE_COMBINATION_ID )))) GL_CODE_COMBINATION_ID
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
, D.CURRENCY_CODE
FROM PSP_DISTRIBUTION_LINES_HISTORY A
, PSP_ORGANIZATION_ACCOUNTS POD
, PSP_ELEMENT_TYPE_ACCOUNTS PEA
, PSP_DEFAULT_LABOR_SCHEDULES PDLS
, PSP_SCHEDULE_LINES B
, PSP_ORGANIZATION_ACCOUNTS S
, PSP_PAYROLL_SUB_LINES SB
, PSP_PAYROLL_CONTROLS D
, PSP_PAYROLL_LINES C
, PER_ALL_ASSIGNMENTS_F E
, HR_ALL_ORGANIZATION_UNITS F
WHERE A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL
AND A.REVERSAL_ENTRY_FLAG IS NULL
AND A.SUSPENSE_ORG_ACCOUNT_ID =S.ORGANIZATION_ACCOUNT_ID
AND A.PAYROLL_SUB_LINE_ID=SB.PAYROLL_SUB_LINE_ID
AND SB.PAYROLL_LINE_ID=C.PAYROLL_LINE_ID
AND C.PAYROLL_CONTROL_ID=D.PAYROLL_CONTROL_ID
AND C.ASSIGNMENT_ID=E.ASSIGNMENT_ID
AND A.DISTRIBUTION_DATE BETWEEN E.EFFECTIVE_START_DATE
AND E.EFFECTIVE_END_DATE
AND E.ORGANIZATION_ID=F.ORGANIZATION_ID
AND A.SCHEDULE_LINE_ID=B.SCHEDULE_LINE_ID(+)
AND A.DEFAULT_ORG_ACCOUNT_ID=POD.ORGANIZATION_ACCOUNT_ID(+)
AND A.ELEMENT_ACCOUNT_ID=PEA.ELEMENT_ACCOUNT_ID(+)
AND A.ORG_SCHEDULE_ID=PDLS.ORG_SCHEDULE_ID(+)
AND NOT EXISTS (SELECT '1'
FROM PSP_ADJUSTMENT_LINES_HISTORY G
WHERE G.ORIG_LINE_ID=A.DISTRIBUTION_LINE_ID
AND G.ORIGINAL_LINE_FLAG='Y'
AND G.ORIG_SOURCE_TYPE='D') UNION ALL SELECT A.PRE_GEN_DIST_LINE_ID DISTRIBUTION_LINE_ID
, D.PAYROLL_ID
, A.TIME_PERIOD_ID
, A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ELEMENT_TYPE_ID
, A.SUSPENSE_ORG_ACCOUNT_ID
, DECODE(A.DR_CR_FLAG
, 'C'
, -(A.DISTRIBUTION_AMOUNT)
, A.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT
, A.SUSPENSE_REASON_CODE
, B.ORGANIZATION_ID
, S.PROJECT_ID ACTUAL_PROJECT_ID
, S.EXPENDITURE_ORGANIZATION_ID ACTUAL_EXP_ORG_ID
, NVL(A.SUSPENSE_AUTO_EXP_TYPE
, S.EXPENDITURE_TYPE) ACTUAL_EXPENDITURE_TYPE
, S.TASK_ID ACTUAL_TASK_ID
, S.AWARD_ID ACTUAL_AWARD_ID
, NVL(A.SUSPENSE_AUTO_GLCCID
, S.GL_CODE_COMBINATION_ID) ACTUAL_GL_CCID
, A.DISTRIBUTION_DATE
, A.PROJECT_ID
, A.EXPENDITURE_ORGANIZATION_ID
, A.EXPENDITURE_TYPE
, A.TASK_ID
, A.AWARD_ID
, A.GL_CODE_COMBINATION_ID
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
, PPC.CURRENCY_CODE
FROM PSP_PRE_GEN_DIST_LINES_HISTORY A
, HR_ALL_ORGANIZATION_UNITS B
, PER_ALL_ASSIGNMENTS_F C
, PER_TIME_PERIODS D
, PSP_ORGANIZATION_ACCOUNTS S
, PSP_PAYROLL_CONTROLS PPC
WHERE A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL
AND A.REVERSAL_ENTRY_FLAG IS NULL
AND A.SUSPENSE_ORG_ACCOUNT_ID = S.ORGANIZATION_ACCOUNT_ID
AND A.ASSIGNMENT_ID=C.ASSIGNMENT_ID
AND A.DISTRIBUTION_DATE BETWEEN C.EFFECTIVE_START_DATE
AND C.EFFECTIVE_END_DATE
AND PPC.PAYROLL_CONTROL_ID = A.PAYROLL_CONTROL_ID
AND C.ORGANIZATION_ID=B.ORGANIZATION_ID
AND D.TIME_PERIOD_ID=A.TIME_PERIOD_ID
AND NOT EXISTS (SELECT '1'
FROM PSP_ADJUSTMENT_LINES_HISTORY B
WHERE B.ORIG_LINE_ID=A.PRE_GEN_DIST_LINE_ID
AND B.ORIGINAL_LINE_FLAG='Y'
AND B.ORIG_SOURCE_TYPE='P')