1 CREATE OR REPLACE PACKAGE BODY SON_CREATE_ASSGMT_PKG 2 AS 3 /*************************************************************** 4 ** 5 ** 6 ** Package : SON_CREATE_ASSGMT_PKG .pkb(package body) 7 ** $Logfile : $ 8 ** $Creation Date : 18/06/2009 9 ** $Author : Monika Bhakuni 10 ** 11 ** (C) Copyright: 12 ** 13 ** ------------------------------------------------------------ 14 ** 15 ** Description : The package body contains procedure for 16 ** Creating employee asignments data to Apps 17 ** tables using the API procedure 18 ** 19 ** EQHDOK 20 ** ------------------------------------------------------------ 21 ** 22 ** $Log: $ 23 ** 24 **************************************************************/ 25 PROCEDURE Create_assignment(errbuf out varchar2,retcode out varchar2) 26 IS 27 l_total_records NUMBER; 28 l_success_records NUMBER := 0; 29 l_failure_records NUMBER := 0; 30 l_flag CHAR := 'Y'; 31 l_organisation_id Number; 32 l_organisation_name Varchar2(100); 33 l_Job_id NUmber; 34 l_Job_name Varchar2(100); 35 l_grade_id Number; 36 l_Grade_name Varchar2(100); 37 l_Location_id Number; 38 l_Location_name Varchar2(100); 39 l_Group_id Number; 40 l_Person_id Number; 41 l_Group_name Varchar2(100); 42 l_Position_id Number; 43 l_Position_name Varchar2(100); 44 l_Payroll_id Number; 45 l_Payroll_name Varchar2(100); 46 l_status Varchar2(100); 47 l_Assignment_number Number; 48 l_Assignment_type Varchar2(10); 49 l_Salary_basis Varchar2(100); 50 l_supervisor_id Number; 51 l_supervisor_name Varchar2(100); 52 l_supervisor_assignment_id Number; 53 l_effective_date Date; 54 l_working_hours Varchar2(10); 55 l_frequency Varchar2(100); 56 l_Time_normal_start Varchar2(10); 57 l_Time_normal_finish Varchar2(10); 58 l_Probation_Unit Varchar2(100); 59 l_Probation_Period Number; 60 l_Date_Probation_end Date; 61 l_Notice_period Number; 62 l_Notice_Period_UOM Varchar2(30); 63 --l_mar_status_code varchar2(100); 64 /* Cusror to get records from the temporary table */ 65 CURSOR assn 66 IS 67 SELECT LINEID,table_STATUS,ERROR_MESSAGE,assignment_id,organisation_id,organisation_name,Job_id,Job_name,grade_id,Grade_name,Location_id,Location_name,Group_id, 68 Group_name,Position_id,Position_name,Payroll_id,Payroll_name,status,Assignment_number,Assignment_type,Salary_basis,supervisor_id, 69 supervisor_name ,supervisor_assignment_id,effective_date,working_hours,frequency,Time_normal_start,Time_normal_finish, Probation_Unit, 70 Probation_Period,Date_Probation_end,Notice_period,Notice_Period_UOM 71 FROM xx_assignment_api_test; 72 BEGIN 73 SELECT COUNT (*) 74 INTO l_total_records 75 FROM xx_assignment_api_test; 76 FOR i IN assn 77 LOOP 78 /* The API is called with the relevent parameters */ 79 BEGIN 80 --l_assignment_number :=i.assignment_number; 81 hr_assignment_api.update_gb_emp_asg 82 ( p_validate => false 83 ,p_effective_date => to_date(sysdate)--career.effective_start_date--lv_effective_start_date 84 ,p_datetrack_update_mode => 'CORRECTION' 85 ,p_assignment_id => 64 86 ,p_object_version_number => 13 --v_object_version_number 87 ,p_supervisor_id => 62 --career.supervisor_id 88 ,p_assignment_number => 4--TRIM(career.assignment_number) 89 --,p_change_reason => TRIM(career.change_reason) 90 --,p_frequency => career.frequency 91 ,p_manager_flag => 'N'--career.manager_flag 92 --,p_normal_hours => career.normal_hours 93 --,p_perf_review_period => career.perf_review_period 94 --,p_perf_review_period_frequency => career.perf_review_period_frequency 95 ,p_probation_period => 6 96 ,p_probation_unit => 'M' 97 ,p_sal_review_period => 1 98 ,p_sal_review_period_frequency => 'Y' 99 --,p_time_normal_finish => career.time_normal_finish 100 --,p_time_normal_start => career.time_normal_start 101 --,p_ass_attribute1 => TO_CHAR(to_date(career.ass_attribute1),'YYYY/MM/DD HH24:MI:SS' ) 102 --,p_ass_attribute3 => TRIM(career.ass_attribute3) 103 --,p_ass_attribute4 => TO_CHAR(career.effective_end_date,'YYYY/MM/DD HH24:MI:SS' ) 104 --,p_employee_category => lv_employee_category 105 -- ,p_supervisor_assignment_id => career.supervisor_assignment_id 106 --,p_cagr_grade_def_id => lv_cagr_grade_def_id 107 --,p_cagr_concatenated_segments => lv_cagr_concatenated_segments 108 --,p_concatenated_segments => lv_concatenated_segments 109 --,p_soft_coding_keyflex_id => lv_soft_coding_keyflex_id 110 --,p_comment_id => lv_comment_id 111 --,p_effective_start_date => lv_effective_start_date--career.effective_start_date 112 --,p_effective_end_date => lv_effective_end_date 113 ,p_no_managers_warning => 'S'--lv_org_now_no_manager_warning 114 ,p_other_manager_warning =>'L' --lv_other_manager_warning 115 --,p_hourly_salaried_warning =>'H' --lv_hourly_salaried_warning 116 ); 117 /* 118 hr_assignment_api.update_emp_asg_criteria 119 (p_effective_date => i.effective_date, 120 p_datetrack_update_mode => 'CORRECTION', 121 p_assignment_id => 64 122 -- p_called_from_mass_update => NULL, -- commented the following as null value updated for emploment category #479488 123 -- p_grade_id => i.grade_id, -- commented for Iltis #522390 -- Added for Iltis#515268 124 --p_position_id => i.position_id, 125 --p_job_id => i.job_id, 126 --p_payroll_id => i.payroll_id, 127 --p_location_id => i.location_id, 128 --p_organization_id =>i.organisation_id, 129 --p_pay_basis_id => NULL, 130 --p_segment2 => NULL, 131 --p_segment3 => NULL, 132 --p_segment4 => NULL, 133 -- --p_employment_category => NULL, -- commented the following as null value updated for emploment cat 134 -- p_concat_segments => NULL, 135 -- p_contract_id => NULL, 136 -- p_establishment_id => NULL, 137 -- p_scl_segment1 => NULL, 138 -- p_grade_ladder_pgm_id => NULL, 139 -- p_supervisor_assignment_id => NULL, 140 --p_object_version_number => NULL, 141 --p_special_ceiling_step_id => NULL, 142 --p_people_group_id => 225, 143 --p_soft_coding_keyflex_id => 1043, 144 --p_group_name => i.group_name, 145 --p_effective_start_date => to_date(sysdate), 146 --p_effective_end_date => to_date(sysdate) 147 --p_org_now_no_manager_warning => 'ABC', 148 --p_other_manager_warning => 'XYZ', 149 --p_spp_delete_warning => 'DEF', 150 --p_entries_changed_warning => 'NULL', 151 --p_tax_district_changed_warning => 'WARN', 152 --p_concatenated_segments => NULL, 153 --p_gsp_post_process_warning => NULL 154 ); 155 */ 156 hr_assignment_api.update_emp_asg_criteria 157 (p_effective_date => to_date(01/01/2009,'DD/MM/YYYY')--career.effective_START_Date 158 ,p_datetrack_update_mode => 'CORRECTION' 159 ,p_assignment_id => 64 --career.assignment_id 160 ,p_validate => false 161 ,p_called_from_mass_update => false 162 --,p_grade_id => career.grade_id 163 --,p_position_id => TRIM(career.position_id) 164 ,p_job_id => 105 --career.job_id 165 ,p_payroll_id => 61 --career.payroll_id 166 ,p_location_id => 156--career.location_id 167 ,p_organization_id => 92--career.organization_id 168 ,p_pay_basis_id => 61--career.pay_basis_id 169 -- ,p_employment_category => career.ASSIGNMENT_CATEGORY 170 ,p_object_version_number => 13--lv_object_version_number 171 --,p_special_ceiling_step_id => lv_special_ceiling_step_id 172 --,p_people_group_id => career.people_group_id 173 --,p_soft_coding_keyflex_id => lv_soft_coding_keyflex_id 174 ,p_group_name => 'SSL'--lv_group_name 175 --,p_effective_start_date => lv_effective_start_date 176 --,p_effective_end_date => lv_effective_end_date 177 ,p_org_now_no_manager_warning => 'de'--lv_org_now_no_manager_warning 178 ,p_other_manager_warning =>'gr' --lv_other_manager_warning 179 ,p_spp_delete_warning => 'vgcdfre'--lv_spp_delete_warning 180 ,p_entries_changed_warning => 'vdfwefd'--lv_entries_changed_warning 181 ,p_tax_district_changed_warning => 'vfgf'--lv_tax_district_c_warning 182 ,p_concatenated_segments => 'dsede'--lv_concatenated_segments 183 ,p_gsp_post_process_warning => 'dsqwe'--lv_gsp_post_process_warning 184 ); 185 END; 186 END LOOP; 187 commit; 188 END Create_assignment; 189 /*--End of package--------*/ 190* END SON_CREATE_ASSGMT_PKG; SQL> / Warning: Package Body created with compilation errors. SQL> show err; Errors for PACKAGE BODY SON_CREATE_ASSGMT_PKG: LINE/COL ERROR -------- ----------------------------------------------------------------- 81/4 PL/SQL: Statement ignored 81/4 PLS-00306: wrong number or types of arguments in call to 'UPDATE_GB_EMP_ASG' 156/2 PL/SQL: Statement ignored 156/2 PLS-00306: wrong number or types of arguments in call to 'UPDATE_EMP_ASG_CRITERIA' SQL>