SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_LotAttr]
AS
SELECT dbo.SOP10201.SOPTYPE,
dbo.SOP10201.SOPNUMBE, dbo.SOP10201.LNITMSEQ, dbo.SOP10201.CMPNTSEQ,
dbo.SOP10201.QTYTYPE, dbo.SOP10201.SERLTNUM, dbo.SOP10201.SERLTQTY,
dbo.SOP10201.SLTSQNUM, dbo.SOP10201.DATERECD,
dbo.SOP10201.DTSEQNUM,
dbo.SOP10201.UNITCOST, dbo.SOP10201.ITEMNMBR, dbo.SOP10201.TRXSORCE,
dbo.SOP10201.POSTED, dbo.SOP10201.OVRSERLT, dbo.SOP10201.BIN,
dbo.SOP10201.MFGDATE, dbo.SOP10201.EXPNDATE,
dbo.SOP10201.DEX_ROW_ID,
dbo.IV00301.ITEMNMBR AS Expr1, dbo.IV00301.LOTNUMBR, dbo.IV00301.LOTATRB1,
dbo.IV00301.LOTATRB2, dbo.IV00301.LOTATRB3, dbo.IV00301.LOTATRB4 AS BestBefore,
dbo.IV00301.LOTATRB5,
dbo.IV00300.MFGDATE
AS Manufactured, dbo.IV00300.EXPNDATE AS Expiry, dbo.IV00300.DATERECD AS
Received
FROM
dbo.SOP10201 INNER JOIN
dbo.SOP10200 ON dbo.SOP10201.SOPTYPE = dbo.SOP10200.SOPTYPE AND
dbo.SOP10201.SOPNUMBE = dbo.SOP10200.SOPNUMBE AND dbo.SOP10201.LNITMSEQ =
dbo.SOP10200.LNITMSEQ AND
dbo.SOP10201.CMPNTSEQ
= dbo.SOP10200.CMPNTSEQ LEFT OUTER JOIN
dbo.IV00300 ON dbo.SOP10201.ITEMNMBR = dbo.IV00300.ITEMNMBR AND
dbo.SOP10201.SERLTNUM = dbo.IV00300.LOTNUMBR AND dbo.SOP10200.LOCNCODE =
dbo.IV00300.LOCNCODE LEFT OUTER JOIN
dbo.IV00301 ON dbo.SOP10201.SERLTNUM = dbo.IV00301.LOTNUMBR AND
dbo.SOP10201.ITEMNMBR = dbo.IV00301.ITEMNMBR
GO
/****** Object: View [dbo].[BI_SOP_BulkPick_Lots] Script Date: 16/10/2019 09:41:13 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[BI_SOP_BulkPick_Lots]
AS
SELECT PHolds.SOPNUMBE AS IsPHold, dbo.SOP10200.SOPTYPE, dbo.SOP10200.SOPNUMBE, dbo.SOP10100.ORIGTYPE, dbo.SOP10100.ORIGNUMB, dbo.SOP10100.DOCID, dbo.SOP10100.DOCDATE, dbo.SOP10100.GLPOSTDT, dbo.SOP10100.ReqShipDate,
dbo.SOP10100.CUSTNMBR, dbo.SOP10100.CUSTNAME, dbo.SOP10100.CSTPONBR, dbo.SOP10200.ITEMNMBR, dbo.SOP10200.ITEMDESC, dbo.SOP10200.UOFM, dbo.SOP10200.LOCNCODE, dbo.SOP10200.UNITCOST, dbo.SOP10200.UNITPRCE,
dbo.SOP10200.XTNDPRCE, ISNULL(dbo.BI_SOP_LotAttr.SERLTQTY, dbo.SOP10200.QUANTITY) AS QUANTITY, dbo.SOP10200.ATYALLOC, dbo.SOP10200.QTYBSUOM, dbo.SOP10200.SALSTERR, dbo.SOP10200.SLPRSNID, RTRIM(dbo.SOP10200.SOPNUMBE)
+ ' | ' + CAST(dbo.SOP10100.DOCDATE AS varchar) + ' | ' + RTRIM(dbo.SOP10100.CUSTNMBR) + ' ' + RTRIM(dbo.SOP10100.ShipToName) + ' | ' + RTRIM(dbo.SOP10200.ADDRESS1) + RTRIM(dbo.SOP10200.ADDRESS2) + RTRIM(dbo.SOP10200.ADDRESS3)
+ RTRIM(dbo.SOP10200.CITY) AS SOPLbl, dbo.SOP10100.PCKSLPNO, dbo.SOP10100.PICTICNU, dbo.SOP10100.ShipToName, dbo.SOP10100.ADDRESS1, dbo.SOP10100.ADDRESS2, dbo.SOP10100.ADDRESS3, dbo.SOP10100.CITY,
RTRIM(dbo.SOP10200.ADDRESS1) + RTRIM(dbo.SOP10200.ADDRESS2) + RTRIM(dbo.SOP10200.ADDRESS3) + RTRIM(dbo.SOP10200.CITY) AS FullAdd, ISNULL(dbo.BI_SOP_LotAttr.SERLTQTY, dbo.SOP10200.QTYTOINV) AS QTYTOINV, dbo.RM00101.HOLD,
dbo.IV00101.ITEMSHWT, dbo.BI_SOP_LotAttr.BestBefore, dbo.BI_SOP_LotAttr.SERLTQTY
FROM dbo.IV00101 INNER JOIN
dbo.SOP10200 INNER JOIN
dbo.SOP10100 ON dbo.SOP10200.SOPTYPE = dbo.SOP10100.SOPTYPE AND dbo.SOP10200.SOPNUMBE = dbo.SOP10100.SOPNUMBE INNER JOIN
dbo.RM00101 ON dbo.SOP10100.CUSTNMBR = dbo.RM00101.CUSTNMBR ON dbo.IV00101.ITEMNMBR = dbo.SOP10200.ITEMNMBR LEFT OUTER JOIN
dbo.BI_SOP_LotAttr ON dbo.SOP10200.CMPNTSEQ = dbo.BI_SOP_LotAttr.CMPNTSEQ AND dbo.SOP10200.ITEMNMBR = dbo.BI_SOP_LotAttr.ITEMNMBR AND dbo.SOP10200.SOPTYPE = dbo.BI_SOP_LotAttr.SOPTYPE AND
dbo.SOP10200.SOPNUMBE = dbo.BI_SOP_LotAttr.SOPNUMBE AND dbo.SOP10200.LNITMSEQ = dbo.BI_SOP_LotAttr.LNITMSEQ LEFT OUTER JOIN
(SELECT DISTINCT SOPNUMBE
FROM dbo.SOP10104
WHERE (DELETE1 = 0)) AS PHolds ON dbo.SOP10100.SOPNUMBE = PHolds.SOPNUMBE
WHERE (dbo.SOP10200.SOPTYPE = 2) AND (dbo.SOP10100.PICTICNU > '0') AND (PHolds.SOPNUMBE IS NULL)
GO
No comments:
Post a Comment