Posts

2026-04-05|56267|Productive|18-00322-000

2026-04-05|27367|Productive|26-13121-001

tickets

Midweek Employee Class change groovy: https://pikeinc.atlassian.net/browse/ITS-168643 "Update Flag" groovy logic update: https://pikeinc.atlassian.net/browse/ITS-168845

https://pikeinc.atlassian.net/browse/OFSP2-1414

PDFEmailRequired_c CREWLEADER_ID_C

2026-03-08|512102|Productive|25-13951-001

final with comments

WITH /*--------------------------------------------------------------- CTE 1: BB_MULTI_CURRENCY Purpose: Identify participants who have multi-currency earnings in prior periods (BB = Balance Brought Forward). Logic: 1. Extract participant earnings grouped by home currency. 2. Identify cases where earnings were generated in earlier periods (PAY_PERIOD_ID PAYMENT_BATCH_PERIOD). 3. If more than one currency exists for the same participant/payrun/period, flag it as multi-currency. ----------------------------------------------------------------*/ BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag 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 IN (:payrun_id) OR COALESCE(:payrun...

add comment

WITH BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL') AND (P.PAYMENT_BATCH_PERIOD IN (:period_id) OR COALESCE(:period_id, 'ALL') = 'ALL') GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT...

add comments

WITH BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL') AND (P.PAYMENT_BATCH_PERIOD IN (:period_id) OR COALESCE(:period_id, 'ALL') = 'ALL') GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT...

childfixed

WITH BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL') AND (P.PAYMENT_BATCH_PERIOD IN (:period_id) OR COALESCE(:period_id, 'ALL') = 'ALL') GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT...

childreprot tofix111

WITH BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL') AND (P.PAYMENT_BATCH_PERIOD IN (:period_id) OR COALESCE(:period_id, 'ALL') = 'ALL') GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT...

childqueryenhanced

WITH /* ========================= KEEP ORIGINAL FLAG LOGIC ========================= */ BB_MULTI_CURRENCY AS ( -- (same as in your original child query) ), PERIOD_MULTI_CURRENCY AS ( -- (same as in your original child query) ), MULTI_CURRENCY_FLAGS AS ( -- (same as in your original child query) ), CONVERSION_RATES_USD AS ( SELECT CR.FROM_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' ), PERSON_NUMBERS AS ( SELECT 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_...

BBMC Child query

WITH BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL') = 'ALL') AND (P.PAYMENT_BATCH_PERIOD IN (:period_id) OR COALESCE(:period_id, 'ALL') = 'ALL') GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT...