eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.ARW_SEARCH_CUSTOMERS SQL Statements

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

Line: 15


  SELECT
       cus.customer_id,
       cus.DETAILS_LEVEL,
       cus.CUSTOMER_NUMBER,
       substrb(cus.CUSTOMER_NAME, 1, 50) CUSTOMER_NAME,
       cus.ADDRESS_ID,
       cus.CONCATENATED_ADDRESS,
       cus.CONTACT_NAME,
       cus.CONTACT_PHONE,
       cus.BILL_TO_SITE_USE_ID,
       ari_utilities.get_site_uses(cus.ADDRESS_ID) SITE_USES,
       cus.ORG_ID,
       'N' selected,
       ari_utilities.get_site_use_location(cus.ADDRESS_ID) location
  FROM
       ari_customer_search_v cus
  WHERE
       cus.customer_id = p_customer_id
  ORDER BY DETAILS_LEVEL DESC, cus.address_id;

Line: 37


  SELECT DISTINCT
       cus.cust_account_id customer_id,
       'CUST' DETAILS_LEVEL,
       cus.ACCOUNT_NUMBER customer_number,
       substrb(party.party_name, 1, 50) CUSTOMER_NAME,
       -1 address_id,
       'ALL_LOCATIONS' CONCATENATED_ADDRESS,
       ari_utilities.get_contact(cus.cust_account_id, null, 'ALL') CONTACT_NAME,
       ari_utilities.get_phone(cus.cust_account_id, null, 'ALL','GEN') CONTACT_PHONE,
       -1 BILL_TO_SITE_USE_ID,
       NULL SITE_USES,
       cus.org_id,
       'N' selected,
       '' location
  FROM
       hz_cust_accounts cus,
       hz_parties party,
       ra_customer_trx ct
  WHERE
       ct.trx_number = p_search_criteria
   and ct.bill_to_customer_id = cus.cust_account_id
   and party.party_id = cus.party_id;

Line: 62


  SELECT
       cus.customer_id,
       cus.DETAILS_LEVEL,
       cus.CUSTOMER_NUMBER,
       substrb(cus.CUSTOMER_NAME, 1, 50) CUSTOMER_NAME,
       cus.ADDRESS_ID,
       cus.CONCATENATED_ADDRESS,
       cus.CONTACT_NAME,
       cus.CONTACT_PHONE,
       cus.BILL_TO_SITE_USE_ID,
       ari_utilities.get_site_uses(cus.ADDRESS_ID) SITE_USES,
       cus.ORG_ID,
       'N' selected,
       ari_utilities.get_site_use_location(cus.ADDRESS_ID) location
  FROM
       ari_customer_search_v cus
  WHERE
       cus.CUSTOMER_NUMBER like p_customer_name_number
  UNION
	SELECT
       cus.customer_id,
       cus.DETAILS_LEVEL,
       cus.CUSTOMER_NUMBER,
       substrb(cus.CUSTOMER_NAME, 1, 50) CUSTOMER_NAME,
       cus.ADDRESS_ID,
       cus.CONCATENATED_ADDRESS,
       cus.CONTACT_NAME,
       cus.CONTACT_PHONE,
       cus.BILL_TO_SITE_USE_ID,
       ari_utilities.get_site_uses(cus.ADDRESS_ID) SITE_USES,
       cus.ORG_ID,
       'N' selected,
       ari_utilities.get_site_use_location(cus.ADDRESS_ID) location
  FROM
       ari_customer_search_v cus
  WHERE
	cus.customer_name like p_customer_name_number
  ORDER BY 2 DESC, 5;

Line: 117


     SELECT adr.cust_acct_site_id address_id, adr.cust_account_id customer_id, score(1) total_score
     FROM   hz_cust_acct_sites_all adr,
            hz_cust_accounts cus,
            hz_parties party,
            ar_system_parameters_all sys
     WHERE ctxsys.CONTAINS (address_text, NVL(p_keyword, '%') , 1) > 0
     AND   adr.cust_account_id =  cus.cust_account_id
     AND   cus.party_id = party.party_id
     AND   adr.org_id      = sys.org_id    ;

Line: 131


   SELECT adr.cust_acct_site_id address_id, adr.cust_account_id customer_id, score(1) total_score
   FROM   hz_cust_acct_sites adr
   WHERE ctxsys.CONTAINS (address_text, NVL(p_keyword, '%') , 1) > 0
   ORDER BY score(1) desc;

Line: 154


   l_cust_tab.DELETE;

Line: 155


   l_rev_cust_tab.DELETE;

Line: 156


   l_addr_tab.DELETE;

Line: 415


	     l_cust_rec.selected := 'Y';

Line: 552


 |    invokes the search_customers function for a given keyword, and inserts  |
 |    the result data from the PL/SQL table into a global temporary table.    |
 |                                                                            |
 | REQUIRES                                                                   |
 |                                                                            |
 | RETURNS                                                                    |
 |                                                                            |
 | EXCEPTIONS RAISED                                                          |
 |                                                                            |
 | KNOWN BUGS                                                                 |
 |                                                                      |
 |                                                                            |
 | NOTES                                                                      |
 |    This wrapper was necessary because the new techstack did not have any   |
 |    way to exchange PL/SQL table types b/w java beans and PL/SQL procedures.|
 | HISTORY                                                                    |
 |      15-Dec-00       Krishnakumar Menon           Created                  |
 |      25-Oct-04       vnb                 Bug 3926187 - Modified to handle  |
 |                                          exceptions                        |
 *----------------------------------------------------------------------------*/
PROCEDURE ari_search ( i_keyword   IN varchar2,
		       i_name_num IN VARCHAR2,
                       x_status    OUT NOCOPY VARCHAR2,
                       x_msg_count OUT NOCOPY NUMBER,
                       x_msg_data  OUT NOCOPY VARCHAR2 ) is

    l_search_result_table  customer_tabletype;

Line: 605


    l_debug_info := 'Delete all entries from the table for the current session';

Line: 613


    delete from ar_cust_search_gt;

Line: 671


    	l_debug_info := 'Insert returned rows into the global temporary table';

Line: 684


            INSERT INTO ar_cust_search_gt (
                customer_id,
                address_id,
                bill_to_site_use_id,
                details_level,
                customer_number,
                customer_name,
                contact_name,
                contact_phone,
                site_uses,
                org_id,
                concatenated_address,
                location
            )
            VALUES (
                l_search_result_table(l_tab_idx).customer_id,
                l_search_result_table(l_tab_idx).address_id,
                decode(l_search_result_table(l_tab_idx).bill_to_site_use_id,-1,null,
                       l_search_result_table(l_tab_idx).bill_to_site_use_id),
                l_search_result_table(l_tab_idx).details_level,
                l_search_result_table(l_tab_idx).customer_number,
                l_search_result_table(l_tab_idx).customer_name,
                l_search_result_table(l_tab_idx).contact_name,
                l_search_result_table(l_tab_idx).contact_phone,
                l_search_result_table(l_tab_idx).site_uses,
                l_search_result_table(l_tab_idx).org_id,
                decode(l_search_result_table(l_tab_idx).address_id, -1, l_all_locations,
                       substrb(l_search_result_table(l_tab_idx).concatenated_address,1,255)),
                l_search_result_table(l_tab_idx).location

              );

Line: 724


                       arp_standard.debug('Unexpected Exception in ari_search: Loop and Insert');

Line: 783


		SELECT
		Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id
		FROM
		hz_cust_acct_sites     acct_sites,
		hz_party_sites         party_sites,
		hz_cust_accounts       Cust,
		ar_sites_assigned_v    Sites_assigned
		WHERE Sites_assigned.party_id = p_party_id
		AND Sites_assigned.cust_account_id=nvl(p_customer_id,Sites_assigned.cust_account_id)
		AND cust.cust_account_id = Sites_assigned.cust_account_id
		AND Sites_assigned.cust_account_id = acct_sites.cust_account_id
		AND Sites_assigned.cust_acct_site_id = acct_sites.cust_acct_site_id
		AND ACCT_SITES.party_site_id     = PARTY_SITES.party_site_id;

