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 is RA_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
AS
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 DISTINCT HCAS.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,
AR_INV.GL_DATE,
L_CURRENCY_CODE,
AR_INV.TERMS,
--L_TERM_NAME,
L_CUSTOMER_ID,
L_CUSTOMER_ID,
L_ADDRESS_ID,
L_ADDRESS_ID,
L_CUSTOMER_ID,
1,
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.
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
Thanks you Very Much
ReplyDelete