Santoshkumaraddar's Oracle Financial Blog: September 2016

AP Interface

Procedure:

Best Practices for interfaces as per my experience

1. Create a staging table from mandatory, conditional required, optional required columns from interface select statement as xx_table_name

2. alter the table add columns as process flags, error flags

3. Load the data through sql loader as per client requirements

4. write a plsql procedures to validate data and transfer to interface tables

5. submit standard program to import data

https://drive.google.com/open?id=0B7PbLub7Vp-1aWd0T3ZDZGZIQzg

AP Invoice Interface

Interface Table: AP_INVOICES_INTERFACE,

AP_INVOICE_LINES_INTERFACE,

Error Tables

AP_INTERFACE_REJECTIONS,

AP_INTERFACE_CONTROLS

Base Tables

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

Concurrent Program = Payables Open Interface Import

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

Account’s Payables Invoices Interface and Loader Process Details

1) Payables Interface:
-----------------
Pre-requisites:
--------------
a) Vendors and their Sites Should Be Setup
b) Currency and exchange Rates Should Be setup
c) Source and invoice type lookup code must be defined
d) Account Payables Account and Distribution Account must be defined.
e) Payment Terms should be defines and GL Period must be in opened status.

Interface Tables:
----------------
AP_INVOICES_INTERFACE
AP_INVOCE_LINES_INTERFACE

Error Tables:
------------
AP_INTERFACE_REJECTIONS
AP_INTERFACE_CONTROLS

BASE TABLES :
------------
AP_INVOICES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_PAYMENT_SCHEDULES

INTERFACE PROGRAM:
-----------------
Payables Open Interface Import

Parameters to be passed during the program:
-----------------------------------------
Source = External
group_id = As Specified in the data file
Batch Name = N/A
Hold Name = null
Hold Reason = null
gl date = null
Purge = No
Summarize = No

Main Columns In AP_INVOICES_INTERFACE:
--------------------------------------
INVOICE_ID,

INVOICE_NUM,

INVOICE_DATE,

INVOICE_AMOUNT
VENDOR_ID,

VENDOR_NUM,

VENDOR_NAME,

VENDOR_SITE_CODE,

VENDOR_SITE_ID
TERMS_NAME,

TERM_ID,

TERM_DATE,

SOURCE,

PAYMENT_METHOD_LOOKUP_CODE,
GL_DATE,

PO_NUMBER,

INVOICE_CURRENCE_CODE,

EXCHANGE_RATE_TYPE
VOUCHER_NUMBER,

PAYMENT_CURRENCY_CODE,

DOC_CATEGPRY_CODE

Main Columns In AP_INVOICE_LINES_INTERFACE:
------------------------------------------
INVOICE_ID ,

INVOICE_LINE_ID,

LINE_NUM,

LINE_TYPE_LOOKUP_CODE,
AMOUNT,

DIST_CODE_COMBINATION_ID,

DIST_CODE_CONCATENATED
PO_HEADER_ID,

PO_LINE_ID,

PO_LINE_NUMBER

Tables To Be Used For Validation:
--------------------------------
PO_VENDORS / AP_SUPPLIERS

AP_TERMS

ORG_ORGANIZATION_DEFINITIONS
FND_CURRNECIES

AP_INVOICES_ALL

GL_DAILY_CONVERSION_TYPES
GL_CODE_COMBINATIONS

AP_LOOKUP_CODES GL_PERIOD_STATUSES

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

Staging Table Creation:

CREATE TABLE XX_AP_INV_STAGE

