SALES ORDER CONVERSION

                                            SALES ORDER CONVERSION

CREATE OR REPLACE PACKAGE BODY xx_ont_oe_orders_imp_pkg
AS

/*
-------------------------------------------------------------
Package Name    : XX_ONT_OE_ORDERS_IMP_PKG
RICEW Object id : XX_ONT_01
Purpose         : Package Specification
--------------------------------------------------------------
*/  

PROCEDURE main (
      p_errbuf     OUT      VARCHAR2
    , p_retcode    OUT      VARCHAR2
    , p_run_mode   IN       VARCHAR2
   )
   IS
      --Cursor to select Order Headers from Staging table xx_items_stg
      CURSOR c_order_control
      IS
         SELECT *
           FROM xx_ont_order_stg
          WHERE rec_type = 'ONTH' AND status = 'NW';

      --Cursor to select Order Lines from Staging table xx_items_stg
      CURSOR c_order_line_control (p_header VARCHAR2)
      IS
         SELECT *
           FROM xx_ont_order_stg
          WHERE rec_type = 'ONTL'
            AND orig_sys_document_ref = p_header
            AND status = 'NW';

      --Cursor to select from Pre-Interface Order table XX_ONT_ORDER_PREINT
      CURSOR c_order_validate
      IS
         SELECT *
           FROM xx_ont_order_preint
          WHERE status = 'IP';

      --Cursor to select from Pre-Interface Line table XX_ONT_ORDER_PREINT
      CURSOR c_line_validate (p_header VARCHAR2)
      IS
         SELECT *
           FROM xx_ont_lines_preint
          WHERE status = 'IP' AND orig_sys_document_ref = p_header;

/*--------------------------------------------------------------------------------------------------------
Private Variable Declaration Section
---------------------------------------------------------------------------------------------------------*/

      --General Variables
      l_seq_val                       NUMBER;
      l_standard_request_id           fnd_concurrent_requests.request_id%TYPE
                                                                          := 0;
      l_completed                     BOOLEAN;
      l_process_status                NUMBER;
      l_interface_stg_rec             xx_ont_order_preint%ROWTYPE;
      l_organization_id               NUMBER;
      l_process_id                    NUMBER;
      l_count                         NUMBER;
--EMF Variables
      l_package_name                  xx_emf_message_headers.program_name%TYPE
                                                 := 'XX_ONT_OE_ORDERS_IMP_PKG';
      l_program_name                  xx_emf_message_headers.program_name%TYPE
                                            := l_package_name || '.' || 'MAIN';
      l_ricewid                       xx_emf_message_headers.ricew_id%TYPE
                                                               := 'ONT_CNV_01';
      --Object Name
      l_request_id                    xx_emf_message_headers.request_id%TYPE;
      l_error_rec                     xx_emf_message_details%ROWTYPE;
      --used for EMF error header
      l_messages_rec                  xx_emf_message_details%ROWTYPE;
      --used for EMF messages
      l_null_rec                      xx_emf_message_details%ROWTYPE   := NULL;
      --used for EMF messages
      l_header_id                     xx_emf_message_headers.header_id%TYPE;
      --EMF ID
      l_return_value                  NUMBER                           := NULL;
      --stores number returned by EMF API's
      l_err_width_rec                 xx_emf.output_rec_type;
      --record type variable for assigning width to error section
      l_processed_recs                NUMBER                              := 0;
      --stores total records for summary report at header Level
      l_processed_line_recs           NUMBER                              := 0;
      ----stores total records for summary report at line levele
      l_successful_recs               NUMBER                              := 0;
      --stores total successful records for summary report
      l_error_recs                    NUMBER                              := 0;
      --stores total error records for the summary report
      l_cnt_processed_recs            NUMBER                              := 0;
      -- stores Total Control record
      l_cnt_successful_recs           NUMBER                              := 0;
      -- stores total successful control record
      l_cnt_error_recs                NUMBER                              := 0;
      -- stores total errored control record
      l_ord_insert_rec_count          NUMBER                              := 0;
      -- stores total Order interface record
      l_cntrl_count                   NUMBER                              := 0;
      -- store total control record
      l_order_exist                   NUMBER                              := 0;
      -- count for existing order
      l_status                        xx_emf_message_headers.status%TYPE;
      --stores ERROR/WARNING/SUCCESS
      l_error_flag                    VARCHAR2 (1)                      := 'N';
      --Flag to capture error (Y=ERROR/N=SUCCESS)
      l_warning_flag                  VARCHAR2 (1);
      --Flag to capture warning ('W')
      l_output_message                VARCHAR2 (1000);
      --stores the message returned from external routines
      l_phase                         VARCHAR2 (200);
      l_vstatus                       VARCHAR2 (200);
      l_dev_phase                     VARCHAR2 (200);
      l_dev_status                    VARCHAR2 (200);
      l_message                       VARCHAR2 (2000);
--  Common Validations Variables
      l_order_source_id               oe_order_sources.order_source_id%TYPE;
      l_orig_sys_document_ref         oe_headers_iface_all.orig_sys_document_ref%TYPE;
      l_sold_to_org_id                oe_headers_iface_all.sold_to_org_id%TYPE;
      l_site_id                       hz_cust_site_uses_all.site_use_id%TYPE;
      l_cntrl_cnt                     NUMBER;
      l_control_cnt                   xx_ont_order_stg.record_count%TYPE;
--  Exception Variables
      e_emf_initialize_error          EXCEPTION;
      --Stop the program if EMF initialization fails
      e_emf_initialize_detail_error   EXCEPTION;
      --Stop the program with EMF error header insertion fails
      e_cntrl_fail                    EXCEPTION;
      e_control_valid_fail            EXCEPTION;
   BEGIN
      l_request_id := fnd_profile.VALUE ('CONC_REQUEST_ID');
/*----------------------------------------------------------------------------------------------------
Initialize Trace
Purpose : Set the program environment for Tracing
-----------------------------------------------------------------------------------------------------*/

      xx_trace.set_program_environment (p_program_name => l_program_name);
/*----------------------------------------------------------------------------------------------------
Initialize EMF Section
Purpose : EMF is initialized so that error can be logged.
-----------------------------------------------------------------------------------------------------*/

      xx_trace.vl ('Entering Program ' || l_program_name);
      --Call to EMF insert_program_start
      l_header_id :=
         xx_emf.insert_program_start (p_program_name      => l_program_name
                                    , p_program_type      => g_program_type
                                    , p_ricew_id          => l_ricewid
                                    , p_request_id        => l_request_id
                                     );

      IF (l_header_id <= 1)
      THEN                                --Header id should be greater than 1
         RAISE e_emf_initialize_error;
      --Raise Exception to Stop the program if EMF initialization fails
      END IF;

      --
      xx_trace.l ('EMF initialized, header_id :' || TO_CHAR (l_header_id));
    --
