View: PAY_JP_ITWB_ARCHIVE_OPTION_V

Product:	PAY - Payroll
Description:	This view is used for Japanese localization only.
Implementation/DBA Data:	APPS.PAY_JP_ITWB_ARCHIVE_OPTION_V

View Text

SELECT PPA.BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE
, PJIP.ITAX_ORG_ID
, STATUS_V.STATUS
, PJIP.PAYROLL_ACTION_ID ARCHIVE_PAYROLL_ACTION_ID
, PJIP.ARCHIVE_SEQUENCE
, TO_CHAR(TRUNC(PJIP.EFFECTIVE_DATE
, 'YYYY')
, 'YYYY.MM')||' - '||TO_CHAR(PJIP.EFFECTIVE_DATE
, 'YYYY.MM') ARCHIVE_PERIOD
, TO_CHAR(TRUNC(PJIP.EFFECTIVE_DATE
, 'YYYY')
, 'YYYY.MM')||' - '||TO_CHAR(PJIP.EFFECTIVE_DATE
, 'YYYY.MM')||' ('||TO_CHAR(PJIP.PAYROLL_ACTION_ID)||')' ARCHIVE_INFO
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_JP_ITWB_PACT_V PJIP
, (SELECT PAYROLL_ACTION_ID
, 'ADD' STATUS
FROM PAY_PAYROLL_ACTIONS UNION SELECT PAYROLL_ACTION_ID
, 'UPDATE' STATUS
FROM PAY_PAYROLL_ACTIONS) STATUS_V
WHERE PPA.ACTION_TYPE = 'X'
AND PPA.ACTION_STATUS = 'C'
AND PPA.REPORT_TYPE = 'JP_ITWB_ARCH'
AND PPA.REPORT_QUALIFIER = 'JP'
AND PPA.REPORT_CATEGORY = 'ARCHIVE'
AND PJIP.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PJIP.STATUS <> 'UPDATE'
AND EXISTS( SELECT NULL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_JP_ITWB_ASSACT_V PJIA
WHERE PAA.PAYROLL_ACTION_ID = PJIP.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PJIA.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID)
AND STATUS_V.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID UNION SELECT PPA.BUSINESS_GROUP_ID
, PPA.EFFECTIVE_DATE
, PJIP.ITAX_ORG_ID
, 'RENEW' STATUS
, PJIP.PAYROLL_ACTION_ID ARCHIVE_PAYROLL_ACTION_ID
, PJIP.ARCHIVE_SEQUENCE
, TO_CHAR(TRUNC(PJIP.EFFECTIVE_DATE
, 'YYYY')
, 'YYYY.MM')||' - '||TO_CHAR(PJIP.EFFECTIVE_DATE
, 'YYYY.MM') ARCHIVE_PERIOD
, TO_CHAR(TRUNC(PJIP.EFFECTIVE_DATE
, 'YYYY')
, 'YYYY.MM')||' - '||TO_CHAR(PJIP.EFFECTIVE_DATE
, 'YYYY.MM')||' ('||TO_CHAR(PJIP.PAYROLL_ACTION_ID)||')' ARCHIVE_INFO
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_JP_ITWB_PACT_V PJIP
WHERE PPA.ACTION_TYPE = 'X'
AND PPA.ACTION_STATUS = 'C'
AND PPA.REPORT_TYPE = 'JP_ITWB_ARCH'
AND PPA.REPORT_QUALIFIER = 'JP'
AND PPA.REPORT_CATEGORY = 'ARCHIVE'
AND PJIP.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PJIP.STATUS <> 'UPDATE'
AND EXISTS( SELECT NULL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_JP_ITWB_ASSACT_V PJIA
WHERE PAA.PAYROLL_ACTION_ID = PJIP.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PJIA.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID) UNION SELECT PERF.BUSINESS_GROUP_ID
, TO_DATE(NULL) EFFECTIVE_DATE
, TO_NUMBER(NULL) ITAX_ORG_ID
, 'RENEW' STATUS
, NULL ARCHIVE_PAYROLL_ACTION_ID
, NULL ARCHIVE_SEQUENCE
, NULL ARCHIVE_PERIOD
, NULL ARCHIVE_INFO
FROM PER_BUSINESS_GROUPS PERF

Columns