path: root/sql/modules/Reconciliaton.sql

CREATE TABLE pending_reports (
    id bigserial primary key not null,
    report_id int,
    scn int,
    their_balance INT,
    our_balance INT,
    errorcode INT,
    user TEXT,
    corrections INT NOT NULL DEFAULT 0
    clear_time TIMESTAMP NOT NULL,
    insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
    ledger_id int REFERENCES acc_trans(entry_id),
    overlook boolean not null default 'f'
);

CREATE TABLE report_corrections (
    id serial primary key not null,
    correction_id int not null default 1,
    entry references pending_reports(id) not null,
    user text not null,
    reason text not null,
    insert_time timestamptz not null default now()
);

-- to correct OUR wrong amount.
CREATE OR REPLACE FUNCTION reconciliation_correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$

    DECLARE
        new_code INT;
        current_row RECORD;
        l_row acc_trans;
        in_user TEXT;
        full_reason TEXT;
    BEGIN
        select into in_user from current_user;
        
        select into current_row from pending_reports where pending_reports.id = in_report_id and pending_reports.id = in_id;
        select into l_row from acc_trans where entry_id = current_row.lid;
        
        IF NOT FOUND THEN
            RAISE EXCEPTION 'No such id % in this report.', in_scn;
        END IF;
        
        IF user <> current_row.user THEN
        
            IF current_row.our_balance <> in_new_amount AND in_new_amount = current_row.their_balance THEN
                update pending_reports pr
                set pr.corrections = pending_reports.corrections + 1, 
                pr.new_balance = in_new_amount,
                error_code = 0
                where id = in_report_id and scn = in_scn;
                return 0;
                
                -- After that, it is required to update the general ledger.
                full_reason := "User % is filing a reconciliation correction on the general ledger, changing amount % to amount %. 
                Their reason given is: %", in_user, current_row.our_balance, in_new_amount, reason;
                select update_ledger(current_row.lid, in_new_amount, full_reason)
            ELSE IF current_row.our_balance = in_new_amount THEN
                -- This should be something, does it equal the original 
                -- balance? If so, there's no change.
                return current_row.error_code;
            END IF;
        END IF;
        
        return current_row.error_code;            
                    
    END;
$$ language 'plpgsql';

-- to correct an incorrect bank statement value.
CREATE OR REPLACE FUNCTION reconciliation_correct_bank_statement (in_report_id INT, in_id int, in_new_amount NUMERIC) returns INT AS $$

    DECLARE
        new_code INT;
        current_row RECORD;
        in_user TEXT;
    BEGIN
        select into in_user from current_user;
        select into current_row from pending_reports where pending_reports.id = in_report_id and pending_reports.scn = in_scn;
        IF NOT FOUND THEN
            RAISE EXCEPTION 'No such SCN % in this report.', in_scn;
        END IF;
        
        IF user <> current_row.user THEN
        
            IF current_row.their_balance <> in_new_amount AND in_new_amount = current_row.our_balance THEN
                update pending_reports pr
                set pr.corrections = pending_reports.corrections + 1, 
                pr.new_balance = in_new_amount,
                error_code = 0
                where id = in_report_id and scn = in_scn;
                return 0;
            
            ELSE IF current_row.their_balance = in_new_amount THEN
                -- This should be something, does it equal the original 
                -- balance? If so, there's no change.
                return current_row.error_code;         
            END IF;
        END IF;
        
        return current_row.error_code;            
                    
    END;
$$ language 'plpgsql';


CREATE OR REPLACE reconciliation_correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$

    DECLARE
        in_user TEXT;
        pending_entry pending_reports;
    BEGIN
        select into in_user from current_user;
        
        select into pending_entry from pending_reports where report_id = in_report_id and id = in_id;
        
        IF NOT FOUND THEN
            -- Raise an exception.
            RAISE EXCEPTION "Cannot find entry.";
        ELSE IF pending_entry.errorcode <> 4 THEN 
            -- Only error codes of 4 may be "passed through" safely.
            RAISE EXCEPTION "Selected entry not permitted to be passed through.";
            
        ELSE
            -- Then we mark it passthroughable, and "approve" will overlook it.
            update pending_reports set overlook = 't', errorcode = 0 where report_id = in_report_id and id = in_id;
            return 0;
        END IF;
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation_correct_bank_charge (in_report_id int, in_id int) returns INT AS $$

    DECLARE
        in_user TEXT;
        pending_entry pending_reports;
    BEGIN
    
        select into pending_entry from pending_reports where report_id = in_report_id and id = in_id;
    
        IF NOT FOUND THEN
             -- Raise an exception.
             RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
         ELSE IF pending_entry.errorcode <> 2 THEN 
             -- Only error codes of 4 may be "passed through" safely.
             RAISE EXCEPTION "Attempt to retroactively add a non-bank-charge entry to the ledger.";
         
         ELSE
             -- Then we mark it passthroughable, and "approve" will overlook it.
             
             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date, 'Bank charge');
             
             update pending_reports set errorcode = 0 where report_id = in_report_id and id = in_id;
             return 0;
         END IF;
    END;

