MC fix
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
PAY_CURRENCY_CODE,
latest_currency_code,
-- Multi-currency scenario type
'Period' AS MC_SCENARIO_TYPE,
-- Home currency (kept separate)
NULL AS BB_HOME_CURRENCY_CODE,
HOME_CURRENCY_CODE AS PTD_HOME_CURRENCY_CODE,
-- Parent Record Name and Number (without home currency)
"Parent Record Name",
"Parent Record Number",
-- Record Name and Record Number (with home currency)
"Record Name",
"Record Number",
-- Beginning Balance columns (NULL for Period records)
NULL AS "BB PTD Earning LC",
NULL AS "BB Conversion Rate (LC_to_USD)",
NULL AS "BB PTD Earning (USD)",
NULL AS "BB Conversion Rate (USD_to_Latest_LC)",
NULL AS "BB PTD Earning (Latest HC)",
-- Period columns
"PTD Earning LC",
"Conversion Rate (LC_to_USD)",
"PTD Earning (USD)",
"Conversion Rate (USD_to_Latest_LC)",
"PTD Earning (Latest HC)",
-- Single MC Earnings column
"MC Earnings"
FROM (
-- Period Multi-Currency Query
SELECT
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE AS latest_currency_code,
-- Parent Record Name (without 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)
)
|| '-' ||
(
SELECT 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
)
|| '-' ||
DECODE(
UPPER(pc.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(pc.INCENTIVE_TYPE)
)
) AS "Parent Record Name",
-- Parent Record Number (same as Parent Record Name)
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)
)
|| '-' ||
(
SELECT 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
)
|| '-' ||
DECODE(
UPPER(pc.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(pc.INCENTIVE_TYPE)
)
) 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)
)
|| '-' ||
(
SELECT 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
)
|| '-' ||
DECODE(
UPPER(pc.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(pc.INCENTIVE_TYPE)
)
|| '-' ||
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)
)
|| '-' ||
(
SELECT 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
)
|| '-' ||
DECODE(
UPPER(pc.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(pc.INCENTIVE_TYPE)
)
|| '-' ||
spd.HOME_CURRENCY_CODE
) AS "Record Number",
SUM(spd.PAY_AMT_PAY_CURR) AS "PTD Earning LC",
NVL(
MAX(
CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END
),
1
) AS "Conversion Rate (LC_to_USD)",
SUM(spd.PAY_AMT_PAY_CURR) * MAX(
CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END
) AS "PTD Earning (USD)",
TO_CHAR(
MAX(
CASE
WHEN pd.CURRENCY_CODE = 'USD' THEN 1
ELSE 1 / conversion2.CONVERSION_RATE
END
),
'0000000000000.0000000000000'
) AS "Conversion Rate (USD_to_Latest_LC)",
TO_CHAR(
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
),
'0000000000000.0000000000000'
) AS "PTD Earning (Latest HC)",
MAX(PS.PMT_TXN_AMT_DUE) AS "MC Earnings"
FROM CN_TP_PARTICIPANT_PAY_EXP_V spd
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL 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 CN_TP_PAYSHEETS_ALL PS
ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = spd.PAYRUN_ID
AND PS.QUOTA_ID IS NULL
-- Join to get PAYRUN_NAME
INNER JOIN CN_TP_PAYRUNS_ALL pr
ON pr.PAYRUN_ID = spd.PAYRUN_ID
-- Join to get INCENTIVE_TYPE (latest plan component)
LEFT JOIN (
SELECT
pca.PARTICIPANT_ID,
pca.COMP_PLAN_ID,
pc.INCENTIVE_TYPE,
ROW_NUMBER() OVER (PARTITION BY pca.PARTICIPANT_ID ORDER BY pca.START_DATE DESC) AS rn
FROM CN_SRP_PLAN_ASSIGNS_ALL pca
INNER JOIN CN_COMP_PLANS_ALL pc
ON pca.COMP_PLAN_ID = pc.COMP_PLAN_ID
) pc
ON pc.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID
AND pc.rn = 1
LEFT JOIN (
SELECT
CR.FROM_CURRENCY,
CR.TO_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'
) 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 (
SELECT
CR.FROM_CURRENCY,
CR.TO_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'
) 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 1 = 1
AND 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)
GROUP BY
spd.PAYRUN_ID,
spd.PAYMENT_BATCH_PERIOD,
spd.PAYEE_PARTICIPANT_ID,
spd.HOME_CURRENCY_CODE,
spd.PAY_CURRENCY_CODE,
pd.CURRENCY_CODE,
pr.PAYRUN_NAME,
pc.INCENTIVE_TYPE,
spd.CREDITED_PARTICIPANT_ID
) MCChild_PTD
WHERE EXISTS (
SELECT 1
FROM (
SELECT
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID,
CASE
WHEN COUNT(DISTINCT MULTI_CURR.HOME_CURRENCY_CODE) > 1
THEN 'Y'
ELSE 'N'
END 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 IS NOT NULL
AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE)
WHERE 1 = 1
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
) PRD_ML
WHERE PRD_ML.MULTI_CURR_YN = 'Y'
AND MCChild_PTD.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID
AND MCChild_PTD.PAYRUN_ID = PRD_ML.PAYRUN_ID
AND MCChild_PTD.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID
Comments
Post a Comment