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