$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation_correct_unaccounted_charge (in_report_id int, in_id int, reason TEXT) RETURNS INT AS $$

    DECLARE
        in_user TEXT;
        pending_entry pending_reports;
        note TEXT;
    BEGIN
    
        select into pending_entry from pending_reports where report_id = in_report_id and id = in_id;
    
        IF NOT FOUND THEN
             -- Raise an exception.
             RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
         ELSE IF pending_entry.errorcode <> 3 THEN 
             -- Only error codes of 4 may be "passed through" safely.
             RAISE EXCEPTION "Not an unaccounted charge; cannot be retroactively added to the ledger.";
         
         ELSE
             -- Then we mark it passthroughable, and "approve" will overlook it.
             
             note := 'Retroactive addition of an unaccounted entry, of value %. 
             Being added by user % with the following explanation: %', pending_entry.their_balance, in_user, in_reason;
             
             select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date,note);
             
             update pending_reports set errorcode = 0 where report_id = in_report_id and id = in_id;
             return 0;
         END IF;
    END;
$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation_report_approve (in_report_id INT) returns INT as $$
    
    -- Does some basic checks before allowing the approval to go through; 
    -- moves the approval to "reports", I guess, or some other "final" table.
    --
    -- Pending may just be a single flag in the database to mark that it is
    -- not finalized. Will need to discuss with Chris.
    
    DECLARE
        current_row RECORD;
        completed pending_reports;
        total_errors INT;
        in_user TEXT;
    BEGIN
        
        select into in_user current_user;
        select into current_row distinct on user * from pending_reports where report_id = in_report_id;
        IF NOT FOUND THEN
            RAISE EXCEPTION "Fatal Error: Pending report % not found", in_report_id;
        END IF;
        
        IF current_row.user = in_user THEN
            RAISE EXCEPTION "Fatal Error: User % cannot self-approve report!", in_user;
        END IF;
        
        SELECT INTO total_errors count(*) from pending_reports where report_id = in_report_id and error_code <> 0;
        
        IF total_errors <> 0 THEN
            RAISE EXCEPTION "Fatal Error: Cannot approve while % uncorrected errors remain.", total_errors;
        END IF;
        
        -- so far, so good. Different user, and no errors remain. Therefore, we can move it to completed reports.
        --
        -- User may not be necessary - I would think it better to use the 
        -- in_user, to note who approved the report, than the user who
        -- filed it. This may require clunkier syntax..
        
        insert into reports (report_id, scn, their_balance, our_balance, code, user, correction )
            SELECT report_id, scn, their_balance, our_balance, code, user, corrections 
            FROM pending_reports 
            WHERE report_id = in_report_id;
        
        return 1;        
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation_new_report_id () returns INT as $$

    SELECT nextval('pending_report_report_id_seq');

$$ language 'sql';

