APPS.AMS_REGISTRANTS_PVT SQL Statements

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

Line: 184

SELECT subject_id FROM hz_relationships
WHERE relationship_id = id_in
AND subject_type = 'PERSON';

Line: 190

SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_org_party_id
AND location_id = x_location_id;

Line: 195

SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_per_party_id
AND location_id = x_location_id;

Line: 200

SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_party_rel_party_id
  AND location_id = x_location_id;

Line: 206

SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type          = 'PHONE'
AND phone_line_type             = 'GEN'
AND owner_table_name            = 'HZ_PARTIES'
AND owner_table_id              = party_id_in
AND phone_number                = phone_number
AND NVL(phone_country_code,'x') = NVL(country_code,'x')
AND NVL(phone_area_code,'x')    = NVL(area_code,'x')
AND NVL(phone_extension,'x')    = NVL(extension,'x');

Line: 218

   SELECT owner_table_id
   FROM hz_contact_points
   WHERE contact_point_type   = 'EMAIL'
     AND owner_table_name     = 'HZ_PARTIES'
     AND upper(email_address) = upper(email_address_in);

Line: 225

select 'Y' from hz_parties
where customer_key = l_party_key
and party_type   = 'PERSON'
and party_id = id_in;

Line: 417

SELECT party_id FROM hz_relationships
WHERE object_id = x_org_party_id
AND subject_id = x_per_party_id
AND relationship_code = 'CONTACT_OF';

Line: 423

SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_org_party_id
AND location_id = x_location_id;

Line: 428

SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_per_party_id
AND location_id = x_location_id;

Line: 433

SELECT party_site_id FROM hz_party_sites
WHERE party_id = x_party_rel_party_id
AND location_id = x_location_id;

Line: 438

SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type          = 'PHONE'
AND phone_line_type             = 'GEN'
AND owner_table_name            = 'HZ_PARTIES'
AND owner_table_id              = x_hz_party_id
AND phone_number                = x_phone_number
AND NVL(phone_country_code,'x') = NVL(x_phone_country_code,'x')
AND NVL(phone_area_code,'x')    = NVL(x_phone_area_code,'x')
AND NVL(phone_extension,'x')    = NVL(x_phone_extention,'x');

Line: 450

SELECT 'Y' FROM hz_contact_points
WHERE contact_point_type          = 'EMAIL'
AND owner_table_name            = 'HZ_PARTIES'
AND owner_table_id              = x_hz_party_id
AND email_address               = x_email_address;

Line: 634

            select hz_party_number_s.nextval into x_party_number from dual;

Line: 636

         select hz_parties_s.nextval into x_org_party_id from dual;

Line: 741

               select hz_party_number_s.nextval into x_party_number from dual;

Line: 743

            select hz_parties_s.nextval into x_per_party_id from dual;

Line: 815

            Select hz_org_contacts_s.nextval into x_org_contact_id from dual;

Line: 817

               select hz_party_number_s.nextval into x_party_number from dual;

Line: 833

               select hz_contact_numbers_s.nextval into x_contact_number from dual;

Line: 904

            select hr_locations_s.nextval into x_location_id from dual;

Line: 956

         select hz_party_sites_s.nextval into x_party_site_id from dual;

Line: 958

            select hz_party_site_number_s.nextval into x_party_site_number from dual;

Line: 1007

            select hz_party_site_uses_s.nextval into x_party_site_use_id from dual;

Line: 1069

               select hz_party_sites_s.nextval into x_party_site_id from dual;

Line: 1071

                  select hz_party_site_number_s.nextval into x_party_site_number from dual;

Line: 1118

                  select hz_party_site_uses_s.nextval into x_party_site_use_id from dual;

Line: 1166

         SELECT  hz_contact_points_s.nextval into x_contact_point_id from dual;

Line: 1233

         SELECT  hz_contact_points_s.nextval into x_contact_point_id from dual;

Line: 1399

            select hz_party_number_s.nextval into x_party_number from dual;

Line: 1401

         select hz_parties_s.nextval into x_per_party_id from dual;

Line: 1472

            select hr_locations_s.nextval into x_location_id from dual;

Line: 1532

         select hz_party_sites_s.nextval into x_party_site_id from dual;

Line: 1534

            select hz_party_site_number_s.nextval into x_party_site_number from dual;

Line: 1618

            select hz_party_site_uses_s.nextval into x_party_site_use_id from dual;

Line: 1660

         SELECT  hz_contact_points_s.nextval into x_contact_point_id from dual;

Line: 1731

         SELECT  hz_contact_points_s.nextval into x_contact_point_id from dual;

Line: 1842

   select max(p.party_id)
   from hz_contact_points cp,
        hz_parties p
   where p.customer_key = l_party_key
     and p.party_type = 'PERSON'
     and cp.owner_table_id = p.party_id
     and cp.owner_table_name = 'HZ_PARTIES'
     and cp.email_address = p_email_address
     and cp.primary_flag = 'Y';

Line: 1853

   select max(p.party_id)
   from hz_contact_points cp,
        hz_parties p
   where p.customer_key = l_party_key
     and p.party_type = 'PERSON'
     and cp.owner_table_id = p.party_id
     and cp.owner_table_name = 'HZ_PARTIES'
     and cp.primary_flag = 'Y'
     and cp.phone_number = p_ph_number
     and nvl(cp.phone_country_code, nvl(p_ph_country_code, 'x')) = nvl(p_ph_country_code, 'x')
     and nvl(cp.phone_area_code, nvl(p_ph_area_code, 'x')) = nvl(p_ph_area_code, 'x');

Line: 1866

   select max(psite.party_id)
   from hz_party_sites psite,
        hz_locations loc,
        hz_parties party
   where  psite.location_id = loc.location_id
     and  loc.address1      = p_address1
     and  loc.country       = p_country
     and  party.customer_key = l_party_key
     and  psite.party_id      = party.party_id;

Line: 1877

   select 'Y'
   from hz_parties
   where customer_key = l_party_key
     and party_type   = 'PERSON';

Line: 1983

       select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
        hz_parties party
    where  psite.location_id = loc.location_id
           and  loc.address1      = p_address1
     and  loc.country       = p_country
          and  party.customer_key = l_party_key
          and  psite.party_id      = party.party_id;

Line: 1992

        select max(psite.party_id) from hz_party_sites psite, hz_locations loc,
        hz_parties party
         where  psite.location_id = loc.location_id
          and  loc.country       = p_country
          and  party.customer_key = l_party_key
          and  psite.party_id      = party.party_id;

Line: 2000

       select 'Y' from hz_parties
       where customer_key = l_party_key
         and party_type   = 'ORGANIZATION';

Line: 2005

       select 'Y' from hz_parties
       where customer_key = l_party_key
         and party_type   = 'PERSON';

Line: 2167

       select 'Y' from hz_parties
       where customer_key = l_party_key
         and party_type   = 'PERSON';

Line: 2172

       select max(per.party_id) from
       hz_parties org,
       hz_parties per,
       hz_relationships rel,
       hz_contact_points cpoint
       where org.party_id           = p_org_party_id
         and org.party_type         = 'ORGANIZATION'
         and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
         and rel.SUBJECT_TYPE       = 'PERSON'
         and rel.OBJECT_TABLE_NAME  = 'HZ_PARTIES'
         and rel.RELATIONSHIP_CODE  = 'CONTACT_OF'
         and rel.OBJECT_ID          = org.party_id
         and rel.SUBJECT_ID         = per.PARTY_ID
         and per.customer_key       = l_party_key
         and cpoint.owner_table_id  = rel.party_id
         and cpoint.owner_table_name = 'HZ_PARTIES'
         and cpoint.contact_point_type = 'EMAIL'
         and cpoint.email_address    = p_email_address
         and cpoint.status           = 'A';

Line: 2194

       select max(per.party_id) from
       hz_parties org,
       hz_parties per,
       hz_relationships rel,
       hz_contact_points cpoint,
       hz_contact_points cpoint1
       where org.party_id           = p_org_party_id
         and org.party_type         = 'ORGANIZATION'
         and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
         and rel.SUBJECT_TYPE       = 'PERSON'
         and rel.OBJECT_TABLE_NAME  = 'HZ_PARTIES'
         and rel.RELATIONSHIP_CODE  = 'CONTACT_OF'
         and rel.OBJECT_ID          = org.party_id
         and rel.SUBJECT_ID         = per.PARTY_ID
         and per.customer_key       = l_party_key
         and cpoint.owner_table_id  = rel.party_id
         and cpoint.owner_table_name = 'HZ_PARTIES'
         and cpoint.contact_point_type = 'EMAIL'
         and cpoint.email_address    = p_email_address
         and cpoint.status           = 'A'
         and cpoint1.owner_table_id  = rel.party_id
         and cpoint1.owner_table_name = 'HZ_PARTIES'
         and cpoint1.contact_point_type = 'PHONE'
         and cpoint1.phone_area_code||'-'||cpoint1.phone_number||'-'||cpoint1.phone_extension  =
             p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
         and (cpoint1.phone_line_type<>'FAX' or cpoint1.phone_line_type is null)
         and cpoint1.status           = 'A';

Line: 2224

       select max(per.party_id) from
       hz_parties org,
       hz_parties per,
       hz_relationships rel,
       hz_contact_points cpoint
       where org.party_id           = p_org_party_id
         and org.party_type         = 'ORGANIZATION'
         and rel.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
         and rel.SUBJECT_TYPE       = 'PERSON'
         and rel.OBJECT_TABLE_NAME  = 'HZ_PARTIES'
         and rel.RELATIONSHIP_CODE  = 'CONTACT_OF'
         and rel.OBJECT_ID          = org.party_id
         and rel.SUBJECT_ID         = per.PARTY_ID
         and per.customer_key       = l_party_key
         and cpoint.owner_table_id  = rel.party_id
         and cpoint.owner_table_name = 'HZ_PARTIES'
         and cpoint.contact_point_type = 'PHONE'
         and cpoint.phone_area_code||'-'||cpoint.phone_number||'-'||cpoint.phone_extension  =
             p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension
         and (cpoint.phone_line_type<>'FAX' or cpoint.phone_line_type is null)
         and cpoint.status           = 'A';

Line: 2314

       select max(loc.location_id)
       from  hz_party_sites ps, hz_locations loc
       where ps.party_id          = p_party_id
         and ps.location_id       = loc.location_id
         and loc.address_key      = l_address_key
         and loc.country          = p_country
         and nvl(loc.city,p_city) = p_city;

Line: 2323

       select max(loc.location_id)
       from   hz_locations loc
       where  loc.address_key     = l_address_key
         and loc.country          = p_country
         and nvl(loc.city,p_city) = p_city;

Line: 2390

    SELECT  object_id, subject_id
      FROM  hz_relationships
     WHERE  party_id = p_id
     -- dbiswas 10 Jun 2003 fix for bug 2949603
      -- AND  directional_flag = 'F';

Line: 2738

   SELECT event_offer_id
   FROM   ams_event_offers_all_b
   WHERE  source_code = p_source_code ;

Line: 2744

   SELECT related_event_id
   FROM   ams_campaign_schedules_b
   WHERE  source_code = p_source_code ;