1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Error submitting Job: PLS-00222: cbtt_proc IS NOT in the scope of this

Discussion in 'Oracle Apps Technical' started by vamsioracle, Apr 28, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    I am ahving a problem using dbms_job.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE cbtt_getvalues
    IS
       jobid   NUMBER;
    BEGIN
    /*
    here iget ALL the VALUES i require
    LIKE param,param2, begin_date(date FOR which i want TO schedule)
    */

       jobid :=
          DBMS_JOB.submit (jod            => jobid,
                           what           => cbtt_proc (param1, param2),
                           next_date      => begin_date
                          );
    END cbtt_getvalues;
    I get an error while compiling this.

    Code (SQL):
    PLS-00222: cbtt_proc IS NOT IN the scope OF this.
    How ever the other procedure is compiled, successfully

    vamsi
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    what is cbtt_proc ?
    Can you post the full proc?
     
  3. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Yes post the procedure vamsi, post it under CODE blocks. if it's too long you can also post it as anattachment.
     
  4. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Re: passing parameter using fnd_request

    View attachment cbtt_delegate_to_mail.txt

    Code (SQL):
    CREATE OR REPLACE PROCEDURE cbtt_delegate_to_mail_proc (
       fromname     IN   VARCHAR2,
       sendto       IN   VARCHAR2,
       begin_date   IN   DATE,
       end_date     IN   DATE
    )
    IS
       full_to_name   VARCHAR2 (100);
       to_mail        VARCHAR2 (100);
       --- mail id of the employee to whom delegation has been done
       empid_to       NUMBER;
       mesg           VARCHAR2 (4000);
       conn           UTL_SMTP.connection;
       cbtt_mail      VARCHAR2 (50)       := 'CBTT MAIL ALERT';
       subject        VARCHAR2 (100)      := 'Delegation of Responsibility';
       jobid          NUMBER              := 0;
    BEGIN
    /*employee id and email address of the person based on attribute colmn
        captured in sendto, from wf_routing_rules*/

       SELECT employee_id, email_address
         INTO empid_to, to_mail
         FROM fnd_user
        WHERE user_name = sendto;

       SELECT full_name
         INTO full_to_name
         FROM per_all_people_f
        WHERE person_id = empid_to;

       mesg :=
             fromname
          || ' ,  has delegated the responsibility for approvals to '
          || full_to_name
          || ' from '
          || begin_date
          || ' to '
          || end_date
          || '    Please process all the leave applications before 48 hours '
          || 'THIS IS AN AUTO GENERATED MAIL, PLEASE DONOT  REPLY BACK.';
       conn := UTL_SMTP.open_connection ('CBMS1', 25);
       UTL_SMTP.helo (conn, 'CBMS1');
       UTL_SMTP.mail (conn, to_mail);
       UTL_SMTP.rcpt (conn, to_mail);
    /*utl_smtp.data( conn, mesg );*/
       UTL_SMTP.open_data (conn);
       UTL_SMTP.write_data (conn,
                               'Date: '
                            || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                            || CHR (13)
                           );
       UTL_SMTP.write_data (conn, 'From: ' || cbtt_mail || CHR (13));
       UTL_SMTP.write_data (conn, 'Subject: ' || subject || CHR (13));
       UTL_SMTP.write_data (conn, 'To: ' || to_mail || CHR (13));
       UTL_SMTP.write_data (conn, '' || CHR (13));
       UTL_SMTP.write_data (conn, mesg);
       UTL_SMTP.close_data (conn);
       UTL_SMTP.quit (conn);
    /*cbtt_reminder_mail_proc(fromname, sendto, begin_date,end_date);*/
       DBMS_JOB.submit
          (job            => jobid,
           what           => 'cbtt_reminder_mail_proc(fromname,sendto ,begin_date ,end_date ));',
           next_date      => begin_date
          );
    END cbtt_delegate_to_mail_proc;
    View attachment cbtt_remindr_mail.txt
    Code (SQL):

    CREATE OR REPLACE PROCEDURE cbtt_reminder_mail_proc (
       fromname     IN   VARCHAR2,
       sendto       IN   VARCHAR2,
       begin_date   IN   DATE,
       end_date     IN   DATE
    )
    IS
       full_to_name   VARCHAR2 (100);
       to_mail        VARCHAR2 (100);
       --- mail id of the employee to whom delegation has been done
       empid_to       NUMBER;
       mesg           VARCHAR2 (4000);
       conn           UTL_SMTP.connection;
       cbtt_mail      VARCHAR2 (50)       := 'CBTT MAIL ALERT';
       subject        VARCHAR2 (100)  := 'REMINDER: Delegation of Responsibility';
    BEGIN
    /*employee id and email address of the person based on attribute colmn captured
        in sendto, from wf_routing_rules*/

       SELECT employee_id, email_address
         INTO empid_to, to_mail
         FROM fnd_user
        WHERE user_name = sendto;

       SELECT full_name
         INTO full_to_name
         FROM per_all_people_f
        WHERE person_id = empid_to;

       mesg :=
             fromname
          || ' ,  has delegated the responsibility for approvals to '
          || full_to_name
          || ' from '
          || begin_date
          || ' to '
          || end_date
          || '    Please process all the leave applications before 48 hours '
          || 'THIS IS AN AUTO GENERATED MAIL, PLEASE DONOT  REPLY BACK.';
       conn := UTL_SMTP.open_connection ('CBMS1', 25);
       UTL_SMTP.helo (conn, 'CBMS1');
       UTL_SMTP.mail (conn, to_mail);
       UTL_SMTP.rcpt (conn, to_mail);
    /*utl_smtp.data( conn, mesg );*/
       UTL_SMTP.open_data (conn);
       UTL_SMTP.write_data (conn,
                               'Date: '
                            || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
                            || CHR (13)
                           );
       UTL_SMTP.write_data (conn, 'From: ' || cbtt_mail || CHR (13));
       UTL_SMTP.write_data (conn, 'Subject: ' || subject || CHR (13));
       UTL_SMTP.write_data (conn, 'To: ' || to_mail || CHR (13));
       UTL_SMTP.write_data (conn, '' || CHR (13));
       UTL_SMTP.write_data (conn, mesg);
       UTL_SMTP.close_data (conn);
       UTL_SMTP.quit (conn);
    END cbtt_reminder_mail_proc;
    I have uploaded the codes.

    Procedure cbtt_delegate_to_mail sends mail as soon as a vacation rule is created. Now i want cbtt_reminder_mail_proc to send mail on the date of activation of vacation rule, which is identified by begin_date.

    suppose a vacation rule is created today, for dates in future(say tomorrow), then the first mail fires today and a reminder mail should fire tomorrow. this is my requirement.

    Vamsi
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi vamsi, I have just posted your code inline as it is easier to read. Hope it's ok.
     
  6. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Ok Sadik

    I am much concerned about the working. I have a call with hte client and i need to tell the status of it and negotiate a new dead line for this.

    vamsi
     
  7. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Sadik/Apps_expert.

    Any update on this issue. I could not solve it


    vamsi