ONT Order to Cash Tracker

Description

Categories
Enginatics, R12 only
Repository
GitHub
One row per sales order or quote line with the date of each step from quote to payment, and the accounting and GL posting of its invoice.

Invoice columns are those of the first invoice raised for the line; Invoiced Amount sums all its invoice lines. Amount Due Remaining is shown on the invoice's first order line only, so it can be summed. Paid Date is the date the invoice's last installment closed, Receipt Number the latest receipt applied to it.

Accounted Date is when final accounting was created for the invoice, GL Posted Date when its journal was posted.

SQL 195 lines

Parameters 12

ParameterTypeList of values SQL
Operating UnitLOV
haouv.name=:operating_unit
Order NumberLOV
ooha.order_number=:order_number
Quote NumberLOV
(
ooha.quote_number=:quote_number or
ooha.orig_sys_document_ref like :quote_number||'%' and ooha.source_document_type_id=16
)
Customer NameLOV
upper(hp.party_name) like upper(:customer_name)
Account NumberLOV
hca.account_number=:account_number
Order TypeLOV
ottt.name=:order_type
PhaseLOV
nvl(ooha.transaction_phase_code,'F')=xxen_util.lookup_code(:phase,'TRANSACTION_PHASE',660)
Ordered Date FromDate
ooha.ordered_date>=:ordered_date_from
Ordered Date ToDate
ooha.ordered_date<:ordered_date_to+1
Creation Date FromDate
ooha.creation_date>=:creation_date_from
Creation Date ToDate
ooha.creation_date<:creation_date_to+1
Incomplete OnlyLOV
(
oola.open_flag='Y' or
to_char(oola.line_id) in (
select
rctla.interface_line_attribute6
from
ar_payment_schedules_all apsa,
ra_customer_trx_lines_all rctla
where
apsa.status='OP' and
apsa.customer_trx_id=rctla.customer_trx_id and
rctla.interface_line_context in ('ORDER ENTRY','INTERCOMPANY') and
rctla.line_type='LINE'
)
)