/*----------------------------------------------------------------------------------------------------
Insert EMF Header
Purpose : To tag the Error labels to EMF columns
-----------------------------------------------------------------------------------------------------*/

      l_error_rec.header_id := l_header_id;
      --
      -- Identifier to locate an error.
      --
      l_error_rec.identifier1 := 'Error Code';            --First Error Header
      l_error_rec.identifier2 := 'Error Message';        --Second Error Header
      l_error_rec.identifier3 := 'Record ID';             --Third Error Header
      l_error_rec.identifier4 := 'Order Doc Ref';        --Fourth Error Header
      l_error_rec.identifier5 := 'Order Line Ref';        --Fifth Error Header
      l_error_rec.identifier6 := 'Order Ship Ref';        --Sixth Error Header
      -- l_error_rec.identifier7 := NULL;                   --Seventh Error Header
      -- l_error_rec.identifier8 := NULL;                   --Eighth Error Header
      --
      -- Insert error header
      --
      l_return_value :=
                      xx_emf.insert_error_headers (p_error_rec      => l_error_rec);

      --
      IF l_return_value = 1
      THEN                       --(1 indicates Error and 0 indicates Success)
         --Raise Exception to Stop the program with 'ERROR' status if EMF error header initilaization fails
         RAISE e_emf_initialize_detail_error;
      END IF;

      --
      xx_trace.l ('EMF Error Header inserted');
      l_return_value := NULL;
   --
/*---------------------------------------------------------------------------------------------------
Call To Purge  Program  xx_emf.purge_ricewid_dated_messages
Purpose : DELETE EMF error records for this  RICE object based on retention period.
----------------------------------------------------------------------------------------------------*/

      l_return_value :=
         xx_emf.purge_ricewid_dated_messages (l_ricewid
                                            , (SYSDATE - g_retention_period)
                                             );

      --
      IF l_return_value = 1
      THEN
         --
         l_warning_flag := 'W';
         xx_trace.l ('Failed to purge old EMF records');
      --
      END IF;

/*---------------------------------------------------------------------------------------------------
Read Staging Table Data and Perform Control Level Validations
---------------------------------------------------------------------------------------------------*/

      IF UPPER (p_run_mode) = 'F'
      THEN
         BEGIN                                                   -- 1st Begin
            --
            BEGIN
               SELECT COUNT (*)
                 INTO l_cntrl_count
                 FROM xx_ont_order_stg
                WHERE rec_type = 'ONTH';

               SELECT record_count
                 INTO l_control_cnt
                 FROM xx_ont_order_stg
                WHERE rec_type = 'CRC';

               IF l_cntrl_count <> l_control_cnt
               THEN
                  l_error_flag := 'Y';
                  xx_trace.l
                     ('Order Header Record Count does not match with Control Record Level.'
                     );
                  --Write to Error
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00001'
                    , p_identifier2         => 'Order Header Record Count does not match with Control Record Level.'
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => NULL
                    , p_identifier5         => NULL
                    , p_identifier6         => NULL
                    --p_identifier7 => NULL -- Can be utilized for displaying more information
                    --p_identifier8 => NULL -- Can be utilized for displaying more information
                  ,   p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
               END IF;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_flag := 'Y';
                  xx_trace.l
                     (   'Order Header Record Count does not match with Control Record Level.'
                      || SQLERRM
                     );
                  --Write to Error
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00001'
                    , p_identifier2         =>    'Order Header Record Count does not match with Control Record Level.'
                                               || SQLERRM
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => NULL
                    , p_identifier5         => NULL
                    , p_identifier6         => NULL
                    --p_identifier7 => NULL -- Can be utilized for displaying more information
                    --p_identifier8 => NULL -- Can be utilized for displaying more information
                  ,   p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
            END;

            FOR c_order_rec IN c_order_control
            LOOP
               l_cnt_processed_recs := l_cnt_processed_recs + 1;
               -- Processed records in Control Validation
               l_cntrl_cnt := l_cntrl_cnt + 1;
               -- for Control Count.
               l_error_flag := 'N';

               IF c_order_rec.orig_sys_document_ref IS NULL
               THEN
                  l_error_flag := 'Y';
                  xx_trace.l
                            ('Order Document Reference value cannot be null.');
                  --Write to Error
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00001'
                    , p_identifier2         => 'Order Document Reference value cannot be null.'
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                    , p_identifier5         => NULL
                    , p_identifier6         => NULL
                    --p_identifier7 => NULL -- Can be utilized for displaying more information
                    --p_identifier8 => NULL -- Can be utilized for displaying more information
                  ,   p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
               ELSE
                  l_orig_sys_document_ref :=
                                            c_order_rec.orig_sys_document_ref;
               END IF;

               IF c_order_rec.sold_from_org IS NULL
               THEN
                  l_error_flag := 'Y';
                  xx_trace.l ('Sold from organization cannot be null.');
                  --Write to Error
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00001'
                    , p_identifier2         => 'Sold from organization cannot be null.'
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                    , p_identifier5         => NULL
                    , p_identifier6         => NULL
                    --p_identifier7 => NULL -- Can be utilized for displaying more information
                    --p_identifier8 => NULL -- Can be utilized for displaying more information
                  ,   p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
               END IF;

               IF c_order_rec.customer_name IS NULL
               THEN
                  l_error_flag := 'Y';
                  xx_trace.l ('Customer Name cannot be null.');
                  --Write to Error
                  xx_emf.call_store_message
                         (p_message_group       => 'ERR_DTL'
                        , p_header_id           => l_header_id
                        , p_debug_value         => 20   -- low level debugging
                        , p_global_debug        => g_debug_level
                        , p_identifier1         => 'E-00001'
                        , p_identifier2         => 'Customer Name cannot be null.'
                        , p_identifier3         => NULL
                        -- Can be utilized to store record serial number
                  ,       p_identifier4         => c_order_rec.orig_sys_document_ref
                        , p_identifier5         => NULL
                        , p_identifier6         => NULL
                        --p_identifier7 => NULL -- Can be utilized for displaying more information
                        --p_identifier8 => NULL -- Can be utilized for displaying more information
                  ,       p_process_status      => l_return_value
                        , p_error_message       => l_output_message
                         );
               END IF;

               IF c_order_rec.ship_to_location IS NULL
               THEN
                  l_error_flag := 'Y';
                  xx_trace.l ('Customer Ship to Location cannot be null.');
                  --Write to Error
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00001'
                    , p_identifier2         => 'Customer Ship to Location cannot be null.'
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                    , p_identifier5         => NULL
                    , p_identifier6         => NULL
                    --p_identifier7 => NULL -- Can be utilized for displaying more information
                    --p_identifier8 => NULL -- Can be utilized for displaying more information
                  ,   p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
               END IF;

               FOR c_order_line_rec IN
                  c_order_line_control (c_order_rec.orig_sys_document_ref)
               LOOP
                  l_cnt_processed_recs := l_cnt_processed_recs + 1;

                  IF c_order_line_rec.item_number IS NULL
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.l ('Item Number value cannot be null.');
                     --Write to Error
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00001'
                       , p_identifier2         => 'Item Number value cannot be null.'
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_line_rec.orig_sys_line_ref
                       , p_identifier6         => c_order_line_rec.orig_sys_shipment_ref
                       --p_identifier7 => NULL -- Can be utilized for displaying more information
                       --p_identifier8 => NULL -- Can be utilized for displaying more information
                     ,   p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
                  END IF;

                  IF c_order_line_rec.orig_sys_line_ref IS NULL
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.l ('Order Line Reference value cannot be null.');
                     --Write to Error
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00001'
                       , p_identifier2         => 'Order Line Reference value cannot be null.'
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_line_rec.orig_sys_line_ref
                       , p_identifier6         => c_order_line_rec.orig_sys_shipment_ref
                       --p_identifier7 => NULL -- Can be utilized for displaying more information
                       --p_identifier8 => NULL -- Can be utilized for displaying more information
                     ,   p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
                  END IF;

                  IF c_order_line_rec.orig_sys_shipment_ref IS NULL
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.l
                            ('Order shipment Reference value cannot be null.');
                     --Write to Error
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00001'
                       , p_identifier2         => 'Order Shipment Reference value cannot be null.'
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_line_rec.orig_sys_line_ref
                       , p_identifier6         => c_order_line_rec.orig_sys_shipment_ref
                       --p_identifier7 => NULL -- Can be utilized for displaying more information
                       --p_identifier8 => NULL -- Can be utilized for displaying more information
                     ,   p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
                  END IF;

                  IF c_order_line_rec.ordered_quantity IS NULL
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.l ('Order Quantity cannot be null.');
                     --Write to Error
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00001'
                       , p_identifier2         => 'Order Quantity cannot be null.'
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_line_rec.orig_sys_line_ref
                       , p_identifier6         => c_order_line_rec.orig_sys_shipment_ref
                       --p_identifier7 => NULL -- Can be utilized for displaying more information
                       --p_identifier8 => NULL -- Can be utilized for displaying more information
                     ,   p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
                  END IF;

                  IF c_order_line_rec.ordered_quantity_uom IS NULL
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.l ('Order Quantity UOM cannot be null.');
                     --Write to Error
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00001'
                       , p_identifier2         => 'Order Quantity UOM cannot be null.'
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_line_rec.orig_sys_line_ref
                       , p_identifier6         => c_order_line_rec.orig_sys_shipment_ref
                       --p_identifier7 => NULL -- Can be utilized for displaying more information
                       --p_identifier8 => NULL -- Can be utilized for displaying more information
                     ,   p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
                  END IF;

                  IF c_order_line_rec.unit_selling_price IS NULL
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.l ('Unit Selling price cannot be null.');
                     --Write to Error
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00001'
                       , p_identifier2         => 'Unit Selling price cannot be null.'
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_line_rec.orig_sys_line_ref
                       , p_identifier6         => c_order_line_rec.orig_sys_shipment_ref
                       --p_identifier7 => NULL -- Can be utilized for displaying more information
                       --p_identifier8 => NULL -- Can be utilized for displaying more information
                     ,   p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
                  END IF;

                  IF l_error_flag <> 'Y'
                  THEN
                     --Increment the successfull record count
                     l_cnt_successful_recs := l_cnt_successful_recs + 1;
                  END IF;
               END LOOP;

               IF l_error_flag = 'Y'
               THEN
                  EXIT;
               ELSE
                  --Increment the successfull record count
                  l_cnt_successful_recs := l_cnt_successful_recs + 1;
               END IF;
            END LOOP;

            IF l_error_flag = 'Y'
            THEN
               xx_trace.l ('Control Validation Failed.');
               --Write to Error
               xx_emf.call_store_message
                              (p_message_group       => 'ERR_DTL'
                             , p_header_id           => l_header_id
                             , p_debug_value         => 20
                             -- low level debugging
               ,               p_global_debug        => g_debug_level
                             , p_identifier1         => 'E-00001'
                             , p_identifier2         => 'Control Validation Failed.'
                             , p_identifier3         => NULL
                             -- Can be utilized to store record serial number
               ,               p_identifier4         => NULL
                             , p_identifier5         => NULL
                             , p_identifier6         => NULL
                             --p_identifier7 => NULL -- Can be utilized for displaying more information
                             --p_identifier8 => NULL -- Can be utilized for displaying more information
               ,               p_process_status      => l_return_value
                             , p_error_message       => l_output_message
                              );
               RAISE e_control_valid_fail;
            END IF;
         --
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_flag := 'Y';
               xx_trace.h
                        (   'Exception in Control Validation. Oracle Error :'
                         || SQLERRM
                        );
               xx_emf.call_store_message
                          (p_message_group       => 'ERR_DTL'
                         , p_header_id           => l_header_id
                         , p_debug_value         => 40 -- high level debugging
                         , p_global_debug        => g_debug_level
                         , p_identifier1         => 'E-00001'
                         , p_identifier2         =>    'Error in Control Validation.'
                                                    || SQLERRM
                         , p_identifier3         => NULL
                         , p_process_status      => l_return_value
                         , p_error_message       => l_output_message
                          );
               l_error_recs := l_processed_recs;
               l_successful_recs := 0;

               DELETE FROM xx_ont_order_stg;
               COMMIT;
               RAISE;
         END;                                                      --1st Begin

/*---------------------------------------------------------------------------------------------------
Load Data into Header Pre-Interface Table
---------------------------------------------------------------------------------------------------*/

         BEGIN                                                     --2nd Begin
            INSERT INTO xx_ont_order_preint
                        (record_id
                       , status
                       , order_source
                       , orig_sys_document_ref
                       , order_type
                       , price_list
                       , ship_from_org
                       , customer_name
                       , ship_to_org_id
                       , operation_code
                       , created_by
                       , creation_date
                       , last_update_login
                       , last_updated_by
                       , last_update_date
                       , request_id
                        )
               SELECT xx_ont_order_preint_s.NEXTVAL
                    , 'NW'
                    , order_source
                    , orig_sys_document_ref
                    , order_type
                    , price_list
                    , sold_from_org
                    , customer_name
                    , ship_to_location
                    , operation_code
                    , NVL (created_by, -1)
                    , NVL (creation_date, SYSDATE)
                    , last_updated_login
                    , NVL (last_updated_by, -1)
                    , NVL (last_updated_date, SYSDATE)
                    , l_request_id
                 FROM xx_ont_order_stg
                WHERE rec_type = 'ONTH';

            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_flag := 'Y';
               xx_trace.h
                  (   'Exception While Inserting Into Pre-Interface Header table. Oracle Error:'
                   || SQLERRM
                  );
               xx_emf.call_store_message
                  (p_message_group       => 'ERR_DTL'
                 , p_header_id           => l_header_id
                 , p_debug_value         => 20          -- low level debugging
                 , p_global_debug        => g_debug_level
                 , p_identifier1         => 'E-00002'
                 , p_identifier2         =>    'Exception While Inserting Into Pre-Interface Header table.Oracle Error-'
                                            || SQLERRM
                 , p_identifier3         => NULL
                 -- Can be utilized to store record serial number
               ,   p_process_status      => l_return_value
                 , p_error_message       => l_output_message
                  );
         END;

/*---------------------------------------------------------------------------------------------------
Load Data into Line Pre-Interface Table
---------------------------------------------------------------------------------------------------*/

         BEGIN                                                     --2nd Begin
            INSERT INTO xx_ont_lines_preint
                        (record_id
                       , status
                       , orig_sys_document_ref
                       , orig_sys_line_ref
                       , orig_sys_shipment_ref
                       , inventory_item
                       , ordered_quantity
                       , order_quantity_uom
                       , unit_selling_price
                       , delivery_lead_time
                       , delivery_id
                       , request_date
                       , created_by
                       , creation_date
                       , last_update_login
                       , last_updated_by
                       , last_update_date
                        )
               SELECT xx_ont_lines_preint_s.NEXTVAL
                    , 'NW'
                    , orig_sys_document_ref
                    , orig_sys_line_ref
                    , orig_sys_shipment_ref
                    , item_number
                    , ordered_quantity
                    , ordered_quantity_uom
                    , unit_selling_price
                    , delivery_lead_time
                    , delivery_id
                    , request_date
                    , NVL (created_by, -1)
                    , NVL (creation_date, SYSDATE)
                    , last_updated_login
                    , NVL (last_updated_by, -1)
                    , NVL (last_updated_date, SYSDATE)
                 FROM xx_ont_order_stg
                WHERE rec_type = 'ONTL';

            COMMIT;
         --
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_flag := 'Y';
               xx_trace.h
                  (   'Exception While Inserting Into Pre-Interface Line table. Oracle Error:'
                   || SQLERRM
                  );
               xx_emf.call_store_message
                  (p_message_group       => 'ERR_DTL'
                 , p_header_id           => l_header_id
                 , p_debug_value         => 20          -- low level debugging
                 , p_global_debug        => g_debug_level
                 , p_identifier1         => 'E-00002'
                 , p_identifier2         =>    'Exception While Inserting Into Pre-Interface Line table.Oracle Error-'
                                            || SQLERRM
                 , p_identifier3         => NULL
                 -- Can be utilized to store record serial number
               ,   p_process_status      => l_return_value
                 , p_error_message       => l_output_message
                  );
         END;                                                      --2nd Begin

/*-----------------------------------------------------------------------------
Updating Header pre interface table with status 'IP' where record_status in('NW','PF')
------------------------------------------------------------------------------*/

         BEGIN                                                     --3rd Begin
            UPDATE xx_ont_order_preint
               SET status = 'IP'
             WHERE status IN ('NW', 'PF');

            xx_trace.l ('Header Pre-Interface Table status updated to IP');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_flag := 'Y';
               xx_trace.h
                  (   ' Exception While Updating Status of Header Pre-Interface Table.Oracle Error-'
                   || SQLERRM
                  );
               xx_emf.call_store_message
                  (p_message_group       => 'ERR_DTL'
                 , p_header_id           => l_header_id
                 , p_debug_value         => 40         -- high level debugging
                 , p_global_debug        => g_debug_level
                 , p_identifier1         => 'E-00002'
                 , p_identifier2         =>    'Exception While Updating Status of Header Pre-Interface Table.Oracle Error-'
                                            || SQLERRM
                 , p_identifier3         => NULL
                 -- Can be utilized to store record serial number
               ,   p_process_status      => l_return_value
                 , p_error_message       => l_output_message
                  );
         END;                                                       --3rdBegin

/*-----------------------------------------------------------------------------
Updating Line pre interface table with status 'IP' where record_status in('NW','PF')
------------------------------------------------------------------------------*/

         BEGIN                                                     --3rd Begin
            UPDATE xx_ont_lines_preint
               SET status = 'IP'
             WHERE status IN ('NW', 'PF');

            xx_trace.l ('Line Pre-Interface Table status updated to IP');
         EXCEPTION
            WHEN OTHERS
            THEN
               l_error_flag := 'Y';
               xx_trace.h
                  (   ' Exception While Updating Status of Line Pre-Interface Table.Oracle Error-'
                   || SQLERRM
                  );
               xx_emf.call_store_message
                  (p_message_group       => 'ERR_DTL'
                 , p_header_id           => l_header_id
                 , p_debug_value         => 40         -- high level debugging
                 , p_global_debug        => g_debug_level
                 , p_identifier1         => 'E-00002'
                 , p_identifier2         =>    'Exception While Updating Status of Line Pre-Interface Table.Oracle Error-'
                                            || SQLERRM
                 , p_identifier3         => NULL
                 -- Can be utilized to store record serial number
               ,   p_process_status      => l_return_value
                 , p_error_message       => l_output_message
                  );
         END;                                                       --4rdBegin
      END IF;                                              --IF p_run_mode='F'

/*----------------------------------------------------------------------------
--Business Validations
----------------------------------------------------------------------------*/

      BEGIN                                                       --5 th Begin
         FOR c_order_validate_rec IN c_order_validate
         LOOP
            --
            l_processed_recs := l_processed_recs + 1;

            --Counter for total records
            BEGIN                                                --5.0 th end
               SELECT COUNT (*)
                 INTO l_order_exist
                 FROM oe_order_headers_all
                WHERE orig_sys_document_ref =
                                    c_order_validate_rec.orig_sys_document_ref;

               IF l_order_exist > 0
               THEN
                  l_error_flag := 'Y';
                  xx_trace.h
                     (   ' Exception occured while validating order already exist.Oracle Error-'
                      || SQLERRM
                     );
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00003'
                    , p_identifier2         =>    'Exception occured while validating Order already exist.Oracle Error-'
                                               || SQLERRM
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                    , p_identifier5         => c_order_validate_rec.order_source
                    , p_identifier6         => c_order_validate_rec.ship_from_org
                    ,
                      --p_identifier7 => NULL -- Can be utilized for displaying more information
                      --p_identifier8 => NULL -- Can be utilized for displaying more information
                      p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
--
               END IF;
            END;                                                   --5.0th end

            IF c_order_validate_rec.order_source IS NOT NULL
            THEN
               BEGIN       --                                       5.1 begin
                  SELECT order_source_id
                    INTO l_order_source_id
                    FROM oe_order_sources
                   WHERE NAME = c_order_validate_rec.order_source;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.h
                        (   ' Exception occured while validating order source.Oracle Error-'
                         || SQLERRM
                        );
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00003'
                       , p_identifier2         =>    'Exception occured while validating Order Source.Oracle Error-'
                                                  || SQLERRM
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_validate_rec.order_source
                       , p_identifier6         => c_order_validate_rec.ship_from_org
                       ,
                         --p_identifier7 => NULL -- Can be utilized for displaying more information
                         --p_identifier8 => NULL -- Can be utilized for displaying more information
                         p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
--
               END;                                               --5.1 th end
            ELSE
               l_error_flag := 'Y';
            END IF;

            IF (c_order_validate_rec.ship_from_org IS NOT NULL)
            THEN
               --Validating Organization Code
               BEGIN                                           --4.1 th Begin
                  --
                  SELECT organization_id
                    INTO c_order_validate_rec.ship_from_org_id
                    FROM org_organization_definitions
                   WHERE UPPER (organization_code) =
                                    UPPER (c_order_validate_rec.ship_from_org);
               --
               EXCEPTION
                  --
                  WHEN OTHERS
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.h
                        (   ' Exception occured while validating organization code.Oracle Error-'
                         || SQLERRM
                        );
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00003'
                       , p_identifier2         =>    'Exception occured while validating organization code.Oracle Error-'
                                                  || SQLERRM
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_validate_rec.ship_from_org
                       ,
                         --p_identifier7 => NULL -- Can be utilized for displaying more information
                         --p_identifier8 => NULL -- Can be utilized for displaying more information
                         p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
               --
               END;                                             --4.1 th Begin
            ELSE
               l_error_flag := 'Y';
            END IF;

            IF c_order_validate_rec.customer_name IS NOT NULL
            THEN
               BEGIN                                             -- 5.2 begin
                  SELECT cust_account_id
                    INTO c_order_validate_rec.sold_to_org_id
                    FROM hz_cust_accounts hca, hz_parties hp
                   WHERE hca.party_id = hp.party_id
                     AND hp.party_name = c_order_validate_rec.customer_name;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.h
                        (   ' Exception occured while validating Customer name.Oracle Error-'
                         || SQLERRM
                        );
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00003'
                       , p_identifier2         =>    'Exception occured while validating Customer Name.Oracle Error-'
                                                  || SQLERRM
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_validate_rec.customer_name
                       , p_identifier6         => c_order_validate_rec.ship_from_org
                       ,
                         --p_identifier7 => NULL -- Can be utilized for displaying more information
                         --p_identifier8 => NULL -- Can be utilized for displaying more information
                         p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
               --
               END;                                               --5.2 th end
            ELSE
               l_error_flag := 'Y';
            END IF;

            BEGIN                                                     -- 5.2.1
               SELECT hcsu.site_use_id
                 INTO l_site_id
                 FROM hz_cust_site_uses_all hcsu
                    , hz_cust_acct_sites_all hcas
                    , hz_cust_accounts hca
                    , hz_party_sites hps
                WHERE hcsu.primary_flag = 'Y'
                  AND hcsu.org_id = 204
                  AND hcsu.site_use_code = 'SHIP_TO'
                  AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
                  AND hca.cust_account_id = hcas.cust_account_id
                  AND hca.cust_account_id =
                                           c_order_validate_rec.sold_to_org_id
                  AND hps.party_id = hca.party_id
                  AND hps.party_site_id = hcas.party_site_id
                  AND ROWNUM < 2;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_error_flag := 'Y';
                  xx_trace.h
                     (   ' Exception occured while validating Customer site.Oracle Error-'
                      || SQLERRM
                     );
                  xx_emf.call_store_message
                     (p_message_group       => 'ERR_DTL'
                    , p_header_id           => l_header_id
                    , p_debug_value         => 20       -- low level debugging
                    , p_global_debug        => g_debug_level
                    , p_identifier1         => 'E-00003'
                    , p_identifier2         =>    'Exception occured while validating Customer site.Oracle Error-'
                                               || SQLERRM
                    , p_identifier3         => NULL
                    -- Can be utilized to store record serial number
                  ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                    , p_identifier5         => c_order_validate_rec.customer_name
                    , p_identifier6         => c_order_validate_rec.ship_from_org
                    ,
                      --p_identifier7 => NULL -- Can be utilized for displaying more information
                      --p_identifier8 => NULL -- Can be utilized for displaying more information
                      p_process_status      => l_return_value
                    , p_error_message       => l_output_message
                     );
            --
            END;                                                  --5.2 th end

            IF c_order_validate_rec.price_list IS NOT NULL
            THEN
               BEGIN                                             -- 5.3 begin
                  SELECT NAME
                    INTO c_order_validate_rec.price_list
                    FROM qp_list_headers
                   WHERE list_type_code = 'PRL'
                     AND NAME = c_order_validate_rec.price_list
                     AND NVL (end_date_active, SYSDATE) >= SYSDATE;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.h
                        (   ' Exception occured while validating Price List.Oracle Error-'
                         || SQLERRM
                        );
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 20    -- low level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-00003'
                       , p_identifier2         =>    'Exception occured while validating Price List.Oracle Error-'
                                                  || SQLERRM
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_validate_rec.price_list
                       ,
                         --p_identifier6         => c_order_validate_rec.ship_from_org,
                         --p_identifier7 => NULL -- Can be utilized for displaying more information
                         --p_identifier8 => NULL -- Can be utilized for displaying more information
                         p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
               --
               END;                                               --5.3 th end
            END IF;

            FOR c_line_validate_rec IN
               c_line_validate (c_order_validate_rec.orig_sys_document_ref)
            LOOP
               --
               l_processed_line_recs := l_processed_line_recs + 1;
               l_processed_recs := l_processed_recs + 1;

               IF (c_line_validate_rec.inventory_item IS NOT NULL)
               THEN
                  BEGIN
                                                   --5.3th Begin
                     --
                     SELECT inventory_item_id
                       INTO c_line_validate_rec.inventory_item_id
                       FROM mtl_system_items_b
                      WHERE segment1 = c_line_validate_rec.inventory_item
                        AND organization_id =
                                         c_order_validate_rec.ship_from_org_id;
                  --
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        l_error_flag := 'Y';
                        xx_trace.h ('Item invalid for organization.');
                        xx_emf.call_store_message
                           (p_message_group       => 'ERR_DTL'
                          , p_header_id           => l_header_id
                          , p_debug_value         => 20 -- low level debugging
                          , p_global_debug        => g_debug_level
                          , p_identifier1         => 'E-00003'
                          , p_identifier2         => 'Item invalid for organization'
                          , p_identifier3         => NULL
                          -- Can be utilized to store record serial number
                        ,   p_identifier4         => c_line_validate_rec.orig_sys_document_ref
                          , p_identifier5         => c_line_validate_rec.orig_sys_line_ref
                          , p_identifier6         => c_line_validate_rec.orig_sys_shipment_ref
                          , p_identifier7         => c_line_validate_rec.inventory_item
                          , p_identifier8         => c_order_validate_rec.ship_from_org
                          --p_identifier7 => NULL -- Can be utilized for displaying more information
                          --p_identifier8 => NULL -- Can be utilized for displaying more information
                        ,   p_process_status      => l_return_value
                          , p_error_message       => l_output_message
                           );
                  END;                                          -- 5.3th Begin
               ELSE
                  l_error_flag := 'Y';
               END IF;

               IF (c_line_validate_rec.order_quantity_uom IS NOT NULL)
               THEN
                  --Validate primary_unit_of_measure
                  BEGIN                                        --5.4 th Begin
                     --
                     SELECT uom_code
                       INTO c_line_validate_rec.order_quantity_uom
                       FROM mtl_units_of_measure
                      WHERE uom_code = c_line_validate_rec.order_quantity_uom;
                  --
                  EXCEPTION
                     WHEN OTHERS
                     THEN
                        l_error_flag := 'Y';
                        xx_trace.h
                                  (   'Invalid Unit of Measure.Oracle Error-'
                                   || SQLERRM
                                  );
                        xx_emf.call_store_message
                           (p_message_group       => 'ERR_DTL'
                          , p_header_id           => l_header_id
                          , p_debug_value         => 20
                          , p_global_debug        => g_debug_level
                          , p_identifier1         => 'E-00003'
                          , p_identifier2         =>    'Invalid Unit of Measure.Oracle Error-'
                                                     || SQLERRM
                          , p_identifier3         => NULL
                          -- Can be utilized to store record serial number
                        ,   p_identifier4         => c_line_validate_rec.orig_sys_document_ref
                          , p_identifier5         => c_line_validate_rec.orig_sys_line_ref
                          , p_identifier6         => c_line_validate_rec.orig_sys_shipment_ref
                          , p_identifier7         => c_line_validate_rec.order_quantity_uom
                          , p_identifier8         => c_order_validate_rec.ship_from_org
                          ,
                            --p_identifier7 => NULL -- Can be utilized for displaying more information
                            --p_identifier8 => NULL -- Can be utilized for displaying more information
                            p_process_status      => l_return_value
                          , p_error_message       => l_output_message
                           );
                  END;                                          --5.4 th Begin
               END IF;

               IF l_error_flag = 'N'
               THEN
                  UPDATE xx_ont_lines_preint
                     SET inventory_item_id =
                                         c_line_validate_rec.inventory_item_id
                   WHERE orig_sys_line_ref =
                                         c_line_validate_rec.orig_sys_line_ref;
               END IF;

               IF l_error_flag <> 'Y'
               THEN
                  l_successful_recs := l_successful_recs + 1;
               END IF;
            END LOOP;

/*------------------------------------------------------------------------------
Updating pre interface table with status 'ER' where record_status in('NW','PF')
-------------------------------------------------------------------------------*/

            IF l_error_flag = 'Y'
            THEN
               BEGIN                                           -- 5.5th begin
                  --Update Items Pre-Interface
                  UPDATE xx_ont_order_preint
                     SET status = 'ER'
                   WHERE orig_sys_document_ref =
                                    c_order_validate_rec.orig_sys_document_ref;

                  --Update Category Pre-Interface
                  UPDATE xx_ont_lines_preint
                     SET status = 'ER'
                   WHERE orig_sys_document_ref =
                                    c_order_validate_rec.orig_sys_document_ref;
               EXCEPTION
                  WHEN OTHERS
                  THEN
                     l_error_flag := 'Y';
                     xx_trace.h
                        (   ' Exception While Updating Pre-Interface Table Status.Oracle Error-'
                         || SQLERRM
                        );
                     xx_emf.call_store_message
                        (p_message_group       => 'ERR_DTL'
                       , p_header_id           => l_header_id
                       , p_debug_value         => 40   -- High level debugging
                       , p_global_debug        => g_debug_level
                       , p_identifier1         => 'E-12300'
                       , p_identifier2         =>    'Exception While Updating Pre-Interface Table Status.Oracle Error-'
                                                  || SQLERRM
                       , p_identifier3         => NULL
                       -- Can be utilized to store record serial number
                     ,   p_identifier4         => c_order_validate_rec.orig_sys_document_ref
                       , p_identifier5         => c_order_validate_rec.order_source
                       , p_identifier6         => c_order_validate_rec.ship_from_org
                       ,
                         --p_identifier7 => NULL -- Can be utilized for displaying more information
                         --p_identifier8 => NULL -- Can be utilized for displaying more information
                         p_process_status      => l_return_value
                       , p_error_message       => l_output_message
                        );
               END;                                               -- 5.5th end
            ELSE
               l_successful_recs := l_successful_recs + 1;

               UPDATE xx_ont_order_preint
                  SET order_source_id = l_order_source_id
                    , ship_from_org_id = c_order_validate_rec.ship_from_org_id
                    , sold_to_org_id = c_order_validate_rec.sold_to_org_id
                    , ship_to_org_id = c_order_validate_rec.ship_to_org_id
                WHERE orig_sys_document_ref =
                                    c_order_validate_rec.orig_sys_document_ref;

               l_sold_to_org_id := c_order_validate_rec.sold_to_org_id;
            END IF;

            l_error_flag := 'N';
         END LOOP;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_flag := 'Y';
            xx_trace.h
               (   'Unexpected Exception While Business Validation.Oracle Error-'
                || SQLERRM
               );
            xx_emf.call_store_message
               (p_message_group       => 'ERR_DTL'
              , p_header_id           => l_header_id
              , p_debug_value         => 40            -- High level debugging
              , p_global_debug        => g_debug_level
              , p_identifier1         => 'E-12300'
              , p_identifier2         =>    'Unexpected Exception While Business Validation.Oracle Error-'
                                         || SQLERRM
              , p_process_status      => l_return_value
              , p_error_message       => l_output_message
               );
--End of Business Validation Section
      END;                                                      --5.0 th Begin

  /*---------------------------------------------------------------------------------------
Move data into Oracle Interface Table / Public API
---------------------------------------------------------------------------------------*/

--Insert all data into oracle interface table from pre interface table
      BEGIN
         --6th Begin
         FOR c_order_validate_rec IN c_order_validate
         LOOP
            INSERT INTO oe_headers_iface_all
                        (order_source_id
                       , orig_sys_document_ref
                       , order_type
                       , price_list
                       , ship_from_org_id
                       , customer_name
                       , sold_to_org_id
                       , ship_to_org_id
                       , operation_code
                       , created_by
                       , creation_date
                       , last_update_login
                       , last_updated_by
                       , last_update_date
                        )
                 VALUES (c_order_validate_rec.order_source_id
                       , c_order_validate_rec.orig_sys_document_ref
                       , c_order_validate_rec.order_type
                       , c_order_validate_rec.price_list
                       , c_order_validate_rec.ship_from_org_id
                       , c_order_validate_rec.customer_name
                       , c_order_validate_rec.sold_to_org_id
                       , c_order_validate_rec.ship_to_org_id
                       , c_order_validate_rec.operation_code
                       , c_order_validate_rec.created_by
                       , c_order_validate_rec.creation_date
                       , c_order_validate_rec.last_update_login
                       , c_order_validate_rec.last_updated_by
                       , c_order_validate_rec.last_update_date
                        );

            FOR c_line_validate_rec IN
               c_line_validate (c_order_validate_rec.orig_sys_document_ref)
            LOOP
               INSERT INTO oe_lines_iface_all
                           (order_source_id
                          , orig_sys_document_ref
                          , orig_sys_line_ref
                          , orig_sys_shipment_ref
                          , ship_from_org_id
                          , sold_to_org_id
                          , ship_to_org_id
                          , inventory_item_id
                          , ordered_quantity
                          , order_quantity_uom
                          , unit_selling_price
                          , delivery_lead_time
                          , delivery_id
                          , request_date
                          , created_by
                          , creation_date
                          , last_update_login
                          , last_updated_by
                          , last_update_date
                           )
                    VALUES (c_order_validate_rec.order_source_id
                          , c_line_validate_rec.orig_sys_document_ref
                          , c_line_validate_rec.orig_sys_line_ref
                          , c_line_validate_rec.orig_sys_shipment_ref
                          , c_order_validate_rec.ship_from_org_id
                          , c_order_validate_rec.sold_to_org_id
                          , c_order_validate_rec.ship_to_org_id
                          , c_line_validate_rec.inventory_item_id
                          , c_line_validate_rec.ordered_quantity
                          , c_line_validate_rec.order_quantity_uom
                          , c_line_validate_rec.unit_selling_price
                          , c_line_validate_rec.delivery_lead_time
                          , c_line_validate_rec.delivery_id
                          , c_line_validate_rec.request_date
                          , c_line_validate_rec.created_by
                          , c_line_validate_rec.creation_date
                          , c_line_validate_rec.last_update_login
                          , c_line_validate_rec.last_updated_by
                          , c_line_validate_rec.last_update_date
                           );

               l_ord_insert_rec_count := l_ord_insert_rec_count + 1;
            END LOOP;
         END LOOP;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_flag := 'Y';
            xx_trace.h
               (   'Exception While Inserting into ORDER INTERFACE TABLES.Oracle Error-'
                || SQLERRM
               );
            xx_emf.call_store_message
               (p_message_group       => 'ERR_DTL'
              , p_header_id           => l_header_id
              , p_debug_value         => 20             -- low level debugging
              , p_global_debug        => g_debug_level
              , p_identifier1         => 'E-00004'
              , p_identifier2         =>    'Exception While Inserting into ORDER INTERFACE TABLES.Oracle Error-'
                                         || SQLERRM
              , p_identifier3         => NULL
              -- Can be utilized to store record serial number
            ,   p_process_status      => l_return_value
              , p_error_message       => l_output_message
               );
            RAISE;
      END;                                                           --6th end

       /*----------------------------------------------------------------------------------
      --Updating pre interface table with status 'PR' where record_status in IP'
      ----------------------------------------------------------------------------------*/

      BEGIN                                                       --6 th Begin
         UPDATE xx_ont_order_preint
            SET status = 'PR'
          WHERE status = 'IP';

         UPDATE xx_ont_lines_preint
            SET status = 'PR'
          WHERE status = 'IP';

         xx_trace.l ('Pre-Interface Table status updated to PR');
      EXCEPTION
         WHEN OTHERS
         THEN
            l_error_flag := 'Y';
            xx_trace.h
               (   ' Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
                || SQLERRM
               );
            xx_emf.call_store_message
               (p_message_group       => 'ERR_DTL'
              , p_header_id           => l_header_id
              , p_debug_value         => 40            -- High level debugging
              , p_global_debug        => g_debug_level
              , p_identifier1         => 'E-00004'
              , p_identifier2         =>    'Exception While Updating Pre-Interface Table Status to PR.Oracle Error-'
                                         || SQLERRM
              , p_identifier3         => NULL
              -- Can be utilized to store record serial number
            ,   p_process_status      => l_return_value
              , p_error_message       => l_output_message
               );
      END;

      --Initialize apps
       --fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
      xx_common_validations_pkg.init_apps_params
                                        (p_request_id          => l_request_id
                                       , p_process_status      => l_process_status
                                       , p_error_message       => l_output_message
                                        );

      /*---------------------------------------------------------------------------------------
            Launch Standard Concurrent Program - With WAIT/Call Oracle Standard APIs
      ----------------------------------------------------------------------------------------*/

      BEGIN
         IF l_ord_insert_rec_count > 0
         THEN
            l_standard_request_id :=
               fnd_request.submit_request (application      => 'ONT'
                                         , program          => 'OEOIMP'
                                         , description      => NULL
                                         , start_time       => NULL
                                         , sub_request      => FALSE
                                         , argument1        => l_order_source_id
                                         , argument2        => NULL
                                         --SB l_orig_sys_document_ref
               ,                           argument3        => NULL
                                         , argument4        => 'N'
                                         , argument5        => 1
                                         , argument6        => 4
                                         , argument7        => NULL
                                         --SB l_sold_to_org_id
               ,                           argument8        => NULL
                                         , argument9        => NULL
                                         , argument10       => 'Y'
                                         , argument11       => 'N'
                                          );
            COMMIT;
            --Wait for the completion of the concurrent request (if submitted successfully)
            l_completed :=
               fnd_concurrent.wait_for_request
                                         (request_id      => l_standard_request_id
                                        , INTERVAL        => 60
                                        , max_wait        => 0
                                        , phase           => l_phase
                                        , status          => l_vstatus
                                        , dev_phase       => l_dev_phase
                                        , dev_status      => l_dev_status
                                        , MESSAGE         => l_message
                                         );
            xx_trace.l (   'Request submitted with request id-'
                        || l_standard_request_id
                       );
         ELSE
            xx_emf.call_store_message
                (p_message_group       => 'ERR_DTL'
               , p_header_id           => l_header_id
               , p_debug_value         => 20             --Low level debugging
               , p_global_debug        => g_debug_level
               , p_identifier1         => 'E-00004'
               , p_identifier2         => 'No records To Process in Interface Table'
               , p_identifier3         => NULL
               -- Can be utilized to store record serial number
            ,    p_process_status      => l_return_value
               , p_error_message       => l_output_message
                );
         END IF;
      END;

/*------------------------------------------------------------------------------------------------
Purpose -Delete successfully processed records from pre-interface table
----------------------------------------------------------------------------------------------------*/

      BEGIN
--
         DELETE FROM xx_ont_order_preint
               WHERE status = 'PR';

--
         DELETE FROM xx_ont_lines_preint
               WHERE status = 'PR';
      EXCEPTION
         WHEN OTHERS
         THEN
            xx_trace.h
               (   'Exception while deleting records from pre-interface table'
                || SQLERRM
               );
      END;

          /*----------------------------------------------------------------------------
      Call Insert Summary Count
      Purpose: Display a summary of records processed, successful and in error
      ----------------------------------------------------------------------------*/

      l_error_recs := l_processed_recs - l_successful_recs;
      l_cnt_error_recs := l_cnt_processed_recs - l_cnt_successful_recs;
      l_return_value := NULL;
      --Call Insert Summary Count For Control Validation
      l_return_value :=
         xx_emf.insert_summary_counts
                                  (p_header_id            => l_header_id
                                 , p_display_name         =>    l_package_name
                                                             || '-Control Validation'
                                 , p_total_recs           => l_cnt_processed_recs
                                 , p_successful_recs      => l_cnt_successful_recs
                                 , p_error_recs           => l_cnt_error_recs
                                  );
      l_return_value := NULL;
      --Call Insert Summary Count For Business Validation
      l_return_value :=
         xx_emf.insert_summary_counts
                                    (p_header_id            => l_header_id
                                   , p_display_name         =>    l_package_name
                                                               || '-Business Validation'
                                   , p_total_recs           => l_processed_recs
                                   , p_successful_recs      => l_successful_recs
                                   , p_error_recs           => l_error_recs
                                    );

      --
      IF l_return_value = 1
      THEN
         l_warning_flag := 'W';
         xx_trace.l ('Failed to Insert Summary Count');
      END IF;

        /*----------------------------------------------------------------------------
      Call Insert Summary Count
      Purpose: Display a summary of records processed, successful and in error
      ----------------------------------------------------------------------------*/

      l_error_recs := l_processed_recs - l_successful_recs;
      l_return_value := NULL;
      --
      l_return_value :=
         xx_emf.insert_summary_counts (p_header_id            => l_header_id
                                     , p_display_name         => l_program_name
                                     , p_total_recs           => l_processed_recs
                                     , p_successful_recs      => l_successful_recs
                                     , p_error_recs           => l_error_recs
                                      );

      --
      IF l_return_value = 1
      THEN
         l_warning_flag := 'W';
         xx_trace.l ('Failed to Insert Summary Count');
      END IF;

