IF EXISTS(SELECT name FROM sysobjects WHERE name=N'PU_PO_ArrivalArriveDate' AND xtype='V')
DROP VIEW PU_PO_Arrival_Rd
GO
CREATE VIEW PU_PO_ArrivalArriveDate
AS
SELECT POID, --请购采购交期对照表 2008-06-25
cPOID, iAppIds, ID, dPODate, POcBusType, POcVenCode, cVenAbbName, POcPersonCode, POcMaker, POcCloser, POcInvCode AS Part, cInvName, cInvStd, POiQuantity,
dArriveDate, AppsID, PUAutoID, PUcCode, PUdDate, PUcBusType, AppcInvCode, PUfQuantity, PUdReQuirDate, PUiReceivedQTY,
ArrivaliPOsID, ArrivalcInvCode, MinArrivaldDate, ArrivalQTY ,
CASE WHEN PUdReQuirDate>'2006-01-01' AND dArriveDate >'2006-01-01' AND DATEDIFF(DAY,PUdReQuirDate,dArriveDate)>0 THEN '延期采购'
WHEN PUdReQuirDate BETWEEN '2006-01-01' AND GETDATE() AND dArriveDate < '2006-01-01' THEN '延期采购'
WHEN PUdReQuirDate>'2006-01-01' AND dArriveDate >'2006-01-01' AND DATEDIFF(DAY,PUdReQuirDate,dArriveDate)=0 THEN '实时采购'
WHEN PUdReQuirDate>'2006-01-01' AND dArriveDate >'2006-01-01' AND DATEDIFF(DAY,PUdReQuirDate,dArriveDate)<0 THEN '提前采购'
ELSE '不明确' END AS POStatus,
CASE WHEN (MinArrivaldDate <dArriveDate AND DAY(dArriveDate)<27) THEN '先行交货'
WHEN (MinArrivaldDate =dArriveDate AND DAY(dArriveDate)<27) OR DAY(dArriveDate)>=27 THEN '正常交货'
WHEN MinArrivaldDate > dArriveDate AND DAY(dArriveDate)<27 THEN '延期交货'
WHEN MinArrivaldDate IS NULL AND DAY(dArriveDate)<27 THEN '物料未归'
ELSE '不明确' END AS ArriStatus
FROM (SELECT PO_POMain.POID, --采购单 --查有已做采购单的请购单
PO_POMain.cPOID, PO_PODetails.iAppIds, PO_PODetails.ID, PO_POMain.dPODate, PO_POMain.cBusType AS POcBusType, PO_POMain.cVenCode AS POcVenCode, Vendor.cVenAbbName,
PO_POMain.cPersonCode AS POcPersonCode, PO_POMain.cMaker AS POcMaker, PO_POMain.cCloser AS POcCloser,PO_PODetails.cInvCode AS POcInvCode,Inventory.cInvName,
Inventory.cInvStd, PO_PODetails.iQuantity AS POiQuantity, PO_PODetails.dArriveDate, PU_AppVouch.ID AS AppsID, --请购单
PU_AppVouchs.AutoID AS PUAutoID, PU_AppVouch.cCode AS PUcCode, PU_AppVouch.dDate AS PUdDate, PU_AppVouch.cBusType AS PUcBusType,
PU_AppVouchs.cInvCode AS AppcInvCode, PU_AppVouchs.fQuantity AS PUfQuantity, PU_AppVouchs.dRequirDate AS PUdReQuirDate, PU_AppVouchs.iReceivedQTY AS PUiReceivedQTY
FROM PO_POMain, PO_PODetails,PU_AppVouch, PU_AppVouchs,Inventory,Vendor
WHERE PO_POMain.POID = PO_PODetails.POID AND
PU_AppVouchs.ID = PU_AppVouch.ID + 0 AND
PO_PODetails.cInvCode=Inventory.cInvCode AND
PO_POMain.cVenCode=Vendor.cVenCode AND
PO_POMain.dPODate >= '2007-01-01' AND
PO_PODetails.iAppIds = PU_AppVouchs.AutoID
UNION
SELECT 0 AS POID, --采购单 --查所有未下采购单的请购单
'' AS cPOID, 0 AS iAppIds, 0 AS ID, '1900-01-01' AS dPODate, '' AS POcBusType, '' AS POcVenCode, '' AS cVenAbbName, '' AS POcPersonCode, '' AS POcMaker, '' AS POcCloser,
PU_AppVouchs.cInvCode AS POcInvCode, Inventory.cInvName, Inventory.cInvStd, 0 AS POiQuantity, '1900-01-01' AS dArriveDate,PU_AppVouch.ID AS AppsID, --请购单
PU_AppVouchs.AutoID AS PUAutoID, PU_AppVouch.cCode AS PUcCode, PU_AppVouch.dDate AS PUdDate, PU_AppVouch.cBusType AS PUcBusType, PU_AppVouchs.cInvCode AS AppcInvCode,
PU_AppVouchs.fQuantity AS PUfQuantity, PU_AppVouchs.dRequirDate AS PUdReQuirDate, PU_AppVouchs.iReceivedQTY AS PUiReceivedQTY
FROM PU_AppVouch,PU_AppVouchs,Inventory
WHERE PU_AppVouchs.ID = PU_AppVouch.ID+0 AND
PU_AppVouchs.cInvCode=Inventory.cInvCode AND
PU_AppVouchs.iReceivedQTY=0
UNION
SELECT PO_POMain.POID, --采购单 --查所有直接采购
PO_POMain.cPOID, PO_PODetails.iAppIds, PO_PODetails.ID, PO_POMain.dPODate, PO_POMain.cBusType AS POcBusType, PO_POMain.cVenCode AS POcVenCode,
Vendor.cVenAbbName,PO_POMain.cPersonCode AS POcPersonCode, PO_POMain.cMaker AS POcMaker, PO_POMain.cCloser AS POcCloser, PO_PODetails.cInvCode AS POcInvCode, Inventory.cInvName,
Inventory.cInvStd,PO_PODetails.iQuantity AS POiQuantity, PO_PODetails.dArriveDate,0 AS AppsID, --请购单
0 AS PUAutoID, '' AS PUcCode, '1900-01-01' AS PUdDate, '' AS PUcBusType, '' AS AppcInvCode, 0 AS PUfQuantity, '1900-01-01' AS PUdReQuirDate, 0 AS PUiReceivedQTY
FROM PO_POMain, PO_PODetails, Inventory,Vendor
WHERE PO_POMain.POID = PO_PODetails.POID AND
PO_POMain.cBusType='普通采购' AND
PO_PODetails.cInvCode=Inventory.cInvCode AND
PO_POMain.cVenCode=Vendor.cVenCode AND
PO_POMain.dPODate >= '2007-01-01'AND
PO_PODetails.cSource IS NULL) A
LEFT OUTER JOIN(SELECT PU_ArrivalVouchs.iPOsID AS ArrivaliPOsID, --查到货单情况，第一次到货日期
PU_ArrivalVouchs.cInvCode AS ArrivalcInvCode, MIN(PU_ArrivalVouch.dDate) AS MinArrivaldDate, SUM(iQuantity) AS ArrivalQTY
FROM PU_ArrivalVouch, PU_ArrivalVouchs
WHERE PU_ArrivalVouch.ID = PU_ArrivalVouchs.ID AND
PU_ArrivalVouchs.iQuantity > 0 ---因为到货单的日期一定大于退货单和据收单，
GROUP BY PU_ArrivalVouchs.iPOsID,
PU_ArrivalVouchs.cInvCode) AS Arrival
ON A.ID = Arrival.ArrivaliPOsID AND
A.POcInvCode = Arrival.ArrivalcInvCode
