Invoice Querys

INV GROUP BY CUSTOMER NAME

Select rc.customer_NAME,sum(rctla.extended_amount) ,COUNT(rcta.trx_number)--,rctla.revenue_amount
from ra_customers rc,ra_customer_trx_all rcta ,ra_customer_trx_lines_all rctla
where rc.customer_id =nvl(rcta.sold_to_customer_id,rcta.bill_to_customer_id)
and rcta.customer_trx_id = rctla.customer_trx_id
--and rc.customer_number =:customer
and rcta.trx_number BETWEEN 'xxxxxx' AND 'xxxxxxx'
group by rc.customer_name;


*********************************************************************************

invoices with trx date

select DISTINCT rct.trx_number,rct.trx_date
from ra_customers ac,
ra_customer_trx_all rct,
ra_customer_trx_lines_all b
where ac.customer_id=rct.sold_to_customer_id(+)
and rct.customer_trx_id = b.customer_trx_id
--and  ac.customer_number=3059
--and rct.trx_date = '06-DEC-11'
and rct.trx_number in (select DISTINCT PO from Imported Table)
--and purchase_order in('xxxxxxx')
order by rct.trx_date desc;


*********************************************************************************

INVOICE NUMBERS

select * from ra_interface_lines_all where interface_line_attribute1='12873516';


SELECT attribute14 FROM RA_CUSTOMER_TRX_LINES_ALL WHERE interface_line_attribute1='12873516';


SELECT trx_number FROM RA_CUSTOMER_TRX_ALL WHERE interface_HEADER_attribute1='12873516';


*********************************************************************************

Invoice amout Check Query

Select rc.customer_number,rc.customer_name,rcta.purchase_order,rcta.trx_number,sum(rctla.extended_amount)--,rctla.revenue_amount

from ra_customers rc,ra_customer_trx_all rcta ,ra_customer_trx_lines_all rctla

where rc.customer_id =nvl(rcta.sold_to_customer_id,rcta.bill_to_customer_id)

and rcta.customer_trx_id = rctla.customer_trx_id

--and rc.customer_number =:customer

and rcta.trx_number in  ('xxxxxxx','xxxxx','xxxx')

group by rc.customer_number,rc.customer_name,rcta.purchase_order,rcta.trx_number;