DBA Data[Home] [Help]

APPS.IGS_EN_SPL_PERM_PKG SQL Statements

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

Line: 22

      SELECT   rowid
      FROM     hz_parties
      WHERE    party_id = x_party_id
      FOR UPDATE NOWAIT;
Line: 61

    x_last_update_date                  IN     DATE    ,
    x_last_updated_by                   IN     NUMBER  ,
    x_last_update_login                 IN     NUMBER
  ) AS
  /*
  ||  Created By : pradhakr
  ||  Created On : 29-JUN-2001
  ||  Purpose : Initialises the Old and New references for the columns of the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  Nishikant       13JUN2002       -- some commented codes were present here and removed as per bug#2413811
  ||  (reverse chronological order - newest change first)
  */

    CURSOR cur_old_ref_values IS
      SELECT   *
      FROM     IGS_EN_SPL_PERM
      WHERE    rowid = x_rowid;
Line: 92

    IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
      CLOSE cur_old_ref_values;
Line: 94

      fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
Line: 125

    IF (p_action = 'UPDATE') THEN
      new_references.creation_date                   := old_references.creation_date;
Line: 134

     new_references.last_update_date                  := x_last_update_date;
Line: 135

     new_references.last_updated_by                   := x_last_updated_by;
Line: 136

     new_references.last_update_login                 := x_last_update_login;
Line: 141

  PROCEDURE beforerowinsertupdatedelete1(
    p_inserting IN BOOLEAN,
    p_updating IN BOOLEAN ,
    p_deleting IN BOOLEAN
    )  AS
  /************************************************************************
  Created By                                :
  Date Created By                           :
  Purpose                                   :
  Known limitations, enhancements or remarks:
  Change History                            :
  Who          When          What
  (reverse chronological order - newest change first)
  *************************************************************************/
  l_message_name VARCHAR2(30);
Line: 170

  SELECT ROWID
  FROM igs_en_spl_perm_h
  WHERE spl_perm_request_id  = old_references.spl_perm_request_id;
Line: 265

       SELECT
       decode(old_references.spl_perm_request_id,new_references.spl_perm_request_id,
       NULL,old_references.spl_perm_request_id),
       decode(old_references.date_submission,new_references.date_submission,
       NULL,old_references.date_submission),
       decode(old_references.audit_the_course,new_references.audit_the_course,
       NULL,old_references.audit_the_course),
       decode(old_references.approval_status,new_references.approval_status,
       NULL,old_references.approval_status),
       decode(old_references.reason_for_request,new_references.reason_for_request,
       NULL,old_references.reason_for_request),
       decode(old_references.instructor_more_info,new_references.instructor_more_info,
       NULL,old_references.instructor_more_info),
       decode(old_references.instructor_deny_info,new_references.instructor_deny_info,
       NULL,old_references.instructor_deny_info),
       decode(old_references.student_more_info,new_references.student_more_info,
       NULL,old_references.student_more_info),
       decode(old_references.transaction_type,new_references.transaction_type,
       NULL,old_references.transaction_type)

       INTO
        l_spl_perm_request_id,
        l_date_submission,
        l_audit_the_course,
        l_approval_status,
        l_reason_for_request,
        l_instructor_more_info,
        l_instructor_deny_info,
        l_student_more_info,
        l_transaction_type
       FROM dual;
Line: 298

       igs_en_spl_perm_h_pkg.insert_row(
          l_rowid,
          l_spl_perm_request_h_id,
          l_spl_perm_request_id,
          l_date_submission,
          l_audit_the_course,
          l_approval_status ,
          l_reason_for_request,
          l_instructor_more_info,
          l_instructor_deny_info,
          l_student_more_info,
          l_transaction_type,
          old_references.last_update_date,
          new_references.last_update_date,
          old_references.last_updated_by
          );
Line: 322

              igs_en_spl_perm_h_pkg.delete_row(x_rowid  => splh_rec.rowid);
Line: 327

  END beforerowinsertupdatedelete1;
Line: 348

      fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
Line: 359

      fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
Line: 370

      fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
Line: 382

         Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Line: 425

      SELECT   rowid
      FROM     igs_en_spl_perm
      WHERE    spl_perm_request_id = x_spl_perm_request_id
      FOR UPDATE NOWAIT;
Line: 460

      SELECT   rowid
      FROM     igs_en_spl_perm
      WHERE   ((student_person_id = x_party_id))
      OR      ((instructor_person_id = x_party_id));