(

Invoice_Id NUMBER PRIMARY KEY,

Invoice_Type_Lookup_Code VARCHAR2 (80),

Vendor_Number VARCHAR2 (240) NOT NULL,

Vendor_Site_Code VARCHAR2 (30) NOT NULL,

Invoice_Num VARCHAR2 (50) NOT NULL,

Invoice_Date DATE NOT NULL,

Invoice_Currency_Code VARCHAR2 (15) NOT NULL,

Exchange_Rate NUMBER NOT NULL,

Invoice_Amount NUMBER NOT NULL,

Description VARCHAR2 (240),

Po_Number VARCHAR2 (20),

Liability_Account VARCHAR2 (100),

Desc_liability_acc VARCHAR2 (240),

Tax_code VARCHAR2 (15),

Tax_Amount NUMBER,

Inv_Recieved_Date DATE NOT NULL,

Created_Date DATE NOT NULL,

Terms_name VARCHAR2 (50) NOT NULL,

Terms_Date DATE NOT NULL,

Pay_Group_Lookup_Code VARCHAR2 (25),

Payment_Currency_Code VARCHAR2 (15),

Payment_Method_Lookup_Code VARCHAR2 (25) NOT NULL,

GL_Date DATE NOT NULL,

Invoice_Source VARCHAR2 (80) NOT NULL,

Validation_Record_Flag VARCHAR2 (1),

Error_Message VARCHAR2 (2000),

Created_by NUMBER,

Creation_date DATE,

Last_Updated_by NUMBER,

Last_Update_Date DATE,

Last_Update_Login NUMBER

)

Inserting Data into Staging Table:

INSERT INTO XX_AP_INV_STAGE

VALUES (

20000,

'STANDARD',

5033,

'EIP MAIN',

'B213',

'17-Feb-05',

'USD',

'10459.63',

NULL,

'3940',

'01-000-2210-0000-000',

'Operations-No Department-Accounts Payable-No Sub Account-No Product',

'VAT-15%',

'1000',

'17-Feb-05',

'23-Feb-05',

'45 Net (terms date + 45)',

'23-Feb-05',

'SIZZLE',

'USD',

'CHECK',

'17-Feb-05',

'ERS');

Moving the Data from Flat File to Base Table using SQL * LOADER:

options(skip=1)

load data

errors=5

infile '/ebs12/oracle/apps/apps_st/appl/ar/12.0.0/bin/customer flat file.csv'

insert into table XX_AP_INV_STAGE

---append into table xxcustomersstg

---replace into table xxcustomersstg

---truncate into table xxcustomersstg

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

( Invoice_Id

,Invoice_Type_Lookup_Code

,Vendor_Number

,Vendor_Site_Code

,Invoice_Num

,Invoice_Date

,Invoice_Currency_Code

,Exchange_Rate

,Invoice_Amount

,Description

,Po_Number

,Liability_Account

,Desc_liability_acc

,Tax_code

,Tax_Amount

,Inv_Recieved_Date

,Created_Date

,Terms_name

,Terms_Date

,Pay_Group_Lookup_Code

,Payment_Currency_Code

,Payment_Method_Lookup_Code

,GL_Date

,Invoice_Source

,Validation_Record_Flag CONSTANT "N"

,Created_by CONSTANT "-1"

,Creation_date SYSDATE

,Last_Updated_by CONSTANT "-1"

,Last_Update_Date SYSDATE

,Last_Update_Login CONSTANT "-1"

)

Single Insert:

INSERT INTO AP_INVOICES_INTERFACE

(INVOICE_ID, INVOICE_NUM, INVOICE_TYPE_LOOKUP_CODE, VENDOR_ID,

VENDOR_SITE_ID,

INVOICE_AMOUNT, INVOICE_CURRENCY_CODE, SOURCE, GROUP_ID, TERMS_ID)

VALUES

(88888, 'INV‐8888', 'STANDARD', 24935, 5044, 1000,

'USD', 'INTERCOMPANY', 'TESTCASE1', 10234);

INSERT INTO AP_INVOICE_LINES_INTERFACE

(INVOICE_ID, INVOICE_LINE_ID, LINE_NUMBER, LINE_TYPE_LOOKUP_CODE, AMOUNT,

DIST_CODE_COMBINATION_ID)

VALUES

(88888, 99999,1,'ITEM',1000, 13805);

If the record is loaded successfully into base tables then

STATUS.ap_invoices_interface IS PROCESSED otherwise

STATUS.ap_invoices_interface IS REJECTED

Package, Package Body OR PROCEDURE

CREATE OR REPLACE Package XX_AP_INV_PKG IS

-- Creating Package Specification

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

---PROCESS STATUS VALUES AND THERE MEANINGS ----

-- N --- New

-- V --- Valid

-- E --- Error

-- P --- Processed

--- END OF PROCESS STATUS VALUES AND THERE MEANINGS ----

