用excel檔把item與price匯入oracle erp

1, 先把excel整理成item,price兩欄,此兩欄要是用'值'的方式儲存,且不含小數,如有小數,先變成整數,如要2位小數,則全部*100.=round(price,2)*100

2,把excel另存為.dbf檔

3,用工具把.dbf匯入oracle erp db中的import_price(item varchar2(20),item varchar2(20))臨時table

4,用以下plus sql更新db

[@more@]

declare

ifhave number;
v_list_line_id number;
v_list_header_id number;

cursor cur_into(p_list_header_id in number) is
select b.inventory_item_id ,
round(a.price/100,2) price
from (select distinct item,price from import_price) a,
mtl_system_items_b b
where b.segment1=a.item
and b.organization_id=1
and a.item is not null
and not exists
(select 1 from qp_pricing_attributes a1,qp_list_lines b1
where 1=1
and nvl(b1.end_date_active,sysdate)>=sysdate
and b1.list_line_id=a1.list_line_id
and b1.list_header_id=a1.list_header_id
and a1.list_header_id=p_list_header_id
and a1.product_attr_value=b.inventory_item_id
) ;

cursor cur_update(p_list_header_id in number) is
select b.inventory_item_id ,
round(a.price/100,2) price
from (select distinct item,price from import_price) a,
mtl_system_items_b b
where b.segment1=a.item
and b.organization_id=1
and a.item is not null
and exists
(select 1 from qp_pricing_attributes a1,qp_list_lines b1
where 1=1
and nvl(b1.end_date_active,sysdate)>=sysdate
and b1.list_line_id=a1.list_line_id
and b1.list_header_id=a1.list_header_id
and a1.list_header_id=p_list_header_id
and a1.product_attr_value=b.inventory_item_id
) ;

begin

select list_header_id
into v_list_header_id
from qp_list_headers
where name ='???';

for rec in cur_into(v_list_header_id) loop
select qp_list_lines_s.nextval into v_list_line_id from dual;

insert into qp_pricing_attributes
(pricing_attribute_id,creation_date,created_by,
last_update_date,last_updated_by,last_update_login,
list_line_id,excluder_flag,accumulate_flag,
product_attribute_context,product_attribute,product_attr_value,
product_uom_code,attribute_grouping_no,product_attribute_datatype,
comparison_operator_code,list_header_id,pricing_phase_id,
qualification_ind
)
values
(qp_pricing_attributes_s.nextval,sysdate,-1,
sysdate,-1,-1,
v_list_line_id,'N','N',
'ITEM','PRICING_ATTRIBUTE1',rec.inventory_item_id,
'PCS',qp_pricing_attr_group_no_s.nextval,'C',
'BETWEEN',v_list_header_id,1,
4
);

insert into qp_list_lines
(list_line_id,creation_date,created_by,
last_update_date,last_updated_by,list_header_id,
list_line_type_code,automatic_flag,modifier_level_code,
arithmetic_operator,operand,reprice_flag,
pricing_phase_id,pricing_group_sequence,incompatibility_grp_code,
product_precedence,list_line_no,revision_date,
start_date_active, qualification_ind
)
values
(v_list_line_id,sysdate,-1,
sysdate,-1,v_list_header_id,
'PLL','Y','LINE',
'UNIT_PRICE',rec.price,'Y',
1,0,'EXCL',
220,v_list_line_id,sysdate,
sysdate,4
);

end loop;

for rec in cur_update(v_list_header_id) loop

select list_line_id,counts
into v_list_line_id,ifhave
from (
select a.list_line_id,count(*) counts
from qp_pricing_attributes a,qp_list_lines b
where 1=1
and nvl(b.end_date_active,sysdate)>=sysdate
and b.list_line_id=a.list_line_id
and b.list_header_id=a.list_header_id
and a.product_attr_value=rec.inventory_item_id
and a.list_header_id=v_list_header_id
group by a.list_line_id
)
where rownum=1;

if ifhave>0 then

update qp_list_lines
set operand=rec.price
where 1=1
and list_line_id=v_list_line_id
and list_header_id=v_list_header_id;

end if;

end loop;
end;

/*

commit;

*/