Item and Category Conversion in Oracle Inventory

Following script imports Item and Category Details based on a Copy Item (Already defined). Program Names: INV_ITEM_CAT_ASSIGN_OI and INCOIN
-------------------------------------------

create or replace
PACKAGE xx_item_conv_pkg
AS
PROCEDURE main (errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_child_org IN VARCHAR2);

END xx_item_conv_pkg;
/
create or replace
PACKAGE BODY xx_item_conv_pkg
AS
g_user_id NUMBER DEFAULT fnd_global.user_id;
g_login_user NUMBER DEFAULT fnd_global.login_id;

/*****************************************************************************************
Object :
Event : main
Arguments : errbuf , retcode
Returns :
Description : This is the main procedure for validates the Data from the Staging table and loads into
mtl_system_interface table and calls Order Import program
to create an item.
***************************************************************************************/
PROCEDURE main (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_child_org IN VARCHAR2
)
AS
l_master_org_id mtl_parameters.organization_id%TYPE;
l_master_org_code mtl_parameters.organization_code%TYPE;
l_segment1 mtl_system_items_b.segment1%TYPE;
l_child_segment1 mtl_system_items_b.segment1%TYPE;
l_copy_segment1 mtl_system_items_b.segment1%TYPE;
l_copy_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
l_master_description mtl_system_items_b.description%TYPE;
l_rule_id ra_rules.rule_id%TYPE;
l_sl_num_ctrl_code mfg_lookups.lookup_code%TYPE;
l_process_flag mtl_system_items_interface.process_flag%TYPE;
l_planner_code mtl_planners.planner_code%TYPE;
l_code_combination_id gl_code_combinations.code_combination_id%TYPE;
l_cc_segment1 mtl_system_items_b.segment1%TYPE;
l_conversion_rate gl_daily_rates.conversion_rate%TYPE;
l_buyer_id po_agents.agent_id%TYPE;
l_inventory_asset_flag mtl_system_items_b.inventory_asset_flag%TYPE;
l_planning_make_buy_code mtl_system_items_b.planning_make_buy_code%TYPE;
l_item_type mtl_system_items_b.item_type%TYPE;
l_inv_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
l_wip_supply_type mtl_system_items_b.wip_supply_type%TYPE;
l_item mtl_system_items_b.segment1%TYPE;
l_set_process_id NUMBER;
l_c_set_process_id NUMBER;
l_master_count NUMBER := 0;
l_child_count NUMBER := 0;
l_total_cnt NUMBER;
l_success_cnt NUMBER;
l_proceed_flag VARCHAR2 (1) := 'Y';
l_mstr_flag VARCHAR2 (1) := 'Y';
l_child_flag VARCHAR2 (1) := 'Y';
l_child_validations VARCHAR2 (1) := 'Y';
l_m_null VARCHAR2 (1);
l_c_null VARCHAR2 (1);
l_count NUMBER;
l_chr_commodity VARCHAR2(200);
l_make_buy VARCHAR2(30);
l_error_code VARCHAR2 (1000);
l_error_msg VARCHAR2 (4000);
-- Variables for Request submition
v_validate_request_id NUMBER;
v_wait_status BOOLEAN;
v_phase VARCHAR2 (240);
v_status VARCHAR2 (240);
v_dev_phase VARCHAR2 (240);
v_dev_status VARCHAR2 (240);
v_message VARCHAR2 (240);
v_error NUMBER := 0;
e_abort EXCEPTION;
e_skip EXCEPTION;

/* Fetches all the New records of Master Org from Staging Table*/
CURSOR c_items
IS
SELECT *
FROM xx_item_conv_stg
WHERE status_flag = 'N';

/* Fetches all the Distinct COPY ITEMS from Staging Table*/
CURSOR c_copy_items
IS
SELECT DISTINCT copy_item
FROM xx_item_conv_stg
WHERE status_flag = 'N';

CURSOR c_mstr_copy_rec (
p_master_org_id NUMBER,
p_set_process_id NUMBER,
p_copy_item VARCHAR2
)
IS
SELECT *
FROM mtl_system_items_interface
WHERE organization_id = p_master_org_id
AND set_process_id = p_set_process_id
AND segment1 = p_copy_item;

CURSOR c_chld_copy_rec (
p_child_org_id NUMBER,
p_set_process_id NUMBER,
p_copy_item VARCHAR2
)
IS
SELECT *
FROM mtl_system_items_interface
WHERE organization_id = p_child_org_id
AND set_process_id = p_set_process_id
AND segment1 = p_copy_item;

/* Fetches all Error Records from Staging Table*/
CURSOR c_error_msg (p_set_process_id NUMBER)
IS
SELECT item, error_message, ERROR_CODE
FROM xx_item_conv_stg
WHERE status_flag = 'E'
AND ERROR_CODE IS NOT NULL
AND error_message IS NOT NULL
UNION ALL
SELECT msi.segment1, mie.error_message, mie.message_name
FROM mtl_system_items_interface msi,
mtl_interface_errors mie,
mtl_parameters mp
WHERE msi.set_process_id = p_set_process_id
AND msi.process_flag = 3
AND msi.organization_id = mie.organization_id
AND msi.transaction_id = mie.transaction_id
AND msi.request_id = mie.request_id
AND msi.organization_id = mp.organization_id;

rec_mtl_interface mtl_system_items_interface%ROWTYPE;
BEGIN
l_set_process_id := TO_CHAR (SYSDATE, 'DDMMMISS');

DELETE FROM xx_item_conv_stg;

COMMIT;

BEGIN
INSERT INTO xx_item_conv_stg
(request_id, creation_date, created_by,
last_update_date, last_updated_by, request_date,
requested_by, copy_item, item, description,
primary_uom, item_type, make_buy, planner_code,
item_status, buyer, commodity, life_cycle_category,
item_owner, source_cogs_pf_account, tek_product_family,
tek_company, serial_number_control_flag, fixed_lot_multiplier,
fixed_days_supply, fixed_order_quantity,
minimum_order_quantity, maximum_order_quantity,
first_article, edpm_policy, edpm_state, bom_type,
program, preprocessing_lead_time, full_lead_time,
postprocessing_lead_time, fixed_lead_time,
variable_lead_time, list_price,
release_time_fence_code, subinventory, hazard_class,
avbl_in_child_org, child_item_status, status_flag)
SELECT fnd_global.conc_request_id, SYSDATE, g_user_id, SYSDATE,
g_user_id, SYSDATE, g_user_id, trim(copy_item), trim(item),
trim(description), trim(primary_uom), trim(item_type), trim(make_buy),
trim(planner_code), trim(item_status), trim(buyer), trim(commodity),
trim(life_cycle_category), trim(item_owner), trim(source_cogs_pf_account),
trim(tek_product_family), trim(tek_company), trim(serial_number_control_flag),
trim(fixed_lot_multiplier), trim(fixed_days_supply),
trim(fixed_order_quantity), trim(minimum_order_quantity),
trim(maximum_order_quantity), trim(first_article), trim(edpm_policy),
trim(edpm_state), trim(bom_type), trim(program), trim(preprocessing_lead_time),
trim(full_lead_time), trim(postprocessing_lead_time), trim(fixed_lead_time),
trim(variable_lead_time), trim(list_price), trim(release_time_fence_code),
trim(subinventory), trim(hazard_class), trim(avbl_in_child_org),
trim(child_item_status), 'N'
FROM xx_items;
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
l_error_msg :=
'Error while inserting into table xx_item_conv_stg'
|| CHR (10)
|| 'Please check the data file';
RAISE e_abort;
END;

-- Get Master Org details
BEGIN
SELECT mp.organization_id, mp.organization_code
INTO l_master_org_id, l_master_org_code
FROM mtl_parameters mp
WHERE mp.organization_id = mp.master_organization_id;
EXCEPTION
WHEN OTHERS
THEN
retcode := 2;
l_error_msg := 'Error while fetching master org details';
RAISE e_abort;
END;

-- Check for Duplicates
BEGIN
SELECT COUNT (1), item
INTO l_count, l_item
FROM xx_item_conv_stg tki
WHERE ROWNUM = 1
GROUP BY item
HAVING COUNT (1) > 1;

EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

IF l_count > 0 THEN
retcode := 2;
l_error_msg :=
'Duplicate items found in the file, Please correct the file and run the program again';
RAISE e_abort;
END IF;

-- Check for Null Copy items
BEGIN
SELECT COUNT (1), copy_item
INTO l_count, l_item
FROM xx_item_conv_stg tki
WHERE copy_item IS NULL AND ROWNUM = 1
GROUP BY copy_item
HAVING COUNT (1) > 0;

EXCEPTION
WHEN OTHERS
THEN
NULL;
END;

IF l_count > 0 THEN
retcode := 2;
l_error_msg :=
'Blank Copy Items found in the file, Please correct the file and run the program again';
RAISE e_abort;
END IF;

FOR rec_copy_items IN c_copy_items
LOOP
l_proceed_flag := 'Y';
l_child_flag := 'Y';

IF rec_copy_items.copy_item IS NOT NULL
THEN
-- Check if Copy Item exists in master org
BEGIN
SELECT msi.segment1, msi.inventory_item_id
INTO l_copy_segment1, l_copy_inventory_item_id
FROM mtl_system_items_b msi
WHERE msi.segment1 = rec_copy_items.copy_item
AND msi.segment2 IS NULL
AND msi.organization_id = l_master_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' does not exist in Master Org '
|| SQLCODE
|| SQLERRM
);
l_proceed_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( 'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' does not exist in Master Org; ',
1,
4000
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' Validation Error : '
|| SQLCODE
|| SQLERRM
);
l_proceed_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( 'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' Validation Error; '
|| '|'
|| SQLERRM,
1,
4000
);
END;
ELSE
l_proceed_flag := 'N';
fnd_file.put_line (fnd_file.LOG, 'COPY ITEM is not provided ');
l_error_msg := SUBSTR ('COPY ITEM is not provided; ', 1, 4000);
END IF;

