Posts

Showing posts from November, 2025

PK - code

// ================================================================================== // Trigger: After Update on TimeEntry_c // Purpose: When PPMTaskID_c is updated (null → value) by an end user (not integration), // check if all entries have PPMTaskID_c and uncheck flag on Timesheet if true. // ================================================================================== try { println "[1] Starting PPMTaskCheckTrigger (After Update on TimeEntry_c)" println "[1.1] Processing TimeEntry record ID: ${Id}" // 1️⃣ Quick check if (!(isAttributeChanged('PPMTaskID_c') && PPMTaskID_c != null)) { println "[2] PPMTaskID_c not changed or still null — exiting" return } // 2️⃣ Skip for integration users def oicUsers = [ 'OicPpmPrdUsr01@pike.com','OicCrmPrdUsr01@pike.com','OicHcmPrdUsr01@pike.com', 'OicCRMUat1Usr01@pike.com','OicPpmUat1Usr01@p...

Child query new

WITH -- Materialize the multi-currency detection queries once - FILTERED BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID FROM ( SELECT P.PAYRUN_ID, P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.HOME_CURRENCY_CODE FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE PAY_PERIOD_ID PAYMENT_BATCH_PE...

child query latest -

WITH -- Materialize the multi-currency detection queries once - FILTERED BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID FROM ( SELECT P.PAYRUN_ID, P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.HOME_CURRENCY_CODE FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE PAY_PERIOD_ID PAYMENT_BATCH_PE...

final simple where

WHERE NOT ( NVL("PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("PTD Earning (Latest HC)", ',', '')), 0) = 0 AND NVL("BB PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("BB PTD Earning (Latest HC)", ',', '')), 0) = 0 )

new suppress condition

WHERE NOT ( -- Suppress if PTD pair is null/0 AND BB pair has values ( NVL("PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("PTD Earning (Latest HC)", ',', '')), 0) = 0 AND (NVL("BB PTD Earning LC", 0) != 0 OR NVL(TO_NUMBER(REPLACE("BB PTD Earning (Latest HC)", ',', '')), 0) != 0) ) OR -- Suppress if BB pair is null/0 AND PTD pair has values ( NVL("BB PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("BB PTD Earning (Latest HC)", ',', '')), 0) = 0 AND (NVL("PTD Earning LC", 0) != 0 OR NVL(TO_NUMBER(REPLACE("PTD Earning (Latest HC)", ',', '')), 0) != 0) ) )

New

WHERE NOT ( -- Suppress if both PTD columns are null or 0 (NVL("PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("PTD Earning (Latest HC)", ',', '')), 0) = 0) AND -- Suppress if both BB columns are null or 0 (NVL("BB PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("BB PTD Earning (Latest HC)", ',', '')), 0) = 0) )
PTDEarningsInHomeCurrency_c PTDEarningsInLatestHomeCurrency_c BBInHomeCurrency_c BBInLatestHomeCurrency_

fixed big query

/* Multi-Currency Paysheet Report - OPTIMIZED WITH FILTERS Aggregates payment data with currency conversions (Home USD) Filters: :payrun_id, :participant_id, :period_id (all optional) */ 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 cspl.participant_id, 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_...

big query error

/* Multi-Currency Paysheet Report - OPTIMIZED WITH FILTERS Aggregates payment data with currency conversions (Home USD) Filters: :payrun_id, :participant_id, :period_id (all optional) */ 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 cspl.participant_id, 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_...