be happy and make others to be happy: AR Invoice

AR Invoice (Auto Invoice / Customer Invoice / AR Transaction) upload through interface step by step

We can use Auto Invoice tool to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos etc in Oracle Receivables. For this, we need to develop a custom program that will be used to transfer transaction data from an external system into the AutoInvoice interface tables.

There are three interface tables in Oracle Receivables for AutoInvoice:

RA_INTERFACE_LINES
RA_INTERFACE_DISTRIBUTIONS
RA_INTERFACE_SALESCREDITS

Now, if we initiate the AutoInvoice then it will transfer all the transactional data from the above three interface tables into the following Receivables tables:

RA_BATCHES_ALL
RA_CUSTOMER_TRX _ALL
RA_CUSTOMER_TRX_LINES _ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUST_TRX_LINE_SALESREPS_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLE_APPLICATIONS_ALL
AR_ADJUSTMENTS_ALL

AutoInvoice Exception Handling:

Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which isRA_INTERFACE_ERRORS_ALL.

Note: - Tables mainly used are highlighted in Bold

STEPS:-

1. Create the staging table as

CREATE TABLE SMZPL_AR_INVOICE_STG

(

OU_NAME VARCHAR2(250 ),

ORG_ID NUMBER,

TRANSACTION_SOURCE VARCHAR2(250 ),

INVOICE_CLASS VARCHAR2(250 ),

TRANSACTIONS_TYPE VARCHAR2(250 ),

TRX_DATE DATE,

GL_DATE DATE,

CURRENCY VARCHAR2(10 ),

EXCHANGE_RATE NUMBER,

REFERENCE VARCHAR2(250 ), -- Used to store invoice no. in this example

CUSTOMER_NAME VARCHAR2(250 ),

CUSTOMER_SITE VARCHAR2(100 ),

DISTRIBUTION_ACCOUNT VARCHAR2(30 ),

DISTRIBUTION_ACCOUNT_ID NUMBER,

INVOICE_AMOUNT NUMBER(12,2),

LINE_AMOUNT NUMBER(12,2),

TERMS VARCHAR2(250 ),

QUANTITY NUMBER,

LINE_ITEM VARCHAR2(20 ),

INVOICE_NUM VARCHAR2(40 ),

L_VERIFY_FLAG VARCHAR2(2 ) DEFAULT NULL,

L_ERROR_MESSAGE VARCHAR2(30 ) DEFAULT NULL

);

2. Import the data from excel sheet into staging table through TOAD.

3. Create the procedure

CREATE OR REPLACE PROCEDURE APPS.SMZPL_AR_INVOICE_API

L_ORG_ID HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE;

L_SOB_ID HR_OPERATING_UNITS.SET_OF_BOOKS_ID%TYPE;

L_CUST_TRX_TYPE_ID RA_CUST_TRX_TYPES_ALL.CUST_TRX_TYPE_ID%TYPE;

L_GL_ID_REV RA_CUST_TRX_TYPES_ALL.GL_ID_REV%TYPE;

L_CUST_TRX_TYPE_NAME RA_CUST_TRX_TYPES_ALL.NAME%TYPE;

L_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;

L_TERM_ID RA_TERMS_TL.TERM_ID%TYPE;

L_TERM_NAME RA_TERMS_TL.NAME%TYPE;

L_ADDRESS_ID HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;

L_CUSTOMER_ID HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID%TYPE;

L_VERIFY_FLAG CHAR(1):='Y';

L_ERROR_MESSAGE VARCHAR2(2500);

L_GL_CODE_ID VARCHAR2(100);

CURSOR CUR_AR IS

SELECT a.*,a.rowid rd FROM

SMZPL_AR_INVOICE_STG a

WHERE NVL(L_VERIFY_FLAG,'N')='N';

BEGIN

FOR AR_INV IN CUR_AR LOOP

BEGIN

SELECT ORGANIZATION_ID, SET_OF_BOOKS_ID

INTO L_ORG_ID, L_SOB_ID

FROM HR_OPERATING_UNITS

WHERE trim(NAME) = trim(AR_INV.OU_NAME);

EXCEPTION

WHEN OTHERS THEN

L_VERIFY_FLAG := 'N';

L_ERROR_MESSAGE := 'INVALIDE OPERATING UNIT...';

Update SMZPL_AR_INVOICE_STG

Set L_VERIFY_FLAG = 'N',

L_ERROR_MESSAGE = 'INVALIDE OPERATING UNIT...'

Where rowid = AR_INV.rd;

END;