-- Update staging table with error message and set status flag to E
IF l_proceed_flag = 'N'
THEN
BEGIN
UPDATE xx_item_conv_stg
SET status_flag = 'E',
ERROR_CODE = l_error_code,
error_message = l_error_msg
WHERE copy_item = rec_copy_items.copy_item;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'In Update COPY ITEM exception '
|| SQLCODE
|| SQLERRM
);
retcode := 2;
l_error_msg :=
'Error while updating the status flag in staging table; ';
RAISE e_abort;
END;
ELSE
INSERT INTO mtl_system_items_interface
(organization_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
segment1,
--attribute30,
process_flag, set_process_id,
transaction_type
)
VALUES (l_master_org_id, SYSDATE, g_user_id,
SYSDATE, g_user_id, g_login_user,
rec_copy_items.copy_item,
--rec_items.item,
1, l_set_process_id,
'UPDATE'
);
END IF;

IF rec_copy_items.copy_item IS NOT NULL
THEN
-- Check if COPY item exists in Child Org
l_make_buy := NULL;
BEGIN
SELECT segment1--, decode(planning_make_buy_code,1,'Make',2,'Buy')
INTO l_cc_segment1--, l_make_buy
FROM mtl_system_items_b
WHERE segment1 = rec_copy_items.copy_item
AND organization_id = p_child_org;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line (fnd_file.LOG,
'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' does not exist in child org'
|| SQLERRM
);
l_child_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' does not exist in child org; ',
1,
4000
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' validation error in child org'
|| SQLCODE
|| SQLERRM
);
l_child_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'COPY ITEM: '
|| rec_copy_items.copy_item
|| ' validation error in child org; '
|| SQLERRM,
1,
4000
);
END;
ELSE
l_child_flag := 'N';
fnd_file.put_line (fnd_file.LOG, 'COPY ITEM is not provided ');
l_error_msg :=
SUBSTR (l_error_msg || 'COPY ITEM is not provided; ', 1, 4000);
END IF;

-- Update staging table with error message and set status flag to E
IF l_child_flag = 'N'
THEN
BEGIN
UPDATE xx_item_conv_stg
SET status_flag = 'E',
ERROR_CODE = l_error_code,
error_message = l_error_msg
WHERE copy_item = rec_copy_items.copy_item;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'In Update COPY ITEM exception '
|| SQLCODE
|| SQLERRM
);
retcode := 2;
l_error_msg :=
'Error while updating status flag in the staging table; ';
RAISE e_abort;
END;
ELSE
INSERT INTO mtl_system_items_interface
(organization_id, last_update_date, last_updated_by,
creation_date, created_by, last_update_login,
segment1,
--attribute30,
process_flag, set_process_id,
transaction_type
)
VALUES (p_child_org, SYSDATE, g_user_id,
SYSDATE, g_user_id, g_login_user,
rec_copy_items.copy_item,
--rec_items.item,
1, l_set_process_id,
'UPDATE'
);
END IF;
END LOOP;

COMMIT;

BEGIN
SELECT COUNT (1)
INTO l_master_count
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND process_flag = 1
AND organization_id = l_master_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_master_count := 0;
END;

BEGIN
SELECT COUNT (1)
INTO l_child_count
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND process_flag = 1
AND organization_id = p_child_org;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_child_count := 0;
END;

IF (l_master_count > 0 OR l_child_count > 0)
THEN
v_validate_request_id := NULL;
v_validate_request_id :=
fnd_request.submit_request
('INV',
'INCOIN',
NULL,
NULL,
FALSE,
l_master_org_id,
1, -- All Organizations - Y
1, -- Validate Items - 1 Y 2 N
2, -- Process Items - 1-Y and 2-N
2, -- Delete Processed Rows -1 Y2 N
l_set_process_id, -- Set Process ID
2, -- update Items - 1 for Create and 2 for update
CHR (0),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
COMMIT;

IF v_validate_request_id > 0
THEN
v_wait_status :=
fnd_concurrent.wait_for_request
(request_id => v_validate_request_id,
INTERVAL => 10,
max_wait => 1800,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
MESSAGE => v_message
);
END IF;
END IF;

fnd_file.put_line (fnd_file.LOG, 'Completed valiating the Copy Items');
l_proceed_flag := 'Y';
l_conversion_rate := NULL;

BEGIN
SELECT conversion_rate
INTO l_conversion_rate
FROM gl_daily_rates
WHERE from_currency = 'USD'
AND to_currency = 'CNY'
AND TRUNC (conversion_date) = TRUNC (SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
retcode := 2;
l_error_msg :=
'Error while getting the USD --> CNY conversion date; ';
RAISE e_abort;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Fetching Coversion Rate failed '
|| SQLCODE
|| SQLERRM
);
l_proceed_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Fetching Coversion Rate failed '
|| SQLERRM,
1,
4000
);
RAISE e_abort;
END;

FOR rec_items IN c_items
LOOP
BEGIN
l_mstr_flag := 'Y';
l_child_flag := 'Y';
l_proceed_flag := 'Y';
l_error_msg := NULL;
l_error_code := NULL;
fnd_file.put_line
(fnd_file.LOG,
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++'
);
fnd_file.put_line (fnd_file.LOG,
CHR (10)
|| 'Processing item '
|| rec_items.item
|| CHR (10)
);

l_make_buy := NULL;
SELECT decode(planning_make_buy_code,1,'Make',2,'Buy')
INTO l_make_buy
FROM mtl_system_items_b
WHERE segment1 = rec_items.copy_item
AND segment2 IS NULL
AND organization_id = p_child_org;


-- Check if Item already exists in Master org
l_segment1 := NULL;

BEGIN
SELECT msi.segment1
INTO l_segment1
FROM mtl_system_items_b msi
WHERE msi.segment1 = rec_items.item
AND msi.segment2 IS NULL
AND msi.organization_id = l_master_org_id;

IF l_segment1 IS NOT NULL
THEN
--l_proceed_flag := 'N';
l_mstr_flag := 'N';
fnd_file.put_line
(fnd_file.LOG,
'Item already Exists in Organization '
|| l_master_org_code
);
--l_error_msg := SUBSTR ('Item # ' || rec_items.item || ' already Exists in Organization ' || l_master_org_code,1,4000);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_segment1 := NULL;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Item '
|| rec_items.item
|| ' validation error for Master Org'
|| SQLCODE
|| SQLERRM
);
l_mstr_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( 'Item '
|| rec_items.item
|| ' validation error for Master Org; '
|| '|'
|| SQLERRM,
1,
4000
);
END;

-- Check if Item exits in child org
l_child_segment1 := NULL;

BEGIN
SELECT msi.segment1
INTO l_child_segment1
FROM mtl_system_items_b msi
WHERE msi.segment1 = rec_items.item
AND msi.segment2 IS NULL
AND msi.organization_id = p_child_org;

IF l_child_segment1 IS NOT NULL
THEN
l_child_flag := 'N';
fnd_file.put_line (fnd_file.LOG,
'Item already exists in Child Org'
);
l_error_msg := 'Item already exists';
l_proceed_flag := 'N';

UPDATE xx_item_conv_stg
SET status_flag = 'E',
ERROR_CODE = '-1',
error_message = l_error_msg
WHERE item = rec_items.item;
--raise e_skip;
--l_error_msg := SUBSTR ('Item ' || rec_items.item || ' already exists in Child Org',1,4000);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_child_segment1 := NULL;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Item '
|| rec_items.item
|| ' validation error for Child Org: '
|| SQLCODE
|| SQLERRM
);
l_child_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( 'Item '
|| rec_items.item
|| ' validation error for Child Org; '
|| '|'
|| SQLERRM,
1,
4000
);
END;

IF l_proceed_flag = 'N'
THEN
RAISE e_skip;
END IF;

-- Serial Number Control Code Validation for a record in the Staging Table
l_sl_num_ctrl_code := NULL;

IF rec_items.serial_number_control_flag IS NOT NULL
THEN
BEGIN
SELECT ml.lookup_code
INTO l_sl_num_ctrl_code
FROM mfg_lookups ml
WHERE ml.lookup_type = 'MTL_SERIAL_NUMBER'
AND UPPER (ml.lookup_code) =
UPPER (rec_items.serial_number_control_flag)
AND ml.enabled_flag = 'Y'
AND TRUNC (SYSDATE)
BETWEEN TRUNC (NVL (ml.start_date_active, SYSDATE))
AND TRUNC (NVL (ml.end_date_active,
SYSDATE + 999
)
);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_proceed_flag := 'N';
fnd_file.put_line
(fnd_file.LOG,
'Serial Number Control Flag validation failed '
|| SQLCODE
|| SQLERRM
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Serial Number Control Flag '
|| rec_items.serial_number_control_flag
|| ' validation failed; ',
1,
4000
);
WHEN OTHERS
THEN
l_proceed_flag := 'N';
fnd_file.put_line
(fnd_file.LOG,
'Serial Number Control Flag Validation failed : '
|| SQLCODE
|| SQLERRM
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR
( l_error_msg
|| 'Serial Number Control Flag Validation failed; '
|| SQLERRM,
1,
4000
);
END;
END IF;

-- Planner Code Validations for a record in the Staging Table
l_planner_code := NULL;

IF rec_items.planner_code IS NOT NULL
THEN
BEGIN
SELECT mpl.planner_code
INTO l_planner_code
FROM mtl_planners mpl
WHERE UPPER (mpl.planner_code) =
UPPER (rec_items.planner_code)
AND mpl.organization_id = p_child_org
AND TRUNC (NVL (mpl.disable_date, SYSDATE)) >=
TRUNC (SYSDATE);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
--l_planner_code := 'BAD';
l_proceed_flag := 'N';
fnd_file.put_line (fnd_file.LOG,
'Planner code validation failed: '
|| SQLCODE
|| SQLERRM
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| ' Planner code '
|| rec_items.planner_code
|| ' validation failed; ',
1,
4000
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Planner code validation failed '
|| SQLCODE
|| SQLERRM
);
l_proceed_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Planner code validation failed; '
|| SQLERRM,
1,
4000
);
END;
END IF;

