Child query new
WITH
-- Materialize the multi-currency detection queries once - FILTERED
BB_MULTI_CURRENCY AS (
SELECT
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID
FROM (
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 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)
GROUP BY
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.PAYRUN_ID,
P.HOME_CURRENCY_CODE
) pay_1
INNER JOIN (
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
AND 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)
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
),
PERIOD_MULTI_CURRENCY AS (
SELECT
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.PERIOD_ID
FROM (
SELECT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PD.CURRENCY_CODE AS HOME_CURRENCY_CODE,
PART_PAY.PAYRUN_ID,
PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID,
SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) AS TotalAmt
FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE)
WHERE PART_PAY.SOURCE_EVENT_DATE IS NOT NULL
AND PART_PAY.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID)
AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID)
AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD)
GROUP BY
PART_PAY.CREDITED_PARTICIPANT_ID,
PD.CURRENCY_CODE,
PART_PAY.PAYRUN_ID,
PART_PAY.PAYMENT_BATCH_PERIOD
HAVING SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) <> 0
) MULTI_CURR
GROUP BY
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.PERIOD_ID
HAVING COUNT(DISTINCT MULTI_CURR.HOME_CURRENCY_CODE) > 1
),
-- Materialize conversion rates once
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'
),
-- Materialize INCENTIVE_TYPE lookup once - FILTERED
LATEST_PLAN_COMPONENT AS (
SELECT
PARTICIPANT_ID,
PAYRUN_ID,
PERIOD_ID,
INCENTIVE_TYPE_CODE
FROM (
SELECT DISTINCT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PB.PAYRUN_ID,
PB.PAY_PERIOD_ID AS PERIOD_ID,
DECODE(
UPPER(PC.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(PC.INCENTIVE_TYPE)
) AS INCENTIVE_TYPE_CODE,
DENSE_RANK() OVER (
PARTITION BY
PART_PAY.CREDITED_PARTICIPANT_ID,
PART_PAY.ORG_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
AND PB.ORG_ID = PART_PAY.ORG_ID
AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE
INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
WHERE PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID)
)
WHERE LATEST_PC_RECORD = 1
),
-- Materialize person numbers once - FILTERED
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
WHERE cspl.participant_id = NVL(:participant_id, cspl.participant_id)
),
-- Materialize participant details once - FILTERED
PARTICIPANT_DETAILS_CACHE AS (
SELECT
PARTICIPANT_ID,
CURRENCY_CODE,
START_DATE,
END_DATE
FROM CN_SRP_PARTICIPANT_DETAILS_ALL
WHERE PARTICIPANT_ID = NVL(:participant_id, PARTICIPANT_ID)
),
-- Materialize paysheets once - FILTERED
PAYSHEETS_CACHE AS (
SELECT
PS.PARTICIPANT_ID,
PS.PAYRUN_ID,
PS.PMT_TXN_AMT_DUE
FROM CN_TP_PAYSHEETS_ALL PS
WHERE PS.QUOTA_ID IS NULL
AND PS.PAYRUN_ID = NVL(:payrun_id, PS.PAYRUN_ID)
AND PS.PARTICIPANT_ID = NVL(:participant_id, PS.PARTICIPANT_ID)
),
-- Materialize payruns once - FILTERED
PAYRUNS_CACHE AS (
SELECT
PAYRUN_ID,
PAYRUN_NAME
FROM CN_TP_PAYRUNS_ALL
WHERE PAYRUN_ID = NVL(:payrun_id, PAYRUN_ID)
),
-- Materialize base payment data - COMBINED with scenario flag
BASE_PAYMENTS_ALL AS (
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.CREDITED_PARTICIPANT_ID,
spd.PAY_CURRENCY_CODE,
spd.HOME_CURRENCY_CODE,
spd.PAY_DATE,
spd.PAY_AMT_PAY_CURR,
CASE
WHEN EXISTS (
SELECT 1 FROM BB_MULTI_CURRENCY bb
WHERE bb.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
AND bb.PAYRUN_ID = spd.PAYRUN_ID
AND bb.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
) THEN 'BB'
WHEN EXISTS (
SELECT 1 FROM PERIOD_MULTI_CURRENCY prd
WHERE prd.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
AND prd.PAYRUN_ID = spd.PAYRUN_ID
AND prd.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD
) THEN 'PERIOD'
ELSE NULL
END AS SCENARIO_TYPE,
CASE
WHEN spd.PAY_PERIOD_ID <> spd.PAYMENT_BATCH_PERIOD THEN 'BB_OLD'
WHEN spd.PAY_PERIOD_ID = spd.PAYMENT_BATCH_PERIOD THEN 'BB_CURRENT'
ELSE NULL
END AS PERIOD_TYPE
FROM CN_TP_PARTICIPANT_PAY_EXP_V spd
WHERE 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)
AND (
EXISTS (
SELECT 1 FROM BB_MULTI_CURRENCY bb
WHERE bb.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
AND bb.PAYRUN_ID = spd.PAYRUN_ID
AND bb.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD
)
OR EXISTS (
SELECT 1 FROM PERIOD_MULTI_CURRENCY prd
WHERE prd.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID
AND prd.PAYRUN_ID = spd.PAYRUN_ID
AND prd.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD
)
)
),
-- Aggregate BB and PTD earnings separately for BB MC scenario
BB_PTD_AGGREGATES AS (
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
-- Sum of BB earnings (old periods) - currency agnostic
SUM(CASE WHEN spd.PERIOD_TYPE = 'BB_OLD' THEN spd.PAY_AMT_PAY_CURR ELSE 0 END) AS BB_TOTAL_EARNINGS,
-- Sum of PTD earnings (current period) - currency agnostic
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
WHERE spd.SCENARIO_TYPE = 'BB'
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE
)
SELECT
-- Parent Record Number (with INCENTIVE_TYPE from plan component)
"Parent Record Number" AS "ParentRecordNumber",
-- Multi-Currency Record Name (Child)
"Record Name" AS "RecordName",
-- Multi-Currency Record Number (Child)
"Record Number" AS "RecordNumber",
-- BB in Multi currency (only when BB data exists) - 4 DECIMALS
CASE
WHEN BB_HOME_CURRENCY_CODE IS NOT NULL
THEN TO_CHAR(ROUND("BB MC Earnings", 4), 'FM9999999990.0000')
ELSE NULL
END AS "BBInMultiCurrency_c",
-- BB Home currency
BB_HOME_CURRENCY_CODE AS "BBHomeCurrency_c",
-- BB in Home currency (only when BB data exists) - 4 DECIMALS
CASE
WHEN "BB PTD Earning LC" IS NOT NULL
THEN TO_CHAR(ROUND("BB PTD Earning LC", 4), 'FM9999999990.0000')
ELSE NULL
END AS "BBInHomeCurrency_c",
-- BB in Latest Home Currency - ALREADY FORMATTED IN SUBQUERY
"BB PTD Earning (Latest HC)" AS "BBInLatestHomeCurrency_c",
-- BB Latest Home currency
CASE
WHEN BB_HOME_CURRENCY_CODE IS NOT NULL
THEN latest_currency_code
ELSE NULL
END AS "BBLatestHomeCurrency_c",
-- BB Conversion factor(HC to LHC) - ALREADY FORMATTED IN SUBQUERY
"BB Conversion Rate (HC_to_Latest_HC)" AS "BBConversionFactorHCToLHC_c",
-- PTD in Multicurrency (only when PTD data exists) - 4 DECIMALS
CASE
WHEN PTD_HOME_CURRENCY_CODE IS NOT NULL
THEN TO_CHAR(ROUND("PTD MC Earnings", 4), 'FM9999999990.0000')
ELSE NULL
END AS "PTDInMulticurrency_c",
-- PTD Earnings Home currency
PTD_HOME_CURRENCY_CODE AS "PTDEarningsHomeCurrency_c",
-- PTD Earnings in Home currency (only when PTD data exists) - 4 DECIMALS
CASE
WHEN "PTD Earning LC" IS NOT NULL
THEN TO_CHAR(ROUND("PTD Earning LC", 4), 'FM9999999990.0000')
ELSE NULL
END AS "PTDEarningsInHomeCurrency_c",
-- PTD Earnings in Latest Home Currency - ALREADY FORMATTED IN SUBQUERY
"PTD Earning (Latest HC)" AS "PTDEarningsInLatestHomeCurrency_c",
-- PTD Earnings Latest Home currency
CASE
WHEN PTD_HOME_CURRENCY_CODE IS NOT NULL
THEN latest_currency_code
ELSE NULL
END AS "PTDEarningsLatestHomeCurrency_c",
-- PTD Earnings Conversion factor(HC to LHC) - ALREADY FORMATTED IN SUBQUERY
"Conversion Rate (HC_to_Latest_HC)" AS "PTDEarningsConversionFactorHCToLHC_c",
-- Additional columns for reference
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAY_PERIOD_ID AS "Period_c",
PAYEE_PARTICIPANT_ID,
MC_SCENARIO_TYPE AS "MultiCurrencyScenario_c"
FROM (
-- Beginning Balance Multi-Currency Query - OLD PERIODS (BB Columns)
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE AS latest_currency_code,
'BB MC' AS MC_SCENARIO_TYPE,
spd.HOME_CURRENCY_CODE AS BB_HOME_CURRENCY_CODE,
NULL AS PTD_HOME_CURRENCY_CODE,
-- Parent Record Number (with INCENTIVE_TYPE)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-' || lpc.INCENTIVE_TYPE_CODE
) AS "Parent Record Number",
-- Record Name (with home currency) - BBMC for all BB scenario records
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-BBMC-' || spd.HOME_CURRENCY_CODE
) AS "Record Name",
-- Record Number (with home currency)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-BBMC-' || spd.HOME_CURRENCY_CODE
) AS "Record Number",
-- BB columns populated
SUM(spd.PAY_AMT_PAY_CURR) AS "BB PTD Earning LC",
TO_CHAR(
ROUND(
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
),
4
),
'FM9999999990.0000'
) AS "BB PTD Earning (Latest HC)",
-- COMBINED CONVERSION RATE: HC -> USD -> Latest HC - 4 DECIMALS
TO_CHAR(
ROUND(
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
),
4
),
'FM9999999990.0000'
) AS "BB Conversion Rate (HC_to_Latest_HC)",
-- PTD columns NULL
NULL AS "PTD Earning LC",
NULL AS "PTD Earning (Latest HC)",
NULL AS "Conversion Rate (HC_to_Latest_HC)",
-- BB MC Earnings - sum of BB earnings for this currency
MAX(agg.BB_TOTAL_EARNINGS) AS "BB MC Earnings",
-- PTD MC Earnings - NULL for BB_OLD
NULL AS "PTD MC Earnings"
FROM BASE_PAYMENTS_ALL spd
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)
INNER JOIN PAYRUNS_CACHE pr
ON pr.PAYRUN_ID = spd.PAYRUN_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 PERSON_NUMBERS pn
ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID
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
LEFT JOIN CONVERSION_RATES_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 CONVERSION_RATES_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 spd.SCENARIO_TYPE = 'BB'
AND spd.PERIOD_TYPE = 'BB_OLD'
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE,
pr.PAYRUN_NAME,
pn.person_number,
lpc.INCENTIVE_TYPE_CODE
UNION ALL
-- Beginning Balance Multi-Currency Query - CURRENT PERIOD (PTD Columns but BBMC suffix)
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE AS latest_currency_code,
'BB MC' AS MC_SCENARIO_TYPE,
NULL AS BB_HOME_CURRENCY_CODE,
spd.HOME_CURRENCY_CODE AS PTD_HOME_CURRENCY_CODE,
-- Parent Record Number (with INCENTIVE_TYPE)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-' || lpc.INCENTIVE_TYPE_CODE
) AS "Parent Record Number",
-- Record Name (with home currency) - STILL USE BBMC because it's BB scenario
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-BBMC-' || spd.HOME_CURRENCY_CODE
) AS "Record Name",
-- Record Number (with home currency)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-BBMC-' || spd.HOME_CURRENCY_CODE
) AS "Record Number",
-- BB columns NULL
NULL AS "BB PTD Earning LC",
NULL AS "BB PTD Earning (Latest HC)",
NULL AS "BB Conversion Rate (HC_to_Latest_HC)",
-- PTD columns populated (because it's current period data)
SUM(spd.PAY_AMT_PAY_CURR) AS "PTD Earning LC",
TO_CHAR(
ROUND(
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
),
4
),
'FM9999999990.0000'
) AS "PTD Earning (Latest HC)",
-- COMBINED CONVERSION RATE: HC -> USD -> Latest HC - 4 DECIMALS
TO_CHAR(
ROUND(
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
),
4
),
'FM9999999990.0000'
) AS "Conversion Rate (HC_to_Latest_HC)",
-- BB MC Earnings - NULL for BB_CURRENT
NULL AS "BB MC Earnings",
-- PTD MC Earnings - sum of PTD earnings for this currency
MAX(agg.PTD_TOTAL_EARNINGS) AS "PTD MC Earnings"
FROM BASE_PAYMENTS_ALL spd
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)
INNER JOIN PAYRUNS_CACHE pr
ON pr.PAYRUN_ID = spd.PAYRUN_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 PERSON_NUMBERS pn
ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID
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
LEFT JOIN CONVERSION_RATES_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 CONVERSION_RATES_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 spd.SCENARIO_TYPE = 'BB'
AND spd.PERIOD_TYPE = 'BB_CURRENT'
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE,
pr.PAYRUN_NAME,
pn.person_number,
lpc.INCENTIVE_TYPE_CODE
UNION ALL
-- Period Multi-Currency Query
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE AS latest_currency_code,
'PTD MC' AS MC_SCENARIO_TYPE,
NULL AS BB_HOME_CURRENCY_CODE,
spd.HOME_CURRENCY_CODE AS PTD_HOME_CURRENCY_CODE,
-- Parent Record Number (with INCENTIVE_TYPE)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-' || lpc.INCENTIVE_TYPE_CODE
) AS "Parent Record Number",
-- Record Name (with home currency)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-PDMC-' || spd.HOME_CURRENCY_CODE
) AS "Record Name",
-- Record Number (with home currency)
TO_CHAR(
spd.PAYMENT_BATCH_PERIOD
|| '-' ||
SUBSTR(
pr.PAYRUN_NAME,
1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2),
(INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || pn.person_number
|| '-PDMC-' || spd.HOME_CURRENCY_CODE
) AS "Record Number",
NULL AS "BB PTD Earning LC",
NULL AS "BB PTD Earning (Latest HC)",
NULL AS "BB Conversion Rate (HC_to_Latest_HC)",
SUM(spd.PAY_AMT_PAY_CURR) AS "PTD Earning LC",
TO_CHAR(
ROUND(
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
),
4
),
'FM9999999990.0000'
) AS "PTD Earning (Latest HC)",
-- COMBINED CONVERSION RATE: HC -> USD -> Latest HC - 4 DECIMALS
TO_CHAR(
ROUND(
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
),
4
),
'FM9999999990.0000'
) AS "Conversion Rate (HC_to_Latest_HC)",
-- BB MC Earnings - NULL for PTD MC scenario
NULL AS "BB MC Earnings",
-- PTD MC Earnings - sum of all PTD earnings for this currency (from paysheets)
MAX(PS.PMT_TXN_AMT_DUE) AS "PTD MC Earnings"
FROM BASE_PAYMENTS_ALL spd
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)
INNER JOIN PAYSHEETS_CACHE PS
ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = spd.PAYRUN_ID
INNER JOIN PAYRUNS_CACHE pr
ON pr.PAYRUN_ID = spd.PAYRUN_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 PERSON_NUMBERS pn
ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID
LEFT JOIN CONVERSION_RATES_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 CONVERSION_RATES_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 spd.SCENARIO_TYPE = 'PERIOD'
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAY_PERIOD_ID,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE,
pr.PAYRUN_NAME,
pn.person_number,
lpc.INCENTIVE_TYPE_CODE
) FINAL_RESULT
WHERE NOT (
NVL("PTD Earning LC", 0) = 0
AND NVL(TO_NUMBER(REPLACE("PTD Earning (Latest HC)", ',', '')), 0) = 0
AND NVL("BB PTD Earning LC", 0) = 0
AND NVL(TO_NUMBER(REPLACE("BB PTD Earning (Latest HC)", ',', '')), 0) = 0
)
Comments
Post a Comment