APPS.AMS_CATEGORY_PVT SQL Statements

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

Line: 91

        SELECT ams_categories_b_s.NEXTVAL
          FROM dual;

Line: 95

        SELECT COUNT(*)
          FROM AMS_CATEGORIES_B
         WHERE category_id = my_category_id;

Line: 100

         SELECT parent_category_id
           FROM AMS_CATEGORIES_B
          WHERE category_id = l_parent_parent_id;

Line: 163

   AMS_Utility_PVT.debug_message(l_full_name ||': insert');

Line: 206

    INSERT INTO AMS_CATEGORIES_B (
    CATEGORY_ID,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    OBJECT_VERSION_NUMBER,
    ARC_CATEGORY_CREATED_FOR,
    ENABLED_FLAG,
    PARENT_CATEGORY_ID
    ,ATTRIBUTE_CATEGORY
         ,ATTRIBUTE1
         ,ATTRIBUTE2
         ,ATTRIBUTE3
         ,ATTRIBUTE4
         ,ATTRIBUTE5
         ,ATTRIBUTE6
         ,ATTRIBUTE7
         ,ATTRIBUTE8
         ,ATTRIBUTE9
         ,ATTRIBUTE10
         ,ATTRIBUTE11
         ,ATTRIBUTE12
         ,ATTRIBUTE13
         ,ATTRIBUTE14
         ,ATTRIBUTE15
     ,ACCRUED_LIABILITY_ACCOUNT
     ,DED_ADJUSTMENT_ACCOUNT
     ,BUDGET_CODE_SUFFIX
     ,LEDGER_ID
        ) VALUES (
    L_CATEGORY_REC.CATEGORY_ID,
        SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    FND_GLOBAL.conc_login_id,
    1,                                  -- object_version_number
    L_CATEGORY_REC.ARC_CATEGORY_CREATED_FOR,
    nvl(L_CATEGORY_REC.ENABLED_FLAG,'Y'),
    L_CATEGORY_REC.PARENT_CATEGORY_ID,
    L_CATEGORY_REC.ATTRIBUTE_CATEGORY,
    L_CATEGORY_REC.ATTRIBUTE1,
    L_CATEGORY_REC.ATTRIBUTE2,
    L_CATEGORY_REC.ATTRIBUTE3,
    L_CATEGORY_REC.ATTRIBUTE4,
    L_CATEGORY_REC.ATTRIBUTE5,
    L_CATEGORY_REC.ATTRIBUTE6,
    L_CATEGORY_REC.ATTRIBUTE7,
    L_CATEGORY_REC.ATTRIBUTE8,
    L_CATEGORY_REC.ATTRIBUTE9,
    L_CATEGORY_REC.ATTRIBUTE10,
    L_CATEGORY_REC.ATTRIBUTE11,
    L_CATEGORY_REC.ATTRIBUTE12,
    L_CATEGORY_REC.ATTRIBUTE13,
    L_CATEGORY_REC.ATTRIBUTE14,
    L_CATEGORY_REC.ATTRIBUTE15,
    L_CATEGORY_REC.ACCRUED_LIABILITY_ACCOUNT,
    L_CATEGORY_REC.DED_ADJUSTMENT_ACCOUNT,
    L_CATEGORY_REC.BUDGET_CODE_SUFFIX,
    L_CATEGORY_REC.LEDGER_ID
);

Line: 270

        INSERT INTO AMS_CATEGORIES_TL (
            CATEGORY_NAME,
            DESCRIPTION,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            CATEGORY_ID,
            LANGUAGE,
            SOURCE_LANG
        ) SELECT
            l_category_rec.CATEGORY_NAME,
            l_category_rec.DESCRIPTION,
                sysdate,
            FND_GLOBAL.User_Id,
            sysdate,
            FND_GLOBAL.User_Id,
            FND_GLOBAL.Conc_Login_Id,
            l_category_rec.category_id,
            L.LANGUAGE_CODE,
            userenv('LANG')
            FROM FND_LANGUAGES L
           WHERE L.INSTALLED_FLAG in ('I', 'B')
             AND NOT EXISTS
                (SELECT NULL
                   FROM AMS_CATEGORIES_TL T
                  WHERE T.CATEGORY_ID = l_category_rec.category_id
                    AND T.LANGUAGE = L.LANGUAGE_CODE);

Line: 382

PROCEDURE Update_Category
( p_api_version         IN     NUMBER,
  p_init_msg_list       IN     VARCHAR2 := FND_API.g_false,
  p_commit              IN     VARCHAR2 := FND_API.g_false,
  p_validation_level    IN     NUMBER   := FND_API.g_valid_level_full,
  x_return_status       OUT NOCOPY    VARCHAR2,
  x_msg_count           OUT NOCOPY    NUMBER,
  x_msg_data            OUT NOCOPY    VARCHAR2,

  p_category_rec        IN     category_rec_type
) IS
        l_api_name      CONSTANT VARCHAR2(30)  := 'Update_Category';

Line: 403

         SELECT parent_category_id
           FROM AMS_CATEGORIES_B
          WHERE category_id = l_parent_parent_id;

Line: 412

       SAVEPOINT Update_Category_PVT;

Line: 491

        UPDATE AMS_CATEGORIES_B
        SET
            LAST_UPDATE_DATE = SYSDATE,
            LAST_UPDATED_BY = FND_GLOBAL.user_id,
            LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
            OBJECT_VERSION_NUMBER = L_CATEGORY_REC.OBJECT_VERSION_NUMBER + 1,
            ENABLED_FLAG = L_CATEGORY_REC.ENABLED_FLAG
        WHERE
            CATEGORY_ID = l_category_rec.category_id;

Line: 509

        UPDATE AMS_CATEGORIES_TL
        SET
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY = FND_GLOBAL.User_Id,
            LAST_UPDATE_LOGIN = FND_GLOBAL.Conc_Login_Id,
            SOURCE_LANG = userenv('LANG')
        WHERE
            CATEGORY_ID = p_category_rec.category_id
          AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 529

        UPDATE AMS_CATEGORIES_B
        SET
            LAST_UPDATE_DATE = SYSDATE,
            LAST_UPDATED_BY = FND_GLOBAL.user_id,
            LAST_UPDATE_LOGIN = FND_GLOBAL.conc_login_id,
            OBJECT_VERSION_NUMBER = L_CATEGORY_REC.OBJECT_VERSION_NUMBER + 1,
            ARC_CATEGORY_CREATED_FOR = L_CATEGORY_REC.ARC_CATEGORY_CREATED_FOR,
            ENABLED_FLAG = L_CATEGORY_REC.ENABLED_FLAG,
            PARENT_CATEGORY_ID = L_CATEGORY_REC.PARENT_CATEGORY_ID,
            ATTRIBUTE_CATEGORY = L_CATEGORY_REC.ATTRIBUTE_CATEGORY,
            ATTRIBUTE1 = L_CATEGORY_REC.ATTRIBUTE1,
            ATTRIBUTE2 = L_CATEGORY_REC.ATTRIBUTE2,
            ATTRIBUTE3 = L_CATEGORY_REC.ATTRIBUTE3,
            ATTRIBUTE4 = L_CATEGORY_REC.ATTRIBUTE4,
            ATTRIBUTE5 = L_CATEGORY_REC.ATTRIBUTE5,
            ATTRIBUTE6 = L_CATEGORY_REC.ATTRIBUTE6,
            ATTRIBUTE7 = L_CATEGORY_REC.ATTRIBUTE7,
            ATTRIBUTE8 = L_CATEGORY_REC.ATTRIBUTE8,
            ATTRIBUTE9 = L_CATEGORY_REC.ATTRIBUTE9,
            ATTRIBUTE10 = L_CATEGORY_REC.ATTRIBUTE10,
            ATTRIBUTE11 = L_CATEGORY_REC.ATTRIBUTE11,
            ATTRIBUTE12 = L_CATEGORY_REC.ATTRIBUTE12,
            ATTRIBUTE13 = L_CATEGORY_REC.ATTRIBUTE13,
            ATTRIBUTE14 = L_CATEGORY_REC.ATTRIBUTE14,
            ATTRIBUTE15 = L_CATEGORY_REC.ATTRIBUTE15,
            ACCRUED_LIABILITY_ACCOUNT = L_CATEGORY_REC.ACCRUED_LIABILITY_ACCOUNT,
            DED_ADJUSTMENT_ACCOUNT = L_CATEGORY_REC.DED_ADJUSTMENT_ACCOUNT,
            BUDGET_CODE_SUFFIX  = L_CATEGORY_REC.BUDGET_CODE_SUFFIX,
            LEDGER_ID = L_CATEGORY_REC.LEDGER_ID
        WHERE
            CATEGORY_ID = l_category_rec.category_id;

Line: 569

        UPDATE AMS_CATEGORIES_TL
        SET
            CATEGORY_NAME = l_category_rec.CATEGORY_NAME,
            DESCRIPTION = l_category_rec.DESCRIPTION,
            LAST_UPDATE_DATE = sysdate,
            LAST_UPDATED_BY = FND_GLOBAL.User_Id,
            LAST_UPDATE_LOGIN = FND_GLOBAL.Conc_Login_Id,
            SOURCE_LANG = userenv('LANG')
        WHERE
            CATEGORY_ID = p_category_rec.category_id
          AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 615

        ROLLBACK TO Update_Category_PVT;

Line: 626

        ROLLBACK TO Update_Category_PVT;

Line: 638

    ROLLBACK TO Update_Category_PVT;

Line: 653

END Update_Category;

Line: 670

PROCEDURE Delete_Category
( p_api_version         IN     NUMBER,
  p_init_msg_list       IN     VARCHAR2    := FND_API.g_false,
  p_commit              IN     VARCHAR2    := FND_API.g_false,
  p_validation_level    IN     NUMBER      := FND_API.g_valid_level_full,
  x_return_status       OUT NOCOPY    VARCHAR2,
  x_msg_count           OUT NOCOPY    NUMBER,
  x_msg_data            OUT NOCOPY    VARCHAR2,

  p_category_id         IN     NUMBER,
  p_object_version      IN  NUMBER
) IS

        l_api_name              CONSTANT VARCHAR2(30)  := 'Delete_Category';

Line: 695

        SAVEPOINT Delete_Category_PVT;

Line: 722

   AMS_Utility_PVT.debug_message(l_full_name ||': delete');

Line: 754

       delete from AMS_CATEGORIES_B
       where  category_id = l_category_id
        and object_version_number = p_object_version;

Line: 768

           delete from AMS_CATEGORIES_TL
           where  category_id = l_category_id
           AND userenv('LANG') in (LANGUAGE, SOURCE_LANG);

Line: 805

                ROLLBACK TO Delete_Category_PVT;

Line: 817

                ROLLBACK TO Delete_Category_PVT;

Line: 828

                ROLLBACK TO Delete_Category_PVT;

Line: 842

END Delete_Category;

Line: 879

        SELECT ARC_CATEGORY_CREATED_FOR,
               PARENT_CATEGORY_ID
          FROM AMS_CATEGORIES_B
         WHERE category_id = p_category_id
                 and object_version_number = p_object_version
           FOR UPDATE of category_id NOWAIT;

Line: 888

        SELECT CATEGORY_NAME,
               DESCRIPTION,
                   decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
          FROM AMS_CATEGORIES_TL
         WHERE CATEGORY_ID = p_CATEGORY_ID
           AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
           FOR UPDATE OF CATEGORY_ID NOWAIT;

Line: 1355

        select  ARC_CATEGORY_CREATED_FOR
         from   AMS_CATEGORIES_B
        where   category_id = l_parent_cat_id;

Line: 1363

	SELECT COUNT(*)
	FROM ams_categories_b
	WHERE parent_category_id = l_parent_cat_id
	AND enabled_flag = 'Y';

Line: 1559

        SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_CATEGORIES_TL t,
                                                                  AMS_CATEGORIES_B b
                           where t.category_name = ctg_name_in
                           and b.arc_category_created_for = ctg_arc_in
                           and language = userenv('LANG')
                           and t.category_id = b.category_id
                           --and t.category_id = ctg_id_in
                           --and b.category_id = ctg_id_in
                           );

Line: 1569

        SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_CATEGORIES_TL t,
                                                                  AMS_CATEGORIES_B b
                           where t.category_name = ctg_name_in
                           and b.arc_category_created_for = ctg_arc_in
                           and language = userenv('LANG')
                           and t.category_id = b.category_id
                           and t.category_id <> ctg_id_in
                           and b.category_id <> ctg_id_in);

Line: 1972

   SELECT *
     FROM ams_categories_vl
    WHERE category_id = p_category_rec.category_id;

Line: 2044

PROCEDURE Unit_Test_Insert
IS

        -- local variables
        l_act_category_rec              category_rec_type;

Line: 2099

END Unit_Test_Insert;

Line: 2103

PROCEDURE Unit_Test_Delete
IS

        -- local variables
        l_category_rec          category_rec_type;

Line: 2127

        AMS_Category_PVT.Delete_Category (
         p_api_version          => 1.0 -- p_api_version
        ,p_init_msg_list        => FND_API.G_FALSE
        ,p_commit               => FND_API.G_FALSE
        ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
        ,p_category_rec         => l_category_rec

        ,x_return_status        => l_return_status
        ,x_msg_count            => l_msg_count
        ,x_msg_data             => l_msg_data
        );

Line: 2150

END Unit_Test_Delete;

Line: 2155

PROCEDURE Unit_Test_Update
IS

        -- local variables
        l_category_rec          category_rec_type;

Line: 2171

        select *
          from AMS_CATEGORIES_VL
         WHERE CATEGORY_ID = my_category_id;

Line: 2184

        l_category_rec.NOTES := 'NOTES UPDATED1';

Line: 2187

        AMS_Category_PVT.Update_Category (
         p_api_version          => 1.0 -- p_api_version
        ,p_init_msg_list        => FND_API.G_FALSE
        ,p_commit               => FND_API.G_FALSE
        ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
        ,p_category_rec         => l_category_rec

        ,x_return_status        => l_return_status
        ,x_msg_count            => l_msg_count
        ,x_msg_data             => l_msg_data
        );

Line: 2211

END Unit_Test_Update;

Line: 2234

         select * from AMS_CATEGORIES_B WHERE CATEGORY_ID = my_category_id;

Line: 2272

PROCEDURE Unit_Test_Act_Insert
is

        -- local variables
        l_act_category_rec              AMS_ACT_CATEGORIES%ROWTYPE;

Line: 2331

END Unit_Test_Act_Insert;

Line: 2334

PROCEDURE Unit_Test_Act_Delete
is

        -- local variables
        l_act_category_rec              AMS_ACT_CATEGORIES%ROWTYPE;

Line: 2357

        AMS_Category_PVT.Delete_Act_Category (
         p_api_version          => 1.0 -- p_api_version
        ,p_init_msg_list        => FND_API.G_FALSE
        ,p_commit               => FND_API.G_FALSE
        ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
        ,p_category_rec         => l_act_category_rec

        ,x_return_status        => l_return_status
        ,x_msg_count            => l_msg_count
        ,x_msg_data             => l_msg_data
        );

Line: 2379

END Unit_Test_Act_Delete;

Line: 2383

PROCEDURE Unit_Test_Act_Update
is

        -- local variables
        l_act_category_rec              AMS_ACT_CATEGORIES%ROWTYPE;

Line: 2399

        SELECT *
          FROM AMS_ACT_CATEGORIES
         WHERE ACTIVITY_CATEGORY_ID = my_act_category_id;

Line: 2415

        l_act_category_rec.ATTRIBUTE1 := 'ATTRIBUTE1 UPDATED1';

Line: 2418

        AMS_Category_PVT.Update_Act_Category (
         p_api_version          => 1.0 -- p_api_version
        ,p_init_msg_list        => FND_API.G_FALSE
        ,p_commit               => FND_API.G_FALSE
        ,p_validation_level     => FND_API.G_VALID_LEVEL_FULL
        ,p_category_rec         => l_act_category_rec

        ,x_return_status        => l_return_status
        ,x_msg_count            => l_msg_count
        ,x_msg_data             => l_msg_data
        );

Line: 2442

END Unit_Test_Act_Update;