[Home] [Help]
77: G_STRUCT_SER_EFF CONSTANT NUMBER(1) := 3;
78: G_STRUCT_REV_EFF CONSTANT NUMBER(1) := 4;
79:
80: --
81: -- Bug 4106826 - Added variable l_bom_lists_count for debugging
82: --
83: l_bom_lists_count NUMBER;
84:
85:
79:
80: --
81: -- Bug 4106826 - Added variable l_bom_lists_count for debugging
82: --
83: l_bom_lists_count NUMBER;
84:
85:
86: -- ERES change begins
87: PROCEDURE Raise_ECO_Create_Event( p_organization_id IN NUMBER
278: FUNCTION cnt(p_list_id NUMBER)
279: RETURN NUMBER IS
280: CURSOR lc_cnt IS
281: SELECT COUNT(*)
282: FROM bom_lists
283: WHERE sequence_id = p_list_id;
284: l_cnt NUMBER;
285: BEGIN
286: OPEN lc_cnt;
297: -- DESCRIPTION
298: -- Checks if component exists with attributes matching criteria in
299: -- component interface table.
300: -- REQUIRES
301: -- List - Sequence id of list in BOM_LISTS.
302: -- ECO - Engineering change order number of Mass Change Order.
303: -- Org Id - Organization id of Mass Change Order.
304: -- MODIFIES
305: -- Error Message - PL/SQL error.
309: --
310: -- EXAMPLE
311: --
312: PROCEDURE Match_Attributes(
313: p_list_id IN bom_lists.sequence_id%TYPE,
314: p_eco IN eng_revised_items_interface.change_notice%TYPE,
315: p_org_id IN eng_revised_items_interface.organization_id%TYPE,
316: x_error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
317: IS
321:
322: -- Bug 4216428
323:
324: FND_FILE.PUT_LINE(FND_FILE.LOG,'Delete rev effective structures from list');
325: DELETE FROM bom_lists l
326: WHERE l.sequence_id = p_list_id
327: AND EXISTS (
328: SELECT NULL
329: FROM bom_bill_of_materials b
335: --
336: -- Bug 4106826
337: --
338: FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered Match Attributes');
339: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
340: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete st in Match attributes='||
341: to_char(l_bom_lists_count));
342: -- end Bug 4106826
343:
336: -- Bug 4106826
337: --
338: FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered Match Attributes');
339: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
340: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete st in Match attributes='||
341: to_char(l_bom_lists_count));
342: -- end Bug 4106826
343:
344: -- Removed NVL and added AND/OR condition on Alternate_bom_desigantor to improve performance
337: --
338: FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered Match Attributes');
339: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
340: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete st in Match attributes='||
341: to_char(l_bom_lists_count));
342: -- end Bug 4106826
343:
344: -- Removed NVL and added AND/OR condition on Alternate_bom_desigantor to improve performance
345: DELETE FROM bom_lists l
341: to_char(l_bom_lists_count));
342: -- end Bug 4106826
343:
344: -- Removed NVL and added AND/OR condition on Alternate_bom_desigantor to improve performance
345: DELETE FROM bom_lists l
346: WHERE l.sequence_id = p_list_id
347: AND EXISTS (
348: SELECT NULL
349: FROM
353: ci.acd_type IN (action_replace, action_delete)
354: AND ci.revised_item_sequence_id = ri.revised_item_sequence_id
355: AND ri.change_notice = p_eco
356: AND ri.organization_id = p_org_id
357: /*make sure assemblies dont get deleted from bom_lists if there is another change on them in the same ECO*/
358: AND NOT EXISTS (
359: SELECT NULL
360: FROM eng_revised_items_interface rii,
361: bom_inventory_comps_interface cii
456: );
457:
458: x_error_message := NULL;
459: COMMIT;
460: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
461: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete st in match_attribute='||
462: to_char(l_bom_lists_count));
463:
464: EXCEPTION
457:
458: x_error_message := NULL;
459: COMMIT;
460: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
461: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete st in match_attribute='||
462: to_char(l_bom_lists_count));
463:
464: EXCEPTION
465: WHEN others THEN
458: x_error_message := NULL;
459: COMMIT;
460: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
461: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete st in match_attribute='||
462: to_char(l_bom_lists_count));
463:
464: EXCEPTION
465: WHEN others THEN
466: ROLLBACK TO begin_match;
474: -- DESCRIPTION
475: -- Checks attributes of component item with those of revised item to see
476: -- if they are compatible.
477: -- REQUIRES
478: -- List id - Sequence id of list in BOM_LISTS.
479: -- ECO - Engineering Change Order number of Mass Update.
480: -- Organization - Organization id of revised item.
481: -- MODIFIES
482: -- Error Message - PL/SQL error.
487: -- EXAMPLE
488: --
489:
490: PROCEDURE Check_Combination(
491: p_list_id IN bom_lists.sequence_id%TYPE,
492: p_eco IN eng_revised_items_interface.change_notice%TYPE,
493: p_organization IN eng_revised_items_interface.organization_id%TYPE,
494: x_error_message IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
495: IS
526: -- Components Flag is set to No.
527: --
528: -- - Component Item's ATP Flag or ATP Components Flag is set to Yes.
529: --
530: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
531: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete stmt Check_Combination ='||
532: to_char(l_bom_lists_count));
533: DELETE FROM bom_lists l
534: WHERE l.sequence_id = p_list_id
527: --
528: -- - Component Item's ATP Flag or ATP Components Flag is set to Yes.
529: --
530: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
531: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete stmt Check_Combination ='||
532: to_char(l_bom_lists_count));
533: DELETE FROM bom_lists l
534: WHERE l.sequence_id = p_list_id
535: AND EXISTS (
528: -- - Component Item's ATP Flag or ATP Components Flag is set to Yes.
529: --
530: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
531: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete stmt Check_Combination ='||
532: to_char(l_bom_lists_count));
533: DELETE FROM bom_lists l
534: WHERE l.sequence_id = p_list_id
535: AND EXISTS (
536: SELECT NULL
529: --
530: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
531: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS before delete stmt Check_Combination ='||
532: to_char(l_bom_lists_count));
533: DELETE FROM bom_lists l
534: WHERE l.sequence_id = p_list_id
535: AND EXISTS (
536: SELECT NULL
537: FROM mtl_system_items_b ri,
574: AND c.revised_item_sequence_id = r.revised_item_sequence_id
575: AND r.change_notice = p_eco
576: AND r.organization_id = p_organization);
577:
578: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
579: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 1 Check_Combination ='||
580: to_char(l_bom_lists_count));
581:
582: --
575: AND r.change_notice = p_eco
576: AND r.organization_id = p_organization);
577:
578: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
579: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 1 Check_Combination ='||
580: to_char(l_bom_lists_count));
581:
582: --
583: -- Do not create revised items if:
576: AND r.organization_id = p_organization);
577:
578: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
579: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 1 Check_Combination ='||
580: to_char(l_bom_lists_count));
581:
582: --
583: -- Do not create revised items if:
584: --
589: -- Component is mandatory and is not "Consume or Derive"
590: -- - Shippable = Yes and Revised Items is not pick-to-order
591: --
592:
593: DELETE FROM bom_lists l
594: WHERE l.sequence_id = p_list_id
595: AND EXISTS (
596: SELECT NULL
597: FROM mtl_system_items_b i,
616: AND c.acd_type = action_add
617: AND c.revised_item_sequence_id = r.revised_item_sequence_id
618: AND r.change_notice = p_eco
619: AND r.organization_id = p_organization);
620: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
621: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 2 in Check_Combination='||
622: to_char(l_bom_lists_count));
623:
624: DELETE FROM bom_lists bl
617: AND c.revised_item_sequence_id = r.revised_item_sequence_id
618: AND r.change_notice = p_eco
619: AND r.organization_id = p_organization);
620: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
621: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 2 in Check_Combination='||
622: to_char(l_bom_lists_count));
623:
624: DELETE FROM bom_lists bl
625: WHERE bl.sequence_id = p_list_id
618: AND r.change_notice = p_eco
619: AND r.organization_id = p_organization);
620: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
621: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 2 in Check_Combination='||
622: to_char(l_bom_lists_count));
623:
624: DELETE FROM bom_lists bl
625: WHERE bl.sequence_id = p_list_id
626: AND EXISTS (
620: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
621: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 2 in Check_Combination='||
622: to_char(l_bom_lists_count));
623:
624: DELETE FROM bom_lists bl
625: WHERE bl.sequence_id = p_list_id
626: AND EXISTS (
627: SELECT NULL
628: FROM mtl_system_items_b ri_itm, -- revised item
728: AND ri_itm.effectivity_control = unit_control)
729: OR
730: ri_itm.effectivity_control = date_control)
731: );
732: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
733: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt3 in Check_Combination ='||
734: to_char(l_bom_lists_count));
735: --
736: -- Do not create revised items if:
729: OR
730: ri_itm.effectivity_control = date_control)
731: );
732: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
733: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt3 in Check_Combination ='||
734: to_char(l_bom_lists_count));
735: --
736: -- Do not create revised items if:
737: --
730: ri_itm.effectivity_control = date_control)
731: );
732: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
733: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt3 in Check_Combination ='||
734: to_char(l_bom_lists_count));
735: --
736: -- Do not create revised items if:
737: --
738: -- - Operation Sequence Number does not exist in routing (except 1).
737: --
738: -- - Operation Sequence Number does not exist in routing (except 1).
739: --
740:
741: DELETE FROM bom_lists l
742: WHERE l.sequence_id = p_list_id
743: AND EXISTS (
744: SELECT NULL
745: FROM bom_inventory_comps_interface ci,
779: --
780: -- If use up is specified, only bills for the use up item or bills
781: -- whose components include the use up item are allowed.
782: --
783: DELETE FROM bom_lists l
784: WHERE l.sequence_id = p_list_id
785: AND EXISTS (
786: SELECT NULL
787: FROM eng_revised_items_interface ri,
818: );
819:
820: x_error_message := NULL;
821: COMMIT;
822: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
823: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 4 in Check_Combination ='||
824: to_char(l_bom_lists_count));
825: EXCEPTION
826: WHEN others THEN
819:
820: x_error_message := NULL;
821: COMMIT;
822: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
823: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 4 in Check_Combination ='||
824: to_char(l_bom_lists_count));
825: EXCEPTION
826: WHEN others THEN
827: ROLLBACK TO begin_combo;
820: x_error_message := NULL;
821: COMMIT;
822: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
823: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt 4 in Check_Combination ='||
824: to_char(l_bom_lists_count));
825: EXCEPTION
826: WHEN others THEN
827: ROLLBACK TO begin_combo;
828: x_error_message := SUBSTRB(sqlerrm, 1, 150);
834: -- Check_Component
835: -- DESCRIPTION
836: -- Checks if listed items has valid components.
837: -- REQUIRES
838: -- List - Sequence id of list in BOM_LISTS.
839: -- Organization - Organization id of Bill to be checked.
840: -- Change Order - ECO number of Mass Change Order.
841: -- MODIFIES
842: -- Error buffer - Message if PL/SQL error encountered.
846: --
847: -- EXAMPLE
848: --
849: PROCEDURE Check_Component(
850: p_list_id IN bom_lists.sequence_id%TYPE,
851: p_change_order IN eng_revised_items_interface.change_notice%TYPE,
852: p_organization IN eng_revised_items_interface.organization_id%TYPE,
853: x_error_buffer IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
854: IS
869:
870: --
871: -- Bills can not be components of itself.
872: --
873: DELETE FROM bom_lists bl
874: WHERE bl.sequence_id = p_list_id
875: AND EXISTS (
876: SELECT NULL
877: FROM bom_inventory_comps_interface c,
883:
884: --
885: -- Duplicate adds
886: --
887: DELETE FROM bom_lists l
888: WHERE l.sequence_id = p_list_id
889: AND EXISTS (
890: SELECT NULL
891: FROM bom_inventory_components c,
921: -- Duplicate adds check,
922: -- if action is replace and old comp_item_id!=new comp_item_id
923: -- Included for mass replace of components.
924: --
925: DELETE FROM bom_lists l
926: WHERE l.sequence_id = p_list_id
927: AND EXISTS (
928: SELECT /*+ ORDERED USE_NL (B C CI CIR RI CO RI_ITM) */ NULL
929: FROM bom_structures_b b,
968:
969: --
970: -- Only manufacturing items can be added as components to manufacturing bills
971: --
972: DELETE FROM bom_lists l
973: WHERE l.sequence_id = p_list_id
974: AND EXISTS (
975: SELECT NULL
976: FROM mtl_system_items_b i,
997: -- Only manufacturing items can be added as components to manufacturing
998: -- bills, if action is replace and old comp_item_id!=new comp_item_id
999: -- Included for mass replace of components.
1000: --
1001: DELETE FROM bom_lists l
1002: WHERE l.sequence_id = p_list_id
1003: AND EXISTS (
1004: SELECT NULL
1005: FROM mtl_system_items_b i,
1030:
1031: --
1032: -- Duplicate components deletes and changes
1033: --
1034: DELETE FROM bom_lists l
1035: WHERE l.sequence_id = p_list_id
1036: AND EXISTS (
1037: SELECT NULL
1038: FROM bom_components_b c,
1070: --
1071: -- Other organizations who use our bills as common bills must have the
1072: -- component items in their organization as well.
1073: --
1074: DELETE FROM bom_lists l
1075: WHERE l.sequence_id = p_list_id
1076: AND EXISTS (
1077: SELECT NULL
1078: FROM bom_bill_of_materials cb,
1099: AND ri.organization_id = p_organization);
1100:
1101: COMMIT;
1102: x_error_buffer := NULL;
1103: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1104: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Check_Component ='||
1105: to_char(l_bom_lists_count));
1106: EXCEPTION
1107: WHEN l_subroutine_error THEN
1100:
1101: COMMIT;
1102: x_error_buffer := NULL;
1103: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1104: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Check_Component ='||
1105: to_char(l_bom_lists_count));
1106: EXCEPTION
1107: WHEN l_subroutine_error THEN
1108: x_error_buffer := l_internal_error;
1101: COMMIT;
1102: x_error_buffer := NULL;
1103: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1104: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Check_Component ='||
1105: to_char(l_bom_lists_count));
1106: EXCEPTION
1107: WHEN l_subroutine_error THEN
1108: x_error_buffer := l_internal_error;
1109: WHEN others THEN
1115: --
1116: -- NAME
1117: -- Restrict_List
1118: -- DESCRIPTION
1119: -- Deletes from BOM_LISTS according to access permissions granted by the
1120: -- profile options: planning item access, standard item access
1121: -- and model item access. Also culls out bills that would not
1122: -- qualify for a change order because it does not have
1123: -- components which match all the criteria specified in
1125: -- REQUIRES
1126: -- Model Item Access - Yes (1) or No (2).
1127: -- Planning Item Access - Yes (1) or No (2).
1128: -- Standard Item Access - Yes (1) or No (2).
1129: -- List id - A sequence id used to identify the list in BOM_LISTS. This
1130: -- may either be a session id or a number obtained from the
1131: -- database sequence, BOM_LISTS_S.
1132: -- Organization Id - Organization stored in ENG_CHANGES_INTERFACE.
1133: -- MODIFIES
1127: -- Planning Item Access - Yes (1) or No (2).
1128: -- Standard Item Access - Yes (1) or No (2).
1129: -- List id - A sequence id used to identify the list in BOM_LISTS. This
1130: -- may either be a session id or a number obtained from the
1131: -- database sequence, BOM_LISTS_S.
1132: -- Organization Id - Organization stored in ENG_CHANGES_INTERFACE.
1133: -- MODIFIES
1134: -- Error message. If no error, returns NULL.
1135: -- RETURNS
1157:
1158: --
1159: -- Eliminate common bills
1160: --
1161: DELETE FROM bom_lists l
1162: WHERE l.sequence_id = p_list_id
1163: AND EXISTS (
1164: SELECT NULL
1165: FROM bom_bill_of_materials b
1171:
1172: --
1173: -- Eliminate Packaging BOMs
1174: --
1175: DELETE FROM bom_lists l
1176: WHERE l.sequence_id = p_list_id
1177: AND EXISTS (
1178: SELECT NULL
1179: FROM bom_bill_of_materials b
1189:
1190: --
1191: -- Eliminate Product Families
1192: --
1193: DELETE FROM bom_lists l
1194: WHERE l.sequence_id = p_list_id
1195: AND EXISTS (
1196: SELECT NULL
1197: FROM mtl_system_items_b msi
1202:
1203: --
1204: -- Check profile values for item access
1205: --
1206: DELETE FROM bom_lists l
1207: WHERE l.sequence_id = p_list_id
1208: AND EXISTS (
1209: SELECT NULL
1210: FROM mtl_system_items_b i
1226:
1227: --
1228: -- Check item type
1229: --
1230: DELETE FROM bom_lists l
1231: WHERE l.sequence_id = p_list_id
1232: AND EXISTS (
1233: SELECT NULL
1234: FROM eng_revised_items_interface r,
1241:
1242: --
1243: -- Check if configuration bills are specified
1244: --
1245: DELETE FROM bom_lists l
1246: WHERE l.sequence_id = p_list_id
1247: AND EXISTS (
1248: SELECT NULL
1249: FROM eng_revised_items_interface r,
1253: AND i.inventory_item_id = l.assembly_item_id
1254: AND r.change_notice = p_change_order
1255: AND r.organization_id = p_organization);
1256:
1257: -- delete from bom_lists those records that have Invalid or Obsolete item status codes
1258: DELETE FROM bom_lists l
1259: WHERE l.sequence_id = p_list_id
1260: AND EXISTS (SELECT NULL
1261: FROM eng_revised_items_interface r,
1254: AND r.change_notice = p_change_order
1255: AND r.organization_id = p_organization);
1256:
1257: -- delete from bom_lists those records that have Invalid or Obsolete item status codes
1258: DELETE FROM bom_lists l
1259: WHERE l.sequence_id = p_list_id
1260: AND EXISTS (SELECT NULL
1261: FROM eng_revised_items_interface r,
1262: mtl_system_items_b i
1271: --
1272: -- Check unit effectivity. This code may have to be removed/changed in
1273: -- the future once we start supporting unit effectivity for mass change.
1274: --
1275: DELETE FROM bom_lists l
1276: WHERE l.sequence_id = p_list_id
1277: AND EXISTS (
1278: SELECT NULL
1279: FROM mtl_system_items_b i
1287: IF l_subroutine_error_msg IS NOT NULL THEN
1288: RAISE l_internal_error;
1289: END IF;
1290: x_error_message := NULL;
1291: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1292: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Restrict_List='||
1293: to_char(l_bom_lists_count));
1294: EXCEPTION
1295: WHEN l_internal_error THEN
1288: RAISE l_internal_error;
1289: END IF;
1290: x_error_message := NULL;
1291: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1292: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Restrict_List='||
1293: to_char(l_bom_lists_count));
1294: EXCEPTION
1295: WHEN l_internal_error THEN
1296: x_error_message := l_subroutine_error_msg;
1289: END IF;
1290: x_error_message := NULL;
1291: select count(*) into l_bom_lists_count from bom_lists where sequence_id = p_list_id;
1292: FND_FILE.PUT_LINE(FND_FILE.LOG,' Records in BOM_LISTS after delete stmt in Restrict_List='||
1293: to_char(l_bom_lists_count));
1294: EXCEPTION
1295: WHEN l_internal_error THEN
1296: x_error_message := l_subroutine_error_msg;
1297: WHEN others THEN
1420: -- Also added condition l.organization_id = p_org_id
1421: -- This is done to avoid FULL Table scan on Bom_bill_of_materials Table
1422: -- and thus reduce the Cost
1423: --
1424: CURSOR c_get_bom_list(p_list_id IN bom_lists.sequence_id%TYPE,
1425: p_org_id IN bom_lists.organization_id%TYPE) IS
1426: SELECT l.assembly_item_id,
1427: l.alternate_designator,
1428: b.bill_sequence_id,
1421: -- This is done to avoid FULL Table scan on Bom_bill_of_materials Table
1422: -- and thus reduce the Cost
1423: --
1424: CURSOR c_get_bom_list(p_list_id IN bom_lists.sequence_id%TYPE,
1425: p_org_id IN bom_lists.organization_id%TYPE) IS
1426: SELECT l.assembly_item_id,
1427: l.alternate_designator,
1428: b.bill_sequence_id,
1429: itm.effectivity_control,
1436: ri.revised_item_sequence_id,
1437: ri.increment_rev,
1438: ri.use_up_plan_name,
1439: ri.from_end_item_unit_number
1440: FROM bom_lists l,
1441: eng_revised_items_interface ri,
1442: bom_bill_of_materials b,
1443: mtl_system_items_b itm
1444: WHERE
2217: --The error message is set to null on successful completion
2218: error_message := 'A fatal error occurred while processing mass_update.';
2219:
2220: -- Bug 1807268
2221: -- Added update statement here to update the org_id in bom_lists Table
2222: --
2223: UPDATE bom_lists
2224: SET organization_id = org_id
2225: WHERE sequence_id = list_id;
2219:
2220: -- Bug 1807268
2221: -- Added update statement here to update the org_id in bom_lists Table
2222: --
2223: UPDATE bom_lists
2224: SET organization_id = org_id
2225: WHERE sequence_id = list_id;
2226:
2227: Restrict_List(list_id,