2 CREATE OR REPLACE FUNCTION convertQuoteToInvoice(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.
6 pQuheadid ALIAS FOR $1;
12 _ordertype CHARACTER(1);
14 _usespos BOOLEAN := false;
15 _blanketpos BOOLEAN := true;
16 _showConvertedQuote BOOLEAN := false;
23 -- Check to make sure the quote has not expired
24 IF (SELECT COALESCE(quhead_expire, endOfTime()) < CURRENT_DATE
26 WHERE(quhead_id=pQuheadid)) THEN
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));
36 PERFORM postEvent('CannotConvertQuote', 'Q', quhead_id,
37 quhead_warehous_id, quhead_number,
38 NULL, NULL, NULL, NULL)
40 WHERE (quhead_id=pQuheadid);
45 -- Get Credit Stat, Uses POs and Blanket POs
47 SELECT cust_creditstatus, cust_usespos, cust_blanketpos
48 INTO _creditstatus, _usespos, _blanketpos
50 WHERE ((quhead_cust_id=cust_id)
51 AND (quhead_id=pQuheadid));
53 -- Check to see if customer or prospect
56 SELECT prospect_id INTO _prospectid
58 WHERE ((quhead_cust_id=prospect_id)
59 AND (quhead_id=pQuheadid));
65 ELSIF (_creditstatus = 'H' AND NOT hasPriv('CreateSOForHoldCustomer')) THEN
67 ELSIF (_creditstatus = 'W' AND NOT hasPriv('CreateSOForWarnCustomer')) THEN
71 -- PO/blanket PO checks
73 SELECT quhead_number, COALESCE(quhead_custponumber, ''), invchead_id INTO _qunumber, _ponumber, _iheadid
74 FROM quhead LEFT OUTER JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
75 (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
76 WHERE (quhead_id=pQuheadid);
77 IF (_ponumber = '') THEN
78 RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
82 IF ( (NOT _blanketpos) AND (_iheadid IS NOT NULL) ) THEN
83 RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
91 SELECT quhead_number INTO _qunumber
93 WHERE (quhead_id=pQuheadid)
94 AND (COALESCE(quhead_custponumber, '') = '');
96 RAISE EXCEPTION 'Customer PO required for Quote % [xtuple: convertQuote, -7, %]',
101 IF ( (_usespos) AND (NOT _blanketpos) ) THEN
102 SELECT quhead_number, quhead_custponumber INTO _qunumber, _ponumber
103 FROM quhead JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
104 (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
105 WHERE (quhead_id=pQuheadid);
107 RAISE EXCEPTION 'Duplicate Customer PO % for Quote % [xtuple: convertQuote, -8, %, %]',
108 _ponumber, _qunumber,
109 _ponumber, _qunumber;
113 --Check to see if an invoice exists with the quote number
115 PERFORM quhead_number, invchead_id
116 FROM quhead, invchead
117 WHERE quhead_id = pQuheadid
118 AND invchead_invcnumber = quhead_number;
120 -- If it does then get a new Invoice number otherwise use the quote number as the invoice number
123 SELECT fetchinvcnumber() INTO _inNum;
125 SELECT quhead_number INTO _inNum
127 WHERE quhead_id = pQuheadid;
130 --Insert quote info into invoice tables
132 SELECT NEXTVAL('invchead_invchead_id_seq') INTO _iheadid;
134 ( invchead_ordernumber, invchead_shipdate, invchead_recurring,
135 invchead_id, invchead_invcnumber, invchead_cust_id,
136 invchead_orderdate, invchead_ponumber,
137 invchead_billto_name, invchead_billto_address1,
138 invchead_billto_address2, invchead_billto_address3,
139 invchead_billto_city, invchead_billto_state, invchead_billto_zipcode, invchead_billto_country,
140 invchead_shipto_id, invchead_shipto_name, invchead_shipto_address1,
141 invchead_shipto_address2, invchead_shipto_address3,
142 invchead_shipto_city, invchead_shipto_state, invchead_shipto_zipcode, invchead_shipto_country,
143 invchead_salesrep_id, invchead_commission,
144 invchead_terms_id, invchead_shipchrg_id, invchead_fob, invchead_shipvia,
145 invchead_notes, invchead_freight,
146 invchead_misc_amount, invchead_misc_accnt_id, invchead_misc_descrip,
147 invchead_prj_id, invchead_curr_id, invchead_taxzone_id,
148 invchead_posted, invchead_printed, invchead_invcdate,
149 invchead_saletype_id, invchead_shipzone_id
150 --invchead_taxtype_id,
151 --invchead_shipto_cntct_id, invchead_shipto_cntct_honorific, invchead_shipto_cntct_first_name,
152 --invchead_shipto_cntct_middle, invchead_shipto_cntct_last_name, invchead_shipto_cntct_suffix,
153 --invchead_shipto_cntct_phone, invchead_shipto_cntct_title, invchead_shipto_cntct_fax,
154 --invchead_shipto_cntct_email,
155 --invchead_billto_cntct_id, invchead_billto_cntct_honorific,
156 --invchead_billto_cntct_first_name, invchead_billto_cntct_middle, invchead_billto_cntct_last_name,
157 --invchead_billto_cntct_suffix, invchead_billto_cntct_phone, invchead_billto_cntct_title,
158 --invchead_billto_cntct_fax, invchead_billto_cntct_email,
159 --invchead_ophead_id,
160 --invchead_calcfreight
162 SELECT quhead_number, quhead_packdate, 'f',
163 _iheadid, _inNum, quhead_cust_id,
164 CURRENT_DATE, quhead_custponumber,
165 quhead_billtoname, quhead_billtoaddress1,
166 quhead_billtoaddress2, quhead_billtoaddress3,
167 quhead_billtocity, quhead_billtostate, quhead_billtozip, quhead_billtocountry,
168 quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1,
169 quhead_shiptoaddress2, quhead_shiptoaddress3,
170 quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode, quhead_shiptocountry,
171 quhead_salesrep_id, quhead_commission,
172 quhead_terms_id, cust_shipchrg_id, quhead_fob, quhead_shipvia,
173 quhead_ordercomments, quhead_freight,
174 quhead_misc, quhead_misc_accnt_id, quhead_misc_descrip,
175 quhead_prj_id, quhead_curr_id, quhead_taxzone_id,
176 'f','f',current_date,
177 quhead_saletype_id, quhead_shipzone_id
178 --quhead_shipto_cntct_id, quhead_shipto_cntct_honorific,
179 --quhead_shipto_cntct_first_name, quhead_shipto_cntct_middle, quhead_shipto_cntct_last_name,
180 --quhead_shipto_cntct_suffix, quhead_shipto_cntct_phone, quhead_shipto_cntct_title,
181 --quhead_shipto_cntct_fax, quhead_shipto_cntct_email, quhead_billto_cntct_id,
182 --quhead_billto_cntct_honorific, quhead_billto_cntct_first_name, quhead_billto_cntct_middle,
183 --quhead_billto_cntct_last_name, quhead_billto_cntct_suffix, quhead_billto_cntct_phone,
184 --quhead_billto_cntct_title, quhead_billto_cntct_fax, quhead_billto_cntct_email, quhead_ophead_id,
186 FROM quhead JOIN custinfo ON (cust_id=quhead_cust_id)
187 WHERE (quhead_id=pQuheadid);
189 -- Attachments on Invoice not supported but leaving this in for future use:
191 UPDATE url SET url_source_id = _iheadid,
193 WHERE ((url_source='Q') AND (url_source_id = pQuheadid));
195 UPDATE imageass SET imageass_source_id = _iheadid,
196 imageass_source = 'I'
197 WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));
199 UPDATE docass SET docass_source_id = _iheadid,
200 docass_source_type = 'I'
201 WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));
205 -- Comments not supported on Invoice but leaving this in for future use:
209 ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
210 SELECT comment_cmnttype_id, 'I', _iheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
212 WHERE ( (comment_source='Q')
213 AND (comment_source_id=pQuheadid) );
216 FOR _r IN SELECT quitem.*,
217 quhead_number, quhead_prj_id, quhead_saletype_id,
218 itemsite_item_id, itemsite_leadtime,
219 itemsite_createsopo, itemsite_createsopr,
220 item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
221 FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
222 JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
223 JOIN item ON (item_id=itemsite_item_id)
224 LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
226 WHERE (quhead_id=pQuheadid) LOOP
228 SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _iitemid;
231 ( invcitem_id, invcitem_invchead_id, invcitem_linenumber,
233 invcitem_warehous_id,
235 --invcitem_scheddate, invcitem_promdate,
236 invcitem_price, invcitem_custprice,
237 invcitem_ordered, invcitem_billed,
238 invcitem_qty_uom_id, invcitem_qty_invuomratio,
239 invcitem_price_uom_id, invcitem_price_invuomratio,
240 invcitem_custpn, invcitem_notes, invcitem_taxtype_id )
242 ( _iitemid, _iheadid, _r.quitem_linenumber,
243 (SELECT itemsite_item_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
244 (SELECT itemsite_warehous_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
246 --_r.quitem_scheddate, _r.quitem_promdate,
247 _r.quitem_price, _r.quitem_custprice,
248 _r.quitem_qtyord, _r.quitem_qtyord,
249 _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
250 _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
251 _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id );
253 IF (fetchMetricBool('enablextcommissionission')) THEN
254 PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
255 _r.itemsite_item_id, _r.quhead_saletype_id,
256 _r.quitem_price, _r.quitem_custprice,
257 _iitemid, 'InvoiceItem')
259 WHERE (quhead_id=pQuheadid);
262 -- Chracteristics not supported on Invoice but leaving in for future use:
266 (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
267 SELECT 'SI', _iitemid, charass_char_id, charass_value, charass_default, charass_price
269 WHERE ((charass_target_type='QI')
270 AND (charass_target_id=_r.quitem_id));
274 -- Comments not supported but leaving in for future use
278 ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
279 SELECT comment_cmnttype_id, 'SI', _iitemid, comment_date, comment_user, ('Quote-' || comment_text)
281 WHERE ( (comment_source='QI')
282 AND (comment_source_id=_r.quitem_id) );
287 IF (_r.quitem_createorder) THEN
289 IF (_r.item_type IN ('M')) THEN
290 SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
291 _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'Q', _iitemid, _r.quhead_prj_id ) INTO _orderId
292 FROM itemsite sold, itemsite supply
293 WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
294 AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
295 AND (sold.itemsite_id=_r.quitem_itemsite_id) );
299 (charass_target_type, charass_target_id, charass_char_id, charass_value)
300 SELECT 'W', _orderId, charass_char_id, charass_value
302 WHERE ((charass_target_type='QI')
303 AND (charass_target_id=_r.quitem_id));
305 ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
306 SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
307 _r.quitem_scheddate, '', 'S', _iitemid ) INTO _orderId;
309 UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
310 ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
311 IF (_r.quitem_prcost=0) THEN
312 -- For now quote to invoice/dropship will not be supported but with the creation of a createPurchaseToQuote() version of createPurchaseToSale()
314 -- SELECT createPurchaseToSale(_iitemid, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
315 RAISE EXCEPTION 'Quote contains one or more dropship items that may not be converted from a Quote to an Invoice';
317 -- For now quote to invoice/dropship will not be supported but with the creation of a createPurchaseToQuote() version of createPurchaseToSale()
319 -- SELECT createPurchaseToSale(_iitemid, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
320 RAISE EXCEPTION 'Quote contains one or more dropship items that may not be converted from a Quote to an Invoice';
325 -- UPDATE invcitem SET invcitem_order_type=_ordertype, invcitem_order_id=_orderid
326 -- WHERE (invcitem_id=_iitemid);
332 SELECT metric_value INTO _showConvertedQuote
333 FROM metric WHERE metric_name = 'ShowQuotesAfterSO';
335 IF (_showConvertedQuote) THEN
337 SET quhead_status= 'C'
338 WHERE (quhead_id = pQuheadid);
340 PERFORM deleteQuote(pQuheadid);
347 LANGUAGE plpgsql VOLATILE