PROCEDURE invoice_import (pErrBuf OUT VARCHAR2

,pRetCode OUT NUMBER

,pActionType IN VARCHAR2

);

END XX_AP_INV_PKG;

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

CREATE OR REPLACE PACKAGE BODY XX_AP_INV_PKG IS

g_org_id NUMBER :=FND_PROFILE.value('ORG_ID');

--***********************************************************************

-- Procedure debug_log for logging errors..

--***********************************************************************

PROCEDURE DEBUG_LOG (pMessage IN VARCHAR2) IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,pmessage);

END DEBUG_LOG;

--***********************************************************************

-- End Of Procedure debug_log for logging errors..

--***********************************************************************

-- PROCEDURE VALIDATE_INVOICES FOR VALIDATING VALUES

--***********************************************************************

PROCEDURE VALIDATE_INVOICES

--- LOCAL VARIABLE DECLARATION -----------------------

v_Error VARCHAR2(1);

v_ErrMsg VARCHAR2(6000);

v_Invoice_Num NUMBER;

v_Invoice_Type_Lookup_Code VARCHAR2(80);

v_Vendor_num NUMBER;

v_Vendor_Site_Code VARCHAR2(30);

v_Invoice_Currency_Code VARCHAR2(15);

v_Terms_id VARCHAR2(40);

v_Pay_group_lookup_code VARCHAR2(40);

v_Payment_Method_Lookup_Code VARCHAR2(40);

v_Source_from VARCHAR2(80);

v_Po_Number VARCHAR2(20);

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

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

--***********************************************************************

-- DECLARATION OF 'INVOICES' CURSOR ON RAIN_POMS_AP_UNMINVC..

--***********************************************************************

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

CURSOR Cur_invoice IS

SELECT rowid,

XX_AP_INV_STAGE.*

FROM XX_AP_INV_STAGE

WHERE validation_record_flag <>'P'

ORDER BY invoice_id;

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

--***********************************************************************

-- END OF CURSOR DECLARATION 'Cur_invoice_hdr'

--***********************************************************************

BEGIN

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

--***********************************************************************

--For each selected invoice header from the STAGEing area.

--***********************************************************************

FOR Rec_invoice IN Cur_invoice

LOOP

v_Error := 'N';

v_ErrMsg:=NULL;

v_Invoice_Num:= 0;

v_Invoice_Type_Lookup_Code :=NULL;

v_Vendor_num :=NULL;

v_Vendor_Site_Code:=NULL;

v_Invoice_Currency_Code:=NULL;

v_Terms_id:=NULL;

v_Pay_group_lookup_code :=NULL;

v_Payment_Method_Lookup_Code :=NULL;

v_Source_from:=NULL;

v_Po_Number:=NULL;

--***********************************************************************

--Validate the invoice num in invoice header table*/

--***********************************************************************

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

IF Rec_invoice.Invoice_Num IS NOT NULL THEN

BEGIN

SELECT count(1)

INTO v_Invoice_Num

FROM AP_INVOICES_ALL

WHERE TRIM(Invoice_Num)=TRIM(Rec_invoice.Invoice_Num)

AND ORG_ID = g_org_id ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

NULL;

WHEN TOO_MANY_ROWS THEN

debug_log(Rec_invoice.Invoice_Num||' - '||'There are more than one invoice with this Invoice Num ');

END;

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

IF v_Invoice_Num <> 0 THEN

v_Error := 'Y';

v_ErrMsg:=Rec_invoice.Invoice_Num||' - '||'Invoice_Num is already exist not valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Invoice_Num||' - '||'Invoice_Num is already exist not valid');

END IF;

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

--***********************************************************************

--Validate the invoice_type_lookup_code within invoice header

--***********************************************************************

IF Rec_invoice.Invoice_Type_Lookup_Code IS NOT NULL THEN

BEGIN

SELECT lookup_code

INTO v_Invoice_Type_Lookup_Code

FROM AP_LOOKUP_CODES

WHERE lookup_type = 'INVOICE TYPE'

AND lookup_code IN ('STANDARD','CREDIT')

AND enabled_flag='Y'

AND nvl(trunc(inactive_date),sysdate)>= trunc(sysdate)

