Oracle EBS R12 通过供应商接口导入供应商

1.获取org_id,set_of_books_id,chart_of_accounts_id

  Select * from org_organization_definitions;里面有


1.create table supplier_temp(suppliername varchar2(100),

                           num number,

                           country varchar2(100),

                           position varchar2(100),

                           adress varchar2(100),

                           bak1 varchar2(100),

                           bak2 varchar2(100));

                          

                                                 

2.select * from supplier_temp

for update;


3.把excel数据导入临时表中


4.记得跑TRUNCATE TABLE Ap.Ap_Suppliers_Int;

declare

begin

for item in (select * from supplier_temp) loop

    insert into Ap_Suppliers_Int (vendor_interface_id,vendor_name,Segment1)

       values (1,item.suppliername,item.num);

   

end loop;

end;


5.检查select * from Ap_Suppliers_Int;如果没问题就commit


6.运行请求:供应商开放接口导入

6.记得跑TRUNCATETABLE Ap.Ap_Supplier_Sites_Int;    --//[operating_unit]这玩意不一定一样

declare

vendorid number;

ccid1 number;

ccid2 number;

begin

for item in (select * from supplier_temp) loop

    select vendor_id into vendorid from po_vendors v where v.VENDOR_NAME=item.suppliername;

    select FND_FLEX_EXT.get_ccid('SQLGL','GL#',[chart_of_accounts_id],to_char(SYSDATE, 'YYYY-MM-DD'),item.bak1) into ccid1 from dual;

    select FND_FLEX_EXT.get_ccid('SQLGL','GL#',[chart_of_accounts_id],to_char(SYSDATE, 'YYYY-MM-DD'),item.bak2) into ccid2 from dual;

    insert into AP_SUPPLIER_SITES_INT(Vendor_Site_Interface_Id,vendor_id,country,address_line1, vendor_site_code, pay_site_flag, purchasing_site_flag,org_id,accts_pay_code_combination_id ,Prepay_Code_Combination_Id)

    Values (1,vendorid,item.country,item.adress,item.position,'Y','Y', [operating_unit],ccid1,ccid2);

end loop;

end;


7.检查select * from Ap_Supplier_Sites_Int;如果没问题就commit


8.运行请求：供应商站点开放接口导入


8.drop supplier_temp;