Posts

new update with materialize only

SELECT rules.RecordName, rules.RecordNumber, rules.PaysheetId_c, rules.PayPeriodID_c, rules.PayDate_c, rules.BadgeIDWorkerNumber_c, rules.ParticipantId_c, rules.RoleStartDate_c, rules.RoleEndDate_c, rules.PaymentBatch_c, rules.PayGroup_c, NVL(TO_CHAR(rules.TotalPaysheetAmount_c, 'FM9999999990.0000'), '0') AS TotalPaysheetAmount_c, rules.CurrencyCode, rules.PlanComponentId_c, NVL(TO_CHAR(rules.ProratedTI_c, 'FM9999999990.0000'), '0') AS ProratedTI_c, TO_CHAR(rules.PTDUSD_c) AS PTDUSD_c, TO_CHAR(rules.BBUSD_c) AS BBUSD_c, rules.MultiCurrency_c, rules.PaysheetAutomatedRefreshStartCST_c, rules.PaysheetAutomatedRefreshEndCST_c, TO_CHAR(rules.TotalPaysheetAmountUSD_c) AS TotalPaysheetAmountUSD_c, rules.ConversionRateHomeToUSD_c, rules.PlanComponentName_c, rules.OwnerEmailId_c, rules.HRStatus_c, rules.EmploymentCountry_c, rules.RoleName_c, NVL(TO_CHAR(ru...

complete unified query

/* FINAL COMPLETE OPTIMIZED QUERY */ SELECT rules.*, /* Rule Action Mapping */ DECODE(Rule_Number, 1, 'MAV_DIR_BB_ACTION_1000', 2, 'MAV_DIR_BB_ACTION_1003', 3, 'MAV_DIR_BB_ACTION_1001', 4, 'MAV_DIR_BB_ACTION_1003', 5, 'MAV_DIR_BB_ACTION_1003', 6, 'MAV_DIR_BB_ACTION_1003', 7, 'MAV_DIR_BB_ACTION_1003', 8, 'MAV_DIR_BB_ACTION_1003', 9, 'MAV_DIR_BB_ACTION_1003', 10, 'MAV_DIR_BB_ACTION_1003', 99, 'MAV_DIR_BB_ACTION_1002', 11, 'MAV_DIR_BB_ACTION_1051', NULL) AS BBAction_c, /* Rule Reason Mapping */ DECODE(Rule_Number, 1, 'MVA_DIR_BB_REASON_1018', 2, 'MVA_DIR_BB_REASON_1001', 3, 'MVA_DIR_BB_REASON_1018', 4, 'MVA_DIR_BB_REASON_1001', 5, 'MVA_DIR_BB_REASON_1001', 6, 'MVA_DIR_BB_REASON_1001', 7, 'MVA_DIR_BB_REASON_1001', 8, 'MVA_DIR_BB_REASON_1001', 9, 'MVA_DIR_BB_REASON_1001', 10, 'MVA_DIR_BB_REASON_1...

new calc

SELECT rules.*, /* Rule Action Mapping */ DECODE(Rule_Number, 1, 'MAV_DIR_BB_ACTION_1000', 2, 'MAV_DIR_BB_ACTION_1003', 3, 'MAV_DIR_BB_ACTION_1001', 4, 'MAV_DIR_BB_ACTION_1003', 5, 'MAV_DIR_BB_ACTION_1003', 6, 'MAV_DIR_BB_ACTION_1003', 7, 'MAV_DIR_BB_ACTION_1003', 8, 'MAV_DIR_BB_ACTION_1003', 9, 'MAV_DIR_BB_ACTION_1003', 10, 'MAV_DIR_BB_ACTION_1003', 99, 'MAV_DIR_BB_ACTION_1002', 11, 'MAV_DIR_BB_ACTION_1051', NULL ) AS BBAction_c, /* Rule Reason Mapping */ DECODE(Rule_Number, 1, 'MVA_DIR_BB_REASON_1018', 2, 'MVA_DIR_BB_REASON_1001', 3, 'MVA_DIR_BB_REASON_1018', 4, 'MVA_DIR_BB_REASON_1001', 5, 'MVA_DIR_BB_REASON_1001', 6, 'MVA_DIR_BB_REASON_1001', 7, 'MV...

sql plan

10 USD). PENDING analyst to review and action.', 6, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.', 7, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Consider Payroll collection, if negative CED amount is final.', 8, 'Full CED put on Hold by system as the rep moved to non-sales role with positive CED amount. PENDING analyst to review and action.', 9, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.', 10, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Consider Payroll collection if negati...

updated

SELECT rules.RecordName, rules.RecordNumber, rules.PaysheetId_c, rules.ParticipantDetailId_c, -- Added rules.PayPeriodID_c, rules.PayDate_c, rules.BadgeIDWorkerNumber_c, rules.ParticipantId_c, rules.RoleStartDate_c, rules.RoleEndDate_c, rules.PaymentBatch_c, rules.PayGroup_c, NVL(TO_CHAR(rules.TotalPaysheetAmount_c, 'FM9999999990.0000'), '0') AS TotalPaysheetAmount_c, rules.CurrencyCode, rules.PlanComponentId_c, NVL(TO_CHAR(rules.ProratedTI_c, 'FM9999999990.0000'), '0') AS ProratedTI_c, TO_CHAR(rules.PTDUSD_c) AS PTDUSD_c, TO_CHAR(rules.BBUSD_c) AS BBUSD_c, rules.MultiCurrency_c, rules.PaysheetAutomatedRefreshStartCST_c, rules.PaysheetAutomatedRefreshEndCST_c, TO_CHAR(rules.TotalPaysheetAmountUSD_c) AS TotalPaysheetAmountUSD_c, rules.ConversionRateHomeToUSD_c, rules.PlanComponentName_c, rules.OwnerEmailId_c, rules.HRStatus_c, rules.EmploymentCount...