PTD fxed

WITH RANKED_PDX AS ( SELECT PARTICIPANT_ID, ORG_ID, CURRENCY_CODE, START_DATE, END_DATE, ROW_NUMBER() OVER (PARTITION BY PARTICIPANT_ID, ORG_ID ORDER BY END_DATE DESC NULLS FIRST, START_DATE DESC) AS RN FROM CN_SRP_PARTICIPANT_DETAILS_ALL WHERE PARTICIPANT_ID = NVL(:participant_id, PARTICIPANT_ID) ), CONVERSION_RATES 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' ) SELECT PAYRUN_ID, CREDITED_PARTICIPANT_ID, ORG_ID, PAY_PERIOD_ID, TOTAL_PTD_EARNINGS_USD, LATEST_HOME_CURRENCY, CURRENCIES_LIST, EARNINGS_USD_LIST, EARNINGS_LC_LIST FROM ( SELECT ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID, SUM(ptd_usd.PTD_EARNINGS_USD) OVER (PARTITION BY ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID) AS TOTAL_PTD_EARNINGS_USD, PDX.CURRENCY_CODE AS LATEST_HOME_CURRENCY, LISTAGG(ptd_usd.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS CURRENCIES_LIST, LISTAGG(TO_CHAR(ptd_usd.PTD_EARNINGS_USD, 'FM9999999990.0000'), ', ') WITHIN GROUP (ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS EARNINGS_USD_LIST, LISTAGG( TO_CHAR( ptd_usd.PTD_EARNINGS_USD * ( 1 / ( NVL( (CASE WHEN PDX.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), 'FM9999999990.0000' ), ', ' ) WITHIN GROUP (ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS EARNINGS_LC_LIST, ROW_NUMBER() OVER (PARTITION BY ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID ORDER BY ptd_usd.HOME_CURRENCY_CODE) AS RN FROM ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, P.HOME_CURRENCY_CODE, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PTD_EARNINGS_USD, PD.CURRENCY_CODE AS LATEST_HOME_CURRENCY FROM CN_TP_PARTICIPANT_PAY_ALL P INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.ORG_ID = PD.ORG_ID LEFT JOIN CONVERSION_RATES conversion ON TRUNC( CASE WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE ELSE PB.PAY_DATE END ) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.FROM_CURRENCY = P.HOME_CURRENCY_CODE WHERE ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE)) OR (P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PB.PAY_DATE)) ) AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID -- AND P.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ') AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, P.HOME_CURRENCY_CODE, PD.CURRENCY_CODE ) ptd_usd INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = ptd_usd.PAYRUN_ID AND PB.ORG_ID = ptd_usd.ORG_ID INNER JOIN RANKED_PDX PDX ON ptd_usd.CREDITED_PARTICIPANT_ID = PDX.PARTICIPANT_ID AND ptd_usd.ORG_ID = PDX.ORG_ID AND PDX.RN = 1 AND TRUNC(PB.PAY_DATE) BETWEEN PDX.START_DATE AND NVL(PDX.END_DATE, PB.PAY_DATE) INNER JOIN CONVERSION_RATES conversion2 ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.FROM_CURRENCY = PDX.CURRENCY_CODE ) WHERE RN = 1

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika