JYB新品发货-新品基本信息及发货数量、发货时间SQL

新品发货信息说明：通过产品的创建时间查询出新品基本信息、发货信息及发货时间。

查询条件：产品的创建时间、新品编码

1.查询新品的基本信息：

select msi.inventory_item_id 成品id,
       msi.segment1 成品编码,
       msi.description 成品说明,
       msi.creation_date 成品创建日期,
       xxxxx_category_new(mc.category_id, 1) 成品大类
  from mtl_system_items_b msi, mtl_item_categories mic, mtl_categories mc

 where 1 = 1
   and (msi.segment1 like 'C%' or msi.segment1 like 'W.%')
   and msi.organization_id = 102
      --and msi.creation_date >= trunc(:p_date_start)
      --and msi.creation_date < trunc(:p_date_end + 1)
   and msi.creation_date >= trunc(sysdate - 60)
   and msi.creation_date < trunc(sysdate + 1)
   and msi.segment1 not like 'C.DP%'
   and msi.segment1 not like 'W.DP%'
   and msi.segment1 not like 'C.DZ%'
   and msi.segment1 not like 'W.DZ%'
   and msi.segment1 not like 'C.QT%'
   and msi.segment1 not like 'W.QT%'
      
   and msi.inventory_item_id = mic.inventory_item_id(+)
   and mic.category_set_id(+) = 1100000081
   and mic.organization_id(+) = 102
   and mic.category_id = mc.category_id(+)

 2.查询新品的发货数量和发货日期：

select ool.inventory_item_id,
       nvl(ool.invoiced_quantity, ool.shipped_quantity) 发货数量,
       nvl(ool.actual_shipment_date, ool.last_update_date) 发货日期
  from oe_order_lines_all ool, oe_order_headers_all ooh, mtl_system_items_b msi

 where 1 = 1
      --and nvl(ool.actual_shipment_date, ool.last_update_date) >= trunc(:p_date_start)
   and nvl(ool.actual_shipment_date, ool.last_update_date) >=
       trunc(sysdate - 30)
   and ool.invoice_interface_status_code = 'YES'
   and ool.flow_status_code = 'CLOSED'
   and ool.open_flag = 'N'
      
   and ooh.header_id = ool.header_id
   and (ooh.order_type_id in (1002, 1005) or ooh.order_number like '2%')
      
   and ool.inventory_item_id = msi.inventory_item_id
   and msi.organization_id = 102
      --and msi.creation_date >= trunc(:p_date_start)
      --and msi.creation_date < trunc(:p_date_end + 1)
   and msi.creation_date >= trunc(sysdate - 30)
   and msi.creation_date < trunc(sysdate + 1)