-- ven_hist.sql  by Dan Martin (DRM/CTG) Mon Apr 04 15:36:27 CDT 1994
-- Called by: DB/make_pms.sh   SHELLS/pmpopp.sh   SPL/derec.sql
--            Stored Procedure hist_all()
--            & [probably many] others (e.g., SHELLS/bom_pms.sh [indirectly])

-- FUNCTION: To populate the Table hist with Vendor Balances History.

CREATE DBA PROCEDURE ven_hist (vno integer, code char(3), reason char(60))

          DEFINE vnum, serr, ierr  int;            {Vendor # & Error #s       }
          DEFINE hcod              char(3);        {JOINS with codes.cod_code }
          DEFINE blab              char(60);       {Explicit Textual Reference}
          DEFINE ptsb, pdsb, rhsb,                 {Statement Balances  (+)   }
                 ptef, pdef, rhef,                 {Estimated Fabricate (-)   }
                 pter, pder, rher  decimal(16,4);  {Estimate  Refine    (+)   }

    ON EXCEPTION
             SET serr, ierr                -- catch Error Numbers into these
          SYSTEM "echo 'EXCEPTION in PMS Procedure ven_hist - Errors: '" ||
            serr || " " || ierr || " | mail pmet2";     -- hurl mail at sysop
          SYSTEM "finderr " || serr || " " || ierr || " | mail pmet2 spims2";
   END EXCEPTION ;

 --    IF    code = "XXX"
 --    THEN
         LET blab = reason;
 --      LET hcod = "UNK";
 --    ELSE
 --      LET blab = (SELECT cod_txt FROM codes WHERE cod_code = code);
         LET hcod = code;
 --    END IF ;

         FOREACH pmven FOR
          SELECT v_no,     {VENDOR NUMBER}
                 pt_bal,   {PLATINUM  BALANCE: LAST VENDOR STATEMENT}
                 pd_bal,   {PALLADIUM BALANCE: LAST VENDOR STATEMENT}
                 rh_bal,   {RHODIUM   BALANCE: LAST VENDOR STATEMENT}
                 pt_r_est, {PLATINUM  REFINE ESTIMATES NET BALANCE}
                 pd_r_est, {PALLADIUM REFINE ESTIMATES NET BALANCE}
                 rh_r_est, {RHODIUM   REFINE ESTIMATES NET BALANCE}
                 pt_f_est, {PLATINUM  FABRICATE  ESTIMATES NET BALANCE}
                 pd_f_est, {PALLADIUM FABRICATE  ESTIMATES NET BALANCE}
                 rh_f_est  {RHODIUM   FABRICATE  ESTIMATES NET BALANCE}
            INTO vnum, ptsb, pdsb, rhsb,
                       pter, pder, rher,
                       ptef, pdef, rhef
            FROM vendors
           WHERE v_no = vno
     INSERT INTO hist (hi_rec,  hi_ven,
                       hi_ptcb, hi_pdcb, hi_rhcb,
                       hi_ptef, hi_pdef, hi_rhef,
                       hi_pter, hi_pder, hi_rher,
                       hi_code, hi_rem,  hi_who, hi_mark)
          VALUES      (0,       vnum,
                       ptsb,    pdsb,    rhsb,
                       ptef,    pdef,    rhef,
                       pter,    pder,    rher,
                       hcod,    blab,    USER, CURRENT);
     END FOREACH ;

   END PROCEDURE

        DOCUMENT
         "LAST COMPILED ON: Fri Oct 14 10:00:16 CDT 1994",
         "DOCUMENTATION FOR PMS STORED PROCEDURE 'ven_hist' BELOW:",
         'Initially Compiled and Called by DB/make_pms.sh (and later,',
         'by [many] others).  Purpose is to inject "BEFORE" & "AFTER"',
         'Vendor Precious Metal amounts (when used "BEFORE" & "AFTER")',
         'into the Table hist.',
         'Usage: EXECUTE PROCEDURE',
         '       ven_hist (V#, "CODE", "REASON");',
         "Argument types:  (integer, char(3), char(60)).",
         "CODE will be LOOKED UP from Table codes.cod_code, and the",
         'matching text (codes.cod_txt) will be placed within column"',
         'vendors.v_rem, IFF argument for "CODE" <> "XXX".',
         'Otherwise (i.e., "CODE" = "XXX"), the contents of argument',
         '"REASON" will be placed within Column vendors.v_rem.'
     --  [_________________~ max length for any doc line ~_________________]
 WITH LISTING IN "/tmp/hurl.spl"           -- Where to send Compile Warnings
                 ;                         -- terminates CREATE PROCEDURE


-- EOF ven_hist.sql