XML publisher and DataTemplate- Report Generation (XML Report without RDF)

XML publisher and DataTemplate- Report Generation (XML Report without RDF)   Step1: Create a XML file STEP 2: Create a RTF file .   In this  the RTF(Rich Text Field) is used to insert the xml tags   To insert this goto> Customize Quick Acess Toolbar > more commands > all commands > INSERT FORM FIELD à add it. Then select the field you want and press the INSERT FORM FIELD button which is press at toolbar in green symbol. Here "INSERT FORM FEILD" is taken for link creation Now double click > press ADD HELP TEXT > write in xml tags the field name  > <?field_name?> Step3: Now create Data Definition in the "XML PUBLISHER"Responsibility. and .XML file is attached. Note: Here the  "CODE"name   and the Concurrent Program "SHORT"name should be same. .XML file is attached at Data Template Step4: Now Data template is created in the "XML PUBLISHER" Responsibility. Template i.e.. (RTF template) is added at FILE option. Step5: Create a Concurrent program in the Responsibility of that APLLICATION.here the Executable file name should be always "XDODTEXE"   and output format as XML Step 6:Program is added to the "Application" in the "System Responsibility" Step7:Request should be raised in the same Responsibility where Concurrent program is created. OUTPUT: Add Comment   FORM PERSONALIZATION10/29/2011 0 Comments   Q)  Hiding the Person, Customer and Fax fields of the User form Step 1: open the users form in the System Administrator Step 2:  goto > Help > Diagnostics > Custom Code > Personalize This is to personalize the form according to the requirement. Step 3: Seq : 11 Description: hiding the person,Customer and Fax feilds of the user form level: Function  ,  Enable: Yes condition:                Trigger Event: WHEN-NEW-FORM-INSTANCE                Processing mode: Not in Query Mode   context:                 Level: Responsibility                 Value: System Administrator     Step 4: Actions tab: seq:1 type: property Description: person language: all,  Enabled: yes object type: ITEM target object: USER.EMPLOYEE_NAME property name: DISPLAYED Value: FALSE Next Press VALIDATE   and APPLY NOW Out Put : Here we can see the fields(person,customer,fax) are                   hidden.

Report customaization

Report customaization

Custom Reports Development & Customization Process

Posted by Arun Kumar S.R at 7:33 AM Report Builder Components

Report Builder Components are

1. Data Model
2. Layout Model
3. Object Navigator
4. Report Triggers
5. Parameter Form
6. Program Units
7. Attached Libraries

Data Model
The Report Editor's Data Model view enables you to define and modify the data model objects for a report.

Data Model Comprises tool Palette which Comes with several Options They are
1. Select
2. Magnify
3. SQL Query
4. Ref Cursor Query
5. Express Query
6. Summary Column
7. Formula Column
8. Placeholder Column
9. Cross Product
10. Data Link

The tool palette is a set of tools you can use to create and manipulate objects. Click a tool to activate it for a single operation, or double-click a tool to "lock" it for multiple operations. The tools in the tool palette vary depending on the Report Editor view.

Select
The Selector toolbar provides tools to help you select or arrange dimension values that meet your criteria. The Selector toolbar is displayed at the top of the Selector dialog box.

Magnify
To magnify a hard-to-see portion of your report or reduce the image to get a sense of your report's overall layout.

SQL Query
The SQL Query Statement property is a SQL SELECT statement that retrieves data from the database for your report. Enter a valid SELECT statement not to exceed 64K. The upper limit may vary between operating systems All features of the SELECT statement are supported, except the INTO and FOR UPDATE clauses.

In building your SELECT statement, you can do the following:

1. Use the Tables and Columns dialog box
2. Insert comments
3. Insert bind and lexical references
4. Review SQL errors

Required/Optional : Required

Ref Cursor Query
A ref cursor query uses PL/SQL to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly typed ref cursor. The function must ensure that the ref cursor is opened and associated with a SELECT statement that has a SELECT list that matches the type of the ref cursor.

You base a query on a ref cursor when you want to:

1. more easily administer SQL
2. avoid the use of lexical parameters in your reports
3. share data sources with other applications, such as Form Builder
4. encapsulate logic within a subprogram

Express Query
--------

Summary Column
A summary column performs a computation on another column's data.
Using the Report Wizard or Data Wizard, you can create the following summaries:
sum, average, count, minimum, maximum, % total.

You can also create a summary column manually in the Data Model view, and use the Property Palette to create the following additional summaries: first, last, standard deviation, variance.

Formula Column
A formula column performs a user-defined computation on another column(s) data, including placeholder columns.

Placeholder Column
A placeholder is a column for which you set the data type and value in PL/SQL that you define.

You can set the value of a placeholder column in the following places:
1. The Before Report Trigger, if the placeholder is a report-level column
2. A report-level formula column, if the placeholder is a report-level column
3. A formula in the placeholder's group or a group below it

