APPS.CN_CALC_SUB_BATCHES_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: 21

Procedure Insert_row ( 	 p_calc_sub_batch_id      NUMBER,
			 p_name                   VARCHAR2,
			 p_start_date             DATE,
			 p_end_date               DATE,
			 p_intelligent_flag       VARCHAR2,
			 p_hierarchy_flag         VARCHAR2,
			 p_salesrep_option        VARCHAR2,
			 p_concurrent_flag        VARCHAR2,
			 p_log_name               VARCHAR2,
			 p_status                 VARCHAR2,
			 p_logical_batch_id       NUMBER,
			 p_calc_type              VARCHAR2,
			 p_interval_type_id       NUMBER,
             p_org_id                 NUMBER,
			 P_ATTRIBUTE_CATEGORY     VARCHAR2,
                         P_ATTRIBUTE1             VARCHAR2,
                         P_ATTRIBUTE2             VARCHAR2,
                         P_ATTRIBUTE3             VARCHAR2,
                         P_ATTRIBUTE4             VARCHAR2,
                         P_ATTRIBUTE5             VARCHAR2,
                         P_ATTRIBUTE6             VARCHAR2,
                         P_ATTRIBUTE7             VARCHAR2,
                         P_ATTRIBUTE8             VARCHAR2,
                         P_ATTRIBUTE9             VARCHAR2,
                         P_ATTRIBUTE10            VARCHAR2,
                         P_ATTRIBUTE11            VARCHAR2,
                         P_ATTRIBUTE12            VARCHAR2,
                         P_ATTRIBUTE13            VARCHAR2,
                         P_ATTRIBUTE14            VARCHAR2,
                         P_ATTRIBUTE15            VARCHAR2,
                         P_CREATED_BY             NUMBER  ,
                         P_CREATION_DATE          DATE    ,
                         P_LAST_UPDATE_LOGIN      NUMBER  ,
                         P_LAST_UPDATE_DATE       DATE    ,
                         P_LAST_UPDATED_BY        NUMBER
                        ) IS
   l_calc_sub_batch_id NUMBER(15);

Line: 63

      SELECT cn_calc_submission_batches_s1.NEXTVAL
	INTO l_calc_sub_batch_id
	FROM dual;

Line: 68

   INSERT INTO cn_calc_submission_batches_all
               (    calc_sub_batch_id,
		    name,
		    start_date,
		    end_date,
		    intelligent_flag,
		    hierarchy_flag,
		    salesrep_option,
		    concurrent_flag,
		    log_name,
		    status,
		    logical_batch_id,
		    calc_type,
		    interval_type_id,
            org_id
		    ,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
		   )
     VALUES    (    l_calc_sub_batch_id,
		    p_name,
		    p_start_date,
	            p_end_date,
		    p_intelligent_flag,
		    p_hierarchy_flag,
		    p_salesrep_option,
		    p_concurrent_flag,
		    p_log_name,
		    p_status,
		    p_logical_batch_id,
		    p_calc_type,
		    p_interval_type_id,
            p_org_id
		    ,p_attribute_category
		    ,p_attribute1
		    ,p_attribute2
		    ,p_attribute3
		    ,p_attribute4
		    ,p_attribute5
		    ,p_attribute6
		    ,p_attribute7
		    ,p_attribute8
		    ,p_attribute9
		    ,p_attribute10
		    ,p_attribute11
		    ,p_attribute12
		    ,p_attribute13
		    ,p_attribute14
		    ,p_attribute15
		    ,Nvl( p_created_by,g_created_by)
		    ,Nvl( p_creation_date,g_creation_date )
		    ,Nvl( p_last_update_login, g_last_update_login )
		    ,Nvl( p_last_update_date, g_last_update_date )
		    ,Nvl( p_last_updated_by, g_last_updated_by  )
		    ) ;

Line: 143

END insert_row;

Line: 146

Procedure Update_row ( 	 p_calc_sub_batch_id      NUMBER,
			 p_name                   VARCHAR2,
			 p_start_date             DATE,
			 p_end_date               DATE,
			 p_intelligent_flag       VARCHAR2,
			 p_hierarchy_flag         VARCHAR2,
			 p_salesrep_option        VARCHAR2,
			 p_concurrent_flag        VARCHAR2,
			 p_log_name               VARCHAR2,
			 p_status                 VARCHAR2,
			 p_logical_batch_id       NUMBER,
			 p_calc_type              VARCHAR2,
			 p_interval_type_id       NUMBER,
                         P_ATTRIBUTE_CATEGORY     VARCHAR2,
                         P_ATTRIBUTE1             VARCHAR2,
                         P_ATTRIBUTE2             VARCHAR2,
                         P_ATTRIBUTE3             VARCHAR2,
                         P_ATTRIBUTE4             VARCHAR2,
                         P_ATTRIBUTE5             VARCHAR2,
                         P_ATTRIBUTE6             VARCHAR2,
                         P_ATTRIBUTE7             VARCHAR2,
                         P_ATTRIBUTE8             VARCHAR2,
                         P_ATTRIBUTE9             VARCHAR2,
                         P_ATTRIBUTE10            VARCHAR2,
                         P_ATTRIBUTE11            VARCHAR2,
                         P_ATTRIBUTE12            VARCHAR2,
                         P_ATTRIBUTE13            VARCHAR2,
                         P_ATTRIBUTE14            VARCHAR2,
                         P_ATTRIBUTE15            VARCHAR2,
                         P_CREATED_BY             NUMBER  ,
                         P_CREATION_DATE          DATE    ,
                         P_LAST_UPDATE_LOGIN      NUMBER  ,
                         P_LAST_UPDATE_DATE       DATE    ,
                         P_LAST_UPDATED_BY        NUMBER
                        ) IS

BEGIN

   UPDATE cn_calc_submission_batches_all SET
     calc_sub_batch_id = p_calc_sub_batch_id,
     name = p_name,
     start_date = p_start_date,
     end_date = p_end_date,
     intelligent_flag = p_intelligent_flag,
     hierarchy_flag  = p_hierarchy_flag,
     salesrep_option = p_salesrep_option ,
     concurrent_flag = p_concurrent_flag ,
     log_name = p_log_name,
     status = p_status ,
     logical_batch_id = p_logical_batch_id,
     calc_type = p_calc_type,
     interval_type_id = p_interval_type_id
     ,attribute_category = p_attribute_category
     ,attribute1 = p_attribute1
     ,attribute2 = p_attribute2
     ,attribute3 = p_attribute3
     ,attribute4 = p_attribute4
     ,attribute5 = p_attribute5
     ,attribute6 = p_attribute6
     ,attribute7 = p_attribute7
     ,attribute8 = p_attribute8
     ,attribute9 = p_attribute9
     ,attribute10 = p_attribute10
     ,attribute11 = p_attribute11
     ,attribute12 = p_attribute12
     ,attribute13 = p_attribute13
     ,attribute14 = p_attribute14
     ,attribute15 = p_attribute15
     ,created_by = Nvl( p_created_by,g_created_by)
     ,creation_date = Nvl( p_creation_date,g_creation_date )
     ,last_update_login = Nvl( p_last_update_login, g_last_update_login )
     ,last_update_date =  Nvl( p_last_update_date, g_last_update_date )
     ,last_updated_by = Nvl( p_last_updated_by, g_last_updated_by  )

   WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 222

END update_row;

Line: 224

Procedure delete_row ( 	 p_calc_sub_batch_id      NUMBER ) IS

BEGIN
   DELETE cn_calc_submission_batches_all
     WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 234

   DELETE cn_calc_submission_entries_all
       WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 237

   DELETE cn_calc_sub_quotas_all
     WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 240

END delete_row;

Line: 273

                         P_LAST_UPDATE_LOGIN      NUMBER  ,
                         P_LAST_UPDATE_DATE       DATE    ,
                         P_LAST_UPDATED_BY        NUMBER
  ) IS
     CURSOR C IS
	SELECT * FROM cn_calc_submission_batches_all
	  WHERE calc_sub_batch_id = p_calc_sub_batch_id
	  FOR UPDATE OF calc_sub_batch_id NOWAIT;

Line: 290

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

Line: 420

                         P_LAST_UPDATE_LOGIN      NUMBER   := NULL,
                         P_LAST_UPDATE_DATE       DATE     := NULL,
                         P_LAST_UPDATED_BY        NUMBER   := NULL
    ) IS
BEGIN
   IF p_operation = 'INSERT' THEN
      insert_row (  p_calc_sub_batch_id,
		    p_name,
		    p_start_date,
		    p_end_date,
		    p_intelligent_flag,
		    p_hierarchy_flag,
		    p_salesrep_option,
		    p_concurrent_flag,
		    p_log_name,
		    p_status,
		    p_logical_batch_id,
		    p_calc_type,
		    p_interval_type_id,
            p_org_id
		    ,p_attribute_category
		    ,p_attribute1
		    ,p_attribute2
		    ,p_attribute3
		    ,p_attribute4
		    ,p_attribute5
		    ,p_attribute6
		    ,p_attribute7
		    ,p_attribute8
		    ,p_attribute9
		    ,p_attribute10
		    ,p_attribute11
		    ,p_attribute12
		    ,p_attribute13
		    ,p_attribute14
		    ,p_attribute15
		    ,p_created_by
		    ,p_creation_date
		    ,p_last_update_login
		    ,p_last_update_date
		    ,p_last_updated_by   ) ;

Line: 461

    ELSIF p_operation = 'UPDATE' THEN
      update_row (  p_calc_sub_batch_id,
		    p_name,
		    p_start_date,
		    p_end_date,
		    p_intelligent_flag,
		    p_hierarchy_flag,
		    p_salesrep_option,
		    p_concurrent_flag,
		    p_log_name,
		    p_status,
		    p_logical_batch_id,
		    p_calc_type,
		    p_interval_type_id
		    ,p_attribute_category
		    ,p_attribute1
		    ,p_attribute2
		    ,p_attribute3
		    ,p_attribute4
		    ,p_attribute5
		    ,p_attribute6
		    ,p_attribute7
		    ,p_attribute8
		    ,p_attribute9
		    ,p_attribute10
		    ,p_attribute11
		    ,p_attribute12
		    ,p_attribute13
		    ,p_attribute14
		    ,p_attribute15
		    ,p_created_by
		    ,p_creation_date
		    ,p_last_update_login
		    ,p_last_update_date
		    ,p_last_updated_by  ) ;

Line: 496

    ELSIF p_operation = 'DELETE' THEN
      delete_row (  p_calc_sub_batch_id );

Line: 530

		    ,p_last_update_login
		    ,p_last_update_date
		    ,p_last_updated_by  );

Line: 552

   SELECT  calc_sub_batch_id,
     name,
     intelligent_flag,
     hierarchy_flag,
     salesrep_option,
     logical_batch_id,
     start_date,
     end_date,
     calc_type,
     interval_type_id
     INTO
     x_calc_sub_batch_rec.calc_sub_batch_id,
     x_calc_sub_batch_rec.name,
     x_calc_sub_batch_rec.intelligent_flag,
     x_calc_sub_batch_rec.hierarchy_flag,
     x_calc_sub_batch_rec.salesrep_option,
     x_calc_sub_batch_rec.logical_batch_id,
     x_calc_sub_batch_rec.start_date,
     x_calc_sub_batch_rec.end_date,
     x_calc_sub_batch_rec.calc_type,
     x_calc_sub_batch_rec.interval_type_id
     FROM cn_calc_submission_batches_all csb
     WHERE csb.logical_batch_id = (SELECT pb.logical_batch_id
	FROM cn_process_batches_all pb
	WHERE pb.physical_batch_id = p_physical_batch_id
      AND rownum = 1);

Line: 594

     select intelligent_flag
       into x_return
       from cn_calc_submission_batches_all csb
      where csb.logical_batch_id = (select logical_batch_id
                                      from cn_process_batches_all pb
                                     where pb.physical_batch_id = p_calc_batch_id
                                       and rownum = 1);

Line: 622

     select calc_type
       into l_calc_type
       from cn_calc_submission_batches_all csb
      where csb.logical_batch_id = (select logical_batch_id
                                      from cn_process_batches_all pb
                                     where pb.physical_batch_id = p_calc_batch_id
                                       and rownum = 1);

Line: 655

     select calc_type
       into x_return
       from cn_calc_submission_batches_all csb
      where csb.logical_batch_id = (select logical_batch_id
                                      from cn_process_batches_all pb
                                     where pb.physical_batch_id = p_calc_batch_id
                                       and rownum = 1);

Line: 682

     select salesrep_option
       into x_return
       from cn_calc_submission_batches_all csb
      where csb.logical_batch_id = (select logical_batch_id
                                      from cn_process_batches_all pb
                                     where pb.physical_batch_id = p_calc_batch_id
                                       and rownum = 1);

Line: 708

     SELECT concurrent_flag
       INTO l_concurrent_flag
       FROM cn_calc_submission_batches_all
       WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 730

     SELECT cn_calc_submission_batches_s1.nextval
       INTO  l_calc_sub_batch_id
       FROM  sys.dual;

Line: 748

  PROCEDURE delete_calc_sub_batch (p_calc_sub_batch_id NUMBER) IS
  BEGIN
     DELETE cn_calc_submission_entries_all
       WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 753

     DELETE cn_calc_sub_quotas_all
       WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 756

  END delete_calc_sub_batch;

Line: 770

  PROCEDURE update_calc_sub_batch (p_logical_batch_id NUMBER,
				   p_status	     VARCHAR2) IS
    l_salesrep_option varchar2(30);

Line: 783

     UPDATE cn_calc_submission_batches_all
       SET status = p_status
       WHERE logical_batch_id = p_logical_batch_id;

Line: 788

	SELECT salesrep_option, intelligent_flag, calc_type,
	  start_date, end_date, org_id
	  INTO l_salesrep_option, l_intel_flag, l_calc_type,
	  l_start_date, l_end_date, l_org_id
	  FROM cn_calc_submission_batches_all
	  WHERE logical_batch_id = p_logical_batch_id;

Line: 802

	      UPDATE  cn_notify_log_all
		SET  status = 'COMPLETE'
		WHERE status 	= 'INCOMPLETE'
        AND org_id = l_org_id
		AND period_id between l_from_period_id and l_to_period_id
		AND ( l_intel_flag = 'Y'
		      OR (l_intel_flag = 'N' AND start_date >= l_start_date ) );

Line: 812

  END update_calc_sub_batch;