APPS.AMS_CUST_SETUP_PVT SQL Statements

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

Line: 40

    SELECT AMS_CUSTOM_SETUPS_B_S.NEXTVAL
    FROM DUAL;

Line: 44

    SELECT COUNT(*)
    FROM AMS_CUSTOM_SETUPS_VL
    WHERE custom_setup_id = cust_setup_id;

Line: 118

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

Line: 121

  INSERT INTO AMS_CUSTOM_SETUPS_B
  (
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    activity_type_code,
    media_id,
    enabled_flag,
    allow_essential_grouping,
    usage,
    object_type,
    source_code_suffix,
    application_id
  )
  VALUES
  (
    l_cust_setup_rec.custom_setup_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    1,
    FND_GLOBAL.conc_login_id,
    l_cust_setup_rec.activity_type_code,
    l_cust_setup_rec.media_id,
    NVL(l_cust_setup_rec.enabled_flag,'Y'),
    NVL(l_cust_setup_rec.allow_essential_grouping,'N'),
    l_cust_setup_rec.usage,
    l_cust_setup_rec.object_type,
    l_cust_setup_rec.source_code_suffix,
    l_cust_setup_rec.application_id
  );

Line: 158

  INSERT INTO AMS_CUSTOM_SETUPS_TL
  (
    custom_setup_id,
    language,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    source_lang,
    setup_name,
    description
  )
  SELECT
    l_cust_setup_rec.custom_setup_id,
    l.language_code,
    SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    FND_GLOBAL.conc_login_id,
    USERENV('LANG'),
    l_cust_setup_rec.setup_name,
    l_cust_setup_rec.description
  FROM fnd_languages l
  WHERE l.installed_flag in ('I', 'B')
  AND NOT EXISTS
  (
    SELECT NULL
    FROM AMS_CUSTOM_SETUPS_TL t
    WHERE t.custom_setup_id = l_cust_setup_rec.custom_setup_id
    AND t.language = l.language_code
  );

Line: 196

  INSERT INTO AMS_CUSTOM_SETUP_ATTR
  (
    setup_attribute_id,
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    display_sequence_no,
    object_attribute,
    attr_mandatory_flag,
    attr_available_flag,
    function_name,
    parent_function_name,
    parent_setup_attribute,
    parent_display_sequence,
    show_in_report,
    show_in_cue_card,
    copy_allowed_flag,
    related_ak_attribute,
    essential_seq_num
  )
   select ams_custom_setup_attr_s.nextval,
          x_cust_setup_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          1,
          FND_GLOBAL.conc_login_id,
          stp.display_sequence_no,
          stp.setup_attribute,
          stp.mandatory_flag,
          'Y',
          stp.function_name,
          stp.parent_function_name,
          stp.parent_setup_attribute,
          stp.parent_display_sequence,
          nvl(stp.show_in_report,'Y'),
          nvl(stp.show_in_cue_card,'Y'),
          nvl(stp.copy_allowed_flag,'N'),
          stp.related_ak_attribute,
          stp.essential_seq_num

    FROM  ams_setup_types stp
    WHERE stp.object_type = l_cust_setup_rec.object_type
         AND stp.activity_type_code = l_cust_setup_rec.activity_type_code
         AND (stp.usage ='ALL' OR stp.usage is null)
         AND stp.application_id     = l_cust_setup_rec.application_id;

Line: 248

  INSERT INTO AMS_CUSTOM_SETUP_ATTR
  (
    setup_attribute_id,
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    display_sequence_no,
    object_attribute,
    attr_mandatory_flag,
    attr_available_flag,
    function_name,
    parent_function_name,
    parent_setup_attribute,
    parent_display_sequence,
    show_in_report,
    show_in_cue_card,
    copy_allowed_flag,
    related_ak_attribute,
    essential_seq_num
  )
   select ams_custom_setup_attr_s.nextval,
          x_cust_setup_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          1,
          FND_GLOBAL.conc_login_id,
          stp.display_sequence_no,
          stp.setup_attribute,
          stp.mandatory_flag,
          'Y',
          stp.function_name,
          stp.parent_function_name,
          stp.parent_setup_attribute,
          stp.parent_display_sequence,
          nvl(stp.show_in_report,'Y'),
          nvl(stp.show_in_cue_card,'Y'),
          nvl(stp.copy_allowed_flag,'N'),
          stp.related_ak_attribute,
          stp.essential_seq_num
    FROM  ams_setup_types stp
    WHERE stp.object_type = l_cust_setup_rec.object_type
         AND stp.activity_type_code is null
         AND (stp.usage ='ALL' OR stp.usage is null)
         AND stp.application_id     = l_cust_setup_rec.application_id;

Line: 303

  INSERT INTO AMS_CUSTOM_SETUP_ATTR
  (
    setup_attribute_id,
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    display_sequence_no,
    object_attribute,
    attr_mandatory_flag,
    attr_available_flag,
    function_name,
    parent_function_name,
    parent_setup_attribute,
    parent_display_sequence,
    show_in_report,
    show_in_cue_card,
    copy_allowed_flag,
    related_ak_attribute,
    essential_seq_num
  )
   select ams_custom_setup_attr_s.nextval,
          x_cust_setup_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          1,
          FND_GLOBAL.conc_login_id,
          stp.display_sequence_no,
          stp.setup_attribute,
          stp.mandatory_flag,
          --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
          --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
          decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
          stp.function_name,
          stp.parent_function_name,
          stp.parent_setup_attribute,
          stp.parent_display_sequence,
          nvl(stp.show_in_report,'Y'),
          nvl(stp.show_in_cue_card,'Y'),
          nvl(stp.copy_allowed_flag,'N'),
          stp.related_ak_attribute,
          stp.essential_seq_num

    FROM  ams_setup_types stp
    WHERE stp.object_type = l_cust_setup_rec.object_type
         AND stp.activity_type_code = l_cust_setup_rec.activity_type_code
         AND stp.usage in ('LITE','ALL')
         AND stp.application_id     = l_cust_setup_rec.application_id
         AND stp.setup_attribute not in ('COLT');  --ANCHAUDH

Line: 359

  INSERT INTO AMS_CUSTOM_SETUP_ATTR
  (
    setup_attribute_id,
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    display_sequence_no,
    object_attribute,
    attr_mandatory_flag,
    attr_available_flag,
    function_name,
    parent_function_name,
    parent_setup_attribute,
    parent_display_sequence,
    show_in_report,
    show_in_cue_card,
    copy_allowed_flag,
    related_ak_attribute,
    essential_seq_num
  )
   select ams_custom_setup_attr_s.nextval,
          x_cust_setup_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          1,
          FND_GLOBAL.conc_login_id,
          stp.display_sequence_no,
          stp.setup_attribute,
          stp.mandatory_flag,
          --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
          --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
          decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
          stp.function_name,
          stp.parent_function_name,
          stp.parent_setup_attribute,
          stp.parent_display_sequence,
          nvl(stp.show_in_report,'Y'),
          nvl(stp.show_in_cue_card,'Y'),
          nvl(stp.copy_allowed_flag,'N'),
          stp.related_ak_attribute,
          stp.essential_seq_num
    FROM  ams_setup_types stp
    WHERE stp.object_type = l_cust_setup_rec.object_type
         AND stp.activity_type_code is null
         AND stp.usage in ('LITE','ALL')
         AND stp.application_id     = l_cust_setup_rec.application_id
         AND stp.setup_attribute not in ('COLT');  --ANCHAUDH

Line: 416

  INSERT INTO AMS_CUSTOM_SETUP_ATTR
  (
    setup_attribute_id,
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    display_sequence_no,
    object_attribute,
    attr_mandatory_flag,
    attr_available_flag,
    function_name,
    parent_function_name,
    parent_setup_attribute,
    parent_display_sequence,
    show_in_report,
    show_in_cue_card,
    copy_allowed_flag,
    related_ak_attribute,
    essential_seq_num
  )
   select ams_custom_setup_attr_s.nextval,
          x_cust_setup_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          1,
          FND_GLOBAL.conc_login_id,
          stp.display_sequence_no,
          stp.setup_attribute,
          stp.mandatory_flag,
          --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
          --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
          decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
          stp.function_name,
          stp.parent_function_name,
          stp.parent_setup_attribute,
          stp.parent_display_sequence,
          nvl(stp.show_in_report,'Y'),
          nvl(stp.show_in_cue_card,'Y'),
          nvl(stp.copy_allowed_flag,'N'),
          stp.related_ak_attribute,
          stp.essential_seq_num

    FROM  ams_setup_types stp
    WHERE stp.object_type = l_cust_setup_rec.object_type
         AND stp.activity_type_code = l_cust_setup_rec.activity_type_code
         AND stp.usage in ('LITE','ALL')
         AND stp.application_id     = l_cust_setup_rec.application_id;

Line: 471

  INSERT INTO AMS_CUSTOM_SETUP_ATTR
  (
    setup_attribute_id,
    custom_setup_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    display_sequence_no,
    object_attribute,
    attr_mandatory_flag,
    attr_available_flag,
    function_name,
    parent_function_name,
    parent_setup_attribute,
    parent_display_sequence,
    show_in_report,
    show_in_cue_card,
    copy_allowed_flag,
    related_ak_attribute,
    essential_seq_num
  )
   select ams_custom_setup_attr_s.nextval,
          x_cust_setup_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          SYSDATE,
          FND_GLOBAL.user_id,
          1,
          FND_GLOBAL.conc_login_id,
          stp.display_sequence_no,
          stp.setup_attribute,
          stp.mandatory_flag,
          --decode(stp.setup_attribute,'PTNR','N','Y'), --'Y',--anchaudh modified: to bring up partner attribute unchecked while any user activity template creation
          --'Y', --anchaudh modified: to bring up partner attribute CHECKED while any user activity template creation in R12.
          decode(stp.setup_attribute,'FUND','N','BAPL','N','PTNR','N','Y'), -- VMODUR Bug 4884550, 4945973
          stp.function_name,
          stp.parent_function_name,
          stp.parent_setup_attribute,
          stp.parent_display_sequence,
          nvl(stp.show_in_report,'Y'),
          nvl(stp.show_in_cue_card,'Y'),
          nvl(stp.copy_allowed_flag,'N'),
          stp.related_ak_attribute,
          stp.essential_seq_num
    FROM  ams_setup_types stp
    WHERE stp.object_type = l_cust_setup_rec.object_type
	 AND stp.activity_type_code is null
	 AND stp.usage in ('LITE','ALL')
	 AND stp.application_id     = l_cust_setup_rec.application_id;

Line: 595

PROCEDURE update_cust_setup
(
  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_cust_setup_rec      IN      cust_setup_rec_type
)
IS

  l_api_version       CONSTANT NUMBER := 1.0;

Line: 610

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

Line: 618

  SAVEPOINT update_cust_setup;

Line: 674

      p_validation_mode => JTF_PLSQL_API.g_update,
      x_return_status   => l_return_status,
      p_cust_setup_rec  => l_cust_setup_rec
    );

Line: 690

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

Line: 693

  UPDATE AMS_CUSTOM_SETUPS_B SET
    last_update_date = SYSDATE,
    last_updated_by = FND_GLOBAL.user_id,
    object_version_number = l_cust_setup_rec.object_version_number + 1,
    last_update_login = FND_GLOBAL.conc_login_id,
    activity_type_code = l_cust_setup_rec.activity_type_code,
    media_id = l_cust_setup_rec.media_id,
    enabled_flag = l_cust_setup_rec.enabled_flag,
    object_type = l_cust_setup_rec.object_type,
    source_code_suffix = l_cust_setup_rec.source_code_suffix,
    allow_essential_grouping = l_cust_setup_rec.allow_essential_grouping,
    usage = l_cust_setup_rec.usage
  WHERE custom_setup_id = l_cust_setup_rec.custom_setup_id
  AND object_version_number = l_cust_setup_rec.object_version_number;

Line: 716

  UPDATE AMS_CUSTOM_SETUPS_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'),
    setup_name = l_cust_setup_rec.setup_name,
    description = l_cust_setup_rec.description
  WHERE custom_setup_id = l_cust_setup_rec.custom_setup_id
  AND USERENV('LANG') IN (language, source_lang);

Line: 757

      ROLLBACK TO update_cust_setup;

Line: 767

      ROLLBACK TO update_cust_setup;

Line: 777

      ROLLBACK TO update_cust_setup;

Line: 789

END update_cust_setup;

Line: 798

PROCEDURE delete_cust_setup
(
  p_api_version       IN      NUMBER,
  p_init_msg_list     IN      VARCHAR2 := FND_API.g_false,
  p_commit            IN      VARCHAR2 := FND_API.g_false,

  x_return_status     OUT NOCOPY     VARCHAR2,
  x_msg_count         OUT NOCOPY     NUMBER,
  x_msg_data          OUT NOCOPY     VARCHAR2,

  p_cust_setup_id     IN      NUMBER,
  p_object_version    IN      NUMBER
)
IS

  l_api_version    CONSTANT NUMBER := 1.0;

