childqueryenhanced
WITH
/* =========================
KEEP ORIGINAL FLAG LOGIC
========================= */
BB_MULTI_CURRENCY AS (
-- (same as in your original child query)
),
PERIOD_MULTI_CURRENCY AS (
-- (same as in your original child query)
),
MULTI_CURRENCY_FLAGS AS (
-- (same as in your original child query)
),
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'
),
PERSON_NUMBERS AS (
SELECT
cspl.participant_id,
papf.person_number,
cspl.participant_name
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 IN (:participant_id) OR COALESCE(:participant_id,'ALL')='ALL')
AND (cspl.participant_id IN (:badge) OR COALESCE(:badge,'ALL')='ALL')
),
PARTICIPANT_DETAILS_CACHE AS (
SELECT
b.PARTICIPANT_ID,
b.START_DATE,
b.END_DATE,
b.CURRENCY_CODE
FROM CN_SRP_PARTICIPANT_DETAILS_ALL b
WHERE (b.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL')='ALL')
AND (b.PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL')='ALL')
),
LATEST_PLAN_COMPONENT AS (
-- keep the same logic from your original child query
),
/* ======================================
BASE PAYMENTS (FILTERS SAME AS PARENT)
====================================== */
BASE_PAYMENTS_ALL AS (
SELECT
P.*,
CASE
WHEN P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
THEN 'BB_OLD'
ELSE 'BB_CURRENT'
END PERIOD_TYPE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
INNER JOIN MULTI_CURRENCY_FLAGS F
ON P.PAYRUN_ID = F.PAYRUN_ID
AND P.PAYEE_PARTICIPANT_ID = F.PARTICIPANT_ID
AND P.PAYMENT_BATCH_PERIOD = F.PERIOD_ID
WHERE (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL')='ALL')
AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL')='ALL')
AND (P.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL')='ALL')
AND (P.PAYMENT_BATCH_PERIOD IN (:period_id) OR COALESCE(:period_id,'ALL')='ALL')
),
/* ======================================
AGGREGATE PER CURRENCY
====================================== */
BB_PTD_AGGREGATES AS (
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
HOME_CURRENCY_CODE,
MAX(PAY_DATE) PAY_DATE,
SUM(
CASE
WHEN PERIOD_TYPE='BB_OLD'
THEN PAY_AMT_PAY_CURR
ELSE 0
END
) BB_TOTAL_EARNINGS,
SUM(
CASE
WHEN PERIOD_TYPE='BB_CURRENT'
THEN PAY_AMT_PAY_CURR
ELSE 0
END
) PTD_TOTAL_EARNINGS
FROM BASE_PAYMENTS_ALL
GROUP BY
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
HOME_CURRENCY_CODE
)
/* ======================================
FINAL RESULT
====================================== */
SELECT
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
FROM (
/* ======================
BB MULTI CURRENCY
====================== */
SELECT
agg.PAYRUN_ID,
agg.PAYMENT_BATCH_PERIOD,
agg.PAYMENT_BATCH_PERIOD Period_c,
agg.PAYEE_PARTICIPANT_ID,
'BB MC' MultiCurrencyScenario_c,
agg.HOME_CURRENCY_CODE BBHomeCurrency_c,
(agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number) ParentRecordNumber,
(agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-BBMC-'||agg.HOME_CURRENCY_CODE) RecordName,
(agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-BBMC-'||agg.HOME_CURRENCY_CODE) RecordNumber,
TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS,4),'FM9999999990.0000') BBInMultiCurrency_c,
TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS,4),'FM9999999990.0000') BBInHomeCurrency_c,
TO_CHAR(
ROUND(
agg.BB_TOTAL_EARNINGS
* NVL(cv1.CONVERSION_RATE,1)
* (1/NVL(cv2.CONVERSION_RATE,1))
,4),'FM9999999990.0000') BBInLatestHomeCurrency_c,
pd.CURRENCY_CODE BBLatestHomeCurrency_c,
TO_CHAR(
ROUND(
NVL(cv1.CONVERSION_RATE,1)
* (1/NVL(cv2.CONVERSION_RATE,1))
,4),'FM9999999990.0000') BBConversionFactorHCToLHC_c,
NULL,NULL,NULL,NULL,NULL,NULL
FROM BB_PTD_AGGREGATES agg
INNER JOIN PERSON_NUMBERS pn
ON pn.participant_id = agg.PAYEE_PARTICIPANT_ID
INNER JOIN PARTICIPANT_DETAILS_CACHE pd
ON pd.PARTICIPANT_ID = agg.PAYEE_PARTICIPANT_ID
AND agg.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE,agg.PAY_DATE)
LEFT JOIN CONVERSION_RATES_USD cv1
ON cv1.FROM_CURRENCY = agg.HOME_CURRENCY_CODE
AND TRUNC(agg.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(agg.PAY_DATE) BETWEEN cv2.CONVERSION_START_DATE AND cv2.CONVERSION_END_DATE
WHERE agg.BB_TOTAL_EARNINGS <> 0
UNION ALL
/* ======================
PTD MULTI CURRENCY
====================== */
SELECT
agg.PAYRUN_ID,
agg.PAYMENT_BATCH_PERIOD,
agg.PAYMENT_BATCH_PERIOD,
agg.PAYEE_PARTICIPANT_ID,
'PTD MC',
NULL,
(agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number),
(agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-PDMC-'||agg.HOME_CURRENCY_CODE),
(agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-PDMC-'||agg.HOME_CURRENCY_CODE),
NULL,NULL,NULL,NULL,NULL,NULL,
TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS,4),'FM9999999990.0000'),
agg.HOME_CURRENCY_CODE,
TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS,4),'FM9999999990.0000'),
TO_CHAR(
ROUND(
agg.PTD_TOTAL_EARNINGS
* NVL(cv1.CONVERSION_RATE,1)
* (1/NVL(cv2.CONVERSION_RATE,1))
,4),'FM9999999990.0000'),
pd.CURRENCY_CODE,
TO_CHAR(
ROUND(
NVL(cv1.CONVERSION_RATE,1)
* (1/NVL(cv2.CONVERSION_RATE,1))
,4),'FM9999999990.0000')
FROM BB_PTD_AGGREGATES agg
INNER JOIN PERSON_NUMBERS pn
ON pn.participant_id = agg.PAYEE_PARTICIPANT_ID
INNER JOIN PARTICIPANT_DETAILS_CACHE pd
ON pd.PARTICIPANT_ID = agg.PAYEE_PARTICIPANT_ID
AND agg.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE,agg.PAY_DATE)
LEFT JOIN CONVERSION_RATES_USD cv1
ON cv1.FROM_CURRENCY = agg.HOME_CURRENCY_CODE
AND TRUNC(agg.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(agg.PAY_DATE) BETWEEN cv2.CONVERSION_START_DATE AND cv2.CONVERSION_END_DATE
WHERE agg.PTD_TOTAL_EARNINGS <> 0
)
Comments
Post a Comment