修正代码库中的中类名称

select * from prdt where idx2 is null

select * from indx

update a set a.idx2=b.name from prdt a,indx b where a.idx1=b.idx_no

单价导入

SELECT * FROM UP_DEF

SELECT * FROM MATERAIL_PRICES WHERE OUT_ERP IS NULL

---查询单价表中的物料异常，（物料代码是否代码库中存在）（供应商是否存在cust）

select * from materail_prices where vendor_id not in (select cus_no from cust)

select * from materail_prices where mater_id not in (select prd_no from prdt)

--修正单价中的物料名称/规格/单位

UPDATE A SET A.MATER_NAME=b.NAME from MATERAIL_PRICES a,prdt b where a.mater_id=b.prd_no

UPDATE A SET A.MATER_SPEC=b.SPC from MATERAIL_PRICES a,prdt b where a.mater_id=b.prd_no

UPDATE A SET A.UNIT=b.UT from MATERAIL_PRICES a,prdt b where a.mater_id=b.prd_no

修正供应商对应的资料

UPDATE A SET A.taxrate=b.rto_tax from MATERAIL_PRICES a,cust b where a.vendor_id=b.cus_no

UPDATE A SET A.currency=b.cur from MATERAIL_PRICES a,cust b where a.vendor_id=b.cus_no

UPDATE A SET A.payment=b.payment from MATERAIL_PRICES a,cust b where a.vendor_id=b.cus_no

UPDATE A SET A.vendor_name=b.name from MATERAIL_PRICES a,cust b where a.vendor_id=b.cus_no

--修改单价导入资料

update materail_prices set examine_okno='ok',examineman='导入',examine_date='2010-03-27 00:00:00',

confirm_okno='ok',confirmman='导入',confirm_date='2010-03-27 00:00:00',

confirm_okno2='ok',confirmman2='导入',confirm_date2='2010-03-27 00:00:00',out_erp='Y'

---导入到up_def表中(币种不能为空)

INSERT INTO UP_DEF (PRICE_ID,CUS_NO,CUR_ID,PRD_NO,PRD_MARK,BZ_KND,KND,QTY,S_DD,UP,TAX_RTO,UNIT)

SELECT KIND,VENDOR_ID,CURRENCY,MATER_ID,'','','','0',FILL_DATE,MATER_PRICE,TAXRATE,'1'

FROM MATERAIL_PRICES

WHERE OUT_ERP IS NULL

---

----用单价库中的供应商资料改物料代码库中的主供应

update a set a.sup1=b.vendor_id from prdt a,materail_prices b where a.prd_no=b.mater_id

整理代码还无单价的

SELECT * FROM prdt where  prd_no not in (select mater_id from Materail_prices) and knd='1'