导会员销售数据:

--------------------U_SALE_C------------------
create table U_SALE_C
(
  saleno         VARCHAR2(24) not null,
  row_id         NUMBER not null,
  busno          VARCHAR2(10) not null,
  accdate        DATE not null,
  wareid         VARCHAR2(13) not null,
  batchno        VARCHAR2(30) not null,
  idno           VARCHAR2(12) not null,
  stallno        VARCHAR2(10) not null,
  makeno         VARCHAR2(100),
  stdprice       NUMBER(16,6) not null,
  netprice       NUMBER(16,6) not null,
  minprice       NUMBER(16,6) not null,
  wareqty        NUMBER(16,6) not null,
  groupid        NUMBER not null,
  saler          VARCHAR2(10),
  times          NUMBER(8,2) not null,
  invalidate     DATE,
  minqty         NUMBER(16,6) not null,
  stdtomin       NUMBER(20,10),
  disrate        NUMBER(16,6) not null,
  distype        NUMBER not null,
  disno          VARCHAR2(20),
  message        VARCHAR2(200),
  purprice       NUMBER(16,6) not null,
  purtax         NUMBER(5,2) not null,
  bak1           NUMBER(16,6),
  bak2           VARCHAR2(20),
  avgpurprice    NUMBER(20,10) not null,
  rowtype        NUMBER(3),
  insno          VARCHAR2(40),
  storeqty       NUMBER(16,6),
  pile           NUMBER(12,3),
  saletax        NUMBER(5,2),
  src_rowid      NUMBER,
  bak16          NUMBER(16,6),
  bind_wareqty   NUMBER(16,6),
  bind_rowid     NUMBER,
  promoplanno_d  VARCHAR2(30),
  pstplanno      VARCHAR2(50),
  promno         VARCHAR2(30),
  h2_med_flag    NUMBER(3),
  h2_cf_no       VARCHAR2(40),
  prombarcode    VARCHAR2(100),
  h2_recordno    VARCHAR2(40),
  h2_sickname    VARCHAR2(40),
  input_rowid    NUMBER,
  invalidno      VARCHAR2(40),
  isinsurance    NUMBER,
  ybprice        NUMBER(16,6),
  bak3           VARCHAR2(20),
  introduct_flag NUMBER,
  maincode       VARCHAR2(400),
  maincode_d     VARCHAR2(200)
)
--------------------U_SALE_M-----------
create table U_SALE_M
(
  saleno         VARCHAR2(24) not null,
  busno          VARCHAR2(10),
  posno          VARCHAR2(10),
  accdate        DATE,
  starttime      DATE,
  finaltime      DATE,
  payee          VARCHAR2(10),
  discounter     VARCHAR2(10),
  crediter       VARCHAR2(10),
  returner       VARCHAR2(10),
  warranter1     VARCHAR2(200),
  warranter2     VARCHAR2(200),
  warranter3     VARCHAR2(200),
  warranter4     VARCHAR2(200),
  warranter5     VARCHAR2(200),
  stdsum         NUMBER(16,4),
  netsum         NUMBER(16,4),
  loss           NUMBER(16,4),
  member         VARCHAR2(30),
  precash        NUMBER(16,4),
  bak1           NUMBER(16,6),
  bak2           VARCHAR2(10),
  stamp          TIMESTAMP(6),
  shiftid        NUMBER(3),
  shiftdate      DATE,
  yb_saleno      VARCHAR2(100),
  return_saleno  VARCHAR2(24),
  pstplannos     VARCHAR2(300),
  changeamt      NUMBER(10,2),
  o2o_trade_from VARCHAR2(50),
  promoplanno    VARCHAR2(300),
  weather        VARCHAR2(10),
  extsource      VARCHAR2(30),
  extno          VARCHAR2(50),
  wln_flag       VARCHAR2(10) default '0',
  wln_no         VARCHAR2(200)
)

