Introduction
This Post illustrates the steps required to do GL Revaluation Accounts in FUSION Application
Script to GL Revaluation Accounts
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
What we expect in the script.
This script helps us to how GL Revaluation Accounts working in FUSION.A couple of tables which is being used in the scripts are gl_je_headers,gl_je_lines,etc
Summary
This Post described the script for GL Revaluation Accounts Query in FUSION
Queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
