GSP記錄補(bǔ)充

WMS中先生成單據(jù)編號

--《--采購入庫----》

--1、刪除表

drop table temp_djbh

--2、創(chuàng)建表

create table temp_djbh as

select djbh,djbh_sj shdh,djbh_sj ysdh,djbh_sj cgddh from in_upload_m where 1=2;

--3、將生成的數(shù)據(jù)插入表 中

declare

v_shdh varchar2(20) :='JHGYMA00027196';--收貨最大單據(jù)編號

v_ysdh varchar2(20) :='JYDYMA00026766';--驗(yàn)收最大單據(jù)編號

begin

  for r_c in (select djbh,djbh_sj cgddh from in_upload_m where djbh_sj like 'JHF%' and dwid <>'QCRK') loop

    v_shdh := substr(v_shdh||'0000000000',1,9)||(substr(v_shdh,7,8) +1);

    v_ysdh := substr(v_ysdh||'0000000000',1,9)||(substr(v_ysdh,7,8) +1);

    insert into temp_djbh (djbh,shdh,ysdh,cgddh) values(r_c.djbh,v_shdh,v_ysdh,r_c.cgddh);

  end loop;

end;

--4、查詢表中數(shù)據(jù)

select * from temp_djbh order by djbh

--《---銷售退回單號----》

declare

v_shdh varchar2(20) :='XSSYMA00019474';--收貨最大單據(jù)編號

v_ysdh varchar2(20) :='FHBYMA00019481';--驗(yàn)收最大單據(jù)編號

begin

  for r_c in (select djbh,djbh_sj cgddh from in_upload_m where djbh_sj like 'XST%' and dwid 

<>'QCRK') loop

    v_shdh := substr(v_shdh||'0000000000',1,9)||(substr(v_shdh,7,8) +1);

    v_ysdh := substr(v_ysdh||'0000000000',1,9)||(substr(v_ysdh,7,8) +1);

    insert into temp_djbh (djbh,shdh,ysdh,cgddh) values(r_c.djbh,v_shdh,v_ysdh,r_c.cgddh);

  end loop;

end;

--《--購?fù)藛翁?---》

declare

v_shdh varchar2(20) :='JYTYMA00004497';--收貨最大單據(jù)編號

begin

  for r_c in (select djbh from inf_gjtc_hz where djbh like 'JHT%') loop

    v_shdh := substr(v_shdh||'0000000000',1,9)||(substr(v_shdh,7,8) +1);

    insert into temp_djbh (djbh,shdh,ysdh,cgddh) values(r_c.djbh,v_shdh,'','');

  end loop;

end;

ERP中插入相關(guān)記錄

select * from spkfk where spbh in ('J000531','J000532','J000533','J000626','J000535','J000673')

update spkfk set spid=replace(spid,'SPA','SPH') where spbh in ('J000531','J000532','J000533','J000626','J000535','J000673')

--單據(jù)對應(yīng)關(guān)系

select * from openquery ( ORACLE  , 'SELECT * FROM temp_djbh')  

select * from fr_tmp_wms_djdygx

--收貨明細(xì)

truncate table fr_tmp_jh_djmx

insert into fr_tmp_jh_djmx (djbh,dj_sn,dj_sort,spid,xgdjbh,recnum,duiydjbh,duiydj_cn,shl,hshj,hsje,wkfs,ddwd,qywd,ddsj,jsshl,qysj)

select  a.shdh as djbh,a.dj_sort as dj_sn,a.dj_sort,a.spid,a.djbh_sj as xgdjbh,a.dj_sort_sj as recnum,a.djbh_rk as duiydjbh,a.dj_sort as duiydj_cn,a.sl as shl,b.hshj,b.hsje,isnull(a.wkfs,'') wkfs,a.dhwd as ddwd,'' as qywd,case when a.sf_lc=1 then CONVERT(varchar(20), a.dhsj, 24) else '' end as ddsj,0 as jsshl,case when sf_lc=1 then CONVERT(varchar(20), a.qysj, 24) else '' end qysj 

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a,jxddmx b

