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

   1 SELECT dropIfExists('FUNCTION', 'createPurchaseToSale(integer, integer, boolean)', 'fixcountry');
   2 SELECT dropIfExists('FUNCTION', 'createPurchaseToSale(integer, integer, boolean, numeric)', 'fixcountry');
   3 SELECT dropIfExists('FUNCTION', 'createPurchaseToSale(integer, integer, boolean, numeric, date, numeric)', 'fixcountry');
   4
   5 CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN) RETURNS INTEGER AS $$
   6 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   7 -- See www.xtuple.com/CPAL for the full text of the software license.
   8 DECLARE
   9   pCoitemId ALIAS FOR $1;
  10   pItemSourceId ALIAS FOR $2;
  11   pDropShip ALIAS FOR $3;
  12
  13 BEGIN
  14
  15   RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, NULL);
  16
  17 END;
  18 $$ LANGUAGE 'plpgsql';
  19
  20
  21 CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN, NUMERIC) RETURNS INTEGER AS $$
  22 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  23 -- See www.xtuple.com/CPAL for the full text of the software license.
  24 DECLARE
  25   pCoitemId ALIAS FOR $1;
  26   pItemSourceId ALIAS FOR $2;
  27   pDropShip ALIAS FOR $3;
  28   pPrice ALIAS FOR $4;
  29
  30 BEGIN
  31
  32   RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, pPrice);
  33
  34 END;
  35 $$ LANGUAGE 'plpgsql';
  36
  37
  38 CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN, NUMERIC, DATE, NUMERIC) RETURNS INTEGER AS $$
  39 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  40 -- See www.xtuple.com/CPAL for the full text of the software license.
  41 DECLARE
  42   pCoitemId ALIAS FOR $1;
  43   pItemSourceId ALIAS FOR $2;
  44   pDropShip ALIAS FOR $3;
  45   pQty ALIAS FOR $4;
  46   pDueDate ALIAS FOR $5;
  47   pPrice ALIAS FOR $6;
  48
  49 BEGIN
  50
  51   RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, pQty, pDueDate, pPrice, NULL);
  52
  53 END;
  54 $$ LANGUAGE 'plpgsql';
  55
  56
  57 CREATE OR REPLACE FUNCTION createPurchaseToSale(INTEGER, INTEGER, BOOLEAN, NUMERIC, DATE, NUMERIC, INTEGER) RETURNS INTEGER AS $$
  58 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
  59 -- See www.xtuple.com/CPAL for the full text of the software license.
  60 DECLARE
  61   pCoitemId ALIAS FOR $1;
  62   pItemSourceId ALIAS FOR $2;
  63   pDropShip ALIAS FOR $3;
  64   pQty ALIAS FOR $4;
  65   pDueDate ALIAS FOR $5;
  66   pPrice ALIAS FOR $6;
  67   pPoheadId ALIAS FOR $7;
  68
  69   _s RECORD;
  70   _w RECORD;
  71   _i RECORD;
  72   _shipto RECORD;
  73   _poheadid INTEGER := -1;
  74   _poitemid INTEGER := -1;
  75   _taxtypeid INTEGER := -1;
  76   _polinenumber INTEGER;
  77   _ponumber NUMERIC;
  78   _price NUMERIC;
  79   _temp INTEGER;
  80
  81 BEGIN
  82
  83   -- Check for existing poitem for this coitem
  84   SELECT poitem_id INTO _poitemid
  85   FROM poitem
  86   WHERE (poitem_order_id=pCoitemId)
  87     AND (poitem_order_type='S');
  88   IF (FOUND) THEN
  89     RETURN _poitemid;
  90   END IF;
  91
  92   SELECT *,
  93          COALESCE(roundQty(item_fractional, (coitem_qtyord * coitem_qty_invuomratio)), 0.0) AS orderqty
  94   INTO _s
  95   FROM coitem JOIN cohead ON (cohead_id = coitem_cohead_id)
  96               LEFT OUTER JOIN shiptoinfo ON (cohead_shipto_id = shipto_id)
  97               LEFT OUTER JOIN addr ON (shipto_addr_id = addr_id)
  98               LEFT OUTER JOIN cntct ON (shipto_cntct_id = cntct_id)
  99               LEFT OUTER JOIN itemsite ON (coitem_itemsite_id = itemsite_id)
 100               LEFT OUTER JOIN item ON (item_id = itemsite_item_id)
 101   WHERE (coitem_id = pCoitemId);
 102   IF (NOT FOUND) THEN
 103     RETURN -1;
 104   END IF;
 105
 106   SELECT * INTO _w
 107   FROM itemsite JOIN whsinfo ON (warehous_id = itemsite_warehous_id)
 108                 LEFT OUTER JOIN addr ON (warehous_addr_id = addr_id)
 109                 LEFT OUTER JOIN cntct ON (warehous_cntct_id = cntct_id)
 110   WHERE (itemsite_id = _s.itemsite_id);
 111
 112   SELECT * INTO _i
 113   FROM itemsrc JOIN vendinfo ON (itemsrc_vend_id = vend_id)
 114                LEFT OUTER JOIN cntct ON (vend_cntct1_id = cntct_id)
 115                LEFT OUTER JOIN addr ON (vend_addr_id = addr_id)
 116   WHERE (itemsrc_id = pItemSourceId);
 117   IF (NOT FOUND) THEN
 118     RETURN -2;
 119   END IF;
 120
 121   -- pPoheadId - NULL=add to existing PO if one exists
 122   --               -1=must create new PO
 123   --               >0=must add to existing specified PO
 124   IF (pDropShip) THEN
 125     SELECT COALESCE(pohead_id, -1) INTO _temp
 126     FROM pohead
 127     WHERE ( (pohead_status = 'U')
 128       AND (pohead_vend_id = _i.itemsrc_vend_id)
 129       AND (pohead_shiptoname = COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''))
 130       AND (pohead_shiptoaddress1 = COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''))
 131       AND (pohead_shiptoaddress2 = COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''))
 132       AND (pohead_shiptoaddress3 = COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''))
 133       AND (pohead_shiptocity = COALESCE(_s.cohead_shiptocity, _s.addr_city, ''))
 134       AND (pohead_shiptostate = COALESCE(_s.cohead_shiptostate, _s.addr_state, ''))
 135       AND (pohead_shiptozipcode = COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''))
 136       AND (pohead_shiptocountry = COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''))
 137       AND ((pohead_id=pPoheadId) OR (pPoheadid IS NULL)) );
 138   ELSE
 139     SELECT COALESCE(pohead_id, -1) INTO _temp
 140     FROM pohead
 141     WHERE ( (pohead_status = 'U')
 142       AND (pohead_vend_id = _i.itemsrc_vend_id)
 143       AND (pohead_shiptoaddress1 = COALESCE(_w.addr_line1, ''))
 144       AND (pohead_shiptoaddress2 = COALESCE(_w.addr_line2, ''))
 145       AND (pohead_shiptoaddress3 = COALESCE(_w.addr_line3, ''))
 146       AND (pohead_shiptocity = COALESCE(_w.addr_city, ''))
 147       AND (pohead_shiptostate = COALESCE(_w.addr_state, ''))
 148       AND (pohead_shiptozipcode = COALESCE(_w.addr_postalcode, ''))
 149       AND (pohead_shiptocountry = COALESCE(_w.addr_country, ''))
 150       AND ((pohead_id=pPoheadId) OR (pPoheadid IS NULL)) );
 151   END IF;
 152
 153   IF (FOUND) THEN
 154     IF (pPoheadId = -1) THEN
 155       RAISE EXCEPTION 'Problem creating new PO';
 156     END IF;
 157     _poheadid := _temp;
 158     UPDATE pohead
 159     SET pohead_dropship = pDropShip
 160     WHERE (pohead_id = _poheadid);
 161   ELSE
 162     IF (pPoheadId > 0) THEN
 163       RAISE EXCEPTION 'Problem adding to existing PO';
 164     END IF;
 165     SELECT NEXTVAL('pohead_pohead_id_seq') INTO _poheadid;
 166     SELECT fetchPoNumber() INTO _ponumber;
 167
 168     IF (pDropShip) THEN
 169       INSERT INTO pohead
 170         ( pohead_id, pohead_number, pohead_status, pohead_dropship,
 171           pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
 172           pohead_orderdate, pohead_curr_id, pohead_cohead_id,
 173           pohead_warehous_id, pohead_shipvia,
 174           pohead_terms_id, pohead_shipto_cntct_id,
 175           pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
 176           pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
 177           pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
 178           pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
 179           pohead_shipto_cntct_email, pohead_shiptoaddress_id,
 180           pohead_shiptoname,
 181           pohead_shiptoaddress1,
 182           pohead_shiptoaddress2,
 183           pohead_shiptoaddress3,
 184           pohead_shiptocity,
 185           pohead_shiptostate, pohead_shiptozipcode,
 186           pohead_shiptocountry, pohead_vend_cntct_id,
 187           pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
 188           pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
 189           pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
 190           pohead_vend_cntct_title, pohead_vend_cntct_fax,
 191           pohead_vend_cntct_email, pohead_vendaddress1,
 192           pohead_vendaddress2, pohead_vendaddress3,
 193           pohead_vendcity, pohead_vendstate,
 194           pohead_vendzipcode, pohead_vendcountry, pohead_comments )
 195       VALUES
 196         ( _poheadid, _ponumber, 'U', pDropShip,
 197           getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
 198           CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()), _s.cohead_id,
 199           COALESCE(_s.cohead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
 200           COALESCE(_i.vend_terms_id, -1), COALESCE(_s.cohead_shipto_cntct_id, _s.shipto_cntct_id),
 201           COALESCE(_s.cohead_shipto_cntct_honorific, _s.cntct_honorific), COALESCE(_s.cohead_shipto_cntct_first_name, _s.cntct_first_name),
 202           COALESCE(_s.cohead_shipto_cntct_middle, _s.cntct_middle), COALESCE(_s.cohead_shipto_cntct_last_name, _s.cntct_last_name),
 203           COALESCE(_s.cohead_shipto_cntct_suffix, _s.cntct_suffix), COALESCE(_s.cohead_shipto_cntct_phone, _s.cntct_phone),
 204           COALESCE(_s.cohead_shipto_cntct_title, _s.cntct_title), COALESCE(_s.cohead_shipto_cntct_fax, _s.cntct_fax),
 205           COALESCE(_s.cohead_shipto_cntct_email, _s.cntct_email), COALESCE(_s.shipto_addr_id, _s.addr_id),
 206           COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''),
 207           COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''),
 208           COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''),
 209           COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''),
 210           COALESCE(_s.cohead_shiptocity, _s.addr_city, ''),
 211           COALESCE(_s.cohead_shiptostate, _s.addr_state, ''), COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''),
 212           COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''), _i.cntct_id,
 213           COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
 214           COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
 215           COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
 216           COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
 217           COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
 218           COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
 219           COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
 220           COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')), COALESCE(_s.cohead_shipcomments, TEXT('')) );
 221     ELSE
 222       INSERT INTO pohead
 223         ( pohead_id, pohead_number, pohead_status, pohead_dropship,
 224           pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
 225           pohead_orderdate, pohead_curr_id, pohead_cohead_id,
 226           pohead_warehous_id, pohead_shipvia,
 227           pohead_terms_id, pohead_shipto_cntct_id,
 228           pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
 229           pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
 230           pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
 231           pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
 232           pohead_shipto_cntct_email, pohead_shiptoaddress_id,
 233           pohead_shiptoaddress1,
 234           pohead_shiptoaddress2,
 235           pohead_shiptoaddress3,
 236           pohead_shiptocity,
 237           pohead_shiptostate, pohead_shiptozipcode,
 238           pohead_shiptocountry, pohead_vend_cntct_id,
 239           pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
 240           pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
 241           pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
 242           pohead_vend_cntct_title, pohead_vend_cntct_fax,
 243           pohead_vend_cntct_email, pohead_vendaddress1,
 244           pohead_vendaddress2, pohead_vendaddress3,
 245           pohead_vendcity, pohead_vendstate,
 246           pohead_vendzipcode, pohead_vendcountry )
 247       VALUES
 248         ( _poheadid, _ponumber, 'U', pDropShip,
 249           getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
 250           CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()), _s.cohead_id,
 251           COALESCE(_s.cohead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
 252           COALESCE(_i.vend_terms_id, -1), _w.cntct_id,
 253           _w.cntct_honorific, _w.cntct_first_name,
 254           _w.cntct_middle, _w.cntct_last_name,
 255           _w.cntct_suffix, _w.cntct_phone,
 256           _w.cntct_title, _w.cntct_fax,
 257           _w.cntct_email, _w.addr_id,
 258           COALESCE(_w.addr_line1, ''),
 259           COALESCE(_w.addr_line2, ''),
 260           COALESCE(_w.addr_line3, ''),
 261           COALESCE(_w.addr_city, ''),
 262           COALESCE(_w.addr_state, ''), COALESCE(_w.addr_postalcode, ''),
 263           COALESCE(_w.addr_country, ''), _i.cntct_id,
 264           COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
 265           COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
 266           COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
 267           COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
 268           COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
 269           COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
 270           COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
 271           COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')) );
 272     END IF;
 273   END IF;
 274
 275   SELECT NEXTVAL('poitem_poitem_id_seq') INTO _poitemid;
 276
 277   SELECT (COALESCE(MAX(poitem_linenumber), 0) + 1) INTO _polinenumber
 278   FROM poitem
 279   WHERE (poitem_pohead_id = _poheadid);
 280
 281   SELECT COALESCE(itemtax_taxtype_id, -1) INTO _taxtypeid
 282   FROM itemtax
 283   WHERE (itemtax_item_id = _i.itemsrc_item_id);
 284
 285   IF (pPrice IS NULL) THEN
 286     SELECT itemsrcPrice(pItemSourceId,
 287                         COALESCE(_s.cohead_warehous_id, -1),
 288                         pDropShip,
 289                         (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
 290                         COALESCE(_i.vend_curr_id, baseCurrId()),
 291                         CURRENT_DATE) INTO _price;
 292   ELSE
 293     _price := pPrice;
 294   END IF;
 295   raise notice '_price=%', _price;
 296
 297   IF (pDropShip) THEN
 298     INSERT INTO poitem
 299       ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber,
 300         poitem_duedate, poitem_itemsite_id,
 301         poitem_vend_item_descrip, poitem_vend_uom,
 302         poitem_invvenduomratio, poitem_qty_ordered,
 303         poitem_unitprice, poitem_vend_item_number,
 304         poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost,
 305         poitem_manuf_name, poitem_manuf_item_number,
 306         poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
 307     VALUES
 308       ( _poitemid, 'U', _poheadid, _polinenumber,
 309         COALESCE(pDueDate, _s.coitem_scheddate), _s.coitem_itemsite_id,
 310         COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
 311         COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
 312         _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
 313         pItemSourceId, pCoitemId, 'S', _s.cohead_prj_id, stdcost(_i.itemsrc_item_id),
 314         COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
 315         COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
 316         COALESCE(_s.coitem_memo, TEXT('')));
 317   ELSE
 318     INSERT INTO poitem
 319       ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber,
 320         poitem_duedate, poitem_itemsite_id,
 321         poitem_vend_item_descrip, poitem_vend_uom,
 322         poitem_invvenduomratio, poitem_qty_ordered,
 323         poitem_unitprice, poitem_vend_item_number,
 324         poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost,
 325         poitem_manuf_name, poitem_manuf_item_number,
 326         poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
 327     VALUES
 328       ( _poitemid, 'U', _poheadid, _polinenumber,
 329         COALESCE(pDueDate, _s.coitem_scheddate), _s.coitem_itemsite_id,
 330         COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
 331         COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
 332         _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
 333         pItemSourceId, pCoitemId, 'S', _s.cohead_prj_id, stdcost(_i.itemsrc_item_id),
 334         COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
 335         COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
 336         COALESCE(_s.coitem_memo, TEXT('')));
 337   END IF;
 338   -- Copy characteristics from the coitem to the poitem
 339   INSERT INTO charass
 340     ( charass_target_type, charass_target_id, charass_char_id,
 341       charass_value, charass_default, charass_price )
 342   SELECT 'PI', _poitemid, charass_char_id,
 343          charass_value, charass_default, charass_price
 344   FROM charass
 345   WHERE ( (charass_target_type='SI')
 346     AND   (charass_target_id=pCoitemId) );
 347
 348   UPDATE coitem
 349   SET coitem_order_type = 'P',
 350       coitem_order_id = _poitemid
 351   WHERE ( coitem_id = pCoitemId );
 352
 353   -- Generate the PoItemCreatedBySo event notice
 354   PERFORM postEvent('PoItemCreatedBySo', 'P', poitem_id,
 355                     itemsite_warehous_id,
 356                     (pohead_number || '-'|| poitem_linenumber || ': ' || item_number),
 357                     NULL, NULL, NULL, NULL)
 358   FROM poitem JOIN pohead ON (pohead_id=poitem_pohead_id)
 359               JOIN itemsite ON (itemsite_id=poitem_itemsite_id)
 360               JOIN item ON (item_id=itemsite_item_id)
 361   WHERE (poitem_id=_poitemid)
 362     AND (poitem_duedate <= (CURRENT_DATE + itemsite_eventfence));
 363
 364   RETURN _poitemid;
 365
 366 END;
 367 $$ LANGUAGE 'plpgsql' VOLATILE;