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_PEOPLE_F papf ON papf.person_id = hpp.hr_person_id WHERE (cspl.participant_id IN (:participant_id) OR COALESCE(:participant_id,'ALL')='ALL') AND (cspl.participant_id IN (:badge) OR COALESCE(:badge,'ALL')='ALL') ), PARTICIPANT_DETAILS_CACHE AS ( SELECT b.PARTICIPANT_ID, b.START_DATE, b.END_DATE, b.CURRENCY_CODE FROM CN_SRP_PARTICIPANT_DETAILS_ALL b WHERE (b.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL')='ALL') AND (b.PARTICIPANT_ID IN (:badge) OR COALESCE(:badge,'ALL')='ALL') ), LATEST_PLAN_COMPONENT AS ( -- keep the same logic from your original child query ), /* ====================================== BASE PAYMENTS (FILTERS SAME AS PARENT) ====================================== */ BASE_PAYMENTS_ALL AS ( SELECT P.*, CASE WHEN P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD THEN 'BB_OLD' ELSE 'BB_CURRENT' END PERIOD_TYPE FROM CN_TP_PARTICIPANT_PAY_EXP_V P INNER JOIN MULTI_CURRENCY_FLAGS F ON P.PAYRUN_ID = F.PAYRUN_ID AND P.PAYEE_PARTICIPANT_ID = F.PARTICIPANT_ID AND P.PAYMENT_BATCH_PERIOD = F.PERIOD_ID 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') ), /* ====================================== AGGREGATE PER CURRENCY ====================================== */ BB_PTD_AGGREGATES AS ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE, MAX(PAY_DATE) PAY_DATE, SUM( CASE WHEN PERIOD_TYPE='BB_OLD' THEN PAY_AMT_PAY_CURR ELSE 0 END ) BB_TOTAL_EARNINGS, SUM( CASE WHEN PERIOD_TYPE='BB_CURRENT' THEN PAY_AMT_PAY_CURR ELSE 0 END ) PTD_TOTAL_EARNINGS FROM BASE_PAYMENTS_ALL GROUP BY PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE ) /* ====================================== FINAL RESULT ====================================== */ SELECT ParentRecordNumber, RecordName, RecordNumber, BBInMultiCurrency_c, BBHomeCurrency_c, BBInHomeCurrency_c, BBInLatestHomeCurrency_c, BBLatestHomeCurrency_c, BBConversionFactorHCToLHC_c, PTDInMultiCurrency_c, PTDEarningsHomeCurrency_c, PTDEarningsInHomeCurrency_c, PTDEarningsInLatestHomeCurrency_c, PTDEarningsLatestHomeCurrency_c, PTDEarningsConversionFactorHCToLHC_c, PAYRUN_ID, PAYMENT_BATCH_PERIOD, Period_c, PAYEE_PARTICIPANT_ID, MultiCurrencyScenario_c FROM ( /* ====================== BB MULTI CURRENCY ====================== */ SELECT agg.PAYRUN_ID, agg.PAYMENT_BATCH_PERIOD, agg.PAYMENT_BATCH_PERIOD Period_c, agg.PAYEE_PARTICIPANT_ID, 'BB MC' MultiCurrencyScenario_c, agg.HOME_CURRENCY_CODE BBHomeCurrency_c, (agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number) ParentRecordNumber, (agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-BBMC-'||agg.HOME_CURRENCY_CODE) RecordName, (agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-BBMC-'||agg.HOME_CURRENCY_CODE) RecordNumber, TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS,4),'FM9999999990.0000') BBInMultiCurrency_c, TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS,4),'FM9999999990.0000') BBInHomeCurrency_c, TO_CHAR( ROUND( agg.BB_TOTAL_EARNINGS * NVL(cv1.CONVERSION_RATE,1) * (1/NVL(cv2.CONVERSION_RATE,1)) ,4),'FM9999999990.0000') BBInLatestHomeCurrency_c, pd.CURRENCY_CODE BBLatestHomeCurrency_c, TO_CHAR( ROUND( NVL(cv1.CONVERSION_RATE,1) * (1/NVL(cv2.CONVERSION_RATE,1)) ,4),'FM9999999990.0000') BBConversionFactorHCToLHC_c, NULL,NULL,NULL,NULL,NULL,NULL FROM BB_PTD_AGGREGATES agg INNER JOIN PERSON_NUMBERS pn ON pn.participant_id = agg.PAYEE_PARTICIPANT_ID INNER JOIN PARTICIPANT_DETAILS_CACHE pd ON pd.PARTICIPANT_ID = agg.PAYEE_PARTICIPANT_ID AND agg.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE,agg.PAY_DATE) LEFT JOIN CONVERSION_RATES_USD cv1 ON cv1.FROM_CURRENCY = agg.HOME_CURRENCY_CODE AND TRUNC(agg.PAY_DATE) BETWEEN cv1.CONVERSION_START_DATE AND cv1.CONVERSION_END_DATE LEFT JOIN CONVERSION_RATES_USD cv2 ON cv2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(agg.PAY_DATE) BETWEEN cv2.CONVERSION_START_DATE AND cv2.CONVERSION_END_DATE WHERE agg.BB_TOTAL_EARNINGS <> 0 UNION ALL /* ====================== PTD MULTI CURRENCY ====================== */ SELECT agg.PAYRUN_ID, agg.PAYMENT_BATCH_PERIOD, agg.PAYMENT_BATCH_PERIOD, agg.PAYEE_PARTICIPANT_ID, 'PTD MC', NULL, (agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number), (agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-PDMC-'||agg.HOME_CURRENCY_CODE), (agg.PAYMENT_BATCH_PERIOD||'-REGULAR-'||pn.person_number||'-PDMC-'||agg.HOME_CURRENCY_CODE), NULL,NULL,NULL,NULL,NULL,NULL, TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS,4),'FM9999999990.0000'), agg.HOME_CURRENCY_CODE, TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS,4),'FM9999999990.0000'), TO_CHAR( ROUND( agg.PTD_TOTAL_EARNINGS * NVL(cv1.CONVERSION_RATE,1) * (1/NVL(cv2.CONVERSION_RATE,1)) ,4),'FM9999999990.0000'), pd.CURRENCY_CODE, TO_CHAR( ROUND( NVL(cv1.CONVERSION_RATE,1) * (1/NVL(cv2.CONVERSION_RATE,1)) ,4),'FM9999999990.0000') FROM BB_PTD_AGGREGATES agg INNER JOIN PERSON_NUMBERS pn ON pn.participant_id = agg.PAYEE_PARTICIPANT_ID INNER JOIN PARTICIPANT_DETAILS_CACHE pd ON pd.PARTICIPANT_ID = agg.PAYEE_PARTICIPANT_ID AND agg.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE,agg.PAY_DATE) LEFT JOIN CONVERSION_RATES_USD cv1 ON cv1.FROM_CURRENCY = agg.HOME_CURRENCY_CODE AND TRUNC(agg.PAY_DATE) BETWEEN cv1.CONVERSION_START_DATE AND cv1.CONVERSION_END_DATE LEFT JOIN CONVERSION_RATES_USD cv2 ON cv2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(agg.PAY_DATE) BETWEEN cv2.CONVERSION_START_DATE AND cv2.CONVERSION_END_DATE WHERE agg.PTD_TOTAL_EARNINGS <> 0 )

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika