BG with BB amount

/* 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 cspl.participant_id, 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 cspl.participant_id = NVL(:participant_id, cspl.participant_id) ), -- Materialize participant details once - FILTERED PARTICIPANT_DETAILS_CACHE AS ( SELECT a.analyst_id, b.PARTICIPANT_ID, b.PARTICIPANT_DETAIL_ID, b.ORG_ID, b.START_DATE, b.END_DATE, b.COUNTRY_CODE, b.ATTRIBUTE_CHAR40, b.ATTRIBUTE_NUMBER1, b.CURRENCY_CODE, b.ATTRIBUTE_CHAR2, b.ATTRIBUTE_CHAR17 FROM CN_SRP_PARTICIPANTS_ALL a INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL b ON a.participant_id = b.participant_id WHERE b.PARTICIPANT_ID = NVL(:participant_id, b.PARTICIPANT_ID) ), -- Materialize multi-currency detection - FILTERED BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag FROM ( SELECT P.PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) GROUP BY PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAYRUN_ID, 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 AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.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(1) > 1 ), -- FILTERED PERIOD_MULTI_CURRENCY AS ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID, 'Y' 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 BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE) WHERE PART_PAY.SOURCE_EVENT_DATE IS NOT NULL AND PART_PAY.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID) AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID) AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD) 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 HAVING COUNT(MULTI_CURR.HOME_CURRENCY_CODE) > 1 ), MULTI_CURRENCY_FLAGS AS ( SELECT NVL(bb_MultiC.PAYRUN_ID, PRD_ML.PAYRUN_ID) AS PAYRUN_ID, NVL(bb_MultiC.PAYMENT_BATCH_PERIOD, PRD_ML.PERIOD_ID) AS PERIOD_ID, NVL(bb_MultiC.PAYEE_PARTICIPANT_ID, PRD_ML.PARTICIPANT_ID) AS PARTICIPANT_ID, NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag, NVL(PRD_ML.MULTI_CURR_YN, 'N') AS MULTI_CURR_YN FROM BB_MULTI_CURRENCY bb_MultiC FULL OUTER JOIN PERIOD_MULTI_CURRENCY PRD_ML ON bb_MultiC.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID AND bb_MultiC.PAYRUN_ID = PRD_ML.PAYRUN_ID AND bb_MultiC.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID WHERE NVL(PRD_ML.MULTI_CURR_YN, 'N') = 'Y' OR NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') = 'Y' ), -- Materialize base payment data - FILTERED BASE_PAYMENTS AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_ID, PG.PAY_GROUP_NAME, P.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, PS.PAYSHEET_ID, PS.BONUS_PTD, PS.PMT_TXN_AMT_DUE, ( SELECT meaning FROM cn_lookups WHERE lookup_type = 'CN_PAYSHEET_STATUS' AND lookup_code = PS.OBJECT_STATUS AND enabled_flag = 'Y' AND lookup_code != 'REVIEWED' ) AS PAYSHEET_STATUS, P.PAY_AMT_PAY_CURR, P.HOME_CURRENCY_CODE, P.PAY_CURRENCY_CODE, P.SOURCE_EVENT_DATE 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_PAY_GROUPS_ALL_VL PG ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID AND PG.ORG_ID = P.ORG_ID INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = P.PAYRUN_ID AND PS.QUOTA_ID IS NULL 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 PB.PAYRUN_ID = NVL(:payrun_id, PB.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) ), -- Materialize participant detail for date range - FILTERED PARTICIPANT_DETAIL_DATERANGE AS ( SELECT PART_DTLS.PARTICIPANT_ID, PART_HDR.HR_PRIMARY_WORKER_NUMBER, PART_DTLS.PARTICIPANT_DETAIL_ID, PART_DTLS.ORG_ID AS PD_ORG_ID, PART_DTLS.START_DATE AS PD_START_DATE, PART_DTLS.END_DATE AS PD_END_DATE, PART_DTLS.COUNTRY_CODE AS HR_Country, PART_DTLS.ATTRIBUTE_CHAR40 AS Employment_Country, ( SELECT PARTY_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID ) AS ANALYST_PARTY_ID, PART_DTLS.ATTRIBUTE_NUMBER1 AS PD_HR_TI, PART_DTLS.CURRENCY_CODE AS LATEST_HOME_CURRENCY, PART_DTLS.ATTRIBUTE_CHAR2 AS HR_Status, PART_DTLS.ATTRIBUTE_CHAR17 AS SalesRep FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS INNER JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID WHERE PART_DTLS.PARTICIPANT_ID = NVL(:participant_id, PART_DTLS.PARTICIPANT_ID) ), -- Aggregate to USD PAYMENTS_USD AS ( SELECT SPD.ORG_ID, SPD.PAYRUN_ID, SPD.CREDITED_PARTICIPANT_ID, SPD.PAYRUN_NAME, SPD.PAY_PERIOD_ID, SPD.PAY_DATE, SPD.PAY_GROUP_ID, SPD.PAY_GROUP_NAME, SPD.PAYSHEET_ID, SPD.PAYSHEET_STATUS, SUM( SPD.PAY_AMT_PAY_CURR * NVL( (CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PAY_AMT_PAY_CURR_USD, MAX( SPD.PMT_TXN_AMT_DUE * NVL( (CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS paysheet_payout_usd, MAX(SPD.PMT_TXN_AMT_DUE) AS paysheet_payout_LC, MAX(partipant_detail.LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY FROM BASE_PAYMENTS SPD INNER JOIN CONVERSION_RATES_USD conversion ON TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE LEFT JOIN PARTICIPANT_DETAIL_DATERANGE partipant_detail ON TRUNC(SPD.PAY_DATE) BETWEEN partipant_detail.PD_START_DATE AND NVL(partipant_detail.PD_END_DATE, DATE '9999-12-31') AND SPD.CREDITED_PARTICIPANT_ID = partipant_detail.PARTICIPANT_ID AND SPD.ORG_ID = partipant_detail.PD_ORG_ID AND SPD.HOME_CURRENCY_CODE = partipant_detail.LATEST_HOME_CURRENCY GROUP BY SPD.ORG_ID, SPD.PAYRUN_ID, SPD.CREDITED_PARTICIPANT_ID, SPD.PAYRUN_NAME, SPD.PAY_PERIOD_ID, SPD.PAY_DATE, SPD.PAY_GROUP_ID, SPD.PAY_GROUP_NAME, SPD.PAYSHEET_ID, SPD.PAYSHEET_STATUS ), -- Convert back to local currency BG AS ( SELECT after_group.ORG_ID, after_group.PAYRUN_ID, after_group.CREDITED_PARTICIPANT_ID, after_group.PAYRUN_NAME, after_group.PAY_PERIOD_ID, after_group.PAY_DATE, after_group.PAY_GROUP_ID, after_group.PAY_GROUP_NAME, after_group.PAYSHEET_ID, after_group.PAY_AMT_PAY_CURR_USD, after_group.paysheet_payout_LC, after_group.LATEST_HOME_CURRENCY, after_group.PAYSHEET_STATUS, TO_CHAR( 1 / ( NVL( (CASE WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ), '00000000.000000000' ) AS conversionrate, TO_CHAR( after_group.PAY_AMT_PAY_CURR_USD * ( 1 / ( NVL( (CASE WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), '00000000.000000000' ) AS finalPayout, TO_CHAR( after_group.paysheet_payout_usd * ( 1 / ( NVL( (CASE WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), '00000000.000000000' ) AS finalPayout_paysheet FROM PAYMENTS_USD after_group INNER JOIN CONVERSION_RATES_USD conversion2 ON TRUNC(after_group.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.FROM_CURRENCY = after_group.LATEST_HOME_CURRENCY ), -- Materialize latest plan component - FILTERED LATEST_PLAN_COMPONENT AS ( SELECT PARTICIPANT_ID, PAYRUN_NAME, ORG_ID, PAYRUN_ID, PERIOD_ID, PLAN_COMPONENT_ID, PLAN_COMPONENT_name, 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, PC.Plan_component_name, 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 PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID) ) WHERE LATEST_PC_RECORD = 1 ), -- Materialize calendar data PAY_CAL AS ( SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) AS PAYMENT_BATCH_NAME, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) AS PAYGROUP_NAME, PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 AS PERIOD_ID, PAYRUN_CAL.CURRENCY_CODE, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR011) AS INCENTIVE_TYPE, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR012) AS COUNTRY, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP009) AS MPA_CUTOFF_START_DT, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010) AS MPA_CUTOFF_END_DT, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP007) AS PAYSHEET_CUTOFF_START_DT, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP012) AS PAYROLL_SUBMISSION_END_CST, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008) AS PAYSHEET_CUTOFF_DT FROM ZCA_REF_ENTITIES PAYRUN_CAL WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c' AND (:period_id IS NULL OR PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 = :period_id) ), --BB BB_AMT AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID", P.ORG_ID "ORG_ID", PB.PAY_PERIOD_ID "PAY_PERIOD_ID", SUM(P.PAY_AMT_PAY_CURR) B_BALANCE FROM CN_TP_PARTICIPANT_PAY_ALL P JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.ORG_ID = PD.ORG_ID 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.CREDITED_PARTICIPANT_ID = 134135 --AND P.ORG_ID = 300000008656040 --AND PAY_VW.PAY_PERIOD_ID = 2025007 GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID ), -- Materialize prorated TI - FILTERED PM_TI AS ( SELECT PARTICIPANT_ID, ORG_ID, PERIOD_ID, START_DATE, END_DATE, Prorated_MonthlyTI FROM ( SELECT SPFM.PARTICIPANT_ID, SPFM.ORG_ID, SPFM.PERIOD_ID, SRP_CP.START_DATE, SRP_CP.END_DATE, SPFM.OUTPUT_ACHIEVED_ITD AS Prorated_MonthlyTI, ROW_NUMBER() OVER ( PARTITION BY SPFM.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID ORDER BY NVL(SRP_CP.END_DATE, DATE '9999-12-31') DESC ) AS LATEST_TI_RECORD FROM CN_SRP_PER_FORM_METRICS_ALL SPFM INNER JOIN CN_FORMULAS_ALL_B Monthly_TI ON Monthly_TI.FORMULA_ID = SPFM.FORMULA_ID AND Monthly_TI.ATTRIBUTE9 = 'EmployeeEvents' AND Monthly_TI.ATTRIBUTE10 = 'MonthlyTI' INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP ON SRP_CP.PARTICIPANT_ID = SPFM.PARTICIPANT_ID AND SPFM.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SPFM.ORG_ID = SRP_CP.ORG_ID INNER JOIN CN_PERIOD_DIMS_ALL_TL PERIOD ON PERIOD.ORG_ID = SPFM.ORG_ID AND PERIOD.PERIOD_TYPE = 'Monthly' AND PERIOD.PERIOD_ID = SPFM.PERIOD_ID WHERE ( PERIOD.END_DATE BETWEEN SRP_CP.START_DATE AND NVL(SRP_CP.END_DATE, DATE '9999-12-31') OR NVL(SRP_CP.END_DATE, DATE '9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE ) AND SPFM.PARTICIPANT_ID = NVL(:participant_id, SPFM.PARTICIPANT_ID) AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID) ) WHERE LATEST_TI_RECORD = 1 ) SELECT -- Build unique record identifier: PeriodID-PayrunName-BadgeID-IncentiveType TO_CHAR( BG.PAY_PERIOD_ID || '-' || SUBSTR( BG.PAYRUN_NAME, 1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2), (INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || PN.person_number || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "Record Name", -- Duplicate of Record Name for reference TO_CHAR( BG.PAY_PERIOD_ID || '-' || SUBSTR( BG.PAYRUN_NAME, 1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2), (INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || PN.person_number || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "Record Number", -- Core payment identifiers BG.PAYRUN_ID AS "Payrun ID", BG.PAYSHEET_ID, 'Regular cycle' AS "Payment Cycle", BG.PAY_PERIOD_ID AS "Pay Period ID", BG.PAY_DATE AS "Pay Date", -- Get badge ID from HR system PN.person_number AS badge_id, BG.CREDITED_PARTICIPANT_ID AS "Participant Id", -- Role and employment info LATEST_PLAN_COMPONENT.ROLE_NAME AS "Role Name", parDetails.ATTRIBUTE_CHAR2 AS "HR Status", parDetails.ATTRIBUTE_CHAR40 AS "Employment Country", LATEST_PLAN_COMPONENT.START_DATE AS "Role Start Date", LATEST_PLAN_COMPONENT.END_DATE AS "Role End Date", -- Payment batch details BG.PAYRUN_NAME AS "Payment Batch", BG.PAY_GROUP_NAME AS "Pay group", -- Financial amounts in local currency - TODO: implement BB and CED calculations BB_AMT.B_BALANCE AS "BEGINNING_BALANCE_LC", 'TBD' AS "Current Earnings Due (CED)_LC", BG.paysheet_payout_LC AS paysheet_payout_MC, BG.LATEST_HOME_CURRENCY AS "Home Currency", BG.PAYSHEET_STATUS AS "PrePayHub Status", -- Plan component and TI LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS "Plan component Id", PM_TI.Prorated_MonthlyTI AS "Prorated TI", parDetails.ATTRIBUTE_NUMBER1 AS "HR TI", 'TBD' AS "BB % of HR TI", -- USD amounts - TODO: implement conversions 'TBD' AS "Current Earnings Due (CED) USD", 'TBD' AS "PTD earings USD", 'TBD' AS "BEGINNING_BALANCE_USD", -- Incentive classification LATEST_PLAN_COMPONENT.INCENTIVE_TYPE, 'Analyst - Missed Deadline' AS MPAReasonCode, -- Multi-currency flags PRD_ML.MULTI_CURR_YN AS "Multi Currency", -- Calendar cutoffs - TODO: implement automated refresh end date 'TBD' AS "Paysheet automated Refresh End CST", PAY_CAL.MPA_CUTOFF_START_DT, PAY_CAL.MPA_CUTOFF_END_DT, BG.PAY_AMT_PAY_CURR_USD AS "Total Paysheet Amount in USD", -- Analyst info ( SELECT DISTINCT HR_PRIMARY_WORKER_NUMBER FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ) AS "Badge Id of SCP Analyst", ( SELECT DISTINCT B.RESOURCE_PROFILE_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ) AS "ANALYST_RESOURCE_PROFILE_ID", -- Conversion rate: home currency to USD BG.conversionrate AS home_to_usd, -- Worker details (same as badge_id) PN.person_number AS "Worker Number", parDetails.COUNTRY_CODE AS "HR Country", ( SELECT PARTY_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ) AS "ANALYST_PARTY_ID", parDetails.ATTRIBUTE_CHAR17 AS "SalesRep", -- Additional details LATEST_PLAN_COMPONENT.plan_component_name, pay_cal.PAYROLL_SUBMISSION_END_CST, ( SELECT DISTINCT B.EMAIL_ADDRESS FROM CN_SRP_PARTICIPANTS_ALL ANALYST JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ) AS "OWNER_EMAIL_ID", -- Paysheet cutoff dates PAY_CAL.PAYSHEET_CUTOFF_START_DT, PAY_CAL.PAYSHEET_CUTOFF_DT AS "PAYSHEET_CUTOFF_END_DT", -- Multi-currency flags (detailed) PRD_ML.BBMultiCurrencyFlag, -- Final payout amounts in local currency -- BG.PAYSHEET_ID, BG.finalPayout AS final_payout_LC, BG.finalPayout_paysheet AS final_payout_PS__LC FROM BG INNER JOIN MULTI_CURRENCY_FLAGS PRD_ML ON BG.PAYRUN_ID = PRD_ML.PAYRUN_ID AND BG.CREDITED_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID AND BG.PAY_PERIOD_ID = PRD_ML.PERIOD_ID INNER JOIN LATEST_PLAN_COMPONENT ON LATEST_PLAN_COMPONENT.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND LATEST_PLAN_COMPONENT.ORG_ID = BG.ORG_ID AND LATEST_PLAN_COMPONENT.PERIOD_ID = BG.PAY_PERIOD_ID AND LATEST_PLAN_COMPONENT.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN BB_AMT ON bb_amt.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND bb_amt.ORG_ID = BG.ORG_ID AND bb_amt.PAY_PERIOD_ID = BG.PAY_PERIOD_ID AND bb_amt.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN PAY_CAL ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(LATEST_PLAN_COMPONENT.PAYRUN_NAME) AND PAY_CAL.PAYGROUP_NAME = UPPER(LATEST_PLAN_COMPONENT.PAY_GROUP_NAME) AND PAY_CAL.PERIOD_ID = BG.PAY_PERIOD_ID AND PAY_CAL.CURRENCY_CODE = BG.LATEST_HOME_CURRENCY AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)) LEFT JOIN PM_TI ON PM_TI.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PM_TI.ORG_ID = BG.ORG_ID AND PM_TI.PERIOD_ID = BG.PAY_PERIOD_ID INNER JOIN PARTICIPANT_DETAILS_CACHE parDetails ON BG.PAY_DATE BETWEEN parDetails.START_DATE AND NVL(parDetails.END_DATE, BG.PAY_DATE) AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID INNER JOIN PERSON_NUMBERS PN ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID WHERE 1 = 1 AND BG.payrun_id = NVL(:payrun_id, BG.payrun_id) AND BG.CREDITED_PARTICIPANT_ID = NVL(:participant_id, BG.CREDITED_PARTICIPANT_ID) AND BG.PAY_PERIOD_ID = NVL(:period_id, BG.PAY_PERIOD_ID)

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where