create or replace function reconciliation_add_entry(in_report_id INT, in_scn INT, in_amount INT, in_account INT, in_user TEXT, in_date TIMESTAMP) RETURNS INT AS $$
    
    DELCARE
        la RECORD;
        errorcode INT;
        our_value NUMERIC;
        lid INT;
    BEGIN
    
        SELECT INTO la FROM acc_trans gl 
        WHERE gl.source = in_scn 
        and gl.account = in_account 
        and gl.amount = in_amount;
        
        lid := NULL;
        IF NOT FOUND THEN
            -- they have it, we don't. This is Bad, and implies either a bank
            -- charge or an unaccounted cheque.
            
            if in_scn <> '' and in_scn IS NOT NULL THEN
            
                -- It's a bank charge. Approval action will probably be 
                -- adding it as an entry to the general ledger.
                errorcode := 2; 
                our_value := 0;
            ELSE
                -- Okay, now this is bad.
                -- They have a cheque/sourced charge that we don't. 
                -- REsolution action is going to be
                errorcode := 3;
                our_value := 0;
            END IF;
            
        ELSE if la.amount <> in_amount THEN
        
            errorcode := 1;
            our_value := la.amount;
            lid := la.entry_id;
            
        ELSE
            -- it reconciles. No problem.
            
            errorcode := 0;
            our_value := la.amount;
            lid := la.entry_id;
            
        END IF;
        
        INSERT INTO pending_reports (
                report_id,
                scn,
                their_balance,
                our_balance,
                error_code, 
                user,
                clear_time,
                ledger_id
            ) 
            VALUES (
                in_report_id,
                in_scn,
                in_amount,
                gl.balance,
                errorcode,
                in_user,
                in_date,
                lid
            );
            
        -- success, basically. This could very likely be collapsed to
        -- do the compare check here, instead of in the Perl app. Save us a DB
        -- call.
        return 1; 
        
    END;    
$$ language 'plpgsql';

create or replace function reconciliation_pending_transactions (in_report_id INT, in_month TIMESTAMP, in_user INT) RETURNS setof int as $$
    
    DECLARE
        gl_row acc_trans;
    BEGIN
    
        FOR gl_row IN
            select gl.* from acc_trans gl, pending_reports pr 
            where gl.cleared = 'f' 
            and date_trunc('month',gl.transdate) <= date_trunc('month', in_month)
            and gl.entry_id <> pr.lid
        LOOP
        
            INSERT INTO pending_reports (
                report_id,
                scn,
                their_balance,
                our_balance,
                error_code, 
                user,
                clear_time,
                ledger_id
            ) 
            VALUES (
                in_report_id,   -- add it to the report ID
                gl_row.source,  -- the source control number
                0,              -- The banks' amount for the transaction
                gl_row.amount,  -- our recorded amount
                4,              -- The error code, meaning it's uncleared.
                in_user,        -- the report-generating user
                in_month,       -- basically, right now.
                gl_row.entry_id -- the foreign key to the ledger
            );
        
        END LOOP;
    END;
    
$$ language plpgsql;

CREATE OR REPLACE FUNCTION reconciliation_report (in_report_id INT) RETURNS setof pending_reports as $$

    DECLARE
        row pending_reports;
    BEGIN    
        FOR row IN select * from pending_reports where report_id = in_report_id LOOP
        
            RETURN NEXT row;
        
        END LOOP;    
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation_get_total (in_report_id INT) returns setof pending_reports AS $$

    DECLARE
        row pending_reports;
    BEGIN
    
        SELECT INTO row FROM pending_reports 
        WHERE ledger_id IS NULL 
        and report_id = in_report_id 
        AND scn = -1;
        
        IF NOT FOUND THEN -- I think this is a fairly major error condition
            RAISE EXCEPTION "No Bank Total found.";
        ELSE
            return row;
        END IF;
    END;

$$ language 'plpgsql';

CREATE OR REPLACE FUNCTION reconciliation_corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$

    DECLARE
        corr report_corrections;
    BEGIN
    
        SELECT INTO corr FROM report_corrections WHERE report_id = in_report_id AND id = in_id LIMIT 1;
        IF NOT FOUND THEN
            RAISE EXCEPTION "No corrections for selected entry.";
        ELSE
            FOR corr IN select * from report_corrections WHERE report_id = in_report_id AND id = in_id LOOP
                RETURN NEXT corr;
            END LOOP;
        END IF;
    END;

$$ language 'plplsql';

CREATE OR REPLACE FUNCTION reconciliation_single_entry (in_report_id INT, in_id INT) returns setof pending_reports AS

    DECLARE
        row pending_reports;
    BEGIN
    
        SELECT INTO row FROM pending_reports WHERE report_id = in_report_id and id = in_id LIMIT 1; 
        -- if there's more than one, that's a Bad Thing
        
        IF NOT FOUND THEN
            RAISE EXCEPTION "Could not find selected report entry";
        ELSE
            RETURN row;
        END IF;
    END;

$$ language 'plpgsql';