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)