where a.shdh like 'JHG%' and a.djbh_sj=b.djbh and a.dj_sort_sj=b.dj_sn and a.spid=b.spid order by a.shdh

--更新拒收數(shù)量

update b set b.jsshl=a.sl from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys_js') a,fr_tmp_jh_djmx b

where a.shdh like 'JHG%' and a.shdh=b.djbh and a.dj_sort=b.dj_sn and a.spid=b.spid 

delete jh_djmx where djbh>'JHGYMA00027196' and djbh like 'JHG%'

 insert into jh_djmx

select * from fr_tmp_jh_djmx  order by djbh

--insert into fr_tmp_jh_djmx

--    select f.shdh as djbh,d.dj_sort as dj_sn,d.dj_sort,d.spid,d.cgddh as xgdjbh,d.cgdd_sort as recnum,d.djbh as duiydjbh,d.dj_sort as duiydj_cn,b.dw,'' pihao,

--  '' as baozhiqi,'' as sxrq,b.jlgg,'' as hw,d.jiansh as baozhshl,d.lingsshl as lingsshl,d.shl,a.dj,a.hshj,100 as koul,round(d.shl*a.dj,2) as je,a.shlv,round(d.shl*a.hshj,2) -round(d.shl*a.dj,2) as she,

--  round(d.shl*a.hshj,2) as hsje,0 as lshj,0 as lshje,0 as chbdj,'' as miejph,'' as beihy,0 as ydj,0 as ndj,0 as yixsl,0 as weixsl,0 as picishl,'' as songhr,'否' as zengp,'' as pici,'' as fapiaoh,0 as bukccb,0 as yhshj,

--  0 as nhshj,0 as jiansh,0 as buckcb,'' as beizhu,100 as koulv,100 as koulv1,100 as koulv2,100 as koulv3,0 as dj2,0 as je2,''  as thyy,100 as koulv4,'' as jwh,'' as yssx,'' as wkfs,'' as ddwd,'' as qywd,'' as ddsj,0 as jsshl,0 as pckhdj,

--  '' as xsbmxz,'' as qyrq,'' as qysj,0 as pcxsxj,'是' as is_zx,0 as yiwchsl,0 as quxsl  

-- from jxddmx a (nolock) join jxddhz e on a.djbh=e.djbh                                                           

--  join spkfk b (nolock) on a.spid = b.spid                                                                                        

--  join int_wms_cgrk_bill_his d (nolock) on  a.dj_sn=d.cgdd_sort and a.djbh=d.cgddh                    

--   left join huoweizl c (nolock) on d.yspd = c.huowlb 

--    left join fr_tmp_wms_djdygx f on left(shdh,3)='jhg' and f.djbh=d.djbh                                                             

-- where a.djbh like 'jhf%'  and d.yspd<>'5' 

-- order by f.shdh 

-- --冷藏明細(xì)

--update b set b.ddwd=a.dhwd,b.wkfs=a.wkfs,b.ddsj=CONVERT(varchar(20), dhsj, 24),b.qysj=CONVERT(varchar(20), a.qysj, 24) from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a left join fr_tmp_jh_djmx b on a.djbh_sj=b.xgdjbh and a.spid=b.spid and b.recnum=a.dj_sort_sj 

--where a.sf_lc=1 and a.djbh_sj like 'jhF%'  

--收貨匯總

truncate table fr_tmp_jh_djhz

insert into fr_tmp_jh_djhz (djbh,djbs,djlx,bendian,rq,dwbh,kaiprq,ontime,yuansph,username,zhy)

select distinct a.shdh as djbh,'JHG' as djbs,'112' as djlx,'YMA' as bendian,CONVERT(varchar(20),a.savedate, 23) rq,a.dwid as dwbh,CONVERT(varchar(20),a.savedate, 23) kaiprq,CONVERT(varchar(20),a.savedate, 24) ontime,a.djbh_sj as yuansph,a.ry_shy as username,'進(jìn)貨票單' as zhy

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a

where a.shdh like 'JHG%' order by a.shdh

