Datanory Production » Datanory - GOLDCREST

as we check from our side we notice that there are 5 items,
we we have run the SP on this particular invoice INMCJ000070-240328

----Temp table for on_behalf
--Select customer_id, oba.member_id as user_id, ob.member_id as salesman_id Into #on_behalf from on_behalf_access_customer obc
--Inner Join on_behalf_access oba On oba.access_id = obc.access_id
--Inner Join on_behalf ob On ob.on_behalf_id = obc.on_behalf_id Where ob.status = 'Active' and oba.member_id = '2c9e40b5f5904775b71192b8fd8b71ad' and (GetDate() between ob.start_date and ob.end_date)

-- select CASE status
-- WHEN 'Active' THEN 'Insert/Update' END as action,
-- invoice_id as invoice_id, Invoice as docNo, item_id, uom_code, qty, price, subtotal, qty_bal, so_discount_amount, total_discount
-- from(

-- SELECT ROW_NUMBER() OVER (ORDER BY invoice_id , sales_order_line ) AS TotalRowNumber,
-- invoice_no as Invoice, sales_order_line as SalesOrderLine
-- , item_id as item_id, product_uom_code as uom_code, qty_invoiced as qty, return_qty_balance as qty_bal, mprice as price, line_total as subtotal, status, invoice_id, ISNULL as so_discount_amount, ISNULL as total_discount
-- from(
-- select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance,
-- d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
-- from txn_invoice_history_detail(nolock) d
-- inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
-- --end version
-- left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
-- where h.tenant_id = 'Goldcrest'
-- and exists (Select 1 from customer_to_user_relation(nolock) cr where cr.customer_id= h.customer_id and cr.user_id= '2c9e40b5f5904775b71192b8fd8b71ad'
-- and convert(date,getdate()) between start_date and end_date)
-- group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
-- Union

-- -- Modify on 20230926 (Download Sub Account data)
-- select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance,
-- d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
-- from txn_invoice_history_detail(nolock) d
-- inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
-- inner join customer c on c.customer_id = h.customer_id
-- left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
-- inner join org o on o.org_id = h.org_id
-- WHERE h.tenant_id = 'Goldcrest' and o.master_acc_billing = 'Yes'
-- and exists (Select 1 from customer_to_user_relation(nolock) cr where cr.user_id= '2c9e40b5f5904775b71192b8fd8b71ad' and cr.customer_id = c.master_account)
-- group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
-- Union
-- --Details for on behalf salesman
-- select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance,
-- d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
-- from txn_invoice_history_detail(nolock) d
-- inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
-- --version arif 20220317
-- and h.invoice_id=d.invoice_id
-- --end version
-- left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
-- where h.tenant_id = 'Goldcrest'
-- and exists (Select 1 from #on_behalf(nolock) obc Where obc.customer_id = h.customer_id)

-- group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date

-- Union

-- -- Modify on 20230926 (Download Sub Account data)
-- --Details for on behalf salesman
-- select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance,
-- d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
-- from txn_invoice_history_detail(nolock) d
-- inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id

-- --version arif 20220317
-- --and h.invoice_id='9b682788-5274-411b-8fb9-ea3005721a0f'
-- --end version
-- left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
-- inner join customer c on c.customer_id = h.customer_id
-- inner join org o on o.org_id = h.org_id
-- WHERE h.tenant_id = 'Goldcrest' and o.master_acc_billing = 'Yes'
-- and exists (Select 1 from #on_behalf(nolock) obc where c.master_account = obc.customer_id)
-- group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date

-- )x where Convert(varchar(10), updated_date, 120) >= Convert(varchar(10), DATEADD), 120) OR invoice_balance > 0

-- )v
-- where TotalRowNumber between
-- (1*999999)+1-999999and 1 * 999999
-- --and v.invoice_id= '9b682788-5274-411b-8fb9-ea3005721a0f';

---
-- Create temp table for on_behalf
Select customer_id, oba.member_id as user_id, ob.member_id as salesman_id
Into #on_behalf
from on_behalf_access_customer obc
Inner Join on_behalf_access oba On oba.access_id = obc.access_id
Inner Join on_behalf ob On ob.on_behalf_id = obc.on_behalf_id
Where ob.status = 'Active'
and oba.member_id = '2c9e40b5f5904775b71192b8fd8b71ad'
and (GetDate() between ob.start_date and ob.end_date);

-- Main query with filter for specific invoice_id
select CASE status
WHEN 'Active' THEN 'Insert/Update' END as action,
invoice_id as invoice_id,
Invoice as docNo,
item_id,
uom_code,
qty,
price,
subtotal,
qty_bal,
so_discount_amount,
total_discount
from (
SELECT ROW_NUMBER() OVER (ORDER BY invoice_id , sales_order_line ) AS TotalRowNumber,
invoice_no as Invoice,
sales_order_line as SalesOrderLine,
item_id as item_id,
product_uom_code as uom_code,
qty_invoiced as qty,
return_qty_balance as qty_bal,
mprice as price,
line_total as subtotal,
status,
invoice_id,
ISNULL as so_discount_amount,
ISNULL as total_discount
from (
select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance,
d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance,
d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
from txn_invoice_history_detail(nolock) d
inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
inner join product_item pro on pro.item_id= d.item_id
left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
where h.tenant_id = 'Goldcrest'
and exists (Select 1 from customer_to_user_relation(nolock) cr
where cr.customer_id= h.customer_id
and cr.user_id= '2c9e40b5f5904775b71192b8fd8b71ad'
and convert(date,getdate()) between start_date and end_date)
group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no,
d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date,
h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date

Union

-- Modify on 20230926 (Download Sub Account data)
        select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance, 
            d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, 
            d.web_add_disc_amount, d.web_product_total_disc, h.updated_date    
        from txn_invoice_history_detail(nolock) d
        inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
        inner join customer c on c.customer_id = h.customer_id
        left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
        inner join org o on o.org_id = h.org_id
        WHERE h.tenant_id = 'Goldcrest' 
          and o.master_acc_billing = 'Yes'
          and exists (Select 1 from customer_to_user_relation(nolock) cr 
                      where cr.user_id= '2c9e40b5f5904775b71192b8fd8b71ad' 
                        and cr.customer_id = c.master_account)
        group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, 
                 d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, 
                 h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date

Union

--Details for on behalf salesman
        select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance, 
            d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, 
            d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
        from txn_invoice_history_detail(nolock) d
        inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
        left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
        where h.tenant_id = 'Goldcrest' 
          and exists (Select 1 from #on_behalf(nolock) obc 
                      where obc.customer_id = h.customer_id)
        group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, 
                 d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, 
                 h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date

Union

-- Modify on 20230926 (Download Sub Account data)
        --Details for on behalf salesman
        select h.invoice_id, h.invoice_no, d.sales_order_line, d.item_id, d.qty_invoiced, d.return_qty_balance, 
            d.mprice, d.line_total, m.product_uom_code, 'Active' as status, h.invoice_date, h.invoice_balance, 
            d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
        from txn_invoice_history_detail(nolock) d
        inner join txn_invoice_history_header(nolock) h on h.invoice_id=d.invoice_id
        left join mst_product_uom(nolock) m on m.product_uom_id = d.morder_uom
        inner join customer c on c.customer_id = h.customer_id
        inner join org o on o.org_id = h.org_id
        WHERE h.tenant_id = 'Goldcrest' 
          and o.master_acc_billing = 'Yes'
          and exists (Select 1 from #on_behalf(nolock) obc 
                      where c.master_account = obc.customer_id)
        group by d.item_id, m.product_uom_code, d.mprice, d.line_total, h.invoice_id, h.invoice_no, 
                 d.sales_order_line, d.qty_invoiced, d.return_qty_balance, h.invoice_date, 
                 h.invoice_balance, d.web_add_disc_amount, d.web_product_total_disc, h.updated_date
    ) x  
    where Convert(varchar(10), updated_date, 120) >= Convert(varchar(10), DATEADD(MONTH, -2, GetDate()), 120) 
       OR invoice_balance > 0 
) v 
where TotalRowNumber between (1 * 999999) + 1 - 999999 and 1 * 999999
  and invoice_id = '9b682788-5274-411b-8fb9-ea3005721a0f';

--drop Table #on_behalf

Already asked tech team ( shu ern for Advise)
waiting for the feedback

As I checked for this particular invoice there is 5 item Under SP

Need your confirmation on this matter

Arif

https://app.clickup.com/t/86epmxktf

https://app.clickup.com/t/86eptfwzx

Goldcrest has raised an escalation regarding an incorrect calculation in the web portal for invoice INWWQ000963-240619.
Upon review, the total should be calculated as 24×3×2.2083=158.997624 which should be rounded to 159.00. However, the portal shows 159.12 instead.
We need assistance from the technical team to address this issue.
--

https://app.clickup.com/t/86eptfp1k

Goldcrest has raised an escalation regarding incorrect invoice quantities during the mobile upload process for invoice INWWQ000963-240619.
Upon review, the SKU setup appears to be correct for all items in this invoice. The specific SKUs in question are:
250-AP01
250-BC01
250-BC01
250-LC01
250-OR01
The UOM stock is set to CTN with a conversion factor of 24. Each SKU ordered should total 72 pieces (3 CTN each), and for 250-BC01, the total should be 24 pieces. However, on the mobile device, it shows 576 and 192 pieces, respectively and for
250-BC01 is 192 pieces
We need assistance from the technical team to address this escalation. Please find the attached Files for reference.

testing data V4.14-Trial.0

Org_id : Goldcrest

User: MCJ

Invoice NO: INMCJ000034-240715

as on the tested the total amount in the mobile is same as in the portal

https://app.clickup.com/t/86eptfp1k--> this ticket we found it internally, this ticket already resolved
as we checked the numbers are appears correct.

Case Closed