财务线下在excel核对、修改后的，物料成本及收入COA，批量修改脚本。
create global temporary table Temp_CCID( vItemID varchar(100), --物料编码 vCostCOA varchar(100), --成本COA vCostCCID varchar(100), --成本CCID vSalesCOA varchar(100), --收入COA vSalesCCID varchar(100) --收入CCID ) on commit preserve rows; select rowid,t.vitemid,t.vcostcoa,t.vsalescoa from Temp_CCID t --将excel数据导入到该临时表中 DECLARE l_ccid1 NUMBER; --成本 l_ccid2 NUMBER; --收入 cursor cur1 is select t.vitemid,t.vcostcoa,t.vsalescoa from Temp_CCID t; c_item Temp_CCID.vitemid%type; c_cost Temp_CCID.vCostCOA%type; c_sale Temp_CCID.vSalesCOA%type; BEGIN open cur1; fetch cur1 into c_item, c_cost, c_sale; while cur1%found loop l_ccid1 := APPS.fnd_flex_ext.get_ccid( --查找成本，科目组合对应的CCID，若无则新建 application_short_name => 'SQLGL' ,key_flex_code => 'GL#' ,structure_number => structure_number --查询structure_number ,validation_date => to_char(SYSDATE ,'yyyymmdd') ,concatenated_segments => c_cost); l_ccid2 := APPS.fnd_flex_ext.get_ccid( --查找收入，科目组合对应的CCID，若无则新建 application_short_name => 'SQLGL' ,key_flex_code => 'GL#' ,structure_number => structure_number --查询structure_number ,validation_date => to_char(SYSDATE ,'yyyymmdd') ,concatenated_segments => c_sale); dbms_output.put_line(c_item||' '||c_cost||' '||l_ccid1||' '||c_sale||' '||l_ccid2); --输出查找结果 update apps.mtl_system_items_b msi set --替换成本及收入 msi.COST_OF_SALES_ACCOUNT=l_ccid1, msi.SALES_ACCOUNT=l_ccid2 where msi.SEGMENT1=c_item AND msi.ORGANIZATION_ID=94; fetch cur1 into c_item, c_cost, c_sale; end loop; close cur1; END; --------------删除临时表------------ truncate table Temp_CCID; drop table Temp_CCID; /* SELECT * fROM APPS.gl_sets_of_books --查询structure_number */
