git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-fix-pohead-add-variance-all.sql

   1
   2
   3 -- void all the exist variance and add one for whole order-----------------
   4
   5 CREATE OR REPLACE FUNCTION x_fix_pohead_add_variance_all(i_pohead_number text)
   6     RETURNS  INTEGER
   7 AS $BODY$
   8 DECLARE
   9     _p RECORD;
  10     v_date DATE;
  11     v_purchprice_accnt_id INTEGER;
  12     v_lb_accnt_id INTEGER;
  13     v_result INTEGER;
  14     v_base NUMERIC;
  15     v_diff NUMERIC;
  16     v_value NUMERIC;
  17     v_gltrans_amount NUMERIC;
  18
  19 BEGIN
  20
  21     v_result := 0;
  22
  23     IF (NOT fetchMetricBool('RecordPPVonReceipt')) THEN -- If the 'Purchase Price Variance on Receipt' option is true
  24         RAISE EXCEPTION 'Purchase Price Variance on Receipt option is FALSE';
  25     END IF;
  26
  27     SELECT
  28             costcat_purchprice_accnt_id,
  29             costcat_liability_accnt_id
  30     INTO
  31             v_purchprice_accnt_id,
  32             v_lb_accnt_id
  33     FROM
  34             costcat
  35     LIMIT 1;
  36
  37     -- what's the poitem total value.
  38     -- this should be at the recevicved date really..
  39
  40     SELECT
  41             SUM(currToBase(
  42                     pohead_curr_id,
  43                     poitem_unitprice,
  44
  45                     -- date is when it was recived...
  46
  47                     (SELECT max(recv_gldistdate) FROM
  48                         recv
  49                         LEFT JOIN
  50                         recvgrp
  51                         ON
  52                         recvgrp_id = recv_recvgrp_id
  53                         WHERE
  54                         recv_orderitem_id = poitem_id
  55                         AND
  56                         recvgrp_void != 1
  57
  58                     )
  59
  60                     ) * (poitem_qty_received - poitem_qty_returned))
  61     INTO
  62             v_base
  63     FROM
  64             poitem
  65     LEFT JOIN
  66             pohead
  67     ON
  68             pohead_id = poitem_pohead_id
  69
  70     WHERE pohead_number = i_pohead_number;
  71
  72     -- what's the posted value?
  73     SELECT
  74         SUM(gltrans_amount)
  75         INTO
  76         v_diff
  77         FROM
  78         gltrans
  79         WHERE
  80         gltrans_misc_id IN (
  81             SELECT
  82                     invhist_id
  83
  84             FROM
  85                     invdetailview
  86             WHERE
  87                     (
  88                         invhist_ordnumber LIKE i_pohead_number || '-%'
  89                         OR
  90                         invhist_ordnumber = i_pohead_number
  91                     )
  92                 AND
  93                     invfifo_void = 0
  94         ) AND
  95         gltrans_accnt_id = v_lb_accnt_id;
  96
  97
  98
  99     SELECT
 100             *
 101     INTO
 102             _p
 103     FROM
 104             pohead
 105     WHERE
 106             pohead_number = i_pohead_number;
 107
 108
 109     SELECT
 110             MAX(recv_date)::date
 111     INTO
 112             v_date
 113     FROM
 114             recv
 115     LEFT JOIN
 116             recvgrp
 117     ON
 118             recvgrp_id = recv_recvgrp_id
 119     WHERE
 120             (recvgrp_void = 0 OR recvgrp_void IS NULL)
 121         AND
 122             recvgrp_pohead_id = _p.pohead_id;
 123
 124
 125     -- void any existing purchase price variences..
 126
 127     PERFORM deleteGlSeries( gltrans_sequence,  'Fix Purchase Price Variance on ' || to_char(NOW(), 'Day Mon DD YYY') )
 128
 129         FROM
 130                 gltrans
 131         WHERE
 132                 gltrans_accnt_id = v_purchprice_accnt_id
 133             AND
 134                 gltrans_doctype = 'PO'
 135             AND
 136                 gltrans_notes LIKE 'Purchase price variance adjusted for P/O ' || _p.pohead_number || '%'
 137                 OR
 138                 gltrans_notes LIKE 'Void Purchase price variance adjusted for P/O ' || _p.pohead_number || '%'
 139             AND
 140                 NOT gltrans_deleted;
 141
 142
 143     v_value = v_base - v_diff;
 144
 145
 146     IF (ABS(v_value) <> 0) THEN
 147         SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
 148                                        'S/R', 'PO', _p.pohead_number,
 149                                       'Purchase price variance adjusted for P/O ' || _p.pohead_number || ' on ' || v_date,
 150                                       v_lb_accnt_id,
 151                                       v_purchprice_accnt_id, -1,
 152                                       v_value,
 153                                       v_date::DATE, true ) INTO v_result;
 154     END IF;
 155
 156
 157     RETURN v_result;
 158
 159 END
 160 $BODY$
 161   LANGUAGE plpgsql VOLATILE
 162   COST 100;
 163
 164 ALTER FUNCTION  x_fix_pohead_add_variance_all(text)
 165   OWNER TO admin;
 166