Oracle Object

Null Objects and Attributes

An object whose value is NULL is called atomically null. An atomically null object is different from an object that has null values for all its attributes. In an object with null values, a table column, object attribute, collection, or collection element might be NULL if it has been initialized to NULL or has not been initialized at all. Usually, a NULL value is replaced by an actual value later on. When all the attributes are null, you can still change these attributes and call the object's subprograms or methods. With an atomically null object, you can do neither of these things.

Example 2-1 creates the contacts table and defines the person_typ object type and two instances of this type.

Example 2-1 Inserting NULLs for Objects in a Table

CREATE OR REPLACE TYPE person_typ AS OBJECT (
  idno           NUMBER,
  name           VARCHAR2(30),
  phone          VARCHAR2(20),
  MAP MEMBER FUNCTION get_idno RETURN NUMBER, 
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) );
/

CREATE OR REPLACE TYPE BODY person_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN idno;
  END;
  MEMBER PROCEDURE display_details ( SELF IN OUT NOCOPY person_typ ) IS
  BEGIN
    -- use the PUT_LINE procedure of the DBMS_OUTPUT package to display details
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(idno) || ' - '  || name || ' - '  || phone);
  END;
END;
/
CREATE TABLE contacts (
  contact        person_typ,
  contact_date   DATE );

INSERT INTO contacts VALUES (
  person_typ (NULL, NULL, NULL), '24 Jun 2003' );

INSERT INTO contacts VALUES (
  NULL, '24 Jun 2003' );

Two instances of person_typ are inserted into the table and give two different results. In both cases, Oracle Database allocates space in the contacts table for a new row and sets its DATE column to the value given. But in the first case, Oracle Database allocates space for an object in the contact column and sets each of the object's attributes to NULL. In the second case, Oracle Database sets the person_typ field itself to NULL and does not allocate space for an object.

In some cases, you can omit checks for null values. A table row or row object cannot be null. A nested table of objects cannot contain an element whose value is NULL.

A nested table or array can be null, so you do need to handle that condition. A null collection is different from an empty one, one that has no elements.

Character Length Semantics

Lengths for character types CHAR and VARCHAR2 may be specified as a number of characters, instead of bytes, in object attributes and collections even if some of the characters consist of multiple bytes.

To specify character-denominated lengths for CHAR and VARCHAR2 attributes, you add the qualifier char to the length specification.

Like CHAR and VARCHAR2, NCHAR and NVARCHAR2 may also be used as attribute types in objects and collections. NCHAR andNVARCHAR2 are always implicitly measured in terms of characters, so no char qualifier is used.

For example, the following statement creates an object with both a character-length VARCHAR2 attribute and an NCHAR attribute:

Example 2-2 Creating the employee_typ Object Using a char Qualifier

CREATE OR REPLACE TYPE employee_typ AS OBJECT ( 
  name        VARCHAR2(30 char), 
  language    NCHAR(10), 
  phone       VARCHAR2(20) );
/

For CHAR and VARCHAR2 attributes whose length is specified without a char qualifier, the NLS_LENGTH_SEMANTICS initialization parameter setting (CHAR or BYTE) indicates the default unit of measure.

Constraints for Object Tables

You can define constraints on an object table just as you can on other tables. You can define constraints on the leaf-level scalar attributes of a column object, with the exception of REFs that are not scoped.

The following examples illustrate defining constraints.

Example 2-3 places an implicit PRIMARY KEY constraint on the office_id column of the object table office_tab.

Example 2-3 Creating the office_tab Object Table with a Constraint

-- requires Ex. 2-1
CREATE OR REPLACE TYPE location_typ AS OBJECT (
  building_no  NUMBER,
  city         VARCHAR2(40) );
/

CREATE OR REPLACE TYPE office_typ AS OBJECT (
  office_id    VARCHAR(10),
  office_loc   location_typ,
  occupant     person_typ );/

CREATE TABLE office_tab OF office_typ (
             office_id      PRIMARY KEY );

The object type location_typ defined in Example 2-3 is the type of the dept_loc column in the department_mgrs table inExample 2-4.

Example 2-4 defines constraints on scalar attributes of the location_typ objects in the table.

Example 2-4 Creating the department_mgrs Table with Multiple Constraints

-- requires Ex. 2-1 and 2-3
CREATE TABLE department_mgrs (
  dept_no     NUMBER PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_mgr    person_typ,
  dept_loc    location_typ,
  CONSTRAINT  dept_loc_cons1
      UNIQUE (dept_loc.building_no, dept_loc.city),
  CONSTRAINT  dept_loc_cons2
       CHECK (dept_loc.city IS NOT NULL) );

INSERT INTO department_mgrs VALUES 
          ( 101, 'Physical Sciences', 
           person_typ(65,'Vrinda Mills', '1-1-650-555-0125'),
           location_typ(300, 'Palo Alto'));

See Also:

"Constraints on Objects"

Indexes for Object Tables

You can define indexes on an object table or on the storage table for a nested table column or attribute just as you can on other tables. For an example of an index on a nested table, see Example 5-5.

You can define indexes on leaf-level scalar attributes of column objects, as shown in Example 2-5. You can only define indexes on REF attributes or columns if the REF is scoped. This example indexes city, which is a leaf-level scalar attribute of the column object dept_addr.

Example 2-5 Creating an Index on an Object Type in a Table

-- requires Ex. 2-1, 2-3, 
CREATE TABLE department_loc (
  dept_no     NUMBER PRIMARY KEY, 
  dept_name   CHAR(20),
  dept_addr   location_typ );

CREATE INDEX  i_dept_addr1
          ON  department_loc (dept_addr.city);

INSERT INTO department_loc VALUES
          ( 101, 'Physical Sciences',
           location_typ(300, 'Palo Alto'));
INSERT INTO department_loc VALUES 
          ( 104, 'Life Sciences', 
           location_typ(400, 'Menlo Park'));
INSERT INTO department_loc VALUES 
          ( 103, 'Biological Sciences', 
           location_typ(500, 'Redwood Shores'));

Wherever Oracle Database expects a column name in an index definition, you can also specify a scalar attribute of a column object.

Triggers for Object Tables

You can define triggers on an object table just as you can on other tables. You cannot define a trigger on the storage table for a nested table column or attribute. You cannot modify LOB values in a trigger body. Otherwise, there are no special restrictions on using object types with triggers.

Example 2-6 defines a trigger on the office_tab table defined in "Constraints for Object Tables".

Example 2-6 Creating a Trigger on Objects in a Table

-- requires Ex. 2-1 and 2-3
CREATE TABLE movement (
     idno           NUMBER,
     old_office     location_typ,
     new_office     location_typ );

CREATE TRIGGER trigger1
  BEFORE UPDATE
             OF  office_loc
             ON  office_tab
   FOR EACH ROW
           WHEN  (new.office_loc.city = 'Redwood Shores')
   BEGIN
     IF :new.office_loc.building_no = 600 THEN
      INSERT INTO movement (idno, old_office, new_office)
       VALUES (:old.occupant.idno, :old.office_loc, :new.office_loc);
     END IF;
   END;/
INSERT INTO office_tab VALUES 
    ('BE32', location_typ(300, 'Palo Alto' ),person_typ(280, 'John Chan', 
       '415-555-0101'));
 
UPDATE office_tab set office_loc =location_typ(600, 'Redwood Shores')
  where office_id = 'BE32'; 
 
select * from office_tab;
 
select * from movement;

Rules for REF Columns and Attributes

In Oracle Database, a REF column or attribute can be unconstrained or constrained using a SCOPE clause or a referential constraint clause. When a REF column is unconstrained, it may store object references to row objects contained in any object table of the corresponding object type.

Oracle Database does not ensure that the object references stored in such columns point to valid and existing row objects. Therefore, REF columns may contain object references that do not point to any existing row object. Such REF values are referred to as dangling references.

A SCOPE constraint can be applied to a specific object table. All the REF values stored in a column with a SCOPE constraint point at row objects of the table specified in the SCOPE clause. The REF values may, however, be dangling.

A REF column may be constrained with a REFERENTIAL constraint similar to the specification for foreign keys. The rules for referential constraints apply to such columns. That is, the object reference stored in these columns must point to a valid and existing row object in the specified object table.

PRIMARY KEY constraints cannot be specified for REF columns. However, you can specify NOT NULL constraints for such columns.

See Also:

• "Using References to Row Objects"
• "Substitution of REF Columns and Attributes"

Name Resolution

Oracle SQL lets you omit qualifying table names in some relational operations. For example, if dept_addr is a column in thedepartment_loc table and old_office is a column in the movement table, you can use the following:

SELECT * FROM department_loc WHERE EXISTS 
  (SELECT * FROM movement WHERE dept_addr = old_office);

Oracle Database determines which table each column belongs to.

Using dot notation, you can qualify the column names with table names or table aliases to make things more maintainable. For example:

Example 2-7 Using the Dot Notation for Name Resolution

-- requires Ex. 2-1, 2-3, 2-5, and 2-6
SELECT * FROM department_loc WHERE EXISTS 
  (SELECT * FROM movement WHERE department_loc.dept_addr = movement.old_office);

SELECT * FROM department_loc d WHERE EXISTS 
  (SELECT * FROM movement m WHERE d.dept_addr = m.old_office);

In some cases, object-relational features require you to specify the table aliases.

When Table Aliases Are Required

Using unqualified names can lead to problems. For example, if you add an assignment column to depts and forget to change the query, Oracle Database automatically recompiles the query so that the inner SELECT uses the assignment column from thedepts table. This situation is called inner capture.

To avoid inner capture and similar problems resolving references, Oracle Database requires you to use a table alias to qualify any dot-notational reference to subprograms or attributes of objects.

Use of a table alias is optional when referencing top-level attributes of an object table directly, without using the dot notation. For example, the following statements define two tables that contain the person_typ object type. person_obj_table is an object table for objects of type person_typ, and contacts is a relational table that contains a column of the object person_typ.

The following queries show some correct and incorrect ways to reference attribute idno:

Note:

These statements are not related to other examples in this chapter.

#1 SELECT idno FROM person_obj_table; --Correct

#2 SELECT contact.idno FROM contacts; --Illegal
#3 SELECT contacts.contact.idno FROM contacts; --Illegal
#4 SELECT p.contact.idno FROM contacts p; --Correct

• In #1, idno is the name of a column of person_obj_table. It references this top-level attribute directly, without using the dot notation, so no table alias is required.
• In #2, idno is the name of an attribute of the person_typ object in the column named contact. This reference uses the dot notation and so requires a table alias, as shown in #4.
• #3 uses the table name itself to qualify the reference. This is incorrect; a table alias is required.

You must qualify a reference to an object attribute or subprogram with a table alias rather than a table name even if the table name is itself qualified by a schema name.

For example, the following expression incorrectly refers to the HR schema, department_loc table, dept_addr column, and cityattribute of that column. The expression is incorrect because department_loc is a table name, not an alias.

HR.department_loc.dept_addr.city

The same requirement applies to attribute references that use REFs.

Table aliases should uniquely pick out the same table throughout a query and should not be the same as schema names that could legally appear in the query.

Note:

Oracle recommends that you define table aliases in all UPDATE, DELETE, and SELECT statements and subqueries and use them to qualify column references whether or not the columns contain object types.

Restriction on Using User-Defined Types with a Remote Database

Objects or user-defined types (specifically, types declared with a SQL CREATE TYPE statement, as opposed to types declared within a PL/SQL package) are currently useful only within a single database. Oracle Database restricts use of a database link as follows:

• You cannot connect to a remote database to select, insert, or update a user-defined type or an object REF on a remote table.
  You can use the CREATE TYPE statement with the optional keyword OID to create a user-specified object identifier (OID) that allows an object type to be used in multiple databases. See the discussion on assigning an OID to an object type in the Oracle Database Data Cartridge Developer's Guide.
• You cannot use database links within PL/SQL code to declare a local variable of a remote user-defined type.
• You cannot convey a user-defined type argument or return value in a PL/SQL remote procedure call.

Bulk collect , For all, save exception,Bulk Row count

Bulk collect , For all, save exception

PL/SQL anonymous block doing row-by-row INSERT 

SQL> CREATE TABLE sales_target_errors
  2  (sql_err_mesg varchar2(4000))
  3  /

SQL>  DECLARE
  2        TYPE array IS TABLE OF sales_target%ROWTYPE
  3           INDEX BY BINARY_INTEGER;
  4        sales_src_arr   ARRAY;
  5        errors          NUMBER;
  6        error_mesg     VARCHAR2(255);
  7        bulk_error      EXCEPTION;
  8        l_cnt           NUMBER := 0;
  9        PRAGMA exception_init
 10              (bulk_error, -24381);
 11        CURSOR c IS 
 12           SELECT * 
 13           FROM   sales_src;
 14        BEGIN
 15        OPEN c;
 16        LOOP
 17          FETCH c 
 18             BULK COLLECT 
 19             INTO sales_src_arr 
 20             LIMIT 100;
 21          BEGIN
 22             FORALL i IN 1 .. sales_src_arr.count 
 23                      SAVE EXCEPTIONS
 24               INSERT INTO sales_target VALUES sales_src_arr(i);
 25          EXCEPTION
 26          WHEN bulk_error THEN
 27            errors := 
 28               SQL%BULK_EXCEPTIONS.COUNT;
 29            l_cnt := l_cnt + errors;
 30            FOR i IN 1..errors LOOP
 31              error_mesg := SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 32              INSERT INTO sales_target_errors 
 33              VALUES     (error_mesg);
 34       END LOOP;
 35          END;
 36          EXIT WHEN c%NOTFOUND;
 37      
 38       END LOOP;
 39       CLOSE c;
 40       DBMS_OUTPUT.PUT_LINE
 41        ( l_cnt || ' total errors' );
 42       END;

-------------------------------------------------------------------------------

2)

SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS





SQL%BULK_ROWCOUNT and SQL%BULK_EXCEPTIONS are used in bulk opeartions with FORALL

SQL%BULK_ROWCOUNT
Let say, we are processing a bulk operation, need to know how many records are processed by each interation for the given DML (Update, Delete and Insert).

We have SQL%BULK_ROWCOUNT attribute to know the number of records processed in each iteration in a bulk operation.

The follwoing example shows how to use it.

ORCL> create table t_emp2 as select * from emp
  2  /

Table created.

ORCL> select deptno, count(*)
  2     from t_emp2
  3     where deptno in (10, 30)
  4     group by deptno
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        30          6

Dept 30 have 6 rows and dept 10 have 3 rows.

ORCL> declare
  2  
  3     type lt_dept_arr is table of number;
  4     l_dept_arr lt_dept_arr := lt_dept_arr(10, 30);
  5  
  6  begin
  7  
  8     forall i in l_dept_arr.first..l_dept_arr.last
  9     delete from t_emp2 where deptno = l_dept_arr(i);
 10  
 11     for j in l_dept_arr.first..l_dept_arr.last loop
 12        dbms_output.put_line ('The number of records deleted in '
 13                               || j || 'th iteration are  '
 14                               ||sql%bulk_rowcount(j) );
 15     end loop;
 16  
 17  end;
/
The number of records deleted in 1th iteration are  3
The number of records deleted in 2th iteration are  6

PL/SQL procedure successfully completed.


Handling exceptions in FORAll using SQL%BULK_EXCEPTIONS
PL/SQL provides a mechanism to handle exceptions that were raised during the FORALL bulk operation using SQL%BULK_EXCEPTIONS.

SQL%BULK_EXCEPTIONS argument saves all the exceptions raised during the bulk operation provided FORALL statement should have SAVE EXCEPTIONS added.

SQL%BULK_EXCEPTIONS.count gives the number of exceptions raised in the last FORALL bulk opearation

SQL%BULK_EXCEPTIONS.ERROR_INDEX gives the iteration number in which the exception is raised

SQL%BULK_EXCEPTIONS.ERROR_CODE gives the error code, using which we can find the error message as ERRM( - SQL%BULK_EXCEPTIONS ). noted that '-' is added at the beginning
of the SQL%BULK_EXCEPTIONS in the SQLERRM function.

Lets take an example to illustrate the functionality.

srikar@ORCL> create table t_emp as select * from emp;

Table created.

We have created a table 't_emp' whice is similar to emp table.

Now, we append each empname with ' Oracl' due to which error is raised in the bulk operation as the empname column maximum size is 10 characters and the append is not allowed for few columns,so the records whose length is crossing 10 characters would raise an exception.

In the follwoing code, if an exception is raised then the exception is saved and processed with the next records.


ORCL> ed
Wrote file afiedt.buf

  1  declare
  2         type lt_emp_arr is table of t_emp.empno%type;
  3         l_emp_arr    lt_emp_arr;
  4         errors       number;
  5  begin
  6         select empno bulk collect into l_emp_arr from t_emp;
  7         forall i in l_emp_arr.first..l_emp_arr.last save exceptions
  8         update t_emp set ename = ename||' Oracl' where empno = l_emp_arr(i) ;
  9  exception
 10       when others then
 11        errors := sql%bulk_exceptions.count;
 12        dbms_output.put_line ('The total number of errors occured are '
 13                                  || errors
 14                             );
 15        for j in 1..errors loop
 16           dbms_output.put_line ('The error iteration is  '
 17                                  || sql%bulk_exceptions(j).error_index
 18                                  || ' and the error code is '
 19                                  || sql%bulk_exceptions(j).error_code
 20                                  || ' and the error message is '
 21                                  || sqlerrm (- sql%bulk_exceptions(j).error_code)
 22                                );
 23        end loop;
 24* end;
 25  /
The total number of errors occured are 11
The error iteration is  1 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  2 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  4 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  5 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  6 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  7 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  8 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , ma
ximum: )
The error iteration is  10 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  11 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  12 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )
The error iteration is  14 and the error code is 12899 and the error message is ORA-12899: value too large for column  (actual: , m
aximum: )

PL/SQL procedure successfully completed.

ORCL> select ename from t_emp;

ENAME
----------
SMITH
ALLEN
WARD Oracl
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING Oracl
TURNER
ADAMS
JAMES
FORD Oracl
MILLER

14 rows selected.

WITH Clause

WITH Clause : Subquery Factoring

(WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1))

• Subquery Factoring
• PL/SQL Declaration Section

Related articles.

• WITH Clause Enhancements in Oracle Database 12c Release 1 (12cR1)

Subquery Factoring

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

This article shows how the WITH clause can be used to reduce repetition and simplify complex SQL statements. I'm not suggesting the following queries are the best way to retrieve the required information. They merely demonstrate the use of the WITH clause.

Using the SCOTT schema, for each employee we want to know how many other people are in their department. Using an inline view we might do the following.

SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc
WHERE  e.deptno = dc.deptno;

Using a WITH clause this would look like the following.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e,
       dept_count dc
WHERE  e.deptno = dc.deptno;

The difference seems rather insignificant here.

What if we also want to pull back each employees manager name and the number of people in the managers department? Using the inline view it now looks like this.

SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc1,
       emp m,
       (SELECT deptno, COUNT(*) AS dept_count
        FROM   emp
        GROUP BY deptno) dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

Using the WITH clause this would look like the following.

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc1.dept_count AS emp_dept_count,
       m.ename AS manager_name,
       dc2.dept_count AS mgr_dept_count
FROM   emp e,
       dept_count dc1,
       emp m,
       dept_count dc2
WHERE  e.deptno = dc1.deptno
AND    e.mgr = m.empno
AND    m.deptno = dc2.deptno;

So we don't need to redefine the same subquery multiple times. Instead we just use the query name defined in the WITH clause, making the query much easier to read.

If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. This can make multiple references to the subquery more efficient. The MATERIALIZE and INLINE optimizer hints can be used to influence the decision. The undocumentedMATERIALIZE hint tells the optimizer to resolve the subquery as a global temporary table, while the INLINE hint tells it to process the query inline.

WITH dept_count AS (
  SELECT /*+ MATERIALIZE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

WITH dept_count AS (
  SELECT /*+ INLINE */ deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT ...

Even when there is no repetition of SQL, the WITH clause can simplify complex queries, like the following example that lists those departments with above average wages.

WITH 
  dept_costs AS (
    SELECT dname, SUM(sal) dept_total
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
    GROUP BY dname),
  avg_cost AS (
    SELECT SUM(dept_total)/COUNT(*) avg
    FROM   dept_costs)
SELECT *
FROM   dept_costs
WHERE  dept_total > (SELECT avg FROM avg_cost)
ORDER BY dname;

In the previous example, the main body of the query is very simple, with the complexity hidden in the WITH clause.

PL/SQL Declaration Section

An Oracle Database 12c enhancement allows PL/SQL declarations in the WITH clause.

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1)

In addition to the Subquery Factoring Clause, Oracle 12c includes a PL/SQL declaration section in theWITH clause.

• Setup
• Functions in the WITH Clause
• Procedures in the WITH Clause
• PL/SQL Support
• Performance Advantages
• PRAGMA UDF
• WITH_PLSQL Hint
• DETERMINISTIC Hint
• Scalar Subquery Caching

Related articles.

• WITH Clause : Subquery Factoring
• Efficient Function Calls From SQL

Setup

The examples in this article require the following test table.

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM   dual
CONNECT BY level <= 1000000;
COMMIT;

-- Gathering stats on a CTAS is no longer necessary in 12c,
-- provided the statement is issued by a non-SYS user.
-- EXEC DBMS_STATS.gather_table_stats(USER, 't1');

Functions in the WITH Clause

The declaration section of the WITH clause can be used to define PL/SQL functions, as shown below.

WITH
  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/

WITH_FUNCTION(ID)
-----------------
                1

SQL>

Interestingly, the ";" does not seem to work as a terminator to the SQL statement when the PL/SQL declaration is included in the WITH clause. If we attempt to use it on its own, SQL*Plus waits for more text to be entered. Even the example in the SQL Reference manual uses a combination of ";" and "/".

From a name resolution perspective, functions defined in the PL/SQL declaration section of the WITH clause take precedence over objects with the same name defined at the schema level.

Procedures in the WITH Clause

We can also define procedures in the declaration section, even if they are not used.

SET SERVEROUTPUT ON

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;
SELECT id
FROM   t1
WHERE  rownum = 1
/

        ID
----------
         1

SQL>

In reality, you would only put a procedure into a WITH clause if you planned to call the procedure from a function in the declaration section.

WITH
  PROCEDURE with_procedure(p_id IN NUMBER) IS
  BEGIN
    DBMS_OUTPUT.put_line('p_id=' || p_id);
  END;

  FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
  BEGIN
    with_procedure(p_id);
    RETURN p_id;
  END;
SELECT with_function(id)
FROM   t1
WHERE  rownum = 1
/

WITH_FUNCTION(ID)
-----------------
                1

p_id=1
SQL>

PL/SQL Support

There does not appear to be any PL/SQL support for this feature. Any attempt to use it results in compilation errors, as shown below.

BEGIN
  FOR cur_rec IN (WITH
                    FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
                    BEGIN
                      RETURN p_id;
                    END;
                  SELECT with_function(id)
                  FROM   t1
                  WHERE  rownum = 1)
  LOOP
    NULL;
  END LOOP;
END;
/
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
                             *
ERROR at line 3:
ORA-06550: line 3, column 30:
PL/SQL: ORA-00905: missing keyword
ORA-06550: line 2, column 19:
PL/SQL: SQL Statement ignored
ORA-06550: line 5, column 34:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop


SQL>

Using dynamic SQL allows you to work around this restriction.

SET SERVEROUTPUT ON
DECLARE
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  l_value   NUMBER;
BEGIN
  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1
            WHERE  rownum = 1';
  
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor INTO l_value;
  DBMS_OUTPUT.put_line('l_value=' || l_value);
  CLOSE l_cursor;
END;
/
l_value=1

PL/SQL procedure successfully completed.

SQL>

Support for this feature using static SQL inside PL/SQL is due in a future release.

Performance Advantages

The whole reason for defining the PL/SQL code inline is to improve performance. Create a regular function to use as a comparison.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_id;
END;
/

Run the following test, which measures the elapsed time and CPU usage of the query using the inline function definition.

SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
  
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'SELECT normal_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 
END;
/
WITH_FUNCTION  : Time=45 hsecs CPU Time=39 hsecs
NORMAL_FUNCTION: Time=129 hsecs CPU Time=113 hsecs

PL/SQL procedure successfully completed.

SQL>

From this we can see the inline function definition takes approximately one third the elapsed time and CPU time to complete.

PRAGMA UDF

In a number of presentations prior to the official 12c release, speakers mentioned PRAGMA UDF (User Defined Function), which supposedly gives you the performance advantages of inline PL/SQL, whilst allowing you to define the PL/SQL object outside the SQL statement. The following code redefines the previous normal function to use this pragma.

CREATE OR REPLACE FUNCTION normal_function(p_id IN NUMBER) RETURN NUMBER IS
  PRAGMA UDF;
BEGIN
  RETURN p_id;
END;
/

Once the function is compiled, running the test from the previous section against this function produces rather interesting results.

SET SERVEROUTPUT ON
DECLARE
  l_time    PLS_INTEGER;
  l_cpu     PLS_INTEGER;
  
  l_sql     VARCHAR2(32767);
  l_cursor  SYS_REFCURSOR;
  
  TYPE t_tab IS TABLE OF NUMBER;
  l_tab t_tab;
BEGIN
  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('WITH_FUNCTION  : ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');

  l_time := DBMS_UTILITY.get_time;
  l_cpu  := DBMS_UTILITY.get_cpu_time;

  l_sql := 'SELECT normal_function(id)
            FROM   t1';
            
  OPEN l_cursor FOR l_sql;
  FETCH l_cursor
  BULK COLLECT INTO l_tab;
  CLOSE l_cursor;
  
  DBMS_OUTPUT.put_line('NORMAL_FUNCTION: ' ||
                       'Time=' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs ' ||
                       'CPU Time=' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs ');
 
END;
/
WITH_FUNCTION  : Time=44 hsecs CPU Time=40 hsecs
NORMAL_FUNCTION: Time=33 hsecs CPU Time=29 hsecs

PL/SQL procedure successfully completed.

SQL>

The standalone function using PRAGMA UDF seems to consistently out perform the inline function.

I was under the impression calling a function defined with PRAGMA UDF directly from PL/SQL would fail. This does not seem to be the case.

DECLARE
  l_number NUMBER;
BEGIN
  l_number := normal_function(1);
END;
/

PL/SQL procedure successfully completed.

SQL>

WITH_PLSQL Hint

If the query containing the PL/SQL declaration section is not the top level query, the top-level query must include the WITH_PLSQL hint. Without this hint, the statement will fail to compile, as shown by the following update statement.

UPDATE t1 a
SET a.id = (WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(a.id)
            FROM   dual);
/
SET a.id = (WITH
            *
ERROR at line 2:
ORA-32034: unsupported use of WITH clause


SQL>

Adding the WITH_PLSQL hint allows the statement to compile and run as expected.

UPDATE /*+ WITH_PLSQL */ t1 a
SET a.id = (WITH
              FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
              BEGIN
                RETURN p_id;
              END;
            SELECT with_function(a.id)
            FROM   dual);
/

1000000 rows updated.

SQL>

DETERMINISTIC Hint

As Jonathan Lewis pointed out here, the use of functions in the WITH clause prevent the DETERMINISTIC optimizations from taking place.

SET TIMING ON ARRAYSIZE 15

WITH
  FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    DBMS_LOCK.sleep(1);
    RETURN p_id;
  END;
SELECT slow_function(id)
FROM   t1
WHERE  ROWNUM <= 10;
/

SLOW_FUNCTION(ID)
-----------------
  1
  1
  1
  1
  1
  1
  1
  1
  1
  1

10 rows selected.

Elapsed: 00:00:10.07
SQL>

So be careful that in trying to improve performance, you don't actually decrease it. This is mostly likely a bug, so retest on future releases to make sure this still applies.

Scalar Subquery Caching

In the previous section we saw the negative impact of inline function definitions on the DETERMINISTIC hint optimizations. Fortunately, scalar subquery caching is not adversely affected in the same way.

SET TIMING ON

WITH
  FUNCTION slow_function(p_id IN NUMBER) RETURN NUMBER DETERMINISTIC IS
  BEGIN
    DBMS_LOCK.sleep(1);
    RETURN p_id;
  END;
SELECT (SELECT slow_function(id) FROM dual)
FROM   t1
WHERE  ROWNUM <= 10;
/

(SELECTSLOW_FUNCTION(ID)FROMDUAL)
---------------------------------
    1
    1
    1
    1
    1
    1
    1
    1
    1
    1

10 rows selected.

Elapsed: 00:00:01.04
SQL>

For more info Refer: http://www.oracle-base.com/articles/12c/with-clause-enhancements-12cr1.php 

Interface Tables:

AP_SUPPLIERS_INT                      -Supplier Information
AP_SUPPLIER_SITES_INT - Supplier Sites Information
AP_SUP_SITE_CONTACT_INT - Supplier Contact details

This uses Vendor ID, Vendor Site Code to relate the contacts to specific vendor.

Run the following Interface Programs:
a) Supplier Open Interface Import (Inserts data into PO_VENDORS).
b) Supplier Sites Open Interface Import ( Inserts data intopo_vendor_sites_all)
c) Supplier Site Contacts Open Interface Import(Inserts data into po_vendor_contacts)

Base Tables:

po_vendors

po_vendor_sites_all

po_vendor_contacts

1)Creating Staging tables and inserting data:

CREATE TABLE XXAP_SUPP_RY(

VENDOR_INTERFACE_ID NUMBER(15) PRIMARY KEY,

VENDOR_NAME VARCHAR2(240),

SEGMENT1VARCHAR2(30),

ENABLED_FLAGVARCHAR2(1),

VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30),

TERMS_ID NUMBER,

SET_OF_BOOKS_IDNUMBER,

ACCTS_PAY_CODE_COMBINATION_ID number,

PREPAY_CODE_COMBINATION_ID NUMBER,

INVOICE_CURRENCY_CODE VARCHAR2(15),

PAYMENT_CURRENCY_CODE VARCHAR2(15));

insert into XXAP_SUPP_RY

(VENDOR_INTERFACE_ID,

VENDOR_NAME,

SEGMENT1,

ENABLED_FLAG,

VENDOR_TYPE_LOOKUP_CODE,

TERMS_ID)

values

(123123123,'RY',

'987654321','y','supplier',10003)

create table XXAP_SUPP_SITES_RY

(VENDOR_INTERFACE_ID NUMBER(15) references XXAP_SUPP_RY(VENDOR_INTERFACE_ID),

VENDOR_SITE_CODE VARCHAR2(15) not null,

ADDRESS_LINE1 VARCHAR2(240),ADDRESS_LINE2 VARCHAR2(240),ADDRESS_LINE3 VARCHAR2(240),

CITY VARCHAR2(25),STATE VARCHAR2(150),

ZIP VARCHAR2(20),COUNTRY VARCHAR2(25))

insert into XXAP_SUPP_SITES_RY

(VENDOR_INTERFACE_ID,

VENDOR_SITE_CODE,

ADDRESS_LINE1,ADDRESS_LINE2,

ADDRESS_LINE3,

CITY,STATE,

ZIP,COUNTRY

) values

(123123123,'Melbourne',

'No 82',

'Dock Lands',

' ',

'Melbourne','Melbourne',

'60001','AUSTRALIA')

CREATE TABLE XXAP_SUPP_CONTACT_RY(

VENDOR_SITE_CODE varchar2(15),

FIRST_NAME varchar2(15),

MIDDLE_NAME varchar2(15),

LAST_NAME varchar2(20),

PREFIX varchar2(5),

PHONE varchar2(15),

FAX varchar2(15),

EMAIL_ADDRESS varchar2(250),

ORG_ID number);

insert into XXAP_SUPP_CONTACT_RY(

VENDOR_SITE_CODE,

FIRST_NAME,

MIDDLE_NAME,

LAST_NAME,

PREFIX,

PHONE,

FAX,

EMAIL_ADDRESS

)

values

('Melbourne',

'Andrew ',' ','Andrew Aggasy',

'Mr',

'602345678',

'68876',

' aggasy.a@ferrari.com')

2. Creating Package to insert data into Interface Tables

create or replace package xxap_supp_pkg

is

procedure xxap_sup(errbuf out varchar2,Retcode out number);

end xxap_supp_pkg;

/

create or replace package body xxap_supp_pkg

is

procedure xxap_sup(errbuf out varchar2,Retcode out number)

is

v_accts_pay_code_comb_idNUMBER;

v_prepay_code_combination_idNUMBER;

v_invoice_currency_codeap_system_parameters_all.invoice_currency_code%TYPE;

v_payment_currency_codeap_system_parameters_all.payment_currency_code%TYPE;

v_org_id number :=204;

cursor c1 is select a.VENDOR_Interface_ID ,

a.VENDOR_NAME,

a.SEGMENT1,

a.ENABLED_FLAG,

a.VENDOR_TYPE_LOOKUP_CODE,

a.TERMS_ID,

b.VENDOR_SITE_CODE,

b.ADDRESS_LINE1,

b.ADDRESS_LINE2,

b.ADDRESS_LINE3,

b.city,

b.state,

b.country,

b.zip,

c.FIRST_NAME ,

c.MIDDLE_NAME,

c.LAST_NAME ,

c.PREFIX ,

c.PHONE ,

c.FAX,

c.EMAIL_ADDRESS

from XXAP_SUPP_RY a, XXAP_SUPP_SITES_RY b ,XXAP_SUPP_CONTACT_RY c

where a.VENDOR_INTERFACE_ID=b.VENDOR_INTERFACE_ID

and b.VENDOR_SITE_code =c.VENDOR_SITE_code;

BEGIN

FOR i IN c1

LOOP

Begin

SELECT accts_pay_code_combination_id,

prepay_code_combination_id

INTO v_accts_pay_code_comb_id

, v_prepay_code_combination_id

FROM financials_system_params_all

WHERE org_id = v_org_id;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line

(fnd_file.LOG, 'Acct_Pay_ccid and Prepayments_ccid are not available for this Org Id');

END;

BEGIN

SELECT invoice_currency_code

, payment_currency_code

INTO v_invoice_currency_code

, v_payment_currency_code

FROM ap_system_parameters_all

WHERE org_id = v_org_id;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (fnd_file.output, SQLCODE || ' ' || SQLERRM);

