, ,

How to Reading XML data using PLSQL

This Document explains the steps of how we read an XML data file using PLSQL and write them to oracle tables.

Create a UTL Directory:

This involves the following steps. Creating a Logical directory and giving the Permissions to access this directory using an URL. Using the XML Dom Parser Procedures to read the Xml file , parse it
and then load it into the respective columns.

1) Create a logical directory
Create or replace directory TEST_LOB_DIR as ‘/data/tst1/attachment’
It creates a directory with Owner SYS.Grant privileges on this directory to Apps.

2) Create a physical directory at the given location ‘/data/tst1/attachment’
Permissions on this directory too.This directory has to be a UTL directory.

3) Edit the conf file at this path
Cd $IAS_ORACLE_HOME/Apache/Apache/conf
==>/apps/applmgr/tst1ora/IAS/Apache/Apache/conf
Edit the file apps.conf
Alias /attachment/ “/data/tst1/attachment/”
<Location /attachment/>
Order allow, deny
Allow from all
</Location>

4) Bounce the Apache.

Create a procedure to parse and retrieve the XML data and then insert it into appropriate columns in the database.

Used two PLSQL packages for these –
1. XMLPARSER
2. XMLDOM

1. XMLParser – Procedures

i.)parse(p Parser, url VARCHAR2)
Description – The parse procedure takes two parameters which are the parse object and the url of the
xml file that has to parsed.
ii.) setValidationMode(p Parser, yes BOOLEAN)
Checks whether XML is Valid or not.
iii.) setBaseDir(p Parser, dir VARCHAR2)
Sets the base url or directory path.
iv.) getDocument(p Parser)
Get the Document which has to be parsed.

2. XMLDOM
DOMDocument
DOMELEMENT
DOMNODELIST
DOMNODE
DOMNamedNodeMap

FUNCTIONS –
getNodeName(n DOMNode) RETURN VARCHAR2 – Retrieves the Name of the Node
getNodeValue(n DOMNode) RETURN VARCHAR2 – Retrieves the Value of the Node
getElementsByTagName(doc DOMDocument, tagname IN VARCHAR2)- Retrieves the elements in
the by tag name
getDocumentElement(doc DOMDocument) RETURN DOMElement – Retrieves the root element of
the document
getFirstChild(n DOMNode) RETURN DOMNode – Retrieves the first child of the node
getLength(nl DOMNodeList) RETURN NUMBER- Retrieves the number of items in the list.

Sample XML File

-<vapi_HRMS>
– <record>
  <EmpID>O01006923</EmpID> 
  <vcEmp_Full_Name>Vinod Mudakkayil</vcEmp_Full_Name> 
  <vcDesignation>VP</vcDesignation> 
  <chLevel>L8</chLevel> 
  <chGrade>-</chGrade> 
  <vcDepartment>HR-Recruitment</vcDepartment> 
  <vcProgram_Name>HR – Recruitment</vcProgram_Name> 
  <vcSub_Program_Name>HR-Recruitment</vcSub_Program_Name> 
  <vcEmail_ID>[email protected]</vcEmail_ID> 
  <vcCentername>Bangalore</vcCentername> 
  <intSeniorID>01040101</intSeniorID> 
  <Hop_Id>101006923</Hop_Id> 
  <VP_Id>101006923</VP_Id> 
  <VCEMP_STATUS>CORPORATE</VCEMP_STATUS> 
  <vcEmp_First_Name>Vinod</vcEmp_First_Name> 
  <vcEmp_Last_Name>Mudakkayil</vcEmp_Last_Name> 
  <vcEmp_Gender>Male</vcEmp_Gender> 
  <vcEmp_DOJ>2005/04/07</vcEmp_DOJ> 
  <dob>4-Mar-1963</dob> 
  </record>
– <record>
  <EmpID>P01006923</EmpID> 
  <vcEmp_Full_Name>Vinod Mudakkayil</vcEmp_Full_Name> 
  <vcDesignation>VP</vcDesignation> 
  <chLevel>L8</chLevel> 
  <chGrade>-</chGrade> 
  <vcDepartment>HR-Recruitment</vcDepartment> 
  <vcProgram_Name>HR – Recruitment</vcProgram_Name> 
  <vcSub_Program_Name>HR-Recruitment</vcSub_Program_Name> 
  <vcEmail_ID>[email protected]</vcEmail_ID> 
  <vcCentername>Bangalore</vcCentername> 
  <intSeniorID>01040101</intSeniorID> 
  <Hop_Id>01006923,101006923,O01006923</Hop_Id> 
  <VP_Id>O01006923</VP_Id> 
  <VCEMP_STATUS>CORPORATE</VCEMP_STATUS> 
  <vcEmp_First_Name>Vinod</vcEmp_First_Name> 
  <vcEmp_Last_Name>Mudakkayil</vcEmp_Last_Name> 
  <vcEmp_Gender>Male</vcEmp_Gender> 
  <vcEmp_DOJ>2005/04/07</vcEmp_DOJ> 
  <dob>4-Mar-1963</dob> 
  </record>
  </vapi_HRMS>

Sample Code:

— Call the procedure —
PROCEDURE xml_perse (
      errbuf    OUT   VARCHAR2,
      retcode   OUT   NUMBER,
      dir             VARCHAR2,
      inpfile         VARCHAR2
   )
   IS
      p         xmlparser.parser;
      doc       xmldom.domdocument;
      docelem   DBMS_XMLDOM.domelement;
— prints elements in a document
   BEGIN
— new parser
      p := xmlparser.newparser;
— set some characteristics
      xmlparser.setvalidationmode (p, FALSE);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse Validated’);
–xmlparser.setErrorLog(p, dir || ‘/’ || errfile);
      xmlparser.setbasedir (p, dir);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse set path’);
— parse input file
      xmlparser.parse (p, dir || ‘/’ || inpfile);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse parse’);
— get document
      doc := xmlparser.getdocument (p);
      fnd_file.put_line (fnd_file.LOG, ‘ xml_perse get document’);
— Print document elements
      DBMS_OUTPUT.put (‘The elements are: ‘);
      printelements (doc);
      COMMIT;
   EXCEPTION
      WHEN OTHERS
      THEN
         –SQLERRM
         DBMS_OUTPUT.put (SQLERRM);
   END xml_perse;

   PROCEDURE printelements (doc xmldom.domdocument)
   IS
      nl1                  xmldom.domnodelist;
      nl2                  xmldom.domnodelist;
      nl3                  xmldom.domnodelist;
      nl4                  xmldom.domnodelist;
      nl5                  xmldom.domnodelist;
     
      len1                 NUMBER;
      len2                 NUMBER;
      len3                 NUMBER;
      len4                 NUMBER;
      len5                 NUMBER;
     
      n1                   xmldom.domnode;
      n2                   xmldom.domnode;
      n3                   xmldom.domnode;
      n4                   xmldom.domnode;
    
      nnm                  xmldom.domnamednodemap;
      attrname             VARCHAR (1000);
      attrval              VARCHAR (1000);
      v_empid              VARCHAR2 (1000);
      v_emp_full_name      VARCHAR2 (1000);
      v_designation        VARCHAR2 (1000);
    
   BEGIN
— get all elements
      fnd_file.put_line (fnd_file.LOG, ‘get all elements’);
      nl1 := xmldom.getelementsbytagname (doc, ‘record’);
      nl2 := xmldom.getelementsbytagname (doc, ‘EmpID’);
      nl3 := xmldom.getelementsbytagname (doc, ‘vcEmp_Full_Name’);
      nl4 := xmldom.getelementsbytagname (doc, ‘vcDesignation’);
     
      fnd_file.put_line (fnd_file.LOG, ‘Length of the Elements’);
— Length of the Elements
      len1 := xmldom.getlength (nl1);
      len2 := xmldom.getlength (nl2);
      len3 := xmldom.getlength (nl3);
      len4 := xmldom.getlength (nl4);     

