Oracle Financials Statistics Queries

begin
mo_global.set_policy_context('S',xx);
end;

1)   AR Transactions (Invoice, Credit Memo, Deposits)

SELECT COUNT(*),class
FROM RA_CUSTOMER_TRX_ALL rcta,ra_batch_sources_all rbsa,
ar_payment_schedules_all apsa,hz_parties hp,hz_cust_accounts hca,ra_cust_trx_types_all rctta,RA_CUST_TRX_LINE_GL_DIST_all a
WHERE rcta.batch_source_id=rbsa.batch_source_id
and rcta.customer_trx_id=a.customer_trx_id
and rcta.customer_trx_id=apsa.customer_trx_id
and a.customer_trx_id=apsa.customer_trx_id
and rcta.cust_trx_type_id=rctta.cust_trx_type_id
and hp.party_id=hca.party_id
and hca.cust_account_id=apsa.customer_id
and rbsa.name not like '%Migration%'
and rcta.TRX_DATE between to_date('01-Jan-2009') and to_date('30-Jun-2009')
group by class

SELECT COUNT(*),CTT_CLASS
from RA_CUSTOMER_TRX_PARTIAL_V rcta
where rcta.TRX_DATE between to_date('01-Jan-2009') and to_date('30-Jun-2009')
group by CTT_CLASS




2)     AP Invoices

SELECT count(*)
FROM   AP_INVOICES_ALL
where  source not like 'XXXX SUPPLIER DEP'



3) AR Receipts

select count(*) from ar_cash_receipts_all where RECEIPT_DATE between to_date('01-Jan-2009') and to_date('30-Jun-2009')



   4) AP Checks

SELECT SUM(AMOUNT),COUNT(*)
FROM   AP_CHECKS_ALL
WHERE  STATUS_LOOKUP_CODE NOT LIKE 'VOIDED'
and CHECK_DATE between to_date('01-Jan-2009') and to_date('30-Jun-2009')



5) Journals

 select count(*) from GL_JE_HEADERS
 where je_source<>'Manual'
 and DATE_CREATED between to_date('01-Jan-2009') and to_date('30-Jun-2009')



6) Requisitions

select count(*) from po_requisition_headers_all where creation_date between to_date('01-Jan-2009') and to_date('30-Jun-2009')

     

7) Purchase Orders

select count(*) from po_headers_all where creation_date between to_date('01-Jan-2009') and to_date('30-Jun-2009')

8) Active Suppliers

select count(*) from po_vendors where creation_date between to_date('01-Jan-2009') and to_date('31-Aug-2009')
and enabled_flag = 'Y'