/*-----------------------------------------------------------------------------------------
      Call flush PL/SQL table section
      Purpose : Flush data from pl/sql table isrespective of the count.
      Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/

      xx_emf.call_store_message (p_message_group       => 'ALL'
                               , p_header_id           => l_header_id
                               , p_debug_value         => 20
                               , p_global_debug        => g_debug_level
                               , p_identifier1         => 'Flushing Data into EMF'
                               , p_process_status      => l_return_value
                               , p_error_message       => l_output_message
                                );

      IF l_return_value = 1
      THEN
         l_warning_flag := 'W';
         xx_trace.l ('Failed to print EMF messages on the output');
      END IF;

      --
      l_messages_rec := l_null_rec;
      l_return_value := NULL;
        /*----------------------------------------------------------------------------
         Set display width for output
         Purpose: Call to set EMF error and report section column widths
      --------------------------------------------------------------------------*/

      l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
      l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
      l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
      l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
      l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
      l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
      l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
      l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
       --
      /*----------------------------------------------------------------
      Call to xx_emf.display_output_messages
         Purpose--Call to generate output file (Errors/Messages)
      --------------------------------------------------------------*/

      l_err_width_rec.header_id := l_header_id;
      l_return_value :=
         xx_emf.display_output_messages (p_header_id          => l_header_id
                                       , p_err_width_rec      => l_err_width_rec
                                        );

      IF l_return_value = 1
      THEN
         l_error_flag := 'Y';
         xx_trace.l ('Failed to display output messages');
      END IF;

      --
      l_return_value := NULL;

      --
      /*---------------------------------------------------
      Call ToEMF Update_program_status in EMF tables
      -----------------------------------------------------*/

      IF l_error_flag = 'Y'
      THEN                                                             --ERROR
         l_warning_flag := '';              --error status supercedes warning
         l_status := 'ERROR';
         l_return_value :=
            xx_emf.update_program_status (p_header_id      => l_header_id
                                        , p_status         => l_status
                                         );

         --
         IF l_return_value = 1
         THEN
            xx_trace.l ('Failed to update program status');
            l_return_value := NULL;
         END IF;
      ELSIF l_warning_flag = 'W'
      THEN
         l_status := 'WARNING';
         l_return_value :=
            xx_emf.update_program_status (p_header_id      => l_header_id
                                        , p_status         => l_status
                                         );

         --
         IF l_return_value = 1
         THEN
            xx_trace.l ('Failed to update program status');
            l_return_value := NULL;
         END IF;
      ELSIF l_error_flag = 'N'
      THEN
         l_status := 'SUCCESS';
         l_return_value := NULL;
         l_return_value :=
            xx_emf.update_program_status (p_header_id      => l_header_id
                                        , p_status         => l_status
                                         );

         --
         IF l_return_value = 1
         THEN
            xx_trace.l ('Failed to update program status for Success');
         END IF;

         COMMIT;
      END IF;
   EXCEPTION                                                  --Main Exception
--Call emf_initialize_error if EMF header fails
      WHEN e_emf_initialize_error
      THEN
         xx_trace.l ('Failed to initialize EMF');
--Call emf_initialize_detail_error if EMF error/report detail fails
      WHEN e_emf_initialize_detail_error
      THEN
         l_status := 'ERROR';
         l_return_value :=
                         xx_emf.update_program_status (l_header_id, l_status);
         xx_trace.l ('Failed to insert EMF error header');
--Main Exception Block
      WHEN OTHERS
      THEN
--Write to error
         l_error_flag := 'Y';
         xx_emf.call_store_message (p_message_group       => 'ERR_DTL'
                                  , p_header_id           => l_header_id
                                  , p_debug_value         => 40
                                  --High level debugging
         ,                          p_global_debug        => g_debug_level
                                  , p_identifier1         => 'E-00004'
                                  , p_identifier2         =>    'Exception in Main.'
                                                             || SQLERRM
                                  , p_process_status      => l_return_value
                                  , p_error_message       => l_output_message
                                   );
         --
         l_messages_rec := l_null_rec;
         l_return_value := NULL;
/*-------------------------------------------------------------------------------------
    Call To Insert Summary Count
------------------------------------------------------------------------------------*/

         l_return_value :=
            xx_emf.insert_summary_counts
                                      (p_header_id            => l_header_id
                                     , p_display_name         => l_program_name
                                     , p_total_recs           => l_processed_recs
                                     , p_successful_recs      => l_successful_recs
                                     , p_error_recs           => l_error_recs
                                      );

         IF l_return_value = 1
         THEN
            xx_trace.h ('Unable to insert summary counts');
         END IF;

/*-----------------------------------------------------------------------------------------
    Call flush PL/SQL table section
    Purpose : Flush data from pl/sql table isrespective of the count.
    Special Logic : record's message group is passed as 'ALL' and rest all columns are null
-------------------------------------------------------------------------------------------*/

         xx_emf.call_store_message (p_message_group       => 'ALL'
                                  , p_header_id           => l_header_id
                                  , p_debug_value         => 20
                                  --Low level debugging
         ,                          p_global_debug        => g_debug_level
                                  , p_identifier1         => 'Flushing Data into EMF'
                                  , p_process_status      => l_return_value
                                  , p_error_message       => l_output_message
                                   );

         IF l_return_value = 1
         THEN
            l_return_value := NULL;
            xx_trace.h ('Unable to call EMF error log');
            l_return_value :=
                         xx_emf.update_program_status (l_header_id, l_status);
         END IF;

         l_return_value := NULL;
         /*----------------------------------------------------------------------------
            Purpose: Call to set EMF error and report section column widths
          --------------------------------------------------------------------------*/

         l_err_width_rec.identifier1 := TO_CHAR (g_err_col1_width);
         l_err_width_rec.identifier2 := TO_CHAR (g_err_col2_width);
         l_err_width_rec.identifier3 := TO_CHAR (g_err_col3_width);
         l_err_width_rec.identifier4 := TO_CHAR (g_err_col4_width);
         l_err_width_rec.identifier5 := TO_CHAR (g_err_col5_width);
         l_err_width_rec.identifier6 := TO_CHAR (g_err_col6_width);
         l_err_width_rec.identifier7 := TO_CHAR (g_err_col7_width);
         l_err_width_rec.identifier8 := TO_CHAR (g_err_col8_width);
         /*----------------------------------------------------------------
           Purpose--Call to generate output file (Errors/Messages)
         --------------------------------------------------------------*/

         l_err_width_rec.header_id := l_header_id;
         l_return_value :=
            xx_emf.display_output_messages (p_header_id          => l_header_id
                                          , p_err_width_rec      => l_err_width_rec
                                           );

         IF l_return_value = 1
         THEN
            xx_trace.h ('Unable to display output messages');
         END IF;

         l_return_value := NULL;
/*-------------------------------------------------------------------
  Call update program status
---------------------------------------------------------------------*/

         l_status := 'ERROR';
         l_return_value :=
            xx_emf.update_program_status (p_header_id      => l_header_id
                                        , p_status         => l_status
                                         );

         IF l_return_value = 1
         THEN
            xx_trace.h ('Unable to update program status');
            l_return_value := NULL;
         END IF;
   END main;                                                       --0th Begin
END xx_ont_oe_orders_imp_pkg;
/


No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...