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
Post a Comment