APPS.IGS_PS_PURGE_DEL_RECORD SQL Statements

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

Line: 10

  Purpose        :  To purge logically deleted records from the table IGS_PS_OFR_OPT_ALL.
                    If any child record exists for a logically deleted record then that
                    record will be updated such that it becomes active else it will be deleted.

  Known limitations,enhancements,remarks:
  Change History
  Who        When             What
********************************************************************************************** */
CURSOR c_del_recs IS
SELECT rowid, a.*
FROM igs_ps_ofr_opt_all a
WHERE delete_flag = 'Y';

Line: 44

        p_action                => 'VALIDATE_DELETE' ,
        x_rowid                 => rec_del_recs.rowid ,
        x_course_cd             => rec_del_recs.course_cd ,
        x_version_number        => rec_del_recs.version_number ,
        x_cal_type              => rec_del_recs.cal_type ,
        x_location_cd           => rec_del_recs.location_cd ,
        x_attendance_mode       => rec_del_recs.attendance_mode ,
        x_attendance_type       => rec_del_recs.attendance_type ,
        x_coo_id                => rec_del_recs.coo_id ,
        x_forced_location_ind   => rec_del_recs.forced_location_ind ,
        x_forced_att_mode_ind   => rec_del_recs.forced_att_mode_ind ,
        x_forced_att_type_ind   => rec_del_recs.forced_att_type_ind ,
        x_time_limitation       => rec_del_recs.time_limitation ,
        x_enr_officer_person_id => rec_del_recs.enr_officer_person_id ,
        x_attribute_category    => rec_del_recs.attribute_category ,
        x_attribute1            => rec_del_recs.attribute1 ,
        x_attribute2            => rec_del_recs.attribute2 ,
        x_attribute3            => rec_del_recs.attribute3 ,
        x_attribute4            => rec_del_recs.attribute4 ,
        x_attribute5            => rec_del_recs.attribute5 ,
        x_attribute6            => rec_del_recs.attribute6 ,
        x_attribute7            => rec_del_recs.attribute7 ,
        x_attribute8            => rec_del_recs.attribute8 ,
        x_attribute9            => rec_del_recs.attribute9 ,
        x_attribute10           => rec_del_recs.attribute10 ,
        x_attribute11           => rec_del_recs.attribute11 ,
        x_attribute12           => rec_del_recs.attribute12 ,
        x_attribute13           => rec_del_recs.attribute13 ,
        x_attribute14           => rec_del_recs.attribute14 ,
        x_attribute15           => rec_del_recs.attribute15 ,
        x_attribute16           => rec_del_recs.attribute16 ,
        x_attribute17           => rec_del_recs.attribute17 ,
        x_attribute18           => rec_del_recs.attribute18 ,
        x_attribute19           => rec_del_recs.attribute19 ,
        x_attribute20           => rec_del_recs.attribute20 ,
        x_creation_date         => rec_del_recs.creation_date ,
        x_created_by            => rec_del_recs.created_by ,
        x_last_update_date      => rec_del_recs.last_update_date ,
        x_last_updated_by       => rec_del_recs.last_updated_by ,
        x_last_update_login     => rec_del_recs.last_update_login ,
        x_org_id                => rec_del_recs.org_id ,
        x_program_length        => rec_del_recs.program_length ,
        x_program_length_measurement => rec_del_recs.program_length_measurement
      );

Line: 97

        IGS_GE_MSG_STACK.DELETE_MSG ;

Line: 100

        igs_ps_ofr_opt_pkg.update_row (
          x_rowid                 => rec_del_recs.rowid ,
          x_course_cd             => rec_del_recs.course_cd ,
          x_version_number        => rec_del_recs.version_number ,
          x_cal_type              => rec_del_recs.cal_type ,
          x_location_cd           => rec_del_recs.location_cd ,
          x_attendance_mode       => rec_del_recs.attendance_mode ,
          x_attendance_type       => rec_del_recs.attendance_type ,
          x_coo_id                => rec_del_recs.coo_id ,
          x_forced_location_ind   => rec_del_recs.forced_location_ind ,
          x_forced_att_mode_ind   => rec_del_recs.forced_att_mode_ind ,
          x_forced_att_type_ind   => rec_del_recs.forced_att_type_ind ,
          x_time_limitation       => rec_del_recs.time_limitation ,
          x_enr_officer_person_id => rec_del_recs.enr_officer_person_id ,
          x_attribute_category    => rec_del_recs.attribute_category ,
          x_attribute1            => rec_del_recs.attribute1 ,
          x_attribute2            => rec_del_recs.attribute2 ,
          x_attribute3            => rec_del_recs.attribute3 ,
          x_attribute4            => rec_del_recs.attribute4 ,
          x_attribute5            => rec_del_recs.attribute5 ,
          x_attribute6            => rec_del_recs.attribute6 ,
          x_attribute7            => rec_del_recs.attribute7 ,
          x_attribute8            => rec_del_recs.attribute8 ,
          x_attribute9            => rec_del_recs.attribute9 ,
          x_attribute10           => rec_del_recs.attribute10 ,
          x_attribute11           => rec_del_recs.attribute11 ,
          x_attribute12           => rec_del_recs.attribute12 ,
          x_attribute13           => rec_del_recs.attribute13 ,
          x_attribute14           => rec_del_recs.attribute14 ,
          x_attribute15           => rec_del_recs.attribute15 ,
          x_attribute16           => rec_del_recs.attribute16 ,
          x_attribute17           => rec_del_recs.attribute17 ,
          x_attribute18           => rec_del_recs.attribute18 ,
          x_attribute19           => rec_del_recs.attribute19 ,
          x_attribute20           => rec_del_recs.attribute20 ,
          x_program_length        => rec_del_recs.program_length ,
          x_program_length_measurement => rec_del_recs.program_length_measurement,
          x_delete_flag           => 'N'
        );

