metasql/registrar.invoices.sql

diff --git a/metasql/registrar.invoices.sql b/metasql/registrar.invoices.sql
index eef4b89..b2089c0 100644 (file)
--- a/metasql/registrar.invoices.sql
+++ b/metasql/registrar.invoices.sql
@@ -15,6 +15,11 @@ SELECT
         contacts,
         product,
         amount,
+        is_paid,
+        cust_curr_id,
+        cust_curr_id_curr_name,
+        aropen_id,
+        invchead_void,
         <? if exists("status") || exists("search") ?>
             0 AS balance
         <? else ?>
@@ -28,7 +33,10 @@ FROM
             invchead_invcdate,
             invchead_invcnumber,
             to_char(invchead_invcdate,'DD/Mon/YYYY') AS f_invchead_invcdate,
+            custinfo.cust_curr_id AS cust_curr_id,
+            curr_symbol.curr_name AS cust_curr_id_curr_name,
             custinfo.cust_name AS cust_name,
+            aropen.aropen_id AS aropen_id,
             admin_cntct.cntct_registrar_organization AS admin_cntct_registrar_organization,
             CONCAT(
                 admin_cntct.cntct_registrar_organization, E'\n', admin_cntct.cntct_name,
@@ -64,7 +72,13 @@ FROM
                                 invcitem_invchead_id = invchead_id
                         ORDER BY item.item_type
                     ) AS i
-            ) AS product
+            ) AS product,
+            CASE WHEN aropen.aropen_paid < aropen.aropen_amount THEN
+                'NO'
+            ELSE
+                'YES'
+            END AS is_paid,
+            invchead_void
     FROM
             invchead
     LEFT JOIN
@@ -77,6 +91,10 @@ FROM
             custinfo AS custinfo
     ON
             custinfo.cust_id = invchead_cust_id
+    LEFT JOIN
+            curr_symbol
+    ON
+            curr_symbol.curr_id = custinfo.cust_curr_id
     LEFT JOIN
             cntct AS admin_cntct
     ON