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;

1 comment:

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