Oracle Payroll Run Results

Payroll generally runs for a consolidation set/assignment set OR payroll can be run for a particular employee assignment using quick pay. Whatever the process we follow to run the payroll, the elements attached to the employee assignment will be calculated based on the fast formula attached to it and returns values to the respective formula results. In this whole process of payroll run, the elements, database items, balances with its dimensions etc...are being assigned with the run result values. These run result values can be found in both run results after payroll run OR Statement of Earnings (SOE) report and dimension values in balances and so on.

Concurrent Program to RUN payroll:

This concurrent program is to run the payroll for a SET. Quick pay run is shown below.
The concept of calculating run result values/pay values is same for the SET/group payroll run and the quick pay. 

Once the ‘Payroll Run’ completes successfully, we can view run results/element pay values/balance dimension values/database item values.

Now, the question is ‘How to get the run result values of a payroll run using SQL queries’?

We will see how to check these values from the front end application before discussing the corresponding technical queries.

How to check run result values from the front end application?
Go to HRMS responsibility – View – Payroll Process results

To find all the payroll runs for a particular payroll in between the given range of period and with certain action type. (Table: pay_payroll_actions)

Note: Sensitive information in the forms is hidden

Assignment Process:

Balances: Click on this button to view calculated balance values

FYR: _ASG_PROC_YTD, _ASG_YTD, _ASG_RUN…etc are balance dimensions

View Results: Click on this button to view element pay values

Navigation to view results at assignment level: 

View – Assignment Process Results

Click on corresponding buttons to view respective run results for each assignment in the given period. (Table: pay_assignment_actions)

Quick Pay: Employee People screen – Assignment – Others – Quick Pay

You can run the quick pay and view results from this form. The data is same as above but specifically for a particular person

Quick Pay before processing:

Status is unprocessed and View results button is disabled

Quick Pay after processing:

Status is completed and View results button is enabled

Queries

Example 1:

If the requirement is to fetch run result values (like element pay values after the payroll run) for a particular payroll on particular month.

Get the Payroll ID using the payroll name using pay_payrolls_f

select * from pay_payrolls_f where payroll_name='XYZ'

Get payroll_action_id from pay_payroll_actions using the effective_date which is payroll run date

select * from pay_payroll_actions where payroll_id=64 order by effective_date desc (say 26-Nov-2015 is the effective date-payroll run)

Use the payroll action ID in the below query to get run result value of each element (Direct and Indirect) after the payroll run (Monthly/Weekly).

Query to get run results:

SELECT    papf.employee_number,

           papf.full_name,

           paaf.assignment_number,

           paaf.normal_hours,

           petf.element_name,

           piv.name,

           prrv.result_value

    FROM   pay_run_result_values prrv,

           pay_input_values_f piv,

           pay_run_results prr,

           pay_assignment_actions paa,

           per_all_assignments_f paaf,

           per_all_people_f papf,

           pay_element_types_f petf,

           pay_payrolls_f ppf

   WHERE   paa.payroll_action_id = 2942805 --Payroll action ID         

     AND prrv.input_value_id = piv.input_value_id

     AND prr.run_result_id = prrv.run_result_id

     AND prr.assignment_action_id = paa.assignment_action_id

     AND paa.assignment_id = paaf.assignment_id

     AND paaf.person_id = papf.person_id

     AND prr.element_type_id = petf.element_type_id

     --AND prr.element_type_id = 900  -- Particular element name

     --AND piv.name = 'Pay Value'

     AND paaf.payroll_id = ppf.payroll_id

     AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date

                           AND  ppf.effective_end_date

     AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date

                           AND  papf.effective_end_date

     AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date

                           AND  paaf.effective_end_date

Example 2:

Get any balance value (for example Balances from SOE report).

