CREATE OR REPLACE PACKAGE BODY
APPS.ftxs_118c_bom_conv_pkg
AS
/*****************************************************************
*
******************************************************************
* $Header: $
******************************************************************
*
* Filename: ftxs_118c_bom_conv_pkg.pkb
*
* Purpose : This Package contains procedures and
* to be used for Bom and Routing Conversion.
*
*****************************************************************/
--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_BOM_HEADER_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_headers
as
l_head_count number;
v_org varchar2(20);
l_err_msg varchar2(1000);
l_item number;
l_valid_head_count number;
l_org_id number;
l_err_count number;
CURSOR bom_head_cur IS SELECT fbh.* , rowid row_id
FROM FTXS_118C_BOM_HEADER_TEMP fbh
WHERE status_flag = 'N';
CURSOR bom_head_err_cur IS SELECT fbh.* , rowid row_id
FROM FTXS_118C_BOM_HEADER_TEMP fbh
WHERE status_flag = 'E';
BEGIN
l_head_count := 0;
l_valid_head_count := 0;
l_err_count :=0;
FOR bom_head_rec in bom_head_cur
LOOP
l_head_count := l_head_count + 1;
l_item := null;
l_err_msg := NULL;
v_org := 0;
l_item := 0;
BEGIN
-- fetch the organization_code
BEGIN
SELECT organization_code INTO v_org FROM
ftxs_118c_inv_org_map
WHERE source_inv_org =bom_head_rec. organization_code;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_head_rec. organization_code;
WHEN others THEN
l_err_msg := SQLCODE || ' - ' || SQLERRM;
END;
-- Validate Organization_code
BEGIN
SELECT organization_id INTO l_org_id
FROM mtl_parameters WHERE organization_code = v_org;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - '||bom_head_rec.organization_code||'Organization code does not exists ';
WHEN others THEN
l_err_msg := l_err_msg || ' - '||bom_head_rec.organization_code||SQLCODE||'-'||SQLERRM;
END;
-- Validate item Number
Begin
SELECT inventory_item_id INTO
l_item
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND segment1 = bom_head_rec.item_number
AND bom_enabled_flag = 'Y';
EXCEPTION
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - '|| bom_head_rec.item_number||'Item Number does not exists ';
WHEN others THEN
l_err_msg := l_err_msg || ' - '||bom_head_rec.item_number||SQLCODE||'-'||SQLERRM;
END;
IF l_err_msg is NULL THEN
l_valid_head_count := l_valid_head_count + 1;
UPDATE FTXS_118C_BOM_HEADER_TEMP
SET status_flag = 'V'
,err_msg = null
WHERE rowid = bom_head_rec.row_id;
ELSE
UPDATE FTXS_118C_BOM_HEADER_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_head_rec.row_id;
l_err_count := l_err_count + 1;
END IF;
EXCEPTION
WHEN others THEN
UPDATE FTXS_118C_BOM_HEADER_TEMP
SET status_flag= 'E'
,err_msg = l_err_msg
WHERE rowid = bom_head_rec.row_id;
l_err_count := l_err_count + 1;
END;
IF MOD(l_valid_head_count,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_valid_head_count);
Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 then
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR bom_head_err_rec IN bom_head_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,bom_head_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END validate_headers;
--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface table
--*********************************************************************************************
PROCEDURE Insert_headers
AS
l_userid NUMBER:=fnd_global.user_id;
l_count NUMBER;
v_date DATE := SYSDATE;
l_err_msg varchar2(1000);
l_err_count number;
CURSOR ins_headers_cur IS SELECT fbh.* ,rowid row_id
FROM FTXS_118C_BOM_HEADER_TEMP fbh
WHERE status_flag = 'V';
CURSOR ins_headers_err_cur IS SELECT fbh.* ,rowid row_id
FROM FTXS_118C_BOM_HEADER_TEMP fbh
WHERE status_flag = 'E';
BEGIN
l_count := 0;
l_err_count :=0;
FOR ins_headers_rec in ins_headers_cur
LOOP
BEGIN
l_err_msg := null;
INSERT INTO bom_bill_of_mtls_interface
(organization_code
,assembly_type
,process_flag
,item_number
,transaction_type
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES (ins_headers_rec.organization_code
,ins_headers_rec.assembly_type
,1
,ins_headers_rec.item_number
,'CREATE'
,v_date
,l_userid
,v_date
,l_userid
,l_userid
);
l_count := l_count + 1;
IF mod(l_count,100) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN others THEN
l_err_msg := SQLCODE || ' - '|| SQLERRM;
UPDATE FTXS_118C_BOM_HEADER_TEMP
SET status_flag= 'E'
,err_msg = l_err_msg
WHERE rowid = ins_headers_rec.row_id;
l_err_count := l_err_count + 1;
END;
END LOOP;
COMMIT;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 then
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR ins_headers_err_rec IN ins_headers_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,ins_headers_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END Insert_headers;
--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_BOM_COMPS_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_bom_comp
AS
l_comp_count number;
v_org varchar2(20);
l_err_msg varchar2(1000);
l_item number;
l_org_id number;
l_valid_comp_count number;
v_num_comp_inv_id number;
l_err_count number;
CURSOR bom_comp_cur IS SELECT fbc.*
,rowid row_id
FROM FTXS_118C_BOM_COMPS_TEMP fbc
WHERE status_flag='N';
CURSOR bom_comp_err_cur IS SELECT fbc.*
,rowid row_id
FROM FTXS_118C_BOM_COMPS_TEMP fbc
WHERE status_flag='E';
BEGIN
l_comp_count := 0;
l_valid_comp_count := 0;
l_err_count := 0;
FOR bom_comp_rec in bom_comp_cur
LOOP
l_comp_count := l_comp_count + 1;
l_err_msg := NULL;
v_org := NULL;
l_item := NULL;
l_org_id := NULL;
v_num_comp_inv_id:=NULL;
l_err_count := 0;
BEGIN
-- fetch the organization_code
BEGIN
SELECT organization_code INTO v_org FROM
ftxs_118c_inv_org_map
WHERE source_inv_org =bom_comp_rec. organization_code;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_comp_rec. organization_code;
WHEN others THEN
l_err_msg := bom_comp_rec. organization_code || ' - ' || SQLCODE || ' - ' || SQLERRM;
END;
-- Validate Organization_code
BEGIN
SELECT organization_id INTO l_org_id
FROM mtl_parameters WHERE organization_code = v_org;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.organization_code|| ' - '||'Organization code does not exists ';
WHEN others THEN
l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.organization_code|| ' - ' ||SQLCODE||'-'||SQLERRM;
END;
-- Validate Assembly item Number ---------
BEGIN
SELECT inventory_item_id INTO
l_item
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND segment1 = bom_comp_rec.item_number
AND bom_enabled_flag = 'Y';
EXCEPTION
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - ' ||bom_comp_rec.item_number||'Item Number does not exists ';
WHEN others THEN
l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;
END;
--validate component item number----------------------------
BEGIN
SELECT inventory_item_id
INTO v_num_comp_inv_id
FROM mtl_system_items_b
WHERE segment1 = bom_comp_rec.component_item_number
AND organization_id = l_org_id
AND bom_enabled_flag = 'Y';
EXCEPTION
WHEN no_data_found THEN
l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.component_item_number||'Error in getting the inventory item id for the component item Number ';
WHEN others THEN
l_err_msg := l_err_msg|| ' - ' ||bom_comp_rec.organization_code||SQLCODE||'-'||SQLERRM;
END;
IF l_err_msg IS NULL THEN
l_valid_comp_count := l_valid_comp_count + 1;
UPDATE FTXS_118C_BOM_COMPS_TEMP
SET status_flag = 'V'
,err_msg = null
WHERE rowid = bom_comp_rec.row_id;
ELSE
UPDATE FTXS_118C_BOM_COMPS_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_comp_rec.row_id;
l_err_count:=l_err_count + 1;
END IF;
EXCEPTION
WHEN others THEN
UPDATE FTXS_118C_BOM_COMPS_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_comp_rec.row_id;
l_err_count :=l_err_count + 1;
END;
IF MOD(l_valid_comp_count,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records fetched :'||l_comp_count);
Fnd_file.put_line(fnd_file.log,'Total no of valid records :'||l_valid_comp_count);
Fnd_file.put_line(fnd_file.log,'Total no of errored records :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 THEN
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR bom_comp_err_rec IN bom_comp_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,bom_comp_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END validate_bom_comp;
--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
PROCEDURE Insert_bom_comp
as
l_userid NUMBER:=fnd_global.user_id;
v_date date := SYSDATE;
l_count number;
l_err_msg varchar2(1000);
l_err_count number;
CURSOR ins_comp_cur IS SELECT fbc.* ,rowid row_id
FROM FTXS_118C_BOM_COMPS_TEMP fbc
WHERE status_flag = 'V';
CURSOR ins_comp_err_cur IS SELECT fbc.* ,rowid row_id
FROM FTXS_118C_BOM_COMPS_TEMP fbc
WHERE status_flag = 'E';
BEGIN
l_count := 0;
l_err_count := NULL;
FOR ins_comp_rec IN ins_comp_cur
LOOP
BEGIN
l_err_msg := NULL;
INSERT INTO bom_inventory_comps_interface
( organization_code
,assembly_item_number
,operation_seq_num
,component_item_number
,component_quantity
,wip_supply_type
,supply_subinventory
,process_flag
,transaction_type
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES (ins_comp_rec.organization_code
,ins_comp_rec.item_number
,ins_comp_rec.operation_seq_num
,ins_comp_rec.component_item_number
,ins_comp_rec.component_quantity
,ins_comp_rec.wip_supply_type
,ins_comp_rec.supply_subinventory
,1
,'CREATE'
,v_date
,l_userid
,v_date
,l_userid
,l_userid
);
l_count := l_count + 1;
IF MOD(l_count,100) = 0 THEN
COMMIT;
END IF;
EXCEPTION
WHEN others THEN
l_err_msg := l_err_msg || ' - ' ||SQLCODE|| ' - ' ||SQLERRM;
UPDATE FTXS_118C_BOM_COMPS_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = ins_comp_rec.row_id;
l_err_count := l_err_count +1;
END;
End LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 then
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR ins_comp_err_rec IN ins_comp_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,ins_comp_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END Insert_bom_comp;
--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_ROUTING_HEADER_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_rout_headers
AS
l_rout_count number;
V_org varchar2(10);
l_err_msg varchar2(1000);
l_item number;
l_valid_rout_count number;
l_err_count number;
l_org_id number;
CURSOR bom_rout_head_cur IS SELECT frh.*
,rowid row_id
FROM FTXS_118C_ROUTING_HEADER_TEMP frh
WHERE status_flag='N';
CURSOR bom_rout_head_err_cur IS SELECT frh.*
,rowid row_id
FROM FTXS_118C_ROUTING_HEADER_TEMP frh
WHERE status_flag='E';
BEGIN
l_rout_count := 0;
l_valid_rout_count := 0;
l_err_count := 0;
FOR bom_rout_head_rec IN bom_rout_head_cur
LOOP
l_rout_count := l_rout_count + 1;
l_err_msg := NULL;
v_org := NULL;
l_item := NULL;
l_org_id := NULL;
BEGIN
-- fetch the organization_code
BEGIN
SELECT organization_code INTO v_org FROM
ftxs_118c_inv_org_map
WHERE source_inv_org =bom_rout_head_rec.organization_code;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_rout_head_rec.organization_code;
WHEN others THEN
l_err_msg := SQLCODE || ' - ' || SQLERRM;
END;
-- Validate Organization_code
BEGIN
SELECT organization_id INTO l_org_id
FROM mtl_parameters where organization_code = v_org;
Exception
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - '||bom_rout_head_rec.organization_code||'Organization code does not exists ';
WHEN others THEN
l_err_msg := l_err_msg || ' - '||bom_rout_head_rec.organization_code||SQLCODE||'-'||SQLERRM;
END;
-- Validate assembly item Number -------
BEGIN
SELECT inventory_item_id INTO
l_item
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND segment1 = bom_rout_head_rec.item_number
AND bom_enabled_flag = 'Y';
EXCEPTION
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - ' ||bom_rout_head_rec.item_number||'Item Number does not exists ';
WHEN others THEN
l_err_msg := l_err_msg|| ' - ' ||bom_rout_head_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;
END;
IF l_err_msg IS NULL THEN
l_valid_rout_count := l_valid_rout_count + 1;
UPDATE FTXS_118C_ROUTING_HEADER_TEMP
SET status_flag = 'V'
,err_msg = null
WHERE rowid = bom_rout_head_rec.row_id;
ELSE
UPDATE FTXS_118C_ROUTING_HEADER_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_rout_head_rec.row_id;
l_err_count := l_err_count+1;
END IF;
EXCEPTION
WHEN others THEN
UPDATE FTXS_118C_ROUTING_HEADER_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_rout_head_rec.row_id;
l_err_count :=l_err_count + 1;
END;
IF MOD(l_valid_rout_count,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records fetched :'||l_rout_count);
Fnd_file.put_line(fnd_file.log,'Total no of valid records :'||l_valid_rout_count);
Fnd_file.put_line(fnd_file.log,'Total no of errored records :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 THEN
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR bom_rout_head_err_rec IN bom_rout_head_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,bom_rout_head_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END validate_rout_headers;
--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
PROCEDURE Insert_rout_headers
AS
l_userid NUMBER:=0;
v_date date := SYSDATE;
l_count number;
l_err_msg varchar2(1000);
l_err_count number;
CURSOR ins_rout_header_cur IS SELECT frh.* ,rowid row_id
FROM FTXS_118C_ROUTING_HEADER_TEMP frh
WHERE status_flag = 'V';
CURSOR ins_rout_header_err_cur IS SELECT frh.* ,rowid row_id
FROM FTXS_118C_ROUTING_HEADER_TEMP frh
WHERE status_flag = 'E';
BEGIN
l_count := 0;
l_userid := fnd_global.user_id;
l_err_count :=0;
FOR ins_rout_header_rec IN ins_rout_header_cur
LOOP
BEGIN
l_err_msg := NULL;
INSERT INTO bom_op_routings_interface
(organization_code
,assembly_item_number
,routing_type
,process_flag
,transaction_type
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES (ins_rout_header_rec.organization_code
,ins_rout_header_rec.item_number
,ins_rout_header_rec.routing_type
,1
,'CREATE'
,v_date
,l_userid
,v_date
,l_userid
,l_userid
);
l_count := l_count + 1;
EXCEPTION
WHEN others THEN
UPDATE FTXS_118C_ROUTING_HEADER_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = ins_rout_header_rec.row_id;
l_err_count := l_err_count + 1;
END;
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 THEN
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR ins_rout_header_err_rec IN ins_rout_header_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,ins_rout_header_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END Insert_rout_headers;
--**********************************************************************************************
--This Procedure is used to validate the data in the FTXS_118C_ROUTING_OPS_TEMP Staging Table ---
--**********************************************************************************************
PROCEDURE validate_routing_ops
AS
l_rout_ops_count number;
V_org varchar2(20);
l_err_msg varchar2(1000);
l_item number;
l_valid_rout_ops_count number;
v_num_dept_id number;
l_org_id number;
l_err_count number;
CURSOR bom_routing_ops_cur IS SELECT fro.*
,rowid row_id
FROM FTXS_118C_ROUTING_OPS_TEMP fro
WHERE status_flag='N';
CURSOR bom_routing_ops_err_cur IS SELECT fro.*
,rowid row_id
FROM FTXS_118C_ROUTING_OPS_TEMP fro
WHERE status_flag='E';
BEGIN
l_rout_ops_count := 0;
l_valid_rout_ops_count := 0;
l_err_count := 0;
FOR bom_routing_ops_rec in bom_routing_ops_cur
LOOP
l_rout_ops_count :=l_rout_ops_count + 1;
l_err_msg := NULL;
v_org := NULL;
l_item := NULL;
l_org_id := NULL;
v_num_dept_id:=NULL;
BEGIN
-- fetch the organization_code
BEGIN
SELECT organization_code INTO v_org FROM
ftxs_118c_inv_org_map
WHERE source_inv_org =bom_routing_ops_rec.organization_code;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := 'No organization_code has mapped for this ' || ' - '||bom_routing_ops_rec.organization_code;
WHEN others THEN
l_err_msg := SQLCODE || ' - ' || SQLERRM;
END;
-- Validate Organization_code
BEGIN
SELECT organization_id INTO l_org_id
FROM mtl_parameters WHERE organization_code = v_org;
Exception
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - '||bom_routing_ops_rec.organization_code||'Organization code does not exists ';
WHEN others THEN
l_err_msg := l_err_msg || ' - '||bom_routing_ops_rec.organization_code||SQLCODE||'-'||SQLERRM;
END;
-- Validate assembly item Number ----------------------------------------------
BEGIN
SELECT inventory_item_id INTO
l_item
FROM mtl_system_items_b
WHERE organization_id = l_org_id
AND segment1 = bom_routing_ops_rec.item_number
AND bom_enabled_flag = 'Y';
Exception
WHEN no_data_found THEN
l_err_msg := l_err_msg || ' - ' ||bom_routing_ops_rec.item_number||'Item Number does not exists ';
WHEN others THEN
l_err_msg := l_err_msg|| ' - ' ||bom_routing_ops_rec.item_number|| ' - '||SQLCODE||'-'||SQLERRM;
END;
---validate department code----------------------------------------
BEGIN
SELECT department_id INTO
v_num_dept_id
FROM bom_departments
WHERE department_code=bom_routing_ops_rec.department_code
AND organization_id = l_org_id;
EXCEPTION
WHEN no_data_found THEN
l_err_msg := bom_routing_ops_rec.department_code||'department code does not exists ';
WHEN others THEN
l_err_msg :=bom_routing_ops_rec.department_code||SQLCODE||'-'||SQLERRM;
END;
IF l_err_msg IS NULL THEN
l_valid_rout_ops_count := l_valid_rout_ops_count + 1;
UPDATE FTXS_118C_ROUTING_OPS_TEMP
SET status_flag = 'V'
,err_msg = NULL
WHERE rowid = bom_routing_ops_rec.row_id;
ELSE
UPDATE FTXS_118C_ROUTING_OPS_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_routing_ops_rec.row_id;
l_err_count := l_err_count +1;
END IF;
EXCEPTION
WHEN others THEN
UPDATE FTXS_118C_ROUTING_OPS_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = bom_routing_ops_rec.row_id;
l_err_count := l_err_count + 1;
End;
IF MOD(l_valid_rout_ops_count,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records fetched :'||l_rout_ops_count);
Fnd_file.put_line(fnd_file.log,'Total no of valid records :'||l_valid_rout_ops_count);
Fnd_file.put_line(fnd_file.log,'Total no of errored records :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 then
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR bom_routing_ops_err_rec IN bom_routing_ops_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,bom_routing_ops_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END validate_routing_ops;
--**********************************************************************************************
--This is Procedure is used to move the data from staging table to interface tables
--*********************************************************************************************
PROCEDURE Insert_routing_ops
as
l_userid NUMBER:=0;
v_date date := sysdate;
l_count number;
l_err_msg varchar2(1000);
l_err_count number;
CURSOR ins_routing_ops_cur IS SELECT fro.* ,rowid row_id
FROM FTXS_118C_ROUTING_OPS_TEMP fro
WHERE status_flag = 'V';
CURSOR ins_routing_ops_err_cur IS SELECT fro.* ,rowid row_id
FROM FTXS_118C_ROUTING_OPS_TEMP fro
WHERE status_flag = 'E';
BEGIN
l_count := 0;
l_userid := fnd_global.user_id;
l_err_count := 0;
FOR ins_routing_ops_rec IN ins_routing_ops_cur
LOOP
BEGIN
l_err_msg := NULL;
INSERT INTO bom_op_sequences_interface
(organization_code
,assembly_item_number
,operation_seq_num
,department_code
,effectivity_date
,process_flag
,transaction_type
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
)
VALUES (ins_routing_ops_rec.organization_code
,ins_routing_ops_rec.item_number
,ins_routing_ops_rec.operation_seq_num
,ins_routing_ops_rec.department_code
,ins_routing_ops_rec.effectivity_date
,1
,'CREATE'
,v_date
,l_userid
,v_date
,l_userid
,l_userid
);
l_count := l_count + 1;
EXCEPTION
WHEN others THEN
UPDATE FTXS_118C_ROUTING_OPS_TEMP
SET status_flag = 'E'
,err_msg = l_err_msg
WHERE rowid = ins_routing_ops_rec.row_id;
l_err_count:=l_err_count+1;
END;
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
Fnd_file.put_line(fnd_file.log,'Total no of records inserted:'||l_count);
Fnd_file.put_line(fnd_file.log,'Total no of records errored :'||l_err_count);
Fnd_file.put_line(fnd_file.log,'**************************************************');
IF l_err_count >=1 THEN
Fnd_file.put_line(fnd_file.log,'**************************************************');
FOR ins_routing_ops_err_rec IN ins_routing_ops_err_cur
LOOP
Fnd_file.put_line(fnd_file.log,ins_routing_ops_err_rec.err_msg);
END LOOP;
Fnd_file.put_line(fnd_file.log,'**************************************************');
END IF;
END Insert_routing_ops;
PROCEDURE main_procedure(errbuf OUT varchar2
,retcode OUT number) is
BEGIN
validate_headers;
Insert_headers;
validate_bom_comp;
Insert_bom_comp;
validate_rout_headers;
Insert_rout_headers;
validate_routing_ops;
Insert_routing_ops;
EXCEPTION
WHEN others THEN
fnd_file.put_line( fnd_file.log,' error occoured while inserting data into interface table '||SQLCODE||SQLERRM);
END main_procedure;
END;
/
No comments:
Post a Comment