eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.JTF_QUALIFIER_PVT SQL Statements

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

Line: 91


    /* If record is being updated, check that primary key is not null */
    IF ( (p_validation_mode = JTF_CTM_UTILITY_PVT.G_UPDATE) AND
         (l_seed_qual_rec.seeded_qual_id IS NULL OR
          l_seed_qual_rec.seeded_qual_id = FND_API.G_MISS_NUM) )THEN


            l_return_status := FND_API.G_RET_STS_ERROR;

Line: 123


/* Insert seeded qualifier record into database */
PROCEDURE Create_Seed_Qual_Record
            ( p_seed_qual_rec       IN  JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
              x_seed_qual_out_rec   OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type )
IS

    CURSOR c_chk_qual_name (l_qual_name VARCHAR2) IS
        SELECT seeded_qual_id
        FROM JTF_SEEDED_QUAL
        WHERE UPPER(name) = UPPER(l_qual_name);

Line: 166


        JTF_SEEDED_QUAL_PKG.INSERT_ROW(
                                     X_Rowid                 => l_rowid,
                                     X_SEEDED_QUAL_ID        => l_seed_qual_rec.seeded_qual_id,
                                     X_LAST_UPDATE_DATE      => l_seed_qual_rec.LAST_UPDATE_DATE,
                                     X_LAST_UPDATED_BY       => l_seed_qual_rec.LAST_UPDATED_BY,
                                     X_CREATION_DATE         => l_seed_qual_rec.CREATION_DATE,
                                     X_CREATED_BY            => l_seed_qual_rec.CREATED_BY,
                                     X_LAST_UPDATE_LOGIN     => l_seed_qual_rec.LAST_UPDATE_LOGIN,
                                     X_NAME                  => l_seed_qual_rec.NAME,
                                     X_DESCRIPTION           => l_seed_qual_rec.DESCRIPTION,
                                     X_ORG_ID                => l_seed_qual_rec.ORG_ID
                                     );

Line: 207


PROCEDURE Update_Seed_Qual_Record
            ( p_seed_qual_rec       IN  JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type,
              x_seed_qual_out_rec   OUT NOCOPY JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type )
IS

    l_rowid                 ROWID;

Line: 224


    JTF_SEEDED_QUAL_PKG.UPDATE_ROW(
                                 X_Rowid                 => l_rowid,
                                 X_SEEDED_QUAL_ID        => l_seed_qual_rec.seeded_qual_id,
                                 X_LAST_UPDATE_DATE      => l_seed_qual_rec.LAST_UPDATE_DATE,
                                 X_LAST_UPDATED_BY       => l_seed_qual_rec.LAST_UPDATED_BY,
                                 X_CREATION_DATE         => l_seed_qual_rec.CREATION_DATE,
                                 X_CREATED_BY            => l_seed_qual_rec.CREATED_BY,
                                 X_LAST_UPDATE_LOGIN     => l_seed_qual_rec.LAST_UPDATE_LOGIN,
                                 X_NAME                  => l_seed_qual_rec.NAME,
                                 X_DESCRIPTION           => l_seed_qual_rec.DESCRIPTION,
                                 X_ORG_ID                => l_seed_qual_rec.ORG_ID
                                 );

Line: 246


END Update_Seed_Qual_Record;

Line: 250


PROCEDURE Delete_Seed_Qual_Record
            ( p_seeded_qual_id  IN  NUMBER,
              x_return_status   OUT NOCOPY VARCHAR2 )
IS

    l_rowid                 ROWID;

Line: 264


    JTF_SEEDED_QUAL_PKG.DELETE_ROW ( X_SEEDED_QUAL_ID => p_seeded_qual_id);

Line: 270


END Delete_Seed_Qual_Record;

Line: 280


    SELECT data_type
    FROM all_tab_columns col, user_synonyms syn
    WHERE col.column_name = UPPER(l_column_name)
     AND syn.synonym_name = UPPER(l_table_name)
     AND col.owner = syn.table_owner
	 AND col.table_name = syn.table_name;

Line: 294


     SELECT oracle_username
     INTO   l_apps_schema_name
     FROM   fnd_oracle_userid
     WHERE  read_only_flag = 'U';

Line: 333


    select 1
    into    l_count
    from    jtf_terr                jta,
            jtf_terr_qual           jtq
    where jta.terr_id = jtq.terr_id
          and jtq.qual_usg_id = l_qual_usg_id
          and rownum < 2;

Line: 456


    SELECT 'X'
    FROM all_tab_columns col, user_synonyms syn
	WHERE syn.synonym_name = UPPER(l_table_name)
	AND col.owner = syn.table_owner
	AND col.table_name = syn.table_name;

Line: 464


    SELECT 'X'
	FROM all_tab_columns col, user_synonyms syn
	WHERE syn.synonym_name = UPPER(l_table_name)
	AND col.owner = syn.table_owner
	AND col.table_name = syn.table_name
	AND col.column_name = UPPER(l_col_name);

Line: 478


     SELECT oracle_username
     INTO   l_apps_schema_name
     FROM   fnd_oracle_userid
     WHERE  read_only_flag = 'U';

Line: 533


** value, so that the record can still be inserted into the database
** Checks items that use lookup values
*/
FUNCTION qual_usgs_info_is_valid
         ( p_qual_usgs_rec IN OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type)
RETURN VARCHAR2
IS

    /* return varaible */
    l_return_variable   VARCHAR2(1) := FND_API.G_TRUE;

Line: 585


    SELECT 'X'
    FROM JTF_QUAL_USGS
    WHERE seeded_qual_id = p_seeded_qual_id
      AND qual_type_usg_id = p_qual_type_usg_id;

Line: 603


    /* If record is being updated, check that primary key is not null */
    IF ( (p_validation_mode = JTF_CTM_UTILITY_PVT.G_UPDATE) AND
         ( l_qual_usgs_rec.qual_usg_id IS NULL OR
           l_qual_usgs_rec.qual_usg_id = FND_API.G_MISS_NUM) ) THEN

        l_return_status := FND_API.G_RET_STS_ERROR;

Line: 658


    /* If record is being updated, check that primary key is not null */
    IF (p_validation_mode = JTF_CTM_UTILITY_PVT.G_CREATE) THEN

        /* check that Unique Key constraint not violated */
        IF ( l_qual_usgs_rec.seeded_qual_id IS NOT NULL AND
             l_qual_usgs_rec.seeded_qual_id <> FND_API.G_MISS_NUM  AND
             l_qual_usgs_rec.qual_type_usg_id IS NOT NULL AND
             l_qual_usgs_rec.qual_type_usg_id <> FND_API.G_MISS_NUM )THEN

            /* check if rec already exists */
            OPEN c_chk_uk_violation ( l_qual_usgs_rec.seeded_qual_id
                                    , l_qual_usgs_rec.qual_type_usg_id);

Line: 794


    JTF_QUAL_USGS_PKG.INSERT_ROW(
        X_Rowid                        => l_rowid,
        X_QUAL_USG_ID                  => l_qual_usgs_rec.QUAL_USG_ID,
        X_LAST_UPDATE_DATE             => l_qual_usgs_rec.LAST_UPDATE_DATE,
        X_LAST_UPDATED_BY              => l_qual_usgs_rec.LAST_UPDATED_BY,
        X_CREATION_DATE                => l_qual_usgs_rec.CREATION_DATE,
        X_CREATED_BY                   => l_qual_usgs_rec.CREATED_BY,
        X_LAST_UPDATE_LOGIN            => l_qual_usgs_rec.LAST_UPDATE_LOGIN,
        X_APPLICATION_SHORT_NAME       => l_qual_usgs_rec.APPLICATION_SHORT_NAME,
        X_SEEDED_QUAL_ID               => p_seed_qual_id,
        X_QUAL_TYPE_USG_ID             => l_qual_usgs_rec.QUAL_TYPE_USG_ID,
        X_ENABLED_FLAG                 => l_qual_usgs_rec.ENABLED_FLAG,
        X_QUAL_COL1                    => l_qual_usgs_rec.QUAL_COL1,
        X_QUAL_COL1_ALIAS              => l_qual_usgs_rec.QUAL_COL1_ALIAS,
        X_QUAL_COL1_DATATYPE           => l_qual_usgs_rec.QUAL_COL1_DATATYPE,
        X_QUAL_COL1_TABLE              => l_qual_usgs_rec.QUAL_COL1_TABLE,
        X_QUAL_COL1_TABLE_ALIAS        => l_qual_usgs_rec.QUAL_COL1_TABLE_ALIAS,
        X_PRIM_INT_CDE_COL             => l_qual_usgs_rec.PRIM_INT_CDE_COL,
        X_PRIM_INT_CDE_COL_DATATYPE    => l_qual_usgs_rec.PRIM_INT_CDE_COL_DATATYPE,
        X_PRIM_INT_CDE_COL_ALIAS       => l_qual_usgs_rec.PRIM_INT_CDE_COL_ALIAS,
        X_SEC_INT_CDE_COL              => l_qual_usgs_rec.SEC_INT_CDE_COL,
        X_SEC_INT_CDE_COL_ALIAS        => l_qual_usgs_rec.SEC_INT_CDE_COL_ALIAS,
        X_SEC_INT_CDE_COL_DATATYPE     => l_qual_usgs_rec.SEC_INT_CDE_COL_DATATYPE,
        X_INT_CDE_COL_TABLE            => l_qual_usgs_rec.INT_CDE_COL_TABLE,
        X_INT_CDE_COL_TABLE_ALIAS      => l_qual_usgs_rec.INT_CDE_COL_TABLE_ALIAS,
        X_SEEDED_FLAG                  => l_qual_usgs_rec.SEEDED_FLAG,
        X_DISPLAY_TYPE                 => l_qual_usgs_rec.DISPLAY_TYPE,
        X_LOV_SQL                      => l_qual_usgs_rec.LOV_SQL,
        x_CONVERT_TO_ID_FLAG           => l_qual_usgs_rec.CONVERT_TO_ID_FLAG,
        x_COLUMN_COUNT                 => l_qual_usgs_rec.COLUMN_COUNT,
        x_FORMATTING_FUNCTION_FLAG     => l_qual_usgs_rec.FORMATTING_FUNCTION_FLAG,
        x_FORMATTING_FUNCTION_NAME     => l_qual_usgs_rec.FORMATTING_FUNCTION_NAME,
        x_SPECIAL_FUNCTION_FLAG        => l_qual_usgs_rec.SPECIAL_FUNCTION_FLAG,
        x_SPECIAL_FUNCTION_NAME        => l_qual_usgs_rec.SPECIAL_FUNCTION_NAME,
        x_ENABLE_LOV_VALIDATION        => l_qual_usgs_rec.ENABLE_LOV_VALIDATION,
        x_DISPLAY_SQL1                 => l_qual_usgs_rec.DISPLAY_SQL1,
        x_LOV_SQL2                     => l_qual_usgs_rec.LOV_SQL2,
        x_DISPLAY_SQL2                 => l_qual_usgs_rec.DISPLAY_SQL2,
        x_LOV_SQL3                     => l_qual_usgs_rec.LOV_SQL3,
        x_DISPLAY_SQL3                 => l_qual_usgs_rec.DISPLAY_SQL3,
        X_ORG_ID                       => l_qual_usgs_rec.ORG_ID,
        X_RULE1                        => l_qual_usgs_rec.RULE1,
        X_RULE2                        => l_qual_usgs_rec.RULE2,
        X_DISPLAY_SEQUENCE             => l_qual_usgs_rec.DISPLAY_SEQUENCE,
        X_DISPLAY_LENGTH               => l_qual_usgs_rec.DISPLAY_LENGTH,
        X_JSP_LOV_SQL                  => l_qual_usgs_rec.JSP_LOV_SQL,
        x_use_in_lookup_flag           => l_qual_usgs_rec.use_in_lookup_flag);

Line: 862


PROCEDURE Update_Qual_Usgs_Record
            ( p_qual_usgs_rec       IN  JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type,
              x_qual_usgs_out_rec   OUT NOCOPY JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type)
IS

    l_rowid                 ROWID;

Line: 886


 JTF_QUAL_USGS_PKG.UPDATE_ROW(
X_Rowid             => l_rowid,
X_QUAL_USG_ID           => l_qual_usgs_rec.QUAL_USG_ID,
X_LAST_UPDATE_DATE      => l_qual_usgs_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY       => l_qual_usgs_rec.LAST_UPDATED_BY,
X_CREATION_DATE         => l_qual_usgs_rec.CREATION_DATE,
X_CREATED_BY            => l_qual_usgs_rec.CREATED_BY,
X_LAST_UPDATE_LOGIN     => l_qual_usgs_rec.LAST_UPDATE_LOGIN,
X_APPLICATION_SHORT_NAME    => l_qual_usgs_rec.APPLICATION_SHORT_NAME,
X_SEEDED_QUAL_ID        => l_qual_usgs_rec.SEEDED_QUAL_ID,
X_QUAL_TYPE_USG_ID      => l_qual_usgs_rec.QUAL_TYPE_USG_ID,
X_ENABLED_FLAG          => l_qual_usgs_rec.ENABLED_FLAG,
X_QUAL_COL1         => l_qual_usgs_rec.QUAL_COL1,
X_QUAL_COL1_ALIAS       => l_qual_usgs_rec.QUAL_COL1_ALIAS,
X_QUAL_COL1_DATATYPE        => l_qual_usgs_rec.QUAL_COL1_DATATYPE,
X_QUAL_COL1_TABLE       => l_qual_usgs_rec.QUAL_COL1_TABLE,
X_QUAL_COL1_TABLE_ALIAS     => l_qual_usgs_rec.QUAL_COL1_TABLE_ALIAS,
X_PRIM_INT_CDE_COL      => l_qual_usgs_rec.PRIM_INT_CDE_COL,
X_PRIM_INT_CDE_COL_DATATYPE => l_qual_usgs_rec.PRIM_INT_CDE_COL_DATATYPE,
X_PRIM_INT_CDE_COL_ALIAS    => l_qual_usgs_rec.PRIM_INT_CDE_COL_ALIAS,
X_SEC_INT_CDE_COL       => l_qual_usgs_rec.SEC_INT_CDE_COL,
X_SEC_INT_CDE_COL_ALIAS     => l_qual_usgs_rec.SEC_INT_CDE_COL_ALIAS,
X_SEC_INT_CDE_COL_DATATYPE  => l_qual_usgs_rec.SEC_INT_CDE_COL_DATATYPE,
X_INT_CDE_COL_TABLE     => l_qual_usgs_rec.INT_CDE_COL_TABLE,
X_INT_CDE_COL_TABLE_ALIAS   => l_qual_usgs_rec.INT_CDE_COL_TABLE_ALIAS,
X_SEEDED_FLAG           => l_qual_usgs_rec.SEEDED_FLAG,
X_DISPLAY_TYPE          => l_qual_usgs_rec.DISPLAY_TYPE,
X_LOV_SQL               => l_qual_usgs_rec.LOV_SQL,
x_CONVERT_TO_ID_FLAG    => l_qual_usgs_rec.CONVERT_TO_ID_FLAG,
x_COLUMN_COUNT          => l_qual_usgs_rec.COLUMN_COUNT,
x_FORMATTING_FUNCTION_FLAG => l_qual_usgs_rec.FORMATTING_FUNCTION_FLAG,
x_FORMATTING_FUNCTION_NAME => l_qual_usgs_rec.FORMATTING_FUNCTION_NAME,
x_SPECIAL_FUNCTION_FLAG  => l_qual_usgs_rec.SPECIAL_FUNCTION_FLAG,
x_SPECIAL_FUNCTION_NAME  => l_qual_usgs_rec.SPECIAL_FUNCTION_NAME,
x_ENABLE_LOV_VALIDATION => l_qual_usgs_rec.ENABLE_LOV_VALIDATION,
x_DISPLAY_SQL1 => l_qual_usgs_rec.DISPLAY_SQL1,
x_LOV_SQL2 => l_qual_usgs_rec.LOV_SQL2,
x_DISPLAY_SQL2  => l_qual_usgs_rec.DISPLAY_SQL2,
x_LOV_SQL3 => l_qual_usgs_rec.LOV_SQL3,
x_DISPLAY_SQL3 => l_qual_usgs_rec.DISPLAY_SQL3,
X_ORG_ID => l_qual_usgs_rec.ORG_ID,
X_RULE1                 => l_qual_usgs_rec.RULE1,
X_RULE2                 => l_qual_usgs_rec.RULE2,
X_DISPLAY_SEQUENCE      => l_qual_usgs_rec.DISPLAY_SEQUENCE,
X_DISPLAY_LENGTH        => l_qual_usgs_rec.DISPLAY_LENGTH,
X_JSP_LOV_SQL           => l_qual_usgs_rec.JSP_LOV_SQL,
X_use_in_lookup_flag           => l_qual_usgs_rec.use_in_lookup_flag
	 );

Line: 943


END Update_Qual_Usgs_Record;

Line: 946


/* Check if records should be deleted
** seeded flag <> Y
** cannot delete seeded_qual if more that one qual_usg exists for that qualifier
** cannot delete qualifier if it is being used in a territory or territory type definition
** check if record should be deleted
*/
PROCEDURE is_qualifier_delete_allowed ( p_seeded_qual_id   IN  NUMBER
                                      , p_qual_usg_id      IN  NUMBER
                                      , x_return_status    OUT NOCOPY VARCHAR2 )
IS

    /* seeded qualifier record does not have more than one child */
    CURSOR c_chk_sq_child ( p_seeded_qual_id NUMBER, p_qual_usg_id NUMBER ) IS
    SELECT qual_usg_id
    FROM JTF_QUAL_USGS
    WHERE seeded_qual_id = p_seeded_qual_id
    AND qual_usg_id <> p_qual_usg_id;

Line: 966


    SELECT qual_usg_id
    FROM JTF_QUAL_USGS
    WHERE seeded_flag = 'Y'
    AND qual_usg_id = p_qual_usg_id;

Line: 973


    SELECT terr_qual_id
    FROM JTF_TERR_QUAL
    WHERE qual_usg_id = p_qual_usg_id;

Line: 979


    SELECT terr_type_qual_id
    FROM JTF_TERR_TYPE_QUAL
    WHERE qual_usg_id = p_qual_usg_id;

Line: 1003


            FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL1');

Line: 1021


            FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL2');

Line: 1038


            FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL3');

Line: 1057


            FND_MESSAGE.Set_Name('JTF', 'DELETE QUAL PVT: CHECK_DEL4');

Line: 1074


PROCEDURE Delete_Qual_Usgs_Record
            ( p_qual_usg_id     IN  NUMBER,
              x_return_status   OUT NOCOPY VARCHAR2 )
IS

    l_rowid                 ROWID;

Line: 1089


    JTF_QUAL_USGS_PKG.DELETE_ROW ( X_QUAL_USG_ID => p_qual_usg_id);

Line: 1094


END Delete_Qual_Usgs_Record;

Line: 1413


PROCEDURE Update_Qualifier
(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_Seed_Qual_Rec       IN    JTF_QUALIFIER_PUB.Seed_Qual_Rec_Type         := JTF_QUALIFIER_PUB.G_MISS_SEED_QUAL_REC,
 p_Qual_Usgs_Rec       IN    JTF_QUALIFIER_PUB.Qual_Usgs_All_Rec_Type     := JTF_QUALIFIER_PUB.G_MISS_QUAL_USGS_ALL_REC,
 x_Seed_Qual_Rec       OUT NOCOPY   JTF_QUALIFIER_PUB.Seed_Qual_Out_Rec_Type,
 x_Qual_Usgs_Rec       OUT NOCOPY   JTF_QUALIFIER_PUB.Qual_Usgs_All_Out_Rec_Type
)
IS
    l_api_name              CONSTANT VARCHAR2(30) := 'Update_Qualifier';

Line: 1442


    SAVEPOINT UPDATE_QUALIFIER_PVT;

Line: 1462


        FND_MESSAGE.Set_Name('JTF', 'PVT UpdateQual API: Start');

Line: 1494


                                 JTF_CTM_UTILITY_PVT.G_UPDATE,
                                 p_validation_level,
                                 l_return_status);

Line: 1508


                                 JTF_CTM_UTILITY_PVT.G_UPDATE,
                                 p_validation_level,
                                 l_return_status);

Line: 1533


    update_seed_qual_record ( l_seed_qual_rec,
                              l_seed_qual_out_rec);

Line: 1548


        FND_MESSAGE.Set_Name('JTF', 'PVTQUAL API:Update QualUsgRec');

Line: 1552


    update_qual_usgs_record ( l_qual_usgs_rec,
                              l_qual_usgs_out_rec);

Line: 1591


        FND_MESSAGE.Set_Name('JTF', 'PVT Update Qual API: End');

Line: 1604


        ROLLBACK TO UPDATE_QUALIFIER_PVT;

Line: 1614


        ROLLBACK TO UPDATE_QUALIFIER_PVT;

Line: 1624


        ROLLBACK TO UPDATE_QUALIFIER_PVT;

Line: 1637


END Update_Qualifier;

Line: 1675


PROCEDURE Delete_Qualifier
(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_Seeded_Qual_Id      IN    NUMBER   := FND_API.G_MISS_NUM,
 p_Qual_Usg_Id         IN    NUMBER   := FND_API.G_MISS_NUM
)
IS
    l_api_name              CONSTANT VARCHAR2(30) := 'Delete_Qualifier';

Line: 1700


    SAVEPOINT DELETE_QUALIFIER_PVT;

Line: 1721


        FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Start');

Line: 1745


    /* check if record should be deleted */
    is_qualifier_delete_allowed ( l_seeded_qual_id, l_qual_usg_id , l_return_status);

Line: 1762


        FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Debug1');

Line: 1769


    delete_seed_qual_record ( l_seeded_qual_id,
                              l_return_status);

Line: 1785


        FND_MESSAGE.Set_Name('JTF', 'Delete Qualifier PVT: Debug2');

Line: 1791


    delete_qual_usgs_record ( l_qual_usg_id,
                              l_return_status);

Line: 1825


        FND_MESSAGE.Set_Name('JTF', 'PVT Delete Qual API: End');

Line: 1838


        ROLLBACK TO DELETE_QUALIFIER_PVT;

Line: 1848


        ROLLBACK TO DELETE_QUALIFIER_PVT;

Line: 1858


        ROLLBACK TO DELETE_QUALIFIER_PVT;