Scribd Logo
Upload

Welcome to Scribd!

  • 105 views

    Query Field Mapping

    Uploaded by

    narasimha4u11
    Reid has requested a PSQuery to provide details about lot-controlled items that will expire soon. This will help warehouses utilize expiring items first and save costs. The query will return 14 fields including item number, description, lot ID, expiration date, quantity due to expire, bin location, unit cost and value of quantity due to expire. It will join four tables and include filters to return data only for the specified business unit and item. A second query will calculate the average monthly issue quantity for that item over the past three years.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd

    Query Field Mapping

    Uploaded by

    narasimha4u11
    105 views2 pages
    Reid has requested a PSQuery to provide details about lot-controlled items that will expire soon. This will help warehouses utilize expiring items first and save costs. The query will return 14 fields including item number, description, lot ID, expiration date, quantity due to expire, bin location, unit cost and value of quantity due to expire. It will join four tables and include filters to return data only for the specified business unit and item. A second query will calculate the average monthly issue quantity for that item over the past three years.

    Original Title

    Query_Field_Mapping

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Reid has requested a PSQuery to provide details about lot-controlled items that will expire soon. This will help warehouses utilize expiring items first and save costs. The query will return 14 fields including item number, description, lot ID, expiration date, quantity due to expire, bin location, unit cost and value of quantity due to expire. It will join four tables and include filters to return data only for the specified business unit and item. A second query will calculate the average monthly issue quantity for that item over the past three years.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    105 views2 pages

    Query Field Mapping

    Uploaded by

    narasimha4u11
    Reid has requested a PSQuery to provide details about lot-controlled items that will expire soon. This will help warehouses utilize expiring items first and save costs. The query will return 14 fields including item number, description, lot ID, expiration date, quantity due to expire, bin location, unit cost and value of quantity due to expire. It will join four tables and include filters to return data only for the specified business unit and item. A second query will calculate the average monthly issue quantity for that item over the past three years.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 2

    Request: Reid has requested one PSQuery that will give him below details on item expiration that

    will
    give him details about availability of lot controlled items that are going to expire. This query will help
    warehouses in utilizing the items first that are going to expire and will save cost.

    Below are the fields requested with record field mapping details.

    S.NO Field Name Database Field Record Name


    .
    1 Item# INV_ITEM_ID LOT_CONTROL_INV
    2 Description DESCR60 MASTER_ITEM_TBL
    3 Min REORDER_POINT BU_ITEMS_INV
    4 Max QTY_MAXIMUM BU_ITEMS_INV
    5 Lot id INV_LOT_ID LOT_CONTROL_INV
    6 Lot Expiration Date EXPIRATION_DATE LOT_CONTROL_INV
    7 Quantity due to expire QTY_ONHAND LOT_CONTROL_INV
    8 Bin Location STORAGE_AREA, PHYSICAL_INV
    STOR_LEVEL_1,
    STOR_LEVEL_2,
    STOR_LEVEL_3,
    STOR_LEVEL_4,
    9 Unit Of Measure STD_PACK_UOM BU_ITEMS_INV
    10 Unit Cost DFLT_ACTUAL_COST BU_ITEMS_INV
    11 Value due to expire QTY_ONHAND * LOT_CONTROL_INV,
    DFLT_ACTUAL_COST BU_ITEMS_INV
    12 Average monthly issue quantity
    13 Estimate of quantity that will expire
    before usage
    14 Value of estimated quantity to expire

    SELECT L.INV_ITEM_ID, M.DESCR60, B.REORDER_POINT, B.QTY_MAXIMUM, L.INV_LOT_ID,


    L.EXPIRATION_DATE, L.QTY_ONHAND, P.STORAGE_AREA, P.STOR_LEVEL_1, P.STOR_LEVEL_2,
    P.STOR_LEVEL_3, P.STOR_LEVEL_4, B.STD_PACK_UOM, B.DFLT_ACTUAL_COST, (L.QTY_ONHAND *
    B.DFLT_ACTUAL_COST) As Value_due_to_expire

    FROM PS_LOT_CONTROL_INV L, PS_MASTER_ITEM_TBL M, PS_BU_ITEMS_INV B, PS_PHYSICAL_INV P

    WHERE L.BUSINESS_UNIT = B.BUSINESS_UNIT

    AND L.INV_ITEM_ID = B.INV_ITEM_ID

    AND L.INV_ITEM_ID = M.INV_ITEM_ID

    AND M.SETID = 'SHARE'

    AND L.BUSINESS_UNIT = P.BUSINESS_UNIT


    AND L.INV_ITEM_ID = P.INV_ITEM_ID

    AND L.QTY_ONHAND = P.QTY

    AND L.BUSINESS_UNIT = '36001'

    AND L.INV_ITEM_ID = 'L00000000000000929'

    AND L.QTY_ONHAND <> 0

    WITH UR;

    SELECT (SUM(QTY_BASE)/12) FROM PS_TRANSACTION_INV WHERE BUSINESS_UNIT = '36001' AND


    INV_ITEM_ID = 'L00000000000000929' AND TRANSACTION_GROUP = '030' AND TRANSACTION_DATE <=
    CURRENT_DATE AND TRANSACTION_DATE >= CURRENT_DATE - 360 DAYS WITH UR;

    You might also like