fnd_file.put_line(fnd_file.LOG, 'Existing Invoice and Payment Currency_Codes are notexisting

with client currency codes');

END;

fnd_file.put_line (fnd_file.output, SQLCODE || ' ' || SQLERRM);

------Inserting data into Interface Tables-----------

INSERT INTO ap_suppliers_int

(vendor_interface_id, vendor_name

,segment1, enabled_flag, vendor_type_lookup_code , terms_id,terms_name,

set_of_books_id, accts_pay_code_combination_id, created_by, creation_date, last_update_date

, last_updated_by, prepay_code_combination_id

, invoice_currency_code, payment_currency_code

)

VALUES

(i.vendor_interface_id,i.vendor_name

, i.segment1, i.enabled_flag, i.vendor_type_lookup_code , i.terms_id,'Net 45',1

, v_accts_pay_code_comb_id, -1

,SYSDATE, SYSDATE

, -1, v_prepay_code_combination_id

, v_invoice_currency_code

,v_payment_currency_code

);

INSERT INTO ap_supplier_sites_int

(vendor_interface_id, vendor_site_code

, address_line1, address_line2

, address_line3, city, state, zip

, country, created_by, creation_date

, last_update_date, last_updated_by

)

VALUES (i.vendor_interface_id, i.vendor_site_code

, i.address_line1, i.address_line2

, i.address_line3, i.city, i.state, i.zip

, i.country, -1, SYSDATE

, SYSDATE, -1

);

INSERT INTO ap_sup_site_contact_int

(vendor_site_code,first_name, middle_name, last_name, prefix, phone,

fax, EMAIL_ADDRESS, created_by

, creation_date, last_update_date

, last_updated_by

)

VALUES (i.vendor_site_code,i.first_name, i.middle_name,i.last_name,

i.prefix, i.phone, i.fax, i.EMAIL_ADDRESS, -1, SYSDATE, SYSDATE, -1);

END LOOP;

COMMIT;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line(fnd_file.LOG, 'Error in Inserting data into interface tables ');

fnd_file.put_line (fnd_file.LOG, SQLCODE || ' ' || SQLERRM);

end xxap_sup;

end xxap_supp_pkg;/

3. Create Executable and concurrent program in Application Developer.

4. Attach Concurrent Program- xxpv_supplier_int program to the Request Group.

5. Run Concurrent Program “xxpv_supplier_int program” in Payables Application.

6. Run the following Standard Concurrent programs in Account Payables Application.

Supplier Open Interface Import

Supplier Sites Open Interface Import

Supplier Site Contacts Open Interface Import

AP invoice interface

This interface helps us to import vendor invoices into Oracle applications

from external systems into Oracle Applications.

Pre-requisites:

Set of Books

Code combinations

Employees

Lookups

Interface tables:

AP_INVOICES_INTERFACE

AP_INVOICE_LINES_INTERFACE

Base tables:

AP_INVOICES_ALL – header information

AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program:

Payables Open Interface Import

Validations:

Check for valid vendor

Check for Source, Location, org_id, currency_code’s validity

Check for valid vendor site code.

Check if record already exists in payables interface table.

Some important columns that need to be populated in the interface tables:

SQL * LOADER

OPTIONS (SKIP=1)

LOAD DATA

INFILE *

APPEND

INTO TABLE apcx_il_ap_invoice_stg

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(Invoice_source,

ORG_ID,

OPERATING_UNIT,

INVOICE_TYPE_LOOKUP_CODE ,

VENDOR_NAME,

SUPPLIER_NUMBER ,

VENDOR_SITE_CODE ,

INVOICE_DATE,

INVOICE_NUM ,

INVOICE_CURRENCY_CODE,

INVOICE_AMOUNT,

EXCHANGE_RATE ,

TERM_NAME,

PAYMENT_METHOD_LOOKUP_CODE ,

PAY_GROUP_LOOKUP_CODE,

DISTRIBUTION_LINE_NUMBER ,

AMOUNT ,

LIABILITY_ACCOUNT,

PREPAYMENT_ACCT ,

DESCRIPTION,

EXCHANGE_RATE_TYPE,

EXCHANGE_DATE,

VENDOR_ID,

HEADER_GL_DATE ,

SOURCE ,

LINE_GL_DATE ,

PROCESS_FLAG,

ERROR_MESSAGE

)

STAGING TABLE

CREATE TABLE APCX_IL_AP_INVOICE_STG

(

ORG_ID NUMBER,

OPERATING_UNITVARCHAR2(100 BYTE),

INVOICE_IDNUMBER,

INVOICE_TYPE_LOOKUP_CODE VARCHAR2(100 BYTE),

INVOICE_NUM VARCHAR2(100 BYTE),

VENDOR_IDNUMBER,

VENDOR_NAME VARCHAR2(100 BYTE),

SUPPLIER_NUMBERVARCHAR2(100 BYTE),

VENDOR_SITE_CODE VARCHAR2(100 BYTE),

NEW_VENDOR_SITE_CODE VARCHAR2(100 BYTE),

VENDOR_SITE_ID NUMBER,

INVOICE_DATEDATE,

INVOICE_CURRENCY_CODE VARCHAR2(100 BYTE),

INVOICE_AMOUNT NUMBER,

NET_INV_AMOUNTNUMBER,

WITHHELD_AMOUNT NUMBER,

AMOUNT_PAIDNUMBER,

HEADER_GL_DATE DATE,

DESCRIPTION VARCHAR2(240 BYTE),

EXCHANGE_RATE_TYPE VARCHAR2(100 BYTE),

EXCHANGE_DATE DATE,

EXCHANGE_RATENUMBER,

ACCTS_PAY_CODE_COMBINATION_IDNUMBER,

LIABILITY_ACCOUNT VARCHAR2(240 BYTE),

TERMS_DATEDATE,

TERM_NAME VARCHAR2(100 BYTE),

TERM_DESCRIPTIONVARCHAR2(240 BYTE),

TERM_IDNUMBER,

PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(100 BYTE),

PAY_GROUP_LOOKUP_CODE VARCHAR2(100 BYTE),

PAYMENT_CURRENCY_CODE VARCHAR2(100 BYTE),

PAYMENT_CROSS_RATE_DATEDATE,

PAYMENT_CROSS_RATE_TYPE VARCHAR2(100 BYTE),

PAYMENT_CROSS_RATE NUMBER,

SOURCE VARCHAR2(100 BYTE),

LINES VARCHAR2(100 BYTE),

INVOICE_DISTRIBUTION_IDNUMBER,

DISTRIBUTION_LINE_NUMBER VARCHAR2(100 BYTE),

LINE_TYPE VARCHAR2(100 BYTE),

AMOUNTNUMBER,

VAT_CODE VARCHAR2(100 BYTE),

LINE_GL_DATEDATE,

DIST_CODE_COMBINATION_ID NUMBER,

PREPAYMENT_ACCTVARCHAR2(240 BYTE),

LINE_DESCRIPTION VARCHAR2(240 BYTE),

TAX_RECOVERY_RATE NUMBER,

TAX_RECOVERABLE_FLAGVARCHAR2(1 BYTE),

ASSETS_TRACKING_FLAG VARCHAR2(2 BYTE),

GROUP_ID NUMBER,

PROCESS_FLAG VARCHAR2(1 BYTE),

ERROR_MESSAGE VARCHAR2(2000 BYTE),

NEW_ACCTS_PAY_CCID_IDNUMBER,

NEW_DIST_CODE_COMBINATION_IDNUMBER,

INVOICE_SOURCE VARCHAR2(15 BYTE)

)

CREATE SYNONYM APCX_IL_AP_INVOICE_STG FOR APC_CUSTOM.APCX_IL_AP_INVOICE_STG;

CREATE OR REPLACE PACKAGE apcx_asean_ap_invoice_conv_pkg

IS

/*

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

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

* Type : Package Specification.

* Name : apcx_asean_ap_invoice_conv_pkg

* Purpose : Package Specification for APC Open Invoice-AP migration.

* Author : xxxxx.

* Date : 05-Oct-10

* Version : 1.1.0

* Description : Package Specification for APC Open Invoice-AP migration.

*'

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

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

*/

--v_gl_date DATE := '31-SEP-2010';

PROCEDURE main (retcode OUT VARCHAR2, errbuf OUT VARCHAR2 ,p_val_load

in varchar2,P_SOURCE IN VARCHAR2);

PROCEDURE VALIDATE (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2);

PROCEDURE upload_invoices (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2);

FUNCTION get_ccid(v_segments IN varchar2) RETURN NUMBER;

END apcx_asean_ap_invoice_conv_pkg;

/

CREATE OR REPLACE PACKAGE BODY apcx_asean_ap_invoice_conv_pkg

AS

/*

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

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

* Type : Package Body.

* Name : apcx_asean_ap_invoice_conv_pkg

* Purpose : Package Body for APC Open Invoice-AP migration.

* Author : xxxx.

* Date :

* Version : 1.1.0

* Description : Package Body for APC Open Invoice-AP migration.

* '

*/

g_err_msg VARCHAR2 (4000) := NULL;

g_request_idNUMBER := fnd_global.conc_request_id;

g_user_id NUMBER := fnd_profile.VALUE ('USER_ID');

g_org_id NUMBER := fnd_profile.VALUE ('ORG_ID');

g_sob_id NUMBER := fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

g_hdr_rec NUMBER := 0;

g_func_curr VARCHAR2(10);

-----------------------------------------------------

-- Process_flag details

-- 'E' - ERROR

-- 'V' - VALIDATED

-- 'Y' - PROCESSED

------------------------------------------------------

function get_ccid(v_segments IN varchar2)

return number is

v_chart_of_accounts_id number;

p_new_ccid number;

BEGIN

SELECT chart_of_accounts_id

INTO v_chart_of_accounts_id

FROM gl_sets_of_books

WHERE set_of_books_id = g_sob_id ;

-- SELECT code_combination_id

--INTO p_new_ccid

-- FROM gl_code_combinations_kfv

-- WHERE concatenated_segments =v_segments ;

SELECT code_combination_id

INTO p_new_ccid

FROM gl_code_combinations

WHERE segment1 =substr(v_segments,1,4) -------- Added by sudhir

and segment2 =substr(v_segments,6,7)

and segment3 =substr(v_segments,14,4)

and segment4=substr(v_segments,19,4)

and segment5=substr(v_segments,24,3)

and segment6=substr(v_segments,28,4)

and segment7=substr(v_segments,33,4)

and segment8=substr(v_segments,38,4)

and segment9=substr(v_segments,43,5);

return(p_new_ccid);

EXCEPTION

WHEN NO_DATA_FOUND THEN

p_new_ccid :=

fnd_flex_ext.get_ccid

(application_short_name => 'SQLGL',

key_flex_code => 'GL#',

structure_number => v_chart_of_accounts_id,

validation_date => TO_CHAR (SYSDATE, apps.fnd_flex_ext.DATE_FORMAT ),

concatenated_segments => v_segments

);

return(p_new_ccid);

end;

PROCEDURE main (retcode OUT VARCHAR2, errbuf OUT VARCHAR2, p_val_load in varchar2,P_SOURCE IN VARCHAR2)

IS

BEGIN

if p_val_load = 'VAL' then

fnd_file.put_line (fnd_file.output, '**************************************************' );

fnd_file.put_line (fnd_file.output, ' Validating records in the Staging table

apcx_il_ap_invoice_stg and apcx_il_ap_invoice_stg' );

--fnd_file.put_line (fnd_file.output, ' Calling apcx_il_ap_invoice_conv_pkg.validate' );

validate (g_org_id, g_sob_id,P_SOURCE);

end if;

--IF g_hdr_rec >0

if p_val_load = 'LOAD' then

fnd_file.put_line (fnd_file.output, ' Inserting Valdated records into Payable open Interface table' );

--fnd_file.put_line (fnd_file.output, ' Call apcx_il_ap_invoice_conv_pkg.upload_invoices' );

upload_invoices (g_org_id, g_sob_id,P_SOURCE);

fnd_file.put_line (fnd_file.output, 'Uploaded Invoices into interface tables' );

END IF;

END;

PROCEDURE VALIDATE (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2)

IS

CURSOR c_inv (cp_source varchar2)

IS

SELECT DISTINCT inv_hdr.operating_unit,

inv_hdr.invoice_type_lookup_code,

inv_hdr.invoice_num,

inv_hdr.vendor_name,

inv_hdr.supplier_number,

inv_hdr.vendor_site_code,

inv_hdr.invoice_date,

inv_hdr.invoice_currency_code,

inv_hdr.invoice_amount,

inv_hdr.header_gl_date,

inv_hdr.description,

inv_hdr.liability_account,

inv_hdr.term_name,

inv_hdr.line_type,

inv_hdr.payment_method_lookup_code,

inv_hdr.pay_group_lookup_code,

inv_hdr.SOURCE,

inv_hdr.INVOICE_SOURCE -----added sudhir

FROM apcx_il_ap_invoice_stg inv_hdr

WHERE NVL(process_flag,'X') in ('X','E')--IS NULL

--AND invoice_id between 854558 and 1382310

--and rownum<482

and inv_hdr.invoice_source=p_source

AND inv_hdr.org_id=g_org_id --and inv_hdr.header_gl_date=v_gl_date

ORDER BY supplier_number,invoice_num;

c_inv_rec c_inv%ROWTYPE;

CURSOR c_inv_line (cp_invoice_num VARCHAR2,cp_supplier_number VARCHAR2,cp_source varchar2)

IS

SELECTinv_line.invoice_num,

inv_line.distribution_line_number,

inv_line.amount,

inv_line.line_gl_date,

-- inv_line.dist_code_combination_id,

inv_line.prepayment_acct,

inv_line.line_type,

inv_line.ROWID

FROM apcx_il_ap_invoice_stg inv_line

WHERE invoice_num = cp_invoice_num

and inv_line.INVOICE_SOURCE=P_SOURCE

AND supplier_number = cp_supplier_number --AND process_flag IS NULL

ANDinv_line.org_id=g_org_id --and inv_line.line_gl_date=v_gl_date

ORDER BY distribution_line_number;

c_inv_line_recc_inv_line%ROWTYPE;

v_sourceap_lookup_codes.lookup_code%TYPE := NULL;

v_vendor_idpo_vendors.vendor_id%TYPE := NULL;

v_currency_codefnd_currencies.currency_code%TYPE := NULL;

v_pay_currency_codefnd_currencies.currency_code%TYPE := NULL;

v_vendor_site_idpo_vendor_sites_all.vendor_site_id%TYPE := NULL;

v_invoice_num_exist NUMBER := NULL;

v_term_idap_terms.term_id%TYPE := NULL;

v_period_status VARCHAR2 (30):= NULL;

v_payment_method ap_lookup_codes.lookup_code%TYPE:= NULL;

v_pay_group po_lookup_codes.lookup_code%TYPE := NULL;

v_invoice_type ap_lookup_codes.lookup_code%TYPE := NULL;

v_total_inv_amt NUMBER := NULL;

v_dist_code_ccid NUMBER:= NULL;

v_accts_pay_code_ccid NUMBER := NULL;

lp_coaid NUMBER:= NULL;

v_line_type VARCHAR2 (60);

v_line_numberNUMBER;

v_ap_batch_id NUMBER := NULL;

v_hdr_invoice_idNUMBER := NULL;

v_ap_inv_line_id NUMBER := NULL;

v_error_flagBOOLEAN;

g_err_msg VARCHAR2 (2000) := NULL;

v_ln_rec NUMBER:= 0;

v_precess_rec NUMBER := 0;

v_rate_type VARCHAR2 (20) := 'User';

v_conv_rate NUMBER;

v_exch_rate NUMBER;

v_exch_dateDATE;

v_exch_type VARCHAR2 (20);

v_pymt_rateNUMBER;

v_pymt_date DATE;

v_pymt_type VARCHAR2 (20);

v_countnumber:=0;

v_dist_amt NUMBER:=0;

e_inv_hdr_excepEXCEPTION;

e_inv_line_excep EXCEPTION;

line_amt number(10,2);

BEGIN

v_error_flag := FALSE;

SELECT ap_batches_s.NEXTVAL

INTO v_ap_batch_id

FROM DUAL;

--dbms_output.put_line('Batch:'||v_ap_batch_id);

fnd_file.put_line(fnd_file.output,'Batch:'||v_ap_batch_id);

SELECT currency_code

INTO g_func_curr

FROM gl_sets_of_books

WHERE set_of_books_id = g_sob_id;

fnd_file.put_line(fnd_file.output,'Func Curr:'||g_func_curr );

FOR c_inv_rec IN c_inv(p_source)

LOOP

--dbms_output.put_line('HeaderLoop');

--fnd_file.put_line(fnd_file.output,'HeaderLoop');

--dbms_output.put_line('Invoice# '||c_inv_rec.invoice_num);

fnd_file.put_line(fnd_file.output,'Invoice# '||c_inv_rec.invoice_num);

v_line_number := 0;

v_precess_rec := v_precess_rec+1;

line_amt:=0;

--Update apcx_il_ap_invoice_stg table amount column with invoice amount

/*UPDATE apcx_il_ap_invoice_stg

set amount=c_inv_rec.invoice_amount

where invoice_num=c_inv_rec.invoice_num;*/

BEGIN

g_err_msg := NULL;

v_error_flag := FALSE;

--

-- Validation for Source

--

/*BEGIN

SELECT lookup_code

INTO v_source

FROM ap_lookup_codes

WHERE lookup_type = 'SOURCE'

AND UPPER (displayed_field) = UPPER (c_inv_rec.SOURCE)

AND enabled_flag = 'Y'

AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,SYSDATE))

AND TRUNC (NVL (inactive_date,SYSDATE));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :='Invalid Invoice Source-'|| v_source|| ', Error='|| SUBSTR (SQLERRM, 1, 2000);

dbms_output.put_line(g_err_msg);

fnd_file.put_line (fnd_file.output, g_err_msg);

END;*/

v_source :='CONVERSION';

--

--Validation for Invoice Amount#

--

BEGIN

SELECT sum(amount)

INTO v_dist_amt

FROM apcx_il_ap_invoice_stg

WHERE invoice_num = c_inv_rec.invoice_num

and supplier_number = c_inv_rec.supplier_number;

--IF v_dist_amt <> c_inv_rec.net_inv_amount

--THEN

--v_error_flag := TRUE;

--g_err_msg :=' Invoice Header Amount:'||c_inv_rec.net_inv_amount|| ' NOT EQUAL

to net Distribution Amount:'||v_dist_amt;

fnd_file.put_line (fnd_file.output,g_err_msg);

--END IF;

END;

--

--Validation for Vendor

--

fnd_file.put_line(fnd_file.output,'Vendor Id Begin');

BEGIN

SELECT po.vendor_id

INTO v_vendor_id

FROM po_vendors po , po_vendor_sites_all pv

WHERE --vendor_name = c_inv_rec.vendor_name and

--SEGMENT1 = c_inv_rec.supplier_number

--attribute9=c_inv_rec.SUPPLIER_NUMBER

po.vendor_id = pv.vendor_id

and pv.attribute9 = c_inv_rec.SUPPLIER_NUMBER

and pv.attribute11 = c_inv_rec.invoice_source

AND pv.org_id=g_org_id

-- and upper(vendor_name) = trim(upper(c_inv_rec.VENDOR_NAME)) -- Added BY Sudhir 04sep09

AND enabled_flag = 'Y'

AND TRUNC (NVL (end_date_active, TRUNC (SYSDATE))) >=TRUNC (SYSDATE);

fnd_file.put_line(fnd_file.output,'Vendor_id:'||v_vendor_id);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Vendor Name: '|| c_inv_rec.vendor_name;fnd_file.put_line

(fnd_file.output, 'INVOICE_NUM='|| c_inv_rec.invoice_num

|| g_err_msg

);

END;

--

--Validation for Invoice Number--

--

BEGIN

SELECT count(1)

INTO v_count

FROM ap_invoices_all

WHERE invoice_num = c_inv_rec.invoice_num

AND vendor_id = v_vendor_id

AND org_id = g_org_id;

IF v_count > 0

THEN

v_error_flag := TRUE;

g_err_msg :=' Duplicate Invoice Number:'||c_inv_rec.invoice_num;

fnd_file.put_line (fnd_file.output,g_err_msg);

END IF;

END;

-- Validation for Invoice Currency Code

--

BEGIN

SELECT currency_code

INTO v_currency_code

FROM fnd_currencies

WHERE currency_code = UPPER (c_inv_rec.invoice_currency_code);

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=

' Invalid Invoice Currency code Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num ||' '||

c_inv_rec.invoice_currency_code|| g_err_msg);

END;

/* IF v_currency_code <> g_func_curr

THEN

BEGIN

SELECT conversion_rate

INTOv_conv_rate

FROM gl_daily_rates

WHERE conversion_type = v_rate_type

and from_currency = v_currency_code

and to_currency = g_func_curr

and conversion_date = v_gl_date

;

v_exch_rate := NULL;

v_exch_date := v_gl_date;

v_exch_type := v_rate_type;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg := ' Invalid Conversion Rate for Invoice Currency Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||' '||

c_inv_rec.invoice_currency_code|| g_err_msg);

END;

ELSE

v_exch_rate := NULL;

v_exch_date := v_gl_date;

v_exch_type := 'User';

END IF;*/

--

-- Validation for Invoice Payment Currency Code

--

/*BEGIN

SELECT currency_code

INTO v_pay_currency_code

FROM fnd_currencies

WHERE currency_code = UPPER (c_inv_rec.payment_currency_code);

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=g_err_msg|| 'Invalid Payment Currency code Error='|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.payment_currency_code|| g_err_msg);

END;

IF v_pay_currency_code <> g_func_curr

THEN

BEGIN

SELECT conversion_rate

INTOv_conv_rate

FROM gl_daily_rates

WHERE conversion_type = v_rate_type

and from_currency = v_pay_currency_code

and to_currency = g_func_curr

and conversion_date = v_gl_date

;

v_pymt_rate := v_conv_rate;

v_pymt_date := v_gl_date;

v_pymt_type := v_rate_type;

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=g_err_msg

|| 'Invalid Conversion Rate for Payment Currency Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.payment_currency_code|| g_err_msg);

END;

ELSE

v_pymt_rate := c_inv_rec.exchange_rate;

v_pymt_date := c_inv_rec.exchange_date;

v_pymt_type := c_inv_rec.exchange_rate_type;

END IF;*/

--

--validation for vendor Site and Pay Site Flag

--

BEGIN

SELECT vendor_site_id

INTO v_vendor_site_id

FROM po_vendor_sites_all

WHERE vendor_id = v_vendor_id

AND vendor_site_code = c_inv_rec.vendor_site_code

AND org_id = g_org_id

AND pay_site_flag = 'Y'

AND TRUNC (NVL (inactive_date, TRUNC (SYSDATE))) >=TRUNC (SYSDATE);

EXCEPTION

WHEN no_data_found

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Vendor Site code: '|| c_inv_rec.vendor_site_code

|| ':There is no such vendorsite code';

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num|| g_err_msg);

