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