Product Availability SQL

CREATE PROCEDURE [dbo].[sp_MAR540]
AS
/*

Programmed by    Tim Peer, eNVy Systems, Inc.
                Warehouse Suite Product Availability
                Mar, 2009
                Produced for Standens Limited Online Sales Commerce System (EZOrder)
                Comments please to peert@envysys.com

    Customization for your environment:
    Change Ownership [database].[dbo/envymgr].table
    Database XReference:        mandb100 = Manufacturing, MANDB101 = OMAR, Corpdb = Common database where all global functions are retained.
    Apply dependent objects
    Load and execute the procedure.
    Report roll-up to Inventory Warehouse
    Report roll-up to Location.

Usage:

    Create dependent objects.
        Execute dbo].[sp_MAR540tep]
        Select * from DM_ProductAvailability

Dependent objects:

Pass the current SQL date for comparing to the MANMAN date format.

    create function    [dbo].[date_to_mm_date] (@d datetime) returns int as
    -- takes datetime and returns a date in manman integer format
    begin
        return cast (convert(char,@d,112) as int)
    end

CREATE TABLE [mar540_productavailability](
    [PartNumber] [char](32) NULL,                    /* The Mfg part record. */
    [product_number] [varchar](32) NULL,        /* Product_Number will match to OMAR product number. If blank, MFG part is not linked to OMAR or is Routable. */
    [product_description] [varchar](60) NULL,   /* Placeholder and not used */
    [QtyOnHand] [float] NULL,                    /* Onhand Balance (neg = sum(Allocation or Firm Planned Order), post = sum(inventory QOH), group by warehouse, location */
    [SalesOrderNumber] [varchar](24) NULL,        /* Firm Planned order Number */
    [SalesOrderLine] [int] NULL,                /* Firm Planned Line Number */
    [ShipToNumber] [varchar](12) NULL,            /* Associated Ship-to for the FPO */
    [ScheduleDate] [int] null default 0,        /* Date the order is scheduled to ship */
    [BackorderQty] [float] null default 0,        /* Back order qty from MA_SOHREC */
    [orderqty] [float] null default 0,            /* FPO Order Quantity */
    [StagedQty] [float] null default 0,            /* FPO Staged Quantity */
    [QtyPerAssy] [float] null default 1,            /* For Top Level Assys, the QPA for each component part used on an FPO */
    [Warehouse] [varchar](20) NULL DEFAULT ('NA'),    /* Warehouse Key */
    [location] varchar(20) null default (' '),        /* Warehouse Location Key */
    [SOURCE] INT DEFAULT 0,                            /* 0 = Firm Planned Order,1=Inventory -> Product Matched, 2= Inventory <-- Product Unmatched */
    [OnOrderQty] [float] NULL DEFAULT ((0))            /* The FPO Order Quantity */
) ON [PRIMARY]

=================================================================================================
Used by:

    Create View VDM_ProductAvailability as
    SELECT     TOP (100) PERCENT product_number, Warehouse, SUM(QOH) AS Available
    FROM         (SELECT     TOP (100) PERCENT PartNumber, location, Warehouse, QtyOnHand AS QOH, product_number
                           FROM          EnvyMgr.mar540_productavailability) AS derivedtbl_1
    GROUP BY Warehouse, PartNumber, product_number
    ORDER BY Product_number, Warehouse


Availability Analysis

    1. Create or Alter the MAR540_PRODUCTAVAILABILITY TABLE
    2. Fetch all open Sales Order Lines (Ordered but not shipped).
    3. Load Product, SO, Line, Shipto, Schedule Date, Backlog Qty and Staged Qty into table
    4. Set default QPA: QtyPerAssy = 1
    5. Loop and match products to prtno records.
    6. Loop and match products to toprec record. Update the MA_TOPQTY = qtyperassy
    7. Loop to retrieve the QOH balance from inventory.
    8. Multiply QOH Balance by QtyPerAssy for the product.
    9. Add negative balances for filtered locations.

