Datanory Production » Datanory - SNEKKU

exec sp_executesql N' select d.[ord_dtl_id]
,d.[ord_hdr_id]
,d.[order_line_no]
,d.[product_id]
,case when h.order_status in (''Approved'', ''Auto-Approved'') then isnull(d.[warehouse_id],'''') else isnull(wh_ori.warehouse_id,'''') end as default_warehouse_id
,case when h.order_status in (''Approved'', ''Auto-Approved'') then isnull(wh.warehouse_code,'''') else isnull(wh_ori.warehouse_code,'''') end as default_warehouse_name
,case when exists (select 1 from txn_sales_order_approval_history
where order_id=d.ord_hdr_id
and status in (''Auto-Approved'',''Approved'',''Rejected'',''Closed'',''Auto-Closed'')) then d.[warehouse_id] else '''' end as warehouse_id
,case when exists (select 1 from txn_sales_order_approval_history
where order_id=d.ord_hdr_id
and status in (''Auto-Approved'',''Approved'',''Rejected'',''Closed'',''Auto-Closed'')) then isnull(wh.warehouse_code,'''') else '''' end as warehouse_name
,isnull(convert(decimal(18,2), d.[base_uom_list_price]), 0) as [base_uom_list_price]
,isnull(convert(decimal(18,2), d.[web_uom_list_price]), 0) as [web_uom_list_price]
,isnull(convert(decimal(18,2), d.[web_discount_price]), 0) as [web_discount_price]
,convert(decimal(18,0),d.[mobile_uom1_qty]) as [mobile_uom1_qty]
,convert(decimal(18,0),d.[mobile_uom2_qty]) as [mobile_uom2_qty]
,convert(decimal(18,0),d.[web_uom1_qty]) as [web_uom1_qty]
,convert(decimal(18,0),d.[web_uom2_qty]) as [web_uom2_qty]
,isnull(convert(decimal(18,2),d.[mobile_product_total_disc]),0) as [mobile_product_total_disc]
,isnull(convert(decimal(18,2),d.[web_product_total_disc]),0) as [web_product_total_disc]
,isnull(convert(decimal(18,2),d.[mobile_product_net_amount]),0) as [mobile_product_net_amount]
,isnull(convert(decimal(18,2),d.[web_product_net_amount]),0) as [web_product_net_amount]
,d.[status]
,d.serial_number
,p.item_code as product_code
,REPLACE as product_name
,isnull(uom1.product_uom_code,'''') + '' / '' + isnull(uom2.product_uom_code,'''') as uom_desc
,p.cf_alt_uom as uom_conversion_rate
,isnull(d.web_additional_discount_perc, 0) as web_additional_discount_perc
,isnull(convert(decimal(18,2),d.web_add_disc_amount), 0) as web_add_disc_amount
,isnull(d.is_foc, 0) as is_foc
from txn_sales_order_details d
inner join txn_sales_order_header h on d.ord_hdr_id=h.ord_hdr_id
inner join customer c on h.customer_id=c.customer_id
inner join product_item p on d.product_id=p.item_id
left join warehouse wh on d.warehouse_id=wh.warehouse_id
left join warehouse wh_ori on c.branch_id=wh_ori.branch_id and right(wh_ori.warehouse_id,1) = ''M''
left join mst_product_price pp on c.price_id=pp.price_code_id and p.item_id=pp.item_id
left join mst_product_uom uom1 on uom1.product_uom_id=p.stock_uom_id
left join mst_product_uom uom2 on uom2.product_uom_id=p.alt_uom_id
where d.ord_hdr_id=@ord_hdr_id
order by p.sorting',N'@ord_hdr_id nvarchar(36)',@ord_hdr_id=N'07ccf76c-6efd-4215-b71b-11fb89226b93'

as we check from our side via profiler under ethe order sequence and product are duplicated (no 2 are existed twice )
BM130AF01004--> order detail_id= 'e8a3552e-5340-46f7-8109-4b0318bdfc63'