VIEW: APPS.PAY_JP_ITWB_ARCHIVE_OPTION_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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