Last updated on JUNE 20, 2016

Applies to:

Symptoms

-- Problem Statement:
On 11.5.10.2 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 t10.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
)
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.INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'
and t11.sales_order_line IS NOT NULL
)
);

Cause