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