-- get defined balance ID

 SELECT   defined_balance_id

   FROM   pay_defined_balances pdb,

          pay_balance_dimensions pbd,

          pay_balance_types_tl pbt_tl,

          pay_balance_types pbt

  WHERE   PBT_TL.balance_type_id = pbt.balance_type_id

 -- AND userenv('LANG') = pbt.language

    AND PBT_TL.balance_name = :p_balance_type

    AND NVL (PBT.legislation_code, :l_legislation_code) =

                :l_legislation_code

    AND PDB.balance_type_id = pbt.balance_type_id

    AND PBD.balance_dimension_id = PDB.balance_dimension_id

    AND NVL (PDB.legislation_code, :l_legislation_code) =  :l_legislation_code

   AND PBD.database_item_suffix = :p_dimension_suffix

Parameters:

Balance Type: Gross Pay

Legislation Code: GB

Dimension: _ASG_YTD

    Change these values depends on your requirement. 

    Above example is to get ‘Gross_YTD’ – Gross Pay Year to Date.

    Get assignment action id from pay_assignment_actions using payroll_action_id (refer in Example1)

     Get Balance value using defined balance ID and assignment action ID:

     pay_balance_pkg.get_value(p_defined_balance_ID,p_assignment_action_id)

     -- Example to get balance value

     select

     pay_balance_pkg.get_value(241,108585390)

     from dual;

Refer Statement of Earnings post published in August 2013 for fetching other values of SOE report. 

to be contd…

Please do suggest or comment if you have any concerns regarding this post.

Thank you.

Regards,

Rajesh

Oracle API User Hooks

Hi All,

Happy Diwali :)

I would like to give you some idea on Oracle User hooks.

You might have come across situations wherein we must modify the Oracle standard API to get some info into custom tables/extra validations/to send alerts/etc. However, Oracle doesn’t recommend modifying the standard API.

What is API?

API, an abbreviation of Application Program Interface, is a set of routines, protocols, and tools for building software applications. The API specifies how software components should interact and APIs are used when programming graphical user interface (GUI) components.

What is User Hook?

User Hook, is a pl/sql code used to execute from the standard API. 

Oracle has supplemented the core product API functionality by the addition of API user hooks.

When the API processing reaches a user hook, core product processing stops and any customer specific logic for that event is executed. Then, if no errors occur, the main API processing resumes.

You can use such extra logic to add functionality not supplied directly by Oracle Applications.

NOTE: You should not manually edit the API source code files supplied by Oracle If you do modify these codes, Oracle Applications will be unable to support the product, and upgrades may not be possible. Oracle Applications only supports direct calls to the published APIs. Direct calls to any other server-side package procedures or functions that are written as part of the Oracle HRMS Applications suite are not supported.

Hence, the conclusion is User hook is used to add extra logic to the Business APIs using User Hooks

Below diagram shows the overview of how the custom logic executes in the Oracle standard API:

User Hook Tables:

HR_API_HOOKS - Contains all the API hooks

HR_API_MODULES – Module list in which hooks available

Steps to Implementing User Hooks:

There are basically 4 steps to implementing API User Hooks.

    1. Choose the API you wish to hook some extra logic to.

    2. Write the PL/SQL procedure that you wish to be called by the hook.

    3. Register or associate the procedure you have written with one or  more specific user hooks.

    4. Run the pre-processor program which builds the logic to execute your      PL/SQL procedure from the hook specified in 3.

Note: Oracle has not given the user hook option to all the forms OR APIs. 

HR_API_HOOKS table gives the hook details

Use HR_API_MODULES table to get the API names (Module Package) and the procedure name (Module Name) with respective Module Type.

A list of each API, it's hooks, and the parameters available to a procedure called by that hook, can be obtained by running the script 

$PER_TOP/admin/sql/hrahkpar.sql.

TYPES of User Hook:

There are 5 different types of User Hooks (Module Type in the HR_API_MODULES):

> Business Process APIs:

        1. AD – After Delete

        2. AI – After Insert

> Row Handler APIs:

        1. AP – After Process

        2. AU – After Update

        3. BP – Before Process

Example:

Business Process User Hooks:

The Business Process hooks should be used if extra logic is required on top of the standard business process logic. For instance, CREATE_EMPLOYEE, UPDATE_ELEMENT_ENTRY,etc. 

A full list can be obtained by running the following script:

SELECT module_name 

  FROM  hr_api_modules 

WHERE  api_module_type='BP'

The two types of Business Process hook available are:

Before Process - These hooks execute logic before the main API logic. The                 majority of validation will not have taken place. No database changes will have been made.

After Process  - These hooks will execute after the main API validation has                 completed and database changes made. If the main validation failed then the user hook will not be called.

The following sql retrieves the hook details of the After Process hook for the CREATE_EMPLOYEE business process.

SELECTahk.api_hook_id,

           ahk.api_module_id,

           ahk.hook_package,

           ahk.hook_procedure

  FROM hr_api_hooks ahk,

           hr_api_modules ahm 

WHERE ahm.module_name='CREATE_EMPLOYEE'

           and ahm.api_module_type = 'BP'

           and ahk.api_hook_type = 'AP'

           and ahk.api_module_id=ahm.api_module_id;

Row Handler User Hooks:

The Row Handler hooks should be used if extra logic is required prior to

performing an Insert,Update or Delete on a specific table. As all the main APIs

call the row handlers, these hooks would be executed by any API that updates

the specific table. 

A full list of the Row Handler APIs can be obtained by running the following sql:

SELECT module_name 

  FROM  hr_api_modules

WHERE api_module_type='RH';

The following sql retrieves hook details for the After Insert hook for the PER_ALL_PEOPLE_F table:

SELECT ahk.api_hook_id,

            ahk.hook_package,

            ahk.hook_procedure

  FROM  hr_api_hooks ahk,

            hr_api_modules ahm

WHERE (ahm.module_name='PER_ALL_PEOPLE_F'

            OR ahm.module_name='PER_PEOPLE_F')

     AND ahm.api_module_type = 'RH'

     AND ahk.api_hook_type = 'AI'

     AND ahk.api_module_id=ahm.api_module_id;

Core Product Logic

Core Product Logic is split into a number of components. For tables that can be altered

by an API there is an internal row handler code module. These rows handlers are

implemented for nearly all the tables in the system where APIs are available. They

control all the insert, update, delete and lock processing required by the main APIs. For

example, if a main API needs to insert a new row into the PER_ALL_PEOPLE_F table it

will not perform the DML itself. Instead it will execute the PER_ALL_PEOPLE_F row handler

Using User Hooks

After choosing the type of hook required and the location for it, the hook code

has to be written. It then needs to be registered, and finally the hook package

has to be modified to call it. The example used in this section describes the 

implementation of an After Process hook in the CREATE_EMPLOYEE Business Process API.

Writing the custom PL/SQL procedure

The hook code must be written in a PL/SQL server-side package procedure. The

procedure is always called if registered (unless the main validation logic

errors first), and, any conditional logic must be implemented in the code

and an application error raised if required. 

NOTE: No commits or rollbacks are allowed in the hook procedure. These are always 

performed after the API has been called whether it be in a PL/SQL wrapper or 

form.

When the PL/SQL package has been created, it must be compiled successfully

on the database.

Example:

CREATE OR REPLACE PACKAGE scoop_nationality_check AS

PROCEDURE polish_name_check

                  (p_last_name         in  VARCHAR2

                  ,p_nationality       in   VARCHAR2);

END scoop_nationality_check;

/

CREATE OR REPLACE PACKAGE BODY scoop_nationality_check AS

PROCEDURE polish_name_check

                  (p_last_name        in  VARCHAR2

                  ,p_nationality       in  VARCHAR2)

IS

BEGIN

-- When the first name entered is a polish name then check that the

-- nationality entered is Polish

IF p_last_name = 'Chrosicka' THEN

   IF p_nationality != 'POL'  THEN

     dbms_standard.raise_application_error (num => -20999

                                                                ,msg => 'Nationality must be Polish');

     END IF;

END IF;

END polish_name_check;

END scoop_nationality_check;

/

Registering the User Hook

The next step is to link the custom package procedure created above

DECLARE

l_api_hook_call_id           NUMBER;

l_object_version_number NUMBER;

BEGIN

