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