Thursday, July 17, 2008

AR Invoice Interface

AR INVOICE INTERFACE
************************

Interface Tables are :-
-----------------------
ra_interface_lines_all
ra_interface_distributions_all
ra_interface_errors_all

Concurrent Program to import data from interface tables :-
------------------------------------------------------------
Autoinvoice Import Program

Script to Migrate data from Staging table to Interface Table
------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE xxx_ar_invoice_api (errbuf out varchar2, rectcode out varchar2)
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);
l_error_message varchar2(2500);


BEGIN

BEGIN
SELECT organization_id, SET_OF_BOOKS_ID
INTO l_org_id, l_sob_id
FROM hr_operating_units
WHERE name = 'xxx Operating Unit';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Operating Unit...';

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 name = 'xxx-Spares-Inv';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Invoice Type...';
END;


BEGIN
select currency_code
into l_currency_code
from fnd_currencies
where currency_code = 'INR';
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Currency Code...';
END;


BEGIN
SELECT term_id,name
into l_term_id,l_term_name
FROM ra_terms_tl
WHERE upper(name) = upper('IMMEDIATE');
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Terms Name...';
END;


BEGIN
SELECT DISTINCT HCAS.cust_acct_site_id,HCA.cust_account_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
WHERE HCA.party_id = HP.party_id
AND HP.party_id = HPS.party_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 HCSU.primary_flag = 'Y'
AND upper (ltrim (rtrim (HP.party_name))) = upper (ltrim (rtrim ('Customer Name')))
AND HCAs.org_id = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_verify_flag := 'N';
l_error_message := 'Invalide Customer Name...';
END;


INSERT INTO ra_interface_lines_all(
INTERFACE_LINE_ID,
BATCH_SOURCE_NAME,
LINE_TYPE,
CUST_TRX_TYPE_ID,
cust_trx_type_name,
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_ship_customer_id,
--orig_system_ship_address_id,
orig_system_sold_customer_id,
QUANTITY,
--unit_selling_price
AMOUNT,
DESCRIPTION,
conversion_type,
conversion_rate,
INTERFACE_LINE_CONTEXT,
INTERFACE_LINE_ATTRIBUTE1,
org_id
)
Values
(
RA_CUSTOMER_TRX_LINES_S.NEXTVAL,
'Invoice Migration',
'LINE',
l_cust_trx_type_id,
l_cust_trx_type_name,
sysdate,
sysdate,
l_currency_code,
l_term_id,
l_term_name,
l_customer_id,
l_customer_id,
l_address_id,
l_address_id,
--85222,
--87978,
l_customer_id,
1,
--40000
4000,
'Test Invoice2 12JUN08',
'User',
1,
'Invoice Migration',
'RINV_000000002',
l_org_id
);



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'
,4000
,l_gl_id_rev
,100
,'Invoice Migration',
'RINV_000000002',
l_org_id
);

Commit;
END xxx_ar_invoice_api;
/

8 comments:

Allen Sandiego said...
This comment has been removed by the author.
fa said...

How to run the upload invoice part in the application after interfaced to interface tables?

AMANDEEP SINGH said...

Hi ,

I really need your help.
We have a Invoice Interface program.

The program picks up the INVOICES from a TEXT file and creates the invoices for a particular customer.

But now we have a small issue, when an invoice interface happens by the INTERFACE PROGRAM , the program creates the invoices in a dummy customer IN Accounts Receivable . Actually the INVOICE has to be created for a cust#100304 , but instead its getting created in a dummy customer . Can you help me out why is this happening, is there any issue with the text file having the invoice number , customer number or what???????

Your help will be really appreciated.

Thanks
Amandeep Singh
+91 8886141313
aman.oracleapps@gmail.com

Anonymous said...

@Sridevi Koduru, crazy girl. almost post the same status on every blog.
If you are really worthful, first try to answer/guide the people in need, then automatically people will reach you for training stuff.
NO need of begging every where like this. Looks like earning money is your motto, share the knowledge first.

Anonymous said...

@Anonymous, Your comments true bro :)

Anonymous said...

We boycott Sridevi Koduru :D

Anonymous said...

I will recommend anyone looking for Business loan to Le_Meridian they helped me with Four Million USD loan to startup my Quilting business and it's was fast When obtaining a loan from them it was surprising at how easy they were to work with. They can finance up to the amount of $500,000.000.00 (Five Hundred Million Dollars) in any region of the world as long as there 1.9% ROI can be guaranteed on the projects.The process was fast and secure. It was definitely a positive experience.Avoid scammers on here and contact Le_Meridian Funding Service On. lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. if you looking for business loan.

gajendra said...

fbdi template for ar invoice
Use Simplified Loader Receivable Invoices template provides user-friendly Excel sheets to load complex AR Invoices to Oracle Fusion. Use Simplified Loader sheets for data migration or BAU. Replace the complex FBDI process with a one-click upload using Simplified Loader.
to get more - <a href="https://simplifiedloader.com/Catalogue/oracle_fusion_receivable_invoice_excel"https://simplifiedloader.com/Catalogue/oracle_fusion_receivable_invoice_excel