--Stock Items
CREATE TABLE XXFIA_ITEM_INTERFACE
(ITEM_CODE VARCHAR2(30),
DESCRIPTION VARCHAR2(240),
SEGMENT1 VARCHAR2(240),
SEGMENT2 VARCHAR2(240),
CATEGORY_ID NUMBER,
UOM VARCHAR2(240),
EXPENSE_ACCOUNT_SEG VARCHAR2(240),
EXPENSE_ACCOUNT NUMBER,
SOURCE_NUMBER VARCHAR2(240),
PROCESS_FLAG VARCHAR2(2),
ERROR_MSG VARCHAR2(240));
--Non Stock Items
CREATE TABLE XXFIA_ITEM_INTERFACE_NS
(ITEM_CODE VARCHAR2(30),
DESCRIPTION VARCHAR2(240),
SEGMENT1 VARCHAR2(240),
SEGMENT2 VARCHAR2(240),
CATEGORY_ID NUMBER,
UOM VARCHAR2(240),
EXPENSE_ACCOUNT_SEG VARCHAR2(240),
EXPENSE_ACCOUNT NUMBER,
SOURCE_NUMBER VARCHAR2(240),
PROCESS_FLAG VARCHAR2(2),
ERROR_MSG VARCHAR2(240))
;
1. Upload using toad or any other tool.
2. Check if all the UOM exists in the masters.
select distinct trim(UOM),1
from XXFIA_ITEM_INTERFACE
WHERE TRIM(UOM)NOT IN (SELECT uom_code
FROM
MTL_UNITS_OF_MEASURE MTL)
UNION
select distinct trim(UOM),2
from XXFIA_ITEM_INTERFACE_NS
WHERE TRIM(UOM) NOT IN (SELECT trim(uom_code)
FROM
MTL_UNITS_OF_MEASURE MTL)
ORDER BY 1
;
--truncate table XXFIA_ITEM_INTERFACE;
3. get the category_id
select category_id,segment1,SEGMENT2, description,category_concat_segs, structure_name
from mtl_categories_v
--where segment1 = '%ELEC%'
order by category_id,structure_id;
SELECT * FROM XXFIA_ITEM_INTERFACE
WHERE CATEGORY_ID IS NULL
;
4. update category_id
DECLARE
CURSOR C1 IS
SELECT CATEGORY_ID,SEGMENT1, SEGMENT2 FROM mtl_categories_v;
BEGIN
FOR I IN C1 LOOP
UPDATE XXFIA_ITEM_INTERFACE
SET CATEGORY_ID = I.CATEGORY_ID
WHERE UPPER(I.SEGMENT1) = TRIM(UPPER(SEGMENT1))
AND UPPER(I.SEGMENT2) = TRIM(UPPER(SEGMENT2))
AND CATEGORY_ID IS NULL
;
COMMIT;
END LOOP;
END;
SELECT DISTINCT TRIM(SEGMENT1), TRIM(SEGMENT2) FROM XXFIA_ITEM_INTERFACE
WHERE CATEGORY_ID IS NULL
5. get the Master Organization, Other Oranizations.
--2398 MASTER
--2352 CHILD
6. Check if the accounts combination exists in GL or not.
SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID
,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)
,EXPENSE_ACCOUNT_SEG
,3083 --TEMPLATE_ID
--,3391161
, fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL'
,key_flex_code => 'GL#'
,structure_number => 50522
,validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY')
,concatenated_segments => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT
FROM XXFIA_ITEM_INTERFACE
where
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL'
,key_flex_code => 'GL#'
,structure_number => 50522
,validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY')
,concatenated_segments => EXPENSE_ACCOUNT_SEG||'0000') = '0';
SELECT * FROM GL_CODE_COMBINATIONS_V
WHERE CODE_COMBINATION_ID = 3391161;
7. Get the template Id and insert into the interface tables.
--STOCK TEMPLATE_ID 3083
insert into mtl_system_items_interface
(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,
description
,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;
,EXPENSE_ACCOUNT
)
SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID
,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)
,3083 --TEMPLATE_ID
--,3391161
, fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL'
,key_flex_code => 'GL#'
,structure_number => 50522
,validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY')
,concatenated_segments => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT
FROM XXFIA_ITEM_INTERFACE
WHERE ITEM_CODE = ('67000450');
8. Run the concurrent Program from Inventory Resp
Import Items with Organization All, and Proces Flag as 1 and other as yes.
9.
For Categories.
insert into MTL_ITEM_CATEGORIES_INTERFACE
(
INVENTORY_ITEM_ID,
item_number,
category_set_id, -- SAME for all --select category_set_id,category_set_name,description,structure_name, structure_id from mtl_category_sets_v where category_set_name LIKE 'FIA%';
category_id, --updated using above script
process_flag, organization_id, set_process_id, transaction_type)
--values('TESTCATG2',1100000041,2127,1,2398,1,'CREATE');
SELECT
(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = TRIM(ITEM_CODE) and organization_id =2398 ),
TRIM(ITEM_CODE)
,1100000041 --static get once for all
,category_id
,1
,2398 --MASTER_ORG_ID
,1,'CREATE' FROM XXFIA_ITEM_INTERFACE
WHERE TRIM(ITEM_CODE) = ('67000450');
10. Again Run the concurrent Program from Inventory Resp
Import Items with Organization All, and Proces Flag as 1 and other as yes.
11. Before adding to other organizations
we need to run the datafix
---RUN DATAFIX
12.
--FOR CHILD ORG
insert into mtl_system_items_interface
(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,
description
,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;
,EXPENSE_ACCOUNT
)
SELECT 1,1,'CREATE',2352 --CHILD_ORG_ID
,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)
,3083 --TEMPLATE_ID
--,3391161
, fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL'
,key_flex_code => 'GL#'
,structure_number => 50522
,validation_date => TO_CHAR(SYSDATE,'DD-MON-YYYY')
,concatenated_segments => EXPENSE_ACCOUNT_SEG||'0000')ACCOUNT
FROM XXFIA_ITEM_INTERFACE WHERE TRIM(ITEM_CODE) NOT IN ( '67000450')
;
COMMIT;
13.
Again Run the concurrent Program from Inventory Resp
Import Items with Organization All, and Proces Flag as 1 and other as yes.
14. do the same for Non Stock Items also.
--------------Non Stock--------------
SELECT * FROM XXFIA_ITEM_INTERFACE_NS;
TEMPLATE_ID:3084 --NON STOCK
DELETE FROM mtl_system_items_interface;
1.
insert into mtl_system_items_interface
(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,
description
,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;
--,EXPENSE_ACCOUNT
,MARKET_PRICE
)
SELECT 1,1,'CREATE',2398 --MASTER_ORG_ID
,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)
,3084 --TEMPLATE_ID
,TRUNC(EXPENSE_ACCOUNT_SEG,2)
FROM XXFIA_ITEM_INTERFACE_NS
WHERE ITEM_CODE <>'NG000016'-- <> 'NGE00001'
AND CATEGORY_ID IS NOT NULL
AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)
--AND TRIM(ITEM_CODE) = 'NB000143'
;
2. concurrent Program.
3.
insert into MTL_ITEM_CATEGORIES_INTERFACE
(
INVENTORY_ITEM_ID,
item_number,
category_set_id, -- SAME for all --select category_set_id,category_set_name,description,structure_name, structure_id from mtl_category_sets_v where category_set_name LIKE 'FIA%';
category_id, --updated using above script
process_flag, organization_id, set_process_id, transaction_type)
----values('TESTCATG2',1100000041,2127,1,2398,1,'CREATE');
SELECT
(SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 = TRIM(ITEM_CODE) AND ORGANIZATION_ID = 2398),
TRIM(ITEM_CODE)
,1100000041 --static get once for all
,category_id
,1
,2398 --MASTER_ORG_ID
,1,'CREATE' FROM XXFIA_ITEM_INTERFACE_NS
WHERE ITEM_CODE <> 'NG000016'--<> 'NGE00001'
AND CATEGORY_ID IS NOT NULL
--AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)
;
4. concurrent Program.
5.
--FOR CHILD ORG
insert into mtl_system_items_interface
(process_flag, set_process_id, transaction_type,organization_id,segment1,PRIMARY_UOM_CODE,
description
,TEMPLATE_ID --3083 select template_id,template_name,description from MTL_ITEM_TEMPLATES;
--,EXPENSE_ACCOUNT
)
SELECT 1,1,'CREATE',2352 --CHILD_ORG_ID
,TRIM(ITEM_CODE),TRIM(UOM), TRIM(DESCRIPTION)
,3084 --TEMPLATE_ID
FROM XXFIA_ITEM_INTERFACE_NS
WHERE ITEM_CODE <> 'NG000016'
AND CATEGORY_ID IS NOT NULL
--AND ITEM_CODE NOT IN (SELECT SEGMENT1 FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = 2398)
COMMIT;
6.concurrent Program.
-----------------------------------------------Data Fix to assign items to child org-------------------
Update 2398 to your master org
REM datafix after uploading master items_before_assign_to_Other_orgs
REM take backup of mtl_systems_items_b before running this script REM $Header: b19630365.sql 120.0.12010000.1 2014/10/28 12:08:24 liswang noship $
REMREM dbdrv sql ~PROD ~PATH ~FILE none none none sqlplus \
REM dbdrv phase=upg checkfile:~PROD:~PATH:~FILE
REM +======================================================================+
REM | Copyright (c) 1997, 2013 Oracle Corporation |
REM | Redwood Shores, California, USA |
REM | All rights reserved. |
REM +======================================================================+
REM SQL Script File Name:
REM b19630365.sql
REMREM Description:
REM This sql script updates item attributes with
REM R12 default value for all items.
REMREM ======================================================================== set verify off
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK; Update mtl_system_items_b
set CATALOG_STATUS_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND CATALOG_STATUS_FLAG is null;COMMIT;Update mtl_system_items_b
set COLLATERAL_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND COLLATERAL_FLAG is null;COMMIT;Update mtl_system_items_b
set CONTAINER_ITEM_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND CONTAINER_ITEM_FLAG is null;COMMIT;Update mtl_system_items_b
set DOWNLOADABLE_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND DOWNLOADABLE_FLAG is null;COMMIT;Update mtl_system_items_b
set ELECTRONIC_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND ELECTRONIC_FLAG is null;COMMIT;Update mtl_system_items_b
set EVENT_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND EVENT_FLAG is null;COMMIT;Update mtl_system_items_b
set INDIVISIBLE_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND INDIVISIBLE_FLAG is null;COMMIT;Update mtl_system_items_b
set MRP_CALCULATE_ATP_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND MRP_CALCULATE_ATP_FLAG is null;COMMIT;Update mtl_system_items_b
set VEHICLE_ITEM_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND VEHICLE_ITEM_FLAG is null;COMMIT;Update mtl_system_items_b
set RECIPE_ENABLED_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND RECIPE_ENABLED_FLAG is null;COMMIT;Update mtl_system_items_b
set BOM_ENABLED_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND BOM_ENABLED_FLAG is null;COMMIT;Update mtl_system_items_b
set LOT_STATUS_ENABLED = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND LOT_STATUS_ENABLED is null;COMMIT;Update mtl_system_items_b
set SERIAL_STATUS_ENABLED = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND SERIAL_STATUS_ENABLED is null;COMMIT;Update mtl_system_items_b
set LOT_DIVISIBLE_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND LOT_DIVISIBLE_FLAG is null;COMMIT;Update mtl_system_items_b
set LOT_SPLIT_ENABLED = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND LOT_SPLIT_ENABLED is null;COMMIT;Update mtl_system_items_b
set LOT_MERGE_ENABLED = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND LOT_MERGE_ENABLED is null;COMMIT;Update mtl_system_items_b
set LOT_TRANSLATE_ENABLED = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND LOT_TRANSLATE_ENABLED is null;COMMIT;
Update mtl_system_items_b
set BULK_PICKED_FLAG = 'N'
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND BULK_PICKED_FLAG is null;COMMIT;
COMMIT;Update mtl_system_items_b
set EQUIPMENT_TYPE = 2
WHERE ORGANIZATION_ID = 2398 AND ENABLED_FLAG = 'Y' AND EQUIPMENT_TYPE is null;COMMIT;EXIT;
No comments:
Post a Comment