USE TABER;
IF OBJECT_ID('dbo.uvw_PurchaseOrdersHeaders','V') IS NOT NULL DROP VIEW dbo.uvw_PurchaseOrdersHeaders;
GO

CREATE VIEW dbo.uvw_PurchaseOrdersHeaders
WITH VIEW_METADATA
AS
SELECT Orders.PRMDATE AS "Promised"
     , CAST(DATEDIFF(DAY, GETDATE(), PRMDATE) AS VARCHAR(20)) AS "Days Due"
     , VENDORID AS Vendor
     , STUFF(STUFF(SUBSTRING(RTRIM(PURCHPHONE1), 1, 10), 4, 0, '-'), 8, 0, '-') + COALESCE(NULLIF(' x' + SUBSTRING(PURCHPHONE1, 11, 4), ' x0000'), '') AS "Vendor Phone"
     , BUYERID  AS "Buyer"
     , SHIPMTHD AS "Ship Method"
     , PONUMBER AS "PO#"
     , (CASE WHEN COMMNTID = 'RECEIVED' THEN 1 ELSE 0 END) AS Received
FROM POP10100 Orders
WHERE TABER.dbo.DYN_FUNC_PO_Status( Orders.POSTATUS ) IN ('New','Released','Change Order')
GO

IF OBJECT_ID('dbo.uvw_PurchaseOrders','V') IS NOT NULL DROP VIEW dbo.uvw_PurchaseOrders;
GO

CREATE VIEW dbo.uvw_PurchaseOrders
WITH VIEW_METADATA
AS
SELECT Header.Promised
     , Header."Days Due"
     , Header.Vendor
     , Header."Vendor Phone"
     , Items.Items
     , Header.Buyer
     , Header."Ship Method"
     , Header.PO#
     , (CASE WHEN Header.Received = 1 OR Items.AllReceived = 1 THEN 1 ELSE 0 END) AS Received
FROM uvw_PurchaseOrdersHeaders Header
LEFT JOIN (
    SELECT PO#
         , dbo.GROUP_CONCAT(RTRIM(ISNULL(ITMSHNAM, Items.ItemDesc)) + ' (' + CAST(Qty AS VARCHAR(20)) + ')') AS Items
         , MIN(Received) AS AllReceived
    FROM uvw_PurchaseOrdersDetail AS Items
    LEFT JOIN IV00101 AS IV ON IV.ITEMNMBR = Items.ItemNmbr
    GROUP BY PO#
) AS Items ON Items.PO# = Header.PO#
GO

CREATE TRIGGER dbo.upd_PurchaseOrders
ON dbo.uvw_PurchaseOrders
INSTEAD OF UPDATE
AS
BEGIN
    UPDATE POP10100 -- headers table
    SET PRMDATE = inserted.Promised
    WHERE PONUMBER = inserted."PO#"

    UPDATE POP10110 -- line items table
    SET PRMDATE = inserted.Promised
    WHERE PONUMBER = inserted."PO#"
END
GO

GRANT UPDATE ON dbo.uvw_PurchaseOrders (Promised, Received) TO dyngrp AS dbo;
GO