git.roojs.org Git - xtuple/blob - foundation-database/public/functions/assessfinancecharge.sql

   1 CREATE OR REPLACE FUNCTION assessFinanceCharge(pAropenid INTEGER,
   2                                                pAssessDate DATE,
   3                                                pAssessAmount NUMERIC) RETURNS INTEGER AS $$
   4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   5 -- See www.xtuple.com/CPAL for the full text of the software license.
   6 DECLARE
   7   _fc           RECORD;
   8   _ar           RECORD;
   9   _invcheadid   INTEGER;
  10
  11 BEGIN
  12
  13   -- cache some information
  14   SELECT * INTO _fc FROM fincharg;
  15   IF (NOT FOUND) THEN
  16     RAISE EXCEPTION 'assessFinanceCharge, configuration not set.';
  17   END IF;
  18
  19   SELECT * INTO _ar FROM aropen WHERE (aropen_id=pAropenid);
  20   IF (NOT FOUND) THEN
  21     RAISE EXCEPTION 'assessFinanceCharge, aropen not found.';
  22   END IF;
  23
  24   -- create invoice head
  25   INSERT INTO invchead
  26     ( invchead_cust_id,
  27       invchead_shipto_id,
  28       invchead_ordernumber,
  29       invchead_orderdate,
  30       invchead_posted,
  31       invchead_printed,
  32       invchead_invcnumber,
  33       invchead_invcdate,
  34       invchead_shipdate,
  35       invchead_ponumber,
  36       invchead_shipvia,
  37       invchead_fob,
  38       invchead_billto_name,
  39       invchead_billto_address1,
  40       invchead_billto_address2,
  41       invchead_billto_address3,
  42       invchead_billto_city,
  43       invchead_billto_state,
  44       invchead_billto_zipcode,
  45       invchead_billto_phone,
  46       invchead_shipto_name,
  47       invchead_shipto_address1,
  48       invchead_shipto_address2,
  49       invchead_shipto_address3,
  50       invchead_shipto_city,
  51       invchead_shipto_state,
  52       invchead_shipto_zipcode,
  53       invchead_shipto_phone,
  54       invchead_salesrep_id,
  55       invchead_commission,
  56       invchead_terms_id,
  57       invchead_freight,
  58       invchead_misc_amount,
  59       invchead_misc_descrip,
  60       invchead_misc_accnt_id,
  61       invchead_payment,
  62       invchead_paymentref,
  63       invchead_notes,
  64       invchead_billto_country,
  65       invchead_shipto_country,
  66       invchead_prj_id,
  67       invchead_curr_id,
  68       invchead_gldistdate,
  69       invchead_recurring,
  70       invchead_recurring_interval,
  71       invchead_recurring_type,
  72       invchead_recurring_until,
  73       invchead_recurring_invchead_id,
  74       invchead_shipchrg_id,
  75       invchead_taxzone_id,
  76       invchead_void,
  77       invchead_saletype_id,
  78       invchead_shipzone_id )
  79   SELECT
  80       invchead_cust_id,
  81       invchead_shipto_id,
  82       invchead_ordernumber,
  83       invchead_orderdate,
  84       FALSE,
  85       FALSE,
  86       fetchInvcNumber(),
  87       pAssessDate,
  88       invchead_shipdate,
  89       invchead_ponumber,
  90       invchead_shipvia,
  91       invchead_fob,
  92       invchead_billto_name,
  93       invchead_billto_address1,
  94       invchead_billto_address2,
  95       invchead_billto_address3,
  96       invchead_billto_city,
  97       invchead_billto_state,
  98       invchead_billto_zipcode,
  99       invchead_billto_phone,
 100       invchead_shipto_name,
 101       invchead_shipto_address1,
 102       invchead_shipto_address2,
 103       invchead_shipto_address3,
 104       invchead_shipto_city,
 105       invchead_shipto_state,
 106       invchead_shipto_zipcode,
 107       invchead_shipto_phone,
 108       invchead_salesrep_id,
 109       0.0,
 110       invchead_terms_id,
 111       0.0,
 112       0.0,
 113       NULL,
 114       NULL,
 115       0.0,
 116       NULL,
 117       '',
 118       invchead_billto_country,
 119       invchead_shipto_country,
 120       invchead_prj_id,
 121       invchead_curr_id,
 122       NULL,
 123       FALSE,
 124       NULL,
 125       NULL,
 126       NULL,
 127       NULL,
 128       invchead_shipchrg_id,
 129       invchead_taxzone_id,
 130       invchead_void,
 131       invchead_saletype_id,
 132       invchead_shipzone_id
 133   FROM invchead
 134   WHERE (invchead_invcnumber=_ar.aropen_docnumber)
 135   RETURNING invchead_id INTO _invcheadid;
 136
 137   -- create invoice item
 138   INSERT INTO invcitem
 139     ( invcitem_invchead_id,
 140       invcitem_linenumber,
 141       invcitem_item_id,
 142       invcitem_warehous_id,
 143       invcitem_custpn,
 144       invcitem_number,
 145       invcitem_descrip,
 146       invcitem_ordered,
 147       invcitem_billed,
 148       invcitem_custprice,
 149       invcitem_price,
 150       invcitem_notes,
 151       invcitem_salescat_id,
 152       invcitem_taxtype_id,
 153       invcitem_qty_uom_id,
 154       invcitem_qty_invuomratio,
 155       invcitem_price_uom_id,
 156       invcitem_price_invuomratio,
 157       invcitem_coitem_id,
 158       invcitem_updateinv,
 159       invcitem_rev_accnt_id )
 160   VALUES
 161     ( _invcheadid,
 162       1,
 163       -1,
 164       -1,
 165       NULL,
 166       _fc.fincharg_markoninvoice,
 167       'Finance Charge Assessment',
 168       1.0,
 169       1.0,
 170       pAssessAmount,
 171       pAssessAmount,
 172       '',
 173       _fc.fincharg_salescat_id,
 174       NULL,
 175       NULL,
 176       1.0,
 177       NULL,
 178       1.0,
 179       NULL,
 180       FALSE,
 181       _fc.fincharg_accnt_id );
 182
 183   -- update aropen
 184   UPDATE aropen SET aropen_fincharg_date = pAssessDate,
 185                     aropen_fincharg_amount = COALESCE(aropen_fincharg_amount, 0.0) + pAssessAmount
 186   WHERE (aropen_id=pAropenid);
 187
 188
 189   RETURN 0;
 190
 191 END;
 192 $$ LANGUAGE 'plpgsql';