— loop through elements
      FOR i IN 0 .. len1 – 1
      LOOP
         v_empid := NULL;
         v_emp_full_name := NULL;
         v_designation := NULL;
        
         n1 := xmldom.item (nl1, i);
         n2 := xmldom.item (nl2, i);
         n3 := xmldom.item (nl3, i);
         n4 := xmldom.item (nl4, i);        

         v_empid := xmldom.getnodevalue (n2);
         v_emp_full_name := xmldom.getnodevalue (n3);
         v_designation := xmldom.getnodevalue (n4);
         
         fnd_file.put_line (fnd_file.LOG, ‘***************************’);
         fnd_file.put_line (fnd_file.LOG, v_empid);
         fnd_file.put_line (fnd_file.LOG, v_emp_full_name);
         fnd_file.put_line (fnd_file.LOG, v_designation);
        
         fnd_file.put_line (fnd_file.LOG, ‘***************************’);

         DELETE FROM xx_employee_temp;

         –WHERE status = ‘S’;
         INSERT INTO xx_employee_temp
                     (empid, emp_full_name, designation,status, error_description
                     )
              VALUES (v_empid, v_emp_full_name, v_designation,NULL, NULL
                     );

         DBMS_OUTPUT.put_line (‘ ‘);
      END LOOP;

      fnd_file.put_line (fnd_file.LOG, ‘Inserted’);
   END printelements;
/by
, , , , , , ,

Re-Compile Invalid Schema Objects in Oracle Database

Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don’t cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = ‘INVALID’
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word “BODY” at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘MY_SCHEMA’, ‘MY_PROCEDURE’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);
EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, ‘PACKAGE’, 1,
                                             ‘PACKAGE BODY’, 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
                  AND    status != ‘VALID’
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = ‘PACKAGE’ THEN
        EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||
            ‘ “‘ || cur_rec.owner || ‘”.”‘ || cur_rec.object_name || ‘” COMPILE’;
      ElSE
        EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner ||
            ‘”.”‘ || cur_rec.object_name || ‘” COMPILE BODY’;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||
                             ‘ : ‘ || cur_rec.object_name);
    END;
  END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’);
UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

 PROCEDURE RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
   threads  IN   PLS_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below.
schema – The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads – The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags – Used for internal diagnostics and testing only.

The following examples show how these procedures are used.
— Schema level.
EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);
EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

— Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

— Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

There are a number of restrictions associated with the use of this package including:

Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Running DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 – The level of parallelism is derived based on the CPU_COUNT parameter.
1 – The recompilation is run serially, one object at a time.
N – The recompilation is run in parallel with “N” number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For more information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
/by
, , , , , ,

How to Create and Register a Concurrent Program using SQL*Loader

When building customization’s for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Tables might be seeded tables, i.e. Open Interface tables, or custom tables which you’ve added to a special customization’s database schema.

To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.


Starting a SQL*Loader load can be done by command line by executing the below:

sqlldr db_user/db_password@host:port/sid control=control_file_name.ctl log=log_file_name.log

SQL*Loader can also be executed by starting a special concurrent program which you can create in Oracle E-Business Suite. Below the steps how to do this.

1) First of all you need to have a csv file with data – the csv can also contain header information. The header in a csv file can be skipped by adding a special parameter in the control file. Take note of the columns in the csv file.

2) You need a control file – for example see the below control file. There are a big number of commands you can use in the control file to completely have control on how data gets loaded into tables. If you want to know more than contact me on this topic.

SKIP = 1
LOAD DATA
INFILE ‘data_file_name.csv’
BADFILE ‘data_file_name.bad’
REPLACE INTO TABLE XXX.YOUR_CUSTOM_TABLE
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(
COLUMN1,COLUMN2,COLUMN3,…
)

SKIP=1
Tells SQL*Loader to exclude row 1 in the data file (to exclude the headers)

INFILE
Specifies the name of the incoming data csv file

BADFILE
Determines the bad file generated for any errors occured during the load

REPLACE
This command will first truncate the table and than add the records. If you change this in an APPEND command the records will be added only to the table specified without truncating first

FIELDS TERMINATED BY
Determines the seperator used in the data csv file

OPTIONALLY ENCLOSED BY
Determines an additional enclosing character like for example ” if you’re adding data which contains the column seperator used

TRAILING NULLCOLS
Is used to treat any missing data in the csv file as NULL for the table to be loaded

COLUMN1, COLUMN2, COLUMN3, …
Gives the column names to be loaded

3) Create the SQL*Loader Executable in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Executable.


Give you executable a name, shortname and assign it to your customizations application. A description is optionally. Select Execution Method SQL*Loader to let eBS know SQL*Loader needs to be started. The Execution File Name holds the name of the control file you want to start (exclude the extension ctl here). The control file needs to be located in the bin directory of your customization application.

