Child quqey -3.0
WITH
-- 1. Get Parent Naming logic in one place to avoid parenthesis nesting errors
PARENT_NAMING AS (
SELECT
pr.PAYRUN_ID,
pn.participant_id,
pn.person_number,
(spd.PAYMENT_BATCH_PERIOD || '-' ||
/* SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2) + 1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1))*/
'REGULAR'
) AS BASE_PARENT_STRING
FROM CN_TP_PAYRUNS_ALL pr
CROSS JOIN PERSON_NUMBERS pn -- This assumes PERSON_NUMBERS CTE exists below
JOIN CN_TP_PARTICIPANT_PAY_EXP_V spd ON spd.PAYRUN_ID = pr.PAYRUN_ID
WHERE pr.PAYRUN_ID = NVL(:payrun_id, pr.PAYRUN_ID)
),
-- [KEEPING YOUR ORIGINAL LOGIC FOR REMAINING CTES]
CONVERSION_RATES_USD AS (
SELECT CR.FROM_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'
),
LATEST_PLAN_COMPONENT AS (
SELECT PARTICIPANT_ID, PAYRUN_ID, PERIOD_ID, PB_INCENTIVE_TYPE
FROM (
SELECT DISTINCT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PB.PAYRUN_ID,
PB.PAY_PERIOD_ID AS PERIOD_ID,
NVL(PB.REPORT_GROUP,'ALL') PB_INCENTIVE_TYPE,
DENSE_RANK() OVER (PARTITION BY PART_PAY.CREDITED_PARTICIPANT_ID, PB.PAY_PERIOD_ID, PB.PAYRUN_ID ORDER BY PART_PAY.SOURCE_EVENT_DATE DESC) AS LATEST_PC_RECORD
FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY
INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID
WHERE PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
) WHERE LATEST_PC_RECORD = 1
),
PERSON_NUMBERS AS (
SELECT DISTINCT cspl.participant_id, papf.person_number
FROM CN_SRP_PARTICIPANTS_ALL cspl
INNER JOIN hz_person_profiles hpp ON cspl.party_id = hpp.party_id
INNER JOIN PER_ALL_PEOPLE_F papf ON papf.person_id = hpp.hr_person_id
),
PARTICIPANT_DETAILS_CACHE AS (
SELECT PARTICIPANT_ID, CURRENCY_CODE, START_DATE, END_DATE FROM CN_SRP_PARTICIPANT_DETAILS_ALL
),
BASE_PAYMENTS_ALL AS (
SELECT
spd.*,
CASE WHEN spd.PAY_PERIOD_ID <> spd.PAYMENT_BATCH_PERIOD THEN 'BB_OLD' ELSE 'BB_CURRENT' END AS PERIOD_TYPE
FROM CN_TP_PARTICIPANT_PAY_EXP_V spd
WHERE spd.PAYRUN_ID = NVL(:payrun_id, spd.PAYRUN_ID)
AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, spd.PAYMENT_BATCH_PERIOD)
),
BB_PTD_AGGREGATES AS (
SELECT
spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE,
SUM(CASE WHEN spd.PERIOD_TYPE = 'BB_OLD' THEN spd.PAY_AMT_PAY_CURR ELSE 0 END) AS BB_TOTAL_EARNINGS,
SUM(CASE WHEN spd.PERIOD_TYPE = 'BB_CURRENT' THEN spd.PAY_AMT_PAY_CURR ELSE 0 END) AS PTD_TOTAL_EARNINGS
FROM BASE_PAYMENTS_ALL spd
GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE
)
-- FINAL OUTPUT BLOCK
SELECT * FROM (
-- BRANCH 1: PRIOR PERIOD (BB MC)
SELECT
spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID AS Period_c, spd.PAYEE_PARTICIPANT_ID,
'BB MC' AS MultiCurrencyScenario_c,
(PN.BASE_PARENT_STRING || '-' || pn.person_number || '-' || DECODE(UPPER(lpc.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.PB_INCENTIVE_TYPE))) AS ParentRecordNumber,
(spd.PAYMENT_BATCH_PERIOD || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS RecordName,
(spd.PAYMENT_BATCH_PERIOD || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS RecordNumber,
spd.HOME_CURRENCY_CODE AS BBHomeCurrency_c,
TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS BBInMultiCurrency_c,
TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS BBInHomeCurrency_c,
TO_CHAR(ROUND((agg.BB_TOTAL_EARNINGS * NVL(cv1.CONVERSION_RATE,1) / NVL(cv2.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS BBInLatestHomeCurrency_c,
pd.CURRENCY_CODE AS BBLatestHomeCurrency_c,
TO_CHAR(ROUND((NVL(cv1.CONVERSION_RATE,1) / NVL(cv2.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS BBConversionFactorHCToLHC_c,
NULL AS PTDInMultiCurrency_c, NULL AS PTDEarningsHomeCurrency_c, NULL AS PTDEarningsInHomeCurrency_c, NULL AS PTDEarningsInLatestHomeCurrency_c, NULL AS PTDEarningsLatestHomeCurrency_c, NULL AS PTDEarningsConversionFactorHCToLHC_c
FROM BASE_PAYMENTS_ALL spd
INNER 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
INNER JOIN PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID
INNER JOIN PARENT_NAMING PN ON PN.PAYRUN_ID = spd.PAYRUN_ID AND PN.participant_id = spd.CREDITED_PARTICIPANT_ID
INNER JOIN LATEST_PLAN_COMPONENT lpc ON lpc.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND lpc.PAYRUN_ID = spd.PAYRUN_ID AND lpc.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD
INNER JOIN PARTICIPANT_DETAILS_CACHE 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)
LEFT JOIN CONVERSION_RATES_USD cv1 ON cv1.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN cv1.CONVERSION_START_DATE AND cv1.CONVERSION_END_DATE
LEFT JOIN CONVERSION_RATES_USD cv2 ON cv2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN cv2.CONVERSION_START_DATE AND cv2.CONVERSION_END_DATE
WHERE spd.PERIOD_TYPE = 'BB_OLD'
UNION ALL
-- BRANCH 2: CURRENT PERIOD (PTD MC)
SELECT
spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID AS Period_c, spd.PAYEE_PARTICIPANT_ID,
'PTD MC' AS MultiCurrencyScenario_c,
(PN.BASE_PARENT_STRING || '-' || pn.person_number || '-' || DECODE(UPPER(lpc.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.PB_INCENTIVE_TYPE))) AS ParentRecordNumber,
(spd.PAYMENT_BATCH_PERIOD || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS RecordName,
(spd.PAYMENT_BATCH_PERIOD || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS RecordNumber,
NULL AS BBHomeCurrency_c, NULL AS BBInMultiCurrency_c, NULL AS BBInHomeCurrency_c, NULL AS BBInLatestHomeCurrency_c, NULL AS BBLatestHomeCurrency_c, NULL AS BBConversionFactorHCToLHC_c,
TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS PTDInMultiCurrency_c,
spd.HOME_CURRENCY_CODE AS PTDEarningsHomeCurrency_c,
TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS PTDEarningsInHomeCurrency_c,
TO_CHAR(ROUND((agg.PTD_TOTAL_EARNINGS * NVL(cv3.CONVERSION_RATE,1) / NVL(cv4.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS PTDEarningsInLatestHomeCurrency_c,
pd.CURRENCY_CODE AS PTDEarningsLatestHomeCurrency_c,
TO_CHAR(ROUND((NVL(cv3.CONVERSION_RATE,1) / NVL(cv4.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS PTDEarningsConversionFactorHCToLHC_c
FROM BASE_PAYMENTS_ALL spd
INNER 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
INNER JOIN PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID
INNER JOIN PARENT_NAMING PN ON PN.PAYRUN_ID = spd.PAYRUN_ID AND PN.participant_id = spd.CREDITED_PARTICIPANT_ID
INNER JOIN LATEST_PLAN_COMPONENT lpc ON lpc.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND lpc.PAYRUN_ID = spd.PAYRUN_ID AND lpc.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD
INNER JOIN PARTICIPANT_DETAILS_CACHE 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)
LEFT JOIN CONVERSION_RATES_USD cv3 ON cv3.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN cv3.CONVERSION_START_DATE AND cv3.CONVERSION_END_DATE
LEFT JOIN CONVERSION_RATES_USD cv4 ON cv4.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN cv4.CONVERSION_START_DATE AND cv4.CONVERSION_END_DATE
WHERE spd.PERIOD_TYPE = 'BB_CURRENT'
) FINAL_RESULTS
GROUP BY
ParentRecordNumber, RecordName, RecordNumber, BBInMultiCurrency_c, BBHomeCurrency_c,
BBInHomeCurrency_c, BBInLatestHomeCurrency_c, BBLatestHomeCurrency_c, BBConversionFactorHCToLHC_c,
PTDInMultiCurrency_c, PTDEarningsHomeCurrency_c, PTDEarningsInHomeCurrency_c,
PTDEarningsInLatestHomeCurrency_c, PTDEarningsLatestHomeCurrency_c, PTDEarningsConversionFactorHCToLHC_c,
PAYRUN_ID, PAYMENT_BATCH_PERIOD, Period_c, PAYEE_PARTICIPANT_ID, MultiCurrencyScenario_c;
Comments
Post a Comment