Section 10- DOne

-- PTD USD PTD_EARNINGS_USD AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PTD_EARNINGS_USD, MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY FROM CN_TP_PARTICIPANT_PAY_ALL P INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.ORG_ID = PD.ORG_ID LEFT JOIN CONVERSION_RATES_USD conversion ON TRUNC( CASE WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE ELSE PB.PAY_DATE END ) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.FROM_CURRENCY = P.HOME_CURRENCY_CODE WHERE ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE)) OR (P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PB.PAY_DATE)) ) AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID ), --PTD earnings MC PTD_EARNINGS_AMT AS ( SELECT ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID, ptd_usd.PTD_EARNINGS_USD, PDX.CURRENCY_CODE LATEST_HOME_CURRENCY, TO_CHAR( ptd_usd.PTD_EARNINGS_USD * ( 1 / ( NVL( (CASE WHEN PDX.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), 'FM9999999990.0000' ) AS PTD_EARNINGS_LC FROM PTD_EARNINGS_USD ptd_usd INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = ptd_usd.PAYRUN_ID AND PB.ORG_ID = ptd_usd.ORG_ID LEFT JOIN ( SELECT * FROM ( SELECT PB.PAYRUN_ID, PB.PAY_DATE, PDX.PARTICIPANT_ID, PDX.ORG_ID, PDX.START_DATE, PDX.END_DATE, CPV.start_date period_start_date, CPV.end_date period_end_date, PDX.CURRENCY_CODE, PB.PAY_PERIOD_ID, RANK() OVER (ORDER BY PDX.start_date DESC) rank FROM CN_TP_PAYRUNS_ALL PB, CN_SRP_PARTICIPANT_DETAILS_ALL PDX, CN_PERIODS_VL CPV WHERE PB.ORG_ID = PDX.ORG_ID AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND PDX.PARTICIPANT_ID = NVL(:participant_id, pdx.PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) AND PB.pay_period_id = CPV.period_id AND PDX.START_DATE >= CPV.start_date ) a WHERE rank = 1 ) PDX ON ptd_usd.PAYRUN_ID = PDX.PAYRUN_ID AND ptd_usd.CREDITED_PARTICIPANT_ID = PDX.PARTICIPANT_ID AND ptd_usd.ORG_ID = PDX.Org_ID AND ptd_usd.PAY_PERIOD_ID = PDX.PAY_PERIOD_ID LEFT JOIN CONVERSION_RATES_USD conversion2 ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.FROM_CURRENCY = PDX.CURRENCY_CODE ), -- Materialize prorated TI - FILTERED PM_TI AS ( SELECT PARTICIPANT_ID, ORG_ID, PERIOD_ID, START_DATE, END_DATE, Prorated_MonthlyTI FROM ( SELECT SPFM.PARTICIPANT_ID, SPFM.ORG_ID, SPFM.PERIOD_ID, SRP_CP.START_DATE, SRP_CP.END_DATE, SPFM.OUTPUT_ACHIEVED_ITD AS Prorated_MonthlyTI, ROW_NUMBER() OVER ( PARTITION BY SPFM.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID ORDER BY NVL(SRP_CP.END_DATE, DATE '9999-12-31') DESC ) AS LATEST_TI_RECORD FROM CN_SRP_PER_FORM_METRICS_ALL SPFM INNER JOIN CN_FORMULAS_ALL_B Monthly_TI ON Monthly_TI.FORMULA_ID = SPFM.FORMULA_ID AND Monthly_TI.ATTRIBUTE9 = 'EmployeeEvents' AND Monthly_TI.ATTRIBUTE10 = 'MonthlyTI' INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP ON SRP_CP.PARTICIPANT_ID = SPFM.PARTICIPANT_ID AND SPFM.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SPFM.ORG_ID = SRP_CP.ORG_ID INNER JOIN CN_PERIOD_DIMS_ALL_TL PERIOD ON PERIOD.ORG_ID = SPFM.ORG_ID AND PERIOD.PERIOD_TYPE = 'Monthly' AND PERIOD.PERIOD_ID = SPFM.PERIOD_ID WHERE ( PERIOD.END_DATE BETWEEN SRP_CP.START_DATE AND NVL(SRP_CP.END_DATE, DATE '9999-12-31') OR NVL(SRP_CP.END_DATE, DATE '9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE ) AND SPFM.PARTICIPANT_ID = NVL(:participant_id, SPFM.PARTICIPANT_ID) AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID) ) WHERE LATEST_TI_RECORD = 1 ),

Comments

Popular posts from this blog

Section 15-Done

Section 16-Done

Section 13-Done