AND TRIM(lookup_code)= UPPER(TRIM(Rec_invoice.Invoice_Type_Lookup_Code));

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Invoice_Type_Lookup_Code ||' - '||'Invoice Type Lookup code not valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Invoice_Type_Lookup_Code||' - '||'Invoice Type Lookup code not valid');

END;

END IF;

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

--***********************************************************************

--Validate the vendor name in invoice header table

----***********************************************************************

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

IF Rec_invoice.Vendor_Number IS NOT NULL THEN

BEGIN

SELECT SEGMENT1

INTO v_Vendor_num

FROM PO_VENDORS

WHERE TRIM(SEGMENT1)=TRIM(Rec_invoice.Vendor_Number)

AND ENABLED_FLAG ='Y';

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Vendor_Number||' - '||'Vendor Number is not valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Vendor_Number||' - '||'Vendor number is not valid');

END;

END IF;

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

--***********************************************************************

--Validate the vendor site code in invoice header table

--***********************************************************************

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

IF Rec_invoice.Vendor_Site_Code IS NOT NULL THEN

BEGIN

SELECT PVS.vendor_site_code

INTO v_Vendor_Site_Code

FROM po_vendor_sites_all PVS,

po_vendors PV

WHERE PVS.pay_site_flag ='Y'

AND PV.Vendor_id =PVS.VENDOR_ID

AND PVS.org_id =g_org_id

AND TRIM(PVS.vendor_site_code) = TRIM(Rec_invoice.Vendor_Site_Code);

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Vendor_Site_Code||' - '||'Vendor site code is not valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Vendor_Site_Code||' - '||'Vendor site code is not valid');

END;

END IF;

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

--***********************************************************************

--Validate the Invoice Currency Code in invoice header table

--***********************************************************************

IF Rec_invoice.Invoice_Currency_Code IS NOT NULL THEN

BEGIN

SELECT currency_code

INTO v_Invoice_Currency_Code

FROM FND_CURRENCIES

WHERE enabled_flag='Y'

AND UPPER(TRIM(currency_code)) = UPPER(TRIM(Rec_invoice.Invoice_Currency_Code));

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Invoice_Currency_Code||' - '||'Invoice Currency Code is not valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Invoice_Currency_Code||' - '||'Invoice Currency Code is not valid');

END;

END IF;

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

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

--***********************************************************************

--Validate the Payment Terms in invoice header table

--***********************************************************************

IF Rec_invoice.Terms_name IS NOT NULL then

BEGIN

SELECT Term_id

INTO v_Terms_id

FROM AP_TERMS

WHERE UPPER(TRIM(name))= UPPER(TRIM(Rec_invoice.Terms_name))

AND enabled_flag = 'Y'

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

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Terms_name||' - '||'Invalid Payment terms';

v_ErrMsg:=v_ErrMsg||' ; ';

END;

END IF;

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

/*--***********************************************************************

--Validate the Pay Group Lookup Code in invoice header table

--***********************************************************************

IF Rec_invoice.Pay_Group_Lookup_Code IS NOT NULL then

BEGIN

SELECT lookup_code

INTO v_Pay_group_lookup_code

FROM FND_LOOKUP_VALUES

WHERE lookup_type = 'PAY GROUP'

AND UPPER(lookup_code)= UPPER(Rec_invoice.Pay_Group_Lookup_Code)

AND enabled_flag = 'Y'

AND language = 'US'

AND trunc(nvl(end_date_active,sysdate)) >= trunc(sysdate);

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Pay_Group_Lookup_Code||' - '||'Invalid Pay Group Lookup Code ';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Pay_Group_Lookup_Code||' - '||'Invalid Pay Group Lookup Code ');

END;

END IF;*/

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

--***********************************************************************

--Validate the Payment Method Lookup_Code in invoice header table

--***********************************************************************

IF Rec_invoice.Payment_Method_Lookup_Code IS NOT NULL THEN

BEGIN

SELECT distinct lookup_code

INTO v_Payment_Method_Lookup_Code

FROM AP_LOOKUP_CODES

WHERE lookup_type = 'PAYMENT METHOD'

AND enabled_flag='Y'

AND nvl(trunc(inactive_date),sysdate)>= trunc(sysdate)