Rolling Up Availability Status by Warehouse
------------------------------------------------------------------------------------------
CREATE VIEW [EnvyMgr].[VDM_ProductAvailability]
AS
SELECT     TOP (100) PERCENT product_number, Warehouse, SUM(CAST(QOH AS NUMERIC(18, 2))) AS Available
FROM         (SELECT     TOP (100) PERCENT PartNumber, location, Warehouse, QtyOnHand AS QOH, product_number
                       FROM          EnvyMgr.mar540_productavailability
                       UNION
                       SELECT     mandb100.EnvyMgr.PRTREC.PRTNO, mandb100.EnvyMgr.LOTREC.LOCCODE, mandb100.EnvyMgr.LOTREC.LOCWAR,
                                             mandb100.EnvyMgr.LOTREC.LOTQTY AS onhandqty, EnvyMgr.MA_PRODREC.MA_PRODNO
                       FROM         EnvyMgr.MA_PRODREC INNER JOIN
                                             mandb100.EnvyMgr.LOTREC INNER JOIN
                                             mandb100.EnvyMgr.LOCREC ON mandb100.EnvyMgr.LOTREC.LOCWAR = mandb100.EnvyMgr.LOCREC.LOCWAR AND
                                             mandb100.EnvyMgr.LOTREC.LOCCODE = mandb100.EnvyMgr.LOCREC.LOCCODE AND
                                             mandb100.EnvyMgr.LOTREC.PRTNO = mandb100.EnvyMgr.LOCREC.PRTNO INNER JOIN
                                             mandb100.EnvyMgr.PRTREC ON mandb100.EnvyMgr.LOCREC.PRTNO = mandb100.EnvyMgr.PRTREC.PRTNO ON
                                             EnvyMgr.MA_PRODREC.MA_PRODNO = mandb100.EnvyMgr.LOTREC.PRTNO
                       WHERE     (mandb100.EnvyMgr.LOTREC.LOCCODE <> 'TRANSIT')) AS derivedtbl_1
GROUP BY Warehouse, product_number
HAVING      (SUM(CAST(QOH AS NUMERIC(18, 2))) > 0)
ORDER BY product_number, Warehouse
===========================================================================================
Sum availability by Warehouse and Location
-------------------------------------------------------------------------------------------

SELECT     TOP (100) PERCENT PartNumber, location, Warehouse, QtyOnHand AS QOH
FROM          EnvyMgr.mar540_productavailability

    Select ProdAvailable = select qoh from VDM_ProductAvailability where product_number = "<omar product>"
    drop procedure sp_mar540tep
*/

/*
Lets load the FPO Orders first. Note two values are retained for QOH in the availability analysis. The Backorderqty and OnOrderQty.
The OnOrderqty is stored as a negative value to ensure proper netting when we add inventory balances.

-- DROP TABLE envymgr.[mar540_productavailability]
SET ANSI_PADDING OFF
GO
CREATE TABLE [EnvyMgr].[mar540_productavailability](
    [PartNumber] [char](32) NULL,                    /* The Mfg part record. */
    [product_number] [varchar](32) NULL,        /* Product_Number will match to OMAR product number. If blank, MFG part is not linked to OMAR or is Routable. */
    [product_description] [varchar](60) NULL,   /* Placeholder and not used */
    [QtyOnHand] [float] NULL,                    /* Onhand Balance (neg = sum(Allocation or Firm Planned Order), post = sum(inventory QOH), group by warehouse, location */
    [SalesOrderNumber] [varchar](24) NULL,        /* Firm Planned order Number */
    [SalesOrderLine] [int] NULL,                /* Firm Planned Line Number */
    [ShipToNumber] [varchar](12) NULL,            /* Associated Ship-to for the FPO */
    [ScheduleDate] [int] null default 0,        /* Date the order is scheduled to ship */
    [BackorderQty] [float] null default 0,        /* Back order qty from MA_SOHREC */
    [orderqty] [float] null default 0,            /* FPO Order Quantity */
    [StagedQty] [float] null default 0,            /* FPO Staged Quantity */
    [QtyPerAssy] [float] null default 1,            /* For Top Level Assys, the QPA for each component part used on an FPO */
    [Warehouse] [varchar](20) NULL DEFAULT ('NA'),    /* Warehouse Key */
    [location] varchar(20) null default (' '),        /* Warehouse Location Key */
    [SOURCE] INT DEFAULT 0,                            /* 0 = Firm Planned Order,1=Inventory -> Product Matched, 2= Inventory <-- Product Unmatched */
    [OnOrderQty] [float] NULL DEFAULT ((0))            /* The FPO Order Quantity */
) ON [PRIMARY]
*/
begin
    delete from envymgr.mar540_productavailability
end
insert into envymgr.mar540_productavailability (
salesordernumber, salesorderline,shiptonumber,scheduledate,backorderqty,stagedqty, product_number, warehouse, orderqty, QTYOnhand)
SELECT         vDM_SO_vi.sales_order_number,
        vDM_SO_vi.sales_order_line_number,
        vDM_SO_vi.ship_to_number,
                vDM_SO_vi.scheduled_ship_date,
        vDM_SO_vi.quantity_ordered - vDM_SO_vi.quantity_shipped AS backorderqty,
        vDM_SO_vi.quantity_staged,
        vDM_SO_vi.product_number, warehouse,
        vDM_SO_vi.quantity_ordered, (vDM_SO_vi.quantity_ordered - vDM_SO_vi.quantity_shipped + vDM_SO_vi.quantity_staged) * -1
FROM         envymgr.vDM_SO_vi INNER JOIN
                      envymgr.MA_PRODREC ON vDM_SO_vi.product_number = MA_PRODREC.MA_PRODNO
WHERE     (MA_PRODREC.MA_PRODINVFLG = 0)
AND (vDM_SO_vi.quantity_ordered <> vDM_SO_vi.quantity_shipped)
order by product_number,sales_order_number,sales_order_line_number,scheduled_ship_date

/*
    Variable Definitions

    prtno = Part Number
    message = (debugging) text
    prodno = Omar MA_Prodno
    partno = OMAR MA_TOPPart
    qtypassy = Quantity Per Assembly for the MA_TOPREC assembly
    QOH        = Inventory Quantity on Hand
    onorderqty = (negative) on order quantity from ma_sodqty
    loccode = Manufacturing Locrec.LOCCode
    warehouse Maufuacturing locrec.WarCode (Locrec.Locwar)
*/
declare @prtno varchar(32),@message varchar(32), @prodno varchar(32),@partno varchar(32),
    @qtypassy int, @QOH double precision, @onorderqty float, @loccode varchar(20)
declare @warehouse varchar(32)

/*
    Create a cursor and process case. ma_prodno = prtno
*/

DECLARE MATCH_PRODNO_PRTNO CURSOR FOR
SELECT     mandb100.EnvyMgr.PRTREC.PRTNO, EnvyMgr.mar540_productavailability.product_number, warehouse
FROM         EnvyMgr.mar540_productavailability INNER JOIN
                      mandb100.EnvyMgr.PRTREC ON EnvyMgr.mar540_productavailability.product_number = mandb100.EnvyMgr.PRTREC.PRTNO
GROUP BY mandb100.EnvyMgr.PRTREC.PRTNO, EnvyMgr.mar540_productavailability.product_number, warehouse

OPEN MATCH_PRODNO_PRTNO
FETCH NEXT FROM MATCH_PRODNO_PRTNO
INTO @partno,@prodno,   @warehouse
select @qtypassy = 1
WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE envymgr.mar540_productavailability SET PartNumber = @partno,qtyperassy = @qtypassy where product_number = @prodno and warehouse = @warehouse
   FETCH NEXT FROM MATCH_PRODNO_PRTNO
   INTO @partno,@prodno, @warehouse
END
close MATCH_PRODNO_PRTNO
deallocate MATCH_PRODNO_PRTNO

/*
    Create a cursor and process case, ma_toprec.ma_partno = prtno
*/

DECLARE MatchPrtno_cursor CURSOR FOR
SELECT    EnvyMgr.MA_TOPREC.MA_PARTNO,MA_TOPREC.MA_PRODNO,MA_TOPQTY, onorderqty, warehouse
FROM      EnvyMgr.MA_TOPREC INNER JOIN
          EnvyMgr.mar540_productavailability ON EnvyMgr.MA_TOPREC.MA_PRODNO = EnvyMgr.mar540_productavailability.product_number
