if qty available greater than zero then include in pricelist

1 files changed, 4 insertions, 2 deletions

diff --git a/calculate_price_after_disc.ktr b/calculate_price_after_disc.ktr
index 80046e5..5a11b82 100644
--- a/calculate_price_after_disc.ktr
+++ b/calculate_price_after_disc.ktr
@@ -619,7 +619,8 @@
     </partitioning>
     <connection>dw bi indoteknik (localhost)</connection>
     <sql>select p.product_id, p.item_code, p.name, b.pricing_group,
-coalesce((select
+coalesce((
+	select
 	case
 		when human_last_update is null then coalesce(system_price, 0)
 		when system_last_update is null then coalesce(product_price, 0)
@@ -628,8 +629,9 @@ coalesce((select
 		else coalesce(product_price, 0)
 	end as purchase_price
 	from purchase_pricelist_dw ppd 
+	join product p2 on p2.product_id = ppd.product_id 
 	where ppd.product_id = p.product_id
-	and (system_last_update >= (now() - '365 days'::interval) or human_last_update >= (now() - '365 days'::interval))
+	and (system_last_update >= (now() - '365 days'::interval) or human_last_update >= (now() - '365 days'::interval) or p2.qty_available > 0)
 	order by ppd.count_trx_po desc, ppd.count_trx_po_vendor desc
 	limit 1
 ),0) as purchase_price,