Post date: Jan 23, 2013 12:37:1 PM
SELECT DISTINCT gjh.NAME journal_name, gjb.NAME batch_name,
gjh.je_source SOURCE, gjh.je_category CATEGORY,
gjh.currency_code,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
|| '.'
|| gcc.segment8 ACCOUNT,
--|| '.'
--|| gcc.segment9 ACCOUNT,
NVL (gjl.entered_dr, 0) debit_valas,
NVL (gjl.entered_cr, 0) credit_valas,
NVL (gjl.accounted_dr, 0) debit,
NVL (gjl.accounted_cr, 0) credit, xah.event_type_code,
xal.accounting_class_code,
NVL (xal.entered_dr, 0) debit_valas_xla,
NVL (xal.entered_cr, 0) credit_valas_xla,
NVL (xal.accounted_dr, 0) debit_xla,
NVL (xal.accounted_cr, 0) credit_xla, xal.gl_sl_link_id,
aca.check_number payment_number
--,(AIPA.AMOUNT * NVL (AIpa.EXCHANGE_RATE, 1)) payment_amount
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_je_batches gjb,
gl_import_references gir,
gl_code_combinations gcc,
xla_ae_lines xal,
xla_ae_headers xah,
ap_payment_history_all apha,
ap_checks_all aca,
ap_invoice_payments_all aipa
WHERE gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.code_combination_id = gcc.code_combination_id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.gl_sl_link_table = xal.gl_sl_link_table
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xah.event_id = apha.accounting_event_id
AND apha.check_id = aca.check_id
AND aca.check_id = aipa.check_id
AND gjh.period_name =
'DEC-12'
-- 'Jan-10','Feb-10','Mar-10','Apr-10','May-10','Jun-10','Jul-10','Aug-10','Oct-10','Nov-10','Dec-10')
AND gcc.segment3 in ( '2131222')--,'211112','211113','211114','216112')
AND gjh.actual_flag = 'A'
AND gjh.status = 'P'
AND gcc.enabled_flag = 'Y'
--and aia.INVOICE_NUM = 'KB10010914'
AND (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)) <> 0
select *
from gl_interface
select *
from gl_je_lines
select *
from gl_je_batches
where name='Payables A 1648480 1635684'
select *
from gl_je_batches
where status='U'
and default_period_name='DEC-12'
select *
from gl_je_headers
where je_batch_id='1463600'
/* Formatted on 2013/01/23 13:43 (Formatter Plus v4.8.8) */
SELECT *
FROM gl_je_lines
WHERE je_header_id IN (SELECT je_header_id
FROM gl_je_headers
WHERE je_batch_id IN ('1463600'))
select *--ledger_id
from gl_sets_of_books
select *
from gl_je_lines
where ledger_id ='-2032'
select *
from gl_je_lines
where ledger_id not in (select set_of_books_id from gl_sets_of_books)
and period_name ='DEC-12'
select *
from gl_je_headers
where ledger_id not in (select set_of_books_id from gl_sets_of_books)
and period_name='DEC-12'
select *
from gl_import_references
where je_batch_id ='1463600'
select *
from gl_ledgers
SELECT gled.ledger_id as LEDGER_ID
FROM gl_ledger_relationships glr, gl_ledgers gled
WHERE glr.primary_ledger_id = &p_ledger_id -- Put primary ledger id here
AND glr.application_id = 101
AND ((glr.target_ledger_category_code IN ('SECONDARY','ALC')
AND glr.relationship_type_code = 'SUBLEDGER')
OR (glr.target_ledger_category_code IN ('PRIMARY')
AND glr.relationship_type_code = 'NONE'))
AND glr.target_ledger_id = gled.ledger_id
AND Nvl(gled.complete_flag,'Y') = 'Y'
GROUP BY gled.ledger_id;
select *
from xla_ae_headers
--where period_name='DEC-12'
--and ledger_id ='2032'
where gl_transfer_status_code <> 'Y'
select *
from ad_bugs
where bug_number='8469298'