-- Product Code Validation for a record in the Staging Table
l_code_combination_id := NULL;

IF rec_items.tek_product_family IS NOT NULL AND rec_items.tek_company IS NOT NULL
THEN
BEGIN
SELECT gk.code_combination_id
INTO l_code_combination_id
FROM gl_code_combinations_kfv gk
WHERE gk.concatenated_segments =
( SELECT DISTINCT rec_items.tek_company||'.'||gcc.segment2||'.'||
rec_items.tek_product_family||'.'||gcc.segment4||'.'||
gcc.segment5||'.'||gcc.segment6||'.'||
gcc.segment7||'.'||gcc.segment8
FROM gl_code_combinations gcc, mtl_system_items_interface msi
WHERE gcc.code_combination_id = msi.cost_of_sales_account
AND msi.segment1 = rec_items.copy_item
AND msi.organization_id = p_child_org
AND rownum = 1);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_proceed_flag := 'N';
fnd_file.put_line (fnd_file.LOG,
'COGS Account does not exist '
|| SQLCODE
|| SQLERRM
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR (l_error_msg
|| ' COGS Account does not exist; ',
1,
4000
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Code Combination Id Failed '
|| SQLCODE
|| SQLERRM
);
l_proceed_flag := 'N';
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Code Combination Id Failed; '
|| SQLERRM,
1,
4000
);
END;
END IF;

l_buyer_id := NULL;

IF rec_items.buyer = 'DONOTUSE'--IS NOT NULL
THEN
BEGIN
SELECT pa.agent_id
INTO l_buyer_id
FROM po_agents pa, per_all_people_f papf
WHERE pa.agent_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.full_name = UPPER (rec_items.buyer);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_buyer_id := NULL;
l_proceed_flag := 'N';
fnd_file.put_line (fnd_file.LOG,
'Buyer: '
|| rec_items.buyer
|| ' not found'
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Buyer: '
|| rec_items.buyer
|| ' not found; ',
1,
4000
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Buyer: '
|| rec_items.buyer
|| 'for item: '
|| rec_items.item
|| ' Failed'
|| SQLCODE
|| SQLERRM
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Buyer: '
|| rec_items.buyer
|| 'for item: '
|| rec_items.item
|| ' Failed; '
|| SQLERRM,
1,
4000
);
END;
ELSE
l_buyer_id := NULL;
END IF;

rec_mtl_interface := NULL;
l_m_null := 'N';
l_c_null := 'N';

OPEN c_mstr_copy_rec (l_master_org_id,
l_set_process_id,
rec_items.copy_item
);

FETCH c_mstr_copy_rec
INTO rec_mtl_interface;

IF c_mstr_copy_rec%NOTFOUND
THEN
l_m_null := 'Y';
END IF;

CLOSE c_mstr_copy_rec;

rec_mtl_interface.attribute30 := rec_items.item;
rec_mtl_interface.interface_table_unique_id := NULL;

IF l_mstr_flag = 'Y' AND l_m_null = 'N'
THEN
INSERT INTO mtl_system_items_interface
VALUES rec_mtl_interface;
END IF;

rec_mtl_interface := NULL;

OPEN c_chld_copy_rec (p_child_org,
l_set_process_id,
rec_items.copy_item
);

FETCH c_chld_copy_rec
INTO rec_mtl_interface;

IF c_chld_copy_rec%NOTFOUND
THEN
l_c_null := 'Y';
END IF;

CLOSE c_chld_copy_rec;

rec_mtl_interface.attribute30 := rec_items.item;
rec_mtl_interface.interface_table_unique_id := NULL;

IF l_child_flag = 'Y' AND l_c_null = 'N'
THEN
INSERT INTO mtl_system_items_interface
VALUES rec_mtl_interface;
END IF;

l_inventory_asset_flag := NULL;
l_planning_make_buy_code := NULL;
l_item_type := NULL;
l_wip_supply_type := NULL;

BEGIN
SELECT msi.inventory_asset_flag, msi.planning_make_buy_code,
msi.item_type, msi.wip_supply_type
INTO l_inventory_asset_flag, l_planning_make_buy_code,
l_item_type, l_wip_supply_type
FROM mtl_system_items_interface msi
WHERE msi.process_flag = 4
AND msi.organization_id = p_child_org
AND msi.segment1 = rec_items.copy_item
AND msi.set_process_id = l_set_process_id
AND msi.attribute30 = rec_items.item;

IF l_item_type IS NOT NULL
THEN
IF l_item_type IN
('SA', 'PH', 'ATO', 'MP', 'OPT', 'PCFG', 'OSP')
THEN
l_inv_item_status_code := 'Precost';
ELSIF l_item_type IN ('OC', 'SVC', 'REF', 'PL')
THEN
l_inv_item_status_code := 'Active';
ELSIF l_item_type = 'MB'
THEN
IF l_planning_make_buy_code = 1
THEN
l_inv_item_status_code := 'Precost';
ELSE
l_inv_item_status_code := 'Active';
END IF;
ELSIF l_item_type = 'P'
THEN
IF l_inventory_asset_flag = 'N'
THEN
l_inv_item_status_code := 'Active';
ELSE
l_inv_item_status_code := 'Precost';
END IF;
END IF;
END IF;

-- planning_make_buy_code 1 Make 2 Buy
IF l_item_type IN ('P', 'SA')
AND l_planning_make_buy_code = 2
AND l_inventory_asset_flag = 'N'
AND p_child_org = 121
THEN
l_wip_supply_type := 3; -- 3 -- Operation Pull
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while deriving inventory_asset_flag/planning_make_buy_code/item_type/wip_supply_type'
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR
( l_error_msg
|| 'Error while fetching inventory_asset_flag/planning_make_buy_code/item_type/wip_supply_type; ',
1,
4000
);
END;

l_chr_commodity := NULL;

IF rec_items.commodity IS NOT NULL AND upper(l_make_buy) = 'BUY' THEN

BEGIN
SELECT upper(description)
INTO l_chr_commodity
FROM fnd_lookup_values_vl lookup
WHERE lookup_type = 'xx_COMMODITY_MAPPING'
AND enabled_flag = 'Y'
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE)
AND upper(lookup_code) = upper(rec_items.commodity)
AND EXISTS
(
SELECT 1
FROM mtl_categories_v
WHERE structure_name = 'PO Item Category'
AND category_concat_segs = upper(lookup.description)

);

INSERT INTO mtl_item_categories_interface
( item_number
, organization_id
, category_name
, old_category_name
, category_set_name
, category_set_id
, category_id
, process_flag
, transaction_type
, set_process_id
, last_updated_by
, created_by
, last_update_login
, last_update_date
, creation_date
)
VALUES
(
rec_items.item||'_'
, 82
, l_chr_commodity
, 'NEW'
, 'PO ITEM CATEGORY'
, NULL
, NULL
, 1
, 'UPDATE'
, -9876
, g_user_id
, g_user_id
, g_login_user
, SYSDATE
, SYSDATE
);
EXCEPTION
WHEN OTHERS THEN
l_proceed_flag := 'N';
fnd_file.put_line (fnd_file.LOG,
'Commodity Item Category: '
|| rec_items.commodity
|| ' is not valid'
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Commodity Item Category: '
|| rec_items.commodity
|| ' is not valid',
1,
4000
);
END;


END IF;

IF rec_items.life_cycle_category IS NOT NULL THEN

BEGIN

SELECT category_concat_segs
INTO rec_items.life_cycle_category
FROM mtl_categories_v
WHERE structure_name = 'Life Cycle Category'
AND upper(category_concat_segs) = upper(rec_items.life_cycle_category);

INSERT INTO mtl_item_categories_interface
( item_number
, organization_id
, category_name
, category_set_name
, category_set_id
, category_id
, process_flag
, transaction_type
, set_process_id
, last_updated_by
, created_by
, last_update_login
, last_update_date
, creation_date
)
VALUES
(
rec_items.item||'_'
, 82
, rec_items.life_cycle_category
, 'Life Cycle Category Set'
, NULL
, NULL
, 1
, 'CREATE'
, -9876
, g_user_id
, g_user_id
, g_login_user
, SYSDATE
, SYSDATE
);

INSERT INTO mtl_item_categories_interface
( item_number
, organization_id
, category_name
, category_set_name
, category_set_id
, category_id
, process_flag
, transaction_type
, set_process_id
, last_updated_by
, created_by
, last_update_login
, last_update_date
, creation_date
)
VALUES
(
rec_items.item||'_'
, p_child_org
, rec_items.life_cycle_category
, 'Life Cycle Category Set'
, NULL
, NULL
, 1
, 'CREATE'
, -9876
, g_user_id
, g_user_id
, g_login_user
, SYSDATE
, SYSDATE
);

EXCEPTION
WHEN OTHERS THEN
l_proceed_flag := 'N';
fnd_file.put_line (fnd_file.LOG,
'Life Cycle Category: '
|| rec_items.life_cycle_category
|| ' is not valid'
);
l_error_code := SQLCODE;
l_error_msg :=
SUBSTR ( l_error_msg
|| 'Life Cycle Category: '
|| rec_items.life_cycle_category
|| ' is not valid',
1,
4000
);
END;


END IF;

