Section 12-Done
SELECT
-- Build unique record identifier
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)
)
|| '-' || PN.person_number
|| '-' || BG.PAYSHEET_ID
) AS RecordId_c,
-- Basic identifiers
BG.PAYRUN_ID AS PayRunId_c,
BG.PAYSHEET_ID AS PaysheetId_c,
BG.PAY_PERIOD_ID AS PeriodId_c,
PN.person_number AS PersonNumber_c,
PN.PARTICIPANT_NAME AS ParticipantName_c,
BG.CREDITED_PARTICIPANT_ID AS ParticipantId_c,
-- Dates
TO_CHAR(BG.PAY_DATE, 'YYYY-MM-DD') AS PayDate_c,
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'YYYY-MM-DD') AS PaysheetCutoffDate_c,
TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'YYYY-MM-DD') AS MPACutoffStartDate_c,
TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'YYYY-MM-DD') AS MPACutoffEndDate_c,
TO_CHAR(PAY_CAL.PAYROLL_SUBMISSION_END_CST, 'YYYY-MM-DD') AS PayrollSubmissionEndDate_c,
-- Plan and role information
LPC.PLAN_COMPONENT_ID AS PlanComponentId_c,
LPC.PLAN_COMPONENT_name AS PlanComponentName_c,
LPC.INCENTIVE_TYPE AS IncentiveType_c,
LPC.ROLE_NAME AS RoleName_c,
BG.PAY_GROUP_NAME AS PayGroupName_c,
-- Currency information
BG.LATEST_HOME_CURRENCY AS HomeCurrency_c,
-- **MODIFIED: Use DUAL_SNAP instead of parDetails**
DUAL_SNAP.HRStatus_c AS HRStatus_c,
DUAL_SNAP.SalesRP_c AS SalesRP_c,
DUAL_SNAP.EmploymentCountry_c AS EmploymentCountry_c,
-- HR Country and Analyst
parDetails.COUNTRY_CODE AS HRCountry_c,
(
SELECT PARTY_NAME
FROM HZ_PARTIES
WHERE PARTY_ID = parDetails.ANALYST_PARTY_ID
) AS AnalystName_c,
-- HR TI (Target Incentive)
TO_CHAR(parDetails.PD_HR_TI, 'FM9999999990.0000') AS HRTI_c,
-- Prorated Monthly TI
TO_CHAR(
NVL(PM_TI.Prorated_MonthlyTI, 0),
'FM9999999990.0000'
) AS ProratedMonthlyTI_c,
Comments
Post a Comment