AP INTERFACE
AP INTERFACE
Interface Tables:
AP_SUPPLIERS_INT - Supplier Information
AP_SUPPLIER_SITES_INT - Supplier Sites Information
AP_SUP_SITE_CONTACT_INT - Supplier Contact details
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 into po_vendor_sites_all)
c) Supplier Site Contacts Open Interface Import(Inserts data into po_vendor_contacts)
Run the following Interface Programs:
a) Supplier Open Interface Import (Inserts data into PO_VENDORS).
b) Supplier Sites Open Interface Import ( Inserts data into po_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),
SEGMENT1 VARCHAR2(30),
ENABLED_FLAG VARCHAR2(1),
VENDOR_TYPE_LOOKUP_CODE VARCHAR2(30),
TERMS_ID NUMBER,
SET_OF_BOOKS_ID NUMBER,
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',
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_id NUMBER;
v_prepay_code_combination_id NUMBER;
v_invoice_currency_code ap_system_parameters_all.invoice_currency_code%TYPE;
v_payment_currency_code ap_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_UNIT VARCHAR2(100 BYTE),
INVOICE_ID NUMBER,
INVOICE_TYPE_LOOKUP_CODE VARCHAR2(100 BYTE),
INVOICE_NUM VARCHAR2(100 BYTE),
VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2(100 BYTE),
SUPPLIER_NUMBER VARCHAR2(100 BYTE),
VENDOR_SITE_CODE VARCHAR2(100 BYTE),
NEW_VENDOR_SITE_CODE VARCHAR2(100 BYTE),
VENDOR_SITE_ID NUMBER,
INVOICE_DATE DATE,
INVOICE_CURRENCY_CODE VARCHAR2(100 BYTE),
INVOICE_AMOUNT NUMBER,
NET_INV_AMOUNT NUMBER,
WITHHELD_AMOUNT NUMBER,
AMOUNT_PAID NUMBER,
HEADER_GL_DATE DATE,
DESCRIPTION VARCHAR2(240 BYTE),
EXCHANGE_RATE_TYPE VARCHAR2(100 BYTE),
EXCHANGE_DATE DATE,
EXCHANGE_RATE NUMBER,
ACCTS_PAY_CODE_COMBINATION_ID NUMBER,
LIABILITY_ACCOUNT VARCHAR2(240 BYTE),
TERMS_DATE DATE,
TERM_NAME VARCHAR2(100 BYTE),
TERM_DESCRIPTION VARCHAR2(240 BYTE),
TERM_ID NUMBER,
PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(100 BYTE),
PAY_GROUP_LOOKUP_CODE VARCHAR2(100 BYTE),
PAYMENT_CURRENCY_CODE VARCHAR2(100 BYTE),
PAYMENT_CROSS_RATE_DATE DATE,
PAYMENT_CROSS_RATE_TYPE VARCHAR2(100 BYTE),
PAYMENT_CROSS_RATE NUMBER,
SOURCE VARCHAR2(100 BYTE),
LINES VARCHAR2(100 BYTE),
INVOICE_DISTRIBUTION_ID NUMBER,
DISTRIBUTION_LINE_NUMBER VARCHAR2(100 BYTE),
LINE_TYPE VARCHAR2(100 BYTE),
AMOUNT NUMBER,
VAT_CODE VARCHAR2(100 BYTE),
LINE_GL_DATE DATE,
DIST_CODE_COMBINATION_ID NUMBER,
PREPAYMENT_ACCT VARCHAR2(240 BYTE),
LINE_DESCRIPTION VARCHAR2(240 BYTE),
TAX_RECOVERY_RATE NUMBER,
TAX_RECOVERABLE_FLAG VARCHAR2(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_ID NUMBER,
NEW_DIST_CODE_COMBINATION_ID NUMBER,
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_id NUMBER := 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
SELECT inv_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
AND inv_line.org_id=g_org_id --and inv_line.line_gl_date=v_gl_date
ORDER BY distribution_line_number;
c_inv_line_rec c_inv_line%ROWTYPE;
v_source ap_lookup_codes.lookup_code%TYPE := NULL;
v_vendor_id po_vendors.vendor_id%TYPE := NULL;
v_currency_code fnd_currencies.currency_code%TYPE := NULL;
v_pay_currency_code fnd_currencies.currency_code%TYPE := NULL;
v_vendor_site_id po_vendor_sites_all.vendor_site_id%TYPE := NULL;
v_invoice_num_exist NUMBER := NULL;
v_term_id ap_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_number NUMBER;
v_ap_batch_id NUMBER := NULL;
v_hdr_invoice_id NUMBER := NULL;
v_ap_inv_line_id NUMBER := NULL;
v_error_flag BOOLEAN;
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_date DATE;
v_exch_type VARCHAR2 (20);
v_pymt_rate NUMBER;
v_pymt_date DATE;
v_pymt_type VARCHAR2 (20);
v_count number:=0;
v_dist_amt NUMBER:=0;
e_inv_hdr_excep EXCEPTION;
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
INTO v_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
INTO v_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
AND SUPPLIER_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
AND SUPPLIER_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
SELECT inv_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
SET process_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;
/
No comments:
Post a Comment