1 package body CTO_WORKFLOW_API_PK as
2 /* $Header: CTOWFAPB.pls 120.29.12010000.2 2008/08/05 12:21:13 abhissri ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1999 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 | |
10 | FILE NAME : CTOWFAPB.pls
11 | |
12 | DESCRIPTION: |
13 | Contain all CTO and WF related APIs. |
14 | One API is used to check if a configured item (model) |
15 | is created. This API is applied by the Processing Constraints |
16 | Framework provided by OE |
17 | |
18 | |
19 | HISTORY : |
20 | Aug 16, 99 James Chiu Initial version |
21 | 09-11-2000 Kiran Konada Added code for ATO ITEM Enhancement |
22 | 01-02-2001 Renga Kannan Added some debug messages |
23 | 01-02-2001 ssheth added code chnage for bug#1494878 |
24 | 07-18-2001 Ksarkar code change fix for bug# 1876997 |
25 | Change query to refer base tables instead of |
26 | the expensive view (wsh_delivery_line_status_v) |
27 | 08-16-2001 Kiran Konada, fix for bug#1874380 |
28 | to support ATO item under a PTO. |
29 | item_type_code for an ato item under PTO |
30 | is 'OPTION' and top_model_line_id will NOT be |
31 | null, UNLIKE an ato item order, where |
32 | item_type_code = 'Standard' and |
33 | top_model_lined_id is null |
34 | This fix has actually been provided in |
35 | branched version 115.33.1155.3 |
36 | 08-17-2001 Kiran Konada, fix for bug#1895563 |
37 | procedures changed: |
38 | display_wf_status() |
39 | wf_update_after_inv_reserv |
40 | wf_update_after_inv_unreserv |
41 | 09-20-2001 RaviKumar V Addepalli |
42 | Changed the following procedures |
43 | 1. display_wf_status() |
44 | 2. wf_update_after_inv_reserv |
45 | 3. wf_update_after_inv_unreserv |
46 | For the the AutoCreate Purchase requisitions, For|
47 | a ATO Buy item the procedure will have to update |
48 | the line status and the workflow differently. |
49 |
50 |
51 |
52 | 13-NOV-2001 Modified by Renga Kannan |
53 | |
54 | Ato buy item workflow is modified |
55 | Wait for PO RECEIPT activity is |
56 | removed and hence the corresponding code |
57 | is modified in wf_update_inv_unreserve |
58 | and wf_update_inv_reserve |
59 | |
60 | 03/22/2002 bugfix#2234858 |
61 | added new functionality to support DROP SHIP |
62 |
63 | 04/18/2002 bugfix#2312701
64 | added exception to disallow reservation for |
65 | dropshipped orders before po approval |
66 | |
67 | 05/06/2002 bugfix#2358576 |
68 | restrict reservation for dropshippped orders |
69 | only to config items and ato items |
70 |
71 | 07/23/2002 bugfix#2466429
72 | Modified by Kiran Konada
73 | primary_reservation_quantity will have qty as per
74 | primary UoM
75 | 12/12/2002 KIran Konadad
76 | added a parametr in call to populate_req_interface
77 | for MLSUPPLY feture
78 |
79 |
80 | 08/13/ 2003 Kiran Konada
81 | for bug# 3063156
82 propagte bugfix 3042904 to main
83 | passed new paremeters project_id and task_id to
84 | populate_req_interface
85 | these are passed as NULL values as populate_req_interface
86 | calculates them for top_most buy items
87 |
88 Kiran Konada
89 | Muiple sources for DMF-J enahncement in
90 | wf_update_after_inv_unresv
91 | added a call to procedure check_cto_can _create_supply
92 | If cto cannot create supply(planning supply), worlflow will
93 | remain at shipping
94 | If cto can create supply, workflow would be moved appropriately
95 | depending on amount of qty unreserved
96 |
97 |
98 |
99 | 08/28/2003 Kiran Konada
100 | chnaging the procedure wf_update_after_inv_unresv
101 | getting inventory_item_id and oship from org_id
102 | from oe_order_lines_all This is added during Unit test
103 | for support to handle mutiple sources situations during supply
104 | creation
105 |
106 | 09/25/2003 Shashi Bhaskaran
107 | Bugfix : 3077912
108 | Offset need_by_date with postprocessing lead time.
109 | Bugfix : 3076061
110 | When reservations are removed, CTO need to move the
111 | order line status to 'Production Eligible' and WF status
112 | to 'Create Supply Eligible' if the flow schedules are all
113 | completed or no flow schedules exists.
114 | Bugfix : 2972186
115 | Split line status is showing "Production Complete" instead
116 | of "Shipped".
117 |
118 | 10/23/2003 Kiran Konada
119 | changed dsiplay_wf-status for patchset J
120 | introduced a prpcedure Get_Resv_Qty_and_Code
121 |
122 | 10/25/2003 Kiran Konada
123 | removed t_rsv_code.count and added
124 | t_rsv_code IS NOT NULL
125 |
126 | 11/03/2003 Kiran Konada
127 | Main propagation bug#3140641
128 |
129 | reverted changes made on 08/13/ 2003
130 | removed project_id and task_id as parameters to populate_req_interface
131 | revrting bufix 3042904 ude to bug#3129117
132 |
133 | 11/04/2003 Kiran Konada
134 | Made a call to CTOUTILB.check_cto_can_create_supply
135 | from display_wf_status
136 |
137 |
138 | 11/19/2003 Kiran Konada
139 | There was a full table scan on fnd_concurrent_programs
140 | hece, added where clause
141 | and application_id = 702;BOM , bugfix 2885568
142 |
143 | original query
144 | select concurrent_program_id
145 into p_program_id
146 from fnd_concurrent_programs a
147 where concurrent_program_name = 'CTOACREQ'
148 |
149 |
150 | new query
151 | select concurrent_program_id
152 | into p_program_id
153 | from fnd_concurrent_programs a
154 | where concurrent_program_name = 'CTOACREQ'
155 | and application_id = 702
156 |
157 |
158 |
159 | 01/23/2004 Sushant Sawant
160 | Added condition config_orgs <> 3 to avoid warehouse change for items
161 | not based on 'Based on Model'.
162 |
163 |
164 | 02/06/2004 Sushant Sawant fixed bug 3424802
165 | split line should be allowed irrespective of CIB attribute.
166 |
167 |
168 | 02/23/2004 Sushant Sawant fixed Bug 3419221
169 | New LINE_FLOW_STATUS code 'SUPPLY_ELIGIBLE' was introduced.
170 | Config Lines with Internal and External source types will be assigned this status.
171 |
172 |
173 | Modified : 13-APR-2004 Sushant Sawant
174 | Added check for booked orders to provide SUPPLY_ELIGIBLE/ENTERED
175 | as status of config line.
176 |
177 |
178 | Modified : 14-APR-2004 Sushant Sawant
179 | Fixed bug 3419221. following status for config item/ato item are available
180 | as replacement for BOM_AND_RTG_CREATED
181 | New Status: ENTERED/BOOKED/SUPPLY_ELIGIBLE
182 |
183 |
184 | Modified : 17-Nov-2004 Kiran Konada
185 | bugfix 3875420, as flow rsv is not visible in
186 mtl_reservations, we check for rsv_code FO
187 | Modified : 06-Jun-2005 Renga Kannan
188 | Bug Fix 4380768
189 |
190 |
191 | 16-Jun-2005 Kiran Konada
192 | changes for OPM and Ireq project
193 | comment string : OPM
194 | get_resv_qty_and_code API
195 | -cursor c-resv chnaged to include secondary
196 | reservation qty
197 | -sec rsv qty has been assigned to record
198 | structure x_rsv_rec(l_index) and debug messages
199 | -qyery on wip_flow_schedules has been removed
200 | and existing FLM api get_flow_qty is used to
201 | get flow qty in both cases of fresh order line
202 | and split order line
203 | -new query to get external and internal
204 | req data from iface table and assigned to x_rsv_rec(l_index
205 | 24-Jun-2005 Renga Kannan
206 | Get_resv_qty_and_code API is not handling uom
207 conversion.
208 | Added code to pass the qty in primary uom
209 |
210 | 05-Jun-2005 Renga Kannan
211 | Modified function complete_activity for MOAC
212 |
213 |
214 | 09-Aug-2005 Kiran Konada
215 | 4545070
216 | Replaced call to OE_ORDER_WF_UTIL.update_flow_status_code with
217 | call to CTO_WORKFLOW_API_PK.display_wf_status
218 |
219 |
220 | 25-Sep-2005 Kiran Konada
221 | bugfix4637281
222 | changing IF..ELSIF into multiple IF..ENDIF's
223 |
224 | 12-OCT-2005 Kiran Koanda
225 | r12 bugfix 4666778, changed query to look at models CIB attribute
226 |
227 | 15-NOV-2005 Kiran Konada
228 | bug#4739807,when rsv is only bcos of receiving
229 |
230 | 16-NOV-2005 Kiran Konada
231 | bug# 4743430
232 | For homogenous supply with receiving as one of
233 | reservations types. L_toekn2 is null, So the status
234 | would be just IN_RECEIVING
235 |
236 |
237 | 01-Dec-2005 Kiran Konada
238 | FP of bugfix 4051282
239 | Main line bug#4350569
240 |
241 =============================================================================*/
242
243 G_PKG_NAME CONSTANT VARCHAR2(30):='CTO_WORKFLOW_API_PK';
244
245 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
246
247 TYPE rsv_code_tbl_type is TABLE OF VARCHAR2(1) INDEX BY Binary_integer;
248
249 -- Added for Cross docking project.
250 Procedure get_status_tokens(
251 p_order_line_id Number,
252 p_config_item_id Number,
253 p_ship_from_org_id Number,
254 p_ordered_quantity Number,
255 x_token1 OUT NOCOPY Varchar2,
256 x_token2 OUT NOCOPY varchar2,
257 x_return_status OUT NOCOPY varchar2,
258 x_msg_data OUT NOCOPY Varchar2,
259 x_msg_count OUT NOCOPY Number);
260
261
262
263 /**************************************************************************
264
265 Procedure: query_wf_activity_status
266 Parameters: p_itemtype -
267 p_itemkey -
268 p_activity_label - "
269 p_activity_name - "
270 p_activity_status -
271 Description: this procedure is used to query a Workflow activity status
272
273 *****************************************************************************/
274
275
276
277 PROCEDURE query_wf_activity_status(
278 p_itemtype IN VARCHAR2,
279 p_itemkey IN VARCHAR2,
280 p_activity_label IN VARCHAR2,
281 p_activity_name IN VARCHAR2,
282 p_activity_status OUT NOCOPY VARCHAR2 )
283
284 IS
285
286
287 BEGIN
288
289 select activity_status
290 into p_activity_status
291 from wf_item_activity_statuses was
292 where was.item_type = p_itemtype
293 and was.item_key = p_itemkey
294 and was.process_activity in
295 (SELECT wpa.instance_id
296 FROM wf_process_activities wpa
297 WHERE wpa.activity_name = p_activity_name);
298
299
300 EXCEPTION
301
302 when others then
303 p_activity_status := 'NULL';
304 IF PG_DEBUG <> 0 THEN
305 oe_debug_pub.add('query_wf_activity_status: ' || 'exception in CTO_WORKFLOW_API_PK.query_wf_activity_status'||sqlerrm, 1);
306 END IF;
307
308 END query_wf_activity_status;
309
310
311
312
313 /**************************************************************************
314
315 Procedure: get_activity_status
316 Parameters: itemtype -
317 itemkey -
318 linetype - "
319 activity_name - "
320 Description: this procedure is used by Match and Reserve to check if an
321 instance of WF process resides at a desired block activity.
322
323 *****************************************************************************/
324
325 PROCEDURE get_activity_status(
326 itemtype IN VARCHAR2,
327 itemkey IN VARCHAR2,
328 linetype IN VARCHAR2,
329 activity_name OUT NOCOPY VARCHAR2 )
330
331 IS
332
333 v_activity_status_code VARCHAR2(8);
334
335 BEGIN
336
337 if upper(linetype) = 'MODEL' then
338 IF PG_DEBUG <> 0 THEN
339 oe_debug_pub.add('get_activity_status: ' || 'get_act_status::line is model', 4);
340 END IF;
341 query_wf_activity_status(itemtype, itemkey, 'CREATE_CONFIG_ITEM_ELIGIBLE',
342 'CREATE_CONFIG_ITEM_ELIGIBLE', v_activity_status_code);
343
344 if upper(v_activity_status_code) = 'NOTIFIED' then
345 activity_name := 'CREATE_CONFIG_ITEM_ELIGIBLE';
346 else
347 activity_name := 'NULL';
348 end if;
349
350 elsif upper(linetype) = 'CONFIG' then
351 IF PG_DEBUG <> 0 THEN
352 oe_debug_pub.add('get_activity_status: ' || 'get_act_status::line is config');
353 END IF;
354 query_wf_activity_status(itemtype, itemkey, 'CREATE_CONFIG_BOM_ELIGIBLE',
355 'CREATE_CONFIG_BOM_ELIGIBLE', v_activity_status_code);
356
357 if upper(v_activity_status_code) = 'NOTIFIED' then
358 activity_name := 'CREATE_CONFIG_BOM_ELIGIBLE';
359 else
360 query_wf_activity_status(itemtype, itemkey, 'CREATE_SUPPLY_ORDER_ELIGIBLE',
361 'CREATE_SUPPLY_ORDER_ELIGIBLE', v_activity_status_code);
362
363 if upper(v_activity_status_code) = 'NOTIFIED' then
364 activity_name := 'CREATE_SUPPLY_ORDER_ELIGIBLE';
365 else
366 activity_name := 'NULL';
367 end if;
368 end if;
369
370 else
371 activity_name := 'NULL';
372
373 end if;
374
375 IF PG_DEBUG <> 0 THEN
376 oe_debug_pub.add('get_activity_status: ' || 'get_act_status::returning activity_name '||activity_name, 4);
377 END IF;
378
379 EXCEPTION
380
381 when others then
382 activity_name := 'NULL';
383 IF PG_DEBUG <> 0 THEN
384 oe_debug_pub.add('get_activity_status: ' || 'exception in CTO_WORKFLOW_API_PK.get_activity_status:'||sqlerrm, 1);
385 END IF;
386
387 END get_activity_status;
388
389
390 /**************************************************************************
391
392 Function: complete_activity
393 Parameters: p_itemtype -
394 p_itemkey -
395 p_activity_name - "
396 p_result_code - "
397 Description: this function is used to complete an WF activity
398
399 *****************************************************************************/
400
401 FUNCTION complete_activity(
402 p_itemtype IN VARCHAR2,
403 p_itemkey IN VARCHAR2,
404 p_activity_name IN VARCHAR2,
405 p_result_code IN VARCHAR2
406 )
407 return BOOLEAN is
408
409 -- Added the variables for MOAC project
410 l_change_context_back varchar2(1) := 'N';
411 l_current_mode varchar2(1);
412 l_current_org Number;
413 l_line_org_id Number;
414
415 BEGIN
416
417 -- Change for MOAC project
418 -- Before completing the workflow activity
419 -- if the current ou is not order line on then change the OU to order
420 -- line ou and reset after the worklfow node is completed.
421
422 Select org_id
423 into l_line_org_id
424 from oe_order_lines_all
425 where line_id = p_itemkey;
426
427 -- Modified by Renga Kannan on 04/28/06
428 -- CAlling Utility API to Switch to order line context
429 CTO_MSUTIL_PUB.switch_to_oe_context(p_oe_org_id => l_line_org_id,
430 x_current_mode => l_current_mode,
431 x_current_org => l_current_org,
432 x_context_switch_flag => l_change_context_back);
433
434 wf_engine.CompleteActivityInternalName(p_itemtype, p_itemkey,
435 p_activity_name,p_result_code);
436 IF PG_DEBUG <> 0 Then
437 oe_debug_pub.add('Complete_activity : l_change_context_back = '||l_change_context_back,5);
438 End if;
439
440 If l_change_context_back = 'Y' then
441 CTO_MSUTIL_PUB.Switch_context_back(p_old_mode => l_current_mode,
442 p_old_org => l_current_org);
443 End if; /*l_change_context_back = 'Y' */
444
445 return (TRUE);
446
447
448 EXCEPTION
449
450 when others then
451 IF PG_DEBUG <> 0 THEN
452 oe_debug_pub.add('complete_activity: ' || 'exception in CTO_WORKFLOW_API_PK.complete_activity:'||sqlerrm, 1);
453 END IF;
454 return (FALSE);
455
456 END complete_activity;
457
458 /*******************************************************************************
459 Function: display_wf_status
460 Parameters: p_order_line_id
461 Description: This function is used to display a proper wf status from
462 OM form.
463 Fix for bug 1494878 :
464 The operating unit can be set differently for
465 OM and WIP reponsibilities. Hence, before calling OM's
466 function to update the line status, we set the org
467 context to the org_id on oe_order_lines_all. After
468 returning from the OM function, we reset the org
469 context to the WIP operating unit (profile MO:op unit)
470
471 fix for bug 1895563 :
472 Removed the top most if statement which was
473 checking if order was booked and (scheduled or reserved)
474 *********************************************************************************/
475
476 FUNCTION display_wf_status(
477 p_order_line_id IN NUMBER
478 )
479 return INTEGER is
480
481 v_ordered_quantity NUMBER;
482 v_header_id NUMBER;
483 l_oe_org_id NUMBER;
484 l_original_org_id NUMBER; -- rkaza. 11/10/2004. bug 3982767
485 x_return_status VARCHAR2(1);
486 l_stmt_num number;
487 v_inv_item_id NUMBER;
488 l_message NUMBER;
489 l_ship_from_org_id Oe_order_lines_all.ship_from_org_id%type;
490 l_change_status Varchar2(100);
491 l_item_type_code Varchar2(100);
492 v_source_type_code Varchar2(100);
493 v_shipped_quantity oe_order_lines_all.shipped_quantity%type;
494 v_shipped_qty number; -- 2972186
495 l_msg_count NUMBER;
496 l_msg_data VARCHAR2(200);
497 l_ato_line_id NUMBER;
498 l_top_model_line_id NUMBER;
499 l_booked_flag varchar2(2) ;
500 l_token1 varchar2(100);
501 l_token2 varchar2(100);
502
503 v_activity_status_code varchar2(8);
504 l_req_created varchar2(1);
505
506 BEGIN
507
508 IF PG_DEBUG <> 0 THEN
509 oe_debug_pub.add('display_wf_status: ' || 'Entering display_wf_status', 1);
510 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'Entering Display_wf_status');
511 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'P_order_line_id ='||to_char(p_order_line_id));
512 End if;
513
514 v_ordered_quantity := 0;
515
516 l_stmt_num := 10;
517
518 select oel.header_id,
519 INV_CONVERT.inv_um_convert( --bug 2317701
520 oel.inventory_item_id,
521 5, -- pass precision of 5
522 oel.ordered_quantity,
523 oel.order_quantity_uom,
524 msi.primary_uom_code,
525 null,
526 null
527 ),
528 oel.org_id,
529 oel.ship_from_org_id ,
530 oel.inventory_item_id,
531 oel.item_type_code,
532 oel.source_type_code,
533 nvl(oel.shipped_quantity,0),
534 oel.ato_line_id, --added for patchset J
535 oel.top_model_line_id, --added for patchset J
536 oel.booked_flag
537 into v_header_id,
538 v_ordered_quantity,
539 l_oe_org_id,
540 l_ship_from_org_id,
541 v_inv_item_id,
542 l_item_type_code,
543 v_source_type_code,
544 v_shipped_qty,
545 l_ato_line_id,
546 l_top_model_line_id,
547 l_booked_flag
548 from oe_order_lines_all oel,
549 mtl_system_items msi
550 where line_id = p_order_line_id
551 and oel.inventory_item_id = msi.inventory_item_id
552 and oel.ship_from_org_id = msi.organization_id;
553
554 If PG_DEBUG <> 0 Then
555 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'v_header_id = '||to_char(v_header_id));
556 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'l_oe_org_id = '||to_char(l_oe_org_id));
557 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'v_ordered_quantity = '||to_char(v_ordered_quantity));
558 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'v_source_type_code = '||v_source_type_code);
559 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'ato line id = '||l_ato_line_id);
560 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'top model line id = '||l_top_model_line_id);
561 oe_debug_pub.add('display_wf_status: ' || 'v_header_id = '||to_char(v_header_id) , 5);
562 oe_debug_pub.add('display_wf_status: ' || 'l_oe_org_id = '||to_char(l_oe_org_id) , 5);
563 oe_debug_pub.add('display_wf_status: ' || 'v_ordered_quantity = '||to_char(v_ordered_quantity) , 5);
564 oe_debug_pub.add('display_wf_status: ' || 'v_source_type_code = '||v_source_type_code , 5);
565 oe_debug_pub.add('display_wf_status: ' || 'ato line id = '||l_ato_line_id , 5);
566 oe_debug_pub.add('display_wf_status: ' || 'top model line id = '||l_top_model_line_id , 5);
567 END IF;
568
569 l_stmt_num := 11;
570
571 If l_booked_flag = 'N' Then
572 IF PG_DEBUG <> 0 Then
573 cto_wip_workflow_api_pk.cto_debug('Display_wf_status',' Line is not yet booked. No need to change status');
574 oe_debug_pub.add('Display_wf_status: Line is not yet booked. No need to change status',1);
575 End if;
576 elsif nvl(v_shipped_quantity,0) <> 0 then
577
578 If PG_DEBUG <> 0 Then
579 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS',
580 'Line is shipped no need to change the status..');
581 End if;
582
583 Elsif v_source_type_code = 'EXTERNAL' THEN
584
585
586
587
588 If PG_DEBUG <> 0 Then
589 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'Drop ship line, will be updating line status');
590 End if;
591
592 --bugfix 5461892
593
594
595 Begin
596 select 'Y'
597 INTO l_req_created
598 from oe_drop_ship_sources
599 where line_id = p_order_line_id;
600 Exception
601 When others THEN
602 l_req_created := 'N';
603 End;
604
605
606 IF l_booked_flag = 'Y' and l_req_created = 'N' THEN
607 l_token1 := 'SUPPLY';
608 l_token2 := 'ELIGIBLE';
609 END IF;
610 --end bugfix 5461892
611
612 Else
613 get_status_tokens(
614 p_order_line_id => p_order_line_id,
615 p_config_item_id => v_inv_item_id,
616 p_ship_from_org_id => l_ship_from_org_id,
617 p_ordered_quantity => v_ordered_quantity,
618 x_token1 => l_token1,
619 x_token2 => l_token2,
620 x_return_status => x_return_status,
621 x_msg_data => l_msg_data,
622 x_msg_count => l_msg_count);
623 END IF; /* nvl(v_shipped_quantity,0) <> 0 */
624
625 l_stmt_num := 170;
626
627 IF l_token2 is null THEN --bugfix 4743430
628 l_change_status := L_token1;
629 ELSE
630 l_change_status := L_token1||'_'||l_token2;
631 END IF;
632
633
634
635
636 If PG_DEBUG <> 0 Then
637 oe_debug_pub.add('Display_wf_status : New status code = '||l_change_status,1);
638 End if;
639
640 If l_change_status <> '_' Then
641
642 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ',
643 NULL,
644 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
645 INTO l_original_org_id
646 FROM DUAL;
647
648
649
650 change_status_batch (
651 p_header_id => v_header_id,
652 p_line_id => p_order_line_id,
653 p_change_status => l_change_status,
654 p_oe_org_id => l_oe_org_id,
655 x_return_status => x_return_status);
656
657 if x_return_status = FND_API.G_RET_STS_ERROR then
658 IF PG_DEBUG <> 0 THEN
659 oe_debug_pub.add('display_wf_status: ' || 'change_status_batch raised expected error.', 1);
660 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'change_status_batch raised expected error.');
661 End if;
662 raise FND_API.G_EXC_ERROR;
663 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
664 IF PG_DEBUG <> 0 THEN
665 oe_debug_pub.add('display_wf_status: ' || 'change_status_batch raised unexpected error.', 1);
666 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'change_status_batch raised unexpected error.');
667 End if;
668 raise FND_API.G_EXC_UNEXPECTED_ERROR;
669 end if; /* x_return_status = FND_API.G_RET_STS_ERROR */
670
671 IF PG_DEBUG <> 0 THEN
672 oe_debug_pub.add('display_wf_status: ' || 'Re-Setting the Org Context to '||l_original_org_id, 5);
673 END IF;
674
675 End if; /* l_change_status <> '_' */
676
677 IF PG_DEBUG <> 0 THEN
678 oe_debug_pub.add('display_wf_status: ' || 'Exiting disp_wf_status', 4);
679 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'Exiting display_wf_Status with return value 1');
680 End if;
681 return 1;
682
683 EXCEPTION
684 when FND_API.G_EXC_ERROR then
685 IF PG_DEBUG <> 0 THEN
686 oe_debug_pub.add('display_wf_status: ' || 'CTO_WORKFLOW_API_PK.display_wf_status raised exp error::stmt number '||to_char(l_stmt_num), 1);
687 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS',
688 'CTO_WORKFLOW_API_PK.display_wf_status raised exp error::stmt number '|| to_char(l_stmt_num));
689 End if;
690 return 0;--bugfix 4545070
691
692 when FND_API.G_EXC_UNEXPECTED_ERROR then
693 IF PG_DEBUG <> 0 THEN
694 oe_debug_pub.add('display_wf_status: ' || 'CTO_WORKFLOW_API_PK.display_wf_status raised unexp error::stmt number '||
695 to_char(l_stmt_num)||sqlerrm, 1);
696 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS',
697 'CTO_WORKFLOW_API_PK.display_wf_status raised unexp error::stmt number '|| to_char(l_stmt_num));
698 End if;
699 return 0;
700 when others then
701 IF PG_DEBUG <> 0 THEN
702 oe_debug_pub.add('display_wf_status: ' || 'CTO_WORKFLOW_API_PK.display_wf_status::stmt number '||to_char(l_stmt_num), 1);
703 oe_debug_pub.add('display_wf_status: ' || sqlerrm, 1);
704 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS',
705 'CTO_WORKFLOW_API_PK.display_wf_status::stmt number '||
706 to_char(l_stmt_num));
707 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', sqlerrm);
708 End if;
709 return 0;
710
711 END display_wf_status;
712
713
714
715
716
717 /*************************************************************************
718 Procedure: inventory_reservation_check
719 Parameters: p_order_line_id
720 x_return_status - standard API output parameter
721 x_msg_count - "
722 x_msg_data - "
723
724 Description: Check if an order line status is either
725 "CREATE_CONFIG_BOM_ELIGIBLE" or
726 "CREATE_SUPPLY_ORDER_ELIGIBLE"
727 "SHIP_LINE"
728 *****************************************************************************/
729 PROCEDURE inventory_reservation_check(
730 p_order_line_id IN NUMBER,
731 x_return_status OUT NOCOPY VARCHAR2,
732 x_msg_count OUT NOCOPY NUMBER,
733 x_msg_data OUT NOCOPY VARCHAR2
734 )
735 IS
736
737 l_api_name CONSTANT varchar2(40) := 'inventory_reservation_check';
738 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
739 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
740 v_activity_status_code varchar2(8);
741 v_source_type_code oe_order_lines.source_type_code%type ;
742
743 l_stmt_num number;
744
745 v_header_id oe_order_lines_all.header_id%type ;
746 v_po_header_id po_headers_all.po_header_id%type ;
747 v_authorization_status po_headers_all.authorization_status%type ;
748
749
750 DROPSHIP_EXCEPTION EXCEPTION ;
751
752 BEGIN
753 IF PG_DEBUG <> 0 THEN
754 oe_debug_pub.add('inventory_reservation_check: ' || 'Entering inventory_reservation_check', 2);
755 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'In inventory_reservation_check');
756 End if;
757
758 x_return_status := FND_API.G_RET_STS_SUCCESS;
759
760 l_stmt_num := 10;
761
762 select upper(item_type_code)
763 , ato_line_id
764 , source_type_code
765 , header_id
766 into v_item_type_code
767 , v_ato_line_id
768 , v_source_type_code
769 , v_header_id
770 from oe_order_lines_all
771 where line_id = p_order_line_id;
772
773 -- here, we pass the Standard item line,
774 -- namely, it will return a success. */
775
776 -- removing code to allow reservation for ATOitem
777 -- by kkonada :adding below code again for dummy assignment*/
778 If PG_DEBUG <> 0 then
779 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'ATO Line Id = '||v_ato_line_id);
780 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'Item Type Code = '||v_item_type_code);
781 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'Source Type
782 Code = '||v_source_type_code);
783 End if;
784 /*
785 ** BUG#2234858, BUG#2312701 AND BUG#2358576 Drop Ship changes to disallow reservation
786 ** for config items and ato items of external source type
787 ** ATO ITEMS will behave as strict ATO ITEMS when under a PTO or stand alone.
788 ** ATO ITEMS lose their identity when they are put as options under ATO MODELS
789 ** ATO ITEMS will have ato_line_id = line_id and item_type_code either STANDARD
790 ** or OPTION when their identity is preserved.
791 */
792
793 l_stmt_num := 30;
794
795 if( v_source_type_code = 'EXTERNAL' AND
796 ( ( v_item_type_code = 'CONFIG' ) OR
797 ( ( v_ato_line_id = p_order_line_id AND
798 ( v_item_type_code = 'STANDARD' OR v_item_type_code = 'OPTION' )
799 )
800 )
801 )
802 )then
803
804 Begin
805 begin
806 -- Fixed FP bug 4888964
807 -- For dropship orders, if the first time created PO is
808 -- cancelled and created another PO against the so req
809 -- OM will store two records in oe_drop_ship_sources table
810 -- till 11.5.10. Though, OM will store only one row from R12
811 -- if the data was created before R12, it can still have
812 -- more than one row for a given line id
813 -- We have chaged the sql to look at only non-cancelled po
814 -- to avoid too many rows exception
815
816 select poh.authorization_status
817 into v_authorization_status
818 from po_headers_all poh,
819 po_lines_all pol,
820 oe_drop_ship_sources ods
821 where ods.header_id = v_header_id
822 and ods.line_id = p_order_line_id
823 and ods.po_header_id = poh.po_header_id
824 and pol.po_line_id = ods.po_line_id
825 and nvl(pol.cancel_flag, 'N') <> 'Y';
826 --End of bug fix 4888964
827 exception
828 when others then
829 If PG_DEBUG <> 0 Then
830 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check',
831 'no data found in po_headers_all for om header ' ||
832 to_char( v_header_id ) || ' line id ' ||
833 to_char( p_order_line_id ) || ' po header id ' ||
834 to_char( v_po_header_id ) ) ;
835 End if;
836 raise DROPSHIP_EXCEPTION ;
837 end ;
838
839 exception
840 when DROPSHIP_EXCEPTION then
841 cto_msg_pub.cto_message('BOM', 'CTO_RESERVATION_INELIGIBLE');
842
843 raise FND_API.G_EXC_ERROR;
844
845 when others then
846 raise FND_API.G_EXC_UNEXPECTED_ERROR;
847
848 end;
849
850 end if ;
851
852
853 l_stmt_num := 40;
854
855 if upper(v_item_type_code) = 'CONFIG' then
856
857 -- a config item line
858 -- check the line status is either
859 -- CREATE_SUPPLY_ORDER_ELIGIBLE or
860 -- SHIP_LINE
861
862 l_stmt_num := 50;
863 query_wf_activity_status(G_ITEM_TYPE_NAME,
864 TO_CHAR(p_order_line_id),
865 'EXECUTECONCPROGAFAS',
866 'EXECUTECONCPROGAFAS',
867 v_activity_status_code);
868
869 if upper(v_activity_status_code) <> 'NOTIFIED' then
870
871 l_stmt_num := 60;
872 query_wf_activity_status(G_ITEM_TYPE_NAME,
873 TO_CHAR(p_order_line_id),
874 'CREATE_SUPPLY_ORDER_ELIGIBLE',
875 'CREATE_SUPPLY_ORDER_ELIGIBLE',
876 v_activity_status_code);
877 l_stmt_num := 70;
878 if upper(v_activity_status_code) <> 'NOTIFIED' then
879
880 query_wf_activity_status(G_ITEM_TYPE_NAME,
881 TO_CHAR(p_order_line_id),
882 'SHIP_LINE',
883 'SHIP_LINE',
884 v_activity_status_code);
885
886 if upper(v_activity_status_code) <> 'NOTIFIED' then
887 If PG_DEBUG <> 0 then
888 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'v_activity_status_code <> NOTIFIED');
889 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'Raising CTO_INVALID_ACTIVITY_STATUS');
890 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
891 End if;
892 raise FND_API.G_EXC_ERROR;
893 end if;
894 end if;
895 end if;
896
897 end if;
898 If PG_DEBUG <> 0 then
899 cto_wip_workflow_api_pk.cto_debug('inventory_reservation_check', 'Exiting inventory_reservation_check');
900 oe_debug_pub.add('inventory_reservation_check: ' || 'Exiting inventory_reservation_check', 2);
901 END IF;
902
903 EXCEPTION
904 when FND_API.G_EXC_ERROR then
905 IF PG_DEBUG <> 0 THEN
906 oe_debug_pub.add('inventory_reservation_check: ' || 'exp error in CTO_WORKFLOW_API_PK.inventory_reservation_check::stmt number '||to_char(l_stmt_num), 1);
907 cto_wip_workflow_api_pk.cto_debug('Inventory_reservation_check','Stmt no :'|| to_char(l_stmt_num));
908 End if;
909
910 x_return_status := FND_API.G_RET_STS_ERROR;
911 cto_msg_pub.count_and_get(
912 p_msg_count => x_msg_count,
913 p_msg_data => x_msg_data
914 );
915 when FND_API.G_EXC_UNEXPECTED_ERROR then
916 IF PG_DEBUG <> 0 THEN
917 oe_debug_pub.add('inventory_reservation_check: ' || 'unexp error in CTO_WORKFLOW_API_PK.inventory_reservation_check::stmt number '||to_char(l_stmt_num), 1);
918 cto_wip_workflow_api_pk.cto_debug('Inventory_reservation_check','Stmt no:'|| to_char(l_stmt_num));
919 End if;
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 cto_msg_pub.count_and_get(
922 p_msg_count => x_msg_count,
923 p_msg_data => x_msg_data
924 );
925
926 when others then
927 IF PG_DEBUG <> 0 THEN
928 oe_debug_pub.add('inventory_reservation_check: ' || 'unexp error in CTO_WORKFLOW_API_PK.inventory_reservation_check::stmt number '||to_char(l_stmt_num), 1);
929
930 oe_debug_pub.add('inventory_reservation_check: ' || sqlerrm);
931 cto_wip_workflow_api_pk.cto_debug('Inventory_reservation_check','Stmt no:'||to_char(l_stmt_num) );
932 cto_wip_workflow_api_pk.cto_debug('Inventory_reservation_check', sqlerrm);
933 End if;
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
936 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
937 END IF;
938 cto_msg_pub.count_and_get(
939 p_msg_count => x_msg_count,
940 p_msg_data => x_msg_data
941 );
942
943
944 END inventory_reservation_check;
945
946
947 /*************************************************************************
948 Procedure: wf_update_after_inv_reserv
949 Parameters: p_order_line_id
950 x_return_status - standard API output parameter
951 x_msg_count - "
952 x_msg_data - "
953
954 Description: update an order line status after inventory reservation
955
956 *****************************************************************************/
957 PROCEDURE wf_update_after_inv_reserv(
958 p_order_line_id IN NUMBER,
959 x_return_status OUT NOCOPY VARCHAR2,
960 x_msg_count OUT NOCOPY NUMBER,
961 x_msg_data OUT NOCOPY VARCHAR2
962 )
963 IS
964
965 l_api_name CONSTANT varchar2(40) := 'wf_update_after_inv_reserv';
966 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
967 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
968 v_activity_status_code varchar2(8);
969 return_value integer :=1; --fix for bug#1895563
970 /*******************************************************
971 return value is intialized to 1 in the begining,
972 in order to have only one if condition to check
973 the status returned by display_wf_status called
974 at various places (for an ato item)
975
976 before this fix return_value was not intialized
977 ********************************************************/
978 l_stmt_num number;
979 l_message varchar2(100);
980 l_source_document_type_id number;
981 l_inv_quantity number;
982 l_complete_activity Boolean;
983
984
985 BEGIN
986 If PG_DEBUG <> 0 Then
987 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Entering wf_update_after_inv_reserv');
988 End if;
989
990 IF PG_DEBUG <> 0 THEN
991 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Entering wf_update_after_inv_reserv', 2);
992 END IF;
993 x_return_status := FND_API.G_RET_STS_SUCCESS;
994 l_stmt_num := 10;
995
996 If PG_DEBUG <> 0 Then
997 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'Before selecting info from oe_order_lines_all');
998 End if;
999
1000
1001 select item_type_code,
1002 ato_line_id
1003 into v_item_type_code,
1004 v_ato_line_id
1005 from oe_order_lines_all
1006 where line_id = p_order_line_id;
1007 If PG_DEBUG <> 0 Then
1008 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'ato_line_id = '||v_ato_line_id);
1009 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'v_item_type_code = '||v_item_type_code);
1010 End if;
1011
1012
1013 IF ((upper(v_item_type_code) = 'STANDARD') -- Ato item line
1014 OR (upper(v_item_type_code) = 'OPTION')) --fix for bug#1874380
1015 AND (v_ato_line_id = p_order_line_id) then
1016
1017 -- an ATO item line
1018 -- check if the line status is CREATE_SUPPLY_ORDER_ELIGIBLE or
1019 -- SHIP_LINE
1020
1021 l_stmt_num := 20;
1022
1023 IF PG_DEBUG <> 0 THEN
1024 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'item_type_code is STANDARD OR OPTION => an ato item', 4);
1025 END IF;--fix for bug#1874380
1026 If PG_DEBUG <> 0 Then
1027 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Before querying wf_activity_status..');
1028 End if;
1029
1030 -- Check if workflow is in Create supply order eligible activity
1031
1032 query_wf_activity_status(G_ITEM_TYPE_NAME,
1033 TO_CHAR(p_order_line_id),
1034 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1035 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1036 v_activity_status_code);
1037
1038 If PG_DEBUG <> 0 then
1039 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','v_activity_status_code = '||
1040 upper(v_activity_status_code));
1041 End if;
1042 -- If notified move the workflow to Ship line
1043
1044
1045 if upper(v_activity_status_code) = 'NOTIFIED' then
1046 l_stmt_num := 30;
1047 If PG_DEBUG <> 0 Then
1048 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Complete the workflow
1049 stmt no : 30');
1050 End if;
1051 -- Modified for MOAC project
1052 -- Called complete_acitivity procedure instead of calling wf_engine api.
1053
1054 If (CTO_WORKFLOW_API_PK.complete_activity(
1055 p_itemtype => G_ITEM_TYPE_NAME,
1056 p_itemkey => to_char(p_order_line_id),
1057 p_activity_name => 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1058 p_result_code => 'RESERVED')) Then
1059
1060 If PG_DEBUG <> 0 Then
1061 oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity is successful',5);
1062 End if;
1063
1064 Else
1065 if PG_DEBUG <> 0 Then
1066 oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity function returned error..',5);
1067 end if;
1068 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1069 End if;
1070
1071 -- End of MOAC Change
1072
1073 --start of fix for bug#1895563
1074 If PG_DEBUG <> 0 Then
1075 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Before calling display_wf_status..'||
1076 'from l_stmt_num=>'||l_stmt_num);
1077 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Before calling display_wf_status.. from l_stmt_num=>'||l_stmt_num,2);
1078 END IF;
1079
1080 return_value := display_wf_status(p_order_line_id);
1081
1082 If PG_DEBUG <> 0 Then
1083 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Return value after display_wf_status'
1084 ||to_char(return_value));
1085 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Return value after display_wf_status at l_stmt_num=>'||l_stmt_num||'is'
1086 ||to_char(return_value),1);
1087 END IF;
1088 --end of fix for bug#1895563
1089
1090
1091 -- follwoing else block was once removed but added again with some modifications to fix
1092 -- bug#1895563
1093 -- start of fix for bug#1895563
1094
1095 else -- not in supply order eligible
1096
1097 l_stmt_num := 40;
1098 If PG_DEBUG <> 0 then
1099 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Stmt no : 40, query
1100 wf_activity status..');
1101 End if;
1102
1103 query_wf_activity_status(G_ITEM_TYPE_NAME,
1104 TO_CHAR(p_order_line_id),
1105 'EXECUTECONCPROGAFAS',
1106 'EXECUTECONCPROGAFAS',
1107 v_activity_status_code);
1108
1109 If PG_DEBUG <> 0 then
1110 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','v_activity_status_code = '||
1111 v_activity_status_code);
1112
1113 End if;
1114 IF upper(v_activity_status_code) <> 'NOTIFIED' THEN
1115
1116 l_stmt_num := 50;
1117
1118 If PG_DEBUG <> 0 Then
1119 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Stmt no : 50
1120 querying wf_activity_status');
1121 End if;
1122 query_wf_activity_status(G_ITEM_TYPE_NAME,
1123 TO_CHAR(p_order_line_id),
1124 'SHIP_LINE',
1125 'SHIP_LINE',
1126 v_activity_status_code);
1127
1128 If PG_DEBUG <> 0 Then
1129 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'v_actitivity_status_code ='||
1130 v_activity_status_code);
1131 End if;
1132 IF upper(v_activity_status_code) = 'NOTIFIED' then
1133
1134
1135 If PG_DEBUG <> 0 Then
1136 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
1137 'Before calling display_wf_status..'||
1138 'from l_stmt_num=>'||l_stmt_num);
1139 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Before calling display_wf_status.. from l_stmt_num=>'||l_stmt_num,2);
1140 END IF;
1141
1142 return_value := display_wf_status(p_order_line_id);
1143
1144
1145 If PG_DEBUG <> 0 Then
1146 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
1147 'Return value after display_wf_status'
1148 ||to_char(return_value));
1149 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Return value after display_wf_status at l_stmt_num=>'||l_stmt_num||'is'
1150 ||to_char(return_value),1);
1151 END IF;
1152 END IF;
1153
1154 ELSE -- Auto create fas Notified
1155
1156 If PG_DEBUG <> 0 Then
1157 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Before calling display_wf_status..'||
1158 'from l_stmt_num=>'||l_stmt_num);
1159 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Before calling display_wf_status.. from l_stmt_num=>'||l_stmt_num,2);
1160 END IF;
1161
1162 return_value := display_wf_status(p_order_line_id);
1163
1164
1165 If PG_DEBUG <> 0 Then
1166 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Return value after display_wf_status'
1167 ||to_char(return_value));
1168 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Return value after display_wf_status at l_stmt_num=>'||l_stmt_num||'is'
1169 ||to_char(return_value),1);
1170 END IF;
1171 END IF;
1172 --end of fix for bug#1895563
1173
1174 end if;
1175 if return_value <> 1 then
1176 If PG_DEBUG <> 0 Then
1177 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Raising CTO_ERROR_FROM_DISPLAY_STATUS');
1178 End if;
1179 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
1180 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1181 end if;
1182
1183
1184 elsif upper(v_item_type_code) = 'CONFIG' then
1185
1186 -- a config item line
1187 -- check the line status is either CREATE_CONFIG_BOM_ELIGIBLE - removed or
1188 -- CREATE_SUPPLY_ORDER_ELIGIBLE
1189 -- SHIP_LINE
1190
1191 l_stmt_num := 60;
1192
1193 If PG_DEBUG <> 0 Then
1194 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
1195 ' Stmt no : 60 , querying activity_status');
1196 End if;
1197
1198 query_wf_activity_status(G_ITEM_TYPE_NAME,
1199 TO_CHAR(p_order_line_id),
1200 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1201 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1202 v_activity_status_code);
1203
1204 If PG_DEBUG <> 0 Then
1205 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' v_actitivity_status_code = '||
1206 v_activity_status_code);
1207 End if;
1208
1209 IF upper(v_activity_status_code) = 'NOTIFIED' then
1210
1211 l_stmt_num := 70;
1212
1213 If PG_DEBUG <> 0 Then
1214 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Stmt no : 70, Completing
1215 workflow activity..');
1216 End if;
1217
1218 -- Modified for MOAC project
1219 -- Called complete_acitivity procedure instead of calling wf_engine api.
1220
1221 IF (CTO_WORKFLOW_API_PK.complete_activity(
1222 p_itemtype => G_ITEM_TYPE_NAME,
1223 p_itemkey => to_char(p_order_line_id),
1224 p_activity_name => 'CREATE_SUPPLY_ORDER_ELIGIBLE',
1225 p_result_code => 'RESERVED')) Then
1226 If PG_DEBUG <> 0 Then
1227 oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: complete_activity is sucessful ',5);
1228 End if;
1229 Else
1230 if PG_DEBUG <> 0 Then
1231 oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity function returned error..',5);
1232 end if;
1233 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1234 End if;
1235
1236 -- End of MOAC Change
1237
1238
1239 ELSE
1240
1241 l_stmt_num := 80;
1242 if pG_DEBUG <> 0 Then
1243 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Stmt no : 80 querying
1244 wf_activity_status');
1245 end if;
1246 query_wf_activity_status(G_ITEM_TYPE_NAME,
1247 TO_CHAR(p_order_line_id),
1248 'EXECUTECONCPROGAFAS',
1249 'EXECUTECONCPROGAFAS',
1250 v_activity_status_code);
1251
1252 If PG_DEBUG <> 0 Then
1253 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','v_activity_status_code = '||
1254 v_activity_status_code);
1255 End if;
1256
1257 IF upper(v_activity_status_code) <> 'NOTIFIED' then
1258
1259 l_stmt_num := 90;
1260
1261 If PG_DEBUG <> 0 Then
1262 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Stmt no : 90,
1263 querying wf_activity_status');
1264 End if;
1265 query_wf_activity_status(G_ITEM_TYPE_NAME,
1266 TO_CHAR(p_order_line_id),
1267 'SHIP_LINE',
1268 'SHIP_LINE',
1269 v_activity_status_code);
1270
1271 If PG_DEBUG <> 0 Then
1272 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', ' v_activity_status_code = '||
1273 v_activity_status_code);
1274 End if;
1275
1276 if upper(v_activity_status_code) <> 'NOTIFIED' then
1277
1278 If PG_DEBUG <> 0 Then
1279 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_reserv', 'v_activity_status_code <> NOTIFIED');
1280 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_reserv', 'Raising CTO_INVALID_ACTIVITY_STATUS');
1281 End if;
1282
1283 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
1284 raise FND_API.G_EXC_ERROR;
1285 end if;
1286
1287 END IF; /* end of v_activity_status_code check */
1288
1289 END IF;
1290
1291 -- 2350079 : moved the call display_wf_status from outside the IF clause to inside the clause.
1292
1293 -- display proper status to OM form
1294
1295 If PG_DEBUG <> 0 Then
1296 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Before calling display_wf_status');
1297 End if;
1298
1299 return_value := display_wf_status(p_order_line_id);
1300
1301 If PG_DEBUG <> 0 Then
1302 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Return_value from display_wf_status = '||
1303 to_char(return_value));
1304 End if;
1305
1306 IF return_value <> 1 then
1307 IF PG_DEBUG <> 0 Then
1308 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV','Raising CTO_ERROR_FROM_DISPLAY_STATUS');
1309 End if;
1310 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
1311 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1312 END IF;
1313
1314 end if; /* end of item_type_code check */
1315 If PG_DEBUG <> 0 then
1316 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',' Exiting wf_update_after_inv_reserv');
1317 End if;
1318 IF PG_DEBUG <> 0 THEN
1319 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'Exiting wf_update_after_inv_reserv', 2);
1320 END IF;
1321
1322 EXCEPTION
1323 when FND_API.G_EXC_ERROR then
1324 IF PG_DEBUG <> 0 THEN
1325 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'exp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_reserv::stmt number '||to_char(l_stmt_num), 1);
1326 END IF;
1327
1328 If PG_DEBUG <> 0 Then
1329 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
1330 'Exp error in CTO_WORKFLOW_API_PK.wf_update_inv_reserv::
1331 stmt no : '||to_char(l_stmt_num)||'::'||sqlerrm);
1332 End if;
1333
1334 x_return_status := FND_API.G_RET_STS_ERROR;
1335 cto_msg_pub.count_and_get(
1336 p_msg_count => x_msg_count,
1337 p_msg_data => x_msg_data
1338 );
1339
1340 when FND_API.G_EXC_UNEXPECTED_ERROR then
1341 IF PG_DEBUG <> 0 THEN
1342 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_reserv::stmt number '||to_char(l_stmt_num), 1);
1343 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV',
1344 'UNExp error in CTO_WORKFLOW_API_PK.wf_update_inv_reserv::
1345 stmt no : '||to_char(l_stmt_num)||'::'||sqlerrm);
1346 End if;
1347
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1349 cto_msg_pub.count_and_get(
1350 p_msg_count => x_msg_count,
1351 p_msg_data => x_msg_data
1352 );
1353
1354 when others then
1355 IF PG_DEBUG <> 0 THEN
1356 oe_debug_pub.add('wf_update_after_inv_reserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_reserv::stmt number '||to_char(l_stmt_num), 1);
1357
1358 oe_debug_pub.add('wf_update_after_inv_reserv: ' || sqlerrm, 1);
1359 END IF;
1360 If PG_DEBUG <> 0 Then
1361 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', 'OTHERS excpn: stmt no : '||to_char(l_stmt_num));
1362 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_RESERV', sqlerrm);
1363 End if;
1364
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1366 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1367 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1368 END IF;
1369 cto_msg_pub.count_and_get(
1370 p_msg_count => x_msg_count,
1371 p_msg_data => x_msg_data
1372 );
1373
1374
1375 END wf_update_after_inv_reserv;
1376
1377
1378
1379 /*************************************************************************
1380 Procedure: inventory_unreservation_check
1381 Parameters: p_order_line_id
1382 p_rsv_quantity - Unreservation Quantity
1383 x_return_status - standard API output parameter
1384 x_msg_count - "
1385 x_msg_data - "
1386
1387 Description: Check if an order line status is
1388 "SHIP_LINE"
1389 *****************************************************************************/
1390 PROCEDURE inventory_unreservation_check(
1391 p_order_line_id IN NUMBER,
1392 p_rsv_quantity IN NUMBER, --bugfix 2001824: Added parameter p_rsv_quantity
1393 x_return_status OUT NOCOPY VARCHAR2,
1394 x_msg_count OUT NOCOPY NUMBER,
1395 x_msg_data OUT NOCOPY VARCHAR2
1396 )
1397 IS
1398
1399 l_api_name CONSTANT VARCHAR2(40) := 'inventory_unreservation_check';
1400 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
1401 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
1402 v_activity_status_code VARCHAR2(8);
1403 l_stmt_num number;
1404
1405 BEGIN
1406 IF PG_DEBUG <> 0 THEN
1407 oe_debug_pub.add('inventory_unreservation_check: ' || 'Entering inventory_unreservation_check', 2);
1408 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check','Entering inventory_unreservation_check');
1409 End if;
1410
1411 x_return_status := FND_API.G_RET_STS_SUCCESS;
1412 l_stmt_num := 10;
1413
1414 select item_type_code, ato_line_id
1415 into v_item_type_code, v_ato_line_id
1416 from oe_order_lines_all
1417 where line_id = p_order_line_id;
1418
1419 --
1420 -- For CONFIG items, unreservation is allowed only when the workflow has moved to SHIP LINE.
1421 -- You cannot un-reserve a CONFIG line (at present) before a work-order is created.
1422 -- If this API is called before or after SHIP_LINE, then, raise error.
1423 --
1424
1425 if (upper(v_item_type_code) = 'CONFIG') then
1426 l_stmt_num := 20;
1427 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(p_order_line_id),
1428 'SHIP_LINE',
1429 'SHIP_LINE', v_activity_status_code);
1430 if upper(v_activity_status_code) <> 'NOTIFIED' then
1431 If PG_DEBUG <> 0 Then
1432 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', 'v_activity_status_code <> NOTIFIED');
1433 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', 'Raising CTO_INVALID_ACTIVITY_STATUS');
1434 End if;
1435 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
1436 raise FND_API.G_EXC_ERROR;
1437 end if;
1438 end if;
1439
1440 --
1441 -- For ATO items and CONFIG items, we should check the qty being unreserved.
1442 --
1443
1444 if (((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
1445 --fix for bug#1874380
1446 AND (v_ato_line_id = p_order_line_id ))
1447 OR
1448 (upper(v_item_type_code) = 'CONFIG' )
1449 then
1450
1451 --begin bugfix 2001824 : check if qty being unreserved is ok
1452 If PG_DEBUG <> 0 Then
1453 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', 'p_rsv_quantity'||p_rsv_quantity);
1454 End if;
1455 if (CTO_UTILITY_PK.check_rsv_quantity (p_order_line_id => p_order_line_id,
1456 p_rsv_quantity => p_rsv_quantity) = FALSE )
1457 then
1458 IF PG_DEBUG <> 0 THEN
1459 oe_debug_pub.add ('inventory_unreservation_check: ' || 'Unreservation of qty '||p_rsv_quantity ||
1460 ' is NOT allowed since part of this has been either ship confirmed, intrasit or closed.', 2);
1461 END IF;
1462
1463 If PG_DEBUG <> 0 Then
1464 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', 'v_activity_status_code <> NOTIFIED');
1465 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', 'Raising CTO_INVALID_ACTIVITY_STATUS');
1466 End if;
1467 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
1468 raise FND_API.G_EXC_ERROR;
1469 else
1470 IF PG_DEBUG <> 0 THEN
1471 oe_debug_pub.add ('inventory_unreservation_check: ' || 'Quantity being unreserved ('||p_rsv_quantity||') is okay.',4);
1472 END IF;
1473 end if;
1474
1475 --end bugfix 2001824
1476 end if;
1477
1478 IF PG_DEBUG <> 0 THEN
1479 oe_debug_pub.add('inventory_unreservation_check: ' || 'Exiting inventory_unreservation_check', 2);
1480 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', 'Exiting inventory_unreservation_check');
1481 End if;
1482
1483 EXCEPTION
1484
1485 when FND_API.G_EXC_ERROR then
1486 IF PG_DEBUG <> 0 THEN
1487 oe_debug_pub.add('inventory_unreservation_check: ' || 'exp error in CTO_WORKFLOW_API_PK.inventory_unreservation_check::stmt number '||to_char(l_stmt_num), 1);
1488 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check',
1489 'Raised FND_API.G_EXC_ERROR, stmt'||l_stmt_num);
1490 End if;
1491 x_return_status := FND_API.G_RET_STS_ERROR;
1492 cto_msg_pub.count_and_get(
1493 p_msg_count => x_msg_count,
1494 p_msg_data => x_msg_data
1495 );
1496
1497 when FND_API.G_EXC_UNEXPECTED_ERROR then
1498 IF PG_DEBUG <> 0 THEN
1499 oe_debug_pub.add('inventory_unreservation_check: ' || 'unexp error in CTO_WORKFLOW_API_PK.inventory_unreservation_check::stmt number '||to_char(l_stmt_num), 1);
1500 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check',
1501 'Raised FND_API.G_EXC_UNEXPECTED_ERROR, stmt'||l_stmt_num);
1502 End if;
1503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1504 cto_msg_pub.count_and_get(
1505 p_msg_count => x_msg_count,
1506 p_msg_data => x_msg_data
1507 );
1508
1509 when others then
1510 IF PG_DEBUG <> 0 THEN
1511 oe_debug_pub.add('inventory_unreservation_check: ' || 'unexp error in CTO_WORKFLOW_API_PK.inventory_unreservation_check::stmt number '||to_char(l_stmt_num), 1);
1512
1513 oe_debug_pub.add('inventory_unreservation_check: ' || sqlerrm, 1);
1514 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check',
1515 'Raised OTHERS excepn, stmt: '||l_stmt_num);
1516 cto_wip_workflow_api_pk.cto_debug('inventory_unreservation_check', sqlerrm);
1517 End if;
1518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1519 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1520 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1521 END IF;
1522 cto_msg_pub.count_and_get(
1523 p_msg_count => x_msg_count,
1524 p_msg_data => x_msg_data
1525 );
1526
1527
1528 END inventory_unreservation_check;
1529
1530
1531 /*************************************************************************
1532 Procedure: wf_update_after_inv_unreserv
1533 Parameters: p_order_line_id
1534 x_return_status - standard API output parameter
1535 x_msg_count - "
1536 x_msg_data - "
1537
1538 Description: update an order line status after inventory unreservation
1539
1540
1541 The logic for this procedure is as follows:
1542
1543 1. IF the Workflow is in Ship Notified then check for the reservation (All kinds)
1544 for this line. If there is no reservation found, Move the workflow to Create
1545 Supply order eligible.
1546
1547 2. IF the line is not in Ship notified then, Check if it is a config line. If it is
1548 a config line error out.
1549
1550 3. Call display_wf_status for all the scenarios.
1551
1552 4. In the case ATO item, No action is taken if the line is not in Ship notified.
1553
1554 5. This API is called for all types if items.
1555
1556 *****************************************************************************/
1557
1558
1559 PROCEDURE wf_update_after_inv_unreserv(
1560 p_order_line_id IN NUMBER,
1561 x_return_status OUT NOCOPY VARCHAR2,
1562 x_msg_count OUT NOCOPY NUMBER,
1563 x_msg_data OUT NOCOPY VARCHAR2
1564 )
1565 IS
1566
1567 l_api_name CONSTANT varchar2(40) := 'wf_update_after_inv_unreserv';
1568 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
1569 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
1570 v_activity_status_code varchar2(8);
1571 v_counter integer;
1572 v_counter2 integer;
1573 return_value integer;
1574 l_stmt_num number;
1575 l_source_document_type_id number; -- bugfix 1799874
1576 l_message varchar2(100);
1577 l_po_req_qty Number;
1578 l_split_from_line_id oe_order_lines_all.split_from_line_id%TYPE;
1579
1580 l_dummy number;
1581
1582
1583 -- added by Renga Kannan on 09/05/02 for ATO Back order changes
1584
1585 l_changed_attributes WSH_INTERFACE.ChangedAttributeTabType;
1586
1587 -- Done Renga Kannan
1588
1589 --added by Kiran
1590 l_inventory_item_id number;
1591 l_ship_from_org_id number;
1592 l_can_create_supply varchar2(1);
1593 --l_source_type varchar2(1);
1594 l_source_type number; --Bugfix 6470516
1595 --done added by kiran
1596
1597 -- Fix for performance bug 4897231
1598 -- To avoid full table scan on po_requisitions_table
1599 -- added another where condition for item_id
1600 -- Po_req_interface table has index on item_id column
1601
1602 cursor INT_REQ is
1603 SELECT interface_source_line_id
1604 FROM po_requisitions_interface_alL
1605 WHERE interface_source_line_id = l_split_from_line_id
1606 AND process_flag is NULL
1607 and item_id = l_inventory_item_id
1608 FOR UPDATE of interface_source_line_id NOWAIT;
1609
1610
1611
1612 -- begin bugfix 2109503
1613 v_x_return_status varchar2(1);
1614 v_x_msg_count number;
1615 v_x_msg_data varchar2(2000);
1616
1617
1618
1619 -- for new shipping min max tolerance api
1620 l_in_attributes WSH_INTEGRATION.MinMaxInRecType;
1621 l_out_attributes WSH_INTEGRATION.MinMaxOutRecType;
1622 l_inout_attributes WSH_INTEGRATION.MinMaxInOutRecType;
1623
1624 --ireq and opm
1625 l_sourcing_org number;
1626 l_return_msg varchar2(100);
1627
1628 -- Bug Fix 4863275
1629 l_ship_xfaced_flag varchar2(1);
1630 v_open_flag varchar2(1); -- Bugfix 7214005
1631
1632 BEGIN
1633 If PG_DEBUG <> 0 Then
1634 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'In wf_update_after_inv_unreserv');
1635 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Entering wf_update_after_inv_unreserv', 2);
1636 END IF;
1637
1638 x_return_status := FND_API.G_RET_STS_SUCCESS;
1639 l_stmt_num := 10;
1640
1641
1642 -- Modified to get the split from line_id also
1643 -- Bug fix 4863275. Added shiping_interfaced_flag in the select list
1644
1645 select item_type_code,
1646 ato_line_id,
1647 split_from_line_id,
1648 inventory_item_id, --added by kiran
1649 ship_from_org_id, --added by kiran
1650 nvl(shipping_interfaced_flag,'N'), -- Bug fix 4863275
1651 open_flag -- Bugfix 7214005
1652 into
1653
1654 v_item_type_code,
1655 v_ato_line_id,
1656 l_split_from_line_id,
1657 l_inventory_item_id,
1658 l_ship_from_org_id,
1659 l_ship_xfaced_flag, -- Bug fix 4863275
1660 v_open_flag -- Bugfix 7214005
1661 from oe_order_lines_all
1662 where line_id = p_order_line_id;
1663
1664 If PG_DEBUG <> 0 Then
1665 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'ATO Line Id = '||v_ato_line_id);
1666 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Item Type Code = '||v_item_type_code);
1667 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Open Flag = '||v_open_flag);
1668 oe_debug_pub.add('wf_update_after_inv_unreserv ' || 'ATO Line Id ='||v_ato_line_id, 1);
1669 oe_debug_pub.add('wf_update_after_inv_unreserv ' || 'Item Type Code ='||v_item_type_code, 1);
1670 oe_debug_pub.add('wf_update_after_inv_unreserv ' || 'Open Flag ='||v_open_flag, 1);
1671 End if;
1672
1673 if v_open_flag = 'Y' then --Bugfix 7214005
1674 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_order_line_id ); --bugfix 1799874
1675
1676 IF ((upper(v_item_type_code) = 'STANDARD' OR upper(v_item_type_code) = 'OPTION' ) --fix for bug# 1874380
1677 AND (v_ato_line_id = p_order_line_id))
1678 OR upper(v_item_type_code) = 'CONFIG'
1679 THEN
1680
1681 -- an ATO item line or CONFIG line
1682 -- check if the line status is SHIP_LINE
1683
1684 -- Unreserve Activity is allowed only if the ATO/Config line is
1685 -- In Ship line activity. Otherwise we cannot unreserve
1686 -- But due to ATO item enhancement and AUTO Create project
1687 -- There are some variations for this rule.
1688 -- Those variations are handled in the else clause .
1689
1690
1691 l_stmt_num := 20;
1692
1693 query_wf_activity_status(G_ITEM_TYPE_NAME,
1694 TO_CHAR(p_order_line_id),
1695 'SHIP_LINE',
1696 'SHIP_LINE',
1697 v_activity_status_code);
1698
1699 IF upper(v_activity_status_code) = 'NOTIFIED' THEN
1700
1701 IF PG_DEBUG <> 0 THEN
1702 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Ship Line Notified...',1);
1703 END IF;
1704
1705 --to check if cto or planning has created the supply
1706 CTO_UTILITY_PK.check_cto_can_create_supply (
1707 P_config_item_id =>l_inventory_item_id,
1708 P_org_id =>l_ship_from_org_id,
1709 x_can_create_supply =>l_can_create_supply,
1710 p_source_type =>l_source_type,
1711 x_return_status =>x_return_status,
1712 X_msg_count =>x_msg_count,
1713 X_msg_data =>x_msg_data,
1714 x_sourcing_org =>l_sourcing_org, --R12 IREQ,OPM new parameter
1715 x_message =>l_return_msg --R12 IREQ,OPM new parameter
1716 );
1717
1718
1719 IF PG_DEBUG <> 0 THEN
1720 oe_debug_pub.add('wf_update_after_inv_unreserv: ' ||'check_cto_can_create_supply'
1721 ||'x_return_status=> ' || x_return_status);
1722
1723 oe_debug_pub.add('wf_update_after_inv_unreserv: ' ||
1724 'l_can_create_supply is=>'||l_can_create_supply,1);
1725
1726 END IF;
1727
1728 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1729 RAISE FND_API.G_EXC_ERROR;
1730 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1731 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1732 END IF;
1733
1734 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1735 and
1736 l_can_create_supply = 'Y' THEN
1737
1738 v_counter := 0;
1739 l_stmt_num := 30;
1740
1741
1742 -- Look at the Qty reserved in MTL_RESERVATION_TABLE
1743
1744 -- This reservation looks for work order, purchase related and inv reservation
1745
1746 select count(*)
1747 into v_counter
1748 from mtl_reservations
1749 where demand_source_type_id = decode (l_source_document_type_id, 10,
1750 inv_reservation_global.g_source_type_internal_ord,
1751 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
1752 and demand_source_line_id = p_order_line_id
1753 and primary_reservation_quantity > 0;
1754
1755 If PG_DEBUG <> 0 Then
1756 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'v_counter = '||v_counter);
1757 End if;
1758
1759 v_counter2 := 0;
1760 l_stmt_num := 40;
1761
1762 -- bugfix 1799874 : as per adrian suherman, we need not worry about internal SO for wip_flow_schedules.
1763
1764 -- Look at the reservation qty in Flow schedule table
1765
1766 select count(*)
1767 into v_counter2
1768 from wip_flow_schedules
1769 where demand_source_type = 2
1770 and demand_source_line = to_char(p_order_line_id)
1771 and status <> 2; -- 3076061 Flow Schedule status : 1 = Open 2 = Closed/Completed
1772
1773
1774 -- begin bugfix 3174334
1775 -- Since flow does not update the schedule with new line_id when the order line is split, we need
1776 -- to call the following function which will determine the open quantity.
1777 -- If open_qty exists, we should keep the workflow in SHIP_LINE.
1778
1779 if v_counter2 = 0 then
1780 v_counter2 :=
1781 MRP_FLOW_SCHEDULE_UTIL.GET_FLOW_QUANTITY( p_demand_source_line => to_char(p_order_line_id),
1782 p_demand_source_type => inv_reservation_global.g_source_type_oe,
1783 p_demand_source_delivery => NULL,
1784 p_use_open_quantity => 'Y');
1785 end if;
1786 -- end bugfix 3174334
1787
1788 If PG_DEBUG <> 0 Then
1789 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'v_counter2 = '||v_counter2);
1790 End if;
1791 ---Modified by Renga Kannan on 12/18/2000
1792
1793 -- Modified by Renga Kannan on 14-NOV-2001
1794
1795 -- In the case of BUY ATO, If the line is being split because of partial shipping
1796 -- There is a possibility that some records may still be in the interface table
1797 -- for the parent line. This API will get called for the new line. If this new line
1798 -- is because of split action, then we should try to update the interface table
1799 -- with this line id.
1800
1801 -- We can find whether this line is split line by looking at the split_from_line_id column
1802 -- If this column is populated that means the line is a split line.
1803
1804 IF l_split_from_line_id is not null THEN
1805
1806 BEGIN
1807 FOR c1 IN INT_REQ
1808 LOOP
1809
1810 UPDATE PO_REQUISITIONS_INTERFACE_ALL
1811 SET interface_source_line_id = p_order_line_id
1812 WHERE CURRENT OF INT_REQ;
1813
1814 END LOOP;
1815 EXCEPTION WHEN OTHERS THEN
1816 Null;
1817 END;
1818
1819 END IF;--split line id
1820
1821 -- For Buy models if the line has some interface records without error that
1822 -- Should be considered as reservation
1823 -- Fix for performance bug 4897231
1824 -- To avoid full table scan on po_requisitions_table
1825 -- added another where condition for item_id
1826 -- Po_req_interface table has index on item_id column
1827
1828 SELECT Nvl(Sum(quantity),0)
1829 INTO l_po_req_qty
1830 FROM po_requisitions_interface_all
1831 WHERE interface_source_line_id = p_order_line_id
1832 and item_id = l_inventory_item_id
1833 AND process_flag is null;
1834
1835 If PG_DEBUG <> 0 Then
1836 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'l_po_req_qty = '||l_po_req_qty);
1837 End if;
1838
1839 -- no reservation at all
1840
1841 -- Begin bugfix 2109503
1842
1843 --
1844 -- bugfix 2109503
1845 --
1846 -- We need to check from OM if its okay to update the workflow back to create supply order
1847 -- eligible. We will call Get_Min_max_Tolerance_Quantity to see whether OM will fulfil this line.
1848 -- If so, we will not update the w/f
1849 -- Eg, OQ=10, ship tolerance belo=20%; WO Qty=10; Complete=8; Ship=8; Inv i/f;
1850 -- This leaves reservation of qty=2 against wip.
1851 -- Running OM i/f will result in closing this line since qty 8 is within undership tolerance
1852 -- OM/Shipping will call Inv to delete any reservations existing and inv will unreserve qty=2
1853 -- after validating against CTO. Now, wf_update_after_inv_unreserv will make the line back to
1854 -- create supply order eligible !! and OM i/f will fail since it expect it to be in ship
1855 -- notified.
1856 --
1857 -- Get_Min_Max_Tolerance_Quantity will return x_min_remaining_quantity=0 if fulfilled.
1858 -- If this value is 0, then, we do not update the w/f status back to create supply eligible.
1859 --
1860
1861 If l_ship_xfaced_flag = 'Y' Then
1862 IF PG_DEBUG <> 0 THEN
1863 oe_debug_pub.add ('wf_update_after_inv_unreserv: ' || 'CTO: Calling OE_Shipping_Integration_Pub.Get_Min_Max_Tolerance_Quantity..', 4);
1864 END IF;
1865
1866 -- rkaza. 04/18/2005 bug 2985672
1867 -- Calling shipping tolerance api as a replacement of OM's api.
1868
1869 l_in_attributes.api_version_number := 1.0;
1870 l_in_attributes.source_code := 'OE';
1871 l_in_attributes.line_id := p_order_line_id;
1872
1873 WSH_INTEGRATION.Get_Min_Max_Tolerance_Quantity
1874 ( p_in_attributes => l_in_attributes,
1875 p_out_attributes => l_out_attributes,
1876 p_inout_attributes => l_inout_attributes,
1877 x_return_status => v_x_return_status,
1878 x_msg_count => v_x_msg_count,
1879 x_msg_data => v_x_msg_data);
1880
1881 IF (v_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1882 IF PG_DEBUG <> 0 THEN
1883 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv',
1884 'Failed in WSH_Integration_Pub.Get Tolerance');
1885 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'CTO:Failed in WSH_Integration_Pub.Get Tolerance :' || v_x_return_status, 1);
1886 END IF;
1887 OE_MSG_PUB.ADD;
1888 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1889 ELSE
1890 IF PG_DEBUG <> 0 THEN
1891 oe_debug_pub.add ('wf_update_after_inv_unreserv: ' || 'CTO: Returned from WSH_Integration_Pub.Get_Min_Max_Tolerance_Quantity.', 4);
1892
1893 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'v_counter = '|| v_counter, 4);
1894
1895 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'v_counter2 = '|| v_counter2, 4);
1896
1897 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_min_remaining_quantity = '|| l_out_attributes.min_remaining_quantity, 4);
1898
1899 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_max_remaining_quantity = '|| l_out_attributes.max_remaining_quantity, 4);
1900
1901 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_min_remaining_quantity2 = '|| l_out_attributes.min_remaining_quantity2, 4);
1902
1903 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'x_max_remaining_quantity2 = '|| l_out_attributes.max_remaining_quantity2, 4);
1904 END IF;
1905 END IF ;--get min max status if block
1906 End If;
1907 -- End bugfix 2109503
1908
1909 -- With post-CMS (change mgmt from OM), unreservation will be triggered from shipping rather than OM.
1910 -- OM.
1911
1912 IF PG_DEBUG <> 0 THEN
1913 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'v_x_min_remaining_quantity = '|| l_out_attributes.min_remaining_quantity);
1914 cto_wip_workflow_api_pk.cto_debug('Wf_update_after_inv_unreserve',' Shipping interfaced flag = '||l_ship_xfaced_flag);
1915 oe_debug_pub.add('Wf_update_after_inv_unreserve: Shipping interfaced flag = '||l_ship_xfaced_flag);
1916 end if;
1917
1918 -- bugfix 2118864
1919 -- If there is no reservation for this line and if the line is not
1920 -- fulfiled, then, Workflow will be moved to Create supply order
1921 -- eligible.
1922
1923 if v_counter = 0 and
1924 v_counter2 = 0 and
1925 l_po_req_qty = 0 and
1926 (l_ship_xfaced_flag = 'N' or nvl(l_out_attributes.min_remaining_quantity,0) > 0 )
1927 then
1928
1929 IF PG_DEBUG <> 0 Then
1930 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'No Reservations exists... Updating workflow.');
1931 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'No Reservation Exists...',1);
1932 END IF;
1933
1934 l_stmt_num := 50;
1935
1936 -- bugfix 3076061
1937 -- The following code should not be executed unless OM.I is installed.
1938 -- need to make WSHCRCNS as prereq
1939
1940 -- Bugfix 4863275: Added l_ship_xface_flag condition. Only if the line is interfaced to shipping.
1941
1942
1943 if (WSH_CODE_CONTROL.Get_Code_Release_Level > '110508' and l_ship_xfaced_flag = 'Y') then
1944
1945 -- The commented part for shipping change and will be tested later.
1946 -- Added by Renga Kannan on 09/05/02 to set the delivery details flag to 'N'
1947
1948 -- bugfix 3202736: changed the index from 0 to 1.
1949
1950 l_changed_attributes(1).source_line_id := p_order_line_id;
1951 l_changed_attributes(1).released_status := 'N';
1952 l_changed_attributes(1).action_flag := 'U';
1953
1954 IF PG_DEBUG <> 0 THEN
1955 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Renga: Updating the shipping attributes..',1);
1956 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv',
1957 'Renga : CAlling Shipping update attribute api');
1958 End if;
1959 WSH_INTERFACE.Update_Shipping_Attributes
1960 ( p_source_code => 'OE'
1961 , p_changed_attributes => l_changed_attributes
1962 , x_return_status => v_x_return_status
1963 );
1964
1965
1966 IF (v_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1967
1968 IF PG_DEBUG <> 0 THEN
1969 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv',
1970 'WSH_INTERFACE.update_shipping_attributes');
1971 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'CTO:Failed in WSH_INTERFACE.update_shipping_attributes :', 1);
1972 END IF;
1973 OE_MSG_PUB.ADD;
1974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1975 ELSE
1976 IF PG_DEBUG <> 0 THEN
1977 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || ' Renga: Success in update_shipping attributes..', 2);
1978 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserve',
1979 ' Renga: Success in update_shipping attributes..');
1980
1981 END IF;
1982 END IF ;
1983 end if; -- bugfix 3076061 Get_Code_Release_Level
1984 -- Fix for bug 5357300
1985 -- Replaces the direct workflow engine call wf_engine.CompleteActivityInternalName
1986 -- To CTO wrapper api call. As the cto wrapper api will take care of org context switch
1987
1988 If (CTO_WORKFLOW_API_PK.complete_activity(
1989 p_itemtype => G_ITEM_TYPE_NAME,
1990 p_itemkey => to_char(p_order_line_id),
1991 p_activity_name => 'SHIP_LINE',
1992 p_result_code => 'UNRESERVE')) Then
1993
1994 If PG_DEBUG <> 0 Then
1995 oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity is successful',5);
1996 End if;
1997
1998 Else
1999 if PG_DEBUG <> 0 Then
2000 oe_debug_pub.add('WF_UPDATE_AFTER_INV_RESERV: Complete_activity function returned error..',5);
2001 end if;
2002 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2003 End if;
2004 -- End of bug fix 5357300
2005 else
2006 IF PG_DEBUG <> 0 THEN
2007 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Reservation Exists. Will not update workflow status.',1);
2008 END IF;
2009 end if; --v_counter
2010
2011 END IF; --status of check_cto_can_create_supply
2012 --start of fix for bug#1895563
2013 --this call was moved here from the end of procedure
2014
2015 -- display proper status to OM form
2016
2017 return_value := display_wf_status(p_order_line_id);
2018
2019 if return_value <> 1 then
2020
2021 if PG_DEBUG <> 0 Then
2022 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Raising CTO_ERROR_FROM_DISPLAY_STATUS');
2023 End if;
2024 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
2025 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2026
2027 end if;
2028 --end of fix for bug#1895563
2029
2030
2031 --- Modified by Renga Kannan on 0p9/25/02
2032 --- We should not error out when the line is not in Ship node.
2033 --- There are some cases where the line will not be in ship node and this
2034 -- API is still called. Shipping is one among the cases. And also, if
2035 --- Customer is having customization for a workflow this will happen.
2036
2037 End if;
2038
2039 END IF;
2040 end if; --v_open_flag Bugfix 7214005
2041
2042 IF PG_DEBUG <> 0 THEN
2043 cto_wip_workflow_api_pk.cto_debug('wf_update_after_inv_unreserv', 'Exiting wf_update_after_inv_unreserv');
2044 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'Exiting wf_update_after_inv_unreserv', 2);
2045 END IF;
2046
2047 EXCEPTION
2048 when FND_API.G_EXC_ERROR then
2049
2050 IF PG_DEBUG <> 0 THEN
2051 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'exp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv::stmt number '||to_char(l_stmt_num), 1);
2052 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE','exp erro stmt no :'|| to_char(l_stmt_num));
2053
2054 END IF;
2055 x_return_status := FND_API.G_RET_STS_ERROR;
2056 cto_msg_pub.count_and_get(
2057 p_msg_count => x_msg_count,
2058 p_msg_data => x_msg_data
2059 );
2060
2061 when FND_API.G_EXC_UNEXPECTED_ERROR then
2062
2063 IF PG_DEBUG <> 0 THEN
2064 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv::stmt number '||to_char(l_stmt_num));
2065 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE','Unexp err stmt no:'|| to_char(l_stmt_num));
2066
2067 END IF;
2068 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2069 cto_msg_pub.count_and_get(
2070 p_msg_count => x_msg_count,
2071 p_msg_data => x_msg_data
2072 );
2073
2074 when others then
2075
2076 IF PG_DEBUG <> 0 THEN
2077 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || 'unexp error in CTO_WORKFLOW_API_PK.wf_update_after_inv_unreserv::stmt number '||to_char(l_stmt_num), 1);
2078 oe_debug_pub.add('wf_update_after_inv_unreserv: ' || sqlerrm, 1);
2079 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE','Unexp errr stmt no :'|| to_char(l_stmt_num));
2080 cto_wip_workflow_api_pk.cto_debug('WF_UPDATE_AFTER_INV_UNRESERVE',sqlerrm);
2081
2082 END IF;
2083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2084 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2085 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2086 END IF;
2087 cto_msg_pub.count_and_get(
2088 p_msg_count => x_msg_count,
2089 p_msg_data => x_msg_data
2090 );
2091
2092
2093 END wf_update_after_inv_unreserv;
2094
2095
2096
2097
2098 /**************************************************************************
2099
2100 Procedure: configuration_item_created
2101 Parameters: p_application_id (standard signature format)
2102 p_entity_short_name
2103 p_validation_entity_short_name
2104 p_validation_tmplt_short_name
2105 p_record_set_short_name
2106 p_scope
2107 x_result
2108 Description: This API with standard signature format is used to check is
2109 a configured item is created. This condition is applied to
2110 an option line.
2111
2112 *****************************************************************************/
2113
2114
2115
2116 PROCEDURE configuration_item_created (
2117 p_application_id IN NUMBER,
2118 p_entity_short_name IN VARCHAR2,
2119 p_validation_entity_short_name IN VARCHAR2,
2120 p_validation_tmplt_short_name IN VARCHAR2,
2121 p_record_set_short_name IN VARCHAR2,
2122 p_scope IN VARCHAR2,
2123 x_result OUT NOCOPY NUMBER )
2124 IS
2125 v_header_id number;
2126 v_model_id number;
2127 v_count number;
2128 v_activity_status_code varchar2(8);
2129 l_stmt_num number;
2130
2131 e_invalid_line_id exception;
2132
2133 BEGIN
2134
2135 IF PG_DEBUG <> 0 THEN
2136 oe_debug_pub.add('configuration_item_created: ' || 'Entering configuration_item_created', 4);
2137 END IF;
2138 --
2139 -- find the top_model_line_id when given an option line id
2140 --
2141 l_stmt_num := 10;
2142
2143 v_model_id := oe_line_security.g_record.ato_line_id; /* refer to a global record */
2144 v_header_id := oe_line_security.g_record.header_id;
2145
2146 --
2147 -- if not an ATO model line, condition is false, return 0
2148 --
2149 if (v_model_id is not NULL) AND (v_model_id <> fnd_api.g_miss_num) then
2150
2151 -- check if the config item is created
2152 -- adding join to header_id for performance
2153
2154 v_count := 0;
2155 l_stmt_num := 20;
2156 select count(*) into v_count
2157 from oe_order_lines_all
2158 where header_id = v_header_id
2159 and ato_line_id = v_model_id
2160 and item_type_code = 'CONFIG';
2161
2162
2163 if v_count <> 0 then
2164 x_result := 1; /* the condition is true */
2165 else
2166 x_result := 0; /* the condition is false */
2167 end if;
2168 end if;
2169
2170 IF PG_DEBUG <> 0 THEN
2171 oe_debug_pub.add('configuration_item_created: ' || 'Exiting configuration_item_created', 4);
2172 END IF;
2173
2174 EXCEPTION
2175
2176 when no_data_found then
2177 IF PG_DEBUG <> 0 THEN
2178 oe_debug_pub.add('configuration_item_created: ' || 'CTO_WORKFLOW_API_PK.configuration_item_created::stmt number '||
2179 to_char(l_stmt_num)||'top model line does not exist, constraint condition is false', 1);
2180 END IF;
2181 x_result := 0;
2182
2183 when OTHERS then
2184 IF PG_DEBUG <> 0 THEN
2185 oe_debug_pub.add('configuration_item_created: ' || 'CTO_WORKFLOW_API_PK.configuration_item_created::stmt number '||
2186 to_char(l_stmt_num)||'constraint condition is false', 1);
2187 oe_debug_pub.add('configuration_item_created: ' || sqlerrm, 1);
2188 END IF;
2189 x_result := 0;
2190
2191
2192 END configuration_item_created;
2193
2194
2195 /**************************************************************************
2196
2197 Procedure: configuration_created
2198 Parameters: p_application_id (standard signature format)
2199 p_entity_short_name
2200 p_validation_entity_short_name
2201 p_validation_tmplt_short_name
2202 p_record_set_short_name
2203 p_scope
2204 x_result
2205 Description: This API with standard signature format is called from
2206 the security constraints to validate whether a change is
2207 allowed on an order line.
2208 This API gets called for every item type. It returns
2209 x_result = 0 if the item is not part of an ATO
2210 model or if it is part of an ATO model that does not
2211 have a config item has not created yet.
2212 Otherwise, it returns x_result = 1.
2213
2214 *****************************************************************************/
2215 PROCEDURE configuration_created (
2216 p_application_id IN NUMBER,
2217 p_entity_short_name IN VARCHAR2,
2218 p_validation_entity_short_name IN VARCHAR2,
2219 p_validation_tmplt_short_name IN VARCHAR2,
2220 p_record_set_short_name IN VARCHAR2,
2221 p_scope IN VARCHAR2,
2222 x_result OUT NOCOPY NUMBER )
2223 IS
2224 l_header_id NUMBER;
2225 l_ato_line_id NUMBER;
2226 l_config_exists NUMBER;
2227 l_stmt_num NUMBER;
2228
2229
2230 BEGIN
2231 IF PG_DEBUG <> 0 THEN
2232 oe_debug_pub.add('configuration_created: ' || 'Entering configuration_created', 5);
2233 END IF;
2234 -- Check if the line is not an ATO model, option, option class, or
2235 -- config line
2236
2237 l_stmt_num := 5;
2238 select ato_line_id, header_id
2239 into l_ato_line_id, l_header_id
2240 from oe_order_lines_all
2241 where line_id = oe_line_security.g_record.line_id
2242 and item_type_code <> 'STANDARD'
2243 and ato_line_id is not null;
2244
2245 -- check if the config item is created
2246 -- adding join to header_id for performance
2247 l_stmt_num := 10;
2248 select 1
2249 into l_config_exists
2250 from oe_order_lines_all
2251 where header_id = l_header_id
2252 and ato_line_id = l_ato_line_id
2253 and item_type_code = 'CONFIG';
2254
2255
2256 oe_debug_pub.add( 'ACTION CODE: ' || oe_line_security.g_record.split_action_code , 1 ) ;
2257 oe_debug_pub.add( 'OPERATION: ' || oe_line_security.g_record.operation , 1 ) ;
2258 oe_debug_pub.add( 'ATTRIBUTE 1: ' || oe_line_security.g_record.attribute1 , 1 ) ;
2259 oe_debug_pub.add( 'SPLIT FROM LINE ID: ' || oe_line_security.g_record.split_from_line_id , 1 ) ;
2260 oe_debug_pub.add( 'LINE ID: ' || oe_line_security.g_record.line_id , 1 ) ;
2261
2262
2263
2264 x_result := 1; /* the condition is true */
2265 IF PG_DEBUG <> 0 THEN
2266 oe_debug_pub.add('configuration_created: ' || 'Exiting configuration_created', 5);
2267 END IF;
2268
2269
2270
2271 -- x_result := 0;
2272 /* IF PG_DEBUG <> 0 THEN
2273 oe_debug_pub.add('configuration_created: ' || 'Exiting configuration_created'||
2274 'Sushant made it return 0 or False purposely', 5);
2275 END IF;*/
2276 EXCEPTION
2277
2278 when no_data_found then
2279 IF PG_DEBUG <> 0 THEN
2280 oe_debug_pub.add('configuration_created: ' || 'CTO_WORKFLOW_API_PK.configuration_item_created::stmt number '||
2281 to_char(l_stmt_num)||'top model line does not exist, constraint condition is false', 1);
2282 END IF;
2283 x_result := 0;
2284
2285 when OTHERS then
2286 IF PG_DEBUG <> 0 THEN
2287 oe_debug_pub.add('configuration_created: ' || 'CTO_WORKFLOW_API_PK.configuration_item_created::stmt number '||
2288 to_char(l_stmt_num)||'constraint condition is false', 1);
2289 oe_debug_pub.add('configuration_created: ' || sqlerrm, 1);
2290 END IF;
2291 x_result := 0;
2292
2293
2294 END configuration_created;
2295
2296 /**************************************************************************
2297
2298 Function: start_model_workflow
2299 Parameters: p_model_line_id
2300 Return: TRUE - if model workflow is started successfully;
2301 FALSE - if model workflow is not started.
2302 Description: This API is used to start the ATO model workflow.
2303 Specifically, it completes the Create Config Item Eligible
2304 block activity. It is called after a Match is performed
2305 from the Sales Order Pad form.
2306 *****************************************************************************/
2307
2308 function start_model_workflow(p_model_line_id IN NUMBER)
2309 return boolean
2310
2311 is
2312 l_active_activity varchar2(30);
2313 l_stmt_num number;
2314
2315 PROCESS_ERROR exception;
2316
2317 begin
2318 IF PG_DEBUG <> 0 THEN
2319 oe_debug_pub.add('configuration_created: ' || 'Entering start_model_workflow', 5);
2320 END IF;
2321
2322 l_stmt_num := 10;
2323 get_activity_status('OEOL',
2324 to_char(p_model_line_id),
2325 'MODEL',
2326 l_active_activity);
2327
2328 IF (l_active_activity = 'CREATE_CONFIG_ITEM_ELIGIBLE') THEN
2329 IF PG_DEBUG <> 0 THEN
2330 oe_debug_pub.add('configuration_created: ' || 'Workflow Status is: '||l_active_activity, 5);
2331 END IF;
2332 l_stmt_num := 20;
2333
2334 IF (CTO_WORKFLOW_API_PK.complete_activity(
2335 p_itemtype => 'OEOL',
2336 p_itemkey => p_model_line_id,
2337 p_activity_name => l_active_activity,
2338 p_result_code => 'COMPLETE') <> TRUE)
2339 THEN
2340 IF PG_DEBUG <> 0 THEN
2341 oe_debug_pub.add('configuration_created: ' || 'Failed in Complete activity.', 1);
2342 END IF;
2343 raise PROCESS_ERROR;
2344 END IF;
2345
2346 IF PG_DEBUG <> 0 THEN
2347 oe_debug_pub.add('configuration_created: ' || 'Success in Complete activity.', 5);
2348 END IF;
2349 ELSE
2350 IF PG_DEBUG <> 0 THEN
2351 oe_debug_pub.add ('configuration_created: ' || 'Workflow Status is not at Create Config Item Eligible.', 5);
2352 END IF;
2353
2354 END IF;
2355
2356 IF PG_DEBUG <> 0 THEN
2357 oe_debug_pub.add('configuration_created: ' || 'Exiting start_model_workflow', 5);
2358 END IF;
2359 return TRUE;
2360
2361 exception
2362
2363 when PROCESS_ERROR then
2364 IF PG_DEBUG <> 0 THEN
2365 oe_debug_pub.add('configuration_created: ' || 'exp error in CTO_WORKFLOW_API_PK.start_model_workflow::stmt number '||to_char(l_stmt_num), 1);
2366 END IF;
2367 return FALSE;
2368
2369 when OTHERS then
2370 IF PG_DEBUG <> 0 THEN
2371 oe_debug_pub.add('configuration_created: ' || 'unexp error in CTO_WORKFLOW_API_PK.start_model_workflow::stmt number '||to_char(l_stmt_num), 1);
2372 oe_debug_pub.add('configuration_created: ' || sqlerrm, 1);
2373 END IF;
2374 return FALSE;
2375
2376 end start_model_workflow;
2377
2378
2379 /**************************************************************************
2380
2381 Procedure: Update_Config_Line
2382 Parameters: p_application_id (standard signature format)
2383 p_entity_short_name
2384 p_validation_entity_short_name
2385 p_validation_tmplt_short_name
2386 p_record_set_short_name
2387 p_scope
2388 x_result
2389 Description: This API with standard signature format is called from
2390 the security constraints to validate whether a change is
2391 allowed on an order line.
2392 This API gets called for every item type. It returns
2393 x_result = 0 if the item is a config item and the line is
2394 being updated by a system action (like scheduling or
2395 cascading).
2396 Otherwise, it returns x_result = 1.
2397
2398 *****************************************************************************/
2399 PROCEDURE Update_Config_Line(
2400 p_application_id IN NUMBER,
2401 p_entity_short_name IN VARCHAR2,
2402 p_validation_entity_short_name IN VARCHAR2,
2403 p_validation_tmplt_short_name IN VARCHAR2,
2404 p_record_set_short_name IN VARCHAR2,
2405 p_scope IN VARCHAR2,
2406 x_result OUT NOCOPY NUMBER )
2407 IS
2408
2409 l_config_item NUMBER;
2410 l_stmt_num NUMBER;
2411
2412
2413 BEGIN
2414 IF PG_DEBUG <> 0 THEN
2415 oe_debug_pub.add('configuration_created: ' || 'Entering Update_Config_Line', 5);
2416 END IF;
2417
2418 l_stmt_num := 5;
2419
2420 select 1
2421 into l_config_item
2422 from oe_order_lines_all
2423 where line_id = oe_line_security.g_record.line_id
2424 and item_type_code = 'CONFIG';
2425
2426 IF PG_DEBUG <> 0 THEN
2427 oe_debug_pub.add('configuration_created: ' || 'This is a config item. Check if update is user or system', 5);
2428 END IF;
2429
2430 IF (oe_config_util.cascade_changes_flag = 'Y'
2431 OR oe_order_sch_util.oesch_perform_scheduling = 'N') THEN
2432
2433 IF PG_DEBUG <> 0 THEN
2434 oe_debug_pub.add('configuration_created: ' || 'Cascading or scheduling change, allow update', 5);
2435 END IF;
2436 x_result := 0;
2437 return;
2438 ELSE
2439 IF PG_DEBUG <> 0 THEN
2440 oe_debug_pub.add('configuration_created: ' || 'User change, update not allowed', 5);
2441 END IF;
2442 x_result := 1; /* the condition is true */
2443 END IF;
2444 IF PG_DEBUG <> 0 THEN
2445 oe_debug_pub.add('configuration_created: ' || 'Exiting Update_Config_Line', 5);
2446 END IF;
2447
2448 EXCEPTION
2449
2450 when no_data_found then
2451 IF PG_DEBUG <> 0 THEN
2452 oe_debug_pub.add('configuration_created: ' || 'CTO_WORKFLOW_API_PK.Update_Config_Line::stmt number '||to_char(l_stmt_num)||'not config item',1);
2453 END IF;
2454 x_result := 0;
2455
2456 when OTHERS then
2457 IF PG_DEBUG <> 0 THEN
2458 oe_debug_pub.add('configuration_created: ' || 'CTO_WORKFLOW_API_PK.Update_Config_Line::stmt number '||to_char(l_stmt_num)||'others',1);
2459
2460 oe_debug_pub.add('configuration_created: ' || sqlerrm);
2461 END IF;
2462 x_result := 0;
2463
2464 END Update_Config_Line;
2465
2466
2467 /**************************************************************************
2468
2469 Procedure: configuration_created_for_pto
2470 Parameters: p_application_id (standard signature format)
2471 p_entity_short_name
2472 p_validation_entity_short_name
2473 p_validation_tmplt_short_name
2474 p_record_set_short_name
2475 p_scope
2476 x_result
2477 Description: This API with standard signature format is called from
2478 the security constraints to validate whether a change is
2479 allowed on an order line.
2480 This API gets called for every item type. It returns
2481 x_result = 0 if the item is not a PTO item or if it is a
2482 PTO item but does not have a configuration item
2483 created under it.
2484 Otherwise, it returns x_result = 1.
2485
2486 *****************************************************************************/
2487 PROCEDURE Configuration_Created_For_Pto (
2488 p_application_id IN NUMBER,
2489 p_entity_short_name IN VARCHAR2,
2490 p_validation_entity_short_name IN VARCHAR2,
2491 p_validation_tmplt_short_name IN VARCHAR2,
2492 p_record_set_short_name IN VARCHAR2,
2493 p_scope IN VARCHAR2,
2494 x_result OUT NOCOPY NUMBER )
2495 IS
2496 l_pto_line NUMBER;
2497 l_current_model_line NUMBER;
2498 l_config_exists NUMBER;
2499 l_stmt_num NUMBER;
2500
2501 CURSOR c_config_exists IS
2502 select ato_line_id
2503 from oe_order_lines_all
2504 where top_model_line_id = oe_line_security.g_record.top_model_line_id
2505 and item_type_code = 'CONFIG';
2506
2507 BEGIN
2508 IF PG_DEBUG <> 0 THEN
2509 oe_debug_pub.add('configuration_created_for_pto: ' || 'Entering configuration_created_for_pto',5);
2510 END IF;
2511
2512 -- Check if the line is not an ATO model, option, option class, or
2513 -- config line
2514
2515 l_stmt_num := 10;
2516 IF oe_line_security.g_record.ato_line_id IS NOT NULL THEN
2517 IF PG_DEBUG <> 0 THEN
2518 oe_debug_pub.add('configuration_created_for_pto: ' || 'This is not a PTO item, constraint condition is false', 5);
2519 END IF;
2520 x_result := 0;
2521 return;
2522 END IF;
2523
2524 l_stmt_num := 20;
2525 IF PG_DEBUG <> 0 THEN
2526 oe_debug_pub.add('configuration_created_for_pto: ' || 'This is a PTO item', 5);
2527 END IF;
2528 l_pto_line := oe_line_security.g_record.line_id;
2529 IF PG_DEBUG <> 0 THEN
2530 oe_debug_pub.add('configuration_created_for_pto: ' || 'l_pto_line = '||to_char(l_pto_line), 5);
2531 END IF;
2532
2533 l_stmt_num := 30;
2534 select 1
2535 into l_config_exists
2536 from oe_order_lines_all
2537 where top_model_line_id = oe_line_security.g_record.top_model_line_id
2538 and item_type_code = 'CONFIG'
2539 and rownum = 1;
2540
2541 IF PG_DEBUG <> 0 THEN
2542 oe_debug_pub.add('configuration_created_for_pto: ' || 'Config item exists somewhere in this configuration', 5);
2543 END IF;
2544
2545 --
2546 -- For each config item, traverse the BOM starting from the ATO model
2547 -- till the top PTO model. If the current item is encountered in the
2548 -- patch traversed, constraint condition should be TRUE, else FALSE
2549 --
2550 FOR v_config_exists in c_config_exists LOOP
2551 l_stmt_num := 40;
2552 l_current_model_line := v_config_exists.ato_line_id;
2553 IF PG_DEBUG <> 0 THEN
2554 oe_debug_pub.add('configuration_created_for_pto: ' || 'ATO model being processed : '||to_char(l_current_model_line), 2);
2555 END IF;
2556
2557 WHILE TRUE LOOP
2558
2559 BEGIN
2560
2561 l_stmt_num := 50;
2562 select link_to_line_id
2563 into l_current_model_line
2564 from oe_order_lines_all
2565 where line_id = l_current_model_line;
2566
2567 IF l_current_model_line = l_pto_line THEN
2568 IF PG_DEBUG <> 0 THEN
2569 oe_debug_pub.add('configuration_created_for_pto: ' || 'Config item exists under this PTO, constraint condition is true', 5);
2570 END IF;
2571 x_result := 1;
2572 return;
2573 END IF;
2574
2575 EXCEPTION
2576 WHEN NO_DATA_FOUND THEN
2577 l_stmt_num := 60;
2578 IF PG_DEBUG <> 0 THEN
2579 oe_debug_pub.add('configuration_created_for_pto: ' || 'No data found::top model', 5);
2580 END IF;
2581 exit; /* break from loop */
2582
2583 END;
2584
2585 END LOOP;
2586 END LOOP; /*cursor loop*/
2587
2588 x_result := 0; /* the condition is false */
2589 IF PG_DEBUG <> 0 THEN
2590 oe_debug_pub.add('configuration_created_for_pto: ' || 'Exiting configuration_created_for_pto',5);
2591 END IF;
2592
2593 EXCEPTION
2594
2595 when no_data_found then
2596 IF PG_DEBUG <> 0 THEN
2597 oe_debug_pub.add('configuration_created_for_pto: ' || 'CTO_WORKFLOW_API_PK.configuration_created_for_pto::stmt number '||to_char(l_stmt_num),1);
2598 oe_debug_pub.add('configuration_created_for_pto: ' || 'config item does not exist, constraint condition is false', 1);
2599 END IF;
2600 x_result := 0;
2601
2602 when OTHERS then
2603 IF PG_DEBUG <> 0 THEN
2604 oe_debug_pub.add('configuration_created_for_pto: ' || 'CTO_WORKFLOW_API_PK.configuration_created_for_pto::stmt number '||to_char(l_stmt_num), 1);
2605 oe_debug_pub.add('configuration_created_for_pto: ' || 'constraint condition is false', 1);
2606 oe_debug_pub.add('configuration_created_for_pto: ' || sqlerrm, 1);
2607 END IF;
2608 x_result := 0;
2609
2610 END Configuration_Created_For_Pto;
2611
2612
2613 /**************************************************************************
2614
2615 Procedure: Top_Ato_Model
2616 Parameters: p_application_id (standard signature format)
2617 p_entity_short_name
2618 p_validation_entity_short_name
2619 p_validation_tmplt_short_name
2620 p_record_set_short_name
2621 p_scope
2622 x_result
2623 Description: This API with standard signature format is called from
2624 the security constraints to validate whether a change is
2625 allowed on an order line.
2626 This API gets called for every item type. It returns
2627 x_result = 1 if the item is a top level ATO Model.
2628 Otherwise, it returns x_result = 0.
2629
2630 *****************************************************************************/
2631 PROCEDURE Top_Ato_Model(
2632 p_application_id IN NUMBER,
2633 p_entity_short_name IN VARCHAR2,
2634 p_validation_entity_short_name IN VARCHAR2,
2635 p_validation_tmplt_short_name IN VARCHAR2,
2636 p_record_set_short_name IN VARCHAR2,
2637 p_scope IN VARCHAR2,
2638 x_result OUT NOCOPY NUMBER )
2639 IS
2640
2641 l_top_ato_model NUMBER;
2642 l_stmt_num NUMBER;
2643
2644
2645 BEGIN
2646 IF PG_DEBUG <> 0 THEN
2647 oe_debug_pub.add('Top_Ato_Model: ' || 'Entering top_ato_model', 5);
2648 END IF;
2649
2650 l_stmt_num := 5;
2651
2652 select 1
2653 into l_top_ato_model
2654 from oe_order_lines_all
2655 where line_id = oe_line_security.g_record.line_id
2656 and ato_line_id = line_id;
2657
2658 x_result := 1; /* the condition is true */
2659 IF PG_DEBUG <> 0 THEN
2660 oe_debug_pub.add('Top_Ato_Model: ' || 'Exiting top_ato_model', 5);
2661 END IF;
2662
2663 EXCEPTION
2664
2665 when no_data_found then
2666 IF PG_DEBUG <> 0 THEN
2667 oe_debug_pub.add('Top_Ato_Model: ' || 'CTO_WORKFLOW_API_PK.top_ato_model::stmt number '||to_char(l_stmt_num)||'not top ato model',1);
2668 END IF;
2669 x_result := 0;
2670
2671 when OTHERS then
2672 IF PG_DEBUG <> 0 THEN
2673 oe_debug_pub.add('Top_Ato_Model: ' || 'CTO_WORKFLOW_API_PK.top_ato_model::stmt number '||to_char(l_stmt_num)||'others',1);
2674
2675 oe_debug_pub.add('Top_Ato_Model: ' || sqlerrm);
2676 END IF;
2677 x_result := 0;
2678
2679 END Top_Ato_Model;
2680
2681
2682 /**************************************************************************
2683
2684 Procedure: Config_Line
2685 Parameters: p_application_id (standard signature format)
2686 p_entity_short_name
2687 p_validation_entity_short_name
2688 p_validation_tmplt_short_name
2689 p_record_set_short_name
2690 p_scope
2691 x_result
2692 Description: This API with standard signature format is called from
2693 the security constraints to validate whether a change is
2694 allowed on an order line.
2695 This API gets called for every item type. It returns
2696 x_result = 0 if the item is a config item and the line is
2697 being updated by a system action (like scheduling or
2698 cascading).
2699 Otherwise, it returns x_result = 1.
2700
2701 *****************************************************************************/
2702 PROCEDURE Config_Line(
2703 p_application_id IN NUMBER,
2704 p_entity_short_name IN VARCHAR2,
2705 p_validation_entity_short_name IN VARCHAR2,
2706 p_validation_tmplt_short_name IN VARCHAR2,
2707 p_record_set_short_name IN VARCHAR2,
2708 p_scope IN VARCHAR2,
2709 x_result OUT NOCOPY NUMBER )
2710 IS
2711
2712 l_config_item NUMBER;
2713 l_stmt_num NUMBER;
2714
2715
2716 BEGIN
2717 IF PG_DEBUG <> 0 THEN
2718 oe_debug_pub.add('Config_Line: ' || 'Entering Config_Line', 5);
2719 END IF;
2720
2721 l_stmt_num := 5;
2722
2723 select 1
2724 into l_config_item
2725 from oe_order_lines_all
2726 where line_id = oe_line_security.g_record.line_id
2727 and item_type_code = 'CONFIG';
2728
2729 IF PG_DEBUG <> 0 THEN
2730 oe_debug_pub.add('Config_Line: ' || 'This is a config item.', 5);
2731 END IF;
2732 x_result := 1; /* the condition is true */
2733 IF PG_DEBUG <> 0 THEN
2734 oe_debug_pub.add('Config_Line: ' || 'Exiting Config_Line', 5);
2735 END IF;
2736
2737 EXCEPTION
2738
2739 when no_data_found then
2740 IF PG_DEBUG <> 0 THEN
2741 oe_debug_pub.add('Config_Line: ' || 'CTO_WORKFLOW_API_PK.Config_Line::stmt number '||to_char(l_stmt_num)||'not config item',1);
2742 END IF;
2743 x_result := 0;
2744
2745 when OTHERS then
2746 IF PG_DEBUG <> 0 THEN
2747 oe_debug_pub.add('Config_Line: ' || 'CTO_WORKFLOW_API_PK.Config_Line::stmt number '||to_char(l_stmt_num)||'others',1);
2748 oe_debug_pub.add('Config_Line: ' || sqlerrm, 1);
2749 END IF;
2750 x_result := 0;
2751
2752 END Config_Line;
2753
2754
2755 /**************************************************************************
2756 Procedure: CHANGE_STATUS_BATCH
2757 Parameters: p_header_id --> is the header ID for the line
2758 p_line_id --> is the line ID for the line
2759 p_change_status --> New status of the line
2760 p_oe_org_id --> is the ORganization ID for the line
2761 x_return_status --> is the return status for the procedure.
2762 Description: This API updates the line status to the in parameter (p_change_status)
2763 based on the line_id provided(p_line_id).
2764 *****************************************************************************/
2765 PROCEDURE change_status_batch (
2766 p_header_id NUMBER,
2767 p_line_id NUMBER,
2768 p_change_status VARCHAR2,
2769 p_oe_org_id NUMBER,
2770 x_return_status OUT NOCOPY VARCHAR2) is
2771
2772 -- local parameters
2773 l_message VARCHAR2(100);
2774 l_stmt_num NUMBER;
2775 lFlowStatusCode oe_order_lines_all.flow_status_code%type; --bugfix 2825486
2776 l_current_mode varchar2(100);
2777 l_current_org Number;
2778 L_CHANGE_CONTEXT_BACK Varchar2(100);
2779
2780 BEGIN
2781
2782 l_stmt_num := 10;
2783 l_message := 'line status is '||p_change_status||', (change_status_batch) at stmt number'||to_char(l_stmt_num);
2784 IF PG_DEBUG <> 0 THEN
2785 oe_debug_pub.add('Change_Status_Batch: ' || l_message, 5);
2786 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2787
2788 END IF;
2789 -- bugfix 2825486: added select to get the current flow_status_code.
2790 l_stmt_num := 20;
2791 select nvl(flow_status_code,'N')
2792 into lFlowStatusCode
2793 from oe_order_lines_all
2794 where header_id = p_header_id
2795 and line_id = p_line_id;
2796
2797
2798 IF PG_DEBUG <> 0 THEN
2799 l_message := 'Current flow_status_code = '||lFlowStatusCode;
2800 oe_debug_pub.add('Change_Status_Batch: ' ||l_message,4);
2801 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2802 END IF;
2803
2804 -- bugfix 2825486: Added IF. Only when flow_status is different, we'll call OM api to update the
2805 -- flow_status_code.
2806
2807 l_stmt_num := 30;
2808 if lFlowStatusCode <> p_change_status then
2809
2810 -- bugfix 2825486: added dbg stmt
2811 IF PG_DEBUG <> 0 THEN
2812 l_message := 'calling OE_ORDER_WF_UTIL.update_flow_status_code to update flow_status to '||p_change_status;
2813 oe_debug_pub.add('Change_Status_Batch: ' ||l_message,5);
2814 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2815 END IF;
2816
2817 -- Fixed by Renga Kannan on 04/27/06
2818 -- Fixed for bug 5122923
2819 -- Setting the org context to order line org context before calling om API
2820 -- Please refer to the bug for more information.
2821 -- Start of bug fxi 5122923
2822 CTO_MSUTIL_PUb.Switch_to_oe_context(
2823 p_oe_org_id => p_oe_org_id,
2824 x_current_mode => l_current_mode,
2825 x_current_org => l_current_org,
2826 x_context_switch_flag => l_change_context_back);
2827
2828
2829 -- End of Bugfix 5122923
2830
2831 l_stmt_num := 40;
2832 OE_ORDER_WF_UTIL.update_flow_status_code(
2833 p_header_id => p_header_id,
2834 p_line_id => p_line_id,
2835 p_flow_status_code => p_change_status,
2836 x_return_status => x_return_status);
2837
2838 if x_return_status = FND_API.G_RET_STS_ERROR THEN
2839 x_return_status := FND_API.G_RET_STS_SUCCESS; -- 5151111
2840 IF PG_DEBUG <> 0 THEN
2841 l_message:= 'Error occurred in updating line status to '||p_change_status||' - Stmt_num'||to_char(l_stmt_num);
2842 oe_debug_pub.add('Change_Status_Batch: ' ||l_message,1);
2843 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2844
2845 --bug#4700053
2846 l_message:= 'Progressing ahead even though line status may be wrong';
2847 oe_debug_pub.add('Change_Status_Batch: ' ||l_message,1);
2848 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2849
2850 END IF;
2851 --raise FND_API.G_EXC_ERROR;
2852
2853 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2854 x_return_status := FND_API.G_RET_STS_SUCCESS; --5151111
2855 IF PG_DEBUG <> 0 THEN
2856 l_message:= 'UnExp Error occurred in updating line status to '||p_change_status||' - Stmt_num'||to_char(l_stmt_num);
2857 oe_debug_pub.add('Change_Status_Batch: ' ||l_message,1);
2858 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2859
2860 --bug#4700053
2861 l_message:= 'Progressing ahead even though line status may be wrong';
2862 oe_debug_pub.add('Change_Status_Batch: ' ||l_message,1);
2863 cto_wip_workflow_api_pk.cto_debug('change_status_batch', l_message);
2864 END IF;
2865 --raise FND_API.G_EXC_UNEXPECTED_ERROR;
2866
2867 end if;
2868 IF PG_DEBUG <> 0 Then
2869 oe_debug_pub.add('Complete_activity : l_change_context_back = '||l_change_context_back,5);
2870 End if;
2871 End if;
2872 -- Start of bug fxi 5122923
2873
2874 If l_change_context_back = 'Y' Then
2875 CTO_MSUTIL_PUB.Switch_context_back(p_old_mode => l_current_mode,
2876 p_old_org => l_current_org);
2877 End if;
2878
2879 l_message := 'after updating line status to '||p_change_status||', (change_status_batch) at stmt number'||to_char(l_stmt_num);
2880 IF PG_DEBUG <> 0 THEN
2881 oe_debug_pub.add('Change_Status_Batch: ' || l_message, 5);
2882 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', l_message);
2883
2884 END IF;
2885 EXCEPTION
2886
2887 WHEN FND_API.G_EXC_ERROR THEN
2888 IF PG_DEBUG <> 0 THEN
2889 oe_debug_pub.add('Change_Status_Batch: ' || 'exp error in CTO_WORKFLOW_API_PK.change_status_batch::'||l_stmt_num, 1);
2890 cto_wip_workflow_api_pk.cto_debug('CHANGE_STATUS_BATCH','exp erro change_status_batch :: '||l_stmt_num||sqlerrm);
2891
2892 END IF;
2893 x_return_status := FND_API.G_RET_STS_ERROR;
2894
2895 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2896 IF PG_DEBUG <> 0 THEN
2897 oe_debug_pub.add('Change_Status_Batch: ' || 'unexp error in CTO_WORKFLOW_API_PK.change_status_batch::'||l_stmt_num, 1);
2898 cto_wip_workflow_api_pk.cto_debug('CHANGE_STATUS_BATCH','Unexp err change_status_batch :: '||l_stmt_num||sqlerrm);
2899 End if;
2900
2901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2902
2903 WHEN OTHERS THEN
2904 IF PG_DEBUG <> 0 THEN
2905 oe_debug_pub.add('Change_Status_Batch: ' || 'unexp error (others) in CTO_WORKFLOW_API_PK.change_status_batch::'||l_stmt_num, 1);
2906 oe_debug_pub.add('Change_Status_Batch: ' || sqlerrm, 1);
2907 cto_wip_workflow_api_pk.cto_debug('CHANGE_STATUS_BATCH','Unexp err change_status_batch :: '||l_stmt_num||sqlerrm);
2908
2909 END IF;
2910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2911 END;
2912
2913
2914
2915 /**************************************************************************
2916 Procedure: auto_create_pur_req
2917 Parameters: p_itemtype -->internal name for item type
2918 p_itemkey -->sales order line id
2919 p_actid -->ID number of WF activity
2920 p_funcmode -->execution mode of WF act
2921 x_result -->result of activity
2922 Description: This Procedure will be called from the WorkFlow and will create
2923 records in the interface table for the eligible line in the
2924 Order management tables
2925 *****************************************************************************/
2926
2927 PROCEDURE auto_create_pur_req(
2928 p_itemtype IN VARCHAR2, /* internal name for item type */
2929 p_itemkey IN VARCHAR2, /* sales order line id */
2930 p_actid IN NUMBER, /* ID number of WF activity */
2931 p_funcmode IN VARCHAR2, /* execution mode of WF act */
2932 x_result OUT NOCOPY VARCHAR2 /* result of activity */
2933 ) IS
2934
2935 errbuf VARCHAR2(100);
2936 retcode VARCHAR2(100);
2937 p_sales_order NUMBER;
2938 p_organization_id VARCHAR2(100);
2939 p_offset_days NUMBER;
2940 x_return_status VARCHAR2(100);
2941 l_stmt_num NUMBER;
2942 p_new_order_quantity oe_order_lines_all.ordered_quantity%TYPE;
2943 v_x_error_msg_count NUMBER;
2944 v_x_hold_result_out VARCHAR2(1);
2945 v_x_hold_return_status VARCHAR2(1);
2946 v_x_error_msg VARCHAR2(150);
2947 so_line oe_order_lines_all%ROWTYPE;
2948 p_program_id NUMBER;
2949 l_res BOOLEAN;
2950 p_order_number VARCHAR2(100);
2951
2952 l_source_document_type_id NUMBER;
2953
2954 -- Bugfix 3077912: New variables
2955 l_need_by_date DATE;
2956 -- End bugfix 3077912
2957
2958 -- rkaza. ireq project.
2959 l_sourcing_rule_exists VARCHAR2(1);
2960 l_req_input_data CTO_AUTO_PROCURE_PK.req_interface_input_data;
2961 l_transit_lead_time NUMBER;
2962 l_exp_error_code NUMBER;
2963
2964
2965 l_rets NUMBER; --bugfix 4545070
2966
2967 BEGIN
2968
2969 savepoint before_process;
2970
2971 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2972 IF PG_DEBUG <> 0 THEN
2973 oe_debug_pub.add('auto_create_pur_req: ' || 'CTO WF Activity: auto_create_pur_req', 1);
2974 END IF;
2975
2976 if (p_funcmode = 'RUN') then
2977
2978 l_stmt_num := 1;
2979
2980 --
2981 -- check if the line has/is beeing processed by the Concurrent Request.
2982 --
2983 select concurrent_program_id
2984 into p_program_id
2985 from fnd_concurrent_programs a
2986 where concurrent_program_name = 'CTOACREQ'
2987 and application_id = 702; --BOM , bugfix 2885568 for
2988 --full table scan
2989
2990
2991 l_stmt_num := 10;
2992
2993 --
2994 -- Select all the records from the SO lines table for the line id passed in the procedure.
2995 -- this cursor will fetch only one record from the table as the parameter passed in is the primary key.
2996 --
2997 SELECT *
2998 INTO so_line
2999 FROM oe_order_lines_all
3000 WHERE line_id = to_number(p_itemkey);
3001
3002 SELECT order_number
3003 INTO p_order_number
3004 FROM oe_order_lines_all a, oe_order_headers_all b
3005 WHERE a.header_id = b.header_id
3006 AND a.line_id = to_number(p_itemkey);
3007
3008 -- get the line source document type ID
3009 l_source_document_type_id := cto_utility_pk.get_source_document_id ( pLineId => p_itemkey );
3010
3011 IF so_line.program_id = p_program_id THEN
3012
3013 -- log message that the line has been processed by the concurrent_program
3014 IF PG_DEBUG <> 0 THEN
3015 oe_debug_pub.add('auto_create_pur_req: ' || 'This line has already been selected to be processed by the concurrent request :'||to_char(so_line.request_id),1);
3016 END IF;
3017
3018 ELSE
3019
3020 l_stmt_num := 20;
3021 -- Removed check hold API call from here as we are going to check for
3022 -- hold in check_supply_type_wf workflow activity, which is just before this workflow
3023 -- node
3024 -- Removed as part of bug fix 5261330
3025
3026
3027 l_stmt_num := 21;
3028 -- check the quantity to be ordered.
3029 -- Fix for performance bug 4897231
3030 -- get_new_order_qty signature is changed
3031 -- to add a new P_item_id parameter
3032 -- Passing the new parameter
3033
3034
3035 begin
3036 p_new_order_quantity := CTO_AUTO_PROCURE_PK.get_new_order_qty (
3037 p_interface_source_line_id => so_line.line_id,
3038 p_order_qty => so_line.ordered_quantity,
3039 p_cancelled_qty => nvl(so_line.cancelled_quantity, 0),
3040 p_item_id => so_line.inventory_item_id);
3041
3042 IF nvl(p_new_order_quantity, 0) = 0 THEN
3043 IF PG_DEBUG <> 0 THEN
3044 oe_debug_pub.add('auto_create_pur_req: ' || 'ERROR GET_NEW_ORDER_QTY:: The new order quantity is zero', 1);
3045 END IF;
3046 raise FND_API.G_EXC_ERROR;
3047 END IF;
3048 end;
3049
3050 -- rkaza. 05/02/2005. ireq project
3051 -- call query sourcing org to get src type and distinguish internal
3052 -- external req cases.
3053 l_stmt_num := 22;
3054 CTO_UTILITY_PK.query_sourcing_org(
3055 p_inventory_item_id => so_line.inventory_item_id,
3056 p_organization_id => so_line.ship_from_org_id,
3057 p_sourcing_rule_exists => l_sourcing_rule_exists,
3058 p_sourcing_org => l_req_input_data.sourcing_org,
3059 p_source_type => l_req_input_data.source_type ,
3060 p_transit_lead_time => l_transit_lead_time,
3061 x_return_status => x_return_status,
3062 x_exp_error_code => l_exp_error_code );
3063
3064 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3065 IF PG_DEBUG <> 0 THEN
3066 oe_debug_pub.add('auto_create_pur_req: ' || 'success from query sourcing org', 5);
3067 oe_debug_pub.add('auto_create_pur_req: ' || 'source_type = ' || l_req_input_data.source_type, 5);
3068 oe_debug_pub.add('auto_create_pur_req: ' || 'sourcing_org = ' || l_req_input_data.sourcing_org, 5);
3069 END IF;
3070 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3071 IF PG_DEBUG <> 0 THEN
3072 oe_debug_pub.add('auto_create_pur_req: ' || 'unexpected error query sourcing org', 5);
3073 END IF;
3074 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3075 END IF;
3076
3077 l_stmt_num := 26;
3078
3079 cto_auto_procure_pk.get_need_by_date(
3080 p_source_type => l_req_input_data.source_type,
3081 p_item_id => so_line.inventory_item_id,
3082 p_org_id => so_line.ship_from_org_id,
3083 p_schedule_ship_date => so_line.schedule_ship_date,
3084 x_need_by_date => l_need_by_date,
3085 x_return_status => x_return_status);
3086
3087 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3088 IF PG_DEBUG <> 0 THEN
3089 oe_debug_pub.add('auto_create_pur_req: ' || 'success from get_need_by_date' || ' l_need_by_date=' || l_need_by_date, 5);
3090 END IF;
3091 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3092 IF PG_DEBUG <> 0 THEN
3093 oe_debug_pub.add('auto_create_pur_req: ' || 'unexpected error in get_need_by_date', 5);
3094 END IF;
3095 raise fnd_api.g_exc_unexpected_error;
3096 END IF;
3097
3098 l_stmt_num := 25;
3099 --pthese attributes are for orders taken in OPM organizations
3100 --and for buy and internal req orders in other type of organizations
3101 l_req_input_data.secondary_qty := so_line.ORDERED_QUANTITY2;
3102 l_req_input_data.secondary_uom := so_line.ORDERED_QUANTITY_UOM2;
3103 l_req_input_data.grade := so_line.PREFERRED_GRADE;
3104
3105 l_stmt_num := 30;
3106
3107 -- Insert record into the interface table.
3108 Begin
3109
3110 -- Call the populate_req_interface.
3111 -- rkaza. Pass l_req_input_data also.
3112
3113 CTO_AUTO_PROCURE_PK.populate_req_interface ( 'CTO', -- pass CTO as a parameter , chaged for ML SUPPLY feature by kkonada
3114 p_destination_org_id => so_line.ship_from_org_id,
3115 p_org_id => so_line.org_id,
3116 p_created_by => so_line.created_by,
3117 p_need_by_date => l_need_by_date, -- 3077912 so_line.schedule_ship_date
3118 p_order_quantity => p_new_order_quantity,
3119 p_order_uom => so_line.order_quantity_uom,
3120 p_item_id => so_line.inventory_item_id,
3121 p_item_revision => so_line.item_revision,
3122 p_interface_source_line_id => so_line.line_id,
3123 p_unit_price => null, -- so_line.unit_selling_price,
3124 p_batch_id => 1000, --l_batch_id
3125 p_order_number => p_order_number,
3126 p_req_interface_input_data => l_req_input_data,
3127 x_return_status => x_return_status );
3128
3129 -- Log the error based on the x_return_status.
3130 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3131 RAISE FND_API.G_EXC_ERROR;
3132
3133 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3135
3136 ELSE
3137 IF PG_DEBUG <> 0 THEN
3138 oe_debug_pub.add('auto_create_pur_req: ' || 'Insert successful.',5);
3139 END IF;
3140
3141 l_stmt_num := 40; --bugfix4545070
3142 l_rets := display_wf_status( p_order_line_id=>so_line.line_id);
3143
3144
3145 IF l_rets = 0 THEN
3146 IF PG_DEBUG <> 0 THEN
3147 oe_debug_pub.add('auto_create_pur_req: ' || 'UNExp Error occurred in call to display_wf_status at - Stmt_num'||to_char(l_stmt_num),1);
3148 END IF;
3149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3150 ELSE
3151 IF PG_DEBUG <> 0 THEN
3152 oe_debug_pub.add('auto_create_pur_req: ' || 'Order updated to REQ_REQUESTED.', 5);
3153 END IF;
3154 END IF;
3155
3156 END IF; -- end of returnstatus check
3157
3158 End; -- Insert record into the interface table.
3159
3160 END IF; -- check if the record is already being processed by the concurrent request.
3161
3162 end if; -- end of p_funcmode check
3163
3164 OE_STANDARD_WF.Save_Messages;
3165 OE_STANDARD_WF.Clear_Msg_Context;
3166
3167 EXCEPTION
3168 WHEN FND_API.G_EXC_ERROR THEN
3169 IF PG_DEBUG <> 0 THEN
3170 oe_debug_pub.add('auto_create_pur_req: ' || 'AUTO_CREATE_PUR_REQ::exp error::'||to_char(l_stmt_num),1);
3171 END IF;
3172 x_return_status := FND_API.G_RET_STS_ERROR;
3173 wf_core.context('CTO_WORKFLOW', 'auto_create_pur_req, stmt_num :'||to_char(l_stmt_num),p_itemtype, p_itemkey, to_char(p_actid),p_funcmode, 1);
3174 x_result := 'CTO_INCOMPLETE';
3175 rollback to before_process;
3176 return;
3177
3178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3179 IF PG_DEBUG <> 0 THEN
3180 oe_debug_pub.add('auto_create_pur_req: ' || 'AUTO_CREATE_PUR_REQ::unexp error::'||to_char(l_stmt_num),1);
3181 END IF;
3182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3183 wf_core.context('CTO_WORKFLOW', 'auto_create_pur_req, stmt_num :'||to_char(l_stmt_num),
3184 p_itemtype, p_itemkey, to_char(p_actid),
3185 p_funcmode);
3186 raise;
3187
3188 WHEN OTHERS THEN
3189 IF PG_DEBUG <> 0 THEN
3190 oe_debug_pub.add('auto_create_pur_req: ' || 'AUTO_CREATE_PUR_REQ::other error::'||to_char(l_stmt_num)||'::'||sqlerrm,1);
3191 END IF;
3192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3193 wf_core.context('CTO_WORKFLOW', 'auto_create_pur_req, stmt_num :'||to_char(l_stmt_num),
3194 p_itemtype, p_itemkey, to_char(p_actid),
3195 p_funcmode);
3196 raise;
3197 END auto_create_pur_req;
3198
3199
3200
3201
3202 PROCEDURE chk_Buy_Ato_Item(
3203 p_application_id IN NUMBER,
3204 p_entity_short_name IN VARCHAR2,
3205 p_validation_entity_short_name IN VARCHAR2,
3206 p_validation_tmplt_short_name IN VARCHAR2,
3207 p_record_set_short_name IN VARCHAR2,
3208 p_scope IN VARCHAR2,
3209 x_result OUT NOCOPY NUMBER )
3210 IS
3211
3212 l_stmt_num NUMBER;
3213 l_inv_item_id Number;
3214 l_ship_org Number;
3215 v_activity_status_code Varchar2(100);
3216 v_sourcing_rule_exists Varchar2(100);
3217 v_source_type Number;
3218 v_sourcing_org Number;
3219 v_transit_lead_time Number;
3220 v_exp_error_code Number;
3221 x_return_status Varchar2(1);
3222 l_item_type_code Varchar2(100);
3223 l_ato_line_id Number;
3224
3225 BEGIN
3226 IF PG_DEBUG <> 0 THEN
3227 oe_debug_pub.add('chk_Buy_Ato_Item: ' || 'Entering Chk_buy_ato_item', 5);
3228 END IF;
3229
3230 l_stmt_num := 5;
3231
3232 SELECT item_type_code,
3233 ato_line_id,
3234 inventory_item_id,
3235 ship_from_org_id
3236 INTO l_item_type_code,
3237 l_ato_line_id,
3238 l_inv_item_id,
3239 l_ship_org
3240 FROM OE_ORDER_LINES_ALL
3241 WHERE line_id = oe_line_security.g_record.line_id;
3242
3243
3244 IF l_item_type_code in ('STANDARD','OPTION') AND
3245 l_ato_line_id = oe_line_security.g_record.line_id THEN
3246
3247 CTO_UTILITY_PK.query_sourcing_org(
3248 p_inventory_item_id => l_inv_item_id,
3249 p_organization_id => l_ship_org,
3250 p_sourcing_rule_exists => v_sourcing_rule_exists,
3251 p_source_type => v_source_type,
3252 p_sourcing_org => v_sourcing_org,
3253 p_transit_lead_time => v_transit_lead_time,
3254 x_exp_error_code => v_exp_error_code,
3255 x_return_status => x_return_status
3256 );
3257
3258 IF nvl(v_source_type,2) = 3 THEN
3259 IF PG_DEBUG <> 0 THEN
3260 oe_debug_pub.add('chk_Buy_Ato_Item: ' || 'It is an buy ato item...',5);
3261 END IF;
3262 x_result := 1; /* This is correct condition */
3263 ELSE
3264 IF PG_DEBUG <> 0 THEN
3265 oe_debug_pub.add('chk_Buy_Ato_Item: ' || 'It is a make ato item',5);
3266 END IF;
3267 x_result := 0;
3268 END IF;
3269
3270 ELSE
3271 IF PG_DEBUG <> 0 THEN
3272 oe_debug_pub.add('chk_Buy_Ato_Item: ' || 'Not an ato item...',5);
3273 END IF;
3274 x_result := 0;
3275 END IF;
3276
3277 EXCEPTION
3278
3279 when no_data_found then
3280 IF PG_DEBUG <> 0 THEN
3281 oe_debug_pub.add('chk_Buy_Ato_Item: ' || 'CTO_WORKFLOW_API_PK.Config_Line::stmt number '||to_char(l_stmt_num)||'not config item',1);
3282 END IF;
3283 x_result := 0;
3284
3285 when OTHERS then
3286 IF PG_DEBUG <> 0 THEN
3287 oe_debug_pub.add('chk_Buy_Ato_Item: ' || 'CTO_WORKFLOW_API_PK.Config_Line::stmt number '||to_char(l_stmt_num)||'others',1);
3288
3289 oe_debug_pub.add('chk_Buy_Ato_Item: ' || sqlerrm);
3290 END IF;
3291 x_result := 0;
3292
3293 END chk_Buy_Ato_Item;
3294
3295
3296
3297
3298
3299 PROCEDURE Reservation_Exists(
3300 p_application_id IN NUMBER,
3301 p_entity_short_name IN VARCHAR2,
3302 p_validation_entity_short_name IN VARCHAR2,
3303 p_validation_tmplt_short_name IN VARCHAR2,
3304 p_record_set_short_name IN VARCHAR2,
3305 p_scope IN VARCHAR2,
3306 x_result OUT NOCOPY NUMBER
3307 )
3308 is
3309
3310 v_aps_version number ;
3311 l_ato_line_id number ;
3312 l_header_id number ;
3313 l_config_line_id number ;
3314
3315
3316
3317 v_rsv_rec CTO_UTILITY_PK.resv_tbl_rec_type;
3318 v_resv_code varchar2(200) ;
3319 v_sum_rxv_qty number ;
3320 v_return_status varchar2(200) ;
3321 v_msg_count number ;
3322 v_msg_data varchar2(200) ;
3323 l_stmt_num number ;
3324
3325
3326 l_config_orgs mtl_system_items.config_orgs%type ;
3327 l_cfg_item_id mtl_system_items.inventory_item_id%type ;
3328 x_primary_uom_code varchar2(3);
3329
3330
3331 begin
3332
3333 l_stmt_num := 1;
3334
3335 v_aps_version := msc_atp_global.get_aps_version ;
3336
3337 if( v_aps_version <> 10) then
3338 oe_debug_pub.add('reservation_exists: ' || 'APS version::'|| v_aps_version , 2);
3339
3340 x_result := 1 ;
3341
3342 return ;
3343
3344 end if;
3345
3346
3347 IF PG_DEBUG <> 0 THEN
3348 oe_debug_pub.add( 'ACTION CODE: ' || oe_line_security.g_record.split_action_code , 1 ) ;
3349 oe_debug_pub.add( 'OPERATION: ' || oe_line_security.g_record.operation , 1 ) ;
3350 oe_debug_pub.add( 'ATTRIBUTE 1: ' || oe_line_security.g_record.attribute1 , 1 ) ;
3351 oe_debug_pub.add( 'SPLIT FROM LINE ID: ' || oe_line_security.g_record.split_from_line_id , 1 ) ;
3352 oe_debug_pub.add( ' LINE ID: ' || oe_line_security.g_record.line_id , 1 ) ;
3353 END IF;
3354 l_stmt_num := 5;
3355 select ato_line_id, header_id , inventory_item_id
3356 into l_ato_line_id, l_header_id , l_cfg_item_id
3357 from oe_order_lines_all
3358 where line_id = oe_line_security.g_record.line_id
3359 and item_type_code <> 'STANDARD'
3360 and ato_line_id is not null;
3361
3362
3363 -- check if the config item is created
3364 -- adding join to header_id for performance
3365 l_stmt_num := 10;
3366
3367 select line_id
3368 into l_config_line_id
3369 from oe_order_lines_all
3370 where header_id = l_header_id
3371 and ato_line_id = l_ato_line_id
3372 and item_type_code = 'CONFIG';
3373
3374
3375
3376
3377 if( oe_line_security.g_record.split_action_code = 'SPLIT' ) then /* bug 3424802 */
3378 IF PG_DEBUG <> 0 THEN
3379 oe_debug_pub.add( 'no need to check for CIB attribute for split scenario ' , 1) ;
3380 END IF;
3381 else
3382 IF PG_DEBUG <> 0 THEN
3383 oe_debug_pub.add( 'need to check for CIB attribute as update warehouse is enabled only for CIB = 3 ' , 1) ;
3384 END IF;
3385 -- R12 fp bug 4380768
3386 -- Modified by Renga on 06/06/05
3387 -- Added nvl clause for config_orgs
3388 -- null value for config_orgs should be treated as 1
3389
3390 --r12 bugfix 4666778, changed query to look at models CIB attribute
3391
3392 --5051814 changed the query which looks at models CIB attribute
3393 --this query catches the CIB contsraint when OM calls for model line
3394 --instead of waiting for a call for config line
3395 select nvl(msi.config_orgs,1)
3396 into l_config_orgs
3397 from oe_order_lines_all oel,
3398 mtl_system_items msi
3399 where oel.line_id = l_ato_line_id
3400 and msi.inventory_item_id = oel.inventory_item_id
3401 and msi.organization_id = oel.ship_from_org_id;
3402
3403
3404 if( l_config_orgs <> '3') then
3405
3406 IF PG_DEBUG <> 0 THEN
3407 oe_debug_pub.add('reservation_exists: ' || 'l_config_orgs <> 3 ::'|| l_config_orgs , 2);
3408 oe_debug_pub.add('reservation_exists: ' || 'l_config_orgs <> Based on Model ::'|| l_config_orgs , 2);
3409 oe_debug_pub.add('reservation_exists: ' || 'l_cfg_item_id ::'|| to_char(l_cfg_item_id) , 2);
3410 END IF;
3411 x_result := 1 ;
3412
3413 return ;
3414
3415 end if ;
3416
3417
3418 end if ; /* check CIB attribute for warehouse update */
3419
3420
3421 l_stmt_num := 30;
3422
3423 CTO_UTILITY_PK.Get_Resv_Qty
3424 (
3425 p_order_line_id => l_config_line_id ,
3426 x_rsv_rec => v_rsv_rec,
3427 x_primary_uom_code => x_primary_uom_code,
3428 x_sum_rsv_qty => v_sum_rxv_qty ,
3429 x_return_status => v_return_status,
3430 x_msg_count => v_msg_count ,
3431 x_msg_data => v_msg_data
3432 ) ;
3433
3434 l_stmt_num := 40;
3435
3436 if( v_rsv_rec.count > 0 ) then
3437
3438
3439 x_result := 1 ;
3440
3441 else
3442
3443 x_result := 0 ;
3444
3445 end if;
3446
3447
3448 exception
3449 when no_data_found then
3450 x_result := 0 ;
3451 IF PG_DEBUG <> 0 THEN
3452 oe_debug_pub.add('CTO_WORKFLOW_API_PK: ' || 'reservation exists::'|| ' NO DATA FOUND EXCEPTION ' || l_stmt_num , 2);
3453 END IF;
3454 when others then
3455
3456 x_result := 0 ;
3457 IF PG_DEBUG <> 0 THEN
3458 oe_debug_pub.add('CTO_WORKFLOW_API_PK: ' || 'reservation exists::'|| ' OTHERS EXCEPTION ' || l_stmt_num , 2);
3459 END IF;
3460
3461 end reservation_exists ;
3462
3463
3464 --- Added for Cross docking project
3465 /*******************************************************************************************
3466 -- API name : get_status_tokens
3467 -- Type : Private
3468 -- Pre-reqs : CTOUTILS.pls, CTOUTILB.pls
3469 -- Function : Given config/ato item Order line id, item id, ship from org and ordered qty
3470 it returns Flow status code in two tokens. The calling module can combine these
3471 two tokens with '-' to get the corresponding flow status code.
3472 -- Parameters:
3473
3474 -- IN : p_order_line_id Expects the config/ato item order line Required
3475 -- p_config_item_id Expects the config/ato item id Required
3476 -- p_ship_from_org_id Expects the ship from org of the order line Required
3477 -- p_ordered_quantity Expects the order lines ordered quantity Required
3478
3479 -- OUT : x_token1 The first part of flow status code.
3480 x_token2 The second part of flow status code.
3481 x_return_status Standard error message status
3482 x_msg_count Std. error message count in the message stack
3483 x_msg_data Std. error message data in the message stack
3484 -- Version :
3485 --
3486 --
3487 ******************************************************************************************/
3488 Procedure get_status_tokens(
3489 p_order_line_id Number,
3490 p_config_item_id Number,
3491 p_ship_from_org_id Number,
3492 p_ordered_quantity Number,
3493 x_token1 OUT NOCOPY Varchar2,
3494 x_token2 OUT NOCOPY varchar2,
3495 x_return_status OUT NOCOPY varchar2,
3496 x_msg_data OUT NOCOPY Varchar2,
3497 x_msg_count OUT NOCOPY Number) is
3498
3499 l_stmt_num Number;
3500 l_rsv_rec CTO_UTILITY_PK.resv_tbl_rec_type;
3501 x_primary_uom_code MTL_SYSTEM_ITEMS.primary_uom_code%type;
3502 l_sum_rsv_qty NUMBER;
3503 l_can_create_supply varchar2(1);
3504 --l_source_type VARCHAR2(5); -- bug 4552271. rkaza. 08/15/05.
3505 l_source_type number; --Bug 6470516
3506 l_sourcing_org number;
3507 l_return_msg varchar2(100);
3508 L_INV_SRC_ID NUMBER;
3509 L_WIP_SRC_ID NUMBER;
3510 L_PO_SRC_ID NUMBER;
3511 L_REQ_SRC_ID NUMBER;
3512 l_int_req_src_id number;
3513 l_ext_req_src_id number;
3514 l_asn_src_id number;
3515 l_rcv_src_id number;
3516 l_flm_src_id number;
3517 l_int_req_if_src_id number;
3518 l_ext_req_if_src_id number;
3519 k number;
3520 --spec changed to include these new variables for OPM
3521 l_hetro Number;
3522
3523 l_onhand_fg Varchar2(1);
3524 l_make_flag varchar2(1);
3525 l_buy_fg varchar2(1);
3526 l_xfer_fg varchar2(1);
3527 l_return_status Varchar2(1);
3528 l_msg_data Varchar2(1000);
3529 l_msg_count Number;
3530
3531
3532
3533 Begin
3534
3535 --call API to get_reservation_code
3536 l_stmt_num := 10;
3537 CTO_UTILITY_PK.Get_Resv_Qty
3538 (
3539 p_order_line_id => p_order_line_id,
3540 x_rsv_rec => l_rsv_rec,
3541 x_primary_uom_code => x_primary_uom_code,
3542 x_sum_rsv_qty => l_sum_rsv_qty,
3543 x_return_status => l_return_status,
3544 x_msg_count => l_msg_count,
3545 x_msg_data => l_msg_data
3546 );
3547
3548 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
3549 IF PG_DEBUG <> 0 THEN
3550 oe_debug_pub.add('get_status_tokens: '||'SUCCESS after Get_Resv_Qty',1);
3551 oe_debug_pub.add('get_status_tokens: '||'Sum of resv qty=>'||l_sum_rsv_qty,1);
3552 oe_debug_pub.add('get_status_tokens: '||'resv record count=>'||l_rsv_rec.count,1);
3553
3554 END IF;
3555 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
3556 IF PG_DEBUG <> 0 THEN
3557 oe_debug_pub.add('get_status_tokens: '||'status after after Get_Resv_Qty_and_Code=>'
3558 || FND_API.G_RET_STS_ERROR,1);
3559 END IF;
3560 RAISE fnd_api.g_exc_error;
3561 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3562
3563 IF PG_DEBUG <> 0 THEN
3564 oe_debug_pub.add('status after after Get_Resv_Qty_and_Code=>'
3565 || FND_API.G_RET_STS_UNEXP_ERROR,1 );
3566
3567 END IF;
3568 RAISE fnd_api.g_exc_unexpected_error;
3569 END IF;
3570 -- If there is no supply tied to this line
3571 -- Then the status should be as follows.
3572 l_stmt_num :=20;
3573 If l_rsv_rec.count = 0 then
3574
3575 l_stmt_num :=30;
3576 CTO_UTILITY_PK.check_cto_can_create_supply
3577 (
3578 P_config_item_id => p_config_item_id,
3579 P_org_id => p_ship_from_org_id,
3580 x_can_create_supply => l_can_create_supply,
3581 p_source_type => l_source_type,
3582 x_return_status => l_return_status,
3583 X_msg_count => l_msg_count,
3584 X_msg_data => l_msg_data,
3585 x_sourcing_org => l_sourcing_org,
3586 x_message => l_return_msg
3587 );
3588 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3589 RAISE FND_API.G_EXC_ERROR;
3590 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3591 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3592 END IF;
3593
3594 IF l_can_create_supply = 'N' THEN
3595 x_token1 := 'AWAITING';
3596 x_token2 := 'SUPPLY';
3597 ELSE
3598 x_token1 := 'SUPPLY';
3599 x_token2 := 'ELIGIBLE';
3600 End if;
3601 Else
3602 l_stmt_num := 40;
3603 l_inv_src_id := inv_reservation_global.g_source_type_inv;
3604 l_wip_src_id := inv_reservation_global.g_source_type_wip;
3605 l_po_src_id := inv_reservation_global.g_source_type_po;
3606 l_req_src_id := inv_reservation_global.g_source_type_req;
3607 --cross_dock
3608 l_ext_req_src_id := inv_reservation_global.g_source_type_req;--bugfix 4652873
3609 l_int_req_src_id := inv_reservation_global.g_source_type_internal_req;
3610 l_asn_src_id := inv_reservation_global.g_source_type_asn;
3611 l_rcv_src_id := inv_reservation_global.g_source_type_rcv;
3612 l_flm_src_id := cto_utility_pk.g_source_type_flow;
3613 l_ext_req_if_src_id := cto_utility_pk.g_source_type_ext_req_if;
3614 l_int_req_if_src_id := cto_utility_pk.g_source_type_int_req_if;
3615 --end cross_dock
3616
3617 IF PG_DEBUG <> 0 THEN
3618 oe_debug_pub.add('get_status_tokens: '||'In IF BLOCK when l_rsv_rec count is > 0 ',5);
3619 END IF;
3620
3621 IF PG_DEBUG = 5 THEN
3622 oe_debug_pub.add('get_status_tokens:'||'printing rsv source type and qty in loop', 5);
3623 oe_debug_pub.add('get_status_tokens:'||'RSV_SRC_TYP '||'Quantity', 5);
3624
3625 l_stmt_num := 50;
3626 k := l_rsv_rec.first;
3627
3628 WHILE (k is not null)
3629 LOOP
3630 oe_debug_pub.add('get_status_tokens:'||l_rsv_rec(k).supply_source_type_id
3631 ||' => '|| l_rsv_rec(k).primary_reservation_quantity, 5);
3632
3633 k := l_rsv_rec.next(k);
3634 END LOOP;
3635 END IF; /* PG_DEBUG = 5 */
3636
3637
3638 --bugfix4637281
3639 --changing IF..ELSIF into multiple IF..ENDIF's
3640 l_stmt_num :=60;
3641 If l_rsv_rec.exists(l_wip_src_id) or l_rsv_rec.exists(l_flm_src_id) Then
3642 l_make_flag := 'Y';
3643 End if;
3644 If l_rsv_rec.exists(l_ext_req_src_id) or
3645 l_rsv_rec.exists(l_po_src_id) or
3646 l_rsv_rec.exists(l_asn_src_id) Then
3647 l_buy_fg := 'Y';
3648 End if;
3649 If l_rsv_rec.exists(l_int_req_src_id) then
3650 l_xfer_fg := 'Y';
3651 End if;
3652 If l_rsv_rec.exists(l_inv_src_id) then
3653 l_onhand_fg := 'Y';
3654 end if; /* l_rsv_rec.exists(l_wip_src_id) or l_rsv_rec.exists(l_flm_src_id) */
3655
3656 l_stmt_num :=70;
3657 select decode(l_make_flag,'Y',1,0)+decode(l_buy_fg,'Y',1,0)+decode(l_xfer_fg,'Y',1,0)
3658 into l_hetro
3659 from dual;
3660
3661 l_stmt_num:=80;
3662 If l_onhand_fg = 'Y' and
3663 l_rsv_rec(l_inv_src_id).primary_reservation_quantity >= p_ordered_quantity Then
3664 x_token1 := 'AWAITING';
3665 elsif l_hetro > 1 or (l_make_flag = 'Y' and l_rsv_rec.exists(l_rcv_src_id)) then
3666 x_token1 := 'SUPPLY';
3667 elsif l_make_flag = 'Y' then
3668 x_token1 := 'PRODUCTION';
3669 elsif l_buy_fg = 'Y' then
3670 if l_rsv_rec.exists(l_rcv_src_id) then
3671 x_token1 :='IN_RECEIVING';
3672 elsif l_rsv_rec.exists(l_asn_src_id) then
3673 x_token1 := 'ASN';
3674 elsif l_rsv_rec.exists(l_po_src_id) then
3675 x_token1 := 'PO';
3676 elsif l_rsv_rec.exists(l_ext_req_src_id) then
3677 x_token1 := 'EXTERNAL_REQ';
3678 end if; /* l_buy_fg = 'Y' */
3679 elsif l_xfer_fg = 'Y' then
3680 if l_rsv_rec.exists(l_rcv_src_id) then
3681 x_token1 := 'IN_RECEIVING';
3682 elsif l_rsv_rec.exists(l_int_req_src_id) then
3683 x_token1 := 'INTERNAL_REQ';
3684 end if;
3685 --bugfix 4739807,when rsv is only bcos of receiving
3686 elsif l_rsv_rec.exists(l_rcv_src_id)then
3687 x_token1 :='IN_RECEIVING';
3688 End if; /* l_onhand_fg = 'Y' */
3689
3690 l_stmt_num := 90;
3691 If x_token1 is null and l_onhand_fg = 'Y' and
3692 l_rsv_rec(l_inv_src_id).primary_reservation_quantity < p_ordered_quantity then
3693 x_token1 := 'SUPPLY';
3694 End if; /* l_token1 is null and l_onhand_fg = 'Y' */
3695
3696 l_stmt_num := 100;
3697 If x_token1 is null then
3698 if l_rsv_rec.exists(l_ext_req_if_src_id) then
3699 x_token1 := 'EXTERNAL_REQ';
3700
3701 IF PG_DEBUG <> 0 THEN
3702 oe_debug_pub.add('get_status_tokens: '||'x_token1=> '||x_token1,5);
3703 END IF;
3704
3705 elsif l_rsv_rec.exists(l_int_req_if_src_id) then
3706 x_token1 := 'INTERNAL_REQ';
3707 end if;
3708 end if; /* l_token1 is null */
3709
3710
3711 l_stmt_num := 110;
3712 If l_onhand_fg = 'Y' Then
3713 If l_rsv_rec(l_inv_src_id).primary_reservation_quantity >= p_ordered_quantity then
3714 x_token2 := 'SHIPPING';
3715 else
3716 x_token2 := 'PARTIAL';
3717 end if;
3718 elsif l_make_flag = 'Y' or l_buy_fg = 'Y' or l_xfer_fg = 'Y' then
3719 If x_token1 <> 'IN_RECEIVING' then
3720 x_token2 := 'OPEN';
3721 End if;
3722 elsif l_rsv_rec.exists(l_ext_req_if_src_id) or l_rsv_rec.exists(l_int_req_if_src_id) then
3723 x_token2 := 'REQUESTED';
3724 end if; /* l_onhand_fg = 'Y' */
3725 End if; /* l_rsv_rec.count = 0 */
3726
3727 EXCEPTION--4752854,fixed as part of code review
3728
3729 WHEN fnd_api.g_exc_error THEN
3730 IF PG_DEBUG <> 0 THEN
3731 oe_debug_pub.add('get_status_tokens: ' || 'Exception in stmt num: '
3732 || to_char(l_stmt_num), 1);
3733 END IF;
3734 x_return_status := FND_API.G_RET_STS_ERROR;
3735 -- Get message count and data
3736 cto_msg_pub.count_and_get
3737 ( p_msg_count => x_msg_count
3738 , p_msg_data => x_msg_data
3739 );
3740 WHEN fnd_api.g_exc_unexpected_error THEN
3741 IF PG_DEBUG <> 0 THEN
3742 oe_debug_pub.add('get_status_tokens: '|| ' Unexpected Exception in stmt num: '
3743 || to_char(l_stmt_num), 1);
3744 END IF;
3745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3746 -- Get message count and data
3747 cto_msg_pub.count_and_get
3748 ( p_msg_count => x_msg_count
3749 , p_msg_data => x_msg_data
3750 );
3751 WHEN OTHERS then
3752
3753
3754 IF PG_DEBUG <> 0 THEN
3755 oe_debug_pub.add('get_status_tokens: '||'errmsg'||sqlerrm,1);
3756 oe_debug_pub.add('get_status_tokens: ' || 'Others Exception in stmt num: '
3757 || to_char(l_stmt_num), 1);
3758 END IF;
3759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3760 -- Get message count and data
3761 cto_msg_pub.count_and_get
3762 ( p_msg_count => x_msg_count
3763 , p_msg_data => x_msg_data
3764 );
3765
3766 End get_status_tokens;
3767
3768
3769
3770 END CTO_WORKFLOW_API_PK;
[Home] [Help]