What is difference between Package and Stored Procedure?

Package:

• A package is a group of PL/SQL types, objects, stored procedures, and functions. it has two parts one is specification other is the body.
• In the specification, we mention procedures, functions & their parameters.
• In the body part, we define the whole operation performed by procedures and functions mentioned in the specification part.
• You can make your procedure private to the package by not declaring it in the package specification.
• packages cannot be called, passed parameters, or nested
• While calling a procedure from a package whole of the package is loaded into the memory. Like if a package consists of 4 procedures & we call 1 procedure then the whole 4 would be loaded to memory.

Procedure:

• The procedure is a standalone pl/sql unit in which all things related to procedure define in one go i.e parameters and whole functionality etc
• A procedure is a stored program in an oracle that is written down when a particular task has to be done.
• A procedure that resides in a package has to be called as <package_name>.<procedure_name>. On the other hand, a standalone procedure can be called by its name alone.
• We can pass IN, OUT parameters in the procedure.

BLANKET SALES AGREEMENT IN ORACLE APPS API

Create or Replace procedure mutl_blanket_update
is
 
  -- Input variables

   l_hdr_rec             OE_Blanket_PUB.header_rec_type;
   l_hdr_val_rec         OE_Blanket_PUB.Header_Val_Rec_Type;
   l_line_tbl               OE_Blanket_PUB.line_tbl_Type;
   l_line_val_tbl           OE_Blanket_PUB.line_Val_tbl_Type;
   l_line_rec               OE_Blanket_PUB.line_rec_Type;
   l_line_val_rec           OE_Blanket_PUB.line_val_rec_Type;
   l_control_rec            OE_Blanket_PUB.Control_rec_type;
 
 -- Output Variables

   x_header_rec             OE_Blanket_PUB.header_rec_type;
   x_line_tbl                OE_Blanket_PUB.line_tbl_Type;
   x_return_status          VARCHAR2(1000);
   x_msg_count              NUMBER;
   x_msg_data               VARCHAR2(4000);
 
   l_msg_index_out      NUMBER;
   l_error_message      VARCHAR2 (100);

begin
   
    l_line_rec.order_number:='351015';
    l_line_rec.header_id :=5903558;
    l_line_rec.attribute9:='TEST';
 
    OE_Blanket_PUB.Process_Blanket(

p_org_id             => 1

,p_operating_unit     => NULL

,p_api_version_number => 1.0

,x_return_status      => x_return_status

,x_msg_count          => x_msg_count

,x_msg_data           => x_msg_data

,p_header_rec         => l_hdr_rec

,p_header_val_rec     => l_hdr_val_rec

,p_line_tbl           => l_line_tbl

,p_line_val_tbl       => l_line_val_tbl

,p_control_rec        => l_control_rec

,x_header_rec         => x_header_rec

,x_line_tbl           => x_line_tbl
);

IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('success');
      DBMS_OUTPUT.put_line ('x_msg_count = ' || x_msg_count);
      DBMS_OUTPUT.put_line ('x_return_status = ' || x_return_status);
   ELSE
    IF x_msg_count > 0
      THEN
         FOR i IN 1 .. x_msg_count
         LOOP
            apps.fnd_msg_pub.get (p_msg_index          => i,
                                  p_encoded            => fnd_api.g_false,
                                  p_data               => x_msg_data,
                                  p_msg_index_out      => l_msg_index_out
                                 );
         END LOOP;

         IF l_error_message IS NULL
         THEN
            l_error_message := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message :=
                       l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;

         DBMS_OUTPUT.put_line ('*****************************************');
         DBMS_OUTPUT.put_line ('API Error: ' || l_error_message);
         DBMS_OUTPUT.put_line ('*****************************************');
      END IF;
   END IF;
END;

SUPPLIER API TO UPDATE PAYMENT PRIORITY

