fixed big query
/*
Multi-Currency Paysheet Report - OPTIMIZED WITH FILTERS
Aggregates payment data with currency conversions (Home <-> USD)
Filters: :payrun_id, :participant_id, :period_id (all optional)
*/
WITH
-- Materialize conversion rates once
CONVERSION_RATES_USD AS (
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'
),
-- Materialize person numbers once - FILTERED
PERSON_NUMBERS AS (
SELECT
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
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
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 = NVL(:participant_id, b.PARTICIPANT_ID)
),
-- Materialize multi-currency detection - FILTERED
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 = 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,
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(1) > 1
),
-- FILTERED
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 = 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.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
),
MULTI_CURRENCY_FLAGS AS (
SELECT
NVL(bb_MultiC.PAYRUN_ID, PRD_ML.PAYRUN_ID) AS PAYRUN_ID,
NVL(bb_MultiC.PAYMENT_BATCH_PERIOD, PRD_ML.PERIOD_ID) AS PERIOD_ID,
NVL(bb_MultiC.PAYEE_PARTICIPANT_ID, PRD_ML.PARTICIPANT_ID) AS PARTICIPANT_ID,
NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag,
NVL(PRD_ML.MULTI_CURR_YN, 'N') AS MULTI_CURR_YN
FROM BB_MULTI_CURRENCY bb_MultiC
FULL OUTER JOIN PERIOD_MULTI_CURRENCY PRD_ML
ON bb_MultiC.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID
AND bb_MultiC.PAYRUN_ID = PRD_ML.PAYRUN_ID
AND bb_MultiC.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID
WHERE NVL(PRD_ML.MULTI_CURR_YN, 'N') = 'Y'
OR NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') = 'Y'
),
-- Materialize base payment data - FILTERED
BASE_PAYMENTS AS (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAYRUN_NAME,
PB.PAY_PERIOD_ID,
PB.PAY_DATE,
PG.PAY_GROUP_ID,
PG.PAY_GROUP_NAME,
P.PLAN_COMPONENT_ID,
PC.INCENTIVE_TYPE,
PS.PAYSHEET_ID,
PS.BONUS_PTD,
PS.PMT_TXN_AMT_DUE,
(
SELECT meaning
FROM cn_lookups
WHERE lookup_type = 'CN_PAYSHEET_STATUS'
AND lookup_code = PS.OBJECT_STATUS
AND enabled_flag = 'Y'
AND lookup_code != 'REVIEWED'
) AS PAYSHEET_STATUS,
P.PAY_AMT_PAY_CURR,
P.HOME_CURRENCY_CODE,
P.PAY_CURRENCY_CODE,
P.SOURCE_EVENT_DATE
FROM CN_TP_PARTICIPANT_PAY_ALL P
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
INNER JOIN CN_PAY_GROUPS_ALL_VL PG
ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID
AND PG.ORG_ID = P.ORG_ID
INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
INNER JOIN CN_TP_PAYSHEETS_ALL PS
ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID
AND PS.PAYRUN_ID = P.PAYRUN_ID
AND PS.QUOTA_ID IS NULL
WHERE (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND PB.PAY_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PB.PAY_DATE))
)
AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
),
-- Materialize participant detail for date range - FILTERED
PARTICIPANT_DETAIL_DATERANGE AS (
SELECT
PART_DTLS.PARTICIPANT_ID,
PART_HDR.HR_PRIMARY_WORKER_NUMBER,
PART_DTLS.PARTICIPANT_DETAIL_ID,
PART_DTLS.ORG_ID AS PD_ORG_ID,
PART_DTLS.START_DATE AS PD_START_DATE,
PART_DTLS.END_DATE AS PD_END_DATE,
PART_DTLS.COUNTRY_CODE AS HR_Country,
PART_DTLS.COUNTRY_CODE AS Employment_Country,
(
SELECT PARTY_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID
AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID
) AS ANALYST_PARTY_ID,
PART_DTLS.ATTRIBUTE_NUMBER1 AS PD_HR_TI,
PART_DTLS.CURRENCY_CODE AS LATEST_HOME_CURRENCY,
PART_DTLS.ATTRIBUTE_CHAR2 AS HR_Status,
PART_DTLS.ATTRIBUTE_CHAR17 AS SalesRep
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS
INNER JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR
ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID
AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID
WHERE PART_DTLS.PARTICIPANT_ID = NVL(:participant_id, PART_DTLS.PARTICIPANT_ID)
),
-- Aggregate to USD
PAYMENTS_USD AS (
SELECT
SPD.ORG_ID,
SPD.PAYRUN_ID,
SPD.CREDITED_PARTICIPANT_ID,
SPD.PAYRUN_NAME,
SPD.PAY_PERIOD_ID,
SPD.PAY_DATE,
SPD.PAY_GROUP_ID,
SPD.PAY_GROUP_NAME,
SPD.PAYSHEET_ID,
SPD.PAYSHEET_STATUS,
SUM(
SPD.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS PAY_AMT_PAY_CURR_USD,
MAX(
SPD.PMT_TXN_AMT_DUE *
NVL(
(CASE
WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS paysheet_payout_usd,
MAX(SPD.PMT_TXN_AMT_DUE) AS paysheet_payout_LC,
MAX(partipant_detail.LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY
FROM BASE_PAYMENTS SPD
INNER JOIN CONVERSION_RATES_USD conversion
ON TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE
AND conversion.CONVERSION_END_DATE
AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE
LEFT JOIN PARTICIPANT_DETAIL_DATERANGE partipant_detail
ON TRUNC(SPD.PAY_DATE) BETWEEN partipant_detail.PD_START_DATE
AND NVL(partipant_detail.PD_END_DATE, DATE '9999-12-31')
AND SPD.CREDITED_PARTICIPANT_ID = partipant_detail.PARTICIPANT_ID
AND SPD.ORG_ID = partipant_detail.PD_ORG_ID
AND SPD.HOME_CURRENCY_CODE = partipant_detail.LATEST_HOME_CURRENCY
GROUP BY
SPD.ORG_ID,
SPD.PAYRUN_ID,
SPD.CREDITED_PARTICIPANT_ID,
SPD.PAYRUN_NAME,
SPD.PAY_PERIOD_ID,
SPD.PAY_DATE,
SPD.PAY_GROUP_ID,
SPD.PAY_GROUP_NAME,
SPD.PAYSHEET_ID,
SPD.PAYSHEET_STATUS
),
-- Convert back to local currency
BG AS (
SELECT
after_group.ORG_ID,
after_group.PAYRUN_ID,
after_group.CREDITED_PARTICIPANT_ID,
after_group.PAYRUN_NAME,
after_group.PAY_PERIOD_ID,
after_group.PAY_DATE,
after_group.PAY_GROUP_ID,
after_group.PAY_GROUP_NAME,
after_group.PAYSHEET_ID,
after_group.PAY_AMT_PAY_CURR_USD,
after_group.paysheet_payout_LC,
after_group.LATEST_HOME_CURRENCY,
after_group.PAYSHEET_STATUS,
TO_CHAR(
1 / (
NVL(
(CASE
WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
),
'FM9999999990.0000'
) AS conversionrate,
TO_CHAR(
after_group.PAY_AMT_PAY_CURR_USD * (
1 / (
NVL(
(CASE
WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS finalPayout,
TO_CHAR(
after_group.paysheet_payout_usd * (
1 / (
NVL(
(CASE
WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS finalPayout_paysheet
FROM PAYMENTS_USD after_group
INNER JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(after_group.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = after_group.LATEST_HOME_CURRENCY
),
-- Materialize latest plan component - FILTERED
LATEST_PLAN_COMPONENT AS (
SELECT
PARTICIPANT_ID,
PAYRUN_NAME,
ORG_ID,
PAYRUN_ID,
PERIOD_ID,
PLAN_COMPONENT_ID,
PLAN_COMPONENT_name,
INCENTIVE_TYPE,
ROLE_NAME,
PAY_GROUP_NAME,
START_DATE,
END_DATE
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,
PC.Plan_component_name,
roles.ROLE_NAME,
PG.PAY_GROUP_NAME,
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 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
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 = 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 calendar data
PAY_CAL AS (
SELECT
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) AS PAYMENT_BATCH_NAME,
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) AS PAYGROUP_NAME,
PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 AS PERIOD_ID,
PAYRUN_CAL.CURRENCY_CODE,
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR011) AS INCENTIVE_TYPE,
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR012) AS COUNTRY,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP009) AS MPA_CUTOFF_START_DT,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010) AS MPA_CUTOFF_END_DT,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP007) AS PAYSHEET_CUTOFF_START_DT,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP012) AS PAYROLL_SUBMISSION_END_CST,
TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008) AS PAYSHEET_CUTOFF_DT
FROM ZCA_REF_ENTITIES PAYRUN_CAL
WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c'
AND (:period_id IS NULL OR PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 = :period_id)
),
--BB
-- Step 1: Convert BB to USD (grouped by HOME_CURRENCY_CODE across all old periods)
BB_AMT_USD AS (
SELECT
P.PAYRUN_ID,
P.PAYEE_PARTICIPANT_ID AS CREDITED_PARTICIPANT_ID,
P.PAYMENT_BATCH_PERIOD AS PAY_PERIOD_ID,
P.HOME_CURRENCY_CODE,
SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR,
SUM(
P.PAY_AMT_PAY_CURR *
CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE NVL(CR_USD.CONVERSION_RATE, 1)
END
) AS BB_AMOUNT_USD,
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY,
MAX(P.PAY_DATE) AS PAY_DATE,
LISTAGG(DISTINCT P.PAY_PERIOD_ID, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS OLD_PAY_PERIOD_IDS
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
INNER JOIN PARTICIPANT_DETAILS_CACHE PD
ON P.PAYEE_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.PAY_DATE)
LEFT JOIN CONVERSION_RATES_USD CR_USD
ON P.HOME_CURRENCY_CODE != 'USD'
AND CR_USD.FROM_CURRENCY = P.HOME_CURRENCY_CODE
AND TRUNC(P.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE
AND CR_USD.CONVERSION_END_DATE
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)
AND P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
AND EXISTS (
SELECT 1 FROM BB_MULTI_CURRENCY bb
WHERE bb.PAYEE_PARTICIPANT_ID = P.PAYEE_PARTICIPANT_ID
AND bb.PAYRUN_ID = P.PAYRUN_ID
AND bb.PAYMENT_BATCH_PERIOD = P.PAYMENT_BATCH_PERIOD
)
GROUP BY
P.PAYRUN_ID,
P.PAYEE_PARTICIPANT_ID,
P.PAYMENT_BATCH_PERIOD,
P.HOME_CURRENCY_CODE
),
-- Step 2: Convert each currency's BB from USD to Latest Home Currency
BB_AMT_BY_CURRENCY AS (
SELECT
BB.PAYRUN_ID,
BB.CREDITED_PARTICIPANT_ID,
BB.PAY_PERIOD_ID,
BB.HOME_CURRENCY_CODE,
BB.BB_AMOUNT_HOME_CURR,
BB.BB_AMOUNT_USD,
BB.LATEST_HOME_CURRENCY,
BB.OLD_PAY_PERIOD_IDS,
BB.BB_AMOUNT_USD *
CASE
WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1)
END AS BB_IN_LATEST_HC_NUM,
CASE
WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1)
END AS CONVERSION_RATE_USD_TO_LC
FROM BB_AMT_USD BB
LEFT JOIN CONVERSION_RATES_USD CR_LC
ON BB.LATEST_HOME_CURRENCY != 'USD'
AND CR_LC.FROM_CURRENCY = BB.LATEST_HOME_CURRENCY
AND TRUNC(BB.PAY_DATE) BETWEEN CR_LC.CONVERSION_START_DATE
AND CR_LC.CONVERSION_END_DATE
),
-- Step 3: Aggregate all currencies into single row per participant
BB_AMT AS (
SELECT
PAYRUN_ID,
CREDITED_PARTICIPANT_ID,
PAY_PERIOD_ID,
LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES,
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'),
', '
) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES_WITH_AMOUNTS,
MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY,
MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS,
SUM(BB_AMOUNT_USD) AS BB_AMOUNT_USD,
TO_CHAR(
SUM(BB_AMOUNT_USD),
'FM9999999990.0000'
) AS BB_IN_USD,
SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM,
TO_CHAR(
SUM(BB_IN_LATEST_HC_NUM),
'FM9999999990.0000'
) AS BB_IN_LATEST_HC,
TO_CHAR(
AVG(CONVERSION_RATE_USD_TO_LC),
'FM9999999990.0000'
) AS BB_CONVERSION_RATE_AVG,
SUM(BB_AMOUNT_HOME_CURR) AS BB_TOTAL_HOME_CURR_SUM,
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'),
' | '
) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_BREAKDOWN_BY_CURRENCY
FROM BB_AMT_BY_CURRENCY
GROUP BY
PAYRUN_ID,
CREDITED_PARTICIPANT_ID,
PAY_PERIOD_ID
),
-- PTD USD
PTD_EARNINGS_USD AS (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID,
SUM(
P.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS PTD_EARNINGS_USD,
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY
FROM CN_TP_PARTICIPANT_PAY_ALL P
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
INNER JOIN CONVERSION_RATES_USD conversion
ON TRUNC(
CASE
WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE
ELSE PB.PAY_DATE
END
) BETWEEN conversion.CONVERSION_START_DATE
AND conversion.CONVERSION_END_DATE
AND conversion.FROM_CURRENCY = P.HOME_CURRENCY_CODE
WHERE (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND PB.PAY_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PB.PAY_DATE))
)
AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID
AND P.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN')
AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
GROUP BY
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
--PTD earnings MC
PTD_EARNINGS_AMT AS (
SELECT
ptd_usd.PAYRUN_ID,
ptd_usd.CREDITED_PARTICIPANT_ID,
ptd_usd.ORG_ID,
ptd_usd.PAY_PERIOD_ID,
ptd_usd.PTD_EARNINGS_USD,
ptd_usd.LATEST_HOME_CURRENCY,
TO_CHAR(
ptd_usd.PTD_EARNINGS_USD * (
1 / (
NVL(
(CASE
WHEN ptd_usd.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'FM9999999990.0000'
) AS PTD_EARNINGS_LC
FROM PTD_EARNINGS_USD ptd_usd
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = ptd_usd.PAYRUN_ID
AND PB.ORG_ID = ptd_usd.ORG_ID
INNER JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = ptd_usd.LATEST_HOME_CURRENCY
),
-- Materialize prorated TI - FILTERED
PM_TI AS (
SELECT
PARTICIPANT_ID,
ORG_ID,
PERIOD_ID,
START_DATE,
END_DATE,
Prorated_MonthlyTI
FROM (
SELECT
SPFM.PARTICIPANT_ID,
SPFM.ORG_ID,
SPFM.PERIOD_ID,
SRP_CP.START_DATE,
SRP_CP.END_DATE,
SPFM.OUTPUT_ACHIEVED_ITD AS Prorated_MonthlyTI,
ROW_NUMBER() OVER (
PARTITION BY
SPFM.PARTICIPANT_ID,
PERIOD.ORG_ID,
PERIOD.PERIOD_ID
ORDER BY NVL(SRP_CP.END_DATE, DATE '9999-12-31') DESC
) AS LATEST_TI_RECORD
FROM CN_SRP_PER_FORM_METRICS_ALL SPFM
INNER JOIN CN_FORMULAS_ALL_B Monthly_TI
ON Monthly_TI.FORMULA_ID = SPFM.FORMULA_ID
AND Monthly_TI.ATTRIBUTE9 = 'EmployeeEvents'
AND Monthly_TI.ATTRIBUTE10 = 'MonthlyTI'
INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP
ON SRP_CP.PARTICIPANT_ID = SPFM.PARTICIPANT_ID
AND SPFM.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID
AND SPFM.ORG_ID = SRP_CP.ORG_ID
INNER JOIN CN_PERIOD_DIMS_ALL_TL PERIOD
ON PERIOD.ORG_ID = SPFM.ORG_ID
AND PERIOD.PERIOD_TYPE = 'Monthly'
AND PERIOD.PERIOD_ID = SPFM.PERIOD_ID
WHERE (
PERIOD.END_DATE BETWEEN SRP_CP.START_DATE
AND NVL(SRP_CP.END_DATE, DATE '9999-12-31')
OR
NVL(SRP_CP.END_DATE, DATE '9999-12-31') BETWEEN PERIOD.START_DATE
AND PERIOD.END_DATE
)
AND SPFM.PARTICIPANT_ID = NVL(:participant_id, SPFM.PARTICIPANT_ID)
AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID)
)
WHERE LATEST_TI_RECORD = 1
)
SELECT
-- Build unique record identifier
TO_CHAR(
BG.PAY_PERIOD_ID
|| '-' ||
SUBSTR(
BG.PAYRUN_NAME,
1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2),
(INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || PN.person_number
|| '-' ||
DECODE(
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)
)
) AS "RecordName",
TO_CHAR(
BG.PAY_PERIOD_ID
|| '-' ||
SUBSTR(
BG.PAYRUN_NAME,
1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2),
(INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1)
)
|| '-' || PN.person_number
|| '-' ||
DECODE(
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)
)
) AS "RecordNumber",
BG.PAYRUN_ID AS "PayrunID_c",
'Regularcycle' AS "PaymentCycle_c",
BG.PAY_PERIOD_ID AS "PayPeriodID_c",
TO_CHAR(BG.PAY_DATE, 'MM/DD/YYYY') AS "PayDate_c",
PN.person_number AS "BadgeIDWorkerNumber_c",
BG.CREDITED_PARTICIPANT_ID AS "ParticipantId_c",
LATEST_PLAN_COMPONENT.ROLE_NAME AS "RoleName_c",
parDetails.ATTRIBUTE_CHAR2 AS "HRStatus_c",
parDetails.COUNTRY_CODE AS "EmploymentCountry_c",
TO_CHAR(LATEST_PLAN_COMPONENT.START_DATE, 'MM/DD/YYYY') AS "RoleStartDate_c",
TO_CHAR(LATEST_PLAN_COMPONENT.END_DATE, 'MM/DD/YYYY') AS "RoleEndDate_c",
BG.PAYRUN_NAME AS "PaymentBatch_c",
BG.PAY_GROUP_NAME AS "PayGroup_c",
-- BB columns - FIXED NVL usage
NVL(BB_AMT.BB_HOME_CURRENCIES, '0') AS "BB Home Currencies",
NVL(BB_AMT.BB_HOME_CURRENCIES_WITH_AMOUNTS, '0') AS "BB Home Currencies with Amounts",
NVL(BB_AMT.BB_BREAKDOWN_BY_CURRENCY, '0') AS "BB Breakdown by Currency",
NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM9999999990.0000'), '0') AS "BB Total (Mixed Currencies Sum)",
NVL(BB_AMT.BB_IN_LATEST_HC, '0') AS "BeginningBalance_c",
BB_AMT.LATEST_HOME_CURRENCY AS "BB Latest Home Currency",
BB_AMT.BB_CONVERSION_RATE_AVG AS "BB Avg Conversion Rate",
NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM999999999990.0000'), '0') AS "BEGINNING_BALANCE_LC",
NVL(TO_CHAR(NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM9999999990.0000'), '0') AS "PTDEarnings_c",
NVL(TO_CHAR(
NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0),
'FM999999999990.0000'
), '0') AS "CED_c",
NVL(TO_CHAR(
NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0),
'FM999999999990.0000'
), '0') AS "TotalPaysheetAmountInLHC_c",
NVL(TO_CHAR(
(NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0)) - NVL(BG.paysheet_payout_LC, 0),
'FM999999999990.0000'
), '0') AS "MCAdjustment_c",
NVL(TO_CHAR(BG.paysheet_payout_LC, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmount_c",
BG.LATEST_HOME_CURRENCY AS "CurrencyCode",
LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS "PlanComponentId_c",
NVL(TO_CHAR(PM_TI.Prorated_MonthlyTI, 'FM9999999990.0000'), '0') AS "ProratedTI_c",
NVL(TO_CHAR(parDetails.ATTRIBUTE_NUMBER1, 'FM9999999990.0000'), '0') AS "HRTI_c",
NVL(CASE
WHEN parDetails.ATTRIBUTE_NUMBER1 IS NOT NULL
AND parDetails.ATTRIBUTE_NUMBER1 <> 0
THEN TO_CHAR(
(NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) / parDetails.ATTRIBUTE_NUMBER1) * 100,
'FM9999999990.0000'
)
ELSE '0'
END, '0') AS "BBOfHRTI_c",
NVL(TO_CHAR(
(NVL(TO_NUMBER(BB_AMT.BB_IN_USD), 0) + NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 0)),
'FM9999999990.0000'
), '0') AS "CEDUSD_c",
NVL(TO_CHAR(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000'), '0') AS "PTDUSD_c",
NVL(BB_AMT.BB_IN_USD, '0') AS "BBUSD_c",
LATEST_PLAN_COMPONENT.INCENTIVE_TYPE AS "IncentiveType_c",
PRD_ML.MULTI_CURR_YN AS "MultiCurrency_c",
TO_CHAR(pay_cal.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "PaysheetAutomatedRefreshEndCST_c",
NVL(TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmountUSD_c",
(
SELECT DISTINCT HR_PRIMARY_WORKER_NUMBER
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
) AS "BadgeIdOfSCPAnalyst_c",
(
SELECT DISTINCT B.RESOURCE_PROFILE_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B
ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
) AS "ResProfileIdOfSCPAnalyst_c",
BG.conversionrate AS ConversionRateHomeToUSD_c,
PN.person_number AS "Worker Number",
parDetails.COUNTRY_CODE AS "HR Country",
(
SELECT PARTY_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
) AS "PartyID_c",
parDetails.ATTRIBUTE_CHAR17 AS "SalesRP_c",
LATEST_PLAN_COMPONENT.plan_component_name AS "PlanComponentName_c",
TO_CHAR(pay_cal.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MI:SS AM') AS "PayrollSubmissionEndCST_c",
(
SELECT DISTINCT B.EMAIL_ADDRESS
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B
ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = BG.ORG_ID
AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID
) AS "OwnerEmailId_c",
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_START_DT,
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_DT,
TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "MPACutoffStartCST_c",
TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "MPACutoffEndCST_c",
PRD_ML.BBMultiCurrencyFlag,
NVL(TO_CHAR(BG.finalPayout, 'FM9999999990.0000'), '0') AS final_payout_LC,
NVL(TO_CHAR(BG.finalPayout_paysheet, 'FM9999999990.0000'), '0') AS final_payout_PS__LC
FROM BG
INNER JOIN MULTI_CURRENCY_FLAGS PRD_ML
ON BG.PAYRUN_ID = PRD_ML.PAYRUN_ID
AND BG.CREDITED_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID
AND BG.PAY_PERIOD_ID = PRD_ML.PERIOD_ID
INNER JOIN LATEST_PLAN_COMPONENT
ON LATEST_PLAN_COMPONENT.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND LATEST_PLAN_COMPONENT.ORG_ID = BG.ORG_ID
AND LATEST_PLAN_COMPONENT.PERIOD_ID = BG.PAY_PERIOD_ID
AND LATEST_PLAN_COMPONENT.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN PTD_EARNINGS_USD
ON PTD_EARNINGS_USD.PAYRUN_ID = BG.PAYRUN_ID
AND PTD_EARNINGS_USD.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PTD_EARNINGS_USD.ORG_ID = BG.ORG_ID
AND PTD_EARNINGS_USD.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
LEFT JOIN PTD_EARNINGS_AMT
ON PTD_EARNINGS_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PTD_EARNINGS_AMT.ORG_ID = BG.ORG_ID
AND PTD_EARNINGS_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
AND PTD_EARNINGS_AMT.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN BB_AMT
ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID
LEFT JOIN PAY_CAL
ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(LATEST_PLAN_COMPONENT.PAYRUN_NAME)
AND PAY_CAL.PAYGROUP_NAME = UPPER(LATEST_PLAN_COMPONENT.PAY_GROUP_NAME)
AND PAY_CAL.PERIOD_ID = BG.PAY_PERIOD_ID
AND PAY_CAL.CURRENCY_CODE = BG.LATEST_HOME_CURRENCY
AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE))
LEFT JOIN PM_TI
ON PM_TI.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND PM_TI.ORG_ID = BG.ORG_ID
AND PM_TI.PERIOD_ID = BG.PAY_PERIOD_ID
INNER JOIN PARTICIPANT_DETAILS_CACHE parDetails
ON BG.PAY_DATE BETWEEN parDetails.START_DATE
AND NVL(parDetails.END_DATE, BG.PAY_DATE)
AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID
INNER JOIN PERSON_NUMBERS PN
ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID
WHERE 1 = 1
AND BG.payrun_id = NVL(:payrun_id, BG.payrun_id)
AND BG.CREDITED_PARTICIPANT_ID = NVL(:participant_id, BG.CREDITED_PARTICIPANT_ID)
AND BG.PAY_PERIOD_ID = NVL(:period_id, BG.PAY_PERIOD_ID)
Comments
Post a Comment