git.roojs.org Git - xtuple/blob - foundation-database/public/functions/woinvavail.sql

   1
   2 CREATE OR REPLACE FUNCTION woinvavail(integer, boolean, boolean, boolean, boolean)
   3   RETURNS SETOF woinvav AS $$
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6 DECLARE
   7    pwoid ALIAS FOR $1;
   8    pshowchildindent ALIAS FOR $2;
   9    pshowchildsum ALIAS FOR $3;
  10    pshowshortage ALIAS FOR $4;
  11    pshowlowinventory ALIAS FOR $5;
  12   _row woinvav%ROWTYPE;
  13   _subrow woinvav%ROWTYPE;
  14   _wonumber TEXT;
  15   _x RECORD;
  16   _subx RECORD;
  17   _qry TEXT;
  18
  19 BEGIN
  20
  21     IF(pshowchildindent) THEN
  22       --get top level order
  23       FOR _x IN
  24           SELECT wo_id,
  25                 itemsite_id,
  26                 item_type,
  27                 wo_number,
  28                 wo_subnumber,
  29                 item_number,
  30                 item_descrip1,
  31                 item_descrip2,
  32                 uom_name,
  33                 qoh,
  34                 wobalance,
  35                 allocated,
  36                 ordered,
  37                 reorderlevel,
  38                 (qoh + ordered - wobalance) AS woavail,
  39                 (qoh + ordered - allocated) AS totalavail
  40          FROM(SELECT wo_id,
  41                 itemsite_id,
  42                 item_type,
  43                 wo_number,
  44                 wo_subnumber,
  45                 item_number,
  46                 item_descrip1,
  47                 item_descrip2,
  48                 uom_name,
  49                 noNeg(itemsite_qtyonhand) AS qoh,
  50                 noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
  51                 qtyAllocated(itemsite_id, wo_duedate) AS allocated,
  52                 qtyOrdered(itemsite_id, wo_duedate) AS ordered,
  53                 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
  54           FROM wo, itemsite, item, uom
  55          WHERE ((wo_id = pwoid)
  56            AND (itemsite_id = wo_itemsite_id)
  57            AND (itemsite_item_id=item_id)
  58            AND (item_inv_uom_id=uom_id))
  59          ORDER BY wo_number, wo_subnumber) AS data
  60        LOOP
  61          _row.woinvav_itemsite_id := _x.itemsite_id;
  62          _row.woinvav_womatl_id := -1;
  63          _row.woinvav_type := _x.item_type;
  64          _row.woinvav_item_wo_number := _x.wo_number || '-' || _x.wo_subnumber;
  65          _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
  66          _row.woinvav_uomname := _x.uom_name;
  67          _row.woinvav_qoh := _x.qoh;
  68          _row.woinvav_balance := _x.wobalance;
  69          _row.woinvav_allocated := _x.allocated;
  70          _row.woinvav_ordered := _x.ordered;
  71          _row.woinvav_woavail := _x.woavail;
  72          _row.woinvav_totalavail := _x.totalavail;
  73          _row.woinvav_reorderlevel := _x.reorderlevel;
  74          _row.woinvav_level := 0;
  75          RETURN NEXT _row;
  76         --get materials for this level
  77         FOR _subx IN
  78           SELECT * FROM woinvavailmatl(_x.wo_id, 1, pshowshortage, pshowlowinventory)
  79         LOOP
  80           RETURN NEXT _subx;
  81         END LOOP;
  82         FOR _subx IN
  83           SELECT * FROM woinvavail(_x.wo_id, 1, pshowshortage, pshowlowinventory)
  84         LOOP
  85           RETURN NEXT _subx;
  86         END LOOP;
  87      END LOOP;
  88     ELSE
  89        SELECT wo_number FROM wo WHERE wo_id=pwoid LIMIT 1 INTO _wonumber;
  90        --display a single level sum of work order requirements
  91        _qry := 'SELECT  wo_id,
  92                         itemsite_id,
  93                         womatl_id,
  94                         item_type,
  95                         wo_number,
  96                         item_number,
  97                         item_descrip1,
  98                         item_descrip2,
  99                         uom_name,
 100                         qoh,
 101                         wobalance,
 102                         allocated,
 103                         ordered,
 104                         reorderlevel,
 105                         (qoh + ordered - wobalance) AS woavail,
 106                         (qoh + ordered - allocated) AS totalavail
 107                   FROM (SELECT wo_id,
 108                         itemsite_id,
 109                         womatl_id,
 110                         item_type,
 111                         wo_number,
 112                         item_number,
 113                         item_descrip1,
 114                         item_descrip2,
 115                         uom_name,
 116                         noNeg(itemsite_qtyonhand) AS qoh,
 117                         noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) AS wobalance,
 118                         qtyAllocated(itemsite_id, womatl_duedate) AS allocated,
 119                         qtyOrdered(itemsite_id, womatl_duedate) AS ordered,
 120                         CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
 121                    FROM wo, womatl, itemsite, item, uom
 122                  WHERE (womatl_wo_id=wo_id)
 123                    AND (womatl_itemsite_id=itemsite_id)
 124                    AND (itemsite_item_id=item_id)
 125                    AND (item_inv_uom_id=uom_id) ';
 126                 IF(pshowchildsum) THEN
 127                   _qry := _qry || ' AND (wo_number=' || _wonumber || ')';
 128                 ELSE
 129                   _qry := _qry || ' AND (womatl_wo_id=' || pwoid || ')';
 130                 END IF;
 131                  _qry := _qry || ' ORDER BY item_number) AS data ';
 132                 IF(pshowshortage) THEN
 133                   _qry := _qry || ' WHERE (((qoh + ordered - allocated) < 0) OR ((qoh + ordered - wobalance) < 0)) ';
 134                 END IF;
 135                 IF(pshowlowinventory AND NOT pshowshortage) THEN
 136                   _qry := _qry || ' WHERE (((qoh - allocated) < 0) OR ((qoh - wobalance) < 0)) ';
 137                 END IF;
 138
 139       FOR _x IN
 140          EXECUTE _qry
 141       LOOP
 142         _row.woinvav_itemsite_id := _x.itemsite_id;
 143         _row.woinvav_womatl_id := _x.womatl_id;
 144         _row.woinvav_type := _x.item_type;
 145         _row.woinvav_item_wo_number := _x.item_number;
 146         _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
 147         _row.woinvav_uomname := _x.uom_name;
 148         _row.woinvav_qoh := _x.qoh;
 149         _row.woinvav_balance := _x.wobalance;
 150         _row.woinvav_allocated := _x.allocated;
 151         _row.woinvav_ordered := _x.ordered;
 152         _row.woinvav_woavail := _x.woavail;
 153         _row.woinvav_totalavail := _x.totalavail;
 154         _row.woinvav_reorderlevel := _x.reorderlevel;
 155         _row.woinvav_level := 0;
 156         RETURN NEXT _row;
 157   END LOOP;
 158   END IF;
 159   RETURN;
 160 END;
 161 $$ LANGUAGE 'plpgsql';
 162
 163 CREATE OR REPLACE FUNCTION woinvavail(integer, integer, boolean, boolean)
 164   RETURNS SETOF woinvav AS $$
 165 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
 166 -- See www.xtuple.com/CPAL for the full text of the software license.
 167 DECLARE
 168    pwoid ALIAS FOR $1;
 169    plevel ALIAS FOR $2;
 170    pshowshortage ALIAS FOR $3;
 171    pshowlowinventory ALIAS FOR $4;
 172   _row woinvav%ROWTYPE;
 173   _x RECORD;
 174   _subx RECORD;
 175   _index INTEGER;
 176   _level INTEGER;
 177   _qry TEXT;
 178 BEGIN
 179     FOR _x IN
 180           SELECT wo_id,
 181                 itemsite_id,
 182                 item_type,
 183                 wo_number,
 184                 wo_subnumber,
 185                 item_number,
 186                 item_descrip1,
 187                 item_descrip2,
 188                 uom_name,
 189                 qoh,
 190                 wobalance,
 191                 allocated,
 192                 ordered,
 193                 reorderlevel,
 194                 (qoh + ordered - wobalance) AS woavail,
 195                 (qoh + ordered - allocated) AS totalavail
 196          FROM(SELECT wo_id,
 197                 itemsite_id,
 198                 item_type,
 199                 wo_number,
 200                 wo_subnumber,
 201                 item_number,
 202                 item_descrip1,
 203                 item_descrip2,
 204                 uom_name,
 205                 noNeg(itemsite_qtyonhand) AS qoh,
 206                 noNeg(wo_qtyord - wo_qtyrcv) AS wobalance,
 207                 qtyAllocated(itemsite_id, wo_duedate) AS allocated,
 208                 qtyOrdered(itemsite_id, wo_duedate) AS ordered,
 209                 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
 210           FROM wo, itemsite, item, uom
 211          WHERE ((wo_ordid = pwoid AND wo_ordtype = 'W')
 212            AND NOT (wo_status = 'C')
 213            AND (itemsite_id = wo_itemsite_id)
 214            AND (itemsite_item_id=item_id)
 215            AND (item_inv_uom_id=uom_id))
 216          ORDER BY wo_number, wo_subnumber) AS data
 217        LOOP
 218          _row.woinvav_itemsite_id := _x.itemsite_id;
 219          _row.woinvav_womatl_id := -1;
 220          _row.woinvav_type := _x.item_type;
 221          _row.woinvav_item_wo_number := _x.wo_number || '-' || _x.wo_subnumber;
 222          _row.woinvav_descrip := _x.item_descrip1 || ' ' || _x.item_descrip2;
 223          _row.woinvav_uomname := _x.uom_name;
 224          _row.woinvav_qoh := _x.qoh;
 225          _row.woinvav_balance := _x.wobalance;
 226          _row.woinvav_allocated := _x.allocated;
 227          _row.woinvav_ordered := _x.ordered;
 228          _row.woinvav_woavail := _x.woavail;
 229          _row.woinvav_totalavail := _x.totalavail;
 230          _row.woinvav_reorderlevel := _x.reorderlevel;
 231          _row.woinvav_level := plevel;
 232          RETURN NEXT _row;
 233         --get materials for this level
 234         FOR _subx IN
 235           SELECT * FROM woinvavailmatl(_x.wo_id, plevel + 1, pshowshortage, pshowlowinventory)
 236         LOOP
 237           RETURN NEXT _subx;
 238         END LOOP;
 239         --get next level wo
 240         FOR _subx IN
 241           SELECT * FROM woinvavail(_x.wo_id, plevel + 1, pshowshortage, pshowlowinventory)
 242         LOOP
 243           RETURN NEXT _subx;
 244         END LOOP;
 245       END LOOP;
 246   RETURN;
 247 END;
 248 $$ LANGUAGE 'plpgsql';
 249
 250