dev 6 - child query fixed v2
WITH
-- Multi-currency flags
BB_MULTI_CURRENCY AS (
SELECT DISTINCT
PB.PAYRUN_ID,
PB.PAYMENT_BATCH_PERIOD,
CASE
WHEN COUNT(DISTINCT SPD.HOME_CURRENCY_CODE) OVER (
PARTITION BY PB.PAYRUN_ID, PB.PAYMENT_BATCH_PERIOD
) > 1 THEN 'Y'
ELSE 'N'
END AS MULTI_CURRENCY_YN
FROM CN_TP_PAYRUNS_ALL PB
INNER JOIN CN_SRP_PYMT_DETAILS SPD
ON SPD.PAYRUN_ID = PB.PAYRUN_ID
AND SPD.PAYMENT_BATCH_PERIOD = PB.PAYMENT_BATCH_PERIOD
WHERE PB.PERIOD_ID = :P_PERIOD_ID
AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID)
),
PERIOD_MULTI_CURRENCY AS (
SELECT DISTINCT
PB.PAYRUN_ID,
CASE
WHEN COUNT(DISTINCT SPD.HOME_CURRENCY_CODE) OVER (
PARTITION BY PB.PAYRUN_ID
) > 1 THEN 'Y'
ELSE 'N'
END AS MULTI_CURRENCY_YN
FROM CN_TP_PAYRUNS_ALL PB
INNER JOIN CN_SRP_PYMT_DETAILS SPD
ON SPD.PAYRUN_ID = PB.PAYRUN_ID
WHERE PB.PERIOD_ID = :P_PERIOD_ID
AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID)
),
-- USD conversion rates
CONVERSION_RATES_USD AS (
SELECT
FROM_CURRENCY,
TO_CURRENCY,
CONVERSION_DATE,
CONVERSION_RATE,
ROW_NUMBER() OVER (
PARTITION BY FROM_CURRENCY, TO_CURRENCY, TRUNC(CONVERSION_DATE)
ORDER BY CONVERSION_DATE DESC
) AS rn
FROM GL_DAILY_RATES
WHERE TO_CURRENCY = 'USD'
AND CONVERSION_TYPE = 'Corporate'
),
-- Latest plan component per participant
LATEST_PLAN_COMPONENT AS (
SELECT
PART_PAY.PARTICIPANT_ID,
PART_PAY.COMP_PLAN_ID,
PART_PAY.ROLE_ID,
ROW_NUMBER() OVER (
PARTITION BY PART_PAY.PARTICIPANT_ID
ORDER BY PART_PAY.START_DATE DESC
) AS rn
FROM CN_TP_PAYRUNS_ALL PB
INNER JOIN CN_PARTICIPANT_PAY PART_PAY
ON PB.ORG_ID = PART_PAY.ORG_ID
AND PART_PAY.START_DATE <= PB.END_DATE
AND NVL(PART_PAY.END_DATE, PB.END_DATE) >= PB.START_DATE
WHERE PB.PERIOD_ID = :P_PERIOD_ID
AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID)
),
-- Person numbers lookup
PERSON_NUMBERS AS (
SELECT
PERSON_ID,
PERSON_NUMBER
FROM PER_ALL_PEOPLE_F
WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
),
-- Participant details cache
PARTICIPANT_DETAILS_CACHE AS (
SELECT
P.PARTICIPANT_ID,
P.NAME AS PARTICIPANT_NAME,
PN.PERSON_NUMBER,
LPC.COMP_PLAN_ID,
CP.NAME AS COMP_PLAN_NAME,
LPC.ROLE_ID,
R.NAME AS ROLE_NAME
FROM CN_PARTICIPANTS P
LEFT JOIN PERSON_NUMBERS PN ON PN.PERSON_ID = P.PERSON_ID
LEFT JOIN LATEST_PLAN_COMPONENT LPC ON LPC.PARTICIPANT_ID = P.PARTICIPANT_ID AND LPC.rn = 1
LEFT JOIN CN_COMP_PLANS_VL CP ON CP.COMP_PLAN_ID = LPC.COMP_PLAN_ID
LEFT JOIN CN_ROLES_VL R ON R.ROLE_ID = LPC.ROLE_ID
),
-- Paysheets cache
PAYSHEETS_CACHE AS (
SELECT
PS.PAYRUN_ID,
PS.PARTICIPANT_ID,
PS.CREDITED_FLAG,
PS.PAYSHEET_STATUS
FROM CN_PAYSHEETS PS
WHERE PS.PAYSHEET_STATUS IN ('CALCULATED', 'FROZEN', 'HELD')
),
-- Payruns cache
PAYRUNS_CACHE AS (
SELECT
PB.PAYRUN_ID,
PB.NAME AS PAYRUN_NAME,
PB.PAYMENT_BATCH_PERIOD,
PB.START_DATE AS PAYRUN_START_DATE,
PB.END_DATE AS PAYRUN_END_DATE,
PB.PAY_DATE AS PAYRUN_PAY_DATE,
PER.NAME AS PERIOD_NAME
FROM CN_TP_PAYRUNS_ALL PB
INNER JOIN CN_PERIODS PER ON PER.PERIOD_ID = PB.PERIOD_ID
WHERE PB.PERIOD_ID = :P_PERIOD_ID
AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID)
),
-- Base payments (transaction level)
BASE_PAYMENTS_ALL AS (
SELECT
PB.PAYRUN_ID,
PB.PAYMENT_BATCH_PERIOD,
SPD.PAYEE_PARTICIPANT_ID,
SPD.CREDITED_PARTICIPANT_ID,
SPD.HOME_CURRENCY_CODE,
PB.PAY_DATE,
'BB' AS SCENARIO_TYPE,
CASE
WHEN SPD.PAYMENT_BATCH_PERIOD < PB.PAYMENT_BATCH_PERIOD THEN 'BB_OLD'
ELSE 'BB_CURRENT'
END AS PERIOD_TYPE,
SPD.AMOUNT AS PAY_AMT_PAY_CURR
FROM CN_TP_PAYRUNS_ALL PB
INNER JOIN CN_SRP_PYMT_DETAILS SPD
ON SPD.PAYRUN_ID = PB.PAYRUN_ID
WHERE PB.PERIOD_ID = :P_PERIOD_ID
AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID)
AND SPD.PAYMENT_BATCH_PERIOD <= PB.PAYMENT_BATCH_PERIOD
UNION ALL
SELECT
PB.PAYRUN_ID,
PB.PAYMENT_BATCH_PERIOD,
SPD.PAYEE_PARTICIPANT_ID,
SPD.CREDITED_PARTICIPANT_ID,
SPD.HOME_CURRENCY_CODE,
PB.PAY_DATE,
'PERIOD' AS SCENARIO_TYPE,
'PERIOD' AS PERIOD_TYPE,
SPD.AMOUNT AS PAY_AMT_PAY_CURR
FROM CN_TP_PAYRUNS_ALL PB
INNER JOIN CN_SRP_PYMT_DETAILS SPD
ON SPD.PAYRUN_ID = PB.PAYRUN_ID
WHERE PB.PERIOD_ID = :P_PERIOD_ID
AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID)
),
-- AGGREGATED payments (one row per currency/period type)
BASE_PAYMENTS_AGGREGATED AS (
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.CREDITED_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.SCENARIO_TYPE,
spd.PERIOD_TYPE,
spd.PAY_DATE,
SUM(spd.PAY_AMT_PAY_CURR) AS TOTAL_PAY_AMT
FROM BASE_PAYMENTS_ALL spd
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.CREDITED_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.SCENARIO_TYPE,
spd.PERIOD_TYPE,
spd.PAY_DATE
),
-- PTD aggregates for MC columns
BB_PTD_AGGREGATES AS (
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
HOME_CURRENCY_CODE,
SUM(CASE WHEN PERIOD_TYPE = 'BB_OLD' THEN TOTAL_PAY_AMT ELSE 0 END) AS BB_TOTAL_EARNINGS,
SUM(CASE WHEN PERIOD_TYPE = 'BB_CURRENT' THEN TOTAL_PAY_AMT ELSE 0 END) AS PTD_TOTAL_EARNINGS
FROM BASE_PAYMENTS_AGGREGATED
WHERE SCENARIO_TYPE = 'BB'
GROUP BY
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
HOME_CURRENCY_CODE
)
-- MAIN QUERY
SELECT
PR.PAYRUN_NAME,
PR.PAYRUN_START_DATE,
PR.PAYRUN_END_DATE,
PR.PAYRUN_PAY_DATE,
PR.PERIOD_NAME,
PD.PERSON_NUMBER,
PD.PARTICIPANT_NAME,
PD.COMP_PLAN_NAME,
PD.ROLE_NAME,
spd.HOME_CURRENCY_CODE AS "Currency",
-- BB OLD
spd.TOTAL_PAY_AMT AS "BB PTD Earning LC",
ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) AS "BB PTD Earning USD",
CASE
WHEN MC.MULTI_CURRENCY_YN = 'Y' THEN ROUND(agg.BB_TOTAL_EARNINGS * NVL(CR.CONVERSION_RATE, 1), 2)
ELSE NULL
END AS "MC BB PTD Earning USD",
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
'BB_OLD' AS SOURCE_TYPE
FROM BASE_PAYMENTS_AGGREGATED spd
INNER JOIN PAYRUNS_CACHE PR
ON PR.PAYRUN_ID = spd.PAYRUN_ID
AND PR.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
INNER JOIN PARTICIPANT_DETAILS_CACHE PD
ON PD.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
LEFT JOIN BB_MULTI_CURRENCY MC
ON MC.PAYRUN_ID = spd.PAYRUN_ID
AND MC.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
LEFT JOIN CONVERSION_RATES_USD CR
ON CR.FROM_CURRENCY = spd.HOME_CURRENCY_CODE
AND CR.CONVERSION_DATE = TRUNC(spd.PAY_DATE)
AND CR.rn = 1
LEFT JOIN BB_PTD_AGGREGATES agg
ON agg.PAYRUN_ID = spd.PAYRUN_ID
AND agg.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
AND agg.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
AND agg.HOME_CURRENCY_CODE = spd.HOME_CURRENCY_CODE
WHERE spd.SCENARIO_TYPE = 'BB'
AND spd.PERIOD_TYPE = 'BB_OLD'
UNION ALL
-- BB CURRENT
SELECT
PR.PAYRUN_NAME,
PR.PAYRUN_START_DATE,
PR.PAYRUN_END_DATE,
PR.PAYRUN_PAY_DATE,
PR.PERIOD_NAME,
PD.PERSON_NUMBER,
PD.PARTICIPANT_NAME,
PD.COMP_PLAN_NAME,
PD.ROLE_NAME,
spd.HOME_CURRENCY_CODE AS "Currency",
spd.TOTAL_PAY_AMT AS "BB PTD Earning LC",
ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) AS "BB PTD Earning USD",
CASE
WHEN MC.MULTI_CURRENCY_YN = 'Y' THEN ROUND(agg.PTD_TOTAL_EARNINGS * NVL(CR.CONVERSION_RATE, 1), 2)
ELSE NULL
END AS "MC BB PTD Earning USD",
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
'BB_CURRENT' AS SOURCE_TYPE
FROM BASE_PAYMENTS_AGGREGATED spd
INNER JOIN PAYRUNS_CACHE PR
ON PR.PAYRUN_ID = spd.PAYRUN_ID
AND PR.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
INNER JOIN PARTICIPANT_DETAILS_CACHE PD
ON PD.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
LEFT JOIN BB_MULTI_CURRENCY MC
ON MC.PAYRUN_ID = spd.PAYRUN_ID
AND MC.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
LEFT JOIN CONVERSION_RATES_USD CR
ON CR.FROM_CURRENCY = spd.HOME_CURRENCY_CODE
AND CR.CONVERSION_DATE = TRUNC(spd.PAY_DATE)
AND CR.rn = 1
LEFT JOIN BB_PTD_AGGREGATES agg
ON agg.PAYRUN_ID = spd.PAYRUN_ID
AND agg.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
AND agg.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
AND agg.HOME_CURRENCY_CODE = spd.HOME_CURRENCY_CODE
WHERE spd.SCENARIO_TYPE = 'BB'
AND spd.PERIOD_TYPE = 'BB_CURRENT'
UNION ALL
-- PERIOD
SELECT
PR.PAYRUN_NAME,
PR.PAYRUN_START_DATE,
PR.PAYRUN_END_DATE,
PR.PAYRUN_PAY_DATE,
PR.PERIOD_NAME,
PD.PARTICIPANT_NAME,
PD.PERSON_NUMBER,
PD.COMP_PLAN_NAME,
PD.ROLE_NAME,
spd.HOME_CURRENCY_CODE AS "Currency",
spd.TOTAL_PAY_AMT AS "Period Earning LC",
ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) AS "Period Earning USD",
CASE
WHEN PMC.MULTI_CURRENCY_YN = 'Y' THEN ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2)
ELSE NULL
END AS "MC Period Earning USD",
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.CREDITED_PARTICIPANT_ID,
'PERIOD' AS SOURCE_TYPE
FROM BASE_PAYMENTS_AGGREGATED spd
INNER JOIN PAYRUNS_CACHE PR
ON PR.PAYRUN_ID = spd.PAYRUN_ID
AND PR.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
INNER JOIN PAYSHEETS_CACHE PS
ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = spd.PAYRUN_ID
INNER JOIN PARTICIPANT_DETAILS_CACHE PD
ON PD.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID
LEFT JOIN PERIOD_MULTI_CURRENCY PMC
ON PMC.PAYRUN_ID = spd.PAYRUN_ID
LEFT JOIN CONVERSION_RATES_USD CR
ON CR.FROM_CURRENCY = spd.HOME_CURRENCY_CODE
AND CR.CONVERSION_DATE = TRUNC(spd.PAY_DATE)
AND CR.rn = 1
WHERE spd.SCENARIO_TYPE = 'PERIOD'
AND PS.CREDITED_FLAG = 'Y'
ORDER BY
PAYRUN_NAME,
PERSON_NUMBER,
SOURCE_TYPE,
"Currency";
Comments
Post a Comment