new check

SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAY_CURRENCY_CODE, latest_currency_code, -- Multi-currency scenario type 'Beginning Balance' AS MC_SCENARIO_TYPE, -- Home currency (kept separate) HOME_CURRENCY_CODE AS BB_HOME_CURRENCY_CODE, NULL AS PTD_HOME_CURRENCY_CODE, -- Parent Record Name and Number (without home currency) "Parent Record Name", "Parent Record Number", -- Record Name and Record Number (with home currency) "Record Name", "Record Number", -- Beginning Balance columns "BB PTD Earning LC", "BB Conversion Rate (LC_to_USD)", "BB PTD Earning (USD)", "BB Conversion Rate (USD_to_Latest_LC)", "BB PTD Earning (Latest HC)", -- Period columns (NULL for BB records) NULL AS "PTD Earning LC", NULL AS "Conversion Rate (LC_to_USD)", NULL AS "PTD Earning (USD)", NULL AS "Conversion Rate (USD_to_Latest_LC)", NULL AS "PTD Earning (Latest HC)", -- Single MC Earnings column "MC Earnings" FROM ( -- Beginning Balance Multi-Currency Query SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE AS latest_currency_code, -- Parent Record Name (without home currency) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "Parent Record Name", -- Parent Record Number (same as Parent Record Name) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "Parent Record Number", -- Record Name (with home currency) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) || '-' || spd.HOME_CURRENCY_CODE ) AS "Record Name", -- Record Number (with home currency) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) || '-' || spd.HOME_CURRENCY_CODE ) AS "Record Number", SUM(spd.PAY_AMT_PAY_CURR) AS "BB PTD Earning LC", NVL( MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ), 1 ) AS "BB Conversion Rate (LC_to_USD)", SUM(spd.PAY_AMT_PAY_CURR) * MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ) AS "BB PTD Earning (USD)", TO_CHAR( MAX( CASE WHEN pd.CURRENCY_CODE = 'USD' THEN 1 ELSE 1 / conversion2.CONVERSION_RATE END ), '0000000000000.0000000000000' ) AS "BB Conversion Rate (USD_to_Latest_LC)", TO_CHAR( SUM(spd.PAY_AMT_PAY_CURR) * MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ) * MAX( CASE WHEN pd.CURRENCY_CODE = 'USD' THEN 1 ELSE 1 / conversion2.CONVERSION_RATE END ), '0000000000000.0000000000000' ) AS "BB PTD Earning (Latest HC)", MAX(PS.PMT_TXN_AMT_DUE) AS "MC Earnings" FROM CN_TP_PARTICIPANT_PAY_EXP_V spd INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL pd ON spd.PAYEE_PARTICIPANT_ID = pd.PARTICIPANT_ID AND spd.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE, spd.PAY_DATE) INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = spd.PAYRUN_ID AND PS.QUOTA_ID IS NULL -- Join to get PAYRUN_NAME INNER JOIN CN_TP_PAYRUNS_ALL pr ON pr.PAYRUN_ID = spd.PAYRUN_ID -- LATEST_PLAN_COMPONENT: Latest plan component per participant/period INNER JOIN ( SELECT PARTICIPANT_ID, PAYRUN_NAME, ORG_ID, PAYRUN_ID, PERIOD_ID, PLAN_COMPONENT_ID, INCENTIVE_TYPE, ROLE_NAME, PAY_GROUP_NAME, START_DATE, END_DATE FROM ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PB.PAYRUN_NAME, PART_PAY.ORG_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID AS PERIOD_ID, PART_PAY.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, roles.ROLE_NAME, PG.PAY_GROUP_NAME, TRUNC(SRP_CP.START_DATE) AS START_DATE, TRUNC(SRP_CP.END_DATE) AS END_DATE, DENSE_RANK() OVER ( PARTITION BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.ORG_ID, PB.PAY_PERIOD_ID, PB.PAYRUN_ID ORDER BY PART_PAY.SOURCE_EVENT_DATE DESC ) AS LATEST_PC_RECORD FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID AND PB.ORG_ID = PART_PAY.ORG_ID AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE INNER JOIN CN_PAY_GROUPS_ALL_TL PG ON PB.PAY_GROUP_ID = PG.PAY_GROUP_ID AND PG.LANGUAGE = 'US' INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID INNER JOIN CN_SRP_PLAN_COMPONENTS_ALL SRP_PC ON SRP_PC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID AND SRP_PC.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID INNER JOIN CN_PARTICIPANT_ROLES cpr ON SRP_CP.SRP_RULE_ID = cpr.PARTICIPANT_ROLE_ID AND SRP_CP.PARTICIPANT_ID = cpr.PARTICIPANT_ID AND SRP_CP.SRP_RULE_TYPE_CODE = 'ROLE' INNER JOIN CN_ROLES_TL roles ON cpr.ROLE_ID = roles.ROLE_ID AND roles.LANGUAGE = 'US' WHERE 1 = 1 ) WHERE LATEST_PC_RECORD = 1 ) LATEST_PLAN_COMPONENT ON LATEST_PLAN_COMPONENT.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND LATEST_PLAN_COMPONENT.ORG_ID = spd.ORG_ID AND LATEST_PLAN_COMPONENT.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD AND LATEST_PLAN_COMPONENT.PAYRUN_ID = spd.PAYRUN_ID LEFT JOIN ( 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' ) conversion ON SPD.HOME_CURRENCY_CODE != 'USD' AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE LEFT JOIN ( 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' ) conversion2 ON pd.CURRENCY_CODE != 'USD' AND conversion2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE WHERE 1 = 1 AND spd.PAYRUN_ID = NVL(:payrun_id, spd.PAYRUN_ID) AND spd.PAYEE_PARTICIPANT_ID = NVL(:participant_id, spd.PAYEE_PARTICIPANT_ID) AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, spd.PAYMENT_BATCH_PERIOD) GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE, spd.CREDITED_PARTICIPANT_ID, pr.PAYRUN_NAME, LATEST_PLAN_COMPONENT.INCENTIVE_TYPE ) MCChild_BB WHERE EXISTS ( SELECT 1 FROM ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID 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 1 = 1 GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD GROUP BY PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAYRUN_ID ) pay_2 ON pay_1.PAYRUN_ID = pay_2.PAYRUN_ID AND pay_1.PAYMENT_BATCH_PERIOD = pay_2.PAYMENT_BATCH_PERIOD AND pay_1.PAYEE_PARTICIPANT_ID = pay_2.PAYEE_PARTICIPANT_ID GROUP BY pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID HAVING COUNT(DISTINCT pay_1.HOME_CURRENCY_CODE) > 1 ) bb_MultiC WHERE MCChild_BB.PAYEE_PARTICIPANT_ID = bb_MultiC.PAYEE_PARTICIPANT_ID AND MCChild_BB.PAYRUN_ID = bb_MultiC.PAYRUN_ID AND MCChild_BB.PAYMENT_BATCH_PERIOD = bb_MultiC.PAYMENT_BATCH_PERIOD ) UNION ALL SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAY_CURRENCY_CODE, latest_currency_code, -- Multi-currency scenario type 'Period' AS MC_SCENARIO_TYPE, -- Home currency (kept separate) NULL AS BB_HOME_CURRENCY_CODE, HOME_CURRENCY_CODE AS PTD_HOME_CURRENCY_CODE, -- Parent Record Name and Number (without home currency) "Parent Record Name", "Parent Record Number", -- Record Name and Record Number (with home currency) "Record Name", "Record Number", -- Beginning Balance columns (NULL for Period records) NULL AS "BB PTD Earning LC", NULL AS "BB Conversion Rate (LC_to_USD)", NULL AS "BB PTD Earning (USD)", NULL AS "BB Conversion Rate (USD_to_Latest_LC)", NULL AS "BB PTD Earning (Latest HC)", -- Period columns "PTD Earning LC", "Conversion Rate (LC_to_USD)", "PTD Earning (USD)", "Conversion Rate (USD_to_Latest_LC)", "PTD Earning (Latest HC)", -- Single MC Earnings column "MC Earnings" FROM ( -- Period Multi-Currency Query SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE AS latest_currency_code, -- Parent Record Name (without home currency) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "Parent Record Name", -- Parent Record Number (same as Parent Record Name) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "Parent Record Number", -- Record Name (with home currency) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) || '-' || spd.HOME_CURRENCY_CODE ) AS "Record Name", -- Record Number (with home currency) TO_CHAR( spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR( pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || ( SELECT papf.person_number 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 spd.CREDITED_PARTICIPANT_ID = cspl.participant_id AND ROWNUM = 1 ) || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) || '-' || spd.HOME_CURRENCY_CODE ) AS "Record Number", SUM(spd.PAY_AMT_PAY_CURR) AS "PTD Earning LC", NVL( MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ), 1 ) AS "Conversion Rate (LC_to_USD)", SUM(spd.PAY_AMT_PAY_CURR) * MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ) AS "PTD Earning (USD)", TO_CHAR( MAX( CASE WHEN pd.CURRENCY_CODE = 'USD' THEN 1 ELSE 1 / conversion2.CONVERSION_RATE END ), '0000000000000.0000000000000' ) AS "Conversion Rate (USD_to_Latest_LC)", TO_CHAR( SUM(spd.PAY_AMT_PAY_CURR) * MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ) * MAX( CASE WHEN pd.CURRENCY_CODE = 'USD' THEN 1 ELSE 1 / conversion2.CONVERSION_RATE END ), '0000000000000.0000000000000' ) AS "PTD Earning (Latest HC)", MAX(PS.PMT_TXN_AMT_DUE) AS "MC Earnings" FROM CN_TP_PARTICIPANT_PAY_EXP_V spd INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL pd ON spd.PAYEE_PARTICIPANT_ID = pd.PARTICIPANT_ID AND spd.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE, spd.PAY_DATE) INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = spd.PAYRUN_ID AND PS.QUOTA_ID IS NULL -- Join to get PAYRUN_NAME INNER JOIN CN_TP_PAYRUNS_ALL pr ON pr.PAYRUN_ID = spd.PAYRUN_ID -- LATEST_PLAN_COMPONENT: Latest plan component per participant/period INNER JOIN ( SELECT PARTICIPANT_ID, PAYRUN_NAME, ORG_ID, PAYRUN_ID, PERIOD_ID, PLAN_COMPONENT_ID, INCENTIVE_TYPE, ROLE_NAME, PAY_GROUP_NAME, START_DATE, END_DATE FROM ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PB.PAYRUN_NAME, PART_PAY.ORG_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID AS PERIOD_ID, PART_PAY.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, roles.ROLE_NAME, PG.PAY_GROUP_NAME, TRUNC(SRP_CP.START_DATE) AS START_DATE, TRUNC(SRP_CP.END_DATE) AS END_DATE, DENSE_RANK() OVER ( PARTITION BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.ORG_ID, PB.PAY_PERIOD_ID, PB.PAYRUN_ID ORDER BY PART_PAY.SOURCE_EVENT_DATE DESC ) AS LATEST_PC_RECORD FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID AND PB.ORG_ID = PART_PAY.ORG_ID AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE INNER JOIN CN_PAY_GROUPS_ALL_TL PG ON PB.PAY_GROUP_ID = PG.PAY_GROUP_ID AND PG.LANGUAGE = 'US' INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID INNER JOIN CN_SRP_PLAN_COMPONENTS_ALL SRP_PC ON SRP_PC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID AND SRP_PC.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID INNER JOIN CN_PARTICIPANT_ROLES cpr ON SRP_CP.SRP_RULE_ID = cpr.PARTICIPANT_ROLE_ID AND SRP_CP.PARTICIPANT_ID = cpr.PARTICIPANT_ID AND SRP_CP.SRP_RULE_TYPE_CODE = 'ROLE' INNER JOIN CN_ROLES_TL roles ON cpr.ROLE_ID = roles.ROLE_ID AND roles.LANGUAGE = 'US' WHERE 1 = 1 ) WHERE LATEST_PC_RECORD = 1 ) LATEST_PLAN_COMPONENT ON LATEST_PLAN_COMPONENT.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND LATEST_PLAN_COMPONENT.ORG_ID = spd.ORG_ID AND LATEST_PLAN_COMPONENT.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD AND LATEST_PLAN_COMPONENT.PAYRUN_ID = spd.PAYRUN_ID LEFT JOIN ( 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' ) conversion ON SPD.HOME_CURRENCY_CODE != 'USD' AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE LEFT JOIN ( 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' ) conversion2 ON pd.CURRENCY_CODE != 'USD' AND conversion2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE WHERE 1 = 1 AND spd.PAYRUN_ID = NVL(:payrun_id, spd.PAYRUN_ID) AND spd.PAYEE_PARTICIPANT_ID = NVL(:participant_id, spd.PAYEE_PARTICIPANT_ID) AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, spd.PAYMENT_BATCH_PERIOD) GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE, spd.CREDITED_PARTICIPANT_ID, pr.PAYRUN_NAME, LATEST_PLAN_COMPONENT.INCENTIVE_TYPE ) MCChild_PTD WHERE EXISTS ( SELECT 1 FROM ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID, CASE WHEN COUNT(DISTINCT MULTI_CURR.HOME_CURRENCY_CODE) > 1 THEN 'Y' ELSE 'N' END AS MULTI_CURR_YN FROM ( SELECT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PD.CURRENCY_CODE AS HOME_CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_ID, PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID, SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) AS TotalAmt FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID AND PART_PAY.SOURCE_EVENT_DATE IS NOT NULL AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE) WHERE 1 = 1 GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PD.CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_ID, PART_PAY.PAYMENT_BATCH_PERIOD HAVING SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) <> 0 ) MULTI_CURR GROUP BY MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID ) PRD_ML WHERE PRD_ML.MULTI_CURR_YN = 'Y' AND MCChild_PTD.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID AND MCChild_PTD.PAYRUN_ID = PRD_ML.PAYRUN_ID AND MCChild_PTD.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID );

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where