hr_api_hook_call_api.create_api_hook_call

 (p_validate                        => FALSE,

  p_effective_date              => TO_DATE('01-JUL-1999','DD-MON-YYYY'),

  p_api_hook_id                  => 63,

  p_api_hook_call_type       => 'PP',

  p_sequence                      => 3000,

  p_enabled_flag                 => 'Y',

  p_call_package                 => 'SCOOP_NATIONALITY_CHECK',

  p_call_procedure              => 'POLISH_NAME_CHECK',

  p_api_hook_call_id           => l_api_hook_call_id,

  p_object_version_number => l_object_version_number);

EXCEPTION WHEN OTHERS

 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,300)); -- Handle exception

END;

Points to be noted:

   a. See section ‘Business Process User Hooks’ to get api_hook_id. (Refer above queries)

   b. The hook mechanism only supports calls to package procedures currently

      so api_hook_call_type must be PP.

   c. Sequence numbers > 2000 are recommended, as sequences < than 2000 are

      reserved for Oracle seeded logic which needs to be processed first.

      Please note that you can add any number of hooks to the same API, and

      order them using the sequence.

To Delete the user hook:

DECLARE

l_api_hook_call_id           NUMBER := 2;

l_object_version_number NUMBER := 3;

BEGIN

hr_api_hook_call_api.delete_api_hook_call

 (p_validate                        => FALSE,

  p_api_hook_call_id           => l_api_hook_call_id,

  p_object_version_number => l_object_version_number);

EXCEPTION WHEN OTHERS

 DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,300)); -- Handle exception

END;

The above call is deleting a specific user hook. Please note any changes made to the HR_API_HOOK_CALLS table, be they via the supplied procedures or not,

will only be effective once the pre-processor has been run.

Running the Pre-Processor

Adding rows to the HR_API_HOOK_CALLS table by itself is not sufficient to

enable this extra logic to be called. The pre-processor program must be called

first. This will look at the definitions in the table and build another package

body in the database which is known as the hook package body.

If successful, the pre-processor will hard code the calls to the custom package procedures into the hook package body. If no extra logic is implemented the hook package body is 

still created but without any calls to custom procedures.

During upgrades the pre-processor program will be automatically called to create

the hook package bodies.

To run the pre-processor run one of the following commands:

cd $PER_TOP/admin/sql

Log into SQLPLUS as the APPS user

SQL> @hrahkall.sql

OR

SQL> @hrahkone.sql

NOTE: The first script will create all hook package bodies, whilst the second will create hook package bodies for one API module only, and prompt for that api_module_id. Refer to the results of the query executed in above(first query) to obtain the api_module_id.

Tips on resolving Invalid Hook Packages

Occasionally when applying patches Hook Packages may fail to compile. This could

be for a variety of reasons. Running the report $PER_TOP/patch/115/sql/hrahkall.sql

will provide a comprehensive list of the invalid hook packages. 

Sometimes the problem may simply be resolved by reapplying the hook package

Refer HRMS Implementation guide for examples

To find the code used by a User Hook

You can use the hook name and type from the error message:

SELECT api_hook_id, hook_procedure 

   FROM hr_api_hooks

 WHERE api_hook_type = 'AP'

     AND hook_procedure LIKE  'UPDATE_ORG_INFORMATION%';

SELECT call_package, call_procedure, legislation_code

  FROM  hr_api_hook_calls

 WHERE api_hook_id = 5012; -- Got this value from the above query

The following query will provide you the additional information:

SELECT *

FROM   hr_api_hook_calls

WHERE api_hook_id IN (SELECT api_hook_id

                                        FROM hr_api_hooks

                                      WHERE api_module_id = (SELECT api_module_id

                                                                               FROM hr_api_modules

                                                                             WHERE module_name LIKE  'UPDATE_ORG_INFORMATION%'));

*************************************************************************************************

Please refer the below unpublished metalink note OR HRMS implementation guide for more info on User hooks:

> Understanding and Using Application Program Interface (API) User Hooks in Oracle HRMS [ID 73170.1]

> Refer ‘API User Hooks’ section in Oracle HRMS Implementation Guide

List of APIs

The following APIs support all the API event points (Before process; After process) :

applicant assignment

offer_apl_asg

update_apl_asg

contact relationship

create_contact_relationship

delete_contact_relationship

employee

actual_termination_emp

final_process_emp

create_employee

employee assignment

activate_emp_asg

suspend_emp_asg

final_process_emp_asg

update_emp_asg

actual_termination_emp_asg

employee assignment criteria

update_emp_asg_criteria

grade rate value

create_grade_rate_value

update_grade_rate_value

delete_grade_rate_value

job requirement

create_job_requirement

mass moves

mass_moves

pay scale value

create_pay_scale_value

update_pay_scale_value

delete_pay_scale_value

person address

create_person_address

update_person_address

personal payment method

create_personal_payment_method

update_personal_payment_method

position

create_position

update_position

position requirement

create_position_requirement

secondary applicant assignment

create_secondary_apl_asg

secondary employee assignment

create_secondary_emp_asg

Row Handlers

The Row Handlers on the following tables support all the Row Handler event points (After insert; After update; After delete) :

PER_ADDRESSES

PER_ALL_PEOPLE_F

PER_ALL_ASSIGNMENTS_F

PAY_PERSONAL_PAYMENT_METHODS_F

PER_POSITIONS

PER_APPLICATIONS

PER_CONTACT_RELATIONSHIPS

This document is for my future reference and posting for others to share the knowledge. Please do suggest or comment which helps all.

Thank you.

Regards,

Rajesh

Barcode Generation using the data outbound TSV (Tab Separated Value) file-Display row values as column wise

There are so many ways to convert oracle rows to columns.

Those are

• PIVOT operator
• XMLAGG function
• SYS_CONNECT_BY_PATH operator
• CROSS JOIN
• STRAGG function
• LAG OVER function
• CASE Operator
• DECODE using RANK Operator and may be more…

I have tried only few among the above and am going to give an example using DECODE and RANK/ROW_NUMBER functions.

Example:

a) Retrieve the data of expenses raised by an employee (row wise display).

SELECT report_header_id, employee_id, invoice_num

  --,(rank() over (partition by employee_id order by report_header_id)) seq,

  -- (row_number() over (partition by employee_id order by report_header_id))

FROM   ap_expense_report_headers_all

 WHERE employee_id = 11923

b) Retrieve the same data in column wise.

SELECT   employee_id, MAX (DECODE (seq, 1, invoice_num, NULL)) expense_1,

         MAX (DECODE (seq, 2, invoice_num, NULL)) expense_2,

         MAX (DECODE (seq, 3, invoice_num, NULL)) expense_3,

         MAX (DECODE (seq, 4, invoice_num, NULL)) expense_4,

         MAX (DECODE (seq, 5, invoice_num, NULL)) expense_5,

         MAX (DECODE (seq, 6, invoice_num, NULL)) expense_6,

         MAX (DECODE (seq, 7, invoice_num, NULL)) expense_7,

         MAX (DECODE (seq, 8, invoice_num, NULL)) expense_8,

         MAX (DECODE (seq, 9, invoice_num, NULL)) expense9

    FROM (SELECT employee_id, invoice_num,

                 ROW_NUMBER () OVER (PARTITION BY employee_id ORDER BY report_header_id)

                                                                          seq

            FROM ap_expense_report_headers_all

           WHERE employee_id = 11923)

GROUP BY employee_id;

Same logic applied and generated the tsv(Tab Separated Value) file using UTL_FILE utility in pl/sql code (i.e. outbound from oracle database).

Sample Code:

CREATE OR REPLACE PROCEDURE xx_barcode_generation_prc (

   errbuff              OUT      VARCHAR2, -- Mandatory parameter for concurrent program executable registration

   retcode              OUT      VARCHAR2, -- Mandatory parameter for concurrent program executable registration

   p_invoice_no         IN       VARCHAR2, -- Parameter1 in concurrent program

   p_transporter_name   IN       VARCHAR2, -- Parameter2 in concurrent program

   p_form38             IN       VARCHAR2  -- Parameter3 in concurrent program

)