PROCEDURE mutl_supplier_updation
   IS
      l_vendor_rec         ap_vendor_pub_pkg.r_vendor_rec_type;
      l_return_status      VARCHAR2 (10);
      l_msg_count          NUMBER;
      l_msg_data           VARCHAR2 (1000);
      l_vendor_id          NUMBER;
      l_party_id           NUMBER;
      l_payment_priority   NUMBER;
      l_supplier_name      VARCHAR2 (255);
      l_error_message      VARCHAR2 (100);
      l_msg_index_out      NUMBER;
      err_code             NUMBER;
      err_msg              VARCHAR2 (1000);

      CURSOR cur_payment
      IS
         SELECT supplier_name, vendor_id, payment_priority
           FROM xx_supplier1 where status is null;
   BEGIN
      FOR supplier_rec IN cur_payment
      LOOP
         l_supplier_name := supplier_rec.supplier_name;
         l_vendor_id := supplier_rec.vendor_id;
         l_payment_priority := supplier_rec.payment_priority;
         DBMS_OUTPUT.put_line ('supplier_name' || l_supplier_name);
         DBMS_OUTPUT.put_line ('vendor_id' || l_vendor_id);
         DBMS_OUTPUT.put_line ('payment_priority' || l_payment_priority);
         --update vendor payment priority value
         l_vendor_rec.vendor_id := l_vendor_id;
         l_vendor_rec.payment_priority := l_payment_priority;
         ap_vendor_pub_pkg.update_vendor_public
                                       (p_api_version        => 1,
                                        x_return_status      => l_return_status,
                                        x_msg_count          => l_msg_count,
                                        x_msg_data           => l_msg_data,
                                        p_vendor_rec         => l_vendor_rec,
                                        p_vendor_id          => l_vendor_rec.vendor_id
                                       );
         DBMS_OUTPUT.put_line ('return_status: ' || l_return_status);
         DBMS_OUTPUT.put_line ('msg_data: ' || l_msg_data);
     --program successful then update the status 'S'
         IF l_return_status = fnd_api.g_ret_sts_success
         THEN
            UPDATE xx_supplier1
               SET status = 'S'
             WHERE vendor_id = l_vendor_id;

            DBMS_OUTPUT.put_line ('success');
         ELSE
            IF l_msg_count > 0
            THEN
               FOR i IN 1 .. l_msg_count
               LOOP
                  apps.fnd_msg_pub.get (p_msg_index          => i,
                                        p_encoded            => fnd_api.g_false,
                                        p_data               => l_msg_data,
                                        p_msg_index_out      => l_msg_index_out
                                       );

                  ---Find Error Message
                  IF l_error_message IS NULL
                  THEN
                     l_error_message := SUBSTR (l_msg_data, 1, 250);
                  ELSE
                     l_error_message :=
                        l_error_message || ' /'
                        || SUBSTR (l_msg_data, 1, 250);
                  END IF;

                  DBMS_OUTPUT.put_line
                                  ('*****************************************');
                  DBMS_OUTPUT.put_line ('API Error: ' || l_error_message);
                  DBMS_OUTPUT.put_line
                                  ('*****************************************');
               END LOOP;

               ---update status and error message
           
                  UPDATE xx_supplier1
                     SET error_message = l_error_message,
                         status = l_return_status
                   WHERE vendor_id = l_vendor_id;
           
            END IF;
         END IF;
      END LOOP;
      ---Find the error message and error code
   EXCEPTION
      WHEN OTHERS
      THEN
         err_code := SQLCODE;
         err_msg := SUBSTR (SQLERRM, 1, 200);
         DBMS_OUTPUT.put_line (   'Error code'
                               || err_code
                               || ':'
                               || 'Error message'
                               || err_msg
                              );
   END mutl_supplier_updation;

PL/SQL - PROCEDURE IN /OUT PARAMETER

create or replace procedure xx1
(a in out number)
is
begin
     a:=a+4;
end;

OUTPUT :
  declare
      a number:=&a;
begin
     xx1(a);
     dbms_output.put_line('the value is' || a);
end;

  Enter number 6
    the value is10

PL/SQL Procedure Basics: How to Create and Use Procedures

create or replace procedure proc1
is
  l_id integer;
begin
  select employee_id
  into l_id
  from employees
  where first_name = 'Suriya Parithy';
  dbms_output.put_line(l_id);
end;

exec proc1;