AND UPPER(TRIM(lookup_code))=UPPER(TRIM(Rec_invoice.Payment_Method_Lookup_Code));

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Payment_Method_Lookup_Code||' - '||'PAYMENT_METHOD_LOOKUP_CODE is not Valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Payment_Method_Lookup_Code||' - '||'PAYMENT_METHOD_LOOKUP_CODE is not Valid');

END;

END IF;

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

--***********************************************************************

--Validate the SOURCE in invoice header table

--***********************************************************************

IF Rec_invoice.Invoice_Source IS NOT NULL THEN

BEGIN

SELECT lookup_code

INTO v_Source_from

FROM AP_LOOKUP_CODES

WHERE lookup_type = 'SOURCE'

-- AND enabled_flag='Y'

-- AND nvl(trunc(inactive_date),sysdate)>= trunc(sysdate)

AND UPPER(TRIM(lookup_code))=UPPER(TRIM(Rec_invoice.Invoice_Source));

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_Error := 'Y';

v_ErrMsg:=v_ErrMsg||Rec_invoice.Invoice_Source||' - '||'SOURCE is not Valid';

v_ErrMsg:=v_ErrMsg||' ; ';

debug_log(Rec_invoice.Invoice_Source||' - '||'SOURCE is not Valid');

END;

END IF;

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

-- Changing the STAGEing table flag

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

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

IF v_Error = 'N' THEN

BEGIN

UPDATE B2_AP_INV_STAGE

SET Validation_Record_Flag ='V'

,Error_Message = NULL

WHERE rowid = Rec_invoice.rowid;

EXCEPTION

WHEN OTHERS THEN

DEBUG_LOG('Error Occured During Updation of Valida Records in Update in GANDAMA_POMS_AP_UNMINVC');

END;

END IF;

IF v_Error = 'Y' THEN

BEGIN

UPDATE B2_AP_INV_STAGE

SET Validation_Record_Flag ='E'

,Error_Message = v_ErrMsg

WHERE rowid = Rec_invoice.rowid;

EXCEPTION

WHEN OTHERS THEN

DEBUG_LOG('Error Occured During Updation of Error Records in GANDAMA_POMS_AP_UNMINVC');

END;

END IF;

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

--********************** END OF FALSE AND TRUE CASES **********************

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

END LOOP;

END VALIDATE_INVOICES;

--***********************************************************************

-- END OF VALIDATE VALUES FOR ALL TABLES..

--***********************************************************************

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

--***********************************************************************

-- PROCEDURE INSERT_TRANSACTION FOR VALIDATING VALUES

--***********************************************************************

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

PROCEDURE INSERT_TRANSACTION IS

v_Errorinvoice BOOLEAN;

v_Errorlines BOOLEAN;

v_ErrMsg VARCHAR2(4000);

v_Exchange_rate_type VARCHAR2(10);

v_Description VARCHAR2(350);

v_Line_id NUMBER;

v_Header_id NUMBER;

v_Line_type_code VARCHAR2(5);

v_Line_number NUMBER;

v_Amount NUMBER;

v_Tax_code VARCHAR2(10);

--***********************************************************************

-- DECLARATION OF 'Cur_invoice_ins' CURSOR ON B21_AP_INV_STAGE

--***********************************************************************

CURSOR Cur_invoice_ins IS

SELECT rowid,

B2_AP_INV_STAGE.*

FROM B2_AP_INV_STAGE

WHERE Validation_Record_Flag ='V'

ORDER BY Invoice_Id;

--***********************************************************************

-- END OF CURSOR DECLARATION 'Cur_invoice_ins'

--***********************************************************************

BEGIN

FOR Rec_invoice_ins IN Cur_invoice_ins

LOOP

v_Errorinvoice := FALSE;

v_ErrMsg:=NULL;

v_Exchange_rate_type :=NULL;

v_Description:=NULL;

v_Header_id :=NULL;

BEGIN

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

-- Generating Header Interface id

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

BEGIN

SELECT B2_AP_INV_HEAID_S.NEXTVAL

INTO v_Header_id

FROM DUAL;

END;

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

-- Deriving the exchange rate

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

BEGIN

IF TRIM(UPPER(Rec_invoice_ins.Invoice_Currency_Code)) <>'CNY'THEN

v_Exchange_rate_type := 'User';

END IF;

END;

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

-- Populating the description

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

