[Home] [Help]
56:
57: cust_site_org_id := NULL ;
58: if(p_customer_site_use_id is not null) then
59: SELECT org_id INTO cust_site_org_id
60: FROM hz_cust_site_uses
61: WHERE site_use_id = p_customer_site_use_id ;
62: else
63: -- added below lines to avoid error when navigated from preferences->Access Request -> additional access for new Oracle apps user who doesn't have iRec access.
64: mo_global.init('AR');
489: l_query_string := 'SELECT anstable.'||p_answer_column|| ' from '|| p_answer_table||' anstable, hz_cust_accounts hca WHERE rownum = 1 and hca.'||p_hz_join_column||' = anstable.'||p_answer_join_column||
490: ' and hca.cust_account_id = '||p_customer_id;
491: elsif ( p_customer_site_use_id is not null ) then
492:
493: l_query_string := 'SELECT anstable.'||p_answer_column|| ' from '|| p_answer_table||' anstable,hz_party_sites hps, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu where hcsu.site_use_id =' || p_customer_site_use_id ||
494: ' and hcsu.cust_acct_site_id = hcas.cust_acct_site_id and hcas.party_site_id = hps.party_site_id and hps.'||p_hz_join_column||' = anstable.'||p_answer_join_column;
495:
496: else
497:
506: hz_party_sites party_sites,
507: hz_locations loc,
508: hz_cust_accounts Cust,
509: hz_parties Party,
510: hz_cust_site_uses site_uses
511: WHERE Party.party_id = Cust.party_id
512: AND Cust.account_number = p_customer_id
513: AND Cust.cust_account_id = acct_sites.cust_account_id
514: AND ACCT_SITES.party_site_id = PARTY_SITES.party_site_id
520: where rownum=1;
521:
522: arp_standard.debug('oldest bill to site id :: '||l_site_use_id);
523:
524: l_query_string :='SELECT anstable.'||p_answer_column|| ' from '|| p_answer_table||' anstable,hz_party_sites hps, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu where hcsu.site_use_id =' || l_site_use_id ||
525: ' and hcsu.cust_acct_site_id = hcas.cust_acct_site_id and hcas.party_site_id = hps.party_site_id and hps.'||p_hz_join_column||' = anstable.'||p_answer_join_column;
526:
527: end if;
528: