ONT DIFOT

Description
Categories: Draft, Toolkit - Operations
Columns: Customer, Account Number, Order Number, Quote Number, Source Type, Source Document, Type, Order Type, Customer Po, Ship To Customer ...
Sales order header and line details with additional logic to measure the order shipping performance metric: delivered in full and on time (DIFOT).
select
x.customer,
x.account_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_account,
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,
hp2.party_name bill_to_customer,
hca2.account_number bill_to_account,
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,
x.ordered_date,
trunc(x.promise_date) - trunc(x.actual_shipment_date+delivery_lead_time) On_time_or_late,
x.price_list,
x.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.warehouse,
x.ship_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.item,
x.description,
x.item_type,
x.quantity,
x.uom,
x.unit_selling_price,
x.extended_price,
x.list_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.shipped_quantity-x.quantity shipped_in_full_when_zero,
x.delivery_lead_time,
x.shipment_priority,
x.shippable_flag,
x.ship_set,
x.delivery,
x.project,
x.task,
x.created_by,
x.creation_date,
x.last_updated_by,
x.last_update_date,
x.order_category,
x.line_category,
x.operating_unit,
x.header_id,
x.line_id,
x.line_number
from
(
select
hp.party_name customer,
hca.account_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,
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 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,
(select mp.organization_code from mtl_parameters mp where nvl(oola.ship_from_org_id,ooha.ship_from_org_id)=mp.organization_id) warehouse,
xxen_util.meaning(nvl(oola.shipping_method_code,ooha.shipping_method_code),'SHIP_METHOD',3) ship_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_selling_price,
oola.extended_price,
oola.unit_list_price list_price,
oola.delivery_lead_time,
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,
ppa.project_number project,
pt.task_number task,
xxen_util.user_name(oola.created_by) created_by,
xxen_util.client_time(oola.creation_date) creation_date,
xxen_util.user_name(oola.last_updated_by) last_updated_by,
xxen_util.client_time(oola.last_update_date) 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,
haouv.name operating_unit,
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
from
hr_all_organization_units_vl haouv,
oe_order_headers_all ooha,
(
select
decode(oola.line_category_code,'RETURN',-1,1)*oola.unit_selling_price*oola.ordered_quantity extended_price,
decode(oola.line_category_code,'RETURN',-1,1)*oola.tax_value tax_amount,
(
select
sum(decode(opa.credit_or_charge_flag,'C',-1,1)*decode(opa.arithmetic_operator,'LUMPSUM',case when oola.orde