APPS.CN_ROLE_PLANS_PKG SQL Statements

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

Line: 4

G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 5

G_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 8

G_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 11

procedure INSERT_ROW
  (X_ROWID      		          IN OUT NOCOPY VARCHAR2,  -- required
   X_ROLE_PLAN_ID          	          IN NUMBER,  -- required
   X_ROLE_ID	       	                  IN NUMBER,  -- required
   X_COMP_PLAN_ID	       	          IN NUMBER,  -- required
   X_START_DATE                           IN DATE,    -- required
   X_END_DATE                             IN DATE,
   X_CREATE_MODULE                        IN VARCHAR2,
   X_ATTRIBUTE_CATEGORY	         	  IN VARCHAR2,
   X_ATTRIBUTE1		        	  IN VARCHAR2,
   X_ATTRIBUTE2		        	  IN VARCHAR2,
   X_ATTRIBUTE3		        	  IN VARCHAR2,
   X_ATTRIBUTE4		        	  IN VARCHAR2,
   X_ATTRIBUTE5		        	  IN VARCHAR2,
   X_ATTRIBUTE6		        	  IN VARCHAR2,
   X_ATTRIBUTE7		        	  IN VARCHAR2,
   X_ATTRIBUTE8		        	  IN VARCHAR2,
   X_ATTRIBUTE9		        	  IN VARCHAR2,
   X_ATTRIBUTE10		       	  IN VARCHAR2,
   X_ATTRIBUTE11		       	  IN VARCHAR2,
   X_ATTRIBUTE12		       	  IN VARCHAR2,
   X_ATTRIBUTE13		       	  IN VARCHAR2,
  X_ATTRIBUTE14		        	  IN VARCHAR2,
  X_ATTRIBUTE15	       	  	          IN VARCHAR2,
  X_CREATED_BY	        		  IN NUMBER,
  X_CREATION_DATE		       	  IN DATE,
  X_LAST_UPDATE_LOGIN	        	  IN NUMBER,
  X_LAST_UPDATE_DATE 	        	  IN DATE,
  X_LAST_UPDATED_BY			  IN NUMBER,
  X_ORG_ID                    IN NUMBER,
  X_OBJECT_VERSION_NUMBER     IN NUMBER) IS


    L_END_DATE 				  cn_role_plans.END_DATE%type;

Line: 63

    L_LAST_UPDATE_LOGIN	       	          cn_role_plans.LAST_UPDATE_LOGIN%type;

Line: 64

    L_LAST_UPDATE_DATE		          cn_role_plans.LAST_UPDATE_DATE%type;

Line: 65

    L_LAST_UPDATED_BY			  cn_role_plans.LAST_UPDATED_BY%type;

Line: 67

    cursor C is select ROWID from CN_ROLE_PLANS
    where ROLE_PLAN_ID = X_ROLE_PLAN_ID;

Line: 73

	SELECT DECODE(X_end_date, FND_API.G_MISS_DATE,
		      to_date(NULL),X_end_date)
	  INTO L_end_date FROM dual;

Line: 76

	SELECT DECODE(X_attribute_category, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_attribute_category)))
	  INTO L_attribute_category FROM dual;

Line: 79

	SELECT DECODE(X_ATTRIBUTE1, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE1)))
	  INTO L_ATTRIBUTE1 FROM dual;

Line: 82

	SELECT DECODE(X_ATTRIBUTE2, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE2)))
	  INTO L_ATTRIBUTE2 FROM dual;

Line: 85

	SELECT DECODE(X_ATTRIBUTE3, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE3)))
	  INTO L_ATTRIBUTE3 FROM dual;

Line: 88

	SELECT DECODE(X_ATTRIBUTE4, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE4)))
	  INTO L_ATTRIBUTE4 FROM dual;

Line: 91

	SELECT DECODE(X_ATTRIBUTE5, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE5)))
	  INTO L_ATTRIBUTE5 FROM dual;

Line: 94

	SELECT DECODE(X_ATTRIBUTE6, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE6)))
	  INTO L_ATTRIBUTE6 FROM dual;

Line: 97

	SELECT DECODE(X_ATTRIBUTE7, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE7)))
	  INTO L_ATTRIBUTE7 FROM dual;

Line: 100

	SELECT DECODE(X_ATTRIBUTE8, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE8)))
	  INTO L_ATTRIBUTE8 FROM dual;

Line: 103

	SELECT DECODE(X_ATTRIBUTE9, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE9)))
	  INTO L_ATTRIBUTE9 FROM dual;

Line: 106

	SELECT DECODE(X_ATTRIBUTE10, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE10)))
	  INTO L_ATTRIBUTE10 FROM dual;

Line: 109

	SELECT DECODE(X_ATTRIBUTE11, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE11)))
	  INTO L_ATTRIBUTE11 FROM dual;

