Search This Blog

Wednesday, October 9, 2013

Script to Update COGS and Sales Account for Inventory Items- Interface Approach

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;

No comments:

Post a Comment