Adding Reconciliation Module

git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/trunk@1249 4979c152-3d1c-0410-bac9-87ea11338e46

1 files changed, 346 insertions, 0 deletions

diff --git a/sql/modules/Reconciliaton.sql b/sql/modules/Reconciliaton.sql
new file mode 100644
index 00000000..161d9369
--- /dev/null
+++ b/sql/modules/Reconciliaton.sql
@@ -0,0 +1,346 @@
+CREATE TABLE pending_reports (
+    id bigserial primary key not null,
+    report_id int,
+    scn int,
+    their_balance INT,
+    our_balance INT,
+    code 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'
+);
+
+-- 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
+    
+        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
+    
+        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 () 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;
\ No newline at end of file