USE [UFDATA_002_2018]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[FHS_View_DeliveryControl]
AS
SELECt '' AS lid,
PUAS.AutoID AS 请购单AutoID,
PUAS.ID AS 请购ID,
PUA.dDate AS 请购单日期,
MonTH(PUA.dDate) AS [请购单日期(月份)],
CAST(MonTH(PUA.dDate) AS VARCHAr(50)) + '月' + CAST(DAY(PUA.dDate) AS VARCHAr(50)) + '日' AS [请购单日期(月日)],
PUA.cAuditDate AS 请购单审核日期,
PUAS.cItemCode AS 项目号,
PUAS.cDefine22 AS 模组号,
'' AS 交期属性,
PUAS.cDefine29 AS 工程师,
Inv.cInvCode AS 存货编码,
Inv.cInvName AS 存货名称,
Inv.cInvStd AS 规格,
Inv.cAddress AS 品牌,
PUAS.fQuantity AS 请购数量,
Unit.cComUnitName AS 单位,
PUAS.dRequirDate AS 需求日期,
PUA.cCode AS 请购单号,
POM.cPOID AS 采购单号,
POD.irowno AS 采购单行号,
POD.ID AS 采购单ID,
POM.cPersonCode AS 采购员编码,
(
SELECT cPsn_Name FROM dbo.hr_hi_person WHERe cPsn_Num = POM.cPersonCode
) AS 采购员姓名,
POM.cMaker AS 采购订单制单人,
POM.dPODate AS 采购订单制单日期,
POD.dArriveDate AS 预计到货日期,
POD.cDefine36 AS 最新变更交期,
POM.cAuditDate AS 采购订单审核日期,
POD.iQuantity AS 订货数量,
'' AS 是否急件,
POD.cDefine32 AS 交期异常原因,
POD.cDefine30 AS 请购变更原因,
POD.cDefine28 AS 责任部门,
POD.cDefine31 AS ECR,
POD.cDefine34 AS 预计金额,
DATEDIFF(dd, PUA.dDate, POM.cAuditDate) AS 下单天数,
'' AS 到货单号,
'' AS 到货单AutoId,
'' AS 到货单行号,
'' AS 收货制单人,
'' AS 到货制单日期,
'' AS 到货审核日期,
'' AS 到货数量,
'' AS 现存量,
RD01.cCode AS 入库单号,
RDS01.AutoID AS 入库单AutoId,
RDS01.irowno AS 入库单行号,
RD01.cMaker AS 入库单制单人,
RDS01.iQuantity AS 入库数量,
RD01.dDate AS 入库单日期,
RD01.dVeriDate AS 入库单审核日期,
PUAS.cbcloser AS 请购关闭人,
POD.cbCloser AS 订单关闭人,
RD01.cVenCode AS 供应商编码,
UFDATA_002_2018.dbo.Vendor.cVenName AS 供应商名称,
PUAS.cbMemo,
CAST(PUAS.fQuantity -
(
SELECt ISNULL(SUM(iQuantity), 0)
FROM UFDATA_002_2018.dbo.PO_Podetails
INNER JOIN UFDATA_002_2018.dbo.PO_Pomain
ON UFDATA_002_2018.dbo.PO_Podetails.POID = UFDATA_002_2018.dbo.PO_Pomain.POID
WHERe UFDATA_002_2018.dbo.PO_Podetails.iAppIds = PUAS.AutoID
AND UFDATA_002_2018.dbo.PO_Pomain.iverifystateex = 2
) AS DECIMAL(18, 0)) AS '请购单转单未完成数量',
CAST(
(
SELECt ISNULL(SUM(iQuantity), 0)
FROM UFDATA_002_2018.dbo.PO_Podetails
INNER JOIN UFDATA_002_2018.dbo.PO_Pomain
ON UFDATA_002_2018.dbo.PO_Podetails.POID = UFDATA_002_2018.dbo.PO_Pomain.POID
WHERe UFDATA_002_2018.dbo.PO_Podetails.iAppIds = PUAS.AutoID
AND UFDATA_002_2018.dbo.PO_Pomain.iverifystateex = 2
) -
(
SELECt ISNULL(SUM(iQuantity), 0)
FROM UFDATA_002_2018.dbo.rdrecords01
INNER JOIN UFDATA_002_2018.dbo.RdRecord01
ON UFDATA_002_2018.dbo.RdRecord01.ID = UFDATA_002_2018.dbo.rdrecords01.ID
WHERe UFDATA_002_2018.dbo.rdrecords01.iPOsID = POD.ID
AND UFDATA_002_2018.dbo.RdRecord01.dnverifytime IS NOT NULL
) AS DECIMAL(18, 0)) AS '采购入库未完成数',
'测控' AS '账套',
--(SELECt ISNULL(SUM(iQuantity),0) FROM PO_Podetails INNER JOIN dbo.PO_Pomain ON PO_Podetails.POID=PO_Pomain.POID WHERe PO_Podetails.iAppIds=PUAS.AutoID AND PO_Pomain.iverifystateex=2),
--(SELECt ISNULL(SUM(iQuantity),0) FROM rdrecords01 INNER JOIN RdRecord01 ON RdRecord01.ID=rdrecords01.ID WHERe rdrecords01.iPOsID=POD.ID AND rdrecord01.dnverifytime IS NOT null)
CAST(POD.iNatUnitPrice AS DECIMAL(30, 4)) AS '本币未税单价',
POD.iNatMoney AS '本币无税金额'
FROM UFDATA_002_2018.dbo.PU_AppVouchs AS PUAS
LEFT OUTER JOIN UFDATA_002_2018.dbo.PO_Podetails AS POD
ON PUAS.AutoID = POD.iAppIds
LEFT OUTER JOIN UFDATA_002_2018.dbo.PO_Pomain AS POM
ON POM.POID = POD.POID
LEFT OUTER JOIN UFDATA_002_2018.dbo.rdrecords01 AS RDS01
ON RDS01.iPOsID = POD.ID
LEFT OUTER JOIN UFDATA_002_2018.dbo.RdRecord01 AS RD01
ON RD01.ID = RDS01.ID
INNER JOIN UFDATA_002_2018.dbo.PU_AppVouch AS PUA
ON PUAS.ID = PUA.ID
AND PUA.iverifystateex = 2
LEFT JOIN UFDATA_002_2018.dbo.Inventory AS Inv
ON PUAS.cInvCode = Inv.cInvCode
LEFT JOIN UFDATA_002_2018.dbo.Vendor
ON UFDATA_002_2018.dbo.Vendor.cVenCode = POM.cVenCode
LEFT JOIN UFDATA_002_2018.dbo.ComputationUnit AS Unit
ON Unit.cComunitCode = Inv.cComUnitCode
--WHERe PUAS.cItemCode='A17053'
--ORDER BY PUA.cCode DESC
UNIOn ALL
SELECt '' AS lid,
PUAS.AutoID AS 请购单AutoID,
PUAS.ID AS 请购ID,
PUA.dDate AS 请购单日期,
MonTH(PUA.dDate) AS [请购单日期(月份)],
CAST(MonTH(PUA.dDate) AS VARCHAr(50)) + '月' + CAST(DAY(PUA.dDate) AS VARCHAr(50)) + '日' AS [请购单日期(月日)],
PUA.cAuditDate AS 请购单审核日期,
PUAS.cItemCode AS 项目号,
PUAS.cDefine22 AS 模组号,
'' AS 交期属性,
PUAS.cDefine28 AS 工程师,
Inv.cInvCode AS 存货编码,
Inv.cInvName AS 存货名称,
Inv.cInvStd AS 规格,
Inv.cAddress AS 品牌,
PUAS.fQuantity AS 请购数量,
Unit.cComUnitName AS 单位,
PUAS.dRequirDate AS 需求日期,
PUA.cCode AS 请购单号,
POM.cPOID AS 采购单号,
POD.irowno AS 采购单行号,
POD.ID AS 采购单ID,
POM.cPersonCode AS 采购员编码,
(
SELECT cPsn_Name FROM dbo.hr_hi_person WHERe cPsn_Num = POM.cPersonCode
) AS 采购员姓名,
POM.cMaker AS 采购订单制单人,
POM.dPODate AS 采购订单制单日期,
POD.dArriveDate AS 预计到货日期,
POD.cDefine36 AS 最新变更交期,
POM.cAuditDate AS 采购订单审核日期,
POD.iQuantity AS 订货数量,
'' AS 是否急件,
POD.cDefine32 AS 交期异常原因,
POD.cDefine30 AS 请购变更原因,
POD.cDefine29 AS 责任部门,
POD.cDefine31 AS ECR,
POD.cDefine34 AS 预计金额,
DATEDIFF(dd, PUA.dDate, POM.cAuditDate) AS 下单天数,
'' AS 到货单号,
'' AS 到货单AutoId,
'' AS 到货单行号,
'' AS 收货制单人,
'' AS 到货制单日期,
'' AS 到货审核日期,
'' AS 到货数量,
'' AS 现存量,
RD01.cCode AS 入库单号,
RDS01.AutoID AS 入库单AutoId,
RDS01.irowno AS 入库单行号,
RD01.cMaker AS 入库单制单人,
RDS01.iQuantity AS 入库数量,
RD01.dDate AS 入库单日期,
RD01.dVeriDate AS 入库单审核日期,
PUAS.cbcloser AS 请购关闭人,
POD.cbCloser AS 采购关闭人,
RD01.cVenCode AS 供应商编码,
UFDATA_003_2018.dbo.Vendor.cVenName AS 供应商名称,
PUAS.cbMemo,
CAST(PUAS.fQuantity -
(
SELECt ISNULL(SUM(iQuantity), 0)
FROM UFDATA_003_2018.dbo.PO_Podetails
INNER JOIN UFDATA_003_2018.dbo.PO_Pomain
ON UFDATA_003_2018.dbo.PO_Podetails.POID = UFDATA_003_2018.dbo.PO_Pomain.POID
WHERe UFDATA_003_2018.dbo.PO_Podetails.iAppIds = PUAS.AutoID
AND UFDATA_003_2018.dbo.PO_Pomain.iverifystateex = 2
) AS DECIMAL(18, 0)) AS '请购单转单未完成数量',
CAST(
(
SELECt ISNULL(SUM(iQuantity), 0)
FROM UFDATA_003_2018.dbo.PO_Podetails
INNER JOIN UFDATA_003_2018.dbo.PO_Pomain
ON UFDATA_003_2018.dbo.PO_Podetails.POID = UFDATA_003_2018.dbo.PO_Pomain.POID
WHERe UFDATA_003_2018.dbo.PO_Podetails.iAppIds = PUAS.AutoID
AND UFDATA_003_2018.dbo.PO_Pomain.iverifystateex = 2
) -
(
SELECt ISNULL(SUM(iQuantity), 0)
FROM UFDATA_003_2018.dbo.rdrecords01
INNER JOIN UFDATA_003_2018.dbo.RdRecord01
ON UFDATA_003_2018.dbo.RdRecord01.ID = UFDATA_003_2018.dbo.rdrecords01.ID
WHERe UFDATA_003_2018.dbo.rdrecords01.iPOsID = POD.ID
AND UFDATA_003_2018.dbo.RdRecord01.dnverifytime IS NOT NULL
) AS DECIMAL(18, 0)) AS '采购入库未完成数',
'医疗' AS '账套',
--(SELECt ISNULL(SUM(iQuantity),0) FROM PO_Podetails INNER JOIN dbo.PO_Pomain ON PO_Podetails.POID=PO_Pomain.POID WHERe PO_Podetails.iAppIds=PUAS.AutoID AND PO_Pomain.iverifystateex=2),
--(SELECt ISNULL(SUM(iQuantity),0) FROM rdrecords01 INNER JOIN RdRecord01 ON RdRecord01.ID=rdrecords01.ID WHERe rdrecords01.iPOsID=POD.ID AND rdrecord01.dnverifytime IS NOT null)
CAST(POD.iNatUnitPrice AS DECIMAL(30, 4)) AS '本币未税单价',
POD.iNatMoney AS '本币无税金额'
FROM UFDATA_003_2018.dbo.PU_AppVouchs AS PUAS
LEFT OUTER JOIN UFDATA_003_2018.dbo.PO_Podetails AS POD
ON PUAS.AutoID = POD.iAppIds
LEFT OUTER JOIN UFDATA_003_2018.dbo.PO_Pomain AS POM
ON POM.POID = POD.POID
LEFT OUTER JOIN UFDATA_003_2018.dbo.rdrecords01 AS RDS01
ON RDS01.iPOsID = POD.ID
LEFT OUTER JOIN UFDATA_003_2018.dbo.RdRecord01 AS RD01
ON RD01.ID = RDS01.ID
INNER JOIN UFDATA_003_2018.dbo.PU_AppVouch AS PUA
ON PUAS.ID = PUA.ID
AND PUA.iverifystateex = 2
LEFT JOIN UFDATA_003_2018.dbo.Inventory AS Inv
ON PUAS.cInvCode = Inv.cInvCode
LEFT JOIN UFDATA_003_2018.dbo.Vendor
ON UFDATA_003_2018.dbo.Vendor.cVenCode = POM.cVenCode
LEFT JOIN UFDATA_003_2018.dbo.ComputationUnit AS Unit
ON Unit.cComunitCode = Inv.cComUnitCode;
GO
