APPS.INV_ITEM_CATALOG_ELEM_PUB SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 116

           l_item_desc_element_table.DELETE(icoi_rec_dup);

Line: 125

   INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 177

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 206

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 228

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 247

,  p_delete_rec_flag   IN   NUMBER
,  p_commit_flag       IN   NUMBER
,  p_prog_appid        IN   NUMBER
,  p_prog_id           IN   NUMBER
,  p_request_id        IN   NUMBER
,  p_user_id           IN   NUMBER
,  p_login_id          IN   NUMBER
)
IS
   l_api_name       CONSTANT  VARCHAR2(30)  := 'process_Item_Catalog_grp_recs';

Line: 264

      SELECT
         mdei_dup.rowid
      ,  mdei_dup.transaction_id
      ,  mdei_dup.element_name
      FROM
         mtl_desc_elem_val_interface  mdei_dup
      WHERE
         mdei_dup.rowid > (select rowid
                           FROM mtl_desc_elem_val_interface  mdei
                           WHERE mdei.set_process_id = g_xset_id
                           AND mdei.process_flag IN (1, 2)
                           AND rownum < 2
                           AND (mdei.inventory_item_id = mdei_dup.inventory_item_id
                           OR mdei.item_number = mdei_dup.item_number )
                           AND mdei.element_name = mdei_dup.element_name
                          )
      FOR UPDATE OF mdei_dup.transaction_id;

Line: 287

      SELECT
         mdei.rowid, mdei.transaction_id
      ,  mdei.inventory_item_id
      ,  mdei.element_name, mdei.element_value
      ,  mdei.element_sequence, mdei.item_number
      ,  mdei.default_element_flag
      FROM
         mtl_desc_elem_val_interface  mdei
      WHERE
         mdei.set_process_id = g_xset_id
         AND mdei.process_flag IN (1, 2, 4) --R12C
      ORDER BY mdei.item_number,mdei.inventory_item_id
      FOR UPDATE OF mdei.transaction_id;

Line: 377

   UPDATE mtl_desc_elem_val_interface  mdei
   SET    process_flag = 2
   WHERE
      mdei.set_process_id = g_xset_id
      AND  mdei.process_flag = 1;

Line: 392

     SELECT mtl_system_items_interface_s.NEXTVAL
       INTO l_transaction_id
       FROM dual;

Line: 452

           UPDATE mtl_desc_elem_val_interface
           SET
           transaction_id = l_transaction_id,
           request_id     = g_request_id,
           process_flag       =  3
           WHERE
           CURRENT OF icoi_csr;

Line: 460

           UPDATE mtl_desc_elem_val_interface
           SET
           inventory_item_id = l_inventory_item_id
           WHERE
           CURRENT OF icoi_csr;

Line: 474

     INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 483

     SELECT mtl_system_items_interface_s.NEXTVAL
       INTO l_transaction_id
       FROM dual;

Line: 497

       UPDATE mtl_desc_elem_val_interface
       SET
         transaction_id = l_transaction_id,
         request_id     = g_request_id,
         process_flag       =  3
       WHERE
         CURRENT OF icoi_csr_dup;

Line: 512

   INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 522

   SELECT mtl_system_items_interface_s.NEXTVAL
     INTO l_transaction_id
     FROM dual;

Line: 583

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 589

      INV_ITEM_MSG.Debug(Mctx, 'update interface record');

Line: 591

      UPDATE mtl_desc_elem_val_interface
      SET
         transaction_id     =  l_transaction_id
      ,  inventory_item_id  =  l_inventory_item_id
      ,  item_number        =  l_item_number
      ,  process_flag       =  l_process_flag
      ,  program_application_id  =  g_prog_appid
      ,  program_id         =  g_prog_id
      ,  program_update_date=  SYSDATE
      ,  request_id         =  g_request_id
      ,  last_update_date   =  SYSDATE
      ,  last_updated_by    =  g_user_id
      ,  last_update_login  =  g_login_id
      WHERE
         CURRENT OF icoi_csr;

Line: 618

   IF (p_delete_rec_flag = 1) THEN

      INV_ITEM_MSG.Debug(Mctx, 'calling delete_OI_records');

Line: 622

      INV_ITEM_CATALOG_ELEM_PUB.delete_OI_records
      (  p_commit         =>  l_commit
      ,  p_rec_set_id     =>  g_xset_id
      ,  x_return_status  =>  l_return_status
      );

Line: 628

      INV_ITEM_MSG.Debug(Mctx, 'done delete_OI_records: return_status=' || l_return_status);

Line: 635

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 637

   END IF;  -- p_delete_rec_flag = 1

Line: 659

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 684

      INV_ITEM_MSG.Write_List (p_delete => TRUE);

Line: 696

PROCEDURE delete_OI_records
(
   p_commit         IN   VARCHAR2
,  p_rec_set_id     IN   NUMBER
,  x_return_status  OUT  NOCOPY VARCHAR2
)
IS
   l_api_name       CONSTANT  VARCHAR2(30)  := 'delete_OI_records';

Line: 706

   l_del_process_flag    NUMBER  :=  7;  -- process_flag value for records to be deleted

Line: 718

     DELETE FROM mtl_desc_elem_val_interface
      WHERE  set_process_id = p_rec_set_id
        AND  process_flag = l_del_process_flag
        AND  rownum < G_ROWS_TO_COMMIT;

Line: 725

      INV_ITEM_MSG.Debug(Mctx, 'deleted ' || TO_CHAR(SQL%ROWCOUNT) || ' record(s)');

Line: 746

END delete_OI_records;