/*=======================================================================
| This Script is developed for DELETING All of Components,All of Bills |
| and All of Items one time; The Delete Group name is 'DT'+sequence_id; |
| the First ,delete components; then delete bills,last delete items; |
| Before deleting items,Please confirm item which not exists STANDARD |
| COST ; |
| If there is something wrong with the deleting,Please query table |
| bom_delete_errors from error message; |
|========================================================================*/
DECLARE
l_master_org_id NUMBER :=85;--Get from table mtl_parameters;
l_delete_group_name VARCHAR2(10);
l_organization_id NUMBER;
l_delete_type NUMBER; -- 1=Item 2=Bill 3=Routing 4=Component 5=Operation 6=Bill,Routing 7=Item,Bill,Routing
l_action_type NUMBER; -- BOM_DELETE_ACTION_TYPE 1=Check 2=Delete
l_delete_org_type NUMBER; -- 1=Current Organization 2=Organization Hierarchy 3=All Organizations
l_delete_group_sequence_id NUMBER;
l_engineering_flag NUMBER;
l_delete_status_type NUMBER; --BOM_DELETE_STATUS_TYPE: 1=Pending 2=Check ok 3=Error 4=Deleted
l_delete_entity_type NUMBER; --BOM_DELETE_ENTITY_TYPE: 1=Item 2=Bill of material 3=Routing 4=Component 5=Operation
l_user_id NUMBER;
BEGIN
-- Get User Id
BEGIN
SELECT t.user_id
INTO l_user_id
FROM fnd_user t
WHERE t.user_name ='APPLSYSPUB'
;
EXCEPTION
WHEN no_data_found THEN
l_user_id := -1;
END;
/* Delete Components First */
--Create Delete Group
SELECT bom_delete_groups_s.nextval
INTO l_delete_group_sequence_id
FROM dual;
l_delete_group_name := 'DT'||to_char(l_delete_group_sequence_id);
l_organization_id := 85;
l_delete_type := 4; --1=Item 2=Bill 3=Routing 4=Component 5=Operation 6=Bill,Routing 7=Item,Bill,Routing
l_action_type := 1;
l_engineering_flag := 1;
l_delete_org_type := 3; --All Organizations
BEGIN
INSERT INTO bom_delete_groups(
delete_group_sequence_id
, delete_group_name
, organization_id
, delete_type
, action_type
, engineering_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, delete_common_bill_flag
, delete_org_type
)
VALUES(
l_delete_group_sequence_id
, l_delete_group_name
, l_organization_id
, l_delete_type
, l_action_type
, l_engineering_flag
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_user_id
, 2
, l_delete_org_type
);
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
--Insert Into Assembly Item Infor
l_delete_entity_type := 4; --4=Component
l_delete_status_type := 1; --1=Pending
BEGIN
INSERT INTO bom_delete_entities(
delete_entity_sequence_id
, delete_group_sequence_id
, delete_entity_type
, bill_sequence_id
, inventory_item_id
, organization_id
, alternate_designator
, item_description
, item_concat_segments
, delete_status_type
, prior_process_flag
, prior_commit_flag
, last_update_date
, last_updated_by
, creation_date
, last_update_login
, created_by
)
SELECT
bom_delete_entities_s.nextval
, l_delete_group_sequence_id
, l_delete_entity_type
, bom.bill_sequence_id
, msib.inventory_item_id
, msib.organization_id
, bom.alternate_bom_designator
, msib.DESCRIPTION
, msib.segment1
, to_number(NULL)--l_delete_status_type --
, 1
, 1
, msib.last_update_date
, msib.last_updated_by
, msib.creation_date
, msib.last_update_login
, msib.created_by
FROM bom_bill_of_materials bom
, mtl_system_items_b msib
WHERE bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
-- AND bom.organization_id = l_organization_id
;
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
BEGIN
INSERT INTO bom_delete_sub_entities(
delete_entity_sequence_id
, component_sequence_id
, operation_seq_num
, effectivity_date
, component_item_id
, component_concat_segments
, item_num
, DESCRIPTION
, delete_status_type
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
)
SELECT de.delete_entity_sequence_id
, bic.component_sequence_id
, bic.operation_seq_num
, bic.effectivity_date
, bic.component_item_id
, msib.segment1
, bic.item_num
, msib.DESCRIPTION
, l_delete_status_type
, msib.last_update_date
, msib.last_updated_by
, msib.creation_date
, msib.created_by
, msib.last_update_login
FROM bom_bill_of_materials bom
, bom_inventory_components bic
, bom_delete_entities de
, mtl_system_items_b msib
WHERE bom.bill_sequence_id = bic.bill_sequence_id
AND bom.bill_sequence_id = de.bill_sequence_id
AND de.delete_group_sequence_id = l_delete_group_sequence_id
AND bic.component_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
-- AND bom.organization_id = l_organization_id
;
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
/* Delete Bill */
--Create Delete Group
SELECT bom_delete_groups_s.nextval
INTO l_delete_group_sequence_id
FROM dual;
l_delete_group_name := 'DT'||to_char(l_delete_group_sequence_id);
l_organization_id := 85;
l_delete_type := 2; --1=Item 2=Bill 3=Routing 4=Component 5=Operation 6=Bill,Routing 7=Item,Bill,Routing
l_action_type := 1;
l_engineering_flag := 1;
l_delete_org_type := 3; --All Organizations
BEGIN
INSERT INTO bom_delete_groups(
delete_group_sequence_id
, delete_group_name
, organization_id
, delete_type
, action_type
, engineering_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, delete_common_bill_flag
, delete_org_type
)
VALUES(
l_delete_group_sequence_id
, l_delete_group_name
, l_organization_id
, l_delete_type
, l_action_type
, l_engineering_flag
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_user_id
, 2
, l_delete_org_type
);
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
--Insert Into Assembly Item Infor
l_delete_entity_type := 2; --4=Component
l_delete_status_type := 1; --1=Pending
BEGIN
INSERT INTO bom_delete_entities(
delete_entity_sequence_id
, delete_group_sequence_id
, delete_entity_type
, bill_sequence_id
, inventory_item_id
, organization_id
, alternate_designator
, item_description
, item_concat_segments
, delete_status_type
, prior_process_flag
, prior_commit_flag
, last_update_date
, last_updated_by
, creation_date
, last_update_login
, created_by
)
SELECT
bom_delete_entities_s.nextval
, l_delete_group_sequence_id
, l_delete_entity_type
, bom.bill_sequence_id
, msib.inventory_item_id
, msib.organization_id
, bom.alternate_bom_designator
, msib.DESCRIPTION
, msib.segment1
, l_delete_status_type
, 1
, 1
, msib.last_update_date
, msib.last_updated_by
, msib.creation_date
, msib.last_update_login
, msib.created_by
FROM bom_bill_of_materials bom
, mtl_system_items_b msib
WHERE bom.assembly_item_id = msib.inventory_item_id
AND bom.organization_id = msib.organization_id
-- AND bom.organization_id = l_organization_id
;
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
/* Delete Item */
--Create Delete Group
SELECT bom_delete_groups_s.nextval
INTO l_delete_group_sequence_id
FROM dual;
l_delete_group_name := 'DT'||to_char(l_delete_group_sequence_id);
l_organization_id := 85;
l_delete_type := 1; --1=Item 2=Bill 3=Routing 4=Component 5=Operation 6=Bill,Routing 7=Item,Bill,Routing
l_action_type := 1;
l_engineering_flag := 1;
l_delete_org_type := 3; --All Organizations
BEGIN
INSERT INTO bom_delete_groups(
delete_group_sequence_id
, delete_group_name
, organization_id
, delete_type
, action_type
, engineering_flag
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, delete_common_bill_flag
, delete_org_type
)
VALUES(
l_delete_group_sequence_id
, l_delete_group_name
, l_organization_id
, l_delete_type
, l_action_type
, l_engineering_flag
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
, l_user_id
, 2
, l_delete_org_type
);
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
--Insert Into Assembly Item Infor
l_delete_entity_type := 1; --4=Component
l_delete_status_type := 1; --1=Pending
BEGIN
INSERT INTO bom_delete_entities(
delete_entity_sequence_id
, delete_group_sequence_id
, delete_entity_type
-- , bill_sequence_id
, inventory_item_id
, organization_id
-- , alternate_designator
, item_description
, item_concat_segments
, delete_status_type
, prior_process_flag
, prior_commit_flag
, last_update_date
, last_updated_by
, creation_date
, last_update_login
, created_by
)
SELECT
bom_delete_entities_s.nextval
, l_delete_group_sequence_id
, l_delete_entity_type
-- , bom.bill_sequence_id
, msib.inventory_item_id
, msib.organization_id
-- , bom.alternate_bom_designator
, msib.DESCRIPTION
, msib.segment1
, l_delete_status_type
, 1
, 1
, msib.last_update_date
, msib.last_updated_by
, msib.creation_date
, msib.last_update_login
, msib.created_by
FROM mtl_system_items_b msib
-- WHERE msib.organization_id = l_organization_id
;
EXCEPTION
WHEN OTHERS THEN
NULL;
COMMIT;
END;
错误信息表:BOM_DELETE_SQL_STATEMENTS
本文作者:网友 来源:网络