INV - New Item Creation

CREATE TABLE APPS.XX_ITEM_INTERFACE_STG_DEBUG ( SEGMENT1 VARCHAR2(40 BYTE), DESCRIPTION VARCHAR2(240 BYTE), PRIMARY_UOM_CODE VARCHAR2(3 BYTE), PROCESS_QUALITY_ENABLED_FLAG VARCHAR2(1 BYTE), PROCESS_COSTING_ENABLED_FLAG VARCHAR2(1 BYTE), RECIPE_ENABLED_FLAG VARCHAR2(1 BYTE), PROCESS_EXECUTION_ENABLED_FLAG VARCHAR2(1 BYTE), I_STSTAUS VARCHAR2(500 BYTE), LONG_DESC VARCHAR2(4000 BYTE) ) ------------------------------------------------------------

 

 

 Insert into XX_ITEM_INTERFACE_STG_DEBUG select item_id, item_name, 'DZ' unit , 'Y','Y','Y','Y', null,null from item_master@LSALES a, rfl_fg_item_list b, ITEM_DIST_UNITS@LSALES ut where b.ITEM_CODE=a.item_id and a.ITEM_D_UNITS_ID=ut.ITEM_D_UNITS_ID and upper(ITEM_D_UNITS_NAME)='DOZEN' --872038 and not exists (select * from mtl_system_items_b itm where a.item_id= itm.segment1 and itm.ORGANIZATION_ID=102) ------------------------------------------------------------- -- /* Cursor Declaration ***/ declare cursor c1 is --select * from XX_ITEM_INTERFACE_STG_DEBUG where SEGMENT1 in ('911461','911462'); --xx_item_interface_stg1; select a.* from XX_ITEM_INTERFACE_STG_DEBUG a where -- a.SEGMENT1 in ('911461','911462') and a.SEGMENT1 not in (select segment1 from mtl_system_items_b i where organization_id=102 and a.SEGMENT1= i.SEGMENT1 ) -- and a.SEGMENT1 in ('911465','911466','911467') ; --select ATTRIBUTE1, I.* from mtl_system_items_b i where organization_id=102 and i.SEGMENT1 in ('32144') /* Variable Declaration **/ l_org_id number(3); l_uom varchar2(15); l_suom varchar2(15); l_item_type varchar2(60); l_desc varchar2(60); l_temp_id varchar2(3); l_sale_ac number(16); l_exp_ac number(16); l_cost_sale_ac number(16); l_item_number varchar2(100); l_flag varchar2(4) default'A'; l_msg varchar2(200); begin --delete from MTL_SYSTEM_ITEMS_INTERFACE; --commit; for x1 in c1 loop /* Validation For Organization */ -- begin -- select organization_id into l_org_id -- from ORG_ORGANIZATION_DEFINITIONS -- where organization_code = x1.organization_code; -- exception -- when others then -- l_flag :='E'; -- l_msg :='Item Interface'||'Organization Id'||l_org_id||' not in system'; -- fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); -- end; /* Validation for Item Number */ if x1.SEGMENT1 is null then l_flag :='E'; l_msg :='Item number should not be NULL'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured--'||l_msg); end if; /* Validation for Description */ if x1.DESCRIPTION is null then l_flag :='E'; l_msg :='Item description should not be NULL'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg|| x1.SEGMENT1); end if; /* Validation for Unit Of Measure */ if x1.PRIMARY_UOM_CODE is not null then begin select UOM_CODE into l_uom from mtl_units_of_measure where UOM_CODE =x1.PRIMARY_UOM_CODE; exception when others then l_flag :='E'; l_msg := x1.SEGMENT1||'---'||l_uom ||'UOM is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; else l_msg :='Primary UOM should not be NULL '; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end if; begin select COST_OF_SALES_ACCOUNT into l_cost_sale_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Cost of Sales Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Cost of Sales Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; begin select SALES_ACCOUNT into l_sale_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Sales Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Sales Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; begin select EXPENSE_ACCOUNT into l_exp_ac from MTL_PARAMETERS where ORGANIZATION_ID = 102; exception when no_data_found then l_flag :='E'; l_msg :='Expence Account should not be Null'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); when others then l_flag :='E'; l_msg :='Expence Account is not Valid'; fnd_file.PUT_LINE(fnd_file.log,'Error Occured'||l_msg); end; if l_flag!='E' then insert into MTL_SYSTEM_ITEMS_INTERFACE ( ORGANIZATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, DESCRIPTION, SEGMENT1, PRIMARY_UOM_CODE, TEMPLATE_ID, SUMMARY_FLAG, ENABLED_FLAG, SALES_ACCOUNT, COST_OF_SALES_ACCOUNT, EXPENSE_ACCOUNT, PROCESS_QUALITY_ENABLED_FLAG, PROCESS_COSTING_ENABLED_FLAG, RECIPE_ENABLED_FLAG, PROCESS_EXECUTION_ENABLED_FLAG, RESTRICT_SUBINVENTORIES_CODE, RESTRICT_LOCATORS_CODE , LOCATION_CONTROL_CODE , SET_PROCESS_ID, PROCESS_FLAG, TRANSACTION_TYPE/*, ATTRIBUTE1, -- ou ATTRIBUTE25, -- TF ATTRIBUTE26, -- unload ATTRIBUTE27, -- load ATTRIBUTE28, -- DP ATTRIBUTE29, -- TP ATTRIBUTE30 -- MRP*/ ) values( 102, sysdate, fnd_global.USER_ID, sysdate, fnd_global.USER_ID, fnd_global.LOGIN_ID, x1.DESCRIPTION, x1.SEGMENT1, l_uom, -- l_item_type, -- 1, -- 1 for FG item, 2 for Purchase 1, 'Y', 'Y', l_sale_ac, l_cost_sale_ac, l_exp_ac, x1.PROCESS_QUALITY_ENABLED_FLAG, x1.PROCESS_COSTING_ENABLED_FLAG, x1.RECIPE_ENABLED_FLAG, x1.PROCESS_EXECUTION_ENABLED_FLAG, 1, 1, 2, 2, 1, 'CREATE'/*, x1.ATTRIBUTE1, x1.ATTRIBUTE25, -- TF x1.ATTRIBUTE29, -- unload x1.ATTRIBUTE30, -- load x1.ATTRIBUTE27, -- DP x1.ATTRIBUTE28, -- TP x1.ATTRIBUTE26 -- MRP*/ ); -- MTL_INTERFACE_ERRORS -- update XX_ITEM_INTERFACE_STG_DEBUG set I_STATUSES='OK' where SEGMENT1= x1.SEGMENT1; -- commit; else update XX_ITEM_INTERFACE_STG_DEBUG set I_STSTAUS=l_msg where SEGMENT1= x1.SEGMENT1; commit; end if; end loop; commit; end; / ------------------------------------------------------------