Step 1: Create staging table for header
create table dmf_bom_ph_bom_com (
| C_PROJECTED_YIELD varchar2(100), |
| C_PLANNING_PERCENT varchar2(100), |
| C_MINIMUM_ALLOWED_QUANTITY varchar2(100), |
| C_MAXIMUM_ALLOWED_QUANTITY varchar2(100), |
| C_TRANSACTION_TYPE varchar2(100), |
| C_START_EFFECTIVE_DATE varchar2(100), |
| C_ALTERNATE_BOM_CODE varchar2(100), |
| C_ORGANIZATION_CODE varchar2(100), |
| C_ASSEMBLY_ITEM_NAME varchar2(100), |
| C_QUANTITY_PER_ASSEMBLY varchar2(100), |
| C_ITEM_SEQUENCE_NUMBER varchar2(100), |
| C_OPERATION_SEQUENCE_NUMBER varchar2(100), |
| C_COMPONENT_ITEM_NAME varchar2(100), |
| C_ATTRIBUTE_CATEGORY varchar2(100), |
| SUPPLY_TYPE varchar2(100), |
| OPTIONAL varchar2(100), |
| INCLUDE_ON_SHIP_DOCS varchar2(100), |
| COMPONENT_QUANTITY varchar2(100), |
| COMPONENT_REMARKS varchar2(100), |
RETURN_STATUS varchar2(100))
Step 2:
create table for header dmf_bom_ph_bom_header:
create table for the following fields
return_status,
assembly_type,
transaction_type,
assembly_item_name,
organization_code,
alternate_bom_code,
attribute_category,
operating_unit_name
|
/* Formatted on 10/22/2012 2:33:19 PM (QP5 v5.115.810.9015) */
--CREATE OR REPLACE PROCEDURE bolinf.dmf_bom_ph_billofmaterial (
-- errbuf OUT VARCHAR2,
-- retcode OUT NUMBER
--)
--IS
DECLARE
l_cnt NUMBER := 0;
d_actionvalidate_msg VARCHAR2 (1000);
d_actionvalidate_flag VARCHAR2 (1000);
d_actionvalidate_value DATE;
l_error_details VARCHAR2 (4000);
b1 NUMBER := 1;
bom_hdr_lno NUMBER;
bom_hdr_exid NUMBER;
l_condt_flag VARCHAR2 (10);
assembly_item_name VARCHAR2 (4000);
organization_code VARCHAR2 (4000);
alternate_bom_code VARCHAR2 (4000);
assembly_type VARCHAR2 (4000);
transaction_type VARCHAR2 (4000);
attribute_category VARCHAR2 (4000);
attribute1 VARCHAR2 (4000);
attribute2 VARCHAR2 (4000);
attribute3 VARCHAR2 (4000);
attribute4 VARCHAR2 (4000);
attribute5 VARCHAR2 (4000);
attribute6 VARCHAR2 (4000);
attribute7 VARCHAR2 (4000);
attribute8 VARCHAR2 (4000);
attribute9 VARCHAR2 (4000);
attribute10 VARCHAR2 (4000);
attribute11 VARCHAR2 (4000);
attribute12 VARCHAR2 (4000);
attribute13 VARCHAR2 (4000);
attribute14 VARCHAR2 (4000);
attribute15 VARCHAR2 (4000);
row_identifier NUMBER;
-- bom_implementation_date DATE;
implementation_date DATE;
b2 NUMBER := 1;
bom_comp_lno NUMBER;
bom_comp_exid NUMBER;
c_organization_code VARCHAR2 (4000);
c_assembly_item_name VARCHAR2 (4000);
-- c_start_effective_date VARCHAR2 (100);
c_operation_sequence_number NUMBER;
c_component_item_name VARCHAR2 (4000);
c_alternate_bom_code VARCHAR2 (4000);
c_item_sequence_number NUMBER;
c_quantity_per_assembly NUMBER;
c_planning_percent NUMBER;
c_projected_yield NUMBER;
supply_type VARCHAR2 (4000);
optional VARCHAR2 (4000);
include_on_ship_docs VARCHAR2 (4000);
c_minimum_allowed_quantity NUMBER;
c_maximum_allowed_quantity NUMBER;
component_remarks VARCHAR2 (4000);
c_attribute_category VARCHAR2 (4000);
c_attribute3 VARCHAR2 (4000);
c_attribute4 VARCHAR2 (4000);
c_attribute5 VARCHAR2 (4000);
c_attribute6 VARCHAR2 (4000);
c_attribute7 VARCHAR2 (4000);
c_attribute8 VARCHAR2 (4000);
c_attribute9 VARCHAR2 (4000);
c_attribute10 VARCHAR2 (4000);
c_attribute11 VARCHAR2 (4000);
c_attribute12 VARCHAR2 (4000);
c_attribute13 VARCHAR2 (4000);
c_attribute14 VARCHAR2 (4000);
c_attribute15 VARCHAR2 (4000);
c_transaction_type VARCHAR2 (4000);
x_return_status VARCHAR2 (4000);
x_msg_count NUMBER;
p_status_code VARCHAR2 (1000);
status_code VARCHAR2 (1000);
operating_unit_name VARCHAR2 (1000);
component_item_name VARCHAR2 (1000);
l_assembly_item_name VARCHAR2 (4000);
l_user_id NUMBER;
component_quantity NUMBER;
l_component_item_name VARCHAR2 (1000);
l_alternate_bom_designator VARCHAR2 (100);
-- l_user_id NUMBER := 1150;
CURSOR bom_hdr
IS
SELECT DISTINCT return_status, --row_identifier,
assembly_type,
transaction_type,
--bom_implementation_date,
assembly_item_name,
organization_code,
alternate_bom_code,
attribute_category,
operating_unit_name
FROM dmf_bom_ph_bom_header
WHERE return_status IS NULL
GROUP BY return_status,
-- row_identifier,
assembly_type,
transaction_type,
-- bom_implementation_date,
assembly_item_name,
organization_code,
alternate_bom_code,
attribute_category,
operating_unit_name;
CURSOR bom_comp (
p_assembly_item_name VARCHAR2,
p_organization_code VARCHAR2
)
IS
SELECT DISTINCT c_projected_yield,
c_planning_percent,
c_minimum_allowed_quantity,
c_maximum_allowed_quantity,
c_transaction_type,
-- c_start_effective_date,
c_alternate_bom_code,
c_organization_code,
c_assembly_item_name,
c_quantity_per_assembly,
c_item_sequence_number,
c_operation_sequence_number,
c_component_item_name,
c_attribute_category,
optional,
include_on_ship_docs,
component_remarks,
supply_type,
component_quantity,
return_status
FROM dmf_bom_ph_bom_com
WHERE return_status IS NULL
AND c_assembly_item_name = p_assembly_item_name
AND c_organization_code = p_organization_code
GROUP BY c_projected_yield,
c_planning_percent,
c_minimum_allowed_quantity,
c_maximum_allowed_quantity,
c_transaction_type,
-- c_start_effective_date,
c_alternate_bom_code,
c_organization_code,
c_assembly_item_name,
c_quantity_per_assembly,
c_item_sequence_number,
c_operation_sequence_number,
c_component_item_name,
c_attribute_category,
optional,
include_on_ship_docs,
component_remarks,
supply_type,
component_quantity,
return_status;
BEGIN
-- fnd_profile.get ('USER_ID', l_user_id);
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'HLIFE';
END;
FOR cur_bom_hdr IN bom_hdr
LOOP
BEGIN
SELECT organization_id
INTO operating_unit_name
FROM org_organization_definitions
WHERE organization_code = cur_bom_hdr.organization_code;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Invalide Organization ID ' || cur_bom_hdr.organization_code
);
fnd_file.put_line (
fnd_file.LOG,
'Invalide Organization ID ' || cur_bom_hdr.organization_code
);
END;
BEGIN
SELECT inventory_item_id
INTO l_assembly_item_name
FROM mtl_system_items
WHERE segment1 = cur_bom_hdr.assembly_item_name
AND organization_id = operating_unit_name;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Assembly Invalid Item ' || cur_bom_hdr.assembly_item_name
);
fnd_file.put_line (
fnd_file.LOG,
'Assembly Invalid Item ' || cur_bom_hdr.assembly_item_name
);
END;
-- implementation_date := cur_bom_hdr.bom_implementation_date;
transaction_type := cur_bom_hdr.transaction_type;
IF (cur_bom_hdr.alternate_bom_code IS NOT NULL)
THEN
BEGIN
SELECT alternate_designator_code
INTO l_alternate_bom_designator
FROM bom_alternate_designators
WHERE alternate_designator_code =
cur_bom_hdr.alternate_bom_code;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Alt BOM Code Err: ' || SQLERRM);
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Alt BOM Code Err: ' || SQLERRM);
END;
END IF;
-- l_ALTERNATE_BOM_DESIGNATOR := cur_bom_hdr.alternate_bom_code;
IF (transaction_type IS NULL)
THEN
transaction_type := 'CREATE';
END IF;
INSERT INTO bom_bill_of_mtls_interface (assembly_item_id,
organization_id,
bill_sequence_id,
item_number,
last_update_date,
last_updated_by,
creation_date,
created_by,
transaction_type,
process_flag,
alternate_bom_designator)
VALUES (l_assembly_item_name,
operating_unit_name,
NULL,
cur_bom_hdr.assembly_item_name,
-- implementation_date,
sysdate,
l_user_id,
implementation_date,
l_user_id,
transaction_type,
1,
l_alternate_bom_designator);
BEGIN
FOR cur_bom_comp
IN bom_comp (cur_bom_hdr.assembly_item_name,
cur_bom_hdr.organization_code)
LOOP
BEGIN
SELECT inventory_item_id
INTO l_component_item_name
FROM mtl_system_items
WHERE segment1 = cur_bom_comp.c_component_item_name
AND organization_id = operating_unit_name;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Component Invalid Item '
|| cur_bom_comp.c_component_item_name);
fnd_file.put_line (
fnd_file.LOG,
'Component Invalid Item '
|| cur_bom_comp.c_component_item_name
);
END;
c_item_sequence_number := cur_bom_comp.c_item_sequence_number;
c_operation_sequence_number :=
cur_bom_comp.c_operation_sequence_number;
c_transaction_type := cur_bom_hdr.transaction_type;
IF (c_transaction_type IS NULL)
THEN
c_transaction_type := 'CREATE';
END IF;
component_quantity := cur_bom_comp.component_quantity;
DBMS_OUTPUT.put_line (
'COMPONENT_QUANTITY ' || component_quantity
);
c_planning_percent := cur_bom_comp.c_planning_percent;
IF (c_planning_percent IS NULL)
THEN
c_planning_percent := '100';
END IF;
component_remarks := cur_bom_comp.component_remarks;
-- c_start_effective_date := cur_bom_comp.c_start_effective_date;
INSERT INTO bom_inventory_comps_interface (
component_item_id,
assembly_item_id,
organization_id,
item_num,
operation_seq_num,
-- effectivity_date,
transaction_type,
process_flag,
bill_sequence_id,
component_sequence_id,
component_quantity,
component_yield_factor,
implementation_date,
wip_supply_type,
so_basis,
check_atp,
planning_factor,
last_update_date,
last_updated_by,
creation_date,
created_by,
component_remarks,
ALTERNATE_BOM_DESIGNATOR
)
VALUES (l_component_item_name,
l_assembly_item_name,
operating_unit_name,
c_item_sequence_number,
c_operation_sequence_number,
-- c_start_effective_date,
c_transaction_type,
1,
NULL,
NULL,
component_quantity,
NULL,
implementation_date,
NULL,
NULL,
NULL,
c_planning_percent,
sysdate,--implementation_date,
l_user_id,
implementation_date,
l_user_id,
component_remarks,
l_alternate_bom_designator);
END LOOP;
END;
END LOOP;
UPDATE dmf_bom_ph_bom_header
SET return_status = 'S';
UPDATE dmf_bom_ph_bom_com
SET return_status = 'S';
COMMIT;
END;
Step final :
Once data inserted into the interface table then pls run the import concurrent in Bills of material responsibility
"Bill and Routing Interface"
/
