Reports 2017-11-18T12:27:27+00:00

ONT Order Line Details

Description
Categories: Enginatics, Logistics, Sales
Sales order line details

select
haou.name ou,
hca.account_number,
hp.party_name,
oos.name order_source,
decode(ooha.source_document_type_id,
10,'Requisitions',
2,'Orders',
16,'Quotes',
7,'Incidents'
) source_type,
decode(ooha.source_document_type_id,
10,prha.segment1,
2,ooha2.order_number,
16,aqha.quote_number||':'||aqha.quote_version,
7,ciab.incident_number) source_doc,
ooha.order_number,
ottt.name order_type,
xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) order_status,
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,
oola.ordered_quantity qty,
oola.order_quantity_uom uom,
nvl(decode(oola.unit_selling_price_per_pqty,9.99E125,null,oola.unit_selling_price_per_pqty),oola.unit_selling_price) unit_selling_price,
oola.ordered_quantity*nvl(decode(oola.unit_selling_price_per_pqty,9.99E125,null,oola.unit_selling_price_per_pqty),oola.unit_selling_price) extended_price,
oola.request_date,
oola.promise_date,
oola.schedule_ship_date,
oola.actual_shipment_date,
xxen_util.meaning(ooha.shipment_priority_code,'SHIPMENT_PRIORITY',660) shipment_priority,
decode(oola.shippable_flag,'Y','Y') shippable_flag,
(select distinct listagg(os.set_name,', ') within group (order by os.set_name) over (partition by oola.line_id) set_name from oe_sets os where oola.ship_set_id=os.set_id) ship_set_name,
wnd.name delivery,
ppa.project_number,
pt.task_number,
oola.creation_date,
xxen_util.user_name(oola.created_by) created_by,
ooha.header_id,
oola.line_id
from
hr_all_organization_units haou,
oe_order_headers_all ooha,
oe_order_lines_all oola,
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_headers_all ooha2,
po_requisition_headers_all prha,
aso_quote_headers_all aqha,
cs_incidents_all_b ciab,
(
select distinct
wdd.source_line_id,
min(wda.delivery_id) over (partition by wdd.source_line_id, wda.delivery_id) delivery_id
from
(select wdd.* from wsh_delivery_details wdd where wdd.source_code='OE') wdd,
wsh_delivery_assignments wda
where
wdd.delivery_detail_id=wda.delivery_detail_id
) wda,
wsh_new_deliveries wnd,
(
select ppa.project_id, ppa.segment1 project_number from pa_projects_all ppa union
select psm.project_id, psm.project_number from pjm_seiban_numbers psm
) ppa,
&xrrpv_table
pa_tasks pt
where
1=1 and
haou.organization_id=ooha.org_id and
haou.organization_id=oola.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.source_document_id=ooha2.header_id(+) and
ooha.source_document_id=prha.requisition_header_id(+) and
ooha.source_document_id=aqha.quote_header_id(+) and
ooha.source_document_id=ciab.incident_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(+) and
oola.line_id=wda.source_line_id(+) and
wda.delivery_id=wnd.delivery_id(+) and
oola.project_id=ppa.project_id(+) and
oola.task_id=pt.task_id(+)
order by
haou.name,
hca.account_number,
ooha.order_number,
oola.line_number,
oola.shipment_number,
nvl(oola.option_number,-1),
nvl(oola.component_number,-1),
nvl(oola.service_number,-1)

Parameter Name SQL text Validation
organization_id|inventory_item_id
(
select distinct
dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1) organization_id,
dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id
from
xxen_report_run_param_values xrrpv
where
xrrpv.run_id=:run_id
) xrrpv,
Order Number
ooha.order_number=:order_number
LOV
Customer Name
upper(hp.party_name) like upper(:customer_name)
LOV
Account Number
hca.account_number=:account_number
LOV
Order Type
ottt.name=:order_type
LOV
Line Type
ottt2.name=:line_type
LOV
Item
msiv.concatenated_segments like :item
LOV
Shippable Flag
oola.shippable_flag=:shippable_flag
LOV Oracle
Project
ppa.project_number=:project_number
LOV
Task Number
pt.task_number=:task_number
LOV
Schedule Ship Date From
oola.schedule_ship_date>=:schedule_ship_date_from
Date
Schedule Ship Date To
oola.schedule_ship_date<:schedule_ship_date_to+1
Date
Operating Unit
haou.name=:operating_unit
LOV
organization_id|inventory_item_id
xrrpv.organization_id=msiv.organization_id and
xrrpv.inventory_item_id=msiv.inventory_item_id