APPS.RA_CUSTOMER_TEXT_PKG SQL Statements

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

Line: 31

 |    update_text_addr                                                        |
 |                                                                            |
 | DESCRIPTION                                                                |
 |    This procedure updates the address_text column of ra_addresses table    |
 |    with the concatenated customer,contacts,phones,address information      |
 |    so that interMedia index can be created on it to perform text searches. |
 |                                                                            |
 | PARAMETERS                                                                 |
 |   INPUT                                                                    |
 |                                                                            |
 |                                                                            |
 |   OUTPUT                                                                   |
 |      Errbuf                  VARCHAR2 -- Conc Pgm Error mesgs.             |
 |      RetCode                 VARCHAR2 -- Conc Pgm Error Code.              |
 |                                          0 - Success, 2 - Failure.         |
 |                                                                            |
 |                                                                            |
 | HISTORY                                                                    |
 |    12-Apr-1999    Ujjal Singh   Created.                                   |
 *----------------------------------------------------------------------------*/

PROCEDURE update_text_addr (
                        Errbuf                  OUT NOCOPY  VARCHAR2,
                        Retcode                 OUT NOCOPY  VARCHAR2,
                        p_idx_cust_contacts     IN          VARCHAR2) IS

cursor cust_index (p_schema IN VARCHAR2)is
/* Bug Fix: 4095863 */
select STATUS , DOMIDX_OPSTATUS , PARAMETERS from all_indexes where index_name = 'HZ_CUST_ACCT_SITES_ALL_T1'
and owner = p_schema;

Line: 104

         select count(1) into l_count
         from   ctxsys.ctx_index_errors
         where  err_index_name = 'HZ_CUST_ACCT_SITES_ALL_T1'
           and  rownum =1;

Line: 138

    SELECT PAR_VALUE INTO l_idx_mem
    FROM CTX_PARAMETERS
    WHERE PAR_NAME = 'MAX_INDEX_MEMORY';

Line: 144

        SELECT PAR_VALUE INTO l_idx_mem
        FROM CTX_PARAMETERS
        WHERE PAR_NAME = 'DEFAULT_INDEX_MEMORY';

Line: 200

        arp_util.debug('OTHERS : ra_customer_text_pkg.update_text_addr');

Line: 204

END update_text_addr;

Line: 243

    SELECT cust_account_id, ac.cust_acct_site_id, ps.party_site_id, loc.address1||' '||loc.address2||' '||loc.address3||' '||loc.address4||' '|| loc.city||' '||loc.state||' '||
           loc.province||' '||loc.postal_code || ' ' cust_address
    FROM APPS.HZ_LOCATIONS loc, APPS.HZ_PARTY_SITES ps,
         APPS.HZ_CUST_ACCT_SITES_ALL ac
    WHERE ac.PARTY_SITE_ID=ps.PARTY_SITE_ID
    AND loc.LOCATION_ID = ps.LOCATION_ID
    AND ac.ROWID = rid)
  LOOP
    write_to_lob(site_lob,sites.cust_address,stvar);

Line: 254

      SELECT cust_account_id, p.party_id,
        party_name||' '||ACCOUNT_NUMBER||' '||tax_reference || ' ' data
      FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNTS c
      WHERE p.party_id = c.party_id
      AND c.cust_account_id = sites.cust_account_id)
    LOOP
      l_party_id := cust.party_id;

Line: 263

         SELECT distinct customer_exemption_number || ' ' taxinfo
         FROM APPS.ra_tax_exemptions_all
         WHERE customer_id = cust.cust_account_id)
      LOOP
        write_to_lob(site_lob,tax.taxinfo,stvar);

Line: 272

       SELECT rel.PARTY_ID rel_party_id,
           p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
       FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
            APPS.HZ_RELATIONSHIPS rel
       WHERE ar.cust_account_id = sites.cust_account_id
       AND (ar.cust_acct_site_id is null)
       AND ar.ROLE_TYPE = 'CONTACT'
       AND ar.party_id = rel.party_id
       AND p.party_id = rel.subject_id
       AND rel.subject_table_name = 'HZ_PARTIES'
       AND rel.object_table_name  = 'HZ_PARTIES'
       AND rel.DIRECTIONAL_FLAG   = 'F')
    LOOP
      write_to_lob(site_lob,cont.name,stvar);

Line: 288

        SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
               ' ' || EMAIL_ADDRESS || ' ' phone
        FROM APPS.hz_contact_points
        WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
        AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
        AND OWNER_TABLE_ID = cont.rel_party_id)
      LOOP
        write_to_lob(site_lob,cp1.phone,stvar);

Line: 300

      SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
                ' ' || EMAIL_ADDRESS || ' ' phone
      FROM APPS.hz_contact_points
      WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
      AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
      AND OWNER_TABLE_ID = l_party_id)
    LOOP
      write_to_lob(site_lob,cp1.phone,stvar);

Line: 311

     SELECT rel.PARTY_ID rel_party_id,
         p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
     FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
          APPS.HZ_RELATIONSHIPS rel
     WHERE ar.cust_account_id = sites.cust_account_id
     AND (ar.cust_acct_site_id = sites.cust_acct_site_id)
     AND ar.ROLE_TYPE = 'CONTACT'
     AND ar.party_id = rel.party_id
     AND p.party_id = rel.subject_id
     AND rel.subject_table_name = 'HZ_PARTIES'
     AND rel.object_table_name  = 'HZ_PARTIES'
     AND rel.DIRECTIONAL_FLAG   = 'F')
    LOOP
      write_to_lob(site_lob,cont.name,stvar);

Line: 327

        SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
               ' ' || EMAIL_ADDRESS || ' ' phone
        FROM APPS.hz_contact_points
        WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
        AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
        AND OWNER_TABLE_ID = cont.rel_party_id)
      LOOP
        write_to_lob(site_lob,cp1.phone,stvar);

Line: 339

      SELECT PHONE_NUMBER||phone_area_code||phone_country_code|| ' ' phone
      FROM APPS.hz_contact_points
      WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
      AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'EMAIL', 'WEB')
      AND OWNER_TABLE_ID = sites.party_site_id)
    LOOP
      write_to_lob(site_lob,cp1.phone,stvar);

Line: 361

    SELECT ac.status, cust_account_id, ac.cust_acct_site_id, ps.party_site_id, loc.address1||' '||loc.address2||' '||loc.address3 ||' '||loc.address4||' '|| loc.city||' '||loc.state||' '||
           loc.province||' '||loc.postal_code || ' ' cust_address
    FROM APPS.HZ_LOCATIONS loc, APPS.HZ_PARTY_SITES ps,
         APPS.HZ_CUST_ACCT_SITES_ALL ac
    WHERE ac.PARTY_SITE_ID=ps.PARTY_SITE_ID
    AND loc.LOCATION_ID = ps.LOCATION_ID
    AND ac.ROWID = rid)
  LOOP
    IF sites.status is null or sites.status = 'A' THEN
      write_to_char(sites.cust_address,site_char);

Line: 373

        SELECT cust_account_id, p.party_id,
        party_name||' '||ACCOUNT_NUMBER||' '||tax_reference || ' ' data
        FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNTS c
        WHERE p.party_id = c.party_id
        AND c.cust_account_id = sites.cust_account_id)
      LOOP
        l_party_id := cust.party_id;

Line: 383

          SELECT distinct customer_exemption_number || ' ' taxinfo
          FROM APPS.ra_tax_exemptions_all
          WHERE customer_id = cust.cust_account_id)
        LOOP
          write_to_char(tax.taxinfo,site_char);

Line: 392

        SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
                  ' ' || EMAIL_ADDRESS || ' ' phone
        FROM APPS.hz_contact_points
        WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
        AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
        AND OWNER_TABLE_ID = l_party_id)
      LOOP
        write_to_char(cp1.phone,site_char);

Line: 404

       SELECT rel.PARTY_ID rel_party_id,
           p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
       FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
            APPS.HZ_PARTY_RELATIONSHIPS rel
       WHERE ar.cust_account_id = sites.cust_account_id
       AND (ar.cust_acct_site_id = sites.cust_acct_site_id)
       AND ar.ROLE_TYPE = 'CONTACT'
       AND ar.party_id = rel.party_id
       AND p.party_id = rel.subject_id )
      LOOP
        write_to_char(cont.name,site_char);

Line: 417

          SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
                 ' ' || EMAIL_ADDRESS || ' ' phone
          FROM APPS.hz_contact_points
          WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
          AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
          AND OWNER_TABLE_ID = cont.rel_party_id)
        LOOP
          write_to_char(cp1.phone,site_char);

Line: 430

        SELECT PHONE_NUMBER||phone_area_code||phone_country_code|| ' ' phone
        FROM APPS.hz_contact_points
        WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
        AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'EMAIL', 'WEB')
        AND OWNER_TABLE_ID = sites.party_site_id)
      LOOP
        write_to_char(cp1.phone,site_char);