Product: | PSP - Labor Distribution |
---|---|
Description: | Based on PSP_DISTRIBUTION_LINES_HISTORY for the Distribution History by Default Account report |
Implementation/DBA Data: | APPS.PSP_REP_DEF_CHG_V |
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