Thursday, May 26, 2011

Oracle Receivables API for Invoice and Receipt Creation

DECLARE
l_trx_hdr_id NUMBER;
l_trx_lines_id NUMBER;
l_line_number NUMBER :=1;
l_cust_account_id NUMBER;
l_btch_source NUMBER;
l_count NUMBER;
l_cust_trx_type_id NUMBER;
l_memo_lines NUMBER;
l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
l_mesg VARCHAR2(2000);
l_batch_id NUMBER;
l_cnt NUMBER := 0;
l_batch_source_rec ar_invoice_api_pub.batch_source_rec_type;
l_trx_header_tbl ar_invoice_api_pub.trx_header_tbl_type;
l_trx_lines_tbl ar_invoice_api_pub.trx_line_tbl_type;
l_trx_dist_tbl ar_invoice_api_pub.trx_dist_tbl_type;
l_trx_salescredits_tbl ar_invoice_api_pub.trx_salescredits_tbl_type;
l_customer_trx_id NUMBER;
l_chr_mesg VARCHAR2(2000);

--Reciepts
l_party_id NUMBER;
l_bank_acct_id NUMBER;
l_cr_id NUMBER;
l_receipt_method_id NUMBER;
l_pay_method_name ar_receipt_methods.name%TYPE;
l_recp_number ar_cash_receipts_all.receipt_number%TYPE;
e_excp EXCEPTION;
BEGIN
fnd_global.apps_initialize (user_id => 0,
resp_id => 20678,
resp_appl_id => 222);

mo_global.init('AR');


l_trx_header_tbl(1).trx_header_id := 11117; -- use gl group id
l_trx_header_tbl(1).trx_number := 'TestInvoiceAPI';
l_trx_header_tbl(1).bill_to_customer_id := 3043;
l_trx_header_tbl(1).cust_trx_type_id := 2;
l_batch_source_rec.batch_source_id := -1;

-- Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 11117;
l_trx_lines_tbl(1).trx_line_id := 11117;
l_trx_lines_tbl(1).line_number := 1;
l_trx_lines_tbl(1).memo_line_id := 1052;
l_trx_lines_tbl(1).quantity_invoiced := 1;
l_trx_lines_tbl(1).unit_selling_price := 1;
l_trx_lines_tbl(1).line_type := 'LINE';

AR_INVOICE_API_PUB.create_single_invoice (p_api_version => 1.0,
p_batch_source_rec => l_batch_source_rec,
p_trx_header_tbl => l_trx_header_tbl,
p_trx_lines_tbl => l_trx_lines_tbl,
p_trx_dist_tbl => l_trx_dist_tbl,
p_trx_salescredits_tbl => l_trx_salescredits_tbl,
x_customer_trx_id => l_customer_trx_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

COMMIT;

dbms_output.put_line('Test'l_customer_trx_idl_return_statusl_msg_data);

IF l_return_status<> FND_API.G_RET_STS_SUCCESS
THEN
dbms_output.put_line('... Msg Count: ' l_msg_count);
l_chr_mesg := 'Error in Create Single Invoice';
IF l_msg_count = 1
THEN
dbms_output.put_line('... Msg Data: ' l_msg_data);
l_chr_mesg := l_chr_mesgl_msg_data;
RAISE e_excp;
ELSIF l_msg_count > 1
THEN
-- the messages on the stack are more than one so call them in a loop
-- and put the messages in a PL/SQL table.
l_count := 0;
LOOP
l_count := l_count +1 ;
l_mesg := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_mesg IS NOT NULL
THEN
dbms_output.put_line('... Msg Data: ' l_count '. ' l_mesg);
l_chr_mesg := l_chr_mesg' 'l_mesg;
ELSE
RAISE e_excp;
END IF;
END LOOP;
END IF;
END IF;

l_msg_count := NULL;
l_msg_data := NULL;
l_cr_id := NULL;
l_return_status := NULL;

ar_receipt_api_pub.Create_and_apply
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
-- p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'INR',
p_amount => 450,
p_receipt_number => 'TEST78889',
p_receipt_date => TRUNC(SYSDATE-30),
p_gl_date => TRUNC(SYSDATE-30),
p_customer_id => 3043,
--p_default_site_use => 'Y', --bug4448307-4509459
p_customer_bank_account_id => NULL,
p_customer_site_use_id => NULL,
p_remittance_bank_account_id => --Bank use id
--p_customer_site_use_id => r_main_rec.party_site_id,
p_receipt_method_id => 2001,
-- p_attribute_record => l_attribute_rec,
p_org_id => 81,
p_cr_id => l_cr_id,
p_customer_trx_id => 2187,
p_amount_applied => 450);

dbms_output.put_line('Test'l_cr_idl_return_statusl_msg_data);

COMMIT;

IF l_return_status<> FND_API.G_RET_STS_SUCCESS
THEN
dbms_output.put_line('... Msg Count: ' l_msg_count);
l_chr_mesg := 'Error in Create Single Invoice';
IF l_msg_count = 1
THEN
dbms_output.put_line('... Msg Data: ' l_msg_data);
l_chr_mesg := l_chr_mesgl_msg_data;
RAISE e_excp;
ELSIF l_msg_count > 1
THEN
-- the messages on the stack are more than one so call them in a loop
-- and put the messages in a PL/SQL table.
l_count := 0;
LOOP
l_count := l_count +1 ;
l_mesg := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_mesg IS NOT NULL
THEN
dbms_output.put_line('... Msg Data: ' l_count '. ' l_mesg);
l_chr_mesg := l_chr_mesg' 'l_mesg;
ELSE
RAISE e_excp;
END IF;
END LOOP;
END IF;
END IF;

EXCEPTION
WHEN OTHERS THEN
l_chr_mesg := l_chr_mesg' ''Error in Create Single Invoice'SQLERRM;
RAISE e_excp;

END;

4 comments:

oracle Ebs training said...

Hi,
It looks like you spent much time and effort in writing this blog. I am appreciating your effort. It Was Very Good Information For oracle community.surely i will refer my friends to read this blog it will help them at certain time.
Thank you,
Oracle Financials training

Unknown said...

Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

Smith said...

Oracle Apps R12 and Oracle Fusion Cloud Self Paced Online Training Videos Published on Udemy with Life Time Access & Live Meeting Support to Clear your Queries. Avail 25% to 80% discount. Please Check https://www.oracleappstechnical.com for Never Before Offers and Discount Coupon Codes.

Oracle Apps Technical Dot Com said...

25% OFF on Oracle Apps R12 Financials Self Paced Course along with 11 Additional Add On Courses (321 Session Videos of 120 Hours Recordings). Our Top Trending Course with 1700 Enrolled Udemy Students

Please Check https://www.oracleappstechnical.com for details