Line: 496

      SELECT   rowid
      FROM     igs_en_spl_perm
      WHERE   ((uoo_id = x_uoo_id));
Line: 536

    x_last_update_date                  IN     DATE    ,
    x_last_updated_by                   IN     NUMBER  ,
    x_last_update_login                 IN     NUMBER
  ) AS
  /*
  ||  Created By : pradhakr
  ||  Created On : 29-JUN-2001
  ||  Purpose : Initialises the columns, Checks Constraints, Calls the
  ||            Trigger Handlers for the table, before any DML operation.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - newest change first)
  */


  BEGIN

    set_column_values (
      p_action,
      x_rowid,
      x_spl_perm_request_id,
      x_student_person_id,
      x_uoo_id,
      x_date_submission,
      x_audit_the_course,
      x_instructor_person_id,
      x_approval_status,
      x_reason_for_request,
      x_instructor_more_info,
      x_instructor_deny_info,
      x_student_more_info,
      x_transaction_type,
      x_request_type,
      x_creation_date,
      x_created_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login
    );
Line: 577

    IF (p_action = 'INSERT') THEN
       -- Call all the procedures related to Before Insert.
      IF ( get_pk_for_validation(
             new_references.spl_perm_request_id
           )
         ) THEN
        fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
Line: 588

    ELSIF (p_action = 'UPDATE') THEN
      -- Call all the procedures related to Before Update.
      beforerowinsertupdatedelete1 ( p_inserting => FALSE,
                                     p_updating  => TRUE ,
                                     p_deleting  => FALSE);
Line: 594

    ELSIF (p_action = 'DELETE') THEN
      -- Call all the procedures related to Before Delete.
      beforerowinsertupdatedelete1 ( p_inserting => FALSE,
                                     p_updating  => FALSE ,
                                     p_deleting  => TRUE);
Line: 600

    ELSIF (p_action = 'VALIDATE_INSERT') THEN
      -- Call all the procedures related to Before Insert.
      IF ( get_pk_for_validation (
             new_references.spl_perm_request_id
           )
         ) THEN
        fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
Line: 610

    ELSIF (p_action = 'VALIDATE_DELETE') THEN
      check_child_existance;
Line: 622

    IF (p_action IN ('INSERT','UPDATE')) THEN

      IF new_references.transaction_type IN ('STD_REQ','STD_AU_REQ','STD_MI') THEN
         igs_en_workflow.inform_instruct_stdnt_petition( p_student_id       => new_references.student_person_id,
                                                         p_instructor_id    => new_references.instructor_person_id,
                                                         p_uoo_id           => new_references.uoo_id,
                                                         p_date_submission  => new_references.date_submission,
                                                         p_transaction_type => new_references.transaction_type,
                                                         p_request_type     => new_references.request_type
                                                       );
Line: 649

  PROCEDURE insert_row (
    x_rowid                             IN OUT NOCOPY VARCHAR2,
    x_spl_perm_request_id               IN OUT NOCOPY NUMBER,
    x_student_person_id                 IN     NUMBER,
    x_uoo_id                            IN     NUMBER,
    x_date_submission                   IN     DATE,
    x_audit_the_course                  IN     VARCHAR2,
    x_instructor_person_id              IN     NUMBER,
    x_approval_status                   IN     VARCHAR2,
    x_reason_for_request                IN     VARCHAR2,
    x_instructor_more_info              IN     VARCHAR2,
    x_instructor_deny_info              IN     VARCHAR2,
    x_student_more_info                 IN     VARCHAR2,
    x_transaction_type                  IN     VARCHAR2,
    x_request_type                      IN     VARCHAR2,
    x_mode                              IN     VARCHAR2
  ) AS
  /*
  ||  Created By : pradhakr
  ||  Created On : 29-JUN-2001
  ||  Purpose : Handles the INSERT DML logic for the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - nbabyewest change first)
  */
    CURSOR c IS
      SELECT   rowid
      FROM     igs_en_spl_perm
      WHERE    spl_perm_request_id               = x_spl_perm_request_id;
Line: 681

    SELECT rowid, approval_status, transaction_type FROM igs_en_spl_perm
    WHERE student_person_id = x_student_person_id AND
          uoo_id = x_uoo_id AND
          request_type = x_request_type;
Line: 686

    x_last_update_date           DATE;
Line: 687

    x_last_updated_by            NUMBER;
