ONT Order Upload

➜ Back to Library
Description

ONT Order Upload creates new sales orders, updates existing orders, adds lines, books orders and cancels orders in Oracle Order Management from Excel – via the standard Oracle public API. Each spreadsheet row is one order line, grouped under a shared order identifier.

When to use it

  • Mass-create new sales orders from a spreadsheet (one or many lines per order).
  • Add lines to existing orders in bulk.
  • Bulk-update header or line fields (dates, prices, addresses, shipping, salesrep, flexfields) on existing orders.
  • Book many entered orders at once.
  • Cancel orders (and all their lines) in bulk, with a recorded change reason.
  • Apply line-level discounts or price overrides with a proper price-adjustment audit trail.

Before you start

  • Blitz Report is installed and you are signed in to a responsibility with access to the operating unit.
  • The customer (and its account) exists, is active, and has ship-to/bill-to sites in the operating unit.
  • The item exists and is orderable; a valid order type and line type exist for the operating unit.

Step 1 – Choose a mode, set the parameters and download

In Blitz Report, open ONT Order Upload, choose an Upload Mode (Create, Update – the default – downloads existing orders to edit; Create gives an empty template), and set the parameters:

ParameterPurpose
Upload ModeCreate or Create, Update. Default is Create, Update.
Operating UnitThe operating unit; drives the customer/order/warehouse lists.
Order Number / Customer / Order Type / Order Status / Item / datesRestrict which existing orders are downloaded.
Order ActionPre-set the action (Book / Cancel) written into every downloaded row.

Run the upload to download and open the Excel file. Each order is automatically handled as a create or an update depending on whether it already exists.

Step 2 – Enter the orders

Each row is one order line; the Upload Order Identifier groups a single order’s lines. For a new order leave Order Number blank and put a unique reference (e.g. new1) in the identifier; repeat it on every line. On the header enter the Operating Unit, Customer, Order Type, Header Ship To and Header Bill To; on each line the Item, Ordered Quantity, UOM and Line Request Date.

Step 3 – Book or cancel (optional)

Set Order Action to Book to submit the order for booking, or to Cancel to cancel the order and all its lines (a Header Change Reason is required to cancel).

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and runs the upload’s validation, then saves the file. Correct anything it flags before continuing.

Step 5 – Upload and view the result

Back in Blitz Report, click Upload and select your saved file. This submits the Blitz Upload request, which creates, updates, books or cancels each order. When it finishes, a result report opens showing each row as success or error.

What’s produced

  • Created and updated sales orders with their lines; orders booked or cancelled as requested.
  • A result report listing every row with a status (success or error) and a message.

Common questions

How do I create a brand-new order?
Leave Order Number blank and put a unique reference (e.g. new1) in Upload Order Identifier; repeat that identifier on every line of the order.

How do I put several lines on one order?
Give all those rows the same Upload Order Identifier; the header columns are read from the first row.

How do I book an order?
Set Order Action to Book (you can pre-set it for the whole download via the Order Action parameter).

How do I cancel an order?
Set Order Action to Cancel and supply a Header Change Reason – this cancels the order and all its lines. There is no separate delete.

Why must I enter a Discount Modifier when I change the selling price?
A manual price override must be backed by a price adjustment so it’s auditable; the upload enforces this exactly as the Order Management form does.

Troubleshooting

