
CREATE OR REPLACE FUNCTION  period_temp_open(i_target_dt DATE)
    RETURNS  INTEGER
    
AS $BODY$
DECLARE        
    v_tmp INTEGER;
    v_btmp BOOLEAN;
    v_ret INTEGER;
    
    r_period RECORD;
    r_invlocdist RECORD;
    r_tmp RECORD;
    
    v_period_start DATE;
    v_period_end DATE;
    v_period_closed BOOLEAN;
    v_period_freeze BOOLEAN;
BEGIN

    
    SELECT
            period_start INTO v_period_start
        FROM
            period
        WHERE
            period_start <= i_target_dt
            AND
            period_end >= i_target_dt
        LIMIT 1;
    
    
    SELECT
            period_closed,  period_freeze
            INTO
            v_period_closed, v_period_freeze
        FROM
            period
        WHERE
            period_start = v_period_start;
    
    
    
    --RAISE NOTICE 'period state is % ', v_period_closed;
    
    IF NOT v_period_closed AND NOT v_period_freeze THEN
        RETURN 0;
    END IF;
    
    SELECT
            period_start INTO v_period_end
        FROM
            period
        WHERE
            period_closed
        ORDER BY
            period_start DESC LIMIT 1;

    --RAISE NOTICE 'opening periods between % and %', v_period_start, v_period_end;
    
    PERFORM thawAccountingPeriod(period_id) FROM
        (SELECT
                period_id
            FROM
                period
            WHERE
                period_start >= v_period_start
                AND
                period_start <= v_period_end
            ORDER BY
                period_start DESC
        ) x;
    
    PERFORM openAccountingPeriod( period_id ) FROM
        (SELECT
                period_id
            FROM
                period
            WHERE
                period_start >= v_period_start
                AND
                period_start <= v_period_end
            ORDER BY
                period_start DESC
        ) x;
    
        --IF v_tmp < 0 THEN
        --    RAISE EXCEPTION 'open period returned %', v_tmp;
        --END IF;
    
    SELECT period_id INTO v_ret FROM period where period_start = v_period_end LIMIT 1;
    RETURN v_ret;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  
ALTER FUNCTION  period_temp_open(  DATE)
  OWNER TO admin;    



