[Home] [Help]
PACKAGE BODY: APPS.CZ_BOM_CONFIG_EXPLOSIONS_PKG
Source
1 PACKAGE BODY CZ_BOM_CONFIG_EXPLOSIONS_PKG as
2 /* $Header: BOMCZCBB.pls 120.3.12000000.3 2007/07/16 16:59:51 abhissri ship $ */
3
4
5 /*
6 +=======================================================================+
7 | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA |
8 | All rights reserved. |
9 +=======================================================================+
10 | NAME
11 | BOMCZCBB.pls
12 |
13 | DESCRIPTION : CZ_BOM_CONFIG_EXPLOSIONS_PKG, will insert mandatory
14 | and optional components of a selected configuration
15 | into BOM Tables.
16 | PARAMETERS
17 |
18 | NOTES
19 |
20 | MODIFIED (MM/DD/YY)
21 |
22 | 09/03/99 Rahul Chitko Initial Creation
23 | 12/22/99 Rahul Chitko The BOM_INS_MODEL_Mandatory procedure is
24 | changed to insert Mandatory items and
25 | also the option items chosen for a
26 | configuration. This procedure was
27 | modified to also inherit the op-seq's
28 | based on a profile option.
29 |
30 | Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
31 | Added a new functionality for preconfi
32 gure bom.
33 | Modified on 15-OCT-2001 by Sushant Sawant: BUG#2048023
34 | Fixed matched_item_id variable in
35 | perform_match procedure.
36 |
37 | Modified on 18-OCT-2001 by Sushant Sawant: BUG#2048023
38 | Fixed perform_match logic
39 | loop is continued for all models
40 |
41 |
42 | Modified on 19-NOV-2001 By Renga Kannan : Modified the sequence
43 | add a new sequence called
44 | BOM_CTO_ORDER_LINES_S1
45 | this sequence generates -ve nos
46 | This is because now, BCOL table
47 | is used for pre-config bom functionality
48 | We don't want any +ve seq, so that it won't
49 | converge with line_id field. There is a dependecy
50 | with bmobmsc.odf version 115.37
51 |
52 | Modified on 10-APR-2002 By Sushant Sawant: Fixed BUG 2310435 and BUG 2312199
53 | BUG 2310435 CUSTOMER BUG is similar to 2312199 INTERNAL BUG
54 | The process_configurator_data code was dependent on the
55 | parent_config_item_id being populated in the
56 | cz_config_details_v table. This dependency has been
57 | removed and the code has been changed to properly
58 | identify the top model and the relationships to the
59 | appropriate components.This bug was identified as a
60 | common bom issue at agilent, however it is generic
61 | in nature as CZ has started populating this field
62 | randomly.
63 |
64 | Modified on 23-APR-2002 By Sushant Sawant:
65 | schedule_Ship_date populated as sysdate
66 | instead of trunc(sysdate). This was causing
67 | issues related to bom and routings if they
68 | were created on the same day.
69 |
70 | Modified on 15-MAY-2002 By Sushant Sawant:
71 | Fixed bug 2372939
72 | Error Message not propagated properly
73 | from other cto routines to front end.
74 |
75 |
76 | Modified on 17-JUL-2002 By Kiran Konada
77 | Fixed bug 2457660
78 | changed the cursor C1 to pick quantity from CZ table rather than BIC
79 | changed the debug message to print p_cz_config_hdr_id
80 |
81 | Modified on 09-SEP-2002 By Kundan Sarkar:
82 | Fixed bug 2550121 ( Customer bug 2394597 )
83 | Preconfiguration fails when pre-config item is created
84 | without any catalog group id but its base model has an
85 | item catalog attached to it.
86 |
87
88 |
89 | Modified on 21-APR-2004 By Sushant Sawant
90 | Front Port for bug 3262450
91 | Instead of hard coding UOM , we need to get base model's
92 | UOM for pre-config item.
93 |
94 |
95 |
96 |
97 | Modified on 21-APR-2004 By Sushant Sawant
98 | Fixed bug 3285539. The Front Port bug 3262450 has been revisited.
99 |
100 |************************************************************************
101 |Following changes were pulled in while overloading old BOM_INS_MODEL_AND_MANDAT
102 ORY for backward compatibility.
103 |
104 |
105 | 03/12/02 Refai Farook Changes to the operation sequence number
106 inheritance.
107 | Inheritance should occur from the near p
108 arent which has valid op.seq
109 | 03/26/02 Refai Farook Operation sequence number inheritance lo
110 gic has been changed
111 | 03/27/02 Refai Farook Club component quantitites will be using
112 rowid to identify the unique
113 | row from bom_explosion_temp
114 |
115 |************************************************************************
116 |
117 |
118 |
119 | Modified on 26-DEC-2002 by Sushant Sawant: BUG #2726217
120 | Replicated Overloading Changes to main
121 |
122 | Modified on 28-JAN-2003 by Sushant Sawant: BUG #2756186
123 | Added additional out parameter
124 | x_routing_exists to create_preconfig_item_ml
125 | to indicate whether routing already
126 | exists for the preconfigured item.
127 |
128 | Modified on 14-MAR-2003 By Sushant Sawant
129 | Decimal-Qty Support for Option Items.
130
131 +=======================================================================
132 */
133
134
135 TYPE bcol_tbl_type is table of bom_cto_order_lines%rowtype INDEX by BINARY_INTEGER ;
136
137 PROCEDURE INSERT_INTO_BCOL (
138 p_bcol_tab bcol_tbl_type
139 );
140
141 procedure populate_link_to_line_id(
142 p_bcol_tab in out NOCOPY bcol_tbl_type
143 ) ;
144
145 PROCEDURE populate_parent_ato
146 ( p_t_bcol in out NOCOPY bcol_tbl_type,
147 p_bcol_line_id in bom_cto_order_lines.line_id%type );
148
149 PROCEDURE populate_plan_level
150 ( p_t_bcol in out NOCOPY bcol_tbl_type );
151
152 procedure contiguous_to_sparse_bcol(
153 p_t_bcol in out NOCOPY bcol_tbl_type
154 );
155
156 procedure sparse_to_contiguous_bcol(
157 p_t_bcol in out NOCOPY bcol_tbl_type
158 );
159
160 procedure process_configurator_data ( p_group_id IN NUMBER,
161 p_bill_sequence_id IN NUMBER,
162 p_top_bill_sequence_id IN NUMBER,
163 p_top_predefined_item_id IN NUMBER,
164 p_validation_org_id IN NUMBER,
165 p_current_org_id IN NUMBER,
166 p_cz_config_hdr_id IN NUMBER,
167 p_cz_config_rev_num IN NUMBER,
168 x_top_ato_line_id OUT NOCOPY NUMBER,
169 x_top_matched_item_id OUT NOCOPY NUMBER,
170 x_match_profile_on OUT NOCOPY VARCHAR2,
171 x_match_found OUT NOCOPY VARCHAR2,
172 x_message IN OUT NOCOPY VARCHAR2
173 );
174
175 /*
176 procedure create_preconfig_item_ml(
177 p_use_matched_item in varchar2,
178 p_match_profile_on in varchar2,
179 p_top_predefined_item_id in number,
180 p_top_ato_line_id in bom_cto_order_lines.ato_line_id%type,
181 x_bill_sequence_id out number,
182 x_mlmo_item_created out varchar2
183 ) ;
184
185 */
186
187 procedure perform_match(
188 p_ato_line_id in bom_cto_order_lines.ato_line_id%type ,
189 x_match_found out NOCOPY varchar2,
190 x_matching_config_id out NOCOPY number,
191 x_error_message out NOCOPY VARCHAR2,
192 x_message_name out NOCOPY varchar2
193 );
194
195
196
197
198 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
199 x_bill_sequence_id NUMBER := NULL,
200 X_Top_Bill_Sequence_Id NUMBER,
201 X_Organization_Id NUMBER,
202 X_Group_Id NUMBER,
203 X_Effectivity_Date DATE,
204 X_Sort_Order VARCHAR2,
205 X_Select_Flag VARCHAR2,
206 X_Select_Quantity NUMBER,
207 X_Session_Id NUMBER,
208 X_Context VARCHAR2,
209 X_Attribute1 VARCHAR2,
210 X_Attribute2 VARCHAR2,
211 X_Attribute3 VARCHAR2,
212 X_Attribute4 VARCHAR2,
213 X_Attribute5 VARCHAR2,
214 X_Attribute6 VARCHAR2,
215 X_Attribute7 VARCHAR2,
216 X_Attribute8 VARCHAR2,
217 X_Attribute9 VARCHAR2,
218 X_Attribute10 VARCHAR2,
219 X_Attribute11 VARCHAR2,
220 X_Attribute12 VARCHAR2,
221 X_Attribute13 VARCHAR2,
222 X_Attribute14 VARCHAR2,
223 X_Attribute15 VARCHAR2
224 ) IS
225 CURSOR C IS SELECT rowid FROM BOM_CONFIG_EXPLOSIONS
226 WHERE group_id = X_Group_Id
227 AND sort_order = X_Sort_Order;
228
229 BEGIN
230
231 --
232 -- The cursor cfgdetv executing in CZLDCFGR.pld will pass the top_sequence and sort_order
233 -- values to this procedure and only the Option Classes and the option selected from the
234 -- option classes will be inserted into BOM_INVENTORY_COMPONENTS when this statement
235 -- executes.
236 --
237 INSERT INTO BOM_INVENTORY_COMPONENTS
238 (
239 bill_sequence_id,
240 component_sequence_id,
241 component_item_id,
242 creation_date,
243 created_by,
244 last_update_date,
245 last_updated_by,
246 attribute1,
247 attribute2,
248 attribute3,
249 attribute4,
250 attribute5,
251 attribute6,
252 attribute7,
253 attribute8,
254 attribute9,
255 attribute10,
256 attribute11,
257 attribute12,
258 attribute13,
259 attribute14,
260 attribute15,
261 component_quantity,
262 component_yield_factor,
263 planning_factor,
264 quantity_related,
265 include_in_cost_rollup,
266 so_basis,
267 optional,
268 mutually_exclusive_options,
269 check_atp,
270 shipping_allowed,
271 required_to_ship,
272 required_for_revenue,
273 include_on_ship_docs,
274 include_on_bill_docs,
275 low_quantity,
276 high_quantity,
277 pick_components,
278 bom_item_type,
279 operation_seq_num,
280 item_num,
281 effectivity_date,
282 disable_date,
283 implementation_date,
284 wip_supply_type
285 )
286 SELECT
287 x_bill_sequence_id,
288 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
289 be.component_item_id,
290 be.creation_date,
291 be.created_by,
292 be.last_update_date,
293 be.last_updated_by,
294 be.attribute1,
295 be.attribute2,
296 be.attribute3,
297 be.attribute4,
298 be.attribute5,
299 be.attribute6,
300 be.attribute7,
301 be.attribute8,
302 be.attribute9,
303 be.attribute10,
304 be.attribute11,
305 be.attribute12,
306 be.attribute13,
307 be.attribute14,
308 be.attribute15,
309 round( be.component_quantity,7 ), /* Support Decimal Qty for Option Items */
310 bic.component_yield_factor,
311 bic.planning_factor,
312 bic.quantity_related,
313 bic.include_in_cost_rollup,
314 be.so_basis,
315 be.optional,
316 be.mutually_exclusive_options,
317 be.check_atp,
318 be.shipping_allowed,
319 be.required_to_ship,
320 be.required_for_revenue,
321 be.include_on_ship_docs,
322 be.include_on_bill_docs,
323 be.low_quantity,
324 be.high_quantity,
325 be.pick_components,
326 be.bom_item_type,
327 be.operation_seq_num,
328 be.item_num,
329 be.effectivity_date,
330 be.disable_date,
331 be.implementation_date,
332 bic.wip_supply_type
333 FROM BOM_EXPLOSIONS be,
334 bom_inventory_components bic
335 WHERE be.TOP_BILL_SEQUENCE_ID = X_Top_Bill_Sequence_Id
336 AND be.ORGANIZATION_ID = X_Organization_Id
337 AND be.EXPLOSION_TYPE = 'OPTIONAL'
338 AND be.SORT_ORDER = X_Sort_Order
339 AND be.EFFECTIVITY_DATE <= X_Effectivity_Date
340 AND be.DISABLE_DATE > X_Effectivity_Date
341 AND bic.component_sequence_id = be.component_sequence_id;
342
343 /*
344 OPEN C;
345 FETCH C INTO X_Rowid;
346 if (C%NOTFOUND) then
347 CLOSE C;
348 Raise NO_DATA_FOUND;
349 end if;
350 CLOSE C;
351 */
352
353 IF SQL%ROWCOUNT = 0
354 THEN
355 RAISE NO_DATA_FOUND;
356 END IF;
357
358 END Insert_Row;
359
360
361 /****************************************************************************************
362 * Procedure : Transfer_Comps
363 * Parameters : Bill Sequence ID
364 * Purpose : Will transfer components for a chosen configuration from the temp table to
365 * production table and will also clean up the temporary table.
366 *
367 *****************************************************************************************/
368 Procedure Transfer_Comps
369 ( p_bill_sequence_id IN NUMBER)
370 IS
371 BEGIN
372
373 -- dbms_output.put_line('Within Transfer of Components . . . ');
374
375 INSERT INTO BOM_INVENTORY_COMPONENTS
376 (
377 bill_sequence_id,
378 component_sequence_id,
379 component_item_id,
380 creation_date,
381 created_by,
382 last_update_date,
383 last_updated_by,
384 attribute1,
385 attribute2,
386 attribute3,
387 attribute4,
388 attribute5,
389 attribute6,
390 attribute7,
391 attribute8,
392 attribute9,
393 attribute10,
394 attribute11,
395 attribute12,
396 attribute13,
397 attribute14,
398 attribute15,
399 component_quantity,
400 component_yield_factor,
401 planning_factor,
402 quantity_related,
403 include_in_cost_rollup,
404 so_basis,
405 optional,
406 mutually_exclusive_options,
407 check_atp,
411 include_on_ship_docs,
408 shipping_allowed,
409 required_to_ship,
410 required_for_revenue,
412 include_on_bill_docs,
413 low_quantity,
414 high_quantity,
415 pick_components,
416 bom_item_type,
417 operation_seq_num,
418 item_num,
419 effectivity_date,
420 disable_date,
421 implementation_date,
422 wip_supply_type,
423 From_End_Item_Unit_Number,
424 To_End_Item_Unit_Number
425 )
426 SELECT
427 bill_sequence_id,
428 BOM_INVENTORY_COMPONENTS_S.nextval,
429 be.Component_Item_Id,
430 SYSDATE,
431 1,
432 SYSDATE,
433 1,
434 be.Attribute1,
435 be.Attribute2,
436 be.Attribute3,
437 be.Attribute4,
438 be.Attribute5,
439 be.Attribute6,
440 be.Attribute7,
441 be.Attribute8,
442 be.Attribute9,
443 be.Attribute10,
444 be.Attribute11,
445 be.Attribute12,
446 be.Attribute13,
447 be.Attribute14,
448 be.Attribute15,
449 round( be.Component_Quantity, 7 ), /* Support Decimal-Qty for Option Items */
450 1, /* Component Yield*/
451 be.planning_factor, /*Component Planning factor */
452 NVL(to_number(be.so_transactions_flag),2), /* used for Quantity Related */
453 be.include_in_rollup_flag, /* Include in Cost Rollup */
454 be.so_basis, /* SO Basis */
455 be.optional, /* Optional */
456 be.mutually_exclusive_options, /*Mutually_Exclusive_Options */
457 be.check_atp, /*Check_ATP*/
458 2, /*Shipping Allowed */
459 2, /*Required to ship */
460 2, /*Required_For_Revenue*/
461 2, /*Include on Ship Docs */
462 2, /*Include_On_Bill_Docs */
463 be.Low_Quantity,
464 be.High_Quantity,
465 DECODE(be.pick_components_flag, 'Y', 1, 2), /* Pick_Components */
466 be.Bom_Item_Type,
467 be.operation_seq_num, /*Operation Sequence Num */
468 be.item_num, /*Item_Num */
469 SYSDATE,
470 be.disable_date, /*Disable_Date*/
471 SYSDATE, /* Implementation Date */
472 be.wip_supply_type, /* wip_supply_type */
473 substr(be.pricing_attribute1,1,30), /* Used for From_Unit_Number */
474 substr(be.pricing_attribute2,1,30) /* Used for To_Unit_Number */
475 FROM bom_explosion_temp be
476 WHERE be.bill_sequence_id = p_Bill_Sequence_id;
477
478 -- dbms_output.put_line('Transfer Complete . . .No. Of Comps Transfered: ' || sql%rowcount);
479
480 EXCEPTION
481 WHEN OTHERS THEN
482 -- dbms_output.put_line('Exception when transferring comps ' ||
483 -- SQLERRM);
484 null;
485 END Transfer_Comps;
486
487 /*****************************************************************************************
488 * Procedure : Club_Component_Quantities (Local)
489 * Parameters: Bill Sequence ID
490 * Purpose : Will go through the components and consolidate components with the same
491 * component item ID and operation sequence. The component quantities for all
492 * such components will be added and only one record will be kept and the rest
493 * will be deleted.
494 ****************************************************************************************/
495 Procedure Club_Component_Quantities
496 ( p_bill_sequence_id NUMBER )
497 IS
498 CURSOR c_Club_Comps IS
499 SELECT bet.bill_sequence_id
500 , bet.component_item_id
501 , bet.operation_seq_num
502 , bet.component_sequence_id
503 , round( bet.component_quantity, 7 ) component_quantity /* Support Decimal-Qty for Option Items */
504 , rowid /* Sushant added on 19-Aug-2002 */
505 FROM bom_explosion_temp bet
506 WHERE bill_sequence_id = p_bill_sequence_id
507 ORDER BY bet.bill_sequence_id,
508 bet.component_item_id,
509 bet.operation_seq_num;
510
511 l_component_item_id NUMBER := NULL;
512 l_operation_seq_num NUMBER := NULL;
513 l_component_seq_id NUMBER := NULL;
514 l_quantity NUMBER := NULL;
515 l_rowid VARCHAR2(50); /* Added by Sushant on 19-Aug-2002*/
516
517 cursor bill_list is
518 select component_item_id, operation_seq_num, component_sequence_id
519 from bom_explosion_temp
520 where bill_sequence_id = p_bill_sequence_id
521 order by component_item_id;
522 BEGIN
523
524 FOR c_components IN c_Club_Comps
525 LOOP
526 IF l_component_item_id = c_components.component_item_id AND
527 l_operation_seq_num = c_components.operation_seq_num
528 THEN
529 l_quantity := l_quantity + c_components.component_quantity;
530 -- dbms_output.put_line('Found ' || l_component_item_id);
531
532 -- And then delete the component
533 DELETE FROM Bom_Explosion_Temp
534 WHERE rowid = c_components.rowid; /* changed by Sushant on 19-AUG-2002 */
535 /*
536 WHERE component_sequence_id = c_components.component_sequence_id
537 AND bill_sequence_id = p_bill_sequence_id;
538 */
539
540 -- dbms_output.put_line('deleted ' || c_components.component_sequence_id);
541
542 END IF;
543
544 IF l_component_item_id <> c_components.component_item_id OR
545 l_operation_seq_num <> c_components.operation_seq_num
546 THEN
547
551 WHERE rowid = l_rowid; /* Changed by Sushant on 19-Aug-2002 */
548 -- Update the Component and then reset the local variables
549 UPDATE BOM_EXPLOSION_TEMP
550 SET component_quantity = l_quantity
552 /*
553 WHERE component_sequence_id = l_component_seq_id;
554 */
555
556 l_component_item_id := c_components.component_item_id;
557 l_operation_seq_num := c_components.operation_seq_num;
558 l_component_seq_id := c_components.component_sequence_id;
559 -- dbms_output.put_line('Comp Seq: ' || l_component_seq_id);
560
561 l_quantity := c_components.component_quantity;
562 l_rowid := c_components.rowid; /* Added by Sushant on 19-Aug-2002 */
563
564 END IF;
565
566 IF l_component_item_id IS NULL AND
567 l_operation_seq_num IS NULL
568 THEN
569 -- dbms_output.put_line('null so assigning ');
570
571 l_component_item_id := c_components.component_item_id;
572 l_operation_seq_num := c_components.operation_seq_num;
573 l_quantity := c_components.component_quantity;
574 l_component_seq_id := c_components.component_sequence_id;
575 -- dbms_output.put_line('l_comp_id assigned: ' || l_component_item_id);
576 l_rowid := c_components.rowid;/* Added by Sushant on 19-Aug-2002 */
577
578
579 END IF;
580
581
582
583 END LOOP; /* Changed by Sushant on 20-Aug-2002 */
584
585 /* Update the last component which will be left out in the loop */
586 /* Added by Sushant on 19-Aug-2002 */
587 UPDATE BOM_EXPLOSION_TEMP
588 SET component_quantity = l_quantity
589 WHERE rowid = l_rowid;
590
591
592 END Club_Component_Quantities;
593
594 /************************************************************************************
595 * Procedure : Set_Op_Seq (Local)
596 * Parameters : Organization_Id
597 * Component_item_id
598 * Operation Sequence Number
599 * Purpose : Recursively traverse down the tree of option classes and set the op-seq
600 * for any components from the option class that are chosen for a configuration
601 *************************************************************************************/
602 PROCEDURE Set_Op_Seq
603 ( p_organization_id IN NUMBER
604 , p_component_item_id IN NUMBER
605 , p_operation_seq_num IN NUMBER
606 )
607 IS
608 CURSOR c_oc_comps IS
609 SELECT bic.bill_sequence_id
610 , bic.component_sequence_id
611 , bic.operation_seq_num
612 , bic.component_item_id
613 FROM bom_inventory_components bic,
614 bom_bill_of_materials bom
615 WHERE -- bic.operation_seq_num = 1 AND /* Changed by Sushant on 19-Aug-2002*/
616 bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, NULL,
617 bom.bill_sequence_id, bom.common_bill_sequence_id)
618 AND bom.assembly_item_id = p_component_item_id
619 AND bom.organization_id = p_organization_id
620 AND bom.alternate_bom_designator IS NULL;
621
622 l_operation_seq_num NUMBER; /* Added by Sushant on 19-Aug-2002 */
623
624 BEGIN
625
626 FOR c_comps_of_options IN c_oc_comps
627 LOOP
628
629 -- When it is identified that this component has a bill
630 -- the process must look at its children and assign the right
631 -- operation sequences
632
633
634 -- dbms_output.put_line('Checking if any components of ' || c_comps_of_options.component_item_id || ' are bill themselves . . . ');
635
636 IF c_comps_of_options.operation_seq_num <> 1 THEN
637 l_operation_seq_num := c_comps_of_options.operation_seq_num;
638 ELSE
639 l_operation_seq_num := p_operation_seq_num;
640 END IF;
641
642
643 Set_Op_Seq
644 ( p_organization_id => p_organization_id
645 , p_component_item_id => c_comps_of_options.component_item_id
646 , p_operation_seq_num => p_operation_seq_num
647 );
648
649 IF c_comps_of_options.operation_seq_num = 1 THEN /* Added by sushant on 19-Aug-2002 */
650 BEGIN
651 UPDATE bom_explosion_temp
652 SET operation_seq_num = l_operation_seq_num /* Changed by Sushant on 19-Aug-2002 */
653 WHERE component_sequence_id =
654 c_comps_of_options.component_sequence_id;
655
656
657 --dbms_output.put_line('Updated ' || SQL%ROWCOUNT || ' rows for seq:' || c_comps_of_options.component_sequence_id);
658 EXCEPTION
659 WHEN NO_DATA_FOUND THEN
660 null;
661 -- This exception only means that a component under this
662 -- OC was not selected for the configured bill
663 END;
664 END IF; /* Added by Sushant on 19-Aug-2002 */
665 END LOOP;
666
667 END Set_Op_Seq;
668
669
670 /*******************************************************************************************
671 ** Procedure : BOM_INS_MODEL_AND_MANDATORY
675 ** Cz_Config_Rev_Num
672 ** Parameters : Group_Id
673 ** Bill_Sequence_Id
674 ** Cz_Config_Hdr_Id
676 ** Purpose : This procedure will be called when the configurator Applet Returns after the
677 ** user has Chosen a Configuration and hit Done. This procedure take the options
678 ** the user has chosen and the option classes that those options belong to and insert
679 ** them in a temporary table. Then it will take all the mandatory components that
680 ** are associated with the option classes from which a user has chosen atleast 1
681 ** Option and insert the data in a temporary table.
682 ** Once the required data is gathered under one group id, the process will check if
683 ** the Profile "BOM:CONFIG_INHERIT_OP_SEQ" is set.If YES then the procedure will
684 ** loop through the option classes and assign the operation sequence to its children
685 ** if the children have an op_seq of 1. This process will recursively loop through
686 ** its children and perform the operation sequence inheritance for all the children.
687 ** Once the records have been assigned the proper op-seq's the process will then
688 ** proceed to consolidate the components. Components quantities for components with
689 ** the same op-seq and component_item_id will be added and only 1 record for that
690 ** combination will exist and the duplicates will be deleted. The final data will be
691 ** moved from the temporary table to the production table and the data in the temp
692 ** table will be cleaned up.
693 ********************************************************************************************/
694
695 PROCEDURE BOM_INS_MODEL_AND_MANDATORY(p_group_id IN NUMBER,
696 p_bill_sequence_id IN NUMBER,
697 p_top_bill_sequence_id IN NUMBER,
698 p_top_predefined_item_id IN NUMBER,
699 p_validation_org_id IN NUMBER,
700 p_current_org_id IN NUMBER,
701 p_cz_config_hdr_id IN NUMBER,
702 p_cz_config_rev_num IN NUMBER,
703 x_top_ato_line_id OUT NOCOPY NUMBER,
704 x_top_matched_item_id OUT NOCOPY NUMBER,
705 x_match_profile_on OUT NOCOPY VARCHAR2,
706 x_match_found OUT NOCOPY VARCHAR2,
707 x_message IN OUT NOCOPY VARCHAR2) IS
708
709 BEGIN
710
711 /* Temporary fix, might need assembly item id for which the bill is being
712 configured */
713
714 /* BUG #1957336 Temporary change for preconfigure bom by Sushant Sawant */
715
716 -- insert into my_debug_messages values ( ' header_id ' || to_char( p_cz_config_hdr_id ) ) ;
717 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' header_id ' || to_char( p_cz_config_hdr_id ) ) ;
718
719 CTO_UTILITY_PK.PC_BOM_BILL_SEQUENCE_ID := p_bill_sequence_id ;
720 CTO_UTILITY_PK.PC_BOM_TOP_BILL_SEQUENCE_ID := p_top_bill_sequence_id ;
721 CTO_UTILITY_PK.PC_BOM_CURRENT_ORG := p_current_org_id ;
722
723
724
725 /* BUG #1957336 Temporary change for preconfigure bom by Sushant Sawant */
726 --insert into my_debug_messages values( ' bill_sequence_id ' || to_char( p_bill_sequence_id ) || ' top_bill_sequence_id ' || to_char( p_top_bill_sequence_id ) ) ;
727
728 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' bill_sequence_id ' || to_char( p_bill_sequence_id ) || ' top_bill_sequence_id ' || to_char( p_top_bill_sequence_id ) ) ;
729
730
731 -- insert into my_debug_messages values( ' validation_org ' || to_char( p_validation_org_id) || ' current org ' || to_char( p_current_org_id ) ) ;
732 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' validation_org ' || to_char( p_validation_org_id) || ' current org ' || to_char( p_current_org_id ) ) ;
733
734
735
736 if( p_top_predefined_item_id is not null ) then
737 -- insert into my_debug_messages values( ' predefined_item_id ' || to_char( p_top_predefined_item_id ) ) ;
738 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' predefined_item_id ' || to_char( p_top_predefined_item_id ) ) ;
739
740 null ;
741 else
742 -- insert into my_debug_messages values ( ' predefined_item_id is null ' ) ;
743 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' predefined_item_id is null ' ) ;
744
745 null ;
746
747 end if ;
748
749
750
751
752
753 process_configurator_data( p_group_id ,
754 p_bill_sequence_id ,
755 p_top_bill_sequence_id ,
756 p_top_predefined_item_id ,
757 p_validation_org_id,
758 p_current_org_id,
759 p_cz_config_hdr_id,
760 p_cz_config_rev_num,
761 x_top_ato_line_id ,
762 x_top_matched_item_id ,
763 x_match_profile_on ,
764 x_match_found ,
765 x_message
766 ) ;
767
768
772 -- insert into my_debug_messages values( ' match_profile_on ' || x_match_profile_on ) ;
769 -- insert into my_debug_messages values( ' top_ato_line_id ' || to_char( x_top_ato_line_id ) ) ;
770 -- insert into my_debug_messages values( ' top_matched_item_id ' || to_char( x_top_matched_item_id ) ) ;
771
773
774
775 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' top_ato_line_id ' || to_char( x_top_ato_line_id ) ) ;
776 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' top_matched_item_id ' || to_char( x_top_matched_item_id ) ) ;
777
778 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'BOM_INS_MODEL' , ' match_profile_on ' || x_match_profile_on ) ;
779
780 return ;
781
782
783
784 END BOM_INS_MODEL_AND_MANDATORY ;
785
786
787
788
789
790
791
792 procedure process_configurator_data ( p_group_id IN NUMBER,
793 p_bill_sequence_id IN NUMBER,
794 p_top_bill_sequence_id IN NUMBER,
795 p_top_predefined_item_id IN NUMBER,
796 p_validation_org_id IN NUMBER,
797 p_current_org_id IN NUMBER,
798 p_cz_config_hdr_id IN NUMBER,
799 p_cz_config_rev_num IN NUMBER,
800 x_top_ato_line_id OUT NOCOPY NUMBER,
801 x_top_matched_item_id OUT NOCOPY NUMBER,
802 x_match_profile_on OUT NOCOPY VARCHAR2,
803 x_match_found OUT NOCOPY VARCHAR2,
804 x_message IN OUT NOCOPY VARCHAR2)
805 IS
806
807 TYPE config_data_rec_type is RECORD (
808 line_id NUMBER ,
809 config_item_id cz_config_details_v.config_item_id%type,
810 config_hdr_id cz_config_details_v.config_hdr_id%type,
811 parent_config_item_id cz_config_details_v.parent_config_item_id%type,
812 inventory_item_id cz_config_details_v.inventory_item_id%type,
813 component_code cz_config_details_v.component_code%type,
814 segment1 mtl_system_items.segment1%type,
815 component_item_id bom_inventory_components.component_item_id%type,
816 component_sequence_id bom_inventory_components.component_sequence_id%type,
817 bom_item_type bom_inventory_components.bom_item_type%type,
818 wip_supply_type bom_inventory_components.wip_supply_type%type,
819 component_quantity bom_inventory_components.component_quantity%type,
820 config_orgs mtl_system_items.config_orgs%type,
821 config_match mtl_system_items.config_match%type,
822 uom_code cz_config_details_v.uom_code%type --bugfix 4605114
823 ) ;
824
825 TYPE config_data_tbl_type is table of config_data_rec_type INDEX by BINARY_INTEGER ;
826
827
828 config_data_tab config_data_tbl_type ;
829
830 bcol_tab bcol_tbl_type ;
831
832 bcol_index NUMBER := 0 ;
833
834 gUserID number ;
835 gLoginId number ;
836
837
838 CURSOR C1 IS
839 select
840 config_item_id ,
841 parent_config_item_id,
842 cz.inventory_item_id ,
843 cz.component_code,
844 msi.segment1,
845 bic.component_item_id,
846 nvl( bic.component_sequence_id , p_top_bill_sequence_id ) ,
847 bic.bom_item_type,
848 bic.wip_supply_type,
849 --bic.component_quantity
850 cz.quantity, --bugfix 2457660
851 msi.config_orgs,
852 msi.config_match,
853 cz.uom_code --bugfix 4605114
854 from cz_config_details_v cz ,
855 bom_inventory_components bic,
856 mtl_system_items msi
857 where bic.component_sequence_id(+) = cz.component_sequence_id
858 AND msi.inventory_item_id = cz.inventory_item_id
859 AND cz.config_hdr_id = p_cz_config_hdr_id
860 AND cz.config_rev_nbr = p_cz_config_rev_num
861 AND msi.organization_id = p_validation_org_id ;
862 /* order by parent_config_item_id desc ; */
863
864
865 v_step varchar2(20) ;
866 config_index number := 0 ;
867 v_ato_line_id number ;
868 v_validation_org_id number := 204 ; /* Temporary fix for validation org */
869 lMatchProfile varchar2(10) ;
870
871 l_custom_match_profile varchar2(10);
872
873 v_perform_match varchar2(1) ;
874
875 -- x_match_found varchar2(1);
876 x_error_message varchar2(1000) ;
877 x_message_name varchar2(1000) ;
878
879 v_sqlcode number ;
880 v_sqlerrm varchar2(2000 );
881
882 v_top_model_item_id number ;
883 v_top_model_index number ;
884
885
886 v_sysdate DATE ;
887
888 v_match_flag_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE ;
889 v_sparse_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE ;
890
891 i number ;
892 x_return_status varchar2(1) ;
893 x_msg_count number;
894 x_msg_data varchar2(100);
898 v_ck_line_id number ;
895
896
897
899 v_ck_ato_line_id number ;
900 v_ck_inventory_item_id number ;
901 v_ck_config_item_id number ;
902 v_ck_perform_match varchar2(10) ;
903
904
905 v_order_quantity_uom varchar2(3) ; -- Bugfix 3262450 New variable
906
907 --bugfix 4440577
908 TYPE parent_cfg_id_rec_type is RECORD( pcfg_id number --parent config item id
909 );
910 TYPE parent_cfg_id_tbl_type IS TABLE OF parent_cfg_id_rec_type INDEX by BINARY_INTEGER ;
911
912
913 TYPE line_id_rec_type is RECORD( line_id number
914 );
915 TYPE line_id_tbl_type IS TABLE OF line_id_rec_type INDEX by BINARY_INTEGER ;
916
917 tab_pci parent_cfg_id_tbl_type; --table of CZ parent cfg id's sparse indexed by line_id
918 tab_li line_id_tbl_type; --table of line id's sparse indexed by CZ cfg id's
919 --end 4440577
920
921
922 BEGIN
923
924 v_step := 'Step 1 ' ;
925
926
927 v_sysdate := sysdate ;
928
929 -- insert into my_debug_messages values ( ' came into process_configurator ' ) ;
930 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' came into process_configurator ' ) ;
931
932 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' latest changes as of 10-APR-2002 in process_configurator ' ) ;
933
934
935
936 gUserId := nvl(Fnd_Global.USER_ID, -1);
937 gLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
938
939 v_step := 'Step 2 ' ;
940
941 lMatchProfile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
942
943 v_step := 'Step 3 ' ;
944
945 /* Temporary statement needs to be fixed */
946 CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG := p_validation_org_id ;
947
948 v_step := 'Step 5 ' ;
949
950 if( lMatchProfile = 1 ) then
951
952 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' Match is ON ' ) ;
953
954 v_perform_match := 'Y' ;
955 x_match_profile_on := 'Y' ;
956 else
957 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' Match is OFF ' ) ;
958 v_perform_match := 'N' ;
959 x_match_profile_on := 'N' ;
960 end if ;
961
962 /* Temporary Statement v_perform_match := 'Y' ;*/
963
964
965 v_step := 'Step 8 ' ;
966
967 /* BUGFIX FOR BUG#2310435 */
968 select assembly_item_id
969 into v_top_model_item_id
970 from bom_bill_of_materials
971 where bill_sequence_id = p_top_bill_sequence_id ;
972
973
974
975 v_step := 'Step 10 ' ;
976
977 open c1 ;
978
979 WHILE(TRUE)
980 LOOP
981
982 config_index := config_data_tab.count + 1 ;
983
984 fetch c1 into config_data_tab(config_index).config_item_id,
985 config_data_tab(config_index).parent_config_item_id,
986 config_data_tab(config_index).inventory_item_id,
987 config_data_tab(config_index).component_code,
988 config_data_tab(config_index).segment1,
989 config_data_tab(config_index).component_item_id,
990 config_data_tab(config_index).component_sequence_id,
991 config_data_tab(config_index).bom_item_type,
992 config_data_tab(config_index).wip_supply_type,
993 config_data_tab(config_index).component_quantity,
994 config_data_tab(config_index).config_orgs,
995 config_data_tab(config_index).config_match,
996 config_data_tab(config_index).uom_code; --4605114
997
998 exit when c1%notfound ;
999
1000
1001 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'cz_c_d '
1002 || ' hdr '|| p_cz_config_hdr_id --bugfix2457660
1003 || ' itm ' || to_char( config_data_tab(config_index).config_item_id)
1004 || ' pci ' || to_char( config_data_tab(config_index).parent_config_item_id )
1005 || ' iid ' || to_char( config_data_tab(config_index).inventory_item_id )
1006 || ' ccd ' || config_data_tab(config_index).component_code
1007 || ' csd ' || to_char( config_data_tab(config_index).component_sequence_id )
1008 || ' bit ' || to_char( config_data_tab(config_index).bom_item_type )
1009 || ' wst ' || to_char( config_data_tab(config_index).wip_supply_type )
1010 || ' qty ' || to_char( config_data_tab(config_index).component_quantity)
1011 || ' behavior ' || config_data_tab(config_index).config_orgs
1012 || ' match ' || config_data_tab(config_index).config_match
1013 || ' uom ' || config_data_tab(config_index).uom_code ) ;--bugfix 4605114
1014
1015
1016
1017
1018 END LOOP ;
1019
1020 close c1 ;
1021
1022
1023
1024 v_step := 'Step 12 ' ;
1025
1026
1027
1028
1029
1030 v_step := 'Step 13 ' ;
1031
1032 for i in 1..config_data_tab.count
1033 loop
1034 bcol_index := bcol_tab.count + 1 ;
1035
1039 -- Generate the line_id column with this new sequence
1036 --multiplied by -1 to generate -ve sequnnce 'kkonada'
1037 -- Modified by Renga Kannan on 11/19/01
1038 -- Created a new sequence for Bom_cto_order_lines table
1040 -- This seqence will be a -ve sequence by itself.
1041 -- The -ve seq is used to avoid converging with om line_id
1042 -- IN the case of Bcol generation for Sales order
1043 -- Line_id is populated with Oe_line_id
1044
1045
1046 select bom_cto_order_lines_s1.nextval
1047 into bcol_tab(bcol_index).line_id
1048 from dual ;
1049 /* Temporary Fix, Need a new sequence for bom_cto_order_lines */
1050
1051 /*
1052 bcol_tab(bcol_index).header_id := config_data_tab(1).parent_config_item_id ;
1053 */
1054
1055
1056
1057 /* BUGFIX FOR BUG#2310435 */
1058 if( config_data_tab(i).inventory_item_id = v_top_model_item_id ) then
1059 v_top_model_index := i ;
1060 end if ;
1061
1062
1063
1064 /* BUGFIX FOR BUG#2310435 */
1065 bcol_tab(bcol_index).header_id := config_data_tab(1).config_hdr_id ;
1066
1067
1068
1069 /* BUGFIX FOR BUG#2310435 */
1070
1071 /*
1072 bcol_tab(bcol_index).ato_line_id := bcol_tab(1).line_id;
1073 bcol_tab(bcol_index).top_model_line_id := bcol_tab(1).line_id ;
1074 */
1075
1076
1077 bcol_tab(bcol_index).inventory_item_id := config_data_tab(i).inventory_item_id ;
1078 bcol_tab(bcol_index).component_code := config_data_tab(i).component_code ;
1079 bcol_tab(bcol_index).component_sequence_id := config_data_tab(i).component_sequence_id ;
1080 bcol_tab(bcol_index).wip_supply_type := config_data_tab(i).wip_supply_type ;
1081
1082 /*
1083 if( bcol_tab(bcol_index).line_id = bcol_tab(bcol_index).ato_line_id ) then
1084 bcol_tab(bcol_index).ordered_quantity := 1 ;
1085 bcol_tab(bcol_index).bom_item_type := '1' ;
1086 bcol_tab(bcol_index).plan_level := 1 ;
1087 bcol_tab(bcol_index).component_code := config_data_tab(i).inventory_item_id;
1088 else
1089 bcol_tab(bcol_index).ordered_quantity := config_data_tab(i).component_quantity ;
1090 bcol_tab(bcol_index).bom_item_type := config_data_tab(i).bom_item_type ;
1091 end if;
1092
1093 */
1094
1095 bcol_tab(bcol_index).ordered_quantity := config_data_tab(i).component_quantity ;
1096 bcol_tab(bcol_index).bom_item_type := config_data_tab(i).bom_item_type ;
1097
1098
1099
1100
1101
1102
1103 bcol_tab(bcol_index).order_quantity_uom := config_data_tab(i).uom_code ; --4605114
1104
1105
1106
1107
1108 bcol_tab(bcol_index).schedule_ship_date := v_sysdate ;
1109 bcol_tab(bcol_index).ship_from_org_id := p_current_org_id ;
1110
1111 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
1112
1113
1114 if( lMatchProfile = 1 ) then
1115 if( l_custom_match_profile = 2 ) then
1116 bcol_tab(bcol_index).perform_match := nvl( config_data_tab(i).config_match , 'Y' ) ;
1117 else
1118 bcol_tab(bcol_index).perform_match := nvl( config_data_tab(i).config_match , 'C' ) ;
1119 end if;
1120 else
1121 bcol_tab(bcol_index).perform_match := 'N' ;
1122 end if;
1123
1124
1125 bcol_tab(bcol_index).config_creation := nvl( config_data_tab(i).config_orgs , 1 ) ;
1126 bcol_tab(bcol_index).option_specific := 'N' ;
1127 bcol_tab(bcol_index).reuse_config := 'N' ;
1128
1129
1130
1131
1132
1133
1134 /* audit columns */
1135 bcol_tab(bcol_index).creation_date := sysdate ;
1136 bcol_tab(bcol_index).created_by := gUserId ;
1137 bcol_tab(bcol_index).last_update_date := sysdate ;
1138 bcol_tab(bcol_index).last_updated_by := gUserId ;
1139 bcol_tab(bcol_index).program_id := CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ;
1140
1141 --bugfix 4440577
1142 --tab_pci index by line_id and store cz parent config_id
1143 tab_pci(bcol_tab(bcol_index).line_id).pcfg_id :=
1144 config_data_tab(i).parent_config_item_id;
1145 --bugfix 4440577
1146 --tab_li index cz config item id and store bcol line id
1147 tab_li(config_data_tab(i).config_item_id).line_id :=
1148 bcol_tab(bcol_index).line_id;
1149
1150
1151
1152 end loop ;
1153
1154
1155 v_step := 'Step 14 ' ;
1156 /* BUGFIX FOR BUG#2310435 */
1157
1158 for myindex in bcol_tab.first..bcol_tab.last
1159 loop
1160
1161 bcol_tab(myindex).ato_line_id := bcol_tab(v_top_model_index).line_id;
1162 bcol_tab(myindex).top_model_line_id := bcol_tab(v_top_model_index).line_id ;
1163
1164 if( bcol_tab(myindex).line_id = bcol_tab(v_top_model_index).ato_line_id ) then
1165 bcol_tab(myindex).ordered_quantity := 1 ;
1169
1166 bcol_tab(myindex).bom_item_type := '1' ;
1167 bcol_tab(myindex).plan_level := 1 ;
1168 bcol_tab(myindex).component_code := config_data_tab(v_top_model_index).inventory_item_id;
1170 end if;
1171
1172 --populate link_to_line_id
1173 --bugfix 4440577
1174 if ( bcol_tab(myindex).line_id = bcol_tab(v_top_model_index).ato_line_id ) then
1175 bcol_tab(myindex).link_to_line_id := null;
1176 else
1177 bcol_tab(myindex).link_to_line_id :=
1178 tab_li( tab_pci(bcol_tab(myindex).line_id).pcfg_id).line_id;
1179
1180
1181 end if;
1182 --end 4440577
1183
1184
1185 end loop ;
1186
1187
1188 v_step := 'Step 15 ' ;
1189 --commented call to link_2_line_id as part of fix4440577
1190 --populate_link_to_line_id( bcol_tab) ;
1191
1192
1193 v_step := 'Step 16 ' ;
1194
1195 v_ato_line_id := bcol_tab(v_top_model_index).line_id ;
1196
1197 x_top_ato_line_id := v_ato_line_id ;
1198
1199
1200 v_step := 'Step 20 ' ;
1201
1202 contiguous_to_sparse_bcol( bcol_tab ) ;
1203
1204 v_step := 'Step 25 ' ;
1205 populate_plan_level(bcol_tab ) ;
1206
1207 v_step := 'Step 30 ' ;
1208
1209 populate_parent_ato(bcol_tab , v_ato_line_id ) ;
1210
1211
1212 v_step := 'Step 31 ' ;
1213
1214
1215 /*
1216 ** CHECK FOR INVALID MODEL SETUP
1217 **
1218 */
1219
1220
1221 i := bcol_tab.first ;
1222 while i is not null
1223 loop
1224 if( bcol_tab(i).bom_item_type = 1 and nvl(bcol_tab(i).wip_supply_type, 1 ) <> 6 and bcol_tab(i).config_creation in (1, 2) ) then
1225
1226 if( bcol_tab(bcol_tab(i).parent_ato_line_id).config_creation = 3) then
1227
1228 oe_debug_pub.add('populate_bcol: ' || 'INVALID MODEL SETUP exists for line id ' || bcol_tab(i).line_id
1229 || ' model item ' || bcol_tab(i).inventory_item_id
1230 || ' item type ' || bcol_tab(i).config_creation
1231 , 1 );
1232 oe_debug_pub.add('populate_bcol: ' || ' parent line id ' || bcol_tab(bcol_tab(i).parent_ato_line_id).line_id
1233 || ' parent model item ' || bcol_tab(bcol_tab(i).parent_ato_line_id).inventory_item_id
1234 || ' parent item type ' || bcol_tab(bcol_tab(i).parent_ato_line_id).config_creation
1235 , 1 );
1236
1237
1238
1239 x_message := 'CTO_INVALID_MODEL_SETUP' ;
1240
1241 cto_msg_pub.cto_message('BOM','CTO_INVALID_MODEL_SETUP');
1242 raise FND_API.G_EXC_ERROR;
1243
1244 end if;
1245
1246 end if ;
1247
1248
1249 i := bcol_tab.next(i) ;
1250
1251 end loop ;
1252
1253
1254
1255
1256
1257 /*
1258 ** CALL TRANSFORMED MATCH ATTRIBUTES PENDING
1259 ** PENDING WORK!!!!
1260 */
1261
1262 if( lMatchProfile = 1 ) then
1263 oe_debug_pub.add('populate_bcol: ' || ' preparing information for v_match_flag_tab ' , 3 );
1264 i :=bcol_tab.first ;
1265
1266 while i is not null
1267 loop
1268
1269 if( bcol_tab(i).bom_item_type = 1 and nvl( bcol_tab(i).wip_supply_type , 1 ) <> 6 ) then
1270 v_match_flag_tab(v_match_flag_tab.count + 1).line_id := bcol_tab(i).line_id ;
1271 v_match_flag_tab(v_match_flag_tab.count ).parent_ato_line_id := bcol_tab(i).parent_ato_line_id ;
1272 v_match_flag_tab(v_match_flag_tab.count ).ato_line_id := bcol_tab(i).ato_line_id ;
1273 v_match_flag_tab(v_match_flag_tab.count ).match_flag := bcol_tab(i).perform_match ;
1274
1275 end if;
1276
1277 i := bcol_tab.next(i) ;
1278
1279 end loop ;
1280
1281
1282 oe_debug_pub.add('populate_bcol: ' || ' going to call cto_match_config.evaluate_n_pop_match_flag ' , 3 );
1283
1284 cto_match_config.evaluate_n_pop_match_flag( p_match_flag_tab => v_match_flag_tab
1285 , x_sparse_tab => v_sparse_tab
1286 , x_return_status => x_return_status
1287 , x_msg_count => x_msg_count
1288 , x_msg_data => x_msg_data );
1289
1290
1291
1292
1293
1294 oe_debug_pub.add('populate_bcol: ' || ' populating match flag from results ' , 3 );
1295
1296 i := v_sparse_tab.first ;
1297
1298 while i is not null
1299 loop
1300
1301 bcol_tab(i).perform_match := v_sparse_tab(i).match_flag ;
1302
1303 oe_debug_pub.add('populate_bcol: ' || i || ' match set to ' || bcol_tab(i).perform_match , 3 );
1304
1305
1306
1307 if( bcol_tab(i).line_id = bcol_tab(i).ato_line_id ) then
1308 oe_debug_pub.add('populate_bcol: ' || ' v_perform_match before ' || v_perform_match , 3 );
1309 v_perform_match := bcol_tab(i).perform_match ;
1313
1310 x_match_profile_on := bcol_tab(i).perform_match ;
1311
1312 oe_debug_pub.add('populate_bcol: ' || ' v_perform_match after ' || v_perform_match , 3 );
1314 end if ;
1315 i := v_sparse_tab.next(i) ;
1316
1317 end loop ;
1318
1319 oe_debug_pub.add('populate_bcol: ' || ' done populating match flag from results ' , 3 );
1320
1321
1322 else
1323
1324 oe_debug_pub.add('populate_bcol: ' || ' will not be calling cto_match_config.evaluate_n_pop_match_flag ' , 3 );
1325
1326 end if ;
1327
1328
1329
1330
1331
1332 v_step := 'Step 40 ' ;
1333
1334 insert_into_bcol( bcol_tab ) ;
1335
1336
1337 v_step := 'Step 45 ' ;
1338 if( v_perform_match = 'Y' ) then
1339
1340 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Calling CTOMCFGB perform_match ' ) ;
1341 CTO_MATCH_CONFIG.perform_match( v_ato_line_id ,
1342 x_return_status ,
1343 x_msg_count,
1344 x_msg_data
1345 ) ;
1346
1347 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'done CTOMCFGB perform_match ' ) ;
1348
1349
1350 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1351 perform_match , config_item_id into x_match_found , x_top_matched_item_id
1352 from bom_cto_order_lines_gt
1353 where line_id = v_ato_line_id ;
1354
1355 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'CTOMCFGB perform_match result ' || x_match_found ) ;
1356
1357
1358 if( x_match_found = 'Y' ) then
1359 -- insert into my_debug_messages values ( 'Top Model Match Success ' ) ;
1360 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Top Model Match Success ' ) ;
1361 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Top Match '|| to_char( x_top_matched_item_id ) ) ;
1362
1363 null ;
1364
1365 end if ;
1366
1367 end if ;
1368
1369 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'copying bcolgt to bcol ' ) ;
1370
1371
1372 CTO_UTILITY_PK.copy_bcolgt_bcol( v_ato_line_id , x_return_status, x_msg_count, x_msg_data ) ;
1373
1374
1375 select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
1376 into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
1377 v_ck_config_item_id, v_ck_perform_match
1378 from bom_cto_order_lines
1379 where line_id = v_ato_line_id ;
1380
1381 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' BCOL INFO ' || v_ck_line_id) ;
1382
1383
1384 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' line_id ' || v_ck_line_id) ;
1385 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' ato_line_id ' || v_ck_ato_line_id) ;
1386 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' inventory_item_id ' || v_ck_inventory_item_id) ;
1387 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' config_item_id ' || v_ck_config_item_id ) ;
1388 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' perform_match ' || v_ck_perform_match ) ;
1389
1390
1391
1392
1393
1394
1395
1396 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Done Sucessfully ' ) ;
1397
1398 exception
1399 when others then
1400 V_SQLCODE := SQLCODE ;
1401 V_SQLERRM := SQLERRM ;
1402
1403
1404 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' came into exception at step ' || v_step ) ;
1405 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' exception in process configurator SQL ' || to_char( V_SQLCODE ) ) ;
1406
1407 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' exception in process configurator SQL ' || V_SQLERRM ) ;
1408
1409
1410 END process_configurator_data ;
1411
1412
1413 /* Patchset J signature */
1414 procedure create_preconfig_item_ml(
1415 p_use_matched_item in varchar2,
1416 p_match_profile_on in varchar2,
1417 p_top_predefined_item_id in number,
1418 p_top_matched_item_id in number,
1419 p_top_ato_line_id in bom_cto_order_lines.ato_line_id%type,
1420 p_current_org_id in number ,
1421 x_bill_sequence_id out NOCOPY number,
1422 x_mlmo_item_created out NOCOPY varchar2,
1423 x_routing_exists out NOCOPY varchar2,
1424 x_return_status out NOCOPY varchar2,
1425 x_msg_count out NOCOPY number,
1426 x_msg_data out NOCOPY varchar2,
1427 x_t_dropped_items out NOCOPY CTO_CONFIG_BOM_PK.t_dropped_item_type
1428 )
1429 IS
1430 v_step varchar2(20) ;
1431 begin
1432
1433 v_step := 'Step 1 ' ;
1434
1435 create_preconfig_item_ml( p_use_matched_item => p_use_matched_item
1436 , p_match_profile_on => p_match_profile_on
1440 , p_current_org_id => p_current_org_id
1437 , p_top_predefined_item_id => p_top_predefined_item_id
1438 , p_top_matched_item_id => p_top_matched_item_id
1439 , p_top_ato_line_id => p_top_ato_line_id
1441 , x_bill_sequence_id => x_bill_sequence_id
1442 , x_mlmo_item_created => x_mlmo_item_created
1443 , x_routing_exists => x_routing_exists
1444 , x_return_status => x_return_status
1445 , x_msg_count => x_msg_count
1446 , x_msg_data => x_msg_data ) ;
1447
1448 v_step := 'Step 10 ' ;
1449
1450
1451
1452 if x_return_status = FND_API.G_RET_STS_ERROR then
1453
1454 oe_debug_pub.add ('Create_Preconfig_Item_ML:New: ' ||
1455 'Failed in create_preconfig_item_ml with expected error.', 1);
1456
1457 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1458 ':NEW: failed in create_preconfig_item_ml ' || x_msg_data ) ;
1459 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ':NEW: failed in create_preconfig_item_ml at step ' || to_char( x_msg_count) ) ;
1460 CTO_CONFIG_BOM_PK.get_dropped_components( x_t_dropped_items ) ; -- Fp bug 5485452
1461 raise FND_API.G_EXC_ERROR;
1462
1463 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1464
1465 oe_debug_pub.add ('Create_Preconfig_Item_ML:New: ' ||
1466 'Failed in create_preconfig_item_ml with unexpected error.', 1);
1467
1468 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1469 end if;
1470
1471
1472
1473 v_step := 'Step 15 ' ;
1474
1475 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1476 ':NEW: going to call dropped components ' ) ;
1477
1478 v_step := 'Step 20 ' ;
1479
1480 CTO_CONFIG_BOM_PK.get_dropped_components( x_t_dropped_items ) ;
1481
1482
1483 v_step := 'Step 30 ' ;
1484
1485 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1486 ':NEW: done dropped components ' ) ;
1487
1488 exception
1489 when FND_API.G_EXC_ERROR then
1490 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1491 ':NEW: came into expected exception create_preconfig_item_ml at step ' || v_step ) ;
1492
1493 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1494 ':NEW: came into expected exception create_preconfig_item_ml at step ' || x_msg_data ) ;
1495 x_return_status := FND_API.G_RET_STS_ERROR;
1496
1497 CTO_MSG_PUB.Count_And_Get
1498 (p_msg_count => x_msg_count
1499 ,p_msg_data => x_msg_data
1500 );
1501
1502
1503
1504
1505
1506 when FND_API.G_EXC_UNEXPECTED_ERROR then
1507 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1508 ':NEW: came into unexpected exception create_preconfig_item_ml at step ' || v_step ) ;
1509
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1511
1512 CTO_MSG_PUB.Count_And_Get
1513 (p_msg_count => x_msg_count
1514 ,p_msg_data => x_msg_data
1515 );
1516
1517
1518 when OTHERS then
1519 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1520 ':NEW: came into others exception create_preconfig_item_ml at step ' || v_step ) ;
1521
1522 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1523
1524
1525 CTO_MSG_PUB.Count_And_Get
1526 (p_msg_count => x_msg_count
1527 ,p_msg_data => x_msg_data
1528 );
1529
1530
1531 end create_preconfig_item_ml ;
1532
1533
1534
1535 procedure create_preconfig_item_ml(
1536 p_use_matched_item in varchar2,
1537 p_match_profile_on in varchar2,
1538 p_top_predefined_item_id in number,
1539 p_top_matched_item_id in number,
1540 p_top_ato_line_id in bom_cto_order_lines.ato_line_id%type,
1541 p_current_org_id in number ,
1542 x_bill_sequence_id out NOCOPY number,
1543 x_mlmo_item_created out NOCOPY varchar2,
1544 x_routing_exists out NOCOPY varchar2,
1545 x_return_status out NOCOPY varchar2,
1546 x_msg_count out NOCOPY number,
1547 x_msg_data out NOCOPY varchar2
1548 )
1549 IS
1550 lStatus integer ;
1551 XReturnStatus varchar2(1) ;
1552 XMsgCount number ;
1553 XMsgData varchar2(1000) ;
1554 v_step varchar2(100) ;
1558
1555 v_flow_calc number ;
1556 v_ship_from_org_id number ;
1557
1559 l_x_error_msg varchar2(200 ) ;
1560 l_x_msg_name varchar2(200) ;
1561
1562 lPerformMatch varchar2(1) ;
1563 cursor c_can_configurations is
1564 select line_id, inventory_item_id , parent_ato_line_id , perform_match
1565 from bom_cto_order_lines
1566 where bom_item_type = '1'
1567 and ato_line_id = p_top_ato_line_id
1568 and nvl(wip_supply_type,0) <> 6
1569 order by plan_level desc;
1570
1571 gUserId number ;
1572 gLoginId number ;
1573
1574 v_appl_name varchar2(20) ;
1575 v_error_name varchar2(20) ;
1576
1577 -- start fix 2394597
1578 l_top_model_id number;
1579 lprogram_id number;
1580 lconfig_item_id number;
1581 lValidationOrg number;
1582 licg_id number;
1583 -- end fix 2394597
1584
1585
1586 MATCHED_ITEM_BOM_NOT_FOUND exception ;
1587
1588 v_ck_line_id number ;
1589 v_ck_ato_line_id number ;
1590 v_ck_inventory_item_id number ;
1591 v_ck_config_item_id number ;
1592 v_ck_perform_match varchar2(10) ;
1593
1594
1595 v_dropped_count number := 0 ;
1596
1597 begin
1598
1599 gUserId := nvl(Fnd_Global.USER_ID, -1) ;
1600 gLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
1601
1602 x_msg_data := null ;
1603
1604
1605 v_step := 'Step 1 ' ;
1606
1607 -- insert into my_debug_messages values ( ' came into create_preconfig_item_ml ') ;
1608 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into create_preconfig_item_ml ') ;
1609
1610
1611
1612 select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
1613 into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
1614 v_ck_config_item_id, v_ck_perform_match
1615 from bom_cto_order_lines
1616 where line_id = p_top_ato_line_id ;
1617
1618 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' line_id ' || v_ck_line_id) ;
1619 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' ato_line_id ' || v_ck_ato_line_id) ;
1620 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' inventory_item_id ' || v_ck_inventory_item_id) ;
1621 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' config_item_id ' || v_ck_config_item_id ) ;
1622 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' perform_match ' || v_ck_perform_match ) ;
1623
1624 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' use_matched_item ' || p_use_matched_item ) ;
1625 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' top_predefined_item_id ' || p_top_predefined_item_id ) ;
1626 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' top_matched_item_id ' || p_top_matched_item_id ) ;
1627 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' p_match_profile_on ' || p_match_profile_on ) ;
1628
1629
1630
1631 if( p_use_matched_item = 'N' and p_match_profile_on = 'Y' ) then
1632
1633 -- insert into my_debug_messages values ( ' user has not opted to use matched item') ;
1634 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' user has not opted to use matched item') ;
1635
1636 update bom_cto_order_lines set config_item_id = p_top_predefined_item_id
1637 , perform_match = 'U' where line_id = p_top_ato_line_id ;
1638
1639 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1640 bom_cto_order_lines_gt
1641 set config_item_id = p_top_predefined_item_id
1642 , perform_match = 'U'
1643 where line_id = p_top_ato_line_id ;
1644
1645
1646
1647 elsif( p_use_matched_item = 'N' ) then
1648
1649
1650 update bom_cto_order_lines set config_item_id = p_top_predefined_item_id
1651 , perform_match = 'N' where line_id = p_top_ato_line_id ;
1652
1653 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1654 bom_cto_order_lines_gt
1655 set config_item_id = p_top_predefined_item_id
1656 , perform_match = 'N'
1657 where line_id = p_top_ato_line_id ;
1658
1659
1660
1661
1662 else
1663
1664 -- insert into my_debug_messages values ( ' user has opted to use matched item') ;
1665 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' user has opted to use matched item') ;
1666
1667 null ;
1668
1669
1670 end if;
1671
1672 v_step := 'Step 5 ' ;
1673
1674 -- insert into my_debug_messages values ( ' going to call populate_src_orgs ') ;
1675 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' going to call populate_src_orgs ') ;
1676
1677 -- insert into my_debug_messages values ( ' going to call populate_src_orgs ' || to_char( p_top_ato_line_id ) ) ;
1678 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' going to call populate_src_orgs ' || to_char( p_top_ato_line_id ) ) ;
1679
1680
1681
1682
1683 /*
1684 lStatus := CTO_MSUTIL_PUB.Populate_Src_Orgs(p_top_ato_line_id,
1688
1685 x_return_status,
1686 x_msg_count,
1687 x_msg_data);
1689 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , x_msg_data ) ;
1690 FND_MESSAGE.parse_encoded( x_msg_data, v_appl_name, v_error_name ) ;
1691
1692 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML ' , v_appl_name || ' err ' || v_error_name ) ;
1693
1694 if( x_return_status = FND_API.G_RET_STS_ERROR ) then
1695
1696
1697 RAISE FND_API.G_EXC_ERROR;
1698
1699
1700 elsif( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1702 end if ;
1703
1704
1705 -- insert into my_debug_messages values ( ' returned from populate_src_orgs ') ;
1706 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' returned from populate_src_orgs ') ;
1707
1708
1709 */
1710
1711
1712
1713
1714 -- start 2394597
1715
1716 v_step := 'Step 9 ' ;
1717
1718 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' copying model catalog group id from validation org ') ;
1719
1720 oe_debug_pub.add ('Getting top model item id.. ', 1) ;
1721
1722 -- Following statement will select top model item id.
1723 -- Since only one row is expected to be returned this is implemented
1724 -- as SELECT statement instead of using a CURSOR for multi-row
1725
1726 select inventory_item_id , nvl(program_id,0) ,config_item_id
1727 into l_top_model_id,lprogram_id,lconfig_item_id
1728 from bom_cto_order_lines
1729 where line_id=p_top_ato_line_id ;
1730
1731 oe_debug_pub.add ('Top Model Id : '||to_char(l_top_model_id)||' Program_id : '||to_char(lprogram_id)||' Config Item Id : '||to_char(lconfig_item_id), 2);
1732
1733
1734 oe_debug_pub.add ('Getting Validation Org.. ', 1) ;
1735
1736
1737 if lprogram_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID then
1738 lValidationOrg := CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG ;
1739 end if;
1740
1741
1742 oe_debug_pub.add ('Validation Org : '||to_char(lValidationOrg), 2) ;
1743
1744 -- Following statement will select item_catalog_grp_id
1745 -- of top model item id in validation org.
1746
1747 select nvl(item_catalog_group_id,0)
1748 into licg_id
1749 from mtl_system_items
1750 where inventory_item_id = l_top_model_id
1751 and organization_id = lValidationOrg;
1752
1753 oe_debug_pub.add ('Item Catalog Group Id : '||to_char(licg_id), 2) ;
1754
1755 -- If the model item has catalog , that catalog group id
1756 -- is copied to preconfig item in all org
1757
1758 if licg_id <> 0 then
1759 update mtl_system_items
1760 set item_catalog_group_id = licg_id
1761 where inventory_item_id = lconfig_item_id
1762 and nvl(item_catalog_group_id,0) = 0; --Bugfix 6043798
1763 oe_debug_pub.add ('Updated catalog group id of preconfig item ' , 2) ;
1764 end if;
1765
1766 -- end fix 2394597
1767
1768
1769
1770 v_step := 'Step 10 ' ;
1771
1772 /*
1773 lStatus := CTO_ITEM_PK.create_all_items( p_top_ato_line_id ,XReturnStatus, XMsgCount, XMsgData ) ;
1774 */
1775
1776
1777
1778
1779
1780
1781 select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
1782 into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
1783 v_ck_config_item_id, v_ck_perform_match
1784 from bom_cto_order_lines
1785 where line_id = p_top_ato_line_id ;
1786
1787 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' BEFORE ITEM CHECK BCOL ' || v_ck_line_id) ;
1788 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' line_id ' || v_ck_line_id) ;
1789 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' ato_line_id ' || v_ck_ato_line_id) ;
1790 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' inventory_item_id ' || v_ck_inventory_item_id) ;
1791 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' config_item_id ' || v_ck_config_item_id ) ;
1792 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' perform_match ' || v_ck_perform_match ) ;
1793
1794 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' use_matched_item ' || p_use_matched_item ) ;
1795 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' top_predefined_item_id ' || p_top_predefined_item_id ) ;
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808 lStatus := CTO_ITEM_PK.Create_And_Link_Item(p_top_ato_line_id,
1809 xReturnStatus ,
1810 xMsgCount ,
1811 xMsgData ,
1812 'PRECONFIG' ) ;
1813
1814
1815
1816 IF lStatus <> 1 then
1817 oe_debug_pub.add ('Create_All_Items returned with 0', 1) ;
1818 oe_debug_pub.add ('Create_All_Items returned with 0' || xMsgData , 1) ;
1819 oe_debug_pub.add ('Create_All_Items returned with 0' || to_char(xMsgCount) , 1) ;
1820
1821 x_msg_count := xMsgCount ;
1822 x_msg_data := xMsgData ;
1823
1824 -- cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
1825
1826 raise FND_API.G_EXC_ERROR;
1827
1828 end if;
1829
1830
1831
1832 -- insert into my_debug_messages values ( ' returned from CTO_ITEM_PK.create_all_items ') ;
1833 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' returned from CTO_ITEM_PK.create_all_items ') ;
1834
1835
1836
1837 v_step := 'Step 15 ' ;
1838
1839 begin
1840
1841 x_routing_exists := 'N' ;
1842
1843 v_step := 'Step 16 ' ;
1844
1845 select 'Y' into x_routing_exists
1846 from dual
1847 where EXISTS (
1848 select routing_sequence_id
1849 from bom_operational_routings bor, bom_cto_src_orgs bcso
1850 where bor.assembly_item_id = bcso.config_item_id
1851 and bor.organization_id = bcso.organization_id
1852 and bor.alternate_routing_designator is null
1853 and bcso.line_id = p_top_ato_line_id
1854 and bcso.create_bom = 'Y'
1855 ) ;
1856
1857
1858 exception
1859 when no_data_found then
1860 x_routing_exists := 'N' ;
1861
1862 when others then
1863
1864 cto_wip_workflow_api_pk.cto_debug( 'CREATE_PRECONFIG_ITEM_ML' ,
1865 ' error in checking if routing exists ') ;
1866
1867 raise fnd_api.g_exc_unexpected_error ;
1868 end ;
1869
1870
1871
1872
1873
1874 v_dropped_count := CTO_CONFIG_BOM_PK.get_dit_count ;
1875
1876 cto_wip_workflow_api_pk.cto_debug('CTO_PRECONFIG_ITEM_ML' , ' dropped count ' || to_char(v_dropped_count) );
1877
1878
1879 if( v_dropped_count > 0 ) then
1880 cto_config_bom_pk.reset_dropped_components ;
1881 end if ;
1882
1883
1884 v_dropped_count := CTO_CONFIG_BOM_PK.get_dit_count ;
1885
1886
1887 cto_wip_workflow_api_pk.cto_debug('CTO_PRECONFIG_ITEM_ML' , ' dropped count ' || to_char(v_dropped_count) );
1888
1889
1890
1891
1892 v_step := 'Step 20 ' ;
1893
1894
1895 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' before create bom error message ' || x_msg_data ) ;
1896
1897 CTO_BOM_RTG_PK.create_all_boms_and_routings( p_top_ato_line_id,
1898 v_flow_calc,
1899 x_return_status,
1900 x_msg_count,
1901 x_msg_data);
1902
1903 v_step := 'Step 21 ' ;
1904
1905
1906 -- insert into my_debug_messages values( ' error message ' || x_msg_data ) ;
1907 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' error message ' || x_msg_data ) ;
1908
1909 v_step := 'Step 22 ' ;
1910
1911 if( x_return_status = FND_API.G_RET_STS_ERROR ) then
1912 RAISE FND_API.G_EXC_ERROR ;
1913 elsif( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1915 end if ;
1916
1917 -- insert into my_debug_messages values ( ' returned from create_all_boms_and_routings ') ;
1918 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' returned from create_all_boms_and_routings ') ;
1919
1920
1921 v_step := 'Step 30 ' ;
1922
1923
1924
1925 if( p_match_profile_on = 'Y' and p_use_matched_item = 'N' ) then
1926
1927
1928 if( p_top_matched_item_id is not null ) then
1929
1930
1931 v_step := 'Step 32 ' ;
1932 delete from bom_ato_configurations where config_item_id =
1933 p_top_matched_item_id ;
1934
1935 v_step := 'Step 33 ' ;
1936
1937 -- insert into my_debug_messages values( ' delete top matched item id from bom_ato_configurations for item ' || to_char( p_top_matched_item_id ) ) ;
1938 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' delete top matched item id from bom_ato_configurations for item ' || to_char( p_top_matched_item_id ) ) ;
1939
1940
1941 end if ;
1942
1943
1944
1945 /*
1946 for lNextRec in c_can_configurations
1947 loop
1948
1949 v_step := 'Step 34 ' ;
1950
1951 -- insert into my_debug_messages values ( ' fetched ' || to_char(lNextRec.line_id) ) ;
1952 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' fetched ' || to_char(lNextRec.line_id) ) ;
1953
1954 v_step := 'Step 34b ' ;
1955
1956 select perform_match
1957 into lPerformMatch
1958 from bom_cto_order_lines
1959 where line_id = lNextRec.line_id;
1960
1961
1962 if( lPerformMatch = 'N' ) then
1963
1964 v_step := 'Step 35 ' ;
1968 0,
1965 lStatus := CTO_MATCH_CONFIG.can_configurations(
1966 lNextRec.line_id,
1967 0,
1969 0,
1970 gUserId,
1971 gLoginId,
1972 l_x_error_msg,
1973 l_x_msg_name);
1974
1975
1976 -- insert into my_debug_messages values ( ' canned configuration ' || to_char( lNextRec.line_id ) ) ;
1977 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' canned configuration ' || to_char( lNextRec.line_id ) ) ;
1978
1979 oe_debug_pub.add(
1980 'Returned from canning in stmt num 110 with status '
1981 || to_char(lStatus), 1);
1982
1983 if (lStatus <> 1) then
1984
1985
1986 v_step := 'Step 40 ' ;
1987
1988 raise fnd_api.g_exc_unexpected_error;
1989
1990 end if; -- end lStatus <> 1
1991
1992
1993
1994
1995 end if ; * if lPerformMatch *
1996 end loop ;
1997 */
1998
1999
2000 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' canning has been outsourced to CTO_ITEM_PK ' ) ;
2001
2002
2003
2004 end if ; /* match_profile_on */
2005
2006
2007 v_step := 'Step 45 ' ;
2008
2009 if( p_use_matched_item = 'Y' and p_match_profile_on = 'Y' ) then
2010
2011
2012 begin
2013
2014 select bill_sequence_id into x_bill_sequence_id
2015 from bom_bill_of_materials
2016 where assembly_item_id = p_top_matched_item_id
2017 and organization_id = p_current_org_id ;
2018
2019 exception
2020 when no_data_found then
2021 raise MATCHED_ITEM_BOM_NOT_FOUND ;
2022
2023 when others then
2024 raise;
2025
2026 end;
2027
2028
2029
2030 v_step := 'Step 47 ' ;
2031
2032 -- insert into my_debug_messages values( 'matched bill_sequence_id is ' || to_char( x_bill_sequence_id ) ) ;
2033 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , 'matched bill_sequence_id is ' || to_char( x_bill_sequence_id ) ) ;
2034
2035 end if ;
2036
2037
2038 v_step := 'Step 50 ' ;
2039
2040
2041 begin
2042
2043
2044
2045 select decode( count(*) , 0 , 'N' , 'Y' ) into x_mlmo_item_created
2046 from bom_cto_src_orgs
2047 where organization_id <> nvl(rcv_org_id, organization_id)
2048 and top_model_line_id = p_top_ato_line_id ;
2049
2050
2051
2052 v_step := 'Step 55 ' ;
2053
2054 -- insert into my_debug_messages values ( ' mlmo item has been created ' || x_mlmo_item_created );
2055 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' mlmo item has been created ' || x_mlmo_item_created );
2056
2057 exception
2058 when no_data_found then
2059
2060 v_step := 'Step 56 ' ;
2061 x_mlmo_item_created := 'N' ;
2062 -- insert into my_debug_messages values ( ' mlmo item has not been created ' );
2063 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' mlmo item has not been created ' );
2064
2065 when others then
2066 raise ;
2067
2068 end ;
2069
2070
2071
2072
2073
2074
2075 v_step := 'Step 60 ' ;
2076
2077 delete from bom_cto_order_lines
2078 where ato_line_id = p_top_ato_line_id ;
2079
2080 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' deleted bcol ' || to_char(sql%rowcount)) ;
2081
2082 delete from bom_cto_src_orgs_b
2083 where top_model_line_id = p_top_ato_line_id ;
2084
2085 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' deleted bcso_b ' || to_char(sql%rowcount)) ;
2086
2087
2088
2089 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' exiting create_preconfig_item_ml ') ;
2090
2091
2092
2093 -- This is a wrapper API to call PLM team's to sync up item media index
2094 -- With out this sync up the item cannot be searched in Simple item search page
2095 -- Bug 6033399 (FP 6034006)
2096 CTO_MSUTIL_PUB.syncup_item_media_index;
2097
2098
2099 exception
2100
2101 when MATCHED_ITEM_BOM_NOT_FOUND then
2102
2103 x_return_status := FND_API.G_RET_STS_ERROR;
2104 x_msg_data := 'CTO_MATCHED_BOM_NOT_FOUND' ;
2105
2106 CTO_MSG_PUB.Count_And_Get
2107 (p_msg_count => x_msg_count
2108 ,p_msg_data => x_msg_data
2109 );
2110
2111
2112
2113
2114
2115 when FND_API.G_EXC_ERROR then
2116 -- insert into my_debug_messages values ( ' came into expected exception create_preconfig_item_ml at step ' || v_step ) ;
2120 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || to_char( x_msg_count) ) ;
2117 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || v_step ) ;
2118
2119 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || x_msg_data ) ;
2121
2122
2123 x_return_status := FND_API.G_RET_STS_ERROR;
2124
2125 CTO_MSG_PUB.Count_And_Get
2126 (p_msg_count => x_msg_count
2127 ,p_msg_data => x_msg_data
2128 );
2129
2130 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || x_msg_data ) ;
2131 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || to_char( x_msg_count) ) ;
2132
2133
2134
2135 -- x_msg_data := FND_MESSAGE.GET ;
2136
2137 when FND_API.G_EXC_UNEXPECTED_ERROR then
2138 -- insert into my_debug_messages values ( ' came into unexpected exception create_preconfig_item_ml at step ' || v_step ) ;
2139 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into unexpected exception create_preconfig_item_ml at step ' || v_step ) ;
2140
2141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2142
2143 CTO_MSG_PUB.Count_And_Get
2144 (p_msg_count => x_msg_count
2145 ,p_msg_data => x_msg_data
2146 );
2147
2148
2149 when OTHERS then
2150 -- insert into my_debug_messages values ( ' came into others exception create_preconfig_item_ml at step ' || v_step ) ;
2151 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into others exception create_preconfig_item_ml at step ' || v_step ) ;
2152
2153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2154
2155
2156 CTO_MSG_PUB.Count_And_Get
2157 (p_msg_count => x_msg_count
2158 ,p_msg_data => x_msg_data
2159 );
2160
2161
2162
2163 end create_preconfig_item_ml ;
2164
2165
2166
2167
2168 procedure contiguous_to_sparse_bcol(
2169 p_t_bcol in out NOCOPY bcol_tbl_type
2170 )
2171 is
2172 p_t_sparse_bcol bcol_tbl_type ;
2173 v_line_id number ;
2174
2175 begin
2176 for i in 1..p_t_bcol.count
2177 loop
2178 p_t_sparse_bcol(i) := p_t_bcol(i) ;
2179 end loop ;
2180
2181 p_t_bcol.delete ;
2182
2183 for i in 1..p_t_sparse_bcol.count
2184 loop
2185 p_t_bcol(p_t_sparse_bcol(i).line_id) := p_t_sparse_bcol(i) ;
2186
2187 v_line_id := p_t_sparse_bcol(i).line_id ;
2188
2189 -- insert into my_debug_messages values ( 'p_t_sparse' || to_char( v_line_id ) ) ;
2190 end loop ;
2191
2192
2193
2194 end contiguous_to_sparse_bcol ;
2195
2196
2197
2198 procedure sparse_to_contiguous_bcol(
2199 p_t_bcol in out NOCOPY bcol_tbl_type
2200 )
2201 is
2202 p_t_plain_bcol bcol_tbl_type ;
2203 i number ;
2204 v_line_id number ;
2205 begin
2206 i := p_t_bcol.first ;
2207
2208 while i is not null
2209 loop
2210 p_t_plain_bcol(p_t_plain_bcol.count + 1 ) := p_t_bcol(i) ;
2211 i := p_t_bcol.next(i) ;
2212
2213 v_line_id := p_t_plain_bcol(p_t_plain_bcol.count).line_id ;
2214
2215 -- insert into my_debug_messages values ( 'p_t_plain ' || to_char( v_line_id ) ) ;
2216
2217 end loop ;
2218
2219 p_t_bcol.delete ;
2220
2221 for i in 1..p_t_plain_bcol.count
2222 loop
2223 p_t_bcol(i) := p_t_plain_bcol(i) ;
2224 end loop ;
2225
2226
2227
2228 end sparse_to_contiguous_bcol ;
2229
2230
2231
2232 PROCEDURE populate_parent_ato
2233 ( p_t_bcol in out NOCOPY bcol_tbl_type,
2234 p_bcol_line_id in bom_cto_order_lines.line_id%type )
2235 is
2236 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
2237 v_raw_line_id TABNUM ;
2238 v_src_point number ;
2239 v_prev_src_point number ;
2240 j number ;
2241 v_step VARCHAR2(10) ;
2242 i number := 0 ;
2243
2244 begin
2245
2246 /*
2247 ** Strategy: Resolve parent_ato for each line item by setting it to 1 + plan_level of parent.
2248 ** use the link_to_line_id column to get to the parent. if parents plan_level is not yet
2249 ** resolved, go to its immediate ancestor recursively till you find a line item with
2250 ** plan_level set( Top level plan level is always set to zero ). When coming out of recursion
2251 ** set the plan_level of any ancestors that havent been resolved yet.
2252 ** Implementation: As Pl/Sql does not support Stack built-in-datatype, an equivalent behavior
2253 ** can be achieved by storing items in a table( PUSH implementation) and retrieving them from
2257 v_step := 'Step C1' ;
2254 ** the end of the table ( POP implmentation [LIFO] )
2255 */
2256
2258
2259 i := p_t_bcol.first ;
2260
2261
2262 /* for i in 1..p_t_bcol.last commented for bug 1728383 */
2263
2264 while i is not null
2265 loop
2266
2267 if( p_t_bcol.exists(i) ) then
2268
2269 v_src_point := i ;
2270 /* please note, here it stores the index which is the same as line_id due to sparse array*/
2271
2272 /*
2273 ** resolve parent ato line id for item.
2274 */
2275 v_step := 'Step C2' ;
2276
2277 while( p_t_bcol.exists(v_src_point) )
2278 loop
2279
2280 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
2281 /* store each unresolved item in its heirarchy */
2282
2283 v_prev_src_point := v_src_point ;
2284
2285 v_src_point := p_t_bcol(v_src_point).link_to_line_id ;
2286
2287
2288
2289
2290 if( v_src_point is null or v_prev_src_point = p_bcol_line_id ) then
2291 v_src_point := v_prev_src_point ;
2292
2293 /* break if pto is on top of top level ato or
2294 the current lineid is top level phantom ato
2295 */
2296
2297 exit ;
2298 end if ;
2299 if( p_t_bcol(v_src_point).bom_item_type = '1' AND
2300 p_t_bcol(v_src_point).ato_line_id is not null AND
2301 nvl( p_t_bcol(v_src_point).wip_supply_type , 0 ) <> '6' ) then
2302
2303 exit ;
2304 /* break if non phantom ato parent found */
2305 end if ;
2306
2307
2308
2309 end loop ;
2310
2311 j := v_raw_line_id.count ; /* total number of items to be resolved */
2312
2313 v_step := 'Step C3' ;
2314
2315 while( j >= 1 )
2316 loop
2317
2318 p_t_bcol(v_raw_line_id(j)).parent_ato_line_id := v_src_point ;
2319
2320 j := j -1 ;
2321
2322 end loop ;
2323
2324 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
2325
2326 end if ;
2327
2328
2329
2330 i := p_t_bcol.next(i) ; /* added for bug 1728383 for performance */
2331
2332
2333 end loop ;
2334
2335 exception
2336 when others then
2337 -- insert into my_debug_messages values ( ' came into parent_ato exception at step ' || v_step ) ;
2338 null ;
2339 end populate_parent_ato ;
2340
2341
2342
2343
2344
2345
2346 PROCEDURE populate_plan_level
2347 ( p_t_bcol in out NOCOPY bcol_tbl_type )
2348 is
2349 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
2350 v_raw_line_id TABNUM ;
2351 v_src_point number ;
2352 j number ;
2353 v_step VARCHAR2(10) ;
2354 i number := 0 ;
2355
2356 begin
2357
2358 /*
2359 ** Strategy: Resolve plan_level for each line item by setting it to 1 + plan_level of parent.
2360 ** use the link_to_line_id column to get to the parent. if parents plan_level is not yet
2361 ** resolved, go to its immediate ancestor recursively till you find a line item with
2362 ** plan_level set( Top level plan level is always set to zero ). When coming out of recursion
2363 ** set the plan_level of any ancestors that havent been resolved yet.
2364 ** Implementation: As Pl/Sql does not support Stack built-in-datatype, an equivalent behavior
2365 ** can be achieved by storing items in a table( PUSH implementation) and retrieving them from
2366 ** the end of the table ( POP implmentation [LIFO] )
2367 */
2368
2369 v_step := 'Step B1' ;
2370
2371 i := p_t_bcol.first ;
2372
2373
2374
2375 /* for i in 1..p_t_bcol.last commented for bug 1728383 */
2376
2377
2378 while i is not null
2379 loop
2380
2381 if( p_t_bcol.exists(i) ) then
2382
2383 v_src_point := i ;
2384
2385
2386 /*
2387 ** resolve plan level for item only if not yet resolved
2388 */
2389
2390 while( p_t_bcol(v_src_point).plan_level is null )
2391 loop
2392
2393 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
2394 /* store each unresolved item in its heirarchy */
2395
2396 v_src_point := p_t_bcol(v_src_point).link_to_line_id ;
2397
2398 end loop ;
2399
2400 v_step := 'Step B2' ;
2401
2402 j := v_raw_line_id.count ; /* total number of items to be resolved */
2403
2404 while( j >= 1 )
2405 loop
2406
2407 p_t_bcol(v_raw_line_id(j)).plan_level := p_t_bcol(v_src_point).plan_level + 1;
2408
2409 v_src_point := v_raw_line_id(j) ;
2410
2411 j := j -1 ;
2412 end loop ;
2413
2414 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
2415
2416 end if ;
2417
2418
2419
2420 i := p_t_bcol.next(i) ; /* added for bug 1728383 for performance */
2424
2421
2422
2423 end loop ;
2425 exception
2426 when others then
2427 -- insert into my_debug_messages values ( ' came into plan_level exception at step ' || v_step ) ;
2428 null ;
2429
2430 end populate_plan_level ;
2431
2432
2433
2434 procedure insert_into_bcol (
2435 p_bcol_tab bcol_tbl_type
2436 )
2437 IS
2438 v_step number := 0 ;
2439 v_sqlcode number := 0 ;
2440 i number ;
2441 BEGIN
2442
2443 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' came into insert into bcol: ' , 1 ) ;
2444
2445
2446
2447
2448 if( p_bcol_tab.count = 0 ) then
2449 return ;
2450 end if ;
2451
2452 i := p_bcol_tab.first ;
2453
2454 while i is not null
2455 loop
2456
2457 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
2458 ' line_id ' || p_bcol_tab(i).line_id ||
2459 ' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
2460 ' qty ' || p_bcol_tab(i).ordered_quantity , 1) ;
2461
2462 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
2463 ' line_id ' || p_bcol_tab(i).line_id ||
2464 ' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
2465 ' parent qty ' || p_bcol_tab(p_bcol_tab(i).parent_ato_line_id ).ordered_quantity , 1) ;
2466 v_step := i ;
2467
2468 Insert into bom_cto_order_lines_gt(
2469 HEADER_ID ,
2470 LINE_ID ,
2471 LINK_TO_LINE_ID ,
2472 ATO_LINE_ID ,
2473 PARENT_ATO_LINE_ID ,
2474 TOP_MODEL_LINE_ID ,
2475 PLAN_LEVEL ,
2476 WIP_SUPPLY_TYPE ,
2477 PERFORM_MATCH ,
2478 BOM_ITEM_TYPE ,
2479 COMPONENT_CODE ,
2480 COMPONENT_SEQUENCE_ID ,
2481 CONFIG_ITEM_ID ,
2482 INVENTORY_ITEM_ID ,
2483 ITEM_TYPE_CODE ,
2484 BATCH_ID ,
2485 ORDERED_QUANTITY ,
2486 ORDER_QUANTITY_UOM ,
2487 SCHEDULE_SHIP_DATE ,
2488 SHIP_FROM_ORG_ID ,
2489 LAST_UPDATE_DATE ,
2490 LAST_UPDATED_BY ,
2491 CREATION_DATE ,
2492 CREATED_BY ,
2493 LAST_UPDATE_LOGIN ,
2494 REQUEST_ID ,
2495 PROGRAM_APPLICATION_ID ,
2496 PROGRAM_ID ,
2497 PROGRAM_UPDATE_DATE ,
2498 qty_per_parent_model,
2499 option_specific ,
2500 reuse_config ,
2501 config_creation )
2502 VALUES (
2503 nvl( p_bcol_tab(i).header_id , 1 ) ,
2504 p_bcol_tab(i).line_id,
2505 p_bcol_tab(i).link_to_line_id,
2506 p_bcol_tab(i).ato_line_id,
2507 p_bcol_tab(i).parent_ato_line_id,
2508 nvl( p_bcol_tab(i).top_model_line_id,1 ),
2509 p_bcol_tab(i).plan_level,
2510 p_bcol_tab(i).wip_supply_type,
2511 p_bcol_tab(i).perform_match,
2512 p_bcol_tab(i).bom_item_type,
2513 p_bcol_tab(i).component_code,
2514 p_bcol_tab(i).component_sequence_id,
2515 p_bcol_tab(i).config_item_id,
2516 p_bcol_tab(i).inventory_item_id,
2517 nvl( p_bcol_tab(i).item_type_code, decode( p_bcol_tab(i).line_id, p_bcol_tab(i).ato_line_id , 'MODEL' , decode( p_bcol_tab(i).bom_item_type , '4' , 'OPTION' , 'CLASS' ) )),
2518 p_bcol_tab(i).batch_id,
2519 p_bcol_tab(i).ordered_quantity,
2520 p_bcol_tab(i).order_quantity_uom,
2521 p_bcol_tab(i).schedule_ship_date,
2522 p_bcol_tab(i).ship_from_org_id,
2523 p_bcol_tab(i).last_update_date,
2524 p_bcol_tab(i).last_updated_by,
2525 p_bcol_tab(i).creation_date,
2526 p_bcol_tab(i).created_by ,
2527 p_bcol_tab(i).last_update_login ,
2528 p_bcol_tab(i).request_id ,
2529 p_bcol_tab(i).program_application_id ,
2530 p_bcol_tab(i).program_id ,
2531 p_bcol_tab(i).program_update_date ,
2532 p_bcol_tab(i).ordered_quantity / p_bcol_tab(p_bcol_Tab(i).parent_ato_line_id).ordered_quantity ,
2533 p_bcol_tab(i).option_specific ,
2534 p_bcol_tab(i).reuse_config,
2535 p_bcol_tab(i).config_creation ) ;
2536
2537 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
2538 ' line_id ' || p_bcol_tab(i).line_id ||
2539 ' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
2540 ' ato line_id ' || p_bcol_tab(i).ato_line_id ||
2541 ' bom_item_type ' || nvl( p_bcol_tab(i).bom_item_type, -7) ||
2545
2542 ' wip_supply_type ' || nvl( p_bcol_tab(i).wip_supply_type , -7) ||
2543 ' config_item_id ' || nvl( p_bcol_tab(i).config_item_id , -7) ||
2544 ' count ' || SQL%ROWCOUNT , 2);
2546
2547 i:= p_bcol_tab.next(i) ;
2548
2549 end loop ;
2550
2551 exception
2552 when others then
2553 V_SQLCODE := SQLCODE ;
2554 cto_wip_workflow_api_pk.cto_debug( ' exception in bcol_gt at step ' || to_char( v_step ) , 1 ) ;
2555 cto_wip_workflow_api_pk.cto_debug( ' exception in bcol_gt at step ' || V_SQLCODE , 1 ) ;
2556 END INSERT_INTO_BCOL ;
2557
2558
2559
2560 /*
2561 ** This procedure requires the parameter passed in to be populated with line_id
2562 ** and component_code without which the intended functionality is not guaranteed
2563 */
2564 procedure populate_link_to_line_id(
2565 p_bcol_tab in out NOCOPY bcol_tbl_type
2566 )
2567 is
2568 TYPE varchar2_1000_tbl_type is table of varchar2(1000 ) index by binary_integer ;
2569 v_parent_code_tab varchar2_1000_tbl_type ;
2570 v_loc number :=0 ;
2571 begin
2572
2573
2574 for i in 1..p_bcol_tab.count
2575 loop
2576
2577 v_loc := instr(p_bcol_tab(i).component_code , '-' , -1 ) ;
2578
2579 if( v_loc = 0 ) then
2580 v_parent_code_tab(i) := null ;
2581 else
2582 v_parent_code_tab(i ) := substr( p_bcol_tab(i).component_code , 1 , v_loc - 1 ) ;
2583 end if ;
2584
2585 p_bcol_tab(i).link_to_line_id := null ; /* clear existing data as top model needs null*/
2586 end loop;
2587
2588
2589 for i in 1..v_parent_code_tab.count
2590 loop
2591
2592 for j in 1..p_bcol_tab.count
2593 loop
2594 if( v_parent_code_tab(i) = p_bcol_tab(j).component_code ) then
2595 p_bcol_tab(i).link_to_line_id := p_bcol_tab(j).line_id ;
2596 exit ;
2597 end if ;
2598 end loop ;
2599
2600 end loop ;
2601
2602
2603
2604 end populate_link_to_line_id;
2605
2606
2607
2608
2609 procedure perform_match(
2610 p_ato_line_id in bom_cto_order_lines.ato_line_id%type ,
2611 x_match_found out NOCOPY varchar2,
2612 x_matching_config_id out NOCOPY number,
2613 x_error_message out NOCOPY VARCHAR2,
2614 x_message_name out NOCOPY varchar2
2615 )
2616 is
2617 l_stmt_num number := 0;
2618 l_cfm_value number;
2619 l_config_line_id number;
2620 l_tree_id integer;
2621 l_return_status varchar2(1);
2622 l_x_error_msg_count number;
2623 l_x_error_msg varchar2(240);
2624 l_x_error_msg_name varchar2(30);
2625 l_x_table_name varchar2(30);
2626 l_match_profile varchar2(10);
2627 l_org_id number;
2628 l_model_id number;
2629 l_primary_uom_code varchar(3);
2630 l_x_config_id number;
2631 l_top_model_line_id number;
2632
2633 l_x_qoh number;
2634 l_x_rqoh number;
2635 l_x_qs number;
2636 l_x_qr number;
2637 l_x_att number;
2638 l_active_activity varchar2(30);
2639 l_x_bill_seq_id number;
2640 l_status integer;
2641
2642 l_perform_match varchar2(1) ;
2643
2644 x_return_status varchar2(1);
2645 x_msg_count number;
2646 x_msg_data varchar2(100);
2647
2648 PROCESS_ERROR EXCEPTION;
2649
2650
2651 cursor c_model_lines is
2652 select line_id, parent_ato_line_id
2653 from bom_cto_order_lines
2654 where bom_item_type = '1'
2655 and ato_line_id = p_ato_line_id
2656 and nvl(wip_supply_type,0) <> 6
2657 order by plan_level desc;
2658
2659 v_sqlcode number ;
2660 l_custom_match_profile varchar2(10);
2661 begin
2662
2663 l_stmt_num := 1;
2664
2665 x_match_found := 'N' ;
2666
2667 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
2668
2669 l_stmt_num := 5;
2670
2671 /* for each model */
2672
2673 for l_next_rec in c_model_lines loop
2674
2675 l_x_config_id := NULL;
2676
2677
2678
2679 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2680 perform_match into l_perform_match
2681 from bom_cto_order_lines_gt
2682 where line_id = l_next_rec.line_id ;
2683
2684
2685
2686 if( l_perform_match = 'N' ) then
2687
2688 begin
2689
2690 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2691 bom_cto_order_lines_gt set perform_match = 'N'
2692 where perform_match = 'Y'
2693 and line_id = l_next_rec.parent_ato_line_id ;
2694
2695 exception
2696 when no_data_found then
2697 null ;
2698
2699 end ;
2700
2701 x_match_found := 'N' ;
2705
2702
2703 x_matching_config_id := NULL ; /* fix for bug#2048023. */
2704
2706
2707 else
2708
2709 if ( l_custom_match_profile = 2) then
2710 l_stmt_num := 10;
2711 oe_debug_pub.add('Standard Match.', 1);
2712 l_status := cto_match_config.check_config_match(
2713 l_next_rec.line_id,
2714 l_x_config_id,
2715 l_x_error_msg,
2716 l_x_error_msg_name);
2717
2718 elsif (l_custom_match_profile = 1) then
2719 l_stmt_num := 15;
2720 l_status := CTO_CUSTOM_MATCH_PK.find_matching_config(
2721 l_next_rec.line_id,
2722 l_x_config_id,
2723 l_x_error_msg,
2724 l_x_error_msg_name,
2725 l_x_table_name);
2726 end if;
2727
2728 l_stmt_num := 20;
2729
2730 if (l_status = 0) then
2731 oe_debug_pub.add('Failed in Check Config Match for line id '
2732 || to_char(l_next_rec.line_id), 1);
2733
2734 raise PROCESS_ERROR;
2735
2736 end if;
2737
2738
2739 l_stmt_num := 25;
2740
2741
2742 if (l_status = 1 and l_x_config_id is NULL) then
2743 l_stmt_num := 30;
2744
2745 x_message_name := 'CTO_MR_NO_MATCH';
2746 x_error_message := 'No matching configurations for line '
2747 || to_char(l_next_rec.line_id);
2748 l_stmt_num := 137;
2749
2750 -- insert into my_debug_messages values ( 'No Match found' ) ;
2751 x_match_found := 'N' ;
2752
2753 x_matching_config_id := NULL ; /* fix for bug#2048023. */
2754
2755 /* fix for bug#2048023.
2756 This variable has to be initialized to null as it was not
2757 null for a lower level match in the perform match loop.
2758 */
2759
2760
2761 /* update the perform match column to 'N' so that this item is canned */
2762 begin
2763 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2764 bom_cto_order_lines_gt
2765 set perform_match = 'N'
2766 where line_id = l_next_rec.line_id
2767 and perform_match = 'Y';
2768
2769 exception
2770 when no_data_found then
2771 null ;
2772
2773 end ;
2774
2775
2776
2777 /* update the perform match column to 'N' so that no match
2778 is attempted against its parent and it is canned
2779 */
2780
2781 begin
2782 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2783 bom_cto_order_lines_gt
2784 set perform_match = 'N'
2785 where line_id = l_next_rec.parent_ato_line_id
2786 and perform_match = 'Y';
2787
2788 exception
2789 when no_data_found then
2790 null ;
2791
2792 end ;
2793
2794
2795
2796
2797 elsif (l_status = 1 and l_x_config_id is not null) then
2798
2799 l_stmt_num := 35;
2800
2801 /*
2802 oe_debug_pub.add('Match for line id '
2803 || to_char(l_next_rec.line_id)
2804 || ' is ' || to_char(l_x_config_id) ,1);
2805 */
2806
2807 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2808 bom_cto_order_lines_gt
2809 set config_item_id = l_x_config_id
2810 where line_id = l_next_rec.line_id;
2811
2812 l_stmt_num := 40 ;
2813
2814 x_matching_config_id := l_x_config_id ;
2815
2816 x_match_found := 'Y' ;
2817
2818
2819 l_stmt_num := 45 ;
2820
2821 -- insert into my_debug_messages values ( 'Match found' ) ;
2822 -- insert into my_debug_messages values ( 'Matched Item ' || to_char(x_matching_config_id ) ) ;
2823
2824 end if;
2825
2826
2827 end if ; /* if perform_match = 'N' */
2828
2829
2830 end loop;
2831
2832
2833 exception
2834 when others then
2835 V_SQLCODE := SQLCODE ;
2836 -- insert into my_debug_messages values ( ' exception in match at step ' || to_char( l_stmt_num ) ) ;
2837 -- insert into my_debug_messages values ( ' exception in match SQL ' || to_char( V_SQLCODE ) ) ;
2838
2842 /*******************************************************************************************
2839 end perform_match ;
2840
2841 /* Copied this procedure as it is from BOMCZCBB.pls 115.9.1155.7 version */
2843 ** Procedure : BOM_INS_MODEL_AND_MANDATORY
2844 ** Parameters : Group_Id
2845 ** Bill_Sequence_Id
2846 ** Cz_Config_Hdr_Id
2847 ** Cz_Config_Rev_Num
2848 ** Purpose : This procedure will be called when the configurator Applet Returns after the
2849 ** user has Chosen a Configuration and hit Done. This procedure take the options
2850 ** the user has chosen and the option classes that those options belong to and insert
2851 ** them in a temporary table. Then it will take all the mandatory components that
2852 ** are associated with the option classes from which a user has chosen atleast 1
2853 ** Option and insert the data in a temporary table.
2854 ** Once the required data is gathered under one group id, the process will check if
2855 ** the Profile "BOM:CONFIG_INHERIT_OP_SEQ" is set.If YES then the procedure will
2856 ** loop through the option classes and assign the operation sequence to its children
2857 ** if the children have an op_seq of 1. This process will recursively loop through
2858 ** its children and perform the operation sequence inheritance for all the children.
2859 ** Once the records have been assigned the proper op-seq's the process will then
2860 ** proceed to consolidate the components. Components quantities for components with
2861 ** the same op-seq and component_item_id will be added and only 1 record for that
2862 ** combination will exist and the duplicates will be deleted. The final data will be
2863 ** moved from the temporary table to the production table and the data in the temp
2864 ** table will be cleaned up.
2865 ********************************************************************************************/
2866 PROCEDURE BOM_INS_MODEL_AND_MANDATORY(x_group_id IN NUMBER,
2867 x_bill_sequence_id IN NUMBER,
2868 x_top_bill_sequence_id IN NUMBER,
2869 x_cz_config_hdr_id IN NUMBER,
2870 x_cz_config_rev_num IN NUMBER,
2871 x_message IN OUT NOCOPY VARCHAR2) IS
2872 X_Stmt_Num NUMBER;
2873 X_Return_Val NUMBER;
2874 X_err_message VARCHAR2(2000);
2875 l_op_seq_profile NUMBER := 0;
2876 l_bill_sequence_id NUMBER;
2877 l_organization_id NUMBER;
2878
2879 /* Cursor will select the options that the user had chosen from the option classes
2880 on the Model
2881 */
2882
2883 CURSOR cz_options_chosen IS
2884 SELECT bic.bill_sequence_id,
2885 x_top_bill_sequence_id top_bill_sequence_id,
2886 bic.operation_seq_num,
2887 bic.component_item_id,
2888 bic.last_update_date,
2889 bic.last_updated_by,
2890 bic.creation_date,
2891 bic.created_by,
2892 bic.item_num,
2893 round( to_number(cz.quantity), 7) component_quantity, /* Support Decimal-Qty for Option Items */
2894 bic.component_yield_factor,
2895 bic.effectivity_date,
2896 bic.implementation_date,
2897 bic.planning_factor,
2898 bic.quantity_related,
2899 bic.so_basis,
2900 bic.optional,
2901 bic.mutually_exclusive_options,
2902 bic.include_in_cost_rollup,
2903 bic.check_atp,
2904 bic.required_to_ship,
2905 bic.required_for_revenue,
2906 bic.include_on_ship_docs,
2907 bic.include_on_bill_docs,
2908 bic.low_quantity,
2909 bic.high_quantity,
2910 bic.wip_supply_type,
2911 bic.pick_components,
2912 bic.bom_item_type,
2913 bic.component_sequence_id,
2914 bic.From_End_Item_Unit_Number,
2915 bic.To_End_Item_Unit_Number,
2916 bic.attribute_category,
2917 bic.attribute1,
2918 bic.attribute2,
2919 bic.attribute3,
2920 bic.attribute4,
2921 bic.attribute5,
2922 bic.attribute6,
2923 bic.attribute7,
2924 bic.attribute8,
2925 bic.attribute9,
2926 bic.attribute10,
2927 bic.attribute11,
2928 bic.attribute12,
2929 bic.attribute13,
2930 bic.attribute14,
2931 bic.attribute15,
2932 cz.component_code
2933 FROM bom_inventory_components bic
2934 , cz_config_details_v cz
2935 WHERE bic.bom_item_type NOT IN('1', '2')
2936 AND bic.component_sequence_id = cz.component_sequence_id
2937 AND cz.config_hdr_id = x_cz_config_hdr_id
2938 AND cz.config_rev_nbr = x_cz_config_rev_num;
2939
2940 /* Mandatory Components for the all the options classes in which user has chosen
2941 atleast 1 option (this is used when the operation sequence inheritance is OFF)
2942 */
2943 CURSOR c_cz_required_items IS
2944 SELECT bic.bill_sequence_id,
2945 x_top_bill_sequence_id,
2946 bic.operation_seq_num,
2947 bic.component_item_id,
2948 bic.last_update_date,
2949 bic.last_updated_by,
2950 bic.creation_date,
2951 bic.created_by,
2952 bic.item_num,
2956 bic.implementation_date,
2953 round( (bic.component_quantity * to_number(cz.quantity)), 7) component_quantity, /* Support Decimal-Qty for Option Items */
2954 bic.component_yield_factor,
2955 bic.effectivity_date,
2957 bic.planning_factor,
2958 bic.quantity_related,
2959 bic.so_basis,
2960 bic.optional,
2961 bic.mutually_exclusive_options,
2962 bic.include_in_cost_rollup,
2963 bic.check_atp,
2964 bic.required_to_ship,
2965 bic.required_for_revenue,
2966 bic.include_on_ship_docs,
2967 bic.include_on_bill_docs,
2968 bic.low_quantity,
2969 bic.high_quantity,
2970 bic.wip_supply_type,
2971 bic.pick_components,
2972 bic.bom_item_type,
2973 bic.component_sequence_id,
2974 bic.From_End_Item_Unit_Number,
2975 bic.To_End_Item_Unit_Number,
2976 bic.attribute_category,
2977 bic.attribute1,
2978 bic.attribute2,
2979 bic.attribute3,
2980 bic.attribute4,
2981 bic.attribute5,
2982 bic.attribute6,
2983 bic.attribute7,
2984 bic.attribute8,
2985 bic.attribute9,
2986 bic.attribute10,
2987 bic.attribute11,
2988 bic.attribute12,
2989 bic.attribute13,
2990 bic.attribute14,
2991 bic.attribute15
2992 FROM bom_inventory_components bic
2993 , bom_inventory_components mod_oc
2994 , bom_bill_of_materials bom
2995 , cz_config_details_v cz
2996 WHERE cz.config_hdr_id = x_cz_config_hdr_id
2997 AND cz.config_rev_nbr = x_cz_config_rev_num
2998 AND mod_oc.component_sequence_id = cz.component_sequence_id
2999 AND mod_oc.bom_item_type IN (1,2)
3000 AND bom.assembly_item_id = mod_oc.component_item_id
3001 AND bom.organization_id = cz.organization_id
3002 AND bom.alternate_bom_designator IS NULL
3003 AND bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, bom.bill_sequence_id,
3004 bom.bill_sequence_id, bom.common_bill_sequence_id
3005 )
3006 AND bic.optional = 2
3007 AND bic.bom_item_type NOT IN (1,2)
3008 AND bic.effectivity_date <= SYSDATE
3009 AND nvl(bic.disable_date,SYSDATE+1) > SYSDATE;
3010
3011 /* Mandatory Components for the all the options classes in which user has chosen
3012 atleast 1 option (this is used for operation sequence inheritance from the parent)
3013 Additional table used here is bom_explosion_temp and the join to cz_config_details_v
3014 has been made using the component_code
3015 */
3016
3017 CURSOR c_cz_req_items_with_Inherit IS
3018 SELECT bic.bill_sequence_id,
3019 x_top_bill_sequence_id,
3020 bic.operation_seq_num,
3021 bet.operation_seq_num parent_operation_seq_num,
3022 bic.component_item_id,
3023 bic.last_update_date,
3024 bic.last_updated_by,
3025 bic.creation_date,
3026 bic.created_by,
3027 bic.item_num,
3028 round( (bic.component_quantity * to_number(cz.quantity)) , 7) component_quantity, /* Support Decimal-Qty for Option Items */
3029 bic.component_yield_factor,
3030 bic.effectivity_date,
3031 bic.implementation_date,
3032 bic.planning_factor,
3033 bic.quantity_related,
3034 bic.so_basis,
3035 bic.optional,
3036 bic.mutually_exclusive_options,
3037 bic.include_in_cost_rollup,
3038 bic.check_atp,
3039 bic.required_to_ship,
3040 bic.required_for_revenue,
3041 bic.include_on_ship_docs,
3042 bic.include_on_bill_docs,
3043 bic.low_quantity,
3044 bic.high_quantity,
3045 bic.wip_supply_type,
3046 bic.pick_components,
3047 bic.bom_item_type,
3048 bic.component_sequence_id,
3049 bic.From_End_Item_Unit_Number,
3050 bic.To_End_Item_Unit_Number,
3051 bic.attribute_category,
3052 bic.attribute1,
3053 bic.attribute2,
3054 bic.attribute3,
3055 bic.attribute4,
3056 bic.attribute5,
3057 bic.attribute6,
3058 bic.attribute7,
3059 bic.attribute8,
3060 bic.attribute9,
3061 bic.attribute10,
3062 bic.attribute11,
3063 bic.attribute12,
3064 bic.attribute13,
3065 bic.attribute14,
3066 bic.attribute15
3067 FROM bom_inventory_components bic
3068 , bom_inventory_components mod_oc
3069 , bom_bill_of_materials bom
3070 , cz_config_details_v cz
3071 , bom_explosion_temp bet
3072 WHERE cz.config_hdr_id = x_cz_config_hdr_id
3073 AND cz.config_rev_nbr = x_cz_config_rev_num
3074 AND cz.component_code = bet.component_code
3075 AND mod_oc.component_sequence_id = cz.component_sequence_id
3076 AND mod_oc.bom_item_type IN (1,2)
3077 AND bom.assembly_item_id = mod_oc.component_item_id
3078 AND bom.organization_id = cz.organization_id
3079 AND bom.alternate_bom_designator IS NULL
3080 AND bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, bom.bill_sequence_id,
3084 AND bic.bom_item_type NOT IN (1,2)
3081 bom.bill_sequence_id, bom.common_bill_sequence_id
3082 )
3083 AND bic.optional = 2
3085 AND bic.effectivity_date <= SYSDATE
3086 AND nvl(bic.disable_date,SYSDATE+1) > SYSDATE;
3087
3088 /* Components in the temp table with valid component code . Component code will be available
3089 for the records that are from cz_config_details_v (i.e all the option classes for the
3090 configured item and all the choosen options) */
3091
3092 CURSOR c_Comps_With_ComponentCode IS
3093 SELECT * from bom_explosion_temp WHERE component_code IS NOT NULL
3094 ORDER BY component_code;
3095
3096 /* Option classes that are under the top model */
3097
3098 CURSOR c_Options_of_Model IS
3099 SELECT bic.component_sequence_id
3100 , bic.component_item_id
3101 , bic.operation_seq_num
3102 , cz.organization_id
3103 FROM bom_inventory_components bic,
3104 cz_config_details_v cz
3105 WHERE cz.component_sequence_id = bic.component_sequence_id
3106 AND bic.bom_item_type IN (1,2)
3107 AND bic.bill_sequence_id = l_bill_sequence_id
3108 AND cz.config_hdr_id = x_cz_config_hdr_id
3109 AND cz.config_rev_nbr = x_cz_config_rev_num;
3110
3111
3112 CURSOR c_Club_Comps IS
3113 SELECT bet.bill_sequence_id
3114 , bet.component_item_id
3115 , bet.operation_seq_num
3116 , bet.component_sequence_id
3117 FROM bom_explosion_temp bet
3118 WHERE bill_sequence_id = x_bill_sequence_id
3119 ORDER BY bet.bill_sequence_id,
3120 bet.component_item_id,
3121 bet.operation_seq_num,
3122 bet.component_sequence_id;
3123
3124
3125 BEGIN
3126
3127 --
3128 -- Check if the Top Model being defined is using some model as common. If it is then
3129 -- we need to use the bill_sequence_id of the other model when find the bill structure
3130 --
3131
3132 SELECT DECODE(common_bill_sequence_id, x_top_bill_sequence_id,
3133 bill_sequence_id, common_bill_sequence_id
3134 ),
3135 DECODE(nvl(common_organization_id, organization_id), organization_id,
3136 organization_id, common_organization_id)
3137 INTO l_bill_sequence_id,
3138 l_organization_id
3139 FROM bom_bill_of_materials
3140 WHERE bill_sequence_id = x_top_bill_sequence_id;
3141
3142 X_Stmt_Num := 10;
3143
3144 /* Flush the temp table before starting the process */
3145
3146 DELETE from bom_explosion_temp;
3147
3148 /* Insert Model */
3149
3150 INSERT INTO BOM_INVENTORY_COMPONENTS
3151 (
3152 bill_sequence_id,
3153 component_sequence_id,
3154 component_item_id,
3155 creation_date,
3156 created_by,
3157 last_update_date,
3158 last_updated_by,
3159 attribute1,
3160 attribute2,
3161 attribute3,
3162 attribute4,
3163 attribute5,
3164 attribute6,
3165 attribute7,
3166 attribute8,
3167 attribute9,
3168 attribute10,
3169 attribute11,
3170 attribute12,
3171 attribute13,
3172 attribute14,
3173 attribute15,
3174 component_quantity,
3175 component_yield_factor,
3176 planning_factor,
3177 quantity_related,
3178 include_in_cost_rollup,
3179 so_basis,
3180 optional,
3181 mutually_exclusive_options,
3182 check_atp,
3183 shipping_allowed,
3184 required_to_ship,
3185 required_for_revenue,
3186 include_on_ship_docs,
3187 include_on_bill_docs,
3188 low_quantity,
3189 high_quantity,
3190 pick_components,
3191 bom_item_type,
3192 operation_seq_num,
3193 item_num,
3194 effectivity_date,
3195 disable_date,
3196 implementation_date,
3197 wip_supply_type
3198 )
3199 SELECT x_bill_sequence_id,
3200 BOM_INVENTORY_COMPONENTS_S.nextval,
3201 be.Component_Item_Id,
3202 SYSDATE,
3203 1,
3204 SYSDATE,
3205 1,
3206 be.Attribute1,
3207 be.Attribute2,
3208 be.Attribute3,
3209 be.Attribute4,
3210 be.Attribute5,
3211 be.Attribute6,
3212 be.Attribute7,
3213 be.Attribute8,
3214 be.Attribute9,
3215 be.Attribute10,
3216 be.Attribute11,
3217 be.Attribute12,
3218 be.Attribute13,
3219 be.Attribute14,
3220 be.Attribute15,
3221 round( be.Component_Quantity, 7 ), /* Support Decimal-Qty for Option Items */
3222 1, /* Component Yield*/
3223 100, /*Component Planning factor */
3224 2, /* Quantity Related */
3225 2, /* Include in Cost Rollup */
3226 2, /* SO Basis */
3227 1, /* Optional */
3228 2, /*Mutually_Exclusive_Options */
3229 2, /*Check_ATP*/
3230 2, /*Shipping Allowed */
3231 2, /*Required to ship */
3232 2, /*Required_For_Revenue*/
3236 be.High_Quantity,
3233 2, /*Include on Ship Docs */
3234 2, /*Include_On_Bill_Docs */
3235 be.Low_Quantity,
3237 1, /* Pick_Components */
3238 be.Bom_Item_Type,
3239 1, /*Operation Sequence Num */
3240 1, /*Item_Num */
3241 SYSDATE,
3242 NULL /*Disable_Date*/,
3243 SYSDATE, /* Implementation Date */
3244 6 /* wip_supply_type */
3245 FROM bom_explosions be
3246 WHERE be.top_bill_sequence_id = X_top_Bill_Sequence_id
3247 AND be.explosion_type = 'OPTIONAL'
3248 AND be.effectivity_date <= SYSDATE
3249 AND nvl(be.disable_date,SYSDATE+1) > SYSDATE
3250 AND be.plan_level = 0;
3251
3252 /* Insert the Mandatory Components of the Model */
3253
3254 INSERT INTO BOM_EXPLOSION_TEMP
3255 ( top_bill_sequence_id,
3256 organization_id,
3257 plan_level,
3258 sort_order,
3259 bill_sequence_id,
3260 component_sequence_id,
3261 component_item_id,
3262 attribute1,
3263 attribute2,
3264 attribute3,
3265 attribute4,
3266 attribute5,
3267 attribute6,
3268 attribute7,
3269 attribute8,
3270 attribute9,
3271 attribute10,
3272 attribute11,
3273 attribute12,
3274 attribute13,
3275 attribute14,
3276 attribute15,
3277 component_quantity,
3278 component_yield_factor,
3279 planning_factor,
3280 include_in_rollup_flag,
3281 so_transactions_flag, /* Used for Quantity Related */
3282 so_basis,
3283 optional,
3284 mutually_exclusive_options,
3285 check_atp,
3286 shipping_allowed,
3287 required_to_ship,
3288 required_for_revenue,
3289 include_on_ship_docs,
3290 include_on_bill_docs,
3291 low_quantity,
3292 high_quantity,
3293 pick_components,
3294 bom_item_type,
3295 operation_seq_num,
3296 item_num,
3297 effectivity_date,
3298 disable_date,
3299 implementation_date,
3300 wip_supply_type,
3301 pricing_attribute1, /** used for from unit item number **/
3302 pricing_attribute2 /** used for to unit item number **/
3303 )
3304 SELECT x_top_bill_sequence_id,
3305 l_organization_id, -- resolved at the begining
3306 1, /*Plan Level */
3307 '1', /* Sort Order */
3308 x_bill_sequence_id,
3309 component_sequence_id,
3310 Component_Item_Id,
3311 Attribute1,
3312 Attribute2,
3313 Attribute3,
3314 Attribute4,
3315 Attribute5,
3316 Attribute6,
3317 Attribute7,
3318 Attribute8,
3319 Attribute9,
3320 Attribute10,
3321 Attribute11,
3322 Attribute12,
3323 Attribute13,
3324 Attribute14,
3325 Attribute15,
3326 Component_Quantity,
3327 component_yield_factor,
3328 planning_factor, /*Component Planning factor */
3329 include_in_cost_rollup, /* Include in Cost Rollup */
3330 NVL(to_char(quantity_related),'2'),
3331 so_basis, /* SO Basis */
3332 optional, /* Optional */
3333 Mutually_Exclusive_Options, /*Mutually_Exclusive_Options */
3334 check_atp, /*Check_ATP*/
3335 shipping_allowed, /*Shipping Allowed */
3336 required_to_ship, /*Required to ship */
3337 required_for_revenue, /*Required_For_Revenue*/
3338 include_on_ship_docs, /*Include on Ship Docs */
3339 include_on_bill_docs, /*Include_On_Bill_Docs */
3340 Low_Quantity,
3341 High_Quantity,
3342 pick_components, /* Pick_Components */
3343 Bom_Item_Type,
3344 operation_seq_num, /*Operation Sequence Num */
3345 item_num, /*Item_Num */
3346 effectivity_date,
3347 disable_date, /*Disable_Date*/
3348 implementation_date, /* Implementation Date */
3349 wip_supply_type,/* wip_supply_type */
3350 from_end_item_unit_number,
3351 to_end_item_unit_number
3352 FROM bom_inventory_components
3353 WHERE bill_sequence_id = l_Bill_Sequence_id -- Sequence_id resolved at the begining
3354 AND effectivity_date <= SYSDATE
3355 AND nvl(disable_date,SYSDATE+1) > SYSDATE
3356 AND optional = 2
3357 AND bom_item_type NOT IN (1,2);
3358
3359 X_Stmt_num := 19;
3360
3361 /* Insert the Option Classes from which user has chosen atleast one option item along with
3362 the component code*/
3363
3364 INSERT INTO BOM_EXPLOSION_TEMP
3365 ( top_bill_sequence_id,
3366 organization_id,
3367 plan_level,
3368 sort_order,
3369 bill_sequence_id,
3370 component_sequence_id,
3371 component_item_id,
3372 attribute1,
3373 attribute2,
3374 attribute3,
3375 attribute4,
3376 attribute5,
3377 attribute6,
3378 attribute7,
3379 attribute8,
3380 attribute9,
3381 attribute10,
3382 attribute11,
3386 attribute15,
3383 attribute12,
3384 attribute13,
3385 attribute14,
3387 component_quantity,
3388 component_yield_factor,
3389 planning_factor,
3390 include_in_rollup_flag,
3391 so_transactions_flag, /* Used for Quantity Related */
3392 so_basis,
3393 optional,
3394 mutually_exclusive_options,
3395 check_atp,
3396 shipping_allowed,
3397 required_to_ship,
3398 required_for_revenue,
3399 include_on_ship_docs,
3400 include_on_bill_docs,
3401 low_quantity,
3402 high_quantity,
3403 pick_components,
3404 bom_item_type,
3405 operation_seq_num,
3406 item_num,
3407 effectivity_date,
3408 disable_date,
3409 implementation_date,
3410 wip_supply_type,
3411 pricing_attribute1, /** used for from unit item number **/
3412 pricing_attribute2, /** used for to unit item number **/
3413 component_code
3414 )
3415 SELECT x_top_bill_sequence_id,
3416 l_organization_id, -- resolved at the begining
3417 1, /*Plan Level */
3418 '1', /* Sort Order */
3419 x_bill_sequence_id,
3420 bic.component_sequence_id,
3421 bic.Component_Item_Id,
3422 Attribute1,
3423 Attribute2,
3424 Attribute3,
3425 Attribute4,
3426 Attribute5,
3427 Attribute6,
3428 Attribute7,
3429 Attribute8,
3430 Attribute9,
3431 Attribute10,
3432 Attribute11,
3433 Attribute12,
3434 Attribute13,
3435 Attribute14,
3436 Attribute15,
3437 --Component_Quantity,
3438 round( cz.quantity,7), /* Support Decimal-Qty for option items */
3439 component_yield_factor,
3440 planning_factor, /*Component Planning factor */
3441 include_in_cost_rollup, /* Include in Cost Rollup */
3442 NVL(to_char(quantity_related),'2'),
3443 so_basis, /* SO Basis */
3444 optional, /* Optional */
3445 Mutually_Exclusive_Options, /*Mutually_Exclusive_Options */
3446 check_atp, /*Check_ATP*/
3447 shipping_allowed, /*Shipping Allowed */
3448 required_to_ship, /*Required to ship */
3449 required_for_revenue, /*Required_For_Revenue*/
3450 include_on_ship_docs, /*Include on Ship Docs */
3451 include_on_bill_docs, /*Include_On_Bill_Docs */
3452 Low_Quantity,
3453 High_Quantity,
3454 pick_components, /* Pick_Components */
3455 bic.Bom_Item_Type,
3456 operation_seq_num, /*Operation Sequence Num */
3457 item_num, /*Item_Num */
3458 effectivity_date,
3459 disable_date, /*Disable_Date*/
3460 implementation_date, /* Implementation Date */
3461 wip_supply_type,/* wip_supply_type */
3462 from_end_item_unit_number,
3463 to_end_item_unit_number,
3464 cz.component_code
3465 FROM bom_inventory_components bic,
3466 cz_config_details_v cz
3467 WHERE bic.component_sequence_id = cz.component_sequence_id
3468 AND bic.bom_item_type IN (1,2) /* Model, Option Classes */
3469 AND cz.config_hdr_id = x_cz_config_hdr_id
3470 AND cz.config_rev_nbr = x_cz_config_rev_num;
3471
3472 X_Stmt_Num := 20;
3473
3474 /** Check if the BOM:CONFIG_INHERIT_OP_SEQ is Set **/
3475
3476 l_op_seq_profile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
3477
3478 /* Insert Mandatory Components for the options selected if inheritance is OFF */
3479
3480 IF l_op_seq_profile <> 1 THEN
3481
3482 FOR cz_mandatory_items IN c_cz_required_items
3483 LOOP
3484 INSERT INTO bom_explosion_temp(
3485 top_bill_sequence_id,
3486 organization_id,
3487 plan_level,
3488 sort_order,
3489 operation_seq_num,
3490 component_item_id,
3491 item_num,
3492 component_quantity,
3493 component_yield_factor,
3494 effectivity_date,
3495 implementation_date,
3496 planning_factor,
3497 so_transactions_flag, /** used for quantity_related **/
3498 so_basis,
3499 optional,
3500 mutually_exclusive_options,
3501 include_in_rollup_flag,
3502 check_atp,
3503 required_to_ship,
3504 required_for_revenue,
3505 include_on_ship_docs,
3506 include_on_bill_docs,
3507 low_quantity,
3508 high_quantity,
3509 component_sequence_id,
3510 bill_sequence_id,
3511 wip_supply_type,
3512 pick_components,
3513 bom_item_type,
3514 pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
3515 pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
3516 attribute1,
3517 attribute2,
3521 attribute6,
3518 attribute3,
3519 attribute4,
3520 attribute5,
3522 attribute7,
3523 attribute8,
3524 attribute9,
3525 attribute10,
3526 attribute11,
3527 attribute12,
3528 attribute13,
3529 attribute14,
3530 attribute15
3531 )
3532 VALUES
3533 ( x_top_bill_sequence_id
3534 , l_organization_id
3535 , 1 /* Plan Level */
3536 , '1' /* Sort Order */
3537 , cz_mandatory_items.OPERATION_SEQ_NUM
3538 , cz_mandatory_items.COMPONENT_ITEM_ID
3539 , Decode(cz_mandatory_items.item_num, NULL,
3540 1, cz_mandatory_items.ITEM_NUM)
3541 , round( cz_mandatory_items.COMPONENT_QUANTITY , 7) /* Support Decimal-Qty for Option Items */
3542 , cz_mandatory_items.component_yield_factor /* component_yield_factor */
3543 , cz_mandatory_items.EFFECTIVITY_DATE
3544 , cz_mandatory_items.IMPLEMENTATION_DATE
3545 , 100 /* planning_factor */
3546 , '2' /* quantity_related */
3547 , cz_mandatory_items.SO_BASIS
3548 , cz_mandatory_items.OPTIONAL
3549 , cz_mandatory_items.MUTUALLY_EXCLUSIVE_OPTIONS
3550 , cz_mandatory_items.include_in_cost_rollup
3551 , cz_mandatory_items.CHECK_ATP
3552 , cz_mandatory_items.REQUIRED_TO_SHIP
3553 , cz_mandatory_items.REQUIRED_FOR_REVENUE
3554 , cz_mandatory_items.INCLUDE_ON_SHIP_DOCS
3555 , cz_mandatory_items.INCLUDE_ON_BILL_DOCS
3556 , cz_mandatory_items.LOW_QUANTITY
3557 , cz_mandatory_items.HIGH_QUANTITY
3558 , cz_mandatory_items.component_sequence_id
3559 , X_BILL_SEQUENCE_ID
3560 , decode(cz_mandatory_items.bom_item_type, 2, 6, 1, 6,
3561 nvl(cz_mandatory_items.wip_supply_type,1)) /* wip_supply_type */
3562 , cz_mandatory_items.PICK_COMPONENTS
3563 , cz_mandatory_items.BOM_ITEM_TYPE
3564 , cz_mandatory_items.From_End_Item_Unit_Number
3565 , cz_mandatory_items.To_End_Item_Unit_Number
3566 , cz_mandatory_items.attribute1
3567 , cz_mandatory_items.attribute2
3568 , cz_mandatory_items.attribute3
3569 , cz_mandatory_items.attribute4
3570 , cz_mandatory_items.attribute5
3571 , cz_mandatory_items.attribute6
3572 , cz_mandatory_items.attribute7
3573 , cz_mandatory_items.attribute8
3574 , cz_mandatory_items.attribute9
3575 , cz_mandatory_items.attribute10
3576 , cz_mandatory_items.attribute11
3577 , cz_mandatory_items.attribute12
3578 , cz_mandatory_items.attribute13
3579 , cz_mandatory_items.attribute14
3580 , cz_mandatory_items.attribute15
3581 );
3582
3583 END LOOP;
3584
3585 END IF;
3586
3587
3588 /* Insert all the selected Options along with the component code */
3589
3590 FOR cz_options IN cz_options_chosen
3591 LOOP
3592 INSERT INTO bom_explosion_temp(
3593 top_bill_sequence_id,
3594 organization_id,
3595 plan_level,
3596 sort_order,
3597 operation_seq_num,
3598 component_item_id,
3599 item_num,
3600 component_quantity,
3601 component_yield_factor,
3602 effectivity_date,
3603 implementation_date,
3604 planning_factor,
3605 so_transactions_flag,
3606 so_basis,
3607 optional,
3608 mutually_exclusive_options,
3609 include_in_rollup_flag,
3610 check_atp,
3611 required_to_ship,
3612 required_for_revenue,
3613 include_on_ship_docs,
3614 include_on_bill_docs,
3615 low_quantity,
3616 high_quantity,
3617 component_sequence_id,
3618 bill_sequence_id,
3619 wip_supply_type,
3620 pick_components,
3621 bom_item_type,
3622 pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
3623 pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
3624 attribute1,
3625 attribute2,
3626 attribute3,
3627 attribute4,
3628 attribute5,
3629 attribute6,
3630 attribute7,
3631 attribute8,
3632 attribute9,
3633 attribute10,
3634 attribute11,
3635 attribute12,
3636 attribute13,
3637 attribute14,
3638 attribute15,
3639 component_code
3640 )
3641 VALUES
3642 ( x_top_bill_sequence_id
3643 , l_organization_id
3644 , 1 /* Plan Level */
3645 , '1' /* Sort Order */
3646 , cz_options.OPERATION_SEQ_NUM
3647 , cz_options.COMPONENT_ITEM_ID
3648 , Decode(cz_options.item_num, NULL,
3652 , cz_options.EFFECTIVITY_DATE
3649 1, cz_options.ITEM_NUM)
3650 , cz_options.COMPONENT_QUANTITY
3651 , cz_options.component_yield_factor /* component_yield_factor */
3653 , cz_options.IMPLEMENTATION_DATE
3654 , 100 /* planning_factor */
3655 , '2' /* quantity_related */
3656 , cz_options.SO_BASIS
3657 , cz_options.OPTIONAL
3658 , cz_options.MUTUALLY_EXCLUSIVE_OPTIONS
3659 , cz_options.include_in_cost_rollup
3660 , cz_options.CHECK_ATP
3661 , cz_options.REQUIRED_TO_SHIP
3662 , cz_options.REQUIRED_FOR_REVENUE
3663 , cz_options.INCLUDE_ON_SHIP_DOCS
3664 , cz_options.INCLUDE_ON_BILL_DOCS
3665 , cz_options.LOW_QUANTITY
3666 , cz_options.HIGH_QUANTITY
3667 , cz_options.component_sequence_id
3668 , X_BILL_SEQUENCE_ID
3669 , decode(cz_options.bom_item_type, 2, 6, 1, 6,
3670 nvl(cz_options.wip_supply_type,1)) /* wip_supply_type */
3671 , cz_options.PICK_COMPONENTS
3672 , cz_options.BOM_ITEM_TYPE
3673 , cz_options.From_End_Item_Unit_Number
3674 , cz_options.To_End_Item_Unit_Number
3675 , cz_options.attribute1
3676 , cz_options.attribute2
3677 , cz_options.attribute3
3678 , cz_options.attribute4
3679 , cz_options.attribute5
3680 , cz_options.attribute6
3681 , cz_options.attribute7
3682 , cz_options.attribute8
3683 , cz_options.attribute9
3684 , cz_options.attribute10
3685 , cz_options.attribute11
3686 , cz_options.attribute12
3687 , cz_options.attribute13
3688 , cz_options.attribute14
3689 , cz_options.attribute15
3690 , cz_options.component_code
3691 );
3692
3693 END LOOP;
3694
3695
3696 /** Finished inserting the chosen options **/
3697
3698 /* Proceed to operation sequence number inheritance.
3699
3700 Inheritance will be performed for the following
3701 1. All option classes choosen for the config item (this does not include the option classes
3702 that are directly under the top model. Those should already have the valid op.seq number.
3703 Inheritance starts from the second level. First level components under the top model
3704 will always have the op.seq number.)
3705
3706 2. All the choosen options
3707
3708 The above two are identified by a valid value for the component_code.
3709
3710 3. Mandatory components that are directly under the model will already have the op.seq number
3711
3712 4. At this point we have not yet inserted the mandatory components for the choosen options of this
3713 config item if the inherit op.seq is ON. They will be inserted along with the inherited value.
3714
3715 */
3716
3717 IF l_op_seq_profile = 1
3718 THEN
3719
3720 FOR r1 IN c_Comps_With_ComponentCode
3721 LOOP
3722
3723 IF r1.operation_seq_num = 1 AND Instr(r1.component_code,'-',1,2) <> 0
3724 /* If operation seq number is 1 and the component is not the first level comp. under the top model */
3725 THEN
3726 /* Get the op.seq number from it's immediate parent */
3727 UPDATE bom_explosion_temp btemp
3728 SET btemp.operation_seq_num = (SELECT operation_seq_num FROM
3729 bom_explosion_temp WHERE component_code =
3730 substr(btemp.component_code,1,to_number(instr(btemp.component_code,'-',-1,1))-1))
3731 WHERE component_code = r1.component_code;
3732 END IF;
3733
3734 END LOOP;
3735
3736 END IF;
3737
3738 /* Insert Mandatory Components for the choosen options along with inherited value*/
3739
3740 IF l_op_seq_profile = 1 THEN
3741
3742 FOR cz_mandatory_items IN c_cz_req_items_with_Inherit
3743 LOOP
3744 INSERT INTO bom_explosion_temp(
3745 top_bill_sequence_id,
3746 organization_id,
3747 plan_level,
3748 sort_order,
3749 operation_seq_num,
3750 component_item_id,
3751 item_num,
3752 component_quantity,
3753 component_yield_factor,
3754 effectivity_date,
3755 implementation_date,
3756 planning_factor,
3757 so_transactions_flag, /** used for quantity_related **/
3758 so_basis,
3759 optional,
3760 mutually_exclusive_options,
3761 include_in_rollup_flag,
3762 check_atp,
3763 required_to_ship,
3764 required_for_revenue,
3765 include_on_ship_docs,
3766 include_on_bill_docs,
3767 low_quantity,
3768 high_quantity,
3769 component_sequence_id,
3770 bill_sequence_id,
3771 wip_supply_type,
3772 pick_components,
3773 bom_item_type,
3774 pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
3778 attribute3,
3775 pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
3776 attribute1,
3777 attribute2,
3779 attribute4,
3780 attribute5,
3781 attribute6,
3782 attribute7,
3783 attribute8,
3784 attribute9,
3785 attribute10,
3786 attribute11,
3787 attribute12,
3788 attribute13,
3789 attribute14,
3790 attribute15
3791 )
3792 VALUES
3793 ( x_top_bill_sequence_id
3794 , l_organization_id
3795 , 1 /* Plan Level */
3796 , '1' /* Sort Order */
3797 , decode(cz_mandatory_items.OPERATION_SEQ_NUM,1,
3798 cz_mandatory_items.PARENT_OPERATION_SEQ_NUM,cz_mandatory_items.OPERATION_SEQ_NUM)
3799 , cz_mandatory_items.COMPONENT_ITEM_ID
3800 , Decode(cz_mandatory_items.item_num, NULL,
3801 1, cz_mandatory_items.ITEM_NUM)
3802 , cz_mandatory_items.COMPONENT_QUANTITY
3803 , cz_mandatory_items.component_yield_factor /* component_yield_factor */
3804 , cz_mandatory_items.EFFECTIVITY_DATE
3805 , cz_mandatory_items.IMPLEMENTATION_DATE
3806 , 100 /* planning_factor */
3807 , '2' /* quantity_related */
3808 , cz_mandatory_items.SO_BASIS
3809 , cz_mandatory_items.OPTIONAL
3810 , cz_mandatory_items.MUTUALLY_EXCLUSIVE_OPTIONS
3811 , cz_mandatory_items.include_in_cost_rollup
3812 , cz_mandatory_items.CHECK_ATP
3813 , cz_mandatory_items.REQUIRED_TO_SHIP
3814 , cz_mandatory_items.REQUIRED_FOR_REVENUE
3815 , cz_mandatory_items.INCLUDE_ON_SHIP_DOCS
3816 , cz_mandatory_items.INCLUDE_ON_BILL_DOCS
3817 , cz_mandatory_items.LOW_QUANTITY
3818 , cz_mandatory_items.HIGH_QUANTITY
3819 , cz_mandatory_items.component_sequence_id
3820 , X_BILL_SEQUENCE_ID
3821 , decode(cz_mandatory_items.bom_item_type, 2, 6, 1, 6,
3822 nvl(cz_mandatory_items.wip_supply_type,1)) /* wip_supply_type */
3823 , cz_mandatory_items.PICK_COMPONENTS
3824 , cz_mandatory_items.BOM_ITEM_TYPE
3825 , cz_mandatory_items.From_End_Item_Unit_Number
3826 , cz_mandatory_items.To_End_Item_Unit_Number
3827 , cz_mandatory_items.attribute1
3828 , cz_mandatory_items.attribute2
3829 , cz_mandatory_items.attribute3
3830 , cz_mandatory_items.attribute4
3831 , cz_mandatory_items.attribute5
3832 , cz_mandatory_items.attribute6
3833 , cz_mandatory_items.attribute7
3834 , cz_mandatory_items.attribute8
3835 , cz_mandatory_items.attribute9
3836 , cz_mandatory_items.attribute10
3837 , cz_mandatory_items.attribute11
3838 , cz_mandatory_items.attribute12
3839 , cz_mandatory_items.attribute13
3840 , cz_mandatory_items.attribute14
3841 , cz_mandatory_items.attribute15
3842 );
3843
3844 END LOOP;
3845
3846 END IF;
3847
3848 /*
3849 l_op_seq_profile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
3850
3851 IF l_op_seq_profile = 1
3852 THEN
3853 -- Call procedure to inherit operation sequences
3854
3855 FOR c_model_options IN c_Options_of_Model
3856 LOOP
3857 -- for each option/model under the base model for which atleast one
3858 -- option is chosen, drill down the tree and set the op-seq
3859 Set_Op_Seq
3860 ( p_organization_id => c_model_options.organization_id
3861 , p_component_item_id => c_model_options.component_item_id
3862 , p_operation_seq_num => c_model_options.operation_seq_num
3863 );
3864 END LOOP;
3865 END IF;
3866 */
3867
3868 /** Once the operation inheritance is complete, then consolidate the components
3869 *** based on the same component_item_id and operation_seq_num
3870 **/
3871
3872 Club_Component_Quantities
3873 ( p_bill_sequence_id => x_bill_sequence_id );
3874
3875 Transfer_Comps
3876 ( p_bill_sequence_id => x_bill_sequence_id );
3877
3878 /* Flush the temp table after the process */
3879
3880 DELETE from bom_explosion_temp;
3881
3882 IF (X_Return_Val <> 0 ) THEN
3883 return;
3884 END IF;
3885 -- Commit;
3886 EXCEPTION
3887 WHEN OTHERS THEN
3888 DELETE from bom_explosion_temp;
3889 x_message := 'BOM_CONFIG_EXPLOSIONS_PKG.Insert_Mandatory_Components('
3890 || to_char(X_Stmt_Num) || '):';
3891 FND_MESSAGE.Set_Name('BOM','CZ_PLSQL_ERROR');
3892 FND_MESSAGE.Set_Token('PACKAGE',x_message);
3893 FND_MESSAGE.Set_Token('ORA_ERROR',SQLCODE);
3894 FND_MESSAGE.Set_Token('ORA_TEXT',substr(SQLERRM,1,100));
3895 x_message := FND_MESSAGE.Get;
3896 return;
3897 END BOM_INS_MODEL_AND_MANDATORY;
3898
3899
3900
3901 END CZ_BOM_CONFIG_EXPLOSIONS_PKG;