-- Update staging table with error message and set status flag to E
IF l_proceed_flag = 'N'
THEN
UPDATE xx_item_conv_stg
SET status_flag = 'E',
ERROR_CODE =
DECODE (ERROR_CODE,
NULL, l_error_code,
ERROR_CODE || l_error_code
),
error_message =
DECODE (error_message,
NULL, l_error_msg,
error_message || l_error_msg
)
WHERE item = rec_items.item;
ELSE
UPDATE mtl_system_items_interface msi
SET msi.segment1 = rec_items.item,
msi.item_number = rec_items.item,
msi.description =
NVL (rec_items.description, msi.description),
msi.inventory_item_id = NULL,
msi.process_flag = 1,
msi.transaction_type = 'CREATE',
msi.last_update_date = SYSDATE,
msi.last_updated_by = g_user_id,
msi.creation_date = SYSDATE,
msi.created_by = g_user_id,
msi.last_update_login = g_login_user,
msi.request_id = NULL,
msi.program_application_id = NULL,
msi.program_id = NULL,
msi.program_update_date = NULL,
msi.transaction_id = NULL,
msi.attribute30 = NULL,
msi.attribute1 = nvl(rec_items.item_owner,msi.attribute1),
msi.attribute4 = nvl(rec_items.first_article, msi.attribute4),
msi.attribute2 = nvl(rec_items.edpm_policy, attribute2),
msi.attribute3 = nvl(rec_items.edpm_state, attribute3),
msi.attribute13 = nvl(rec_items.bom_type, attribute13)
WHERE msi.process_flag = 4
AND msi.organization_id = l_master_org_id
AND msi.segment1 = rec_items.copy_item
AND msi.set_process_id = l_set_process_id
AND msi.attribute30 = rec_items.item;

UPDATE mtl_system_items_interface msi
SET msi.segment1 = rec_items.item,
msi.item_number = rec_items.item,
msi.description =
NVL (rec_items.description, msi.description),
msi.inventory_item_id = NULL,
msi.planner_code = l_planner_code,
msi.inventory_item_status_code =
NVL (l_inv_item_status_code,
inventory_item_status_code
), --rec_items.item_status,
msi.buyer_id = l_buyer_id,
msi.attribute1 = nvl(rec_items.item_owner,msi.attribute1),
msi.cost_of_sales_account =
NVL (l_code_combination_id,
msi.cost_of_sales_account),
--rec_items.source_cogs_pf_account, -- inlcude logic to derive this id.
msi.serial_number_control_code = l_sl_num_ctrl_code,
msi.fixed_lot_multiplier =
nvl(rec_items.fixed_lot_multiplier,rec_items.fixed_order_quantity),
msi.fixed_days_supply = rec_items.fixed_days_supply,
--msi.fixed_order_quantity =
-- rec_items.fixed_order_quantity,
msi.minimum_order_quantity =
rec_items.minimum_order_quantity,
msi.maximum_order_quantity =
rec_items.maximum_order_quantity,
msi.attribute4 = nvl(rec_items.first_article, msi.attribute4),
msi.attribute2 = nvl(rec_items.edpm_policy, attribute2),
msi.attribute3 = nvl(rec_items.edpm_state, attribute3),
msi.attribute13 = nvl(rec_items.bom_type, attribute13),
msi.preprocessing_lead_time =
decode(upper(l_make_buy),'BUY',rec_items.preprocessing_lead_time, NULL),
msi.full_lead_time = decode(upper(l_make_buy),'BUY',rec_items.full_lead_time, NULL),
msi.postprocessing_lead_time =
decode(upper(l_make_buy),'BUY',rec_items.postprocessing_lead_time, NULL),
msi.cumulative_total_lead_time = decode(upper(l_make_buy),'BUY',nvl(rec_items.preprocessing_lead_time,0) + nvl(rec_items.full_lead_time,0) + nvl(rec_items.postprocessing_lead_time,0),NULL),
msi.list_price_per_unit =
decode(upper(l_make_buy),'BUY',ROUND((rec_items.list_price * l_conversion_rate), 5),NULL)
,
msi.release_time_fence_code =
rec_items.release_time_fence_code,
--msi.wip_supply_subinventory = rec_items.subinventory,
msi.wip_supply_type = nvl(l_wip_supply_type,msi.wip_supply_type),
msi.process_flag = 1,
msi.transaction_type = 'CREATE',
msi.last_update_date = SYSDATE,
msi.last_updated_by = g_user_id,
msi.creation_date = SYSDATE,
msi.created_by = g_user_id,
msi.last_update_login = g_login_user,
msi.request_id = NULL,
msi.program_application_id = NULL,
msi.program_id = NULL,
msi.program_update_date = NULL,
msi.transaction_id = NULL,
msi.attribute30 = NULL
WHERE msi.process_flag = 4
AND msi.organization_id = p_child_org
AND msi.segment1 = rec_items.copy_item
AND msi.set_process_id = l_set_process_id
AND msi.attribute30 = rec_items.item;
END IF;

COMMIT;
EXCEPTION
WHEN e_skip
THEN
NULL;
END;
END LOOP;

BEGIN
SELECT COUNT (1)
INTO l_master_count
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND process_flag = 1
AND organization_id = l_master_org_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_master_count := 0;
END;

BEGIN
SELECT COUNT (1)
INTO l_child_count
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND process_flag = 1
AND organization_id = p_child_org;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_child_count := 0;
END;

