eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IGS_PS_ENT_PT_REF_CD_PKG SQL Statements

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

Line: 31


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

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

Line: 49


    IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
      Close cur_old_ref_values;

Line: 51


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

Line: 73


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

Line: 80


    new_references.last_update_date := x_last_update_date;

Line: 81


    new_references.last_updated_by := x_last_updated_by;

Line: 82


    new_references.last_update_login := x_last_update_login;

Line: 92


  PROCEDURE BeforeRowInsertUpdateDelete1(
    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: 105


	IF p_inserting THEN
		IGS_PS_GEN_003.CRSP_GET_COO_KEY (
			new_references.coo_id,
			new_references.course_cd,
			new_references.version_number,
			new_references.cal_type,
			new_references.location_cd,
			new_references.attendance_mode,
			new_references.attendance_type);

Line: 116


	IF p_inserting OR p_updating THEN
		v_course_cd := new_references.course_cd;

Line: 123


	-- Validate that inserts/updates/deletes are allowed
	IF IGS_PS_VAL_CRS.CRSP_VAL_IUD_CRV_DTL(v_course_cd,
				v_version_number,
				v_message_name) = FALSE THEN
		Fnd_Message.Set_Name('IGS',v_message_name);

Line: 131


	IF p_inserting THEN
		-- Validate IGS_PS_COURSE offering option
		-- IGS_GE_NOTE:crsp_val_iud_crv_dtl called from this function
		IF IGS_PS_VAL_COp.crsp_val_coo_inactiv (
			new_references.course_cd,
			new_references.version_number,
			new_references.cal_type,
			new_references.location_cd,
			new_references.attendance_mode,
			new_references.attendance_type,
			v_message_name) = FALSE THEN
			Fnd_Message.Set_Name('IGS',v_message_name);

Line: 155


	IF p_inserting OR p_updating THEN
		-- Validate IGS_PS_UNIT sets are valid for the IGS_PS_COURSE offering option
		 IF (new_references.unit_set_cd IS NOT NULL OR
		    new_references.us_version_number IS NOT NULL) THEN
			IF IGS_PS_VAL_CEPRC.crsp_val_ceprc_coous (
				new_references.coo_id,
				new_references.unit_set_cd,
				new_references.us_version_number,
				v_message_name) = 'FALSE' THEN
				Fnd_Message.Set_Name('IGS',v_message_name);

Line: 170


	IF p_inserting THEN
		IF IGS_PS_VAL_CEPRC.crsp_val_ceprc_uniq (
			new_references.coo_id,
			new_references.reference_cd_type,
			new_references.sequence_number,
			new_references.unit_set_cd,
			new_references.us_version_number,
			v_message_name) = FALSE THEN
			Fnd_Message.Set_Name('IGS',v_message_name);

Line: 193


  END BeforeRowInsertUpdateDelete1;

Line: 310


    SELECT 'X'
    FROM   igs_ge_ref_cd_type_all
    WHERE  restricted_flag='Y'
    AND    reference_cd_type=cp_reference_cd_type;

Line: 324


    SELECT 'X'
    FROM   igs_ps_ofr_opt_unit_set_v
    WHERE  course_cd=cp_course_cd
    AND    crv_version_number=cp_crv_version_number
    AND    cal_type = cp_cal_type
    AND    location_cd = cp_location_cd
    AND    attendance_mode = cp_attendance_mode
    AND    attendance_type = cp_attendance_type
    AND    unit_set_cd = cp_unit_set_cd
    AND    us_version_number = cp_us_version_number;

Line: 361


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

Line: 374


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

Line: 387


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

Line: 403


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

Line: 420


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

Line: 439


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

Line: 462


      SELECT   rowid
      FROM     IGS_PS_ENT_PT_REF_CD
      WHERE    course_cd = x_course_cd
      AND      version_number = x_version_number
      AND      cal_type = x_cal_type
      AND      location_cd = x_location_cd
      AND      attendance_mode = x_attendance_mode
      AND      attendance_type = x_attendance_type
      AND      reference_cd_type = x_reference_cd_type
      AND      sequence_number = x_sequence_number
      FOR UPDATE NOWAIT;

Line: 500


      SELECT   rowid
      FROM     IGS_PS_ENT_PT_REF_CD
      WHERE    course_cd = x_course_cd
      AND      version_number = x_version_number
      AND      cal_type = x_cal_type
      AND      location_cd = x_location_cd
      AND      attendance_mode = x_attendance_mode
      AND      attendance_type = x_attendance_type ;

Line: 531


      SELECT   rowid
      FROM     IGS_PS_ENT_PT_REF_CD
      WHERE    coo_id = x_coo_id ;

Line: 557


      SELECT   rowid
      FROM     IGS_PS_ENT_PT_REF_CD
      WHERE    reference_cd_type = x_reference_cd_type ;

Line: 584


      SELECT   rowid
      FROM     IGS_PS_ENT_PT_REF_CD
      WHERE    unit_set_cd = x_unit_set_cd
      AND      us_version_number = x_version_number ;

Line: 624


      SELECT   rowid
      FROM     IGS_PS_ENT_PT_REF_CD
      WHERE    course_cd = x_course_cd
      AND      version_number = x_version_number
      AND      cal_type = x_cal_type
      AND      unit_set_cd = x_unit_set_cd
      AND      us_version_number = x_us_version_number ;

Line: 668


      SELECT   ROWID
      FROM     igs_ps_ref_cd
      WHERE    reference_cd_type = x_reference_cd_type
      AND      reference_cd = x_reference_cd ;

Line: 708


    x_last_update_date IN DATE DEFAULT NULL,
    x_last_updated_by IN NUMBER DEFAULT NULL,
    x_last_update_login IN NUMBER DEFAULT NULL
  ) AS
  BEGIN

    Set_Column_Values (
      p_action,
      x_rowid,
      x_course_cd,
      x_version_number,
      x_cal_type,
      x_location_cd,
      x_attendance_mode,
      x_attendance_type,
      x_reference_cd_type,
      x_sequence_number,
      x_coo_id,
      x_unit_set_cd,
      x_us_version_number,
      x_reference_cd,
      x_description,
      x_creation_date,
      x_created_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login
    );

Line: 737


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

Line: 755


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

Line: 760


    ELSIF (p_action = 'DELETE') THEN
      -- Call all the procedures related to Before Delete.
      BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );

Line: 763


    ELSIF (p_action = 'VALIDATE_INSERT') THEN
	IF  Get_PK_For_Validation (
	new_references.course_cd ,
      new_references.version_number,
      new_references.cal_type,
      new_references.location_cd,
      new_references.attendance_mode,
      new_references.attendance_type,
      new_references.reference_cd_type,
      new_references.sequence_number) THEN
	    Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');

Line: 778


    ELSIF (p_action = 'VALIDATE_UPDATE') THEN
	Check_Constraints;

Line: 795


procedure INSERT_ROW (
  X_ROWID in out NOCOPY VARCHAR2,
  X_COURSE_CD in VARCHAR2,
  X_SEQUENCE_NUMBER in NUMBER,
  X_REFERENCE_CD_TYPE in VARCHAR2,
  X_ATTENDANCE_TYPE in VARCHAR2,
  X_CAL_TYPE in VARCHAR2,
  X_LOCATION_CD in VARCHAR2,
  X_VERSION_NUMBER in NUMBER,
  X_ATTENDANCE_MODE in VARCHAR2,
  X_COO_ID in NUMBER,
  X_UNIT_SET_CD in VARCHAR2,
  X_US_VERSION_NUMBER in NUMBER,
  X_REFERENCE_CD in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_MODE in VARCHAR2 default 'R'
  ) AS
    cursor C is select ROWID from IGS_PS_ENT_PT_REF_CD
      where COURSE_CD = X_COURSE_CD
      and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
      and REFERENCE_CD_TYPE = X_REFERENCE_CD_TYPE
      and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
      and CAL_TYPE = X_CAL_TYPE
      and LOCATION_CD = X_LOCATION_CD
      and VERSION_NUMBER = X_VERSION_NUMBER
      and ATTENDANCE_MODE = X_ATTENDANCE_MODE;

Line: 821


    X_LAST_UPDATE_DATE DATE;

Line: 822


    X_LAST_UPDATED_BY NUMBER;

Line: 823


    X_LAST_UPDATE_LOGIN NUMBER;

Line: 825


  X_LAST_UPDATE_DATE := SYSDATE;

Line: 827


    X_LAST_UPDATED_BY := 1;

Line: 828


    X_LAST_UPDATE_LOGIN := 0;

Line: 830


    X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 831


    if X_LAST_UPDATED_BY is NULL then
      X_LAST_UPDATED_BY := -1;

Line: 834


    X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 835


    if X_LAST_UPDATE_LOGIN is NULL then
      X_LAST_UPDATE_LOGIN := -1;

Line: 845


    p_action => 'INSERT',
    x_rowid => X_ROWID,
    x_course_cd => X_COURSE_CD,
    x_version_number => X_VERSION_NUMBER,
    x_cal_type => X_CAL_TYPE,
    x_location_cd => X_LOCATION_CD,
    x_attendance_mode => X_ATTENDANCE_MODE,
    x_attendance_type => X_ATTENDANCE_TYPE,
    x_reference_cd_type => X_REFERENCE_CD_TYPE,
    x_sequence_number => X_SEQUENCE_NUMBER,
    x_coo_id => X_COO_ID ,
    x_unit_set_cd => X_UNIT_SET_CD ,
    x_us_version_number => X_US_VERSION_NUMBER ,
    x_reference_cd => X_REFERENCE_CD ,
    x_description => X_DESCRIPTION ,
    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: 867


  insert into IGS_PS_ENT_PT_REF_CD (
    COURSE_CD,
    VERSION_NUMBER,
    CAL_TYPE,
    LOCATION_CD,
    ATTENDANCE_MODE,
    ATTENDANCE_TYPE,
    COO_ID,
    REFERENCE_CD_TYPE,
    SEQUENCE_NUMBER,
    UNIT_SET_CD,
    US_VERSION_NUMBER,
    REFERENCE_CD,
    DESCRIPTION,
    CREATION_DATE,
    CREATED_BY,
    LAST_UPDATE_DATE,
    LAST_UPDATED_BY,
    LAST_UPDATE_LOGIN
  ) values (
    NEW_REFERENCES.COURSE_CD,
    NEW_REFERENCES.VERSION_NUMBER,
    NEW_REFERENCES.CAL_TYPE,
    NEW_REFERENCES.LOCATION_CD,
    NEW_REFERENCES.ATTENDANCE_MODE,
    NEW_REFERENCES.ATTENDANCE_TYPE,
    NEW_REFERENCES.COO_ID,
    NEW_REFERENCES.REFERENCE_CD_TYPE,
    NEW_REFERENCES.SEQUENCE_NUMBER,
    NEW_REFERENCES.UNIT_SET_CD,
    NEW_REFERENCES.US_VERSION_NUMBER,
    NEW_REFERENCES.REFERENCE_CD,
    NEW_REFERENCES.DESCRIPTION,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_DATE,
    X_LAST_UPDATED_BY,
    X_LAST_UPDATE_LOGIN
  );

Line: 915


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

Line: 919


end INSERT_ROW;

Line: 937


  cursor c1 is select
      COO_ID,
--added by sarakshi, bug#2146753
      course_cd,
      version_number,
      cal_type,
      location_cd,
      attendance_mode,
      attendance_type,
      reference_cd_type,
      sequence_number,
--
      UNIT_SET_CD,
      US_VERSION_NUMBER,
      REFERENCE_CD,
      DESCRIPTION
    from IGS_PS_ENT_PT_REF_CD
    where ROWID = X_ROWID
    for update nowait;

Line: 963


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

Line: 1001


procedure UPDATE_ROW (
  X_ROWID IN VARCHAR2,
  X_COURSE_CD in VARCHAR2,
  X_SEQUENCE_NUMBER in NUMBER,
  X_REFERENCE_CD_TYPE in VARCHAR2,
  X_ATTENDANCE_TYPE in VARCHAR2,
  X_CAL_TYPE in VARCHAR2,
  X_LOCATION_CD in VARCHAR2,
  X_VERSION_NUMBER in NUMBER,
  X_ATTENDANCE_MODE in VARCHAR2,
  X_COO_ID in NUMBER,
  X_UNIT_SET_CD in VARCHAR2,
  X_US_VERSION_NUMBER in NUMBER,
  X_REFERENCE_CD in VARCHAR2,
  X_DESCRIPTION in VARCHAR2,
  X_MODE in VARCHAR2 default 'R'
  ) AS
    X_LAST_UPDATE_DATE DATE;

Line: 1019


    X_LAST_UPDATED_BY NUMBER;

Line: 1020


    X_LAST_UPDATE_LOGIN NUMBER;

Line: 1022


  X_LAST_UPDATE_DATE := SYSDATE;

Line: 1024


    X_LAST_UPDATED_BY := 1;

Line: 1025


    X_LAST_UPDATE_LOGIN := 0;

Line: 1027


    X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 1028


    if X_LAST_UPDATED_BY is NULL then
      X_LAST_UPDATED_BY := -1;

Line: 1031


    X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 1032


    if X_LAST_UPDATE_LOGIN is NULL then
      X_LAST_UPDATE_LOGIN := -1;

Line: 1042


    p_action => 'UPDATE',
    x_rowid => X_ROWID,
    x_course_cd => X_COURSE_CD,
    x_version_number => X_VERSION_NUMBER,
    x_cal_type => X_CAL_TYPE,
    x_location_cd => X_LOCATION_CD,
    x_attendance_mode => X_ATTENDANCE_MODE,
    x_attendance_type => X_ATTENDANCE_TYPE,
    x_reference_cd_type => X_REFERENCE_CD_TYPE,
    x_sequence_number => X_SEQUENCE_NUMBER,
    x_coo_id => X_COO_ID ,
    x_unit_set_cd => X_UNIT_SET_CD ,
    x_us_version_number => X_US_VERSION_NUMBER ,
    x_reference_cd => X_REFERENCE_CD ,
    x_description => X_DESCRIPTION ,
    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: 1064


  update IGS_PS_ENT_PT_REF_CD set
--added by sarakshi, bug#2146753
    course_cd=new_references.course_cd,
    version_number=new_references.version_number,
    cal_type=new_references.cal_type,
    location_cd=new_references.location_cd,
    attendance_mode=new_references.attendance_mode,
    attendance_type=new_references.attendance_type,
    reference_cd_type=new_references.reference_cd_type,
    sequence_number=new_references.sequence_number,
--
    COO_ID = NEW_REFERENCES.COO_ID,
    UNIT_SET_CD = NEW_REFERENCES.UNIT_SET_CD,
    US_VERSION_NUMBER = NEW_REFERENCES.US_VERSION_NUMBER,
    REFERENCE_CD = NEW_REFERENCES.REFERENCE_CD,
    DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
    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: 1090


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

Line: 1094


end UPDATE_ROW;

Line: 1113


  cursor c1 is select rowid from IGS_PS_ENT_PT_REF_CD
     where COURSE_CD = X_COURSE_CD
     and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
     and REFERENCE_CD_TYPE = X_REFERENCE_CD_TYPE
     and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
     and CAL_TYPE = X_CAL_TYPE
     and LOCATION_CD = X_LOCATION_CD
     and VERSION_NUMBER = X_VERSION_NUMBER
     and ATTENDANCE_MODE = X_ATTENDANCE_MODE
  ;

Line: 1128


    INSERT_ROW (
     X_ROWID,
     X_COURSE_CD,
     X_SEQUENCE_NUMBER,
     X_REFERENCE_CD_TYPE,
     X_ATTENDANCE_TYPE,
     X_CAL_TYPE,
     X_LOCATION_CD,
     X_VERSION_NUMBER,
     X_ATTENDANCE_MODE,
     X_COO_ID,
     X_UNIT_SET_CD,
     X_US_VERSION_NUMBER,
     X_REFERENCE_CD,
     X_DESCRIPTION,
     X_MODE);

Line: 1147


  UPDATE_ROW (
   X_ROWID,
   X_COURSE_CD,
   X_SEQUENCE_NUMBER,
   X_REFERENCE_CD_TYPE,
   X_ATTENDANCE_TYPE,
   X_CAL_TYPE,
   X_LOCATION_CD,
   X_VERSION_NUMBER,
   X_ATTENDANCE_MODE,
   X_COO_ID,
   X_UNIT_SET_CD,
   X_US_VERSION_NUMBER,
   X_REFERENCE_CD,
   X_DESCRIPTION,
   X_MODE);

Line: 1165


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

Line: 1173


  delete from IGS_PS_ENT_PT_REF_CD
  where ROWID = X_ROWID;

Line: 1179


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

Line: 1182


end DELETE_ROW;