APPS.IGS_PE_PERS_ENCUMB_PKG SQL Statements

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

Line: 36

    x_last_update_date IN DATE,
    x_last_updated_by IN NUMBER,
    x_last_update_login IN NUMBER
  ) AS

    CURSOR cur_old_ref_values IS
      SELECT   *
      FROM     igs_pe_pers_encumb
      WHERE    rowid = x_rowid;

Line: 54

    IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
      Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');

Line: 77

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

Line: 84

    new_references.last_update_date := x_last_update_date;

Line: 85

    new_references.last_updated_by := x_last_updated_by;

Line: 86

    new_references.last_update_login := x_last_update_login;

Line: 90

  PROCEDURE BeforeRowInsertUpdate1(
    p_inserting IN BOOLEAN,
    p_updating IN BOOLEAN,
    p_deleting IN BOOLEAN
    ) AS
/*------------------------------------------------------------------
 Change History

 Bug ID : 2000408
 who      when          what
 PKPATEL  8-APR-2003    Bug No: 2804863, Added the check with igs_pe_gen_001.g_hold_validation for calling
                        igs_pe_gen_001.get_hold_auth
                                                Added system_type_rec.s_encumbrance_cat = 'ACADEMIC' check to validate staff and person ID.
vkarthik		16-Jul-2004  Added validation on hold start date and hold expiry date as part of Bug 3771317
------------------------------------------------------------------*/
        CURSOR cur_hold_ovr IS
        SELECT hold_old_end_dt
        FROM igs_pe_hold_rel_ovr hovr, igs_en_elgb_ovr_all ovr
        WHERE ovr.elgb_override_id =hovr.elgb_override_id  AND
        ovr.person_id = new_references.person_id AND new_references.start_dt = hovr.start_date
        AND new_references.encumbrance_type = hovr.hold_type;

Line: 113

        SELECT s_encumbrance_cat
        FROM   igs_fi_encmb_type
        WHERE  encumbrance_type = cp_encumbrance_type;

Line: 151

        IF p_inserting THEN

                IF igs_en_val_pen.enrp_val_prsn_encmb (
                                new_references.person_id,
                                new_references.encumbrance_type ,
                                new_references.start_dt,
                                new_references.expiry_dt,
                                v_message_name) = FALSE THEN

                 FND_MESSAGE.SET_NAME('IGS', v_message_name);

Line: 198

                (p_inserting OR (p_updating AND
                (NVL(old_references.start_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
                <> new_references.start_dt)))
                THEN

                IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
                                new_references.start_dt,
                                v_message_name) = FALSE THEN

                          Fnd_Message.Set_Name('IGS', v_message_name);

Line: 225

                (p_inserting OR (p_updating AND
                (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
                <> new_references.expiry_dt)))
                THEN
                IF IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (
                                new_references.start_dt,
                                new_references.expiry_dt,
                                v_message_name) = FALSE THEN
                          Fnd_Message.Set_Name('IGS', v_message_name);

Line: 252

           (p_inserting OR
            ( p_updating AND (new_references.authorising_person_id <> NVL(old_references.authorising_person_id,-1))))
            THEN

                IF IGS_CO_VAL_OC.genp_val_prsn_id (
                        new_references.authorising_person_id,
                        v_message_name) = FALSE THEN
                          FND_MESSAGE.SET_NAME('IGS', v_message_name);

Line: 274

  END BeforeRowInsertUpdate1;

Line: 282

  PROCEDURE AfterRowInsertUpdate2(
    p_inserting IN BOOLEAN,
    p_updating IN BOOLEAN,
    p_deleting IN BOOLEAN
    ) AS
        v_message_name  varchar2(30);

Line: 300

        IF p_inserting THEN
                -- Cannot call IGS_EN_GEN_009.ENRP_INS_DFLT_EFFECT because trigger will be mutating.
                 -- Save the rowid of the current row.
                IF v_rowid_saved = FALSE THEN

                        v_rowid_saved := TRUE;

Line: 338

                IF p_inserting THEN
                        IF IGS_EN_VAL_PEN.enrp_val_prsn_encmb (new_references.person_id,
                                                new_references.encumbrance_type,
                                                new_references.start_dt,
                                                new_references.expiry_dt,
                                                v_message_name) = FALSE THEN
                                 Fnd_Message.Set_Name('IGS', v_message_name);

Line: 372

  END AfterRowInsertUpdate2;

Line: 408

          SELECT 'X'
          FROM   fnd_responsibility
          WHERE  application_id = cp_appl_id AND
	             responsibility_id = cp_resp_id;

Line: 414

          SELECT 'X'
          FROM   fnd_responsibility resp, fnd_application appl
          WHERE  resp.application_id = appl.application_id AND
	             responsibility_id = cp_resp_id;

Line: 424

          SELECT 'X'
          FROM fnd_lookup_values_vl
          WHERE lookup_code = p_lookup_code AND
	       lookup_type = p_lookup_type AND
	       view_application_id = p_view_application_id AND
	       security_group_id = p_security_group_id  AND
	       NVL(enabled_flag,'Y') = 'Y';

Line: 447

				   FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');

Line: 457

				   FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');

Line: 488

               Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');

Line: 501

               Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');

Line: 513

               Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');

Line: 529

               fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');

Line: 548

               fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');

Line: 572

      SELECT   rowid
      FROM     IGS_PE_PERS_ENCUMB
      WHERE    person_id = x_person_id
      AND      encumbrance_type  = x_encumbrance_type
      AND      start_dt = x_start_dt
      FOR UPDATE NOWAIT;

Line: 600

      SELECT   rowid
      FROM     IGS_PE_PERS_ENCUMB
      WHERE   (
                  CAL_TYPE = X_CAL_TYPE AND SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
          );

Line: 625

      SELECT   rowid
      FROM     IGS_PE_PERS_ENCUMB
      WHERE    encumbrance_type  = x_encumbrance_type ;

Line: 651

      SELECT   rowid
      FROM     IGS_PE_PERS_ENCUMB
      WHERE    authorising_person_id = x_person_id  OR
               person_id = x_person_id ;

Line: 690

    x_last_update_date IN DATE,
    x_last_updated_by IN NUMBER,
    x_last_update_login IN NUMBER
  ) AS
  BEGIN
    Set_Column_Values (
      p_action,
      x_rowid,
      x_comments,
      x_person_id,
      x_encumbrance_type,
      x_start_dt,
      x_expiry_dt,
      x_authorising_person_id,
      x_spo_course_cd,
      x_spo_sequence_number,
      x_cal_type,
      x_sequence_number,
      x_auth_resp_id,
      x_external_reference,
      x_creation_date,
      x_created_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login
    );

Line: 717

     IF (p_action = 'INSERT') THEN
       -- Call all the procedures related to Before Insert.

     BeforeRowInsertUpdate1 (
          p_inserting => TRUE,
      p_updating  => FALSE,
          p_deleting  => FALSE);

Line: 738

 ELSIF (p_action = 'UPDATE') THEN

       -- Call all the procedures related to Before Update.
       BeforeRowInsertUpdate1 (
          p_inserting => FALSE,
          p_updating  => TRUE,
              p_deleting  => FALSE );

Line: 750

 ELSIF (p_action = 'DELETE') THEN
       -- Call all the procedures related to Before Delete.

       Check_Child_Existance; -- if procedure present

Line: 754

 ELSIF (p_action = 'VALIDATE_INSERT') THEN

      IF  Get_PK_For_Validation (
          new_references.person_id,
          new_references.encumbrance_type ,
          new_references.start_dt) THEN
         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');

Line: 766

 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
       Check_Constraints; -- if procedure present

Line: 769

ELSIF (p_action = 'VALIDATE_DELETE') THEN
      Check_Child_Existance; -- if procedure present

Line: 783

    IF (p_action = 'INSERT') THEN
      -- Call all the procedures related to After Insert.

      AfterRowInsertUpdate2 (
          p_inserting => TRUE,
          p_updating  => FALSE,
              p_deleting  => FALSE);

Line: 792

    ELSIF (p_action = 'UPDATE') THEN
      -- Call all the procedures related to After Update.
      AfterRowInsertUpdate2 (
          p_inserting => FALSE,
          p_updating  => TRUE,
              p_deleting  => FALSE );

Line: 799

    ELSIF (p_action = 'DELETE') THEN
      -- Call all the procedures related to After Delete.
      Null;

Line: 806

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_PERSON_ID in NUMBER,
  X_ENCUMBRANCE_TYPE in VARCHAR2,
  X_START_DT in DATE,
  X_EXPIRY_DT in DATE,
  X_AUTHORISING_PERSON_ID in NUMBER,
  X_COMMENTS in VARCHAR2,
  X_SPO_COURSE_CD in VARCHAR2,
  X_SPO_SEQUENCE_NUMBER in NUMBER,
  X_CAL_TYPE           IN   VARCHAR2,
  X_SEQUENCE_NUMBER    IN   NUMBER,
  x_auth_resp_id IN NUMBER,
  x_external_reference IN VARCHAR2 ,
  X_MODE IN VARCHAR2
  ) AS
    CURSOR C (cp_start_dt igs_pe_pers_encumb.start_dt%TYPE) IS
      SELECT ROWID FROM igs_pe_pers_encumb
      WHERE person_id = x_person_id
      AND encumbrance_type = x_encumbrance_type
      AND start_dt = cp_start_dt;

Line: 829

        SELECT s_encumbrance_cat
        FROM   igs_fi_encmb_type
        WHERE  encumbrance_type = cp_encumbrance_type;

Line: 834

    X_LAST_UPDATE_DATE DATE;

Line: 835

    X_LAST_UPDATED_BY NUMBER;

Line: 836

    X_LAST_UPDATE_LOGIN NUMBER;

Line: 838

  x_last_update_date := SYSDATE;

Line: 840

    x_last_updated_by := 1;

Line: 841

    x_last_update_login := 0;

Line: 843

    X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 844

    IF x_last_updated_by IS NULL THEN
      X_LAST_UPDATED_BY := -1;

Line: 847

    X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 848

    if X_LAST_UPDATE_LOGIN is NULL then
      X_LAST_UPDATE_LOGIN := -1;

Line: 863

  p_action=>'INSERT',
  x_rowid=>X_ROWID,
  x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
  x_comments=>X_COMMENTS,
  x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
  x_expiry_dt=>TRUNC(X_EXPIRY_DT),
  x_person_id=>X_PERSON_ID,
  x_start_dt=>TRUNC(X_START_DT),
  x_spo_course_cd=>X_SPO_COURSE_CD,
  x_spo_sequence_number=>X_SPO_SEQUENCE_NUMBER,
  X_CAL_TYPE           => X_CAL_TYPE ,
  X_SEQUENCE_NUMBER    => X_SEQUENCE_NUMBER    ,
  x_auth_resp_id    => x_auth_resp_id    ,
  x_external_reference =>  x_external_reference,
  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: 885

  p_action=>'INSERT',
  x_rowid=>X_ROWID,
  x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
  x_comments=>X_COMMENTS,
  x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
  x_expiry_dt=>X_EXPIRY_DT,
  x_person_id=>X_PERSON_ID,
  x_start_dt=>X_START_DT,
  x_spo_course_cd=>X_SPO_COURSE_CD,
  x_spo_sequence_number=>X_SPO_SEQUENCE_NUMBER,
  X_CAL_TYPE           => X_CAL_TYPE ,
  X_SEQUENCE_NUMBER    => X_SEQUENCE_NUMBER    ,
  x_auth_resp_id    => x_auth_resp_id    ,
  x_external_reference =>  x_external_reference,
  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: 906

  INSERT INTO igs_pe_pers_encumb (
    person_id,
    encumbrance_type,
    start_dt,
    expiry_dt,
    authorising_person_id,
    comments,
    spo_course_cd,
    spo_sequence_number,
    cal_type     ,
    sequence_number  ,
    auth_resp_id,
    external_reference,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login
  ) VALUES (
     new_references.person_id,
     new_references.encumbrance_type,
     new_references.start_dt,
     new_references.expiry_dt,
     new_references.authorising_person_id,
     new_references.comments,
     new_references.spo_course_cd,
     new_references.spo_sequence_number,
     new_references.cal_type,
     new_references.sequence_number ,
     new_references.auth_resp_id ,
     new_references.external_reference,
    x_last_update_date,
    x_last_updated_by,
    x_last_update_date,
    x_last_updated_by,
    x_last_update_login
  );

Line: 953

  p_action => 'INSERT',
  x_rowid => X_ROWID
  );

Line: 956

END insert_row;

Line: 974

  CURSOR c1 IS SELECT
      expiry_dt,
      authorising_person_id,
      comments
    FROM igs_pe_pers_encumb
    WHERE ROWID = X_ROWID
    FOR UPDATE NOWAIT;

Line: 987

    fnd_message.set_name('FND', 'FORM_RECORD_DELETED');

Line: 1012

PROCEDURE UPDATE_ROW (
  X_ROWID in VARCHAR2,
  X_PERSON_ID in NUMBER,
  X_ENCUMBRANCE_TYPE in VARCHAR2,
  X_START_DT in DATE,
  X_EXPIRY_DT in DATE,
  X_AUTHORISING_PERSON_ID in NUMBER,
  X_COMMENTS in VARCHAR2,
  x_spo_course_cd in VARCHAR2,
  x_spo_sequence_number in NUMBER,
  X_CAL_TYPE           IN   VARCHAR2,
  X_SEQUENCE_NUMBER    IN   NUMBER,
  x_auth_resp_id IN NUMBER,
  X_EXTERNAL_REFERENCE IN VARCHAR2 ,
  X_MODE in VARCHAR2
  ) AS
    X_LAST_UPDATE_DATE DATE;

Line: 1029

    X_LAST_UPDATED_BY NUMBER;

Line: 1030

    X_LAST_UPDATE_LOGIN NUMBER;

Line: 1032

    SELECT s_encumbrance_cat
    FROM   igs_fi_encmb_type
    WHERE  encumbrance_type = cp_encumbrance_type;

Line: 1038

  X_LAST_UPDATE_DATE := SYSDATE;

Line: 1040

    X_LAST_UPDATED_BY := 1;

Line: 1041

    X_LAST_UPDATE_LOGIN := 0;

Line: 1043

    X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 1044

    if X_LAST_UPDATED_BY is NULL then
      X_LAST_UPDATED_BY := -1;

Line: 1047

    X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 1048

    if X_LAST_UPDATE_LOGIN is NULL then
      X_LAST_UPDATE_LOGIN := -1;

Line: 1063

  p_action=>'UPDATE',
  x_rowid=>X_ROWID,
  x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
  x_comments=>X_COMMENTS,
  x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
  x_expiry_dt=>TRUNC(X_EXPIRY_DT),
  x_person_id=>X_PERSON_ID,
  x_start_dt=>TRUNC(X_START_DT),
  x_spo_course_cd => x_spo_course_cd,
  x_spo_sequence_number => x_spo_sequence_number,
  X_CAL_TYPE           => X_CAL_TYPE ,
  X_SEQUENCE_NUMBER    => X_SEQUENCE_NUMBER    ,
  x_auth_resp_id    => x_auth_resp_id    ,
  X_EXTERNAL_REFERENCE => X_EXTERNAL_REFERENCE,
  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: 1085

  p_action=>'UPDATE',
  x_rowid=>X_ROWID,
  x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
  x_comments=>X_COMMENTS,
  x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
  x_expiry_dt=>X_EXPIRY_DT,
  x_person_id=>X_PERSON_ID,
  x_start_dt=>X_START_DT,
  x_spo_course_cd => x_spo_course_cd,
  x_spo_sequence_number => x_spo_sequence_number,
  X_CAL_TYPE           => X_CAL_TYPE ,
  X_SEQUENCE_NUMBER    => X_SEQUENCE_NUMBER    ,
  x_auth_resp_id    => x_auth_resp_id    ,
  X_EXTERNAL_REFERENCE => X_EXTERNAL_REFERENCE,
  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: 1106

  UPDATE IGS_PE_PERS_ENCUMB SET
    expiry_dt =  new_references.expiry_dt,
    authorising_person_id =  new_references.authorising_person_id,
    comments =  new_references.comments,
    cal_type               = new_references.cal_type ,
    sequence_number        = new_references.sequence_number    ,
    auth_resp_id        = new_references.auth_resp_id    ,
    external_reference  = new_references.external_reference,
    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: 1122

  p_action => 'UPDATE',
  x_rowid => X_ROWID
  );

Line: 1125

END UPDATE_ROW;

Line: 1144

   SELECT ROWID FROM igs_pe_pers_encumb
   WHERE person_id = x_person_id
   AND encumbrance_type = x_encumbrance_type
   AND start_dt = cp_start_dt  ;

Line: 1150

   SELECT s_encumbrance_cat
   FROM   igs_fi_encmb_type
   WHERE  encumbrance_type = cp_encumbrance_type;

Line: 1171

    INSERT_ROW (
     X_ROWID,
     X_PERSON_ID,
     X_ENCUMBRANCE_TYPE,
     X_START_DT,
     X_EXPIRY_DT,
     X_AUTHORISING_PERSON_ID,
     X_COMMENTS,
     x_spo_course_cd,
     x_spo_sequence_number,
     X_CAL_TYPE     ,
     X_SEQUENCE_NUMBER   ,
     x_auth_resp_id,
     x_external_reference,
     X_MODE);

Line: 1189

  UPDATE_ROW (
   X_ROWID,
   X_PERSON_ID,
   X_ENCUMBRANCE_TYPE,
   X_START_DT,
   X_EXPIRY_DT,
   X_AUTHORISING_PERSON_ID,
   X_COMMENTS,
   x_spo_course_cd,
   x_spo_sequence_number,
   X_CAL_TYPE     ,
   X_SEQUENCE_NUMBER   ,
   x_auth_resp_id,
   x_external_reference,
   X_MODE);

Line: 1206

procedure DELETE_ROW (
  X_ROWID IN VARCHAR2
) AS
BEGIN
 Before_DML(
  p_action => 'DELETE',
  x_rowid => X_ROWID
  );

Line: 1214

  DELETE FROM IGS_PE_PERS_ENCUMB
  WHERE ROWID = X_ROWID;

Line: 1220

  p_action => 'DELETE',
  x_rowid => X_ROWID
  );

Line: 1223

END DELETE_ROW;