Inventory Item VAT Assigment Program

CREATE OR REPLACE PROCEDURE APPS.XXATFL_INV_ITEM_VAT_CONV_P(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER,P_TEMPLATE_id NUMBER) IS


Cursor lcur_valid_org IS

       Select INVENTORY_ORGANIZATION_ID org_id from HR_LOCATIONS_V hlv
        Where hlv.INACTIVE_DATE is null
        and hlv.INVENTORY_ORGANIZATION_ID is not null
        and not exists (Select 'x' from JAI_TEMPL_ORG_REGNS jtor
        where jtor.organization_id=hlv.inventory_organization_id
        and jtor.template_id=p_template_id);

Cursor lcur_valid_items (ln_org_id  NUMBER, ln_temp_id NUMBER) IS
      Select jtor.templ_org_regns_id,jtor.template_id,misb.inventory_item_id
        from jai_templ_org_regns jtor, mtl_system_items_b misb, hr_locations_v hlv
        where jtor.organization_id=misb.organization_id
        and jtor.organization_id = hlv.inventory_organization_id
        and hlv.inactive_date is null
        and hlv.inventory_organization_id is not null
        and hlv.inventory_organization_id=ln_org_id
        and jtor.template_id=ln_temp_id
        and misb.segment1 in (select item_no from ic_item_mst where INACTIVE_IND=0);
       --order by 1,2,3,4

ln_user        NUMBER:=fnd_profile.value('USER_ID');
Begin
For i in lcur_valid_org loop
Exit when lcur_valid_org%notfound;
 Begin
 INSERT INTO JAI_TEMPL_ORG_REGNS
   (
   TEMPL_ORG_REGNS_ID,                  
   TEMPLATE_ID       ,
   ORGANIZATION_ID    ,                
   CREATION_DATE     ,
   CREATED_BY         ,
   LAST_UPDATE_DATE   ,
   LAST_UPDATE_LOGIN  ,
   LAST_UPDATED_BY
   )
  VALUES
   (
   JAI_TEMPL_ORG_REGNS_S.nextval,
   p_template_id,
   i.org_id,
   sysdate,
   ln_user,
   sysdate,
   APPS.FND_GLOBAL.LOGIN_ID,
   ln_user
   );
Commit;
Exception
  When Others then
    fnd_file.put_line( fnd_file.output,' Error While Inserting into JAI_TEMPL_ORG_REGNS table '||'Template Id :'||p_template_id||'Inv Org Id'||i.org_id ||SQLERRM );
  End;
FOR j in lcur_valid_items (i.org_id,p_template_id) loop
  Begin
  INSERT INTO JAI_TEMPL_ITM_REGNS
       (
        TEMPL_ITM_REGNS_ID,
        TEMPL_ORG_REGNS_ID,
        INVENTORY_ITEM_ID,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,
        LAST_UPDATED_BY
       )
        VALUES
       (jai_templ_itm_regns_s.nextval,
        j.templ_org_regns_id,
        j.inventory_item_id,
        sysdate,
        ln_user,
        sysdate,
        APPS.FND_GLOBAL.LOGIN_ID,
        ln_user);
    Commit;
  Exception
  When Others then
    fnd_file.put_line( fnd_file.output,' Error While Inserting into JAI_TEMPL_ITM_REGNS table : '||'Templ Org Regns Id: '||j.templ_org_regns_id||'Item Id: '||j.inventory_item_id|| SQLERRM );
  End;
End loop;
End loop;
--- Deleting the records for start with 6 series items as per requirement of User
DELETE FROM JAI_TEMPL_ITM_REGNS jtir
        where
        exists
        (select msib.inventory_item_id from mtl_system_items_b msib,ic_item_mst iim
         where msib.organization_id=23
         and Iim.item_no=msib.segment1
         AND msib.inventory_item_id=jtir.inventory_item_id
         and msib.segment1 like '6%'
         and iim.INACTIVE_IND=0);
     commit;
     -----

   
End;