---#####################
PRE-STG - table Load :
---################
begin
delete from EBS_ora.pay_run_results;
commit;
insert into EBS_ora.pay_run_results select * from pay_run_results@appsread ;
commit;
delete from EBS_ora.pay_assignment_actions;
commit;
insert into EBS_ora.pay_assignment_actions select * from
pay_assignment_actions@appsread ;
commit;
delete from EBS_ora.pay_element_types_f;
commit;
insert into EBS_ora.pay_element_types_f select * from
pay_element_types_f@appsread ;
commit;
delete from EBS_ora.pay_run_result_values;
commit;
insert into EBS_ora.pay_run_result_values select * from
pay_run_result_values@appsread ;
commit;
delete from pay_element_classifications;
commit;
insert into EBS_ora.pay_element_classifications select * from
pay_element_classifications@appsread ;
commit;
delete from EBS_ora.pay_input_values_f;
commit;
insert into EBS_ora.pay_input_values_f select * from
pay_input_values_f@appsread ;
commit;
delete from EBS_ora.pay_element_types_f_tl;
commit;
insert into EBS_ora.pay_element_types_f_tl select * from
pay_element_types_f_tl@appsread ;
commit;
delete from EBS_ora.pay_payroll_actions_v;
commit;
insert into pay_payroll_actions_v select * from pay_payroll_actions_v@appsread ;
commit;
delete from EBS_ora.per_assignments_f;
commit;
insert into EBS_ora.per_assignments_f select * from per_assignments_f@appsread ;
commit;
delete from EBS_ora.per_all_people_f;
commit;
insert into EBS_ora.per_all_people_f select * from per_all_people_f@appsread ;
commit;
delete from EBS_ora.hrfv_supervisors;
commit;
insert into EBS_ora.hrfv_supervisors select * from hrfv_supervisors@appsread ;
commit;
end;
---#####################
LOAD Data STG -Payroll View to table :
---################
begin
insert into EBS_ora.stg_NAS_PAYROLL select * from EBS_ora.stg_NAS_PAYROLL_v where
period_name='2023-12';
end
---#####################
LOAD Data SIL to Final table :
---################
begin
insert into EBS_ora.D_NAS_PAYROLL
(TRX_SK ,
PERIOD_NAME,
EMPLOYEE_NUMBER,
FULL_NAME,
ORGANIZATION_NAME,
JOB_NAME,
GRADE_NAME,
POSITION_NAME,
SUPERVISOR_NAME,
ANNUALTICKETALLOWANCE,
BASICSALARY,
BASICSALARYADJUSTMENT,
CHAMBERFEES,
CHECKAIRMANALLOWANCE,
CHECKFLIGHTATTENDANTALLOWANCE,
CHIEFPILOTALLOWANCE,
COSTOFLIVINGALLOWANCE,
COSTOFLIVINGADJUSTMENT,
CREWOVERTIME,
CREWPERDIEMRECURRING,
DIRECTORFLIGHTALLOWANCE,
DRIVERALLOWANCE,
DUTYALLOWANCE,
EDUCATIONALLOWANCE,
EMPLOYEEGOSIANNUITIES,
EMPLOYEEGOSIARREARSPAYMENT,
EMPLOYEEUNEMPLOYMENTFUND,
EMPLOYERGOSIANNUITIES,
EMPLOYERGOSIHAZARDS,
EMPLOYERUNEMPLOYMENTFUND,
ENDOFSERVICEPROVISION,
ENGINERUNUPANDTAXIALLOWANCE,
EXTRAALLOWANCEUNSCHFLIGH,
EXTRASPECIALALLOWANCEFIXED,
EXTRASPECIALALLPERCENTAGE,
EXTRATRANSPORTATIONALLOWANCE,
FLEETMANAGERALLOWANCE,
FLYINGALLOWANCE,
GCCGOSIDEDUCTIONEMPLOYEE,
GCCGOSIDEDUCTIONEMPLOYER,
GOSIREFERENCESALARY,
GUARANTEEDFLYINGALLOWANCE,
HOUSINGADVANCE,
HOUSINGADVANCERECOVERY,
HOUSINGALLOWANCE,
HOUSINGALLOWANCEADJUSTMENT,
INCHARGEALLOWANCE,
INSPECTIONALLOWANCE,
LFAEXTRASPECIALALLOWANCE,
LICENSEALLOWANCE,
LINETRAININGINSPECTORALLOWANCE,
MISCELLANEOUSALLNONRECURRING,
MISCELLANEOUSALLOWRECURRING,
MISCELLANEOUSDEDUCTIONSNONREC,
MISCELLANEOUSDEDUCTIONSREC,
NATIONALALLOWANCE,
NATIONALALLOWANCEADJUSTMENT,
NONCREWOVERTIME,
OVERNIGHTSTAY_LAYOVER,
PARKINGALLOWANCE,
POROSCOPEINSPECTIONALLOWANCE,
RELOCATIONALLOWANCE,
SIMULATORINSPECTORALLOWANCE,
TRAININGMANAGERALLOWANCE,
TRANSPORTATIONALLOWANCE,
TRANSPORTATIONALLOWADJUSTMENT,
TYPERATINGALLOWANCE,
TYPERATINGALLOWANCEADJUSTMENT,
UNPAIDLEAVEDEDUCTION,
ORGANIZATION_ID,
ANNUAL_TICKETS,
INSURANCE_COST,
LABOR_FEE
)
select
rownum as TRX_SK ,
PERIOD_NAME,
EMPLOYEE_NUMBER,
FULL_NAME,
nvl(ORGANIZATION_NAME, '-') ORGANIZATION_NAME,
JOB_NAME,
GRADE_NAME,
POSITION_NAME,
SUPERVISOR_NAME,
ANNUALTICKETALLOWANCE,
BASICSALARY,
BASICSALARYADJUSTMENT,
CHAMBERFEES,
CHECKAIRMANALLOWANCE,
CHECKFLIGHTATTENDANTALLOWANCE,
CHIEFPILOTALLOWANCE,
COSTOFLIVINGALLOWANCE,
COSTOFLIVINGADJUSTMENT,
CREWOVERTIME,
CREWPERDIEMRECURRING,
DIRECTORFLIGHTALLOWANCE,
DRIVERALLOWANCE,
DUTYALLOWANCE,
EDUCATIONALLOWANCE,
EMPLOYEEGOSIANNUITIES,
EMPLOYEEGOSIARREARSPAYMENT,
EMPLOYEEUNEMPLOYMENTFUND,
EMPLOYERGOSIANNUITIES,
EMPLOYERGOSIHAZARDS,
EMPLOYERUNEMPLOYMENTFUND,
ENDOFSERVICEPROVISION,
ENGINERUNUPANDTAXIALLOWANCE,
EXTRAALLOWANCEUNSCHFLIGH,
EXTRASPECIALALLOWANCEFIXED,
EXTRASPECIALALLPERCENTAGE,
EXTRATRANSPORTATIONALLOWANCE,
FLEETMANAGERALLOWANCE,
FLYINGALLOWANCE,
GCCGOSIDEDUCTIONEMPLOYEE,
GCCGOSIDEDUCTIONEMPLOYER,
GOSIREFERENCESALARY,
GUARANTEEDFLYINGALLOWANCE,
HOUSINGADVANCE,
HOUSINGADVANCERECOVERY,
HOUSINGALLOWANCE,
HOUSINGALLOWANCEADJUSTMENT,
INCHARGEALLOWANCE,
INSPECTIONALLOWANCE,
LFAEXTRASPECIALALLOWANCE,
LICENSEALLOWANCE,
LINETRAININGINSPECTORALLOWANCE,
MISCELLANEOUSALLNONRECURRING,
MISCELLANEOUSALLOWRECURRING,
MISCELLANEOUSDEDUCTIONSNONREC,
MISCELLANEOUSDEDUCTIONSREC,
NATIONALALLOWANCE,
NATIONALALLOWANCEADJUSTMENT,
NONCREWOVERTIME,
OVERNIGHTSTAY_LAYOVER,
PARKINGALLOWANCE,
POROSCOPEINSPECTIONALLOWANCE,
RELOCATIONALLOWANCE,
SIMULATORINSPECTORALLOWANCE,
TRAININGMANAGERALLOWANCE,
TRANSPORTATIONALLOWANCE,
TRANSPORTATIONALLOWADJUSTMENT,
TYPERATINGALLOWANCE,
TYPERATINGALLOWANCEADJUSTMENT,
UNPAIDLEAVEDEDUCTION,
ORGANIZATION_ID,
ANNUAL_TICKETS,
INSURANCE_COST,
LABOR_FEE
from
EBS_ORA.SIL_NAS_PAYROLL_V ;
commit;
end;
CASE SUBSTR (TYPERATINGALLOWANCE, 1, 1) WHEN '.' THEN SUBSTR (TYPERATINGALLOWANCE,
2, 8) ELSE TYPERATINGALLOWANCE END
'34812','52515','54146','54321','54783','54895','55812'
