[Home] [Help]
PACKAGE BODY: APPS.HR_DOR_REVIEW_SS
Source
1 Package body HR_DOR_REVIEW_SS As
2 /* $Header: hrdorrevss.pkb 120.3.12020000.14 2013/03/27 07:41:18 pathota ship $ */
3 --
4 -- Package variables
5 g_package varchar2(30) := 'HR_DOR_REVIEW_SS';
6 g_data_error exception;
7 --
8
9
10 -- ---------------------------------------------------------------------------
11 -- |----------------------< get_approval_req >--------------------------|
12 -- ---------------------------------------------------------------------------
13 --
14 PROCEDURE get_approval_req (
15 itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
16 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
17 actid IN NUMBER,
18 funcmode IN VARCHAR2,
19 resultout OUT nocopy VARCHAR2 )
20 IS
21
22 l_item_value varchar2(200);
23
24 BEGIN
25 hr_utility.set_location('ENTERING get_approval_req', 10);
26 IF (funcmode='RUN') THEN
27 l_item_value := wf_engine.getItemAttrText(
28 itemtype => itemtype
29 ,itemkey => itemkey
30 , aname => 'HR_RUNTIME_APPROVAL_REQ_FLAG');
31
32 if l_item_value = 'NO' then
33 resultout:='COMPLETE:N';
34 else
35 resultout:='COMPLETE:Y';
36 end if;
37 hr_utility.trace('l_resultout' || resultout);
38
39 RETURN;
40 END IF; --RUN
41
42 IF (funcmode='CANCEL') THEN
43 resultout:='COMPLETE';
44 RETURN;
45 END IF;
46 Exception
47 when others then
48 hr_utility.set_location('ENTERING exception get_approval_req', 10);
49
50 end get_approval_req;
51
52
53
54 --------------------------------------------------------------------------
55 --------------------------Start_transaction-------------------------------
56 -------This method creates record in tables hr_api_transactions and ------
57 -------hr_api_transaction_steps.------------------------------------------
58 --------------------------------------------------------------------------
59
60
61 PROCEDURE start_transaction(
62 p_item_type in varchar2
63 ,p_item_key in varchar2
64 ,p_act_id in number
65 ,p_fun_mode in varchar2
66 ,p_login_person_id in number
67 ,p_product_code in varchar2 default 'PER'
68 ,p_url in varchar2 default null
69 ,p_status in varchar2 default 'W'
70 ,p_section_display_name in varchar2 default null
71 ,p_function_id in number default null
72 ,p_transaction_ref_table in varchar2 default 'HR_DOCUMENT_EXTRA_INFO'
73 ,p_transaction_ref_id in number default null
74 ,p_transaction_type in varchar2 default 'WF'
75 ,p_assignment_id in number default null
76 ,p_api_addtnl_info in varchar2 default null
77 ,p_selected_person_id in number default null
78 ,p_transaction_effective_date in date default sysdate
79 ,p_process_name in varchar2 default null
80 ,p_plan_id in number default null
81 ,p_rptg_grp_id in number default null
82 ,p_effective_date_option in varchar2 default 'E'
83 ,p_save_mode in varchar2 default null
84 ,p_transaction_step_id out nocopy number
85 ,p_transaction_id out nocopy number
86 ,p_error_message out nocopy varchar2)
87 IS
88
89 l_transaction_id number default null;
90 l_transaction_step_id number default null;
91 l_trans_obj_vers_num number default null;
92 l_count integer default 0;
93 l_transaction_table hr_transaction_ss.transaction_table;
94 l_review_item_name varchar2(50);
95 l_message_number VARCHAR2(10);
96 l_result varchar2(100) default null;
97 l_old_transaction_step_id number;
98 l_old_object_version_number number;
99 p_effective_date Date;
100
101 Cursor cur_trans(p_selected_person_id IN number) is
102 select transaction_id
103 from hr_api_transactions
104 where transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
105 and status ='W'
106 and selected_person_id = p_selected_person_id ;
107
108 BEGIN
109 -- hr_utility.trace_on(null,'ORCL1');
110 hr_utility.set_location('Entering '||g_package||'.start_transaction', 10);
111
112
113
114 --
115 IF l_transaction_id is null THEN
116
117 --deleting the transactions that are in status 'W' to avoid inadvertent SFL
118
119 for t_rec in cur_trans(p_selected_person_id)
120 loop
121 hr_transaction_api.rollback_transaction(
122 p_transaction_id => t_rec.transaction_id);
123 end loop;
124
125
126 -- Start a Transaction
127 hr_transaction_ss.start_transaction
128 (itemtype => p_item_type
129 ,itemkey => p_item_key
130 ,actid => p_act_id
131 ,funmode => 'RUN'
132 ,p_login_person_id => p_login_person_id
133 ,p_product_code => p_product_code
134 ,p_url => p_url
135 ,p_status => p_status
136 ,p_section_display_name => p_section_display_name
137 ,p_function_id => p_function_id
138 ,p_transaction_ref_table => p_transaction_ref_table
139 ,p_transaction_ref_id => p_transaction_ref_id
140 ,p_transaction_type => p_transaction_type
141 ,p_assignment_id => p_assignment_id
142 ,p_api_addtnl_info => p_api_addtnl_info
143 ,p_selected_person_id => p_selected_person_id
144 ,p_transaction_effective_date => p_transaction_effective_date
145 ,p_process_name => p_process_name
146 ,p_plan_id => p_plan_id
147 ,p_rptg_grp_id => p_rptg_grp_id
148 ,p_effective_date_option => p_effective_date_option
149 ,result => l_result);
150
151 l_transaction_id := hr_transaction_ss.get_transaction_id
152 (p_item_type => p_item_type
153 ,p_item_key => p_item_key);
154
155 wf_engine.SetItemAttrText(
156 itemtype => p_item_type,
157 itemkey => p_item_key,
158 aname => 'TRANSACTION_ID',
159 avalue => l_transaction_id);
160
161 wf_engine.SetItemAttrText(
162 itemtype => p_item_type,
163 itemkey => p_item_key,
164 aname => 'HR_REVIEW_TEMPLATE_RN_ATTR',
165 avalue => 'DOR_REVIEW_NTF');
166
167 wf_engine.SetItemAttrText(
168 itemtype => p_item_type,
169 itemkey => p_item_key,
170 aname => 'HR_RESTRICT_EDIT_ATTR',
171 avalue => 'Y');
172
173
174 END IF;
175
176 --
177 -- Delete transaction step if exist
178 --
179
180 IF (hr_transaction_api.transaction_step_exist (p_item_type => p_item_type
181 ,p_item_key => p_item_key
182 ,p_activity_id => p_act_id) ) THEN
183
184 hr_transaction_api.get_transaction_step_info(
185 p_item_type => p_item_type
186 ,p_item_key => p_item_key
187 ,p_activity_id => p_act_id
188 ,p_transaction_step_id => l_old_transaction_step_id
189 ,p_object_version_number => l_old_object_version_number);
190
191
192 hr_transaction_api.delete_transaction_step(
193 p_validate => false
194 ,p_transaction_step_id => l_old_transaction_step_id
195 ,p_person_id => p_login_person_id
196 ,p_object_version_number => l_old_object_version_number);
197
198 END IF;
199
200 --
201 -- Create a transaction step
202 --
203 hr_transaction_api.create_transaction_step
204 (p_validate => false
205 ,p_creator_person_id => p_login_person_id
206 ,p_transaction_id => l_transaction_id
207 ,p_api_name => g_package || '.PROCESS_API'
208 ,p_item_type => p_item_type
209 ,p_item_key => p_item_key
210 ,p_activity_id => p_act_id
211 ,p_transaction_step_id => l_transaction_step_id
212 ,p_object_version_number => l_trans_obj_vers_num);
213
214 ----Saving all the mandatory values in the hr_api_transaction_values table----
215
216
217 l_count := l_count + 1;
218 l_transaction_table(l_count).param_name := 'P_LOGIN_PERSON_ID';
219 l_transaction_table(l_count).param_value := p_login_person_id;
220 l_transaction_table(l_count).param_data_type := 'NUMBER';
221
222 l_count := l_count + 1;
223 l_transaction_table(l_count).param_name := 'P_SELECTED_PERSON_ID';
224 l_transaction_table(l_count).param_value := p_selected_person_id;
225 l_transaction_table(l_count).param_data_type := 'NUMBER';
226
227 l_count := l_count + 1;
228 l_transaction_table(l_count).param_name := 'P_ASSIGNMENT_ID';
229 l_transaction_table(l_count).param_value := p_assignment_id;
230 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
231
232 l_count := l_count + 1;
233 l_transaction_table(l_count).param_name := 'P_REVIEW_ACTID';
234 l_transaction_table(l_count).param_value := p_act_id;
235 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
236
237 l_count := l_count + 1;
238 l_transaction_table(l_count).param_name := 'P_ITEM_TYPE';
239 l_transaction_table(l_count).param_value := p_item_type;
240 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
241
242 l_count := l_count + 1;
243 l_transaction_table(l_count).param_name := 'P_ITEM_KEY';
244 l_transaction_table(l_count).param_value := p_item_key;
245 l_transaction_table(l_count).param_data_type := 'VARCHAR2';
246
247 hr_transaction_ss.save_transaction_step
248 (p_item_type => p_item_type
249 ,p_item_key => p_item_key
250 ,p_actid => p_act_id
251 ,p_login_person_id => p_login_person_id
252 ,p_transaction_step_id => l_transaction_step_id
253 ,p_api_name => g_package || '.PROCESS_API'
254 ,p_transaction_data => l_transaction_table);
255
256 p_transaction_step_id := l_transaction_step_id;
257 p_transaction_id := l_transaction_id;
258
259 if p_error_message = 'E' then
260 hr_utility.raise_error;
261 else
262 p_error_message := 'S';
263 end if;
264
265 EXCEPTION
266 WHEN hr_utility.hr_error THEN
267 -- -------------------------------------------
268 -- an application error has been raised so we must
269 -- redisplay the web form to display the error
270 -- --------------------------------------------
271 hr_message.provide_error;
272 l_message_number := hr_message.last_message_number;
273 IF l_message_number = 'APP-7165' OR
274 l_message_number = 'APP-7155' THEN
275 --populate the p_error_message out variable
276 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
277 p_error_message => p_error_message,
278 p_attr_name => 'Page',
279 p_app_short_name => 'PER',
280 p_message_name => 'HR_UPDATE_NOT_ALLOWED');
281 ELSE
282 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
283 p_error_message => p_error_message);
284 END IF;
285 WHEN OTHERS THEN
286 p_error_message := hr_java_conv_util_ss.get_formatted_error_message(
287 p_error_message => p_error_message);
288
289 END start_transaction;
290
291
292 --------------------------------------------------------------------------
293 --------------------------save_transaction_values-------------------------
294 -----This method stores the document information of a person in the ------
295 -----hr_api_transaction_values table--------------------------------------
296 --------------------------------------------------------------------------
297
298
299 PROCEDURE save_transaction_values(
300 p_transaction_step_id in varchar2
301 ,p_login_person_id in varchar2
302 ,p_person_id in number
303 ,p_document_extra_info_id in number
304 ,p_document_type_id in number
305 ,p_date_from in date
306 ,p_date_to in date default null
307 ,p_document_number in varchar2
308 ,p_issued_by in varchar2 default null
309 ,p_issued_at in varchar2 default null
310 ,p_issued_date in date default null
311 ,p_issuing_authority in varchar2 default null
312 ,p_verified_by in number default null
313 ,p_verified_date in date default null
314 ,p_related_object_name in varchar2 default null
315 ,p_related_object_id_col in varchar2 default null
316 ,p_related_object_id in number default null
317 ,p_dei_attribute_category in varchar2 default null
318 ,p_dei_attribute1 in varchar2 default null
319 ,p_dei_attribute2 in varchar2 default null
320 ,p_dei_attribute3 in varchar2 default null
321 ,p_dei_attribute4 in varchar2 default null
322 ,p_dei_attribute5 in varchar2 default null
323 ,p_dei_attribute6 in varchar2 default null
324 ,p_dei_attribute7 in varchar2 default null
325 ,p_dei_attribute8 in varchar2 default null
326 ,p_dei_attribute9 in varchar2 default null
327 ,p_dei_attribute10 in varchar2 default null
328 ,p_dei_attribute11 in varchar2 default null
329 ,p_dei_attribute12 in varchar2 default null
330 ,p_dei_attribute13 in varchar2 default null
331 ,p_dei_attribute14 in varchar2 default null
332 ,p_dei_attribute15 in varchar2 default null
333 ,p_dei_attribute16 in varchar2 default null
334 ,p_dei_attribute17 in varchar2 default null
335 ,p_dei_attribute18 in varchar2 default null
336 ,p_dei_attribute19 in varchar2 default null
337 ,p_dei_attribute20 in varchar2 default null
338 ,p_dei_attribute21 in varchar2 default null
339 ,p_dei_attribute22 in varchar2 default null
340 ,p_dei_attribute23 in varchar2 default null
341 ,p_dei_attribute24 in varchar2 default null
342 ,p_dei_attribute25 in varchar2 default null
343 ,p_dei_attribute26 in varchar2 default null
344 ,p_dei_attribute27 in varchar2 default null
345 ,p_dei_attribute28 in varchar2 default null
346 ,p_dei_attribute29 in varchar2 default null
347 ,p_dei_attribute30 in varchar2 default null
348 ,p_dei_information_category in varchar2 default null
349 ,p_dei_information1 in varchar2 default null
350 ,p_dei_information2 in varchar2 default null
351 ,p_dei_information3 in varchar2 default null
352 ,p_dei_information4 in varchar2 default null
353 ,p_dei_information5 in varchar2 default null
354 ,p_dei_information6 in varchar2 default null
355 ,p_dei_information7 in varchar2 default null
356 ,p_dei_information8 in varchar2 default null
357 ,p_dei_information9 in varchar2 default null
358 ,p_dei_information10 in varchar2 default null
359 ,p_dei_information11 in varchar2 default null
360 ,p_dei_information12 in varchar2 default null
361 ,p_dei_information13 in varchar2 default null
362 ,p_dei_information14 in varchar2 default null
363 ,p_dei_information15 in varchar2 default null
364 ,p_dei_information16 in varchar2 default null
365 ,p_dei_information17 in varchar2 default null
366 ,p_dei_information18 in varchar2 default null
367 ,p_dei_information19 in varchar2 default null
368 ,p_dei_information20 in varchar2 default null
369 ,p_dei_information21 in varchar2 default null
370 ,p_dei_information22 in varchar2 default null
371 ,p_dei_information23 in varchar2 default null
372 ,p_dei_information24 in varchar2 default null
373 ,p_dei_information25 in varchar2 default null
374 ,p_dei_information26 in varchar2 default null
375 ,p_dei_information27 in varchar2 default null
376 ,p_dei_information28 in varchar2 default null
377 ,p_dei_information29 in varchar2 default null
378 ,p_dei_information30 in varchar2 default null
379 ,p_request_id in number default null
380 ,p_program_application_id in number default null
381 ,p_program_id in number default null
382 ,p_program_update_date in date default null
383 ,p_object_version_number in out nocopy number
384 ,p_return_status out nocopy varchar2
385 ) is
386
387 l_proc varchar2(70) := g_package||'.save_transaction_values';
388 p_action_mode varchar2(30);
389 p_transaction_id hr_api_transactions.transaction_id%type;
390 p_org_rec hr_document_extra_info%rowtype;
391 msg_name varchar2(1000);
392 msg_appl varchar2(10);
393
394 BEGIN
395 -- hr_utility.trace_on(null,'ORCL1');
396 hr_utility.set_location('Entering '||l_proc, 30);
397 --
398
399 p_transaction_id := get_transaction_id(p_transaction_step_id);
400 p_action_mode := getActionMode(p_transaction_id);
401
402 /*-----------Flipping the old attachments primary key from docextrainfoid
403 to transactionstepid ----------------------------------*/
404
405 /*save_attachments(
406 p_transaction_id => p_transaction_step_id
407 ,p_document_extra_info_id => p_document_extra_info_id
408 ,p_flip_flag => 'TXN'
409 ,p_return_status => p_return_status);*/
410
411
412 IF p_transaction_step_id IS NOT NULL AND
413 p_login_person_id IS NOT NULL THEN
414
415 hr_transaction_api.set_number_value
416 (p_transaction_step_id => p_transaction_step_id
417 ,p_person_id => p_login_person_id
418 ,p_name => 'P_PERSON_ID'
419 ,p_value => p_person_id);
420
421 hr_transaction_api.set_number_value
422 (p_transaction_step_id => p_transaction_step_id
423 ,p_person_id => p_login_person_id
424 ,p_name => 'P_DOCUMENT_TYPE_ID'
425 ,p_value => p_document_type_id);
426
427 hr_transaction_api.set_number_value
428 (p_transaction_step_id => p_transaction_step_id
429 ,p_person_id => p_login_person_id
430 ,p_name => 'P_DOCUMENT_EXTRA_INFO_ID'
431 ,p_value => p_document_extra_info_id);
432
433 if p_action_mode = 'DOR_INSERT' then
434
435 hr_transaction_api.set_date_value
436 (p_transaction_step_id => p_transaction_step_id
437 ,p_person_id => p_login_person_id
438 ,p_name => 'P_DATE_FROM'
439 ,p_value => p_date_from);
440
441 hr_transaction_api.set_date_value
442 (p_transaction_step_id => p_transaction_step_id
443 ,p_person_id => p_login_person_id
444 ,p_name => 'P_DATE_TO'
445 ,p_value => p_date_to);
446
447 hr_transaction_api.set_varchar2_value
448 (p_transaction_step_id => p_transaction_step_id
449 ,p_person_id => p_login_person_id
450 ,p_name => 'P_DOCUMENT_NUMBER'
451 ,p_value => p_document_number);
452
453 hr_transaction_api.set_varchar2_value
454 (p_transaction_step_id => p_transaction_step_id
455 ,p_person_id => p_login_person_id
456 ,p_name => 'P_ISSUED_BY'
457 ,p_value => p_issued_by);
458
459 hr_transaction_api.set_varchar2_value
460 (p_transaction_step_id => p_transaction_step_id
461 ,p_person_id => p_login_person_id
462 ,p_name => 'P_ISSUED_AT'
463 ,p_value => p_issued_at);
464
465 hr_transaction_api.set_date_value
466 (p_transaction_step_id => p_transaction_step_id
467 ,p_person_id => p_login_person_id
468 ,p_name => 'P_ISSUED_DATE'
469 ,p_value => p_issued_date);
470
471 hr_transaction_api.set_varchar2_value
472 (p_transaction_step_id => p_transaction_step_id
473 ,p_person_id => p_login_person_id
474 ,p_name => 'P_ISSUING_AUTHORITY'
475 ,p_value => p_issuing_authority);
476
477 hr_transaction_api.set_number_value
478 (p_transaction_step_id => p_transaction_step_id
479 ,p_person_id => p_login_person_id
480 ,p_name => 'P_VERIFIED_BY'
481 ,p_value => p_verified_by);
482
483 hr_transaction_api.set_date_value
484 (p_transaction_step_id => p_transaction_step_id
485 ,p_person_id => p_login_person_id
486 ,p_name => 'P_VERIFIED_DATE'
487 ,p_value => p_verified_date);
488
489 hr_transaction_api.set_varchar2_value
490 (p_transaction_step_id => p_transaction_step_id
491 ,p_person_id => p_login_person_id
492 ,p_name => 'P_RELATED_OBJECT_NAME'
493 ,p_value => p_related_object_name);
494
495 hr_transaction_api.set_varchar2_value
496 (p_transaction_step_id => p_transaction_step_id
497 ,p_person_id => p_login_person_id
498 ,p_name => 'P_RELATED_OBJECT_ID_COL'
499 ,p_value => p_related_object_id_col);
500
501 hr_transaction_api.set_number_value
502 (p_transaction_step_id => p_transaction_step_id
503 ,p_person_id => p_login_person_id
504 ,p_name => 'P_RELATED_OBJECT_ID'
505 ,p_value => p_related_object_id);
506
507 hr_transaction_api.set_varchar2_value
508 (p_transaction_step_id => p_transaction_step_id
509 ,p_person_id => p_login_person_id
510 ,p_name => 'P_DEI_ATTRIBUTE_CATEGORY'
511 ,p_value => p_dei_attribute_category);
512
513 hr_transaction_api.set_varchar2_value
514 (p_transaction_step_id => p_transaction_step_id
515 ,p_person_id => p_login_person_id
516 ,p_name => 'P_DEI_ATTRIBUTE1'
517 ,p_value => p_dei_attribute1);
518
519 hr_transaction_api.set_varchar2_value
520 (p_transaction_step_id => p_transaction_step_id
521 ,p_person_id => p_login_person_id
522 ,p_name => 'P_DEI_ATTRIBUTE2'
523 ,p_value => p_dei_attribute2);
524
525 hr_transaction_api.set_varchar2_value
526 (p_transaction_step_id => p_transaction_step_id
527 ,p_person_id => p_login_person_id
528 ,p_name => 'P_DEI_ATTRIBUTE3'
529 ,p_value => p_dei_attribute3);
530
531 hr_transaction_api.set_varchar2_value
532 (p_transaction_step_id => p_transaction_step_id
533 ,p_person_id => p_login_person_id
534 ,p_name => 'P_DEI_ATTRIBUTE4'
535 ,p_value => p_dei_attribute4);
536
537 hr_transaction_api.set_varchar2_value
538 (p_transaction_step_id => p_transaction_step_id
539 ,p_person_id => p_login_person_id
540 ,p_name => 'P_DEI_ATTRIBUTE5'
541 ,p_value => p_dei_attribute5);
542
543 hr_transaction_api.set_varchar2_value
544 (p_transaction_step_id => p_transaction_step_id
545 ,p_person_id => p_login_person_id
546 ,p_name => 'P_DEI_ATTRIBUTE6'
547 ,p_value => p_dei_attribute6);
548
549 hr_transaction_api.set_varchar2_value
550 (p_transaction_step_id => p_transaction_step_id
551 ,p_person_id => p_login_person_id
552 ,p_name => 'P_DEI_ATTRIBUTE7'
553 ,p_value => p_dei_attribute7);
554
555 hr_transaction_api.set_varchar2_value
556 (p_transaction_step_id => p_transaction_step_id
557 ,p_person_id => p_login_person_id
558 ,p_name => 'P_DEI_ATTRIBUTE8'
559 ,p_value => p_dei_attribute8);
560
561 hr_transaction_api.set_varchar2_value
562 (p_transaction_step_id => p_transaction_step_id
563 ,p_person_id => p_login_person_id
564 ,p_name => 'P_DEI_ATTRIBUTE9'
565 ,p_value => p_dei_attribute9);
566
567 hr_transaction_api.set_varchar2_value
568 (p_transaction_step_id => p_transaction_step_id
569 ,p_person_id => p_login_person_id
570 ,p_name => 'P_DEI_ATTRIBUTE10'
571 ,p_value => p_dei_attribute10);
572
573 hr_transaction_api.set_varchar2_value
574 (p_transaction_step_id => p_transaction_step_id
575 ,p_person_id => p_login_person_id
576 ,p_name => 'P_DEI_ATTRIBUTE11'
577 ,p_value => p_dei_attribute11);
578
579 hr_transaction_api.set_varchar2_value
580 (p_transaction_step_id => p_transaction_step_id
581 ,p_person_id => p_login_person_id
582 ,p_name => 'P_DEI_ATTRIBUTE12'
583 ,p_value => p_dei_attribute12);
584
585 hr_transaction_api.set_varchar2_value
586 (p_transaction_step_id => p_transaction_step_id
587 ,p_person_id => p_login_person_id
588 ,p_name => 'P_DEI_ATTRIBUTE13'
589 ,p_value => p_dei_attribute13);
590
591 hr_transaction_api.set_varchar2_value
592 (p_transaction_step_id => p_transaction_step_id
593 ,p_person_id => p_login_person_id
594 ,p_name => 'P_DEI_ATTRIBUTE14'
595 ,p_value => p_dei_attribute14);
596
597 hr_transaction_api.set_varchar2_value
598 (p_transaction_step_id => p_transaction_step_id
599 ,p_person_id => p_login_person_id
600 ,p_name => 'P_DEI_ATTRIBUTE15'
601 ,p_value => p_dei_attribute15);
602
603 hr_transaction_api.set_varchar2_value
604 (p_transaction_step_id => p_transaction_step_id
605 ,p_person_id => p_login_person_id
606 ,p_name => 'P_DEI_ATTRIBUTE16'
607 ,p_value => p_dei_attribute16);
608
609 hr_transaction_api.set_varchar2_value
610 (p_transaction_step_id => p_transaction_step_id
611 ,p_person_id => p_login_person_id
612 ,p_name => 'P_DEI_ATTRIBUTE17'
613 ,p_value => p_dei_attribute17);
614
615 hr_transaction_api.set_varchar2_value
616 (p_transaction_step_id => p_transaction_step_id
617 ,p_person_id => p_login_person_id
618 ,p_name => 'P_DEI_ATTRIBUTE18'
619 ,p_value => p_dei_attribute18);
620
621 hr_transaction_api.set_varchar2_value
622 (p_transaction_step_id => p_transaction_step_id
623 ,p_person_id => p_login_person_id
624 ,p_name => 'P_DEI_ATTRIBUTE19'
625 ,p_value => p_dei_attribute19);
626
627 hr_transaction_api.set_varchar2_value
628 (p_transaction_step_id => p_transaction_step_id
629 ,p_person_id => p_login_person_id
630 ,p_name => 'P_DEI_ATTRIBUTE20'
631 ,p_value => p_dei_attribute20);
632
633 hr_transaction_api.set_varchar2_value
634 (p_transaction_step_id => p_transaction_step_id
635 ,p_person_id => p_login_person_id
636 ,p_name => 'P_DEI_ATTRIBUTE21'
637 ,p_value => p_dei_attribute21);
638
639 hr_transaction_api.set_varchar2_value
640 (p_transaction_step_id => p_transaction_step_id
641 ,p_person_id => p_login_person_id
642 ,p_name => 'P_DEI_ATTRIBUTE22'
643 ,p_value => p_dei_attribute22);
644
645 hr_transaction_api.set_varchar2_value
646 (p_transaction_step_id => p_transaction_step_id
647 ,p_person_id => p_login_person_id
648 ,p_name => 'P_DEI_ATTRIBUTE23'
649 ,p_value => p_dei_attribute23);
650
651 hr_transaction_api.set_varchar2_value
652 (p_transaction_step_id => p_transaction_step_id
653 ,p_person_id => p_login_person_id
654 ,p_name => 'P_DEI_ATTRIBUTE24'
655 ,p_value => p_dei_attribute24);
656
657 hr_transaction_api.set_varchar2_value
658 (p_transaction_step_id => p_transaction_step_id
659 ,p_person_id => p_login_person_id
660 ,p_name => 'P_DEI_ATTRIBUTE25'
661 ,p_value => p_dei_attribute25);
662
663 hr_transaction_api.set_varchar2_value
664 (p_transaction_step_id => p_transaction_step_id
665 ,p_person_id => p_login_person_id
666 ,p_name => 'P_DEI_ATTRIBUTE26'
667 ,p_value => p_dei_attribute26);
668
669 hr_transaction_api.set_varchar2_value
670 (p_transaction_step_id => p_transaction_step_id
671 ,p_person_id => p_login_person_id
672 ,p_name => 'P_DEI_ATTRIBUTE27'
673 ,p_value => p_dei_attribute27);
674
675 hr_transaction_api.set_varchar2_value
676 (p_transaction_step_id => p_transaction_step_id
677 ,p_person_id => p_login_person_id
678 ,p_name => 'P_DEI_ATTRIBUTE28'
679 ,p_value => p_dei_attribute28);
680
681 hr_transaction_api.set_varchar2_value
682 (p_transaction_step_id => p_transaction_step_id
683 ,p_person_id => p_login_person_id
684 ,p_name => 'P_DEI_ATTRIBUTE29'
685 ,p_value => p_dei_attribute29);
686
687 hr_transaction_api.set_varchar2_value
688 (p_transaction_step_id => p_transaction_step_id
689 ,p_person_id => p_login_person_id
690 ,p_name => 'P_DEI_ATTRIBUTE30'
691 ,p_value => p_dei_attribute30);
692
693 hr_transaction_api.set_varchar2_value
694 (p_transaction_step_id => p_transaction_step_id
695 ,p_person_id => p_login_person_id
696 ,p_name => 'P_DEI_INFORMATION_CATEGORY'
697 ,p_value => p_dei_information_category);
698
699 hr_transaction_api.set_varchar2_value
700 (p_transaction_step_id => p_transaction_step_id
701 ,p_person_id => p_login_person_id
702 ,p_name => 'P_DEI_INFORMATION1'
703 ,p_value => p_dei_information1);
704
705 hr_transaction_api.set_varchar2_value
706 (p_transaction_step_id => p_transaction_step_id
707 ,p_person_id => p_login_person_id
708 ,p_name => 'P_DEI_INFORMATION2'
709 ,p_value => p_dei_information2);
710
711 hr_transaction_api.set_varchar2_value
712 (p_transaction_step_id => p_transaction_step_id
713 ,p_person_id => p_login_person_id
714 ,p_name => 'P_DEI_INFORMATION3'
715 ,p_value => p_dei_information3);
716
717 hr_transaction_api.set_varchar2_value
718 (p_transaction_step_id => p_transaction_step_id
719 ,p_person_id => p_login_person_id
720 ,p_name => 'P_DEI_INFORMATION4'
721 ,p_value => p_dei_information4);
722
723 hr_transaction_api.set_varchar2_value
724 (p_transaction_step_id => p_transaction_step_id
725 ,p_person_id => p_login_person_id
726 ,p_name => 'P_DEI_INFORMATION5'
727 ,p_value => p_dei_information5);
728
729 hr_transaction_api.set_varchar2_value
730 (p_transaction_step_id => p_transaction_step_id
731 ,p_person_id => p_login_person_id
732 ,p_name => 'P_DEI_INFORMATION6'
733 ,p_value => p_dei_information6);
734
735 hr_transaction_api.set_varchar2_value
736 (p_transaction_step_id => p_transaction_step_id
737 ,p_person_id => p_login_person_id
738 ,p_name => 'P_DEI_INFORMATION7'
739 ,p_value => p_dei_information7);
740
741 hr_transaction_api.set_varchar2_value
742 (p_transaction_step_id => p_transaction_step_id
743 ,p_person_id => p_login_person_id
744 ,p_name => 'P_DEI_INFORMATION8'
745 ,p_value => p_dei_information8);
746
747 hr_transaction_api.set_varchar2_value
748 (p_transaction_step_id => p_transaction_step_id
749 ,p_person_id => p_login_person_id
750 ,p_name => 'P_DEI_INFORMATION9'
751 ,p_value => p_dei_information9);
752
753 hr_transaction_api.set_varchar2_value
754 (p_transaction_step_id => p_transaction_step_id
755 ,p_person_id => p_login_person_id
756 ,p_name => 'P_DEI_INFORMATION10'
757 ,p_value => p_dei_information10);
758
759 hr_transaction_api.set_varchar2_value
760 (p_transaction_step_id => p_transaction_step_id
761 ,p_person_id => p_login_person_id
762 ,p_name => 'P_DEI_INFORMATION11'
763 ,p_value => p_dei_information11);
764
765 hr_transaction_api.set_varchar2_value
766 (p_transaction_step_id => p_transaction_step_id
767 ,p_person_id => p_login_person_id
768 ,p_name => 'P_DEI_INFORMATION12'
769 ,p_value => p_dei_information12);
770
771 hr_transaction_api.set_varchar2_value
772 (p_transaction_step_id => p_transaction_step_id
773 ,p_person_id => p_login_person_id
774 ,p_name => 'P_DEI_INFORMATION13'
775 ,p_value => p_dei_information13);
776
777 hr_transaction_api.set_varchar2_value
778 (p_transaction_step_id => p_transaction_step_id
779 ,p_person_id => p_login_person_id
780 ,p_name => 'P_DEI_INFORMATION14'
781 ,p_value => p_dei_information14);
782
783 hr_transaction_api.set_varchar2_value
784 (p_transaction_step_id => p_transaction_step_id
785 ,p_person_id => p_login_person_id
786 ,p_name => 'P_DEI_INFORMATION15'
787 ,p_value => p_dei_information15);
788
789 hr_transaction_api.set_varchar2_value
790 (p_transaction_step_id => p_transaction_step_id
791 ,p_person_id => p_login_person_id
792 ,p_name => 'P_DEI_INFORMATION16'
793 ,p_value => p_dei_information16);
794
795 hr_transaction_api.set_varchar2_value
796 (p_transaction_step_id => p_transaction_step_id
797 ,p_person_id => p_login_person_id
798 ,p_name => 'P_DEI_INFORMATION17'
799 ,p_value => p_dei_information17);
800
801 hr_transaction_api.set_varchar2_value
802 (p_transaction_step_id => p_transaction_step_id
803 ,p_person_id => p_login_person_id
804 ,p_name => 'P_DEI_INFORMATION18'
805 ,p_value => p_dei_information18);
806
807 hr_transaction_api.set_varchar2_value
808 (p_transaction_step_id => p_transaction_step_id
809 ,p_person_id => p_login_person_id
810 ,p_name => 'P_DEI_INFORMATION19'
811 ,p_value => p_dei_information19);
812
813 hr_transaction_api.set_varchar2_value
814 (p_transaction_step_id => p_transaction_step_id
815 ,p_person_id => p_login_person_id
816 ,p_name => 'P_DEI_INFORMATION20'
817 ,p_value => p_dei_information20);
818
819 hr_transaction_api.set_varchar2_value
820 (p_transaction_step_id => p_transaction_step_id
821 ,p_person_id => p_login_person_id
822 ,p_name => 'P_DEI_INFORMATION21'
823 ,p_value => p_dei_information21);
824
825 hr_transaction_api.set_varchar2_value
826 (p_transaction_step_id => p_transaction_step_id
827 ,p_person_id => p_login_person_id
828 ,p_name => 'P_DEI_INFORMATION22'
829 ,p_value => p_dei_information22);
830
831 hr_transaction_api.set_varchar2_value
832 (p_transaction_step_id => p_transaction_step_id
833 ,p_person_id => p_login_person_id
834 ,p_name => 'P_DEI_INFORMATION23'
835 ,p_value => p_dei_information23);
836
837 hr_transaction_api.set_varchar2_value
838 (p_transaction_step_id => p_transaction_step_id
839 ,p_person_id => p_login_person_id
840 ,p_name => 'P_DEI_INFORMATION24'
841 ,p_value => p_dei_information24);
842
843 hr_transaction_api.set_varchar2_value
844 (p_transaction_step_id => p_transaction_step_id
845 ,p_person_id => p_login_person_id
846 ,p_name => 'P_DEI_INFORMATION25'
847 ,p_value => p_dei_information25);
848
849 hr_transaction_api.set_varchar2_value
850 (p_transaction_step_id => p_transaction_step_id
851 ,p_person_id => p_login_person_id
852 ,p_name => 'P_DEI_INFORMATION26'
853 ,p_value => p_dei_information26);
854
855 hr_transaction_api.set_varchar2_value
856 (p_transaction_step_id => p_transaction_step_id
857 ,p_person_id => p_login_person_id
858 ,p_name => 'P_DEI_INFORMATION27'
859 ,p_value => p_dei_information27);
860
861 hr_transaction_api.set_varchar2_value
862 (p_transaction_step_id => p_transaction_step_id
863 ,p_person_id => p_login_person_id
864 ,p_name => 'P_DEI_INFORMATION28'
865 ,p_value => p_dei_information28);
866
867 hr_transaction_api.set_varchar2_value
868 (p_transaction_step_id => p_transaction_step_id
869 ,p_person_id => p_login_person_id
870 ,p_name => 'P_DEI_INFORMATION29'
871 ,p_value => p_dei_information29);
872
873 hr_transaction_api.set_varchar2_value
874 (p_transaction_step_id => p_transaction_step_id
875 ,p_person_id => p_login_person_id
876 ,p_name => 'P_DEI_INFORMATION30'
877 ,p_value => p_dei_information30);
878
879
880 hr_transaction_api.set_number_value
881 (p_transaction_step_id => p_transaction_step_id
882 ,p_person_id => p_login_person_id
883 ,p_name => 'P_REQUEST_ID'
884 ,p_value => p_request_id);
885
886 hr_transaction_api.set_number_value
887 (p_transaction_step_id => p_transaction_step_id
888 ,p_person_id => p_login_person_id
889 ,p_name => 'P_PROGRAM_APPLICATION_ID'
890 ,p_value => p_program_application_id);
891
892 hr_transaction_api.set_number_value
893 (p_transaction_step_id => p_transaction_step_id
894 ,p_person_id => p_login_person_id
895 ,p_name => 'P_PROGRAM_ID'
896 ,p_value => p_program_id);
897
898 hr_transaction_api.set_date_value
899 (p_transaction_step_id => p_transaction_step_id
900 ,p_person_id => p_login_person_id
901 ,p_name => 'P_PROGRAM_UPDATE_DATE'
902 ,p_value => p_program_update_date);
903
904 hr_transaction_api.set_number_value
905 (p_transaction_step_id => p_transaction_step_id
906 ,p_person_id => p_login_person_id
907 ,p_name => 'P_OBJECT_VERSION_NUMBER'
908 ,p_value => p_object_version_number);
909
910
911 elsif p_action_mode = 'DOR_UPDATE' then
912
913 SELECT *
914 INTO p_org_rec
915 FROM HR_DOCUMENT_EXTRA_INFO
916 WHERE document_extra_info_id = p_document_extra_info_id
917 AND person_id = p_person_id
918 AND document_type_id = p_document_type_id;
919
920
921 if p_org_rec.date_from = p_date_from then
922 hr_transaction_api.set_date_value
923 (p_transaction_step_id => p_transaction_step_id
924 ,p_person_id => p_login_person_id
925 ,p_name => 'P_DATE_FROM'
926 ,p_value => p_date_from);
927 else
928 hr_transaction_api.set_date_value
929 (p_transaction_step_id => p_transaction_step_id
930 ,p_person_id => p_login_person_id
931 ,p_name => 'P_DATE_FROM'
932 ,p_value => p_date_from
933 ,p_original_value => p_org_rec.date_from);
934 end if;
935
936 if p_org_rec.date_to = p_date_to then
937 hr_transaction_api.set_date_value
938 (p_transaction_step_id => p_transaction_step_id
939 ,p_person_id => p_login_person_id
940 ,p_name => 'P_DATE_TO'
941 ,p_value => p_date_to);
942 else
943 hr_transaction_api.set_date_value
944 (p_transaction_step_id => p_transaction_step_id
945 ,p_person_id => p_login_person_id
946 ,p_name => 'P_DATE_TO'
947 ,p_value => p_date_to
948 ,p_original_value => p_org_rec.date_to);
949 end if;
950
951 if p_org_rec.document_number = p_document_number then
952 hr_transaction_api.set_varchar2_value
953 (p_transaction_step_id => p_transaction_step_id
954 ,p_person_id => p_login_person_id
955 ,p_name => 'P_DOCUMENT_NUMBER'
956 ,p_value => p_document_number);
957 else
958 hr_transaction_api.set_varchar2_value
959 (p_transaction_step_id => p_transaction_step_id
960 ,p_person_id => p_login_person_id
961 ,p_name => 'P_DOCUMENT_NUMBER'
962 ,p_value => p_document_number
963 ,p_original_value => p_org_rec.document_number);
964 end if;
965
966 if p_org_rec.issued_by = p_issued_by then
967 hr_transaction_api.set_varchar2_value
968 (p_transaction_step_id => p_transaction_step_id
969 ,p_person_id => p_login_person_id
970 ,p_name => 'P_ISSUED_BY'
971 ,p_value => p_issued_by);
972 else
973 hr_transaction_api.set_varchar2_value
974 (p_transaction_step_id => p_transaction_step_id
975 ,p_person_id => p_login_person_id
976 ,p_name => 'P_ISSUED_BY'
977 ,p_value => p_issued_by
978 ,p_original_value => p_org_rec.issued_by);
979 end if;
980
981 if p_org_rec.issued_at = p_issued_at then
982 hr_transaction_api.set_varchar2_value
983 (p_transaction_step_id => p_transaction_step_id
984 ,p_person_id => p_login_person_id
985 ,p_name => 'P_ISSUED_AT'
986 ,p_value => p_issued_at);
987 else
988 hr_transaction_api.set_varchar2_value
989 (p_transaction_step_id => p_transaction_step_id
990 ,p_person_id => p_login_person_id
991 ,p_name => 'P_ISSUED_AT'
992 ,p_value => p_issued_at
993 ,p_original_value => p_org_rec.issued_at);
994 end if;
995
996 if p_org_rec.issued_date = p_issued_date then
997 hr_transaction_api.set_date_value
998 (p_transaction_step_id => p_transaction_step_id
999 ,p_person_id => p_login_person_id
1000 ,p_name => 'P_ISSUED_DATE'
1001 ,p_value => p_issued_date);
1002 else
1003 hr_transaction_api.set_date_value
1004 (p_transaction_step_id => p_transaction_step_id
1005 ,p_person_id => p_login_person_id
1006 ,p_name => 'P_ISSUED_DATE'
1007 ,p_value => p_issued_date
1008 ,p_original_value => p_org_rec.issued_date);
1009 end if;
1010
1011 if p_org_rec.issuing_authority = p_issuing_authority then
1012 hr_transaction_api.set_varchar2_value
1013 (p_transaction_step_id => p_transaction_step_id
1014 ,p_person_id => p_login_person_id
1015 ,p_name => 'P_ISSUING_AUTHORITY'
1016 ,p_value => p_issuing_authority);
1017 else
1018 hr_transaction_api.set_varchar2_value
1019 (p_transaction_step_id => p_transaction_step_id
1020 ,p_person_id => p_login_person_id
1021 ,p_name => 'P_ISSUING_AUTHORITY'
1022 ,p_value => p_issuing_authority
1023 ,p_original_value => p_org_rec.issuing_authority);
1024 end if;
1025
1026 if p_org_rec.verified_by = p_verified_by then
1027 hr_transaction_api.set_number_value
1028 (p_transaction_step_id => p_transaction_step_id
1029 ,p_person_id => p_login_person_id
1030 ,p_name => 'P_VERIFIED_BY'
1031 ,p_value => p_verified_by);
1032 else
1033 hr_transaction_api.set_number_value
1034 (p_transaction_step_id => p_transaction_step_id
1035 ,p_person_id => p_login_person_id
1036 ,p_name => 'P_VERIFIED_BY'
1037 ,p_value => p_verified_by
1038 ,p_original_value => p_org_rec.verified_by);
1039 end if;
1040
1041 if p_org_rec.verified_date = p_verified_date then
1042 hr_transaction_api.set_date_value
1043 (p_transaction_step_id => p_transaction_step_id
1044 ,p_person_id => p_login_person_id
1045 ,p_name => 'P_VERIFIED_DATE'
1046 ,p_value => p_verified_date);
1047 else
1048 hr_transaction_api.set_date_value
1049 (p_transaction_step_id => p_transaction_step_id
1050 ,p_person_id => p_login_person_id
1051 ,p_name => 'P_VERIFIED_DATE'
1052 ,p_value => p_verified_date
1053 ,p_original_value => p_org_rec.verified_date);
1054 end if;
1055
1056 if p_org_rec.related_object_name = p_related_object_name then
1057 hr_transaction_api.set_varchar2_value
1058 (p_transaction_step_id => p_transaction_step_id
1059 ,p_person_id => p_login_person_id
1060 ,p_name => 'P_RELATED_OBJECT_NAME'
1061 ,p_value => p_related_object_name);
1062 else
1063 hr_transaction_api.set_varchar2_value
1064 (p_transaction_step_id => p_transaction_step_id
1065 ,p_person_id => p_login_person_id
1066 ,p_name => 'P_RELATED_OBJECT_NAME'
1067 ,p_value => p_related_object_name
1068 ,p_original_value => p_org_rec.related_object_name);
1069 end if;
1070
1071 if p_org_rec.related_object_id_col = p_related_object_id_col then
1072 hr_transaction_api.set_varchar2_value
1073 (p_transaction_step_id => p_transaction_step_id
1074 ,p_person_id => p_login_person_id
1075 ,p_name => 'P_RELATED_OBJECT_ID_COL'
1076 ,p_value => p_related_object_id_col);
1077 else
1078 hr_transaction_api.set_varchar2_value
1079 (p_transaction_step_id => p_transaction_step_id
1080 ,p_person_id => p_login_person_id
1081 ,p_name => 'P_RELATED_OBJECT_ID_COL'
1082 ,p_value => p_related_object_id_col
1083 ,p_original_value => p_org_rec.related_object_id_col);
1084 end if;
1085
1086 if p_org_rec.related_object_id = p_related_object_id then
1087 hr_transaction_api.set_number_value
1088 (p_transaction_step_id => p_transaction_step_id
1089 ,p_person_id => p_login_person_id
1090 ,p_name => 'P_RELATED_OBJECT_ID'
1091 ,p_value => p_related_object_id);
1092 else
1093 hr_transaction_api.set_number_value
1094 (p_transaction_step_id => p_transaction_step_id
1095 ,p_person_id => p_login_person_id
1096 ,p_name => 'P_RELATED_OBJECT_ID'
1097 ,p_value => p_related_object_id
1098 ,p_original_value => p_org_rec.related_object_id);
1099 end if;
1100
1101 if p_org_rec.dei_attribute_category = p_dei_attribute_category then
1102 hr_transaction_api.set_varchar2_value
1103 (p_transaction_step_id => p_transaction_step_id
1104 ,p_person_id => p_login_person_id
1105 ,p_name => 'P_DEI_ATTRIBUTE_CATEGORY'
1106 ,p_value => p_dei_attribute_category);
1107 else
1108 hr_transaction_api.set_varchar2_value
1109 (p_transaction_step_id => p_transaction_step_id
1110 ,p_person_id => p_login_person_id
1111 ,p_name => 'P_DEI_ATTRIBUTE_CATEGORY'
1112 ,p_value => p_dei_attribute_category
1113 ,p_original_value => p_org_rec.dei_attribute_category);
1114 end if;
1115
1116 if p_org_rec.dei_attribute1 = p_dei_attribute1 then
1117 hr_transaction_api.set_varchar2_value
1118 (p_transaction_step_id => p_transaction_step_id
1119 ,p_person_id => p_login_person_id
1120 ,p_name => 'P_DEI_ATTRIBUTE1'
1121 ,p_value => p_dei_attribute1);
1122 else
1123 hr_transaction_api.set_varchar2_value
1124 (p_transaction_step_id => p_transaction_step_id
1125 ,p_person_id => p_login_person_id
1126 ,p_name => 'P_DEI_ATTRIBUTE1'
1127 ,p_value => p_dei_attribute1
1128 ,p_original_value => p_org_rec.dei_attribute1);
1129 end if;
1130
1131 if p_org_rec.dei_attribute2 = p_dei_attribute2 then
1132 hr_transaction_api.set_varchar2_value
1133 (p_transaction_step_id => p_transaction_step_id
1134 ,p_person_id => p_login_person_id
1135 ,p_name => 'P_DEI_ATTRIBUTE2'
1136 ,p_value => p_dei_attribute2);
1137 else
1138 hr_transaction_api.set_varchar2_value
1139 (p_transaction_step_id => p_transaction_step_id
1140 ,p_person_id => p_login_person_id
1141 ,p_name => 'P_DEI_ATTRIBUTE2'
1142 ,p_value => p_dei_attribute2
1143 ,p_original_value => p_org_rec.dei_attribute2);
1144 end if;
1145
1146 if p_org_rec.dei_attribute3 = p_dei_attribute3 then
1147 hr_transaction_api.set_varchar2_value
1148 (p_transaction_step_id => p_transaction_step_id
1149 ,p_person_id => p_login_person_id
1150 ,p_name => 'P_DEI_ATTRIBUTE3'
1151 ,p_value => p_dei_attribute3);
1152 else
1153 hr_transaction_api.set_varchar2_value
1154 (p_transaction_step_id => p_transaction_step_id
1155 ,p_person_id => p_login_person_id
1156 ,p_name => 'P_DEI_ATTRIBUTE3'
1157 ,p_value => p_dei_attribute3
1158 ,p_original_value => p_org_rec.dei_attribute3);
1159 end if;
1160
1161 if p_org_rec.dei_attribute4 = p_dei_attribute4 then
1162 hr_transaction_api.set_varchar2_value
1163 (p_transaction_step_id => p_transaction_step_id
1164 ,p_person_id => p_login_person_id
1165 ,p_name => 'P_DEI_ATTRIBUTE4'
1166 ,p_value => p_dei_attribute4);
1167 else
1168 hr_transaction_api.set_varchar2_value
1169 (p_transaction_step_id => p_transaction_step_id
1170 ,p_person_id => p_login_person_id
1171 ,p_name => 'P_DEI_ATTRIBUTE4'
1172 ,p_value => p_dei_attribute4
1173 ,p_original_value => p_org_rec.dei_attribute4);
1174 end if;
1175
1176 if p_org_rec.dei_attribute5 = p_dei_attribute5 then
1177 hr_transaction_api.set_varchar2_value
1178 (p_transaction_step_id => p_transaction_step_id
1179 ,p_person_id => p_login_person_id
1180 ,p_name => 'P_DEI_ATTRIBUTE5'
1181 ,p_value => p_dei_attribute5);
1182 else
1183 hr_transaction_api.set_varchar2_value
1184 (p_transaction_step_id => p_transaction_step_id
1185 ,p_person_id => p_login_person_id
1186 ,p_name => 'P_DEI_ATTRIBUTE5'
1187 ,p_value => p_dei_attribute5
1188 ,p_original_value => p_org_rec.dei_attribute5);
1189 end if;
1190
1191 if p_org_rec.dei_attribute6 = p_dei_attribute6 then
1192 hr_transaction_api.set_varchar2_value
1193 (p_transaction_step_id => p_transaction_step_id
1194 ,p_person_id => p_login_person_id
1195 ,p_name => 'P_DEI_ATTRIBUTE6'
1196 ,p_value => p_dei_attribute6);
1197 else
1198 hr_transaction_api.set_varchar2_value
1199 (p_transaction_step_id => p_transaction_step_id
1200 ,p_person_id => p_login_person_id
1201 ,p_name => 'P_DEI_ATTRIBUTE6'
1202 ,p_value => p_dei_attribute6
1203 ,p_original_value => p_org_rec.dei_attribute6);
1204 end if;
1205
1206 if p_org_rec.dei_attribute7 = p_dei_attribute7 then
1207 hr_transaction_api.set_varchar2_value
1208 (p_transaction_step_id => p_transaction_step_id
1209 ,p_person_id => p_login_person_id
1210 ,p_name => 'P_DEI_ATTRIBUTE7'
1211 ,p_value => p_dei_attribute7);
1212 else
1213 hr_transaction_api.set_varchar2_value
1214 (p_transaction_step_id => p_transaction_step_id
1215 ,p_person_id => p_login_person_id
1216 ,p_name => 'P_DEI_ATTRIBUTE7'
1217 ,p_value => p_dei_attribute7
1218 ,p_original_value => p_org_rec.dei_attribute7);
1219 end if;
1220
1221 if p_org_rec.dei_attribute8 = p_dei_attribute8 then
1222 hr_transaction_api.set_varchar2_value
1223 (p_transaction_step_id => p_transaction_step_id
1224 ,p_person_id => p_login_person_id
1225 ,p_name => 'P_DEI_ATTRIBUTE8'
1226 ,p_value => p_dei_attribute8);
1227 else
1228 hr_transaction_api.set_varchar2_value
1229 (p_transaction_step_id => p_transaction_step_id
1230 ,p_person_id => p_login_person_id
1231 ,p_name => 'P_DEI_ATTRIBUTE8'
1232 ,p_value => p_dei_attribute8
1233 ,p_original_value => p_org_rec.dei_attribute8);
1234 end if;
1235
1236 if p_org_rec.dei_attribute9 = p_dei_attribute9 then
1237 hr_transaction_api.set_varchar2_value
1238 (p_transaction_step_id => p_transaction_step_id
1239 ,p_person_id => p_login_person_id
1240 ,p_name => 'P_DEI_ATTRIBUTE9'
1241 ,p_value => p_dei_attribute9);
1242 else
1243 hr_transaction_api.set_varchar2_value
1244 (p_transaction_step_id => p_transaction_step_id
1245 ,p_person_id => p_login_person_id
1246 ,p_name => 'P_DEI_ATTRIBUTE9'
1247 ,p_value => p_dei_attribute9
1248 ,p_original_value => p_org_rec.dei_attribute9);
1249 end if;
1250
1251 if p_org_rec.dei_attribute10 = p_dei_attribute10 then
1252 hr_transaction_api.set_varchar2_value
1253 (p_transaction_step_id => p_transaction_step_id
1254 ,p_person_id => p_login_person_id
1255 ,p_name => 'P_DEI_ATTRIBUTE10'
1256 ,p_value => p_dei_attribute10);
1257 else
1258 hr_transaction_api.set_varchar2_value
1259 (p_transaction_step_id => p_transaction_step_id
1260 ,p_person_id => p_login_person_id
1261 ,p_name => 'P_DEI_ATTRIBUTE10'
1262 ,p_value => p_dei_attribute10
1263 ,p_original_value => p_org_rec.dei_attribute10);
1264 end if;
1265
1266 if p_org_rec.dei_attribute11 = p_dei_attribute11 then
1267 hr_transaction_api.set_varchar2_value
1268 (p_transaction_step_id => p_transaction_step_id
1269 ,p_person_id => p_login_person_id
1270 ,p_name => 'P_DEI_ATTRIBUTE11'
1271 ,p_value => p_dei_attribute11);
1272 else
1273 hr_transaction_api.set_varchar2_value
1274 (p_transaction_step_id => p_transaction_step_id
1275 ,p_person_id => p_login_person_id
1276 ,p_name => 'P_DEI_ATTRIBUTE11'
1277 ,p_value => p_dei_attribute11
1278 ,p_original_value => p_org_rec.dei_attribute11);
1279 end if;
1280
1281 if p_org_rec.dei_attribute12 = p_dei_attribute12 then
1282 hr_transaction_api.set_varchar2_value
1283 (p_transaction_step_id => p_transaction_step_id
1284 ,p_person_id => p_login_person_id
1285 ,p_name => 'P_DEI_ATTRIBUTE12'
1286 ,p_value => p_dei_attribute12);
1287 else
1288 hr_transaction_api.set_varchar2_value
1289 (p_transaction_step_id => p_transaction_step_id
1290 ,p_person_id => p_login_person_id
1291 ,p_name => 'P_DEI_ATTRIBUTE12'
1292 ,p_value => p_dei_attribute12
1293 ,p_original_value => p_org_rec.dei_attribute12);
1294 end if;
1295
1296 if p_org_rec.dei_attribute13 = p_dei_attribute13 then
1297 hr_transaction_api.set_varchar2_value
1298 (p_transaction_step_id => p_transaction_step_id
1299 ,p_person_id => p_login_person_id
1300 ,p_name => 'P_DEI_ATTRIBUTE13'
1301 ,p_value => p_dei_attribute13);
1302 else
1303 hr_transaction_api.set_varchar2_value
1304 (p_transaction_step_id => p_transaction_step_id
1305 ,p_person_id => p_login_person_id
1306 ,p_name => 'P_DEI_ATTRIBUTE13'
1307 ,p_value => p_dei_attribute13
1308 ,p_original_value => p_org_rec.dei_attribute13);
1309 end if;
1310
1311 if p_org_rec.dei_attribute14 = p_dei_attribute14 then
1312 hr_transaction_api.set_varchar2_value
1313 (p_transaction_step_id => p_transaction_step_id
1314 ,p_person_id => p_login_person_id
1315 ,p_name => 'P_DEI_ATTRIBUTE14'
1316 ,p_value => p_dei_attribute14);
1317 else
1318 hr_transaction_api.set_varchar2_value
1319 (p_transaction_step_id => p_transaction_step_id
1320 ,p_person_id => p_login_person_id
1321 ,p_name => 'P_DEI_ATTRIBUTE14'
1322 ,p_value => p_dei_attribute14
1323 ,p_original_value => p_org_rec.dei_attribute14);
1324 end if;
1325
1326 if p_org_rec.dei_attribute15 = p_dei_attribute15 then
1327 hr_transaction_api.set_varchar2_value
1328 (p_transaction_step_id => p_transaction_step_id
1329 ,p_person_id => p_login_person_id
1330 ,p_name => 'P_DEI_ATTRIBUTE15'
1331 ,p_value => p_dei_attribute15);
1332 else
1333 hr_transaction_api.set_varchar2_value
1334 (p_transaction_step_id => p_transaction_step_id
1335 ,p_person_id => p_login_person_id
1336 ,p_name => 'P_DEI_ATTRIBUTE15'
1337 ,p_value => p_dei_attribute15
1338 ,p_original_value => p_org_rec.dei_attribute15);
1339 end if;
1340
1341 if p_org_rec.dei_attribute16 = p_dei_attribute16 then
1342 hr_transaction_api.set_varchar2_value
1343 (p_transaction_step_id => p_transaction_step_id
1344 ,p_person_id => p_login_person_id
1345 ,p_name => 'P_DEI_ATTRIBUTE16'
1346 ,p_value => p_dei_attribute16);
1347 else
1348 hr_transaction_api.set_varchar2_value
1349 (p_transaction_step_id => p_transaction_step_id
1350 ,p_person_id => p_login_person_id
1351 ,p_name => 'P_DEI_ATTRIBUTE16'
1352 ,p_value => p_dei_attribute16
1353 ,p_original_value => p_org_rec.dei_attribute16);
1354 end if;
1355
1356 if p_org_rec.dei_attribute17 = p_dei_attribute17 then
1357 hr_transaction_api.set_varchar2_value
1358 (p_transaction_step_id => p_transaction_step_id
1359 ,p_person_id => p_login_person_id
1360 ,p_name => 'P_DEI_ATTRIBUTE17'
1361 ,p_value => p_dei_attribute17);
1362 else
1363 hr_transaction_api.set_varchar2_value
1364 (p_transaction_step_id => p_transaction_step_id
1365 ,p_person_id => p_login_person_id
1366 ,p_name => 'P_DEI_ATTRIBUTE17'
1367 ,p_value => p_dei_attribute17
1368 ,p_original_value => p_org_rec.dei_attribute17);
1369 end if;
1370
1371 if p_org_rec.dei_attribute18 = p_dei_attribute18 then
1372 hr_transaction_api.set_varchar2_value
1373 (p_transaction_step_id => p_transaction_step_id
1374 ,p_person_id => p_login_person_id
1375 ,p_name => 'P_DEI_ATTRIBUTE18'
1376 ,p_value => p_dei_attribute18);
1377 else
1378 hr_transaction_api.set_varchar2_value
1379 (p_transaction_step_id => p_transaction_step_id
1380 ,p_person_id => p_login_person_id
1381 ,p_name => 'P_DEI_ATTRIBUTE18'
1382 ,p_value => p_dei_attribute18
1383 ,p_original_value => p_org_rec.dei_attribute18);
1384 end if;
1385
1386 if p_org_rec.dei_attribute19 = p_dei_attribute19 then
1387 hr_transaction_api.set_varchar2_value
1388 (p_transaction_step_id => p_transaction_step_id
1389 ,p_person_id => p_login_person_id
1390 ,p_name => 'P_DEI_ATTRIBUTE19'
1391 ,p_value => p_dei_attribute19);
1392 else
1393 hr_transaction_api.set_varchar2_value
1394 (p_transaction_step_id => p_transaction_step_id
1395 ,p_person_id => p_login_person_id
1396 ,p_name => 'P_DEI_ATTRIBUTE19'
1397 ,p_value => p_dei_attribute19
1398 ,p_original_value => p_org_rec.dei_attribute19);
1399 end if;
1400
1401 if p_org_rec.dei_attribute20 = p_dei_attribute20 then
1402 hr_transaction_api.set_varchar2_value
1403 (p_transaction_step_id => p_transaction_step_id
1404 ,p_person_id => p_login_person_id
1405 ,p_name => 'P_DEI_ATTRIBUTE20'
1406 ,p_value => p_dei_attribute20);
1407 else
1408 hr_transaction_api.set_varchar2_value
1409 (p_transaction_step_id => p_transaction_step_id
1410 ,p_person_id => p_login_person_id
1411 ,p_name => 'P_DEI_ATTRIBUTE20'
1412 ,p_value => p_dei_attribute20
1413 ,p_original_value => p_org_rec.dei_attribute20);
1414 end if;
1415
1416 if p_org_rec.dei_attribute21 = p_dei_attribute21 then
1417 hr_transaction_api.set_varchar2_value
1418 (p_transaction_step_id => p_transaction_step_id
1419 ,p_person_id => p_login_person_id
1420 ,p_name => 'P_DEI_ATTRIBUTE21'
1421 ,p_value => p_dei_attribute21);
1422 else
1423 hr_transaction_api.set_varchar2_value
1424 (p_transaction_step_id => p_transaction_step_id
1425 ,p_person_id => p_login_person_id
1426 ,p_name => 'P_DEI_ATTRIBUTE21'
1427 ,p_value => p_dei_attribute21
1428 ,p_original_value => p_org_rec.dei_attribute21);
1429 end if;
1430
1431 if p_org_rec.dei_attribute22 = p_dei_attribute22 then
1432 hr_transaction_api.set_varchar2_value
1433 (p_transaction_step_id => p_transaction_step_id
1434 ,p_person_id => p_login_person_id
1435 ,p_name => 'P_DEI_ATTRIBUTE22'
1436 ,p_value => p_dei_attribute22);
1437 else
1438 hr_transaction_api.set_varchar2_value
1439 (p_transaction_step_id => p_transaction_step_id
1440 ,p_person_id => p_login_person_id
1441 ,p_name => 'P_DEI_ATTRIBUTE22'
1442 ,p_value => p_dei_attribute22
1443 ,p_original_value => p_org_rec.dei_attribute22);
1444 end if;
1445
1446 if p_org_rec.dei_attribute23 = p_dei_attribute23 then
1447 hr_transaction_api.set_varchar2_value
1448 (p_transaction_step_id => p_transaction_step_id
1449 ,p_person_id => p_login_person_id
1450 ,p_name => 'P_DEI_ATTRIBUTE23'
1451 ,p_value => p_dei_attribute23);
1452 else
1453 hr_transaction_api.set_varchar2_value
1454 (p_transaction_step_id => p_transaction_step_id
1455 ,p_person_id => p_login_person_id
1456 ,p_name => 'P_DEI_ATTRIBUTE23'
1457 ,p_value => p_dei_attribute23
1458 ,p_original_value => p_org_rec.dei_attribute23);
1459 end if;
1460
1461 if p_org_rec.dei_attribute24 = p_dei_attribute24 then
1462 hr_transaction_api.set_varchar2_value
1463 (p_transaction_step_id => p_transaction_step_id
1464 ,p_person_id => p_login_person_id
1465 ,p_name => 'P_DEI_ATTRIBUTE24'
1466 ,p_value => p_dei_attribute24);
1467 else
1468 hr_transaction_api.set_varchar2_value
1469 (p_transaction_step_id => p_transaction_step_id
1470 ,p_person_id => p_login_person_id
1471 ,p_name => 'P_DEI_ATTRIBUTE24'
1472 ,p_value => p_dei_attribute24
1473 ,p_original_value => p_org_rec.dei_attribute24);
1474 end if;
1475
1476 if p_org_rec.dei_attribute25 = p_dei_attribute25 then
1477 hr_transaction_api.set_varchar2_value
1478 (p_transaction_step_id => p_transaction_step_id
1479 ,p_person_id => p_login_person_id
1480 ,p_name => 'P_DEI_ATTRIBUTE25'
1481 ,p_value => p_dei_attribute25);
1482 else
1483 hr_transaction_api.set_varchar2_value
1484 (p_transaction_step_id => p_transaction_step_id
1485 ,p_person_id => p_login_person_id
1486 ,p_name => 'P_DEI_ATTRIBUTE25'
1487 ,p_value => p_dei_attribute25
1488 ,p_original_value => p_org_rec.dei_attribute25);
1489 end if;
1490
1491 if p_org_rec.dei_attribute26 = p_dei_attribute26 then
1492 hr_transaction_api.set_varchar2_value
1493 (p_transaction_step_id => p_transaction_step_id
1494 ,p_person_id => p_login_person_id
1495 ,p_name => 'P_DEI_ATTRIBUTE26'
1496 ,p_value => p_dei_attribute26);
1497 else
1498 hr_transaction_api.set_varchar2_value
1499 (p_transaction_step_id => p_transaction_step_id
1500 ,p_person_id => p_login_person_id
1501 ,p_name => 'P_DEI_ATTRIBUTE26'
1502 ,p_value => p_dei_attribute26
1503 ,p_original_value => p_org_rec.dei_attribute26);
1504 end if;
1505
1506 if p_org_rec.dei_attribute27 = p_dei_attribute27 then
1507 hr_transaction_api.set_varchar2_value
1508 (p_transaction_step_id => p_transaction_step_id
1509 ,p_person_id => p_login_person_id
1510 ,p_name => 'P_DEI_ATTRIBUTE27'
1511 ,p_value => p_dei_attribute27);
1512 else
1513 hr_transaction_api.set_varchar2_value
1514 (p_transaction_step_id => p_transaction_step_id
1515 ,p_person_id => p_login_person_id
1516 ,p_name => 'P_DEI_ATTRIBUTE27'
1517 ,p_value => p_dei_attribute27
1518 ,p_original_value => p_org_rec.dei_attribute27);
1519 end if;
1520
1521 if p_org_rec.dei_attribute28 = p_dei_attribute28 then
1522 hr_transaction_api.set_varchar2_value
1523 (p_transaction_step_id => p_transaction_step_id
1524 ,p_person_id => p_login_person_id
1525 ,p_name => 'P_DEI_ATTRIBUTE28'
1526 ,p_value => p_dei_attribute28);
1527 else
1528 hr_transaction_api.set_varchar2_value
1529 (p_transaction_step_id => p_transaction_step_id
1530 ,p_person_id => p_login_person_id
1531 ,p_name => 'P_DEI_ATTRIBUTE28'
1532 ,p_value => p_dei_attribute28
1533 ,p_original_value => p_org_rec.dei_attribute28);
1534 end if;
1535
1536 if p_org_rec.dei_attribute29 = p_dei_attribute29 then
1537 hr_transaction_api.set_varchar2_value
1538 (p_transaction_step_id => p_transaction_step_id
1539 ,p_person_id => p_login_person_id
1540 ,p_name => 'P_DEI_ATTRIBUTE29'
1541 ,p_value => p_dei_attribute29);
1542 else
1543 hr_transaction_api.set_varchar2_value
1544 (p_transaction_step_id => p_transaction_step_id
1545 ,p_person_id => p_login_person_id
1546 ,p_name => 'P_DEI_ATTRIBUTE29'
1547 ,p_value => p_dei_attribute29
1548 ,p_original_value => p_org_rec.dei_attribute29);
1549 end if;
1550
1551 if p_org_rec.dei_attribute30 = p_dei_attribute30 then
1552 hr_transaction_api.set_varchar2_value
1553 (p_transaction_step_id => p_transaction_step_id
1554 ,p_person_id => p_login_person_id
1555 ,p_name => 'P_DEI_ATTRIBUTE30'
1556 ,p_value => p_dei_attribute30);
1557 else
1558 hr_transaction_api.set_varchar2_value
1559 (p_transaction_step_id => p_transaction_step_id
1560 ,p_person_id => p_login_person_id
1561 ,p_name => 'P_DEI_ATTRIBUTE30'
1562 ,p_value => p_dei_attribute30
1563 ,p_original_value => p_org_rec.dei_attribute30);
1564 end if;
1565
1566 if p_org_rec.dei_information_category = p_dei_information_category then
1567 hr_transaction_api.set_varchar2_value
1568 (p_transaction_step_id => p_transaction_step_id
1569 ,p_person_id => p_login_person_id
1570 ,p_name => 'P_DEI_INFORMATION_CATEGORY'
1571 ,p_value => p_dei_information_category);
1572 else
1573 hr_transaction_api.set_varchar2_value
1574 (p_transaction_step_id => p_transaction_step_id
1575 ,p_person_id => p_login_person_id
1576 ,p_name => 'P_DEI_INFORMATION_CATEGORY'
1577 ,p_value => p_dei_information_category
1578 ,p_original_value => p_org_rec.dei_information_category);
1579 end if;
1580
1581 if p_org_rec.dei_information1 = p_dei_information1 then
1582 hr_transaction_api.set_varchar2_value
1583 (p_transaction_step_id => p_transaction_step_id
1584 ,p_person_id => p_login_person_id
1585 ,p_name => 'P_DEI_INFORMATION1'
1586 ,p_value => p_dei_information1);
1587 else
1588 hr_transaction_api.set_varchar2_value
1589 (p_transaction_step_id => p_transaction_step_id
1590 ,p_person_id => p_login_person_id
1591 ,p_name => 'P_DEI_INFORMATION1'
1592 ,p_value => p_dei_information1
1593 ,p_original_value => p_org_rec.dei_information1);
1594 end if;
1595
1596 if p_org_rec.dei_information2 = p_dei_information2 then
1597 hr_transaction_api.set_varchar2_value
1598 (p_transaction_step_id => p_transaction_step_id
1599 ,p_person_id => p_login_person_id
1600 ,p_name => 'P_DEI_INFORMATION2'
1601 ,p_value => p_dei_information2);
1602 else
1603 hr_transaction_api.set_varchar2_value
1604 (p_transaction_step_id => p_transaction_step_id
1605 ,p_person_id => p_login_person_id
1606 ,p_name => 'P_DEI_INFORMATION2'
1607 ,p_value => p_dei_information2
1608 ,p_original_value => p_org_rec.dei_information2);
1609 end if;
1610
1611 if p_org_rec.dei_information3 = p_dei_information3 then
1612 hr_transaction_api.set_varchar2_value
1613 (p_transaction_step_id => p_transaction_step_id
1614 ,p_person_id => p_login_person_id
1615 ,p_name => 'P_DEI_INFORMATION3'
1616 ,p_value => p_dei_information3);
1617 else
1618 hr_transaction_api.set_varchar2_value
1619 (p_transaction_step_id => p_transaction_step_id
1620 ,p_person_id => p_login_person_id
1621 ,p_name => 'P_DEI_INFORMATION3'
1622 ,p_value => p_dei_information3
1623 ,p_original_value => p_org_rec.dei_information3);
1624 end if;
1625
1626 if p_org_rec.dei_information4 = p_dei_information4 then
1627 hr_transaction_api.set_varchar2_value
1628 (p_transaction_step_id => p_transaction_step_id
1629 ,p_person_id => p_login_person_id
1630 ,p_name => 'P_DEI_INFORMATION4'
1631 ,p_value => p_dei_information4);
1632 else
1633 hr_transaction_api.set_varchar2_value
1634 (p_transaction_step_id => p_transaction_step_id
1635 ,p_person_id => p_login_person_id
1636 ,p_name => 'P_DEI_INFORMATION4'
1637 ,p_value => p_dei_information4
1638 ,p_original_value => p_org_rec.dei_information4);
1639 end if;
1640
1641 if p_org_rec.dei_information5 = p_dei_information5 then
1642 hr_transaction_api.set_varchar2_value
1643 (p_transaction_step_id => p_transaction_step_id
1644 ,p_person_id => p_login_person_id
1645 ,p_name => 'P_DEI_INFORMATION5'
1646 ,p_value => p_dei_information5);
1647 else
1648 hr_transaction_api.set_varchar2_value
1649 (p_transaction_step_id => p_transaction_step_id
1650 ,p_person_id => p_login_person_id
1651 ,p_name => 'P_DEI_INFORMATION5'
1652 ,p_value => p_dei_information5
1653 ,p_original_value => p_org_rec.dei_information5);
1654 end if;
1655
1656 if p_org_rec.dei_information6 = p_dei_information6 then
1657 hr_transaction_api.set_varchar2_value
1658 (p_transaction_step_id => p_transaction_step_id
1659 ,p_person_id => p_login_person_id
1660 ,p_name => 'P_DEI_INFORMATION6'
1661 ,p_value => p_dei_information6);
1662 else
1663 hr_transaction_api.set_varchar2_value
1664 (p_transaction_step_id => p_transaction_step_id
1665 ,p_person_id => p_login_person_id
1666 ,p_name => 'P_DEI_INFORMATION6'
1667 ,p_value => p_dei_information6
1668 ,p_original_value => p_org_rec.dei_information6);
1669 end if;
1670
1671 if p_org_rec.dei_information7 = p_dei_information7 then
1672 hr_transaction_api.set_varchar2_value
1673 (p_transaction_step_id => p_transaction_step_id
1674 ,p_person_id => p_login_person_id
1675 ,p_name => 'P_DEI_INFORMATION7'
1676 ,p_value => p_dei_information7);
1677 else
1678 hr_transaction_api.set_varchar2_value
1679 (p_transaction_step_id => p_transaction_step_id
1680 ,p_person_id => p_login_person_id
1681 ,p_name => 'P_DEI_INFORMATION7'
1682 ,p_value => p_dei_information7
1683 ,p_original_value => p_org_rec.dei_information7);
1684 end if;
1685
1686 if p_org_rec.dei_information8 = p_dei_information8 then
1687 hr_transaction_api.set_varchar2_value
1688 (p_transaction_step_id => p_transaction_step_id
1689 ,p_person_id => p_login_person_id
1690 ,p_name => 'P_DEI_INFORMATION8'
1691 ,p_value => p_dei_information8);
1692 else
1693 hr_transaction_api.set_varchar2_value
1694 (p_transaction_step_id => p_transaction_step_id
1695 ,p_person_id => p_login_person_id
1696 ,p_name => 'P_DEI_INFORMATION8'
1697 ,p_value => p_dei_information8
1698 ,p_original_value => p_org_rec.dei_information8);
1699 end if;
1700
1701 if p_org_rec.dei_information9 = p_dei_information9 then
1702 hr_transaction_api.set_varchar2_value
1703 (p_transaction_step_id => p_transaction_step_id
1704 ,p_person_id => p_login_person_id
1705 ,p_name => 'P_DEI_INFORMATION9'
1706 ,p_value => p_dei_information9);
1707 else
1708 hr_transaction_api.set_varchar2_value
1709 (p_transaction_step_id => p_transaction_step_id
1710 ,p_person_id => p_login_person_id
1711 ,p_name => 'P_DEI_INFORMATION9'
1712 ,p_value => p_dei_information9
1713 ,p_original_value => p_org_rec.dei_information9);
1714 end if;
1715
1716 if p_org_rec.dei_information10 = p_dei_information10 then
1717 hr_transaction_api.set_varchar2_value
1718 (p_transaction_step_id => p_transaction_step_id
1719 ,p_person_id => p_login_person_id
1720 ,p_name => 'P_DEI_INFORMATION10'
1721 ,p_value => p_dei_information10);
1722 else
1723 hr_transaction_api.set_varchar2_value
1724 (p_transaction_step_id => p_transaction_step_id
1725 ,p_person_id => p_login_person_id
1726 ,p_name => 'P_DEI_INFORMATION10'
1727 ,p_value => p_dei_information10
1728 ,p_original_value => p_org_rec.dei_information10);
1729 end if;
1730 if p_org_rec.dei_information11 = p_dei_information11 then
1731 hr_transaction_api.set_varchar2_value
1732 (p_transaction_step_id => p_transaction_step_id
1733 ,p_person_id => p_login_person_id
1734 ,p_name => 'P_DEI_INFORMATION11'
1735 ,p_value => p_dei_information11);
1736 else
1737 hr_transaction_api.set_varchar2_value
1738 (p_transaction_step_id => p_transaction_step_id
1739 ,p_person_id => p_login_person_id
1740 ,p_name => 'P_DEI_INFORMATION11'
1741 ,p_value => p_dei_information11
1742 ,p_original_value => p_org_rec.dei_information11);
1743 end if;
1744
1745 if p_org_rec.dei_information12 = p_dei_information12 then
1746 hr_transaction_api.set_varchar2_value
1747 (p_transaction_step_id => p_transaction_step_id
1748 ,p_person_id => p_login_person_id
1749 ,p_name => 'P_DEI_INFORMATION12'
1750 ,p_value => p_dei_information12);
1751 else
1752 hr_transaction_api.set_varchar2_value
1753 (p_transaction_step_id => p_transaction_step_id
1754 ,p_person_id => p_login_person_id
1755 ,p_name => 'P_DEI_INFORMATION12'
1756 ,p_value => p_dei_information12
1757 ,p_original_value => p_org_rec.dei_information12);
1758 end if;
1759
1760 if p_org_rec.dei_information13 = p_dei_information13 then
1761 hr_transaction_api.set_varchar2_value
1762 (p_transaction_step_id => p_transaction_step_id
1763 ,p_person_id => p_login_person_id
1764 ,p_name => 'P_DEI_INFORMATION13'
1765 ,p_value => p_dei_information13);
1766 else
1767 hr_transaction_api.set_varchar2_value
1768 (p_transaction_step_id => p_transaction_step_id
1769 ,p_person_id => p_login_person_id
1770 ,p_name => 'P_DEI_INFORMATION13'
1771 ,p_value => p_dei_information13
1772 ,p_original_value => p_org_rec.dei_information13);
1773 end if;
1774
1775 if p_org_rec.dei_information14 = p_dei_information14 then
1776 hr_transaction_api.set_varchar2_value
1777 (p_transaction_step_id => p_transaction_step_id
1778 ,p_person_id => p_login_person_id
1779 ,p_name => 'P_DEI_INFORMATION14'
1780 ,p_value => p_dei_information14);
1781 else
1782 hr_transaction_api.set_varchar2_value
1783 (p_transaction_step_id => p_transaction_step_id
1784 ,p_person_id => p_login_person_id
1785 ,p_name => 'P_DEI_INFORMATION14'
1786 ,p_value => p_dei_information14
1787 ,p_original_value => p_org_rec.dei_information14);
1788 end if;
1789
1790 if p_org_rec.dei_information15 = p_dei_information15 then
1791 hr_transaction_api.set_varchar2_value
1792 (p_transaction_step_id => p_transaction_step_id
1793 ,p_person_id => p_login_person_id
1794 ,p_name => 'P_DEI_INFORMATION15'
1795 ,p_value => p_dei_information15);
1796 else
1797 hr_transaction_api.set_varchar2_value
1798 (p_transaction_step_id => p_transaction_step_id
1799 ,p_person_id => p_login_person_id
1800 ,p_name => 'P_DEI_INFORMATION15'
1801 ,p_value => p_dei_information15
1802 ,p_original_value => p_org_rec.dei_information15);
1803 end if;
1804
1805 if p_org_rec.dei_information16 = p_dei_information16 then
1806 hr_transaction_api.set_varchar2_value
1807 (p_transaction_step_id => p_transaction_step_id
1808 ,p_person_id => p_login_person_id
1809 ,p_name => 'P_DEI_INFORMATION16'
1810 ,p_value => p_dei_information16);
1811 else
1812 hr_transaction_api.set_varchar2_value
1813 (p_transaction_step_id => p_transaction_step_id
1814 ,p_person_id => p_login_person_id
1815 ,p_name => 'P_DEI_INFORMATION16'
1816 ,p_value => p_dei_information16
1817 ,p_original_value => p_org_rec.dei_information16);
1818 end if;
1819
1820 if p_org_rec.dei_information17 = p_dei_information17 then
1821 hr_transaction_api.set_varchar2_value
1822 (p_transaction_step_id => p_transaction_step_id
1823 ,p_person_id => p_login_person_id
1824 ,p_name => 'P_DEI_INFORMATION17'
1825 ,p_value => p_dei_information17);
1826 else
1827 hr_transaction_api.set_varchar2_value
1828 (p_transaction_step_id => p_transaction_step_id
1829 ,p_person_id => p_login_person_id
1830 ,p_name => 'P_DEI_INFORMATION17'
1831 ,p_value => p_dei_information17
1832 ,p_original_value => p_org_rec.dei_information17);
1833 end if;
1834
1835 if p_org_rec.dei_information18 = p_dei_information18 then
1836 hr_transaction_api.set_varchar2_value
1837 (p_transaction_step_id => p_transaction_step_id
1838 ,p_person_id => p_login_person_id
1839 ,p_name => 'P_DEI_INFORMATION18'
1840 ,p_value => p_dei_information18);
1841 else
1842 hr_transaction_api.set_varchar2_value
1843 (p_transaction_step_id => p_transaction_step_id
1844 ,p_person_id => p_login_person_id
1845 ,p_name => 'P_DEI_INFORMATION18'
1846 ,p_value => p_dei_information18
1847 ,p_original_value => p_org_rec.dei_information18);
1848 end if;
1849
1850 if p_org_rec.dei_information19 = p_dei_information19 then
1851 hr_transaction_api.set_varchar2_value
1852 (p_transaction_step_id => p_transaction_step_id
1853 ,p_person_id => p_login_person_id
1854 ,p_name => 'P_DEI_INFORMATION19'
1855 ,p_value => p_dei_information19);
1856 else
1857 hr_transaction_api.set_varchar2_value
1858 (p_transaction_step_id => p_transaction_step_id
1859 ,p_person_id => p_login_person_id
1860 ,p_name => 'P_DEI_INFORMATION19'
1861 ,p_value => p_dei_information19
1862 ,p_original_value => p_org_rec.dei_information19);
1863 end if;
1864
1865 if p_org_rec.dei_information20 = p_dei_information20 then
1866 hr_transaction_api.set_varchar2_value
1867 (p_transaction_step_id => p_transaction_step_id
1868 ,p_person_id => p_login_person_id
1869 ,p_name => 'P_DEI_INFORMATION20'
1870 ,p_value => p_dei_information20);
1871 else
1872 hr_transaction_api.set_varchar2_value
1873 (p_transaction_step_id => p_transaction_step_id
1874 ,p_person_id => p_login_person_id
1875 ,p_name => 'P_DEI_INFORMATION20'
1876 ,p_value => p_dei_information20
1877 ,p_original_value => p_org_rec.dei_information20);
1878 end if;
1879
1880 if p_org_rec.dei_information21 = p_dei_information21 then
1881 hr_transaction_api.set_varchar2_value
1882 (p_transaction_step_id => p_transaction_step_id
1883 ,p_person_id => p_login_person_id
1884 ,p_name => 'P_DEI_INFORMATION21'
1885 ,p_value => p_dei_information21);
1886 else
1887 hr_transaction_api.set_varchar2_value
1888 (p_transaction_step_id => p_transaction_step_id
1889 ,p_person_id => p_login_person_id
1890 ,p_name => 'P_DEI_INFORMATION21'
1891 ,p_value => p_dei_information21
1892 ,p_original_value => p_org_rec.dei_information21);
1893 end if;
1894
1895 if p_org_rec.dei_information22 = p_dei_information22 then
1896 hr_transaction_api.set_varchar2_value
1897 (p_transaction_step_id => p_transaction_step_id
1898 ,p_person_id => p_login_person_id
1899 ,p_name => 'P_DEI_INFORMATION22'
1900 ,p_value => p_dei_information22);
1901 else
1902 hr_transaction_api.set_varchar2_value
1903 (p_transaction_step_id => p_transaction_step_id
1904 ,p_person_id => p_login_person_id
1905 ,p_name => 'P_DEI_INFORMATION22'
1906 ,p_value => p_dei_information22
1907 ,p_original_value => p_org_rec.dei_information22);
1908 end if;
1909
1910 if p_org_rec.dei_information23 = p_dei_information23 then
1911 hr_transaction_api.set_varchar2_value
1912 (p_transaction_step_id => p_transaction_step_id
1913 ,p_person_id => p_login_person_id
1914 ,p_name => 'P_DEI_INFORMATION23'
1915 ,p_value => p_dei_information23);
1916 else
1917 hr_transaction_api.set_varchar2_value
1918 (p_transaction_step_id => p_transaction_step_id
1919 ,p_person_id => p_login_person_id
1920 ,p_name => 'P_DEI_INFORMATION23'
1921 ,p_value => p_dei_information23
1922 ,p_original_value => p_org_rec.dei_information23);
1923 end if;
1924
1925 if p_org_rec.dei_information24 = p_dei_information24 then
1926 hr_transaction_api.set_varchar2_value
1927 (p_transaction_step_id => p_transaction_step_id
1928 ,p_person_id => p_login_person_id
1929 ,p_name => 'P_DEI_INFORMATION24'
1930 ,p_value => p_dei_information24);
1931 else
1932 hr_transaction_api.set_varchar2_value
1933 (p_transaction_step_id => p_transaction_step_id
1934 ,p_person_id => p_login_person_id
1935 ,p_name => 'P_DEI_INFORMATION24'
1936 ,p_value => p_dei_information24
1937 ,p_original_value => p_org_rec.dei_information24);
1938 end if;
1939
1940 if p_org_rec.dei_information25 = p_dei_information25 then
1941 hr_transaction_api.set_varchar2_value
1942 (p_transaction_step_id => p_transaction_step_id
1943 ,p_person_id => p_login_person_id
1944 ,p_name => 'P_DEI_INFORMATION25'
1945 ,p_value => p_dei_information25);
1946 else
1947 hr_transaction_api.set_varchar2_value
1948 (p_transaction_step_id => p_transaction_step_id
1949 ,p_person_id => p_login_person_id
1950 ,p_name => 'P_DEI_INFORMATION25'
1951 ,p_value => p_dei_information25
1952 ,p_original_value => p_org_rec.dei_information25);
1953 end if;
1954
1955 if p_org_rec.dei_information26 = p_dei_information26 then
1956 hr_transaction_api.set_varchar2_value
1957 (p_transaction_step_id => p_transaction_step_id
1958 ,p_person_id => p_login_person_id
1959 ,p_name => 'P_DEI_INFORMATION26'
1960 ,p_value => p_dei_information26);
1961 else
1962 hr_transaction_api.set_varchar2_value
1963 (p_transaction_step_id => p_transaction_step_id
1964 ,p_person_id => p_login_person_id
1965 ,p_name => 'P_DEI_INFORMATION26'
1966 ,p_value => p_dei_information26
1967 ,p_original_value => p_org_rec.dei_information26);
1968 end if;
1969
1970 if p_org_rec.dei_information27 = p_dei_information27 then
1971 hr_transaction_api.set_varchar2_value
1972 (p_transaction_step_id => p_transaction_step_id
1973 ,p_person_id => p_login_person_id
1974 ,p_name => 'P_DEI_INFORMATION27'
1975 ,p_value => p_dei_information27);
1976 else
1977 hr_transaction_api.set_varchar2_value
1978 (p_transaction_step_id => p_transaction_step_id
1979 ,p_person_id => p_login_person_id
1980 ,p_name => 'P_DEI_INFORMATION27'
1981 ,p_value => p_dei_information27
1982 ,p_original_value => p_org_rec.dei_information27);
1983 end if;
1984
1985 if p_org_rec.dei_information28 = p_dei_information28 then
1986 hr_transaction_api.set_varchar2_value
1987 (p_transaction_step_id => p_transaction_step_id
1988 ,p_person_id => p_login_person_id
1989 ,p_name => 'P_DEI_INFORMATION28'
1990 ,p_value => p_dei_information28);
1991 else
1992 hr_transaction_api.set_varchar2_value
1993 (p_transaction_step_id => p_transaction_step_id
1994 ,p_person_id => p_login_person_id
1995 ,p_name => 'P_DEI_INFORMATION28'
1996 ,p_value => p_dei_information28
1997 ,p_original_value => p_org_rec.dei_information28);
1998 end if;
1999
2000 if p_org_rec.dei_information29 = p_dei_information29 then
2001 hr_transaction_api.set_varchar2_value
2002 (p_transaction_step_id => p_transaction_step_id
2003 ,p_person_id => p_login_person_id
2004 ,p_name => 'P_DEI_INFORMATION29'
2005 ,p_value => p_dei_information29);
2006 else
2007 hr_transaction_api.set_varchar2_value
2008 (p_transaction_step_id => p_transaction_step_id
2009 ,p_person_id => p_login_person_id
2010 ,p_name => 'P_DEI_INFORMATION29'
2011 ,p_value => p_dei_information29
2012 ,p_original_value => p_org_rec.dei_information29);
2013 end if;
2014
2015 if p_org_rec.dei_information30 = p_dei_information30 then
2016 hr_transaction_api.set_varchar2_value
2017 (p_transaction_step_id => p_transaction_step_id
2018 ,p_person_id => p_login_person_id
2019 ,p_name => 'P_DEI_INFORMATION30'
2020 ,p_value => p_dei_information30);
2021 else
2022 hr_transaction_api.set_varchar2_value
2023 (p_transaction_step_id => p_transaction_step_id
2024 ,p_person_id => p_login_person_id
2025 ,p_name => 'P_DEI_INFORMATION30'
2026 ,p_value => p_dei_information30
2027 ,p_original_value => p_org_rec.dei_information30);
2028 end if;
2029
2030 if p_org_rec.request_id = p_request_id then
2031 hr_transaction_api.set_number_value
2032 (p_transaction_step_id => p_transaction_step_id
2033 ,p_person_id => p_login_person_id
2034 ,p_name => 'P_REQUEST_ID'
2035 ,p_value => p_request_id);
2036 else
2037 hr_transaction_api.set_number_value
2038 (p_transaction_step_id => p_transaction_step_id
2039 ,p_person_id => p_login_person_id
2040 ,p_name => 'P_REQUEST_ID'
2041 ,p_value => p_request_id
2042 ,p_original_value => p_org_rec.request_id);
2043 end if;
2044
2045 if p_org_rec.program_application_id = p_program_application_id then
2046 hr_transaction_api.set_number_value
2047 (p_transaction_step_id => p_transaction_step_id
2048 ,p_person_id => p_login_person_id
2049 ,p_name => 'P_PROGRAM_APPLICATION_ID'
2050 ,p_value => p_program_application_id
2051 ,p_original_value => p_org_rec.program_application_id);
2052 else
2053 hr_transaction_api.set_number_value
2054 (p_transaction_step_id => p_transaction_step_id
2055 ,p_person_id => p_login_person_id
2056 ,p_name => 'P_PROGRAM_APPLICATION_ID'
2057 ,p_value => p_program_application_id);
2058 end if;
2059
2060 if p_org_rec.program_id = p_program_id then
2061 hr_transaction_api.set_number_value
2062 (p_transaction_step_id => p_transaction_step_id
2063 ,p_person_id => p_login_person_id
2064 ,p_name => 'P_PROGRAM_ID'
2065 ,p_value => p_program_id
2066 ,p_original_value => p_org_rec.program_id);
2067 else
2068 hr_transaction_api.set_number_value
2069 (p_transaction_step_id => p_transaction_step_id
2070 ,p_person_id => p_login_person_id
2071 ,p_name => 'P_PROGRAM_ID'
2072 ,p_value => p_program_id);
2073 end if;
2074
2075 if p_org_rec.program_update_date = p_program_update_date then
2076 hr_transaction_api.set_date_value
2077 (p_transaction_step_id => p_transaction_step_id
2078 ,p_person_id => p_login_person_id
2079 ,p_name => 'P_PROGRAM_UPDATE_DATE'
2080 ,p_value => p_program_update_date);
2081 else
2082 hr_transaction_api.set_date_value
2083 (p_transaction_step_id => p_transaction_step_id
2084 ,p_person_id => p_login_person_id
2085 ,p_name => 'P_PROGRAM_UPDATE_DATE'
2086 ,p_value => p_program_update_date
2087 ,p_original_value => p_org_rec.program_update_date);
2088 end if;
2089
2090 if p_org_rec.object_version_number = p_object_version_number then
2091 hr_transaction_api.set_number_value
2092 (p_transaction_step_id => p_transaction_step_id
2093 ,p_person_id => p_login_person_id
2094 ,p_name => 'P_OBJECT_VERSION_NUMBER'
2095 ,p_value => p_object_version_number);
2096 else
2097 hr_transaction_api.set_number_value
2098 (p_transaction_step_id => p_transaction_step_id
2099 ,p_person_id => p_login_person_id
2100 ,p_name => 'P_OBJECT_VERSION_NUMBER'
2101 ,p_value => p_object_version_number
2102 ,p_original_value => p_org_rec.object_version_number);
2103 end if;
2104
2105 end if;
2106
2107 END IF;
2108
2109 if p_return_status = 'E' then
2110 hr_utility.raise_error;
2111 else
2112 p_return_status := 'S';
2113 end if;
2114
2115 EXCEPTION
2116
2117 WHEN g_data_error THEN
2118 hr_utility.trace('Exception in g_data_error in '||l_proc||','|| SQLERRM );
2119 hr_utility.set_location(' Leaving:' || l_proc,666);
2120 p_return_status := SQLERRM;
2121
2122 WHEN hr_utility.hr_error THEN
2123 hr_utility.get_message_details(msg_name,msg_appl);
2124 fnd_message.set_name(msg_appl,msg_name);
2125 p_return_status := hr_utility.get_message;
2126
2127 WHEN OTHERS THEN
2128 hr_utility.trace('When others exception in ' ||l_proc||','|| SQLERRM );
2129 hr_utility.set_location(' Leaving:' || l_proc,660);
2130 p_return_status := SQLERRM;
2131
2132
2133 END save_transaction_values;
2134 --------------------------------------------------------------------------
2135 --------------------------Process_api-------------------------------------
2136 ------This method is called from commit_transaction ----------------------
2137 --------------------------------------------------------------------------
2138
2139 procedure process_api
2140 (p_validate in boolean default false
2141 ,p_transaction_step_id in number
2142 ,p_effective_date in varchar2 default null
2143 )
2144 IS
2145
2146 l_proc varchar2(72) := g_package ||'.process_api';
2147 p_dor_rec HR_DOCUMENT_EXTRA_INFO%rowtype;
2148 p_return_status varchar2(100);
2149 l_object_version_number number;
2150 l_effective_date date;
2151 l_validate number;
2152 p_item_type wf_items.item_type%type;
2153 p_item_key wf_items.item_key%type;
2154 p_action_mode varchar2(30);
2155 p_transaction_id hr_api_transactions.transaction_id%type;
2156 l_user_name varchar2(30);
2157 l_verified_by_user_name varchar2(30);
2158 l_verified_by number;
2159 l_verified_date date;
2160 msg_name varchar2(1000);
2161 msg_appl varchar2(10);
2162
2163 BEGIN
2164
2165 -- hr_utility.trace_on(null,'ORCL1');
2166 hr_utility.set_location(' Entering:' || l_proc,40);
2167
2168
2169 If p_validate then
2170 l_validate := hr_api.g_true_num;
2171 else
2172 l_validate := hr_api.g_false_num;
2173 end if;
2174
2175 p_transaction_id := get_transaction_id(p_transaction_step_id);
2176 p_action_mode := getActionMode(p_transaction_id);
2177
2178
2179 get_review_data_from_tt(
2180 p_transaction_step_id => p_transaction_step_id
2181 ,p_dor_rec => p_dor_rec);
2182
2183 p_item_type :=
2184 hr_transaction_api.get_varchar2_value
2185 (p_transaction_step_id => p_transaction_step_id
2186 ,p_name => 'P_ITEM_TYPE');
2187
2188 p_item_key :=
2189 hr_transaction_api.get_varchar2_value
2190 (p_transaction_step_id => p_transaction_step_id
2191 ,p_name => 'P_ITEM_KEY');
2192
2193 if p_dor_rec.verified_by is null then
2194
2195 l_user_name := wf_engine.GetItemAttrText(
2196 itemtype => p_item_type,
2197 itemkey => p_item_key,
2198 aname => 'FORWARD_TO_USERNAME');
2199
2200 /*if l_user_name is not null then
2201 select user_id into l_verified_by
2202 from fnd_user where user_name = l_user_name;
2203 end if;*/
2204
2205 /*Bug#14001547.Commented this part as this is not retrieving the exact user_name in case of approval based on position hierarchy.
2206 Added below code to fetch the userid and then username of the person who approved the transaction at the latest incase of approval based on position hierarchy.*/
2207
2208 if ((l_user_name is not null) and (substr(l_user_name,1,3) NOT IN ('POS','PQH') )) then /*Added PHQ for bug#14806618*/
2209 select user_id into l_verified_by
2210 from fnd_user where user_name = l_user_name;
2211 elsif ((l_user_name is not null) and (substr(l_user_name,1,3) IN ('POS','PQH') )) then /*Added PHQ for bug#14806618*/
2212 select USER_NAME
2213 into l_verified_by_user_name
2214 from PQH_SS_APPROVAL_HISTORY
2215 where transaction_history_id = p_transaction_id
2216 and creation_date = (select max(CREATION_DATE)
2217 from PQH_SS_APPROVAL_HISTORY
2218 where transaction_history_id = p_transaction_id);
2219
2220 select user_id into l_verified_by
2221 from fnd_user where user_name = l_verified_by_user_name;
2222 end if;
2223
2224 l_verified_date := sysdate;
2225
2226 end if;
2227
2228 if p_action_mode = 'DOR_INSERT' then
2229 hr_document_extra_info_swi.create_doc_extra_info
2230 (p_validate => l_validate
2231 ,p_person_id => p_dor_rec.person_id
2232 ,p_document_type_id => p_dor_rec.document_type_id
2233 ,p_date_from => p_dor_rec.date_from
2234 ,p_date_to => p_dor_rec.date_to
2235 ,p_document_number => p_dor_rec.document_number
2236 ,p_issued_by => p_dor_rec.issued_by
2237 ,p_issued_at => p_dor_rec.issued_at
2238 ,p_issued_date => p_dor_rec.issued_date
2239 ,p_issuing_authority => p_dor_rec.issuing_authority
2240 ,p_verified_by => l_verified_by
2241 ,p_verified_date => l_verified_date
2242 ,p_related_object_name => p_dor_rec.related_object_name
2243 ,p_related_object_id_col => p_dor_rec.related_object_id_col
2244 ,p_related_object_id => p_dor_rec.related_object_id
2245 ,p_dei_attribute_category => p_dor_rec.dei_attribute_category
2246 ,p_dei_attribute1 => p_dor_rec.dei_attribute1
2247 ,p_dei_attribute2 => p_dor_rec.dei_attribute2
2248 ,p_dei_attribute3 => p_dor_rec.dei_attribute3
2249 ,p_dei_attribute4 => p_dor_rec.dei_attribute4
2250 ,p_dei_attribute5 => p_dor_rec.dei_attribute5
2251 ,p_dei_attribute6 => p_dor_rec.dei_attribute6
2252 ,p_dei_attribute7 => p_dor_rec.dei_attribute7
2253 ,p_dei_attribute8 => p_dor_rec.dei_attribute8
2254 ,p_dei_attribute9 => p_dor_rec.dei_attribute9
2255 ,p_dei_attribute10 => p_dor_rec.dei_attribute10
2256 ,p_dei_attribute11 => p_dor_rec.dei_attribute11
2257 ,p_dei_attribute12 => p_dor_rec.dei_attribute12
2258 ,p_dei_attribute13 => p_dor_rec.dei_attribute13
2259 ,p_dei_attribute14 => p_dor_rec.dei_attribute14
2260 ,p_dei_attribute15 => p_dor_rec.dei_attribute15
2261 ,p_dei_attribute16 => p_dor_rec.dei_attribute16
2262 ,p_dei_attribute17 => p_dor_rec.dei_attribute17
2263 ,p_dei_attribute18 => p_dor_rec.dei_attribute18
2264 ,p_dei_attribute19 => p_dor_rec.dei_attribute19
2265 ,p_dei_attribute20 => p_dor_rec.dei_attribute20
2266 ,p_dei_attribute21 => p_dor_rec.dei_attribute21
2267 ,p_dei_attribute22 => p_dor_rec.dei_attribute22
2268 ,p_dei_attribute23 => p_dor_rec.dei_attribute23
2269 ,p_dei_attribute24 => p_dor_rec.dei_attribute24
2270 ,p_dei_attribute25 => p_dor_rec.dei_attribute25
2271 ,p_dei_attribute26 => p_dor_rec.dei_attribute26
2272 ,p_dei_attribute27 => p_dor_rec.dei_attribute27
2273 ,p_dei_attribute28 => p_dor_rec.dei_attribute28
2274 ,p_dei_attribute29 => p_dor_rec.dei_attribute29
2275 ,p_dei_attribute30 => p_dor_rec.dei_attribute30
2276 ,p_dei_information_category => p_dor_rec.dei_information_category
2277 ,p_dei_information1 => p_dor_rec.dei_information1
2278 ,p_dei_information2 => p_dor_rec.dei_information2
2279 ,p_dei_information3 => p_dor_rec.dei_information3
2280 ,p_dei_information4 => p_dor_rec.dei_information4
2281 ,p_dei_information5 => p_dor_rec.dei_information5
2282 ,p_dei_information6 => p_dor_rec.dei_information6
2283 ,p_dei_information7 => p_dor_rec.dei_information7
2284 ,p_dei_information8 => p_dor_rec.dei_information8
2285 ,p_dei_information9 => p_dor_rec.dei_information9
2286 ,p_dei_information10 => p_dor_rec.dei_information10
2287 ,p_dei_information11 => p_dor_rec.dei_information11
2288 ,p_dei_information12 => p_dor_rec.dei_information12
2289 ,p_dei_information13 => p_dor_rec.dei_information13
2290 ,p_dei_information14 => p_dor_rec.dei_information14
2291 ,p_dei_information15 => p_dor_rec.dei_information15
2292 ,p_dei_information16 => p_dor_rec.dei_information16
2293 ,p_dei_information17 => p_dor_rec.dei_information17
2294 ,p_dei_information18 => p_dor_rec.dei_information18
2295 ,p_dei_information19 => p_dor_rec.dei_information19
2296 ,p_dei_information20 => p_dor_rec.dei_information20
2297 ,p_dei_information21 => p_dor_rec.dei_information21
2298 ,p_dei_information22 => p_dor_rec.dei_information22
2299 ,p_dei_information23 => p_dor_rec.dei_information23
2300 ,p_dei_information24 => p_dor_rec.dei_information24
2301 ,p_dei_information25 => p_dor_rec.dei_information25
2302 ,p_dei_information26 => p_dor_rec.dei_information26
2303 ,p_dei_information27 => p_dor_rec.dei_information27
2304 ,p_dei_information28 => p_dor_rec.dei_information28
2305 ,p_dei_information29 => p_dor_rec.dei_information29
2306 ,p_dei_information30 => p_dor_rec.dei_information30
2307 ,p_request_id => p_dor_rec.request_id
2308 ,p_program_application_id => p_dor_rec.program_application_id
2309 ,p_program_id => p_dor_rec.program_id
2310 ,p_program_update_date => p_dor_rec.program_update_date
2311 ,p_document_extra_info_id => p_dor_rec.document_extra_info_id
2312 ,p_object_version_number => l_object_version_number
2313 ,p_return_status => p_return_status
2314 );
2315
2316 elsif p_action_mode ='DOR_UPDATE' then
2317
2318
2319 hr_document_extra_info_swi.update_doc_extra_info
2320 (p_validate => l_validate
2321 ,p_person_id => p_dor_rec.person_id
2322 ,p_document_type_id => p_dor_rec.document_type_id
2323 ,p_date_from => p_dor_rec.date_from
2324 ,p_date_to => p_dor_rec.date_to
2325 ,p_document_number => p_dor_rec.document_number
2326 ,p_issued_by => p_dor_rec.issued_by
2327 ,p_issued_at => p_dor_rec.issued_at
2328 ,p_issued_date => p_dor_rec.issued_date
2329 ,p_issuing_authority => p_dor_rec.issuing_authority
2330 ,p_verified_by => l_verified_by
2331 ,p_verified_date => p_dor_rec.verified_date
2332 ,p_related_object_name => p_dor_rec.related_object_name
2333 ,p_related_object_id_col => p_dor_rec.related_object_id_col
2334 ,p_related_object_id => p_dor_rec.related_object_id
2335 ,p_dei_attribute_category => p_dor_rec.dei_attribute_category
2336 ,p_dei_attribute1 => p_dor_rec.dei_attribute1
2337 ,p_dei_attribute2 => p_dor_rec.dei_attribute2
2338 ,p_dei_attribute3 => p_dor_rec.dei_attribute3
2339 ,p_dei_attribute4 => p_dor_rec.dei_attribute4
2340 ,p_dei_attribute5 => p_dor_rec.dei_attribute5
2341 ,p_dei_attribute6 => p_dor_rec.dei_attribute6
2342 ,p_dei_attribute7 => p_dor_rec.dei_attribute7
2343 ,p_dei_attribute8 => p_dor_rec.dei_attribute8
2344 ,p_dei_attribute9 => p_dor_rec.dei_attribute9
2345 ,p_dei_attribute10 => p_dor_rec.dei_attribute10
2346 ,p_dei_attribute11 => p_dor_rec.dei_attribute11
2347 ,p_dei_attribute12 => p_dor_rec.dei_attribute12
2348 ,p_dei_attribute13 => p_dor_rec.dei_attribute13
2349 ,p_dei_attribute14 => p_dor_rec.dei_attribute14
2350 ,p_dei_attribute15 => p_dor_rec.dei_attribute15
2351 ,p_dei_attribute16 => p_dor_rec.dei_attribute16
2352 ,p_dei_attribute17 => p_dor_rec.dei_attribute17
2353 ,p_dei_attribute18 => p_dor_rec.dei_attribute18
2354 ,p_dei_attribute19 => p_dor_rec.dei_attribute19
2355 ,p_dei_attribute20 => p_dor_rec.dei_attribute20
2356 ,p_dei_attribute21 => p_dor_rec.dei_attribute21
2357 ,p_dei_attribute22 => p_dor_rec.dei_attribute22
2358 ,p_dei_attribute23 => p_dor_rec.dei_attribute23
2359 ,p_dei_attribute24 => p_dor_rec.dei_attribute24
2360 ,p_dei_attribute25 => p_dor_rec.dei_attribute25
2361 ,p_dei_attribute26 => p_dor_rec.dei_attribute26
2362 ,p_dei_attribute27 => p_dor_rec.dei_attribute27
2363 ,p_dei_attribute28 => p_dor_rec.dei_attribute28
2364 ,p_dei_attribute29 => p_dor_rec.dei_attribute29
2365 ,p_dei_attribute30 => p_dor_rec.dei_attribute30
2366 ,p_dei_information_category => p_dor_rec.dei_information_category
2367 ,p_dei_information1 => p_dor_rec.dei_information1
2368 ,p_dei_information2 => p_dor_rec.dei_information2
2369 ,p_dei_information3 => p_dor_rec.dei_information3
2370 ,p_dei_information4 => p_dor_rec.dei_information4
2371 ,p_dei_information5 => p_dor_rec.dei_information5
2372 ,p_dei_information6 => p_dor_rec.dei_information6
2373 ,p_dei_information7 => p_dor_rec.dei_information7
2374 ,p_dei_information8 => p_dor_rec.dei_information8
2375 ,p_dei_information9 => p_dor_rec.dei_information9
2376 ,p_dei_information10 => p_dor_rec.dei_information10
2377 ,p_dei_information11 => p_dor_rec.dei_information11
2378 ,p_dei_information12 => p_dor_rec.dei_information12
2379 ,p_dei_information13 => p_dor_rec.dei_information13
2380 ,p_dei_information14 => p_dor_rec.dei_information14
2381 ,p_dei_information15 => p_dor_rec.dei_information15
2382 ,p_dei_information16 => p_dor_rec.dei_information16
2383 ,p_dei_information17 => p_dor_rec.dei_information17
2384 ,p_dei_information18 => p_dor_rec.dei_information18
2385 ,p_dei_information19 => p_dor_rec.dei_information19
2386 ,p_dei_information20 => p_dor_rec.dei_information20
2387 ,p_dei_information21 => p_dor_rec.dei_information21
2388 ,p_dei_information22 => p_dor_rec.dei_information22
2389 ,p_dei_information23 => p_dor_rec.dei_information23
2390 ,p_dei_information24 => p_dor_rec.dei_information24
2391 ,p_dei_information25 => p_dor_rec.dei_information25
2392 ,p_dei_information26 => p_dor_rec.dei_information26
2393 ,p_dei_information27 => p_dor_rec.dei_information27
2394 ,p_dei_information28 => p_dor_rec.dei_information28
2395 ,p_dei_information29 => p_dor_rec.dei_information29
2396 ,p_dei_information30 => p_dor_rec.dei_information30
2397 ,p_request_id => p_dor_rec.request_id
2398 ,p_program_application_id => p_dor_rec.program_application_id
2399 ,p_program_id => p_dor_rec.program_id
2400 ,p_program_update_date => p_dor_rec.program_update_date
2401 ,p_document_extra_info_id => p_dor_rec.document_extra_info_id
2402 ,p_object_version_number => p_dor_rec.object_version_number
2403 ,p_return_status => p_return_status
2404 );
2405
2406 end if;
2407
2408
2409
2410 /*-----------Flipping the attachments primary key from transactionid
2411 to docextrainfoid-------------------------------------------*/
2412
2413 save_attachments(
2414 p_transaction_id => p_transaction_id
2415 ,p_document_extra_info_id => p_dor_rec.document_extra_info_id
2416 ,p_flip_flag => 'NTXN'
2417 ,p_return_status => p_return_status);
2418
2419
2420 if p_return_status = 'E' then
2421 hr_utility.raise_error;
2422 else
2423 p_return_status := 'S';
2424 end if;
2425
2426
2427 EXCEPTION
2428
2429 WHEN g_data_error THEN
2430 hr_utility.trace('Exception in g_data_error in '||l_proc||','|| SQLERRM );
2431 hr_utility.set_location(' Leaving:' || l_proc,666);
2432 raise;
2433
2434 WHEN hr_utility.hr_error THEN
2435 hr_utility.get_message_details(msg_name,msg_appl);
2436 fnd_message.set_name(msg_appl,msg_name);
2437 p_return_status := hr_utility.get_message;
2438
2439 WHEN OTHERS THEN
2440 hr_utility.trace('When others exception in ' ||l_proc||','|| SQLERRM );
2441 hr_utility.set_location(' Leaving:' || l_proc,660);
2442 raise ;
2443
2444
2445 END process_api;
2446
2447
2448 --------------------------------------------------------------------------
2449 --------------------------validate_dor------------------------------------
2450 ------This method validate the api of documents of records----------------
2451 --------------------------------------------------------------------------
2452
2453
2454
2455 PROCEDURE validate_dor(
2456 p_validate in number default hr_api.g_true_num
2457 ,p_person_id in number
2458 ,p_document_extra_info_id in number
2459 ,p_document_type_id in number
2460 ,p_date_from in date
2461 ,p_date_to in date default null
2462 ,p_document_number in varchar2
2463 ,p_issued_by in varchar2 default null
2464 ,p_issued_at in varchar2 default null
2465 ,p_issued_date in date default null
2466 ,p_issuing_authority in varchar2 default null
2467 ,p_verified_by in number default null
2468 ,p_verified_date in date default null
2469 ,p_related_object_name in varchar2 default null
2470 ,p_related_object_id_col in varchar2 default null
2471 ,p_related_object_id in number default null
2472 ,p_dei_attribute_category in varchar2 default null
2473 ,p_dei_attribute1 in varchar2 default null
2474 ,p_dei_attribute2 in varchar2 default null
2475 ,p_dei_attribute3 in varchar2 default null
2476 ,p_dei_attribute4 in varchar2 default null
2477 ,p_dei_attribute5 in varchar2 default null
2478 ,p_dei_attribute6 in varchar2 default null
2479 ,p_dei_attribute7 in varchar2 default null
2480 ,p_dei_attribute8 in varchar2 default null
2481 ,p_dei_attribute9 in varchar2 default null
2482 ,p_dei_attribute10 in varchar2 default null
2483 ,p_dei_attribute11 in varchar2 default null
2484 ,p_dei_attribute12 in varchar2 default null
2485 ,p_dei_attribute13 in varchar2 default null
2486 ,p_dei_attribute14 in varchar2 default null
2487 ,p_dei_attribute15 in varchar2 default null
2488 ,p_dei_attribute16 in varchar2 default null
2489 ,p_dei_attribute17 in varchar2 default null
2490 ,p_dei_attribute18 in varchar2 default null
2491 ,p_dei_attribute19 in varchar2 default null
2492 ,p_dei_attribute20 in varchar2 default null
2493 ,p_dei_attribute21 in varchar2 default null
2494 ,p_dei_attribute22 in varchar2 default null
2495 ,p_dei_attribute23 in varchar2 default null
2496 ,p_dei_attribute24 in varchar2 default null
2497 ,p_dei_attribute25 in varchar2 default null
2498 ,p_dei_attribute26 in varchar2 default null
2499 ,p_dei_attribute27 in varchar2 default null
2500 ,p_dei_attribute28 in varchar2 default null
2501 ,p_dei_attribute29 in varchar2 default null
2502 ,p_dei_attribute30 in varchar2 default null
2503 ,p_dei_information_category in varchar2 default null
2504 ,p_dei_information1 in varchar2 default null
2505 ,p_dei_information2 in varchar2 default null
2506 ,p_dei_information3 in varchar2 default null
2507 ,p_dei_information4 in varchar2 default null
2508 ,p_dei_information5 in varchar2 default null
2509 ,p_dei_information6 in varchar2 default null
2510 ,p_dei_information7 in varchar2 default null
2511 ,p_dei_information8 in varchar2 default null
2512 ,p_dei_information9 in varchar2 default null
2513 ,p_dei_information10 in varchar2 default null
2514 ,p_dei_information11 in varchar2 default null
2515 ,p_dei_information12 in varchar2 default null
2516 ,p_dei_information13 in varchar2 default null
2517 ,p_dei_information14 in varchar2 default null
2518 ,p_dei_information15 in varchar2 default null
2519 ,p_dei_information16 in varchar2 default null
2520 ,p_dei_information17 in varchar2 default null
2521 ,p_dei_information18 in varchar2 default null
2522 ,p_dei_information19 in varchar2 default null
2523 ,p_dei_information20 in varchar2 default null
2524 ,p_dei_information21 in varchar2 default null
2525 ,p_dei_information22 in varchar2 default null
2526 ,p_dei_information23 in varchar2 default null
2527 ,p_dei_information24 in varchar2 default null
2528 ,p_dei_information25 in varchar2 default null
2529 ,p_dei_information26 in varchar2 default null
2530 ,p_dei_information27 in varchar2 default null
2531 ,p_dei_information28 in varchar2 default null
2532 ,p_dei_information29 in varchar2 default null
2533 ,p_dei_information30 in varchar2 default null
2534 ,p_request_id in number default null
2535 ,p_program_application_id in number default null
2536 ,p_program_id in number default null
2537 ,p_program_update_date in date default null
2538 ,p_action_mode in varchar2 default null
2539 ,p_object_version_number in out nocopy number
2540 ,p_return_status out nocopy varchar2
2541 )
2542 IS
2543
2544 l_proc varchar2(72) := g_package ||'validate_dor';
2545 l_document_type hr_document_types_v.document_type%type;
2546 l_num number;
2547 l_num1 number;
2548 msg_name varchar2(1000);
2549 msg_appl varchar2(10);
2550
2551 --FIX FOR BUG 15835786
2552 cursor csr_chk_duplicate_txn(p_document_type_id number,
2553 p_person_id number,
2554 p_date_from DATE,
2555 p_date_to DATE,
2556 p_document_number varchar2) is
2557 select count(*)
2558 from hr_api_transaction_values hatv1,
2559 hr_api_transaction_values hatv2,
2560 hr_api_transaction_values hatv3,
2561 hr_api_transaction_values hatv4,
2562 hr_api_transaction_steps hats,
2563 hr_api_transactions hat
2564 WHERE hat.selected_person_id = p_person_id
2565 and hatv1.number_value = p_document_type_id
2566 and hatv2.date_value = p_date_from
2567 and hatv3.date_value = p_date_to
2568 and hatv4.VARCHAR2_VALUE = p_document_number
2569 and hatv1.name = 'P_DOCUMENT_TYPE_ID'
2570 and hatv2.name = 'P_DATE_FROM'
2571 and hatv3.name = 'P_DATE_TO'
2572 and hatv4.name = 'P_DOCUMENT_NUMBER'
2573 and hatv1.transaction_step_id = hats.transaction_step_id
2574 and hatv2.transaction_step_id = hats.transaction_step_id
2575 and hatv3.transaction_step_id = hats.transaction_step_id
2576 and hatv4.transaction_step_id =hats.transaction_step_id
2577 and hats.transaction_id = hat.transaction_id
2578 and hat.transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
2579 and hat.status not in ('D','E','W');
2580
2581
2582 cursor csr_chk_doc_number(p_document_type_id number,
2583 p_person_id number,
2584 p_document_number varchar2) is
2585 select count(*)
2586 from hr_api_transaction_values hatv1,
2587 hr_api_transaction_values hatv2,
2588 hr_api_transaction_steps hats,
2589 hr_api_transactions hat
2590 WHERE hat.selected_person_id = p_person_id
2591 and hatv1.number_value = p_document_type_id
2592 and hatv2.VARCHAR2_VALUE = p_document_number
2593 and hatv1.name = 'P_DOCUMENT_TYPE_ID'
2594 and hatv2.name = 'P_DOCUMENT_NUMBER'
2595 and hatv1.transaction_step_id = hats.transaction_step_id
2596 and hatv2.transaction_step_id =hats.transaction_step_id
2597 and hats.transaction_id = hat.transaction_id
2598 and hat.transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
2599 and hat.status not in ('D','E','W');
2600 --FIX FOR BUG 15835786
2601
2602 BEGIN
2603
2604 --hr_utility.trace_on(null,'ORCL1');
2605 hr_utility.set_location(' Entering:' || l_proc,50);
2606
2607
2608 --- Validating the document information thru api------------------
2609 if p_action_mode = 'DOR_INSERT' then
2610 hr_document_extra_info_swi.create_doc_extra_info
2611 (p_validate => p_validate
2612 ,p_person_id => p_person_id
2613 ,p_document_type_id => p_document_type_id
2614 ,p_date_from => p_date_from
2615 ,p_date_to => p_date_to
2616 ,p_document_number => p_document_number
2617 ,p_issued_by => p_issued_by
2618 ,p_issued_at => p_issued_at
2619 ,p_issued_date => p_issued_date
2620 ,p_issuing_authority => p_issuing_authority
2621 ,p_verified_by => p_verified_by
2622 ,p_verified_date => p_verified_date
2623 ,p_related_object_name => p_related_object_name
2624 ,p_related_object_id_col => p_related_object_id_col
2625 ,p_related_object_id => p_related_object_id
2626 ,p_dei_attribute_category => p_dei_attribute_category
2627 ,p_dei_attribute1 => p_dei_attribute1
2628 ,p_dei_attribute2 => p_dei_attribute2
2629 ,p_dei_attribute3 => p_dei_attribute3
2630 ,p_dei_attribute4 => p_dei_attribute4
2631 ,p_dei_attribute5 => p_dei_attribute5
2632 ,p_dei_attribute6 => p_dei_attribute6
2633 ,p_dei_attribute7 => p_dei_attribute7
2634 ,p_dei_attribute8 => p_dei_attribute8
2635 ,p_dei_attribute9 => p_dei_attribute9
2636 ,p_dei_attribute10 => p_dei_attribute10
2637 ,p_dei_attribute11 => p_dei_attribute11
2638 ,p_dei_attribute12 => p_dei_attribute12
2639 ,p_dei_attribute13 => p_dei_attribute13
2640 ,p_dei_attribute14 => p_dei_attribute14
2641 ,p_dei_attribute15 => p_dei_attribute15
2642 ,p_dei_attribute16 => p_dei_attribute16
2643 ,p_dei_attribute17 => p_dei_attribute17
2644 ,p_dei_attribute18 => p_dei_attribute18
2645 ,p_dei_attribute19 => p_dei_attribute19
2646 ,p_dei_attribute20 => p_dei_attribute20
2647 ,p_dei_attribute21 => p_dei_attribute21
2648 ,p_dei_attribute22 => p_dei_attribute22
2649 ,p_dei_attribute23 => p_dei_attribute23
2650 ,p_dei_attribute24 => p_dei_attribute24
2651 ,p_dei_attribute25 => p_dei_attribute25
2652 ,p_dei_attribute26 => p_dei_attribute26
2653 ,p_dei_attribute27 => p_dei_attribute27
2654 ,p_dei_attribute28 => p_dei_attribute28
2655 ,p_dei_attribute29 => p_dei_attribute29
2656 ,p_dei_attribute30 => p_dei_attribute30
2657 ,p_dei_information_category => p_dei_information_category
2658 ,p_dei_information1 => p_dei_information1
2659 ,p_dei_information2 => p_dei_information2
2660 ,p_dei_information3 => p_dei_information3
2661 ,p_dei_information4 => p_dei_information4
2662 ,p_dei_information5 => p_dei_information5
2663 ,p_dei_information6 => p_dei_information6
2664 ,p_dei_information7 => p_dei_information7
2665 ,p_dei_information8 => p_dei_information8
2666 ,p_dei_information9 => p_dei_information9
2667 ,p_dei_information10 => p_dei_information10
2668 ,p_dei_information11 => p_dei_information11
2669 ,p_dei_information12 => p_dei_information12
2670 ,p_dei_information13 => p_dei_information13
2671 ,p_dei_information14 => p_dei_information14
2672 ,p_dei_information15 => p_dei_information15
2673 ,p_dei_information16 => p_dei_information16
2674 ,p_dei_information17 => p_dei_information17
2675 ,p_dei_information18 => p_dei_information18
2676 ,p_dei_information19 => p_dei_information19
2677 ,p_dei_information20 => p_dei_information20
2678 ,p_dei_information21 => p_dei_information21
2679 ,p_dei_information22 => p_dei_information22
2680 ,p_dei_information23 => p_dei_information23
2681 ,p_dei_information24 => p_dei_information24
2682 ,p_dei_information25 => p_dei_information25
2683 ,p_dei_information26 => p_dei_information26
2684 ,p_dei_information27 => p_dei_information27
2685 ,p_dei_information28 => p_dei_information28
2686 ,p_dei_information29 => p_dei_information29
2687 ,p_dei_information30 => p_dei_information30
2688 ,p_request_id => p_request_id
2689 ,p_program_application_id => p_program_application_id
2690 ,p_program_id => p_program_id
2691 ,p_program_update_date => p_program_update_date
2692 ,p_document_extra_info_id => p_document_extra_info_id
2693 ,p_object_version_number => p_object_version_number
2694 ,p_return_status => p_return_status
2695 );
2696
2697 elsif p_action_mode ='DOR_UPDATE' then
2698
2699 hr_document_extra_info_swi.update_doc_extra_info
2700 (p_validate => p_validate
2701 ,p_person_id => p_person_id
2702 ,p_document_type_id => p_document_type_id
2703 ,p_date_from => p_date_from
2704 ,p_date_to => p_date_to
2705 ,p_document_number => p_document_number
2706 ,p_issued_by => p_issued_by
2707 ,p_issued_at => p_issued_at
2708 ,p_issued_date => p_issued_date
2709 ,p_issuing_authority => p_issuing_authority
2710 ,p_verified_by => p_verified_by
2711 ,p_verified_date => p_verified_date
2712 ,p_related_object_name => p_related_object_name
2713 ,p_related_object_id_col => p_related_object_id_col
2714 ,p_related_object_id => p_related_object_id
2715 ,p_dei_attribute_category => p_dei_attribute_category
2716 ,p_dei_attribute1 => p_dei_attribute1
2717 ,p_dei_attribute2 => p_dei_attribute2
2718 ,p_dei_attribute3 => p_dei_attribute3
2719 ,p_dei_attribute4 => p_dei_attribute4
2720 ,p_dei_attribute5 => p_dei_attribute5
2721 ,p_dei_attribute6 => p_dei_attribute6
2722 ,p_dei_attribute7 => p_dei_attribute7
2723 ,p_dei_attribute8 => p_dei_attribute8
2724 ,p_dei_attribute9 => p_dei_attribute9
2725 ,p_dei_attribute10 => p_dei_attribute10
2726 ,p_dei_attribute11 => p_dei_attribute11
2727 ,p_dei_attribute12 => p_dei_attribute12
2728 ,p_dei_attribute13 => p_dei_attribute13
2729 ,p_dei_attribute14 => p_dei_attribute14
2730 ,p_dei_attribute15 => p_dei_attribute15
2731 ,p_dei_attribute16 => p_dei_attribute16
2732 ,p_dei_attribute17 => p_dei_attribute17
2733 ,p_dei_attribute18 => p_dei_attribute18
2734 ,p_dei_attribute19 => p_dei_attribute19
2735 ,p_dei_attribute20 => p_dei_attribute20
2736 ,p_dei_attribute21 => p_dei_attribute21
2737 ,p_dei_attribute22 => p_dei_attribute22
2738 ,p_dei_attribute23 => p_dei_attribute23
2739 ,p_dei_attribute24 => p_dei_attribute24
2740 ,p_dei_attribute25 => p_dei_attribute25
2741 ,p_dei_attribute26 => p_dei_attribute26
2742 ,p_dei_attribute27 => p_dei_attribute27
2743 ,p_dei_attribute28 => p_dei_attribute28
2744 ,p_dei_attribute29 => p_dei_attribute29
2745 ,p_dei_attribute30 => p_dei_attribute30
2746 ,p_dei_information_category => p_dei_information_category
2747 ,p_dei_information1 => p_dei_information1
2748 ,p_dei_information2 => p_dei_information2
2749 ,p_dei_information3 => p_dei_information3
2750 ,p_dei_information4 => p_dei_information4
2751 ,p_dei_information5 => p_dei_information5
2752 ,p_dei_information6 => p_dei_information6
2753 ,p_dei_information7 => p_dei_information7
2754 ,p_dei_information8 => p_dei_information8
2755 ,p_dei_information9 => p_dei_information9
2756 ,p_dei_information10 => p_dei_information10
2757 ,p_dei_information11 => p_dei_information11
2758 ,p_dei_information12 => p_dei_information12
2759 ,p_dei_information13 => p_dei_information13
2760 ,p_dei_information14 => p_dei_information14
2761 ,p_dei_information15 => p_dei_information15
2762 ,p_dei_information16 => p_dei_information16
2763 ,p_dei_information17 => p_dei_information17
2764 ,p_dei_information18 => p_dei_information18
2765 ,p_dei_information19 => p_dei_information19
2766 ,p_dei_information20 => p_dei_information20
2767 ,p_dei_information21 => p_dei_information21
2768 ,p_dei_information22 => p_dei_information22
2769 ,p_dei_information23 => p_dei_information23
2770 ,p_dei_information24 => p_dei_information24
2771 ,p_dei_information25 => p_dei_information25
2772 ,p_dei_information26 => p_dei_information26
2773 ,p_dei_information27 => p_dei_information27
2774 ,p_dei_information28 => p_dei_information28
2775 ,p_dei_information29 => p_dei_information29
2776 ,p_dei_information30 => p_dei_information30
2777 ,p_request_id => p_request_id
2778 ,p_program_application_id => p_program_application_id
2779 ,p_program_id => p_program_id
2780 ,p_program_update_date => p_program_update_date
2781 ,p_document_extra_info_id => p_document_extra_info_id
2782 ,p_object_version_number => p_object_version_number
2783 ,p_return_status => p_return_status
2784 );
2785
2786 end if;
2787
2788
2789 ----Validating the document information in transaction table--------
2790
2791 --FIX FOR BUG 15835786
2792 open csr_chk_duplicate_txn(
2793 p_document_type_id => p_document_type_id,
2794 p_person_id=> p_person_id ,
2795 p_date_from => p_date_from,
2796 p_date_to=> p_date_to,
2797 p_document_number=>p_document_number);
2798
2799 open csr_chk_doc_number(
2800 p_document_type_id => p_document_type_id,
2801 p_person_id=> p_person_id ,
2802 p_document_number=>p_document_number);
2803 --FIX FOR BUG 15835786
2804 fetch csr_chk_duplicate_txn into l_num;
2805 if l_num > 0 then
2806 close csr_chk_duplicate_txn;
2807
2808 select document_type into l_document_type
2809 from hr_document_types_v
2810 where document_type_id = p_document_type_id;
2811
2812 hr_utility.set_message(800, 'HR_449708_DOR_UNQ_PER_DOC');
2813 hr_utility.set_message_token('TYPE', l_document_type);
2814 hr_utility.set_message_token('DATE_FROM', p_date_from);
2815 hr_utility.set_message_token('DATE_TO', p_date_to);
2816 p_return_status := hr_utility.hr_error_number||hr_utility.get_message;
2817
2818 else
2819 close csr_chk_duplicate_txn;
2820 --fix FOR BUG 15835786
2821 fetch csr_chk_doc_number into l_num1;
2822 if l_num1 > 0 then
2823 close csr_chk_doc_number;
2824 hr_utility.set_message(800, 'HR_449708_DOR_UNQ_PER_DOC');
2825 hr_utility.set_message_token('TYPE', p_document_number);
2826 hr_utility.set_message_token('DATE_FROM', p_date_from);
2827 hr_utility.set_message_token('DATE_TO', p_date_to);
2828 p_return_status := hr_utility.hr_error_number||hr_utility.get_message;
2829
2830 else
2831 close csr_chk_doc_number;
2832
2833 end if;
2834 --FIX FOR BUG 15835786
2835 end if;
2836
2837 if p_return_status = 'E' then
2838 hr_utility.raise_error;
2839 end if;
2840
2841
2842 EXCEPTION
2843
2844 WHEN g_data_error THEN
2845 hr_utility.trace('Exception in g_data_error in hr_dor_review_ss.validate_dor ' || SQLERRM );
2846 hr_utility.set_location(' Leaving:' || l_proc,555);
2847 p_return_status := SQLERRM;
2848 raise;
2849
2850 WHEN hr_utility.hr_error THEN
2851 hr_utility.get_message_details(msg_name,msg_appl);
2852 fnd_message.set_name(msg_appl,msg_name);
2853 p_return_status := hr_utility.get_message;
2854 hr_utility.set_location(p_return_status,566);
2855
2856 WHEN OTHERS THEN
2857 hr_utility.trace('When others exception in hr_dor_review_ss.validate_dor ' || SQLERRM );
2858 hr_utility.set_location(' Leaving:' || l_proc,560);
2859 p_return_status := SQLERRM;
2860 raise;
2861
2862
2863
2864
2865
2866 END validate_dor ;
2867
2868 --------------------------------------------------------------------------
2869 --------------------------get_review_data_from_tt-------------------------
2870 ------This method retrieve the documents of records data from the --------
2871 ------transaction table---------------------------------------------------
2872 --------------------------------------------------------------------------
2873
2874
2875 PROCEDURE get_review_data_from_tt(
2876 p_transaction_step_id in number
2877 ,p_dor_rec out nocopy HR_DOCUMENT_EXTRA_INFO%rowtype)
2878 IS
2879
2880 l_proc varchar2(72) := g_package ||'get_review_data_from_tt';
2881
2882 BEGIN
2883
2884 hr_utility.set_location(' Entering:' || l_proc,60);
2885
2886 p_dor_rec.person_id :=
2887 hr_transaction_api.get_number_value
2888 (p_transaction_step_id => p_transaction_step_id
2889 ,p_name => 'P_PERSON_ID');
2890
2891 p_dor_rec.document_type_id :=
2892 hr_transaction_api.get_number_value
2893 (p_transaction_step_id => p_transaction_step_id
2894 ,p_name => 'P_DOCUMENT_TYPE_ID');
2895
2896 p_dor_rec.date_from :=
2897 hr_transaction_api.get_date_value
2898 (p_transaction_step_id => p_transaction_step_id
2899 ,p_name => 'P_DATE_FROM');
2900
2901 p_dor_rec.date_to :=
2902 hr_transaction_api.get_date_value
2903 (p_transaction_step_id => p_transaction_step_id
2904 ,p_name => 'P_DATE_TO');
2905
2906 p_dor_rec.document_number :=
2907 hr_transaction_api.get_varchar2_value
2908 (p_transaction_step_id => p_transaction_step_id
2909 ,p_name => 'P_DOCUMENT_NUMBER');
2910
2911 p_dor_rec.issued_by :=
2912 hr_transaction_api.get_varchar2_value
2913 (p_transaction_step_id => p_transaction_step_id
2914 ,p_name => 'P_ISSUED_BY');
2915
2916 p_dor_rec.issued_at :=
2917 hr_transaction_api.get_varchar2_value
2918 (p_transaction_step_id => p_transaction_step_id
2919 ,p_name => 'P_ISSUED_AT');
2920
2921 p_dor_rec.issued_date :=
2922 hr_transaction_api.get_date_value
2923 (p_transaction_step_id => p_transaction_step_id
2924 ,p_name => 'P_ISSUED_DATE');
2925
2926 p_dor_rec.issuing_authority :=
2927 hr_transaction_api.get_varchar2_value
2928 (p_transaction_step_id => p_transaction_step_id
2929 ,p_name => 'P_ISSUING_AUTHORITY');
2930
2931 p_dor_rec.verified_by :=
2932 hr_transaction_api.get_number_value
2933 (p_transaction_step_id => p_transaction_step_id
2934 ,p_name => 'P_VERIFIED_BY');
2935
2936 p_dor_rec.verified_date :=
2937 hr_transaction_api.get_date_value
2938 (p_transaction_step_id => p_transaction_step_id
2939 ,p_name => 'P_VERIFIED_DATE');
2940
2941 p_dor_rec.related_object_name :=
2942 hr_transaction_api.get_varchar2_value
2943 (p_transaction_step_id => p_transaction_step_id
2944 ,p_name => 'P_RELATED_OBJECT_NAME');
2945
2946 p_dor_rec.related_object_id_col :=
2947 hr_transaction_api.get_varchar2_value
2948 (p_transaction_step_id => p_transaction_step_id
2949 ,p_name => 'P_RELATED_OBJECT_ID_COL');
2950
2951 p_dor_rec.related_object_id :=
2952 hr_transaction_api.get_number_value
2953 (p_transaction_step_id => p_transaction_step_id
2954 ,p_name => 'P_RELATED_OBJECT_ID');
2955
2956 p_dor_rec.dei_attribute_category :=
2957 hr_transaction_api.get_varchar2_value
2958 (p_transaction_step_id => p_transaction_step_id
2959 ,p_name => 'P_DEI_ATTRIBUTE_CATEGORY');
2960
2961 p_dor_rec.dei_attribute1 :=
2962 hr_transaction_api.get_varchar2_value
2963 (p_transaction_step_id => p_transaction_step_id
2964 ,p_name => 'P_DEI_ATTRIBUTE1');
2965
2966 p_dor_rec.dei_attribute2 :=
2967 hr_transaction_api.get_varchar2_value
2968 (p_transaction_step_id => p_transaction_step_id
2969 ,p_name => 'P_DEI_ATTRIBUTE2');
2970
2971 p_dor_rec.dei_attribute3 :=
2972 hr_transaction_api.get_varchar2_value
2973 (p_transaction_step_id => p_transaction_step_id
2974 ,p_name => 'P_DEI_ATTRIBUTE3');
2975
2976 p_dor_rec.dei_attribute4 :=
2977 hr_transaction_api.get_varchar2_value
2978 (p_transaction_step_id => p_transaction_step_id
2979 ,p_name => 'P_DEI_ATTRIBUTE4');
2980
2981 p_dor_rec.dei_attribute4 :=
2982 hr_transaction_api.get_varchar2_value
2983 (p_transaction_step_id => p_transaction_step_id
2984 ,p_name => 'P_DEI_ATTRIBUTE5');
2985
2986 p_dor_rec.dei_attribute6 :=
2987 hr_transaction_api.get_varchar2_value
2988 (p_transaction_step_id => p_transaction_step_id
2989 ,p_name => 'P_DEI_ATTRIBUTE6');
2990
2991 p_dor_rec.dei_attribute7 :=
2992 hr_transaction_api.get_varchar2_value
2993 (p_transaction_step_id => p_transaction_step_id
2994 ,p_name => 'P_DEI_ATTRIBUTE7');
2995
2996 p_dor_rec.dei_attribute8 :=
2997 hr_transaction_api.get_varchar2_value
2998 (p_transaction_step_id => p_transaction_step_id
2999 ,p_name => 'P_DEI_ATTRIBUTE8');
3000
3001 p_dor_rec.dei_attribute9 :=
3002 hr_transaction_api.get_varchar2_value
3003 (p_transaction_step_id => p_transaction_step_id
3004 ,p_name => 'P_DEI_ATTRIBUTE9');
3005
3006 p_dor_rec.dei_attribute10 :=
3007 hr_transaction_api.get_varchar2_value
3008 (p_transaction_step_id => p_transaction_step_id
3009 ,p_name => 'P_DEI_ATTRIBUTE10');
3010
3011 p_dor_rec.dei_attribute11 :=
3012 hr_transaction_api.get_varchar2_value
3013 (p_transaction_step_id => p_transaction_step_id
3014 ,p_name => 'P_DEI_ATTRIBUTE11');
3015
3016 p_dor_rec.dei_attribute12 :=
3017 hr_transaction_api.get_varchar2_value
3018 (p_transaction_step_id => p_transaction_step_id
3019 ,p_name => 'P_DEI_ATTRIBUTE12');
3020
3021 p_dor_rec.dei_attribute13 :=
3022 hr_transaction_api.get_varchar2_value
3023 (p_transaction_step_id => p_transaction_step_id
3024 ,p_name => 'P_DEI_ATTRIBUTE13');
3025
3026 p_dor_rec.dei_attribute14 :=
3027 hr_transaction_api.get_varchar2_value
3028 (p_transaction_step_id => p_transaction_step_id
3029 ,p_name => 'P_DEI_ATTRIBUTE14');
3030
3031 p_dor_rec.dei_attribute15 :=
3032 hr_transaction_api.get_varchar2_value
3033 (p_transaction_step_id => p_transaction_step_id
3034 ,p_name => 'P_DEI_ATTRIBUTE15');
3035
3036 p_dor_rec.dei_attribute16 :=
3037 hr_transaction_api.get_varchar2_value
3038 (p_transaction_step_id => p_transaction_step_id
3039 ,p_name => 'P_DEI_ATTRIBUTE16');
3040
3041 p_dor_rec.dei_attribute17 :=
3042 hr_transaction_api.get_varchar2_value
3043 (p_transaction_step_id => p_transaction_step_id
3044 ,p_name => 'P_DEI_ATTRIBUTE17');
3045
3046 p_dor_rec.dei_attribute18 :=
3047 hr_transaction_api.get_varchar2_value
3048 (p_transaction_step_id => p_transaction_step_id
3049 ,p_name => 'P_DEI_ATTRIBUTE18');
3050
3051 p_dor_rec.dei_attribute19 :=
3052 hr_transaction_api.get_varchar2_value
3053 (p_transaction_step_id => p_transaction_step_id
3054 ,p_name => 'P_DEI_ATTRIBUTE19');
3055
3056 p_dor_rec.dei_attribute20 :=
3057 hr_transaction_api.get_varchar2_value
3058 (p_transaction_step_id => p_transaction_step_id
3059 ,p_name => 'P_DEI_ATTRIBUTE20');
3060
3061 p_dor_rec.dei_attribute21 :=
3062 hr_transaction_api.get_varchar2_value
3063 (p_transaction_step_id => p_transaction_step_id
3064 ,p_name => 'P_DEI_ATTRIBUTE21');
3065
3066 p_dor_rec.dei_attribute22 :=
3067 hr_transaction_api.get_varchar2_value
3068 (p_transaction_step_id => p_transaction_step_id
3069 ,p_name => 'P_DEI_ATTRIBUTE22');
3070
3071 p_dor_rec.dei_attribute23 :=
3072 hr_transaction_api.get_varchar2_value
3073 (p_transaction_step_id => p_transaction_step_id
3074 ,p_name => 'P_DEI_ATTRIBUTE23');
3075
3076 p_dor_rec.dei_attribute24 :=
3077 hr_transaction_api.get_varchar2_value
3078 (p_transaction_step_id => p_transaction_step_id
3079 ,p_name => 'P_DEI_ATTRIBUTE24');
3080
3081 p_dor_rec.dei_attribute25 :=
3082 hr_transaction_api.get_varchar2_value
3083 (p_transaction_step_id => p_transaction_step_id
3084 ,p_name => 'P_DEI_ATTRIBUTE25');
3085
3086 p_dor_rec.dei_attribute26 :=
3087 hr_transaction_api.get_varchar2_value
3088 (p_transaction_step_id => p_transaction_step_id
3089 ,p_name => 'P_DEI_ATTRIBUTE26');
3090
3091 p_dor_rec.dei_attribute27 :=
3092 hr_transaction_api.get_varchar2_value
3093 (p_transaction_step_id => p_transaction_step_id
3094 ,p_name => 'P_DEI_ATTRIBUTE27');
3095
3096 p_dor_rec.dei_attribute28 :=
3097 hr_transaction_api.get_varchar2_value
3098 (p_transaction_step_id => p_transaction_step_id
3099 ,p_name => 'P_DEI_ATTRIBUTE28');
3100
3101 p_dor_rec.dei_attribute29 :=
3102 hr_transaction_api.get_varchar2_value
3103 (p_transaction_step_id => p_transaction_step_id
3104 ,p_name => 'P_DEI_ATTRIBUTE29');
3105
3106 p_dor_rec.dei_attribute30 :=
3107 hr_transaction_api.get_varchar2_value
3108 (p_transaction_step_id => p_transaction_step_id
3109 ,p_name => 'P_DEI_ATTRIBUTE30');
3110
3111 p_dor_rec.dei_information_category :=
3112 hr_transaction_api.get_varchar2_value
3113 (p_transaction_step_id => p_transaction_step_id
3114 ,p_name => 'P_DEI_INFORMATION_CATEGORY');
3115
3116 p_dor_rec.dei_information1 :=
3117 hr_transaction_api.get_varchar2_value
3118 (p_transaction_step_id => p_transaction_step_id
3119 ,p_name => 'P_DEI_INFORMATION1');
3120
3121 p_dor_rec.dei_information2 :=
3122 hr_transaction_api.get_varchar2_value
3123 (p_transaction_step_id => p_transaction_step_id
3124 ,p_name => 'P_DEI_INFORMATION2');
3125
3126 p_dor_rec.dei_information3 :=
3127 hr_transaction_api.get_varchar2_value
3128 (p_transaction_step_id => p_transaction_step_id
3129 ,p_name => 'P_DEI_INFORMATION3');
3130
3131 p_dor_rec.dei_information4 :=
3132 hr_transaction_api.get_varchar2_value
3133 (p_transaction_step_id => p_transaction_step_id
3134 ,p_name => 'P_DEI_INFORMATION4');
3135
3136 p_dor_rec.dei_information5 :=
3137 hr_transaction_api.get_varchar2_value
3138 (p_transaction_step_id => p_transaction_step_id
3139 ,p_name => 'P_DEI_INFORMATION5');
3140
3141 p_dor_rec.dei_information6 :=
3142 hr_transaction_api.get_varchar2_value
3143 (p_transaction_step_id => p_transaction_step_id
3144 ,p_name => 'P_DEI_INFORMATION6');
3145
3146 p_dor_rec.dei_information7 :=
3147 hr_transaction_api.get_varchar2_value
3148 (p_transaction_step_id => p_transaction_step_id
3149 ,p_name => 'P_DEI_INFORMATION7');
3150
3151 p_dor_rec.dei_information8 :=
3152 hr_transaction_api.get_varchar2_value
3153 (p_transaction_step_id => p_transaction_step_id
3154 ,p_name => 'P_DEI_INFORMATION8');
3155
3156 p_dor_rec.dei_information9 :=
3157 hr_transaction_api.get_varchar2_value
3158 (p_transaction_step_id => p_transaction_step_id
3159 ,p_name => 'P_DEI_INFORMATION9');
3160
3161 p_dor_rec.dei_information10 :=
3162 hr_transaction_api.get_varchar2_value
3163 (p_transaction_step_id => p_transaction_step_id
3164 ,p_name => 'P_DEI_INFORMATION10');
3165
3166 p_dor_rec.dei_information11 :=
3167 hr_transaction_api.get_varchar2_value
3168 (p_transaction_step_id => p_transaction_step_id
3169 ,p_name => 'P_DEI_INFORMATION11');
3170
3171 p_dor_rec.dei_information12 :=
3172 hr_transaction_api.get_varchar2_value
3173 (p_transaction_step_id => p_transaction_step_id
3174 ,p_name => 'P_DEI_INFORMATION12');
3175
3176 p_dor_rec.dei_information13 :=
3177 hr_transaction_api.get_varchar2_value
3178 (p_transaction_step_id => p_transaction_step_id
3179 ,p_name => 'P_DEI_INFORMATION13');
3180
3181 p_dor_rec.dei_information14 :=
3182 hr_transaction_api.get_varchar2_value
3183 (p_transaction_step_id => p_transaction_step_id
3184 ,p_name => 'P_DEI_INFORMATION14');
3185
3186 p_dor_rec.dei_information15 :=
3187 hr_transaction_api.get_varchar2_value
3188 (p_transaction_step_id => p_transaction_step_id
3189 ,p_name => 'P_DEI_INFORMATION15');
3190
3191 p_dor_rec.dei_information16 :=
3192 hr_transaction_api.get_varchar2_value
3193 (p_transaction_step_id => p_transaction_step_id
3194 ,p_name => 'P_DEI_INFORMATION16');
3195
3196 p_dor_rec.dei_information17 :=
3197 hr_transaction_api.get_varchar2_value
3198 (p_transaction_step_id => p_transaction_step_id
3199 ,p_name => 'P_DEI_INFORMATION17');
3200
3201 p_dor_rec.dei_information18 :=
3202 hr_transaction_api.get_varchar2_value
3203 (p_transaction_step_id => p_transaction_step_id
3204 ,p_name => 'P_DEI_INFORMATION18');
3205
3206 p_dor_rec.dei_information19 :=
3207 hr_transaction_api.get_varchar2_value
3208 (p_transaction_step_id => p_transaction_step_id
3209 ,p_name => 'P_DEI_INFORMATION19');
3210
3211 p_dor_rec.dei_information20 :=
3212 hr_transaction_api.get_varchar2_value
3213 (p_transaction_step_id => p_transaction_step_id
3214 ,p_name => 'P_DEI_INFORMATION20');
3215
3216 p_dor_rec.dei_information21 :=
3217 hr_transaction_api.get_varchar2_value
3218 (p_transaction_step_id => p_transaction_step_id
3219 ,p_name => 'P_DEI_INFORMATION21');
3220
3221 p_dor_rec.dei_information22 :=
3222 hr_transaction_api.get_varchar2_value
3223 (p_transaction_step_id => p_transaction_step_id
3224 ,p_name => 'P_DEI_INFORMATION22');
3225
3226 p_dor_rec.dei_information23 :=
3227 hr_transaction_api.get_varchar2_value
3228 (p_transaction_step_id => p_transaction_step_id
3229 ,p_name => 'P_DEI_INFORMATION23');
3230
3231 p_dor_rec.dei_information24 :=
3232 hr_transaction_api.get_varchar2_value
3233 (p_transaction_step_id => p_transaction_step_id
3234 ,p_name => 'P_DEI_INFORMATION24');
3235
3236 p_dor_rec.dei_information25 :=
3237 hr_transaction_api.get_varchar2_value
3238 (p_transaction_step_id => p_transaction_step_id
3239 ,p_name => 'P_DEI_INFORMATION25');
3240
3241 p_dor_rec.dei_information26 :=
3242 hr_transaction_api.get_varchar2_value
3243 (p_transaction_step_id => p_transaction_step_id
3244 ,p_name => 'P_DEI_INFORMATION26');
3245
3246 p_dor_rec.dei_information27 :=
3247 hr_transaction_api.get_varchar2_value
3248 (p_transaction_step_id => p_transaction_step_id
3249 ,p_name => 'P_DEI_INFORMATION27');
3250
3251 p_dor_rec.dei_information28 :=
3252 hr_transaction_api.get_varchar2_value
3253 (p_transaction_step_id => p_transaction_step_id
3254 ,p_name => 'P_DEI_INFORMATION28');
3255
3256 p_dor_rec.dei_information29 :=
3257 hr_transaction_api.get_varchar2_value
3258 (p_transaction_step_id => p_transaction_step_id
3259 ,p_name => 'P_DEI_INFORMATION29');
3260
3261 p_dor_rec.dei_information30 :=
3262 hr_transaction_api.get_varchar2_value
3263 (p_transaction_step_id => p_transaction_step_id
3264 ,p_name => 'P_DEI_INFORMATION30');
3265
3266 p_dor_rec.request_id :=
3267 hr_transaction_api.get_number_value
3268 (p_transaction_step_id => p_transaction_step_id
3269 ,p_name => 'P_REQUEST_ID');
3270
3271 p_dor_rec.program_application_id :=
3272 hr_transaction_api.get_number_value
3273 (p_transaction_step_id => p_transaction_step_id
3274 ,p_name => 'P_PROGRAM_APPLICATION_ID');
3275
3276 p_dor_rec.program_id :=
3277 hr_transaction_api.get_number_value
3278 (p_transaction_step_id => p_transaction_step_id
3279 ,p_name => 'P_PROGRAM_ID');
3280
3281 p_dor_rec.program_update_date :=
3282 hr_transaction_api.get_date_value
3283 (p_transaction_step_id => p_transaction_step_id
3284 ,p_name => 'P_PROGRAM_UPDATE_DATE');
3285
3286 p_dor_rec.document_extra_info_id :=
3287 hr_transaction_api.get_number_value
3288 (p_transaction_step_id => p_transaction_step_id
3289 ,p_name => 'P_DOCUMENT_EXTRA_INFO_ID');
3290
3291 p_dor_rec.object_version_number :=
3292 hr_transaction_api.get_number_value
3293 (p_transaction_step_id => p_transaction_step_id
3294 ,p_name => 'P_OBJECT_VERSION_NUMBER');
3295
3296 EXCEPTION
3297
3298 WHEN g_data_error THEN
3299 hr_utility.trace('Exception in g_data_error in '||l_proc||','|| SQLERRM );
3300 hr_utility.set_location(' Leaving:' || l_proc,666);
3301 raise;
3302
3303 WHEN OTHERS THEN
3304 hr_utility.trace('When others exception in ' ||l_proc||','|| SQLERRM );
3305 hr_utility.set_location(' Leaving:' || l_proc,660);
3306 raise ;
3307
3308
3309 END get_review_data_from_tt;
3310
3311
3312 --------------------------------------------------------------------------
3313 --------------------------get_transaction_values--------------------------
3314 ------This method retrieve the documents of records data from the --------
3315 ------transaction table and return back the values.-----------------------
3316 --------------------------------------------------------------------------
3317
3318 PROCEDURE get_transaction_values(
3319 p_transaction_step_id in varchar2
3320 ,p_person_id out nocopy varchar2
3321 ,p_document_extra_info_id out nocopy varchar2
3322 ,p_document_type_id out nocopy varchar2
3323 ,p_date_from out nocopy varchar2
3324 ,p_date_to out nocopy varchar2
3325 ,p_document_number out nocopy varchar2
3326 ,p_issued_by out nocopy varchar2
3327 ,p_issued_at out nocopy varchar2
3328 ,p_issued_date out nocopy varchar2
3329 ,p_issuing_authority out nocopy varchar2
3330 ,p_verified_by out nocopy varchar2
3331 ,p_verified_date out nocopy varchar2
3332 ,p_related_object_name out nocopy varchar2
3333 ,p_related_object_id_col out nocopy varchar2
3334 ,p_related_object_id out nocopy varchar2
3335 ,p_dei_attribute_category out nocopy varchar2
3336 ,p_dei_attribute1 out nocopy varchar2
3337 ,p_dei_attribute2 out nocopy varchar2
3338 ,p_dei_attribute3 out nocopy varchar2
3339 ,p_dei_attribute4 out nocopy varchar2
3340 ,p_dei_attribute5 out nocopy varchar2
3341 ,p_dei_attribute6 out nocopy varchar2
3342 ,p_dei_attribute7 out nocopy varchar2
3343 ,p_dei_attribute8 out nocopy varchar2
3344 ,p_dei_attribute9 out nocopy varchar2
3345 ,p_dei_attribute10 out nocopy varchar2
3346 ,p_dei_attribute11 out nocopy varchar2
3347 ,p_dei_attribute12 out nocopy varchar2
3348 ,p_dei_attribute13 out nocopy varchar2
3349 ,p_dei_attribute14 out nocopy varchar2
3350 ,p_dei_attribute15 out nocopy varchar2
3351 ,p_dei_attribute16 out nocopy varchar2
3352 ,p_dei_attribute17 out nocopy varchar2
3353 ,p_dei_attribute18 out nocopy varchar2
3354 ,p_dei_attribute19 out nocopy varchar2
3355 ,p_dei_attribute20 out nocopy varchar2
3356 ,p_dei_attribute21 out nocopy varchar2
3357 ,p_dei_attribute22 out nocopy varchar2
3358 ,p_dei_attribute23 out nocopy varchar2
3359 ,p_dei_attribute24 out nocopy varchar2
3360 ,p_dei_attribute25 out nocopy varchar2
3361 ,p_dei_attribute26 out nocopy varchar2
3362 ,p_dei_attribute27 out nocopy varchar2
3363 ,p_dei_attribute28 out nocopy varchar2
3364 ,p_dei_attribute29 out nocopy varchar2
3365 ,p_dei_attribute30 out nocopy varchar2
3366 ,p_dei_information_category out nocopy varchar2
3367 ,p_dei_information1 out nocopy varchar2
3368 ,p_dei_information2 out nocopy varchar2
3369 ,p_dei_information3 out nocopy varchar2
3370 ,p_dei_information4 out nocopy varchar2
3371 ,p_dei_information5 out nocopy varchar2
3372 ,p_dei_information6 out nocopy varchar2
3373 ,p_dei_information7 out nocopy varchar2
3374 ,p_dei_information8 out nocopy varchar2
3375 ,p_dei_information9 out nocopy varchar2
3376 ,p_dei_information10 out nocopy varchar2
3377 ,p_dei_information11 out nocopy varchar2
3378 ,p_dei_information12 out nocopy varchar2
3379 ,p_dei_information13 out nocopy varchar2
3380 ,p_dei_information14 out nocopy varchar2
3381 ,p_dei_information15 out nocopy varchar2
3382 ,p_dei_information16 out nocopy varchar2
3383 ,p_dei_information17 out nocopy varchar2
3384 ,p_dei_information18 out nocopy varchar2
3385 ,p_dei_information19 out nocopy varchar2
3386 ,p_dei_information20 out nocopy varchar2
3387 ,p_dei_information21 out nocopy varchar2
3388 ,p_dei_information22 out nocopy varchar2
3389 ,p_dei_information23 out nocopy varchar2
3390 ,p_dei_information24 out nocopy varchar2
3391 ,p_dei_information25 out nocopy varchar2
3392 ,p_dei_information26 out nocopy varchar2
3393 ,p_dei_information27 out nocopy varchar2
3394 ,p_dei_information28 out nocopy varchar2
3395 ,p_dei_information29 out nocopy varchar2
3396 ,p_dei_information30 out nocopy varchar2
3397 ,p_request_id out nocopy varchar2
3398 ,p_program_application_id out nocopy varchar2
3399 ,p_program_id out nocopy varchar2
3400 ,p_program_update_date out nocopy varchar2
3401 ,p_object_version_number out nocopy varchar2
3402 ,p_return_status out nocopy varchar2
3403 ,p_document_type out nocopy varchar2
3404 ,p_category_name out nocopy varchar2
3405 ,p_sub_category_name out nocopy varchar2
3406 ,p_country_name out nocopy varchar2
3407 ,p_system_doc_type out nocopy varchar2
3408 ) IS
3409
3410 l_proc varchar2(72) := g_package ||'.get_transaction_values';
3411 p_dor_rec hr_document_extra_info%rowtype;
3412 l_country_name varchar2(150);
3413 l_document_type varchar2(150);
3414 l_category_name varchar2(150);
3415 l_sub_category_name varchar2(150);
3416 l_system_doc_type varchar2(150);
3417 l_legislation_code varchar2(10);
3418 p_transaction_id number;
3419 msg_name varchar2(1000);
3420 msg_appl varchar2(10);
3421 l_status varchar2(10);
3422
3423 BEGIN
3424
3425 --hr_utility.trace_on(null,'ORCL1');
3426 hr_utility.set_location(' Entering:' || l_proc,70);
3427 p_transaction_id := get_transaction_id(p_transaction_step_id);
3428
3429 begin
3430 select status into l_status
3431 from hr_api_transactions where
3432 transaction_id=p_transaction_id;
3433
3434 exception
3435 when NO_DATA_FOUND then
3436 l_status := '';
3437 end;
3438
3439 --Retrieving the data---------
3440
3441 hr_dor_review_ss.get_review_data_from_tt(
3442 p_transaction_step_id => p_transaction_step_id,
3443 p_dor_rec => p_dor_rec);
3444
3445
3446 ---Assigning values to all parameters
3447
3448 p_person_id := TO_CHAR(p_dor_rec.person_id);
3449 p_document_extra_info_id := TO_CHAR(p_dor_rec.document_extra_info_id);
3450 p_document_type_id := TO_CHAR(p_dor_rec.document_type_id);
3451 p_date_from := TO_CHAR(p_dor_rec.date_from,'dd-mm-rrrr');
3452 p_date_to := TO_CHAR(p_dor_rec.date_to,'dd-mm-rrrr');
3453 p_document_number := p_dor_rec.document_number;
3454 p_issued_by := p_dor_rec.issued_by;
3455 p_issued_at := p_dor_rec.issued_at;
3456 p_issued_date := TO_CHAR(p_dor_rec.issued_date,'dd-mm-rrrr');
3457 p_issuing_authority := p_dor_rec.issuing_authority;
3458 p_verified_by := TO_CHAR(p_dor_rec.verified_by);
3459 p_verified_date := TO_CHAR(p_dor_rec.verified_date,'dd-mm-rrrr');
3460 p_related_object_name := p_dor_rec.related_object_name;
3461 p_related_object_id_col := p_dor_rec.related_object_id_col;
3462 p_related_object_id := TO_CHAR(p_dor_rec.related_object_id);
3463 p_dei_attribute_category := p_dor_rec.dei_attribute_category;
3464 p_dei_attribute1 := p_dor_rec.dei_attribute1;
3465 p_dei_attribute2 := p_dor_rec.dei_attribute2;
3466 p_dei_attribute3 := p_dor_rec.dei_attribute3;
3467 p_dei_attribute4 := p_dor_rec.dei_attribute4;
3468 p_dei_attribute5 := p_dor_rec.dei_attribute5;
3469 p_dei_attribute6 := p_dor_rec.dei_attribute6;
3470 p_dei_attribute7 := p_dor_rec.dei_attribute7;
3471 p_dei_attribute8 := p_dor_rec.dei_attribute8;
3472 p_dei_attribute9 := p_dor_rec.dei_attribute9;
3473 p_dei_attribute10 := p_dor_rec.dei_attribute10;
3474 p_dei_attribute11 := p_dor_rec.dei_attribute11;
3475 p_dei_attribute12 := p_dor_rec.dei_attribute12;
3476 p_dei_attribute13 := p_dor_rec.dei_attribute13;
3477 p_dei_attribute14 := p_dor_rec.dei_attribute14;
3478 p_dei_attribute15 := p_dor_rec.dei_attribute15;
3479 p_dei_attribute16 := p_dor_rec.dei_attribute16;
3480 p_dei_attribute17 := p_dor_rec.dei_attribute17;
3481 p_dei_attribute18 := p_dor_rec.dei_attribute18;
3482 p_dei_attribute19 := p_dor_rec.dei_attribute19;
3483 p_dei_attribute20 := p_dor_rec.dei_attribute20;
3484 p_dei_attribute21 := p_dor_rec.dei_attribute21;
3485 p_dei_attribute22 := p_dor_rec.dei_attribute22;
3486 p_dei_attribute23 := p_dor_rec.dei_attribute23;
3487 p_dei_attribute24 := p_dor_rec.dei_attribute24;
3488 p_dei_attribute25 := p_dor_rec.dei_attribute25;
3489 p_dei_attribute26 := p_dor_rec.dei_attribute26;
3490 p_dei_attribute27 := p_dor_rec.dei_attribute27;
3491 p_dei_attribute28 := p_dor_rec.dei_attribute28;
3492 p_dei_attribute29 := p_dor_rec.dei_attribute29;
3493 p_dei_attribute30 := p_dor_rec.dei_attribute30;
3494 p_dei_information_category := p_dor_rec.dei_information_category;
3495 p_dei_information1 := p_dor_rec.dei_information1;
3496 p_dei_information2 := p_dor_rec.dei_information2;
3497 p_dei_information3 := p_dor_rec.dei_information3;
3498 p_dei_information4 := p_dor_rec.dei_information4;
3499 p_dei_information5 := p_dor_rec.dei_information5;
3500 p_dei_information6 := p_dor_rec.dei_information6;
3501 p_dei_information7 := p_dor_rec.dei_information7;
3502 p_dei_information8 := p_dor_rec.dei_information8;
3503 p_dei_information9 := p_dor_rec.dei_information9;
3504 p_dei_information10 := p_dor_rec.dei_information10;
3505 p_dei_information11 := p_dor_rec.dei_information11;
3506 p_dei_information12 := p_dor_rec.dei_information12;
3507 p_dei_information13 := p_dor_rec.dei_information13;
3508 p_dei_information14 := p_dor_rec.dei_information14;
3509 p_dei_information15 := p_dor_rec.dei_information15;
3510 p_dei_information16 := p_dor_rec.dei_information16;
3511 p_dei_information17 := p_dor_rec.dei_information17;
3512 p_dei_information18 := p_dor_rec.dei_information18;
3513 p_dei_information19 := p_dor_rec.dei_information19;
3514 p_dei_information20 := p_dor_rec.dei_information20;
3515 p_dei_information21 := p_dor_rec.dei_information21;
3516 p_dei_information22 := p_dor_rec.dei_information22;
3517 p_dei_information23 := p_dor_rec.dei_information23;
3518 p_dei_information24 := p_dor_rec.dei_information24;
3519 p_dei_information25 := p_dor_rec.dei_information25;
3520 p_dei_information26 := p_dor_rec.dei_information26;
3521 p_dei_information27 := p_dor_rec.dei_information27;
3522 p_dei_information28 := p_dor_rec.dei_information28;
3523 p_dei_information29 := p_dor_rec.dei_information29;
3524 p_dei_information30 := p_dor_rec.dei_information30;
3525 p_request_id := TO_CHAR(p_dor_rec.request_id);
3526 p_program_application_id := TO_CHAR(p_dor_rec.program_application_id);
3527 p_program_id := TO_CHAR(p_dor_rec.program_id);
3528 p_program_update_date := TO_CHAR(p_dor_rec.program_update_date,'dd-mm-rrrr');
3529 p_object_version_number := TO_CHAR(p_dor_rec.object_version_number);
3530
3531
3532
3533 SELECT DOCUMENT_TYPE
3534 ,CATEGORY_NAME
3535 ,SUB_CATEGORY_NAME
3536 ,LEGISLATION_CODE
3537 ,SYSTEM_DOCUMENT_TYPE
3538 INTO l_document_type
3539 ,l_category_name
3540 ,l_sub_category_name
3541 ,l_legislation_code
3542 ,l_system_doc_type
3543 FROM HR_DOCUMENT_TYPES_V
3544 WHERE DOCUMENT_TYPE_ID=p_dor_rec.document_type_id;
3545
3546 if l_legislation_code is not null then
3547 SELECT TERRITORY_SHORT_NAME
3548 INTO l_country_name
3549 FROM FND_TERRITORIES_VL
3550 WHERE TERRITORY_CODE = l_legislation_code;
3551 else
3552 l_country_name := null;
3553 end if;
3554
3555 hr_utility.set_location(' Entering:' || l_proc,72);
3556
3557 p_document_type := l_document_type;
3558 p_category_name := l_category_name;
3559 p_sub_category_name := l_sub_category_name;
3560 p_country_name := l_country_name;
3561 p_system_doc_type := l_system_doc_type;
3562
3563 /*-----------Flipping the attachments primary key from docextrainfoid
3564 to transactionid------------------------------------------ */
3565
3566 -- p_transaction_id := get_transaction_id(p_transaction_step_id);
3567 if l_status <> 'AC' THEN
3568 save_attachments(
3569 p_transaction_id => p_transaction_id
3570 ,p_document_extra_info_id => p_dor_rec.document_extra_info_id
3571 ,p_flip_flag => 'TXN'
3572 ,p_return_status => p_return_status);
3573 END IF;
3574
3575 commit;
3576
3577 if p_return_status = 'E' then
3578 hr_utility.raise_error;
3579 else
3580 p_return_status := 'S';
3581 end if;
3582
3583
3584 EXCEPTION
3585
3586 WHEN g_data_error THEN
3587 hr_utility.trace('Exception in g_data_error in '||l_proc||','|| SQLERRM );
3588 hr_utility.set_location(' Leaving:' || l_proc,777);
3589 p_return_status := SQLERRM;
3590
3591 WHEN hr_utility.hr_error THEN
3592 hr_utility.get_message_details(msg_name,msg_appl);
3593 fnd_message.set_name(msg_appl,msg_name);
3594 p_return_status := hr_utility.get_message;
3595
3596 WHEN OTHERS THEN
3597 hr_utility.trace('When others exception in ' ||l_proc||','|| SQLERRM );
3598 hr_utility.set_location(' Leaving:' || l_proc,770);
3599 p_return_status := SQLERRM;
3600
3601 END get_transaction_values;
3602
3603
3604 /*=============================================================================
3605
3606 This procedure will flip the attachemnets' primary key from
3607 transaction id to doc_extra_info_id and vice versa.
3608 The flip flag denotes the flag to change the primary key.
3609
3610 If "TXN" then the priamry key will be change to transaction id.
3611 If "NTXN" then the priamry key will be change to doc_extra_info_id
3612
3613 =============================================================================*/
3614
3615 PROCEDURE save_attachments(
3616 p_transaction_id in number
3617 ,p_document_extra_info_id in number
3618 ,p_flip_flag in varchar2
3619 ,p_return_status out nocopy varchar2)
3620 IS
3621 p_entity_name varchar2(100);
3622 l_proc varchar2(50) := 'save_attachments';
3623 msg_name varchar2(1000);
3624 msg_appl varchar2(10);
3625
3626 Cursor get_attached_docs(p_value in number) is
3627 select rowid
3628 from fnd_attached_documents
3629 where entity_name = 'R_DOCUMENT_EXTRA_INFO'
3630 and pk1_value = p_value;
3631
3632
3633 BEGIN
3634
3635 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 1);
3636
3637 p_entity_name := 'R_DOCUMENT_EXTRA_INFO' ;
3638
3639
3640 if p_flip_flag = 'TXN' then
3641
3642 for crec in get_attached_docs(p_document_extra_info_id) Loop
3643 update_attachment
3644 (p_entity_name=> p_entity_name
3645 ,p_pk1_value=> p_transaction_id
3646 ,p_rowid=> crec.rowid);
3647 end loop;
3648
3649 elsif p_flip_flag = 'NTXN' then
3650
3651 for crec in get_attached_docs(p_transaction_id) Loop
3652 update_attachment
3653 (p_entity_name=> p_entity_name
3654 ,p_pk1_value=> p_document_extra_info_id
3655 ,p_rowid=> crec.rowid);
3656 end loop;
3657
3658 end if;
3659 commit;
3660 if p_return_status = 'E' then
3661 hr_utility.raise_error;
3662 else
3663 p_return_status := 'S';
3664 end if;
3665
3666 hr_utility.set_location('Leaving:'|| g_package||'.'||l_proc, 2);
3667
3668 EXCEPTION
3669
3670 WHEN hr_utility.hr_error THEN
3671 hr_utility.get_message_details(msg_name,msg_appl);
3672 fnd_message.set_name(msg_appl,msg_name);
3673 p_return_status := hr_utility.get_message;
3674 when others then
3675 hr_utility.trace('When others exception in ' ||l_proc||','|| SQLERRM );
3676 hr_utility.set_location(' Leaving:' || l_proc,770);
3677 p_return_status := SQLERRM;
3678
3679
3680 END save_attachments;
3681
3682
3683 /*===========================================================================
3684 This procedure calls the fnd api to update the attachments
3685 ===========================================================================*/
3686
3687
3688 procedure update_attachment
3689 (p_entity_name in varchar2 default null
3690 ,p_pk1_value in varchar2 default null
3691 ,p_rowid in varchar2 ) is
3692
3693
3694
3695 l_proc varchar2(72) := g_package ||'update_attachment';
3696 l_rowid varchar2(50);
3697 l_language varchar2(30) ;
3698 data_error exception;
3699 msg_name varchar2(1000);
3700 msg_appl varchar2(10);
3701 cursor csr_get_attached_doc is
3702 select *
3703 from fnd_attached_documents
3704 where rowid = p_rowid;
3705 cursor csr_get_doc(csr_p_document_id in number) is
3706 select *
3707 from fnd_documents
3708 where document_id = csr_p_document_id;
3709 cursor csr_get_doc_tl (csr_p_lang in varchar2
3710 ,csr_p_document_id in number) is
3711 select *
3712 from fnd_documents_tl
3713 where document_id = csr_p_document_id
3714 and language = csr_p_lang;
3715 l_attached_doc_pre_upd csr_get_attached_doc%rowtype;
3716 l_doc_pre_upd csr_get_doc%rowtype;
3717 l_doc_tl_pre_upd csr_get_doc_tl%rowtype;
3718 Begin
3719 hr_utility.set_location(' Entering:' || l_proc,10);
3720 select userenv('LANG') into l_language from dual;
3721 Open csr_get_attached_doc;
3722 fetch csr_get_attached_doc into l_attached_doc_pre_upd;
3723 IF csr_get_attached_doc%NOTFOUND THEN
3724 close csr_get_attached_doc;
3725 raise data_error;
3726 END IF;
3727
3728 Open csr_get_doc(l_attached_doc_pre_upd.document_id);
3729 fetch csr_get_doc into l_doc_pre_upd;
3730 IF csr_get_doc%NOTFOUND then
3731 close csr_get_doc;
3732 raise data_error;
3733 END IF;
3734
3735 Open csr_get_doc_tl (csr_p_lang => l_language
3736 ,csr_p_document_id => l_attached_doc_pre_upd.document_id);
3737 fetch csr_get_doc_tl into l_doc_tl_pre_upd;
3738 IF csr_get_doc_tl%NOTFOUND then
3739 close csr_get_doc_tl;
3740 raise data_error;
3741 END IF;
3742
3743 hr_utility.set_location(' before fnd_attached_documents_pkg.lock_row :' || l_proc,20);
3744 fnd_attached_documents_pkg.lock_row
3745 (x_rowid => p_rowid
3746 ,x_attached_document_id =>
3747 l_attached_doc_pre_upd.attached_document_id
3748 ,x_document_id => l_doc_pre_upd.document_id
3749 ,x_seq_num => l_attached_doc_pre_upd.seq_num
3750 ,x_entity_name => l_attached_doc_pre_upd.entity_name
3751 ,x_column1 => l_attached_doc_pre_upd.column1
3752 ,x_pk1_value => l_attached_doc_pre_upd.pk1_value
3753 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
3754 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
3755 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
3756 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
3757 ,x_automatically_added_flag =>
3758 l_attached_doc_pre_upd.automatically_added_flag
3759 ,x_attribute_category =>
3760 l_attached_doc_pre_upd.attribute_category
3761 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
3762 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
3763 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
3764 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
3765 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
3766 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
3767 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
3768 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
3769 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
3770 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
3771 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
3772 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
3773 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
3774 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
3775 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
3776 ,x_datatype_id => l_doc_pre_upd.datatype_id
3777 ,x_category_id => l_doc_pre_upd.category_id
3778 ,x_security_type => l_doc_pre_upd.security_type
3779 ,x_security_id => l_doc_pre_upd.security_id
3780 ,x_publish_flag => l_doc_pre_upd.publish_flag
3781 ,x_image_type => l_doc_pre_upd.image_type
3782 ,x_storage_type => l_doc_pre_upd.storage_type
3783 ,x_usage_type => l_doc_pre_upd.usage_type
3784 ,x_start_date_active => l_doc_pre_upd.start_date_active
3785 ,x_end_date_active => l_doc_pre_upd.end_date_active
3786 ,x_language => l_doc_tl_pre_upd.language
3787 ,x_description => l_doc_tl_pre_upd.description
3788 ,x_file_name => l_doc_pre_upd.file_name
3789 ,x_media_id => l_doc_pre_upd.media_id
3790 ,x_doc_attribute_category =>
3791 l_doc_tl_pre_upd.doc_attribute_category
3792 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
3793 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
3794 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
3795 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
3796 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
3797 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
3798 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
3799 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
3800 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
3801 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
3802 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
3803 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
3804 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
3805 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
3806 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
3807 ,x_url => l_doc_pre_upd.url
3808 ,x_title => l_doc_tl_pre_upd.title
3809 );
3810
3811
3812 hr_utility.set_location(' before fnd_attached_documents_pkg.update_row :' || l_proc,30);
3813
3814
3815 fnd_attached_documents_pkg.update_row
3816 (x_rowid => p_rowid
3817 ,x_attached_document_id =>
3818 l_attached_doc_pre_upd.attached_document_id
3819 ,x_document_id => l_doc_pre_upd.document_id
3820 ,x_last_update_date => trunc(sysdate)
3821 ,x_last_updated_by => l_attached_doc_pre_upd.last_updated_by
3822 ,x_seq_num => l_attached_doc_pre_upd.seq_num
3823 ,x_entity_name => p_entity_name
3824 ,x_column1 => l_attached_doc_pre_upd.column1
3825 ,x_pk1_value => p_pk1_value
3826 ,x_pk2_value => l_attached_doc_pre_upd.pk2_value
3827 ,x_pk3_value => l_attached_doc_pre_upd.pk3_value
3828 ,x_pk4_value => l_attached_doc_pre_upd.pk4_value
3829 ,x_pk5_value => l_attached_doc_pre_upd.pk5_value
3830 ,x_automatically_added_flag =>
3831 l_attached_doc_pre_upd.automatically_added_flag
3832 ,x_attribute_category =>
3833 l_attached_doc_pre_upd.attribute_category
3834 ,x_attribute1 => l_attached_doc_pre_upd.attribute1
3835 ,x_attribute2 => l_attached_doc_pre_upd.attribute2
3836 ,x_attribute3 => l_attached_doc_pre_upd.attribute3
3837 ,x_attribute4 => l_attached_doc_pre_upd.attribute4
3838 ,x_attribute5 => l_attached_doc_pre_upd.attribute5
3839 ,x_attribute6 => l_attached_doc_pre_upd.attribute6
3840 ,x_attribute7 => l_attached_doc_pre_upd.attribute7
3841 ,x_attribute8 => l_attached_doc_pre_upd.attribute8
3842 ,x_attribute9 => l_attached_doc_pre_upd.attribute9
3843 ,x_attribute10 => l_attached_doc_pre_upd.attribute10
3844 ,x_attribute11 => l_attached_doc_pre_upd.attribute11
3845 ,x_attribute12 => l_attached_doc_pre_upd.attribute12
3846 ,x_attribute13 => l_attached_doc_pre_upd.attribute13
3847 ,x_attribute14 => l_attached_doc_pre_upd.attribute14
3848 ,x_attribute15 => l_attached_doc_pre_upd.attribute15
3849
3850 ,x_datatype_id => l_doc_pre_upd.datatype_id
3851 ,x_category_id => l_doc_pre_upd.category_id
3852 ,x_security_type => l_doc_pre_upd.security_type
3853 ,x_security_id => l_doc_pre_upd.security_id
3854 ,x_publish_flag => l_doc_pre_upd.publish_flag
3855 ,x_image_type => l_doc_pre_upd.image_type
3856 ,x_storage_type => l_doc_pre_upd.storage_type
3857 ,x_usage_type => l_doc_pre_upd.usage_type
3858 ,x_start_date_active => trunc(sysdate)
3859 ,x_end_date_active => l_doc_pre_upd.end_date_active
3860 ,x_language => l_language
3861 ,x_description => l_doc_tl_pre_upd.description
3862 ,x_file_name => l_doc_pre_upd.file_name
3863 ,x_media_id => l_doc_pre_upd.media_id
3864 ,x_doc_attribute_category =>
3865 l_doc_tl_pre_upd.doc_attribute_category
3866 ,x_doc_attribute1 => l_doc_tl_pre_upd.doc_attribute1
3867 ,x_doc_attribute2 => l_doc_tl_pre_upd.doc_attribute2
3868 ,x_doc_attribute3 => l_doc_tl_pre_upd.doc_attribute3
3869 ,x_doc_attribute4 => l_doc_tl_pre_upd.doc_attribute4
3870 ,x_doc_attribute5 => l_doc_tl_pre_upd.doc_attribute5
3871 ,x_doc_attribute6 => l_doc_tl_pre_upd.doc_attribute6
3872 ,x_doc_attribute7 => l_doc_tl_pre_upd.doc_attribute7
3873 ,x_doc_attribute8 => l_doc_tl_pre_upd.doc_attribute8
3874 ,x_doc_attribute9 => l_doc_tl_pre_upd.doc_attribute9
3875 ,x_doc_attribute10 => l_doc_tl_pre_upd.doc_attribute10
3876 ,x_doc_attribute11 => l_doc_tl_pre_upd.doc_attribute11
3877 ,x_doc_attribute12 => l_doc_tl_pre_upd.doc_attribute12
3878 ,x_doc_attribute13 => l_doc_tl_pre_upd.doc_attribute13
3879 ,x_doc_attribute14 => l_doc_tl_pre_upd.doc_attribute14
3880 ,x_doc_attribute15 => l_doc_tl_pre_upd.doc_attribute15
3881 ,x_url => l_doc_pre_upd.url
3882 ,x_title => l_doc_tl_pre_upd.title
3883 );
3884
3885 hr_utility.set_location(' after fnd_attached_documents_pkg.update_row :' || l_proc,40);
3886 hr_utility.set_location(' Leaving:' || l_proc,50);
3887
3888 EXCEPTION
3889 when others then
3890 hr_utility.set_location(' Error in :' || l_proc,60);
3891 raise;
3892 End update_attachment;
3893 /*==========================================================================
3894 This procedure will abort the workflow process and update the
3895 transaction status to E.
3896 ============================================================================*/
3897 procedure delete_transaction(p_transaction_id in number)
3898 IS
3899 p_item_type WF_ITEMS.ITEM_TYPE%TYPE;
3900 p_item_key WF_ITEMS.ITEM_KEY%TYPE;
3901 l_proc varchar2(50) := 'delete_transaction';
3902
3903 BEGIN
3904
3905 hr_utility.set_location('Entering:'|| g_package||'.'||l_proc, 1);
3906
3907 select item_type,item_key into
3908 p_item_type,p_item_key
3909 from hr_api_transactions
3910 where transaction_id = p_transaction_id;
3911
3912 wf_engine.abortprocess(itemtype => p_item_type
3913 ,itemkey => p_item_key);
3914
3915 hr_transaction_api.rollback_transaction
3916 (p_transaction_id => p_transaction_id);
3917
3918 hr_utility.set_location('Leaving:'|| g_package||'.'||l_proc, 2);
3919
3920 EXCEPTION
3921 when others then
3922 hr_utility.trace('When others exception in ' ||l_proc||','|| SQLERRM );
3923 hr_utility.set_location(' Leaving:' || l_proc,770);
3924 raise;
3925
3926 END delete_transaction;
3927
3928
3929
3930 function isUpdateAllowed(p_transaction_id in number default null,
3931 p_transaction_status in varchar2 default null,
3932 p_document_extra_info_id in number default null)
3933 return varchar2
3934
3935 IS
3936 c_proc constant varchar2(30) := 'isUpdateAllowed';
3937 dor_UpdateAllowed varchar2(30);
3938 p_count number := 0;
3939 ltransaction_id hr_api_transactions.transaction_id%TYPE;
3940 dummy varchar2(10);
3941 pNtfFyiDetails varchar2(1) := 'N';
3942 /*
3943 CURSOR c_transaction is
3944 select transaction_id
3945 from hr_api_transactions
3946 where transaction_id = (
3947 select transaction_id from hr_api_transaction_steps
3948 where transaction_step_id = (
3949 select transaction_step_id from hr_api_transaction_values
3950 where NAME = 'P_DOCUMENT_EXTRA_INFO_ID'
3951 and NUMBER_VALUE = p_document_extra_info_id ));
3952
3953
3954 cursor c_transaction is
3955 select 1
3956 FROM hr_api_transaction_values
3957 WHERE name = 'P_DOCUMENT_EXTRA_INFO_ID'
3958 AND transaction_step_id in
3959 (
3960 SELECT transaction_step_id
3961 FROM hr_api_transaction_steps
3962 WHERE transaction_id in
3963 (
3964 SELECT transaction_id
3965 FROM hr_api_transactions
3966 WHERE transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
3967 and selected_person_id= (select person_id from hr_document_extra_info
3968 where DOCUMENT_EXTRA_INFO_ID = p_document_extra_info_id)
3969 )
3970 )
3971 and NUMBER_VALUE = p_document_extra_info_id;
3972 */
3973 /*cursor c_transaction is
3974 SELECT 1
3975 FROM hr_api_transactions trx,hr_api_transaction_steps trs,hr_api_transaction_values trv
3976 WHERE trx.transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
3977 and trx.transaction_id = trs.transaction_id
3978 and trs.transaction_step_id = trv.TRANSACTION_STEP_ID
3979 and hr_transaction_api.get_number_value(trs.TRANSACTION_STEP_ID, 'P_DOCUMENT_EXTRA_INFO_ID')
3980 =p_document_extra_info_id
3981 and trx.selected_person_id in (select person_id from hr_document_extra_info
3982 where DOCUMENT_EXTRA_INFO_ID = p_document_extra_info_id) ;*/
3983
3984 cursor c_transaction is
3985 SELECT distinct trx.function_id
3986 FROM hr_api_transactions trx,hr_api_transaction_steps trs,hr_api_transaction_values trv
3987 WHERE trx.transaction_ref_table = 'HR_DOCUMENT_EXTRA_INFO'
3988 and trx.transaction_id = trs.transaction_id
3989 and trs.transaction_step_id = trv.TRANSACTION_STEP_ID
3990 and hr_transaction_api.get_number_value(trs.TRANSACTION_STEP_ID, 'P_DOCUMENT_EXTRA_INFO_ID')
3991 =p_document_extra_info_id
3992 and trx.selected_person_id in (select person_id from hr_document_extra_info
3993 where DOCUMENT_EXTRA_INFO_ID = p_document_extra_info_id) ;
3994
3995 begin
3996
3997
3998 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
3999
4000 if p_document_extra_info_id is null then
4001 -- for now this will only allow for transaction owner to update
4002
4003 if(p_transaction_id is not null) then
4004 if(istxnowner(p_transaction_id,fnd_global.employee_id)
4005 and p_transaction_status in ('W','S','RI','RIS')) then
4006 dor_UpdateAllowed := 'HrUpdateEnabled';
4007 else
4008 dor_UpdateAllowed := 'HrUpdateDisabled';
4009 end if;
4010 end if;
4011 elsif p_transaction_id is null then
4012 open c_transaction;
4013 fetch c_transaction into ltransaction_id;
4014 hr_utility.set_location('p_transaction_id is null' , 1);
4015 --fix for bug 14848787
4016 begin
4017 hr_utility.set_location('ltransaction_id saketi'|| ltransaction_id , 1);
4018 select 'X' into dummy from fnd_form_functions where function_id = ltransaction_id
4019 and parameters like '%pNtfFyiDetails=Y%';
4020 hr_utility.set_location('dummy saketi'|| dummy , 1);
4021 dummy := null;
4022 pNtfFyiDetails := 'Y';
4023 exception
4024 when NO_DATA_FOUND then
4025 hr_utility.set_location('No data found' , 1);
4026 pNtfFyiDetails := 'N';
4027 end;
4028 --emd of fix for bug 14848787
4029 if pNtfFyiDetails = 'Y' then
4030 close c_transaction;
4031 hr_utility.set_location('pNtfFyiDetails is Y' , 1);
4032 dor_UpdateAllowed := 'HrUpdateEnabled';
4033 else --fix
4034 hr_utility.set_location('pNtfFyiDetails is N' , 1);
4035 --open c_transaction;
4036 --fetch c_transaction into ltransaction_id;
4037
4038 if c_transaction%found then
4039 close c_transaction;
4040 dor_UpdateAllowed := 'HrUpdateDisabled';
4041 else
4042 close c_transaction;
4043 dor_UpdateAllowed := 'HrUpdateEnabled';
4044 end if;
4045 end if; -- fix
4046 end if;
4047
4048 return dor_UpdateAllowed;
4049
4050 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
4051
4052
4053 exception
4054 when others then
4055 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
4056 Wf_Core.Context(g_package, c_proc, p_transaction_id);
4057 raise;
4058 end isUpdateAllowed;
4059
4060
4061 function isDeleteAllowed(p_transaction_id in number,
4062 p_transaction_status in varchar2) return varchar2
4063
4064 IS
4065 c_proc constant varchar2(30) := 'isDeleteAllowed';
4066 dor_DeleteAllowed varchar2(30);
4067 pvalue varchar2(30);
4068
4069 begin
4070
4071 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4072
4073
4074 -- for now this will only allow for transaction owner to update
4075
4076 if(p_transaction_id is not null) then
4077 if(istxnowner(p_transaction_id,fnd_global.employee_id)
4078 and p_transaction_status in ('W','S','RI','RIS')) then
4079 dor_DeleteAllowed := 'HrDeleteEnabled';
4080 else
4081 pvalue := fnd_profile.value('HR_APRVL_TXN_INITIATOR_DEL_ENABLED');
4082 if pvalue = 'Y' then
4083 dor_DeleteAllowed := 'HrDeleteEnabled';
4084 else
4085 dor_DeleteAllowed := 'HrDeleteDisabled';
4086 end if;
4087 end if;
4088 end if;
4089
4090 return dor_DeleteAllowed;
4091
4092 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
4093
4094
4095 exception
4096 when others then
4097 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
4098 Wf_Core.Context(g_package, c_proc, p_transaction_id);
4099 raise;
4100
4101 end isDeleteAllowed;
4102
4103
4104 function isAttachAllowed(p_transaction_id in number,
4105 p_transaction_status in varchar2) return varchar2
4106
4107 IS
4108 c_proc constant varchar2(30) := 'isAttachAllowed';
4109 dor_AttachAllowed varchar2(30);
4110 begin
4111
4112 hr_utility.set_location('Entering:'|| g_package||'.'||c_proc, 1);
4113
4114
4115 -- for now this will only allow for transaction owner to update
4116
4117 if(p_transaction_id is not null) then
4118 if(istxnowner(p_transaction_id,fnd_global.employee_id)
4119 and p_transaction_status in ('W','S','RI','RIS')) then
4120 dor_AttachAllowed := 'HrDocsEnabled';
4121 else
4122 dor_AttachAllowed := 'HrDocsDisabled';
4123 end if;
4124 end if;
4125
4126 return dor_AttachAllowed;
4127
4128 hr_utility.set_location('Leaving:'|| g_package||'.'||c_proc, 30);
4129
4130
4131 exception
4132 when others then
4133 hr_utility.set_location(g_package||c_proc|| 'errored : '||SQLERRM ||' '||to_char(SQLCODE), 30);
4134 Wf_Core.Context(g_package, c_proc, p_transaction_id);
4135 raise;
4136
4137 end isAttachAllowed;
4138
4139 function isTxnOwner(p_transaction_id in number,
4140 p_person_id in number) return boolean
4141 is
4142 -- local variables
4143 x_returnStatus boolean;
4144 ln_hr_api_transaction_rec hr_api_transactions%rowtype;
4145 ln_person_id number;
4146
4147 begin
4148
4149 -- set the default value
4150 x_returnStatus := false;
4151 ln_person_id := p_person_id;
4152
4153 if(p_transaction_id is not null) then
4154 -- ignore passed personid
4155 -- derive from the transaction details
4156 select hr_api_transactions.creator_person_id
4157 into ln_person_id
4158 from hr_api_transactions
4159 where transaction_id=p_transaction_id;
4160 end if;
4161
4162 --
4163 if(ln_person_id= fnd_global.employee_id) then
4164 x_returnStatus := true;
4165 else
4166 x_returnStatus :=false;
4167 end if;
4168 return x_returnStatus;
4169 exception
4170 when others then
4171 raise;
4172 end isTxnOwner;
4173
4174 function getActionMode(p_transaction_id in number)
4175 return varchar2
4176 is
4177
4178 p_api_addtnl_info hr_api_transactions.api_addtnl_info%type;
4179
4180 begin
4181
4182 SELECT api_addtnl_info
4183 INTO p_api_addtnl_info
4184 FROM hr_api_transactions
4185 WHERE transaction_id = p_transaction_id;
4186
4187 return p_api_addtnl_info;
4188
4189 EXCEPTION
4190 WHEN no_data_found THEN
4191 raise;
4192
4193 WHEN others THEN
4194 raise;
4195 end getActionMode;
4196
4197
4198 -- ----------------------------------------------------------------------------
4199 -- |------------------------< get_transaction_id >----------------------------|
4200 -- ----------------------------------------------------------------------------
4201 function get_transaction_id
4202 (p_transaction_step_id in number) return number is
4203 -- --------------------------------------------------------------------------
4204 -- declare local variables
4205 -- --------------------------------------------------------------------------
4206 l_proc constant varchar2(100) := g_package || ' get_transaction_id';
4207 l_transaction_id hr_api_transactions.transaction_id%type;
4208 -- cursor to select the transaction_id of the step
4209 cursor csr_hats is
4210 select hats.transaction_id
4211 from hr_api_transaction_steps hats
4212 where hats.transaction_step_id = p_transaction_step_id;
4213
4214 begin
4215
4216 hr_utility.set_location('Entering:'|| l_proc, 5);
4217
4218 open csr_hats;
4219 hr_utility.trace('Going into Fetch after (open csr_hats ): '|| l_proc);
4220 fetch csr_hats into l_transaction_id;
4221 if csr_hats%notfound then
4222 -- the transaction step doesn't exist
4223 close csr_hats;
4224 hr_utility.set_message(801, 'HR_51751_WEB_TRA_STEP_EXISTS');
4225 hr_utility.raise_error;
4226 end if;
4227 close csr_hats;
4228 hr_utility.set_location(' Leaving:'||l_proc, 15);
4229 return(l_transaction_id);
4230
4231 hr_utility.set_location(' Leaving:'||l_proc, 20);
4232
4233 end get_transaction_id;
4234
4235 PROCEDURE block_for_transaction (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
4236 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
4237 actid IN NUMBER,
4238 funcmode IN VARCHAR2,
4239 resultout OUT nocopy VARCHAR2 )
4240 IS
4241
4242 l_item_value varchar2(200);
4243 l_result_out varchar2(400);
4244
4245 BEGIN
4246 IF (funcmode='RUN') THEN
4247 wf_engine.AddItemAttr(itemtype => itemtype,
4248 itemkey => itemkey,
4249 aname => 'CREATE_TRN_BLOCK_ACTIVITY',
4250 text_value => actid,
4251 number_value => null,
4252 date_value => null);
4253
4254 WF_STANDARD.Block(itemtype => itemtype,
4255 itemkey => itemkey,
4256 actid => itemkey,
4257 funcmode => funcmode,
4258 resultout => l_result_out);
4259
4260 resultout := l_result_out;
4261 END IF;
4262
4263 IF (funcmode='CANCEL') THEN
4264 resultout:='COMPLETE';
4265 RETURN;
4266 END IF;
4267 Exception
4268 when others then
4269 hr_utility.set_location('ENTERING exception block_for_transaction', 10);
4270
4271
4272 end block_for_transaction;
4273
4274 /* Added for bug#16449378*/
4275 PROCEDURE create_bprv_block (itemtype IN WF_ITEMS.ITEM_TYPE%TYPE,
4276 itemkey IN WF_ITEMS.ITEM_KEY%TYPE,
4277 actid IN NUMBER,
4278 funcmode IN VARCHAR2,
4279 resultout OUT nocopy VARCHAR2 )
4280 IS
4281
4282 l_item_value varchar2(200);
4283 l_result_out varchar2(400);
4284
4285 BEGIN
4286 hr_utility.set_location('Entered hr_dor_review_ss.create_bprv_block',10);
4287 hr_utility.set_location('create_bprv_block - funcmode = '||funcmode,20);
4288 IF (funcmode='RUN') THEN
4289 hr_utility.set_location('create_bprv_block - funcmode = '||funcmode,30);
4290 wf_engine.AddItemAttr(itemtype => itemtype,
4291 itemkey => itemkey,
4292 aname => 'CREATE_TRN_BPRV_BLOCK',
4293 text_value => actid,
4294 number_value => null,
4295 date_value => null);
4296 hr_utility.set_location('create_bprv_block - funcmode = '||funcmode,40);
4297 WF_STANDARD.Block(itemtype => itemtype,
4298 itemkey => itemkey,
4299 actid => itemkey,
4300 funcmode => funcmode,
4301 resultout => l_result_out);
4302 hr_utility.set_location('create_bprv_block - l_result_out = '||l_result_out,50);
4303 resultout := l_result_out;
4304 hr_utility.set_location('create_bprv_block ',60);
4305 END IF;
4306 hr_utility.set_location('create_bprv_block - ',70);
4307 IF (funcmode='CANCEL') THEN
4308 hr_utility.set_location('create_bprv_block - funcmode = '||funcmode,80);
4309 resultout:='COMPLETE';
4310 hr_utility.set_location('create_bprv_block - resultout = '||resultout,90);
4311 RETURN;
4312 END IF;
4313 Exception
4314 when others then
4315 hr_utility.set_location('ENTERING exception create_bprv_block', 100);
4316
4317
4318 end create_bprv_block;
4319
4320 END HR_DOR_REVIEW_SS;
4321