新品发货信息说明:通过产品的创建时间查询出新品基本信息、发货信息及发货时间。
查询条件:产品的创建时间、新品编码
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)