WHEN OTHERS THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Vendor Site code: '

|| c_inv_rec.vendor_site_code

|| ':There is no such vendorsite code';

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num|| g_err_msg);

END;

--

-- Validation for the Liability Account

--

v_accts_pay_code_ccid:=get_ccid(c_inv_rec.liability_account);

IF v_accts_pay_code_ccid =0

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Liability Account:'|| c_inv_rec.liability_account;

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num|| g_err_msg);

END IF;

--

--Validation for Payment Term

--

BEGIN

SELECT term_id

INTO v_term_id

FROM ap_terms

WHERE UPPER (NAME) =UPPER (NVL (c_inv_rec.term_name, 'Immediate'))

AND enabled_flag = 'Y'

AND TRUNC (NVL (end_date_active, TRUNC (SYSDATE))) >=

TRUNC (SYSDATE);

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=' Invalid Invoice Terms Name, Error='|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||' '||

c_inv_rec.term_name|| g_err_msg);

END;

--

--Varify the Open AP Period

--

/*BEGIN

SELECT a.closing_status

INTO v_period_status

FROM gl_period_statuses a, fnd_application b

WHERE a.application_id = b.application_id

AND b.application_short_name = 'SQLAP'

AND a.set_of_books_id = g_sob_id

AND (TRUNC (c_inv_rec.header_gl_date)BETWEEN TRUNC (start_date)AND TRUNC (end_date));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg :=g_err_msg

|| 'Invoice Header, AP Period,either not defined or, Error='

|| SUBSTR (SQLERRM, 1, 2000);

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.invoice_date|| g_err_msg);

END;

--Verify for Open period

IF v_period_status <> 'O'

THEN

v_error_flag := TRUE;

g_err_msg := g_err_msg || 'Invoide Header, Perod Not Open';

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='|| c_inv_rec.invoice_num||

c_inv_rec.invoice_date|| g_err_msg);

END IF;*/

