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 negative CED amount is final. Rep''s consent NEEDS to be attached for such case.', 99, 'Full BB put on Hold by system as the rep is active sale rep OVER the defined pre-approved BB threshold limits (BB % HR TI ≥ -5% and ≤ 5%) or is Austria rep. PENDING analyst to review and action.', 11, 'Zero BB is pre-approved by system for active sale rep', NULL ) AS BBMPAComment_c, DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'MAV_DIR_MPA_CODE_1008' ) AS MPAReasonCode_c, DECODE(Rule_Number, 1, NULL, 2, (-1) * rules.CED_c, 3, NULL, 4, (-1) * rules.CED_c, 5, (-1) * rules.CED_c, 6, (-1) * rules.CED_c, 7, (-1) * rules.CED_c, 8, (-1) * rules.CED_c, 9, (-1) * rules.CED_c, 10, (-1) * rules.CED_c, 11 , NULL, 99, (-1) * rules.BeginningBalance_c, NULL ) AS MPAGenerated_c, DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 99, rules.PTDEarnings_c, NULL ) AS ExpectedPaymentAmount_c, DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'Auto approved' ) AS MPAStatus_c, DECODE(Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'Y' ) AS RecoverableFlagV1_c, rules.Owner_Id_c OraZcxOwner_Id_c, rules.PayrunID_c, rules.ParticipantName_c, rules.BadgeIdOfSCPAnalyst_c FROM ( SELECT base.*, CASE WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c != 'AT' AND UPPER(base.SalesRP_c) = 'Y' AND base.HRTI_c != 0 AND base.BBOfHRTI_c BETWEEN -0.05 AND 0.05 AND base.BeginningBalance_c != 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 1 WHEN base.HRStatus_c = 'L' AND UPPER(base.SalesRP_c) = 'Y' AND base.CED_c != 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 2 WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c != 'AT' AND UPPER(base.SalesRP_c) = 'Y' AND base.CEDUSD_c BETWEEN 0 AND 10 AND base.IncentiveType_c = 'COMMISSION' THEN 3 WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND base.CEDUSD_c BETWEEN 0 AND 10 AND base.IncentiveType_c = 'BONUS' THEN 4 WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10 AND base.IncentiveType_c = 'COMMISSION' AND base.EmploymentCountry_c = 'AT' THEN 4 WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND base.CEDUSD_c > 10 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 5 WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c = 'RO' AND UPPER(base.SalesRP_c) = 'Y' AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 6 WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c != 'RO' AND UPPER(base.SalesRP_c) = 'Y' AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 7 WHEN base.HRStatus_c = 'A' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND base.CED_c > 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 8 WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c = 'RO' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 9 WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c != 'RO' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND base.CED_c < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 10 WHEN base.HRStatus_c = 'A' AND UPPER(base.SalesRP_c) = 'Y' AND base.BeginningBalance_c = 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 11 WHEN base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 99 ELSE 0 END AS Rule_Number FROM ( WITH CN_ROLE_DETAILS AS ( SELECT PART_ROLE.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID, CN_ROLE.ROLE_ID, CN_ROLE.ROLE_NAME, PART_ROLE.START_DATE_ACTIVE, PART_ROLE.END_DATE_ACTIVE, ROW_NUMBER() OVER (PARTITION BY PART_ROLE.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID ORDER BY NVL(PART_ROLE.END_DATE_ACTIVE,'9999-12-31') DESC) AS LATEST_ROLE_ORDER FROM CN_PARTICIPANT_ROLES PART_ROLE JOIN CN_ROLES_TL CN_ROLE ON PART_ROLE.ROLE_ID = CN_ROLE.ROLE_ID AND CN_ROLE.LANGUAGE = 'US' JOIN CN_SRP_PARTICIPANTS_ALL PH ON PH.PARTICIPANT_ID = PART_ROLE.PARTICIPANT_ID JOIN CN_PERIOD_DIMS_ALL_TL PERIOD ON PERIOD.ORG_ID = PH.ORG_ID AND PERIOD.PERIOD_TYPE = 'Monthly' AND PERIOD.PERIOD_ID IN (:P_PERIOD_ID) WHERE EXISTS (SELECT 1 FROM CN_ROLE_PLANS_ALL RP WHERE RP.ROLE_ID = PART_ROLE.ROLE_ID) AND (PERIOD.END_DATE BETWEEN PART_ROLE.START_DATE_ACTIVE AND NVL(PART_ROLE.END_DATE_ACTIVE,'9999-12-31') OR NVL(PART_ROLE.END_DATE_ACTIVE,'9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE) ), PRORATED_MONTHLY_TI AS ( SELECT SPFM.PARTICIPANT_ID, SPFM.ORG_ID, SPFM.PERIOD_ID, SRP_CP.START_DATE, SRP_CP.END_DATE, SPFM.OUTPUT_ACHIEVED_ITD Prorated_MonthlyTI, ROW_NUMBER() OVER (PARTITION BY SPFM.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID ORDER BY NVL(SRP_CP.END_DATE,'9999-12-31') DESC) AS LATEST_TI_RECORD FROM CN_SRP_PER_FORM_METRICS_ALL SPFM 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' 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 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 AND PERIOD.PERIOD_ID IN (:P_PERIOD_ID) WHERE (PERIOD.END_DATE BETWEEN SRP_CP.START_DATE AND NVL(SRP_CP.END_DATE,'9999-12-31') OR NVL(SRP_CP.END_DATE,'9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE) ), LATEST_PLAN_COMPONENT AS ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID PARTICIPANT_ID, PART_PAY.ORG_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID PERIOD_ID, PART_PAY.PLAN_COMPONENT_ID, PC.PLAN_COMPONENT_NAME, PC.REPORT_GROUP INCENTIVE_TYPE, 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 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 AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID) JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID 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 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 ), CO_PAYRUN_CALENDAR AS ( SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR010) PAYMENT_BATCH_NAME, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR009) PAYGROUP_NAME, PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 PERIOD_ID, PAYRUN_CAL.CURRENCY_CODE, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) INCENTIVE_TYPE, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) COUNTRY, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP004 MPA_CUTOFF_START_DT, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP003 MPA_CUTOFF_END_DT, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP011 PAYSHEET_CUTOFF_START_DT, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010 PAYSHEET_CUTOFF_DT, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008 PAYROLL_SUBMISSION_END_CST FROM MOO_REF_ENTITIES PAYRUN_CAL WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c' AND PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 IN (:P_PERIOD_ID) ), CN_CURR_CONV_RATES AS ( SELECT CR.FROM_CURRENCY, CR.TO_CURRENCY, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE, CR.CONVERSION_RATE FROM CN_CONVERSION_RATES CR JOIN CN_CONVERSION_TYPES_TL CT ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID AND CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE='US' ), CN_SRP_PYMT_DETAILS AS ( SELECT PS.PAYRUN_ID, PS.PARTICIPANT_ID CREDITED_PARTICIPANT_ID, PS.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_ID, PG.PAY_GROUP_NAME, NVL(PB.REPORT_GROUP,'ALL') INCENTIVE_TYPE, PS.PAYSHEET_ID, (SELECT meaning FROM cn_lookups WHERE lookup_type LIKE 'CN_PAYSHEET_STATUS' AND lookup_code = PS.OBJECT_STATUS AND enabled_flag = 'Y' AND lookup_code != 'REVIEWED') PAYSHEET_STATUS, SUM(PS.TOTAL_AMT_HOME_CURRENCY) TOTAL_AMT FROM CN_TP_PAYSHEETS_ALL PS JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PS.PAYRUN_ID AND PB.ORG_ID = PS.ORG_ID AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID) JOIN CN_PAY_GROUPS_ALL_VL PG ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID AND PG.ORG_ID = PS.ORG_ID JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON PS.PARTICIPANT_ID = PD.PARTICIPANT_ID AND PD.ORG_ID = PS.ORG_ID AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,PB.PAY_DATE) WHERE PS.QUOTA_ID IS NULL GROUP BY PS.PAYRUN_ID, PS.PARTICIPANT_ID, PS.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_ID, PG.PAY_GROUP_NAME, PB.REPORT_GROUP, PS.PAYSHEET_ID, PS.OBJECT_STATUS ), BB_AMT AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.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 AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID IN (:P_PERIOD_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 GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID ), PTD_EARNINGS_AMT AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, SUM(P.PAY_AMT_PAY_CURR) PTD_EARNINGS 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 AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID IN (:P_PERIOD_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.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN') GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID ), CN_PART_DETAILS AS ( SELECT PART_DTLS.PARTICIPANT_ID, PART_HDR.PARTICIPANT_NAME, PAPF.PERSON_NUMBER AS HR_PRIMARY_WORKER_NUMBER, PART_DTLS.PARTICIPANT_DETAIL_ID, PART_DTLS.ORG_ID, PART_DTLS.START_DATE, PART_DTLS.END_DATE, PART_DTLS.COUNTRY_CODE AS HR_Country, PART_DTLS.ATTRIBUTE_CHAR40 AS Employment_Country, ANALYST.PARTY_ID AS ANALYST_PARTY_ID, ANALYST.HR_PRIMARY_WORKER_NUMBER AS ANALYST_BADGE_ID, RES.RESOURCE_PROFILE_ID AS ANALYST_RESOURCE_PROFILE_ID, RES.EMAIL_ADDRESS AS OWNER_EMAIL_ID, PART_DTLS.ATTRIBUTE_NUMBER1 AS HR_TI, PART_DTLS.CURRENCY_CODE AS HOME_CURRENCY, PART_DTLS.ATTRIBUTE_CHAR2 AS HR_Status, PART_DTLS.ATTRIBUTE_CHAR17 AS SalesRep, PART_DTLS.ATTRIBUTE_CHAR33 AS ActionCode FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS 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 LEFT JOIN HZ_PERSON_PROFILES HPP ON HPP.PARTY_ID = PART_HDR.PARTY_ID LEFT JOIN PER_ALL_PEOPLE_F PAPF ON PAPF.PERSON_ID = HPP.HR_PERSON_ID AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE LEFT JOIN CN_SRP_PARTICIPANTS_ALL ANALYST ON ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID AND ANALYST.ORG_ID = PART_HDR.ORG_ID LEFT JOIN JTF_RS_RESOURCE_PROFILES RES ON RES.PARTY_ID = ANALYST.PARTY_ID ), MULTI_CURR_YN AS ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID, CASE WHEN COUNT(MULTI_CURR.HOME_CURRENCY_CODE)>1 THEN 'Y' ELSE 'N' END AS MULTI_CURR_YN FROM ( SELECT PART_PAY.CREDITED_PARTICIPANT_ID PARTICIPANT_ID, PART_PAY.HOME_CURRENCY_CODE, PART_PAY.PAYRUN_ID, PB.ORG_ID, PB.PAY_PERIOD_ID PERIOD_ID, SUM(PART_PAY.PAY_AMT_PAY_CURR) "TotalAmt" FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID AND PB.ORG_ID = PART_PAY.ORG_ID AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID) WHERE PART_PAY.PAY_AMT_PAY_CURR <> 0 GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.HOME_CURRENCY_CODE, PART_PAY.PAYRUN_ID, PB.ORG_ID, PB.PAY_PERIOD_ID HAVING SUM(PART_PAY.PAY_AMT_PAY_CURR) <> 0 ) MULTI_CURR GROUP BY MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID ) SELECT TO_CHAR(SPD.PAY_PERIOD_ID || '-' || SUBSTR(SPD.PAYRUN_NAME,1+INSTR(SPD.PAYRUN_NAME,'_',-1,2),(INSTR(SPD.PAYRUN_NAME,'_',-1,1)-INSTR(SPD.PAYRUN_NAME,'_',-1,2)-1)) || '-' || PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER || '-' || DECODE(UPPER(SPD.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(SPD.INCENTIVE_TYPE))) RecordName, TO_CHAR(PD_PAY_DT.ANALYST_RESOURCE_PROFILE_ID) Owner_Id_c, NVL(MC_YN.MULTI_CURR_YN,'N') MultiCurrency_c, TO_CHAR(SPD.PAY_PERIOD_ID || '-' || SUBSTR(SPD.PAYRUN_NAME,1+INSTR(SPD.PAYRUN_NAME,'_',-1,2),(INSTR(SPD.PAYRUN_NAME,'_',-1,1)-INSTR(SPD.PAYRUN_NAME,'_',-1,2)-1)) || '-' || PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER || '-' || DECODE(UPPER(SPD.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(SPD.INCENTIVE_TYPE))) RecordNumber, PD_PAY_DT.HOME_CURRENCY CurrencyCode, TO_CHAR(SPD.PAYRUN_ID) PayrunID_c, 'Regular cycle' PaymentCycle_c, TO_CHAR(SPD.PAY_PERIOD_ID) PayPeriodID_c, TO_CHAR(SPD.PAY_DATE,'MM/DD/YYYY') PayDate_c, PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER BadgeIDWorkerNumber_c, TO_CHAR(SPD.CREDITED_PARTICIPANT_ID) ParticipantId_c, PD_PAY_DT.PARTICIPANT_NAME ParticipantName_c, PRD.ROLE_NAME RoleName_c, NVL(PD_PYMT_DT.HR_Status,PD_PAY_DT.HR_Status) HRStatus_c, TO_CHAR(PRD.START_DATE_ACTIVE,'MM/DD/YYYY') RoleStartDate_c, TO_CHAR(PRD.END_DATE_ACTIVE,'MM/DD/YYYY') RoleEndDate_c, SPD.PAYRUN_NAME PaymentBatch_c, SPD.PAY_GROUP_NAME PayGroup_c, ROUND(NVL(BB.B_BALANCE,0),4) BeginningBalance_c, ROUND(NVL(CUR_PAY.PTD_EARNINGS,0),4) PTDEarnings_c, TO_CHAR(LPC.PLAN_COMPONENT_ID) PlanComponentId_c, ROUND(NVL(PD_PAY_DT.HR_TI,0),4) HRTI_c, CASE WHEN NVL(PD_PAY_DT.HR_TI,0)=0 THEN 0 ELSE ROUND((NVL(BB.B_BALANCE,0)/PD_PAY_DT.HR_TI),4) END BBOfHRTI_c, ROUND(NVL(SPD.TOTAL_AMT,0),4) TotalPaysheetAmount_c, ROUND(NVL(PM_TI.Prorated_MonthlyTI,0),4) ProratedTI_c, NVL(PD_PYMT_DT.SalesRep,PD_PAY_DT.SalesRep) SalesRP_c, ROUND((NVL(BB.B_BALANCE,0) + NVL(CUR_PAY.PTD_EARNINGS,0)),4) CED_c, ROUND(((NVL(BB.B_BALANCE,0) + NVL(CUR_PAY.PTD_EARNINGS,0)) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) CEDUSD_c, ROUND((NVL(BB.B_BALANCE,0) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) BBUSD_c, ROUND((NVL(CUR_PAY.PTD_EARNINGS,0) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) PTDUSD_c, SPD.INCENTIVE_TYPE IncentiveType_c, PD_PAY_DT.HR_Country EmploymentCountry_c, TO_CHAR(PD_PAY_DT.OWNER_EMAIL_ID) OwnerEmailId_c, TO_CHAR(PD_PAY_DT.ANALYST_BADGE_ID) BadgeIdOfSCPAnalyst_c, ROUND(NVL(CC_TO_USD.CONVERSION_RATE,1),4) ConversionRateHomeToUSD_c, ROUND((NVL(SPD.TOTAL_AMT,0) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) TotalPaysheetAmountUSD_c, LPC.PLAN_COMPONENT_NAME PlanComponentName_c, TO_CHAR(PAY_CAL.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MM:SS PM') PayrollSubmissionEndCST_c, TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY HH:MM:SS PM') MPACutoffStartCST_c, TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY HH:MM:SS PM') MPACutoffEndCST_c, TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MM:SS PM') PaysheetAutomatedRefreshEndCST_c, TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MM:SS PM') PaysheetAutomatedRefreshStartCST_c, SPD.PAYSHEET_ID PaysheetId_c, PD_PYMT_DT.ActionCode ActionCode_c, PD_PAY_DT.PARTICIPANT_DETAIL_ID ParticipantDetailId_c FROM CN_SRP_PYMT_DETAILS SPD JOIN CN_PART_DETAILS PD_PAY_DT ON TRUNC(SPD.PAY_DATE) BETWEEN PD_PAY_DT.START_DATE AND NVL(PD_PAY_DT.END_DATE,'9999-12-31') AND SPD.CREDITED_PARTICIPANT_ID = PD_PAY_DT.PARTICIPANT_ID AND SPD.ORG_ID = PD_PAY_DT.ORG_ID LEFT JOIN CN_ROLE_DETAILS PRD ON PRD.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND PRD.ORG_ID = SPD.ORG_ID AND PRD.PERIOD_ID = SPD.PAY_PERIOD_ID AND PRD.LATEST_ROLE_ORDER = 1 LEFT JOIN PRORATED_MONTHLY_TI PM_TI ON PM_TI.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND PM_TI.ORG_ID = SPD.ORG_ID AND PM_TI.PERIOD_ID = SPD.PAY_PERIOD_ID AND PM_TI.LATEST_TI_RECORD = 1 LEFT JOIN LATEST_PLAN_COMPONENT LPC ON LPC.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND LPC.ORG_ID = SPD.ORG_ID AND LPC.PERIOD_ID = SPD.PAY_PERIOD_ID AND LPC.PAYRUN_ID = SPD.PAYRUN_ID AND LPC.INCENTIVE_TYPE = SPD.INCENTIVE_TYPE AND LPC.LATEST_PC_RECORD = 1 LEFT JOIN CO_PAYRUN_CALENDAR PAY_CAL ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(SPD.PAYRUN_NAME) AND PAY_CAL.PAYGROUP_NAME = UPPER(SPD.PAY_GROUP_NAME) AND PAY_CAL.PERIOD_ID = SPD.PAY_PERIOD_ID AND PAY_CAL.CURRENCY_CODE = PD_PAY_DT.HOME_CURRENCY AND UPPER(PAY_CAL.COUNTRY) = UPPER(PD_PAY_DT.HR_Country) AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(SPD.INCENTIVE_TYPE)) LEFT JOIN CN_PART_DETAILS PD_PYMT_DT ON TRUNC(PAY_CAL.PAYSHEET_CUTOFF_DT) BETWEEN PD_PYMT_DT.START_DATE AND NVL(PD_PYMT_DT.END_DATE,'9999-12-31') AND PD_PYMT_DT.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND PD_PYMT_DT.ORG_ID = SPD.ORG_ID LEFT JOIN CN_CURR_CONV_RATES CC_TO_USD ON TRUNC(SPD.PAY_DATE) BETWEEN CC_TO_USD.CONVERSION_START_DATE AND CC_TO_USD.CONVERSION_END_DATE AND CC_TO_USD.TO_CURRENCY = 'USD' AND CC_TO_USD.FROM_CURRENCY = PD_PAY_DT.HOME_CURRENCY LEFT JOIN MULTI_CURR_YN MC_YN ON MC_YN.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND MC_YN.PAYRUN_ID = SPD.PAYRUN_ID AND MC_YN.ORG_ID = SPD.ORG_ID AND MC_YN.PERIOD_ID = SPD.PAY_PERIOD_ID LEFT JOIN PTD_EARNINGS_AMT CUR_PAY ON CUR_PAY.CREDITED_PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND CUR_PAY.ORG_ID = SPD.ORG_ID AND CUR_PAY.PAYRUN_ID = SPD.PAYRUN_ID AND CUR_PAY.PAY_PERIOD_ID = SPD.PAY_PERIOD_ID LEFT JOIN BB_AMT BB ON BB.CREDITED_PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID AND BB.ORG_ID = SPD.ORG_ID AND BB.PAYRUN_ID = SPD.PAYRUN_ID AND BB.PAY_PERIOD_ID = SPD.PAY_PERIOD_ID WHERE NVL(MC_YN.MULTI_CURR_YN,'N') = 'N' AND (SPD.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID) OR COALESCE(:P_PARTICIPANT_ID,'ALL') = 'ALL') AND (SPD.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND SPD.ORG_ID = (SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') AND SPD.PAY_PERIOD_ID IN (:P_PERIOD_ID) AND (SPD.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT,'ALL') = 'ALL') ) base ) rules SQL Query Timeout: 500 Explain plan cannot generate for SQL query including secured tables or views ]]> ]]>

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika