Posts

new query

WITH BASE_QUERY AS ( -- Your entire existing query goes here 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' ), -- ... [rest of your CTEs remain exactly the same] ... -- [I'm abbreviating here for clarity, but include ALL your CTEs] -- Your final SELECT from the original query SELECT TO_CHAR( BG.PAY_PERIOD_ID || '-' || SUBSTR( BG.PAYRUN_NAME, 1 + INSTR(BG.PAYRUN_NAME, '_',...

Parent query

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 - FILTERED PARTICIPA...

fixed

// ============================================================================ // Section 6: SPIF vs Comm Deduction (Action Code: MAV_DIR_BB_ACTION_1012) // Covers original Sections 10 & 11 // ============================================================================ if (BBActionManual_c == 'MAV_DIR_BB_ACTION_1012' && SalesRP_c == 'Y') { println("[EPA Trigger] *** START Section 6: SPIF vs Comm Deduction ***") println("[EPA Trigger] Action Code: ${BBActionManual_c}") println("[EPA Trigger] IncentiveType: ${IncentiveType_c}") println("[EPA Trigger] CED_c: ${CED_c}") println("[EPA Trigger] Current ExpectedPaymentAmount_c: ${ExpectedPaymentAmount_c}") // Verify the Incentive Type condition (BONUS only in original code) if (IncentiveType_c == 'BONUS') { println("[EPA Trigger] IncentiveType check passed (BONUS).") // ** VALIDATION 1: Employee Consent check ** if (EmployeeConsent_c == 'N...

check and ifx

// ============================================================================ // Section 6: SPIF vs Comm Deduction (Action Code: MAV_DIR_BB_ACTION_1012) // Covers original Sections 10 & 11 // ============================================================================ if (BBActionManual_c == 'MAV_DIR_BB_ACTION_1012' && SalesRP_c == 'Y') { println("[EPA Trigger] *** START Section 6: SPIF vs Comm Deduction ***") println("[EPA Trigger] Action Code: ${BBActionManual_c}") println("[EPA Trigger] IncentiveType: ${IncentiveType_c}") println("[EPA Trigger] CED_c: ${CED_c}") println("[EPA Trigger] Current ExpectedPaymentAmount_c: ${ExpectedPaymentAmount_c}") // Verify the Incentive Type condition (BONUS only in original code) if (IncentiveType_c == 'BONUS') { println("[EPA Trigger] IncentiveType check passed (BONUS).") // ** VALIDATION 1: Employee Consent check ** if (EmployeeConsent_c == 'N...

section 6 fixed

// ============================================================================ // Section 6: SPIF vs Comm Deduction (Action Code: MAV_DIR_BB_ACTION_1012) // Covers original Sections 10 & 11 // ============================================================================ if (BBActionManual_c == 'MAV_DIR_BB_ACTION_1012' && SalesRP_c == 'Y') { println("[EPA Trigger] *** START Section 6: SPIF vs Comm Deduction ***") println("[EPA Trigger] Action Code: ${BBActionManual_c}") println("[EPA Trigger] IncentiveType: ${IncentiveType_c}") println("[EPA Trigger] CED_c: ${CED_c}") println("[EPA Trigger] Current ExpectedPaymentAmount_c: ${ExpectedPaymentAmount_c}") // Verify the Incentive Type condition (BONUS only in original code) if (IncentiveType_c == 'BONUS') { println("[EPA Trigger] IncentiveType check passed (BONUS).") // ** VALIDATION 1: Employee Consent check ** if (EmployeeConsent_c == 'N...

epa fix

// ============================================================================ // Section 1: Hold Beginning Balance (Full BB on Hold Until next month) // Action Code: MAV_DIR_BB_ACTION_1007 // ============================================================================ if (BBActionManual_c == 'MAV_DIR_BB_ACTION_1007') { println("[EPA Trigger] *** START Section 1: Hold Beginning Balance ***") println("[EPA Trigger] Action Code: ${BBActionManual_c}") println("[EPA Trigger] IncentiveType: ${IncentiveType_c}") println("[EPA Trigger] CED_c: ${CED_c}") println("[EPA Trigger] Current ExpectedPaymentAmount_c: ${ExpectedPaymentAmount_c}") // Verify the Incentive Type condition (COMMISSION or BONUS, not SPIFF) if (IncentiveType_c == 'COMMISSION' || IncentiveType_c == 'BONUS') { println("[EPA Trigger] IncentiveType check passed.") // ** NULL-AWARE CALCULATION ** if (ExpectedPaymentAmount_c == null || CED_c ...

BB manual action

// ============================================================================ // SECTION 1: Approve Beginning Balance (Pay Full Amount) // Action Code: MAV_DIR_BB_ACTION_1006 // ============================================================================ if (BBActionManual_c == 'MAV_DIR_BB_ACTION_1006') { println("[BBAction Trigger] Action: Approve Beginning Balance (Pay Full Amount)") println("[BBAction Trigger] IncentiveType: ${IncentiveType_c}") // Verify the Incentive Type condition (COMMISSION or BONUS, not SPIFF) if (IncentiveType_c == 'COMMISSION' || IncentiveType_c == 'BONUS') { println("[BBAction Trigger] Clearing BBReasonCode and BBMPAComment") BBMPAComment_c = null BBReasonCode_c = null MPAReasonCode_c = null println("[BBAction Trigger] Section 1 completed successfully") } else { println("[BBAction Trigger] IncentiveType not...