APPS.OE_CREATE_ACCOUNT_INFO SQL Statements

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

Line: 63

  SELECT status,party_id
    FROM hz_cust_accounts
   WHERE cust_account_id = in_cust_account_id;

Line: 68

  SELECT cust_account_id,party_id,status
    FROM hz_cust_accounts
   WHERE account_number=in_cust_account_number
     AND status='A';

Line: 74

  SELECT party_id
    FROM hz_cust_accounts
   WHERE cust_account_id=in_cust_account_id
     AND status='A';

Line: 80

  SELECT cust_account_id
    FROM hz_cust_acct_relate
   WHERE related_cust_account_id=in_cust_account_id
     AND status='A';

Line: 680

	       select cust_account_id
		  into matched_cust
		  from hz_cust_acct_relate
		  where related_cust_account_id=l_cust_account_id
		  and cust_account_id=p_site_tbl(i).p_cust_account_id
                  AND status ='A'  --added for bug 11699930
	          and rownum = 1;  --added for bug# 14113693

Line: 1209

  SELECT site_use.site_use_type,site_use.party_site_id,site_use.primary_per_type
    FROM hz_party_site_uses site_use,
         hz_party_sites site
   WHERE party_site_use_id = in_party_site_use_id
     AND site.party_site_id = site_use.party_site_id
     AND site.status = 'A'
     AND site_use.status='A';

Line: 1218

  SELECT party_id,status
    FROM hz_party_sites
   WHERE party_site_id = in_party_site_id;

Line: 1226

  SELECT s.cust_acct_site_id,u.site_use_id,u.primary_flag
    FROM hz_cust_acct_sites s,
         hz_cust_site_uses_all u
   WHERE s.cust_account_id = in_cust_account_id
     AND s.party_site_id = in_party_site_id
     AND s.status(+) = 'A'
     AND u.cust_acct_site_id(+) = s.cust_acct_site_id
     AND u.site_use_code(+) = in_site_use_code
     AND u.status(+) = 'A';

Line: 1238

  SELECT uses.cust_acct_site_id,
         uses.status,
         uses.primary_flag,
         cust_site.cust_account_id
    FROM hz_cust_site_uses uses, hz_cust_acct_sites_all cust_site
   WHERE site_use_id = in_site_Use_id
     AND site_use_code = in_site_use_code
     AND cust_site.cust_acct_site_id = uses.cust_acct_site_id;

Line: 1248

        SELECT a.cust_account_id
        FROM hz_cust_acct_sites_all a,
             hz_cust_site_uses b
        WHERE b.site_use_id=l_site_use_id
        and a.cust_acct_site_id=b.cust_acct_site_id
        and b.status = 'A';

Line: 1257

   SELECT party_site_id
          FROM hz_party_sites
	  where party_id = in_party_id
	  and status='A';

Line: 1264

    SELECT party_site_use_id
          FROM hz_party_site_uses
	  where party_site_id=in_party_site_id and
	  site_use_type=in_site_use_type and
	  primary_per_type='Y'
	  and status='A';

Line: 1275

  SELECT site_use.party_site_use_id
    FROM hz_party_site_uses site_use,
         hz_party_sites site
   WHERE party_site_use_id = site_use.party_site_use_id
     AND site.party_site_id = in_party_site_id
     AND site_use.site_use_type =in_site_use_type
     AND site.status = 'A'
     AND site_use.status='A';

Line: 2106

	oe_debug_pub.add('Site use code selected for end customer is'||l_site_use_code);

Line: 2899

  SELECT cust_account_id
    FROM hz_cust_account_roles
   where cust_account_role_id = in_cust_account_role_id;

Line: 2904

  SELECT status,
         role_type,
         cust_account_id,
         cust_acct_site_id
    FROM hz_cust_account_roles
   where cust_account_role_id = in_cust_account_role_id;

Line: 2912

  SELECT cust_acct_site_id,
         status,
         role_type,
         cust_account_id
    FROM hz_cust_account_roles
   where cust_account_role_id = in_cust_account_role_id;

Line: 2921

  SELECT role.cust_account_role_id,
         role.cust_acct_site_id
    FROM hz_org_contacts org,
         hz_relationships rel,
         hz_cust_accounts acct,
         hz_cust_account_roles role
   WHERE org.org_Contact_id = in_org_contact_id
     AND org.party_relationship_id = rel.relationship_id
     AND rel.object_id=acct.party_id
     AND rel.subject_table_name='HZ_PARTIES'
     AND rel.object_table_name='HZ_PARTIES'
     AND acct.cust_account_id = in_cust_account_id
     and role.cust_account_id = acct.cust_account_id
     and role.role_type ='CONTACT'
     and role.party_id = rel.party_id
     and acct.status = 'A'
     and role.status = 'A'
     and rel.status = 'A';

Line: 2942

  SELECT cust_account_id,account_number
    FROM hz_cust_accounts
   WHERE party_id = l_Party_Id
     and status = 'A';

Line: 2948

  SELECT rel.party_id
    FROM hz_org_contacts org,
         hz_relationships rel
   WHERE org.org_contact_id = in_org_contact_id
     AND rel.status = 'A'
     AND rel.relationship_id = org.party_relationship_id
     AND (rel.object_id=in_party_id OR rel.subject_id=in_party_id)
     AND rel.subject_table_name='HZ_PARTIES'
     AND rel.object_table_name='HZ_PARTIES';

Line: 3019

  p_contact_tbl.DELETE;

Line: 3075

  p_contact_tbl.DELETE;

Line: 3571

        SELECT cust_account_id,
               account_number
        FROM hz_cust_accounts
        WHERE party_id = l_Party_Id
        and status = 'A';

Line: 3578

        select party_type
        from hz_parties
        where party_id = l_party_id;

Line: 3583

        select party_id,party_type
        from hz_parties
        where party_id = p_party_number;

Line: 3615

   p_account_tbl.DELETE;

Line: 4065

        SELECT cust_account_id,
               account_number
        FROM hz_cust_accounts
        WHERE party_id = l_Party_Id
        and status = 'A';

Line: 4072

        select party_type
        from hz_parties
        where party_id = l_party_id;

Line: 4077

        select party_id,party_type
        from hz_parties
        where party_id = p_party_number;

Line: 4092

  p_account_tbl.DELETE;

Line: 4410

   select cust_account_id
      --into p_cust_account_id -- commented for bug 3449269
      from hz_cust_accounts
      where party_id=l_party_id
      and status='A';

Line: 4475

	 select s.cust_account_id
	    into p_cust_account_id
	    from hz_cust_acct_sites_all s,
	    hz_cust_site_uses u
	    where s.cust_acct_site_id=u.cust_acct_site_id
	    and u.site_use_id=p_site_use_id;

Line: 4492

	 select party_id
	    into p_party_id
	    from hz_cust_accounts
	    where cust_account_id=p_cust_account_id;

Line: 4555

	 select party_id
	     into p_party_id
	     from hz_cust_accounts
	    where cust_account_id = p_cust_account_id;

Line: 4607

	 select party_id
	    into p_party_id
	    from hz_parties
	    where party_number=p_party_number;

Line: 4652

	 select party_site_id
	    into p_party_site_id
	    from hz_party_site_uses
	    where party_site_use_id=p_party_site_use_id;

Line: 4696

	 select party_id
	    into p_party_id
	    from hz_party_sites
	    where party_site_id=p_party_site_id;

Line: 4740

	 select u.site_use_id
	    into p_site_use_id
	    from hz_cust_acct_sites s,hz_cust_site_uses_all u
	    where s.party_site_id=p_party_site_id
	    and s.cust_acct_site_id= u.cust_acct_site_id;

Line: 4783

	  select distinct cust_account_id
	     into p_cust_account_id
	     from hz_cust_acct_sites cs
	     where party_site_id=p_party_site_id;

Line: 4834

	 select distinct cust_account_id
	     into p_cust_account_id
	     from hz_cust_acct_sites
	     where cust_acct_site_id=p_site_id;

Line: 5291

	 select party_site_id
	    into p_site_id
	    from hz_party_site_uses
	    where party_site_use_id=p_site_use_id
	    and status='A';

Line: 5339

	 select s.party_id
	    into p_party_id
	    from hz_party_sites s
	    where s.party_site_id=p_site_id
	    and s.status='A';

Line: 5482

	    select a.party_id
	       into p_party_id
	       from hz_cust_accounts a,
	       hz_cust_acct_sites s,
	       hz_cust_site_uses_all u
	       where u.site_use_id=p_account_site_use_id
	       and u.cust_acct_site_id=s.cust_acct_site_id
	       and s.cust_account_id=a.cust_account_id;

Line: 5568

	      select s.party_id
		 into p_party_id
		 from hz_party_sites s
		 where s.party_site_id=p_site_id
		 and s.status='A';

Line: 5659

	SELECT party_id
	  INTO l_id
	  FROM hz_parties party
	 WHERE party.party_number = p_party_number
	       and status='A';

Line: 5670

	SELECT  party.party_id
	  INTO l_id
	  FROM HZ_PARTIES Party
	 WHERE party.party_name = p_party_name
	       and status='A';

Line: 5739

     SELECT org_contact.org_contact_id
       FROM hz_parties party,
	    hz_relationships rel,
	    hz_org_contacts org_contact,
	    ar_lookups arl
      WHERE rel.object_id=in_party_id
	AND rel.relationship_id=org_contact.party_relationship_id
	AND rel.party_id=rel.subject_id
	AND rel.directional_flag='Y'
	AND party.party_type='PERSON'
	AND party.person_last_name || decode(party.person_first_name, null, null, ', '||
					     party.person_first_name) || decode(arl.meaning, null, null, ' '||arl.meaning) = in_org_contact_name
	AND arl.lookup_code(+)=org_contact.title
	AND arl.lookup_type(+)='CONTACT_TITLE';

Line: 5824

     SELECT site.party_site_id
       FROM hz_locations loc,
	    hz_party_sites site
      WHERE site.location_id=loc.location_id
	and site.party_id=in_sold_to_party_id
	and loc.address1  = p_site_address1
	and nvl( loc.address2, fnd_api.g_miss_char) =
	    nvl( p_site_address2, fnd_api.g_miss_char)
	and nvl( loc.address3, fnd_api.g_miss_char) =
	    nvl( p_site_address3, fnd_api.g_miss_char)
	and nvl( loc.address4, fnd_api.g_miss_char) =
	    nvl( p_site_address4, fnd_api.g_miss_char)
	and nvl( loc.city, fnd_api.g_miss_char) =
	    nvl( p_site_city, fnd_api.g_miss_char)
	and nvl( loc.state, fnd_api.g_miss_char) =
	    nvl( p_site_state, fnd_api.g_miss_char)
	and nvl( loc.postal_code, fnd_api.g_miss_char) =
	    nvl( p_site_postal_code, fnd_api.g_miss_char)
	and nvl( loc.country, fnd_api.g_miss_char) =
	    nvl( p_site_country, fnd_api.g_miss_char);

Line: 5848

     SELECT site.party_site_id
       FROM HZ_PARTY_SITES  	        SITE,
	    HZ_LOCATIONS	        LOC
      WHERE site.location_id=loc.location_id
	and site.status='A'
	and loc.ADDRESS1  = p_site_address1
	AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
	    nvl( p_site_address2, fnd_api.g_miss_char)
	AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
	    nvl( p_site_address3, fnd_api.g_miss_char)
	AND nvl( loc.ADDRESS4, fnd_api.g_miss_char) =
	    nvl( p_site_address4, fnd_api.g_miss_char)
	AND nvl( loc.city, fnd_api.g_miss_char) =
	    nvl( p_site_city, fnd_api.g_miss_char)
	AND nvl( loc.state, fnd_api.g_miss_char) =
	    nvl( p_site_state, fnd_api.g_miss_char)
	AND nvl( loc.postal_code, fnd_api.g_miss_char) =
	    nvl( p_site_postal_code, fnd_api.g_miss_char)
	AND nvl( loc.country, fnd_api.g_miss_char) =
	    nvl( p_site_country, fnd_api.g_miss_char)
	AND site.status = 'A'
	AND site.party_id in(
			     SELECT in_sold_to_party_id FROM DUAL
			     UNION
			     SELECT object_ID
			     FROM HZ_relationships rel
			     WHERE rel.subject_id= in_sold_to_party_id
			     and  rel.status='A');

Line: 5878

     SELECT site.party_site_id
       FROM hz_locations loc,
	    hz_party_sites site
      WHERE site.location_id=loc.location_id
	and site.status='A'
	and loc.address1  = p_site_address1
	and nvl( loc.address2, fnd_api.g_miss_char) =
	    nvl( p_site_address2, fnd_api.g_miss_char)
	and nvl( loc.address3, fnd_api.g_miss_char) =
	    nvl( p_site_address3, fnd_api.g_miss_char)
	and nvl( loc.address4, fnd_api.g_miss_char) =
	    nvl( p_site_address4, fnd_api.g_miss_char)
	and nvl( loc.city, fnd_api.g_miss_char) =
	    nvl( p_site_city, fnd_api.g_miss_char)
	and nvl( loc.state, fnd_api.g_miss_char) =
	    nvl( p_site_state, fnd_api.g_miss_char)
	and nvl( loc.postal_code, fnd_api.g_miss_char) =
	    nvl( p_site_postal_code, fnd_api.g_miss_char)
	and nvl( loc.country, fnd_api.g_miss_char) =
	    nvl( p_site_country, fnd_api.g_miss_char);

Line: 5947

	  SELECT site.party_site_id
	     INTO l_id
	     FROM hz_locations loc,
	     hz_party_sites site
	     WHERE loc.ADDRESS1  = p_site_address1
	     AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
	     nvl( p_site_address2, fnd_api.g_miss_char)
	     AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
	     nvl( p_site_address3, fnd_api.g_miss_char)
	     AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
	     DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
	     DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
	     DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
	     nvl( p_site_address4, fnd_api.g_miss_char)
	     AND site.status = 'A'
	     AND site.party_id = p_party_id
	     and site.location_id=loc.location_id;

Line: 5997

	  SELECT site.party_site_id
	   INTO l_id
	   FROM hz_locations loc,
	   hz_party_sites site
	   WHERE loc.ADDRESS1  = p_site_address1
	   AND nvl( loc.ADDRESS2, fnd_api.g_miss_char) =
	   nvl( p_site_address2, fnd_api.g_miss_char)
	   AND nvl( loc.ADDRESS3, fnd_api.g_miss_char) =
	   nvl( p_site_address3, fnd_api.g_miss_char)
	   AND DECODE(loc.CITY,NULL,NULL,loc.CITY||', ')||
	   DECODE(loc.STATE, NULL, NULL, loc.STATE || ', ')||
	   DECODE(POSTAL_CODE, NULL, NULL, loc.POSTAL_CODE || ', ')||
	   DECODE(loc.COUNTRY, NULL, NULL, loc.COUNTRY) =
	   nvl( p_site_address4, fnd_api.g_miss_char)
	   AND site.status = 'A'
	   and site.location_id=loc.location_id;

Line: 6095

      SELECT ORGANIZATION_ID
      INTO l_id
      FROM OE_SOLD_TO_ORGS_V
      WHERE CUSTOMER_NUMBER = p_customer_number;

Line: 6101

      Select  Cust_Acct.Cust_account_id
	 into l_id
	 from HZ_CUST_ACCOUNTS  Cust_Acct,
	 HZ_PARTIES Party
	 where Cust_Acct.Party_id = Party.party_id
	 and cust_acct.status='A'
	  and Party.Party_name = p_sold_to_org;

Line: 6162

		select 'Y' into x_found from HZ_CUST_ACCOUNTS where cust_account_id= p_cust_id;