BOM ITEM CONVERSION

CREATE TABLE XXOIC_CV_BOM_STG
(
RECORD_NO NUMBER,
ORGANIZATION_CODE VARCHAR2(3 BYTE),
ITEM_NAME VARCHAR2(81 BYTE),
ALTERNATE_BOM VARCHAR2(10 BYTE),
OPERATION_SEQUENCE NUMBER,
COMPONENT_ITEM VARCHAR2(81 BYTE),
COMPONENT_QUANTITY NUMBER,
COMPONENT_YIELD NUMBER,
INCLUDED_IN_COST_ROLLUP VARCHAR2(3 BYTE),
COMPONENT_SUPPLY_TYPE VARCHAR2(80 BYTE),
SUPPLY_SUBINVENTORY VARCHAR2(10 BYTE),
ERROR_CODE VARCHAR2(20 BYTE),
PROCESS_FLAG VARCHAR2(2 BYTE) DEFAULT 'R',
ERROR_MSG VARCHAR2(2000 BYTE),
DATA_FILE_NAME VARCHAR2(200 BYTE),
DATE_RUN DATE,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER
)

/*USING BELOW CTL FILE TO INSERT THE DATA TO THE ABOVE TABLE*/

--Control file

-- *****************************************************************************

-- *****************************************************************************
-- ----------------------------------------------------------------------------+
-- Description : Control file for Bill of Material.

-- Written on : 29-sept-2008
-- -----------------------------------------------------------------------------
OPTIONS (skip=1)
LOAD DATA
INFILE '&data_file_name'
APPEND
INTO TABLE XXOIC.XXOIC_CV_BOM_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
record_no sequence(MAX,1),
organization_code ,
item_name ,
alternate_bom ,
operation_sequence ,
component_item ,
component_quantity ,
component_yield ,
included_in_cost_rollup ,
component_supply_type ,
supply_subinventory ,
data_file_name ,
date_run SYSDATE,
last_update_date SYSDATE,
last_updated_by "FND_GLOBAL.USER_ID",
creation_date SYSDATE,
created_by "FND_GLOBAL.USER_ID",
last_update_login "FND_GLOBAL.LOGIN_ID"
)

/* Move the above CTL File & CSV file (with data)into the Below Path
/prod01/app/applprod/prodappl/XXOIC/bin */

/*After inserting the data to the above custom table...we have to validate the data,using below script
For this we have to registerd the concurrent program
in this program pls give the parameter : /prod01/app/applprod/prodappl/XXOIC/bin/xxxx.CSV*/

select text from user_source where name='XXOIC_CV_BOM_VALIDATE_PKG'
*********************************************************************************************************
/* Formatted on 2009/06/08 17:52 (Formatter Plus v4.8.6) */
PACKAGE xxoic_cv_bom_validate_pkg
AS
/* =====================================================================

-- All rights reserved.
-- =====================================================================
-- NAME : XXOIC_CV_BOM_VALIDATE_PKG.sql
--
-- DESIGN REFERENCE : Package for creating a Bill Of Material
--
-- PROGRAM TYPE : SQL Script
--
-- PURPOSE : This package inputs data from staging table into interface tables
-- NOTES :
--
-- REVISION HISTORY :
-- =====================================================================
-- Date Author Activity
-- =====================================================================


-- ===================================================================*/
PROCEDURE xxoic_cv_bom_int (
/* =====================================================================
-- NAME : xxoic_cv_bom_int
--
-- PROGRAM TYPE : Procedure
--
-- DESCRIPTION : This procedure fetches data from xxoic.xxoic_cv_bom_stg staging table and
-- inserts into bom_bill_of_mtls_interface,bom_inventory_comps_interface
-- interface tables.
-- INPUTS : None
-- OUTPUTS : None
-- ===================================================================*/
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
);
END xxoic_cv_bom_validate_pkg;

PACKAGE BODY xxoic_cv_bom_validate_pkg
AS
PROCEDURE xxoic_cv_bom_int (
/* =====================================================================
-- NAME : xxoic_cv_bom_int
--
-- PROGRAM TYPE : Procedure
--
-- DESCRIPTION : This procedure fetches data from xxoic.xxoic_cv_bom_stg staging table and
-- inserts into bom_bill_of_mtls_interface,bom_inventory_comps_interface
-- interface tables.

-- INPUTS : None

-- OUTPUTS : None
-- ===================================================================*/
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
)
AS
CURSOR c_bom_itm
IS
SELECT record_no, organization_code, item_name, alternate_bom
FROM xxoic.xxoic_cv_bom_stg
WHERE process_flag = 'R';

