

ALTER TABLE pohead ADD COLUMN pohead_has_error INTEGER DEFAULT 0;


CREATE OR REPLACE FUNCTION x_fix_pohead_flag_errors(i_pohead_id INT)
  RETURNS  int  AS
$BODY$
DECLARE
    v_lb_accnt_id INT;
    v_pohead_match TEXT;
BEGIN  
    v_pohead_match := '%';
    
    IF i_pohead_id > 0 THEN
        
        UPDATE pohead set pohead_has_error = 0 where pohead_id = i_pohead_id;
        SELECT pohead_number || '-%' INTO v_pohead_match FROM pohead  where pohead_id = i_pohead_id;
    ELSE
        
        UPDATE pohead set pohead_has_error = 0;
    END IF;
    
     SELECT 
             costcat_liability_accnt_id
    INTO 
             v_lb_accnt_id
    FROM 
            costcat 
    LIMIT 1;
    
    UPDATE pohead set pohead_has_error = 1 where pohead_number IN (
        SELECT distinct(pohead_number) FROM
        (
        SELECT
            *,
            split_part(gltrans_docnumber,'-',1) as pohead_number,
            COALESCE((SELECT invdetail_id FROM invdetailview where invhist_id = gltrans_misc_id),0) as invdetail_id
            FROM
            gltrans
            WHERE
            gltrans_source = 'S/R'
            AND
            gltrans_accnt_id = v_lb_accnt_id
            AND
            gltrans_doctype = 'PO' and gltrans_posted
            AND
            gltrans_misc_id > 0
            AND
            gltrans_docnumber like v_pohead_match
        ) x
        WHERE invdetail_id = 0
        
    );
    
    UPDATE pohead set pohead_has_error = 2 where pohead_number IN (
        select
                distinct(pohead_number) as pohead_number
            FROM
            (
                select
                    CONCAT(pohead_number, '-', poitem_linenumber) as ordnumber,
                    pohead_id,
                    recv_itemsite_id,
                    max(recv_id) as recv_id_max,
                    SUM(recv_qty) as total_recv_qty,
                    COALESCE((SELECT
                        sum(invdetail_qty)
                        FROM
                            invdetailview
                        WHERE
                            invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
                        AND
                            invhist_posted
                        AND
                            invhist_transtype = 'RP'
                        AND
                            invhist_ordtype = 'PO'
                        AND
                            invhist_itemsite_id = recv_itemsite_id
                        AND
                            invdetail_qty > 0
                    ),0) as tx_qty
                    from
                        recv
                    LEFT JOIN
                        recvgrp
                    on
                        recvgrp_id = recv_recvgrp_id
                    LEFT JOIN
                        poitem
                    ON
                        poitem_id = recv_orderitem_id
                    LEFT JOIN
                        pohead
                    ON
                        pohead_id = poitem_pohead_id
                    where
                        recv_posted
                        AND
                        recv_recvgrp_id IS NOT NULL
                        AND
                        pohead_number like v_pohead_match
                    GROUP BY
                        pohead_number, poitem_linenumber, recv_itemsite_id, pohead_id
            ) x where
                tx_qty != total_recv_qty
                AND tx_qty = 0.0
                AND pohead_number like v_pohead_match
                GROUP by pohead_id
        );
        
     UPDATE pohead set pohead_has_error = 3 where pohead_number IN (
        select
                distinct(pohead_number) as pohead_number
            FROM
            (
                select
                    pohead_number,
                    CONCAT(pohead_number, '-', poitem_linenumber) as ordnumber,
                    pohead_id,
                    SUM(poitem_qty_received - poitem_qty_returned) as po_qty,
                    COALESCE((SELECT
                        sum(invdetail_qty)
                        FROM
                            invdetailview
                        WHERE
                            invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
                        AND
                            invhist_posted
                        AND
                            invhist_transtype = 'RP'
                        AND
                            invhist_ordtype = 'PO'
                        AND
                            invhist_itemsite_id = poitem_itemsite_id
                        
                    ),0) as tx_qty
                    from
                    
                        poitem
                    LEFT JOIN
                        pohead
                    ON
                        pohead_id = poitem_pohead_id
                    WHERE
                        pohead_number like v_pohead_match
                    GROUP BY
                        pohead_number, poitem_linenumber,  pohead_id,poitem_itemsite_id
            ) x where
                po_qty != tx_qty
                  AND pohead_number like v_pohead_match
                GROUP by pohead_id,pohead_number
        );
        
        
         
        
        RETURN 1; 
   END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION  x_fix_pohead_flag_errors(int)
  OWNER TO admin;
 
 