Query to get GL Account Hierarchy Query in fusion

Below is the query used in the fusion to know about the GL account Hierarchy

Common tables unsed int eh GL Hierarchy 

1.

SELECT DISTINCT segment_name,
                application_column_name
FROM   fnd_flex_values_vl f,
       fnd_id_flex_segments fifs
WHERE  f.flex_value_set_id IN (SELECT flex_value_set_id
                               FROM   fnd_id_flex_segments
                               WHERE  application_id = 101
                                      AND id_flex_code = 'GL#'
                                      AND enabled_flag = 'Y'
                                      AND application_column_name IN (
                                          'SEGMENT1', 'SEGMENT2', 'SEGMENT3',
                                          'SEGMENT4',
                                          'SEGMENT5', 'SEGMENT6', 'SEGMENT7' )
                              ---Account Segment
                              )
       AND fifs.flex_value_set_id = f.flex_value_set_id
--         AND flex_value = 21801 

Query to get Journal Details in fusion

 Here we have query to get the journal details in fusion 

SELECT gjb.NAME                  batch_name,
       gjh.period_name,
       gjh.status                je_header_status,
       gjh.NAME                  je_header_name,
       gjh.creation_date         je_header_creation_date,
       gjh.description           je_header_description,
       gjh.je_category,
       gjh.je_source,
       gjh.posted_date           je_posted_date,
       gjh.post_currency_code,
       gl.NAME                   ledger_name,
       gjl.je_line_num,
       gjl.effective_date        accounting_date,
       gjl.creation_date         line_creation_date,
       Nvl (gjl.entered_dr, 0)   entered_dr,
       Nvl (gjl.entered_cr, 0)   entered_cr,
       Nvl (gjl.accounted_dr, 0) accounted_dr,
       Nvl (gjl.accounted_cr, 0) accounted_cr,
       gjl.description           line_description,
       gcc.segment1              legal_entity,
       gcc.segment3              account,
       gl_flexfields_pkg.Get_description_sql (gcc.chart_of_accounts_id,
       --- chart of account id
       1, ----- Position of segment
       gcc.segment1 ---- Segment value
       )                         legal_entity_desc,
       gl_flexfields_pkg.Get_description_sql (gcc.chart_of_accounts_id,
       --- chart of account id
       3, ----- Position of segment
       gcc.segment3 ---- Segment value
       )                         account_desc,
       (SELECT DISTINCT display_name
        FROM   gl_je_action_log gja,
               per_users pu,
               per_person_names_f panf
        WHERE  gja.je_batch_id = gjb.je_batch_id
               AND panf.person_id = pu.person_id
               AND gja.user_id = pu.username
               AND panf.name_type = 'GLOBAL'
               AND rownum = 1)   entered_by
FROM   gl_je_headers gjh,
       gl_je_batches gjb,
       gl_ledgers gl,
       gl_je_lines gjl,
       gl_code_combinations gcc
WHERE  1 = 1
       --         AND gjh.name = 'Jan-19 Purchase Invoices'
       --         AND gjb.name = 'Payables A 23468000001 23468 N'
       AND gjh.je_batch_id = gjb.je_batch_id
       AND gl.ledger_id = gjh.ledger_id
       AND gjl.je_header_id = gjh.je_header_id ---POSTED
       AND gcc.code_combination_id = gjl.code_combination_id
       AND gjh.je_source = Nvl (:p_je_source, gjh.je_source)
       AND gl_flexfields_pkg.Get_description_sql (gcc.chart_of_accounts_id,
           --- chart of account id
           1, ----- Position of segment
           gcc.segment1 ---- Segment value
           ) IN ( :p_legal_entity )
       AND gjh.NAME = Nvl (:p_journal_name, gjh.NAME)
       AND gjh.creation_date BETWEEN :p_from_date AND :p_to_date
ORDER  BY gjh.creation_date,
          gjh.NAME,
          gjl.je_line_num 

Query to get AR Invoice Details from SLA & GL in fusion

Below I have attached a sample query to get the AR Invoice details from SLA & GL

