從EXCEL導PRICE LIST到OM單價庫

因應組織變更或年度報價或批次調整後,往往要重新匯入或修改單價,以下SCRIPE就是例項. 先在EXCEL中整理好機型與單價 兩欄 然後在 第二行第三欄 用FUNCTION =CONCATENATE("insert into lsitem values('",A2,"',",B2,");") 將此FUNCTION拉到最後一筆 然後將此第三欄COPY TO TOAD begin delete lsitem; insert into lsitem values('FS1B4055CP-KFA2',34.12); insert into lsitem values('A239001CCP-50-NDB1',1.65); ...... commit; end; 將資料INSERT INTO到臨時TABLE: LSITEM 再將要匯入的PRICE LIST ID找出來,並替換SCRIPT中的LIST_HEADER_ID,其中1174是我的USER_ID. select NAME,LIST_HEADER_ID,creation_date from qp_list_headers where name like '%GUCS 2011%'[@more@]/* select * from LSITEM ---------- begin delete lsitem; insert into lsitem values('FS1B4055CP-KFA2',34.12); insert into lsitem values('A239001CCP-50-NDB1',1.65); ...... commit; end; ------------ commit INSERT INTO LSITEM VALUES('PS139617ECPD',8); =CONCATENATE("insert into lsitem values('",A2,"',",B2,");") select NAME,LIST_HEADER_ID,creation_date from qp_list_headers where name like '%GUCS 2011%' select * from ls_item where item='A663341' */ DECLARE IFHAVE NUMBER; V_LIST_LINE_ID NUMBER; V_LIST_header_ID NUMBER; old_LIST_LINE_ID number; cursor curadd is select a.item,a.price price,b.inventory_item_id ,7427815 LIST_HEADER_ID from lsitem a, mtl_system_items_b b where b.segment1=a.item and b.organization_id=1 -- and 1=2 and a.ITEM IS NOT NULL and b.inventory_item_id not in (select PRODUCT_ATTR_VALUE from qp_pricing_attributes where list_header_id =7427815 ); cursor curupd is select a.item,round(a.price,3) price,b.inventory_item_id , i.LIST_HEADER_ID,i.list_line_id from lsitem a, mtl_system_items_b b, qp_list_headers h, qp_pricing_attributes i where b.segment1=a.item and b.organization_id=1 and a.ITEM IS NOT NULL and h.list_header_id=7427815 and i.list_header_id=h.list_header_id and i.product_attr_value=to_char(b.inventory_item_id); begin for recu in curupd loop update qp_list_lines set operand=recu.price, last_update_date=sysdate, last_updated_by=1174 --start_date_active=to_date('20110101','yyyymmdd') where list_line_id=recu.list_line_id; end loop; for reca in curadd loop SELECT qp_list_lines_s.nextval INTO V_LIST_LINE_ID FROM DUAL; /* select 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 from qp_pricing_attributes WHERE LIST_HEADER_ID=152767 where list_line_id=1886828 */ insert into qp_pricing_attributes a (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,1174,sysdate,1174,1, v_list_line_id,'N','N','ITEM','PRICING_ATTRIBUTE1',to_char(RECa.INVENTORY_ITEM_ID), 'PCS',QP_PRICING_ATTR_GROUP_NO_S.nextval,'C','BETWEEN', RECa.list_header_id,1,4); /* select list_line_id,creation_date,created_by,last_update_date,last_updated_by,last_update_login, list_header_id,list_line_type_code,start_date_active,automatic_flag,modifier_level_code, revision_date,arithmetic_operator,operand,reprice_flag,list_line_no, pricing_phase_id,pricing_group_sequence,incompatibility_grp_code,product_precedence, qualification_ind from qp_list_lines where list_line_id=1886828 select * from qp_list_lines where list_line_id=2498577 */ insert into qp_list_lines (list_line_id,creation_date,created_by,last_update_date,last_updated_by,last_update_login, list_header_id,list_line_type_code,start_date_active,automatic_flag,modifier_level_code, revision_date,arithmetic_operator,operand,reprice_flag,list_line_no, pricing_phase_id,pricing_group_sequence,incompatibility_grp_code,product_precedence, qualification_ind) VALUES (v_list_line_id,sysdate,1174,sysdate,1174,1, RECa.list_header_id,'PLL',SYSDATE,'Y','LINE', SYSDATE,'UNIT_PRICE',reca.price,'Y',V_LIST_LINE_ID, 1,0,'EXCL',220, 4); END LOOP; COMMIT; END ; /* update qp_list_lines set operand=round(operand,3) where list_header_id =7435743 */