DBA Data[Home] [Help]

APPS.IGS_AS_DOC_FEE_PMNT_PKG SQL Statements

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

Line: 22

    x_last_update_date                  IN     DATE    ,
    x_last_updated_by                   IN     NUMBER  ,
    x_last_update_login                 IN     NUMBER  ,
    x_plan_id                           IN     NUMBER  ,
    x_invoice_id                        IN     NUMBER  ,
    x_plan_discon_from                  IN     DATE    ,
    x_plan_discon_by                    IN     NUMBER  ,
    x_num_of_copies                     IN     NUMBER  ,
    x_prev_paid_plan                    IN     VARCHAR2,
    x_cal_type                          IN     VARCHAR2,
    x_ci_sequence_number                IN     NUMBER  ,
    x_program_on_file                   IN     VARCHAR2
  ) AS
  /*
  ||  Created By : [email protected]
  ||  Created On : 07-FEB-2002
  ||  Purpose : Initialises the Old and New references for the columns of the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - newest change first)
  */
    CURSOR cur_old_ref_values IS
      SELECT   *
      FROM     igs_as_doc_fee_pmnt
      WHERE    ROWID = x_rowid;
Line: 54

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

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

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

    new_references.last_update_date                  := x_last_update_date;
Line: 76

    new_references.last_updated_by                   := x_last_updated_by;
Line: 77

    new_references.last_update_login                 := x_last_update_login;
Line: 103

    x_last_update_date                  IN     DATE    ,
    x_last_updated_by                   IN     NUMBER  ,
    x_last_update_login                 IN     NUMBER  ,
    x_plan_id                           IN     NUMBER  ,
    x_invoice_id                        IN     NUMBER  ,
    x_plan_discon_from                  IN     DATE    ,
    x_plan_discon_by                    IN     NUMBER  ,
    x_num_of_copies                     IN     NUMBER  ,
    x_prev_paid_plan                    IN     VARCHAR2,
    x_cal_type                          IN     VARCHAR2,
    x_ci_sequence_number                IN     NUMBER  ,
    x_program_on_file                   IN     VARCHAR2
  ) AS
  /*
  ||  Created By : [email protected]
  ||  Created On : 07-FEB-2002
  ||  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_person_id,
      x_fee_paid_date,
      x_fee_amount,
      x_fee_recorded_date,
      x_fee_recorded_by,
      x_creation_date,
      x_created_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login,
      x_plan_id          ,
      x_invoice_id       ,
      x_plan_discon_from ,
      x_plan_discon_by   ,
      x_num_of_copies    ,
      x_prev_paid_plan   ,
      x_cal_type         ,
      x_ci_sequence_number,
      x_program_on_file
    );
Line: 150

    IF (p_action = 'INSERT') THEN
      -- Check uniqueness if cal type and seq num are not null
      IF new_references.cal_type IS NOT NULL AND
         new_references.ci_sequence_number IS NOT NULL THEN
         IF ( check_unique_calseq(
              new_references.person_id,
              new_references.plan_id,
              new_references.cal_type,
              new_references.ci_sequence_number
            )
          ) THEN
            fnd_message.set_name('IGS','IGS_AS_PLAN_EXISTS_CAL');
Line: 169

  PROCEDURE insert_row (
    x_rowid                             IN OUT NOCOPY VARCHAR2,
    x_person_id                         IN     NUMBER,
    x_fee_paid_date                     IN     DATE,
    x_fee_amount                        IN     NUMBER,
    x_fee_recorded_date                 IN     DATE,
    x_fee_recorded_by                   IN     NUMBER,
    x_mode                              IN     VARCHAR2,
    x_plan_id                           IN     NUMBER  ,
    x_invoice_id                        IN     NUMBER  ,
    x_plan_discon_from                  IN     DATE    ,
    x_plan_discon_by                    IN     NUMBER  ,
    x_num_of_copies                     IN     NUMBER  ,
    x_prev_paid_plan                    IN     VARCHAR2,
    x_cal_type                          IN     VARCHAR2,
    x_ci_sequence_number                IN     NUMBER  ,
    x_program_on_file                   IN     VARCHAR2,
    X_RETURN_STATUS                     OUT NOCOPY    VARCHAR2,
    X_MSG_DATA                          OUT NOCOPY    VARCHAR2,
    X_MSG_COUNT                         OUT NOCOPY    NUMBER
  ) AS
  /*
  ||  Created By : [email protected]
  ||  Created On : 07-FEB-2002
  ||  Purpose : Handles the INSERT DML logic for the table.
  ||  Known limitations, enhancements or remarks :
  ||  Change History :
  ||  Who             When            What
  ||  (reverse chronological order - newest change first)
  */
    CURSOR c IS
      SELECT   ROWID
      FROM     igs_as_doc_fee_pmnt
      WHERE    person_id                         = x_person_id;
Line: 203

    x_last_update_date           DATE;
Line: 204

    x_last_updated_by            NUMBER;
Line: 205

    x_last_update_login          NUMBER;
Line: 208

    x_last_update_date := SYSDATE;
Line: 210

      x_last_updated_by := 1;
Line: 211

      x_last_update_login := 0;
Line: 213

      x_last_updated_by := fnd_global.user_id;
Line: 214

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

      x_last_update_login := fnd_global.login_id;
Line: 218

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

      p_action                            => 'INSERT',
      x_rowid                             => x_rowid,
      x_person_id                         => x_person_id,
      x_fee_paid_date                     => x_fee_paid_date,
      x_fee_amount                        => x_fee_amount,
      x_fee_recorded_date                 => x_fee_recorded_date,
      x_fee_recorded_by                   => x_fee_recorded_by,
      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 ,
      x_plan_id                           => x_plan_id           ,
      x_invoice_id                        => x_invoice_id        ,
      x_plan_discon_from                  => x_plan_discon_from  ,
      x_plan_discon_by                    => x_plan_discon_by    ,
      x_num_of_copies                     => x_num_of_copies     ,
      x_prev_paid_plan                    => x_prev_paid_plan    ,
      x_cal_type                          => x_cal_type          ,
      x_ci_sequence_number                => x_ci_sequence_number,
      x_program_on_file                   => x_program_on_file

    );
Line: 250

    INSERT INTO igs_as_doc_fee_pmnt (
      person_id,
      fee_paid_date,
      fee_amount,
      fee_recorded_date,
      fee_recorded_by,
      creation_date,
      created_by,
      last_update_date,
      last_updated_by,
      last_update_login,
      plan_id     ,
      invoice_id  ,
      plan_discon_from  ,
      plan_discon_by  ,
      num_of_copies  ,
      prev_paid_plan ,
      cal_type    ,
      ci_sequence_number,
      program_on_file
    ) VALUES (
      new_references.person_id,
      new_references.fee_paid_date,
      new_references.fee_amount,
      new_references.fee_recorded_date,
      new_references.fee_recorded_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login,
      new_references.plan_id           ,
      new_references.invoice_id        ,
      new_references.plan_discon_from  ,
      new_references.plan_discon_by    ,
      new_references.num_of_copies     ,
      new_references.prev_paid_plan    ,
      new_references.cal_type          ,
      new_references.ci_sequence_number,
      new_references.program_on_file
    );
Line: 327

             FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
Line: 335

  END insert_row;
Line: 367

      SELECT
        person_id,
        fee_paid_date,
        fee_amount,
        fee_recorded_date,
        fee_recorded_by,
        plan_id          ,
        invoice_id        ,
        plan_discon_from  ,
        plan_discon_by    ,
        num_of_copies     ,
        prev_paid_plan    ,
        cal_type          ,
        ci_sequence_number,
        program_on_file
      FROM  igs_as_doc_fee_pmnt
      WHERE ROWID = x_rowid
      FOR UPDATE NOWAIT;
Line: 391

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

         FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
Line: 460

  PROCEDURE update_row (
    x_rowid                             IN     VARCHAR2,
    x_person_id                         IN     NUMBER,
    x_fee_paid_date                     IN     DATE,
    x_fee_amount                        IN     NUMBER,
    x_fee_recorded_date                 IN     DATE,
    x_fee_recorded_by                   IN     NUMBER,
    x_mode                              IN     VARCHAR2,
    x_plan_id                           IN     NUMBER  ,
    x_invoice_id                        IN     NUMBER  ,
    x_plan_discon_from                  IN     DATE    ,
    x_plan_discon_by                    IN     NUMBER  ,
    x_num_of_copies                     IN     NUMBER  ,
    x_prev_paid_plan                    IN     VARCHAR2,
    x_cal_type                          IN     VARCHAR2,
    x_ci_sequence_number                IN     NUMBER  ,
    x_program_on_file                   IN     VARCHAR2,
    X_RETURN_STATUS                     OUT NOCOPY    VARCHAR2,
    X_MSG_DATA                          OUT NOCOPY    VARCHAR2,
    X_MSG_COUNT                         OUT NOCOPY    NUMBER
    ) AS
  /*
  ||  Created By : [email protected]
  ||  Created On : 07-FEB-2002
  ||  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: 491

    x_last_updated_by            NUMBER;
Line: 492

    x_last_update_login          NUMBER;
Line: 495

    x_last_update_date := SYSDATE;
Line: 497

      x_last_updated_by := 1;
Line: 498

      x_last_update_login := 0;
Line: 500

      x_last_updated_by := fnd_global.user_id;
Line: 501

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

      x_last_update_login := fnd_global.login_id;
Line: 505

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

      p_action                            => 'UPDATE',
      x_rowid                             => x_rowid,
      x_person_id                         => x_person_id,
      x_fee_paid_date                     => x_fee_paid_date,
      x_fee_amount                        => x_fee_amount,
      x_fee_recorded_date                 => x_fee_recorded_date,
      x_fee_recorded_by                   => x_fee_recorded_by,
      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,
      x_plan_id                           => x_plan_id           ,
      x_invoice_id                        => x_invoice_id        ,
      x_plan_discon_from                  => x_plan_discon_from  ,
      x_plan_discon_by                    => x_plan_discon_by    ,
      x_num_of_copies                     => x_num_of_copies     ,
      x_prev_paid_plan                    => x_prev_paid_plan    ,
      x_cal_type                          => x_cal_type          ,
      x_ci_sequence_number                => x_ci_sequence_number,
      x_program_on_file                   => x_program_on_file
    );
Line: 536

    UPDATE igs_as_doc_fee_pmnt
      SET
        fee_paid_date                     = new_references.fee_paid_date,
        fee_amount                        = new_references.fee_amount,
        fee_recorded_date                 = new_references.fee_recorded_date,
        fee_recorded_by                   = new_references.fee_recorded_by,
        last_update_date                  = x_last_update_date,
        last_updated_by                   = x_last_updated_by,
        last_update_login                 = x_last_update_login ,
        plan_id                           = new_references.plan_id,
        invoice_id                        = new_references.invoice_id   ,
        plan_discon_from                  = new_references.plan_discon_from ,
        plan_discon_by                    = new_references.plan_discon_by    ,
        num_of_copies                     = new_references.num_of_copies     ,
        prev_paid_plan                    = new_references.prev_paid_plan    ,
        cal_type                          = new_references.cal_type          ,
        ci_sequence_number                = new_references.ci_sequence_number,
        program_on_file                   = new_references.program_on_file
      WHERE ROWID = x_rowid;
Line: 608

           FND_MESSAGE.SET_TOKEN('NAME','Update_Row : '||SQLERRM);
Line: 617

  END update_row;
Line: 640

  ||  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_as_doc_fee_pmnt
      WHERE    person_id                         = x_person_id;
Line: 659

      insert_row (
        x_rowid,
        x_person_id,
        x_fee_paid_date,
        x_fee_amount,
        x_fee_recorded_date,
        x_fee_recorded_by,
        x_mode ,
        x_plan_id   ,
        x_invoice_id ,
        x_plan_discon_from  ,
        x_plan_discon_by    ,
        x_num_of_copies     ,
        x_prev_paid_plan    ,
        x_cal_type          ,
        x_ci_sequence_number,
        x_program_on_file,
        L_RETURN_STATUS ,
        L_MSG_DATA      ,
        L_MSG_COUNT
      );
Line: 683

    update_row (
      x_rowid,
      x_person_id,
      x_fee_paid_date,
      x_fee_amount,
      x_fee_recorded_date,
      x_fee_recorded_by,
      x_mode ,
      x_plan_id   ,
      x_invoice_id ,
      x_plan_discon_from  ,
      x_plan_discon_by    ,
      x_num_of_copies     ,
      x_prev_paid_plan    ,
      x_cal_type          ,
      x_ci_sequence_number,
      x_program_on_file   ,
      L_RETURN_STATUS     ,
      L_MSG_DATA          ,
      L_MSG_COUNT
    );
Line: 706

  PROCEDURE delete_row (
    x_rowid                             IN VARCHAR2 ,
    X_RETURN_STATUS                     OUT NOCOPY    VARCHAR2,
    X_MSG_DATA                          OUT NOCOPY    VARCHAR2,
    X_MSG_COUNT                         OUT NOCOPY    NUMBER
  ) AS
  /*
  ||  Created By : [email protected]
  ||  Created On : 07-FEB-2002
  ||  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
    FND_MSG_PUB.initialize;
Line: 724

      p_action => 'DELETE',
      x_rowid => x_rowid
    );
Line: 727

    DELETE FROM igs_as_doc_fee_pmnt
    WHERE ROWID = x_rowid;
Line: 759

           FND_MESSAGE.SET_TOKEN('NAME','Insert_Row : '||SQLERRM);
Line: 768

  END delete_row;
Line: 780

           SELECT 'Y'
	   FROM   igs_as_doc_fee_pmnt
	   WHERE  person_id  = cp_person_id AND
		  plan_id    = cp_plan_id  AND
		  cal_type   = cp_cal_type AND
		  ci_sequence_number  = cp_seq_num ;