APPS.CN_PAYGROUP_PVT SQL Statements

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

Line: 28

	 SELECT pay_group_id
	   FROM cn_pay_groups
	   WHERE name = p_PayGroup_rec.name
	   AND start_date = p_PayGroup_rec.start_date
	   AND end_date = p_PayGroup_rec.end_date
       and org_id= p_PayGroup_rec.org_id;

Line: 48

      SELECT cn_pay_groups_s.nextval
        INTO x_pay_group_id
        FROM dual;

Line: 281

L_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 282

L_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 285

L_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 290

      SELECT period_set_id
	FROM cn_period_sets
	WHERE period_set_name = p_paygroup_rec.period_set_name
    and org_id = p_paygroup_rec.org_id;

Line: 296

      SELECT period_type_id
	FROM cn_period_types
	WHERE period_type = p_paygroup_rec.period_type
    and org_id = p_paygroup_rec.org_id;

Line: 330

   x_loading_status := 'CN_INSERTED';

Line: 368

   SELECT COUNT(*)
     INTO l_count
     FROM cn_pay_groups
     WHERE name = p_PayGroup_rec.name
     AND start_date = p_PayGroup_rec.start_date
     AND end_date = p_PayGroup_rec.end_date
     and org_id = p_PayGroup_rec.org_id;

Line: 399

      SELECT 1 INTO l_dummy FROM dual
        WHERE NOT EXISTS
        ( SELECT 1
          FROM   cn_pay_groups
          WHERE
                 ((end_date IS NOT NULL) AND
                  (p_paygroup_rec.end_date IS NOT NULL) AND
                  ((start_date BETWEEN p_paygroup_rec.start_date
                    AND p_Paygroup_rec.end_date) OR
                   (end_date BETWEEN p_Paygroup_rec.start_date
                    AND p_paygroup_rec.end_date) OR
                   (p_paygroup_rec.start_date BETWEEN start_date
                    AND end_date))
                  )
          AND  name   = p_paygroup_rec.name
          and org_id = p_paygroup_rec.org_id
        );

Line: 478

	 x_operation            => 'INSERT',
	 x_rowid                => L_ROWID,
	 x_pay_group_id         => l_pay_group_id,
	 x_name                 => p_PayGroup_rec.name,
	 x_period_set_name      => p_PayGroup_rec.period_set_name,
	 x_period_type          => p_PayGroup_rec.period_type,
	 x_start_date           => p_PayGroup_rec.start_date,
	 x_end_date             => p_PayGroup_rec.end_date,
	 x_pay_group_description=> p_PayGroup_rec.pay_group_description,
	 x_period_set_id        => l_period_set_id,
	 x_period_type_id       => l_period_type_id,
	 x_attribute_category   => p_PayGroup_rec.attribute_category,
	 x_attribute1           => p_PayGroup_rec.attribute1,
	 x_attribute2           => p_PayGroup_rec.attribute2,
	 x_attribute3           => p_PayGroup_rec.attribute3,
	 x_attribute4           => p_PayGroup_rec.attribute4,
	 x_attribute5           => p_PayGroup_rec.attribute5,
	 x_attribute6           => p_PayGroup_rec.attribute6,
	 x_attribute7           => p_PayGroup_rec.attribute7,
	 x_attribute8           => p_PayGroup_rec.attribute8,
	 x_attribute9           => p_PayGroup_rec.attribute9,
 	 x_attribute10          => p_PayGroup_rec.attribute10,
	 x_attribute11          => p_PayGroup_rec.attribute10,
	 x_attribute12          => p_PayGroup_rec.attribute12,
	 x_attribute13          => p_PayGroup_rec.attribute13,
	 x_attribute14          => p_PayGroup_rec.attribute14,
	 x_attribute15          => p_PayGroup_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_object_version_number => p_PayGroup_rec.object_version_number,
	 x_org_id                => p_PayGroup_rec.org_id
	);

Line: 578

PROCEDURE  Update_PayGroup (
		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_PayGroup_rec         IN OUT NOCOPY     PayGroup_rec_type,
		x_status               OUT NOCOPY 	VARCHAR2,
		x_loading_status       OUT NOCOPY 	VARCHAR2
		)  IS
L_PKG_NAME                  CONSTANT VARCHAR2(30) := 'CN_PayGroup_PUB';

Line: 591

L_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 592

L_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 595

L_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 600

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

Line: 617

      SELECT period_set_id
	FROM cn_period_sets
	WHERE period_set_name = p_paygroup_rec.period_set_name
    and org_id = p_paygroup_rec.org_id;

Line: 623

      SELECT period_type_id
	FROM cn_period_types
	WHERE period_type = p_paygroup_rec.period_type
    and org_id = p_paygroup_rec.org_id;

Line: 630

      SELECT *
	FROM cn_pay_groups
	WHERE pay_group_id = p_pay_group_id;

Line: 636

    select
        pay_group_id,
        name,
        period_set_name,
        period_type,
        start_date,
        end_date,
        pay_group_description,
        attribute_category,
        attribute1,
        attribute2,
        attribute3,
        attribute4,
        attribute5,
        attribute6,
        attribute7,
        attribute8,
        attribute9,
        attribute10,
        attribute11,
        attribute12,
        attribute13,
        attribute14,
        attribute15,
        object_version_number,
        org_id
    from cn_pay_groups
    where pay_group_id = p_paygroup_rec.pay_group_id;

Line: 673

      SELECT  salesrep_id
        FROM cn_srp_pay_groups
        WHERE  pay_group_id = c_pay_group_id
        AND trunc(start_date) = trunc(c_start_date)
        AND trunc(nvl(end_date, l_null_date)) = trunc(nvl(c_end_date, l_null_date))
        AND org_id = c_org_id;

Line: 685

      SELECT role_id
        FROM cn_srp_roles
        WHERE salesrep_id = p_salesrep_id
        and org_id = p_org_id;

Line: 697

         SELECT comp_plan_id,
           start_date,
           end_date
           FROM cn_srp_plan_assigns
           WHERE role_id = p_role_id
           AND salesrep_id = p_salesrep_id
           AND org_id = p_org_id;

Line: 708

      select sr.srp_role_id, rp.role_plan_id
	from cn_srp_pay_groups spg, cn_pay_groups pg,
	     cn_srp_roles sr, cn_role_plans rp
       where spg.end_date is null
         and spg.pay_group_id = pg.pay_group_id
         and pg.pay_group_id  = p_paygroup_rec.pay_group_id
         and sr.salesrep_id   = spg.salesrep_id
         and sr.org_id        = spg.org_id
	 and sr.role_id       = rp.role_id
	 and greatest(sr.start_date, rp.start_date) <=
	     least(nvl(sr.end_date, l_null_date),
		   nvl(rp.end_date, l_null_date));

Line: 725

   SAVEPOINT    Update_PayGroup;

Line: 746

   x_loading_status := 'CN_UPDATED';

Line: 782

   IF p_paygroup_rec.start_date IS NOT NULL --start date has been updated
     THEN
      IF p_paygroup_rec.end_date IS NOT NULL
	AND (p_paygroup_rec.start_date > p_paygroup_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: 847

    SELECT COUNT(*)
      INTO l_count
      FROM cn_pay_groups
     WHERE name = p_PayGroup_rec.name
       AND start_date = p_PayGroup_rec.start_date
       AND end_date = p_PayGroup_rec.end_date
       and org_id = p_PayGroup_rec.org_id
       AND pay_group_id <> l_pay_group_id;

Line: 876

      SELECT 1 INTO l_dummy FROM dual
        WHERE NOT EXISTS
        ( SELECT 1
          FROM   cn_pay_groups
          WHERE
                 ((end_date IS NOT NULL) AND
                  (p_paygroup_rec.end_date IS NOT NULL) AND
                  ((start_date BETWEEN p_paygroup_rec.start_date
                    AND p_Paygroup_rec.end_date) OR
                   (end_date BETWEEN p_Paygroup_rec.start_date
                    AND p_paygroup_rec.end_date) OR
                   (p_paygroup_rec.start_date BETWEEN start_date
                    AND end_date))
                  )
          AND  name   = p_paygroup_rec.name
          and org_id =  p_paygroup_rec.org_id
          AND  pay_group_id <> l_pay_group_id
        );

Line: 905

   SELECT COUNT(1)
     INTO l_count
     FROM cn_srp_pay_groups
     WHERE pay_group_id = l_pay_group_id;

Line: 918

      SELECT MIN(start_date),MAX(end_date)
	INTO l_start_date,l_end_date
	FROM cn_srp_pay_groups
	WHERE pay_group_id = l_pay_group_id;

Line: 942

   SELECT COUNT(1)
     INTO l_count
     FROM cn_role_pay_groups
     WHERE pay_group_id = l_pay_group_id;

Line: 955

      SELECT MIN(start_date)
	INTO l_start_date
	FROM cn_role_pay_groups
	WHERE pay_group_id = l_pay_group_id;

Line: 960

      SELECT MAX(end_date)
	INTO l_end_date
	FROM cn_role_pay_groups
	WHERE pay_group_id = l_pay_group_id;

Line: 1039

      select 1 into l_valid_data from dual where exists
      (select count(cp.pay_date) from
       cn_srp_pay_groups cspg,cn_payment_worksheets cpw,cn_payruns cp
       where
       cp.payrun_id = cpw.payrun_id and cp.pay_group_id = cspg.pay_group_id and
       cp.org_id = cpw.org_id and cp.org_id = cspg.org_id and
       cpw.salesrep_id = cspg.salesrep_id and cpw.org_id = cspg.org_id and
       cspg.pay_group_id=p_PayGroup_rec.pay_group_id and cpw.quota_id is null);

Line: 1050

      select max(cps.end_date) into l_pay_period_end_date  from
      cn_srp_pay_groups cspg,cn_payment_worksheets cpw,cn_payruns cp,cn_period_statuses cps
      where
      cp.payrun_id = cpw.payrun_id and cp.pay_group_id = cspg.pay_group_id and
      cp.org_id = cpw.org_id and cp.org_id = cspg.org_id and
      cpw.salesrep_id = cspg.salesrep_id and cpw.org_id = cspg.org_id and
      cspg.pay_group_id=p_PayGroup_rec.pay_group_id and cpw.quota_id is null
      and cp.pay_period_id = cps.period_id and cp.org_id = cps.org_id;

Line: 1087

      SELECT 1 INTO l_dummy FROM dual
        WHERE NOT EXISTS
        ( SELECT 1
            FROM cn_pay_groups pg,
                 cn_payruns p
           WHERE pg.pay_group_id = p.pay_group_id
             and pg.org_id = p.org_id
             AND pg.pay_group_id = l_pay_group_id
        );

Line: 1122

       SELECT 1 INTO l_dummy FROM dual
        WHERE NOT EXISTS
        ( SELECT 1
            FROM cn_srp_periods csp,
                 cn_posting_details_sum cpd,
                 cn_srp_pay_groups spg
           WHERE cpd.credited_salesrep_id = spg.salesrep_id
             and cpd.pay_period_id = csp.period_id
             and csp.salesrep_id = cpd.credited_salesrep_id
             and csp.org_id = cpd.org_id
             and csp.org_id = spg.org_id
             and csp.start_date between spg.start_date and nvl(spg.end_date, csp.end_date)
             AND spg.pay_group_id = l_pay_group_id
        );

Line: 1171

	     x_operation            => 'UPDATE',
	     x_rowid                => L_ROWID,
	     x_pay_group_id         => l_pay_group_id,
	     x_name                 => p_PayGroup_rec.name,
	     x_period_set_name      => p_PayGroup_rec.period_set_name,
	     x_period_type          => p_PayGroup_rec.period_type,
	     x_start_date           => p_PayGroup_rec.start_date,
	     x_end_date             => p_PayGroup_rec.end_date,
	     x_pay_group_description=> p_PayGroup_rec.pay_group_description,
             x_period_set_id        => l_period_set_id,
             x_period_type_id       => l_period_type_id,
	     x_attribute_category   => p_PayGroup_rec.attribute_category,
	     x_attribute1           => p_PayGroup_rec.attribute1,
	     x_attribute2           => p_PayGroup_rec.attribute2,
	     x_attribute3           => p_PayGroup_rec.attribute3,
	     x_attribute4           => p_PayGroup_rec.attribute4,
	     x_attribute5           => p_PayGroup_rec.attribute5,
	     x_attribute6           => p_PayGroup_rec.attribute6,
	     x_attribute7           => p_PayGroup_rec.attribute7,
	     x_attribute8           => p_PayGroup_rec.attribute8,
	     x_attribute9           => p_PayGroup_rec.attribute9,
     	     x_attribute10          => p_PayGroup_rec.attribute10,
 	     x_attribute11          => p_PayGroup_rec.attribute10,
	     x_attribute12          => p_PayGroup_rec.attribute12,
	     x_attribute13          => p_PayGroup_rec.attribute13,
	     x_attribute14          => p_PayGroup_rec.attribute14,
	     x_attribute15          => p_PayGroup_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_object_version_number => L_OBJECT_VERSION_NUMBER,
	     x_org_id               => p_PayGroup_rec.org_id
	    );

Line: 1213

      cn_srp_plan_assigns_pvt.update_srp_plan_assigns
	(p_api_version    => 1.0,
	 x_return_status  => x_return_status,
	 x_msg_count      => x_msg_count,
	 x_msg_data       => x_msg_data,
	 p_srp_role_id    => s.srp_role_id,
	 p_role_plan_id   => s.role_plan_id,
	 p_attribute_rec  => NULL,
	 x_loading_status => x_loading_status);

Line: 1295

      ROLLBACK TO Update_PayGroup;

Line: 1304

      ROLLBACK TO Update_PayGroup;

Line: 1314

      ROLLBACK TO Update_PayGroup;

Line: 1327

END Update_PayGroup;

Line: 1333

PROCEDURE  Delete_PayGroup
  (    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_PayGroup_rec               IN  OUT NOCOPY PayGroup_rec_type ,
       x_status		                OUT NOCOPY 	VARCHAR2,
       x_loading_status    	        OUT NOCOPY 	VARCHAR2
       )  IS
L_PKG_NAME                  CONSTANT VARCHAR2(30) := 'CN_PayGroup_PUB';

Line: 1346

L_LAST_UPDATE_DATE          DATE    := sysdate;

Line: 1347

L_LAST_UPDATED_BY           NUMBER  := fnd_global.user_id;

Line: 1350

L_LAST_UPDATE_LOGIN         NUMBER  := fnd_global.login_id;

Line: 1356

	    := 'Delete_PayGroup';

Line: 1366

   SAVEPOINT    Delete_PayGroup ;

Line: 1387

   x_loading_status := 'CN_DELETED';

Line: 1424

  SELECT COUNT(1)
     INTO l_count_role
     FROM cn_role_pay_groups
     WHERE pay_group_id = l_pay_group_id;

Line: 1443

   SELECT COUNT(1)
     INTO l_count
     FROM cn_srp_pay_groups
     WHERE pay_group_id = l_pay_group_id;

Line: 1463

	       x_operation            => 'DELETE',
	       x_rowid                => L_ROWID,
	       x_pay_group_id         => l_pay_group_id,
	       x_name                 => null,
	       x_period_set_name      => null,
	       x_period_type          => null,
	       x_start_date           => null,
	       x_end_date             => null,
	       x_pay_group_description=> null,
           x_period_set_id        => NULL,
	       x_period_type_id       => 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_object_version_number => L_OBJECT_VERSION_NUMBER,
	       x_org_id                => null
	       );

Line: 1517

      ROLLBACK TO Delete_PayGroup;

Line: 1526

      ROLLBACK TO Delete_PayGroup;

Line: 1536

      ROLLBACK TO Delete_PayGroup;

Line: 1550

END Delete_PayGroup;