This is our 13th year with Oracle EBS R12 and we hardly use any standard reports. Recently, after implementing Tax for our Bahrain operations, I was asked to modify the Customer SOA accommodating the tax requirements.
I opened up the view, that the implementation partner made, and found that the view had unnecessary joins and grouping using names for transactions and other. I changed the base query with the below.
Select
a.org_id, a.payment_schedule_id,
a.amount_due_original, a.amount_due_remaining, a.class, a.invoice_currency_code, a.customer_id, a.customer_trx_id,
a.amount_line_items_original, a.amount_line_items_remaining
,a.tax_original, a.tax_remaining, a.discount_original, a.discount_remaining
,a.trx_number,a.trx_date, b.purchase_order, b.interface_header_attribute1 order_number, interface_header_attribute2 order_type,interface_header_attribute6 order_line_id
,c.header_id order_header_id
,sum(a.amount_due_remaining) over (order by a.payment_schedule_id asc) running_total
from AR_PAYMENT_SCHEDULES_ALL a
left outer join RA_CUSTOMER_TRX_ALL b on a.CUSTOMER_TRX_ID = b.CUSTOMER_TRX_ID
left outer join oe_order_lines_all c on b.interface_header_attribute6=c.line_id
where
1=1
and a.org_id = 285
and a.customer_id=(select customer_id from ar_customers where customer_number='227634')
--and a.amount_due_remaining > 0 --uncomment for unmatched invoices only listing
order by a.payment_schedule_id
/
Later a new view was created like following:
CREATE OR REPLACE VIEW OMSCUSTSOA_V
AS
Select
a.org_id, a.payment_schedule_id,
a.amount_due_original, a.amount_due_remaining, a.class, a.invoice_currency_code, a.customer_id, a.customer_trx_id,
a.amount_line_items_original, a.amount_line_items_remaining
,a.tax_original, a.tax_remaining, a.discount_original, a.discount_remaining
,a.trx_number,a.trx_date, b.purchase_order, b.interface_header_attribute1 order_number, interface_header_attribute2 order_type,interface_header_attribute6 order_line_id
,c.header_id order_header_id
from AR_PAYMENT_SCHEDULES_ALL a
left outer join RA_CUSTOMER_TRX_ALL b on a.CUSTOMER_TRX_ID = b.CUSTOMER_TRX_ID
left outer join oe_order_lines_all c on b.interface_header_attribute6=c.line_id
where
1=1
/
Subsequently, for any customer, the view is referred in the final query.
Select a.*, sum(amount_due_original) over(order by payment_schedule_id asc) running_total
from OMSCUSTSOA_V a
where
1=1
and a.org_id=285
--and a.amount_due_remaining > 0 --this condition will fetch open invoices that are yet to be matched
and a.customer_id=(select customer_id from ar_customers where customer_number='227634')
order by payment_schedule_id
/
Hope this helps few out there!
