APPS.CN_CALC_SUBMISSION_PUB SQL Statements

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

Line: 7

G_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 8

G_LAST_UPDATED_BY           NUMBER  := FND_GLOBAL.USER_ID;

Line: 11

G_LAST_UPDATE_LOGIN         NUMBER  := FND_GLOBAL.LOGIN_ID;

Line: 67

	 select 1
	     from cn_period_statuses_all
	     where period_status = 'O'
	     and org_id = g_org_id
	     and (period_set_id, period_type_id) = (select period_set_id, period_type_id
	                                              from cn_repositories_all
	                                             where org_id = g_org_id)
	     and l_start_date between start_date and end_date;

Line: 79

	 SELECT COUNT(*)
	   FROM cn_calc_submission_batches_all
	   WHERE name = p_calc_submission_rec.batch_name
	     AND org_id = g_org_id;

Line: 425

	SELECT 1
	    FROM cn_srp_intel_periods_all
	    WHERE salesrep_id = l_salesrep_id
		AND org_id = g_org_id;

Line: 521

	SELECT quota_id
	  FROM cn_quotas_all
	  WHERE name = p_quota_name
	  AND org_id = g_org_id
	  AND incentive_type_code = 'BONUS'
	  AND ( (interval_type_id = -1000 AND p_interval_type = 'PERIOD')
		OR (interval_type_id = -1001 AND p_interval_type = 'QUARTER')
		OR (interval_type_id = -1002 AND p_interval_type = 'YEAR')
		OR (interval_type_id IN (-1000, -1001, -1002) AND p_interval_type = 'ALL')
		);

Line: 815

      SELECT user_id INTO x_user_id
	FROM fnd_user
	WHERE user_name = p_app_user_resp_rec.user_name;

Line: 857

      SELECT /*+ index_ss(V.T) */ responsibility_id
	  INTO x_responsibility_id
	FROM fnd_responsibility_vl
	WHERE responsibility_name = p_app_user_resp_rec.responsibility_name;

Line: 947

      SELECT status
	FROM cn_calc_submission_batches_all
	WHERE calc_sub_batch_id = p_calc_sub_batch_id;

Line: 1090

   x_loading_status := 'CN_INSERTED';

Line: 1233

      /*SELECT interval_type_id INTO l_interval_type_id
	FROM cn_interval_types
	WHERE name = l_p_calc_submission_rec.interval_type;*/

Line: 1257

     ( p_operation           => 'INSERT',
       p_calc_sub_batch_id   => l_calc_sub_batch_id,
       p_name                => l_p_calc_submission_rec.batch_name,
       p_start_date          => l_p_calc_submission_rec.start_date,
       p_end_date            => l_p_calc_submission_rec.end_date,
       p_intelligent_flag    => l_p_calc_submission_rec.incremental_calculation,
       p_hierarchy_flag      => l_p_calc_submission_rec.entire_hierarchy,
       p_salesrep_option     => l_p_calc_submission_rec.salesrep_option,
       p_concurrent_flag     => l_p_calc_submission_rec.concurrent_calculation,
       p_status              => 'INCOMPLETE',
       p_interval_type_id    => l_interval_type_id,
       p_org_id              => g_org_id,
       p_calc_type           => l_p_calc_submission_rec.calculation_type,
       p_attribute_category  => l_p_calc_submission_rec.attribute_category,
       p_attribute1            => l_p_calc_submission_rec.attribute1,
       p_attribute2            => l_p_calc_submission_rec.attribute2,
       p_attribute3            => l_p_calc_submission_rec.attribute3,
       p_attribute4            => l_p_calc_submission_rec.attribute4,
       p_attribute5            => l_p_calc_submission_rec.attribute5,
       p_attribute6            => l_p_calc_submission_rec.attribute6,
       p_attribute7            => l_p_calc_submission_rec.attribute7,
       p_attribute8            => l_p_calc_submission_rec.attribute8,
       p_attribute9            => l_p_calc_submission_rec.attribute9,
       p_attribute10           => l_p_calc_submission_rec.attribute10,
       p_attribute11           => l_p_calc_submission_rec.attribute11,
       p_attribute12           => l_p_calc_submission_rec.attribute12,
       p_attribute13           => l_p_calc_submission_rec.attribute13,
       p_attribute14           => l_p_calc_submission_rec.attribute14,
       p_attribute15           => l_p_calc_submission_rec.attribute15,
       p_last_update_date     => g_last_update_date,
       p_last_updated_by      => g_last_updated_by,
       p_creation_date        => g_creation_date,
       p_created_by           => g_created_by,
       p_last_update_login    => g_last_update_login
     );

