1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to find onhand quantity based on lot and serial

Discussion in 'SQL PL/SQL' started by prabhur, Apr 13, 2017.

  1. prabhur

    prabhur Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    [​IMG]



    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

    With logic below
    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
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    666
    Likes Received:
    136
    Trophy Points:
    810
    Location:
    Russian Federation
    What is your problem ?
    Are you learning the sql?

    Can you do it :
    1) Provide here the scripts to create your tables and scripts to populate your tables
    2) Provide an example of your query and describe your problem and we will help you
    ?
     
    Last edited: Apr 13, 2017
  3. prabhur

    prabhur Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Hi,
    I have attached script for generating database with table. I need
    Output columns as below
    --------------------------------

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


    By using below logic on table

    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
     

    Attached Files:

  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,453
    Likes Received:
    354
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is a script for SQL Server and it is nothing like Oracle. Are you using Oracle for this? Or do you mistakenly think that all databases are the same and one answer fits all? What we could tell you here wouldn't apply to SQL Server as the command syntax for SQL*Plus and T-SQL are different.

    You should post this to a SQL Server forum; we can do nothing for you here.
     
  5. prabhur

    prabhur Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Ok .Thanks.