Inventory Items COGS and Sales Account Update:
Updating COGS account and Sales Account for existing items.
1. Creation of Staging Table:
create table XX_ITEM_ACNT_Update_STG(Item varchar2(1000), COGS varchar2(300), sales Varchar2(300),status varchar2(50),error_msg varchar2(1000));
2. Loading given data into Staging Table:
You can create Control File and save yout data file as .CSV(steps from 2.1 to 2.4) or you can directly load data using SQL Developer(Steps from 2.5 to 2.10)
2.1 If it is SQLLDR(control file):
Save following code in a file with extension .CTL. Example: Control.CTL
OPTIONS (SKIP=1)
LOAD DATA
Characterset UTF8
Infile 'Data_file.csv'
Truncate
into table XX_ITEMS_STG
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
( Item "ltrim(rtrim(:Item))"
,COGS "ltrim(rtrim(:COGS))"
,Sales "ltrim(rtrim(trim(CHR(13) from trim(CHR(10) from :Sales))))"
)
2.2 Create data file with Name, Data_file.csv
2.3 Place the two files Data_File.csv and Control.CTL in UNIX
2.4 Run the command to load data
sqlldr userid=<user_naem>/<password> control=<control_file_name(with extn)>
Example: sqlldr userid=apps/apps control=Control.CTL
Note: Make sure that you have write permissions in the directory where you are xexecuting this command
(Ignore(from 2.4 to 2.15) if already performed 2.1 to 2.4)If it is using SQL Developer,
2.5. From Connections, search for the staging table and double click on it.
2.6 Click on Data tab
2.7 From Actions Drop Down(extreme Right) , Select Import Data
2.8 Select the Excel File from which we need to load data
2.9 Click on Next, Next with Default Options
2.10 Select the columns to be loaded
2.11 Map the columns between Excel file and Table
2.12 Finish
3. Validate the Data:
Declare
Cursor Item_Cur
is
Select Item From XX_ITEM_ACNT_Update_STG
Where 1=1
And Status = 'N';
L_Cogs Number;
L_Sales Number;
l_error VARCHAR2(2000);
l_status1 VARCHAR2(5);
l_status2 VARCHAR2(5);
l_status3 VARCHAR2(5);
l_item_id NUMBER;
begin
update XX_ITEM_ACNT_Update_STG
set status = 'N';
Commit;
For Item_Rec In Item_Cur
Loop
L_Cogs:= Null;
L_Sales := Null;
l_error := NULL;
l_status1 := NULL;
l_status2 := NULL;
l_status3 := NULL;
l_item_id := NULL;
begin
select disctinct inventory_item_id
into l_item_id
from inv.mtl_system_items_b
where 1=1
and segment1 = item_rec.item;
exception when others then
l_status3 := 'VE';
l_error := 'Not Valid Item';
end;
begin
Select Gcc_Cogs.Code_Combination_Id,stg.status
Into L_Cogs,l_status1
From XX_ITEM_ACNT_Update_STGStg,
Gl_Code_Combinations Gcc_Cogs
Where 1=1
And Stg.Cogs = Gcc_Cogs.Segment1 ||'.' ||Gcc_Cogs.Segment2 ||'.' ||Gcc_Cogs.Segment3
||'.' ||Gcc_Cogs.Segment4 ||'.' ||Gcc_Cogs.Segment5
and stg.item= item_rec.item;
Exception When Others Then
l_error := ' || No COGS Account Found ||';
l_status1:= 'VE';
end;
begin
Select Gcc_sales.Code_Combination_Id, stg.status
Into L_sales,l_status2
From XX_ITEM_ACNT_Update_STGStg,
Gl_Code_Combinations Gcc_sales
Where 1=1
And Stg.sales = GCC_SALES.SEGMENT1 ||'.' ||GCC_sales.SEGMENT2 ||'.' ||GCC_sales.SEGMENT3
||'.' ||Gcc_Sales.Segment4 ||'.' ||Gcc_Sales.Segment5
and stg.item = item_rec.item;
Exception When Others Then
l_error := l_error||' No Sales Account Found';
l_status2:= 'VE';
End;
if(l_status1 = 'VE' or l_status2 = 'VE' or l_status3 = 'VE') then
Update XX_ITEM_ACNT_Update_STG
SET ERROR_MSG = L_ERROR,
status = 'VE'
Where 1=1
and item = item_rec.item;
end if;
End Loop;
Commit;
Exception When Others Then
dbms_output.put_line('Error in the procedure '|| SQLERRM);
end;
4. Procedure for inserting data into Interface table and submitting Import Items Program
create or replace procedure xx_items_account_update
is
cursor items_cur
is
select msib.inventory_item_id, msib.segment1, msib.organization_id, GCC_COGS.CODE_COMBINATION_ID COGS,
gcc_sales.code_combination_id sales
from XX_ITEM_ACNT_Update_STG STG,
apps.GL_CODE_COMBINATIONS GCC_COGS,
apps.GL_CODE_COMBINATIONS GCC_SALES,
mtl_system_items_b msib
where 1=1
and STG.COGS = GCC_COGS.SEGMENT1 ||'.' ||GCC_COGS.SEGMENT2 ||'.' ||GCC_COGS.SEGMENT3
||'.' ||GCC_COGS.SEGMENT4 ||'.' ||GCC_COGS.SEGMENT5
and STG.SALES = GCC_SALES.SEGMENT1 ||'.' ||GCC_sales.SEGMENT2 ||'.' ||GCC_sales.SEGMENT3
||'.' ||GCC_SALES.SEGMENT4 ||'.' ||GCC_SALES.SEGMENT5
and MSIB.SEGMENT1 = STG.ITEM
and msib.organization_id = 2149
and stg.status = 'N';
l_user_id NUMBER;
l_application_id NUMBER;
l_resp_id NUMBER;
l_set_process_id NUMBER := 275336;
lb_program_completed BOOLEAN;
ln_req_id NUMBER;
lc_rphase VARCHAR2 (60);
lc_rstatus VARCHAR2 (60);
lc_dphase VARCHAR2 (60);
lc_dstatus VARCHAR2 (60);
ls_message VARCHAR2 (240);
begin
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'CONVERSION';
-- Get the application_id and responsibility_id
select APPLICATION_ID, RESPONSIBILITY_ID
INTO l_application_id, l_resp_id
from apps.FND_RESPONSIBILITY_TL tl
where RESPONSIBILITY_NAME = 'Inventory'
and language = 'US';
fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
for items_rec in items_cur
loop
begin
INSERT INTO apps.mtl_system_items_interface
(inventory_item_id,
organization_id,
last_update_date,
last_updated_by,
transaction_type,
process_flag,
set_process_id,
COST_OF_SALES_ACCOUNT,
SALES_ACCOUNT
)
VALUES (items_rec.inventory_item_id,
2149, -- Organization_ID
SYSDATE,
l_user_id,
'UPDATE',
1,
l_set_process_id,
items_rec.cogs,
items_rec.sales
);
exception when others then
DBMS_OUTPUT.PUT_LINE('Error while inserting records into Interface Table for Item: ' ||items_rec.segment1||' and the error is '||SQLERRM);
end;
commit;
end loop;
ln_req_id := fnd_request.submit_request ('INV', --application
'INCOIN', --program Item Import Short Text
NULL,
--'Item Conversion', --Description
NULL, --Start Time
false, --Sub Request
2149, --Organization_id
2, --All Organizations
1, --Validate Items
1, --Process items
1, --Delete Processed Rows
l_set_process_id, --Process Set
2 --Create or update items
);
COMMIT;
lb_program_completed :=
fnd_concurrent.wait_for_request (ln_req_id,
60,
99999,
lc_rphase,
lc_rstatus,
lc_dphase,
lc_dstatus,
ls_message
);
IF NOT ((lc_dphase = 'COMPLETE') AND (lc_dstatus = 'NORMAL'))
THEN
DBMS_OUTPUT.put_line ('failed :' || TO_CHAR (ln_req_id));
ELSE
DBMS_OUTPUT.put_line ('completed :' || TO_CHAR (ln_req_id));
END IF;
begin
UPDATE XX_ITEM_ACNT_Update_STG
SET status = 'FI'
WHERE item IN (
SELECT segment1
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND last_updated_by = l_user_id
--AND last_update_date = SYSDATE
)
AND status = 'N';
COMMIT;
UPDATE XX_ITEM_ACNT_Update_STG stg
SET status = 'S'
WHERE 1 = 1
AND status = 'N'
AND stg.item IN (SELECT segment1
FROM apps.mtl_system_items_b
WHERE segment1 = item
and organization_id = 2149
and last_updated_by = l_user_id);
COMMIT;
exception when others then
DBMS_OUTPUT.put_line('Error while updating staging table: '||SQLERRM );
end;
exception when others then
DBMS_OUTPUT.put_line('Unexpected Exception in Procedure: '||SQLERRM );
end XX_items_account_update;
Note: Change Organization_Id(2149) with your Organization_ID
5. Invoking the Procedure:
begin
xx_items_account_update;
end;
/
6. Queries to verify the updated data:
SELECT * FROM XX_ITEM_ACNT_Update_STG
WHERE 1=1
and status = 'N';
SELECT COUNT(*), STATUS FROM XX_ITEM_ACNT_Update_STG
WHERE 1=1
GROUP BY STATUS;
select count(*) from MTL_SYSTEM_ITEMS_B
where 1=1
and organization_id = &Organization_ID;
AND TRUNC(LAST_UPDATE_DATE) = TRUNC(SYSDATE);
select * from MTL_SYSTEM_ITEMS_INTERFACE
where 1=1
AND SET_PROCESS_ID = 275336;
Updating COGS account and Sales Account for existing items.
1. Creation of Staging Table:
create table XX_ITEM_ACNT_Update_STG(Item varchar2(1000), COGS varchar2(300), sales Varchar2(300),status varchar2(50),error_msg varchar2(1000));
2. Loading given data into Staging Table:
You can create Control File and save yout data file as .CSV(steps from 2.1 to 2.4) or you can directly load data using SQL Developer(Steps from 2.5 to 2.10)
2.1 If it is SQLLDR(control file):
Save following code in a file with extension .CTL. Example: Control.CTL
OPTIONS (SKIP=1)
LOAD DATA
Characterset UTF8
Infile 'Data_file.csv'
Truncate
into table XX_ITEMS_STG
fields terminated by ","
optionally enclosed by '"'
TRAILING NULLCOLS
( Item "ltrim(rtrim(:Item))"
,COGS "ltrim(rtrim(:COGS))"
,Sales "ltrim(rtrim(trim(CHR(13) from trim(CHR(10) from :Sales))))"
)
2.2 Create data file with Name, Data_file.csv
2.3 Place the two files Data_File.csv and Control.CTL in UNIX
2.4 Run the command to load data
sqlldr userid=<user_naem>/<password> control=<control_file_name(with extn)>
Example: sqlldr userid=apps/apps control=Control.CTL
Note: Make sure that you have write permissions in the directory where you are xexecuting this command
(Ignore(from 2.4 to 2.15) if already performed 2.1 to 2.4)If it is using SQL Developer,
2.5. From Connections, search for the staging table and double click on it.
2.6 Click on Data tab
2.7 From Actions Drop Down(extreme Right) , Select Import Data
2.8 Select the Excel File from which we need to load data
2.9 Click on Next, Next with Default Options
2.10 Select the columns to be loaded
2.11 Map the columns between Excel file and Table
2.12 Finish
3. Validate the Data:
Declare
Cursor Item_Cur
is
Select Item From XX_ITEM_ACNT_Update_STG
Where 1=1
And Status = 'N';
L_Cogs Number;
L_Sales Number;
l_error VARCHAR2(2000);
l_status1 VARCHAR2(5);
l_status2 VARCHAR2(5);
l_status3 VARCHAR2(5);
l_item_id NUMBER;
begin
update XX_ITEM_ACNT_Update_STG
set status = 'N';
Commit;
For Item_Rec In Item_Cur
Loop
L_Cogs:= Null;
L_Sales := Null;
l_error := NULL;
l_status1 := NULL;
l_status2 := NULL;
l_status3 := NULL;
l_item_id := NULL;
begin
select disctinct inventory_item_id
into l_item_id
from inv.mtl_system_items_b
where 1=1
and segment1 = item_rec.item;
exception when others then
l_status3 := 'VE';
l_error := 'Not Valid Item';
end;
begin
Select Gcc_Cogs.Code_Combination_Id,stg.status
Into L_Cogs,l_status1
From XX_ITEM_ACNT_Update_STGStg,
Gl_Code_Combinations Gcc_Cogs
Where 1=1
And Stg.Cogs = Gcc_Cogs.Segment1 ||'.' ||Gcc_Cogs.Segment2 ||'.' ||Gcc_Cogs.Segment3
||'.' ||Gcc_Cogs.Segment4 ||'.' ||Gcc_Cogs.Segment5
and stg.item= item_rec.item;
Exception When Others Then
l_error := ' || No COGS Account Found ||';
l_status1:= 'VE';
end;
begin
Select Gcc_sales.Code_Combination_Id, stg.status
Into L_sales,l_status2
From XX_ITEM_ACNT_Update_STGStg,
Gl_Code_Combinations Gcc_sales
Where 1=1
And Stg.sales = GCC_SALES.SEGMENT1 ||'.' ||GCC_sales.SEGMENT2 ||'.' ||GCC_sales.SEGMENT3
||'.' ||Gcc_Sales.Segment4 ||'.' ||Gcc_Sales.Segment5
and stg.item = item_rec.item;
Exception When Others Then
l_error := l_error||' No Sales Account Found';
l_status2:= 'VE';
End;
if(l_status1 = 'VE' or l_status2 = 'VE' or l_status3 = 'VE') then
Update XX_ITEM_ACNT_Update_STG
SET ERROR_MSG = L_ERROR,
status = 'VE'
Where 1=1
and item = item_rec.item;
end if;
End Loop;
Commit;
Exception When Others Then
dbms_output.put_line('Error in the procedure '|| SQLERRM);
end;
4. Procedure for inserting data into Interface table and submitting Import Items Program
create or replace procedure xx_items_account_update
is
cursor items_cur
is
select msib.inventory_item_id, msib.segment1, msib.organization_id, GCC_COGS.CODE_COMBINATION_ID COGS,
gcc_sales.code_combination_id sales
from XX_ITEM_ACNT_Update_STG STG,
apps.GL_CODE_COMBINATIONS GCC_COGS,
apps.GL_CODE_COMBINATIONS GCC_SALES,
mtl_system_items_b msib
where 1=1
and STG.COGS = GCC_COGS.SEGMENT1 ||'.' ||GCC_COGS.SEGMENT2 ||'.' ||GCC_COGS.SEGMENT3
||'.' ||GCC_COGS.SEGMENT4 ||'.' ||GCC_COGS.SEGMENT5
and STG.SALES = GCC_SALES.SEGMENT1 ||'.' ||GCC_sales.SEGMENT2 ||'.' ||GCC_sales.SEGMENT3
||'.' ||GCC_SALES.SEGMENT4 ||'.' ||GCC_SALES.SEGMENT5
and MSIB.SEGMENT1 = STG.ITEM
and msib.organization_id = 2149
and stg.status = 'N';
l_user_id NUMBER;
l_application_id NUMBER;
l_resp_id NUMBER;
l_set_process_id NUMBER := 275336;
lb_program_completed BOOLEAN;
ln_req_id NUMBER;
lc_rphase VARCHAR2 (60);
lc_rstatus VARCHAR2 (60);
lc_dphase VARCHAR2 (60);
lc_dstatus VARCHAR2 (60);
ls_message VARCHAR2 (240);
begin
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'CONVERSION';
-- Get the application_id and responsibility_id
select APPLICATION_ID, RESPONSIBILITY_ID
INTO l_application_id, l_resp_id
from apps.FND_RESPONSIBILITY_TL tl
where RESPONSIBILITY_NAME = 'Inventory'
and language = 'US';
fnd_global.apps_initialize (l_user_id, l_resp_id, l_application_id);
for items_rec in items_cur
loop
begin
INSERT INTO apps.mtl_system_items_interface
(inventory_item_id,
organization_id,
last_update_date,
last_updated_by,
transaction_type,
process_flag,
set_process_id,
COST_OF_SALES_ACCOUNT,
SALES_ACCOUNT
)
VALUES (items_rec.inventory_item_id,
2149, -- Organization_ID
SYSDATE,
l_user_id,
'UPDATE',
1,
l_set_process_id,
items_rec.cogs,
items_rec.sales
);
exception when others then
DBMS_OUTPUT.PUT_LINE('Error while inserting records into Interface Table for Item: ' ||items_rec.segment1||' and the error is '||SQLERRM);
end;
commit;
end loop;
ln_req_id := fnd_request.submit_request ('INV', --application
'INCOIN', --program Item Import Short Text
NULL,
--'Item Conversion', --Description
NULL, --Start Time
false, --Sub Request
2149, --Organization_id
2, --All Organizations
1, --Validate Items
1, --Process items
1, --Delete Processed Rows
l_set_process_id, --Process Set
2 --Create or update items
);
COMMIT;
lb_program_completed :=
fnd_concurrent.wait_for_request (ln_req_id,
60,
99999,
lc_rphase,
lc_rstatus,
lc_dphase,
lc_dstatus,
ls_message
);
IF NOT ((lc_dphase = 'COMPLETE') AND (lc_dstatus = 'NORMAL'))
THEN
DBMS_OUTPUT.put_line ('failed :' || TO_CHAR (ln_req_id));
ELSE
DBMS_OUTPUT.put_line ('completed :' || TO_CHAR (ln_req_id));
END IF;
begin
UPDATE XX_ITEM_ACNT_Update_STG
SET status = 'FI'
WHERE item IN (
SELECT segment1
FROM mtl_system_items_interface
WHERE set_process_id = l_set_process_id
AND last_updated_by = l_user_id
--AND last_update_date = SYSDATE
)
AND status = 'N';
COMMIT;
UPDATE XX_ITEM_ACNT_Update_STG stg
SET status = 'S'
WHERE 1 = 1
AND status = 'N'
AND stg.item IN (SELECT segment1
FROM apps.mtl_system_items_b
WHERE segment1 = item
and organization_id = 2149
and last_updated_by = l_user_id);
COMMIT;
exception when others then
DBMS_OUTPUT.put_line('Error while updating staging table: '||SQLERRM );
end;
exception when others then
DBMS_OUTPUT.put_line('Unexpected Exception in Procedure: '||SQLERRM );
end XX_items_account_update;
Note: Change Organization_Id(2149) with your Organization_ID
5. Invoking the Procedure:
begin
xx_items_account_update;
end;
/
6. Queries to verify the updated data:
SELECT * FROM XX_ITEM_ACNT_Update_STG
WHERE 1=1
and status = 'N';
SELECT COUNT(*), STATUS FROM XX_ITEM_ACNT_Update_STG
WHERE 1=1
GROUP BY STATUS;
select count(*) from MTL_SYSTEM_ITEMS_B
where 1=1
and organization_id = &Organization_ID;
AND TRUNC(LAST_UPDATE_DATE) = TRUNC(SYSDATE);
select * from MTL_SYSTEM_ITEMS_INTERFACE
where 1=1
AND SET_PROCESS_ID = 275336;
No comments:
Post a Comment