PTD fxed
WITH
RANKED_PDX AS (
SELECT
PARTICIPANT_ID,
ORG_ID,
CURRENCY_CODE,
START_DATE,
END_DATE,
ROW_NUMBER() OVER (PARTITION BY PARTICIPANT_ID, ORG_ID ORDER BY END_DATE DESC NULLS FIRST, START_DATE DESC) AS RN
FROM CN_SRP_PARTICIPANT_DETAILS_ALL
WHERE PARTICIPANT_ID = NVL(:participant_id, PARTICIPANT_ID)
),
CONVERSION_RATES AS (
SELECT
CR.FROM_CURRENCY,
CR.TO_CURRENCY,
CR.CONVERSION_START_DATE,
CR.CONVERSION_END_DATE,
CR.CONVERSION_RATE
FROM CN_CONVERSION_RATES CR
INNER JOIN CN_CONVERSION_TYPES_TL CT
ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID
WHERE CT.CONVERSION_TYPE_ID = '-1001'
AND CT.LANGUAGE = 'US'
AND CR.TO_CURRENCY = 'USD'
)
SELECT
PAYRUN_ID,
CREDITED_PARTICIPANT_ID,
ORG_ID,
PAY_PERIOD_ID,
TOTAL_PTD_EARNINGS_USD,
LATEST_HOME_CURRENCY,
CURRENCIES_LIST,
EARNINGS_USD_LIST,
EARNINGS_LC_LIST
FROM (
SELECT
ptd_usd.PAYRUN_ID,
ptd_usd.CREDITED_PARTICIPANT_ID,
ptd_usd.ORG_ID,
ptd_usd.PAY_PERIOD_ID,
SUM(ptd_usd.PTD_EARNINGS_USD) OVER (PARTITION BY ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID) AS TOTAL_PTD_EARNINGS_USD,
PDX.CURRENCY_CODE AS LATEST_HOME_CURRENCY,
LISTAGG(ptd_usd.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS CURRENCIES_LIST,
LISTAGG(TO_CHAR(ptd_usd.PTD_EARNINGS_USD, 'FM9999999990.0000'), ', ') WITHIN GROUP (ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS EARNINGS_USD_LIST,
LISTAGG(
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'
),
', '
) WITHIN GROUP (ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS EARNINGS_LC_LIST,
ROW_NUMBER() OVER (PARTITION BY ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS RN
FROM (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID,
P.HOME_CURRENCY_CODE,
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,
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 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.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ')
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,
P.HOME_CURRENCY_CODE,
PD.CURRENCY_CODE
) 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
INNER JOIN RANKED_PDX PDX
ON ptd_usd.CREDITED_PARTICIPANT_ID = PDX.PARTICIPANT_ID
AND ptd_usd.ORG_ID = PDX.ORG_ID
AND PDX.RN = 1
AND TRUNC(PB.PAY_DATE) BETWEEN PDX.START_DATE
AND NVL(PDX.END_DATE, PB.PAY_DATE)
INNER JOIN CONVERSION_RATES conversion2
ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = PDX.CURRENCY_CODE
)
WHERE RN = 1
Comments
Post a Comment