APPS.IEX_TERR_PUB SQL Statements

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

Line: 23

select request_id
from AR_CONC_PROCESS_REQUESTS
where CONCURRENT_PROGRAM_NAME in ('ARSUMREF','IEX_POPULATE_UWQ_SUM');

Line: 300

 l_last_update_date       DATE;

Line: 320

 SELECT  party_id INTO l_party_id
 FROM hz_cust_accounts
 WHERE  cust_account_id = l_custaccount_id;

Line: 324

  logmessage('Error while selecting the Party_id' || SQLERRM);

Line: 349

      INSERT INTO IEX_CHANGED_ACCOUNTS_ALL
      (
      OBJECT_VERSION_NUMBER
      ,PARTY_ID
      ,ACCOUNT_ID
      ,SITE_USE_ID
      ,LAST_UPDATE_DATE
      ,LAST_UPDATED_BY
      ,CREATION_DATE
      ,CREATED_BY
      ,LAST_UPDATE_LOGIN
      ,CHANGE_TYPE
      ,PROCESSED_FLAG
      )
      VALUES
      (
      1
      ,l_party_id
      ,l_custaccount_id
      ,null
      ,sysdate
      ,FND_GLOBAL.user_id
      ,sysdate
      ,FND_GLOBAL.user_id
      ,FND_GLOBAL.login_id
      ,'ACCOUNT'
      ,'N'
      );

Line: 420

      logmessage('Total Winners selected ' || l_count);

Line: 426

        UPDATE  HZ_CUSTOMER_PROFILES ACC
        SET object_version_number  =  nvl(object_version_number,0) + 1,
	    ACC.LAST_UPDATE_DATE       = SYSDATE,
 	    ACC.LAST_UPDATED_BY        = FND_GLOBAL.USER_ID,
	    ACC.LAST_UPDATE_LOGIN      = FND_GLOBAL.login_id ,
	    ACC.PROGRAM_APPLICATION_ID = FND_GLOBAL.PROG_APPL_ID,
	    ACC.PROGRAM_UPDATE_DATE    = SYSDATE,
            ACC.COLLECTOR_ID           = x_winners_rec.resource_id(i)
         WHERE  ACC.PARTY_ID           = x_winners_rec.trans_object_id(i)
      	 AND ACC.SITE_USE_ID           IS NULL
         AND ACC.CUST_ACCOUNT_ID       = l_custaccount_id
	 AND ACC.COLLECTOR_ID          <> x_winners_rec.resource_id(i);

Line: 442

         SELECT resource_name
	 INTO l_rsc_name
	 FROM jtf_rs_resource_extns_vl
	 WHERE resource_id = x_winners_rec.resource_id(i);

Line: 457

       logmessage('No Winners selected... No rows Updated');

Line: 534

     SELECT cust_account_id, site_use_id, party_id,collector_id
     into l_custaccount_id, l_siteuse_id, l_party_id ,l_collector_id
	   from hz_customer_profiles  where  CUST_ACCOUNT_PROFILE_id = l_profile_id;

Line: 723

 l_last_update_date       DATE;

Line: 769

    p_selection_mode     => 'Specific',
    p_filter_id          => l_acctsiteuse_id,
    x_return_status      => l_return_status,
    x_msg_count          => l_msg_count,
    x_msg_data           => l_msg_data);

Line: 906

  l_sql := 'SELECT ' ||
         ' party.party_name party_name, ' ||
         ' acc.account_name account_name, ' ||
         ' party.address1 address1, ' ||
	 ' party.city city, ' ||
	 ' party.state state, ' ||
         ' party.county county, ' ||
	 ' fnd_terr.territory_short_name country, ' ||
	 ' party.province province, ' ||
	 ' party.postal_code postal_code, ' ||
 	 ' phone.phone_country_code phone_country_code, ' ||
	 ' phone.phone_area_code phone_area_code, ' ||
	 ' phone.phone_number phone_number, ' ||
	 ' phone.phone_extension phone_extension ' ||
	 ' FROM ' ||
	 ' hz_cust_accounts acc, ' ||
	 ' hz_parties party, ' ||
	 ' fnd_territories_tl fnd_terr, ' ||
	 ' hz_contact_points phone ' ||
	 ' WHERE ' ||
	 ' acc.party_id = party.party_id ' ||
	 ' AND phone.owner_table_id(+) = party.party_id ' ||
	 ' AND phone.owner_table_name(+) = ''HZ_PARTIES'' '  ||
	 ' AND phone.contact_point_type(+) = ''PHONE'' ' ||
	 ' AND phone.primary_by_purpose(+) = ''Y'' ' ||
	 ' AND phone.contact_point_purpose(+) = ''COLLECTIONS'' ' ||
	 ' AND phone.phone_line_type(+) NOT IN(''PAGER'',   ''FAX'') ' ||
	 ' AND phone.status(+) = ''A'' ' ||
	 ' AND nvl(phone.do_not_use_flag(+),   ''N'') = ''N'' ' ||
	 ' AND party.country = fnd_terr.territory_code(+) ' ||
	 ' AND fnd_terr.LANGUAGE(+) = userenv(''LANG'') ' ;

Line: 950

    SELECT PARTY_ID INTO l_party_id from
    HZ_CUST_ACCOUNTS WHERE
    CUST_ACCOUNT_ID = p_party_id;

Line: 955

      iex_debug_pub.logmessage ('IEX Summarty Synchronization , Error occurred in party_id select  ' || sqlerrm);

Line: 988

 UPDATE IEX_DLN_UWQ_SUMMARY SET
   party_name    =  l_party_name,
   account_name  =  l_account_name,
   address1      =  l_address1,
   city          =  l_city,
   state         =  l_state,
   county        =  l_county,
   country       =  l_country,
   province      =  l_province,
   postal_code   =  l_postal_code,
   phone_country_code =  l_phone_country_code,
   phone_area_code =  l_phone_area_code,
   phone_number =  l_phone_number,
   phone_extension =  l_phone_extension
   WHERE party_id = l_party_id;

Line: 1010

      iex_debug_pub.logmessage ('IEX Summary Synchronization , Completed Update. ' );

Line: 1025

  SELECT resource_id,resource_type
  INTO l_resource_id,l_resource_type
  FROM ar_collectors
  WHERE collector_id = p_collector_id;

Line: 1030

 UPDATE IEX_DLN_UWQ_SUMMARY IDS
  SET   COLLECTOR_ID = P_COLLECTOR_ID,
        COLLECTOR_RESOURCE_ID = L_RESOURCE_ID ,
        COLLECTOR_RES_TYPE = L_RESOURCE_TYPE
  WHERE COLLECTOR_ID <> P_COLLECTOR_ID
    AND   IDS.PARTY_ID = P_PARTY_ID
    AND   NVL(IDS.CUST_ACCOUNT_ID,1) = NVL(P_ACCOUNT_ID,NVL(IDS.CUST_ACCOUNT_ID,1))
    AND   NVL(IDS.SITE_USE_ID,1)     = NVL(P_SITE_USE_ID,NVL(IDS.SITE_USE_ID,1))
    AND   IEU_PARAM_PK_COL = l_ieu_param_pk_col;    --Added for bug#6833110 by PNAVEENK on 29-Aug-2008