IN and NVL Function in Oracle BIP Data model SQL Query.

Here is the sample query where you can use IN clause and NVL Function in the SQL Query - where clause in the Oracle BIP report.

Ex : Say you have an input parameter with the Comma separated values, and the report should pull all the required values as per the input parameter or it should pull all the records when no value is provided.

Input Parameter -   :P_NAME

Properties -   Comma separated values

Sample Query

SELECT * FROM <TABLE>

WHERE ( 1 = DECODE(:P_Name,null,1,0) OR name IN (:P_Name))

This way it will pull all the records if no input parameter is specified or if any input parameter is specified it will retrieve only those records.

Sample General Ledger ( GL ) Journal - SQL Query - Oracle Cloud Fusion.

 

Here is the sample SQL Query to fetch the basic General Ledger( GL ) - SQL Query in the Oracle Cloud fusion. This query may be modified accordingly as per the requirements.

SELECT src.user_je_source_name,    

       cat.user_je_category_name,  

       hdr.name,                   

       batch.name,                 

       hdr.date_created,

       hdr.default_effective_date,

       hdr.status,                 

       hdr.currency_code,          

       line.je_line_num,           

       line.description,           

       line.entered_dr,

       line.entered_cr,

       line.accounted_dr,

       line.accounted_cr,

       line.currency_conversion_rate,

       line.currency_conversion_type,

       line.currency_conversion_date,

       gcc.segment1,

       gcc.segment2,

       gcc.segment3,

       gcc.segment4,

       gcc.segment5,

       gcc.segment6,

       gcc.segment7,

       gcc.segment8,

       gcc.segment9,

       gcc.segment10,

       batch.name                 Reference_1,

       batch.description          Reference_2,

       NULL                       Reference_3,

       hdr.name                   Reference_4,

       hdr.description            Reference_5,

       hdr.external_reference     Reference_6,

       hdr.accrual_rev_flag       Reference_7,

       hdr.accrual_rev_period_name       Reference_8,

       hdr.accrual_rev_change_sign_flag  Reference_9,

       line.description           Reference_10,

       ledger.name                

       gp.period_name             

  FROM gl_je_headers       hdr,

       gl_je_lines         line,

       gl_code_combinations gcc,

       gl_ledgers          ledger,

       gl_je_sources       src,

       gl_je_categories    cat,

       gl_je_batches       batch, 

       gl_periods          gp

 WHERE 1 = 1

   AND hdr.je_header_id = line.je_header_id

   AND line.code_combination_id = gcc.code_combination_id

   AND hdr.ledger_id = ledger.ledger_id

   AND line.ledger_id = ledger.ledger_id

   AND hdr.je_source = src.je_source_name

   AND hdr.je_category = cat.je_category_name

   AND hdr.je_batch_id = batch.je_batch_id

   AND hdr.period_name = gp.period_name

   AND ledger.period_set_name = gp.period_set_name