PL/SQL

Cursor Attributes

There are five cursor attributes:
%isopen

%found

%notfound

%rowcount

%bulk_rowcount

------------------------------------------------------------------------------
%isopen
With %isopen it is possible to test whether a cursor was opened:

declare 
      cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
      l_name
begin
      open cur_emp;
      fetch cur_emp  into  l_name;
      if cur_emp%isopen
      then
            log("Cursor is Open")
      end if;
      close cur;
end;

If there are records in EMP table then the message "Cursor is Open" will be logged at the terminal.

------------------------------------------------------------------------------
%found returns true when the last fetch operation on the cursor fetched a row.
%notfound returns true when the last fetch operation on the cursor did not fetch a row.

declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;
begin
       open cur_emp;
       Loop
       if cur_emp%found
       then
              log("Record returned");        
       elsif cur_emp%notfound
              log("No records");
       exit;          
       end if;
       close cur;
end;

If there are 5 records in EMP TABLE then you will get "Record returned" message five times and then
loop will exit logging the message "No records".
%NOTFOUND is mostly used to exit the LOOP for cursor after the last record has been fetched/processed.

---------------------------------------------------
%rowcount returns the number of rows that have been fetched so far

example;
declare 
        cursor cur_emp is
        SELECT NAME
        FROM   EMP;       
begin
       open cur_emp;
       loop
       if cur%found then     
                    dbms_output.put_line(cur%rowcount);
      else
                    exit;
      end if;
      end loop;
end;

%bulk_rowcount is similar to %rowcount, but is used in bulk collects.

Count a specific character occurence in a String

In PLSQL there are functions like INSTR, SUBSTR, REPLACE, but
to Count number of occurrences of a character in the string there is no standard function.

Here is a simple way, In the example below I am trying to find out the number of
occurrence of '|' in the string

select length('san|tom|elp|ert|') -
length(replace('san|tom|elp|ert|','|')) count from dual;

PLSQL Collections

A collection in PLSQL helps to achieve the Array kind of Programming. Its an ordered group of elements, all of the same type. In a collection an element has a unique subscript that determines its position in the collection.

PLSQL has 3 types of collections
    Index-by tables
    Varrays
    Nested Tables

Declaration: 

Nested tables
TYPE type_name IS TABLE OF element_type ;
e.g.
TYPE Books IS TABLE OF VARCHAR2(60);

Varrays
TYPE type_name IS VARRAY(size_limit) OF element_type;
e.g.
TYPE Books IS VARRAY(100) OF VARCHAR2(60);

Index-by tables
TYPE type_name IS TABLE OF element_type
   INDEX BY BINARY_INTEGER ;
  
TYPE Books IS TABLE OF VARCHAR2(60)
   INDEX BY VARCHAR2(60) ;
  
Varrays size is fixed at the time of declaration, however the size of Nested tables and Index-by tables is dynamic.


Initialization:
Nested Tables
Declare
    TYPE Books IS TABLE OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Varrays
Declare
    TYPE Books IS varray(3) OF VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Index by Table
Declare
    TYPE Books IS table OF VARCHAR2(60)
    inde by VARCHAR2(60);   
    c_book  BOOKS;

begin
     c_book := Books('Book1', 'Book2');
     
end;

Using collections in PLSQL.
The best way to implement collections is to use them in For Loops or Bulk collects.
FORALL and BULK COLLECTS can be used for implementing collections in better way

BULK COLLECT tell the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine. You can use these keywords in the SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is the syntax:

... BULK COLLECT INTO collection_name[, collection_name] ...

e.g.
DECLARE
   TYPE empntab IS TABLE OF emp.empno%TYPE;
   TYPE enameTab IS TABLE OF emp.ename%TYPE;
   empnum empntab;  -- no need to initialize
   enames enameTab;
BEGIN
   SELECT empno, ename BULK COLLECT INTO empnum, enames FROM emp;
   ...
END;

FORALL
Its used along with collections to perform SQL operations such as Insert, Updates and Deletes.
We can say its spec ail loop to perform Mass operations, a shortcut to implement FOR Loops

e.g.
DECLARE
   TYPE BOOKS IS VARRAY(10) OF VARCHAR2(60);
   var_book BOOKS := BOOKS('Book1','Book2','Book3','Book4','Book5');
BEGIN
   FORALL i IN 1..7  -- bulk-bind only part of varray
      UPDATE BOOK_TAB SET CODE = 1111+1 WHERE NAME = var_book(i);
END;

Other types
RECORD:
TYPE type_name IS RECORD (colname type1, colname2 type2);

e.g.
DECLARE
   TYPE EMPREC IS RECORD (
      EMPNUM    VARCHAR2(60),
      NAME      VARCHAR2(120));
     
      EMP1 EMPREC;
BEGIN
   ...
END;

Collection Methods:

A variety of methods/Functions exist for collections, these can be used to make the implementation
of collection more effective

    EXISTS(n) - Returns TRUE if the specified element exists.

    COUNT - Returns the number of elements in the collection.

    LIMIT - Returns the maximum number of elements for a VARRAY, or NULL for nested tables.

    FIRST - Returns the index of the first element in the collection.

    LAST - Returns the index of the last element in the collection.

    PRIOR(n) - Returns the index of the element prior to the specified element.

    NEXT(n) - Returns the index of the next element after the specified element.

    EXTEND - Appends a single NULL element to the collection.

    EXTEND(n) - Appends n NULL elements to the collection.

    EXTEND(n1,n2) - Appends n1 copies of the n2th element to the collection.

    TRIM - Removes a single element from the end of the collection.

    TRIM(n) - Removes n elements from the end of the collection.

    DELETE - Removes all elements from the collection.

    DELETE(n) - Removes element n from the collection.

    DELETE(n1,n2) - Removes all elements from n1 to n2 from the collection.

PO RECEIPT OPEN INTERFACE SCRIPT

PO RECEIPT OPEN INTERFACE SCRIPT

DECLARE
   l_reqid                     NUMBER;
   l_formatconversion          DATE;
   l_validate_flag             VARCHAR2 (1000);
   l_validate_msg              VARCHAR2 (1000);
   l_actionvalidate_msg        VARCHAR2 (1000);
   l_actionvalidate_flag       VARCHAR2 (1000);
   d_actionvalidate_msg        VARCHAR2 (1000);
   d_actionvalidate_flag       VARCHAR2 (1000);
   d_actionvalidate_value      DATE;
   l_sqlerrm                   VARCHAR2 (2000);
   lv_rowid                    VARCHAR2 (1000);
   l_condt_flag                VARCHAR2 (10);
   l_interfaceflag             VARCHAR2 (10);
   l_h_iface_header_id         NUMBER;
   l_h_group_id                NUMBER;
   l_h_validation_flag         VARCHAR2 (2000);
   l_h_last_update_date        DATE;
   l_h_creation_date           DATE;
   l_h_created_by              NUMBER;
   l_h_last_updated_by         NUMBER;
   l_h_processing_status       VARCHAR2 (2000);
   l_h_transaction_type        VARCHAR2 (2000);
   l_h_vendor_name             VARCHAR2 (2000);
   l_h_auto_trx_code           VARCHAR2 (2000);
   l_h_receipt_source          VARCHAR2 (2000);
   l_h_ship_to_organization    VARCHAR2 (2000);
   l_h_expected_receipt_date   DATE;
   l_h_reference               VARCHAR2 (2000);
   l_h_waybill_airbill         VARCHAR2 (2000);
   l_h_bill_of_lading          VARCHAR2 (2000);
   l_l_iface_header_id         NUMBER;
   l_l_group_id                NUMBER;
   l_l_iface_transaction_id    NUMBER;
   l_l_last_updated_by         NUMBER;
   l_l_created_by              NUMBER;
   l_l_creation_date           DATE;
   l_l_last_update_date        DATE;
   l_l_po_line_id              NUMBER;
   l_l_validation_flag         VARCHAR2 (2000);
   l_l_shipment_hdr_id         NUMBER;
   l_l_location                VARCHAR2 (2000);
   l_l_processing_status       VARCHAR2 (2000);
   l_l_transaction_status      VARCHAR2 (2000);
   l_l_processing_mode         VARCHAR2 (2000);
   l_l_line_location           VARCHAR2 (2000);
   l_l_auto_trx_code           VARCHAR2 (2000);
   l_l_source_document         VARCHAR2 (2000);
   l_l_transaction_type        VARCHAR2 (2000);
   l_l_transaction_date        DATE;
   l_l_quantity                NUMBER;
   l_l_uom                     VARCHAR2 (2000);
   l_l_item_num                VARCHAR2 (2000);
   l_l_to_organization         VARCHAR2 (2000);
   l_l_item_description        VARCHAR2 (2000);
   l_l_shipment_num            VARCHAR2 (2000);
   l_l_ship_to_location        VARCHAR2 (2000);
   l_l_po_number               VARCHAR2 (2000);
   l_l_destination_type        VARCHAR2 (2000);
   l_l_deliver_to_location     VARCHAR2 (2000);
   l_l_subinventory            VARCHAR2 (2000);
   l_l_reference               VARCHAR2 (2000);
   l_l_po_line_num             NUMBER;
   l_l_category                VARCHAR2 (2000);
   l_l_project                 VARCHAR2 (2000);

   CURSOR rcv_hdr
   IS
      SELECT DISTINCT rcv_hdr_po_receipt5.ROWID,
                      rcv_hdr_po_receipt5.oal_exec_id,
                      rcv_hdr_po_receipt5.oal_loader_id,
                      rcv_hdr_po_receipt5.oal_line_no,
                      rcv_hdr_po_receipt5.h_iface_header_id,
                      rcv_hdr_po_receipt5.h_group_id,
                      rcv_hdr_po_receipt5.h_validation_flag,
                      rcv_hdr_po_receipt5.h_last_update_date,
                      rcv_hdr_po_receipt5.h_creation_date,
                      rcv_hdr_po_receipt5.h_created_by,
                      rcv_hdr_po_receipt5.h_last_updated_by,
                      rcv_hdr_po_receipt5.h_processing_status,
                      rcv_hdr_po_receipt5.h_transaction_type,
                      rcv_hdr_po_receipt5.h_vendor_name,
                      rcv_hdr_po_receipt5.h_auto_trx_code,
                      rcv_hdr_po_receipt5.h_receipt_source,
                      rcv_hdr_po_receipt5.h_ship_to_organization,
                      rcv_hdr_po_receipt5.h_expected_receipt_date,
                      rcv_hdr_po_receipt5.h_reference,
                      rcv_hdr_po_receipt5.h_waybill_airbill,
                      rcv_hdr_po_receipt5.h_bill_of_lading
                 FROM rcv_hdr_po_receipt5@sunr12toapp_222_qa
                WHERE NVL (rcv_hdr_po_receipt5.status_code, 'NULL') LIKE
                                                  NVL (p_status_code, 'NULL');

   CURSOR rcv_trx (p_h_reference VARCHAR2)
   IS
      SELECT DISTINCT rcv_trx_po_receipt5.ROWID,
                      rcv_trx_po_receipt5.oal_exec_id,
                      rcv_trx_po_receipt5.oal_loader_id,
                      rcv_trx_po_receipt5.oal_line_no,
                      rcv_trx_po_receipt5.l_deliver_to_location,
                      rcv_trx_po_receipt5.l_iface_header_id,
                      rcv_trx_po_receipt5.l_group_id,
                      rcv_trx_po_receipt5.l_iface_transaction_id,
                      rcv_trx_po_receipt5.l_last_updated_by,
                      rcv_trx_po_receipt5.l_created_by,
                      rcv_trx_po_receipt5.l_creation_date,
                      rcv_trx_po_receipt5.l_last_update_date,
                      rcv_trx_po_receipt5.l_po_line_id,
                      rcv_trx_po_receipt5.l_validation_flag,
                      rcv_trx_po_receipt5.l_shipment_hdr_id,
                      rcv_trx_po_receipt5.l_location,
                      rcv_trx_po_receipt5.l_processing_status,
                      rcv_trx_po_receipt5.l_transaction_status,
                      rcv_trx_po_receipt5.l_processing_mode,
                      rcv_trx_po_receipt5.l_line_location,
                      rcv_trx_po_receipt5.l_auto_trx_code,
                      rcv_trx_po_receipt5.l_source_document,
                      rcv_trx_po_receipt5.l_transaction_type,
                      rcv_trx_po_receipt5.l_transaction_date,
                      rcv_trx_po_receipt5.l_quantity,
                      rcv_trx_po_receipt5.l_uom,
                      rcv_trx_po_receipt5.l_item_num,
                      rcv_trx_po_receipt5.l_to_organization,
                      rcv_trx_po_receipt5.l_item_description,
                      rcv_trx_po_receipt5.l_shipment_num,
                      rcv_trx_po_receipt5.l_ship_to_location,
                      rcv_trx_po_receipt5.l_po_number,
                      rcv_trx_po_receipt5.l_destination_type,
                      rcv_trx_po_receipt5.l_subinventory,
                      rcv_trx_po_receipt5.l_reference,
                      rcv_trx_po_receipt5.l_po_line_num,
                      rcv_trx_po_receipt5.l_category,
                      rcv_trx_po_receipt5.l_project
                 FROM rcv_trx_po_receipt5@sunr12toapp_222_qa
                WHERE NVL (rcv_trx_po_receipt5.status_code, 'NULL') LIKE
                                                  NVL (p_status_code, 'NULL')
                  AND NVL (rcv_trx_po_receipt5.l_reference, '-123') =
                                                   NVL (p_h_reference, '-123');

   PROCEDURE validate_query_new (
      qry             IN       VARCHAR2,
      validate_flag   OUT      VARCHAR2,
      validate_msg    OUT      VARCHAR2
   )
   IS
      l_qry               VARCHAR2 (5000);
      column_names        VARCHAR2 (1000);
      multi_column_flag   NUMBER (10)     := 0;
      l_tname             VARCHAR2 (1000);
      l_datatype          VARCHAR2 (1000);
      l_start             NUMBER;
      l_end               NUMBER;
   BEGIN
      l_qry := qry;
      validate_flag := 'S';
      validate_msg := NULL;
      column_names := SUBSTR (qry, 8, INSTR (UPPER (qry), 'FROM', 1) - 8);
      multi_column_flag := INSTR (REPLACE (column_names, '*', ','), ',', 1);

      IF multi_column_flag <> 0
      THEN
         validate_flag := 'F';
         validate_msg := 'Multi Column';
      ELSE
         l_start := (INSTR (UPPER (qry), 'FROM', 1) + 5);
         l_end := INSTR (UPPER (qry), 'WHERE', 1);

         IF (l_end > 0)
         THEN
            l_tname := SUBSTR (qry, l_start, (l_end - l_start));
         ELSE
            l_tname := SUBSTR (qry, l_start);
         END IF;

         IF NVL (INSTR (l_tname, ' '), 0) = 0
         THEN
            BEGIN
               SELECT data_type
                 INTO l_datatype
                 FROM all_tab_columns
                WHERE table_name = UPPER (TRIM (l_tname))
                  AND column_name = UPPER (TRIM (column_names));
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
               WHEN OTHERS
               THEN
                  NULL;
            END;
         END IF;

         IF l_datatype LIKE 'DATE'
         THEN
            l_qry :=
                  'SELECT TO_CHAR('
               || column_names
               || ',''DD-MON-RRRR HH24:MI:SS'') '
               || SUBSTR (qry, INSTR (UPPER (qry), 'FROM'));
         END IF;

         BEGIN
            EXECUTE IMMEDIATE l_qry
                         INTO validate_msg;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               validate_flag := 'S';
               validate_msg := NULL;
            WHEN TOO_MANY_ROWS
            THEN
               BEGIN
                  EXECUTE IMMEDIATE    'SELECT * FROM ('
                                    || l_qry
                                    || ') WHERE ROWNUM = 1'
                               INTO validate_msg;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     validate_flag := 'F';
                     validate_msg := 'Err in Too Many Rows...' || SQLERRM;
               END;
            WHEN OTHERS
            THEN
               validate_flag := 'F';
               validate_msg := 'Err in Qry : ' || SQLERRM;
         END;
      END IF;
   END validate_query_new;

   PROCEDURE validate_query_date (
      qry                  IN       VARCHAR2,
      date_validate_flag   OUT      VARCHAR2,
      date_validate_msg    OUT      VARCHAR2,
      date_validate_msg1   OUT      DATE
   )
   IS
      l_qry               VARCHAR2 (5000);
      column_names        VARCHAR2 (1000);
      multi_column_flag   NUMBER (10)     := 0;
      l_tname             VARCHAR2 (1000);
      l_datatype          VARCHAR2 (1000);
      l_start             NUMBER;
      l_end               NUMBER;
   BEGIN
      l_qry := qry;
      date_validate_flag := 'S';
      date_validate_msg := NULL;
      date_validate_msg1 := NULL;
      column_names := SUBSTR (qry, 8, INSTR (UPPER (qry), 'FROM', 1) - 8);
      multi_column_flag := INSTR (REPLACE (column_names, '*', ','), ',', 1);

      IF multi_column_flag <> 0
      THEN
         date_validate_flag := 'F';
         date_validate_msg := 'Multi Column';
      ELSE
         l_start := (INSTR (UPPER (qry), 'FROM', 1) + 5);
         l_end := INSTR (UPPER (qry), 'WHERE', 1);

         IF (l_end > 0)
         THEN
            l_tname := SUBSTR (qry, l_start, (l_end - l_start));
         ELSE
            l_tname := SUBSTR (qry, l_start);
         END IF;

         IF NVL (INSTR (l_tname, ' '), 0) = 0
         THEN
            BEGIN
               SELECT data_type
                 INTO l_datatype
                 FROM all_tab_columns
                WHERE table_name = UPPER (TRIM (l_tname))
                  AND column_name = UPPER (TRIM (column_names));
            EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
               WHEN OTHERS
               THEN
                  NULL;
            END;
         END IF;

         BEGIN
            EXECUTE IMMEDIATE l_qry
                         INTO date_validate_msg1;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               date_validate_flag := 'S';
               date_validate_msg1 := NULL;
            WHEN TOO_MANY_ROWS
            THEN
               BEGIN
                  EXECUTE IMMEDIATE    'SELECT * FROM ('
                                    || l_qry
                                    || ') WHERE ROWNUM = 1'
                               INTO date_validate_msg1;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     date_validate_flag := 'F';
                     date_validate_msg :=
                                         'Err in Too Many Rows...' || SQLERRM;
               END;
            WHEN OTHERS
            THEN
               date_validate_flag := 'F';
               date_validate_msg := 'Err in Qry : ' || SQLERRM;
         END;
      END IF;
   END validate_query_date;
