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   UPDATE url SET url_source_id = _soheadid,
 147                  url_source = 'S'
 148   WHERE ((url_source='Q') AND (url_source_id = pQuheadid));
 149
 150   UPDATE imageass SET imageass_source_id = _soheadid,
 151                       imageass_source = 'S'
 152   WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));
 153
 154   UPDATE docass SET docass_source_id = _soheadid,
 155                     docass_source_type = 'S'
 156   WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));
 157
 158   -- Copy Comments
 159   INSERT INTO comment
 160   ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
 161   SELECT comment_cmnttype_id, 'S', _soheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
 162   FROM comment
 163   WHERE ( (comment_source='Q')
 164     AND   (comment_source_id=pQuheadid) );
 165
 166   FOR _r IN SELECT quitem.*,
 167                    quhead_number, quhead_prj_id,
 168                    itemsite_item_id, itemsite_leadtime,
 169                    itemsite_createsopo, itemsite_createsopr,
 170                    item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
 171             FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
 172                         JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
 173                         JOIN item ON (item_id=itemsite_item_id)
 174                         LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
 175                                                      (itemsrc_default) )
 176             WHERE (quhead_id=pQuheadid)
 177             ORDER BY quitem_linenumber LOOP
 178
 179     SELECT NEXTVAL('coitem_coitem_id_seq') INTO _soitemid;
 180
 181     INSERT INTO coitem
 182     ( coitem_id, coitem_cohead_id, coitem_linenumber, coitem_itemsite_id,
 183       coitem_status, coitem_scheddate, coitem_promdate,
 184       coitem_price, coitem_custprice,
 185       coitem_qtyord, coitem_qtyshipped, coitem_qtyreturned,
 186       coitem_qty_uom_id, coitem_qty_invuomratio,
 187       coitem_price_uom_id, coitem_price_invuomratio,
 188       coitem_unitcost, coitem_prcost,
 189       coitem_custpn, coitem_memo, coitem_taxtype_id, coitem_order_id )
 190     VALUES
 191     ( _soitemid, _soheadid, _r.quitem_linenumber, _r.quitem_itemsite_id,
 192       'O', _r.quitem_scheddate, _r.quitem_promdate,
 193       _r.quitem_price, _r.quitem_custprice,
 194       _r.quitem_qtyord, 0, 0,
 195       _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
 196       _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
 197       stdcost(_r.itemsite_item_id), _r.quitem_prcost,
 198       _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id, -1 );
 199
 200     IF (fetchMetricBool('enablextcommissionission')) THEN
 201       PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
 202                                         _r.itemsite_item_id, _r.quitem_price,
 203                                         _soitemid, 'SalesItem')
 204       FROM quhead
 205       WHERE (quhead_id=pQuheadid);
 206     END IF;
 207
 208     INSERT INTO charass
 209           (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
 210     SELECT 'SI', _soitemid, charass_char_id, charass_value, charass_default, charass_price
 211       FROM charass
 212      WHERE ((charass_target_type='QI')
 213        AND  (charass_target_id=_r.quitem_id));
 214
 215     -- Copy Comments
 216     INSERT INTO comment
 217     ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
 218     SELECT comment_cmnttype_id, 'SI', _soitemid, comment_date, comment_user, ('Quote-' || comment_text)
 219     FROM comment
 220     WHERE ( (comment_source='QI')
 221       AND   (comment_source_id=_r.quitem_id) );
 222
 223     _orderid := -1;
 224     _ordertype := '';
 225     IF (_r.quitem_createorder) THEN
 226
 227       IF (_r.item_type IN ('M')) THEN
 228         SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
 229                          _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'S', _soitemid, _r.quhead_prj_id ) INTO _orderId
 230         FROM itemsite sold, itemsite supply
 231         WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
 232          AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
 233          AND (sold.itemsite_id=_r.quitem_itemsite_id) );
 234         _orderType := 'W';
 235
 236         INSERT INTO charass
 237               (charass_target_type, charass_target_id, charass_char_id, charass_value)
 238         SELECT 'W', _orderId, charass_char_id, charass_value
 239           FROM charass
 240          WHERE ((charass_target_type='QI')
 241            AND  (charass_target_id=_r.quitem_id));
 242
 243       ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
 244         SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
 245                          _r.quitem_scheddate, '', 'S', _soitemid ) INTO _orderId;
 246         _orderType := 'R';
 247         UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
 248       ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
 249         IF (_r.quitem_prcost=0) THEN
 250           SELECT createPurchaseToSale(_soitemid, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
 251         ELSE
 252           SELECT createPurchaseToSale(_soitemid, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
 253         END IF;
 254         _orderType := 'P';
 255       END IF;
 256
 257       UPDATE coitem SET coitem_order_type=_ordertype, coitem_order_id=_orderid
 258       WHERE (coitem_id=_soitemid);
 259
 260     END IF;
 261
 262   END LOOP;
 263
 264   SELECT metric_value INTO _showConvertedQuote
 265   FROM metric WHERE metric_name = 'ShowQuotesAfterSO';
 266
 267   IF (_showConvertedQuote) THEN
 268     UPDATE quhead
 269     SET quhead_status= 'C'
 270     WHERE (quhead_id = pQuheadid);
 271   ELSE
 272   PERFORM deleteQuote(pQuheadid);
 273   END IF;
 274
 275   RETURN _soheadid;
 276
 277 END;
 278 $$ LANGUAGE 'plpgsql';
 279