APPS.CN_PMTPLAN_PVT SQL Statements

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

Line: 24

      SELECT pmt_plan_id
        FROM cn_pmt_plans
       WHERE name = p_PmtPlan_rec.name
         AND start_date = p_PmtPlan_rec.start_date
         AND end_date = p_PmtPlan_rec.end_date
         AND org_id = p_PmtPlan_rec.org_id;

Line: 33

      SELECT pmt_plan_id
        FROM cn_pmt_plans
       WHERE name = p_PmtPlan_rec.name
         AND start_date = p_PmtPlan_rec.start_date
         AND org_id = p_PmtPlan_rec.org_id;

Line: 59

         SELECT nvl(p_PmtPlan_rec.pmt_plan_id,cn_pmt_plans_s.nextval)
           INTO x_pmt_plan_id
           FROM dual;

Line: 74

         SELECT nvl(p_PmtPlan_rec.pmt_plan_id,cn_pmt_plans_s.nextval)
           INTO x_pmt_plan_id
           FROM dual;

Line: 125

      SELECT credit_type_id
	FROM cn_credit_types
	WHERE name = p_PmtPlan_rec.credit_type_name
            and org_id = p_PmtPlan_rec.org_id;

Line: 131

      SELECT interval_type_id
	FROM cn_interval_types
	WHERE name = p_pmtplan_rec.pay_interval_type_name
            and org_id = p_PmtPlan_rec.org_id;

Line: 164

   IF g_mode = 'INSERT'
     THEN

   IF ( (cn_api.chk_miss_char_para
	     (p_char_para => p_PmtPlan_rec.credit_type_name,
	      p_para_name  => 'Credit Type Name',
	      p_loading_status => x_loading_status,
	      x_loading_status => x_loading_status)) = FND_API.G_TRUE )
   THEN
      RAISE FND_API.G_EXC_ERROR ;

Line: 296

   select count(1) into l_pg_count from cn_lookups
   where lookup_type like 'PAYMENT_GROUP_CODE'
   and lookup_code = p_PmtPlan_rec.payment_group_code;

Line: 316

    SELECT COUNT(*)
	INTO l_count
        FROM cn_pmt_plans
        WHERE name = p_PmtPlan_rec.name
                and org_id = p_PmtPlan_rec.org_id;

Line: 428

    L_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 429

    L_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 432

    L_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 437

    SELECT credit_type_id
    FROM cn_credit_types
    WHERE name = p_PmtPlan_rec.credit_type_name
        and org_id = p_PmtPlan_rec.org_id;

Line: 443

      SELECT interval_type_id
	FROM cn_interval_types
	WHERE name = p_pmtplan_rec.pay_interval_type_name
            and org_id = p_PmtPlan_rec.org_id;

Line: 450

      SELECT interval_type_id
	FROM cn_interval_types
	WHERE name = p_pmtplan_rec.recoverable_interval_type
        and org_id = p_PmtPlan_rec.org_id;

Line: 481

   x_loading_status := 'CN_INSERTED';

Line: 489

   g_mode := 'INSERT';

Line: 615

        x_operation            => 'INSERT',
        x_rowid                => L_ROWID,
        x_org_id                => p_PmtPlan_rec.org_id,
        x_pmt_plan_id          => p_PmtPlan_rec.pmt_plan_id,
        x_name                 => p_PmtPlan_rec.name,
        x_minimum_amount	   => p_PmtPlan_rec.minimum_amount,
        x_maximum_amount	   => p_PmtPlan_rec.maximum_amount,
        x_min_rec_flag	   => Nvl(p_PmtPlan_rec.min_rec_flag, 'Y'),
        x_max_rec_flag	   => Nvl(p_PmtPlan_rec.max_rec_flag, 'Y'),
        x_max_recovery_amount  => p_PmtPlan_rec.max_recovery_amount,
        x_credit_type_id	   => l_credit_type_id,
        x_pay_interval_type_id => l_pay_interval_type_id,
        x_start_date           => p_PmtPlan_rec.start_date,
        x_end_date             => p_PmtPlan_rec.end_date,
        x_object_version_number => p_PmtPlan_rec.object_version_number,
        x_recoverable_interval_type_id => l_recoverable_interval_type_id,
        x_pay_against_commission   => l_pay_against_commission,
        x_attribute_category   => p_PmtPlan_rec.attribute_category,
        x_attribute1           => p_PmtPlan_rec.attribute1,
        x_attribute2           => p_PmtPlan_rec.attribute2,
        x_attribute3           => p_PmtPlan_rec.attribute3,
        x_attribute4           => p_PmtPlan_rec.attribute4,
        x_attribute5           => p_PmtPlan_rec.attribute5,
        x_attribute6           => p_PmtPlan_rec.attribute6,
        x_attribute7           => p_PmtPlan_rec.attribute7,
        x_attribute8           => p_PmtPlan_rec.attribute8,
        x_attribute9           => p_PmtPlan_rec.attribute9,
        x_attribute10          => p_PmtPlan_rec.attribute10,
        x_attribute11          => p_PmtPlan_rec.attribute10,
        x_attribute12          => p_PmtPlan_rec.attribute12,
        x_attribute13          => p_PmtPlan_rec.attribute13,
        x_attribute14          => p_PmtPlan_rec.attribute14,
        x_attribute15          => p_PmtPlan_rec.attribute15,
        x_last_update_date     => l_last_update_date,
        x_last_updated_by      => l_last_updated_by,
        x_creation_date        => l_creation_date,
        x_created_by           => l_created_by,
        x_last_update_login    => l_last_update_login,
        x_program_type         => l_program_type,
        x_payment_group_code   => p_PmtPlan_rec.payment_group_code
	 );

Line: 657

       x_loading_status := 'CN_INSERTED';

Line: 730

PROCEDURE  Update_PmtPlan (
    p_api_version		IN 	NUMBER,
    p_init_msg_list		IN	VARCHAR2,
    p_commit	    		IN  	VARCHAR2,
    p_validation_level		IN  	NUMBER,
    x_return_status       	OUT 	NOCOPY VARCHAR2,
    x_msg_count	        	OUT 	NOCOPY NUMBER,
    x_msg_data			OUT 	NOCOPY VARCHAR2,
    p_old_PmtPlan_rec          IN      PmtPlan_rec_type,
    p_PmtPlan_rec              IN  OUT NOCOPY  PmtPlan_rec_type,
    x_status            	OUT 	NOCOPY VARCHAR2,
    x_loading_status    	OUT 	NOCOPY VARCHAR2
   )  IS

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

Line: 761

    L_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 762

    L_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 765

    L_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 770

    SELECT credit_type_id
    FROM cn_credit_types
    WHERE name = p_PmtPlan_rec.credit_type_name
        and org_id = p_PmtPlan_rec.org_id;

Line: 777

      SELECT interval_type_id
	FROM cn_interval_types
	WHERE name = p_pmtplan_rec.pay_interval_type_name
         AND org_id = p_PmtPlan_rec.org_id;

Line: 783

      SELECT *
	FROM cn_pmt_plans
	WHERE pmt_plan_id = p_pmt_plan_id
            AND org_id = p_PmtPlan_rec.org_id;

Line: 794

      SELECT name
	FROM cn_credit_types
	WHERE credit_type_id = l_credit_type_id
            AND org_id = p_PmtPlan_rec.org_id;

Line: 800

      SELECT name
	FROM cn_interval_types
	WHERE interval_type_id  = l_interval_type_id
            AND org_id = p_PmtPlan_rec.org_id;

Line: 806

    SELECT nvl(object_version_number,1)
      FROM cn_pmt_plans
      WHERE pmt_plan_id = p_old_PmtPlan_rec.pmt_plan_id
            AND org_id = p_old_PmtPlan_rec.org_id;

Line: 814

      SELECT name
	FROM cn_interval_types
	WHERE interval_type_id  = l_rec_interval_type_id
            AND org_id = p_PmtPlan_rec.org_id;

Line: 821

      SELECT interval_type_id
	FROM cn_interval_types
	WHERE name  = l_rec_interval_type
            AND org_id = p_PmtPlan_rec.org_id;

Line: 831

   SAVEPOINT    Update_PmtPlan;

Line: 852

   x_loading_status := 'CN_UPDATED';

Line: 860

   g_mode := 'UPDATE';

Line: 922

   IF p_pmtplan_rec.start_date IS NOT NULL --start date has been updated
     THEN
      IF p_PmtPlan_rec.end_date IS NOT NULL
	AND (p_PmtPlan_rec.start_date > p_PmtPlan_rec.end_date)
	THEN
	 --Error condition
	 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
	   THEN
	    fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');

Line: 1003

   SELECT COUNT(1)
     INTO l_count
     FROM cn_srp_pmt_plans
     WHERE pmt_plan_id = l_pmt_plan_id;

Line: 1047

      SELECT MIN(start_date)
	INTO l_start_date
	FROM cn_srp_pmt_plans
	WHERE pmt_plan_id = l_pmt_plan_id;

Line: 1052

    SELECT MAX(end_date)
	INTO l_end_date
	FROM cn_srp_pmt_plans
	WHERE pmt_plan_id = l_pmt_plan_id
	AND end_date IS NOT NULL;

Line: 1058

	SELECT count(1)
	INTO l_null_end_date_srps
	FROM cn_srp_pmt_plans
	WHERE pmt_plan_id = l_pmt_plan_id
	AND end_date IS NULL;

Line: 1205

    	x_operation            => 'UPDATE',
    	x_rowid                => L_ROWID,
    	x_org_id               => p_PmtPlan_rec.org_id,
    	x_pmt_plan_id          => l_pmt_plan_id,
    	x_name                 => p_PmtPlan_rec.name,
    	x_minimum_amount       => p_PmtPlan_rec.minimum_amount,
    	x_maximum_amount       => p_PmtPlan_rec.maximum_amount,
    	x_min_rec_flag         => p_PmtPlan_rec.min_rec_flag,
    	x_max_rec_flag         => p_PmtPlan_rec.max_rec_flag,
    	x_max_recovery_amount  => p_PmtPlan_rec.max_recovery_amount,
    	x_credit_type_id       => l_credit_type_id,
            x_pay_interval_type_id => l_pay_interval_type_id,
            x_start_date           => p_PmtPlan_rec.start_date,
    	x_end_date             => p_PmtPlan_rec.end_date,
            x_object_version_number => p_PmtPlan_rec.object_version_number,
            x_recoverable_interval_type_id => l_recoverable_interval_type_id,
            x_pay_against_commission   => l_pay_against_commission,
    	x_attribute_category   => p_PmtPlan_rec.attribute_category,
    	x_attribute1           => p_PmtPlan_rec.attribute1,
    	x_attribute2           => p_PmtPlan_rec.attribute2,
    	x_attribute3           => p_PmtPlan_rec.attribute3,
            x_attribute4           => p_PmtPlan_rec.attribute4,
    	x_attribute5           => p_PmtPlan_rec.attribute5,
    	x_attribute6           => p_PmtPlan_rec.attribute6,
    	x_attribute7           => p_PmtPlan_rec.attribute7,
    	x_attribute8           => p_PmtPlan_rec.attribute8,
    	x_attribute9           => p_PmtPlan_rec.attribute9,
    	x_attribute10          => p_PmtPlan_rec.attribute10,
    	x_attribute11          => p_PmtPlan_rec.attribute10,
    	x_attribute12          => p_PmtPlan_rec.attribute12,
    	x_attribute13          => p_PmtPlan_rec.attribute13,
    	x_attribute14          => p_PmtPlan_rec.attribute14,
    	x_attribute15          => p_PmtPlan_rec.attribute15,
    	x_last_update_date     => l_last_update_date,
    	x_last_updated_by      => l_last_updated_by,
    	x_creation_date        => l_creation_date,
    	x_created_by           => l_created_by,
    	x_last_update_login    => l_last_update_login,
    	x_program_type         => l_program_type,
    	x_payment_group_code   => p_PmtPlan_rec.payment_group_code
    	);

Line: 1246

        x_loading_status := 'CN_UPDATED';

Line: 1267

      ROLLBACK TO Update_PmtPlan;

Line: 1276

      ROLLBACK TO Update_PmtPlan;

Line: 1287

      ROLLBACK TO Update_PmtPlan;

Line: 1300

END Update_PmtPlan;

Line: 1306

PROCEDURE  Delete_PmtPlan
   (    p_api_version			IN 	NUMBER,
  	p_init_msg_list		        IN	VARCHAR2,
	p_commit	    		IN  	VARCHAR2,
	p_validation_level		IN  	NUMBER,
        x_return_status       		OUT 	NOCOPY VARCHAR2,
    	x_msg_count	          	OUT 	NOCOPY NUMBER,
    	x_msg_data		  	OUT 	NOCOPY VARCHAR2,
    	p_PmtPlan_rec                  IN      PmtPlan_rec_type ,
        x_status			OUT 	NOCOPY VARCHAR2,
    	x_loading_status    		OUT 	NOCOPY VARCHAR2
   )  IS

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

Line: 1327

L_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 1328

L_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 1331

L_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 1340

   SAVEPOINT    Delete_PmtPlan ;

Line: 1361

   x_loading_status := 'CN_DELETED';

Line: 1369

   g_mode := 'DELETE';

Line: 1403

   SELECT COUNT(1)
     INTO l_count
     FROM cn_srp_pmt_plans
     WHERE pmt_plan_id = l_pmt_plan_id;

Line: 1411

             fnd_message.set_name('CN', 'CN_DELETE_NA');

Line: 1415

          x_loading_status := 'CN_DELETE_NA';

Line: 1420

   SELECT COUNT(1)
     INTO l_count
     FROM cn_role_pmt_plans
     WHERE pmt_plan_id = l_pmt_plan_id;

Line: 1428

             fnd_message.set_name('CN', 'CN_DELETE_NA');

Line: 1431

          x_loading_status := 'CN_DELETE_NA';

Line: 1437

	 x_operation            => 'DELETE',
	 x_rowid                => L_ROWID,
	 x_org_id               => p_PmtPlan_rec.org_id,
	 x_pmt_plan_id          => l_pmt_plan_id,
	 x_name                 => null,
	 x_minimum_amount	   => null,
	 x_maximum_amount	   => null,
	 x_min_rec_flag	           => null,
	 x_max_rec_flag	           => null,
	 x_max_recovery_amount     => null,
	 x_credit_type_id	   => null,
	 x_pay_interval_type_id => null,
	 x_start_date           => null,
	 x_end_date             => null,
         x_object_version_number => l_object_version_number,
         x_recoverable_interval_type_id => null,
         x_pay_against_commission   => null,
	 x_attribute_category   => null,
	 x_attribute1           => null,
	 x_attribute2           => null,
	 x_attribute3           => null,
	 x_attribute4           => null,
	 x_attribute5           => null,
	 x_attribute6           => null,
	 x_attribute7           => null,
	 x_attribute8           => null,
	 x_attribute9           => null,
	 x_attribute10          => null,
	 x_attribute11          => null,
	 x_attribute12          => null,
	 x_attribute13          => null,
	 x_attribute14          => null,
	 x_attribute15          => null,
	 x_last_update_date     => null,
  	 x_last_updated_by      => l_last_updated_by,
	 x_creation_date        => l_creation_date,
	 x_created_by           => l_created_by,
	 x_last_update_login    => l_last_update_login,
	 x_program_type         => l_program_type,
     x_payment_group_code   => p_PmtPlan_rec.payment_group_code
	);

Line: 1478

      x_loading_status := 'CN_DELETED';

Line: 1498

      ROLLBACK TO Delete_PmtPlan;

Line: 1507

      ROLLBACK TO Delete_PmtPlan;

Line: 1517

      ROLLBACK TO Delete_PmtPlan;

Line: 1531

END Delete_PmtPlan;