BEGIN
   apps.fnd_global.apps_initialize (##ebs_user_id##,
                                    ##ebs_resp_id##,
                                    ##ebs_appl_id##,
                                    NULL,
                                    NULL
                                   );

   FOR cur_rcv_hdr IN rcv_hdr
   LOOP
      l_h_iface_header_id := cur_rcv_hdr.h_iface_header_id;

      IF (l_h_iface_header_id IS NULL)
      THEN
         BEGIN
            validate_query_new
                         ('SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL FROM DUAL',
                          l_actionvalidate_flag,
                          l_actionvalidate_msg
                         );
         END;

         IF l_actionvalidate_flag = 'F'
         THEN
            l_actionvalidate_msg := NULL;
         END IF;

         l_h_iface_header_id := l_actionvalidate_msg;
         cur_rcv_hdr.h_iface_header_id := l_actionvalidate_msg;
      END IF;

      l_h_group_id := cur_rcv_hdr.h_group_id;

      IF (l_h_group_id IS NULL)
      THEN
         BEGIN
            validate_query_new
                          ('SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL FROM DUAL',
                           l_actionvalidate_flag,
                           l_actionvalidate_msg
                          );
         END;

         IF l_actionvalidate_flag = 'F'
         THEN
            l_actionvalidate_msg := NULL;
         END IF;

         l_h_group_id := l_actionvalidate_msg;
         cur_rcv_hdr.h_group_id := l_actionvalidate_msg;
      END IF;

      l_h_validation_flag := cur_rcv_hdr.h_validation_flag;

      IF (l_h_validation_flag IS NULL)
      THEN
         l_h_validation_flag := 'Y';
      END IF;

      l_h_last_update_date := cur_rcv_hdr.h_last_update_date;

      IF (l_h_last_update_date IS NULL)
      THEN
         BEGIN
            validate_query_date ('SELECT SYSDATE FROM DUAL',
                                 d_actionvalidate_flag,
                                 d_actionvalidate_msg,
                                 d_actionvalidate_value
                                );
         END;

         IF d_actionvalidate_flag = 'F'
         THEN
            d_actionvalidate_value := NULL;
         END IF;

         l_h_last_update_date := d_actionvalidate_value;
         cur_rcv_hdr.h_last_update_date := d_actionvalidate_value;
      END IF;

      l_h_creation_date := cur_rcv_hdr.h_creation_date;

      IF (l_h_creation_date IS NULL)
      THEN
         BEGIN
            validate_query_date ('SELECT SYSDATE FROM DUAL',
                                 d_actionvalidate_flag,
                                 d_actionvalidate_msg,
                                 d_actionvalidate_value
                                );
         END;

         IF d_actionvalidate_flag = 'F'
         THEN
            d_actionvalidate_value := NULL;
         END IF;

         l_h_creation_date := d_actionvalidate_value;
         cur_rcv_hdr.h_creation_date := d_actionvalidate_value;
      END IF;

      l_h_created_by := cur_rcv_hdr.h_created_by;

      IF (l_h_created_by IS NULL)
      THEN
         l_h_created_by := fnd_global.user_id;
      END IF;

      l_h_last_updated_by := cur_rcv_hdr.h_last_updated_by;

      IF (l_h_last_updated_by IS NULL)
      THEN
         l_h_last_updated_by := fnd_global.user_id;
      END IF;

      l_h_processing_status := cur_rcv_hdr.h_processing_status;

      IF (l_h_processing_status IS NULL)
      THEN
         l_h_processing_status := 'PENDING';
      END IF;

      l_h_transaction_type := cur_rcv_hdr.h_transaction_type;

      IF (l_h_transaction_type IS NULL)
      THEN
         l_h_transaction_type := 'NEW';
      END IF;

      l_h_vendor_name := cur_rcv_hdr.h_vendor_name;

      IF (l_h_vendor_name IS NOT NULL)
      THEN
         BEGIN
            validate_query_new
               (   'SELECT VENDOR_ID FROM PO_VENDORS WHERE ENABLED_FLAG = ''Y'' AND ((END_DATE_ACTIVE IS NULL) 
OR (END_DATE_ACTIVE > SYSDATE)) AND VENDOR_NAME = '''
                || cur_rcv_hdr.h_vendor_name
                || '''',
                l_actionvalidate_flag,
                l_actionvalidate_msg
               );
         END;

         IF l_actionvalidate_flag = 'F'
         THEN
            l_actionvalidate_msg := NULL;
         END IF;

         l_h_vendor_name := l_actionvalidate_msg;
         cur_rcv_hdr.h_vendor_name := l_actionvalidate_msg;
      END IF;

      l_h_auto_trx_code := cur_rcv_hdr.h_auto_trx_code;

      IF (l_h_auto_trx_code IS NOT NULL)
      THEN
         BEGIN
            validate_query_new
               (   'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''AUTO TRANSACT CODE'' AND ENABLED_FLAG = ''Y'' 
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
                || cur_rcv_hdr.h_auto_trx_code
                || '''',
                l_actionvalidate_flag,
                l_actionvalidate_msg
               );
         END;

         IF l_actionvalidate_flag = 'F'
         THEN
            l_actionvalidate_msg := NULL;
         END IF;

         l_h_auto_trx_code := l_actionvalidate_msg;
         cur_rcv_hdr.h_auto_trx_code := l_actionvalidate_msg;
      END IF;

      l_h_receipt_source := cur_rcv_hdr.h_receipt_source;

      IF (l_h_receipt_source IS NOT NULL)
      THEN
         BEGIN
            validate_query_new
               (   'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''SHIPMENT SOURCE TYPE'' 
AND ENABLED_FLAG = ''Y'' AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE))
AND DISPLAYED_FIELD = '''
                || cur_rcv_hdr.h_receipt_source
                || '''',
                l_actionvalidate_flag,
                l_actionvalidate_msg
               );
         END;

         IF l_actionvalidate_flag = 'F'
         THEN
            l_actionvalidate_msg := NULL;
         END IF;

         l_h_receipt_source := l_actionvalidate_msg;
         cur_rcv_hdr.h_receipt_source := l_actionvalidate_msg;
      END IF;

      l_h_ship_to_organization := cur_rcv_hdr.h_ship_to_organization;

      IF (l_h_ship_to_organization IS NOT NULL)
      THEN
         BEGIN
            validate_query_new
               (   'SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = '''
                || cur_rcv_hdr.h_ship_to_organization
                || ''' AND ((DISABLE_DATE IS NULL) 
OR (DISABLE_DATE > SYSDATE)) AND OPERATING_UNIT = ##EBS_ORG_ID##',
                l_actionvalidate_flag,
                l_actionvalidate_msg
               );
         END;

         IF l_actionvalidate_flag = 'F'
         THEN
            l_actionvalidate_msg := NULL;
         END IF;

         l_h_ship_to_organization := l_actionvalidate_msg;
         cur_rcv_hdr.h_ship_to_organization := l_actionvalidate_msg;
      END IF;

      l_h_expected_receipt_date := cur_rcv_hdr.h_expected_receipt_date;
      l_h_reference := cur_rcv_hdr.h_reference;
      l_h_waybill_airbill := cur_rcv_hdr.h_waybill_airbill;
      l_h_bill_of_lading := cur_rcv_hdr.h_bill_of_lading;

      BEGIN
         INSERT INTO rcv_headers_interface
                     (header_interface_id, GROUP_ID,
                      validation_flag, last_update_date,
                      creation_date, created_by,
                      last_updated_by, processing_status_code,
                      transaction_type, vendor_id,
                      auto_transact_code, receipt_source_code,
                      ship_to_organization_id, expected_receipt_date,
                      attribute15, waybill_airbill_num, bill_of_lading
                     )
              VALUES (l_h_iface_header_id, l_h_group_id,
                      l_h_validation_flag, l_h_last_update_date,
                      l_h_creation_date, l_h_created_by,
                      l_h_last_updated_by, l_h_processing_status,
                      l_h_transaction_type, l_h_vendor_name,
                      l_h_auto_trx_code, l_h_receipt_source,
                      l_h_ship_to_organization, l_h_expected_receipt_date,
                      l_h_reference, l_h_waybill_airbill, l_h_bill_of_lading
                     );

         BEGIN
            SELECT DISTINCT ROWID
                       INTO lv_rowid
                       FROM rcv_headers_interface
                      WHERE attribute15 = l_h_reference;
         EXCEPTION
            WHEN TOO_MANY_ROWS
            THEN
               DBMS_OUTPUT.put_line ('Err Msg : ' || 'row id is not available'
                                    );
         END;

         INSERT INTO oal_line_mapping@sunr12toapp_222_qa
                     (oal_exec_id, oal_line_no,
                      oal_row_id, oal_loader_id,
                      interface_table_name
                     )
              VALUES (cur_rcv_hdr.oal_exec_id, cur_rcv_hdr.oal_line_no,
                      lv_rowid, cur_rcv_hdr.oal_loader_id,
                      'RCV_HEADERS_INTERFACE'
                     );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Err Msg 5 lv_rowid: ' || lv_rowid);
            l_sqlerrm := SQLERRM;

            UPDATE rcv_hdr_po_receipt5@sunr12toapp_222_qa
               SET error_message = l_sqlerrm,
                   status_code = 'E'
             WHERE oal_line_no = cur_rcv_hdr.oal_line_no
               AND oal_exec_id = cur_rcv_hdr.oal_exec_id;
      END;

      IF l_sqlerrm IS NULL
      THEN
         l_interfaceflag := 'Y';
      ELSE
         l_interfaceflag := 'N';
      END IF;

      UPDATE rcv_hdr_po_receipt5@sunr12toapp_222_qa
         SET int_exec_success_flag = l_interfaceflag
       WHERE oal_line_no = cur_rcv_hdr.oal_line_no
         AND oal_exec_id = cur_rcv_hdr.oal_exec_id;

      FOR cur_rcv_trx IN rcv_trx (cur_rcv_hdr.h_reference)
      LOOP
         l_l_iface_header_id := cur_rcv_trx.l_iface_header_id;

         IF (l_l_iface_header_id IS NULL)
         THEN
            BEGIN
               validate_query_new
                         ('SELECT RCV_HEADERS_INTERFACE_S.CURRVAL FROM DUAL',
                          l_actionvalidate_flag,
                          l_actionvalidate_msg
                         );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_iface_header_id := l_actionvalidate_msg;
            cur_rcv_trx.l_iface_header_id := l_actionvalidate_msg;
         END IF;

         l_l_group_id := cur_rcv_trx.l_group_id;

         IF (l_l_group_id IS NULL)
         THEN
            BEGIN
               validate_query_new
                          ('SELECT RCV_INTERFACE_GROUPS_S.CURRVAL FROM DUAL',
                           l_actionvalidate_flag,
                           l_actionvalidate_msg
                          );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_group_id := l_actionvalidate_msg;
            cur_rcv_trx.l_group_id := l_actionvalidate_msg;
         END IF;

         l_l_iface_transaction_id := cur_rcv_trx.l_iface_transaction_id;

         IF (l_l_iface_transaction_id IS NULL)
         THEN
            BEGIN
               validate_query_new
                    ('SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL FROM DUAL',
                     l_actionvalidate_flag,
                     l_actionvalidate_msg
                    );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_iface_transaction_id := l_actionvalidate_msg;
            cur_rcv_trx.l_iface_transaction_id := l_actionvalidate_msg;
         END IF;

         l_l_last_updated_by := cur_rcv_trx.l_last_updated_by;

         IF (l_l_last_updated_by IS NULL)
         THEN
            l_l_last_updated_by := fnd_global.user_id;
         END IF;

         l_l_created_by := cur_rcv_trx.l_created_by;

         IF (l_l_created_by IS NULL)
         THEN
            l_l_created_by := fnd_global.user_id;
         END IF;

         l_l_creation_date := cur_rcv_trx.l_creation_date;

         IF (l_l_creation_date IS NULL)
         THEN
            BEGIN
               validate_query_date ('SELECT SYSDATE FROM DUAL',
                                    d_actionvalidate_flag,
                                    d_actionvalidate_msg,
                                    d_actionvalidate_value
                                   );
            END;

            IF d_actionvalidate_flag = 'F'
            THEN
               d_actionvalidate_value := NULL;
            END IF;

            l_l_creation_date := d_actionvalidate_value;
            cur_rcv_trx.l_creation_date := d_actionvalidate_value;
         END IF;

         l_l_last_update_date := cur_rcv_trx.l_last_update_date;

         IF (l_l_last_update_date IS NULL)
         THEN
            BEGIN
               validate_query_date ('SELECT SYSDATE FROM DUAL',
                                    d_actionvalidate_flag,
                                    d_actionvalidate_msg,
                                    d_actionvalidate_value
                                   );
            END;

            IF d_actionvalidate_flag = 'F'
            THEN
               d_actionvalidate_value := NULL;
            END IF;

            l_l_last_update_date := d_actionvalidate_value;
            cur_rcv_trx.l_last_update_date := d_actionvalidate_value;
         END IF;

         l_l_po_line_id := cur_rcv_trx.l_po_line_id;

         IF (l_l_po_line_id IS NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT POL.PO_LINE_ID FROM PO_LINES_ALL POL, MTL_PARAMETERS MP, PO_HEADERS_ALL POH WHERE POH.PO_HEADER_ID = POL.PO_HEADER_ID 
AND POH.ORG_ID = POL.ORG_ID AND MP.ORGANIZATION_CODE = '''
                   || cur_rcv_trx.l_to_organization
                   || ''' AND POL.ORG_ID = ##EBS_ORG_ID## AND POH.SEGMENT1 = '''
                   || cur_rcv_trx.l_po_number
                   || ''' AND POL.LINE_NUM = '''
                   || cur_rcv_trx.l_po_line_num
                   || ''' AND POH.ENABLED_FLAG = ''Y'' AND POH.AUTHORIZATION_STATUS = ''APPROVED'' AND ((POH.END_DATE IS NULL) OR (POH.END_DATE > SYSDATE)) AND POH.TYPE_LOOKUP_CODE NOT IN (''RFQ'', ''QUOTATION'')',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_po_line_id := l_actionvalidate_msg;
            cur_rcv_trx.l_po_line_id := l_actionvalidate_msg;
         END IF;

         l_l_validation_flag := cur_rcv_trx.l_validation_flag;

         IF (l_l_validation_flag IS NULL)
         THEN
            l_l_validation_flag := 'Y';
         END IF;

         l_l_shipment_hdr_id := cur_rcv_trx.l_shipment_hdr_id;
         l_l_location := cur_rcv_trx.l_location;
         l_l_processing_status := cur_rcv_trx.l_processing_status;

         IF (l_l_processing_status IS NULL)
         THEN
            l_l_processing_status := 'PENDING';
         END IF;

         l_l_transaction_status := cur_rcv_trx.l_transaction_status;

         IF (l_l_transaction_status IS NULL)
         THEN
            l_l_transaction_status := 'PENDING';
         END IF;

         l_l_processing_mode := cur_rcv_trx.l_processing_mode;

         IF (l_l_processing_mode IS NULL)
         THEN
            l_l_processing_mode := 'BATCH';
         END IF;

         l_l_line_location := cur_rcv_trx.l_line_location;

         IF (l_l_line_location IS NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT POLL.LINE_LOCATION_ID FROM PO_LINE_LOCATIONS_ALL POLL, PO_LINES_ALL POL, PO_HEADERS_ALL POH  
WHERE POLL.PO_LINE_ID = POL.PO_LINE_ID AND POL.PO_LINE_ID = '''
                   || cur_rcv_trx.l_po_line_id
                   || '''  
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID AND POH.ORG_ID = POL.ORG_ID 
AND POLL.ORG_ID = ##EBS_ORG_ID## AND POLL.PO_HEADER_ID = POL.PO_HEADER_ID 
AND POLL.SHIPMENT_NUM = '''
                   || cur_rcv_trx.l_shipment_num
                   || '''  AND POH.SEGMENT1 = '''
                   || cur_rcv_trx.l_po_number
                   || '''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_line_location := l_actionvalidate_msg;
            cur_rcv_trx.l_line_location := l_actionvalidate_msg;
         END IF;

         l_l_auto_trx_code := cur_rcv_trx.l_auto_trx_code;

         IF (l_l_destination_type = 'INVENTORY')
         THEN
            l_l_auto_trx_code := 'DELIVER';
         END IF;

         l_l_source_document := cur_rcv_trx.l_source_document;

         IF (l_l_source_document IS NULL)
         THEN
            l_l_source_document := 'PO';
         END IF;

         l_l_transaction_type := cur_rcv_trx.l_transaction_type;

         IF (l_l_transaction_type IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''RCV TRANSACTION TYPE'' AND ENABLED_FLAG = ''Y'' 
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
                   || cur_rcv_trx.l_transaction_type
                   || '''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_transaction_type := l_actionvalidate_msg;
            cur_rcv_trx.l_transaction_type := l_actionvalidate_msg;
         END IF;

         l_l_transaction_date := cur_rcv_trx.l_transaction_date;

         IF (l_l_transaction_date IS NULL)
         THEN
            BEGIN
               validate_query_date ('SELECT SYSDATE FROM DUAL',
                                    d_actionvalidate_flag,
                                    d_actionvalidate_msg,
                                    d_actionvalidate_value
                                   );
            END;

            IF d_actionvalidate_flag = 'F'
            THEN
               d_actionvalidate_value := NULL;
            END IF;

            l_l_transaction_date := d_actionvalidate_value;
            cur_rcv_trx.l_transaction_date := d_actionvalidate_value;
         END IF;

         l_l_quantity := cur_rcv_trx.l_quantity;
         l_l_uom := cur_rcv_trx.l_uom;

         IF (l_l_uom IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT UNIT_OF_MEASURE FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CODE = '''
                   || cur_rcv_trx.l_uom
                   || '''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_uom := l_actionvalidate_msg;
            cur_rcv_trx.l_uom := l_actionvalidate_msg;
         END IF;

         l_l_item_num := cur_rcv_trx.l_item_num;

         IF (l_l_item_num IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT MSI.INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B MSI,MTL_PARAMETERS MP 
WHERE MSI.ORGANIZATION_ID = MP.ORGANIZATION_ID 
AND MSI.SEGMENT1 = '''
                   || cur_rcv_trx.l_item_num
                   || ''' AND MP.ORGANIZATION_CODE = '''
                   || cur_rcv_trx.l_to_organization
                   || '''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_item_num := l_actionvalidate_msg;
            cur_rcv_trx.l_item_num := l_actionvalidate_msg;
         END IF;

         l_l_to_organization := cur_rcv_trx.l_to_organization;

         IF (l_l_to_organization IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS WHERE ORGANIZATION_CODE = '''
                   || cur_rcv_trx.l_to_organization
                   || ''' AND ((DISABLE_DATE IS NULL) 
OR (DISABLE_DATE > SYSDATE)) AND OPERATING_UNIT = ##EBS_ORG_ID##',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_to_organization := l_actionvalidate_msg;
            cur_rcv_trx.l_to_organization := l_actionvalidate_msg;
         END IF;

         l_l_item_description := cur_rcv_trx.l_item_description;
         l_l_shipment_num := cur_rcv_trx.l_shipment_num;
         l_l_ship_to_location := cur_rcv_trx.l_ship_to_location;

         IF (l_l_ship_to_location IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = '''
                   || cur_rcv_trx.l_ship_to_location
                   || ''' AND SHIP_TO_SITE_FLAG = ''Y''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_ship_to_location := l_actionvalidate_msg;
            cur_rcv_trx.l_ship_to_location := l_actionvalidate_msg;
         END IF;

         l_l_po_number := cur_rcv_trx.l_po_number;

         IF (l_l_po_number IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 = '''
                   || cur_rcv_trx.l_po_number
                   || ''' AND ORG_ID = ##EBS_ORG_ID## AND ENABLED_FLAG = ''Y'' 
AND AUTHORIZATION_STATUS = ''APPROVED'' AND ((END_DATE IS NULL) OR (END_DATE > SYSDATE)) 
AND TYPE_LOOKUP_CODE NOT IN (''RFQ'',''QUOTATION'')',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_po_number := l_actionvalidate_msg;
            cur_rcv_trx.l_po_number := l_actionvalidate_msg;
         END IF;

         l_l_destination_type := cur_rcv_trx.l_destination_type;

         IF (l_l_destination_type IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT LOOKUP_CODE FROM PO_LOOKUP_CODES WHERE LOOKUP_TYPE = ''RCV DESTINATION TYPE'' AND ENABLED_FLAG = ''Y'' 
AND ((INACTIVE_DATE IS NULL) OR (INACTIVE_DATE > SYSDATE)) AND DISPLAYED_FIELD = '''
                   || cur_rcv_trx.l_destination_type
                   || '''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_destination_type := l_actionvalidate_msg;
            cur_rcv_trx.l_destination_type := l_actionvalidate_msg;
         END IF;

         l_l_deliver_to_location := cur_rcv_trx.l_deliver_to_location;

         IF (l_l_deliver_to_location IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT LOCATION_ID FROM HR_LOCATIONS_ALL WHERE LOCATION_CODE = '''
                   || cur_rcv_trx.l_deliver_to_location
                   || ''' AND SHIP_TO_SITE_FLAG = ''Y''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_deliver_to_location := l_actionvalidate_msg;
            cur_rcv_trx.l_deliver_to_location := l_actionvalidate_msg;
         END IF;

         l_l_subinventory := cur_rcv_trx.l_subinventory;
         l_l_reference := cur_rcv_trx.l_reference;
         l_l_po_line_num := cur_rcv_trx.l_po_line_num;
         l_l_category := cur_rcv_trx.l_category;
         l_l_project := cur_rcv_trx.l_project;

         IF (l_l_project IS NOT NULL)
         THEN
            BEGIN
               validate_query_new
                  (   'SELECT PP.PROJECT_ID FROM PA_PROJECT_TYPES_ALL PT, PA_PROJECTS_ALL PP WHERE PT.PROJECT_TYPE = PP.PROJECT_TYPE 
AND NVL (PP.TEMPLATE_FLAG, ''N'') NOT IN (''Y'') AND PA_SECURITY.ALLOW_QUERY (PP.PROJECT_ID) = ''Y'' 
AND PT.PROJECT_TYPE NOT IN (''AWARD_PROJECT'') AND PT.ORG_ID = PP.ORG_ID AND PA_PROJECT_UTILS.CHECK_PRJ_STUS_ACTION_ALLOWED (PP.PROJECT_STATUS_CODE,''NEW_TXNS'') = ''Y'' 
AND PP.SEGMENT1 = '''
                   || cur_rcv_trx.l_project
                   || '''',
                   l_actionvalidate_flag,
                   l_actionvalidate_msg
                  );
            END;

            IF l_actionvalidate_flag = 'F'
            THEN
               l_actionvalidate_msg := NULL;
            END IF;

            l_l_project := l_actionvalidate_msg;
            cur_rcv_trx.l_project := l_actionvalidate_msg;
         END IF;

         BEGIN
            INSERT INTO rcv_transactions_interface
                        (header_interface_id, GROUP_ID,
                         interface_transaction_id, last_updated_by,
                         created_by, creation_date,
                         last_update_date, po_line_id,
                         validation_flag, shipment_header_id,
                         location_id, processing_status_code,
                         transaction_status_code, processing_mode_code,
                         po_line_location_id, auto_transact_code,
                         source_document_code, transaction_type,
                         transaction_date, quantity, unit_of_measure,
                         item_id, to_organization_id,
                         item_description, shipment_num,
                         ship_to_location_id, po_header_id,
                         destination_type_code, deliver_to_location_id,
                         subinventory, attribute15, attribute14,
                         category_id, project_id
                        )
                 VALUES (l_l_iface_header_id, l_l_group_id,
                         l_l_iface_transaction_id, l_l_last_updated_by,
                         l_l_created_by, l_l_creation_date,
                         l_l_last_update_date, l_l_po_line_id,
                         l_l_validation_flag, l_l_shipment_hdr_id,
                         l_l_location, l_l_processing_status,
                         l_l_transaction_status, l_l_processing_mode,
                         l_l_line_location, l_l_auto_trx_code,
                         l_l_source_document, l_l_transaction_type,
                         l_l_transaction_date, l_l_quantity, l_l_uom,
                         l_l_item_num, l_l_to_organization,
                         l_l_item_description, l_l_shipment_num,
                         l_l_ship_to_location, l_l_po_number,
                         l_l_destination_type, l_l_deliver_to_location,
                         l_l_subinventory, l_l_reference, l_l_po_line_num,
                         l_l_category, l_l_project
                        );

            BEGIN
               SELECT DISTINCT ROWID
                          INTO lv_rowid
                          FROM rcv_transactions_interface
                         WHERE attribute15 = l_l_reference;
            EXCEPTION
               WHEN TOO_MANY_ROWS
               THEN
                  DBMS_OUTPUT.put_line (   'Err Msg : '
                                        || 'row id is not available'
                                       );
            END;

            INSERT INTO oal_line_mapping@sunr12toapp_222_qa
                        (oal_exec_id, oal_line_no,
                         oal_row_id, oal_loader_id,
                         interface_table_name
                        )
                 VALUES (cur_rcv_trx.oal_exec_id, cur_rcv_trx.oal_line_no,
                         lv_rowid, cur_rcv_trx.oal_loader_id,
                         'RCV_TRANSACTIONS_INTERFACE'
                        );
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('Err Msg 5 lv_rowid: ' || lv_rowid);
               l_sqlerrm := SQLERRM;

               UPDATE rcv_trx_po_receipt5@sunr12toapp_222_qa
                  SET error_message = l_sqlerrm,
                      status_code = 'E'
                WHERE oal_line_no = cur_rcv_trx.oal_line_no
                  AND oal_exec_id = cur_rcv_trx.oal_exec_id;
         END;

         IF l_sqlerrm IS NULL
         THEN
            l_interfaceflag := 'Y';
         ELSE
            l_interfaceflag := 'N';
         END IF;

         UPDATE rcv_trx_po_receipt5@sunr12toapp_222_qa
            SET int_exec_success_flag = l_interfaceflag
          WHERE oal_line_no = cur_rcv_trx.oal_line_no
            AND oal_exec_id = cur_rcv_trx.oal_exec_id;
      END LOOP;

      COMMIT;
   END LOOP;
END;