--insert into fr_tmp_jh_djhz

--select distinct d.djbh as djbh,'JHG' as djbs,'112' as djlx,'YMA' as bendian,'' as rq,c.dwbh,a.bezzs,'' as kaiprq,'' ontime,'' as pihao,a.bm,a.ywy,a.username,'' as leth,c.djbh as yuansph,'' as zph,'' as kpman,'否' as is_yckp,'否' yishj,'進(jìn)貨票單' as zhy,'' as ywbmid,'' as ywyid,'' beizhu,a.fukfs,'' fukqx,a.ydhrq,'' as jingd,a.cyfs,a.cydw,a.is_wtys,'' yssx,'' as wkfs,'' qysj,'' is_jus,'否' shenhe,'' shenhr,

--'' as shenhyj,'' shenhrq,'' as shenhe_cw,'' as shenher_cw,'' as shenheyj_cw,'' as shenherq_cw,'' fhdz,a.ywlxr,a.xszxshx,a.bmgs,a.rktype,'是' is_zx

--from jxddhz a,mchk b,int_wms_cgrk_bill_his c,(select distinct djbh,duiydjbh from fr_tmp_jh_djmx) d

--where  a.dwbh=b.dwbh  and a.djbh=c.cgddh and c.djbh=d.duiydjbh

--order by d.djbh

delete jh_djhz where djbh>'JHGYMA00027196' and djbh like 'JHG%'

 insert into jh_djhz

select * from fr_tmp_jh_djhz  order by djbh

--收貨更新匯總

--update b set b.rq=CONVERT(varchar(20), a.savedate, 23),b.ontime=CONVERT(varchar(20), a.savedate, 24),b.username=a.ry_shy from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a left join fr_tmp_jh_djhz b on a.djbh_rk=b.yuansph  

--where a.sf_lc=1 and a.djbh_sj like 'jhF%'

--update b set b.rq=CONVERT(varchar(20), a.savedate, 23),b.ontime=CONVERT(varchar(20), a.savedate, 24),b.username=a.ry_shy from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a left join fr_tmp_jh_djhz b on a.djbh_rk=b.yuansph  

--where a.djbh_sj like 'jhF%'

--采退出庫復(fù)核明細(xì)

truncate table fr_tmp_jzorder_mx_ysd

insert into fr_tmp_jzorder_mx_ysd (djbh,dj_sn,dj_sort,spid,pihao,baozhiqi,sxrq,shl,xgdjbh,recnum,thyy) 

select a.fudj as djbh,a.dj_sort as dj_sn,a.dj_sort,a.spid,a.ph as pihao,CONVERT(varchar(20), a.rq_sc, 23) as baozhiqi,CONVERT(varchar(20), a.yxqz, 23) as sxrq,a.sl as shl,a.djbh as xgdjbh,a.dj_sort_erp as recnum,a.thyy 

from openquery ( ORACLE  , 'SELECT * FROM v_ck_gjtc') a

where a.fudj like 'JYT%' order by a.fudj 

delete jzorder_mx_ysd where djbh>'JYTYMA00004497' and djbh like 'JYT%'

insert into jzorder_mx_ysd

select * from fr_tmp_jzorder_mx_ysd where djbh like 'JYT%' order by djbh

--采退出庫復(fù)核匯總

truncate table fr_tmp_jzorder_hz_ysd

insert into fr_tmp_jzorder_hz_ysd ( djbh,djbs,djlx,bendian,rq,dwbh,yanshr,username,kaiprq,ontime,kpman,zhy,kaipiaodjbh )

select distinct a.fudj as djbh,'JYT' as djbs,'131' as djlx,'YMA' as bendian,CONVERT(varchar(20), a.rq, 23) as rq,a.dwid as dwbh,a.ry_fuhy as yanshr,a.ry_fuhy as username,CONVERT(varchar(20), a.rq, 23) as kaiprq,CONVERT(varchar(20), a.rq, 24) as ontime,a.ry_fuhy as kpman,'進(jìn)貨退出GSP復(fù)核' as zhy,a.djbh as kaipiaodjbh 