Line: 800


delete from ar_irec_user_acct_sites_all where (session_id=p_session_id
  or trunc(CREATION_DATE)<=trunc(sysdate-2));

Line: 819


				select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
			)LOOP

				IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
					INSERT INTO ar_irec_user_acct_sites_all
					(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
					VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 830


		      INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
				VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 838


			/* insert all the sites this party is having direct access */

			FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop
			IF
			FETCH_SITES_ID_CURSOR_RECORD.address_id IS NOT NULL
			AND ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ) > 0
			THEN
				INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
				VALUES(p_session_id,FETCH_SITES_ID_CURSOR_RECORD.account_id,ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 851


			/* Check for account level access and insert all bill to sites */

			FOR customer_assigned_record IN (
				select cust_account_id from ar_customers_assigned_v where party_id=p_party_id AND cust_account_id=nvl(p_customer_id,cust_account_id)
			)LOOP

				FOR account_assigned_site IN (
					select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID=customer_assigned_record.cust_account_id
				)LOOP

					IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
						INSERT INTO ar_irec_user_acct_sites_all
						(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
						VALUES(p_session_id,customer_assigned_record.cust_account_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 873


		      INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
				VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 886


DELETE FROM ar_irec_user_acct_sites_all A WHERE ROWID > (
     SELECT min(rowid) FROM ar_irec_user_acct_sites_all B
     WHERE A.org_id = B.org_id
     AND A.SESSION_ID=B.SESSION_ID
     AND A.USER_ID=B.USER_ID
     AND A.CUSTOMER_ID=B.CUSTOMER_ID
     AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
     AND A.CREATION_DATE=B.CREATION_DATE
     );

Line: 916


		SELECT
		Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id
		FROM
		hz_cust_acct_sites     acct_sites,
		hz_party_sites         party_sites,
		hz_cust_accounts       Cust,
		ar_sites_assigned_v    Sites_assigned
		WHERE -- Sites_assigned.party_id = p_party_id AND
		Sites_assigned.cust_account_id=nvl(p_customer_id,Sites_assigned.cust_account_id)
		AND cust.cust_account_id = Sites_assigned.cust_account_id
		AND Sites_assigned.cust_account_id = acct_sites.cust_account_id
		AND Sites_assigned.cust_acct_site_id = acct_sites.cust_acct_site_id
		AND ACCT_SITES.party_site_id     = PARTY_SITES.party_site_id;

Line: 933


delete from ar_irec_user_acct_sites_all where (session_id=p_session_id
  or trunc(CREATION_DATE)<=trunc(sysdate-2));

Line: 946


				select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
			)LOOP

				IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
					INSERT INTO ar_irec_user_acct_sites_all
					(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
					VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));

Line: 957


		      INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
				VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));

Line: 965


			/* insert all the sites this party is having direct access */

			FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop
			IF
			FETCH_SITES_ID_CURSOR_RECORD.address_id IS NOT NULL
			AND ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ) > 0
			THEN
				INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
				VALUES(p_session_id,FETCH_SITES_ID_CURSOR_RECORD.account_id,ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ),p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));

Line: 978


			/* Check for account level access and insert all bill to sites */

			FOR customer_assigned_record IN (
				select cust_account_id from ar_customers_assigned_v where cust_account_id=nvl(p_customer_id,cust_account_id)
			)LOOP

				FOR account_assigned_site IN (
					select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID=customer_assigned_record.cust_account_id
				)LOOP

					IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
						INSERT INTO ar_irec_user_acct_sites_all
						(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
						VALUES(p_session_id,customer_assigned_record.cust_account_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));

Line: 1000


		      INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE)
				VALUES(p_session_id,p_customer_id,p_site_use_id,p_user_id,trunc(sysdate),l_org_id, trunc(sysdate));