CURSOR c_bom_comp (cp_item VARCHAR2, cp_organization_code VARCHAR2)
IS
SELECT record_no, organization_code, item_name, alternate_bom,
operation_sequence, component_item, component_quantity,
component_yield,
DECODE (included_in_cost_rollup,
'Y', 1,
'N', 2,
NULL
) included_in_cost_rollup,
component_supply_type, supply_subinventory
FROM xxoic.xxoic_cv_bom_stg
WHERE process_flag = 'R'
AND organization_code = cp_organization_code
AND item_name = cp_item;

r_bom_record_itm c_bom_itm%ROWTYPE;
r_bom_record_comp c_bom_comp%ROWTYPE;
lv_error_msg VARCHAR2 (200 BYTE);
lv_flag VARCHAR2 (1 BYTE);
lv_comp_flag VARCHAR2 (1 BYTE);
ln_organization_id NUMBER;
lv_count NUMBER;
ln_item_id NUMBER;
ln_component_item_id NUMBER;
lv_organization_code VARCHAR2 (3 BYTE);
lv_error_code VARCHAR2 (200);
ln_bom_item_type NUMBER;
lv_item_num VARCHAR (240 BYTE);
ln_record_no NUMBER;
ln_record_no_comp NUMBER;
lv_alternate_bom VARCHAR2 (10);
lv_trans_detail1 VARCHAR2 (2000);
lv_trans_detail2 VARCHAR2 (2000);
lv_trans_detail3 VARCHAR2 (2000);
lv_trans_detail4 VARCHAR2 (2000);
ln_lookup_code NUMBER;
ln_successful_rec NUMBER := 0;
ln_failed_rec NUMBER := 0;
BEGIN
FOR r_bom_record_itm IN c_bom_itm
LOOP
lv_flag := NULL;
lv_comp_flag := NULL;
lv_error_code := NULL;
ln_record_no := r_bom_record_itm.record_no;
lv_trans_detail1 := NULL;
lv_trans_detail2 := NULL;
lv_trans_detail3 := NULL;
lv_trans_detail4 := NULL;

/*
================================================================================
--Validation for organization id
================================================================================
*/
IF r_bom_record_itm.organization_code IS NOT NULL
THEN
BEGIN
SELECT organization_id
INTO ln_organization_id
FROM org_organization_definitions
WHERE organization_code = r_bom_record_itm.organization_code;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg := 'Organization does not exist';
lv_trans_detail1 :=
'Unable to find organization id for this record';
lv_trans_detail2 := 'Provide with valid organization code';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( r_bom_record_itm.organization_code
|| ' Organization does not exist '
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
r_bom_record_itm.organization_code
|| ' Organization does not exist'
);
END;
ELSE
IF r_bom_record_itm.organization_code IS NULL
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg := 'Organization code can not be null';
lv_trans_detail1 := 'Organization code can not be left blank';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( 'Organization code can not be null'
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
'Organization code can not be null'
);
END IF;
END IF;

/*

================================================================================

--Validation for inventry item name

================================================================================

*/
IF r_bom_record_itm.item_name IS NOT NULL
THEN
BEGIN
SELECT bom_item_type, segment1, inventory_item_id
INTO ln_bom_item_type, lv_item_num, ln_item_id
FROM mtl_system_items_b
WHERE segment1 = r_bom_record_itm.item_name
AND organization_id = ln_organization_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg := 'Item name does not exist';
lv_trans_detail1 := 'Item name value must be provided';
lv_trans_detail2 := 'Required for deriving item type';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( r_bom_record_itm.item_name
|| 'Item name does not exist'
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
r_bom_record_itm.item_name
|| 'Item name does not exist'
);
END;
END IF;

