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: 16


SELECT * FROM (SELECT cust.cust_account_id CUSTOMER_ID,
  'CUST' DETAILS_LEVEL,
  cust.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  SUBSTRB(party.PARTY_NAME,1,50) CUSTOMER_NAME,
  -1 address_id,
  'ALL_LOCATIONS' concatenated_address,
  TO_CHAR(NULL) contact_name,
  TO_CHAR(NULL) contact_phone,
  -1 BILL_TO_SITE_USE_ID,
  TO_CHAR(NULL) site_uses,
  -1 org_id,
  'N' selected,
   TO_CHAR(NULL) location
FROM hz_cust_accounts cust,
  hz_parties party
WHERE cust.party_id = party.party_id AND
      cust.cust_account_id = p_customer_id

UNION ALL

SELECT HCA.CUST_ACCOUNT_ID CUSTOMER_ID ,
  'ADDR' DETAILS_LEVEL,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  SUBSTRB(HP.PARTY_NAME,1,50) CUSTOMER_NAME,
  ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID,
  ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE,LOC.ADDRESS1, LOC.ADDRESS2,LOC.ADDRESS3,LOC.ADDRESS4,LOC.CITY,LOC.COUNTY, LOC.STATE,LOC.PROVINCE,LOC.POSTAL_CODE, T.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS,
  TO_CHAR(NULL) CONTACT_NAME,
  TO_CHAR(NULL) CONTACT_PHONE,
  -1 BILL_TO_SITE_USE_ID,
  ari_utilities.get_site_uses(ACCT_SITE.CUST_ACCT_SITE_ID) SITE_USES,
  ACCT_SITE.org_id,
  'N' selected,
  ari_utilities.get_site_use_location(ACCT_SITE.CUST_ACCT_SITE_ID) location
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
  HZ_PARTY_SITES PARTY_SITE ,
 -- For Bug# 13688313
 -- HZ_LOC_ASSIGNMENTS LOC_ASSIGN ,
  HZ_LOCATIONS LOC ,
  FND_TERRITORIES_VL T,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP
WHERE ACCT_SITE.PARTY_SITE_ID   = PARTY_SITE.PARTY_SITE_ID
  AND LOC.LOCATION_ID             = PARTY_SITE.LOCATION_ID
  AND PARTY_SITE.PARTY_ID		= HCA.PARTY_ID
  AND HCA.CUST_ACCOUNT_ID		= ACCT_SITE.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID		= HP.PARTY_ID
  AND PARTY_SITE.PARTY_ID		= HP.PARTY_ID
 -- For Bug# 13688313
 -- AND LOC.LOCATION_ID             = LOC_ASSIGN.LOCATION_ID
 -- AND NVL(ACCT_SITE.ORG_ID, -99)  = NVL(LOC_ASSIGN.ORG_ID, -99)
  AND LOC.COUNTRY                 = T.TERRITORY_CODE
  AND nvl(acct_site.bill_to_flag, 'N') in (decode(p_show_all_sites,'N','P',nvl(acct_site.bill_to_flag,'N')),decode(p_show_all_sites,'N','Y',nvl(acct_site.bill_to_flag,'N')))
  AND ACCT_SITE.CUST_ACCOUNT_ID LIKE p_customer_id)
ORDER BY 2 DESC, 5;

Line: 74


  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: 99


SELECT * FROM
(SELECT cust.cust_account_id CUSTOMER_ID,
  'CUST' DETAILS_LEVEL,
  cust.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  SUBSTRB(party.PARTY_NAME,1,50) CUSTOMER_NAME,
  -1 address_id,
  'ALL_LOCATIONS' concatenated_address,
  TO_CHAR(NULL) contact_name,
  TO_CHAR(NULL) contact_phone,
  -1 BILL_TO_SITE_USE_ID,
  TO_CHAR(NULL) site_uses,
  -1 org_id,
  'N' selected,
  TO_CHAR(NULL) location
FROM hz_cust_accounts cust,
  hz_parties party
WHERE cust.party_id = party.party_id AND
      cust.ACCOUNT_NUMBER LIKE p_customer_name_number

UNION

SELECT cust.cust_account_id CUSTOMER_ID,
  'CUST' DETAILS_LEVEL,
  cust.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  SUBSTRB(party.PARTY_NAME,1,50) CUSTOMER_NAME,
  -1 address_id,
  'ALL_LOCATIONS' concatenated_address,
  TO_CHAR(NULL) contact_name,
  TO_CHAR(NULL) contact_phone,
  -1 BILL_TO_SITE_USE_ID,
  TO_CHAR(NULL) site_uses,
  -1 org_id,
  'N' selected,
  TO_CHAR(NULL) location
FROM hz_cust_accounts cust,
  hz_parties party
WHERE cust.party_id = party.party_id
  AND party.PARTY_NAME LIKE p_customer_name_number
)

UNION ALL

(SELECT HCA.CUST_ACCOUNT_ID CUSTOMER_ID ,
  'ADDR' DETAILS_LEVEL,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  SUBSTRB(HP.PARTY_NAME,1,50) CUSTOMER_NAME,
  ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID,
  ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE,LOC.ADDRESS1, LOC.ADDRESS2,LOC.ADDRESS3,LOC.ADDRESS4,LOC.CITY,LOC.COUNTY, LOC.STATE,LOC.PROVINCE,LOC.POSTAL_CODE, T.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS,
  TO_CHAR(NULL) CONTACT_NAME,
  TO_CHAR(NULL) CONTACT_PHONE,
  -1 BILL_TO_SITE_USE_ID,
  ari_utilities.get_site_uses(ACCT_SITE.CUST_ACCT_SITE_ID) SITE_USES,
  ACCT_SITE.org_id,
  'N' selected,
  ari_utilities.get_site_use_location(ACCT_SITE.CUST_ACCT_SITE_ID) location
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
  HZ_PARTY_SITES PARTY_SITE ,
 -- HZ_LOC_ASSIGNMENTS LOC_ASSIGN ,
  HZ_LOCATIONS LOC ,
  FND_TERRITORIES_VL T,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP
WHERE ACCT_SITE.PARTY_SITE_ID   = PARTY_SITE.PARTY_SITE_ID
  AND LOC.LOCATION_ID             = PARTY_SITE.LOCATION_ID
  AND PARTY_SITE.PARTY_ID		= HCA.PARTY_ID
  AND HCA.CUST_ACCOUNT_ID		= ACCT_SITE.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID		= HP.PARTY_ID
  AND PARTY_SITE.PARTY_ID		= HP.PARTY_ID
-- For Bug# 13688313
 -- AND LOC.LOCATION_ID             = LOC_ASSIGN.LOCATION_ID
 -- AND NVL(ACCT_SITE.ORG_ID, -99)  = NVL(LOC_ASSIGN.ORG_ID, -99)
  AND LOC.COUNTRY                 = T.TERRITORY_CODE
  AND nvl(acct_site.bill_to_flag, 'N') in (decode(p_show_all_sites,'N','P',nvl(acct_site.bill_to_flag,'N')),decode(p_show_all_sites,'N','Y',nvl(acct_site.bill_to_flag,'N')))
  AND HCA.ACCOUNT_NUMBER LIKE p_customer_name_number

UNION

SELECT HCA.CUST_ACCOUNT_ID CUSTOMER_ID ,
  'ADDR' DETAILS_LEVEL,
  HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,
  SUBSTRB(HP.PARTY_NAME,1,50) CUSTOMER_NAME,
  ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID,
  ARP_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE,LOC.ADDRESS1, LOC.ADDRESS2,LOC.ADDRESS3,LOC.ADDRESS4,LOC.CITY,LOC.COUNTY, LOC.STATE,LOC.PROVINCE,LOC.POSTAL_CODE, T.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS,
  TO_CHAR(NULL) CONTACT_NAME,
  TO_CHAR(NULL) CONTACT_PHONE,
  -1 BILL_TO_SITE_USE_ID,
  ari_utilities.get_site_uses(ACCT_SITE.CUST_ACCT_SITE_ID) SITE_USES,
  ACCT_SITE.org_id,
  'N' selected,
  ari_utilities.get_site_use_location(ACCT_SITE.CUST_ACCT_SITE_ID) location
FROM HZ_CUST_ACCT_SITES ACCT_SITE ,
  HZ_PARTY_SITES PARTY_SITE ,
 -- HZ_LOC_ASSIGNMENTS LOC_ASSIGN ,
  HZ_LOCATIONS LOC ,
  FND_TERRITORIES_VL T,
  HZ_CUST_ACCOUNTS HCA,
  HZ_PARTIES HP
WHERE ACCT_SITE.PARTY_SITE_ID   = PARTY_SITE.PARTY_SITE_ID
  AND LOC.LOCATION_ID             = PARTY_SITE.LOCATION_ID
  AND PARTY_SITE.PARTY_ID		= HCA.PARTY_ID
  AND HCA.CUST_ACCOUNT_ID		= ACCT_SITE.CUST_ACCOUNT_ID
  AND HCA.PARTY_ID		= HP.PARTY_ID
  AND PARTY_SITE.PARTY_ID		= HP.PARTY_ID
-- For Bug# 13688313
 -- AND LOC.LOCATION_ID             = LOC_ASSIGN.LOCATION_ID
--  AND NVL(ACCT_SITE.ORG_ID, -99)  = NVL(LOC_ASSIGN.ORG_ID, -99)
  AND LOC.COUNTRY                 = T.TERRITORY_CODE
  AND nvl(acct_site.bill_to_flag, 'N') in (decode(p_show_all_sites,'N','P',nvl(acct_site.bill_to_flag,'N')),decode(p_show_all_sites,'N','Y',nvl(acct_site.bill_to_flag,'N')))
  AND HP.PARTY_NAME LIKE p_customer_name_number )
ORDER BY 2 DESC, 5;

Line: 226


     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: 240


   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: 263


   l_cust_tab.DELETE;

Line: 264


   l_rev_cust_tab.DELETE;

Line: 265


   l_addr_tab.DELETE;

Line: 525


	     l_cust_rec.selected := 'Y';

Line: 663


 |    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,
		       i_exclude_contact IN VARCHAR2,
		       i_show_all_sites 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: 719


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

Line: 727


    delete from ar_cust_search_gt;

Line: 777


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

Line: 819


            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: 859


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

Line: 919


		SELECT
		Sites_assigned.CUST_ACCOUNT_ID account_id , acct_sites.CUST_ACCT_SITE_ID address_id,acct_sites.org_id org_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: 936


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

Line: 961


      select org_id into l_org_id from hz_cust_site_uses where site_use_id = p_site_use_id;

Line: 967


		/* the following insert statement is added for bug 7678038  to show receipts created with out location */

  	     	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,'-1',p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 974


				select CUST_ACCT_SITE_ID,org_id from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
			)LOOP

       -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
			 insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => account_assigned_site.org_id, p_customer_id => p_customer_id, p_cust_acct_site_id => account_assigned_site.CUST_ACCT_SITE_ID);

Line: 981


					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),account_assigned_site.org_id, trunc(sysdate));

Line: 990


					insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => p_customer_id, p_site_use_id => p_site_use_id);

Line: 992


       /*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: 999


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

			FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop

      -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
      insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => FETCH_SITES_ID_CURSOR_RECORD.org_id, p_customer_id => FETCH_SITES_ID_CURSOR_RECORD.account_id, p_cust_acct_site_id => FETCH_SITES_ID_CURSOR_RECORD.address_id);

Line: 1010


				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),FETCH_SITES_ID_CURSOR_RECORD.org_id, trunc(sysdate));

Line: 1016


			/* 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,org_id from hz_cust_acct_sites where CUST_ACCOUNT_ID=customer_assigned_record.cust_account_id
				)LOOP
        -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
				insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => account_assigned_site.org_id, p_customer_id => customer_assigned_record.cust_account_id, p_cust_acct_site_id => account_assigned_site.CUST_ACCT_SITE_ID);

Line: 1029


						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),account_assigned_site.org_id, trunc(sysdate));

Line: 1035


		/* the following insert statement is added for bug 7678038  to show receipts created with out location */

  	     	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,'-1',p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 1046


				insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => p_customer_id, p_site_use_id => p_site_use_id);

Line: 1047


        /* 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: 1060


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: 1075


PROCEDURE insert_acct_site_uses ( p_session_id in number,
		p_user_id in number ,
		p_org_id in number ,
		p_customer_id in number,
		p_cust_acct_site_id in number default null,
		p_site_use_id in number default null
		) is
l_site_use_id  NUMBER;

Line: 1088


			SELECT cust_acct_site_id into l_cust_acct_site_id FROM hz_cust_site_uses WHERE  SITE_USE_ID = p_site_use_id;

Line: 1094


				SELECT SITE_USE_ID FROM hz_cust_site_uses WHERE cust_acct_site_id = l_cust_acct_site_id and SITE_USE_CODE = 'BILL_TO'
			)LOOP
					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,account_assigned_site.SITE_USE_ID,p_user_id,trunc(sysdate),p_org_id, trunc(sysdate));

Line: 1101


end insert_acct_site_uses;

Line: 1119


		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: 1136


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

Line: 1152


				select CUST_ACCT_SITE_ID from hz_cust_acct_sites where CUST_ACCOUNT_ID = p_customer_id
			)LOOP

      -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
      insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => p_customer_id, p_cust_acct_site_id => account_assigned_site.CUST_ACCT_SITE_ID);

Line: 1158


					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: 1167


     insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => p_customer_id, p_site_use_id => p_site_use_id);

Line: 1169


    /*  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: 1177


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

			FOR FETCH_SITES_ID_CURSOR_RECORD IN FETCH_SITES_ID_CURSOR loop

      -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
      insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => FETCH_SITES_ID_CURSOR_RECORD.account_id, p_cust_acct_site_id => FETCH_SITES_ID_CURSOR_RECORD.address_id);

Line: 1187


				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: 1193


			/* 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
           -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
           insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => customer_assigned_record.cust_account_id, p_cust_acct_site_id => account_assigned_site.CUST_ACCT_SITE_ID);

Line: 1205


						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: 1214


		     insert_acct_site_uses(p_session_id => p_session_id, p_user_id => p_user_id, p_org_id => l_org_id, p_customer_id => p_customer_id, p_site_use_id => p_site_use_id);

Line: 1215


     /* 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: 1225


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: 1238


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: 1253


		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: 1270


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

Line: 1272


select person_party_id into p_party_id from fnd_user where user_id = p_user_id;

Line: 1276


		/* the following insert statement is added for bug 7678038  to show receipts created with out location */

  	     	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,'-1',p_user_id,trunc(sysdate),p_org_id, trunc(sysdate),'Y');

Line: 1283


				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: 1293


			/* 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: 1303


			/* 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: 1314


		/* the following insert statement is added for bug 7678038  to show receipts created with out location */

  	     	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,'-1',p_user_id,trunc(sysdate),p_org_id, trunc(sysdate),'Y');

Line: 1324


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: 1336


END update_account_sites;