[Home] [Help]
| Product: | PSP - Labor Distribution |
|---|---|
| Description: | Based on the PSP_DISTRIBUTION_LINES_HISTORY table used in the Distribution History by Organization report |
| Implementation/DBA Data: | Not implemented in this database |
SELECT A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ELEMENT_TYPE_ID
, A.TIME_PERIOD_ID
, A.PAYROLL_ID
, A.DISTRIBUTION_AMOUNT
, A.DISTRIBUTION_DATE
, A.PERIOD_NAME
, A.SUSPENSE_ORG_ACCOUNT_ID ACCOUNT_ID
, A.ORGANIZATION_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID
, 'SUSP' ORG_SOURCE
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_REP_DIST_ORG_V A
, PSP_ORGANIZATION_ACCOUNTS B
WHERE A.SUSPENSE_ORG_ACCOUNT_ID = B.ORGANIZATION_ACCOUNT_ID
AND A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL
AND A.DEFAULT_ORG_ACCOUNT_ID IS NULL
AND A.ELEMENT_ACCOUNT_ID IS NULL
AND A.ORG_SCHEDULE_ID IS NULL
AND A.SCHEDULE_LINE_ID IS NULL UNION ALL SELECT A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ELEMENT_TYPE_ID
, A.TIME_PERIOD_ID
, A.PAYROLL_ID
, A.DISTRIBUTION_AMOUNT
, A.DISTRIBUTION_DATE
, A.PERIOD_NAME
, A.DEFAULT_ORG_ACCOUNT_ID ACCOUNT_ID
, A.ORGANIZATION_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID
, 'DEF' ORG_SOURCE
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_REP_DIST_ORG_V A
, PSP_ORGANIZATION_ACCOUNTS B
WHERE A.DEFAULT_ORG_ACCOUNT_ID = B.ORGANIZATION_ACCOUNT_ID
AND SUSPENSE_ORG_ACCOUNT_ID IS NULL
AND ELEMENT_ACCOUNT_ID IS NULL
AND ORG_SCHEDULE_ID IS NULL
AND SCHEDULE_LINE_ID IS NULL UNION ALL SELECT A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ELEMENT_TYPE_ID
, A.TIME_PERIOD_ID
, A.PAYROLL_ID
, A.DISTRIBUTION_AMOUNT
, A.DISTRIBUTION_DATE
, A.PERIOD_NAME
, A.ELEMENT_ACCOUNT_ID ACCOUNT_ID
, A.ORGANIZATION_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID
, 'EL_TYPE' ORG_SOURCE
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_REP_DIST_ORG_V A
, PSP_ELEMENT_TYPE_ACCOUNTS B
WHERE A.ELEMENT_ACCOUNT_ID = B.ELEMENT_ACCOUNT_ID
AND SUSPENSE_ORG_ACCOUNT_ID IS NULL
AND DEFAULT_ORG_ACCOUNT_ID IS NULL
AND ORG_SCHEDULE_ID IS NULL
AND SCHEDULE_LINE_ID IS NULL UNION ALL SELECT A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ELEMENT_TYPE_ID
, A.TIME_PERIOD_ID
, A.PAYROLL_ID
, A.DISTRIBUTION_AMOUNT
, A.DISTRIBUTION_DATE
, A.PERIOD_NAME
, A.ORG_SCHEDULE_ID ACCOUNT_ID
, A.ORGANIZATION_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID
, 'LABOR' ORG_SOURCE
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_REP_DIST_ORG_V A
, PSP_DEFAULT_LABOR_SCHEDULES B
WHERE A.ORG_SCHEDULE_ID = B.ORG_SCHEDULE_ID
AND SUSPENSE_ORG_ACCOUNT_ID IS NULL
AND DEFAULT_ORG_ACCOUNT_ID IS NULL
AND ELEMENT_ACCOUNT_ID IS NULL
AND SCHEDULE_LINE_ID IS NULL UNION ALL SELECT A.PERSON_ID
, A.ASSIGNMENT_ID
, A.ELEMENT_TYPE_ID
, A.TIME_PERIOD_ID
, A.PAYROLL_ID
, A.DISTRIBUTION_AMOUNT
, A.DISTRIBUTION_DATE
, A.PERIOD_NAME
, A.SCHEDULE_LINE_ID ACCOUNT_ID
, A.ORGANIZATION_ID
, B.PROJECT_ID
, B.EXPENDITURE_ORGANIZATION_ID
, B.EXPENDITURE_TYPE
, B.TASK_ID
, B.AWARD_ID
, B.GL_CODE_COMBINATION_ID
, 'SCH_LINE' ORG_SOURCE
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_REP_DIST_ORG_V A
, PSP_SCHEDULE_LINES B
WHERE A.SCHEDULE_LINE_ID = B.SCHEDULE_LINE_ID
AND SUSPENSE_ORG_ACCOUNT_ID IS NULL
AND DEFAULT_ORG_ACCOUNT_ID IS NULL
AND ELEMENT_ACCOUNT_ID IS NULL
AND ORG_SCHEDULE_ID IS NULL