Line: 1305

	    ( p_operation         => 'INSERT',
	      p_calc_sub_batch_id => l_calc_sub_batch_id,
	      p_salesrep_id       => l_salesreps_id_tbl(ctr),
	      p_hierarchy_flag    => l_hierarchy_flag,
	      p_org_id            => g_org_id,
	      p_last_update_date     => g_last_update_date,
	      p_last_updated_by      => g_last_updated_by,
	      p_creation_date        => g_creation_date,
	      p_created_by           => g_created_by,
	      p_last_update_login    => g_last_update_login
	      );

Line: 1323

	   ( p_operation         => 'INSERT',
	     p_calc_sub_batch_id => l_calc_sub_batch_id,
	     p_quota_id          => l_bonus_pe_id_tbl(ctr),
	     p_org_id            => g_org_id,
	     p_last_update_date     => g_last_update_date,
	     p_last_updated_by      => g_last_updated_by,
	     p_creation_date        => g_creation_date,
	     p_created_by           => g_created_by,
	     p_last_update_login    => g_last_update_login
	     );

Line: 1392

   x_loading_status := 'CN_INSERTED';

Line: 1455

				      p_action_code  => 'I',     -- Insert
				      p_bind_data_id => l_bind_data_id,
				      x_return_code  => x_return_status
				      );

Line: 1606

PROCEDURE Update_Calc_Submission
  (
   p_api_version        IN    NUMBER,
   p_init_msg_list      IN    VARCHAR2 := FND_API.G_FALSE,
   p_commit	            IN    VARCHAR2 := FND_API.G_FALSE,
   p_validation_level   IN    NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   x_return_status      OUT NOCOPY   VARCHAR2,
   x_msg_count	        OUT NOCOPY   NUMBER,
   x_msg_data	        OUT NOCOPY   VARCHAR2,
   p_calc_submission_rec_old      IN    calc_submission_rec_type := g_miss_calc_submission_rec,
   p_calc_submission_rec_new      IN    calc_submission_rec_type := g_miss_calc_submission_rec,
   p_app_user_resp_rec    IN  app_user_resp_rec_type                := g_miss_app_user_resp_rec,
   p_salesrep_tbl         IN  salesrep_tbl_type                     := g_miss_salesrep_tbl,
   p_salesrep_tbl_action  IN    VARCHAR2,
   p_bonus_pe_tbl         IN  plan_element_tbl_type                 := g_miss_pe_tbl,
   p_bonus_pe_tbl_action  IN    VARCHAR2,
   x_loading_status       OUT NOCOPY   VARCHAR2
   ) IS

      l_api_name		   CONSTANT VARCHAR2(30) := 'Update_Calc_Submission';

Line: 1648

	 SELECT *
	   FROM cn_calc_submission_batches_all
	   WHERE name = l_name
	   AND org_id = g_org_id;

Line: 1658

	   SELECT COUNT(*)
	     FROM cn_calc_sub_quotas_all
	     WHERE calc_sub_batch_id = l_calc_sub_batch_id
	     AND quota_id = l_quota_id;

Line: 1666

	   SELECT COUNT(*)
	     FROM cn_calc_submission_entries_all
	     WHERE calc_sub_batch_id = l_calc_sub_batch_id
	     AND salesrep_id = l_salesrep_id;

Line: 1679

                    'cn.plsql.cn_calc_submission_pub.update_calc_submission.begin',
	      		    'Beginning of update_calc_submission ...');

Line: 1685

   SAVEPOINT	update_calc_submission;

Line: 1704

   x_loading_status := 'CN_UPDATED';

Line: 1734

                    'cn.plsql.cn_calc_submission_pub.update_calc_submission.org_validate',
	      		    'Validated org_id = ' || g_org_id || ' status = '||l_status);

Line: 1739

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

Line: 1742

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       true);

Line: 1747

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

Line: 1823

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       true);

Line: 1841

	 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_NOT_UPDATEABLE');

Line: 1845

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       true);

Line: 1851

	 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_NOT_UPDATEABLE');

Line: 1856

      x_loading_status := 'CN_CALC_SUB_NOT_UPDATEABLE';

Line: 1861

   SELECT Decode( l_p_calc_submission_rec.batch_name, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.name, l_p_calc_submission_rec.batch_name )
     INTO l_p_calc_submission_rec.batch_name
     FROM dual;

Line: 1872

   SELECT Decode( l_p_calc_submission_rec.start_date, fnd_api.g_miss_date,
		  l_calc_sub_batch_rec.start_date, l_p_calc_submission_rec.start_date )
     INTO l_p_calc_submission_rec.start_date
     FROM dual;

Line: 1877

   SELECT Decode( l_p_calc_submission_rec.end_date, fnd_api.g_miss_date,
		  l_calc_sub_batch_rec.end_date, l_p_calc_submission_rec.end_date )
     INTO l_p_calc_submission_rec.end_date
     FROM dual;

Line: 1882

   SELECT Decode( l_p_calc_submission_rec.calculation_type, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.calc_type, l_p_calc_submission_rec.calculation_type )
     INTO l_p_calc_submission_rec.calculation_type
     FROM dual;

Line: 1887

   SELECT Decode( l_p_calc_submission_rec.salesrep_option, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.salesrep_option, l_p_calc_submission_rec.salesrep_option )
     INTO l_p_calc_submission_rec.salesrep_option
     FROM dual;

Line: 1892

   SELECT Decode( l_p_calc_submission_rec.entire_hierarchy, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.hierarchy_flag, l_p_calc_submission_rec.entire_hierarchy )
     INTO l_p_calc_submission_rec.entire_hierarchy
     FROM dual;

Line: 1897

   SELECT Decode( l_p_calc_submission_rec.concurrent_calculation, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.concurrent_flag, l_p_calc_submission_rec.concurrent_calculation )
     INTO l_p_calc_submission_rec.concurrent_calculation
     FROM dual;

Line: 1902

   SELECT Decode( l_p_calc_submission_rec.incremental_calculation, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.intelligent_flag, l_p_calc_submission_rec.incremental_calculation )
     INTO l_p_calc_submission_rec.incremental_calculation
     FROM dual;

Line: 1907

   SELECT Decode( l_p_calc_submission_rec.interval_type, fnd_api.g_miss_char,
		  Decode( l_calc_sub_batch_rec.interval_type_id, NULL, NULL, -1000, 'PERIOD',
			  -1001, 'QUARTER', -1002, 'YEAR', -1003 , 'ALL' ),
		  l_p_calc_submission_rec.interval_type )
     INTO l_p_calc_submission_rec.interval_type
     FROM dual;

Line: 1914

   SELECT Decode( l_p_calc_submission_rec.attribute_category, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute_category, l_p_calc_submission_rec.attribute_category )
     INTO l_p_calc_submission_rec.attribute_category
     FROM dual;

Line: 1919

   SELECT Decode( l_p_calc_submission_rec.attribute1, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute1, l_p_calc_submission_rec.attribute1 )
     INTO l_p_calc_submission_rec.attribute1
     FROM dual;

Line: 1924

   SELECT Decode( l_p_calc_submission_rec.attribute2, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute2, l_p_calc_submission_rec.attribute2 )
     INTO l_p_calc_submission_rec.attribute2
     FROM dual;

Line: 1929

   SELECT Decode( l_p_calc_submission_rec.attribute3, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute3, l_p_calc_submission_rec.attribute3 )
     INTO l_p_calc_submission_rec.attribute3
     FROM dual;

Line: 1934

   SELECT Decode( l_p_calc_submission_rec.attribute4, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute4, l_p_calc_submission_rec.attribute4 )
     INTO l_p_calc_submission_rec.attribute4
     FROM dual;

Line: 1939

   SELECT Decode( l_p_calc_submission_rec.attribute5, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute5, l_p_calc_submission_rec.attribute5 )
     INTO l_p_calc_submission_rec.attribute5
     FROM dual;

Line: 1944

   SELECT Decode( l_p_calc_submission_rec.attribute6, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute6, l_p_calc_submission_rec.attribute6 )
     INTO l_p_calc_submission_rec.attribute6
     FROM dual;

Line: 1949

   SELECT Decode( l_p_calc_submission_rec.attribute7, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute7, l_p_calc_submission_rec.attribute7 )
     INTO l_p_calc_submission_rec.attribute7
     FROM dual;

Line: 1954

   SELECT Decode( l_p_calc_submission_rec.attribute8, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute8, l_p_calc_submission_rec.attribute8 )
     INTO l_p_calc_submission_rec.attribute8
     FROM dual;

Line: 1959

   SELECT Decode( l_p_calc_submission_rec.attribute9, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute9, l_p_calc_submission_rec.attribute9 )
     INTO l_p_calc_submission_rec.attribute9
     FROM dual;

Line: 1964

   SELECT Decode( l_p_calc_submission_rec.attribute10, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute10, l_p_calc_submission_rec.attribute10 )
     INTO l_p_calc_submission_rec.attribute10
     FROM dual;

Line: 1969

   SELECT Decode( l_p_calc_submission_rec.attribute11, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute11, l_p_calc_submission_rec.attribute11 )
     INTO l_p_calc_submission_rec.attribute11
     FROM dual;

Line: 1974

   SELECT Decode( l_p_calc_submission_rec.attribute12, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute12, l_p_calc_submission_rec.attribute12 )
     INTO l_p_calc_submission_rec.attribute12
     FROM dual;

Line: 1979

   SELECT Decode( l_p_calc_submission_rec.attribute13, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute13, l_p_calc_submission_rec.attribute13 )
     INTO l_p_calc_submission_rec.attribute13
     FROM dual;

Line: 1984

   SELECT Decode( l_p_calc_submission_rec.attribute14, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute14, l_p_calc_submission_rec.attribute14 )
     INTO l_p_calc_submission_rec.attribute14
     FROM dual;

Line: 1989

   SELECT Decode( l_p_calc_submission_rec.attribute15, fnd_api.g_miss_char,
		  l_calc_sub_batch_rec.attribute15, l_p_calc_submission_rec.attribute1 )
     INTO l_p_calc_submission_rec.attribute15
     FROM dual;

Line: 2052

      /*SELECT interval_type_id INTO l_interval_type_id
	FROM cn_interval_types
	WHERE name = l_p_calc_submission_rec.interval_type;*/

Line: 2075

     ( p_operation           => 'UPDATE',
       p_calc_sub_batch_id   => l_calc_sub_batch_id,
       p_name                => l_p_calc_submission_rec.batch_name,
       p_start_date          => l_p_calc_submission_rec.start_date,
       p_end_date            => l_p_calc_submission_rec.end_date,
       p_intelligent_flag    => l_p_calc_submission_rec.incremental_calculation,
       p_hierarchy_flag      => l_p_calc_submission_rec.entire_hierarchy,
       p_salesrep_option     => l_p_calc_submission_rec.salesrep_option,
       p_concurrent_flag     => l_p_calc_submission_rec.concurrent_calculation,
       p_status              => l_calc_sub_batch_rec.status,
       p_interval_type_id    => l_interval_type_id,
       p_org_id              => g_org_id,
       p_calc_type           => l_p_calc_submission_rec.calculation_type,
       p_attribute_category  => l_p_calc_submission_rec.attribute_category,
       p_attribute1            => l_p_calc_submission_rec.attribute1,
       p_attribute2            => l_p_calc_submission_rec.attribute2,
       p_attribute3            => l_p_calc_submission_rec.attribute3,
       p_attribute4            => l_p_calc_submission_rec.attribute4,
       p_attribute5            => l_p_calc_submission_rec.attribute5,
       p_attribute6            => l_p_calc_submission_rec.attribute6,
       p_attribute7            => l_p_calc_submission_rec.attribute7,
       p_attribute8            => l_p_calc_submission_rec.attribute8,
       p_attribute9            => l_p_calc_submission_rec.attribute9,
       p_attribute10           => l_p_calc_submission_rec.attribute10,
       p_attribute11           => l_p_calc_submission_rec.attribute11,
       p_attribute12           => l_p_calc_submission_rec.attribute12,
       p_attribute13           => l_p_calc_submission_rec.attribute13,
       p_attribute14           => l_p_calc_submission_rec.attribute14,
       p_attribute15           => l_p_calc_submission_rec.attribute15,
       p_last_update_date     => g_last_update_date,
       p_last_updated_by      => g_last_updated_by,
       p_creation_date        => g_creation_date,
       p_created_by           => g_created_by,
       p_last_update_login    => g_last_update_login
     );

Line: 2129

		 (  p_operation         => 'INSERT',
		    p_calc_sub_batch_id => l_calc_sub_batch_id,
		    p_salesrep_id       => l_salesreps_id_tbl(ctr),
		    p_hierarchy_flag    => l_hierarchy_flag,
		    p_org_id            => g_org_id,
		    p_last_update_date     => g_last_update_date,
		    p_last_updated_by      => g_last_updated_by,
		    p_creation_date        => g_creation_date,
		    p_created_by           => g_created_by,
		    p_last_update_login    => g_last_update_login
		    );

Line: 2146

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       true);

Line: 2159

       ELSIF p_salesrep_tbl_action = 'DELETE' THEN
	 forall j IN 1 .. l_salesreps_id_tbl.COUNT
	   DELETE cn_calc_submission_entries_all
	   WHERE calc_sub_batch_id = l_calc_sub_batch_id
	   AND salesrep_id = l_salesreps_id_tbl(j);

Line: 2167

	       SELECT 1
		   FROM cn_calc_submission_entries_all
		   WHERE calc_sub_batch_id = l_calc_sub_batch_id;

Line: 2181

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       true);

Line: 2209

		 ( p_operation         => 'INSERT',
		   p_calc_sub_batch_id => l_calc_sub_batch_id,
		   p_quota_id          => l_bonus_pe_id_tbl(ctr),
		   p_org_id            => g_org_id,
		   p_last_update_date     => g_last_update_date,
		   p_last_updated_by      => g_last_updated_by,
		   p_creation_date        => g_creation_date,
		   p_created_by           => g_created_by,
		   p_last_update_login    => g_last_update_login
		   );

Line: 2224

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       true);

Line: 2236

       ELSIF p_bonus_pe_tbl_action = 'DELETE' THEN
	 forall j IN 1 .. l_bonus_pe_id_tbl.COUNT
	    DELETE cn_calc_sub_quotas
	      WHERE calc_sub_batch_id = l_calc_sub_batch_id
	      AND quota_id = l_bonus_pe_id_tbl(j);

Line: 2283

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       false);

Line: 2293

                       'cn.plsql.cn_calc_submission_pub.update_calc_submission.error',
	       		       false);

Line: 2306

   x_loading_status := 'CN_UPDATED';

Line: 2371

				      p_action_code  => 'U',     -- update
				      p_bind_data_id => l_bind_data_id,
				      x_return_code  => x_return_status
				      );

Line: 2404

                    'cn.plsql.cn_calc_submission_pub.update_calc_submission.end',
	      		    'End of update_calc_submission.');

Line: 2410

      ROLLBACK TO update_calc_submission;

Line: 2419

      ROLLBACK TO update_calc_submission;

Line: 2429

      ROLLBACK TO update_calc_submission;

Line: 2445

                         'cn.plsql.cn_calc_submission_pub.update_calc_submission.exception',
		       		     sqlerrm);

Line: 2449

END update_calc_submission;