from openquery ( ORACLE  , 'SELECT * FROM v_ck_gjtc') a

where a.fudj like 'JYT%' order by a.fudj 

delete jzorder_hz_ysd where djbh>'JYTYMA00004497' and djbh like 'JYT%'

insert into jzorder_hz_ysd

select * from   fr_tmp_jzorder_hz_ysd where djbh like 'JYT%' order by djbh

--采購驗(yàn)收明細(xì)

truncate table fr_tmp_jzorder_mx_ysd

 insert into fr_tmp_jzorder_mx_ysd (djbh,dj_sn,dj_sort,spid,pihao,baozhiqi,sxrq,shl,xgdjbh,recnum,dwbh) 

 select a.ysdh as djbh,a.dj_sort as dj_sn,a.dj_sort,a.spid,a.ph pihao,CONVERT(varchar(20),a.rq_sc, 23) baozhiqi,CONVERT(varchar(20), yxqz, 23) sxrq,a.sl as shl,a.djbh_sj as xgdjbh,dj_sort_sj recnum,a.dwid as dwbh

 from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a

 where a.ysdh like 'JYD%'  order by a.ysdh 

 delete jzorder_mx_ysd where djbh>'JYDYMA00026766' and djbh like 'JYD%'

 insert into jzorder_mx_ysd

 select * from fr_tmp_jzorder_mx_ysd where djbh like 'JYD%' order by djbh 

--采購驗(yàn)收匯總

truncate table fr_tmp_jzorder_hz_ysd 

 insert into fr_tmp_jzorder_hz_ysd ( djbh,djbs,djlx,bendian,rq,dwbh,yanshr,username,kaiprq,ontime,kpman,zhy,kaipiaodjbh )

select  distinct a.ysdh as djbh,'JYD' as djbs,'130' as djlx,'YMA' as bendian,CONVERT(varchar(20), a.ysrq, 23) as rq,a.dwid as dwbh,a.ry_zjy as yanshr,a.ry_zjy as username,CONVERT(varchar(20), a.ysrq, 23) as kaiprq,CONVERT(varchar(20), a.ysrq, 24) as ontime,a.ry_zjy as kpman,'進(jìn)貨入庫GSP驗(yàn)收' as zhy,a.djbh_rk as kaipiaodjbh 

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a

where a.ysdh like 'JYD%'  order by a.ysdh

delete jzorder_hz_ysd where djbh>'JYDYMA00026766' and djbh like 'JYD%'

 insert into jzorder_hz_ysd

 select * from fr_tmp_jzorder_hz_ysd where djbh like 'JYD%' order by djbh 

--銷退驗(yàn)收明細(xì)

select top 10 * from jzorder_mx_fhd where  djbh like 'FHB%' order by djbh desc

truncate table fr_tmp_jzorder_mx_fhd 

insert into fr_tmp_jzorder_mx_fhd (djbh,dj_sn,dj_sort,spid,pihao,baozhiqi,sxrq,hw,shl,xgdjbh,duiydjbh,duiydj_cn,thyy)

select a.ysdh as djbh,a.dj_sort as dj_sn,a.dj_sort,a.spid,a.ph as pihao,CONVERT(varchar(20),a.rq_sc, 23) baozhiqi,CONVERT(varchar(20), yxqz, 23) sxrq,b.hw,a.sl as shl,a.shdh as xgdjbh,a.djbh_sj as duiydjbh,a.dj_sort_sj as duiydj_cn,b.thyy

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a,pf_djmx b

where a.ysdh like 'FHB%' and a.djbh_sj=b.djbh and a.dj_sort_sj=b.dj_sn and a.spid=b.spid order by a.ysdh

delete jzorder_mx_fhd where djbh>'FHBYMA00019481' and djbh like 'FHB%'

--需要更新xh

insert into jzorder_mx_fhd (djbh,dj_sn,dj_sort,spid,pihao,baozhiqi,sxrq,hw,shl,xgdjbh,duiydjbh,duiydj_cn,thyy)

