HOW to make QR Code Report using BI Publisher and Oracle Report in R12 of Oracle apps. How to generate QR code in Oracle EBS XML Report. How to display barcode on Oracle BI Publisher report

 HOW to make QR Code Report using BI Publisher and Oracle Report in R12 of Oracle apps. 

How to generate QR code in Oracle EBS XML Report. 

How to display barcode on Oracle BI Publisher report.

We have made the QR Code report for Service Sales Invoice in Receivable

Step1:

Query for Select (HUGEBLOB) file.

select qr_code_image--apps.xxply_get_base64 (qr_code_image) IMAGE_FILE

         from apps.jai_irn_details jid , apps.jai_tax_det_factors jtd

         WHERE jid.DET_FACTOR_ID=jtd.det_factor_id 

         --and jtd.trx_id = :p_delivery_id

         and JTD.TRX_NUMBER = :p_invoice_no

         --and jtd.entity_code ='SALES_ORDER_ISSUE'

         and jtd.entity_code ='TRANSACTIONS'

         and rownum =1

 

Step2:

Made the Function to select (qr_code_image) using below feature

DBMS_LOB.createtemporary (lob_loc => v_result, CACHE => FALSE, dur => 0);

   wf_mail_util.encodeblob (p_source, v_result);

 

Create DB Funtion:

CREATE OR REPLACE FUNCTION APPS.xxply_get_base64 (p_source BLOB)

   RETURN CLOB

IS

   v_result   CLOB;

BEGIN

   DBMS_LOB.createtemporary (lob_loc => v_result, CACHE => FALSE, dur => 0);

   wf_mail_util.encodeblob (p_source, v_result);

   RETURN (v_result);

EXCEPTION

   WHEN OTHERS

   THEN

      RETURN NULL;

END;

/

 

Step3:

Then Make the Select Statement Query for Report Using RDF Report in Oracle Report.

 

SELECT project_number, project_id, invoice_no, invoice_date, customer_name,

       address_id, ship_to_address_id, customer_id, client_bbu_no,

       client_bbu_id, clientbill_hdr_id,instruction,project_inv_org,organization_id,

(select apps.xxply_get_base64 (qr_code_image) IMAGE_FILE

         from apps.jai_irn_details jid , apps.jai_tax_det_factors jtd

         WHERE jid.DET_FACTOR_ID=jtd.det_factor_id 

         --and jtd.trx_id = :p_delivery_id

         and JTD.TRX_NUMBER = :p_invoice_no

         --and jtd.entity_code ='SALES_ORDER_ISSUE'

         and jtd.entity_code ='TRANSACTIONS'

         and rownum =1

         ) IMAGE_FILE

  FROM pwcss_clientbill_hdr_v

 WHERE invoice_no = :p_invoice_no

 

 

According to the Above Query We found the ‘IMAGE_FILE’ as the QR_CODE in text format.

 

Add an Formula Column as ‘CF_12’ for printing in the RTF file. If we found value >0

function CF_12Formula return Number is

v_num number;

begin

  select length(xxply_get_base64 (qr_code_image)) IMAGE_FILE

    into v_num

    from jai_irn_details jid , jai_tax_det_factors jtd

    WHERE jid.DET_FACTOR_ID=jtd.det_factor_id 

    --and jtd.trx_id = :p_delivery_id

      and JTD.TRX_NUMBER = :p_invoice_no

    --and jtd.entity_code ='SALES_ORDER_ISSUE'

      and jtd.entity_code ='TRANSACTIONS'

       and rownum =1; 

 

 

if v_num is null then

    return 0;

else

    return v_num;

end if;

exception when others then return 0;

   

end;

 

 

 

 

Step4:

Then Make the RTF Template Using RDF Report XML Tag.

On the Top Paste the image

IMAGE_FILE

Code in IMAGE_FILE :

<?if:CF_12> 0?>

<fo:instream-foreign-object content-type=”image/png” height=”1.5 in” width=”1.5 in”>

<xsl:value-of select=”IMAGE_FILE”/>

</fo:instream-foreign-object>                  

<?end if?>