need to fix
SELECT
BB.PAYRUN_ID,
BB.CREDITED_PARTICIPANT_ID,
BB.PAY_PERIOD_ID,
BB.HOME_CURRENCY_CODE,
BB.BB_AMOUNT_HOME_CURR, -- mulricurrency
BB.BB_AMOUNT_USD,
BB.LATEST_HOME_CURRENCY,
BB.OLD_PAY_PERIOD_IDS,
BB.BB_AMOUNT_USD *
CASE
WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1)
END AS BB_IN_LATEST_HC_NUM,
CASE
WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1)
END AS CONVERSION_RATE_USD_TO_LC
FROM (
SELECT
P.PAYRUN_ID,
P.PAYEE_PARTICIPANT_ID AS CREDITED_PARTICIPANT_ID,
P.PAYMENT_BATCH_PERIOD AS PAY_PERIOD_ID,
--P.HOME_CURRENCY_CODE,
LISTAGG(DISTINCT P.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS HOME_Currency_code,
SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR, -- multicurrency
SUM(
P.PAY_AMT_PAY_CURR *
CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE NVL(CR_USD.CONVERSION_RATE, 1)
END
) AS BB_AMOUNT_USD,
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY,
MAX(P.PAY_DATE) AS PAY_DATE,
LISTAGG(DISTINCT P.PAY_PERIOD_ID, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS OLD_PAY_PERIOD_IDS
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
INNER JOIN PARTICIPANT_DETAILS_CACHE PD
ON P.PAYEE_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.PAY_DATE)
INNER JOIN PARTICIPANT_DETAILS_CACHE PDX
ON P.PAYEE_PARTICIPANT_ID = PDX.PARTICIPANT_ID
AND (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PDX.START_DATE
AND NVL(PDX.END_DATE, (P.SOURCE_EVENT_DATE)))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND P.PAY_DATE BETWEEN PDX.START_DATE
AND NVL(PDX.END_DATE, P.PAY_DATE))
)
LEFT JOIN CONVERSION_RATES_USD CR_USD
ON P.HOME_CURRENCY_CODE != 'USD'
AND CR_USD.FROM_CURRENCY =PDX.CURRENCY_CODE
AND TRUNC(P.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE
AND CR_USD.CONVERSION_END_DATE
WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID)
AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
AND P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
AND EXISTS (
SELECT 1 FROM BB_MULTI_CURRENCY bb
WHERE bb.PAYEE_PARTICIPANT_ID = P.PAYEE_PARTICIPANT_ID
AND bb.PAYRUN_ID = P.PAYRUN_ID
AND bb.PAYMENT_BATCH_PERIOD = P.PAYMENT_BATCH_PERIOD
)
GROUP BY
P.PAYRUN_ID,
P.PAYEE_PARTICIPANT_ID,
P.PAYMENT_BATCH_PERIOD,
P.HOME_CURRENCY_CODE
) BB
LEFT JOIN (
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') CR_LC
ON BB.LATEST_HOME_CURRENCY != 'USD'
AND CR_LC.FROM_CURRENCY = BB.LATEST_HOME_CURRENCY
AND TRUNC(BB.PAY_DATE) BETWEEN CR_LC.CONVERSION_START_DATE
AND CR_LC.CONVERSION_END_DATE
Comments
Post a Comment