1 PACKAGE PA_HR_UPDATE_API AUTHID CURRENT_USER AS
2 -- $Header: PARHRUPS.pls 120.3.12000000.4 2007/04/05 14:16:01 kjai ship $
3
4 -- This Procedure checks whether the given OU is a Valid or Not
5 PROCEDURE check_exp_OU(p_org_id IN NUMBER
6 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
7 ,x_error_message_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
8 );
9 -- This an wrapper api for check_exp_ou this
10 -- Function returns 'Y' if the OU is valid otherwise 'N'
11 FUNCTION validate_exp_OU (p_org_id IN NUMBER)
12 return VARCHAR2;
13
14
15 -- This Procedure is called from workflow process to update/create resources in projects
16 -- the workflow would be kicked of by the database trigger on table Hr_Organization_Information
17 -- and Pa_All_Organization entities.
18 -- 1.Whenever the default operating Unit which is
19 -- stored in Hr_Organization_Information.Org_information1 changes / modified ,the
20 -- trigger kicks off the workflow and calls this api to Update the Pa_Resource_OU
21 -- entity.
22 -- 2.Whenever the new record is inserted into Pa_All_Organizations with Pa_Org_Use_type
23 -- is of type 'Expenditure' or the exisitng record in Pa_all_Organiations
24 -- is updated with inactive_date then trigger fires and kicks of the workflow,calls this
25 -- api to Update the Pa_Resource_OU.
26 PROCEDURE Default_OU_Change
27 ( P_calling_mode IN VARCHAR2
28 ,P_Organization_id IN Hr_Organization_Information.Organization_id%type
29 ,P_Default_OU_new IN Hr_Organization_Information.Org_Information1%type
30 ,P_Default_OU_old IN Hr_Organization_Information.Org_Information1%type
31 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
32 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
33 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
34 );
35
36
37 -- This Procedure is called from workflow process to update/create resources in projects
38 -- The workflow would be kicked of by the database trigger on table Hr_Organization_Information
39 -- It will update the job levels information if the Project Resource Job Group is changed
40 -- Created by adabdull 2-JAN-2002
41 PROCEDURE Proj_Res_Job_Group_Change
42 ( p_calling_mode IN VARCHAR2
43 ,p_organization_id IN Hr_Organization_Information.Organization_id%type
44 ,p_proj_job_group_new IN Hr_Organization_Information.Org_Information1%type
45 ,p_proj_job_group_old IN Hr_Organization_Information.Org_Information1%type
46 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
47 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
49 );
50
51 --This API pulls all resources into PA from HR for a given organization
52 --Created by virangan 11-JUN-2001
53
54 PROCEDURE pull_resources( p_organization_id IN pa_all_organizations.organization_id%type
55 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
56 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
57 ,x_msg_count OUT NOCOPY NUMBER ); --File.Sql.39 bug 4440895
58
59
60 -- This API will be called from workflow process to update resources in projects.
61 -- The workflow would be kicked of by the database trigger on pa_all_organization entity
62 -- whenever a inactive_date in pa_all_organization is updated this api get kicked of by the
63 -- workflow.
64
65 PROCEDURE make_resource_inactive
66 (P_calling_mode IN VARCHAR2
67 ,P_Organization_id IN Hr_Organization_Information.Organization_id%type
68 ,P_Default_OU IN pa_all_organizations.org_id%type
69 ,P_inactive_date IN pa_all_organizations.inactive_date%type
70 ,P_Default_OU_NEW IN pa_all_organizations.org_id%type DEFAULT NULL --Added for bug 5330402
71 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
72 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
73 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
74 );
75
76 -- This Procedure is kicked off by the workflow when jei_infomration2
77 -- which stores the jobs billability information. whenever the row is updated
78 -- or inserted into per_job_extra_info entity which stores the job information
79 -- and types a database triggers fires and kicks of the workflow
80 -- This procedure makes calls to forecast regenerate apis and create resource
81 -- denorm apis to to update the new billability for the resource
82
83 PROCEDURE per_job_extra_billability
84 (p_calling_mode IN VARCHAR2
85 ,P_job_id IN per_jobs.job_id%type
86 ,P_billable_flag_new IN per_job_extra_info.jei_information2%type
87 ,P_billable_flag_old IN per_job_extra_info.jei_information2%type
88 ,P_utilize_flag_old IN per_job_extra_info.jei_information3%type
89 ,P_utilize_flag_new IN per_job_extra_info.jei_information3%type
90 ,P_job_level_new IN per_job_extra_info.jei_information4%type
91 ,P_job_level_old IN per_job_extra_info.jei_information4%type
92 ,p_schedulable_flag_new IN per_job_extra_info.jei_information6%type
93 ,p_schedulable_flag_old IN per_job_extra_info.jei_information6%type
94 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
95 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
96 ,x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
97
98
99 -- This Procedure will get a list of all affected jobs due to change in the grade,
100 -- sequence or mapping and then calls to PRM API GET_JOB_LEVEL in a loop which actually
101 -- updates the levels in the denorm table
102 -- whenever a grade,sequence,jobmapping columns in per_grades,per_valid_grades,pa_job_relationships
103 -- respectively updated,workflow will kickoff this api from the database trigger on table
104 -- per_grades,Per_valid_grades,pa_job_relationships
105 -- Requirements for calling this api from triggers different entities.
106 -- Per_Grades_Entity--
107 -- IN Parameters
108 -- P_calling_mode,P_per_grades_grade_id,P_per_grades_sequence_new -- for INSERT trigger
109 -- P_calling_mode,P_per_grades_grade_id,P_per_grades_sequence_new,P_per_grades_sequence_old -- for UPDATE
110 -- P_calling_mode,P_per_grades_grade_id,P_per_grades_sequence_old --- for DELETE trigger
111 -- Per_Valid_Grades Entity--
112 -- IN Parameters
113 -- P_calling_mode,P_per_valid_grade_job_id,P_per_valid_grade_id_new -- for INSERT
114 -- P_calling_mode,P_per_valid_grade_job_id,P_per_valid_grade_id_new,P_per_valid_grade_id_old -- UPDATE
115 -- P_calling_mode,P_per_valid_grade_job_id,P_per_valid_grade_id_old -- for DELETE trigger
116 -- Pa_Job_Relationships Entity--
117 -- IN Parameters
118 -- P_calling_mode,P_from_job_id_new,P_to_job_id_new,P_from_job_group_id,P_to_job_group_id -- INSERT
119 -- P_calling_mode,P_from_job_id_new,P_to_job_id_new,P_from_job_group_id,P_to_job_group_id,
120 -- P_from_job_id_old,P_to_job_id_old ----------- UPDATE trigger
121 -- P_calling_mode,P_from_job_id_old,P_to_job_id_old,P_from_job_group_id,P_to_job_group_id -- DELETE
122
123 PROCEDURE update_job_levels
124 ( P_calling_mode IN VARCHAR2
125 ,P_per_grades_grade_id IN per_grades.grade_id%type DEFAULT NULL
126 ,P_per_grades_sequence_old IN NUMBER DEFAULT NULL
127 ,P_per_grades_sequence_new IN NUMBER DEFAULT NULL
128 ,P_per_valid_grade_job_id IN per_valid_grades.valid_grade_id%type DEFAULT NULL
129 ,P_per_valid_grade_id_old IN per_grades.grade_id%type DEFAULT NULL
130 ,P_per_valid_grade_id_new IN per_grades.grade_id%type DEFAULT NULL
131 ,P_from_job_id_old IN pa_job_relationships.from_job_id%type DEFAULT NULL
132 ,P_from_job_id_new IN pa_job_relationships.from_job_id%type DEFAULT NULL
133 ,P_to_job_id_old IN pa_job_relationships.to_job_id%type DEFAULT NULL
134 ,P_to_job_id_new IN pa_job_relationships.to_job_id%type DEFAULT NULL
135 ,P_from_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
136 ,P_to_job_group_id IN pa_job_relationships.to_job_id%type DEFAULT NULL
137 ,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
138 ,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
139 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
140 );
141
142 -- This API returns the Billability of job / person
143 -- The IN parameters will be Person_id and Date for Person's Billability
144 -- and Job_id for the Job's billability
145 FUNCTION check_job_billability
146 (
147 P_job_id IN number
148 ,P_person_id IN number
149 ,P_date IN date
150 ) RETURN VARCHAR2;
151
152 pragma RESTRICT_REFERENCES(check_job_billability,WNDS,WNPS);
153
154 ----------------------------------------------------------------
155 -- This API returns the schedulable_flag value of the passed job
156 ----------------------------------------------------------------
157 FUNCTION check_job_schedulable
158 (
159 p_job_id IN NUMBER DEFAULT NULL
160 ,p_person_id IN NUMBER DEFAULT NULL
161 ,p_date IN DATE DEFAULT NULL
162 ) RETURN VARCHAR2;
163
164 pragma RESTRICT_REFERENCES(check_job_schedulable,WNDS,WNPS);
165
166
167 -- This API returns the utilization of job / person
168 -- The IN parameters will be Person_id and Date for Person's Billability
169 -- OR Job_id for the Job's billability
170 FUNCTION check_job_utilization
171 (
172 P_job_id IN number
173 ,P_person_id IN number
174 ,P_date IN date
175 ) RETURN VARCHAR2;
176 pragma RESTRICT_REFERENCES(check_job_utilization,WNDS,WNPS);
177
178
179
180 -- This API returns the job group id for the corresponding Job
181 FUNCTION get_job_group_id(
182 P_job_id IN per_jobs.job_id%type
183 ) RETURN per_job_groups.job_group_id%type;
184
185 -- pragma RESTRICT_REFERENCES (get_job_group_id, WNDS, WNPS );
186
187 FUNCTION get_job_name(
188 P_job_id IN per_jobs.job_id%type
189 ) RETURN per_jobs.name%type;
190
191 -- pragma RESTRICT_REFERENCES (get_job_name, WNDS, WNPS );
192
193 FUNCTION get_org_name(
194 P_org_id IN hr_all_organization_units.organization_id%type
195 ) RETURN hr_all_organization_units.name%type;
196
197 -- pragma RESTRICT_REFERENCES (get_org_name, WNDS, WNPS );
198
199 FUNCTION get_grade_name(
200 P_grade_id IN NUMBER
201 ) RETURN VARCHAR2 ;
202
203 -- pragma RESTRICT_REFERENCES (get_grade_name, WNDS, WNPS );
204
205
206 -- This Function returns the job level(sequence) based on the job_id and Job_group_id
207 FUNCTION get_job_level(
208 P_job_id IN per_jobs.job_id%type
209 ,P_job_group_id IN per_job_groups.job_group_id%type
210 ) RETURN NUMBER;
211
212 -- This Function returns boolean value of true if a job is master job otherwise
213 -- it returns false -- IN parameter will be job_id
214 FUNCTION check_master_job(P_job_id IN per_Jobs.job_id%type)
215 RETURN boolean;
216 -- pragma RESTRICT_REFERENCES (check_master_job, WNDS, WNPS );
217
218 -- This Procedure updates the pa_resource_OU and set the resources
219 -- end date active to sysdate when pa_all_organizations.inactive_date
220 -- is updated.
221 PROCEDURE Update_OU_resource(P_default_OU_old IN Pa_all_organizations.org_id%type
222 ,P_default_OU_new IN Pa_all_organizations.org_id%type
223 ,P_resource_id IN Pa_Resources_denorm.resource_id%type
224 default NULL
225 ,P_person_id IN Pa_Resources_denorm.person_id%type
226 default NULL
227 ,P_start_date IN Date default NULL
228 ,P_end_date_old IN Date default NULL
229 ,P_end_date_new IN Date default NULL
230 ,x_return_status IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
231 ,x_msg_data IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
232 ,x_msg_count IN OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
233 );
234 PROCEDURE Update_EndDate(p_person_id IN per_all_people_f.person_id%TYPE,
235 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
236 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
237 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
238 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
239 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
240 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
241 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
242
243 PROCEDURE Update_Org(p_person_id IN per_all_people_f.person_id%TYPE,
244 p_old_org_id IN per_all_assignments_f.organization_id%TYPE,
245 p_new_org_id IN per_all_assignments_f.organization_id%TYPE,
246 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
247 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
248 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
249 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
250 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
251 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
252 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
253
254 FUNCTION check_pjr_default_ou(P_Organization_id IN Hr_Organization_Information.Organization_id%type,
255 P_Default_OU_new IN Hr_Organization_Information.Org_Information1%type) RETURN VARCHAR2; -- Bug 4656855
256 pragma RESTRICT_REFERENCES (check_pjr_default_ou, WNDS, WNPS);
257
258 FUNCTION Belongs_ExpOrg(p_org_id IN per_all_assignments_f.organization_id%TYPE) RETURN VARCHAR2;
259 pragma RESTRICT_REFERENCES (Belongs_ExpOrg, WNDS, WNPS);
260
261 FUNCTION Get_DefaultOU(p_org_id IN per_all_assignments_f.organization_id%TYPE) RETURN NUMBER ;
262 pragma RESTRICT_REFERENCES (Get_DefaultOU, WNDS, WNPS);
263
264 PROCEDURE Update_Job(p_person_id IN per_all_people_f.person_id%TYPE,
265 p_old_job IN per_all_assignments_f.job_id%TYPE,
266 p_new_job IN per_all_assignments_f.job_id%TYPE,
267 p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
268 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
269 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
270 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
271 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
272
273 PROCEDURE Update_Supervisor(p_person_id IN per_all_people_f.person_id%TYPE,
274 p_old_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
275 p_new_supervisor IN per_all_assignments_f.supervisor_id%TYPE,
276 p_new_start_date IN per_all_assignments_f.effective_start_date%TYPE,
277 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
281
278 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
279 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
280 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
282 PROCEDURE Update_PrimaryFlag(p_person_id IN per_all_people_f.person_id%TYPE,
283 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
284 p_new_start_date IN per_all_assignments_f.effective_end_date%TYPE,
285 p_old_end_date IN per_all_assignments_f.effective_start_date%TYPE,
286 p_new_end_date IN per_all_assignments_f.effective_end_date%TYPE,
287 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
288 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
289 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
290
291 PROCEDURE Update_Name(
292 p_person_id IN per_all_people_f.person_id%TYPE,
293 p_old_name IN per_all_people_f.full_name%TYPE,
294 p_new_name IN per_all_people_f.full_name%TYPE,
295 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
296 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
297 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
298
299 -- Added the following parameters for the bug fix :1583544
300 -- p_date_from_old,p_date_from_new,p_date_to_old,p_date_to_new,
301 -- p_addr_prim_flag_old,p_addr_prim_flag_new
302 -- to update the pa_resource_denorm when person address changes with respect
303 -- date
304 PROCEDURE address_change ( p_calling_mode in varchar2,
305 p_person_id in number,
306 p_country_old in varchar2,
307 p_country_new in varchar2,
308 p_city_old in varchar2,
309 p_city_new in varchar2,
310 p_region2_old in varchar2,
311 p_region2_new in varchar2,
312 p_date_from_old in date,
313 p_date_from_new in date,
314 p_date_to_old in date,
315 p_date_to_new in date,
316 p_addr_prim_flag_old in varchar2,
317 p_addr_prim_flag_new in varchar2,
318 x_return_status out NOCOPY varchar2, --File.Sql.39 bug 4440895
319 x_msg_count out NOCOPY number, --File.Sql.39 bug 4440895
320 x_msg_data out NOCOPY varchar2); --File.Sql.39 bug 4440895
321
322 FUNCTION Get_Country_name(p_country_code VARCHAR2) RETURN VARCHAR2 ;
323
324 -- Procedure to delete the records in pa_resources_denorm when the corresponding records
325 -- in per_all_assignments_f are deleted.
326 PROCEDURE Delete_PA_Resource_Denorm(
327 p_person_id IN per_all_people_f.person_id%TYPE,
328 p_old_start_date IN per_all_assignments_f.effective_start_date%TYPE,
329 p_old_end_date IN per_all_assignments_f.effective_end_date%TYPE,
330 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
331 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
332 x_msg_count OUT NOCOPY NUMBER); --File.Sql.39 bug 4440895
333
334 -- PROCEDURE
335 -- withdraw_cand_nominations
336 -- PURPOSE
337 -- to withdraw all PJR candidate nominations for this
338 -- person_id when the person is terminated in HR
339 -- or the assignment organization no longer belongs to
340 -- expenditure hierarchy
341 -- HISTORY
342 -- 05-MAR-207 kjai Created for Bug 5683340
343 --
344 PROCEDURE withdraw_cand_nominations
345 ( p_person_id IN NUMBER,
346 p_effective_date IN DATE,
347 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
348 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
349 x_msg_data OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
350
351 END PA_HR_UPDATE_API;
[Home] [Help]