Profile Option in Oracle Apps.

Profile Options

Profile options are used to determine the behaviour of oracle application forms and programs.

We can set the profile option at 4 different levels.

1. Site
2. Application
3. Responsibility
4. User

                               

                  Site Level: - These settings will apply to all users at an installation site.

     Application Level : - These settings apply to all users of any responsibility associated with

                                        the application.

 Responsibility Level: - These settings apply to all users currently signed on under the

    responsibility.

                 User Level: - These settings apply to an individual user, identified by their application
                                         username.

Navigation: -System Administrator === > Profile === > System.

How to run the Concurrent Program from Backend.

How to Run Concurrent Program from Backend.

Now we are going to learn about how to run the concurrent program from backend.

We can run the concurrent program from backend using FND_REQUEST.Submit_Request API.

Call the FND_REQUEST.Submit_Request and pass the necessary parameters.

Application     -           Give the application short name in which application your con 

 program was registered.

            Program           -           Give the short name of the concurrent program.

            Description      -           Give the name of the concurrent program.

            start_time        -           Null.

            sub_request     -           FALSE

            argument1       -           Give the parameters.

Declare

Request_id NUMBER := 0;

Begin

            Request_id := FND_REQUEST.Submit_Request(application => 'XX'

                                    , program => 'XX_PLANNER_CSD_UPDATE'

                                    , description => 'Planner csd update data load'

                                    , start_time => NULL

                                    , sub_request => FALSE

                                    , argument1 => p_file_path

                                    );

            IF Request_id = 0    THEN

                        DBMS_OUTPUT.PUT_LINE( 'Request submission For this store FAILED' );

            ELSE 

  DBMS_OUTPUT.PUT_LINE( 'Request submission For this store SUCCESSFUL' );

            END IF;

End;

commit;

How to Wait for Concurrent Request to Complete Previous Program.

Wait for concurrent request using fnd_concurrent.wait_for_request

 Now we are going to learn about how to wait for a concurrent program to complete previously submitted first. If you submitted the concurrent programs from backend and you want to run them in sequence one after the other then this can be achieved through fnd_concurrent.wait_for_request

 Call the fnd_concurrent.wait_for_request and pass the necessary parameters.

      l_complete      BOOLEAN;
      l_phase           VARCHAR2 (100);
      l_status           VARCHAR2 (100);
      l_dev_phase   VARCHAR2 (100);
      l_dev_status   VARCHAR2 (100);
      l_message      VARCHAR2 (100);

 The l_request_id is the return value of previously submitted program. If the first program submitted successfully then it should wait.

Arguments (input)

     request_id      -   Request ID to wait on
     interval         -   Number of seconds to wait.
     max_wait     - Max amount of time to wait (in seconds) for request's completion
 Arguments (output)  

     phase            - Request phase ( from meaning in fnd_lookups )
     status            - Request status( for display purposes          )
     dev_phase    - Request phase as a constant string so that it can be used for comparisons
     dev_status    - Request status as a constatnt string
     message       - Completion message if request has completed

IF l_request_id > 0
      THEN
         l_complete :=
            fnd_concurrent.wait_for_request (request_id      => l_request_id
                                                             ,interval            => 10
                                                             ,max_wait        => 60
                                                             ,phase              => l_phase
                                                             ,status              => l_status
                                                             ,dev_phase      => l_dev_phase
                                                             ,dev_status      => l_dev_status
                                                             ,message         => l_message
                                            );
         COMMIT;

         IF UPPER (lc_dev_phase) IN ('COMPLETE')
         THEN
            dbms_output.put_line('Concurrent request completed successfully');
         END IF;
      END IF;

Registering PL/SQL Stored Procedure in Oracle Apps.

Registering PL/SQL Stored procedure in Oracle Apps

Now we are going to learn about how to register the PL/SQL stored procedure in Apps.

Step1:- Create a procedure

create or replace procedure emp_test(p_empno         number,

                                                                        errbuf      out   varchar2,

                                                                         retcode   out   number)

 As

            v_empno  number;

            v_ename  varchar2(10);

v_salary number;

            v_deptno number;

            l_error varchar2(100);

                        l_retcode number;

           Begin

select empno,ename,sal,deptno into v_empno,v_ename,v_salary, v_deptno
from emp  
where empno = p_empno;

Dbms_Output.put_line('Empno:- ' ||v_empno||' Name:- '||v_ename||' Salary:- '||v_salary||'   Department:- '||v_deptno);

   

Exception

                        When Others Then

                        l_error := 'error while executing the procedure '||SQLERRM;

                        errbuf :=l_error;

                        retcode := 1;

End;

Step2:- Login To Oracle Apps and create the executable.

             Navigation --- >  System Administrator => Cncurrent => Program =>  Executable

        
            Execution Method:- PL/SQL Stored Procedure.

            Execution File Name:- Give the procedure Name.

       

Step3:- Create the concurrent programm

             Navigation --- >  System Administrator => Cncurrent => Program =>  Define

     

           If there is any parameter then click on parameters tab. And provide the parameters.

Step4:-  Attach the concurrent program to a request group.

             
              Navigation --- >  System Administrator => Security => Responsibility => Request.

             
              Attach the concurrent program to request group on which responsibility u want.
     

           