Line: 688

    x_last_update_login          NUMBER;
Line: 696

    x_last_update_date := SYSDATE;
Line: 698

      x_last_updated_by := 1;
Line: 699

      x_last_update_login := 0;
Line: 701

      x_last_updated_by := fnd_global.user_id;
Line: 702

      IF (x_last_updated_by IS NULL) THEN
        x_last_updated_by := -1;
Line: 705

      x_last_update_login := fnd_global.login_id;
Line: 706

      IF (x_last_update_login IS NULL) THEN
        x_last_update_login := -1;
Line: 715

    SELECT    igs_en_spl_perm_s.NEXTVAL
    INTO      x_spl_perm_request_id
    FROM      dual;
Line: 725

            update_row (
              x_rowid,
              x_spl_perm_request_id,
              x_student_person_id,
              x_uoo_id,
              x_date_submission,
              x_audit_the_course,
              x_instructor_person_id,
              x_approval_status,
              x_reason_for_request,
              x_instructor_more_info,
              x_instructor_deny_info,
              x_student_more_info,
              x_transaction_type,
              x_request_type,
              x_mode
            );
Line: 753

      p_action                            => 'INSERT',
      x_rowid                             => x_rowid,
      x_spl_perm_request_id               => x_spl_perm_request_id,
      x_student_person_id                 => x_student_person_id,
      x_uoo_id                            => x_uoo_id,
      x_date_submission                   => x_date_submission,
      x_audit_the_course                  => x_audit_the_course,
      x_instructor_person_id              => x_instructor_person_id,
      x_approval_status                   => x_approval_status,
      x_reason_for_request                => x_reason_for_request,
      x_instructor_more_info              => x_instructor_more_info,
      x_instructor_deny_info              => x_instructor_deny_info,
      x_student_more_info                 => x_student_more_info,
      x_transaction_type                  => x_transaction_type,
      x_request_type                      => x_request_type,
      x_creation_date                     => x_last_update_date,
      x_created_by                        => x_last_updated_by,
      x_last_update_date                  => x_last_update_date,
      x_last_updated_by                   => x_last_updated_by,
      x_last_update_login                 => x_last_update_login
    );
Line: 775

    INSERT INTO igs_en_spl_perm (
      spl_perm_request_id,
      student_person_id,
      uoo_id,
      date_submission,
      audit_the_course,
      instructor_person_id,
      approval_status,
      reason_for_request,
      instructor_more_info,
      instructor_deny_info,
      student_more_info,
      transaction_type,
      request_type,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      last_update_login
    ) VALUES (
      new_references.spl_perm_request_id,
      new_references.student_person_id,
      new_references.uoo_id,
      new_references.date_submission,
      new_references.audit_the_course,
      new_references.instructor_person_id,
      new_references.approval_status,
      new_references.reason_for_request,
      new_references.instructor_more_info,
      new_references.instructor_deny_info,
      new_references.student_more_info,
      new_references.transaction_type,
      new_references.request_type,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login
    );
Line: 824

    p_action =>'INSERT',
    x_rowid => X_ROWID
  );
Line: 828

  END insert_row;
Line: 857

      SELECT
        student_person_id,
        uoo_id,
        date_submission,
        audit_the_course,
        instructor_person_id,
        approval_status,
        reason_for_request,
        instructor_more_info,
        instructor_deny_info,
        student_more_info,
        transaction_type,
        request_type
      FROM  igs_en_spl_perm
      WHERE rowid = x_rowid
      FOR UPDATE NOWAIT;
Line: 881

      fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
Line: 915

  PROCEDURE update_row (
    x_rowid                             IN     VARCHAR2,
    x_spl_perm_request_id               IN     NUMBER,
    x_student_person_id                 IN     NUMBER,
    x_uoo_id                            IN     NUMBER,
    x_date_submission                   IN     DATE,
    x_audit_the_course                  IN     VARCHAR2,
    x_instructor_person_id              IN     NUMBER,
    x_approval_status                   IN     VARCHAR2,
    x_reason_for_request                IN     VARCHAR2,
    x_instructor_more_info              IN     VARCHAR2,
    x_instructor_deny_info              IN     VARCHAR2,
    x_student_more_info                 IN     VARCHAR2,
    x_transaction_type                  IN     VARCHAR2,
    x_request_type                      IN     VARCHAR2,
    x_mode                              IN     VARCHAR2
  ) AS
  /*
  ||  Created By : pradhakr
  ||  Created On : 29-JUN-2001
  ||  Purpose : Handles the UPDATE DML logic for the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - newest change first)
  */
    x_last_update_date           DATE ;
Line: 942

    x_last_updated_by            NUMBER;
Line: 943

    x_last_update_login          NUMBER;
Line: 948

    x_last_update_date := SYSDATE;
Line: 950

      x_last_updated_by := 1;
Line: 951

      x_last_update_login := 0;
Line: 953

      x_last_updated_by := fnd_global.user_id;
Line: 954

      IF x_last_updated_by IS NULL THEN
        x_last_updated_by := -1;
Line: 957

      x_last_update_login := fnd_global.login_id;
Line: 958

      IF (x_last_update_login IS NULL) THEN
        x_last_update_login := -1;
Line: 968

      p_action                            => 'UPDATE',
      x_rowid                             => x_rowid,
      x_spl_perm_request_id               => x_spl_perm_request_id,
      x_student_person_id                 => x_student_person_id,
      x_uoo_id                            => x_uoo_id,
      x_date_submission                   => x_date_submission,
      x_audit_the_course                  => x_audit_the_course,
      x_instructor_person_id              => x_instructor_person_id,
      x_approval_status                   => x_approval_status,
      x_reason_for_request                => x_reason_for_request,
      x_instructor_more_info              => x_instructor_more_info,
      x_instructor_deny_info              => x_instructor_deny_info,
      x_student_more_info                 => x_student_more_info,
      x_transaction_type                  => x_transaction_type,
      x_request_type                      => x_request_type,
      x_creation_date                     => x_last_update_date,
      x_created_by                        => x_last_updated_by,
      x_last_update_date                  => x_last_update_date,
      x_last_updated_by                   => x_last_updated_by,
      x_last_update_login                 => x_last_update_login
    );
Line: 991

    UPDATE igs_en_spl_perm
      SET
        student_person_id                 = new_references.student_person_id,
        uoo_id                            = new_references.uoo_id,
        date_submission                   = new_references.date_submission,
        audit_the_course                  = new_references.audit_the_course,
        instructor_person_id              = new_references.instructor_person_id,
        approval_status                   = new_references.approval_status,
        reason_for_request                = new_references.reason_for_request,
        instructor_more_info              = new_references.instructor_more_info,
        instructor_deny_info              = new_references.instructor_deny_info,
        student_more_info                 = new_references.student_more_info,
        transaction_type                  = new_references.transaction_type,
        request_type                      = new_references.request_type,
        last_update_date                  = x_last_update_date,
        last_updated_by                   = x_last_updated_by,
        last_update_login                 = x_last_update_login
      WHERE rowid = x_rowid;
Line: 1015

    p_action =>'UPDATE',
    x_rowid => X_ROWID
  );
Line: 1019

  END update_row;
Line: 1042

  ||  Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - newest change first)
  */
    CURSOR c1 IS
      SELECT   rowid
      FROM     igs_en_spl_perm
      WHERE    spl_perm_request_id               = x_spl_perm_request_id;
Line: 1060

      insert_row (
        x_rowid,
        x_spl_perm_request_id,
        x_student_person_id,
        x_uoo_id,
        x_date_submission,
        x_audit_the_course,
        x_instructor_person_id,
        x_approval_status,
        x_reason_for_request,
        x_instructor_more_info,
        x_instructor_deny_info,
        x_student_more_info,
        x_transaction_type,
        x_request_type,
        x_mode
      );
Line: 1081

    update_row (
      x_rowid,
      x_spl_perm_request_id,
      x_student_person_id,
      x_uoo_id,
      x_date_submission,
      x_audit_the_course,
      x_instructor_person_id,
      x_approval_status,
      x_reason_for_request,
      x_instructor_more_info,
      x_instructor_deny_info,
      x_student_more_info,
      x_transaction_type,
      x_request_type,
      x_mode
    );
Line: 1102

  PROCEDURE delete_row (
    x_rowid IN VARCHAR2
  ) AS
  /*
  ||  Created By : pradhakr
  ||  Created On : 29-JUN-2001
  ||  Purpose : Handles the DELETE DML logic for the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - newest change first)
  */
  BEGIN

    before_dml (
      p_action => 'DELETE',
      x_rowid => x_rowid
    );
Line: 1121

    DELETE FROM igs_en_spl_perm
    WHERE rowid = x_rowid;
Line: 1128

  END delete_row;