APPS.JTY_CUST_QUAL_PKG SQL Statements

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

Line: 24

/* this procedure forms the insert and update statement for the columns UPDATE_ATTR_VAL_STMT */
/* and UPDATE_ATTR_VAL_STMT in the table jtf_qual_usgs_all                                   */
PROCEDURE get_attr_val_stmt(
  p_comparison_operator        IN VARCHAR2,
  p_low_value_char             IN VARCHAR2,
  p_high_value_char            IN VARCHAR2,
  p_low_value_char_id          IN VARCHAR2,
  p_low_value_number           IN VARCHAR2,
  p_high_value_number          IN VARCHAR2,
  p_interest_type_id           IN VARCHAR2,
  p_primary_interest_code_id   IN VARCHAR2,
  p_secondary_interest_code_id IN VARCHAR2,
  p_value1_id                  IN VARCHAR2,
  p_value2_id                  IN VARCHAR2,
  p_value3_id                  IN VARCHAR2,
  p_value4_id                  IN VARCHAR2,
  p_first_char                 IN VARCHAR2,
  p_currency_code              IN VARCHAR2,
  p_update_stmt                OUT NOCOPY VARCHAR2,
  p_insert_stmt                OUT NOCOPY VARCHAR2,
  retcode                      OUT NOCOPY VARCHAR2,
  errbuf                       OUT NOCOPY VARCHAR2) IS
BEGIN
  p_insert_stmt :=
    'INSERT into jty_denorm_terr_attr_values_gt( ' || g_new_line ||
    '   terr_id' || g_new_line ||
    '  ,start_date' || g_new_line ||
    '  ,end_date' || g_new_line ||
    '  ,source_id' || g_new_line ||
    '  ,trans_type_id' || g_new_line ||
    '  ,creation_date' || g_new_line ||
    '  ,created_by' || g_new_line ||
    '  ,last_update_date' || g_new_line ||
    '  ,last_updated_by' || g_new_line ||
    '  ,last_update_login' || g_new_line ||
    '  ,absolute_rank' || g_new_line ||
    '  ,top_level_terr_id' || g_new_line ||
    '  ,program_id' || g_new_line ||
    '  ,program_login_id' || g_new_line ||
    '  ,program_application_id' || g_new_line ||
    '  ,request_id' || g_new_line ||
    '  ,program_update_date' || g_new_line;

Line: 67

  p_update_stmt := 'UPDATE jty_denorm_terr_attr_values_gt ' || g_new_line || 'SET ';

Line: 69

  p_insert_stmt := p_insert_stmt || '  ,' || p_comparison_operator || g_new_line;

Line: 71

  p_update_stmt := p_update_stmt || p_comparison_operator || ' = :1,' || g_new_line;

Line: 74

    p_insert_stmt := p_insert_stmt || '  ,' || p_low_value_char_id || g_new_line;

Line: 75

	p_update_stmt := p_update_stmt || p_low_value_char_id || ' = :2,' || g_new_line;

Line: 77

    p_insert_stmt := p_insert_stmt || '  ,dummy1' || g_new_line;

Line: 78

	p_update_stmt := p_update_stmt || 'dummy1 = :2, ' || g_new_line;

Line: 82

    p_insert_stmt := p_insert_stmt || '  ,' || p_low_value_char || g_new_line;

Line: 83

	p_update_stmt := p_update_stmt || p_low_value_char || ' = :3,' || g_new_line;

Line: 85

    p_insert_stmt := p_insert_stmt || '  ,dummy2' || g_new_line;

Line: 86

	p_update_stmt := p_update_stmt || 'dummy2 = :3, ' || g_new_line;

Line: 90

    p_insert_stmt := p_insert_stmt || '  ,' || p_high_value_char || g_new_line;

Line: 91

	p_update_stmt := p_update_stmt || p_high_value_char || ' = :4,' || g_new_line;

Line: 93

    p_insert_stmt := p_insert_stmt || '  ,dummy3' || g_new_line;

Line: 94

	p_update_stmt := p_update_stmt || 'dummy3 = :4, ' || g_new_line;

Line: 98

    p_insert_stmt := p_insert_stmt || '  ,' || p_low_value_number || g_new_line;

Line: 99

	p_update_stmt := p_update_stmt || p_low_value_number || ' = :5,' || g_new_line;

Line: 101

    p_insert_stmt := p_insert_stmt || '  ,dummy4' || g_new_line;

Line: 102

	p_update_stmt := p_update_stmt || 'dummy4 = :5, ' || g_new_line;

Line: 106

    p_insert_stmt := p_insert_stmt || '  ,' || p_high_value_number || g_new_line;

Line: 107

	p_update_stmt := p_update_stmt || p_high_value_number || ' = :6,' || g_new_line;

Line: 109

    p_insert_stmt := p_insert_stmt || '  ,dummy5' || g_new_line;

Line: 110

	p_update_stmt := p_update_stmt || 'dummy5 = :6, ' || g_new_line;

Line: 114

    p_insert_stmt := p_insert_stmt || '  ,' || p_interest_type_id || g_new_line;

Line: 115

	p_update_stmt := p_update_stmt || p_interest_type_id || ' = :7,' || g_new_line;

Line: 117

    p_insert_stmt := p_insert_stmt || '  ,dummy6' || g_new_line;

Line: 118

	p_update_stmt := p_update_stmt || 'dummy6 = :7, ' || g_new_line;

Line: 122

    p_insert_stmt := p_insert_stmt || '  ,' || p_primary_interest_code_id || g_new_line;

Line: 123

	p_update_stmt := p_update_stmt || p_primary_interest_code_id || ' = :8,' || g_new_line;

Line: 125

    p_insert_stmt := p_insert_stmt || '  ,dummy7' || g_new_line;

Line: 126

	p_update_stmt := p_update_stmt || 'dummy7 = :8, ' || g_new_line;

Line: 130

    p_insert_stmt := p_insert_stmt || '  ,' || p_secondary_interest_code_id || g_new_line;

Line: 131

	p_update_stmt := p_update_stmt || p_secondary_interest_code_id || ' = :9,' || g_new_line;

Line: 133

    p_insert_stmt := p_insert_stmt || '  ,dummy8' || g_new_line;

Line: 134

	p_update_stmt := p_update_stmt || 'dummy8 = :9, ' || g_new_line;

Line: 138

    p_insert_stmt := p_insert_stmt || '  ,' || p_currency_code || g_new_line;

Line: 139

	p_update_stmt := p_update_stmt || p_currency_code || ' = :10,' || g_new_line;

Line: 141

    p_insert_stmt := p_insert_stmt || '  ,dummy9' || g_new_line;

Line: 142

	p_update_stmt := p_update_stmt || 'dummy9 = :10, ' || g_new_line;

Line: 146

    p_insert_stmt := p_insert_stmt || '  ,' || p_value1_id || g_new_line;

Line: 147

	p_update_stmt := p_update_stmt || p_value1_id || ' = :11,' || g_new_line;

Line: 149

    p_insert_stmt := p_insert_stmt || '  ,dummy10' || g_new_line;

Line: 150

	p_update_stmt := p_update_stmt || 'dummy10 = :11, ' || g_new_line;

Line: 154

    p_insert_stmt := p_insert_stmt || '  ,' || p_value2_id || g_new_line;

Line: 155

	p_update_stmt := p_update_stmt || p_value2_id || ' = :12,' || g_new_line;

Line: 157

    p_insert_stmt := p_insert_stmt || '  ,dummy11' || g_new_line;

Line: 158

	p_update_stmt := p_update_stmt || 'dummy11 = :12, ' || g_new_line;

Line: 162

    p_insert_stmt := p_insert_stmt || '  ,' || p_value3_id || g_new_line;

Line: 163

	p_update_stmt := p_update_stmt || p_value3_id || ' = :13,' || g_new_line;

Line: 165

    p_insert_stmt := p_insert_stmt || '  ,dummy12' || g_new_line;

Line: 166

	p_update_stmt := p_update_stmt || 'dummy12 = :13, ' || g_new_line;

Line: 170

    p_insert_stmt := p_insert_stmt || '  ,' || p_value4_id || g_new_line;

Line: 171

	p_update_stmt := p_update_stmt || p_value4_id || ' = :14,' || g_new_line;

Line: 173

    p_insert_stmt := p_insert_stmt || '  ,dummy13' || g_new_line;

Line: 174

	p_update_stmt := p_update_stmt || 'dummy13 = :14, ' || g_new_line;

Line: 178

    p_insert_stmt := p_insert_stmt || '  ,' || p_first_char || g_new_line;

Line: 179

	p_update_stmt := p_update_stmt || p_first_char || ' = :15' || g_new_line;

Line: 181

    p_insert_stmt := p_insert_stmt || '  ,dummy14' || g_new_line;

Line: 182

	p_update_stmt := p_update_stmt || 'dummy14 = :15 ' || g_new_line;

Line: 185

  p_insert_stmt := p_insert_stmt || ')' || g_new_line;

Line: 186

  p_insert_stmt := p_insert_stmt || 'VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, ' ||
    ':16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32)';

Line: 189

  p_update_stmt := p_update_stmt || 'where terr_id = :16' || g_new_line ||
                                    'and source_id = :17' || g_new_line ||
                                    'and trans_type_id = :18';

Line: 198

    errbuf  := 'Error in generating insert and update attribute values statement';

Line: 233

  p_real_time_select           IN VARCHAR2,
  p_real_time_where            IN VARCHAR2,
  p_real_time_from             IN VARCHAR2,
  p_html_lov_sql1              IN VARCHAR2,
  p_html_lov_sql2              IN VARCHAR2,
  p_html_lov_sql3              IN VARCHAR2,
  p_display_sql1               IN VARCHAR2,
  p_display_sql2               IN VARCHAR2,
  p_display_sql3               IN VARCHAR2,
  p_hierarchy_type             IN VARCHAR2,
  p_equal_flag                 IN VARCHAR2,
  p_like_flag                  IN VARCHAR2,
  p_between_flag               IN VARCHAR2,
  retcode                      OUT NOCOPY VARCHAR2,
  errbuf                       OUT NOCOPY VARCHAR2) IS

  l_count            NUMBER;

Line: 250

  l_insert_stmt      VARCHAR2(2000);

Line: 251

  l_update_stmt      VARCHAR2(2000);

Line: 266

  SELECT count(*)
  INTO   l_count
  FROM   jtf_seeded_qual_all_b
  WHERE  seeded_qual_id = p_seeded_qual_id;

Line: 278

  SELECT count(*)
  INTO   l_count
  FROM   jtf_qual_usgs_all
  WHERE  qual_usg_id = p_seeded_qual_id;

Line: 291

    SELECT qual_type_usg_id
    INTO   l_qual_type_usg_id
    FROM   jtf_qual_type_usgs_all
    WHERE  source_id = p_source_id
    AND    qual_type_id = p_trans_type_id;

Line: 303

  /* get the attribute value insert and update statement */
  get_attr_val_stmt(
    p_comparison_operator        => p_comparison_operator,
    p_low_value_char             => p_low_value_char,
    p_high_value_char            => p_high_value_char,
    p_low_value_char_id          => p_low_value_char_id,
    p_low_value_number           => p_low_value_number,
    p_high_value_number          => p_high_value_number,
    p_interest_type_id           => p_interest_type_id,
    p_primary_interest_code_id   => p_primary_interest_code_id,
    p_secondary_interest_code_id => p_sec_interest_code_id,
    p_value1_id                  => p_value1_id,
    p_value2_id                  => p_value2_id,
    p_value3_id                  => p_value3_id,
    p_value4_id                  => p_value4_id,
    p_first_char                 => p_first_char,
    p_currency_code              => p_currency_code,
    p_update_stmt                => l_update_stmt,
    p_insert_stmt                => l_insert_stmt,
    retcode                      => retcode,
    errbuf                       => errbuf);

Line: 330

  SELECT count(*)
  INTO   l_count
  FROM   jtf_seeded_qual_all_b
  WHERE  upper(name) = upper(p_name);

