Search This Blog

Thursday, October 13, 2022

Item Creation with catalog, templates apps R12

--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