[Home] [Help]
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
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
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
250: Return r1.bill_seq_id;
251: END LOOP;
252: */
253:
254: SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
255: WHERE BBOM_C.assembly_item_id = p_pk1_value AND BBOM_C.organization_id = p_organization_id AND
256: BBOM_C.alternate_bom_designator = p_top_alternate_designator;
257:
258: ELSIF (p_obj_name IS NOT NULL AND p_top_alternate_designator IS NULL) THEN
256: BBOM_C.alternate_bom_designator = p_top_alternate_designator;
257:
258: ELSIF (p_obj_name IS NOT NULL AND p_top_alternate_designator IS NULL) THEN
259:
260: SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
261: WHERE BBOM_C.obj_name = P_OBJ_NAME AND BBOM_C.pk1_value = p_pk1_value AND
262: BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator is NULL;
263:
264: ELSIF (p_obj_name IS NOT NULL AND p_top_alternate_designator IS NOT NULL) THEN
262: BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator is NULL;
263:
264: ELSIF (p_obj_name IS NOT NULL AND p_top_alternate_designator IS NOT NULL) THEN
265:
266: SELECT BBOM_C.bill_sequence_id INTO l_bill_sequence_id FROM bom_structures_b BBOM_C
267: WHERE BBOM_C.obj_name = P_OBJ_NAME AND BBOM_C.pk1_value = p_pk1_value AND
268: BBOM_C.organization_id = p_organization_id AND BBOM_C.alternate_bom_designator = p_top_alternate_designator;
269:
270: END IF;
322:
323: SELECT
324: ecp.policy_char_value INTO l_change_policy_char_val
325: FROM
326: MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP, Bom_Structures_b bsb
327: WHERE
328: ecp.policy_object_pk1_value =
329: (SELECT TO_CHAR(ic.item_catalog_group_id)
330: FROM mtl_item_catalog_groups_b ic
464: FROM BOM_STRUCTURE_TYPES_B bst1
465: CONNECT BY PRIOR bst1.parent_structure_type_id = bst1.structure_type_id
466: START WITH bst1.structure_type_id =
467: ( select strb.structure_type_id
468: from bom_structures_b strb
469: where strb.bill_sequence_id = bet.bill_sequence_id
470: )
471: )
472: )
861: to_number(NULL) COMP_EFFECTIVITY_CONTROL
862: FROM
863: --BOM_PLM_EXPLOSION_TEMP BET,
864: BOM_EXPLOSIONS_ALL BET,
865: BOM_STRUCTURES_B BOM,
866: BOM_COMPONENTS_B BIC
867: WHERE BET.GROUP_ID = c_grp_id
868: AND BET.PLAN_LEVEL = c_level - 1
869: /* Do not explode the component if it is a pending change (disable, change)) */
1139: Cursor l_TopBill_csr is
1140: Select msi.concatenated_segments,
1141: bom.alternate_bom_designator
1142: From mtl_system_items_b_kfv msi,
1143: bom_structures_b bom,
1144: BOM_EXPLOSIONS_ALL bet
1145: Where msi.inventory_item_id = bom.assembly_item_id
1146: And msi.organization_id = bom.organization_id
1147: And bom.bill_sequence_id = bet.top_bill_sequence_id
1942: SELECT max(common_bill_sequence_id), max(structure_type_id), max(is_preferred),
1943: max(implementation_date), max(source_bill_sequence_id),max(assembly_type), max(effectivity_control)
1944: INTO comp_common_bill_seq_tbl(i), structure_type_id_tbl(i), is_preferred_tbl(i), bom_implementation_date_tbl(i),
1945: comp_source_bill_seq_tbl(i),assembly_type_tbl(i), comp_effectivity_control_tbl(i)
1946: FROM bom_structures_b WHERE
1947: bill_sequence_id = comp_bill_seq_tbl(i);
1948:
1949: -- Update the change policy value if the component has a bill
1950: /*
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;
2738: CURSOR getPreferredStructure ( p_obj_name IN VARCHAR2,
2739: p_pk1_value IN VARCHAR2,
2740: p_pk2_value IN VARCHAR2,
2741: p_structure_type_id IN NUMBER) IS
2742: SELECT alternate_bom_designator FROM bom_structures_b WHERE ( (p_obj_name IS NULL AND obj_name IS NULL)
2743: OR (p_obj_name IS NOT NULL AND obj_name = p_obj_name)) AND pk1_value = p_pk1_value AND
2744: pk2_value = p_pk2_value AND structure_type_id = p_structure_type_id;
2745:
2746:
3008: BEGIN
3009:
3010: SELECT bill_sequence_id,common_bill_sequence_id,effectivity_control, bill_sequence_id, structure_type_id, assembly_type
3011: INTO l_bill_sequence_id, l_common_bill_sequence_id, l_effectivity_control,l_top_bill_sequence_id, l_structure_type_id, l_bom_or_eng
3012: FROM bom_structures_b bom
3013: WHERE nvl(bom.obj_name,'EGO_ITEM') = nvl(object_name,'EGO_ITEM')
3014: AND bom.pk1_value = pk_value1
3015: AND nvl(bom.pk2_value,'-1') = nvl(pk_value2,'-1')
3016: AND bom.organization_id = org_id
3099: -- If item BOM is commoning an item BOM, THEN we get the current item rev FROM the
3100: -- common BOM to explode in the case of end irem revision effective
3101:
3102: SELECT pk1_value, pk2_value,obj_name INTO l_pk_value1, l_pk_value2, l_obj_name FROM
3103: bom_structures_b WHERE bill_sequence_id = l_common_bill_sequence_id;
3104:
3105: IF (l_obj_name IS NOT NULL) -- NON ITEM
3106: THEN
3107: -- If item BOM is commoning a CAD BOM, THEN we get the current item rev FROM the same
3325: bom.implementation_date,
3326: 'Y',
3327: bom.source_bill_sequence_id,
3328: bom.effectivity_control
3329: FROM bom_structures_b bom
3330: where bill_sequence_id = l_bill_sequence_id);
3331:
3332: IF (SQL%NOTFOUND) THEN
3333: insert INTO BOM_EXPLOSIONS_ALL
3816: FOR cr IN cur(grp_id) LOOP
3817:
3818: SELECT msi.primary_uom_code, msi.organization_id into
3819: t_master_uom, t_master_org_id
3820: FROM mtl_system_items_b msi, bom_structures_b bbm
3821: WHERE cr.curCBSI = bbm.bill_sequence_id and
3822: bbm.organization_id = msi.organization_id and
3823: msi.inventory_item_id = cr.curCII;
3824: