-- rcup_spl.sql by Dan Martin (DRM/CTG) Mon Jun 14 15:49:58 CDT 1993

   -- PURPOSE: to Update all <R>eady RECEIVERS in real-time
   -- Replaces Procedure pmrc_spl()
   -- 09-14-93: Mod to do RGR & BO for Stock AND Text PO's
   -- 04-13-94: Mod (fairly major) to eliminate bug with Receiving the same
   --           Stock Number to more than one PO # on the same date.
   -- 05-06-94: Instated a Group of valid Account Numbers [1,7], which
   --           will be Updated into the Inventory File (per tlg).
   --           This is intended to facilitate AIRCO & ARCO Inventory @ Enid.

CREATE DBA PROCEDURE rcup_spl (plant char(3)) -- Argument provided by $SITE

          DEFINE stk_no                char(14);
          DEFINE itm_no                char(2);
          DEFINE rc_date               date;
          DEFINE on_hand, itm_ct, qoo  smallfloat;
          DEFINE u_cst,   last_cst     money;
          DEFINE po_num,  serr, ierr   int;
          DEFINE l_time                smallint;

    ON EXCEPTION                           -- quit if ANY error(s) encountered
             SET serr, ierr                -- catch Error Numbers into these
          SYSTEM "echo 'EXCEPTION in Procedure rcup_spl for SITE '" || plant ||
                  " - Errors: " || serr || " " || ierr ||
                  " | mail spims2";        -- hurl mail at sysop
          SYSTEM "finderr " || serr || " " || ierr || " | mail spims2";
   END EXCEPTION ;

           TRACE ON;

--------------------------------------------------------------------
-- Previously, Stored Procedure rtoz_rc() has UPDATEd all <R>eady --
-- Receivers into <Z> Status.  Now determine # of Days Lead Time, --
-- via a circuitous, kludge-strewn path:                          --
--------------------------------------------------------------------

          SELECT rec_po_no    pono,
                 m_pom_sup_no sup,
                 "M"          source,
                 m_pom_date   po_date,
                 rec_date     mr_date,
                 m_po_m.rowid po_rec
            FROM m_rec, m_po_m
           WHERE rec_stat  = "Z"      AND
                 rec_po_no = m_pom_no
       UNION
          SELECT rec_po_no, sup_m_pom_sup_no, "S", sup_m_pom_date,
                 rec_date, sup_mpom.rowid
            FROM m_rec, sup_mpom
           WHERE rec_stat  = "Z"      AND
                 rec_po_no = sup_m_pom_no
        ORDER BY 1
       INTO TEMP poze;

          SELECT pono, MAX(po_date) mxpd
            FROM poze
        GROUP BY 1
        ORDER BY 1
       INTO TEMP pdt;

    CREATE INDEX t_indx ON pdt(pono);

          SELECT pono, mr_date
            FROM poze
        GROUP BY 1, 2
        ORDER BY 1, 2
       INTO TEMP poes;

      DROP TABLE poze;

          SELECT rec_inv_stk_no lstk, (TODAY - mxpd) lead
            FROM m_rec, invrec, pdt
           WHERE rec_stat            = "Z"           AND
                 rec_po_no           = rec_inv_po_no AND
                 rec_date            = rec_inv_date  AND
                 rec_inv_update      = "Z"           AND
                 rec_po_no           = pono
       INTO TEMP many;

      DROP INDEX t_indx;
      DROP TABLE pdt;

          SELECT lstk, MAX(lead) lead_time
            FROM many
        GROUP BY lstk
        ORDER BY 1
       INTO TEMP slt;

      DROP TABLE many;

    CREATE INDEX m_indx ON slt(lstk);

          SELECT rec_inv_stk_no                              stkrc,
                                                             lead_time,
                 SUM(rec_inv_bal_receiv)                     tlqty,     
                 SUM(rec_inv_bal_receiv * rec_inv_unit_cost) tlcst,
                 11111111.11                                 avguc
            FROM m_rec, invrec, slt
           WHERE rec_stat            = "Z"           AND
                 rec_po_no           = rec_inv_po_no AND
                 rec_inv_update      = "Z"           AND
                 rec_date            = rec_inv_date  AND
    rec_inv_accode[1,7] IN ("1460010","1560170","1560180","1560190") AND
                 rec_inv_stk_no      = lstk
        GROUP BY rec_inv_stk_no, lead_time
       INTO TEMP stk;

          UPDATE stk
             SET avguc = (tlcst / tlqty)
           WHERE ROWID > 0;

          SELECT stkrc,
                 lead_time,
                 tlqty,     
                 tlcst,
                 avguc,
                 inv_bal_on_hand    i_bal,
                 inv_unit_cost      iu_cost,
                 inv_last_unit_cost ilu_cost,
                 inv_qty_on_order   iqoo
            FROM stk, inv_file
           WHERE stkrc = inv_stk_no
       INTO TEMP hot;      -- gather only needed info into here

      DROP TABLE stk;      -- now just so much useless baggage

          UPDATE hot       -- process values for [later] UPDATE of inv_file
             SET iu_cost   =  ( (tlcst + (i_bal * iu_cost)) 
                              / (tlqty + i_bal)
                              ),
                 ilu_cost  =  (avguc),
                 iqoo      =  (iqoo - tlqty)
           WHERE ROWID > 0;
          UPDATE hot       -- process values for [later] UPDATE of inv_file
             SET i_bal     =  (i_bal + tlqty)
           WHERE ROWID > 0;

------------------------------------------------------------------------------
-- BY NOW TEMP TABLE hot HAS ALL THE INFO REQUIRED TO UPDATE TABLE inv_file --
------------------------------------------------------------------------------

-------------------------------------------------------------------------
-- NOW, UPDATE INVENTORY FILE (inv_file) WITH "Z" STATUS RECEIVER INFO --
-------------------------------------------------------------------------

         FOREACH imod FOR                         -- ratchet thru Rows in hot
          SELECT stkrc,  i_bal,   iu_cost, ilu_cost, iqoo, lead_time
            INTO stk_no, on_hand, u_cst,   last_cst, qoo,  l_time
            FROM hot
          UPDATE inv_file
             SET inv_unit_cost      = u_cst,
                 inv_bal_on_hand    = on_hand,
                 inv_qty_on_order   = qoo,
                 inv_last_unit_cost = last_cst,
                 inv_last_act       = TODAY,
                 inv_last_typ       = "RC",
                 inv_ltd            = l_time
           WHERE inv_stk_no = stk_no              -- joins inv_file & hot
                 ;          -- required terminator of SELECT
     END FOREACH ;

      DROP TABLE hot;       -- now just so much useless baggage

-------------------------------------------------------------------
-- FIX PSEUDO-STOCK-ITEM BACK TO ZERO VALUES FOR MOST CATEGORIES --
-------------------------------------------------------------------

          UPDATE inv_file
             SET inv_bal_on_hand    = 0, inv_unit_cost    = 0,
                 inv_last_unit_cost = 0, inv_qty_on_order = 0,
                 inv_qty_rec        = 0, inv_issues_out   = 0,
                 inv_sr_out         = 0, inv_ltd = 0
           WHERE inv_stk_no = "NON-STOCK";

-------------------------------------------------------
-- THIS COMPLETES OUR ACTIVITIES WITH TABLE inv_file --
-- NOW, GATHER UP STOCK ITEM QUANTITIES RECEIVED     --
-- pono sup po_date
-------------------------------------------------------

          SELECT rec_inv_po_no           rpono,
                 rec_inv_item_no         ritno,
                 SUM(rec_inv_bal_receiv) rbal
            FROM invrec, poes, inv_file
           WHERE (pono = rec_inv_po_no  AND mr_date = rec_inv_date) AND
                 rec_inv_stk_no = inv_stk_no AND rec_inv_update = "Z"
        GROUP BY rec_inv_po_no, rec_inv_item_no
       INTO TEMP r_d_upd;  -- now we have Quantities Rec'd by PO & Item Number

-----------------------------------------------------------------
-- UPDATE PO DETAIL FILE (m_po_d) WITH ITEM QUANTITIES BY PO # --
-- ALSO CHANGE STATUS TO "C" WHEN ORD-QTY HAS BEEN RECEIVED    --
-----------------------------------------------------------------

         FOREACH dmod FOR                     -- ratchet thru Rows in r_d_upd
          SELECT rpono,  ritno,  rbal
            INTO po_num, itm_no, itm_ct       -- infuse variables with
            FROM r_d_upd                      -- current r_d_upd.column values
          UPDATE m_po_d
             SET m_pod_bo_quant = m_pod_quant_order-(m_pod_rgr_quant+itm_ct)
           WHERE m_pod_no        = po_num AND
                 m_pod_item_no   = itm_no;
          UPDATE m_po_d
             SET m_pod_rgr_quant = m_pod_rgr_quant + itm_ct
           WHERE m_pod_no        = po_num AND
                 m_pod_item_no   = itm_no;
          UPDATE m_po_d
             SET m_pod_stat = "C"
           WHERE m_pod_no         = po_num  AND
                 m_pod_item_no    = itm_no  AND
                 m_pod_rgr_quant >= m_pod_quant_order;
     END FOREACH ;

      DROP TABLE r_d_upd;   -- now just so much useless baggage

          SELECT DISTINCT rec_po_no rpono
            FROM m_rec
           WHERE rec_stat    = "Z" AND
                 rec_po_stat = "C"
       INTO TEMP r_m_upd;

