bb
SELECT *
FROM (
-- MCChild: Main query with payment details and conversions
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE AS latest_currency_code,
SUM(spd.PAY_AMT_PAY_CURR) AS "BB PTD Earning LC",
NVL(
MAX(
CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END
),
1
) AS "BB Conversion Rate (LC_to_USD)",
SUM(spd.PAY_AMT_PAY_CURR) * MAX(
CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END
) AS "BB PTD Earning (USD)",
TO_CHAR(
MAX(
CASE
WHEN pd.CURRENCY_CODE = 'USD' THEN 1
ELSE 1 / conversion2.CONVERSION_RATE
END
),
'0000000000000.0000000000000'
) AS "BB Conversion Rate (USD_to_Latest_LC)",
TO_CHAR(
SUM(spd.PAY_AMT_PAY_CURR) * MAX(
CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END
) * MAX(
CASE
WHEN pd.CURRENCY_CODE = 'USD' THEN 1
ELSE 1 / conversion2.CONVERSION_RATE
END
),
'0000000000000.0000000000000'
) AS "BB PTD Earning (Latest HC)",
MAX(PS.PMT_TXN_AMT_DUE) AS "BB MC Earnings"
FROM CN_TP_PARTICIPANT_PAY_EXP_V spd
-- Join participant details
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL pd
ON spd.PAYEE_PARTICIPANT_ID = pd.PARTICIPANT_ID
AND spd.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE, spd.PAY_DATE)
-- Join paysheets
INNER JOIN CN_TP_PAYSHEETS_ALL PS
ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = spd.PAYRUN_ID
AND PS.QUOTA_ID IS NULL
-- Left join for LC to USD conversion
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'
) conversion
ON SPD.HOME_CURRENCY_CODE != 'USD'
AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE
AND TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE
-- Left join for USD to latest LC conversion
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'
) conversion2
ON pd.CURRENCY_CODE != 'USD'
AND conversion2.FROM_CURRENCY = pd.CURRENCY_CODE
AND TRUNC(SPD.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE
WHERE 1 = 1
AND spd.PAYRUN_ID = NVL(:payrun_id, spd.PAYRUN_ID)
AND spd.PAYEE_PARTICIPANT_ID = NVL(:participant_id, spd.PAYEE_PARTICIPANT_ID)
AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, spd.PAYMENT_BATCH_PERIOD)
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE
) MCChild
-- EXISTS clause to filter only multi-currency participants (Beginning Balance scenario)
WHERE EXISTS (
SELECT 1
FROM (
-- Subquery to identify multi-currency scenarios in batch payments
-- This identifies participants with multiple home currencies where pay period differs from batch period
SELECT
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID
FROM (
-- Get distinct payment records with home currency
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE 1 = 1
GROUP BY
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.PAYRUN_ID,
P.HOME_CURRENCY_CODE
) pay_1
INNER JOIN (
-- Get payments where pay period differs from batch period
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
GROUP BY
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
PAYRUN_ID
) pay_2
ON pay_1.PAYRUN_ID = pay_2.PAYRUN_ID
AND pay_1.PAYMENT_BATCH_PERIOD = pay_2.PAYMENT_BATCH_PERIOD
AND pay_1.PAYEE_PARTICIPANT_ID = pay_2.PAYEE_PARTICIPANT_ID
GROUP BY
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID
HAVING COUNT(DISTINCT pay_1.HOME_CURRENCY_CODE) > 1
) bb_MultiC
WHERE MCChild.PAYEE_PARTICIPANT_ID = bb_MultiC.PAYEE_PARTICIPANT_ID
AND MCChild.PAYRUN_ID = bb_MultiC.PAYRUN_ID
AND MCChild.PAYMENT_BATCH_PERIOD = bb_MultiC.PAYMENT_BATCH_PERIOD
);
Comments
Post a Comment