QueryK

WITH CN_ROLE_DETAILS AS ( SELECT PART_ROLE.PARTICIPANT_ID "PARTICIPANT_ID", PERIOD.ORG_ID ORG_ID, PERIOD.PERIOD_ID PERIOD_ID, CN_ROLE.ROLE_ID "ROLE_ID", CN_ROLE.ROLE_NAME "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' 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 "PARTICIPANT_ID", SPFM.ORG_ID "ORG_ID", SPFM.PERIOD_ID "PERIOD_ID", SRP_CP.START_DATE "START_DATE", SRP_CP.END_DATE "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 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 "ORG_ID", PB.PAYRUN_ID PAYRUN_ID, PB.PAY_PERIOD_ID "PERIOD_ID", PART_PAY.PLAN_COMPONENT_ID PLAN_COMPONENT_ID, PC.PLAN_COMPONENT_NAME PLAN_COMPONENT_NAME, PC.REPORT_GROUP INCENTIVE_TYPE, --PB.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 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 --CN_ROLE_DETAILS RD 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 --CN_ROLE_DETAILS RD ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID --where PART_PAY.CREDITED_PARTICIPANT_ID=134136 ), CO_PAYRUN_CALENDAR AS ( SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) "PAYMENT_BATCH_NAME", UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) "PAYGROUP_NAME", --PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR009 "PERIOD_ID", PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 "PERIOD_ID", PAYRUN_CAL.CURRENCY_CODE "CURRENCY_CODE", UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR011) "INCENTIVE_TYPE", --TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP013) "PAYROLL_CUTOFF_LC", UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR012) COUNTRY, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP009 "MPA_CUTOFF_START_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010 "MPA_CUTOFF_END_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP007 "PAYSHEET_CUTOFF_START_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008 "PAYSHEET_CUTOFF_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP012 "PAYROLL_SUBMISSION_END_CST" FROM ZCA_REF_ENTITIES PAYRUN_CAL WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c' ), CN_CURR_CONV_RATES AS ( SELECT CR.FROM_CURRENCY "FROM_CURRENCY", CR.TO_CURRENCY "TO_CURRENCY", CR.CONVERSION_START_DATE "CONVERSION_START_DATE", CR.CONVERSION_END_DATE "CONVERSION_END_DATE", CR.CONVERSION_RATE "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 "ORG_ID", PB.PAYRUN_NAME "PAYRUN_NAME", PB.PAY_PERIOD_ID "PAY_PERIOD_ID", PB.PAY_DATE "PAY_DATE", PG.PAY_GROUP_ID "PAY_GROUP_ID", PG.PAY_GROUP_NAME "PAY_GROUP_NAME", NVL(PB.REPORT_GROUP,'ALL') "INCENTIVE_TYPE", --PC.REPORT_GROUP "INCENTIVE_TYPE", PS.PAYSHEET_ID "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(P.PAY_AMT_PAY_CURR) "TOTAL_AMT" 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 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 --AND PS.PARTICIPANT_ID = 54000 --AND PS.ORG_ID = 300000008656040 --AND PAY_VW.PAY_PERIOD_ID = 2025007 GROUP BY PS.PAYRUN_ID, PS.PARTICIPANT_ID, PS.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID,--This is the pay period --P.PAY_PERIOD_ID,--This gives period for which the amount is calculated PB.PAY_DATE, PG.PAY_GROUP_ID, PG.PAY_GROUP_NAME, --P.PLAN_COMPONENT_ID, --PC.INCENTIVE_TYPE, PB.REPORT_GROUP, --PC.REPORT_GROUP, PS.PAYSHEET_ID, PS.OBJECT_STATUS ), BB_AMT AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID", P.ORG_ID "ORG_ID", PB.PAY_PERIOD_ID "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 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.CREDITED_PARTICIPANT_ID = 134135 --AND P.ORG_ID = 300000008656040 --AND PAY_VW.PAY_PERIOD_ID = 2025007 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 "CREDITED_PARTICIPANT_ID", P.ORG_ID "ORG_ID", PB.PAY_PERIOD_ID "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 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') --AND P.CREDITED_PARTICIPANT_ID = 134135 --AND P.ORG_ID = 300000008656040 --AND PAY_VW.PAY_PERIOD_ID = 2025007 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 "PARTICIPANT_ID", PART_HDR.PARTICIPANT_NAME PARTICIPANT_NAME, --PART_HDR.HR_PRIMARY_WORKER_NUMBER "HR_PRIMARY_WORKER_NUMBER", (SELECT DISTINCT papf.person_number FROM hz_person_profiles hpp INNER JOIN PER_ALL_PEOPLE_F papf ON papf.person_id = hpp.hr_person_id WHERE hpp.party_id = PART_HDR.party_id) AS "HR_PRIMARY_WORKER_NUMBER", PART_DTLS.PARTICIPANT_DETAIL_ID "PARTICIPANT_DETAIL_ID", PART_DTLS.ORG_ID "ORG_ID", PART_DTLS.START_DATE "START_DATE", PART_DTLS.END_DATE "END_DATE", PART_DTLS.COUNTRY_CODE HR_Country, PART_DTLS.ATTRIBUTE_CHAR40 Employment_Country, (SELECT DISTINCT PARTY_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID ) AS "ANALYST_PARTY_ID", (SELECT DISTINCT HR_PRIMARY_WORKER_NUMBER FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID) AS "ANALYST_BADGE_ID", (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 = PART_HDR.ORG_ID AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID ) AS "ANALYST_RESOURCE_PROFILE_ID", (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 = PART_HDR.ORG_ID AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID ) AS "OWNER_EMAIL_ID", PART_DTLS.ATTRIBUTE_NUMBER1 "HR_TI", PART_DTLS.CURRENCY_CODE "HOME_CURRENCY", PART_DTLS.ATTRIBUTE_CHAR2 HR_Status, PART_DTLS.ATTRIBUTE_CHAR17 SalesRep 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 ), MULTI_CURR_YN AS ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID, --COUNT(MULTI_CURR.HOME_CURRENCY_CODE) 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 "HOME_CURRENCY_CODE", PART_PAY.PAYRUN_ID "PAYRUN_ID", PB.ORG_ID, PB.PAY_PERIOD_ID "PERIOD_ID", SUM(PART_PAY.PAY_AMT_PAY_CURR) "TotalAmt" --COUNT(PART_PAY.HOME_CURRENCY_CODE) MULTI_CURR_YN 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 WHERE PART_PAY.PAY_AMT_PAY_CURR <> 0 --AND PART_PAY.CREDITED_PARTICIPANT_ID = 134136 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 --WHERE MULTI_CURR.PARTICIPANT_ID = 134136 --AND MULTI_CURR.TotalAmt <> 0 GROUP BY MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID ) SELECT --TO_CHAR(CONCAT(CONCAT(SPD.PAYRUN_ID, '-'), SPD.PAYSHEET_ID)) "Record Name", 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", PD_PYMT_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(PD_PAY_DT.HR_TI,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(SPD.TOTAL_AMT,4) "TotalPaysheetAmount_c", ROUND(PM_TI.Prorated_MonthlyTI,4) "ProratedTI_c", PD_PYMT_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", 'Analyst - Missed Deadline' "MPAReasonCode_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", --PAY_CAL.PAYSHEET_CUTOFF_START_DT, TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MM:SS PM') "PaysheetAutomatedRefreshEndCST_c", SPD.PAYSHEET_ID "Paysheet_id" /*SPD.PAYSHEET_ID "Paysheet_id", PD_PAY_DT.Employment_Country "Employment Country", PD_PAY_DT.ANALYST_PARTY_ID "ANALYST_PARTY_ID", --SPD.PAY_GROUP_ID, --SPD.PLAN_COMPONENT_ID "Plan component Id", PAY_CAL.PAYSHEET_CUTOFF_START_DT, --SPD.PAYSHEET_ID, --SPD.COMM_DUE_BB "BEGINNING_BALANCE_LC", --SPD.BONUS_PTD "PTD_EARNINGS_LC", SPD.PAYSHEET_STATUS "PrePayHub Status",*/ 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.PARTICIPANT_ID = PD_PAY_DT.PARTICIPANT_ID --AND PRD.PARTICIPANT_DETAIL_ID = PD_PAY_DT.PARTICIPANT_DETAIL_ID --AND TRUNC(SPD.PAY_DATE) BETWEEN PRD.START_DATE_ACTIVE AND NVL(PRD.END_DATE_ACTIVE,'9999-12-31') 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 TRUNC(SPD.PAY_DATE) BETWEEN PM_TI.START_DATE AND PM_TI.END_DATE 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 TO_CHAR(PAY_CAL.PERIOD_ID) = TO_CHAR(SPD.PAY_PERIOD_ID) 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.PARTICIPANT_ID = PD_PAY_DT.PARTICIPANT_ID AND PD_PYMT_DT.ORG_ID = SPD.ORG_ID --AND PD_PYMT_DT.ORG_ID = PD_PAY_DT.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 UPPER(SPD.PAYSHEET_STATUS) = 'UNPAID' --AND (SPD.CREDITED_PARTICIPANT_ID = :P_PARTICIPANT_ID --134135--54000 AND (SPD.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID) OR COALESCE(:P_PARTICIPANT_ID,'ALL') = 'ALL') --OR SPD.PAYRUN_ID = :P_PAYRUN_ID --OR SPD.PAYRUN_NAME = :P_PAYRUN_NAME 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) --2025007 AND (SPD.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT,'ALL') = 'ALL')

Comments

Popular posts from this blog

Section 15-Done

Section 16-Done

Section 13-Done