How to Setup, Map, and Print Custom Reports with a Barcode / MICR Font

Introduction

Introduced in XML Publisher 5.6.0 is a new tab named Administration. This replaces the xdo.cfg configuration file and its goal is to simply the administration of XML Publisher as part of an Oracle E-Business Suite environment

Most of the company's will have a check print report where we use custom MICR string defined by the bank.

To achieve this we need to do the following setups.

Step 1:  Decide Your MICR True Type Font

Decide the MICR font you are going to use (eg.,MICRE13B) . This font should be installed in your system in order to use this in the MS Word.

Step 2:  Design your rtf file

Design your rtf file, for the MICR string use the font that you installed, this can be chosen from the MS Word Drop fonts down

Step 3: Register your font in EBS

Register the font file that you have in XML Publisher Administration. Here are the steps to do it.

EBS Navigation - XML Publisher Administration ->Administration ->Font Files

Font Name: for Font Name, choose any descriptive name

File: File browse your font file in your machine. Click on Apply.

Step 4:  Create font mapping set

You have completed registering your font, Now you have to create the Font mappings for the font  you have created.

EBS Navigation - XML Publisher Administration ->Administration -> Font Mappings -->Create Font Mapping Set

Mapping name: Mapping name is the name you will give to a set of fonts.

Mapping code: Mapping code is the internal name you will give to this set

Type: 'PDF Form' for PDF templates. 'FO to PDF' for all other template types.

Step 5: Create Font Mapping

This allows you to add fonts to a set

Font Family: Font Family is the exact same name you see in MS Word under Font. If you don't use the same name the font will not be picked up at run time.

Style and weight: Style and weight must also match how you use the font in the RTF or PDF layout template. Normal and Normal are good defaults.

Language and Territory: Language and Territory should remain blank (NULL) unless you have a strong business reason, as these fields can cause the font not to be picked up at run time.

Step 6: Change the FO Processing Configuration window

You have created a font and included it to a mapping set. Now you have to change the configuration to set the Font mapping set to your mapping set.

Once this is done, all set and you are ready to go.

Run your payment/report and you should see the MICR string printed in the output.

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible.

Submit a Concurrent Program/Request from PL/SQL

Oracle has provided the feasibility to submit a concurrent request from backend using "fnd_request.submit_request" API.

Before submitting the API we need to set the environment and this can be done using "fnd_global.apps_initialize"

Here is a sample code to submit a concurrent program from PL/SQL

Note:- This code is to submit a Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have a different API.

/*********************************************************

*PURPOSE: To Submit a Concurrent Request from backend    *

*AUTHOR: Ragul Halan                                     *

**********************************************************/

--

DECLARE

   l_responsibility_id     NUMBER;

   l_resp_application_id   NUMBER;

   l_security_group_id     NUMBER;

   l_user_id               NUMBER;

   l_request_id            NUMBER;

BEGIN

   --

   -- Get the apps environment variables --

   --

   SELECT user_id, responsibility_id, responsibility_application_id,

          security_group_id

     INTO l_user_id, l_responsibility_id, l_resp_application_id,

          l_security_group_id

     FROM fnd_user_resp_groups

    WHERE user_id = (SELECT user_id

                       FROM fnd_user

                      WHERE user_name = '&USER_NAME')

      AND responsibility_id =

             (SELECT responsibility_id

                FROM fnd_responsibility_vl

               WHERE responsibility_name = '&RESP_NAME');

   --

   --To set environment context.

   --

   apps.fnd_global.apps_initialize (l_user_id,

                                    l_responsibility_id,

                                    l_resp_application_id

                                   );

   --

   --Submitting Concurrent Request

   --

   l_request_id :=

      fnd_request.submit_request (application      => 'XXCUST',         -- Application Short Name

                                  program          => 'XX_DEPT_DTLS',   -- Program Short Name

                                  description      => 'XX_DESCRIPTION', -- Any Meaningful Description

                                  start_time       => SYSDATE,          -- Start Time

                                  sub_request      => FALSE,            -- Subrequest Default False

                                  argument1        => 'Accounting'      -- Parameters Starting

                                 );

   --

   COMMIT;

   --

   IF l_request_id = 0

   THEN

      DBMS_OUTPUT.put_line ('Concurrent request failed to submit');

   ELSE

      DBMS_OUTPUT.put_line ('Successfully Submitted the Concurrent Request: '||l_request_id);

   END IF;

   --

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (   'Error While Submitting Concurrent Request '

                            || TO_CHAR (SQLCODE)

                            || '-'

                            || SQLERRM

                           );

END;

/

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible.

Find Oracle APPS User Password From Backend

In three steps we can find any user name’s password in oracle apps

Step 1. Create below package

This package is used to decrypt the user's password.

CREATE OR REPLACE PACKAGE get_user_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2;

END get_user_pwd;

/

CREATE OR REPLACE PACKAGE BODY get_user_pwd

AS

   FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)

      RETURN VARCHAR2

   AS

      LANGUAGE JAVA

      NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt

(java.lang.String,java.lang.String) return java.lang.String';

END get_user_pwd;

/

Step 2. Query to get password for apps user

SELECT (SELECT get_user_pwd.decrypt

                  (UPPER ((SELECT UPPER (fnd_profile.VALUE ('Guest_User_Pwd'))

                             FROM DUAL)

                         ),

                   usertable.encrypted_foundation_password

                  )

          FROM DUAL) AS apps_password

  FROM fnd_user usertable

 WHERE usertable.user_name LIKE

          UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('Guest_User_Pwd'),

                                 1,

                                   INSTR (fnd_profile.VALUE ('Guest_User_Pwd'),

                                          '/'

                                         )

                                 - 1

                                )

                    FROM DUAL)

                );

above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12

/***********************************************************************************/

/*          QUERY TO GET APPS USER PASSWORD FOR BACK END  IN R12  */

/***********************************************************************************/

ALTER SESSION SET current_schema = apps;

SELECT (SELECT get_user_pwd.decrypt

                    (fnd_web_sec.get_guest_username_pwd,

                     usertable.encrypted_foundation_password

                    )

          FROM DUAL) AS apps_password

  FROM fnd_user usertable

 WHERE usertable.user_name LIKE

          (SELECT SUBSTR (fnd_web_sec.get_guest_username_pwd,

                          1,

                          INSTR (fnd_web_sec.get_guest_username_pwd, '/') - 1

                         )

             FROM DUAL);

Step 3. Query to get password for application user

SELECT usertable.user_name,

       (SELECT get_user_pwd.decrypt

                  (UPPER

                      ((SELECT (SELECT get_user_pwd.decrypt

                                          (UPPER

                                              ((SELECT UPPER

                                                          (fnd_profile.VALUE

                                                              ('Guest_User_Pwd'

                                                              )

                                                          )

                                                  FROM DUAL)

                                              ),

                                           usertable.encrypted_foundation_password

                                          )

                                  FROM DUAL) AS apps_password

                          FROM fnd_user usertable

                         WHERE usertable.user_name LIKE

                                  UPPER

                                     ((SELECT SUBSTR

                                                 (fnd_profile.VALUE

                                                             ('Guest_User_Pwd'),

                                                  1,

                                                    INSTR

                                                       (fnd_profile.VALUE

                                                             ('Guest_User_Pwd'),

                                                        '/'

                                                       )

                                                  - 1

                                                 )

                                         FROM DUAL)

                                     ))

                      ),

                   usertable.encrypted_user_password

                  )

          FROM DUAL) AS encrypted_user_password

  FROM fnd_user usertable

 WHERE usertable.user_name LIKE UPPER ('&Username');

above query works only in 11i since Oracle removed ‘Guest User Password’ profile option from Release 12 so please use below query for R12

/***********************************************************************************/

/*          QUERY TO GET APPS USER PASSWORD FOR BACK END  IN R12  */

/***********************************************************************************/

ALTER SESSION SET current_schema = apps;

SELECT usr.user_name, usr.description,

       get_user_pwd.decrypt

          ((SELECT (SELECT get_user_pwd.decrypt

                              (fnd_web_sec.get_guest_username_pwd,

                               usertable.encrypted_foundation_password

                              )

                      FROM DUAL) AS apps_password

              FROM fnd_user usertable

             WHERE usertable.user_name =

                      (SELECT SUBSTR

                                  (fnd_web_sec.get_guest_username_pwd,

                                   1,

                                     INSTR

                                          (fnd_web_sec.get_guest_username_pwd,

                                           '/'

                                          )

                                   - 1

                                  )

                         FROM DUAL)),

           usr.encrypted_user_password

          ) PASSWORD

  FROM fnd_user usr

 WHERE usr.user_name = '&User_Name';