BG with BB amount
/*
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.ATTRIBUTE_CHAR40 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
)
),
'00000000.000000000'
) 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
)
)
),
'00000000.000000000'
) 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
)
)
),
'00000000.000000000'
) 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
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
BB_AMT AS (
SELECT P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID",
P.ORG_ID "ORG_ID",
PB.PAY_PERIOD_ID "PAY_PERIOD_ID",
SUM(P.PAY_AMT_PAY_CURR) B_BALANCE
FROM CN_TP_PARTICIPANT_PAY_ALL P
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
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.CREDITED_PARTICIPANT_ID = 134135
--AND P.ORG_ID = 300000008656040
--AND PAY_VW.PAY_PERIOD_ID = 2025007
GROUP BY P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
-- 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: PeriodID-PayrunName-BadgeID-IncentiveType
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 "Record Name",
-- Duplicate of Record Name for reference
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 "Record Number",
-- Core payment identifiers
BG.PAYRUN_ID AS "Payrun ID",
BG.PAYSHEET_ID,
'Regular cycle' AS "Payment Cycle",
BG.PAY_PERIOD_ID AS "Pay Period ID",
BG.PAY_DATE AS "Pay Date",
-- Get badge ID from HR system
PN.person_number AS badge_id,
BG.CREDITED_PARTICIPANT_ID AS "Participant Id",
-- Role and employment info
LATEST_PLAN_COMPONENT.ROLE_NAME AS "Role Name",
parDetails.ATTRIBUTE_CHAR2 AS "HR Status",
parDetails.ATTRIBUTE_CHAR40 AS "Employment Country",
LATEST_PLAN_COMPONENT.START_DATE AS "Role Start Date",
LATEST_PLAN_COMPONENT.END_DATE AS "Role End Date",
-- Payment batch details
BG.PAYRUN_NAME AS "Payment Batch",
BG.PAY_GROUP_NAME AS "Pay group",
-- Financial amounts in local currency - TODO: implement BB and CED calculations
BB_AMT.B_BALANCE AS "BEGINNING_BALANCE_LC",
'TBD' AS "Current Earnings Due (CED)_LC",
BG.paysheet_payout_LC AS paysheet_payout_MC,
BG.LATEST_HOME_CURRENCY AS "Home Currency",
BG.PAYSHEET_STATUS AS "PrePayHub Status",
-- Plan component and TI
LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS "Plan component Id",
PM_TI.Prorated_MonthlyTI AS "Prorated TI",
parDetails.ATTRIBUTE_NUMBER1 AS "HR TI",
'TBD' AS "BB % of HR TI",
-- USD amounts - TODO: implement conversions
'TBD' AS "Current Earnings Due (CED) USD",
'TBD' AS "PTD earings USD",
'TBD' AS "BEGINNING_BALANCE_USD",
-- Incentive classification
LATEST_PLAN_COMPONENT.INCENTIVE_TYPE,
'Analyst - Missed Deadline' AS MPAReasonCode,
-- Multi-currency flags
PRD_ML.MULTI_CURR_YN AS "Multi Currency",
-- Calendar cutoffs - TODO: implement automated refresh end date
'TBD' AS "Paysheet automated Refresh End CST",
PAY_CAL.MPA_CUTOFF_START_DT,
PAY_CAL.MPA_CUTOFF_END_DT,
BG.PAY_AMT_PAY_CURR_USD AS "Total Paysheet Amount in USD",
-- Analyst info
(
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 "Badge Id of SCP Analyst",
(
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 "ANALYST_RESOURCE_PROFILE_ID",
-- Conversion rate: home currency to USD
BG.conversionrate AS home_to_usd,
-- Worker details (same as badge_id)
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 "ANALYST_PARTY_ID",
parDetails.ATTRIBUTE_CHAR17 AS "SalesRep",
-- Additional details
LATEST_PLAN_COMPONENT.plan_component_name,
pay_cal.PAYROLL_SUBMISSION_END_CST,
(
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 "OWNER_EMAIL_ID",
-- Paysheet cutoff dates
PAY_CAL.PAYSHEET_CUTOFF_START_DT,
PAY_CAL.PAYSHEET_CUTOFF_DT AS "PAYSHEET_CUTOFF_END_DT",
-- Multi-currency flags (detailed)
PRD_ML.BBMultiCurrencyFlag,
-- Final payout amounts in local currency
-- BG.PAYSHEET_ID,
BG.finalPayout AS final_payout_LC,
BG.finalPayout_paysheet 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 BB_AMT
ON bb_amt.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND bb_amt.ORG_ID = BG.ORG_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