APPS.AMS_APPROVAL_DETAILS_PVT SQL Statements

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

Line: 72

FUNCTION seed_needs_update(
   p_approval_details_rec   IN  Approval_Details_Rec_Type
) RETURN VARCHAR2;   -- FND_API.g_true/g_false

Line: 129

SELECT name
INTO l_rule_name
FROM ams_approval_details_v
where approval_detail_id = p_approval_detail_id;

Line: 142

SELECT usage
INTO l_usage
FROM ams_custom_setups_b
WHERE custom_setup_id = p_custom_setup_id;

Line: 201

     SELECT ams_approval_details_s.NEXTVAL
     FROM   dual;

Line: 205

     SELECT 1 FROM   dual
       WHERE EXISTS (SELECT 1 FROM   ams_approval_details
                   WHERE  approval_detail_id = x_id);

Line: 290

   AMS_Utility_PVT.debug_message (l_full_name || ': Insert');

Line: 295

   AMS_APPROVAL_DETAILS_PKG.INSERT_ROW (
      X_ROWID => l_row_id,
      X_APPROVAL_DETAIL_ID => l_Approval_Details_rec.APPROVAL_DETAIL_ID,
      X_START_DATE_ACTIVE => l_Approval_Details_rec.START_DATE_ACTIVE,
      X_END_DATE_ACTIVE => l_Approval_Details_rec.END_DATE_ACTIVE,
      X_OBJECT_VERSION_NUMBER => 1, --l_Approval_Details_rec.OBJECT_VERSION_NUMBER,
      --X_SECURITY_GROUP_ID => l_Approval_Details_rec.SECURITY_GROUP_ID,
      X_BUSINESS_GROUP_ID => l_Approval_Details_rec.BUSINESS_GROUP_ID,
      X_BUSINESS_UNIT_ID => l_Approval_Details_rec.BUSINESS_UNIT_ID,
      X_ORGANIZATION_ID => l_Approval_Details_rec.ORGANIZATION_ID,
      X_CUSTOM_SETUP_ID => l_Approval_Details_rec.CUSTOM_SETUP_ID,
      X_APPROVAL_OBJECT => l_Approval_Details_rec.APPROVAL_OBJECT,
      X_APPROVAL_OBJECT_TYPE => l_Approval_Details_rec.APPROVAL_OBJECT_TYPE,
      X_APPROVAL_TYPE => l_Approval_Details_rec.APPROVAL_TYPE,
      X_APPROVAL_PRIORITY => l_Approval_Details_rec.APPROVAL_PRIORITY,
      X_APPROVAL_LIMIT_TO => l_Approval_Details_rec.APPROVAL_LIMIT_TO,
      X_APPROVAL_LIMIT_FROM => l_Approval_Details_rec.APPROVAL_LIMIT_FROM,
      X_SEEDED_FLAG => nvl(l_Approval_Details_rec.SEEDED_FLAG, 'N'),
      X_ACTIVE_FLAG => nvl(l_Approval_Details_rec.ACTIVE_FLAG, 'Y'),
      X_CURRENCY_CODE => l_Approval_Details_rec.CURRENCY_CODE,
      X_USER_COUNTRY_CODE => l_Approval_Details_rec.USER_COUNTRY_CODE,
      X_NAME => l_Approval_Details_rec.NAME,
      X_DESCRIPTION => l_Approval_Details_rec.DESCRIPTION,
      X_CREATION_DATE => sysdate,
      X_CREATED_BY => FND_GLOBAL.User_Id,
      X_LAST_UPDATE_DATE => sysdate,
      X_LAST_UPDATED_BY => FND_GLOBAL.User_Id,
      X_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
      ) ;

Line: 388

PROCEDURE Update_approval_details (
   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_approval_details_rec   IN  Approval_Details_Rec_Type
)
IS

   L_API_VERSION   CONSTANT NUMBER := 1.0;

Line: 403

   L_API_NAME    CONSTANT VARCHAR2(30) := 'Update_Approval_Details';

Line: 412

                SELECT 1 FROM   ams_approval_details
                   WHERE  approval_detail_id = x_id
                     AND object_version_number = ver;

Line: 418

    SAVEPOINT Update_approval_details;

Line: 469

	       p_validation_mode =>  JTF_PLSQL_API.g_update,
		  x_return_status   => l_return_status
	    );

Line: 494

	    OR seed_needs_update(l_approval_details_rec) = FND_API.g_true
	 THEN
   -------------------------- update --------------------
      IF (AMS_DEBUG_HIGH_ON) THEN

      AMS_Utility_PVT.debug_message (l_full_name || ': Update');

Line: 514

	 AMS_APPROVAL_DETAILS_PKG.UPDATE_ROW(
        X_APPROVAL_DETAIL_ID => l_Approval_Details_rec.approval_detail_id,
        X_START_DATE_ACTIVE => l_approval_details_rec.START_DATE_ACTIVE,
        X_END_DATE_ACTIVE => l_approval_details_rec.END_DATE_ACTIVE,
        X_OBJECT_VERSION_NUMBER => l_approval_details_rec.OBJECT_VERSION_NUMBER+1,
        --X_SECURITY_GROUP_ID => l_approval_details_rec.SECURITY_GROUP_ID,
        X_BUSINESS_GROUP_ID => l_approval_details_rec.BUSINESS_GROUP_ID,
        X_BUSINESS_UNIT_ID => l_approval_details_rec.BUSINESS_UNIT_ID,
        X_ORGANIZATION_ID => l_approval_details_rec.ORGANIZATION_ID,
        X_CUSTOM_SETUP_ID => l_approval_details_rec.CUSTOM_SETUP_ID,
        X_APPROVAL_OBJECT => l_approval_details_rec.APPROVAL_OBJECT,
        X_APPROVAL_OBJECT_TYPE => l_approval_details_rec.APPROVAL_OBJECT_TYPE,
        X_APPROVAL_TYPE => l_approval_details_rec.APPROVAL_TYPE,
        X_APPROVAL_PRIORITY => l_approval_details_rec.APPROVAL_PRIORITY,
        X_APPROVAL_LIMIT_TO => l_approval_details_rec.APPROVAL_LIMIT_TO,
        X_APPROVAL_LIMIT_FROM => l_approval_details_rec.APPROVAL_LIMIT_FROM,
        X_SEEDED_FLAG => l_approval_details_rec.SEEDED_FLAG,
        X_ACTIVE_FLAG => l_Approval_Details_rec.ACTIVE_FLAG,
        X_CURRENCY_CODE => l_Approval_Details_rec.CURRENCY_CODE,
	X_USER_COUNTRY_CODE => l_Approval_Details_rec.USER_COUNTRY_CODE,
        X_NAME  => l_approval_details_rec.NAME,
        X_DESCRIPTION   => l_approval_details_rec.DESCRIPTION,
        X_LAST_UPDATE_DATE => SYSDATE,
        X_LAST_UPDATED_BY => FND_GLOBAL.User_Id,
        X_LAST_UPDATE_LOGIN => FND_GLOBAL.Conc_Login_Id
        );

Line: 557

     ROLLBACK TO Update_approval_details;

Line: 566

     ROLLBACK TO Update_approval_details;

Line: 575

     ROLLBACK TO Update_approval_details;

Line: 586

END Update_approval_details;

Line: 604

PROCEDURE Delete_approval_details (
   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_approval_detail_id          IN  NUMBER,
   p_object_version    IN  NUMBER
) IS

   CURSOR c_approval_details IS
   SELECT   *
   FROM  ams_approval_details_vl
   WHERE approval_detail_id = p_approval_detail_id;

Line: 630

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

Line: 647

    SAVEPOINT Delete_approval_details;

Line: 668

    AMS_Utility_PVT.debug_message (l_full_name || ': Delete');

Line: 673

       AMS_APPROVAL_DETAILS_PKG.DELETE_ROW (p_approval_detail_id);

Line: 697

     ROLLBACK TO Delete_approval_details;

Line: 706

     ROLLBACK TO Delete_approval_details;

Line: 715

	ROLLBACK TO Delete_approval_details;

Line: 726

END Delete_approval_details;

Line: 1265

   SELECT   *
   FROM     ams_approval_details_vl
   WHERE    approval_detail_id = p_approval_details_rec.approval_detail_id;

Line: 1405

	        select 1 into l_count
		   from AMS_APPROVAL_DETAILS_VL
		   where approval_detail_id =p_approval_details_rec.approval_detail_id
		   and name =  p_approval_details_rec.name
		   and description =  p_approval_details_rec.description
		   and start_date_active = p_approval_details_rec.start_date_active
		   and end_date_active = p_approval_details_rec.end_Date_active
		  -- and security_group_id = p_approval_details_rec.security_group_id
		   and business_group_id = p_approval_details_rec.business_group_id
		   and user_country_code = p_approval_details_rec.user_country_code
		   and organization_id = p_approval_details_rec.organization_id
		   and custom_setup_id = p_approval_details_rec.custom_setup_id
		   and approval_object = p_approval_details_rec.approval_object
		   and approval_object_type = p_approval_details_rec.approval_object_type
		   and approval_type = p_approval_details_rec.approval_type
		   and approval_priority = p_approval_details_rec.approval_priority
		   and approval_limit_to = p_approval_details_rec.approval_limit_to
		   and approval_limit_from = p_approval_details_rec.approval_limit_from
		   and seeded_flag = 'Y'
		   and active_flag = 'Y'
		   and currency_code = p_approval_details_rec.currency_code;

Line: 1432

		   select 1 into l_count
		   from AMS_APPROVAL_DETAILS_VL
		   where approval_detail_id =p_approval_details_rec.approval_detail_id
		   and name =  p_approval_details_rec.name
		   and description =  p_approval_details_rec.description
		   and start_date_active = p_approval_details_rec.start_date_active
		   and end_date_active = p_approval_details_rec.end_Date_active
		   -- and security_group_id = p_approval_details_rec.security_group_id
		   and business_group_id = p_approval_details_rec.business_group_id
		   and user_country_code = p_approval_details_rec.user_country_code
		   and organization_id = p_approval_details_rec.organization_id
		   and custom_setup_id = p_approval_details_rec.custom_setup_id
		   and approval_object = p_approval_details_rec.approval_object
		   and approval_object_type = p_approval_details_rec.approval_object_type
		   and approval_type = p_approval_details_rec.approval_type
		   and approval_priority = p_approval_details_rec.approval_priority
		   and approval_limit_to = p_approval_details_rec.approval_limit_to
		   and approval_limit_from = p_approval_details_rec.approval_limit_from
		   and seeded_flag = 'Y'
		   and active_flag = 'Y'
		   and currency_code = p_approval_details_rec.currency_code;

Line: 1460

	      select 1 into l_count
		 from AMS_APPROVAL_DETAILS_VL
		 where approval_detail_id =p_approval_details_rec.approval_detail_id
		 and name =  p_approval_details_rec.name
		 and description =  p_approval_details_rec.description
		 and start_date_active = p_approval_details_rec.start_date_active
		 and end_date_active = p_approval_details_rec.end_Date_active
		-- and security_group_id = p_approval_details_rec.security_group_id
		 and business_group_id = p_approval_details_rec.business_group_id
		 and user_country_code = p_approval_details_rec.user_country_code
		 and organization_id = p_approval_details_rec.organization_id
		 and custom_setup_id = p_approval_details_rec.custom_setup_id
		 and approval_object = p_approval_details_rec.approval_object
		 and approval_object_type =p_approval_details_rec.approval_object_type
		 and approval_type = p_approval_details_rec.approval_type
		 and approval_priority = p_approval_details_rec.approval_priority
		 and approval_limit_to = p_approval_details_rec.approval_limit_to
		 and approval_limit_from = p_approval_details_rec.approval_limit_from
		 and seeded_flag = 'Y'
		 and active_flag = 'Y'
		 and currency_code = p_approval_details_rec.currency_code;

Line: 1498

FUNCTION seed_needs_update(
	p_approval_details_rec         in  approval_details_Rec_Type
)
RETURN VARCHAR2
IS
  l_count NUMBER := 0;

Line: 1507

	select 1 into l_count
	from AMS_APPROVAL_DETAILS
	where approval_detail_id = p_approval_details_rec.approval_detail_id
	and   seeded_flag = 'Y';

Line: 1517

      RETURN FND_API.g_true;  -- needs update

Line: 1519

      RETURN FND_API.g_false;  -- doesnt need update

Line: 1521

END seed_needs_update;

Line: 1561

	SELECT   *
	FROM     ams_approval_details_vl
	WHERE    approval_detail_id = id_in;

Line: 1566

	SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_APPROVAL_DETAILS_VL
							   where name = name_in);

Line: 1570

	SELECT 1 FROM DUAL WHERE EXISTS (select 1 from AMS_APPROVAL_DETAILS_VL
							   where name = name_in
								and approval_detail_id = id_in);

Line: 1577

	SELECT '1' FROM DUAL WHERE EXISTS (select 1 from AMS_APPROVAL_DETAILS_VL
                                         where name = name_in
					 and approval_detail_id <> id_in);

Line: 1715

      SELECT count(1)
      FROM hr_organization_units  hou
      WHERE hou.organization_id   = cur_organization_id
      --AND hou.business_group_id = cur_business_group_id
      AND hou.type              = nvl(cur_org_type,hou.type)
      AND sysdate between hou.date_from and nvl(hou.date_to,sysdate +1 );

Line: 1723

	 SELECT 1 FROM   dual
	 WHERE EXISTS (SELECT 1 FROM   ams_categories_b
		                WHERE  enabled_flag = 'Y'
				AND arc_category_created_for = 'FUND'
				AND category_id = id_in);

Line: 1730

   SELECT 1 from dual
   where EXISTS (select 1 FROM ams_custom_setups_b
			   WHERE enabled_flag = 'Y'
			   AND object_type IN ('RCAM','ECAM'));

Line: 1736

   SELECT 1 from dual
   where EXISTS (select 1 FROM ams_custom_setups_b
			   WHERE enabled_flag = 'Y'
			   AND object_type = p_lookup_code);

Line: 1974

         SELECT approval_limit_from, approval_limit_to
	 FROM ams_approval_details
         WHERE  approval_detail_id = l_approval_id;

Line: 2071

          select approval_detail_id, name
            from AMS_APPROVAL_DETAILS_VL --  Perf Bug Fix. Was previously using _V
            where nvl(start_date_active,l_miss_date)  = nvl(p_approval_details_rec.start_date_active, l_miss_date)
            and nvl(end_date_active,l_miss_date)  = nvl(p_approval_details_rec.end_date_active, l_miss_date)
            and nvl(business_unit_id,l_miss_num)  = nvl(p_approval_details_rec.business_unit_id, l_miss_num)
            and nvl(user_country_code,l_miss_char) = nvl(p_approval_details_rec.user_country_code, l_miss_char)
            and nvl(currency_code,l_miss_char) = nvl(p_approval_details_rec.currency_code, l_miss_char)
            and nvl(organization_id,l_miss_num)  = nvl(p_approval_details_rec.organization_id, l_miss_num)
            and nvl(custom_setup_id,l_miss_num)  = nvl(p_approval_details_rec.custom_setup_id, l_miss_num)
            and approval_object = p_approval_details_rec.approval_object
            and nvl(approval_object_type,l_miss_char)  = nvl(p_approval_details_rec.approval_object_type, l_miss_char)
            and nvl(approval_type,l_miss_char)  = nvl(p_approval_details_rec.approval_type, l_miss_char)
            and nvl(approval_priority,l_miss_char)  = nvl(p_approval_details_rec.approval_priority, l_miss_char)
            -- Bug 3068835 both lines were using limit_to
            and nvl(approval_limit_from,l_miss_num)  = nvl(p_approval_details_rec.approval_limit_from, l_miss_num)
            and nvl(approval_limit_to,l_miss_num)  = nvl(p_approval_details_rec.approval_limit_to, l_miss_num);

Line: 2143

         SELECT TRUNC(start_date_active) , TRUNC(end_date_active) FROM ams_approval_details
                 WHERE approval_detail_id = p_approval_details_rec.approval_detail_id;

Line: 2147

         SELECT approval_detail_id, TRUNC(start_date_active), TRUNC(end_date_active) FROM ams_approval_details
                where nvl(business_unit_id,l_miss_num)  = nvl(p_approval_details_rec.business_unit_id, l_miss_num)
                and nvl(user_country_code,l_miss_char) = nvl(p_approval_details_rec.user_country_code, l_miss_char)
                and nvl(currency_code,l_miss_char) = nvl(p_approval_details_rec.currency_code, l_miss_char)
                and nvl(organization_id,l_miss_num)  = nvl(p_approval_details_rec.organization_id, l_miss_num)
                and nvl(custom_setup_id,l_miss_num)  = nvl(p_approval_details_rec.custom_setup_id, l_miss_num)
                and approval_object  = p_approval_details_rec.approval_object
                and nvl(approval_object_type,l_miss_char)  = nvl(p_approval_details_rec.approval_object_type, l_miss_char)
                and approval_type  = p_approval_details_rec.approval_type
                and nvl(approval_priority,l_miss_char)  = nvl(p_approval_details_rec.approval_priority, l_miss_char)
                AND approval_detail_id NOT IN(nvl(p_approval_details_rec.approval_detail_id,0));

Line: 2162

   /* If the Rule is already active i.e. start_date is < SYSDATE, you cannot update the start_date */
	IF p_validation_mode = JTF_PLSQL_API.g_update THEN

     OPEN c_approval_dates;

Line: 2180

               Ams_Utility_Pvt.debug_message('You cannot update the Approval Rule start date as it is already active');

Line: 2184

                Fnd_Message.set_name('AMS', 'AMS_APRD_START_DATE_NO_UPDATE');