BEGIN

SELECT CUST_TRX_TYPE_ID,NAME,GL_ID_REV

INTO L_CUST_TRX_TYPE_ID,L_CUST_TRX_TYPE_NAME, L_GL_ID_REV

FROM RA_CUST_TRX_TYPES_ALL

WHERE SET_OF_BOOKS_ID = L_SOB_ID

AND ORG_ID = L_ORG_ID

AND trim(NAME) =trim(AR_INV.TRANSACTIONS_TYPE);

EXCEPTION

WHEN OTHERS THEN

L_VERIFY_FLAG := 'N';

L_ERROR_MESSAGE := 'INVALIDE INVOICE TYPE...';

Update SMZPL_AR_INVOICE_STG

Set L_VERIFY_FLAG = 'N',

L_ERROR_MESSAGE = 'INVALIDE INVOICE TYPE...'

Where rowid = AR_INV.rd;

END;

BEGIN

SELECT CURRENCY_CODE

INTO L_CURRENCY_CODE

FROM FND_CURRENCIES

WHERE trim(CURRENCY_CODE) =trim(AR_INV.CURRENCY);

EXCEPTION

WHEN OTHERS THEN

L_VERIFY_FLAG := 'N';

L_ERROR_MESSAGE := 'INVALIDE CURRENCY CODE...';

Update SMZPL_AR_INVOICE_STG

Set L_VERIFY_FLAG = 'N',

L_ERROR_MESSAGE = 'INVALIDE CURRENCY CODE...'

Where rowid = AR_INV.rd;

END;

BEGIN

SELECT TERM_ID,NAME

INTO L_TERM_ID,L_TERM_NAME

FROM RA_TERMS_TL

WHERE UPPER(NAME) = UPPER(AR_INV.TERMS);

EXCEPTION

WHEN OTHERS THEN

L_VERIFY_FLAG := 'N';

L_ERROR_MESSAGE := 'INVALIDE TERMS NAME...';

Update SMZPL_AR_INVOICE_STG

Set L_VERIFY_FLAG = 'N',

L_ERROR_MESSAGE = 'INVALIDE TERMS NAME...'

Where rowid = AR_INV.rd;

END;

BEGIN

SELECT DISTINCTHCAS.CUST_ACCT_SITE_ID,HCA.CUST_ACCOUNT_ID--HPS.LOCATION_ID,HP.PARTY_ID

INTO L_ADDRESS_ID,L_CUSTOMER_ID

FROM HZ_PARTIES HP

,HZ_PARTY_SITES HPS

,HZ_CUST_ACCOUNTS HCA

,HZ_CUST_ACCT_SITES_ALL HCAS

,HZ_CUST_SITE_USES_ALL HCSU

,HZ_LOCATIONS HL

WHERE HCA.PARTY_ID = HP.PARTY_ID

AND HP.PARTY_ID = HPS.PARTY_ID

AND HPS.PARTY_SITE_ID=HCAS.PARTY_SITE_ID

AND HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID

AND HCAS.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID

AND HCSU.SITE_USE_CODE = 'BILL_TO'

AND HL.LOCATION_ID =HPS.LOCATION_ID

AND UPPER (LTRIM (RTRIM(HP.PARTY_NAME)))=UPPER(LTRIM(RTRIM(AR_INV.CUSTOMER_NAME)))

AND HCAS.ORG_ID =L_ORG_ID;

EXCEPTION

WHEN OTHERS THEN

L_VERIFY_FLAG := 'N';

L_ERROR_MESSAGE := 'INVALIDE CUSTOMER NAME...';

Update SMZPL_AR_INVOICE_STG

Set L_VERIFY_FLAG = 'N',

L_ERROR_MESSAGE = 'INVALIDE CUSTOMER NAME...'

Where rowid = AR_INV.rd;

END;

/**

BEGIN

SELECT CODE_COMBINATION_ID INTO L_GL_CODE_ID

FROM GL_CODE_COMBINATIONS_KFV

WHERE CONCATENATED_SEGMENTS=AR_INV.DISTRIBUTION_ACCOUNT;

EXCEPTION

WHEN OTHERS THEN

L_VERIFY_FLAG := 'N';

L_ERROR_MESSAGE := 'INVALIDE ACCOUNT ID';

END;

**/

IF L_VERIFY_FLAG<>'N' THEN

BEGIN

