APPS.INV_ITEM_CATEGORY_PVT SQL Statements

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

Line: 15

c_INSERT    CONSTANT  VARCHAR2(3)  :=  'INS';

Line: 16

c_UPDATE    CONSTANT  VARCHAR2(3)  :=  'UPD';

Line: 17

c_DELETE    CONSTANT  VARCHAR2(3)  :=  'DEL';

Line: 86

      SELECT 'x',request_id, approval_status --2879647
      FROM  mtl_system_items_b
      WHERE  inventory_item_id = p_inventory_item_id
        AND  organization_id   = p_organization_id;

Line: 94

      SELECT  structure_id, validate_flag, mult_item_cat_assign_flag,
                                                        control_level
                                                        ,hierarchy_enabled--Bug: 2996160
      FROM  mtl_category_sets_b
      WHERE  category_set_id = p_category_set_id;

Line: 102

      SELECT  structure_id
      FROM  mtl_categories_b
      WHERE  category_id = p_category_id
        AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE;--Bug: 2996160

Line: 111

      SELECT 'x'
      FROM  mtl_category_set_valid_cats
      WHERE  category_set_id = p_category_set_id
        AND  category_id = p_category_id;

Line: 122

      SELECT  COUNT( category_id ), COUNT( DECODE(category_id, p_category_id,1, NULL) )
      FROM  mtl_item_categories
      WHERE
              inventory_item_id = p_inventory_item_id
         AND  organization_id   = p_organization_id
         AND  category_set_id = p_category_set_id;

Line: 202

   select Decode(Count(1), 0,'N','Y')
   INTO  l_is_gpc_catalog
   FROM  mtl_default_category_sets
   WHERE  functional_area_id = 21
   AND  category_set_id = p_category_set_id;

Line: 215

      SELECT COUNT(*) INTO l_default_cats
      FROM   MTL_DEFAULT_CATEGORY_SETS
      WHERE  CATEGORY_SET_ID = p_category_set_id;

Line: 299

         SELECT MASTER_ORGANIZATION_ID
         INTO           p_master_org_id
         FROM           mtl_parameters
         WHERE  organization_id = p_organization_id;

Line: 307

        (  p_Msg_Name        =>  'INV_CAT_CANNOT_CREATE_DELETE'
        ,  p_transaction_id  =>  p_transaction_id
        );

Line: 383

              DELETE FROM mtl_item_categories
               WHERE inventory_item_id = p_inventory_item_id
               AND category_set_id = p_category_set_id;

Line: 387

               DELETE FROM mtl_item_categories
               WHERE organization_id   = p_organization_id
               AND inventory_item_id = p_inventory_item_id
               AND category_set_id = p_category_set_id;

Line: 411

        INV_ITEM_MSG.Debug(Mctx, 'begin INSERT INTO mtl_item_categories');

Line: 417

         INSERT INTO mtl_item_categories
                (
                inventory_item_id
                , organization_id
                , category_set_id
                , category_id
                , creation_date
                , created_by
                , last_update_date
                , last_updated_by
                , last_update_login
                , request_id   --4105867
                )
                SELECT
                p_inventory_item_id
                , p.organization_id
                , p_category_set_id
                , p_category_id
                , SYSDATE
                , FND_GLOBAL.user_id
                , SYSDATE
                , FND_GLOBAL.user_id
                , FND_GLOBAL.login_id
                , FND_GLOBAL.conc_request_id
                FROM    mtl_parameters p , mtl_system_items_b i
                WHERE   p.master_organization_id = p_master_org_id
                  AND     i.inventory_item_id = p_inventory_item_id
                  AND     i.organization_id = p.organization_id
                  AND not exists
                  (SELECT 'x'
                   FROM    mtl_item_categories
                   whERE   inventory_item_id = p_inventory_item_id
                     AND   organization_id = p.organization_id
                     AND   category_set_id = p_category_set_id
                     AND   category_id = p_category_id);

Line: 453

       INSERT INTO mtl_item_categories
        (
          inventory_item_id
        , organization_id
        , category_set_id
        , category_id
        , creation_date
        , created_by
        , last_update_date
        , last_updated_by
        , last_update_login
        , request_id   --4105867
        )
        VALUES
        (
           p_inventory_item_id
        ,  p_organization_id
        ,  p_category_set_id
        ,  p_category_id
        ,  SYSDATE
        ,  FND_GLOBAL.user_id
        ,  SYSDATE
        ,  FND_GLOBAL.user_id
        ,  FND_GLOBAL.login_id
        ,  FND_GLOBAL.conc_request_id
        );

Line: 481

         INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');

Line: 553

PROCEDURE Delete_Category_Assignment
(
   p_api_version        IN   NUMBER
,  p_init_msg_list      IN   VARCHAR2  DEFAULT  fnd_api.g_FALSE
,  p_commit             IN   VARCHAR2  DEFAULT  fnd_api.g_FALSE
,  p_inventory_item_id  IN   NUMBER
,  p_organization_id    IN   NUMBER
,  p_category_set_id    IN   NUMBER
,  p_category_id        IN   NUMBER
,  p_transaction_id     IN   NUMBER
,  x_return_status      OUT  NOCOPY VARCHAR2
,  x_msg_count          OUT  NOCOPY NUMBER
,  x_msg_data           OUT  NOCOPY VARCHAR2
)
IS
   l_api_name        CONSTANT  VARCHAR2(30)  := 'Delete_Category_Assignment';

Line: 611

      SELECT  control_level,default_category_id--Bug:2527058
      FROM    mtl_category_sets_b
      WHERE   category_set_id = p_category_set_id;

Line: 617

      SELECT  structure_id
      FROM  mtl_categories_b
      WHERE  category_id = p_category_id;

Line: 626

     SELECT category_set_id
       FROM mtl_default_category_sets
      WHERE functional_area_id = cp_functional_area;

Line: 633

     SELECT gdsn_outbound_enabled_flag
       FROM mtl_system_items_b
      WHERE inventory_item_id = cp_inventory_item_id
        AND organization_id   = cp_organization_id;

Line: 641

   SAVEPOINT Delete_Category_Assignment_PVT;

Line: 733

     SELECT  count(category_id)
     INTO    cnt_cat
     FROM    mtl_item_categories
     WHERE   INVENTORY_ITEM_ID = p_inventory_item_id
     AND     ORGANIZATION_ID   = p_organization_id
     AND     CATEGORY_SET_ID   = p_category_set_id;

Line: 779

   SELECT MASTER_ORGANIZATION_ID
   INTO   p_master_org_id
   FROM   mtl_parameters
   WHERE  organization_id = p_organization_id;

Line: 787

        (  p_Msg_Name        =>  'INV_CAT_CANNOT_CREATE_DELETE'
        ,  p_transaction_id  =>  p_transaction_id
        );

Line: 795

          DELETE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
            FROM  mtl_item_categories MIC
           WHERE  category_set_id = p_category_set_id
             AND  category_id     = p_category_id
             AND  inventory_item_id = p_inventory_item_id
             AND  organization_id =
                  (SELECT organization_id
                     FROM mtl_parameters p
                    WHERE p.master_organization_id = p_master_org_id
                      AND p.organization_id = mic.organization_id);

Line: 808

                DELETE FROM mtl_item_categories
                WHERE organization_id   = p_organization_id
                  AND inventory_item_id = p_inventory_item_id
                  AND category_set_id   = p_category_set_id
                  AND category_id       = p_category_id;

Line: 826

      INV_ITEM_MSG.Debug(Mctx, 'after DELETE FROM mtl_item_categories');

Line: 842

      ROLLBACK TO Delete_Category_Assignment_PVT;

Line: 850

      ROLLBACK TO Delete_Category_Assignment_PVT;

Line: 858

      ROLLBACK TO Delete_Category_Assignment_PVT;

Line: 879

END Delete_Category_Assignment;

Line: 918

      SELECT 'x'
      FROM  mtl_Category_set_valid_cats VC
      WHERE  VC.category_set_id = p_category_set_id
        AND  VC.category_id = p_category_id
        AND NOT EXISTS
           (SELECT NULL FROM  mtl_Category_set_valid_cats
            WHERE parent_category_id = VC.category_id
              AND category_set_id = p_category_set_id);

Line: 931

      SELECT 'x'
      FROM  mtl_Category_set_valid_cats
      WHERE category_set_id = p_category_set_id
        AND parent_category_id = p_category_id ;

Line: 1011

  PROCEDURE Update_Valid_Category(
    p_api_version         IN  NUMBER,
    p_init_msg_list       IN  VARCHAR2 DEFAULT FND_API.G_FALSE,
    p_commit              IN  VARCHAR2 DEFAULT FND_API.G_FALSE,
    p_category_set_id     IN  NUMBER,
    p_category_id         IN  NUMBER,
    p_parent_category_id  IN  NUMBER,
    x_return_status       OUT  NOCOPY VARCHAR2,
    x_errorcode           OUT  NOCOPY NUMBER,
    x_msg_count           OUT  NOCOPY NUMBER,
    x_msg_data            OUT  NOCOPY VARCHAR2
  ) IS
    -- Start OF comments
    -- API name  : Update_Valid_Category
    -- TYPE      : Private and USed by ENI Upgrade program alone
    -- Pre-reqs  : 11.5.10 level
    -- FUNCTION  : Create a category.
    --             This sets the PUB API package level variable
    --             and calls the corresponding PUB API procedure.
    --             This will not do validations for ENABLED_FLAG and DISABLE_DATE
    -- END OF comments
  BEGIN
      INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'Y';

Line: 1034

      INV_ITEM_CATEGORY_PUB.Update_Valid_Category
      (
        p_api_version        => p_api_version  ,
        p_init_msg_list      => p_init_msg_list,
        p_commit             => p_commit       ,
        p_category_set_id    => p_category_set_id ,
        p_category_id        => p_category_id  ,
        p_parent_category_id => p_parent_category_id,
        x_return_status      => x_return_status,
        x_errorcode          => x_errorcode    ,
        x_msg_count          => x_msg_count    ,
        x_msg_data           => x_msg_data
      );

Line: 1052

  END Update_Valid_Category;

Line: 1055

  PROCEDURE Update_Category_Assignment
  (
     p_api_version        IN   NUMBER
  ,  p_init_msg_list      IN   VARCHAR2
  ,  p_commit             IN   VARCHAR2
  ,  p_inventory_item_id  IN   NUMBER
  ,  p_organization_id    IN   NUMBER
  ,  p_category_set_id    IN   NUMBER
  ,  p_category_id        IN   NUMBER
  ,  p_old_category_id    IN   NUMBER
  ,  p_transaction_id   IN   NUMBER
  ,  x_return_status      OUT  NOCOPY VARCHAR2
  ,  x_msg_count          OUT  NOCOPY NUMBER
  ,  x_msg_data           OUT  NOCOPY VARCHAR2
  )
  IS
     l_api_name        CONSTANT  VARCHAR2(30)  := 'Update_Category_Assignment';

Line: 1095

       SELECT structure_id,
             validate_flag,
             mult_item_cat_assign_flag,
             control_level,
             hierarchy_enabled
       FROM   mtl_category_sets_b
       WHERE  category_set_id = p_category_set_id;

Line: 1107

       SELECT 'x'
       FROM  mtl_category_set_valid_cats
       WHERE  category_set_id = p_category_set_id
         AND  category_id = p_category_id;

Line: 1115

       SELECT  structure_id
       FROM  mtl_categories_b
       WHERE  category_id = p_category_id
          AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE; /*Bug no: 5946409 Checking whether the category is disabled */

Line: 1124

      SAVEPOINT Update_Category_Assignment_PVT;

Line: 1167

      SELECT  Count(1)
      INTO    l_reccount
      FROM    mtl_item_categories
      WHERE   inventory_item_id = p_inventory_item_id
      AND     organization_id = p_organization_id
      AND     category_set_id = p_category_set_id
      AND     category_id = p_old_category_id;

Line: 1237

         INV_ITEM_MSG.Debug(Mctx, 'Select Master Org from Mtl_Parameters');

Line: 1240

      SELECT MASTER_ORGANIZATION_ID
      INTO   p_master_org_id
      FROM   mtl_parameters
      WHERE  organization_id = p_organization_id;

Line: 1247

         (  p_Msg_Name        =>  'INV_CAT_CANNOT_CREATE_DELETE'
          ,  p_transaction_id  =>  p_transaction_id
         );

Line: 1258

      SELECT  Count(1)
      INTO    l_reccount
      FROM    mtl_item_categories
      WHERE   inventory_item_id = p_inventory_item_id
      AND     organization_id = p_organization_id
      AND     category_set_id = p_category_set_id
      AND     category_id = p_category_id;

Line: 1298

          UPDATE  /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
		 Mtl_Item_Categories MIC
          SET    Category_Id = p_category_id
                ,last_update_date  = SYSDATE
                ,last_updated_by   = FND_GLOBAL.user_id
                ,last_update_login = FND_GLOBAL.login_id
                ,request_id        = FND_GLOBAL.conc_request_id -- 4105867
          WHERE  category_set_id = p_category_set_id
          AND    category_id = p_old_category_id
          AND    inventory_item_id = p_inventory_item_id
          --Bug 12901485
          --For performance tunning, change it to exists sub-query
          AND exists (SELECT p.organization_id
          --End Bug 12901485
                                FROM   mtl_parameters p
                                WHERE  p.master_organization_id = p_master_org_id
                                AND    p.organization_id = mic.organization_id);

Line: 1316

         FOR c_Get_Revised_Orgs IN ( SELECT organization_id
                                       FROM   mtl_parameters p
                                      WHERE  p.master_organization_id = p_master_org_id )
         LOOP

          INV_ITEM_EVENTS_PVT.Invoke_ICX_wrapper(
                 p_entity_type    => 'ITEM_CATEGORY',
                 p_dml_type       => 'UPDATE',
                 p_inventory_item_id => p_inventory_item_id ,
                 p_organization_id =>  c_Get_Revised_Orgs.organization_id ,
                 p_category_set_id   => p_category_set_id ,
                 p_category_id       => p_category_id );

Line: 1332

	--Category assignment is not getting updated in eni_oltp_item_star table
	--when user update category assignment through
	--INV_ITEM_CATEGORY_PUB.Update_Category_Assignment

          INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
                  p_api_version       => p_api_version
                 ,p_init_msg_list     => p_init_msg_list
                 ,p_inventory_item_id => p_inventory_item_id
                 ,p_organization_id   => p_organization_id
		 ,p_category_set_id   => p_category_set_id
		 ,p_old_category_id   => p_old_category_id
		 ,p_new_category_id   => p_category_id
                 ,x_return_status     => l_return_status
                 ,x_msg_count         => l_msg_count
                 ,x_msg_data          => l_msg_data);

Line: 1354

         UPDATE Mtl_Item_Categories
         SET    Category_Id = p_category_id
                ,last_update_date  = SYSDATE
                ,last_updated_by   = FND_GLOBAL.user_id
                ,last_update_login = FND_GLOBAL.login_id
                ,request_id        = FND_GLOBAL.conc_request_id --4105867
         WHERE  organization_id   = p_organization_id
         AND     inventory_item_id = p_inventory_item_id
         AND     category_set_id = p_category_set_id
         AND     category_id = p_old_category_id;

Line: 1367

                 p_dml_type       => 'UPDATE',
                 p_inventory_item_id => p_inventory_item_id ,
                 p_organization_id => p_organization_id ,
                 p_category_set_id   => p_category_set_id ,
                 p_category_id       => p_category_id );

Line: 1374

	--Category assignment is not getting updated in eni_oltp_item_star table
	--when user update category assignment through
	--INV_ITEM_CATEGORY_PUB.Update_Category_Assignment

          INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
                  p_api_version       => p_api_version
                 ,p_init_msg_list     => p_init_msg_list
                 ,p_inventory_item_id => p_inventory_item_id
                 ,p_organization_id   => p_organization_id
		 ,p_category_set_id   => p_category_set_id
		 ,p_old_category_id   => p_old_category_id
		 ,p_new_category_id   => p_category_id
                 ,x_return_status     => l_return_status
                 ,x_msg_count         => l_msg_count
                 ,x_msg_data          => l_msg_data);

Line: 1398

         INV_ITEM_MSG.Debug(Mctx, 'after update FROM mtl_item_categories');

Line: 1414

            ROLLBACK TO Update_Category_Assignment_PVT;

Line: 1422

            ROLLBACK TO Update_Category_Assignment_PVT;

Line: 1430

	     ROLLBACK TO Update_Category_Assignment_PVT;

Line: 1438

            ROLLBACK TO Update_Category_Assignment_PVT;

Line: 1458

   END Update_Category_Assignment;