4) Create the concurrent program in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Define.


Give your concurrent program a name, a short name, assign it to your customization’s application and optionally provide a description. Assign your created executable to the concurrent program.

5) Add parameters to dynamically provide the incoming data csv file. Click on Parameters.


Add Sequence 10 and give the parameter a name. In this we want to provide a full path to the incoming data csv file so we use seeded Value Set 100 Characters to hold the path. Optionally add a default value.

6) When done creating the concurrent program add it to a Concurrent Request Group and start loading data in your tables.

/by
, , , , , ,

OPM Recipe Header Uploading through Script using API

Recipe Header Uploading can be done using the following steps:

1. First Create a staging table say “MJIL_RCP_HDR_UPL_TBL” as shown below.

CREATE TABLE MJIL_RCP_HDR_UPL_TBL
(
   RECIPE_ID                  NUMBER (15),
   RECIPE_DESCRIPTION         VARCHAR2 (70 BYTE),
   RECIPE_NO                  VARCHAR2 (32 BYTE),
   RECIPE_VERSION             NUMBER (5),
   USER_ID                    NUMBER (15),
   USER_NAME                  VARCHAR2 (70 BYTE),
   OWNER_ORGN_CODE            VARCHAR2 (4 BYTE),
   CREATION_ORGN_CODE         VARCHAR2 (4 BYTE),
   OWNER_ORGANIZATION_ID      NUMBER,
   CREATION_ORGANIZATION_ID   NUMBER,
   FORMULA_ID                 NUMBER (15),
   FORMULA_NO                 VARCHAR2 (32 BYTE),
   FORMULA_VERS               NUMBER,
   ROUTING_ID                 NUMBER,
   ROUTING_NO                 VARCHAR2 (32 BYTE),
   ROUTING_VERS               NUMBER (5),
   PROJECT_ID                 NUMBER (15),
   RECIPE_STATUS              VARCHAR2 (30 BYTE),
   PLANNED_PROCESS_LOSS       NUMBER,
   TEXT_CODE                  NUMBER (10),
   DELETE_MARK                NUMBER (5),
   CONTIGUOUS_IND             NUMBER,
   ENHANCED_PI_IND            VARCHAR2 (1 BYTE),
   RECIPE_TYPE                NUMBER,
   CREATION_DATE              DATE,
   CREATED_BY                 NUMBER (15),
   LAST_UPDATED_BY            NUMBER (15),
   LAST_UPDATE_DATE           DATE,
   LAST_UPDATE_LOGIN          NUMBER (15),
   OWNER_ID                   NUMBER (15),
   OWNER_LAB_TYPE             VARCHAR2 (4 BYTE),
   CALCULATE_STEP_QUANTITY    NUMBER (5),
   FIXED_PROCESS_LOSS         NUMBER,
   FIXED_PROCESS_LOSS_UOM     VARCHAR2 (3 BYTE),
   ATTRIBUTE_CATEGORY         VARCHAR2 (30 BYTE),
   ATTRIBUTE1                 VARCHAR2 (240 BYTE),
   ATTRIBUTE2                 VARCHAR2 (240 BYTE),
   ATTRIBUTE3                 VARCHAR2 (240 BYTE),
   ATTRIBUTE4                 VARCHAR2 (240 BYTE),
   ATTRIBUTE5                 VARCHAR2 (240 BYTE),
   ATTRIBUTE6                 VARCHAR2 (240 BYTE),
   ATTRIBUTE7                 VARCHAR2 (240 BYTE),
   ATTRIBUTE8                 VARCHAR2 (240 BYTE),
   ATTRIBUTE9                 VARCHAR2 (240 BYTE),
   ATTRIBUTE10                VARCHAR2 (240 BYTE),
   ATTRIBUTE11                VARCHAR2 (240 BYTE),
   ATTRIBUTE12                VARCHAR2 (240 BYTE),
   ATTRIBUTE13                VARCHAR2 (240 BYTE),
   ATTRIBUTE14                VARCHAR2 (240 BYTE),
   ATTRIBUTE15                VARCHAR2 (240 BYTE),
   ATTRIBUTE16                VARCHAR2 (240 BYTE),
   ATTRIBUTE17                VARCHAR2 (240 BYTE),
   ATTRIBUTE18                VARCHAR2 (240 BYTE),
   ATTRIBUTE19                VARCHAR2 (240 BYTE),
   ATTRIBUTE20                VARCHAR2 (240 BYTE),
   ATTRIBUTE21                VARCHAR2 (240 BYTE),
   ATTRIBUTE22                VARCHAR2 (240 BYTE),
   ATTRIBUTE23                VARCHAR2 (240 BYTE),
   ATTRIBUTE24                VARCHAR2 (240 BYTE),
   ATTRIBUTE25                VARCHAR2 (240 BYTE),
   ATTRIBUTE26                VARCHAR2 (240 BYTE),
   ATTRIBUTE27                VARCHAR2 (240 BYTE),
   ATTRIBUTE28                VARCHAR2 (240 BYTE),
   ATTRIBUTE29                VARCHAR2 (240 BYTE),
   ATTRIBUTE30                VARCHAR2 (240 BYTE)
);