------------------------------------------------------------------------------
-- UPDATE PO MASTER FILE (m_po_m) CHANGE STATUS TO "C" WHEN RECEIVER CLOSED --
------------------------------------------------------------------------------

         FOREACH mmod FOR                     -- ratchet thru Rows in r_m_upd
          SELECT rpono
            INTO po_num                       -- infuse variable with
            FROM r_m_upd                      -- current r_m_upd.column value
          UPDATE m_po_m
             SET m_pom_stat = "C"
           WHERE m_pom_no   = po_num;
{
          UPDATE m_po_d
             SET m_pod_stat = "C"
           WHERE m_pod_no   = po_num;
          UPDATE m_po_t
             SET m_pot_stat = "C"
           WHERE m_pot_no   = po_num;
}
     END FOREACH ;

      DROP TABLE r_m_upd;

------------------------------------------------------------
-- UPDATE PO MASTER FILE (m_po_m) AND EXPIRE BLANKET PO's --
------------------------------------------------------------

          UPDATE m_po_m
             SET m_pom_stat = "E"
           WHERE mbpoexp < TODAY AND
                 mpombpo = "Y";

-------------------------------------------------------
-- UPDATE REC-INV FILE (invrec) CHANGE STATUS TO "U" --
-------------------------------------------------------

          UPDATE invrec
             SET rec_inv_update = "U"
           WHERE rec_inv_update = "Z";

--------------------------------------------------------------------
-- DETERMINE WHICH ITEMS ARE TEXT RECEIVERS & UPDATE TABLE m_po_t --
--------------------------------------------------------------------

---------------------------------------------
-- GATHER UP TEXT ITEM QUANTITIES RECEIVED --
---------------------------------------------

          SELECT non_inv_po_no   npono,
                 non_inv_item_no nitno,
                 non_qty         nbal
            FROM nonrec, poes
           WHERE (pono = non_inv_po_no  AND mr_date = non_inv_date) AND
                 non_inv_update = "Z"
       INTO TEMP r_t_upd;  -- now we have Non-Stock Rec'd by PO & Item Number

----------------------------------------------------------------
-- UPDATE PO TEXT FILE (m_po_t) WITH ITEM QUANTITIES BY PO #  --
-- ALSO CLOSE TEXT ITEMS WHEN WE HAVE RECEIVED ENOUGH OF THEM --
----------------------------------------------------------------

         FOREACH tmod FOR                     -- ratchet thru Rows in r_t_upd
          SELECT npono,  nitno,  nbal
            INTO po_num, itm_no, itm_ct       -- infuse variables with
            FROM r_t_upd                      -- current r_t_upd.column values
          UPDATE m_po_t
             SET m_pot_bo_quant  = m_pot_qty - (m_pot_rgr_quant + itm_ct)
           WHERE m_pot_no        = po_num AND
                 m_pot_item_no   = itm_no;
          UPDATE m_po_t
             SET m_pot_rgr_quant = m_pot_rgr_quant + itm_ct
           WHERE m_pot_no        = po_num AND
                 m_pot_item_no   = itm_no;
          UPDATE m_po_t
             SET m_pot_stat = "C"
           WHERE m_pot_no         = po_num  AND
                 m_pot_item_no    = itm_no  AND
                 m_pot_rgr_quant >= m_pot_qty;
     END FOREACH ;

---------------------------------------------------------
-- UPDATE NON-STOCK FILE (nonrec) CHANGE STATUS TO "U" --
---------------------------------------------------------

          UPDATE nonrec
             SET non_inv_update = "U"
           WHERE non_inv_update = "Z";

      DROP TABLE poes;  -- farewell, old friend

          UPDATE m_rec SET rec_stat = "P" WHERE rec_stat = "Z";

           TRACE OFF;

   END PROCEDURE               -- Th-th-that's all, Folks !

        DOCUMENT "LAST COMPILED ON: Thu Oct 12 15:49:10 CDT 1995",
                 "DOCUMENTATION FOR STORED PROCEDURE 'rcup_spl' BELOW:",
                 'UPDATES inv_file WITH "Z" STATUS RECEIVER INFO',
                 "  (this includes changes to Lead Time).",
                 "UPDATES m_po_d   WITH ITEM QUANTITIES BY PO #",
                 'UPDATEs m_po_d   STATUS TO "C" WHEN ORD-QTY RECEIVED',
                 'UPDATES m_po_m   STATUS TO "C" WHEN RECEIVER CLOSED',
                 'UPDATES invrec   STATUS FROM "Z" TO "U"',
                 'UPDATES nonrec   STATUS FROM "Z" TO "U"',
                 'UPDATES m_po_t   Qty Rec and Qty BO fields',
                 'UPDATEs m_po_t   STATUS TO "C" WHEN ORD-QTY RECEIVED',
                 'Usage: EXECUTE PROCEDURE rcup_spl ("plant");',
                 "Where: 'plant' is a three-character Plant-ID string",
                 '       (e.g., "law", "bea", "dod", etc.).  It is expected',
                 "       that this string will always be passed in via $SITE."
 WITH LISTING IN "/tmp/hurl.spl"           -- Where to send Compile Warnings
                 ;                         -- terminates CREATE PROCEDURE

-- EOF rcup_spl.sql