oracle園地

1.取料號.單位及版本

  SELECT msi.inventory_item_id, msi.primary_uom_code,MAX(mir.revision) max_revision
           INTO v_inv_id, v_item_unit,v_revision
           FROM mtl_system_items_b msi,mtl_item_revisions_b mir
          WHERE msi.organization_id = 127
          AND msi.segment1 = temp_rec.items
          AND msi.inventory_item_id=mir.inventory_item_id
          AND mir.organization_id = 127
           GROUP BY  msi.inventory_item_id, msi.primary_uom_code;

2.找ON_HAND
 SELECT LOT_NUMBER ,TRANSACTION_QUANTITY
      INTO V_LOT_NUMBER,V_TRANSACTION_QUANTITY
      FROM MTL_ONHAND_QUANTITIES 
    WHERE INVENTORY_ITEM_ID=V_INV_ID
    AND ORGANIZATION_ID=127
    AND SUBINVENTORY_CODE=V_SUB_CODE
    AND LOT_NUMBER=V_LOT
   -- AND TRANSACTION_QUANTITY=V_QTY
    AND LOCATOR_ID=V_INVENTORY_LOCATION_ID_GET;
       EXCEPTION
       WHEN NO_DATA_FOUND THEN
             MESSAGE('找不到LOT名稱['|| V_LOT ||']，匯入動作已取消，請檢查!');
             RAISE EX_LOT_NUMBER;
    WHEN OTHERS THEN
      NULL;
      END; 

  3.
 SELECT LOT_NUMBER ,TRANSACTION_QUANTITY,CREATE_TRANSACTION_ID
         INTO V_LOT_NUMBER,V_TRANSACTION_QUANTITY,V_CREATE_TRANSACTION_ID
         FROM MTL_ONHAND_QUANTITIES 
       WHERE INVENTORY_ITEM_ID=V_INV_ID
       AND ORGANIZATION_ID=127
       AND SUBINVENTORY_CODE=V_SUB_CODE
       AND LOT_NUMBER=V_LOT;