[Home] [Help]
PACKAGE BODY: APPS.CTO_WORKFLOW
Source
1 package body CTO_WORKFLOW as
2 /* $Header: CTOWKFLB.pls 120.13.12010000.2 2008/08/26 19:15:17 ntungare ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 | |
10 | FILE NAME : CTOWKFLB.pls |
11 | DESCRIPTION : |
12 | create_config_item_wf( ) |
13 | check_reservation_status_wf( ) |
14 | create_bom_and_routing_wf( ) |
15 | calculate_cost_rollup_wf( ) |
16 | calculate_cost_rollup_wf_ml( ) |
17 | set_parameter_lead_time_wf_ml( ) |
18 | set_parameter_lead_time_wf_ml() |
19 | check_supply_type_wf( ) |
20 | set_parameter_work_order_wf( ) |
21 | submit_conc_prog_wf( ) |
22 | submit_and_continue_wf( ) |
23 | validate_line ( ) |
24 | validate_config_line( ) |
25 | config_line_exists ( ) |
26 | reservation_exists( ) |
27 | check_inv_rsv_exists( ) |
28 | flow_sch_exists( ) |
29 | rsv_before_booking_wf( ) |
30 | |
31 | |
32 | HISTORY : 03/26/99 Initial Version |
33 | 12/14/00 Renga Kannan, Create_config_item_wf is |
34 | fixed to handle link_item also. This is part |
35 | bug fix for bug# 1381938 |
36 | |
37 | 06/26/01 Sushant fixed bug 1853597. query should retrieve|
38 | only one row |
39 | |
40 | 07/12/01 Kiran Konada ,fix for bug#1861812 |
41 | logic added to rsv_before_booking_wf so that |
42 | it gets executed only for ato item |
43 | NOTE: oexwford.wft also changed for this fix |
44 | This fix has actually been provided in |
45 | branched code 115.57.1155.3 |
46 | |
47 | 07/18/01 Shashi Bhaskaran, bugfix 1799874 |
48 | Modified code to fix demand_source_type for |
49 | internal orders. |
50 | |
51 | 08/16/2001 Kiran Konada, fix for bug#1874380 |
52 | to support ATO item under a PTO. |
53 | item_type_code for an ato item under PTO |
54 | is 'OPTION' and top_model_line_id will NOT be|
55 | null, UNLIKE an ato item order, where |
56 | item_type_code = 'Standard' and |
57 | top_model_lined_id is null |
58 | This fix has actually been provided in |
59 | branched code 115.57.1155.5 |
60 | |
61 | |
62 | 08/29/01 Modified check_supply_type_wf |
63 | For procuring configuration |
64 | |
65 | Sep 26, 01 Shashi Bhaskaran Fixed bug 2017099 |
66 | Check with ordered_quantity(OQ) instead of OQ-CQ |
67 | where CQ=cancelled_quantity. When a line is |
68 | is canceled, OQ gets reflected. |
69 | |
70 | 11/16/2001 bugfix#2111718 |
71 | added the check for ato item in proceudre |
72 | validate_config_line( ). Ato item with flow |
73 | routing was erroing out in this procedure |
74 | |
75 | 03/08/2002 bugfix#2234858 |
76 | added new functionality to support DROP SHIP |
77 | |
78 | 03/22/2002 bugfix#2234858 |
79 | removed dependency on schedule status code |
80 | and visible demand flag for external source type |
81 | items to support DROP SHIP |
82 | |
83 | 03/22/2002 bugfix#2313475 ( 2286525 in br ) |
84 | Replace top_model_line_id with ato_line_id |
85 | 04/18/2002 bugfix 2320488 |
86 | replace org_id in message with org_name |
87 | |
88 | 05/03/2002 bugfix#2342412 |
89 | error message needs to be displayed in case of |
90 | reservation error.
91 |
92 | 06/04/02 bugfix2327972--Kiran Konada
93 | added a new function node which calls procedure
94 | chk_rsv_after_afas_wf
95 | This nodes checks if any type of reservation
96 | exists. Node has been added in warning path after
97 | autocreate fas node
98 |
99 | 10/31/02 Sushant Sawant
100 | Added Enhanced costing functionality for matched items
101 |
102 | 11/03/2003 Kiran Konada
103 | added a call to Update_Flow_Status_Code in check_supply_
104 | creation_wf
105 |
106 | 11/04/2003 Kiran Konada
107 | added a call to display_wf_status instead of
108 | Update_Flow_Status_Code in check_supply_
109 | creation_wf
110 |
111 | 11/14/2003 Kiran Konada
112 | removed procedu set_parameter_lead_time_wf
113 | bcos of bug#3202825
114 |
115 |
116 |
117 | 12/11/03 Sushant Sawant
118 | removed update_flow_status_code calls in create_config_item_wf
119 | and added display_wf_status
120 |
121 |
122 | 01/19/04 Sushant Sawant
123 | Fixed Bug 3380730, 3380874 to provide proper status code on config line
124 | and corrected use of cto_workflow_api_pk.display_wf_status
125 |
126 |
127 | 01/23/04 Sushant Sawant
128 | Fixed Bug 3388135 to provide message for match in case of dropship models.
129 |
130 | 02/23/2004 Sushant Sawant fixed Bug 3419221
131 | New LINE_FLOW_STATUS code 'SUPPLY_ELIGIBLE' was introduced.
132 | Config Lines with Internal and External source types will be assigned this status.
133 | when the config line reached check supply creation workflow activity.
134 |
135 |
136 | 03/01/2004 Kiran Konada
137 | Bugfix 2318060
138 | 'N' value to BUILD_IN_WIP_FLAG is caught as expected error
139 | when workflow moves through set_parameter_work_order node
140 |
141 |
142 | 04/06/2004 KKONADA removed fullstop after BOM , bugfix#3554874
143 |
144 | 05/10/2004 Sushant Sawant
145 | fixed bug 3548069 in procedure validate_line.
146 | model lines with schedule ship date null should not
147 | be picked for config creation.
148 |
149 | 04/04/2005 Renga Kannan
150 | Fixed bug 4197665 in procedure check_supply_type_wf
151 | The to_char function had a wrong parameter in it.
152 | It was erroring out only in 10G instance. Fixed it.
153 |
154 |
155 | 06/01/2005 Renga Kannan
156 |
157 | Added nocopy Hint to all out parameters.
158 |
159 |
160 |
161 | 06/16/2005 Kiran Konada :
162 | chnaged for OPM project
163 | change comment : OPM
164 | check_supply_type:
165 | check for p_source_type in (1,66) is replaced by
166 | l_can_create_supply = N
167 | check_cto_can_create_supply new parameters l_sourcing_org
168 | and l_message
169 |
170 | check_supply_creation api
171 | check_cto_can_create_supply new parameters l_sourcing_org
172 | and l_message
173 |
174 |
175 | 07/05/2005 Renga Kannan
176 | Changed for MOAC project
177 | Code change for ONT RESERVATION TIME FENCE
178 |
179 |
180 |
181 | 18-AUG-2005 Kiran Konada
182 | bugfix#4556596
183 | when Check_supply_type_wf node detects that
184 | multiple sources are present workflow moves
185 | shipline. But there is no call to display_wf_status
186 | so line status remains at "supply eligible"
187 | Fix is to call display_wf_status
188 |
189 |
190 |
191 =============================================================================*/
192 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_WORKFLOW';
193
194
195 /*
196 procedure send_oid_notification ;
197
198
199 procedure get_planner_code( p_inventory_item_id in number
200 , p_organization_id in number
201 , x_planner_code out Nocopy fnd_user.user_name%type ) ;
202
203 procedure handle_expected_error( p_error_type in number
204 , p_inventory_item_id in number
205 , p_organization_id in number
206 , p_line_id in number
207 , p_sales_order_num in number
208 , p_top_model_name in number
209 , p_top_model_line_num in varchar2
210 , p_top_config_name in number default null
211 , p_top_config_line_num in varchar2 default null
212 , p_msg_count in number
213 , p_planner_code in varchar2
214 , p_request_id in varchar2
215 , p_process in varchar2 ) ;
216
217 */
218
219
220 /*============================================================================
221 Procedure: create_config_item_wf
222 Description: This API gets called from create configuration activity
223 in "Create Configuration, Line Manual" process.
224
225 Parameters:
226 ============================================================================*/
227 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
228
229 PROCEDURE create_config_item_wf(
230 p_itemtype in VARCHAR2, /* internal name for the item type */
231 p_itemkey in VARCHAR2, /* sales order line id */
232 p_actid in NUMBER, /* ID number of WF activity */
233 p_funcmode in VARCHAR2, /* execution mode of WF activity */
234 x_result out NoCopy VARCHAR2 /* result of activity */
235 )
236 IS
237
238 l_stmt_num number := 0;
239 l_x_error_msg_name varchar2(30);
240 l_x_error_msg varchar2(500); --bugfix 2776026: increased the var size
241 l_x_error_msg_count number;
242 l_x_table_name varchar2(150);
243 l_x_hold_result_out varchar2(1);
244 l_x_hold_return_status varchar2(1);
245 l_status integer;
246 l_activity_name varchar2(30);
247 l_model_line_id number;
248 l_model_id number;
249 l_mfg_org_id number;
250 l_x_bill_seq_id number;
251 l_return_status VARCHAR2(1);
252 l_flow_status_code VARCHAR2(30);
253 l_perform_flow_calc number := 1;
254
255 lTopAtoLineId number; -- 2313475 lTopModelLineId
256 l_header_id number;
257 l_config_line_id number;
258 l_config_id number;
259 l_xReturnStatus varchar2(1);
260 l_xMsgCount number;
261 l_xMsgData varchar2(2000);
262 l_program_id number;
263
264 l_tree_id integer;
265 l_perform_match varchar2(2) ;
266
267 l_x_qoh number;
268 l_x_rqoh number;
269 l_x_qs number;
270 l_x_qr number;
271 l_x_att number;
272
273 l_reservation_uom_code varchar2(3);
274 l_primary_uom_code varchar2(3);
275 l_quantity_to_reserve number;
276 l_schedule_ship_date DATE;
277
278 l_automatic_reservation varchar2(2) ;
279 l_diff_days number ;
280 l_reservation_time_fence number;
281
282 x_available_qty number ;
283 x_quantity_reserved number ;
284 x_msg_count number ;
285 x_msg_data varchar2(200) ;
286 x_error_message varchar2(200) ;
287 x_message_name varchar2(200) ;
288 x_reserve_status varchar2(200) ;
289 x_return_status varchar2(200) ;
290
291 l_active_activity varchar2(30);
292 lMatchProfile varchar2(10);
293
294 v_source_type_code oe_order_lines_all.source_type_code%type ;
295 x_oper_unit_list cto_auto_procure_pk.oper_unit_tbl;
296 l_batch_no Number;
297
298
299 l_config_item_id number ;
300 lPerformPPRollup varchar2(10) ;
301 lPerformCSTRollup varchar2(10) ;
302 lPerformLTCalc varchar2(10) ;
303 lPerformFWCalc varchar2(10) ;
304 lNotifyUsers varchar2(10) ;
305
306 l_requestId number ;
307
308 v_order_number number ;
309 v_top_model_name varchar2(100);
310 v_top_model_line_num varchar2(100);
311 v_top_config_name varchar2(100);
312 v_top_config_line_num varchar2(100);
313
314 l_msg_data Varchar2(2000);
315
316
317
318 v_top_model_id number ;
319 v_ship_from_org_id number ;
320 v_planner_code fnd_user.user_name%type ;
321 v_recipient varchar2(200) ;
322
323 lFlowStatusCode varchar2(200) ;
324 return_value NUMBER;
325
326 l_token CTO_MSG_PUB.token_tbl;
327 l_config_item_name varchar2(1000) ;
328 BEGIN
329 IF PG_DEBUG <> 0 THEN
330 oe_debug_pub.add('create_config_item_wf: ' || 'Function Mode: ' || p_funcmode, 1);
331 oe_debug_pub.add('create_config_item_wf: ' || 'Item Key : ' || p_itemkey , 1);
332 oe_debug_pub.add('create_config_item_wf: ' || 'Item Type : ' || p_itemtype , 1);
333 oe_debug_pub.add('create_config_item_wf: ' || 'activity id : ' || p_actid , 1);
334
335 oe_debug_pub.add('create_config_item_wf: ' || 'CTO Activity: Create Config Item', 1);
336
337 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
338 END IF;
339
340
341 OE_STANDARD_WF.Set_Msg_Context(p_actid);
342
343 savepoint before_item_creation;
344
345 if (p_funcmode = 'RUN') then
346
347 /*-----------------------------------------------------+
348 Do the following before creating config item:
349 1. Check for existence of config line
350 2. Validate model line
351 3. Check Holds
352 +-----------------------------------------------------*/
353
354 l_stmt_num := 100;
355
356 IF (config_line_exists(to_number(p_itemkey))) THEN
357 IF PG_DEBUG <> 0 THEN
358 oe_debug_pub.add('create_config_item_wf: ' || 'Configuration Line exists for this model.', 1);
359 END IF;
360 l_stmt_num := 102;
361
362 -- The exception block for the following select statement is added by Renga Kannan
363 -- on 12/14/00. This part of the bug fix # 1381938
364 -- When the start Model workflow is called and it is having configuration item
365 -- in Oe_order_lines_all, it can be becasue of two things. One is the Auto create config
366 -- batch program is running and the other is due to link_item
367 -- In the case of Auto create config process we will get the program_id value in bcol table.
368 -- But in the case of link item the data may not be there in bcol table at all. So in the
369 -- case of link_item in the when_no_data_found exception we will set the l_program_id value to 0.
370 -- This will move the model work flow as well as the config line workflow.
371
372 begin
373 select program_id
374 into l_program_id
375 from bom_cto_order_lines
376 where line_id = to_number(p_itemkey);
377 exception
378 when no_data_found then
379 IF PG_DEBUG <> 0 THEN
380 oe_debug_pub.add('create_config_item_wf: ' || 'The configuration item is created by the link_item process...',1);
381 END IF;
382 -- Set the l_program_id to zero so that the config workflow is moved further...
383 l_program_id := 0;
384 end;
385
386 -- End of change by Renga kannan 12/14/00
387
388 --
389 -- Return if this line is being processed by AutoCreate Config.
390 --
391 if (l_program_id = 31881) then
392 x_result := 'COMPLETE';
393 return;
394 end if;
395
396 l_stmt_num := 103;
397 select line_id
398 into l_config_line_id
399 from oe_order_lines_all
400 where ato_line_id = to_number(p_itemkey)
401 and item_type_code = 'CONFIG';
402
403 /* ATO Line Workflow will not have individual activities to be bypassed
404 l_stmt_num := 104;
405 wf_engine.CompleteActivityInternalName(
406 'OEOL',
407 l_config_line_id,
408 'CREATE_CONFIG_BOM_ELIGIBLE',
409 'CREATED');
410 */
411
412
413 x_result := 'COMPLETE';
414 return;
415 END IF; /* end if check config item */
416
417 l_stmt_num := 105;
418 IF (validate_line(to_number(p_itemkey)) <> TRUE) THEN
419 cto_msg_pub.cto_message('BOM','CTO_LINE_STATUS_NOT_ELIGIBLE');
420 raise FND_API.G_EXC_ERROR;
421 END IF;
422
423 l_stmt_num := 110;
424
425 IF PG_DEBUG <> 0 THEN
426 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
427
428 oe_debug_pub.add('create_config_item_wf: ' || 'Calling Check Holds.',1);
429 END IF;
430 /* bugfix 4051282: check for activity hold and generic hold */
431 OE_HOLDS_PUB.Check_Holds(p_api_version => 1.0,
432 p_line_id => to_number(p_itemkey),
433 p_wf_item => 'OEOL',
434 p_wf_activity => 'CREATE_CONFIG',
435 x_result_out => l_x_hold_result_out,
436 x_return_status => l_x_hold_return_status,
437 x_msg_count => l_x_error_msg_count,
438 x_msg_data => l_x_error_msg);
439
440 IF (l_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
441 IF PG_DEBUG <> 0 THEN
442 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Check Holds with expected error.' ,1);
443 END IF;
444 raise FND_API.G_EXC_ERROR;
445
446 ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
447 IF PG_DEBUG <> 0 THEN
448 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Check Holds with unexpected error.' ,1);
449 END IF;
450 raise FND_API.G_EXC_UNEXPECTED_ERROR;
451
452 ELSE
453 IF PG_DEBUG <> 0 THEN
454 oe_debug_pub.add('create_config_item_wf: ' || 'Success in Check Holds. ' || l_x_hold_return_status,1);
455 END IF;
456 IF (l_x_hold_result_out = FND_API.G_TRUE) THEN
457 IF PG_DEBUG <> 0 THEN
458 oe_debug_pub.add('create_config_item_wf: ' || 'Order Line ID ' || p_itemkey ||
459 'is on HOLD. ' ||l_x_hold_result_out);
460 END IF;
461 cto_msg_pub.cto_message('BOM', 'CTO_ORDER_LINE_ON_HOLD');
462 x_result := 'COMPLETE:ON_HOLD';
463 return;
464
465 END IF; -- end to check hold = TRUE
466 END IF; -- end to check hold return status
467
468
469
470 oe_debug_pub.add('create_config_item_wf: ' || 'Getting Profile Values ' , 1);
471
472
473 lPerformPPRollup := nvl( FND_PROFILE.Value('CTO_PERFORM_PURCHASE_PRICE_ROLLUP'), 1 ) ;
474 lPerformCSTRollup := nvl( FND_PROFILE.Value('CTO_PERFORM_COST_ROLLUP') , 1 ) ;
475 --Bugfix 6737389
476 --lPerformFWCalc := nvl( FND_PROFILE.Value('CTO_PERFORM_FLOW_CALC') , 1 );
477 lPerformFWCalc := nvl( FND_PROFILE.Value('CTO_PERFORM_FLOW_CALC') , 2 );
478 lPerformLTCalc := nvl( FND_PROFILE.Value('BOM:ATO_PERFORM_LEADTIME_CALC' ) , 1 ) ;
479 lNotifyUsers := nvl( FND_PROFILE.Value('CTO_NOTIFY_USER_FOR_ERRORS'), 1) ;
480
481 oe_debug_pub.add('create_config_item_wf: ' || 'Done Getting Profile Values ' , 1);
482
483 IF PG_DEBUG <> 0 THEN
484 oe_debug_pub.add('create_config_item_wf: ' || 'Profile Perform Purchase Price Rollup ' || lPerformPPRollup , 1);
485 oe_debug_pub.add('create_config_item_wf: ' || 'Profile Perform Cost Rollup ' || lPerformCSTRollup , 1);
486 oe_debug_pub.add('create_config_item_wf: ' || 'Profile Perform Lead Time Calculations ' || lPerformLTCalc , 1);
487 oe_debug_pub.add('create_config_item_wf: ' || 'Profile Perform Flow Calculations ' || lPerformFWCalc , 1);
488 oe_debug_pub.add('create_config_item_wf: ' || 'Profile Notify User for Errors ' || lNotifyUsers , 1);
489 END IF;
490
491
492 if( lPerformFWCalc = 1 ) then
493 l_perform_flow_calc := 1;
494 oe_debug_pub.add('create_config_item_wf: ' || 'Flow Calc is 1 ' , 1);
495 else
496 --Begin Bugfix 6737389
497 if( lPerformFWCalc = 2 ) then
498 l_perform_flow_calc := 2;
499 oe_debug_pub.add('create_config_item_wf: ' || 'Flow Calc is 2 ' , 1);
500 else
501 l_perform_flow_calc := 3;
502 oe_debug_pub.add('create_config_item_wf: ' || 'Flow Calc is 3 ' , 1);
503 end if;
504 --End Bugfix 6737389
505 end if ;
506
507
508 l_stmt_num := 115;
509
510 --
511 -- get the top ato_line_id for this model line
512 -- Bugfix 2313475 replace top_model_line_id with ato_line_id
513 --
514 /* BUG#2234858 Added new functionality for drop ship
515 ** need to retrieve source_type_code to find whether
516 ** line is dropshipped
517 */
518 select ato_line_id
519 , order_quantity_uom , ordered_quantity -- added by sushant for reservation
520 , schedule_ship_date -- added by sushant for reservation
521 , source_type_code -- added by sushant for drop ship
522 into lTopAtoLineId
523 , l_reservation_uom_code , l_quantity_to_reserve
524 , l_schedule_ship_date
525 , v_source_type_code
526 from oe_order_lines_all
527 where line_id = to_number(p_itemkey);
528
529 IF PG_DEBUG <> 0 THEN
530 oe_debug_pub.add('create_config_item_wf: ' || 'lTopATOLineId::'||to_char(lTopAtoLineId));
531 END IF;
532
533
534
535
536
537
538 /* Additional Code for Error Processing */
539 /* COLLECT DATA for ERROR PROCESSING */
540
541
542
543 oe_debug_pub.add('create_config_item_wf: ' || 'Going to Collect Data for Error Processing ' , 1 );
544
545
546
547 select oeh.order_number , msi.segment1, oel.line_number || '.' || oel.shipment_number
548 , msi.inventory_item_id , msi.organization_id
549 into v_order_number, v_top_model_name, v_top_model_line_num
550 , v_top_model_id, v_ship_from_org_id
551 from oe_order_headers_all oeh , oe_order_lines_all oel , mtl_system_items msi
552 where oeh.header_id = oel.header_id
553 and oel.line_id = lTopAtoLineId
554 and oel.inventory_item_id = msi.inventory_item_id
555 and oel.ship_from_org_id = msi.organization_id ;
556
557
558
559
560
561 oe_debug_pub.add('create_config_item_wf: ' || 'Going to get planner code ' , 1 );
562
563 CTO_UTILITY_PK.get_planner_code( v_top_model_id, v_ship_from_org_id , v_planner_code ) ;
564
565
566 oe_debug_pub.add('create_config_item_wf: ' || 'planner code is ' || v_planner_code , 1 );
567
568
569
570
571
572
573
574
575
576 /*--------------------------------------------------------+
577 Call API to create config item. The API will create config
578 items for all ATO models. The top model config item will be
579 linked in oe_order_lines_all AFTER BOM creation.
580 Depending on the "Match" profile, it will create new items
581 or match to existing items.
582 +---------------------------------------------------------*/
583
584 l_stmt_num := 120;
585
586 IF PG_DEBUG <> 0 THEN
587 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
588
589 oe_debug_pub.add('create_config_item_wf: ' || 'Calling Create and Link Item.', 1);
590 END IF;
591
592 l_status := CTO_ITEM_PK.create_and_link_item
593 (pTopAtoLineId => to_number(p_itemkey),
594 xReturnStatus => l_xReturnStatus,
595 xMsgCount => l_xMsgCount,
596 xMsgData => l_xMsgData );
597
598
599 oe_debug_pub.add('create_config_item_wf: ' || 'done create and link Item .');
600
601 if (l_status = 0 and l_xReturnStatus = FND_API.G_RET_STS_ERROR ) then
602 IF PG_DEBUG <> 0 THEN
603 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Create and Link Item with expected error.');
604 END IF;
605
606
607
608 /* EXPECTED ERROR PROCESSING */
609
610 if( lNotifyUsers = 1 ) then
611 IF PG_DEBUG <> 0 THEN
612 oe_debug_pub.add('create_config_item_wf: ' || 'Going to Call CTO_UTILITY_PK.handle_expected_error ..',3);
613 END IF;
614
615 CTO_UTILITY_PK.handle_expected_error( p_error_type => CTO_UTILITY_PK.EXP_ERROR_AND_ITEM_NOT_CREATED
616 , p_inventory_item_id => v_top_model_id
617 , p_organization_id => v_ship_from_org_id
618 , p_line_id => lTopAtoLineId
619 , p_sales_order_num => v_order_number
620 , p_top_model_name => v_top_model_name
621 , p_top_model_line_num => v_top_model_line_num
622 , p_msg_count => l_xMsgCount
623 , p_planner_code => v_planner_code
624 , p_request_id => null
625 , p_process => 'NOTIFY_OEE_INC' ) ;
626
627
628
629 end if; /* lNotifyUsers */
630
631
632
633
634 raise FND_API.G_EXC_ERROR;
635
636 elsif (l_status = 0 and l_xReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR ) then
637 IF PG_DEBUG <> 0 THEN
638 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Create and Link Item with unexpected error.');
639 END IF;
640 raise FND_API.G_EXC_UNEXPECTED_ERROR;
641
642 else
643 IF PG_DEBUG <> 0 THEN
644 oe_debug_pub.add('create_config_item_wf: ' || 'Success in Create And Link Item.', 1);
645 END IF;
646 end if;
647
648
649 oe_debug_pub.add('create_config_item_wf: ' || 'going for bom and routing .');
650
651
652 /*----------------------------------------------------------+
653 Create BOM and Routing
654 +-----------------------------------------------------------*/
655
656 -- rkaza. bug 4524248. bom structure import enhancements. 11/05/05.
657 l_stmt_num := 125;
658
659 IF PG_DEBUG <> 0 THEN
660 oe_debug_pub.add('create_config_item_wf: About to generate bom batch ID', 5);
661 end if;
662
663 cto_msutil_pub.set_bom_batch_id(x_return_status => l_xReturnStatus);
664 if l_xReturnStatus <> fnd_api.G_RET_STS_SUCCESS then
665 IF PG_DEBUG <> 0 THEN
666 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in set_bom_batch_id with unexp error.', 1);
667 END IF;
668 raise FND_API.G_EXC_UNEXPECTED_ERROR;
669 end if;
670
671
672 oe_debug_pub.add('create_config_item_wf: ' || ' resetting CTO_CONFIG_BOM_PK.g_t_dropped_item_type ' , 1 );
673
674 CTO_CONFIG_BOM_PK.g_t_dropped_item_type.delete ;
675
676
677
678 IF PG_DEBUG <> 0 THEN
679 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
680
681 oe_debug_pub.add('create_config_item_wf: ' || 'Calling Create BOM and Routing ', 1);
682 END IF;
683
684
685
686
687
688
689 l_stmt_num := 130;
690 CTO_BOM_RTG_PK.create_all_boms_and_routings(
691 pAtoLineId => to_number(p_itemkey),
692 pFlowCalc => l_perform_flow_calc,
693 xReturnStatus => l_xReturnStatus,
694 xMsgCount => l_xMsgCount,
695 xMsgData => l_xMsgData);
696
697
698
699
700 if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count > 0 ) then
701 oe_debug_pub.add( 'DROPPED component count > 0 ' , 1 ) ;
702
703 select oeh.order_number , msi.segment1, oel.line_number || '.' || oel.shipment_number
704 into v_order_number, v_top_model_name, v_top_model_line_num
705 from oe_order_headers_all oeh , oe_order_lines_all oel , mtl_system_items msi
706 where oeh.header_id = oel.header_id
707 and oel.line_id = lTopAtoLineId
708 and oel.inventory_item_id = msi.inventory_item_id
709 and oel.ship_from_org_id = msi.organization_id ;
710
711
712 for i in 1..CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count
713 loop
714
715 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).SALES_ORDER_NUM := v_order_number ;
716 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_NAME := v_top_model_name ;
717 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_MODEL_LINE_NUM := v_top_model_line_num ;
718 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME := null ;
719 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM := null ;
720 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID := null ;
721
722 end loop ;
723
724 end if ;
725
726
727
728
729
730 IF (l_xReturnStatus = fnd_api.G_RET_STS_ERROR) THEN
731 IF PG_DEBUG <> 0 THEN
732 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Create BOM and Routing with exp error.', 1);
733 END IF;
734
735
736 if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count > 0 ) then
737
738 if( lNotifyUsers = 1 ) then
739 oe_debug_pub.add('create_config_item_wf: ' || '********** ******* Will be Sending Notifications .', 1);
740 CTO_UTILITY_PK.send_oid_notification ; /* DROPPED COMPONENTS BOM NOT CREATED NOTIFICATION */
741
742 else
743 oe_debug_pub.add('create_config_item_wf: ' || '********** ******* Will not be Sending Notifications .', 1);
744
745 end if;
746
747
748
749 end if;
750
751
752
753
754
755
756
757
758
759 /* EXPECTED ERROR NOTIFICATION */
760
761
762 if( lNotifyUsers = 1 ) then
763 IF PG_DEBUG <> 0 THEN
764 oe_debug_pub.add('create_config_item_wf: ' || 'Going to Call CTO_UTILITY_PK.handle_expected_error ..',3);
765 END IF;
766
767 CTO_UTILITY_PK.handle_expected_error( p_error_type => CTO_UTILITY_PK.EXP_ERROR_AND_ITEM_NOT_CREATED
768 , p_inventory_item_id => v_top_model_id
769 , p_organization_id => v_ship_from_org_id
770 , p_line_id => lTopAtoLineId
771 , p_sales_order_num => v_order_number
772 , p_top_model_name => v_top_model_name
773 , p_top_model_line_num => v_top_model_line_num
774 , p_msg_count => l_xMsgCount
775 , p_planner_code => v_planner_code
776 , p_request_id => null
777 , p_process => 'NOTIFY_OEE_INC' ) ;
778
779
780
781 end if; /* lNotifyUsers */
782
783
784
785 raise FND_API.G_EXC_ERROR;
786
787
788
789
790 ELSIF (l_xReturnStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
791 IF PG_DEBUG <> 0 THEN
792 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Create BOM and Routing with unexp error.', 1);
793 END IF;
794 raise FND_API.G_EXC_UNEXPECTED_ERROR;
795 ELSE
796 IF PG_DEBUG <> 0 THEN
797 oe_debug_pub.add('create_config_item_wf: ' || 'Success in Create BOM and Routing ', 1);
798 END IF;
799 END IF;
800
801
802
803 --
804 -- Get the config item id to be linked
805 --
806
807 l_stmt_num := 140;
808 select bcol.config_item_id,
809 bcol.inventory_item_id,
810 bcol.ship_from_org_id,
811 perform_match -- Sushant added this to check full item match
812 into l_config_id,
813 l_model_id,
814 l_mfg_org_id,
815 l_perform_match -- Sushant added this to check full item match
816 from bom_cto_order_lines bcol
817 where bcol.line_id = to_number(p_itemkey);
818
819
820
821 --
822 -- Link the top level config item in oe_order_lines_all
823 --
824
825 l_stmt_num := 150;
826 IF PG_DEBUG <> 0 THEN
827 oe_debug_pub.add ('create_config_item_wf: ' || 'CALLING LINK_ITEM');
828 END IF;
829
830 l_status := CTO_CONFIG_ITEM_PK.link_item(
831 pOrgId => l_mfg_org_id,
832 pModelId => l_model_id,
833 pConfigId => l_config_id,
834 pLineId => to_number(p_itemkey),
835 xMsgCount => l_xMsgCount,
836 xMsgData => l_xMsgData );
837
838 if l_status <> 1 then
839 IF PG_DEBUG <> 0 THEN
840 oe_debug_pub.add ('create_config_item_wf: ' || 'Failed in link_item function', 1);
841 END IF;
842
843
844 /* EXPECTED ERROR NOTIFICATION */
845
846
847 if( lNotifyUsers = 1 ) then
848 IF PG_DEBUG <> 0 THEN
849 oe_debug_pub.add('create_config_item_wf: ' || 'Going to Call CTO_UTILITY_PK.handle_expected_error ..',3);
850 END IF;
851
852 CTO_UTILITY_PK.handle_expected_error( p_error_type => CTO_UTILITY_PK.EXP_ERROR_AND_ITEM_NOT_CREATED
853 , p_inventory_item_id => v_top_model_id
854 , p_organization_id => v_ship_from_org_id
855 , p_line_id => lTopAtoLineId
856 , p_sales_order_num => v_order_number
857 , p_top_model_name => v_top_model_name
858 , p_top_model_line_num => v_top_model_line_num
859 , p_msg_count => l_xMsgCount
860 , p_planner_code => v_planner_code
861 , p_request_id => null
862 , p_process => 'NOTIFY_OEE_INC' ) ;
863
864
865
866 end if; /* lNotifyUsers */
867
868
869 raise FND_API.G_EXC_ERROR;
870 end if;
871
872 IF PG_DEBUG <> 0 THEN
873 oe_debug_pub.add ('create_config_item_wf: ' || 'Success in link_item function', 1);
874
875 oe_debug_pub.add ('create_config_item_wf: ' || 'Getting config line id.', 1);
876 END IF;
877
878 l_stmt_num := 152;
879
880 select line_id, header_id, inventory_item_id
881 into l_config_line_id, l_header_id, l_config_item_id
882 from oe_order_lines_all
883 where ato_line_id = to_number(p_itemkey)
884 and item_type_code = 'CONFIG';
885
886 IF PG_DEBUG <> 0 THEN
887 oe_debug_pub.add ('create_config_item_wf: ' || 'Config line id is ' || to_char(l_config_line_id), 1);
888
889 oe_debug_pub.add('create_config_item_wf: ' || 'header ID: ' || to_char(l_header_id), 1);
890 END IF;
891
892
893 l_stmt_num := 155;
894
895 CTO_WORKFLOW_API_PK.query_wf_activity_status(
896 p_itemtype => 'OEOL' ,
897 p_itemkey => to_char(l_config_line_id ) ,
898 p_activity_label => 'CREATE_CONFIG_BOM_ELIGIBLE',
899 p_activity_name => 'CREATE_CONFIG_BOM_ELIGIBLE',
900 p_activity_status => l_active_activity );
901
902 IF PG_DEBUG <> 0 THEN
903 oe_debug_pub.add('create_config_item_wf: ' || 'Workflow CREATE_CONFIG_BOM_ELIGIBLE Status is: ' ||
904 l_active_activity, 1);
905
906 oe_debug_pub.add('create_config_item_wf: ' || ' updating config bom eligible to complete:created ' , 1);
907 END IF;
908
909
910
911 /* Additional Code for Error Processing */
912 /* COLLECT CONFIG DATA for ERROR PROCESSING */
913
914
915
916
917
918
919
920 select msi.segment1, oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' )
921 || '.' || nvl(component_number , '' )
922 into v_top_config_name, v_top_config_line_num
923 from oe_order_lines_all oel , mtl_system_items msi
924 where oel.ato_line_id = lTopAtoLineId
925 and item_type_code = 'CONFIG'
926 and oel.inventory_item_id = msi.inventory_item_id
927 and oel.ship_from_org_id = msi.organization_id ;
928
929
930
931
932
933
934 l_stmt_num := 160;
935
936
937 /*
938 lMatchProfile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
939 IF PG_DEBUG <> 0 THEN
940 oe_debug_pub.add('create_config_item_wf: ' || 'Match Profile is ' || lMatchProfile, 1 );
941 END IF;
942 removed lMatchProfile check as this fix has been made in CTOUTILB.pls
943 */
944
945
946 IF PG_DEBUG <> 0 THEN
947 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
948 END IF;
949
950
951
952
953
954 if( CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count > 0 ) then
955 oe_debug_pub.add( 'DROPPED component count > 0 ' , 1 ) ;
956
957 select msi.segment1, oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' )
958 || '.' || nvl(component_number , '' )
959 into v_top_config_name, v_top_config_line_num
960 from oe_order_lines_all oel , mtl_system_items msi
961 where oel.ato_line_id = lTopAtoLineId
962 and item_type_code = 'CONFIG'
963 and oel.inventory_item_id = msi.inventory_item_id
964 and oel.ship_from_org_id = msi.organization_id ;
965
966
967 for i in 1..CTO_CONFIG_BOM_PK.g_t_dropped_item_type.count
968 loop
969
970 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_NAME := v_top_config_name ;
971 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).TOP_CONFIG_LINE_NUM := v_top_config_line_num ;
972 CTO_CONFIG_BOM_PK.g_t_dropped_item_type(i).REQUEST_ID := null ;
973
974 end loop ;
975
976
977 if( lNotifyUsers = 1 ) then
978 CTO_UTILITY_PK.send_oid_notification ; /* DROPPED COMPONENTS ITEM CREATED NOTIFICATION */
979
980 else
981 oe_debug_pub.add('create_config_item_wf: ' || '********** ******* Will not be Sending Notifications .', 1);
982
983 end if;
984
985
986
987 end if ;
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001 l_stmt_num := 163;
1002 /* ATO Line Workflow will not have individual activities to be bypassed
1003 wf_engine.CompleteActivityInternalName(
1004 'OEOL',
1005 l_config_line_id,
1006 'CREATE_CONFIG_BOM_ELIGIBLE',
1007 'CREATED');
1008
1009 */
1010
1011
1012
1013
1014
1015
1016 if( lPerformPPRollup = 1 ) then
1017
1018 IF PG_DEBUG <> 0 THEN
1019 oe_debug_pub.add('create_config_item_wf: ' || 'Will Perform Purchase Price Rollup as profile is Yes ', 1);
1020 END IF;
1021
1022 -- Added by Renga Kannan on 03/26/02 to call
1023 -- Create purchase doc code for match case. In the case of
1024 -- match the workflow will be moved to create config eligible directly
1025
1026 CTO_AUTO_PROCURE_PK.Create_Purchasing_Doc(
1027 p_config_item_id => l_config_item_id ,
1028 p_overwrite_list_price => 'N',
1029 p_called_in_batch => 'N',
1030 p_batch_number => l_batch_no,
1031 p_ato_line_id => to_number(p_itemKey),
1032 x_oper_unit_list => x_oper_unit_list,
1033 x_return_status => x_Return_Status,
1034 x_msg_count => X_Msg_Count,
1035 x_msg_data => x_msg_data);
1036
1037
1038
1039
1040
1041
1042
1043 IF (x_Return_Status = fnd_api.G_RET_STS_ERROR) THEN
1044 IF PG_DEBUG <> 0 THEN
1045 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Purchase Price ROLLUP .', 1);
1046 END IF;
1047
1048
1049 /* EXPECTED ERROR NOTIFICATION */
1050
1051
1052 if( lNotifyUsers = 1 ) then
1053 IF PG_DEBUG <> 0 THEN
1054 oe_debug_pub.add('create_config_item_wf: ' || 'Going to Call CTO_UTILITY_PK.handle_expected_error ..',3);
1055 END IF;
1056
1057 CTO_UTILITY_PK.handle_expected_error( p_error_type => CTO_UTILITY_PK.EXP_ERROR_AND_ITEM_NOT_CREATED
1058 , p_inventory_item_id => v_top_model_id
1059 , p_organization_id => v_ship_from_org_id
1060 , p_line_id => lTopAtoLineId
1061 , p_sales_order_num => v_order_number
1062 , p_top_model_name => v_top_model_name
1063 , p_top_model_line_num => v_top_model_line_num
1064 , p_top_config_name => v_top_config_name
1065 , p_top_config_line_num => v_top_config_line_num
1066 , p_msg_count => l_xMsgCount
1067 , p_planner_code => v_planner_code
1068 , p_request_id => null
1069 , p_process => 'NOTIFY_OEE_IC' ) ;
1070
1071
1072
1073 end if; /* lNotifyUsers */
1074
1075
1076
1077
1078
1079 raise FND_API.G_EXC_ERROR;
1080
1081
1082
1083
1084 ELSIF ( x_Return_Status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
1085 IF PG_DEBUG <> 0 THEN
1086 oe_debug_pub.add('create_config_item_wf: ' || 'Failed in Purchase Price ROllup .', 1);
1087 END IF;
1088
1089 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1090 ELSE
1091 IF PG_DEBUG <> 0 THEN
1092 oe_debug_pub.add('create_config_item_wf: ' || 'Success in Purchase Price Rollup ', 1);
1093 END IF;
1094 END IF;
1095
1096
1097
1098
1099
1100 else
1101
1102 IF PG_DEBUG <> 0 THEN
1103 oe_debug_pub.add('create_config_item_wf: ' || 'Will Not Perform Purchase Price Rollup as profile is No ', 1);
1104 END IF;
1105
1106 end if ;
1107
1108
1109
1110
1111
1112
1113
1114 if( lPerformCSTRollup = 1 ) then
1115
1116 IF PG_DEBUG <> 0 THEN
1117 oe_debug_pub.add('create_config_item_wf: ' || 'Will Perform Cost Rollup as profile is Yes ', 1);
1118 END IF;
1119
1120
1121
1122 l_status := CTO_CONFIG_COST_PK.cost_rollup_ml(
1123 pTopAtoLineId => p_itemkey,
1124 x_msg_count => l_xmsgcount,
1125 x_msg_data => l_xmsgdata);
1126
1127 if (l_status = 0) then
1128 IF PG_DEBUG <> 0 THEN
1129 oe_debug_pub.add('create_config_item_wf: ' || 'Failure in cost_rollup ', 1);
1130 END IF;
1131 --cto_msg_pub.cto_message('BOM', l_xmsgdata);
1132 raise FND_API.G_EXC_ERROR;
1133
1134
1135 elsif( l_status = -1 ) then
1136 IF PG_DEBUG <> 0 THEN
1137 oe_debug_pub.add('create_config_item_wf: ' || 'Unexpected Failure in cost_rollup ', 1);
1138 END IF;
1139 cto_msg_pub.cto_message('BOM', l_xmsgdata);
1140 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1141
1142 else
1143 IF PG_DEBUG <> 0 THEN
1144 oe_debug_pub.add('create_config_item_wf: ' || 'Success in cost_rollup ', 1);
1145 END IF;
1146 end if;
1147
1148
1149
1150 else
1151
1152 IF PG_DEBUG <> 0 THEN
1153 oe_debug_pub.add('create_config_item_wf: ' || 'Will Not perform cost_rollup as profile is No ', 1);
1154 END IF;
1155
1156 end if ;
1157
1158
1159
1160
1161
1162 /*
1163 **
1164 ** LEAD TIME CALCULATION CHANGES GO HERE
1165 **
1166 */
1167
1168 if( lPerformLTCalc = 1 ) then
1169
1170 IF PG_DEBUG <> 0 THEN
1171 oe_debug_pub.add('create_config_item_wf: ' || 'Will perform Lead Time Calc as profile is Yes ' || lTopAtoLineId , 1);
1172 END IF;
1173
1174
1175
1176 l_requestId := fnd_request.submit_request( application => 'BOM',
1177 program => 'CTOCLT',
1178 description => null,
1179 start_time => null,
1180 sub_request => false,
1181 argument1 => lTopAtoLineId );
1182
1183 IF PG_DEBUG <> 0 THEN
1184 oe_debug_pub.add('create_config_item_wf: ' || ' request id ' || l_requestid , 1);
1185 END IF;
1186
1187 /*
1188 | FUNCTION submit_request (
1189 | application IN varchar2,
1190 | program IN varchar2,
1191 | description IN varchar2,
1192 | start_time IN varchar2,
1193 | sub_request IN boolean,
1194 | argument1 IN varchar2,
1195 | argument2 IN varchar2,
1196 | .........
1197 | argument100 IN varchar2) return number
1198 |
1199 */
1200
1201
1202 else
1203
1204 IF PG_DEBUG <> 0 THEN
1205 oe_debug_pub.add('create_config_item_wf: ' || 'Will Not perform Lead Time Calc as profile is No ', 1);
1206 END IF;
1207
1208 end if ;
1209
1210
1211
1212
1213
1214 --
1215 -- Bugfix 2234858 Drop SHIPMENT Project Enhancement
1216 -- Sushant Modified this code to allow reservation only for non drop shipped items
1217 --
1218 --Bugfix 6046572: No reservation getting created in case of custom match. Replaced
1219 --l_perform_match = 'Y' with l_perform_match in ('Y', 'C')
1220 IF ( v_source_type_code = 'INTERNAL' AND l_perform_match in ('Y', 'C')) THEN
1221
1222 l_stmt_num := 167;
1223
1224
1225 create_reservation(
1226 l_mfg_org_id,
1227 lTopAtoLineId,
1228 l_config_id,
1229 l_reservation_uom_code,
1230 l_quantity_to_reserve,
1231 l_schedule_ship_date,
1232 'ONLINE' ,
1233 x_reserve_status ,
1234 x_msg_count ,
1235 x_msg_data ,
1236 x_return_status ) ;
1237
1238 -- Complete the block activity in the config flow
1239 l_stmt_num := 170;
1240
1241 if( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
1242
1243
1244 IF PG_DEBUG <> 0 THEN
1245 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1246
1247 oe_debug_pub.add('create_config_item_wf: ' || ' create_reservation status success ' , 1 );
1248
1249 oe_debug_pub.add('create_config_item_wf: ' || ' reserve status ' || x_reserve_status , 1 );
1250 END IF;
1251
1252
1253 l_stmt_num := 175;
1254
1255
1256 IF PG_DEBUG <> 0 THEN
1257 oe_debug_pub.add('create_config_item_wf: ' || 'going to get Workflow Status for config line : ' ||
1258 to_char( l_config_line_id ) , 1 );
1259 END IF;
1260
1261 CTO_WORKFLOW_API_PK.get_activity_status(
1262 itemtype => 'OEOL',
1263 itemkey => to_char(l_config_line_id),
1264 linetype => 'CONFIG',
1265 activity_name => l_active_activity);
1266
1267 IF PG_DEBUG <> 0 THEN
1268 oe_debug_pub.add('create_config_item_wf: ' || 'Workflow Status is: ' ||
1269 l_active_activity, 1);
1270 END IF;
1271
1272
1273 l_stmt_num := 178;
1274
1275 CTO_WORKFLOW_API_PK.query_wf_activity_status(
1276 p_itemtype => 'OEOL' ,
1277 p_itemkey => to_char(l_config_line_id ) ,
1278 p_activity_label => 'SHIP_LINE',
1279 p_activity_name => 'SHIP_LINE',
1280 p_activity_status => l_active_activity );
1281
1282 IF PG_DEBUG <> 0 THEN
1283 oe_debug_pub.add('create_config_item_wf: ' || 'Workflow SHIP_LINE Status is: ' ||
1284 l_active_activity, 1);
1285 END IF;
1286
1287
1288
1289 else /* create reservation not successful */
1290
1291 IF PG_DEBUG <> 0 THEN
1292 oe_debug_pub.add('create_config_item_wf: ' || ' create_reservation status failure ' , 1 );
1293 END IF;
1294
1295
1296
1297 end if ;
1298
1299
1300 elsif v_source_type_code = 'INTERNAL' then /* end if source type code INTERNAL */
1301
1302 l_stmt_num := 220;
1303 IF PG_DEBUG <> 0 THEN
1304 oe_debug_pub.add('create_config_item_wf: ' || ' No Reservation attempted as match not successful ' , 1 );
1305 END IF;
1306
1307
1308
1309 elsif v_source_type_code = 'EXTERNAL' then /* end if source type code INTERNAL */
1310
1311 IF PG_DEBUG <> 0 THEN
1312 oe_debug_pub.add('create_reservation: ' || 'Drop Ship Scenario ' , 1 );
1313 END IF ;
1314
1315
1316
1317 oe_debug_pub.add('create_reservation: ' || 'Drop Ship Scenario perform match ' || l_perform_match , 1 );
1318
1319
1320
1321 if( l_perform_match = 'Y' ) then
1322
1323 IF PG_DEBUG <> 0 THEN
1324 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1325
1326 oe_debug_pub.add('create_reservation: ' || 'DropShip Matched item ' ||
1327 l_config_id, 1 );
1328 END IF;
1329
1330
1331 select segment1
1332 into l_config_item_name
1333 from mtl_system_items
1334 where inventory_item_id = l_config_id
1335 and organization_id = l_mfg_org_id ;
1336 /* fixed bug 1853597 to retrieve only one row for each item */
1337
1338 l_stmt_num := 235;
1339
1340 IF PG_DEBUG <> 0 THEN
1341 oe_debug_pub.add('create_reservation: ' || 'CTO_CONFIG_MATCH for item ' || l_config_item_name , 1 );
1342 END IF;
1343
1344
1345 l_token(1).token_name := 'CONFIG_ITEM';
1346 l_token(1).token_value := l_config_item_name;
1347
1348 cto_msg_pub.cto_message('BOM', 'CTO_CONFIG_MATCH', l_token);
1349 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1350
1351 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1352
1353 OE_STANDARD_WF.Save_Messages;
1354 OE_STANDARD_WF.Clear_Msg_Context;
1355
1356
1357 end if ; /* l_perform_match = 'Y' */
1358
1359
1360
1361 end if ; /* v_source_type_code = 'INTERNAL' */
1362
1363
1364 IF PG_DEBUG <> 0 THEN
1365 oe_debug_pub.add('create_config_item_wf: ' || 'Time Stamp '
1366 || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' )) ;
1367
1368
1369 END IF;
1370
1371
1372 l_stmt_num := 230;
1373 -- Added by Renga Kannan 03/30/06
1374 -- This is a wrapper API to call PLM team's to sync up item media index
1375 -- With out this sync up the item cannot be searched in Simple item search page
1376 -- This is fixed for bug 4656048
1377
1378 CTO_MSUTIL_PUB.syncup_item_media_index;
1379
1380
1381
1382 x_result := 'COMPLETE';
1383
1384
1385
1386
1387
1388 end if; /* end of p_funcmode = 'RUN' */
1389
1390 CTO_CONFIG_BOM_PK.gApplyHold := 'N'; -- bugfix 2899529: Reset this global variable.
1391
1392 OE_STANDARD_WF.Save_Messages;
1393 OE_STANDARD_WF.Clear_Msg_Context;
1394
1395
1396 EXCEPTION
1397
1398 when FND_API.G_EXC_ERROR then
1399 IF PG_DEBUG <> 0 THEN
1400 OE_DEBUG_PUB.add('create_config_item_wf: ' || 'CTO_WORKFLOW.create_config_item_wf ' ||
1401 to_char(l_stmt_num) || ':' ||
1402 l_x_error_msg);
1403 END IF;
1404 fnd_msg_pub.count_and_get(p_data=>x_msg_data,p_count=>x_msg_count);
1405 OE_STANDARD_WF.Save_Messages;
1406 OE_STANDARD_WF.Clear_Msg_Context;
1407 x_result := 'COMPLETE:INCOMPLETE';
1408 rollback to savepoint before_item_creation;
1409
1410 when FND_API.G_EXC_UNEXPECTED_ERROR then
1411 IF PG_DEBUG <> 0 THEN
1412 OE_DEBUG_PUB.add('create_config_item_wf: ' || 'CTO_WORKFLOW.create_config_item_wf ' ||
1413 to_char(l_stmt_num) || ':' ||
1414 l_x_error_msg);
1415 END IF;
1416 OE_STANDARD_WF.Save_Messages;
1417 OE_STANDARD_WF.Clear_Msg_Context;
1418 wf_core.context('CTO_WORKFLOW', 'create_config_item_wf',
1419 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1420 raise;
1421
1422 when OTHERS then
1423 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ITEM_ERROR');
1424 IF PG_DEBUG <> 0 THEN
1425 oe_debug_pub.add('create_config_item_wf: ' || 'CTO_WORKFLOW.create_config_item_wf' ||
1426 to_char(l_stmt_num) || ':' ||
1427 substrb(sqlerrm, 1, 100));
1428 END IF;
1429 wf_core.context('CTO_WORKFLOW', 'create_config_item_wf',
1430 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1431
1432 OE_STANDARD_WF.Save_Messages;
1433 OE_STANDARD_WF.Clear_Msg_Context;
1434
1435 raise;
1436
1437 END create_config_item_wf;
1438
1439
1440
1441 /*============================================================================
1442 Procedure: create_reservation
1443 Description: This API gets called from create_config_item_wf
1444
1445 Parameters:
1446 ============================================================================*/
1447 PROCEDURE create_reservation(
1448 p_mfg_org_id in number ,
1449 p_top_model_line_id in number,
1450 p_config_id in number ,
1451 p_reservation_uom_code in varchar2 ,
1452 p_quantity_to_reserve in number,
1453 p_schedule_ship_date in DATE,
1454 p_mode in varchar2 ,
1455 x_reserve_status out NoCopy varchar2,
1456 x_msg_count out NoCopy number ,
1457 x_msg_data out NoCopy varchar2,
1458 x_return_status out NoCopy varchar2
1459 )
1460 IS
1461
1462 l_tree_id integer ;
1463 l_return_status varchar2(1) ;
1464
1465
1466 l_x_qoh number;
1467 l_x_rqoh number;
1468 l_x_qs number;
1469 l_x_qr number;
1470 l_x_att number;
1471
1472 l_primary_uom_code varchar2(3);
1473 l_automatic_reservation varchar2(2) ;
1474 l_diff_days number ;
1475 l_reservation_time_fence number;
1476
1477 l_quantity_to_reserve number ;
1478 x_available_qty number ;
1479 x_quantity_reserved number ;
1480 -- x_msg_count number ;
1481 -- x_msg_data varchar2(200) ;
1482 x_error_message varchar2(200) ;
1483 x_message_name varchar2(200) ;
1484
1485 l_stmt_num number := 0 ;
1486
1487 PROCESS_ERROR exception;
1488 RESERVATION_ERROR exception;
1489
1490 l_x_error_msg_name varchar2(30);
1491 l_x_error_msg varchar2(500); --bugfix 2776026: increased the var size
1492
1493
1494 l_organization_name varchar2(200) ;
1495 l_config_item_name varchar2(200) ;
1496 lMatchProfile varchar2(10);
1497
1498 l_partial_reservation boolean := FALSE ;
1499 l_token CTO_MSG_PUB.token_tbl;
1500
1501 l_current_org_id Number;
1502 BEGIN
1503 /*
1504 ** Check whether full match was successful for top model to create reservations
1505 */
1506
1507 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1508 x_reserve_status := 'MATCH' ;
1509
1510
1511 /*
1512 ** x_reserve_status := { 'MATCH', 'NOQTY' , 'PARTIAL' , 'COMPLETE' }
1513 */
1514
1515 l_quantity_to_reserve := p_quantity_to_reserve ;
1516
1517
1518 /* use this to override l_automatic_reservation := 'Y' ;*/
1519
1520
1521 l_automatic_reservation := FND_PROFILE.VALUE('CTO_AUTOMATIC_RESERVATION');
1522
1523 -- Code change for MOAC
1524
1525
1526 l_current_org_id := MO_GLOBAL.get_current_org_id;
1527 l_reservation_time_fence :=
1528 oe_sys_parameters.value('ONT_RESERVATION_TIME_FENCE',l_current_org_id);
1529
1530 -- End of MOAC code Change
1531
1532 l_diff_days := trunc( p_schedule_ship_date ) - trunc( sysdate ) ;
1533
1534
1535
1536 IF PG_DEBUG <> 0 THEN
1537 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1538
1539 oe_debug_pub.add('create_reservation: ' || ' going to check for reservation ' , 1 );
1540
1541 oe_debug_pub.add('create_reservation: ' || ' going to check for reservation ' || to_char(l_diff_days) ||
1542 ' time fence ' || to_char(l_reservation_time_fence)
1543 , 1 );
1544
1545 oe_debug_pub.add('create_reservation: ' || ' going to check for automatic reservation ' ||
1546 l_automatic_reservation , 1 );
1547 END IF;
1548
1549 if( l_automatic_reservation = '1' and
1550 l_diff_days <= l_reservation_time_fence
1551 )
1552 then
1553
1554 IF PG_DEBUG <> 0 THEN
1555 oe_debug_pub.add('create_reservation: ' || ' going to attempt reservation ' , 1 );
1556 END IF;
1557
1558 /*-------------------------------------------------+
1559 Create a quantity tree to get atr for reservation.
1560 +--------------------------------------------------*/
1561 l_stmt_num := 150;
1562
1563 INV_QUANTITY_TREE_GRP.create_tree
1564 ( p_api_version_number => 1.0
1565 , p_init_msg_lst => fnd_api.g_false
1566 , x_return_status => l_return_status
1567 , x_msg_count => x_msg_count
1568 , x_msg_data => x_msg_data
1569 , p_organization_id => p_mfg_org_id
1570 , p_inventory_item_id => p_config_id
1571 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
1572 , p_is_revision_control => FALSE
1573 , p_is_lot_control => FALSE
1574 , p_is_serial_control => FALSE
1575 , x_tree_id => l_tree_id);
1576
1577 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1578 IF PG_DEBUG <> 0 THEN
1579 oe_debug_pub.add('create_reservation: ' || 'Failed in create_tree with status: ' ||
1580 l_return_status, 1);
1581 END IF;
1582 raise PROCESS_ERROR;
1583 ELSE
1584 IF PG_DEBUG <> 0 THEN
1585 oe_debug_pub.add('create_reservation: ' || 'Success in create_tree.',1);
1586
1587 oe_debug_pub.add('create_reservation: ' || 'Tree ID:' || to_char(l_tree_id),1);
1588 END IF;
1589 END IF;
1590
1591 /*-----------------------------------------------------+
1592 Query quantity tree get quantity available to reserve.
1593 +------------------------------------------------------*/
1594 l_stmt_num := 160;
1595
1596 INV_QUANTITY_TREE_GRP.query_tree
1597 (p_api_version_number => 1.0,
1598 p_init_msg_lst => fnd_api.g_false,
1599 x_return_status => l_return_status,
1600 x_msg_count => x_msg_count,
1601 x_msg_data => x_msg_data,
1602 p_tree_id => l_tree_id,
1603 p_revision => NULL,
1604 p_lot_number => NULL,
1605 p_subinventory_code => NULL,
1606 p_locator_id => NULL,
1607 x_qoh => l_x_qoh,
1608 x_rqoh => l_x_rqoh,
1609 x_qr => l_x_qr,
1610 x_qs => l_x_qs,
1611 x_att => l_x_att,
1612 x_atr => x_available_qty);
1613
1614 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1615 IF PG_DEBUG <> 0 THEN
1616 oe_debug_pub.add('create_reservation: ' || 'Failed in create_tree with status: ' ||
1617 l_return_status, 1);
1618 END IF;
1619 raise PROCESS_ERROR;
1620 end if;
1621 IF PG_DEBUG <> 0 THEN
1622 oe_debug_pub.add('create_reservation: ' || 'Success in query_tree.', 1);
1623
1624 oe_debug_pub.add('create_reservation: ' || 'l_x_qoh: ' || to_char(l_x_qoh));
1625
1626 oe_debug_pub.add('create_reservation: ' || 'l_x_rqoh: ' || to_char(l_x_rqoh));
1627
1628 oe_debug_pub.add('create_reservation: ' || 'x_available_qty: ' || to_char(x_available_qty));
1629
1630 oe_debug_pub.add('create_reservation: ' || ' config id ' || to_char(p_config_id ) ||
1631 ' mfg_org_id ' || to_char( p_mfg_org_id ) , 1);
1632 END IF;
1633
1634 l_stmt_num := 170;
1635
1636 select msi.primary_uom_code
1637 into l_primary_uom_code
1638 from mtl_system_items msi
1639 where msi.inventory_item_id = p_config_id
1640 and msi.organization_id = p_mfg_org_id;
1641
1642 IF PG_DEBUG <> 0 THEN
1643 oe_debug_pub.add('create_reservation: ' || ' pri uom ' || l_primary_uom_code ||
1644 ' res uom ' || p_reservation_uom_code , 1);
1645 END IF;
1646
1647
1648 /*------------------------------------------------------
1649 The quantity query gives ATR in the primary uom code
1650 so we need to convert it to the same uom as the
1651 p_reservation_uom_code.
1652 +------------------------------------------------------*/
1653 IF (l_primary_uom_code <> p_reservation_uom_code) THEN
1654 l_stmt_num := 175;
1655 x_available_qty := INV_CONVERT.inv_um_convert
1656 ( item_id => p_config_id,
1657 precision => 5, -- bugfix 2204376: pass precision of 5
1658 from_quantity => x_available_qty, -- from qty
1659 from_unit => l_primary_uom_code, -- from uom
1660 to_unit => p_reservation_uom_code, -- to uom
1661 from_name => null,
1662 to_name => null);
1663 END IF;
1664
1665 /*---------------------------------------------------------+
1666 p_automatic_reservation is TRUE when match and reserve is
1667 called from Order Import. From Order Import, if a match
1668 is found, a reservation is made automatically if there
1669 is sufficient quantity.
1670 +---------------------------------------------------------*/
1671 if (x_available_qty > 0 )
1672 then
1673 l_stmt_num := 180;
1674 IF PG_DEBUG <> 0 THEN
1675 oe_debug_pub.add('create_reservation: ' || 'Entering Create Reservation. ',1);
1676
1677 oe_debug_pub.add('create_reservation: ' || 'Quantity to Rsrv: '
1678 || to_char(l_quantity_to_reserve ),1);
1679
1680 oe_debug_pub.add('create_reservation: ' || 'Quantity Available to Rsrv: '
1681 || to_char(x_available_qty),1);
1682 END IF;
1683
1684 if( l_quantity_to_reserve > x_available_qty ) then
1685
1686 l_quantity_to_reserve := x_available_qty ;
1687
1688 l_partial_reservation := TRUE ;
1689
1690 IF PG_DEBUG <> 0 THEN
1691 oe_debug_pub.add('create_reservation: ' || 'Going to attempt reservation for' ||
1692 to_char(l_quantity_to_reserve ));
1693 END IF;
1694
1695
1696 end if ;
1697
1698 l_stmt_num := 185;
1699
1700 IF PG_DEBUG <> 0 THEN
1701 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1702 END IF;
1703
1704
1705 if (CTO_MATCH_AND_RESERVE.create_config_reservation
1706 (p_model_line_id => p_top_model_line_id,
1707 p_config_item_id => p_config_id,
1708 p_quantity_to_reserve => l_quantity_to_reserve,
1709 p_reservation_uom_code => p_reservation_uom_code,
1710 x_quantity_reserved => x_quantity_reserved,
1711 x_error_msg => l_x_error_msg,
1712 x_error_msg_name => l_x_error_msg_name) = TRUE)
1713 then
1714 IF PG_DEBUG <> 0 THEN
1715 oe_debug_pub.add('create_reservation: ' || 'Success in Create Reservation. ',1);
1716
1717 oe_debug_pub.add('create_reservation: ' || 'Matching Config Item: ' || to_char(p_config_id),1 );
1718
1719 oe_debug_pub.add ('create_reservation: ' || 'Quantity On-Hand: ' || to_char(x_available_qty),1);
1720
1721 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1722 END IF;
1723
1724
1725 l_stmt_num := 190;
1726
1727 select segment1
1728 into l_config_item_name
1729 from mtl_system_items
1730 where inventory_item_id = p_config_id
1731 and organization_id = p_mfg_org_id ;
1732
1733
1734 l_stmt_num := 195;
1735
1736 /*
1737 BUG 1870761 commented for some time as
1738 mtl_organizations view has severe performance
1739 issues in TST115 environment 07-10-2001
1740
1741 select organization_name into l_organization_name
1742 from mtl_organizations
1743 where organization_id = p_mfg_org_id ;
1744
1745 */
1746
1747 /* reintroduced the organization_name in the message
1748 ** as per bug#2320488 by using table
1749 ** inv_organization_name_v
1750 */
1751 begin
1752
1753 select organization_name into l_organization_name
1754 from inv_organization_name_v
1755 where organization_id = p_mfg_org_id ;
1756
1757 exception
1758 when others then
1759
1760 l_organization_name := to_char( p_mfg_org_id ) ;
1761
1762 end ;
1763
1764
1765
1766
1767 IF PG_DEBUG <> 0 THEN
1768 oe_debug_pub.add('create_reservation: ' || 'CTO_RESERVATION_SUCCESS for ' ||
1769 to_char(l_quantity_to_reserve) ||
1770 ' units for item ' || l_config_item_name ||
1771 ' in org ' || l_organization_name , 1 );
1772 END IF;
1773
1774
1775 l_stmt_num := 200;
1776
1777 if( p_mode = 'ONLINE' ) then
1778
1779
1780 l_token(1).token_name := 'QUANTITY';
1781 l_token(1).token_value := l_quantity_to_reserve;
1782 l_token(2).token_name := 'CONFIG_ITEM';
1783 l_token(2).token_value := l_config_item_name;
1784 l_token(3).token_name := 'SHIP_ORG';
1785 l_token(3).token_value := l_organization_name;
1786
1787
1788 cto_msg_pub.cto_message('BOM', 'CTO_RESERVATION_SUCCESS', l_token );
1789 --fnd_message.set_token('QUANTITY', l_quantity_to_reserve );
1790 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1791 --fnd_message.set_token('SHIP_ORG', l_organization_name );
1792
1793 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1794
1795
1796 OE_STANDARD_WF.Save_Messages;
1797 OE_STANDARD_WF.Clear_Msg_Context;
1798
1799 end if ;
1800
1801
1802 l_stmt_num := 205;
1803
1804 if( l_partial_reservation ) then
1805 x_reserve_status := 'PARTIAL' ;
1806 else
1807 x_reserve_status := 'COMPLETE' ;
1808 end if ;
1809
1810 x_message_name := 'CTO_RESERVE';
1811
1812 else
1813 IF PG_DEBUG <> 0 THEN
1814 oe_debug_pub.add('create_reservation: ' || 'Failed in Create Reservation. ',1);
1815 END IF;
1816 cto_msg_pub.cto_message('BOM', l_x_error_msg ); /* BUGFIX#2342412 */
1817 raise RESERVATION_ERROR;
1818 end if;
1819
1820 else
1821 /*--------------------------------------------------+
1822 If available quantity to reserve is less than
1823 zero, return with no option to reserve.
1824 Otherwise, user has the option to reserve against
1825 the ATR quantity.
1826 +--------------------------------------------------*/
1827
1828 l_stmt_num := 210;
1829
1830 IF PG_DEBUG <> 0 THEN
1831 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1832
1833 oe_debug_pub.add('create_reservation: ' || 'Not Enough Qty to reserve. ',1);
1834
1835 oe_debug_pub.add('create_reservation: ' || 'Quantity Available to Rsrv: '
1836 || to_char(x_available_qty),1);
1837 END IF;
1838
1839 x_message_name := 'CTO_CONFIG_LINKED';
1840 x_error_message := 'Config Item Linked. No Qty to Rsrv';
1841
1842 l_stmt_num := 215;
1843
1844 select segment1
1845 into l_config_item_name
1846 from mtl_system_items
1847 where inventory_item_id = p_config_id
1848 and organization_id = p_mfg_org_id ;
1849
1850
1851 l_stmt_num := 220;
1852
1853 /*
1854 BUG 1870761 commented for some time as
1855 mtl_organizations view has severe performance
1856 issues in TST115 environment 07-10-2001
1857
1858 select organization_name into l_organization_name
1859 from mtl_organizations
1860 where organization_id = p_mfg_org_id ;
1861
1862 */
1863
1864 /* reintroduced the organization_name in the message
1865 ** as per bug#2320488 by using table
1866 ** inv_organization_name_v
1867 */
1868 begin
1869
1870 select organization_name into l_organization_name
1871 from inv_organization_name_v
1872 where organization_id = p_mfg_org_id ;
1873
1874 exception
1875 when others then
1876
1877 l_organization_name := to_char( p_mfg_org_id ) ;
1878
1879 end ;
1880
1881
1882
1883 IF PG_DEBUG <> 0 THEN
1884 oe_debug_pub.add('create_reservation: ' || 'CTO_RESERVATION_FAILURE for ' || l_config_item_name ||
1885 ' in org ' || l_organization_name , 1 );
1886 END IF;
1887
1888 l_stmt_num := 225;
1889
1890 if( p_mode = 'ONLINE' ) then
1891
1892 l_token(1).token_name := 'CONFIG_ITEM';
1893 l_token(1).token_value := l_config_item_name;
1894 l_token(2).token_name := 'SHIP_ORG';
1895 l_token(2).token_value := l_organization_name;
1896
1897 cto_msg_pub.cto_message('BOM', 'CTO_RESERVATION_FAILURE', l_token);
1898 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1899 --fnd_message.set_token('SHIP_ORG', l_organization_name );
1900
1901 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1902
1903 OE_STANDARD_WF.Save_Messages;
1904 OE_STANDARD_WF.Clear_Msg_Context;
1905
1906 end if;
1907
1908 x_reserve_status := 'NOQTY' ;
1909
1910 end if ;
1911
1912
1913 else
1914
1915 l_stmt_num := 230;
1916
1917 IF PG_DEBUG <> 0 THEN
1918 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1919
1920 oe_debug_pub.add('create_reservation: ' || 'No reservation could be attempted for ' ||
1921 l_config_item_name , 1 );
1922 END IF;
1923
1924
1925 select segment1
1926 into l_config_item_name
1927 from mtl_system_items
1928 where inventory_item_id = p_config_id
1929 and organization_id = p_mfg_org_id ;
1930 /* fixed bug 1853597 to retrieve only one row for each item */
1931
1932 l_stmt_num := 235;
1933
1934 IF PG_DEBUG <> 0 THEN
1935 oe_debug_pub.add('create_reservation: ' || 'CTO_CONFIG_MATCH for item ' || l_config_item_name , 1 );
1936 END IF;
1937
1938 if( p_mode = 'ONLINE' ) then
1939
1940 l_token(1).token_name := 'CONFIG_ITEM';
1941 l_token(1).token_value := l_config_item_name;
1942
1943 cto_msg_pub.cto_message('BOM', 'CTO_CONFIG_MATCH', l_token);
1944 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1945
1946 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1947
1948 OE_STANDARD_WF.Save_Messages;
1949 OE_STANDARD_WF.Clear_Msg_Context;
1950
1951 end if ;
1952
1953 x_reserve_status := 'MATCH' ;
1954
1955 end if ; /* check for full match */
1956
1957
1958 IF PG_DEBUG <> 0 THEN
1959 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1960 END IF;
1961
1962
1963 EXCEPTION
1964 when RESERVATION_ERROR then
1965 --rollback to savepoint before_item_creation;
1966 IF PG_DEBUG <> 0 THEN
1967 OE_DEBUG_PUB.add('create_reservation: ' || 'RESERVATION_ERROR ' , 1 );
1968
1969 OE_DEBUG_PUB.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation ' || to_char(l_stmt_num) );
1970 END IF;
1971 OE_STANDARD_WF.Save_Messages;
1972 OE_STANDARD_WF.Clear_Msg_Context;
1973
1974 x_return_status := FND_API.G_RET_STS_ERROR ;
1975
1976
1977 when NO_DATA_FOUND then
1978 --rollback to savepoint before_item_creation;
1979 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ITEM_ERROR');
1980 IF PG_DEBUG <> 0 THEN
1981 oe_debug_pub.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation::ndf::' || to_char(l_stmt_num) );
1982 END IF;
1983 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1984
1985
1986 when PROCESS_ERROR then
1987 --rollback to savepoint before_item_creation;
1988
1989 IF PG_DEBUG <> 0 THEN
1990 OE_DEBUG_PUB.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation ' || to_char(l_stmt_num) );
1991 END IF;
1992
1993 OE_STANDARD_WF.Save_Messages;
1994 OE_STANDARD_WF.Clear_Msg_Context;
1995
1996 x_return_status := FND_API.G_RET_STS_ERROR ;
1997
1998 when FND_API.G_EXC_UNEXPECTED_ERROR then
1999 --rollback to savepoint before_item_creation;
2000 IF PG_DEBUG <> 0 THEN
2001 OE_DEBUG_PUB.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation ' ||
2002 to_char(l_stmt_num) || ':' ||
2003 l_x_error_msg);
2004 END IF;
2005 OE_STANDARD_WF.Save_Messages;
2006 OE_STANDARD_WF.Clear_Msg_Context;
2007
2008 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2009
2010 when OTHERS then
2011 --rollback to savepoint before_item_creation;
2012 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ITEM_ERROR');
2013 IF PG_DEBUG <> 0 THEN
2014 oe_debug_pub.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation' ||
2015 to_char(l_stmt_num) || ':' ||
2016 substrb(sqlerrm, 1, 100));
2017 END IF;
2018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2019
2020
2021 END CREATE_RESERVATION ;
2022
2023
2024
2025
2026 /*============================================================================
2027 Procedure: rsv_before_booking_wf
2028 Description: This works only for an ATO item .
2029 This procedure gets called just before "Create supply order Eligble"
2030 activity in the ATO workflow.
2031 The format follows the standard Workflow API format.
2032
2033 Parameters:
2034 =============================================================================*/
2035
2036 PROCEDURE rsv_before_booking_wf (
2037 p_itemtype in VARCHAR2, /* item type */
2038 p_itemkey in VARCHAR2, /* config line id */
2039 p_actid in NUMBER, /* ID number of WF activity */
2040 p_funcmode in VARCHAR2, /* execution mode of WF activity */
2041 x_result out NoCopy VARCHAR2 /* result of activity */
2042 )
2043 IS
2044
2045 l_stmt_num NUMBER;
2046 l_ResultStatus boolean;
2047 l_msg_count number;
2048 l_msg_data varchar2(2000);
2049 l_return_status varchar2(1);
2050 return_value INTEGER;
2051
2052 --start bug#1861812
2053
2054 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2055 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2056
2057
2058 v_header_id oe_order_lines_all.header_id%type ;
2059 v_config_line_id oe_order_lines_all.line_id%type ;
2060
2061
2062 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
2063 l_hold_release_rec OE_Holds_PVT.Hold_release_REC_type;
2064
2065 l_x_hold_result_out Varchar2(30);
2066 l_x_hold_return_status Varchar2(30);
2067 l_x_error_msg_count Number;
2068 l_x_error_msg Varchar2(2000);
2069
2070 v_aps_version number ;
2071 BEGIN
2072 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2073 IF PG_DEBUG <> 0 THEN
2074 oe_debug_pub.add('rsv_before_booking_wf: ' || 'CTO Activity: Check Reservation before booking activity ', 1);
2075 END IF;
2076
2077 if (p_funcmode = 'RUN') then
2078
2079
2080 --
2081 -- start bugfix 1861812
2082 --
2083 l_stmt_num := 98;
2084 SELECT item_type_code, ato_line_id , header_id, line_id
2085 INTO v_item_type_code, v_ato_line_id, v_header_id , v_config_line_id
2086 FROM oe_order_lines_all
2087 WHERE line_id = p_itemkey;
2088
2089
2090
2091
2092
2093 /* Check for Activity Hold and convert it to regular hold
2094 ** Check where create_supply hold exists on the config line. Remove the create_supply hold
2095 ** apply regular AutoCreate Config Exception Hold.
2096 */
2097
2098
2099
2100
2101
2102
2103
2104 v_aps_version := msc_atp_global.get_aps_version ;
2105
2106 oe_debug_pub.add('link_config: ' || 'APS version::'|| v_aps_version , 2);
2107
2108 if( v_aps_version = 10 ) then
2109
2110
2111 oe_debug_pub.add( '*************************CHECKING HOLDS IN CHECK_RESERVATION_BEFORE_BOOKING ACTIVITY************ ' , 1) ;
2112
2113
2114
2115 OE_HOLDS_PUB.Check_Holds (
2116 p_api_version => 1.0
2117 ,p_line_id => v_config_line_id
2118 ,p_hold_id => 61
2119 ,p_wf_item => 'OEOL'
2120 ,p_wf_activity => 'CREATE_SUPPLY'
2121 ,p_chk_act_hold_only => 'Y'
2122 ,x_result_out => l_x_hold_result_out
2123 ,x_return_status => l_x_hold_return_status
2124 ,x_msg_count => l_x_error_msg_count
2125 ,x_msg_data => l_x_error_msg);
2126
2127 IF (l_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
2128 IF PG_DEBUG <> 0 THEN
2129 oe_debug_pub.add('CTOCITMB:Failed in Check Holds with expected error.' ,1);
2130 END IF;
2131 raise FND_API.G_EXC_ERROR;
2132
2133 ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2134 IF PG_DEBUG <> 0 THEN
2135 oe_debug_pub.add('CTOCITMB:Failed in Check Holds with unexpected error.' ,1);
2136 END IF;
2137 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2138
2139 ELSE
2140 IF PG_DEBUG <> 0 THEN
2141 oe_debug_pub.add('CTOCITMB:Success in Check Holds.' ,1);
2142 END IF;
2143
2144 if l_x_hold_result_out = FND_API.G_TRUE then
2145 IF PG_DEBUG <> 0 THEN
2146 oe_debug_pub.add('CTOCITMB:Create Supply Activity Hold exists on Config Line .' ,1);
2147 END IF;
2148
2149 l_hold_source_rec.hold_entity_code := 'O';
2150 l_hold_source_rec.hold_id := 61 ;
2151 l_hold_source_rec.hold_entity_id := v_header_id;
2152 l_hold_source_rec.header_id := v_header_id;
2153 l_hold_source_rec.line_id := v_config_line_id ;
2154
2155 l_hold_release_rec.release_reason_code :='CTO_AUTOMATIC';
2156 --set created_by = 1 to indicate automatic hold release
2157 l_hold_release_rec.created_by := 1;
2158
2159
2160 OE_HOLDS_PUB.Release_Holds (
2161 p_api_version => 1.0
2162 -- ,p_line_id => v_config_line_id
2163 -- ,p_hold_id => 1063
2164 ,p_hold_source_rec => l_hold_source_rec
2165 ,p_hold_release_rec => l_hold_release_rec
2166 ,x_return_status => l_x_hold_return_status
2167 ,x_msg_count => l_x_error_msg_count
2168 ,x_msg_data => l_x_error_msg);
2169
2170 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2171 IF PG_DEBUG <> 0 THEN
2172 oe_debug_pub.add('CTOCITMB:Failed in Release Holds with expected error.' ,1);
2173 END IF;
2174 raise FND_API.G_EXC_ERROR;
2175
2176 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2177 IF PG_DEBUG <> 0 THEN
2178 oe_debug_pub.add('CTOCITMB:Failed in Release Holds with unexpected error.' ,1);
2179 END IF;
2180 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2181 END IF;
2182
2183 IF PG_DEBUG <> 0 THEN
2184 oe_debug_pub.add('CTOCITMB: Hold Released on config line.' ,1);
2185 END IF;
2186
2187
2188
2189
2190 IF PG_DEBUG <> 0 THEN
2191 oe_debug_pub.add('CTOCITMB:Calling OM api to apply hold.' ,1);
2192 END IF;
2193
2194 l_hold_source_rec.hold_entity_code := 'O';
2195 l_hold_source_rec.hold_id := 55 ;
2196 l_hold_source_rec.hold_entity_id := v_header_id;
2197 l_hold_source_rec.header_id := v_header_id;
2198 l_hold_source_rec.line_id := v_config_line_id;
2199
2200 OE_Holds_PUB.Apply_Holds (
2201 p_api_version => 1.0
2202 , p_hold_source_rec => l_hold_source_rec
2203 , x_return_status => l_return_status
2204 , x_msg_count => l_msg_count
2205 , x_msg_data => l_msg_data);
2206
2207 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2208 IF PG_DEBUG <> 0 THEN
2209 oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with expected error.' ,1);
2210 END IF;
2211 raise FND_API.G_EXC_ERROR;
2212
2213 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2214 IF PG_DEBUG <> 0 THEN
2215 oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with unexpected error.' ,1);
2216 END IF;
2217 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2218 END IF;
2219
2220 IF PG_DEBUG <> 0 THEN
2221 oe_debug_pub.add('CTOCITMB: An Exception Hold applied to config line.' ,1);
2222 END IF;
2223
2224 else
2225
2226
2227 IF PG_DEBUG <> 0 THEN
2228 oe_debug_pub.add('CTOCITMB:Create Supply Activity Hold does not exist on Config Line .' ,1);
2229 END IF;
2230
2231 end if; /* if activity hold exists */
2232
2233
2234 END IF ; /* success in check holds */
2235
2236 end if ; /* check for aps version */
2237
2238
2239 --if an ato item
2240
2241 IF ((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION')) --bug#1874380
2242 AND (v_ato_line_id = p_itemkey)
2243 THEN
2244
2245 --end bug#1861812
2246
2247 /*-------------------------------------------------------------+
2248 1.call procedure check_reservation_exists_ato_item to see
2249 if the reservation exists before "create supply order eligible" activity.
2250 2.If the reservation exists , the work flow goes to "Ship_line" status
2251 3.If reservation does not exists then the workflow goes to "Create
2252 Supply Order Eligible" status.
2253
2254 +--------------------------------------------------------------*/
2255 l_stmt_num := 99;
2256 /* Bugfix 3075105: Instead of check_inv_rsv_exists, call check_rsv_exists
2257 to check all reservations. If a reservation is found, progress thru
2258 the Reserved path.
2259
2260 check_inv_rsv_exists(to_number(p_itemkey),
2261 l_ResultStatus,
2262 l_msg_count,
2263 l_msg_data,
2264 l_return_status);
2265 **/
2266 -- Bugfix 3075105 begin
2267 check_rsv_exists(to_number(p_itemkey),
2268 l_ResultStatus,
2269 l_msg_count,
2270 l_msg_data,
2271 l_return_status);
2272 -- Bugfix 3075105 end
2273
2274 if ((l_ResultStatus=TRUE) and (l_return_status=FND_API.G_RET_STS_SUCCESS)) then
2275
2276 IF PG_DEBUG <> 0 THEN
2277 oe_debug_pub.add('rsv_before_booking_wf: ' || 'Reservation exists, completing flow with reserved', 2);
2278 END IF;
2279 x_result :='COMPLETE:RESERVED';
2280
2281 --
2282 --below code calls display_wf_status to update the correct
2283 --before booking and scheduling if item is reserved
2284 --
2285 l_stmt_num := 100;
2286 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(to_number(p_itemkey));
2287
2288 if return_value <> 1 then
2289 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
2290 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2291 end if;
2292
2293 elsif ((l_ResultStatus=FALSE) and (l_return_status=FND_API.G_RET_STS_SUCCESS)) then
2294 IF PG_DEBUG <> 0 THEN
2295 oe_debug_pub.add('rsv_before_booking_wf: ' || 'Reservation does not exist, completing flow with complete.', 2);
2296 END IF;
2297 x_result :='COMPLETE';
2298
2299 elsif(l_return_status=FND_API.G_RET_STS_ERROR) then
2300 IF PG_DEBUG <> 0 THEN
2301 oe_debug_pub.add('rsv_before_booking_wf: ' || 'returning from check_rsv_exists with expected error.', 2);
2302 END IF;
2303 RAISE FND_API.G_EXC_ERROR;
2304
2305 elsif(l_return_status=FND_API.G_RET_STS_UNEXP_ERROR) then
2306 IF PG_DEBUG <> 0 THEN
2307 oe_debug_pub.add('rsv_before_booking_wf: ' || 'returning from check_rsv_exists with unexpected error.', 2);
2308 END IF;
2309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2310
2311 end if;
2312 --start bug#1861812
2313
2314 --
2315 --if not an ato item complete with default flow
2316 --
2317 ELSE
2318 x_result :='COMPLETE';
2319 END IF;
2320 --end bug#1861812
2321
2322 end if ; /*p_funcmode ='RUN"*/
2323
2324 OE_STANDARD_WF.Save_Messages;
2325 OE_STANDARD_WF.Clear_Msg_Context;
2326
2327 EXCEPTION
2328 when FND_API.G_EXC_ERROR then
2329 IF PG_DEBUG <> 0 THEN
2330 OE_DEBUG_PUB.add('rsv_before_booking_wf: ' || 'CTO_WORKFLOW.rsv_before_booking_wf ' || to_char(l_stmt_num) );
2331 END IF;
2332 OE_STANDARD_WF.Save_Messages;
2333 OE_STANDARD_WF.Clear_Msg_Context;
2334 raise; -- can be re-tried
2335
2336 when FND_API.G_EXC_UNEXPECTED_ERROR then
2337 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2338 FND_MSG_PUB.Add_Exc_Msg
2339 (G_PKG_NAME
2340 ,'rsv_before_booking_wf'
2341 );
2342 end if;
2343
2344 IF PG_DEBUG <> 0 THEN
2345 oe_debug_pub.add('rsv_before_booking_wf: ' || 'corresponds to unexpected error at called program check_inv_rsv_exists '||'
2346 l_stmt_num :'|| l_stmt_num ||sqlerrm, 1);
2347 END IF;
2348 OE_STANDARD_WF.Save_Messages;
2349 OE_STANDARD_WF.Clear_Msg_Context;
2350 wf_core.context('CTO_WORKFLOW', 'rsv_before_booking_wf', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2351 raise;
2352
2353 when OTHERS then
2354 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2355 FND_MSG_PUB.Add_Exc_Msg
2356 (G_PKG_NAME
2357 ,'rsv_before_booking_wf'
2358 );
2359 end if;
2360 IF PG_DEBUG <> 0 THEN
2361 oe_debug_pub.add('rsv_before_booking_wf: ' || 'error at rsv_before_booking_wf ' || to_char(l_stmt_num)|| sqlerrm);
2362 END IF;
2363 /*-------------------------------------------+
2364 Error Information for Notification.
2365 +--------------------------------------------*/
2366 wf_core.context('CTO_WORKFLOW','rsv_before_booking_wf',p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2367 raise;
2368
2369 END rsv_before_booking_wf;
2370
2371
2372
2373
2374 /*============================================================================
2375 Procedure: check_inv_rsv_exists
2376 Description:
2377 This procedure is being called from rsv_before_booking_wf API.
2378 This checks if inventory reservations exist for an
2379 ATO item before booking
2380 ===========================================================================*/
2381 PROCEDURE check_inv_rsv_exists
2382 (
2383 pLineId in number ,
2384 x_ResultStatus out NoCopy boolean ,
2385 x_msg_count out NoCopy number ,
2386 x_msg_data out NoCopy varchar2,
2387 x_return_status out NoCopy varchar2
2388 )
2389
2390 is
2391
2392 lReserveId number;
2393
2394 BEGIN
2395
2396 select reservation_id
2397 into lReserveId
2398 from mtl_reservations mr,
2399 oe_order_lines_all oel,
2400 oe_order_headers_all oeh,
2401 oe_transaction_types_all ota,
2402 oe_transaction_types_tl otl,
2403 mtl_sales_orders mso
2404 where mr.demand_source_line_id = oel.line_id --ato item line id
2405 and oel.line_id = pLineId
2406 and oeh.header_id = oel.header_id
2407 and oeh.order_type_id = ota.transaction_type_id
2408 and ota.transaction_type_code='ORDER'
2409 and ota.transaction_type_id = otl.transaction_type_id
2410 and oeh.order_number = mso.segment1
2411 and otl.name = mso.segment2
2412 and otl.language = (select language_code
2413 from fnd_languages
2414 where installed_flag = 'B')
2415 and mso.sales_order_id = mr.demand_source_header_id
2416 --and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
2417 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
2418 INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
2419 INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
2420 and mr.reservation_quantity > 0
2421 and supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_inv
2422 and rownum = 1;
2423
2424 IF PG_DEBUG <> 0 THEN
2425 oe_debug_pub.add ('check_inv_rsv_exists: ' || 'found that reservation exists before booking', 1);
2426 END IF;
2427 x_ResultStatus := TRUE;
2428 x_return_status := FND_API.G_RET_STS_SUCCESS;
2429
2430 EXCEPTION
2431
2432 when no_data_found then
2433 x_ResultStatus := FALSE;
2434 x_return_status := FND_API.G_RET_STS_SUCCESS;
2435 IF PG_DEBUG <> 0 THEN
2436 oe_debug_pub.add ('check_inv_rsv_exists: ' || 'no reservations before booking, this is not an error', 1);
2437 END IF;
2438
2439 when others then
2440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2441 IF PG_DEBUG <> 0 THEN
2442 oe_debug_pub.add ('check_inv_rsv_exists: ' || 'unexpected error in called program check_inv_rsv_exists'|| sqlerrm , 1);
2443 END IF;
2444 if fnd_msg_pub.check_msg_level
2445 (fnd_msg_pub.g_msg_lvl_unexp_error)
2446 then
2447 fnd_msg_pub.Add_Exc_msg
2448 ( 'CTO_WORKFLOW',
2449 'check_inv_rsv_exists'
2450 );
2451 end if;
2452 cto_msg_pub.count_and_get
2453 (
2454 p_msg_count=>x_msg_count,
2455 p_msg_data=>x_msg_data
2456 );
2457 end check_inv_rsv_exists;
2458
2459
2460 --begin bugfix 3075105
2461 /*============================================================================
2462 Procedure: check_rsv_exists
2463 Description:
2464 This procedure is being called from rsv_before_booking_wf API.
2465 This checks if inventory reservations exist for an
2466 ATO item before booking
2467 ===========================================================================*/
2468 PROCEDURE check_rsv_exists
2469 (
2470 pLineId in number ,
2471 x_ResultStatus out NoCopy boolean ,
2472 x_msg_count out NoCopy number ,
2473 x_msg_data out NoCopy varchar2,
2474 x_return_status out NoCopy varchar2
2475 )
2476
2477 is
2478
2479 lRsvCount number := 0;
2480 lFloCount number := 0;
2481
2482 BEGIN
2483
2484 select count(*)
2485 into lRsvCount
2486 from mtl_reservations mr,
2487 oe_order_lines_all oel,
2488 oe_order_headers_all oeh,
2489 oe_transaction_types_all ota,
2490 oe_transaction_types_tl otl,
2491 mtl_sales_orders mso
2492 where mr.demand_source_line_id = oel.line_id --ato item line id
2493 and oel.line_id = pLineId
2494 and oeh.header_id = oel.header_id
2495 and oeh.order_type_id = ota.transaction_type_id
2496 and ota.transaction_type_code='ORDER'
2497 and ota.transaction_type_id = otl.transaction_type_id
2498 and oeh.order_number = mso.segment1
2499 and otl.name = mso.segment2
2500 and otl.language = (select language_code
2501 from fnd_languages
2502 where installed_flag = 'B')
2503 and mso.sales_order_id = mr.demand_source_header_id
2504 --and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
2505 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
2506 INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
2507 INV_RESERVATION_GLOBAL.g_source_type_oe)
2508 and mr.reservation_quantity > 0
2509 and rownum = 1;
2510
2511
2512 if lRsvCount = 0 then
2513 -- Check to see if reservns exist in wip_flow_schedules
2514 select count(*)
2515 into lFloCount
2516 from wip_flow_schedules
2517 where demand_source_type = inv_reservation_global.g_source_type_oe
2518 and demand_source_line = to_char(pLineId)
2519 and status <> 2; -- Flow Schedule status : 1 = Open 2 = Closed/Completed
2520 else
2521 x_ResultStatus := TRUE;
2522 x_return_status := FND_API.G_RET_STS_SUCCESS;
2523 IF PG_DEBUG <> 0 THEN
2524 oe_debug_pub.add ('check_rsv_exists: ' || 'MTL reservation exists before booking', 1);
2525 END IF;
2526 return;
2527 end if;
2528
2529 if lFloCount > 0 then
2530 x_ResultStatus := TRUE;
2531 x_return_status := FND_API.G_RET_STS_SUCCESS;
2532 IF PG_DEBUG <> 0 THEN
2533 oe_debug_pub.add ('check_rsv_exists: ' || 'FLOW reservation exists before booking', 1);
2534 END IF;
2535 else
2536 x_ResultStatus := FALSE;
2537 x_return_status := FND_API.G_RET_STS_SUCCESS;
2538 IF PG_DEBUG <> 0 THEN
2539 oe_debug_pub.add ('check_rsv_exists: ' || 'NO reservations before booking, this is not an error', 1);
2540 END IF;
2541 end if;
2542
2543 EXCEPTION
2544
2545 when no_data_found then
2546 x_ResultStatus := FALSE;
2547 x_return_status := FND_API.G_RET_STS_SUCCESS;
2548 IF PG_DEBUG <> 0 THEN
2549 oe_debug_pub.add ('check_rsv_exists: ' || 'no reservations before booking, this is not an error', 1);
2550 END IF;
2551
2552 when others then
2553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2554 IF PG_DEBUG <> 0 THEN
2555 oe_debug_pub.add ('check_rsv_exists: ' || 'unexpected error in called program check_inv_rsv_exists'|| sqlerrm , 1);
2556 END IF;
2557 if fnd_msg_pub.check_msg_level
2558 (fnd_msg_pub.g_msg_lvl_unexp_error)
2559 then
2560 fnd_msg_pub.Add_Exc_msg
2561 ( 'CTO_WORKFLOW',
2562 'check_inv_rsv_exists'
2563 );
2564 end if;
2565 cto_msg_pub.count_and_get
2566 (
2567 p_msg_count=>x_msg_count,
2568 p_msg_data=>x_msg_data
2569 );
2570 end check_rsv_exists;
2571
2572 --end bugfix 3075105
2573
2574 /*============================================================================
2575 obsolete ?
2576 Procedure: check_reservation_status_wf
2577 Description: This procedure gets called when executing the
2578 Check Reservation activity in the ATO workflow. The
2579 format is follows the standard Workflow API format.
2580
2581 More to come...
2582 Parameters:
2583 =============================================================================*/
2584 PROCEDURE check_reservation_status_wf(
2585 p_itemtype in VARCHAR2, /* item type */
2586 p_itemkey in VARCHAR2, /* config line id */
2587 p_actid in number, /* ID number of WF activity */
2588 p_funcmode in VARCHAR2, /* execution mode of WF activity */
2589 x_result out NoCopy VARCHAR2 /* result of activity */
2590 )
2591 IS
2592
2593 l_stmt_num NUMBER;
2594 l_mfg_org_id NUMBER;
2595 l_config_item_id NUMBER;
2596 l_x_bill_seq_id NUMBER;
2597 l_status INTEGER;
2598 l_return_status VARCHAR2(1);
2599 l_header_id NUMBER;
2600 l_flow_status_code VARCHAR2(30);
2601 l_reserved_qty NUMBER;
2602 l_qty NUMBER;
2603
2604 PROCESS_ERROR EXCEPTION;
2605 UNEXP_ERROR EXCEPTION;
2606 BEGIN
2607 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2608 IF PG_DEBUG <> 0 THEN
2609 oe_debug_pub.add('check_reservation_status_wf: ' || 'CTO Activity: Check Reservation', 1);
2610 END IF;
2611
2612 if (p_funcmode = 'RUN') then
2613
2614 /*-------------------------------------------------------------+
2615 Check the status of the configuration line.
2616 2. If the Config BOM exists for this configuration
2617 item. This can happen if a match was performed.
2618 If the BOM exists, the workflow goes to "Create
2619 Supply Order Eligible" status.
2620 3. Otherwise, workflow goes to Create Mfg Config Data Eligible.
2621 +--------------------------------------------------------------*/
2622 l_stmt_num := 50;
2623 select oel.inventory_item_id, oel.ship_from_org_id,
2624 oel.header_id,
2625 --(oel.ordered_quantity - oel.cancelled_quantity) --bugfix 2017099
2626 oel.ordered_quantity
2627 into l_config_item_id, l_mfg_org_id, l_header_id,
2628 l_qty
2629 from oe_order_lines_all oel
2630 where oel.line_id = to_number(p_itemkey);
2631
2632 /*------------------------------------+
2633 Check if Config BOM exists.
2634 +------------------------------------*/
2635
2636 l_stmt_num := 110;
2637 IF PG_DEBUG <> 0 THEN
2638 oe_debug_pub.add('check_reservation_status_wf: ' || 'Check For Config BOM ', 1);
2639 END IF;
2640
2641 l_status := CTO_CONFIG_BOM_PK.check_bom(pItemId => l_config_item_id,
2642 pOrgId => l_mfg_org_id,
2643 xBillId => l_x_bill_seq_id);
2644
2645 IF (l_status = 1) THEN
2646 IF PG_DEBUG <> 0 THEN
2647 oe_debug_pub.add('check_reservation_status_wf: ' || 'Config Data Created.', 1);
2648 END IF;
2649 x_result := 'COMPLETE:CREATED';
2650 l_flow_status_code := 'BOM_AND_RTG_CREATED';
2651
2652 ELSE
2653
2654 l_stmt_num := 130;
2655 IF PG_DEBUG <> 0 THEN
2656 oe_debug_pub.add('check_reservation_status_wf: ' || 'Config Data Not Created.',1);
2657 END IF;
2658 x_result := 'COMPLETE';
2659 l_flow_status_code := 'ITEM_CREATED';
2660
2661 END IF;
2662
2663
2664 --
2665 -- It was agreed with OM that if we cannot get a lock
2666 -- on this line for update, we will not error out.
2667 --
2668
2669 l_stmt_num := 140;
2670 IF PG_DEBUG <> 0 THEN
2671 oe_debug_pub.add('check_reservation_status_wf: ' || 'Calling flow status API ',1);
2672 END IF;
2673 OE_Order_WF_Util.Update_Flow_Status_Code(
2674 p_header_id => l_header_id,
2675 p_line_id => to_number(p_itemkey),
2676 p_flow_status_code => l_flow_status_code,
2677 x_return_status => l_return_status);
2678
2679 IF PG_DEBUG <> 0 THEN
2680 oe_debug_pub.add('check_reservation_status_wf: ' || 'Return from flow status API '
2681 ||l_return_status,1);
2682 END IF;
2683
2684 end if; /* p_funcmode = 'RUN' */
2685
2686 OE_STANDARD_WF.Save_Messages;
2687 OE_STANDARD_WF.Clear_Msg_Context;
2688
2689 EXCEPTION
2690 when NO_DATA_FOUND then
2691 IF PG_DEBUG <> 0 THEN
2692 oe_debug_pub.add('check_reservation_status_wf: ' || 'Configuration Line Not Reserved.', 1);
2693 END IF;
2694 OE_STANDARD_WF.Save_Messages;
2695 OE_STANDARD_WF.Clear_Msg_Context;
2696 x_result := 'COMPLETE';
2697
2698 when PROCESS_ERROR then
2699 IF PG_DEBUG <> 0 THEN
2700 OE_DEBUG_PUB.add('check_reservation_status_wf: ' || 'CTO_WORKFLOW.check_reservation_status_wf ' ||
2701 to_char(l_stmt_num) || ':' ||
2702 substrb(sqlerrm, 1, 100));
2703 END IF;
2704 OE_STANDARD_WF.Save_Messages;
2705 OE_STANDARD_WF.Clear_Msg_Context;
2706
2707 when UNEXP_ERROR then
2708 IF PG_DEBUG <> 0 THEN
2709 OE_DEBUG_PUB.add('check_reservation_status_wf: ' || 'CTO_WORKFLOW.create_config_item_wf ' ||
2710 to_char(l_stmt_num) || ':' ||
2711 l_return_status);
2712 END IF;
2713 OE_STANDARD_WF.Save_Messages;
2714 OE_STANDARD_WF.Clear_Msg_Context;
2715 wf_core.context('CTO_WORKFLOW', 'create_config_item_wf',
2716 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2717 raise;
2718
2719 when OTHERS then
2720 cto_msg_pub.cto_message('BOM', 'CTO_CHECK_STATUS_ERROR');
2721 IF PG_DEBUG <> 0 THEN
2722 oe_debug_pub.add('check_reservation_status_wf: ' || 'CTO_WORKFLOW.check_reservation_status_wf ' ||
2723 to_char(l_stmt_num) || ':' ||
2724 substrb(sqlerrm, 1, 100));
2725 END IF;
2726 /*-------------------------------------------+
2727 Error Information for Notification.
2728 +--------------------------------------------*/
2729 wf_core.context('CTO_WORKFLOW', 'check_reservation_status_wf',
2730 p_itemtype, p_itemkey, to_char(p_actid),
2731 p_funcmode);
2732 raise;
2733 END check_reservation_status_wf;
2734
2735
2736
2737 --
2738 -- Procedure for multilevel testing
2739 -- To be renamed after tested completely
2740 --
2741
2742 PROCEDURE calculate_cost_rollup_wf_ml(
2743 p_itemtype in VARCHAR2, /*item type */
2744 p_itemkey in VARCHAR2, /* config line id */
2745 p_actid in number, /* ID number of WF activity */
2746 p_funcmode in VARCHAR2, /* execution mode of WF activity*/
2747 x_result out NoCopy VARCHAR2 /* result of activity */
2748 )
2749 IS
2750 l_stmt_num number := 0;
2751 l_x_msg_count number;
2752 l_x_msg_data varchar2(2000);
2753 l_top_ato_line_id number;
2754 l_status integer;
2755 UNEXP_ERROR exception;
2756
2757 BEGIN
2758 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2759 IF PG_DEBUG <> 0 THEN
2760 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'CTO Activity: Calculate Cost Rollup', 1);
2761 END IF;
2762
2763 if (p_funcmode = 'RUN') then
2764
2765 l_stmt_num := 135;
2766
2767 select oel.ato_line_id
2768 into l_top_ato_line_id
2769 from oe_order_lines_all oel
2770 where oel.line_id = to_number(p_itemkey);
2771
2772 IF PG_DEBUG <> 0 THEN
2773 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Cost rollup::top_ato_line_id::'||to_char(l_top_ato_line_id));
2774 END IF;
2775
2776 l_stmt_num := 140;
2777 l_status := CTO_CONFIG_COST_PK.cost_rollup_ml(
2778 pTopAtoLineId => l_top_ato_line_id,
2779 x_msg_count => l_x_msg_count,
2780 x_msg_data => l_x_msg_data);
2781
2782 if (l_status = 0) then
2783 IF PG_DEBUG <> 0 THEN
2784 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Failure in cost_rollup ', 1);
2785 END IF;
2786 cto_msg_pub.cto_message('BOM', l_x_msg_data);
2787 raise UNEXP_ERROR;
2788 else
2789 IF PG_DEBUG <> 0 THEN
2790 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Success in cost_rollup ', 1);
2791 END IF;
2792 end if;
2793
2794 x_result := 'COMPLETE';
2795
2796 end if; /* end p_funcmode = 'RUN' */
2797
2798 OE_STANDARD_WF.Save_Messages;
2799 OE_STANDARD_WF.Clear_Msg_Context;
2800
2801 EXCEPTION
2802 when UNEXP_ERROR then
2803 IF PG_DEBUG <> 0 THEN
2804 OE_DEBUG_PUB.add('calculate_cost_rollup_wf_ml: ' || 'CTO_WORKFLOW.calculate_cost_rollup_wf' ||
2805 to_char(l_stmt_num) || ':' ||
2806 l_x_msg_data);
2807 END IF;
2808 OE_STANDARD_WF.Save_Messages;
2809 OE_STANDARD_WF.Clear_Msg_Context;
2810 wf_core.context('CTO_WORKFLOW', 'calculate_cost_rollup_wf',
2811 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2812 raise;
2813
2814 when NO_DATA_FOUND then
2815 IF PG_DEBUG <> 0 THEN
2816 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'CTO_WORKFLOW.calculate_cost_rollup_wf ' ||
2817 to_char(l_stmt_num) || ':' ||
2818 substrb(sqlerrm, 1, 100),1);
2819 END IF;
2820 cto_msg_pub.cto_message('BOM', 'CTO_CALC_COST_ROLLUP_ERROR');
2821 OE_STANDARD_WF.Save_Messages;
2822 OE_STANDARD_WF.Clear_Msg_Context;
2823
2824 when OTHERS then
2825 cto_msg_pub.cto_message('BOM', 'CTO_CALC_COST_ROLLUP_ERROR');
2826 IF PG_DEBUG <> 0 THEN
2827 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'CTO_WORKFLOW.calculate_cost_rollup_wf ' ||
2828 to_char(l_stmt_num) || ':' ||
2829 substrb(sqlerrm, 1, 100),1);
2830 END IF;
2831 wf_core.context('CTO_WORKFLOW', 'calculate_cost_rollup_wf',
2832 p_itemtype, p_itemkey, to_char(p_actid),
2833 p_funcmode);
2834 raise;
2835
2836 END calculate_cost_rollup_wf_ml;
2837
2838
2839
2840
2841 /*============================================================================
2842 Procedure: set_parameter_lead_time_wf_ml
2843 Description: This procedure gets called when executing the Calculate
2844 Leadtime activity in the ATO workflow. The
2845 format is follows the standard Workflow API format.
2846
2847 More to come...
2848 Parameters:
2849 =============================================================================*/
2850 PROCEDURE set_parameter_lead_time_wf_ml(
2851 p_itemtype in VARCHAR2, /* workflow item type */
2852 p_itemkey in VARCHAR2, /* sales order line id */
2853 p_actid in number, /* ID number of WF activity */
2854 p_funcmode in VARCHAR2, /* execution mode of WF activity */
2855 x_result out NoCopy VARCHAR2 /* result of activity */
2856 )
2857 IS
2858
2859 l_stmt_num number := 0;
2860 l_config_item_id number;
2861 l_mfg_org_code varchar2(3);
2862 l_mfg_org_id number;
2863 l_item_name varchar2(40);
2864 l_x_error_msg_name varchar2(30);
2865 l_x_error_msg varchar2(2000);
2866 l_routing_count number;
2867 l_x_rtg_id number;
2868 l_x_rtg_type number;
2869
2870
2871 /* Variables for the Workflow Item Attributes */
2872 l_req_id number;
2873 lAtoLineId number;
2874
2875 l_status integer;
2876 l_x_error_msg_count number;
2877 l_x_hold_result_out varchar2(1);
2878 l_x_hold_return_status varchar2(1);
2879
2880 UNEXP_ERROR exception;
2881
2882 BEGIN
2883
2884 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2885 IF PG_DEBUG <> 0 THEN
2886 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'CTO Activity: Set Parameter lead Time', 1);
2887 END IF;
2888
2889 if (p_funcmode = 'RUN') then
2890
2891 /*-----------------------------------------------------+
2892 Prepare to calculate. Launch Calculate Manufacturing
2893 Lead Time concurrent program. Set Item Attributes
2894 as Parameters to Calculate Mfg Lead Time concurrent prg.
2895 +----------------------------------------------------*/
2896
2897 /*----------------------------------------------+
2898 Assign Parameter Values to Parameters for
2899 Concurrent Program.
2900 +----------------------------------------------*/
2901 -- Line ID - We are using the Org parameter from
2902 -- the 11.5.1 workflow activity.
2903
2904 select oel.ato_line_id
2905 into lAtoLineId
2906 from oe_order_lines_all oel
2907 where line_id = to_number(p_itemkey);
2908
2909 --wf_engine.SetItemAttrText(p_itemtype, p_itemkey,
2910 -- 'LEAD_TIME_ROLLUP_ORG', p_itemkey);
2911 wf_engine.SetItemAttrText(p_itemtype, p_itemkey,
2912 'LEAD_TIME_ROLLUP_ORG', to_char(lAtoLineId));
2913
2914 IF PG_DEBUG <> 0 THEN
2915 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'Line ID: ' || p_itemkey, 1);
2916
2917 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'ATO Line ID: ' || to_char(lAtoLineId), 1);
2918 END IF;
2919
2920 x_result := 'COMPLETE';
2921 end if; /* p_funcmode = 'RUN' */
2922
2923 OE_STANDARD_WF.Save_Messages;
2924 OE_STANDARD_WF.Clear_Msg_Context;
2925
2926 EXCEPTION
2927
2928 when UNEXP_ERROR then
2929 IF PG_DEBUG <> 0 THEN
2930 OE_DEBUG_PUB.add('set_parameter_lead_time_wf_ml: ' || 'CTO_WORKFLOW.set_parameter_lead_time_wf_ml' ||
2931 to_char(l_stmt_num) || ':' ||
2932 l_x_error_msg);
2933 END IF;
2934 OE_STANDARD_WF.Save_Messages;
2935 OE_STANDARD_WF.Clear_Msg_Context;
2936 wf_core.context('CTO_WORKFLOW', 'set_parameter_lead_time_wf_ml',
2937 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2938 raise;
2939
2940 when NO_DATA_FOUND then
2941 IF PG_DEBUG <> 0 THEN
2942 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'CTO_WORKFLOW.set_parameter_lead_time_wf_ml'
2943 || to_char(l_stmt_num) || ':' ||
2944 substrb(sqlerrm, 1, 100),1);
2945 END IF;
2946 cto_msg_pub.cto_message('BOM', 'CTO_CALC_LEAD_TIME_ERROR');
2947 OE_STANDARD_WF.Save_Messages;
2948 OE_STANDARD_WF.Clear_Msg_Context;
2949
2950 when OTHERS then
2951 cto_msg_pub.cto_message('BOM', 'CTO_CALC_LEAD_TIME_ERROR');
2952 IF PG_DEBUG <> 0 THEN
2953 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'CTO_WORKFLOW.set_parameter_lead_time_wf_ml'
2954 || to_char(l_stmt_num) || ':' ||
2955 substrb(sqlerrm, 1, 100),1);
2956 END IF;
2957 wf_core.context('CTO_WORKFLOW', 'set_parameter_lead_time_wf_ml',
2958 p_itemtype, p_itemkey, to_char(p_actid),
2959 p_funcmode);
2960 raise;
2961
2962 END set_parameter_lead_time_wf_ml;
2963
2964
2965 /*============================================================================
2966 Procedure: check_supply_type_wf
2967 Description: This procedure gets called when executing the Reserve
2968 Configuration activity in the ATO workflow. The
2969 format is follows the standard Workflow API format.
2970
2971 More to come...
2972 Parameters:
2973 =============================================================================*/
2974
2975
2976
2977 -- The following procedure is modified by Renga Kannan on 08/29/01
2978 -- This procedure now looks at the Buy ATO item and Config item also.
2979
2980
2981 PROCEDURE check_supply_type_wf(
2982 p_itemtype in VARCHAR2, /*item type */
2983 p_itemkey in VARCHAR2, /* config line id */
2984 p_actid in number, /* ID number of WF activity */
2985 p_funcmode in VARCHAR2, /* execution mode of WF activity*/
2986 x_result out NoCopy VARCHAR2 /* result of activity */
2987 )
2988 IS
2989 l_stmt_num number := 0;
2990 l_supply_type number;
2991
2992 l_msg_name varchar2(30);
2993 l_msg_txt varchar2(2000);
2994 l_msg_count number;
2995 l_inventory_item_id Mtl_system_items.inventory_item_id%type;
2996 l_ship_from_org_id Mtl_system_items.organization_id%type;
2997 l_item_type_code Oe_order_lines_all.item_type_code%type;
2998 x_return_status Varchar2(1);
2999 P_source_type Number;
3000 p_sourcing_rule_exists Varchar2(1);
3001
3002 p_transit_lead_time Number;
3003 x_exp_error_code Number;
3004
3005 l_source_type_code oe_order_lines.source_type_code%type ;
3006
3007 --added by kkonada OPM
3008 l_can_create_supply VARCHAR2(1);
3009 l_return_status VARCHAR2(1);
3010 l_msg_data VARCHAR2(2000);
3011 l_sourcing_org number;
3012 l_message Varchar2(100);
3013
3014 l_ret_stat number; --bugfix 4556596
3015 v_x_error_msg_count NUMBER;
3016 v_x_hold_result_out VARCHAR2(1);
3017 v_x_hold_return_status VARCHAR2(1);
3018 v_x_error_msg VARCHAR2(150);
3019 BEGIN
3020 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3021 IF PG_DEBUG <> 0 THEN
3022 oe_debug_pub.add('check_supply_type_wf: ' || 'CTO Activity: Check Supply Type', 1);
3023
3024 oe_debug_pub.add('check_supply_type_wf: ' || 'Item key = '||p_itemkey,1);
3025
3026 oe_debug_pub.add('check_supply_type_wf: ' || 'Func Mode ='||p_funcmode,1);
3027 END IF;
3028
3029 if (p_funcmode = 'RUN') then
3030 l_stmt_num := 100;
3031
3032 /*
3033 ** BUG#2234858
3034 ** need to retrieve source type code
3035 */
3036 BEGIN
3037 select inventory_item_id, ship_from_org_id,item_type_code, source_type_code
3038 into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code
3039 from oe_order_lines_all
3040 where line_id = to_number(p_itemkey)
3041 and ato_line_id is not null;
3042 IF PG_DEBUG <> 0 THEN
3043 oe_debug_pub.add('check_supply_type_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);
3044
3045 oe_debug_pub.add('check_supply_type_wf: ' || 'Ship from org id ='||to_char(l_ship_from_org_id),1);
3046
3047 oe_debug_pub.add('check_supply_type_wf: ' || 'Item type code ='||l_item_type_code,1);
3048 END IF;
3049 EXCEPTION
3050 WHEN NO_DATA_FOUND THEN
3051 Null;
3052 END;
3053
3054 -- check for hold on the line.
3055 -- Bug fix 5261330
3056 -- Started checking for hold in this node
3057 -- As this node will allways get executed for all supply types
3058 -- It is more effecient to check in this node and remove the hold check
3059 -- from the respective supply creation nodes
3060
3061
3062 OE_HOLDS_PUB.Check_Holds(p_api_version => 1.0,
3063 p_line_id => to_number(p_itemkey),
3064 p_wf_item => 'OEOL',
3065 p_wf_activity => 'CREATE_SUPPLY',
3066 x_result_out => v_x_hold_result_out,
3067 x_return_status => v_x_hold_return_status,
3068 x_msg_count => v_x_error_msg_count,
3069 x_msg_data => v_x_error_msg);
3070
3071 IF (v_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
3072 IF PG_DEBUG <> 0 THEN
3073 oe_debug_pub.add('Check_supply_type_wf: ' || 'Expected error in Check Hold: ' || v_x_hold_return_status, 1);
3074 END IF;
3075 RAISE FND_API.G_EXC_ERROR;
3076
3077 ELSIF (v_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3078 IF PG_DEBUG <> 0 THEN
3079 oe_debug_pub.add('Check_supply_type_wf: ' || 'Unexp error in Check Hold ' || v_x_hold_return_status, 1);
3080 END IF;
3081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3082
3083 ELSE
3084 IF PG_DEBUG <> 0 THEN
3085 oe_debug_pub.add('Check_supply_type_wf: ' || 'Success in Check Hold ' || v_x_hold_return_status, 5);
3086 END IF;
3087
3088 IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
3089 IF PG_DEBUG <> 0 THEN
3090 oe_debug_pub.add('Check_supply_type_wf: ' || 'Order Line ID ' || p_itemkey || 'is on HOLD. ' ||v_x_hold_result_out, 1);
3091 END IF;
3092 cto_msg_pub.cto_message('BOM', 'CTO_ORDER_LINE_ON_HOLD');
3093 x_result := 'COMPLETE:INCOMPLETE';
3094 return;
3095 END IF;
3096 END IF;
3097
3098
3099
3100 /*
3101 ** BUG#2234858
3102 ** need to branch on source type for drop ship functionality
3103 */
3104 IF( l_source_type_code = 'EXTERNAL' )
3105 THEN
3106
3107 IF PG_DEBUG <> 0 THEN
3108 oe_debug_pub.add('check_supply_type_wf: ' || 'It is Config item Drop Ship case...',1);
3109 END IF;
3110 x_result := 'COMPLETE:DROPSHIP';
3111 OE_STANDARD_WF.Save_Messages;
3112 OE_STANDARD_WF.Clear_Msg_Context;
3113 return;
3114
3115
3116 END IF ;
3117
3118
3119
3120 -- get the sourcing type of the item in the specified organization.
3121 l_stmt_num := 200;
3122 -- Call the procedure to return the sourcing rule.
3123
3124 l_stmt_num := 200;
3125 --OPM
3126 --Check if Cto can create supply
3127 --query sourcing org is replaced with this new prcoedure
3128 --by KKONADA
3129 CTO_UTILITY_PK.check_cto_can_create_supply
3130 (
3131 P_config_item_id => l_inventory_item_id,
3132 P_org_id => l_ship_from_org_id,
3133 x_can_create_supply => l_can_create_supply,
3134 p_source_type => p_source_type,
3135 x_return_status => l_return_status,
3136 X_msg_count => l_msg_count,
3137 X_msg_data => l_msg_data,
3138 x_sourcing_org => l_sourcing_org,
3139 x_message => l_message
3140 );
3141
3142 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3143 IF PG_DEBUG <> 0 THEN
3144 oe_debug_pub.add('check_supply_type_wf: ' ||
3145 'Expected Error in check_cto_can_create_supply.',1);
3146 END IF;
3147 raise FND_API.G_EXC_ERROR;
3148
3149 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3150 IF PG_DEBUG <> 0 THEN
3151 oe_debug_pub.add('check_supply_type_wf: ' ||
3152 'Unexpected Error in check_cto_can_create_supply.',1);
3153 END IF;
3154 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3155
3156 END IF;
3157
3158 l_stmt_num := 210;
3159
3160 -- rkaza. ireq project. 05/02/2005.
3161 -- if CTO cannot create supply, let planning create the supply and
3162 -- move the workflow to ship line.
3163
3164 --Kiran Konada
3165 --If code flow is at this point , it means L-return_status was a SUCCESS
3166
3167 IF l_can_create_supply = 'N' THEN
3168 IF PG_DEBUG <> 0 THEN
3169
3170 oe_debug_pub.add('check_supply_type_wf: ' ||l_message,1);
3171
3172 END IF;
3173 x_result := 'COMPLETE:PLANNING';
3174
3175 l_stmt_num := 220;
3176
3177 --start bugfix 4556596
3178 IF PG_DEBUG <> 0 THEN
3179 oe_debug_pub.add('check_supply_type_wf: ' ||'calling display_wf_status with status',1);
3180 END IF;
3181
3182 l_ret_stat :=CTO_WORKFLOW_API_PK.display_wf_status
3183 (p_order_line_id=>p_itemkey
3184 );
3185
3186 IF l_ret_stat = 1 THEN
3187 oe_debug_pub.add('check_supply_type_wf: ' ||'call to display_wf_status success',1);
3188 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'call to display_wf_status success.');
3189
3190 Elsif l_ret_stat = 0 THEN
3191
3192 IF PG_DEBUG <> 0 THEN
3193
3194 oe_debug_pub.add('check_supply_type_wf: ' ||'call to display_wf_status failed',1);
3195 oe_debug_pub.add('check_supply_type_wf: ' ||'l_ret_stat=> '||l_ret_stat,1);
3196
3197 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'call to display_wf_status failed.');
3198 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'l_ret_stat=> '||l_ret_stat);
3199
3200 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3201 END IF;
3202 END IF;
3203
3204 --end bugfix 4556596
3205
3206 OE_STANDARD_WF.Save_Messages;
3207 OE_STANDARD_WF.Clear_Msg_Context;
3208 return;
3209 END IF;
3210
3211
3212
3213 l_stmt_num := 300;
3214
3215
3216 -- Modified by Renga Kannan on 02/06/02 for autocreate req for model
3217 -- rkaza. Use buy branch for 100% transfer rule case also.
3218
3219 IF p_source_type in (1, 3) THEN /* ATO Buy and IR cases */
3220 IF PG_DEBUG <> 0 THEN
3221 if p_source_type = 3 then
3222 oe_debug_pub.add('check_supply_type_wf: ' || 'It is ATO Buy case...',1);
3223 else
3224 oe_debug_pub.add('check_supply_type_wf: ' || 'It is ATO internal transfer case...',1);
3225 end if;
3226 END IF;
3227 x_result := 'COMPLETE:BUY';
3228 OE_STANDARD_WF.Save_Messages;
3229 OE_STANDARD_WF.Clear_Msg_Context;
3230 return;
3231 END IF;
3232
3233 l_stmt_num := 400;
3234
3235 select NVL(cfm_routing_flag,2)
3236 into l_supply_type
3237 from oe_order_lines_all oel,
3238 bom_operational_routings bor
3239 where oel.line_id = to_number(p_itemkey)
3240 and oel.inventory_item_id = bor.assembly_item_id (+)
3241 and oel.ship_from_org_id = bor.organization_id (+)
3242 and bor.alternate_routing_designator (+) is NULL;
3243
3244 --- Fixed bug 4197665
3245 --- replaced to_char(l_supply_type,1) with to_char(l_supply_type)
3246
3247 if (l_supply_type = 1) then
3248 -- Flow Schedule
3249 IF PG_DEBUG <> 0 THEN
3250 oe_debug_pub.add('check_supply_type_wf: ' || 'Routing Type is Flow Schedule. ' ||
3251 to_char (l_supply_type),1);
3252 END IF;
3253 x_result := 'COMPLETE:FLOW_SCHEDULE';
3254 else
3255 -- Discrete Job
3256 IF PG_DEBUG <> 0 THEN
3257 oe_debug_pub.add('check_supply_type_wf: ' || 'Routing Type is Discrete Job or No Routing. ' ||
3258 to_char (l_supply_type),1);
3259 END IF;
3260 x_result := 'COMPLETE:WORK_ORDER';
3261 end if;
3262
3263 IF PG_DEBUG <> 0 THEN
3264 oe_debug_pub.add('check_supply_type_wf: ' || 'Success in Check Supply Type', 1);
3265 END IF;
3266
3267 end if; /* end p_funcmode = 'RUN'*/
3268 OE_STANDARD_WF.Save_Messages;
3269 OE_STANDARD_WF.Clear_Msg_Context;
3270
3271 EXCEPTION
3272
3273 when FND_API.G_EXC_ERROR then
3274 IF PG_DEBUG <> 0 THEN
3275 OE_DEBUG_PUB.add('check_supply_type_wf: ' || 'CTO_WORKFLOW.check_supply_type_wf ' ||
3276 to_char(l_stmt_num));
3277 END IF;
3278 OE_STANDARD_WF.Save_Messages;
3279 OE_STANDARD_WF.Clear_Msg_Context;
3280 x_result := 'COMPLETE:INCOMPLETE';
3281
3282
3283 when FND_API.G_EXC_UNEXPECTED_ERROR then
3284 IF PG_DEBUG <> 0 THEN
3285 OE_DEBUG_PUB.add('check_supply_type_wf: ' || 'CTO_WORKFLOW.check_supply_type_wf ' ||
3286 to_char(l_stmt_num));
3287 END IF;
3288 OE_STANDARD_WF.Save_Messages;
3289 OE_STANDARD_WF.Clear_Msg_Context;
3290 wf_core.context('CTO_WORKFLOW', 'check_supply_type_wf',
3291 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3292 raise;
3293
3294 when OTHERS then
3295 IF PG_DEBUG <> 0 THEN
3296 oe_debug_pub.add('check_supply_type_wf: ' || 'CTO_WORKFLOW.check_supply_type_wf' ||
3297 to_char(l_stmt_num)||':'||sqlerrm); --bugfix 3136206
3298 END IF;
3299 wf_core.context('CTO_WORKFLOW', 'check_supply_type_wf',
3300 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3301
3302 OE_STANDARD_WF.Save_Messages;
3303 OE_STANDARD_WF.Clear_Msg_Context;
3304
3305 raise;
3306
3307 END check_supply_type_wf;
3308
3309
3310
3311
3312 /*============================================================================
3313 Procedure: create_flow_schedule__wf
3314 Description: This procedure gets called when executing the
3315 Create Flow Schedule activity in the CTO workflow.
3316
3317
3318 More to come...
3319 Parameters:
3320 =============================================================================*/
3321 PROCEDURE create_flow_schedule_wf(
3322 p_itemtype in VARCHAR2, /* workflow item type */
3323 p_itemkey in VARCHAR2, /* sales order line id */
3324 p_actid in number, /* ID number of WF activity */
3325 p_funcmode in VARCHAR2, /* execution mode of WF activity */
3326 x_result out NoCopy VARCHAR2 /* result of activity */
3327 )
3328 IS
3329
3330 l_stmt_num number := 0;
3331 l_msg_count number;
3332 l_msg_txt varchar2(2000);
3333 l_msg_name varchar2(60);
3334 l_quantity number := 0;
3335 l_sch_ship_date date;
3336 l_header_id number;
3337 l_ship_iface_flag varchar2(1);
3338 l_x_return_status varchar2(1);
3339 l_x_error_msg_count number;
3340 l_x_hold_result_out varchar2(1);
3341 l_x_hold_return_status varchar2(1);
3342 l_x_error_msg varchar2(150);
3343 l_source_document_type_id number; --bugfix 1799874
3344
3345
3346 BEGIN
3347 SAVEPOINT before_process;
3348
3349 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3350 IF PG_DEBUG <> 0 THEN
3351 oe_debug_pub.add('create_flow_schedule_wf: ' || 'CTO Activity: Create Flow Schedule', 1);
3352 END IF;
3353
3354 if (p_funcmode = 'RUN') then
3355 /*----------------------------------------------------------+
3356 Check order line status and check order line for holds.
3357 Do not process the order if status is invalid or
3358 if a hold is found.
3359 +-----------------------------------------------------------*/
3360 if (validate_config_line(to_number(p_itemkey)) <> TRUE) then
3361 cto_msg_pub.cto_message('BOM','CTO_LINE_STATUS_NOT_ELIGIBLE');
3362 raise FND_API.G_EXC_ERROR;
3363 end if;
3364
3365 --bugfix 1799874 start
3366 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => to_number(p_itemkey) );
3367 --bugfix 1799874 end
3368
3369
3370 l_stmt_num := 110;
3371 --select (oel.ordered_quantity - nvl(oel.cancelled_quantity, 0))
3372 select oel.ordered_quantity --bufix 2017099
3373 into l_quantity
3374 from oe_order_lines_all oel
3375 where oel.line_id = to_number(p_itemkey)
3376 and exists (select '1'
3377 from bom_operational_routings bor
3378 where bor.assembly_item_id = oel.inventory_item_id
3379 and bor.organization_id = oel.ship_from_org_id
3380 and bor.alternate_routing_designator is null
3381 and nvl(bor.cfm_routing_flag, 2) = 1)
3382 and not exists (select '1'
3383 from mtl_reservations mr
3384 where mr.demand_source_line_id = oel.line_id
3385 and mr.organization_id = oel.ship_from_org_id
3386 --and mr.demand_source_type_id = inv_reservation_global.g_source_type_oe
3387 and mr.demand_source_type_id =
3388 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
3389 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
3390 and mr.reservation_quantity > 0);
3391
3392 if (l_quantity <= 0) then
3393 IF PG_DEBUG <> 0 THEN
3394 oe_debug_pub.add('create_flow_schedule_wf: ' || 'l_quantity <= 0', 1);
3395 END IF;
3396 cto_msg_pub.cto_message('BOM','CTO_CREATE_FLOW_SCHED_ERROR');
3397 raise FND_API.G_EXC_ERROR;
3398 end if;
3399
3400 l_stmt_num := 120;
3401 -- Removed check hold API call from here as we are going to check for
3402 -- hold in check_supply_type_wf workflow activity, which is just before this workflow
3403 -- node
3404 -- Removed as part of bug fix 5261330
3405
3406 l_stmt_num := 130;
3407 --
3408 -- MRP will not create flow schedules if the scheduled ship date is
3409 -- earlier than today
3410 --
3411 select schedule_ship_date, header_id
3412 into l_sch_ship_date, l_header_id
3413 from oe_order_lines_all oel
3414 where line_id = to_number(p_itemkey);
3415
3416 if (trunc(l_sch_ship_date) < trunc(sysdate)) then
3417 IF PG_DEBUG <> 0 THEN
3418 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Schedule ship date '||
3419 to_char(l_sch_ship_date)||',
3420 is earlier than sysdate');
3421 END IF;
3422 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_SCH_DATE');
3423 x_result := 'COMPLETE:INCOMPLETE';
3424 return;
3425
3426 end if;
3427
3428 CTO_FLOW_SCHEDULE.cto_fs(
3429 p_config_line_id => p_itemkey,
3430 x_return_status => l_x_return_status,
3431 x_msg_name => l_msg_name,
3432 x_msg_txt => l_msg_txt);
3433
3434
3435
3436 IF (l_x_return_status = FND_API.G_RET_STS_ERROR) THEN
3437 IF PG_DEBUG <> 0 THEN
3438 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Expected error in cto_fs. ', 1);
3439 END IF;
3440 cto_msg_pub.cto_message('BOM', l_msg_name);
3441 raise FND_API.G_EXC_ERROR;
3442
3443 ELSIF (l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3444 IF PG_DEBUG <> 0 THEN
3445 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Unexpected error in Create Flow Schedule for line id ' || p_itemkey, 1);
3446 END IF;
3447 cto_msg_pub.cto_message('BOM', l_msg_name);
3448 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3449
3450 END IF;
3451
3452 --
3453 -- check if flow schedules have been scheduled for this line
3454 --
3455
3456 if Flow_Sch_Exists(to_number(p_itemkey)) then
3457 OE_Order_WF_Util.Update_Flow_Status_Code(
3458 p_header_id => l_header_id,
3459 p_line_id => to_number(p_itemkey),
3460 p_flow_status_code => 'PRODUCTION_OPEN',
3461 x_return_status => l_x_return_status);
3462
3463 x_result := 'COMPLETE';
3464 else
3465 IF PG_DEBUG <> 0 THEN
3466 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Flow schedules not created');
3467 END IF;
3468 cto_msg_pub.cto_message('BOM', 'CTO_NO_FLOW_SCHEDULE');
3469 x_result := 'COMPLETE:INCOMPLETE';
3470 return;
3471 end if;
3472 end if; /* p_funcmode = 'RUN' */
3473
3474 OE_STANDARD_WF.Save_Messages;
3475 OE_STANDARD_WF.Clear_Msg_Context;
3476
3477 EXCEPTION
3478
3479 when FND_API.G_EXC_ERROR then
3480 IF PG_DEBUG <> 0 THEN
3481 OE_DEBUG_PUB.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf raised exp error in stmt ' ||
3482 to_char(l_stmt_num) || ':' || l_x_error_msg);
3483 END IF;
3484 OE_STANDARD_WF.Save_Messages;
3485 OE_STANDARD_WF.Clear_Msg_Context;
3486 x_result := 'COMPLETE:INCOMPLETE';
3487 rollback to savepoint before_process;
3488 return;
3489
3490 when NO_DATA_FOUND then
3491 IF PG_DEBUG <> 0 THEN
3492 oe_debug_pub.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf '
3493 || to_char(l_stmt_num) || ':' ||
3494 substrb(sqlerrm, 1, 100),1);
3495 END IF;
3496 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_FLOW_SCHED_ERROR');
3497 OE_STANDARD_WF.Save_Messages;
3498 OE_STANDARD_WF.Clear_Msg_Context;
3499 x_result := 'COMPLETE:INCOMPLETE';
3500 rollback to savepoint before_process;
3501 return;
3502
3503 when FND_API.G_EXC_UNEXPECTED_ERROR then
3504 IF PG_DEBUG <> 0 THEN
3505 OE_DEBUG_PUB.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf raised unexp error in stmt ' ||
3506 to_char(l_stmt_num) || ':' || l_x_error_msg);
3507 END IF;
3508 OE_STANDARD_WF.Save_Messages;
3509 OE_STANDARD_WF.Clear_Msg_Context;
3510 raise;
3511
3512 when OTHERS then
3513 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_FLOW_SCHED_ERROR');
3514 IF PG_DEBUG <> 0 THEN
3515 oe_debug_pub.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf '
3516 || to_char(l_stmt_num) || ':' ||
3517 substrb(sqlerrm, 1, 250),1);
3518 END IF;
3519 wf_core.context('CTO_WORKFLOW', 'create_flow_schedule_wf',
3520 p_itemtype, p_itemkey, to_char(p_actid),
3521 p_funcmode);
3522 raise;
3523
3524 END create_flow_schedule_wf;
3525
3526
3527 -- begin bugfix 2105156
3528
3529 -- PROCEDURE Lock_Line_Id
3530 --
3531 -- Usage Used by set_parameter_work_order_wf API to update the program_id
3532 -- This is done to manually 'lock' the line.
3533 --
3534 -- Desc This procedure is set for autonomous transaction.
3535 -- This procedure accepts line_id.
3536 --
3537 -- Note This procedure uses autonomous transaction.That means
3538 -- commit or rollback with in this procedure will not affect
3539 -- the callers transaction.
3540
3541 PROCEDURE lock_line_id(p_line_id IN NUMBER,
3542 x_result OUT NoCopy VARCHAR2 )
3543 IS
3544 Pragma AUTONOMOUS_TRANSACTION;
3545
3546 record_locked EXCEPTION;
3547 pragma exception_init (record_locked, -54);
3548 l_dummy VARCHAR2(2);
3549
3550 BEGIN
3551
3552 x_result := null;
3553
3554 -- select to see if we can acquire lock. If we cannot, it will raise RECORD_LOCKED exception.
3555
3556 SELECT '1' into l_dummy
3557 FROM oe_order_lines_all
3558 WHERE line_id = p_line_id
3559 FOR UPDATE NOWAIT;
3560
3561 UPDATE oe_order_lines_all oel
3562 SET oel.program_id = -99
3563 WHERE oel.line_id = p_line_id
3564 AND nvl(oel.program_id, 0) <> -99;
3565
3566 COMMIT;
3567 IF PG_DEBUG <> 0 THEN
3568 oe_debug_pub.add ('lock_line_id: ' || 'committed program_id with -99');
3569 END IF;
3570
3571 EXCEPTION
3572 when no_data_found then
3573 null;
3574
3575 when record_locked then
3576 IF PG_DEBUG <> 0 THEN
3577 OE_DEBUG_PUB.add ('lock_line_id: ' || 'CTO_WORKFLOW.Lock_Line_Id: Could not lock line id '|| p_line_id ||' for update.');
3578
3579 OE_DEBUG_PUB.add ('lock_line_id: ' || 'This line is being processed by another process.');
3580 END IF;
3581 x_result := 'COMPLETE:INCOMPLETE';
3582
3583 when others then
3584 IF PG_DEBUG <> 0 THEN
3585 OE_DEBUG_PUB.add ('lock_line_id: ' || 'CTO_WORKFLOW.Lock_Line_Id: Unexpected Error : '||sqlerrm);
3586 END IF;
3587 x_result := 'COMPLETE:INCOMPLETE';
3588
3589 END Lock_Line_Id;
3590
3591 -- end bugfix 2105156
3592
3593
3594 -- bugfix 3136206: we want to make this as autonomous txn, otherwise, these changes will get rolledback due
3595 -- to exception in main api which does a rollback to savepoint.
3596
3597 PROCEDURE unlock_line_id(p_line_id IN NUMBER,
3598 x_result OUT NoCopy VARCHAR2 )
3599 IS
3600 Pragma AUTONOMOUS_TRANSACTION;
3601
3602 record_locked EXCEPTION;
3603 pragma exception_init (record_locked, -54);
3604 l_dummy VARCHAR2(2);
3605
3606 BEGIN
3607
3608 x_result := null;
3609
3610 UPDATE oe_order_lines_all oel
3611 SET oel.program_id = null
3612 WHERE oel.line_id = p_line_id
3613 AND nvl(oel.program_id, 0) = -99;
3614
3615 IF (sql%rowcount > 0) THEN
3616 COMMIT;
3617 IF (PG_DEBUG <> 0) THEN
3618 oe_debug_pub.add ('unlock_line_id: ' || 'unlocked line_id '||p_line_id);
3619 END IF;
3620 END IF;
3621
3622 EXCEPTION
3623
3624 when others then
3625 IF PG_DEBUG <> 0 THEN
3626 OE_DEBUG_PUB.add ('unlock_line_id: error: ' || sqlerrm);
3627
3628 END IF;
3629 x_result := 'COMPLETE:INCOMPLETE';
3630
3631 END UnLock_Line_Id;
3632
3633 -- end bugfix 3136206
3634
3635 /*============================================================================
3636 Procedure: set_parameter_work_order_wf
3637
3638 Description: This procedure gets called when executing the Set
3639 Parameter Work Order activity in the ATO workflow.
3640
3641 More to come...
3642 Parameters:
3643 =============================================================================*/
3644 PROCEDURE set_parameter_work_order_wf(
3645 p_itemtype in VARCHAR2, /* workflow item type */
3646 p_itemkey in VARCHAR2, /* sales order line id */
3647 p_actid in number, /* ID number of WF activity */
3648 p_funcmode in VARCHAR2, /* execution mode of WF activity */
3649 x_result out NoCopy VARCHAR2 /* result of activity */
3650 )
3651 IS
3652
3653 l_stmt_num number := 0;
3654 l_quantity number := 0;
3655 l_class_code number;
3656 l_wip_group_id number;
3657 l_mfg_org_id number;
3658 l_afas_line_id number;
3659 l_msg_name varchar2(30);
3660 l_msg_txt varchar2(500); --bugfix 2776026: increased the var size
3661 l_return_status varchar2(1);
3662 l_user_id varchar2(30);
3663 l_msg_count number;
3664 l_hold_result_out varchar2(1);
3665 l_hold_return_status varchar2(1);
3666 l_ship_iface_flag varchar2(1);
3667
3668 l_source_document_type_id number; -- bugfix 1799874
3669
3670 --fix for bug#1874380
3671 l_item_type_code varchar2(30);
3672 l_ato_line_id number;
3673 l_line_id number;
3674 l_top_model_line_id number;
3675 --end of fix for bug#1874380
3676
3677
3678
3679 -- bugfix 2053360 : declare a new exception
3680 record_locked exception;
3681 pragma exception_init (record_locked, -54);
3682
3683 l_result varchar2(20) := null; --bugfix 2105156
3684
3685 l_build_in_wip varchar2(1); --bugfix 2318060
3686
3687 BEGIN
3688
3689 savepoint before_process;
3690
3691 IF PG_DEBUG <> 0 THEN
3692 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'Function Mode: ' || p_funcmode, 1);
3693
3694 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO Activity: Set Parameter Work Order', 1);
3695 END IF;
3696 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3697
3698 if (p_funcmode = 'RUN') then
3699
3700 -- Note: bugfix 3136206: The Lock_Line_Id api should be called BEFORE the SELECT FOR UPDATE
3701 -- since it is executed in autonomous transaction mode. Autonomous txns are run in
3702 -- different sessions. If this api is called after SELECT FOR UPDATE, then it will fail.
3703
3704 --
3705 -- begin bugfix 2105156: Call lock_line_Id to manually lock the row if possible.
3706 -- Lock_Line_Id API will update the program_id in oeol to -99.
3707 -- if you cannot, raise RECORD_LOCKED exception
3708 --
3709
3710
3711 Lock_Line_Id ( to_number(p_itemkey), l_result );
3712 if ( l_result is not null ) then
3713 raise record_locked;
3714 end if;
3715
3716
3717 --bugfix 1799874 start
3718 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => to_number(p_itemkey) );
3719 --bugfix 1799874 end
3720
3721 /*----------------------------------------------------------+
3722 Check order line status and check order line for holds.
3723 Do not process the order if status is invalid or
3724 if a hold is found.
3725 +-----------------------------------------------------------*/
3726 l_stmt_num := 100;
3727 --select oel.ordered_quantity - nvl(oel.cancelled_quantity, 0),
3728 select oel.ordered_quantity, -- bugfix 2017099
3729 oel.ship_from_org_id,
3730 oel.ato_line_id,--5108885
3731 oel.ato_line_id, oel.line_id,oel.top_model_line_id, oel.item_type_code --fix for bug#1874380
3732 into l_quantity, l_mfg_org_id, l_afas_line_id, l_ato_line_id, l_line_id,
3733 l_top_model_line_id,l_item_type_code
3734 from mtl_system_items msi,
3735 oe_order_lines_all oel
3736 where oel.line_id = to_number(p_itemkey)
3737 and (oel.open_flag is null
3738 or oel.open_flag = 'Y')
3739 and oel.ordered_quantity > 0
3740 and oel.inventory_item_id = msi.inventory_item_id
3741 and msi.organization_id = oel.ship_from_org_id
3742 and oel.schedule_status_code = 'SCHEDULED'
3743 and oel.booked_flag = 'Y'
3744 and oel.ato_line_id is not null
3745 --and oel.shipping_interfaced_flag = 'Y'
3746 and msi.replenish_to_order_flag = 'Y'
3747 and msi.pick_components_flag = 'N'
3748 and msi.build_in_wip_flag = 'Y'
3749 and msi.bom_item_type = 4
3750 /*----------------------------------+
3751 ATO items do not have to have
3752 a base model.
3753 and msi.base_item_id is not NULL
3754 +-----------------------------------*/
3755 and not exists
3756 (select '1'
3757 from oe_order_lines_all oel2
3758 where oel2.ship_from_org_id = oel.ship_from_org_id
3759 and oel2.header_id = oel.header_id
3760 and oel2.line_id = oel.line_id
3761 and rownum = 1
3762 and WIP_ATO_UTILS.check_wip_supply_type(
3763 oel2.header_id,
3764 oel2.line_id,
3765 NULL,
3766 oel2.ship_from_org_id)
3767 not in (0,1)
3768 )
3769 and not exists
3770 (select '1'
3771 from bom_operational_routings bor
3772 where bor.assembly_item_id = oel.inventory_item_id
3773 and bor.organization_id = oel.ship_from_org_id
3774 and bor.alternate_routing_designator is null
3775 and nvl(bor.cfm_routing_flag, 2) = 1)
3776 and not exists
3777 (select '1'
3778 from mtl_reservations mr
3779 where mr.demand_source_line_id = oel.line_id
3780 and mr.organization_id = oel.ship_from_org_id
3781 --and mr.demand_source_type_id = inv_reservation_global.g_source_type_oe
3782 and mr.demand_source_type_id =
3783 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
3784 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
3785 and mr.reservation_quantity > 0)
3786 FOR UPDATE OF oel.line_id NOWAIT; --bugfix 2053360
3787
3788
3789
3790 if (l_quantity <= 0) then
3791 IF PG_DEBUG <> 0 THEN
3792 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'Ordered quantity is zero.', 1);
3793 END IF;
3794 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3795 raise FND_API.G_EXC_ERROR;
3796 end if;
3797
3798 l_stmt_num := 101;
3799
3800 -- Removed check hold API call from here as we are going to check for
3801 -- hold in check_supply_type_wf workflow activity, which is just before this workflow
3802 -- node
3803 -- Removed as part of bug fix 5261330
3804 -- wf_engine.SetItemAttrNumber(p_itemtype, p_itemkey,
3805 -- 'AFAS_ORG_ID',l_mfg_org_id);
3806 -- oe_debug_pub.add('mfg_org_id: ' || to_char(l_mfg_org_id),1);
3807
3808 wf_engine.SetItemAttrNumber(p_itemtype, p_itemkey,
3809 'AFAS_LINE_ID', l_afas_line_id);
3810 IF PG_DEBUG <> 0 THEN
3811 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'order_line_id: ' || p_itemkey,1);
3812
3813 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'l_afas_line_id: ' || l_afas_line_id);
3814 END IF;
3815
3816 x_result := 'COMPLETE';
3817 end if; /* p_funcmode = 'RUN' */
3818
3819 OE_STANDARD_WF.Save_Messages;
3820 OE_STANDARD_WF.Clear_Msg_Context;
3821
3822 EXCEPTION
3823
3824 --
3825 -- begin bugfix 2053360: handle the record_locked exception
3826 --
3827
3828 when record_locked then
3829 cto_msg_pub.cto_message('BOM', 'CTO_ORDER_LINE_LOCKED');
3830 IF PG_DEBUG <> 0 THEN
3831 OE_DEBUG_PUB.add ('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf: Could not lock line id '||
3832 p_itemkey ||' for update.');
3833
3834 OE_DEBUG_PUB.add ('set_parameter_work_order_wf: ' || 'This line is being processed by another process.');
3835 END IF;
3836
3837 OE_STANDARD_WF.Save_Messages;
3838 OE_STANDARD_WF.Clear_Msg_Context;
3839 x_result := 'COMPLETE:INCOMPLETE';
3840 return;
3841
3842
3843 when FND_API.G_EXC_ERROR then
3844 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3845 IF PG_DEBUG <> 0 THEN
3846 OE_DEBUG_PUB.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf raised exc error. ' ||
3847 to_char(l_stmt_num) );
3848 END IF;
3849 OE_STANDARD_WF.Save_Messages;
3850 OE_STANDARD_WF.Clear_Msg_Context;
3851 x_result := 'COMPLETE:INCOMPLETE';
3852 rollback to savepoint before_process;
3853 return;
3854
3855
3856 when FND_API.G_EXC_UNEXPECTED_ERROR then
3857 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3858 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3859 IF PG_DEBUG <> 0 THEN
3860 OE_DEBUG_PUB.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf raised unexc error. ' ||
3861 to_char(l_stmt_num) );
3862 END IF;
3863 OE_STANDARD_WF.Save_Messages;
3864 OE_STANDARD_WF.Clear_Msg_Context;
3865 wf_core.context('CTO_WORKFLOW', 'set_parameter_work_order_wf',
3866 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3867 raise;
3868
3869
3870 when NO_DATA_FOUND then
3871 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3872
3873 --start bugfix 2318060
3874 BEGIN
3875 SELECT build_in_wip_flag
3876 INTO l_build_in_wip
3877 FROM mtl_system_items mtl,
3878 Oe_order_lines_all oel
3879 WHERE oel.line_id = to_number(p_itemkey)
3880 AND oel.inventory_item_id = mtl.inventory_item_id
3881 AND oel.ship_from_org_id = mtl.organization_id;
3882 EXCEPTION
3883 WHEN others THEN
3884 null;
3885
3886 END;
3887
3888 IF l_build_in_wip = 'N' THEN
3889 --set the build in wip flag
3890 cto_msg_pub.cto_message('BOM', 'CTO_BUILD_IN_WIP_FLAG');
3891 IF PG_DEBUG <> 0 THEN
3892 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf:'
3893 ||'ERROR : Buld_in_wip_flag needs to be checked',1 );
3894 END IF;
3895
3896 ELSE--no_data_found is for someother reason
3897
3898 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3899 IF PG_DEBUG <> 0 THEN
3900 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf'
3901 || to_char(l_stmt_num) || ':' ||
3902 substrb(sqlerrm, 1, 100),1);
3903 END IF;
3904 END IF;
3905
3906 --end bugfix 2318060
3907
3908 OE_STANDARD_WF.Save_Messages;
3909 OE_STANDARD_WF.Clear_Msg_Context;
3910
3911 -- Begin bugfix 2053360:
3912 -- Set the result to INCOMPLETE so that the wf returns to Create Supply Order Eligible
3913 x_result := 'COMPLETE:INCOMPLETE';
3914 return;
3915
3916 when OTHERS then
3917 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3918 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3919 IF PG_DEBUG <> 0 THEN
3920 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf: '
3921 || to_char(l_stmt_num) || ':' ||
3922 substrb(sqlerrm, 1, 100));
3923 END IF;
3924 wf_core.context('CTO_WORKFLOW', 'set_parameter_work_order_wf',
3925 p_itemtype, p_itemkey, to_char(p_actid),
3926 p_funcmode);
3927
3928 raise;
3929
3930 END set_parameter_work_order_wf;
3931
3932
3933 /*============================================================================
3934 Procedure: submit_conc_prog_wf
3935 Description: This procedure gets called for the Lead Time Calculate and
3936 the AutoCreate FAS workflow activities. It is a wrapper
3937 around the Workflow activity that submits the concurrent
3938 program via Workflow. The wrapper is needed to retrieve
3939 and display the concurrent request ID after Workflow
3940 submits the request.
3941 =============================================================================*/
3942 PROCEDURE submit_conc_prog_wf(
3943 p_itemtype in VARCHAR2, /* workflow item type */
3944 p_itemkey in VARCHAR2, /* sales order line id */
3945 p_actid in number, /* ID number of WF activity */
3946 p_funcmode in VARCHAR2, /* execution mode of WF activity */
3947 x_result out NoCopy VARCHAR2 /* result of activity */
3948
3949 )
3950 IS
3951
3952 l_stmt_num number := 0;
3953 l_req_id number := 0;
3954 l_msg_name varchar2(30);
3955 l_msg_txt varchar2(2000);
3956 l_token CTO_MSG_PUB.token_tbl;
3957
3958 BEGIN
3959
3960 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3961 IF PG_DEBUG <> 0 THEN
3962 oe_debug_pub.add('submit_conc_prog_wf: ' || 'CTO Activity: submit_conc_prog_wf.',1);
3963 END IF;
3964
3965 if (p_funcmode = 'RUN') then
3966
3967 l_stmt_num := 100;
3968 fnd_wf_standard.ExecuteConcProgram(p_itemtype,
3969 p_itemkey,
3970 p_actid,
3971 p_funcmode,
3972 x_result);
3973
3974 /*---------------------------------------------------------------+
3975 Get Request ID - We are using the same item attribute to store
3976 the request ID of the Lead Time conc prog and AFAS conc prog.
3977 +-----------------------------------------------------------------*/
3978 l_stmt_num := 110;
3979 l_req_id := wf_engine.GetItemAttrNumber(p_itemtype, p_itemkey,
3980 'LEAD_TIME_REQUEST_ID');
3981
3982 IF PG_DEBUG <> 0 THEN
3983 oe_debug_pub.add('submit_conc_prog_wf: ' || 'Request ID: ' || to_char(l_req_id), 1);
3984 END IF;
3985
3986 l_token(1).token_name := 'REQUEST_ID';
3987 l_token(1).token_value := l_req_id;
3988
3989 --oe_debug_pub.add ('1. l_token(1).name = '|| l_token(1).token_name);
3990 --oe_debug_pub.add ('1. l_token(1).value = '|| l_token(1).token_value);
3991
3992 cto_msg_pub.cto_message('BOM', 'CTO_CONCURRENT_REQUEST', l_token);
3993 --fnd_message.set_token('REQUEST_ID', l_req_id);
3994
3995 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
3996
3997 OE_STANDARD_WF.Save_Messages;
3998 OE_STANDARD_WF.Clear_Msg_Context;
3999
4000 end if;
4001
4002 EXCEPTION
4003
4004 when NO_DATA_FOUND then
4005 IF PG_DEBUG <> 0 THEN
4006 oe_debug_pub.add('submit_conc_prog_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4007 || to_char(l_stmt_num) || ':' ||
4008 substrb(sqlerrm, 1, 100));
4009 END IF;
4010 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4011 p_itemtype, p_itemkey, to_char(p_actid),
4012 p_funcmode);
4013 raise;
4014
4015 when OTHERS then
4016 IF PG_DEBUG <> 0 THEN
4017 oe_debug_pub.add('submit_conc_prog_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4018 || to_char(l_stmt_num) || ':' ||
4019 substrb(sqlerrm, 1, 100));
4020 END IF;
4021 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4022 p_itemtype, p_itemkey, to_char(p_actid),
4023 p_funcmode);
4024 raise;
4025
4026 END submit_conc_prog_wf;
4027
4028 /*============================================================================
4029 Procedure: submit_and_continue_wf
4030 Description: This procedure gets called for the Lead Time Calculate and
4031 the AutoCreate FAS workflow activities. It is a wrapper
4032 around the Workflow activity that submits the concurrent
4033 program via Workflow. The wrapper is needed to retrieve
4034 and display the concurrent request ID after Workflow
4035 submits the request.
4036 =============================================================================*/
4037 PROCEDURE submit_and_continue_wf(
4038 p_itemtype in VARCHAR2, /* workflow item type */
4039 p_itemkey in VARCHAR2, /* sales order line id */
4040 p_actid in number, /* ID number of WF activity */
4041 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4042 x_result out NoCopy VARCHAR2 /* result of activity */
4043
4044 )
4045 IS
4046
4047 l_stmt_num number := 0;
4048 l_req_id number := 0;
4049 l_msg_name varchar2(30);
4050 l_msg_txt varchar2(2000);
4051 l_token CTO_MSG_PUB.token_tbl;
4052
4053 l_activity_status varchar2(100);
4054
4055 l_conc_msg number := 0;
4056
4057 BEGIN
4058
4059 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4060 IF PG_DEBUG <> 0 THEN
4061 oe_debug_pub.add('submit_and_continue_wf: ' || 'CTO Activity: submit_conc_prog_wf.',1);
4062 END IF;
4063
4064 if (p_funcmode = 'RUN') then
4065
4066 l_stmt_num := 10;
4067 CTO_WORKFLOW_API_PK.query_wf_activity_status(
4068 p_itemtype => p_itemtype,
4069 p_itemkey => p_itemkey,
4070 p_activity_label => 'EXECLEADTIME',
4071 p_activity_name => 'EXECLEADTIME',
4072 p_activity_status => l_activity_status);
4073
4074 IF PG_DEBUG <> 0 THEN
4075 oe_debug_pub.add('submit_and_continue_wf: ' || 'EXECLEADTIME activity status:'||l_activity_status,1);
4076 END IF;
4077
4078 IF l_activity_status = 'ACTIVE' THEN
4079 l_conc_msg := 1;
4080 IF PG_DEBUG <> 0 THEN
4081 oe_debug_pub.add('submit_and_continue_wf: ' || 'Show message for Lead Time conc program',1);
4082 END IF;
4083 ELSE
4084 CTO_WORKFLOW_API_PK.query_wf_activity_status(
4085 p_itemtype => p_itemtype,
4086 p_itemkey => p_itemkey,
4087 p_activity_label => 'EXECUTECONCPROGAFAS',
4088 p_activity_name => 'EXECUTECONCPROGAFAS',
4089 p_activity_status => l_activity_status);
4090 IF PG_DEBUG <> 0 THEN
4091 oe_debug_pub.add('submit_and_continue_wf: ' || 'EXECUTECONCPROGAFAS activity status:'||l_activity_status,1);
4092 END IF;
4093 IF l_activity_status = 'ACTIVE' THEN
4094 l_conc_msg := 2;
4095 IF PG_DEBUG <> 0 THEN
4096 oe_debug_pub.add('submit_and_continue_wf: ' || 'Show message for AFAS conc program',1);
4097 END IF;
4098 ELSE
4099 l_conc_msg := 1;
4100 END IF;
4101 END IF;
4102
4103 l_stmt_num := 100;
4104 fnd_wf_standard.SubmitConcProgram(p_itemtype,
4105 p_itemkey,
4106 p_actid,
4107 p_funcmode,
4108 x_result);
4109
4110 /*---------------------------------------------------------------+
4111 Get Request ID - We are using the same item attribute to store
4112 the request ID of the Lead Time conc prog and AFAS conc prog.
4113 +-----------------------------------------------------------------*/
4114 l_stmt_num := 110;
4115 l_req_id := wf_engine.GetItemAttrNumber(p_itemtype, p_itemkey,
4116 'LEAD_TIME_REQUEST_ID');
4117
4118 IF PG_DEBUG <> 0 THEN
4119 oe_debug_pub.add('submit_and_continue_wf: ' || 'Request ID: ' || to_char(l_req_id), 1);
4120 END IF;
4121
4122 l_token(1).token_name := 'REQUEST_ID';
4123 l_token(1).token_value := l_req_id;
4124
4125 --oe_debug_pub.add('l_token(1).token_name = '||l_token(1).token_name );
4126 --oe_debug_pub.add('l_token(1).token_value = '||l_token(1).token_value );
4127 IF l_conc_msg = 2 THEN
4128 cto_msg_pub.cto_message('BOM', 'CTO_CONCURRENT_REQUEST',l_token);
4129 ELSE
4130 cto_msg_pub.cto_message('BOM', 'CTO_CONCURRENT_REQUEST_ID',l_token);
4131 END IF;
4132
4133 --fnd_message.set_token('REQUEST_ID', l_req_id);
4134
4135 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
4136
4137 OE_STANDARD_WF.Save_Messages;
4138 OE_STANDARD_WF.Clear_Msg_Context;
4139
4140 end if;
4141
4142 EXCEPTION
4143
4144 when NO_DATA_FOUND then
4145 IF PG_DEBUG <> 0 THEN
4146 oe_debug_pub.add('submit_and_continue_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4147 || to_char(l_stmt_num) || ':' ||
4148 substrb(sqlerrm, 1, 100));
4149 END IF;
4150 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4151 p_itemtype, p_itemkey, to_char(p_actid),
4152 p_funcmode);
4153 raise;
4154
4155 when OTHERS then
4156 IF PG_DEBUG <> 0 THEN
4157 oe_debug_pub.add('submit_and_continue_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4158 || to_char(l_stmt_num) || ':' ||
4159 substrb(sqlerrm, 1, 100));
4160 END IF;
4161 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4162 p_itemtype, p_itemkey, to_char(p_actid),
4163 p_funcmode);
4164 raise;
4165
4166 END submit_and_continue_wf;
4167
4168
4169 /*============================================================================
4170 Procedure: validate_line
4171 Description:
4172
4173 More to come...
4174 Parameters:
4175 =============================================================================*/
4176 FUNCTION validate_line(
4177 p_line_id in number
4178
4179 )
4180 RETURN boolean
4181
4182 IS
4183
4184 l_valid_model_line NUMBER := 0;
4185 v_aps_version number ;
4186
4187 BEGIN
4188
4189
4190 v_aps_version := msc_atp_global.get_aps_version ;
4191
4192 /*------------------------------------------------------------+
4193 Select line details to make sure the model line is valid.
4194 +------------------------------------------------------------*/
4195 select 1
4196 into l_valid_model_line
4197 from oe_order_lines_all oel,
4198 mtl_system_items msi
4199 where oel.line_id = p_line_id
4200 and msi.organization_id = oel.ship_from_org_id
4201 and msi.inventory_item_id = oel.inventory_item_id
4202 and msi.bom_item_type = 1
4203 --and msi.build_in_wip_flag = 'Y'
4204 and msi.replenish_to_order_flag = 'Y'
4205 and oel.open_flag = 'Y'
4206 and (oel.cancelled_flag = 'N'
4207 or oel.cancelled_flag is null)
4208 and ( oel.booked_flag = 'Y' or v_aps_version >= 10 )
4209 and schedule_ship_date is not null /* Fixed bug 3548069 */
4210 and (
4211 ( oel.schedule_status_code = 'SCHEDULED'
4212 and oel.source_type_code = 'INTERNAL'
4213 and oel.visible_demand_flag = 'Y'
4214 )
4215 OR ( oel.source_type_code = 'EXTERNAL' )
4216 ) ; /* BUG#2234858 additional changes Made by sushant for Drop Ship */
4217
4218 --and oel.item_type_code = 'MODEL';
4219
4220 if (l_valid_model_line > 0 ) then
4221 return TRUE;
4222 else
4223 return FALSE;
4224 end if;
4225
4226 EXCEPTION
4227
4228 when NO_DATA_FOUND then
4229 return FALSE;
4230
4231
4232 when OTHERS then
4233 return FALSE;
4234
4235 END validate_line;
4236
4237
4238 /*============================================================================
4239 Procedure: validate_config_line
4240 Description: This procedure gets called when executing the Match
4241 Configuration activity in the ATO workflow. The
4242 format is follows the standard Workflow API format.
4243
4244 More to come...
4245 Parameters:
4246 =============================================================================*/
4247 FUNCTION validate_config_line(
4248 p_config_line_id in number
4249 )
4250 RETURN boolean
4251
4252 IS
4253
4254 l_valid_config_line NUMBER := 0;
4255
4256
4257 BEGIN
4258
4259 /*------------------------------------------------------------+
4260 Select line details to make sure the config line is valid.
4261 +------------------------------------------------------------*/
4262 select 1
4263 into l_valid_config_line
4264 from oe_order_lines_all oel,
4265 mtl_system_items msi
4266 where oel.line_id = p_config_line_id
4267 and msi.organization_id = oel.ship_from_org_id
4268 and oel.inventory_item_id = msi.inventory_item_id
4269 and msi.bom_item_type = 4
4270 and msi.build_in_wip_flag = 'Y'
4271 and msi.replenish_to_order_flag = 'Y'
4272 and oel.open_flag = 'Y'
4273 and (oel.cancelled_flag = 'N'
4274 or oel.cancelled_flag is null)
4275 and oel.visible_demand_flag = 'Y'
4276 and oel.booked_flag = 'Y'
4277 and oel.schedule_status_code = 'SCHEDULED'
4278 and ( oel.item_type_code = 'CONFIG' OR
4279 ( oel.item_type_code in ('STANDARD','OPTION') AND --bugfix#2111718
4280 oel.ato_line_id = p_config_line_id ) );
4281
4282 if (l_valid_config_line > 0 ) then
4283 return TRUE;
4284 else
4285 return FALSE;
4286 end if;
4287
4288 EXCEPTION
4289
4290 when NO_DATA_FOUND then
4291 return FALSE;
4292
4293 when OTHERS then
4294 return FALSE;
4295 END validate_config_line;
4296
4297
4298
4299 /*============================================================================
4300 Procedure: config_line_exists
4301 Description: This procedure gets called when executing the Match
4302 Configuration activity in the ATO workflow. The
4303 format is follows the standard Workflow API format.
4304
4305 More to come...
4306
4307 Parameters:
4308 =============================================================================*/
4309 FUNCTION config_line_exists(
4310 p_model_line_id in number
4311 )
4312 RETURN boolean
4313
4314 IS
4315 l_config_item NUMBER := 0;
4316
4317 BEGIN
4318 select 1
4319 into l_config_item
4320 from oe_order_lines_all oelM,
4321 oe_order_lines_all oelC
4322 where oelM.line_id = p_model_line_id
4323 and oelC.ato_line_id = oelM.line_id
4324 and oelC.item_type_code = 'CONFIG';
4325
4326 if (l_config_item > 0) then
4327 return TRUE;
4328 end if;
4329
4330 EXCEPTION
4331 when NO_DATA_FOUND then
4332 return FALSE;
4333
4334 when OTHERS then
4335 return FALSE;
4336
4337 end config_line_exists;
4338
4339
4340
4341
4342
4343 procedure config_item_created_wf(
4344 p_itemtype in VARCHAR2, /*w workflow item type */
4345 p_itemkey in VARCHAR2, /* config line id */
4346 p_actid in NUMBER, /* ID number of WF activity */
4347 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4348 x_result out NoCopy VARCHAR2 /* result of activity */
4349 )
4350 is
4351 begin
4352 if( config_line_exists( p_itemkey) ) then
4353 -- x_result := 'COMPLETE:COMPLETE' ;
4354 x_result := 'COMPLETE:CONFIG_CREATED' ;
4355
4356 else
4357
4358 -- x_result := 'COMPLETE:INCOMPLETE' ;
4359 x_result := 'COMPLETE:CONFIG_NOT_CREATED' ;
4360
4361 end if;
4362
4363
4364
4365 end config_item_created_wf ;
4366
4367 /*============================================================================
4368 Procedure: reservation_exists
4369 Description: This procedure gets called when executing the Match
4370 Configuration activity in the ATO workflow. The
4371 format is follows the standard Workflow API format.
4372
4373 More to come...
4374
4375 Parameters:
4376 =============================================================================*/
4377 FUNCTION reservation_exists(
4378 p_config_line_id in number,
4379 x_reserved_qty out NoCopy number
4380 )
4381 RETURN boolean
4382
4383 IS
4384 x_reserved_quantity NUMBER := 0;
4385 l_source_document_type_id NUMBER; -- bugfix 1799874
4386
4387 BEGIN
4388
4389 --bugfix 1799874 start
4390 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_config_line_id );
4391 --bugfix 1799874 end
4392
4393 select sum(nvl(mrs.reservation_quantity,0))
4394 into x_reserved_quantity
4395 from mtl_system_items msi,
4396 oe_order_lines_all oel,
4397 mtl_reservations mrs
4398 where oel.line_id = p_config_line_id
4399 and oel.open_flag = 'Y'
4400 --and (oel.ordered_quantity - oel.cancelled_quantity) > 0
4401 and oel.ordered_quantity > 0 -- bugfix 2017099
4402 and oel.inventory_item_id = msi.inventory_item_id
4403 and msi.organization_id = oel.ship_from_org_id
4404 and oel.item_type_code = 'CONFIG'
4405 and oel.schedule_status_code = 'SCHEDULED'
4406 and oel.booked_flag = 'Y'
4407 and (oel.cancelled_flag = 'N'
4408 or oel.cancelled_flag is null)
4409 and msi.replenish_to_order_flag = 'Y'
4410 and msi.pick_components_flag = 'N'
4411 and msi.bom_item_type = 4
4412 and msi.base_item_id is not NULL
4413 and mrs.demand_source_line_id = oel.line_id
4414 and mrs.demand_source_header_id is not NULL
4415 and mrs.organization_id = oel.ship_from_org_id
4416 --and mrs.demand_source_type_id = inv_reservation_global.g_source_type_oe
4417 and mrs.demand_source_type_id =
4418 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
4419 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
4420 and mrs.supply_source_type_id =
4421 inv_reservation_global.g_source_type_inv
4422 and mrs.reservation_quantity > 0
4423 group by oel.line_id;
4424
4425 return TRUE;
4426
4427 EXCEPTION
4428 when NO_DATA_FOUND then
4429 return FALSE;
4430
4431 when OTHERS then
4432 return FALSE;
4433
4434 end reservation_exists;
4435
4436 function flow_sch_exists(pLineId in number)
4437 return boolean
4438 is
4439
4440 lWipEntityId number;
4441
4442 begin
4443
4444 select wip_entity_id
4445 into lWipEntityId
4446 from wip_flow_schedules wfs,
4447 oe_order_lines_all oel,
4448 oe_order_headers_all oeh,
4449 oe_transaction_types_all ota,
4450 oe_transaction_types_tl otl,
4451 mtl_sales_orders mso
4452 where wfs.demand_source_line = to_char(pLineId) --config line id
4453 and oel.line_id = pLineId
4454 and oeh.header_id = oel.header_id
4455 and oeh.order_type_id = ota.transaction_type_id
4456 and ota.transaction_type_code='ORDER'
4457 and ota.transaction_type_id = otl.transaction_type_id
4458 and oeh.order_number = mso.segment1
4459 and otl.name = mso.segment2
4460 and otl.language = (select language_code
4461 from fnd_languages
4462 where installed_flag = 'B')
4463 and mso.sales_order_id = wfs.demand_source_header_id
4464 and oel.inventory_item_id = wfs.primary_item_id
4465 and rownum = 1;
4466
4467 IF PG_DEBUG <> 0 THEN
4468 oe_debug_pub.add ('flow_sch_exists: ' || 'Flow Schedule Exists!', 1);
4469 END IF;
4470 return TRUE; -- Flow Schedule exists
4471
4472 exception
4473 when no_data_found then
4474
4475 IF PG_DEBUG <> 0 THEN
4476 oe_debug_pub.add ('flow_sch_exists: ' || 'Flow Schedules does not exist ', 1);
4477 END IF;
4478 return FALSE; -- Flow Schedule does not exist
4479
4480 when others then
4481 return FALSE;
4482
4483 end flow_sch_exists;
4484
4485
4486
4487 PROCEDURE Purchase_price_calc_wf(
4488 p_itemtype in VARCHAR2, /* workflow item type */
4489 p_itemkey in VARCHAR2, /* sales order line id */
4490 p_actid in number, /* ID number of WF activity */
4491 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4492 x_result out NoCopy VARCHAR2 /* result of activity */
4493
4494 )
4495 IS
4496 xreturnstatus varchar2(1);
4497 xmsgcount Number;
4498 xmsgdata Varchar2(800);
4499 x_oper_unit_list cto_auto_procure_pk.oper_unit_tbl;
4500 L_STMT_NUM Number;
4501 l_ato_line_id Number;
4502 l_batch_no Number;
4503 BEGIN
4504
4505 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4506 IF PG_DEBUG <> 0 THEN
4507 oe_debug_pub.add('Purchase_price_calc_wf: ' || 'CTO Activity: Purchase Price Calc wf.',1);
4508 END IF;
4509
4510 select ato_line_id
4511 into l_ato_line_id
4512 from oe_order_lines_all
4513 where line_id = p_itemkey;
4514
4515 if (p_funcmode = 'RUN') then
4516 /*
4517 CTO_AUTO_PROCURE_PK.Create_Purchasing_Doc(
4518 p_top_model_line_id => l_ato_line_id,
4519 p_overwrite_list_price => 'N',
4520 p_called_in_batch => 'N',
4521 p_batch_number => l_batch_no,
4522 p_ato_line_id => l_ato_line_id,
4523 x_oper_unit_list => x_oper_unit_list,
4524 x_return_status => xReturnStatus,
4525 x_msg_count => XMsgCount,
4526 x_msg_data => xmsgdata);
4527
4528 if xreturnstatus = FND_API.G_RET_STS_ERROR then
4529 IF PG_DEBUG <> 0 THEN
4530 oe_debug_pub.add('Purchase_price_calc_wf: ' || ' Failed in Create_purchasing_doc call...',1);
4531 END IF;
4532 -- raise FND_API.G_EXC_ERROR;
4533 elsif xreturnstatus = FND_API.G_RET_STS_UNEXP_ERROR then
4534 IF PG_DEBUG <> 0 THEN
4535 oe_debug_pub.add('Purchase_price_calc_wf: ' || ' Failed in Create_purchasing_doc call...',1);
4536 END IF;
4537 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
4538 end if;
4539 */
4540
4541 oe_debug_pub.add('Purchase_price_calc_wf: ' || ' Failed to call Create_purchasing_doc call...',1);
4542
4543
4544 end if;
4545 OE_STANDARD_WF.Save_Messages;
4546 OE_STANDARD_WF.Clear_Msg_Context;
4547
4548 Exception when OTHERS then
4549 IF PG_DEBUG <> 0 THEN
4550 oe_debug_pub.add('Purchase_price_calc_wf: ' || 'CTO_WORKFLOW.Purchase_Price_calc_wf: '
4551 || to_char(l_stmt_num) || ':' ||
4552 substrb(sqlerrm, 1, 100));
4553 END IF;
4554 wf_core.context('CTO_WORKFLOW', 'Purchase_Price_calc_wf',
4555 p_itemtype, p_itemkey, to_char(p_actid),
4556 p_funcmode);
4557 raise;
4558
4559 End Purchase_Price_calc_wf;
4560
4561
4562 /*============================================================================
4563 Procedure: chk_rsv_after_afas_wf
4564 Description:
4565 The format follows the standard Workflow API format.
4566 06/04/02 bugfix2327972
4567 | added a new function node which calls procedure
4568 | chk_rsv_after_afas_wf.
4569 | This nodes checks if any type of reservation
4570 | exists. Node has been added in warning path after
4571 | autocreate fas node
4572 | This calls CTO_UTILITY_PK.chk_all_rsv_details to
4573 | check if any reservations exits for this line
4574 |
4575
4576 Parameters:
4577 =============================================================================*/
4578
4579 PROCEDURE chk_rsv_after_afas_wf (
4580 p_itemtype in VARCHAR2, /* item type */
4581 p_itemkey in VARCHAR2, /* config line id */
4582 p_actid in NUMBER, /* ID number of WF activity */
4583 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4584 x_result out NoCopy VARCHAR2 /* result of activity */
4585 )
4586 IS
4587
4588 l_stmt_num NUMBER;
4589
4590 l_msg_count number;
4591 l_msg_data varchar2(2000);
4592 l_return_status varchar2(1);
4593 l_rsv_details CTO_UTILITY_PK.t_resv_details;
4594
4595
4596
4597
4598 BEGIN
4599 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4600 IF PG_DEBUG <> 0 THEN
4601 oe_debug_pub.add('chk_rsv_after_afas_wf: ' || 'CTO Activity: Check Reservation after afas activity ', 1);
4602 END IF;
4603
4604 IF (p_funcmode = 'RUN') then
4605
4606 l_stmt_num := 260;
4607
4608 CTO_UTILITY_PK.chk_all_rsv_details
4609 ( p_itemkey,
4610 l_rsv_details,
4611 l_msg_count,
4612 l_msg_data,
4613 l_return_status
4614 );
4615
4616 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4617 x_result :='COMPLETE:RESERVED';
4618 ELSE
4619 x_result := 'COMPLETE';
4620 END IF;
4621
4622 END IF ; /*p_funcmode ='RUN"*/
4623
4624 OE_STANDARD_WF.Save_Messages;
4625 OE_STANDARD_WF.Clear_Msg_Context;
4626
4627 EXCEPTION
4628 when FND_API.G_EXC_ERROR then
4629 IF PG_DEBUG <> 0 THEN
4630 OE_DEBUG_PUB.add('chk_rsv_after_afas_wf: ' || 'CTO_WORKFLOW.chk_rsv_after_afas_wf' || to_char(l_stmt_num) );
4631 END IF;
4632 OE_STANDARD_WF.Save_Messages;
4633 OE_STANDARD_WF.Clear_Msg_Context;
4634 raise; -- can be re-tried
4635
4636 when FND_API.G_EXC_UNEXPECTED_ERROR then
4637 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4638 FND_MSG_PUB.Add_Exc_Msg
4639 (G_PKG_NAME
4640 ,'chk_rsv_after_afas_wf'
4641 );
4642 end if;
4643
4644 IF PG_DEBUG <> 0 THEN
4645 oe_debug_pub.add('chk_rsv_after_afas_wf: ' || 'corresponds to unexpected error at called program chk_rsv_after_afas_wf '||'
4646 l_stmt_num :'|| l_stmt_num ||sqlerrm, 1);
4647 END IF;
4648 OE_STANDARD_WF.Save_Messages;
4649 OE_STANDARD_WF.Clear_Msg_Context;
4650 wf_core.context('CTO_WORKFLOW', 'chk_rsv_after_afas_wf', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4651 raise;
4652
4653 when OTHERS then
4654 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4655 FND_MSG_PUB.Add_Exc_Msg
4656 (G_PKG_NAME
4657 ,'chk_rsv_after_afas_wf'
4658 );
4659 end if;
4660 IF PG_DEBUG <> 0 THEN
4661 oe_debug_pub.add('chk_rsv_after_afas_wf: ' || 'error at chk_rsv_after_afas_wf' || to_char(l_stmt_num)|| sqlerrm);
4662 END IF;
4663 /*-------------------------------------------+
4664 Error Information for Notification.
4665 +--------------------------------------------*/
4666 wf_core.context('CTO_WORKFLOW','chk_rsv_after_afas_wf',p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4667 raise;
4668
4669 END chk_rsv_after_afas_wf;
4670
4671
4672 --This will get called from a new node in create supply subprocess
4673 --added fro DMF-J
4674 --new node is : Check supply creation which befor create supply order
4675 --block activity
4676 --create by KKONADA
4677 PROCEDURE check_supply_creation_wf(
4678 p_itemtype in VARCHAR2, /*item type */
4679 p_itemkey in VARCHAR2, /* config line id */
4680 p_actid in number, /* ID number of WF activity */
4681 p_funcmode in VARCHAR2, /* execution mode of WF activity*/
4682 x_result out NoCopy VARCHAR2 /* result of activity */
4683 )
4684 IS
4685 l_can_create_supply VARCHAR2(1);
4686 l_source_type NUMBER;
4687 l_return_status VARCHAR2(1);
4688 l_msg_count NUMBER;
4689 l_msg_data VARCHAR2(2000);
4690 l_stmt_num NUMBER;
4691
4692 l_inventory_item_id number;
4693 l_ship_from_org_id number;
4694 l_item_type_code Oe_order_lines_all.item_type_code%type;
4695 l_source_type_code oe_order_lines.source_type_code%type ;
4696
4697 l_status NUMBER;
4698 -- l_return_status VARCHAR2(1);
4699 l_header_id NUMBER;
4700 return_value NUMBER;
4701
4702 --opm
4703 l_sourcing_org number;
4704 l_message varchar2(100);
4705
4706 BEGIN
4707
4708 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4709 IF PG_DEBUG <> 0 THEN
4710 oe_debug_pub.add('check_supply_creation_wf: ' || 'CTO Activity: Check Supply Type', 1);
4711
4712 oe_debug_pub.add('check_supply_creation_wf: ' || 'Item key = '||p_itemkey,1);
4713
4714 oe_debug_pub.add('check_supply_creation_wf: ' || 'Func Mode ='||p_funcmode,1);
4715 END IF;
4716
4717 IF (p_funcmode = 'RUN') THEN
4718
4719
4720
4721 l_stmt_num:=10;
4722 select inventory_item_id, ship_from_org_id,item_type_code, source_type_code,header_id
4723 into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code,l_header_id
4724 from oe_order_lines_all
4725 where line_id = to_number(p_itemkey)
4726 and ato_line_id is not null;
4727 IF PG_DEBUG <> 0 THEN
4728 oe_debug_pub.add('check_supply_creation_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);
4729
4730 oe_debug_pub.add('check_supply_creation_wf: ' || 'Ship from org id ='||to_char(l_ship_from_org_id),1);
4731
4732 oe_debug_pub.add('check_supply_creation_wf: ' || 'Item type code ='||l_item_type_code,1);
4733
4734
4735 oe_debug_pub.add('check_supply_creation_wf: ' || 'l_source_type_code ='||l_source_type_code,1);
4736 END IF;
4737
4738 /*
4739
4740 ** need to branch on source type for drop ship functionality
4741 */
4742 l_stmt_num:=20;
4743 IF( l_source_type_code = 'EXTERNAL' )
4744 THEN
4745
4746 IF PG_DEBUG <> 0 THEN
4747 oe_debug_pub.add('check_supply_creation_wf: ' || 'It is Config item Drop Ship case...',1);
4748 END IF;
4749 x_result := 'COMPLETE';
4750 OE_STANDARD_WF.Save_Messages;
4751 OE_STANDARD_WF.Clear_Msg_Context;
4752
4753
4754
4755
4756
4757
4758
4759 l_stmt_num:=25;
4760 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(to_number(p_itemkey));
4761
4762
4763 IF PG_DEBUG <> 0 THEN
4764 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4765 ||return_value ,5);
4766 END IF;
4767
4768 if return_value <> 1 then
4769 IF PG_DEBUG <> 0 THEN
4770 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4771 ||return_value ,1);
4772 END IF;
4773 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
4774 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4775 end if;
4776
4777
4778
4779
4780 return;
4781
4782
4783 END IF ;
4784
4785 l_stmt_num:=30;
4786 CTO_UTILITY_PK.check_cto_can_create_supply
4787 (
4788 P_config_item_id => l_inventory_item_id,
4789 P_org_id => l_ship_from_org_id,
4790 x_can_create_supply => l_can_create_supply,
4791 p_source_type => l_source_type,
4792 x_return_status => l_return_status,
4793 X_msg_count => l_msg_count,
4794 X_msg_data => l_msg_data,
4795 x_sourcing_org => l_sourcing_org, --new param R12 OPM
4796 x_message => l_message --new param R12 OPM
4797 );
4798
4799 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4800 IF l_can_create_supply = 'N' THEN
4801
4802 IF PG_DEBUG <> 0 THEN
4803 oe_debug_pub.add('check_supply_creation_wf: ' || 'It is Config item Planning case...',1);
4804 oe_debug_pub.add('check_supply_creation_wf: ' || l_message,1);
4805 END IF;
4806
4807 l_stmt_num:=40;
4808 x_result := 'COMPLETE:PLANNING';
4809
4810 IF PG_DEBUG <> 0 THEN
4811 oe_debug_pub.add('check_supply_creation_wf: ' || 'wrkflow result code'
4812 ||x_result ,5);
4813 END IF;
4814
4815
4816
4817 ELSE
4818
4819 l_stmt_num:=60;
4820 x_result := 'COMPLETE';
4821
4822 IF PG_DEBUG <> 0 THEN
4823 oe_debug_pub.add('check_supply_creation_wf: ' || 'wrkflow result code'
4824 ||x_result ,5);
4825 END IF;
4826
4827 END IF;--l_can_create_supply
4828
4829 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4830 RAISE FND_API.G_EXC_ERROR;
4831 ELSE
4832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4833 END IF;--l_return_status
4834
4835
4836 l_stmt_num:=70;
4837 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(to_number(p_itemkey));
4838
4839
4840 IF PG_DEBUG <> 0 THEN
4841 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4842 ||return_value ,5);
4843 END IF;
4844
4845 if return_value <> 1 then
4846 IF PG_DEBUG <> 0 THEN
4847 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4848 ||return_value ,1);
4849 END IF;
4850 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
4851 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4852 end if;
4853
4854 END IF;--run
4855
4856 OE_STANDARD_WF.Save_Messages;
4857 OE_STANDARD_WF.Clear_Msg_Context;
4858
4859 EXCEPTION
4860
4861 when FND_API.G_EXC_ERROR then
4862 IF PG_DEBUG <> 0 THEN
4863 OE_DEBUG_PUB.add('check_supply_creation_wf: ' || 'CTO_WORKFLOW.check_supply_creation_wf ' ||
4864 to_char(l_stmt_num));
4865 END IF;
4866 OE_STANDARD_WF.Save_Messages;
4867 OE_STANDARD_WF.Clear_Msg_Context;
4868 --x_result := 'COMPLETE:INCOMPLETE';
4869 wf_core.context('CTO_WORKFLOW', 'check_supply_creation_wf',
4870 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4871 raise;
4872
4873
4874 when FND_API.G_EXC_UNEXPECTED_ERROR then
4875 IF PG_DEBUG <> 0 THEN
4876 OE_DEBUG_PUB.add('check_supply_creation_wf: ' || 'CTO_WORKFLOW.check_supply_creation_wf ' ||
4877 to_char(l_stmt_num));
4878 END IF;
4879 OE_STANDARD_WF.Save_Messages;
4880 OE_STANDARD_WF.Clear_Msg_Context;
4881 wf_core.context('CTO_WORKFLOW', 'check_supply_creation_wf',
4882 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4883 raise;
4884
4885 when OTHERS then
4886 IF PG_DEBUG <> 0 THEN
4887 oe_debug_pub.add('check_supply_creation_wf: ' || 'CTO_WORKFLOW.check_supply_creation_wf' ||
4888 to_char(l_stmt_num));
4889 oe_debug_pub.add('check_supply_creation_wf: ' || 'errmsg' ||sqlerrm,1);
4890 END IF;
4891 wf_core.context('CTO_WORKFLOW', 'check_supply_creation_wf',
4892 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4893
4894 OE_STANDARD_WF.Save_Messages;
4895 OE_STANDARD_WF.Clear_Msg_Context;
4896
4897 raise;
4898
4899
4900 END check_supply_creation_wf;
4901
4902
4903
4904
4905
4906
4907 END CTO_WORKFLOW;