1/9用友股份-LE支持服务业务本部产品方案--《可用量-到货待检量后台查询sql》建立日期：2013-05-01修改日期：2013-05-01文档属性：客户文控编号：LE-DY-CP-2013-01552/9文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-05-01刘艳产品支持部V1.0版本领域模块备注3/9目录可用量-到货待检量后台查询sql......................................................................................4一、项目背景：................................................................................................................4二、方案：........................................................................................................................4(一)查询汇总的数据：........................................................................................4(二)查询明细数据：............................................................................................74/9可用量-到货待检量后台查询sql一、项目背景：可用量是NC库存管理重要的数据，但经常有项目觉得可用量中某一些数据不准，但NC中可用量展现的都是汇总的数据无从分析，具体是哪条出现的错误，下面的sql可以帮助项目后台分析数据，跟前台展现的数据做比对，快速定位具体问题单据。二、方案：(一)查询汇总的数据：select*from(selectpk_corp,ccalbodyid,cinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vbatchcode,dplandate,sum(nonreceivenum)asnonreceivenumfrom(selectpo_arriveorder.pk_corp,po_arriveorder.cstoreorganizationasccalbodyid,po_arriveorder_b.cmangidascinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vproducenumasvbatchcode,5/9'可用量合并日期'asdplandate,nvl(po_arriveorder_b.narrvnum,0)-nvl(po_arriveorder_b.naccumwarehousenum,0)asnonreceivenumfrompo_arriveorder_binnerjoinpo_arriveorderonpo_arriveorder_b.carriveorderid=po_arriveorder.carriveorderidleftouterjoinbd_busitypeonpo_arriveorder.cbiztype=bd_busitype.pk_busitypewherepo_arriveorder.ibillstatus!=4andpo_arriveorder.dr=0andpo_arriveorder_b.dr=0and(BFAFLAG='N'orBFAFLAGisnull)andbd_busitype.verifyrule!='C'andbd_busitype.verifyrule!='J'andabs(nvl(po_arriveorder_b.narrvnum,0))>abs(nvl(po_arriveorder_b.naccumwarehousenum,0))andpo_arriveorder.dreceivedate<='可用量合并日期')poviewwhere0=0and(0=0andpk_corp='公司主键'and(0=0and(pk_corp='公司主键')))Groupbypk_corp,ccalbodyid,cinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vbatchcode,dplandateunionallselectpk_corp,ccalbodyid,cinventoryid,vfree1,vfree2,vfree3,vfree4,6/9vfree5,cwarehouseid,vbatchcode,dplandate,sum(nonreceivenum)asnonreceivenumfrom(selectpo_arriveorder.pk_corp,po_arriveorder.cstoreorganizationasccalbodyid,po_arriveorder_b.cmangidascinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vproducenumasvbatchcode,po_arriveorder.dreceivedateasdplandate,nvl(po_arriveorder_b.narrvnum,0)-nvl(po_arriveorder_b.naccumwarehousenum,0)asnonreceivenumfrompo_arriveorder_binnerjoinpo_arriveorderonpo_arriveorder_b.carriveorderid=po_arriveorder.carriveorderidleftouterjoinbd_busitypeonpo_arriveorder.cbiztype=bd_busitype.pk_busitypewherepo_arriveorder.ibillstatus!=4andpo_arriveorder.dr=0andpo_arriveorder_b.dr=0and(BFAFLAG='N'orBFAFLAGisnull)andbd_busitype.verifyrule!='C'andbd_busitype.verifyrule!='J'andabs(nvl(po_arriveorder_b.narrvnum,0))>abs(nvl(po_arriveorder_b.naccumwarehousenum,0))andpo_arriveorder.dreceivedate>'可用量合并日期')poviewwhere0=0and(0=0andpk_corp='公司主键'and(0=0and(pk_corp='公司主键')))Groupbypk_corp,ccalbodyid,cinventoryid,vfree1,vfree2,7/9vfree3,vfree4,vfree5,cwarehouseid,vbatchcode,dplandate)wherecinventoryidin(selectpk_invmandocfrombd_invbasdocbd1,bd_invmandocbm1wherebd1.pk_invbasdoc=bm1.pk_invbasdocandbm1.pk_corp='公司主键'andbd1.invcode='存货编码')(二)查询明细数据：select*from(selectpk_corp,ccalbodyid,cinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vbatchcode,dplandate,(nonreceivenum)asnonreceivenum,varrordercode,crownofrom(selectpo_arriveorder.pk_corp,po_arriveorder.cstoreorganizationasccalbodyid,po_arriveorder_b.cmangidascinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vproducenumasvbatchcode,'可用量合并日期'asdplandate,nvl(po_arriveorder_b.narrvnum,0)-8/9nvl(po_arriveorder_b.naccumwarehousenum,0)asnonreceivenum,po_arriveorder.varrordercodeasvarrordercode,po_arriveorder_b.crownoascrownofrompo_arriveorder_binnerjoinpo_arriveorderonpo_arriveorder_b.carriveorderid=po_arriveorder.carriveorderidleftouterjoinbd_busitypeonpo_arriveorder.cbiztype=bd_busitype.pk_busitypewherepo_arriveorder.ibillstatus!=4andpo_arriveorder.dr=0andpo_arriveorder_b.dr=0and(BFAFLAG='N'orBFAFLAGisnull)andbd_busitype.verifyrule!='C'andbd_busitype.verifyrule!='J'andabs(nvl(po_arriveorder_b.narrvnum,0))>abs(nvl(po_arriveorder_b.naccumwarehousenum,0))andpo_arriveorder.dreceivedate<='可用量合并日期')poviewwhere0=0and(0=0andpk_corp='公司主键'and(0=0and(pk_corp='公司主键')))unionallselectpk_corp,ccalbodyid,cinventoryid,vfree1,vfree2,vfree3,vfree4,vfree5,cwarehouseid,vbatchcode,dplandate,(nonreceivenum)asnonreceivenum,varrordercode,crownofrom(selectpo_arriveorder.pk_corp,po_arriveorder.cstoreorganizationasccalbodyid,po_arriveorder_b.cmangidascinventoryid,vfree1,vfree2,9/9vfree3,vfree4,vfree5,cwarehouseid,vproducenumasvbatchcode,po_arriveorder.dreceivedateasdplandate,nvl(po_arriveorder_b.narrvnum,0)-nvl(po_arriveorder_b.naccumwarehousenum,0)asnonreceivenum,po_arriveorder.varrordercodeasvarrordercode,po_arriveorder_b.crownoascrownofrompo_arriveorder_binnerjoinpo_arriveorderonpo_arriveorder_b.carriveorderid=po_arriveorder.carriveorderidleftouterjoinbd_busitypeonpo_arriveorder.cbiztype=bd_busitype.pk_busitypewherepo_arriveorder.ibillstatus!=4andpo_arriveorder.dr=0andpo_arriveorder_b.dr=0and(BFAFLAG='N'orBFAFLAGisnull)andbd_busitype.verifyrule!='C'andbd_busitype.verifyrule!='J'andabs(nvl(po_arriveorder_b.narrvnum,0))>abs(nvl(po_arriveorder_b.naccumwarehousenum,0))andpo_arriveorder.dreceivedate>'可用量合并日期')poviewwhere0=0and(0=0andpk_corp='公司主键'and(0=0and(pk_corp='公司主键'))))wherecinventoryidin(selectpk_invmandocfrombd_invbasdocbd1,bd_invmandocbm1wherebd1.pk_invbasdoc=bm1.pk_invbasdocandbm1.pk_corp='公司主键'andbd1.invcode='存货编码')