IS

   -- Getting row values in column wise

   CURSOR cur_main (p_invoice_no VARCHAR2)

   IS

      SELECT DISTINCT a.po_number, a.invoice_no, a.invoice_date,

                      MAX (DECODE (seq, 1, a.part_no, NULL)) partno_1,

                      MAX (DECODE (seq, 1, a.quantity, NULL)) qty_1,

                      MAX (DECODE (seq, 2, a.part_no, NULL)) partno_2,

                      MAX (DECODE (seq, 2, a.quantity, NULL)) qty_2,

                      MAX (DECODE (seq, 3, a.part_no, NULL)) partno_3,

                      MAX (DECODE (seq, 3, a.quantity, NULL)) qty_3,

                      MAX (DECODE (seq, 4, a.part_no, NULL)) partno_4,

                      MAX (DECODE (seq, 4, a.quantity, NULL)) qty_4,

                      MAX (DECODE (seq, 5, a.part_no, NULL)) partno_5,

                      MAX (DECODE (seq, 5, a.quantity, NULL)) qty_5,

                      MAX (DECODE (seq, 6, a.part_no, NULL)) partno_6,

                      MAX (DECODE (seq, 6, a.quantity, NULL)) qty_6,

                      MAX (DECODE (seq, 7, a.part_no, NULL)) partno_7,

                      MAX (DECODE (seq, 7, a.quantity, NULL)) qty_7,

                      MAX (DECODE (seq, 8, a.part_no, NULL)) partno_8,

                      MAX (DECODE (seq, 8, a.quantity, NULL)) qty_8,

                      MAX (DECODE (seq, 9, a.part_no, NULL)) partno_9,

                      MAX (DECODE (seq, 9, a.quantity, NULL)) qty_9

                 FROM (SELECT po_number, invoice_no, invoice_date, part_no,

                              order_line_id, quantity,

                              RANK () OVER (PARTITION BY invoice_no ORDER BY order_line_id)

                                                                          seq

                         FROM (SELECT   (CASE

                                            WHEN UPPER (oeh.order_type) LIKE

                                                                  '%INTERNAL%'

                                               THEN TO_CHAR (wsh.delivery_id)

                                            ELSE oeh.cust_po_number

                                         END

                                        ) po_number,

                                        jisp.excise_invoice_no invoice_no,

                                        TO_CHAR

                                           (jisp.excise_invoice_date,

                                            'DD-MON-YYYY'

                                           ) invoice_date,

                                        mtl.segment1 part_no,

                                        jisp.quantity quantity,

                                        (  NVL (jisp.selling_price, 0)

                                         * NVL (jisp.quantity, 0)

                                        ) invoice_value,

                                        jisp.order_line_id

                                   FROM jai_om_wsh_lines_all jisp,

                                        wsh_new_deliveries_v wsh,

                                        oe_order_headers_v oeh,

                                        mtl_system_items mtl,

                                        oe_order_lines_v oel

                                  WHERE jisp.order_header_id = oeh.header_id

                                    AND jisp.delivery_id = wsh.delivery_id

                                    AND jisp.order_header_id = oel.header_id

                                    AND jisp.order_line_id = oel.line_id

                                    AND jisp.inventory_item_id =

                                                         mtl.inventory_item_id

                                    AND mtl.organization_id =

                                           (SELECT inventory_organization_id

                                              FROM financials_system_parameters)

                                    --AND OEH.ORG_ID=NVL(:P_ORG_ID,OEH.ORG_ID)

                                    AND jisp.excise_invoice_no =

                                           NVL (p_invoice_no,

                                                jisp.excise_invoice_no

                                               )

                               ORDER BY jisp.order_line_id)) a

             GROUP BY po_number, invoice_no, invoice_date;

   l_file_name      VARCHAR2 (100);

   l_file           UTL_FILE.FILE_TYPE; -- Data type of utl_file

   l_org_id         NUMBER;

   l_sysdate        DATE;

   l_po_number      VARCHAR2 (240);

   l_invoice_no     VARCHAR2 (240);

   l_invoice_date   DATE;

   l_inv_value      NUMBER;

   l_partno1        VARCHAR2 (240);

   l_qty1           NUMBER;

   l_partno2        VARCHAR2 (240);

   l_qty2           NUMBER;

   l_partno3        VARCHAR2 (240);

   l_qty3           NUMBER;

   l_partno4        VARCHAR2 (240);

   l_qty4           NUMBER;

   l_partno5        VARCHAR2 (240);

   l_qty5           NUMBER;

   l_partno6        VARCHAR2 (240);

   l_qty6           NUMBER;

   l_partno7        VARCHAR2 (240);

   l_qty7           NUMBER;

   l_partno8        VARCHAR2 (240);

   l_qty8           NUMBER;

   l_partno9        VARCHAR2 (240);

   l_qty9           NUMBER;

   l_chr            VARCHAR2 (20);

   l_error          VARCHAR2 (400);

