APPS.AZ_R12_UPD_DET_LOG SQL Statements

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

Line: 11

  PROCEDURE update_master_status(p_request_id IN NUMBER,   p_source IN VARCHAR2,   p_log_status IN VARCHAR2,   p_status_check_clause IN VARCHAR2,   p_id_list IN typ_nest_tab_number);

Line: 17

   PROCEDURE update_status(p_request_id IN NUMBER,   p_source IN VARCHAR2) IS

  v_current_id NUMBER := 2;

Line: 25

  l_api_name constant VARCHAR2(70) := 'update_status : ';

Line: 33

      SELECT MAX(id)
      INTO v_last_commit_marker_id
      FROM az_diff_results
      WHERE request_id = p_request_id
       AND source = p_source
       AND name = v_commit_marker_name
      ORDER BY id;

Line: 52

    DELETE FROM az_diff_results
    WHERE request_id = p_request_id
     AND source = p_source
     AND name = v_commit_marker_name;

Line: 59

    UPDATE az_diff_results
    SET detail_log_status = 'SP'
    WHERE request_id = p_request_id
     AND source = p_source
     AND id > v_last_commit_marker_id
     AND detail_log_status IN('I',   'U',   'IW',   'UW')
     AND detail_log_status <> 'SP';

Line: 70

    SELECT id bulk collect
    INTO v_commit_marker_id_list
    FROM az_diff_results
    WHERE request_id = p_request_id
     AND source = p_source
     AND detail_log_status = 'SW'
    ORDER BY id;

Line: 79

      update_master_status(p_request_id,   p_source,   'concat(g.detail_log_status,''W'')',   ' not in (''IW'',''UW'',''SW'')',   v_commit_marker_id_list);

Line: 84

    SELECT id bulk collect
    INTO v_commit_marker_id_list
    FROM az_diff_results
    WHERE request_id = p_request_id
     AND source = p_source
     AND detail_log_status = 'E'
    ORDER BY id;

Line: 93

      update_master_status(p_request_id,   p_source,   '''SE''',   ' not in (''E'',''SE'')',   v_commit_marker_id_list);

Line: 105

    raise_error_msg(SQLCODE,   sqlerrm,   'update_status',   'procedure end');

Line: 107

  END update_status;

Line: 109

  PROCEDURE update_master_status(p_request_id IN NUMBER,   p_source IN VARCHAR2,   p_log_status IN VARCHAR2,   p_status_check_clause IN VARCHAR2,   p_id_list IN typ_nest_tab_number) IS
  l_api_name constant VARCHAR2(30) := 'update_master_status : ';

Line: 116

      l_log_query := 'UPDATE az_diff_results g
                          SET g.detail_log_status = ' || p_log_status || '
                          WHERE g.id in
                                    (SELECT k.id
                                    FROM
                                                  (SELECT parent_id, id, detail_log_status
                                                   FROM
                                                            (SELECT d.parent_id, d.id, d.detail_log_status
                                                             FROM az_diff_results d
                                                             WHERE d.request_id = ' || p_request_id || '
                                                             AND d.source = ''' || p_source || '''
                                                             AND d.parent_id >0) f
                                          CONNECT BY PRIOR f.parent_id = f.id START WITH f.id = ' || p_id_list(i) || ') k

                                          WHERE (k.detail_log_status ' || p_status_check_clause || '))
                          AND g.request_id = ' || p_request_id || '
                          AND g.source = ''' || p_source || '''';

Line: 136

        || to_char(systimestamp),   'Executing update master status query: ' || l_log_query);

Line: 141

  END update_master_status;

Line: 169

  PROCEDURE update_det_log_counts(p_request_id IN NUMBER,   p_source IN VARCHAR2,   p_update_xsl OUT nocopy VARCHAR2) IS

  v_diff_type_list typ_nest_tab_varchar;

Line: 175

  v_update_xsl VARCHAR2(32767);

Line: 176

  l_api_name constant VARCHAR2(50) := 'update_det_log_counts : ';

Line: 179

    v_update_xsl := '';

Line: 180

    v_update_xsl := v_update_xsl || ' ';

Line: 181

    v_update_xsl := v_update_xsl || ' ';

Line: 182

    v_update_xsl := v_update_xsl || '  ';

Line: 183

    v_update_xsl := v_update_xsl || '  ';

Line: 184

    v_update_xsl := v_update_xsl || '   ';

Line: 185

    v_update_xsl := v_update_xsl || '  ';

Line: 186

    v_update_xsl := v_update_xsl || '  ';

Line: 187

    v_update_xsl := v_update_xsl || '  ';

Line: 188

    v_update_xsl := v_update_xsl || '  ';

Line: 189

    v_update_xsl := v_update_xsl || '  ';

Line: 190

    v_update_xsl := v_update_xsl || '   ';

Line: 191

    v_update_xsl := v_update_xsl || ' ';

Line: 192

    v_update_xsl := v_update_xsl || '   ';

Line: 193

    v_update_xsl := v_update_xsl || '  ';

Line: 194

    v_update_xsl := v_update_xsl || '   ';

Line: 195

    v_update_xsl := v_update_xsl || '   ';

Line: 198

    SELECT COUNT(1)
    INTO v_rows_count
    FROM az_diff_results d
    WHERE request_id = p_request_id
     AND source = p_source
     AND parent_id = 1
     AND detail_log_status = 'I';

Line: 206

    v_update_xsl := v_update_xsl || '
    v_update_xsl := v_update_xsl || p_source || ''']">';

Line: 208

    v_update_xsl := v_update_xsl || v_rows_count || '';

Line: 211

    SELECT COUNT(1)
    INTO v_rows_count
    FROM az_diff_results d
    WHERE request_id = p_request_id
     AND source = p_source
     AND parent_id = 1
     AND detail_log_status = 'U';

Line: 219

    v_update_xsl := v_update_xsl || '
    v_update_xsl := v_update_xsl || p_source || ''']">';

Line: 221

    v_update_xsl := v_update_xsl || v_rows_count || '';

Line: 223

    SELECT COUNT(1)
    INTO v_rows_count
    FROM az_diff_results d
    WHERE request_id = p_request_id
     AND source = p_source
     AND parent_id = 1
     AND(detail_log_status LIKE 'S%' OR detail_log_status = 'E');

Line: 231

    v_update_xsl := v_update_xsl || '
    v_update_xsl := v_update_xsl || p_source || ''']">';

Line: 233

    v_update_xsl := v_update_xsl || v_rows_count || '';

Line: 236

    SELECT COUNT(1)
    INTO v_rows_count
    FROM az_diff_results d
    WHERE request_id = p_request_id
     AND source = p_source
     AND parent_id = 1
     AND detail_log_status = 'IW';

Line: 244

    v_update_xsl := v_update_xsl || '
    v_update_xsl := v_update_xsl || p_source || ''']">';

Line: 246

    v_update_xsl := v_update_xsl || v_rows_count || '';

Line: 249

    SELECT COUNT(1)
    INTO v_rows_count
    FROM az_diff_results d
    WHERE request_id = p_request_id
     AND source = p_source
     AND parent_id = 1
     AND detail_log_status = 'UW';

Line: 257

    v_update_xsl := v_update_xsl || '
    v_update_xsl := v_update_xsl || p_source || ''']">';

Line: 259

    v_update_xsl := v_update_xsl || v_rows_count || '';

Line: 261

    v_update_xsl := v_update_xsl || '   ';

Line: 262

    v_update_xsl := v_update_xsl || '  ';

Line: 263

    v_update_xsl := v_update_xsl || '  ';

Line: 264

    v_update_xsl := v_update_xsl || '   ';

Line: 265

    v_update_xsl := v_update_xsl || '  ';

Line: 266

    v_update_xsl := v_update_xsl || '  ';

Line: 267

    v_update_xsl := v_update_xsl || '  ';

Line: 268

    v_update_xsl := v_update_xsl || '  ';

Line: 269

    v_update_xsl := v_update_xsl || '  ';

Line: 271

    p_update_xsl := v_update_xsl;

Line: 275

      || to_char(systimestamp) , 'Generated  v_update_xsl: ' || v_update_xsl);

Line: 285

    raise_error_msg(SQLCODE,   sqlerrm,   'update_det_log_counts',   'Error while updating the count based on type of status');

Line: 287

  END update_det_log_counts;