Open MatchPrtno_cursor
FETCH NEXT FROM MatchPrtno_cursor
INTO @partno,@prodno,@qtypassy, @onorderqty, @warehouse
WHILE @@FETCH_STATUS = 0
BEGIN
   UPDATE envymgr.mar540_productavailability SET PartNumber = @partno,qtyperassy = @qtypassy, onorderqty = @onorderqty * @qtypassy where product_number = @prodno and warehouse = @warehouse
   FETCH NEXT FROM MatchPrtno_cursor
   INTO @partno,@prodno,@qtypassy, @onorderqty, @warehouse
END
close MatchPrtno_cursor
deallocate MatchPrtno_cursor

/*
    Create a cursor and process case. Load Inventory Locrec in Availability table.
*/

DECLARE @prtwarcnt int
BEGIN

    DECLARE LOCLOTPRT CURSOR FOR
    --select mandb100.EnvyMgr.LOCREC.LOCWAR,mandb100.EnvyMgr.LOCREC.PRTNO,mandb100.EnvyMgr.LOTREC.LOTQTY, mandb100.EnvyMgr.LOTREC.LOCCODE
    --FROM         mandb100.EnvyMgr.LOTREC INNER JOIN
    --                    mandb100.EnvyMgr.LOCREC ON mandb100.EnvyMgr.LOTREC.PRTNO = mandb100.EnvyMgr.LOCREC.PRTNO AND
    --                    mandb100.EnvyMgr.LOTREC.LOCWAR = mandb100.EnvyMgr.LOCREC.LOCWAR AND
    --                    mandb100.EnvyMgr.LOTREC.LOCCODE = mandb100.EnvyMgr.LOCREC.LOCCODE
    --WHERE     (mandb100.EnvyMgr.LOCREC.LOCNET = 1) AND (mandb100.EnvyMgr.LOTREC.LOTEXPDAT >= corpdb.dbo.date_to_mm_date(GETDATE()))
    SELECT     mandb100.EnvyMgr.LOCREC.LOCWAR, mandb100.EnvyMgr.LOCREC.PRTNO, mandb100.EnvyMgr.LOTREC.LOTQTY,
                      mandb100.EnvyMgr.LOTREC.LOCCODE
FROM         mandb100.EnvyMgr.LOTREC INNER JOIN
                      mandb100.EnvyMgr.LOCREC ON mandb100.EnvyMgr.LOTREC.PRTNO = mandb100.EnvyMgr.LOCREC.PRTNO AND
                      mandb100.EnvyMgr.LOTREC.LOCWAR = mandb100.EnvyMgr.LOCREC.LOCWAR AND
                      mandb100.EnvyMgr.LOTREC.LOCCODE = mandb100.EnvyMgr.LOCREC.LOCCODE
WHERE     (mandb100.EnvyMgr.LOCREC.LOCNET = 1) AND (mandb100.EnvyMgr.LOTREC.LOTEXPDAT >= corpdb.dbo.date_to_mm_date(GETDATE())) AND
                      (NOT (mandb100.EnvyMgr.LOTREC.LOCCODE IN ('TRANSIT')))

    OPEN LOCLOTPRT
    FETCH NEXT FROM LOCLOTPRT
    INTO @Warehouse, @partno,@qoh, @loccode

    WHILE @@FETCH_STATUS = 0
    BEGIN

            INSERT INTO envymgr.mar540_productavailability
    (partnumber, salesordernumber, salesorderline,shiptonumber,scheduledate,backorderqty,stagedqty,product_number,warehouse, OnOrderQty, qtyonhand,qtyperassy,source, location)
            VALUES (@PARTNO, ' ',0,' ',0,0,0,' ',@WAREHOUSE,@QOH,@QOH,1,1,@loccode)


        FETCH NEXT FROM LOCLOTPRT
        INTO @Warehouse, @partno,@qoh, @loccode
    END
END

close LOCLOTPRT
deallocate LOCLOTPRT