BEGIN

   -- Initialize the apps and set org context

--    mo_global.init;

   l_org_id := fnd_profile.VALUE ('ORG_ID');

   mo_global.set_policy_context ('S', l_org_id);

   COMMIT;

   -- Generating the Dynamic File Name using Invoice Number and system date

   l_file_name :=

               'XX' || '_BARCODE' || l_invoice_no || l_sysdate || '.tsv';

   -- Opening file using the utility utl_file in write mode

   l_file :=

      UTL_FILE.FOPEN

            ('/data/erp/db/tech_st/11.1.0/appsutil/outbound/ERPTEST',

             l_file_name,

             'W'

            );

   OPEN cur_main (p_invoice_no);

   LOOP

      FETCH cur_main

       INTO l_po_number, l_invoice_no, l_invoice_date, l_partno1, l_qty1,

            l_partno2, l_qty2, l_partno3, l_qty3, l_partno4, l_qty4,

            l_partno5, l_qty5, l_partno6, l_qty6, l_partno7, l_qty7,

            l_partno8, l_qty8, l_partno9, l_qty9;

      EXIT WHEN cur_main%NOTFOUND;

      fnd_file.put_line (fnd_file.LOG, 'Starting...Inside cursor');

      SELECT SYSDATE

        INTO l_sysdate

        FROM DUAL;

        -- To get the TAB character; ascii value of TAB is 9

      SELECT CHR (9)

        INTO l_chr

        FROM DUAL;

      SELECT   ROUND (SUM ((NVL (selling_price, 0) * NVL (quantity, 0))), 2)

          INTO l_inv_value

          FROM jai_om_wsh_lines_all

         WHERE excise_invoice_no = l_invoice_no AND org_id = l_org_id

      GROUP BY excise_invoice_no;

      fnd_file.put_line

         (fnd_file.LOG,

          '***********************************************************************'

         );

      fnd_file.put_line (fnd_file.LOG, 'Opening File : ' || l_file_name);

        -- Writing the file using the data

      UTL_FILE.PUT_LINE (l_file,

                            l_po_number

                         || l_chr

                         || l_invoice_no

                         || l_chr

                         || l_invoice_date

                         || l_chr

                         || p_form38

                         || l_chr

                         || p_transporter_name

                         || l_chr

                         || l_inv_value

                         || l_chr

                         || l_partno1

                         || l_chr

                         || l_qty1

                         || l_partno2

                         || l_chr

                         || l_qty2

                         || l_partno3

                         || l_chr

                         || l_qty3

                         || l_partno4

                         || l_chr

                         || l_qty4

                         || l_partno5

                         || l_chr

                         || l_qty5

                         || l_partno6

                         || l_chr

                         || l_qty6

                         || l_partno7

                         || l_chr

                         || l_qty7

                         || l_partno8

                         || l_chr

                         || l_qty8

                         || l_partno9

                         || l_chr

                         || l_qty9

                        );

      fnd_file.put_line (fnd_file.LOG, 'Ending File : ' || l_file_name);

      fnd_file.put_line

         (fnd_file.LOG,

          '***********************************************************************'

         );

   END LOOP;

   UTL_FILE.FCLOSE(l_file);

EXCEPTION

   WHEN OTHERS

   THEN

      l_error := SUBSTR (SQLERRM, 1, 300);

      fnd_file.put_line (fnd_file.LOG,

                         'Other error in XX_BARCODE_GENERATION_PRC: ' || l_error

                        );

END xx_barcode_generation_prc;

/

Thanks,

Rajesh