/*

================================================================================

--Validation for alternate bom desig

================================================================================

*/
IF r_bom_record_itm.alternate_bom IS NOT NULL
THEN
BEGIN
SELECT alternate_designator_code
INTO lv_alternate_bom
FROM bom_alternate_designators_tl
WHERE alternate_designator_code =
r_bom_record_itm.alternate_bom
AND organization_id = ln_organization_id;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg :=
'The given alternate_designator does not exist';
lv_trans_detail1 :=
'The given alternate_designator does not exist';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line ( 'The given alternate_designator '
|| r_bom_record_itm.alternate_bom
|| ' does not exist'
|| SQLERRM
);
fnd_file.put_line (fnd_file.LOG,
'The given alternate_designator '
|| r_bom_record_itm.alternate_bom
|| ' does not exist'
);
END;
END IF;

/*

================================================================================

check for already inserted data in interface table

================================================================================

*/
BEGIN
SELECT COUNT (1)
INTO lv_count
FROM bom_bill_of_mtls_interface
WHERE organization_id = ln_organization_id
AND assembly_item_id = ln_item_id;

IF lv_count > 0
THEN
lv_flag := 'N';
END IF;
END;

/*

================================================================================

--updload data into bom_bill_of_mtls_interface interface table

================================================================================

*/
IF lv_flag IS NULL
THEN
BEGIN
INSERT INTO bom_bill_of_mtls_interface
(bill_sequence_id,
alternate_bom_designator, assembly_item_id,
process_flag, item_number, last_update_login,
last_update_date, last_updated_by,
creation_date, created_by, organization_id,
transaction_type
)
VALUES (bom.bom_inventory_components_s.NEXTVAL,
lv_alternate_bom, ln_item_id,
1, lv_item_num, fnd_global.login_id,
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id, ln_organization_id,
'Create'
);

lv_comp_flag := 'Y';
EXCEPTION
WHEN OTHERS
THEN
lv_comp_flag := 'N';
lv_error_msg :=
'Unble to insert records into interface table bom_bill_of_mtls_interface';
lv_error_code := SQLCODE;
lv_trans_detail1 :=
'Unble to insert records into interface table bom_bill_of_mtls_interface';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line
( 'Unble to insert records into interface table bom_bill_of_mtls_interface with record_no.'
|| ln_record_no
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'Unble to insert records into interface table bom_bill_of_mtls_interface'
);
END;
END IF;

/*

===============================================================================

Loop for inserting data into bom_inventory_comps_interface table

===============================================================================

*/
IF lv_comp_flag = 'Y'
THEN
BEGIN
FOR r_bom_record_comp IN
c_bom_comp (r_bom_record_itm.item_name,
r_bom_record_itm.organization_code
)
LOOP
lv_error_code := NULL;
ln_record_no_comp := r_bom_record_comp.record_no;
lv_trans_detail1 := NULL;
lv_trans_detail2 := NULL;
lv_trans_detail3 := NULL;
lv_trans_detail4 := NULL;

/*

================================================================================

--Validation for component item name/component item id

================================================================================

*/
IF r_bom_record_comp.component_item IS NOT NULL
THEN
BEGIN
SELECT inventory_item_id
INTO ln_component_item_id
FROM mtl_system_items_b
WHERE organization_id = ln_organization_id
AND segment1 = r_bom_record_comp.component_item
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg :=
'component item of this name does not exist';
lv_trans_detail1 :=
'Unable to find component_item_id for this component_item_name';
lv_trans_detail2 :=
'Provide with valid component item name';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no_comp,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line
( r_bom_record_comp.component_item
|| ' component item does not exist '
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
r_bom_record_comp.component_item
|| ' component item does not exist'
);
END;
END IF;