SELECT gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6 || '-' || gcc.segment7 || '-' || gcc.segment8 cc,
--null Journal_Excel_Row,
GJH.NAME Je_Name,
--GJH.NAME || ' (' || gjl.JE_LINE_NUM || ')' Journal_NAME1,
xect.name event_class,
xett.NAME event_type,
to_char(xe.EVENT_DATE, 'mm/dd/yyyy') event_date,
(
SELECT MEANING
FROM xla_lookups
WHERE LOOKUP_typE = 'XLA_ACCOUNTING_CLASS'
AND lookup_code = xal.accounting_class_code
) accounting_class,
Nvl(xal.entered_dr, 0) Ent_Dr,
Nvl(xal.entered_cr, 0) Ent_Cr,
Nvl(xal.accounted_dr, 0) Acc_Dr,
Nvl(xal.accounted_cr, 0) Acc_Cr,
nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0) ent_net,
nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) acc_net,
'Customer Name' cp1,
(
SELECT DISTINCT hp.party_name
FROM hz_cust_accounts hca,
ra_customer_trx_all rct,
hz_parties hp
WHERE hca.cust_account_id = rct.bill_to_customer_id
AND rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND hp.party_id = hca.party_id
AND ROWNUM = 1
) cv1,
'Customer Number' cp2,
(
SELECT DISTINCT account_number
FROM hz_cust_accounts hca,
ra_customer_trx_all rct
WHERE hca.cust_account_id = rct.bill_to_customer_id
AND rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv2,
'Invoice Number' cp3,
(
SELECT DISTINCT trx_number
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv3,
'Invoice Document Number' cp4,
(
SELECT DISTINCT to_char(DOC_SEQUENCE_VALUE)
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv4,
'Invoice Date' cp5,
(
SELECT DISTINCT to_char(trx_date, 'mm/dd/yyyy')
FROM ra_customer_trx_all rct
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND ROWNUM = 1
) cv5,
'Transaction Type' cp6,
(
SELECT invt.name
FROM ra_customer_trx_all rct,
ra_cust_trx_types_all invt
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND invt.cust_trx_type_seq_id = rct.cust_trx_type_seq_id
AND ROWNUM = 1
) cv6,
'Batch Source' cp7,
(
SELECT rbs.name
FROM ra_customer_trx_all rct,
ra_batch_sources_all rbs
WHERE rct.customer_trx_id = xte.SOURCE_ID_INT_1
AND rct.batch_source_seq_id = rbs.batch_source_seq_id
AND ROWNUM = 1
) cv7,
NULL cp8,
NULL cv8,
NULL cp9,
NULL cv9,
NULL cp10,
NULL cv10,
xal.CREATED_BY,
xal.LAST_UPDATED_BY,
xal.CURRENCY_CODE,
nvl(xal.CURRENCY_CONVERSION_RATE, 1) rate,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gjh.DOC_SEQUENCE_VALUE
FROM gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc,
gl_import_references gir,
xla_ae_lines xal,
xla_ae_headers xah,
ledgers gl,
periods gp,
xla_events xe,
xla_transaction_entities xte,
XLA_EVENT_TYPES_TL xett,
XLA_EVENT_CLASSES_TL xect
WHERE 1 = 1
AND gjh.period_name = gp.period_name
AND gjh.je_header_id = gjl.je_header_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
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 gjl.STATUS = 'P'
AND gjh.je_source = 'Receivables'
AND gjh.ledger_id = gl.ledger_id
AND gjh.je_category IN (
'Sales Invoices',
'Contract Invoices',
'Credit Memos'
)
AND xah.application_id = xe.application_id
AND xah.event_id = xe.event_id
AND xe.application_id = xte.application_id
AND xe.entity_id = xte.entity_id
AND xett.APPLICATION_ID = xe.APPLICATION_ID
AND xett.EVENT_TYPE_CODE = xe.EVENT_TYPE_CODE
AND xett.LANGUAGE = userenv('LANG')
AND xett.APPLICATION_ID = xect.APPLICATION_ID
AND xect.EVENT_CLASS_CODE = xett.EVENT_CLASS_CODE
AND xect.LANGUAGE = userenv('LANG')
AND (
gcc.segment1 IN (:p_company)
OR 'All' IN (:p_company || 'All')
)
AND (
gcc.segment2 IN (:p_department)
OR 'All' IN (:p_department || 'All')
)
AND (
gcc.segment4 IN (:p_product)
OR 'All' IN (:p_product || 'All')
)
AND (
gcc.segment5 IN (:p_cost_center)
OR 'All' IN (:p_cost_center || 'All')
)
AND (
gcc.segment6 IN (:p_location)
OR 'All' IN (:p_location || 'All')
)
AND (
gcc.segment7 IN (:p_intercompany)
OR 'All' IN (:p_intercompany || 'All')
)
AND (
gcc.segment8 IN (:p_future1)
OR 'All' IN (:p_future1 || 'All')
)
AND gcc.segment3 >= nvl(:p_account_from, gcc.segment3)
AND gcc.segment3 <= nvl(:p_account_to, gcc.segment3)
AND (
gcc.segment3 IN (:p_acc)
OR 'All' IN (:p_acc || 'All')
)
AND (
CASE 
WHEN :p_translated_flag = 'ENTERED'
AND gjl.CURRENCY_CODE = :p_currency_code
THEN 1
WHEN :p_translated_flag = 'TOTAL'
AND gl.currency_code = :p_currency_code
THEN 1
WHEN :p_translated_flag = 'TOTAL'
AND gl.ledger_id IN (
SELECT ledger_id
FROM trans_flag
)
THEN 1
END
) = 1

How to change User-Level time zone for BIP Reports

Issue:

When we run the report some time we will face a few of the transactions that will not come up in the report if we give one day prior to their transaction date in the report then we will get the record in the report.

How to overcome this issue:

we can make setup changes in the two levels

1.At the administration level, if we change it here it will impact all of the reports.

2.We can change it to a specific report, this will have an impact on the particular report only.

How to change at the administration level:

Navigation-->Administration-->Manage BI Publisher-->Runtime Configuration-->Report Timezone

in the Report Timezone, we will have a two values

1.JVM

2.User 

By default, it is pointing to the user level, now we will select JVM so this change will apply to all of the reports.

How to change the report:

If we change the report this will have an impact on the specif report only.

Below are the steps to follow.

1.Select a report and click on the edit reprot 

2.Click on the properties of the report 

Then click on the LOV and select the JVM and the save it .

once you save and run the report for the desired date and then you should able to see the transactions in the report.

These are the  2 ways we can set the User-Level time zone for the reports.

Thank You....