1 package body CTO_MATCH_AND_RESERVE as
2 /* $Header: CTOMCRSB.pls 120.1.12010000.2 2008/08/26 19:14:04 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 : CTOMCRSB.pls |
11 | DESCRIPTION: |
12 | This file creates a package that containst procedures called |
13 | from the Match and Reserve menu from Order Entry's Enter |
14 | Orders form. |
15 | |
16 | match_inquiry - |
17 | This function is called when the Match and Reserve |
18 | menu is invoked. It does the following: |
19 | 1. Checks if the order line is eligible to be matched |
20 | and reserved. |
21 | 2. If it is, it determines if the order line is already |
22 | linked to a configuration item. If it is, it uses that |
23 | config item in the available quantity inquiry. If it |
24 | does not, it attempts to match the configuration from |
25 | oe_order_lines_all against bom_ato_configurations. |
26 | 3. If a configuration item exists, it calls Inventory's |
27 | API to query available quantity (on-hand and available |
28 | to reserve). If it has any quantity available to reserve |
29 | it returns true. |
30 | |
31 | |
32 | To Do: Handle Errors. Need to discuss with Usha and Girish what |
33 | error information to include in Notification. |
34 | |
35 | HISTORY : |
36 | May 10, 99 Angela Makalintal Initial version |
37 | |
38 | APR 01, 02 Renga Kannan Added call to Purchase |
39 | price rollup |
40 | 05/03/2002 Sushant Sawant |
41 | BUGFIX#2342412 |
42 | update config line status |
43 | after matched item is linked |
44 | 05/09/2002 Sushant Sawant |
45 | BUGFIX#2367720 |
46 | match_inquiry should return |
47 | available qty as 0 for |
48 | dropshipped items |
49 | |
50 | 10/25/2002 Kundan Sarkar Bugfix 2644849 (2620282 in br)|
51 | Passing bom revision info |
52 | |
53 | 10/31/2002 Sushant Sawant Added Enhanced costing functionality
54 | for matched items .
55 |
56 |
57 |
58 | Modified : 13-APR-2004 Sushant Sawant
59 | Fixed Bug 3523260
60 | Match and Reserve should work for unbooked orders that are scheduled.
61 | No reservation should take place for unbooked orders.
62 |
63
64 |
65 | Modified : 14-MAY-2004 Sushant Sawant
66 | Fixed bug 3484511.
67 |
68 | Modified : Kiran Konada
69 | Fixed bug 3692727
70 | ship_from_org_id was bein inserted during call to match_configured_item
71 | (-->calls CTOMCFGB.insert_into_bcol_gt)
72 | as ship_from_org_id attribute was not initialzed , during runtime
73 | we were landing into datafound at element(1) of ship_from_org_id attr
74 | Modified the insert statment to populate null vale for attr shiP-from_org_id
75
76 *****************************************************************************
77 Dependencies introduced
78 Date : Patchset : Introduced by : File : Reason
79 10/31/02 11.5.9 Kundan Sarkar CTORCFGS.pls 2620282
80 10/31/02 11.5.9 Kundan Sarkar CTORCFGB.pls
81
82 =============================================================================*/
83
84 /*****************************************************************************
85 Function: match_inquiry
86
87 Description:
88
89 This function is called from the 'Match' action from the
90 Sales Order Pad form.
91
92 p_model_line_id - top model line id from oe_order_lines
93 p_automatic_reservation - true if reservation is done
94 automatically, without user
95 intervention. used by order import.
96 p_quantity_to_reserve - quantity to be reserved. used only
97 when p_automatic_reservation is true
98 p_reservation_uom_code - uom in which to make the reservation.
99 the x_available_qty returned is
100 in this uom.
101 x_match_config_id - config id of the matching configuration
102 from bom_ato_configurations
103 x_available_qty - available quantity for reservation
104 in p_reservation_uom_code.
105 x_error_message - error message if match function fails
106 x_message_name - name of error message if match
107 function fails
108
109
110 match_inquiry returns TRUE if the process is successful
111 (no process errors). If a match is found,
112 x_config_match_id is populated with the inventory item
113 id of the matching config item.
114
115 If a match is not found, match_inquiry returns true and
116 x_config_match_id is NULL.
117
118 x_available_qty is the quantity available to reserve for
119 the configuration item. If it is zero, the user is not
120 given the option to reserve.
121
122 x_quantity_reserved returns the total quantity reserved.
123
124 match_inquiry returns FALSE if the process encounters
125 any errors.
126
127 if p_automatic_reseravation is true, match_inquiry returns
128 TRUE if a reservation is successful. otherwise, it returns
129 FALSE.
130
131 12/2/99: Product Management wants Match and Reserve to do
132 a link to the matching item even if reservation cannot
133 be made due to insufficient available quantity.
134
135 The change has been made. Match_inquiry now performs
136 a link if a matching item is found.
137
138 05/01/00: Modifying match_inquiry to work for multilevel
139 configurations.
140 *****************************************************************************/
141
142 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
143
144 function match_inquiry(
145 p_model_line_id in NUMBER,
146 p_automatic_reservation in BOOLEAN,
147 p_quantity_to_reserve in NUMBER,
148 p_reservation_uom_code in VARCHAR2,
149 x_config_id out nocopy NUMBER,
150 x_available_qty out nocopy NUMBER,
151 x_quantity_reserved out nocopy NUMBER,
152 x_error_message out nocopy VARCHAR2,
153 x_message_name out nocopy varchar2
154 )
155 RETURN boolean
156
157 IS
158
159 l_stmt_num number := 0;
160 l_cfm_value number;
161 l_config_line_id number;
162 l_tree_id integer;
163 l_return_status varchar2(1);
164 l_x_error_msg_count number;
165 l_x_error_msg varchar2(500); --bugfix 2776026: increased the var size
166 l_x_error_msg_name varchar2(30);
167 l_x_table_name varchar2(30);
168 l_match_profile varchar2(10);
169 l_custom_match_profile varchar2(10);
170 l_org_id number;
171 l_model_id number;
172 l_primary_uom_code varchar(3);
173 l_x_config_id number;
174 l_top_model_line_id number;
175
176
177 l_header_id number;
178
179 l_x_qoh number;
180 l_x_rqoh number;
181 l_x_qs number;
182 l_x_qr number;
183 l_x_att number;
184 l_active_activity varchar2(30);
185 l_x_bill_seq_id number;
186 l_status integer;
187
188 x_return_status varchar2(1);
189 x_msg_count number;
190 x_msg_data varchar2(500); -- bugfix 2776026: increased the var size
191
192 PROCESS_ERROR EXCEPTION;
193 INVALID_LINE EXCEPTION;
194 BOM_NOT_DEFINED EXCEPTION;
195 INVALID_WORKFLOW_STATUS EXCEPTION;
196 RESERVATION_ERROR EXCEPTION;
197
198
199
200 l_source_type_code oe_order_lines_all.source_type_code%type ;
201 l_booked_flag oe_order_lines_all.booked_flag%type ;
202
203
204
205 cursor c_model_lines is
206 select line_id, parent_ato_line_id
207 from bom_cto_order_lines
208 where bom_item_type = 1
209 --and top_model_line_id = p_model_line_id
210 and ato_line_id = p_model_line_id
211 and nvl(wip_supply_type,0) <> 6
212 and ato_line_id is not null
213 order by plan_level desc;
214
215 -- Added by Renga Kannan on 04/01/2002 for Purchase Price rollup
216 x_oper_unit_list cto_auto_procure_pk.oper_unit_tbl;
217 l_batch_no Number;
218
219
220 v_cto_match_rec CTO_CONFIGURED_ITEM_GRP.CTO_MATCH_REC_TYPE ;
221
222
223 l_match_found varchar2(1) ;
224
225 lValidationOrg number ;
226
227 v_model_item_name varchar2(2000) ;
228 l_top_model_item_id number ;
229
230
231 l_token CTO_MSG_PUB.token_tbl ;
232
233
234
235 lPerformPPRollup varchar2(10) ;
236 lPerformCSTRollup varchar2(10) ;
237 lPerformFWCalc varchar2(10) ;
238
239 l_perform_match varchar2(2) ;
240
241 l_perform_flow_calc number := 1;
242
243
244 return_value NUMBER;
245
246
247 BEGIN
248
249 x_available_qty := 0;
250 x_config_id := NULL;
251 x_quantity_reserved := 0;
252
253 l_stmt_num := 50;
254 l_match_profile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
255 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
256
257 IF PG_DEBUG <> 0 THEN
258 oe_debug_pub.add('MATCH_CONFIG: ' || l_match_profile, 1);
259 oe_debug_pub.add('CUSTOM_MATCH: ' || l_custom_match_profile, 1);
260 END IF;
261
262
263 --
264 -- Do not match if config line exists.
265 --
266 l_stmt_num := 110;
267 if (config_line_exists(p_model_line_id,
268 l_config_line_id,
269 l_x_config_id) = TRUE)
270 then
271 IF PG_DEBUG <> 0 THEN
272 oe_debug_pub.add('Config Line Already Exists.', 1);
273 END IF;
274
275 x_message_name := 'CTO_CONFIG_ITEM_EXISTS';
276 return FALSE;
277 end if;
278
279 --
280 -- Validate model line. Check that model line has ship from org and
281 -- that bill is defined in the ship from org.
282 --
283
284 l_stmt_num := 100;
285 IF (cto_workflow.validate_line(p_model_line_id) = FALSE) THEN
286 raise INVALID_LINE;
287 END IF;
288
289 -- call to populate_bom_cto_order_lines with top_model_line_id
290 -- populating bcol using ato_line_id instead of top_model_line_id
291 -- change to support multiple ATO models under a PTO model, sajani
292
293 l_stmt_num := 101;
294 select top_model_line_id, inventory_item_id
295 into l_top_model_line_id, l_top_model_item_id
296 from oe_order_lines_all
297 where line_id = p_model_line_id;
298
299 IF PG_DEBUG <> 0 THEN
300 oe_debug_pub.add('Top Model Line Id: ' || to_char(l_top_model_line_id));
301 END IF;
302
303
304 l_stmt_num := 102;
305
306 IF PG_DEBUG <> 0 THEN
307 oe_debug_pub.add('Before populate_bcol.', 1);
308 END IF;
309
310 delete from bom_cto_order_lines where ato_line_id = p_model_line_id ;
311
312 IF PG_DEBUG <> 0 THEN
313 oe_debug_pub.add('CTOMCRSB: deleted bcol: ' || to_char(SQL%ROWCOUNT));
314 END IF;
315
316 delete from bom_cto_src_orgs_b where top_model_line_id = p_model_line_id ;
317
318 IF PG_DEBUG <> 0 THEN
319 oe_debug_pub.add('CTOMCRSB: deleted bcso_b : ' || to_char(SQL%ROWCOUNT));
320 END IF;
321
322
323
324 CTO_UTILITY_PK.Populate_Bcol(p_bcol_line_id => p_model_line_id,
325 x_return_status => x_Return_Status,
326 x_msg_count => X_Msg_Count,
327 x_msg_data => X_Msg_Data);
328
329 if x_return_status = FND_API.G_RET_STS_ERROR then
330 IF PG_DEBUG <> 0 THEN
331 oe_debug_pub.add ('Failed in populate_bcol with expected error.', 1);
332 END IF;
333
334 cto_msg_pub.cto_message('BOM','CTO_MATCH_AND_RESERVE');
335 raise FND_API.G_EXC_ERROR;
336
337 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
338 IF PG_DEBUG <> 0 THEN
339 oe_debug_pub.add ('Failed in populate_bcol with unexpected error.', 1);
340 END IF;
341
342 cto_msg_pub.cto_message('BOM','CTO_MATCH_AND_RESERVE');
343 raise FND_API.G_EXC_UNEXPECTED_ERROR;
344 end if;
345
346 IF PG_DEBUG <> 0 THEN
347 oe_debug_pub.add('After populate_bcol.', 1);
348 END IF;
349
350
351 l_stmt_num := 105;
352 select bcol.inventory_item_id, bcol.ship_from_org_id, perform_match
353 into l_model_id, l_org_id , l_perform_match
354 from bom_cto_order_lines bcol
355 where bcol.line_id = p_model_line_id;
356
357
358 --
359 -- Check Workflow status of model line.
360 -- Workflow needs to be at Create Config Item Eligible.
361 --
362
363 l_stmt_num := 120;
364 IF PG_DEBUG <> 0 THEN
365 oe_debug_pub.add('Configuration Line does not exist.', 1);
366 END IF;
367
368 CTO_WORKFLOW_API_PK.get_activity_status(
369 itemtype => 'OEOL',
370 itemkey => to_char(p_model_line_id),
371 linetype => 'MODEL',
372 activity_name => l_active_activity);
373
374 IF PG_DEBUG <> 0 THEN
375 oe_debug_pub.add('Workflow Status is: ' ||
376 l_active_activity, 1);
377 END IF;
378
379
380 /*
381 if (l_active_activity = 'NULL') then
382 IF PG_DEBUG <> 0 THEN
383 oe_debug_pub.add('Model Workflow Status not Eligible for MR.', 1);
384 END IF;
385
386 raise INVALID_WORKFLOW_STATUS;
387 end if;
388
389
390 Commented for Patchset J as match can be invoked after order is scheduled
391
392 */
393
394
395 -- This is the part that will change. We need to do the following:
396 -- 1. Select and mark the lines in oe_order_lines_all
397 -- 2. Add lines in bom_cto_order_lines
398 -- 3. Match up the tree (stop as soon as an assly does not match)
399 -- 4. If the final assembly matches,
400 -- a. add sourcing info in bom_cto_src_orgs
401 -- b. call create items, which will create all the items
402 -- in all the relevant orgs
403 -- c. link top config item to top model
404 -- 5. Unmark the records
405
406
407 --
408 -- This is the loop that traverses bom_cto_order_lines to match
409 -- each configured assembly from bottom to top. The loop
410 -- exits as soon as an assembly does not match.
411 --
412
413
414 if( l_perform_match = 'N' ) then
415
416 oe_debug_pub.add('Top Model is not Eligible for MR as match is ' || l_perform_match , 1);
417 oe_debug_pub.add('Top Model is not Eligible for MR iid is ' || l_top_model_item_id , 1);
418
419
420 select concatenated_segments into v_model_item_name
421 from mtl_system_items_kfv
422 where inventory_item_id = l_top_model_item_id
423 and rownum = 1 ;
424
425 oe_debug_pub.add('Top Model is not Eligible for MR name is ' || v_model_item_name , 1);
426
427 -- l_token(1).token_name := 'MODEL_NAME' ;
428 -- l_token(1).token_value := v_model_item_name ;
429
430 x_message_name := 'CTO_MATCH_NA' ;
431
432 l_stmt_num := 137;
433 delete from bom_cto_order_lines
434 where top_model_line_id = l_top_model_line_id;
435
436 IF PG_DEBUG <> 0 THEN
437 oe_debug_pub.add(x_error_message,1);
438 END IF;
439
440
441 return TRUE ;
442
443 end if ;
444
445
446
447 /* BUGFIX# 3484511 */
448 select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) ,-99)
449 into lValidationOrg
450 from oe_order_lines_all oel
451 where oel.line_id = p_model_line_id ;
452
453
454
455
456 select
457 line_id,
458 link_to_line_id,
459 ato_line_id,
460 top_model_line_id,
461 inventory_item_id,
462 component_code,
463 component_sequence_id,
464 lValidationOrg,
465 qty_per_parent_model,
466 ordered_quantity,
467 order_quantity_uom,
468 parent_ato_line_id,
469 perform_match,
470 plan_level,
471 bom_item_type,
472 wip_supply_type,
473 null --bugfix 3692727 ,null as shippig org doesnot matter
474 --during matching
475 bulk collect into
476 v_cto_match_rec.line_id,
477 v_cto_match_rec.link_to_line_id,
478 v_cto_match_rec.ato_line_id,
479 v_cto_match_rec.top_model_line_id,
480 v_cto_match_rec.inventory_item_id,
481 v_cto_match_rec.component_code,
482 v_cto_match_rec.component_sequence_id,
483 v_cto_match_rec.validation_org,
484 v_cto_match_rec.qty_per_parent_model,
485 v_cto_match_rec.ordered_quantity,
486 v_cto_match_rec.order_quantity_uom,
487 v_cto_match_rec.parent_ato_line_id,
488 v_cto_match_rec.perform_match,
489 v_cto_match_rec.plan_level,
490 v_cto_match_rec.bom_item_type,
491 v_cto_match_rec.wip_supply_type,
492 v_cto_match_rec.ship_from_org_id --bugfix 3692727
493 from bom_cto_order_lines
494 where ato_line_id = p_model_line_id
495 order by plan_level ;
496
497
498
499 oe_debug_pub.add ('match_inquiry: GOING TO CALL CTO_CONFIGURED_ITEM_GRP.match_configured_item ' , 1) ;
500
501 CTO_CONFIGURED_ITEM_GRP.match_configured_item (
502 p_api_version => 1.0,
503 /*
504 p_init_msg_list =>
505 p_commit =>
506 p_validation_level =>
507 */
508 x_return_status => x_return_status ,
509 x_msg_count => x_msg_count ,
510 x_msg_data => x_msg_data ,
511 p_action => 'CTO' ,
512 p_source => 'CTO' ,
513 p_cto_match_rec => v_cto_match_rec ) ;
514
515
516
517
518 oe_debug_pub.add ('match_inquiry: CTO_CONFIGURED_ITEM_GRP.match_configured_item done ' , 1) ;
519
520 IF ( x_return_status = fnd_api.G_RET_STS_ERROR) THEN
521 IF PG_DEBUG <> 0 THEN
522 oe_debug_pub.add ('Create_Item: ' ||
523 'CTO_CONFIGURED_ITEM_GRP.match_configured_item returned with expected error.');
524 END IF;
525 raise FND_API.G_EXC_ERROR;
526
527 ELSIF ( x_return_status = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
528 IF PG_DEBUG <> 0 THEN
529 oe_debug_pub.add ('Create_Item: ' ||
530 'CTO_CONFIGURED_ITEM_GRP.match_configured_item returned with unexp error.');
531 END IF;
532 raise FND_API.G_EXC_UNEXPECTED_ERROR;
533
534 END IF;
535
536
537
538
539
540 l_x_config_id := null ;
541 for i in 1..v_cto_match_rec.line_id.count
542 loop
543
544 if( v_cto_match_rec.line_id(i) = p_model_line_id ) then
545 l_x_config_id := v_cto_match_rec.config_item_id(i) ;
546 exit ;
547 end if;
548
549 end loop ;
550
551
552
553 --
554 -- If match is found for top assembly, link it to top model line.
555 -- This starts the configuration line workflow. We then call
556 -- an API to move the model line workflow.
557 --
558 -- We then check if the configuration item can be reserved.
559 --
560
561
562 if (l_x_config_id is NULL) then
563 x_message_name := 'CTO_MR_NO_MATCH';
564 x_error_message := 'No matching configurations for line '
565 || to_char(l_top_model_line_id);
566
567 l_stmt_num := 137;
568 delete from bom_cto_order_lines
569 where top_model_line_id = l_top_model_line_id;
570
571 IF PG_DEBUG <> 0 THEN
572 oe_debug_pub.add(x_error_message,1);
573 END IF;
574
575
576
577 else
578
579 oe_debug_pub.add('CTOMCRSB: ' || 'Getting Profile Values ' , 1);
580
581 lPerformPPRollup := nvl( FND_PROFILE.Value('CTO_PERFORM_PURCHASE_PRICE_ROLLUP'), 1 ) ;
582 lPerformCSTRollup := nvl( FND_PROFILE.Value('CTO_PERFORM_COST_ROLLUP') , 1 ) ;
583 --Bugfix 6716677
584 --lPerformFWCalc := nvl( FND_PROFILE.Value('CTO_PERFORM_FLOW_CALC') , 1 );
585 lPerformFWCalc := nvl( FND_PROFILE.Value('CTO_PERFORM_FLOW_CALC') , 2 );
586
587 oe_debug_pub.add('CTOMCRSB: ' || 'Done Getting Profile Values ' , 1);
588
589 IF PG_DEBUG <> 0 THEN
590 oe_debug_pub.add('CTOMCRSB: ' || 'Profile Perform Purchase Price Rollup
591 ' || lPerformPPRollup , 1);
592 oe_debug_pub.add('CTOMCRSB: ' || 'Profile Perform Cost Rollup ' || lPerformCSTRollup , 1);
593 oe_debug_pub.add('CTOMCRSB: ' || 'Profile Perform Flow Calculations ' || lPerformFWCalc , 1);
594 END IF;
595
596
597
598
599
600 if( lPerformFWCalc = 1 ) then
601 l_perform_flow_calc := 1;
602 oe_debug_pub.add('CTOMCSRB: ' || 'Flow Calc is 1 ' , 1);
603 else
604 --Begin Bugfix 6716677
605 if( lPerformFWCalc = 2 ) then
606 l_perform_flow_calc := 2;
607 oe_debug_pub.add('CTOMCRSB: ' || 'Flow Calc is 2 ' , 1);
608 else
609 l_perform_flow_calc := 3;
610 oe_debug_pub.add('CTOMCRSB: ' || 'Flow Calc is 3 ' , 1);
611 end if;
612 --End Bugfix 6716677
613 end if ;
614
615
616 -- populate bom_cto_src_orgs to create items and boms
617 IF PG_DEBUG <> 0 THEN
618 oe_debug_pub.add('Before Populate_Src_Orgs');
619 END IF;
620
621 l_stmt_num := 140;
622
623 l_Status := CTO_MSUTIL_PUB.Populate_Src_Orgs(
624 pTopAtoLineId => p_model_line_id,
625 x_return_status => x_return_status,
626 x_msg_count => x_msg_count,
627 x_msg_data => x_msg_data);
628
629 IF ( l_Status <> 1 and X_Return_Status = FND_API.G_RET_STS_ERROR) THEN
630 IF PG_DEBUG <> 0 THEN
631 oe_debug_pub.add('CTO_MSUTIL_PUB.Populate_src_orgs returned with exp error',1);
632 END IF;
633
634 raise FND_API.G_EXC_ERROR;
635
636 ELSIF ( l_Status <> 1 and X_Return_Status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
637 IF PG_DEBUG <> 0 THEN
638 oe_debug_pub.add('CTO_MSUTIL_PUB.Populate_src_orgs returned with unexp error',1);
639 END IF;
640
641 raise FND_API.G_EXC_UNEXPECTED_ERROR;
642
643 END IF;
644
645
646 IF PG_DEBUG <> 0 THEN
647 oe_debug_pub.add('Ater CTO_MSUTIL_PUB.Populate_Src_Orgs', 2);
648 END IF;
649
650
651 -- call create_all_items, which will go through
652 -- bom_cto_order_lines and create all items in all src orgs
653
654 IF PG_DEBUG <> 0 THEN
655 oe_debug_pub.add('Calling Create_All_Items');
656 END IF;
657
658
659 l_stmt_num := 145;
660
661 l_status := CTO_ITEM_PK.Create_All_Items(
662 pTopAtoLineId => p_model_line_id,
663 xReturnStatus => x_Return_Status,
664 xMsgCount => x_msg_count,
665 XMsgData => x_msg_data);
666
667 IF (l_status <> 1 and x_Return_Status = fnd_api.g_ret_sts_error ) then
668 IF PG_DEBUG <> 0 THEN
669 oe_debug_pub.add ('Create_All_Items returned with 0', 1);
670 END IF;
671
672
673 --cto_msg_pub.cto_message('BOM','CTO_MATCH_AND_RESERVE');
674 raise FND_API.G_EXC_ERROR;
675
676 ELSIF (l_status <> 1 and x_Return_Status = fnd_api.g_ret_sts_unexp_error ) then
677 IF PG_DEBUG <> 0 THEN
678 oe_debug_pub.add ('Create_All_Items returned with 0', 1);
679 END IF ;
680
681
682 --cto_msg_pub.cto_message('BOM','CTO_MATCH_AND_RESERVE');
683 raise FND_API.G_EXC_UNEXPECTED_ERROR;
684
685 END IF;
686
687
688
689
690
691
692
693
694
695
696 -- call create_all_boms_and_rtgs
697 l_stmt_num := 146;
698 CTO_BOM_RTG_PK.create_all_boms_and_routings(
699 pAtoLineId => p_model_line_id,
700 pFlowCalc => l_perform_flow_calc ,
701 xReturnStatus => x_return_status,
702 xMsgCount => x_msg_count,
703 xMsgData => x_msg_data);
704
705 IF PG_DEBUG <> 0 THEN
706 oe_debug_pub.add('Matching Final Assy Item is: ' ||
707 to_char(l_x_config_id),1);
708 END IF;
709
710
711
712
713 l_stmt_num := 147;
714 l_status := CTO_CONFIG_ITEM_PK.link_item(
715 pOrgId => l_org_id,
716 pModelId => l_model_id,
717 pConfigId => l_x_config_id,
718 pLineId => p_model_line_id,
719 xMsgCount => x_msg_count,
720 xMsgData => x_msg_data);
721
722 if (l_status <> 1) then
723
724 IF PG_DEBUG <> 0 THEN
725 oe_debug_pub.add ('Failed in link_item function', 1);
726 END IF;
727
728 raise PROCESS_ERROR;
729
730 end if;
731 IF PG_DEBUG <> 0 THEN
732 oe_debug_pub.add ('Success in link_item function', 1);
733 END IF;
734
735
736
737
738 if (CTO_WORKFLOW_API_PK.start_model_workflow(p_model_line_id) = FALSE)
739 then
740 IF PG_DEBUG <> 0 THEN
741 oe_debug_pub.add('Failed in call to start_model_workflow',1);
742 END IF;
743
744 raise PROCESS_ERROR;
745 end if;
746
747 x_config_id := l_x_config_id;
748 x_message_name := 'CTO_CONFIG_LINKED';
749
750
751 l_stmt_num := 149;
752
753
754 /* BUGFIX#2342412 */
755 select line_id, header_id , source_type_code , booked_flag
756 into l_config_line_id, l_header_id , l_source_type_code , l_booked_flag
757 from oe_order_lines_all
758 where ato_line_id = p_model_line_id
759 and item_type_code = 'CONFIG';
760
761
762 IF PG_DEBUG <> 0 THEN
763 oe_debug_pub.add('Calling flow status API ',1);
764 END IF;
765
766
767 /*
768
769 IMPORTANT!!!!
770 FLOW STATUS CODE needs to be changed using CTO API
771
772 */
773
774
775
776
777 return_value:= CTO_WORKFLOW_API_PK.display_wf_status(l_config_line_id);
778
779
780 IF PG_DEBUG <> 0 THEN
781 oe_debug_pub.add('CTOMCRSB: ' || 'return value from display_wf_status' ||return_value ,5);
782 END IF;
783
784 if return_value <> 1 then
785 IF PG_DEBUG <> 0 THEN
786 oe_debug_pub.add('CTOMCRSB: ' || 'return value from display_wf_status' ||return_value ,1);
787 END IF;
788 cto_msg_pub.cto_message('CTO', 'CTO_ERROR_FROM_DISPLAY_STATUS');
789 raise FND_API.G_EXC_UNEXPECTED_ERROR;
790 end if;
791
792
793
794
795
796 /* BUGFIX#2342412
797 OE_Order_WF_Util.Update_Flow_Status_Code(
798 p_header_id => l_header_id,
799 p_line_id => l_config_line_id,
800 p_flow_status_code => 'BOM_AND_RTG_CREATED',
801 x_return_status => l_return_status);
802
803 IF PG_DEBUG <> 0 THEN
804 oe_debug_pub.add('Return from flow status API ' ||l_return_status,1);
805 END IF;
806
807
808 */
809
810
811
812 /* BUG#2367720 */
813 if( l_source_type_code = 'INTERNAL' AND l_booked_flag = 'Y' ) then
814
815
816
817
818
819
820
821 /*-------------------------------------------------+
822 Create a quantity tree to get atr for reservation.
823 +--------------------------------------------------*/
824 l_stmt_num := 150;
825 INV_QUANTITY_TREE_GRP.create_tree
826 ( p_api_version_number => 1.0
827 , p_init_msg_lst => fnd_api.g_false
828 , x_return_status => l_return_status
829 , x_msg_count => x_msg_count
830 , x_msg_data => x_msg_data
831 , p_organization_id => l_org_id
832 , p_inventory_item_id => x_config_id
833 , p_tree_mode => inv_quantity_tree_pub.g_reservation_mode
834 , p_is_revision_control => FALSE
835 , p_is_lot_control => FALSE
836 , p_is_serial_control => FALSE
837 , x_tree_id => l_tree_id);
838
839 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
840 IF PG_DEBUG <> 0 THEN
841 oe_debug_pub.add('Failed in create_tree with status: ' ||
842 l_return_status, 1);
843 END IF;
844
845 raise PROCESS_ERROR;
846 ELSE
847 IF PG_DEBUG <> 0 THEN
848 oe_debug_pub.add('Success in create_tree.',1);
849 oe_debug_pub.add('Tree ID:' || to_char(l_tree_id),1);
850 END IF;
851
852 END IF;
853
854 /*-----------------------------------------------------+
855 Query quantity tree get quantity available to reserve.
856 +------------------------------------------------------*/
857 l_stmt_num := 160;
858 INV_QUANTITY_TREE_GRP.query_tree
859 (p_api_version_number => 1.0,
860 p_init_msg_lst => fnd_api.g_false,
861 x_return_status => l_return_status,
862 x_msg_count => x_msg_count,
863 x_msg_data => x_msg_data,
864 p_tree_id => l_tree_id,
865 p_revision => NULL,
866 p_lot_number => NULL,
867 p_subinventory_code => NULL,
868 p_locator_id => NULL,
869 x_qoh => l_x_qoh,
870 x_rqoh => l_x_rqoh,
871 x_qr => l_x_qr,
872 x_qs => l_x_qs,
873 x_att => l_x_att,
874 x_atr => x_available_qty);
875
876 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
877 IF PG_DEBUG <> 0 THEN
878 oe_debug_pub.add('Failed in create_tree with status: ' ||
879 l_return_status, 1);
880 END IF;
881 raise PROCESS_ERROR;
882 end if;
883
884 IF PG_DEBUG <> 0 THEN
885 oe_debug_pub.add('Success in query_tree.', 1);
886 oe_debug_pub.add('l_x_qoh: ' || to_char(l_x_qoh));
887 oe_debug_pub.add('l_x_rqoh: ' || to_char(l_x_rqoh));
888 oe_debug_pub.add('x_available_qty: ' || to_char(x_available_qty));
889 END IF;
890
891
892 l_stmt_num := 170;
893 select msi.primary_uom_code
894 into l_primary_uom_code
895 from mtl_system_items msi
896 where msi.inventory_item_id = x_config_id
897 and msi.organization_id = l_org_id;
898
899 /*------------------------------------------------------
900 The quantity query gives ATR in the primary uom code
901 so we need to convert it to the same uom as the
902 p_reservation_uom_code.
903 +------------------------------------------------------*/
904 IF (l_primary_uom_code <> p_reservation_uom_code) THEN
905 l_stmt_num := 175;
906 x_available_qty := inv_convert.inv_um_convert(
907 x_config_id,
908 5, -- bugfix 2204376: pass precision of 5
909 x_available_qty, -- from qty
910 l_primary_uom_code, -- from uom
911 p_reservation_uom_code, -- to uom
912 null,
913 null);
914 END IF;
915
916 /*---------------------------------------------------------+
917 p_automatic_reservation is TRUE when match and reserve is
918 called from Order Import. From Order Import, if a match
919 is found, a reservation is made automatically if there
920 is sufficient quantity.
921 +---------------------------------------------------------*/
922 if (x_available_qty >= p_quantity_to_reserve and
923 p_automatic_reservation = TRUE)
924 then
925 l_stmt_num := 180;
926 IF PG_DEBUG <> 0 THEN
927 oe_debug_pub.add('Entering Create Reservation. ',1);
928 oe_debug_pub.add('Quantity Available to Rsrv: '
929 || to_char(x_available_qty),1);
930 END IF;
931
932
933 if (create_config_reservation(p_model_line_id,
934 x_config_id,
935 p_quantity_to_reserve,
936 p_reservation_uom_code,
937 x_quantity_reserved,
938 l_x_error_msg,
939 l_x_error_msg_name) = TRUE)
940 then
941 IF PG_DEBUG <> 0 THEN
942 oe_debug_pub.add('Success in Create Reservation. ',1);
943 END IF;
944 else
945 IF PG_DEBUG <> 0 THEN
946 oe_debug_pub.add('Failed in Create Reservation. ',1);
947 END IF;
948
949 raise RESERVATION_ERROR;
950 end if;
951
952 end if; --x_available_qty >= p_quantity_to_reserve
953
954 /*--------------------------------------------------+
955 If available quantity to reserve is less than
956 zero, return with no option to reserve.
957 Otherwise, user has the option to reserve against
958 the ATR quantity.
959 +--------------------------------------------------*/
960 if (x_available_qty <= 0) then
961 l_stmt_num := 190;
962 IF PG_DEBUG <> 0 THEN
963 oe_debug_pub.add('Not Enough Qty to reserve. ',1);
964 oe_debug_pub.add('Quantity Available to Rsrv: '
965 || to_char(x_available_qty),1);
966 END IF;
967
968
969 x_message_name := 'CTO_CONFIG_LINKED';
970 x_error_message := 'Config Item Linked. No Qty to Rsrv';
971 --return TRUE;
972
973 else
974 IF PG_DEBUG <> 0 THEN
975 oe_debug_pub.add
976 ('Matching Config Item: ' || to_char(x_config_id),1 );
977 oe_debug_pub.add
978 ('Quantity On-Hand: ' || to_char(x_available_qty),1);
979 END IF ;
980
981
982 x_message_name := 'CTO_RESERVE';
983
984 end if;
985
986
987
988 else
989
990 /* IMPORTANT!! */
991
992 oe_debug_pub.add
993 ('Matching Config Item: ' || ' Will Not attempt Reservation as Order is either not booked or is Dropship ' ,1 );
994
995
996 /* BUG#2367720 */
997 end if; /* code to be restricted to INTERNAL source type only */
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010 if( lPerformPPRollup = 1 ) then
1011
1012
1013 -- Added by Renga Kannan on 04/01/02 to call the Purchase price rollup API
1014
1015 IF PG_DEBUG <> 0 THEN
1016 oe_debug_pub.add('Calling Purchase doc creation..',1);
1017 END IF;
1018
1019
1020
1021 CTO_AUTO_PROCURE_PK.Create_Purchasing_Doc(
1022 p_config_item_id => l_x_config_id,
1023 p_overwrite_list_price => 'N',
1024 p_called_in_batch => 'N',
1025 p_batch_number => l_batch_no,
1026 p_mode => 'ORDER',
1027 p_ato_line_id => p_model_line_id,
1028 x_oper_unit_list => x_oper_unit_list,
1029 x_return_status => x_Return_Status,
1030 x_msg_count => X_Msg_Count,
1031 x_msg_data => x_msg_data);
1032
1033
1034
1035
1036
1037
1038 if x_return_status = FND_API.G_RET_STS_ERROR then
1039 IF PG_DEBUG <> 0 THEN
1040 oe_debug_pub.add(' Failed in Create_purchasing_doc call...',1);
1041 END IF ;
1042
1043
1044 -- raise FND_API.G_EXC_ERROR;
1045 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1046 IF PG_DEBUG <> 0 THEN
1047 oe_debug_pub.add(' Failed in Create_purchasing_doc call...',1);
1048 END IF;
1049
1050
1051 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
1052 end if;
1053
1054
1055 else
1056
1057 IF PG_DEBUG <> 0 THEN
1058 oe_debug_pub.add('CTOMCRSB: ' || 'Will Not perform PP Rollup as profile is No ', 1);
1059 END IF;
1060
1061
1062
1063 end if; /* pp rollup based on profile */
1064
1065
1066
1067
1068 if( lPerformCSTRollup = 1 ) then
1069
1070
1071
1072
1073
1074 /* Changes for enhanced cost rollup */
1075
1076 IF PG_DEBUG <> 0 THEN
1077 oe_debug_pub.add('going to call cost rollup in CTOMCRSB for matched items.',1);
1078 END IF;
1079
1080
1081 l_status := CTO_CONFIG_COST_PK.cost_rollup_ml(
1082 pTopAtoLineId => p_model_line_id,
1083 x_msg_count => x_msg_count,
1084 x_msg_data => x_msg_data);
1085
1086 if (l_status = 0) then
1087 IF PG_DEBUG <> 0 THEN
1088 oe_debug_pub.add('Failure in cost_rollup ', 1);
1089 END IF;
1090
1091 cto_msg_pub.cto_message('BOM', x_msg_data);
1092 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1093 else
1094 IF PG_DEBUG <> 0 THEN
1095 oe_debug_pub.add('Success in cost_rollup ', 1);
1096 END IF;
1097
1098 end if;
1099
1100
1101
1102 else
1103
1104 IF PG_DEBUG <> 0 THEN
1105 oe_debug_pub.add('CTOMCRSB: ' || 'Will Not perform Cost Rollup as profile is No ', 1);
1106 END IF;
1107
1108
1109
1110 end if ; /* cost rollup based on profile */
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126 end if; -- end l_x_config_id is not null
1127
1128 -- clean up oe_order_lines_all batch_id column
1129
1130 return TRUE;
1131
1132 EXCEPTION
1133
1134 when INVALID_LINE then
1135 x_message_name := 'CTO_LINE_STATUS_NOT_ELIGIBLE';
1136 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1137 to_char(l_stmt_num) || ':' ||
1138 substrb(sqlerrm,1,100);
1139 IF PG_DEBUG <> 0 THEN
1140 oe_debug_pub.add(x_error_message, 1);
1141 END IF;
1142
1143 return FALSE;
1144
1145 when BOM_NOT_DEFINED then
1146 x_message_name := 'CTO_BOM_NOT_DEFINED';
1147 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1148 to_char(l_stmt_num) || ':' ||
1149 substrb(sqlerrm,1,100);
1150 IF PG_DEBUG <> 0 THEN
1151 oe_debug_pub.add(x_error_message, 1);
1152 END IF;
1153
1154 return FALSE;
1155
1156 when INVALID_WORKFLOW_STATUS then
1157 x_message_name:= 'CTO_INVALID_WORKFLOW_STATUS';
1158 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1159 to_char(l_stmt_num) || ':' ||
1160 substrb(sqlerrm,1,100);
1161 IF PG_DEBUG <> 0 THEN
1162 oe_debug_pub.add(x_error_message, 1);
1163 END IF;
1164
1165 return FALSE;
1166
1167 when PROCESS_ERROR then
1168 x_message_name := 'CTO_MATCH_ERROR';
1169 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1170 to_char(l_stmt_num) || ':' ||
1171 substrb(sqlerrm,1,100);
1172 IF PG_DEBUG <> 0 THEN
1173 oe_debug_pub.add(x_error_message, 1);
1174 END IF;
1175
1176 return FALSE;
1177
1178 when RESERVATION_ERROR then
1179 x_message_name := 'CTO_RESERVE_ERROR';
1180 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1181 to_char(l_stmt_num) || ':' ||
1182 substrb(sqlerrm,1,100);
1183 IF PG_DEBUG <> 0 THEN
1184 oe_debug_pub.add(x_error_message, 1);
1185 END IF;
1186
1187 return FALSE;
1188
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 x_error_message := 'CTOMCRSB:match_inquiry failed with expected error in stmt '
1191 ||to_char(l_stmt_num);
1192 IF PG_DEBUG <> 0 THEN
1193 oe_debug_pub.add ('match_inquiry: exp_error ' || to_char(l_stmt_num) ||sqlerrm,1);
1194 END IF;
1195
1196 return FALSE;
1197
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 x_error_message := 'CTOMCRSB:match_inquiry failed with unexpected error in stmt '
1200 ||to_char(l_stmt_num);
1201 IF PG_DEBUG <> 0 THEN
1202 oe_debug_pub.add ('match_inquiry: unexp_error ' || to_char(l_stmt_num) ||sqlerrm,1);
1203 END IF;
1204
1205 return FALSE;
1206
1207
1208 when OTHERS then
1209 x_message_name := 'CTO_MATCH_ERROR';
1210 x_error_message := 'CTOMCRSB:match_inquiry: '
1211 || to_char(l_stmt_num) || ':' ||
1212 substrb(sqlerrm,1,100);
1213 IF PG_DEBUG <> 0 THEN
1214 oe_debug_pub.add(x_error_message, 1);
1215 END IF;
1216
1217 return FALSE;
1218 END match_inquiry ;
1219
1220
1221 /*****************************************************************************
1222 Function: create_config_reservation
1223 Parameters: p_model_line_id - line id of the top model in oe_order_lines_all
1224 p_config_item_id - config id of the matching configuration
1225 from bom_ato_configurations
1226 p_quantity_to_reserve - quantity to reserve in ordered_quantity_uom
1227 x_error_message - error message if match function fails
1228 x_message_name - name of error message if match
1229 function fails
1230
1231 Description: This function is called after a match inquiry
1232 has been done and the user attempts to reserve
1233 available inventory. This is called from
1234 the Match and Reserve menu item.
1235
1236 match_and_reserve returns TRUE if the process is successful
1237 (no process errors) and a reservation is successully made.
1238
1239 match_and_reserve returns FALSE if the process fails to create
1240 the reservation.
1241 *****************************************************************************/
1242
1243
1244 function create_config_reservation(
1245 p_model_line_id IN NUMBER,
1246 p_config_item_id IN NUMBER,
1247 p_quantity_to_reserve IN NUMBER,
1248 p_reservation_uom_code IN VARCHAR2,
1249 x_quantity_reserved OUT nocopy NUMBER,
1250 x_error_msg OUT nocopy VARCHAR2,
1251 x_error_msg_name OUT nocopy VARCHAR2
1252 )
1253 return boolean
1254
1255 IS
1256
1257 l_stmt_num NUMBER := 0;
1258 l_rec_reserve CTO_RESERVE_CONFIG.rec_reserve;
1259 l_x_reserved_qty NUMBER := 0;
1260 l_x_reservation_id NUMBER;
1261 l_x_status VARCHAR(1);
1262 l_x_error_msg VARCHAR2(2000);
1263 l_x_error_msg_name VARCHAR2(30);
1264 l_x_error_msg_count NUMBER;
1265 l_x_table_name VARCHAR2(30);
1266 l_x_qoh NUMBER;
1267 l_x_rqoh NUMBER;
1268 l_x_qr NUMBER;
1269 l_x_qs NUMBER;
1270 l_x_att NUMBER;
1271 l_x_atr NUMBER;
1272 l_config_line_id NUMBER;
1273 l_config_id NUMBER;
1274 l_workflow_itemkey VARCHAR2(30);
1275 l_activity_result VARCHAR2(30);
1276 l_active_activity VARCHAR2(30);
1277 l_status NUMBER;
1278 lSourceCode varchar2(30);
1279
1280 -- 2620282 : New variable to store bom revision date
1281 l_rev_date date;
1282
1283 /* Handled Exceptions */
1284 PARAMETER_ERROR EXCEPTION;
1285 RESERVATION_ERROR EXCEPTION;
1286 PROCESS_ERROR EXCEPTION;
1287 INVALID_WORKFLOW_STATUS EXCEPTION;
1288
1289 BEGIN
1290 l_stmt_num := 100;
1291 IF (p_config_item_id is NULL or
1292 p_quantity_to_reserve is NULL or
1293 p_model_line_id is NULL)
1294 THEN
1295 raise PARAMETER_ERROR;
1296 END IF;
1297
1298 /*---------------------------------------------------+
1299 Link happens as part of the Match Inquiry. Verify
1300 that the configuration item has been linked.
1301 +---------------------------------------------------*/
1302 l_stmt_num := 110;
1303 IF (config_line_exists(p_model_line_id,
1304 l_config_line_id,
1305 l_config_id) = FALSE)
1306 THEN
1307 /*----------------------------------------------+
1308 Config line does not exist. Raise error.
1309 +-----------------------------------------------*/
1310 IF PG_DEBUG <> 0 THEN
1311 oe_debug_pub.add('create_config_reservation: ' || 'Config line does not exist. ', 1);
1312 END IF;
1313 l_stmt_num := 115;
1314 raise PROCESS_ERROR;
1315
1316 END IF;
1317
1318 /* 2620282 : Selecting bom revision date to pass it in the
1319 call to BOM_REVISIONS.get_item_revision_fn while getting config line
1320 information to perform reservation */
1321
1322 /* 4162494 : Join with wip_parameters assumes mfg org is the distribution org
1323 which is incorrect. */
1324
1325 l_stmt_num := 139;
1326 select trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
1327 'MI')+1/(60*24)
1328 into l_rev_date
1329 from bom_calendar_dates cal,
1330 mtl_parameters mp,
1331 -- 4162494 wip_parameters wp,
1332 mtl_system_items msi,
1333 oe_order_lines_all oel
1334 where oel.line_id = l_config_line_id
1335 and mp.organization_id = oel.ship_from_org_id
1336 -- 4162494 and wp.organization_id = mp.organization_id
1337 and msi.organization_id = oel.ship_from_org_id
1338 and msi.inventory_item_id = oel.inventory_item_id
1339 and cal.calendar_code = mp.calendar_code
1340 and cal.exception_set_id = mp.calendar_exception_set_id
1341 and cal.seq_num =
1342 (select greatest(1, (cal2.prior_seq_num -
1343 (ceil(nvl(msi.fixed_lead_time,0) +
1344 nvl(msi.variable_lead_time,0) *
1345 p_quantity_to_reserve
1346 ))))
1347 from bom_calendar_dates cal2
1348 where cal2.calendar_code = mp.calendar_code
1349 and cal2.exception_set_id =
1350 mp.calendar_exception_set_id
1351 and cal2.calendar_date =
1352 trunc(oel.schedule_ship_date)
1353 );
1354
1355 /*-----------------------------------------------------------------+
1356 Get necessary information from order line to perform reservation.
1357 The reservation against the configuration item is made against the
1358 configuration line, not the model line.
1359 +------------------------------------------------------------------*/
1360 l_stmt_num := 140;
1361 lSourceCode := fnd_profile.value('ONT_SOURCE_CODE');
1362 IF PG_DEBUG <> 0 THEN
1363 oe_debug_pub.add('create_config_reservation: ' || 'lSourceCode is '||lSourceCode, 2);
1364 END IF;
1365 select mso.sales_order_id,
1366 oel.line_id, -- config line id
1367 oel.ship_from_org_id,
1368 oel.inventory_item_id,
1369 oel.order_quantity_uom,
1370 p_quantity_to_reserve,
1371 inv_reservation_global.g_source_type_inv,
1372 NULL,
1373 oel.schedule_ship_date,
1374 oeh.source_document_type_id, -- bugfix 1799874: to check if it is an internal SO or regular
1375 -- 2776026: Pass revision only if item is revision contol.
1376 -- 2620282: Selecting bom revision information
1377 decode( nvl(msi.revision_qty_control_code, 1), 1, NULL ,
1378 BOM_REVISIONS.get_item_revision_fn (
1379 'ALL',
1380 'ALL',
1381 oel.ship_from_org_id,
1382 oel.inventory_item_id,
1383 l_rev_date
1384 ))
1385 into l_rec_reserve
1386 from oe_order_lines_all oel,
1387 oe_order_headers_all oeh,
1388 --oe_order_types_v oet,
1389 oe_transaction_types_tl oet,
1390 mtl_sales_orders mso,
1391 mtl_system_items msi
1392 where oel.line_id = l_config_line_id
1393 and oel.open_flag = 'Y'
1394 and item_type_code = 'CONFIG'
1395 and oeh.header_id = oel.header_id
1396 and oet.transaction_type_id = oeh.order_type_id
1397 and mso.segment1 = to_char(oeh.order_number)
1398 and mso.segment2 = oet.name
1399 and oet.language = (select language_code
1400 from fnd_languages
1401 where installed_flag = 'B')
1402 and mso.segment3 = lSourceCode
1403 -- and mso.segment3 = 'ORDER ENTRY'
1404 and oel.inventory_item_id = p_config_item_id
1405 and msi.inventory_item_id = oel.inventory_item_id
1406 and msi.organization_id = oel.ship_from_org_id
1407 and msi.base_item_id is not NULL;
1408
1409
1410 if (SQL%ROWCOUNT = 1) then
1411 l_stmt_num := 150;
1412 CTO_RESERVE_CONFIG.reserve_config(l_rec_reserve,
1413 l_x_reserved_qty,
1414 l_x_reservation_id,
1415 l_x_status,
1416 l_x_error_msg,
1417 l_x_error_msg_name);
1418 else
1419 raise PROCESS_ERROR;
1420 end if;
1421
1422 if (l_x_status = FND_API.g_ret_sts_success) then
1423 l_stmt_num := 160;
1424 IF PG_DEBUG <> 0 THEN
1425 oe_debug_pub.add
1426 ('create_config_reservation: ' || 'Success in reserve_config with reservation id:' ||
1427 to_char(l_x_reservation_id),1);
1428 END IF;
1429 else
1430 IF PG_DEBUG <> 0 THEN
1431 oe_debug_pub.add('create_config_reservation: ' || 'Failed in reserve_config.',1);
1432 END IF;
1433 raise PROCESS_ERROR;
1434
1435 end if;
1436
1437 x_error_msg_name := 'CTO_MR_SUCCESS';
1438 return TRUE;
1439
1440 EXCEPTION
1441 when PROCESS_ERROR then
1442 /* BUG#2367720 */
1443 if( l_x_error_msg_name is null ) then
1444 x_error_msg_name := 'CTO_RESERVE_ERROR';
1445
1446 else
1447
1448 x_error_msg_name := l_x_error_msg_name ;
1449
1450 end if ;
1451
1452 x_error_msg := 'CTOMCRSB:create_config_reservation: ' ||
1453 l_x_status || ': ' ||
1454 l_x_error_msg;
1455 IF PG_DEBUG <> 0 THEN
1456 oe_debug_pub.add('create_config_reservation: ' || x_error_msg, 1);
1457 END IF;
1458 return FALSE;
1459
1460 when OTHERS then
1461 x_error_msg_name := 'CTO_RESERVE_ERROR';
1462 x_error_msg := 'CTOMCRSB:create_config_reservation: ' ||
1463 to_char(l_stmt_num) || ':' ||
1464 substrb(sqlerrm,1,100);
1465 IF PG_DEBUG <> 0 THEN
1466 oe_debug_pub.add('create_config_reservation: ' || x_error_msg, 1);
1467 END IF;
1468 return FALSE;
1469
1470 END create_config_reservation;
1471
1472
1473 function config_line_exists(p_model_line_id IN NUMBER,
1474 x_config_line_id OUT nocopy NUMBER,
1475 x_config_item_id OUT nocopy NUMBER)
1476 return boolean
1477
1478 is
1479
1480 begin
1481 /***************************************************************
1482 If config line already exists, do not match. If a config
1483 line already exists, verify that the status of the configuration
1484 line allows a Match and Reserve to be performed.
1485 ***************************************************************/
1486 select oel.line_id, oel.inventory_item_id
1487 into x_config_line_id, x_config_item_id
1488 from oe_order_lines_all oel,
1489 mtl_system_items msi
1490 where oel.link_to_line_id = p_model_line_id
1491 and oel.item_type_code = 'CONFIG'
1492 and oel.inventory_item_id = msi.inventory_item_id
1493 and oel.ship_from_org_id = msi.organization_id
1494 and msi.base_item_id is not null
1495 and msi.bom_item_type = 4; --standard item
1496
1497 return TRUE;
1498
1499 exception
1500
1501 when NO_DATA_FOUND then
1502 return FALSE;
1503
1504 when OTHERS then
1505 return FALSE;
1506
1507 end;
1508
1509 end CTO_MATCH_AND_RESERVE;
[Home] [Help]