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;

Oracle Receivables Create Credit memo against Invoice

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);
p_cm_line_tbl AR_CREDIT_MEMO_API_PUB.Cm_Line_Tbl_Type_Cover%TYPE;

--Reciepts
e_excp EXCEPTION;

BEGIN
fnd_global.apps_initialize (user_id => 0,
resp_id => 20678,
resp_appl_id => 222);

mo_global.init('AR');


AR_CREDIT_MEMO_API_PUB.create_request
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
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,
x_request_id => l_batch_id,
-- credit memo request parameters
p_customer_trx_id =>11101,
p_line_credit_flag =>'N',
p_line_amount =>-100,
p_tax_amount => 0,
p_cm_reason_code =>'RETURN',
--p_comments =>,
p_cm_line_tbl =>p_cm_line_tbl,
p_skip_workflow_flag =>'Y',
p_org_id => 81,
--p_trx_number => 107801,
p_batch_source_name => 'MANUAL-OTHER',
p_credit_method_installments => NULL,
p_credit_method_rules => NULL);

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_mesg||l_msg_data;
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;

dbms_output.put_line(l_return_status||l_msg_data);
END;

Oracle Receivables API for Cancelling Receipt

1. 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);
   p_cm_line_tbl AR_CREDIT_MEMO_API_PUB.Cm_Line_Tbl_Type_Cover%TYPE;
   
   --Reciepts
   e_excp EXCEPTION;
   
   BEGIN
   fnd_global.apps_initialize (user_id => 0,resp_id => 20678,resp_appl_id => 222);
   
   mo_global.init('AR');
   
   ar_receipt_api_pub.REVERSE(
   p_api_version => 1.0
   ,p_init_msg_list => FND_API.G_FALSE
   ,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_cash_receipt_id => 6023
   ,p_receipt_number => NULL
   ,p_reversal_category_code => 'REV'
   ,p_reversal_category_name => NULL
   ,p_reversal_gl_date => TRUNC(SYSDATE)
   ,p_reversal_date => TRUNC(SYSDATE)
   ,p_reversal_reason_code => 'PAYMENT REVERSAL'
   ,p_reversal_reason_name => NULL
   ,p_reversal_comments => NULL
   ,p_called_from => NULL
   ,p_cancel_claims_flag => 'Y'
   ,p_org_id => 81
   );
   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_mesg||l_msg_data;
   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;
   
   dbms_output.put_line(l_return_status||l_msg_data);
   
   COMMIT;
   END;