Posts

finalquery

WITH MAIN_DATA AS ( 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,...

unified query - to be chabnged

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...

final_single_query

SELECT rules.RecordName, rules.Owner_Id_c, rules.MultiCurrency_c, rules.RecordNumber, rules.CurrencyCode, rules.PayrunID_c, rules.PaymentCycle_c, rules.PayPeriodID_c, rules.PayDate_c, rules.BadgeIDWorkerNumber_c, rules.ParticipantId_c, rules.ParticipantName_c, rules.RoleName_c, rules.HRStatus_c, rules.RoleStartDate_c, rules.RoleEndDate_c, rules.PaymentBatch_c, rules.PayGroup_c, NVL(TO_CHAR(rules.BeginningBalance_c, 'FM9999999990.0000'), '0') AS BeginningBalance_c, NVL(TO_CHAR(rules.PTDEarnings_c, 'FM9999999990.0000'), '0') AS PTDEarnings_c, rules.PlanComponentId_c, NVL(TO_CHAR(rules.HRTI_c, 'FM9999999990.0000'), '0') AS HRTI_c, rules.BBOfHRTI_c, NVL(TO_CHAR(rules.TotalPaysheetAmount_c, 'FM9999999990.0000'), '0') AS TotalPaysheetAmount_c, NVL(TO_CHAR(rules.ProratedTI_c, 'FM9999999990.0000'), '0') AS ProratedT...

Child query- Period to be added

WITH -- Materialize the multi-currency detection queries once - FILTERED BB_MULTI_CURRENCY AS ( 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 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 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_PE...