1/70用友股份-LE支持服务业务本部产品方案--《NC5X单个仓库调整现存量》建立日期：2014-01-12修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-CP-2014-00232/70文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2014-01-10王鑫产品支持部V1.1版本领域模块备注NC5X集团供应链采购管理3/70目录NC5X单个仓库调整现存量......................................................................................4一、问题描述............................................................................................................4二、问题分析............................................................................................................4三、解决方案............................................................................................................5(一先登录系统确认错误数据。...........................................................................5)(二删除现存量表数据.sql......................................................................................5)(三现存量调整.sql..................................................................................................6)(四有货位现存量调整.sql....................................................................................45)(五补全货位现存量中现存量.sql........................................................................59)(六补全货位现存量中现存量.sql........................................................................62)四、特殊说明..........................................................................................................704/70NC5X单个仓库调整现存量注：此解决方案涉及数据库操作或代码操作，请由计算机专业人士操作，并且操作前请做好备份工作！一、问题描述因目前很多项目库存数据较大，及因特殊原因导致现存量数据不正确。通过前台库存管理→基础设置→调整现存量都无法调整正确数据。故此，提供后台数据库进行数据更新方案。该SQL更新与前台调整现存量是同类逻辑进行调整。调整现存量不论前台还是后台数据库调整，前提都是业务单据上的数值是正确的，才可以调整正确。二、问题分析通过删除现存量数据，再更新现存量表数据。以下字段作为where条件。注意:以下方案请现在测试环境测试通过再同步正式环境，同时调整过程中where条件必须保持一致，不然无法调整正确。（特别注意SQL语句中红色条件）ccalbodyid库存组织PKcwarehouseid仓库PKpk_corp公司5/70三、解决方案先登录系统确认错误数据。(一)删除现存量表数据.sql(二)--删除线存量表体数据deletefromic_onhandnum_bwherepk_onhandnumin(selectpk_onhandnumfromic_onhandnumwhere1=1and(pk_corp='1011')and(ccalbodyid='101112100000000003VF')and(cwarehouseid='101112100000000004HN'))--仓库PK--删除线存量表头数据deletefromic_onhandnum6/70where1=1and(pk_corp='1011')and(ccalbodyid='101112100000000003VF')and(cwarehouseid='101112100000000004HN')--仓库PK--调整现存量现存量调整.sql(三)--现存量调整insertintoic_onhandnum(pk_onhandnum,pk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vlot,castunitid,cvendorid,hsl,vfree1,vfree2,7/70vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,nonhandnum,nonhandastnum,ngrossnum,nnum1,nastnum1,ngrossnum1,nnum2,nastnum2,ngrossnum2)select'130410104906'||TO_CHAR(10000000+rownum)aspk_onhandnum,pk_corp,ccalbodyid,cwarehouseid,cinventoryid,8/70cinvbasid,vlot,castunitid,cvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,nonhandnum,nonhandastnum,ngrossnum,nnum1,nastnum1,ngrossnum1,nnum2,9/70nastnum2,ngrossnum2from(selectpk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vlot,castunitid,cvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,(case10/70whensum(nonhandnum)=0thennullelsesum(nonhandnum)end)asnonhandnum,(casewhensum(nonhandastnum)=0thennullelsesum(nonhandastnum)end)asnonhandastnum,(casewhensum(ngrossnum)=0thennullelsesum(ngrossnum)end)asngrossnum,(casewhensum(nnum1)=0thennullelsesum(nnum1)11/70end)asnnum1,(casewhensum(nastnum1)=0thennullelsesum(nastnum1)end)asnastnum1,(casewhensum(ngrossnum1)=0thennullelsesum(ngrossnum1)end)asngrossnum1,(casewhensum(nnum2)=0thennullelsesum(nnum2)end)asnnum2,(casewhensum(nastnum2)=0thennull12/70elsesum(nastnum2)end)asnastnum2,(casewhensum(ngrossnum2)=0thennullelsesum(ngrossnum2)end)asngrossnum2from(selectpk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vbatchcodeasvlot,castunitid,cprovideridascvendorid,hsl,vfree1,vfree2,vfree3,vfree4,13/70vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,nspacenumasnonhandnum,nspaceassistnumasnonhandastnum,ngrossnum,0asnnum1,0asnastnum1,0asngrossnum1,0asnnum2,0asnastnum2,0asngrossnum2from(SELECTpk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vbatchcode,castunitid,14/70cproviderid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,coalesce(sum(ninspacenum),0.0)-coalesce(sum(noutspacenum),0.0)asnspacenum,coalesce(sum(ninspaceassistnum),0.0)-coalesce(sum(noutspaceassistnum),0.0)asnspaceassistnum,sum(coalesce(ningrossnum,0.0)-coalesce(noutgrossnum,0.0))asngrossnumfrom(SELECTa.pk_corp,15/70a.ccalbodyid,a.cwarehouseid,a.cinventoryid,a.cinvbasid,a.vfree1,a.vfree2,a.vfree3,a.vfree4,a.vfree5,a.vfree6,a.vfree7,a.vfree8,a.vfree9,a.vfree10,vbatchcode,castunitid,casewhenman.ISSUPPLIERSTOCK='Y'orman.ISSUPPLIERSTOCK='1'orwh.isgathersettle='Y'then16/70a.cprovideridelsecast(nullaschar)endascproviderid,casewhenbas.isstorebyconvert='Y'orbas.isstorebyconvert='1'thena.hslelsecast(nullasdecimal)endashsl,casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.ningrossnumelsecast(nullasdecimal)endasningrossnum,17/70casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.noutgrossnumelsecast(nullasdecimal)endasnoutgrossnum,ninspacenum,noutspacenum,ninspaceassistnum,noutspaceassistnumfrom(SELECTh.pk_corp,b.dbizdate,h.daccountdate,h.pk_calbodyASccalbodyid,h.cwarehouseid,c.cspaceid,b.cinventoryid,b.vfree1,18/70b.vfree2,b.vfree3,b.vfree4,b.vfree5,b.vfree6,b.vfree7,b.vfree8,b.vfree9,b.vfree10,b.vbatchcode,s.dvalidate,b.castunitid,b.hsl,COALESCE(c.ninspacenum,b.ninnum,0.0)asninspacenum,COALESCE(c.ninspaceassistnum,b.ninassistnum,0.0)asninspaceassistnum,19/70COALESCE(c.noutspacenum,b.noutnum,0.0)asnoutspacenum,COALESCE(c.noutspaceassistnum,b.noutassistnum,0.0)asnoutspaceassistnum,b.ccostobject,b.cprojectid,b.cprojectphaseid,b.csourcetype,h.fbillflag,h.cbilltypecode,h.cdispatcherid,b.cvendoridAScproviderid,COALESCE(c.ningrossnum,b.ningrossnum,20/700.0)asningrossnum,COALESCE(c.noutgrossnum,b.noutgrossnum,0.0)asnoutgrossnum,b.cinvbasid,b.nplannedmny,h.cgeneralhid,b.cgeneralbidFROMic_general_hhinnerjoinic_general_bbonh.cgeneralhid=b.cgeneralhidleftouterjoinic_general_bb1conb.cgeneralbid=c.cgeneralbidANDc.dr=021/70leftouterjoinscm_batchcodesonb.cinvbasid=s.pk_invbasdocandb.vbatchcode=s.vbatchcodeWHERE(((h.cbilltypecodein('45')ANDb.fchecked=0AND(h.cbiztypeIN(SELECTpk_busitypeFROMbd_busitypeWHEREverifyrule<>'J')ORh.cbiztypeISNULL))ORh.cbilltypecodein('40','41','44','4401','46','47','48','49','4A','4B','4E'))ANDninnumISNOTNULLOR22/70((h.cbilltypecodein('4C')AND(h.cbiztypeIN(SELECTpk_busitypeFROMbd_busitypeWHEREverifyrule<>'C')ORh.cbiztypeISNULL))ORh.cbilltypecodein('4451','4D','4F','4G','4H','4I','4J','4O','4Y'))ANDnoutnumISNOTNULLORh.cbilltypecodein('4Q'))ANDh.dr=0ANDb.dr=0)ainnerjoinbd_invmandocmanona.CINVENTORYID=man.PK_INVMANDOCinnerjoinbd_invbasdocbasonman.pk_invbasdoc=23/70bas.pk_invbasdocinnerjoinbd_stordocwhona.cwarehouseid=wh.pk_stordocwhere(ninspacenumisnotnullornoutspacenumisnotnull))vwhere1=1and(pk_corp='1011')and(ccalbodyid='101112100000000003VF')and(cwarehouseid='101112100000000004HN')--仓库PKgroupbypk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vbatchcode,castunitid,cproviderid,hsl,24/70vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10)tempnospacewherenspacenum>0ornspaceassistnum>0orngrossnum>0ornspacenum<0ornspaceassistnum<0orngrossnum<0unionallselectpk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,25/70vbatchcodeasvlot,castunitid,cprovideridascvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,0asnonhandnum,0asnonhandastnum,0asngrossnum,nnum1,nastnum1,ngrossnum1,0asnnum2,0asnastnum2,26/700asngrossnum2from(selectpk_corp,pk_calbodyasccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vbatchcode,castunitid,cproviderid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,SUM(nvl(ninnum,0.0)-nvl(transnum,0.0)-27/70nvl(ljhhnum,0.0))ASnnum1,SUM(nvl(ninassistnum,0.0)-nvl(transastnum,0.0)-nvl(ljhhastnum,0.0))ASnastnum1,sum(coalesce(ningrossnum,0.0)-coalesce(noutgrossnum,0.0))asngrossnum1FROM(selecta.pk_corp,a.pk_calbody,a.cwarehouseid,a.cinventoryid,a.cinvbasid,a.vbatchcode,a.castunitid,casewhenman.ISSUPPLIERSTOCK='Y'orman.ISSUPPLIERSTOCK='1'orwh.isgathersettle='Y'thena.cprovideridelse28/70CAST(NULLASchar)endascproviderid,casewhenbas.isstorebyconvert='Y'orbas.isstorebyconvert='1'thena.hslelseCAST(NULLASdecimal)endashsl,casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.ningrossnumelseCAST(NULLASdecimal)endasningrossnum,case29/70whenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.noutgrossnumelseCAST(NULLASdecimal)endasnoutgrossnum,a.vfree1,a.vfree2,a.vfree3,a.vfree4,a.vfree5,a.vfree6,a.vfree7,a.vfree8,a.vfree9,a.vfree10,ninnum,ninassistnum,transnum,transastnum,30/70ljhhnum,ljhhastnumfrom(SELECTh.pk_corp,h.pk_calbody,h.cbilltypecode,h.vbillcode,h.cgeneralhid,h.daccountdate,h.cwarehouseid,b.cinventoryid,b.vfree1,b.vfree2,b.vfree3,b.vfree4,b.vfree5,b.vfree6,b.vfree7,b.vfree8,b.vfree9,b.vfree10,b.vbatchcode,b.castunitid,31/70b.dbizdate,b.cvendoridascproviderid,b.ninnum,b.ninassistnum,b.noutnum,b.noutassistnum,b.nretnumASljhhnum,b.nretastnumASljhhastnum,b.nretgrossnumasljhhgrsnum,b.ntranoutnumAStransnum,b.ntranoutastnumAStransastnum,0.0ASrestnum,0.0ASrestastnum,b.nprice,h.fbillflag,b.hsl,b.ningrossnum,32/70b.noutgrossnum,b.cinvbasid,b.dvalidateFROMic_general_hhINNERJOINic_general_bbONh.cgeneralhid=b.cgeneralhidWHERE(h.cbilltypecodein('49','41','4J'))AND(h.dr=0)AND(b.dr=0))ainnerjoinbd_invmandocmanona.cinventoryid=man.pk_invmandocinnerjoinbd_invbasdocbasonman.pk_invbasdoc=bas.pk_invbasdocinnerjoinbd_stordocwhona.cwarehouseid=33/70wh.pk_stordocwhereninnumisnotnull)vwhere1=1and(pk_corp='1011')and(pk_calbody='101112100000000003VF')and(cwarehouseid='101112100000000004HN')--仓库PKgroupbypk_corp,pk_calbody,cwarehouseid,cinventoryid,cinvbasid,vbatchcode,castunitid,cproviderid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,34/70vfree6,vfree7,vfree8,vfree9,vfree10)tempborrowwherennum1>0ornastnum1>0orngrossnum1>0ornnum1<0ornastnum1<0orngrossnum1<0unionallselectpk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vbatchcodeasvlot,castunitid,cprovideridascvendorid,hsl,vfree1,35/70vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,0asnonhandnum,0asnonhandastnum,0asngrossnum,0asnnum1,0asnastnum1,0asngrossnum1,nnum2,nastnum2,ngrossnum2from(selectpk_corp,pk_calbodyasccalbodyid,cwarehouseid,cinventoryid,36/70cinvbasid,vbatchcode,castunitid,cproviderid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,SUM(nvl(noutnum,0.0)-nvl(transnum,0.0)-nvl(ljhhnum,0.0))ASnnum2,SUM(nvl(noutassistnum,0.0)-nvl(transastnum,0.0)-nvl(ljhhastnum,0.0))ASnastnum2,sum(coalesce(ningrossnum,0.0)-37/70coalesce(noutgrossnum,0.0))asngrossnum2FROM(selecta.pk_corp,a.pk_calbody,a.cwarehouseid,a.cinventoryid,a.cinvbasid,a.vbatchcode,a.castunitid,casewhenman.ISSUPPLIERSTOCK='Y'orman.ISSUPPLIERSTOCK='1'orwh.isgathersettle='Y'thena.cprovideridelseCAST(NULLASchar)endascproviderid,casewhenbas.isstorebyconvert='Y'or38/70bas.isstorebyconvert='1'thena.hslelseCAST(NULLASdecimal)endashsl,casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.ningrossnumelseCAST(NULLASdecimal)endasningrossnum,casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.noutgrossnumelse39/70CAST(NULLASdecimal)endasnoutgrossnum,a.vfree1,a.vfree2,a.vfree3,a.vfree4,a.vfree5,a.vfree6,a.vfree7,a.vfree8,a.vfree9,a.vfree10,noutnum,noutassistnum,transnum,transastnum,ljhhnum,ljhhastnumfrom(SELECTh.pk_corp,h.pk_calbody,h.cbilltypecode,h.vbillcode,40/70h.cgeneralhid,h.daccountdate,h.ts,h.cwarehouseid,b.cinventoryid,b.vfree1,b.vfree2,b.vfree3,b.vfree4,b.vfree5,b.vfree6,b.vfree7,b.vfree8,b.vfree9,b.vfree10,b.vbatchcode,b.dvalidate,b.castunitid,b.ninnum,b.ninassistnum,b.noutnum,b.noutassistnum,41/70b.nretnumASljhhnum,b.nretastnumASljhhastnum,0.0ASrestnum,b.nretgrossnumasljhhgrsnum,0.0asnretgrossnum,0.0ASrestastnum,b.ntranoutnumAStransnum,b.ntranoutastnumAStransastnum,h.ccustomerid,b.cvendoridascproviderid,b.dbizdate,b.nprice,h.fbillflag,b.hsl,b.ningrossnum,b.noutgrossnum,b.cinvbasid42/70FROMic_general_hhINNERJOINic_general_bbONh.cgeneralhid=b.cgeneralhidWHERE(h.cbilltypecodeIN('4H','42','4B'))AND(h.dr=0)AND(b.dr=0))ainnerjoinbd_invmandocmanona.cinventoryid=man.pk_invmandocinnerjoinbd_invbasdocbasonman.pk_invbasdoc=bas.pk_invbasdocinnerjoinbd_stordocwhona.cwarehouseid=wh.pk_stordocwherenoutnumisnotnull)v43/70where1=1and(pk_corp='1011')and(pk_calbody='101112100000000003VF')and(cwarehouseid='101112100000000004HN')--仓库PKgroupbypk_corp,pk_calbody,cwarehouseid,cinventoryid,cinvbasid,vbatchcode,castunitid,cproviderid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,44/70vfree9,vfree10)templendwherennum2>0ornastnum2>0orngrossnum2>0ornnum2<0ornastnum2<0orngrossnum2<0)temponhandgroupbypk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vlot,castunitid,cvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,45/70vfree6,vfree7,vfree8,vfree9,vfree10)tempfinal有货位现存量调整.sql(四)--有货位现存量调整insertintoic_onhandnum_b(pk_onhandnum_b,pk_onhandnum,pk_corp,ccalbodyidb,cwarehouseidb,cspaceid,cinventoryidb,cinvbasid,vlotb,castunitidb,cvendorid,hsl,vfreeb1,46/70vfreeb2,vfreeb3,vfreeb4,vfreeb5,vfreeb6,vfreeb7,vfreeb8,vfreeb9,vfreeb10,nnum,nastnum)select'130410110226'||TO_CHAR(10000000+rownum)aspk_onhandnum_b,'_________N/A________',pk_corp,ccalbodyid,cwarehouseid,cspaceid,cinventoryid,cinvbasid,vlot,castunitid,47/70cvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,nnum,nastnumfrom(selectpk_corp,ccalbodyid,cwarehouseid,cspaceid,cinventoryid,cinvbasid,vbatchcodeasvlot,castunitid,48/70cprovideridascvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,nspacenumasnnum,nspaceassistnumasnastnumfrom(SELECTpk_corp,ccalbodyid,cwarehouseid,cspaceid,cinventoryid,cinvbasid,vbatchcode,castunitid,49/70cproviderid,hsl,sum(coalesce(ningrossnum,0.0)-coalesce(noutgrossnum,0.0))ngrossnum,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10,coalesce(sum(ninspacenum),0.0)-coalesce(sum(noutspacenum),0.0)asnspacenum,coalesce(sum(ninspaceassistnum),0.0)-coalesce(sum(noutspaceassistnum),0.0)asnspaceassistnumfrom(selecta.pk_corp,a.ccalbodyid,cwarehouseid,50/70a.cspaceid,cinventoryid,cinvbasid,a.vfree1,a.vfree2,a.vfree3,a.vfree4,a.vfree5,a.vfree6,a.vfree7,a.vfree8,a.vfree9,a.vfree10,vbatchcode,castunitid,ninspacenum,noutspacenum,ninspaceassistnum,noutspaceassistnum,casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'then51/70a.ningrossnumelseCAST(NULLASdecimal)endasningrossnum,casewhenbas.ismngstockbygrswt='Y'orbas.ismngstockbygrswt='1'thena.noutgrossnumelseCAST(NULLASdecimal)endasnoutgrossnum,casewhenman.ISSUPPLIERSTOCK='Y'orman.ISSUPPLIERSTOCK='1'orwh.isgathersettle='Y'thena.cprovideridelseCAST(NULLASchar)endascproviderid,casewhenbas.isstorebyconvert='Y'orbas.isstorebyconvert='1'then52/70a.hslelseCAST(NULLASdecimal)endashslfrom(SELECTh.pk_corp,b.dbizdate,h.daccountdate,h.pk_calbodyASccalbodyid,h.cwarehouseid,c.cspaceid,b.cinventoryid,b.vfree1,b.vfree2,b.vfree3,b.vfree4,b.vfree5,b.vfree6,b.vfree7,b.vfree8,b.vfree9,b.vfree10,b.vbatchcode,53/70s.dvalidate,b.castunitid,b.hsl,COALESCE(c.ninspacenum,b.ninnum,0.0)asninspacenum,COALESCE(c.ninspaceassistnum,b.ninassistnum,0.0)asninspaceassistnum,COALESCE(c.noutspacenum,b.noutnum,0.0)asnoutspacenum,COALESCE(c.noutspaceassistnum,b.noutassistnum,0.0)asnoutspaceassistnum,b.ccostobject,b.cprojectid,b.cprojectphaseid,b.csourcetype,h.fbillflag,h.cbilltypecode,54/70h.cdispatcherid,b.cvendoridAScproviderid,COALESCE(c.ningrossnum,b.ningrossnum,0.0)asningrossnum,COALESCE(c.noutgrossnum,b.noutgrossnum,0.0)asnoutgrossnum,b.cinvbasid,b.nplannedmny,h.cgeneralhid,b.cgeneralbidFROMic_general_hhinnerjoinic_general_bbonh.cgeneralhid=b.cgeneralhidleftouterjoinic_general_bb1conb.cgeneralbid=c.cgeneralbid55/70ANDc.dr=0leftouterjoinscm_batchcodesonb.cinvbasid=s.pk_invbasdocandb.vbatchcode=s.vbatchcodeWHERE(((h.cbilltypecodein('45')ANDb.fchecked=0AND(h.cbiztypeIN(SELECTpk_busitypeFROMbd_busitype56/70WHEREverifyrule<>'J')ORh.cbiztypeISNULL))ORh.cbilltypecodein('40','41','44','4401','46','47','48','49','4A','4B','4E'))ANDninnumISNOTNULLOR((h.cbilltypecodein('4C')AND(h.cbiztypeIN(SELECTpk_busitypeFROMbd_busitype57/70WHEREverifyrule<>'C')ORh.cbiztypeISNULL))ORh.cbilltypecodein('4451','4D','4F','4G','4H','4I','4J','4O','4Y'))ANDnoutnumISNOTNULLORh.cbilltypecodein('4Q'))ANDh.dr=0ANDb.dr=0)ainnerjoinbd_invmandocmanona.cinventoryid=man.pk_invmandocinnerjoinbd_invbasdocbasonman.pk_invbasdoc=58/70bas.pk_invbasdocinnerjoinbd_stordocwhona.cwarehouseid=wh.pk_stordocwherecspaceidisnotnull)vwhere1=1and(pk_corp='1011')and(ccalbodyid='101112100000000003VF')and(cwarehouseid='101112100000000004HN')--仓库PKgroupbypk_corp,ccalbodyid,cwarehouseid,cspaceid,cinventoryid,cinvbasid,vbatchcode,castunitid,cproviderid,hsl,vfree1,59/70vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10)tempnospacewherenspacenum>0ornspacenum<0ornspaceassistnum>0ornspaceassistnum<0)tempfinal补全货位现存量中现存量.sql(五)--补全货位现存量中现存量1UPDATEic_onhandnum_bsetpk_onhandnum=(selectpk_onhandnumfromic_onhandnumwhereic_onhandnum_b.cinventoryidb=ic_onhandnum.cinventoryidANDic_onhandnum_b.cwarehouseidb=60/70ic_onhandnum.cwarehouseidANDic_onhandnum_b.ccalbodyidb=ic_onhandnum.ccalbodyidAND(ic_onhandnum_b.pk_corp=ic_onhandnum.pk_corp)AND(ic_onhandnum_b.cvendorid=ic_onhandnum.cvendoridoric_onhandnum_b.cvendoridisnullandic_onhandnum.cvendoridisnull)AND(ic_onhandnum_b.hsl=ic_onhandnum.hsloric_onhandnum_b.hslisnullandic_onhandnum.hslisnull)AND(ic_onhandnum_b.vlotb=ic_onhandnum.vlotoric_onhandnum_b.vlotbisnullandic_onhandnum.vlotisnull)AND(ic_onhandnum_b.castunitidb=ic_onhandnum.castunitidoric_onhandnum_b.castunitidbisnullandic_onhandnum.castunitidisnull)AND(ic_onhandnum_b.vfreeb1=ic_onhandnum.vfree1or61/70ic_onhandnum_b.vfreeb1isnullandic_onhandnum.vfree1isnull)AND(ic_onhandnum_b.vfreeb2=ic_onhandnum.vfree2oric_onhandnum_b.vfreeb2isnullandic_onhandnum.vfree2isnull)AND(ic_onhandnum_b.vfreeb3=ic_onhandnum.vfree3oric_onhandnum_b.vfreeb3isnullandic_onhandnum.vfree3isnull)AND(ic_onhandnum_b.vfreeb4=ic_onhandnum.vfree4oric_onhandnum_b.vfreeb4isnullandic_onhandnum.vfree4isnull)AND(ic_onhandnum_b.vfreeb5=ic_onhandnum.vfree5oric_onhandnum_b.vfreeb5isnullandic_onhandnum.vfree5isnull)AND(ic_onhandnum_b.vfreeb6=ic_onhandnum.vfree6oric_onhandnum_b.vfreeb6isnullandic_onhandnum.vfree6isnull)62/70AND(ic_onhandnum_b.vfreeb7=ic_onhandnum.vfree7oric_onhandnum_b.vfreeb7isnullandic_onhandnum.vfree7isnull)AND(ic_onhandnum_b.vfreeb8=ic_onhandnum.vfree8oric_onhandnum_b.vfreeb8isnullandic_onhandnum.vfree8isnull)AND(ic_onhandnum_b.vfreeb9=ic_onhandnum.vfree9oric_onhandnum_b.vfreeb9isnullandic_onhandnum.vfree9isnull)AND(ic_onhandnum_b.vfreeb10=ic_onhandnum.vfree10oric_onhandnum_b.vfreeb10isnullandic_onhandnum.vfree10isnull))wherepk_onhandnum='_________N/A________'补全货位现存量中现存量.sql(六)--补全货位现存量中现存量2insertintoic_onhandnum63/70(pk_onhandnum,pk_corp,ccalbodyid,cwarehouseid,cinventoryid,cinvbasid,vlot,castunitid,cvendorid,hsl,vfree1,vfree2,vfree3,vfree4,vfree5,vfree6,vfree7,vfree8,vfree9,vfree10)select'OF1312231521'||TO_CHAR(10000000+rownum)aspk_onhandnum,pk_corp,64/70ccalbodyidb,cwarehouseidb,cinventoryidb,cinvbasid,vlotb,castunitidb,cvendorid,hsl,vfreeb1,vfreeb2,vfreeb3,vfreeb4,vfreeb5,vfreeb6,vfreeb7,vfreeb8,vfreeb9,vfreeb10from(selectpk_corp,ccalbodyidb,cwarehouseidb,cinventoryidb,65/70cinvbasid,vlotb,castunitidb,cvendorid,hsl,vfreeb1,vfreeb2,vfreeb3,vfreeb4,vfreeb5,vfreeb6,vfreeb7,vfreeb8,vfreeb9,vfreeb10from(selectpk_corp,ccalbodyidb,cwarehouseidb,cinventoryidb,cinvbasid,vlotb,castunitidb,66/70cvendorid,hsl,vfreeb1,vfreeb2,vfreeb3,vfreeb4,vfreeb5,vfreeb6,vfreeb7,vfreeb8,vfreeb9,vfreeb10,sum(isnull(nnum,0))asnsumnum,sum(isnull(nastnum,0))asnsumastnumfromic_onhandnum_bwherepk_onhandnum='_________N/A________'groupbypk_corp,ccalbodyidb,cwarehouseidb,cinventoryidb,cinvbasid,vlotb,67/70castunitidb,cvendorid,hsl,vfreeb1,vfreeb2,vfreeb3,vfreeb4,vfreeb5,vfreeb6,vfreeb7,vfreeb8,vfreeb9,vfreeb10)tempwheretemp.nsumnum=0andtemp.nsumastnum=0andnotexists(selectpk_onhandnumfromic_onhandnumwheretemp.cinventoryidb=ic_onhandnum.cinventoryidANDtemp.cwarehouseidb=ic_onhandnum.cwarehouseidANDtemp.ccalbodyidb=ic_onhandnum.ccalbodyid68/70AND(temp.pk_corp=ic_onhandnum.pk_corp)AND(temp.cvendorid=ic_onhandnum.cvendoridortemp.cvendoridisnullandic_onhandnum.cvendoridisnull)AND(temp.hsl=ic_onhandnum.hslortemp.hslisnullandic_onhandnum.hslisnull)AND(temp.vlotb=ic_onhandnum.vlotortemp.vlotbisnullandic_onhandnum.vlotisnull)AND(temp.castunitidb=ic_onhandnum.castunitidortemp.castunitidbisnullandic_onhandnum.castunitidisnull)AND(temp.vfreeb1=ic_onhandnum.vfree1ortemp.vfreeb1isnullandic_onhandnum.vfree1isnull)AND(temp.vfreeb2=ic_onhandnum.vfree2ortemp.vfreeb2isnullandic_onhandnum.vfree2isnull)AND(temp.vfreeb3=ic_onhandnum.vfree3ortemp.vfreeb3isnullandic_onhandnum.vfree3isnull)AND(temp.vfreeb4=ic_onhandnum.vfree4ortemp.vfreeb4isnulland69/70ic_onhandnum.vfree4isnull)AND(temp.vfreeb5=ic_onhandnum.vfree5ortemp.vfreeb5isnullandic_onhandnum.vfree5isnull)AND(temp.vfreeb6=ic_onhandnum.vfree6ortemp.vfreeb6isnullandic_onhandnum.vfree6isnull)AND(temp.vfreeb7=ic_onhandnum.vfree7ortemp.vfreeb7isnullandic_onhandnum.vfree7isnull)AND(temp.vfreeb8=ic_onhandnum.vfree8ortemp.vfreeb8isnullandic_onhandnum.vfree8isnull)AND(temp.vfreeb9=ic_onhandnum.vfree9ortemp.vfreeb9isnullandic_onhandnum.vfree9isnull)AND(temp.vfreeb10=ic_onhandnum.vfree10ortemp.vfreeb10isnullandic_onhandnum.vfree10isnull)))tbl70/70四、特殊说明注意:以下方案请现在测试环境测试通过再同步正式环境，同时调整过程中where条件必须保持一致，不然无法调整正确。ccalbodyid库存组织PKcwarehouseid仓库PKpk_corp公司
