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

Popular posts from this blog

Section 15-Done

Section 16-Done

Section 13-Done