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_PE_PRIV_LEVEL_PKG SQL Statements

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

Line: 32


    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


/******************************************************
Created By : nigupta
Date Created By : 11-MAY-2000
Purpose : To Set Column Values
Know limitations, enhancements or remarks : None
Change History
Who		When		What


(reverse chronological order - newest change first)
********************************************************/

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

Line: 63


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

Line: 65


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

Line: 82


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

Line: 89


    new_references.last_update_date := x_last_update_date;

Line: 90


    new_references.last_updated_by := x_last_updated_by;

Line: 91


    new_references.last_update_login := x_last_update_login;

Line: 137


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

Line: 148


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

Line: 159


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

Line: 169


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

Line: 193


      SELECT   rowid
      FROM     igs_pe_priv_level
      WHERE    privacy_level_id = x_privacy_level_id
      FOR UPDATE NOWAIT;

Line: 230


      SELECT   rowid
      FROM     igs_pe_priv_level
      WHERE    person_id = x_person_id ;

Line: 251


  PROCEDURE BeforeDeletePrivLevel
  IS
    CURSOR ref_notes IS
    SELECT rowid
    FROM   igs_ge_note
    WHERE  reference_number = old_references.ref_notes_id;

Line: 259


      igs_ge_note_pkg.delete_row (ref_rec.rowid);

Line: 262


  END BeforeDeletePrivLevel;

Line: 264


  PROCEDURE beforeinsertupdate(p_inserting BOOLEAN , p_updating BOOLEAN) IS
    p_message_name VARCHAR2(30);

Line: 267


    IF ( p_inserting = TRUE OR ( p_updating = TRUE AND new_references.data_group_id <> old_references.data_group_id ) ) THEN
        IF NOT igs_pe_data_groups_pkg.val_data_group(new_references.data_group_id , p_message_name) THEN
           Fnd_Message.Set_Name ('IGS',p_message_name);

Line: 275


    IF p_inserting OR p_updating THEN
      IF(new_references.start_date IS NOT NULL) THEN

          /* kumma, 2902713, Modified the following if condition so that start date should not get compare with the sysdate if user has not changed the start date */

        --IF(trunc(new_references.start_date) <> trunc(sysdate) aND new_references.start_date < sysdate) THEN
        IF(trunc(new_references.start_date) < trunc(sysdate) AND new_references.start_date <> nvl((old_references.start_date),trunc(sysdate)))  THEN
            Fnd_Message.Set_Name('IGS','IGS_FI_ST_NOT_LT_CURRDT');

Line: 313


    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

/******************************************************
Created By : nigupta
Date Created By : 11-MAY-2000
Purpose : To check before DML
Know limitations, enhancements or remarks : None
Change History
Who		When		What


(reverse chronological order - newest change first)
********************************************************/
  BEGIN

    Set_Column_Values (
      p_action,
      x_rowid,
      x_privacy_level_id,
      x_person_id,
      x_data_group,
      x_data_group_id,
      x_action,
      x_whom,
      x_ref_notes_id,
      x_start_date,
      x_end_date,
      x_creation_date,
      x_created_by,
      x_last_update_date,
      x_last_updated_by,
      x_last_update_login
    );

Line: 349


     IF (p_action = 'INSERT') THEN
      -- Call all the procedures related to Before Insert.
      beforeinsertupdate(TRUE,FALSE);

Line: 360


    ELSIF (p_action = 'UPDATE') THEN
      -- Call all the procedures related to Before Update.
      beforeinsertupdate(FALSE,TRUE);

Line: 365


    ELSIF (p_action = 'DELETE') THEN
       BeforeDeletePrivLevel;

Line: 369


    ELSIF (p_action = 'VALIDATE_INSERT') THEN
	 -- Call all the procedures related to Before Insert.
      IF Get_PK_For_Validation (
    		new_references.privacy_level_id)  THEN
	       Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');

Line: 378


    ELSIF (p_action = 'VALIDATE_UPDATE') THEN
      Check_Constraints;

Line: 380


    ELSIF (p_action = 'VALIDATE_DELETE') THEN
      Null;

Line: 387


 PROCEDURE afterrowinsertupdate(
    p_inserting IN BOOLEAN,
    p_updating IN BOOLEAN,
    p_deleting IN BOOLEAN
    ) AS
  ------------------------------------------------------------------------------------------
  --Created by  : kumma
  --Date created: 23-APR-2003
  --
  --Purpose:Bug 2902713. Moved the overlap validation from library
  --
  --Known limitations/enhancements and/or remarks:
  --
  --Change History:
  --Who         When            What
  ----------------------------------------------------------------------------------------------
  CURSOR c_priv_overlap(cp_person_id igs_pe_visa.person_id%TYPE, cp_data_group_id igs_pe_priv_level.data_group_id%TYPE) IS
  SELECT count(1)
  FROM
     igs_pe_priv_level p1,
     igs_pe_priv_level p2
  WHERE
     p1.person_id = cp_person_id and
     p1.person_id = p2.person_id and
     p1.data_group_id = cp_data_group_id and
     p1.data_group_id = p2.data_group_id and
     NVL(p1.end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) >= p2.start_date and
     NVL(p1.end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) <= NVL(p2.end_date,TO_DATE('4712/12/31','YYYY/MM/DD')) and
     p1.rowid <> p2.rowid;

Line: 428


 END afterrowinsertupdate;

Line: 453


    IF (p_action = 'INSERT') THEN
      -- Call all the procedures related to After Insert.
      AfterRowInsertUpdate (
          p_inserting => TRUE,
          p_updating  => FALSE,
          p_deleting  => FALSE
         );

Line: 461


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

Line: 468


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

Line: 475


 procedure INSERT_ROW (
      X_ROWID in out NOCOPY VARCHAR2,
       x_PRIVACY_LEVEL_ID IN OUT NOCOPY NUMBER,
       x_PERSON_ID IN NUMBER,
       x_DATA_GROUP IN VARCHAR2,
       x_DATA_GROUP_ID IN NUMBER,
       x_LVL IN NUMBER,
       x_ACTION IN VARCHAR2,
       x_WHOM IN VARCHAR2,
       x_REF_NOTES_ID IN NUMBER,
       x_START_DATE IN DATE,
       x_END_DATE IN DATE,
      X_MODE in VARCHAR2 default 'R'
  ) AS

/******************************************************
Created By : nigupta
Date Created By : 11-MAY-2000
Purpose : To insert row
Know limitations, enhancements or remarks : None
Change History
Who		When		What
kumma           03-JUN-2002     Passes NULL for LVL, bug # 2377971

(reverse chronological order - newest change first)
********************************************************/

    cursor C is select ROWID from IGS_PE_PRIV_LEVEL
             where                 PRIVACY_LEVEL_ID= X_PRIVACY_LEVEL_ID
;

Line: 505


     X_LAST_UPDATE_DATE DATE ;

Line: 506


     X_LAST_UPDATED_BY NUMBER ;

Line: 507


     X_LAST_UPDATE_LOGIN NUMBER ;

Line: 509


     X_LAST_UPDATE_DATE := SYSDATE;

Line: 511


        X_LAST_UPDATED_BY := 1;

Line: 512


        X_LAST_UPDATE_LOGIN := 0;

Line: 514


               X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 515


            if X_LAST_UPDATED_BY is NULL then
                X_LAST_UPDATED_BY := -1;

Line: 518


            X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 519


         if X_LAST_UPDATE_LOGIN is NULL then
            X_LAST_UPDATE_LOGIN := -1;

Line: 528


Select IGS_PE_PRIV_LEVEL_S.NEXTVAL into X_PRIVACY_LEVEL_ID from Dual;

Line: 531


 	       p_action=>'INSERT',
 	       x_rowid=>X_ROWID,
 	       x_privacy_level_id=>X_PRIVACY_LEVEL_ID,
 	       x_person_id=>X_PERSON_ID,
 	       x_data_group=>X_DATA_GROUP,
 	       x_data_group_id=>X_DATA_GROUP_ID,
 	       x_action=>X_ACTION,
 	       x_whom=>X_WHOM,
  	       x_ref_notes_id=>X_REF_NOTES_ID,
	       x_start_date=>X_START_DATE,
 	       x_end_date=>X_END_DATE,
	       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: 550


 insert into IGS_PE_PRIV_LEVEL (
		PRIVACY_LEVEL_ID
		,PERSON_ID
		,DATA_GROUP
		,DATA_GROUP_ID
		,LVL
		,ACTION
		,WHOM
		,REF_NOTES_ID
		,START_DATE
		,END_DATE
	        ,CREATION_DATE
		,CREATED_BY
		,LAST_UPDATE_DATE
		,LAST_UPDATED_BY
		,LAST_UPDATE_LOGIN
        ) values  (
	        NEW_REFERENCES.PRIVACY_LEVEL_ID
	        ,NEW_REFERENCES.PERSON_ID
	        ,NEW_REFERENCES.DATA_GROUP
	        ,NEW_REFERENCES.DATA_GROUP_ID
	        ,NULL
	        ,NEW_REFERENCES.ACTION
	        ,NEW_REFERENCES.WHOM
	        ,NEW_REFERENCES.REF_NOTES_ID
	        ,NEW_REFERENCES.START_DATE
	        ,NEW_REFERENCES.END_DATE
	        ,X_LAST_UPDATE_DATE
		,X_LAST_UPDATED_BY
		,X_LAST_UPDATE_DATE
		,X_LAST_UPDATED_BY
		,X_LAST_UPDATE_LOGIN
);

Line: 595


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

Line: 610


end INSERT_ROW;

Line: 633


                                and also modified the cursor query to not to select LVL

(reverse chronological order - newest change first)
********************************************************/

   cursor c1 is select
      PERSON_ID
,      DATA_GROUP
,      DATA_GROUP_ID
,      ACTION
,      WHOM
,      REF_NOTES_ID
,      START_DATE
,      END_DATE
    from IGS_PE_PRIV_LEVEL
    where ROWID = X_ROWID
    for update nowait;

Line: 655


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

Line: 684


 Procedure UPDATE_ROW (
      X_ROWID in  VARCHAR2,
       x_PRIVACY_LEVEL_ID IN NUMBER,
       x_PERSON_ID IN NUMBER,
       x_DATA_GROUP IN VARCHAR2,
       x_DATA_GROUP_ID IN NUMBER,
       x_LVL IN NUMBER,
       x_ACTION IN VARCHAR2,
       x_WHOM IN VARCHAR2,
       x_REF_NOTES_ID IN NUMBER,
       x_START_DATE IN DATE,
       x_END_DATE IN DATE,
      X_MODE in VARCHAR2 default 'R'
  ) AS

/******************************************************
Created By : nigupta
Date Created By : 11-MAY-2000
Purpose : To update row
Know limitations, enhancements or remarks : None
Change History
Who		When		What
kumma           03-JUN-2002     Removed the code to update LVL, Bug # 2377971

(reverse chronological order - newest change first)
********************************************************/

     X_LAST_UPDATE_DATE DATE ;

Line: 712


     X_LAST_UPDATED_BY NUMBER ;

Line: 713


     X_LAST_UPDATE_LOGIN NUMBER ;

Line: 715


     X_LAST_UPDATE_DATE := SYSDATE;

Line: 717


        X_LAST_UPDATED_BY := 1;

Line: 718


        X_LAST_UPDATE_LOGIN := 0;

Line: 720


               X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;

Line: 721


            if X_LAST_UPDATED_BY is NULL then
                X_LAST_UPDATED_BY := -1;

Line: 724


            X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;

Line: 725


         if X_LAST_UPDATE_LOGIN is NULL then
            X_LAST_UPDATE_LOGIN := -1;

Line: 734


 		p_action=>'UPDATE',
 		x_rowid=>X_ROWID,
 	       x_privacy_level_id=>X_PRIVACY_LEVEL_ID,
 	       x_person_id=>X_PERSON_ID,
 	       x_data_group=>X_DATA_GROUP,
 	       x_data_group_id=>X_DATA_GROUP_ID,
 	       x_action=>X_ACTION,
 	       x_whom=>X_WHOM,
  	       x_ref_notes_id=>X_REF_NOTES_ID,
	       x_start_date=>X_START_DATE,
 	       x_end_date=>X_END_DATE,
	       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: 753


 update IGS_PE_PRIV_LEVEL set
      PERSON_ID =  NEW_REFERENCES.PERSON_ID,
      DATA_GROUP =  NEW_REFERENCES.DATA_GROUP,
      DATA_GROUP_ID =  NEW_REFERENCES.DATA_GROUP_ID,
      ACTION =  NEW_REFERENCES.ACTION,
      WHOM =  NEW_REFERENCES.WHOM,
      REF_NOTES_ID =  NEW_REFERENCES.REF_NOTES_ID,
      START_DATE =  NEW_REFERENCES.START_DATE,
      END_DATE =  NEW_REFERENCES.END_DATE,
	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: 778


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

Line: 794


end UPDATE_ROW;

Line: 823


    cursor c1 is select ROWID from IGS_PE_PRIV_LEVEL
             where     PRIVACY_LEVEL_ID= X_PRIVACY_LEVEL_ID
;

Line: 831


    INSERT_ROW (
      X_ROWID,
       X_PRIVACY_LEVEL_ID,
       X_PERSON_ID,
       X_DATA_GROUP,
       X_DATA_GROUP_ID,
       X_LVL,
       X_ACTION,
       X_WHOM,
       X_REF_NOTES_ID,
       X_START_DATE,
       X_END_DATE,
      X_MODE );

Line: 847


UPDATE_ROW (
      X_ROWID,
       X_PRIVACY_LEVEL_ID,
       X_PERSON_ID,
       X_DATA_GROUP,
       X_DATA_GROUP_ID,
       X_LVL,
       X_ACTION,
       X_WHOM,
       X_REF_NOTES_ID,
       X_START_DATE,
       X_END_DATE,
      X_MODE );

Line: 862


procedure DELETE_ROW (
  X_ROWID in VARCHAR2,
  x_mode IN VARCHAR2
) AS

/******************************************************
Created By : nigupta
Date Created By : 11-MAY-2000
Purpose : To Delete row
Know limitations, enhancements or remarks : None
Change History
Who		When		What


(reverse chronological order - newest change first)
********************************************************/

begin
Before_DML (
p_action => 'DELETE',
x_rowid => X_ROWID
);

Line: 887


 delete from IGS_PE_PRIV_LEVEL
 where ROWID = X_ROWID;

Line: 900


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

Line: 903


end DELETE_ROW;