Line: 112

	SELECT DECODE(X_ATTRIBUTE12, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE12)))
	  INTO L_ATTRIBUTE12 FROM dual;

Line: 115

	SELECT DECODE(X_ATTRIBUTE13, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE13)))
	  INTO L_ATTRIBUTE13 FROM dual;

Line: 118

	SELECT DECODE(X_ATTRIBUTE14, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_ATTRIBUTE14)))
	  INTO L_ATTRIBUTE14 FROM dual;

Line: 121

	SELECT DECODE(X_attribute15, FND_API.G_MISS_CHAR,
		      NULL,Ltrim(Rtrim(X_attribute15)))
	  INTO L_attribute15 FROM dual;

Line: 124

	SELECT DECODE(X_created_by, FND_API.G_MISS_NUM,
		      G_CREATED_BY,Ltrim(Rtrim(X_CREATED_BY)))
	  INTO L_created_by FROM dual;

Line: 127

	SELECT DECODE(X_creation_date, FND_API.G_MISS_DATE,
		      G_CREATION_DATE,X_CREATION_DATE)
	  INTO L_creation_date FROM dual;

Line: 130

	SELECT DECODE(X_last_update_login, FND_API.G_MISS_NUM,
		      G_LAST_UPDATE_LOGIN,Ltrim(Rtrim(X_LAST_UPDATE_LOGIN)))
	  INTO L_last_update_login FROM dual;

Line: 133

	SELECT DECODE(X_last_update_date, FND_API.G_MISS_DATE,
		      G_LAST_UPDATE_DATE,X_LAST_UPDATE_DATE)
	  INTO L_last_update_date FROM dual;

Line: 136

	SELECT DECODE(X_last_updated_by, FND_API.G_MISS_NUM,
		      G_LAST_UPDATED_BY,Ltrim(Rtrim(X_LAST_UPDATED_BY)))
	  INTO L_last_updated_by FROM dual;

Line: 140

	-- dbms_output.put_line('before insert_row');

Line: 142

	INSERT INTO cn_role_plans (
			 ROLE_PLAN_ID,
			 ROLE_ID,
			 COMP_PLAN_ID,
			 START_DATE,
			 END_DATE,
			 CREATE_MODULE,
			 ATTRIBUTE_CATEGORY,
			 ATTRIBUTE1,
			 ATTRIBUTE2,
			 ATTRIBUTE3,
			 ATTRIBUTE4,
			 ATTRIBUTE5,
			 ATTRIBUTE6,
			 ATTRIBUTE7,
			 ATTRIBUTE8,
			 ATTRIBUTE9,
			 ATTRIBUTE10,
			 ATTRIBUTE11,
			 ATTRIBUTE12,
			 ATTRIBUTE13,
			 ATTRIBUTE14,
			 ATTRIBUTE15,
			 CREATED_BY,
			 CREATION_DATE,
			 LAST_UPDATE_LOGIN,
			 LAST_UPDATE_DATE,
			 LAST_UPDATED_BY,
             ORG_ID,
             OBJECT_VERSION_NUMBER)
                  VALUES (
			 X_ROLE_PLAN_ID,
			 X_ROLE_ID,
			 X_COMP_PLAN_ID,
			 X_START_DATE,
			 L_END_DATE,
			 X_CREATE_MODULE,
			 L_ATTRIBUTE_CATEGORY,
			 L_ATTRIBUTE1,
			 L_ATTRIBUTE2,
			 L_ATTRIBUTE3,
			 L_ATTRIBUTE4,
			 L_ATTRIBUTE5,
			 L_ATTRIBUTE6,
			 L_ATTRIBUTE7,
			 L_ATTRIBUTE8,
			 L_ATTRIBUTE9,
			 L_ATTRIBUTE10,
			 L_ATTRIBUTE11,
			 L_ATTRIBUTE12,
			 L_ATTRIBUTE13,
			 L_ATTRIBUTE14,
			 L_ATTRIBUTE15,
			 L_CREATED_BY,
			 L_CREATION_DATE,
			 L_LAST_UPDATE_LOGIN,
			 L_LAST_UPDATE_DATE,
		     L_LAST_UPDATED_BY,
             X_ORG_ID,
             X_OBJECT_VERSION_NUMBER);

Line: 203

	-- dbms_output.put_line('after insert_row');

Line: 208

    dbms_output.put_line('fail insert');

Line: 214

  dbms_output.put_line('leaving insert_row');

Line: 217

END insert_row;

Line: 220