Cross Product
The Cross Product Group property is the group that contains the source groups of the Horizontal and Vertical Repeating Frames. The cross product group correlates values between one or more groups to produce the data in the matrix.

Values Enter a valid cross product group name.
Applies to matrices
Required/Optional required
Default blank

Data Link
Data links relate the results of multiple queries. A data link (or parent-child relationship) causes the child query to be executed once for each instance of its parent group.
When you create a data link in the Data Model view of your report, Report Builder constructs a clause (as specified in the link's Property Palette) that will be added to the child query's SELECT statement at runtime.

Layout Model
The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

This is the view where you can design your Report Layout.

Object Navigator
The Object Navigator provides a hierarchical display of all major objects in a report or template, including attached libraries and external queries. Using this view, you can take such actions as select an object, bring up the Property Palette for an object, edit an object's PL/SQL, drag and drop PL/SQL program units, and search for an object by name.

Report Triggers
Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. Using the conditional processing capabilities of PL/SQL for these triggers, you can do things such as customize the formatting of your report, perform initialization tasks, and access the database.

To create or modify a report trigger, use Report Triggers in the Object Navigator. Report triggers must explicitly return TRUE or FALSE. Report Builder has five global report triggers which are fired in the following sequence.

1. Before Parameter Form trigger
2. After Parameter Form trigger
3. Before Report trigger
4. Between Pages trigger
5. After Report trigger

1. Before Parameter Form trigger
The Before Parameter Form trigger fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.
Definition Level Report

On Failure
Displays an error message and then returns to the place from which you ran the report.

2. After Parameter Form trigger
The After Parameter Form trigger fires after the Runtime Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the Runtime Parameter Form. Columns from the data model are not accessible from this trigger. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

Definition Level Report

On Failure
Returns to the Runtime Parameter Form. If the Form is suppressed, then returns to place from which you ran the report.

3. Before Report trigger
The Before Report trigger fires before the report is executed but after queries is parsed and data is fetched.
Definition Level Report

On Failure
Displays an error message and then returns to the place from which you ran the report.

4. Between Pages trigger
Between Pages trigger fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting. In the Runtime Previewer or Live Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.
Definition Level report
On Failure
Displays an error message when you try to go to the page for which the trigger returned FALSE. The Between Pages trigger does not fire before the first page.

5. After Report trigger
The After Report trigger fires after you exit the Runtime Previewer, or after report output is sent to a specified destination, such as a file, a printer, or a mailid. This trigger can be used to clean up any initial processing that was done, such as deleting tables.
Note, however, that this trigger always fires, whether or not your report completed successfully.
Definition Level Report

On Failure
Does not affect formatting because the report is done. You can raise a message, though, to indicate that the report did not run correctly

Note: The After-Report trigger does not fire when you are in the Live Previewer.

Layout Model Properties

In this Layout model, we have several Options

Frames:
Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.
For example, a frame might be used to surround all objects owned by a group, to surround column headings, or to surround summaries.
When you default the layout for a report, Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.

Repeating Frames
Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.
When you default the layout for a report, Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view.

Text
This allows to embed the text in the layout view.

Field
A field is the layout container for each column in the layout. A field is owned by the object surrounding it, typically a repeating frame, unless the field is a summary (in which case it is owned by a frame).

Anchor
Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. The end of the anchor with a symbol on it is attached to the parent object...

Parameter Form
The Report Editor's Parameter Form view enables you to create a Runtime Parameter Form for your report. You can select pre-defined system parameters for your form using the Parameter Form Builder, or you can create your own.

Program Units
Program units are packages, functions, or procedures that you can reference from any PL/SQL within the current report.

Stored program units (also known as stored subprograms) can be compiled separately and stored permanently in an Oracle database, ready to be executed. Once compiled and stored in the data dictionary, they are schema objects, which can be referenced by any number of applications connected to that database.
Stored program units offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.

Stored program units are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a program unit need be loaded into memory for execution by multiple users.

Attached Libraries
Attached libraries are external PL/SQL libraries that you have associated with a report or another external library. When an external library is attached, you can reference its packages, functions, and procedures from within your report. For example, if you attached an external library name MYLIB to your report and it contained a function named ADDXY, then you could reference ADDXY from any PL/SQL in the report.

Report Customizations
You can customize business intelligence reports by both content and layout. You also have the option to add columns, filter the data and sort the resulting information. For example, you can add another column for a running total, or insert grand totals and subtotals as you need them. In layout, you can change column headings, report titles and the order of columns. You can also display the data in graphical form, such as bar graphs or pie charts.

Customization is enhancement of new features to the Existing Report Content and Layout depending on the new requirement.

PO RECEIPT OPEN INTERFACE SCRIPT

PO RECEIPT OPEN INTERFACE SCRIPT

/* Formatted on 2011/07/21 16:17 (Formatter Plus v4.8.8) */
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;