BEGIN

v_Description:=Rec_invoice_ins.Description||'-'||Rec_invoice_ins.Po_Number;

END;

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

-- Insert invoice header in to interface table

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

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

INSERT INTO AP_INVOICES_INTERFACE

(

INVOICE_ID ,

INVOICE_TYPE_LOOKUP_CODE ,

VENDOR_NUM ,

VENDOR_SITE_CODE ,

INVOICE_NUM ,

INVOICE_DATE ,

INVOICE_CURRENCY_CODE ,

EXCHANGE_RATE ,

EXCHANGE_RATE_TYPE ,

INVOICE_AMOUNT ,

DESCRIPTION ,

INVOICE_RECEIVED_DATE ,

CREATION_DATE ,

TERMS_NAME ,

TERMS_DATE ,

PAY_GROUP_LOOKUP_CODE ,

PAYMENT_CURRENCY_CODE ,

PAYMENT_METHOD_LOOKUP_CODE ,

GL_DATE ,

SOURCE ,

CREATED_BY ,

LAST_UPDATED_BY ,

LAST_UPDATE_DATE ,

LAST_UPDATE_LOGIN

)

VALUES

(

v_Header_id ,

UPPER(Rec_invoice_ins.Invoice_Type_Lookup_Code) ,

TRIM(Rec_invoice_ins.Vendor_Number) ,

Rec_invoice_ins.Vendor_Site_Code ,

Rec_invoice_ins.Invoice_Num ,

Rec_invoice_ins.Invoice_Date ,

Rec_invoice_ins.Invoice_Currency_Code ,

Rec_invoice_ins.Exchange_Rate ,

v_Exchange_rate_type ,

Rec_invoice_ins.Invoice_Amount ,

v_Description ,

Rec_invoice_ins.Inv_Recieved_Date ,

Rec_invoice_ins.Created_Date ,

Rec_invoice_ins.Terms_name ,

Rec_invoice_ins.Terms_Date ,

Rec_invoice_ins.Pay_Group_Lookup_Code ,

Rec_invoice_ins.Payment_Currency_Code ,

UPPER(Rec_invoice_ins.Payment_Method_Lookup_Code),

Rec_invoice_ins.GL_Date ,

Rec_invoice_ins.Invoice_Source ,

Rec_invoice_ins.Created_by ,

Rec_invoice_ins.Last_Updated_by ,

Rec_invoice_ins.Last_Update_Date ,

Rec_invoice_ins.Last_Update_Login

);

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

v_Errorinvoice := FALSE;

DEBUG_LOG('Header Interface Table got Inserted sucessfully');

EXCEPTION

WHEN OTHERS THEN

v_Errorinvoice := TRUE;

v_ErrMsg :='AFTER INSERT INTO AP INVOICES INTERFACE FAILED IN EXCEPTION'||SUBSTR (SQLERRM,1,150);

DEBUG_LOG(v_ErrMsg);

END;

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

-- Changing the STAGEing table flag

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

IF v_Errorinvoice = TRUE THEN

BEGIN

UPDATE B2_AP_INV_STAGE

SET Validation_Record_Flag = 'E'

,Error_Message = v_ErrMsg

WHERE

ROWID = Rec_invoice_ins.ROWID;

EXCEPTION

WHEN OTHERS THEN

DEBUG_LOG('AFTER UPDATATION OF ERROR RECORDS FAILED IN EXCEPTION'||SUBSTR (SQLERRM,1,150));

END;

END IF;

--********************************************************************************

-- Inserting values from STAGEing table B21_AP_INV_STAGE to AP_INVOICE_LINES_INTERFACE

--********************************************************************************

IF v_Errorinvoice = FALSE THEN

-- intializing the variables

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

v_ErrMsg := NULL;

v_Errorlines := FALSE;

v_Line_id := NULL;

v_Line_type_code:= 'TAX';

v_Line_number := 1 ;

v_Amount := 0;

--v_Tax_code := 'VAT15';

BEGIN

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

-- Genarating lines interface id

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

BEGIN

SELECT B2_AP_INV_LINES_S.NEXTVAL

INTO v_Line_id

FROM DUAL;

END;

INSERT INTO AP_INVOICE_LINES_INTERFACE

