Shulou(Shulou.com)06/01 Report--

Mr. Cheng document number in WMS

-- "--purchase into storage -"

1. Delete the table

Drop table temp_djbh

2. Create a table

Create table temp_djbh as

Select djbh,djbh_sj shdh,djbh_sj ysdh,djbh_sj cgddh from in_upload_m where 1: 2

Insert the generated data into the table

Declare

V_shdh varchar2 (20): = 'JHGYMA00027196';-- maximum receipt document number

V_ysdh varchar2 (20): = 'JYDYMA00026766';-- acceptance maximum document number

Begin

For Runc in (select djbh,djbh_sj cgddh from in_upload_m where djbh_sj like 'JHF%' and dwid' QCRK') loop

V_shdh: = substr (v_shdh | | '0000000000000000)) | | (substr (venershdhmag8) + 1)

V_ysdh: = substr (v_ysdh | | '0000000000000000)) | | (substr (viciysdhmag8) + 1)

Insert into temp_djbh (djbh,shdh,ysdh,cgddh) values (rascc.djbhrec. Cgddh)

End loop

End

-- 4. Query the data in the table

Select * from temp_djbh order by djbh

-sales return order number-

Declare

V_shdh varchar2 (20): = 'XSSYMA00019474';-- maximum receipt document number

V_ysdh varchar2 (20): = 'FHBYMA00019481';-- acceptance maximum document number

Begin

For ritual in (select djbh,djbh_sj cgddh from in_upload_m where djbh_sj like 'XST%' and dwid)

'QCRK') loop

V_shdh: = substr (v_shdh | | '0000000000000000)) | | (substr (venershdhmag8) + 1)

V_ysdh: = substr (v_ysdh | | '0000000000000000)) | | (substr (viciysdhmag8) + 1)

Insert into temp_djbh (djbh,shdh,ysdh,cgddh) values (rascc.djbhrec. Cgddh)

End loop

End

-purchase order number

Declare

V_shdh varchar2 (20): = 'JYTYMA00004497';-- maximum receipt document number

Begin

For Renec in (select djbh from inf_gjtc_hz where djbh like 'JHT%') loop

V_shdh: = substr (v_shdh | | '0000000000000000)) | | (substr (venershdhmag8) + 1)

Insert into temp_djbh (djbh,shdh,ysdh,cgddh) values (ringing c.djbhmage vaunshdhrewangshdhrew)

End loop

End

Insert related records into ERP

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

Update spkfk set spid=replace (spid,'SPA','SPH') where spbh in ('J000531 and J000532)

-- document correspondence

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

Select * from fr_tmp_wms_djdygx

-- receiving details

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 else') 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 vault rkwark cystic') a

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

-- updating rejected quantity

Update b set b.jsshl=a.sl from openquery (ORACLE, 'SELECT * FROM vastrkcystic lcystic js') a, mpcmpdjmx 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.shlfua.djmag2) as je,a.shlv,round (d.shlfua.hshjmag2)-round (d.shlfua.djmag2) as she

-- round (d.shlfua.hshjmag2) 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,' No'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 koulv1100 as koulv2100 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,' is'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.yspding5'

-- order by f.shdh

-refrigerated details

-- update b set b.ddwddenda.dhwdbookb.wkfscompila.wkfsreportb.ddsjendCONVERT (varchar (20), dhsj, 24), b.qysj=CONVERT (varchar (20), a.qysj, 24) from openquery (ORACLE, 'SELECT * FROM vicious rkeletlcystic') 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%'

-- receiving summary

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,' purchase ticket'as zhy

From openquery (ORACLE, 'SELECT * FROM) 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,' No'as is_yckp,' No 'yishj,' purchase ticket' as zhy,'' as ywbmid,'' as ywyid,'' beizhu,a.fukfs,'' fukqx A. Ydhrq shenhe,'' shenhr'as jingd,a.cyfs,a.cydw,a.is_wtys,'' yssx,'' as wkfs,'' qysj,'' is_jus,' No'

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

-- from jxddhz a select distinct djbh,duiydjbh from fr_tmp_jh_djmx mchk bpeng intact wmswriting cgrksand billproof his c, (mchk) 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

-- receiving update summary

-- 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 vastrkcysl') 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 vastrkcysl') a left join fr_tmp_jh_djhz b on a.djbh_rk=b.yuansph

-- where a.djbh_sj like 'jhF%'

-- check details of collection and return to the warehouse

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 vastck.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

-- Review summary of the collection and withdrawal of the library

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,' purchase and exit GSP check'as zhy,a.djbh as kaipiaodjbh

From openquery (ORACLE, 'SELECT * FROM vastck.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

-details of purchase acceptance

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

-- purchase acceptance summary

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,' inventory GSP acceptance check

From openquery (ORACLE, 'SELECT * FROM) 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

-- details of sales return acceptance

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 vicious rkwark cystic') a, 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 needs to be updated

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 vicious rkwark cystic') a, 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

-- Summary of sales and return acceptance

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

Details of sales and return of goods

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 vicious rkwark cystic') a, 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

-- Summary of sales, return and receipt

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,' sales return receipt'as zhy

From openquery (ORACLE, 'SELECT * FROM) 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

-- restore shielded goods

Update spkfk set spid=replace (spid,'SPH','SPA') where spbh in ('J000531 and J000532)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.