訂單綜合查詢之sql

說明
1, BOM 是指bom筆數,1為正常,0為未建,2及以上為有重複,bom不正常在訂單裡無法發工單與出貨
2, inv扣帳 是指inv是否有扣帳, 大於0為正常, 0為未扣帳(庫存), 出貨後沒扣帳的要查原因並處理
3, 在ar介面 是指目前在ar的interfaced的筆數
4, 立ar 是指ar是否已立帳,大於0為正常, 0則為未立帳,如正式訂單已出貨正常的話要立ar或在ar介面,否則為異常
5, line type 為 ship only 的 不會立ar
6, replenish_to_order_flag不為Y無法撿料出貨的,要重copy模板.
7, 如item最後異動日在訂單建立日期之後,則可能會導至當前訂單未出貨的訂單無法出貨,無法出貨的時候要把訂單line取消後重建方可出貨
8. oe_intf與inv_intf 如出貨後依然為n,則是出貨不成功的,這時要用trip_name重run: Order Management Interface - SRS / Inventory Interface - SRS / Interface Trip Stop - SRS
9, RESERVABLE_TYPE 等於2,表示料號屬性的保留屬性沒打V, 等於1的方可保留. 能保留方可撿料與出貨.
10, 沒有ship no的說明還沒有做編排箱號[@more@]select oh.order_number,oh.org_id,oh.header_id,oh.transactional_curr_code curr,
oh.cust_po_number,
(SELECT count(*)
FROM BOM_BILL_OF_MATERIALS bom
WHERE 1=1
AND ASSEMBLY_ITEM_ID = ol.INVENTORY_ITEM_ID
and bom.ORGANIZATION_ID=nvl(ol.ship_from_org_id,ol.org_id)
) bom_flag,
(select count(*) from mtl_material_transactions mmT
WHERE 1=1
and mmt.TRANSACTION_REFERENCE=to_char(ol.HEADER_ID)
and mmt.TRX_SOURCE_LINE_ID=ol.LINE_ID
and mmt.ORGANIZATION_ID=nvl(ol.ship_from_org_id,ol.org_id)
and mmt.SOURCE_CODE='ORDER ENTRY'
) inv_flag,
(select count(*) from RA_INTERFACE_lines_all ril
where ril.INTERFACE_LINE_CONTEXT='ORDER ENTRY'
and ril.INTERFACE_LINE_ATTRIBUTE1=to_char(oh.order_number)
and ril.INTERFACE_LINE_ATTRIBUTE6=to_char(ol.line_id)
and ril.org_id=ol.org_id
) ri_flag,
(select count(*) from ra_customer_trx_lines_all rtl
where rtl.INTERFACE_LINE_ATTRIBUTE6=to_char(ol.line_id)
and rtl.sales_order=to_char(oh.order_number)
and rtl.INTERFACE_LINE_CONTEXT='ORDER ENTRY'
and rtl.org_id=ol.org_id
) ar_flag,
--gobo_get_ship_no(ol.line_id) ship_no,
ol.line_number,
ol.line_id,
lt.name line_type,
ol.inventory_item_id,
ol.ordered_item,
msi.segment1 fac_item,
ol.CREATION_DATE oeol_creation,
msi.last_update_date item_last_update,
MSI.INVENTORY_ITEM_STATUS_CODE item_status,
msi.REPLENISH_TO_ORDER_FLAG,
msi.ITEM_TYPE,
MSI.RESERVABLE_TYPE,
ol.ordered_quantity,
ol.shipped_quantity,
ol.unit_selling_price ,
ol.flow_status_code,
ol.SCHEDULE_SHIP_DATE,
ol.ACTUAL_SHIPMENT_DATE,
ol.ACTUAL_FULFILLMENT_DATE,
ol.FULFILLED_QUANTITY,
ol.FULFILLMENT_DATE,
gobo_get_ship_no(ol.line_id) ship_no,
wda.delivery_id,
wdd.delivery_detail_id,
wdd.subinventory pick_sub,
wdd.revision pick_rev,
wdd.released_status,
flv.MEANING pick_mean,
flv.description pick_desc,
wdd.OE_INTERFACED_FLAG,
wdd.INV_INTERFACED_FLAG,
wdd.picked_quantity,
wdt.name trip_name,
(select osf.organization_code||'_'||fh.order_number||'_'||fl.line_number||'.'||fl.shipment_number pi
from oe_ship_from_orgs_v osf,oe_order_headers_all fh,oe_order_lines_all fl
where osf.organization_id=fh.org_id
and fh.header_id=fl.header_id
and to_char(fl.line_id)=ol.attribute12
and to_char(fl.header_id)=ol.attribute11
) att_pi,
(select osf.organization_code||'_'||fh.order_number||'_'||fl.line_number||'.'||fl.shipment_number pi
from oe_ship_from_orgs_v osf,oe_order_headers_all fh ,otc_orders_all ooa,oe_order_lines_all fl
where osf.organization_id=fh.org_id
and fh.header_id=fl.header_id
and fl.line_id=ooa.line_id
and ol.orig_sys_line_ref=ooa.orig_sys_line_ref
and ol.orig_sys_document_ref=ooa.orig_sys_document_ref
--and ooa.dest_line=ol.line_id
) ooa_pi
from mtl_system_items_b msi,
FND_LOOKUP_VALUES_VL flv,
WSH_DELIVERABLE_TRIPS_V wdT,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
oe_order_lines_all ol,
oe_transaction_types_tl lt,
oe_order_headers_all oh
where 1=1
and msi.inventory_item_id=ol.inventory_item_id
and msi.organization_id=nvl(ol.ship_from_org_id,ol.org_id)
and msi.segment1=nvl(:p_item,msi.segment1)
and wda.delivery_detail_id(+)=wdd.delivery_detail_id
and flv.VIEW_APPLICATION_ID(+)=665
and flv.LOOKUP_TYPE(+)='PICK_STATUS'
and flv.lookup_code(+)=wdd.released_status
and wdD.DELIVERY_DETAIL_ID = wdT.DELIVERY_DETAIL_ID(+)
and wdd.source_line_id(+)=ol.line_id
and wdd.source_header_id(+)=ol.header_id
and ol.line_type_id = lt.transaction_type_id
--and upper(lt.name) not like '%SHIP%ONLY%'
AND lt.LANGUAGE = 'US'
and ol.line_number=nvl(:p_line_no,ol.line_number)
and ol.header_id=oh.header_id
and ol.org_id=oh.org_id
and oh.order_number=nvl(:p_order_number,-1)
and oh.org_id=nvl(:p_org_id,-1)
order by ol.LINE_NUMBER