AR Invoice Conversion:
In the implementation project customer invoices migration is a challenge. Without having proper understanding of AR setups, migrating AR invoice is little tough. Doing the following setups invoices with multiple lines can also be processed.
The following set ups are required to import AR Invoices and few set ups are mandatory.
1.Create a DFF in the descriptive Segments 'Line Transaction Flexfield',
set context as 'XX OPENINVOICE' ,also select INTERFACE_LINE_ATTRIBUTE1 for passing the reference value.
Pass the 'XX OPENINVOICE' value in the INTERFACE_LINE_CONTEXT and reference numbers
in the INTERFACE_LINE_ATTRIBUTE1 on RA_INTERFACE_LINES_ALL table.This is mandatory because grouping rules.
2.Create a Source(AR->SETUP>TRANSACTIONS->SOURCES), 'OPENING INVOICES' ,
type should be 'IMPORTED'and uncheck auto transaction numbering.
3.Create a Transaction Types(AR->SETUP>TRANSACTIONS->TRANSACION TYPES), 'OPEN INVOICE''
attach receivable and revenue account. Then attch this Transaction types in the Source 'OPENING INVOICES'.
If rec and rev account pass in the RA_INTERFACE_DISTRIBUTIONS_ALL all table then no need to attach the
receivable and revenue account in the transaction type. Passing the values in RA_INTERFACE_DISTRIBUTIONS_ALL account information replace the any auto account set up.
4. In the Transaction type attach grouping rule 'DEFAULT'.
In the Transaction type goto tab customer information
sold to customer- ID
BILL to customer - ID
address to customer - ID ,
goto account info tab accounting flexfiled- ID
PAYMENT TERMS -ID
5. No INTERFACE_LINE_ATTRIBUTE(i) should be attached to the grouping rule.
Table :::
CREATE TABLE XX_REC_INV_TBL
(
OPERATING_UNIT VARCHAR2(240 BYTE),
CUSTOMER_CODE VARCHAR2(2000 BYTE),
INVOICE_DATE DATE,
INVOICE_NUMBER VARCHAR2(50 BYTE),
GL_DATE DATE,
INV_CLASS VARCHAR2(20 BYTE),
CURRENCY VARCHAR2(3 BYTE),
INV_TYPE VARCHAR2(240 BYTE),
RATE NUMBER,
PAYMENT_TERMS VARCHAR2(240 BYTE),
LINE_DESCRIPTION VARCHAR2(240 BYTE),
PRICE NUMBER,
UNIT_OF_MEASURE VARCHAR2(100 BYTE),
QTY NUMBER,
REC_ACCOUNT VARCHAR2(240 BYTE),
REV_ACCOUNT VARCHAR2(240 BYTE),
PROCESS VARCHAR2(1 BYTE) DEFAULT 'N',
ERROR_DESCRIPTION VARCHAR2(4000 BYTE),
CUSTOMER_ID NUMBER,
SITE_ID NUMBER,
ORG_ID NUMBER,
REC_ID NUMBER,
REV_ID NUMBER,
TERM_ID NUMBER,
SITE_CODE VARCHAR2(100 BYTE),
UNIT_PRICE NUMBER,
VAT_CODE VARCHAR2(100 BYTE)
)
*** I populates all the ID in my validation program.
PROCEDURE interface_table (pi_operating_unit IN VARCHAR2)
IS
CURSOR c1
IS
SELECT ROWID rowxx, a.org_id, invoice_number, invoice_date,
TO_DATE (gl_date, 'DD-MON-RRRR') gl_date, currency, customer_id,
site_id, line_description, price, uom_code, qty, rec_id,
rev_id, payment_terms, term_id, inv_type, rate, vat_code
FROM xx_rec_inv_tbl a
WHERE a.process = 'V' AND operating_unit = pi_operating_unit;
p_error NUMBER;
p_interface_line_context VARCHAR2 (100) := 'XX OPENINVOICE';
p_interface_line_attribute8 VARCHAR2 (100);
p_interface_line_attribute9 VARCHAR2 (100);
p_party_id NUMBER;
p_cust_account_id NUMBER;
p_cust_account_ref VARCHAR2 (240);
p_bill_to_address VARCHAR2 (240);
p_ship_to_address VARCHAR2 (200);
p_bill_to_address_id NUMBER;
p_ship_to_address_id NUMBER;
p_item_id NUMBER;
p_uom_code VARCHAR2 (150);
p_unit_of_measure VARCHAR2 (150);
v_seq NUMBER;
p_party_name VARCHAR2 (240);
p_message1 VARCHAR2 (2000);
p_message VARCHAR2 (2000);
v_rec_ccid NUMBER;
v_rev_ccid NUMBER;
p_org_id NUMBER;
v_interface_line_id NUMBER;
--------------------------------------------------------------
l_trx_number VARCHAR2 (20) := 0;
l_customer_number VARCHAR2 (20) := 0;
l_retention_amount NUMBER;
l_advance_amount NUMBER;
l_line_no NUMBER;
v_uom_code VARCHAR2 (3);
v_line_number NUMBER;
v_trx_type_id NUMBER;
BEGIN
FOR c1_rec IN c1
LOOP
p_error := 0;
v_trx_type_id := NULL;
BEGIN
SELECT ra_customer_trx_lines_s.NEXTVAL
INTO v_interface_line_id
FROM DUAL;
SELECT cust_trx_type_id
INTO v_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = c1_rec.inv_type AND org_id = c1_rec.org_id;
INSERT INTO ra_interface_lines_all
(interface_line_id, description,
interface_line_context,
interface_line_attribute1,
trx_number, batch_source_name,
line_type, currency_code,
amount, cust_trx_type_id,
--term_name,
orig_system_bill_customer_id,
orig_system_bill_customer_ref,
orig_system_bill_address_id,
orig_system_bill_address_ref, trx_date, gl_date,
uom_code, line_number, comments, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login, org_id, conversion_type,
conversion_rate, tax_code, term_id, quantity,
unit_selling_price
)
VALUES (v_interface_line_id, c1_rec.line_description,
p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
TRIM (c1_rec.invoice_number), 'OPENING INVOICES',
'LINE', c1_rec.currency,
ROUND (c1_rec.qty * c1_rec.price, 2), v_trx_type_id,
--1,
--c1_rec.payment_terms,
c1_rec.customer_id,
NULL,
c1_rec.site_id,
c1_rec.site_id, c1_rec.invoice_date, c1_rec.gl_date,
c1_rec.uom_code, 1, NULL, 0,
c1_rec.invoice_date, 0, c1_rec.invoice_date,
-1, c1_rec.org_id, 'User',
NVL (c1_rec.rate, 1), NULL, -- 'ZERO RATE',
c1_rec.term_id, c1_rec.qty,
c1_rec.price
);
INSERT INTO ra_interface_distributions_all
(interface_line_id, interface_line_context,
interface_line_attribute1,
account_class, PERCENT, org_id, comments,
code_combination_id
)
VALUES (v_interface_line_id, p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
'REC', 100, c1_rec.org_id, 'Debtors Receivables',
c1_rec.rec_id
);
INSERT INTO ra_interface_distributions_all
(interface_line_id, interface_line_context,
interface_line_attribute1,
account_class, PERCENT, org_id, comments,
code_combination_id
)
VALUES (v_interface_line_id, p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
'REV', 100, c1_rec.org_id, 'Debtors Revenue',
c1_rec.rev_id
);
COMMIT;
END;
END LOOP;
END interface_table;
6. Do no select transaction type during auto invoice import, credit memo will also be imported automatically
** For a same invoice for multiple lines payment terms can not be different.
** For credit memo , payment terms can not be passed
Run the Auto Invoice Master Program which will call the Auto Invoice Import and after both request
successful completion it will load the data. While running the Auto Invoice Master Program select above defined source.
source and transaction types.
In the implementation project customer invoices migration is a challenge. Without having proper understanding of AR setups, migrating AR invoice is little tough. Doing the following setups invoices with multiple lines can also be processed.
The following set ups are required to import AR Invoices and few set ups are mandatory.
1.Create a DFF in the descriptive Segments 'Line Transaction Flexfield',
set context as 'XX OPENINVOICE' ,also select INTERFACE_LINE_ATTRIBUTE1 for passing the reference value.
Pass the 'XX OPENINVOICE' value in the INTERFACE_LINE_CONTEXT and reference numbers
in the INTERFACE_LINE_ATTRIBUTE1 on RA_INTERFACE_LINES_ALL table.This is mandatory because grouping rules.
2.Create a Source(AR->SETUP>TRANSACTIONS->SOURCES), 'OPENING INVOICES' ,
type should be 'IMPORTED'and uncheck auto transaction numbering.
3.Create a Transaction Types(AR->SETUP>TRANSACTIONS->TRANSACION TYPES), 'OPEN INVOICE''
attach receivable and revenue account. Then attch this Transaction types in the Source 'OPENING INVOICES'.
If rec and rev account pass in the RA_INTERFACE_DISTRIBUTIONS_ALL all table then no need to attach the
receivable and revenue account in the transaction type. Passing the values in RA_INTERFACE_DISTRIBUTIONS_ALL account information replace the any auto account set up.
4. In the Transaction type attach grouping rule 'DEFAULT'.
In the Transaction type goto tab customer information
sold to customer- ID
BILL to customer - ID
address to customer - ID ,
goto account info tab accounting flexfiled- ID
PAYMENT TERMS -ID
5. No INTERFACE_LINE_ATTRIBUTE(i) should be attached to the grouping rule.
Table :::
CREATE TABLE XX_REC_INV_TBL
(
OPERATING_UNIT VARCHAR2(240 BYTE),
CUSTOMER_CODE VARCHAR2(2000 BYTE),
INVOICE_DATE DATE,
INVOICE_NUMBER VARCHAR2(50 BYTE),
GL_DATE DATE,
INV_CLASS VARCHAR2(20 BYTE),
CURRENCY VARCHAR2(3 BYTE),
INV_TYPE VARCHAR2(240 BYTE),
RATE NUMBER,
PAYMENT_TERMS VARCHAR2(240 BYTE),
LINE_DESCRIPTION VARCHAR2(240 BYTE),
PRICE NUMBER,
UNIT_OF_MEASURE VARCHAR2(100 BYTE),
QTY NUMBER,
REC_ACCOUNT VARCHAR2(240 BYTE),
REV_ACCOUNT VARCHAR2(240 BYTE),
PROCESS VARCHAR2(1 BYTE) DEFAULT 'N',
ERROR_DESCRIPTION VARCHAR2(4000 BYTE),
CUSTOMER_ID NUMBER,
SITE_ID NUMBER,
ORG_ID NUMBER,
REC_ID NUMBER,
REV_ID NUMBER,
TERM_ID NUMBER,
SITE_CODE VARCHAR2(100 BYTE),
UNIT_PRICE NUMBER,
VAT_CODE VARCHAR2(100 BYTE)
)
*** I populates all the ID in my validation program.
PROCEDURE interface_table (pi_operating_unit IN VARCHAR2)
IS
CURSOR c1
IS
SELECT ROWID rowxx, a.org_id, invoice_number, invoice_date,
TO_DATE (gl_date, 'DD-MON-RRRR') gl_date, currency, customer_id,
site_id, line_description, price, uom_code, qty, rec_id,
rev_id, payment_terms, term_id, inv_type, rate, vat_code
FROM xx_rec_inv_tbl a
WHERE a.process = 'V' AND operating_unit = pi_operating_unit;
p_error NUMBER;
p_interface_line_context VARCHAR2 (100) := 'XX OPENINVOICE';
p_interface_line_attribute8 VARCHAR2 (100);
p_interface_line_attribute9 VARCHAR2 (100);
p_party_id NUMBER;
p_cust_account_id NUMBER;
p_cust_account_ref VARCHAR2 (240);
p_bill_to_address VARCHAR2 (240);
p_ship_to_address VARCHAR2 (200);
p_bill_to_address_id NUMBER;
p_ship_to_address_id NUMBER;
p_item_id NUMBER;
p_uom_code VARCHAR2 (150);
p_unit_of_measure VARCHAR2 (150);
v_seq NUMBER;
p_party_name VARCHAR2 (240);
p_message1 VARCHAR2 (2000);
p_message VARCHAR2 (2000);
v_rec_ccid NUMBER;
v_rev_ccid NUMBER;
p_org_id NUMBER;
v_interface_line_id NUMBER;
--------------------------------------------------------------
l_trx_number VARCHAR2 (20) := 0;
l_customer_number VARCHAR2 (20) := 0;
l_retention_amount NUMBER;
l_advance_amount NUMBER;
l_line_no NUMBER;
v_uom_code VARCHAR2 (3);
v_line_number NUMBER;
v_trx_type_id NUMBER;
BEGIN
FOR c1_rec IN c1
LOOP
p_error := 0;
v_trx_type_id := NULL;
BEGIN
SELECT ra_customer_trx_lines_s.NEXTVAL
INTO v_interface_line_id
FROM DUAL;
SELECT cust_trx_type_id
INTO v_trx_type_id
FROM ra_cust_trx_types_all
WHERE NAME = c1_rec.inv_type AND org_id = c1_rec.org_id;
INSERT INTO ra_interface_lines_all
(interface_line_id, description,
interface_line_context,
interface_line_attribute1,
trx_number, batch_source_name,
line_type, currency_code,
amount, cust_trx_type_id,
--term_name,
orig_system_bill_customer_id,
orig_system_bill_customer_ref,
orig_system_bill_address_id,
orig_system_bill_address_ref, trx_date, gl_date,
uom_code, line_number, comments, created_by,
creation_date, last_updated_by, last_update_date,
last_update_login, org_id, conversion_type,
conversion_rate, tax_code, term_id, quantity,
unit_selling_price
)
VALUES (v_interface_line_id, c1_rec.line_description,
p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
TRIM (c1_rec.invoice_number), 'OPENING INVOICES',
'LINE', c1_rec.currency,
ROUND (c1_rec.qty * c1_rec.price, 2), v_trx_type_id,
--1,
--c1_rec.payment_terms,
c1_rec.customer_id,
NULL,
c1_rec.site_id,
c1_rec.site_id, c1_rec.invoice_date, c1_rec.gl_date,
c1_rec.uom_code, 1, NULL, 0,
c1_rec.invoice_date, 0, c1_rec.invoice_date,
-1, c1_rec.org_id, 'User',
NVL (c1_rec.rate, 1), NULL, -- 'ZERO RATE',
c1_rec.term_id, c1_rec.qty,
c1_rec.price
);
INSERT INTO ra_interface_distributions_all
(interface_line_id, interface_line_context,
interface_line_attribute1,
account_class, PERCENT, org_id, comments,
code_combination_id
)
VALUES (v_interface_line_id, p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
'REC', 100, c1_rec.org_id, 'Debtors Receivables',
c1_rec.rec_id
);
INSERT INTO ra_interface_distributions_all
(interface_line_id, interface_line_context,
interface_line_attribute1,
account_class, PERCENT, org_id, comments,
code_combination_id
)
VALUES (v_interface_line_id, p_interface_line_context,
TRIM (c1_rec.invoice_number) || '-'
|| v_interface_line_id,
'REV', 100, c1_rec.org_id, 'Debtors Revenue',
c1_rec.rev_id
);
COMMIT;
END;
END LOOP;
END interface_table;
6. Do no select transaction type during auto invoice import, credit memo will also be imported automatically
** For a same invoice for multiple lines payment terms can not be different.
** For credit memo , payment terms can not be passed
Run the Auto Invoice Master Program which will call the Auto Invoice Import and after both request
successful completion it will load the data. While running the Auto Invoice Master Program select above defined source.
source and transaction types.
This comment has been removed by the author.
ReplyDelete