APPS.IGR_IMP_002 SQL Statements

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

Line: 24

  PROCEDURE update_parent_record_status ( p_interface_run_id  IN NUMBER ) AS
  /*************************************************************
   Created By : rbezawad
   Date Created By :  27-Feb-05
   Purpose : Procedure to set the IGR_I_APPL_INT.STATUS value to Warning (status='4') when IGR_I_APPL_INT record
             is processed successfully (status='1') but processing of any of the child interface records is not
             successful (status<>'1').  Also set the IGS_AD_INTERFACE.STATUS to Error (Status='3') when processing any
	     of the child interface records (IGR_I_APPL_INT) is not successful (status<>'1').
   Know limitations, enhancements or remarks
   Change History
   Who             When            What
   (reverse chronological order - newest change first)
  ***************************************************************/
  BEGIN

    -- Based upon inquiry child
    UPDATE igr_i_appl_int iappl
    SET    status = '4',
           error_code = 'E347',
           error_text =  igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E347', 8405)
    WHERE  status = '1'
    AND    interface_run_id = p_interface_run_id
    AND    (
                EXISTS (SELECT 1 FROM igr_i_lines_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
            OR  EXISTS (SELECT 1 FROM igr_i_pkg_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
            OR  EXISTS (SELECT 1 FROM igr_i_info_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
            OR  EXISTS (SELECT 1 FROM igr_i_char_int WHERE status <> '1' AND interface_inq_appl_id = iappl.interface_inq_appl_id)
           );

Line: 55

    UPDATE igs_ad_interface ad
    SET    record_status = '3',
           status = '4',
           error_code = 'E347'
    WHERE  status = '1'
    AND    interface_run_id = p_interface_run_id
    AND    (EXISTS (SELECT 1 FROM igr_i_appl_int WHERE status <> '1' AND interface_id = ad.interface_id));

Line: 64

  END update_parent_record_status;

Line: 130

      UPDATE igr_i_appl_int a
      SET    interface_run_id = p_interface_run_id,
             person_id = (SELECT person_id
                          FROM   igs_ad_interface
                          WHERE  interface_id = a.interface_id)
      WHERE  interface_id IN (SELECT interface_id
                              FROM   igs_ad_interface
                              WHERE  interface_run_id = p_interface_run_id
                              AND    status IN ('1','4'));

Line: 142

      UPDATE igr_i_appl_int
      SET    error_code = NULL,
             error_text = NULL,
             status = '1'
      WHERE  interface_run_id = p_interface_run_id
      AND    error_code = 'E347'
      AND    status = '4';

Line: 159

                                          p_rule             => 'N'); -- Update not yet supported

Line: 173

      UPDATE igr_i_lines_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,enquiry_appl_number,inquiry_date,inquiry_source_type,sales_lead_id)
             = (SELECT person_id,enquiry_appl_number,inquiry_dt,inquiry_source_type,sales_lead_id
                FROM   igr_i_appl_int
                WHERE  interface_inq_appl_id = a.interface_inq_appl_id)
      WHERE  interface_inq_appl_id IN (SELECT interface_inq_appl_id
                                       FROM   igr_i_appl_int
                                       WHERE  interface_run_id = p_interface_run_id
                                       AND    status IN ('1','4'));

Line: 192

                                         p_rule             => 'N'); -- Update not yet supported

Line: 206

      UPDATE igr_i_pkg_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,enquiry_appl_number,sales_lead_id)
             = (SELECT person_id,enquiry_appl_number,sales_lead_id
                FROM   igr_i_appl_int
                WHERE  interface_inq_appl_id = a.interface_inq_appl_id)
      WHERE  interface_inq_appl_id IN (SELECT interface_inq_appl_id
                                       FROM   igr_i_appl_int
                                       WHERE  interface_run_id = p_interface_run_id
                                       AND    status IN ('1','4'));

Line: 225

                               p_rule             => 'N'); -- Update not yet supported

Line: 239

      UPDATE igr_i_info_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,enquiry_appl_number)
             = (SELECT person_id,enquiry_appl_number
                FROM   igr_i_appl_int
                WHERE  interface_inq_appl_id = a.interface_inq_appl_id)
      WHERE  interface_inq_appl_id IN (SELECT interface_inq_appl_id
                                       FROM   igr_i_appl_int
                                       WHERE  interface_run_id = p_interface_run_id
                                       AND    status IN ('1','4'));

Line: 258

                                   p_rule             => 'N'); -- Update not yet supported

Line: 272

      UPDATE igr_i_char_int a
      SET    interface_run_id = p_interface_run_id,
             (person_id,enquiry_appl_number)
             = (SELECT person_id,enquiry_appl_number
                FROM   igr_i_appl_int
                WHERE  interface_inq_appl_id = a.interface_inq_appl_id)
      WHERE  interface_inq_appl_id IN (SELECT interface_inq_appl_id
                                       FROM   igr_i_appl_int
                                       WHERE  interface_run_id = p_interface_run_id
                                       AND    status IN ('1','4'));

Line: 291

                                   p_rule             => 'N'); -- Update not yet supported

Line: 303

   Purpose : Procedure is used to delete the records from the recruitment interface tables, which are processed successfully.
   Know limitations, enhancements or remarks
   Change History
   Who             When            What
   (reverse chronological order - newest change first)
  ***************************************************************/

  BEGIN

    IF g_inquiry_acad_int_inc THEN
      DELETE FROM igr_i_lines_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 320

      DELETE FROM igr_i_pkg_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 327

      DELETE FROM igr_i_info_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 334

      DELETE FROM igr_i_char_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;

Line: 341

      DELETE FROM igr_i_appl_int
      WHERE  status = '1'
      AND    interface_run_id = p_interface_run_id;