git.roojs.org Git - Pman.Xtuple/blob - pgsql/x-fix-pohead-flag-errors.sql

   1
   2
   3 ALTER TABLE pohead ADD COLUMN pohead_has_error INTEGER DEFAULT 0;
   4
   5
   6 CREATE OR REPLACE FUNCTION x_fix_pohead_flag_errors(i_pohead_id INT)
   7   RETURNS  int  AS
   8 $BODY$
   9 DECLARE
  10     v_lb_accnt_id INT;
  11     v_pohead_match TEXT;
  12 BEGIN
  13     v_pohead_match := '%';
  14
  15     IF i_pohead_id > 0 THEN
  16
  17         UPDATE pohead set pohead_has_error = 0 where pohead_id = i_pohead_id;
  18         SELECT pohead_number || '-%' INTO v_pohead_match FROM pohead  where pohead_id = i_pohead_id;
  19     ELSE
  20
  21         UPDATE pohead set pohead_has_error = 0;
  22     END IF;
  23
  24      SELECT
  25              costcat_liability_accnt_id
  26     INTO
  27              v_lb_accnt_id
  28     FROM
  29             costcat
  30     LIMIT 1;
  31
  32     UPDATE pohead set pohead_has_error = 1 where pohead_number IN (
  33         SELECT distinct(pohead_number) FROM
  34         (
  35         SELECT
  36             *,
  37             split_part(gltrans_docnumber,'-',1) as pohead_number,
  38             COALESCE((SELECT invdetail_id FROM invdetailview where invhist_id = gltrans_misc_id),0) as invdetail_id
  39             FROM
  40             gltrans
  41             WHERE
  42             gltrans_source = 'S/R'
  43             AND
  44             gltrans_accnt_id = v_lb_accnt_id
  45             AND
  46             gltrans_doctype = 'PO' and gltrans_posted
  47             AND
  48             gltrans_misc_id > 0
  49             AND
  50             gltrans_docnumber like v_pohead_match
  51         ) x
  52         WHERE invdetail_id = 0
  53
  54     );
  55
  56     UPDATE pohead set pohead_has_error = 2 where pohead_number IN (
  57         select
  58                 distinct(pohead_number) as pohead_number
  59             FROM
  60             (
  61                 select
  62                     CONCAT(pohead_number, '-', poitem_linenumber) as ordnumber,
  63                     pohead_id,
  64                     recv_itemsite_id,
  65                     max(recv_id) as recv_id_max,
  66                     SUM(recv_qty) as total_recv_qty,
  67                     COALESCE((SELECT
  68                         sum(invdetail_qty)
  69                         FROM
  70                             invdetailview
  71                         WHERE
  72                             invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
  73                         AND
  74                             invhist_posted
  75                         AND
  76                             invhist_transtype = 'RP'
  77                         AND
  78                             invhist_ordtype = 'PO'
  79                         AND
  80                             invhist_itemsite_id = recv_itemsite_id
  81                         AND
  82                             invdetail_qty > 0
  83                     ),0) as tx_qty
  84                     from
  85                         recv
  86                     LEFT JOIN
  87                         recvgrp
  88                     on
  89                         recvgrp_id = recv_recvgrp_id
  90                     LEFT JOIN
  91                         poitem
  92                     ON
  93                         poitem_id = recv_orderitem_id
  94                     LEFT JOIN
  95                         pohead
  96                     ON
  97                         pohead_id = poitem_pohead_id
  98                     where
  99                         recv_posted
 100                         AND
 101                         recv_recvgrp_id IS NOT NULL
 102                         AND
 103                         pohead_number like v_pohead_match
 104                     GROUP BY
 105                         pohead_number, poitem_linenumber, recv_itemsite_id, pohead_id
 106             ) x where
 107                 tx_qty != total_recv_qty
 108                 AND tx_qty = 0.0
 109                 AND pohead_number like v_pohead_match
 110                 GROUP by pohead_id
 111         );
 112
 113      UPDATE pohead set pohead_has_error = 3 where pohead_number IN (
 114         select
 115                 distinct(pohead_number) as pohead_number
 116             FROM
 117             (
 118                 select
 119                     pohead_number,
 120                     CONCAT(pohead_number, '-', poitem_linenumber) as ordnumber,
 121                     pohead_id,
 122                     SUM(poitem_qty_received - poitem_qty_returned) as po_qty,
 123                     COALESCE((SELECT
 124                         sum(invdetail_qty)
 125                         FROM
 126                             invdetailview
 127                         WHERE
 128                             invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
 129                         AND
 130                             invhist_posted
 131                         AND
 132                             invhist_transtype = 'RP'
 133                         AND
 134                             invhist_ordtype = 'PO'
 135                         AND
 136                             invhist_itemsite_id = poitem_itemsite_id
 137
 138                     ),0) as tx_qty
 139                     from
 140
 141                         poitem
 142                     LEFT JOIN
 143                         pohead
 144                     ON
 145                         pohead_id = poitem_pohead_id
 146                     WHERE
 147                         pohead_number like v_pohead_match
 148                     GROUP BY
 149                         pohead_number, poitem_linenumber,  pohead_id,poitem_itemsite_id
 150             ) x where
 151                 po_qty != tx_qty
 152                   AND pohead_number like v_pohead_match
 153                 GROUP by pohead_id,pohead_number
 154         );
 155
 156
 157
 158
 159         RETURN 1;
 160    END;
 161 $BODY$
 162   LANGUAGE plpgsql VOLATILE
 163   COST 100;
 164 ALTER FUNCTION  x_fix_pohead_flag_errors(int)
 165   OWNER TO admin;
 166
 167