CREATE OR REPLACE PROCEDURE xxxx_process_bom_interface (
   ptnv_errbuf    OUT   VARCHAR2,
   ptnv_errcode   OUT   VARCHAR2
)
AS
   CURSOR bom_items
   IS
      SELECT DISTINCT UPPER (bom_item) bom_item
                 FROM xxxx_bom_interface
                WHERE status IS NULL;

   CURSOR comps_data (p_bom_item VARCHAR2)
   IS
      SELECT DISTINCT UPPER (component_item) component_item, component_qty
                 FROM xxxx_bom_interface b
                WHERE UPPER (bom_item) = p_bom_item AND status IS NULL
             ORDER BY component_item;

   CURSOR get_orgs
   IS
      SELECT organization_code, organization_id
        FROM mtl_parameters
       WHERE organization_id = 24;

   --ORDER BY 2 DESC;
   error_flag                   BOOLEAN                              := FALSE;
   l_inventory_item_id          mtl_system_items.inventory_item_id%TYPE;
   l_bom_header_rec             bom_bo_pub.bom_head_rec_type            := bom_bo_pub.g_miss_bom_header_rec;
   l_bom_revision_tbl           bom_bo_pub.bom_revision_tbl_type        := bom_bo_pub.g_miss_bom_revision_tbl;
   l_bom_component_tbl          bom_bo_pub.bom_comps_tbl_type           := bom_bo_pub.g_miss_bom_component_tbl;
   l_bom_ref_designator_tbl     bom_bo_pub.bom_ref_designator_tbl_type  := bom_bo_pub.g_miss_bom_ref_designator_tbl;
   l_bom_sub_component_tbl      bom_bo_pub.bom_sub_component_tbl_type   := bom_bo_pub.g_miss_bom_sub_component_tbl;
   l_bom_comp_ops_tbl           bom_bo_pub.bom_comp_ops_tbl_type        := Bom_Bo_Pub.G_MISS_BOM_COMP_OPS_TBL;
   l_error_message_list         error_handler.error_tbl_type;
   l_x_bom_header_rec           bom_bo_pub.bom_head_rec_type;
   l_x_bom_revision_tbl         bom_bo_pub.bom_revision_tbl_type;
   l_x_bom_component_tbl        bom_bo_pub.bom_comps_tbl_type;
   l_x_bom_ref_designator_tbl   bom_bo_pub.bom_ref_designator_tbl_type;
   l_x_bom_sub_component_tbl    bom_bo_pub.bom_sub_component_tbl_type;
   l_x_return_status            VARCHAR2 (2000);
   l_x_msg_count                NUMBER;
   --i                            NUMBER;
   k                            NUMBER                                   := 1;
   c                            NUMBER                                   := 0;
   l_cnt                        NUMBER                                   := 0;
BEGIN
   fnd_file.put_line
      (1,
       '----------------------ERROR Log Starts----------------------------- '
      );

   FOR i IN bom_items
   LOOP
      BEGIN
         SELECT inventory_item_id
           INTO l_inventory_item_id
           FROM mtl_system_items
          WHERE UPPER (segment1) = i.bom_item
            AND organization_id = 25
            AND UPPER (enabled_flag) = 'Y';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            error_flag := TRUE;

            UPDATE xxxx_bom_interface
               SET status = 'Error BOM item not found  '
             WHERE UPPER (bom_item) = i.bom_item;

            fnd_file.put_line
                  (1,
                      'Error BOM item not found-Item not set up in Inventory '
                   || i.bom_item
                  );
         WHEN OTHERS
         THEN
            error_flag := TRUE;

            UPDATE xxxx_bom_interface
               SET status = 'Error validating BOM item '
             WHERE UPPER (bom_item) = i.bom_item;

            fnd_file.put_line (1, 'Error validating BOM item ' || i.bom_item);
      END;

      IF NOT error_flag
      THEN
         UPDATE xxxx_bom_interface
            SET bom_inventory_item_id = l_inventory_item_id
          WHERE UPPER (bom_item) = i.bom_item;
      END IF;

      BEGIN
         SELECT COUNT (*)
           INTO l_cnt
           FROM bom_bill_of_materials_v
          WHERE assembly_item_id = l_inventory_item_id
                AND organization_id = 25;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            l_cnt := 0;
         WHEN OTHERS
         THEN
            l_cnt := 0;
      END;

      IF l_cnt > 0
      THEN
         UPDATE xxxx_bom_interface
            SET status = 'Error BOM already defined for this item '
          WHERE UPPER (bom_item) = i.bom_item;

         fnd_file.put_line (1,
                               'Error BOM already defined for this item '
                            || i.bom_item
                           );
      END IF;

      FOR j IN comps_data (i.bom_item)
      LOOP
         BEGIN
            SELECT inventory_item_id
              INTO l_inventory_item_id
              FROM mtl_system_items
             WHERE UPPER (segment1) = j.component_item
               AND organization_id = 25
               AND UPPER (enabled_flag) = 'Y';
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               error_flag := TRUE;

               UPDATE xxxx_bom_interface
                  SET status = 'Error Component item not found'
                WHERE UPPER (component_item) = j.component_item;

               fnd_file.put_line (1,
                                     'Item not set up in Inventory '
                                  || j.component_item
                                 );
            WHEN OTHERS
            THEN
               error_flag := TRUE;

               UPDATE xxxx_bom_interface
                  SET status = 'Error BOM item not found'
                WHERE UPPER (component_item) = j.component_item;

               fnd_file.put_line (1,
                                  'Error validating Item ' || j.component_item
                                 );
         END;

         IF NOT error_flag
         THEN
            UPDATE xxxx_bom_interface
               SET comp_inventory_item_id = l_inventory_item_id
             WHERE UPPER (component_item) = j.component_item;
         END IF;

         BEGIN
            SELECT COUNT (*)
              INTO l_cnt
              FROM bom_inventory_components c,
                   mtl_system_items msic,
                   bom_bill_of_materials b,
                   mtl_system_items msib
             WHERE 1 = 1
               AND c.component_item_id = msic.inventory_item_id
               AND msic.organization_id = 25
               AND c.bill_sequence_id = b.bill_sequence_id
               AND msib.segment1 = i.bom_item
               AND msib.inventory_item_id = b.assembly_item_id
               AND msib.organization_id = 25
               AND b.organization_id = msib.organization_id
               AND c.component_item_id = l_inventory_item_id;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               l_cnt := 0;
            WHEN OTHERS
            THEN
               l_cnt := 0;
         END;

         IF l_cnt > 0
         THEN
            UPDATE xxxx_bom_interface
               SET status = 'Error Component already present for this BOM'
             WHERE UPPER (component_item) = j.component_item;
         END IF;
      END LOOP;
   END LOOP;

   fnd_file.put_line
       (1,
        '----------------------ERROR Log End-------------------------------- '
       );
   --  Need to initialize for calling BOM API
   --  Each database table that the program writes to requires system information, such as who is
   --  trying to update the current record. User must provide this information to the import program
   --  initializing certain variables. To initialize the varables the user must call the following
   --  procedure.
   --fnd_global.apps_initialize (1001255, 50326, 700, 0);

   FOR l IN get_orgs
   LOOP
      FOR i IN bom_items
      LOOP
      fnd_global.apps_initialize (1001255, 50326, 700, 0);
--      l_bom_header_rec       := bom_bo_pub.g_miss_bom_header_rec;
--   l_bom_revision_tbl        := bom_bo_pub.g_miss_bom_revision_tbl;
--   l_bom_component_tbl       := bom_bo_pub.g_miss_bom_component_tbl;
--   l_bom_ref_designator_tbl  := bom_bo_pub.g_miss_bom_ref_designator_tbl;
--   l_bom_sub_component_tbl   := bom_bo_pub.g_miss_bom_sub_component_tbl;
         fnd_file.put_line (1, 'Processing BOM for item ' || i.bom_item);
         fnd_file.put_line (1,
                               'Processing BOM for organization '
                            || l.organization_code
                           );
         --  Set the Values for the Bill.
         l_bom_header_rec.assembly_item_name := i.bom_item;
         l_bom_header_rec.organization_code := l.organization_code;
         l_bom_header_rec.assembly_type := 1;
         l_bom_header_rec.transaction_type := 'CREATE';
         l_bom_header_rec.return_status := NULL;
         c := 0;

         FOR j IN comps_data (i.bom_item)
         LOOP
            c := c + 1;
            fnd_file.put_line (1,
                                  'Adding component '
                               || j.component_item
                               || ' for BOM Item '
                               || i.bom_item
                              );
            --  Set the Values for the Components.
            l_bom_component_tbl (k).organization_code :=  l_bom_header_rec.organization_code;
            l_bom_component_tbl (k).assembly_item_name := l_bom_header_rec.assembly_item_name;
            l_bom_component_tbl (k).start_effective_date := trunc(SYSDATE);
            l_bom_component_tbl (k).component_item_name := j.component_item;
            l_bom_component_tbl (k).alternate_bom_code := NULL;
            l_bom_component_tbl (k).projected_yield := NULL;
            l_bom_component_tbl (k).planning_percent := NULL;
            l_bom_component_tbl (k).quantity_related := NULL;
            l_bom_component_tbl (k).check_atp := NULL;
            l_bom_component_tbl (k).include_in_cost_rollup := NULL;
            l_bom_component_tbl (k).wip_supply_type := NULL;
            l_bom_component_tbl (k).so_basis := NULL;
            l_bom_component_tbl (k).optional := NULL;
            l_bom_component_tbl (k).mutually_exclusive := NULL;
            l_bom_component_tbl (k).shipping_allowed := NULL;
            l_bom_component_tbl (k).required_to_ship := NULL;
            l_bom_component_tbl (k).required_for_revenue := NULL;
            l_bom_component_tbl (k).include_on_ship_docs := NULL;
            l_bom_component_tbl (k).supply_subinventory := NULL;
            l_bom_component_tbl (k).location_name := NULL;
            l_bom_component_tbl (k).minimum_allowed_quantity := NULL;
            l_bom_component_tbl (k).maximum_allowed_quantity := NULL;
            l_bom_component_tbl (k).comments := NULL;
            l_bom_component_tbl (k).from_end_item_unit_number := NULL;
            l_bom_component_tbl (k).to_end_item_unit_number := NULL;
            l_bom_component_tbl (k).item_sequence_number := (c * 10);
            l_bom_component_tbl (k).operation_sequence_number := 1;
            l_bom_component_tbl (k).transaction_type := 'CREATE';
            l_bom_component_tbl (k).quantity_per_assembly := j.component_qty;
            l_bom_component_tbl (k).return_status := NULL;
            k := k + 1;
         END LOOP;

         fnd_file.put_line (1, 'done Adding component ');
         error_handler.initialize;
         --  Call the Public API
         --  The public API is the user's interface to the import program. The user must call it
         --  programatically, while sending in one business object at a time. The public API returns
         --  the processed business object, the business object status, and a count of all
         --  associated error and warning messages.
         bom_bo_pub.process_bom
                      (p_bo_identifier               => 'BOM',
                       p_api_version_number          => 1.0
                                                           --  This parameter is required. It is used by the
                                                           --  API to compare the version number of incoming
                                                           --  calls to its current version number.
         ,
                       p_init_msg_list               => TRUE
                                                            --  This parameter is set to TRUE, allows callers to
                                                            --  to request that the API do the initialization
                                                            --  of message list on their behalf.
         ,
                       p_bom_header_rec              => l_bom_header_rec
                                                                        --  This is a set of data structures that represent
                                                                        --  the incoming business objects. This is a record
                                                                        --  that holds the Bill of Materials header for the
                                                                        --  BOM
         ,
                       p_bom_revision_tbl            => l_bom_revision_tbl
                                                                          --  All the p*_tbl parameters are data structure
                                                                          --  that represent incoming business objects They
                                                                          --  are PL/SQL tables of records  that hold records
                                                                          --  for each of the other entities.
         ,
                       p_bom_component_tbl           => l_bom_component_tbl,
                       p_bom_ref_designator_tbl      => l_bom_ref_designator_tbl,
                       p_bom_sub_component_tbl       => l_bom_sub_component_tbl,
                       x_bom_header_rec              => l_x_bom_header_rec
                                                                          --  All the x*_tbl parameters are data structure
                                                                          --  that represent outgoing business objects They
                                                                          --  are PL/SQL tables of records  that hold records
                                                                          --  for each of the other entities except now they
                                                                          --  have all the changes that the import program
                                                                          -- made to it through all the steps.
         ,
                       x_bom_revision_tbl            => l_x_bom_revision_tbl,
                       x_bom_component_tbl           => l_x_bom_component_tbl,
                       x_bom_ref_designator_tbl      => l_x_bom_ref_designator_tbl,
                       x_bom_sub_component_tbl       => l_x_bom_sub_component_tbl,
                       x_return_status               => l_x_return_status
                                                                         --  This is a flag that indicates the state of the
                                                                         --  whole business object after the import.
                                                                         --  'S' - Success
                                                                         --  'E' - Error
                                                                         --  'F' - Fatal Error
                                                                         --  'U' - Unexpected Error
         ,
                       x_msg_count                   => l_x_msg_count
                                                                     --  This holds the number of messages in the API
                                                                     --  message stack after the import.
         ,
                       p_debug                       => 'N',
                       p_output_dir                  => '',
                       p_debug_filename              => ''
                      );
         fnd_file.put_line (1, 'done calling api ');
         fnd_file.put_line (1, 'Return Status = ' || l_x_return_status);
         fnd_file.put_line (1, 'Message Count = ' || l_x_msg_count);
         /**** Error messages ****/
         error_handler.get_message_list (l_error_message_list);

         IF l_x_return_status <> 'S'
         THEN
            --fnd_file.put_line (1, 'Rolling back for BOM item ' || i.bom_item);

            --  Error Processing
            FOR i IN 1 .. l_x_msg_count
            LOOP
               fnd_file.put_line
                               (1,
                                   TO_CHAR (i)
                                || ' MESSAGE TEXT  '
                                || SUBSTR
                                        (l_error_message_list (i).MESSAGE_TEXT,
                                         1,
                                         250
                                        )
                               );
               fnd_file.put_line (1,
                                     TO_CHAR (i)
                                  || ' MESSAGE TYPE  '
                                  || l_error_message_list (i).MESSAGE_TYPE
                                 );
            END LOOP;
         ELSE
            --fnd_file.put_line (1, 'Commiting for BOM item ' || i.bom_item);
            COMMIT;
         END IF;
      END LOOP;
   END LOOP;

   --ROLLBACK;
   BEGIN
      UPDATE xxxx_bom_interface b
         SET status = 'BOM loaded Susscessfully'
       WHERE bom_item IN (SELECT bom_item_number
                            FROM xxxx_bundles_v
                           WHERE bom_item_number = b.bom_item);
   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
   END;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      fnd_file.put_line (1, ' Error ' || SQLERRM);
      RAISE;
END xxxx_process_bom_interface;
/