Queries for use

Genral Ledger
---------------
select jh.name Journal_name,jh.period_name Period,jh.currency_code Currency ,jh.je_category Category,jh.je_source Source,jl.je_line_num Linenumber,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account,jl.entered_dr Debit,jl.entered_cr Credit
from gl_je_headers jh,
gl_je_lines jl,
gl_code_combinations gcc
where jl.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
and jh.JE_HEADER_ID=jl.JE_HEADER_ID
and jh.name=:name
and jh.period_name=:period
and jh.currency_code='STAT'
and jh.je_source='82'
INVOICE DETAILS
---------------------
selectap.invoice_num Invoice_num,ap.invoice_date Invoice_date,ap.invoice_currency_code Invoice_currency,ap.invoice_amount Invoice_amount,aid.amount Amount,aid.accounting_date Gl_date,pv.vendor_name Supplier,pvs.vendor_site_code Site_name--,at.name Payment_terms,ap.terms_date Term_date,alc.meaning Invoice_type,alc.lookup_code,alc1.meaning Invoice_distribution_type,alc2.meaning Payment_method,gcc.segment1'.'gcc.segment2'.'gcc.segment3'.'gcc.segment4'.'gcc.segment5 Account
from ap_invoices_all ap,
ap_invoice_distributions_all aid,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
ap_terms_tl at,
gl_code_combinations gcc
where alc.lookup_type='INVOICE TYPE'
and alc.lookup_code=ap.INVOICE_TYPE_LOOKUP_CODE
and alc1.lookup_type='INVOICE DISTRIBUTION TYPE'
and alc1.lookup_code=aid.LINE_TYPE_LOOKUP_CODE
and alc2.lookup_type='PAYMENT METHOD'
and alc2.lookup_code=ap.PAYMENT_METHOD_LOOKUP_CODE
and ap.invoice_id=aid.invoice_id
and ap.vendor_id = pv.vendor_id
and gcc.code_combination_id=aid.DIST_CODE_COMBINATION_ID
and pv.vendor_id=pvs.vendor_id
and ap.vendor_site_id=pvs.vendor_site_id
--and ap.terms_id=at.term_id(+)
and ap.invoice_num='214901'
PAYMENT DETAILS IN 11i
-----------------------------
select alc.MEANING Payment_type,acs.name Document,aca.check_number Document_number,aca.amount Payment_amount,aca.CURRENCY_CODE Payment_currency,aca.CHECKRUN_NAME Batchname,aca.EXCHANGE_DATE Rate_date--,aca.EXCHANGE_RATE Exc_rate,aba.bank_account_name Bank_account,aba.CURRENCY_CODE Account_currency,alc1.meaning Payment_method,alc2.meaning Check_status,pv.vendor_name Supplier,pvs.VENDOR_SITE_CODE Suppliersite
from ap_checks_all aca,
ap_check_stocks_all acs,
ap_bank_accounts_all aba,
po_vendors pv,
po_vendor_sites_all pvs,
fnd_lookup_values alc,
fnd_lookup_values alc1,
fnd_lookup_values alc2--,
gl_daily_conversion_types gdct
--,gl_daily_conversion_types gdct1
where alc.lookup_type='PAYMENT TYPE'
and alc.lookup_code=aca.PAYMENT_TYPE_FLAG
and alc1.lookup_type='PAYMENT METHOD'
and alc1.lookup_code=aca.PAYMENT_METHOD_LOOKUP_CODE
and alc2.lookup_type='CHECK STATE'
and alc2.lookup_code=aca.STATUS_LOOKUP_CODE
--and aca.EXCHANGE_RATE_TYPE=gdct.CONVERSION_TYPE
and aca.check_stock_id=acs.check_stock_id
and aca.BANK_ACCOUNT_ID=aba.BANK_ACCOUNT_ID
and aca.VENDOR_ID=pv.vendor_id
and aca.VENDOR_SITE_ID=pvs.VENDOR_SITE_ID
and aca.check_number='1000013'
AR to GL query
-----------------
SELECT gjjlv.period_name "Period" , gjb.name "Batch name" , gjjlv.header_name "Journal Entry For" , gjjlv.je_source "Source" , glcc.concatenated_segments "Accounts" , gjjlv.line_entered_dr "Entered Debit" , gjjlv.line_entered_cr "Entered Credit" , gjjlv.line_accounted_dr "Accounted Debit" , gjjlv.line_accounted_cr "Accounted Credit" , gjjlv.currency_code "Currency" , arm.name "Payment Method" , acra.receipt_number "Receipt Num" , acra.receipt_date "Receipt Date" , RA.CUSTOMER_NAME "Reference" , gjjlv.created_by "Gl Transfer By"
FROM apps.gl_je_journal_lines_v gjjlv ,
gl_je_lines gje ,
gl_je_headers gjh ,
gl_je_batches gjb ,
ar_cash_receipts_all acra ,
apps.ra_customers ra ,
apps.gl_code_combinations_kfv glcc ,
ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and glcc.segment1 ='30D'
AR Transactional query
------------------------------
SELECT ol.line_id,trxl.customer_trx_id, trx.trx_number
FROM ra_customer_trx_all trx,
ra_customer_trx_lines_all trxl,
oe_order_lines_all ol,
oe_order_headers_all oh,
oe_transaction_types_tl tt
WHERE trxl.interface_line_context ='ORDER ENTRY'
AND trxl.INTERFACE_LINE_ATTRIBUTE1 = to_char(oh.order_number)
AND trxl.INTERFACE_LINE_ATTRIBUTE2 = TT.name
AND TT.transaction_type_id = oh.ORDER_TYPE_ID
AND to_char(ol.LINE_ID) = TRXL.INTERFACE_LINE_ATTRIBUTE6
AND TRXL.line_type = 'LINE'
AND TRXL.INTERFACE_LINE_ATTRIBUTE11 = '0'
AND trx.CUSTOMER_TRX_ID = trxl.CUSTOMER_TRX_ID
AND oh.header_id = ol.header_id
AND EXISTS( SELECT 1 from fnd_languages where installed_flag = 'B' AND tt.language = language_code and rownum=1)
AND ol.line_id ").append(s).toString());