MessageCauseWhat to do
Operating Unit not foundThe operating unit isn’t recognised or not accessible.Pick the Operating Unit from the dropdown; ensure your responsibility has access.
Header Change Reason is required when Order Action is CancelCancel attempted without a reason.Enter a Header Change Reason (and optionally comments).
Discount Modifier is required when Unit Selling Price differs from Unit List PriceA manual price override with no modifier.Choose a Discount Modifier, or set the selling price equal to the list price.
Tax Exempt Reason is required when Tax Handling is ExemptTax Handling set to Exempt without a reason.Fill in the corresponding Tax Exempt Reason.
Order-processing error (invalid item/customer/site)The Oracle API rejected the data.Read the Oracle error in the Message column, correct the field, and re-upload (the order is rolled back, so no partial data is left).
select
case when :order_action is not null then xxen_upload.action_meaning(xxen_upload.action_update) end action_,
case when :order_action is not null then xxen_upload.status_meaning(xxen_upload.status_new) end status_,
case when :order_action is not null then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) end message_,
null modified_columns_,
to_number(null) request_id_,
:debug_level debug_level,
haouv.name operating_unit,
-- Header Main Tab - Left Side
hp.party_name customer,
hca.account_number customer_number,
nvl(oola.cust_po_number,ooha.cust_po_number) customer_po,
(select hcsua.location from hz_cust_site_uses_all hcsua where hcsua.site_use_id=ooha.ship_to_org_id) header_ship_to,
(select hcsua.location from hz_cust_site_uses_all hcsua where hcsua.site_use_id=ooha.invoice_to_org_id) header_bill_to,
-- Header Main Tab - Right Side
to_char(ooha.order_number) upload_order_identifier,
ooha.order_number,
ottt.name order_type,
xxen_util.client_time(ooha.ordered_date) ordered_date,
(select qslhv.name from qp_secu_list_headers_v qslhv where qslhv.list_header_id=ooha.price_list_id) header_price_list,
(select nvl(jrret.resource_name,jrs.name) from jtf_rs_salesreps jrs, jtf_rs_resource_extns_tl jrret where jrs.resource_id=jrret.resource_id(+) and jrret.language(+)=userenv('lang') and jrs.salesrep_id=ooha.salesrep_id and jrs.org_id=ooha.org_id) header_salesrep,
xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660) header_status,
:order_action order_action,
to_char(null) header_change_reason,
to_char(null) header_change_comments,
ooha.transactional_curr_code currency,
-- Header Others Tab - Left Side
(select rtv.name from ra_terms_vl rtv where rtv.term_id=ooha.payment_term_id) header_payment_terms,
(select mp.organization_code from mtl_parameters mp where mp.organization_id=ooha.ship_from_org_id) header_warehouse,
xxen_util.meaning(ooha.fob_point_code,'FOB',222) header_fob_point,
ooha.shipping_instructions header_shipping_instructions,
xxen_util.meaning(ooha.tax_exempt_flag,'TAX_CONTROL_FLAG',222) header_tax_handling,
xxen_util.meaning(ooha.tax_exempt_reason_code,'TAX_REASON',0) header_tax_exempt_reason,
ooha.tax_exempt_number header_tax_exempt_number,
oos.name order_source,
-- Header Others Tab - Right Side
xxen_util.meaning(ooha.sales_channel_code,'SALES_CHANNEL',660) header_sales_channel,
(select osmv.meaning from oe_ship_methods_v osmv where osmv.lookup_code=ooha.shipping_method_code) header_shipping_method,
xxen_util.meaning(ooha.freight_terms_code,'FREIGHT_TERMS',660) header_freight_terms,
xxen_util.meaning(ooha.shipment_priority_code,'SHIPMENT_PRIORITY',660) header_shipment_priority,
ooha.packing_instructions header_packing_instructions,
-- Header Additional Fields
xxen_util.client_time(ooha.request_date) header_request_date,
ooha.cust_po_number header_cust_po_number,
(select oav.name||' : '||oav.revision from oe_agreements_vl oav where oav.agreement_id=ooha.agreement_id) header_agreement,
xxen_util.meaning(ooha.demand_class_code,'DEMAND_CLASS',3) header_demand_class,
(select rar.name from ra_rules rar where rar.rule_id=ooha.accounting_rule_id) header_accounting_rule,
(select rar.name from ra_rules rar where rar.rule_id=ooha.invoicing_rule_id) header_invoicing_rule,
(select hcsua.location from hz_cust_site_uses_all hcsua where hcsua.site_use_id=ooha.deliver_to_org_id) header_deliver_to,
-- Source Reference Fields
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,
xxen_util.meaning(ooha.order_category_code,'ORDER_CATEGORY',660) order_category,
-- Line Items Main
rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') line,
ottt2.name line_type,
msiv.concatenated_segments item,
msiv.description,
oola.ordered_quantity,
oola.order_quantity_uom uom,
xxen_util.meaning(oola.flow_status_code,'LINE_FLOW_STATUS',660) line_status,
oola.item_type_code item_type,
xxen_util.meaning(oola.line_category_code,'ORDER_CATEGORY',660) line_category,
-- Line Pricing Tab
(select qslhv.name from qp_secu_list_headers_v qslhv where qslhv.list_header_id=oola.price_list_id) line_price_list,
oola.unit_list_price,
oola.unit_selling_price,
xxen_util.meaning(oola.calculate_price_flag,'CALCULATE_PRICE_FLAG',660) calculate_price,
-- Line Change (reason/comments required by Oracle when modifying a Booked line, e.g. reducing ordered_quantity)
to_char(null) line_change_reason,
to_char(null) line_change_comments,
-- Line Discount
(select qplhv.name from qp_list_headers_vl qplhv, oe_price_adjustments opa where opa.line_id=oola.line_id and opa.list_line_type_code='DIS' and opa.applied_flag='Y' and opa.list_header_id=qplhv.list_header_id and rownum=1) discount_modifier,
(select opa.list_line_no from oe_price_adjustments opa where opa.line_id=oola.line_id and opa.list_line_type_code='DIS' and opa.applied_flag='Y' and rownum=1) discount_line,
(select xxen_util.meaning(opa.arithmetic_operator,'ARITHMETIC_OPERATOR',661) from oe_price_adjustments opa where opa.line_id=oola.line_id and opa.list_line_type_code='DIS' and opa.applied_flag='Y' and rownum=1) discount_type,
(select opa.operand from oe_price_adjustments opa where opa.line_id=oola.line_id and opa.list_line_type_code='DIS' and opa.applied_flag='Y' and rownum=1) discount_value,
-- Line Shipping Tab
(select mp.organization_code from mtl_parameters mp where mp.organization_id=oola.ship_from_org_id) line_warehouse,
xxen_util.meaning(oola.source_type_code,'SOURCE_TYPE',660) line_source_type,
xxen_util.client_time(oola.request_date) line_request_date,
xxen_util.client_time(oola.schedule_ship_date) schedule_ship_date,
xxen_util.client_time(oola.promise_date) promise_date,
-- Line Addresses Tab
(select hcsua.location from hz_cust_site_uses_all hcsua where hcsua.site_use_id=oola.ship_to_org_id) line_ship_to,
(select hcsua.location from hz_cust_site_uses_all hcsua where hcsua.site_use_id=oola.invoice_to_org_id) line_bill_to,
-- Line Others Tab
(select nvl(jrret.resource_name,jrs.name) from jtf_rs_salesreps jrs, jtf_rs_resource_extns_tl jrret where jrs.resource_id=jrret.resource_id(+) and jrret.language(+)=userenv('lang') and jrs.salesrep_id=oola.salesrep_id and jrs.org_id=oola.org_id) line_salesrep,
oola.cust_po_number line_cust_po_number,
oola.customer_line_number,
(select osmv.meaning from oe_ship_methods_v osmv where osmv.lookup_code=oola.shipping_method_code) line_shipping_method,
xxen_util.meaning(oola.freight_terms_code,'FREIGHT_TERMS',660) line_freight_terms,
(select ppa.segment1 from pa_projects_all ppa where ppa.project_id=oola.project_id) project,
(select pt.task_number from pa_tasks pt where pt.task_id=oola.task_id) task,
-- Line Tax
xxen_util.meaning(oola.tax_exempt_flag,'TAX_CONTROL_FLAG',222) line_tax_handling,
xxen_util.meaning(oola.tax_exempt_reason_code,'TAX_REASON',0) line_tax_exempt_reason,
oola.tax_exempt_number line_tax_exempt_number,
oola.tax_code tax_classification_code,
-- Line Returns
xxen_util.meaning(oola.return_reason_code,'CREDIT_MEMO_REASON',222) return_reason,
-- 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,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE16',ooha.rowid,ooha.attribute16) order_header_attribute16,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE17',ooha.rowid,ooha.attribute17) order_header_attribute17,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE18',ooha.rowid,ooha.attribute18) order_header_attribute18,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE19',ooha.rowid,ooha.attribute19) order_header_attribute19,
xxen_util.display_flexfield_value(660,'OE_HEADER_ATTRIBUTES',ooha.context,'ATTRIBUTE20',ooha.rowid,ooha.attribute20) order_header_attribute20,
-- 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,
-- Order Line DFF Attributes
xxen_util.display_flexfield_context(660,'OE_LINE_ATTRIBUTES',oola.context) order_line_dff_context,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE1',oola.rowid,oola.attribute1) order_line_attribute1,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE2',oola.rowid,oola.attribute2) order_line_attribute2,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE3',oola.rowid,oola.attribute3) order_line_attribute3,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE4',oola.rowid,oola.attribute4) order_line_attribute4,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE5',oola.rowid,oola.attribute5) order_line_attribute5,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE6',oola.rowid,oola.attribute6) order_line_attribute6,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE7',oola.rowid,oola.attribute7) order_line_attribute7,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE8',oola.rowid,oola.attribute8) order_line_attribute8,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE9',oola.rowid,oola.attribute9) order_line_attribute9,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE10',oola.rowid,oola.attribute10) order_line_attribute10,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE11',oola.rowid,oola.attribute11) order_line_attribute11,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE12',oola.rowid,oola.attribute12) order_line_attribute12,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE13',oola.rowid,oola.attribute13) order_line_attribute13,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE14',oola.rowid,oola.attribute14) order_line_attribute14,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE15',oola.rowid,oola.attribute15) order_line_attribute15,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE16',oola.rowid,oola.attribute16) order_line_attribute16,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE17',oola.rowid,oola.attribute17) order_line_attribute17,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE18',oola.rowid,oola.attribute18) order_line_attribute18,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE19',oola.rowid,oola.attribute19) order_line_attribute19,
xxen_util.display_flexfield_value(660,'OE_LINE_ATTRIBUTES',oola.context,'ATTRIBUTE20',oola.rowid,oola.attribute20) order_line_attribute20,
-- Order Line TP Attributes
xxen_util.display_flexfield_context(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context) order_line_tp_context,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE1',oola.rowid,oola.tp_attribute1) order_line_tp_attribute1,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE2',oola.rowid,oola.tp_attribute2) order_line_tp_attribute2,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE3',oola.rowid,oola.tp_attribute3) order_line_tp_attribute3,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE4',oola.rowid,oola.tp_attribute4) order_line_tp_attribute4,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE5',oola.rowid,oola.tp_attribute5) order_line_tp_attribute5,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE6',oola.rowid,oola.tp_attribute6) order_line_tp_attribute6,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE7',oola.rowid,oola.tp_attribute7) order_line_tp_attribute7,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE8',oola.rowid,oola.tp_attribute8) order_line_tp_attribute8,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE9',oola.rowid,oola.tp_attribute9) order_line_tp_attribute9,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE10',oola.rowid,oola.tp_attribute10) order_line_tp_attribute10,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE11',oola.rowid,oola.tp_attribute11) order_line_tp_attribute11,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE12',oola.rowid,oola.tp_attribute12) order_line_tp_attribute12,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE13',oola.rowid,oola.tp_attribute13) order_line_tp_attribute13,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE14',oola.rowid,oola.tp_attribute14) order_line_tp_attribute14,
xxen_util.display_flexfield_value(660,'OE_LINE_TP_ATTRIBUTES',oola.tp_context,'TP_ATTRIBUTE15',oola.rowid,oola.tp_attribute15) order_line_tp_attribute15,
--Who 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,
--Id columns
ooha.header_id,
oola.line_id,
null upload_row
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
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(+)
Parameter NameSQL textValidation
Upload Mode
:p_upload_mode like '%' || xxen_upload.action_update
LOV
Operating Unit
haouv.name=:operating_unit
LOV
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
Open only
ooha.open_flag='Y' and
oola.open_flag='Y'
LOV Oracle
Exclude Cancelled
ooha.cancelled_flag='N' and
oola.cancelled_flag='N'
LOV Oracle
Order Status
ooha.flow_status_code=:header_status
LOV
Order Action
 
