Manufacturing Interface BOM Full Process With Revision
Bom Custom Table
CREATE TABLE APPS.XXX_BOM_BILL_MTLS_STG
(
ITEM_NAME VARCHAR2(50 BYTE),
REVISION_NO CHAR(5 BYTE),
ITEM_SEQ_BOM_COMP NUMBER(5),
OPERATION_SEQUENCE NUMBER(5),
BOM_COMPONENT VARCHAR2(50 BYTE),
QUANTITY NUMBER(10,2),
YIELD_FACTOR NUMBER(10,2),
SUPPLY_TYPE VARCHAR2(25 BYTE),
SUB_INVENTORY VARCHAR2(50 BYTE),
LOCATORS VARCHAR2(50 BYTE),
PROJECTS VARCHAR2(50 BYTE),
TASKS VARCHAR2(50 BYTE),
ATTRIBUTE1 VARCHAR2(50 BYTE),
ATTRIBUTE2 VARCHAR2(50 BYTE),
ATTRIBUTE3 VARCHAR2(50 BYTE),
ATTRIBUTE4 VARCHAR2(50 BYTE),
ATTRIBUTE5 VARCHAR2(50 BYTE),
H_VERIFY_FLAG CHAR(1 BYTE),
L_VERIFY_FLAG CHAR(1 BYTE),
ERROR_MESSAGE VARCHAR2(3000 BYTE)
)
Import BOM Details Through Procedure and Validate with Open Interface
CREATE OR REPLACE PROCEDURE APPS.xxx_bom_bill_mtls_api
--(errbuf varchar2,retcode varchar2)
AS
l_verify_flag CHAR (1);
l_error_message VARCHAR2 (2500);
l_organization_id NUMBER (15);
l_inventory_item_id NUMBER (15);
l_component_item_id NUMBER (15);
l_bom_exists NUMBER (15);
l_wip_supply_type NUMBER (3);
V_SUBINVENTORY_CODE VARCHAR2 (50);
L_INVENTORY_LOCATION_ID NUMBER (10);
CURSOR c_header
IS
SELECT DISTINCT item_name, revision_no, attribute1, attribute2,
attribute3, attribute4, attribute5
FROM xxx_bom_bill_mtls_stg
WHERE NVL (h_verify_flag, 'N') = 'N';
CURSOR c_lines (p_item_name VARCHAR2)
IS
SELECT *
FROM xxx_bom_bill_mtls_stg
WHERE item_name = p_item_name
ORDER BY item_name, item_seq_bom_comp;
BEGIN
FOR c_bom IN c_header
LOOP
l_verify_flag := 'Y';
l_error_message := NULL;
l_bom_exists := NULL;
BEGIN
SELECT organization_id
INTO l_organization_id
FROM org_organization_definitions
WHERE organization_name = 'MBS - Hamriyah
Free Zone';
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := l_error_message || 'Organization
is not valid';
END;
BEGIN
SELECT inventory_item_id
INTO l_inventory_item_id
FROM mtl_system_items_b
WHERE organization_id = l_organization_id
AND segment1 = TRIM (UPPER (c_bom.item_name));
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := l_error_message || 'Item
is not valid';
END;
BEGIN
SELECT assembly_item_id
INTO l_bom_exists
FROM bom_bill_of_materials_v
WHERE organization_id = l_organization_id
AND assembly_item_id = l_inventory_item_id;
IF l_bom_exists > 0
THEN
l_verify_flag := 'N';
l_error_message := l_error_message || 'Item
already existing';
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
IF l_verify_flag <> 'N'
THEN
SAVEPOINT a;
BEGIN
INSERT INTO
bom_bill_of_mtls_interface
(assembly_item_id,
organization_id,
transaction_type, revision,
assembly_type, process_flag,
attribute1, attribute2,
attribute3, attribute4, attribute5
)
VALUES (l_inventory_item_id, l_organization_id,
'CREATE', TRIM (NVL (c_bom.revision_no, 0)),
1 ---1) Manufacturing,
2) Engineering
, 1,
c_bom.attribute1, c_bom.attribute2,
c_bom.attribute3, c_bom.attribute4, c_bom.attribute5
);
UPDATE xxx_bom_bill_mtls_stg
SET h_verify_flag = 'Y'
WHERE item_name = c_bom.item_name;
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message := SQLERRM;
UPDATE xxx_bom_bill_mtls_stg
SET h_verify_flag = 'N',
error_message = l_error_message
WHERE item_name = c_bom.item_name;
--goto next_bom;
END;
FOR c_comp IN c_lines (c_bom.item_name)
LOOP
BEGIN
SELECT inventory_item_id
INTO l_component_item_id
FROM mtl_system_items_b
WHERE organization_id = l_organization_id
AND UPPER (segment1) = UPPER (TRIM (c_comp.bom_component));
EXCEPTION
WHEN OTHERS
THEN
l_verify_flag := 'N';
l_error_message :=
l_error_message
|| 'Bom Component is not
valid';
END;
If Trim(c_comp.SUB_INVENTORY) Is Not Null And Trim(c_comp.LOCATORS) Is Not Null Then
BEGIN
select SUBINVENTORY_CODE
into V_SUBINVENTORY_CODE
from mtl_Item_Locations
where organization_id = l_organization_id
And SUBINVENTORY_CODE =Trim(c_comp.SUB_INVENTORY) And SEGMENT1=Trim(c_comp.LOCATORS) And Project_id Is Null;
Dbms_Output.Put_Line('Subinventory Code Duplicate ='||c_comp.SUB_INVENTORY);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message ||'Bom
Subinventory is not valid';
END;
Else
V_SUBINVENTORY_CODE:=Trim(c_comp.SUB_INVENTORY);
End if;
If Trim(c_comp.SUB_INVENTORY) Is Not Null And Trim(c_comp.LOCATORS) Is Not Null Then
BEGIN
select INVENTORY_LOCATION_ID
into L_INVENTORY_LOCATION_ID
from mtl_Item_Locations
where organization_id = l_organization_id
And SUBINVENTORY_CODE =Trim(c_comp.SUB_INVENTORY) And SEGMENT1=Trim(c_comp.LOCATORS) And Project_id Is Null;
Dbms_Output.Put_Line('Locators Code Duplicate ='||c_comp.LOCATORS);
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := l_error_message ||'Bom Locator
is not valid';
END;
Else
L_INVENTORY_LOCATION_ID :=Null;
End If;
BEGIN
IF c_comp.supply_type = 'Phantom'
THEN
l_wip_supply_type := 6;
ELSIF c_comp.supply_type = 'Operation Pull'
THEN
l_wip_supply_type := 3;
ELSE
l_wip_supply_type := 1;
END IF;
END;
IF l_verify_flag <> 'N'
THEN
BEGIN
INSERT INTO
bom_inventory_comps_interface
(assembly_item_id, process_flag,
transaction_type, component_item_id,
component_sequence_id,
item_num,
operation_seq_num,
organization_id, effectivity_date,
component_quantity,
component_yield_factor,
wip_supply_type,
supply_subinventory, supply_locator_id
)
VALUES (l_inventory_item_id, 1,
'CREATE', l_component_item_id,
bom_inventory_components_s.NEXTVAL,
TRIM (c_comp.item_seq_bom_comp),
TRIM (c_comp.operation_sequence),
l_organization_id, SYSDATE,
TRIM (c_comp.quantity),
TRIM (c_comp.yield_factor),
l_wip_supply_type,
V_SUBINVENTORY_CODE,L_INVENTORY_LOCATION_ID
);
UPDATE xxx_bom_bill_mtls_stg
SET l_verify_flag = 'Y'
WHERE item_name = c_comp.item_name
AND bom_component = c_comp.bom_component;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := SQLERRM;
ROLLBACK TO SAVEPOINT a;
UPDATE xxx_bom_bill_mtls_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE item_name = c_comp.item_name
AND bom_component = c_comp.bom_component;
UPDATE xxx_bom_bill_mtls_stg
SET h_verify_flag = 'N'
WHERE item_name = c_comp.item_name;
--goto next_bom ;
END;
ELSE
ROLLBACK TO SAVEPOINT a;
UPDATE xxx_bom_bill_mtls_stg
SET l_verify_flag = 'N',
error_message = l_error_message
WHERE item_name = c_comp.item_name
AND bom_component = c_comp.bom_component;
UPDATE xxx_bom_bill_mtls_stg
SET h_verify_flag = 'N'
WHERE item_name = c_comp.item_name;
--goto next_bom;
END IF;
END LOOP;
ELSE
UPDATE xxx_bom_bill_mtls_stg
SET h_verify_flag = 'N',
error_message = l_error_message
WHERE item_name = c_bom.item_name;
END IF;
COMMIT;
END LOOP;
END xxx_bom_bill_mtls_api;
/
No comments:
Post a Comment