VIEW: APPS.PSP_REP_DEF_CHG_V

Source

View Text - Preformatted

SELECT D.PAYROLL_ID , D.TIME_PERIOD_ID , C.PERSON_ID , C.ASSIGNMENT_ID , C.ELEMENT_TYPE_ID , SUM(A.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT , A.DEFAULT_REASON_CODE , F.ORGANIZATION_ID , G.PROJECT_ID , G.EXPENDITURE_ORGANIZATION_ID , G.EXPENDITURE_TYPE , G.TASK_ID , G.AWARD_ID , G.GL_CODE_COMBINATION_ID , A.DEFAULT_ORG_ACCOUNT_ID , A.DISTRIBUTION_DATE ,D.BUSINESS_GROUP_ID ,D.SET_OF_BOOKS_ID , d.currency_code FROM PSP_DISTRIBUTION_LINES_HISTORY A, PSP_PAYROLL_SUB_LINES B, PSP_PAYROLL_LINES C, PSP_PAYROLL_CONTROLS D, PER_ASSIGNMENTS_F E, HR_ORGANIZATION_UNITS F, PSP_ORGANIZATION_ACCOUNTS G WHERE G.ORGANIZATION_ACCOUNT_ID = A.DEFAULT_ORG_ACCOUNT_ID AND A.PAYROLL_SUB_LINE_ID = B.PAYROLL_SUB_LINE_ID AND B.PAYROLL_LINE_ID = C.PAYROLL_LINE_ID AND C.PAYROLL_CONTROL_ID = D.PAYROLL_CONTROL_ID AND (A.DEFAULT_ORG_ACCOUNT_ID IS NOT NULL) AND A.SUSPENSE_ORG_ACCOUNT_ID IS NULL AND NOT EXISTS (SELECT 1 FROM psp_adjustment_lines_history palh where palh.orig_line_id = a.distribution_line_id AND palh.orig_source_type = 'D' AND palh.original_line_flag = 'Y') AND E.ASSIGNMENT_ID = C.ASSIGNMENT_ID AND E.ORGANIZATION_ID = F.ORGANIZATION_ID AND A.DISTRIBUTION_DATE BETWEEN E.EFFECTIVE_START_DATE AND E.EFFECTIVE_END_DATE GROUP BY D.BUSINESS_GROUP_ID, D.SET_OF_BOOKS_ID, D.PAYROLL_ID, F.ORGANIZATION_ID, d.currency_code, G.GL_CODE_COMBINATION_ID, A.SUSPENSE_ORG_ACCOUNT_ID, G.PROJECT_ID, G.EXPENDITURE_ORGANIZATION_ID, G.EXPENDITURE_TYPE, G.TASK_ID, G.AWARD_ID, A.DEFAULT_ORG_ACCOUNT_ID, D.TIME_PERIOD_ID, C.PERSON_ID, C.ASSIGNMENT_ID, C.ELEMENT_TYPE_ID, A.DISTRIBUTION_DATE, A.DEFAULT_REASON_CODE

View Text - HTML Formatted

SELECT D.PAYROLL_ID
, D.TIME_PERIOD_ID
, C.PERSON_ID
, C.ASSIGNMENT_ID
, C.ELEMENT_TYPE_ID
, SUM(A.DISTRIBUTION_AMOUNT) DISTRIBUTION_AMOUNT
, A.DEFAULT_REASON_CODE
, F.ORGANIZATION_ID
, G.PROJECT_ID
, G.EXPENDITURE_ORGANIZATION_ID
, G.EXPENDITURE_TYPE
, G.TASK_ID
, G.AWARD_ID
, G.GL_CODE_COMBINATION_ID
, A.DEFAULT_ORG_ACCOUNT_ID
, A.DISTRIBUTION_DATE
, D.BUSINESS_GROUP_ID
, D.SET_OF_BOOKS_ID
, D.CURRENCY_CODE
FROM PSP_DISTRIBUTION_LINES_HISTORY A
, PSP_PAYROLL_SUB_LINES B
, PSP_PAYROLL_LINES C
, PSP_PAYROLL_CONTROLS D
, PER_ASSIGNMENTS_F E
, HR_ORGANIZATION_UNITS F
, PSP_ORGANIZATION_ACCOUNTS G
WHERE G.ORGANIZATION_ACCOUNT_ID = A.DEFAULT_ORG_ACCOUNT_ID
AND A.PAYROLL_SUB_LINE_ID = B.PAYROLL_SUB_LINE_ID
AND B.PAYROLL_LINE_ID = C.PAYROLL_LINE_ID
AND C.PAYROLL_CONTROL_ID = D.PAYROLL_CONTROL_ID
AND (A.DEFAULT_ORG_ACCOUNT_ID IS NOT NULL)
AND A.SUSPENSE_ORG_ACCOUNT_ID IS NULL
AND NOT EXISTS (SELECT 1
FROM PSP_ADJUSTMENT_LINES_HISTORY PALH
WHERE PALH.ORIG_LINE_ID = A.DISTRIBUTION_LINE_ID
AND PALH.ORIG_SOURCE_TYPE = 'D'
AND PALH.ORIGINAL_LINE_FLAG = 'Y')
AND E.ASSIGNMENT_ID = C.ASSIGNMENT_ID
AND E.ORGANIZATION_ID = F.ORGANIZATION_ID
AND A.DISTRIBUTION_DATE BETWEEN E.EFFECTIVE_START_DATE
AND E.EFFECTIVE_END_DATE GROUP BY D.BUSINESS_GROUP_ID
, D.SET_OF_BOOKS_ID
, D.PAYROLL_ID
, F.ORGANIZATION_ID
, D.CURRENCY_CODE
, G.GL_CODE_COMBINATION_ID
, A.SUSPENSE_ORG_ACCOUNT_ID
, G.PROJECT_ID
, G.EXPENDITURE_ORGANIZATION_ID
, G.EXPENDITURE_TYPE
, G.TASK_ID
, G.AWARD_ID
, A.DEFAULT_ORG_ACCOUNT_ID
, D.TIME_PERIOD_ID
, C.PERSON_ID
, C.ASSIGNMENT_ID
, C.ELEMENT_TYPE_ID
, A.DISTRIBUTION_DATE
, A.DEFAULT_REASON_CODE