APPS.IBC_UTILITIES_PVT SQL Statements

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

Line: 25

  select value
  from nls_database_parameters
  where parameter = 'NLS_CHARACTERSET';

Line: 63

    SELECT content_item_name
      FROM ibc_citem_versions_tl
     WHERE citem_version_id = (SELECT citem_version_id
                                 FROM ibc_citem_versions_b civb
                                WHERE content_item_id = p_content_item_id
                                  AND version_number = (SELECT MAX(version_number)
                                                          FROM ibc_citem_versions_b civb2
                                                         WHERE civb2.content_item_id = civb.content_item_id)
                               )
      AND language = USERENV('lang');

Line: 96

    SELECT directory_node_code
      FROM ibc_directory_nodes_b
     WHERE directory_node_id = p_directory_node_id;

Line: 131

        SELECT
            group_name resource_name
        FROM
            jtf_rs_groups_vl
        WHERE
            group_id = f_resource_id;

Line: 163

        SELECT directory_node_id
          FROM ibc_directory_nodes_b
         WHERE directory_path = p_directory_path
           AND node_type = p_node_type;

Line: 196

        SELECT
            keyword
        FROM
            ibc_citem_keywords
        WHERE
            content_item_id=pcItemId;

Line: 244

  SELECT description
    FROM fnd_languages_vl
   WHERE language_code = cv_language_code;

Line: 280

     SELECT attribute_bundle_data
      INTO xmlBlob_loc
       FROM IBC_ATTRIBUTE_BUNDLES
     WHERE attribute_bundle_id = p_file_id;

Line: 366

  SELECT file_id, file_name, mime_type, language
  FROM IBC_RENDITIONS
  WHERE CITEM_VERSION_ID = p_citem_version_id
  AND LANGUAGE = NVL(p_lang_code, USERENV('LANG'));

Line: 373

  SELECT NVL(DESCRIPTION, MEANING)
  FROM FND_LOOKUP_VALUES
  WHERE LOOKUP_TYPE = Ibc_Utilities_Pvt.G_REND_LOOKUP_TYPE
  AND LANGUAGE = NVL(p_lang_code, USERENV('LANG'))
  AND LOOKUP_CODE = l_mime_type;

Line: 620

  SELECT r.ATTRIBUTE_TYPE_CODE, r.CONTENT_ITEM_ID, c.ENCRYPT_FLAG
  FROM IBC_COMPOUND_RELATIONS r, IBC_CONTENT_ITEMS c
  WHERE r.CITEM_VERSION_ID = p_citem_version_id
  AND r.CONTENT_ITEM_ID = c.CONTENT_ITEM_ID
  ORDER BY r.SORT_ORDER;

Line: 667

  SELECT r.ATTRIBUTE_TYPE_CODE, r.CONTENT_ITEM_ID, c.ENCRYPT_FLAG
  FROM IBC_COMPOUND_RELATIONS r, IBC_CONTENT_ITEMS c
  WHERE r.CITEM_VERSION_ID = p_citem_version_id
  AND r.CONTENT_ITEM_ID = c.CONTENT_ITEM_ID
  ORDER BY r.SORT_ORDER;

Line: 674

  SELECT CITEM_VERSION_ID
  FROM IBC_CITEM_VERSIONS_B
  WHERE CONTENT_ITEM_ID = l_component_item_id
  AND VERSION_NUMBER = (SELECT MAX(VERSION_NUMBER)
            FROM IBC_CITEM_VERSIONS_B
            WHERE CONTENT_ITEM_ID = l_component_item_id);

Line: 738

        SELECT  application_id
        FROM    fnd_application_vl
        WHERE   application_short_name = x_short_name;

Line: 743

        SELECT  msg.message_number
        FROM    fnd_new_messages msg, fnd_languages_vl lng
        WHERE   msg.message_name = x_msg
          AND   msg.application_id = x_id
          AND   lng.LANGUAGE_CODE = msg.language_code
          AND   lng.language_id = x_lang_id;

Line: 894

/**************************** INSERT ATTACHMENT *******************/
-- This procedure is used to insert new attachments
--
-- This procedure does not commit the action.
--
-- VARIABLES *Required
-- *p_file_id = fnd_lob file_id that will be assigned to this object
-- *p_file_data = the attachment
-- *p_file_name = name of the file being added
-- *p_mime_type = this is equivalent to p_file_content_type of the
-- -- fnd_lobs table, but is not used with that name to avoid confusion.
-- *p_file_format = only two(2) valid formats: 'text','binary'
--  p_program_tag IN VARCHAR2 DEFAULT NULL
/*******************************************************************/
PROCEDURE insert_attachment(
    x_file_id        OUT NOCOPY NUMBER
    ,p_file_data     IN    BLOB
    ,p_file_name     IN    VARCHAR2
    ,p_mime_type     IN    VARCHAR2
    ,p_file_format   IN    VARCHAR2
    ,p_program_tag   IN   VARCHAR2
    ,x_return_status OUT NOCOPY VARCHAR2
)
IS
    l_api_name CONSTANT VARCHAR2(30) := 'insert_attachment';

Line: 939

    SELECT
    fnd_lobs_s.NEXTVAL
  INTO
    x_file_id
  FROM
    dual;

Line: 946

   INSERT INTO fnd_lobs(
     file_id
     ,file_name
     ,file_content_type
     ,file_data
      ,upload_date
      ,expiration_date
      ,program_name
      ,program_tag
     ,file_format
   )VALUES(
     x_file_id
     ,p_file_name
     ,p_mime_type
     ,p_file_data
      ,SYSDATE
      ,NULL
      ,NULL
      ,p_program_tag
     ,p_file_format
  );

Line: 971

      Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');

Line: 997

   Ibc_Audit_Logs_Pkg.insert_row(
      px_audit_log_id            => audit_log_id
      ,p_activity                => p_activity
      ,p_parent_value            => p_parent_value
      ,p_user_id                 => Fnd_Global.user_id
      ,p_time_stamp              => SYSDATE
      ,p_object_type             => p_object_type
      ,p_object_value1           => p_object_value1
      ,p_object_value2           => p_object_value2
      ,p_object_value3           => p_object_value3
      ,p_object_value4           => p_object_value4
      ,p_object_value5           => p_object_value5
      ,p_description             => p_description
      ,p_object_version_number   => 1
      ,x_rowid                   => temp_rowid
   );

Line: 1020

/**************************** INSERT ATTRIBUTE BUNDLE ***************/
-- This procedure is used to create new attribute bundles
--
-- This procedure does not commit the action.
--
-- VARIABLES
-- file_id = file id in fnd_lobs given to the lob created.
/*******************************************************************/
PROCEDURE insert_attribute_bundle(
   x_lob_file_id OUT NOCOPY NUMBER
   ,p_new_bundle IN   CLOB
   ,x_return_status OUT NOCOPY VARCHAR2
)
IS
    l_api_name CONSTANT VARCHAR2(30) := 'insert_attribute_bundle';

Line: 1040

    SELECT  ibc_attribute_bundles_s1.NEXTVAL
      INTO x_lob_file_id
      FROM dual;

Line: 1044

   INSERT INTO IBC_ATTRIBUTE_BUNDLES(
      attribute_bundle_id
     ,attribute_bundle_data
     ,created_by
     ,creation_date
    ,last_updated_by
    ,last_update_date
    ,last_update_login
    ,object_version_number
   )VALUES(
     x_lob_file_id
     ,p_new_bundle
    ,FND_GLOBAL.user_id
    ,SYSDATE
    ,FND_GLOBAL.user_id
    ,SYSDATE
    ,FND_GLOBAL.login_id
    ,1
    );

Line: 1066

      Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');

Line: 1100

    SELECT
    fnd_lobs_s.NEXTVAL
  INTO
    x_lob_file_id
  FROM
    dual;

Line: 1108

    INSERT INTO fnd_lobs(
     file_id,
     file_name,
     file_content_type,
     file_data,
       upload_date,
       expiration_date,
       program_name,
       program_tag,
     file_format)
   VALUES(
     x_lob_file_id,
     'ibc_attributes',
     'text/plain',
     EMPTY_BLOB(),
       SYSDATE,
       p_exp_date,
       'CONTENT_ITEM',
       p_program_tag,
     'text');

Line: 1132

      Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');

Line: 1158

      SELECT 'X'
        FROM jtf_rs_groups_denorm rsgroup,
             jtf_rs_group_members rsmember,
             jtf_rs_resource_extns rsextn
       WHERE parent_group_id = p_group_id
         AND rsgroup.group_id = rsmember.group_id
         AND rsmember.delete_flag = 'N'
         AND rsextn.resource_id = rsmember.resource_id
         AND rsextn.user_id = p_user_id;

Line: 1169

      SELECT 'X'
        FROM jtf_rs_resource_extns
       WHERE resource_id = p_resource_id
         AND user_id = p_user_id;

Line: 1175

      SELECT 'X'
        FROM fnd_user_resp_groups
       WHERE user_id = p_user_id
         AND responsibility_id = p_resp_id;

Line: 1221

/**************************** POST INSERT ***************/
-- This procedure is used to recreate the references of the file_id
-- used in ibc_citem_version_tl table. Called from FNDGFU
-- usage.
--
-- This procedure does not commit the action.
--
-- VARIABLES
-- file_id = file id in returned after FNDGFU inserts the file into
-- FND_LOB
/*******************************************************************/
PROCEDURE post_insert(p_file_id  IN   NUMBER,
              p_file_type IN   VARCHAR2)
IS
CURSOR CUR_FND_LOBS
IS
SELECT
  file_id,
  file_name,
  file_content_type,
  file_data,
  upload_date,
  expiration_date,
  program_name,
  program_tag,
  file_format,
  LANGUAGE
FROM
  FND_LOBS
WHERE
  file_id = p_file_id;

Line: 1255

SELECT   rendition_id, citem.citem_version_id,  citem.LANGUAGE,
  default_rendition_mime_type,
  attachment_file_id
FROM
  IBC_RENDITIONS ren,
  ibc_citem_versions_tl citem
WHERE
  citem.citem_version_id   = ren.citem_version_id(+)  AND
  citem.LANGUAGE         = ren.LANGUAGE(+)  AND
  NVL(default_rendition_mime_type,' ') = NVL(mime_type,' ')
  AND citem.citem_version_id = p_citem_version_id;

Line: 1300

  SELECT DECODE(p_file_type,'ATTRIB',ATTRIBUTE_FILE_ID,'ATTACH',ATTACHMENT_FILE_ID) file_id
  INTO l_old_file_id
  FROM  ibc_citem_versions_tl A
  WHERE a.citem_version_id = l_citem_version_id
  AND A.LANGUAGE = USERENV('LANG');

Line: 1310

UPDATE IBC_CITEM_VERSIONS_TL SET
ATTRIBUTE_FILE_ID   =DECODE(p_file_type,'ATTRIB',p_file_id,ATTRIBUTE_FILE_ID)
,ATTACHMENT_FILE_ID =DECODE(p_file_type,'ATTACH',p_file_id,ATTACHMENT_FILE_ID)
,last_update_date =SYSDATE
WHERE CITEM_VERSION_ID IN   (
SELECT
  b.citem_version_id
FROM
  ibc_citem_versions_tl a,
  ibc_citem_versions_tl b
WHERE
  a.citem_version_id  = l_citem_version_id AND
  NVL(a.attachment_file_id, 0) = NVL(DECODE(p_file_type,'ATTACH',b.attachment_file_id,a.attachment_file_id), 0) AND
  a.attribute_file_id =  DECODE(p_file_type,'ATTRIB',b.attribute_file_id,a.attribute_file_id)    AND
  a.LANGUAGE = b.LANGUAGE    AND
  a.LANGUAGE = l_language)
AND USERENV('LANG') IN (LANGUAGE, source_lang);

Line: 1331

  UPDATE FND_LOBS
  SET file_name = (SELECT attachment_file_name FROM ibc_citem_versions_tl
               WHERE attachment_file_id=p_file_id AND ROWNUM=1)
  WHERE file_id = p_file_id;

Line: 1359

  UPDATE FND_LOBS
  SET file_name = l_file_name
  WHERE file_id = p_file_id;

Line: 1365

DELETE FROM fnd_lobs
WHERE file_id = l_old_file_id
AND NOT EXISTS (SELECT NULL FROM ibc_citem_versions_tl
WHERE DECODE(p_file_type,'ATTRIB',ATTRIBUTE_FILE_ID,'ATTACH',ATTACHMENT_FILE_ID) = l_old_file_id);

Line: 1374

END post_insert;

Line: 1377

PROCEDURE post_insert_attach(p_file_id IN   NUMBER)
IS

BEGIN
post_insert(p_file_id => p_file_id
      ,p_file_type => 'ATTACH');

Line: 1384

END post_insert_attach;

Line: 1387

PROCEDURE post_insert_attrib(p_file_id IN   NUMBER)
IS
BEGIN
post_insert(p_file_id => p_file_id
      ,p_file_type => 'ATTRIB');

Line: 1392

END post_insert_attrib;

Line: 1436

      SELECT hidden_flag
        FROM ibc_directory_nodes_b
       WHERE directory_node_id = p_dir_node_id;

Line: 1441

      SELECT 'CITEM', civb.content_item_id
        FROM ibc_citem_versions_b  civb,
             ibc_citem_versions_tl civtl
       WHERE civb.citem_version_id = civtl.citem_version_id
         AND language = NVL(p_language, USERENV('lang'))
         AND civb.content_item_id <> NVL(p_chk_content_item_id, -1)
         AND EXISTS (SELECT 'X'
                       FROM ibc_content_items
                      WHERE directory_node_id = l_dir_node_id
                        AND content_item_id = civb.content_item_id
                    )
         AND UPPER(civtl.content_item_name) = UPPER(p_name)
      UNION
      SELECT 'DIRNODE', dirnodeb.directory_node_id
        FROM ibc_directory_nodes_b dirnodeb,
             ibc_directory_node_rels dirrel
       WHERE dirnodeb.directory_node_id = dirrel.child_dir_node_id
         AND dirrel.parent_dir_node_id = l_dir_node_id
         AND dirnodeb.directory_node_id <> NVL(p_chk_dir_node_id, -1)
         AND UPPER(dirnodeb.directory_node_code) = UPPER(p_name)
      ;

Line: 1470

        SELECT parent_dir_node_id
          INTO l_dir_node_id
          FROM ibc_directory_node_rels
         WHERE child_dir_node_id = p_chk_dir_node_id;

Line: 1475

        SELECT directory_node_id
          INTO l_dir_node_id
          FROM ibc_content_items
         WHERE content_item_id = p_chk_content_item_id;

Line: 1528

SELECT
  fnd_lobs_s.NEXTVAL   INTO    x_file_id
FROM
  dual;

Line: 1533

   INSERT INTO fnd_lobs(
    file_id
    ,file_name
    ,file_content_type
    ,file_data
    ,upload_date
    ,expiration_date
    ,program_name
    ,program_tag
    ,file_format
   )VALUES(
    x_file_id
    ,p_file_name
    ,p_mime_type
    ,EMPTY_BLOB()
    ,SYSDATE
    ,NULL
    ,NULL
    ,p_program_tag
    ,p_file_format
 );

Line: 1560

     Fnd_Message.Set_Name('IBC', 'LOB_INSERT_ERROR');

Line: 1587

    SELECT description  assoc_name,
           concatenated_segments assoc_code
      FROM mtl_system_items_vl
      WHERE organization_id = p_assoc_object_val1
       AND inventory_item_id = p_assoc_object_val2;

Line: 1603

  l_pcatquery VARCHAR2(1000) := 'SELECT CATEGORY_DESC  assoc_name,CONCAT_CAT_PARENTAGE assoc_code FROM ENI_PROD_DEN_HRCHY_PARENTS_V WHERE CATEGORY_ID = :p_assoc_object_val2 AND CATEGORY_SET_ID = :p_assoc_object_val1';

Line: 1653

    SELECT file_data
      INTO l_xmlblob
      FROM FND_LOBS
     WHERE file_id = p_file_id;