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