1/6用友股份-LE支持服务业务本部产品方案--《暂估红冲数据以审核日期出报表方案》建立日期：2013-07-01修改日期：xxxx-xx-xx文档属性：客户文控编号：LE-DY-CP-2013-04652/6文档控制创建记录适用范围审阅人日期审阅签字所属部门发布范围内部员工伙伴客户发布人姓名所属部门发布时间日期作者所属部门邮件地址版本2013-07-01刘杰冉产品支持部V1.0版本领域模块备注3/6目录暂估红冲数据以审核日期出报表方案................................................................4一、应用场景描述：...........................................................................................4二、方案实现方法：...........................................................................................44/6暂估红冲数据以审核日期出报表方案一、应用场景描述：我们目前系统里暂估统计的报表都是以业务日期来统计，大部分时候已经可以满足客户的需求，有极个别情况，成本计算跟业务不同步跨月，或财务以成本计算为准来统计数据。这样造成财务跟业务对账比较麻烦。本方案做了一个以成本计算日期来统计暂估数据的查询语句，如果需要的话，可以通过查询引擎发布成标准的查询。具体查询语句如下：二、方案实现方法：--暂估明细selecta.dauditdate,f.custcode,f.custname,a.VSOURCEBILLCODE,b.vbillcode,g.invcode,g.invname,g.invspec,g.invtype,a.nnumber,a.nprice,a.nmoneyfromIC_GENERAL_Bc,ia_bill_ba,ia_billb,BD_CUBASDOCf,BD_INVBASDOCgwherec.CGENERALBID=a.CSOURCEBILLITEMIDanda.cbillid=b.cbillidandb.ccustomvendorbasid=f.pk_cubasdocanda.cinvbasid=g.pk_invbasdocandb.bestimateflag='Y'anda.dr=0anda.CSOURCEBILLTYPECODE='45'andsubstr(a.dauditdate,1,7)='2012-11'anda.pk_corp='1002'unionallselecta.dauditdate,f.custcode,f.custname,a.VSOURCEBILLCODE,b.vbillcode,g.invcode,g.invname,g.invspec,g.invtype,a.nnumber,a.nprice,a.nmoneyfromic_vmi_sumc,ia_bill_ba,ia_billb,BD_CUBASDOCf,BD_INVBASDOCgwherec.CVMIHID=a.CSOURCEBILLITEMIDanda.cbillid=b.cbillidandb.ccustomvendorbasid=f.pk_cubasdocanda.cinvbasid=g.pk_invbasdocandb.bestimateflag='Y'anda.dr=0anda.CSOURCEBILLTYPECODE='50'andsubstr(a.dauditdate,1,7)='2012-11'anda.pk_corp='1002'--本月红冲当月——采购selectd.dauditdate,f.custcode,f.custname,d.CICBILLCODEVSOURCEBILLCODE,e.vbillcode,g.invcode,g.invname,g.invspec,g.invtype,d.nnumber,d.nprice,d.nmoneyfromIC_GENERAL_Bc,ia_bill_bd,ia_bille,BD_CUBASDOCf,BD_INVBASDOCgwherec.CGENERALBID=d.cicitemidandd.cbillid=e.cbillid5/6ande.ccustomvendorbasid=f.pk_cubasdocandd.cinvbasid=g.pk_invbasdocande.bestimateflag='Y'andd.dr=0andd.CSOURCEBILLTYPECODE='27'andexists(select1fromia_bill_ba,ia_billbwherec.CGENERALBID=a.CSOURCEBILLITEMIDanda.cbillid=b.cbillidandsubstr(d.dauditdate,1,7)=substr(a.dauditdate,1,7))andsubstr(d.dauditdate,1,7)='2012-11'andd.pk_corp='1002'unionall--本月红冲当月-消耗汇总selectd.dauditdate,f.custcode,f.custname,c.vbillcodeVSOURCEBILLCODE,e.vbillcode,g.invcode,g.invname,g.invspec,g.invtype,d.nnumber,d.nprice,d.nmoneyfromic_vmi_sumc,ia_bill_bd,ia_bille,BD_CUBASDOCf,BD_INVBASDOCgwherec.CVMIHID=d.CFIRSTBILLITEMIDandd.cbillid=e.cbillidande.ccustomvendorbasid=f.pk_cubasdoc(+)andd.cinvbasid=g.pk_invbasdocande.bestimateflag='Y'andd.dr=0andd.CSOURCEBILLTYPECODE='27'andexists(select1fromia_bill_ba,ia_billbwherec.CVMIHID=a.CSOURCEBILLITEMIDanda.cbillid=b.cbillidandsubstr(d.dauditdate,1,7)=substr(a.dauditdate,1,7))andsubstr(d.dauditdate,1,7)='2012-11'andd.pk_corp='1002'--本月红冲往月-采购selectd.dauditdate,f.custcode,f.custname,d.CICBILLCODEVSOURCEBILLCODE,e.vbillcode,g.invcode,g.invname,g.invspec,g.invtype,d.nnumber,d.nprice,d.nmoneyfromIC_GENERAL_Bc,ia_bill_bd,ia_bille,BD_CUBASDOCf,BD_INVBASDOCgwherec.CGENERALBID=d.cicitemidandd.cbillid=e.cbillidande.ccustomvendorbasid=f.pk_cubasdocandd.cinvbasid=g.pk_invbasdocande.bestimateflag='Y'andd.dr=0andd.CSOURCEBILLTYPECODE='27'andexists(select1fromia_bill_ba,ia_billbwherec.CGENERALBID=a.CSOURCEBILLITEMIDanda.cbillid=b.cbillidandsubstr(d.dauditdate,1,7)<>substr(a.dauditdate,1,7))andsubstr(d.dauditdate,1,7)='2012-11'andd.pk_corp='1002'unionall--本月红冲往月-消耗汇总selectd.dauditdate,f.custcode,f.custname,c.vbillcodeVSOURCEBILLCODE,e.vbillcode,g.invcode,g.invname,g.invspec,g.invtype,d.nnumber,d.nprice,d.nmoneyfromic_vmi_sumc,ia_bill_bd,ia_bille,BD_CUBASDOCf,BD_INVBASDOCgwherec.CVMIHID=d.CFIRSTBILLITEMIDandd.cbillid=e.cbillid6/6ande.ccustomvendorbasid=f.pk_cubasdoc(+)andd.cinvbasid=g.pk_invbasdocande.bestimateflag='Y'andd.dr=0andd.CSOURCEBILLTYPECODE='27'andexists(select1fromia_bill_ba,ia_billbwherec.CVMIHID=a.CSOURCEBILLITEMIDanda.cbillid=b.cbillidandsubstr(d.dauditdate,1,7)<>substr(a.dauditdate,1,7))andsubstr(d.dauditdate,1,7)='2012-11'andd.pk_corp='1002'