/*

================================================================================

--Validation for component_supply_type

================================================================================

*/
IF r_bom_record_comp.component_supply_type IS NOT NULL
THEN
BEGIN
SELECT DISTINCT lookup_code
INTO ln_lookup_code
FROM mfg_lookups
WHERE lookup_type = 'WIP_SUPPLY'
AND meaning =
r_bom_record_comp.component_supply_type;
EXCEPTION
WHEN OTHERS
THEN
lv_error_code := SQLCODE;
lv_flag := 'N';
lv_error_msg :=
'The given value for component_supply_type is invalid ';
lv_trans_detail1 :=
'The given value for component_supply_type is invalid';
xxoic_error_handling_pkg.xxoic_print_log
(ln_record_no_comp,
lv_error_code,
lv_error_msg,
lv_trans_detail1,
lv_trans_detail2,
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
DBMS_OUTPUT.put_line
( 'The given component_supply_type '
|| r_bom_record_comp.component_supply_type
|| 'is invalid '
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'The given component_supply_type '
|| r_bom_record_comp.component_supply_type
|| 'is invalid '
);
END;
END IF;

/*

================================================================================

--updload data into bom_inventory_comps_interface interface table

================================================================================

*/
IF lv_flag IS NULL
THEN
BEGIN
INSERT INTO bom_inventory_comps_interface
(bill_sequence_id,
assembly_item_id, component_item_id,
effectivity_date,
organization_code,
organization_id, bom_item_type,
last_update_login, last_update_date,
last_updated_by, creation_date,
created_by,
alternate_bom_designator,
operation_seq_num,
component_quantity,
component_yield_factor, process_flag,
transaction_type,
include_in_cost_rollup,
wip_supply_type,
supply_subinventory
)
VALUES (bom.bom_inventory_components_s.CURRVAL,
ln_item_id, ln_component_item_id,
SYSDATE,
r_bom_record_comp.organization_code,
ln_organization_id, ln_bom_item_type,
fnd_global.login_id, SYSDATE,
fnd_global.user_id, SYSDATE,
fnd_global.user_id,
r_bom_record_comp.alternate_bom,
r_bom_record_comp.operation_sequence,
r_bom_record_comp.component_quantity,
r_bom_record_comp.component_yield, 1,
'Create',
r_bom_record_comp.included_in_cost_rollup,
ln_lookup_code,
r_bom_record_comp.supply_subinventory
);

ln_successful_rec := ln_successful_rec + 1;
/*

================================================================================

--Update xxoic.xxoic_cv_bom_stg staging table using error handling package

================================================================================

*/
xxoic_error_handling_pkg.xxoic_print_output
(ln_record_no_comp,
'Data loaded successfully',
'In bom_inventory_comps_interface table',
lv_trans_detail3,
lv_trans_detail4,
'xxoic.xxoic_cv_bom_stg',
SYSDATE
);
EXCEPTION
WHEN OTHERS
THEN
lv_flag := 'N';
lv_error_msg :=
'Unble to insert records into interface table ';
lv_error_code := SQLCODE;
lv_trans_detail1 :=
'Unble to insert records into interface table ';
-- xxoic_error_handling_pkg.xxoic_print_log

-- (ln_record_no_comp,

-- lv_error_code,

-- lv_error_msg,

-- lv_trans_detail1,

-- lv_trans_detail2,

-- lv_trans_detail3,

-- lv_trans_detail4,

-- 'xxoic.xxoic_cv_bom_stg',

-- SYSDATE

-- );
DBMS_OUTPUT.put_line
( 'Unble to insert records into interface table bom_inventory_comps_interface with record_no.'
|| ln_record_no_comp
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'Unble to insert records into interface table bom_inventory_comps_interface'
);
END;
END IF;

IF lv_flag = 'N'
THEN
ln_failed_rec := ln_failed_rec + 1;
END IF;
END LOOP;
END;
END IF;
END LOOP;

DBMS_OUTPUT.put_line ( 'Number of successful records is '
|| ln_successful_rec
|| ' and failed records is '
|| ln_failed_rec
);
fnd_file.put_line (fnd_file.LOG,
'Number of successful records is '
|| ln_successful_rec
|| ' and failed records is '
|| ln_failed_rec
);
END xxoic_cv_bom_int;
END xxoic_cv_bom_validate_pkg;
*********************************************************************************************************

/* Once run the above program..pls check the data in to the below table*/

SELECT * FROM bom_inventory_comps_interface

select * from bom_bill_of_mtls_interface

After run the above programs pls run the Standard program which is oracle provided
Program Name is : Bill and Routing Interface(Responsibility is Bills of Material Super User)

After run the above program..check the view Log..
If its any error ....

error flag is 3
Then update the Below script

update bom_inventory_comps_interface set process_flag=1 where organization_id=751
and process_flag=3

update bom_bill_of_mtls_interface set process_flag=1 where organization_id=751
and process_flag=3

Once updated above script RE-Run the Program Name is : Bill and Routing Interface(Responsibility is Bills of Material Super User)