--

-- Validate PAYMENT_METHOD_LOOKUP_CODE

--

BEGIN

SELECT lookup_code

INTO v_payment_method

FROM ap_lookup_codes

WHERE lookup_type = 'PAYMENT METHOD'

AND UPPER (lookup_code) =UPPER (c_inv_rec.payment_method_lookup_code)

AND enabled_flag = 'Y'

AND TRUNC (SYSDATE) BETWEEN TRUNC (NVL (start_date_active,SYSDATE - 1))

AND TRUNC (NVL (inactive_date,SYSDATE + 1));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg := ' Invalid Payment Method Lookup Code: ' ||

c_inv_rec.payment_method_lookup_code || ', Error=' || SQLERRM;

fnd_file.put_line (fnd_file.output,'INVOICE_NUM='||

c_inv_rec.invoice_num||' '|| g_err_msg);

END;

--

-- Validate PAY_GROUP_CODE

--

BEGIN

IF c_inv_rec.pay_group_lookup_code IS NOT NULL

THEN

SELECT lookup_code

INTO v_pay_group

FROM po_lookup_codes

WHERE lookup_type = 'PAY GROUP'

AND UPPER (lookup_code) =UPPER (c_inv_rec.pay_group_lookup_code)

AND enabled_flag = 'Y'

AND NVL (inactive_date, SYSDATE + 1) > TRUNC (SYSDATE);

END IF;

EXCEPTION

WHEN OTHERS

THEN

v_pay_group := NULL;

v_error_flag := FALSE;

END;

--

--Update the invoice header stagging table

--

IF v_error_flag = TRUE

THEN

RAISE e_inv_hdr_excep;

ELSE

--

--loop start for Invoice lines

--

--FOR c_inv_line_rec IN c_inv_line (c_inv_rec.Line_num)

FOR c_inv_line_rec IN c_inv_line (c_inv_rec.invoice_num, c_inv_rec.supplier_number,p_source)

LOOP

--Initialize Inv Line variables

v_error_flag := FALSE;

g_err_msg := NULL;

v_ap_inv_line_id := NULL;

v_dist_code_ccid := NULL;

-- Line Number Counter serial Number

v_line_number := v_line_number + 1;

BEGIN

--

-- Validate the Line Type

--

BEGIN

SELECT lookup_code

INTO v_line_type

FROM ap_lookup_codes

WHERE lookup_type = 'INVOICE DISTRIBUTION TYPE'

AND UPPER (displayed_field) = UPPER (NVL (c_inv_line_rec.line_type,'ITEM'));

EXCEPTION

WHEN OTHERS

THEN

v_error_flag := TRUE;

g_err_msg := ' Invalid Line Type';

fnd_file.put_line (fnd_file.output,'LINE TYPE:' || c_inv_line_rec.line_type);

END;

--

--Validation for CCID for distribution

--

v_dist_code_ccid:=get_ccid(c_inv_line_rec.prepayment_acct);

IF v_dist_code_ccid = 0

THEN

v_error_flag := TRUE;

g_err_msg := ' No Mapping for Invoice Distribution Code Combination:'||c_inv_line_rec.prepayment_acct;

fnd_file.put_line

(fnd_file.output,'INVOICE_NUM :'|| c_inv_rec.invoice_num|| ', LINE :'|| c_inv_line_rec.distribution_line_number

|| g_err_msg);

END IF;

--

--Update for Validated records.

--

IF v_error_flag = FALSE

THEN

v_invoice_type :=UPPER (c_inv_rec.invoice_type_lookup_code);

UPDATE apcx_il_ap_invoice_stg

SET GROUP_ID = v_ap_batch_id,

invoice_type_lookup_code = v_invoice_type,

payment_method_lookup_code = v_payment_method,

line_type='ITEM',

pay_group_lookup_code = v_pay_group,

invoice_currency_code = v_currency_code,

vendor_id = v_vendor_id,

vendor_site_id = v_vendor_site_id,

--amount=line_amt ,

new_dist_code_combination_id = v_dist_code_ccid,

new_accts_pay_ccid_id = v_accts_pay_code_ccid,

--header_gl_date = v_gl_date,

--line_gl_date = v_gl_date,

source = v_source,

-- exchange_date = v_exch_date,

-- exchange_rate = v_exch_rate,

-- exchange_rate_type = v_exch_type,

process_flag = 'V',

error_message = 'VALIDATED'

WHERE invoice_num = c_inv_line_rec.invoice_num

AND distribution_line_number = c_inv_line_rec.distribution_line_number

AND SUPPLIER_NUMBER = c_inv_rec.SUPPLIER_NUMBER;

COMMIT;

ELSE

RAISE e_inv_hdr_excep;--Raise the Header exception instead of line exception

due to no more line should be marked as validated.

--

--If line errored out don't process header and lines

--

END IF;

EXCEPTION

WHEN e_inv_line_excep

THEN

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = c_inv_line_rec.invoice_num

AND distribution_line_number =c_inv_line_rec.distribution_line_number;

END;

END LOOP; -- Invoice Lines' Loop--

g_hdr_rec := g_hdr_rec +1;

END IF;

EXCEPTION

WHEN e_inv_hdr_excep

THEN

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = c_inv_rec.invoice_num

ANDSUPPLIER_NUMBER = c_inv_rec.SUPPLIER_NUMBER;

COMMIT;

WHEN OTHERS

THEN