/*
Begin filter for nettable inventory.

    Create a cursor and process case. Apply special case filter. We will load inventory and negate the location TRANSIT.
    Special Case: Since Transit is a shipping status (for Standens), it was decided to not treat inventory in this location
    as consumable in availability reports.

DECLARE @WARCODE VARCHAR(30)
DECLARE TRANSIT_CURS CURSOR FOR
SELECT     lotrec.LOTQTY, locrec.LOCCODE, locrec.WARCODE, locrec.PRTNO
FROM         mandb100.EnvyMgr.LOCREC AS locrec INNER JOIN
                      mandb100.EnvyMgr.LOTREC AS lotrec ON locrec.LOCCODE = lotrec.LOCCODE AND locrec.PRTNO = lotrec.PRTNO AND
                      locrec.LOCWAR = lotrec.LOCWAR AND locrec.PRTNO = lotrec.PRTNO
WHERE     (locrec.LOCCODE = 'TRANSIT') AND (lotrec.LOTQTY > 0)
OPEN TRANSIT_CURS

FETCH TRANSIT_CURS INTO @QOH, @LOCCODE, @WARCODE, @PRTNO
print @loccode + @warcode + @prtno
WHILE @@FETCH_STATUS = 0
BEGIN
print @loccode + @warcode + @prtno
    INSERT INTO envymgr.mar540_productavailability
    (location, partnumber, salesordernumber, salesorderline,shiptonumber,scheduledate,backorderqty,stagedqty,product_number,warehouse, OnOrderQty, qtyonhand,qtyperassy,source)
            VALUES (@LOCCODE, @PRTNO, ' ',0,' ',0,0,0,@PRTNO,@WARcode,@QOH*-1,@QOH*-1,1,2)

    FETCH TRANSIT_CURS INTO @QOH, @LOCCODE, @WARCODE, @PRTNO
END
close TRANSIT_CURS
deallocate TRANSIT_CURS
End Inventory Filter

Update the MAR540_productavailability table with product alias for inventory
    Case:
        Lotrec.prtno = ma_toprec.ma_partno
        Lotrec.prtno = ma_prodrec.ma_prodno
        Remaining Product_number = ' ' are MFG parts not sold in OMAR as
*/
begin
print 'update mar540_productavailability set product_number = ma_prodno fROM         mandb100.EnvyMgr.LOTREC INNER JOIN                      EnvyMgr.mar540_productavailability ON mandb100.EnvyMgr.LOTREC.PRTNO = EnvyMgr.mar540_productavailability.PartNumber INNER JOIN                      EnvyMgr.MA_TOPREC ON mandb100.EnvyMgr.LOTREC.PRTNO = EnvyMgr.MA_TOPREC.MA_TOPPART '
    update mar540_productavailability set product_number = ma_prodno

FROM         mandb100.EnvyMgr.PRTREC INNER JOIN
                      EnvyMgr.MA_TOPREC ON mandb100.EnvyMgr.PRTREC.PRTNO = EnvyMgr.MA_TOPREC.MA_PARTNO INNER JOIN
                      EnvyMgr.mar540_productavailability ON mandb100.EnvyMgr.PRTREC.PRTNO = EnvyMgr.mar540_productavailability.PartNumber
end

/* Process the Inventory Part = OMAR Product
print 'update mar540_productavailability set product_number = ma_prodno FROM         mandb100.EnvyMgr.LOCREC INNER JOIN EnvyMgr.MA_PRODREC ON mandb100.EnvyMgr.LOCREC.PRTNO = EnvyMgr.MA_PRODREC.MA_PRODNO'
begin
    update mandb101.envymgr.mar540_productavailability set product_number = ma_prodno
FROM         mandb100.EnvyMgr.LOCREC INNER JOIN
                      EnvyMgr.MA_PRODREC ON mandb100.EnvyMgr.LOCREC.PRTNO = EnvyMgr.MA_PRODREC.MA_PRODNO INNER JOIN
                      EnvyMgr.mar540_productavailability ON EnvyMgr.MA_PRODREC.MA_PRODNO = EnvyMgr.mar540_productavailability.PartNumber

end
*/