-- Function: xt.distribute(int, numeric, int, text)

-- DROP FUNCTION xt.distribute(int, numeric, int, text);

CREATE OR REPLACE FUNCTION xt.distribute(int, numeric, int, text)
  RETURNS integer AS
$BODY$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemlocSeries ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pLocationId ALIAS FOR $3;
  pLot ALIAS FOR $4;
  -- pExpiration ALIAS FOR $4;
  -- pWarranty ALIAS FOR $5;
  _info RECORD;
  _distId INTEGER;
  _traceSeries INTEGER;
  _result INTEGER;

BEGIN

  IF (pItemlocSeries < 0) THEN
    RAISE EXCEPTION 'pItemlocSeries must be >= 0';
  END IF;

  /* Check if itemlocdist record(s) already exist */
  SELECT itemlocdist_id,
    invhist_id,
    invhist_invqty,
    invhistsense(invhist_id) as sense,
    itemsite_id,
    itemsite_controlmethod,
    itemsite_loccntrl,
    count(*) AS rows INTO _info
  FROM itemlocdist,
    invhist
    JOIN itemsite on itemsite_id = invhist_itemsite_id
  WHERE itemlocdist_series = pItemlocSeries
    AND invhist_series = pItemlocSeries
  GROUP BY itemlocdist_id,
    invhist_id,
    invhist_invqty,
    itemsite_id,
    itemsite_controlmethod,
    itemsite_loccntrl;


  RAISE NOTICE '_info.rows: %', _info.rows;

  /* We shouldn't have detail if there are no detail control settings turned on */
  IF (_info.rows IS NULL) THEN
    RAISE EXCEPTION 'No records found that are able to be distributed [xtuple: xt.distribute, -1]';
  ELSE IF (_info.rows > 0) THEN
    RAISE EXCEPTION 'Only distribute for one transaction at a time is supported [xtuple: xt.distribute, -2].';
  END IF;

  /* Gather distribution detail */

  /* Validate quantity */

  /* Loop through and handle each trace detail */
  IF ((_info.itemsite_controlmethod = 'L') OR (_info.itemsite_controlmethod = 'S')) THEN
    SELECT nextval('itemloc_series_seq') INTO _traceSeries;

    IF (_info.itemsite_controlmethod = 'S') THEN
      SELECT ls_id FROM (
        SELECT ls_id
        FROM itemloc join ls on itemloc_ls_id = ls_id
        WHERE ls_number = pLot
          AND itemloc_itemsite_id = _info.itemsite_id
        UNION ALL
        SELECT ls_id
        FROM itemlocdist join ls on itemlocdist_ls_id = ls_id
        WHERE ls_number = pLot
          AND itemlocdist_itemsite_id = _info.itemsite_id
      ) AS query;

      IF (FOUND AND pQty = 1) THEN
        RAISE EXCEPTION 'Serial number % already exists in inventory.', pLot;
      ELSE IF (NOT FOUND AND (pQty = -1)) THEN
        RAISE EXCEPTION 'Serial number does not exist in inventory.';
      ELSE IF (pQty != -1 AND (pQty != 1)) THEN
        RAISE EXCEPTION 'Serial number quantity must be one.';
      END IF; --pQty with series error handling
    END IF; --serial item

    SELECT createlotserial(pLot, _traceSeries, pQty, NULL::DATE, NULL::DATE, _info.itemlocdist_id) INTO _distId
    FROM itemlocdist
    WHERE (itemlocdist_id=_info.itemlocdist_id);

    UPDATE itemlocdist
    SET itemlocdist_source_type = 'L',
      itemlocdist_source_id = pLocationId
    WHERE itemlocdist_id = _distId;

    DELETE FROM itemlocdist WHERE itemlocdist_id = _info.itemlocdist_id;

    SELECT distributeitemlocseries(_traceSeries);

  ELSE -- Location control without lot/serial
    INSERT INTO itemlocdist (itemlocdist_itemlocdist_id, itemlocdist_source_type,
      itemlocdist_source_id, itemlocdist_itemsite_id, itemlocdist_expiration, itemlocdist_qty,
      itemlocdist_series, itemlocdist_invhist_id )
    VALUES (_info.itemlocdist_id, 'L', pLocationId, pQty, endoftime(), _info.itemsite_id, pItemlocSeries, _info.invhist_id);

    SELECT distributeitemlocseries(pItemlocSeries);
  END IF;

  -- Wrap up
  SELECT postitemlocseries(pItemLocSeries);

  RETURN NULL;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION xt.distribute(int, numeric, int, text)
  OWNER TO admin;