dev 6 - child query fixed v2

WITH -- Multi-currency flags BB_MULTI_CURRENCY AS ( SELECT DISTINCT PB.PAYRUN_ID, PB.PAYMENT_BATCH_PERIOD, CASE WHEN COUNT(DISTINCT SPD.HOME_CURRENCY_CODE) OVER ( PARTITION BY PB.PAYRUN_ID, PB.PAYMENT_BATCH_PERIOD ) > 1 THEN 'Y' ELSE 'N' END AS MULTI_CURRENCY_YN FROM CN_TP_PAYRUNS_ALL PB INNER JOIN CN_SRP_PYMT_DETAILS SPD ON SPD.PAYRUN_ID = PB.PAYRUN_ID AND SPD.PAYMENT_BATCH_PERIOD = PB.PAYMENT_BATCH_PERIOD WHERE PB.PERIOD_ID = :P_PERIOD_ID AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID) ), PERIOD_MULTI_CURRENCY AS ( SELECT DISTINCT PB.PAYRUN_ID, CASE WHEN COUNT(DISTINCT SPD.HOME_CURRENCY_CODE) OVER ( PARTITION BY PB.PAYRUN_ID ) > 1 THEN 'Y' ELSE 'N' END AS MULTI_CURRENCY_YN FROM CN_TP_PAYRUNS_ALL PB INNER JOIN CN_SRP_PYMT_DETAILS SPD ON SPD.PAYRUN_ID = PB.PAYRUN_ID WHERE PB.PERIOD_ID = :P_PERIOD_ID AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID) ), -- USD conversion rates CONVERSION_RATES_USD AS ( SELECT FROM_CURRENCY, TO_CURRENCY, CONVERSION_DATE, CONVERSION_RATE, ROW_NUMBER() OVER ( PARTITION BY FROM_CURRENCY, TO_CURRENCY, TRUNC(CONVERSION_DATE) ORDER BY CONVERSION_DATE DESC ) AS rn FROM GL_DAILY_RATES WHERE TO_CURRENCY = 'USD' AND CONVERSION_TYPE = 'Corporate' ), -- Latest plan component per participant LATEST_PLAN_COMPONENT AS ( SELECT PART_PAY.PARTICIPANT_ID, PART_PAY.COMP_PLAN_ID, PART_PAY.ROLE_ID, ROW_NUMBER() OVER ( PARTITION BY PART_PAY.PARTICIPANT_ID ORDER BY PART_PAY.START_DATE DESC ) AS rn FROM CN_TP_PAYRUNS_ALL PB INNER JOIN CN_PARTICIPANT_PAY PART_PAY ON PB.ORG_ID = PART_PAY.ORG_ID AND PART_PAY.START_DATE <= PB.END_DATE AND NVL(PART_PAY.END_DATE, PB.END_DATE) >= PB.START_DATE WHERE PB.PERIOD_ID = :P_PERIOD_ID AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID) ), -- Person numbers lookup PERSON_NUMBERS AS ( SELECT PERSON_ID, PERSON_NUMBER FROM PER_ALL_PEOPLE_F WHERE SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ), -- Participant details cache PARTICIPANT_DETAILS_CACHE AS ( SELECT P.PARTICIPANT_ID, P.NAME AS PARTICIPANT_NAME, PN.PERSON_NUMBER, LPC.COMP_PLAN_ID, CP.NAME AS COMP_PLAN_NAME, LPC.ROLE_ID, R.NAME AS ROLE_NAME FROM CN_PARTICIPANTS P LEFT JOIN PERSON_NUMBERS PN ON PN.PERSON_ID = P.PERSON_ID LEFT JOIN LATEST_PLAN_COMPONENT LPC ON LPC.PARTICIPANT_ID = P.PARTICIPANT_ID AND LPC.rn = 1 LEFT JOIN CN_COMP_PLANS_VL CP ON CP.COMP_PLAN_ID = LPC.COMP_PLAN_ID LEFT JOIN CN_ROLES_VL R ON R.ROLE_ID = LPC.ROLE_ID ), -- Paysheets cache PAYSHEETS_CACHE AS ( SELECT PS.PAYRUN_ID, PS.PARTICIPANT_ID, PS.CREDITED_FLAG, PS.PAYSHEET_STATUS FROM CN_PAYSHEETS PS WHERE PS.PAYSHEET_STATUS IN ('CALCULATED', 'FROZEN', 'HELD') ), -- Payruns cache PAYRUNS_CACHE AS ( SELECT PB.PAYRUN_ID, PB.NAME AS PAYRUN_NAME, PB.PAYMENT_BATCH_PERIOD, PB.START_DATE AS PAYRUN_START_DATE, PB.END_DATE AS PAYRUN_END_DATE, PB.PAY_DATE AS PAYRUN_PAY_DATE, PER.NAME AS PERIOD_NAME FROM CN_TP_PAYRUNS_ALL PB INNER JOIN CN_PERIODS PER ON PER.PERIOD_ID = PB.PERIOD_ID WHERE PB.PERIOD_ID = :P_PERIOD_ID AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID) ), -- Base payments (transaction level) BASE_PAYMENTS_ALL AS ( SELECT PB.PAYRUN_ID, PB.PAYMENT_BATCH_PERIOD, SPD.PAYEE_PARTICIPANT_ID, SPD.CREDITED_PARTICIPANT_ID, SPD.HOME_CURRENCY_CODE, PB.PAY_DATE, 'BB' AS SCENARIO_TYPE, CASE WHEN SPD.PAYMENT_BATCH_PERIOD < PB.PAYMENT_BATCH_PERIOD THEN 'BB_OLD' ELSE 'BB_CURRENT' END AS PERIOD_TYPE, SPD.AMOUNT AS PAY_AMT_PAY_CURR FROM CN_TP_PAYRUNS_ALL PB INNER JOIN CN_SRP_PYMT_DETAILS SPD ON SPD.PAYRUN_ID = PB.PAYRUN_ID WHERE PB.PERIOD_ID = :P_PERIOD_ID AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID) AND SPD.PAYMENT_BATCH_PERIOD <= PB.PAYMENT_BATCH_PERIOD UNION ALL SELECT PB.PAYRUN_ID, PB.PAYMENT_BATCH_PERIOD, SPD.PAYEE_PARTICIPANT_ID, SPD.CREDITED_PARTICIPANT_ID, SPD.HOME_CURRENCY_CODE, PB.PAY_DATE, 'PERIOD' AS SCENARIO_TYPE, 'PERIOD' AS PERIOD_TYPE, SPD.AMOUNT AS PAY_AMT_PAY_CURR FROM CN_TP_PAYRUNS_ALL PB INNER JOIN CN_SRP_PYMT_DETAILS SPD ON SPD.PAYRUN_ID = PB.PAYRUN_ID WHERE PB.PERIOD_ID = :P_PERIOD_ID AND (:P_PAYRUN_ID = 'ALL' OR PB.PAYRUN_ID = :P_PAYRUN_ID) ), -- AGGREGATED payments (one row per currency/period type) BASE_PAYMENTS_AGGREGATED AS ( SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.CREDITED_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.SCENARIO_TYPE, spd.PERIOD_TYPE, spd.PAY_DATE, SUM(spd.PAY_AMT_PAY_CURR) AS TOTAL_PAY_AMT FROM BASE_PAYMENTS_ALL spd GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.CREDITED_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.SCENARIO_TYPE, spd.PERIOD_TYPE, spd.PAY_DATE ), -- PTD aggregates for MC columns BB_PTD_AGGREGATES AS ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE, SUM(CASE WHEN PERIOD_TYPE = 'BB_OLD' THEN TOTAL_PAY_AMT ELSE 0 END) AS BB_TOTAL_EARNINGS, SUM(CASE WHEN PERIOD_TYPE = 'BB_CURRENT' THEN TOTAL_PAY_AMT ELSE 0 END) AS PTD_TOTAL_EARNINGS FROM BASE_PAYMENTS_AGGREGATED WHERE SCENARIO_TYPE = 'BB' GROUP BY PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE ) -- MAIN QUERY SELECT PR.PAYRUN_NAME, PR.PAYRUN_START_DATE, PR.PAYRUN_END_DATE, PR.PAYRUN_PAY_DATE, PR.PERIOD_NAME, PD.PERSON_NUMBER, PD.PARTICIPANT_NAME, PD.COMP_PLAN_NAME, PD.ROLE_NAME, spd.HOME_CURRENCY_CODE AS "Currency", -- BB OLD spd.TOTAL_PAY_AMT AS "BB PTD Earning LC", ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) AS "BB PTD Earning USD", CASE WHEN MC.MULTI_CURRENCY_YN = 'Y' THEN ROUND(agg.BB_TOTAL_EARNINGS * NVL(CR.CONVERSION_RATE, 1), 2) ELSE NULL END AS "MC BB PTD Earning USD", spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, 'BB_OLD' AS SOURCE_TYPE FROM BASE_PAYMENTS_AGGREGATED spd INNER JOIN PAYRUNS_CACHE PR ON PR.PAYRUN_ID = spd.PAYRUN_ID AND PR.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD INNER JOIN PARTICIPANT_DETAILS_CACHE PD ON PD.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID LEFT JOIN BB_MULTI_CURRENCY MC ON MC.PAYRUN_ID = spd.PAYRUN_ID AND MC.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD LEFT JOIN CONVERSION_RATES_USD CR ON CR.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND CR.CONVERSION_DATE = TRUNC(spd.PAY_DATE) AND CR.rn = 1 LEFT JOIN BB_PTD_AGGREGATES agg ON agg.PAYRUN_ID = spd.PAYRUN_ID AND agg.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD AND agg.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID AND agg.HOME_CURRENCY_CODE = spd.HOME_CURRENCY_CODE WHERE spd.SCENARIO_TYPE = 'BB' AND spd.PERIOD_TYPE = 'BB_OLD' UNION ALL -- BB CURRENT SELECT PR.PAYRUN_NAME, PR.PAYRUN_START_DATE, PR.PAYRUN_END_DATE, PR.PAYRUN_PAY_DATE, PR.PERIOD_NAME, PD.PERSON_NUMBER, PD.PARTICIPANT_NAME, PD.COMP_PLAN_NAME, PD.ROLE_NAME, spd.HOME_CURRENCY_CODE AS "Currency", spd.TOTAL_PAY_AMT AS "BB PTD Earning LC", ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) AS "BB PTD Earning USD", CASE WHEN MC.MULTI_CURRENCY_YN = 'Y' THEN ROUND(agg.PTD_TOTAL_EARNINGS * NVL(CR.CONVERSION_RATE, 1), 2) ELSE NULL END AS "MC BB PTD Earning USD", spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, 'BB_CURRENT' AS SOURCE_TYPE FROM BASE_PAYMENTS_AGGREGATED spd INNER JOIN PAYRUNS_CACHE PR ON PR.PAYRUN_ID = spd.PAYRUN_ID AND PR.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD INNER JOIN PARTICIPANT_DETAILS_CACHE PD ON PD.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID LEFT JOIN BB_MULTI_CURRENCY MC ON MC.PAYRUN_ID = spd.PAYRUN_ID AND MC.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD LEFT JOIN CONVERSION_RATES_USD CR ON CR.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND CR.CONVERSION_DATE = TRUNC(spd.PAY_DATE) AND CR.rn = 1 LEFT JOIN BB_PTD_AGGREGATES agg ON agg.PAYRUN_ID = spd.PAYRUN_ID AND agg.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD AND agg.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID AND agg.HOME_CURRENCY_CODE = spd.HOME_CURRENCY_CODE WHERE spd.SCENARIO_TYPE = 'BB' AND spd.PERIOD_TYPE = 'BB_CURRENT' UNION ALL -- PERIOD SELECT PR.PAYRUN_NAME, PR.PAYRUN_START_DATE, PR.PAYRUN_END_DATE, PR.PAYRUN_PAY_DATE, PR.PERIOD_NAME, PD.PARTICIPANT_NAME, PD.PERSON_NUMBER, PD.COMP_PLAN_NAME, PD.ROLE_NAME, spd.HOME_CURRENCY_CODE AS "Currency", spd.TOTAL_PAY_AMT AS "Period Earning LC", ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) AS "Period Earning USD", CASE WHEN PMC.MULTI_CURRENCY_YN = 'Y' THEN ROUND(spd.TOTAL_PAY_AMT * NVL(CR.CONVERSION_RATE, 1), 2) ELSE NULL END AS "MC Period Earning USD", spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.CREDITED_PARTICIPANT_ID, 'PERIOD' AS SOURCE_TYPE FROM BASE_PAYMENTS_AGGREGATED spd INNER JOIN PAYRUNS_CACHE PR ON PR.PAYRUN_ID = spd.PAYRUN_ID AND PR.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD INNER JOIN PAYSHEETS_CACHE PS ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = spd.PAYRUN_ID INNER JOIN PARTICIPANT_DETAILS_CACHE PD ON PD.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID LEFT JOIN PERIOD_MULTI_CURRENCY PMC ON PMC.PAYRUN_ID = spd.PAYRUN_ID LEFT JOIN CONVERSION_RATES_USD CR ON CR.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND CR.CONVERSION_DATE = TRUNC(spd.PAY_DATE) AND CR.rn = 1 WHERE spd.SCENARIO_TYPE = 'PERIOD' AND PS.CREDITED_FLAG = 'Y' ORDER BY PAYRUN_NAME, PERSON_NUMBER, SOURCE_TYPE, "Currency";

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika