SELECT glcc.segment1 co, glcc.segment2 loc, glcc.segment3 cc,
glcc.segment4 acct, glcc.segment5 prod, glcc.segment6 chan,
glcc.segment7 proj, '' "CODECOMBO desc", xal.accounting_date,
gjh.je_source, gjh.je_category CATEGORY, gjb.NAME batch,
gjh.NAME "JOURNAL name", '' "JE SEQ name", '' "JE SEQ NUM",
gjl.je_line_num "JE LINE", gjl.description "JE LINE DESCR",
gjl.accounted_cr gl_cr, gjl.accounted_dr gl_dr, '' "VENDOR/CUSTOMER",
acra.receipt_number "TRANSACTION NUM",
xal.accounting_class_code "TRANSACTION TYPE", xal.accounted_cr xla_cr,
xal.accounted_dr xla_dr, gjh.period_name period,
(gb.begin_balance_dr - gb.begin_balance_cr) begin_balance,
( gb.period_net_dr
- gb.period_net_cr
+ gb.project_to_date_dr
- gb.project_to_date_cr
) end_balance,
gl.NAME ledger_name
FROM apps.gl_je_headers gjh,
apps.gl_je_batches gjb,
apps.gl_balances gb,
apps.gl_ledgers gl,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.gl_code_combinations glcc,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_distribution_links xdl,
apps.ar_distributions_all ada,
apps.ar_cash_receipt_history_all acrha,
apps.ar_cash_receipts_all acra
WHERE 1 = 1
AND gjh.ledger_id = gl.ledger_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.code_combination_id = glcc.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.ae_header_id = xdl.ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.application_id = xdl.application_id
AND xdl.source_distribution_type(+) = 'AR_DISTRIBUTIONS_ALL'
AND xdl.source_distribution_id_num_1(+) = ada.line_id
AND ada.source_id = acrha.cash_receipt_history_id
AND acrha.cash_receipt_id = acra.cash_receipt_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND glcc.segment4 = '13010'
AND gjh.period_name = 'OCT-14'
AND gl.NAME = 'Amazon.com, Inc.'
AND gjh.je_source = 'Receivables'
--and gjh.je_category = 'Receipts'
--and GJH.name = 'OCT-14 Receipts USD'
glcc.segment4 acct, glcc.segment5 prod, glcc.segment6 chan,
glcc.segment7 proj, '' "CODECOMBO desc", xal.accounting_date,
gjh.je_source, gjh.je_category CATEGORY, gjb.NAME batch,
gjh.NAME "JOURNAL name", '' "JE SEQ name", '' "JE SEQ NUM",
gjl.je_line_num "JE LINE", gjl.description "JE LINE DESCR",
gjl.accounted_cr gl_cr, gjl.accounted_dr gl_dr, '' "VENDOR/CUSTOMER",
acra.receipt_number "TRANSACTION NUM",
xal.accounting_class_code "TRANSACTION TYPE", xal.accounted_cr xla_cr,
xal.accounted_dr xla_dr, gjh.period_name period,
(gb.begin_balance_dr - gb.begin_balance_cr) begin_balance,
( gb.period_net_dr
- gb.period_net_cr
+ gb.project_to_date_dr
- gb.project_to_date_cr
) end_balance,
gl.NAME ledger_name
FROM apps.gl_je_headers gjh,
apps.gl_je_batches gjb,
apps.gl_balances gb,
apps.gl_ledgers gl,
apps.gl_je_lines gjl,
apps.gl_import_references gir,
apps.gl_code_combinations glcc,
apps.xla_ae_lines xal,
apps.xla_ae_headers xah,
apps.xla_distribution_links xdl,
apps.ar_distributions_all ada,
apps.ar_cash_receipt_history_all acrha,
apps.ar_cash_receipts_all acra
WHERE 1 = 1
AND gjh.ledger_id = gl.ledger_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gjl.code_combination_id = glcc.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.ae_header_id = xdl.ae_header_id
AND xal.application_id = xah.application_id
AND xal.ae_line_num = xdl.ae_line_num
AND xal.application_id = xdl.application_id
AND xdl.source_distribution_type(+) = 'AR_DISTRIBUTIONS_ALL'
AND xdl.source_distribution_id_num_1(+) = ada.line_id
AND ada.source_id = acrha.cash_receipt_history_id
AND acrha.cash_receipt_id = acra.cash_receipt_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND glcc.segment4 = '13010'
AND gjh.period_name = 'OCT-14'
AND gl.NAME = 'Amazon.com, Inc.'
AND gjh.je_source = 'Receivables'
--and gjh.je_category = 'Receipts'
--and GJH.name = 'OCT-14 Receipts USD'
Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
ReplyDeleteExpense Reports