query with full decode

SELECT RULES.*, -- Previous DECODE statements DECODE(Rule_Number, 1, 'Pre-approved based on Threshold limits', 3, 'Pre-approved based on Threshold limits', 'Auto-hold, manual action required') AS BBReasonCode_c, DECODE(Rule_Number, 1, 'AUTO-APPROVED', 3, 'AUTO-APPROVED', 'MANUAL REVIEW REQUIRED') AS Approval_Status_c, DECODE(Rule_Number, 1, 'N', 3, 'N', 'Y') AS Hold_Flag_c, DECODE(Rule_Number, 1, 'No Action Required', 3, 'No Action Required', 2, 'Review Leave Status Payment', 5, 'Review High Value Terminated Payment', 6, 'Review Negative Terminated Payment', 7, 'Review Negative Terminated Payment', 8, 'Review Non-Sales Rep Payment', 9, 'Review Non-Sales Rep Payment', 10, 'Review Non-Sales Rep Payment', 'Manual Review Required') AS Action_Required_c, DECODE(Rule_Number, 1, 'Low', 3, 'Low', 2, 'High', 5, 'High', 6, 'High', 7, 'High', 'Medium') AS Priority_Level_c, DECODE(Rule_Number, 1, 'Standard Processing', 3, 'Standard Processing', 2, 'Leave Status Exception', 5, 'High Value Termination Exception', 6, 'Negative Termination Exception', 7, 'Negative Termination Exception', 8, 'Non-Sales Rep Exception', 9, 'Non-Sales Rep Exception', 10, 'Non-Sales Rep Exception', 'General Exception') AS Exception_Category_c, DECODE(Rule_Number, 1, 'System Auto-Approved', 3, 'System Auto-Approved', 2, 'HR Manager', 5, 'Finance Director', 6, 'Finance Manager', 7, 'Finance Manager', 8, 'Compensation Analyst', 9, 'Compensation Analyst', 10, 'Compensation Analyst', 'Compensation Manager') AS Reviewer_Assignment_c, DECODE(Rule_Number, 1, 'Auto-Process Queue', 3, 'Auto-Process Queue', 'Manual Review Queue') AS Processing_Queue_c, 'RULE-' || LPAD(TO_CHAR(Rule_Number), 2, '0') AS Rule_Code_c, -- NEW FIELDS BELOW -- BBMPAComment_c (NEW BB/MPA Comment) 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%). PENDING analyst to review and action.', NULL ) AS BBMPAComment_c, -- MPAReasonCode_c (MPA reason code) DECODE(Rule_Number, 1, NULL, 3, NULL, 'Analyst - Missed Deadline' ) AS MPAReasonCode_c, -- MPAGenerated_c (MPA Auto prepopulate) DECODE(Rule_Number, 1, NULL, 2, 'Reverse of CED', 3, NULL, 4, 'Reverse of CED', 5, 'Reverse of CED', 6, 'Reverse of CED', 7, 'Reverse of CED', 8, 'Reverse of CED', 9, 'Reverse of CED', 10, 'Reverse of CED', 99, 'Reverse of BB', NULL ) AS MPAGenerated_c, -- ExpectedPaymentAmount_c (Expected Payout Amount) DECODE(Rule_Number, 1, NULL, 3, NULL, 99, TO_NUMBER(RULES.PTDEarnings_c), -- Assuming you have PTDEarnings_c column 0 -- Zero for all other rules (2,4,5,6,7,8,9,10) ) AS ExpectedPaymentAmount_c, -- MPAStatus_c (SCP/SPIF manager's approval) DECODE(Rule_Number, 1, NULL, 3, NULL, 'Auto approved' ) AS MPAStatus_c, -- RecoverableFlagV1_c (Recoverable MPA? Y/N) DECODE(Rule_Number, 1, NULL, 3, NULL, 'Y' ) AS RecoverableFlagV1_c FROM ( SELECT BASE.*, -- Evaluate complex conditions ONLY ONCE CASE WHEN BASE.HRStatus_c = 'A' AND BASE.EmploymentCountry_c != 'AT' AND UPPER(BASE.SalesRP_c) = 'YES' AND TO_NUMBER(BASE.HRTI_c) != 0 AND TO_NUMBER(BASE.BBOfHRTI_c) BETWEEN -5 AND 5 AND TO_NUMBER(BASE.BeginningBalance_c) != 0 AND BASE.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 1 WHEN BASE.HRStatus_c = 'L' AND UPPER(BASE.SalesRP_c) = 'YES' AND TO_NUMBER(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) = 'YES' AND TO_NUMBER(BASE.CEDUSD_c) BETWEEN 0 AND 10 AND BASE.IncentiveType_c = 'COMMISSION' THEN 3 WHEN BASE.HRStatus_c = 'T' AND UPPER(BASE.SalesRP_c) = 'YES' AND TO_NUMBER(BASE.CEDUSD_c) BETWEEN 0 AND 10 AND BASE.IncentiveType_c = 'BONUS' THEN 4 WHEN BASE.HRStatus_c = 'T' AND UPPER(BASE.SalesRP_c) = 'YES' AND TO_NUMBER(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) = 'YES' AND TO_NUMBER(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) = 'YES' AND TO_NUMBER(BASE.CED_c) < 0 AND BASE.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 7 WHEN BASE.HRStatus_c = 'A' AND (UPPER(BASE.SalesRP_c) = 'NO' OR BASE.SalesRP_c IS NULL) AND TO_NUMBER(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) = 'NO' OR BASE.SalesRP_c IS NULL) AND TO_NUMBER(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) = 'NO' OR BASE.SalesRP_c IS NULL) AND TO_NUMBER(BASE.CED_c) < 0 AND BASE.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 10 WHEN TO_NUMBER(BASE.BeginningBalance_c) != 0 AND BASE.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 99 ELSE 0 END AS Rule_Number FROM ( -- ============================================ -- YOUR ENTIRE ORIGINAL QUERY GOES HERE -- ============================================ WITH CONVERSION_RATES_USD AS ( -- ... your original query ... ) SELECT * FROM your_final_query -- ============================================ ) BASE ) RULES ORDER BY RULES.PayPeriodID_c, RULES.BadgeIDWorkerNumber_c;

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika