[Home] [Help]
PACKAGE BODY: APPS.INVTROAP
Source
1 PACKAGE BODY INVTROAP as
2 /* $Header: INVWFTOB.pls 120.4.12010000.2 2008/07/29 13:50:13 ptkumar ship $ */
3
4 Procedure Start_TO_Approval( To_Header_Id in number,
5 Item_Type in varchar2,
6 Item_Key in varchar2 ) IS
7
8 l_ItemType varchar2(100) := nvl(Item_Type,'INVTROAP');
9 l_ItemKey varchar2(100) := Item_Key;
10 l_process_int_name varchar2(100) := 'APPROVE_TRANSFER_ORDER';
11 x_requestor_username varchar2(30);
12 x_requestor_disp_name varchar2(80);
13 l_requestor_id number;
14 l_requestor_name varchar2(60);
15 l_wf_item_exists boolean;
16 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
17 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
18 l_timeout_period number;
19 l_timeout_action number;
20 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
21 Begin
22
23 /* To Defer the process from the start */
24
25 -- wf_engine.threshold := -1;
26
27
28 /* To check weather the current item already exist for approval */
29
30 l_trohdr_rec := INV_Trohdr_Util.Query_row( To_Header_Id );
31
32 if ( l_trohdr_rec.header_status <> INV_Globals.G_TO_STATUS_INCOMPLETE ) then
33 FND_MESSAGE.SET_NAME('INV','INV_TO_INVALID_FOR_APPROVAL');
34 FND_MESSAGE.SET_TOKEN('ENTITY', To_Header_Id);
35 FND_MSG_PUB.Add;
36 RETURN;
37 else
38 Select nvl(TXN_APPROVAL_TIMEOUT_PERIOD,0)
39 Into l_timeout_period
40 From MTL_PARAMETERS
41 Where organization_id = l_trohdr_rec.organization_id;
42
43 if ( l_timeout_period = 0 ) then
44 Select nvl(MO_APPROVAL_TIMEOUT_ACTION,1)
45 Into l_timeout_action
46 From MTL_PARAMETERS
47 Where organization_id = l_trohdr_rec.organization_id;
48
49 if( l_timeout_action = 1 ) then
50 Inv_trohdr_Util.Update_Row_Status(To_Header_Id,
51 Inv_Globals.G_TO_STATUS_APPROVED);
52
53 l_trolin_tbl := INV_trolin_util.Get_Lines( To_Header_Id );
54 For l_line_count in 1..l_trolin_tbl.count Loop
55
56 /* bug 2345192 */
57 --Bug #5462193, commented the code below
58 -- Changing the if condition to compare with INCOMPLETE.
59
60 /*
61 if(l_trolin_tbl(l_line_count).Line_Status=6) then
62 Inv_trolin_Util.Update_Row_Status(l_trolin_tbl(l_line_count).Line_id,
63 INV_Globals.G_TO_STATUS_CANCELLED);
64 */
65
66 if (l_trolin_tbl(l_line_count).Line_Status = INV_GLOBALS.G_TO_STATUS_INCOMPLETE) then
67 Inv_trolin_Util.Update_Row_Status(l_trolin_tbl(l_line_count).Line_id,
68 INV_Globals.G_TO_STATUS_APPROVED);
69 end if;
70 end Loop;
71 else
72 Inv_trohdr_Util.Update_Row_Status(To_Header_Id,
73 Inv_Globals.G_TO_STATUS_REJECTED);
74
75 l_trolin_tbl := INV_trolin_util.Get_Lines( To_Header_Id );
76 For l_line_count in 1..l_trolin_tbl.count Loop
77
78 --Bug #5462193, added the if condition
79 if (l_trolin_tbl(l_line_count).Line_Status = INV_GLOBALS.G_TO_STATUS_INCOMPLETE) then
80 Inv_trolin_Util.Update_Row_Status(l_trolin_tbl(l_line_count).Line_id,
81 INV_Globals.G_TO_STATUS_REJECTED);
82 end if;
83 end Loop;
84 end if;
85 Return;
86 else
87 Inv_trohdr_Util.Update_Row_Status(To_Header_Id,
88 Inv_Globals.G_TO_STATUS_PENDING_APPROVAL);
89 end if;
90
91 end if;
92
93 l_wf_item_exists := wf_item.item_exist( itemtype => l_ItemType,
94 itemkey => l_ItemKey );
95 if ( l_wf_item_exists ) then
96 -- inv_Debug.message('item exists');
97 FND_MESSAGE.SET_NAME('INV','INV_ALREADY_EXISTS');
98 FND_MESSAGE.SET_TOKEN('ENTITY','Approval Process');
99 FND_MSG_PUB.Add;
100 else
101 --Inv_debug.message(' creating the process');
102 wf_engine.createprocess( itemtype => l_ItemType,
103 itemkey => l_ItemKey,
104 process => l_process_int_name );
105 --Inv_debug.message(' created the process');
106
107 l_requestor_name := FND_GLOBAL.USER_NAME;
108
109 --l_requestor_name := 'OPERATIONS';
110
111 select ORIG_SYSTEM_ID
112 into l_requestor_id
113 from WF_USERS
114 where NAME = l_requestor_name;
115
116 wf_directory.GetUserName( p_orig_system => 'PER',
117 p_orig_system_id => l_requestor_id,
118 p_name => x_requestor_username,
119 p_display_name => x_requestor_disp_name);
120
121 wf_engine.SetItemOwner( itemtype => l_ItemType,
122 itemkey => l_ItemKey,
123 owner => l_requestor_name );
124
125 --inv_debug.message('Starting to set attributes' );
126
127 wf_engine.setitemattrNumber( itemtype => l_ItemType,
128 itemkey => l_ItemKey,
129 aname => 'TO_HEADER_ID',
130 avalue => To_Header_Id );
131 wf_engine.setitemattrNumber( itemtype => l_ItemType,
132 itemkey => l_ItemKey,
133 aname => 'ORG_ID',
134 avalue => l_trohdr_rec.organization_id );
135
136
137 wf_engine.setitemattrtext( itemtype => l_ItemType,
138 itemkey => l_ItemKey,
139 aname => 'REQUESTOR_USERNAME',
140 avalue => x_requestor_username );
141
142 wf_engine.setitemattrtext( itemtype => l_ItemType,
143 itemkey => l_ItemKey,
144 aname => 'REQUESTOR_DISPLAY_NAME',
145 avalue => x_requestor_disp_name );
146
147 wf_engine.setitemattrNumber( itemtype => l_ItemType,
148 itemkey => l_ItemKey,
149 aname => 'REQUESTOR_ID',
150 avalue => l_requestor_id );
151
152 wf_engine.setitemattrtext( itemtype => l_ItemType,
153 itemkey => l_ItemKey,
154 aname => 'FORWARD_FROM_USERNAME',
155 avalue => x_requestor_username );
156
157 wf_engine.setitemattrtext( itemtype => l_ItemType,
158 itemkey => l_ItemKey,
159 aname => 'FORWARD_FROM_DISPLAY_NAME',
160 avalue => x_requestor_disp_name );
161
162 wf_engine.setitemattrNumber( itemtype => l_ItemType,
163 itemkey => l_ItemKey,
164 aname => 'FORWARD_FROM_ID',
165 avalue => l_requestor_id );
166
167
168 --inv_debug.message('completed setting attributes' );
169 --inv_debug.message('Itemtype='||l_ItemType );
170 --inv_debug.message('Itemkey='||l_Itemkey );
171
172 --Inv_Debug.Message('Starting the process' );
173
174 wf_engine.startprocess( itemtype => l_ItemType,
175 itemkey => l_ItemKey );
176
177 --inv_debug.message('Started the process' );
178 FND_MESSAGE.SET_NAME('INV','INV_APPROVAL_LAUNCHED');
179 FND_MSG_PUB.Add;
180 end if;
181
182 Exception
183 When Others then
184 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
185 THEN
186 FND_MSG_PUB.Add_Exc_Msg
187 ( 'INVTROAP'
188 , 'Start_TO_Approval'
189 );
190 END IF;
191 wf_core.context('INVTROAP','Start_TO_Approval',l_itemtype,l_itemkey);
192 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
193
194 End Start_TO_Approval;
195
196
197 Procedure Check_TO_Status( itemtype in varchar2,
198 itemkey in varchar2,
199 actid in number,
200 funcmode in varchar2,
201 result out nocopy varchar2 ) is
202 p_header_id Number;
203 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
204
205 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
206 Begin
207 if (funcmode = 'RUN') then
208
209 p_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
210 itemkey => itemkey,
211 aname => 'TO_HEADER_ID');
212 l_trohdr_rec := INV_Trohdr_Util.Query_row( p_header_id );
213
214 if ( l_trohdr_rec.header_status IN
215 ( INV_Globals.G_TO_STATUS_PENDING_APPROVAL,
216 INV_Globals.G_TO_STATUS_APPROVED ) ) then
217
218 wf_engine.setitemattrtext( itemtype => itemtype,
219 itemkey => itemkey,
220 aname => 'TO_NUMBER',
221 avalue => l_trohdr_rec.request_number );
222
223 wf_engine.setitemattrtext( itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'TO_DESCRIPTION',
226 avalue => l_trohdr_rec.description );
227
228 wf_engine.setitemattrdate( itemtype => itemtype,
229 itemkey => itemkey,
230 aname => 'DATE_REQUIRED',
231 avalue => l_trohdr_rec.date_required );
232
233 result := 'COMPLETE:PENDING_APPROVAL';
234 else
235 result := 'COMPLETE';
236 end if;
237
238 return;
239 end if;
240
241 if ( funcmode = 'CANCEL') then
242 result := 'COMPLETE';
243 else
244 result := '';
245 end if;
246
247 Exception
248 When others then
249 wf_core.context('INVTROAP','Check_TO_Status',itemtype,
250 itemkey, to_char(actid),funcmode);
251 raise;
252
253 End Check_TO_Status;
254
255
256 Procedure Spawn_TO_Lines( itemtype in varchar2,
257 itemkey in varchar2,
258 actid in number,
259 funcmode in varchar2,
260 result out nocopy varchar2 ) is
261
262 l_to_number varchar2(30);
263 l_to_desc varchar2(100);
264 l_header_id Number;
265 l_requestor_id Number;
266 l_line_count Number := 0;
267 l_child_itemtype varchar2(100) := itemtype;
268 l_child_itemkey varchar2(100);
269 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
270 l_requestor_username varchar2(30);
271 l_requestor_disp_name varchar2(80);
272 From_locator_value varchar2(200);
273 to_locator_value varchar2(200);
274
275
276 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
277 Begin
278 if (funcmode = 'RUN') then
279
280 l_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
281 itemkey => itemkey,
282 aname => 'TO_HEADER_ID');
283
284 l_to_number := wf_engine.GetItemAttrtext( itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'TO_NUMBER');
287
288 l_to_desc := wf_engine.GetItemAttrtext( itemtype => itemtype,
289 itemkey => itemkey,
290 aname => 'TO_DESCRIPTION');
291
292 l_requestor_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
293 itemkey => itemkey,
294 aname => 'REQUESTOR_ID');
295
296 l_requestor_username := wf_engine.GetItemAttrtext( itemtype => itemtype,
297 itemkey => itemkey,
298 aname => 'REQUESTOR_USERNAME');
299
300 l_requestor_disp_name := wf_engine.GetItemAttrtext( itemtype => itemtype,
301 itemkey => itemkey,
302 aname => 'REQUESTOR_DISPLAY_NAME');
303
304
305 -- call TO API for loading line pl/sql table.
306
307 l_trolin_tbl := INV_trolin_util.Get_Lines( l_header_id );
308
309 For l_line_count in 1..l_trolin_tbl.count Loop
310
311 -- Bug #5462193, Cancelled lines should not go for approval.
312 IF l_trolin_tbl(l_line_count).line_status = INV_GLOBALS.G_TO_STATUS_INCOMPLETE THEN
313
314 l_child_itemkey := to_char(l_header_id)||'-'||
315 to_char(l_trolin_tbl(l_line_count).line_id);
316
317 wf_engine.createprocess( itemtype => l_child_itemtype,
318 itemkey => l_child_itemkey,
319 process => 'TO_LINE_APPROVE');
320
321 wf_engine.SetItemOwner( itemtype => l_child_itemtype,
322 itemkey => l_child_itemkey,
323 owner => l_requestor_username );
324
325 /* Set the item attributes here */
326
327 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
328 itemkey => l_child_itemkey,
329 aname => 'TO_NUMBER',
330 avalue => l_to_number );
331
332 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
333 itemkey => l_child_itemkey,
334 aname => 'TO_HEADER_ID',
335 avalue => l_header_id );
336
337 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
338 itemkey => l_child_itemkey,
339 aname => 'TO_DESCRIPTION',
340 avalue => l_to_desc );
341
342 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
343 itemkey => l_child_itemkey,
344 aname => 'LINE_NUMBER',
345 avalue => l_trolin_tbl(l_line_count).line_number );
346
347 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
348 itemkey => l_child_itemkey,
349 aname => 'LINE_QUANTITY',
350 avalue => l_trolin_tbl(l_line_count).quantity );
351
352 --INVCONV
353 wf_engine.setitemattrNumber( itemtype => itemtype,
354 itemkey => l_child_itemkey,
355 aname => 'SEC_LINE_QTY',
356 avalue => l_trolin_tbl(l_line_count).secondary_quantity );
357
358 wf_engine.setitemattrtext( itemtype => itemtype,
359 itemkey => l_child_itemkey,
360 aname => 'SEC_UOM',
361 avalue => l_trolin_tbl(l_line_count).secondary_uom );
362 --INVCONV
363
364 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
365 itemkey => l_child_itemkey,
366 aname => 'FROM_SUBINVENTORY',
370 l_trolin_tbl(l_line_count).from_locator_id);
367 avalue => l_trolin_tbl(l_line_count).from_subinventory_code );
368
369 from_locator_value := INV_UTILITIES.get_conc_segments(l_trolin_tbl(l_line_count).organization_id,
371
372 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
373 itemkey => l_child_itemkey,
374 aname => 'FROM_LOCATOR',
375 avalue => from_locator_value);
376
377 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
378 itemkey => l_child_itemkey,
379 aname => 'TO_SUBINVENTORY',
380 avalue =>
381 l_trolin_tbl(l_line_count).to_subinventory_code );
382
383 to_locator_value := INV_UTILITIES.get_conc_segments(l_trolin_tbl(l_line_count).organization_id,
384 l_trolin_tbl(l_line_count).to_locator_id);
385
386 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
387 itemkey => l_child_itemkey,
388 aname => 'TO_LOCATOR',
389 avalue => to_locator_value);
390
391 wf_engine.setitemattrtext( itemtype => l_child_itemtype,
392 itemkey => l_child_itemkey,
393 aname => 'UOM',
394 avalue => l_trolin_tbl(l_line_count).uom_code );
395
396 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
397 itemkey => l_child_itemkey,
398 aname => 'LINE_ID',
399 avalue => l_trolin_tbl(l_line_count).line_id );
400
401 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
402 itemkey => l_child_itemkey,
403 aname => 'ITEM_ID',
404 avalue => l_trolin_tbl(l_line_count).inventory_item_id );
405
406 wf_engine.setitemattrdate( itemtype => l_child_itemtype,
407 itemkey => l_child_itemkey,
408 aname => 'DATE_REQUIRED',
409 avalue => l_trolin_tbl(l_line_count).date_required );
410
411 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
412 itemkey => l_child_itemkey,
413 aname => 'ORG_ID',
414 avalue => l_trolin_tbl(l_line_count).organization_id );
415
416 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
417 itemkey => l_child_itemkey,
418 aname => 'REQUESTOR_ID',
419 avalue => l_requestor_id );
420
421 wf_engine.setitemattrtext( itemtype => l_child_ItemType,
422 itemkey => l_child_ItemKey,
423 aname => 'REQUESTOR_USERNAME',
424 avalue => l_requestor_username );
425
426 wf_engine.setitemattrtext( itemtype => l_child_ItemType,
427 itemkey => l_child_ItemKey,
428 aname => 'REQUESTOR_DISPLAY_NAME',
429 avalue => l_requestor_disp_name );
430
431 wf_engine.setitemattrNumber( itemtype => l_child_itemtype,
432 itemkey => l_child_itemkey,
433 aname => 'FORWARD_FROM_ID',
434 avalue => l_requestor_id );
435
436 wf_engine.setitemattrtext( itemtype => l_child_ItemType,
437 itemkey => l_child_ItemKey,
438 aname => 'FORWARD_FROM_USERNAME',
439 avalue => l_requestor_username );
440
441 wf_engine.setitemattrtext( itemtype => l_child_ItemType,
442 itemkey => l_child_ItemKey,
443 aname => 'FORWARD_FROM_DISPLAY_NAME',
444 avalue => l_requestor_disp_name );
445
446 wf_engine.setitemparent( itemtype => l_child_itemtype,
447 itemkey => l_child_itemkey,
448 parent_itemtype => itemtype,
449 parent_itemkey => itemkey,
450 parent_context => NULL );
451
452 END IF;
453 End loop;
454
455 For l_line_count in 1..l_trolin_tbl.count Loop
456
457 -- Bug #5462193, Only incomplete lines should go for approval.
458 IF l_trolin_tbl(l_line_count).line_status = INV_GLOBALS.G_TO_STATUS_INCOMPLETE THEN
459
460 l_child_itemkey := to_char(l_header_id)||'-'||
461 to_char(l_trolin_tbl(l_line_count).line_id);
462
463 Inv_trolin_Util.Update_Row_Status(l_trolin_tbl(l_line_count).Line_id ,
464 INV_Globals.G_TO_STATUS_PENDING_APPROVAL);
465
466 wf_engine.startprocess( itemtype => l_child_itemtype,
467 itemkey => l_child_itemkey );
468 END IF;
469 End loop;
470
471 result := 'COMPLETE';
472 return;
473
474 end if;
475
476 if ( funcmode = 'CANCEL') then
477
478 result := 'COMPLETE';
479 return;
480 else
484
481 result := '';
482 return;
483 end if;
485 Exception
486 when others then
487 wf_core.context('INVTROAP','Spawn_TO_Lines',itemtype,itemkey,
488 actid,funcmode);
489 raise;
490
491 End Spawn_TO_Lines;
492
493 Procedure Evaluate_TO_Status( itemtype in varchar2,
494 itemkey in varchar2,
495 actid in number,
496 funcmode in varchar2,
497 result out nocopy varchar2 ) is
498 l_header_id Number;
499 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
500 l_lines_approved Number := 0;
501 l_lines_rejected Number := 0;
502 l_total_lines Number := 0;
503
504 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
505 Begin
506
507 if ( funcmode = 'RUN') then
508
509 l_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
510 itemkey => itemkey,
511 aname => 'TO_HEADER_ID');
512
513 l_trolin_tbl := INV_trolin_util.Get_Lines( l_header_id );
514 l_total_lines := l_trolin_tbl.count;
515
516 For l_row_count in 1..l_trolin_tbl.count Loop
517
518 if ( l_trolin_tbl(l_row_count).line_status <>
519 INV_Globals.G_TO_STATUS_APPROVED ) then
520 l_lines_rejected := l_lines_rejected + 1;
521 else
522 l_lines_approved := l_lines_approved + 1;
523 end if;
524
525 End Loop;
526
527 if ( l_lines_rejected = 0 ) then
528 result := 'COMPLETE:APPROVED';
529 elsif ( l_lines_rejected < l_total_lines ) then
530 result := 'COMPLETE:PART_APPROVE';
531 else
532 result := 'COMPLETE:REJECTED';
533 end if;
534
535 return;
536
537 end if;
538
539 if ( funcmode = 'CANCEL') then
540 result := 'COMPLETE';
541 return;
542 else
543 result := '';
544 return;
545
546 end if;
547 Exception
548 when others then
549 wf_core.context('INVTROAP','Get_TO_Approval_Status',itemtype,itemkey,
550 to_char(actid),funcmode);
551 raise;
552
553 End Evaluate_TO_Status;
554
555
556 Procedure Upd_TO_Approved( itemtype in varchar2,
557 itemkey in varchar2,
558 actid in number,
559 funcmode in varchar2,
560 result out nocopy varchar2 ) is
561 l_header_id Number;
562 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
563 Begin
564 if (funcmode = 'RUN') then
565 l_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
566 itemkey => itemkey,
567 aname => 'TO_HEADER_ID');
568 Inv_trohdr_Util.Update_Row_Status(l_header_id,
569 Inv_Globals.G_TO_STATUS_APPROVED);
570 result := 'COMPLETE';
571 return;
572 end if;
573
574 if ( funcmode = 'CANCEL') then
575 result := 'COMPLETE';
576 return;
577 else
578 result := '';
579 return;
580 end if;
581
582 exception
583 when others then
584 wf_core.context('INVTROAP','Upd_TO_Approved',itemtype,itemkey,
585 to_char(actid),funcmode);
586 raise;
587
588 End Upd_TO_Approved;
589
590
591 Procedure Upd_TO_Part_Approved( itemtype in varchar2,
592 itemkey in varchar2,
593 actid in number,
594 funcmode in varchar2,
595 result out nocopy varchar2 ) is
596 l_header_id Number;
597 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
598 Begin
599 if (funcmode = 'RUN') then
600 l_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
601 itemkey => itemkey,
602 aname => 'TO_HEADER_ID');
603 Inv_trohdr_Util.Update_Row_Status(l_header_id,
604 Inv_Globals.G_TO_STATUS_PART_APPROVED);
605 result := 'COMPLETE';
606 return;
607 end if;
608
609 if ( funcmode = 'CANCEL') then
610 result := 'COMPLETE';
611 return;
612 else
613 result := '';
614 return;
615
616 end if;
617
618 exception
619 when others then
620 wf_core.context('INVTROAP','Upd_TO_Part_Approved',itemtype,itemkey,
621 to_char(actid),funcmode);
622 raise;
623
624 End Upd_TO_Part_Approved;
625
626
627
628 Procedure Upd_TO_Rejected( itemtype in varchar2,
629 itemkey in varchar2,
630 actid in number,
634 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
631 funcmode in varchar2,
632 result out nocopy varchar2 ) is
633 l_header_id Number;
635 Begin
636 if (funcmode = 'RUN') then
637
638 l_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
639 itemkey => itemkey,
640 aname => 'TO_HEADER_ID');
641 Inv_trohdr_Util.Update_Row_Status(l_header_id,
642 Inv_Globals.G_TO_STATUS_REJECTED);
643 result := 'COMPLETE';
644 return;
645
646 end if;
647
648 if ( funcmode = 'CANCEL') then
649
650 result := 'COMPLETE';
651 return;
652 else
653 result := '';
654 return;
655
656 end if;
657
658 exception
659 when others then
660 wf_core.context('INVTROAP','Upd_TO_Rejected',itemtype,itemkey,
661 to_char(actid),funcmode);
662 raise;
663
664 End Upd_TO_Rejected;
665
666
667 Procedure Check_Null_Planner( itemtype in varchar2,
668 itemkey in varchar2,
669 actid in number,
670 funcmode in varchar2,
671 result out nocopy varchar2 ) is
672
673 l_item_id Number ;
674 l_org_id Number;
675 l_planner_code Varchar2(10);
676 --l_item_name Varchar2(40);
677 l_item_name Varchar2(4000); --changed the size to 4000 for holding Concatenetaed Segments for Bug# 6936609
678 l_item_description MTL_SYSTEM_ITEMS_B.DESCRIPTION%TYPE; -- Added for Bug# 4148672
679
680 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
681 Begin
682 if (funcmode = 'RUN') then
683
684 l_item_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
685 itemkey => itemkey,
686 aname => 'ITEM_ID');
687 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
688 itemkey => itemkey,
689 aname => 'ORG_ID');
690
691 /* Commented the select statement from MTL_SYSTEM_ITEMS_KFV AND
692 Selected the values of item name, planner code and description
693 from MTL_SYSTEM_ITEMS_VL which supports MLS.
694 Select Concatenated_segments , planner_code, description -- Description added for Bug# 4148672
695 into l_item_name , l_planner_code, l_item_description -- l_item_description added for Bug# 4148672
696 from MTL_SYSTEM_ITEMS_KFV
697 where organization_id = l_org_id and
698 inventory_item_id = l_item_id;*/
699
700 Select
701 --segment1, --commented segment1 for Bug# 6936609
702 CONCATENATED_SEGMENTS, --and added CONCATENATED_SEGMENTS for Bug# 6936609
703 planner_code, description -- Description added for Bug# 4148672
704 into l_item_name , l_planner_code, l_item_description -- l_item_description added for Bug# 4148672
705 from MTL_SYSTEM_ITEMS_VL -- Modified the view to MTL_SYSTEM_ITEMS_FVL for Bug# 4148672
706 where organization_id = l_org_id and
707 inventory_item_id = l_item_id;
708
709
710 wf_engine.setitemattrtext( itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'ITEM_NAME',
713 avalue => l_item_name );
714
715 wf_engine.setitemattrtext( itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'PLANNER_CODE',
718 avalue => l_planner_code );
719 --
720 -- Begin Fix for Bug#4148672
721 -- Passing the value of item description to workflow engine to
722 -- print the item description in the notification sent to the
723 -- planner of the item.
724 --
725 wf_engine.setitemattrtext( itemtype => itemtype,
726 itemkey => itemkey,
727 aname => 'ITEM_DESC',
728 avalue => l_item_description );
729
730 --
731 -- End Fix for Bug#4148672
732 --
733 if ( l_planner_code IS NULL ) then
734 result := 'COMPLETE:Y';
735 else
736 result := 'COMPLETE:N';
737 end if;
738
739 return;
740 end if;
741
742 if ( funcmode = 'CANCEL') then
743
744 result := 'COMPLETE';
745 return;
746 else
747 result := '';
748 return;
749
750 end if;
751
752 exception
753 when others then
754 wf_core.context('INVTROAP','Null_Planner',itemtype,itemkey,
755 to_char(actid),funcmode);
756 raise;
757
758 End Check_Null_Planner;
759
760
764 funcmode in varchar2,
761 Procedure Requestor_Is_Planner( itemtype in varchar2,
762 itemkey in varchar2,
763 actid in number,
765 result out nocopy varchar2 ) is
766 l_requestor_id Number;
767 l_planner_code Varchar2(10);
768 l_org_id Number;
769 l_planner_id Number;
770 x_planner_username varchar2(30);
771 x_planner_disp_name varchar2(80);
772
773 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
774 Begin
775 if (funcmode = 'RUN') then
776
777 l_requestor_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
778 itemkey => itemkey,
779 aname => 'REQUESTOR_ID');
780
781 l_planner_code := wf_engine.GetItemAttrText( itemtype => itemtype,
782 itemkey => itemkey,
783 aname => 'PLANNER_CODE');
784
785 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
786 itemkey => itemkey,
787 aname => 'ORG_ID');
788
789 select employee_id
790 into l_planner_id
791 from MTL_PLANNERS
792 where planner_code = l_planner_code and
793 organization_id = l_org_id;
794
795 wf_engine.setitemattrNumber( itemtype => itemtype,
796 itemkey => itemkey,
797 aname => 'PLANNER_ID',
798 avalue => l_planner_id );
799
800 /* Bug #2416309
801 * Populate the Forward To User Id, Forward To Username and Forward To Display Name
802 * irrespective of whether the requestor is the planner. This is done so that
803 * the From Role of the "Line Approved" message is set to the Planner's user name
804 * and display name. This value is displayed as "From" in the "Move Order Line
805 * Approved" notification
806 */
807 wf_directory.GetUserName( p_orig_system => 'PER',
808 p_orig_system_id => l_planner_id,
809 p_name => x_planner_username,
810 p_display_name => x_planner_disp_name);
811
812 wf_engine.setitemattrNumber( itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'FORWARD_TO_ID',
815 avalue => l_planner_id );
816
817 wf_engine.setitemattrtext( itemtype => ItemType,
818 itemkey => ItemKey,
819 aname => 'FORWARD_TO_USERNAME',
820 avalue => x_planner_username);
821
822 wf_engine.setitemattrtext( itemtype => ItemType,
823 itemkey => ItemKey,
824 aname => 'FORWARD_TO_DISPLAY_NAME',
825 avalue => x_planner_disp_name );
826
827 IF (l_planner_id = l_requestor_id) THEN
828 result := 'COMPLETE:Y';
829 ELSE
830 result := 'COMPLETE:N';
831 END IF;
832
833 return;
834 end if;
835
836 if ( funcmode = 'CANCEL') then
837
838 result := 'COMPLETE';
839 return;
840 else
841 result := '';
842 return;
843
844 end if;
845
846 exception
847 when others then
848 wf_core.context('INVTROAP','Requestor_Is_Planner',itemtype,itemkey,
849 to_char(actid),funcmode);
850 raise;
851
852 End Requestor_Is_Planner;
853
854
855 Procedure Timeout_Action( itemtype in varchar2,
856 itemkey in varchar2,
857 actid in number,
858 funcmode in varchar2,
859 result out nocopy varchar2 ) is
860 l_org_id Number;
861 l_timeout_action Number;
862
863 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
864 Begin
865 if (funcmode = 'RUN') then
866 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
867 itemkey => itemkey,
868 aname => 'ORG_ID');
869 Select nvl(MO_APPROVAL_tIMEOUT_ACTION,1)
870 Into l_timeout_action
871 From MTL_PARAMETERS
872 Where organization_id = l_org_id;
873
874 if ( l_timeout_action = 1 ) then
875 result := 'COMPLETE:APPROVED';
876 return;
877 else
878 result := 'COMPLETE:REJECTED';
879 return;
880 end if;
881 end if;
882
883 if ( funcmode = 'CANCEL') then
884
885 result := 'COMPLETE';
886 return;
887 else
888 result := '';
889 return;
890
891 end if;
892
893 exception
894 when others then
895 wf_core.context('INVTROAP','TimeOut_Action',itemtype,itemkey,
896 to_char(actid),funcmode);
897 raise;
901 Procedure Upd_Line_Approve( itemtype in varchar2,
898 End TimeOut_Action;
899
900
902 itemkey in varchar2,
903 actid in number,
904 funcmode in varchar2,
905 result out nocopy varchar2 ) is
906 l_Line_id Number;
907 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
908 l_line_status NUMBER;
909
910 Begin
911 if (funcmode = 'RUN') then
912
913 l_line_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
914 itemkey => itemkey,
915 aname => 'LINE_ID');
916
917 -- Bug #5462193, added the code below so that cancelled line is not approved
918 select line_status
919 into l_line_status
920 from mtl_txn_request_lines
921 where line_id = l_line_id;
922
923
924 IF l_line_status = INV_GLOBALS.G_TO_STATUS_PENDING_APPROVAL THEN
925 Inv_trolin_Util.Update_Row_Status(l_Line_id ,
926 INV_Globals.G_TO_STATUS_APPROVED);
927 END IF;
928 result := 'COMPLETE';
929 return;
930 end if;
931
932 if ( funcmode = 'CANCEL') then
933
934 result := 'COMPLETE';
935 return;
936 else
937 result := '';
938 return;
939
940 end if;
941
942 exception
943 when others then
944 wf_core.context('INVTROAP','Upd_Line_Approve',itemtype,itemkey,
945 to_char(actid),funcmode);
946 raise;
947
948 End Upd_Line_Approve;
949
950 Procedure Upd_Line_Reject( itemtype in varchar2,
951 itemkey in varchar2,
952 actid in number,
953 funcmode in varchar2,
954 result out nocopy varchar2 ) is
955 l_Line_id Number ;
956 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
957 l_line_status NUMBER;
958 Begin
959 if (funcmode = 'RUN') then
960
961 l_line_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
962 itemkey => itemkey,
963 aname => 'LINE_ID');
964
965 -- Bug #5462193, added the code below so that cancelled line is not approved
966 select line_status
967 into l_line_status
968 from mtl_txn_request_lines
969 where line_id = l_line_id;
970
971 IF l_line_status = INV_GLOBALS.G_TO_STATUS_PENDING_APPROVAL THEN
972 Inv_trolin_Util.Update_Row_Status(l_Line_id ,
973 INV_Globals.G_TO_STATUS_REJECTED);
974 END IF;
975 result := 'COMPLETE';
976
977 end if;
978
979 if ( funcmode = 'CANCEL') then
980
981 result := 'COMPLETE';
982 return;
983 else
984 result := '';
985 return;
986
987 end if;
988
989 exception
990 when others then
991 wf_core.context('INVTROAP','Upd_Line_Reject',itemtype,itemkey,
992 to_char(actid),funcmode);
993 raise;
994
995 End Upd_Line_Reject;
996
997 Procedure Compute_Timeout( itemtype in varchar2,
998 itemkey in varchar2,
999 actid in number,
1000 funcmode in varchar2,
1001 result out nocopy varchar2 ) is
1002 l_org_id Number;
1003 l_timeout_period Number;
1004 l_mfg_cal_date Date;
1005
1006 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1007 Begin
1008 if (funcmode = 'RUN') then
1009
1010 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1011 itemkey => itemkey,
1012 aname => 'ORG_ID');
1013 Select nvl(TXN_APPROVAL_TIMEOUT_PERIOD,0)
1014 Into l_timeout_period
1015 From MTL_PARAMETERS
1016 Where
1017 organization_id = l_org_id;
1018
1019 Select c1.calendar_date
1020 into l_mfg_cal_date
1021 from mtl_parameters o,
1022 bom_calendar_dates c1,
1023 bom_calendar_dates c
1024 where o.organization_id = l_org_id
1025 and c1.calendar_code = c.calendar_code
1026 and c1.exception_set_id = c.exception_set_id
1027 and c1.seq_num = (nvl(c.seq_num,c.next_seq_num) + l_timeout_period)
1028 and c.calendar_code = o.CALENDAR_CODE
1029 and c.exception_set_id = o.CALENDAR_EXCEPTION_SET_ID
1030 and c.calendar_date = trunc(sysdate);
1031
1032
1033 l_timeout_period := trunc(l_mfg_cal_date) - trunc(sysdate);
1034
1035 wf_engine.setitemattrNumber( itemtype => itemtype,
1036 itemkey => itemkey,
1037 aname => 'APPROVAL_TIMEOUT',
1038 avalue => l_timeout_period );
1039 result := 'COMPLETE';
1040
1041 end if;
1045 result := 'COMPLETE';
1042
1043 if ( funcmode = 'CANCEL') then
1044
1046 return;
1047 else
1048 result := '';
1049 return;
1050
1051 end if;
1052
1053 exception
1054 when others then
1055 wf_core.context('INVTROAP','Compute_Timeout',itemtype,itemkey,
1056 to_char(actid),funcmode);
1057 raise;
1058 End Compute_Timeout;
1059
1060
1061 Procedure More_TO_Lines( itemtype in varchar2,
1062 itemkey in varchar2,
1063 actid in number,
1064 funcmode in varchar2,
1065 result out nocopy varchar2 ) is
1066
1067 l_header_id Number;
1068 l_current_line Number := 0;
1069 l_total_lines Number := 0;
1070 l_from_notify_role Varchar2(20);
1071 l_to_notify_role Varchar2(20);
1072 l_org_id Number;
1073 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
1074 From_locator_value Varchar2(200);
1075 To_locator_value Varchar2(200);
1076
1077 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1078 Begin
1079 if (funcmode = 'RUN') then
1080
1081 l_header_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1082 itemkey => itemkey,
1083 aname => 'TO_HEADER_ID');
1084
1085 l_org_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1086 itemkey => itemkey,
1087 aname => 'ORG_ID');
1088
1089 l_trolin_tbl := INV_trolin_util.Get_Lines( l_header_id );
1090 l_total_lines := l_trolin_tbl.count;
1091
1092
1093 wf_engine.setItemAttrNumber( itemtype => itemtype,
1094 itemkey => itemkey,
1095 aname => 'TOTAL_LINES',
1096 avalue => l_total_lines );
1097
1098 l_current_line := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1099 itemkey => itemkey,
1100 aname => 'CURRENT_LINE');
1101
1102 l_current_line := nvl(l_current_line,0) + 1;
1103
1104 if ( ( l_current_line <= l_total_lines ) AND
1105 ( l_trolin_tbl(l_current_line).line_status =
1106 INV_Globals.G_TO_STATUS_APPROVED ) ) then
1107 wf_engine.setitemattrNumber( itemtype => itemtype,
1108 itemkey => itemkey,
1109 aname => 'LINE_NUMBER',
1110 avalue => l_trolin_tbl(l_current_line).line_number );
1111
1112 wf_engine.setitemattrNumber( itemtype => itemtype,
1113 itemkey => itemkey,
1114 aname => 'LINE_QUANTITY',
1115 avalue => l_trolin_tbl(l_current_line).quantity );
1116 --INVCONV
1117 wf_engine.setitemattrNumber( itemtype => itemtype,
1118 itemkey => itemkey,
1119 aname => 'SEC_LINE_QTY',
1120 avalue => l_trolin_tbl(l_current_line).secondary_quantity );
1121
1122 wf_engine.setitemattrtext( itemtype => itemtype,
1123 itemkey => itemkey,
1124 aname => 'SEC_UOM',
1125 avalue => l_trolin_tbl(l_current_line).secondary_uom );
1126 --INVCONV
1127
1128 wf_engine.setitemattrtext( itemtype => itemtype,
1129 itemkey => itemkey,
1130 aname => 'FROM_SUBINVENTORY',
1131 avalue =>
1132 l_trolin_tbl(l_current_line).from_subinventory_code );
1133
1134 from_locator_value := INV_UTILITIES.get_conc_segments(l_org_id,l_trolin_tbl(l_current_line).from_locator_id);
1135
1136 wf_engine.setitemattrtext( itemtype => itemtype,
1137 itemkey => itemkey,
1138 aname => 'FROM_LOCATOR',
1139 avalue => from_locator_value);
1140
1141 wf_engine.setitemattrtext( itemtype => itemtype,
1142 itemkey => itemkey,
1143 aname => 'TO_SUBINVENTORY',
1144 avalue =>
1145 l_trolin_tbl(l_current_line).to_subinventory_code );
1146
1147 to_locator_value := INV_UTILITIES.get_conc_segments(l_org_id,l_trolin_tbl(l_current_line).to_locator_id);
1148
1149 wf_engine.setitemattrtext( itemtype => itemtype,
1150 itemkey => itemkey,
1151 aname => 'TO_LOCATOR',
1152 avalue => to_locator_value);
1153
1154 wf_engine.setitemattrtext( itemtype => itemtype,
1155 itemkey => itemkey,
1156 aname => 'UOM',
1157 avalue => l_trolin_tbl(l_current_line).uom_code );
1158
1159 wf_engine.setitemattrNumber( itemtype => itemtype,
1160 itemkey => itemkey,
1161 aname => 'ITEM_ID',
1162 avalue => l_trolin_tbl(l_current_line).inventory_item_id );
1163
1167 avalue => l_trolin_tbl(l_current_line).date_required );
1164 wf_engine.setitemattrdate( itemtype => itemtype,
1165 itemkey => itemkey,
1166 aname => 'DATE_REQUIRED',
1168
1169 wf_engine.setItemAttrNumber( itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'CURRENT_LINE',
1172 avalue => l_current_line );
1173 Begin
1174 select NOTIFY_LIST
1175 into l_to_notify_role
1176 from mtl_secondary_inventories_fk_v
1177 where SECONDARY_INVENTORY_NAME =
1178 l_trolin_tbl(l_current_line).to_subinventory_code and
1179 organization_id = l_org_id;
1180 Exception
1181 when others then
1182 l_to_notify_role := NULL;
1183 End;
1184
1185 wf_engine.setItemAttrText( itemtype => itemtype,
1186 itemkey => itemkey,
1187 aname => 'TO_NOTIFY_ROLE',
1188 avalue => l_to_notify_role );
1189
1190 Begin
1191 select NOTIFY_LIST
1192 into l_from_notify_role
1193 from mtl_secondary_inventories_fk_v
1194 where SECONDARY_INVENTORY_NAME =
1195 l_trolin_tbl(l_current_line).from_subinventory_code and
1196 organization_id = l_org_id;
1197 Exception
1198 when Others then
1199 l_from_notify_role := Null;
1200 End;
1201
1202 wf_engine.setItemAttrText( itemtype => itemtype,
1203 itemkey => itemkey,
1204 aname => 'FROM_NOTIFY_ROLE',
1205 avalue => l_from_notify_role );
1206
1207 result := 'COMPLETE:Y';
1208 else
1209 result := 'COMPLETE:N';
1210 end if;
1211 return;
1212 end if;
1213
1214 if ( funcmode = 'CANCEL') then
1215
1216 result := 'COMPLETE';
1217 return;
1218 else
1219 result := '';
1220 return;
1221
1222 end if;
1223
1224 exception
1225 when others then
1226 wf_core.context('INVTROAP','More_TO_Lines',itemtype,itemkey,
1227 to_char(actid),funcmode);
1228 raise;
1229 End More_TO_Lines;
1230
1231
1232 Procedure Check_To_Sub_Roles( itemtype in varchar2,
1233 itemkey in varchar2,
1234 actid in number,
1235 funcmode in varchar2,
1236 result out nocopy varchar2 ) is
1237
1238 l_sub_role Varchar2(100);
1239
1240 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1241 Begin
1242 if (funcmode = 'RUN') then
1243
1244 l_sub_role := wf_engine.GetItemAttrText( itemtype => itemtype,
1245 itemkey => itemkey,
1246 aname => 'TO_NOTIFY_ROLE');
1247
1248 if ( l_sub_role IS NULL ) then
1249 result := 'COMPLETE:Y';
1250 else
1251 result := 'COMPLETE:N';
1252 end if;
1253
1254 return;
1255 end if;
1256
1257 if ( funcmode = 'CANCEL') then
1258
1259 result := 'COMPLETE';
1260 return;
1261 else
1262 result := '';
1263 return;
1264
1265 end if;
1266
1267 exception
1268 when others then
1269 wf_core.context('INVTROAP','Check_To_Sub_Roles',itemtype,itemkey,
1270 to_char(actid),funcmode);
1271 raise;
1272
1273 End Check_To_Sub_Roles;
1274
1275 Procedure Check_From_Sub_Roles( itemtype in varchar2,
1276 itemkey in varchar2,
1277 actid in number,
1278 funcmode in varchar2,
1279 result out nocopy varchar2 ) is
1280
1281 l_sub_role Varchar2(100);
1282
1283 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1284 Begin
1285 if (funcmode = 'RUN') then
1286
1287 l_sub_role := wf_engine.GetItemAttrText( itemtype => itemtype,
1288 itemkey => itemkey,
1289 aname => 'FROM_NOTIFY_ROLE');
1290
1291 if ( l_sub_role IS NULL ) then
1292 result := 'COMPLETE:Y';
1293 else
1294 result := 'COMPLETE:N';
1295 end if;
1296
1297 return;
1298 end if;
1299
1300 if ( funcmode = 'CANCEL') then
1301
1302 result := 'COMPLETE';
1303 return;
1304 else
1305 result := '';
1306 return;
1307
1308 end if;
1309
1310 exception
1311 when others then
1312 wf_core.context('INVTROAP','Check_From_Sub_Roles',itemtype,itemkey,
1313 to_char(actid),funcmode);
1314 raise;
1315
1316 End Check_From_Sub_Roles;
1317
1318
1319 Procedure Selector( itemtype in varchar2,
1320 itemkey in varchar2,
1321 actid in number,
1322 command in varchar2,
1323 result out nocopy varchar2 ) is
1324 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1325 Begin
1326 If ( command = 'RUN' ) then
1327 result := 'APPROVE_TRANSFER_ORDER';
1328 return;
1329 end if;
1330
1331 Exception
1332 When Others then
1333 WF_CORE.CONTEXT('INVTROAP','Selector',itemtype,itemkey,
1334 to_char(actid),command);
1335 raise;
1336 End Selector;
1337
1338
1339 END INVTROAP;