Line: 1010


DELETE FROM ar_irec_user_acct_sites_all A WHERE ROWID > (
     SELECT min(rowid) FROM ar_irec_user_acct_sites_all B
     WHERE A.org_id = B.org_id
     AND A.SESSION_ID=B.SESSION_ID
     AND A.USER_ID=B.USER_ID
     AND A.CUSTOMER_ID=B.CUSTOMER_ID
     AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
     AND A.CREATION_DATE=B.CREATION_DATE
     );

Line: 1023


PROCEDURE update_account_sites ( p_customer_id in number,
		p_session_id in number,
		p_user_id in number ,
		p_org_id in number ,
		p_is_internal_user in varchar2
		)
	IS

	 l_curr_index   NUMBER;

Line: 1038


		SELECT
		Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id
		FROM
		hz_cust_acct_sites     acct_sites,
		hz_party_sites         party_sites,
		hz_cust_accounts       Cust,
		ar_sites_assigned_v    Sites_assigned
		WHERE Sites_assigned.party_id = p_party_id
		AND Sites_assigned.cust_account_id=nvl(p_customer_id,Sites_assigned.cust_account_id)
		AND cust.cust_account_id = Sites_assigned.cust_account_id
		AND Sites_assigned.cust_account_id = acct_sites.cust_account_id
		AND Sites_assigned.cust_acct_site_id = acct_sites.cust_acct_site_id
		AND ACCT_SITES.party_site_id     = PARTY_SITES.party_site_id;

Line: 1055


delete from ar_irec_user_acct_sites_all where session_id=p_session_id AND RELATED_CUSTOMER_FLAG = 'Y';

Line: 1057


select person_party_id into p_party_id from fnd_user where user_id = p_user_id;

Line: 1061


				select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
			)LOOP
				IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
					INSERT INTO ar_irec_user_acct_sites_all
					(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE, RELATED_CUSTOMER_FLAG)
					VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate), 'Y');

Line: 1071


			/* insert all the sites this party is having direct access */
			FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop
			IF  FETCH_SITES_ID_CURSOR_RECORD.address_id IS NOT NULL
			AND ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ) > 0
			THEN
				INSERT INTO ar_irec_user_acct_sites_all
				(SESSION_ID,CUSTOMER_ID,CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE, RELATED_CUSTOMER_FLAG )
				VALUES(p_session_id,FETCH_SITES_ID_CURSOR_RECORD.account_id,ari_utilities.get_bill_to_site_use_id( FETCH_SITES_ID_CURSOR_RECORD.address_id ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate), 'Y');

Line: 1081


			/* Check for account level access and insert all bill to sites */
			FOR account_assigned_site IN (
					select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID=p_customer_id
				)LOOP

					IF ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID )>0 THEN
						INSERT INTO ar_irec_user_acct_sites_all
						(SESSION_ID,CUSTOMER_ID, CUSTOMER_SITE_USE_ID,USER_ID,CURRENT_DATE,ORG_ID, CREATION_DATE, RELATED_CUSTOMER_FLAG)
						VALUES(p_session_id,p_customer_id,ari_utilities.get_bill_to_site_use_id( account_assigned_site.CUST_ACCT_SITE_ID ),p_user_id,trunc(sysdate),p_org_id, trunc(sysdate), 'Y');

Line: 1097


DELETE FROM ar_irec_user_acct_sites_all A WHERE ROWID > (
     SELECT min(rowid) FROM ar_irec_user_acct_sites_all B
     WHERE A.org_id = B.org_id
     AND A.SESSION_ID=B.SESSION_ID
     AND A.USER_ID=B.USER_ID
     AND A.CUSTOMER_ID=B.CUSTOMER_ID
     AND A.CUSTOMER_SITE_USE_ID=B.CUSTOMER_SITE_USE_ID
     AND A.CREATION_DATE=B.CREATION_DATE
     );

Line: 1109


END update_account_sites;