queryforincenitvetype
SELECT
-- Record Name
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)
)
|| '-' ||
(
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 BG.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
)
|| '-' ||
DECODE(
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)
)
) AS "Record Name",
-- Record Number
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)
)
|| '-' ||
(
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 BG.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
)
|| '-' ||
DECODE(
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE),
'COMMISSION', 'COM',
'BONUS', 'SPIF',
UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)
)
) AS "Record Number",
-- Payment details
BG.PAYRUN_ID AS "Payrun ID",
'Regular cycle' AS "Payment Cycle",
BG.CREDITED_PARTICIPANT_ID AS "Participant Id",
BG.PAYRUN_NAME AS "Payment Batch",
BG.PAY_PERIOD_ID AS "Pay Period ID",
BG.PAY_DATE AS "Pay Date",
-- Worker Number
(
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 BG.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
) AS "Worker Number",
-- Participant information
parDetails.COUNTRY_CODE AS "HR Country",
parDetails.ATTRIBUTE_CHAR40 AS "Employment 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_NUMBER1 AS "HR TI",
BG.LATEST_HOME_CURRENCY,
parDetails.ATTRIBUTE_CHAR2 AS "HR Status",
parDetails.ATTRIBUTE_CHAR17 AS "SalesRep",
-- Role information
LATEST_PLAN_COMPONENT.ROLE_NAME AS "Role Name",
LATEST_PLAN_COMPONENT.START_DATE AS "Role Start Date",
LATEST_PLAN_COMPONENT.END_DATE AS "Role End Date",
PM_TI.Prorated_MonthlyTI AS "Prorated TI",
-- Pay group and plan component
BG.PAY_GROUP_NAME AS "Pay group",
LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS "Plan component Id",
LATEST_PLAN_COMPONENT.INCENTIVE_TYPE,
-- Calendar dates
PAY_CAL.MPA_CUTOFF_START_DT,
PAY_CAL.MPA_CUTOFF_END_DT,
PAY_CAL.PAYSHEET_CUTOFF_START_DT,
PAY_CAL.PAYSHEET_CUTOFF_DT AS "PAYSHEET_CUTOFF_END_DT",
-- Paysheet status and amounts
-- 'tbd' AS "PTD_EARNINGS_LC",
BG.PAYSHEET_STATUS AS "PrePayHub Status",
--'tbd' AS "Total Paysheet Amount",
nvl(PRD_ML.MULTI_CURR_YN,'N'),
nvl(PRD_ML.BBMultiCurrencyFlag, 'N'),
BG.conversionrate AS usd_to_local,
-- 'TBD' AS "Current Earnings Due (CED)_LC",
--'TBD' AS "BEGINNING_BALANCE_USD",
--'TBD' AS "PTD_EARNINGS_USD",
--'TBD' AS "Current Earnings Due (CED)_USD",
--'TBD' AS "BB % of HR TI",
-- Payment amounts
BG.PAYSHEET_ID,
BG.PAY_AMT_PAY_CURR_USD,
BG.finalPayout AS final_payout_LC,
-- Badge ID
(
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 BG.CREDITED_PARTICIPANT_ID = cspl.participant_id
AND ROWNUM = 1
) AS badge_id
FROM (
-- BG: Base aggregated payment data with conversions
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.LATEST_HOME_CURRENCY,
after_group.PAYSHEET_STATUS,
TO_CHAR(1 / conversion2.CONVERSION_RATE, '00000000.000000000') AS conversionrate,
TO_CHAR(
after_group.PAY_AMT_PAY_CURR_USD * (1 / conversion2.CONVERSION_RATE),
'00000000.000000000'
) AS finalPayout
FROM (
-- after_group: Aggregated payment amounts in USD
SELECT
before_group1.ORG_ID,
before_group1.PAYRUN_ID,
before_group1.CREDITED_PARTICIPANT_ID,
before_group1.PAYRUN_NAME,
before_group1.PAY_PERIOD_ID,
before_group1.PAY_DATE,
before_group1.PAY_GROUP_ID,
before_group1.PAY_GROUP_NAME,
before_group1.PAYSHEET_ID,
before_group1.PAYSHEET_STATUS,
SUM(before_group1.PAY_AMT_PAY_CURR_USD) AS PAY_AMT_PAY_CURR_USD,
MAX(before_group1.LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY
FROM (
-- before_group1: Individual payment records converted to USD
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.PAY_AMT_PAY_CURR * conversion.CONVERSION_RATE AS PAY_AMT_PAY_CURR_USD,
partipant_detail.LATEST_HOME_CURRENCY,
spd.PAYSHEET_STATUS
FROM (
-- spd: Core payment transactions
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,
(
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
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))
)
) SPD
INNER JOIN (
-- conversion: USD conversion rates (home currency to USD)
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'
) conversion
ON TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE
AND conversion.TO_CURRENCY = 'USD'
AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE
LEFT JOIN (
-- partipant_detail: Participant details with home currency
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
) 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
) before_group1
GROUP BY
before_group1.ORG_ID,
before_group1.PAYRUN_ID,
before_group1.CREDITED_PARTICIPANT_ID,
before_group1.PAYRUN_NAME,
before_group1.PAY_PERIOD_ID,
before_group1.PAY_DATE,
before_group1.PAY_GROUP_ID,
before_group1.PAY_GROUP_NAME,
before_group1.PAYSHEET_ID,
before_group1.PAYSHEET_STATUS
) after_group
INNER JOIN (
-- conversion2: USD conversion rates (USD to local currency)
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'
) conversion2
ON TRUNC(after_group.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE
AND conversion2.TO_CURRENCY = 'USD'
AND conversion2.FROM_CURRENCY = after_group.LATEST_HOME_CURRENCY
) BG
-- PRD_ML: Period multi-currency flag
INNER JOIN (
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,
bb_MultiC.BBMultiCurrencyFlag,
PRD_ML.MULTI_CURR_YN
FROM
(
-- Subquery to identify multi-currency scenarios in batch payments
SELECT pay_1.PAYRUN_ID
,pay_1.PAYMENT_BATCH_PERIOD
,pay_1.PAYEE_PARTICIPANT_ID
,'Y' AS BBMultiCurrencyFlag
FROM
(
-- Get distinct paAyment records with home currency
SELECT P.PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE 1 = 1
-- Add filters here if needed:
-- AND PAYEE_PARTICIPANT_ID = 106000
-- AND PAYRUN_ID = 300000494369243
GROUP BY PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,PAYRUN_ID
,HOME_CURRENCY_CODE
) pay_1
INNER JOIN
(
-- Get payments where pay period differs from batch period
SELECT PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
-- Add filters here if needed:
-- AND PAYEE_PARTICIPANT_ID = 106000
-- AND PAYRUN_ID = 300000494369243
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
) bb_MultiC
FULL OUTER JOIN
(
-- Subquery to identify multi-currency participants
SELECT PARTICIPANT_ID
,PAYRUN_ID
,ORG_ID
,PERIOD_ID
,MULTI_CURR_YN
FROM (
SELECT MULTI_CURR.PARTICIPANT_ID
,MULTI_CURR.PAYRUN_ID
,MULTI_CURR.ORG_ID
,MULTI_CURR.PERIOD_ID
,CASE WHEN COUNT(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
-- Add filters here if needed:
-- AND PART_PAY.CREDITED_PARTICIPANT_ID IN (134107, 134136)
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_ML1
WHERE PRD_ML1.MULTI_CURR_YN = 'Y'
) 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
) PRD_ML
ON 1= 1
--and BG.ORG_ID = PRD_ML.ORG_ID
AND 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
AND ( nvl(PRD_ML.MULTI_CURR_YN, 'N') = 'Y'
OR nvl(PRD_ML.BBMultiCurrencyFlag, 'N') = 'Y' )
-- LATEST_PLAN_COMPONENT: Latest plan component per participant/period
INNER JOIN (
SELECT
PARTICIPANT_ID,
PAYRUN_NAME,
ORG_ID,
PAYRUN_ID,
PERIOD_ID,
PLAN_COMPONENT_ID,
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,
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 1 = 1
)
WHERE LATEST_PC_RECORD = 1
) 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
-- PAY_CAL: Payrun calendar details
LEFT JOIN (
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_TIMESTAMP008) AS PAYSHEET_CUTOFF_DT
FROM ZCA_REF_ENTITIES PAYRUN_CAL
WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c'
) 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))
-- PM_TI: Prorated monthly TI
LEFT JOIN (
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
)
)
WHERE LATEST_TI_RECORD = 1
) 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
-- parDetails: Participant details with analyst information
INNER JOIN (
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
) 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
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