[Home] [Help]
5: TYPE l_trx_id_type IS TABLE OF ra_customer_trx_all.customer_trx_id%type
6: INDEX BY BINARY_INTEGER;
7: TYPE l_term_id_type IS TABLE OF ra_customer_trx_all.term_id%type
8: INDEX BY BINARY_INTEGER;
9: TYPE hz_customer_prof_cach is table of hz_customer_profiles.cons_bill_level%TYPE
10: INDEX BY BINARY_INTEGER;
11:
12: temp_bill_level_table hz_customer_prof_cach;
13:
219:
220: begin
221: select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
222: into bfb_level
223: from hz_customer_profiles cp
224: where cp.cust_account_id = p_cust_account_id
225: and cp.site_use_id IS NULL;
226: exception
227: when no_data_found then
234: IF (temp_bill_level_table.EXISTS(p_cust_account_id) = FALSE) then
235: -- the cons_bill_level is driven by the value at the account level profile
236: select decode(cp.cons_bill_level,'ACCOUNT','A','SITE','S','N')
237: into bfb_level
238: from hz_customer_profiles cp
239: where cp.cust_account_id = p_cust_account_id
240: and cp.site_use_id IS NULL;
241:
242: temp_bill_level_table(p_cust_account_id) := bfb_level;
349: -- if bill level is not set
350: , -91)))
351: into l_default_term
352: from ra_cust_trx_types tt,
353: hz_customer_profiles ap,
354: hz_cust_site_uses su,
355: ra_terms_b apt,
356: ra_terms_b spt,
357: ( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
355: ra_terms_b apt,
356: ra_terms_b spt,
357: ( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
358: su2.site_use_id profile_bill_to_site_use_id
359: from hz_customer_profiles cp,
360: hz_cust_site_uses su1,
361: hz_cust_site_uses su2
362: where cp.site_use_id = su1.site_use_id
363: and cp.status ='A'
424: 'SITE', nvl(nvl(sp.standard_terms, ap.standard_terms), -95)
425: , -91)))) new_term_id, trx.customer_trx_id
426: from ra_customer_trx trx,
427: ra_cust_trx_types tt,
428: hz_customer_profiles ap,
429: hz_cust_site_uses su,
430: ra_terms_b sut,
431: ra_terms_b apt,
432: ra_terms_b spt,
433: ra_terms_b ttt,
434: ra_terms_b invt,
435: ( select /*+ leading(su2) */ cp.override_terms,cp.standard_terms,cp.cust_account_id,
436: su2.site_use_id profile_bill_to_site_use_id
437: from hz_customer_profiles cp,
438: hz_cust_site_uses su1,
439: hz_cust_site_uses su2
440: where cp.site_use_id = su1.site_use_id
441: and cp.status ='A'