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....

SQL for journal details in fusion

 Here we have a query to get the journal details in oracle 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

How to generate QR Codes through BIP Report

1.Prepare SQL  logic to design QR Code

for example: if I scan my QR code it should have the details about my purchase order & order date

Sample SQL:

select 
'Purchase Order Number: '||pha.segment1||chr(10)||
'Order Date: '||to_char(pha.creation_Date,'MM/dd/yyyy') qr_code,
pha.segment1 po_number,to_char(pha.creation_Date,'MM/dd/yyyy') order_Date from 
po_headers_All pha
order by pha.creation_date desc

2. we need to create  RTF and to convert the XML field to QR code format we need to use the below tag

Syntax:

<?qrcode: <DATA>; <SIZE>[; <CHARSET>]?>

Example:

<?qrcode:QR_CODE; 100?>
where
DATA – Data to be encoded in the QR code format.
SIZE – QR code size dimension in points(pt).
CHARSET – (Optional) Character set for encoding the data. Default is UTF8.

I have data present in the "QR_CODE"  column in the data model and I have given the QR Code SIZE as 100.

I have ignored the CHARSET as it is optional.

3. Create a rtf and upload it to the system as shown below 

4.Run the report and your QR XML field data is converted to "QR Code" format and we can scan the QR Codes.

We can see in the report we have QR Code is generated and those can be scanned by any scan devices 

By above procedure can can generate the QR Codes.

Please leave a comment if you have any questions.

Thank You..