serial number status

最新推荐文章于 2023-06-18 10:02:59 发布

王景远

最新推荐文章于 2023-06-18 10:02:59 发布

阅读量964

点赞数

分类专栏： PL/SQL

本文链接：https://blog.csdn.net/whhitgen/article/details/8333343

版权

PL/SQL 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

本文介绍了一个用于创建或替换名为 MTL_ONHAND_SERIAL_MWB_VAS 的视图的 SQL 语句，该视图整合了多个表的数据，包括 mtl_system_items_vl、mtl_item_locations_kfv、mtl_serial_numbers 和 mtl_parameters。通过这些数据，可以详细地追踪到每个库存物品的位置、状态等信息。

摘要由CSDN通过智能技术生成

CREATE OR REPLACE VIEW MTL_ONHAND_SERIAL_MWB_V AS
SELECT msn.current_organization_id organization_id,
       mp.organization_code organization_code,
       msn.current_subinventory_code subinventory_code,
       msn.current_locator_id locator_id,
       mil.concatenated_segments locator,
       msn.inventory_item_id inventory_item_id,
       msiv.description item_description,
       msiv.concatenated_segments item,
       msn.revision revision,
       msiv.primary_uom_code uom,
       1 on_hand,
       Decode(Nvl(msn.lpn_id, -1), -1, 1, 0) unpacked,
       Decode(Nvl(msn.lpn_id, -1), -1, 0, 1) packed,
       msn.cost_group_id cost_group_id,
       msn.lpn_id lpn_id,
       To_char(null) lpn,
       msn.lot_number lot_number,
       msn.serial_number serial_number,
       msn.end_item_unit_number unit_number,
       mil.project_id project_id,
       mil.task_id task_id,
       mil.status_id locator_status_id,
       msn.status_id serial_status_id,
       Decode(Nvl(msn.lpn_id, -1), -1, To_number(NULL), 1) containerized_flag,
       msn.planning_tp_type planning_tp_type,
       msn.planning_organization_id planning_organization_id,
       msn.owning_tp_type owning_tp_type,
       msn.owning_organization_id owning_organization_id,
       msiv.lot_control_code item_lot_control,
       msiv.serial_number_control_code item_serial_control
  FROM mtl_system_items_vl    msiv,
       mtl_item_locations_kfv mil,
       mtl_serial_numbers     msn,
       mtl_parameters         mp
 WHERE msn.current_organization_id = mp.organization_id
   AND msn.current_organization_id = msiv.organization_id
   AND msn.inventory_item_id = msiv.inventory_item_id
   AND msn.current_organization_id = mil.organization_id(+)
   AND msn.current_locator_id = mil.inventory_location_id(+)
   AND msn.current_status = 3
   AND nvl(msn.organization_type, 2) = 2

SELECT * FROM mfg_lookups WHERE lookup_type = 'SERIAL_NUM_STATUS';

SELECT *
  FROM inv.mtl_transaction_types
 WHERE transaction_type_id IN (18, 61, 15, 35, 87, 53, 52);

SELECT * FROM po_lookup_codes WHERE lookup_type = 'ORGANIZATION TYPE';