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
74 SELECT quhead_number INTO _qunumber
76 WHERE (quhead_id=pQuheadid)
77 AND (COALESCE(quhead_custponumber, '') = '');
79 RAISE EXCEPTION 'Customer PO required for Quote %', _qunumber;
83 IF ( (_usespos) AND (NOT _blanketpos) ) THEN
84 SELECT quhead_number, quhead_custponumber INTO _qunumber, _ponumber
85 FROM quhead JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
86 (UPPER(invchead_ponumber)=UPPER(quhead_custponumber)) )
87 WHERE (quhead_id=pQuheadid);
89 RAISE EXCEPTION 'Duplicate Customer PO % for Quote %', _ponumber, _qunumber;
93 --Check to see if an invoice exists with the quote number
95 PERFORM quhead_number, invchead_id
97 WHERE quhead_id = pQuheadid
98 AND invchead_invcnumber = quhead_number;
100 -- If it does then get a new Invoice number otherwise use the quote number as the invoice number
103 SELECT fetchinvcnumber() INTO _inNum;
105 SELECT quhead_number INTO _inNum
107 WHERE quhead_id = pQuheadid;
110 --Insert quote info into invoice tables
112 SELECT NEXTVAL('invchead_invchead_id_seq') INTO _iheadid;
114 ( invchead_ordernumber, invchead_shipdate, invchead_recurring,
115 invchead_id, invchead_invcnumber, invchead_cust_id,
116 invchead_orderdate, invchead_ponumber,
117 invchead_billto_name, invchead_billto_address1,
118 invchead_billto_address2, invchead_billto_address3,
119 invchead_billto_city, invchead_billto_state, invchead_billto_zipcode, invchead_billto_country,
120 invchead_shipto_id, invchead_shipto_name, invchead_shipto_address1,
121 invchead_shipto_address2, invchead_shipto_address3,
122 invchead_shipto_city, invchead_shipto_state, invchead_shipto_zipcode, invchead_shipto_country,
123 invchead_salesrep_id, invchead_commission,
124 invchead_terms_id, invchead_shipchrg_id, invchead_fob, invchead_shipvia,
125 invchead_notes, invchead_freight,
126 invchead_misc_amount, invchead_misc_accnt_id, invchead_misc_descrip,
127 invchead_prj_id, invchead_curr_id, invchead_taxzone_id,
128 invchead_posted, invchead_printed, invchead_invcdate,
129 invchead_saletype_id, invchead_shipzone_id
130 --invchead_taxtype_id,
131 --invchead_shipto_cntct_id, invchead_shipto_cntct_honorific, invchead_shipto_cntct_first_name,
132 --invchead_shipto_cntct_middle, invchead_shipto_cntct_last_name, invchead_shipto_cntct_suffix,
133 --invchead_shipto_cntct_phone, invchead_shipto_cntct_title, invchead_shipto_cntct_fax,
134 --invchead_shipto_cntct_email,
135 --invchead_billto_cntct_id, invchead_billto_cntct_honorific,
136 --invchead_billto_cntct_first_name, invchead_billto_cntct_middle, invchead_billto_cntct_last_name,
137 --invchead_billto_cntct_suffix, invchead_billto_cntct_phone, invchead_billto_cntct_title,
138 --invchead_billto_cntct_fax, invchead_billto_cntct_email,
139 --invchead_ophead_id,
140 --invchead_calcfreight
142 SELECT quhead_number, quhead_packdate, 'f',
143 _iheadid, _inNum, quhead_cust_id,
144 CURRENT_DATE, quhead_custponumber,
145 quhead_billtoname, quhead_billtoaddress1,
146 quhead_billtoaddress2, quhead_billtoaddress3,
147 quhead_billtocity, quhead_billtostate, quhead_billtozip, quhead_billtocountry,
148 quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1,
149 quhead_shiptoaddress2, quhead_shiptoaddress3,
150 quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode, quhead_shiptocountry,
151 quhead_salesrep_id, quhead_commission,
152 quhead_terms_id, cust_shipchrg_id, quhead_fob, quhead_shipvia,
153 quhead_ordercomments, quhead_freight,
154 quhead_misc, quhead_misc_accnt_id, quhead_misc_descrip,
155 quhead_prj_id, quhead_curr_id, quhead_taxzone_id,
156 'f','f',current_date,
157 quhead_saletype_id, quhead_shipzone_id
158 --quhead_shipto_cntct_id, quhead_shipto_cntct_honorific,
159 --quhead_shipto_cntct_first_name, quhead_shipto_cntct_middle, quhead_shipto_cntct_last_name,
160 --quhead_shipto_cntct_suffix, quhead_shipto_cntct_phone, quhead_shipto_cntct_title,
161 --quhead_shipto_cntct_fax, quhead_shipto_cntct_email, quhead_billto_cntct_id,
162 --quhead_billto_cntct_honorific, quhead_billto_cntct_first_name, quhead_billto_cntct_middle,
163 --quhead_billto_cntct_last_name, quhead_billto_cntct_suffix, quhead_billto_cntct_phone,
164 --quhead_billto_cntct_title, quhead_billto_cntct_fax, quhead_billto_cntct_email, quhead_ophead_id,
166 FROM quhead JOIN custinfo ON (cust_id=quhead_cust_id)
167 WHERE (quhead_id=pQuheadid);
169 -- Attachments on Invoice not supported but leaving this in for future use:
171 UPDATE url SET url_source_id = _iheadid,
173 WHERE ((url_source='Q') AND (url_source_id = pQuheadid));
175 UPDATE imageass SET imageass_source_id = _iheadid,
176 imageass_source = 'I'
177 WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));
179 UPDATE docass SET docass_source_id = _iheadid,
180 docass_source_type = 'I'
181 WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));
185 -- Comments not supported on Invoice but leaving this in for future use:
189 ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
190 SELECT comment_cmnttype_id, 'I', _iheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
192 WHERE ( (comment_source='Q')
193 AND (comment_source_id=pQuheadid) );
196 FOR _r IN SELECT quitem.*,
197 quhead_number, quhead_prj_id, quhead_saletype_id,
198 itemsite_item_id, itemsite_leadtime,
199 itemsite_createsopo, itemsite_createsopr,
200 item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
201 FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
202 JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
203 JOIN item ON (item_id=itemsite_item_id)
204 LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
206 WHERE (quhead_id=pQuheadid) LOOP
208 SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _iitemid;
211 ( invcitem_id, invcitem_invchead_id, invcitem_linenumber,
213 invcitem_warehous_id,
215 --invcitem_scheddate, invcitem_promdate,
216 invcitem_price, invcitem_custprice,
217 invcitem_ordered, invcitem_billed,
218 invcitem_qty_uom_id, invcitem_qty_invuomratio,
219 invcitem_price_uom_id, invcitem_price_invuomratio,
220 invcitem_custpn, invcitem_notes, invcitem_taxtype_id )
222 ( _iitemid, _iheadid, _r.quitem_linenumber,
223 (SELECT itemsite_item_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
224 (SELECT itemsite_warehous_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
226 --_r.quitem_scheddate, _r.quitem_promdate,
227 _r.quitem_price, _r.quitem_custprice,
228 _r.quitem_qtyord, _r.quitem_qtyord,
229 _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
230 _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
231 _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id );
233 IF (fetchMetricBool('enablextcommissionission')) THEN
234 PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
235 _r.itemsite_item_id, _r.quhead_saletype_id,
236 _r.quitem_price, _r.quitem_custprice,
237 _iitemid, 'InvoiceItem')
239 WHERE (quhead_id=pQuheadid);
242 -- Chracteristics not supported on Invoice but leaving in for future use:
246 (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
247 SELECT 'SI', _iitemid, charass_char_id, charass_value, charass_default, charass_price
249 WHERE ((charass_target_type='QI')
250 AND (charass_target_id=_r.quitem_id));
254 -- Comments not supported but leaving in for future use
258 ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
259 SELECT comment_cmnttype_id, 'SI', _iitemid, comment_date, comment_user, ('Quote-' || comment_text)
261 WHERE ( (comment_source='QI')
262 AND (comment_source_id=_r.quitem_id) );
267 IF (_r.quitem_createorder) THEN
269 IF (_r.item_type IN ('M')) THEN
270 SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
271 _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'Q', _iitemid, _r.quhead_prj_id ) INTO _orderId
272 FROM itemsite sold, itemsite supply
273 WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
274 AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
275 AND (sold.itemsite_id=_r.quitem_itemsite_id) );
279 (charass_target_type, charass_target_id, charass_char_id, charass_value)
280 SELECT 'W', _orderId, charass_char_id, charass_value
282 WHERE ((charass_target_type='QI')
283 AND (charass_target_id=_r.quitem_id));
285 ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
286 SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
287 _r.quitem_scheddate, '', 'S', _iitemid ) INTO _orderId;
289 UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
290 ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
291 IF (_r.quitem_prcost=0) THEN
292 -- For now quote to invoice/dropship will not be supported but with the creation of a createPurchaseToQuote() version of createPurchaseToSale()
294 -- SELECT createPurchaseToSale(_iitemid, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
295 RAISE EXCEPTION 'Quote contains one or more dropship items that may not be converted from a Quote to an Invoice';
297 -- For now quote to invoice/dropship will not be supported but with the creation of a createPurchaseToQuote() version of createPurchaseToSale()
299 -- SELECT createPurchaseToSale(_iitemid, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
300 RAISE EXCEPTION 'Quote contains one or more dropship items that may not be converted from a Quote to an Invoice';
305 -- UPDATE invcitem SET invcitem_order_type=_ordertype, invcitem_order_id=_orderid
306 -- WHERE (invcitem_id=_iitemid);
312 SELECT metric_value INTO _showConvertedQuote
313 FROM metric WHERE metric_name = 'ShowQuotesAfterSO';
315 IF (_showConvertedQuote) THEN
317 SET quhead_status= 'C'
318 WHERE (quhead_id = pQuheadid);
320 PERFORM deleteQuote(pQuheadid);
327 LANGUAGE plpgsql VOLATILE