Need Query to find onhand quantity based on lot and serial

Transact-SQL
1

Hi,
I have four tables mtl_system_items_b,mtl_onhand_quantities_detail,mtl_lot_numbers,mtl_serial_numbers. Based on tables i want onhand quantity in both lot and serial number wise.

image.png960×488 33.9 KB

mtl_onhand_quantities_detail

ORGANIZATION_ID 4 int
INVENTORY_ITEM_ID -5 bigint
SUBINVENTORY_CODE 12 varchar
LOCATOR_ID -5 bigint
PRIMARY_TRANSACTION_QUANTITY 3 decimal
LOT_NUMBER 12 varchar
TRANSACTION_UOM_CODE 12 varchar
TRANSACTION_QUANTITY 3 decimal

mtl_system_items_b
ORGANIZATION_ID 4 int
INVENTORY_ITEM_ID -5 bigint
SEGMENT1 12 varchar
DESCRIPTION -9 nvarchar
INVENTORY_ITEM_STATUS_CODE 12 varchar
PRIMARY_UOM_CODE 12 varchar
LOT_CONTROL_CODE 4 int
SERIAL_NUMBER_CONTROL_CODE 4 int

mtl_serial_numbers
INVENTORY_ITEM_ID -5 bigint
SERIAL_NUMBER 12 varchar
CURRENT_STATUS 4 int
LOT_NUMBER 12 varchar
CURRENT_ORGANIZATION_ID 4 int
CURRENT_SUBINVENTORY_CODE 12 varchar
CURRENT_LOCATOR_ID -5 bigint

mtl_lot_numbers
ORGANIZATION_ID 4 int
INVENTORY_ITEM_ID -5 bigint
LOT_NUMBER -9 nvarchar
EXPIRATION_DATE 11 datetime
STATUS_ID 4 int

By applying below logic on Tables

  1. mtl_system_items_b
    segment1 --> Item name
    inventory_item_id --> Item Number
    primary_uom_code --> UOM
  2. mtl_onhand_quantities_detail
    primary_transaction_quantity --> On hand Qty
  3. mtl_lot_numbers
    lot_number
  4. mtl_serial_numbers
    serial_number

Logic:

  1. Pure Lot Control Items
    lot_control_code <> 1
  2. Pure Serial Control Items
    serial_number_control_code <> 1
    lot_number = NULL
    current_status = 3
  3. Both Lot and Serial Control Items
    serial_number_control_code <> 1
    lot_number <> NULL
    current_status = 3
  4. No Control Items
    lot_control_code = 1
    serial_number_control_code=1

Output Required columns as below

ORGANIZATION_ID
SUBINVENTORY_CODE
LOCATOR_ID
ITEM_NAME
ITEM_NUMBER
ONHAND_QTY
LOT_NUMBER
SERIAL_NUMBER
UOM
LOT_QTY/SERIAL_QTY

Script for generating table

2

Not sure about your logic and how the tables should be joined, but this might start you off:

use stock;

declare @logic int=4; /* 1=Pure Lot Control Items
                         2=Pure Serial Control Items
                         3=Both Lot and Serial Control Items
                         4=No Control Items
                      */

select i.organization_id
      ,o.subinventory_code
      ,o.locator_id
      ,i.segment1 as [Item name]
      ,i.inventory_item_id as [Item number]
      ,sum(o.primary_transaction_quantity) as [On hand qty]
      ,o.lot_number /* or l.lot_number */
      ,s.serial_number
      ,i.primary_uom_code as [UOM]
	  ,case
	      when isnull(count(s.serial_number) over(partition by i.organization_id
                                                              ,o.subinventory_code
                                                              ,o.locator_id
                                                              ,i.segment1
                                                              ,i.inventory_item_id
                                                              ,o.lot_number /* or l.lot_number */
                                                              ,i.primary_uom_code
                                                 )
                     ,0
					 )
              =0
          then null
          else sum(o.primary_transaction_quantity)
              /count(s.serial_number) over(partition by i.organization_id
                                                       ,o.subinventory_code
                                                       ,o.locator_id
                                                       ,i.segment1
                                                       ,i.inventory_item_id
                                                       ,o.lot_number /* or l.lot_number */
                                                       ,i.primary_uom_code
                                          )
       end as [lot/serial]
  from dbo.mtl_system_items_b as i
       inner join dbo.mtl_onhand_quantities_detail as o
               on o.organization_id=i.organization_id
              and o.inventory_item_id=i.inventory_item_id
       left outer join dbo.mtl_serial_numbers as s
                    on s.current_organization_id=o.organization_id
                   and s.inventory_item_id=o.inventory_item_id
                   and s.current_locator_id=o.locator_id
                   and s.current_subinventory_code=o.subinventory_code
                   and @logic in (2,3)
 where (@logic=1 and i.lot_control_code<>'1')
    or (@logic=2 and i.serial_number_control_code<>1 and o.lot_number is null and s.current_status=3)
	or (@logic=3 and i.serial_number_control_code<>1 and o.lot_number is not null and s.current_status=3)
	or (@logic=4 and i.lot_control_code='1' and i.serial_number_control_code=1)
 group by i.organization_id
         ,o.subinventory_code
         ,o.locator_id
         ,i.segment1
         ,i.inventory_item_id
         ,o.lot_number /* or l.lot_number */
         ,s.serial_number
         ,i.primary_uom_code
;
3

mtl_system_items_b is master table with INVENTORY_ITEM_STATUS_CODE active means item is active in stock
mtl_onhand_quantities_detail contains On hand Qty details
mtl_lot_numbers contains lot details
mtl_serial_numbers contains serial details

the logic is
1 lot have 1000 serial(single) items

if Pure Lot Control Items i.e item available only in lot
lot_control_code <> 1

if Pure Serial Control Items i.e item available only in serial
serial_number_control_code <> 1
lot_number = NULL
current_status = 3

if Both Lot and Serial i.e item available in both lot and serial
serial_number_control_code <> 1
lot_number <> NULL
current_status = 3

if item is not in lot and serial
lot_control_code = 1
serial_number_control_code=1

so serial qty is always 1
if item is in serial,serial qty is always 1
if item is in lot, lot qty should be calculated

4

Yeah, I'm still not understanding the logic so I'm folding on this one

5

did you understand how to get lot and serial items based on conditions from tables?
Which part you didn't understand?

6

Did the query I supplied join correct?
Are you looking for a single query or 4?
From the data you supplied, what should be the output?

7

Single query is need. Item validity check based on INVENTORY_ITEM_STATUS_CODE = active means item is valid in stock Output should have lot items, serial items , items in both lot and serial , items not in both lot and serial in stock . Lot qty - count of number of lots in that item Serial qty- will be always 1.

8

Hi,
I got query for the task.
--pure lot

select a.ORGANIZATION_ID,a.SUBINVENTORY_CODE,LOCATOR_ID,SEGMENT1 ITEM_NAME,
a.INVENTORY_ITEM_ID ITEM_NUMBER, (select sum(PRIMARY_TRANSACTION_QUANTITY) from mtl_onhand_quantities_detail
where INVENTORY_ITEM_ID=a.INVENTORY_ITEM_ID
and ORGANIZATION_ID=a.ORGANIZATION_ID) ONHAND_QTY,LOT_NUMBER,'' as SERIAL_NUMBER,b.PRIMARY_UOM_CODE UOM,
PRIMARY_TRANSACTION_QUANTITY LOT_QTYSERIAL_QTY
from mtl_onhand_quantities_detail a,mtl_system_items_b b
where 1 = 1
AND a.ORGANIZATION_ID=b.ORGANIZATION_ID and
a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID
and b.LOT_CONTROL_CODE<>1 and a.ORGANIZATION_ID=141
and a.INVENTORY_ITEM_ID not in(select INVENTORY_ITEM_ID from mtl_serial_numbers c where SERIAL_NUMBER is not null
and CURRENT_ORGANIZATION_ID=141)

union
---pureserial
select distinct
a.ORGANIZATION_ID,a.SUBINVENTORY_CODE,a.LOCATOR_ID,b.SEGMENT1 ITEM_NAME,
b.INVENTORY_ITEM_ID ITEM_NUMBER,(select sum(primary_transaction_quantity)
from mtl_onhand_quantities_detail
where 1 = 1
AND organization_id=b.organization_id
and inventory_item_id=b.inventory_item_id
) ONHAND_QTY,
a.LOT_NUMBER,
sn.SERIAL_NUMBER,
b.PRIMARY_UOM_CODE UOM,
1 as LOT_QTYSERIAL_QTY
from mtl_onhand_quantities_detail a,mtl_system_items_b b,mtl_serial_numbers sn
where a.ORGANIZATION_ID=b.ORGANIZATION_ID
and a.INVENTORY_ITEM_ID =b.INVENTORY_ITEM_ID
and b.ORGANIZATION_ID=sn.CURRENT_ORGANIZATION_ID
and b.INVENTORY_ITEM_ID =sn.INVENTORY_ITEM_ID
and b.INVENTORY_ITEM_ID=sn.INVENTORY_ITEM_ID
and b.ORGANIZATION_ID=sn.CURRENT_ORGANIZATION_ID
and b.SERIAL_NUMBER_CONTROL_CODE<>1
and isnull (a.locator_id, 0) = isnull (sn.current_locator_id, 0)
and b.ORGANIZATION_ID=141
and sn.LOT_NUMBER is null
and b.INVENTORY_ITEM_ID not in(select INVENTORY_ITEM_ID from mtl_lot_numbers where ORGANIZATION_ID=141)
and sn.CURRENT_STATUS=3
and a.subinventory_code=sn.current_subinventory_code
union
---bothlot&serial
select a.ORGANIZATION_ID,b.SUBINVENTORY_CODE,b.LOCATOR_ID ,a.segment1 ITEM_NAME,
a.inventory_item_id ITEM_NUMBER ,
(select sum(primary_transaction_quantity)
from mtl_onhand_quantities_detail
where 1 = 1
AND organization_id=b.organization_id
and inventory_item_id=b.inventory_item_id
) ONHAND_QTY,b.LOT_NUMBER, sn. serial_number , a.primary_uom_code UOM, 1 LOT_QTYSERIAL_QTY
--b.primary_transaction_quantity
from
mtl_system_items_b a ,mtl_onhand_quantities_detail b,mtl_serial_numbers sn where a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID
and a.INVENTORY_ITEM_ID=sn.INVENTORY_ITEM_ID and b.ORGANIZATION_ID=141 and
a.ORGANIZATION_ID=b.ORGANIZATION_ID and a.ORGANIZATION_ID=sn.CURRENT_ORGANIZATION_ID and
a.serial_number_control_code <> 1
and
b.lot_number is not NULL and sn.current_status = 3 and
b.LOT_NUMBER=sn.LOT_NUMBER and
isnull (b.locator_id, 0) = isnull (sn.current_locator_id, 0) and
b.subinventory_code=sn.current_subinventory_code
union
--notin both
select a.ORGANIZATION_ID,a.SUBINVENTORY_CODE,LOCATOR_ID,SEGMENT1 ITEM_NAME,
a.INVENTORY_ITEM_ID ITEM_NUMBER, (select sum(PRIMARY_TRANSACTION_QUANTITY) from mtl_onhand_quantities_detail
where a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID
and a.ORGANIZATION_ID=b.ORGANIZATION_ID) ONHAND_QTY, '' as LOT_NUMBER,'' as SERIAL_NUMBER,b.PRIMARY_UOM_CODE UOM,
PRIMARY_TRANSACTION_QUANTITY LOT_QTYSERIAL_QTY
from mtl_onhand_quantities_detail a,mtl_system_items_b b
where 1 = 1
AND a.ORGANIZATION_ID=b.ORGANIZATION_ID and
a.INVENTORY_ITEM_ID=b.INVENTORY_ITEM_ID and a.ORGANIZATION_ID=141
and b.LOT_CONTROL_CODE=1
and b.SERIAL_NUMBER_CONTROL_CODE=1

Thanks.