ONT Orders and Lines

Description
Categories: Enginatics
Repository: Github
Detail Sales Order or Quote header report with line item details including status, cost, project and shipping information.
Run ONT Orders and Lines and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.operating_unit,
x.customer,
x.customer_number,
x.order_number,
x.quote_number,
x.source_type,
x.source_document,
x.type,
x.order_type,
x.customer_po,
hp1.party_name ship_to_customer,
hca1.account_number ship_to_customer_number,
hcsua1.location ship_to_location,
(select hz_format_pub.format_address(hps1.location_id,null,null,' , ') from dual) ship_to_address,
ftv1.territory_short_name ship_to_country,
coalesce(hcsua1.tax_reference,hp1.tax_reference,hp1.jgzz_fiscal_code) ship_to_tax_reference,
hp2.party_name bill_to_customer,
hca2.account_number bill_to_customer_number,
hcsua2.location bill_to_location,
(select hz_format_pub.format_address(hps2.location_id,null,null,' , ') from dual) bill_to_address,
ftv2.territory_short_name bill_to_country,
coalesce(hcsua2.tax_reference,hp2.tax_reference,hp2.jgzz_fiscal_code) bill_to_tax_reference,
x.ordered_date,
x.price_list,
x.salesperson,
x.invoice_salesperson,
x.order_source,
x.order_source_reference,
x.header_status,
x.currency,
x.subtotal,
x.tax,
nvl(x.line_charges_total,0)+nvl(x.header_charges,0) charges,
nvl(x.subtotal,0)+nvl(x.tax,0)+nvl(x.line_charges_total,0)+nvl(x.header_charges,0) total,
x.payment_terms,
x.invoice_class,
x.invoice_type,
x.invoice_number,
x.invoice_date,
x.invoice_gl_date,
x.invoice_status,
x.invoice_line,
x.invoice_amount,
x.invoice_currency,
x.invoice_accounted_amount,
x.warehouse,
x.shipping_method,
x.line_set,
x.freight_terms,
x.fob,
x.shipment_priority,
x.shipping_instructions,
x.packing_instructions,
x.payment_type,
x.line,
x.line_type,
x.line_status,
x.cancelled_flag,
x.cancel_date,
x.cancelled_by,
x.cancel_reason,
x.cancelled_quantity,
x.cancelled_amount,
x.item,
x.description,
x.item_type,
&category_columns
x.quantity,
x.uom,
x.list_price,
x.discounted_price,
x.unit_selling_price,
x.extended_price,
x.line_charges,
x.tax_code,
x.tax_amount,
x.calculate_price_flag,
x.pricing_quantity,
x.pricing_uom,
x.pricing_date,
x.request_date,
x.promise_date,
x.schedule_ship_date,
x.actual_shipment_date,
x.shipped_quantity,
x.shippable_flag,
x.ship_set,
x.delivery,
nvl2(x.next_step,x.next_step,decode(x.next_step_code,
'IT','Run Interfaces',
'NA','Not Applicable',
'PC','Transact Move Order',
'PO','Progress Order to Awaiting Shipping',
'PR','Pick Release',
'RC','Replenishment Complete',
'RX','Receive Crossdocked Supply',
'SC','Ship Confirm/Close Trip Stop'
)) next_step,
x.project,
x.task,
&dff_columns2
x.header_created_by,
x.header_creation_date,
x.header_last_updated_by,
x.header_last_update_date,
x.line_created_by,
x.line_creation_date,
x.line_last_updated_by,
x.line_last_update_date,
x.order_category,
x.line_category,
x.header_id,
x.line_id,
x.line_number,
x.line_days_late,
x.line_quantity_short,
xxen_util.meaning(x.line_shipped_flag,'YES_NO',0) line_shipped,
x.order_date_type_code,
x.split_line,
x.orig_line_id,
x.orig_line_promise_date,
x.orig_line_quantity,
x.orig_line_delivery_lead_time,
x.orig_line_actual_shipment_date,
x.orig_line_tot_shipped_quantity,
nvl(xxen_util.meaning(x.deliv_in_full,'YES_NO',0),'N/A') line_dif,
nvl(xxen_util.meaning(x.deliv_on_time,'YES_NO',0),'N/A') line_dot,
nvl(xxen_util.meaning(decode(x.deliv_in_full||x.deliv_on_time,null,null,'YY','Y','N'),'YES_NO',0),'N/A') line_difot,
nvl(xxen_util.meaning(min(x.deliv_in_full) over (partition by x.header_id),'YES_NO',0),'N/A') orders_dif,
nvl(xxen_util.meaning(min(x.deliv_on_time) over (partition by x.header_id),'YES_NO',0),'N/A') orders_dot,
nvl(xxen_util.meaning(decode(min(x.deliv_in_full) over (partition by x.header_id)||min(x.deliv_on_time) over (partition by x.header_id),null,null,'YY','Y','N'),'YES_NO',0),'N/A') orders_difot
from
(
select /*+ push_pred(oolh) push_pred(oolh2) push_pred(wda) */ distinct
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,
(select qlhv.name from qp_list_headers_vl qlhv where ooha.price_list_id=qlhv.list_header_id) price_list,
jrrev.resource_name salesperson,
max(jrrev2.resource_name) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_salesperson,
oos.name order_source,
ooha.orig_sys_document_ref order_source_reference,
xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) header_status,
ooha.transactional_curr_code currency,
sum(decode(oola.cancelled_flag,'N',oola.extended_price)) over (partition by oola.header_id) subtotal,
sum(decode(oola.cancelled_flag,'N',oola.tax_amount)) over (partition by oola.header_id) tax,
sum(decode(oola.cancelled_flag,'N',oola.line_charges)) over (partition by oola.header_id) line_charges_total,
(select sum(decode(opa.credit_or_charge_flag,'C',-1,1)*opa.operand) from oe_price_adjustments opa where ooha.header_id=opa.header_id and opa.line_id is null and opa.list_line_type_code='FREIGHT_CHARGE' and opa.applied_flag='Y') header_charges,
(select rtv.name from ra_terms_vl rtv where nvl(oola.payment_term_id,ooha.payment_term_id)=rtv.term_id) payment_terms,
xxen_util.meaning(max(rctta.type) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6),'INV/CM/ADJ',222) invoice_class,
max(rctta.name) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_type,
max(rcta.trx_number) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_number,
max(rcta.trx_date) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_date,
max(rctlgda0.gl_date) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_gl_date,
xxen_util.meaning(max(rcta.status_trx) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6),'PAYMENT_SCHEDULE_STATUS',222) invoice_status,
listagg(decode(rctla.line_type,'FREIGHT',null,rctla.line_number),', ') within group (order by decode(rctla.line_type,'FREIGHT',null,rctla.line_number)) over (partition by rctla.interface_line_attribute6) invoice_line,
sum(rctla.extended_amount) over (partition by rctla.interface_line_attribute6) invoice_amount,
max(rcta.invoice_currency_code) keep (dense_rank last order by rcta.customer_trx_id) over (partition by rctla.interface_line_attribute6) invoice_currency,
sum(rctlgda.acctd_amount) over (partition by rctla.interface_line_attribute6) invoice_accounted_amount,
(select mp.organization_code from mtl_parameters mp where nvl(oola.ship_from_org_id,ooha.ship_from_org_id)=mp.organization_id) warehouse,
(select wcv.carrier_name from wsh_carriers_v wcv where oola.freight_carrier_code=wcv.freight_code) freight_carrier,
xxen_util.meaning(nvl(oola.shipping_method_code,ooha.shipping_method_code),'SHIP_METHOD',3) shipping_method,
xxen_util.meaning(ooha.customer_preference_set_code,'REQUEST_DATE_TYPE',660) line_set,
xxen_util.meaning(nvl(oola.freight_terms_code,ooha.freight_terms_code),'FREIGHT_TERMS',660) freight_terms,
xxen_util.meaning(nvl(oola.fob_point_code,ooha.fob_point_code),'FOB',222) fob,
xxen_util.meaning(nvl(oola.shipment_priority_code,ooha.shipment_priority_code),'SHIPMENT_PRIORITY',660) shipment_priority,
nvl(oola.shipping_instructions,ooha.shipping_instructions) shipping_instructions,
nvl(oola.packing_instructions,ooha.packing_instructions) packing_instructions,
xxen_util.meaning(nvl(oola.payment_type_code,ooha.payment_type_code),'PAYMENT TYPE',660) payment_type,
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 quantity,
oola.order_quantity_uom uom,
oola.unit_list_price list_price,
oola.discounted_price,
oola.unit_selling_price,
oola.extended_price,
oola.line_charges,
oola.tax_code,
oola.tax_amount,
xxen_util.meaning(oola.calculate_price_flag,'CALCULATE_PRICE_FLAG',660) calculate_price_flag,
oola.pricing_quantity,
oola.pricing_quantity_uom pricing_uom,
oola.pricing_date,
xxen_util.client_time(oola.request_date) request_date,
xxen_util.client_time(oola.promise_date) promise_date,
xxen_util.client_time(oola.schedule_ship_date) schedule_ship_date,
xxen_util.client_time(oola.actual_shipment_date) actual_shipment_date,
oola.shipped_quantity,
xxen_util.meaning(decode(oola.shippable_flag,'Y','Y'),'YES_NO',0) 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,
wnd.name delivery,
wda.next_step_code,
xxen_util.meaning(wda.next_step_code,'NEXT_STEP',665) next_step,
xxen_util.meaning(case when ooha.cancelled_flag='Y' or oola.cancelled_flag='Y' then 'Y' end,'YES_NO',0) cancelled_flag,
xxen_util.client_time(oolh.hist_creation_date) cancel_date,
xxen_util.user_name(oolh.hist_created_by) cancelled_by,
xxen_util.meaning(oer.reason_code,'CANCEL_CODE',660) cancel_reason,
decode(oola.cancelled_flag,'Y',oola.cancelled_quantity) cancelled_quantity,
decode(oola.cancelled_flag,'Y',oola.cancelled_quantity)*oola.unit_selling_price cancelled_amount,
ppa.project_number project,
pt.task_number task,
&dff_columns
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_number,
oola.shipment_number,
oola.option_number,
oola.component_number,
oola.service_number,
oola.line_id,
nvl(oola.ship_to_org_id,ooha.ship_to_org_id) ship_to_org_id,
nvl(oola.invoice_to_org_id,ooha.invoice_to_org_id) invoice_to_org_id,
ooha.order_date_type_code,
oola.split_line,
oola.orig_line_id_ orig_line_id,
case
when oola.ordered_quantity<=0 or oola.is_shippable='N' then null
when oola.shipped_quantity<0 then 'Y'
else 'N'
end line_shipped_flag,
case when oola.ordered_quantity>0 and oola.is_shippable='Y' then greatest(trunc(decode(nvl(ooha.order_date_type_code,'SHIP'),'SHIP',nvl(oola.actual_shipment_date,sysdate),nvl(oola.actual_shipment_date,sysdate) + nvl(oola.delivery_lead_time,0)))-trunc(oola.promise_date),0) end line_days_late,
case when oola.ordered_quantity>0 and oola.is_shippable='Y' then greatest(oola.ordered_quantity-nvl(oola.shipped_quantity,0),0) end line_quantity_short,
coalesce(oolh2.delivery_lead_time,oola2.delivery_lead_time,oola.delivery_lead_time,0) orig_line_delivery_lead_time,
xxen_util.client_time(trunc(coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date))) orig_line_promise_date,
sum(oola.ordered_quantity) over (partition by oola.orig_line_id_) orig_line_quantity,
xxen_util.client_time(trunc(coalesce(oolh2.actual_shipment_date,oola2.actual_shipment_date,oola.actual_shipment_date))) orig_line_actual_shipment_date,
sum(oola.shipped_quantity) over (partition by oola.orig_line_id_) orig_line_tot_shipped_quantity,
case
when sum(oola.ordered_quantity) over (partition by oola.orig_line_id_)<=0 or oola.is_shippable='N' then null
when sum(oola.ordered_quantity) over (partition by oola.orig_line_id_)<=nvl(sum(oola.shipped_quantity) over (partition by oola.orig_line_id_),0) then 'Y'
else 'N'
end deliv_in_full,
case
when sum(oola.ordered_quantity) over (partition by oola.orig_line_id_)<=0 or oola.is_shippable='N' then null
when trunc(coalesce(oolh2.actual_shipment_date,oola2.actual_shipment_date,oola.actual_shipment_date,coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date)+1))
     + decode(nvl(ooha.order_date_type_code,'SHIP'),'SHIP',0,nvl(coalesce(oolh2.delivery_lead_time,oola2.delivery_lead_time,oola.delivery_lead_time),0))
     <= trunc(coalesce(oolh2.promise_date,oola2.promise_date,oola.promise_date))
then 'Y'
else 'N