Btukfyl

Hi I have a scenario where in using a cursor loop getting all the invoice values and checking the details and updating flag values to 'E' if the update validation is satisfied and only inserting those invoices to another table which doesn't satisfy the update statement.

Is there a way by which based on the flag value we can insert those invoices?

Kindly find the Code:

Procedure 

 CURSOR c2

      IS

         SELECT *

           FROM invoice_tl

          WHERE process_flag = 'N';           

BEGIN

     FOR rec IN c2

     LOOP

     BEGIN



        fnd_file.put_line (fnd_file.LOG, 'The Line Number is ' || ' ' || rec.line_number);



           IF rec.line_number IS NOT NULL

           THEN



              UPDATE invoice_tl

                 SET process_flag = 'E',

                     error_description =

                        (SELECT 'Credit Memo line amount cannot be more than Invoice Line Amount : '

                        || (rctl.extended_amount

                                    - NVL (

                                         (SELECT SUM (amount)

                                            FROM ar_activity_details

                                           WHERE customer_trx_line_id =

                                                    rctl.customer_trx_line_id),

                                         0)

                                    + NVL (

                                         (SELECT SUM (extended_amount)

                                            FROM ra_customer_trx_lines_all

                                           WHERE previous_customer_trx_line_id =

                                                    rctl.customer_trx_line_id),

                                         0))

                           FROM ra_customer_trx_all rct,

                                ra_customer_trx_lines_all rctl

                          WHERE rct.customer_trx_id =

                                   rctl.customer_trx_id

                                AND rct.org_id = 2326

                                AND rct.trx_number = rec.invoice_number

                                AND rctl.line_number = rec.line_number

                                AND rct.cust_trx_type_id =

                                       ln_trans_type_id)

               WHERE process_flag = 'N'

                     AND invoice_number = rec.invoice_number

                     AND line_number = rec.line_number

                     AND amount >

                             (SELECT (rctl.extended_amount

                                     - NVL (

                                          (SELECT SUM (amount)

                                             FROM ar_activity_details

                                            WHERE customer_trx_line_id =

                                                     rctl.customer_trx_line_id),

                                          0)

                                     + NVL (

                                          (SELECT SUM (extended_amount)

                                             FROM ra_customer_trx_lines_all

                                            WHERE previous_customer_trx_line_id =

                                                     rctl.customer_trx_line_id),

                                          0))

                               FROM ra_customer_trx_all rct,

                                    ra_customer_trx_lines_all rctl

                              WHERE rct.customer_trx_id =

                                       rctl.customer_trx_id

                                    AND rct.org_id = 2326

                                    AND rct.trx_number =

                                           rec.invoice_number

                                    AND rctl.line_number =

                                           rec.line_number

                                    AND rct.cust_trx_type_id =

                                           ln_trans_type_id);



              fnd_file.put_line (

                 fnd_file.LOG,

                 'Error Message if the CM amount more than the Invoice Line amount.');

              COMMIT;

           END IF;

        END;



        BEGIN

         fnd_file.put_line (

                 fnd_file.LOG,

                 'The Process FLag is : ' || rec.process_flag);        

           INSERT INTO second_table (

                          customer_number,

                          orig_system_cust_reference,

                          orig_system_add_reference,

                          customer_name,

                          locations,

                          inv_date,

                          creation_date,

                          inv_num,

                          balance_amount,

                          customer_trx_id,

                          customer_trx_line_id,

                          NAME,

                          term_desc,

                          term_id,

                          gl_date,

                          rec_segments1,

                          rec_segments2.....

       END;

    END LOOP;    

END

Your best bet is to use the RETURNING INTO clause. So you'll define an array and capture the appropriate parts of the updated rows:

declare

    type line_number_tt is table of invoice_tl.line_number%TYPE;

    line_number_array line_number_tt;

begin

    ....



    update invoice_tl 

    ...

    returning line_number bulk collect into line_number_array;



    [do stuff with the array here]

end;

I would really try to get rid of the select-then-loop you have, if you can. That's row by row processing and there's a reason it's called "slow-by-slow."