procedure UPDATE_ROW (
		      X_ROLE_PLAN_ID	       	          IN NUMBER,  -- required
		      X_ROLE_ID	       	                  IN NUMBER,
		      X_COMP_PLAN_ID	       	          IN NUMBER,
		      X_START_DATE                        IN DATE,
		      X_END_DATE                          IN DATE,
		      X_ATTRIBUTE_CATEGORY	       	  IN VARCHAR2,
		      X_ATTRIBUTE1		       	  IN VARCHAR2,
		      X_ATTRIBUTE2		       	  IN VARCHAR2,
		      X_ATTRIBUTE3		       	  IN VARCHAR2,
		      X_ATTRIBUTE4		       	  IN VARCHAR2,
		      X_ATTRIBUTE5		       	  IN VARCHAR2,
		      X_ATTRIBUTE6		       	  IN VARCHAR2,
		      X_ATTRIBUTE7		       	  IN VARCHAR2,
		      X_ATTRIBUTE8		       	  IN VARCHAR2,
		      X_ATTRIBUTE9		       	  IN VARCHAR2,
		      X_ATTRIBUTE10		       	  IN VARCHAR2,
		      X_ATTRIBUTE11		       	  IN VARCHAR2,
		      X_ATTRIBUTE12		       	  IN VARCHAR2,
		      X_ATTRIBUTE13		       	  IN VARCHAR2,
		      X_ATTRIBUTE14		       	  IN VARCHAR2,
		      X_ATTRIBUTE15	       		  IN VARCHAR2,
		      X_CREATED_BY	       		  IN NUMBER,
		      X_CREATION_DATE		       	  IN DATE,
		      X_LAST_UPDATE_LOGIN	       	  IN NUMBER,
		      X_LAST_UPDATE_DATE		  IN DATE,
              X_LAST_UPDATED_BY			  IN NUMBER,
              X_OBJECT_VERSION_NUMBER     IN NUMBER,
              X_ORG_ID                      IN NUMBER) IS

   CURSOR cur IS
     SELECT * FROM cn_role_plans
       WHERE role_plan_id = x_role_plan_id;

Line: 269

        SELECT DECODE(X_role_id, FND_API.G_MISS_NUM,
		      rec.role_id,Ltrim(Rtrim(X_role_id)))
	  INTO rec.role_id FROM dual;

Line: 272

        SELECT DECODE(X_comp_plan_id, FND_API.G_MISS_NUM,
		      rec.comp_plan_id,Ltrim(Rtrim(X_comp_plan_id)))
	  INTO rec.comp_plan_id FROM dual;

Line: 275

        SELECT DECODE(X_start_date, FND_API.G_MISS_DATE,
		      rec.start_date,X_start_date)
	  INTO rec.start_date FROM dual;

Line: 278

        SELECT DECODE(X_end_date, FND_API.G_MISS_DATE,
		      rec.end_date,X_end_date)
	  INTO rec.end_date FROM dual;

Line: 281

	SELECT DECODE(X_attribute_category, FND_API.G_MISS_CHAR,
		      rec.attribute_category,Ltrim(Rtrim(X_attribute_category)))
	  INTO rec.attribute_category FROM dual;

Line: 284

	SELECT DECODE(X_ATTRIBUTE1, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE1,Ltrim(Rtrim(X_ATTRIBUTE1)))
	  INTO rec.ATTRIBUTE1 FROM dual;

Line: 287

	SELECT DECODE(X_ATTRIBUTE2, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE2,Ltrim(Rtrim(X_ATTRIBUTE2)))
	  INTO rec.ATTRIBUTE2 FROM dual;

Line: 290

	SELECT DECODE(X_ATTRIBUTE3, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE3,Ltrim(Rtrim(X_ATTRIBUTE3)))
	  INTO rec.ATTRIBUTE3 FROM dual;

Line: 293

	SELECT DECODE(X_ATTRIBUTE4, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE4,Ltrim(Rtrim(X_ATTRIBUTE4)))
	  INTO rec.ATTRIBUTE4 FROM dual;

Line: 296

	SELECT DECODE(X_ATTRIBUTE5, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE5,Ltrim(Rtrim(X_ATTRIBUTE5)))
	  INTO rec.ATTRIBUTE5 FROM dual;

Line: 299

	SELECT DECODE(X_ATTRIBUTE6, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE6,Ltrim(Rtrim(X_ATTRIBUTE6)))
	  INTO rec.ATTRIBUTE6 FROM dual;

Line: 302

	SELECT DECODE(X_ATTRIBUTE7, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE7,Ltrim(Rtrim(X_ATTRIBUTE7)))
	  INTO rec.ATTRIBUTE7 FROM dual;

Line: 305

	SELECT DECODE(X_ATTRIBUTE8, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE8,Ltrim(Rtrim(X_ATTRIBUTE8)))
	  INTO rec.ATTRIBUTE8 FROM dual;

Line: 308

	SELECT DECODE(X_ATTRIBUTE9, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE9,Ltrim(Rtrim(X_ATTRIBUTE9)))
	  INTO rec.ATTRIBUTE9 FROM dual;

Line: 311

	SELECT DECODE(X_ATTRIBUTE10, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE10,Ltrim(Rtrim(X_ATTRIBUTE10)))
	  INTO rec.ATTRIBUTE10 FROM dual;

Line: 314

	SELECT DECODE(X_ATTRIBUTE11, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE11,Ltrim(Rtrim(X_ATTRIBUTE11)))
	  INTO rec.ATTRIBUTE11 FROM dual;

Line: 317

	SELECT DECODE(X_ATTRIBUTE12, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE12,Ltrim(Rtrim(X_ATTRIBUTE12)))
	  INTO rec.ATTRIBUTE12 FROM dual;

Line: 320

	SELECT DECODE(X_ATTRIBUTE13, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE13,Ltrim(Rtrim(X_ATTRIBUTE13)))
	  INTO rec.ATTRIBUTE13 FROM dual;

Line: 323

	SELECT DECODE(X_ATTRIBUTE14, FND_API.G_MISS_CHAR,
		      rec.ATTRIBUTE14,Ltrim(Rtrim(X_ATTRIBUTE14)))
	  INTO rec.ATTRIBUTE14 FROM dual;

Line: 326

	SELECT DECODE(X_attribute15, FND_API.G_MISS_CHAR,
		      rec.attribute15,Ltrim(Rtrim(X_attribute15)))
	  INTO rec.attribute15 FROM dual;

Line: 329

	SELECT DECODE(X_created_by, FND_API.G_MISS_NUM,
		      G_CREATED_BY,Ltrim(Rtrim(X_created_by)))
	  INTO rec.created_by FROM dual;

Line: 332

	SELECT DECODE(X_creation_date, FND_API.G_MISS_DATE,
		      G_CREATION_DATE,X_creation_date)
	  INTO rec.creation_date FROM dual;

Line: 335

	SELECT DECODE(X_last_update_login, FND_API.G_MISS_NUM,
		      G_LAST_UPDATE_LOGIN,Ltrim(Rtrim(X_last_update_login)))
	  INTO rec.last_update_login FROM dual;

Line: 338

	SELECT DECODE(X_last_update_date, FND_API.G_MISS_DATE,
		      G_LAST_UPDATE_DATE,X_last_update_date)
	  INTO rec.last_update_date FROM dual;

Line: 341

	SELECT DECODE(X_last_updated_by, FND_API.G_MISS_NUM,
		      G_LAST_UPDATED_BY,Ltrim(Rtrim(X_last_updated_by)))
	  INTO rec.last_updated_by FROM dual;

Line: 345

	UPDATE cn_role_plans SET
	  role_id      = rec.role_id,
	  comp_plan_id = rec.comp_plan_id,
	  start_date   = rec.start_date,
	  end_date     = rec.end_date,
	  ATTRIBUTE_CATEGORY = rec.ATTRIBUTE_CATEGORY,
	  ATTRIBUTE1 = rec.ATTRIBUTE1,
	  ATTRIBUTE2 = rec.ATTRIBUTE2,
	  ATTRIBUTE3 = rec.ATTRIBUTE3,
	  ATTRIBUTE4 = rec.ATTRIBUTE4,
	  ATTRIBUTE5 = rec.ATTRIBUTE5,
	  ATTRIBUTE6 = rec.ATTRIBUTE6,
	  ATTRIBUTE7 = rec.ATTRIBUTE7,
	  ATTRIBUTE8 = rec.ATTRIBUTE8,
	  ATTRIBUTE9 = rec.ATTRIBUTE9,
	  ATTRIBUTE10 = rec.ATTRIBUTE10,
	  ATTRIBUTE11 = rec.ATTRIBUTE11,
	  ATTRIBUTE12 = rec.ATTRIBUTE12,
	  ATTRIBUTE13 = rec.ATTRIBUTE13,
	  ATTRIBUTE14 = rec.ATTRIBUTE14,
	  ATTRIBUTE15 = rec.ATTRIBUTE15,
	  CREATED_BY = rec.CREATED_BY,
	  CREATION_DATE = rec.CREATION_DATE,
	  LAST_UPDATE_LOGIN = rec.LAST_UPDATE_LOGIN,
	  LAST_UPDATE_DATE = rec.LAST_UPDATE_DATE,
	  LAST_UPDATED_BY = rec.LAST_UPDATED_BY,
      OBJECT_VERSION_NUMBER = rec.OBJECT_VERSION_NUMBER +1,
      ORG_ID = rec.ORG_ID
	WHERE role_plan_id =  rec.role_plan_id;

Line: 382

END UPDATE_ROW;

Line: 390

procedure DELETE_ROW (X_ROLE_PLAN_ID	  IN NUMBER) IS
BEGIN
   DELETE FROM cn_role_plans
     WHERE role_plan_id = x_role_plan_id;

Line: 397

END delete_row;