VIEW: APPS.PAY_JP_TAX_SWOT_V

Source

View Text - Preformatted

SELECT pjwa.business_group_id, to_number(to_char(pjwa.effective_date,'YYYY')) year, pjwa.itax_organization_id, pjwa.effective_date, pjwa.date_earned, pjwa.assignment_action_id, pjwa.person_id, pjwa.period_of_service_id, pjwa.itax_category, pjwa.itax_yea_category, decode(padr.address_id,null,padc.town_or_city,padr.town_or_city), pjwa.actual_termination_date, pay_jp_wic_pkg.spr_term_valid( pjwa.assignment_action_id, pjwa.assignment_id, pjwa.action_sequence, pjwa.effective_date, pjwa.itax_organization_id, pjwa.itax_category, pjwa.itax_yea_category, pjwa.employment_category, pjwa.actual_termination_date) from per_addresses padc, per_addresses padr, pay_jp_wic_assacts_v pjwa where /* Residential Address */ padr.person_id(+) = pjwa.person_id and padr.address_type(+) = 'JP_R' and nvl(pjwa.actual_termination_date,to_date(lpad(to_char(to_number(to_char(pjwa.effective_date,'YYYY'))+1),4,'0') || '-01-01','YYYY-MM-DD')) between PADR.date_from(+) and nvl(PADR.date_to(+), to_date('4712-12-31','YYYY-MM-DD')) /* Current Address */ and PADC.person_id(+) = pjwa.person_id and PADC.address_type(+) = 'JP_C' and nvl(pjwa.actual_termination_date,to_date(lpad(to_char(to_number(to_char(pjwa.effective_date,'YYYY'))+1),4,'0') || '-01-01','YYYY-MM-DD')) between PADC.date_from(+) and nvl(PADC.date_to(+), to_date('4712-12-31','YYYY-MM-DD'))

View Text - HTML Formatted

SELECT PJWA.BUSINESS_GROUP_ID
, TO_NUMBER(TO_CHAR(PJWA.EFFECTIVE_DATE
, 'YYYY')) YEAR
, PJWA.ITAX_ORGANIZATION_ID
, PJWA.EFFECTIVE_DATE
, PJWA.DATE_EARNED
, PJWA.ASSIGNMENT_ACTION_ID
, PJWA.PERSON_ID
, PJWA.PERIOD_OF_SERVICE_ID
, PJWA.ITAX_CATEGORY
, PJWA.ITAX_YEA_CATEGORY
, DECODE(PADR.ADDRESS_ID
, NULL
, PADC.TOWN_OR_CITY
, PADR.TOWN_OR_CITY)
, PJWA.ACTUAL_TERMINATION_DATE
, PAY_JP_WIC_PKG.SPR_TERM_VALID( PJWA.ASSIGNMENT_ACTION_ID
, PJWA.ASSIGNMENT_ID
, PJWA.ACTION_SEQUENCE
, PJWA.EFFECTIVE_DATE
, PJWA.ITAX_ORGANIZATION_ID
, PJWA.ITAX_CATEGORY
, PJWA.ITAX_YEA_CATEGORY
, PJWA.EMPLOYMENT_CATEGORY
, PJWA.ACTUAL_TERMINATION_DATE)
FROM PER_ADDRESSES PADC
, PER_ADDRESSES PADR
, PAY_JP_WIC_ASSACTS_V PJWA
WHERE /* RESIDENTIAL ADDRESS */ PADR.PERSON_ID(+) = PJWA.PERSON_ID
AND PADR.ADDRESS_TYPE(+) = 'JP_R'
AND NVL(PJWA.ACTUAL_TERMINATION_DATE
, TO_DATE(LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(PJWA.EFFECTIVE_DATE
, 'YYYY'))+1)
, 4
, '0') || '-01-01'
, 'YYYY-MM-DD')) BETWEEN PADR.DATE_FROM(+)
AND NVL(PADR.DATE_TO(+)
, TO_DATE('4712-12-31'
, 'YYYY-MM-DD')) /* CURRENT ADDRESS */
AND PADC.PERSON_ID(+) = PJWA.PERSON_ID
AND PADC.ADDRESS_TYPE(+) = 'JP_C'
AND NVL(PJWA.ACTUAL_TERMINATION_DATE
, TO_DATE(LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(PJWA.EFFECTIVE_DATE
, 'YYYY'))+1)
, 4
, '0') || '-01-01'
, 'YYYY-MM-DD')) BETWEEN PADC.DATE_FROM(+)
AND NVL(PADC.DATE_TO(+)
, TO_DATE('4712-12-31'
, 'YYYY-MM-DD'))