Datanory Production » Datanory - SUNTORY

-- Check Inventory Tables

Select * from warehouse_inventory_header where org_id ='KYMMSENG' order by txn_date desc

Select * from warehouse_inventory_detail where warehouse_inventory_hdr_id = 'KSWHGS0001-SUNTORY-20240606'

Select * from mst_warehouse_item wi where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'

-- Patch inventory -- (Note: warehouse_inventory_detail is stock UOM)

        --Update warehouse_inventory_detail set

        --opening_gs = x.closing_gs,

        --reserved_gs = x.reserved_gs,

        --closing_gs = x.closing_gs,

        --opening_bs = x.closing_bs,

        --closing_bs = x.closing_bs,

        --updated_by = 'System', updated_date = getdate()

        --from (

        --Select * from warehouse_inventory_detail where warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240605'

        --) x

        --where warehouse_inventory_detail.warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240606' and warehouse_inventory_detail.item_id = x.item_id and x.uom_id = warehouse_inventory_detail.uom_id

-- Patch closing stock of yesterday's inventory --

        --Update warehouse_inventory_detail set closing_gs = y.closing_gs, closing_bs = y.closing_bs from (

        --Select dd.warehouse_inventory_dtl_id, dd.item_id, dd.warehouse_inventory_hdr_id,  x.closing_gs, x.closing_bs from warehouse_inventory_detail dd 

        --inner join (

        --Select warehouse_inventory_dtl_id, warehouse_inventory_hdr_id, item_id, uom_id, opening_gs, stock_in_gs,

        --stock_out_gs, reserved_gs, (opening_gs + stock_in_gs - stock_out_gs) [closing_gs] , opening_bs, stock_in_bs, stock_out_bs, (opening_bs + stock_in_bs - stock_out_bs) [closing_bs]

        --from warehouse_inventory_detail where warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240605' 

        --) x on x.warehouse_inventory_dtl_id = dd.warehouse_inventory_dtl_id 

        --where dd.closing_gs <> x.closing_gs or dd.closing_bs <> x.closing_bs

        --) y where y.warehouse_inventory_dtl_id = warehouse_inventory_detail.warehouse_inventory_dtl_id and y.warehouse_inventory_hdr_id = warehouse_inventory_detail.warehouse_inventory_hdr_id and y.item_id = warehouse_inventory_detail.item_id and warehouse_inventory_detail.warehouse_inventory_hdr_id = 'KSWHGS0001-SUNTORY-20240605' 

-- Insert into warehouse_inventory_detail where not exists --

        --insert into warehouse_inventory_detail (warehouse_inventory_dtl_id, warehouse_inventory_hdr_id, item_id, uom_id, opening_gs, stock_in_gs, stock_out_gs, reserved_gs, closing_gs, opening_bs, stock_in_bs, stock_out_bs, closing_bs, status, created_date, created_by, updated_date ,updated_by, tenant_id, org_id)

        --Select newid(), 'KSWHGS0001-SUNTORY-20240606', item_id, uom_id, closing_gs, 0, 0, reserved_gs, closing_gs, closing_bs, 0, 0, closing_bs, status, GEtDAte(), 'System', GetDate() ,'System', tenant_id, org_id

        --from warehouse_inventory_detail dd where warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240605' 

        --and not exists (Select 1 from warehouse_inventory_detail d where d.warehouse_inventory_hdr_id = 'KSWHGS0001-SUNTORY-20240606' and d.item_id = dd.item_id)

        -- Check mst_warehouse_item --

        Select * from mst_warehouse_item wi

        inner join warehouse_inventory_detail d on d.item_id = wi.item_id

        where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  and d.warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240606'

-- Check duplication in mst_warehouse_item

        Select * from mst_warehouse_item w where exists (

        Select 

        item_id from mst_warehouse_item wi where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  

        and w.item_id = wi.item_id

        group by item_id having count(1) > 1

        )

        and w.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  

        order by item_id

-- Delete Duplication in mst_Warehouse_item

        --delete from mst_warehouse_item where tenant_id = 'KYMMSENG' and warehouse_item_id in (

        Select warehouse_item_id from mst_warehouse_item w where exists (

        Select 

        item_id from mst_warehouse_item wi where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  

        and w.item_id = wi.item_id

        group by item_id having count(1) > 1

        )

        and w.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3' and tenant_id ='KYMMSENG'

        --)

-- Update quantity in mst_warehouse_item (quantity in lowest uom ) --

        --Update mst_warehouse_item set

        --quantity_available = x.quantity

        --from (

        --Select wi.warehouse_item_id, wi.item_id, quantity_available, wi.warehouse_id, d.closing_gs, p.cf_oth_uom, d.closing_gs * p.cf_oth_uom [quantity] from mst_warehouse_item wi

        --inner join warehouse_inventory_detail d on d.item_id = wi.item_id

        --inner join product_item p on p.item_id = d.item_id 

        --where wi.warehouse_id ='0086d5d6-b9f1-44ba-a35c-1051b438ebf3'  and d.warehouse_inventory_hdr_id ='KSWHGS0001-SUNTORY-20240606'

        --and Cast(d.closing_gs * p.cf_oth_uom as decimal(18,2)) <> wi.quantity_available

        --) x

        --where mst_warehouse_item.item_id = x.item_id and mst_warehouse_item.warehouse_item_id = x.warehouse_item_id