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
Post a Comment