3 ALTER TABLE pohead ADD COLUMN pohead_has_error INTEGER DEFAULT 0;
6 CREATE OR REPLACE FUNCTION x_fix_pohead_flag_errors(i_pohead_id INT)
13 v_pohead_match := '%';
15 IF i_pohead_id > 0 THEN
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;
21 UPDATE pohead set pohead_has_error = 0;
25 costcat_liability_accnt_id
32 UPDATE pohead set pohead_has_error = 1 where pohead_number IN (
33 SELECT distinct(pohead_number) FROM
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
42 gltrans_source = 'S/R'
44 gltrans_accnt_id = v_lb_accnt_id
46 gltrans_doctype = 'PO' and gltrans_posted
50 gltrans_docnumber like v_pohead_match
52 WHERE invdetail_id = 0
56 UPDATE pohead set pohead_has_error = 2 where pohead_number IN (
58 distinct(pohead_number) as pohead_number
62 CONCAT(pohead_number, '-', poitem_linenumber) as ordnumber,
65 max(recv_id) as recv_id_max,
66 SUM(recv_qty) as total_recv_qty,
72 invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
76 invhist_transtype = 'RP'
78 invhist_ordtype = 'PO'
80 invhist_itemsite_id = recv_itemsite_id
89 recvgrp_id = recv_recvgrp_id
93 poitem_id = recv_orderitem_id
97 pohead_id = poitem_pohead_id
101 recv_recvgrp_id IS NOT NULL
103 pohead_number like v_pohead_match
105 pohead_number, poitem_linenumber, recv_itemsite_id, pohead_id
107 tx_qty != total_recv_qty
109 AND pohead_number like v_pohead_match
113 UPDATE pohead set pohead_has_error = 3 where pohead_number IN (
115 distinct(pohead_number) as pohead_number
120 CONCAT(pohead_number, '-', poitem_linenumber) as ordnumber,
122 SUM(poitem_qty_received - poitem_qty_returned) as po_qty,
128 invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
132 invhist_transtype = 'RP'
134 invhist_ordtype = 'PO'
136 invhist_itemsite_id = poitem_itemsite_id
145 pohead_id = poitem_pohead_id
147 pohead_number like v_pohead_match
149 pohead_number, poitem_linenumber, pohead_id,poitem_itemsite_id
152 AND pohead_number like v_pohead_match
153 GROUP by pohead_id,pohead_number
162 LANGUAGE plpgsql VOLATILE
164 ALTER FUNCTION x_fix_pohead_flag_errors(int)