complete unified query
/* FINAL COMPLETE OPTIMIZED QUERY */
SELECT
rules.*,
/* Rule Action Mapping */
DECODE(Rule_Number, 1, 'MAV_DIR_BB_ACTION_1000', 2, 'MAV_DIR_BB_ACTION_1003', 3, 'MAV_DIR_BB_ACTION_1001', 4, 'MAV_DIR_BB_ACTION_1003', 5, 'MAV_DIR_BB_ACTION_1003', 6, 'MAV_DIR_BB_ACTION_1003', 7, 'MAV_DIR_BB_ACTION_1003', 8, 'MAV_DIR_BB_ACTION_1003', 9, 'MAV_DIR_BB_ACTION_1003', 10, 'MAV_DIR_BB_ACTION_1003', 99, 'MAV_DIR_BB_ACTION_1002', 11, 'MAV_DIR_BB_ACTION_1051', NULL) AS BBAction_c,
/* Rule Reason Mapping */
DECODE(Rule_Number, 1, 'MVA_DIR_BB_REASON_1018', 2, 'MVA_DIR_BB_REASON_1001', 3, 'MVA_DIR_BB_REASON_1018', 4, 'MVA_DIR_BB_REASON_1001', 5, 'MVA_DIR_BB_REASON_1001', 6, 'MVA_DIR_BB_REASON_1001', 7, 'MVA_DIR_BB_REASON_1001', 8, 'MVA_DIR_BB_REASON_1001', 9, 'MVA_DIR_BB_REASON_1001', 10, 'MVA_DIR_BB_REASON_1001', 99, 'MVA_DIR_BB_REASON_1001', 11, 'MVA_DIR_BB_REASON_1027', NULL) AS BBReasonCode_c,
/* Rule Comment Mapping */
DECODE(Rule_Number,
1, 'Full BB is pre-approved by system as the rep is active sale rep within the defined threshold limits (BB % HR TI ≥ -5% and ≤ 5%). Pre-approved not applicable for Austria country.',
2, 'Full CED put on Hold by system as the rep is on LOA with positive or negative CED amount. PENDING analyst to review and action.',
3, 'Full CED is pre-approved by system as the rep is terminated within the defined threshold limits of CED amount (CED ≥ 0 and ≤ 10 USD). Pre-approved not applicable for Austria country.',
4, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED ≥ 0 and ≤ 10 USD). PENDING analyst to review and action.',
5, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED > 10 USD). PENDING analyst to review and action.',
6, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.',
7, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Consider Payroll collection, if negative CED amount is final.',
8, 'Full CED put on Hold by system as the rep moved to non-sales role with positive CED amount. PENDING analyst to review and action.',
9, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.',
10, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Consider Payroll collection if negative CED amount is final. Rep''s consent NEEDS to be attached for such case.',
99, 'Full BB put on Hold by system as the rep is active sale rep OVER the defined pre-approved BB threshold limits (BB % HR TI ≥ -5% and ≤ 5%) or is Austria rep. PENDING analyst to review and action.',
11, 'Zero BB is pre-approved by system for active sale rep',
NULL
) AS BBMPAComment_c,
DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'MAV_DIR_MPA_CODE_1008') AS MPAReasonCode_c,
DECODE(Rule_Number, 1, NULL, 2, (-1) * rules.CED_c, 3, NULL, 4, (-1) * rules.CED_c, 5, (-1) * rules.CED_c, 6, (-1) * rules.CED_c, 7, (-1) * rules.CED_c, 8, (-1) * rules.CED_c, 9, (-1) * rules.CED_c, 10, (-1) * rules.CED_c, 11 , NULL, 99, (-1) * rules.BeginningBalance_c, NULL) AS MPAGenerated_c,
DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 99, rules.PTDEarnings_c, NULL) AS ExpectedPaymentAmount_c,
DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'Auto approved') AS MPAStatus_c,
DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'Y') AS RecoverableFlagV1_c,
rules.Owner_Id_c OraZcxOwner_Id_c
FROM (
SELECT base.*,
/* Core Business Rules Engine */
CASE
WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c != 'AT' AND UPPER(base.SalesRP_c) = 'Y' AND base.HRTI_c != 0 AND base.BBOfHRTI_c BETWEEN -0.05 AND 0.05 AND base.BeginningBalance_c != 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 1
WHEN base.HRStatus_c = 'L' AND UPPER(base.SalesRP_c) = 'Y' AND base.CED_c != 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 2
WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c != 'AT' AND UPPER(base.SalesRP_c) = 'Y' AND base.CEDUSD_c BETWEEN 0 AND 10 AND base.IncentiveType_c = 'COMMISSION' THEN 3
WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND base.CEDUSD_c BETWEEN 0 AND 10 AND base.IncentiveType_c = 'BONUS' THEN 4
WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10 AND base.IncentiveType_c = 'COMMISSION' AND base.EmploymentCountry_c = 'AT' THEN 4
WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND base.CEDUSD_c > 10 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 5
WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c = 'RO' AND UPPER(base.SalesRP_c) = 'Y' AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 6
WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c != 'RO' AND UPPER(base.SalesRP_c) = 'Y' AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 7
WHEN base.HRStatus_c = 'A' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND base.CED_c > 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 8
WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c = 'RO' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 9
WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c != 'RO' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 10
WHEN base.HRStatus_c = 'A' AND UPPER(base.SalesRP_c) = 'Y' AND base.BeginningBalance_c = 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 11
WHEN base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 99
ELSE 0
END AS Rule_Number
FROM (
WITH
/* CTE 1: Participant and Analyst Header Info */
CN_PART_DETAILS AS (
SELECT /*+ MATERIALIZE */
PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, PAPF.PERSON_NUMBER AS HR_PRIMARY_WORKER_NUMBER,
PART_DTLS.PARTICIPANT_DETAIL_ID, PART_DTLS.ORG_ID, PART_DTLS.START_DATE, PART_DTLS.END_DATE,
PART_DTLS.COUNTRY_CODE AS HR_Country, PART_DTLS.ATTRIBUTE_CHAR40 AS Employment_Country,
ANALYST.HR_PRIMARY_WORKER_NUMBER AS ANALYST_BADGE_ID, RES.RESOURCE_PROFILE_ID AS ANALYST_RESOURCE_PROFILE_ID,
RES.EMAIL_ADDRESS AS OWNER_EMAIL_ID, PART_DTLS.ATTRIBUTE_NUMBER1 AS HR_TI, PART_DTLS.CURRENCY_CODE AS HOME_CURRENCY,
PART_DTLS.ATTRIBUTE_CHAR2 AS HR_Status, PART_DTLS.ATTRIBUTE_CHAR17 AS SalesRep, PART_DTLS.ATTRIBUTE_CHAR33 AS ActionCode
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS
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
LEFT JOIN HZ_PERSON_PROFILES HPP ON HPP.PARTY_ID = PART_HDR.PARTY_ID
LEFT JOIN PER_ALL_PEOPLE_F PAPF ON PAPF.PERSON_ID = HPP.HR_PERSON_ID AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
LEFT JOIN CN_SRP_PARTICIPANTS_ALL ANALYST ON ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID AND ANALYST.ORG_ID = PART_HDR.ORG_ID
LEFT JOIN JTF_RS_RESOURCE_PROFILES RES ON RES.PARTY_ID = ANALYST.PARTY_ID
),
/* CTE 2: Role Details for the Period */
CN_ROLE_DETAILS AS (
SELECT /*+ MATERIALIZE */ PART_ROLE.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID, CN_ROLE.ROLE_NAME,
PART_ROLE.START_DATE_ACTIVE, PART_ROLE.END_DATE_ACTIVE,
ROW_NUMBER() OVER (PARTITION BY PART_ROLE.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID ORDER BY NVL(PART_ROLE.END_DATE_ACTIVE,'9999-12-31') DESC) AS LATEST_ROLE_ORDER
FROM CN_PARTICIPANT_ROLES PART_ROLE
JOIN CN_ROLES_TL CN_ROLE ON PART_ROLE.ROLE_ID = CN_ROLE.ROLE_ID AND CN_ROLE.LANGUAGE = 'US'
JOIN CN_PERIOD_DIMS_ALL_TL PERIOD ON PERIOD.PERIOD_TYPE = 'Monthly' AND PERIOD.PERIOD_ID IN (:P_PERIOD_ID)
WHERE (PERIOD.END_DATE >= PART_ROLE.START_DATE_ACTIVE AND PERIOD.START_DATE <= NVL(PART_ROLE.END_DATE_ACTIVE,'9999-12-31'))
),
/* CTE 3: Core Payment Data */
CN_SRP_PYMT_DETAILS AS (
SELECT /*+ MATERIALIZE */ PS.PAYRUN_ID, PS.PARTICIPANT_ID CREDITED_PARTICIPANT_ID, PS.ORG_ID, PB.PAYRUN_NAME,
PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_NAME, NVL(PB.REPORT_GROUP,'ALL') INCENTIVE_TYPE, PS.PAYSHEET_ID,
SUM(PS.TOTAL_AMT_HOME_CURRENCY) TOTAL_AMT
FROM CN_TP_PAYSHEETS_ALL PS
JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PS.PAYRUN_ID AND PB.ORG_ID = PS.ORG_ID
JOIN CN_PAY_GROUPS_ALL_VL PG ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID AND PG.ORG_ID = PS.ORG_ID
WHERE PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
AND PS.QUOTA_ID IS NULL
GROUP BY PS.PAYRUN_ID, PS.PARTICIPANT_ID, PS.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_NAME, PB.REPORT_GROUP, PS.PAYSHEET_ID
),
/* CTE 4: Beginning Balance Logic */
BB_AMT AS (
SELECT /*+ MATERIALIZE */ P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.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
WHERE PB.PAY_PERIOD_ID IN (:P_PERIOD_ID) AND P.PAY_PERIOD_ID <> PB.PAY_PERIOD_ID
GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID
),
/* CTE 5: PTD Earnings Logic */
PTD_EARNINGS AS (
SELECT /*+ MATERIALIZE */ P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, SUM(P.PAY_AMT_PAY_CURR) PTD_AMT
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
WHERE PB.PAY_PERIOD_ID IN (:P_PERIOD_ID) AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID
AND P.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN')
GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID
)
/* Main Data Assembly */
SELECT
TO_CHAR(SPD.PAY_PERIOD_ID) PayPeriodID_c,
/* Unique Record Identifier Calculation */
TO_CHAR(SPD.PAY_PERIOD_ID || '-' || SUBSTR(SPD.PAYRUN_NAME, 1+INSTR(SPD.PAYRUN_NAME,'_',-1,2), (INSTR(SPD.PAYRUN_NAME,'_',-1,1)-INSTR(SPD.PAYRUN_NAME,'_',-1,2)-1)) || '-' || PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER || '-' || DECODE(UPPER(SPD.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(SPD.INCENTIVE_TYPE))) RecordNumber,
TO_CHAR(SPD.PAY_PERIOD_ID || '-' || SUBSTR(SPD.PAYRUN_NAME, 1+INSTR(SPD.PAYRUN_NAME,'_',-1,2), (INSTR(SPD.PAYRUN_NAME,'_',-1,1)-INSTR(SPD.PAYRUN_NAME,'_',-1,2)-1)) || '-' || PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER || '-' || DECODE(UPPER(SPD.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(SPD.INCENTIVE_TYPE))) RecordName,
PD_PAY_DT.ANALYST_RESOURCE_PROFILE_ID Owner_Id_c,
PD_PAY_DT.HOME_CURRENCY CurrencyCode,
TO_CHAR(SPD.PAYRUN_ID) PayrunID_c,
TO_CHAR(SPD.PAY_DATE,'MM/DD/YYYY') PayDate_c,
PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER BadgeIDWorkerNumber_c,
TO_CHAR(SPD.CREDITED_PARTICIPANT_ID) ParticipantId_c,
PD_PAY_DT.PARTICIPANT_NAME ParticipantName_c,
PRD.ROLE_NAME RoleName_c,
TO_CHAR(PRD.START_DATE_ACTIVE, 'MM/DD/YYYY') RoleStartDate_c,
TO_CHAR(PRD.END_DATE_ACTIVE, 'MM/DD/YYYY') RoleEndDate_c,
SPD.PAYRUN_NAME PaymentBatch_c,
SPD.PAY_GROUP_NAME PayGroup_c,
ROUND(NVL(SPD.TOTAL_AMT,0),4) TotalPaysheetAmount_c,
ROUND(NVL(BB.B_BALANCE,0),4) BeginningBalance_c,
ROUND(NVL(PTD.PTD_AMT,0),4) PTDEarnings_c,
ROUND((NVL(BB.B_BALANCE,0) + NVL(PTD.PTD_AMT,0)),4) CED_c,
CASE WHEN NVL(PD_PAY_DT.HR_TI,0)=0 THEN 0 ELSE ROUND((NVL(BB.B_BALANCE,0)/PD_PAY_DT.HR_TI),4) END BBOfHRTI_c,
NVL(PD_PAY_DT.HR_Status, 'A') HRStatus_c,
SPD.INCENTIVE_TYPE IncentiveType_c,
PD_PAY_DT.HR_Country EmploymentCountry_c,
SPD.PAYSHEET_ID PaysheetId_c,
/* USD Placeholder (Can be joined with CN_CONVERSION_RATES if needed) */
ROUND((NVL(BB.B_BALANCE,0) + NVL(PTD.PTD_AMT,0)),4) CEDUSD_c,
NVL(PD_PAY_DT.SalesRep, 'Y') SalesRP_c,
PD_PAY_DT.PARTICIPANT_DETAIL_ID ParticipantDetailId_c
FROM CN_SRP_PYMT_DETAILS SPD
/* Performance Join: Using ranges instead of TRUNC functions */
JOIN CN_PART_DETAILS PD_PAY_DT
ON SPD.CREDITED_PARTICIPANT_ID = PD_PAY_DT.PARTICIPANT_ID
AND SPD.ORG_ID = PD_PAY_DT.ORG_ID
AND SPD.PAY_DATE >= PD_PAY_DT.START_DATE
AND SPD.PAY_DATE <= NVL(PD_PAY_DT.END_DATE, TO_DATE('31-12-9999','DD-MM-YYYY'))
LEFT JOIN CN_ROLE_DETAILS PRD
ON PRD.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND PRD.ORG_ID = SPD.ORG_ID
AND PRD.PERIOD_ID = SPD.PAY_PERIOD_ID
AND PRD.LATEST_ROLE_ORDER = 1
LEFT JOIN BB_AMT BB
ON BB.CREDITED_PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND BB.PAYRUN_ID = SPD.PAYRUN_ID
LEFT JOIN PTD_EARNINGS PTD
ON PTD.CREDITED_PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND PTD.PAYRUN_ID = SPD.PAYRUN_ID
WHERE SPD.ORG_ID = (SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV')
) base
) rules;
Comments
Post a Comment