[Home] [Help]
PACKAGE BODY: APPS.CTO_WORKFLOW
Source
1 package body CTO_WORKFLOW as
2 /* $Header: CTOWKFLB.pls 120.19.12020000.2 2012/08/09 15:12:48 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 -- Start Bugfix 8305535
1380 -- Calling RAISE EVENT to push items to Siebel
1381 l_stmt_num := 231;
1382 CTO_MSUTIL_PUB.Raise_event_for_seibel;
1383 -- End Bugfix 8305535
1384
1385
1386 x_result := 'COMPLETE';
1387
1388
1389
1390
1391
1392 end if; /* end of p_funcmode = 'RUN' */
1393
1394 CTO_CONFIG_BOM_PK.gApplyHold := 'N'; -- bugfix 2899529: Reset this global variable.
1395
1396 OE_STANDARD_WF.Save_Messages;
1397 OE_STANDARD_WF.Clear_Msg_Context;
1398
1399
1400 EXCEPTION
1401
1402 when FND_API.G_EXC_ERROR then
1403 IF PG_DEBUG <> 0 THEN
1404 OE_DEBUG_PUB.add('create_config_item_wf: ' || 'CTO_WORKFLOW.create_config_item_wf ' ||
1405 to_char(l_stmt_num) || ':' ||
1406 l_x_error_msg);
1407 END IF;
1408 fnd_msg_pub.count_and_get(p_data=>x_msg_data,p_count=>x_msg_count);
1409 OE_STANDARD_WF.Save_Messages;
1410 OE_STANDARD_WF.Clear_Msg_Context;
1411 x_result := 'COMPLETE:INCOMPLETE';
1412 rollback to savepoint before_item_creation;
1413
1414 when FND_API.G_EXC_UNEXPECTED_ERROR then
1415 IF PG_DEBUG <> 0 THEN
1416 OE_DEBUG_PUB.add('create_config_item_wf: ' || 'CTO_WORKFLOW.create_config_item_wf ' ||
1417 to_char(l_stmt_num) || ':' ||
1418 l_x_error_msg);
1419 END IF;
1420 OE_STANDARD_WF.Save_Messages;
1421 OE_STANDARD_WF.Clear_Msg_Context;
1422 wf_core.context('CTO_WORKFLOW', 'create_config_item_wf',
1423 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1424 raise;
1425
1426 when OTHERS then
1427 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ITEM_ERROR');
1428 IF PG_DEBUG <> 0 THEN
1429 oe_debug_pub.add('create_config_item_wf: ' || 'CTO_WORKFLOW.create_config_item_wf' ||
1430 to_char(l_stmt_num) || ':' ||
1431 substrb(sqlerrm, 1, 100));
1432 END IF;
1433 wf_core.context('CTO_WORKFLOW', 'create_config_item_wf',
1434 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
1435
1436 OE_STANDARD_WF.Save_Messages;
1437 OE_STANDARD_WF.Clear_Msg_Context;
1438
1439 raise;
1440
1441 END create_config_item_wf;
1442
1443
1444
1445 /*============================================================================
1446 Procedure: create_reservation
1447 Description: This API gets called from create_config_item_wf
1448
1449 Parameters:
1450 ============================================================================*/
1451 PROCEDURE create_reservation(
1452 p_mfg_org_id in number ,
1453 p_top_model_line_id in number,
1454 p_config_id in number ,
1455 p_reservation_uom_code in varchar2 ,
1456 p_quantity_to_reserve in number,
1457 p_schedule_ship_date in DATE,
1458 p_mode in varchar2 ,
1459 x_reserve_status out NoCopy varchar2,
1460 x_msg_count out NoCopy number ,
1461 x_msg_data out NoCopy varchar2,
1462 x_return_status out NoCopy varchar2
1463 )
1464 IS
1465
1466 l_tree_id integer ;
1467 l_return_status varchar2(1) ;
1468
1469
1470 l_x_qoh number;
1471 l_x_rqoh number;
1472 l_x_qs number;
1473 l_x_qr number;
1474 l_x_att number;
1475
1476 l_primary_uom_code varchar2(3);
1477 l_automatic_reservation varchar2(2) ;
1478 l_diff_days number ;
1479 l_reservation_time_fence number;
1480
1481 l_quantity_to_reserve number ;
1482 x_available_qty number ;
1483 x_quantity_reserved number ;
1484 -- x_msg_count number ;
1485 -- x_msg_data varchar2(200) ;
1486 x_error_message varchar2(200) ;
1487 x_message_name varchar2(200) ;
1488
1489 l_stmt_num number := 0 ;
1490
1491 PROCESS_ERROR exception;
1492 RESERVATION_ERROR exception;
1493
1494 l_x_error_msg_name varchar2(30);
1495 l_x_error_msg varchar2(500); --bugfix 2776026: increased the var size
1496
1497
1498 l_organization_name varchar2(200) ;
1499 l_config_item_name varchar2(200) ;
1500 lMatchProfile varchar2(10);
1501
1502 l_partial_reservation boolean := FALSE ;
1503 l_token CTO_MSG_PUB.token_tbl;
1504
1505 l_current_org_id Number;
1506
1507 -- Bug 12374440
1508 -- defined a variable and cursor for getting subinv_code
1509 l_subinventory_code VARCHAR2(30) DEFAULT NULL;
1510
1511 cursor c_subinv is
1512 select subinventory
1513 from oe_order_lines
1514 where line_id = p_top_model_line_id;
1515
1516 BEGIN
1517 /*
1518 ** Check whether full match was successful for top model to create reservations
1519 */
1520
1521 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1522 x_reserve_status := 'MATCH' ;
1523
1524
1525 /*
1526 ** x_reserve_status := { 'MATCH', 'NOQTY' , 'PARTIAL' , 'COMPLETE' }
1527 */
1528
1529 l_quantity_to_reserve := p_quantity_to_reserve ;
1530
1531
1532 /* use this to override l_automatic_reservation := 'Y' ;*/
1533
1534
1535 l_automatic_reservation := FND_PROFILE.VALUE('CTO_AUTOMATIC_RESERVATION');
1536
1537 -- Code change for MOAC
1538
1539
1540 l_current_org_id := MO_GLOBAL.get_current_org_id;
1541 l_reservation_time_fence :=
1542 oe_sys_parameters.value('ONT_RESERVATION_TIME_FENCE',l_current_org_id);
1543
1544 -- End of MOAC code Change
1545
1546 l_diff_days := trunc( p_schedule_ship_date ) - trunc( sysdate ) ;
1547
1548
1549
1550 IF PG_DEBUG <> 0 THEN
1551 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1552
1553 oe_debug_pub.add('create_reservation: ' || ' going to check for reservation ' , 1 );
1554
1555 oe_debug_pub.add('create_reservation: ' || ' going to check for reservation ' || to_char(l_diff_days) ||
1556 ' time fence ' || to_char(l_reservation_time_fence)
1557 , 1 );
1558
1559 oe_debug_pub.add('create_reservation: ' || ' going to check for automatic reservation ' ||
1560 l_automatic_reservation , 1 );
1561 END IF;
1562
1563 if( l_automatic_reservation = '1' and
1564 l_diff_days <= l_reservation_time_fence
1565 )
1566 then
1567
1568 IF PG_DEBUG <> 0 THEN
1569 oe_debug_pub.add('create_reservation: ' || ' going to attempt reservation ' , 1 );
1570 END IF;
1571
1572 /*-------------------------------------------------+
1573 Create a quantity tree to get atr for reservation.
1574 +--------------------------------------------------*/
1575 l_stmt_num := 150;
1576
1577 INV_QUANTITY_TREE_GRP.create_tree
1578 ( p_api_version_number => 1.0
1579 , p_init_msg_lst => fnd_api.g_false
1580 , x_return_status => l_return_status
1581 , x_msg_count => x_msg_count
1582 , x_msg_data => x_msg_data
1583 , p_organization_id => p_mfg_org_id
1584 , p_inventory_item_id => p_config_id
1585 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
1586 , p_is_revision_control => FALSE
1587 , p_is_lot_control => FALSE
1588 , p_is_serial_control => FALSE
1589 , x_tree_id => l_tree_id);
1590
1591 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1592 IF PG_DEBUG <> 0 THEN
1593 oe_debug_pub.add('create_reservation: ' || 'Failed in create_tree with status: ' ||
1594 l_return_status, 1);
1595 END IF;
1596 raise PROCESS_ERROR;
1597 ELSE
1598 IF PG_DEBUG <> 0 THEN
1599 oe_debug_pub.add('create_reservation: ' || 'Success in create_tree.',1);
1600
1601 oe_debug_pub.add('create_reservation: ' || 'Tree ID:' || to_char(l_tree_id),1);
1602 END IF;
1603 END IF;
1604
1605 /*-----------------------------------------------------+
1606 Query quantity tree get quantity available to reserve.
1607 +------------------------------------------------------*/
1608 l_stmt_num := 160;
1609
1610 --Bugfix 12374440: Passing the subinventory information in order to get the
1611 --availability from this subinventory only.
1612
1613 open c_subinv;
1614 fetch c_subinv into l_subinventory_code;
1615 close c_subinv;
1616
1617 IF PG_DEBUG <> 0 THEN
1618 oe_debug_pub.add('create_reservation: ' || 'Subinventory Code:' || l_subinventory_code,1);
1619 END IF;
1620
1621 l_stmt_num := 165;
1622 INV_QUANTITY_TREE_GRP.query_tree
1623 (p_api_version_number => 1.0,
1624 p_init_msg_lst => fnd_api.g_false,
1625 x_return_status => l_return_status,
1626 x_msg_count => x_msg_count,
1627 x_msg_data => x_msg_data,
1628 p_tree_id => l_tree_id,
1629 p_revision => NULL,
1630 p_lot_number => NULL,
1631 --p_subinventory_code => NULL,
1632 p_subinventory_code => l_subinventory_code, --Bugfix 12374440
1633 p_locator_id => NULL,
1634 x_qoh => l_x_qoh,
1635 x_rqoh => l_x_rqoh,
1636 x_qr => l_x_qr,
1637 x_qs => l_x_qs,
1638 x_att => l_x_att,
1639 x_atr => x_available_qty);
1640
1641 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1642 IF PG_DEBUG <> 0 THEN
1643 oe_debug_pub.add('create_reservation: ' || 'Failed in create_tree with status: ' ||
1644 l_return_status, 1);
1645 END IF;
1646 raise PROCESS_ERROR;
1647 end if;
1648 IF PG_DEBUG <> 0 THEN
1649 oe_debug_pub.add('create_reservation: ' || 'Success in query_tree.', 1);
1650
1651 oe_debug_pub.add('create_reservation: ' || 'l_x_qoh: ' || to_char(l_x_qoh));
1652
1653 oe_debug_pub.add('create_reservation: ' || 'l_x_rqoh: ' || to_char(l_x_rqoh));
1654
1655 oe_debug_pub.add('create_reservation: ' || 'x_available_qty: ' || to_char(x_available_qty));
1656
1657 oe_debug_pub.add('create_reservation: ' || ' config id ' || to_char(p_config_id ) ||
1658 ' mfg_org_id ' || to_char( p_mfg_org_id ) , 1);
1659 END IF;
1660
1661 l_stmt_num := 170;
1662
1663 select msi.primary_uom_code
1664 into l_primary_uom_code
1665 from mtl_system_items msi
1666 where msi.inventory_item_id = p_config_id
1667 and msi.organization_id = p_mfg_org_id;
1668
1669 IF PG_DEBUG <> 0 THEN
1670 oe_debug_pub.add('create_reservation: ' || ' pri uom ' || l_primary_uom_code ||
1671 ' res uom ' || p_reservation_uom_code , 1);
1672 END IF;
1673
1674
1675 /*------------------------------------------------------
1676 The quantity query gives ATR in the primary uom code
1677 so we need to convert it to the same uom as the
1678 p_reservation_uom_code.
1679 +------------------------------------------------------*/
1680 IF (l_primary_uom_code <> p_reservation_uom_code) THEN
1681 l_stmt_num := 175;
1682 x_available_qty := INV_CONVERT.inv_um_convert
1683 ( item_id => p_config_id,
1684 precision => 5, -- bugfix 2204376: pass precision of 5
1685 from_quantity => x_available_qty, -- from qty
1686 from_unit => l_primary_uom_code, -- from uom
1687 to_unit => p_reservation_uom_code, -- to uom
1688 from_name => null,
1689 to_name => null);
1690 END IF;
1691
1692 /*---------------------------------------------------------+
1693 p_automatic_reservation is TRUE when match and reserve is
1694 called from Order Import. From Order Import, if a match
1695 is found, a reservation is made automatically if there
1696 is sufficient quantity.
1697 +---------------------------------------------------------*/
1698 if (x_available_qty > 0 )
1699 then
1700 l_stmt_num := 180;
1701 IF PG_DEBUG <> 0 THEN
1702 oe_debug_pub.add('create_reservation: ' || 'Entering Create Reservation. ',1);
1703
1704 oe_debug_pub.add('create_reservation: ' || 'Quantity to Rsrv: '
1705 || to_char(l_quantity_to_reserve ),1);
1706
1707 oe_debug_pub.add('create_reservation: ' || 'Quantity Available to Rsrv: '
1708 || to_char(x_available_qty),1);
1709 END IF;
1710
1711 if( l_quantity_to_reserve > x_available_qty ) then
1712
1713 l_quantity_to_reserve := x_available_qty ;
1714
1715 l_partial_reservation := TRUE ;
1716
1717 IF PG_DEBUG <> 0 THEN
1718 oe_debug_pub.add('create_reservation: ' || 'Going to attempt reservation for' ||
1719 to_char(l_quantity_to_reserve ));
1720 END IF;
1721
1722
1723 end if ;
1724
1725 l_stmt_num := 185;
1726
1727 IF PG_DEBUG <> 0 THEN
1728 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1729 END IF;
1730
1731
1732 if (CTO_MATCH_AND_RESERVE.create_config_reservation
1733 (p_model_line_id => p_top_model_line_id,
1734 p_config_item_id => p_config_id,
1735 p_quantity_to_reserve => l_quantity_to_reserve,
1736 p_reservation_uom_code => p_reservation_uom_code,
1737 --Bugfix 12374440
1738 p_subinventory_code => l_subinventory_code,
1739 x_quantity_reserved => x_quantity_reserved,
1740 x_error_msg => l_x_error_msg,
1741 x_error_msg_name => l_x_error_msg_name) = TRUE)
1742 then
1743 IF PG_DEBUG <> 0 THEN
1744 oe_debug_pub.add('create_reservation: ' || 'Success in Create Reservation. ',1);
1745
1746 oe_debug_pub.add('create_reservation: ' || 'Matching Config Item: ' || to_char(p_config_id),1 );
1747
1748 oe_debug_pub.add ('create_reservation: ' || 'Quantity On-Hand: ' || to_char(x_available_qty),1);
1749
1750 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1751 END IF;
1752
1753
1754 l_stmt_num := 190;
1755
1756 select segment1
1757 into l_config_item_name
1758 from mtl_system_items
1759 where inventory_item_id = p_config_id
1760 and organization_id = p_mfg_org_id ;
1761
1762
1763 l_stmt_num := 195;
1764
1765 /*
1766 BUG 1870761 commented for some time as
1767 mtl_organizations view has severe performance
1768 issues in TST115 environment 07-10-2001
1769
1770 select organization_name into l_organization_name
1771 from mtl_organizations
1772 where organization_id = p_mfg_org_id ;
1773
1774 */
1775
1776 /* reintroduced the organization_name in the message
1777 ** as per bug#2320488 by using table
1778 ** inv_organization_name_v
1779 */
1780 begin
1781
1782 select organization_name into l_organization_name
1783 from inv_organization_name_v
1784 where organization_id = p_mfg_org_id ;
1785
1786 exception
1787 when others then
1788
1789 l_organization_name := to_char( p_mfg_org_id ) ;
1790
1791 end ;
1792
1793
1794
1795
1796 IF PG_DEBUG <> 0 THEN
1797 oe_debug_pub.add('create_reservation: ' || 'CTO_RESERVATION_SUCCESS for ' ||
1798 to_char(l_quantity_to_reserve) ||
1799 ' units for item ' || l_config_item_name ||
1800 ' in org ' || l_organization_name , 1 );
1801 END IF;
1802
1803
1804 l_stmt_num := 200;
1805
1806 if( p_mode = 'ONLINE' ) then
1807
1808
1809 l_token(1).token_name := 'QUANTITY';
1810 l_token(1).token_value := l_quantity_to_reserve;
1811 l_token(2).token_name := 'CONFIG_ITEM';
1812 l_token(2).token_value := l_config_item_name;
1813 l_token(3).token_name := 'SHIP_ORG';
1814 l_token(3).token_value := l_organization_name;
1815
1816
1817 cto_msg_pub.cto_message('BOM', 'CTO_RESERVATION_SUCCESS', l_token );
1818 --fnd_message.set_token('QUANTITY', l_quantity_to_reserve );
1819 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1820 --fnd_message.set_token('SHIP_ORG', l_organization_name );
1821
1822 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1823
1824
1825 OE_STANDARD_WF.Save_Messages;
1826 OE_STANDARD_WF.Clear_Msg_Context;
1827
1828 end if ;
1829
1830
1831 l_stmt_num := 205;
1832
1833 if( l_partial_reservation ) then
1834 x_reserve_status := 'PARTIAL' ;
1835 else
1836 x_reserve_status := 'COMPLETE' ;
1837 end if ;
1838
1839 x_message_name := 'CTO_RESERVE';
1840
1841 else
1842 IF PG_DEBUG <> 0 THEN
1843 oe_debug_pub.add('create_reservation: ' || 'Failed in Create Reservation. ',1);
1844 END IF;
1845 cto_msg_pub.cto_message('BOM', l_x_error_msg ); /* BUGFIX#2342412 */
1846 raise RESERVATION_ERROR;
1847 end if;
1848
1849 else
1850 /*--------------------------------------------------+
1851 If available quantity to reserve is less than
1852 zero, return with no option to reserve.
1853 Otherwise, user has the option to reserve against
1854 the ATR quantity.
1855 +--------------------------------------------------*/
1856
1857 l_stmt_num := 210;
1858
1859 IF PG_DEBUG <> 0 THEN
1860 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1861
1862 oe_debug_pub.add('create_reservation: ' || 'Not Enough Qty to reserve. ',1);
1863
1864 oe_debug_pub.add('create_reservation: ' || 'Quantity Available to Rsrv: '
1865 || to_char(x_available_qty),1);
1866 END IF;
1867
1868 x_message_name := 'CTO_CONFIG_LINKED';
1869 x_error_message := 'Config Item Linked. No Qty to Rsrv';
1870
1871 l_stmt_num := 215;
1872
1873 select segment1
1874 into l_config_item_name
1875 from mtl_system_items
1876 where inventory_item_id = p_config_id
1877 and organization_id = p_mfg_org_id ;
1878
1879
1880 l_stmt_num := 220;
1881
1882 /*
1883 BUG 1870761 commented for some time as
1884 mtl_organizations view has severe performance
1885 issues in TST115 environment 07-10-2001
1886
1887 select organization_name into l_organization_name
1888 from mtl_organizations
1889 where organization_id = p_mfg_org_id ;
1890
1891 */
1892
1893 /* reintroduced the organization_name in the message
1894 ** as per bug#2320488 by using table
1895 ** inv_organization_name_v
1896 */
1897 begin
1898
1899 select organization_name into l_organization_name
1900 from inv_organization_name_v
1901 where organization_id = p_mfg_org_id ;
1902
1903 exception
1904 when others then
1905
1906 l_organization_name := to_char( p_mfg_org_id ) ;
1907
1908 end ;
1909
1910
1911
1912 IF PG_DEBUG <> 0 THEN
1913 oe_debug_pub.add('create_reservation: ' || 'CTO_RESERVATION_FAILURE for ' || l_config_item_name ||
1914 ' in org ' || l_organization_name , 1 );
1915 END IF;
1916
1917 l_stmt_num := 225;
1918
1919 if( p_mode = 'ONLINE' ) then
1920
1921 l_token(1).token_name := 'CONFIG_ITEM';
1922 l_token(1).token_value := l_config_item_name;
1923 l_token(2).token_name := 'SHIP_ORG';
1924 l_token(2).token_value := l_organization_name;
1925
1926 cto_msg_pub.cto_message('BOM', 'CTO_RESERVATION_FAILURE', l_token);
1927 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1928 --fnd_message.set_token('SHIP_ORG', l_organization_name );
1929
1930 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1931
1932 OE_STANDARD_WF.Save_Messages;
1933 OE_STANDARD_WF.Clear_Msg_Context;
1934
1935 end if;
1936
1937 x_reserve_status := 'NOQTY' ;
1938
1939 end if ;
1940
1941
1942 else
1943
1944 l_stmt_num := 230;
1945
1946 IF PG_DEBUG <> 0 THEN
1947 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1948
1949 oe_debug_pub.add('create_reservation: ' || 'No reservation could be attempted for ' ||
1950 l_config_item_name , 1 );
1951 END IF;
1952
1953
1954 select segment1
1955 into l_config_item_name
1956 from mtl_system_items
1957 where inventory_item_id = p_config_id
1958 and organization_id = p_mfg_org_id ;
1959 /* fixed bug 1853597 to retrieve only one row for each item */
1960
1961 l_stmt_num := 235;
1962
1963 IF PG_DEBUG <> 0 THEN
1964 oe_debug_pub.add('create_reservation: ' || 'CTO_CONFIG_MATCH for item ' || l_config_item_name , 1 );
1965 END IF;
1966
1967 if( p_mode = 'ONLINE' ) then
1968
1969 l_token(1).token_name := 'CONFIG_ITEM';
1970 l_token(1).token_value := l_config_item_name;
1971
1972 cto_msg_pub.cto_message('BOM', 'CTO_CONFIG_MATCH', l_token);
1973 --fnd_message.set_token('CONFIG_ITEM', l_config_item_name );
1974
1975 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
1976
1977 OE_STANDARD_WF.Save_Messages;
1978 OE_STANDARD_WF.Clear_Msg_Context;
1979
1980 end if ;
1981
1982 x_reserve_status := 'MATCH' ;
1983
1984 end if ; /* check for full match */
1985
1986
1987 IF PG_DEBUG <> 0 THEN
1988 oe_debug_pub.add('create_reservation: ' || 'Time Stamp ' || to_char( sysdate , 'dd-mon-yyyy hh24:mi:ss' ));
1989 END IF;
1990
1991
1992 EXCEPTION
1993 when RESERVATION_ERROR then
1994 --rollback to savepoint before_item_creation;
1995 IF PG_DEBUG <> 0 THEN
1996 OE_DEBUG_PUB.add('create_reservation: ' || 'RESERVATION_ERROR ' , 1 );
1997
1998 OE_DEBUG_PUB.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation ' || to_char(l_stmt_num) );
1999 END IF;
2000 OE_STANDARD_WF.Save_Messages;
2001 OE_STANDARD_WF.Clear_Msg_Context;
2002
2003 x_return_status := FND_API.G_RET_STS_ERROR ;
2004
2005
2006 when NO_DATA_FOUND then
2007 --rollback to savepoint before_item_creation;
2008 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ITEM_ERROR');
2009 IF PG_DEBUG <> 0 THEN
2010 oe_debug_pub.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation::ndf::' || to_char(l_stmt_num) );
2011 END IF;
2012 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2013
2014
2015 when PROCESS_ERROR then
2016 --rollback to savepoint before_item_creation;
2017
2018 IF PG_DEBUG <> 0 THEN
2019 OE_DEBUG_PUB.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation ' || to_char(l_stmt_num) );
2020 END IF;
2021
2022 OE_STANDARD_WF.Save_Messages;
2023 OE_STANDARD_WF.Clear_Msg_Context;
2024
2025 x_return_status := FND_API.G_RET_STS_ERROR ;
2026
2027 when FND_API.G_EXC_UNEXPECTED_ERROR then
2028 --rollback to savepoint before_item_creation;
2029 IF PG_DEBUG <> 0 THEN
2030 OE_DEBUG_PUB.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation ' ||
2031 to_char(l_stmt_num) || ':' ||
2032 l_x_error_msg);
2033 END IF;
2034 OE_STANDARD_WF.Save_Messages;
2035 OE_STANDARD_WF.Clear_Msg_Context;
2036
2037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2038
2039 when OTHERS then
2040 --rollback to savepoint before_item_creation;
2041 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_ITEM_ERROR');
2042 IF PG_DEBUG <> 0 THEN
2043 oe_debug_pub.add('create_reservation: ' || 'CTO_WORKFLOW.create_reservation' ||
2044 to_char(l_stmt_num) || ':' ||
2045 substrb(sqlerrm, 1, 100));
2046 END IF;
2047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2048
2049
2050 END CREATE_RESERVATION ;
2051
2052
2053
2054
2055 /*============================================================================
2056 Procedure: rsv_before_booking_wf
2057 Description: This works only for an ATO item .
2058 This procedure gets called just before "Create supply order Eligble"
2059 activity in the ATO workflow.
2060 The format follows the standard Workflow API format.
2061
2062 Parameters:
2063 =============================================================================*/
2064
2065 PROCEDURE rsv_before_booking_wf (
2066 p_itemtype in VARCHAR2, /* item type */
2067 p_itemkey in VARCHAR2, /* config line id */
2068 p_actid in NUMBER, /* ID number of WF activity */
2069 p_funcmode in VARCHAR2, /* execution mode of WF activity */
2070 x_result out NoCopy VARCHAR2 /* result of activity */
2071 )
2072 IS
2073
2074 l_stmt_num NUMBER;
2075 l_ResultStatus boolean;
2076 l_msg_count number;
2077 l_msg_data varchar2(2000);
2078 l_return_status varchar2(1);
2079 return_value INTEGER;
2080
2081 --start bug#1861812
2082
2083 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
2084 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
2085
2086
2087 v_header_id oe_order_lines_all.header_id%type ;
2088 v_config_line_id oe_order_lines_all.line_id%type ;
2089
2090
2091 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
2092 l_hold_release_rec OE_Holds_PVT.Hold_release_REC_type;
2093
2094 l_x_hold_result_out Varchar2(30);
2095 l_x_hold_return_status Varchar2(30);
2096 l_x_error_msg_count Number;
2097 l_x_error_msg Varchar2(2000);
2098
2099 v_aps_version number ;
2100 BEGIN
2101 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2102 IF PG_DEBUG <> 0 THEN
2103 oe_debug_pub.add('rsv_before_booking_wf: ' || 'CTO Activity: Check Reservation before booking activity ', 1);
2104 END IF;
2105
2106 if (p_funcmode = 'RUN') then
2107
2108
2109 --
2110 -- start bugfix 1861812
2111 --
2112 l_stmt_num := 98;
2113 SELECT item_type_code, ato_line_id , header_id, line_id
2114 INTO v_item_type_code, v_ato_line_id, v_header_id , v_config_line_id
2115 FROM oe_order_lines_all
2116 WHERE line_id = p_itemkey;
2117
2118
2119
2120
2121
2122 /* Check for Activity Hold and convert it to regular hold
2123 ** Check where create_supply hold exists on the config line. Remove the create_supply hold
2124 ** apply regular AutoCreate Config Exception Hold.
2125 */
2126
2127
2128
2129
2130
2131
2132
2133 v_aps_version := msc_atp_global.get_aps_version ;
2134
2135 oe_debug_pub.add('link_config: ' || 'APS version::'|| v_aps_version , 2);
2136
2137 if( v_aps_version = 10 ) then
2138
2139
2140 oe_debug_pub.add( '*************************CHECKING HOLDS IN CHECK_RESERVATION_BEFORE_BOOKING ACTIVITY************ ' , 1) ;
2141
2142
2143
2144 OE_HOLDS_PUB.Check_Holds (
2145 p_api_version => 1.0
2146 ,p_line_id => v_config_line_id
2147 ,p_hold_id => 61
2148 ,p_wf_item => 'OEOL'
2149 ,p_wf_activity => 'CREATE_SUPPLY'
2150 ,p_chk_act_hold_only => 'Y'
2151 ,x_result_out => l_x_hold_result_out
2152 ,x_return_status => l_x_hold_return_status
2153 ,x_msg_count => l_x_error_msg_count
2154 ,x_msg_data => l_x_error_msg);
2155
2156 IF (l_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
2157 IF PG_DEBUG <> 0 THEN
2158 oe_debug_pub.add('CTOCITMB:Failed in Check Holds with expected error.' ,1);
2159 END IF;
2160 raise FND_API.G_EXC_ERROR;
2161
2162 ELSIF (l_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2163 IF PG_DEBUG <> 0 THEN
2164 oe_debug_pub.add('CTOCITMB:Failed in Check Holds with unexpected error.' ,1);
2165 END IF;
2166 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2167
2168 ELSE
2169 IF PG_DEBUG <> 0 THEN
2170 oe_debug_pub.add('CTOCITMB:Success in Check Holds.' ,1);
2171 END IF;
2172
2173 if l_x_hold_result_out = FND_API.G_TRUE then
2174 IF PG_DEBUG <> 0 THEN
2175 oe_debug_pub.add('CTOCITMB:Create Supply Activity Hold exists on Config Line .' ,1);
2176 END IF;
2177
2178 l_hold_source_rec.hold_entity_code := 'O';
2179 l_hold_source_rec.hold_id := 61 ;
2180 l_hold_source_rec.hold_entity_id := v_header_id;
2181 l_hold_source_rec.header_id := v_header_id;
2182 l_hold_source_rec.line_id := v_config_line_id ;
2183
2184 l_hold_release_rec.release_reason_code :='CTO_AUTOMATIC';
2185 --set created_by = 1 to indicate automatic hold release
2186 l_hold_release_rec.created_by := 1;
2187
2188
2189 OE_HOLDS_PUB.Release_Holds (
2190 p_api_version => 1.0
2191 -- ,p_line_id => v_config_line_id
2192 -- ,p_hold_id => 1063
2193 ,p_hold_source_rec => l_hold_source_rec
2194 ,p_hold_release_rec => l_hold_release_rec
2195 ,x_return_status => l_x_hold_return_status
2196 ,x_msg_count => l_x_error_msg_count
2197 ,x_msg_data => l_x_error_msg);
2198
2199 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2200 IF PG_DEBUG <> 0 THEN
2201 oe_debug_pub.add('CTOCITMB:Failed in Release Holds with expected error.' ,1);
2202 END IF;
2203 raise FND_API.G_EXC_ERROR;
2204
2205 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2206 IF PG_DEBUG <> 0 THEN
2207 oe_debug_pub.add('CTOCITMB:Failed in Release Holds with unexpected error.' ,1);
2208 END IF;
2209 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2210 END IF;
2211
2212 IF PG_DEBUG <> 0 THEN
2213 oe_debug_pub.add('CTOCITMB: Hold Released on config line.' ,1);
2214 END IF;
2215
2216
2217
2218
2219 IF PG_DEBUG <> 0 THEN
2220 oe_debug_pub.add('CTOCITMB:Calling OM api to apply hold.' ,1);
2221 END IF;
2222
2223 l_hold_source_rec.hold_entity_code := 'O';
2224 l_hold_source_rec.hold_id := 55 ;
2225 l_hold_source_rec.hold_entity_id := v_header_id;
2226 l_hold_source_rec.header_id := v_header_id;
2227 l_hold_source_rec.line_id := v_config_line_id;
2228
2229 OE_Holds_PUB.Apply_Holds (
2230 p_api_version => 1.0
2231 , p_hold_source_rec => l_hold_source_rec
2232 , x_return_status => l_return_status
2233 , x_msg_count => l_msg_count
2234 , x_msg_data => l_msg_data);
2235
2236 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2237 IF PG_DEBUG <> 0 THEN
2238 oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with expected error.' ,1);
2239 END IF;
2240 raise FND_API.G_EXC_ERROR;
2241
2242 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2243 IF PG_DEBUG <> 0 THEN
2244 oe_debug_pub.add('CTOCITMB:Failed in Apply Holds with unexpected error.' ,1);
2245 END IF;
2246 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2247 END IF;
2248
2249 IF PG_DEBUG <> 0 THEN
2250 oe_debug_pub.add('CTOCITMB: An Exception Hold applied to config line.' ,1);
2251 END IF;
2252
2253 else
2254
2255
2256 IF PG_DEBUG <> 0 THEN
2257 oe_debug_pub.add('CTOCITMB:Create Supply Activity Hold does not exist on Config Line .' ,1);
2258 END IF;
2259
2260 end if; /* if activity hold exists */
2261
2262
2263 END IF ; /* success in check holds */
2264
2265 end if ; /* check for aps version */
2266
2267
2268 --if an ato item
2269
2270 IF ((upper(v_item_type_code) = 'STANDARD')
2271 OR (upper(v_item_type_code) = 'OPTION') --bug#1874380
2272 --Adding INCLUDED item type code for SUN ER#9793792
2273 OR (upper(v_item_type_code) = 'INCLUDED')
2274 )
2275 AND (v_ato_line_id = p_itemkey)
2276 THEN
2277
2278 --end bug#1861812
2279
2280 /*-------------------------------------------------------------+
2281 1.call procedure check_reservation_exists_ato_item to see
2282 if the reservation exists before "create supply order eligible" activity.
2283 2.If the reservation exists , the work flow goes to "Ship_line" status
2284 3.If reservation does not exists then the workflow goes to "Create
2285 Supply Order Eligible" status.
2286
2287 +--------------------------------------------------------------*/
2288 l_stmt_num := 99;
2289 /* Bugfix 3075105: Instead of check_inv_rsv_exists, call check_rsv_exists
2290 to check all reservations. If a reservation is found, progress thru
2291 the Reserved path.
2292
2293 check_inv_rsv_exists(to_number(p_itemkey),
2294 l_ResultStatus,
2295 l_msg_count,
2296 l_msg_data,
2297 l_return_status);
2298 **/
2299 -- Bugfix 3075105 begin
2300 check_rsv_exists(to_number(p_itemkey),
2301 l_ResultStatus,
2302 l_msg_count,
2303 l_msg_data,
2304 l_return_status);
2305 -- Bugfix 3075105 end
2306
2307 if ((l_ResultStatus=TRUE) and (l_return_status=FND_API.G_RET_STS_SUCCESS)) then
2308
2309 IF PG_DEBUG <> 0 THEN
2310 oe_debug_pub.add('rsv_before_booking_wf: ' || 'Reservation exists, completing flow with reserved', 2);
2311 END IF;
2312 x_result :='COMPLETE:RESERVED';
2313
2314 --
2315 --below code calls display_wf_status to update the correct
2316 --before booking and scheduling if item is reserved
2317 --
2318 l_stmt_num := 100;
2319 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(to_number(p_itemkey));
2320
2321 if return_value <> 1 then
2322 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
2323 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2324 end if;
2325
2326 elsif ((l_ResultStatus=FALSE) and (l_return_status=FND_API.G_RET_STS_SUCCESS)) then
2327 IF PG_DEBUG <> 0 THEN
2328 oe_debug_pub.add('rsv_before_booking_wf: ' || 'Reservation does not exist, completing flow with complete.', 2);
2329 END IF;
2330 x_result :='COMPLETE';
2331
2332 elsif(l_return_status=FND_API.G_RET_STS_ERROR) then
2333 IF PG_DEBUG <> 0 THEN
2334 oe_debug_pub.add('rsv_before_booking_wf: ' || 'returning from check_rsv_exists with expected error.', 2);
2335 END IF;
2336 RAISE FND_API.G_EXC_ERROR;
2337
2338 elsif(l_return_status=FND_API.G_RET_STS_UNEXP_ERROR) then
2339 IF PG_DEBUG <> 0 THEN
2340 oe_debug_pub.add('rsv_before_booking_wf: ' || 'returning from check_rsv_exists with unexpected error.', 2);
2341 END IF;
2342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2343
2344 end if;
2345 --start bug#1861812
2346
2347 --
2348 --if not an ato item complete with default flow
2349 --
2350 ELSE
2351 x_result :='COMPLETE';
2352 END IF;
2353 --end bug#1861812
2354
2355 end if ; /*p_funcmode ='RUN"*/
2356
2357 OE_STANDARD_WF.Save_Messages;
2358 OE_STANDARD_WF.Clear_Msg_Context;
2359
2360 EXCEPTION
2361 when FND_API.G_EXC_ERROR then
2362 IF PG_DEBUG <> 0 THEN
2363 OE_DEBUG_PUB.add('rsv_before_booking_wf: ' || 'CTO_WORKFLOW.rsv_before_booking_wf ' || to_char(l_stmt_num) );
2364 END IF;
2365 OE_STANDARD_WF.Save_Messages;
2366 OE_STANDARD_WF.Clear_Msg_Context;
2367 raise; -- can be re-tried
2368
2369 when FND_API.G_EXC_UNEXPECTED_ERROR then
2370 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2371 FND_MSG_PUB.Add_Exc_Msg
2372 (G_PKG_NAME
2373 ,'rsv_before_booking_wf'
2374 );
2375 end if;
2376
2377 IF PG_DEBUG <> 0 THEN
2378 oe_debug_pub.add('rsv_before_booking_wf: ' || 'corresponds to unexpected error at called program check_inv_rsv_exists '||'
2379 l_stmt_num :'|| l_stmt_num ||sqlerrm, 1);
2380 END IF;
2381 OE_STANDARD_WF.Save_Messages;
2382 OE_STANDARD_WF.Clear_Msg_Context;
2383 wf_core.context('CTO_WORKFLOW', 'rsv_before_booking_wf', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2384 raise;
2385
2386 when OTHERS then
2387 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
2388 FND_MSG_PUB.Add_Exc_Msg
2389 (G_PKG_NAME
2390 ,'rsv_before_booking_wf'
2391 );
2392 end if;
2393 IF PG_DEBUG <> 0 THEN
2394 oe_debug_pub.add('rsv_before_booking_wf: ' || 'error at rsv_before_booking_wf ' || to_char(l_stmt_num)|| sqlerrm);
2395 END IF;
2396 /*-------------------------------------------+
2397 Error Information for Notification.
2398 +--------------------------------------------*/
2399 wf_core.context('CTO_WORKFLOW','rsv_before_booking_wf',p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2400 raise;
2401
2402 END rsv_before_booking_wf;
2403
2404
2405
2406
2407 /*============================================================================
2408 Procedure: check_inv_rsv_exists
2409 Description:
2410 This procedure is being called from rsv_before_booking_wf API.
2411 This checks if inventory reservations exist for an
2412 ATO item before booking
2413 ===========================================================================*/
2414 PROCEDURE check_inv_rsv_exists
2415 (
2416 pLineId in number ,
2417 x_ResultStatus out NoCopy boolean ,
2418 x_msg_count out NoCopy number ,
2419 x_msg_data out NoCopy varchar2,
2420 x_return_status out NoCopy varchar2
2421 )
2422
2423 is
2424
2425 lReserveId number;
2426
2427 BEGIN
2428
2429 select reservation_id
2430 into lReserveId
2431 from mtl_reservations mr,
2432 oe_order_lines_all oel,
2433 oe_order_headers_all oeh,
2434 oe_transaction_types_all ota,
2435 oe_transaction_types_tl otl,
2436 mtl_sales_orders mso
2437 where mr.demand_source_line_id = oel.line_id --ato item line id
2438 and oel.line_id = pLineId
2439 and oeh.header_id = oel.header_id
2440 and oeh.order_type_id = ota.transaction_type_id
2441 and ota.transaction_type_code='ORDER'
2442 and ota.transaction_type_id = otl.transaction_type_id
2443 and oeh.order_number = mso.segment1
2444 and otl.name = mso.segment2
2445 and otl.language = (select language_code
2446 from fnd_languages
2447 where installed_flag = 'B')
2448 and mso.sales_order_id = mr.demand_source_header_id
2449 --and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
2450 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
2451 INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
2452 INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
2453 and mr.reservation_quantity > 0
2454 and supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_inv
2455 and rownum = 1;
2456
2457 IF PG_DEBUG <> 0 THEN
2458 oe_debug_pub.add ('check_inv_rsv_exists: ' || 'found that reservation exists before booking', 1);
2459 END IF;
2460 x_ResultStatus := TRUE;
2461 x_return_status := FND_API.G_RET_STS_SUCCESS;
2462
2463 EXCEPTION
2464
2465 when no_data_found then
2466 x_ResultStatus := FALSE;
2467 x_return_status := FND_API.G_RET_STS_SUCCESS;
2468 IF PG_DEBUG <> 0 THEN
2469 oe_debug_pub.add ('check_inv_rsv_exists: ' || 'no reservations before booking, this is not an error', 1);
2470 END IF;
2471
2472 when others then
2473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2474 IF PG_DEBUG <> 0 THEN
2475 oe_debug_pub.add ('check_inv_rsv_exists: ' || 'unexpected error in called program check_inv_rsv_exists'|| sqlerrm , 1);
2476 END IF;
2477 if fnd_msg_pub.check_msg_level
2478 (fnd_msg_pub.g_msg_lvl_unexp_error)
2479 then
2480 fnd_msg_pub.Add_Exc_msg
2481 ( 'CTO_WORKFLOW',
2482 'check_inv_rsv_exists'
2483 );
2484 end if;
2485 cto_msg_pub.count_and_get
2486 (
2487 p_msg_count=>x_msg_count,
2488 p_msg_data=>x_msg_data
2489 );
2490 end check_inv_rsv_exists;
2491
2492
2493 --begin bugfix 3075105
2494 /*============================================================================
2495 Procedure: check_rsv_exists
2496 Description:
2497 This procedure is being called from rsv_before_booking_wf API.
2498 This checks if inventory reservations exist for an
2499 ATO item before booking
2500 ===========================================================================*/
2501 PROCEDURE check_rsv_exists
2502 (
2503 pLineId in number ,
2504 x_ResultStatus out NoCopy boolean ,
2505 x_msg_count out NoCopy number ,
2506 x_msg_data out NoCopy varchar2,
2507 x_return_status out NoCopy varchar2
2508 )
2509
2510 is
2511
2512 lRsvCount number := 0;
2513 lFloCount number := 0;
2514
2515 BEGIN
2516
2517 select count(*)
2518 into lRsvCount
2519 from mtl_reservations mr,
2520 oe_order_lines_all oel,
2521 oe_order_headers_all oeh,
2522 oe_transaction_types_all ota,
2523 oe_transaction_types_tl otl,
2524 mtl_sales_orders mso
2525 where mr.demand_source_line_id = oel.line_id --ato item line id
2526 and oel.line_id = pLineId
2527 and oeh.header_id = oel.header_id
2528 and oeh.order_type_id = ota.transaction_type_id
2529 and ota.transaction_type_code='ORDER'
2530 and ota.transaction_type_id = otl.transaction_type_id
2531 and oeh.order_number = mso.segment1
2532 and otl.name = mso.segment2
2533 and otl.language = (select language_code
2534 from fnd_languages
2535 where installed_flag = 'B')
2536 and mso.sales_order_id = mr.demand_source_header_id
2537 --and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
2538 and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
2539 INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
2540 INV_RESERVATION_GLOBAL.g_source_type_oe)
2541 and mr.reservation_quantity > 0
2542 and rownum = 1;
2543
2544
2545 if lRsvCount = 0 then
2546 -- Check to see if reservns exist in wip_flow_schedules
2547 select count(*)
2548 into lFloCount
2549 from wip_flow_schedules
2550 where demand_source_type = inv_reservation_global.g_source_type_oe
2551 and demand_source_line = to_char(pLineId)
2552 and status <> 2; -- Flow Schedule status : 1 = Open 2 = Closed/Completed
2553 else
2554 x_ResultStatus := TRUE;
2555 x_return_status := FND_API.G_RET_STS_SUCCESS;
2556 IF PG_DEBUG <> 0 THEN
2557 oe_debug_pub.add ('check_rsv_exists: ' || 'MTL reservation exists before booking', 1);
2558 END IF;
2559 return;
2560 end if;
2561
2562 if lFloCount > 0 then
2563 x_ResultStatus := TRUE;
2564 x_return_status := FND_API.G_RET_STS_SUCCESS;
2565 IF PG_DEBUG <> 0 THEN
2566 oe_debug_pub.add ('check_rsv_exists: ' || 'FLOW reservation exists before booking', 1);
2567 END IF;
2568 else
2569 x_ResultStatus := FALSE;
2570 x_return_status := FND_API.G_RET_STS_SUCCESS;
2571 IF PG_DEBUG <> 0 THEN
2572 oe_debug_pub.add ('check_rsv_exists: ' || 'NO reservations before booking, this is not an error', 1);
2573 END IF;
2574 end if;
2575
2576 EXCEPTION
2577
2578 when no_data_found then
2579 x_ResultStatus := FALSE;
2580 x_return_status := FND_API.G_RET_STS_SUCCESS;
2581 IF PG_DEBUG <> 0 THEN
2582 oe_debug_pub.add ('check_rsv_exists: ' || 'no reservations before booking, this is not an error', 1);
2583 END IF;
2584
2585 when others then
2586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2587 IF PG_DEBUG <> 0 THEN
2588 oe_debug_pub.add ('check_rsv_exists: ' || 'unexpected error in called program check_inv_rsv_exists'|| sqlerrm , 1);
2589 END IF;
2590 if fnd_msg_pub.check_msg_level
2591 (fnd_msg_pub.g_msg_lvl_unexp_error)
2592 then
2593 fnd_msg_pub.Add_Exc_msg
2594 ( 'CTO_WORKFLOW',
2595 'check_inv_rsv_exists'
2596 );
2597 end if;
2598 cto_msg_pub.count_and_get
2599 (
2600 p_msg_count=>x_msg_count,
2601 p_msg_data=>x_msg_data
2602 );
2603 end check_rsv_exists;
2604
2605 --end bugfix 3075105
2606
2607 /*============================================================================
2608 obsolete ?
2609 Procedure: check_reservation_status_wf
2610 Description: This procedure gets called when executing the
2611 Check Reservation activity in the ATO workflow. The
2612 format is follows the standard Workflow API format.
2613
2614 More to come...
2615 Parameters:
2616 =============================================================================*/
2617 PROCEDURE check_reservation_status_wf(
2618 p_itemtype in VARCHAR2, /* item type */
2619 p_itemkey in VARCHAR2, /* config line id */
2620 p_actid in number, /* ID number of WF activity */
2621 p_funcmode in VARCHAR2, /* execution mode of WF activity */
2622 x_result out NoCopy VARCHAR2 /* result of activity */
2623 )
2624 IS
2625
2626 l_stmt_num NUMBER;
2627 l_mfg_org_id NUMBER;
2628 l_config_item_id NUMBER;
2629 l_x_bill_seq_id NUMBER;
2630 l_status INTEGER;
2631 l_return_status VARCHAR2(1);
2632 l_header_id NUMBER;
2633 l_flow_status_code VARCHAR2(30);
2634 l_reserved_qty NUMBER;
2635 l_qty NUMBER;
2636
2637 PROCESS_ERROR EXCEPTION;
2638 UNEXP_ERROR EXCEPTION;
2639 BEGIN
2640 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2641 IF PG_DEBUG <> 0 THEN
2642 oe_debug_pub.add('check_reservation_status_wf: ' || 'CTO Activity: Check Reservation', 1);
2643 END IF;
2644
2645 if (p_funcmode = 'RUN') then
2646
2647 /*-------------------------------------------------------------+
2648 Check the status of the configuration line.
2649 2. If the Config BOM exists for this configuration
2650 item. This can happen if a match was performed.
2651 If the BOM exists, the workflow goes to "Create
2652 Supply Order Eligible" status.
2653 3. Otherwise, workflow goes to Create Mfg Config Data Eligible.
2654 +--------------------------------------------------------------*/
2655 l_stmt_num := 50;
2656 select oel.inventory_item_id, oel.ship_from_org_id,
2657 oel.header_id,
2658 --(oel.ordered_quantity - oel.cancelled_quantity) --bugfix 2017099
2659 oel.ordered_quantity
2660 into l_config_item_id, l_mfg_org_id, l_header_id,
2661 l_qty
2662 from oe_order_lines_all oel
2663 where oel.line_id = to_number(p_itemkey);
2664
2665 /*------------------------------------+
2666 Check if Config BOM exists.
2667 +------------------------------------*/
2668
2669 l_stmt_num := 110;
2670 IF PG_DEBUG <> 0 THEN
2671 oe_debug_pub.add('check_reservation_status_wf: ' || 'Check For Config BOM ', 1);
2672 END IF;
2673
2674 l_status := CTO_CONFIG_BOM_PK.check_bom(pItemId => l_config_item_id,
2675 pOrgId => l_mfg_org_id,
2676 xBillId => l_x_bill_seq_id);
2677
2678 IF (l_status = 1) THEN
2679 IF PG_DEBUG <> 0 THEN
2680 oe_debug_pub.add('check_reservation_status_wf: ' || 'Config Data Created.', 1);
2681 END IF;
2682 x_result := 'COMPLETE:CREATED';
2683 l_flow_status_code := 'BOM_AND_RTG_CREATED';
2684
2685 ELSE
2686
2687 l_stmt_num := 130;
2688 IF PG_DEBUG <> 0 THEN
2689 oe_debug_pub.add('check_reservation_status_wf: ' || 'Config Data Not Created.',1);
2690 END IF;
2691 x_result := 'COMPLETE';
2692 l_flow_status_code := 'ITEM_CREATED';
2693
2694 END IF;
2695
2696
2697 --
2698 -- It was agreed with OM that if we cannot get a lock
2699 -- on this line for update, we will not error out.
2700 --
2701
2702 l_stmt_num := 140;
2703 IF PG_DEBUG <> 0 THEN
2704 oe_debug_pub.add('check_reservation_status_wf: ' || 'Calling flow status API ',1);
2705 END IF;
2706 OE_Order_WF_Util.Update_Flow_Status_Code(
2707 p_header_id => l_header_id,
2708 p_line_id => to_number(p_itemkey),
2709 p_flow_status_code => l_flow_status_code,
2710 x_return_status => l_return_status);
2711
2712 IF PG_DEBUG <> 0 THEN
2713 oe_debug_pub.add('check_reservation_status_wf: ' || 'Return from flow status API '
2714 ||l_return_status,1);
2715 END IF;
2716
2717 end if; /* p_funcmode = 'RUN' */
2718
2719 OE_STANDARD_WF.Save_Messages;
2720 OE_STANDARD_WF.Clear_Msg_Context;
2721
2722 EXCEPTION
2723 when NO_DATA_FOUND then
2724 IF PG_DEBUG <> 0 THEN
2725 oe_debug_pub.add('check_reservation_status_wf: ' || 'Configuration Line Not Reserved.', 1);
2726 END IF;
2727 OE_STANDARD_WF.Save_Messages;
2728 OE_STANDARD_WF.Clear_Msg_Context;
2729 x_result := 'COMPLETE';
2730
2731 when PROCESS_ERROR then
2732 IF PG_DEBUG <> 0 THEN
2733 OE_DEBUG_PUB.add('check_reservation_status_wf: ' || 'CTO_WORKFLOW.check_reservation_status_wf ' ||
2734 to_char(l_stmt_num) || ':' ||
2735 substrb(sqlerrm, 1, 100));
2736 END IF;
2737 OE_STANDARD_WF.Save_Messages;
2738 OE_STANDARD_WF.Clear_Msg_Context;
2739
2740 when UNEXP_ERROR then
2741 IF PG_DEBUG <> 0 THEN
2742 OE_DEBUG_PUB.add('check_reservation_status_wf: ' || 'CTO_WORKFLOW.create_config_item_wf ' ||
2743 to_char(l_stmt_num) || ':' ||
2744 l_return_status);
2745 END IF;
2746 OE_STANDARD_WF.Save_Messages;
2747 OE_STANDARD_WF.Clear_Msg_Context;
2748 wf_core.context('CTO_WORKFLOW', 'create_config_item_wf',
2749 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2750 raise;
2751
2752 when OTHERS then
2753 cto_msg_pub.cto_message('BOM', 'CTO_CHECK_STATUS_ERROR');
2754 IF PG_DEBUG <> 0 THEN
2755 oe_debug_pub.add('check_reservation_status_wf: ' || 'CTO_WORKFLOW.check_reservation_status_wf ' ||
2756 to_char(l_stmt_num) || ':' ||
2757 substrb(sqlerrm, 1, 100));
2758 END IF;
2759 /*-------------------------------------------+
2760 Error Information for Notification.
2761 +--------------------------------------------*/
2762 wf_core.context('CTO_WORKFLOW', 'check_reservation_status_wf',
2763 p_itemtype, p_itemkey, to_char(p_actid),
2764 p_funcmode);
2765 raise;
2766 END check_reservation_status_wf;
2767
2768
2769
2770 --
2771 -- Procedure for multilevel testing
2772 -- To be renamed after tested completely
2773 --
2774
2775 PROCEDURE calculate_cost_rollup_wf_ml(
2776 p_itemtype in VARCHAR2, /*item type */
2777 p_itemkey in VARCHAR2, /* config line id */
2778 p_actid in number, /* ID number of WF activity */
2779 p_funcmode in VARCHAR2, /* execution mode of WF activity*/
2780 x_result out NoCopy VARCHAR2 /* result of activity */
2781 )
2782 IS
2783 l_stmt_num number := 0;
2784 l_x_msg_count number;
2785 l_x_msg_data varchar2(2000);
2786 l_top_ato_line_id number;
2787 l_status integer;
2788 UNEXP_ERROR exception;
2789
2790 BEGIN
2791 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2792 IF PG_DEBUG <> 0 THEN
2793 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'CTO Activity: Calculate Cost Rollup', 1);
2794 END IF;
2795
2796 if (p_funcmode = 'RUN') then
2797
2798 l_stmt_num := 135;
2799
2800 select oel.ato_line_id
2801 into l_top_ato_line_id
2802 from oe_order_lines_all oel
2803 where oel.line_id = to_number(p_itemkey);
2804
2805 IF PG_DEBUG <> 0 THEN
2806 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Cost rollup::top_ato_line_id::'||to_char(l_top_ato_line_id));
2807 END IF;
2808
2809 l_stmt_num := 140;
2810 l_status := CTO_CONFIG_COST_PK.cost_rollup_ml(
2811 pTopAtoLineId => l_top_ato_line_id,
2812 x_msg_count => l_x_msg_count,
2813 x_msg_data => l_x_msg_data);
2814
2815 if (l_status = 0) then
2816 IF PG_DEBUG <> 0 THEN
2817 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Failure in cost_rollup ', 1);
2818 END IF;
2819 cto_msg_pub.cto_message('BOM', l_x_msg_data);
2820 raise UNEXP_ERROR;
2821 else
2822 IF PG_DEBUG <> 0 THEN
2823 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'Success in cost_rollup ', 1);
2824 END IF;
2825 end if;
2826
2827 x_result := 'COMPLETE';
2828
2829 end if; /* end p_funcmode = 'RUN' */
2830
2831 OE_STANDARD_WF.Save_Messages;
2832 OE_STANDARD_WF.Clear_Msg_Context;
2833
2834 EXCEPTION
2835 when UNEXP_ERROR then
2836 IF PG_DEBUG <> 0 THEN
2837 OE_DEBUG_PUB.add('calculate_cost_rollup_wf_ml: ' || 'CTO_WORKFLOW.calculate_cost_rollup_wf' ||
2838 to_char(l_stmt_num) || ':' ||
2839 l_x_msg_data);
2840 END IF;
2841 OE_STANDARD_WF.Save_Messages;
2842 OE_STANDARD_WF.Clear_Msg_Context;
2843 wf_core.context('CTO_WORKFLOW', 'calculate_cost_rollup_wf',
2844 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2845 raise;
2846
2847 when NO_DATA_FOUND then
2848 IF PG_DEBUG <> 0 THEN
2849 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'CTO_WORKFLOW.calculate_cost_rollup_wf ' ||
2850 to_char(l_stmt_num) || ':' ||
2851 substrb(sqlerrm, 1, 100),1);
2852 END IF;
2853 cto_msg_pub.cto_message('BOM', 'CTO_CALC_COST_ROLLUP_ERROR');
2854 OE_STANDARD_WF.Save_Messages;
2855 OE_STANDARD_WF.Clear_Msg_Context;
2856
2857 when OTHERS then
2858 cto_msg_pub.cto_message('BOM', 'CTO_CALC_COST_ROLLUP_ERROR');
2859 IF PG_DEBUG <> 0 THEN
2860 oe_debug_pub.add('calculate_cost_rollup_wf_ml: ' || 'CTO_WORKFLOW.calculate_cost_rollup_wf ' ||
2861 to_char(l_stmt_num) || ':' ||
2862 substrb(sqlerrm, 1, 100),1);
2863 END IF;
2864 wf_core.context('CTO_WORKFLOW', 'calculate_cost_rollup_wf',
2865 p_itemtype, p_itemkey, to_char(p_actid),
2866 p_funcmode);
2867 raise;
2868
2869 END calculate_cost_rollup_wf_ml;
2870
2871
2872
2873
2874 /*============================================================================
2875 Procedure: set_parameter_lead_time_wf_ml
2876 Description: This procedure gets called when executing the Calculate
2877 Leadtime activity in the ATO workflow. The
2878 format is follows the standard Workflow API format.
2879
2880 More to come...
2881 Parameters:
2882 =============================================================================*/
2883 PROCEDURE set_parameter_lead_time_wf_ml(
2884 p_itemtype in VARCHAR2, /* workflow item type */
2885 p_itemkey in VARCHAR2, /* sales order line id */
2886 p_actid in number, /* ID number of WF activity */
2887 p_funcmode in VARCHAR2, /* execution mode of WF activity */
2888 x_result out NoCopy VARCHAR2 /* result of activity */
2889 )
2890 IS
2891
2892 l_stmt_num number := 0;
2893 l_config_item_id number;
2894 l_mfg_org_code varchar2(3);
2895 l_mfg_org_id number;
2896 l_item_name varchar2(40);
2897 l_x_error_msg_name varchar2(30);
2898 l_x_error_msg varchar2(2000);
2899 l_routing_count number;
2900 l_x_rtg_id number;
2901 l_x_rtg_type number;
2902
2903
2904 /* Variables for the Workflow Item Attributes */
2905 l_req_id number;
2906 lAtoLineId number;
2907
2908 l_status integer;
2909 l_x_error_msg_count number;
2910 l_x_hold_result_out varchar2(1);
2911 l_x_hold_return_status varchar2(1);
2912
2913 UNEXP_ERROR exception;
2914
2915 BEGIN
2916
2917 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2918 IF PG_DEBUG <> 0 THEN
2919 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'CTO Activity: Set Parameter lead Time', 1);
2920 END IF;
2921
2922 if (p_funcmode = 'RUN') then
2923
2924 /*-----------------------------------------------------+
2925 Prepare to calculate. Launch Calculate Manufacturing
2926 Lead Time concurrent program. Set Item Attributes
2927 as Parameters to Calculate Mfg Lead Time concurrent prg.
2928 +----------------------------------------------------*/
2929
2930 /*----------------------------------------------+
2931 Assign Parameter Values to Parameters for
2932 Concurrent Program.
2933 +----------------------------------------------*/
2934 -- Line ID - We are using the Org parameter from
2935 -- the 11.5.1 workflow activity.
2936
2937 select oel.ato_line_id
2938 into lAtoLineId
2939 from oe_order_lines_all oel
2940 where line_id = to_number(p_itemkey);
2941
2942 --wf_engine.SetItemAttrText(p_itemtype, p_itemkey,
2943 -- 'LEAD_TIME_ROLLUP_ORG', p_itemkey);
2944 wf_engine.SetItemAttrText(p_itemtype, p_itemkey,
2945 'LEAD_TIME_ROLLUP_ORG', to_char(lAtoLineId));
2946
2947 IF PG_DEBUG <> 0 THEN
2948 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'Line ID: ' || p_itemkey, 1);
2949
2950 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'ATO Line ID: ' || to_char(lAtoLineId), 1);
2951 END IF;
2952
2953 x_result := 'COMPLETE';
2954 end if; /* p_funcmode = 'RUN' */
2955
2956 OE_STANDARD_WF.Save_Messages;
2957 OE_STANDARD_WF.Clear_Msg_Context;
2958
2959 EXCEPTION
2960
2961 when UNEXP_ERROR then
2962 IF PG_DEBUG <> 0 THEN
2963 OE_DEBUG_PUB.add('set_parameter_lead_time_wf_ml: ' || 'CTO_WORKFLOW.set_parameter_lead_time_wf_ml' ||
2964 to_char(l_stmt_num) || ':' ||
2965 l_x_error_msg);
2966 END IF;
2967 OE_STANDARD_WF.Save_Messages;
2968 OE_STANDARD_WF.Clear_Msg_Context;
2969 wf_core.context('CTO_WORKFLOW', 'set_parameter_lead_time_wf_ml',
2970 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
2971 raise;
2972
2973 when NO_DATA_FOUND then
2974 IF PG_DEBUG <> 0 THEN
2975 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'CTO_WORKFLOW.set_parameter_lead_time_wf_ml'
2976 || to_char(l_stmt_num) || ':' ||
2977 substrb(sqlerrm, 1, 100),1);
2978 END IF;
2979 cto_msg_pub.cto_message('BOM', 'CTO_CALC_LEAD_TIME_ERROR');
2980 OE_STANDARD_WF.Save_Messages;
2981 OE_STANDARD_WF.Clear_Msg_Context;
2982
2983 when OTHERS then
2984 cto_msg_pub.cto_message('BOM', 'CTO_CALC_LEAD_TIME_ERROR');
2985 IF PG_DEBUG <> 0 THEN
2986 oe_debug_pub.add('set_parameter_lead_time_wf_ml: ' || 'CTO_WORKFLOW.set_parameter_lead_time_wf_ml'
2987 || to_char(l_stmt_num) || ':' ||
2988 substrb(sqlerrm, 1, 100),1);
2989 END IF;
2990 wf_core.context('CTO_WORKFLOW', 'set_parameter_lead_time_wf_ml',
2991 p_itemtype, p_itemkey, to_char(p_actid),
2992 p_funcmode);
2993 raise;
2994
2995 END set_parameter_lead_time_wf_ml;
2996
2997
2998 /*============================================================================
2999 Procedure: check_supply_type_wf
3000 Description: This procedure gets called when executing the Reserve
3001 Configuration activity in the ATO workflow. The
3002 format is follows the standard Workflow API format.
3003
3004 More to come...
3005 Parameters:
3006 =============================================================================*/
3007
3008
3009
3010 -- The following procedure is modified by Renga Kannan on 08/29/01
3011 -- This procedure now looks at the Buy ATO item and Config item also.
3012
3013
3014 PROCEDURE check_supply_type_wf(
3015 p_itemtype in VARCHAR2, /*item type */
3016 p_itemkey in VARCHAR2, /* config line id */
3017 p_actid in number, /* ID number of WF activity */
3018 p_funcmode in VARCHAR2, /* execution mode of WF activity*/
3019 x_result out NoCopy VARCHAR2 /* result of activity */
3020 )
3021 IS
3022 l_stmt_num number := 0;
3023 l_supply_type number;
3024
3025 l_msg_name varchar2(30);
3026 l_msg_txt varchar2(2000);
3027 l_msg_count number;
3028 l_inventory_item_id Mtl_system_items.inventory_item_id%type;
3029 l_ship_from_org_id Mtl_system_items.organization_id%type;
3030 l_item_type_code Oe_order_lines_all.item_type_code%type;
3031 x_return_status Varchar2(1);
3032 P_source_type Number;
3033 p_sourcing_rule_exists Varchar2(1);
3034
3035 p_transit_lead_time Number;
3036 x_exp_error_code Number;
3037
3038 l_source_type_code oe_order_lines.source_type_code%type ;
3039
3040 --added by kkonada OPM
3041 l_can_create_supply VARCHAR2(1);
3042 l_return_status VARCHAR2(1);
3043 l_msg_data VARCHAR2(2000);
3044 l_sourcing_org number;
3045 l_message Varchar2(100);
3046
3047 l_ret_stat number; --bugfix 4556596
3048 v_x_error_msg_count NUMBER;
3049 v_x_hold_result_out VARCHAR2(1);
3050 v_x_hold_return_status VARCHAR2(1);
3051 v_x_error_msg VARCHAR2(150);
3052 BEGIN
3053 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3054 IF PG_DEBUG <> 0 THEN
3055 oe_debug_pub.add('check_supply_type_wf: ' || 'CTO Activity: Check Supply Type', 1);
3056
3057 oe_debug_pub.add('check_supply_type_wf: ' || 'Item key = '||p_itemkey,1);
3058
3059 oe_debug_pub.add('check_supply_type_wf: ' || 'Func Mode ='||p_funcmode,1);
3060 END IF;
3061
3062 if (p_funcmode = 'RUN') then
3063 l_stmt_num := 100;
3064
3065 /*
3066 ** BUG#2234858
3067 ** need to retrieve source type code
3068 */
3069 BEGIN
3070 select inventory_item_id, ship_from_org_id,item_type_code, source_type_code
3071 into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code
3072 from oe_order_lines_all
3073 where line_id = to_number(p_itemkey)
3074 and ato_line_id is not null;
3075 IF PG_DEBUG <> 0 THEN
3076 oe_debug_pub.add('check_supply_type_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);
3077
3078 oe_debug_pub.add('check_supply_type_wf: ' || 'Ship from org id ='||to_char(l_ship_from_org_id),1);
3079
3080 oe_debug_pub.add('check_supply_type_wf: ' || 'Item type code ='||l_item_type_code,1);
3081 END IF;
3082 EXCEPTION
3083 WHEN NO_DATA_FOUND THEN
3084 Null;
3085 END;
3086
3087 -- check for hold on the line.
3088 -- Bug fix 5261330
3089 -- Started checking for hold in this node
3090 -- As this node will allways get executed for all supply types
3091 -- It is more effecient to check in this node and remove the hold check
3092 -- from the respective supply creation nodes
3093
3094
3095 OE_HOLDS_PUB.Check_Holds(p_api_version => 1.0,
3096 p_line_id => to_number(p_itemkey),
3097 p_wf_item => 'OEOL',
3098 p_wf_activity => 'CREATE_SUPPLY',
3099 x_result_out => v_x_hold_result_out,
3100 x_return_status => v_x_hold_return_status,
3101 x_msg_count => v_x_error_msg_count,
3102 x_msg_data => v_x_error_msg);
3103
3104 IF (v_x_hold_return_status = FND_API.G_RET_STS_ERROR) THEN
3105 IF PG_DEBUG <> 0 THEN
3106 oe_debug_pub.add('Check_supply_type_wf: ' || 'Expected error in Check Hold: ' || v_x_hold_return_status, 1);
3107 END IF;
3108 RAISE FND_API.G_EXC_ERROR;
3109
3110 ELSIF (v_x_hold_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3111 IF PG_DEBUG <> 0 THEN
3112 oe_debug_pub.add('Check_supply_type_wf: ' || 'Unexp error in Check Hold ' || v_x_hold_return_status, 1);
3113 END IF;
3114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3115
3116 ELSE
3117 IF PG_DEBUG <> 0 THEN
3118 oe_debug_pub.add('Check_supply_type_wf: ' || 'Success in Check Hold ' || v_x_hold_return_status, 5);
3119 END IF;
3120
3121 IF (v_x_hold_result_out = FND_API.G_TRUE) THEN
3122 IF PG_DEBUG <> 0 THEN
3123 oe_debug_pub.add('Check_supply_type_wf: ' || 'Order Line ID ' || p_itemkey || 'is on HOLD. ' ||v_x_hold_result_out, 1);
3124 END IF;
3125 cto_msg_pub.cto_message('BOM', 'CTO_ORDER_LINE_ON_HOLD');
3126 x_result := 'COMPLETE:INCOMPLETE';
3127 return;
3128 END IF;
3129 END IF;
3130
3131
3132
3133 /*
3134 ** BUG#2234858
3135 ** need to branch on source type for drop ship functionality
3136 */
3137 IF( l_source_type_code = 'EXTERNAL' )
3138 THEN
3139
3140 IF PG_DEBUG <> 0 THEN
3141 oe_debug_pub.add('check_supply_type_wf: ' || 'It is Config item Drop Ship case...',1);
3142 END IF;
3143 x_result := 'COMPLETE:DROPSHIP';
3144 OE_STANDARD_WF.Save_Messages;
3145 OE_STANDARD_WF.Clear_Msg_Context;
3146 return;
3147
3148
3149 END IF ;
3150
3151
3152
3153 -- get the sourcing type of the item in the specified organization.
3154 l_stmt_num := 200;
3155 -- Call the procedure to return the sourcing rule.
3156
3157 l_stmt_num := 200;
3158 --OPM
3159 --Check if Cto can create supply
3160 --query sourcing org is replaced with this new prcoedure
3161 --by KKONADA
3162 CTO_UTILITY_PK.check_cto_can_create_supply
3163 (
3164 P_config_item_id => l_inventory_item_id,
3165 P_org_id => l_ship_from_org_id,
3166 x_can_create_supply => l_can_create_supply,
3167 p_source_type => p_source_type,
3168 x_return_status => l_return_status,
3169 X_msg_count => l_msg_count,
3170 X_msg_data => l_msg_data,
3171 x_sourcing_org => l_sourcing_org,
3172 x_message => l_message
3173 );
3174
3175 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3176 IF PG_DEBUG <> 0 THEN
3177 oe_debug_pub.add('check_supply_type_wf: ' ||
3178 'Expected Error in check_cto_can_create_supply.',1);
3179 END IF;
3180 raise FND_API.G_EXC_ERROR;
3181
3182 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3183 IF PG_DEBUG <> 0 THEN
3184 oe_debug_pub.add('check_supply_type_wf: ' ||
3185 'Unexpected Error in check_cto_can_create_supply.',1);
3186 END IF;
3187 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3188
3189 END IF;
3190
3191 l_stmt_num := 210;
3192
3193 -- rkaza. ireq project. 05/02/2005.
3194 -- if CTO cannot create supply, let planning create the supply and
3195 -- move the workflow to ship line.
3196
3197 --Kiran Konada
3198 --If code flow is at this point , it means L-return_status was a SUCCESS
3199
3200 IF l_can_create_supply = 'N' THEN
3201 IF PG_DEBUG <> 0 THEN
3202
3203 oe_debug_pub.add('check_supply_type_wf: ' ||l_message,1);
3204
3205 END IF;
3206 x_result := 'COMPLETE:PLANNING';
3207
3208 l_stmt_num := 220;
3209
3210 --start bugfix 4556596
3211 IF PG_DEBUG <> 0 THEN
3212 oe_debug_pub.add('check_supply_type_wf: ' ||'calling display_wf_status with status',1);
3213 END IF;
3214
3215 l_ret_stat :=CTO_WORKFLOW_API_PK.display_wf_status
3216 (p_order_line_id=>p_itemkey
3217 );
3218
3219 IF l_ret_stat = 1 THEN
3220 oe_debug_pub.add('check_supply_type_wf: ' ||'call to display_wf_status success',1);
3221 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'call to display_wf_status success.');
3222
3223 Elsif l_ret_stat = 0 THEN
3224
3225 IF PG_DEBUG <> 0 THEN
3226
3227 oe_debug_pub.add('check_supply_type_wf: ' ||'call to display_wf_status failed',1);
3228 oe_debug_pub.add('check_supply_type_wf: ' ||'l_ret_stat=> '||l_ret_stat,1);
3229
3230 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'call to display_wf_status failed.');
3231 cto_wip_workflow_api_pk.cto_debug('DISPLAY_WF_STATUS', 'l_ret_stat=> '||l_ret_stat);
3232
3233 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3234 END IF;
3235 END IF;
3236
3237 --end bugfix 4556596
3238
3239 OE_STANDARD_WF.Save_Messages;
3240 OE_STANDARD_WF.Clear_Msg_Context;
3241 return;
3242 END IF;
3243
3244
3245
3246 l_stmt_num := 300;
3247
3248
3249 -- Modified by Renga Kannan on 02/06/02 for autocreate req for model
3250 -- rkaza. Use buy branch for 100% transfer rule case also.
3251
3252 IF p_source_type in (1, 3) THEN /* ATO Buy and IR cases */
3253 IF PG_DEBUG <> 0 THEN
3254 if p_source_type = 3 then
3255 oe_debug_pub.add('check_supply_type_wf: ' || 'It is ATO Buy case...',1);
3256 else
3257 oe_debug_pub.add('check_supply_type_wf: ' || 'It is ATO internal transfer case...',1);
3258 end if;
3259 END IF;
3260 x_result := 'COMPLETE:BUY';
3261 OE_STANDARD_WF.Save_Messages;
3262 OE_STANDARD_WF.Clear_Msg_Context;
3263 return;
3264 END IF;
3265
3266 l_stmt_num := 400;
3267
3268 select NVL(cfm_routing_flag,2)
3269 into l_supply_type
3270 from oe_order_lines_all oel,
3271 bom_operational_routings bor
3272 where oel.line_id = to_number(p_itemkey)
3273 and oel.inventory_item_id = bor.assembly_item_id (+)
3274 and oel.ship_from_org_id = bor.organization_id (+)
3275 and bor.alternate_routing_designator (+) is NULL;
3276
3277 --- Fixed bug 4197665
3278 --- replaced to_char(l_supply_type,1) with to_char(l_supply_type)
3279
3280 if (l_supply_type = 1) then
3281 -- Flow Schedule
3282 IF PG_DEBUG <> 0 THEN
3283 oe_debug_pub.add('check_supply_type_wf: ' || 'Routing Type is Flow Schedule. ' ||
3284 to_char (l_supply_type),1);
3285 END IF;
3286 x_result := 'COMPLETE:FLOW_SCHEDULE';
3287 else
3288 -- Discrete Job
3289 IF PG_DEBUG <> 0 THEN
3290 oe_debug_pub.add('check_supply_type_wf: ' || 'Routing Type is Discrete Job or No Routing. ' ||
3291 to_char (l_supply_type),1);
3292 END IF;
3293 x_result := 'COMPLETE:WORK_ORDER';
3294 end if;
3295
3296 IF PG_DEBUG <> 0 THEN
3297 oe_debug_pub.add('check_supply_type_wf: ' || 'Success in Check Supply Type', 1);
3298 END IF;
3299
3300 end if; /* end p_funcmode = 'RUN'*/
3301 OE_STANDARD_WF.Save_Messages;
3302 OE_STANDARD_WF.Clear_Msg_Context;
3303
3304 EXCEPTION
3305
3306 when FND_API.G_EXC_ERROR then
3307 IF PG_DEBUG <> 0 THEN
3308 OE_DEBUG_PUB.add('check_supply_type_wf: ' || 'CTO_WORKFLOW.check_supply_type_wf ' ||
3309 to_char(l_stmt_num));
3310 END IF;
3311 OE_STANDARD_WF.Save_Messages;
3312 OE_STANDARD_WF.Clear_Msg_Context;
3313 x_result := 'COMPLETE:INCOMPLETE';
3314
3315
3316 when FND_API.G_EXC_UNEXPECTED_ERROR then
3317 IF PG_DEBUG <> 0 THEN
3318 OE_DEBUG_PUB.add('check_supply_type_wf: ' || 'CTO_WORKFLOW.check_supply_type_wf ' ||
3319 to_char(l_stmt_num));
3320 END IF;
3321 OE_STANDARD_WF.Save_Messages;
3322 OE_STANDARD_WF.Clear_Msg_Context;
3323 wf_core.context('CTO_WORKFLOW', 'check_supply_type_wf',
3324 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3325 raise;
3326
3327 when OTHERS then
3328 IF PG_DEBUG <> 0 THEN
3329 oe_debug_pub.add('check_supply_type_wf: ' || 'CTO_WORKFLOW.check_supply_type_wf' ||
3330 to_char(l_stmt_num)||':'||sqlerrm); --bugfix 3136206
3331 END IF;
3332 wf_core.context('CTO_WORKFLOW', 'check_supply_type_wf',
3333 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3334
3335 OE_STANDARD_WF.Save_Messages;
3336 OE_STANDARD_WF.Clear_Msg_Context;
3337
3338 raise;
3339
3340 END check_supply_type_wf;
3341
3342
3343
3344
3345 /*============================================================================
3346 Procedure: create_flow_schedule__wf
3347 Description: This procedure gets called when executing the
3348 Create Flow Schedule activity in the CTO workflow.
3349
3350
3351 More to come...
3352 Parameters:
3353 =============================================================================*/
3354 PROCEDURE create_flow_schedule_wf(
3355 p_itemtype in VARCHAR2, /* workflow item type */
3356 p_itemkey in VARCHAR2, /* sales order line id */
3357 p_actid in number, /* ID number of WF activity */
3358 p_funcmode in VARCHAR2, /* execution mode of WF activity */
3359 x_result out NoCopy VARCHAR2 /* result of activity */
3360 )
3361 IS
3362
3363 l_stmt_num number := 0;
3364 l_msg_count number;
3365 l_msg_txt varchar2(2000);
3366 l_msg_name varchar2(60);
3367 l_quantity number := 0;
3368 l_sch_ship_date date;
3369 l_header_id number;
3370 l_ship_iface_flag varchar2(1);
3371 l_x_return_status varchar2(1);
3372 l_x_error_msg_count number;
3373 l_x_hold_result_out varchar2(1);
3374 l_x_hold_return_status varchar2(1);
3375 l_x_error_msg varchar2(150);
3376 l_source_document_type_id number; --bugfix 1799874
3377
3378
3379 BEGIN
3380 SAVEPOINT before_process;
3381
3382 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3383 IF PG_DEBUG <> 0 THEN
3384 oe_debug_pub.add('create_flow_schedule_wf: ' || 'CTO Activity: Create Flow Schedule', 1);
3385 END IF;
3386
3387 if (p_funcmode = 'RUN') then
3388 /*----------------------------------------------------------+
3389 Check order line status and check order line for holds.
3390 Do not process the order if status is invalid or
3391 if a hold is found.
3392 +-----------------------------------------------------------*/
3393 if (validate_config_line(to_number(p_itemkey)) <> TRUE) then
3394 cto_msg_pub.cto_message('BOM','CTO_LINE_STATUS_NOT_ELIGIBLE');
3395 raise FND_API.G_EXC_ERROR;
3396 end if;
3397
3398 --bugfix 1799874 start
3399 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => to_number(p_itemkey) );
3400 --bugfix 1799874 end
3401
3402
3403 l_stmt_num := 110;
3404 --select (oel.ordered_quantity - nvl(oel.cancelled_quantity, 0))
3405 select oel.ordered_quantity --bufix 2017099
3406 into l_quantity
3407 from oe_order_lines_all oel
3408 where oel.line_id = to_number(p_itemkey)
3409 and exists (select '1'
3410 from bom_operational_routings bor
3411 where bor.assembly_item_id = oel.inventory_item_id
3412 and bor.organization_id = oel.ship_from_org_id
3413 and bor.alternate_routing_designator is null
3414 and nvl(bor.cfm_routing_flag, 2) = 1)
3415 and not exists (select '1'
3416 from mtl_reservations mr
3417 where mr.demand_source_line_id = oel.line_id
3418 and mr.organization_id = oel.ship_from_org_id
3419 --and mr.demand_source_type_id = inv_reservation_global.g_source_type_oe
3420 and mr.demand_source_type_id =
3421 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
3422 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
3423 and mr.reservation_quantity > 0);
3424
3425 if (l_quantity <= 0) then
3426 IF PG_DEBUG <> 0 THEN
3427 oe_debug_pub.add('create_flow_schedule_wf: ' || 'l_quantity <= 0', 1);
3428 END IF;
3429 cto_msg_pub.cto_message('BOM','CTO_CREATE_FLOW_SCHED_ERROR');
3430 raise FND_API.G_EXC_ERROR;
3431 end if;
3432
3433 l_stmt_num := 120;
3434 -- Removed check hold API call from here as we are going to check for
3435 -- hold in check_supply_type_wf workflow activity, which is just before this workflow
3436 -- node
3437 -- Removed as part of bug fix 5261330
3438
3439 l_stmt_num := 130;
3440 --
3441 -- MRP will not create flow schedules if the scheduled ship date is
3442 -- earlier than today
3443 --
3444 select schedule_ship_date, header_id
3445 into l_sch_ship_date, l_header_id
3446 from oe_order_lines_all oel
3447 where line_id = to_number(p_itemkey);
3448
3449 if (trunc(l_sch_ship_date) < trunc(sysdate)) then
3450 IF PG_DEBUG <> 0 THEN
3451 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Schedule ship date '||
3452 to_char(l_sch_ship_date)||',
3453 is earlier than sysdate');
3454 END IF;
3455 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_SCH_DATE');
3456 x_result := 'COMPLETE:INCOMPLETE';
3457 return;
3458
3459 end if;
3460
3461 CTO_FLOW_SCHEDULE.cto_fs(
3462 p_config_line_id => p_itemkey,
3463 x_return_status => l_x_return_status,
3464 x_msg_name => l_msg_name,
3465 x_msg_txt => l_msg_txt);
3466
3467
3468
3469 IF (l_x_return_status = FND_API.G_RET_STS_ERROR) THEN
3470 IF PG_DEBUG <> 0 THEN
3471 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Expected error in cto_fs. ', 1);
3472 END IF;
3473 cto_msg_pub.cto_message('BOM', l_msg_name);
3474 raise FND_API.G_EXC_ERROR;
3475
3476 ELSIF (l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3477 IF PG_DEBUG <> 0 THEN
3478 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Unexpected error in Create Flow Schedule for line id ' || p_itemkey, 1);
3479 END IF;
3480 cto_msg_pub.cto_message('BOM', l_msg_name);
3481 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3482
3483 END IF;
3484
3485 --
3486 -- check if flow schedules have been scheduled for this line
3487 --
3488
3489 if Flow_Sch_Exists(to_number(p_itemkey)) then
3490 OE_Order_WF_Util.Update_Flow_Status_Code(
3491 p_header_id => l_header_id,
3492 p_line_id => to_number(p_itemkey),
3493 p_flow_status_code => 'PRODUCTION_OPEN',
3494 x_return_status => l_x_return_status);
3495
3496 x_result := 'COMPLETE';
3497 else
3498 IF PG_DEBUG <> 0 THEN
3499 oe_debug_pub.add('create_flow_schedule_wf: ' || 'Flow schedules not created');
3500 END IF;
3501 cto_msg_pub.cto_message('BOM', 'CTO_NO_FLOW_SCHEDULE');
3502 x_result := 'COMPLETE:INCOMPLETE';
3503 return;
3504 end if;
3505 end if; /* p_funcmode = 'RUN' */
3506
3507 OE_STANDARD_WF.Save_Messages;
3508 OE_STANDARD_WF.Clear_Msg_Context;
3509
3510 EXCEPTION
3511
3512 when FND_API.G_EXC_ERROR then
3513 IF PG_DEBUG <> 0 THEN
3514 OE_DEBUG_PUB.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf raised exp error in stmt ' ||
3515 to_char(l_stmt_num) || ':' || l_x_error_msg);
3516 END IF;
3517 OE_STANDARD_WF.Save_Messages;
3518 OE_STANDARD_WF.Clear_Msg_Context;
3519 x_result := 'COMPLETE:INCOMPLETE';
3520 rollback to savepoint before_process;
3521 return;
3522
3523 when NO_DATA_FOUND then
3524 IF PG_DEBUG <> 0 THEN
3525 oe_debug_pub.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf '
3526 || to_char(l_stmt_num) || ':' ||
3527 substrb(sqlerrm, 1, 100),1);
3528 END IF;
3529 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_FLOW_SCHED_ERROR');
3530 OE_STANDARD_WF.Save_Messages;
3531 OE_STANDARD_WF.Clear_Msg_Context;
3532 x_result := 'COMPLETE:INCOMPLETE';
3533 rollback to savepoint before_process;
3534 return;
3535
3536 when FND_API.G_EXC_UNEXPECTED_ERROR then
3537 IF PG_DEBUG <> 0 THEN
3538 OE_DEBUG_PUB.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf raised unexp error in stmt ' ||
3539 to_char(l_stmt_num) || ':' || l_x_error_msg);
3540 END IF;
3541 OE_STANDARD_WF.Save_Messages;
3542 OE_STANDARD_WF.Clear_Msg_Context;
3543 raise;
3544
3545 when OTHERS then
3546 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_FLOW_SCHED_ERROR');
3547 IF PG_DEBUG <> 0 THEN
3548 oe_debug_pub.add('create_flow_schedule_wf: ' || 'CTO_WORKFLOW.create_flow_schedule_wf '
3549 || to_char(l_stmt_num) || ':' ||
3550 substrb(sqlerrm, 1, 250),1);
3551 END IF;
3552 wf_core.context('CTO_WORKFLOW', 'create_flow_schedule_wf',
3553 p_itemtype, p_itemkey, to_char(p_actid),
3554 p_funcmode);
3555 raise;
3556
3557 END create_flow_schedule_wf;
3558
3559
3560 -- begin bugfix 2105156
3561
3562 -- PROCEDURE Lock_Line_Id
3563 --
3564 -- Usage Used by set_parameter_work_order_wf API to update the program_id
3565 -- This is done to manually 'lock' the line.
3566 --
3567 -- Desc This procedure is set for autonomous transaction.
3568 -- This procedure accepts line_id.
3569 --
3570 -- Note This procedure uses autonomous transaction.That means
3571 -- commit or rollback with in this procedure will not affect
3572 -- the callers transaction.
3573
3574 PROCEDURE lock_line_id(p_line_id IN NUMBER,
3575 x_result OUT NoCopy VARCHAR2 )
3576 IS
3577 Pragma AUTONOMOUS_TRANSACTION;
3578
3579 record_locked EXCEPTION;
3580 pragma exception_init (record_locked, -54);
3581 l_dummy VARCHAR2(2);
3582
3583 BEGIN
3584
3585 x_result := null;
3586
3587 -- select to see if we can acquire lock. If we cannot, it will raise RECORD_LOCKED exception.
3588
3589 SELECT '1' into l_dummy
3590 FROM oe_order_lines_all
3591 WHERE line_id = p_line_id
3592 FOR UPDATE NOWAIT;
3593
3594 UPDATE oe_order_lines_all oel
3595 SET oel.program_id = -99
3596 WHERE oel.line_id = p_line_id
3597 AND nvl(oel.program_id, 0) <> -99;
3598
3599 COMMIT;
3600 IF PG_DEBUG <> 0 THEN
3601 oe_debug_pub.add ('lock_line_id: ' || 'committed program_id with -99');
3602 END IF;
3603
3604 EXCEPTION
3605 when no_data_found then
3606 null;
3607
3608 when record_locked then
3609 IF PG_DEBUG <> 0 THEN
3610 OE_DEBUG_PUB.add ('lock_line_id: ' || 'CTO_WORKFLOW.Lock_Line_Id: Could not lock line id '|| p_line_id ||' for update.');
3611
3612 OE_DEBUG_PUB.add ('lock_line_id: ' || 'This line is being processed by another process.');
3613 END IF;
3614 x_result := 'COMPLETE:INCOMPLETE';
3615
3616 when others then
3617 IF PG_DEBUG <> 0 THEN
3618 OE_DEBUG_PUB.add ('lock_line_id: ' || 'CTO_WORKFLOW.Lock_Line_Id: Unexpected Error : '||sqlerrm);
3619 END IF;
3620 x_result := 'COMPLETE:INCOMPLETE';
3621
3622 END Lock_Line_Id;
3623
3624 -- end bugfix 2105156
3625
3626
3627 -- bugfix 3136206: we want to make this as autonomous txn, otherwise, these changes will get rolledback due
3628 -- to exception in main api which does a rollback to savepoint.
3629
3630 PROCEDURE unlock_line_id(p_line_id IN NUMBER,
3631 x_result OUT NoCopy VARCHAR2 )
3632 IS
3633 Pragma AUTONOMOUS_TRANSACTION;
3634
3635 record_locked EXCEPTION;
3636 pragma exception_init (record_locked, -54);
3637 l_dummy VARCHAR2(2);
3638
3639 BEGIN
3640
3641 x_result := null;
3642
3643 UPDATE oe_order_lines_all oel
3644 SET oel.program_id = null
3645 WHERE oel.line_id = p_line_id
3646 AND nvl(oel.program_id, 0) = -99;
3647
3648 IF (sql%rowcount > 0) THEN
3649 COMMIT;
3650 IF (PG_DEBUG <> 0) THEN
3651 oe_debug_pub.add ('unlock_line_id: ' || 'unlocked line_id '||p_line_id);
3652 END IF;
3653 END IF;
3654
3655 EXCEPTION
3656
3657 when others then
3658 IF PG_DEBUG <> 0 THEN
3659 OE_DEBUG_PUB.add ('unlock_line_id: error: ' || sqlerrm);
3660
3661 END IF;
3662 x_result := 'COMPLETE:INCOMPLETE';
3663
3664 END UnLock_Line_Id;
3665
3666 -- end bugfix 3136206
3667
3668 /*============================================================================
3669 Procedure: set_parameter_work_order_wf
3670
3671 Description: This procedure gets called when executing the Set
3672 Parameter Work Order activity in the ATO workflow.
3673
3674 More to come...
3675 Parameters:
3676 =============================================================================*/
3677 PROCEDURE set_parameter_work_order_wf(
3678 p_itemtype in VARCHAR2, /* workflow item type */
3679 p_itemkey in VARCHAR2, /* sales order line id */
3680 p_actid in number, /* ID number of WF activity */
3681 p_funcmode in VARCHAR2, /* execution mode of WF activity */
3682 x_result out NoCopy VARCHAR2 /* result of activity */
3683 )
3684 IS
3685
3686 l_stmt_num number := 0;
3687 l_quantity number := 0;
3688 l_class_code number;
3689 l_wip_group_id number;
3690 l_mfg_org_id number;
3691 l_afas_line_id number;
3692 l_msg_name varchar2(30);
3693 l_msg_txt varchar2(500); --bugfix 2776026: increased the var size
3694 l_return_status varchar2(1);
3695 l_user_id varchar2(30);
3696 l_msg_count number;
3697 l_hold_result_out varchar2(1);
3698 l_hold_return_status varchar2(1);
3699 l_ship_iface_flag varchar2(1);
3700
3701 l_source_document_type_id number; -- bugfix 1799874
3702
3703 --fix for bug#1874380
3704 l_item_type_code varchar2(30);
3705 l_ato_line_id number;
3706 l_line_id number;
3707 l_top_model_line_id number;
3708 --end of fix for bug#1874380
3709
3710
3711
3712 -- bugfix 2053360 : declare a new exception
3713 record_locked exception;
3714 pragma exception_init (record_locked, -54);
3715
3716 l_result varchar2(20) := null; --bugfix 2105156
3717
3718 l_build_in_wip varchar2(1); --bugfix 2318060
3719
3720 BEGIN
3721
3722 savepoint before_process;
3723
3724 IF PG_DEBUG <> 0 THEN
3725 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'Function Mode: ' || p_funcmode, 1);
3726
3727 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO Activity: Set Parameter Work Order', 1);
3728 END IF;
3729 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3730
3731 if (p_funcmode = 'RUN') then
3732
3733 -- Note: bugfix 3136206: The Lock_Line_Id api should be called BEFORE the SELECT FOR UPDATE
3734 -- since it is executed in autonomous transaction mode. Autonomous txns are run in
3735 -- different sessions. If this api is called after SELECT FOR UPDATE, then it will fail.
3736
3737 --
3738 -- begin bugfix 2105156: Call lock_line_Id to manually lock the row if possible.
3739 -- Lock_Line_Id API will update the program_id in oeol to -99.
3740 -- if you cannot, raise RECORD_LOCKED exception
3741 --
3742
3743
3744 Lock_Line_Id ( to_number(p_itemkey), l_result );
3745 if ( l_result is not null ) then
3746 raise record_locked;
3747 end if;
3748
3749
3750 --bugfix 1799874 start
3751 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => to_number(p_itemkey) );
3752 --bugfix 1799874 end
3753
3754 /*----------------------------------------------------------+
3755 Check order line status and check order line for holds.
3756 Do not process the order if status is invalid or
3757 if a hold is found.
3758 +-----------------------------------------------------------*/
3759 l_stmt_num := 100;
3760 --select oel.ordered_quantity - nvl(oel.cancelled_quantity, 0),
3761 select oel.ordered_quantity, -- bugfix 2017099
3762 oel.ship_from_org_id,
3763 oel.ato_line_id,--5108885
3764 oel.ato_line_id, oel.line_id,oel.top_model_line_id, oel.item_type_code --fix for bug#1874380
3765 into l_quantity, l_mfg_org_id, l_afas_line_id, l_ato_line_id, l_line_id,
3766 l_top_model_line_id,l_item_type_code
3767 from mtl_system_items msi,
3768 oe_order_lines_all oel
3769 where oel.line_id = to_number(p_itemkey)
3770 and (oel.open_flag is null
3771 or oel.open_flag = 'Y')
3772 and oel.ordered_quantity > 0
3773 and oel.inventory_item_id = msi.inventory_item_id
3774 and msi.organization_id = oel.ship_from_org_id
3775 and oel.schedule_status_code = 'SCHEDULED'
3776 and oel.booked_flag = 'Y'
3777 and oel.ato_line_id is not null
3778 --and oel.shipping_interfaced_flag = 'Y'
3779 and msi.replenish_to_order_flag = 'Y'
3780 and msi.pick_components_flag = 'N'
3781 and msi.build_in_wip_flag = 'Y'
3782 and msi.bom_item_type = 4
3783 /*----------------------------------+
3784 ATO items do not have to have
3785 a base model.
3786 and msi.base_item_id is not NULL
3787 +-----------------------------------*/
3788 and not exists
3789 (select '1'
3790 from oe_order_lines_all oel2
3791 where oel2.ship_from_org_id = oel.ship_from_org_id
3792 and oel2.header_id = oel.header_id
3793 and oel2.line_id = oel.line_id
3794 and rownum = 1
3795 and WIP_ATO_UTILS.check_wip_supply_type(
3796 oel2.header_id,
3797 oel2.line_id,
3798 NULL,
3799 oel2.ship_from_org_id)
3800 not in (0,1)
3801 )
3802 and not exists
3803 (select '1'
3804 from bom_operational_routings bor
3805 where bor.assembly_item_id = oel.inventory_item_id
3806 and bor.organization_id = oel.ship_from_org_id
3807 and bor.alternate_routing_designator is null
3808 and nvl(bor.cfm_routing_flag, 2) = 1)
3809 and not exists
3810 (select '1'
3811 from mtl_reservations mr
3812 where mr.demand_source_line_id = oel.line_id
3813 and mr.organization_id = oel.ship_from_org_id
3814 --and mr.demand_source_type_id = inv_reservation_global.g_source_type_oe
3815 and mr.demand_source_type_id =
3816 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
3817 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
3818 and mr.reservation_quantity > 0)
3819 FOR UPDATE OF oel.line_id NOWAIT; --bugfix 2053360
3820
3821
3822
3823 if (l_quantity <= 0) then
3824 IF PG_DEBUG <> 0 THEN
3825 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'Ordered quantity is zero.', 1);
3826 END IF;
3827 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3828 raise FND_API.G_EXC_ERROR;
3829 end if;
3830
3831 l_stmt_num := 101;
3832
3833 -- Removed check hold API call from here as we are going to check for
3834 -- hold in check_supply_type_wf workflow activity, which is just before this workflow
3835 -- node
3836 -- Removed as part of bug fix 5261330
3837 -- wf_engine.SetItemAttrNumber(p_itemtype, p_itemkey,
3838 -- 'AFAS_ORG_ID',l_mfg_org_id);
3839 -- oe_debug_pub.add('mfg_org_id: ' || to_char(l_mfg_org_id),1);
3840
3841 wf_engine.SetItemAttrNumber(p_itemtype, p_itemkey,
3842 'AFAS_LINE_ID', l_afas_line_id);
3843 IF PG_DEBUG <> 0 THEN
3844 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'order_line_id: ' || p_itemkey,1);
3845
3846 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'l_afas_line_id: ' || l_afas_line_id);
3847 END IF;
3848
3849 x_result := 'COMPLETE';
3850 end if; /* p_funcmode = 'RUN' */
3851
3852 OE_STANDARD_WF.Save_Messages;
3853 OE_STANDARD_WF.Clear_Msg_Context;
3854
3855 EXCEPTION
3856
3857 --
3858 -- begin bugfix 2053360: handle the record_locked exception
3859 --
3860
3861 when record_locked then
3862 cto_msg_pub.cto_message('BOM', 'CTO_ORDER_LINE_LOCKED');
3863 IF PG_DEBUG <> 0 THEN
3864 OE_DEBUG_PUB.add ('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf: Could not lock line id '||
3865 p_itemkey ||' for update.');
3866
3867 OE_DEBUG_PUB.add ('set_parameter_work_order_wf: ' || 'This line is being processed by another process.');
3868 END IF;
3869
3870 OE_STANDARD_WF.Save_Messages;
3871 OE_STANDARD_WF.Clear_Msg_Context;
3872 x_result := 'COMPLETE:INCOMPLETE';
3873 return;
3874
3875
3876 when FND_API.G_EXC_ERROR then
3877 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3878 IF PG_DEBUG <> 0 THEN
3879 OE_DEBUG_PUB.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf raised exc error. ' ||
3880 to_char(l_stmt_num) );
3881 END IF;
3882 OE_STANDARD_WF.Save_Messages;
3883 OE_STANDARD_WF.Clear_Msg_Context;
3884 x_result := 'COMPLETE:INCOMPLETE';
3885 rollback to savepoint before_process;
3886 return;
3887
3888
3889 when FND_API.G_EXC_UNEXPECTED_ERROR then
3890 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3891 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3892 IF PG_DEBUG <> 0 THEN
3893 OE_DEBUG_PUB.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf raised unexc error. ' ||
3894 to_char(l_stmt_num) );
3895 END IF;
3896 OE_STANDARD_WF.Save_Messages;
3897 OE_STANDARD_WF.Clear_Msg_Context;
3898 wf_core.context('CTO_WORKFLOW', 'set_parameter_work_order_wf',
3899 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
3900 raise;
3901
3902
3903 when NO_DATA_FOUND then
3904 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3905
3906 --start bugfix 2318060
3907 BEGIN
3908 SELECT build_in_wip_flag
3909 INTO l_build_in_wip
3910 FROM mtl_system_items mtl,
3911 Oe_order_lines_all oel
3912 WHERE oel.line_id = to_number(p_itemkey)
3913 AND oel.inventory_item_id = mtl.inventory_item_id
3914 AND oel.ship_from_org_id = mtl.organization_id;
3915 EXCEPTION
3916 WHEN others THEN
3917 null;
3918
3919 END;
3920
3921 IF l_build_in_wip = 'N' THEN
3922 --set the build in wip flag
3923 cto_msg_pub.cto_message('BOM', 'CTO_BUILD_IN_WIP_FLAG');
3924 IF PG_DEBUG <> 0 THEN
3925 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf:'
3926 ||'ERROR : Buld_in_wip_flag needs to be checked',1 );
3927 END IF;
3928
3929 ELSE--no_data_found is for someother reason
3930
3931 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3932 IF PG_DEBUG <> 0 THEN
3933 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf'
3934 || to_char(l_stmt_num) || ':' ||
3935 substrb(sqlerrm, 1, 100),1);
3936 END IF;
3937 END IF;
3938
3939 --end bugfix 2318060
3940
3941 OE_STANDARD_WF.Save_Messages;
3942 OE_STANDARD_WF.Clear_Msg_Context;
3943
3944 -- Begin bugfix 2053360:
3945 -- Set the result to INCOMPLETE so that the wf returns to Create Supply Order Eligible
3946 x_result := 'COMPLETE:INCOMPLETE';
3947 return;
3948
3949 when OTHERS then
3950 unlock_line_id (p_itemkey, x_result); -- bugfix 3136206
3951 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_WORK_ORDER_ERROR');
3952 IF PG_DEBUG <> 0 THEN
3953 oe_debug_pub.add('set_parameter_work_order_wf: ' || 'CTO_WORKFLOW.set_parameter_work_order_wf: '
3954 || to_char(l_stmt_num) || ':' ||
3955 substrb(sqlerrm, 1, 100));
3956 END IF;
3957 wf_core.context('CTO_WORKFLOW', 'set_parameter_work_order_wf',
3958 p_itemtype, p_itemkey, to_char(p_actid),
3959 p_funcmode);
3960
3961 raise;
3962
3963 END set_parameter_work_order_wf;
3964
3965
3966 /*============================================================================
3967 Procedure: submit_conc_prog_wf
3968 Description: This procedure gets called for the Lead Time Calculate and
3969 the AutoCreate FAS workflow activities. It is a wrapper
3970 around the Workflow activity that submits the concurrent
3971 program via Workflow. The wrapper is needed to retrieve
3972 and display the concurrent request ID after Workflow
3973 submits the request.
3974 =============================================================================*/
3975 PROCEDURE submit_conc_prog_wf(
3976 p_itemtype in VARCHAR2, /* workflow item type */
3977 p_itemkey in VARCHAR2, /* sales order line id */
3978 p_actid in number, /* ID number of WF activity */
3979 p_funcmode in VARCHAR2, /* execution mode of WF activity */
3980 x_result out NoCopy VARCHAR2 /* result of activity */
3981
3982 )
3983 IS
3984
3985 l_stmt_num number := 0;
3986 l_req_id number := 0;
3987 l_msg_name varchar2(30);
3988 l_msg_txt varchar2(2000);
3989 l_token CTO_MSG_PUB.token_tbl;
3990 l_cnt number; --bug 9679523
3991
3992 BEGIN
3993
3994 OE_STANDARD_WF.Set_Msg_Context(p_actid);
3995 IF PG_DEBUG <> 0 THEN
3996 oe_debug_pub.add('submit_conc_prog_wf: ' || 'CTO Activity: submit_conc_prog_wf.',1);
3997 oe_debug_pub.add('submit_conc_prog_wf: ' || 'p_item_type: ' || p_itemtype, 1);
3998 oe_debug_pub.add('submit_conc_prog_wf: ' || 'p_itemkey: ' || p_itemkey, 1);
3999 oe_debug_pub.add('submit_conc_prog_wf: ' || 'p_actid: ' || p_actid, 1);
4000 oe_debug_pub.add('submit_conc_prog_wf: ' || 'p_funcmode: ' || p_funcmode, 1);
4001 END IF;
4002
4003 -- Bugfix 9288692
4004 -- An update is needed again in case of retry activity. During the first run of AFAS, if there is
4005 -- an error, the program_id is updated to null. When the AFAS activity is retried, the line
4006 -- doesn't get picked up because the program_id is null.
4007
4008 UPDATE oe_order_lines_all
4009 SET program_id = -99
4010 WHERE line_id = to_number(p_itemkey)
4011 AND NVL(program_id, 0) <> -99;
4012
4013 IF PG_DEBUG <> 0 THEN
4014 if sql%rowcount > 0 then
4015 oe_debug_pub.add ('submit_conc_prog_wf: ' || 'updated program_id to -99');
4016 end if;
4017 END IF;
4018
4019 --
4020 -- bug 9679523
4021 -- Delete completed data from WJSI to support retry
4022 --
4023 DELETE FROM WIP_INTERFACE_ERRORS
4024 WHERE INTERFACE_ID IN (
4025 SELECT INTERFACE_ID
4026 FROM WIP_JOB_SCHEDULE_INTERFACE
4027 WHERE source_line_id = to_number(p_itemkey)
4028 AND PROCESS_PHASE = 4
4029 AND PROCESS_STATUS = 4);
4030
4031 l_cnt := sql%rowcount;
4032 IF PG_DEBUG <> 0 THEN
4033 if sql%rowcount > 0 then
4034 oe_debug_pub.add ('submit_conc_prog_wf: ' || 'Rows deleted from wie:' || l_cnt);
4035 end if;
4036 END IF;
4037
4038 DELETE FROM WIP_JOB_SCHEDULE_INTERFACE I
4039 WHERE source_line_id = to_number(p_itemkey)
4040 AND I.PROCESS_PHASE = 4
4041 AND I.PROCESS_STATUS = 4;
4042
4043 l_cnt := sql%rowcount;
4044 IF PG_DEBUG <> 0 THEN
4045 if sql%rowcount > 0 then
4046 oe_debug_pub.add ('submit_conc_prog_wf: ' || 'Rows deleted from wjsi:' || l_cnt);
4047 end if;
4048 END IF;
4049
4050
4051 if (p_funcmode = 'RUN') then
4052
4053 l_stmt_num := 100;
4054 fnd_wf_standard.ExecuteConcProgram(p_itemtype,
4055 p_itemkey,
4056 p_actid,
4057 p_funcmode,
4058 x_result);
4059
4060 /*---------------------------------------------------------------+
4061 Get Request ID - We are using the same item attribute to store
4062 the request ID of the Lead Time conc prog and AFAS conc prog.
4063 +-----------------------------------------------------------------*/
4064 l_stmt_num := 110;
4065 l_req_id := wf_engine.GetItemAttrNumber(p_itemtype, p_itemkey,
4066 'LEAD_TIME_REQUEST_ID');
4067
4068 IF PG_DEBUG <> 0 THEN
4069 oe_debug_pub.add('submit_conc_prog_wf: ' || 'Request ID: ' || to_char(l_req_id), 1);
4070 END IF;
4071
4072 l_token(1).token_name := 'REQUEST_ID';
4073 l_token(1).token_value := l_req_id;
4074
4075 --oe_debug_pub.add ('1. l_token(1).name = '|| l_token(1).token_name);
4076 --oe_debug_pub.add ('1. l_token(1).value = '|| l_token(1).token_value);
4077
4078 cto_msg_pub.cto_message('BOM', 'CTO_CONCURRENT_REQUEST', l_token);
4079 --fnd_message.set_token('REQUEST_ID', l_req_id);
4080
4081 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
4082
4083 OE_STANDARD_WF.Save_Messages;
4084 OE_STANDARD_WF.Clear_Msg_Context;
4085
4086 end if;
4087
4088 EXCEPTION
4089
4090 when NO_DATA_FOUND then
4091 IF PG_DEBUG <> 0 THEN
4092 oe_debug_pub.add('submit_conc_prog_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4093 || to_char(l_stmt_num) || ':' ||
4094 substrb(sqlerrm, 1, 100));
4095 END IF;
4096 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4097 p_itemtype, p_itemkey, to_char(p_actid),
4098 p_funcmode);
4099 raise;
4100
4101 when OTHERS then
4102 IF PG_DEBUG <> 0 THEN
4103 oe_debug_pub.add('submit_conc_prog_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4104 || to_char(l_stmt_num) || ':' ||
4105 substrb(sqlerrm, 1, 100));
4106 END IF;
4107 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4108 p_itemtype, p_itemkey, to_char(p_actid),
4109 p_funcmode);
4110 raise;
4111
4112 END submit_conc_prog_wf;
4113
4114 /*============================================================================
4115 Procedure: submit_and_continue_wf
4116 Description: This procedure gets called for the Lead Time Calculate and
4117 the AutoCreate FAS workflow activities. It is a wrapper
4118 around the Workflow activity that submits the concurrent
4119 program via Workflow. The wrapper is needed to retrieve
4120 and display the concurrent request ID after Workflow
4121 submits the request.
4122 =============================================================================*/
4123 PROCEDURE submit_and_continue_wf(
4124 p_itemtype in VARCHAR2, /* workflow item type */
4125 p_itemkey in VARCHAR2, /* sales order line id */
4126 p_actid in number, /* ID number of WF activity */
4127 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4128 x_result out NoCopy VARCHAR2 /* result of activity */
4129
4130 )
4131 IS
4132
4133 l_stmt_num number := 0;
4134 l_req_id number := 0;
4135 l_msg_name varchar2(30);
4136 l_msg_txt varchar2(2000);
4137 l_token CTO_MSG_PUB.token_tbl;
4138
4139 l_activity_status varchar2(100);
4140
4141 l_conc_msg number := 0;
4142
4143 BEGIN
4144
4145 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4146 IF PG_DEBUG <> 0 THEN
4147 oe_debug_pub.add('submit_and_continue_wf: ' || 'CTO Activity: submit_conc_prog_wf.',1);
4148 END IF;
4149
4150 if (p_funcmode = 'RUN') then
4151
4152 l_stmt_num := 10;
4153 CTO_WORKFLOW_API_PK.query_wf_activity_status(
4154 p_itemtype => p_itemtype,
4155 p_itemkey => p_itemkey,
4156 p_activity_label => 'EXECLEADTIME',
4157 p_activity_name => 'EXECLEADTIME',
4158 p_activity_status => l_activity_status);
4159
4160 IF PG_DEBUG <> 0 THEN
4161 oe_debug_pub.add('submit_and_continue_wf: ' || 'EXECLEADTIME activity status:'||l_activity_status,1);
4162 END IF;
4163
4164 IF l_activity_status = 'ACTIVE' THEN
4165 l_conc_msg := 1;
4166 IF PG_DEBUG <> 0 THEN
4167 oe_debug_pub.add('submit_and_continue_wf: ' || 'Show message for Lead Time conc program',1);
4168 END IF;
4169 ELSE
4170 CTO_WORKFLOW_API_PK.query_wf_activity_status(
4171 p_itemtype => p_itemtype,
4172 p_itemkey => p_itemkey,
4173 p_activity_label => 'EXECUTECONCPROGAFAS',
4174 p_activity_name => 'EXECUTECONCPROGAFAS',
4175 p_activity_status => l_activity_status);
4176 IF PG_DEBUG <> 0 THEN
4177 oe_debug_pub.add('submit_and_continue_wf: ' || 'EXECUTECONCPROGAFAS activity status:'||l_activity_status,1);
4178 END IF;
4179 IF l_activity_status = 'ACTIVE' THEN
4180 l_conc_msg := 2;
4181 IF PG_DEBUG <> 0 THEN
4182 oe_debug_pub.add('submit_and_continue_wf: ' || 'Show message for AFAS conc program',1);
4183 END IF;
4184 ELSE
4185 l_conc_msg := 1;
4186 END IF;
4187 END IF;
4188
4189 l_stmt_num := 100;
4190 fnd_wf_standard.SubmitConcProgram(p_itemtype,
4191 p_itemkey,
4192 p_actid,
4193 p_funcmode,
4194 x_result);
4195
4196 /*---------------------------------------------------------------+
4197 Get Request ID - We are using the same item attribute to store
4198 the request ID of the Lead Time conc prog and AFAS conc prog.
4199 +-----------------------------------------------------------------*/
4200 l_stmt_num := 110;
4201 l_req_id := wf_engine.GetItemAttrNumber(p_itemtype, p_itemkey,
4202 'LEAD_TIME_REQUEST_ID');
4203
4204 IF PG_DEBUG <> 0 THEN
4205 oe_debug_pub.add('submit_and_continue_wf: ' || 'Request ID: ' || to_char(l_req_id), 1);
4206 END IF;
4207
4208 l_token(1).token_name := 'REQUEST_ID';
4209 l_token(1).token_value := l_req_id;
4210
4211 --oe_debug_pub.add('l_token(1).token_name = '||l_token(1).token_name );
4212 --oe_debug_pub.add('l_token(1).token_value = '||l_token(1).token_value );
4213 IF l_conc_msg = 2 THEN
4214 cto_msg_pub.cto_message('BOM', 'CTO_CONCURRENT_REQUEST',l_token);
4215 ELSE
4216 cto_msg_pub.cto_message('BOM', 'CTO_CONCURRENT_REQUEST_ID',l_token);
4217 END IF;
4218
4219 --fnd_message.set_token('REQUEST_ID', l_req_id);
4220
4221 l_token := CTO_MSG_PUB.G_MISS_TOKEN_TBL; -- initialize
4222
4223 OE_STANDARD_WF.Save_Messages;
4224 OE_STANDARD_WF.Clear_Msg_Context;
4225
4226 end if;
4227
4228 EXCEPTION
4229
4230 when NO_DATA_FOUND then
4231 IF PG_DEBUG <> 0 THEN
4232 oe_debug_pub.add('submit_and_continue_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4233 || to_char(l_stmt_num) || ':' ||
4234 substrb(sqlerrm, 1, 100));
4235 END IF;
4236 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4237 p_itemtype, p_itemkey, to_char(p_actid),
4238 p_funcmode);
4239 raise;
4240
4241 when OTHERS then
4242 IF PG_DEBUG <> 0 THEN
4243 oe_debug_pub.add('submit_and_continue_wf: ' || 'CTO_WORKFLOW.submit_conc_prog_wf: '
4244 || to_char(l_stmt_num) || ':' ||
4245 substrb(sqlerrm, 1, 100));
4246 END IF;
4247 wf_core.context('CTO_WORKFLOW', 'submit_conc_prog_wf',
4248 p_itemtype, p_itemkey, to_char(p_actid),
4249 p_funcmode);
4250 raise;
4251
4252 END submit_and_continue_wf;
4253
4254
4255 /*============================================================================
4256 Procedure: validate_line
4257 Description:
4258
4259 More to come...
4260 Parameters:
4261 =============================================================================*/
4262 FUNCTION validate_line(
4263 p_line_id in number
4264
4265 )
4266 RETURN boolean
4267
4268 IS
4269
4270 l_valid_model_line NUMBER := 0;
4271 v_aps_version number ;
4272
4273 BEGIN
4274
4275
4276 v_aps_version := msc_atp_global.get_aps_version ;
4277
4278 /*------------------------------------------------------------+
4279 Select line details to make sure the model line is valid.
4280 +------------------------------------------------------------*/
4281 select 1
4282 into l_valid_model_line
4283 from oe_order_lines_all oel,
4284 mtl_system_items msi
4285 where oel.line_id = p_line_id
4286 and msi.organization_id = oel.ship_from_org_id
4287 and msi.inventory_item_id = oel.inventory_item_id
4288 and msi.bom_item_type = 1
4289 --and msi.build_in_wip_flag = 'Y'
4290 and msi.replenish_to_order_flag = 'Y'
4291 and oel.open_flag = 'Y'
4292 and (oel.cancelled_flag = 'N'
4293 or oel.cancelled_flag is null)
4294 and ( oel.booked_flag = 'Y' or v_aps_version >= 10 )
4295 and schedule_ship_date is not null /* Fixed bug 3548069 */
4296 and (
4297 ( oel.schedule_status_code = 'SCHEDULED'
4298 and oel.source_type_code = 'INTERNAL'
4299 --
4300 -- Bug 14474393
4301 -- Commenting this check since as per OM team the condition based on
4302 -- schedule status code should be enough as this flag is controlled by
4303 -- the profile OM: Bypass ATP
4304 --
4305 -- and oel.visible_demand_flag = 'Y'
4306 )
4307 OR ( oel.source_type_code = 'EXTERNAL' )
4308 ) ; /* BUG#2234858 additional changes Made by sushant for Drop Ship */
4309
4310 --and oel.item_type_code = 'MODEL';
4311
4312 if (l_valid_model_line > 0 ) then
4313 return TRUE;
4314 else
4315 return FALSE;
4316 end if;
4317
4318 EXCEPTION
4319
4320 when NO_DATA_FOUND then
4321 return FALSE;
4322
4323
4324 when OTHERS then
4325 return FALSE;
4326
4327 END validate_line;
4328
4329
4330 /*============================================================================
4331 Procedure: validate_config_line
4332 Description: This procedure gets called when executing the Match
4333 Configuration activity in the ATO workflow. The
4334 format is follows the standard Workflow API format.
4335
4336 More to come...
4337 Parameters:
4338 =============================================================================*/
4339 FUNCTION validate_config_line(
4340 p_config_line_id in number
4341 )
4342 RETURN boolean
4343
4344 IS
4345
4346 l_valid_config_line NUMBER := 0;
4347
4348
4349 BEGIN
4350
4351 /*------------------------------------------------------------+
4352 Select line details to make sure the config line is valid.
4353 +------------------------------------------------------------*/
4354 select 1
4355 into l_valid_config_line
4356 from oe_order_lines_all oel,
4357 mtl_system_items msi
4358 where oel.line_id = p_config_line_id
4359 and msi.organization_id = oel.ship_from_org_id
4360 and oel.inventory_item_id = msi.inventory_item_id
4361 and msi.bom_item_type = 4
4362 and msi.build_in_wip_flag = 'Y'
4363 and msi.replenish_to_order_flag = 'Y'
4364 and oel.open_flag = 'Y'
4365 and (oel.cancelled_flag = 'N'
4366 or oel.cancelled_flag is null)
4367 --
4368 -- Bug 14474393
4369 -- Commenting this check since as per OM team the condition based on
4370 -- schedule status code should be enough as this flag is controlled by
4371 -- the profile OM: Bypass ATP
4372 --
4373 -- and oel.visible_demand_flag = 'Y'
4374 and oel.booked_flag = 'Y'
4375 and oel.schedule_status_code = 'SCHEDULED'
4376 and ( oel.item_type_code = 'CONFIG' OR
4377 --Adding INCLUDED item type code for SUN ER#9793792
4378 ( oel.item_type_code in ('STANDARD','OPTION','INCLUDED') AND --bugfix#2111718
4379 oel.ato_line_id = p_config_line_id ) );
4380
4381 if (l_valid_config_line > 0 ) then
4382 return TRUE;
4383 else
4384 return FALSE;
4385 end if;
4386
4387 EXCEPTION
4388
4389 when NO_DATA_FOUND then
4390 return FALSE;
4391
4392 when OTHERS then
4393 return FALSE;
4394 END validate_config_line;
4395
4396
4397
4398 /*============================================================================
4399 Procedure: config_line_exists
4400 Description: This procedure gets called when executing the Match
4401 Configuration activity in the ATO workflow. The
4402 format is follows the standard Workflow API format.
4403
4404 More to come...
4405
4406 Parameters:
4407 =============================================================================*/
4408 FUNCTION config_line_exists(
4409 p_model_line_id in number
4410 )
4411 RETURN boolean
4412
4413 IS
4414 l_config_item NUMBER := 0;
4415
4416 BEGIN
4417 select 1
4418 into l_config_item
4419 from oe_order_lines_all oelM,
4420 oe_order_lines_all oelC
4421 where oelM.line_id = p_model_line_id
4422 and oelC.ato_line_id = oelM.line_id
4423 and oelC.item_type_code = 'CONFIG';
4424
4425 if (l_config_item > 0) then
4426 return TRUE;
4427 end if;
4428
4429 EXCEPTION
4430 when NO_DATA_FOUND then
4431 return FALSE;
4432
4433 when OTHERS then
4434 return FALSE;
4435
4436 end config_line_exists;
4437
4438
4439
4440
4441
4442 procedure config_item_created_wf(
4443 p_itemtype in VARCHAR2, /*w workflow item type */
4444 p_itemkey in VARCHAR2, /* config line id */
4445 p_actid in NUMBER, /* ID number of WF activity */
4446 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4447 x_result out NoCopy VARCHAR2 /* result of activity */
4448 )
4449 is
4450 begin
4451 if( config_line_exists( p_itemkey) ) then
4452 -- x_result := 'COMPLETE:COMPLETE' ;
4453 x_result := 'COMPLETE:CONFIG_CREATED' ;
4454
4455 else
4456
4457 -- x_result := 'COMPLETE:INCOMPLETE' ;
4458 x_result := 'COMPLETE:CONFIG_NOT_CREATED' ;
4459
4460 end if;
4461
4462
4463
4464 end config_item_created_wf ;
4465
4466 /*============================================================================
4467 Procedure: reservation_exists
4468 Description: This procedure gets called when executing the Match
4469 Configuration activity in the ATO workflow. The
4470 format is follows the standard Workflow API format.
4471
4472 More to come...
4473
4474 Parameters:
4475 =============================================================================*/
4476 FUNCTION reservation_exists(
4477 p_config_line_id in number,
4478 x_reserved_qty out NoCopy number
4479 )
4480 RETURN boolean
4481
4482 IS
4483 x_reserved_quantity NUMBER := 0;
4484 l_source_document_type_id NUMBER; -- bugfix 1799874
4485
4486 BEGIN
4487
4488 --bugfix 1799874 start
4489 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => p_config_line_id );
4490 --bugfix 1799874 end
4491
4492 select sum(nvl(mrs.reservation_quantity,0))
4493 into x_reserved_quantity
4494 from mtl_system_items msi,
4495 oe_order_lines_all oel,
4496 mtl_reservations mrs
4497 where oel.line_id = p_config_line_id
4498 and oel.open_flag = 'Y'
4499 --and (oel.ordered_quantity - oel.cancelled_quantity) > 0
4500 and oel.ordered_quantity > 0 -- bugfix 2017099
4501 and oel.inventory_item_id = msi.inventory_item_id
4502 and msi.organization_id = oel.ship_from_org_id
4503 and oel.item_type_code = 'CONFIG'
4504 and oel.schedule_status_code = 'SCHEDULED'
4505 and oel.booked_flag = 'Y'
4506 and (oel.cancelled_flag = 'N'
4507 or oel.cancelled_flag is null)
4508 and msi.replenish_to_order_flag = 'Y'
4509 and msi.pick_components_flag = 'N'
4510 and msi.bom_item_type = 4
4511 and msi.base_item_id is not NULL
4512 and mrs.demand_source_line_id = oel.line_id
4513 and mrs.demand_source_header_id is not NULL
4514 and mrs.organization_id = oel.ship_from_org_id
4515 --and mrs.demand_source_type_id = inv_reservation_global.g_source_type_oe
4516 and mrs.demand_source_type_id =
4517 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
4518 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
4519 and mrs.supply_source_type_id =
4520 inv_reservation_global.g_source_type_inv
4521 and mrs.reservation_quantity > 0
4522 group by oel.line_id;
4523
4524 return TRUE;
4525
4526 EXCEPTION
4527 when NO_DATA_FOUND then
4528 return FALSE;
4529
4530 when OTHERS then
4531 return FALSE;
4532
4533 end reservation_exists;
4534
4535 function flow_sch_exists(pLineId in number)
4536 return boolean
4537 is
4538
4539 lWipEntityId number;
4540
4541 begin
4542
4543 select wip_entity_id
4544 into lWipEntityId
4545 from wip_flow_schedules wfs,
4546 oe_order_lines_all oel,
4547 oe_order_headers_all oeh,
4548 oe_transaction_types_all ota,
4549 oe_transaction_types_tl otl,
4550 mtl_sales_orders mso
4551 where wfs.demand_source_line = to_char(pLineId) --config line id
4552 and oel.line_id = pLineId
4553 and oeh.header_id = oel.header_id
4554 and oeh.order_type_id = ota.transaction_type_id
4555 and ota.transaction_type_code='ORDER'
4556 and ota.transaction_type_id = otl.transaction_type_id
4557 and oeh.order_number = mso.segment1
4558 and otl.name = mso.segment2
4559 and otl.language = (select language_code
4560 from fnd_languages
4561 where installed_flag = 'B')
4562 and mso.sales_order_id = wfs.demand_source_header_id
4563 and oel.inventory_item_id = wfs.primary_item_id
4564 and rownum = 1;
4565
4566 IF PG_DEBUG <> 0 THEN
4567 oe_debug_pub.add ('flow_sch_exists: ' || 'Flow Schedule Exists!', 1);
4568 END IF;
4569 return TRUE; -- Flow Schedule exists
4570
4571 exception
4572 when no_data_found then
4573
4574 IF PG_DEBUG <> 0 THEN
4575 oe_debug_pub.add ('flow_sch_exists: ' || 'Flow Schedules does not exist ', 1);
4576 END IF;
4577 return FALSE; -- Flow Schedule does not exist
4578
4579 when others then
4580 return FALSE;
4581
4582 end flow_sch_exists;
4583
4584
4585
4586 PROCEDURE Purchase_price_calc_wf(
4587 p_itemtype in VARCHAR2, /* workflow item type */
4588 p_itemkey in VARCHAR2, /* sales order line id */
4589 p_actid in number, /* ID number of WF activity */
4590 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4591 x_result out NoCopy VARCHAR2 /* result of activity */
4592
4593 )
4594 IS
4595 xreturnstatus varchar2(1);
4596 xmsgcount Number;
4597 xmsgdata Varchar2(800);
4598 x_oper_unit_list cto_auto_procure_pk.oper_unit_tbl;
4599 L_STMT_NUM Number;
4600 l_ato_line_id Number;
4601 l_batch_no Number;
4602 BEGIN
4603
4604 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4605 IF PG_DEBUG <> 0 THEN
4606 oe_debug_pub.add('Purchase_price_calc_wf: ' || 'CTO Activity: Purchase Price Calc wf.',1);
4607 END IF;
4608
4609 select ato_line_id
4610 into l_ato_line_id
4611 from oe_order_lines_all
4612 where line_id = p_itemkey;
4613
4614 if (p_funcmode = 'RUN') then
4615 /*
4616 CTO_AUTO_PROCURE_PK.Create_Purchasing_Doc(
4617 p_top_model_line_id => l_ato_line_id,
4618 p_overwrite_list_price => 'N',
4619 p_called_in_batch => 'N',
4620 p_batch_number => l_batch_no,
4621 p_ato_line_id => l_ato_line_id,
4622 x_oper_unit_list => x_oper_unit_list,
4623 x_return_status => xReturnStatus,
4624 x_msg_count => XMsgCount,
4625 x_msg_data => xmsgdata);
4626
4627 if xreturnstatus = FND_API.G_RET_STS_ERROR then
4628 IF PG_DEBUG <> 0 THEN
4629 oe_debug_pub.add('Purchase_price_calc_wf: ' || ' Failed in Create_purchasing_doc call...',1);
4630 END IF;
4631 -- raise FND_API.G_EXC_ERROR;
4632 elsif xreturnstatus = FND_API.G_RET_STS_UNEXP_ERROR then
4633 IF PG_DEBUG <> 0 THEN
4634 oe_debug_pub.add('Purchase_price_calc_wf: ' || ' Failed in Create_purchasing_doc call...',1);
4635 END IF;
4636 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
4637 end if;
4638 */
4639
4640 oe_debug_pub.add('Purchase_price_calc_wf: ' || ' Failed to call Create_purchasing_doc call...',1);
4641
4642
4643 end if;
4644 OE_STANDARD_WF.Save_Messages;
4645 OE_STANDARD_WF.Clear_Msg_Context;
4646
4647 Exception when OTHERS then
4648 IF PG_DEBUG <> 0 THEN
4649 oe_debug_pub.add('Purchase_price_calc_wf: ' || 'CTO_WORKFLOW.Purchase_Price_calc_wf: '
4650 || to_char(l_stmt_num) || ':' ||
4651 substrb(sqlerrm, 1, 100));
4652 END IF;
4653 wf_core.context('CTO_WORKFLOW', 'Purchase_Price_calc_wf',
4654 p_itemtype, p_itemkey, to_char(p_actid),
4655 p_funcmode);
4656 raise;
4657
4658 End Purchase_Price_calc_wf;
4659
4660
4661 /*============================================================================
4662 Procedure: chk_rsv_after_afas_wf
4663 Description:
4664 The format follows the standard Workflow API format.
4665 06/04/02 bugfix2327972
4666 | added a new function node which calls procedure
4667 | chk_rsv_after_afas_wf.
4668 | This nodes checks if any type of reservation
4669 | exists. Node has been added in warning path after
4670 | autocreate fas node
4671 | This calls CTO_UTILITY_PK.chk_all_rsv_details to
4672 | check if any reservations exits for this line
4673 |
4674
4675 Parameters:
4676 =============================================================================*/
4677
4678 PROCEDURE chk_rsv_after_afas_wf (
4679 p_itemtype in VARCHAR2, /* item type */
4680 p_itemkey in VARCHAR2, /* config line id */
4681 p_actid in NUMBER, /* ID number of WF activity */
4682 p_funcmode in VARCHAR2, /* execution mode of WF activity */
4683 x_result out NoCopy VARCHAR2 /* result of activity */
4684 )
4685 IS
4686
4687 l_stmt_num NUMBER;
4688
4689 l_msg_count number;
4690 l_msg_data varchar2(2000);
4691 l_return_status varchar2(1);
4692 l_rsv_details CTO_UTILITY_PK.t_resv_details;
4693
4694
4695
4696
4697 BEGIN
4698 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4699 IF PG_DEBUG <> 0 THEN
4700 oe_debug_pub.add('chk_rsv_after_afas_wf: ' || 'CTO Activity: Check Reservation after afas activity ', 1);
4701 END IF;
4702
4703 IF (p_funcmode = 'RUN') then
4704
4705 l_stmt_num := 260;
4706
4707 CTO_UTILITY_PK.chk_all_rsv_details
4708 ( p_itemkey,
4709 l_rsv_details,
4710 l_msg_count,
4711 l_msg_data,
4712 l_return_status
4713 );
4714
4715 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4716 x_result :='COMPLETE:RESERVED';
4717 ELSE
4718 x_result := 'COMPLETE';
4719 END IF;
4720
4721 END IF ; /*p_funcmode ='RUN"*/
4722
4723 OE_STANDARD_WF.Save_Messages;
4724 OE_STANDARD_WF.Clear_Msg_Context;
4725
4726 EXCEPTION
4727 when FND_API.G_EXC_ERROR then
4728 IF PG_DEBUG <> 0 THEN
4729 OE_DEBUG_PUB.add('chk_rsv_after_afas_wf: ' || 'CTO_WORKFLOW.chk_rsv_after_afas_wf' || to_char(l_stmt_num) );
4730 END IF;
4731 OE_STANDARD_WF.Save_Messages;
4732 OE_STANDARD_WF.Clear_Msg_Context;
4733 raise; -- can be re-tried
4734
4735 when FND_API.G_EXC_UNEXPECTED_ERROR then
4736 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4737 FND_MSG_PUB.Add_Exc_Msg
4738 (G_PKG_NAME
4739 ,'chk_rsv_after_afas_wf'
4740 );
4741 end if;
4742
4743 IF PG_DEBUG <> 0 THEN
4744 oe_debug_pub.add('chk_rsv_after_afas_wf: ' || 'corresponds to unexpected error at called program chk_rsv_after_afas_wf '||'
4745 l_stmt_num :'|| l_stmt_num ||sqlerrm, 1);
4746 END IF;
4747 OE_STANDARD_WF.Save_Messages;
4748 OE_STANDARD_WF.Clear_Msg_Context;
4749 wf_core.context('CTO_WORKFLOW', 'chk_rsv_after_afas_wf', p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4750 raise;
4751
4752 when OTHERS then
4753 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
4754 FND_MSG_PUB.Add_Exc_Msg
4755 (G_PKG_NAME
4756 ,'chk_rsv_after_afas_wf'
4757 );
4758 end if;
4759 IF PG_DEBUG <> 0 THEN
4760 oe_debug_pub.add('chk_rsv_after_afas_wf: ' || 'error at chk_rsv_after_afas_wf' || to_char(l_stmt_num)|| sqlerrm);
4761 END IF;
4762 /*-------------------------------------------+
4763 Error Information for Notification.
4764 +--------------------------------------------*/
4765 wf_core.context('CTO_WORKFLOW','chk_rsv_after_afas_wf',p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4766 raise;
4767
4768 END chk_rsv_after_afas_wf;
4769
4770
4771 --This will get called from a new node in create supply subprocess
4772 --added fro DMF-J
4773 --new node is : Check supply creation which befor create supply order
4774 --block activity
4775 --create by KKONADA
4776 PROCEDURE check_supply_creation_wf(
4777 p_itemtype in VARCHAR2, /*item type */
4778 p_itemkey in VARCHAR2, /* config line id */
4779 p_actid in number, /* ID number of WF activity */
4780 p_funcmode in VARCHAR2, /* execution mode of WF activity*/
4781 x_result out NoCopy VARCHAR2 /* result of activity */
4782 )
4783 IS
4784 l_can_create_supply VARCHAR2(1);
4785 l_source_type NUMBER;
4786 l_return_status VARCHAR2(1);
4787 l_msg_count NUMBER;
4788 l_msg_data VARCHAR2(2000);
4789 l_stmt_num NUMBER;
4790
4791 l_inventory_item_id number;
4792 l_ship_from_org_id number;
4793 l_item_type_code Oe_order_lines_all.item_type_code%type;
4794 l_source_type_code oe_order_lines.source_type_code%type ;
4795
4796 l_status NUMBER;
4797 -- l_return_status VARCHAR2(1);
4798 l_header_id NUMBER;
4799 return_value NUMBER;
4800
4801 --opm
4802 l_sourcing_org number;
4803 l_message varchar2(100);
4804
4805 BEGIN
4806
4807 OE_STANDARD_WF.Set_Msg_Context(p_actid);
4808 IF PG_DEBUG <> 0 THEN
4809 oe_debug_pub.add('check_supply_creation_wf: ' || 'CTO Activity: Check Supply Type', 1);
4810
4811 oe_debug_pub.add('check_supply_creation_wf: ' || 'Item key = '||p_itemkey,1);
4812
4813 oe_debug_pub.add('check_supply_creation_wf: ' || 'Func Mode ='||p_funcmode,1);
4814 END IF;
4815
4816 IF (p_funcmode = 'RUN') THEN
4817
4818
4819
4820 l_stmt_num:=10;
4821 select inventory_item_id, ship_from_org_id,item_type_code, source_type_code,header_id
4822 into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code,l_header_id
4823 from oe_order_lines_all
4824 where line_id = to_number(p_itemkey)
4825 and ato_line_id is not null;
4826 IF PG_DEBUG <> 0 THEN
4827 oe_debug_pub.add('check_supply_creation_wf: ' || 'Inventory_item_id ='||to_char(l_inventory_item_id),1);
4828
4829 oe_debug_pub.add('check_supply_creation_wf: ' || 'Ship from org id ='||to_char(l_ship_from_org_id),1);
4830
4831 oe_debug_pub.add('check_supply_creation_wf: ' || 'Item type code ='||l_item_type_code,1);
4832
4833
4834 oe_debug_pub.add('check_supply_creation_wf: ' || 'l_source_type_code ='||l_source_type_code,1);
4835 END IF;
4836
4837 /*
4838
4839 ** need to branch on source type for drop ship functionality
4840 */
4841 l_stmt_num:=20;
4842 IF( l_source_type_code = 'EXTERNAL' )
4843 THEN
4844
4845 IF PG_DEBUG <> 0 THEN
4846 oe_debug_pub.add('check_supply_creation_wf: ' || 'It is Config item Drop Ship case...',1);
4847 END IF;
4848 x_result := 'COMPLETE';
4849 OE_STANDARD_WF.Save_Messages;
4850 OE_STANDARD_WF.Clear_Msg_Context;
4851
4852
4853
4854
4855
4856
4857
4858 l_stmt_num:=25;
4859 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(to_number(p_itemkey));
4860
4861
4862 IF PG_DEBUG <> 0 THEN
4863 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4864 ||return_value ,5);
4865 END IF;
4866
4867 if return_value <> 1 then
4868 IF PG_DEBUG <> 0 THEN
4869 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4870 ||return_value ,1);
4871 END IF;
4872 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
4873 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4874 end if;
4875
4876
4877
4878
4879 return;
4880
4881
4882 END IF ;
4883
4884 l_stmt_num:=30;
4885 CTO_UTILITY_PK.check_cto_can_create_supply
4886 (
4887 P_config_item_id => l_inventory_item_id,
4888 P_org_id => l_ship_from_org_id,
4889 x_can_create_supply => l_can_create_supply,
4890 p_source_type => l_source_type,
4891 x_return_status => l_return_status,
4892 X_msg_count => l_msg_count,
4893 X_msg_data => l_msg_data,
4894 x_sourcing_org => l_sourcing_org, --new param R12 OPM
4895 x_message => l_message --new param R12 OPM
4896 );
4897
4898 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
4899 IF l_can_create_supply = 'N' THEN
4900
4901 IF PG_DEBUG <> 0 THEN
4902 oe_debug_pub.add('check_supply_creation_wf: ' || 'It is Config item Planning case...',1);
4903 oe_debug_pub.add('check_supply_creation_wf: ' || l_message,1);
4904 END IF;
4905
4906 l_stmt_num:=40;
4907 x_result := 'COMPLETE:PLANNING';
4908
4909 IF PG_DEBUG <> 0 THEN
4910 oe_debug_pub.add('check_supply_creation_wf: ' || 'wrkflow result code'
4911 ||x_result ,5);
4912 END IF;
4913
4914
4915
4916 ELSE
4917
4918 l_stmt_num:=60;
4919 x_result := 'COMPLETE';
4920
4921 IF PG_DEBUG <> 0 THEN
4922 oe_debug_pub.add('check_supply_creation_wf: ' || 'wrkflow result code'
4923 ||x_result ,5);
4924 END IF;
4925
4926 END IF;--l_can_create_supply
4927
4928 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
4929 RAISE FND_API.G_EXC_ERROR;
4930 ELSE
4931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4932 END IF;--l_return_status
4933
4934
4935 l_stmt_num:=70;
4936 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(to_number(p_itemkey));
4937
4938
4939 IF PG_DEBUG <> 0 THEN
4940 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4941 ||return_value ,5);
4942 END IF;
4943
4944 if return_value <> 1 then
4945 IF PG_DEBUG <> 0 THEN
4946 oe_debug_pub.add('check_supply_creation_wf: ' || 'return value from display_wf_status'
4947 ||return_value ,1);
4948 END IF;
4949 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
4950 raise FND_API.G_EXC_UNEXPECTED_ERROR;
4951 end if;
4952
4953 END IF;--run
4954
4955 OE_STANDARD_WF.Save_Messages;
4956 OE_STANDARD_WF.Clear_Msg_Context;
4957
4958 EXCEPTION
4959
4960 when FND_API.G_EXC_ERROR then
4961 IF PG_DEBUG <> 0 THEN
4962 OE_DEBUG_PUB.add('check_supply_creation_wf: ' || 'CTO_WORKFLOW.check_supply_creation_wf ' ||
4963 to_char(l_stmt_num));
4964 END IF;
4965 OE_STANDARD_WF.Save_Messages;
4966 OE_STANDARD_WF.Clear_Msg_Context;
4967 --x_result := 'COMPLETE:INCOMPLETE';
4968 wf_core.context('CTO_WORKFLOW', 'check_supply_creation_wf',
4969 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4970 raise;
4971
4972
4973 when FND_API.G_EXC_UNEXPECTED_ERROR then
4974 IF PG_DEBUG <> 0 THEN
4975 OE_DEBUG_PUB.add('check_supply_creation_wf: ' || 'CTO_WORKFLOW.check_supply_creation_wf ' ||
4976 to_char(l_stmt_num));
4977 END IF;
4978 OE_STANDARD_WF.Save_Messages;
4979 OE_STANDARD_WF.Clear_Msg_Context;
4980 wf_core.context('CTO_WORKFLOW', 'check_supply_creation_wf',
4981 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4982 raise;
4983
4984 when OTHERS then
4985 IF PG_DEBUG <> 0 THEN
4986 oe_debug_pub.add('check_supply_creation_wf: ' || 'CTO_WORKFLOW.check_supply_creation_wf' ||
4987 to_char(l_stmt_num));
4988 oe_debug_pub.add('check_supply_creation_wf: ' || 'errmsg' ||sqlerrm,1);
4989 END IF;
4990 wf_core.context('CTO_WORKFLOW', 'check_supply_creation_wf',
4991 p_itemtype, p_itemkey, to_char(p_actid), p_funcmode);
4992
4993 OE_STANDARD_WF.Save_Messages;
4994 OE_STANDARD_WF.Clear_Msg_Context;
4995
4996 raise;
4997
4998
4999 END check_supply_creation_wf;
5000
5001
5002
5003
5004
5005
5006 END CTO_WORKFLOW;