select
null action_,
null status_,
null message_,
haouv.name operating_unit,
hp.party_name customer,
hca.account_number customer_number,
ooha.order_number,
nvl(ooha.quote_number,regexp_substr(ooha.orig_sys_document_ref,'^(\d+).',1,1,null,1)) quote_number,
decode(ooha.source_document_type_id,10,'Requisitions',2,'Orders',16,'Quotes',7,'Incidents',(select oos0.name from oe_order_sources oos0 where ooha.source_document_type_id=oos0.order_source_id)) source_type,
case ooha.source_document_type_id
when 10 then (select prha.segment1 from po_requisition_headers_all prha where ooha.source_document_id=prha.requisition_header_id)
when 2 then (select to_char(ooha0.order_number) from oe_order_headers_all ooha0 where ooha.source_document_id=ooha0.header_id)
when 16 then (select aqha.quote_number||':'||aqha.quote_version from aso_quote_headers_all aqha where ooha.source_document_id=aqha.quote_header_id)
when 7 then (select ciab.incident_number from cs_incidents_all_b ciab where ooha.source_document_id=ciab.incident_id)
end source_document,
decode(ooha.transaction_phase_code,'N','Quote','Order') type,
ottt.name order_type,
nvl(oola.cust_po_number,ooha.cust_po_number) customer_po,
xxen_util.client_time(ooha.ordered_date) ordered_date,
oos.name order_source,
xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) header_status,
ooha.transactional_curr_code currency,
rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') line,
ottt2.name line_type,
xxen_util.meaning(oola.flow_status_code,'LINE_FLOW_STATUS',660) line_status,
msiv.concatenated_segments item,
msiv.description,
xxen_util.meaning(oola.item_type_code,'ITEM_TYPE',660) item_type,
-- Order Header DFF Attributes
xxen_util.display_flexfield_context(660,'OE_HEADER_ATTRIBUTES',ooha.context) order_header_dff_context,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE1',ooha.rowid,ooha.attribute1) order_header_attribute1,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE2',ooha.rowid,ooha.attribute2) order_header_attribute2,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE3',ooha.rowid,ooha.attribute3) order_header_attribute3,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE4',ooha.rowid,ooha.attribute4) order_header_attribute4,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE5',ooha.rowid,ooha.attribute5) order_header_attribute5,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE6',ooha.rowid,ooha.attribute6) order_header_attribute6,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE7',ooha.rowid,ooha.attribute7) order_header_attribute7,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE8',ooha.rowid,ooha.attribute8) order_header_attribute8,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE9',ooha.rowid,ooha.attribute9) order_header_attribute9,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE10',ooha.rowid,ooha.attribute10) order_header_attribute10,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE11',ooha.rowid,ooha.attribute11) order_header_attribute11,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE12',ooha.rowid,ooha.attribute12) order_header_attribute12,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE13',ooha.rowid,ooha.attribute13) order_header_attribute13,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE14',ooha.rowid,ooha.attribute14) order_header_attribute14,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE15',ooha.rowid,ooha.attribute15) order_header_attribute15,
-- Order Header TP Attributes
xxen_util.display_flexfield_context(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context) order_header_tp_context,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE1',ooha.rowid,ooha.tp_attribute1) order_header_tp_attribute1,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE2',ooha.rowid,ooha.tp_attribute2) order_header_tp_attribute2,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE3',ooha.rowid,ooha.tp_attribute3) order_header_tp_attribute3,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE4',ooha.rowid,ooha.tp_attribute4) order_header_tp_attribute4,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE5',ooha.rowid,ooha.tp_attribute5) order_header_tp_attribute5,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE6',ooha.rowid,ooha.tp_attribute6) order_header_tp_attribute6,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE7',ooha.rowid,ooha.tp_attribute7) order_header_tp_attribute7,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE8',ooha.rowid,ooha.tp_attribute8) order_header_tp_attribute8,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE9',ooha.rowid,ooha.tp_attribute9) order_header_tp_attribute9,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE10',ooha.rowid,ooha.tp_attribute10) order_header_tp_attribute10,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE11',ooha.rowid,ooha.tp_attribute11) order_header_tp_attribute11,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE12',ooha.rowid,ooha.tp_attribute12) order_header_tp_attribute12,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE13',ooha.rowid,ooha.tp_attribute13) order_header_tp_attribute13,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE14',ooha.rowid,ooha.tp_attribute14) order_header_tp_attribute14,
xxen_util.display_flexfield_value(660,'OE_HEADER_TP_ATTRIBUTES',ooha.tp_context,'TP_ATTRIBUTE15',ooha.rowid,ooha.tp_attribute15) order_header_tp_attribute15,
xxen_util.user_name(ooha.created_by) header_created_by,
xxen_util.client_time(ooha.creation_date) header_creation_date,
xxen_util.user_name(ooha.last_updated_by) header_last_updated_by,
xxen_util.client_time(ooha.last_update_date) header_last_update_date,
xxen_util.user_name(oola.created_by) line_created_by,
xxen_util.client_time(oola.creation_date) line_creation_date,
xxen_util.user_name(oola.last_updated_by) line_last_updated_by,
xxen_util.client_time(oola.last_update_date) line_last_update_date,
xxen_util.meaning(ooha.order_category_code,'ORDER_CATEGORY',660) order_category,
xxen_util.meaning(oola.line_category_code,'ORDER_CATEGORY',660) line_category,
ooha.header_id,
oola.line_id
from
hr_all_organization_units_vl haouv,
oe_order_headers_all ooha,
oe_transaction_types_tl ottt,
oe_transaction_types_tl ottt2,
mtl_system_items_vl msiv,
hz_cust_accounts hca,
hz_parties hp,
oe_order_sources oos,
oe_order_lines_all oola
where
1=1 and
2=2 and
3=3 and
4=4 and
ooha.order_number=50004 and
haouv.organization_id=ooha.org_id and
ooha.sold_to_org_id=hca.cust_account_id(+) and
hca.party_id=hp.party_id(+) and
ooha.order_type_id=ottt.transaction_type_id(+) and
ottt.language(+)=userenv('lang') and
ooha.order_source_id=oos.order_source_id(+) and
ooha.header_id=oola.header_id(+) and
oola.line_type_id=ottt2.transaction_type_id(+) and
ottt2.language(+)=userenv('lang') and
oola.inventory_item_id=msiv.inventory_item_id(+) and
oola.ship_from_org_id=msiv.organization_id(+)
order by
haouv.name,
hp.party_name,
ooha.order_number,
rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') |
