[Noalyss-commit] [noalyss] 140/323: Bug : ANC ND VAT not computed

[Dany De Bontridder]

sparkyx pushed a commit to branch master
in repository noalyss.

commit 64642d250fbc81bdc8b720295203cf3bead12f86
Author: Dany De Bontridder <address@hidden>
Date:   Wed Feb 7 14:25:49 2018 +0100

    Bug : ANC ND VAT not computed
---
 include/sql/patch/upgrade125.sql | 36 ++++++++++++++++++++++++++++++++++++
 1 file changed, 36 insertions(+)

diff --git a/include/sql/patch/upgrade125.sql b/include/sql/patch/upgrade125.sql
new file mode 100644
index 0000000..b5539c8
--- /dev/null
+++ b/include/sql/patch/upgrade125.sql
@@ -0,0 +1,36 @@
+begin;
+CREATE OR REPLACE FUNCTION comptaproc.anc_correct_tvand()
+RETURNS VOID
+AS $function$ 
+declare
+        n_count numeric;
+        i record;
+        newrow_tva record;
+begin
+         for i in select * from operation_analytique where oa_jrnx_id_source 
is not null loop
+         -- Get all the anc accounting from the base operation and insert the 
missing record for VAT 
+                for newrow_tva in select *  from operation_analytique where 
j_id=i.oa_jrnx_id_source and po_id <> i.po_id loop
+                    
+                        -- check if the record is yet present
+                        select count(*) into n_count from operation_analytique 
where  po_id=newrow_tva.po_id and oa_jrnx_id_source=i.oa_jrnx_id_source;
+
+                        if n_count = 0 then
+                          raise info 'insert operation analytique po_id = % 
oa_group = % ',i.po_id, i.oa_group;
+                          insert into operation_analytique 
+                          
(po_id,oa_amount,oa_description,oa_debit,j_id,oa_group,oa_date,oa_jrnx_id_source,oa_positive)
+                          values 
(newrow_tva.po_id,i.oa_amount,i.oa_description,i.oa_debit,i.j_id,i.oa_group,i.oa_date,i.oa_jrnx_id_source,i.oa_positive);
+                        end if;
+         
+                end loop;
+
+         
+         end loop;
+end;
+ $function$
+LANGUAGE plpgsql;
+
+select comptaproc.anc_correct_tvand();
+
+update version set val=126;
+
+commit;
\ No newline at end of file