View: PSP_REP_SSP_CHG_V

Product:	PSP - Labor Distribution
Description:	Based on PSP_DISTRIBUTION_LINES_HISTORY used in Suspense Account Charging report
Implementation/DBA Data:	APPS.PSP_REP_SSP_CHG_V

View Text

SELECT S.ASSIGNMENT_ID
, F.ORGANIZATION_ID
, A.DISTRIBUTION_DATE
, F.NAME
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_DISTRIBUTION_LINES_HISTORY A
, PSP_SUMMARY_LINES S
, PER_ALL_ASSIGNMENTS_F E
, HR_ALL_ORGANIZATION_UNITS F
, PSP_ORGANIZATION_ACCOUNTS S1
WHERE A.SUSPENSE_ORG_ACCOUNT_ID IS NOT NULL
AND A.REVERSAL_ENTRY_FLAG IS NULL
AND A.SUSPENSE_ORG_ACCOUNT_ID= S1.ORGANIZATION_ACCOUNT_ID
AND A.SUMMARY_LINE_ID=S.SUMMARY_LINE_ID
AND S.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 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.ASSIGNMENT_ID
, B.ORGANIZATION_ID
, A.DISTRIBUTION_DATE
, B.NAME
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_PRE_GEN_DIST_LINES_HISTORY A
, HR_ALL_ORGANIZATION_UNITS B
, PER_ALL_ASSIGNMENTS_F C
, PSP_ORGANIZATION_ACCOUNTS S
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 C.ORGANIZATION_ID=B.ORGANIZATION_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') UNION ALL SELECT A.ASSIGNMENT_ID
, B.ORGANIZATION_ID
, A.DISTRIBUTION_DATE
, B.NAME
, A.BUSINESS_GROUP_ID
, A.SET_OF_BOOKS_ID
FROM PSP_ADJUSTMENT_LINES_HISTORY A
, HR_ALL_ORGANIZATION_UNITS B
, PER_ALL_ASSIGNMENTS_F C
, PSP_ORGANIZATION_ACCOUNTS S
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 C.ORGANIZATION_ID=B.ORGANIZATION_ID
AND NOT EXISTS (SELECT '1'
FROM PSP_ADJUSTMENT_LINES_HISTORY B
WHERE B.ORIG_LINE_ID=A.ADJUSTMENT_LINE_ID
AND B.ORIGINAL_LINE_FLAG='Y'
AND B.ORIG_SOURCE_TYPE='A')

Columns