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.CN_SRP_PLAN_ASSIGNS_PKG SQL Statements

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

Line: 27


   SELECT  cn_srp_plan_assigns_s.nextval
     INTO  x_srp_plan_assign_id
     FROM  dual;

Line: 45


      SELECT MAX(end_date)
        INTO l_next_end_date
        FROM cn_period_statuses_all s, cn_repositories_all r
       WHERE s.period_type_id = r.period_type_id
	AND s.period_set_id  = r.period_set_id
	AND s.org_id = p_org_id
	AND r.org_id = p_org_id;

Line: 55


        SELECT MIN(end_date)
          INTO l_next_end_date
          FROM cn_period_statuses_all s, cn_repositories_all r
         WHERE trunc(end_date) >= trunc(p_end_date)
           AND s.period_type_id = r.period_type_id
	  AND s.period_set_id  = r.period_set_id
	  AND s.org_id = p_org_id
	  AND r.org_id = p_org_id;

Line: 81


PROCEDURE INSERT_ROW
  (X_SRP_PLAN_ASSIGN_ID IN OUT NOCOPY NUMBER,
   X_SRP_ROLE_ID IN NUMBER,
   X_ROLE_PLAN_ID IN NUMBER,
   X_SALESREP_ID IN NUMBER,
   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_DATE IN DATE,
   X_LAST_UPDATED_BY IN NUMBER,
   X_LAST_UPDATE_LOGIN IN NUMBER
   ) IS
      l_dummy NUMBER;

Line: 122


   SELECT org_id INTO l_org_id
     FROM cn_comp_plans_all
    WHERE comp_plan_id = x_comp_plan_id;

Line: 126


   INSERT INTO CN_SRP_PLAN_ASSIGNS
     (SRP_PLAN_ASSIGN_ID,
      SRP_ROLE_ID,
      ROLE_PLAN_ID,
      SALESREP_ID,
      ORG_ID,
      ROLE_ID,
      COMP_PLAN_ID,
      START_DATE,
      END_DATE,
      ATTRIBUTE_CATEGORY,
      ATTRIBUTE1,
      ATTRIBUTE2,
      ATTRIBUTE3,
      ATTRIBUTE4,
      ATTRIBUTE5,
      ATTRIBUTE6,
      ATTRIBUTE7,
      ATTRIBUTE8,
      ATTRIBUTE9,
      ATTRIBUTE10,
      ATTRIBUTE11,
      ATTRIBUTE12,
      ATTRIBUTE13,
      ATTRIBUTE14,
      ATTRIBUTE15,
      CREATION_DATE,
      CREATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_LOGIN
      ) VALUES
     (X_SRP_PLAN_ASSIGN_ID,
     X_SRP_ROLE_ID,
     X_ROLE_PLAN_ID,
     X_SALESREP_ID,
     l_ORG_ID,
     X_ROLE_ID,
     X_COMP_PLAN_ID,
     X_START_DATE,
     X_END_DATE,
     X_ATTRIBUTE_CATEGORY,
     X_ATTRIBUTE1,
     X_ATTRIBUTE2,
     X_ATTRIBUTE3,
     X_ATTRIBUTE4,
     X_ATTRIBUTE5,
     X_ATTRIBUTE6,
     X_ATTRIBUTE7,
     X_ATTRIBUTE8,
     X_ATTRIBUTE9,
     X_ATTRIBUTE10,
     X_ATTRIBUTE11,
     X_ATTRIBUTE12,
     X_ATTRIBUTE13,
     X_ATTRIBUTE14,
     X_ATTRIBUTE15,
     X_CREATION_DATE,
     X_CREATED_BY,
     X_LAST_UPDATE_DATE,
     X_LAST_UPDATED_BY,
     X_LAST_UPDATE_LOGIN
     );

Line: 189


   SELECT 1 INTO l_dummy  FROM CN_SRP_PLAN_ASSIGNS_ALL
     WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID;

Line: 195


END INSERT_ROW;

Line: 232


	 SELECT * FROM CN_SRP_PLAN_ASSIGNS_ALL
	   WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID
	   FOR UPDATE OF SRP_PLAN_ASSIGN_ID nowait;

Line: 241


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

Line: 305


PROCEDURE UPDATE_ROW
  (X_SRP_PLAN_ASSIGN_ID IN NUMBER,
   X_SRP_ROLE_ID IN NUMBER,
   X_ROLE_PLAN_ID IN NUMBER,
   X_SALESREP_ID IN NUMBER,
   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_LAST_UPDATE_DATE IN DATE,
   X_LAST_UPDATED_BY IN NUMBER,
   X_LAST_UPDATE_LOGIN IN NUMBER
   ) IS

      l_srp_role_id		CN_SRP_PLAN_ASSIGNS.srp_role_id%TYPE;

Line: 362


      SELECT * FROM CN_SRP_PLAN_ASSIGNS_ALL
	WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID
	FOR UPDATE OF SRP_PLAN_ASSIGN_ID nowait;

Line: 372


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

Line: 376


    SELECT
      decode(x_srp_role_id,
	     fnd_api.g_miss_num, oldrow.srp_role_id,
	     x_srp_role_id),
      decode(x_role_plan_id,
	     fnd_api.g_miss_num, oldrow.role_plan_id,
	     x_role_plan_id),
      decode(x_salesrep_id,
	     fnd_api.g_miss_num, oldrow.salesrep_id,
	     x_salesrep_id),
      decode(x_role_id,
	     fnd_api.g_miss_num, oldrow.role_id,
	     x_role_id),
      decode(x_comp_plan_id,
	     fnd_api.g_miss_num, oldrow.comp_plan_id,
	     x_comp_plan_id),
      decode(x_start_date,
	     fnd_api.g_miss_date, oldrow.start_date,
	     x_start_date),
      decode(x_end_date,
	     fnd_api.g_miss_date, oldrow.end_date,
	     x_end_date),
      decode(x_attribute_category,
	     fnd_api.g_miss_char, oldrow.attribute_category,
	     x_attribute_category),
      decode(x_attribute1,
	     fnd_api.g_miss_char, oldrow.attribute1,
	     x_attribute1),
      decode(x_attribute2,
	     fnd_api.g_miss_char, oldrow.attribute2,
	     x_attribute2),
      decode(x_attribute3,
	     fnd_api.g_miss_char, oldrow.attribute3,
	     x_attribute3),
      decode(x_attribute4,
	     fnd_api.g_miss_char, oldrow.attribute4,
	     x_attribute4),
      decode(x_attribute5,
	     fnd_api.g_miss_char, oldrow.attribute5,
	     x_attribute5),
      decode(x_attribute6,
	     fnd_api.g_miss_char, oldrow.attribute6,
	     x_attribute6),
      decode(x_attribute7,
	     fnd_api.g_miss_char, oldrow.attribute7,
	     x_attribute7),
      decode(x_attribute8,
	     fnd_api.g_miss_char, oldrow.attribute8,
	     x_attribute8),
      decode(x_attribute9,
	     fnd_api.g_miss_char, oldrow.attribute9,
	     x_attribute9),
      decode(x_attribute10,
	     fnd_api.g_miss_char, oldrow.attribute10,
	     x_attribute10),
      decode(x_attribute11,
	     fnd_api.g_miss_char, oldrow.attribute11,
	     x_attribute11),
      decode(x_attribute12,
	     fnd_api.g_miss_char, oldrow.attribute12,
	     x_attribute12),
      decode(x_attribute13,
	     fnd_api.g_miss_char, oldrow.attribute13,
	     x_attribute13),
      decode(x_attribute14,
	     fnd_api.g_miss_char, oldrow.attribute14,
	     x_attribute14),
      decode(x_attribute15,
	     fnd_api.g_miss_char, oldrow.attribute15,
	     x_attribute15)
      INTO
      l_srp_role_id,
      l_role_plan_id,
      l_salesrep_id,
      l_role_id,
      l_comp_plan_id,
      l_start_date,
      l_end_date,
      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
      FROM dual;

Line: 472


   UPDATE CN_SRP_PLAN_ASSIGNS_ALL SET
     SRP_PLAN_ASSIGN_ID = x_srp_plan_assign_id ,
     SRP_ROLE_ID = l_srp_role_id ,
     ROLE_PLAN_ID = l_role_plan_id ,
     SALESREP_ID = l_salesrep_id ,
     ROLE_ID = l_role_id ,
     COMP_PLAN_ID = l_comp_plan_id ,
     START_DATE = l_start_date ,
     END_DATE = l_end_date ,
     ATTRIBUTE_CATEGORY = l_attribute_category ,
     ATTRIBUTE1 = l_attribute1 ,
     ATTRIBUTE2 = l_attribute2 ,
     ATTRIBUTE3 = l_attribute3 ,
     ATTRIBUTE4 = l_attribute4 ,
     ATTRIBUTE5 = l_attribute5 ,
     ATTRIBUTE6 = l_attribute6 ,
     ATTRIBUTE7 = l_attribute7 ,
     ATTRIBUTE8 = l_attribute8 ,
     ATTRIBUTE9 = l_attribute9 ,
     ATTRIBUTE10 = l_attribute10 ,
     ATTRIBUTE11 = l_attribute11 ,
     ATTRIBUTE12 = l_attribute12 ,
     ATTRIBUTE13 = l_attribute13 ,
     ATTRIBUTE14 = l_attribute14 ,
     ATTRIBUTE15 = l_attribute15 ,
     LAST_UPDATE_DATE = x_last_update_date ,
     LAST_UPDATED_BY = x_last_updated_by ,
     LAST_UPDATE_LOGIN = x_last_update_login
     WHERE SRP_PLAN_ASSIGN_ID = x_srp_plan_assign_id;

Line: 515


	    cn_srp_period_quotas_pkg.insert_record
	      (x_srp_plan_assign_id  => x_srp_plan_assign_id
	       ,x_quota_id	     => NULL
	       ,x_start_period_id    => NULL    -- obsolete
	       ,x_end_period_id      => NULL    -- obsolete
	       ,x_start_date         => next_period(oldrow.end_date,
						    oldrow.org_id)
	       ,x_end_date           => x_end_date );

Line: 523


	    cn_srp_per_quota_rc_pkg.insert_record
	      (x_srp_plan_assign_id  => x_srp_plan_assign_id
	       ,x_quota_id	     => NULL
	       ,x_revenue_class_id   => NULL
	       ,x_start_period_id    => NULL
	       ,x_end_period_id      => NULL
	       ,x_start_date         => next_period(oldrow.end_date,
						    oldrow.org_id)
	       ,x_end_date           => x_end_date);

Line: 534


	    cn_srp_period_quotas_pkg.delete_record
	      (  x_srp_plan_assign_id => x_srp_plan_assign_id
		 ,x_quota_id	       => NULL
		 ,x_start_period_id    => NULL    -- obsolete
		 ,x_end_period_id      => NULL    -- obsolete
		 ,x_start_date         => next_period(x_end_date,
						      oldrow.org_id)
		 ,x_end_date           => oldrow.end_date );

Line: 542


	    cn_srp_per_quota_rc_pkg.delete_record
	      (x_srp_plan_assign_id  => x_srp_plan_assign_id
	       ,x_quota_id	     => NULL
	       ,x_revenue_class_id   => NULL
	       ,x_start_period_id    => NULL
	       ,x_end_period_id      => NULL
	       ,x_start_date         => next_period(x_end_date,
						    oldrow.org_id)
	       ,x_end_date           => oldrow.end_date );

Line: 553


	     SELECT MIN(start_date)
	    	INTO l_next_start_date
	    	FROM cn_acc_period_statuses_v
	       WHERE period_status IN ('F', 'O')
	       AND org_id = oldrow.org_id;

Line: 561


	      cn_srp_period_quotas_pkg.insert_record
	        (x_srp_plan_assign_id  => x_srp_plan_assign_id
	        ,x_quota_id	      => NULL
	        ,x_start_period_id    => NULL    -- obsolete
	        ,x_end_period_id      => NULL    -- obsolete
		 ,x_start_date         => next_period(oldrow.end_date,
						      oldrow.org_id)
	        ,x_end_date           => x_end_date );

Line: 569


	      cn_srp_per_quota_rc_pkg.insert_record
	        (x_srp_plan_assign_id  => x_srp_plan_assign_id
	        ,x_quota_id	     => NULL
	        ,x_revenue_class_id   => NULL
	        ,x_start_period_id    => NULL
	        ,x_end_period_id      => NULL
		 ,x_start_date         => next_period(oldrow.end_date,
						      oldrow.org_id)
	        ,x_end_date           => x_end_date);

Line: 581


	    cn_srp_period_quotas_pkg.delete_record
	      (  x_srp_plan_assign_id => x_srp_plan_assign_id
		 ,x_quota_id	        => NULL
		 ,x_start_period_id    => NULL    -- obsolete
		 ,x_end_period_id      => NULL    -- obsolete
		 ,x_start_date         => next_period(x_end_date,
						      oldrow.org_id)
		 ,x_end_date           => oldrow.end_date );

Line: 589


	    cn_srp_per_quota_rc_pkg.delete_record
	      (x_srp_plan_assign_id  => x_srp_plan_assign_id
	       ,x_quota_id	     => NULL
	       ,x_revenue_class_id   => NULL
	       ,x_start_period_id    => NULL
	       ,x_end_period_id      => NULL
	       ,x_start_date         => next_period(x_end_date,
						    oldrow.org_id)
	       ,x_end_date           => oldrow.end_date );

Line: 600


	 -- start_date changed, delete/add the whole set
	 -- Remove all assignments for this plan/salesrep

	 -- cascades to per_quota_rc
	 cn_srp_per_quota_rc_pkg.delete_record
	   (x_srp_plan_assign_id  => x_srp_plan_assign_id
	    ,x_quota_id	          => NULL
	    ,x_revenue_class_id   => NULL
	    ,x_start_period_id    => NULL
	    ,x_end_period_id      => NULL
	    ,x_start_date         => oldrow.start_date
	    ,x_end_date           => oldrow.end_date );

Line: 612


	 cn_srp_period_quotas_pkg.delete_record
	   (  x_srp_plan_assign_id => x_srp_plan_assign_id
	      ,x_quota_id	    => NULL
	      ,x_start_period_id    => NULL    -- obsolete
	      ,x_end_period_id      => NULL    -- obsolete
	      ,x_start_date         => oldrow.start_date
	      ,x_end_date           => oldrow.end_date );

Line: 620


	 cn_srp_period_quotas_pkg.insert_record
	   (x_srp_plan_assign_id  => x_srp_plan_assign_id
	    ,x_quota_id	          => NULL
	    ,x_start_period_id    => NULL    -- obsolete
	    ,x_end_period_id      => NULL    -- obsolete
	    ,x_start_date         => x_start_date
	    ,x_end_date           => x_end_date );

Line: 627


	 cn_srp_per_quota_rc_pkg.insert_record
	   (x_srp_plan_assign_id  => x_srp_plan_assign_id
	    ,x_quota_id	          => NULL
	    ,x_revenue_class_id   => NULL
	    ,x_start_period_id    => NULL
	    ,x_end_period_id      => NULL
	    ,x_start_date         => x_start_date
	    ,x_end_date           => x_end_date);

Line: 638


END UPDATE_ROW;

Line: 648


PROCEDURE DELETE_ROW  (X_SRP_PLAN_ASSIGN_ID IN NUMBER) IS
BEGIN

   -- delete child rec is called in API:CN_SRP_PLAN_ASSIGNS_PVT
   -- cn_srp_quota_assigns_pkg.delete_record

   DELETE FROM CN_SRP_PLAN_ASSIGNS_ALL
     WHERE SRP_PLAN_ASSIGN_ID = X_SRP_PLAN_ASSIGN_ID;

Line: 661


END DELETE_ROW;