Line: 814

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

Line: 820

    SELECT COUNT(*)
    FROM AMS_OBJECT_ATTRIBUTES
    WHERE custom_setup_id = cust_setup_id;

Line: 825

    SELECT object_version_number
    FROM AMS_CUSTOM_SETUPS_B
    WHERE custom_setup_id = cust_setup_id;

Line: 831

  SAVEPOINT delete_cust_setup;

Line: 870

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

Line: 883

      DELETE FROM AMS_CUSTOM_SETUP_ATTR
      WHERE custom_setup_id = p_cust_setup_id;

Line: 886

      DELETE FROM AMS_CUSTOM_SETUPS_TL
      WHERE custom_setup_id = p_cust_setup_id;

Line: 897

      DELETE FROM AMS_CUSTOM_SETUPS_B
      WHERE custom_setup_id = p_cust_setup_id
      AND object_version_number = p_object_version;

Line: 916

    UPDATE AMS_CUSTOM_SETUPS_B SET			-- IS USED
      object_version_number = l_object_version +1,
      enabled_flag = 'N'
    WHERE custom_setup_id = p_cust_setup_id
      AND object_version_number = p_object_version;

Line: 953

      ROLLBACK TO delete_cust_setup;

Line: 963

      ROLLBACK TO delete_cust_setup;

Line: 973

      ROLLBACK TO delete_cust_setup;

Line: 985

END delete_cust_setup;

Line: 1014

    SELECT custom_setup_id
    FROM AMS_CUSTOM_SETUPS_B
    WHERE custom_setup_id = p_cust_setup_id
    AND object_version_number = p_object_version
    FOR UPDATE OF custom_setup_id NOWAIT;

Line: 1021

    SELECT custom_setup_id
    FROM AMS_CUSTOM_SETUPS_TL
    WHERE custom_setup_id = p_cust_setup_id
    AND USERENV('LANG') IN (language, source_lang)
    FOR UPDATE OF custom_setup_id NOWAIT;

Line: 1382

    AND p_validation_mode = JTF_PLSQL_API.g_update
  THEN
    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
      FND_MESSAGE.set_name('AMS', 'AMS_CUS_SETUP_NO_CUS_SETUP_ID');

Line: 1395

    AND p_validation_mode = JTF_PLSQL_API.g_update
  THEN
    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
      FND_MESSAGE.set_name('AMS', 'AMS_API_NO_OBJ_VER_NUM');

Line: 1481

       SELECT  ''
	    FROM AMS_CUSTOM_SETUPS_TL
	   WHERE UPPER(setup_name) = UPPER(p_setup_name)
	      AND language = USERENV('LANG') ;

Line: 1487

		SELECT  ''
	       FROM AMS_CUSTOM_SETUPS_TL
		 WHERE UPPER(setup_name) = UPPER(p_setup_name)
	        AND custom_setup_id <> p_setup_id
		   AND language = USERENV('LANG');

Line: 1523

  IF p_cust_setup_rec.custom_setup_id IS NOT NULL THEN      -- UPDATE RECORD
    l_uk_flag := AMS_Utility_PVT.check_uniqueness
                 (
                   'AMS_CUSTOM_SETUPS_TL',
                   'custom_setup_id <> ' || p_cust_setup_rec.custom_setup_id
                   || ' AND setup_name =  ''' || p_cust_setup_rec.setup_name
                   || ''' AND language = ''' || USERENV('LANG') ||''''
                 );

Line: 1555

    IF p_cust_setup_rec.custom_setup_id IS NULL THEN    -- UPDATE RECORD

	  OPEN c_name_unique_cr (p_cust_setup_rec.setup_name);

Line: 1687

    SELECT * FROM AMS_CUSTOM_SETUPS_VL
    WHERE custom_setup_id = p_cust_setup_rec.custom_setup_id;

Line: 1768

  x_cust_setup_rec.last_update_date := FND_API.g_miss_date;

Line: 1769

  x_cust_setup_rec.last_updated_by := FND_API.g_miss_num;

Line: 1772

  x_cust_setup_rec.last_update_login := FND_API.g_miss_num;