U8数据库学习

U8系统用到的几个表与视图

数据库为账套数据库

名称	说明
PU_AppVouch	请购单主表
PU_AppVouchs	请购单⼦表
Inventory	存货档案
zpurpoheader	采购订单视图

工艺路线一览表

--- 工艺路线一览表 ---
SELECT 
a.cinvcode as 物料编码,
g.cInvName AS 物料名称,
g.cInvStd AS 规格型号,
'被公用'=case when SharingPartId='0' then 
'否' else '是' end,
c.OpSeq as 工序行号,c.Description as 工序说明,
e.Description as 工作中心,
'类型'=case when b.RountingType='1' then '主' else '替代' end,
b.Version as 版本号,
b.IdentDesc as 替代说明,
f.cPersonName as 建档人,
b.Createdate as 建档日期,
Person1.cPersonName as 修改人,
b.ModifyDate as 修改日期,
Person2.cPersonName as 审核人,
b.relsDate as 审核日期,
b.VersionDesc as 版本说明,
b.IdentCode as 替代标识
FROM v_sfc_proutingpart_rpt a
LEFT JOIN v_sfc_prouting_rpt b ON a.PRoutingId = b.PRoutingId
LEFT JOIN v_sfc_proutingdetail_rpt c ON b.PRoutingId=c.PRoutingId
left join v_sfc_operation_rpt d on c.OperationId = d.OperationId
LEFT JOIN sfc_workcenter e ON c.WcId = e.WcId
LEFT JOIN Person f ON b.CreateUser = f.cPersonCode
LEFT JOIN Person Person1 ON b.ModifyUser = Person1.cPersonCode
LEFT JOIN Person Person2 ON b.relsUser = Person2.cPersonCode
LEFT JOIN Inventory g ON a.cinvcode = g.cinvcode
order by a.cinvcode

后台导出请购单列表：

---PU_AppVouch 请购单主表
---PU_AppVouchs 请购单⼦表
---Inventory 存货档案

select a.cCode as'单据编码',
a.ddate as'制单⽇期',
a.cBusType as'业务类型',
a.cMaker as'制单⼈',
a.cVerifier as'审核⼈',
a.cDefine14 as'请购部门',
b.cInvCode as'存货编码',
c.cInvName as'存货名称',
c.cInvStd as'规格型号',
b.fQuantity as'数量',
b.cbMemo as'备注'
from PU_AppVouch a join PU_AppVouchs b on a.ID=b.id join Inventory c on b.cInvCode=c.cInvCode  
where dDate>='2020-5-01 00:00:00.000'order by dDate;

采购订单表
Select cpoid as 订单号码,dpodate 采购⽇期,cbustype as 业务类型,cvencode as 供应单位,cvenname as 供应商名称,
cinvcode 存货编码,
cinvname 存货名称,
cinvstd  规格型号,
iquantity as 数量,
inum as 件数,
iarrqty as 累计到货数量,
iarrqty as 累计到货数量,
ireceivedqty  as 累计⼊库数量,
iinvqty  as 累计开票数量,
iunitprice as 单价,
imoney as 原币⾦额,
itax   as 税额,
isum   as 价税合计,
darrivedate as 计划到货⽇期,
cmemo  as 备注,cexch_name as 币种,cmaker as 创建⼈,cverifier as 审批⼈,cptname as 采购类型,cvenaddress as 地址
 From zpurpoheader 
 left join zPurpotail on zpurpoheader.POID=zPurpotail.POID  
Where  1=1  and ( 1=1   And ((dPODate >= N'2015-06-01') And (dPODate <= N'2015-06-05'))

SELECT a.ivtid,
       a.cpoid AS 订单号码,
       a.dpodate 采购日期,
       a.cbustype AS 业务类型,
       a.cvencode AS 供应单位,
       a.cvenname AS 供应商名称,
       a.cmemo AS 备注,
       a.cexch_name AS 币种,
       a.cmaker AS 创建人,
       a.cverifier AS 审批人,
       a.cptname AS 采购类型,
       a.cvenaddress AS 地址,
       a.cvoucherstate 订单状态,
       b.行数,
       b.数量,
       b.累计入库数量,
       b.数量 - b.累计入库数量 剩余数量
FROM zpurpoheader a
    LEFT JOIN
    (
        SELECT poid,
               COUNT(*) 行数,
               SUM(CONVERT(FLOAT, iquantity)) 数量,
               SUM(CONVERT(FLOAT, iarrqty)) 累计到货数量,
               SUM(CONVERT(FLOAT, ireceivedqty)) 累计入库数量
        FROM dbo.zpurpotail
        GROUP BY poid
    ) b
        ON b.poid = a.poid
WHERE b.累计入库数量 = 0

-- （3）明细表：从采购单入手，获取采购明细；再根据行号，获取相应入库单。

SELECT a.cpoid AS 订单号码,
       a.dpodate 采购日期,
       a.cbustype AS 业务类型,
       a.cvenname AS 供应商名称,
       a.cvenaddress 地址,
       a.cvoucherstate 采购单状态,
       a.cmemo 备注,
       a.cexch_name 币种,
       RIGHT(REPLICATE('0', 6) + LTRIM(b.ivouchrowno), 6) 行号,
       b.cinvcode 存货编码,
       b.cinvname 存货名称,
       b.cinvstd 规格型号,
       CONVERT(FLOAT, b.iquantity) 数量,
       CONVERT(FLOAT, b.ireceivedqty) 累计入库数量,
       CONVERT(FLOAT, b.iinvqty) 累计开票数量,
       rd.cBatch 批号,
       PARSENAME(REPLACE(STUFF(rd.cbsysbarcode, 1, 2, ''), '|', '.'), 2) 入库单号,
       a.cmaker 创建人,
       a.cverifier 审批人,
       a.cptname 采购类型
FROM zpurpoheader a --采购订单
    LEFT JOIN zpurpotail b
        ON a.poid = b.poid
    LEFT JOIN rdrecords01 rd
        ON rd.cPOID = a.cpoid
           AND rd.irowno = b.ivouchrowno
WHERE a.cpoid = '订单号';