-- void all the gl transactions which do not have a misc Id for a specific pohead..









CREATE OR REPLACE FUNCTION x_fix_gltrans_pohead_misc(i_pohead_id  int)
  RETURNS  int  AS
$BODY$
DECLARE
    v_pohead_number TEXT;  
   v_lb_accnt_id INT;
BEGIN
     SELECT 
             costcat_liability_accnt_id
    INTO 
             v_lb_accnt_id
    FROM 
            costcat 
    LIMIT 1;
    
    
    SELECT pohead_number into v_pohead_number from pohead where pohead_id = i_pohead_id;

    PERFORM deleteGlSeries(gltrans_sequence,  'Fix Unmatched Purchase transaction' || to_char(NOW(), 'Day Mon DD YYY') ) FROM
        (select distinct(gltrans_sequence) FROM 
            (SELECT
                    *,
                    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_number || '-%'
                AND
                    gltrans_posted
                AND
                    NOT gltrans_deleted
            ) x
            WHERE invdetail_id = 0
        ) y;
    
    PERFORM x_fix_pohead_flag_errors(i_pohead_id);
    
    RETURN 1;
        
END     
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION  x_fix_gltrans_pohead_misc(int)
  OWNER TO admin;
 