g_err_msg:=sqlerrm;

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = c_inv_rec.invoice_num

ANDSUPPLIER_NUMBER = c_inv_rec.SUPPLIER_NUMBER;

END;

END LOOP; -- Invoice Header Loop --

fnd_file.put_line (fnd_file.output,'-----------------------------------------');

fnd_file.put_line (fnd_file.output,'Total Invoice records Processed:'||v_precess_rec);

fnd_file.put_line (fnd_file.output,' Total Invoice records Validated:'||g_hdr_rec);

fnd_file.put_line (fnd_file.output,'-----------------------------------------');

END VALIDATE;

PROCEDURE upload_invoices (g_org_id IN NUMBER, g_sob_id IN NUMBER,P_SOURCE IN VARCHAR2)

IS

CURSOR c_inv(cp_source varchar2)

IS

SELECT DISTINCT inv_hdr.GROUP_ID,

inv_hdr.invoice_type_lookup_code,

inv_hdr.invoice_num,

inv_hdr.supplier_number,

inv_hdr.vendor_id,

inv_hdr.vendor_site_id,

inv_hdr.invoice_date,

inv_hdr.invoice_currency_code,

inv_hdr.invoice_amount,

inv_hdr.header_gl_date,

inv_hdr.description,

inv_hdr.exchange_rate,

inv_hdr.new_accts_pay_ccid_id,

inv_hdr.payment_method_lookup_code,

inv_hdr.pay_group_lookup_code,

inv_hdr.SOURCE

FROM apcx_il_ap_invoice_stg inv_hdr

WHERE process_flag = 'V'

AND inv_hdr.org_id=g_org_id

AND inv_hdr.INVOICE_SOURCE=P_SOURCE

ORDER BY supplier_number,invoice_num;

c_inv_rec c_inv%ROWTYPE;

CURSOR c_inv_line (cp_invoice_num VARCHAR2,cp_supplier_number VARCHAR2,cp_source varchar2)

IS

SELECTinv_line.invoice_num,

inv_line.distribution_line_number,

inv_line.line_type,

inv_line.amount,

inv_line.line_gl_date,

inv_line.new_dist_code_combination_id,

inv_line.prepayment_acct,

inv_line.ROWID

FROM apcx_il_ap_invoice_stg inv_line

WHERE invoice_num = cp_invoice_num --AND process_flag = 'V'

AND supplier_number = cp_supplier_number

AND inv_line.org_id=g_org_id

AND inv_line.INVOICE_SOURCE=P_SOURCE

ORDER BY distribution_line_number;

v_line_num NUMBER;

v_hdr_rec NUMBER :=0;

BEGIN

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'INSERTING RECORDS IN AP_INVOICES_INTERFACE TABLE');

FOR inv_hdr IN c_inv(p_source)

LOOP

v_line_num := 0;

--fnd_file.put_line (fnd_file.output,'Invoice#'|| inv_hdr.invoice_num);

BEGIN

INSERT INTO ap_invoices_interface

(GROUP_ID,

invoice_id,

invoice_num,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

exchange_rate,

exchange_rate_type,

exchange_date,

description,

SOURCE,

payment_method_lookup_code,

pay_group_lookup_code,

gl_date,

accts_pay_code_combination_id,

org_id,

created_by,

creation_date,

last_updated_by,

last_update_date

)

VALUES (inv_hdr.GROUP_ID,

ap_invoices_interface_s.NEXTVAL,

inv_hdr.invoice_num,

inv_hdr.invoice_type_lookup_code,

inv_hdr.invoice_date,

inv_hdr.vendor_id,

inv_hdr.vendor_site_id,

inv_hdr.invoice_amount,

inv_hdr.invoice_currency_code,

inv_hdr.exchange_rate,

'User',

sysdate,

inv_hdr.description,

inv_hdr.SOURCE,

inv_hdr.payment_method_lookup_code,

inv_hdr.pay_group_lookup_code,

inv_hdr.header_gl_date,

inv_hdr.new_accts_pay_ccid_id,

g_org_id,

g_user_id,

SYSDATE,

g_user_id,

SYSDATE

);

--FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'INSERTING RECORDS IN AP_INVOICE_LINES_INTERFACE TABLE');

FOR inv_line IN c_inv_line (inv_hdr.invoice_num,inv_hdr.supplier_number,p_source)

LOOP

v_line_num := v_line_num + 1;

BEGIN

INSERT INTO ap_invoice_lines_interface

(invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id,

created_by,

creation_date,

last_updated_by,

last_update_date

)

VALUES (ap_invoices_interface_s.CURRVAL,

ap_invoice_lines_interface_s.NEXTVAL,

v_line_num,

inv_line.line_type,

inv_line.amount,

inv_line.line_gl_date,

inv_line.new_dist_code_combination_id,

g_user_id,

SYSDATE,

g_user_id,

SYSDATE

);

UPDATE apcx_il_ap_invoice_stg

SETprocess_flag = 'P',

error_message = 'INSERTED'

WHERE invoice_num = inv_hdr.invoice_num

AND distribution_line_number =inv_line.distribution_line_number

AND SUPPLIER_NUMBER = inv_hdr.SUPPLIER_NUMBER;

EXCEPTION

WHEN OTHERS

THEN

g_err_msg :='Invoice#'||inv_hdr.invoice_num||' DistLine#'||inv_line.distribution_line_number ||' '||SQLERRM;

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = inv_hdr.invoice_num

AND distribution_line_number = inv_line.distribution_line_number

AND SUPPLIER_NUMBER = inv_hdr.SUPPLIER_NUMBER;

END;

END LOOP;

v_hdr_rec := v_hdr_rec+1;

EXCEPTION

WHEN OTHERS

THEN

g_err_msg :='Invoice#'||inv_hdr.invoice_num||' '||SQLERRM;

UPDATE apcx_il_ap_invoice_stg

SET process_flag = 'E',

error_message = g_err_msg

WHERE invoice_num = inv_hdr.invoice_num

AND SUPPLIER_NUMBER = inv_hdr.SUPPLIER_NUMBER;

END;

END LOOP;

fnd_file.put_line (fnd_file.output,'----------------------------------');

fnd_file.put_line (fnd_file.output,' Total Invoice Inserted:'||v_hdr_rec);

fnd_file.put_line (fnd_file.output,'----------------------------------');

COMMIT;

END upload_invoices;

END apcx_asean_ap_invoice_conv_pkg;

/

===============================================================================

AP INTERFACE VALIDATION

Create Or REplace Procedure xx_invocie(errbuf out varchar2,
retcode out varchar2) as
cursor c1 is select * from xx_inv_headers;
cursor c2 is select * from xx_inv_dist;
i_num Varchar2(100);
l_flag Varchar2(1) default 'A'
begin



for x1 in c1 loop

Begin
-- Invoice Number Validation
SELECT invoice_num
INTO i_num
FROM ap_invoices_all
WHERE invoice_num = x1.invoice_num
and vendor_id = x1.vendor_id ;
Fnd_File.put_line(Fnd_File.output,'Invoice Number is Duplicate');
l_flag:='E';
Exception
When no_data_found then
Fnd_File.put_line(Fnd_File.output,'Invoice Number Valid');
l_flag:='A';
End;



insert into AP_INVOICES_INTERFACE(INVOICE_ID,
INVOICE_NUM,
INVOICE_TYPE_LOOKUP_CODE,
INVOICE_DATE,
VENDOR_ID,
VENDOR_SITE_ID,
INVOICE_AMOUNT,
INVOICE_CURRENCY_CODE,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SOURCE,
GL_DATE)
values(AP_INVOICES_INTERFACE_S.NEXTVAL,
X1.INVOICE_NUM,
X1.INVOICE_TYPE_LOOKUP_CODE,
X1.INVOICE_DATE,
X1.VENDOR_ID,
X1.VENDOR_SITE_ID,
X1.INVOICE_AMOUNT,
X1.INVOICE_CURRENCY_CODE,
X1.DESCRIPTION,
X1.CREATED_BY,
SYSDATE,
SYSDATE,
X1.LAST_UPDATED_BY,
SYSDATE,
X1.SOURCE,
X1.GL_DATE);
END LOOP;

FOR X2 IN C1 LOOP
begin
SELECT CODE_COMBINATION_ID
INTO l_code_id
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = x2.dist_code_combination_id;
exception
When others then
Fnd_File.put_line(Fnd_File.output,'Invalid Code Combination ID');
End;

Begin
SELECT lookup_code
into L_lookup
FROM ap_lookup_codes
WHERE LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
AND LOOKUP_CODE = X2.line_type_lookup_code;
exception
When others then
Fnd_File.put_line(Fnd_File.output,'Invalid Lookup');
End;


Insert into AP_INVOICE_LINES_INTERFACE
(
invoice_id,
invoice_line_id,
amount,
dist_code_combination_id,
description,
line_number,
line_type_lookup_code,
accounting_date,
creation_date,
created_by
)
values
(
APPS.AP_INVOICES_INTERFACE_S.CURRVAL,
APPS.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL, 
X2.AMOUNT,
X2.DIST_CODE_COMBINATION_ID,
X2.DESCRIPTION,
X2.LINE_NUMBER,
X2.LINE_TYPE_LOOKUP_CODE,
X2.ACCOUNTING_DATE,
X2.CREATION_DATE,
X2.CREATED_BY)
END LOOP;
END;