[Home] [Help]
PACKAGE BODY: APPS.IGIDOSL
Source
1 PACKAGE BODY igidosl AS
2 -- $Header: igidoslb.pls 120.26.12000000.5 2007/07/02 05:00:40 pshivara ship $
3 --
4
5 /* ============== FND LOG VARIABLES ================== */
6 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
7 l_state_level number := FND_LOG.LEVEL_STATEMENT ;
8 l_proc_level number := FND_LOG.LEVEL_PROCEDURE ;
9 l_event_level number := FND_LOG.LEVEL_EVENT ;
10 l_excep_level number := FND_LOG.LEVEL_EXCEPTION ;
11 l_error_level number := FND_LOG.LEVEL_ERROR ;
12 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED ;
13
14 /* ============== WORKFLOW VARAIBLES =================
15 ## Variables for framing and coloring the Table.
16 ## copied FROM WF_NOTIFICATION package
17 ## /fnddev/fnd/11.5/patch/115/sql/wfntfb.pls
18 ##
19 */
20
21 table_width VARCHAR2(6) := '"100%"';
22 table_border VARCHAR2(3) := '"0"';
23 table_cellpadding VARCHAR2(3) := '"3"';
24 table_cellspacing VARCHAR2(3) := '"1"';
25 total_cellspacing VARCHAR2(3) := '"0"';
26 table_bgcolor VARCHAR2(7) := '"white"';
27 th_bgcolor VARCHAR2(9) := '"#cccc99"';
28 th_fontcolor VARCHAR2(9) := '"#336699"';
29 th_fontface VARCHAR2(80) := '"Arial, Helvetica, Geneva, sans-serif"';
30 th_fontsize VARCHAR2(2) := '2';
31 td_bgcolor VARCHAR2(9) := '"#f7f7e7"';
32 td_fontcolor VARCHAR2(7) := '"black"';
33 td_fontface VARCHAR2(80) := '"Arial, Helvetica, Geneva, sans-serif"';
34 td_fontsize varchar2(2) := '2';
35
36 -- Global variable to hold user id
37 g_userid NUMBER ;
38 g_currency_code VARCHAR2(15);
39 g_sob_id NUMBER(15);
40 g_total_text VARCHAR2(100);
41
42 CURSOR C_curr (p_sob_id NUMBER)
43 IS
44 SELECT currency_code FROM gl_sets_of_books
45 WHERE set_of_books_id = p_sob_id;
46
47 -- Cursor to FETCH user id FROM user name
48 CURSOR c_userid (p_username VARCHAR2) IS
49 SELECT user_id, employee_id
50 FROM fnd_user
51 WHERE user_name = p_username;
52
53 --
54 --
55 -- PUBLIC ROUTINES
56 --
57 --
58
59 /* =================== DEBUG_LOG_UNEXP_ERROR =================== */
60
61 Procedure Debug_log_unexp_error (P_module IN VARCHAR2,
62 P_error_type IN VARCHAR2)
63 IS
64
65 BEGIN
66
67 IF (l_unexp_level >= l_debug_level) THEN
68
69 IF (P_error_type = 'DEFAULT') THEN
70 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
71 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
72 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
73 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igidosl.' || P_module ,TRUE);
74 ELSIF (P_error_type = 'USER') THEN
75 FND_LOG.MESSAGE(l_unexp_level, 'igi.plsql.igidosl.' || P_module ,TRUE);
76 END IF;
77
78 END IF;
79
80 END Debug_log_unexp_error;
81
82 /* =================== DEBUG_LOG_STRING =================== */
83
84 Procedure Debug_log_string (P_level IN NUMBER,
85 P_module IN VARCHAR2,
86 P_Message IN VARCHAR2)
87 IS
88
89 BEGIN
90
91 IF (P_level >= l_debug_level) THEN
92 FND_LOG.STRING(P_level, 'igi.plsql.igidosl.' || P_module, P_message) ;
93 END IF;
94
95 END Debug_log_string;
96
97 /* =================== SELECTOR =================== */
98
99 PROCEDURE Selector(itemtype IN VARCHAR2,
100 itemkey IN VARCHAR2,
101 actid IN NUMBER,
102 funcmode IN VARCHAR2,
103 resultout OUT NOCOPY VARCHAR2) IS
104
105 l_session_org_id NUMBER;
106 l_work_item_org_id NUMBER;
107 l_user_id NUMBER;
108 l_resp_id NUMBER;
109 l_appl_id NUMBER;
110
111 BEGIN
112
113 /* =============== START DEBUG LOG ================ */
114 DEBUG_LOG_STRING (l_proc_level, 'selector.Msg1',
115 ' ** BEGIN SELECTOR ** ');
116 /* =============== END DEBUG LOG ================== */
117 -- Bug 4124934 Start
118
119 IF funcmode = 'RUN'
120 THEN
121 -- Return process to run when workflow is invoked
122 resultout := 'DOSFLOWE';
123
124 ELSIF funcmode = 'TEST_CTX'
125 THEN
126 -- Code that compares current session context
127 -- with the work item context required to execute
128 -- the workflow safely
129
130 fnd_profile.get (name=>'ORG_ID', val=> l_session_org_id);
131
132 l_work_item_org_id := wf_engine.GetItemAttrNumber
133 (itemtype,
134 itemkey,
135 'ORG_ID');
136
137 IF l_session_org_id = l_work_item_org_id
138 THEN
139 resultout := 'TRUE';
140 ELSE
141 -- This will cause workflow to call this funciton
142 -- in SET_CTX mode
143 resultout := 'FALSE';
144 END IF;
145
146 ELSIF funcmode = 'SET_CTX'
147 THEN
148 l_user_id := wf_engine.GetItemAttrNumber
149 (itemtype,
150 itemkey,
151 'USER_ID');
152 l_resp_id := wf_engine.GetItemAttrNumber
153 (itemtype,
154 itemkey,
155 'RESPONSIBILITY_ID');
156 l_appl_id := wf_engine.GetItemAttrNumber
157 (itemtype,
158 itemkey,
159 'RESP_APPL_ID');
160
161 DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg',
162 ' Setting apps context with userid, respid, applid as '
163 ||l_user_id ||' '|| l_resp_id ||' '|| l_appl_id );
164
165
166 FND_GLOBAL.apps_initialize(l_user_id,l_resp_id,l_appl_id);
167
168 resultout := 'COMPLETE';
169 ELSE
170 resultout := 'COMPLETE';
171 END IF;
172
173 -- Bug 4124934 End
174
175 /* =============== START DEBUG LOG ================ */
176 DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg2',
177 ' result --> ' || resultout);
178 DEBUG_LOG_STRING (l_proc_level, 'selector.Msg3',
179 ' ** END SELECTOR ** ' || resultout);
180 /* =============== END DEBUG LOG ================== */
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 /* =============== START DEBUG LOG ================ */
185 DEBUG_LOG_UNEXP_ERROR ('selector.unexp1','DEFAULT');
186 /* =============== END DEBUG LOG ================== */
187 resultout := NULL;
188 Wf_Core.Context ('IGIDOSL','Selector', itemtype, itemkey,
189 TO_CHAR(actid),funcmode);
190 RAISE;
191 END Selector;
192
193 /* ========================== STARTUP ============================= */
194
195 PROCEDURE Startup (Wkf_Name VARCHAR2,
196 Dossier_Id NUMBER,
197 Dossier_Num VARCHAR2,
198 ledger_id NUMBER, -- Added for bug 6126275
199 Packet_Id NUMBER,
200 User_Name VARCHAR2,
201 Dossier_Transaction_Name VARCHAR2,
202 Dossier_Description VARCHAR2,
203 User_Id VARCHAR2,
204 Responsibility_Id VARCHAR2,
205 Dossier_Transaction_Detail VARCHAR2) IS
206
207 ItemType VARCHAR2(30) := Wkf_name;
208 ItemKey VARCHAR2(60) := (Dossier_Num);
209 UserKey VARCHAR2(50) := 'DOSFLOW'||Dossier_Num;
210 l_trx_status VARCHAR2(80);
211 l_trx_number VARCHAR2(60);
212 l_approval_run NUMBER;
213
214 l_total_amount NUMBER;
215 l_formatted_total_amount VARCHAR2(100);
216
217 l_employee_id NUMBER(15);
218 l_preparer_name VARCHAR2(80);
219 l_preparer_display_name VARCHAR2(80);
220
221 CURSOR c_source_total
222 IS
223 SELECT SUM(NVL(s.funds_available,0) - NVL(s.new_balance,0))
224 FROM igi_dos_trx_sources s
225 WHERE trx_id IN (
226 SELECT trx_id FROM igi_dos_trx_headers
227 WHERE trx_number = Dossier_num
228 AND dossier_id = Dossier_id);
229
230 BEGIN
231
232 /* =============== START DEBUG LOG ================ */
233 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg1',
234 ' ** BEGIN STARTUP ** ');
235 /* =============== END DEBUG LOG ================== */
236
237 -- set of books id.
238 -- g_sob_id := FND_PROFILE.VALUE ('GL_SET_OF_BKS_ID'); /* Commented for bug 6126275 */
239 g_sob_id := ledger_id; /* Added for bug 6126275 */
240
241 /* =============== START DEBUG LOG ================ */
242 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg2',
243 ' g_sob_id --> ' || g_sob_id);
244 /* =============== END DEBUG LOG ================== */
245
246 -- currency code.
247 OPEN c_curr(g_sob_id);
248 FETCH c_curr INTO g_currency_code;
249 CLOSE c_curr;
250
251 /* =============== START DEBUG LOG ================ */
252 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg3',
253 ' g_curency_code --> ' || g_currency_code);
254 /* =============== END DEBUG LOG ================== */
255
256 -- Get user id
257 OPEN c_userid (User_Name);
258 FETCH c_userid INTO g_userid, l_employee_id;
259 CLOSE c_userid;
260
261 /* =============== START DEBUG LOG ================ */
262 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg4',
263 ' l_employee_id --> ' || l_employee_id);
264 /* =============== END DEBUG LOG ================== */
265
266 SELECT meaning
267 INTO l_trx_status
268 FROM igi_lookups
269 WHERE lookup_type = 'DOSSIER STATUS'
270 AND lookup_code = 'INPROCESS';
271
272 /* =============== START DEBUG LOG ================ */
273 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg5',
274 ' l_trx_status --> ' || l_trx_status);
275 /* =============== END DEBUG LOG ================== */
276
277 -- Flag the DOSSIER as in process
278 UPDATE igi_dos_trx_headers trx
279 SET trx.trx_status = l_trx_status,
280 trx.last_update_date= sysdate
281 WHERE trx.trx_number = dossier_num;
282
283 /* =============== START DEBUG LOG ================ */
284 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg6',
285 ' updated igi_dos_trx_headers ');
286 /* =============== END DEBUG LOG ================== */
287
288 -- Get the approval run id
289 SELECT igi_dos_approval_run_s1.NextVal
290 INTO l_approval_run
291 FROM sys.dual;
292
293 /* =============== START DEBUG LOG ================ */
294 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg7',
295 ' l_approval_run --> ' || l_approval_run);
296 /* =============== END DEBUG LOG ================== */
297
298 -- Build the unique item key
299 ItemKey := ItemKey || '/' || TO_CHAR(l_approval_run);
300
301 /* =============== START DEBUG LOG ================ */
302 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg8',
303 ' Itemkey --> ' || itemkey);
304 /* =============== END DEBUG LOG ================== */
305
306 -- Workflow Initiation
307 --4124934 added Process parameter
308 Wf_Engine.CreateProcess ( itemtype => ItemType,
309 itemkey => ItemKey,
310 process => 'DOSFLOWE');
311
312 /* =============== START DEBUG LOG ================ */
313 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg9',
314 ' calling create process ');
318
315 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg3',
316 ' Assigning workflow attribute values ');
317 /* =============== END DEBUG LOG ================== */
319 --
320 -- Assign workflow item attribute values
321 --
322
323 Wf_Engine.SetItemAttrNumber ( itemtype => ItemType,
324 itemkey => ItemKey,
325 aname => 'DOSSIER_ID',
326 avalue => Dossier_Id);
327
328 /* =============== START DEBUG LOG ================ */
329 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg10',
330 ' Dossier id --> ' || Dossier_id);
331 /* =============== END DEBUG LOG ================== */
332
333 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
334 itemkey => ItemKey,
335 aname => 'DOSSIER_NUM',
336 avalue => Dossier_Num);
337
338 /* =============== START DEBUG LOG ================ */
339 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg11',
340 ' Dossier Num --> ' || Dossier_num);
341 /* =============== END DEBUG LOG ================== */
342
343 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
344 itemkey => ItemKey,
345 aname => 'INITIAL_ENTRY_FLAG',
346 avalue => 'TRUE');
347
348 /* =============== START DEBUG LOG ================ */
349 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg12',
350 ' Initial entry flag --> ' || 'TRUE');
351 /* =============== END DEBUG LOG ================== */
352
353 Wf_Engine.SetItemAttrNumber ( itemtype => ItemType,
354 itemkey => ItemKey,
355 aname => 'PACKET_ID',
356 avalue => Packet_Id);
357
358 /* =============== START DEBUG LOG ================ */
359 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg13',
360 ' Packet id --> ' || Packet_id);
361 /* =============== END DEBUG LOG ================== */
362
363 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
364 itemkey => ItemKey,
365 aname => 'CREATOR_NAME',
366 avalue => User_Name);
367
368 /* =============== START DEBUG LOG ================ */
369 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg14',
370 ' CREATOR_NAME --> ' || User_Name);
371 /* =============== END DEBUG LOG ================== */
372
373 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
374 itemkey => ItemKey,
375 aname => 'DOSSIER_TRANSACTION_NAME',
376 avalue => Dossier_Transaction_Name);
377
378 /* =============== START DEBUG LOG ================ */
379 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg15',
380 ' DOSSIER_TRANSACTION_NAME --> ' || Dossier_Transaction_Name);
381 /* =============== END DEBUG LOG ================== */
382
383 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
384 itemkey => ItemKey,
385 aname => 'DOSSIER_DESCRIPTION',
386 avalue => Dossier_Description);
387
388 /* =============== START DEBUG LOG ================ */
389 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg15.1',
390 ' DOSSIER_DESCRIPTION --> ' || Dossier_Description);
391 /* =============== END DEBUG LOG ================== */
392
393 OPEN c_source_total;
394 FETCH c_source_total INTO l_total_amount;
395 CLOSE c_source_total;
396 -- Bug 5138221 .. Start
397 l_formatted_total_amount := TO_CHAR(l_total_amount, FND_CURRENCY.Get_Format_Mask(g_currency_code,22));
398 -- Bug 5138221 .. End
399 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
400 itemkey => ItemKey,
401 aname => 'TOTAL',
402 avalue => l_formatted_total_amount);
403
404
405 /* =============== START DEBUG LOG ================ */
406 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg16',
407 ' TOTAL_AMOUNT --> ' || l_formatted_total_amount);
408 /* =============== END DEBUG LOG ================== */
409
410 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
411 itemkey => ItemKey,
412 aname => 'DOCUMENT_ID',
413 avalue => itemtype||':'||itemkey);
414
415 /* =============== START DEBUG LOG ================ */
416 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg17',
417 ' DOCUMENT_ID --> ' || itemtype||':'||itemkey);
418 /* =============== END DEBUG LOG ================== */
419
420 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
421 itemkey => ItemKey,
422 aname => 'DOSSIER_TRANSACTION_DETAIL',
423 avalue => 'plsqlclob:igidosl.dossier_transaction_detail/'||itemtype||':'||itemkey);
424
425 /* =============== START DEBUG LOG ================ */
426 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg18',
427 ' DOSSIER_TRANSACTION_DETAIL --> ' ||
431 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
428 'plsqlclob:igidosl.dossier_transaction_detail/'||itemtype||':'||itemkey);
429 /* =============== END DEBUG LOG ================== */
430
432 itemkey => ItemKey,
433 aname => 'USER_ID',
434 avalue => User_Id);
435
436 /* =============== START DEBUG LOG ================ */
437 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg19',
438 ' USER_ID --> ' || User_Id );
439 /* =============== END DEBUG LOG ================== */
440
441 Wf_Engine.SetItemAttrText ( itemtype => ItemType,
442 itemkey => ItemKey,
443 aname => 'RESPONSIBILITY_ID',
444 avalue => Responsibility_Id);
445
446 /* =============== START DEBUG LOG ================ */
447 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg20',
448 ' RESPONSIBILITY_ID --> ' || Responsibility_Id );
449 /* =============== END DEBUG LOG ================== */
450
451 -- Retrieve preparer's User name (Login name for Apps) and displayed name
452 wf_directory.GetUserName(p_orig_system => 'PER',
453 p_orig_system_id => l_employee_id,
454 p_name => l_preparer_name,
455 p_display_name => l_preparer_display_name );
456
457
458 -- Copy username to Workflow
459 wf_engine.SetItemAttrText( itemtype => itemtype,
460 itemkey => itemkey,
461 aname => 'PREPARER_NAME',
462 avalue => l_preparer_display_name );
463
464 /* =============== START DEBUG LOG ================ */
465 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg21',
466 ' PREPARER_NAME --> ' || l_preparer_display_name );
467 /* =============== END DEBUG LOG ================== */
468
469 -- Copy displayed username to Workflow
470 wf_engine.SetItemAttrText( itemtype => itemtype,
471 itemkey => itemkey,
472 aname => 'PREPARER_LOGIN_NAME',
473 avalue => l_preparer_name );
474
475 /* =============== START DEBUG LOG ================ */
476 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg22',
477 ' PREPARER_LOGIN_NAME --> ' || l_preparer_name );
478 /* =============== END DEBUG LOG ================== */
479
480 --
481 -- Set process attributes
482 --
483
484 /* =============== START DEBUG LOG ================ */
485 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg23',
486 ' Setting process attributes ');
487 /* =============== END DEBUG LOG ================== */
488
489 Wf_Engine.SetItemOwner ( itemtype => ItemType,
490 itemkey => ItemKey,
491 owner => User_name);
492
493 Wf_Engine.SetItemUserKey ( itemtype => ItemType,
494 itemkey => ItemKey,
495 userkey => UserKey);
496
497 /* =============== START DEBUG LOG ================ */
498 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg24',
499 ' Starting workflow process ' );
500 /* =============== END DEBUG LOG ================== */
501
502 -- Set user id, responsibility id , org id and application id context values
503 -- Bug 4124934 , Start
504 wf_engine.SetItemAttrText ( itemtype => ItemType,
505 itemkey => ItemKey,
506 aname => 'ORG_ID',
507 avalue => FND_PROFILE.VALUE('ORG_ID'));
508
509 wf_engine.SetItemAttrText ( itemtype => ItemType,
510 itemkey => ItemKey,
511 aname => 'USER_ID',
512 avalue => FND_PROFILE.VALUE('USER_ID'));
513
514 wf_engine.SetItemAttrText ( itemtype => ItemType,
515 itemkey => ItemKey,
516 aname => 'RESPONSIBILITY_ID',
517 avalue => FND_PROFILE.VALUE('RESP_ID'));
518
519 wf_engine.SetItemAttrText ( itemtype => ItemType,
520 itemkey => ItemKey,
521 aname => 'RESP_APPL_ID',
522 avalue => FND_PROFILE.VALUE('RESP_APPL_ID'));
523 -- Bug 4124934 End
524
525 --
526 -- Kick-off the workflow process instance
527 --
528 Wf_Engine.StartProcess ( itemtype => ItemType,
529 itemkey => ItemKey);
530
531 COMMIT;
532
533 /* =============== START DEBUG LOG ================ */
534 DEBUG_LOG_STRING (l_proc_level, 'startup.Msg25',
535 ' ** END OF STARTUP ** ');
536 /* =============== END DEBUG LOG ================== */
537
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 /* =============== START DEBUG LOG ================ */
542 DEBUG_LOG_UNEXP_ERROR ('startup.unexp1','DEFAULT');
543 /* =============== END DEBUG LOG ================== */
544 Wf_Core.Context ('IGIDOSL', 'Startup', itemtype, itemkey,
545 ' username='||user_name);
546 RAISE;
550
547 END Startup;
548
549 /* ========================== SETROLE ================== */
551 PROCEDURE SetRole ( itemtype VARCHAR2,
552 itemkey VARCHAR2,
553 actid NUMBER,
554 funcmode VARCHAR2,
555 result OUT NOCOPY VARCHAR2)
556 IS
557
558 l_picked_role wf_notifications.responder%TYPE;
559
560 BEGIN
561
562 /* =============== START DEBUG LOG ================ */
563 DEBUG_LOG_STRING (l_proc_level, 'setrole.Msg1',
564 ' ** START SETROLE ** ');
565 /* =============== END DEBUG LOG ================== */
566
567 IF funcmode <> 'RUN' THEN
568 result := 'COMPLETE';
569 /* =============== START DEBUG LOG ================ */
570 DEBUG_LOG_STRING (l_proc_level, 'setrole.Msg2',
571 ' funcmode <> RUN - result --> '
572 || result || ' -- RETURN ');
573 /* =============== END DEBUG LOG ================== */
574
575 RETURN;
576 END IF;
577
578 l_picked_role := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
579 itemkey => itemkey,
580 aname => 'PICKED_ROLE');
581
582 /* =============== START DEBUG LOG ================ */
583 DEBUG_LOG_STRING (l_proc_level, 'setrole.Msg3',
584 ' l_picked_role --> '|| l_picked_role);
585 /* =============== END DEBUG LOG ================== */
586
587 Wf_Engine.SetItemAttrText ( itemtype => itemtype,
588 itemkey => itemkey,
589 aname => 'ROLE_NAME',
590 avalue => l_picked_role);
591
592 /* =============== START DEBUG LOG ================ */
593 DEBUG_LOG_STRING (l_proc_level, 'setrole.Msg4',
594 ' setting l_picked_role to ROLE_NAME ');
595 /* =============== END DEBUG LOG ================== */
596
597 Wf_Engine.SetItemAttrText ( itemtype => itemtype,
598 itemkey => itemkey,
599 aname => 'SELECTED_USER_NAME',
600 avalue => l_picked_role);
601
602 /* =============== START DEBUG LOG ================ */
603 DEBUG_LOG_STRING (l_proc_level, 'setrole.Msg5',
604 ' setting l_picked_role to SELECTED_USER_NAME ');
605 /* =============== END DEBUG LOG ================== */
606
607 result := 'COMPLETE';
608
609 /* =============== START DEBUG LOG ================ */
610 DEBUG_LOG_STRING (l_proc_level, 'setrole.Msg6',
611 ' ** END SETROLE ** ');
612 /* =============== END DEBUG LOG ================== */
613
614
615 EXCEPTION
616 WHEN OTHERS THEN
617 /* =============== START DEBUG LOG ================ */
618 DEBUG_LOG_UNEXP_ERROR ('setrole.Unexp1','DEFAULT');
619 /* =============== END DEBUG LOG ================== */
620
621 result := NULL;
622 Wf_Core.Context ('IGIDOSL', 'SetRole', itemtype, itemkey ,
623 TO_CHAR(actid),funcmode);
624 RAISE;
625 END SetRole;
626
627 /* ================== GETPARENTPOSITION ================== */
628
629 PROCEDURE GetParentPosition (itemtype VARCHAR2,
630 itemkey VARCHAR2,
631 actid NUMBER,
632 funcmode VARCHAR2,
633 result OUT NOCOPY VARCHAR2)
634 IS
635
636 l_creator_name wf_notifications.responder%TYPE
637 := Wf_Engine.GetItemAttrText (itemtype => itemtype,
638 itemkey => itemkey,
639 aname => 'CREATOR_NAME');
640
641 l_initial_entry VARCHAR2(5)
642 := Wf_Engine.GetItemAttrText (itemtype => itemtype,
643 itemkey => itemkey,
644 aname => 'INITIAL_ENTRY_FLAG');
645
646 l_pos_struct_element_id per_pos_structure_elements.pos_structure_element_id%TYPE
647 := Wf_Engine.GetItemAttrNumber (itemtype => itemtype,
648 itemkey => itemkey,
649 aname => 'POS_STRUCTURE_ELEMENT_ID');
650
651 l_dossier_id igi_dos_doc_types.dossier_id%TYPE
652 :=Wf_Engine.GetItemAttrNumber (itemtype => ItemType,
653 itemkey => ItemKey,
654 aname => 'DOSSIER_ID');
655
656 l_picked_role wf_notifications.responder%TYPE;
657 l_error VARCHAR2(1000) ;
658 l_position_structure_id per_position_structures.position_structure_id%TYPE ;
659 l_structure_version_id per_pos_structure_versions.pos_structure_version_id%TYPE ;
660 l_structure_element_id per_pos_structure_elements.pos_structure_element_id%TYPE ;
661 l_parent_position_id per_positions.position_id%TYPE ;
662 l_position_id per_all_positions.position_id%TYPE;
663 l_top_position_id per_all_positions.position_id%TYPE ;
664 l_hierarchy_version_id per_pos_structure_versions.pos_structure_version_id%TYPE;
665 l_hierarchy_id per_position_structures.position_structure_id%TYPE;
666 l_business_group_id hr_all_positions_f.business_group_id%TYPE;
670 cur_pos_not_found EXCEPTION ;
667 l_organization_id hr_all_positions_f.organization_id%TYPE;
668 l_current_user_name fnd_user.user_name%TYPE;
669
671 pos_hier_not_found EXCEPTION ;
672 pos_hier_ver_not_found EXCEPTION ;
673 top_pos_not_found EXCEPTION ;
674 par_pos_not_found EXCEPTION ;
675
676 /*************************************/
677 /* FETCH current position of creator */
678 /*************************************/
679
680 CURSOR c_cur_pos
681 IS
682 SELECT hap.position_id,
683 hap.business_group_id,
684 hap.organization_id,
685 fu.user_name
686 FROM hr_all_positions_f hap,
687 per_all_assignments_f paa,
688 fnd_user fu ,
689 per_people_f p,
690 per_periods_of_service b
691 WHERE
692 fu.user_id = g_userid
693 AND paa.person_id = p.person_id
694 AND paa.primary_flag = 'Y'
695 AND paa.period_of_service_id = b.period_of_service_id
696 AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
697 AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
698 AND (b.actual_termination_date is null OR b.actual_termination_date>= trunc(sysdate) )
699 AND p.employee_number IS NOT NULL
700 and fu.start_date <= SYSDATE
701 and NVL(fu.end_date,SYSDATE) >= SYSDATE
702 and fu.employee_id IS NOT NULL
703 and fu.employee_id = P.PERSON_ID
704 and NVL(b.actual_termination_date,SYSDATE) >= SYSDATE
705 and P.business_group_id = paa.business_group_id
706 and paa.assignment_type = 'E'
707 and paa.business_group_id = hap.business_group_id
708 and paa.position_id IS NOT NULL
709 and paa.position_id = hap.position_id
710 and paa.organization_id = hap.organization_id
711 and hap.date_effective <= SYSDATE
712 and NVL(hap.date_end, SYSDATE) >= SYSDATE
713 and NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID') ;
714
715
716
717 /*********************************************/
718 /* FETCH position hierarchy for dossier type */
719 /*********************************************/
720
721 CURSOR c_pos_hier (p_dossier_id igi_dos_doc_types.dossier_id%TYPE)
722 IS
723 SELECT hierarchy_id
724 FROM igi_dos_doc_types
725 WHERE dossier_id = p_dossier_id;
726
727 /***********************************************/
728 /* FETCH current version of position hierarchy */
729 /***********************************************/
730
731 CURSOR c_pos_hier_ver(p_hierarchy_id igi_dos_doc_types.hierarchy_id%TYPE,
732 p_business_group_id hr_all_positions_f.business_group_id%TYPE)
733 IS
734 SELECT pos_structure_version_id
735 FROM per_pos_structure_versions
736 WHERE position_structure_id = p_hierarchy_id
737 AND SYSDATE BETWEEN date_FROM AND NVL(date_to, SYSDATE)
738 AND business_group_id = p_business_group_id
739 AND version_number =
740 (SELECT MAX(version_number)
741 FROM per_pos_structure_versions
742 WHERE position_structure_id = p_hierarchy_id
743 AND SYSDATE BETWEEN date_FROM AND NVL(date_to,SYSDATE)
744 AND business_group_id = p_business_group_id);
745
746 /***********************************/
747 /* FETCH top position in hierarchy */
748 /***********************************/
749
750 CURSOR c_get_top_position
751 (p_pos_structure_ver_id per_pos_structure_elements.pos_structure_version_id%TYPE,
752 p_business_group_id hr_all_positions_f.business_group_id%TYPE)
753 IS
754 SELECT ppse.parent_position_id
755 FROM per_pos_structure_elements ppse
756 WHERE ppse.pos_structure_version_id = p_pos_structure_ver_id
757 AND business_group_id = p_business_group_id
758 AND ppse.parent_position_id NOT IN
759 (SELECT subordinate_position_id
760 FROM per_pos_structure_elements
761 WHERE pos_structure_version_id = p_pos_structure_ver_id
762 AND business_group_id = p_business_group_id);
763
764 /****************************/
765 /* FETCH parent position id */
766 /****************************/
767
768 CURSOR c_par_pos_id
769 (p_hier_ver_id per_pos_structure_versions.pos_structure_version_id%TYPE,
770 p_position_id per_all_positions.position_id%TYPE,
771 p_business_group_id hr_all_positions_f.business_group_id%TYPE)
772 IS
773 SELECT parent_position_id
774 FROM per_pos_structure_elements
775 WHERE pos_structure_version_id = p_hier_ver_id
776 AND business_group_id = p_business_group_id
777 AND subordinate_position_id = p_position_id;
778
779 BEGIN
780
781 /* =============== START DEBUG LOG ================ */
782 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg1',
783 ' ** START GETPARENTPOSITION ** ');
784 /* =============== END DEBUG LOG ================== */
785
786 IF funcmode <> 'RUN' THEN
787 /* =============== START DEBUG LOG ================ */
788 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg2',
789 ' result --> COMPLETE ');
793 RETURN;
790 /* =============== END DEBUG LOG ================== */
791
792 result := 'COMPLETE';
794 ELSE
795 /* =============== START DEBUG LOG ================ */
796 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg3',
797 ' result --> ERROR ');
798 /* =============== END DEBUG LOG ================== */
799 result := 'Error' ;
800 END IF;
801
802 /* =============== START DEBUG LOG ================ */
803 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg4',
804 ' l_initial_entry --> ' || l_initial_entry);
805 /* =============== END DEBUG LOG ================== */
806
807 IF l_initial_entry = 'TRUE' THEN
808
809 -- Get the Current Position
810 BEGIN
811 OPEN c_cur_pos;
812 FETCH c_cur_pos INTO l_position_id,
813 l_business_group_id,
814 l_organization_id,
815 l_current_user_name;
816 CLOSE c_cur_pos ;
817
818 /* =============== START DEBUG LOG ================ */
819 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg5',
820 ' l_position_id --> ' || l_position_id ||
821 ' l_business_group_id --> ' || l_business_group_id ||
822 ' l_organization_id --> ' || l_organization_id ||
823 ' l_current_user_name --> ' || l_current_user_name);
824 /* =============== END DEBUG LOG ================== */
825
826 EXCEPTION
827 WHEN OTHERS THEN
828 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_NO_CURRENT_POS');
829 FND_MESSAGE.SET_TOKEN('USER_ID',g_userid);
830 /* =============== START DEBUG LOG ================ */
831 DEBUG_LOG_UNEXP_ERROR ('getparentposition.Unexp2','USER');
832 /* =============== END DEBUG LOG ================== */
833 RAISE cur_pos_not_found;
834 END;
835
836 /* =============== START DEBUG LOG ================ */
837 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg6',
838 ' Setting attribute text ');
839 /* =============== END DEBUG LOG ================== */
840
841 Wf_Engine.SetItemAttrText (itemtype => ItemType,
842 itemkey => ItemKey,
843 aname => 'CURRENT_POSITION_ID',
844 avalue => l_position_id);
845
846 /* =============== START DEBUG LOG ================ */
847 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg7',
848 ' l_position_id - CURRENT_POSITION_ID --> ' || l_position_id );
849 /* =============== END DEBUG LOG ================== */
850
851
852 Wf_Engine.SetItemAttrText (itemtype => ItemType,
853 itemkey => ItemKey,
854 aname => 'BUSINESS_GROUP_ID',
855 avalue => l_business_group_id);
856
857 /* =============== START DEBUG LOG ================ */
858 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg8',
859 ' l_business_group_id - BUSINESS_GROUP_ID --> ' || l_business_group_id );
860 /* =============== END DEBUG LOG ================== */
861
862 Wf_Engine.SetItemAttrText (itemtype => ItemType,
863 itemkey => ItemKey,
864 aname => 'ORGANIZATION_ID',
865 avalue => l_organization_id);
866
867 /* =============== START DEBUG LOG ================ */
868 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg9',
869 ' l_organization_id - ORGANIZATION_ID --> ' || l_organization_id );
870 /* =============== END DEBUG LOG ================== */
871
872 Wf_Engine.SetItemAttrText (itemtype => ItemType,
873 itemkey => ItemKey,
874 aname => 'CURRENT_USER_NAME',
875 avalue => l_current_user_name);
876
877 /* =============== START DEBUG LOG ================ */
878 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg10',
879 ' l_current_user_name - CURRENT_USER_NAME --> ' || l_current_user_name );
880 /* =============== END DEBUG LOG ================== */
881
882 -- Get the Position Hierarchy
883 BEGIN
884
885 /* =============== START DEBUG LOG ================ */
886 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg11',
887 ' Getting Position Hierarchy ');
888 /* =============== END DEBUG LOG ================== */
889
890 OPEN c_pos_hier(l_dossier_id);
891 FETCH c_pos_hier INTO l_hierarchy_id;
892 CLOSE c_pos_hier;
893
894 EXCEPTION
895
896 WHEN OTHERS THEN
897 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_NO_HIERARCHY');
898 FND_MESSAGE.SET_TOKEN('DOSSIER_ID',l_dossier_id);
899 /* =============== START DEBUG LOG ================ */
900 DEBUG_LOG_UNEXP_ERROR ('getparentposition.Unexp3','USER');
901 /* =============== END DEBUG LOG ================== */
902 RAISE pos_hier_not_found;
906 itemkey => ItemKey,
903 END;
904
905 Wf_Engine.SetItemAttrText (itemtype => ItemType,
907 aname => 'POS_STRUCTURE_ID',
908 avalue => l_hierarchy_id);
909
910 /* =============== START DEBUG LOG ================ */
911 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg12',
912 ' l_hierarchy_id -- POS_STRUCTURE_ID --> ' || l_hierarchy_id );
913 /* =============== END DEBUG LOG ================== */
914
915 -- Get the Position Hierarchy Version
916 BEGIN
917
918 /* =============== START DEBUG LOG ================ */
919 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg13',
920 ' Getting Position Hierarchy Version ');
921 /* =============== END DEBUG LOG ================== */
922
923 OPEN c_pos_hier_ver(l_hierarchy_id,
924 l_business_group_id);
925 FETCH c_pos_hier_ver INTO l_hierarchy_version_id;
926 CLOSE c_pos_hier_ver;
927
928 EXCEPTION
929 WHEN OTHERS THEN
930 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_NO_HIER_VER');
931 FND_MESSAGE.SET_TOKEN('HIERARCHY_ID',l_hierarchy_id);
932 FND_MESSAGE.SET_TOKEN('BUS_GRP_ID',l_business_group_id);
933 /* =============== START DEBUG LOG ================ */
934 DEBUG_LOG_UNEXP_ERROR ('getparentposition.Unexp4','USER');
935 /* =============== END DEBUG LOG ================== */
936 RAISE pos_hier_ver_not_found;
937 END;
938
939 Wf_Engine.SetItemAttrText (itemtype => ItemType,
940 itemkey => ItemKey,
941 aname => 'POS_STRUCTURE_VERSION_ID',
942 avalue => l_hierarchy_version_id);
943
944 /* =============== START DEBUG LOG ================ */
945 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg14',
946 ' l_hierarchy_version_id -- POS_STRUCTURE_VERSION_ID --> '
947 || l_hierarchy_version_id );
948 /* =============== END DEBUG LOG ================== */
949
950
951 -- Get the Top Position in the hierarchy
952 BEGIN
953
954 /* =============== START DEBUG LOG ================ */
955 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg15',
956 ' Get the Top Position in the hierarchy ');
957 /* =============== END DEBUG LOG ================== */
958
959 OPEN c_get_top_position(l_hierarchy_version_id,
960 l_business_group_id);
961 FETCH c_get_top_position INTO l_top_position_id;
962 CLOSE c_get_top_position;
963
964 EXCEPTION
965 WHEN OTHERS THEN
966 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_NO_HIER_TOP');
967 FND_MESSAGE.SET_TOKEN('HIER_VER_ID',l_hierarchy_version_id);
968 FND_MESSAGE.SET_TOKEN('BUS_GRP_ID',l_business_group_id);
969 /* =============== START DEBUG LOG ================ */
970 DEBUG_LOG_UNEXP_ERROR ('getparentposition.Unexp5','USER');
971 /* =============== END DEBUG LOG ================== */
972 RAISE top_pos_not_found;
973 END;
974
975 Wf_Engine.SetItemAttrText (itemtype => ItemType,
976 itemkey => ItemKey,
977 aname => 'TOP_POSITION_ID',
978 avalue => l_top_position_id);
979
980 /* =============== START DEBUG LOG ================ */
981 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg16',
982 ' l_top_position_id -- TOP_POSITION_ID --> '
983 || l_top_position_id );
984 /* =============== END DEBUG LOG ================== */
985
986 IF l_position_id = l_top_position_id THEN
987 -- Save the current position as the parent position so that the
988 -- notification for approval gets sent to the top level.
989
990 /* =============== START DEBUG LOG ================ */
991 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg17',
992 ' l_position_id --> ' || l_position_id || ' = ' ||
993 ' l_top_position_id --> ' || l_top_position_id );
994 /* =============== END DEBUG LOG ================== */
995
996 Wf_Engine.SetItemAttrText (itemtype => ItemType,
997 itemkey => ItemKey,
998 aname => 'PARENT_POSITION_ID',
999 avalue => l_position_id);
1000
1001 /* =============== START DEBUG LOG ================ */
1002 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg18',
1003 ' l_position_id -- PARENT_POSITION_ID --> ' || l_position_id);
1004 /* =============== END DEBUG LOG ================== */
1005
1006 ELSE -- creator is not at the top
1007
1008 /* =============== START DEBUG LOG ================ */
1009 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg19',
1010 ' creator is not at the top ' );
1011 /* =============== END DEBUG LOG ================== */
1012
1016 /* =============== START DEBUG LOG ================ */
1013 -- Get the parent for the current position
1014 BEGIN
1015
1017 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg20',
1018 ' Get the parent for the current position ' );
1019 /* =============== END DEBUG LOG ================== */
1020
1021 OPEN c_par_pos_id(l_hierarchy_version_id,
1022 l_position_id,
1023 l_business_group_id);
1024 FETCH c_par_pos_id INTO l_parent_position_id;
1025 CLOSE c_par_pos_id;
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_NO_PARENT_POS');
1030 FND_MESSAGE.SET_TOKEN('HIERARCHY_ID',l_hierarchy_id);
1031 FND_MESSAGE.SET_TOKEN('BUS_GRP_ID',l_business_group_id);
1032 FND_MESSAGE.SET_TOKEN('POSITION_ID',l_position_id);
1033 /* =============== START DEBUG LOG ================ */
1034 DEBUG_LOG_UNEXP_ERROR ('getparentposition.Unexp6','USER');
1035 /* =============== END DEBUG LOG ================== */
1036 RAISE par_pos_not_found;
1037 END;
1038
1039 Wf_Engine.SetItemAttrText (itemtype => ItemType,
1040 itemkey => ItemKey,
1041 aname => 'PARENT_POSITION_ID',
1042 avalue => l_parent_position_id);
1043
1044 /* =============== START DEBUG LOG ================ */
1045 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg21',
1046 ' l_parent_position_id -- PARENT_POSITION_ID --> '
1047 || TO_CHAR(l_parent_position_id));
1048 /* =============== END DEBUG LOG ================== */
1049
1050 END IF;
1051
1052 result := 'COMPLETE:HAS_PARENT';
1053
1054 /* =============== START DEBUG LOG ================ */
1055 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg22',
1056 ' result --> ' || result);
1057 /* =============== END DEBUG LOG ================== */
1058
1059 -- Flip the initial entry flag as subsequent entries will not be initial
1060 l_initial_entry := 'FALSE';
1061 Wf_Engine.SetItemAttrText (itemtype => itemtype,
1062 itemkey => itemkey,
1063 aname => 'INITIAL_ENTRY_FLAG',
1064 avalue => l_initial_entry);
1065
1066 /* =============== START DEBUG LOG ================ */
1067 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg23',
1068 ' l_initial_entry - INITIAL_ENTRY_FLAG ' || l_initial_entry);
1069 /* =============== END DEBUG LOG ================== */
1070
1071 ELSE -- This is not the initial entry
1072
1073 /* =============== START DEBUG LOG ================ */
1074 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg24',
1075 ' Not initial entry INTO GetParentPosition ');
1076 /* =============== END DEBUG LOG ================== */
1077
1078 -- FETCH the parent position id as the current position id
1079 l_position_id := Wf_Engine.GetItemAttrText (itemtype => ItemType,
1080 itemkey => ItemKey,
1081 aname => 'PARENT_POSITION_ID');
1082
1083 /* =============== START DEBUG LOG ================ */
1084 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg25',
1085 ' GetItemAttrText PARENT_POSITION_ID --> ' || l_position_id);
1086 /* =============== END DEBUG LOG ================== */
1087
1088
1089 l_top_position_id := Wf_Engine.GetItemAttrText (itemtype => ItemType,
1090 itemkey => ItemKey,
1091 aname => 'TOP_POSITION_ID');
1092
1093 /* =============== START DEBUG LOG ================ */
1094 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg26',
1095 ' GetItemAttrText TOP_POSITION_ID --> ' || l_top_position_id );
1096 /* =============== END DEBUG LOG ================== */
1097
1098 IF l_top_position_id = l_position_id THEN
1099 result := 'COMPLETE:NO_HIERS' ;
1100 /* =============== START DEBUG LOG ================ */
1101 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg27',
1102 ' l_top_position_id --> ' || l_top_position_id
1103 || ' = ' ||
1104 ' l_position_id --> ' || l_position_id ||
1105 ' result --> ' || result);
1106 /* =============== END DEBUG LOG ================== */
1107
1108 ELSE -- top != current
1109
1110 /* =============== START DEBUG LOG ================ */
1111 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg28',
1112 ' l_top_position_id --> ' || l_top_position_id
1113 || ' != ' ||
1114 ' l_position_id --> ' || l_position_id);
1115 /* =============== END DEBUG LOG ================== */
1116
1117 l_hierarchy_version_id :=
1118 Wf_Engine.GetItemAttrNumber ( itemtype => ItemType,
1122 /* =============== START DEBUG LOG ================ */
1119 itemkey => ItemKey,
1120 aname => 'POS_STRUCTURE_VERSION_ID');
1121
1123 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg29',
1124 ' GetItemAttrNumber - POS_STRUCTURE_VERSION_ID --> ' || TO_CHAR(l_hierarchy_version_id));
1125 /* =============== END DEBUG LOG ================== */
1126
1127 l_business_group_id :=
1128 Wf_Engine.GetItemAttrNumber ( itemtype => ItemType,
1129 itemkey => ItemKey,
1130 aname => 'BUSINESS_GROUP_ID');
1131
1132 /* =============== START DEBUG LOG ================ */
1133 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg29',
1134 ' GetItemAttrNumber - BUSINESS_GROUP_ID --> ' || TO_CHAR(l_business_group_id));
1135 /* =============== END DEBUG LOG ================== */
1136
1137 -- Get parent position
1138 BEGIN
1139
1140 /* =============== START DEBUG LOG ================ */
1141 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg30',
1142 ' Get parent position ');
1143 /* =============== END DEBUG LOG ================== */
1144
1145 OPEN c_par_pos_id(l_hierarchy_version_id,
1146 l_position_id,
1147 l_business_group_id);
1148 FETCH c_par_pos_id INTO l_parent_position_id;
1149 CLOSE c_par_pos_id;
1150
1151 EXCEPTION
1152 WHEN OTHERS THEN
1153 /* =============== START DEBUG LOG ================ */
1154 DEBUG_LOG_UNEXP_ERROR ('getparentposition.unexp7','DEFAULT');
1155 /* =============== END DEBUG LOG ================== */
1156 RAISE par_pos_not_found;
1157 END;
1158
1159 Wf_Engine.SetItemAttrText (itemtype => ItemType,
1160 itemkey => ItemKey,
1161 aname => 'PARENT_POSITION_ID',
1162 avalue => l_parent_position_id);
1163
1164 /* =============== START DEBUG LOG ================ */
1165 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg31',
1166 ' l_parent_position_id - PARENT_POSITION_ID --> ' || TO_CHAR(l_parent_position_id));
1167 /* =============== END DEBUG LOG ================== */
1168
1169 l_current_user_name :=
1170 Wf_Engine.GetItemAttrText (itemtype => ItemType,
1171 itemkey => ItemKey,
1172 aname => 'SELECTED_USER_NAME');
1173
1174 /* =============== START DEBUG LOG ================ */
1175 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg32',
1176 ' GetItemAttrText SELECTED_USER_NAME --> ' || l_current_user_name);
1177 /* =============== END DEBUG LOG ================== */
1178
1179 Wf_Engine.SetItemAttrText (itemtype => ItemType,
1180 itemkey => ItemKey,
1181 aname => 'CURRENT_USER_NAME',
1182 avalue => l_current_user_name);
1183
1184 /* =============== START DEBUG LOG ================ */
1185 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg33',
1186 ' l_current_user_name - CURRENT_USER_NAME ' || l_current_user_name);
1187 /* =============== END DEBUG LOG ================== */
1188
1189 result := 'COMPLETE:HAS_PARENT';
1190
1191 /* =============== START DEBUG LOG ================ */
1192 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg34',
1193 ' result --> ' || result);
1194 /* =============== END DEBUG LOG ================== */
1195
1196 END IF; -- top = current
1197
1198 END IF; -- initial entry
1199
1200 /* =============== START DEBUG LOG ================ */
1201 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg35',
1202 ' ** END GETPARENTPOSITION ** ');
1203 /* =============== END DEBUG LOG ================== */
1204
1205 EXCEPTION
1206 WHEN cur_pos_not_found OR
1207 pos_hier_not_found OR
1208 pos_hier_ver_not_found OR
1209 top_pos_not_found OR
1210 par_pos_not_found THEN
1211
1212 /* =============== START DEBUG LOG ================ */
1213 DEBUG_LOG_STRING (l_proc_level, 'getparentposition.Msg36',
1214 ' #### Into User defined EXCEPTION ');
1215 /* =============== END DEBUG LOG ================== */
1216
1217 Wf_Core.Context ('IGIDOSL', 'GetParentPosition', itemtype, itemkey,
1218 TO_CHAR(actid),funcmode,
1219 'Creator='||l_creator_name
1220 ||' Initflag='||l_initial_entry
1221 ||' error= '||l_error);
1222
1223 IF c_cur_pos%ISOPEN THEN
1224 CLOSE c_cur_pos;
1225 END IF ;
1226 IF c_pos_hier%ISOPEN THEN
1227 CLOSE c_pos_hier;
1228 END IF ;
1229 IF c_pos_hier_ver%ISOPEN THEN
1230 CLOSE c_pos_hier_ver;
1231 END IF ;
1232 IF c_get_top_position%ISOPEN THEN
1233 CLOSE c_get_top_position;
1234 END IF ;
1238
1235 IF c_par_pos_id%ISOPEN THEN
1236 CLOSE c_par_pos_id;
1237 END IF ;
1239 RAISE;
1240
1241 WHEN OTHERS THEN
1242 /* =============== START DEBUG LOG ================ */
1243 DEBUG_LOG_UNEXP_ERROR ('getparentposition.unexp1','DEFAULT');
1244 /* =============== END DEBUG LOG ================== */
1245
1246 result := NULL;
1247 Wf_Core.Context ('IGIDOSL', 'GetParentPosition', itemtype, itemkey,
1248 TO_CHAR(actid),funcmode);
1249
1250 IF c_cur_pos%ISOPEN THEN
1251 CLOSE c_cur_pos;
1252 END IF ;
1253 IF c_pos_hier%ISOPEN THEN
1254 CLOSE c_pos_hier;
1255 END IF ;
1256 IF c_pos_hier_ver%ISOPEN THEN
1257 CLOSE c_pos_hier_ver;
1258 END IF ;
1259 IF c_get_top_position%ISOPEN THEN
1260 CLOSE c_get_top_position;
1261 END IF ;
1262 IF c_par_pos_id%ISOPEN THEN
1263 CLOSE c_par_pos_id;
1264 END IF ;
1265
1266 RAISE;
1267
1268 END GetParentPosition;
1269
1270 /* ====================== APPROVE ========================= */
1271
1272 PROCEDURE Approve ( itemtype VARCHAR2,
1273 itemkey VARCHAR2,
1274 actid NUMBER,
1275 funcmode VARCHAR2,
1276 result OUT NOCOPY VARCHAR2)
1277 IS
1278 l_trx_status VARCHAR2(30);
1279 l_sob_id NUMBER;
1280
1281 l_dossier_num VARCHAR2(255)
1282 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1283 itemkey => itemkey,
1284 aname => 'DOSSIER_NUM');
1285
1286 l_user_id VARCHAR2(30) -- Changed FROM NUM to TEXT by bug 1635667
1287 := Wf_Engine.GetItemAttrText( itemtype => itemtype,
1288 itemkey => itemkey,
1289 aname => 'USER_ID');
1290
1291 l_responsibility_id VARCHAR2(30) -- Changed FROM NUM to TEXT by bug 1635667
1292 := Wf_Engine.GetItemAttrText( itemtype => itemtype,
1293 itemkey => itemkey,
1294 aname => 'RESPONSIBILITY_ID');
1295 encumbrance_error EXCEPTION;
1296
1297 CURSOR get_status
1298 IS
1299 SELECT meaning
1300 FROM igi_lookups
1301 WHERE lookup_type ='DOSSIER STATUS'
1302 AND lookup_code ='COMPLETE';
1303
1304 BEGIN
1305
1306 /* =============== START DEBUG LOG ================ */
1307 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg1',
1308 ' ** START APPROVE ** ');
1309 /* =============== END DEBUG LOG ================== */
1310
1311 IF funcmode <> 'RUN' THEN
1312
1313 /* =============== START DEBUG LOG ================ */
1314 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg2',
1315 ' funcmode <> RUN result COMPLETE');
1316 /* =============== END DEBUG LOG ================== */
1317
1318 result := 'COMPLETE';
1319 return;
1320 END IF;
1321
1322 -- WHEN the dossier has passed through the validation loop and
1323 -- been successfully authorised set the status to Complete.
1324 OPEN get_status;
1325 FETCH get_status INTO l_trx_status;
1326 CLOSE get_status;
1327
1328 /* =============== START DEBUG LOG ================ */
1329 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg3',
1330 ' l_trx_status --> ' || l_trx_status);
1331 /* =============== END DEBUG LOG ================== */
1332
1333 SELECT dtype.sob_id
1334 INTO l_sob_id
1335 FROM igi_dos_doc_types dtype,
1336 igi_dos_trx_headers thead
1337 WHERE thead.dossier_id = dtype.dossier_id
1338 AND thead.trx_number = l_dossier_num;
1339
1340 /* =============== START DEBUG LOG ================ */
1341 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg3',
1342 ' l_sob_id --> ' || l_sob_id);
1343 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg4',
1344 ' Calling igi_dis_funds.approve with ' ||
1345 ' l_dossier_num --> ' || l_dossier_num ||
1346 ' l_user_id --> ' || l_user_id ||
1347 ' l_responsibility_id --> ' || l_responsibility_id ||
1348 ' l_sob_id --> ' || l_sob_id);
1349 /* =============== END DEBUG LOG ================== */
1350
1351 IF igi_dos_funds.approve(l_dossier_num, l_user_id, l_responsibility_id, l_sob_id)
1352 THEN
1353
1354 UPDATE igi_dos_trx_headers trx
1355 SET trx.trx_status = l_trx_status,
1356 trx.last_update_date= sysdate
1357 WHERE trx.trx_number = l_dossier_num;
1358
1359 /* =============== START DEBUG LOG ================ */
1360 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg5',
1361 ' updating igi_dos_trx_headers ');
1362 /* =============== END DEBUG LOG ================== */
1363
1364 ELSE
1365 /* =============== START DEBUG LOG ================ */
1366 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg6',
1367 ' raising encumbrance_error ');
1368 /* =============== END DEBUG LOG ================== */
1369
1370 RAISE encumbrance_error;
1374
1371 END IF;
1372
1373 result := 'COMPLETE' ;
1375 /* =============== START DEBUG LOG ================ */
1376 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg7',
1377 ' result --> ' || result);
1378 DEBUG_LOG_STRING (l_proc_level, 'approve.Msg8',
1379 ' ** END APPROVE ** ');
1380 /* =============== END DEBUG LOG ================== */
1381
1382 EXCEPTION
1383
1384 WHEN encumbrance_error THEN
1385 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_ENCMBRC_ERROR');
1386 FND_MESSAGE.SET_TOKEN('DOSSIER_NUM',l_dossier_num);
1387 FND_MESSAGE.SET_TOKEN('USER_ID',l_user_id);
1388 FND_MESSAGE.SET_TOKEN('RESP_ID',l_responsibility_id);
1389 FND_MESSAGE.SET_TOKEN('SOB_ID',l_sob_id);
1390 /* =============== START DEBUG LOG ================ */
1391 DEBUG_LOG_UNEXP_ERROR ('approve.unexp1','USER');
1392 /* =============== END DEBUG LOG ================== */
1393
1394 result := NULL;
1395 Wf_Core.Context ('IGIDOSL', 'Reverse Encumbrances -Unable to create encumbrances for specified packet'
1396 , itemtype, itemkey , to_char(actid),funcmode
1397 , 'Dossier Trx Num ='||itemkey ) ;
1398 RAISE ;
1399
1400 WHEN OTHERS THEN
1401 /* =============== START DEBUG LOG ================ */
1402 DEBUG_LOG_UNEXP_ERROR ('approve.unexp2','DEFAULT');
1403 /* =============== END DEBUG LOG ================== */
1404
1405 result := NULL;
1406 Wf_Core.Context ('IGIDOSL', 'Approve', itemtype, itemkey , TO_CHAR(actid),funcmode) ;
1407 RAISE;
1408
1409 END Approve ;
1410
1411 /* ======================= REJECT ============================== */
1412
1413 PROCEDURE Reject ( itemtype VARCHAR2,
1414 itemkey VARCHAR2,
1415 actid NUMBER,
1416 funcmode VARCHAR2,
1417 result OUT NOCOPY VARCHAR2)
1418 IS
1419 l_trx_status VARCHAR2(80);
1420
1421 l_dossier_id VARCHAR2(255)
1422 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1423 itemkey => itemkey,
1424 aname => 'DOSSIER_NUM');
1425
1426 l_creator_name wf_notifications.responder%TYPE
1427 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1428 itemkey => itemkey,
1429 aname => 'CREATOR_NAME');
1430 BEGIN
1431
1432
1433 /* =============== START DEBUG LOG ================ */
1434 DEBUG_LOG_STRING (l_proc_level, 'reject.Msg1',
1438 IF funcmode <> 'RUN' THEN
1435 ' ** START REJECT ** ');
1436 /* =============== END DEBUG LOG ================== */
1437
1439
1440 /* =============== START DEBUG LOG ================ */
1441 DEBUG_LOG_STRING (l_proc_level, 'reject.Msg2',
1442 ' funcmode <> RUN result COMPLETE');
1443 /* =============== END DEBUG LOG ================== */
1444 result := 'COMPLETE';
1445 return;
1446 END IF;
1447
1448
1449 SELECT meaning INTO l_trx_status
1450 FROM igi_lookups
1451 WHERE lookup_type ='DOSSIER STATUS'
1452 and lookup_code ='REJECTED';
1453
1454 /* =============== START DEBUG LOG ================ */
1455 DEBUG_LOG_STRING (l_proc_level, 'reject.Msg3',
1456 ' l_trx_status --> ' || l_trx_status);
1457 /* =============== END DEBUG LOG ================== */
1458
1459 UPDATE IGI_DOS_TRX_HEADERS trx
1460 SET trx.trx_status = l_trx_status,
1461 trx.last_update_date= sysdate
1462 WHERE trx.trx_number = l_dossier_id;
1463
1464 /* =============== START DEBUG LOG ================ */
1465 DEBUG_LOG_STRING (l_proc_level, 'reject.Msg4',
1466 ' updating igi_dos_trx_headers ');
1467 /* =============== END DEBUG LOG ================== */
1468
1469 result := 'COMPLETE' ;
1470
1471 /* =============== START DEBUG LOG ================ */
1472 DEBUG_LOG_STRING (l_proc_level, 'reject.Msg5',
1473 ' result --> ' || result);
1474 DEBUG_LOG_STRING (l_proc_level, 'reject.Msg6',
1475 ' ** END REJECT ** ');
1476 /* =============== END DEBUG LOG ================== */
1477
1478 EXCEPTION
1479 WHEN OTHERS THEN
1480 /* =============== START DEBUG LOG ================ */
1481 DEBUG_LOG_UNEXP_ERROR ('reject.unexp1','USER');
1482 /* =============== END DEBUG LOG ================== */
1483 result := NULL;
1484 Wf_Core.Context ('IGIDOSL', 'Reject', itemtype, itemkey
1485 , to_char(actid),funcmode) ;
1486 RAISE;
1487 END Reject;
1488
1489 /* ==================== SENDAPPROVED ======================= */
1490
1491 PROCEDURE SendApproved ( itemtype IN VARCHAR2,
1492 itemkey IN VARCHAR2,
1493 actid IN NUMBER,
1494 funcmode IN VARCHAR2,
1495 result OUT NOCOPY VARCHAR2)
1496 IS
1497
1498 l_creator_name wf_notifications.responder%TYPE
1499 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1500 itemkey => itemkey,
1501 aname => 'CREATOR_NAME');
1502
1503 l_nid NUMBER ;
1504 unable_to_send_notify EXCEPTION ;
1505
1506 BEGIN
1507 -- This procedure sends a notification to the creator of the dossier
1508 -- every time a dossier is approved.
1509 -- This had to be done within a procedure because it was not possible
1510 -- to send a notification activity WHEN it was within a workflow loop
1511 -- (as the last activity in the loop). This was due to the way workflow
1512 -- resets the loop the last activity gets cancelled-see #bug 937429.
1513
1514 /* =============== START DEBUG LOG ================ */
1515 DEBUG_LOG_STRING (l_proc_level, 'SendApproved.Msg1',
1516 ' ** START SENDAPPROVED ** ');
1517 /* =============== END DEBUG LOG ================== */
1518
1519
1520 IF funcmode <> 'RUN' THEN
1521 /* =============== START DEBUG LOG ================ */
1522 DEBUG_LOG_STRING (l_proc_level, 'SendApproved.Msg2',
1523 ' funcmode <> RUN result COMPLETE');
1524 /* =============== END DEBUG LOG ================== */
1525 result := 'COMPLETE' ;
1526 return;
1527 END IF ;
1528
1529 l_nid := Wf_Notification.Send(
1530 l_creator_name
1531 , itemtype
1532 ,'ALL_APPROVED'
1536 ,'Send The Dossier Approved Message To The Creator.'
1533 , null
1534 , 'WF_ENGINE.CB'
1535 , itemtype||':'||itemkey||':'||to_char(actid)
1537 , null
1538 ) ;
1539
1540 /* =============== START DEBUG LOG ================ */
1541 DEBUG_LOG_STRING (l_proc_level, 'SendApproved.Msg3',
1542 ' Notification sent ');
1543 /* =============== END DEBUG LOG ================== */
1544
1545 IF l_nid = 0 OR l_nid < 0 THEN
1546 /* =============== START DEBUG LOG ================ */
1547 DEBUG_LOG_STRING (l_proc_level, 'SendApproved.Msg4',
1548 ' raising unable_to_send_notify');
1549 /* =============== END DEBUG LOG ================== */
1550 RAISE unable_to_send_notify ;
1551 END IF ;
1552
1553 result := 'COMPLETE' ;
1554
1555 /* =============== START DEBUG LOG ================ */
1556 DEBUG_LOG_STRING (l_proc_level, 'SendApproved.Msg4',
1557 ' result --> ' || result);
1558 DEBUG_LOG_STRING (l_proc_level, 'SendApproved.Msg5',
1559 ' ** END SENDAPPROVED ** ');
1560 /* =============== END DEBUG LOG ================== */
1561
1562
1563 EXCEPTION
1564 WHEN unable_to_send_notify THEN
1565 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_FAIL_SEND_NOTC');
1566 FND_MESSAGE.SET_TOKEN('NOTICE_ID',l_nid);
1567 /* =============== START DEBUG LOG ================ */
1568 DEBUG_LOG_UNEXP_ERROR ('SendApproved.Unexp1','USER');
1569 /* =============== END DEBUG LOG ================== */
1570 result := NULL;
1571 Wf_Core.Context ( 'IGIDOSL','SendApproved', itemtype, itemkey
1572 , to_char(actid),funcmode
1573 ,'Failed to Send Dossier Approved Notice- Notice Id:'||to_char(l_nid)) ;
1574 RAISE ;
1575
1576 WHEN OTHERS THEN
1577 /* =============== START DEBUG LOG ================ */
1578 DEBUG_LOG_UNEXP_ERROR ('SendApproved.Unexp2','DEFAULT');
1579 /* =============== END DEBUG LOG ================== */
1580 result := NULL;
1581 Wf_Core.Context ( 'IGIDOSL','SendApproved', itemtype, itemkey
1582 , to_char(actid),funcmode) ;
1583 RAISE ;
1584
1585 END SendApproved ;
1586
1590 itemkey IN VARCHAR2,
1587 /* ===================== SENDREJECTED ======================== */
1588
1589 PROCEDURE SendRejected ( itemtype IN VARCHAR2,
1591 actid IN NUMBER,
1592 funcmode IN VARCHAR2,
1593 result OUT NOCOPY VARCHAR2)
1594 IS
1595
1596 l_creator_name wf_notifications.responder%TYPE
1597 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1598 itemkey => itemkey,
1599 aname => 'CREATOR_NAME');
1600
1601 l_nid NUMBER ;
1602 unable_to_send_notify EXCEPTION ;
1603
1604 BEGIN
1605
1606 -- This procedure sends a notification to the creator of the dossier
1607 -- time a dossier is rejected.
1608 -- This had to be done within a procedure because it was not possible
1609 -- to send a notification activity WHEN it was within a workflow loop
1610 -- (as the last activity in the loop). This was due to the way workflow
1611 -- resets the loop the last activity gets cancelled-see #bug 937429.
1612
1613 /* =============== START DEBUG LOG ================ */
1614 DEBUG_LOG_STRING (l_proc_level, 'SendRejected.Msg1',
1615 ' ** START SENDREJECTED ** ');
1616 /* =============== END DEBUG LOG ================== */
1617
1618
1619 IF funcmode <> 'RUN' THEN
1620 /* =============== START DEBUG LOG ================ */
1621 DEBUG_LOG_STRING (l_proc_level, 'SendRejected.Msg2',
1622 ' funcmode <> RUN result COMPLETE');
1626 END IF ;
1623 /* =============== END DEBUG LOG ================== */
1624 result := 'COMPLETE' ;
1625 return;
1627
1628 l_nid := Wf_Notification.Send(
1629 l_creator_name
1630 , itemtype
1631 ,'AUTHORISER_REJECTION'
1632 , null
1633 , 'WF_ENGINE.CB'
1634 , itemtype||':'||itemkey||':'||to_char(actid)
1635 ,'Send The Dossier Rejection Message To The Creator.'
1636 , null
1637 ) ;
1638
1639 /* =============== START DEBUG LOG ================ */
1640 DEBUG_LOG_STRING (l_proc_level, 'SendRejected.Msg3',
1641 ' Notification sent ');
1642 /* =============== END DEBUG LOG ================== */
1643
1644 IF l_nid = 0 OR l_nid < 0 THEN
1645 /* =============== START DEBUG LOG ================ */
1646 DEBUG_LOG_STRING (l_proc_level, 'SendRejected.Msg4',
1647 ' raising unable_to_send_notify');
1648 /* =============== END DEBUG LOG ================== */
1649 RAISE unable_to_send_notify ;
1650 END IF ;
1651
1652 result := 'COMPLETE' ;
1653
1654 /* =============== START DEBUG LOG ================ */
1655 DEBUG_LOG_STRING (l_proc_level, 'SendRejected.Msg4',
1656 ' result --> ' || result);
1657 DEBUG_LOG_STRING (l_proc_level, 'SendRejected.Msg5',
1658 ' ** END SENDREJECTED ** ');
1659 /* =============== END DEBUG LOG ================== */
1660
1661
1662 EXCEPTION
1663 WHEN unable_to_send_notify THEN
1664 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_FAIL_SEND_NOTC');
1665 FND_MESSAGE.SET_TOKEN('NOTICE_ID',l_nid);
1666 /* =============== START DEBUG LOG ================ */
1667 DEBUG_LOG_UNEXP_ERROR ('SendRejected.Unexp1','USER');
1668 /* =============== END DEBUG LOG ================== */
1669 Wf_Core.Context ('IGIDOSL','SendRejected', itemtype, itemkey
1670 , to_char(actid),funcmode, 'Failed to Send Dossier Reject Notice- Notice Id:'||to_char(l_nid)) ;
1671 RAISE ;
1672
1673 WHEN OTHERS THEN
1674 /* =============== START DEBUG LOG ================ */
1675 DEBUG_LOG_UNEXP_ERROR ('SendRejected.Unexp2','DEFAULT');
1676 /* =============== END DEBUG LOG ================== */
1677 result := NULL;
1678 Wf_Core.Context ('IGIDOSL','SendRejected', itemtype, itemkey
1679 , to_char(actid),funcmode) ;
1680 RAISE ;
1681
1682 END SendRejected ;
1683
1684 /* ======================= CREATELIST ====================== */
1685
1686 PROCEDURE CreateList ( itemtype VARCHAR2,
1687 itemkey VARCHAR2,
1688 actid NUMBER,
1689 funcmode VARCHAR2,
1690 result OUT NOCOPY VARCHAR2)
1691 IS
1692 l_user_count NUMBER := 0;
1693 l_local_table Wf_Directory.Usertable;
1694 l_size NUMBER;
1695 l_list VARCHAR2(1000) := 'ALL';
1696
1697 l_user_name fnd_user.user_name%TYPE;
1698
1699 l_next_position per_positions.name%TYPE
1700 := Wf_Engine.GetItemAttrText( itemtype => itemtype,
1701 itemkey => itemkey,
1702 aname => 'NEXT_POSITION');
1703
1704 l_parent_position_id per_positions.position_id%TYPE
1705 := Wf_Engine.GetItemAttrText (itemtype => ItemType,
1706 itemkey => ItemKey,
1707 aname => 'PARENT_POSITION_ID');
1708
1709 l_business_group_id hr_all_positions_f.business_group_id%TYPE
1710 := Wf_Engine.GetItemAttrText( itemtype => itemtype,
1711 itemkey => itemkey,
1712 aname => 'BUSINESS_GROUP_ID');
1713
1714 l_organization_id hr_all_positions_f.organization_id%TYPE
1715 := Wf_Engine.GetItemAttrText( itemtype => itemtype,
1716 itemkey => itemkey,
1717 aname => 'ORGANIZATION_ID');
1718
1719 CURSOR c_user_list
1720 IS
1721 select fu.user_name user_name
1722 FROM hr_all_positions_f hap,
1723 per_all_assignments_f paa,
1724 fnd_user fu ,
1725 per_people_f p,
1726 per_periods_of_service b
1727 WHERE
1728 paa.person_id = p.person_id
1729 AND paa.primary_flag = 'Y'
1730 AND paa.period_of_service_id = b.period_of_service_id
1731 AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
1732 AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
1733 AND (b.actual_termination_date >= trunc(sysdate) or b.actual_termination_date is null)
1734 AND p.employee_number IS NOT NULL
1735 and fu.start_date <= SYSDATE
1736 and NVL(fu.end_date,SYSDATE) >= SYSDATE
1737 and fu.employee_id IS NOT NULL
1738 and fu.employee_id = p.person_id
1739 and p.business_group_id = paa.business_group_id
1740 and p.business_group_id = l_business_group_id
1741 and paa.organization_id = l_organization_id
1745 and paa.position_id = hap.position_id
1742 and paa.assignment_type = 'E'
1743 and paa.business_group_id = hap.business_group_id
1744 and paa.position_id IS NOT NULL
1746 and paa.organization_id = hap.organization_id
1747 and hap.date_effective <= SYSDATE
1748 and NVL(hap.date_end, SYSDATE) >= SYSDATE
1749 and NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID')
1750 and hap.position_id = l_parent_position_id;
1751
1752
1753
1754 e_user_list_not_found EXCEPTION;
1755
1756 BEGIN
1757
1758 /* =============== START DEBUG LOG ================ */
1759 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg1',
1760 ' ** START CREATELIST ** ');
1761 /* =============== END DEBUG LOG ================== */
1762
1763 IF funcmode <> 'RUN' THEN
1764 /* =============== START DEBUG LOG ================ */
1765 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg2',
1766 ' funcmode <> RUN result COMPLETE');
1767 /* =============== END DEBUG LOG ================== */
1768 result := 'COMPLETE';
1769 return;
1770 END IF;
1771
1772 /* =============== START DEBUG LOG ================ */
1773 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg3',
1774 ' FETCH the users for the position ');
1775 /* =============== END DEBUG LOG ================== */
1776
1777 -- FETCH the users for the position
1778 BEGIN
1779
1780 FOR I IN c_user_list
1781 LOOP
1782 l_user_count := l_user_count + 1;
1783 l_list := l_list || fnd_global.local_chr(10) || I.user_name;
1784 l_user_name := I.user_name;
1785
1786 /* =============== START DEBUG LOG ================ */
1787 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg4',
1788 ' user_name --> ' || I.user_name);
1789 /* =============== END DEBUG LOG ================== */
1790
1791 END LOOP;
1792
1793 /* =============== START DEBUG LOG ================ */
1794 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg4',
1795 ' out of loop ');
1796 /* =============== END DEBUG LOG ================== */
1797
1798 IF l_user_count = 1 THEN
1799 /* =============== START DEBUG LOG ================ */
1800 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg5',
1801 ' One user exists --> ' || l_user_name);
1802 /* =============== END DEBUG LOG ================== */
1803 l_list := l_user_name;
1804 END IF;
1805
1806 EXCEPTION
1807 WHEN OTHERS THEN
1808 /* =============== START DEBUG LOG ================ */
1809 DEBUG_LOG_UNEXP_ERROR ('CreateList.Unexp1','DEFAULT');
1810 /* =============== END DEBUG LOG ================== */
1811 RAISE e_user_list_not_found;
1812 END;
1813
1814 Wf_Engine.SetItemAttrText (itemtype => Itemtype,
1815 itemkey => Itemkey,
1816 aname => 'USER_LIST',
1817 avalue => l_list);
1818
1819 /* =============== START DEBUG LOG ================ */
1820 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg6',
1821 ' l_list - USER_LIST --> ' || l_list);
1822 /* =============== END DEBUG LOG ================== */
1823
1824 IF l_user_count = 1 THEN
1825 result := 'COMPLETE:N' ;
1826 ELSE
1827 result := 'COMPLETE:Y' ;
1828 END IF;
1829
1830 /* =============== START DEBUG LOG ================ */
1831 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg7',
1832 ' result --> ' || result);
1833 /* =============== END DEBUG LOG ================== */
1834
1835 Wf_Engine.SetItemAttrText ( itemtype => Itemtype,
1836 itemkey => Itemkey,
1837 aname => 'PICKED_ROLE',
1838 avalue => l_list) ;
1839
1840 /* =============== START DEBUG LOG ================ */
1841 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg8',
1842 ' l_list - PICKED_ROLE --> ' || l_list);
1843 DEBUG_LOG_STRING (l_proc_level, 'CreateList.Msg9',
1844 ' ** END CREATELIST ** ');
1845 /* =============== END DEBUG LOG ================== */
1846
1847 EXCEPTION
1848 WHEN e_user_list_not_found THEN
1849 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_NO_USER_LIST');
1850 /* =============== START DEBUG LOG ================ */
1851 DEBUG_LOG_UNEXP_ERROR ('CreateList.Unexp2','USER');
1852 /* =============== END DEBUG LOG ================== */
1853 Wf_Core.Context ('IGIDOSL', 'CreateList cursor', itemtype, itemkey
1854 , to_char(actid),funcmode) ;
1855 RAISE;
1856 WHEN OTHERS THEN
1857 /* =============== START DEBUG LOG ================ */
1858 DEBUG_LOG_UNEXP_ERROR ('CreateList.Unexp3','DEFAULT');
1859 /* =============== END DEBUG LOG ================== */
1860 result := NULL;
1861 Wf_Core.Context ('IGIDOSL', 'CreateList', itemtype, itemkey
1862 , to_char(actid),funcmode) ;
1863 Raise;
1864
1865 END CreateList;
1866
1867 /* ======================= CHECKLIST ========================== */
1871 actid NUMBER,
1868
1869 PROCEDURE CheckList ( itemtype VARCHAR2,
1870 itemkey VARCHAR2,
1872 funcmode VARCHAR2,
1873 result OUT NOCOPY VARCHAR2)
1874 IS
1875
1876
1877 l_user_list VARCHAR2(250)
1878 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1879 itemkey => itemkey,
1880 aname => 'USER_LIST');
1881
1882 l_next_position per_positions.name%TYPE;
1883
1884 l_parent_position_id hr_all_positions_f.position_id%TYPE
1888
1885 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1886 itemkey => itemkey,
1887 aname => 'PARENT_POSITION_ID');
1889 l_next_authoriser wf_notifications.responder%TYPE ;
1890 l_pos NUMBER := 0;
1891
1892 no_next_authoriser EXCEPTION ;
1893
1894 BEGIN
1895
1896 /* =============== START DEBUG LOG ================ */
1897 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg1',
1898 ' ** START CHECKLIST ** ');
1899 /* =============== END DEBUG LOG ================== */
1900
1901 IF funcmode <> 'RUN' THEN
1902 /* =============== START DEBUG LOG ================ */
1903 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg2',
1904 ' funcmode <> RUN result COMPLETE');
1905 /* =============== END DEBUG LOG ================== */
1906 result := 'COMPLETE';
1907 return;
1908 END IF;
1909
1910 -- The returned value is held in item attribute which is the same name as
1911 -- the workflow message attribute with source 'Respond'.
1912
1913 l_next_authoriser := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
1914 itemkey => itemkey,
1915 aname => 'NEXT_AUTHORISER');
1916
1917 l_next_authoriser := UPPER(l_next_authoriser) ;
1918
1919 /* =============== START DEBUG LOG ================ */
1920 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg3',
1921 ' GetItemAttrText NEXT_AUTHORISER --> ' || l_next_authoriser);
1922 /* =============== END DEBUG LOG ================== */
1923
1924 l_pos := INSTR(l_user_list, l_next_authoriser, 1) ;
1925
1926 /* =============== START DEBUG LOG ================ */
1927 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg4',
1928 ' l_pos --> ' || l_pos);
1929 /* =============== END DEBUG LOG ================== */
1930
1931 IF l_pos <> 0 THEN
1932
1933 /* =============== START DEBUG LOG ================ */
1934 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg5',
1935 ' l_pos <> 0 ');
1936 /* =============== END DEBUG LOG ================== */
1937
1938 IF l_next_authoriser = 'ALL'
1939 THEN
1940
1941 l_next_position := 'POS:'||TO_CHAR(l_parent_position_id);
1942 l_next_authoriser := l_next_position;
1943 /* =============== START DEBUG LOG ================ */
1944 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg6',
1945 ' l_next_position --> ' || l_next_position ||
1946 ' l_next_authoriser --> ' || l_next_authoriser);
1947 /* =============== END DEBUG LOG ================== */
1948
1949 END IF ;
1950
1951 Wf_Engine.SetItemAttrText ( itemtype => Itemtype,
1952 itemkey => Itemkey,
1953 aname => 'PICKED_ROLE',
1954 avalue => l_next_authoriser) ;
1955
1956 /* =============== START DEBUG LOG ================ */
1957 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg7',
1958 ' l_next_authoriser - PICKED_ROLE --> ' || l_next_authoriser);
1959 /* =============== END DEBUG LOG ================== */
1960
1961 Wf_Engine.SetItemAttrText ( itemtype => Itemtype,
1962 itemkey => Itemkey,
1963 aname => 'SELECTED_USER_NAME',
1964 avalue => l_next_authoriser) ;
1965
1966 /* =============== START DEBUG LOG ================ */
1967 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg8',
1968 ' l_next_authoriser - SELECTED_USER_NAME --> ' || l_next_authoriser);
1969 /* =============== END DEBUG LOG ================== */
1970
1971 Result := 'COMPLETE:T';
1972
1973 ELSE
1974
1975 Result := 'COMPLETE:F';
1976
1977 END IF;
1978
1979 /* =============== START DEBUG LOG ================ */
1983 ' ** END CHECKLIST ** ');
1980 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg9',
1981 ' result --> ' || result);
1982 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg10',
1987 WHEN no_next_authoriser THEN
1984 /* =============== END DEBUG LOG ================== */
1985
1986 EXCEPTION
1988
1989 /* =============== START DEBUG LOG ================ */
1990 DEBUG_LOG_STRING (l_proc_level, 'CheckList.Msg11',
1991 ' Exception no_next_authoriser' );
1992 /* =============== END DEBUG LOG ================== */
1993 Wf_Core.Context ('IGIDOSL', 'CheckList', itemtype, itemkey
1994 , to_char(actid),funcmode, ' Could not find a response value for next authoriser') ;
1995 RAISE ;
1996
1997 WHEN OTHERS THEN
1998 /* =============== START DEBUG LOG ================ */
1999 DEBUG_LOG_UNEXP_ERROR ('CheckList.Unexp1','DEFAULT');
2000 /* =============== END DEBUG LOG ================== */
2001 result := NULL;
2002 Wf_Core.Context ('IGIDOSL', 'CheckList', itemtype, itemkey
2003 , to_char(actid),funcmode) ;
2004 Raise;
2005
2006 END CheckList;
2007
2008 /* ======================== UNRESERVEFUNDS ===================== */
2009
2010 PROCEDURE UnreserveFunds ( itemtype VARCHAR2,
2011 itemkey VARCHAR2,
2012 actid NUMBER,
2013 funcmode VARCHAR2,
2014 result OUT NOCOPY VARCHAR2)
2015 IS
2016 l_trx_status VARCHAR2(30);
2017
2018 l_trx_number VARCHAR2(30)
2019 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
2020 itemkey => itemkey,
2021 aname => 'DOSSIER_NUM');
2022 l_user_id VARCHAR2(30)
2023 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
2024 itemkey => itemkey,
2025 aname => 'USER_ID');
2026
2027 l_responsibility_id VARCHAR2(30)
2028 := Wf_Engine.GetItemAttrText ( itemtype => itemtype,
2029 itemkey => itemkey,
2030 aname => 'RESPONSIBILITY_ID');
2031 l_sob_id number;
2032 unreserve_error EXCEPTION ;
2033
2034 BEGIN
2035
2039 /* =============== END DEBUG LOG ================== */
2036 /* =============== START DEBUG LOG ================ */
2037 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg1',
2038 ' ** START UNRESERVEFUNDS ** ');
2040
2041 IF funcmode <> 'RUN' THEN
2042 /* =============== START DEBUG LOG ================ */
2043 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg2',
2044 ' funcmode <> RUN result COMPLETE');
2045 /* =============== END DEBUG LOG ================== */
2046 result := 'COMPLETE';
2047 return;
2048 END IF;
2049
2050 SELECT dtype.SOB_ID
2051 INTO l_sob_id
2052 FROM IGI_DOS_DOC_TYPES dtype,
2053 igi_dos_trx_headers thead
2054 WHERE thead.dossier_id = dtype.dossier_id
2055 and thead.trx_number = l_trx_number;
2056
2057 /* =============== START DEBUG LOG ================ */
2058 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg3',
2059 ' l_sob_id --> ' || l_sob_id);
2060 /* =============== END DEBUG LOG ================== */
2061
2062 IF NOT IGI_DOS_FUNDS.REJECT(l_trx_number, l_user_id, l_responsibility_id, l_sob_id)
2063 THEN
2064
2065 /* =============== START DEBUG LOG ================ */
2066 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg4',
2067 ' Raise unreserve error ');
2068 /* =============== END DEBUG LOG ================== */
2069
2070 RAISE unreserve_error ;
2071 ELSE
2072
2073 SELECT meaning INTO l_trx_status
2074 FROM igi_lookups
2075 WHERE lookup_type ='DOSSIER STATUS'
2076 and lookup_code ='REJECTED';
2077
2078 /* =============== START DEBUG LOG ================ */
2079 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg5',
2080 ' l_trx_status --> ' || l_trx_status);
2081 /* =============== END DEBUG LOG ================== */
2082
2083 UPDATE IGI_DOS_TRX_HEADERS trx
2084 SET trx.trx_status = l_trx_status,
2085 trx.last_update_date= sysdate
2089 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg6',
2086 WHERE trx.trx_number = l_trx_number ;
2087
2088 /* =============== START DEBUG LOG ================ */
2090 ' updated igi_dos_trx_headers ');
2091 /* =============== END DEBUG LOG ================== */
2092
2093 END IF ;
2094
2095 /* =============== START DEBUG LOG ================ */
2096 DEBUG_LOG_STRING (l_proc_level, 'UnreserveFunds.Msg7',
2097 ' ** END UNRESERVEFUNDS ** ');
2098 /* =============== END DEBUG LOG ================== */
2099
2100 EXCEPTION
2101 WHEN unreserve_error THEN
2102 FND_MESSAGE.SET_NAME('IGI','IGI_DOS_WKF_UNRSRV_ERROR');
2103 FND_MESSAGE.SET_TOKEN('TRANS_NUM',l_trx_number);
2104 FND_MESSAGE.SET_TOKEN('USER_ID',l_user_id);
2105 FND_MESSAGE.SET_TOKEN('RESP_ID',l_responsibility_id);
2106 FND_MESSAGE.SET_TOKEN('SOB_ID',l_sob_id);
2107 /* =============== START DEBUG LOG ================ */
2108 DEBUG_LOG_UNEXP_ERROR ('UnreserveFunds.Unexp1','USER');
2109 /* =============== END DEBUG LOG ================== */
2110 Wf_Core.Context ('IGIDOSL', 'UnreserveFunds -Unable to reserve funds for specified packet'
2111 , itemtype, itemkey , to_char(actid),funcmode
2112 , 'Dossier Trx Num ='||l_trx_number ) ;
2113 RAISE ;
2114
2115 WHEN OTHERS THEN
2116 /* =============== START DEBUG LOG ================ */
2117 DEBUG_LOG_UNEXP_ERROR ('UnreserveFunds.Unexp2','DEFAULT');
2118 /* =============== END DEBUG LOG ================== */
2119 result := NULL;
2120 Wf_Core.Context ('IGIDOSL', 'UnreserveFunds', itemtype, itemkey
2121 , to_char(actid),funcmode) ;
2122 RAISE;
2123
2124 END UnreserveFunds ;
2125
2126 /* ============================= FRAMEDOSTABLE ================================= */
2127
2128
2129 PROCEDURE FrameDosTable ( Document IN OUT NOCOPY CLOB)
2130 IS
2131
2132 l_table_header VARCHAR2(32000);
2133
2134 /* === Message variables === */
2135 l_trans_dets_head VARCHAR2(50);
2136 l_line_head VARCHAR2(50);
2137 l_source_budget_head VARCHAR2(50);
2138 l_source_account_head VARCHAR2(50);
2139 l_source_period_head VARCHAR2(50);
2140 l_source_amount_head VARCHAR2(50);
2141 l_dest_budget_head VARCHAR2(50);
2142 l_dest_account_head VARCHAR2(50);
2143 l_dest_period_head VARCHAR2(50);
2144 l_dest_amount_head VARCHAR2(50);
2145
2146 BEGIN
2147
2148 /* =============== START DEBUG LOG ================ */
2149 DEBUG_LOG_STRING (l_proc_level, 'FrameDosTable.Msg1',
2150 ' ** START FRAMEDOSTABLE ** ');
2151 DEBUG_LOG_STRING (l_proc_level, 'FrameDosTable.Msg2',
2152 ' Getting messages for Table Heading');
2153 /* =============== END DEBUG LOG ================== */
2154
2155 -- Getting the Headings FROM fnd message.
2156
2157 fnd_message.set_name ('IGI', 'IGI_DOS_TRAN_DETS');
2158 l_trans_dets_head := fnd_message.get;
2159 fnd_message.set_name ('IGI', 'IGI_DOS_LINE');
2160 l_line_head := fnd_message.get;
2161 fnd_message.set_name ('IGI', 'IGI_DOS_SOURCE_BUDGET');
2162 l_source_budget_head := fnd_message.get;
2163 fnd_message.set_name ('IGI', 'IGI_DOS_SOURCE_ACCOUNT');
2164 l_source_account_head := fnd_message.get;
2165 fnd_message.set_name ('IGI', 'IGI_DOS_SOURCE_PERIOD');
2166 l_source_period_head := fnd_message.get;
2167 fnd_message.set_name ('IGI', 'IGI_DOS_SOURCE_AMOUNT');
2168 l_source_amount_head := fnd_message.get;
2169 fnd_message.set_name ('IGI', 'IGI_DOS_DEST_BUDGET');
2170 l_dest_budget_head := fnd_message.get;
2171 fnd_message.set_name ('IGI', 'IGI_DOS_DEST_ACCOUNT');
2172 l_dest_account_head := fnd_message.get;
2173 fnd_message.set_name ('IGI', 'IGI_DOS_DEST_PERIOD');
2174 l_dest_period_head := fnd_message.get;
2175 fnd_message.set_name ('IGI', 'IGI_DOS_DEST_AMOUNT');
2176 l_dest_amount_head := fnd_message.get;
2177
2178 /* =============== START DEBUG LOG ================ */
2179 DEBUG_LOG_STRING (l_proc_level, 'FrameDosTable.Msg3',
2180 ' Heading Done - Now Framing the table ');
2181 /* =============== END DEBUG LOG ================== */
2182
2183 l_table_header := '<br><font color='||th_fontcolor||'face='||
2184 th_fontface||'><b>'|| l_trans_dets_head || '</b>'; -- <hr>';
2185
2186 l_table_header := l_table_header ||'<table bgcolor=' ||table_bgcolor
2192 l_table_header := l_table_header || '<tr bgcolor='||th_bgcolor||'>';
2187 ||' width=' ||table_width
2188 ||' border=' ||table_border
2189 ||' cellpadding=' ||table_cellpadding
2190 ||' cellspacing=' ||table_cellspacing||'>';
2191
2193 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2194 th_fontface||' size='||th_fontsize||'><b>' || l_line_head || '</b></td>';
2195 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2196 th_fontface||' size='||th_fontsize||'><b>' || l_source_budget_head || '</b></td>';
2197 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2198 th_fontface||' size='||th_fontsize||'><b>' || l_source_account_head || '</b></td>';
2199 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2200 th_fontface||' size='||th_fontsize||'><b>' || l_source_period_head || '</b></td>';
2204 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2201 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2202 th_fontface||' size='||th_fontsize||'><b>' || l_source_amount_head
2203 || '(' || g_currency_code || ')'|| '</b></td>';
2205 th_fontface||' size='||th_fontsize||'><b>' || l_dest_budget_head || '</b></td>';
2206 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2207 th_fontface||' size='||th_fontsize||'><b>' || l_dest_account_head || '</b></td>';
2208 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2209 th_fontface||' size='||th_fontsize||'><b>' || l_dest_period_head || '</b></td>';
2210 l_table_header := l_table_header || '<td align="left"><font color='||th_fontcolor||' face='||
2211 th_fontface||' size='||th_fontsize||'><b>' || l_dest_amount_head
2212 || '(' || g_currency_code || ')'|| '</b></td>';
2213 l_table_header := l_table_header || '</tr>';
2214
2215 /* =============== START DEBUG LOG ================ */
2216 DEBUG_LOG_STRING (l_proc_level, 'FrameDosTable.Msg4',
2217 ' l_table_header --> ' || substr(l_table_header,1,3800));
2218 /* =============== END DEBUG LOG ================== */
2219
2220 WF_NOTIFICATION.WriteToClob(Document,l_table_header);
2221
2222 /* =============== START DEBUG LOG ================ */
2223 DEBUG_LOG_STRING (l_proc_level, 'FrameDosTable.Msg5',
2224 ' ** END FRAMEDOSTABLE ** ');
2225 /* =============== END DEBUG LOG ================== */
2226
2227 EXCEPTION
2228 WHEN OTHERS THEN
2229 /* =============== START DEBUG LOG ================ */
2230 DEBUG_LOG_UNEXP_ERROR ('FrameDosTable.Unexp1','DEFAULT');
2231 /* =============== END DEBUG LOG ================== */
2232 APP_EXCEPTION.RAISE_EXCEPTION;
2233 END FrameDosTable;
2234
2235
2236 /* ============================= ADDTOTALTOTABLE ================================= */
2237
2238
2239 PROCEDURE AddTotalToTable ( Document IN OUT NOCOPY CLOB,
2240 p_total IN VARCHAR2)
2241 IS
2242
2243 l_frame_total VARCHAR2(32000);
2244
2245 BEGIN
2246
2247 /* =============== START DEBUG LOG ================ */
2248 DEBUG_LOG_STRING (l_proc_level, 'AddTotalToTable.Msg1',
2249 ' ** START ADDTOTALTOTABLE ** ');
2250 DEBUG_LOG_STRING (l_proc_level, 'AddTotalToTable.Msg2',
2251 ' Adding text and the total for destination ');
2252 /* =============== END DEBUG LOG ================== */
2253
2254 l_frame_total := l_frame_total || '<td bgcolor='||th_bgcolor||' align="right" COLSPAN=8 ><font color='||
2255 th_fontcolor||' face='||th_fontface||' size='||th_fontsize ||'><b>'|| g_total_text ||'</b></td>';
2256
2257 l_frame_total := l_frame_total || '<td bgcolor='||td_bgcolor||' align="right"><font color='||
2258 td_fontcolor ||' face='||td_fontface||' size='||td_fontsize||'>'||p_total||'</td>';
2259
2260 l_frame_total := l_frame_total || '</tr>';
2261
2262 /* =============== START DEBUG LOG ================ */
2263 DEBUG_LOG_STRING (l_proc_level, 'AddTotalToTable.Msg3',
2264 ' l_frame_total --> ' || substr(l_frame_total,1,3900));
2265 /* =============== END DEBUG LOG ================== */
2266
2267 WF_NOTIFICATION.WriteToClob(Document,l_frame_total);
2268
2269 /* =============== START DEBUG LOG ================ */
2270 DEBUG_LOG_STRING (l_proc_level, 'AddTotalToTable.Msg4',
2271 ' ** END ADDTOTALTOTABLE ** ');
2272 /* =============== END DEBUG LOG ================== */
2273
2274 EXCEPTION
2275 WHEN OTHERS THEN
2276 /* =============== START DEBUG LOG ================ */
2277 DEBUG_LOG_UNEXP_ERROR ('AddTotalToTable.Unexp1','DEFAULT');
2278 /* =============== END DEBUG LOG ================== */
2279 APP_EXCEPTION.RAISE_EXCEPTION;
2280
2281 END AddTotalToTable;
2282
2283 /* ============================= DOSSIER_TRANSACTION_DETAIL ================================= */
2284
2285 Procedure dossier_transaction_detail(document_id in VARCHAR2,
2286 display_type in VARCHAR2,
2287 document in out NOCOPY CLOB,
2288 document_type in out NOCOPY VARCHAR2)
2289 IS
2290
2291 l_table_frame VARCHAR2(32000) := NULL;
2292 l_trx_detail VARCHAR2(32000) := NULL;
2293 l_dossier_id VARCHAR2(255);
2294 l_dossier_num VARCHAR2(250);
2295 l_dossier_trx_id NUMBER;
2296
2297 l_itemtype VARCHAR2(30);
2298 l_itemkey VARCHAR2(60);
2299 l_document_old VARCHAR2(32000) := NULL;
2300 l_line_num NUMBER := 0;
2301
2302 l_source_formatted_amount VARCHAR2(100);
2303 l_dest_formatted_amount VARCHAR2(100);
2304
2305 l_dest_total NUMBER;
2306 l_source_total NUMBER;
2307
2308 l_source_formatted_total VARCHAR2(100);
2309 l_dest_formatted_total VARCHAR2(100);
2310
2311 l_new_source VARCHAR2(1);
2312
2313 CURSOR c_get_trx_id
2314 IS
2315 SELECT trx_id FROM igi_dos_trx_headers
2316 WHERE trx_number = l_dossier_num
2317 AND dossier_id = l_dossier_id;
2318
2322 NVL(s.funds_available,0) - NVL(s.new_balance,0) amount,
2319 CURSOR c_get_sources
2320 IS
2321 SELECT s.budget_name,
2323 s.visible_segments,
2324 s.period_name,
2325 s.source_id,
2326 s.source_trx_id
2327 FROM igi_dos_trx_sources s
2331 WHERE d.trx_id = l_dossier_trx_id
2328 WHERE trx_id = l_dossier_trx_id
2329 AND EXISTS (SELECT budget_name
2330 FROM igi_dos_trx_dest d
2332 AND source_id = s.source_id
2333 AND source_trx_id = s.source_trx_id);
2334
2335 CURSOR c_get_destinations(p_source_id NUMBER, p_source_trx_id NUMBER)
2336 IS
2337 SELECT budget_name,
2338 ABS(NVL(funds_available,0) - NVL(new_balance,0)) amount,
2339 visible_segments,
2340 period_name,
2341 source_id,
2342 source_trx_id,
2343 destination_id,
2344 dest_trx_id
2345 FROM igi_dos_trx_dest
2346 WHERE trx_id = l_dossier_trx_id
2347 AND source_id = p_source_id
2348 AND source_trx_id = p_source_trx_id
2349 ORDER BY destination_id,
2350 dest_trx_id;
2351
2352 BEGIN
2353
2354 /* =============== START DEBUG LOG ================ */
2355 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg1',
2356 ' ** START DOSSIER_TRANSACTION_DETAIL ** ' ) ;
2357 /* =============== END DEBUG LOG ================== */
2358
2359 l_itemtype := substr(document_id,1,instr(document_id,':')-1);
2360 l_itemkey := substr(document_id,instr(document_id,':')+1);
2361
2362 /* =============== START DEBUG LOG ================ */
2363 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg2',
2364 ' l_itemtype --> ' || l_itemtype ) ;
2365 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg3',
2366 ' l_itemkey --> ' || l_itemkey ) ;
2367 /* =============== END DEBUG LOG ================== */
2368
2369 -- set of books id.
2370 g_sob_id := FND_PROFILE.VALUE ('GL_SET_OF_BKS_ID');
2371
2372
2373 /* =============== START DEBUG LOG ================ */
2374 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg3.1',
2375 ' g_sob_id --> ' || g_sob_id);
2376 /* =============== END DEBUG LOG ================== */
2377
2378 -- currency code.
2379 OPEN c_curr(g_sob_id);
2380 FETCH c_curr INTO g_currency_code;
2381 CLOSE c_curr;
2382
2383 /* =============== START DEBUG LOG ================ */
2384 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg3.2',
2385 ' g_curency_code --> ' || g_currency_code);
2386 /* =============== END DEBUG LOG ================== */
2387
2388
2389 l_dossier_num
2390 := Wf_Engine.GetItemAttrText ( itemtype => l_itemtype,
2391 itemkey => l_itemkey,
2392 aname => 'DOSSIER_NUM');
2393
2394 /* =============== START DEBUG LOG ================ */
2395 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg4',
2396 ' l_dossier_num --> ' || l_dossier_num);
2397 /* =============== END DEBUG LOG ================== */
2398
2399 l_dossier_id
2400 := Wf_Engine.GetItemAttrNumber ( itemtype => l_itemtype,
2401 itemkey => l_itemkey,
2402 aname => 'DOSSIER_ID');
2403
2404 /* =============== START DEBUG LOG ================ */
2405 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg5',
2406 ' l_dossier_id --> ' || l_dossier_id);
2407 /* =============== END DEBUG LOG ================== */
2408
2409 OPEN c_get_trx_id;
2410 FETCH c_get_trx_id INTO l_dossier_trx_id;
2411 CLOSE c_get_trx_id;
2412
2413 /* =============== START DEBUG LOG ================ */
2414 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg6',
2415 ' l_dossier_trx_id --> ' || l_dossier_trx_id);
2416 /* =============== END DEBUG LOG ================== */
2417
2418 FrameDosTable (Document);
2419
2420 /* =============== START DEBUG LOG ================ */
2421 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg7',
2422 ' Copying table headings in to clob ');
2423 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg8',
2424 ' starrting for loop ');
2425 /* =============== END DEBUG LOG ================== */
2426
2427 l_source_total := 0;
2428
2429 -- getting grand total test message.
2433 FOR source_rec IN c_get_sources LOOP
2430 fnd_message.set_name ('IGI','IGI_DOS_TOTAL');
2431 g_total_text := fnd_message.get;
2432
2434
2435 /* =============== START DEBUG LOG ================ */
2436 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg9'
2437 , ' Source_id --> ' || source_rec.source_id);
2438 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg10',
2439 ' Source_trx_id --> ' || source_rec.source_trx_id);
2440 /* =============== END DEBUG LOG ================== */
2441
2442 l_line_num := l_line_num + 1;
2443 l_new_source := 'Y';
2444 l_dest_total := 0;
2445
2446 FOR dest_rec IN c_get_destinations(source_rec.source_id, source_rec.source_trx_id) LOOP
2447
2448
2449 /* =============== START DEBUG LOG ================ */
2450 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg11',
2451 ' l_line_num --> ' || l_line_num);
2452 /* =============== END DEBUG LOG ================== */
2453
2454 l_trx_detail := '<tr>';
2455
2456 IF l_new_source = 'Y' THEN
2457
2458 /* =============== START DEBUG LOG ================ */
2459 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg12',
2460 ' l_new_source --> ' || l_new_source);
2461 /* =============== END DEBUG LOG ================== */
2462
2463 -- Source details
2464 l_new_source := 'N';
2465 l_source_formatted_amount := TO_CHAR(source_rec.amount, FND_CURRENCY.Get_Format_Mask(g_currency_code,22));
2466 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2467 ||' face='||td_fontface||' size='||td_fontsize||'>' || to_char(l_line_num) || '</td>';
2468 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2469 ||' face='||td_fontface||' size='||td_fontsize||'>' || source_rec.budget_name || '</td>';
2470 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2474 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="right"><font color='||td_fontcolor
2471 ||' face='||td_fontface||' size='||td_fontsize||'>' || source_rec.visible_segments || '</td>';
2472 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2473 ||' face='||td_fontface||' size='||td_fontsize||'>' || source_rec.period_name || '</td>';
2475 ||' face='||td_fontface||' size='||td_fontsize||'>' || l_source_formatted_amount || '</td>';
2476
2477 ELSE
2478
2479 /* =============== START DEBUG LOG ================ */
2480 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg13',
2481 ' l_new_source --> ' || l_new_source);
2482 /* =============== END DEBUG LOG ================== */
2483
2484 -- Blank Source details
2485 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2486 ||' face='||td_fontface||' size='||td_fontsize||'>' || ' ' || '</td>';
2487 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2488 ||' face='||td_fontface||' size='||td_fontsize||'>' || ' ' || '</td>';
2489 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2490 ||' face='||td_fontface||' size='||td_fontsize||'>' || ' ' || '</td>';
2494 ||' face='||td_fontface||' size='||td_fontsize||'>' || ' ' || '</td>';
2491 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2492 ||' face='||td_fontface||' size='||td_fontsize||'>' || ' ' || '</td>';
2493 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="right"><font color='||td_fontcolor
2495
2496 END IF;
2497
2498 /* =============== START DEBUG LOG ================ */
2499 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg14',
2500 ' Framing destination details ');
2501 /* =============== END DEBUG LOG ================== */
2502
2503 -- Destination details
2504 l_dest_formatted_amount := TO_CHAR(dest_rec.amount, FND_CURRENCY.Get_Format_Mask(g_currency_code,22));
2505 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2506 ||' face='||td_fontface||' size='||td_fontsize||'>' || dest_rec.budget_name || '</td>';
2507 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2508 ||' face='||td_fontface||' size='||td_fontsize||'>' || dest_rec.visible_segments || '</td>';
2509 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor
2510 ||' face='||td_fontface||' size='||td_fontsize||'>' || dest_rec.period_name || '</td>';
2511 l_trx_detail := l_trx_detail || '<td bgcolor='||td_bgcolor||' align="right"><font color='||td_fontcolor
2512 ||' face='||td_fontface||' size='||td_fontsize||'>' || l_dest_formatted_amount || '</td>';
2513 l_trx_detail := l_trx_detail || '</tr>';
2514
2515 l_dest_total := l_dest_total + dest_rec.amount;
2516
2517 /* =============== START DEBUG LOG ================ */
2518 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg15',
2519 ' source_rec.budget_name --> ' || source_rec.budget_name);
2520 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg16',
2521 ' source_rec.visible_segments --> ' || source_rec.visible_segments);
2522 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg17',
2523 ' source_budget_name --> ' || source_rec.budget_name);
2524 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg18',
2525 ' source_rec.amount --> ' || l_source_formatted_amount);
2526 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg19',
2527 ' dest_rec.budget_name --> ' || dest_rec.budget_name);
2528 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg20',
2529 ' dest_rec.visible_segments --> ' || dest_rec.visible_segments);
2530 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg21',
2531 ' dest_rec.period_name --> ' || dest_rec.period_name);
2532 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg22',
2533 ' dest_rec.amount --> ' || l_dest_formatted_amount);
2534 /* =============== END DEBUG LOG ================== */
2535
2536 WF_NOTIFICATION.WriteToClob(Document,l_trx_detail);
2537
2538 /* =============== START DEBUG LOG ================ */
2539 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg23',
2540 ' l_trx_detail updated to clob ');
2541 /* =============== END DEBUG LOG ================== */
2542
2543 END LOOP; -- destinations
2544
2545 /* =============== START DEBUG LOG ================ */
2546 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg24',
2547 ' Calling AddTotalToTable to set the destination total ');
2548 /* =============== END DEBUG LOG ================== */
2549
2550 -- Adding total for every destination.
2551 l_dest_formatted_total := TO_CHAR(l_dest_total, FND_CURRENCY.Get_Format_Mask(g_currency_code,22));
2552 AddTotalToTable (Document, l_dest_formatted_total);
2553
2554 l_source_total := l_source_total + source_rec.amount;
2555
2556 END LOOP; -- sources
2557
2558 -- getting grand total test message.
2559 fnd_message.set_name ('IGI','IGI_DOS_GRAND_TOTAL');
2560 g_total_text := fnd_message.get;
2561
2562 /* =============== START DEBUG LOG ================ */
2563 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg24.1',
2564 ' Calling AddTotalToTable to set the grand total ');
2565 /* =============== END DEBUG LOG ================== */
2566
2567 -- Adding the grand total.
2568 l_source_formatted_total := TO_CHAR(l_source_total, FND_CURRENCY.Get_Format_Mask(g_currency_code,22));
2569 AddTotalToTable (Document, l_source_formatted_total);
2570
2571 /* =============== START DEBUG LOG ================ */
2577 WF_NOTIFICATION.WriteToClob(Document,l_trx_detail);
2572 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg25',
2573 ' Out of loop ' ) ;
2574 /* =============== END DEBUG LOG ================== */
2575
2576 l_trx_detail := '</table><br>';
2578
2579
2580 /* =============== START DEBUG LOG ================ */
2581 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg26',
2582 ' Copied to CLOB ' ) ;
2583 DEBUG_LOG_STRING (l_proc_level, 'dossier_transaction_detail.Msg27',
2584 ' ** END DOSSIER_TRANSACTION_DETAIL ** ' ) ;
2585 /* =============== END DEBUG LOG ================== */
2586
2587 EXCEPTION
2588 WHEN OTHERS THEN
2589 /* =============== START DEBUG LOG ================ */
2590 DEBUG_LOG_UNEXP_ERROR ('dossier_transaction_detail.Unexp1','DEFAULT');
2591 /* =============== END DEBUG LOG ================== */
2592 RAISE;
2593
2594 END dossier_transaction_detail;
2595
2596 /* ======================= REWINDINPROCESS ================================= */
2597
2598 --
2599 -- Rewinds the 'In Process' status of the Dossier Transaction
2600 -- to 'Creating'
2601 --
2602 PROCEDURE RewindInProcess( itemtype IN VARCHAR2,
2603 itemkey IN VARCHAR2,
2604 actid IN NUMBER,
2605 funcmode IN VARCHAR2,
2606 result OUT NOCOPY VARCHAR2 ) IS
2607
2608 l_trx_status igi_lookups.meaning%TYPE;
2609 l_dossier_num igi_dos_trx_headers.trx_number%TYPE :=
2610 Wf_Engine.GetItemAttrText ( itemtype => itemtype,
2611 itemkey => itemkey,
2612 aname => 'DOSSIER_NUM');
2613
2614 CURSOR c_meaning IS
2615 SELECT meaning
2616 FROM igi_lookups
2617 WHERE lookup_type = 'DOSSIER STATUS'
2618 AND lookup_code = 'CREATING';
2619
2620 BEGIN
2621
2622 /* =============== START DEBUG LOG ================ */
2623 DEBUG_LOG_STRING (l_proc_level, 'RewindInProcess.Msg1',
2624 ' ** START REWINDINPROCESS ** ');
2625 /* =============== END DEBUG LOG ================== */
2626
2627 -- FETCH the translated value for 'Creating'
2628 OPEN c_meaning;
2629 FETCH c_meaning INTO l_trx_status;
2630 CLOSE c_meaning;
2631
2632 /* =============== START DEBUG LOG ================ */
2633 DEBUG_LOG_STRING (l_proc_level, 'RewindInProcess.Msg2',
2634 ' l_trx_status --> ' || l_trx_status);
2635 /* =============== END DEBUG LOG ================== */
2636
2637 -- Rewind the 'In Process' status to 'Creating'
2638 UPDATE igi_dos_trx_headers trx
2639 SET trx.trx_status = l_trx_status,
2640 trx.last_update_date= sysdate
2641 WHERE trx.trx_number = l_dossier_num;
2642
2643 /* =============== START DEBUG LOG ================ */
2644 DEBUG_LOG_STRING (l_proc_level, 'RewindInProcess.Msg3',
2645 ' updating igi_dos_trx_headers ');
2646 /* =============== END DEBUG LOG ================== */
2647
2648 COMMIT;
2649
2650 /* =============== START DEBUG LOG ================ */
2651 DEBUG_LOG_STRING (l_proc_level, 'RewindInProcess.Msg4',
2652 ' result --> COMPLETE ');
2653 /* =============== END DEBUG LOG ================== */
2654
2655 result := 'COMPLETE';
2656
2657 /* =============== START DEBUG LOG ================ */
2658 DEBUG_LOG_STRING (l_proc_level, 'RewindInProcess.Msg5',
2659 ' ** END REWINDINPROCESS ** ');
2660 /* =============== END DEBUG LOG ================== */
2661
2662 EXCEPTION
2663 WHEN OTHERS THEN
2664 /* =============== START DEBUG LOG ================ */
2665 DEBUG_LOG_UNEXP_ERROR ('RewindInProcess.Unexp1','DEFAULT');
2666 /* =============== END DEBUG LOG ================== */
2670
2667 result := NULL;
2668 RAISE;
2669 END RewindInProcess;
2671 /* ===================== ISEMPLOYEE **********************/
2672
2673 --
2674 -- Validates if dossier approval launcher is an employee
2675 --
2676
2677 PROCEDURE IsEmployee( itemtype IN VARCHAR2,
2678 itemkey IN VARCHAR2,
2679 actid IN NUMBER,
2680 funcmode IN VARCHAR2,
2681 result OUT NOCOPY VARCHAR2 ) IS
2682
2683 l_emp_id NUMBER(15);
2684
2685 l_user_id NUMBER(15) := Wf_Engine.GetItemAttrText
2686 ( itemtype => itemtype,
2687 itemkey => itemkey,
2688 aname => 'USER_ID');
2689
2690 CURSOR c_emp_id (p_user_id fnd_user.user_id%TYPE)
2691 IS
2692 SELECT employee_id
2693 FROM fnd_user
2694 WHERE user_id = p_user_id;
2695
2696 BEGIN
2697
2698 /* =============== START DEBUG LOG ================ */
2699 DEBUG_LOG_STRING (l_proc_level, 'IsEmployee.Msg1',
2700 ' ** START ISEMPLOYEE ** ');
2701 /* =============== END DEBUG LOG ================== */
2702
2703 IF funcmode <> 'RUN' THEN
2704 /* =============== START DEBUG LOG ================ */
2705 DEBUG_LOG_STRING (l_proc_level, 'IsEmployee.Msg2',
2706 ' funcmode <> RUN result COMPLETE');
2707 /* =============== END DEBUG LOG ================== */
2708 result := 'COMPLETE';
2709 return;
2710 END IF;
2711
2712 -- Get the employee id of the dossier approval launcher
2713 OPEN c_emp_id(l_user_id);
2714 FETCH c_emp_id INTO l_emp_id;
2715 CLOSE c_emp_id;
2716
2717 /* =============== START DEBUG LOG ================ */
2718 DEBUG_LOG_STRING (l_proc_level, 'IsEmployee.Msg3',
2719 ' l_emp_id --> ' || l_emp_id);
2720 /* =============== END DEBUG LOG ================== */
2721
2722 IF l_emp_id IS NULL THEN
2723 -- The launcher is not an employee
2724 result := 'COMPLETE:N';
2725
2726 ELSE
2727 -- Save the employee id for subsequent validations
2728 Wf_Engine.SetItemAttrText ( itemtype => itemtype,
2729 itemkey => itemkey,
2730 aname => 'EMPLOYEE_ID',
2731 avalue => l_emp_id);
2732
2736 /* =============== END DEBUG LOG ================== */
2733 /* =============== START DEBUG LOG ================ */
2734 DEBUG_LOG_STRING (l_proc_level, 'IsEmployee.Msg4',
2735 ' SetItemAttrText EMPLOYEE_ID to ' || l_emp_id);
2737
2738 -- The launcher is an employee
2739 result := 'COMPLETE:Y';
2740 END IF;
2741
2742 /* =============== START DEBUG LOG ================ */
2743 DEBUG_LOG_STRING (l_proc_level, 'IsEmployee.Msg5',
2744 ' result --> ' || result);
2745 DEBUG_LOG_STRING (l_proc_level, 'IsEmployee.Msg6',
2746 ' ** END ISEMPLOYEE ** ');
2747 /* =============== END DEBUG LOG ================== */
2748
2749 EXCEPTION
2750 WHEN OTHERS THEN
2751 /* =============== START DEBUG LOG ================ */
2752 DEBUG_LOG_UNEXP_ERROR ('RewindInProcess.Unexp1','DEFAULT');
2753 /* =============== END DEBUG LOG ================== */
2754 result := NULL;
2755 RAISE;
2756
2757 END IsEmployee;
2758
2759 /* ========================= HASPOSITION ===================== */
2760
2761 --
2762 -- Validates if dossier approval launcher has a position
2763 -- assignment.
2764 --
2765 PROCEDURE HasPosition( itemtype IN VARCHAR2,
2766 itemkey IN VARCHAR2,
2767 actid IN NUMBER,
2768 funcmode IN VARCHAR2,
2769 result OUT NOCOPY VARCHAR2 ) IS
2770
2771
2772 CURSOR c_get_position(p_emp_id per_employees_current_x.employee_id%TYPE)
2773 IS
2774 select hap.position_id,
2775 hap.name,
2776 hap.business_group_id,
2777 hap.organization_id
2778 FROM
2779 hr_all_positions_f hap,
2780 per_all_assignments_f paa,
2781 per_people_f p,
2782 per_periods_of_service b
2783 WHERE
2784 p.person_id = p_emp_id
2785 AND paa.person_id = p.person_id
2786 AND paa.primary_flag = 'Y'
2790 AND (b.actual_termination_date>= trunc(sysdate) or b.actual_termination_date is null)
2787 AND paa.period_of_service_id = b.period_of_service_id
2788 AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
2789 AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
2791 AND p.employee_number IS NOT NULL
2792 and p.business_group_id = paa.business_group_id
2793 and paa.assignment_type = 'E'
2794 and paa.business_group_id = hap.business_group_id
2795 and paa.position_id IS NOT NULL
2796 and paa.position_id = hap.position_id
2797 and paa.organization_id = hap.organization_id
2798 and hap.date_effective <= SYSDATE
2799 and NVL(hap.date_end, SYSDATE) >= SYSDATE
2800 and NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID') ;
2801
2802
2803 l_position_id hr_all_positions_f.position_id%TYPE;
2804 l_position_name hr_all_positions_f.name%TYPE;
2805 l_business_group_id hr_all_positions_f.business_group_id%TYPE;
2806 l_organization_id hr_all_positions_f.organization_id%TYPE;
2807
2808 l_emp_id NUMBER(15) := Wf_Engine.GetItemAttrText
2809 ( itemtype => itemtype,
2810 itemkey => itemkey,
2811 aname => 'EMPLOYEE_ID');
2812
2813 BEGIN
2814
2815 /* =============== START DEBUG LOG ================ */
2816 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg1',
2817 ' ** END HASPOSITION ** ');
2818 /* =============== END DEBUG LOG ================== */
2819
2820 IF funcmode <> 'RUN' THEN
2821 /* =============== START DEBUG LOG ================ */
2822 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg2',
2823 ' funcmode <> RUN result COMPLETE');
2824 /* =============== END DEBUG LOG ================== */
2825 result := 'COMPLETE';
2826 return;
2827 END IF;
2828
2829 -- FETCH position for employee
2830 OPEN c_get_position(l_emp_id);
2831 FETCH c_get_position INTO l_position_id
2832 ,l_position_name
2833 ,l_business_group_id
2834 ,l_organization_id;
2835
2836
2837 /* =============== START DEBUG LOG ================ */
2838 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg3',
2839 ' Get position for l_emp_id --> ' || l_emp_id);
2840 /* =============== END DEBUG LOG ================== */
2841
2842 IF (c_get_position%NOTFOUND) THEN
2843
2844 result := 'COMPLETE:N';
2845 ELSE
2846
2847 Wf_Engine.SetItemAttrText (itemtype => ItemType,
2848 itemkey => ItemKey,
2849 aname => 'CURRENT_POSITION_ID',
2850 avalue => l_position_id);
2851
2852 /* =============== START DEBUG LOG ================ */
2853 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg4',
2854 ' SetItemAttrText CURRENT_POSITION_ID -> ' || l_position_id );
2855 /* =============== END DEBUG LOG ================== */
2856
2860 avalue => l_business_group_id);
2857 Wf_Engine.SetItemAttrText (itemtype => ItemType,
2858 itemkey => ItemKey,
2859 aname => 'BUSINESS_GROUP_ID',
2861
2862 /* =============== START DEBUG LOG ================ */
2863 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg5',
2864 ' SetItemAttrText BUSINESS_GROUP_ID -> ' || l_business_group_id);
2865 /* =============== END DEBUG LOG ================== */
2866
2867 Wf_Engine.SetItemAttrText (itemtype => ItemType,
2868 itemkey => ItemKey,
2869 aname => 'ORGANIZATION_ID',
2870 avalue => l_organization_id);
2871
2872 /* =============== START DEBUG LOG ================ */
2873 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg6',
2874 ' SetItemAttrText ORGANIZATION_ID -> ' || l_organization_id);
2875 /* =============== END DEBUG LOG ================== */
2876
2877 result := 'COMPLETE:Y';
2878
2879 END IF;
2880
2881 /* =============== START DEBUG LOG ================ */
2882 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg7',
2883 ' result --> ' || result );
2884 /* =============== END DEBUG LOG ================== */
2885
2886 IF (c_get_position%ISOPEN) THEN
2887 CLOSE c_get_position;
2888 END IF;
2889
2890 /* =============== START DEBUG LOG ================ */
2891 DEBUG_LOG_STRING (l_proc_level, 'HasPosition.Msg8',
2892 ' ** END HASPOSITION ** ');
2893 /* =============== END DEBUG LOG ================== */
2894
2895 EXCEPTION
2896 WHEN OTHERS THEN
2897 /* =============== START DEBUG LOG ================ */
2898 DEBUG_LOG_UNEXP_ERROR ('HasPosition.Unexp1','DEFAULT');
2899 /* =============== END DEBUG LOG ================== */
2900 result := NULL;
2901 RAISE;
2902 END HasPosition;
2903
2904 /* ======================== POSITIONINHIERARCHY ***********************/
2905
2906 --
2907 -- Validates if dossier approval launcher position is in the
2908 -- hierarchy attached to the dossier type.
2909 --
2910 PROCEDURE PositionInHierarchy( itemtype IN VARCHAR2,
2911 itemkey IN VARCHAR2,
2912 actid IN NUMBER,
2913 funcmode IN VARCHAR2,
2914 result OUT NOCOPY VARCHAR2 ) IS
2915
2916 l_position_id hr_all_positions_f.position_id%TYPE :=
2917 Wf_Engine.GetItemAttrText (itemtype => ItemType,
2918 itemkey => ItemKey,
2919 aname => 'CURRENT_POSITION_ID');
2920
2921 l_business_group_id hr_all_positions_f.business_group_id%TYPE :=
2922 Wf_Engine.GetItemAttrText (itemtype => ItemType,
2923 itemkey => ItemKey,
2924 aname => 'BUSINESS_GROUP_ID');
2925
2926 l_organization_id hr_all_positions_f.organization_id%TYPE :=
2927 Wf_Engine.GetItemAttrText (itemtype => ItemType,
2928 itemkey => ItemKey,
2929 aname => 'ORGANIZATION_ID');
2930
2931 l_dossier_id igi_dos_doc_types.dossier_id%TYPE :=
2932 Wf_Engine.GetItemAttrNumber (itemtype => ItemType,
2933 itemkey => ItemKey,
2934 aname => 'DOSSIER_ID');
2935
2936 l_valid BOOLEAN := TRUE;
2937
2938 l_hierarchy_id
2939 per_position_structures.position_structure_id%TYPE;
2940 l_hierarchy_version_id
2941 per_pos_structure_versions.pos_structure_version_id%TYPE;
2942 l_pos_structure_element_id
2943 per_pos_structure_elements.pos_structure_element_id%TYPE;
2944
2945 -- Cursor to FETCH position hierarchy
2946 CURSOR c_pos_hier (p_dossier_id igi_dos_doc_types.dossier_id%TYPE) IS
2947 SELECT hierarchy_id
2948 FROM igi_dos_doc_types
2949 WHERE dossier_id = p_dossier_id;
2950
2951 -- Cursor to FETCH current version of position hierarchy
2955 SELECT pos_structure_version_id
2952 CURSOR c_pos_hier_ver
2953 (p_hierarchy_id igi_dos_doc_types.hierarchy_id%TYPE,
2954 p_business_group_id hr_all_positions_f.business_group_id%TYPE) IS
2956 FROM per_pos_structure_versions
2957 WHERE position_structure_id = p_hierarchy_id
2958 AND SYSDATE BETWEEN date_FROM AND NVL(date_to, SYSDATE)
2959 AND business_group_id = p_business_group_id
2960 AND version_number =
2961 (SELECT MAX(version_number)
2962 FROM per_pos_structure_versions
2963 WHERE position_structure_id = p_hierarchy_id
2964 AND SYSDATE BETWEEN date_FROM AND NVL(date_to,SYSDATE)
2965 AND business_group_id = p_business_group_id);
2966
2967 -- Cursor to FETCH current version of position hierarchy
2968 CURSOR c_is_pos_in_hier
2969 (p_business_group_id
2970 hr_all_positions_f.business_group_id%TYPE,
2971 p_pos_structure_ver_id
2972 per_pos_structure_elements.pos_structure_version_id%TYPE,
2973 p_position_id per_all_positions.position_id%TYPE) IS
2974 SELECT pos_structure_element_id
2975 FROM per_pos_structure_elements
2976 WHERE pos_structure_version_id = p_pos_structure_ver_id
2977 AND business_group_id = p_business_group_id
2978 AND (subordinate_position_id = p_position_id OR
2979 parent_position_id = p_position_id);
2980
2981 BEGIN
2982
2983 /* =============== START DEBUG LOG ================ */
2984 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg1',
2985 ' ** END POSITIONINHIERARCHY ** ');
2986 /* =============== END DEBUG LOG ================== */
2987
2988 IF funcmode <> 'RUN' THEN
2989 /* =============== START DEBUG LOG ================ */
2990 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg2',
2991 ' funcmode <> RUN result COMPLETE');
2992 /* =============== END DEBUG LOG ================== */
2993 result := 'COMPLETE';
2994 return;
2995 END IF;
2996
2997 -- Get the position Hierarchy
2998 OPEN c_pos_hier(l_dossier_id);
2999 FETCH c_pos_hier INTO l_hierarchy_id;
3000
3001 /* =============== START DEBUG LOG ================ */
3002 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg3',
3003 ' Getting position Hierarchy for --> ' || l_dossier_id);
3004 /* =============== END DEBUG LOG ================== */
3005
3006 IF (c_pos_hier%NOTFOUND) THEN
3007 l_valid := FALSE;
3008 ELSE
3009 Wf_Engine.SetItemAttrText (itemtype => ItemType,
3010 itemkey => ItemKey,
3011 aname => 'POS_STRUCTURE_ID',
3012 avalue => l_hierarchy_id);
3013
3014 /* =============== START DEBUG LOG ================ */
3015 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg4',
3016 ' SetItemAttrText POS_STRUCTURE_ID as ' || l_hierarchy_id);
3017 /* =============== END DEBUG LOG ================== */
3018
3019 result := 'COMPLETE:Y';
3020 l_valid := TRUE;
3021 END IF;
3022
3023 IF (c_pos_hier%ISOPEN) THEN
3024 CLOSE c_pos_hier;
3025 END IF;
3026
3027 IF l_valid THEN
3028
3029 /* =============== START DEBUG LOG ================ */
3030 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg5',
3031 ' l_valid --> TRUE ');
3032 /* =============== END DEBUG LOG ================== */
3033
3034 -- Get the current version of position Hierarchy
3035 OPEN c_pos_hier_ver(l_hierarchy_id,
3036 l_business_group_id);
3037 FETCH c_pos_hier_ver INTO l_hierarchy_version_id;
3038
3039 /* =============== START DEBUG LOG ================ */
3040 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg6',
3041 ' Getting hierarchy for l_hierarchy_id --> '||l_hierarchy_id
3042 ||' l_business_group_id --> '||l_business_group_id);
3043 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg7',
3044 ' l_hierarchy_version_id --> ' || l_hierarchy_version_id );
3045 /* =============== END DEBUG LOG ================== */
3046
3047 IF (c_pos_hier_ver%NOTFOUND) THEN
3048
3049 result := 'COMPLETE:N';
3050 l_valid := FALSE;
3051
3052 /* =============== START DEBUG LOG ================ */
3053 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg8',
3054 'result --> ' || result || ' and l_valid --> FALSE ');
3055 /* =============== END DEBUG LOG ================== */
3056
3057 ELSE
3058
3059 Wf_Engine.SetItemAttrText (itemtype => ItemType,
3060 itemkey => ItemKey,
3061 aname => 'POS_STRUCTURE_VERSION_ID',
3062 avalue => l_hierarchy_version_id);
3063
3064 /* =============== START DEBUG LOG ================ */
3065 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg9',
3066 ' SetItemAttrText POS_STRUCTURE_VERSION_ID --> ' ||l_hierarchy_version_id);
3067 /* =============== END DEBUG LOG ================== */
3068
3069 l_valid := TRUE;
3070
3071 /* =============== START DEBUG LOG ================ */
3072 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg10',
3076 END IF;
3073 ' l_valid --> TRUE ');
3074 /* =============== END DEBUG LOG ================== */
3075
3077
3078 IF (c_pos_hier_ver%ISOPEN) THEN
3079 CLOSE c_pos_hier_ver;
3080 END IF;
3081
3082 END IF;
3083
3084 IF l_valid THEN
3085
3086 -- Check if Postion eixsts in Hierarchy
3087 OPEN c_is_pos_in_hier(l_business_group_id,
3088 l_hierarchy_version_id,
3089 l_position_id);
3090 FETCH c_is_pos_in_hier INTO l_pos_structure_element_id;
3091
3092 /* =============== START DEBUG LOG ================ */
3093 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg11',
3094 ' check if Postion eixsts in Hierarchy ' ||
3095 ' l_business_group_id --> ' || l_business_group_id ||
3096 ' l_hierarchy_version_id --> ' || l_hierarchy_version_id ||
3097 ' l_position_id --> ' || l_position_id);
3098 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg12',
3099 ' l_pos_structure_element_id --> ' || l_pos_structure_element_id);
3100 /* =============== END DEBUG LOG ================== */
3101
3102 IF (c_is_pos_in_hier%NOTFOUND) THEN
3103 l_valid := FALSE;
3104 ELSE
3105 l_valid := TRUE;
3106 END IF;
3107
3108 IF (c_is_pos_in_hier%ISOPEN) THEN
3109 CLOSE c_is_pos_in_hier;
3110 END IF;
3111
3112 END IF;
3113
3114 IF l_valid THEN
3115 result := 'COMPLETE:Y';
3116 ELSE
3117 result := 'COMPLETE:N';
3118 END IF;
3119
3120 /* =============== START DEBUG LOG ================ */
3121 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg13',
3122 ' result --> ' || result);
3123 DEBUG_LOG_STRING (l_proc_level, 'PositionInHierarchy.Msg14',
3124 ' ** END POSITIONINHIERARCHY ** ');
3125 /* =============== END DEBUG LOG ================== */
3126
3127 EXCEPTION
3128 WHEN OTHERS THEN
3129 /* =============== START DEBUG LOG ================ */
3130 DEBUG_LOG_UNEXP_ERROR ('PositionInHierarchy.Unexp1','DEFAULT');
3131 /* =============== END DEBUG LOG ================== */
3132 result := NULL;
3133 RAISE;
3134
3135 END PositionInHierarchy;
3136
3137 END igidosl;
3138