add max and group in purchasing job

1 files changed, 6 insertions, 3 deletions

diff --git a/indoteknik_custom/models/purchasing_job.py b/indoteknik_custom/models/purchasing_job.py
index 756b1079..bf5ed8c4 100644
--- a/indoteknik_custom/models/purchasing_job.py
+++ b/indoteknik_custom/models/purchasing_job.py
@@ -65,10 +65,13 @@ class PurchasingJob(models.Model):
                     END AS purchase_representative_id
             FROM v_procurement_monitoring_by_product pmp
                 LEFT JOIN purchasing_job_state pjs ON pjs.purchasing_job_id = pmp.product_id
-                LEFT JOIN ( SELECT distinct vso.product_id,
-                        sol.vendor_id
+                LEFT JOIN ( 
+                    SELECT vso.product_id,
+                    max(sol.vendor_id) as vendor_id
                     FROM v_sales_outstanding vso
-                        LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id) sub ON sub.product_id = pmp.product_id
+                    LEFT JOIN sale_order_line sol ON sol.id = vso.sale_line_id
+                    group by vso.product_id    
+                ) sub ON sub.product_id = pmp.product_id
             WHERE pmp.action = 'kurang'::text AND sub.vendor_id IS NOT NULL
             GROUP BY pmp.product_id, pmp.brand, pmp.item_code, pmp.product, pmp.action, sub.vendor_id;
         """ % self._table)