(

INVOICE_ID

,INVOICE_LINE_ID

,LINE_TYPE_LOOKUP_CODE

,LINE_NUMBER

--,ACCOUNTING_DATE

--,DIST_CODE_COMBINATION_ID

,AMOUNT

--,TAX_RECOVERABLE_FLAG

--,DESCRIPTION

,TAX_CODE

--,TAX_RECOVERY_RATE

,CREATED_BY

,CREATION_DATE

,LAST_UPDATED_BY

,Last_UPDATE_DATE

,Last_UPDATE_LOGIN

)

VALUES

(

v_Header_id

,v_Line_id

,v_Line_type_code

,v_Line_number

,REC_INVOICE_INS.TAX_Amount

,REC_INVOICE_INS.Tax_code

,Rec_invoice_ins.Created_by

,Rec_invoice_ins.Creation_date

,Rec_invoice_ins.Last_Updated_by

,Rec_invoice_ins.Last_Update_Date

,Rec_invoice_ins.Last_Update_Login

);

v_Errorlines := FALSE;

DEBUG_LOG('Lines Interface Table got Inserted sucessfully');

EXCEPTION

WHEN OTHERS THEN

v_Errorlines := TRUE;

v_ErrMsg:='AFTER INSERT INTO AP INVOICE LINES INTERFACE FAILED IN EXCEPTION'||SUBSTR (SQLERRM,1,150);

DEBUG_LOG(v_ErrMsg);

END;

IF v_Errorlines = TRUE THEN

BEGIN

UPDATE B2_AP_INV_STAGE

SET Validation_Record_Flag = 'E'

,Error_Message = v_ErrMsg

WHERE

ROWID = Rec_invoice_ins.ROWID;

EXCEPTION

WHEN OTHERS THEN

DEBUG_LOG('AFTER UPDATATION OF ERROR RECORDS INTO RAIN_POMS_AP_UNMINVC FAILED IN EXCEPTION'||SUBSTR (SQLERRM,1,150));

END;

END IF;

IF v_Errorlines = FALSE THEN

BEGIN

UPDATE B2_AP_INV_STAGE

SET Validation_Record_Flag = 'P'

,Error_Message = NULL

WHERE

ROWID = Rec_invoice_ins.ROWID;

EXCEPTION

WHEN OTHERS THEN

DEBUG_LOG('AFTER UPDATATION OF VALID RECORDS INTO RAIN_POMS_AP_UNMINVC FAILED IN EXCEPTION'||SUBSTR (SQLERRM,1,150));

END;

END IF;

END LOOP;

COMMIT;

END INSERT_TRANSACTION;

--***********************************************************************

-- END OF PROCEDURE INSERT_TRANSACTION

--***********************************************************************

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

--***********************************************************************

-- BEGIN OF PROCEDURE INVOICE IMPORT

--***********************************************************************

PROCEDURE invoice_import(pErrBuf OUT VARCHAR2

,pRetCode OUT NUMBER

,pActionType IN VARCHAR2

) IS

vActionType VARCHAR2(11);

BEGIN

vActionType := pActionType;

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

--*********** RUN MODE IS VALIDATION ONLY START ***************

IF vActionType = 'VALIDATE' THEN

DEBUG_LOG('Start Validation Process');

VALIDATE_INVOICES;

COMMIT;

DEBUG_LOG('End of Validation Process');

END IF;

--*********** RUN MODE IS VALIDATION ONLY END ***************

--*********** RUN MODE IS VALIDATION AND INSERTION START ***************

IF vActionType = 'INSERT' THEN

DEBUG_LOG('Start Validation Process');

VALIDATE_INVOICES;

COMMIT;

INSERT_TRANSACTION;

COMMIT;

DEBUG_LOG('End of Insertion Process');

COMMIT;

END IF;

--*********** RUN MODE IS VALIDATION AND INSERTION END ***************

--*********** End of Calling Data Reconciliation Concurrent program ***************

END invoice_import;

--***********************************************************************

-- END OF PROCEDURE INVOICE IMPORT

--***********************************************************************

END XX_AP_INV_PKG ;

Santoshkumaraddar's Oracle Financial Blog

Wednesday, 14 September 2016

Account’s Payables Invoices interface and Loader Process Details