VIEW: APPS.PAY_JP_WIC_ASSACTS_V

Source

View Text - Preformatted

SELECT wic.ASSIGNMENT_ACTION_ID, wic.ASSIGNMENT_ID, wic.ACTION_SEQUENCE, wic.PAYROLL_ACTION_ID, wic.BUSINESS_GROUP_ID, wic.PAYROLL_ID, wic.EFFECTIVE_DATE, wic.DATE_EARNED, wic.ITAX_ORGANIZATION_ID, wic.SALARY_CATEGORY, wic.ITAX_CATEGORY, wic.ITAX_YEA_CATEGORY, wic.PERSON_ID, wic.PERIOD_OF_SERVICE_ID, wic.DATE_START, wic.LEAVING_REASON, wic.ACTUAL_TERMINATION_DATE, wic.EMPLOYMENT_CATEGORY, wic.LEGISLATIVE_PARAMETERS from pay_jp_wic_assacts_v2 wic where not exists( select null from pay_jp_wic_assacts_v2 wic2 where wic2.assignment_id = wic.assignment_id and wic2.action_sequence > wic.action_sequence and wic2.effective_date < add_months(trunc(wic.effective_date, 'YYYY'), 12) and wic2.itax_organization_id = wic.itax_organization_id and (/* Exclude assacts with the same kind of "Tax Category". */ ((wic.itax_category in ('M_KOU', 'D_KOU') and wic2.itax_category in ('M_KOU', 'D_KOU')) or (wic.itax_category in ('M_OTSU','D_OTSU') and wic2.itax_category in ('M_OTSU', 'D_OTSU')) or (wic.itax_category = 'D_HEI' and wic2.itax_category = 'D_HEI')) or /* Exclude assacts having "Valid" future YEA assact. */ (wic2.itax_yea_category = '0')))

View Text - HTML Formatted

SELECT WIC.ASSIGNMENT_ACTION_ID
, WIC.ASSIGNMENT_ID
, WIC.ACTION_SEQUENCE
, WIC.PAYROLL_ACTION_ID
, WIC.BUSINESS_GROUP_ID
, WIC.PAYROLL_ID
, WIC.EFFECTIVE_DATE
, WIC.DATE_EARNED
, WIC.ITAX_ORGANIZATION_ID
, WIC.SALARY_CATEGORY
, WIC.ITAX_CATEGORY
, WIC.ITAX_YEA_CATEGORY
, WIC.PERSON_ID
, WIC.PERIOD_OF_SERVICE_ID
, WIC.DATE_START
, WIC.LEAVING_REASON
, WIC.ACTUAL_TERMINATION_DATE
, WIC.EMPLOYMENT_CATEGORY
, WIC.LEGISLATIVE_PARAMETERS
FROM PAY_JP_WIC_ASSACTS_V2 WIC
WHERE NOT EXISTS( SELECT NULL
FROM PAY_JP_WIC_ASSACTS_V2 WIC2
WHERE WIC2.ASSIGNMENT_ID = WIC.ASSIGNMENT_ID
AND WIC2.ACTION_SEQUENCE > WIC.ACTION_SEQUENCE
AND WIC2.EFFECTIVE_DATE < ADD_MONTHS(TRUNC(WIC.EFFECTIVE_DATE
, 'YYYY')
, 12)
AND WIC2.ITAX_ORGANIZATION_ID = WIC.ITAX_ORGANIZATION_ID
AND (/* EXCLUDE ASSACTS WITH THE SAME KIND OF "TAX CATEGORY". */ ((WIC.ITAX_CATEGORY IN ('M_KOU'
, 'D_KOU')
AND WIC2.ITAX_CATEGORY IN ('M_KOU'
, 'D_KOU')) OR (WIC.ITAX_CATEGORY IN ('M_OTSU'
, 'D_OTSU')
AND WIC2.ITAX_CATEGORY IN ('M_OTSU'
, 'D_OTSU')) OR (WIC.ITAX_CATEGORY = 'D_HEI'
AND WIC2.ITAX_CATEGORY = 'D_HEI')) OR /* EXCLUDE ASSACTS HAVING "VALID" FUTURE YEA ASSACT. */ (WIC2.ITAX_YEA_CATEGORY = '0')))