VIEW: APPS.PSP_REP_SSP_CHG_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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