select a.ysdh as djbh,a.dj_sort as dj_sn,a.dj_sort,a.spid,a.ph as pihao,CONVERT(varchar(20),a.rq_sc, 23) baozhiqi,CONVERT(varchar(20), yxqz, 23) sxrq,b.hw,a.sl as shl,a.shdh as xgdjbh,a.djbh_sj as duiydjbh,a.dj_sort_sj as duiydj_cn,b.thyy

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a,pf_djmx b

where a.ysdh like 'FHB%' and a.djbh_sj=b.djbh and a.dj_sort_sj=b.dj_sn and a.spid=b.spid order by a.ysdh

--select * from fr_tmp_jzorder_mx_fhd　

--銷退驗(yàn)收匯總

truncate table fr_tmp_jzorder_hz_fhd 

insert into fr_tmp_jzorder_hz_fhd (djbh,djbs,djlx,bendian,rq,dwbh,yanshr,username,kaiprq,ontime,kpman,kaipiaodjbh)

select distinct a.ysdh as djbh,'FHB' as djbs,'233' as djlx,'YMA' as bendian,CONVERT(varchar(20),a.ysrq, 23) rq,a.dwid as dwbh,a.ry_zjy as yanshr,a.ry_zjy as username,CONVERT(varchar(20),a.ysrq, 23) kaiprq,CONVERT(varchar(20),a.ysrq, 24) ontime,a.ry_zjy as kpman,a.djbh_sj as duiydjbh

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a

where a.ysdh like 'FHB%' order by a.ysdh

delete jzorder_hz_fhd where djbh>'FHBYMA00019481' and djbh like 'FHB%'

insert into jzorder_hz_fhd

select * from fr_tmp_jzorder_hz_fhd

--銷退收貨明細(xì)

truncate table fr_tmp_pf_djmx 

insert into fr_tmp_pf_djmx (djbh,dj_sn,spid,dj_sort,xgdjbh,recnum,duiydjbh,duiydj_cn,pihao,baozhiqi,hw,shl,hshj,hsje,sxrq,thyy,dwbh)

select  a.shdh as djbh,a.dj_sort as dj_sn,a.spid,a.dj_sort,a.djbh_sj as xgdjbh,a.dj_sort_sj as recnum,a.djbh_rk as duiydjbh,a.dj_sort as duiydj_cn,a.ph as pihao,CONVERT(varchar(20),a.rq_sc, 23) baozhiqi,b.hw,a.sl as shl,b.hshj,b.hsje,CONVERT(varchar(20), yxqz, 23) sxrq,b.thyy,a.dwid as dwbh

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a,pf_djmx b

where a.shdh like 'XSS%' and a.djbh_sj=b.djbh and a.dj_sort_sj=b.dj_sn and a.spid=b.spid order by a.shdh

delete pf_djmx where djbh>'XSSYMA00019474' and djbh like 'XSS%'

insert into pf_djmx

select * from fr_tmp_pf_djmx

--銷退收貨匯總

truncate table fr_tmp_pf_djhz 

insert into fr_tmp_pf_djhz (djbh,djbs,djlx,bendian,rq,dwbh,kaiprq,ontime,yuansph,username,zhy)

select distinct a.shdh as djbh,'XSS' as djbs,'232' as djlx,'YMA' as bendian,CONVERT(varchar(20),a.savedate, 23) rq,a.dwid as dwbh,CONVERT(varchar(20),a.savedate, 23) kaiprq,CONVERT(varchar(20),a.savedate, 24) ontime,a.djbh_sj as yuansph,a.ry_shy as username,'銷售退回收貨單' as zhy

from openquery ( ORACLE  , 'SELECT * FROM v_rk_lcys') a

where a.shdh like 'XSS%'  order by a.shdh

delete pf_djhz where djbh>'XSSYMA00019474' and djbh like 'XSS%'

insert into pf_djhz

select * from fr_tmp_pf_djhz

--還原屏蔽商品

update spkfk set spid=replace(spid,'SPH','SPA') where spbh in ('J000531','J000532','J000533','J000626','J000535','J000673')