SELECT DISTINCT o101368.book_currency AS c_1,
(o101368.converted_journal_total_dr
* o101368.currency_conversion_rate
) AS c_2,
o101368.converted_journal_total_cr
* o101368.currency_conversion_rate AS c_3,
o101368.currency,
o101368.currency_conversion_date,
o101368.currency_conversion_rate,
o101368.currency_conversion_type,
o101368.effective_date,
o101368.journal_batch_name,
o101368.journal_category,
o101368.journal_entry_name,
LPAD(o101368.journal_entry_name,8) reval_reverse,
o101368.journal_source, o101368.period_name,
o101368.reversal_period,
o101368.set_of_books_name set_of_books_name,
o101400.company_50248 company, o101400.account_50248 account,
o101400.interco_50248 interco,
o101368.reverse_status_flag,
as107663_128801_old AS as107663_128801_old,
as107665_128817_old AS as107665_128817_old,
as107668_136855_old AS as107668_136855_old,
NVL(SUM(o101400.entered_cr), 0) AS e125430_sum,
NVL(SUM(o101400.entered_dr), 0) AS e125439_sum,
NVL(SUM(o101400.converted_cr), 0) AS e118546_sum,
NVL(SUM(o101400.converted_dr), 0) AS e118547_sum,
NVL(SUM(o101400.converted_dr), 0) - NVL(SUM(o101400.converted_cr), 0) net_converted
FROM (SELECT je1.je_header_id journal_entry_id, je1.NAME journal_entry_name, je1.ledger_id, set_of_books.NAME set_of_books_name,
journal_category.user_je_category_name journal_category,
journal_source.user_je_source_name journal_source, je1.period_name,
je1.currency_code currency, je1.default_effective_date effective_date,
DECODE (je1.tax_status_code,
'N', 'Not Required',
'R', 'Required',
'T', 'Taxed',
NULL
) tax_status,
je1.je_batch_id journal_batch_id, journal_batch.NAME journal_batch_name, je1.accrual_rev_effective_date reversal_date,
je1.accrual_rev_period_name reversal_period,
DECODE (je1.accrual_rev_status,
'M', 'Marked for Reversal',
'N', 'Not Reversed',
'R', 'Reversed',
'U', 'Unreversable',
NULL
) reverse_status_flag,
DECODE (DECODE (je1.accrual_rev_change_sign_flag,
'Y', 'C',
'N', 'S'
),
'C', 'Change Sign',
'S', 'Switch Dr/Cr',
NULL
) reversal_method,
je1.description description, je1.control_total control_total, je1.running_total_dr entered_journal_total_dr,
je1.running_total_cr entered_journal_total_cr, je1.running_total_accounted_dr converted_journal_total_dr,
je1.running_total_accounted_cr converted_journal_total_cr, je1.currency_conversion_rate currency_conversion_rate,
je1.currency_conversion_type currency_conversion_type_code,
gl_conversion_type.user_conversion_type currency_conversion_type,
je1.currency_conversion_date currency_conversion_date, je1.external_reference,
je1.parent_je_header_id primary_journal_entry_id, je2.NAME primary_journal_entry_name, je1.reversed_je_header_id reversed_journal_entry_id,
je3.NAME reversed_journal_entry_name, je1.doc_sequence_value document_number, je1.originating_bal_seg_value,
set_of_books.currency_code book_currency
FROM gl_je_headers je1,
gl_sets_of_books set_of_books,
gl_je_batches journal_batch,
gl_je_headers je2,
gl_je_headers je3,
gl_je_categories journal_category,
gl_je_sources journal_source,
gl_daily_conversion_types gl_conversion_type
WHERE je1.ledger_id = set_of_books.set_of_books_id
AND je1.je_batch_id = journal_batch.je_batch_id
AND je1.parent_je_header_id = je2.je_header_id(+)
AND je1.reversed_je_header_id = je3.je_header_id(+)
AND je1.actual_flag = 'A'
AND je1.je_category = journal_category.je_category_name
AND je1.je_source = journal_source.je_source_name
AND je1.currency_conversion_type = gl_conversion_type.conversion_type) o101368,
(SELECT journal_line.je_header_id journal_entry_id, journal_entry.NAME journal_entry_name,
journal_line.je_line_num journal_line_number,
journal_line.ledger_id set_of_books_id,
gl_set_of_books.NAME set_of_books_name, journal_line.code_combination_id account_id,
DECODE (gcc.chart_of_accounts_id,
50248, gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6,
NULL
) ACCOUNT,
gcc.segment1 company_50248, gcc.segment2 account_50248, gcc.segment3 department_50248, gcc.segment4 region_50248,
gcc.segment5 function_50248, gcc.segment6 interco_50248, journal_line.period_name period_name,
journal_line.effective_date effective_date, journal_line.entered_dr entered_dr,
journal_line.entered_cr entered_cr, journal_line.accounted_dr converted_dr,
journal_line.accounted_cr converted_cr, journal_line.description description,
journal_line.stat_amount stat_amount, journal_line.subledger_doc_sequence_value subledger_document_number,
journal_line.reference_1, journal_line.reference_2,
journal_line.reference_3, journal_line.reference_4,
journal_line.reference_5, journal_line.reference_6,
journal_line.reference_7, journal_line.reference_8,
journal_line.reference_9, journal_line.reference_10,
journal_line.attribute_category line_context,
journal_line.attribute1 line_project_number_2,
journal_line.taxable_line_flag tax_tax_line, journal_line.tax_type_code tax_tax_type_3,
journal_line.tax_code_id tax_tax_code_3, journal_line.tax_rounding_rule_code tax_rounding_rule_3,
journal_line.amount_includes_tax_flag tax_amount_includes_tax_3,
journal_line.tax_document_identifier tax_document_identifier_3,
journal_line.tax_document_date tax_document_date_3, journal_line.tax_customer_name "TAX_CUSTOMER/VENDOR_NAME_3",
journal_line.tax_customer_reference "TAX_CUSTOMER/VENDOR_REFERENC_3",
journal_line.tax_registration_number tax_tax_registration_number_3,
journal_line.jgzz_recon_ref local_recon_ref
FROM gl_sets_of_books gl_set_of_books,
gl_je_lines journal_line,
gl_je_headers journal_entry,
gl_code_combinations gcc
WHERE journal_line.ledger_id = gl_set_of_books.set_of_books_id
AND journal_line.je_header_id = journal_entry.je_header_id
AND journal_line.code_combination_id = gcc.code_combination_id) o101400,
(SELECT o101398.journal_batch_name AS as107663_128801_old_2,
MAX (o101398.journal_batch_id) AS as107663_128801_old
FROM (SELECT journal_batch1.je_batch_id journal_batch_id, journal_batch1.NAME journal_batch_name
FROM gl_je_batches journal_batch1) o101398
GROUP BY o101398.journal_batch_name),
(SELECT o101399.journal_entry_name AS as107665_128817_old_2,
MAX (o101399.journal_entry_id) AS as107665_128817_old
FROM (SELECT je1.je_header_id journal_entry_id, je1.NAME journal_entry_name
FROM gl_je_headers je1,
gl_sets_of_books set_of_books,
gl_encumbrance_types_b gl_encumbrance_type,
--gl_budget_versions gl_budget_version,
gl_je_batches journal_batch,
gl_je_headers je2,
gl_je_headers je3,
gl_je_categories journal_category,
gl_je_sources journal_source,
gl_daily_conversion_types gl_conversion_type
WHERE je1.ledger_id = set_of_books.set_of_books_id
AND je1.encumbrance_type_id = gl_encumbrance_type.encumbrance_type_id(+)
AND je1.je_batch_id = journal_batch.je_batch_id
AND je1.parent_je_header_id = je2.je_header_id(+)
AND je1.reversed_je_header_id = je3.je_header_id(+)
AND je1.je_category = journal_category.je_category_name
AND je1.je_source = journal_source.je_source_name
AND je1.currency_conversion_type = gl_conversion_type.conversion_type
) o101399
GROUP BY o101399.journal_entry_name),
(SELECT o101395.period_name AS as107668_136855_old_2,
MAX (o101395.period_number) AS as107668_136855_old
FROM (SELECT gl_period.period_name, gl_period.period_num period_number
FROM gl_periods gl_period) o101395
GROUP BY o101395.period_name)
WHERE o101368.journal_entry_id = o101400.journal_entry_id
AND o101368.journal_batch_name = as107663_128801_old_2(+)
AND o101368.journal_entry_name = as107665_128817_old_2(+)
AND o101368.period_name = as107668_136855_old_2(+)
AND o101368.period_name IN (:P_PERIOD_NAME)
AND O101368.JOURNAL_SOURCE IN (:P_JOURNAL_SOURCE)
AND O101368.JOURNAL_CATEGORY IN (:P_JOURNAL_CATEGORY)
GROUP BY as107668_136855_old,
as107665_128817_old,
as107663_128801_old,
o101368.book_currency,
(o101368.converted_journal_total_dr
* o101368.currency_conversion_rate
),
o101368.converted_journal_total_cr * o101368.currency_conversion_rate,
o101368.currency,
o101368.currency_conversion_date,
o101368.currency_conversion_rate,
o101368.currency_conversion_type,
o101368.effective_date,
o101368.journal_batch_name,
o101368.journal_category,
o101368.journal_entry_name,
o101368.journal_source,
o101368.period_name,
o101368.reversal_period,
o101368.set_of_books_name,
o101400.company_50248,
o101400.account_50248,
o101400.interco_50248,
o101368.reverse_status_flag
ORDER BY o101368.set_of_books_name ASC,
o101368.currency_conversion_date ASC,
as107668_136855_old ASC,
o101400.account_50248 ASC,
o101368.currency ASC
(o101368.converted_journal_total_dr
* o101368.currency_conversion_rate
) AS c_2,
o101368.converted_journal_total_cr
* o101368.currency_conversion_rate AS c_3,
o101368.currency,
o101368.currency_conversion_date,
o101368.currency_conversion_rate,
o101368.currency_conversion_type,
o101368.effective_date,
o101368.journal_batch_name,
o101368.journal_category,
o101368.journal_entry_name,
LPAD(o101368.journal_entry_name,8) reval_reverse,
o101368.journal_source, o101368.period_name,
o101368.reversal_period,
o101368.set_of_books_name set_of_books_name,
o101400.company_50248 company, o101400.account_50248 account,
o101400.interco_50248 interco,
o101368.reverse_status_flag,
as107663_128801_old AS as107663_128801_old,
as107665_128817_old AS as107665_128817_old,
as107668_136855_old AS as107668_136855_old,
NVL(SUM(o101400.entered_cr), 0) AS e125430_sum,
NVL(SUM(o101400.entered_dr), 0) AS e125439_sum,
NVL(SUM(o101400.converted_cr), 0) AS e118546_sum,
NVL(SUM(o101400.converted_dr), 0) AS e118547_sum,
NVL(SUM(o101400.converted_dr), 0) - NVL(SUM(o101400.converted_cr), 0) net_converted
FROM (SELECT je1.je_header_id journal_entry_id, je1.NAME journal_entry_name, je1.ledger_id, set_of_books.NAME set_of_books_name,
journal_category.user_je_category_name journal_category,
journal_source.user_je_source_name journal_source, je1.period_name,
je1.currency_code currency, je1.default_effective_date effective_date,
DECODE (je1.tax_status_code,
'N', 'Not Required',
'R', 'Required',
'T', 'Taxed',
NULL
) tax_status,
je1.je_batch_id journal_batch_id, journal_batch.NAME journal_batch_name, je1.accrual_rev_effective_date reversal_date,
je1.accrual_rev_period_name reversal_period,
DECODE (je1.accrual_rev_status,
'M', 'Marked for Reversal',
'N', 'Not Reversed',
'R', 'Reversed',
'U', 'Unreversable',
NULL
) reverse_status_flag,
DECODE (DECODE (je1.accrual_rev_change_sign_flag,
'Y', 'C',
'N', 'S'
),
'C', 'Change Sign',
'S', 'Switch Dr/Cr',
NULL
) reversal_method,
je1.description description, je1.control_total control_total, je1.running_total_dr entered_journal_total_dr,
je1.running_total_cr entered_journal_total_cr, je1.running_total_accounted_dr converted_journal_total_dr,
je1.running_total_accounted_cr converted_journal_total_cr, je1.currency_conversion_rate currency_conversion_rate,
je1.currency_conversion_type currency_conversion_type_code,
gl_conversion_type.user_conversion_type currency_conversion_type,
je1.currency_conversion_date currency_conversion_date, je1.external_reference,
je1.parent_je_header_id primary_journal_entry_id, je2.NAME primary_journal_entry_name, je1.reversed_je_header_id reversed_journal_entry_id,
je3.NAME reversed_journal_entry_name, je1.doc_sequence_value document_number, je1.originating_bal_seg_value,
set_of_books.currency_code book_currency
FROM gl_je_headers je1,
gl_sets_of_books set_of_books,
gl_je_batches journal_batch,
gl_je_headers je2,
gl_je_headers je3,
gl_je_categories journal_category,
gl_je_sources journal_source,
gl_daily_conversion_types gl_conversion_type
WHERE je1.ledger_id = set_of_books.set_of_books_id
AND je1.je_batch_id = journal_batch.je_batch_id
AND je1.parent_je_header_id = je2.je_header_id(+)
AND je1.reversed_je_header_id = je3.je_header_id(+)
AND je1.actual_flag = 'A'
AND je1.je_category = journal_category.je_category_name
AND je1.je_source = journal_source.je_source_name
AND je1.currency_conversion_type = gl_conversion_type.conversion_type) o101368,
(SELECT journal_line.je_header_id journal_entry_id, journal_entry.NAME journal_entry_name,
journal_line.je_line_num journal_line_number,
journal_line.ledger_id set_of_books_id,
gl_set_of_books.NAME set_of_books_name, journal_line.code_combination_id account_id,
DECODE (gcc.chart_of_accounts_id,
50248, gcc.segment1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| gcc.segment4
|| '-'
|| gcc.segment5
|| '-'
|| gcc.segment6,
NULL
) ACCOUNT,
gcc.segment1 company_50248, gcc.segment2 account_50248, gcc.segment3 department_50248, gcc.segment4 region_50248,
gcc.segment5 function_50248, gcc.segment6 interco_50248, journal_line.period_name period_name,
journal_line.effective_date effective_date, journal_line.entered_dr entered_dr,
journal_line.entered_cr entered_cr, journal_line.accounted_dr converted_dr,
journal_line.accounted_cr converted_cr, journal_line.description description,
journal_line.stat_amount stat_amount, journal_line.subledger_doc_sequence_value subledger_document_number,
journal_line.reference_1, journal_line.reference_2,
journal_line.reference_3, journal_line.reference_4,
journal_line.reference_5, journal_line.reference_6,
journal_line.reference_7, journal_line.reference_8,
journal_line.reference_9, journal_line.reference_10,
journal_line.attribute_category line_context,
journal_line.attribute1 line_project_number_2,
journal_line.taxable_line_flag tax_tax_line, journal_line.tax_type_code tax_tax_type_3,
journal_line.tax_code_id tax_tax_code_3, journal_line.tax_rounding_rule_code tax_rounding_rule_3,
journal_line.amount_includes_tax_flag tax_amount_includes_tax_3,
journal_line.tax_document_identifier tax_document_identifier_3,
journal_line.tax_document_date tax_document_date_3, journal_line.tax_customer_name "TAX_CUSTOMER/VENDOR_NAME_3",
journal_line.tax_customer_reference "TAX_CUSTOMER/VENDOR_REFERENC_3",
journal_line.tax_registration_number tax_tax_registration_number_3,
journal_line.jgzz_recon_ref local_recon_ref
FROM gl_sets_of_books gl_set_of_books,
gl_je_lines journal_line,
gl_je_headers journal_entry,
gl_code_combinations gcc
WHERE journal_line.ledger_id = gl_set_of_books.set_of_books_id
AND journal_line.je_header_id = journal_entry.je_header_id
AND journal_line.code_combination_id = gcc.code_combination_id) o101400,
(SELECT o101398.journal_batch_name AS as107663_128801_old_2,
MAX (o101398.journal_batch_id) AS as107663_128801_old
FROM (SELECT journal_batch1.je_batch_id journal_batch_id, journal_batch1.NAME journal_batch_name
FROM gl_je_batches journal_batch1) o101398
GROUP BY o101398.journal_batch_name),
(SELECT o101399.journal_entry_name AS as107665_128817_old_2,
MAX (o101399.journal_entry_id) AS as107665_128817_old
FROM (SELECT je1.je_header_id journal_entry_id, je1.NAME journal_entry_name
FROM gl_je_headers je1,
gl_sets_of_books set_of_books,
gl_encumbrance_types_b gl_encumbrance_type,
--gl_budget_versions gl_budget_version,
gl_je_batches journal_batch,
gl_je_headers je2,
gl_je_headers je3,
gl_je_categories journal_category,
gl_je_sources journal_source,
gl_daily_conversion_types gl_conversion_type
WHERE je1.ledger_id = set_of_books.set_of_books_id
AND je1.encumbrance_type_id = gl_encumbrance_type.encumbrance_type_id(+)
AND je1.je_batch_id = journal_batch.je_batch_id
AND je1.parent_je_header_id = je2.je_header_id(+)
AND je1.reversed_je_header_id = je3.je_header_id(+)
AND je1.je_category = journal_category.je_category_name
AND je1.je_source = journal_source.je_source_name
AND je1.currency_conversion_type = gl_conversion_type.conversion_type
) o101399
GROUP BY o101399.journal_entry_name),
(SELECT o101395.period_name AS as107668_136855_old_2,
MAX (o101395.period_number) AS as107668_136855_old
FROM (SELECT gl_period.period_name, gl_period.period_num period_number
FROM gl_periods gl_period) o101395
GROUP BY o101395.period_name)
WHERE o101368.journal_entry_id = o101400.journal_entry_id
AND o101368.journal_batch_name = as107663_128801_old_2(+)
AND o101368.journal_entry_name = as107665_128817_old_2(+)
AND o101368.period_name = as107668_136855_old_2(+)
AND o101368.period_name IN (:P_PERIOD_NAME)
AND O101368.JOURNAL_SOURCE IN (:P_JOURNAL_SOURCE)
AND O101368.JOURNAL_CATEGORY IN (:P_JOURNAL_CATEGORY)
GROUP BY as107668_136855_old,
as107665_128817_old,
as107663_128801_old,
o101368.book_currency,
(o101368.converted_journal_total_dr
* o101368.currency_conversion_rate
),
o101368.converted_journal_total_cr * o101368.currency_conversion_rate,
o101368.currency,
o101368.currency_conversion_date,
o101368.currency_conversion_rate,
o101368.currency_conversion_type,
o101368.effective_date,
o101368.journal_batch_name,
o101368.journal_category,
o101368.journal_entry_name,
o101368.journal_source,
o101368.period_name,
o101368.reversal_period,
o101368.set_of_books_name,
o101400.company_50248,
o101400.account_50248,
o101400.interco_50248,
o101368.reverse_status_flag
ORDER BY o101368.set_of_books_name ASC,
o101368.currency_conversion_date ASC,
as107668_136855_old ASC,
o101400.account_50248 ASC,
o101368.currency ASC
What psycho would name their tables like this.
ReplyDelete