Unable To Invoice Sales Orders After Lines Are Closed

(Doc ID 754320.1)

Last updated on OCTOBER 26, 2017

Applies to:

Oracle Order Management - Version to [Release 11.5.10]
Information in this document applies to any platform.
***Checked for relevance on 26-NOV-2014***


-- Problem Statement:
On in Production:

Find 30 orders and their associated lines (spanning 3 different trip numbers)
have order line statuses = Closed and properly invoiced from OM side,
but there are no Invoice for them in Receivables.

These sales orders need to be re-invoiced.

The following sql will help you identify those lines which are seen to be
closed in OM but not invoiced to AR:-

select t1.line_id, t1.ordered_quantity, t1.invoiced_quantity,
t2.order_number, t2.header_id, t2.org_id
from oe_order_lines_all t1,
oe_order_headers_all t2
where t1.invoice_interface_status_code = 'YES'
and t1.flow_status_code = 'CLOSED'
and t1.open_flag = 'N'
and t1.line_category_code in ('ORDER', 'RETURN')
and nvl(t1.invoiced_quantity, 0) <> 0
and t2.header_id = t1.header_id
and t2.ordered_date >= to_date('01-NOV-2008', 'dd-MON-yyyy')
and not exists ( select 1
from ra_customer_trx_lines_all t10
where t10.interface_line_attribute6 = to_char(t1.line_id)
and t10.sales_order = t2.order_number
and t10.sales_order_line IS NOT NULL
and not exists ( select 1
from ra_interface_lines_all t11
where t11.interface_line_attribute6 = to_char(t1.line_id)
and t11.sales_order = t2.order_number
and t11.sales_order_line IS NOT NULL


Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms