[Home] [Help]
PACKAGE BODY: APPS.BOM_EXPLODER_PUB
Source
1 package body BOM_EXPLODER_PUB as
2 /* $Header: BOMPLMXB.pls 120.22.12010000.3 2008/09/09 11:16:33 hvutukur ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMPLMXB.sql |
9 | DESCRIPTION : This file is a packaged procedure for the PLM exploders.
10 | Parameters: org_id organization_id
11 | order_by 1 - Op seq, item seq
12 | 2 - Item seq, op seq
13 | grp_id unique value to identify current explosion
14 | use value FROM sequence BOM_EXPLOSIONS_ALL_s
15 | session_id unique value to identify current session
16 | use value FROM BOM_EXPLOSIONS_ALL_session_s
17 | levels_to_explode
18 | bom_or_eng 1 - BOM
19 | 2 - ENG
20 | impl_flag 1 - implemented only
21 | 2 - both impl AND unimpl
22 | explode_option 1 - All
23 | 2 - Current
24 | 3 - Current AND future
25 | module 1 - Costing
26 | 2 - Bom
27 | 3 - Order entry
28 | cst_type_id cost type id for costed explosion
29 | std_comp_flag 1 - explode only standard components
30 | 2 - all components
31 | expl_qty explosion quantity
32 | item_id item id of asembly to explode
33 | list_id unique id for lists in bom_lists for range
34 | report_option 1 - cost rollup with report
35 | 2 - cost rollup no report
36 | 3 - temp cost rollup with report
37 | cst_rlp_id rollup_id
38 | req_id request id
39 | prgm_appl_id program application id
40 | prg_id program id
41 | user_id user id
42 | lock_flag 1 - do not lock the table
43 | 2 - lock the table
44 | alt_rtg_desg alternate routing designator
45 | rollup_option 1 - single level rollup
46 | 2 - full rollup
47 | plan_factor_flag1 - Yes
48 | 2 - No
49 | alt_desg alternate bom designator
50 | rev_date explosion date
51 | comp_code concatenated component code lpad 16
52 | show_rev 1 - obtain current revision of component
53 | 2 - don't obtain current revision
54 | material_ctrl 1 - obtain subinventory locator
55 | 2 - don't obtain subinventory locator
56 | lead_time 1 - calculate offset percent
57 | 2 - don't calculate offset percent
58 | err_msg error message IN OUT NOCOPY buffer
59 | error_code error code out. returns sql error code
60 | IF sql error, 9999 IF loop detected.
61 | |
62 +==========================================================================*/
63
64 temp number := 0;
65 no_profile EXCEPTION;
66 invalid_org EXCEPTION;
67 invalid_assembly_item_name EXCEPTION;
68 invalid_comp_seq_id EXCEPTION;
69 invalid_bill_seq_id EXCEPTION;
70 invalid_locator_id EXCEPTION;
71 missing_parameters EXCEPTION;
72 exploder_error EXCEPTION;
73 G_EGOUser VARCHAR2(30) := BOM_SECURITY_PUB.Get_EGO_User;
74 G_SortWidth constant number := 7; -- no more than 9999999 components per level
75
76 g_parent_sort_order VARCHAR2(2000) := '0000001';
77 g_sort_count NUMBER := 0;
78
79 TYPE G_VARCHAR2_TBL_TYPE_2000 IS TABLE OF VARCHAR2(2000)
80 INDEX BY BINARY_INTEGER;
81
82 TYPE G_NUMBER_TBL_TYPE IS TABLE OF NUMBER
83 INDEX BY BINARY_INTEGER;
84
85 g_parent_sort_order_tbl G_VARCHAR2_TBL_TYPE_2000;
86 g_quantity_of_children_tbl G_NUMBER_TBL_TYPE;
87 g_total_qty_at_next_level_tbl G_NUMBER_TBL_TYPE;
88
89 g_global_count NUMBER := 1;
90 g_total_quantity NUMBER := 0;
91
92 PROCEDURE Reset_Globals IS
93 BEGIN
94
95 /* Reset all the globally used values */
96
97 g_quantity_of_children_tbl.DELETE;
98 g_total_qty_at_next_level_tbl.DELETE;
99 g_parent_sort_order_tbl.DELETE;
100 g_global_count := 1;
101 g_total_quantity := 0;
102 g_sort_count := 0;
103 g_parent_sort_order := '0000001';
104
105 END;
106
107 FUNCTION Is_Internal_With_Privilege(p_object_name IN VARCHAR2,
108 p_user_name IN VARCHAR2,
109 p_function_name IN VARCHAR2) RETURN VARCHAR2
110 IS
111 l_count NUMBER;
112 l_exists VARCHAR2(1);
113 BEGIN
114 SELECT COUNT(1) INTO l_count FROM ego_internal_people_v
115 WHERE user_name = p_user_name;
116
117 --dbms_output.put_line('User name '||p_user_name);
118 --dbms_output.put_line('User Internal '||l_count);
119
120 IF l_count = 0
121 THEN
122 Return 'N';
123 END IF;
124
125 SELECT 'X' INTO l_exists
126 FROM fnd_form_functions functions,
127 fnd_menu_entries cmf,
128 fnd_menus menus
129 WHERE functions.function_name = p_function_name
130 AND functions.function_id = cmf.function_id
131 AND menus.menu_id = cmf.menu_id
132 AND menus.menu_name = FND_PROFILE.VALUE('EGO_INTERNAL_USER_DEFAULT_ROLE');
133
134 --dbms_output.put_line('User has privilege');
135
136 Return 'Y';
137
138 EXCEPTION WHEN NO_DATA_FOUND
139 THEN
140 Return 'N';
141 END;
142
143 FUNCTION Is_EndItem_Specific ( p_inventory_item_id IN NUMBER,
144 p_organization_id IN NUMBER,
145 p_revision_id IN NUMBER) RETURN VARCHAR2 IS
146 l_result VARCHAR2(1);
147 BEGIN
148 IF p_revision_id IS NULL
149 THEN
150 Return 'N';
151 END IF;
152 SELECT 'Y' INTO l_result FROM mtl_item_revisions_b WHERE inventory_item_id = p_inventory_item_id AND
153 organization_id = p_organization_id AND revision_id = p_revision_id;
154 Return l_result;
155 EXCEPTION WHEN NO_DATA_FOUND
156 THEN
157 Return 'N';
158 END;
159
160 FUNCTION Get_Revision_Code ( p_revision_id IN NUMBER) RETURN VARCHAR2 IS
161 l_revision VARCHAR2(10);
162 BEGIN
163 IF p_revision_id IS NULL
164 THEN
165 Return null;
166 END IF;
167 SELECT revision INTO l_revision FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;
168 Return l_revision;
169 EXCEPTION WHEN NO_DATA_FOUND
170 THEN
171 Return null;
172 END;
173
174 FUNCTION Get_Current_RevisionDetails( p_inventory_item_id IN NUMBER,
175 p_organization_id IN NUMBER,
176 p_effectivity_date IN DATE) RETURN VARCHAR2 IS
177 CURSOR c1 IS
178 SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE
179 inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
180 effectivity_date <= p_effectivity_date
181 AND ((BOM_GLOBALS.get_show_Impl_comps_only = 'Y' AND implementation_date IS NOT NULL) OR BOM_GLOBALS.get_show_Impl_comps_only = 'N') -- added for Bug 7242865
182 ORDER BY effectivity_date DESC;
183
184 BEGIN
185
186 OPEN c1;
187 FETCH c1 INTO p_current_revision_code, p_current_revision_id, p_current_revision_label;
188 IF c1%ROWCOUNT = 0
189 THEN
190 p_current_revision_code := null ;
191 p_current_revision_id := null;
192 p_current_revision_label := null;
193 END IF;
194 CLOSE c1;
195 Return p_current_revision_code;
196 EXCEPTION WHEN OTHERS THEN
197 p_current_revision_code := null ;
198 p_current_revision_id := null;
199 p_current_revision_label := null;
200 Return null;
201
202 END;
203
204
205 FUNCTION Get_Comp_Bill_Seq_Id (p_obj_name IN VARCHAR2,
206 p_top_alternate_designator IN VARCHAR2,
207 p_organization_id IN NUMBER,
208 p_pk1_value IN VARCHAR2,
209 p_pk2_value IN VARCHAR2)
210
211 RETURN NUMBER IS
212
213 l_bill_sequence_id NUMBER;
214
215 CURSOR c1 IS
216 SELECT BBOM_C.bill_sequence_id bill_seq_id FROM bom_structures_b BBOM_C
217 WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
218 BBOM_C.alternate_bom_designator = p_top_alternate_designator;
219
220
221 BEGIN
222
223 /*
224
225 SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
226 WHERE nvl(BBOM_C.obj_name,'EGO_ITEM') = nvl(p_obj_name,'EGO_ITEM')
227 AND BBOM_C.pk1_value = p_pk1_value
228 AND nvl(BBOM_C.pk2_value,'-1') = nvl(p_pk2_value,'-1')
229 AND BBOM_C.organization_id = p_organization_id
230 AND nvl(BBOM_C.alternate_bom_designator, 'NONE') = nvl(p_top_alternate_designator, 'NONE');
231
232 RETURN l_bill_sequence_id;
233
234 EXCEPTION WHEN OTHERS THEN
235 RETURN 0;
236 */
237
238 /* The above code is replaced by the following to make sure the index BOM_STRUCTURES_B_N3 is used */
239
240 IF (p_obj_name IS NULL AND p_top_alternate_designator IS NULL) THEN
241
242 SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
243 WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
244 BBOM_C.alternate_bom_designator IS NULL;
245
246 ELSIF (p_obj_name IS NULL AND p_top_alternate_designator IS NOT NULL) THEN
247 /*
248 FOR r1 IN c1
249 LOOP
250 Return r1.bill_seq_id;
251 END LOOP;
252 */
253
254 SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
255 WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
256 BBOM_C.alternate_bom_designator = p_top_alternate_designator;
257
258 ELSIF (p_obj_name IS NOT NULL AND p_top_alternate_designator IS NULL) THEN
259
260 SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
261 WHERE BBOM_C.obj_name = P_OBJ_NAME AND BBOM_C.pk1_value = p_pk1_value AND
262 BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator is NULL;
263
264 ELSIF (p_obj_name IS NOT NULL AND p_top_alternate_designator IS NOT NULL) THEN
265
266 SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
267 WHERE BBOM_C.obj_name = P_OBJ_NAME AND BBOM_C.pk1_value = p_pk1_value AND
268 BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator = p_top_alternate_designator;
269
270 END IF;
271
272 RETURN l_bill_sequence_id;
273
274 EXCEPTION WHEN OTHERS THEN
275
276 RETURN 0;
277
278 END;
279
280 FUNCTION Get_Sort_Order (p_parent_sort_order IN VARCHAR2,
281 p_component_quantity IN NUMBER := NULL)
282 RETURN VARCHAR2 IS
283
284 BEGIN
285
286 IF p_parent_sort_order <> g_parent_sort_order THEN
287
288 g_parent_sort_order_tbl(g_global_count) := g_parent_sort_order;
289 g_quantity_of_children_tbl(g_global_count) := g_sort_count;
290 g_total_qty_at_next_level_tbl(g_global_count) := g_total_quantity;
291
292 g_sort_count := 0;
293 g_total_quantity := 0;
294 g_parent_sort_order := p_parent_sort_order;
295 g_global_count := g_global_count + 1;
296
297 END IF;
298
299 g_sort_count := g_sort_count + 1;
300 g_total_quantity := g_total_quantity + p_component_quantity;
301
302 Return (g_parent_sort_order||lpad(to_char(g_sort_count), G_SortWidth, '0'));
303
304 END;
305
306 /*****************************************************************************************
307 * Procedure : Get_Change_Policy_Val
308 * Parameters : p_item_rev_id -- Item Revi
309 * : p_bill_seq_id -- Bill Sequence Id
310 * Purpose : This procedure is called to get change policy value for the structure.
311 * The values will 1 (ALLOWED) or 2(CHANGE_ORDER_REQUIRED) or 3 (NOT_ALLOWED)
312 *
313 ********************************************************************************************/
314 FUNCTION Get_Change_Policy_Val (p_item_rev_id IN NUMBER,
315 p_bill_seq_id IN NUMBER)
316 RETURN VARCHAR2 IS
317
318 l_change_policy_val NUMBER;
319 l_change_policy_char_val VARCHAR2(80);
320
321 BEGIN
322
323 SELECT
324 ecp.policy_char_value INTO l_change_policy_char_val
325 FROM
326 MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP, Bom_Structures_b bsb
327 WHERE
328 ecp.policy_object_pk1_value =
329 (SELECT TO_CHAR(ic.item_catalog_group_id)
330 FROM mtl_item_catalog_groups_b ic
331 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
332 FROM EGO_OBJ_TYPE_LIFECYCLES olc
333 WHERE olc.object_id = (SELECT OBJECT_ID
334 FROM fnd_objects
335 WHERE obj_name = 'EGO_ITEM')
336 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
337 AND olc.object_classification_code = ic.item_catalog_group_id
338 )
339 AND ROWNUM = 1
340 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
341 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
342 AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
343 AND ecp.policy_object_pk3_value = ITEM_DTLS.current_phase_id
344 AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
345 AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
346 AND ecp.attribute_code = 'STRUCTURE_TYPE'
347 AND bsb.Structure_Type_id = ecp.attribute_number_value
348 AND bsb.Assembly_item_id = ITEM_DTLS.inventory_item_id
349 AND bsb.organization_id = ITEM_DTLS.organization_id
350 AND bsb.Bill_Sequence_id = p_bill_seq_id;
351
352
353 RETURN l_change_policy_char_val;
354
355 EXCEPTION WHEN OTHERS THEN
356 RETURN 'ALLOWED';
357
358 END Get_Change_Policy_Val;
359
360
361 /*****************************************************************************************
362 * Procedure : Insert_Attachments
363 * Scope : Local
364 * Parameters : p_group_id
365 * Purpose : This procedure is called at the end of the explosion call to insert attachments
366 * for all the nodes in the tree
367 * The sort order of the attachment will be computed as sort_order + rowcount of the attachment
368 * so that it pushes the attachments as children of the component
369 *
370 ********************************************************************************************/
371 PROCEDURE Insert_Attachments( p_group_id IN NUMBER
372 , p_plan_level IN NUMBER
373 )
374 IS
375 BEGIN
376 INSERT INTO BOM_EXPLOSIONS_ALL
377 ( top_bill_sequence_id
378 ,bill_sequence_id
379 ,organization_id
380 ,explosion_type
381 ,component_sequence_id
382 ,component_item_id
383 ,plan_level
384 ,sort_order
385 ,creation_date
386 ,created_by
387 ,last_update_date
388 ,last_updated_by
389 ,top_item_id
390 ,basis_type
391 ,component_quantity
392 ,assembly_item_id
393 ,item_num
394 ,comp_bill_seq_id
395 ,group_id
396 ,alternate_bom_designator
397 ,parent_sort_order
398 ,structure_type_id
399 ,pk1_value
400 ,hgrid_flag
401 ,revision_id
402 ,effectivity_control
403 ,access_flag
404 ,line_id
405 ,obj_name
406 ,exploded_option
407 ,rexplode_flag
408 ,exploded_date
409 ,exploded_unit_number
410 ,exploded_end_item_rev
411 )
412 SELECT
413 BET.TOP_BILL_SEQUENCE_ID,
414 BET.BILL_SEQUENCE_ID,
415 BET.ORGANIZATION_ID,
416 BET.EXPLOSION_TYPE,
417 ATDOCS.SEQ_NUM COMPONENT_SEQUENCE_ID,
418 ATDOCS.ATTACHED_DOCUMENT_ID COMPONENT_ITEM_ID,
419 plan_level +1 ,
420 bet.sort_order||'99'||LPAD(ROWNUM, 6, '0') SORT_ORDER,
421 BET.CREATION_DATE ,
422 BET.CREATED_BY ,
423 ATDOCS.LAST_UPDATE_DATE ,
424 ATDOCS.LAST_UPDATED_BY ,
425 BET.TOP_ITEM_ID,
426 1 BASIS_TYPE,
427 1 COMPONENT_QUANTITY,
428 BET.COMPONENT_ITEM_ID ASSEMBLY_ITEM_ID ,
429 ATDOCS.ATTACHED_DOCUMENT_ID ITEM_NUM ,
430 BET.COMP_BILL_SEQ_ID ,
431 BET.GROUP_ID ,
432 BOM_GLOBALS.GET_ALTERNATE(BET.BILL_SEQUENCE_ID) ALT_BOM_DESG,
433 BET.SORT_ORDER PARENT_SORT_ORDER,
434 BET.STRUCTURE_TYPE_ID STRUCTURE_TYPE_ID ,
435 TO_CHAR(ATDOCS.ATTACHED_DOCUMENT_ID) PK1_VALUE,
436 BET.HGRID_FLAG HGRID_FLAG ,
437 BET.REVISION_ID REVISION_ID ,
438 BET.EFFECTIVITY_CONTROL EFFECTIVITY_CONTROL,
439 BET.ACCESS_FLAG ACCESS_FLAG,
440 ATDOCS.category_id,
441 'ATTACHMENT',
442 BET.EXPLODED_OPTION,
443 0, -- default insert attachment with rexplode flag of 0
444 BET.exploded_date,
445 BET.exploded_unit_number,
446 BET.exploded_end_item_rev
447 FROM BOM_EXPLOSIONS_ALL BET ,
448 FND_ATTACHED_DOCUMENTS ATDOCS,
449 FND_DOCUMENTS_TL DOCTL
450 WHERE ATDOCS.DOCUMENT_ID = DOCTL.DOCUMENT_ID
451 AND DOCTL.LANGUAGE = USERENV('LANG')
452 AND ( ( ATDOCS.ENTITY_NAME = 'MTL_SYSTEM_ITEMS'
453 AND ATDOCS.PK1_VALUE = TO_CHAR(BET.ORGANIZATION_ID)
454 AND ATDOCS.PK2_VALUE = TO_CHAR(BET.COMPONENT_ITEM_ID) )
455 OR
456 ( ATDOCS.ENTITY_NAME = 'MTL_ITEM_REVISIONS'
457 AND ATDOCS.PK1_VALUE = TO_CHAR(BET.ORGANIZATION_ID)
458 AND ATDOCS.PK2_VALUE = TO_CHAR(BET.COMPONENT_ITEM_ID)
459 AND ATDOCS.PK3_VALUE = BET.REVISION_ID ) )
460 AND ATDOCS.CATEGORY_ID IN
461 ( SELECT BIA.attach_category_id FROM BOM_ITEM_ATTACH_CATEGORY_ASSOC BIA
462 WHERE BIA.STRUCTURE_TYPE_ID IN
463 ( SELECT bst1.structure_type_id
464 FROM BOM_STRUCTURE_TYPES_B bst1
465 CONNECT BY PRIOR bst1.parent_structure_type_id = bst1.structure_type_id
466 START WITH bst1.structure_type_id =
467 ( select strb.structure_type_id
468 from bom_structures_b strb
469 where strb.bill_sequence_id = bet.bill_sequence_id
470 )
471 )
472 )
473 AND group_id = p_group_id
474 AND plan_level = p_plan_level
475 AND NVL(obj_name,'EGO_ITEM') = 'EGO_ITEM';
476
477
478 --Dbms_Output.put_line('insert for plan level: ' || p_plan_level || ' no: ' || SQL%ROWCOUNT);
479 /*
480 for c in (select item_name from bom_explosions_v where explode_group_id = p_group_id
481 and plan_level = 0
482 )
483 loop
484
485 Dbms_Output.put_line('file name: ' || c.item_name);
486 end loop;
487 */
488 END Insert_Attachments;
489
490
491 /****************************************************************************
492 * Procedure : Apply_Exclusion_Rules
493 * Parameters : p_Group_Id
494 * Scope : Local
495 * Purpose : This procedure is invoked at the end of explosion. It will
496 * look at the defined rules for various bills and apply them
497 * to the explosion identified by p_Group_Id.
498 * Instead of pruning the tree based on the exclusion rule, the
499 * nodes in the tree are stamped. Is_Excluded_By_Rule column will
500 * be set to 'Y' if a node is excluded.
501 ******************************************************************************/
502 PROCEDURE Apply_Exclusion_Rules (p_Group_Id IN NUMBER, reApply IN NUMBER DEFAULT 0)
503 IS
504 exclusion_t dbms_sql.varchar2_table;
505 BEGIN
506 SELECT '%'||d.exclusion_path || '%'
507 BULK COLLECT INTO exclusion_t
508 FROM bom_rules_b r,
509 bom_exclusion_rule_def d
510 WHERE d.rule_id = r.rule_id
511 AND d.from_revision_id IS NULL
512 AND d.implementation_date IS NOT NULL
513 AND d.disable_date IS NULL
514 AND d.acd_type = 1
515 AND r.bill_sequence_id IN
516 (SELECT bill_sequence_id
517 FROM BOM_EXPLOSIONS_ALL
518 WHERE group_id = p_Group_Id
519 );
520
521 IF (reApply = 1) THEN
522 UPDATE BOM_EXPLOSIONS_ALL
523 SET is_excluded_by_rule = NULL
524 WHERE group_id = p_Group_Id
525 AND is_excluded_by_rule = 'Y';
526 END IF;
527
528 UPDATE BOM_EXPLOSIONS_ALL
529 SET reapply_exclusions = NULL
530 WHERE group_id = p_Group_Id AND plan_level = 0;
531
532 FORALL i in 1..exclusion_t.count
533 UPDATE BOM_EXPLOSIONS_ALL
534 SET is_excluded_by_rule = 'Y'
535 WHERE group_id = p_Group_Id
536 AND new_component_code like exclusion_t(i);
537 END;/* Procedure Apply_Exclusion_Rules Ends */
538
539 /****************************************************************************
540 * Procedure : Apply_New_Exclusion_Rules
541 * Parameters : p_bill_sequence_id
542 * Scope : Local
543 * Purpose : This procedure is invoked when new explosion rules have been added
544 ******************************************************************************/
545 PROCEDURE Apply_New_Exclusion_Rules (p_bill_sequence_id IN NUMBER)
546 IS
547 exclusion_t dbms_sql.varchar2_table;
548 BEGIN
549 SELECT '%'||d.exclusion_path || '%'
550 BULK COLLECT INTO exclusion_t
551 FROM bom_rules_b r,
552 bom_exclusion_rule_def d
553 WHERE d.rule_id = r.rule_id
554 AND d.from_revision_id IS NULL
555 AND d.implementation_date IS NOT NULL
556 AND d.disable_date IS NULL
557 AND d.acd_type = 1
558 AND r.bill_sequence_id = p_bill_sequence_id;
559
560 FORALL i in 1..exclusion_t.count
561 UPDATE BOM_EXPLOSIONS_ALL
562 SET is_excluded_by_rule = 'Y'
563 WHERE group_id IN
564 (SELECT t.Group_Id FROM BOM_EXPLOSIONS_ALL t
565 WHERE t.bill_sequence_id = p_bill_sequence_id
566 )
567 AND new_component_code like exclusion_t(i);
568 END; /* Procedure Apply_New_Exclusion_Rules Ends */
569
570 /****************************************************************************
571 * Procedure : Set_Reapply_Exclusion_Flag
572 * Parameters : p_bill_sequence_id
573 * Scope : Local
574 * Purpose : This procedure sets the reapply_exclusions flag to 'Y' for all the
575 * structures where this structure is added as substructure.
576 * Only the rows with plan_level=0 will be modified.
577 ******************************************************************************/
578 PROCEDURE Set_Reapply_Exclusion_Flag (p_bill_sequence_id IN NUMBER)
579 IS
580 BEGIN
581 UPDATE BOM_EXPLOSIONS_ALL
582 SET reapply_exclusions = 'Y'
583 WHERE Top_bill_sequence_id IN
584 (SELECT Top_bill_sequence_id FROM BOM_EXPLOSIONS_ALL
585 WHERE bill_sequence_id = p_bill_sequence_id
586 )
587 AND plan_level = 0;
588 END;/* Procedure Set_Reapply_Exclusion_Flag Ends */
589
590 /* If the component node is excluded, this function will return 'Y' otherwise null */
591
592 FUNCTION Check_Excluded_By_Rule (p_component_code IN VARCHAR2) RETURN VARCHAR2
593 IS
594 BEGIN
595 IF rev_specific_exclusions_array.COUNT = 0
596 THEN
597 Return null;
598 END IF;
599
600 FOR i IN 1..rev_specific_exclusions_array.COUNT
601 LOOP
602 IF instr(p_component_code, rev_specific_exclusions_array(i)) <> 0
603 THEN
604 Return 'Y';
605 END IF;
606 END LOOP;
607 Return null;
608 END;
609
610 /* If the component does not have access, this function will return 'F' otherwise 'T' */
611 FUNCTION Check_Component_Access (p_component_code IN VARCHAR2) RETURN VARCHAR2
612 IS
613 BEGIN
614 FOR i IN 1..asss_without_access_array.COUNT
615 LOOP
616 IF instr(p_component_code, asss_without_access_array(i)) <> 0
617 THEN
618 Return 'F';
619 END IF;
620 END LOOP;
621 Return 'T';
622 END;
623
624
625 PROCEDURE bom_exploder(
626 verify_flag IN NUMBER DEFAULT 0,
627 online_flag IN NUMBER DEFAULT 1,
628 org_id IN NUMBER,
629 order_by IN NUMBER DEFAULT 1,
630 grp_id IN NUMBER,
631 levels_to_explode IN NUMBER DEFAULT 1,
632 bom_or_eng IN NUMBER DEFAULT 1,
633 impl_flag IN NUMBER DEFAULT 1,
634 plan_factor_flag IN NUMBER DEFAULT 2,
635 explode_option IN NUMBER DEFAULT 2,
636 std_comp_flag IN NUMBER DEFAULT 2,
637 incl_oc_flag IN NUMBER DEFAULT 1,
638 max_level IN NUMBER,
639 unit_number IN VARCHAR2,
640 rev_date IN DATE DEFAULT sysdate,
641 object_rev_id IN NUMBER,
642 minor_rev_id IN NUMBER,
643 show_rev IN NUMBER DEFAULT 2,
644 material_ctrl IN NUMBER DEFAULT 2,
645 lead_time IN NUMBER DEFAULT 2,
646 object_name IN VARCHAR2 DEFAULT NULL,
647 pk_value1 IN VARCHAR2 DEFAULT NULL,
648 pk_value2 IN VARCHAR2 DEFAULT NULL,
649 pk_value3 IN VARCHAR2 DEFAULT NULL,
650 pk_value4 IN VARCHAR2 DEFAULT NULL,
651 pk_value5 IN VARCHAR2 DEFAULT NULL,
652 end_item_id IN NUMBER DEFAULT NULL,
653 end_item_org_id IN NUMBER DEFAULT NULL,
654 end_item_rev_id IN NUMBER DEFAULT NULL,
655 end_item_minor_rev_id IN NUMBER DEFAULT NULL,
656 end_item_minor_rev_code IN VARCHAR2 DEFAULT NULL,
657 filter_pbom IN VARCHAR2 DEFAULT NULL,
658 top_bill_sequence IN NUMBER,
659 err_msg IN OUT NOCOPY VARCHAR2,
660 error_code IN OUT NOCOPY NUMBER) IS
661
662 prev_sort_order VARCHAR2(4000);
663 prev_top_bill_id NUMBER;
664 cum_count NUMBER;
665 total_rows NUMBER;
666 cat_sort VARCHAR2(7);
667 impl_eco varchar2(20);
668
669 -- verify local vars
670 cur_component VARCHAR2(20);
671 cur_substr VARCHAR2(20);
672 cur_loopstr VARCHAR2(4000);
673 cur_loopflag VARCHAR2(1);
674 loop_found BOOLEAN := false;
675 max_level_exceeded BOOLEAN := false;
676 start_pos NUMBER;
677
678 l_end_item_id NUMBER := end_item_id;
679 l_end_item_org_id NUMBER := end_item_org_id;
680 l_end_item_rev_id NUMBER := end_item_rev_id;
681 l_end_item_minor_rev_id NUMBER := end_item_minor_rev_id;
682 l_end_item_minor_rev_code VARCHAR2(30) := end_item_minor_rev_code;
683
684 CURSOR exploder (
685 c_level NUMBER,
686 c_grp_id NUMBER,
687 c_org_id NUMBER,
688 c_bom_or_eng NUMBER,
689 c_rev_date date,
690 c_impl_flag NUMBER,
691 c_explode_option NUMBER,
692 c_order_by NUMBER,
693 c_verify_flag NUMBER,
694 c_plan_factor_flag NUMBER,
695 c_std_comp_flag NUMBER,
696 c_incl_oc NUMBER
697 ) IS
698 SELECT
699 BET.TOP_BILL_SEQUENCE_ID TBSI,
700 BOM.BILL_SEQUENCE_ID BSI,
701 BOM.COMMON_BILL_SEQUENCE_ID CBSI,
702 nvl(BOM.COMMON_ORGANIZATION_ID,BOM.ORGANIZATION_ID) COI,
703 BOM.ORGANIZATION_ID OI,
704 BIC.COMPONENT_SEQUENCE_ID CSI,
705 BIC.PK1_VALUE CID,
706 BIC.BASIS_TYPE BT,
707 BIC.COMPONENT_QUANTITY CQ,
708 C_LEVEL PLAN_LEVEL,
709 (BIC.COMPONENT_QUANTITY * decode(BIC.BASIS_TYPE, 1,BET.EXTENDED_QUANTITY,1) *
710 decode(c_plan_factor_flag, 1, BIC.PLANNING_FACTOR/100, 1) /
711 decode(BIC.COMPONENT_YIELD_FACTOR, 0, 1,BIC.COMPONENT_YIELD_FACTOR)) EQ,
712 TO_CHAR(NULL) SO,
713 C_GRP_ID GROUP_ID,
714 BET.TOP_ALTERNATE_DESIGNATOR TAD,
715 BIC.COMPONENT_YIELD_FACTOR CYF,
716 BET.TOP_ITEM_ID TID,
717 BET.COMPONENT_CODE CC,
718 BIC.INCLUDE_IN_COST_ROLLUP IICR,
719 BET.LOOP_FLAG LF,
720 BIC.PLANNING_FACTOR PF,
721 BIC.OPERATION_SEQ_NUM OSN,
722 BIC.BOM_ITEM_TYPE BIT,
723 BET.BOM_ITEM_TYPE PBIT,
724 --to_char(BET.COMPONENT_ITEM_ID) PAID,
725 BET.PK1_VALUE PAID,
726 BOM.ALTERNATE_BOM_DESIGNATOR, -- for routing
727 BIC.WIP_SUPPLY_TYPE WST,
728 BIC.ITEM_NUM ITN,
729 BIC.EFFECTIVITY_DATE ED,
730 BIC.DISABLE_DATE DD,
731 /*
732 Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE)) TED,
733 Least(Nvl(BIC.DISABLE_DATE,BET.DISABLE_DATE),Nvl(BET.DISABLE_DATE,BIC.DISABLE_DATE)) TDD,
734 */
735 /* When there is a fixed parent rev, then the trimmed eff dt and trimmed disable dt are same as eff date
736 and dis date, as the effectivity check is anyways reapplied in the view */
737 decode(BET.COMP_FIXED_REV_HIGH_DATE,
738 null,
739 Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.TRIMMED_EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE)),
740 BIC.EFFECTIVITY_DATE) TED,
741 --Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.TRIMMED_EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE)) TED,
742 decode(BET.COMP_FIXED_REV_HIGH_DATE,
743 null,
744 Least(Nvl(BIC.DISABLE_DATE,BET.TRIMMED_DISABLE_DATE),Nvl(BET.TRIMMED_DISABLE_DATE,BIC.DISABLE_DATE)),
745 BIC.DISABLE_DATE) TDD,
746 --Least(Nvl(BIC.DISABLE_DATE,BET.TRIMMED_DISABLE_DATE),Nvl(BET.TRIMMED_DISABLE_DATE,BIC.DISABLE_DATE)) TDD,
747 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
748 BIC.TO_END_ITEM_UNIT_NUMBER EUN,
749 /*
750 Greatest(BIC.FROM_END_ITEM_UNIT_NUMBER,Nvl(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) TFUN,
751 Least(Nvl(BIC.TO_END_ITEM_UNIT_NUMBER,BET.TO_END_ITEM_UNIT_NUMBER),Nvl(BET.TO_END_ITEM_UNIT_NUMBER,BIC.TO_END_ITEM_UNIT_NUMBER)) TEUN,
752 */
753 Greatest(BIC.FROM_END_ITEM_UNIT_NUMBER,Nvl(BET.TRIMMED_FROM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) TFUN,
754 Least(Nvl(BIC.TO_END_ITEM_UNIT_NUMBER,BET.TRIMMED_TO_UNIT_NUMBER),Nvl(BET.TRIMMED_TO_UNIT_NUMBER,BIC.TO_END_ITEM_UNIT_NUMBER)) TEUN,
755 BIC.IMPLEMENTATION_DATE ID,
756 --decode(BIC.IMPLEMENTATION_DATE,null,BIC.IMPLEMENTATION_DATE,decode(BET.IMPLEMENTATION_DATE,null,BET.IMPLEMENTATION_DATE,BIC.IMPLEMENTATION_DATE)) ID,
757 --decode(BIC.IMPLEMENTATION_DATE,null,null,decode(BET.IMPLEMENTATION_DATE,null,null,BIC.IMPLEMENTATION_DATE)) ID,
758 --decode(BET.IMPLEMENTATION_DATE,null,null,BIC.IMPLEMENTATION_DATE) ID,
759 BIC.OPTIONAL OPT,
760 BIC.SUPPLY_SUBINVENTORY SS,
761 BIC.SUPPLY_LOCATOR_ID SLI,
762 BIC.COMPONENT_REMARKS CR,
763 BIC.CHANGE_NOTICE CN,
764 --decode(BIC.IMPLEMENTATION_DATE,null,BIC.CHANGE_NOTICE,decode(BET.IMPLEMENTATION_DATE,null,BET.CHANGE_NOTICE,BIC.CHANGE_NOTICE)) CN,
765 --decode(BET.IMPLEMENTATION_DATE,null,BET.CHANGE_NOTICE,BIC.CHANGE_NOTICE) CN,
766 BIC.OPERATION_LEAD_TIME_PERCENT OLTP,
767 BIC.MUTUALLY_EXCLUSIVE_OPTIONS MEO,
768 BIC.CHECK_ATP CATP,
769 BIC.REQUIRED_TO_SHIP RTS,
770 BIC.REQUIRED_FOR_REVENUE RFR,
771 BIC.INCLUDE_ON_SHIP_DOCS IOSD,
772 BIC.LOW_QUANTITY LQ,
773 BIC.HIGH_QUANTITY HQ,
774 BIC.SO_BASIS SB,
775 BET.OPERATION_OFFSET,
776 BET.CURRENT_REVISION,
777 BET.LOCATOR,
778 BIC.ATTRIBUTE_CATEGORY,
779 BIC.ATTRIBUTE1,
780 BIC.ATTRIBUTE2,
781 BIC.ATTRIBUTE3,
782 BIC.ATTRIBUTE4,
783 BIC.ATTRIBUTE5,
784 BIC.ATTRIBUTE6,
785 BIC.ATTRIBUTE7,
786 BIC.ATTRIBUTE8,
787 BIC.ATTRIBUTE9,
788 BIC.ATTRIBUTE10,
789 BIC.ATTRIBUTE11,
790 BIC.ATTRIBUTE12,
791 BIC.ATTRIBUTE13,
792 BIC.ATTRIBUTE14,
793 BIC.ATTRIBUTE15,
794 BIC.obj_name,
795 BIC.pk1_value,
796 BIC.pk2_value,
797 BIC.pk3_value,
798 BIC.pk4_value,
799 BIC.pk5_value,
800 BIC.from_end_item_rev_id FEREVID,
801 BIC.from_end_item_minor_rev_id FEMREVID,
802 BIC.to_end_item_rev_id TEREVID,
803 BIC.to_end_item_minor_rev_id TEMREVID,
804 BET.NEW_COMPONENT_CODE CLCC,
805 BET.SORT_ORDER PARENT_SORT_ORDER,
806 to_number(NULL) CCBSI,
807 BOM_EXPLODER_PUB.Get_Comp_Bill_Seq_Id (BIC.OBJ_NAME, BET.TOP_ALTERNATE_DESIGNATOR,
808 --NVL(BET.COMMON_ORGANIZATION_ID,BET.ORGANIZATION_ID),
809 BET.ORGANIZATION_ID,
810 BIC.pk1_value,BIC.pk2_value) CBSID, -- comp_bill_seq_id
811 'T' ACFLAG,
812 BOM.ASSEMBLY_TYPE AST,
813 to_char(NULL) REVISION_LABEL,
814 to_number(NULL) REVISION_ID,
815 BOM.EFFECTIVITY_CONTROL BEFC,
816 to_number(NULL) OREVID,
817 to_number(NULL) MREVID,
818 to_char(NULL) MREVCODE,
819 BIC.FROM_OBJECT_REVISION_ID FORI,
820 BIC.FROM_MINOR_REVISION_ID FMRI,
821 BIC.TO_OBJECT_REVISION_ID TORI,
822 BIC.TO_MINOR_REVISION_ID TMRI,
823 /* If the BOM is commoned across org, then do not pick up the fixed component item revision id */
824 /*DECODE( SIGN(BET.ORGANIZATION_ID - BET.COMMON_ORGANIZATION_ID),
825 0,
826 BIC.COMPONENT_ITEM_REVISION_ID,
827 NULL) COMPONENT_ITEM_REVISION_ID,*/
828 --Commented by arudresh for bug 5235768. If a component exists as a fixed rev, the rev must exist
829 --in all orgs in which the BOM is commoned. This check is enforced during common bom creation.
830 BIC.COMPONENT_ITEM_REVISION_ID,
831 BIC.COMPONENT_MINOR_REVISION_ID,
832 BOM.IMPLEMENTATION_DATE,
833 BET.TOP_GTIN_NUMBER TGTIN,
834 BET.TOP_GTIN_DESCRIPTION TGTIN_DESC,
835 BET.TOP_TRADE_ITEM_DESCRIPTOR TTRADE_DESC,
836 BET.GTIN_NUMBER PGTIN,
837 BET.GTIN_DESCRIPTION PGTIN_DESC,
838 BET.TRADE_ITEM_DESCRIPTOR PTRADE_DESC,
839 BIC.CREATION_DATE CRDATE,
840 BIC.CREATED_BY CRBY,
841 BIC.LAST_UPDATE_DATE LUDATE,
842 BIC.LAST_UPDATED_BY LUBY,
843 BIC.AUTO_REQUEST_MATERIAL AREQ,
844 decode(nvl(comp_common_bill_seq_id,'0'),'0','0','1') REEXPLODE,
845 BIC.ACD_TYPE ACD,
846 --decode(BIC.IMPLEMENTATION_DATE,null,BIC.ACD_TYPE,decode(BET.IMPLEMENTATION_DATE,null,BET.ACD_TYPE,BIC.ACD_TYPE)) ACD,
847 --decode(BET.IMPLEMENTATION_DATE,null,BET.ACD_TYPE,BIC.ACD_TYPE) ACD,
848 BIC.QUANTITY_RELATED QTR,
849 'ALLOWED',--BET.CHANGE_POLICY_VALUE,
850 BET.EXPLODED_OPTION EXPOP,
851 BOM.STRUCTURE_TYPE_ID STYPE,
852 BET.COMP_FIXED_REV_HIGH_DATE CRHGDT,
853 NVL(BET.COMPONENT_ITEM_REVISION_ID,BET.COMP_FIXED_REVISION_ID) FPR,
854 BET.COMPONENT_SEQUENCE_ID PCSEQ,
855 BOM.IS_PREFERRED,
856 decode(BET.PARENT_IMPLEMENTATION_DATE,null,BET.PARENT_IMPLEMENTATION_DATE,BET.IMPLEMENTATION_DATE) PID,
857 NVL( DECODE(BET.IMPLEMENTATION_DATE,null,BET.CHANGE_NOTICE,null), BET.PARENT_CHANGE_NOTICE) PCN ,
858 BOM.SOURCE_BILL_SEQUENCE_ID SBSI,
859 BIC.COMMON_COMPONENT_SEQUENCE_ID CCSI,
860 to_number(NULL) CSBSI,
861 to_number(NULL) COMP_EFFECTIVITY_CONTROL
862 FROM
863 --BOM_PLM_EXPLOSION_TEMP BET,
864 BOM_EXPLOSIONS_ALL BET,
865 BOM_STRUCTURES_B BOM,
866 BOM_COMPONENTS_B BIC
867 WHERE BET.GROUP_ID = c_grp_id
868 AND BET.PLAN_LEVEL = c_level - 1
869 /* Do not explode the component if it is a pending change (disable, change)) */
870 AND (BET.PLAN_LEVEL = 0
871 OR BET.IMPLEMENTATION_DATE IS NOT NULL
872 OR BET.ACD_TYPE = 1)
873 AND BET.REXPLODE_FLAG = 1
874 AND BET.ACCESS_FLAG = 'T'
875 AND BET.COMP_BILL_SEQ_ID IS NOT NULL
876 AND BET.COMP_BILL_SEQ_ID = BOM.BILL_SEQUENCE_ID
877
878 -- Link BOM AND Components
879 AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
880
881 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
882
883 -- This check is valid only IF the BOM AND Component both are inventory items
884
885 AND ( (BET.obj_name IS NULL AND BIC.obj_name IS NULL
886 AND (c_std_comp_flag = 1 -- only std components
887 AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2)
888 OR
889 (c_std_comp_flag = 2)
890 OR
891 (c_std_comp_flag = 3 AND nvl(BET.BOM_ITEM_TYPE, 1) IN (1,2)
892 AND (BIC.BOM_ITEM_TYPE IN (1,2)
893 OR
894 (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)))
895 ) OR 1=1 )
896
897
898 AND ( (c_bom_or_eng = 1 AND BOM.ASSEMBLY_TYPE = 1)
899 OR
900 (c_bom_or_eng = 2)
901 )
902 --uncommented by arudresh for bug: 4422266
903
904 -- whether to include option classes AND models under a standard item
905 -- special logic added at CST request
906 -- This check is valid only IF the BOM AND Component both are inventory items
907
908 AND ( (BET.obj_name IS NULL AND BIC.obj_name IS NULL
909 AND (c_incl_oc = 1)
910 or
911 (c_incl_oc = 2 AND
912 ( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4)
913 OR ( BET.BOM_ITEM_TYPE <> 4)))
914 OR 1 = 1)
915
916 -- do not explode IF immediate parent is standard AND current
917 -- component is option class or model - special logic for config items
918 AND ( (BET.obj_name IS NULL
919 AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4 AND BET.BOM_ITEM_TYPE IN (1,2)))
920 OR (BET.obj_name IS NOT NULL))
921
922 AND (
923 ( NVL(BOM.EFFECTIVITY_CONTROL,1) = 2 -- Unit/Serial Effectivity
924 AND ( (c_explode_option = 1) -- ALL
925 OR (c_explode_option IN (2,3) AND BIC.DISABLE_DATE IS NULL
926 AND BIC.from_end_item_unit_number IS NOT NULL
927 AND ( (c_explode_option = 2
928 AND unit_number >= BIC.from_end_item_unit_number
929 AND unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number))
930 OR
931 (c_explode_option = 3
932 AND unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number))
933 )
934 AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
935 OR c_impl_flag = 2 ))
936 )
937 )
938 OR
939 ( NVL(BOM.EFFECTIVITY_CONTROL,1) = 4 -- End Item rev effectivity
940 AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
941 OR c_impl_flag = 2 )
942 AND
943 ( (c_explode_option = 1) -- ALL
944 OR
945 (c_explode_option IN (2,3) -- Current + Current and Future
946 AND BIC.DISABLE_DATE IS NULL
947 AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
948 AND
949 ( (BET.plan_level > 0 AND EXISTS (SELECT null
950 FROM mtl_item_revisions_b
951 WHERE inventory_item_id = BET.component_item_id AND
952 organization_id = BET.organization_id AND
953 revision_id = BIC.FROM_END_ITEM_REV_ID ))
954 OR
955 EXISTS (SELECT null FROM mtl_item_revisions_b
956 WHERE inventory_item_id = l_end_item_id AND
957 organization_id = l_end_item_org_id AND
958 revision_id = BIC.FROM_END_ITEM_REV_ID) )))
959 )
960
961 /*
962 ( NVL(BOM.EFFECTIVITY_CONTROL,1) = 4 -- End Item rev effectivity
963 AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
964 OR c_impl_flag = 2 )
965 AND
966 ( (c_explode_option = 1) -- ALL
967 OR
968 (c_explode_option = 3 -- Current and Future
969 AND BIC.DISABLE_DATE IS NULL
970 AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
971 AND
972 ( (l_end_item_minor_rev_code <= (SELECT concat(to_char(decode(BIC.TO_END_ITEM_REV_ID,null,to_date('9999-12-31','YYYY-MM-DD'),effectivity_date),'yyyymmddhh24miss'),
973 to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
974 FROM mtl_item_revisions_b
975 WHERE inventory_item_id = l_end_item_id AND
976 organization_id = l_end_item_org_id AND
977 revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
978 OR
979 (BET.minor_revision_code <= (SELECT concat(to_char(decode(BIC.TO_END_ITEM_REV_ID,null,to_date('9999-12-31','YYYY-MM-DD'),effectivity_date),'yyyymmddhh24miss'),
980 to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
981 FROM mtl_item_revisions_b
982 WHERE inventory_item_id = BET.component_item_id AND
983 organization_id = BET.organization_id AND
984 revision_id = nvl(BIC.TO_END_ITEM_REV_ID,BIC.FROM_END_ITEM_REV_ID)))
985 )
986 )
987 OR
988 ( c_explode_option = 2 -- Current
989 AND BIC.DISABLE_DATE IS NULL
990 AND BIC.FROM_END_ITEM_REV_ID IS NOT NULL
991 AND
992 (
993 (l_end_item_minor_rev_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
994 to_char(nvl(BIC.from_end_item_minor_rev_id,0)))
995 FROM mtl_item_revisions_b
996 WHERE inventory_item_id = l_end_item_id AND
997 organization_id = l_end_item_org_id AND
998 revision_id = BIC.FROM_END_ITEM_REV_ID)
999 AND (BIC.to_end_item_rev_id IS NULL OR
1000 l_end_item_minor_rev_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1001 to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
1002 FROM mtl_item_revisions_b
1003 WHERE inventory_item_id = l_end_item_id AND
1004 organization_id = l_end_item_org_id AND
1005 revision_id = BIC.TO_END_ITEM_REV_ID) ))
1006
1007 OR
1008
1009 (BET.minor_revision_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1010 to_char(nvl(BIC.from_end_item_minor_rev_id,0)))
1011 FROM mtl_item_revisions_b
1012 WHERE inventory_item_id = BET.component_item_id AND
1013 organization_id = BET.organization_id AND
1014 revision_id = BIC.FROM_END_ITEM_REV_ID)
1015 AND (BIC.to_end_item_rev_id IS NULL OR
1016 BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1017 to_char(nvl(BIC.to_end_item_minor_rev_id,9999999999999999)))
1018 FROM mtl_item_revisions_b
1019 WHERE inventory_item_id = BET.component_item_id AND
1020 organization_id = BET.organization_id AND
1021 revision_id = BIC.TO_END_ITEM_REV_ID)) )
1022 )
1023 )
1024
1025
1026 )
1027 )
1028 */
1029 OR
1030 ( NVL(BOM.EFFECTIVITY_CONTROL,1) =1 -- Date Effectivity
1031 AND ((c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL) OR c_impl_flag = 2 )
1032 AND
1033 ( (c_explode_option = 1 -- ALL
1034 AND (c_level - 1 = 0 OR
1035 -- make sure the component is effective for the parent IF it is other than 1st level
1036 -- though the option is ALL
1037 ( BIC.effectivity_date <= nvl(BET.disable_date, BIC.effectivity_date) AND
1038 NVL(BIC.disable_date, BET.effectivity_date) >= BET.effectivity_date)))
1039 OR
1040 ( ( BIC.IMPLEMENTATION_DATE IS NOT NULL AND
1041 ((c_explode_option = 2 AND
1042 nvl(BET.comp_fixed_rev_high_date,c_rev_date) >= BIC.EFFECTIVITY_DATE AND
1043 nvl(BET.comp_fixed_rev_high_date,c_rev_date) < nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+1)) -- CURRENT
1044 OR
1045 (c_explode_option = 3 AND
1046 nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+ 1) > nvl(BET.comp_fixed_rev_high_date,c_rev_date) )) -- CURRENT AND FUTURE
1047 )
1048 OR
1049 ( BIC.IMPLEMENTATION_DATE IS NULL AND
1050 nvl(BIC.ACD_TYPE,1) = 3
1051 OR
1052 (
1053 ((c_explode_option = 2 AND
1054 nvl(BET.comp_fixed_rev_high_date,c_rev_date) >= BIC.EFFECTIVITY_DATE AND
1055 nvl(BET.comp_fixed_rev_high_date,c_rev_date) < nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+1)) -- CURRENT
1056 OR
1057 (c_explode_option = 3 AND
1058 nvl(BIC.DISABLE_DATE, nvl(BET.comp_fixed_rev_high_date,c_rev_date)+ 1) > nvl(BET.comp_fixed_rev_high_date,c_rev_date) )) -- CURRENT AND FUTURE
1059 )
1060 )
1061 ) -- OR
1062 )-- AND
1063 )--Date eff
1064 )
1065
1066 /*
1067 AND ( BET.minor_revision_code IS NULL
1068 OR
1069 ( BET.minor_revision_code IS NOT NULL AND
1070 (
1071 ( BET.OBJ_NAME IS NOT NULL AND
1072 BET.minor_revision_id BETWEEN nvl(BIC.from_minor_revision_id,BET.minor_revision_id) AND
1073 nvl(BIC.to_minor_revision_id,BET.minor_revision_id))
1074 OR
1075 ( BET.OBJ_NAME IS NULL AND
1076 BET.minor_revision_code >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1077 to_char(nvl(BIC.from_minor_revision_id,0)))
1078 FROM mtl_item_revisions_b WHERE revision_id = BIC.FROM_OBJECT_REVISION_ID)
1079 AND (BIC.to_object_revision_id IS NULL OR
1080 BET.minor_revision_code <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),
1081 to_char(nvl(BIC.to_minor_revision_id,9999999999999999)))
1082 FROM mtl_item_revisions_b WHERE revision_id = BIC.TO_OBJECT_REVISION_ID))
1083 )
1084 )
1085 )
1086 )
1087 */
1088 AND BET.LOOP_FLAG = 2
1089 AND ( filter_pbom IS NULL
1090 OR
1091 EXISTS (SELECT null FROM ego_items_v WHERE inventory_item_id = BIC.component_item_id AND
1092 organization_id = NVL(BET.COMMON_ORGANIZATION_ID,BET.ORGANIZATION_ID) AND
1093 TRADE_ITEM_DESCRIPTOR IS NOT NULL)
1094 )
1095 /*
1096 ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER,
1097 decode(c_order_by, 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
1098 decode(c_order_by, 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM) */;
1099
1100 Cursor Get_Locator (P_Locator in number) is
1101 Select mil.concatenated_segments
1102 From mtl_item_locations_kfv mil
1103 Where mil.inventory_location_id = P_Locator;
1104
1105 Cursor Get_OLTP (P_Assembly in number,
1106 P_Alternate in varchar2,
1107 P_Operation in number) is
1108 Select round(bos.operation_lead_time_percent, 2) oltp
1109 From Bom_Operation_Sequences bos,
1110 Bom_Operational_Routings bor
1111 Where bor.assembly_item_id = P_Assembly
1112 And bor.organization_Id = org_id
1113 And (bor.alternate_routing_designator = P_Alternate
1114 or
1115 (bor.alternate_routing_designator is null AND not exists (
1116 SELECT null
1117 FROM bom_operational_routings bor2
1118 WHERE bor2.assembly_item_id = P_Assembly
1119 AND bor2.organization_id = org_id
1120 AND bor2.alternate_routing_designator = P_Alternate)
1121 ))
1122 And bor.common_routing_sequence_id = bos.routing_sequence_id
1123 And bos.operation_seq_num = P_Operation
1124 And bos.effectivity_date <=
1125 trunc(rev_date)
1126 And nvl(bos.disable_date,
1127 rev_date + 1) >=
1128 trunc(rev_date);
1129
1130 Cursor Calculate_Offset(P_ParentItem in number, P_Percent in number) is
1131 Select P_Percent/100 * msi.full_lead_time offset
1132 From mtl_system_items_b msi
1133 Where msi.inventory_item_id = P_ParentItem
1134 And msi.organization_id = Org_Id;
1135
1136 No_Revision_Found exception;
1137 Pragma exception_init(no_revision_found, -20001);
1138
1139 Cursor l_TopBill_csr is
1140 Select msi.concatenated_segments,
1141 bom.alternate_bom_designator
1142 From mtl_system_items_b_kfv msi,
1143 bom_structures_b bom,
1144 BOM_EXPLOSIONS_ALL bet
1145 Where msi.inventory_item_id = bom.assembly_item_id
1146 And msi.organization_id = bom.organization_id
1147 And bom.bill_sequence_id = bet.top_bill_sequence_id
1148 And bet.group_id = grp_id
1149 And rownum = 1;
1150
1151 total number;
1152
1153 /*
1154 CURSOR getItemRevDetails (p_revision_id IN NUMBER) IS
1155 SELECT revision_id, revision, revision_label FROM mtl_item_revisions_b WHERE revision_id = p_revision_id;
1156 */
1157
1158 CURSOR getCurrentMinorRev (p_obj_name IN VARCHAR2,
1159 p_pk1_value IN VARCHAR2,
1160 p_pk2_value IN VARCHAR2,
1161 p_pk3_value IN VARCHAR2) IS
1162 SELECT nvl(max(minor_revision_id),0) minor_revision_id FROM ego_minor_revisions
1163 WHERE obj_name = p_obj_name AND
1164 pk1_value = p_pk1_value AND
1165 nvl(pk2_value,'-1') = nvl(p_pk2_value,'-1') AND
1166 nvl(pk3_value,'-1') = nvl(p_pk3_value,'-1');
1167
1168 CURSOR getCurrentMinorRevCode (p_revision_id IN NUMBER,
1169 p_minor_rev_id IN NUMBER) IS
1170 SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b
1171 WHERE revision_id = p_revision_id;
1172
1173
1174 CURSOR getItemRevision (p_inventory_item_id IN NUMBER,
1175 p_organization_id IN NUMBER,
1176 p_revision_date IN DATE,
1177 p_impl_flag IN NUMBER) IS
1178 SELECT revision,revision_label,revision_id
1179 FROM mtl_item_revisions_b MIR
1180 WHERE mir.inventory_item_id = p_inventory_item_id
1181 AND mir.organization_id = p_organization_id
1182 AND mir.effectivity_date <= p_revision_date
1183 AND (p_impl_flag = 2 OR (p_impl_flag = 1 AND mir.implementation_date IS NOT NULL) )
1184 ORDER BY mir.effectivity_date DESC;
1185
1186 CURSOR checkPkgHkyStructureType (p_structure_type_id IN NUMBER) IS
1187 SELECT nvl(max(structure_type_id),-1) structure_type_id FROM bom_structure_types_b WHERE structure_type_name ='Packaging Hierarchy'
1188 AND structure_type_id = p_structure_type_id;
1189
1190 CURSOR c_Pkg_Structure_Type IS
1191 SELECT structure_type_id
1192 FROM bom_structure_types_b
1193 WHERE structure_type_name = 'Packaging Hierarchy';
1194
1195 CURSOR getItemRevDetails (p_revision_id IN NUMBER) IS
1196 SELECT
1197 DECODE( SIGN(high_date-SYSDATE),
1198 -1 ,
1199 'P',
1200 1 ,
1201 DECODE( SIGN(effectivity_date-SYSDATE),
1202 1 ,
1203 'F',
1204 'C'
1205 ) ,
1206 0 ,
1207 'C'
1208 ) Revision_Scope,
1209 DECODE( SIGN(high_date-SYSDATE),
1210 -1 ,
1211 high_date,
1212 1 ,
1213 DECODE( SIGN(effectivity_date-SYSDATE),
1214 1 ,
1215 effectivity_date,
1216 SYSDATE
1217 ) ,
1218 0 ,
1219 SYSDATE
1220 ) Revision_high_date,
1221 Effectivity_Date, High_Date, Implementation_Date,
1222 Inventory_Item_Id, Organization_Id, Revision, Revision_label
1223 FROM
1224 ( SELECT
1225 rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
1226 NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
1227 GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
1228 ) High_Date,
1229 rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
1230 FROM Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
1231 WHERE rev1.revision_id = p_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
1232 AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
1233 AND rev2.implementation_date (+) IS NOT NULL
1234 GROUP BY rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
1235 rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label);
1236
1237 l_revision_id NUMBER;
1238 l_revision_label VARCHAR2(100);
1239 l_revision VARCHAR2(10);
1240
1241 l_comp_common_bill_seq_id NUMBER;
1242
1243 /*
1244 TYPE be_temp_TYPE IS TABLE OF bom_plm_explosion_temp%ROWTYPE;
1245 be_temp_TBL be_temp_TYPE;
1246 */
1247
1248 --l_batch_size NUMBER := 20000;
1249 l_batch_size NUMBER := 10000;
1250
1251 /* Declare pl/sql tables for all coulmns in the select list. BULK BIND and INSERT with
1252 pl/sql table of records work fine in 9i releases but not in 8i. So, the only option is
1253 to use individual pl/sql table for each column in the cursor select list */
1254
1255
1256 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER
1257 INDEX BY BINARY_INTEGER;
1258
1259 TYPE DATE_TBL_TYPE IS TABLE OF DATE
1260 INDEX BY BINARY_INTEGER;
1261
1262 /* Declared seperate tables based on the column size since pl/sql preallocates the memory for the varchar variable
1263 when it is lesser than 2000 chars */
1264
1265 /*
1266 TYPE VARCHAR2_TBL_TYPE IS TABLE OF VARCHAR2(2000)
1267 INDEX BY BINARY_INTEGER;
1268 */
1269
1270 TYPE VARCHAR2_TBL_TYPE_1 IS TABLE OF VARCHAR2(1)
1271 INDEX BY BINARY_INTEGER;
1272
1273 TYPE VARCHAR2_TBL_TYPE_3 IS TABLE OF VARCHAR2(3)
1274 INDEX BY BINARY_INTEGER;
1275
1276 TYPE VARCHAR2_TBL_TYPE_10 IS TABLE OF VARCHAR2(10)
1277 INDEX BY BINARY_INTEGER;
1278
1279 TYPE VARCHAR2_TBL_TYPE_20 IS TABLE OF VARCHAR2(20)
1280 INDEX BY BINARY_INTEGER;
1281
1282 TYPE VARCHAR2_TBL_TYPE_25 IS TABLE OF VARCHAR2(25)
1283 INDEX BY BINARY_INTEGER;
1284
1285 TYPE VARCHAR2_TBL_TYPE_30 IS TABLE OF VARCHAR2(30)
1286 INDEX BY BINARY_INTEGER;
1287
1288 TYPE VARCHAR2_TBL_TYPE_40 IS TABLE OF VARCHAR2(40)
1289 INDEX BY BINARY_INTEGER;
1290
1291 TYPE VARCHAR2_TBL_TYPE_80 IS TABLE OF VARCHAR2(80)
1292 INDEX BY BINARY_INTEGER;
1293
1294 TYPE VARCHAR2_TBL_TYPE_150 IS TABLE OF VARCHAR2(150)
1295 INDEX BY BINARY_INTEGER;
1296
1297 TYPE VARCHAR2_TBL_TYPE_240 IS TABLE OF VARCHAR2(240)
1298 INDEX BY BINARY_INTEGER;
1299
1300 TYPE VARCHAR2_TBL_TYPE_260 IS TABLE OF VARCHAR2(260)
1301 INDEX BY BINARY_INTEGER;
1302
1303 TYPE VARCHAR2_TBL_TYPE_1000 IS TABLE OF VARCHAR2(1000)
1304 INDEX BY BINARY_INTEGER;
1305
1306 TYPE VARCHAR2_TBL_TYPE_2000 IS TABLE OF VARCHAR2(2000)
1307 INDEX BY BINARY_INTEGER;
1308
1309 TYPE VARCHAR2_TBL_TYPE_4000 IS TABLE OF VARCHAR2(4000)
1310 INDEX BY BINARY_INTEGER;
1311
1312 top_bill_sequence_id_tbl NUMBER_TBL_TYPE;
1313 bill_sequence_id_tbl NUMBER_TBL_TYPE;
1314 common_bill_sequence_id_tbl NUMBER_TBL_TYPE;
1315 common_organization_id_tbl NUMBER_TBL_TYPE;
1316 organization_id_tbl NUMBER_TBL_TYPE;
1317 component_sequence_id_tbl NUMBER_TBL_TYPE;
1318 component_item_id_tbl NUMBER_TBL_TYPE;
1319 basis_type_tbl NUMBER_TBL_TYPE;
1320 component_quantity_tbl NUMBER_TBL_TYPE;
1321 plan_level_tbl NUMBER_TBL_TYPE;
1322 extended_quantity_tbl NUMBER_TBL_TYPE;
1323 sort_order_tbl VARCHAR2_TBL_TYPE_2000;
1324 group_id_tbl NUMBER_TBL_TYPE;
1325 top_alternate_designator_tbl VARCHAR2_TBL_TYPE_10;
1326 component_yield_factor_tbl NUMBER_TBL_TYPE;
1327 top_item_id_tbl NUMBER_TBL_TYPE;
1328 component_code_tbl VARCHAR2_TBL_TYPE_1000;
1329 include_in_cost_rollup_tbl NUMBER_TBL_TYPE;
1330 loop_flag_tbl NUMBER_TBL_TYPE;
1331 planning_factor_tbl NUMBER_TBL_TYPE;
1332 operation_seq_num_tbl NUMBER_TBL_TYPE;
1333 bom_item_type_tbl NUMBER_TBL_TYPE;
1334 parent_bom_item_type_tbl NUMBER_TBL_TYPE;
1335 parent_item_id_tbl NUMBER_TBL_TYPE;
1336 alternate_bom_designator_tbl VARCHAR2_TBL_TYPE_10;
1337 wip_supply_type_tbl NUMBER_TBL_TYPE;
1338 item_num_tbl NUMBER_TBL_TYPE;
1339 effectivity_date_tbl DATE_TBL_TYPE;
1340 disable_date_tbl DATE_TBL_TYPE;
1341 trimmed_effectivity_date_tbl DATE_TBL_TYPE;
1342 trimmed_disable_date_tbl DATE_TBL_TYPE;
1343 trimmed_from_unit_number_tbl VARCHAR2_TBL_TYPE_30;
1344 trimmed_to_unit_number_tbl VARCHAR2_TBL_TYPE_30;
1345 from_end_item_unit_number_tbl VARCHAR2_TBL_TYPE_30;
1346 to_end_item_unit_number_tbl VARCHAR2_TBL_TYPE_30;
1347 implementation_date_tbl DATE_TBL_TYPE;
1348 optional_tbl NUMBER_TBL_TYPE;
1349 supply_subinventory_tbl VARCHAR2_TBL_TYPE_10;
1350 supply_locator_id_tbl NUMBER_TBL_TYPE;
1351 component_remarks_tbl VARCHAR2_TBL_TYPE_240;
1352 change_notice_tbl VARCHAR2_TBL_TYPE_10;
1353 operation_leadtime_percent_tbl NUMBER_TBL_TYPE;
1354 mutually_exclusive_options_tbl NUMBER_TBL_TYPE;
1355 check_atp_tbl NUMBER_TBL_TYPE;
1356 required_to_ship_tbl NUMBER_TBL_TYPE;
1357 required_for_revenue_tbl NUMBER_TBL_TYPE;
1358 include_on_ship_docs_tbl NUMBER_TBL_TYPE;
1359 low_quantity_tbl NUMBER_TBL_TYPE;
1360 high_quantity_tbl NUMBER_TBL_TYPE;
1361 so_basis_tbl NUMBER_TBL_TYPE;
1362 operation_offset_tbl NUMBER_TBL_TYPE;
1363 current_revision_tbl VARCHAR2_TBL_TYPE_3;
1364 primary_uom_code_tbl VARCHAR2_TBL_TYPE_3;
1365 primary_uom_desc_tbl VARCHAR2_TBL_TYPE_80;
1366 locator_tbl VARCHAR2_TBL_TYPE_40;
1367 attribute_category_tbl VARCHAR2_TBL_TYPE_30;
1368 attribute1_tbl VARCHAR2_TBL_TYPE_150;
1369 attribute2_tbl VARCHAR2_TBL_TYPE_150;
1370 attribute3_tbl VARCHAR2_TBL_TYPE_150;
1371 attribute4_tbl VARCHAR2_TBL_TYPE_150;
1372 attribute5_tbl VARCHAR2_TBL_TYPE_150;
1373 attribute6_tbl VARCHAR2_TBL_TYPE_150;
1374 attribute7_tbl VARCHAR2_TBL_TYPE_150;
1375 attribute8_tbl VARCHAR2_TBL_TYPE_150;
1376 attribute9_tbl VARCHAR2_TBL_TYPE_150;
1377 attribute10_tbl VARCHAR2_TBL_TYPE_150;
1378 attribute11_tbl VARCHAR2_TBL_TYPE_150;
1379 attribute12_tbl VARCHAR2_TBL_TYPE_150;
1380 attribute13_tbl VARCHAR2_TBL_TYPE_150;
1381 attribute14_tbl VARCHAR2_TBL_TYPE_150;
1382 attribute15_tbl VARCHAR2_TBL_TYPE_150;
1383 obj_name_tbl VARCHAR2_TBL_TYPE_30;
1384 pk1_value_tbl VARCHAR2_TBL_TYPE_240;
1385 pk2_value_tbl VARCHAR2_TBL_TYPE_240;
1386 pk3_value_tbl VARCHAR2_TBL_TYPE_240;
1387 pk4_value_tbl VARCHAR2_TBL_TYPE_240;
1388 pk5_value_tbl VARCHAR2_TBL_TYPE_240;
1389 from_end_item_rev_id_tbl NUMBER_TBL_TYPE;
1390 from_end_item_minor_rev_id_tbl NUMBER_TBL_TYPE;
1391 to_end_item_rev_id_tbl NUMBER_TBL_TYPE;
1392 to_end_item_minor_rev_id_tbl NUMBER_TBL_TYPE;
1393 new_component_code_tbl VARCHAR2_TBL_TYPE_4000;
1394 parent_sort_order_tbl VARCHAR2_TBL_TYPE_2000;
1395 comp_common_bill_seq_tbl NUMBER_TBL_TYPE;
1396 comp_bill_seq_tbl NUMBER_TBL_TYPE;
1397 access_flag_tbl VARCHAR2_TBL_TYPE_1;
1398 eng_item_flag_tbl VARCHAR2_TBL_TYPE_1;
1399 assembly_type_tbl NUMBER_TBL_TYPE;
1400 revision_label_tbl VARCHAR2_TBL_TYPE_260;
1401 revision_id_tbl NUMBER_TBL_TYPE;
1402 effectivity_control_tbl NUMBER_TBL_TYPE;
1403 object_rev_id_tbl NUMBER_TBL_TYPE;
1404 minor_rev_id_tbl NUMBER_TBL_TYPE;
1405 minor_rev_code_tbl VARCHAR2_TBL_TYPE_30;
1406 from_object_rev_id_tbl NUMBER_TBL_TYPE;
1407 from_minor_rev_id_tbl NUMBER_TBL_TYPE;
1408 to_object_rev_id_tbl NUMBER_TBL_TYPE;
1409 to_minor_rev_id_tbl NUMBER_TBL_TYPE;
1410 component_item_revision_id_tbl NUMBER_TBL_TYPE;
1411 component_minorrevision_id_tbl NUMBER_TBL_TYPE;
1412 bom_implementation_date_tbl DATE_TBL_TYPE;
1413 gtin_number_tbl VARCHAR2_TBL_TYPE_30;
1414 gtin_description_tbl VARCHAR2_TBL_TYPE_240;
1415 trade_item_descriptor_tbl VARCHAR2_TBL_TYPE_40;
1416 trade_item_descriptor_desc_tbl VARCHAR2_TBL_TYPE_80;
1417 top_gtin_number_tbl VARCHAR2_TBL_TYPE_30;
1418 top_gtin_description_tbl VARCHAR2_TBL_TYPE_240;
1419 top_trade_item_descriptor_tbl VARCHAR2_TBL_TYPE_40;
1420 parent_gtin_number_tbl VARCHAR2_TBL_TYPE_30;
1421 parent_gtin_description_tbl VARCHAR2_TBL_TYPE_240;
1422 parent_trade_descriptor_tbl VARCHAR2_TBL_TYPE_40;
1423 creation_date_tbl DATE_TBL_TYPE;
1424 created_by_tbl NUMBER_TBL_TYPE;
1425 last_update_date_tbl DATE_TBL_TYPE;
1426 last_updated_by_tbl NUMBER_TBL_TYPE;
1427 gtin_publication_status_tbl VARCHAR2_TBL_TYPE_1;
1428 auto_request_material_tbl VARCHAR2_TBL_TYPE_1;
1429 rexplode_flag_tbl VARCHAR2_TBL_TYPE_1;
1430 explode_option_tbl NUMBER_TBL_TYPE;
1431 change_policy_val_tbl VARCHAR2_TBL_TYPE_240;
1432 acd_type_tbl NUMBER_TBL_TYPE;
1433 quantity_related_tbl NUMBER_TBL_TYPE;
1434 structure_type_id_tbl NUMBER_TBL_TYPE;
1435 comp_fixed_rev_high_date_tbl DATE_TBL_TYPE;
1436 comp_fixed_revision_id_tbl NUMBER_TBL_TYPE;
1437 parent_comp_sequence_id_tbl NUMBER_TBL_TYPE;
1438 is_preferred_tbl VARCHAR2_TBL_TYPE_1;
1439 parent_impl_date_tbl DATE_TBL_TYPE;
1440 parent_change_notice_tbl VARCHAR2_TBL_TYPE_10;
1441 source_bill_sequence_id_tbl NUMBER_TBL_TYPE;
1442 common_component_seq_id_tbl NUMBER_TBL_TYPE;
1443 comp_source_bill_seq_tbl NUMBER_TBL_TYPE;
1444 comp_effectivity_control_tbl NUMBER_TBL_TYPE;
1445
1446 l_rows_fetched NUMBER := 0;
1447 l_Pkg_Structure_Type_Id NUMBER := -1;
1448 BEGIN
1449
1450 --Dbms_Output.put_line('end item revision id is '||end_item_rev_id);
1451 --Dbms_Output.put_line('end item id is '||end_item_id);
1452 --Dbms_Output.put_line('end item org id is '||end_item_org_id);
1453 --Dbms_Output.put_line('Group Id '||grp_id);
1454
1455 /* Pre-fetch the structure type id for Packaging Hierarchy seeded type */
1456 FOR pkg_type IN c_Pkg_Structure_Type
1457 LOOP
1458 l_Pkg_Structure_Type_Id := pkg_type.structure_type_id;
1459 END LOOP;
1460
1461 FOR cur_level in 1..levels_to_explode
1462 LOOP
1463
1464 --Dbms_Output.put_line('for plan level '||to_char(cur_level-1));
1465
1466 total_rows := 0;
1467 cum_count := 0;
1468
1469 OPEN exploder (
1470 cur_level,
1471 grp_id,
1472 org_id,
1473 bom_or_eng,
1474 rev_date,
1475 impl_flag,
1476 explode_option,
1477 order_by,
1478 verify_flag,
1479 plan_factor_flag,
1480 std_comp_flag,
1481 incl_oc_flag
1482 );
1483
1484 l_rows_fetched := 0;
1485
1486 LOOP
1487 --dbms_output.put_line('cur level is '||cur_level);
1488
1489 -- FETCH exploder BULK COLLECT INTO be_temp_TBL LIMIT l_batch_size;
1490
1491 --
1492 -- Insert attachments
1493 --
1494
1495 /*
1496 IF (exploder%ROWCOUNT <> 0)
1497 THEN
1498 --Dbms_Output.put_line('Inserting attachments for level: ' || cur_level);
1499 Insert_Attachments( p_group_id => grp_id
1500 , p_plan_level => cur_level
1501 );
1502 END IF;
1503 */
1504
1505 FETCH exploder BULK COLLECT INTO
1506 top_bill_sequence_id_tbl ,
1507 bill_sequence_id_tbl ,
1508 common_bill_sequence_id_tbl ,
1509 common_organization_id_tbl ,
1510 organization_id_tbl ,
1511 component_sequence_id_tbl ,
1512 component_item_id_tbl ,
1513 basis_type_tbl ,
1514 component_quantity_tbl ,
1515 plan_level_tbl ,
1516 extended_quantity_tbl ,
1517 sort_order_tbl ,
1518 group_id_tbl ,
1519 top_alternate_designator_tbl ,
1520 component_yield_factor_tbl ,
1521 top_item_id_tbl ,
1522 component_code_tbl ,
1523 include_in_cost_rollup_tbl ,
1524 loop_flag_tbl ,
1525 planning_factor_tbl ,
1526 operation_seq_num_tbl ,
1527 bom_item_type_tbl ,
1528 parent_bom_item_type_tbl ,
1529 parent_item_id_tbl ,
1530 alternate_bom_designator_tbl ,
1531 wip_supply_type_tbl ,
1532 item_num_tbl ,
1533 effectivity_date_tbl ,
1534 disable_date_tbl ,
1535 trimmed_effectivity_date_tbl ,
1536 trimmed_disable_date_tbl ,
1537 from_end_item_unit_number_tbl ,
1538 to_end_item_unit_number_tbl ,
1539 trimmed_from_unit_number_tbl,
1540 trimmed_to_unit_number_tbl,
1541 implementation_date_tbl ,
1542 optional_tbl ,
1543 supply_subinventory_tbl ,
1544 supply_locator_id_tbl ,
1545 component_remarks_tbl ,
1546 change_notice_tbl ,
1547 operation_leadtime_percent_tbl ,
1548 mutually_exclusive_options_tbl ,
1549 check_atp_tbl ,
1550 required_to_ship_tbl ,
1551 required_for_revenue_tbl ,
1552 include_on_ship_docs_tbl ,
1553 low_quantity_tbl ,
1554 high_quantity_tbl ,
1555 so_basis_tbl ,
1556 operation_offset_tbl ,
1557 Current_revision_tbl ,
1558 locator_tbl ,
1559 attribute_category_tbl ,
1560 attribute1_tbl ,
1561 attribute2_tbl ,
1562 attribute3_tbl ,
1563 attribute4_tbl ,
1564 attribute5_tbl ,
1565 attribute6_tbl ,
1566 attribute7_tbl ,
1567 attribute8_tbl ,
1568 attribute9_tbl ,
1569 attribute10_tbl ,
1570 attribute11_tbl ,
1571 attribute12_tbl ,
1572 attribute13_tbl ,
1573 attribute14_tbl ,
1574 attribute15_tbl ,
1575 obj_name_tbl ,
1576 pk1_value_tbl ,
1577 pk2_value_tbl ,
1578 pk3_value_tbl ,
1579 pk4_value_tbl ,
1580 pk5_value_tbl ,
1581 from_end_item_rev_id_tbl ,
1582 from_end_item_minor_rev_id_tbl ,
1583 to_end_item_rev_id_tbl ,
1584 to_end_item_minor_rev_id_tbl ,
1585 new_component_code_tbl ,
1586 parent_sort_order_tbl ,
1587 comp_common_bill_seq_tbl ,
1588 comp_bill_seq_tbl ,
1589 access_flag_tbl ,
1590 assembly_type_tbl ,
1591 revision_label_tbl ,
1592 revision_id_tbl ,
1593 effectivity_control_tbl ,
1594 object_rev_id_tbl ,
1595 minor_rev_id_tbl ,
1596 minor_rev_code_tbl ,
1597 from_object_rev_id_tbl ,
1598 from_minor_rev_id_tbl ,
1599 to_object_rev_id_tbl ,
1600 to_minor_rev_id_tbl ,
1601 component_item_revision_id_tbl ,
1602 component_minorrevision_id_tbl ,
1603 bom_implementation_date_tbl ,
1604 top_gtin_number_tbl ,
1605 top_gtin_description_tbl ,
1606 top_trade_item_descriptor_tbl,
1607 parent_gtin_number_tbl ,
1608 parent_gtin_description_tbl ,
1609 parent_trade_descriptor_tbl,
1610 creation_date_tbl,
1611 created_by_tbl,
1612 last_update_date_tbl,
1613 last_updated_by_tbl ,
1614 auto_request_material_tbl,
1615 rexplode_flag_tbl,
1616 acd_type_tbl,
1617 quantity_related_tbl,
1618 change_policy_val_tbl,
1619 explode_option_tbl ,
1620 structure_type_id_tbl,
1621 comp_fixed_rev_high_date_tbl,
1622 comp_fixed_revision_id_tbl ,
1623 parent_comp_sequence_id_tbl,
1624 is_preferred_tbl,
1625 parent_impl_date_tbl,
1626 parent_change_notice_tbl,
1627 source_bill_sequence_id_tbl,
1628 common_component_seq_id_tbl,
1629 comp_source_bill_seq_tbl,
1630 comp_effectivity_control_tbl LIMIT l_batch_size;
1631
1632 --dbms_output.put_line('Befoe exit call: Row count is '||exploder%ROWCOUNT);
1633 --dbms_output.put_line('Befoe exit call: l_rows_fetched '||l_rows_fetched);
1634 --dbms_output.put_line('count if tbsi '||top_bill_sequence_id_tbl.COUNT);
1635
1636 EXIT WHEN exploder%ROWCOUNT = l_rows_fetched;
1637 l_rows_fetched := exploder%ROWCOUNT;
1638
1639 --dbms_output.put_line('Row count is '||exploder%ROWCOUNT);
1640
1641 FOR i IN 1..top_bill_sequence_id_tbl.COUNT
1642 LOOP
1643
1644 --dbms_output.put_line('inside expl_rows');
1645
1646 /*
1647 IF cur_level > levels_to_explode THEN
1648 IF cur_level > max_level THEN
1649 max_level_exceeded := true;
1650 END IF; -- exceed max level
1651 exit; -- do not insert extra level
1652 END IF; -- exceed lowest level
1653 */
1654
1655 total_rows := total_rows + 1;
1656
1657 -- Get the sort order
1658
1659 --dbms_output.put_line('calling sort order : '||parent_sort_order_tbl(i));
1660 sort_order_tbl(i) := Get_Sort_Order(parent_sort_order_tbl(i), component_quantity_tbl(i));
1661
1662 -- Get the component code
1663
1664 loop_found := FALSE;
1665 cur_loopstr := new_component_code_tbl(i);
1666
1667 IF obj_name_tbl(i) = 'DDD_CADVIEW' THEN
1668 cur_component := lpad('C'||pk1_value_tbl(i), 20, '0');
1669 ELSE
1670 cur_component := lpad('I'||component_item_id_tbl(i), 20, '0');
1671 END IF;
1672
1673 -- search the current loop_string for current component
1674
1675 FOR i IN 1..cur_level LOOP
1676 start_pos := 1+( (i-1) * 20 );
1677 cur_substr := SUBSTR( cur_loopstr, start_pos, 20 );
1678 IF (cur_component = cur_substr) THEN
1679 loop_found := TRUE;
1680 EXIT;
1681 END IF;
1682 END LOOP;
1683
1684 new_component_code_tbl(i) := new_component_code_tbl(i) || cur_component;
1685 IF loop_found THEN
1686 loop_flag_tbl(i) := 1;
1687 ELSE
1688 loop_flag_tbl(i) := 2;
1689 END IF;
1690
1691
1692 current_revision_tbl(i) := Null;
1693
1694 -- The following pieces are valid only IF the component row is an inventory item
1695
1696 IF obj_name_tbl(i) IS NULL THEN
1697
1698 -- Security check needs to be performed for all the component items
1699 -- Previously, we were doing it only if the component has a BOM
1700 -- Note: Security previleges are determined for the org from which the
1701 -- explosion was initiated. This is true even with a common BOM
1702
1703 -- Identify the access flag
1704
1705 access_flag_tbl(i) := 'T';
1706
1707 /* Security check should be moved to the view as it is user based
1708
1709 IF (G_EGOUser is null) THEN
1710 access_flag_tbl(i) := 'T';
1711 ELSE
1712 access_flag_tbl(i) := BOM_SECURITY_PUB.CHECK_USER_PRIVILEGE(
1713 p_api_version => 1,
1714 p_function => BOM_SECURITY_PUB.FUNCTION_NAME_TO_CHECK,
1715 p_object_name=>'EGO_ITEM',
1716 p_instance_pk1_value=> pk1_value_tbl(i),
1717 p_instance_pk2_value=> org_id, --be_temp_TBL(i).PK2_VALUE,
1718 p_user_name=> G_EGOUser);
1719 END IF;
1720 */
1721
1722
1723 IF obj_name_tbl(i) IS NULL THEN
1724 IF component_item_revision_id_tbl(i) IS NOT NULL THEN
1725 FOR r1 IN getItemRevDetails(component_item_revision_id_tbl(i))
1726 LOOP
1727 revision_id_tbl(i) := component_item_revision_id_tbl(i);
1728 current_revision_tbl(i) := r1.revision;
1729 revision_label_tbl(i) := r1.revision_label;
1730 comp_fixed_rev_high_date_tbl(i) := r1.revision_high_date;
1731 END LOOP;
1732 END IF;
1733 END IF;
1734
1735 IF show_rev = 1 THEN
1736
1737 IF component_item_revision_id_tbl(i) IS NULL THEN
1738
1739 FOR r1 IN getItemRevision(component_item_id_tbl(i),
1740 --nvl(common_organization_id_tbl(i),organization_id_tbl(i)),
1741 --common_organization_id_tbl(i),
1742 organization_id_tbl(i),
1743 rev_date,
1744 impl_flag)
1745 LOOP
1746 revision_id_tbl(i) := r1.revision_id;
1747 current_revision_tbl(i) := r1.revision;
1748 revision_label_tbl(i) := r1.revision_label;
1749 Exit;
1750 END LOOP;
1751
1752 END IF; -- current component revision
1753
1754 IF effectivity_control_tbl(i) = 4 THEN /* Minor rev code is required only for rev effective BOMs */
1755
1756 IF component_minorrevision_id_tbl(i) IS NOT NULL THEN
1757 minor_rev_id_tbl(i) := component_minorrevision_id_tbl(i);
1758 ELSE
1759 FOR r1 IN getCurrentMinorRev (p_obj_name => obj_name_tbl(i),
1760 p_pk1_value => pk1_value_tbl(i),
1761 p_pk2_value => pk2_value_tbl(i),
1762 p_pk3_value => Revision_id_tbl(i))
1763 LOOP
1764 minor_rev_id_tbl(i) := r1.minor_revision_id;
1765 END LOOP;
1766 END IF;
1767
1768 IF obj_name_tbl(i) IS NULL THEN
1769 FOR r1 IN getCurrentMinorRevCode (revision_id_tbl(i), minor_rev_id_tbl(i))
1770 LOOP
1771 minor_rev_code_tbl(i) := r1.mrev_code;
1772 END LOOP;
1773 ELSE
1774 minor_rev_code_tbl(i) := to_char(minor_rev_id_tbl(i));
1775 END IF;
1776
1777 END IF; -- effectivity control
1778
1779 END IF; -- show rev
1780
1781 locator_tbl(i) := Null;
1782
1783 IF material_ctrl = 1 THEN
1784
1785 IF FND_FLEX_KEYVAL.validate_ccid
1786 (appl_short_name => 'INV',
1787 key_flex_code => 'MTLL',
1788 structure_number => 101,
1789 combination_id => supply_locator_id_tbl(i),
1790 displayable => 'ALL',
1791 data_set => organization_id_tbl(i)
1792 )
1793 THEN
1794 locator_tbl(i) := FND_FLEX_KEYVAL.concatenated_values ;
1795 END IF;
1796
1797 END IF; -- supply locator
1798
1799 operation_leadtime_percent_tbl(i) := Null;
1800
1801 FOR X_Operation in Get_OLTP(
1802 P_Assembly => parent_item_id_tbl(i),
1803 P_Alternate => alternate_bom_designator_tbl(i),
1804 P_Operation => operation_seq_num_tbl(i))
1805 LOOP
1806 operation_leadtime_percent_tbl(i) := X_Operation.OLTP;
1807 END LOOP;
1808
1809 operation_offset_tbl(i) := Null;
1810
1811 IF lead_time = 1 THEN
1812 For X_Item in Calculate_Offset(P_ParentItem => parent_item_id_tbl(i),
1813 P_Percent => operation_leadtime_percent_tbl(i))
1814 LOOP
1815 operation_offset_tbl(i) := X_Item.offset;
1816 END LOOP;
1817 END IF; -- operation offset
1818
1819 -- Get the GTIN attributes if the component is an item
1820
1821 IF structure_type_id_tbl(i) = l_Pkg_structure_type_id
1822 THEN
1823 SELECT gtin
1824 , description
1825 , trade_item_descriptor
1826 , primary_uom_code
1827 , eng_item_flag
1828 , primary_uom_code_desc
1829 , trade_item_descriptor_desc
1830 , publication_status
1831 INTO gtin_number_tbl(i)
1832 , gtin_description_tbl(i)
1833 , trade_item_descriptor_tbl(i)
1834 , primary_uom_code_tbl(i)
1835 , eng_item_flag_tbl(i)
1836 , primary_uom_desc_tbl(i)
1837 , trade_item_descriptor_desc_tbl(i)
1838 ,gtin_publication_status_tbl(i)
1839 FROM ego_items_v egi
1840 WHERE inventory_item_id = component_item_id_tbl(i)
1841 --AND organization_id = nvl(common_organization_id_tbl(i),organization_id_tbl(i));
1842 AND organization_id = common_organization_id_tbl(i);
1843 ELSE
1844
1845 gtin_number_tbl(i) := null;
1846 gtin_description_tbl(i) := null;
1847 trade_item_descriptor_tbl(i) := null;
1848 gtin_publication_status_tbl(i) := null;
1849 trade_item_descriptor_desc_tbl(i) := null;
1850 top_gtin_number_tbl(i) := null;
1851 top_gtin_description_tbl(i) := null;
1852 top_trade_item_descriptor_tbl(i) := null;
1853 parent_gtin_number_tbl(i) := null;
1854 parent_gtin_description_tbl(i) := null;
1855 parent_trade_descriptor_tbl(i) := null;
1856 eng_item_flag_tbl(i) := null;
1857 primary_uom_desc_tbl(i) := null;
1858 primary_uom_code_tbl(i) := null;
1859
1860 /*
1861 SELECT msi.primary_uom_code
1862 , msi.eng_item_flag
1863 , mum.description
1864 INTO primary_uom_code_tbl(i)
1865 , eng_item_flag_tbl(i)
1866 , primary_uom_desc_tbl(i)
1867 FROM mtl_system_items_b msi
1868 --, mtl_units_of_measure mum
1869 , mtl_units_of_measure_tl mum
1870 WHERE msi.inventory_item_id = component_item_id_tbl(i)
1871 AND msi.organization_id = nvl(common_organization_id_tbl(i),
1872 organization_id_tbl(i))
1873 AND msi.primary_uom_code = mum.uom_code
1874 AND mum.language = userenv('LANG');
1875
1876 gtin_number_tbl(i) := null;
1877 gtin_description_tbl(i) := null;
1878 trade_item_descriptor_tbl(i) := null;
1879 gtin_publication_status_tbl(i) := null;
1880 trade_item_descriptor_desc_tbl(i) := null;
1881 top_gtin_number_tbl(i) := null;
1882 top_gtin_description_tbl(i) := null;
1883 top_trade_item_descriptor_tbl(i) := null;
1884 parent_gtin_number_tbl(i) := null;
1885 parent_gtin_description_tbl(i) := null;
1886 parent_trade_descriptor_tbl(i) := null;
1887 */
1888
1889 END IF;
1890
1891 ELSE
1892 gtin_number_tbl(i) := null;
1893 gtin_description_tbl(i) := null;
1894 trade_item_descriptor_tbl(i) := null;
1895 gtin_publication_status_tbl(i) := null;
1896 trade_item_descriptor_desc_tbl(i) := null;
1897 top_gtin_number_tbl(i) := null;
1898 top_gtin_description_tbl(i) := null;
1899 top_trade_item_descriptor_tbl(i) := null;
1900 parent_gtin_number_tbl(i) := null;
1901 parent_gtin_description_tbl(i) := null;
1902 parent_trade_descriptor_tbl(i) := null;
1903 eng_item_flag_tbl(i) := null;
1904 primary_uom_desc_tbl(i) := null;
1905 primary_uom_code_tbl(i) := null;
1906 END IF; -- Check for obj_name is null ends
1907
1908 IF comp_bill_seq_tbl(i) <> 0 THEN -- If the component has a BOM
1909
1910 object_rev_id_tbl(i) := revision_id_tbl(i);
1911
1912 -- If there is a BOM for this component and then find out the current
1913 -- minor rev id and code for this component's revision
1914
1915 /*
1916 IF component_minorrevision_id_tbl(i) IS NOT NULL THEN
1917
1918 minor_rev_id_tbl(i) := component_minorrevision_id_tbl(i);
1919
1920 ELSE
1921
1922 FOR r1 IN getCurrentMinorRev (p_obj_name => obj_name_tbl(i),
1923 p_pk1_value => pk1_value_tbl(i),
1924 p_pk2_value => pk2_value_tbl(i),
1925 p_pk3_value => Revision_id_tbl(i))
1926 LOOP
1927 minor_rev_id_tbl(i) := r1.minor_revision_id;
1928 END LOOP;
1929
1930 END IF;
1931
1932 IF obj_name_tbl(i) IS NULL THEN
1933 FOR r1 IN getCurrentMinorRevCode (revision_id_tbl(i), minor_rev_id_tbl(i))
1934 LOOP
1935 minor_rev_code_tbl(i) := r1.mrev_code;
1936 END LOOP;
1937 ELSE
1938 minor_rev_code_tbl(i) := to_char(minor_rev_id_tbl(i));
1939 END IF;
1940 */
1941
1942 SELECT max(common_bill_sequence_id), max(structure_type_id), max(is_preferred),
1943 max(implementation_date), max(source_bill_sequence_id),max(assembly_type), max(effectivity_control)
1944 INTO comp_common_bill_seq_tbl(i), structure_type_id_tbl(i), is_preferred_tbl(i), bom_implementation_date_tbl(i),
1945 comp_source_bill_seq_tbl(i),assembly_type_tbl(i), comp_effectivity_control_tbl(i)
1946 FROM bom_structures_b WHERE
1947 bill_sequence_id = comp_bill_seq_tbl(i);
1948
1949 -- Update the change policy value if the component has a bill
1950 /*
1951 IF (comp_bill_seq_tbl(i) <> 0) THEN
1952 change_policy_val_tbl(i) := Get_Change_Policy_Val(revision_id_tbl(i), comp_bill_seq_tbl(i));
1953 END IF;
1954 */
1955
1956 ELSE
1957
1958 comp_bill_seq_tbl(i) := null;
1959
1960 END IF; -- If the component has a BOM ends here
1961
1962 END LOOP;
1963
1964 -- We are doing this to capture the values for the last parent
1965 g_parent_sort_order_tbl(g_global_count) := g_parent_sort_order;
1966 g_quantity_of_children_tbl(g_global_count) := g_sort_count;
1967 g_total_qty_at_next_level_tbl(g_global_count) := g_total_quantity;
1968
1969 /*
1970 FORALL i IN 1..be_temp_TBL.COUNT
1971 INSERT INTO bom_plm_explosion_temp VALUES be_temp_TBL(i);
1972 */
1973
1974 FORALL i IN 1..top_bill_sequence_id_tbl.COUNT
1975 --INSERT /*+append */ INTO BOM_EXPLOSIONS_ALL
1976 INSERT INTO BOM_EXPLOSIONS_ALL
1977 (
1978 TOP_BILL_SEQUENCE_ID ,
1979 BILL_SEQUENCE_ID ,
1980 COMMON_BILL_SEQUENCE_ID ,
1981 COMMON_ORGANIZATION_ID ,
1982 ORGANIZATION_ID ,
1983 COMPONENT_SEQUENCE_ID ,
1984 COMPONENT_ITEM_ID ,
1985 BASIS_TYPE ,
1986 COMPONENT_QUANTITY ,
1987 PLAN_LEVEL ,
1988 EXTENDED_QUANTITY ,
1989 SORT_ORDER ,
1990 GROUP_ID ,
1991 TOP_ALTERNATE_DESIGNATOR ,
1992 COMPONENT_YIELD_FACTOR ,
1993 TOP_ITEM_ID ,
1994 COMPONENT_CODE ,
1995 INCLUDE_IN_ROLLUP_FLAG ,
1996 LOOP_FLAG ,
1997 PLANNING_FACTOR ,
1998 OPERATION_SEQ_NUM ,
1999 BOM_ITEM_TYPE ,
2000 PARENT_BOM_ITEM_TYPE ,
2001 PRIMARY_UOM_CODE ,
2002 PRIMARY_UNIT_OF_MEASURE ,
2003 ASSEMBLY_ITEM_ID ,
2004 ALTERNATE_BOM_DESIGNATOR ,
2005 WIP_SUPPLY_TYPE ,
2006 ITEM_NUM ,
2007 EFFECTIVITY_DATE ,
2008 DISABLE_DATE ,
2009 TRIMMED_EFFECTIVITY_DATE ,
2010 TRIMMED_DISABLE_DATE ,
2011 TRIMMED_FROM_UNIT_NUMBER ,
2012 TRIMMED_TO_UNIT_NUMBER ,
2013 FROM_END_ITEM_UNIT_NUMBER ,
2014 TO_END_ITEM_UNIT_NUMBER ,
2015 IMPLEMENTATION_DATE ,
2016 OPTIONAL ,
2017 SUPPLY_SUBINVENTORY ,
2018 SUPPLY_LOCATOR_ID ,
2019 COMPONENT_REMARKS ,
2020 CHANGE_NOTICE ,
2021 OPERATION_LEAD_TIME_PERCENT ,
2022 MUTUALLY_EXCLUSIVE_OPTIONS ,
2023 CHECK_ATP ,
2024 REQUIRED_TO_SHIP ,
2025 REQUIRED_FOR_REVENUE ,
2026 INCLUDE_ON_SHIP_DOCS ,
2027 LOW_QUANTITY ,
2028 HIGH_QUANTITY ,
2029 SO_BASIS ,
2030 OPERATION_OFFSET ,
2031 CURRENT_REVISION ,
2032 LOCATOR ,
2033 CONTEXT ,
2034 ATTRIBUTE1 ,
2035 ATTRIBUTE2 ,
2036 ATTRIBUTE3 ,
2037 ATTRIBUTE4 ,
2038 ATTRIBUTE5 ,
2039 ATTRIBUTE6 ,
2040 ATTRIBUTE7 ,
2041 ATTRIBUTE8 ,
2042 ATTRIBUTE9 ,
2043 ATTRIBUTE10 ,
2044 ATTRIBUTE11 ,
2045 ATTRIBUTE12 ,
2046 ATTRIBUTE13 ,
2047 ATTRIBUTE14 ,
2048 ATTRIBUTE15 ,
2049 OBJ_NAME ,
2050 PK1_VALUE ,
2051 PK2_VALUE ,
2052 PK3_VALUE ,
2053 PK4_VALUE ,
2054 PK5_VALUE ,
2055 FROM_END_ITEM_REV_ID ,
2056 FROM_END_ITEM_MINOR_REV_ID ,
2057 TO_END_ITEM_REV_ID ,
2058 TO_END_ITEM_MINOR_REV_ID ,
2059 NEW_COMPONENT_CODE ,
2060 PARENT_SORT_ORDER ,
2061 COMP_COMMON_BILL_SEQ_ID ,
2062 COMP_BILL_SEQ_ID ,
2063 ACCESS_FLAG ,
2064 ENG_ITEM_FLAG ,
2065 ASSEMBLY_TYPE ,
2066 REVISION_LABEL ,
2067 REVISION_ID ,
2068 EFFECTIVITY_CONTROL ,
2069 OBJECT_REVISION_ID ,
2070 MINOR_REVISION_ID ,
2071 MINOR_REVISION_CODE ,
2072 FROM_OBJECT_REVISION_ID ,
2073 FROM_MINOR_REVISION_ID ,
2074 TO_OBJECT_REVISION_ID ,
2075 TO_MINOR_REVISION_ID ,
2076 COMPONENT_ITEM_REVISION_ID ,
2077 COMPONENT_MINOR_REVISION_ID ,
2078 BOM_IMPLEMENTATION_DATE ,
2079 GTIN_NUMBER ,
2080 GTIN_DESCRIPTION ,
2081 TRADE_ITEM_DESCRIPTOR ,
2082 TRADE_ITEM_DESCRIPTOR_DESC ,
2083 GTIN_PUBLICATION_STATUS ,
2084 TOP_GTIN_NUMBER ,
2085 TOP_GTIN_DESCRIPTION ,
2086 TOP_TRADE_ITEM_DESCRIPTOR,
2087 PARENT_GTIN_NUMBER ,
2088 PARENT_GTIN_DESCRIPTION ,
2089 PARENT_TRADE_ITEM_DESCRIPTOR ,
2090 CREATION_DATE ,
2091 CREATED_BY ,
2092 LAST_UPDATE_DATE ,
2093 LAST_UPDATED_BY ,
2094 AUTO_REQUEST_MATERIAL,
2095 REXPLODE_FLAG,
2096 ACD_TYPE,
2097 QUANTITY_RELATED,
2098 CHANGE_POLICY_VALUE,
2099 EXPLODED_OPTION,
2100 COMP_FIXED_REV_HIGH_DATE,
2101 COMP_FIXED_REVISION_ID,
2102 MAX_BILL_LEVEL,
2103 PARENT_COMP_SEQ_ID,
2104 END_ITEM_ID,
2105 END_ITEM_ORG_ID,
2106 STRUCTURE_TYPE_ID,
2107 IS_PREFERRED,
2108 PARENT_IMPLEMENTATION_DATE,
2109 PARENT_CHANGE_NOTICE,
2110 SOURCE_BILL_SEQUENCE_ID,
2111 COMMON_COMPONENT_SEQUENCE_ID,
2112 COMP_SOURCE_BILL_SEQ_ID,
2113 COMP_EFFECTIVITY_CONTROL)
2114 VALUES
2115 (
2116 top_bill_sequence_id_tbl(i) ,
2117 bill_sequence_id_tbl(i) ,
2118 common_bill_sequence_id_tbl(i) ,
2119 common_organization_id_tbl(i) ,
2120 organization_id_tbl(i) ,
2121 component_sequence_id_tbl(i) ,
2122 component_item_id_tbl(i) ,
2123 basis_type_tbl(i) ,
2124 component_quantity_tbl(i) ,
2125 plan_level_tbl(i) ,
2126 extended_quantity_tbl(i) ,
2127 sort_order_tbl(i) ,
2128 group_id_tbl(i) ,
2129 top_alternate_designator_tbl(i) ,
2130 component_yield_factor_tbl(i) ,
2131 top_item_id_tbl(i) ,
2132 component_code_tbl(i) ,
2133 include_in_cost_rollup_tbl(i) ,
2134 loop_flag_tbl(i) ,
2135 planning_factor_tbl(i) ,
2136 operation_seq_num_tbl(i) ,
2137 bom_item_type_tbl(i) ,
2138 parent_bom_item_type_tbl(i) ,
2139 primary_uom_code_tbl(i) ,
2140 primary_uom_desc_tbl(i) ,
2141 parent_item_id_tbl(i) ,
2142 alternate_bom_designator_tbl(i) ,
2143 wip_supply_type_tbl(i) ,
2144 item_num_tbl(i) ,
2145 effectivity_date_tbl(i) ,
2146 disable_date_tbl(i) ,
2147 trimmed_effectivity_date_tbl(i) ,
2148 trimmed_disable_date_tbl(i) ,
2149 trimmed_from_unit_number_tbl(i),
2150 trimmed_to_unit_number_tbl(i),
2151 from_end_item_unit_number_tbl(i) ,
2152 to_end_item_unit_number_tbl(i) ,
2153 implementation_date_tbl(i) ,
2154 optional_tbl(i) ,
2155 supply_subinventory_tbl(i) ,
2156 supply_locator_id_tbl(i) ,
2157 component_remarks_tbl(i) ,
2158 change_notice_tbl(i) ,
2159 operation_leadtime_percent_tbl(i) ,
2160 mutually_exclusive_options_tbl(i) ,
2161 check_atp_tbl(i) ,
2162 required_to_ship_tbl(i) ,
2163 required_for_revenue_tbl(i) ,
2164 include_on_ship_docs_tbl(i) ,
2165 low_quantity_tbl(i) ,
2166 high_quantity_tbl(i) ,
2167 so_basis_tbl(i) ,
2168 operation_offset_tbl(i) ,
2169 Current_revision_tbl(i) ,
2170 locator_tbl(i) ,
2171 attribute_category_tbl(i) ,
2172 attribute1_tbl(i) ,
2173 attribute2_tbl(i) ,
2174 attribute3_tbl(i) ,
2175 attribute4_tbl(i) ,
2176 attribute5_tbl(i) ,
2177 attribute6_tbl(i) ,
2178 attribute7_tbl(i) ,
2179 attribute8_tbl(i) ,
2180 attribute9_tbl(i) ,
2181 attribute10_tbl(i) ,
2182 attribute11_tbl(i) ,
2183 attribute12_tbl(i) ,
2184 attribute13_tbl(i) ,
2185 attribute14_tbl(i) ,
2186 attribute15_tbl(i) ,
2187 obj_name_tbl(i) ,
2188 pk1_value_tbl(i) ,
2189 pk2_value_tbl(i) ,
2190 pk3_value_tbl(i) ,
2191 pk4_value_tbl(i) ,
2192 pk5_value_tbl(i) ,
2193 from_end_item_rev_id_tbl(i) ,
2194 from_end_item_minor_rev_id_tbl(i) ,
2195 to_end_item_rev_id_tbl(i) ,
2196 to_end_item_minor_rev_id_tbl(i) ,
2197 new_component_code_tbl(i) ,
2198 parent_sort_order_tbl(i) ,
2199 comp_common_bill_seq_tbl(i) ,
2200 comp_bill_seq_tbl(i) ,
2201 access_flag_tbl(i) ,
2202 eng_item_flag_tbl(i) ,
2203 assembly_type_tbl(i) ,
2204 revision_label_tbl(i) ,
2205 revision_id_tbl(i) ,
2206 effectivity_control_tbl(i) ,
2207 object_rev_id_tbl(i) ,
2208 minor_rev_id_tbl(i) ,
2209 minor_rev_code_tbl(i) ,
2210 from_object_rev_id_tbl(i) ,
2211 from_minor_rev_id_tbl(i) ,
2212 to_object_rev_id_tbl(i) ,
2213 to_minor_rev_id_tbl(i) ,
2214 component_item_revision_id_tbl(i) ,
2215 component_minorrevision_id_tbl(i) ,
2216 decode(comp_bill_seq_tbl(i), null, to_date(null), bom_implementation_date_tbl(i)) ,
2217 gtin_number_tbl(i),
2218 gtin_description_tbl(i),
2219 trade_item_descriptor_tbl(i),
2220 trade_item_descriptor_desc_tbl(i),
2221 gtin_publication_status_tbl(i),
2222 top_gtin_number_tbl(i),
2223 top_gtin_description_tbl(i),
2224 top_trade_item_descriptor_tbl(i),
2225 parent_gtin_number_tbl(i),
2226 parent_gtin_description_tbl(i),
2227 parent_trade_descriptor_tbl(i),
2228 creation_date_tbl(i),
2229 created_by_tbl(i),
2230 last_update_date_tbl(i),
2231 last_updated_by_tbl(i),
2232 auto_request_material_tbl(i),
2233 rexplode_flag_tbl(i),
2234 acd_type_tbl(i),
2235 quantity_related_tbl(i),
2236 change_policy_val_tbl(i),
2237 explode_option_tbl(i),
2238 comp_fixed_rev_high_date_tbl(i),
2239 comp_fixed_revision_id_tbl(i),
2240 max_level,
2241 parent_comp_sequence_id_tbl(i),
2242 l_end_item_id,
2243 l_end_item_org_id,
2244 decode(comp_bill_seq_tbl(i), null, null, structure_type_id_tbl(i)),
2245 decode(comp_bill_seq_tbl(i), null, null, is_preferred_tbl(i)),
2246 parent_impl_date_tbl(i),
2247 parent_change_notice_tbl(i),
2248 source_bill_sequence_id_tbl(i) ,
2249 common_component_seq_id_tbl(i),
2250 comp_source_bill_seq_tbl(i),
2251 comp_effectivity_control_tbl(i));
2252
2253 --EXIT WHEN top_bill_sequence_id_tbl.COUNT < l_batch_size;
2254 END LOOP;
2255
2256 CLOSE exploder;
2257 /* Update the quantity of children for every parent, total quantity for every parent */
2258
2259 --
2260 -- IF total rows fetched is 0, THEN break the loop here since nothing
2261 -- more to explode
2262 --
2263 IF total_rows = 0 THEN
2264 -- Do not break the loop. We might find some dirty nodes somewhere deep in the hierarchy
2265 --exit;
2266 null;
2267 END IF;
2268
2269 END LOOP; -- while level
2270
2271 --Dbms_Output.put_line('g_parent_sort_order_tbl.COUNT : '||g_parent_sort_order_tbl.COUNT);
2272
2273 FORALL i IN 1..g_parent_sort_order_tbl.COUNT
2274 UPDATE BOM_EXPLOSIONS_ALL
2275 SET quantity_of_children = g_quantity_of_children_tbl(i),
2276 total_qty_at_next_level = g_total_qty_at_next_level_tbl(i)
2277 WHERE group_id = grp_id
2278 AND sort_order = g_parent_sort_order_tbl(i);
2279
2280 UPDATE BOM_EXPLOSIONS_ALL bet SET (bet.primary_uom_code, bet.eng_item_flag, bet.primary_unit_of_measure) =
2281 (SELECT msi.primary_uom_code
2282 , msi.eng_item_flag
2283 , mum.unit_of_measure
2284 FROM mtl_system_items_b msi
2285 , mtl_units_of_measure_tl mum
2286 WHERE msi.inventory_item_id = bet.component_item_id
2287 AND msi.organization_id = bet.common_organization_id
2288 AND msi.primary_uom_code = mum.uom_code
2289 AND mum.language = userenv('LANG'))
2290 WHERE bet.group_id = grp_id AND bet.obj_name IS NULL AND bet.primary_uom_code IS NULL;
2291
2292 /*
2293 ** Once the explosion is done, apply the exclusion rules
2294 ** The reason exclusion is applied after the tree is built, is so that the dataset
2295 ** can be fetched without or without exclusion. This will prevent exploding the tree
2296 ** for applying exclusions
2297 */
2298 Apply_Exclusion_rules(p_group_id => grp_id);
2299
2300 IF max_level_exceeded THEN
2301
2302 error_code := 9998;
2303 Fnd_Message.Set_Name('BOM', 'BOM_LEVELS_EXCEEDED');
2304
2305 FOR l_bill_rec in l_TopBill_csr
2306 LOOP
2307 Fnd_Message.Set_Token('ENTITY', l_bill_rec.concatenated_segments);
2308 Fnd_Message.Set_Token('ENTITY1', l_bill_rec.concatenated_segments);
2309 Fnd_Message.Set_Token('ENTITY2', l_bill_rec.alternate_bom_designator);
2310 END LOOP;
2311
2312 err_msg := Fnd_Message.Get_Encoded;
2313 ELSE
2314 error_code := 0;
2315 err_msg := null;
2316
2317 END IF;
2318
2319 EXCEPTION WHEN OTHERS THEN
2320 error_code := SQLCODE;
2321 Fnd_Msg_Pub.Build_Exc_Msg(
2322 p_pkg_name => 'BOM_EXPLODER_PUB',
2323 p_procedure_name => 'BOM_EXPLODER',
2324 p_error_text => SQLERRM);
2325 err_msg := Fnd_Message.Get_Encoded;
2326 Raise exploder_error;
2327 --ROLLBACK;
2328
2329 END bom_exploder;
2330
2331
2332 procedure exploders(
2333 verify_flag IN NUMBER DEFAULT 0,
2334 online_flag IN NUMBER DEFAULT 0,
2335 org_id IN NUMBER,
2336 order_by IN NUMBER DEFAULT 1,
2337 grp_id IN NUMBER,
2338 session_id IN NUMBER DEFAULT 0,
2339 l_levels_to_explode IN NUMBER DEFAULT 1,
2340 bom_or_eng IN NUMBER DEFAULT 1,
2341 impl_flag IN NUMBER DEFAULT 1,
2342 plan_factor_flag IN NUMBER DEFAULT 2,
2343 l_explode_option IN NUMBER DEFAULT 2,
2344 module IN NUMBER DEFAULT 2,
2345 cst_type_id IN NUMBER DEFAULT 0,
2346 std_comp_flag IN NUMBER DEFAULT 0,
2347 unit_number IN VARCHAR2 DEFAULT '',
2348 rev_date IN DATE DEFAULT sysdate,
2349 object_rev_id IN NUMBER,
2350 minor_rev_id IN NUMBER,
2351 show_rev IN NUMBER DEFAULT 1,
2352 material_ctrl IN NUMBER DEFAULT 2,
2353 lead_time IN NUMBER DEFAULT 2,
2354 object_name IN VARCHAR2 DEFAULT NULL,
2355 pk_value1 IN VARCHAR2 DEFAULT NULL,
2356 pk_value2 IN VARCHAR2 DEFAULT NULL,
2357 pk_value3 IN VARCHAR2 DEFAULT NULL,
2358 pk_value4 IN VARCHAR2 DEFAULT NULL,
2359 pk_value5 IN VARCHAR2 DEFAULT NULL,
2360 end_item_id IN NUMBER DEFAULT NULL,
2361 end_item_org_id IN NUMBER DEFAULT NULL,
2362 end_item_rev_id IN NUMBER DEFAULT NULL,
2363 end_item_minor_rev_id IN NUMBER DEFAULT NULL,
2364 end_item_minor_rev_code IN VARCHAR2 DEFAULT NULL,
2365 filter_pbom IN VARCHAR2 DEFAULT NULL,
2366 top_bill_sequence IN NUMBER,
2367 max_level in NUMBER,
2368 err_msg IN OUT NOCOPY VARCHAR2,
2369 error_code IN OUT NOCOPY NUMBER) AS
2370
2371 --max_level NUMBER;
2372 levels_to_explode NUMBER;
2373 explode_option NUMBER;
2374 cost_org_id NUMBER;
2375 max_levels NUMBER;
2376 incl_oc_flag NUMBER;
2377 counter NUMBER;
2378 l_std_comp_flag NUMBER;
2379 l_error_code NUMBER;
2380 l_err_msg VARCHAR2(2000);
2381 loop_detected EXCEPTION;
2382
2383 BEGIN
2384
2385 levels_to_explode := l_levels_to_explode;
2386 explode_option := l_explode_option;
2387
2388 /*
2389 ** fetch the max permissible levels for explosion
2390 ** doing a max since IF no row exist to prevent no_Data_found exception
2391 ** FROM being raised
2392 */
2393
2394 /*SELECT max(MAXIMUM_BOM_LEVEL)
2395 INTO max_level
2396 FROM BOM_PARAMETERS
2397 WHERE (org_id = -1
2398 or
2399 (org_id <> -1 AND ORGANIZATION_ID = org_id)
2400 );
2401
2402 -- maximum level must be at most 60 (plan level 0..59)
2403
2404 IF nvl(max_level, 60) > 60 THEN
2405 max_level := 60;
2406 END IF;*/
2407 --commented as now, max_level is passed as a parameter.
2408
2409 /*
2410 ** IF levels to explode > max levels or < 0, set it to max_levels
2411 */
2412
2413 IF (levels_to_explode < 0) OR (levels_to_explode > max_level) THEN
2414 levels_to_explode := max_level;
2415 END IF;
2416
2417 /*
2418 ** IF levels_to_explode > 1, THEN explode_option = CURRENT is the
2419 ** only valid option
2420 ** 05/20/93 removed this condition to make it generic. Also the verify
2421 ** needs current+future indented explosion.
2422
2423 IF levels_to_explode > 1 THEN
2424 explode_option := 2;
2425 END IF;
2426 */
2427
2428 IF (module = 1 or module = 2) THEN /* cst or bom explosion */
2429 l_std_comp_flag := 2; /* ALL */
2430 ELSE
2431 l_std_comp_flag := std_comp_flag;
2432 END IF;
2433
2434 IF (module = 1) THEN /* CST */
2435 incl_oc_flag := 2;
2436 ELSE
2437 incl_oc_flag := 1;
2438 END IF;
2439
2440 bom_exploder(
2441 verify_flag => verify_flag,
2442 online_flag => online_flag,
2443 org_id => org_id,
2444 order_by => order_by,
2445 grp_id => grp_id,
2446 levels_to_explode => levels_to_explode,
2447 bom_or_eng => bom_or_eng,
2448 impl_flag => impl_flag,
2449 std_comp_flag => l_std_comp_flag,
2450 plan_factor_flag => plan_factor_flag,
2451 explode_option => explode_option,
2452 incl_oc_flag => incl_oc_flag,
2453 unit_number => unit_number,
2454 max_level => max_level,
2455 rev_date => rev_date,
2456 object_rev_id => object_rev_id,
2457 minor_rev_id => minor_rev_id,
2458 show_rev => show_rev,
2459 material_ctrl => material_ctrl,
2460 lead_time => lead_time,
2461 object_name => object_name,
2462 pk_value1 => pk_value1,
2463 pk_value2 => pk_value2,
2464 pk_value3 => pk_value3,
2465 pk_value4 => pk_value4,
2466 pk_value5 => pk_value5,
2467 end_item_id => end_item_id,
2468 end_item_org_id => end_item_org_id,
2469 end_item_rev_id => end_item_rev_id,
2470 end_item_minor_rev_id => end_item_minor_rev_id,
2471 end_item_minor_rev_code => end_item_minor_rev_code,
2472 filter_pbom => filter_pbom,
2473 top_bill_sequence => top_bill_sequence,
2474 err_msg => l_err_msg,
2475 error_code => l_error_code
2476 );
2477
2478 error_code := l_error_code;
2479 err_msg := l_err_msg;
2480
2481 /* insert the attachments for the current explosion
2482 Attachments are now inserted to improve performance of the view when querying the explosion results
2483 Only the pk1 and status id, etc is inserted into the explosion. Rest of the user displayed columns
2484 are still left in the view.
2485 The following columns are resused for ATTACHMENT node
2486 pk1_value = ATTACHED_DOCUMENT_ID
2487 LINE_ID = DOCUMENT_ID
2488 */
2489
2490 --dbms_output.put_line('Inserting Attachments . . .');
2491 --Insert_Attachments(p_group_id => grp_id);
2492
2493
2494 EXCEPTION WHEN OTHERS THEN
2495 error_code := l_error_code;
2496 err_msg := l_err_msg;
2497 Raise exploder_error;
2498 END exploders;
2499
2500 PROCEDURE loopstr2msg(
2501 grp_id IN NUMBER,
2502 verify_msg IN OUT NOCOPY VARCHAR2
2503 ) IS
2504 top_alt VARCHAR2(10);
2505 org_id NUMBER;
2506 cur_msgstr VARCHAR2(240);
2507 cur_item_id NUMBER;
2508 cur_substr VARCHAR2(16);
2509 position NUMBER;
2510 tmp_msg VARCHAR2(2000);
2511 err_msg VARCHAR2(80);
2512
2513 CURSOR get_loop_rows(c_group_id NUMBER) IS
2514 SELECT
2515 COMPONENT_CODE,
2516 LOOP_FLAG,
2517 PLAN_LEVEL
2518 FROM BOM_EXPLOSIONS_ALL
2519 WHERE GROUP_ID = c_group_id
2520 AND LOOP_FLAG = 1;
2521 BEGIN
2522
2523 SELECT NVL( TOP_ALTERNATE_DESIGNATOR, 'none' ), ORGANIZATION_ID
2524 INTO top_alt, org_id
2525 FROM BOM_EXPLOSIONS_ALL
2526 WHERE GROUP_ID = grp_id
2527 AND ROWNUM = 1
2528 AND PLAN_LEVEL = 0;
2529
2530 FOR loop_rec IN get_loop_rows( grp_id ) LOOP
2531
2532 tmp_msg := '';
2533 FOR i IN 0..loop_rec.plan_level LOOP
2534 position := (i * 16) + 1;
2535 cur_substr := SUBSTR( loop_rec.component_code, position, 16 );
2536 cur_item_id := TO_NUMBER( cur_substr );
2537
2538 SELECT
2539 substrb(MIF.ITEM_NUMBER || ' ' || BBM.ALTERNATE_BOM_DESIGNATOR,1,16)
2540 INTO cur_msgstr
2541 FROM MTL_ITEM_FLEXFIELDS MIF, BOM_BILL_OF_MATERIALS BBM
2542 WHERE MIF.ORGANIZATION_ID = BBM.ORGANIZATION_ID
2543 AND MIF.ITEM_ID = BBM.ASSEMBLY_ITEM_ID
2544 AND BBM.ASSEMBLY_ITEM_ID = cur_item_id
2545 AND BBM.ORGANIZATION_ID = org_id
2546 AND (
2547 ((top_alt = 'none') AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL)
2548 OR
2549 ((top_alt <> 'none')
2550 AND (
2551 ( EXISTS ( SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM1
2552 WHERE BBM1.ORGANIZATION_ID = org_id
2553 AND BBM1.ASSEMBLY_ITEM_ID = cur_item_id
2554 AND BBM1.ALTERNATE_BOM_DESIGNATOR = top_alt)
2555 AND BBM.ALTERNATE_BOM_DESIGNATOR = top_alt
2556 )
2557 OR
2558 ( NOT EXISTS (SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM2
2559 WHERE BBM2.ORGANIZATION_ID = org_id
2560 AND BBM2.ASSEMBLY_ITEM_ID = cur_item_id
2561 AND BBM2.ALTERNATE_BOM_DESIGNATOR = top_alt)
2562 AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL
2563 )
2564 )
2565 )
2566 );
2567
2568 IF i = 0 THEN
2569 tmp_msg := cur_msgstr;
2570 ELSE
2571 tmp_msg := tmp_msg || ' -> ' || cur_msgstr;
2572 END IF;
2573
2574 END LOOP; /* loop through component_code */
2575 verify_msg := tmp_msg;
2576
2577
2578 END LOOP; /* for loop_rec cursor loop */
2579
2580
2581 EXCEPTION
2582 when others THEN
2583 err_msg := substrb(SQLERRM, 1, 70);
2584
2585 END loopstr2msg;
2586
2587 procedure exploder_userexit_pvt (
2588 verify_flag IN NUMBER DEFAULT 0,
2589 org_id IN NUMBER,
2590 order_by IN NUMBER DEFAULT 1,
2591 grp_id IN OUT NOCOPY NUMBER,
2592 session_id IN NUMBER DEFAULT 0,
2593 levels_to_explode IN NUMBER DEFAULT 60,
2594 bom_or_eng IN NUMBER DEFAULT 2,
2595 impl_flag IN NUMBER DEFAULT 2,
2596 plan_factor_flag IN NUMBER DEFAULT 2,
2597 explode_option IN NUMBER DEFAULT 3,
2598 module IN NUMBER DEFAULT 2,
2599 cst_type_id IN NUMBER DEFAULT 0,
2600 std_comp_flag IN NUMBER DEFAULT 0,
2601 expl_qty IN NUMBER DEFAULT 1,
2602 unit_number IN VARCHAR2 DEFAULT NULL,
2603 alt_desg IN VARCHAR2 DEFAULT '',
2604 comp_code IN VARCHAR2 DEFAULT '',
2605 rev_date IN DATE DEFAULT sysdate,
2606 minor_rev_id IN NUMBER DEFAULT NULL,
2607 material_ctrl IN NUMBER DEFAULT 2,
2608 lead_time IN NUMBER DEFAULT 2,
2609 object_name IN VARCHAR2 DEFAULT NULL,
2610 pk_value1 IN VARCHAR2,
2611 pk_value2 IN VARCHAR2 DEFAULT NULL,
2612 pk_value3 IN VARCHAR2 DEFAULT NULL,
2613 pk_value4 IN VARCHAR2 DEFAULT NULL,
2614 pk_value5 IN VARCHAR2 DEFAULT NULL,
2615 end_item_id IN NUMBER DEFAULT NULL,
2616 end_item_revision_id IN NUMBER DEFAULT NULL,
2617 end_item_minor_revision_id IN NUMBER DEFAULT NULL,
2618 err_msg IN OUT NOCOPY VARCHAR2,
2619 error_code IN OUT NOCOPY NUMBER,
2620 end_item_strc_revision_id IN NUMBER DEFAULT NULL,
2621 show_rev IN NUMBER DEFAULT 1,
2622 structure_rev_id IN NUMBER DEFAULT NULL,
2623 structure_type_id IN NUMBER DEFAULT NULL,
2624 filter_pbom IN VARCHAR2 DEFAULT NULL,
2625 p_autonomous_transaction IN NUMBER
2626 ) AS
2627
2628 item_id NUMBER := pk_value1;
2629 l_rev_date DATE := rev_date;
2630
2631 cbsi NUMBER;
2632 out_code NUMBER;
2633 cost_org_id NUMBER;
2634 stmt_num NUMBER := 1;
2635 out_message VARCHAR2(240);
2636 parameter_error EXCEPTION;
2637 inv_uom_conv_exe EXCEPTION;
2638 X_SortWidth number; -- Maximum of 9999999 components per level
2639 cnt NUMBER :=0;
2640
2641 is_cost_organization VARCHAR2(1);
2642 t_conversion_rate NUMBER;
2643 t_master_org_id NUMBER;
2644 t_child_uom varchar(3);
2645 t_comp_qty NUMBER;
2646 t_comp_extd_qty NUMBER;
2647 t_master_uom varchar(3);
2648 t_item_cost NUMBER;
2649
2650 -- Added the check for obj_name since cost calculations are applicable only
2651 -- for inventory item components
2652
2653 -- Also we need to check Item BOM commoning a CAD BOM. In that case also,
2654 -- the following cost calculations are not required
2655 Cursor cur(p_group_id IN NUMBER) is
2656 Select BET.organization_id curOI,
2657 BET.bill_sequence_id curBSI,
2658 BET.component_sequence_id curCSI,
2659 /*if the top item is not the assigned to both orgs, this can lead to problems bug: 5522821*/
2660 Decode(BET.component_item_id, BET.top_item_id,BOM.assembly_item_id,BET.component_item_id) curCII,
2661 BET.common_bill_sequence_id curCBSI,
2662 BET.group_id curGI,
2663 BET.primary_uom_code curPUC,
2664 BET.primary_unit_of_measure curPUM
2665 FROM BOM_EXPLOSIONS_ALL BET, bom_structures_b BOM
2666 WHERE BET.group_id = p_group_id AND BET.obj_name IS NULL AND
2667 BET.bill_sequence_id <> BET.common_bill_sequence_id AND
2668 BET.source_bill_sequence_id = BOM.BILL_SEQUENCE_ID AND
2669 BET.plan_level <> 0;
2670 --AND BOM.OBJ_NAME IS NULL;
2671
2672 cursor conv (t_master_uom varchar2,
2673 t_child_uom varchar2,
2674 t_inv_id number,
2675 t_master_org_id number) is
2676 SELECT conversion_rate
2677 FROM mtl_uom_conversions_view
2678 WHERE primary_uom_code = t_master_uom and
2679 uom_code = t_child_uom and
2680 inventory_item_id = t_inv_id and
2681 organization_id = t_master_org_id;
2682
2683 -- Cannot use mtl_item_rev_highdate_v because of the way it presents the high_date for the last rev
2684 -- The high_date of last_rev for an item should always be higher for example like 31-DEC-9999 no matter
2685 -- the last rev is the current rev or future rev. Otherwise, we can't resolve the revisions as of future date
2686 -- Replaced with the following cursor
2687
2688 /*
2689 CURSOR getItemRev (p_inventory_item_id IN NUMBER,
2690 p_organization_id IN NUMBER,
2691 p_effective_date IN DATE) IS
2692 SELECT revision revision, revision_id revision_id FROM mtl_item_rev_highdate_v
2693 WHERE inventory_item_id = p_inventory_item_id AND
2694 organization_id = p_organization_id AND
2695 p_effective_date BETWEEN effectivity_date AND decode( sign(high_date-effectivity_date), 1 , high_date, p_effective_date) ;
2696 */
2697
2698 CURSOR getItemRev (p_inventory_item_id IN NUMBER,
2699 p_organization_id IN NUMBER,
2700 p_effective_date IN DATE) IS
2701 SELECT revision revision, revision_id revision_id FROM (
2702 SELECT rev1.organization_id , rev1.inventory_item_id , rev1.revision_id , rev1.revision ,
2703 rev1.effectivity_date , nvl(min(rev2.effectivity_date - 1/(60*60*24)),
2704 greatest(to_date('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.effectivity_date)) high_date,
2705 rev1.implementation_date, rev1.change_notice FROM mtl_item_revisions_b rev2 , mtl_item_revisions_b rev1
2706 WHERE rev1.inventory_item_id = p_inventory_item_id AND rev1.organization_id = p_organization_id AND
2707 rev1.organization_id = rev2.organization_id(+) AND
2708 rev1.inventory_item_id = rev2.inventory_item_id(+) AND
2709 rev2.effectivity_date(+) > rev1.effectivity_date
2710 GROUP BY rev1.organization_id , rev1.inventory_item_id , rev1.revision_id ,
2711 rev1.revision , rev1.effectivity_date , rev1.implementation_date , rev1.change_notice)
2712 WHERE p_effective_date BETWEEN effectivity_date AND high_date;
2713
2714 CURSOR getEndItemRev (p_item_revision_id IN NUMBER) IS
2715 SELECT inventory_item_id, organization_id, revision,effectivity_date FROM mtl_item_revisions_b
2716 WHERE revision_id = p_item_revision_id;
2717
2718 CURSOR getCurrentMinorRev (p_obj_name IN VARCHAR2,
2719 p_pk1_value IN VARCHAR2,
2720 p_pk2_value IN VARCHAR2,
2721 p_pk3_value IN VARCHAR2) IS
2722 SELECT nvl(max(minor_revision_id),0) minor_revision_id FROM ego_minor_revisions
2723 WHERE obj_name = p_obj_name AND
2724 pk1_value = p_pk1_value AND
2725 nvl(pk2_value,'-1') = nvl(p_pk2_value,'-1') AND
2726 nvl(pk3_value,'-1') = nvl(p_pk3_value,'-1');
2727
2728 CURSOR getCurrentMinorRevForItemRev (p_item_rev_id IN NUMBER) IS
2729 SELECT nvl(max(minor_revision_id),0) minor_revision_id FROM ego_minor_revisions
2730 WHERE obj_name = 'EGO_ITEM_REVISION'
2731 AND pk3_value = p_item_rev_id;
2732
2733 CURSOR getEndItemMinorRevCode (p_revision_id IN NUMBER,
2734 p_minor_rev_id IN NUMBER) IS
2735 SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(p_minor_rev_id)) mrev_code FROM mtl_item_revisions_b
2736 WHERE revision_id = p_revision_id;
2737
2738 CURSOR getPreferredStructure ( p_obj_name IN VARCHAR2,
2739 p_pk1_value IN VARCHAR2,
2740 p_pk2_value IN VARCHAR2,
2741 p_structure_type_id IN NUMBER) IS
2742 SELECT alternate_bom_designator FROM bom_structures_b WHERE ( (p_obj_name IS NULL AND obj_name IS NULL)
2743 OR (p_obj_name IS NOT NULL AND obj_name = p_obj_name)) AND pk1_value = p_pk1_value AND
2744 pk2_value = p_pk2_value AND structure_type_id = p_structure_type_id;
2745
2746
2747 CURSOR c_dirty_nodes (p_group_id IN NUMBER ) IS
2748 SELECT sort_order, comp_common_bill_seq_id, comp_bill_seq_id FROM BOM_EXPLOSIONS_ALL WHERE
2749 group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 1
2750 ORDER BY sort_order;
2751
2752 CURSOR c_get_first_revision (p_inventory_item_id IN NUMBER,
2753 p_organization_id IN NUMBER) IS
2754 SELECT effectivity_date, revision_id, revision FROM mtl_item_revisions_b WHERE
2755 inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
2756 effectivity_date = ( SELECT min(effectivity_date) FROM mtl_item_revisions_b
2757 WHERE inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id );
2758
2759 CURSOR c_Pkg_Structure_Type IS
2760 SELECT structure_type_id
2761 FROM bom_structure_types_b
2762 WHERE structure_type_name = 'Packaging Hierarchy';
2763
2764 CURSOR getComponentFixedRevisions (p_group_id IN NUMBER) IS
2765 SELECT bet.component_sequence_id, bet.component_item_revision_id revision_id,
2766 mir.revision revision FROM bom_explosions_all bet, mtl_item_revisions_b mir
2767 WHERE bet.group_id = p_group_id AND bet.plan_level <> 0 AND nvl(bet.component_item_revision_id,0) <> 0
2768 AND bet.component_item_revision_id = mir.revision_id;
2769
2770 CURSOR getFixedRevDetails (p_group_id IN NUMBER) IS
2771 SELECT
2772 DECODE( SIGN(high_date-SYSDATE),
2773 -1 ,
2774 high_date,
2775 1 ,
2776 DECODE( SIGN(effectivity_date-SYSDATE),
2777 1 ,
2778 effectivity_date,
2779 SYSDATE
2780 ) ,
2781 0 ,
2782 SYSDATE
2783 ) Revision_high_date,
2784 Revision_id
2785 FROM
2786 ( SELECT
2787 rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
2788 NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
2789 GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
2790 ) High_Date,
2791 rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
2792 FROM bom_explosions_all bet, Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
2793 WHERE bet.group_id = p_group_id AND bet.component_item_revision_id IS NOT NULL AND
2794 rev1.revision_id = bet.component_item_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
2795 AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
2796 AND rev2.implementation_date (+) IS NOT NULL
2797 GROUP BY rev1.Organization_Id, rev1.Inventory_Item_Id, rev1.Revision_Id, rev1.Revision, rev1.Effectivity_Date,
2798 rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label);
2799
2800 CURSOR revTable (p_group_id IN NUMBER) IS
2801 SELECT nvl(BE.component_sequence_id,0) component_sequence_id, --nvl(BE.current_revision,
2802 --always call Get_Current_RevisionDetails, we want to pick the rev label from items
2803 -- even for fixed rev comps.
2804 BOM_EXPLODER_PUB.Get_Current_RevisionDetails(BE.component_item_id,
2805 BE.organization_id,
2806 decode(nvl(BE.component_item_revision_id, BE.comp_fixed_revision_id),
2807 null,
2808 BOM_EXPLODER_PUB.get_explosion_date,
2809 BOM_EXPLODER_PUB.Get_Revision_HighDate(nvl(BE.component_item_revision_id, BE.comp_fixed_revision_id)))) current_revision,
2810 nvl(BE.component_item_revision_id,BOM_EXPLODER_PUB.Get_Current_Revision_Id) REVISION_ID,
2811 --nvl(BE.revision_label,
2812 BOM_EXPLODER_PUB.Get_Current_Revision_Label revision_label,
2813 BE.new_component_code component_code
2814 FROM bom_explosions_all BE WHERE BE.group_id = p_group_id;
2815
2816 CURSOR revTableWithAccessFlag (p_group_id IN NUMBER) IS
2817 SELECT nvl(BE.component_sequence_id,0) component_sequence_id, nvl(BE.current_revision,
2818 BOM_EXPLODER_PUB.Get_Current_RevisionDetails(BE.component_item_id,
2819 BE.organization_id,
2820 decode(BE.comp_fixed_revision_id,
2821 null,
2822 BOM_EXPLODER_PUB.get_explosion_date,
2823 BOM_EXPLODER_PUB.Get_Revision_HighDate(BE.comp_fixed_revision_id)))) current_revision,
2824 nvl(BE.component_item_revision_id,BOM_EXPLODER_PUB.Get_Current_Revision_Id) REVISION_ID,
2825 nvl(BE.revision_label, BOM_EXPLODER_PUB.Get_Current_Revision_Label) revision_label,
2826 BOM_SECURITY_PUB.CHECK_USER_PRIVILEGE(
2827 1,
2828 BOM_SECURITY_PUB.GET_FUNCTION_NAME_TO_CHECK,
2829 'EGO_ITEM',
2830 BE.PK1_VALUE,
2831 BE.ORGANIZATION_ID,
2832 NULL,
2833 NULL,
2834 NULL,
2835 BOM_EXPLODER_PUB.Get_EGO_User) ACCESS_FLAG
2836 FROM bom_explosions_all BE WHERE BE.group_id = p_group_id;
2837
2838
2839 CURSOR changePolicy (p_group_id IN NUMBER) IS
2840 SELECT
2841 nvl(item_dtls.component_sequence_id,0) AS component_sequence_id, ecp.policy_char_value
2842 FROM
2843 (SELECT NVL(mirb.lifecycle_id, msi.lifecycle_id) AS lifecycle_id,
2844 NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
2845 msi.item_catalog_group_id item_catalog_group_id,
2846 msi.inventory_item_id, msi.organization_id , mirb.revision_id,
2847 bet.component_sequence_id, bet.structure_type_id
2848 FROM bom_explosions_all bet, mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
2849 WHERE bet.group_id = p_group_id AND bet.comp_bill_seq_id IS NOT NULL
2850 AND bet.component_item_id = msi.INVENTORY_ITEM_ID AND
2851 bet.organization_id = msi.ORGANIZATION_ID AND
2852 mirb.revision_id = BOM_EXPLODER_PUB.Get_Component_Revision_Id(NVL(BET.component_sequence_id,0))
2853 AND (mirb.current_phase_id IS NOT NULL OR msi.current_phase_id IS NOT NULL)) ITEM_DTLS,
2854 ENG_CHANGE_POLICIES_V ECP
2855 WHERE --ecp.policy_object_pk1_value = item_dtls.item_catalog_group_id
2856 ecp.policy_object_pk1_value =
2857 (SELECT TO_CHAR(ic.item_catalog_group_id)
2858 FROM mtl_item_catalog_groups_b ic
2859 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
2860 FROM EGO_OBJ_TYPE_LIFECYCLES olc
2861 WHERE olc.object_id = (SELECT OBJECT_ID
2862 FROM fnd_objects
2863 WHERE obj_name = 'EGO_ITEM')
2864 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
2865 AND olc.object_classification_code = ic.item_catalog_group_id
2866 )
2867 AND ROWNUM = 1
2868 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2869 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
2870 AND ecp.policy_object_pk2_value = item_dtls.lifecycle_id
2871 AND ecp.policy_object_pk3_value = item_dtls.phase_id
2872 AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
2873 AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
2874 AND ecp.attribute_code = 'STRUCTURE_TYPE'
2875 AND ecp.attribute_number_value = item_dtls.structure_type_id;
2876
2877 l_bill_sequence_id NUMBER;
2878 l_common_bill_sequence_id NUMBER;
2879 l_top_bill_sequence_id NUMBER;
2880 l_effectivity_control NUMBER;
2881 l_structure_type_id NUMBER;
2882 l_Pkg_Structure_Type_Id NUMBER;
2883
2884 l_item_rev VARCHAR2(9);
2885 l_item_rev_id NUMBER;
2886 l_minor_rev_id NUMBER;
2887
2888 l_pk_value1 VARCHAR2(240);
2889 l_pk_value2 VARCHAR2(240);
2890 l_obj_name VARCHAR2(30);
2891
2892 l_end_item_id NUMBER;
2893 l_end_item_org_id NUMBER;
2894 l_end_item_revision_id NUMBER;
2895 l_end_item_revision_code VARCHAR2(10);
2896 l_end_item_minor_revision_id NUMBER;
2897 l_end_item_minor_revision_code VARCHAR2(30);
2898
2899 l_access_flag VARCHAR2(1);
2900 l_rexplode_flag VARCHAR2(1);
2901
2902 l_alt_desg VARCHAR2(10);
2903
2904 l_exploded_date DATE;
2905 l_exploded_unit_number VARCHAR2(30);
2906 l_exploded_end_item_rev NUMBER;
2907 l_exploded_end_item_id NUMBER;
2908 l_exploded_end_item_org_id NUMBER;
2909 l_group_id NUMBER;
2910 l_exploded_option NUMBER;
2911
2912 l_explode_option NUMBER;
2913
2914 l_explosion_group_id NUMBER;
2915
2916 l_bill_expl_exists VARCHAR2(1) := 'N';
2917 l_bill_criteria_exists VARCHAR2(1) := 'N';
2918 l_dirty_node_exists VARCHAR2(1) := 'Y';
2919 l_reapply_exclusions VARCHAR2(1) := 'N';
2920
2921 l_start_rev_date DATE;
2922
2923 l_unit_number NUMBER;
2924
2925 l_show_rev NUMBER := 1;
2926 l_max_bill_level NUMBER;
2927 max_level NUMBER;
2928 l_number NUMBER;
2929
2930 l_bom_or_eng NUMBER;
2931
2932 l_internal_user VARCHAR2(1) := 'N';
2933 l_person VARCHAR2(30);
2934 l_predicate VARCHAR2(32767);
2935 l_predicate_api_status VARCHAR2(1);
2936
2937 --pragma AUTONOMOUS_TRANSACTION; /* This is now controlled by the caller */
2938
2939 BEGIN
2940
2941 --DBMS_PROFILER.START_PROFILER(to_char(session_id)||pk_value1||' : '||pk_value2);
2942 SELECT max(MAXIMUM_BOM_LEVEL)
2943 INTO max_level
2944 FROM BOM_PARAMETERS
2945 WHERE (org_id = -1
2946 or
2947 (org_id <> -1 AND ORGANIZATION_ID = org_id)
2948 );
2949 --max level cannot be greater than 60
2950 IF nvl(max_level, 60) > 60 THEN
2951 max_level := 60;
2952 END IF;
2953
2954 X_SortWidth := BOMPBXIN.G_SortWidth;
2955
2956 IF (verify_flag = 1) AND (module <> 2) THEN
2957 raise parameter_error;
2958 END IF;
2959
2960 /* Grp id is not a mandatory parameter anymore
2961 IF (grp_id is null or item_id is null) THEN
2962 raise parameter_error;
2963 END IF;
2964 */
2965
2966 IF (item_id is null) THEN
2967 raise parameter_error;
2968 END IF;
2969 stmt_num := 2;
2970
2971 IF (object_name IS NULL) AND
2972 (pk_value1 IS NULL OR pk_value2 IS NULL) THEN
2973 raise parameter_error;
2974 END IF;
2975
2976 IF (pk_value1 IS NULL) THEN
2977 raise parameter_error;
2978 END IF;
2979
2980 /* Resolve the structure name (alt_desg) */
2981 /* Find out the preferred BOM when the structure type id is passed in, and alt_desg is not passed */
2982
2983 IF (structure_type_id IS NULL) THEN
2984 l_alt_desg := alt_desg;
2985 ELSE
2986 IF alt_desg IS NOT NULL THEN
2987 l_alt_desg := alt_desg;
2988 ELSE
2989 /* Find out the preferred BOM */
2990 FOR r1 IN getPreferredStructure ( p_obj_name => object_name,
2991 p_pk1_value => pk_value1,
2992 p_pk2_value => pk_value2,
2993 p_structure_type_id => structure_type_id)
2994 LOOP
2995 l_alt_desg := r1.alternate_bom_designator;
2996 END LOOP;
2997 END IF;
2998 END IF;
2999
3000 /* Reset all the globally used values */
3001
3002 Reset_Globals;
3003
3004 G_EGOUser := BOM_SECURITY_PUB.Get_EGO_User;
3005
3006 -- Get the bill sequence id and common bill sequence id
3007
3008 BEGIN
3009
3010 SELECT bill_sequence_id,common_bill_sequence_id,effectivity_control, bill_sequence_id, structure_type_id, assembly_type
3011 INTO l_bill_sequence_id, l_common_bill_sequence_id, l_effectivity_control,l_top_bill_sequence_id, l_structure_type_id, l_bom_or_eng
3012 FROM bom_structures_b bom
3013 WHERE nvl(bom.obj_name,'EGO_ITEM') = nvl(object_name,'EGO_ITEM')
3014 AND bom.pk1_value = pk_value1
3015 AND nvl(bom.pk2_value,'-1') = nvl(pk_value2,'-1')
3016 AND bom.organization_id = org_id
3017 AND nvl(bom.alternate_bom_designator, 'NONE') = nvl(l_alt_desg, 'NONE');
3018 EXCEPTION WHEN NO_DATA_FOUND
3019 THEN
3020 Null;
3021
3022 END;
3023
3024 --Dbms_Output.Put_line('Top Bill seq is : '||l_bill_sequence_id);
3025
3026 /* Resolve the xplode option */
3027
3028 IF (explode_option IN (2,3))
3029 THEN
3030 -- Do the xplosion for "Current and Future" for both "Current" and "Current and Future" options
3031 -- For rev effective BOMS, explosions are seperately maintained for "Current" and "Current and Future" options
3032 l_show_rev := 2;
3033 l_explode_option := 3;
3034 /*
3035 IF nvl(l_effectivity_control,1) <> 4
3036 THEN
3037 l_explode_option := 3;
3038 l_show_rev := 2; -- Revisions are resolved dynamically for non-rev eff BOMs.
3039 ELSE
3040 l_explode_option := explode_option;
3041 END IF;
3042 */
3043
3044 /* Also find out the effectivity date of the first rev. This will be the date
3045 on which we do the explosion*/
3046 /*
3047 IF object_name IS NULL
3048 THEN
3049 FOR r1 IN c_get_first_revision( p_inventory_item_id => pk_value1,
3050 p_organization_id => pk_value2)
3051 LOOP
3052 l_start_rev_date := r1.effectivity_date;
3053 END LOOP;
3054 END IF;
3055 */
3056 ELSE
3057 l_show_rev := 2;
3058 l_explode_option := 1;
3059 END IF;
3060
3061 -- Check if the explosion already exists for this criteria
3062
3063 --bom_or_eng => bom_or_eng, (always do for ENG)
3064 --impl_flag => impl_flag, ( always to for both impl and unimpl)
3065 --l_explode_option => explode_option, (All or Current and Future)
3066 --unit_number => unit_number,
3067 --rev_date => l_rev_date,
3068 --end_item_rev_id => l_end_item_revision_id,
3069
3070 /* Resolve all the criteria */
3071
3072 /**** Moved before the insertion of 0th row
3073 so that revision id is fetched only once and can be inserted
3074 in the 0th row
3075 *****/
3076 l_item_rev := null; -- CAD component
3077 l_item_rev_id := null; -- CAD component
3078
3079 -- Get the item rev IF the explosion is for Item BOM
3080
3081 --Dbms_Output.Put_line('obj name check');
3082
3083 IF object_name IS NULL
3084 THEN
3085
3086 l_pk_value1 := pk_value1;
3087 l_pk_value2 := pk_value2;
3088
3089 /* For end item rev effective common BOM, the end item rev should come FROM the common BOM
3090 If the user is requesting for a particular end item rev, then don't do this*/
3091
3092
3093 IF (l_effectivity_control = 4 ) AND (end_item_revision_id IS NULL) -- End item effective
3094 THEN
3095
3096 IF (l_bill_sequence_id <> l_common_bill_sequence_id)
3097 THEN
3098
3099 -- If item BOM is commoning an item BOM, THEN we get the current item rev FROM the
3100 -- common BOM to explode in the case of end irem revision effective
3101
3102 SELECT pk1_value, pk2_value,obj_name INTO l_pk_value1, l_pk_value2, l_obj_name FROM
3103 bom_structures_b WHERE bill_sequence_id = l_common_bill_sequence_id;
3104
3105 IF (l_obj_name IS NOT NULL) -- NON ITEM
3106 THEN
3107 -- If item BOM is commoning a CAD BOM, THEN we get the current item rev FROM the same
3108 -- current item BOM to explode in the case of end irem revision effective
3109 -- Reset the PK values
3110 l_pk_value1 := pk_value1;
3111 l_pk_value2 := pk_value2;
3112 END IF;
3113
3114 END IF;
3115
3116 END IF;
3117
3118 /* Align the context revision with end item revision when the end item is same as context item */
3119
3120 IF end_item_revision_id IS NOT NULL
3121 THEN
3122
3123 FOR r1 IN getEndItemRev(end_item_revision_id)
3124 LOOP
3125 IF (r1.inventory_item_id = l_pk_value1 AND r1.organization_id = l_pk_value2)
3126 THEN
3127 l_item_rev_id := end_item_revision_id;
3128 l_item_rev := r1.revision;
3129 l_rev_date := r1.effectivity_date;
3130 ELSE
3131 FOR r1 IN getItemRev(l_pk_value1, l_pk_value2, l_rev_date)
3132 LOOP
3133 l_item_rev := r1.revision;
3134 l_item_rev_id := r1.revision_id;
3135 END LOOP;
3136 END IF;
3137 END LOOP;
3138
3139 ELSE
3140
3141 FOR r1 IN getItemRev(l_pk_value1, l_pk_value2, l_rev_date)
3142 LOOP
3143 l_item_rev := r1.revision;
3144 l_item_rev_id := r1.revision_id;
3145 END LOOP;
3146 END IF;
3147
3148
3149 IF l_item_rev IS NULL
3150 THEN
3151 raise parameter_error;
3152 END IF;
3153
3154 END IF;
3155
3156 BEGIN
3157
3158 FOR pkg_type IN c_Pkg_Structure_Type
3159 LOOP
3160 l_Pkg_Structure_Type_Id := pkg_type.structure_type_id;
3161 END LOOP;
3162
3163 SELECT 'Y'
3164 , rexplode_flag
3165 , exploded_date
3166 , exploded_unit_number
3167 , exploded_end_item_rev
3168 , exploded_end_item_id
3169 , exploded_end_item_org_id
3170 , exploded_option
3171 , group_id
3172 , reapply_exclusions
3173 , max_bill_level
3174 INTO l_bill_expl_exists
3175 , l_rexplode_flag
3176 , l_exploded_date
3177 , l_exploded_unit_number
3178 , l_exploded_end_item_rev
3179 , l_exploded_end_item_id
3180 , l_exploded_end_item_org_id
3181 , l_exploded_option
3182 , l_explosion_group_id
3183 , l_reapply_exclusions
3184 , l_max_bill_level
3185 FROM BOM_EXPLOSIONS_ALL
3186 WHERE top_bill_sequence_id = l_bill_sequence_id
3187 AND exploded_option = l_explode_option
3188 AND plan_level = 0;
3189
3190 grp_id := l_explosion_group_id;
3191
3192 IF nvl(l_rexplode_flag,'0') = '1' OR nvl(max_level, 60) <> nvl(l_max_bill_level, 60) OR
3193 l_Pkg_Structure_Type_Id = l_structure_type_id
3194 THEN
3195 l_rexplode_flag := 1;
3196 --set the rexplode flag if max bill level is changed.
3197
3198 UPDATE BOM_EXPLOSIONS_ALL
3199 SET rexplode_flag = 1
3200 WHERE group_id = grp_id
3201 AND sort_order = '0000001';
3202
3203 DELETE FROM BOM_EXPLOSIONS_ALL
3204 WHERE group_id = grp_id
3205 AND sort_order <> '0000001';
3206
3207 --Always do an engg explosion for packaging hierarchies
3208 --bug:4744303
3209 IF l_Pkg_Structure_Type_Id = l_structure_type_id
3210 THEN
3211 l_bom_or_eng := 2;
3212 END IF;
3213
3214 END IF;
3215
3216 /*
3217 IF p_autonomous_transaction = 1 THEN
3218 Commit;
3219 END IF;
3220 */
3221
3222 EXCEPTION WHEN NO_DATA_FOUND
3223 THEN
3224 -- Insert for plan level 0
3225
3226 SELECT BOM_EXPLOSIONS_ALL_S.NEXTVAL INTO grp_id FROM dual;
3227
3228 insert INTO BOM_EXPLOSIONS_ALL
3229 (
3230 group_id,
3231 bill_sequence_id,
3232 common_bill_sequence_id,
3233 common_organization_id,
3234 component_sequence_id,
3235 organization_id,
3236 top_item_id,
3237 component_item_id,
3238 plan_level,
3239 extended_quantity,
3240 basis_type,
3241 component_quantity,
3242 sort_order,
3243 program_update_date,
3244 top_bill_sequence_id,
3245 component_code,
3246 loop_flag,
3247 top_alternate_designator,
3248 obj_name,
3249 pk1_value,
3250 pk2_value,
3251 pk3_value,
3252 pk4_value,
3253 pk5_value,
3254 new_component_code,
3255 parent_sort_order,
3256 comp_common_bill_seq_id,
3257 comp_source_bill_seq_id,
3258 comp_bill_seq_id,
3259 effectivity_control,
3260 access_flag,
3261 assembly_type,
3262 bom_implementation_date,
3263 creation_date,
3264 created_by,
3265 last_update_date,
3266 last_updated_by,
3267 rexplode_flag,
3268 exploded_option,
3269 structure_type_id,
3270 revision_id,
3271 implementation_date,
3272 max_bill_level,
3273 is_preferred,
3274 parent_implementation_date,
3275 hgrid_flag,
3276 source_bill_sequence_id,
3277 comp_effectivity_control
3278 )
3279 (select
3280 --explosion_group_id,
3281 grp_id,
3282 bom.bill_sequence_id,
3283 bom.common_bill_sequence_id,
3284 nvl(bom.common_organization_id,org_id),
3285 NULL,
3286 org_id,
3287 item_id,
3288 item_id,
3289 0,
3290 expl_qty,
3291 1,
3292 1,
3293 lpad('1', X_SortWidth, '0'),
3294 sysdate,
3295 bom.bill_sequence_id,
3296 nvl(comp_code, lpad(pk1_value, 16, '0')),
3297 2,
3298 l_alt_desg,
3299 obj_name,
3300 pk1_value,
3301 pk2_value,
3302 pk3_value,
3303 pk4_value,
3304 pk5_value,
3305 nvl(comp_code, lpad(decode(obj_name,'DDD_CADVIEW','C','I')||pk1_value, 20, '0')),
3306 null,
3307 bom.common_bill_sequence_id,
3308 bom.source_bill_sequence_id,
3309 bom.bill_sequence_id,
3310 bom.effectivity_control,
3311 'T',
3312 bom.assembly_type,
3313 bom.implementation_date,
3314 bom.creation_date,
3315 bom.created_by,
3316 bom.last_update_date,
3317 bom.last_updated_by,
3318 1,
3319 l_explode_option,
3320 structure_type_id,
3321 l_item_rev_id,
3322 bom.implementation_date,
3323 max_level,
3324 is_preferred,
3325 bom.implementation_date,
3326 'Y',
3327 bom.source_bill_sequence_id,
3328 bom.effectivity_control
3329 FROM bom_structures_b bom
3330 where bill_sequence_id = l_bill_sequence_id);
3331
3332 IF (SQL%NOTFOUND) THEN
3333 insert INTO BOM_EXPLOSIONS_ALL
3334 (
3335 group_id,
3336 top_item_id,
3337 component_item_id,
3338 organization_id,
3339 bill_sequence_id,
3340 top_bill_sequence_id,
3341 plan_level,
3342 sort_order,
3343 gtin_number,
3344 gtin_description,
3345 trade_item_descriptor,
3346 trade_item_descriptor_desc,
3347 obj_name,
3348 pk1_value,
3349 pk2_value,
3350 pk3_value,
3351 pk4_value,
3352 pk5_value,
3353 hgrid_flag
3354 )
3355 (select
3356 grp_id,
3357 item_id,
3358 item_id,
3359 org_id,
3360 0,
3361 0,
3362 0,
3363 lpad('1', X_SortWidth, '0'),
3364 gtin,
3365 description,
3366 trade_item_descriptor,
3367 trade_item_descriptor_desc,
3368 object_name,
3369 pk_value1,
3370 pk_value2,
3371 pk_value3,
3372 pk_value4,
3373 pk_value5,
3374 'Y'
3375 FROM ego_items_v eiv
3376 where
3377 eiv.inventory_item_id = item_id
3378 AND eiv.organization_id = org_id);
3379
3380 IF (SQL%NOTFOUND) THEN
3381 raise no_data_found;
3382 END IF;
3383
3384 /* We can't exit an autonomous transcation without completing it */
3385
3386 IF p_autonomous_transaction = 1 THEN
3387 Commit;
3388 END IF;
3389
3390 /* Before retuen set the group id context for the view */
3391
3392 BOM_EXPLODER_PUB.p_explode_option := explode_option ;
3393 BOM_EXPLODER_PUB.p_group_id := grp_id;
3394 Return;
3395
3396 END IF;
3397 END;
3398
3399 --Dbms_Output.put_line('Row count '||sql%rowcount);
3400
3401 --Dbms_Output.put_line('l_item_rev is '||l_item_rev);
3402
3403 -- Get the minor_rev_id IF it is not passed
3404
3405 IF (minor_rev_id IS NULL)
3406 THEN
3407 FOR r1 in getCurrentMinorRev (object_name, l_pk_value1, l_pk_value2,l_item_rev_id)
3408 LOOP
3409 l_minor_rev_id := r1.minor_revision_id;
3410 END LOOP;
3411 ELSE
3412 l_minor_rev_id := minor_rev_id;
3413 END IF;
3414
3415 IF l_minor_rev_id IS NULL
3416 THEN
3417 raise parameter_error;
3418 END IF;
3419
3420 IF (l_effectivity_control = 4 ) -- End Item Rev Effective
3421 THEN
3422
3423 /* If the top item itself is an end item, then the exploder doesn't
3424 require end item revision to be passed
3425 Do the end item explosion for the current item rev and the minor rev
3426 */
3427
3428 IF end_item_revision_id IS NULL
3429 THEN
3430 l_end_item_revision_id := l_item_rev_id;
3431 ELSE
3432 l_end_item_revision_id := end_item_revision_id;
3433 END IF;
3434
3435 IF l_end_item_revision_id IS NULL
3436 THEN
3437 raise parameter_error;
3438 END IF;
3439
3440 -- Get the end item information
3441 IF l_end_item_id IS NULL THEN
3442
3443 BEGIN
3444 SELECT inventory_item_id, organization_id, revision INTO l_end_item_id, l_end_item_org_id,l_end_item_revision_code FROM mtl_item_revisions_b
3445 WHERE revision_id = l_end_item_revision_id;
3446 EXCEPTION WHEN OTHERS THEN
3447 --Dbms_Output.put_line('End item revision is not valid'||sqlerrm);
3448 raise parameter_error;
3449 END;
3450
3451 END IF;
3452
3453 END IF;
3454
3455 IF (l_end_item_revision_id IS NOT NULL)
3456 THEN
3457
3458 /* If end item minor rev id is not passed, then do it for the current minor rev of that item rev */
3459
3460 IF (end_item_minor_revision_id IS NULL)
3461 THEN
3462 IF (end_item_revision_id IS NULL) -- If the top item itself is an end item
3463 THEN
3464 /* If the user did not pass the minor rev AND the top item itself is end item,
3465 THEN we aleady got the minor rev */
3466 l_end_item_minor_revision_id := l_minor_rev_id;
3467 ELSE
3468 FOR r1 IN getCurrentMinorRevForItemRev(l_end_item_revision_id)
3469 LOOP
3470 l_end_item_minor_revision_id := r1.minor_revision_id;
3471 END LOOP;
3472 END IF;
3473 ELSE
3474 l_end_item_minor_revision_id := end_item_minor_revision_id;
3475 END IF;
3476
3477 IF l_end_item_minor_revision_id IS NULL
3478 THEN
3479 raise parameter_error;
3480 END IF;
3481
3482 END IF;
3483
3484 /* Also, find out the minor rev code (used internally) */
3485
3486 IF (l_end_item_minor_revision_id IS NOT NULL)
3487 THEN
3488 FOR r1 IN getEndItemMinorRevCode(l_end_item_revision_id, l_end_item_minor_revision_id)
3489 LOOP
3490 l_end_item_minor_revision_code := r1.mrev_code;
3491 END LOOP;
3492
3493 IF l_end_item_minor_revision_code IS NULL
3494 THEN
3495 raise parameter_error;
3496 END IF;
3497
3498 END IF;
3499
3500 /* Now we got object revision id, minor rev id, end item id, end item org id, end item revision id,
3501 end item minor revision id, end item minor rev code
3502
3503 Check if we already have an explosion for this criteria
3504 */
3505
3506 --Dbms_Output.put_line('Object rev id : '||l_item_rev_id);
3507 --Dbms_Output.put_line('Minor rev id : '||l_minor_rev_id);
3508 --Dbms_Output.put_line('end_item_id : '||l_end_item_id);
3509 --Dbms_Output.put_line('end_item_org_id : '||l_end_item_org_id);
3510 --Dbms_Output.put_line('end_item_rev_id : '||l_end_item_revision_id);
3511 --Dbms_Output.put_line('end_item_minor_rev_id : '||l_end_item_minor_revision_id);
3512 --Dbms_Output.put_line('end_item_minor_rev_code : '||l_end_item_minor_revision_code);
3513
3514 --Dbms_Output.put_line('l_exploded_option '||l_exploded_option);
3515 --Dbms_Output.put_line('l_explode_option '||l_explode_option);
3516 --Dbms_Output.put_line('l_effectivity_control '||l_effectivity_control);
3517 --Dbms_Output.put_line('l_exploded_date '||to_char(l_exploded_date,'dd-mon-yy hh24:mi:ss'));
3518 --Dbms_Output.put_line('l_rev_date '||to_char(l_rev_date,'dd-mon-yy hh24:mi:ss'));
3519
3520 --Dbms_Output.put_line('Group Id '||grp_id);
3521
3522 /* Assign the context information so that the view filters the data further
3523 to make it more specific to the current session criteria */
3524
3525 --BOM_EXPLODER_PUB.p_top_bill_sequence_id := l_bill_sequence_id;
3526 BOM_EXPLODER_PUB.p_explode_option := explode_option ;
3527 BOM_EXPLODER_PUB.p_explosion_date := l_rev_date;
3528 BOM_EXPLODER_PUB.p_expl_end_item_rev := l_end_item_revision_id;
3529 BOM_EXPLODER_PUB.p_expl_end_item_rev_code := l_end_item_revision_code;
3530 BOM_EXPLODER_PUB.p_expl_end_item_id := l_end_item_id;
3531 BOM_EXPLODER_PUB.p_expl_end_item_org_id := l_end_item_org_id;
3532 BOM_EXPLODER_PUB.p_expl_unit_number := unit_number;
3533 BOM_EXPLODER_PUB.p_group_id := grp_id;
3534 BOM_EXPLODER_PUB.p_top_effectivity_control := nvl(l_effectivity_control,1);
3535
3536 IF l_bill_expl_exists = 'Y' AND nvl(l_rexplode_flag,'0') <> '1'
3537 THEN
3538
3539 /* If the explosion exists for the bill and it can satisfy the
3540 current explosion requirements, then do not reexplode */
3541
3542 IF ( l_explode_option = 1 AND l_exploded_option = l_explode_option)
3543 OR
3544 ( ((Nvl(l_effectivity_control,1) = 1 AND l_exploded_date <= l_rev_date)
3545 OR (Nvl(l_effectivity_control,1) IN (2,3) AND l_exploded_unit_number <= unit_number)
3546 OR ( Nvl(l_effectivity_control,1) = 4 AND l_exploded_end_item_id = l_end_item_id
3547 AND l_exploded_end_item_org_id = l_end_item_org_id ))
3548 AND (l_exploded_option = l_explode_option))
3549 THEN
3550
3551 l_bill_criteria_exists := 'Y';
3552 /* The explosion for this criteria already happened.
3553 But,do not return as the exploder still needs to reexplode the nodes that are dirty */
3554 ELSE
3555
3556 /* Reexplode otherwise */
3557
3558 UPDATE BOM_EXPLOSIONS_ALL
3559 SET rexplode_flag = 1
3560 WHERE group_id = grp_id
3561 AND sort_order = '0000001';
3562
3563 DELETE FROM BOM_EXPLOSIONS_ALL
3564 WHERE group_id = grp_id
3565 AND sort_order <> '0000001';
3566
3567 --Commit;
3568
3569 END IF;
3570
3571 END IF;
3572
3573 IF l_effectivity_control IN (2,3)
3574 THEN
3575 /* Get the minimum Unit/Serial number from the BOM */
3576
3577 /* Actually, just use 0 */
3578 l_unit_number := 0;
3579 END IF;
3580
3581 /* Update the top bill with the effectivity and minor rev information.
3582 Do not do this if the explosion criteria matches with the exploded one */
3583
3584 IF l_bill_criteria_exists = 'Y' AND nvl(l_rexplode_flag,'0') <> '1'
3585 THEN
3586
3587 l_dirty_node_exists := 'N';
3588
3589 /* Clean up the dirty nodes before the explosion */
3590
3591 FOR r1 IN c_dirty_nodes(grp_id)
3592 LOOP
3593
3594 l_dirty_node_exists := 'Y';
3595
3596 --Dbms_Output.put_line('Clean up the dirty nodes before the explosion : '||r1.sort_order);
3597 DELETE FROM BOM_EXPLOSIONS_ALL
3598 WHERE group_id = grp_id
3599 AND sort_order like r1.sort_order||'%' AND sort_order <> r1.sort_order;
3600
3601 --Update the change_policy value for the leaf component that has become a subassembly.
3602 UPDATE BOM_EXPLOSIONS_ALL
3603 SET CHANGE_POLICY_VALUE = Get_Change_Policy_Val(0, r1.comp_bill_seq_id)
3604 WHERE sort_order = r1.sort_order
3605 AND group_id = grp_id;
3606
3607 --Commit;
3608
3609 END LOOP;
3610
3611 /* If bill criteria exists and there are no dirty nodes found in the hierarchy, then return */
3612
3613 IF l_dirty_node_exists = 'N'
3614 THEN
3615 IF nvl(l_reapply_exclusions, 'N') = 'Y' THEN
3616 -- ReApply the exclusion rules
3617 Apply_Exclusion_Rules(grp_id,1);
3618 END IF;
3619
3620 IF p_autonomous_transaction = 1 THEN
3621 commit;
3622 END IF;
3623
3624 ELSE
3625
3626 UPDATE BOM_EXPLOSIONS_ALL
3627 SET exploded_date = l_rev_date,
3628 --exploded_unit_number = unit_number,
3629 exploded_unit_number = l_unit_number,
3630 exploded_end_item_rev = l_end_item_revision_id,
3631 exploded_end_item_id = l_end_item_id,
3632 exploded_end_item_org_id = l_end_item_org_id,
3633 object_revision_id = l_item_rev_id,
3634 minor_revision_id = l_minor_rev_id,
3635 revision_id = l_item_rev_id, --insert top item's rev id
3636 max_bill_level = nvl(max_level,60),
3637 end_item_id = l_end_item_id,
3638 end_item_org_id = l_end_item_org_id
3639 --effectivity_date = l_rev_date,
3640 --from_end_item_unit_number = unit_number
3641 WHERE group_id = grp_id
3642 AND sort_order = '0000001';
3643
3644 END IF;
3645
3646 ELSE
3647
3648 UPDATE BOM_EXPLOSIONS_ALL
3649 SET exploded_date = l_rev_date,
3650 --exploded_unit_number = unit_number,
3651 exploded_unit_number = l_unit_number,
3652 exploded_end_item_rev = l_end_item_revision_id,
3653 exploded_end_item_id = l_end_item_id,
3654 exploded_end_item_org_id = l_end_item_org_id,
3655 object_revision_id = l_item_rev_id,
3656 minor_revision_id = l_minor_rev_id,
3657 revision_id = l_item_rev_id, --insert top item's rev id
3658 max_bill_level = nvl(max_level,60),
3659 end_item_id = l_end_item_id,
3660 end_item_org_id = l_end_item_org_id
3661 --effectivity_date = l_rev_date,
3662 --from_end_item_unit_number = unit_number
3663 WHERE group_id = grp_id
3664 AND sort_order = '0000001';
3665 END IF;
3666
3667 IF l_dirty_node_exists = 'Y'
3668 THEN
3669
3670 IF (object_name IS NULL)
3671 THEN
3672
3673 /* Apply the security when the object is an inventory item */
3674
3675 l_access_flag := 'T';
3676
3677 /* Security check should be moved to the view as it is user based
3678
3679 IF (G_EGOUser is null) THEN
3680 l_access_flag := 'T';
3681 ELSE
3682 l_access_flag := BOM_SECURITY_PUB.CHECK_USER_PRIVILEGE(
3683 p_api_version => 1,
3684 p_function => BOM_SECURITY_PUB.FUNCTION_NAME_TO_CHECK,
3685 p_object_name=>'EGO_ITEM',
3686 p_instance_pk1_value=> pk_value1,
3687 p_instance_pk2_value=> pk_value2,
3688 p_user_name=> G_EGOUser);
3689 END IF;
3690 */
3691
3692 /* Get the BOM item type and Parent BOM item type for item BOM */
3693
3694 /*
3695 UPDATE BOM_EXPLOSIONS_ALL
3696 SET access_flag = l_access_flag,
3697 (bom_item_type, parent_bom_item_type, primary_uom_code, eng_item_flag, primary_unit_of_measure) = (SELECT msi.bom_item_type, msi.bom_item_type, msi.primary_uom_code,
3698 msi.eng_item_flag, (select description from mtl_units_of_measure where uom_code = msi.primary_uom_code) FROM
3699 mtl_system_items_b msi WHERE msi.inventory_item_id = item_id AND msi.organization_id = org_id)
3700 WHERE group_id = grp_id
3701 AND sort_order = '0000001';
3702 */
3703
3704 UPDATE BOM_EXPLOSIONS_ALL
3705 SET access_flag = l_access_flag,
3706 (bom_item_type, parent_bom_item_type, primary_uom_code, eng_item_flag, primary_unit_of_measure) = (SELECT msi.bom_item_type, msi.bom_item_type, msi.primary_uom_code,
3707 msi.eng_item_flag, muom.unit_of_measure FROM mtl_system_items_b msi, mtl_units_of_measure muom
3708 WHERE msi.inventory_item_id = item_id AND msi.organization_id = org_id AND muom.uom_code = msi.primary_uom_code)
3709 WHERE group_id = grp_id
3710 AND sort_order = '0000001';
3711
3712 IF (SQL%NOTFOUND) THEN
3713 raise no_data_found;
3714 END IF;
3715
3716 UPDATE BOM_EXPLOSIONS_ALL
3717 SET (gtin_number, gtin_description, trade_item_descriptor, top_gtin_number, top_gtin_description, top_trade_item_descriptor, trade_item_descriptor_desc, gtin_publication_status) =
3718 (SELECT gtin, description, trade_item_descriptor, gtin, description, trade_item_descriptor, trade_item_descriptor_desc, publication_status
3719 FROM ego_items_v egi
3720 WHERE inventory_item_id = item_id AND organization_id = org_id)
3721 WHERE group_id = grp_id
3722 AND sort_order = '0000001';
3723
3724 /* Update the change policy value for the top item */
3725 UPDATE BOM_EXPLOSIONS_ALL
3726 SET CHANGE_POLICY_VALUE = Get_Change_Policy_Val(revision_id, Comp_bill_seq_Id)
3727 WHERE group_id = grp_id
3728 AND sort_order = '0000001';
3729
3730 END IF;
3731
3732
3733 Exploders(
3734 verify_flag => verify_flag,
3735 online_flag => 1,
3736 org_id => org_id,
3737 order_by => order_by,
3738 grp_id => grp_id,
3739 session_id => session_id,
3740 l_levels_to_explode => 60, --levels_to_explode,
3741 bom_or_eng => l_bom_or_eng, --changed by arudresh to pass assy type of parent bill. bug: 4422266
3742 impl_flag => 2, --impl_flag,
3743 plan_factor_flag => plan_factor_flag,
3744 l_explode_option => l_explode_option,
3745 module => module,
3746 unit_number => l_unit_number,
3747 cst_type_id => cst_type_id,
3748 std_comp_flag => std_comp_flag,
3749 rev_date => l_rev_date,
3750 object_rev_id => l_item_rev_id,
3751 minor_rev_id => l_minor_rev_id,
3752 --show_rev => 1,
3753 show_rev => l_show_rev,
3754 material_ctrl => material_ctrl,
3755 lead_time => lead_time,
3756 object_name => object_name,
3757 pk_value1 => pk_value1,
3758 pk_value2 => pk_value2,
3759 pk_value3 => pk_value3,
3760 pk_value4 => pk_value4,
3761 pk_value5 => pk_value5,
3762 end_item_id => l_end_item_id,
3763 end_item_org_id => l_end_item_org_id,
3764 end_item_rev_id => l_end_item_revision_id,
3765 end_item_minor_rev_id => l_end_item_minor_revision_id,
3766 end_item_minor_rev_code => l_end_item_minor_revision_code,
3767 filter_pbom => filter_pbom,
3768 top_bill_sequence => l_top_bill_sequence_id,
3769 max_level => max_level,
3770 err_msg => out_message,
3771 error_code => out_code);
3772
3773 IF (verify_flag <> 1 AND (out_code = 9999 or out_code = 9998
3774 or out_code < 0)) THEN
3775 raise exploder_error;
3776 ELSIF (verify_flag = 1 AND (out_code = 9998 or out_code < 0)) THEN
3777 raise exploder_error;
3778 END IF;
3779
3780 IF (module = 1) THEN
3781 BOMPCEXP.cst_exploder(
3782 grp_id => grp_id,
3783 org_id => org_id,
3784 cst_type_id => cst_type_id,
3785 inq_flag => 1,
3786 err_msg => out_message,
3787 error_code => out_code);
3788 END IF;
3789
3790 IF (verify_flag = 1) THEN
3791 Loopstr2msg( grp_id, out_message );
3792 END IF;
3793
3794 -- If the master organization is referenced as the costing organization THEN
3795 -- is_cost_organzation flag is set to 'N' ELSE IF the child organization itself
3796 -- referenced as the costing organization THEN the is_cost_organization flag is
3797 -- set to 'Y'.
3798
3799 SELECT count(*) INTO cnt
3800 FROM mtl_parameters
3801 WHERE organization_id = cost_organization_id
3802 AND organization_id = org_id;
3803
3804 IF (cnt >0) THEN
3805 is_cost_organization := 'Y';
3806 ELSE
3807 is_cost_organization := 'N';
3808 END IF;
3809
3810 -- If the Intended Bill is referenced some other bill of different organization
3811 -- THEN the conversion rate, uom of the component in the child organization
3812 -- should be calculated.
3813
3814 IF (object_name IS NULL) THEN
3815
3816 FOR cr IN cur(grp_id) LOOP
3817
3818 SELECT msi.primary_uom_code, msi.organization_id into
3819 t_master_uom, t_master_org_id
3820 FROM mtl_system_items_b msi, bom_structures_b bbm
3821 WHERE cr.curCBSI = bbm.bill_sequence_id and
3822 bbm.organization_id = msi.organization_id and
3823 msi.inventory_item_id = cr.curCII;
3824
3825 SELECT msi.primary_uom_code INTO t_child_uom
3826 FROM mtl_system_items_b msi
3827 WHERE msi.inventory_item_id = cr.curCII and
3828 msi.organization_id = cr.curOI;
3829
3830 OPEN conv(t_master_uom, t_child_uom, cr.curCII, t_master_org_id);
3831 Fetch conv INTO t_conversion_rate;
3832 IF conv%NOTFOUND THEN
3833 close conv;
3834 raise inv_uom_conv_exe;
3835 End if;
3836 close conv;
3837
3838 -- If cost_organization is Master organization THEN the item cost should be
3839 -- calculated by multiplying the conversion_rate.
3840
3841 IF is_cost_organization <> 'Y' THEN
3842 UPDATE BOM_EXPLOSIONS_ALL
3843 SET item_cost = item_cost*t_conversion_rate
3844 WHERE group_id = cr.curGI and
3845 component_sequence_id = cr.curCSI and
3846 bill_sequence_id = cr.curBSI and
3847 common_bill_sequence_id = cr.curCBSI;
3848 END IF;
3849
3850 UPDATE BOM_EXPLOSIONS_ALL
3851 SET component_quantity = component_quantity/t_conversion_rate,
3852 extended_quantity = extended_quantity/t_conversion_rate,
3853 -- item_cost = item_cost*t_conversion_rate,
3854 primary_uom_code = cr.curPUC,
3855 primary_unit_of_measure = cr.curPUM
3856 WHERE group_id = cr.curGI and
3857 component_sequence_id = cr.curCSI and
3858 bill_sequence_id = cr.curBSI and
3859 common_bill_sequence_id = cr.curCBSI;
3860
3861 END LOOP;
3862 END IF;
3863
3864 /* Update the explosion to make sure reexplosion doesn't happen */
3865
3866 /* insert attachments for level 0 */
3867
3868 /*
3869 for c in (select rexplode_flag from BOM_EXPLOSIONS_ALL
3870 where group_id = grp_id
3871 and plan_level = 0)
3872 loop
3873 if c.rexplode_flag = 1
3874 then
3875 --Dbms_Output.put_line('Inserting attachments for level 0 .....................2');
3876 Insert_Attachments( p_group_id => grp_id
3877 , p_plan_level => 0
3878 );
3879 end if;
3880 end loop;
3881 */
3882
3883
3884 /* Fetch the structure type id for Packaging Hierarchy seeded type */
3885 /* Packaging hierarchy will always be rexploded
3886 FOR pkg_type IN c_Pkg_Structure_Type
3887 LOOP
3888 l_Pkg_Structure_Type_Id := pkg_type.structure_type_id;
3889 END LOOP;
3890
3891 -- Remove the rows that are disabled so far and
3892 -- recompute the pkg hky attributes (total quantity and qty at next level)
3893 IF l_Pkg_Structure_Type_Id = l_Structure_Type_Id
3894 THEN
3895
3896 DELETE FROM bom_explosions_all
3897 WHERE group_id = grp_id
3898 AND nvl(trimmed_disable_date, l_rev_date+1) <= l_rev_date;
3899
3900 UPDATE bom_explosions_all
3901 SET exploded_date = l_rev_date,
3902 object_revision_id = l_item_rev_id,
3903 minor_revision_id = l_minor_rev_id,
3904 revision_id = l_item_rev_id --insert top item's rev id
3905 --effectivity_date = l_rev_date
3906 WHERE group_id = grp_id
3907 AND sort_order = '0000001';
3908
3909 UPDATE BOM_EXPLOSIONS_ALL BE
3910 SET (quantity_of_children, total_qty_at_next_level) =
3911 (SELECT count(*), sum(component_quantity) FROM bom_explosions_all
3912 WHERE group_id = BE.group_id AND
3913 parent_sort_order = BE.sort_order)
3914 WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL;
3915
3916 IF p_autonomous_transaction = 1 THEN
3917 Commit;
3918 END IF;
3919
3920 END IF;
3921 */
3922
3923 UPDATE BOM_EXPLOSIONS_ALL
3924 SET rexplode_flag = 0
3925 WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 1;
3926
3927 IF p_autonomous_transaction = 1 THEN
3928 Commit;
3929 END IF;
3930
3931 END IF; -- IF l_dirty_node_exists = 'Y' ends here
3932
3933 /* Clear the pl/sql tables used for caching the rev information */
3934 revision_highdate_array.DELETE;
3935 component_revision_array.DELETE;
3936 component_revision_id_array.DELETE;
3937 component_revision_label_array.DELETE;
3938 asss_without_access_array.DELETE;
3939 compseqs_without_access_array.DELETE;
3940 access_flag_array.DELETE;
3941 change_policy_array.DELETE;
3942
3943 /* For Date effective BOMs:
3944 Cache the revision high date information using revision id as the index */
3945
3946 --Get the fixed revisions from the explosion
3947
3948 /*
3949 IF l_effectivity_control = 1
3950 THEN
3951 */
3952
3953 FOR r1 IN getFixedRevDetails(grp_id)
3954 LOOP
3955 --dbms_output.put_line('FR : '||r1.revision_id||'/'||r1.revision_high_date);
3956 revision_highdate_array(r1.revision_id) := r1.revision_high_date;
3957 END LOOP;
3958 /*
3959 END IF;
3960 */
3961
3962 /*
3963 IF l_effectivity_control = 4
3964 THEN
3965 */
3966 /* For the components that come under a fixed rev hiearrchy, the revisions should be
3967 derived based on the high date of the fixed rev.
3968 The fixed rev hierarchy is identified by a value at comp_fixed_revision_id
3969 BE.comp_fixed_revision_id is the revision_id of the parent node (or it could be prior to that)*/
3970
3971 /* For the components that do not come under a fixed rev hiearrchy, the revisions should be
3972 derived based on the high date of the end item rev */
3973 --dbms_output.put_line('rev table is getting built ');
3974
3975 /* Get the security predicate */
3976
3977 SELECT 'HZ_PARTY'||':'||person_party_id INTO l_person
3978 FROM fnd_user WHERE user_name = FND_Global.User_Name;
3979
3980 --dbms_output.put_line('l_person : '||l_person);
3981
3982 EGO_DATA_SECURITY.get_security_predicate(
3983 p_api_version =>1.0,
3984 p_function =>'EGO_VIEW_ITEM',
3985 p_object_name =>'EGO_ITEM',
3986 p_user_name => l_person,
3987 p_statement_type =>'EXISTS',
3988 p_pk1_alias =>'BE.PK1_VALUE',
3989 p_pk2_alias =>'BE.ORGANIZATION_ID',
3990 p_pk3_alias =>NULL,
3991 p_pk4_alias =>NULL,
3992 p_pk5_alias =>NULL,
3993 x_predicate => l_predicate,
3994 x_return_status => l_predicate_api_status);
3995 /*
3996 dbms_output.put_line('l_predicate_api_status : '||l_predicate_api_status);
3997 dbms_output.put_line(substr(l_predicate,1,250));
3998 dbms_output.put_line(substr(l_predicate,251,250));
3999 dbms_output.put_line(substr(l_predicate,501,250));
4000 dbms_output.put_line(substr(l_predicate,751,250));
4001 dbms_output.put_line(substr(l_predicate,1001,250));
4002 dbms_output.put_line(substr(l_predicate,1251,250));
4003 */
4004 IF l_predicate_api_status <> 'T'
4005 THEN
4006 Raise NO_DATA_FOUND;
4007 END IF;
4008
4009 IF l_predicate IS NULL
4010 THEN
4011 l_internal_user := 'Y';
4012 ELSE
4013 /* Select all the assemblies for which the user has no access */
4014
4015 EXECUTE IMMEDIATE 'SELECT BE.new_component_code FROM bom_explosions_all BE WHERE BE.group_id = '||grp_id||' AND comp_bill_seq_id IS NOT NULL AND NOT '|| l_predicate
4016 BULK COLLECT INTO asss_without_access_array;
4017 --dbms_output.put_line('assss without access : '||asss_without_access_array.COUNT);
4018
4019 /* Select all the leaf nodes for which the user has no access */
4020
4021 EXECUTE IMMEDIATE 'SELECT BE.component_sequence_id FROM bom_explosions_all BE WHERE BE.group_id = '||grp_id||' AND comp_bill_seq_id IS NULL AND NOT '|| l_predicate
4022 BULK COLLECT INTO compseqs_without_access_array;
4023
4024 --dbms_output.put_line('comps without access : '||compseqs_without_access_array.COUNT);
4025
4026 END IF;
4027
4028 --dbms_output.put_line('Is Internal user : '||l_internal_user);
4029
4030 /* For an internal user with view item privilege, there is no check.
4031 Also, if the user has view privilege on all the assemblies then there is
4032 no propagation of security within the hierarchy. In this case security for the
4033 leaf nodes will be applied later */
4034
4035 IF l_internal_user = 'Y' OR asss_without_access_array.COUNT = 0
4036 THEN
4037 FOR r1 IN revTable(grp_id)
4038 LOOP
4039 component_revision_array (r1.component_sequence_id) := r1.current_revision;
4040 component_revision_id_array (r1.component_sequence_id) := r1.revision_id;
4041 component_revision_label_array (r1.component_sequence_id) := r1.revision_label;
4042 access_flag_array (r1.component_sequence_id) := 'T';
4043 END LOOP;
4044 ELSE
4045 FOR r1 IN revTable(grp_id)
4046 LOOP
4047 component_revision_array (r1.component_sequence_id) := r1.current_revision;
4048 component_revision_id_array (r1.component_sequence_id) := r1.revision_id;
4049 component_revision_label_array (r1.component_sequence_id) := r1.revision_label;
4050 access_flag_array (r1.component_sequence_id) := Check_Component_Access(r1.component_code);
4051 END LOOP;
4052 END IF;
4053
4054 /* Apply the security for the leaf nodes */
4055
4056 IF compseqs_without_access_array.COUNT <> 0
4057 THEN
4058 FOR i IN 1..compseqs_without_access_array.COUNT
4059 LOOP
4060 access_flag_array (compseqs_without_access_array(i)) := 'F';
4061 END LOOP;
4062 END IF;
4063 /*
4064 END IF;
4065 */
4066 --dbms_output.put_line('rev table is DONE ');
4067
4068 /* Construct the change policy table. This will hold all the components(sub assemblies) for which
4069 there is a structure change policy */
4070
4071 --change_policy_array (0) := 'ALLOWED';
4072
4073 FOR r1 IN changePolicy(grp_id)
4074 LOOP
4075 change_policy_array (r1.component_sequence_id) := r1.policy_char_value;
4076 END LOOP;
4077
4078 /* Bulk collect all the rev specific exclusions into a table */
4079
4080 SELECT exclusion_path
4081 BULK COLLECT INTO rev_specific_exclusions_array
4082 FROM bom_explosions_all be,
4083 bom_rules_b rule,
4084 bom_exclusion_rule_def excl
4085 WHERE be.group_id = grp_id
4086 AND be.comp_bill_seq_id IS NOT NULL --get only the bills not its components
4087 AND be.comp_bill_seq_id = rule.bill_sequence_id
4088 AND rule.rule_id = excl.rule_id
4089 AND excl.from_revision_id IS NOT NULL --conditions to pickup only rev level exclusions
4090 AND excl.implementation_date IS NOT NULL -- do not pickup the pending exclusions
4091 AND excl.disable_date IS NULL -- do not pickup the disabled exclusions
4092 AND excl.acd_type = 1 -- pickup only the exclusion entries
4093 AND Get_Component_Revision(nvl(be.component_sequence_id,0)) >= (SELECT revision FROM mtl_item_revisions_b WHERE
4094 revision_id = excl.from_revision_id) AND
4095 ( excl.to_revision_id IS NULL OR
4096 Get_Component_Revision(nvl(be.component_sequence_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE
4097 revision_id = excl.to_revision_id));
4098 error_code := out_code;
4099 err_msg := out_message;
4100
4101 /*
4102 FND_STATS.GATHER_TABLE_STATS (
4103 errbuf => out_message,
4104 retcode => out_message,
4105 ownname => 'BOM',
4106 tabname => 'BOM_EXPLOSIONS_ALL'
4107 );
4108 */
4109 --DBMS_PROFILER.STOP_PROFILER;
4110
4111 EXCEPTION
4112 when exploder_error THEN
4113 IF p_autonomous_transaction = 1 THEN
4114 rollback;
4115 END IF;
4116 error_code := out_code;
4117 err_msg := out_message;
4118 WHEN parameter_error THEN
4119 IF p_autonomous_transaction = 1 THEN
4120 rollback;
4121 END IF;
4122 error_code := -1;
4123 err_msg := 'parameter error';
4124 Fnd_Msg_Pub.Build_Exc_Msg(
4125 p_pkg_name => 'BOM_EXPLODER_PUB',
4126 p_procedure_name => 'exploder_userexit',
4127 p_error_text => 'verify parameters');
4128 err_msg := Fnd_Message.Get_Encoded;
4129 WHEN inv_uom_conv_exe THEN
4130 IF p_autonomous_transaction = 1 THEN
4131 rollback;
4132 END IF;
4133 FND_MESSAGE.SET_NAME('BOM','BOM_UOMCV_INVUOMTYPE_ERR');
4134 fnd_message.Set_Token('FROMUOM',t_master_uom);
4135 fnd_message.Set_Token('TOUOM',t_child_uom);
4136 fnd_message.raise_error;
4137 WHEN OTHERS THEN
4138 IF p_autonomous_transaction = 1 THEN
4139 rollback;
4140 END IF;
4141 error_code := SQLCODE;
4142 Fnd_Msg_Pub.Build_Exc_Msg(
4143 p_pkg_name => 'BOM_EXPLODER_PUB',
4144 p_procedure_name => 'exploder_userexit',
4145 p_error_text => SQLERRM);
4146 err_msg := Fnd_Message.Get_Encoded;
4147 --ROLLBACK;
4148 END exploder_userexit_pvt;
4149
4150 procedure exploder_userexit_autonomous (
4151 verify_flag IN NUMBER DEFAULT 0,
4152 org_id IN NUMBER,
4153 order_by IN NUMBER DEFAULT 1,
4154 grp_id IN OUT NOCOPY NUMBER,
4155 session_id IN NUMBER DEFAULT 0,
4156 levels_to_explode IN NUMBER DEFAULT 60,
4157 bom_or_eng IN NUMBER DEFAULT 2,
4158 impl_flag IN NUMBER DEFAULT 2,
4159 plan_factor_flag IN NUMBER DEFAULT 2,
4160 explode_option IN NUMBER DEFAULT 3,
4161 module IN NUMBER DEFAULT 2,
4162 cst_type_id IN NUMBER DEFAULT 0,
4163 std_comp_flag IN NUMBER DEFAULT 0,
4164 expl_qty IN NUMBER DEFAULT 1,
4165 unit_number IN VARCHAR2 DEFAULT NULL,
4166 alt_desg IN VARCHAR2 DEFAULT '',
4167 comp_code IN VARCHAR2 DEFAULT '',
4168 rev_date IN DATE DEFAULT sysdate,
4169 minor_rev_id IN NUMBER DEFAULT NULL,
4170 material_ctrl IN NUMBER DEFAULT 2,
4171 lead_time IN NUMBER DEFAULT 2,
4172 object_name IN VARCHAR2 DEFAULT NULL,
4173 pk_value1 IN VARCHAR2,
4174 pk_value2 IN VARCHAR2 DEFAULT NULL,
4175 pk_value3 IN VARCHAR2 DEFAULT NULL,
4176 pk_value4 IN VARCHAR2 DEFAULT NULL,
4177 pk_value5 IN VARCHAR2 DEFAULT NULL,
4178 end_item_id IN NUMBER DEFAULT NULL,
4179 end_item_revision_id IN NUMBER DEFAULT NULL,
4180 end_item_minor_revision_id IN NUMBER DEFAULT NULL,
4181 err_msg IN OUT NOCOPY VARCHAR2,
4182 error_code IN OUT NOCOPY NUMBER,
4183 end_item_strc_revision_id IN NUMBER DEFAULT NULL,
4184 show_rev IN NUMBER DEFAULT 1,
4185 structure_rev_id IN NUMBER DEFAULT NULL,
4186 structure_type_id IN NUMBER DEFAULT NULL,
4187 filter_pbom IN VARCHAR2 DEFAULT NULL
4188 ) AS
4189
4190 pragma AUTONOMOUS_TRANSACTION;
4191
4192 BEGIN
4193
4194 exploder_userexit_pvt (
4195 verify_flag => verify_flag ,
4196 org_id => org_id ,
4197 order_by => order_by,
4198 grp_id => grp_id,
4199 session_id => session_id,
4200 levels_to_explode => levels_to_explode,
4201 bom_or_eng => bom_or_eng,
4202 impl_flag => impl_flag,
4203 plan_factor_flag => plan_factor_flag,
4204 explode_option => explode_option,
4205 module => module,
4206 cst_type_id => cst_type_id,
4207 std_comp_flag => std_comp_flag,
4208 expl_qty => expl_qty,
4209 unit_number => unit_number,
4210 alt_desg => alt_desg,
4211 comp_code => comp_code,
4212 rev_date => rev_date,
4213 minor_rev_id => minor_rev_id,
4214 material_ctrl => material_ctrl,
4215 lead_time => lead_time,
4216 object_name => object_name,
4217 pk_value1 => pk_value1,
4218 pk_value2 => pk_value2,
4219 pk_value3 => pk_value3,
4220 pk_value4 => pk_value4,
4221 pk_value5 => pk_value5,
4222 end_item_id => end_item_id,
4223 end_item_revision_id => end_item_revision_id,
4224 end_item_minor_revision_id => end_item_minor_revision_id,
4225 err_msg => err_msg,
4226 error_code => error_code,
4227 end_item_strc_revision_id => end_item_strc_revision_id,
4228 show_rev => show_rev,
4229 structure_rev_id => structure_rev_id,
4230 structure_type_id => structure_type_id,
4231 filter_pbom => filter_pbom,
4232 p_autonomous_transaction => 1);
4233
4234 END;
4235
4236 procedure exploder_userexit_non_auto (
4237 verify_flag IN NUMBER DEFAULT 0,
4238 org_id IN NUMBER,
4239 order_by IN NUMBER DEFAULT 1,
4240 grp_id IN OUT NOCOPY NUMBER,
4241 session_id IN NUMBER DEFAULT 0,
4242 levels_to_explode IN NUMBER DEFAULT 60,
4243 bom_or_eng IN NUMBER DEFAULT 2,
4244 impl_flag IN NUMBER DEFAULT 2,
4245 plan_factor_flag IN NUMBER DEFAULT 2,
4246 explode_option IN NUMBER DEFAULT 3,
4247 module IN NUMBER DEFAULT 2,
4248 cst_type_id IN NUMBER DEFAULT 0,
4249 std_comp_flag IN NUMBER DEFAULT 0,
4250 expl_qty IN NUMBER DEFAULT 1,
4251 unit_number IN VARCHAR2 DEFAULT NULL,
4252 alt_desg IN VARCHAR2 DEFAULT '',
4253 comp_code IN VARCHAR2 DEFAULT '',
4254 rev_date IN DATE DEFAULT sysdate,
4255 minor_rev_id IN NUMBER DEFAULT NULL,
4256 material_ctrl IN NUMBER DEFAULT 2,
4257 lead_time IN NUMBER DEFAULT 2,
4258 object_name IN VARCHAR2 DEFAULT NULL,
4259 pk_value1 IN VARCHAR2,
4260 pk_value2 IN VARCHAR2 DEFAULT NULL,
4261 pk_value3 IN VARCHAR2 DEFAULT NULL,
4262 pk_value4 IN VARCHAR2 DEFAULT NULL,
4263 pk_value5 IN VARCHAR2 DEFAULT NULL,
4264 end_item_id IN NUMBER DEFAULT NULL,
4265 end_item_revision_id IN NUMBER DEFAULT NULL,
4266 end_item_minor_revision_id IN NUMBER DEFAULT NULL,
4267 err_msg IN OUT NOCOPY VARCHAR2,
4268 error_code IN OUT NOCOPY NUMBER,
4269 end_item_strc_revision_id IN NUMBER DEFAULT NULL,
4270 show_rev IN NUMBER DEFAULT 1,
4271 structure_rev_id IN NUMBER DEFAULT NULL,
4272 structure_type_id IN NUMBER DEFAULT NULL,
4273 filter_pbom IN VARCHAR2 DEFAULT NULL) AS
4274
4275 BEGIN
4276
4277 exploder_userexit_pvt (
4278 verify_flag => verify_flag ,
4279 org_id => org_id ,
4280 order_by => order_by,
4281 grp_id => grp_id,
4282 session_id => session_id,
4283 levels_to_explode => levels_to_explode,
4284 bom_or_eng => bom_or_eng,
4285 impl_flag => impl_flag,
4286 plan_factor_flag => plan_factor_flag,
4287 explode_option => explode_option,
4288 module => module,
4289 cst_type_id => cst_type_id,
4290 std_comp_flag => std_comp_flag,
4291 expl_qty => expl_qty,
4292 unit_number => unit_number,
4293 alt_desg => alt_desg,
4294 comp_code => comp_code,
4295 rev_date => rev_date,
4296 minor_rev_id => minor_rev_id,
4297 material_ctrl => material_ctrl,
4298 lead_time => lead_time,
4299 object_name => object_name,
4300 pk_value1 => pk_value1,
4301 pk_value2 => pk_value2,
4302 pk_value3 => pk_value3,
4303 pk_value4 => pk_value4,
4304 pk_value5 => pk_value5,
4305 end_item_id => end_item_id,
4306 end_item_revision_id => end_item_revision_id,
4307 end_item_minor_revision_id => end_item_minor_revision_id,
4308 err_msg => err_msg,
4309 error_code => error_code,
4310 end_item_strc_revision_id => end_item_strc_revision_id,
4311 show_rev => show_rev,
4312 structure_rev_id => structure_rev_id,
4313 structure_type_id => structure_type_id,
4314 filter_pbom => filter_pbom,
4315 p_autonomous_transaction => 2);
4316
4317 END;
4318
4319 procedure exploder_userexit (
4320 verify_flag IN NUMBER DEFAULT 0,
4321 org_id IN NUMBER,
4322 order_by IN NUMBER DEFAULT 1,
4323 grp_id IN OUT NOCOPY NUMBER,
4324 session_id IN NUMBER DEFAULT 0,
4325 levels_to_explode IN NUMBER DEFAULT 60,
4326 bom_or_eng IN NUMBER DEFAULT 2,
4327 impl_flag IN NUMBER DEFAULT 2,
4328 plan_factor_flag IN NUMBER DEFAULT 2,
4329 explode_option IN NUMBER DEFAULT 3,
4330 module IN NUMBER DEFAULT 2,
4331 cst_type_id IN NUMBER DEFAULT 0,
4332 std_comp_flag IN NUMBER DEFAULT 0,
4333 expl_qty IN NUMBER DEFAULT 1,
4334 unit_number IN VARCHAR2 DEFAULT NULL,
4335 alt_desg IN VARCHAR2 DEFAULT '',
4336 comp_code IN VARCHAR2 DEFAULT '',
4337 rev_date IN DATE DEFAULT sysdate,
4338 minor_rev_id IN NUMBER DEFAULT NULL,
4339 material_ctrl IN NUMBER DEFAULT 2,
4340 lead_time IN NUMBER DEFAULT 2,
4341 object_name IN VARCHAR2 DEFAULT NULL,
4342 pk_value1 IN VARCHAR2,
4343 pk_value2 IN VARCHAR2 DEFAULT NULL,
4344 pk_value3 IN VARCHAR2 DEFAULT NULL,
4345 pk_value4 IN VARCHAR2 DEFAULT NULL,
4346 pk_value5 IN VARCHAR2 DEFAULT NULL,
4347 end_item_id IN NUMBER DEFAULT NULL,
4348 end_item_revision_id IN NUMBER DEFAULT NULL,
4349 end_item_minor_revision_id IN NUMBER DEFAULT NULL,
4350 err_msg IN OUT NOCOPY VARCHAR2,
4351 error_code IN OUT NOCOPY NUMBER,
4352 end_item_strc_revision_id IN NUMBER DEFAULT NULL,
4353 show_rev IN NUMBER DEFAULT 1,
4354 structure_rev_id IN NUMBER DEFAULT NULL,
4355 structure_type_id IN NUMBER DEFAULT NULL,
4356 filter_pbom IN VARCHAR2 DEFAULT NULL,
4357 p_autonomous_transaction IN NUMBER DEFAULT 1 ) AS
4358
4359 BEGIN
4360
4361 IF p_autonomous_transaction = 1
4362 THEN
4363 exploder_userexit_autonomous (
4364 verify_flag => verify_flag ,
4365 org_id => org_id ,
4366 order_by => order_by,
4367 grp_id => grp_id,
4368 session_id => session_id,
4369 levels_to_explode => levels_to_explode,
4370 bom_or_eng => bom_or_eng,
4371 impl_flag => impl_flag,
4372 plan_factor_flag => plan_factor_flag,
4373 explode_option => explode_option,
4374 module => module,
4375 cst_type_id => cst_type_id,
4376 std_comp_flag => std_comp_flag,
4377 expl_qty => expl_qty,
4378 unit_number => unit_number,
4379 alt_desg => alt_desg,
4380 comp_code => comp_code,
4381 rev_date => rev_date,
4382 minor_rev_id => minor_rev_id,
4383 material_ctrl => material_ctrl,
4384 lead_time => lead_time,
4385 object_name => object_name,
4386 pk_value1 => pk_value1,
4387 pk_value2 => pk_value2,
4388 pk_value3 => pk_value3,
4389 pk_value4 => pk_value4,
4390 pk_value5 => pk_value5,
4391 end_item_id => end_item_id,
4392 end_item_revision_id => end_item_revision_id,
4393 end_item_minor_revision_id => end_item_minor_revision_id,
4394 err_msg => err_msg,
4395 error_code => error_code,
4396 end_item_strc_revision_id => end_item_strc_revision_id,
4397 show_rev => show_rev,
4398 structure_rev_id => structure_rev_id,
4399 structure_type_id => structure_type_id,
4400 filter_pbom => filter_pbom);
4401 ELSE
4402 exploder_userexit_non_auto (
4403 verify_flag => verify_flag ,
4404 org_id => org_id ,
4405 order_by => order_by,
4406 grp_id => grp_id,
4407 session_id => session_id,
4408 levels_to_explode => levels_to_explode,
4409 bom_or_eng => bom_or_eng,
4410 impl_flag => impl_flag,
4411 plan_factor_flag => plan_factor_flag,
4412 explode_option => explode_option,
4413 module => module,
4414 cst_type_id => cst_type_id,
4415 std_comp_flag => std_comp_flag,
4416 expl_qty => expl_qty,
4417 unit_number => unit_number,
4418 alt_desg => alt_desg,
4419 comp_code => comp_code,
4420 rev_date => rev_date,
4421 minor_rev_id => minor_rev_id,
4422 material_ctrl => material_ctrl,
4423 lead_time => lead_time,
4424 object_name => object_name,
4425 pk_value1 => pk_value1,
4426 pk_value2 => pk_value2,
4427 pk_value3 => pk_value3,
4428 pk_value4 => pk_value4,
4429 pk_value5 => pk_value5,
4430 end_item_id => end_item_id,
4431 end_item_revision_id => end_item_revision_id,
4432 end_item_minor_revision_id => end_item_minor_revision_id,
4433 err_msg => err_msg,
4434 error_code => error_code,
4435 end_item_strc_revision_id => end_item_strc_revision_id,
4436 show_rev => show_rev,
4437 structure_rev_id => structure_rev_id,
4438 structure_type_id => structure_type_id,
4439 filter_pbom => filter_pbom);
4440 END IF;
4441 END;
4442
4443 FUNCTION Get_Top_Bill_Sequence_Id RETURN NUMBER
4444 IS
4445 BEGIN
4446 Return BOM_EXPLODER_PUB.p_Top_Bill_Sequence_Id;
4447 END;
4448
4449 FUNCTION Get_Explosion_Date RETURN DATE IS
4450 BEGIN
4451 Return BOM_EXPLODER_PUB.p_Explosion_Date;
4452 END;
4453
4454 FUNCTION Get_Expl_End_Item_Rev RETURN NUMBER IS
4455 BEGIN
4456 Return BOM_EXPLODER_PUB.p_Expl_End_Item_Rev;
4457 END;
4458
4459 FUNCTION Get_Expl_End_Item_Rev_Code RETURN VARCHAR2 IS
4460 BEGIN
4461 Return BOM_EXPLODER_PUB.p_Expl_End_Item_Rev_Code;
4462 END;
4463
4464 FUNCTION Get_Expl_Unit_Number RETURN VARCHAR2 IS
4465 BEGIN
4466 Return BOM_EXPLODER_PUB.p_Expl_Unit_Number;
4467 END;
4468
4469 FUNCTION Get_Explode_Option RETURN NUMBER IS
4470 BEGIN
4471 Return BOM_EXPLODER_PUB.p_Explode_Option;
4472 END;
4473
4474 FUNCTION Get_Group_Id RETURN NUMBER IS
4475 BEGIN
4476 Return BOM_EXPLODER_PUB.p_Group_Id;
4477 END;
4478
4479 FUNCTION Get_Top_Effectivity_Control RETURN NUMBER IS
4480 BEGIN
4481 Return BOM_EXPLODER_PUB.p_Group_Id;
4482 END;
4483
4484 FUNCTION Get_Component_Revision(p_component_sequence_id NUMBER) RETURN VARCHAR2 IS
4485 BEGIN
4486 Return BOM_EXPLODER_PUB.component_revision_array(p_component_sequence_id);
4487 END;
4488
4489 FUNCTION Get_Component_Revision_Id(p_component_sequence_id NUMBER) RETURN NUMBER IS
4490 BEGIN
4491 Return BOM_EXPLODER_PUB.component_revision_id_array(p_component_sequence_id);
4492 END;
4493
4494 FUNCTION Get_Component_Revision_Label(p_component_sequence_id NUMBER) RETURN VARCHAR2 IS
4495 BEGIN
4496 Return BOM_EXPLODER_PUB.component_revision_label_array(p_component_sequence_id);
4497 END;
4498
4499 FUNCTION Get_Revision_HighDate(p_revision_id NUMBER) RETURN DATE IS
4500 BEGIN
4501 Return BOM_EXPLODER_PUB.revision_highdate_array(p_revision_id);
4502 END;
4503
4504 FUNCTION Get_Component_Access_Flag(p_component_sequence_id NUMBER) RETURN VARCHAR2 IS
4505 BEGIN
4506 Return BOM_EXPLODER_PUB.access_flag_array(p_component_sequence_id);
4507 END;
4508
4509 FUNCTION Get_EGO_User RETURN VARCHAR2 IS
4510 BEGIN
4511 Return G_EGOUser;
4512 END;
4513
4514 FUNCTION Get_Current_Revision_Code RETURN VARCHAR2 IS
4515 BEGIN
4516 Return BOM_EXPLODER_PUB.p_current_revision_code;
4517 END;
4518
4519 FUNCTION Get_Current_Revision_Id RETURN NUMBER IS
4520 BEGIN
4521 Return BOM_EXPLODER_PUB.p_current_revision_id;
4522 END;
4523
4524 FUNCTION Get_Current_Revision_Label RETURN VARCHAR2 IS
4525 BEGIN
4526 Return BOM_EXPLODER_PUB.p_current_revision_label;
4527 END;
4528
4529 FUNCTION Get_Change_Policy(p_component_sequence_id NUMBER) RETURN VARCHAR2 IS
4530 BEGIN
4531 --Return nvl(BOM_EXPLODER_PUB.change_policy_array(p_component_sequence_id),'ALLOWED');
4532 Return BOM_EXPLODER_PUB.change_policy_array(p_component_sequence_id);
4533 END;
4534
4535 FUNCTION Get_Current_RevisionId( p_inventory_item_id IN NUMBER,
4536 p_organization_id IN NUMBER,
4537 p_effectivity_date IN DATE) RETURN NUMBER IS
4538 CURSOR c1 IS
4539 SELECT revision, revision_id,revision_label FROM mtl_item_revisions_b WHERE
4540 inventory_item_id = p_inventory_item_id AND organization_id = p_organization_id AND
4541 effectivity_date <= p_effectivity_date
4542 AND ((BOM_GLOBALS.get_show_Impl_comps_only = 'Y' AND implementation_date IS NOT NULL) OR BOM_GLOBALS.get_show_Impl_comps_only = 'N') -- added for Bug 7242865
4543 ORDER BY effectivity_date DESC;
4544
4545 BEGIN
4546
4547 OPEN c1;
4548 FETCH c1 INTO p_current_revision_code, p_current_revision_id, p_current_revision_label;
4549 IF c1%ROWCOUNT = 0
4550 THEN
4551 p_current_revision_code := null ;
4552 p_current_revision_id := null;
4553 p_current_revision_label := null;
4554 END IF;
4555 CLOSE c1;
4556 Return p_current_revision_id;
4557 EXCEPTION WHEN OTHERS THEN
4558 p_current_revision_code := null ;
4559 p_current_revision_id := null;
4560 p_current_revision_label := null;
4561 Return null;
4562
4563 END;
4564
4565 END BOM_EXPLODER_PUB;