I've been trying to post this procedure, that I've tried but for some reason it is not getting added to my post...

I just need help in writing the message into the auditing table..

Code:
Create OR Replace Procedure auditingsp( pi_pid    IN Number
                                      , pi_mand   IN Varchar2
                                      , pi_bcode  IN Varchar2
                                      , pi_opp    IN Number
                                      , pi_I_O    IN Varchar2
                                      , pi_Source IN Varchar2 )
 As
   Cursor aud_cur( pi_mand IN Varchar2
               , pi_I_O  IN Varchar2
               , pi_bcode  IN Number ) Is
      SELECT v_yn
           , message 
        FROM acfg
       WHERE mand = pi_mand
         AND (pi_I_O = 'I')
          OR (pi_I_O = 'O' AND bcode=pi_bcode);
 
   Type acfg_tab Is Table Of aud_cur%Rowtype;
   acfg_array acfg_tab;
Begin
   Open aud_cur( pi_mand
             , pi_I_O
             , pi_bcode);
   Loop
      Fetch aud_cur Bulk Collect
         INTO acfg_array Limit 1000;
      Exit When aud_cur%Notfound;
   
      For i IN acfg_array.First .. acfg_array.Last Loop

         If upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'I' THEN		
            INSERT INTO auditing
               ( ID
               , cdate
               , ccode
               , pid
               , mand
	       , skey
               , Source
               , message )
            VALUES
               ( audseq.Nextval
               , Sysdate
               , pi_bcode
               , pi_pid
               , pi_mand
	       , ??????             /* skey needs to be assigned based on the auditing.ccode */
               , pi_Source
               , ??????             /* this should be step a) from my previous reply,Build a dynamic SQL like select 
func(cname) from tname where mand= pi_mand; /* sql should also include acfg.wheresql in the where clause, sql should also 
include acfg.group_col1,acfg.group_col2, acfg.group_col3 if there are any values. Execute the sql and using the 
acfg.message for that row, insert into auditing.message field */ 
 	       );

         Elsif upper(acfg_array(i).v_yn) = 'Y' and pi_I_O = 'O' THEN
		 INSERT INTO auditing
               ( ID
               , cdate
               , ccode
               , pid
               , mand
	       , skey
               , Source
               , message )
            VALUES
               ( audseq.Nextval
               , Sysdate
               , pi_bcode
               , pi_pid
               , pi_mand
	       , ??????             /* skey needs to be assigned based on the auditing.ccode */
               , pi_Source
               , ??????             /* this should be step b) from my previous reply,build a dynamic SQL like select 
func(cname) from tname where mand= pi_mand and auditing.pid= pi_pid group by group_col1;  pi_pid is the argument passed in 
the SP. sql should also include acfg.wheresql in the where clause, sql should also include acfg.group_col2, acfg.group_col3
 if there are any values.Execute the sql and using the acfg.message for that row, insert into auditing.message field  */
	       );

         Elsif upper(acfg_array(i).v_yn) = 'N' THEN
	         dbms_output.put_line('Data is ignored');
         End If;
      End Loop;
   End Loop;
   Close aud_cur;
   Commit;

Exception
   When Others Then
      Rollback;
      dbms_output.put_line(Sqlerrm(Sqlcode));
End auditingsp;
I would appreciate any kind of help.. Please give me your thoughts.

Thank you very much

Edited by: newbiegal on Jul 8, 2009 6:33 AM