INSERT INTO RA_INTERFACE_LINES_ALL(

INTERFACE_LINE_ID,

BATCH_SOURCE_NAME,--ra_batch_sources_all

SET_OF_BOOKS_ID,

LINE_TYPE,

CUST_TRX_TYPE_ID,--1003

CUST_TRX_TYPE_NAME,

TRX_NUMBER,

TRX_DATE,

GL_DATE,

CURRENCY_CODE,

TERM_ID,

--TERM_NAME,

ORIG_SYSTEM_BILL_CUSTOMER_ID,

ORIG_SYSTEM_BILL_CUSTOMER_REF,

ORIG_SYSTEM_BILL_ADDRESS_ID,

ORIG_SYSTEM_BILL_ADDRESS_REF,

ORIG_SYSTEM_SOLD_CUSTOMER_ID,

QUANTITY,

AMOUNT,

DESCRIPTION,

CONVERSION_TYPE,

--CONVERSION_RATE,

INTERFACE_LINE_CONTEXT,

-- INTERFACE_LINE_ATTRIBUTE1,

ORG_ID,

INTERFACE_LINE_ATTRIBUTE1,

INTERFACE_LINE_ATTRIBUTE2,

INTERFACE_LINE_ATTRIBUTE3

)

VALUES

(

RA_CUSTOMER_TRX_LINES_S.NEXTVAL,

'OPENING BALANCE UPLOAD',

L_SOB_ID,

'LINE',

L_CUST_TRX_TYPE_ID,

L_CUST_TRX_TYPE_NAME,

AR_INV.REFERENCE,

AR_INV.GL_DATE,

L_CURRENCY_CODE,

AR_INV.TERMS,

--L_TERM_NAME,

L_CUSTOMER_ID,

L_ADDRESS_ID,

L_CUSTOMER_ID,

AR_INV.INVOICE_AMOUNT,

'AR OPENING DATA',

'Corporate',

--1,

'AFRICA_INVOICES',

L_ORG_ID,

AR_INV.REFERENCE,

1 ,

AR_INV.TRX_DATE

);

INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL

(

INTERFACE_LINE_ID

,ACCOUNT_CLASS

,AMOUNT

,CODE_COMBINATION_ID

,PERCENT

--,INTERFACE_LINE_CONTEXT

--,INTERFACE_LINE_ATTRIBUTE1

,ORG_ID

)

VALUES

(

RA_CUSTOMER_TRX_LINES_S.CURRVAL,

'REV'

,AR_INV.INVOICE_AMOUNT

,L_GL_ID_REV

,100

--,L_GL_ID_REV

-- ,AR_INV.INVOICE_AMOUNT

,L_ORG_ID

);

Update SMZPL_AR_INVOICE_STG

Set L_VERIFY_FLAG = 'Y'

Where rowid = AR_INV.rd;

COMMIT;

END;

END IF;

END LOOP;

END;

4. Some important validations

Ø Check for valid Operating Unit or ORG_ID. [ HR_OPERATING_UNITS ]

Ø Check for valid Transaction Types or Invoice Types. [RA_CUST_TRX_TYPES_ALL ]

Ø Check for valid Transaction Source or BATCH_SOURCE_NAME.[RA_BATCH_SOURCES_ALL ]

Ø Check for valid Currency Code .[ FND_CURRENCIES ]

Ø Check for valid Terms Name [ RA_TERMS_TL ]

Ø Check for valid Customer Name and BILL_TO address. [AR_CUSTOMERS, HZ_PARTIES,HZ_PARTY_SITES, HZ_CUST_ACCOUNTS ,HZ_CUST_ACCT_SITES_ALL,HZ_CUST_SITE_USES_ALL ,HZ_LOCATIONS ]

Ø Transaction Flex Fields at Line level are mandatory which uniquely identifies invoice lines.

Ø Amount at Line level should match with Amount at Distribution level.

Ø Check for valid items ( Item field is not mandatory ) [MTL_SYSTEM_ITEMS_B ]

Ø Check for duplicate value in TRX_NUMBER i.e., two different transactions cannot have same Invoice No.

5. Execute the above procedure to insert data from staging table into Interface tables i.e., from SMZPL_AR_INVOICE_STG intoRA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL.

BEGIN

SMZPL_AR_INVOICE_API;

END;

6. L_VERIFY_FLAG for all the rows which get inserted into interface table will be set to ‘Y’ and L_VERIFY_FLAG will be set to ‘N’ for non processed rows along with the ERROR_MESSAGE.

7. If the records are populated properly into Interface tables then we need to Run the AutoInvoice Import Program.

Navigate to Receivables Responsibility ---> Interface ---> AutoInvoice