DBA Data[Home] [Help]

APPS.IGS_AS_SU_ATMPT_PAT_PKG SQL Statements

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

Line: 27

    x_logical_delete_dt IN DATE DEFAULT NULL,
    x_creation_date IN DATE DEFAULT NULL,
    x_created_by IN NUMBER DEFAULT NULL,
    x_last_update_date IN DATE DEFAULT NULL,
    x_last_updated_by IN NUMBER DEFAULT NULL,
    x_last_update_login IN NUMBER DEFAULT NULL,
    x_uoo_id IN NUMBER DEFAULT NULL
  ) AS
    CURSOR cur_old_ref_values IS
      SELECT   *
      FROM     IGS_AS_SU_ATMPT_PAT
      WHERE    rowid = x_rowid;
Line: 45

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

    new_references.logical_delete_dt := x_logical_delete_dt;
Line: 65

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

    new_references.last_update_date := x_last_update_date;
Line: 73

    new_references.last_updated_by := x_last_updated_by;
Line: 74

    new_references.last_update_login := x_last_update_login;
Line: 84

  PROCEDURE BeforeRowInsertUpdate1(
    p_inserting IN BOOLEAN DEFAULT FALSE,
    p_updating IN BOOLEAN DEFAULT FALSE,
    p_deleting IN BOOLEAN DEFAULT FALSE
    ) AS
	v_message_name		VARCHAR2(30);
Line: 96

		SELECT	version_number
		FROM	IGS_EN_SU_ATTEMPT
		WHERE person_id		= cp_person_id	AND
			  course_cd		= cp_course_cd	AND
			  uoo_id        = cp_uoo_id;
Line: 102

	-- If p_inserting, validate that the assessment pattern is applicable to the
	-- student IGS_PS_UNIT attempt and that the IGS_PS_UNIT attempt status is ENROLLED or
	-- UNCONFIRMED.
	IF p_inserting THEN
		IF IGS_AS_VAL_SUAAP.assp_val_suaap_ins(new_references.person_id,
						new_references.course_cd,
						new_references.unit_cd,
						new_references.cal_type,
						new_references.ci_sequence_number,
						new_references.ass_pattern_id,
						v_message_name,
                        new_references.uoo_id) = FALSE THEN
						FND_MESSAGE.SET_NAME('IGS',v_message_name);
Line: 129

		-- and the logical delete date is not set.
		IF IGS_AS_VAL_SUAAP.GENP_VAL_SDTT_SESS('IGS_AS_SU_ATMPT_PAT') AND
		    (NVL(new_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
		     =  NVL(old_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
			-- Allocate unit_ass_pattern_items within the pattern to the student
			-- (IGS_AS_SU_ATMPT_ITM).
			IF IGS_AS_GEN_004.ASSP_INS_SUAAP_SUAAI(new_references.person_id,
					new_references.course_cd,
					new_references.unit_cd,
					v_version_number,
					new_references.cal_type,
					new_references.ci_sequence_number,
					new_references.ass_pattern_id,
					new_references.creation_dt,
					new_references.s_default_ind,
                    'Y', -- Called from database trigger.
					v_message_name,
                    new_references.uoo_id) = FALSE THEN
				FND_MESSAGE.SET_NAME('IGS',v_message_name); APP_EXCEPTION.RAISE_EXCEPTION;
Line: 152

  END BeforeRowInsertUpdate1;
Line: 163

  PROCEDURE AfterRowInsertUpdate2(
    p_inserting IN BOOLEAN DEFAULT FALSE,
    p_updating IN BOOLEAN DEFAULT FALSE,
    p_deleting IN BOOLEAN DEFAULT FALSE
    ) AS
     v_message_name	VARCHAR2(30);
Line: 172

  	IF p_inserting  THEN
               IF IGS_AS_VAL_SUAAP.assp_val_suaap_actv(	new_references.person_id,
  						new_references.course_cd,
  						new_references.unit_cd,
  						new_references.cal_type,
  						new_references.ci_sequence_number,
  						new_references.ass_pattern_id,
  						new_references.creation_dt,
  						v_message_name,
                        new_references.uoo_id) = FALSE THEN
  			FND_MESSAGE.SET_NAME('IGS',v_message_name);
Line: 191

  	   (NVL(new_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
  		<>  NVL(old_references.logical_delete_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
  		-- If logically p_deleting the suaap record.
  		-- Check if IGS_AS_GEN_001.ASSP_DEL_SUAAP_DFLT has not disabled the trigger.
  		IF IGS_AS_VAL_SUAAP.GENP_VAL_SDTT_SESS('IGS_AS_SU_ATMPT_PAT') THEN
           IF IGS_AS_GEN_001.ASSP_DEL_SUAAP_SUAAI(	new_references.person_id,
  						new_references.course_cd,
  						new_references.unit_cd,
  						new_references.cal_type,
  						new_references.ci_sequence_number,
  						new_references.ass_pattern_id,
  						new_references.creation_dt,
                        NULL,	-- p_ass_id
  					'Y', -- Called from database trigger.
  					NULL,	-- p_s_log_type
  					NULL,	-- p_key
  					NULL,	-- p_ssl_key
  					v_error_count,
  					v_warning_count,
  					v_message_name,
                    new_references.uoo_id) = FALSE THEN
  				FND_MESSAGE.SET_NAME('IGS',v_message_name);
Line: 216

  			-- Logically delete unit_ass_pattern_items within the pattern to the student
  			-- (IGS_AS_SU_ATMPT_ITM).
  			-- Store away the rowid as the routine IGS_AS_GEN_001.ASSP_DEL_SUAAP_SUAAI will cause the
  			-- trigger to be mutating.
  		--	IGS_AS_VAL_SUAAP.genp_set_rowid(l_rowid);
Line: 223

  END AfterRowInsertUpdate2;
Line: 242

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

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

      SELECT   rowid
      FROM     IGS_AS_SU_ATMPT_PAT
      WHERE    course_cd = x_course_cd
      AND      person_id = x_person_id
      AND      ass_pattern_id = x_ass_pattern_id
      AND      creation_dt = x_creation_dt
      AND      uoo_id = x_uoo_id
      FOR UPDATE NOWAIT;
Line: 298

      SELECT   rowid
      FROM     IGS_AS_SU_ATMPT_PAT
      WHERE    person_id = x_person_id
      AND      course_cd = x_course_cd
      AND      uoo_id = x_uoo_id;
Line: 321

      SELECT   rowid
      FROM     IGS_AS_SU_ATMPT_PAT
      WHERE    ass_pattern_id = x_ass_pattern_id ;
Line: 349

    x_logical_delete_dt IN DATE DEFAULT NULL,
    x_creation_date IN DATE DEFAULT NULL,
    x_created_by IN NUMBER DEFAULT NULL,
    x_last_update_date IN DATE DEFAULT NULL,
    x_last_updated_by IN NUMBER DEFAULT NULL,
    x_last_update_login IN NUMBER DEFAULT NULL,
    x_uoo_id IN NUMBER DEFAULT NULL
  ) AS
  BEGIN
    Set_Column_Values (
      p_action,
      x_rowid,
      x_person_id,
      x_course_cd,
      x_unit_cd,
      x_cal_type,
      x_ci_sequence_number,
      x_ass_pattern_id,
      x_creation_dt,
      x_s_default_ind,
      x_logical_delete_dt,
      x_creation_date,
      x_created_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login,
      x_uoo_id
    );
Line: 377

    IF (p_action = 'INSERT') THEN
      -- Call all the procedures related to Before Insert.
      BeforeRowInsertUpdate1 ( p_inserting => TRUE );
Line: 395

    ELSIF (p_action = 'UPDATE') THEN
      -- Call all the procedures related to Before Update.
      BeforeRowInsertUpdate1 ( p_updating => TRUE );
Line: 402

	ELSIF (p_action = 'VALIDATE_INSERT') THEN
	     IF  Get_PK_For_Validation (

    new_references.course_cd ,
    new_references.person_id ,
    new_references.ass_pattern_id ,
    new_references.creation_dt,
    new_references.uoo_id) THEN
         Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
Line: 416

	ELSIF (p_action = 'VALIDATE_UPDATE') THEN

	      Check_Constraints;
Line: 425

procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_PERSON_ID in NUMBER,
  X_COURSE_CD in VARCHAR2,
  X_UNIT_CD in VARCHAR2,
  X_CAL_TYPE in VARCHAR2,
  X_CI_SEQUENCE_NUMBER in NUMBER,
  X_ASS_PATTERN_ID in NUMBER,
  X_CREATION_DT in DATE,
  X_S_DEFAULT_IND in VARCHAR2,
  X_LOGICAL_DELETE_DT in DATE,
  X_MODE in VARCHAR2 default 'R',
  X_UOO_ID in NUMBER
  ) AS
    cursor C is select ROWID from IGS_AS_SU_ATMPT_PAT
      where PERSON_ID = X_PERSON_ID
      and COURSE_CD = X_COURSE_CD
      and ASS_PATTERN_ID = X_ASS_PATTERN_ID
      and CREATION_DT = X_CREATION_DT
      and UOO_ID = X_UOO_ID;
Line: 445

    X_LAST_UPDATE_DATE DATE;
Line: 446

    X_LAST_UPDATED_BY NUMBER;
Line: 447

    X_LAST_UPDATE_LOGIN NUMBER;
Line: 451

    X_PROGRAM_UPDATE_DATE DATE;
Line: 453

  X_LAST_UPDATE_DATE := SYSDATE;
Line: 455

    X_LAST_UPDATED_BY := 1;
Line: 456

    X_LAST_UPDATE_LOGIN := 0;
Line: 458

    X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 459

    if X_LAST_UPDATED_BY is NULL then
      X_LAST_UPDATED_BY := -1;
Line: 462

    X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
Line: 463

    if X_LAST_UPDATE_LOGIN is NULL then
      X_LAST_UPDATE_LOGIN := -1;
Line: 473

     X_PROGRAM_UPDATE_DATE := NULL;
Line: 475

     X_PROGRAM_UPDATE_DATE := SYSDATE;
Line: 483

  p_action=>'INSERT',
  x_rowid=>X_ROWID,
  x_ass_pattern_id=>X_ASS_PATTERN_ID,
  x_cal_type=>X_CAL_TYPE,
  x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
  x_course_cd=>X_COURSE_CD,
  x_creation_dt=>X_CREATION_DT,
  x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
  x_person_id=>X_PERSON_ID,
  x_s_default_ind=> NVL(X_S_DEFAULT_IND,'N'),
  x_unit_cd=>X_UNIT_CD,
  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_uoo_id=>X_UOO_ID
  );
Line: 501

  insert into IGS_AS_SU_ATMPT_PAT (
    PERSON_ID,
    COURSE_CD,
    UNIT_CD,
    CAL_TYPE,
    CI_SEQUENCE_NUMBER,
    ASS_PATTERN_ID,
    CREATION_DT,
    S_DEFAULT_IND,
    LOGICAL_DELETE_DT,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN,
    REQUEST_ID,
    PROGRAM_ID,
    PROGRAM_APPLICATION_ID,
    PROGRAM_UPDATE_DATE,
    UOO_ID
  ) values (
    NEW_REFERENCES.PERSON_ID,
    NEW_REFERENCES.COURSE_CD,
    NEW_REFERENCES.UNIT_CD,
    NEW_REFERENCES.CAL_TYPE,
    NEW_REFERENCES.CI_SEQUENCE_NUMBER,
    NEW_REFERENCES.ASS_PATTERN_ID,
    NEW_REFERENCES.CREATION_DT,
    NEW_REFERENCES.S_DEFAULT_IND,
    NEW_REFERENCES.LOGICAL_DELETE_DT,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN,
    X_REQUEST_ID,
    X_PROGRAM_ID,
    X_PROGRAM_APPLICATION_ID,
    X_PROGRAM_UPDATE_DATE,
    NEW_REFERENCES.UOO_ID
  );
Line: 550

end INSERT_ROW;
Line: 561

  X_LOGICAL_DELETE_DT in DATE,
  X_UOO_ID in NUMBER
) AS
  cursor c1 is select
      S_DEFAULT_IND,
      LOGICAL_DELETE_DT
    from IGS_AS_SU_ATMPT_PAT
    where ROWID = X_ROWID  for update  nowait;
Line: 574

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

      AND ((tlinfo.LOGICAL_DELETE_DT = X_LOGICAL_DELETE_DT)
           OR ((tlinfo.LOGICAL_DELETE_DT is null)
               AND (X_LOGICAL_DELETE_DT is null)))
  ) then
    null;
Line: 594

procedure UPDATE_ROW (
  X_ROWID in  VARCHAR2,
  X_PERSON_ID in NUMBER,
  X_COURSE_CD in VARCHAR2,
  X_UNIT_CD in VARCHAR2,
  X_CAL_TYPE in VARCHAR2,
  X_CI_SEQUENCE_NUMBER in NUMBER,
  X_ASS_PATTERN_ID in NUMBER,
  X_CREATION_DT in DATE,
  X_S_DEFAULT_IND in VARCHAR2,
  X_LOGICAL_DELETE_DT in DATE,
  X_MODE in VARCHAR2 default 'R',
  X_UOO_ID in NUMBER
  ) AS
    X_LAST_UPDATE_DATE DATE;
Line: 609

    X_LAST_UPDATED_BY NUMBER;
Line: 610

    X_LAST_UPDATE_LOGIN NUMBER;
Line: 614

    X_PROGRAM_UPDATE_DATE DATE;
Line: 616

  X_LAST_UPDATE_DATE := SYSDATE;
Line: 618

    X_LAST_UPDATED_BY := 1;
Line: 619

    X_LAST_UPDATE_LOGIN := 0;
Line: 621

    X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
Line: 622

    if X_LAST_UPDATED_BY is NULL then
      X_LAST_UPDATED_BY := -1;
Line: 625

    X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
Line: 626

    if X_LAST_UPDATE_LOGIN is NULL then
      X_LAST_UPDATE_LOGIN := -1;
Line: 635

  p_action=>'UPDATE',
  x_rowid=>X_ROWID,
  x_ass_pattern_id=>X_ASS_PATTERN_ID,
  x_cal_type=>X_CAL_TYPE,
  x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
  x_course_cd=>X_COURSE_CD,
  x_creation_dt=>X_CREATION_DT,
  x_logical_delete_dt=>X_LOGICAL_DELETE_DT,
  x_person_id=>X_PERSON_ID,
  x_s_default_ind=>X_S_DEFAULT_IND,
  x_unit_cd=>X_UNIT_CD,
  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_uoo_id=>X_UOO_ID
  );
Line: 661

     X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
Line: 663

     X_PROGRAM_UPDATE_DATE := SYSDATE;
Line: 666

  update IGS_AS_SU_ATMPT_PAT set
    S_DEFAULT_IND = NEW_REFERENCES.S_DEFAULT_IND,
    LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
    LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
    LAST_UPDATED_BY = X_LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
    REQUEST_ID = X_REQUEST_ID,
    PROGRAM_ID = X_PROGRAM_ID,
    PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
    PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
  where ROWID = X_ROWID;
Line: 681

end UPDATE_ROW;
Line: 692

  X_LOGICAL_DELETE_DT in DATE,
  X_MODE in VARCHAR2 default 'R',
  X_UOO_ID in NUMBER
  ) AS
  cursor c1 is select rowid from IGS_AS_SU_ATMPT_PAT
     where PERSON_ID = X_PERSON_ID
     and COURSE_CD = X_COURSE_CD
     and ASS_PATTERN_ID = X_ASS_PATTERN_ID
     and CREATION_DT = X_CREATION_DT
     and UOO_ID = X_UOO_ID
  ;
Line: 708

    INSERT_ROW (
     X_ROWID,
     X_PERSON_ID,
     X_COURSE_CD,
     X_UNIT_CD,
     X_CAL_TYPE,
     X_CI_SEQUENCE_NUMBER,
     X_ASS_PATTERN_ID,
     X_CREATION_DT,
     X_S_DEFAULT_IND,
     X_LOGICAL_DELETE_DT,
     X_MODE,
     X_UOO_ID);
Line: 724

  UPDATE_ROW (
   X_ROWID,
   X_PERSON_ID,
   X_COURSE_CD,
   X_UNIT_CD,
   X_CAL_TYPE,
   X_CI_SEQUENCE_NUMBER,
   X_ASS_PATTERN_ID,
   X_CREATION_DT,
   X_S_DEFAULT_IND,
   X_LOGICAL_DELETE_DT,
   X_MODE,
   X_UOO_ID);
Line: 738

procedure DELETE_ROW (
  X_ROWID in VARCHAR2) AS
begin
 Before_DML(
  p_action => 'DELETE',
  x_rowid => X_ROWID
  );
Line: 745

  delete from IGS_AS_SU_ATMPT_PAT
 where ROWID = X_ROWID;
Line: 751

end DELETE_ROW;