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
Post a Comment