开发者社区> 科技小能手> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

GSP记录补充

简介:
+关注继续查看

WMS中先生成单据编号


--《--采购入库----》

--1、删除表

drop table temp_djbh

--2、创建表

create table temp_djbh as

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

--3、将生成的数据插入表 中

declare

v_shdh varchar2(20) :='JHGYMA00027196';--收货最大单据编号

v_ysdh varchar2(20) :='JYDYMA00026766';--验收最大单据编号

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、查询表中数据

select * from temp_djbh order by djbh

--《---销售退回单号----》

declare

v_shdh varchar2(20) :='XSSYMA00019474';--收货最大单据编号

v_ysdh varchar2(20) :='FHBYMA00019481';--验收最大单据编号

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;

--《--购退单号----》

declare

v_shdh varchar2(20) :='JYTYMA00004497';--收货最大单据编号

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中插入相关记录



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')


--单据对应关系

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

select * from fr_tmp_wms_djdygx

--收货明细


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


--更新拒收数量

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 

 

 

-- --冷藏明细

--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,'进货票单' 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,'进货票单' 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%'





--采退出库复核明细

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,a.rq_sc as baozhiqi,a.yxqz 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


--采退出库复核汇总


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,'进货退出GSP复核' 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


--采购验收明细

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 

--采购验收汇总


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,'进货入库GSP验收' 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 



--销退验收明细


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 


--销退验收汇总

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


--销退收货明细


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')



本文转自 qvodnet 51CTO博客,原文链接:http://blog.51cto.com/bks2015/1946754

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
ESC的使用心得
ESC使用心得,收获满满,从系统配置到环境变量到linux相关命令
13 0
ESC的使用心得
ESC使用遇到的问题和感悟
65 0
Kam
Optional使用记录
Optional使用记录
71 0
记录一次解决App崩溃问题的解决方案
有些时候遇到crash的情况,而且无法复现,作为开发者确实挺头疼的,使用友盟的U-APM产品,可以帮助我们更快速地定位问题,总结相应的情况,帮助我们更主动地去发现问题、解决问题,进而提高用户的体验。
152 0
ESC的使用心得
第一次接触云服务器
71 0
npm记录
npm太慢, 淘宝npm镜像使用方法 转载 2017年03月20日 09:48:14 淘宝 npm 地址: http://npm.taobao.org/ 如何使用 有很多方法来配置npm的registry地址,下面根据不同情境列出几种比较常用的方法。
1027 0
23703
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载