APPS.IGS_HE_CREATE_EXTRACT_PKG SQL Statements

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

Line: 32

    SELECT LTRIM(RTRIM(value))
      FROM V$PARAMETER
     WHERE name ='utl_file_dir';

Line: 37

    SELECT DECODE( INSTR(l_dbvalue,',',l_start_str_index),0,LENGTH(l_dbvalue)+1, INSTR(l_dbvalue,',',l_start_str_index) )
      FROM DUAL ;

Line: 128

    SELECT SUBSTR(srt.record_id,4,2) recid,
           dtls.submission_name,
           dtls.user_return_subclass,
           dtls.return_name,
           dtls.file_location,
           dtls.file_name,
           shd.enrolment_start_date,
           shd.enrolment_end_date
      FROM igs_he_ext_run_dtls dtls,
           igs_he_submsn_header shd,
           igs_he_submsn_return srt
     WHERE dtls.extract_run_id = p_extract_run_id
       AND shd.submission_name = dtls.submission_name
       AND srt.submission_name = dtls.submission_name
       AND srt.user_return_subclass = dtls.user_return_subclass
       AND srt.return_name = dtls.return_name;

Line: 147

    SELECT sfld.field_number,
           NVL(sfld.length,0) length,
           sfld.closed_flag
      FROM igs_he_sys_rt_cl_fld sfld,
           igs_he_usr_rt_cl_fld ufld,
           igs_he_usr_rtn_clas  uclas
     WHERE uclas.user_return_subclass = cp_user_return_subclass
       AND sfld.system_return_class_type = uclas.system_return_class_type
       AND ufld.field_number = sfld.field_number
       AND ufld.user_return_subclass = uclas.user_return_subclass;

Line: 160

    SELECT value, override_value, field_number
      FROM igs_he_ex_rn_dat_fd
     WHERE extract_run_id = p_extract_run_id
       AND line_number = cp_line_number
     ORDER BY field_number ASC;

Line: 168

    SELECT group_type
      FROM igs_pe_persid_group_v
    WHERE group_id = p_person_id_grp;

Line: 248

    l_sqlstmt := ' SELECT line_number ' ||
                 '   FROM igs_he_ex_rn_dat_ln ln ' ||
                 '  WHERE ln.extract_run_id = :BIND1' ||
                 '    AND ln.exclude_from_file = ''N'' ';

Line: 270

                              '       (SELECT ''X'' ' ||
                              '          FROM igs_pe_prsid_grp_mem_all a ' ||
                              '         WHERE a.person_id = ln.person_id ' ||
                              '           AND a.group_id = :BIND2 ' ||
                              '           AND (a.start_date IS NULL OR a.start_date <= :BIND3) ' ||
                              '           AND (a.end_date IS NULL OR a.end_date >= :BIND4) ' ||
                              '       )';

Line: 319

                '       (SELECT ''X'' ' ||
                '          FROM igs_ps_grp_mbr b ' ||
                '         WHERE b.course_cd = ln.course_cd ' ||
                '           AND b.version_number = ln.crv_version_number ' ||
                '           AND b.course_group_cd = :BIND5 ' ||
                '       )';

Line: 339

        l_sqlstmt := l_sqlstmt || '    AND ln.last_update_date >= :BIND6 ';

Line: 474

    UPDATE igs_he_ext_run_dtls
    SET file_location = l_filepath
    WHERE extract_run_id = p_extract_run_id;