Query : Transactions Posted from AR TO GL

Query : Transactions Posted from AR TO GL

August 13, 2010

SELECT l.subledger_doc_sequence_value “Doc Number”,
l.effective_date “GL Date”,
l.accounted_dr “Debit”,
l.accounted_cr “Credit”,
l.description “Description”,
l.reference_4 “AR Number”,
l.reference_9 “AR Type”
FROM gl_je_lines l, gl_je_headers h
WHERE je_source = ‘Receivables’
AND h.je_header_id = l.je_header_id
AND h.set_of_books_id =
AND h.period_name =

Query : To Extract revenue distribution lines in AR

August 11, 2010

SELECT distinct c.customer_name,
c.customer_number,
c.customer_id,
t.customer_trx_id,
t.trx_number,
ct.NAME invoice_type,
l.line_number,
t.org_id,
cc.segment1,
cc.segment2,
cc.segment3,
cc.segment4,
cc.segment5,
cc.segment6,
d.gl_date,
d.cust_trx_line_gl_dist_id,
d.code_combination_id,
d.account_class
FROM ra_cust_trx_types_all ct,
ra_customers c,
ra_customer_trx_all t,
ra_customer_trx_lines_all l,
gl_code_combinations cc,
ra_cust_trx_line_gl_dist_all d
WHERE 1 = 1
AND t.cust_trx_type_id = ct.cust_trx_type_id
AND t.bill_to_customer_id = c.customer_id
AND d.customer_trx_id = t.customer_trx_id
AND d.customer_trx_line_id = l.customer_trx_line_id(+)
AND d.code_combination_id = cc.code_combination_id
AND TRUNC (d.gl_date) >= TO_DATE (’01-01-2009′, ‘DD-MM-YYYY’)
AND d.posting_control_id = -3
AND d.account_set_flag = ‘N’
AND d.account_class = ‘REV’

Query: AR Query to get open invoices for single/All customers

August 11, 2010

To get open invoice for single customer /for all customer from the table ar_payment_schedules_all , you can modify the query how you want to get the details

select aps.*
FROM ra_customer_trx_all ra,
ra_customer_trx_lines_all rl,
ar_payment_schedules_all aps,
ra_cust_trx_types_all rt,
hz_cust_accounts hc,
hz_parties hp,
hz_cust_acct_sites_all hcasa_bill,
hz_cust_site_uses_all hcsua_bill,
hz_party_sites hps_bill,
ra_cust_trx_line_gl_dist_all rct
WHERE 1 = 1
AND ra.customer_trx_id = rl.customer_trx_id
AND ra.customer_trx_id = aps.customer_trx_id
AND ra.org_id = aps.org_id
AND rct.customer_trx_id = aps.customer_trx_id
AND rct.customer_trx_id = ra.customer_trx_id
AND rct.customer_trx_id = rl.customer_trx_id
AND rct.customer_trx_line_id = rl.customer_trx_line_id
AND ra.complete_flag = ‘Y’
AND rl.line_type IN (‘FREIGHT’, ‘LINE’)
AND ra.cust_trx_type_id = rt.cust_trx_type_id
AND ra.bill_to_customer_id = hc.cust_account_id
AND hc.status = ‘A’
AND hp.party_id = hc.party_id
AND hcasa_bill.cust_account_id = ra.bill_to_customer_id
AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
AND hcsua_bill.site_use_code = ‘BILL_TO’
AND hcsua_bill.site_use_id = ra.bill_to_site_use_id
AND hps_bill.party_site_id = hcasa_bill.party_site_id
AND hcasa_bill.status = ‘A’
AND hcsua_bill.status = ‘A’
AND aps.amount_due_remaining 0
AND aps.status = ‘OP’
and hc.cust_account_id=

Good for 11i

Query for Customer Receipt Details

August 10, 2010

SELECT acra.cash_receipt_id,
DECODE (acra.TYPE,
‘cash’, ‘cash receipt receipt’,
‘misc’, ‘miscellaneous’,
acra.TYPE
) receipt_type,
acra.currency_code, acra.doc_sequence_value receipt_number,
acra.receipt_number reference_number,
TRUNC (acra.receipt_date) receipt_date, hp.party_name received_from,
acra.misc_payment_source, hca.account_number customer_no,
NVL (acra.amount, 0) entered_amount,
NVL (acra.amount, 0) * NVL (acra.exchange_rate, 1) functional_amount,
arm.NAME payment_method, abaa.bank_account_num bank_acc_num,
abb.bank_name, abb.bank_branch_name, acra.comments description
FROM ar_cash_receipts_all acra,
ar_receipt_methods arm,
ap_bank_accounts_all abaa,
ap_bank_branches abb,
hz_cust_accounts hca,
hz_parties hp
WHERE acra.pay_from_customer = hca.cust_account_id(+)
AND acra.org_id = abaa.org_id(+)
AND hca.party_id = hp.party_id(+)
AND acra.receipt_method_id = arm.receipt_method_id
AND acra.remittance_bank_account_id = abaa.bank_account_id
AND abaa.bank_branch_id = abb.bank_branch_id
ORDER BY TRUNC (acra.receipt_date), acra.doc_sequence_value;