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;
10 _ordertype CHARACTER(1);
12 _usespos BOOLEAN := false;
13 _blanketpos BOOLEAN := true;
14 _showConvertedQuote BOOLEAN := false;
21 -- Check to make sure the quote has not expired
22 IF (SELECT COALESCE(quhead_expire, endOfTime()) < CURRENT_DATE
24 WHERE(quhead_id=pQuheadid)) THEN
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));
34 PERFORM postEvent('CannotConvertQuote', 'Q', quhead_id,
35 quhead_warehous_id, quhead_number,
36 NULL, NULL, NULL, NULL)
38 WHERE (quhead_id=pQuheadid);
43 -- Get Credit Stat, Uses POs and Blanket POs
45 SELECT cust_creditstatus, cust_usespos, cust_blanketpos
46 INTO _creditstatus, _usespos, _blanketpos
48 WHERE ((quhead_cust_id=cust_id)
49 AND (quhead_id=pQuheadid));
51 -- Check to see if customer or prospect
54 SELECT prospect_id INTO _prospectid
56 WHERE ((quhead_cust_id=prospect_id)
57 AND (quhead_id=pQuheadid));
63 ELSIF (_creditstatus = 'H' AND NOT hasPriv('CreateSOForHoldCustomer')) THEN
65 ELSIF (_creditstatus = 'W' AND NOT hasPriv('CreateSOForWarnCustomer')) THEN
69 -- PO/blanket PO checks
71 IF ( (_usespos) AND (NOT _blanketpos) ) THEN
73 FROM quhead JOIN invchead ON ( (invchead_cust_id=quhead_cust_id) AND
74 (UPPER(invchead_custponumber)=UPPER(quhead_custponumber)) )
75 WHERE (quhead_id=pQuheadid);
77 RAISE EXCEPTION 'Duplicate Customer PO';
81 --Check to see if an invoice exists with the quote number
83 PERFORM quhead_number, invchead_id
85 WHERE quhead_id = pQuheadid
86 AND invchead_invcnumber = quhead_number;
88 -- If it does then get a new Invoice number otherwise use the quote number as the invoice number
91 SELECT fetchinvcnumber() INTO _inNum;
93 SELECT quhead_number INTO _inNum
95 WHERE quhead_id = pQuheadid;
98 --Insert quote info into invoice tables
100 SELECT NEXTVAL('invchead_invchead_id_seq') INTO _iheadid;
102 ( invchead_ordernumber, invchead_shipdate, invchead_recurring,
103 invchead_id, invchead_invcnumber, invchead_cust_id,
104 invchead_orderdate, invchead_ponumber,
105 invchead_billto_name, invchead_billto_address1,
106 invchead_billto_address2, invchead_billto_address3,
107 invchead_billto_city, invchead_billto_state, invchead_billto_zipcode, invchead_billto_country,
108 invchead_shipto_id, invchead_shipto_name, invchead_shipto_address1,
109 invchead_shipto_address2, invchead_shipto_address3,
110 invchead_shipto_city, invchead_shipto_state, invchead_shipto_zipcode, invchead_shipto_country,
111 invchead_salesrep_id, invchead_commission,
112 invchead_terms_id, invchead_shipchrg_id, invchead_fob, invchead_shipvia,
113 invchead_notes, invchead_freight,
114 invchead_misc_amount, invchead_misc_accnt_id, invchead_misc_descrip,
115 invchead_prj_id, invchead_curr_id, invchead_taxzone_id,
116 invchead_posted, invchead_printed, invchead_invcdate,
117 invchead_saletype_id, invchead_shipzone_id
118 --invchead_taxtype_id,
119 --invchead_shipto_cntct_id, invchead_shipto_cntct_honorific, invchead_shipto_cntct_first_name,
120 --invchead_shipto_cntct_middle, invchead_shipto_cntct_last_name, invchead_shipto_cntct_suffix,
121 --invchead_shipto_cntct_phone, invchead_shipto_cntct_title, invchead_shipto_cntct_fax,
122 --invchead_shipto_cntct_email,
123 --invchead_billto_cntct_id, invchead_billto_cntct_honorific,
124 --invchead_billto_cntct_first_name, invchead_billto_cntct_middle, invchead_billto_cntct_last_name,
125 --invchead_billto_cntct_suffix, invchead_billto_cntct_phone, invchead_billto_cntct_title,
126 --invchead_billto_cntct_fax, invchead_billto_cntct_email,
127 --invchead_ophead_id,
128 --invchead_calcfreight
130 SELECT quhead_number, quhead_packdate, 'f',
131 _iheadid, _inNum, quhead_cust_id,
132 CURRENT_DATE, quhead_custponumber,
133 quhead_billtoname, quhead_billtoaddress1,
134 quhead_billtoaddress2, quhead_billtoaddress3,
135 quhead_billtocity, quhead_billtostate, quhead_billtozip, quhead_billtocountry,
136 quhead_shipto_id, quhead_shiptoname, quhead_shiptoaddress1,
137 quhead_shiptoaddress2, quhead_shiptoaddress3,
138 quhead_shiptocity, quhead_shiptostate, quhead_shiptozipcode, quhead_shiptocountry,
139 quhead_salesrep_id, quhead_commission,
140 quhead_terms_id, cust_shipchrg_id, quhead_fob, quhead_shipvia,
141 quhead_ordercomments, quhead_freight,
142 quhead_misc, quhead_misc_accnt_id, quhead_misc_descrip,
143 quhead_prj_id, quhead_curr_id, quhead_taxzone_id,
144 'f','f',current_date,
145 quhead_saletype_id, quhead_shipzone_id
146 --quhead_shipto_cntct_id, quhead_shipto_cntct_honorific,
147 --quhead_shipto_cntct_first_name, quhead_shipto_cntct_middle, quhead_shipto_cntct_last_name,
148 --quhead_shipto_cntct_suffix, quhead_shipto_cntct_phone, quhead_shipto_cntct_title,
149 --quhead_shipto_cntct_fax, quhead_shipto_cntct_email, quhead_billto_cntct_id,
150 --quhead_billto_cntct_honorific, quhead_billto_cntct_first_name, quhead_billto_cntct_middle,
151 --quhead_billto_cntct_last_name, quhead_billto_cntct_suffix, quhead_billto_cntct_phone,
152 --quhead_billto_cntct_title, quhead_billto_cntct_fax, quhead_billto_cntct_email, quhead_ophead_id,
154 FROM quhead JOIN custinfo ON (cust_id=quhead_cust_id)
155 WHERE (quhead_id=pQuheadid);
157 -- Attachments on Invoice not supported but leaving this in for future use:
159 UPDATE url SET url_source_id = _iheadid,
161 WHERE ((url_source='Q') AND (url_source_id = pQuheadid));
163 UPDATE imageass SET imageass_source_id = _iheadid,
164 imageass_source = 'I'
165 WHERE ((imageass_source='Q') AND (imageass_source_id = pQuheadid));
167 UPDATE docass SET docass_source_id = _iheadid,
168 docass_source_type = 'I'
169 WHERE ((docass_source_type='Q') AND (docass_source_id = pQuheadid));
173 -- Comments not supported on Invoice but leaving this in for future use:
177 ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text, comment_public )
178 SELECT comment_cmnttype_id, 'I', _iheadid, comment_date, comment_user, ('Quote-' || comment_text), comment_public
180 WHERE ( (comment_source='Q')
181 AND (comment_source_id=pQuheadid) );
184 FOR _r IN SELECT quitem.*,
185 quhead_number, quhead_prj_id,
186 itemsite_item_id, itemsite_leadtime,
187 itemsite_createsopo, itemsite_createsopr,
188 item_type, COALESCE(quitem_itemsrc_id, itemsrc_id, -1) AS itemsrcid
189 FROM quhead JOIN quitem ON (quitem_quhead_id=quhead_id)
190 JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
191 JOIN item ON (item_id=itemsite_item_id)
192 LEFT OUTER JOIN itemsrc ON ( (itemsrc_item_id=item_id) AND
194 WHERE (quhead_id=pQuheadid) LOOP
196 SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _iitemid;
199 ( invcitem_id, invcitem_invchead_id, invcitem_linenumber,
201 invcitem_warehous_id,
203 --invcitem_scheddate, invcitem_promdate,
204 invcitem_price, invcitem_custprice,
205 invcitem_ordered, invcitem_billed,
206 invcitem_qty_uom_id, invcitem_qty_invuomratio,
207 invcitem_price_uom_id, invcitem_price_invuomratio,
208 invcitem_custpn, invcitem_notes, invcitem_taxtype_id )
210 ( _iitemid, _iheadid, _r.quitem_linenumber,
211 (SELECT itemsite_item_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
212 (SELECT itemsite_warehous_id FROM itemsite WHERE itemsite_id = _r.quitem_itemsite_id),
214 --_r.quitem_scheddate, _r.quitem_promdate,
215 _r.quitem_price, _r.quitem_custprice,
216 _r.quitem_qtyord, _r.quitem_qtyord,
217 _r.quitem_qty_uom_id, _r.quitem_qty_invuomratio,
218 _r.quitem_price_uom_id, _r.quitem_price_invuomratio,
219 _r.quitem_custpn, _r.quitem_memo, _r.quitem_taxtype_id );
221 IF (fetchMetricBool('enablextcommissionission')) THEN
222 PERFORM xtcommission.getSalesReps(quhead_cust_id, quhead_shipto_id,
223 _r.itemsite_item_id, _r.quitem_price,
224 _iitemid, 'InvoiceItem')
226 WHERE (quhead_id=pQuheadid);
229 -- Chracteristics not supported on Invoice but leaving in for future use:
233 (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_default, charass_price)
234 SELECT 'SI', _iitemid, charass_char_id, charass_value, charass_default, charass_price
236 WHERE ((charass_target_type='QI')
237 AND (charass_target_id=_r.quitem_id));
241 -- Comments not supported but leaving in for future use
245 ( comment_cmnttype_id, comment_source, comment_source_id, comment_date, comment_user, comment_text )
246 SELECT comment_cmnttype_id, 'SI', _iitemid, comment_date, comment_user, ('Quote-' || comment_text)
248 WHERE ( (comment_source='QI')
249 AND (comment_source_id=_r.quitem_id) );
254 IF (_r.quitem_createorder) THEN
256 IF (_r.item_type IN ('M')) THEN
257 SELECT createWo( CAST(_r.quhead_number AS INTEGER), supply.itemsite_id, 1, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
258 _r.itemsite_leadtime, _r.quitem_scheddate, _r.quitem_memo, 'Q', _iitemid, _r.quhead_prj_id ) INTO _orderId
259 FROM itemsite sold, itemsite supply
260 WHERE ((sold.itemsite_item_id=supply.itemsite_item_id)
261 AND (supply.itemsite_warehous_id=_r.quitem_order_warehous_id)
262 AND (sold.itemsite_id=_r.quitem_itemsite_id) );
266 (charass_target_type, charass_target_id, charass_char_id, charass_value)
267 SELECT 'W', _orderId, charass_char_id, charass_value
269 WHERE ((charass_target_type='QI')
270 AND (charass_target_id=_r.quitem_id));
272 ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopr) ) THEN
273 SELECT createPr( CAST(_r.quhead_number AS INTEGER), _r.quitem_itemsite_id, (_r.quitem_qtyord * _r.quitem_qty_invuomratio),
274 _r.quitem_scheddate, '', 'S', _iitemid ) INTO _orderId;
276 UPDATE pr SET pr_prj_id=_r.quhead_prj_id WHERE pr_id=_orderId;
277 ELSIF ( (_r.item_type IN ('P', 'O')) AND (_r.itemsite_createsopo) ) THEN
278 IF (_r.quitem_prcost=0) THEN
279 -- For now quote to invoice/dropship will not be supported but with the creation of a createPurchaseToQuote() version of createPurchaseToSale()
281 -- SELECT createPurchaseToSale(_iitemid, _r.itemsrcid, _r.quitem_dropship) INTO _orderId;
282 RAISE EXCEPTION 'Quote contains one or more dropship items that may not be converted from a Quote to an Invoice';
284 -- For now quote to invoice/dropship will not be supported but with the creation of a createPurchaseToQuote() version of createPurchaseToSale()
286 -- SELECT createPurchaseToSale(_iitemid, _r.itemsrcid, _r.quitem_dropship, _r.quitem_prcost) INTO _orderId;
287 RAISE EXCEPTION 'Quote contains one or more dropship items that may not be converted from a Quote to an Invoice';
292 -- UPDATE invcitem SET invcitem_order_type=_ordertype, invcitem_order_id=_orderid
293 -- WHERE (invcitem_id=_iitemid);
299 SELECT metric_value INTO _showConvertedQuote
300 FROM metric WHERE metric_name = 'ShowQuotesAfterSO';
302 IF (_showConvertedQuote) THEN
304 SET quhead_status= 'C'
305 WHERE (quhead_id = pQuheadid);
307 PERFORM deleteQuote(pQuheadid);
314 LANGUAGE plpgsql VOLATILE