childqueryfix
WITH
/*
BB_MULTI_CURRENCY AS (
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
-- AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND (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.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
HAVING COUNT(DISTINCT P.HOME_CURRENCY_CODE) > 1
),*/
BB_MULTI_CURRENCY AS (
SELECT
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID,
'Y' AS BBMultiCurrencyFlag
FROM (
SELECT
P.PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
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 = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)
GROUP BY
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
PAYRUN_ID,
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 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 = 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(1) > 1
),
/*
PERIOD_MULTI_CURRENCY AS (
SELECT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PART_PAY.PAYRUN_ID,
PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID
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.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID)
AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD)
GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.PAYRUN_ID, PART_PAY.PAYMENT_BATCH_PERIOD
HAVING COUNT(DISTINCT PD.CURRENCY_CODE) > 1
),*/
PERIOD_MULTI_CURRENCY AS (
SELECT
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID,
'Y' AS MULTI_CURR_YN
FROM (
SELECT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PD.CURRENCY_CODE AS HOME_CURRENCY_CODE,
PART_PAY.PAYRUN_ID,
PART_PAY.ORG_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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL')
AND PART_PAY.PAY_PERIOD_ID = NVL(:period_id, PART_PAY.PAY_PERIOD_ID)
GROUP BY
PART_PAY.CREDITED_PARTICIPANT_ID,
PD.CURRENCY_CODE,
PART_PAY.PAYRUN_ID,
PART_PAY.ORG_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.ORG_ID,
MULTI_CURR.PERIOD_ID
HAVING COUNT(MULTI_CURR.HOME_CURRENCY_CODE) > 1
),
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, INCENTIVE_TYPE_CODE, 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,
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, 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
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)
) WHERE LATEST_PC_RECORD = 1
),
*/
LATEST_PLAN_COMPONENT AS (
SELECT
PARTICIPANT_ID,
PAYRUN_NAME,
ORG_ID,
PAYRUN_ID,
PERIOD_ID,
PLAN_COMPONENT_ID,
PLAN_COMPONENT_name,
INCENTIVE_TYPE,
PB_INCENTIVE_TYPE,
ROLE_NAME,
PAY_GROUP_NAME,
START_DATE,
END_DATE,
INCENTIVE_TYPE_CODE
FROM (
SELECT DISTINCT
PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID,
PB.PAYRUN_NAME,
PART_PAY.ORG_ID,
PB.PAYRUN_ID,
PB.PAY_PERIOD_ID AS PERIOD_ID,
PART_PAY.PLAN_COMPONENT_ID,
PC.INCENTIVE_TYPE,
NVL(PB.REPORT_GROUP,'ALL') PB_INCENTIVE_TYPE,
PC.Plan_component_name,
roles.ROLE_NAME,
PG.PAY_GROUP_NAME,
DECODE(UPPER(PC.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(PC.INCENTIVE_TYPE)) AS INCENTIVE_TYPE_CODE,
TRUNC(SRP_CP.START_DATE) AS START_DATE,
TRUNC(SRP_CP.END_DATE) AS END_DATE,
DENSE_RANK() OVER (
PARTITION BY
PART_PAY.CREDITED_PARTICIPANT_ID,
PART_PAY.ORG_ID,
PB.PAY_PERIOD_ID,
PB.PAYRUN_ID
ORDER BY SRP_CP.START_DATE DESC
) AS LATEST_PC_RECORD
FROM CN_TP_PARTICIPANT_PAY_EXP_V 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
AND PART_PAY.INCENTIVE_TYPE_CODE IN ('COMMISSION', 'BONUS')
AND PART_PAY.EARNING_ID IS NOT NULL
INNER JOIN CN_PAY_GROUPS_ALL_TL PG
ON PB.PAY_GROUP_ID = PG.PAY_GROUP_ID
AND PG.LANGUAGE = 'US'
INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
INNER JOIN CN_SRP_PLAN_COMPONENTS_ALL SRP_PC
ON SRP_PC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
AND SRP_PC.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP
ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID
AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
INNER JOIN CN_PARTICIPANT_ROLES cpr
ON SRP_CP.SRP_RULE_ID = cpr.PARTICIPANT_ROLE_ID
AND SRP_CP.PARTICIPANT_ID = cpr.PARTICIPANT_ID
AND SRP_CP.SRP_RULE_TYPE_CODE = 'ROLE'
INNER JOIN CN_ROLES_TL roles
ON cpr.ROLE_ID = roles.ROLE_ID
AND roles.LANGUAGE = 'US'
WHERE
(PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL')
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
),*/
PERSON_NUMBERS AS (
SELECT
cspl.participant_id,
papf.person_number,
--hpp.PERSON_NAME PARTICIPANT_NAME
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
a.analyst_id,
b.PARTICIPANT_ID,
b.PARTICIPANT_DETAIL_ID,
b.ORG_ID,
b.START_DATE,
b.END_DATE,
b.COUNTRY_CODE,
b.ATTRIBUTE_CHAR40,
b.ATTRIBUTE_NUMBER1,
b.CURRENCY_CODE,
b.ATTRIBUTE_CHAR2,
b.ATTRIBUTE_CHAR17,
b.ATTRIBUTE_CHAR33
FROM CN_SRP_PARTICIPANTS_ALL a
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL b
ON a.participant_id = b.participant_id
WHERE
( b.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND ( b.PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL')
),
PAYRUNS_CACHE AS (
SELECT PAYRUN_ID, PAYRUN_NAME FROM CN_TP_PAYRUNS_ALL P
WHERE (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = '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 WHERE (spd.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL')
AND (spd.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL')
AND (spd.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL')
AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.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
)
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 (
-- 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,
spd.HOME_CURRENCY_CODE AS BBHomeCurrency_c,
(spd.PAYMENT_BATCH_PERIOD || '-' || 'REGULAR' || '-' || pn.person_number || '-' || DECODE(UPPER(lpc.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.PB_INCENTIVE_TYPE))) AS ParentRecordNumber,
(spd.PAYMENT_BATCH_PERIOD || '-' ||'REGULAR' || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS RecordName,
(spd.PAYMENT_BATCH_PERIOD || '-' || 'REGULAR' || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS RecordNumber,
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) * (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) * (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 PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_ID
INNER JOIN PERSON_NUMBERS pn ON 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,
NULL AS BBHomeCurrency_c,
(spd.PAYMENT_BATCH_PERIOD || '-' || 'REGULAR' || '-' || pn.person_number || '-' || DECODE(UPPER(lpc.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.PB_INCENTIVE_TYPE))) AS ParentRecordNumber,
(spd.PAYMENT_BATCH_PERIOD || '-' || 'REGULAR' || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS RecordName,
(spd.PAYMENT_BATCH_PERIOD || '-' || 'REGULAR' || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS RecordNumber,
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) * (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) * (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 PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_ID
INNER JOIN PERSON_NUMBERS pn ON 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_RESULT
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