Line: 142

      igs_ps_ofr_opt_pkg.delete_row ( x_rowid => rec_del_recs.rowid );

Line: 161

      fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
                                                  || '     : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'Y' ) );

Line: 166

      fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
                                                  || '     : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'N' ) );

Line: 169

                                                  || '          : ' || igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'UPDATED' ) );

Line: 204

  Purpose        :  To purge logically deleted records from the table IGS_PS_UNIT_OFR_PAT_ALL.
                    If any child record exists for a logically deleted record then that
                    record will be updated such that it becomes active else it will be deleted.

  Known limitations,enhancements,remarks:
  Change History
  Who        When             What

********************************************************************************************** */
CURSOR c_del_recs IS
SELECT a.rowid, a.*
FROM igs_ps_unit_ofr_pat_all a
WHERE a.delete_flag = 'Y';

Line: 221

SELECT alternate_code
FROM   igs_ca_inst_all
WHERE  cal_type        = cp_cal_type
AND    sequence_number = cp_sequence_number;

Line: 246

        p_action                        => 'VALIDATE_DELETE' ,
        x_rowid                         => rec_del_recs.rowid,
        x_unit_cd                       => rec_del_recs.unit_cd,
        x_version_number                => rec_del_recs.version_number,
        x_cal_type                      => rec_del_recs.cal_type,
        x_ci_sequence_number            => rec_del_recs.ci_sequence_number,
        x_ci_start_dt                   => rec_del_recs.ci_start_dt,
        x_ci_end_dt                     => rec_del_recs.ci_end_dt,
        x_waitlist_allowed              => rec_del_recs.waitlist_allowed,
        x_max_students_per_waitlist     => rec_del_recs.max_students_per_waitlist,
        x_creation_date                 => rec_del_recs.creation_date,
        x_created_by                    => rec_del_recs.created_by,
        x_last_update_date              => rec_del_recs.last_update_date,
        x_last_updated_by               => rec_del_recs.last_updated_by,
        x_last_update_login             => rec_del_recs.last_update_login,
        x_org_id                        => rec_del_recs.org_id,
        x_delete_flag                   => rec_del_recs.delete_flag ,
        x_abort_flag                    => rec_del_recs.abort_flag
      );

Line: 274

        IGS_GE_MSG_STACK.DELETE_MSG ;

Line: 277

        igs_ps_unit_ofr_pat_pkg.update_row (
          x_rowid                     => rec_del_recs.rowid ,
          x_unit_cd                   => rec_del_recs.unit_cd,
          x_version_number            => rec_del_recs.version_number,
          x_ci_sequence_number        => rec_del_recs.ci_sequence_number,
          x_cal_type                  => rec_del_recs.cal_type,
          x_ci_start_dt               => rec_del_recs.ci_start_dt,
          x_ci_end_dt                 => rec_del_recs.ci_end_dt,
          x_waitlist_allowed          => rec_del_recs.waitlist_allowed,
          x_max_students_per_waitlist => rec_del_recs.max_students_per_waitlist,
          x_delete_flag               => 'N',
          x_abort_flag                => rec_del_recs.abort_flag
        );

Line: 293

      igs_ps_unit_ofr_pat_pkg.delete_row ( x_rowid => rec_del_recs.rowid );

Line: 311

      fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
                                                  || '     : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'Y' ) );

Line: 316

      fnd_file.put_line ( fnd_file.LOG, igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'DELETE_FLAG' )
                                                  || '     : ' || igs_ge_gen_004.genp_get_lookup ( 'TIMESLOT_ALPHABET', 'N' ) );

Line: 319

                                                  || '          : ' || igs_ge_gen_004.genp_get_lookup ( 'IGS_PS_LOG_PARAMETERS', 'UPDATED' ) );

Line: 352

  Purpose        :  To purge logically deleted records from the tables, where logical delete
                    functionality is being used. If any child record exists for a logically deleted
                    record then that record will be updated such that it becomes active.
                    This package is specific for Program Structure and Planning module only i.e.,
                    this will process only tables of PSP module. The process will currently be
                    purging/activating logically deleted records for IGS_PS_OFR_OPT_ALL table.

  Known limitations,enhancements,remarks:
  Change History
  Who        When             What

********************************************************************************************** */


BEGIN

  -- This sub-process should take care of all the PS tables which have delete_flag.
  -- Processing of each table is independent in the sense that processing of each table
  -- should be treated as one transaction. So exceptions raised in processing of one table
  -- should not affect the processing of another table. So COMMIT, and ROLLBACK are at table level
  -- instead of at the module level.

  -- Begin Processing Records of IGS_PS_OFR_OPT_ALL table.
  purge_igs_ps_ofr_opt_all;