Posts

Showing posts from December, 2025

finl

select rules.* , -- BBAction_c 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', NULL ) AS BBAction_c, -- BBReasonCode_c 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_...

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'...

query with decode

SELECT RULES.*, -- Derived columns using DECODE (slightly faster than CASE) 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, DECO...

query logic

SELECT RULES.*, -- Now derive all columns from the Rule_Number using simple CASE on integers CASE WHEN Rule_Number IN (1, 3) THEN 'Pre-approved based on Threshold limits' ELSE 'Auto-hold, manual action required' END AS BBReasonCode_c, CASE WHEN Rule_Number IN (1, 3) THEN 'AUTO-APPROVED' ELSE 'MANUAL REVIEW REQUIRED' END AS Approval_Status_c, CASE WHEN Rule_Number IN (1, 3) THEN 'N' ELSE 'Y' END AS Hold_Flag_c, CASE WHEN Rule_Number IN (1, 3) THEN 'No Action Required' WHEN Rule_Number = 2 THEN 'Review Leave Status Payment' WHEN Rule_Number = 5 THEN 'Review High Value Terminated Payment' WHEN Rule_Number IN (6, 7) THEN 'Review Negative Terminated Payment' WHEN Rule_Number IN (8, 9, 10) THEN 'Review Non-Sales Rep Payment' ELSE 'Manual Review Re...

conditions

select * from ( 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 distinct cspl.participant_id, papf.person_number, cspl.PARTICIPANT_NAME 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 - F...