APPS.AHL_DI_SUBSCRIPTION_PUB SQL Statements

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

Line: 28

          SELECT lookup_code INTO x_lookup_code
           FROM FND_LOOKUP_VALUES_VL
          WHERE lookup_type = p_lookup_type
            AND lookup_code = p_lookup_code
            AND sysdate between start_date_active
            AND nvl(end_date_active,sysdate);

Line: 38

          SELECT lookup_code INTO x_lookup_code
           FROM FND_LOOKUP_VALUES_VL
          WHERE lookup_type = p_lookup_type
            AND meaning     = p_meaning
            AND sysdate between start_date_active
            AND nvl(end_date_active,sysdate);

Line: 78

 SELECT party_id
 FROM hz_parties
 WHERE upper(party_name) = upper(c_party_name);

Line: 85

 SELECT party_id
 FROM hz_parties
 WHERE party_id = c_party_id;

Line: 93

   SELECT distinct segment1
   FROM PO_HEADERS_V
   WHERE nvl(approved_flag, 'N')='Y' and upper(segment1) = upper(c_ponumber);

Line: 100

 SELECT vendor_id
 FROM po_vendors
 WHERE upper(vendor_name) = upper(c_vendor_name);

Line: 109

  SELECT person_id
  FROM per_people_f ppf, per_person_types ppt
  WHERE upper(ppf.FULL_NAME) = upper(c_party_name)
    AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
    AND nvl(ppf.current_employee_flag,'x') = 'Y'
    AND ppf.person_type_id = ppt.person_type_id
    AND ppt.system_person_type ='EMP';

Line: 118

  SELECT person_id
  FROM per_all_people_f pap, per_person_types ppt
  WHERE trim(upper(FULL_NAME)) = upper(c_party_name)
  AND trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
  AND nvl(pap.current_employee_flag,'x') = 'Y'
  AND pap.person_type_id = ppt.person_type_id
  AND ppt.system_person_type ='EMP'
  AND decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', pap.person_id,
             pap.person_type_id, pap.employee_number,pap.applicant_number)) = 'TRUE'
  AND decode(hr_general.get_xbg_profile,'Y',pap.business_group_id , hr_general.get_business_group_id)
            = pap.business_group_id;

Line: 427

        l_subscription_tbl(i).delete_flag             := p_x_subscription_tbl(i).delete_flag;

Line: 692

/* description :  Update the existing subscription record*/
/*                                                       */
/*------------------------------------------------------ */
PROCEDURE MODIFY_SUBSCRIPTION
(
 p_api_version                IN      NUMBER    :=  1.0               ,
 p_init_msg_list              IN      VARCHAR2  := FND_API.G_TRUE     ,
 p_commit                     IN      VARCHAR2  := FND_API.G_FALSE    ,
 p_validate_only              IN      VARCHAR2  := FND_API.G_TRUE     ,
 p_validation_level           IN      NUMBER    := FND_API.G_VALID_LEVEL_FULL,
 p_x_subscription_tbl         IN  OUT NOCOPY subscription_tbl         ,
 p_module_type                IN      VARCHAR2                        ,
 x_return_status                  OUT NOCOPY VARCHAR2                        ,
 x_msg_count                      OUT NOCOPY NUMBER                          ,
 x_msg_data                       OUT NOCOPY VARCHAR2
)
IS

 -- Get the party id from hz parties
 CURSOR for_party_name(c_party_name  IN VARCHAR2)
 IS
 SELECT party_id
 FROM hz_parties
 WHERE upper(party_name) = upper(c_party_name);

Line: 720

 SELECT party_id
 FROM hz_parties
 WHERE party_id = c_party_id;

Line: 728

    SELECT distinct segment1
   FROM PO_HEADERS_V
   WHERE nvl(approved_flag, 'N')='Y' and upper(segment1) = upper(c_ponumber);

Line: 735

 SELECT vendor_id
 FROM po_vendors
 WHERE upper(vendor_name) = upper(c_vendor_name);

Line: 744

 SELECT person_id
 FROM per_people_f ppf, per_person_types ppt
 WHERE upper(ppf.first_name||' '||ppf.last_name) = upper(c_party_name)
   AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
   AND nvl(ppf.current_employee_flag,'x') = 'Y'
   AND ppf.person_type_id = ppt.person_type_id
   AND ppt.system_person_type ='EMP';

Line: 753

 SELECT person_id
 FROM per_all_people_f pap, per_person_types ppt
 WHERE trim(upper(FULL_NAME)) = upper(c_party_name)
 AND trunc(sysdate) between pap.effective_start_date and pap.effective_end_date
 AND nvl(pap.current_employee_flag,'x') = 'Y'
 AND pap.person_type_id = ppt.person_type_id
 AND ppt.system_person_type ='EMP'
 AND decode(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', pap.person_id,
            pap.person_type_id, pap.employee_number,pap.applicant_number)) = 'TRUE'
 AND decode(hr_general.get_xbg_profile,'Y',pap.business_group_id , hr_general.get_business_group_id)
            = pap.business_group_id;

Line: 768

 SELECT person_id
 FROM per_people_f ppf, per_person_types ppt
 WHERE upper(FULL_NAME) = upper(c_party_name)
   AND ppf.person_id = c_party_id
   AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
   AND nvl(ppf.current_employee_flag,'x') = 'Y'
   AND ppf.person_type_id = ppt.person_type_id
   AND ppt.system_person_type ='EMP';

Line: 891

           IF p_x_subscription_tbl(i).delete_flag = 'N'
           THEN
             FND_MESSAGE.SET_NAME('AHL','AHL_DI_REQ_PTY_ID_NULL');

Line: 936

           IF p_x_subscription_tbl(i).delete_flag = 'N'
           THEN
             FND_MESSAGE.SET_NAME('AHL','AHL_DI_SUBSCRIPTION_REQD');

Line: 1058

           IF p_x_subscription_tbl(i).delete_flag = 'N'
	   THEN
	     FND_MESSAGE.SET_NAME('AHL','AHL_DI_PO_NUM_NOT_EXISTS');

Line: 1095

        l_subscription_tbl(i).delete_flag             := p_x_subscription_tbl(i).delete_flag;