1 package body CTO_MATCH_AND_RESERVE as
2 /* $Header: CTOMCRSB.pls 120.3 2011/11/14 12:27:31 abhissri 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 --Bugfix 12374440
938 null, --Subinventory. Need to think if this has to be passed.
939 x_quantity_reserved,
940 l_x_error_msg,
941 l_x_error_msg_name) = TRUE)
942 then
943 IF PG_DEBUG <> 0 THEN
944 oe_debug_pub.add('Success in Create Reservation. ',1);
945 END IF;
946 else
947 IF PG_DEBUG <> 0 THEN
948 oe_debug_pub.add('Failed in Create Reservation. ',1);
949 END IF;
950
951 raise RESERVATION_ERROR;
952 end if;
953
954 end if; --x_available_qty >= p_quantity_to_reserve
955
956 /*--------------------------------------------------+
957 If available quantity to reserve is less than
958 zero, return with no option to reserve.
959 Otherwise, user has the option to reserve against
960 the ATR quantity.
961 +--------------------------------------------------*/
962 if (x_available_qty <= 0) then
963 l_stmt_num := 190;
964 IF PG_DEBUG <> 0 THEN
965 oe_debug_pub.add('Not Enough Qty to reserve. ',1);
966 oe_debug_pub.add('Quantity Available to Rsrv: '
967 || to_char(x_available_qty),1);
968 END IF;
969
970
971 x_message_name := 'CTO_CONFIG_LINKED';
972 x_error_message := 'Config Item Linked. No Qty to Rsrv';
973 --return TRUE;
974
975 else
976 IF PG_DEBUG <> 0 THEN
977 oe_debug_pub.add
978 ('Matching Config Item: ' || to_char(x_config_id),1 );
979 oe_debug_pub.add
980 ('Quantity On-Hand: ' || to_char(x_available_qty),1);
981 END IF ;
982
983
984 x_message_name := 'CTO_RESERVE';
985
986 end if;
987
988
989
990 else
991
992 /* IMPORTANT!! */
993
994 oe_debug_pub.add
995 ('Matching Config Item: ' || ' Will Not attempt Reservation as Order is either not booked or is Dropship ' ,1 );
996
997
998 /* BUG#2367720 */
999 end if; /* code to be restricted to INTERNAL source type only */
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012 if( lPerformPPRollup = 1 ) then
1013
1014
1015 -- Added by Renga Kannan on 04/01/02 to call the Purchase price rollup API
1016
1017 IF PG_DEBUG <> 0 THEN
1018 oe_debug_pub.add('Calling Purchase doc creation..',1);
1019 END IF;
1020
1021
1022
1023 CTO_AUTO_PROCURE_PK.Create_Purchasing_Doc(
1024 p_config_item_id => l_x_config_id,
1025 p_overwrite_list_price => 'N',
1026 p_called_in_batch => 'N',
1027 p_batch_number => l_batch_no,
1028 p_mode => 'ORDER',
1029 p_ato_line_id => p_model_line_id,
1030 x_oper_unit_list => x_oper_unit_list,
1031 x_return_status => x_Return_Status,
1032 x_msg_count => X_Msg_Count,
1033 x_msg_data => x_msg_data);
1034
1035
1036
1037
1038
1039
1040 if x_return_status = FND_API.G_RET_STS_ERROR then
1041 IF PG_DEBUG <> 0 THEN
1042 oe_debug_pub.add(' Failed in Create_purchasing_doc call...',1);
1043 END IF ;
1044
1045
1046 -- raise FND_API.G_EXC_ERROR;
1047 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1048 IF PG_DEBUG <> 0 THEN
1049 oe_debug_pub.add(' Failed in Create_purchasing_doc call...',1);
1050 END IF;
1051
1052
1053 -- raise FND_API.G_EXC_UNEXPECTED_ERROR;
1054 end if;
1055
1056
1057 else
1058
1059 IF PG_DEBUG <> 0 THEN
1060 oe_debug_pub.add('CTOMCRSB: ' || 'Will Not perform PP Rollup as profile is No ', 1);
1061 END IF;
1062
1063
1064
1065 end if; /* pp rollup based on profile */
1066
1067
1068
1069
1070 if( lPerformCSTRollup = 1 ) then
1071
1072
1073
1074
1075
1076 /* Changes for enhanced cost rollup */
1077
1078 IF PG_DEBUG <> 0 THEN
1079 oe_debug_pub.add('going to call cost rollup in CTOMCRSB for matched items.',1);
1080 END IF;
1081
1082
1083 l_status := CTO_CONFIG_COST_PK.cost_rollup_ml(
1084 pTopAtoLineId => p_model_line_id,
1085 x_msg_count => x_msg_count,
1086 x_msg_data => x_msg_data);
1087
1088 if (l_status = 0) then
1089 IF PG_DEBUG <> 0 THEN
1090 oe_debug_pub.add('Failure in cost_rollup ', 1);
1091 END IF;
1092
1093 cto_msg_pub.cto_message('BOM', x_msg_data);
1094 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1095 else
1096 IF PG_DEBUG <> 0 THEN
1097 oe_debug_pub.add('Success in cost_rollup ', 1);
1098 END IF;
1099
1100 end if;
1101
1102
1103
1104 else
1105
1106 IF PG_DEBUG <> 0 THEN
1107 oe_debug_pub.add('CTOMCRSB: ' || 'Will Not perform Cost Rollup as profile is No ', 1);
1108 END IF;
1109
1110
1111
1112 end if ; /* cost rollup based on profile */
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128 end if; -- end l_x_config_id is not null
1129
1130 -- clean up oe_order_lines_all batch_id column
1131
1132 return TRUE;
1133
1134 EXCEPTION
1135
1136 when INVALID_LINE then
1137 x_message_name := 'CTO_LINE_STATUS_NOT_ELIGIBLE';
1138 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1139 to_char(l_stmt_num) || ':' ||
1140 substrb(sqlerrm,1,100);
1141 IF PG_DEBUG <> 0 THEN
1142 oe_debug_pub.add(x_error_message, 1);
1143 END IF;
1144
1145 return FALSE;
1146
1147 when BOM_NOT_DEFINED then
1148 x_message_name := 'CTO_BOM_NOT_DEFINED';
1149 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1150 to_char(l_stmt_num) || ':' ||
1151 substrb(sqlerrm,1,100);
1152 IF PG_DEBUG <> 0 THEN
1153 oe_debug_pub.add(x_error_message, 1);
1154 END IF;
1155
1156 return FALSE;
1157
1158 when INVALID_WORKFLOW_STATUS then
1159 x_message_name:= 'CTO_INVALID_WORKFLOW_STATUS';
1160 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1161 to_char(l_stmt_num) || ':' ||
1162 substrb(sqlerrm,1,100);
1163 IF PG_DEBUG <> 0 THEN
1164 oe_debug_pub.add(x_error_message, 1);
1165 END IF;
1166
1167 return FALSE;
1168
1169 when PROCESS_ERROR then
1170 x_message_name := 'CTO_MATCH_ERROR';
1171 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1172 to_char(l_stmt_num) || ':' ||
1173 substrb(sqlerrm,1,100);
1174 IF PG_DEBUG <> 0 THEN
1175 oe_debug_pub.add(x_error_message, 1);
1176 END IF;
1177
1178 return FALSE;
1179
1180 when RESERVATION_ERROR then
1181 x_message_name := 'CTO_RESERVE_ERROR';
1182 x_error_message := 'CTOMCRSB:match_inquiry: ' ||
1183 to_char(l_stmt_num) || ':' ||
1184 substrb(sqlerrm,1,100);
1185 IF PG_DEBUG <> 0 THEN
1186 oe_debug_pub.add(x_error_message, 1);
1187 END IF;
1188
1189 return FALSE;
1190
1191 WHEN FND_API.G_EXC_ERROR THEN
1192 x_error_message := 'CTOMCRSB:match_inquiry failed with expected error in stmt '
1193 ||to_char(l_stmt_num);
1194 IF PG_DEBUG <> 0 THEN
1195 oe_debug_pub.add ('match_inquiry: exp_error ' || to_char(l_stmt_num) ||sqlerrm,1);
1196 END IF;
1197
1198 return FALSE;
1199
1200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1201 x_error_message := 'CTOMCRSB:match_inquiry failed with unexpected error in stmt '
1202 ||to_char(l_stmt_num);
1203 IF PG_DEBUG <> 0 THEN
1204 oe_debug_pub.add ('match_inquiry: unexp_error ' || to_char(l_stmt_num) ||sqlerrm,1);
1205 END IF;
1206
1207 return FALSE;
1208
1209
1210 when OTHERS then
1211 x_message_name := 'CTO_MATCH_ERROR';
1212 x_error_message := 'CTOMCRSB:match_inquiry: '
1213 || to_char(l_stmt_num) || ':' ||
1214 substrb(sqlerrm,1,100);
1215 IF PG_DEBUG <> 0 THEN
1216 oe_debug_pub.add(x_error_message, 1);
1217 END IF;
1218
1219 return FALSE;
1220 END match_inquiry ;
1221
1222
1223 /*****************************************************************************
1224 Function: create_config_reservation
1225 Parameters: p_model_line_id - line id of the top model in oe_order_lines_all
1226 p_config_item_id - config id of the matching configuration
1227 from bom_ato_configurations
1228 p_quantity_to_reserve - quantity to reserve in ordered_quantity_uom
1229 x_error_message - error message if match function fails
1230 x_message_name - name of error message if match
1231 function fails
1232
1233 Description: This function is called after a match inquiry
1234 has been done and the user attempts to reserve
1235 available inventory. This is called from
1236 the Match and Reserve menu item.
1237
1238 match_and_reserve returns TRUE if the process is successful
1239 (no process errors) and a reservation is successully made.
1240
1241 match_and_reserve returns FALSE if the process fails to create
1242 the reservation.
1243 *****************************************************************************/
1244
1245
1246 function create_config_reservation(
1247 p_model_line_id IN NUMBER,
1248 p_config_item_id IN NUMBER,
1249 p_quantity_to_reserve IN NUMBER,
1250 p_reservation_uom_code IN VARCHAR2,
1251 --Bugfix 12374440
1252 p_subinventory_code IN VARCHAR2 default null,
1253 x_quantity_reserved OUT nocopy NUMBER,
1254 x_error_msg OUT nocopy VARCHAR2,
1255 x_error_msg_name OUT nocopy VARCHAR2
1256 )
1257 return boolean
1258
1259 IS
1260
1261 l_stmt_num NUMBER := 0;
1262 l_rec_reserve CTO_RESERVE_CONFIG.rec_reserve;
1263 l_x_reserved_qty NUMBER := 0;
1264 l_x_reservation_id NUMBER;
1265 l_x_status VARCHAR(1);
1266 l_x_error_msg VARCHAR2(2000);
1267 l_x_error_msg_name VARCHAR2(30);
1268 l_x_error_msg_count NUMBER;
1269 l_x_table_name VARCHAR2(30);
1270 l_x_qoh NUMBER;
1271 l_x_rqoh NUMBER;
1272 l_x_qr NUMBER;
1273 l_x_qs NUMBER;
1274 l_x_att NUMBER;
1275 l_x_atr NUMBER;
1276 l_config_line_id NUMBER;
1277 l_config_id NUMBER;
1278 l_workflow_itemkey VARCHAR2(30);
1279 l_activity_result VARCHAR2(30);
1280 l_active_activity VARCHAR2(30);
1281 l_status NUMBER;
1282 lSourceCode varchar2(30);
1283
1284 -- 2620282 : New variable to store bom revision date
1285 l_rev_date date;
1286
1287 --Bugfix 12374440: New variable
1288 l_cnt number;
1289
1290 /* Handled Exceptions */
1291 PARAMETER_ERROR EXCEPTION;
1292 RESERVATION_ERROR EXCEPTION;
1293 PROCESS_ERROR EXCEPTION;
1294 INVALID_WORKFLOW_STATUS EXCEPTION;
1295
1296 BEGIN
1297 l_stmt_num := 100;
1298 IF (p_config_item_id is NULL or
1299 p_quantity_to_reserve is NULL or
1300 p_model_line_id is NULL)
1301 THEN
1302 raise PARAMETER_ERROR;
1303 END IF;
1304
1305 /*---------------------------------------------------+
1306 Link happens as part of the Match Inquiry. Verify
1307 that the configuration item has been linked.
1308 +---------------------------------------------------*/
1309 l_stmt_num := 110;
1310 IF (config_line_exists(p_model_line_id,
1311 l_config_line_id,
1312 l_config_id) = FALSE)
1313 THEN
1314 /*----------------------------------------------+
1315 Config line does not exist. Raise error.
1316 +-----------------------------------------------*/
1317 IF PG_DEBUG <> 0 THEN
1318 oe_debug_pub.add('create_config_reservation: ' || 'Config line does not exist. ', 1);
1319 END IF;
1320 l_stmt_num := 115;
1321 raise PROCESS_ERROR;
1322
1323 END IF;
1324
1325 /* 2620282 : Selecting bom revision date to pass it in the
1326 call to BOM_REVISIONS.get_item_revision_fn while getting config line
1327 information to perform reservation */
1328
1329 /* 4162494 : Join with wip_parameters assumes mfg org is the distribution org
1330 which is incorrect. */
1331
1332 l_stmt_num := 139;
1333 select trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
1334 'MI')+1/(60*24)
1335 into l_rev_date
1336 from bom_calendar_dates cal,
1337 mtl_parameters mp,
1338 -- 4162494 wip_parameters wp,
1339 mtl_system_items msi,
1340 oe_order_lines_all oel
1341 where oel.line_id = l_config_line_id
1342 and mp.organization_id = oel.ship_from_org_id
1343 -- 4162494 and wp.organization_id = mp.organization_id
1344 and msi.organization_id = oel.ship_from_org_id
1345 and msi.inventory_item_id = oel.inventory_item_id
1346 and cal.calendar_code = mp.calendar_code
1347 and cal.exception_set_id = mp.calendar_exception_set_id
1348 and cal.seq_num =
1349 (select greatest(1, (cal2.prior_seq_num -
1350 (ceil(nvl(msi.fixed_lead_time,0) +
1351 nvl(msi.variable_lead_time,0) *
1352 p_quantity_to_reserve
1353 ))))
1354 from bom_calendar_dates cal2
1355 where cal2.calendar_code = mp.calendar_code
1356 and cal2.exception_set_id =
1357 mp.calendar_exception_set_id
1358 and cal2.calendar_date =
1359 trunc(oel.schedule_ship_date)
1360 );
1361
1362 /*-----------------------------------------------------------------+
1363 Get necessary information from order line to perform reservation.
1364 The reservation against the configuration item is made against the
1365 configuration line, not the model line.
1366 +------------------------------------------------------------------*/
1367 l_stmt_num := 140;
1368 lSourceCode := fnd_profile.value('ONT_SOURCE_CODE');
1369 IF PG_DEBUG <> 0 THEN
1370 oe_debug_pub.add('create_config_reservation: ' || 'lSourceCode is '||lSourceCode, 2);
1371 END IF;
1372 select mso.sales_order_id,
1373 oel.line_id, -- config line id
1374 oel.ship_from_org_id,
1375 oel.inventory_item_id,
1376 oel.order_quantity_uom,
1377 p_quantity_to_reserve,
1378 inv_reservation_global.g_source_type_inv,
1379 NULL,
1380 oel.schedule_ship_date,
1381 oeh.source_document_type_id, -- bugfix 1799874: to check if it is an internal SO or regular
1382 -- 2776026: Pass revision only if item is revision contol.
1383 -- 2620282: Selecting bom revision information
1384 decode( nvl(msi.revision_qty_control_code, 1), 1, NULL ,
1385 BOM_REVISIONS.get_item_revision_fn (
1386 'ALL',
1387 'ALL',
1388 oel.ship_from_org_id,
1389 oel.inventory_item_id,
1390 l_rev_date
1391 )),
1392 --Bugfix 12374440
1393 p_subinventory_code
1394 into l_rec_reserve
1395 from oe_order_lines_all oel,
1396 oe_order_headers_all oeh,
1397 --oe_order_types_v oet,
1398 oe_transaction_types_tl oet,
1399 mtl_sales_orders mso,
1400 mtl_system_items msi
1401 where oel.line_id = l_config_line_id
1402 and oel.open_flag = 'Y'
1403 and item_type_code = 'CONFIG'
1404 and oeh.header_id = oel.header_id
1405 and oet.transaction_type_id = oeh.order_type_id
1406 and mso.segment1 = to_char(oeh.order_number)
1407 and mso.segment2 = oet.name
1408 and oet.language = (select language_code
1409 from fnd_languages
1410 where installed_flag = 'B')
1411 and mso.segment3 = lSourceCode
1412 -- and mso.segment3 = 'ORDER ENTRY'
1413 and oel.inventory_item_id = p_config_item_id
1414 and msi.inventory_item_id = oel.inventory_item_id
1415 and msi.organization_id = oel.ship_from_org_id
1416 and msi.base_item_id is not NULL;
1417
1418 --Bugfix 12374440
1419 l_cnt := sql%rowcount;
1420 IF PG_DEBUG <> 0 THEN
1421 oe_debug_pub.add('create_config_reservation:' || 'Count of records::' || l_cnt);
1422 oe_debug_pub.add('create_config_reservation:' || 'Subinventory::' || l_rec_reserve.f_subinventory_code);
1423 END IF;
1424
1425 --Bugfix 12374440
1426 --if (SQL%ROWCOUNT = 1) then
1427 if (l_cnt = 1) then
1428 l_stmt_num := 150;
1429 CTO_RESERVE_CONFIG.reserve_config(l_rec_reserve,
1430 l_x_reserved_qty,
1431 l_x_reservation_id,
1432 l_x_status,
1433 l_x_error_msg,
1434 l_x_error_msg_name);
1435 else
1436 raise PROCESS_ERROR;
1437 end if;
1438
1439 if (l_x_status = FND_API.g_ret_sts_success) then
1440 l_stmt_num := 160;
1441 IF PG_DEBUG <> 0 THEN
1442 oe_debug_pub.add
1443 ('create_config_reservation: ' || 'Success in reserve_config with reservation id:' ||
1444 to_char(l_x_reservation_id),1);
1445 END IF;
1446 else
1447 IF PG_DEBUG <> 0 THEN
1448 oe_debug_pub.add('create_config_reservation: ' || 'Failed in reserve_config.',1);
1449 END IF;
1450 raise PROCESS_ERROR;
1451
1452 end if;
1453
1454 x_error_msg_name := 'CTO_MR_SUCCESS';
1455 return TRUE;
1456
1457 EXCEPTION
1458 when PROCESS_ERROR then
1459 /* BUG#2367720 */
1460 if( l_x_error_msg_name is null ) then
1461 x_error_msg_name := 'CTO_RESERVE_ERROR';
1462
1463 else
1464
1465 x_error_msg_name := l_x_error_msg_name ;
1466
1467 end if ;
1468
1469 x_error_msg := 'CTOMCRSB:create_config_reservation: ' ||
1470 l_x_status || ': ' ||
1471 l_x_error_msg;
1472 IF PG_DEBUG <> 0 THEN
1473 oe_debug_pub.add('create_config_reservation: ' || x_error_msg, 1);
1474 END IF;
1475 return FALSE;
1476
1477 when OTHERS then
1478 x_error_msg_name := 'CTO_RESERVE_ERROR';
1479 x_error_msg := 'CTOMCRSB:create_config_reservation: ' ||
1480 to_char(l_stmt_num) || ':' ||
1481 substrb(sqlerrm,1,100);
1482 IF PG_DEBUG <> 0 THEN
1483 oe_debug_pub.add('create_config_reservation: ' || x_error_msg, 1);
1484 END IF;
1485 return FALSE;
1486
1487 END create_config_reservation;
1488
1489
1490 function config_line_exists(p_model_line_id IN NUMBER,
1491 x_config_line_id OUT nocopy NUMBER,
1492 x_config_item_id OUT nocopy NUMBER)
1493 return boolean
1494
1495 is
1496
1497 begin
1498 /***************************************************************
1499 If config line already exists, do not match. If a config
1500 line already exists, verify that the status of the configuration
1501 line allows a Match and Reserve to be performed.
1502 ***************************************************************/
1503 select oel.line_id, oel.inventory_item_id
1504 into x_config_line_id, x_config_item_id
1505 from oe_order_lines_all oel,
1506 mtl_system_items msi
1507 where oel.link_to_line_id = p_model_line_id
1508 and oel.item_type_code = 'CONFIG'
1509 and oel.inventory_item_id = msi.inventory_item_id
1510 and oel.ship_from_org_id = msi.organization_id
1511 and msi.base_item_id is not null
1512 and msi.bom_item_type = 4; --standard item
1513
1514 return TRUE;
1515
1516 exception
1517
1518 when NO_DATA_FOUND then
1519 return FALSE;
1520
1521 when OTHERS then
1522 return FALSE;
1523
1524 end;
1525
1526 end CTO_MATCH_AND_RESERVE;
[Home] [Help]