Line: 341

  INSERT INTO JTF_SEEDED_QUAL_ALL_B (
    SEEDED_QUAL_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    NAME,
    DESCRIPTION,
    ORG_ID,
    SECURITY_GROUP_ID )
  VALUES (
    p_seeded_qual_id,
    l_sysdate,
    l_user_id,
    l_sysdate,
    l_user_id,
    l_login_id,
    p_name,
    p_description,
    null,
    null);

Line: 364

  INSERT INTO JTF_SEEDED_QUAL_ALL_TL (
    SEEDED_QUAL_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    LANGUAGE,
    SOURCE_LANG,
    NAME,
    DESCRIPTION,
    ORG_ID,
    SECURITY_GROUP_ID )
  VALUES (
    p_seeded_qual_id,
    l_sysdate,
    l_user_id,
    l_sysdate,
    l_user_id,
    l_login_id,
    p_language,
    p_language,
    p_name,
    p_description,
    null,
    null);

Line: 391

  INSERT INTO JTF_QUAL_USGS_ALL (
    QUAL_USG_ID,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_LOGIN,
    APPLICATION_SHORT_NAME,
    SEEDED_QUAL_ID,
    QUAL_TYPE_USG_ID,
    ENABLED_FLAG,
    QUAL_COL1,
    QUAL_COL1_ALIAS,
    SEEDED_FLAG,
    CONVERT_TO_ID_FLAG,
    DISPLAY_TYPE,
    ORG_ID,
    ALIAS_RULE1,
    OP_EQL,
    OP_LIKE,
    OP_BETWEEN,
    OP_COMMON_WHERE,
    QUAL_RELATION_FACTOR,
    OBJECT_VERSION_NUMBER,
    COMPARISON_OPERATOR,
    LOW_VALUE_CHAR,
    HIGH_VALUE_CHAR,
    LOW_VALUE_CHAR_ID,
    LOW_VALUE_NUMBER,
    HIGH_VALUE_NUMBER,
    INTEREST_TYPE_ID,
    PRIMARY_INTEREST_CODE_ID,
    SECONDARY_INTEREST_CODE_ID,
    VALUE1_ID,
    VALUE2_ID,
    VALUE3_ID,
    VALUE4_ID,
    FIRST_CHAR,
    CURRENCY_CODE,
    REAL_TIME_SELECT,
    REAL_TIME_WHERE,
    REAL_TIME_FROM,
    UPDATE_ATTR_VAL_STMT,
    INSERT_ATTR_VAL_STMT,
    HTML_LOV_SQL1,
    HTML_LOV_SQL2,
    HTML_LOV_SQL3,
    DISPLAY_SQL1,
    DISPLAY_SQL2,
    DISPLAY_SQL3,
    HIERARCHY_TYPE,
    EQUAL_FLAG,
    LIKE_FLAG,
    BETWEEN_FLAG )
  VALUES (
    p_seeded_qual_id,
    l_sysdate,
    l_user_id,
    l_sysdate,
    l_user_id,
    l_login_id,
    'JTF',
    p_seeded_qual_id,
    l_qual_type_usg_id,
    p_enabled_flag,
    p_qual_col1,
    p_qual_col1,
    'N',
    p_convert_to_id_flag,
    p_display_type,
    -3113,
    p_alias_rule1,
    p_op_eql,
    p_op_like,
    p_op_between,
    p_op_common_where,
    p_qual_relation_factor,
    null,
    p_comparison_operator,
    p_low_value_char,
    p_high_value_char,
    p_low_value_char_id,
    p_low_value_number,
    p_high_value_number,
    p_interest_type_id,
    p_primary_interest_code_id,
    p_sec_interest_code_id,
    p_value1_id,
    p_value2_id,
    p_value3_id,
    p_value4_id,
    p_first_char,
    p_currency_code,
    p_real_time_select,
    p_real_time_where,
    p_real_time_from,
    l_update_stmt,
    l_insert_stmt,
    p_html_lov_sql1,
    p_html_lov_sql2,
    p_html_lov_sql3,
    p_display_sql1,
    p_display_sql2,
    p_display_sql3,
    p_hierarchy_type,
    p_equal_flag,
    p_like_flag,
    p_between_flag);