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

   1
   2 CREATE OR REPLACE FUNCTION convertQuote(INTEGER) RETURNS INTEGER AS $$
   3 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   4 -- See www.xtuple.com/CPAL for the full text of the software license.
   5 DECLARE
   6   pQuheadid ALIAS FOR $1;
   7   _qunumber TEXT;
   8   _ponumber TEXT;
   9   _soheadid INTEGER;
  10   _soitemid INTEGER;
  11   _orderid INTEGER;
  12   _ordertype CHARACTER(1);
  13   _creditstatus TEXT;
  14   _usespos BOOLEAN := false;
  15   _blanketpos BOOLEAN := true;
  16   _showConvertedQuote BOOLEAN := false;
  17   _prospectid   INTEGER;
  18   _r RECORD;
  19   _soNum TEXT;
  20
  21 BEGIN
  22
  23 -- Check to make sure the quote has not expired
  24   IF (SELECT COALESCE(quhead_expire, endOfTime()) < CURRENT_DATE
  25         FROM quhead
  26        WHERE(quhead_id=pQuheadid)) THEN
  27     RETURN -6;
  28   END IF;
  29
  30 --  Check to make sure that all of the quote items have a valid itemsite
  31   SELECT quitem_id INTO _r
  32     FROM quitem LEFT OUTER JOIN itemsite ON (quitem_itemsite_id=itemsite_id)
  33    WHERE ((itemsite_id IS NULL)
  34      AND  (quitem_quhead_id=pQuheadid));
  35   IF (FOUND) THEN
  36     PERFORM postEvent('CannotConvertQuote', 'Q', quhead_id,
  37                       quhead_warehous_id, quhead_number,
  38                       NULL, NULL, NULL, NULL)
  39     FROM quhead
  40     WHERE (quhead_id=pQuheadid);
  41
  42     RETURN -1;
  43   END IF;
  44
  45   SELECT cust_creditstatus, cust_usespos, cust_blanketpos
  46     INTO _creditstatus, _usespos, _blanketpos
  47   FROM quhead, custinfo
  48   WHERE ((quhead_cust_id=cust_id)
  49     AND  (quhead_id=pQuheadid));
  50
  51   IF (NOT FOUND) THEN
  52     SELECT prospect_id INTO _prospectid
  53     FROM quhead, prospect
  54     WHERE ((quhead_cust_id=prospect_id)
  55       AND  (quhead_id=pQuheadid));
  56     IF (NOT FOUND) THEN
  57       RETURN -2;
  58     ELSE
  59       RETURN -3;
  60     END IF;
  61   ELSIF (_creditstatus = 'H' AND NOT checkPrivilege('CreateSOForHoldCustomer')) THEN
  62     RETURN -4;
  63   ELSIF (_creditstatus = 'W' AND NOT checkPrivilege('CreateSOForWarnCustomer')) THEN
  64     RETURN -5;
  65   END IF;
  66
  67   IF (_usespos) THEN
  68     SELECT quhead_number INTO _qunumber
  69     FROM quhead
  70     WHERE (quhead_id=pQuheadid)
  71       AND (COALESCE(quhead_custponumber, '') = '');
  72     IF (FOUND) THEN
  73       RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
  74                       _qunumber, _qunumber;
  75     END IF;
  76   END IF;
  77
  78   IF ( (_usespos) AND (NOT _blanketpos) ) THEN
  79     SELECT quhead_number, quhead_custponumber INTO _qunumber, _ponumber
  80     FROM quhead JOIN cohead ON ( (cohead_cust_id=quhead_cust_id) AND
  81                                  (UPPER(cohead_custponumber)=UPPER(quhead_custponumber)) )
  82     WHERE (quhead_id=pQuheadid);
  83     IF (FOUND) THEN
  84       RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
  85                       _ponumber, _qunumber,
  86                       _ponumber, _qunumber;
  87     END IF;
  88   END IF;
  89
  90   PERFORM quhead_number, cohead_id
  91   FROM quhead, cohead
  92   WHERE quhead_id = pQuheadid
  93   AND cohead_number = quhead_number;
  94
  95   IF (FOUND) THEN
  96     SELECT fetchSoNumber() INTO _soNum;
  97   ELSE
  98     SELECT quhead_number INTO _soNum
  99     FROM quhead
 100     WHERE quhead_id = pQuheadid;
 101   END IF;
 102
 103   SELECT NEXTVAL('cohead_cohead_id_seq') INTO _soheadid;
 104   INSERT INTO cohead
 105   ( cohead_id, cohead_number, cohead_cust_id,
 106     cohead_orderdate, cohead_packdate,
 107     cohead_custponumber, cohead_warehous_id,
 108     cohead_billtoname, cohead_billtoaddress1,
 109     cohead_billtoaddress2, cohead_billtoaddress3,
 110     cohead_billtocity, cohead_billtostate, cohead_billtozipcode,
 111     cohead_billtocountry,
 112     cohead_shipto_id, cohead_shiptoname, cohead_shiptoaddress1,
 113     cohead_shiptoaddress2, cohead_shiptoaddress3,
 114     cohead_shiptocity, cohead_shiptostate, cohead_shiptozipcode,
 115     cohead_shiptocountry,
 116     cohead_salesrep_id, cohead_commission,
 117     cohead_terms_id, cohead_shipchrg_id, cohead_shipform_id,
 118     cohead_fob, cohead_shipvia,
 119     cohead_ordercomments, cohead_shipcomments,
 120     cohead_freight, cohead_misc, cohead_misc_accnt_id, cohead_misc_descrip,
 121     cohead_holdtype, cohead_wasquote, cohead_quote_number, cohead_prj_id,
 122     cohead_curr_id, cohead_taxzone_id, cohead_taxtype_id,
 123     cohead_shipto_cntct_id, cohead_shipto_cntct_honorific, cohead_shipto_cntct_first_name,
 124     cohead_shipto_cntct_middle, cohead_shipto_cntct_last_name, cohead_shipto_cntct_suffix,
 125     cohead_shipto_cntct_phone, cohead_shipto_cntct_title, cohead_shipto_cntct_fax,
 126     cohead_shipto_cntct_email,
 127     cohead_billto_cntct_id, cohead_billto_cntct_honorific,
 128     cohead_billto_cntct_first_name, cohead_billto_cntct_middle, cohead_billto_cntct_last_name,
 129     cohead_billto_cntct_suffix, cohead_billto_cntct_phone, cohead_billto_cntct_title,
 130     cohead_billto_cntct_fax, cohead_billto_cntct_email, cohead_ophead_id,
 131     cohead_calcfreight, cohead_saletype_id, cohead_shipzone_id )
 132   SELECT _soheadid, _soNum, quhead_cust_id,
 133          CURRENT_DATE, quhead_packdate,
 134          quhead_custponumber, quhead_warehous_id,
 135          quhead_billtoname, quhead_billtoaddress1,
 136          quhead_billtoaddress2, quhead_billtoaddress3,
 137          quhead_billtocity, quhead_billtostate, quhead_billtozip,
 138          quhead_billtocountry,
 139          quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1,
 140          quhead_shiptoaddress2, quhead_shiptoaddress3,
 141          quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode,
 142          quhead_shiptocountry,
 143          quhead_salesrep_id, quhead_commission,
 144          quhead_terms_id, cust_shipchrg_id, cust_shipform_id,
 145          quhead_fob, quhead_shipvia,
 146          quhead_ordercomments, quhead_shipcomments,
 147          quhead_freight, quhead_misc, quhead_misc_accnt_id, quhead_misc_descrip,
 148          'N', TRUE, quhead_number, quhead_prj_id,
 149          quhead_curr_id, quhead_taxzone_id, quhead_taxtype_id,
 150          quhead_shipto_cntct_id, quhead_shipto_cntct_honorific,
 151          quhead_shipto_cntct_first_name, quhead_shipto_cntct_middle, quhead_shipto_cntct_last_name,
 152          quhead_shipto_cntct_suffix, quhead_shipto_cntct_phone, quhead_shipto_cntct_title,
 153          quhead_shipto_cntct_fax, quhead_shipto_cntct_email, quhead_billto_cntct_id,
 154          quhead_billto_cntct_honorific, quhead_billto_cntct_first_name, quhead_billto_cntct_middle,
 155          quhead_billto_cntct_last_name, quhead_billto_cntct_suffix, quhead_billto_cntct_phone,
 156          quhead_billto_cntct_title, quhead_billto_cntct_fax, quhead_billto_cntct_email, quhead_ophead_id,
 157          quhead_calcfreight, quhead_saletype_id, quhead_shipzone_id
 158   FROM quhead JOIN custinfo ON (cust_id=quhead_cust_id)
 159   WHERE (quhead_id=pQuheadid);
 160
 161   -- Move Documents
 162   UPDATE url SET url_source_id = _soheadid,
 163                  url_source = 'S'
 164   WHERE ((url_source='Q') AND (url_source_id = pQuheadid));
 165
 166   UPDATE imageass SET imageass_source_id = _soheadid,
 167                       imageass_source = 'S'
 168   WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));
 169
 170   UPDATE docass SET docass_source_id = _soheadid,
 171                     docass_source_type = 'S'
 172   WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));
 173
 174   -- Move Email
 175   IF (fetchMetricBool('EnableBatchManager')) THEN
 176     UPDATE xtbatch.emlassc SET emlassc_type='S',
 177                                emlassc_assc_id=_soheadid
 178     WHERE ((emlassc_type='Q') AND (emlassc_assc_id=pQuheadid));
 179   END IF;
 180
 181   -- Copy Characteristics
 182   INSERT INTO charass
 183         (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
 184   SELECT 'SO', _soheadid, charass_char_id, charass_value, charass_default, charass_price
 185     FROM charass
 186    WHERE ((charass_target_type='QU')
 187      AND  (charass_target_id=pQuheadid));
 188
 189   -- Copy Comments
 190   INSERT INTO comment
 191   ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
 192   SELECT comment_cmnttype_id, 'S', _soheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
 193   FROM comment
 194   WHERE ( (comment_source='Q')
 195     AND   (comment_source_id=pQuheadid) );
 196
 197   FOR _r IN SELECT quitem.*,
 198                    quhead_number, quhead_prj_id, quhead_saletype_id,
 199                    itemsite_item_id, itemsite_leadtime,
 200                    itemsite_createsopo, itemsite_createsopr,
 201                    item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
 202             FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
 203                         JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
 204                         JOIN item ON (item_id=itemsite_item_id)
 205                         LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
 206                                                      (itemsrc_default) )
 207             WHERE (quhead_id=pQuheadid)
 208             ORDER BY quitem_linenumber LOOP
 209
 210     SELECT NEXTVAL('coitem_coitem_id_seq') INTO _soitemid;
 211
 212     INSERT INTO coitem
 213     ( coitem_id, coitem_cohead_id, coitem_linenumber, coitem_itemsite_id,
 214       coitem_status, coitem_scheddate, coitem_promdate,
 215       coitem_price, coitem_custprice,
 216       coitem_qtyord, coitem_qtyshipped, coitem_qtyreturned,
 217       coitem_qty_uom_id, coitem_qty_invuomratio,
 218       coitem_price_uom_id, coitem_price_invuomratio,
 219       coitem_unitcost, coitem_prcost,
 220       coitem_custpn, coitem_memo, coitem_taxtype_id, coitem_order_id )
 221     VALUES
 222     ( _soitemid, _soheadid, _r.quitem_linenumber, _r.quitem_itemsite_id,
 223       'O', _r.quitem_scheddate, _r.quitem_promdate,
 224       _r.quitem_price, _r.quitem_custprice,
 225       _r.quitem_qtyord, 0, 0,
 226       _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
 227       _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
 228       stdcost(_r.itemsite_item_id), _r.quitem_prcost,
 229       _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id, -1 );
 230
 231     IF (fetchMetricBool('enablextcommissionission')) THEN
 232       PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
 233                                         _r.itemsite_item_id, _r.quhead_saletype_id,
 234                                         _r.quitem_price, _r.quitem_custprice,
 235                                         _soitemid, 'SalesItem')
 236       FROM quhead
 237       WHERE (quhead_id=pQuheadid);
 238     END IF;
 239
 240     -- Copy Characteristics
 241     INSERT INTO charass
 242           (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
 243     SELECT 'SI', _soitemid, charass_char_id, charass_value, charass_default, charass_price
 244       FROM charass
 245      WHERE ((charass_target_type='QI')
 246        AND  (charass_target_id=_r.quitem_id));
 247
 248     -- Copy Comments
 249     INSERT INTO comment
 250     ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
 251     SELECT comment_cmnttype_id, 'SI', _soitemid, comment_date, comment_user, ('Quote-' || comment_text)
 252     FROM comment
 253     WHERE ( (comment_source='QI')
 254       AND   (comment_source_id=_r.quitem_id) );
 255
 256     _orderid := -1;
 257     _ordertype := '';
 258     IF (_r.quitem_createorder) THEN
 259
 260       IF (_r.item_type IN ('M')) THEN
 261         SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
 262                          _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
 263         FROM itemsite sold, itemsite supply
 264         WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
 265          AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
 266          AND (sold.itemsite_id=_r.quitem_itemsite_id) );
 267         _orderType := 'W';
 268
 269         INSERT INTO charass
 270               (charass_target_type, charass_target_id, charass_char_id, charass_value)
 271         SELECT 'W', _orderId, charass_char_id, charass_value
 272           FROM charass
 273          WHERE ((charass_target_type='QI')
 274            AND  (charass_target_id=_r.quitem_id));
 275
 276       ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
 277         SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
 278                          _r.quitem_scheddate, '', 'S', _soitemid ) INTO _orderId;
 279         _orderType := 'R';
 280         UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
 281       ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
 282         IF (_r.quitem_prcost=0) THEN
 283           SELECT createPurchaseToSale(_soitemid, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
 284         ELSE
 285           SELECT createPurchaseToSale(_soitemid, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
 286         END IF;
 287         _orderType := 'P';
 288       END IF;
 289
 290       UPDATE coitem SET coitem_order_type=_ordertype, coitem_order_id=_orderid
 291       WHERE (coitem_id=_soitemid);
 292
 293     END IF;
 294
 295   END LOOP;
 296
 297   SELECT metric_value INTO _showConvertedQuote
 298   FROM metric WHERE metric_name = 'ShowQuotesAfterSO';
 299
 300   IF (_showConvertedQuote) THEN
 301     UPDATE quhead
 302     SET quhead_status= 'C'
 303     WHERE (quhead_id = pQuheadid);
 304   ELSE
 305   PERFORM deleteQuote(pQuheadid);
 306   END IF;
 307
 308   RETURN _soheadid;
 309
 310 END;
 311 $$ LANGUAGE 'plpgsql';
 312