Home » Applications » Oracle Fusion Apps & E-Business Suite » Error at Concurrent but OK in toad when run create invoice api (Oracle Ebiz Suite R12)
Error at Concurrent but OK in toad when run create invoice api [message #575274] Mon, 21 January 2013 20:54 Go to next message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
Dear good samaritans,

Hope u guys can help me with my problem. I have created a package to process invoices data and inserted to Oracle Ebiz using ar_invoice_api_pub.create_invoice API. When running the package through toad, the data inserted perfectly just as required. But when running through concurrent program, all of the invoices data are coming out the below error:

Error:API execution failed. ORA-06502: PL/SQL: numeric or value error

I have trace using dbms output and fnd_file.put_line of the data inserted for the API. Whatever runs in toad are the same data runs in concurrent.

Below are my script:

PROCEDURE run_import_test (
      --Concurrent parameters
      --errbuf            OUT NOCOPY      VARCHAR2,
      --retcode           OUT NOCOPY      VARCHAR2,
      --Import parameters
      p_batch_id        IN              VARCHAR2,
      p_system_source   IN              VARCHAR2,
      p_gl_date         IN              VARCHAR2 DEFAULT NULL
   )
   AS
      --API variable
      l_return_status          VARCHAR2 (1);
      l_msg_count              NUMBER;
      l_msg_data               VARCHAR2 (32767);
      l_message                VARCHAR2 (32767);
      l_msg_index_out          NUMBER;
      l_msg_stack              VARCHAR2 (32767);
      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;
      --Invoice Header variable
      l_rec_sts1               VARCHAR2 (1);
      l_msg_stack1             VARCHAR2 (32767);
      i                        NUMBER                                    := 0;
      --Invoice Line variable
      l_rec_sts2               VARCHAR2 (1);
      l_msg_stack2             VARCHAR2 (4000);
      l_line_sts               VARCHAR2 (1);
      j                        NUMBER                                    := 0;
      l_sysdate                DATE;
      --added 06-dec
      l_period_end_date       DATE;
      l_period_start_date     DATE;
      l_syste_date            DATE;

      

   BEGIN         
         l_trx_header_tbl (i).trx_header_id := '1';
         l_trx_header_tbl (i).trx_number := '0000955359';
         l_trx_header_tbl (i).trx_date := trunc(sysdate);

         l_trx_header_tbl (i).gl_date := sysdate;
        

         l_trx_header_tbl (i).trx_currency := 'MYR';
         l_trx_header_tbl (i).cust_trx_type_id := '1065';
         l_trx_header_tbl (i).bill_to_customer_id := '27856';
         l_trx_header_tbl (i).invoicing_rule_id := '-2';
         l_trx_header_tbl (i).term_id := '5';
         
         -- DFF value
         l_trx_header_tbl (i).attribute_category := 'Global Data Elements1';
         
         --shift 4 offset due to functional miss setup
         l_trx_header_tbl (i).attribute5 := 'INVC_LIC_NEW';
         l_trx_header_tbl (i).interface_header_attribute1 := 'INVC_LIC_NEW';
         l_trx_header_tbl (i).attribute6 := '';
         l_trx_header_tbl (i).interface_header_attribute2 := '';
         l_trx_header_tbl (i).attribute7 :='';
         l_trx_header_tbl (i).interface_header_attribute3 := '';
         l_trx_header_tbl (i).attribute8 := '';
         l_trx_header_tbl (i).interface_header_attribute4 := '';
         l_trx_header_tbl (i).attribute9 := 'N';
         l_trx_header_tbl (i).interface_header_attribute5 := 'N';
         l_trx_header_tbl (i).attribute10 := '0';
         l_trx_header_tbl (i).interface_header_attribute6 := '0';
         l_trx_header_tbl (i).attribute11 := '2013/01/08';
         l_trx_header_tbl (i).interface_header_attribute7 := '2013/01/08';
         l_trx_header_tbl (i).attribute12 := '';
         l_trx_header_tbl (i).interface_header_attribute8 := '';
         l_trx_header_tbl (i).interface_header_context := 'SPMS';
         
            
         l_trx_lines_tbl (j).trx_header_id := '1';
         l_trx_lines_tbl (j).trx_line_id := '1';
         l_trx_lines_tbl (j).line_number := '1';
         l_trx_lines_tbl (j).description := 'LICENSE FEE';
         l_trx_lines_tbl (j).memo_line_id := '2042';
         l_trx_lines_tbl (j).quantity_invoiced := '1';
         l_trx_lines_tbl (j).unit_selling_price := '21';
         l_trx_lines_tbl (j).accounting_rule_id := '1056';
        
         l_trx_lines_tbl (j).rule_start_date := trunc(sysdate);      
         l_trx_lines_tbl (j).line_type := 'LINE';
 
         -- DFF value
         l_trx_lines_tbl (j).interface_line_context := 'SPMS';
         l_trx_lines_tbl (j).interface_line_attribute1 := '01683247-000SU/122012';
         l_trx_lines_tbl (j).interface_line_attribute2 := '08/01/2013';
         l_trx_lines_tbl (j).interface_line_attribute3 := '11/08/2013';
         l_trx_lines_tbl (j).interface_line_attribute4 := '';
         l_trx_lines_tbl (j).interface_line_attribute5 := 'N';
         l_trx_lines_tbl (j).interface_line_attribute6 := '';
         l_trx_lines_tbl (j).interface_line_attribute7 := '';
         l_trx_lines_tbl (j).interface_line_attribute8 := 'IN';
         l_trx_lines_tbl (j).interface_line_attribute9 := '08/01/2013';
         l_trx_lines_tbl (j).interface_line_attribute10 := sysdate;
   
         mo_global.set_policy_context ('S', fnd_global.org_id);
         
         --Get batch source
         l_batch_source_rec.batch_source_id := '4025';

         DELETE      ar_trx_errors_gt;

         fnd_concurrent.af_commit;

         BEGIN
            ar_invoice_api_pub.create_invoice
                           (p_api_version               => 1.0,
                            p_commit                    => fnd_api.g_false,
                            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_return_status             => l_return_status,
                            x_msg_count                 => l_msg_count,
                            x_msg_data                  => l_msg_data
                           );
                           
            fnd_concurrent.af_commit;
            dbms_output.put_line('Result: '||l_msg_data);  
           
         EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := fnd_api.g_ret_sts_error;
               l_msg_stack := 'API execution failed. ' || SQLERRM;
               
               dbms_output.put_line('Error: '||l_msg_stack);

               
         END;

         fnd_file.put_line (1, 'l_return_status : ' || l_return_status);
         fnd_file.put_line (1, 'l_msg_count : ' || l_msg_count);
         fnd_file.put_line (1, 'l_msg_data : ' || l_msg_data);

         FOR m IN 1 .. NVL (l_msg_count, 0)
         LOOP
            fnd_msg_pub.get (p_msg_index          => m,
                             p_data               => l_message,
                             p_msg_index_out      => l_msg_index_out,
                             p_encoded            => fnd_api.g_false
                            );
            fnd_file.put_line (1, 'l_message' || m || ' : ' || l_message);
            l_msg_stack := l_msg_stack || ' ' || l_message;
         END LOOP;

         l_msg_stack := l_msg_data || ' ' || l_msg_stack;

      print_error (p_batch_id, p_system_source, 'C');
   END run_import_test;


Thanks in advance.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575275 is a reply to message #575274] Mon, 21 January 2013 21:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
below is a BUG waiting to bite the unsuspecting
 EXCEPTION
            WHEN OTHERS
            THEN
               l_return_status := fnd_api.g_ret_sts_error;
               l_msg_stack := 'API execution failed. ' || SQLERRM;
               
               dbms_output.put_line('Error: '||l_msg_stack);


if you remove the obfuscation above, you know which actual line throws the error!

[Updated on: Mon, 21 January 2013 21:03]

Report message to a moderator

Re: Error at Concurrent but OK in toad when run create invoice api [message #575276 is a reply to message #575275] Mon, 21 January 2013 21:03 Go to previous messageGo to next message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
Honestly, I'm a newbie. Hope u can shed some light over this.

Thank you.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575277 is a reply to message #575276] Mon, 21 January 2013 21:04 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
since we don't have your tables or data, we can NOT run, test, debug or improve posted code.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575278 is a reply to message #575277] Mon, 21 January 2013 21:09 Go to previous messageGo to next message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
OK, then can you tell me what I should do to based on your statement.

Quote:
if you remove the obfuscation above, you know which actual line throws the error!


Honestly, I'm new to this pl/sql.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575279 is a reply to message #575278] Mon, 21 January 2013 21:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Below are my script:
I am baffled, bewildered, & confused as to how to reconcile above with below.
>Honestly, I'm new to this pl/sql.

Are you saying you don't understand your own code?
Re: Error at Concurrent but OK in toad when run create invoice api [message #575280 is a reply to message #575279] Mon, 21 January 2013 21:20 Go to previous messageGo to next message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
I do understand my code. The obfuscation part of the code you were referring to is how i want to capture the error. If you have other ways to suggest, please let me know.

Thank you.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575281 is a reply to message #575280] Mon, 21 January 2013 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
remove, delete, eliminate the whole & complete EXCEPTION code (at least while debugging)


delete, remove, & eliminate all EXCEPTION code
For reason why check these links.

http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html

http://tkyte.blogspot.com/2008/01/why-do-people-do-this.html

http://tkyte.blogspot.com/2007/03/challenge.html


Re: Error at Concurrent but OK in toad when run create invoice api [message #575282 is a reply to message #575281] Mon, 21 January 2013 21:49 Go to previous messageGo to next message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
Thanks for your suggestions. Below are the error that i encounter:

Cause: FDPSTP failed due to 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 3021
ORA-06512: at "APPS.AR_INVOICE_UTILS", line 29
ORA-06512: at "

How do i move from this?
Re: Error at Concurrent but OK in toad when run create invoice api [message #575285 is a reply to message #575282] Mon, 21 January 2013 22:25 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>ORA-06512: at "

did line above get truncated & lost?

>How do i move from this?
I have no idea since I see NOTHING in your most recent post that ties, relates, indicates anything regarding "PROCEDURE run_import_test " you posted.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575286 is a reply to message #575285] Mon, 21 January 2013 22:31 Go to previous messageGo to next message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
>did line above get truncated & lost?

Not sure. Maybe truncated by default. I did not do anything to truncate any error messages.

Anyway, i did some 'googling'. I found this:
Quote:
There was trouble with the "XX Security Profile" set as MO: Security profile for "XX Receivable Manager" Responsibility.
In general, Once security profiles are defined, a concurrent program named as "Security List Maintenance " is run to complete the process. This program had not been run for "XX Security Profile". I ran this program with security profile parameter as "XX Security Profile" and retest the issue to find that it's been resolved.


This person have the same problem with mine. Will let u know if i have successfully try this.

Thank you.
Re: Error at Concurrent but OK in toad when run create invoice api [message #575417 is a reply to message #575286] Wed, 23 January 2013 01:28 Go to previous message
acidsneaker
Messages: 10
Registered: January 2013
Location: Malaysia
Junior Member
At last, the problem resolved. I was using the wrong responsibility thus making me also using the wrong Operating Unit. So, by using the correct responsibility running the program through concurrent the data inserted correctly.

Thank you to BlackSwan for your suggestions.

[Updated on: Wed, 23 January 2013 01:29]

Report message to a moderator

Previous Topic: Which API to run to update existing and insert new recs
Next Topic: PO Charge Account /Accrual Account/Variance Account not Defaulting While Creating PO in Fusion
Goto Forum:
  


Current Time: Thu Mar 28 10:01:46 CDT 2024