Tuesday, 24 September 2013

WITH Clause


WITH Clause : Subquery Factoring

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

Related articles.

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.
Related articles.

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 
Posted by at No comments:
Labels:

Monday, 16 September 2013

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;
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)