LOV
Line Status
oola.flow_status_code=xxen_util.lookup_code(:line_status,'LINE_FLOW_STATUS',660)
LOV
Shippable Flag
oola.shippable_flag=:shippable_flag
LOV Oracle
Creation Date From
ooha.creation_date>=:creation_date_from
Date
Creation Date To
ooha.creation_date<:creation_date_to+1
Date
Line Creation Date From
oola.creation_date>=:line_creation_date_from
Date
Line Creation Date To
oola.creation_date<:line_creation_date_to+1
Date
Order Category
ooha.order_category_code=xxen_util.lookup_code(:order_category,'ORDER_CATEGORY',660)
LOV
Line Category
oola.line_category_code=xxen_util.lookup_code(:line_category,'ORDER_CATEGORY',660)
LOV
Item
msiv.concatenated_segments like :item
LOV
Item Type
oola.item_type_code=:item_type_code
LOV Oracle
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
Ship From Warehouse
oola.ship_from_org_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:ship_from_warehouse)
LOV
Request Date From
oola.request_date>=:request_date_from
Date
Request Date To
oola.request_date<:request_date_to+1
Date
Cancelled Date From
oola.line_id in (select oolh.line_id from oe_order_lines_history oolh where oolh.hist_creation_date>=:cancel_date_from and oolh.hist_creation_date<:cancel_date_to+1 and oolh.hist_type_code='CANCELLATION')
Date
Cancelled Date To
oola.line_id in (select oolh.line_id from oe_order_lines_history oolh where oolh.hist_creation_date<:cancel_date_to+1 and oolh.hist_creation_date>=:cancel_date_from and oolh.hist_type_code='CANCELLATION')
Date
Debug
 
LOV
Download
Blitz Report™