2. Next create a procedure using the script as shown below.

CREATE OR REPLACE PROCEDURE CONA_RECIPE_UPLOAD_PD (ERRBUF    OUT VARCHAR2,
                                                   RETCODE   OUT NUMBER)
IS
   /******************************************************************************
   NAME: APPS.CONA_RECIPE_UPLOAD_PD
   PURPOSE: Recipe Header Uploading
   REVISIONS:
   Ver Date Author Description
   ——— ———- ————— ————————————
   1.0 8/16/2013 1. Oracle User Created this procedure.

   NOTES:

   Automatically available Auto Replace Keywords:
   Object Name: APPS.CONA_RECIPE_UPLOAD_PD
   ******************************************************************************/

   mjil_rcp_hdr_tbl          gmd_recipe_header.recipe_tbl;
   mjil_rcp_hdr_flex_tbl     gmd_recipe_header.recipe_flex;
   X_status                  VARCHAR2 (1);
   X_msg_cnt                 NUMBER;
   X_msg_dat                 VARCHAR2 (1000);
   X_row                     NUMBER := 1;
   l_user_id                 NUMBER := 1114;
   l_responsibility_id       NUMBER := 22883;
   l_out_index               NUMBER;
   l_responsibility_app_id   NUMBER;

   CURSOR c1
   IS
      SELECT * FROM mjil_rcp_hdr_upl_tbl;
BEGIN
   FND_GLOBAL.
    APPS_INITIALIZE (l_user_id, l_responsibility_id, l_responsibility_app_id);

   FOR i IN c1
   LOOP
      mjil_rcp_hdr_tbl (X_row).recipe_no := i.recipe_no;
      mjil_rcp_hdr_tbl (X_row).recipe_version := i.recipe_version;
      mjil_rcp_hdr_tbl (X_row).recipe_description := i.recipe_description;
      mjil_rcp_hdr_tbl (X_row).RECIPE_STATUS := i.RECIPE_STATUS;
      mjil_rcp_hdr_tbl (X_row).RECIPE_TYPE := i.RECIPE_TYPE;
      mjil_rcp_hdr_tbl (X_row).formula_no := i.formula_no;
      mjil_rcp_hdr_tbl (X_row).formula_vers := i.formula_vers;
      mjil_rcp_hdr_tbl (X_row).routing_no := i.routing_no;
      mjil_rcp_hdr_tbl (X_row).routing_vers := i.routing_vers;

      mjil_rcp_hdr_tbl (X_row).delete_mark := i.delete_mark;
      mjil_rcp_hdr_tbl (X_row).creation_date := SYSDATE;
      mjil_rcp_hdr_tbl (X_row).created_by := i.created_by;
      mjil_rcp_hdr_tbl (X_row).last_updated_by := i.last_updated_by;
      mjil_rcp_hdr_tbl (X_row).last_update_date := SYSDATE;
      mjil_rcp_hdr_tbl (X_row).last_update_login := 1114;

      mjil_rcp_hdr_tbl (X_row).user_name := i.user_name;
      mjil_rcp_hdr_tbl (X_row).owner_orgn_code := i.owner_orgn_code;
      mjil_rcp_hdr_tbl (X_row).OWNER_ORGANIZATION_ID :=
         i.owner_organization_id;
      mjil_rcp_hdr_tbl (X_row).creation_orgn_code := i.creation_orgn_code;
      mjil_rcp_hdr_tbl (X_row).owner_id := i.owner_id;

      mjil_rcp_hdr_flex_tbl (X_row).attribute1 := ‘FLEX1’;

      X_row := X_row + 1;
   END LOOP;

   gmd_recipe_header.
    create_recipe_header (p_api_version          => 1,
                          p_init_msg_list        => FND_API.G_TRUE,
                          p_commit               => FND_API.G_TRUE,
                          p_called_from_forms    => ‘NO’,
                          x_return_status        => X_status,
                          x_msg_count            => X_msg_cnt,
                          x_msg_data             => X_msg_dat,
                          p_recipe_header_tbl    => mjil_rcp_hdr_tbl,
                          p_recipe_header_flex   => mjil_rcp_hdr_flex_tbl);

   DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
   DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

   FOR i IN 1 .. X_msg_cnt
   LOOP
      FND_MSG_PUB.get (p_msg_index       => i,
                       p_encoded         => ‘F’,
                       p_data            => X_msg_dat,
                       P_MSG_INDEX_OUT   => l_out_index);
      DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (‘Return status – ‘ || X_status);
      DBMS_OUTPUT.put_line (‘Message count – ‘ || X_msg_cnt);

      FOR i IN 1 .. X_msg_cnt
      LOOP
         FND_MSG_PUB.get (p_msg_index       => i,
                          p_encoded         => ‘F’,
                          p_data            => X_msg_dat,
                          P_MSG_INDEX_OUT   => l_out_index);
         DBMS_OUTPUT.PUT_LINE (‘Message Text ‘ || X_msg_dat);
      END LOOP;
