View: PSP_REP_SSP_CHG1_V

Product:	PSP - Labor Distribution
Description:	Based on PSP_REP_SSP_CHG_V that is used in Suspense Account Charging report.5-FEB-02 Ritesh Modified query to fix Bug:2209306
Implementation/DBA Data:	APPS.PSP_REP_SSP_CHG1_V

View Text

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')

Columns