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