VIEW: APPS.PAY_JP_WIC_ASSACTS_V2

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, /* Return '-1' when any future assacts exist after YEA. Note if any future assacts exist after YEA even the YEA belongs to different SWOT, this YEA is not regarded as YEA, but just as normal payment, which means "Previous Jobs Amount" and "Adjustment Amount" does not contribute to the total amount. */ (case when wic.itax_yea_category is not null then (case when ( select count(*) from pay_jp_wic_assacts_v1 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)) > 0 then '-1' else wic.itax_yea_category end) else null end), wic.PERSON_ID, wic.PERIOD_OF_SERVICE_ID, (case when wic.date_start >= trunc(wic.effective_date, 'YYYY') then wic.date_start else null end), (case when wic.effective_date >= trunc(wic.actual_termination_date, 'YYYY') then wic.leaving_reason else null end), (case when wic.effective_date >= trunc(wic.actual_termination_date, 'YYYY') then wic.actual_termination_date else null end), wic.TAXABLE_AMT, wic.SI_PREM, wic.MUTUAL_AID, wic.ITAX, wic.ITAX_ADJUSTMENT, wic.DISASTER_TAX_REDUCTION, wic.EMPLOYMENT_CATEGORY, wic.LEGISLATIVE_PARAMETERS from pay_jp_wic_assacts_v1 wic

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
, /* RETURN '-1' WHEN ANY FUTURE ASSACTS EXIST AFTER YEA. NOTE IF ANY FUTURE ASSACTS EXIST AFTER YEA EVEN THE YEA BELONGS TO DIFFERENT SWOT
, THIS YEA IS NOT REGARDED AS YEA
, BUT JUST AS NORMAL PAYMENT
, WHICH MEANS "PREVIOUS JOBS AMOUNT"
AND "ADJUSTMENT AMOUNT" DOES NOT CONTRIBUTE TO THE TOTAL AMOUNT. */ (CASE WHEN WIC.ITAX_YEA_CATEGORY IS NOT NULL THEN (CASE WHEN ( SELECT COUNT(*)
FROM PAY_JP_WIC_ASSACTS_V1 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)) > 0 THEN '-1' ELSE WIC.ITAX_YEA_CATEGORY END) ELSE NULL END)
, WIC.PERSON_ID
, WIC.PERIOD_OF_SERVICE_ID
, (CASE WHEN WIC.DATE_START >= TRUNC(WIC.EFFECTIVE_DATE
, 'YYYY') THEN WIC.DATE_START ELSE NULL END)
, (CASE WHEN WIC.EFFECTIVE_DATE >= TRUNC(WIC.ACTUAL_TERMINATION_DATE
, 'YYYY') THEN WIC.LEAVING_REASON ELSE NULL END)
, (CASE WHEN WIC.EFFECTIVE_DATE >= TRUNC(WIC.ACTUAL_TERMINATION_DATE
, 'YYYY') THEN WIC.ACTUAL_TERMINATION_DATE ELSE NULL END)
, WIC.TAXABLE_AMT
, WIC.SI_PREM
, WIC.MUTUAL_AID
, WIC.ITAX
, WIC.ITAX_ADJUSTMENT
, WIC.DISASTER_TAX_REDUCTION
, WIC.EMPLOYMENT_CATEGORY
, WIC.LEGISLATIVE_PARAMETERS
FROM PAY_JP_WIC_ASSACTS_V1 WIC