INSERT INTO u_sale_c(
saleno,row_id,busno,accdate,wareid,batchno,idno,stallno,makeno,
stdprice,netprice,minprice,wareqty,groupid,saler,times,invalidate,
minqty,stdtomin,disrate,distype,disno,message,purprice,purtax,bak1,
bak2,avgpurprice,rowtype,insno,storeqty,pile,saletax,src_rowid,
bak16,bind_wareqty,bind_rowid,promoplanno_d,pstplanno,promno,
h2_med_flag,h2_cf_no,prombarcode,h2_recordno,h2_sickname,input_rowid,
invalidno,isinsurance,ybprice,bak3,introduct_flag,maincode,maincode_d
)
SELECT
"saleno","rowid","busno","accdate","wareid","batchno","idno","stallno","makeno",
"stdprice","netprice","minprice","wareqty","groupid","saler","times","invalidate",
"minqty","stdtomin","disrate","distype","disno","message","purprice","purtax","bak1",
"bak2","avgpurprice","rowtype","insno","storeqty","pile","saletax","src_rowid",
"bak16","bind_wareqty","bind_rowid","promoplanno_d","pstplanno","promno",
"h2_med_flag","h2_cf_no","prombarcode","h2_recordno","h2_sickname","input_rowid",
"invalidno","isinsurance","ybprice","bak3","introduct_flag","maincode","maincode_d"
FROM"u_sale_c"@ms_link1;

INSERT INTO u_sale_m(
SALENO,BUSNO,POSNO,ACCDATE,STARTTIME,FINALTIME,
PAYEE,DISCOUNTER,CREDITER,RETURNER,WARRANTER1,WARRANTER2,
WARRANTER3,WARRANTER4,WARRANTER5,STDSUM,NETSUM,LOSS,
MEMBER,PRECASH,BAK1,BAK2,STAMP,SHIFTID,SHIFTDATE,
YB_SALENO,RETURN_SALENO,CHANGEAMT,O2O_TRADE_FROM,
PROMOPLANNO,WEATHER,EXTSOURCE,EXTNO,WLN_FLAG,WLN_NO
)
SELECT
  "saleno", "busno", "posno", "accdate", "starttime", "finaltime",
  "payee", "discounter", "crediter", "returner", "warranter1", "warranter2",
  "warranter3", "warranter4", "warranter5", "stdsum", "netsum", "loss",
  "member", "precash", "bak1", "bak2",null, "shiftid", "shiftdate",
  "yb_saleno", "return_saleno", "changeamt", "o2o_trade_from",
  "promoplanno", "weather", "extsource", "extno", "wln_flag", "wln_no"
FROM "u_sale_m"@ms_link1;

commit;

--------更新数据源

delete from u_sale_m where member is null ;
delete from u_sale_c where  saleno not in (select saleno from u_sale_m);

delete from u_sale_c1;
delete from u_sale_m1;

insert into  u_sale_c1 select * from u_sale_c;
insert into  u_sale_m1 select * from u_sale_m;

update u_sale_c1 set BAK3 = wareid;

--select count(1) from u_sale_m1

DECLARE
  CURSOR c_update IS
    SELECT rowid, BAK3
    FROM u_sale_c1;

  r_update c_update%ROWTYPE;
BEGIN
  OPEN c_update;
  LOOP
    FETCH c_update INTO r_update;
    EXIT WHEN c_update%NOTFOUND;  
    UPDATE u_sale_c1
    SET wareid = NVL(f_get_wareid(r_update.BAK3, 2), 1)
    WHERE rowid = r_update.rowid
    AND WAREID < 100029;
      COMMIT;
  END LOOP;
  CLOSE c_update;
END;

修改窗体cd_memcard_sale_d 的数据源 <pre> SELECT d.busno, h.finaltime, w.warename, w.warespec, w.wareunit, d.makeno, f.factoryname, case when wareqty <> 0 then d.netprice else d.minprice end netprice, case when wareqty <> 0 then d.wareqty else d.minqty end wareqty , case when wareqty <> 0 then '' else '拆零' end saleqtytype, d.times, d.insno<br /> FROM t_sale_d d, t_sale_h h,t_ware w,t_factory f WHERE h.saleno = d.saleno and d.wareid = w.wareid and h.compid = w.compid and w.factoryid = f.factoryid union all SELECT busno, finaltime, warename, warespec, wareunit, makeno, factoryname, netprice, wareqty, saleqtytype, times, insno FROM cd_memcardsale