How To Create a Table within a Procedure

How To Create a Table within a Procedure

Firt Create the Procedure

CREATE OR REPLACE PROCEDURE test_table
IS
BEGIN
   EXECUTE IMMEDIATE 'create table ttt_temp as select * from fnd_nodes';
END test_table; 

Now Execute the procedure ...

EXEC test_table;

You Can Check the Table is created ...

SELECT *
  FROM ttt_temp;

Close OPM Batches in bulk (Between given Dates)

Custom Procedure

CREATE OR REPLACE PROCEDURE apps.pfl_close_batches (
   errbuf                    OUT   VARCHAR2,
   retcode                   OUT   VARCHAR2,
   p_c_user_id                     NUMBER,
   p_actual_cmplt_date_frm         VARCHAR2,
   p_actual_cmplt_date_to          VARCHAR2
)
IS
   CURSOR c_ops
   IS
      SELECT batch_id, actual_cmplt_date
        FROM gme_batch_header
       WHERE batch_status = 3
         AND organization_id = 88
         AND TRUNC (actual_cmplt_date) <=  TO_DATE (p_actual_cmplt_date_to, 'YYYY/MM/DD HH24:MI:SS')   
         AND TRUNC (actual_cmplt_date) >=  TO_DATE (p_actual_cmplt_date_frm, 'YYYY/MM/DD HH24:MI:SS'); 

   x_message_count    NUMBER;
   x_message_list     VARCHAR2 (4000);
   x_return_status    VARCHAR2 (2000);
   p_batch_header     gme_batch_header%ROWTYPE;
   x_batch_header     gme_batch_header%ROWTYPE;
   r_ops              c_ops%ROWTYPE;
BEGIN
   fnd_profile.initialize (p_c_user_id);
   fnd_global.apps_initialize (user_id           => p_c_user_id,
                               resp_id           => NULL,
                               resp_appl_id      => NULL
                              );

   FOR r_ops IN c_ops
   LOOP
      p_batch_header.batch_id := r_ops.batch_id;
      p_batch_header.plant_code := NULL;
      p_batch_header.batch_no := NULL;
      p_batch_header.batch_type := NULL;
      p_batch_header.batch_close_date := r_ops.actual_cmplt_date;
      gme_api_pub.close_batch (p_api_version           => 2,
                               p_validation_level      => 100,
                               p_init_msg_list         => fnd_api.g_false,
                               p_commit                => fnd_api.g_true,
                               x_message_count         => x_message_count,
                               x_message_list          => x_message_list,
                               x_return_status         => x_return_status,
                               p_batch_header_rec      => p_batch_header,
                               x_batch_header_rec      => x_batch_header,
                               p_org_code              => 'PFG'
                              );
      DBMS_OUTPUT.put_line (x_return_status);
   END LOOP;
END pfl_close_batches;
/

Note :- Change "organization_id" and "p_org_code" according to your configurations

Thanks

Srini