[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.5.12010000.3 2009/04/08 17:15:13 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,
408 shipping_allowed,
409 required_to_ship,
410 required_for_revenue,
411 include_on_ship_docs,
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
548 -- Update the Component and then reset the local variables
549 UPDATE BOM_EXPLOSION_TEMP
550 SET component_quantity = l_quantity
551 WHERE rowid = l_rowid; /* Changed by Sushant on 19-Aug-2002 */
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
672 ** Parameters : Group_Id
673 ** Bill_Sequence_Id
674 ** Cz_Config_Hdr_Id
675 ** Cz_Config_Rev_Num
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
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
772 -- insert into my_debug_messages values( ' match_profile_on ' || x_match_profile_on ) ;
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);
895
896
897
898 v_ck_line_id number ;
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 -- Begin Bugfix 7446162
916 -- TYPE line_id_tbl_type IS TABLE OF line_id_rec_type INDEX by BINARY_INTEGER ;
917 TYPE line_id_tbl_type IS TABLE OF line_id_rec_type INDEX by long ;
918 -- End Bugfix 7446162
919
920 tab_pci parent_cfg_id_tbl_type; --table of CZ parent cfg id's sparse indexed by line_id
921 tab_li line_id_tbl_type; --table of line id's sparse indexed by CZ cfg id's
922 --end 4440577
923
924
925 BEGIN
926
927 v_step := 'Step 1 ' ;
928
929
930 v_sysdate := sysdate ;
931
932 -- insert into my_debug_messages values ( ' came into process_configurator ' ) ;
933 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' came into process_configurator ' ) ;
934
935 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' latest changes as of 10-APR-2002 in process_configurator ' ) ;
936
937
938
939 gUserId := nvl(Fnd_Global.USER_ID, -1);
940 gLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
941
942 v_step := 'Step 2 ' ;
943
944 lMatchProfile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
945
946 v_step := 'Step 3 ' ;
947
948 /* Temporary statement needs to be fixed */
949 CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG := p_validation_org_id ;
950
951 v_step := 'Step 5 ' ;
952
953 if( lMatchProfile = 1 ) then
954
955 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' Match is ON ' ) ;
956
957 v_perform_match := 'Y' ;
958 x_match_profile_on := 'Y' ;
959 else
960 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' Match is OFF ' ) ;
961 v_perform_match := 'N' ;
962 x_match_profile_on := 'N' ;
963 end if ;
964
965 /* Temporary Statement v_perform_match := 'Y' ;*/
966
967
968 v_step := 'Step 8 ' ;
969
970 /* BUGFIX FOR BUG#2310435 */
971 select assembly_item_id
972 into v_top_model_item_id
973 from bom_bill_of_materials
974 where bill_sequence_id = p_top_bill_sequence_id ;
975
976
977
978 v_step := 'Step 10 ' ;
979
980 open c1 ;
981
982 WHILE(TRUE)
983 LOOP
984
985 config_index := config_data_tab.count + 1 ;
986
987 fetch c1 into config_data_tab(config_index).config_item_id,
988 config_data_tab(config_index).parent_config_item_id,
989 config_data_tab(config_index).inventory_item_id,
990 config_data_tab(config_index).component_code,
991 config_data_tab(config_index).segment1,
992 config_data_tab(config_index).component_item_id,
993 config_data_tab(config_index).component_sequence_id,
994 config_data_tab(config_index).bom_item_type,
995 config_data_tab(config_index).wip_supply_type,
996 config_data_tab(config_index).component_quantity,
997 config_data_tab(config_index).config_orgs,
998 config_data_tab(config_index).config_match,
999 config_data_tab(config_index).uom_code; --4605114
1000
1001 exit when c1%notfound ;
1002
1003
1004 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'cz_c_d '
1005 || ' hdr '|| p_cz_config_hdr_id --bugfix2457660
1006 || ' itm ' || to_char( config_data_tab(config_index).config_item_id)
1007 || ' pci ' || to_char( config_data_tab(config_index).parent_config_item_id )
1008 || ' iid ' || to_char( config_data_tab(config_index).inventory_item_id )
1009 || ' ccd ' || config_data_tab(config_index).component_code
1010 || ' csd ' || to_char( config_data_tab(config_index).component_sequence_id )
1011 || ' bit ' || to_char( config_data_tab(config_index).bom_item_type )
1012 || ' wst ' || to_char( config_data_tab(config_index).wip_supply_type )
1013 || ' qty ' || to_char( config_data_tab(config_index).component_quantity)
1014 || ' behavior ' || config_data_tab(config_index).config_orgs
1015 || ' match ' || config_data_tab(config_index).config_match
1016 || ' uom ' || config_data_tab(config_index).uom_code ) ;--bugfix 4605114
1017
1018
1019
1020
1021 END LOOP ;
1022
1023 close c1 ;
1024
1025
1026
1027 v_step := 'Step 12 ' ;
1028
1029
1030
1031
1032
1033 v_step := 'Step 13 ' ;
1034
1035 for i in 1..config_data_tab.count
1036 loop
1037 bcol_index := bcol_tab.count + 1 ;
1038
1039 --multiplied by -1 to generate -ve sequnnce 'kkonada'
1040 -- Modified by Renga Kannan on 11/19/01
1041 -- Created a new sequence for Bom_cto_order_lines table
1042 -- Generate the line_id column with this new sequence
1043 -- This seqence will be a -ve sequence by itself.
1044 -- The -ve seq is used to avoid converging with om line_id
1045 -- IN the case of Bcol generation for Sales order
1046 -- Line_id is populated with Oe_line_id
1047
1048
1049 select bom_cto_order_lines_s1.nextval
1050 into bcol_tab(bcol_index).line_id
1051 from dual ;
1052 /* Temporary Fix, Need a new sequence for bom_cto_order_lines */
1053
1054 /*
1055 bcol_tab(bcol_index).header_id := config_data_tab(1).parent_config_item_id ;
1056 */
1057
1058
1059
1060 /* BUGFIX FOR BUG#2310435 */
1061 if( config_data_tab(i).inventory_item_id = v_top_model_item_id ) then
1062 v_top_model_index := i ;
1063 end if ;
1064
1065
1066
1067 /* BUGFIX FOR BUG#2310435 */
1068 bcol_tab(bcol_index).header_id := config_data_tab(1).config_hdr_id ;
1069
1070
1071
1072 /* BUGFIX FOR BUG#2310435 */
1073
1074 /*
1075 bcol_tab(bcol_index).ato_line_id := bcol_tab(1).line_id;
1076 bcol_tab(bcol_index).top_model_line_id := bcol_tab(1).line_id ;
1077 */
1078
1079
1080 bcol_tab(bcol_index).inventory_item_id := config_data_tab(i).inventory_item_id ;
1081 bcol_tab(bcol_index).component_code := config_data_tab(i).component_code ;
1082 bcol_tab(bcol_index).component_sequence_id := config_data_tab(i).component_sequence_id ;
1083 bcol_tab(bcol_index).wip_supply_type := config_data_tab(i).wip_supply_type ;
1084
1085 /*
1086 if( bcol_tab(bcol_index).line_id = bcol_tab(bcol_index).ato_line_id ) then
1087 bcol_tab(bcol_index).ordered_quantity := 1 ;
1088 bcol_tab(bcol_index).bom_item_type := '1' ;
1089 bcol_tab(bcol_index).plan_level := 1 ;
1090 bcol_tab(bcol_index).component_code := config_data_tab(i).inventory_item_id;
1091 else
1092 bcol_tab(bcol_index).ordered_quantity := config_data_tab(i).component_quantity ;
1093 bcol_tab(bcol_index).bom_item_type := config_data_tab(i).bom_item_type ;
1094 end if;
1095
1096 */
1097
1098 bcol_tab(bcol_index).ordered_quantity := config_data_tab(i).component_quantity ;
1099 bcol_tab(bcol_index).bom_item_type := config_data_tab(i).bom_item_type ;
1100
1101
1102
1103
1104
1105
1106 bcol_tab(bcol_index).order_quantity_uom := config_data_tab(i).uom_code ; --4605114
1107
1108
1109
1110
1111 bcol_tab(bcol_index).schedule_ship_date := v_sysdate ;
1112 bcol_tab(bcol_index).ship_from_org_id := p_current_org_id ;
1113
1114 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
1115
1116
1117 if( lMatchProfile = 1 ) then
1118 if( l_custom_match_profile = 2 ) then
1119 bcol_tab(bcol_index).perform_match := nvl( config_data_tab(i).config_match , 'Y' ) ;
1120 else
1121 bcol_tab(bcol_index).perform_match := nvl( config_data_tab(i).config_match , 'C' ) ;
1122 end if;
1123 else
1124 bcol_tab(bcol_index).perform_match := 'N' ;
1125 end if;
1126
1127
1128 bcol_tab(bcol_index).config_creation := nvl( config_data_tab(i).config_orgs , 1 ) ;
1129 bcol_tab(bcol_index).option_specific := 'N' ;
1130 bcol_tab(bcol_index).reuse_config := 'N' ;
1131
1132
1133
1134
1135
1136
1137 /* audit columns */
1138 bcol_tab(bcol_index).creation_date := sysdate ;
1139 bcol_tab(bcol_index).created_by := gUserId ;
1140 bcol_tab(bcol_index).last_update_date := sysdate ;
1141 bcol_tab(bcol_index).last_updated_by := gUserId ;
1142 bcol_tab(bcol_index).program_id := CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ;
1143
1144 --bugfix 4440577
1145 --tab_pci index by line_id and store cz parent config_id
1146 tab_pci(bcol_tab(bcol_index).line_id).pcfg_id :=
1147 config_data_tab(i).parent_config_item_id;
1148 --bugfix 4440577
1149 --tab_li index cz config item id and store bcol line id
1150 tab_li(config_data_tab(i).config_item_id).line_id :=
1151 bcol_tab(bcol_index).line_id;
1152
1153
1154
1155 end loop ;
1156
1157
1158 v_step := 'Step 14 ' ;
1159 /* BUGFIX FOR BUG#2310435 */
1160
1161 for myindex in bcol_tab.first..bcol_tab.last
1162 loop
1163
1164 bcol_tab(myindex).ato_line_id := bcol_tab(v_top_model_index).line_id;
1165 bcol_tab(myindex).top_model_line_id := bcol_tab(v_top_model_index).line_id ;
1166
1167 if( bcol_tab(myindex).line_id = bcol_tab(v_top_model_index).ato_line_id ) then
1168 bcol_tab(myindex).ordered_quantity := 1 ;
1169 bcol_tab(myindex).bom_item_type := '1' ;
1170 bcol_tab(myindex).plan_level := 1 ;
1171 bcol_tab(myindex).component_code := config_data_tab(v_top_model_index).inventory_item_id;
1172
1173 end if;
1174
1175 --populate link_to_line_id
1176 --bugfix 4440577
1177 if ( bcol_tab(myindex).line_id = bcol_tab(v_top_model_index).ato_line_id ) then
1178 bcol_tab(myindex).link_to_line_id := null;
1179 else
1180 bcol_tab(myindex).link_to_line_id :=
1181 tab_li( tab_pci(bcol_tab(myindex).line_id).pcfg_id).line_id;
1182
1183
1184 end if;
1185 --end 4440577
1186
1187
1188 end loop ;
1189
1190
1191 v_step := 'Step 15 ' ;
1192 --commented call to link_2_line_id as part of fix4440577
1193 --populate_link_to_line_id( bcol_tab) ;
1194
1195
1196 v_step := 'Step 16 ' ;
1197
1198 v_ato_line_id := bcol_tab(v_top_model_index).line_id ;
1199
1200 x_top_ato_line_id := v_ato_line_id ;
1201
1202
1203 v_step := 'Step 20 ' ;
1204
1205 contiguous_to_sparse_bcol( bcol_tab ) ;
1206
1207 v_step := 'Step 25 ' ;
1208 populate_plan_level(bcol_tab ) ;
1209
1210 v_step := 'Step 30 ' ;
1211
1212 populate_parent_ato(bcol_tab , v_ato_line_id ) ;
1213
1214
1215 v_step := 'Step 31 ' ;
1216
1217
1218 /*
1219 ** CHECK FOR INVALID MODEL SETUP
1220 **
1221 */
1222
1223
1224 i := bcol_tab.first ;
1225 while i is not null
1226 loop
1227 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
1228
1229 if( bcol_tab(bcol_tab(i).parent_ato_line_id).config_creation = 3) then
1230
1231 oe_debug_pub.add('populate_bcol: ' || 'INVALID MODEL SETUP exists for line id ' || bcol_tab(i).line_id
1232 || ' model item ' || bcol_tab(i).inventory_item_id
1233 || ' item type ' || bcol_tab(i).config_creation
1234 , 1 );
1235 oe_debug_pub.add('populate_bcol: ' || ' parent line id ' || bcol_tab(bcol_tab(i).parent_ato_line_id).line_id
1236 || ' parent model item ' || bcol_tab(bcol_tab(i).parent_ato_line_id).inventory_item_id
1237 || ' parent item type ' || bcol_tab(bcol_tab(i).parent_ato_line_id).config_creation
1238 , 1 );
1239
1240
1241
1242 x_message := 'CTO_INVALID_MODEL_SETUP' ;
1243
1244 cto_msg_pub.cto_message('BOM','CTO_INVALID_MODEL_SETUP');
1245 raise FND_API.G_EXC_ERROR;
1246
1247 end if;
1248
1249 end if ;
1250
1251
1252 i := bcol_tab.next(i) ;
1253
1254 end loop ;
1255
1256
1257
1258
1259
1260 /*
1261 ** CALL TRANSFORMED MATCH ATTRIBUTES PENDING
1262 ** PENDING WORK!!!!
1263 */
1264
1265 if( lMatchProfile = 1 ) then
1266 oe_debug_pub.add('populate_bcol: ' || ' preparing information for v_match_flag_tab ' , 3 );
1267 i :=bcol_tab.first ;
1268
1269 while i is not null
1270 loop
1271
1272 if( bcol_tab(i).bom_item_type = 1 and nvl( bcol_tab(i).wip_supply_type , 1 ) <> 6 ) then
1273 v_match_flag_tab(v_match_flag_tab.count + 1).line_id := bcol_tab(i).line_id ;
1274 v_match_flag_tab(v_match_flag_tab.count ).parent_ato_line_id := bcol_tab(i).parent_ato_line_id ;
1275 v_match_flag_tab(v_match_flag_tab.count ).ato_line_id := bcol_tab(i).ato_line_id ;
1276 v_match_flag_tab(v_match_flag_tab.count ).match_flag := bcol_tab(i).perform_match ;
1277
1278 end if;
1279
1280 i := bcol_tab.next(i) ;
1281
1282 end loop ;
1283
1284
1285 oe_debug_pub.add('populate_bcol: ' || ' going to call cto_match_config.evaluate_n_pop_match_flag ' , 3 );
1286
1287 cto_match_config.evaluate_n_pop_match_flag( p_match_flag_tab => v_match_flag_tab
1288 , x_sparse_tab => v_sparse_tab
1289 , x_return_status => x_return_status
1290 , x_msg_count => x_msg_count
1291 , x_msg_data => x_msg_data );
1292
1293
1294
1295
1296
1297 oe_debug_pub.add('populate_bcol: ' || ' populating match flag from results ' , 3 );
1298
1299 i := v_sparse_tab.first ;
1300
1301 while i is not null
1302 loop
1303
1304 bcol_tab(i).perform_match := v_sparse_tab(i).match_flag ;
1305
1306 oe_debug_pub.add('populate_bcol: ' || i || ' match set to ' || bcol_tab(i).perform_match , 3 );
1307
1308
1309
1310 if( bcol_tab(i).line_id = bcol_tab(i).ato_line_id ) then
1311 oe_debug_pub.add('populate_bcol: ' || ' v_perform_match before ' || v_perform_match , 3 );
1312 v_perform_match := bcol_tab(i).perform_match ;
1313 x_match_profile_on := bcol_tab(i).perform_match ;
1314
1315 oe_debug_pub.add('populate_bcol: ' || ' v_perform_match after ' || v_perform_match , 3 );
1316
1317 end if ;
1318 i := v_sparse_tab.next(i) ;
1319
1320 end loop ;
1321
1322 oe_debug_pub.add('populate_bcol: ' || ' done populating match flag from results ' , 3 );
1323
1324
1325 else
1326
1327 oe_debug_pub.add('populate_bcol: ' || ' will not be calling cto_match_config.evaluate_n_pop_match_flag ' , 3 );
1328
1329 end if ;
1330
1331
1332
1333
1334
1335 v_step := 'Step 40 ' ;
1336
1337 insert_into_bcol( bcol_tab ) ;
1338
1339
1340 v_step := 'Step 45 ' ;
1341 if( v_perform_match = 'Y' ) then
1342
1343 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Calling CTOMCFGB perform_match ' ) ;
1344 CTO_MATCH_CONFIG.perform_match( v_ato_line_id ,
1345 x_return_status ,
1346 x_msg_count,
1347 x_msg_data
1348 ) ;
1349
1350 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'done CTOMCFGB perform_match ' ) ;
1351
1352
1353 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1354 perform_match , config_item_id into x_match_found , x_top_matched_item_id
1355 from bom_cto_order_lines_gt
1356 where line_id = v_ato_line_id ;
1357
1358 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'CTOMCFGB perform_match result ' || x_match_found ) ;
1359
1360
1361 if( x_match_found = 'Y' ) then
1362 -- insert into my_debug_messages values ( 'Top Model Match Success ' ) ;
1363 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Top Model Match Success ' ) ;
1364 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Top Match '|| to_char( x_top_matched_item_id ) ) ;
1365
1366 null ;
1367
1368 end if ;
1369
1370 end if ;
1371
1372 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'copying bcolgt to bcol ' ) ;
1373
1374
1375 CTO_UTILITY_PK.copy_bcolgt_bcol( v_ato_line_id , x_return_status, x_msg_count, x_msg_data ) ;
1376
1377
1378 select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
1379 into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
1380 v_ck_config_item_id, v_ck_perform_match
1381 from bom_cto_order_lines
1382 where line_id = v_ato_line_id ;
1383
1384 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' BCOL INFO ' || v_ck_line_id) ;
1385
1386
1387 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' line_id ' || v_ck_line_id) ;
1388 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' ato_line_id ' || v_ck_ato_line_id) ;
1389 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' inventory_item_id ' || v_ck_inventory_item_id) ;
1390 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' config_item_id ' || v_ck_config_item_id ) ;
1391 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' perform_match ' || v_ck_perform_match ) ;
1392
1393
1394
1395
1396
1397
1398
1399 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , 'Done Sucessfully ' ) ;
1400
1401 exception
1402 when others then
1403 V_SQLCODE := SQLCODE ;
1404 V_SQLERRM := SQLERRM ;
1405
1406
1407 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' came into exception at step ' || v_step ) ;
1408 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' exception in process configurator SQL ' || to_char( V_SQLCODE ) ) ;
1409
1410 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'PROCESS_CONFIGURATOR_DATA' , ' exception in process configurator SQL ' || V_SQLERRM ) ;
1411
1412
1413 END process_configurator_data ;
1414
1415
1416 /* Patchset J signature */
1417 procedure create_preconfig_item_ml(
1418 p_use_matched_item in varchar2,
1419 p_match_profile_on in varchar2,
1420 p_top_predefined_item_id in number,
1421 p_top_matched_item_id in number,
1422 p_top_ato_line_id in bom_cto_order_lines.ato_line_id%type,
1423 p_current_org_id in number ,
1424 x_bill_sequence_id out NOCOPY number,
1425 x_mlmo_item_created out NOCOPY varchar2,
1426 x_routing_exists out NOCOPY varchar2,
1427 x_return_status out NOCOPY varchar2,
1428 x_msg_count out NOCOPY number,
1429 x_msg_data out NOCOPY varchar2,
1430 x_t_dropped_items out NOCOPY CTO_CONFIG_BOM_PK.t_dropped_item_type
1431 )
1432 IS
1433 v_step varchar2(20) ;
1434 begin
1435
1436 v_step := 'Step 1 ' ;
1437
1438 create_preconfig_item_ml( p_use_matched_item => p_use_matched_item
1439 , p_match_profile_on => p_match_profile_on
1440 , p_top_predefined_item_id => p_top_predefined_item_id
1441 , p_top_matched_item_id => p_top_matched_item_id
1442 , p_top_ato_line_id => p_top_ato_line_id
1443 , p_current_org_id => p_current_org_id
1444 , x_bill_sequence_id => x_bill_sequence_id
1445 , x_mlmo_item_created => x_mlmo_item_created
1446 , x_routing_exists => x_routing_exists
1447 , x_return_status => x_return_status
1448 , x_msg_count => x_msg_count
1449 , x_msg_data => x_msg_data ) ;
1450
1451 v_step := 'Step 10 ' ;
1452
1453
1454
1455 if x_return_status = FND_API.G_RET_STS_ERROR then
1456
1457 oe_debug_pub.add ('Create_Preconfig_Item_ML:New: ' ||
1458 'Failed in create_preconfig_item_ml with expected error.', 1);
1459
1460 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1461 ':NEW: failed in create_preconfig_item_ml ' || x_msg_data ) ;
1462 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) ) ;
1463 CTO_CONFIG_BOM_PK.get_dropped_components( x_t_dropped_items ) ; -- Fp bug 5485452
1464 raise FND_API.G_EXC_ERROR;
1465
1466 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1467
1468 oe_debug_pub.add ('Create_Preconfig_Item_ML:New: ' ||
1469 'Failed in create_preconfig_item_ml with unexpected error.', 1);
1470
1471 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1472 end if;
1473
1474
1475
1476 v_step := 'Step 15 ' ;
1477
1478 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1479 ':NEW: going to call dropped components ' ) ;
1480
1481 v_step := 'Step 20 ' ;
1482
1483 CTO_CONFIG_BOM_PK.get_dropped_components( x_t_dropped_items ) ;
1484
1485
1486 v_step := 'Step 30 ' ;
1487
1488 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1489 ':NEW: done dropped components ' ) ;
1490
1491 exception
1492 when FND_API.G_EXC_ERROR then
1493 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1494 ':NEW: came into expected exception create_preconfig_item_ml at step ' || v_step ) ;
1495
1496 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1497 ':NEW: came into expected exception create_preconfig_item_ml at step ' || x_msg_data ) ;
1498 x_return_status := FND_API.G_RET_STS_ERROR;
1499
1500 CTO_MSG_PUB.Count_And_Get
1501 (p_msg_count => x_msg_count
1502 ,p_msg_data => x_msg_data
1503 );
1504
1505
1506
1507
1508
1509 when FND_API.G_EXC_UNEXPECTED_ERROR then
1510 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1511 ':NEW: came into unexpected exception create_preconfig_item_ml at step ' || v_step ) ;
1512
1513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1514
1515 CTO_MSG_PUB.Count_And_Get
1516 (p_msg_count => x_msg_count
1517 ,p_msg_data => x_msg_data
1518 );
1519
1520
1521 when OTHERS then
1522 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' ,
1523 ':NEW: came into others exception create_preconfig_item_ml at step ' || v_step ) ;
1524
1525 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1526
1527
1528 CTO_MSG_PUB.Count_And_Get
1529 (p_msg_count => x_msg_count
1530 ,p_msg_data => x_msg_data
1531 );
1532
1533
1534 end create_preconfig_item_ml ;
1535
1536
1537
1538 procedure create_preconfig_item_ml(
1539 p_use_matched_item in varchar2,
1540 p_match_profile_on in varchar2,
1541 p_top_predefined_item_id in number,
1542 p_top_matched_item_id in number,
1543 p_top_ato_line_id in bom_cto_order_lines.ato_line_id%type,
1544 p_current_org_id in number ,
1545 x_bill_sequence_id out NOCOPY number,
1546 x_mlmo_item_created out NOCOPY varchar2,
1547 x_routing_exists out NOCOPY varchar2,
1548 x_return_status out NOCOPY varchar2,
1549 x_msg_count out NOCOPY number,
1550 x_msg_data out NOCOPY varchar2
1551 )
1552 IS
1553 lStatus integer ;
1554 XReturnStatus varchar2(1) ;
1555 XMsgCount number ;
1556 XMsgData varchar2(1000) ;
1557 v_step varchar2(100) ;
1558 v_flow_calc number ;
1559 v_ship_from_org_id number ;
1560
1561
1562 l_x_error_msg varchar2(200 ) ;
1563 l_x_msg_name varchar2(200) ;
1564
1565 lPerformMatch varchar2(1) ;
1566 cursor c_can_configurations is
1567 select line_id, inventory_item_id , parent_ato_line_id , perform_match
1568 from bom_cto_order_lines
1569 where bom_item_type = '1'
1570 and ato_line_id = p_top_ato_line_id
1571 and nvl(wip_supply_type,0) <> 6
1572 order by plan_level desc;
1573
1574 gUserId number ;
1575 gLoginId number ;
1576
1577 v_appl_name varchar2(20) ;
1578 v_error_name varchar2(20) ;
1579
1580 -- start fix 2394597
1581 l_top_model_id number;
1582 lprogram_id number;
1583 lconfig_item_id number;
1584 lValidationOrg number;
1585 licg_id number;
1586 -- end fix 2394597
1587
1588
1589 MATCHED_ITEM_BOM_NOT_FOUND exception ;
1590
1591 v_ck_line_id number ;
1592 v_ck_ato_line_id number ;
1593 v_ck_inventory_item_id number ;
1594 v_ck_config_item_id number ;
1595 v_ck_perform_match varchar2(10) ;
1596
1597
1598 v_dropped_count number := 0 ;
1599
1600 begin
1601
1602 gUserId := nvl(Fnd_Global.USER_ID, -1) ;
1603 gLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
1604
1605 x_msg_data := null ;
1606
1607
1608 v_step := 'Step 1 ' ;
1609
1610 -- insert into my_debug_messages values ( ' came into create_preconfig_item_ml ') ;
1611 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into create_preconfig_item_ml ') ;
1612
1613
1614
1615 select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
1616 into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
1617 v_ck_config_item_id, v_ck_perform_match
1618 from bom_cto_order_lines
1619 where line_id = p_top_ato_line_id ;
1620
1621 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' line_id ' || v_ck_line_id) ;
1622 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' ato_line_id ' || v_ck_ato_line_id) ;
1623 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' inventory_item_id ' || v_ck_inventory_item_id) ;
1624 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' config_item_id ' || v_ck_config_item_id ) ;
1625 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' perform_match ' || v_ck_perform_match ) ;
1626
1627 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' use_matched_item ' || p_use_matched_item ) ;
1628 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' top_predefined_item_id ' || p_top_predefined_item_id ) ;
1629 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' top_matched_item_id ' || p_top_matched_item_id ) ;
1630 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' p_match_profile_on ' || p_match_profile_on ) ;
1631
1632
1633
1634 if( p_use_matched_item = 'N' and p_match_profile_on = 'Y' ) then
1635
1636 -- insert into my_debug_messages values ( ' user has not opted to use matched item') ;
1637 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' user has not opted to use matched item') ;
1638
1639 update bom_cto_order_lines set config_item_id = p_top_predefined_item_id
1640 , perform_match = 'U' where line_id = p_top_ato_line_id ;
1641
1642 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1643 bom_cto_order_lines_gt
1644 set config_item_id = p_top_predefined_item_id
1645 , perform_match = 'U'
1646 where line_id = p_top_ato_line_id ;
1647
1648
1649
1650 elsif( p_use_matched_item = 'N' ) then
1651
1652
1653 update bom_cto_order_lines set config_item_id = p_top_predefined_item_id
1654 , perform_match = 'N' where line_id = p_top_ato_line_id ;
1655
1656 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1657 bom_cto_order_lines_gt
1658 set config_item_id = p_top_predefined_item_id
1659 , perform_match = 'N'
1660 where line_id = p_top_ato_line_id ;
1661
1662
1663
1664
1665 else
1666
1667 -- insert into my_debug_messages values ( ' user has opted to use matched item') ;
1668 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' user has opted to use matched item') ;
1669
1670 null ;
1671
1672
1673 end if;
1674
1675 v_step := 'Step 5 ' ;
1676
1677 -- insert into my_debug_messages values ( ' going to call populate_src_orgs ') ;
1678 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' going to call populate_src_orgs ') ;
1679
1680 -- insert into my_debug_messages values ( ' going to call populate_src_orgs ' || to_char( p_top_ato_line_id ) ) ;
1681 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' going to call populate_src_orgs ' || to_char( p_top_ato_line_id ) ) ;
1682
1683
1684
1685
1686 /*
1687 lStatus := CTO_MSUTIL_PUB.Populate_Src_Orgs(p_top_ato_line_id,
1688 x_return_status,
1689 x_msg_count,
1690 x_msg_data);
1691
1692 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , x_msg_data ) ;
1693 FND_MESSAGE.parse_encoded( x_msg_data, v_appl_name, v_error_name ) ;
1694
1695 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML ' , v_appl_name || ' err ' || v_error_name ) ;
1696
1697 if( x_return_status = FND_API.G_RET_STS_ERROR ) then
1698
1699
1700 RAISE FND_API.G_EXC_ERROR;
1701
1702
1703 elsif( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1705 end if ;
1706
1707
1708 -- insert into my_debug_messages values ( ' returned from populate_src_orgs ') ;
1709 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' returned from populate_src_orgs ') ;
1710
1711
1712 */
1713
1714
1715
1716
1717 -- start 2394597
1718
1719 v_step := 'Step 9 ' ;
1720
1721 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' copying model catalog group id from validation org ') ;
1722
1723 oe_debug_pub.add ('Getting top model item id.. ', 1) ;
1724
1725 -- Following statement will select top model item id.
1726 -- Since only one row is expected to be returned this is implemented
1727 -- as SELECT statement instead of using a CURSOR for multi-row
1728
1729 select inventory_item_id , nvl(program_id,0) ,config_item_id
1730 into l_top_model_id,lprogram_id,lconfig_item_id
1731 from bom_cto_order_lines
1732 where line_id=p_top_ato_line_id ;
1733
1734 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);
1735
1736
1737 oe_debug_pub.add ('Getting Validation Org.. ', 1) ;
1738
1739
1740 if lprogram_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID then
1741 lValidationOrg := CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG ;
1742 end if;
1743
1744
1745 oe_debug_pub.add ('Validation Org : '||to_char(lValidationOrg), 2) ;
1746
1747 -- Following statement will select item_catalog_grp_id
1748 -- of top model item id in validation org.
1749
1750 select nvl(item_catalog_group_id,0)
1751 into licg_id
1752 from mtl_system_items
1753 where inventory_item_id = l_top_model_id
1754 and organization_id = lValidationOrg;
1755
1756 oe_debug_pub.add ('Item Catalog Group Id : '||to_char(licg_id), 2) ;
1757
1758 -- If the model item has catalog , that catalog group id
1759 -- is copied to preconfig item in all org
1760
1761 if licg_id <> 0 then
1762 update mtl_system_items
1763 set item_catalog_group_id = licg_id
1764 where inventory_item_id = lconfig_item_id
1765 and nvl(item_catalog_group_id,0) = 0; --Bugfix 6043798
1766 oe_debug_pub.add ('Updated catalog group id of preconfig item ' , 2) ;
1767 end if;
1768
1769 -- end fix 2394597
1770
1771
1772
1773 v_step := 'Step 10 ' ;
1774
1775 /*
1776 lStatus := CTO_ITEM_PK.create_all_items( p_top_ato_line_id ,XReturnStatus, XMsgCount, XMsgData ) ;
1777 */
1778
1779
1780
1781
1782
1783
1784 select line_id, ato_line_id, inventory_item_id, config_item_id , perform_match
1785 into v_ck_line_id, v_ck_ato_line_id, v_ck_inventory_item_id,
1786 v_ck_config_item_id, v_ck_perform_match
1787 from bom_cto_order_lines
1788 where line_id = p_top_ato_line_id ;
1789
1790 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' BEFORE ITEM CHECK BCOL ' || v_ck_line_id) ;
1791 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' line_id ' || v_ck_line_id) ;
1792 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' ato_line_id ' || v_ck_ato_line_id) ;
1793 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' inventory_item_id ' || v_ck_inventory_item_id) ;
1794 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' config_item_id ' || v_ck_config_item_id ) ;
1795 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' perform_match ' || v_ck_perform_match ) ;
1796
1797 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' use_matched_item ' || p_use_matched_item ) ;
1798 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' top_predefined_item_id ' || p_top_predefined_item_id ) ;
1799 v_step := 'Step 11 ' ; -- Added as a part of bug 8305535
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811 lStatus := CTO_ITEM_PK.Create_And_Link_Item(p_top_ato_line_id,
1812 xReturnStatus ,
1813 xMsgCount ,
1814 xMsgData ,
1815 'PRECONFIG' ) ;
1816
1817
1818
1819 IF lStatus <> 1 then
1820 oe_debug_pub.add ('Create_All_Items returned with 0', 1) ;
1821 oe_debug_pub.add ('Create_All_Items returned with 0' || xMsgData , 1) ;
1822 oe_debug_pub.add ('Create_All_Items returned with 0' || to_char(xMsgCount) , 1) ;
1823
1824 x_msg_count := xMsgCount ;
1825 x_msg_data := xMsgData ;
1826
1827 -- cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
1828
1829 raise FND_API.G_EXC_ERROR;
1830
1831 end if;
1832
1833
1834
1835 -- insert into my_debug_messages values ( ' returned from CTO_ITEM_PK.create_all_items ') ;
1836 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' returned from CTO_ITEM_PK.create_all_items ') ;
1837
1838
1839
1840 v_step := 'Step 15 ' ;
1841
1842 begin
1843
1844 x_routing_exists := 'N' ;
1845
1846 v_step := 'Step 16 ' ;
1847
1848 select 'Y' into x_routing_exists
1849 from dual
1850 where EXISTS (
1851 select routing_sequence_id
1852 from bom_operational_routings bor, bom_cto_src_orgs bcso
1853 where bor.assembly_item_id = bcso.config_item_id
1854 and bor.organization_id = bcso.organization_id
1855 and bor.alternate_routing_designator is null
1856 and bcso.line_id = p_top_ato_line_id
1857 and bcso.create_bom = 'Y'
1858 ) ;
1859
1860
1861 exception
1862 when no_data_found then
1863 x_routing_exists := 'N' ;
1864
1865 when others then
1866
1867 cto_wip_workflow_api_pk.cto_debug( 'CREATE_PRECONFIG_ITEM_ML' ,
1868 ' error in checking if routing exists ') ;
1869
1870 raise fnd_api.g_exc_unexpected_error ;
1871 end ;
1872
1873
1874
1875
1876
1877 v_dropped_count := CTO_CONFIG_BOM_PK.get_dit_count ;
1878
1879 cto_wip_workflow_api_pk.cto_debug('CTO_PRECONFIG_ITEM_ML' , ' dropped count ' || to_char(v_dropped_count) );
1880
1881
1882 if( v_dropped_count > 0 ) then
1883 cto_config_bom_pk.reset_dropped_components ;
1884 end if ;
1885
1886
1887 v_dropped_count := CTO_CONFIG_BOM_PK.get_dit_count ;
1888
1889
1890 cto_wip_workflow_api_pk.cto_debug('CTO_PRECONFIG_ITEM_ML' , ' dropped count ' || to_char(v_dropped_count) );
1891
1892
1893
1894
1895 v_step := 'Step 20 ' ;
1896
1897
1898 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' before create bom error message ' || x_msg_data ) ;
1899
1900 CTO_BOM_RTG_PK.create_all_boms_and_routings( p_top_ato_line_id,
1901 v_flow_calc,
1902 x_return_status,
1903 x_msg_count,
1904 x_msg_data);
1905
1906 v_step := 'Step 21 ' ;
1907
1908
1909 -- insert into my_debug_messages values( ' error message ' || x_msg_data ) ;
1910 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' error message ' || x_msg_data ) ;
1911
1912 v_step := 'Step 22 ' ;
1913
1914 if( x_return_status = FND_API.G_RET_STS_ERROR ) then
1915 RAISE FND_API.G_EXC_ERROR ;
1916 elsif( x_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
1917 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1918 end if ;
1919
1920 -- insert into my_debug_messages values ( ' returned from create_all_boms_and_routings ') ;
1921 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' returned from create_all_boms_and_routings ') ;
1922
1923
1924 v_step := 'Step 30 ' ;
1925
1926
1927
1928 if( p_match_profile_on = 'Y' and p_use_matched_item = 'N' ) then
1929
1930
1931 if( p_top_matched_item_id is not null ) then
1932
1933
1934 v_step := 'Step 32 ' ;
1935 delete from bom_ato_configurations where config_item_id =
1936 p_top_matched_item_id ;
1937
1938 v_step := 'Step 33 ' ;
1939
1940 -- insert into my_debug_messages values( ' delete top matched item id from bom_ato_configurations for item ' || to_char( p_top_matched_item_id ) ) ;
1941 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 ) ) ;
1942
1943
1944 end if ;
1945
1946
1947
1948 /*
1949 for lNextRec in c_can_configurations
1950 loop
1951
1952 v_step := 'Step 34 ' ;
1953
1954 -- insert into my_debug_messages values ( ' fetched ' || to_char(lNextRec.line_id) ) ;
1955 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' fetched ' || to_char(lNextRec.line_id) ) ;
1956
1957 v_step := 'Step 34b ' ;
1958
1959 select perform_match
1960 into lPerformMatch
1961 from bom_cto_order_lines
1962 where line_id = lNextRec.line_id;
1963
1964
1965 if( lPerformMatch = 'N' ) then
1966
1967 v_step := 'Step 35 ' ;
1968 lStatus := CTO_MATCH_CONFIG.can_configurations(
1969 lNextRec.line_id,
1970 0,
1971 0,
1972 0,
1973 gUserId,
1974 gLoginId,
1975 l_x_error_msg,
1976 l_x_msg_name);
1977
1978
1979 -- insert into my_debug_messages values ( ' canned configuration ' || to_char( lNextRec.line_id ) ) ;
1980 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' canned configuration ' || to_char( lNextRec.line_id ) ) ;
1981
1982 oe_debug_pub.add(
1983 'Returned from canning in stmt num 110 with status '
1984 || to_char(lStatus), 1);
1985
1986 if (lStatus <> 1) then
1987
1988
1989 v_step := 'Step 40 ' ;
1990
1991 raise fnd_api.g_exc_unexpected_error;
1992
1993 end if; -- end lStatus <> 1
1994
1995
1996
1997
1998 end if ; * if lPerformMatch *
1999 end loop ;
2000 */
2001
2002
2003 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' canning has been outsourced to CTO_ITEM_PK ' ) ;
2004
2005
2006
2007 end if ; /* match_profile_on */
2008
2009
2010 v_step := 'Step 45 ' ;
2011
2012 if( p_use_matched_item = 'Y' and p_match_profile_on = 'Y' ) then
2013
2014
2015 begin
2016
2017 select bill_sequence_id into x_bill_sequence_id
2018 from bom_bill_of_materials
2019 where assembly_item_id = p_top_matched_item_id
2020 and organization_id = p_current_org_id ;
2021
2022 exception
2023 when no_data_found then
2024 raise MATCHED_ITEM_BOM_NOT_FOUND ;
2025
2026 when others then
2027 raise;
2028
2029 end;
2030
2031
2032
2033 v_step := 'Step 47 ' ;
2034
2035 -- insert into my_debug_messages values( 'matched bill_sequence_id is ' || to_char( x_bill_sequence_id ) ) ;
2036 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , 'matched bill_sequence_id is ' || to_char( x_bill_sequence_id ) ) ;
2037
2038 end if ;
2039
2040
2041 v_step := 'Step 50 ' ;
2042
2043
2044 begin
2045
2046
2047
2048 select decode( count(*) , 0 , 'N' , 'Y' ) into x_mlmo_item_created
2049 from bom_cto_src_orgs
2050 where organization_id <> nvl(rcv_org_id, organization_id)
2051 and top_model_line_id = p_top_ato_line_id ;
2052
2053
2054
2055 v_step := 'Step 55 ' ;
2056
2057 -- insert into my_debug_messages values ( ' mlmo item has been created ' || x_mlmo_item_created );
2058 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' mlmo item has been created ' || x_mlmo_item_created );
2059
2060 exception
2061 when no_data_found then
2062
2063 v_step := 'Step 56 ' ;
2064 x_mlmo_item_created := 'N' ;
2065 -- insert into my_debug_messages values ( ' mlmo item has not been created ' );
2066 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' mlmo item has not been created ' );
2067
2068 when others then
2069 raise ;
2070
2071 end ;
2072
2073
2074
2075
2076
2077
2078 v_step := 'Step 60 ' ;
2079
2080 delete from bom_cto_order_lines
2081 where ato_line_id = p_top_ato_line_id ;
2082
2083 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' deleted bcol ' || to_char(sql%rowcount)) ;
2084
2085 delete from bom_cto_src_orgs_b
2086 where top_model_line_id = p_top_ato_line_id ;
2087
2088 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' deleted bcso_b ' || to_char(sql%rowcount)) ;
2089
2090
2091
2092 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' exiting create_preconfig_item_ml ') ;
2093
2094
2095
2096 -- This is a wrapper API to call PLM team's to sync up item media index
2097 -- With out this sync up the item cannot be searched in Simple item search page
2098 -- Bug 6033399 (FP 6034006)
2099 CTO_MSUTIL_PUB.syncup_item_media_index;
2100 --Start Bugfix 8305535
2101 --calling RAISE EVENT to push items into seibel
2102 CTO_MSUTIL_PUB.Raise_event_for_seibel;
2103 --End Bugfix 8305535
2104 exception
2105
2106 when MATCHED_ITEM_BOM_NOT_FOUND then
2107
2108 x_return_status := FND_API.G_RET_STS_ERROR;
2109 x_msg_data := 'CTO_MATCHED_BOM_NOT_FOUND' ;
2110
2111 CTO_MSG_PUB.Count_And_Get
2112 (p_msg_count => x_msg_count
2113 ,p_msg_data => x_msg_data
2114 );
2115
2116
2117
2118
2119
2120 when FND_API.G_EXC_ERROR then
2121 -- insert into my_debug_messages values ( ' came into expected exception create_preconfig_item_ml at step ' || v_step ) ;
2122 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || v_step ) ;
2123
2124 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || x_msg_data ) ;
2125 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) ) ;
2126
2127
2128 x_return_status := FND_API.G_RET_STS_ERROR;
2129
2130 CTO_MSG_PUB.Count_And_Get
2131 (p_msg_count => x_msg_count
2132 ,p_msg_data => x_msg_data
2133 );
2134
2135 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into expected exception create_preconfig_item_ml at step ' || x_msg_data ) ;
2136 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) ) ;
2137
2138
2139
2140 -- x_msg_data := FND_MESSAGE.GET ;
2141
2142 when FND_API.G_EXC_UNEXPECTED_ERROR then
2143 -- insert into my_debug_messages values ( ' came into unexpected exception create_preconfig_item_ml at step ' || v_step ) ;
2144 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into unexpected exception create_preconfig_item_ml at step ' || v_step ) ;
2145
2146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147
2148 CTO_MSG_PUB.Count_And_Get
2149 (p_msg_count => x_msg_count
2150 ,p_msg_data => x_msg_data
2151 );
2152
2153
2154 when OTHERS then
2155 -- insert into my_debug_messages values ( ' came into others exception create_preconfig_item_ml at step ' || v_step ) ;
2156 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( 'CREATE_PRECONFIG_ITEM_ML' , ' came into others exception create_preconfig_item_ml at step ' || v_step ) ;
2157
2158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2159
2160
2161 CTO_MSG_PUB.Count_And_Get
2162 (p_msg_count => x_msg_count
2163 ,p_msg_data => x_msg_data
2164 );
2165
2166
2167
2168 end create_preconfig_item_ml ;
2169
2170
2171
2172
2173 procedure contiguous_to_sparse_bcol(
2174 p_t_bcol in out NOCOPY bcol_tbl_type
2175 )
2176 is
2177 p_t_sparse_bcol bcol_tbl_type ;
2178 v_line_id number ;
2179
2180 begin
2181 for i in 1..p_t_bcol.count
2182 loop
2183 p_t_sparse_bcol(i) := p_t_bcol(i) ;
2184 end loop ;
2185
2186 p_t_bcol.delete ;
2187
2188 for i in 1..p_t_sparse_bcol.count
2189 loop
2190 p_t_bcol(p_t_sparse_bcol(i).line_id) := p_t_sparse_bcol(i) ;
2191
2192 v_line_id := p_t_sparse_bcol(i).line_id ;
2193
2194 -- insert into my_debug_messages values ( 'p_t_sparse' || to_char( v_line_id ) ) ;
2195 end loop ;
2196
2197
2198
2199 end contiguous_to_sparse_bcol ;
2200
2201
2202
2203 procedure sparse_to_contiguous_bcol(
2204 p_t_bcol in out NOCOPY bcol_tbl_type
2205 )
2206 is
2207 p_t_plain_bcol bcol_tbl_type ;
2208 i number ;
2209 v_line_id number ;
2210 begin
2211 i := p_t_bcol.first ;
2212
2213 while i is not null
2214 loop
2215 p_t_plain_bcol(p_t_plain_bcol.count + 1 ) := p_t_bcol(i) ;
2216 i := p_t_bcol.next(i) ;
2217
2218 v_line_id := p_t_plain_bcol(p_t_plain_bcol.count).line_id ;
2219
2220 -- insert into my_debug_messages values ( 'p_t_plain ' || to_char( v_line_id ) ) ;
2221
2222 end loop ;
2223
2224 p_t_bcol.delete ;
2225
2226 for i in 1..p_t_plain_bcol.count
2227 loop
2228 p_t_bcol(i) := p_t_plain_bcol(i) ;
2229 end loop ;
2230
2231
2232
2233 end sparse_to_contiguous_bcol ;
2234
2235
2236
2237 PROCEDURE populate_parent_ato
2238 ( p_t_bcol in out NOCOPY bcol_tbl_type,
2239 p_bcol_line_id in bom_cto_order_lines.line_id%type )
2240 is
2241 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
2242 v_raw_line_id TABNUM ;
2243 v_src_point number ;
2244 v_prev_src_point number ;
2245 j number ;
2246 v_step VARCHAR2(10) ;
2247 i number := 0 ;
2248
2249 begin
2250
2251 /*
2252 ** Strategy: Resolve parent_ato for each line item by setting it to 1 + plan_level of parent.
2253 ** use the link_to_line_id column to get to the parent. if parents plan_level is not yet
2254 ** resolved, go to its immediate ancestor recursively till you find a line item with
2255 ** plan_level set( Top level plan level is always set to zero ). When coming out of recursion
2256 ** set the plan_level of any ancestors that havent been resolved yet.
2257 ** Implementation: As Pl/Sql does not support Stack built-in-datatype, an equivalent behavior
2258 ** can be achieved by storing items in a table( PUSH implementation) and retrieving them from
2259 ** the end of the table ( POP implmentation [LIFO] )
2260 */
2261
2262 v_step := 'Step C1' ;
2263
2264 i := p_t_bcol.first ;
2265
2266
2267 /* for i in 1..p_t_bcol.last commented for bug 1728383 */
2268
2269 while i is not null
2270 loop
2271
2272 if( p_t_bcol.exists(i) ) then
2273
2274 v_src_point := i ;
2275 /* please note, here it stores the index which is the same as line_id due to sparse array*/
2276
2277 /*
2278 ** resolve parent ato line id for item.
2279 */
2280 v_step := 'Step C2' ;
2281
2282 while( p_t_bcol.exists(v_src_point) )
2283 loop
2284
2285 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
2286 /* store each unresolved item in its heirarchy */
2287
2288 v_prev_src_point := v_src_point ;
2289
2290 v_src_point := p_t_bcol(v_src_point).link_to_line_id ;
2291
2292
2293
2294
2295 if( v_src_point is null or v_prev_src_point = p_bcol_line_id ) then
2296 v_src_point := v_prev_src_point ;
2297
2298 /* break if pto is on top of top level ato or
2299 the current lineid is top level phantom ato
2300 */
2301
2302 exit ;
2303 end if ;
2304 if( p_t_bcol(v_src_point).bom_item_type = '1' AND
2305 p_t_bcol(v_src_point).ato_line_id is not null AND
2306 nvl( p_t_bcol(v_src_point).wip_supply_type , 0 ) <> '6' ) then
2307
2308 exit ;
2309 /* break if non phantom ato parent found */
2310 end if ;
2311
2312
2313
2314 end loop ;
2315
2316 j := v_raw_line_id.count ; /* total number of items to be resolved */
2317
2318 v_step := 'Step C3' ;
2319
2320 while( j >= 1 )
2321 loop
2322
2323 p_t_bcol(v_raw_line_id(j)).parent_ato_line_id := v_src_point ;
2324
2325 j := j -1 ;
2326
2327 end loop ;
2328
2329 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
2330
2331 end if ;
2332
2333
2334
2335 i := p_t_bcol.next(i) ; /* added for bug 1728383 for performance */
2336
2337
2338 end loop ;
2339
2340 exception
2341 when others then
2342 -- insert into my_debug_messages values ( ' came into parent_ato exception at step ' || v_step ) ;
2343 null ;
2344 end populate_parent_ato ;
2345
2346
2347
2348
2349
2350
2351 PROCEDURE populate_plan_level
2352 ( p_t_bcol in out NOCOPY bcol_tbl_type )
2353 is
2354 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
2355 v_raw_line_id TABNUM ;
2356 v_src_point number ;
2357 j number ;
2358 v_step VARCHAR2(10) ;
2359 i number := 0 ;
2360
2361 begin
2362
2363 /*
2364 ** Strategy: Resolve plan_level for each line item by setting it to 1 + plan_level of parent.
2365 ** use the link_to_line_id column to get to the parent. if parents plan_level is not yet
2366 ** resolved, go to its immediate ancestor recursively till you find a line item with
2367 ** plan_level set( Top level plan level is always set to zero ). When coming out of recursion
2368 ** set the plan_level of any ancestors that havent been resolved yet.
2369 ** Implementation: As Pl/Sql does not support Stack built-in-datatype, an equivalent behavior
2370 ** can be achieved by storing items in a table( PUSH implementation) and retrieving them from
2371 ** the end of the table ( POP implmentation [LIFO] )
2372 */
2373
2374 v_step := 'Step B1' ;
2375
2376 i := p_t_bcol.first ;
2377
2378
2379
2380 /* for i in 1..p_t_bcol.last commented for bug 1728383 */
2381
2382
2383 while i is not null
2384 loop
2385
2386 if( p_t_bcol.exists(i) ) then
2387
2388 v_src_point := i ;
2389
2390
2391 /*
2392 ** resolve plan level for item only if not yet resolved
2393 */
2394
2395 while( p_t_bcol(v_src_point).plan_level is null )
2396 loop
2397
2398 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
2399 /* store each unresolved item in its heirarchy */
2400
2401 v_src_point := p_t_bcol(v_src_point).link_to_line_id ;
2402
2403 end loop ;
2404
2405 v_step := 'Step B2' ;
2406
2407 j := v_raw_line_id.count ; /* total number of items to be resolved */
2408
2409 while( j >= 1 )
2410 loop
2411
2412 p_t_bcol(v_raw_line_id(j)).plan_level := p_t_bcol(v_src_point).plan_level + 1;
2413
2414 v_src_point := v_raw_line_id(j) ;
2415
2416 j := j -1 ;
2417 end loop ;
2418
2419 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
2420
2421 end if ;
2422
2423
2424
2425 i := p_t_bcol.next(i) ; /* added for bug 1728383 for performance */
2426
2427
2428 end loop ;
2429
2430 exception
2431 when others then
2432 -- insert into my_debug_messages values ( ' came into plan_level exception at step ' || v_step ) ;
2433 null ;
2434
2435 end populate_plan_level ;
2436
2437
2438
2439 procedure insert_into_bcol (
2440 p_bcol_tab bcol_tbl_type
2441 )
2442 IS
2443 v_step number := 0 ;
2444 v_sqlcode number := 0 ;
2445 i number ;
2446 BEGIN
2447
2448 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' came into insert into bcol: ' , 1 ) ;
2449
2450
2451
2452
2453 if( p_bcol_tab.count = 0 ) then
2454 return ;
2455 end if ;
2456
2457 i := p_bcol_tab.first ;
2458
2459 while i is not null
2460 loop
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 ' qty ' || p_bcol_tab(i).ordered_quantity , 1) ;
2466
2467 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
2468 ' line_id ' || p_bcol_tab(i).line_id ||
2469 ' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
2470 ' parent qty ' || p_bcol_tab(p_bcol_tab(i).parent_ato_line_id ).ordered_quantity , 1) ;
2471 v_step := i ;
2472
2473 Insert into bom_cto_order_lines_gt(
2474 HEADER_ID ,
2475 LINE_ID ,
2476 LINK_TO_LINE_ID ,
2477 ATO_LINE_ID ,
2478 PARENT_ATO_LINE_ID ,
2479 TOP_MODEL_LINE_ID ,
2480 PLAN_LEVEL ,
2481 WIP_SUPPLY_TYPE ,
2482 PERFORM_MATCH ,
2483 BOM_ITEM_TYPE ,
2484 COMPONENT_CODE ,
2485 COMPONENT_SEQUENCE_ID ,
2486 CONFIG_ITEM_ID ,
2487 INVENTORY_ITEM_ID ,
2488 ITEM_TYPE_CODE ,
2489 BATCH_ID ,
2490 ORDERED_QUANTITY ,
2491 ORDER_QUANTITY_UOM ,
2492 SCHEDULE_SHIP_DATE ,
2493 SHIP_FROM_ORG_ID ,
2494 LAST_UPDATE_DATE ,
2495 LAST_UPDATED_BY ,
2496 CREATION_DATE ,
2497 CREATED_BY ,
2498 LAST_UPDATE_LOGIN ,
2499 REQUEST_ID ,
2500 PROGRAM_APPLICATION_ID ,
2501 PROGRAM_ID ,
2502 PROGRAM_UPDATE_DATE ,
2503 qty_per_parent_model,
2504 option_specific ,
2505 reuse_config ,
2506 config_creation )
2507 VALUES (
2508 nvl( p_bcol_tab(i).header_id , 1 ) ,
2509 p_bcol_tab(i).line_id,
2510 p_bcol_tab(i).link_to_line_id,
2511 p_bcol_tab(i).ato_line_id,
2512 p_bcol_tab(i).parent_ato_line_id,
2513 nvl( p_bcol_tab(i).top_model_line_id,1 ),
2514 p_bcol_tab(i).plan_level,
2515 p_bcol_tab(i).wip_supply_type,
2516 p_bcol_tab(i).perform_match,
2517 p_bcol_tab(i).bom_item_type,
2518 p_bcol_tab(i).component_code,
2519 p_bcol_tab(i).component_sequence_id,
2520 p_bcol_tab(i).config_item_id,
2521 p_bcol_tab(i).inventory_item_id,
2522 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' ) )),
2523 p_bcol_tab(i).batch_id,
2524 p_bcol_tab(i).ordered_quantity,
2525 p_bcol_tab(i).order_quantity_uom,
2526 p_bcol_tab(i).schedule_ship_date,
2527 p_bcol_tab(i).ship_from_org_id,
2528 p_bcol_tab(i).last_update_date,
2529 p_bcol_tab(i).last_updated_by,
2530 p_bcol_tab(i).creation_date,
2531 p_bcol_tab(i).created_by ,
2532 p_bcol_tab(i).last_update_login ,
2533 p_bcol_tab(i).request_id ,
2534 p_bcol_tab(i).program_application_id ,
2535 p_bcol_tab(i).program_id ,
2536 p_bcol_tab(i).program_update_date ,
2537 p_bcol_tab(i).ordered_quantity / p_bcol_tab(p_bcol_Tab(i).parent_ato_line_id).ordered_quantity ,
2538 p_bcol_tab(i).option_specific ,
2539 p_bcol_tab(i).reuse_config,
2540 p_bcol_tab(i).config_creation ) ;
2541
2542 CTO_WIP_WORKFLOW_API_PK.CTO_DEBUG( ' insert into bcol: ' ||
2543 ' line_id ' || p_bcol_tab(i).line_id ||
2544 ' parent line_id ' || p_bcol_tab(i).parent_ato_line_id ||
2545 ' ato line_id ' || p_bcol_tab(i).ato_line_id ||
2546 ' bom_item_type ' || nvl( p_bcol_tab(i).bom_item_type, -7) ||
2547 ' wip_supply_type ' || nvl( p_bcol_tab(i).wip_supply_type , -7) ||
2548 ' config_item_id ' || nvl( p_bcol_tab(i).config_item_id , -7) ||
2549 ' count ' || SQL%ROWCOUNT , 2);
2550
2551
2552 i:= p_bcol_tab.next(i) ;
2553
2554 end loop ;
2555
2556 exception
2557 when others then
2558 V_SQLCODE := SQLCODE ;
2559 cto_wip_workflow_api_pk.cto_debug( ' exception in bcol_gt at step ' || to_char( v_step ) , 1 ) ;
2560 cto_wip_workflow_api_pk.cto_debug( ' exception in bcol_gt at step ' || V_SQLCODE , 1 ) ;
2561 END INSERT_INTO_BCOL ;
2562
2563
2564
2565 /*
2566 ** This procedure requires the parameter passed in to be populated with line_id
2567 ** and component_code without which the intended functionality is not guaranteed
2568 */
2569 procedure populate_link_to_line_id(
2570 p_bcol_tab in out NOCOPY bcol_tbl_type
2571 )
2572 is
2573 TYPE varchar2_1000_tbl_type is table of varchar2(1000 ) index by binary_integer ;
2574 v_parent_code_tab varchar2_1000_tbl_type ;
2575 v_loc number :=0 ;
2576 begin
2577
2578
2579 for i in 1..p_bcol_tab.count
2580 loop
2581
2582 v_loc := instr(p_bcol_tab(i).component_code , '-' , -1 ) ;
2583
2584 if( v_loc = 0 ) then
2585 v_parent_code_tab(i) := null ;
2586 else
2587 v_parent_code_tab(i ) := substr( p_bcol_tab(i).component_code , 1 , v_loc - 1 ) ;
2588 end if ;
2589
2590 p_bcol_tab(i).link_to_line_id := null ; /* clear existing data as top model needs null*/
2591 end loop;
2592
2593
2594 for i in 1..v_parent_code_tab.count
2595 loop
2596
2597 for j in 1..p_bcol_tab.count
2598 loop
2599 if( v_parent_code_tab(i) = p_bcol_tab(j).component_code ) then
2600 p_bcol_tab(i).link_to_line_id := p_bcol_tab(j).line_id ;
2601 exit ;
2602 end if ;
2603 end loop ;
2604
2605 end loop ;
2606
2607
2608
2609 end populate_link_to_line_id;
2610
2611
2612
2613
2614 procedure perform_match(
2615 p_ato_line_id in bom_cto_order_lines.ato_line_id%type ,
2616 x_match_found out NOCOPY varchar2,
2617 x_matching_config_id out NOCOPY number,
2618 x_error_message out NOCOPY VARCHAR2,
2619 x_message_name out NOCOPY varchar2
2620 )
2621 is
2622 l_stmt_num number := 0;
2623 l_cfm_value number;
2624 l_config_line_id number;
2625 l_tree_id integer;
2626 l_return_status varchar2(1);
2627 l_x_error_msg_count number;
2628 l_x_error_msg varchar2(240);
2629 l_x_error_msg_name varchar2(30);
2630 l_x_table_name varchar2(30);
2631 l_match_profile varchar2(10);
2632 l_org_id number;
2633 l_model_id number;
2634 l_primary_uom_code varchar(3);
2635 l_x_config_id number;
2636 l_top_model_line_id number;
2637
2638 l_x_qoh number;
2639 l_x_rqoh number;
2640 l_x_qs number;
2641 l_x_qr number;
2642 l_x_att number;
2643 l_active_activity varchar2(30);
2644 l_x_bill_seq_id number;
2645 l_status integer;
2646
2647 l_perform_match varchar2(1) ;
2648
2649 x_return_status varchar2(1);
2650 x_msg_count number;
2651 x_msg_data varchar2(100);
2652
2653 PROCESS_ERROR EXCEPTION;
2654
2655
2656 cursor c_model_lines is
2657 select line_id, parent_ato_line_id
2658 from bom_cto_order_lines
2659 where bom_item_type = '1'
2660 and ato_line_id = p_ato_line_id
2661 and nvl(wip_supply_type,0) <> 6
2662 order by plan_level desc;
2663
2664 v_sqlcode number ;
2665 l_custom_match_profile varchar2(10);
2666 begin
2667
2668 l_stmt_num := 1;
2669
2670 x_match_found := 'N' ;
2671
2672 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
2673
2674 l_stmt_num := 5;
2675
2676 /* for each model */
2677
2678 for l_next_rec in c_model_lines loop
2679
2680 l_x_config_id := NULL;
2681
2682
2683
2684 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2685 perform_match into l_perform_match
2686 from bom_cto_order_lines_gt
2687 where line_id = l_next_rec.line_id ;
2688
2689
2690
2691 if( l_perform_match = 'N' ) then
2692
2693 begin
2694
2695 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2696 bom_cto_order_lines_gt set perform_match = 'N'
2697 where perform_match = 'Y'
2698 and line_id = l_next_rec.parent_ato_line_id ;
2699
2700 exception
2701 when no_data_found then
2702 null ;
2703
2704 end ;
2705
2706 x_match_found := 'N' ;
2707
2708 x_matching_config_id := NULL ; /* fix for bug#2048023. */
2709
2710
2711
2712 else
2713
2714 if ( l_custom_match_profile = 2) then
2715 l_stmt_num := 10;
2716 oe_debug_pub.add('Standard Match.', 1);
2717 l_status := cto_match_config.check_config_match(
2718 l_next_rec.line_id,
2719 l_x_config_id,
2720 l_x_error_msg,
2721 l_x_error_msg_name);
2722
2723 elsif (l_custom_match_profile = 1) then
2724 l_stmt_num := 15;
2725 l_status := CTO_CUSTOM_MATCH_PK.find_matching_config(
2726 l_next_rec.line_id,
2727 l_x_config_id,
2728 l_x_error_msg,
2729 l_x_error_msg_name,
2730 l_x_table_name);
2731 end if;
2732
2733 l_stmt_num := 20;
2734
2735 if (l_status = 0) then
2736 oe_debug_pub.add('Failed in Check Config Match for line id '
2737 || to_char(l_next_rec.line_id), 1);
2738
2739 raise PROCESS_ERROR;
2740
2741 end if;
2742
2743
2744 l_stmt_num := 25;
2745
2746
2747 if (l_status = 1 and l_x_config_id is NULL) then
2748 l_stmt_num := 30;
2749
2750 x_message_name := 'CTO_MR_NO_MATCH';
2751 x_error_message := 'No matching configurations for line '
2752 || to_char(l_next_rec.line_id);
2753 l_stmt_num := 137;
2754
2755 -- insert into my_debug_messages values ( 'No Match found' ) ;
2756 x_match_found := 'N' ;
2757
2758 x_matching_config_id := NULL ; /* fix for bug#2048023. */
2759
2760 /* fix for bug#2048023.
2761 This variable has to be initialized to null as it was not
2762 null for a lower level match in the perform match loop.
2763 */
2764
2765
2766 /* update the perform match column to 'N' so that this item is canned */
2767 begin
2768 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2769 bom_cto_order_lines_gt
2770 set perform_match = 'N'
2771 where line_id = l_next_rec.line_id
2772 and perform_match = 'Y';
2773
2774 exception
2775 when no_data_found then
2776 null ;
2777
2778 end ;
2779
2780
2781
2782 /* update the perform match column to 'N' so that no match
2783 is attempted against its parent and it is canned
2784 */
2785
2786 begin
2787 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2788 bom_cto_order_lines_gt
2789 set perform_match = 'N'
2790 where line_id = l_next_rec.parent_ato_line_id
2791 and perform_match = 'Y';
2792
2793 exception
2794 when no_data_found then
2795 null ;
2796
2797 end ;
2798
2799
2800
2801
2802 elsif (l_status = 1 and l_x_config_id is not null) then
2803
2804 l_stmt_num := 35;
2805
2806 /*
2807 oe_debug_pub.add('Match for line id '
2808 || to_char(l_next_rec.line_id)
2809 || ' is ' || to_char(l_x_config_id) ,1);
2810 */
2811
2812 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2813 bom_cto_order_lines_gt
2814 set config_item_id = l_x_config_id
2815 where line_id = l_next_rec.line_id;
2816
2817 l_stmt_num := 40 ;
2818
2819 x_matching_config_id := l_x_config_id ;
2820
2821 x_match_found := 'Y' ;
2822
2823
2824 l_stmt_num := 45 ;
2825
2826 -- insert into my_debug_messages values ( 'Match found' ) ;
2827 -- insert into my_debug_messages values ( 'Matched Item ' || to_char(x_matching_config_id ) ) ;
2828
2829 end if;
2830
2831
2832 end if ; /* if perform_match = 'N' */
2833
2834
2835 end loop;
2836
2837
2838 exception
2839 when others then
2840 V_SQLCODE := SQLCODE ;
2841 -- insert into my_debug_messages values ( ' exception in match at step ' || to_char( l_stmt_num ) ) ;
2842 -- insert into my_debug_messages values ( ' exception in match SQL ' || to_char( V_SQLCODE ) ) ;
2843
2844 end perform_match ;
2845
2846 /* Copied this procedure as it is from BOMCZCBB.pls 115.9.1155.7 version */
2847 /*******************************************************************************************
2848 ** Procedure : BOM_INS_MODEL_AND_MANDATORY
2849 ** Parameters : Group_Id
2850 ** Bill_Sequence_Id
2851 ** Cz_Config_Hdr_Id
2852 ** Cz_Config_Rev_Num
2853 ** Purpose : This procedure will be called when the configurator Applet Returns after the
2854 ** user has Chosen a Configuration and hit Done. This procedure take the options
2855 ** the user has chosen and the option classes that those options belong to and insert
2856 ** them in a temporary table. Then it will take all the mandatory components that
2857 ** are associated with the option classes from which a user has chosen atleast 1
2858 ** Option and insert the data in a temporary table.
2859 ** Once the required data is gathered under one group id, the process will check if
2860 ** the Profile "BOM:CONFIG_INHERIT_OP_SEQ" is set.If YES then the procedure will
2861 ** loop through the option classes and assign the operation sequence to its children
2862 ** if the children have an op_seq of 1. This process will recursively loop through
2863 ** its children and perform the operation sequence inheritance for all the children.
2864 ** Once the records have been assigned the proper op-seq's the process will then
2865 ** proceed to consolidate the components. Components quantities for components with
2866 ** the same op-seq and component_item_id will be added and only 1 record for that
2867 ** combination will exist and the duplicates will be deleted. The final data will be
2868 ** moved from the temporary table to the production table and the data in the temp
2869 ** table will be cleaned up.
2870 ********************************************************************************************/
2871 PROCEDURE BOM_INS_MODEL_AND_MANDATORY(x_group_id IN NUMBER,
2872 x_bill_sequence_id IN NUMBER,
2873 x_top_bill_sequence_id IN NUMBER,
2874 x_cz_config_hdr_id IN NUMBER,
2875 x_cz_config_rev_num IN NUMBER,
2876 x_message IN OUT NOCOPY VARCHAR2) IS
2877 X_Stmt_Num NUMBER;
2878 X_Return_Val NUMBER;
2879 X_err_message VARCHAR2(2000);
2880 l_op_seq_profile NUMBER := 0;
2881 l_bill_sequence_id NUMBER;
2882 l_organization_id NUMBER;
2883
2884 /* Cursor will select the options that the user had chosen from the option classes
2885 on the Model
2886 */
2887
2888 CURSOR cz_options_chosen IS
2889 SELECT bic.bill_sequence_id,
2890 x_top_bill_sequence_id top_bill_sequence_id,
2891 bic.operation_seq_num,
2892 bic.component_item_id,
2893 bic.last_update_date,
2894 bic.last_updated_by,
2895 bic.creation_date,
2896 bic.created_by,
2897 bic.item_num,
2898 round( to_number(cz.quantity), 7) component_quantity, /* Support Decimal-Qty for Option Items */
2899 bic.component_yield_factor,
2900 bic.effectivity_date,
2901 bic.implementation_date,
2902 bic.planning_factor,
2903 bic.quantity_related,
2904 bic.so_basis,
2905 bic.optional,
2906 bic.mutually_exclusive_options,
2907 bic.include_in_cost_rollup,
2908 bic.check_atp,
2909 bic.required_to_ship,
2910 bic.required_for_revenue,
2911 bic.include_on_ship_docs,
2912 bic.include_on_bill_docs,
2913 bic.low_quantity,
2914 bic.high_quantity,
2915 bic.wip_supply_type,
2916 bic.pick_components,
2917 bic.bom_item_type,
2918 bic.component_sequence_id,
2919 bic.From_End_Item_Unit_Number,
2920 bic.To_End_Item_Unit_Number,
2921 bic.attribute_category,
2922 bic.attribute1,
2923 bic.attribute2,
2924 bic.attribute3,
2925 bic.attribute4,
2926 bic.attribute5,
2927 bic.attribute6,
2928 bic.attribute7,
2929 bic.attribute8,
2930 bic.attribute9,
2931 bic.attribute10,
2932 bic.attribute11,
2933 bic.attribute12,
2934 bic.attribute13,
2935 bic.attribute14,
2936 bic.attribute15,
2937 cz.component_code
2938 FROM bom_inventory_components bic
2939 , cz_config_details_v cz
2940 WHERE bic.bom_item_type NOT IN('1', '2')
2941 AND bic.component_sequence_id = cz.component_sequence_id
2942 AND cz.config_hdr_id = x_cz_config_hdr_id
2943 AND cz.config_rev_nbr = x_cz_config_rev_num;
2944
2945 /* Mandatory Components for the all the options classes in which user has chosen
2946 atleast 1 option (this is used when the operation sequence inheritance is OFF)
2947 */
2948 CURSOR c_cz_required_items IS
2949 SELECT bic.bill_sequence_id,
2950 x_top_bill_sequence_id,
2951 bic.operation_seq_num,
2952 bic.component_item_id,
2953 bic.last_update_date,
2954 bic.last_updated_by,
2955 bic.creation_date,
2956 bic.created_by,
2957 bic.item_num,
2958 round( (bic.component_quantity * to_number(cz.quantity)), 7) component_quantity, /* Support Decimal-Qty for Option Items */
2959 bic.component_yield_factor,
2960 bic.effectivity_date,
2961 bic.implementation_date,
2962 bic.planning_factor,
2963 bic.quantity_related,
2964 bic.so_basis,
2965 bic.optional,
2966 bic.mutually_exclusive_options,
2967 bic.include_in_cost_rollup,
2968 bic.check_atp,
2969 bic.required_to_ship,
2970 bic.required_for_revenue,
2971 bic.include_on_ship_docs,
2972 bic.include_on_bill_docs,
2973 bic.low_quantity,
2974 bic.high_quantity,
2975 bic.wip_supply_type,
2976 bic.pick_components,
2977 bic.bom_item_type,
2978 bic.component_sequence_id,
2979 bic.From_End_Item_Unit_Number,
2980 bic.To_End_Item_Unit_Number,
2981 bic.attribute_category,
2982 bic.attribute1,
2983 bic.attribute2,
2984 bic.attribute3,
2985 bic.attribute4,
2986 bic.attribute5,
2987 bic.attribute6,
2988 bic.attribute7,
2989 bic.attribute8,
2990 bic.attribute9,
2991 bic.attribute10,
2992 bic.attribute11,
2993 bic.attribute12,
2994 bic.attribute13,
2995 bic.attribute14,
2996 bic.attribute15
2997 FROM bom_inventory_components bic
2998 , bom_inventory_components mod_oc
2999 , bom_bill_of_materials bom
3000 , cz_config_details_v cz
3001 WHERE cz.config_hdr_id = x_cz_config_hdr_id
3002 AND cz.config_rev_nbr = x_cz_config_rev_num
3003 AND mod_oc.component_sequence_id = cz.component_sequence_id
3004 AND mod_oc.bom_item_type IN (1,2)
3005 AND bom.assembly_item_id = mod_oc.component_item_id
3006 AND bom.organization_id = cz.organization_id
3007 AND bom.alternate_bom_designator IS NULL
3008 AND bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, bom.bill_sequence_id,
3009 bom.bill_sequence_id, bom.common_bill_sequence_id
3010 )
3011 AND bic.optional = 2
3012 AND bic.bom_item_type NOT IN (1,2)
3013 AND bic.effectivity_date <= SYSDATE
3014 AND nvl(bic.disable_date,SYSDATE+1) > SYSDATE;
3015
3016 /* Mandatory Components for the all the options classes in which user has chosen
3017 atleast 1 option (this is used for operation sequence inheritance from the parent)
3018 Additional table used here is bom_explosion_temp and the join to cz_config_details_v
3019 has been made using the component_code
3020 */
3021
3022 CURSOR c_cz_req_items_with_Inherit IS
3023 SELECT bic.bill_sequence_id,
3024 x_top_bill_sequence_id,
3025 bic.operation_seq_num,
3026 bet.operation_seq_num parent_operation_seq_num,
3027 bic.component_item_id,
3028 bic.last_update_date,
3029 bic.last_updated_by,
3030 bic.creation_date,
3031 bic.created_by,
3032 bic.item_num,
3033 round( (bic.component_quantity * to_number(cz.quantity)) , 7) component_quantity, /* Support Decimal-Qty for Option Items */
3034 bic.component_yield_factor,
3035 bic.effectivity_date,
3036 bic.implementation_date,
3037 bic.planning_factor,
3038 bic.quantity_related,
3039 bic.so_basis,
3040 bic.optional,
3041 bic.mutually_exclusive_options,
3042 bic.include_in_cost_rollup,
3043 bic.check_atp,
3044 bic.required_to_ship,
3045 bic.required_for_revenue,
3046 bic.include_on_ship_docs,
3047 bic.include_on_bill_docs,
3048 bic.low_quantity,
3049 bic.high_quantity,
3050 bic.wip_supply_type,
3051 bic.pick_components,
3052 bic.bom_item_type,
3053 bic.component_sequence_id,
3054 bic.From_End_Item_Unit_Number,
3055 bic.To_End_Item_Unit_Number,
3056 bic.attribute_category,
3057 bic.attribute1,
3058 bic.attribute2,
3059 bic.attribute3,
3060 bic.attribute4,
3061 bic.attribute5,
3062 bic.attribute6,
3063 bic.attribute7,
3064 bic.attribute8,
3065 bic.attribute9,
3066 bic.attribute10,
3067 bic.attribute11,
3068 bic.attribute12,
3069 bic.attribute13,
3070 bic.attribute14,
3071 bic.attribute15
3072 FROM bom_inventory_components bic
3073 , bom_inventory_components mod_oc
3074 , bom_bill_of_materials bom
3075 , cz_config_details_v cz
3076 , bom_explosion_temp bet
3077 WHERE cz.config_hdr_id = x_cz_config_hdr_id
3078 AND cz.config_rev_nbr = x_cz_config_rev_num
3079 AND cz.component_code = bet.component_code
3080 AND mod_oc.component_sequence_id = cz.component_sequence_id
3081 AND mod_oc.bom_item_type IN (1,2)
3082 AND bom.assembly_item_id = mod_oc.component_item_id
3083 AND bom.organization_id = cz.organization_id
3084 AND bom.alternate_bom_designator IS NULL
3085 AND bic.bill_sequence_id = DECODE(bom.common_bill_sequence_id, bom.bill_sequence_id,
3086 bom.bill_sequence_id, bom.common_bill_sequence_id
3087 )
3088 AND bic.optional = 2
3089 AND bic.bom_item_type NOT IN (1,2)
3090 AND bic.effectivity_date <= SYSDATE
3091 AND nvl(bic.disable_date,SYSDATE+1) > SYSDATE;
3092
3093 /* Components in the temp table with valid component code . Component code will be available
3094 for the records that are from cz_config_details_v (i.e all the option classes for the
3095 configured item and all the choosen options) */
3096
3097 CURSOR c_Comps_With_ComponentCode IS
3098 SELECT * from bom_explosion_temp WHERE component_code IS NOT NULL
3099 ORDER BY component_code;
3100
3101 /* Option classes that are under the top model */
3102
3103 CURSOR c_Options_of_Model IS
3104 SELECT bic.component_sequence_id
3105 , bic.component_item_id
3106 , bic.operation_seq_num
3107 , cz.organization_id
3108 FROM bom_inventory_components bic,
3109 cz_config_details_v cz
3110 WHERE cz.component_sequence_id = bic.component_sequence_id
3111 AND bic.bom_item_type IN (1,2)
3112 AND bic.bill_sequence_id = l_bill_sequence_id
3113 AND cz.config_hdr_id = x_cz_config_hdr_id
3114 AND cz.config_rev_nbr = x_cz_config_rev_num;
3115
3116
3117 CURSOR c_Club_Comps IS
3118 SELECT bet.bill_sequence_id
3119 , bet.component_item_id
3120 , bet.operation_seq_num
3121 , bet.component_sequence_id
3122 FROM bom_explosion_temp bet
3123 WHERE bill_sequence_id = x_bill_sequence_id
3124 ORDER BY bet.bill_sequence_id,
3125 bet.component_item_id,
3126 bet.operation_seq_num,
3127 bet.component_sequence_id;
3128
3129
3130 BEGIN
3131
3132 --
3133 -- Check if the Top Model being defined is using some model as common. If it is then
3134 -- we need to use the bill_sequence_id of the other model when find the bill structure
3135 --
3136
3137 SELECT DECODE(common_bill_sequence_id, x_top_bill_sequence_id,
3138 bill_sequence_id, common_bill_sequence_id
3139 ),
3140 DECODE(nvl(common_organization_id, organization_id), organization_id,
3141 organization_id, common_organization_id)
3142 INTO l_bill_sequence_id,
3143 l_organization_id
3144 FROM bom_bill_of_materials
3145 WHERE bill_sequence_id = x_top_bill_sequence_id;
3146
3147 X_Stmt_Num := 10;
3148
3149 /* Flush the temp table before starting the process */
3150
3151 DELETE from bom_explosion_temp;
3152
3153 /* Insert Model */
3154
3155 INSERT INTO BOM_INVENTORY_COMPONENTS
3156 (
3157 bill_sequence_id,
3158 component_sequence_id,
3159 component_item_id,
3160 creation_date,
3161 created_by,
3162 last_update_date,
3163 last_updated_by,
3164 attribute1,
3165 attribute2,
3166 attribute3,
3167 attribute4,
3168 attribute5,
3169 attribute6,
3170 attribute7,
3171 attribute8,
3172 attribute9,
3173 attribute10,
3174 attribute11,
3175 attribute12,
3176 attribute13,
3177 attribute14,
3178 attribute15,
3179 component_quantity,
3180 component_yield_factor,
3181 planning_factor,
3182 quantity_related,
3183 include_in_cost_rollup,
3184 so_basis,
3185 optional,
3186 mutually_exclusive_options,
3187 check_atp,
3188 shipping_allowed,
3189 required_to_ship,
3190 required_for_revenue,
3191 include_on_ship_docs,
3192 include_on_bill_docs,
3193 low_quantity,
3194 high_quantity,
3195 pick_components,
3196 bom_item_type,
3197 operation_seq_num,
3198 item_num,
3199 effectivity_date,
3200 disable_date,
3201 implementation_date,
3202 wip_supply_type
3203 )
3204 SELECT x_bill_sequence_id,
3205 BOM_INVENTORY_COMPONENTS_S.nextval,
3206 be.Component_Item_Id,
3207 SYSDATE,
3208 1,
3209 SYSDATE,
3210 1,
3211 be.Attribute1,
3212 be.Attribute2,
3213 be.Attribute3,
3214 be.Attribute4,
3215 be.Attribute5,
3216 be.Attribute6,
3217 be.Attribute7,
3218 be.Attribute8,
3219 be.Attribute9,
3220 be.Attribute10,
3221 be.Attribute11,
3222 be.Attribute12,
3223 be.Attribute13,
3224 be.Attribute14,
3225 be.Attribute15,
3226 round( be.Component_Quantity, 7 ), /* Support Decimal-Qty for Option Items */
3227 1, /* Component Yield*/
3228 100, /*Component Planning factor */
3229 2, /* Quantity Related */
3230 2, /* Include in Cost Rollup */
3231 2, /* SO Basis */
3232 1, /* Optional */
3233 2, /*Mutually_Exclusive_Options */
3234 2, /*Check_ATP*/
3235 2, /*Shipping Allowed */
3236 2, /*Required to ship */
3237 2, /*Required_For_Revenue*/
3238 2, /*Include on Ship Docs */
3239 2, /*Include_On_Bill_Docs */
3240 be.Low_Quantity,
3241 be.High_Quantity,
3242 1, /* Pick_Components */
3243 be.Bom_Item_Type,
3244 1, /*Operation Sequence Num */
3245 1, /*Item_Num */
3246 SYSDATE,
3247 NULL /*Disable_Date*/,
3248 SYSDATE, /* Implementation Date */
3249 6 /* wip_supply_type */
3250 FROM bom_explosions be
3251 WHERE be.top_bill_sequence_id = X_top_Bill_Sequence_id
3252 AND be.explosion_type = 'OPTIONAL'
3253 AND be.effectivity_date <= SYSDATE
3254 AND nvl(be.disable_date,SYSDATE+1) > SYSDATE
3255 AND be.plan_level = 0;
3256
3257 /* Insert the Mandatory Components of the Model */
3258
3259 INSERT INTO BOM_EXPLOSION_TEMP
3260 ( top_bill_sequence_id,
3261 organization_id,
3262 plan_level,
3263 sort_order,
3264 bill_sequence_id,
3265 component_sequence_id,
3266 component_item_id,
3267 attribute1,
3268 attribute2,
3269 attribute3,
3270 attribute4,
3271 attribute5,
3272 attribute6,
3273 attribute7,
3274 attribute8,
3275 attribute9,
3276 attribute10,
3277 attribute11,
3278 attribute12,
3279 attribute13,
3280 attribute14,
3281 attribute15,
3282 component_quantity,
3283 component_yield_factor,
3284 planning_factor,
3285 include_in_rollup_flag,
3286 so_transactions_flag, /* Used for Quantity Related */
3287 so_basis,
3288 optional,
3289 mutually_exclusive_options,
3290 check_atp,
3291 shipping_allowed,
3292 required_to_ship,
3293 required_for_revenue,
3294 include_on_ship_docs,
3295 include_on_bill_docs,
3296 low_quantity,
3297 high_quantity,
3298 pick_components,
3299 bom_item_type,
3300 operation_seq_num,
3301 item_num,
3302 effectivity_date,
3303 disable_date,
3304 implementation_date,
3305 wip_supply_type,
3306 pricing_attribute1, /** used for from unit item number **/
3307 pricing_attribute2 /** used for to unit item number **/
3308 )
3309 SELECT x_top_bill_sequence_id,
3310 l_organization_id, -- resolved at the begining
3311 1, /*Plan Level */
3312 '1', /* Sort Order */
3313 x_bill_sequence_id,
3314 component_sequence_id,
3315 Component_Item_Id,
3316 Attribute1,
3317 Attribute2,
3318 Attribute3,
3319 Attribute4,
3320 Attribute5,
3321 Attribute6,
3322 Attribute7,
3323 Attribute8,
3324 Attribute9,
3325 Attribute10,
3326 Attribute11,
3327 Attribute12,
3328 Attribute13,
3329 Attribute14,
3330 Attribute15,
3331 Component_Quantity,
3332 component_yield_factor,
3333 planning_factor, /*Component Planning factor */
3334 include_in_cost_rollup, /* Include in Cost Rollup */
3335 NVL(to_char(quantity_related),'2'),
3336 so_basis, /* SO Basis */
3337 optional, /* Optional */
3338 Mutually_Exclusive_Options, /*Mutually_Exclusive_Options */
3339 check_atp, /*Check_ATP*/
3340 shipping_allowed, /*Shipping Allowed */
3341 required_to_ship, /*Required to ship */
3342 required_for_revenue, /*Required_For_Revenue*/
3343 include_on_ship_docs, /*Include on Ship Docs */
3344 include_on_bill_docs, /*Include_On_Bill_Docs */
3345 Low_Quantity,
3346 High_Quantity,
3347 pick_components, /* Pick_Components */
3348 Bom_Item_Type,
3349 operation_seq_num, /*Operation Sequence Num */
3350 item_num, /*Item_Num */
3351 effectivity_date,
3352 disable_date, /*Disable_Date*/
3353 implementation_date, /* Implementation Date */
3354 wip_supply_type,/* wip_supply_type */
3355 from_end_item_unit_number,
3356 to_end_item_unit_number
3357 FROM bom_inventory_components
3358 WHERE bill_sequence_id = l_Bill_Sequence_id -- Sequence_id resolved at the begining
3359 AND effectivity_date <= SYSDATE
3360 AND nvl(disable_date,SYSDATE+1) > SYSDATE
3361 AND optional = 2
3362 AND bom_item_type NOT IN (1,2);
3363
3364 X_Stmt_num := 19;
3365
3366 /* Insert the Option Classes from which user has chosen atleast one option item along with
3367 the component code*/
3368
3369 INSERT INTO BOM_EXPLOSION_TEMP
3370 ( top_bill_sequence_id,
3371 organization_id,
3372 plan_level,
3373 sort_order,
3374 bill_sequence_id,
3375 component_sequence_id,
3376 component_item_id,
3377 attribute1,
3378 attribute2,
3379 attribute3,
3380 attribute4,
3381 attribute5,
3382 attribute6,
3383 attribute7,
3384 attribute8,
3385 attribute9,
3386 attribute10,
3387 attribute11,
3388 attribute12,
3389 attribute13,
3390 attribute14,
3391 attribute15,
3392 component_quantity,
3393 component_yield_factor,
3394 planning_factor,
3395 include_in_rollup_flag,
3396 so_transactions_flag, /* Used for Quantity Related */
3397 so_basis,
3398 optional,
3399 mutually_exclusive_options,
3400 check_atp,
3401 shipping_allowed,
3402 required_to_ship,
3403 required_for_revenue,
3404 include_on_ship_docs,
3405 include_on_bill_docs,
3406 low_quantity,
3407 high_quantity,
3408 pick_components,
3409 bom_item_type,
3410 operation_seq_num,
3411 item_num,
3412 effectivity_date,
3413 disable_date,
3414 implementation_date,
3415 wip_supply_type,
3416 pricing_attribute1, /** used for from unit item number **/
3417 pricing_attribute2, /** used for to unit item number **/
3418 component_code
3419 )
3420 SELECT x_top_bill_sequence_id,
3421 l_organization_id, -- resolved at the begining
3422 1, /*Plan Level */
3423 '1', /* Sort Order */
3424 x_bill_sequence_id,
3425 bic.component_sequence_id,
3426 bic.Component_Item_Id,
3427 Attribute1,
3428 Attribute2,
3429 Attribute3,
3430 Attribute4,
3431 Attribute5,
3432 Attribute6,
3433 Attribute7,
3434 Attribute8,
3435 Attribute9,
3436 Attribute10,
3437 Attribute11,
3438 Attribute12,
3439 Attribute13,
3440 Attribute14,
3441 Attribute15,
3442 --Component_Quantity,
3443 round( cz.quantity,7), /* Support Decimal-Qty for option items */
3444 component_yield_factor,
3445 planning_factor, /*Component Planning factor */
3446 include_in_cost_rollup, /* Include in Cost Rollup */
3447 NVL(to_char(quantity_related),'2'),
3448 so_basis, /* SO Basis */
3449 optional, /* Optional */
3450 Mutually_Exclusive_Options, /*Mutually_Exclusive_Options */
3451 check_atp, /*Check_ATP*/
3452 shipping_allowed, /*Shipping Allowed */
3453 required_to_ship, /*Required to ship */
3454 required_for_revenue, /*Required_For_Revenue*/
3455 include_on_ship_docs, /*Include on Ship Docs */
3456 include_on_bill_docs, /*Include_On_Bill_Docs */
3457 Low_Quantity,
3458 High_Quantity,
3459 pick_components, /* Pick_Components */
3460 bic.Bom_Item_Type,
3461 operation_seq_num, /*Operation Sequence Num */
3462 item_num, /*Item_Num */
3463 effectivity_date,
3464 disable_date, /*Disable_Date*/
3465 implementation_date, /* Implementation Date */
3466 wip_supply_type,/* wip_supply_type */
3467 from_end_item_unit_number,
3468 to_end_item_unit_number,
3469 cz.component_code
3470 FROM bom_inventory_components bic,
3471 cz_config_details_v cz
3472 WHERE bic.component_sequence_id = cz.component_sequence_id
3473 AND bic.bom_item_type IN (1,2) /* Model, Option Classes */
3474 AND cz.config_hdr_id = x_cz_config_hdr_id
3475 AND cz.config_rev_nbr = x_cz_config_rev_num;
3476
3477 X_Stmt_Num := 20;
3478
3479 /** Check if the BOM:CONFIG_INHERIT_OP_SEQ is Set **/
3480
3481 l_op_seq_profile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
3482
3483 /* Insert Mandatory Components for the options selected if inheritance is OFF */
3484
3485 IF l_op_seq_profile <> 1 THEN
3486
3487 FOR cz_mandatory_items IN c_cz_required_items
3488 LOOP
3489 INSERT INTO bom_explosion_temp(
3490 top_bill_sequence_id,
3491 organization_id,
3492 plan_level,
3493 sort_order,
3494 operation_seq_num,
3495 component_item_id,
3496 item_num,
3497 component_quantity,
3498 component_yield_factor,
3499 effectivity_date,
3500 implementation_date,
3501 planning_factor,
3502 so_transactions_flag, /** used for quantity_related **/
3503 so_basis,
3504 optional,
3505 mutually_exclusive_options,
3506 include_in_rollup_flag,
3507 check_atp,
3508 required_to_ship,
3509 required_for_revenue,
3510 include_on_ship_docs,
3511 include_on_bill_docs,
3512 low_quantity,
3513 high_quantity,
3514 component_sequence_id,
3515 bill_sequence_id,
3516 wip_supply_type,
3517 pick_components,
3518 bom_item_type,
3519 pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
3520 pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
3521 attribute1,
3522 attribute2,
3523 attribute3,
3524 attribute4,
3525 attribute5,
3526 attribute6,
3527 attribute7,
3528 attribute8,
3529 attribute9,
3530 attribute10,
3531 attribute11,
3532 attribute12,
3533 attribute13,
3534 attribute14,
3535 attribute15
3536 )
3537 VALUES
3538 ( x_top_bill_sequence_id
3539 , l_organization_id
3540 , 1 /* Plan Level */
3541 , '1' /* Sort Order */
3542 , cz_mandatory_items.OPERATION_SEQ_NUM
3543 , cz_mandatory_items.COMPONENT_ITEM_ID
3544 , Decode(cz_mandatory_items.item_num, NULL,
3545 1, cz_mandatory_items.ITEM_NUM)
3546 , round( cz_mandatory_items.COMPONENT_QUANTITY , 7) /* Support Decimal-Qty for Option Items */
3547 , cz_mandatory_items.component_yield_factor /* component_yield_factor */
3548 , cz_mandatory_items.EFFECTIVITY_DATE
3549 , cz_mandatory_items.IMPLEMENTATION_DATE
3550 , 100 /* planning_factor */
3551 , '2' /* quantity_related */
3552 , cz_mandatory_items.SO_BASIS
3553 , cz_mandatory_items.OPTIONAL
3554 , cz_mandatory_items.MUTUALLY_EXCLUSIVE_OPTIONS
3555 , cz_mandatory_items.include_in_cost_rollup
3556 , cz_mandatory_items.CHECK_ATP
3557 , cz_mandatory_items.REQUIRED_TO_SHIP
3558 , cz_mandatory_items.REQUIRED_FOR_REVENUE
3559 , cz_mandatory_items.INCLUDE_ON_SHIP_DOCS
3560 , cz_mandatory_items.INCLUDE_ON_BILL_DOCS
3561 , cz_mandatory_items.LOW_QUANTITY
3562 , cz_mandatory_items.HIGH_QUANTITY
3563 , cz_mandatory_items.component_sequence_id
3564 , X_BILL_SEQUENCE_ID
3565 , decode(cz_mandatory_items.bom_item_type, 2, 6, 1, 6,
3566 nvl(cz_mandatory_items.wip_supply_type,1)) /* wip_supply_type */
3567 , cz_mandatory_items.PICK_COMPONENTS
3568 , cz_mandatory_items.BOM_ITEM_TYPE
3569 , cz_mandatory_items.From_End_Item_Unit_Number
3570 , cz_mandatory_items.To_End_Item_Unit_Number
3571 , cz_mandatory_items.attribute1
3572 , cz_mandatory_items.attribute2
3573 , cz_mandatory_items.attribute3
3574 , cz_mandatory_items.attribute4
3575 , cz_mandatory_items.attribute5
3576 , cz_mandatory_items.attribute6
3577 , cz_mandatory_items.attribute7
3578 , cz_mandatory_items.attribute8
3579 , cz_mandatory_items.attribute9
3580 , cz_mandatory_items.attribute10
3581 , cz_mandatory_items.attribute11
3582 , cz_mandatory_items.attribute12
3583 , cz_mandatory_items.attribute13
3584 , cz_mandatory_items.attribute14
3585 , cz_mandatory_items.attribute15
3586 );
3587
3588 END LOOP;
3589
3590 END IF;
3591
3592
3593 /* Insert all the selected Options along with the component code */
3594
3595 FOR cz_options IN cz_options_chosen
3596 LOOP
3597 INSERT INTO bom_explosion_temp(
3598 top_bill_sequence_id,
3599 organization_id,
3600 plan_level,
3601 sort_order,
3602 operation_seq_num,
3603 component_item_id,
3604 item_num,
3605 component_quantity,
3606 component_yield_factor,
3607 effectivity_date,
3608 implementation_date,
3609 planning_factor,
3610 so_transactions_flag,
3611 so_basis,
3612 optional,
3613 mutually_exclusive_options,
3614 include_in_rollup_flag,
3615 check_atp,
3616 required_to_ship,
3617 required_for_revenue,
3618 include_on_ship_docs,
3619 include_on_bill_docs,
3620 low_quantity,
3621 high_quantity,
3622 component_sequence_id,
3623 bill_sequence_id,
3624 wip_supply_type,
3625 pick_components,
3626 bom_item_type,
3627 pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
3628 pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
3629 attribute1,
3630 attribute2,
3631 attribute3,
3632 attribute4,
3633 attribute5,
3634 attribute6,
3635 attribute7,
3636 attribute8,
3637 attribute9,
3638 attribute10,
3639 attribute11,
3640 attribute12,
3641 attribute13,
3642 attribute14,
3643 attribute15,
3644 component_code
3645 )
3646 VALUES
3647 ( x_top_bill_sequence_id
3648 , l_organization_id
3649 , 1 /* Plan Level */
3650 , '1' /* Sort Order */
3651 , cz_options.OPERATION_SEQ_NUM
3652 , cz_options.COMPONENT_ITEM_ID
3653 , Decode(cz_options.item_num, NULL,
3654 1, cz_options.ITEM_NUM)
3655 , cz_options.COMPONENT_QUANTITY
3656 , cz_options.component_yield_factor /* component_yield_factor */
3657 , cz_options.EFFECTIVITY_DATE
3658 , cz_options.IMPLEMENTATION_DATE
3659 , 100 /* planning_factor */
3660 , '2' /* quantity_related */
3661 , cz_options.SO_BASIS
3662 , cz_options.OPTIONAL
3663 , cz_options.MUTUALLY_EXCLUSIVE_OPTIONS
3664 , cz_options.include_in_cost_rollup
3665 , cz_options.CHECK_ATP
3666 , cz_options.REQUIRED_TO_SHIP
3667 , cz_options.REQUIRED_FOR_REVENUE
3668 , cz_options.INCLUDE_ON_SHIP_DOCS
3669 , cz_options.INCLUDE_ON_BILL_DOCS
3670 , cz_options.LOW_QUANTITY
3671 , cz_options.HIGH_QUANTITY
3672 , cz_options.component_sequence_id
3673 , X_BILL_SEQUENCE_ID
3674 , decode(cz_options.bom_item_type, 2, 6, 1, 6,
3675 nvl(cz_options.wip_supply_type,1)) /* wip_supply_type */
3676 , cz_options.PICK_COMPONENTS
3677 , cz_options.BOM_ITEM_TYPE
3678 , cz_options.From_End_Item_Unit_Number
3679 , cz_options.To_End_Item_Unit_Number
3680 , cz_options.attribute1
3681 , cz_options.attribute2
3682 , cz_options.attribute3
3683 , cz_options.attribute4
3684 , cz_options.attribute5
3685 , cz_options.attribute6
3686 , cz_options.attribute7
3687 , cz_options.attribute8
3688 , cz_options.attribute9
3689 , cz_options.attribute10
3690 , cz_options.attribute11
3691 , cz_options.attribute12
3692 , cz_options.attribute13
3693 , cz_options.attribute14
3694 , cz_options.attribute15
3695 , cz_options.component_code
3696 );
3697
3698 END LOOP;
3699
3700
3701 /** Finished inserting the chosen options **/
3702
3703 /* Proceed to operation sequence number inheritance.
3704
3705 Inheritance will be performed for the following
3706 1. All option classes choosen for the config item (this does not include the option classes
3707 that are directly under the top model. Those should already have the valid op.seq number.
3708 Inheritance starts from the second level. First level components under the top model
3709 will always have the op.seq number.)
3710
3711 2. All the choosen options
3712
3713 The above two are identified by a valid value for the component_code.
3714
3715 3. Mandatory components that are directly under the model will already have the op.seq number
3716
3717 4. At this point we have not yet inserted the mandatory components for the choosen options of this
3718 config item if the inherit op.seq is ON. They will be inserted along with the inherited value.
3719
3720 */
3721
3722 IF l_op_seq_profile = 1
3723 THEN
3724
3725 FOR r1 IN c_Comps_With_ComponentCode
3726 LOOP
3727
3728 IF r1.operation_seq_num = 1 AND Instr(r1.component_code,'-',1,2) <> 0
3729 /* If operation seq number is 1 and the component is not the first level comp. under the top model */
3730 THEN
3731 /* Get the op.seq number from it's immediate parent */
3732 UPDATE bom_explosion_temp btemp
3733 SET btemp.operation_seq_num = (SELECT operation_seq_num FROM
3734 bom_explosion_temp WHERE component_code =
3735 substr(btemp.component_code,1,to_number(instr(btemp.component_code,'-',-1,1))-1))
3736 WHERE component_code = r1.component_code;
3737 END IF;
3738
3739 END LOOP;
3740
3741 END IF;
3742
3743 /* Insert Mandatory Components for the choosen options along with inherited value*/
3744
3745 IF l_op_seq_profile = 1 THEN
3746
3747 FOR cz_mandatory_items IN c_cz_req_items_with_Inherit
3748 LOOP
3749 INSERT INTO bom_explosion_temp(
3750 top_bill_sequence_id,
3751 organization_id,
3752 plan_level,
3753 sort_order,
3754 operation_seq_num,
3755 component_item_id,
3756 item_num,
3757 component_quantity,
3758 component_yield_factor,
3759 effectivity_date,
3760 implementation_date,
3761 planning_factor,
3762 so_transactions_flag, /** used for quantity_related **/
3763 so_basis,
3764 optional,
3765 mutually_exclusive_options,
3766 include_in_rollup_flag,
3767 check_atp,
3768 required_to_ship,
3769 required_for_revenue,
3770 include_on_ship_docs,
3771 include_on_bill_docs,
3772 low_quantity,
3773 high_quantity,
3774 component_sequence_id,
3775 bill_sequence_id,
3776 wip_supply_type,
3777 pick_components,
3778 bom_item_type,
3779 pricing_attribute1, /** Used for From_End_Item_Unit_Number **/
3780 pricing_attribute2, /** Used for To_End_Item_Unit_Number **/
3781 attribute1,
3782 attribute2,
3783 attribute3,
3784 attribute4,
3785 attribute5,
3786 attribute6,
3787 attribute7,
3788 attribute8,
3789 attribute9,
3790 attribute10,
3791 attribute11,
3792 attribute12,
3793 attribute13,
3794 attribute14,
3795 attribute15
3796 )
3797 VALUES
3798 ( x_top_bill_sequence_id
3799 , l_organization_id
3800 , 1 /* Plan Level */
3801 , '1' /* Sort Order */
3802 , decode(cz_mandatory_items.OPERATION_SEQ_NUM,1,
3803 cz_mandatory_items.PARENT_OPERATION_SEQ_NUM,cz_mandatory_items.OPERATION_SEQ_NUM)
3804 , cz_mandatory_items.COMPONENT_ITEM_ID
3805 , Decode(cz_mandatory_items.item_num, NULL,
3806 1, cz_mandatory_items.ITEM_NUM)
3807 , cz_mandatory_items.COMPONENT_QUANTITY
3808 , cz_mandatory_items.component_yield_factor /* component_yield_factor */
3809 , cz_mandatory_items.EFFECTIVITY_DATE
3810 , cz_mandatory_items.IMPLEMENTATION_DATE
3811 , 100 /* planning_factor */
3812 , '2' /* quantity_related */
3813 , cz_mandatory_items.SO_BASIS
3814 , cz_mandatory_items.OPTIONAL
3815 , cz_mandatory_items.MUTUALLY_EXCLUSIVE_OPTIONS
3816 , cz_mandatory_items.include_in_cost_rollup
3817 , cz_mandatory_items.CHECK_ATP
3818 , cz_mandatory_items.REQUIRED_TO_SHIP
3819 , cz_mandatory_items.REQUIRED_FOR_REVENUE
3820 , cz_mandatory_items.INCLUDE_ON_SHIP_DOCS
3821 , cz_mandatory_items.INCLUDE_ON_BILL_DOCS
3822 , cz_mandatory_items.LOW_QUANTITY
3823 , cz_mandatory_items.HIGH_QUANTITY
3824 , cz_mandatory_items.component_sequence_id
3825 , X_BILL_SEQUENCE_ID
3826 , decode(cz_mandatory_items.bom_item_type, 2, 6, 1, 6,
3827 nvl(cz_mandatory_items.wip_supply_type,1)) /* wip_supply_type */
3828 , cz_mandatory_items.PICK_COMPONENTS
3829 , cz_mandatory_items.BOM_ITEM_TYPE
3830 , cz_mandatory_items.From_End_Item_Unit_Number
3831 , cz_mandatory_items.To_End_Item_Unit_Number
3832 , cz_mandatory_items.attribute1
3833 , cz_mandatory_items.attribute2
3834 , cz_mandatory_items.attribute3
3835 , cz_mandatory_items.attribute4
3836 , cz_mandatory_items.attribute5
3837 , cz_mandatory_items.attribute6
3838 , cz_mandatory_items.attribute7
3839 , cz_mandatory_items.attribute8
3840 , cz_mandatory_items.attribute9
3841 , cz_mandatory_items.attribute10
3842 , cz_mandatory_items.attribute11
3843 , cz_mandatory_items.attribute12
3844 , cz_mandatory_items.attribute13
3845 , cz_mandatory_items.attribute14
3846 , cz_mandatory_items.attribute15
3847 );
3848
3849 END LOOP;
3850
3851 END IF;
3852
3853 /*
3854 l_op_seq_profile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
3855
3856 IF l_op_seq_profile = 1
3857 THEN
3858 -- Call procedure to inherit operation sequences
3859
3860 FOR c_model_options IN c_Options_of_Model
3861 LOOP
3862 -- for each option/model under the base model for which atleast one
3863 -- option is chosen, drill down the tree and set the op-seq
3864 Set_Op_Seq
3865 ( p_organization_id => c_model_options.organization_id
3866 , p_component_item_id => c_model_options.component_item_id
3867 , p_operation_seq_num => c_model_options.operation_seq_num
3868 );
3869 END LOOP;
3870 END IF;
3871 */
3872
3873 /** Once the operation inheritance is complete, then consolidate the components
3874 *** based on the same component_item_id and operation_seq_num
3875 **/
3876
3877 Club_Component_Quantities
3878 ( p_bill_sequence_id => x_bill_sequence_id );
3879
3880 Transfer_Comps
3881 ( p_bill_sequence_id => x_bill_sequence_id );
3882
3883 /* Flush the temp table after the process */
3884
3885 DELETE from bom_explosion_temp;
3886
3887 IF (X_Return_Val <> 0 ) THEN
3888 return;
3889 END IF;
3890 -- Commit;
3891 EXCEPTION
3892 WHEN OTHERS THEN
3893 DELETE from bom_explosion_temp;
3894 x_message := 'BOM_CONFIG_EXPLOSIONS_PKG.Insert_Mandatory_Components('
3895 || to_char(X_Stmt_Num) || '):';
3896 FND_MESSAGE.Set_Name('BOM','CZ_PLSQL_ERROR');
3897 FND_MESSAGE.Set_Token('PACKAGE',x_message);
3898 FND_MESSAGE.Set_Token('ORA_ERROR',SQLCODE);
3899 FND_MESSAGE.Set_Token('ORA_TEXT',substr(SQLERRM,1,100));
3900 x_message := FND_MESSAGE.Get;
3901 return;
3902 END BOM_INS_MODEL_AND_MANDATORY;
3903
3904
3905
3906 END CZ_BOM_CONFIG_EXPLOSIONS_PKG;