new query

WITH BASE_QUERY AS ( -- Your entire existing query goes here 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' ), -- ... [rest of your CTEs remain exactly the same] ... -- [I'm abbreviating here for clarity, but include ALL your CTEs] -- Your final SELECT from the original query SELECT 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 RecordName, -- ... [all your other columns] ... BG.PAYRUN_ID AS PayrunID_c, 'Regularcycle' AS PaymentCycle_c, BG.PAY_PERIOD_ID AS PayPeriodID_c, TO_CHAR(BG.PAY_DATE, 'MM/DD/YYYY') AS PayDate_c, PN.person_number AS BadgeIDWorkerNumber_c, BG.CREDITED_PARTICIPANT_ID AS ParticipantId_c, LATEST_PLAN_COMPONENT.ROLE_NAME AS RoleName_c, parDetails.ATTRIBUTE_CHAR2 AS HRStatus_c, parDetails.COUNTRY_CODE AS EmploymentCountry_c, TO_CHAR(LATEST_PLAN_COMPONENT.START_DATE, 'MM/DD/YYYY') AS RoleStartDate_c, TO_CHAR(LATEST_PLAN_COMPONENT.END_DATE, 'MM/DD/YYYY') AS RoleEndDate_c, BG.PAYRUN_NAME AS PaymentBatch_c, BG.PAY_GROUP_NAME AS PayGroup_c, NVL(BB_AMT.BB_HOME_CURRENCIES, '0') AS BB_Home_Currencies, NVL(BB_AMT.BB_HOME_CURRENCIES_WITH_AMOUNTS, '0') AS BB_Home_Currencies_With_Amounts, NVL(BB_AMT.BB_BREAKDOWN_BY_CURRENCY, '0') AS BB_Breakdown_By_Currency, NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM9999999990.0000'), '0') AS BB_Total_Mixed_Currencies_Sum, NVL(BB_AMT.BB_IN_LATEST_HC, '0') AS BeginningBalance_c, BB_AMT.LATEST_HOME_CURRENCY AS BB_Latest_Home_Currency, BB_AMT.BB_CONVERSION_RATE_AVG AS BB_Avg_Conversion_Rate, NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM999999999990.0000'), '0') AS BEGINNING_BALANCE_LC, NVL(TO_CHAR(NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM9999999990.0000'), '0') AS PTDEarnings_c, NVL(TO_CHAR( NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM999999999990.0000' ), '0') AS CED_c, NVL(TO_CHAR( NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM999999999990.0000' ), '0') AS TotalPaysheetAmountInLHC_c, NVL(TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0)) - NVL(BG.paysheet_payout_LC, 0), 'FM999999999990.0000' ), '0') AS MCAdjustment_c, NVL(TO_CHAR(BG.paysheet_payout_LC, 'FM9999999990.0000'), '0') AS TotalPaysheetAmount_c, BG.LATEST_HOME_CURRENCY AS CurrencyCode, LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS PlanComponentId_c, NVL(TO_CHAR(PM_TI.Prorated_MonthlyTI, 'FM9999999990.0000'), '0') AS ProratedTI_c, NVL(TO_CHAR(parDetails.ATTRIBUTE_NUMBER1, 'FM9999999990.0000'), '0') AS HRTI_c, NVL(CASE WHEN parDetails.ATTRIBUTE_NUMBER1 IS NOT NULL AND parDetails.ATTRIBUTE_NUMBER1 <> 0 THEN TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) / parDetails.ATTRIBUTE_NUMBER1) * 100, 'FM9999999990.0000' ) ELSE '0' END, '0') AS BBOfHRTI_c, NVL(TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_USD), 0) + NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 0)), 'FM9999999990.0000' ), '0') AS CEDUSD_c, NVL(TO_CHAR(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000'), '0') AS PTDUSD_c, NVL(BB_AMT.BB_IN_USD, '0') AS BBUSD_c, LATEST_PLAN_COMPONENT.INCENTIVE_TYPE AS IncentiveType_c, PRD_ML.MULTI_CURR_YN AS MultiCurrency_c, TO_CHAR(pay_cal.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PaysheetAutomatedRefreshEndCST_c, NVL(TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000'), '0') AS TotalPaysheetAmountUSD_c, ( 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 BadgeIdOfSCPAnalyst_c, ( 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 ResProfileIdOfSCPAnalyst_c, BG.conversionrate AS ConversionRateHomeToUSD_c, 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 PartyID_c, parDetails.ATTRIBUTE_CHAR17 AS SalesRP_c, LATEST_PLAN_COMPONENT.plan_component_name AS PlanComponentName_c, TO_CHAR(pay_cal.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MI:SS AM') AS PayrollSubmissionEndCST_c, ( 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 OwnerEmailId_c, TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_START_DT, TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_DT, TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS MPACutoffStartCST_c, TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY HH:MI:SS AM') AS MPACutoffEndCST_c, PRD_ML.BBMultiCurrencyFlag, NVL(TO_CHAR(BG.finalPayout, 'FM9999999990.0000'), '0') AS final_payout_LC, NVL(TO_CHAR(BG.finalPayout_paysheet, 'FM9999999990.0000'), '0') AS final_payout_PS__LC, BG.ORG_ID, BG.PAYSHEET_STATUS 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 PTD_EARNINGS_USD ON PTD_EARNINGS_USD.PAYRUN_ID = BG.PAYRUN_ID AND PTD_EARNINGS_USD.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PTD_EARNINGS_USD.ORG_ID = BG.ORG_ID AND PTD_EARNINGS_USD.PAY_PERIOD_ID = BG.PAY_PERIOD_ID LEFT JOIN PTD_EARNINGS_AMT ON PTD_EARNINGS_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PTD_EARNINGS_AMT.ORG_ID = BG.ORG_ID AND PTD_EARNINGS_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID AND PTD_EARNINGS_AMT.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN BB_AMT ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_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) ) -- WRAPPER QUERY WITH CONDITIONAL LOGIC SELECT BQ.*, -- Example: Conditional Status Flag CASE WHEN BQ.MultiCurrency_c = 'Y' THEN 'Multi-Currency Payment' WHEN BQ.BBMultiCurrencyFlag = 'Y' THEN 'BB Multi-Currency' ELSE 'Single Currency' END AS Payment_Type_Flag, -- Example: Payment Status Category CASE WHEN TO_NUMBER(BQ.TotalPaysheetAmount_c) > 0 THEN 'Positive Payout' WHEN TO_NUMBER(BQ.TotalPaysheetAmount_c) < 0 THEN 'Negative Payout' ELSE 'Zero Payout' END AS Payout_Category, -- Example: BB Threshold Flag CASE WHEN TO_NUMBER(BQ.BBOfHRTI_c) > 100 THEN 'BB Exceeds TI' WHEN TO_NUMBER(BQ.BBOfHRTI_c) BETWEEN 50 AND 100 THEN 'BB High' WHEN TO_NUMBER(BQ.BBOfHRTI_c) > 0 THEN 'BB Normal' ELSE 'No BB' END AS BB_Threshold_Flag, -- Example: Currency Variance Flag CASE WHEN BQ.CurrencyCode != BQ.BB_Latest_Home_Currency THEN 'Currency Changed' ELSE 'Currency Stable' END AS Currency_Variance_Flag, -- Example: Adjustment Required Flag CASE WHEN ABS(TO_NUMBER(BQ.MCAdjustment_c)) > 0.01 THEN 'Adjustment Required' ELSE 'No Adjustment' END AS Adjustment_Flag, -- Example: Incentive Type Category CASE WHEN BQ.IncentiveType_c = 'COMMISSION' THEN 'Variable - Commission' WHEN BQ.IncentiveType_c = 'BONUS' THEN 'Variable - Bonus' ELSE 'Other Incentive' END AS Incentive_Category, -- Example: Employment Status Flag CASE WHEN BQ.HRStatus_c = 'ACTIVE' THEN 'Active Employee' WHEN BQ.HRStatus_c = 'TERMINATED' THEN 'Terminated' ELSE 'Other Status' END AS Employment_Status_Flag, -- Example: Regional Classification CASE WHEN BQ.EmploymentCountry_c IN ('US', 'CA', 'MX') THEN 'Americas' WHEN BQ.EmploymentCountry_c IN ('GB', 'DE', 'FR', 'IT', 'ES') THEN 'EMEA' WHEN BQ.EmploymentCountry_c IN ('CN', 'JP', 'IN', 'AU', 'SG') THEN 'APAC' ELSE 'Other Region' END AS Region_Classification, -- Example: Payout Variance (USD vs LC) TO_CHAR( TO_NUMBER(BQ.TotalPaysheetAmountUSD_c) - (TO_NUMBER(BQ.TotalPaysheetAmount_c) * TO_NUMBER(NVL(BQ.ConversionRateHomeToUSD_c, '1'))), 'FM9999999990.0000' ) AS Payout_Variance_USD_LC, -- Example: BB to PTD Ratio CASE WHEN TO_NUMBER(BQ.PTDEarnings_c) != 0 THEN TO_CHAR( (TO_NUMBER(BQ.BeginningBalance_c) / TO_NUMBER(BQ.PTDEarnings_c)) * 100, 'FM9999999990.00' ) ELSE '0' END AS BB_to_PTD_Ratio_Pct, -- Example: High Value Transaction Flag CASE WHEN TO_NUMBER(BQ.TotalPaysheetAmountUSD_c) >= 10000 THEN 'High Value' WHEN TO_NUMBER(BQ.TotalPaysheetAmountUSD_c) >= 5000 THEN 'Medium Value' ELSE 'Standard Value' END AS Transaction_Value_Flag, -- Example: Days Until Payroll Submission (if you have SYSDATE available) CASE WHEN BQ.PayrollSubmissionEndCST_c IS NOT NULL THEN TO_CHAR( TO_DATE(BQ.PayrollSubmissionEndCST_c, 'MM/DD/YYYY HH:MI:SS AM') - TRUNC(SYSDATE) ) ELSE NULL END AS Days_Until_Payroll_Submission, -- Example: Composite Key for External Systems BQ.PayPeriodID_c || '-' || BQ.ParticipantId_c || '-' || BQ.IncentiveType_c AS External_System_Key, -- Example: Alert Flag for Review CASE WHEN BQ.MultiCurrency_c = 'Y' AND ABS(TO_NUMBER(BQ.MCAdjustment_c)) > 100 THEN 'REVIEW REQUIRED' WHEN TO_NUMBER(BQ.BBOfHRTI_c) > 150 THEN 'REVIEW REQUIRED' WHEN BQ.PAYSHEET_STATUS IS NULL THEN 'REVIEW REQUIRED' ELSE 'OK' END AS Review_Alert_Flag FROM BASE_QUERY BQ WHERE 1=1 -- Add any additional filtering conditions here -- AND BQ.Payout_Category != 'Zero Payout' -- AND BQ.Employment_Status_Flag = 'Active Employee' ORDER BY BQ.PayPeriodID_c, BQ.BadgeIDWorkerNumber_c, BQ.IncentiveType_c;

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika