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_AE_SQLUTIL_API SQL Statements

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

Line: 15


/* Step 1: First insert row in JTF_PROFILE_MAPPINGS_B table */
select jtf_profile_mappings_b_s1.nextVal
into v_pKey
from dual;

Line: 20


JTF_AE_PROFMAPS.INSERT_ROW(
X_PROFILE_MAPPINGS_ID => v_pKey,
X_SECURITY_GROUP_ID   => 0,
X_OWNERID             => p_AppID,
X_OBJECT_VERSION_NUMBER => 0,
X_BASETABLE => p_BaseTable,
X_PROFILENAME => p_ProfileName,
X_CREATION_DATE => sysdate,
X_CREATED_BY => p_userID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_userID,
X_LAST_UPDATE_LOGIN => 0);

Line: 39


  p_appID IN JTF_ProfileProperties_B.last_updated_by%TYPE,
  p_propertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
  p_attrName IN JTF_ProfileProperties_B.attrName%TYPE,
  p_propDesc IN JTF_ProfileProperties_TL.prop_description%TYPE,
  p_userID IN JTF_ProfileProperties_B.created_by%TYPE ) IS

v_FKey number;

Line: 54


select jtf_profileproperties_b_s1.NextVal
into v_Pkey
from dual;

Line: 58


/* Insert into JTF_PROFILEPRPOPERTIES tables */
JTF_AE_PROFPROPS.INSERT_ROW (
X_PROFILEPROPERTIES_ID => v_PKey,
X_PROFILE_MAPPINGS_ID  => v_FKey,
X_ATTRNAME             => p_attrName,
X_OWNER_APPLICATION_ID => p_appID,
X_ISPRIMARYKEY_FLAG_CODE => 'true',
X_OBJECT_VERSION_NUMBER  => 0,
X_SECURITY_GROUP_ID => 0,
X_PROPERTYNAME      => p_propertyName,
X_PROP_DESCRIPTION  => p_propDesc,
X_CREATION_DATE     => sysdate,
X_CREATED_BY => p_userID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_userID,
X_LAST_UPDATE_LOGIN => 0);

Line: 81


  p_appID IN JTF_ProfileProperties_B.last_updated_by%TYPE,
  p_attrName IN JTF_ProfileProperties_B.attrName%TYPE,
  p_propDesc IN JTF_ProfileProperties_TL.prop_description%TYPE,
  p_userID IN JTF_ProfileProperties_B.created_by%TYPE ) IS

v_FKey number;

Line: 98


select jtf_profileproperties_b_s1.NextVal
into v_Pkey
from dual;

Line: 102


/* Insert into JTF_PROFILEPRPOPERTIES tables */
JTF_AE_PROFPROPS.INSERT_ROW (
X_PROFILEPROPERTIES_ID => v_PKey,
X_PROFILE_MAPPINGS_ID  => v_FKey,
X_ATTRNAME             => p_attrName,
X_OWNER_APPLICATION_ID => p_appID,
X_ISPRIMARYKEY_FLAG_CODE => 'true',
X_OBJECT_VERSION_NUMBER  => 0,
X_SECURITY_GROUP_ID => 0,
X_PROPERTYNAME      => v_propertyName,
X_PROP_DESCRIPTION  => p_propDesc,
X_CREATION_DATE     => sysdate,
X_CREATED_BY => p_userID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_userID,
X_LAST_UPDATE_LOGIN => 0);

Line: 127


  p_appID IN JTF_ProfileProperties_B.last_updated_by%TYPE,
  p_OwnerID  IN JTF_ProfileProperties_B.owner_application_id%TYPE,
  p_propertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
  p_propDesc IN JTF_ProfileProperties_TL.prop_description%TYPE,
  p_isBase IN NUMBER,
  p_userID IN JTF_ProfileProperties_B.created_by%TYPE,
  p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
  p_OID OUT NOCOPY /* file.sql.39 change */ NUMBER,
  p_ATTR OUT NOCOPY /* file.sql.39 change */ JTF_ProfileProperties_B.attrname%TYPE) IS

v_FKey NUMBER;

Line: 147


select jtf_profileproperties_b_s1.NextVal
into p_PK
from dual;

Line: 153


/* Insert into JTF_PROFILEPRPOPERTIES tables */
JTF_AE_PROFPROPS.INSERT_ROW (
X_PROFILEPROPERTIES_ID => p_pK,
X_PROFILE_MAPPINGS_ID  => v_FKey,
X_ATTRNAME             => p_ATTR,
X_OWNER_APPLICATION_ID => p_OwnerID,
X_ISPRIMARYKEY_FLAG_CODE => 'false',
X_OBJECT_VERSION_NUMBER  => p_OID,
X_SECURITY_GROUP_ID => 0,
X_PROPERTYNAME      => p_propertyName,
X_PROP_DESCRIPTION  => p_propDesc,
X_CREATION_DATE     => sysdate,
X_CREATED_BY => p_userID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_userID,
X_LAST_UPDATE_LOGIN => 0);

Line: 176


  p_appID IN JTF_Profile_Metadata_B.last_updated_by%TYPE,
  p_PropertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
  p_pID IN JTF_Profile_Metadata_B.profileproperties_ID%TYPE,
  p_rID IN JTF_Profile_Metadata_B.profile_rules_ID%TYPE,
  p_disabledCode IN JTF_Profile_Metadata_B.disabled_flag_code%TYPE,
  p_mandatoryCode IN JTF_Profile_Metadata_B.mandatory_flag_code%TYPE,
  p_javaTypeCode IN JTF_Profile_Metadata_B.java_datatype_code%TYPE,
  p_default IN JTF_Profile_Metadata_TL.default_value%TYPE,
  p_userID IN JTF_Profile_Metadata_B.created_by%TYPE,
  p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
  p_OID OUT NOCOPY /* file.sql.39 change */ NUMBER,
  p_SPID OUT NOCOPY /* file.sql.39 change */ NUMBER) IS

v_pMapID NUMBER;

Line: 199


 select profileproperties_id
 into v_pPID
 from jtf_profileproperties_tl t
 where t.propertyname = p_PropertyName AND t.language = userenv('LANG');

Line: 208


select jtf_profile_metadata_b_s1.NextVal
into p_PK
from dual;

Line: 214


/* Insert into JTF_PROFILE_METADATA tables */
JTF_AE_METADATA.INSERT_ROW (
X_PROFILE_METADATA_ID => p_PK,
X_SECURITY_GROUP_ID   => 0,
X_SUBSCRIBED_PROFILES_ID => p_SPID,
X_PROFILEPROPERTIES_ID   => v_pPID,
X_PROFILE_RULES_ID       => p_rID,
X_DISABLED_FLAG_CODE     => p_disabledCode,
X_MANDATORY_FLAG_CODE    => p_mandatoryCode,
X_JAVA_DATATYPE_CODE     => p_javaTypeCode,
X_OBJECT_VERSION_NUMBER  => p_OID,
X_DEFAULT_VALUE          => p_default,
X_SQL_VALIDATION         => null,
X_CREATION_DATE     => sysdate,
X_CREATED_BY => p_userID,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => p_userID,
X_LAST_UPDATE_LOGIN => 0);

Line: 240


  p_appID IN JTF_Profile_Metadata_B.last_updated_by%TYPE,
  p_disabledCode IN JTF_Profile_Metadata_B.disabled_flag_code%TYPE,
  p_mandatoryCode IN JTF_Profile_Metadata_B.mandatory_flag_code%TYPE,
  p_javaTypeCode IN JTF_Profile_Metadata_B.java_datatype_code%TYPE,
  p_default IN JTF_Profile_Metadata_TL.default_value%TYPE,
  p_extPropertyName IN JTF_PROFILEPROPERTIES_TL.propertyName%TYPE,
  p_basePropertyName IN JTF_PROFILEPROPERTIES_TL.propertyName%TYPE,
  p_basePropertyVal IN JTF_PROFILE_RULES_TL.base_property_value%TYPE,
  p_rule IN JTF_Profile_Rules_B.rule%TYPE,
  p_userID IN JTF_Profile_Rules_B.created_by%TYPE,
  p_PK OUT NOCOPY /* file.sql.39 change */ NUMBER,
  p_OID OUT NOCOPY /* file.sql.39 change */ NUMBER,
  p_SPID OUT NOCOPY /* file.sql.39 change */ NUMBER) IS

v_pMapID NUMBER;

Line: 281


  p_appID IN JTF_Profile_Metadata_B.last_updated_by%TYPE,
  p_Key IN JTF_Profile_Metadata_B.profile_metadata_id%TYPE,
  p_disabledCode IN JTF_Profile_Metadata_B.disabled_flag_code%TYPE,
  p_mandatoryCode IN JTF_Profile_Metadata_B.mandatory_flag_code%TYPE,
  p_javaTypeCode IN JTF_Profile_Metadata_B.java_datatype_code%TYPE,
  p_default IN JTF_Profile_Metadata_TL.default_value%TYPE,
  p_version IN JTF_Profile_Metadata_B.object_version_number%TYPE,
  p_userID IN JTF_Profile_Metadata_B.created_by%TYPE,
  p_Count OUT NOCOPY /* file.sql.39 change */ NUMBER) IS

BEGIN

/* Modify the _B table first */

UPDATE JTF_PROFILE_METADATA_B
SET disabled_flag_code = p_disabledCode,
    mandatory_flag_code = p_mandatoryCode,
    java_datatype_code = p_javaTypeCode,
    last_update_date = SYSDATE,
    last_updated_by = p_userID,
    object_version_number = p_version + 1
WHERE profile_metadata_id = p_Key AND object_version_number = p_version;

Line: 304


/* Check if update successful? */
p_Count := SQL%ROWCOUNT;

Line: 311


/* Update successful, so modify _TL table */
UPDATE JTF_PROFILE_METADATA_TL
SET default_value = p_default,
    last_update_date = SYSDATE,
    last_updated_by = p_userID
WHERE profile_metadata_id = p_Key;

Line: 325


  p_appID IN JTF_PROFILE_RULES_B.last_updated_by%TYPE,
  p_propertyName IN JTF_ProfileProperties_TL.propertyName%TYPE,
  p_baseValue IN JTF_PROFILE_RULES_TL.base_property_value%TYPE,
  p_rule IN JTF_Profile_Rules_B.rule%TYPE,
  p_userID IN JTF_Profile_Rules_B.created_by%TYPE,
  p_ruleID OUT NOCOPY /* file.sql.39 change */ JTF_Profile_Rules_B.profile_rules_id%TYPE) IS

v_Result NUMBER;

Line: 338


  SELECT b.profile_rules_id
  FROM JTF_Profile_Rules_B b, JTF_Profile_Rules_TL t
  WHERE b.profile_metadata_id = v_pMetaID AND
        b.profile_rules_id = t.profile_rules_id and
        t.base_property_value = p_baseValue and t.language = userenv('LANG');

Line: 350


SELECT a.profile_metadata_id
INTO v_pMetaID
FROM JTF_Profile_Metadata_B a, JTF_ProfileProperties_B b,
     JTF_ProfileProperties_TL t
WHERE a.subscribed_profiles_id = v_pSubProfID
      AND a.profileproperties_id = b.profileproperties_id
      AND b.profile_mappings_id = v_pMapID
      AND b.profileproperties_id = t.profileproperties_id
      AND t.propertyName = p_propertyName AND t.language = userenv('LANG');

Line: 375


select jtf_profile_rules_b_s1.NextVal
into v_Result
from dual;

Line: 379


      JTF_AE_PROFRULES.INSERT_ROW (
        X_PROFILE_RULES_ID       => v_Result,
        X_SECURITY_GROUP_ID      => 0,
        X_PROFILE_METADATA_ID    => v_pMetaID,
        X_RULE                   => p_rule,
        X_OBJECT_VERSION_NUMBER  => 0,
        X_BASE_PROPERTY_VALUE    => p_baseValue,
        X_CREATION_DATE          => sysdate,
        X_CREATED_BY             => p_userID,
        X_LAST_UPDATE_DATE       => sysdate,
        X_LAST_UPDATED_BY        => p_userID,
        X_LAST_UPDATE_LOGIN      => 0
      );

Line: 409


SELECT profile_mappings_id
INTO v_Result
FROM JTF_PROFILE_MAPPINGS_TL t
WHERE t.profileName = p_profileName AND
      t.language = userenv('LANG');

Line: 441


SELECT MAX(TO_NUMBER(LTRIM(ATTRNAME,v_tmp1)))
INTO v_index
FROM JTF_ProfileProperties_B
WHERE profile_mappings_id = p_profile_mappings_id
AND attrname LIKE v_tmp2;

Line: 467


SELECT s.profile_mappings_id,s.subscribed_profiles_id
INTO p_PMAPID, p_SUBID
FROM JTF_SUBSCRIBED_PROFILES s, JTF_PROFILE_MAPPINGS_TL t
WHERE s.application_id = p_appID
AND s.profile_mappings_id = t.profile_mappings_id
AND t.profileName = p_ProfileName AND t.language = userenv('LANG');