IF (l_master_count > 0 OR l_child_count > 0)
THEN
v_validate_request_id := NULL;
v_validate_request_id :=
fnd_request.submit_request
('INV',
'INCOIN',
NULL,
NULL,
FALSE,
l_master_org_id,
1, -- All Organizations - Y
1, -- Validate Items - 1 Y 2 N
1, -- Process Items - 1-Y and 2-N
2, -- Delete Processed Rows -1 Y2 N
l_set_process_id, -- Set Process ID
1, -- update Items - 1 for Create and 2 for update
CHR (0),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);
COMMIT;

IF v_validate_request_id > 0
THEN
v_wait_status :=
fnd_concurrent.wait_for_request
(request_id => v_validate_request_id,
INTERVAL => 10,
max_wait => 1800,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
MESSAGE => v_message
);
END IF;

UPDATE mtl_item_categories_interface
SET set_process_id = l_set_process_id
WHERE set_process_id = -9876;

v_validate_request_id := NULL;
v_validate_request_id :=
fnd_request.submit_request
('INV',
'INV_ITEM_CAT_ASSIGN_OI',
NULL,
NULL,
FALSE,
to_number(l_set_process_id),
1,
1,
1,
1,
chr(0),
chr(0),
chr(0),
chr(0),
chr(0),
1,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL
);

COMMIT;

IF v_validate_request_id > 0
THEN
v_wait_status :=
fnd_concurrent.wait_for_request
(request_id => v_validate_request_id,
INTERVAL => 10,
max_wait => 1800,
phase => v_phase,
status => v_status,
dev_phase => v_dev_phase,
dev_status => v_dev_status,
MESSAGE => v_message
);
END IF;

DELETE FROM mtl_item_categories_interface WHERE set_process_id = l_set_process_id;

END IF;

BEGIN
UPDATE xx_item_conv_stg
SET status_flag = 'P'
WHERE ERROR_CODE IS NULL
AND error_message IS NULL
AND status_flag = 'N'
AND item NOT IN (
SELECT DISTINCT msi.segment1
FROM mtl_system_items_interface msi,
mtl_interface_errors mie,
mtl_parameters mp
WHERE msi.set_process_id = l_set_process_id
AND msi.process_flag = 3
AND msi.organization_id = mie.organization_id
AND msi.transaction_id = mie.transaction_id
AND msi.request_id = mie.request_id
AND msi.organization_id = mp.organization_id);

UPDATE xx_item_conv_stg stg
SET stg.status_flag = 'E'
WHERE stg.ERROR_CODE IS NULL
AND stg.error_message IS NULL
AND stg.status_flag = 'N'
AND stg.item IN (
SELECT DISTINCT msi.segment1
FROM mtl_system_items_interface msi,
mtl_interface_errors mie,
mtl_parameters mp
WHERE msi.set_process_id = l_set_process_id
AND msi.process_flag = 3
AND msi.organization_id = mie.organization_id
AND msi.transaction_id = mie.transaction_id
AND msi.request_id = mie.request_id
AND msi.organization_id = mp.organization_id);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while updating status_flag as P/E in table xx_item_conv_stg'
|| SQLERRM
);
END;

COMMIT;

SELECT COUNT (1)
INTO l_success_cnt
FROM xx_item_conv_stg;

fnd_file.put_line (fnd_file.output,
'Total Items Processed: ' || l_success_cnt
);

SELECT COUNT (1)
INTO l_success_cnt
FROM xx_item_conv_stg
WHERE status_flag = 'P';

fnd_file.put_line (fnd_file.output,
'Items Imported Successfully: ' || l_success_cnt
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line (fnd_file.output, 'Item Error Report');
fnd_file.put_line (fnd_file.output, '-----------------');
fnd_file.put_line (fnd_file.output,
RPAD ('Item', 30, ' ')
|| RPAD ('Error Code', 36, ' ')
|| 'Error Message'
);
fnd_file.put_line (fnd_file.output,
RPAD ('----', 30, ' ')
|| RPAD ('----------', 36, ' ')
|| '-------------'
);

FOR rec_error_msg IN c_error_msg (l_set_process_id)
LOOP
fnd_file.put_line (fnd_file.output,
RPAD (rec_error_msg.item, 30, ' ')
|| RPAD (NVL (rec_error_msg.ERROR_CODE, ' '),
36,
' '
)
|| rec_error_msg.error_message
);
END LOOP;
EXCEPTION
WHEN e_abort
THEN
ROLLBACK;
retcode := 2;
fnd_file.put_line (fnd_file.LOG,
'Error in the Main Procedure: ' || l_error_msg
);
fnd_file.put_line (fnd_file.output,
'Error in the Main Procedure: ' || l_error_msg
);
WHEN OTHERS
THEN
ROLLBACK;
retcode := 2;
fnd_file.put_line (fnd_file.LOG,
'Error in the Main Procedure' || SQLERRM
);
fnd_file.put_line (fnd_file.output,
'Error in the Main Procedure' || SQLERRM
);
END main;
END xx_item_conv_pkg;