END;
/

3. Finally register the procedure and run it.

/by
, , , , ,

Running Fast Formula From Backend – Oracle HRMS

Prerequisites:


Step1 : Create the Element and Element links
Step2: write the fast formula
Step3: Attach the fast formula in Formula Results

Package used to call the fast formula from the backed : ff_exec

Simple Code Snippet below: 

      l_formula_id        NUMBER;
      l_element_inputs    ff_exec.inputs_t;
      l_element_outputs   ff_exec.outputs_t;
      l_in_count          NUMBER;
      l_out_count         NUMBER;
      l_pay_value         NUMBER;

   BEGIN

      BEGIN
         SELECT formula_id
           INTO l_formula_id
           FROM ff_formulas_f
          WHERE formula_name = ‘XX_PAYROLL_FORMULA’
          AND p_effective_date BETWEEN effective_start_date  
             AND effective_end_date;
      EXCEPTION 
      WHEN OTHERS 
      THEN
      DBMS_OUTPUT.put_line (‘NO formula exists’);
      END;

      IF l_formula_id IS NOT NULL

      THEN

— Insert FND_SESSIONS row ( Optional )

  INSERT INTO fnd_sessions
             ( session_id, 
               effective_date
             )
        VALUES 
            ( USERENV (‘sessionid’), 
              p_effective_date
             );

—
— Initialize the formula.
—

ff_exec.init_formula (l_formula_id,
                      p_effective_date,
                      l_element_inputs,
                      l_element_outputs
                      );

—
— Loop through the Input Values
—
FOR l_in_count IN l_element_inputs.FIRST .. l_element_inputs.LAST
    LOOP

    —
    — Pass The each Input value name and its Value : Eg: START_DATE and p_start_date
    —    
       IF (l_element_inputs (l_in_count).NAME = ‘START_DATE’)
        THEN
           l_element_inputs (l_in_count).VALUE :=
                fnd_date.date_to_canonical (p_start_date);
        END IF;

END LOOP;

—
–Run The formula
—

ff_exec.run_formula (l_element_inputs, l_element_outputs);

—
— Get the Out Put Values
—
FOR l_out_count IN l_element_outputs.FIRST .. l_element_outputs.LAST

    LOOP
      —
      — Get all the Out Put Values Here L_PAY_VALUE is the out put value
      —
      IF (l_element_outputs (l_out_count).NAME = ‘L_PAY_VALUE’)
        THEN
           l_pay_value := l_element_outputs (l_out_count).VALUE;
      END IF;

    END LOOP;

RETURN (l_pay_value);

END;

/by