APPS.JTF_CAL_ITEMS_PVT SQL Statements

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

Line: 8

** INTERNAL ONLY: This procedure is used in Update_Row and Delete_Row to make
** sure the record isn't being used by a different user.
*****************************************************************************/
( p_cal_item_id           IN NUMBER
, p_object_version_number IN NUMBER
)AS

  CURSOR c_lock
  /***************************************************************************
  ** Cursor to lock the record
  ***************************************************************************/
  ( b_cal_item_id            IN NUMBER
  )IS SELECT object_version_number
      FROM  jtf_cal_items_b
      WHERE cal_item_id = b_cal_item_id
      FOR UPDATE OF object_version_number NOWAIT;

Line: 51

    fnd_message.set_name ('JTF', 'JTF_CAL_RECORD_DELETED');

Line: 69

PROCEDURE Insert_Row
( p_api_version       IN     NUMBER
, p_init_msg_list     IN     VARCHAR2
, p_commit            IN     VARCHAR2
, p_validation_level  IN     NUMBER
, x_return_status     OUT    NOCOPY	VARCHAR2
, x_msg_count         OUT    NOCOPY	NUMBER
, x_msg_data          OUT    NOCOPY	VARCHAR2
, p_itm_rec           IN     cal_item_rec_type
, x_cal_item_id       OUT    NOCOPY	NUMBER
)
IS
  l_api_name              CONSTANT VARCHAR2(30)    := 'Insert_Row';

Line: 89

  )IS SELECT ROWID
      FROM   JTF_CAL_ITEMS_B
      WHERE  cal_item_id = b_cal_item_id;

Line: 119

  ** Insert into table. Generate the ID from the
  ** sequence and return it
  ***************************************************************************/

  INSERT INTO JTF_CAL_ITEMS_B
  ( CAL_ITEM_ID
  , RESOURCE_ID
  , RESOURCE_TYPE
  , ITEM_TYPE
  , ITEM_TYPE_CODE
  , SOURCE_CODE
  , SOURCE_ID
  , START_DATE
  , END_DATE
  , TIMEZONE_ID
  , URL
  , CREATED_BY
  , CREATION_DATE
  , LAST_UPDATED_BY
  , LAST_UPDATE_DATE
  , LAST_UPDATE_LOGIN
  , OBJECT_VERSION_NUMBER
  , APPLICATION_ID
  ) VALUES
  ( JTF_CAL_ITEMS_S.NEXTVAL -- returning into l_cal_item_id
  , p_itm_rec.RESOURCE_ID
  , p_itm_rec.RESOURCE_TYPE
  , p_itm_rec.ITEM_TYPE
  , p_itm_rec.ITEM_TYPE_CODE
  , p_itm_rec.SOURCE_CODE
  , p_itm_rec.SOURCE_ID
  , p_itm_rec.START_DATE
  , p_itm_rec.END_DATE
  , p_itm_rec.TIMEZONE_ID
  , GetUrl(p_itm_rec.SOURCE_CODE)
  , p_itm_rec.CREATED_BY
  , p_itm_rec.CREATION_DATE
  , p_itm_rec.LAST_UPDATED_BY
  , p_itm_rec.LAST_UPDATE_DATE
  , p_itm_rec.LAST_UPDATE_LOGIN
  , p_itm_rec.OBJECT_VERSION_NUMBER -- always 1 for a new object
  , p_itm_rec.APPLICATION_ID
  )RETURNING CAL_ITEM_ID INTO l_cal_item_id;

Line: 163

  ** Insert into _TL table
  ***************************************************************************/
  INSERT INTO JTF_CAL_ITEMS_TL
  ( CAL_ITEM_ID
  , LANGUAGE
  , SOURCE_LANG
  , ITEM_NAME
  , ITEM_DESCRIPTION
  , CREATED_BY
  , CREATION_DATE
  , LAST_UPDATED_BY
  , LAST_UPDATE_DATE
  , LAST_UPDATE_LOGIN
  , APPLICATION_ID
  ) SELECT  l_cal_item_id
    ,       l.LANGUAGE_CODE
    ,       userenv('LANG')
    ,       NVL(p_itm_rec.ITEM_NAME, ' ')
    ,       p_itm_rec.ITEM_DESCRIPTION
    ,       p_itm_rec.CREATED_BY
    ,       p_itm_rec.CREATION_DATE
    ,       p_itm_rec.LAST_UPDATED_BY
    ,       p_itm_rec.LAST_UPDATE_DATE
    ,       p_itm_rec.LAST_UPDATE_LOGIN
    ,       p_itm_rec.APPLICATION_ID
    FROM  FND_LANGUAGES l
    WHERE l.INSTALLED_FLAG IN ('I','B')
    AND   NOT EXISTS ( SELECT NULL
                       FROM JTF_CAL_ITEMS_TL t
                       WHERE t.CAL_ITEM_ID = l_cal_item_id
                       AND   t.LANGUAGE = l.LANGUAGE_CODE);

Line: 195

  ** Check whether the insert was succesfull
  ***************************************************************************/
  IF (c_record_exists%ISOPEN)THEN
    CLOSE c_record_exists;

Line: 266

END Insert_Row;

Line: 268

PROCEDURE Update_Row
( 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_itm_rec           IN     cal_item_rec_type
, x_object_version_number OUT    NOCOPY	NUMBER
)IS
   CURSOR c_item (l_cal_item_id IN NUMBER)
      IS
         SELECT DECODE (
                   p_itm_rec.resource_id,
                   fnd_api.g_miss_num, resource_id,
                   p_itm_rec.resource_id
                ) resource_id,
                DECODE (
                   p_itm_rec.resource_type,
                   fnd_api.g_miss_char, resource_type,
                   p_itm_rec.resource_type
                ) resource_type,
                DECODE (
                   p_itm_rec.source_code,
                   fnd_api.g_miss_char, source_code,
                   p_itm_rec.source_code
                ) source_code,
                DECODE (
                   p_itm_rec.source_id,
                   fnd_api.g_miss_num, source_id,
                   p_itm_rec.source_id
                ) source_id,
                DECODE (
                   p_itm_rec.start_date,
                   fnd_api.g_miss_date, start_date,
                   p_itm_rec.start_date
                ) start_date,
                DECODE (
                   p_itm_rec.end_date,
                   fnd_api.g_miss_date, end_date,
                   p_itm_rec.end_date
                ) end_date,
                DECODE (
                   p_itm_rec.timezone_id,
                   fnd_api.g_miss_num, timezone_id,
                   p_itm_rec.timezone_id
                ) timezone_id,
                 DECODE (
                   p_itm_rec.url,
                   fnd_api.g_miss_char, url,
                   p_itm_rec.url
                ) url,
                DECODE (
                   p_itm_rec.created_by,
                   fnd_api.g_miss_num, jtf_cal_items_b.created_by,
                   p_itm_rec.created_by
                ) created_by,
                DECODE (
                   p_itm_rec.creation_date,
                   fnd_api.g_miss_date, jtf_cal_items_b.creation_date,
                   p_itm_rec.creation_date
                ) creation_date,
                DECODE (
                   p_itm_rec.last_updated_by,
                   fnd_api.g_miss_num, jtf_cal_items_b.last_updated_by,
                   p_itm_rec.last_updated_by
                ) last_updated_by,
                DECODE (
                   p_itm_rec.last_update_date,
                   fnd_api.g_miss_date, jtf_cal_items_b.last_update_date,
                   p_itm_rec.last_update_date
                ) last_update_date,
                DECODE (
                   p_itm_rec.last_update_login,
                   fnd_api.g_miss_num, jtf_cal_items_b.last_update_login,
                   p_itm_rec.last_update_login
                ) last_update_login,
                 DECODE (
                   p_itm_rec.application_id,
                   fnd_api.g_miss_num, jtf_cal_items_b.application_id,
                   p_itm_rec.application_id
                ) application_id,
                DECODE (
                   p_itm_rec.item_name,
                   fnd_api.g_miss_char, item_name,
                   p_itm_rec.item_name
                ) item_name
         FROM jtf_cal_items_b, jtf_cal_items_tl
          WHERE jtf_cal_items_b.cal_item_id = l_cal_item_id
          AND jtf_cal_items_tl.cal_item_id = jtf_cal_items_b.cal_item_id;

Line: 361

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

Line: 369

 SAVEPOINT update_calitems_pvt;

Line: 430

  ** Update the record
  ***************************************************************************/
  UPDATE JTF_CAL_ITEMS_B
  SET RESOURCE_ID           = l_item_rec.resource_id
  ,   RESOURCE_TYPE         = l_item_rec.resource_type
  ,   SOURCE_CODE           = l_item_rec.source_code
  ,   SOURCE_ID             = l_item_rec.source_id
  ,   START_DATE            = l_item_rec.start_date
  ,   END_DATE              = l_item_rec.end_date
  ,   TIMEZONE_ID           = l_item_rec.timezone_id
  ,   URL                   = l_url
  ,   LAST_UPDATED_BY       = l_item_rec.last_updated_by
  ,   LAST_UPDATE_DATE      = l_item_rec.last_update_date
  ,   LAST_UPDATE_LOGIN     = l_item_rec.last_update_login
  ,   OBJECT_VERSION_NUMBER = jtf_cal_object_version_s.NEXTVAL
  ,   APPLICATION_ID        = l_item_rec.application_id
  WHERE CAL_ITEM_ID = p_itm_rec.cal_item_id
  RETURNING OBJECT_VERSION_NUMBER INTO x_object_version_number; -- return new object version number

Line: 450

  ** Check if the update was succesful
  ***************************************************************************/
  IF (SQL%NOTFOUND)THEN
    RAISE no_data_found;

Line: 456

  UPDATE JTF_CAL_ITEMS_TL
  SET ITEM_NAME         = NVL(l_item_rec.ITEM_NAME, ' ')
  ,   LAST_UPDATE_DATE  = l_item_rec.LAST_UPDATE_DATE
  ,   LAST_UPDATED_BY   = l_item_rec.LAST_UPDATED_BY
  ,   LAST_UPDATE_LOGIN = l_item_rec.LAST_UPDATE_LOGIN
  ,   SOURCE_LANG       = userenv('LANG')
  ,   APPLICATION_ID    = l_item_rec.APPLICATION_ID
  WHERE CAL_ITEM_ID = p_itm_rec.CAL_ITEM_ID
  AND   userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 487

    ROLLBACK TO update_calitems_pvt;

Line: 497

    ROLLBACK TO update_calitems_pvt;

Line: 506

    ROLLBACK TO update_calitems_pvt;

Line: 516

END Update_Row;

Line: 518

PROCEDURE Delete_Row
( p_api_version           IN     NUMBER
, p_init_msg_list         IN     VARCHAR2
, p_commit                IN     VARCHAR2
, p_validation_level      IN     NUMBER
, x_return_status         OUT    NOCOPY	VARCHAR2
, x_msg_count             OUT    NOCOPY	NUMBER
, x_msg_data              OUT    NOCOPY	VARCHAR2
, p_cal_item_id           IN     NUMBER
, p_object_version_number IN     NUMBER
)IS
  l_api_name      CONSTANT VARCHAR2(30) := 'Delete_Row';

Line: 536

  SAVEPOINT delete_calitems_pvt;

Line: 561

    DELETE FROM JTF_CAL_ITEMS_TL
    WHERE CAL_ITEM_ID = p_cal_item_id;

Line: 564

     ** Check whether delete was succesful
     ***************************************************************************/
    IF (SQL%NOTFOUND)
    THEN
       RAISE no_data_found;

Line: 577

    DELETE FROM JTF_CAL_ITEMS_B
    WHERE CAL_ITEM_ID = p_cal_item_id;

Line: 580

     ** Check whether delete was succesful
     ***************************************************************************/
    IF (SQL%NOTFOUND)THEN
       RAISE no_data_found;

Line: 596

      ROLLBACK TO delete_calitems_pvt;

Line: 604

      ROLLBACK TO delete_calitems_pvt;

Line: 610

      ROLLBACK TO delete_calitems_pvt;

Line: 621

END Delete_Row;

Line: 641

  )IS SELECT object_version_number
      FROM   JTF_CAL_ITEMS_B
      WHERE  cal_item_id = b_cal_item_id;

Line: 706

	SELECT fff.WEB_HTML_CALL call
		FROM  JTF_OBJECTS_B job,  FND_FORM_FUNCTIONS fff
		WHERE	job.OBJECT_CODE = p_source_code
		AND	fff.FUNCTION_NAME = job.WEB_FUNCTION_NAME;

Line: 732

	SELECT  job.WEB_FUNCTION_PARAMETERS par
		FROM  JTF_OBJECTS_B job
		WHERE	job.OBJECT_CODE = p_source_code;

Line: 764

		SELECT job.select_id, select_name, from_table, where_clause
		FROM
		 JTF_OBJECTS_B job
		WHERE
			job.OBJECT_CODE = p_source_code;

Line: 791

      l_query := 'select ' || l_name || ' from ' ||
        l_from || ' where ' ||  l_id || ' =: source_object_id';

Line: 795

      l_query := 'select ' || l_name || ' from ' ||
        l_from || ' where ' || l_where ||
        ' and '|| l_id || ' =: source_object_id';

Line: 824

  DELETE FROM JTF_CAL_ITEMS_TL t
  WHERE NOT EXISTS (SELECT NULL
                    FROM JTF_CAL_ITEMS_B b
                    WHERE b.CAL_ITEM_ID = t.CAL_ITEM_ID
                    );

Line: 830

  UPDATE JTF_CAL_ITEMS_TL T
  SET ( ITEM_NAME
      , ITEM_DESCRIPTION
      ) = ( SELECT B.ITEM_NAME
            ,      B.ITEM_DESCRIPTION
            FROM JTF_CAL_ITEMS_TL B
            WHERE B.CAL_ITEM_ID = T.CAL_ITEM_ID
            AND B.LANGUAGE = T.SOURCE_LANG
          )
  WHERE ( T.CAL_ITEM_ID
        , T.LANGUAGE
        ) IN ( SELECT  SUBT.CAL_ITEM_ID
               ,       SUBT.LANGUAGE
               FROM JTF_CAL_ITEMS_TL SUBB
               ,    JTF_CAL_ITEMS_TL SUBT
               WHERE SUBB.CAL_ITEM_ID = SUBT.CAL_ITEM_ID
               AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
               AND (  SUBB.ITEM_NAME <> SUBT.ITEM_NAME
                   OR SUBB.ITEM_DESCRIPTION <> SUBT.ITEM_DESCRIPTION
                   OR (   SUBB.ITEM_DESCRIPTION IS NULL
                      AND SUBT.ITEM_DESCRIPTION IS NOT NULL
                      )
                   OR (   SUBB.ITEM_DESCRIPTION IS NOT NULL
                      AND SUBT.ITEM_DESCRIPTION IS NULL
                      )
                   )
              );

Line: 858

    INSERT INTO JTF_CAL_ITEMS_TL
    ( CAL_ITEM_ID
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_DATE
    , LAST_UPDATE_LOGIN
    , ITEM_NAME
    , ITEM_DESCRIPTION
    , LANGUAGE
    , SOURCE_LANG
    , APPLICATION_ID
  ) SELECT B.CAL_ITEM_ID
    ,      B.CREATED_BY
    ,      B.CREATION_DATE
    ,      B.LAST_UPDATED_BY
    ,      B.LAST_UPDATE_DATE
    ,      B.LAST_UPDATE_LOGIN
    ,      B.ITEM_NAME
    ,      B.ITEM_DESCRIPTION
    ,      L.LANGUAGE_CODE
    ,      B.SOURCE_LANG
    ,      B.APPLICATION_ID
    FROM JTF_CAL_ITEMS_TL B
    ,    FND_LANGUAGES    L
    WHERE L.INSTALLED_FLAG IN ('I', 'B')
    AND   B.LANGUAGE = USERENV('LANG')
    AND   NOT EXISTS (SELECT NULL
                      FROM JTF_CAL_ITEMS_TL T
                      WHERE T.CAL_ITEM_ID = B.CAL_ITEM_ID
                      AND T.LANGUAGE = L.LANGUAGE_CODE
                     );

Line: 916

  UPDATE JTF_CAL_ITEMS_TL
  SET ITEM_NAME         = p_item_name
  ,   ITEM_DESCRIPTION  = p_item_description
  ,   LAST_UPDATE_DATE  = SYSDATE
  ,   LAST_UPDATED_BY   = DECODE(p_owner, 'SEED',1,0)
  ,   LAST_UPDATE_LOGIN = 0
  ,   SOURCE_LANG       = userenv('LANG')
  WHERE userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
  AND   CAL_ITEM_ID = p_cal_item_id;

Line: 965

  UPDATE JTF_CAL_ITEMS_B
  SET ITEM_TYPE             = p_itm_rec.ITEM_TYPE
  ,   ITEM_TYPE_CODE        = p_itm_rec.ITEM_TYPE_CODE
  ,   SOURCE_CODE           = p_itm_rec.SOURCE_CODE
  ,   SOURCE_ID             = p_itm_rec.SOURCE_ID
  ,   START_DATE            = p_itm_rec.START_DATE
  ,   END_DATE              = p_itm_rec.END_DATE
  ,   TIMEZONE_ID           = p_itm_rec.TIMEZONE_ID
  ,   URL                   = p_itm_rec.URL
  ,   LAST_UPDATE_DATE      = SYSDATE
  ,   LAST_UPDATED_BY       = l_user_id
  ,   LAST_UPDATE_LOGIN     = 0
  ,   OBJECT_VERSION_NUMBER = p_itm_rec.OBJECT_VERSION_NUMBER
  ,   APPLICATION_ID        = p_itm_rec.APPLICATION_ID
  WHERE CAL_ITEM_ID = p_cal_item_id;

Line: 986

    INSERT INTO JTF_CAL_ITEMS_B
    ( CAL_ITEM_ID
    , ITEM_TYPE
    , ITEM_TYPE_CODE
    , SOURCE_CODE
    , SOURCE_ID
    , START_DATE
    , END_DATE
    , TIMEZONE_ID
    , URL
    , CREATED_BY
    , CREATION_DATE
    , LAST_UPDATED_BY
    , LAST_UPDATE_DATE
    , LAST_UPDATE_LOGIN
    , OBJECT_VERSION_NUMBER
    , APPLICATION_ID
    ) VALUES
    ( p_cal_item_id
    , p_itm_rec.ITEM_TYPE
    , p_itm_rec.ITEM_TYPE_CODE
    , p_itm_rec.SOURCE_CODE
    , p_itm_rec.SOURCE_ID
    , p_itm_rec.START_DATE
    , p_itm_rec.END_DATE
    , p_itm_rec.TIMEZONE_ID
    , p_itm_rec.URL
    , l_user_id
    , SYSDATE
    , l_user_id
    , SYSDATE
    , 0
    , p_itm_rec.OBJECT_VERSION_NUMBER
    , p_itm_rec.APPLICATION_ID
    );

Line: 1026

  UPDATE JTF_CAL_ITEMS_TL
  SET    ITEM_NAME         = p_itm_rec.item_name
  ,      ITEM_DESCRIPTION  = p_itm_rec.item_description
  ,      LAST_UPDATE_DATE  = SYSDATE
  ,      LAST_UPDATED_BY   = l_user_id
  ,      LAST_UPDATE_LOGIN = 0
  ,      SOURCE_LANG       = userenv('LANG')
  WHERE CAL_ITEM_ID = p_cal_item_id
  AND   userenv('LANG') IN (LANGUAGE, SOURCE_LANG);

Line: 1041

    INSERT INTO JTF_CAL_ITEMS_TL
    (  CAL_ITEM_ID
    ,  CREATED_BY
    ,  CREATION_DATE
    ,  LAST_UPDATED_BY
    ,  LAST_UPDATE_DATE
    ,  LAST_UPDATE_LOGIN
    ,  ITEM_NAME
    ,  ITEM_DESCRIPTION
    ,  LANGUAGE
    ,  SOURCE_LANG
    ,  APPLICATION_ID
    ) SELECT p_cal_item_id
      ,      l_user_id
      ,      SYSDATE
      ,      l_user_id
      ,      SYSDATE
      ,      0
      ,      p_itm_rec.item_name
      ,      p_itm_rec.item_description
      ,      l.LANGUAGE_CODE
      ,      userenv('LANG')
      ,      p_itm_rec.application_id
      FROM FND_LANGUAGES    l
      WHERE l.INSTALLED_FLAG IN ('I', 'B')
      AND NOT EXISTS( SELECT NULL
                      FROM JTF_CAL_ITEMS_TL t
                      WHERE t.CAL_ITEM_ID = p_cal_item_id
                      AND   t.LANGUAGE = l.LANGUAGE_CODE
                    );