[Home] [Help]
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
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
372: , p_plan_level IN NUMBER
373: )
374: IS
375: BEGIN
376: INSERT INTO BOM_EXPLOSIONS_ALL
377: ( top_bill_sequence_id
378: ,bill_sequence_id
379: ,organization_id
380: ,explosion_type
443: 0, -- default insert attachment with rexplode flag of 0
444: BET.exploded_date,
445: BET.exploded_unit_number,
446: BET.exploded_end_item_rev
447: FROM BOM_EXPLOSIONS_ALL BET ,
448: FND_ATTACHED_DOCUMENTS ATDOCS,
449: FND_DOCUMENTS_TL DOCTL
450: WHERE ATDOCS.DOCUMENT_ID = DOCTL.DOCUMENT_ID
451: AND DOCTL.LANGUAGE = USERENV('LANG')
513: AND d.disable_date IS NULL
514: AND d.acd_type = 1
515: AND r.bill_sequence_id IN
516: (SELECT bill_sequence_id
517: FROM BOM_EXPLOSIONS_ALL
518: WHERE group_id = p_Group_Id
519: );
520:
521: IF (reApply = 1) THEN
518: WHERE group_id = p_Group_Id
519: );
520:
521: IF (reApply = 1) THEN
522: UPDATE BOM_EXPLOSIONS_ALL
523: SET is_excluded_by_rule = NULL
524: WHERE group_id = p_Group_Id
525: AND is_excluded_by_rule = 'Y';
526: END IF;
524: WHERE group_id = p_Group_Id
525: AND is_excluded_by_rule = 'Y';
526: END IF;
527:
528: UPDATE BOM_EXPLOSIONS_ALL
529: SET reapply_exclusions = NULL
530: WHERE group_id = p_Group_Id AND plan_level = 0;
531:
532: FORALL i in 1..exclusion_t.count
529: SET reapply_exclusions = NULL
530: WHERE group_id = p_Group_Id AND plan_level = 0;
531:
532: FORALL i in 1..exclusion_t.count
533: UPDATE BOM_EXPLOSIONS_ALL
534: SET is_excluded_by_rule = 'Y'
535: WHERE group_id = p_Group_Id
536: AND new_component_code like exclusion_t(i);
537: END;/* Procedure Apply_Exclusion_Rules Ends */
557: AND d.acd_type = 1
558: AND r.bill_sequence_id = p_bill_sequence_id;
559:
560: FORALL i in 1..exclusion_t.count
561: UPDATE BOM_EXPLOSIONS_ALL
562: SET is_excluded_by_rule = 'Y'
563: WHERE group_id IN
564: (SELECT t.Group_Id FROM BOM_EXPLOSIONS_ALL t
565: WHERE t.bill_sequence_id = p_bill_sequence_id
560: FORALL i in 1..exclusion_t.count
561: UPDATE BOM_EXPLOSIONS_ALL
562: SET is_excluded_by_rule = 'Y'
563: WHERE group_id IN
564: (SELECT t.Group_Id FROM BOM_EXPLOSIONS_ALL t
565: WHERE t.bill_sequence_id = p_bill_sequence_id
566: )
567: AND new_component_code like exclusion_t(i);
568: END; /* Procedure Apply_New_Exclusion_Rules Ends */
577: ******************************************************************************/
578: PROCEDURE Set_Reapply_Exclusion_Flag (p_bill_sequence_id IN NUMBER)
579: IS
580: BEGIN
581: UPDATE BOM_EXPLOSIONS_ALL
582: SET reapply_exclusions = 'Y'
583: WHERE Top_bill_sequence_id IN
584: (SELECT Top_bill_sequence_id FROM BOM_EXPLOSIONS_ALL
585: WHERE bill_sequence_id = p_bill_sequence_id
580: BEGIN
581: UPDATE BOM_EXPLOSIONS_ALL
582: SET reapply_exclusions = 'Y'
583: WHERE Top_bill_sequence_id IN
584: (SELECT Top_bill_sequence_id FROM BOM_EXPLOSIONS_ALL
585: WHERE bill_sequence_id = p_bill_sequence_id
586: )
587: AND plan_level = 0;
588: END;/* Procedure Set_Reapply_Exclusion_Flag Ends */
860: to_number(NULL) CSBSI,
861: to_number(NULL) COMP_EFFECTIVITY_CONTROL
862: FROM
863: --BOM_PLM_EXPLOSION_TEMP BET,
864: BOM_EXPLOSIONS_ALL BET,
865: BOM_STRUCTURES_B BOM,
866: BOM_COMPONENTS_B BIC
867: WHERE BET.GROUP_ID = c_grp_id
868: AND BET.PLAN_LEVEL = c_level - 1
1140: Select msi.concatenated_segments,
1141: bom.alternate_bom_designator
1142: From mtl_system_items_b_kfv msi,
1143: bom_structures_b bom,
1144: BOM_EXPLOSIONS_ALL bet
1145: Where msi.inventory_item_id = bom.assembly_item_id
1146: And msi.organization_id = bom.organization_id
1147: And bom.bill_sequence_id = bet.top_bill_sequence_id
1148: And bet.group_id = grp_id
1971: INSERT INTO bom_plm_explosion_temp VALUES be_temp_TBL(i);
1972: */
1973:
1974: FORALL i IN 1..top_bill_sequence_id_tbl.COUNT
1975: --INSERT /*+append */ INTO BOM_EXPLOSIONS_ALL
1976: INSERT INTO BOM_EXPLOSIONS_ALL
1977: (
1978: TOP_BILL_SEQUENCE_ID ,
1979: BILL_SEQUENCE_ID ,
1972: */
1973:
1974: FORALL i IN 1..top_bill_sequence_id_tbl.COUNT
1975: --INSERT /*+append */ INTO BOM_EXPLOSIONS_ALL
1976: INSERT INTO BOM_EXPLOSIONS_ALL
1977: (
1978: TOP_BILL_SEQUENCE_ID ,
1979: BILL_SEQUENCE_ID ,
1980: COMMON_BILL_SEQUENCE_ID ,
2270:
2271: --Dbms_Output.put_line('g_parent_sort_order_tbl.COUNT : '||g_parent_sort_order_tbl.COUNT);
2272:
2273: FORALL i IN 1..g_parent_sort_order_tbl.COUNT
2274: UPDATE BOM_EXPLOSIONS_ALL
2275: SET quantity_of_children = g_quantity_of_children_tbl(i),
2276: total_qty_at_next_level = g_total_qty_at_next_level_tbl(i)
2277: WHERE group_id = grp_id
2278: AND sort_order = g_parent_sort_order_tbl(i);
2276: total_qty_at_next_level = g_total_qty_at_next_level_tbl(i)
2277: WHERE group_id = grp_id
2278: AND sort_order = g_parent_sort_order_tbl(i);
2279:
2280: UPDATE BOM_EXPLOSIONS_ALL bet SET (bet.primary_uom_code, bet.eng_item_flag, bet.primary_unit_of_measure) =
2281: (SELECT msi.primary_uom_code
2282: , msi.eng_item_flag
2283: , mum.unit_of_measure
2284: FROM mtl_system_items_b msi
2514: SELECT
2515: COMPONENT_CODE,
2516: LOOP_FLAG,
2517: PLAN_LEVEL
2518: FROM BOM_EXPLOSIONS_ALL
2519: WHERE GROUP_ID = c_group_id
2520: AND LOOP_FLAG = 1;
2521: BEGIN
2522:
2521: BEGIN
2522:
2523: SELECT NVL( TOP_ALTERNATE_DESIGNATOR, 'none' ), ORGANIZATION_ID
2524: INTO top_alt, org_id
2525: FROM BOM_EXPLOSIONS_ALL
2526: WHERE GROUP_ID = grp_id
2527: AND ROWNUM = 1
2528: AND PLAN_LEVEL = 0;
2529:
2661: BET.common_bill_sequence_id curCBSI,
2662: BET.group_id curGI,
2663: BET.primary_uom_code curPUC,
2664: BET.primary_unit_of_measure curPUM
2665: FROM BOM_EXPLOSIONS_ALL BET, bom_structures_b BOM
2666: WHERE BET.group_id = p_group_id AND BET.obj_name IS NULL AND
2667: BET.bill_sequence_id <> BET.common_bill_sequence_id AND
2668: BET.source_bill_sequence_id = BOM.BILL_SEQUENCE_ID AND
2669: BET.plan_level <> 0;
2744: pk2_value = p_pk2_value AND structure_type_id = p_structure_type_id;
2745:
2746:
2747: CURSOR c_dirty_nodes (p_group_id IN NUMBER ) IS
2748: SELECT sort_order, comp_common_bill_seq_id, comp_bill_seq_id FROM BOM_EXPLOSIONS_ALL WHERE
2749: group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 1
2750: ORDER BY sort_order;
2751:
2752: CURSOR c_get_first_revision (p_inventory_item_id IN NUMBER,
2762: WHERE structure_type_name = 'Packaging Hierarchy';
2763:
2764: CURSOR getComponentFixedRevisions (p_group_id IN NUMBER) IS
2765: SELECT bet.component_sequence_id, bet.component_item_revision_id revision_id,
2766: mir.revision revision FROM bom_explosions_all bet, mtl_item_revisions_b mir
2767: WHERE bet.group_id = p_group_id AND bet.plan_level <> 0 AND nvl(bet.component_item_revision_id,0) <> 0
2768: AND bet.component_item_revision_id = mir.revision_id;
2769:
2770: CURSOR getFixedRevDetails (p_group_id IN NUMBER) IS
2788: NVL( MIN(rev2.Effectivity_Date - 1/(60*60*24)),
2789: GREATEST(TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'), reV1.Effectivity_Date)
2790: ) High_Date,
2791: rev1.Implementation_Date, rev1.Change_Notice, rev1.revision_label
2792: FROM bom_explosions_all bet, Mtl_Item_Revisions_B rev2 , Mtl_Item_Revisions_B rev1
2793: WHERE bet.group_id = p_group_id AND bet.component_item_revision_id IS NOT NULL AND
2794: rev1.revision_id = bet.component_item_revision_id AND rev1.Organization_Id = rev2.Organization_Id(+)
2795: AND rev1.Inventory_Item_Id = rev2.Inventory_Item_Id(+) AND rev2.Effectivity_Date(+) > rev1.Effectivity_Date
2796: AND rev2.implementation_date (+) IS NOT NULL
2810: nvl(BE.component_item_revision_id,BOM_EXPLODER_PUB.Get_Current_Revision_Id) REVISION_ID,
2811: --nvl(BE.revision_label,
2812: BOM_EXPLODER_PUB.Get_Current_Revision_Label revision_label,
2813: BE.new_component_code component_code
2814: FROM bom_explosions_all BE WHERE BE.group_id = p_group_id;
2815:
2816: CURSOR revTableWithAccessFlag (p_group_id IN NUMBER) IS
2817: SELECT nvl(BE.component_sequence_id,0) component_sequence_id, nvl(BE.current_revision,
2818: BOM_EXPLODER_PUB.Get_Current_RevisionDetails(BE.component_item_id,
2832: NULL,
2833: NULL,
2834: NULL,
2835: BOM_EXPLODER_PUB.Get_EGO_User) ACCESS_FLAG
2836: FROM bom_explosions_all BE WHERE BE.group_id = p_group_id;
2837:
2838:
2839: CURSOR changePolicy (p_group_id IN NUMBER) IS
2840: SELECT
2844: NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
2845: msi.item_catalog_group_id item_catalog_group_id,
2846: msi.inventory_item_id, msi.organization_id , mirb.revision_id,
2847: bet.component_sequence_id, bet.structure_type_id
2848: FROM bom_explosions_all bet, mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
2849: WHERE bet.group_id = p_group_id AND bet.comp_bill_seq_id IS NOT NULL
2850: AND bet.component_item_id = msi.INVENTORY_ITEM_ID AND
2851: bet.organization_id = msi.ORGANIZATION_ID AND
2852: mirb.revision_id = BOM_EXPLODER_PUB.Get_Component_Revision_Id(NVL(BET.component_sequence_id,0))
3181: , l_exploded_option
3182: , l_explosion_group_id
3183: , l_reapply_exclusions
3184: , l_max_bill_level
3185: FROM BOM_EXPLOSIONS_ALL
3186: WHERE top_bill_sequence_id = l_bill_sequence_id
3187: AND exploded_option = l_explode_option
3188: AND plan_level = 0;
3189:
3194: THEN
3195: l_rexplode_flag := 1;
3196: --set the rexplode flag if max bill level is changed.
3197:
3198: UPDATE BOM_EXPLOSIONS_ALL
3199: SET rexplode_flag = 1
3200: WHERE group_id = grp_id
3201: AND sort_order = '0000001';
3202:
3199: SET rexplode_flag = 1
3200: WHERE group_id = grp_id
3201: AND sort_order = '0000001';
3202:
3203: DELETE FROM BOM_EXPLOSIONS_ALL
3204: WHERE group_id = grp_id
3205: AND sort_order <> '0000001';
3206:
3207: --Always do an engg explosion for packaging hierarchies
3222: EXCEPTION WHEN NO_DATA_FOUND
3223: THEN
3224: -- Insert for plan level 0
3225:
3226: SELECT BOM_EXPLOSIONS_ALL_S.NEXTVAL INTO grp_id FROM dual;
3227:
3228: insert INTO BOM_EXPLOSIONS_ALL
3229: (
3230: group_id,
3224: -- Insert for plan level 0
3225:
3226: SELECT BOM_EXPLOSIONS_ALL_S.NEXTVAL INTO grp_id FROM dual;
3227:
3228: insert INTO BOM_EXPLOSIONS_ALL
3229: (
3230: group_id,
3231: bill_sequence_id,
3232: common_bill_sequence_id,
3329: FROM bom_structures_b bom
3330: where bill_sequence_id = l_bill_sequence_id);
3331:
3332: IF (SQL%NOTFOUND) THEN
3333: insert INTO BOM_EXPLOSIONS_ALL
3334: (
3335: group_id,
3336: top_item_id,
3337: component_item_id,
3554: ELSE
3555:
3556: /* Reexplode otherwise */
3557:
3558: UPDATE BOM_EXPLOSIONS_ALL
3559: SET rexplode_flag = 1
3560: WHERE group_id = grp_id
3561: AND sort_order = '0000001';
3562:
3559: SET rexplode_flag = 1
3560: WHERE group_id = grp_id
3561: AND sort_order = '0000001';
3562:
3563: DELETE FROM BOM_EXPLOSIONS_ALL
3564: WHERE group_id = grp_id
3565: AND sort_order <> '0000001';
3566:
3567: --Commit;
3593:
3594: l_dirty_node_exists := 'Y';
3595:
3596: --Dbms_Output.put_line('Clean up the dirty nodes before the explosion : '||r1.sort_order);
3597: DELETE FROM BOM_EXPLOSIONS_ALL
3598: WHERE group_id = grp_id
3599: AND sort_order like r1.sort_order||'%' AND sort_order <> r1.sort_order;
3600:
3601: --Update the change_policy value for the leaf component that has become a subassembly.
3598: WHERE group_id = grp_id
3599: AND sort_order like r1.sort_order||'%' AND sort_order <> r1.sort_order;
3600:
3601: --Update the change_policy value for the leaf component that has become a subassembly.
3602: UPDATE BOM_EXPLOSIONS_ALL
3603: SET CHANGE_POLICY_VALUE = Get_Change_Policy_Val(0, r1.comp_bill_seq_id)
3604: WHERE sort_order = r1.sort_order
3605: AND group_id = grp_id;
3606:
3622: END IF;
3623:
3624: ELSE
3625:
3626: UPDATE BOM_EXPLOSIONS_ALL
3627: SET exploded_date = l_rev_date,
3628: --exploded_unit_number = unit_number,
3629: exploded_unit_number = l_unit_number,
3630: exploded_end_item_rev = l_end_item_revision_id,
3644: END IF;
3645:
3646: ELSE
3647:
3648: UPDATE BOM_EXPLOSIONS_ALL
3649: SET exploded_date = l_rev_date,
3650: --exploded_unit_number = unit_number,
3651: exploded_unit_number = l_unit_number,
3652: exploded_end_item_rev = l_end_item_revision_id,
3691:
3692: /* Get the BOM item type and Parent BOM item type for item BOM */
3693:
3694: /*
3695: UPDATE BOM_EXPLOSIONS_ALL
3696: SET access_flag = l_access_flag,
3697: (bom_item_type, parent_bom_item_type, primary_uom_code, eng_item_flag, primary_unit_of_measure) = (SELECT msi.bom_item_type, msi.bom_item_type, msi.primary_uom_code,
3698: msi.eng_item_flag, (select description from mtl_units_of_measure where uom_code = msi.primary_uom_code) FROM
3699: mtl_system_items_b msi WHERE msi.inventory_item_id = item_id AND msi.organization_id = org_id)
3700: WHERE group_id = grp_id
3701: AND sort_order = '0000001';
3702: */
3703:
3704: UPDATE BOM_EXPLOSIONS_ALL
3705: SET access_flag = l_access_flag,
3706: (bom_item_type, parent_bom_item_type, primary_uom_code, eng_item_flag, primary_unit_of_measure) = (SELECT msi.bom_item_type, msi.bom_item_type, msi.primary_uom_code,
3707: msi.eng_item_flag, muom.unit_of_measure FROM mtl_system_items_b msi, mtl_units_of_measure muom
3708: WHERE msi.inventory_item_id = item_id AND msi.organization_id = org_id AND muom.uom_code = msi.primary_uom_code)
3712: IF (SQL%NOTFOUND) THEN
3713: raise no_data_found;
3714: END IF;
3715:
3716: UPDATE BOM_EXPLOSIONS_ALL
3717: SET (gtin_number, gtin_description, trade_item_descriptor, top_gtin_number, top_gtin_description, top_trade_item_descriptor, trade_item_descriptor_desc, gtin_publication_status) =
3718: (SELECT gtin, description, trade_item_descriptor, gtin, description, trade_item_descriptor, trade_item_descriptor_desc, publication_status
3719: FROM ego_items_v egi
3720: WHERE inventory_item_id = item_id AND organization_id = org_id)
3721: WHERE group_id = grp_id
3722: AND sort_order = '0000001';
3723:
3724: /* Update the change policy value for the top item */
3725: UPDATE BOM_EXPLOSIONS_ALL
3726: SET CHANGE_POLICY_VALUE = Get_Change_Policy_Val(revision_id, Comp_bill_seq_Id)
3727: WHERE group_id = grp_id
3728: AND sort_order = '0000001';
3729:
3838: -- If cost_organization is Master organization THEN the item cost should be
3839: -- calculated by multiplying the conversion_rate.
3840:
3841: IF is_cost_organization <> 'Y' THEN
3842: UPDATE BOM_EXPLOSIONS_ALL
3843: SET item_cost = item_cost*t_conversion_rate
3844: WHERE group_id = cr.curGI and
3845: component_sequence_id = cr.curCSI and
3846: bill_sequence_id = cr.curBSI and
3846: bill_sequence_id = cr.curBSI and
3847: common_bill_sequence_id = cr.curCBSI;
3848: END IF;
3849:
3850: UPDATE BOM_EXPLOSIONS_ALL
3851: SET component_quantity = component_quantity/t_conversion_rate,
3852: extended_quantity = extended_quantity/t_conversion_rate,
3853: -- item_cost = item_cost*t_conversion_rate,
3854: primary_uom_code = cr.curPUC,
3865:
3866: /* insert attachments for level 0 */
3867:
3868: /*
3869: for c in (select rexplode_flag from BOM_EXPLOSIONS_ALL
3870: where group_id = grp_id
3871: and plan_level = 0)
3872: loop
3873: if c.rexplode_flag = 1
3892: -- recompute the pkg hky attributes (total quantity and qty at next level)
3893: IF l_Pkg_Structure_Type_Id = l_Structure_Type_Id
3894: THEN
3895:
3896: DELETE FROM bom_explosions_all
3897: WHERE group_id = grp_id
3898: AND nvl(trimmed_disable_date, l_rev_date+1) <= l_rev_date;
3899:
3900: UPDATE bom_explosions_all
3896: DELETE FROM bom_explosions_all
3897: WHERE group_id = grp_id
3898: AND nvl(trimmed_disable_date, l_rev_date+1) <= l_rev_date;
3899:
3900: UPDATE bom_explosions_all
3901: SET exploded_date = l_rev_date,
3902: object_revision_id = l_item_rev_id,
3903: minor_revision_id = l_minor_rev_id,
3904: revision_id = l_item_rev_id --insert top item's rev id
3905: --effectivity_date = l_rev_date
3906: WHERE group_id = grp_id
3907: AND sort_order = '0000001';
3908:
3909: UPDATE BOM_EXPLOSIONS_ALL BE
3910: SET (quantity_of_children, total_qty_at_next_level) =
3911: (SELECT count(*), sum(component_quantity) FROM bom_explosions_all
3912: WHERE group_id = BE.group_id AND
3913: parent_sort_order = BE.sort_order)
3907: AND sort_order = '0000001';
3908:
3909: UPDATE BOM_EXPLOSIONS_ALL BE
3910: SET (quantity_of_children, total_qty_at_next_level) =
3911: (SELECT count(*), sum(component_quantity) FROM bom_explosions_all
3912: WHERE group_id = BE.group_id AND
3913: parent_sort_order = BE.sort_order)
3914: WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL;
3915:
3919:
3920: END IF;
3921: */
3922:
3923: UPDATE BOM_EXPLOSIONS_ALL
3924: SET rexplode_flag = 0
3925: WHERE group_id = grp_id AND comp_bill_seq_id IS NOT NULL AND rexplode_flag = 1;
3926:
3927: IF p_autonomous_transaction = 1 THEN
4011: l_internal_user := 'Y';
4012: ELSE
4013: /* Select all the assemblies for which the user has no access */
4014:
4015: EXECUTE IMMEDIATE 'SELECT BE.new_component_code FROM bom_explosions_all BE WHERE BE.group_id = '||grp_id||' AND comp_bill_seq_id IS NOT NULL AND NOT '|| l_predicate
4016: BULK COLLECT INTO asss_without_access_array;
4017: --dbms_output.put_line('assss without access : '||asss_without_access_array.COUNT);
4018:
4019: /* Select all the leaf nodes for which the user has no access */
4017: --dbms_output.put_line('assss without access : '||asss_without_access_array.COUNT);
4018:
4019: /* Select all the leaf nodes for which the user has no access */
4020:
4021: EXECUTE IMMEDIATE 'SELECT BE.component_sequence_id FROM bom_explosions_all BE WHERE BE.group_id = '||grp_id||' AND comp_bill_seq_id IS NULL AND NOT '|| l_predicate
4022: BULK COLLECT INTO compseqs_without_access_array;
4023:
4024: --dbms_output.put_line('comps without access : '||compseqs_without_access_array.COUNT);
4025:
4078: /* Bulk collect all the rev specific exclusions into a table */
4079:
4080: SELECT exclusion_path
4081: BULK COLLECT INTO rev_specific_exclusions_array
4082: FROM bom_explosions_all be,
4083: bom_rules_b rule,
4084: bom_exclusion_rule_def excl
4085: WHERE be.group_id = grp_id
4086: AND be.comp_bill_seq_id IS NOT NULL --get only the bills not its components
4102: FND_STATS.GATHER_TABLE_STATS (
4103: errbuf => out_message,
4104: retcode => out_message,
4105: ownname => 'BOM',
4106: tabname => 'BOM_EXPLOSIONS_ALL'
4107: );
4108: */
4109: --DBMS_PROFILER.STOP_PROFILER;
4110: