[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
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
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
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
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;
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
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: )
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)) */
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
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: /*
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;
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:
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
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
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
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: