[Home] [Help]
PACKAGE BODY: APPS.HR_CCMGR_SS
Source
1 PACKAGE BODY HR_CCMGR_SS AS
2 /* $Header: hrccmwrs.pkb 120.1 2006/02/07 14:56:50 snachuri noship $ */
3
4 -- Global Variables
5 l_trans_tbl hr_transaction_ss.transaction_table;
6 g_package Varchar2(30):='HR_CCMGR_SS';
7 g_api_name Varchar2(30):='HR_CCMGR_SS.PROCESS_API';
8 g_date_format Varchar2(10):='RRRR-MM-DD';
9 g_data_error Exception;
10
11 -- Cursors
12 cursor c_supDetails (p_id Number) IS
13 Select s.full_name, s.person_id
14 From per_all_people_f s, per_all_assignments_f paf
15 Where paf.person_id = p_id
16 and paf.supervisor_id = s.person_id
17 and paf.primary_flag = 'Y'
18 and s.current_employee_flag = 'Y'
19 and paf.assignment_type = 'E'
20 and trunc(sysdate) between s.effective_start_date and s.effective_end_date
21 and trunc(sysdate) between paf.effective_start_date and paf.effective_end_date;
22
23 cursor c_noaccess_list (p_id Varchar2) IS
24 Select haotl.name organization_name,
25 pap.full_name manager_name,
26 fnd_date.canonical_to_date(cm.ORG_INFORMATION3) start_Date,
27 fnd_date.canonical_to_date(cm.ORG_INFORMATION4) end_Date,
28 decode((decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('HR_ALL_ORGANIZATION_UNITS', HAO.ORGANIZATION_ID)
29 ),'TRUE',0,1)+ decode(decode(hr_general.get_xbg_profile,'Y', hao.business_group_id , hr_general.get_business_group_id),hao.business_group_id,0,1)
30 + decode(decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', PAP.PERSON_ID, PAP.PERSON_TYPE_ID, PAP.EMPLOYEE_NUMBER,PAP.APPLICANT_NUMBER)
31 ),'TRUE',0,1) + decode(decode(hr_general.get_xbg_profile,'Y',pap.business_group_id , hr_general.get_business_group_id),pap.business_group_id,0,1)),0,'Y','N') hasUpdateAccess
32 from hr_organization_information cm,per_all_people_f pap
33 ,hr_all_organization_units hao ,hr_all_organization_units_tl haotl
34 where cm.org_information_context = 'Organization Name Alias'
35 and cm.ORG_INFORMATION2 = p_id
36 and cm.ORG_INFORMATION2 = to_char(pap.person_id)
37 and pap.current_employee_flag = 'Y'
38 and hao.organization_id = cm.organization_id
39 and pap.person_id=p_id
40 and trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
41 and trunc(sysdate) between hao.date_from and nvl(hao.date_to,trunc(sysdate))
42 and HAO.ORGANIZATION_ID = HAOTL.ORGANIZATION_ID
43 and HAOTL.LANGUAGE = USERENV('LANG')
44 and trunc(sysdate) between hao.date_from and nvl(hao.date_to,trunc(sysdate))
45 and exists (select 'e' from hr_organization_information class,
46 hr_org_info_types_by_class ctype
47 where ctype.org_information_type = 'Organization Name Alias'
48 and ctype.org_classification = class.org_information1
49 and class.org_information_context = 'CLASS'
50 and class.org_information2 = 'Y'
51 and class.organization_id = cm.organization_id)
52 /* Excluding pending approval */
53 and not exists (select 'e' from hr_api_transaction_steps s, hr_api_transactions t,
54 hr_api_transaction_values v
58 and v.name = 'P_ORGANIZATION_ID'
55 where s.api_name = 'HR_CCMGR_SS.PROCESS_API'
56 and s.transaction_id = t.transaction_id and status = 'Y'
57 and s.transaction_step_id = v.transaction_step_id
59 and v.number_value = hao.organization_id
60 and rownum < 2)
61 and (nvl(fnd_date.canonical_to_date(cm.ORG_INFORMATION4),sysdate) >= sysdate
62 Or (fnd_date.canonical_to_date(cm.ORG_INFORMATION4) <= sysdate
63 and fnd_date.canonical_to_date(cm.ORG_INFORMATION3)
64 = (select max(fnd_date.canonical_to_date(oi.ORG_INFORMATION3))
65 from hr_organization_information oi
66 where oi.org_information_context = 'Organization Name Alias'
67 and oi.organization_id = cm.organization_id)));
68
69 --
70 procedure get_supervisor_details(p_emp_id IN Number,
71 p_sup_id OUT NOCOPY Number,
72 p_sup_name OUT NOCOPY Varchar2) IS
73
74 l_proc Varchar2(200) := g_package || 'get_supervisor_details';
75 Begin
76 hr_utility.set_location(' Entering:' || l_proc,5);
77 Open c_supDetails(p_emp_id);
78 Fetch c_supDetails into p_sup_name, p_sup_id;
79 hr_utility.set_location( l_proc , 10);
80 If c_supDetails%notfound Then
81 hr_utility.set_location( l_proc,15);
82 p_sup_id := Null;
83 p_sup_name := Null;
84 End If;
85 hr_utility.set_location( l_proc, 20);
86 close c_supDetails;
87 hr_utility.set_location(' Leaving:' || l_proc,25);
88
89 Exception When others then
90 hr_utility.set_location(' Leaving:' || l_proc,555);
91 close c_supDetails;
92 p_sup_id := Null;
93 p_sup_name := Null;
94 hr_utility.set_location(' Leaving:' || l_proc,560);
95 End get_supervisor_details;
96
97 procedure get_noaccess_list(document_id IN Varchar2,
98 display_type IN Varchar2,
99 document IN OUT NOCOPY Clob,
100 document_type IN OUT NOCOPY Varchar2) IS
101 l_proc Varchar2(200) := g_package || 'get_noaccess_list';
102 Begin
103 hr_utility.set_location(' Entering:' || l_proc,5);
104 /* ld_effective_date := wf_engine.GetItemAttrDate
105 (itemtype => itemtype
106 ,itemkey => itemkey
107 ,aname => 'CURRENT_EFFECTIVE_DATE'); */
108
109 -- hr_java_script_web.alert('Person Id:'||document_id);
110 document_type := 'text/html';
111 hr_utility.set_location(l_proc,10);
112 For I in c_noaccess_list(document_id) Loop
113 If I.hasUpdateAccess = 'N' Then
114 WF_NOTIFICATION.WriteToClob(document, I.organization_name||'<br>');
115 End If;
116 End Loop;
117 hr_utility.set_location(' Leaving:' || l_proc,15);
118 End get_noaccess_list;
119
120 procedure issue_notify(itemtype IN Varchar2,
121 itemkey IN Varchar2,
122 actid IN Number,
123 funmode IN Varchar2,
124 result OUT NOCOPY Varchar2) IS
125 l_proc Varchar2(200) := g_package || 'issue_notify';
126 lnSupId Number;
127 lnSupName Varchar2(240);
128 lnSupUserName Varchar2(30);
129 Begin
130 hr_utility.set_location(' Entering:' || l_proc,5);
131 result := 'COMPLETE:'||'N';
132 lnSupId := wf_engine.GetItemAttrNumber(itemtype => itemtype,
133 itemkey => itemkey,
134 aname => 'HR_MGR_ID_OF_LOGIN_PERSON');
135 hr_utility.set_location(l_proc,10);
136 --
137 If (lnSupId is not Null And
138 nvl(wf_engine.GetItemAttrText(itemtype => itemtype, itemkey => itemkey,
139 aname => 'HR_TERM_SUP_FLAG'),'#') = 'Y') Then
140 hr_utility.set_location(l_proc,15);
141 result := 'COMPLETE:'||'Y';
142 wf_directory.GetUserName ('PER',
143 lnSupId,
144 lnSupUserName,
145 lnSupName);
146 hr_utility.set_location(l_proc,20);
147 wf_engine.SetItemAttrText(itemtype,
148 itemkey,
149 'SUPERVISOR_USERNAME',
150 lnSupUserName);
151 hr_utility.set_location(l_proc,25);
152 wf_engine.SetItemAttrText(itemtype,
153 itemkey,
154 'SUPERVISOR_DISPLAY_NAME',
155 lnSupName);
156 End If;
157 hr_utility.set_location(' Leaving:' || l_proc,30);
158 Exception
159 When Others then
160 result := 'COMPLETE:'||'N';
161 hr_utility.set_location(' Leaving:' || l_proc,555);
162 End issue_notify;
163
164 --
165 procedure delete_trans_steps(itemtype IN Varchar2
166 ,itemkey IN Varchar2
167 ,actid IN Number) IS
168 l_proc Varchar2(200) := g_package || 'delete_trans_steps';
169 Begin
170 hr_utility.set_location(' Entering:' || l_proc,5);
171 if (itemtype is Null or itemkey is Null) Then
172 hr_utility.set_location(' Leaving:' || l_proc,10);
173 return;
174 End If;
175
176 Delete From hr_api_transaction_values tv
177 Where tv.transaction_step_id in (Select ts.transaction_step_id
178 From hr_api_transaction_steps ts
179 Where ts.item_key = itemkey
183 hr_utility.set_location(l_proc,15);
180 And ts.item_type = itemtype
181 And ts.activity_id = actid
182 And ts.api_name = 'HR_CCMGR_SS.PROCESS_API');
184 Delete From hr_api_transaction_steps
185 Where item_key = itemkey
186 And item_type = itemtype
187 And activity_id = actid
188 And api_name = 'HR_CCMGR_SS.PROCESS_API';
189 hr_utility.set_location(' Leaving:' || l_proc,20);
190 End delete_trans_steps;
191
192
193 --
194 procedure process_api(p_validate in boolean default false
195 ,p_transaction_step_id in number default null
196 ,p_effective_date in varchar2 default null) IS
197
198 l_proc Varchar2(200) := g_package || 'process_api';
199 l_ccmgr_rec HR_CCMGR_TYPE;
200 l_eff_date Date;
201 l_warning boolean;
202 Begin
203 hr_utility.set_location(' Entering:' || l_proc,5);
204 l_eff_date := nvl(hr_transaction_api.get_date_value(p_transaction_step_id, 'P_EFFECTIVE_DATE'),trunc(sysdate));
205
206 dt_fndate.set_effective_date(l_eff_date);
207 hr_utility.set_location(l_proc,10);
208
209 l_ccmgr_rec := HR_CCMGR_TYPE(hr_transaction_api.get_number_value(p_transaction_step_id, 'P_ORG_INFORMATION_ID'),
210 hr_transaction_api.get_number_value(p_transaction_step_id, 'P_ORGANIZATION_ID'),
211 Null, -- organization name
212 hr_transaction_api.get_number_value(p_transaction_step_id, 'P_CURR_MANAGER_ID'),
213 hr_transaction_api.get_number_value(p_transaction_step_id, 'P_CURR_MANAGER'),
214 hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_ORG_INFORMATION1'),
215 hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_MANAGER_ID'),
216 hr_transaction_api.get_varchar2_value(p_transaction_step_id, 'P_MANAGER'),
217 hr_transaction_api.get_date_value(p_transaction_step_id, 'P_CURR_START_DATE'),
218 hr_transaction_api.get_date_value(p_transaction_step_id, 'P_START_DATE'),
219 hr_transaction_api.get_date_value(p_transaction_step_id, 'P_CURR_END_DATE'),
220 hr_transaction_api.get_date_value(p_transaction_step_id, 'P_END_DATE'),
221 hr_transaction_api.get_number_value(p_transaction_step_id, 'P_OVN_NUMBER'),
222 Null, Null, Null);
223 hr_utility.set_location(l_proc,15);
224
225 -- SFL on update page might not have captured manager info.
226 if (l_ccmgr_rec.manager_id is null and l_ccmgr_rec.manager is null) then
227 hr_utility.set_location(l_proc,20);
228 l_ccmgr_rec.manager := l_ccmgr_rec.current_manager;
229 l_ccmgr_rec.manager_id := l_ccmgr_rec.current_manager_id;
230 end if;
231 hr_utility.set_location(l_proc,25);
232 validate_ccmgr_record(l_ccmgr_rec, p_validate,l_eff_date, l_warning);
233 hr_utility.set_location(' Leaving:' || l_proc,30);
234 Exception When others then
235 hr_utility.set_location(' Leaving:' || l_proc,555);
236 raise;
237 End process_api;
238
239 --
240 Procedure validate_ccmgr_record(p_ccmgr_rec IN HR_CCMGR_TYPE
241 ,p_validate_mode IN boolean Default true
242 ,p_eff_date IN Date
243 ,p_warning OUT NOCOPY Boolean) IS
244 l_proc Varchar2(200) := g_package || 'validate_ccmgr_record';
245 x_obj_number Number;
246 x_org_information_id Number;
247 l_manager_id Number;
248 l_start_date Date;
249 l_end_date Date;
250 l_upd_mode Boolean;
251 l_mode Varchar2(30);
252 Begin
253 hr_utility.set_location(' Entering:' || l_proc,5);
254 l_upd_mode := true;
255 savepoint save_ccmgr_rec;
256
257 If (p_ccmgr_rec.current_manager_id = nvl(p_ccmgr_rec.manager_id,p_ccmgr_rec.current_manager_id) And
258 nvl(p_ccmgr_rec.current_start_date,p_ccmgr_rec.start_date) = p_ccmgr_rec.start_date) Then
259 -- mode CORRECTION iff startDate and manager have not changed
260 hr_utility.set_location(l_proc,10);
261 l_mode := 'CORRECTION';
262 l_manager_id := p_ccmgr_rec.current_manager_id;
263 l_start_date := p_ccmgr_rec.start_date;
264 l_end_date := p_ccmgr_rec.end_date;
265 Else
266 hr_utility.set_location(l_proc,15);
267 l_mode := 'UPDATE';
268 l_manager_id := p_ccmgr_rec.current_manager_id;
269 l_start_date := p_ccmgr_rec.current_start_date;
270 l_end_date := p_ccmgr_rec.start_date-1;
271 If (p_ccmgr_rec.current_end_date is not Null) Then
272 l_upd_mode := false;
273 End If;
274 End If;
275 hr_utility.set_location(l_proc,20);
276 x_obj_number := p_ccmgr_rec.object_version_number;
277
278 If (l_upd_mode) Then
279 hr_utility.set_location(l_proc,25);
280 hr_organization_api.update_org_manager
281 (p_validate => false
282 ,p_effective_date => p_eff_date
283 ,p_organization_id => p_ccmgr_rec.organization_id
284 ,p_org_information_id => p_ccmgr_rec.org_information_id
285 ,p_org_info_type_code => 'Organization Name Alias'
286 ,p_org_information1 => p_ccmgr_rec.org_information1
287 ,p_org_information2 => l_manager_id
288 ,p_org_information3 => fnd_date.date_to_canonical(l_start_date)
289 ,p_org_information4 => fnd_date.date_to_canonical(l_end_date)
293 ,p_org_information8 => Null
290 ,p_org_information5 => Null
291 ,p_org_information6 => Null
292 ,p_org_information7 => Null
294 ,p_org_information9 => Null
295 ,p_org_information10 => Null
296 ,p_org_information11 => Null
297 ,p_org_information12 => Null
298 ,p_org_information13 => Null
299 ,p_org_information14 => Null
300 ,p_org_information15 => Null
301 ,p_org_information16 => Null
302 ,p_org_information17 => Null
303 ,p_org_information18 => Null
304 ,p_org_information19 => Null
305 ,p_org_information20 => Null
306 ,p_object_version_number => x_obj_number
307 ,p_warning => p_warning);
308 End If;
309 hr_utility.set_location(l_proc,30);
310 If (p_ccmgr_rec.current_manager_id <> p_ccmgr_rec.manager_id Or
311 p_ccmgr_rec.current_start_date <> p_ccmgr_rec.start_date) Then
312 -- creating the new record
313 hr_utility.set_location(l_proc,35);
314 hr_organization_api.create_org_manager
315 (p_validate => false
316 ,p_effective_date => p_eff_date
317 ,p_organization_id => p_ccmgr_rec.organization_id
318 ,p_org_info_type_code => 'Organization Name Alias'
319 ,p_org_information1 => p_ccmgr_rec.org_information1
320 ,p_org_information2 => p_ccmgr_rec.manager_id
321 ,p_org_information3 => fnd_date.date_to_canonical(p_ccmgr_rec.start_date)
322 ,p_org_information4 => fnd_date.date_to_canonical(p_ccmgr_rec.end_date)
323 ,p_org_information5 => Null
324 ,p_org_information6 => Null
325 ,p_org_information7 => Null
326 ,p_org_information8 => Null
327 ,p_org_information9 => Null
328 ,p_org_information10 => Null
329 ,p_org_information11 => Null
330 ,p_org_information12 => Null
331 ,p_org_information13 => Null
332 ,p_org_information14 => Null
333 ,p_org_information15 => Null
334 ,p_org_information16 => Null
335 ,p_org_information17 => Null
336 ,p_org_information18 => Null
337 ,p_org_information19 => Null
338 ,p_org_information20 => Null
339 ,p_org_information_id => x_org_information_id
340 ,p_object_version_number => x_obj_number
341 ,p_warning => p_warning);
342 End If;
343 hr_utility.set_location(l_proc,40);
344 If p_validate_mode = true Then
345 hr_utility.set_location(l_proc,45);
346 rollback to save_ccmgr_rec;
347 End If;
348 hr_utility.set_location(' Leaving:' || l_proc,50);
349
350 Exception When others then
351 hr_utility.set_location(' Leaving:' || l_proc,555);
352 rollback to save_ccmgr_rec;
353 raise;
354 End validate_ccmgr_record;
355
356 --
357 Function getattrName(msgName OUT NOCOPY Varchar2) Return Varchar2 IS
358 l_proc Varchar2(200) := g_package || 'getattrName';
359 Begin
360
361 hr_utility.set_location(' Entering:' || l_proc,5);
362 If (sqlcode <> '-20001') Then
363 hr_utility.set_location(' Leaving:' || l_proc,10);
364 Return Null; End If;
365 msgName := ltrim(replace(replace(sqlerrm,'ORA-20001',''),':',''));
366 If msgName in ('PER_289693_START_DATE_NULL','PER_289695_START_DATE_FORMAT',
367 'PER_289697_START_BEFORE_END','PER_289699_START_DATE_BFR_HIRE') Then
368 hr_utility.set_location(' Leaving:' || l_proc,15);
369 return 'StartDate';
370 Elsif msgName in ('PER_289696_END_DATE_FORMAT','PER_289700_END_DATE_AFTER_TERM') Then
371 hr_utility.set_location(' Leaving:' || l_proc,20);
372 return 'EndDate';
373 Elsif msgName in ('PER_289694_NO_MANAGER','PER_289698_PERSON_ID_INVALID',
374 'PER_289702_INVALID_MANAGER','PER_289703_CCM_OVERLAP') Then
375 hr_utility.set_location(' Leaving:' || l_proc,25);
376 return 'Manager';
377 Elsif msgName in ('PER_289746_CCM_AFTER_ORG','PER_289701_INVALID_COST_CENTER') Then
378 hr_utility.set_location(' Leaving:' || l_proc,30);
379 return 'OrganizationName';
380 End If;
381 hr_utility.set_location(' Leaving:' || l_proc,35);
382 return Null;
383 End getattrName;
384
385 --
386 Procedure update_ccmgr_recs(p_item_key IN Varchar2
387 ,p_item_type IN Varchar2
388 ,p_activity_id IN Number
389 ,p_login_person_id IN OUT NOCOPY Number
390 ,p_ccmgr_tbl IN OUT NOCOPY HR_CCMGR_TABLE
391 ,p_mode IN Varchar2 Default '#'
392 ,p_error_message OUT NOCOPY Long
393 ,p_status OUT NOCOPY Varchar2) IS
394 l_proc Varchar2(200) := g_package || 'update_ccmgr_recs';
395 l_transaction_id Number:=Null;
396 x_trans_ovn Number:=Null;
397 l_result Varchar2(100);
398 l_count Number;
399 x_warning Boolean;
400 l_attrName Varchar2(30);
401 l_eff_date Date;
402
403
404 Begin
405 hr_utility.set_location(' Entering:' || l_proc,5);
406 p_login_person_id := nvl(fnd_global.employee_id,p_login_person_id);
407
408 Begin
409 hr_utility.set_location(l_proc,10);
410 l_eff_date := nvl(fnd_date.canonical_to_date(wf_engine.GetItemAttrText(p_item_key,p_item_type,'P_EFFECTIVE_DATE')),trunc(sysdate));
411 Exception when others then
412 l_eff_date := trunc(sysdate);
416 l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type ,p_item_key);
413 hr_utility.set_location(l_proc,555);
414 End;
415 hr_utility.set_location(l_proc,15);
417 dt_fndate.set_effective_date(l_eff_date);
418
419 If l_transaction_id is Null Then
420 hr_utility.set_location(l_proc,20);
421 hr_transaction_ss.start_transaction
422 (itemtype => p_item_type
423 ,itemkey => p_item_key
424 ,actid => p_activity_id
425 ,funmode => 'RUN'
426 ,p_login_person_id => p_login_person_id
427 ,result => l_result);
428 l_transaction_id := hr_transaction_ss.get_transaction_id(p_item_type ,p_item_key);
429 End If;
430 hr_utility.set_location(l_proc,25);
431 For I in 1 .. p_ccmgr_tbl.count Loop
432 Begin
433 hr_utility.set_location(l_proc || 'Entering: p_ccmgr_tbl.count Loop' ,30);
434 If nvl(p_mode,'#') <> 'S' Then
435
436 if (p_ccmgr_tbl(I).manager is not Null and p_ccmgr_tbl(I).manager_id is Null) Then
437 Begin
438 hr_utility.set_location(l_proc ,35);
439 Select person_id into p_ccmgr_tbl(I).manager_id
440 From per_all_people_f
441 where full_name = p_ccmgr_tbl(I).manager
442 and current_employee_flag = 'Y'
443 and trunc(sysdate) between effective_start_date and effective_end_date;
444
445 Exception when others then
446 p_status := 'E';
447 p_ccmgr_tbl(I).p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
448 p_attr_name => 'Manager'
449 ,p_app_short_name => 'PER'
450 ,p_message_name => 'HR_CCMGR_USE_MGR_LOV');
451 hr_utility.set_location(l_proc,560);
452 goto exit_point;
453 End;
454 End If;
455 hr_utility.set_location(l_proc ,40);
456 -- Record has not changed
457 If p_ccmgr_tbl(I).current_manager_id = nvl(p_ccmgr_tbl(I).manager_id,p_ccmgr_tbl(I).current_manager_id) And
458 nvl(trunc(p_ccmgr_tbl(I).current_start_date),sysdate) = nvl(trunc(p_ccmgr_tbl(I).start_date),sysdate) And
459 nvl(trunc(p_ccmgr_tbl(I).current_end_date),sysdate) = nvl(trunc(p_ccmgr_tbl(I).end_date),sysdate) Then
460 -- Rolling back unchanged saved step if not Save for later mode.
461 hr_utility.set_location(l_proc ,45);
462 If (p_ccmgr_tbl(I).trans_step_id is not Null) Then
463 hr_utility.set_location(l_proc ,50);
464 Delete From hr_api_transaction_values where transaction_step_id = p_ccmgr_tbl(I).trans_step_id;
465 Delete From hr_api_transaction_steps where transaction_step_id = p_ccmgr_tbl(I).trans_step_id;
466 End If;
467 hr_utility.set_location(l_proc ,55);
468 goto exit_point;
469 End If;
470
471 -- correction
472 hr_utility.set_location(l_proc ,60);
473 If p_ccmgr_tbl(I).manager is Null Then
474 hr_utility.set_location(l_proc ,65);
475 p_ccmgr_tbl(I).manager := p_ccmgr_tbl(I).current_manager;
476 p_ccmgr_tbl(I).manager_id := p_ccmgr_tbl(I).current_manager_id;
477 End If;
478 hr_utility.set_location(l_proc ,70);
479 Begin
480 -- invoke orgInformation api
481 x_warning := False;
482 validate_ccmgr_record(p_ccmgr_tbl(I), true, l_eff_date, x_warning);
483 If x_warning Then -- handling api warnings
484 hr_utility.set_location(l_proc ,75);
485 p_ccmgr_tbl(I).p_warning_message := hr_java_conv_util_ss.get_formatted_error_message(
486 p_attr_name => 'OrganizationName'
487 ,p_error_message => p_ccmgr_tbl(I).p_warning_message
488 ,p_app_short_name => 'PER'
489 ,p_message_name => 'PER_289738_CCM_GAPS_CREATED');
490 p_status := 'W';
491
492 End If;
493 hr_utility.set_location(l_proc ,80);
494 Exception when others then
495 -- handling api errors
496 p_status := 'E';
497 l_attrName := getattrName(p_ccmgr_tbl(I).p_error_message);
498 hr_utility.set_location(l_proc,565);
499 If (l_attrName is not null) Then
500 hr_utility.set_location(l_proc,570);
501 p_ccmgr_tbl(I).p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
502 p_attr_name => l_attrName
503 ,p_app_short_name => 'PER'
504 ,p_message_name => p_ccmgr_tbl(I).p_error_message);
505 Else
506 hr_utility.set_location( l_proc,575);
507 p_ccmgr_tbl(I).p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
508 p_attr_name => 'Manager'
509 ,p_app_short_name => 'ERR'
510 ,p_single_error_message => nvl(fnd_message.get,
511 nvl(hr_utility.get_message,substr(sqlerrm,255))));
512 End If;
513 End;
514 End If;
515
516 --
517 hr_utility.set_location(l_proc ,85);
518 If p_ccmgr_tbl(I).trans_step_id is Null Then
519 hr_utility.set_location(l_proc ,90);
520 x_trans_ovn := Null;
521 hr_transaction_api.create_transaction_step
522 (p_validate => false
523 ,p_creator_person_id => p_login_person_id
524 ,p_transaction_id => l_transaction_id
525 ,p_api_name => g_package||'.PROCESS_API'
529 ,p_transaction_step_id => p_ccmgr_tbl(I).trans_step_id
526 ,p_item_type => p_item_type
527 ,p_item_key => p_item_key
528 ,p_activity_id => p_activity_id
530 ,p_object_version_number => x_trans_ovn);
531 End If;
532 hr_utility.set_location(l_proc ,95);
533 -- populating transaction table
534
535 l_count := 1;
536 l_trans_tbl(l_count).param_name := 'P_ORG_INFORMATION_ID';
537 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).org_information_id;
538 l_trans_tbl(l_count).param_data_type := 'NUMBER';
539
540 l_count := l_count+1;
541 l_trans_tbl(l_count).param_name := 'P_OVN_NUMBER';
542 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).object_version_number;
543 l_trans_tbl(l_count).param_data_type := 'NUMBER';
544
545 l_count := l_count+1;
546 l_trans_tbl(l_count).param_name := 'P_ORGANIZATION_ID';
547 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).organization_id;
548 l_trans_tbl(l_count).param_data_type := 'NUMBER';
549
550 l_count := l_count+1;
551 l_trans_tbl(l_count).param_name := 'P_ORGANIZATION_NAME';
552 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).organization_name;
553 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
554
555 l_count := l_count+1;
556 l_trans_tbl(l_count).param_name := 'P_CURR_MANAGER_ID';
557 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).current_manager_id;
558 l_trans_tbl(l_count).param_data_type := 'NUMBER';
559
560 l_count := l_count+1;
561 l_trans_tbl(l_count).param_name := 'P_CURR_MANAGER';
562 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).current_manager;
563 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
564
565 l_count := l_count+1;
566 l_trans_tbl(l_count).param_name := 'P_ORG_INFORMATION1';
567 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).org_information1;
568 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
569
570 l_count := l_count+1;
571 l_trans_tbl(l_count).param_name := 'P_MANAGER_ID';
572 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).manager_id;
573 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
574
575 l_count := l_count+1;
576 l_trans_tbl(l_count).param_name := 'P_MANAGER';
577 l_trans_tbl(l_count).param_value := p_ccmgr_tbl(I).manager;
578 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
579
580 l_count := l_count+1;
581 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_DATE';
582 l_trans_tbl(l_count).param_value := to_char(l_eff_date,g_date_format);
583 l_trans_tbl(l_count).param_data_type := 'DATE';
584
585 l_count := l_count+1;
586 l_trans_tbl(l_count).param_name := 'P_CURR_START_DATE';
587 l_trans_tbl(l_count).param_value := to_char(p_ccmgr_tbl(I).current_start_date,g_date_format);
588 l_trans_tbl(l_count).param_data_type := 'DATE';
589
590 l_count := l_count+1;
591 l_trans_tbl(l_count).param_name := 'P_START_DATE';
592 l_trans_tbl(l_count).param_value := to_char(p_ccmgr_tbl(I).start_date,g_date_format);
593 l_trans_tbl(l_count).param_data_type := 'DATE';
594
595 l_count := l_count+1;
596 l_trans_tbl(l_count).param_name := 'P_CURR_END_DATE';
597 l_trans_tbl(l_count).param_value := to_char(p_ccmgr_tbl(I).current_end_date,g_date_format);
598 l_trans_tbl(l_count).param_data_type := 'DATE';
599
600 l_count := l_count+1;
601 l_trans_tbl(l_count).param_name := 'P_END_DATE';
602 l_trans_tbl(l_count).param_value := to_char(p_ccmgr_tbl(I).end_date,g_date_format);
603 l_trans_tbl(l_count).param_data_type := 'DATE';
604
605 hr_utility.set_location(l_proc ,100);
606 l_count := l_count+1;
607 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
608 l_trans_tbl(l_count).param_value := wf_engine.GetActivityAttrText(p_item_type,p_item_key,
609 p_activity_id, 'HR_REVIEW_REGION_ITEM', False);
610
611 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
612
613 hr_utility.set_location(l_proc ,105);
614 l_count := l_count+1;
615 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
616 l_trans_tbl(l_count).param_value := p_activity_id;
617 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
618 hr_utility.set_location(l_proc ,110);
619 hr_transaction_ss.save_transaction_step
620 (p_item_type => p_item_type
621 ,p_item_key => p_item_key
622 ,p_actid => p_activity_id
623 ,p_login_person_id => p_login_person_id
624 ,p_transaction_step_id => p_ccmgr_tbl(I).trans_step_id
625 ,p_api_name => g_package||'.PROCESS_API'
626 ,p_transaction_data => l_trans_tbl);
627 hr_utility.set_location(l_proc ,115);
628 <<exit_point>> Null;
629 commit;
630 hr_utility.set_location(l_proc || 'Leaving: p_ccmgr_tbl.count Loop' ,120);
631 Exception when others then
632 p_status := 'E';
633 p_ccmgr_tbl(I).p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
634 p_error_message => p_ccmgr_tbl(I).p_error_message
635 ,p_attr_name => 'Manager'
636 ,p_single_error_message => substr(sqlerrm,255));
637 hr_utility.set_location(l_proc ,555);
638 End;
639 End Loop;
640 hr_utility.set_location(' Leaving:' || l_proc,125);
641 Exception when others then
642 p_status := 'E';
643 hr_utility.set_location(' Leaving:' || l_proc,560);
644 raise;
645 End update_ccmgr_recs;
646 End HR_CCMGR_SS;