Big MC 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)) || '-' || ( select papf.person_number from CN_SRP_PARTICIPANTS_ALL cspl , hz_person_profiles hpp ,PER_ALL_PEOPLE_F papf where 1 =1 and cspl.party_id = hpp.party_id and papf.person_id = hpp.hr_person_id and bg.CREDITED_PARTICIPANT_ID = cspl.participant_id and rownum =1 ) || '-' || DECODE(UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)) ) "Record Name" ,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)) || '-' || ( select papf.person_number from CN_SRP_PARTICIPANTS_ALL cspl , hz_person_profiles hpp ,PER_ALL_PEOPLE_F papf where 1 =1 and cspl.party_id = hpp.party_id and papf.person_id = hpp.hr_person_id and bg.CREDITED_PARTICIPANT_ID = cspl.participant_id and rownum =1 ) || '-' || DECODE(UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)) ) "Record Number" ,BG.PAYRUN_ID "Payrun ID" , 'Regular cycle' "Payment Cycle" ,BG.CREDITED_PARTICIPANT_ID "Participant Id" ,BG.PAYRUN_NAME "Payment Batch" ,BG.PAY_PERIOD_ID "Pay Period ID" ,BG.PAY_DATE "Pay Date" , ( select papf.person_number from CN_SRP_PARTICIPANTS_ALL cspl , hz_person_profiles hpp ,PER_ALL_PEOPLE_F papf where 1 =1 and cspl.party_id = hpp.party_id and papf.person_id = hpp.hr_person_id and bg.CREDITED_PARTICIPANT_ID = cspl.participant_id and rownum =1 ) "Worker Number" , parDetails.COUNTRY_CODE "HR Country" ,parDetails.ATTRIBUTE_CHAR40 "Employment 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 "ANALYST_PARTY_ID" ,parDetails.ATTRIBUTE_NUMBER1 "HR TI" ,BG.LATEST_HOME_CURRENCY ,parDetails.ATTRIBUTE_CHAR2 "HR Status" ,parDetails.ATTRIBUTE_CHAR17 "SalesRep" ,LATEST_PLAN_COMPONENT.ROLE_NAME "Role Name" , LATEST_PLAN_COMPONENT.START_DATE "Role Start Date" ,LATEST_PLAN_COMPONENT.end_date "Role End Date" , PM_TI.Prorated_MonthlyTI "Prorated TI" -- ,BG.PAY_GROUP_ID ,BG.PAY_GROUP_NAME "Pay group" ,LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID "Plan component Id" ,LATEST_PLAN_COMPONENT.INCENTIVE_TYPE ,PAY_CAL.MPA_CUTOFF_START_DT ,PAY_CAL.MPA_CUTOFF_END_DT ,PAY_CAL.PAYSHEET_CUTOFF_START_DT ,PAY_CAL.PAYSHEET_CUTOFF_DT "PAYSHEET_CUTOFF_END_DT" , 'tbd' "PTD_EARNINGS_LC" ,BG.PAYSHEET_STATUS "PrePayHub Status" ,'tbd' "Total Paysheet Amount" ,PRD_ML.MULTI_CURR_YN ,BG.conversionrate usd_to_local ,'TBD' "Current Earnings Due (CED)_LC" ,'TBD' "BEGINNING_BALANCE_USD" ,'TBD' "PTD_EARNINGS_USD" ,'TBD' "Current Earnings Due (CED)_USD" , 'TBD' "BB % of HR TI" ,BG.PAYSHEET_ID ,BG.PAY_AMT_PAY_CURR_USD ,BG.finalPayout final_payout_LC -- ,PRD_ML.MULTI_CURR_YN ,( select papf.person_number from CN_SRP_PARTICIPANTS_ALL cspl , hz_person_profiles hpp ,PER_ALL_PEOPLE_F papf where 1 =1 and cspl.party_id = hpp.party_id and papf.person_id = hpp.hr_person_id and bg.CREDITED_PARTICIPANT_ID = cspl.participant_id and rownum =1 ) badge_id from ( select after_group.ORG_ID ,after_group.PAYRUN_ID ,after_group.CREDITED_PARTICIPANT_ID ,after_group.PAYRUN_NAME ,after_group.PAY_PERIOD_ID ,after_group.PAY_DATE ,after_group.PAY_GROUP_ID ,after_group.PAY_GROUP_NAME ,after_group.PAYSHEET_ID ,after_group.PAY_AMT_PAY_CURR_USD ,after_group.LATEST_HOME_CURRENCY ,after_group.PAYSHEET_STATUS ,to_char(1/conversion2.CONVERSION_RATE, '00000000.000000000') conversionrate ,to_char(after_group.PAY_AMT_PAY_CURR_USD*(1/conversion2.CONVERSION_RATE), '00000000.000000000' ) finalPayout from ( select before_group1.ORG_ID ,before_group1.PAYRUN_ID ,before_group1.CREDITED_PARTICIPANT_ID ,before_group1.PAYRUN_NAME ,before_group1.PAY_PERIOD_ID ,before_group1.PAY_DATE ,before_group1.PAY_GROUP_ID ,before_group1.PAY_GROUP_NAME ,before_group1.PAYSHEET_ID ,before_group1.PAYSHEET_STATUS ,sum(before_group1.PAY_AMT_PAY_CURR_USD) PAY_AMT_PAY_CURR_USD ,max(before_group1.LATEST_HOME_CURRENCY) LATEST_HOME_CURRENCY from ( select spd.ORG_ID ,spd.PAYRUN_ID ,spd.CREDITED_PARTICIPANT_ID ,spd.PAYRUN_NAME ,spd.PAY_PERIOD_ID ,spd.PAY_DATE ,spd.PAY_GROUP_ID ,spd.PAY_GROUP_NAME ,spd.PAYSHEET_ID ,spd.PAY_AMT_PAY_CURR*conversion.CONVERSION_RATE PAY_AMT_PAY_CURR_USD ,partipant_detail.LATEST_HOME_CURRENCY ,spd.PAYSHEET_STATUS from ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID", P.ORG_ID "ORG_ID", PB.PAYRUN_NAME "PAYRUN_NAME", PB.PAY_PERIOD_ID "PAY_PERIOD_ID",--This is the pay period --P.PAY_PERIOD_ID,--This gives period for which the amount is calculated PB.PAY_DATE "PAY_DATE", PG.PAY_GROUP_ID "PAY_GROUP_ID", PG.PAY_GROUP_NAME "PAY_GROUP_NAME", P.PLAN_COMPONENT_ID "PLAN_COMPONENT_ID", PC.INCENTIVE_TYPE "INCENTIVE_TYPE", PS.PAYSHEET_ID "PAYSHEET_ID", --PS.COMM_DUE_BB "COMM_DUE_BB", PS.BONUS_PTD "BONUS_PTD", (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", P.PAY_AMT_PAY_CURR "PAY_AMT_PAY_CURR", P.HOME_CURRENCY_CODE, p.PAY_CURRENCY_CODE 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_PAY_GROUPS_ALL_VL PG ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID AND PG.ORG_ID = P.ORG_ID JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = P.PAYRUN_ID AND PS.QUOTA_ID IS NULL 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.CREDITED_PARTICIPANT_ID = 134136 ) SPD, (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, CN_CONVERSION_TYPES_TL CT where CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID AND CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE='US' ) conversion , ( SELECT PART_DTLS.PARTICIPANT_ID "PARTICIPANT_ID", PART_HDR.HR_PRIMARY_WORKER_NUMBER "HR_PRIMARY_WORKER_NUMBER", PART_DTLS.PARTICIPANT_DETAIL_ID "PARTICIPANT_DETAIL_ID", PART_DTLS.ORG_ID "PD_ORG_ID", PART_DTLS.START_DATE "PD_START_DATE", PART_DTLS.END_DATE "PD_END_DATE", PART_DTLS.COUNTRY_CODE HR_Country, PART_DTLS.ATTRIBUTE_CHAR40 Employment_Country, (SELECT 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", PART_DTLS.ATTRIBUTE_NUMBER1 "PD_HR_TI", PART_DTLS.CURRENCY_CODE "LATEST_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 -- and PART_DTLS.PARTICIPANT_ID= 134136 ) partipant_detail where TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.TO_CURRENCY = 'USD' AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE and TRUNC(SPD.PAY_DATE) BETWEEN partipant_detail.PD_START_DATE(+) AND NVL(partipant_detail.PD_END_DATE(+),'9999-12-31') AND SPD.CREDITED_PARTICIPANT_ID = partipant_detail.PARTICIPANT_ID(+) AND SPD.ORG_ID = partipant_detail.PD_ORG_ID(+) and SPD.HOME_CURRENCY_CODE = partipant_detail.LATEST_HOME_CURRENCY(+) ) before_group1 group BY before_group1.ORG_ID ,before_group1.PAYRUN_ID ,before_group1.CREDITED_PARTICIPANT_ID ,before_group1.PAYRUN_NAME ,before_group1.PAY_PERIOD_ID ,before_group1.PAY_DATE ,before_group1.PAY_GROUP_ID ,before_group1.PAY_GROUP_NAME ,before_group1.PAYSHEET_ID ,before_group1.PAYSHEET_STATUS ) after_group , (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, CN_CONVERSION_TYPES_TL CT where CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID AND CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE='US' ) conversion2 where TRUNC(after_group.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.TO_CURRENCY = 'USD' AND conversion2.FROM_CURRENCY = after_group.LATEST_HOME_CURRENCY ) BG , ---prd_mc ( 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" , Pd.CURRENCY_CODE "HOME_CURRENCY_CODE" ,PART_PAY.PAYRUN_ID "PAYRUN_ID" ,PART_PAY.ORG_ID ,PART_PAY.PAYMENT_BATCH_PERIOD "PERIOD_ID" ,SUM(round(PART_PAY.COMM_AMT_CALC_CURR,2)) "TotalAmt" FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID AND ( PART_PAY.SOURCE_EVENT_DATE IS NOT NULL AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,PART_PAY.SOURCE_EVENT_DATE ) ) WHERE 1 = 1 --AND PART_PAY.CREDITED_PARTICIPANT_ID in ( 134107 , 134136) GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, Pd.CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_ID, PART_PAY.PAYMENT_BATCH_PERIOD HAVING SUM(round(PART_PAY.COMM_AMT_CALC_CURR,2)) <> 0 ) MULTI_CURR group by MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID ) PRD_ML , --letest plan component (select * from ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID "PARTICIPANT_ID", PB.PAYRUN_NAME, 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.INCENTIVE_TYPE INCENTIVE_TYPE, roles.ROLE_NAME, PG.PAY_GROUP_NAME, trunc(SRP_CP.START_DATE) START_DATE, trunc(SRP_CP.END_DATE) END_DATE, --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_PAY_GROUPS_ALL_TL PG ON pb.PAY_GROUP_ID = pg.PAY_GROUP_ID and pg.language = 'US' 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 JOIN CN_PARTICIPANT_ROLES cpr ON SRP_CP.SRP_RULE_ID = cpr.PARTICIPANT_ROLE_ID and SRP_CP.participant_id =cpr.PARTICIPANT_ID and SRP_CP.SRP_RULE_TYPE_CODE = 'ROLE' JOIN CN_ROLES_TL roles on cpr.role_id = roles.role_id and roles.language = 'US' where 1 = 1 --and PART_PAY.CREDITED_PARTICIPANT_ID = 134136 ) where LATEST_PC_RECORD = 1 ) LATEST_PLAN_COMPONENT , --payrun calendar ( 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, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP009) "MPA_CUTOFF_START_DT", TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010) "MPA_CUTOFF_END_DT", TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP007) "PAYSHEET_CUTOFF_START_DT", TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008) "PAYSHEET_CUTOFF_DT" FROM ZCA_REF_ENTITIES PAYRUN_CAL WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c' )PAY_CAL, (select * from ( 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 ) ) PM_TI where LATEST_TI_RECORD = 1 ) PM_TI , ( select a.analyst_id , b.* from CN_SRP_PARTICIPANTS_ALL a, CN_SRP_PARTICIPANT_DETAILS_ALL b where a.participant_id = b.participant_id ) parDetails where bg.ORG_ID = PRD_ML.ORG_ID and 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 -- and bg.paysheet_id = PRD_ML.paysheet_id and PRD_ML.MULTI_CURR_YN = 'Y' --- and BG.PAY_DATE BETWEEN parDetails.START_DATE AND NVL(parDetails.END_DATE,BG.PAY_DATE) AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID -- --JOIN LATEST_PLAN_COMPONENT LPC and 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 --AND LATEST_PLAN_COMPONENT.INCENTIVE_TYPE = SPD.INCENTIVE_TYPE --paycal join -- done AND PAY_CAL.PAYMENT_BATCH_NAME(+) = UPPER(LATEST_PLAN_COMPONENT.PAYRUN_NAME) AND PAY_CAL.PAYGROUP_NAME(+) = UPPER(LATEST_PLAN_COMPONENT.PAY_GROUP_NAME) --AND TO_CHAR(PAY_CAL.PERIOD_ID) = TO_CHAR(SPD.PAY_PERIOD_ID) AND PAY_CAL.PERIOD_ID(+) = BG.PAY_PERIOD_ID AND PAY_CAL.CURRENCY_CODE(+) = BG.LATEST_HOME_CURRENCY --AND UPPER(PAY_CAL.COUNTRY) = UPPER(PD_PAY_DT.HR_Country) AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE(+)) = UPPER(TO_CHAR(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)) --PMTI join AND 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

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where