Step5:- Run the concurrent program.

     
             Switch to Order Management responsibility.

             View => Requests => Submit.

            Click on Find it will show the status of the program.

            Now the procedure is called successfully.

Sending Email From Oracle PL/SQL (UTL_SMTP)

Sending Email From Oracle PL/SQL (UTL_SMTP)

Simple (Single Recipient)

Now we are going to learn about how to send the mail using oracle PL/SQL (UTL_SMTP).

First we will see the simple mailing it contains only sender and one receiver.

Procedure for sending mail.

CREATE OR REPLACE 

PROCEDURE send_mail(    p_from         varchar2,

                                              p_to             varchar2,

                                              p_body        varchar2

                                              errbuf    out  varchar2 )

AS

         l_smtp_host     varchar2(100)   := '<give ur mailing host name>';

         l_smtp_port      number              := <give the port number>;

         l_mail_conn     utl_smtp.connection;

         l_error              varchar2(1000);

Begin 

         l_mail_conn :=utl_smtp.open_connection(  l_smtp_host , l_smtp_port );

         utl_smtp.helo(l_mail_conn, l_smtp_host);                                                                                 
        utl_smtp.mail(l_mail_conn, p_from);

         utl_smtp.rcpt (l_mail_conn, p_to);

         utl_smtp.open_data(l_mail_conn);

         utl_smtp.write_data(l_mail_conn, p_body);

         utl_smtp.close_data(l_mail_conn);

         utl_smtp.quit(l_mail_conn);

Exception

         WHEN OTHERS THEN

         l_error := 'Error while sending mail :   ' || SQLERRM; 

         errbuf := l_error;

END;

/                                                      

 Now the procedure is created successfully. 

 We will execute the procedure to send the mail 

 Declare

 v_error varchar2(100);

 Begin       

         send_mail(p_from => '<sender mail id>',

                           p_to     => '<recevier mail id>',

                           p_body=> ' This is a test mail.',

                           v_error)

End

/ 

Sending email to multiple recipients

Now we are going to learn about how to send the mail to multiple recipients.

 create or replace package test_mail_pkg
  as
      type array is table of varchar2(255);
  
            procedure send( p_sender_email in varchar2,
                   p_from         in varchar2 default NULL,
                   p_to           in array default array(),
                   p_cc           in array default array(),
                   p_subject      in varchar2 default NULL,
                   p_body         in long  default NULL );
   end;
   /
   
   
   
   create or replace package body test_mail_pkg
    as
  
    g_crlf        char(2) default chr(13)||chr(10);
    g_mail_conn   utl_smtp.connection;
    g_mailhost    varchar2(255) := '<give ur mailing host name>';
    
    function address_email( p_string in varchar2,
                            p_recipients in array ) return varchar2
   is
       l_recipients long;
   begin
      for i in 1 .. p_recipients.count
      loop
         utl_smtp.rcpt(g_mail_conn, p_recipients(i) );
         if ( l_recipients is null )
         then
             l_recipients := p_string || p_recipients(i) ;
         else
             l_recipients := l_recipients || ', ' || p_recipients(i);
         end if;
      end loop;
      return l_recipients;
   end;
   
   procedure send( p_sender_email in varchar2,
                   p_from         in varchar2 default NULL,
                   p_to           in array default array(),
                   p_cc           in array default array(),
                   p_subject      in varchar2 default NULL,
                   p_body         in long  default NULL )
   is
       l_to_list   long;
       l_cc_list   long;
        
       l_date      varchar2(255) default
                   to_char( SYSDATE, 'dd Mon yy hh24:mi:ss' );

       procedure writeData( p_text in varchar2 )
       as
       begin
           if ( p_text is not null )
           then
               utl_smtp.write_data( g_mail_conn, p_text || g_crlf );
           end if;
       end;
       
       begin
       g_mail_conn := utl_smtp.open_connection('<give ur mailing host name>',<give the port number>);

       utl_smtp.helo(g_mail_conn, g_mailhost);
       utl_smtp.mail(g_mail_conn, p_sender_email);
       
         l_to_list  := address_email( 'To: ', p_to );
       l_cc_list  := address_email( 'Cc: ', p_cc );
        
       
       utl_smtp.open_data(g_mail_conn );

       writeData( 'Date: ' || l_date );
       writeData( 'From: ' || nvl( p_from, p_sender_email ) );
       writeData( 'Subject: ' || nvl( p_subject, '(no subject)' ) );

       writeData( l_to_list );
       writeData( l_cc_list );

       utl_smtp.write_data( g_mail_conn, '' || g_crlf );
       utl_smtp.write_data(g_mail_conn, p_body );
       utl_smtp.close_data(g_mail_conn );
       utl_smtp.quit(g_mail_conn);
   end;


  end;
  
  

 

The package for sending mail to sending multiple recipient is completed.
Now execute the procedure to send mail to multiple recipient.

Begin
        multi_res_mail_pkg( p_sender_email => '<sender mail id>',
                                         p_from => '<sender mail id>',
                                         p_to => test_mail_pkg.array( '<recevier mail id>','<recevier mail id>' ),
                                         p_cc => test_mail_pkg.array('<recevier mail id>','<recevier mail id>'  ),
                                         p_subject => 'Test Mail',
                                         p_body => 'This is a test mail with multiple recipients.'
                                         )
End;
/