working olf 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 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_...