Home » Applications » Oracle Fusion Apps & E-Business Suite » create invoice in receivable module (receivable (develpment))
create invoice in receivable module [message #415228] Sun, 26 July 2009 10:00 Go to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Hi

please I'm trying to use AR_INVOICE_API_PUB.create_single_invoice
to create invoice but i can't use it please can any one send to me crrect code to try again

Alaa Fouad

Report message to a moderator

Re: create invoice in receivable module [message #415233 is a reply to message #415228] Sun, 26 July 2009 10:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Quote:
to create invoice but i can't use it please can any one send to me crrect code to try again
Sorry! I can't read your mind.
Is there any error?
What is your code?

By
Vamsi

Report message to a moderator

Re: create invoice in receivable module [message #415237 is a reply to message #415233] Sun, 26 July 2009 11:03 Go to previous messageGo to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear sir

I have attached the scrip

please help me

and thanks for your replay

Alaa Fouad
  • Attachment: script.txt
    (Size: 3.38KB, Downloaded 3060 times)

Report message to a moderator

Re: create invoice in receivable module [message #415240 is a reply to message #415237] Sun, 26 July 2009 13:14 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Is there any error? -- second time
How you are calling this procedure, which you have uploaded?

By
Vamsi

Report message to a moderator

Re: create invoice in receivable module [message #415244 is a reply to message #415240] Sun, 26 July 2009 13:20 Go to previous messageGo to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear Sir

the error is 'unexpected errors found!'

i call this procedure using this command

BEGIN
APPS.CITE_AR_INVOICE;
DBMS_OUTPUT.Put_Line('');

COMMIT;
END;

Report message to a moderator

Re: create invoice in receivable module [message #415281 is a reply to message #415244] Mon, 27 July 2009 01:06 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Always copy paste the error along with the error code and line number, etc.
Better to copy paste what ever is there on your sql plus.

Anyway,
Quote:
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);
IF l_return_status = fnd_api.g_ret_sts_error OR
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('unexpected errors found!');
always display all the details of the error when your handle the error.
"dbms_output.put_line('unexpected errors found!');" doesn't give you much information why the API has failed.
Quote:
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
This does.
print it.

By
Vamsi

Report message to a moderator

Re: create invoice in receivable module [message #415325 is a reply to message #415281] Mon, 27 July 2009 04:49 Go to previous messageGo to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear sir
this is the new code

Quote:

PROCEDURE CITE_AR_INVOICE IS
--1.
l_return_status varchar2(1);
l_msg_count number;
l_msg_data 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;
cnt number;
--xx number;
BEGIN ---2.

fnd_global.apps_initialize(1293, 50570, 222);


l_trx_header_tbl(1).trx_header_id := 102;
l_trx_header_tbl(1).trx_number := '2';
l_trx_header_tbl(1).bill_to_customer_id := 1042;
l_trx_header_tbl(1).cust_trx_type_id :=1000;
l_trx_header_tbl(1).trx_date := trunc(sysdate);
l_trx_header_tbl(1).trx_currency := 'EGP';
l_batch_source_rec.batch_source_id :=1001;


--l_trx_header_tbl(1).bill_to_contact_id:=4600;
--l_trx_header_tbl(1).bill_to_address_id:=4262;
--l_trx_header_tbl(1).bill_to_site_use_id:=4640;

--l_trx_header_tbl(1).
--l_trx_header_tbl(1).


--4. Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 102;
l_trx_lines_tbl(1).trx_line_id := 102;
l_trx_lines_tbl(1).line_number := 1;
l_trx_lines_tbl(1).description := 'Test';
l_trx_lines_tbl(1).UOM_CODE := 'ECH';
--l_trx_lines_tbl(1).memo_line_id := 8;
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';

--l_trx_dist_tbl(1).trx_line_id := 10000000;

--l_trx_dist_tbl(1).trx_dist_id:=10000000;

--l_trx_dist_tbl(1).account_class:='REC';
--l_trx_dist_tbl(1).code_combination_id:=84094;


--l_trx_salescredits_tbl(1).trx_salescredit_id:=10000000;
--l_trx_salescredits_tbl(1).trx_line_id:= 10000000;
--l_trx_salescredits_tbl(1).salesrep_id:=100000052;
--l_trx_salescredits_tbl(1).sales_credit_type_id:=10000000;
--5. Populate line 2 information.
--l_trx_lines_tbl(2).trx_header_id := 101;
--l_trx_lines_tbl(2).trx_line_id := 102;
--l_trx_lines_tbl(2).line_number := 2;
--l_trx_lines_tbl(2).description := 'Test';
--l_trx_lines_tbl(2).quantity_invoiced := 12;
--l_trx_lines_tbl(2).unit_selling_price := 12;
--l_trx_lines_tbl(2).line_type := 'LINE';
--6. Populate freight information and link it to line 1.
--l_trx_lines_tbl(3).trx_header_id := 101;
--l_trx_lines_tbl(3).trx_line_id := 103;
--l_trx_lines_tbl(3).link_to_trx_line_id := 101;
--l_trx_lines_tbl(3).line_number := 1;
--l_trx_lines_tbl(3).line_type := 'FREIGHT';
--l_trx_lines_tbl(3). amount := 25;
--7. Call the invoice api to create multiple invoices in a batch.
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);
dbms_output.put_line('l_msg_count='||l_msg_count);
dbms_output.put_line('l_msg_data='||l_msg_data);
dbms_output.put_line('l_return_status='||l_return_status);
IF l_return_status = fnd_api.g_ret_sts_error OR
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('unexpected errors found!');
ELSE
--8. Check whether any record exist in error table
--Invoice Creation API User Notes 6-21
SELECT count(*)
Into cnt
From ar_trx_errors_gt;
IF cnt = 0
THEN
dbms_output.put_line ( 'Customer Trx id '|| l_customer_trx_id);
ELSE
dbms_output.put_line ( 'Transaction not Created, Please check
ar_trx_errors_gt table');
END IF;
end if;

END cite_ar_invoice;



and when i execute this code for first time i get this error

Quote:

SQL> execute CITE_AR_INVOICE;
BEGIN CITE_AR_INVOICE; END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "APPS.OE_SYS_PARAMETERS", line 69
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "APPS.OE_SYS_PARAMETERS_PVT", line 390
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "APPS.OE_PROFILE", line 115
ORA-06512: at "APPS.AR_INVOICE_UTILS", line 9
ORA-06512: at "APPS.AR_INVOICE_API_PUB", line 976
ORA-06512: at "APPS.CITE_AR_INVOICE", line 79
ORA-06512: at line 1



and when i execute the code for the secon time i get this error

Quote:

SQL> execute CITE_AR_INVOICE;
BEGIN CITE_AR_INVOICE; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "APPS.AR_MO_GLOBAL_CACHE", line 55
ORA-06512: at "APPS.ARP_STANDARD", line 2912
ORA-06512: at "APPS.AR_INVOICE_UTILS", line 29
ORA-06512: at "APPS.AR_INVOICE_API_PUB", line 976
ORA-06512: at "APPS.CITE_AR_INVOICE", line 79
ORA-06512: at line 1



and when i execute it for the third time i get this messages

Quote:

SQL> execute CITE_AR_INVOICE;
l_msg_count=
l_msg_data=
l_return_status=E
unexpected errors found!



please advise me what i can do

Alaa Fouad

Report message to a moderator

Re: create invoice in receivable module [message #415329 is a reply to message #415325] Mon, 27 July 2009 05:01 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Try this before your calling code.
begin
    mo_global.init('AR');
    mo_global.set_policy_context('S',&org_id);
end;
By
Vamsi

Report message to a moderator

Re: create invoice in receivable module [message #415381 is a reply to message #415329] Mon, 27 July 2009 08:57 Go to previous messageGo to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear Sir

when i execute your code first the first and second error disappear and I have got these messages

SQL> execute cite_ar_invoice;
l_msg_count=
l_msg_data=Transaction header and line tables cannot be empty.
l_return_status=U
unexpected errors found!

PL/SQL procedure successfully completed.

and the invoice not inserted in the transaction form

so i add the line data into my code to complete the transaction
the new code is

Quote:


PROCEDURE APPS.cite_ar_invoice IS
--1.
l_return_status varchar2(1);
l_msg_count number;
l_msg_data 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;
cnt number;
--xx number;
BEGIN ---2.

--SELECT NVL(oe_sys_parameters.value('MASTER_ORGANIZATION_ID'),0)
--into xx FROM dual;
--fnd_global.apps_initialize(1013435, 55167, 222,0);
fnd_global.apps_initialize(1013435, 55167, 222,0);


l_trx_header_tbl(1).trx_header_id := 101;
--l_trx_header_tbl(1).trx_number := 'Test Invoice API';
l_trx_header_tbl(1).bill_to_customer_id := 1001;
--l_trx_header_tbl(1).cust_trx_type_id := 2376;
l_trx_header_tbl(1).cust_trx_type_id := 1;
l_trx_header_tbl(1).trx_date := trunc(sysdate);
l_trx_header_tbl(1).trx_currency := 'GBP';
--l_batch_source_rec.batch_source_id := 1188;
l_batch_source_rec.batch_source_id := -1;


l_trx_header_tbl(1).bill_to_contact_id:=4600;
l_trx_header_tbl(1).bill_to_address_id:=4262;
l_trx_header_tbl(1).bill_to_site_use_id:=4640;
--l_trx_header_tbl(1).
--l_trx_header_tbl(1).


--4. Populate line 1 information.
l_trx_lines_tbl(1).trx_header_id := 101;
l_trx_lines_tbl(1).trx_line_id := 101;
l_trx_lines_tbl(1).line_number := 1;
l_trx_lines_tbl(1).description := 'Test';
l_trx_lines_tbl(1).UOM_CODE := 'Ea';
--l_trx_lines_tbl(1).memo_line_id := 8;
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';


/*

l_trx_dist_tbl(1).trx_line_id := 10000000;

l_trx_dist_tbl(1).trx_dist_id:=10000000;

l_trx_dist_tbl(1).account_class:='REC';
l_trx_dist_tbl(1).code_combination_id:=84094;


l_trx_salescredits_tbl(1).trx_salescredit_id:=10000000;
l_trx_salescredits_tbl(1).trx_line_id:= 10000000;
l_trx_salescredits_tbl(1).salesrep_id:=100000052;
l_trx_salescredits_tbl(1).sales_credit_type_id:=10000000;

*/


--5. Populate line 2 information.
--l_trx_lines_tbl(2).trx_header_id := 101;
--l_trx_lines_tbl(2).trx_line_id := 102;
--l_trx_lines_tbl(2).line_number := 2;
--l_trx_lines_tbl(2).description := 'Test';
--l_trx_lines_tbl(2).quantity_invoiced := 12;
--l_trx_lines_tbl(2).unit_selling_price := 12;
--l_trx_lines_tbl(2).line_type := 'LINE';
--6. Populate freight information and link it to line 1.
--l_trx_lines_tbl(3).trx_header_id := 101;
--l_trx_lines_tbl(3).trx_line_id := 103;
--l_trx_lines_tbl(3).link_to_trx_line_id := 101;
--l_trx_lines_tbl(3).line_number := 1;
--l_trx_lines_tbl(3).line_type := 'FREIGHT';
--l_trx_lines_tbl(3). amount := 25;
--7. Call the invoice api to create multiple invoices in a batch.
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);
dbms_output.put_line('l_msg_count='||l_msg_count);
dbms_output.put_line('l_msg_data='||l_msg_data);
dbms_output.put_line('l_return_status='||l_return_status);
IF l_return_status = fnd_api.g_ret_sts_error OR
l_return_status = fnd_api.g_ret_sts_unexp_error THEN
dbms_output.put_line('unexpected errors found!');
ELSE
--8. Check whether any record exist in error table
--Invoice Creation API User Notes 6-21
SELECT count(*)
Into cnt
From ar_trx_errors_gt;
IF cnt = 0
THEN
dbms_output.put_line ( 'Customer Trx id '|| l_customer_trx_id);
ELSE
dbms_output.put_line ( 'Transaction not Created, Please check
ar_trx_errors_gt table');
END IF;
end if;

END cite_ar_invoice;



and then execute the code again i have got the following messages

Quote:

SQL> execute cite_ar_invoice;
l_msg_count=
l_msg_data=
l_return_status=S
Transaction not Created, Please check
ar_trx_errors_gt table

PL/SQL procedure successfully completed.



then i select from ar_trx_errors i didn't found any data in this tabel

Alaa Fouad

[Updated on: Mon, 27 July 2009 09:16]

Report message to a moderator

Re: create invoice in receivable module [message #415424 is a reply to message #415381] Mon, 27 July 2009 13:13 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
ar_trx_errors_gt is a global temporary table. As you have commit in your calling code,
BEGIN 
APPS.CITE_AR_INVOICE;
DBMS_OUTPUT.Put_Line('');

COMMIT; 
END;
the table is getting flushed out.

Remove the commit and select * from ar_trx_errors_gt to check the errors.

Later, you can commit depending on the status / error message.

EDIT: Most of the code has commented out, why can't you just delete them.
You have used code tags. That is good. But no formatting. It is not easy to read your code. Try http://www.orafaq.com/utilities/sqlformatter.htm

By
Vamsi

[Updated on: Mon, 27 July 2009 13:17]

Report message to a moderator

Re: create invoice in receivable module [message #415536 is a reply to message #415424] Tue, 28 July 2009 03:32 Go to previous messageGo to next message
alaa_fouad2004
Messages: 64
Registered: January 2009
Member
Dear sir
really i would like to thank you very much about your help

it work correct

thank you again


Alaa Fouad

Report message to a moderator

Re: create invoice in receivable module [message #415542 is a reply to message #415536] Tue, 28 July 2009 03:44 Go to previous messageGo to next message
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
Please post whatever you have found from the errors and what you have corrected.
It will be helpful for others.

By
Vamsi

Report message to a moderator

Re: create invoice in receivable module [message #502958 is a reply to message #415228] Mon, 11 April 2011 16:29 Go to previous messageGo to next message
bmineonly
Messages: 5
Registered: October 2009
Location: karachi
Junior Member
Hey All,

I need your help.
I am using ar invoice api pub create single invoice.
I write a procedure to do. When i call the procedure at query level its work fine but when i call that from apps 12 r its end with the mention error. I tried with Initialization user, application and responsibility and without them but the same error come.

ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "APPS.OE_SYS_PARAMETERS", line 68
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "APPS.OE_SYS_PARAMETERS_PVT", line 390
ORA-06510: PL/SQL: unhandled user-defined exception

Please help

Thanks
Hussain

Report message to a moderator

Re: create invoice in receivable module [message #640850 is a reply to message #502958] Thu, 06 August 2015 01:22 Go to previous messageGo to next message
trsspk
Messages: 2
Registered: August 2015
Location: US
Junior Member
Hi, I'm also facing this staange situation.
1. Developed package and registerd in R12 Apps
2. Executed concurrent program and giving me error:
"Transaction header and line tables cannot be empty"
3. I've placed fnd_file output for each of the api data that is generated in my program and when I execute it from sql (Toad), Transactions are created without any errors.

What should be the cause?
Do you need the code to advise, pls let me know

Report message to a moderator

Re: create invoice in receivable module [message #640926 is a reply to message #640850] Thu, 06 August 2015 16:09 Go to previous message
trsspk
Messages: 2
Registered: August 2015
Location: US
Junior Member
Sorry..its wrong alarm. i was useing debugs to generate the ai code and forgot to change the sequence number from nextval to currval in my debug log messed it up

Report message to a moderator

Previous Topic: BR100 for SCM
Next Topic: How to interactively call BI Publisher report in Forms 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Nov 24 07:17:05 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.