[Home] [Help]
273: | |
274: | The intent of its creation was to minimize the code change for all the |
275: | SQLs which were using : |
276: | |
277: | ra customers.statement_site_use_id = hz_cust_site_uses.site_use_id (+) |
278: | |
279: | These queries can now be changed to: |
280: | |
281: | ARP_CUST_PKG.get_statement_site(hz_cust_accounts.cust_account_id) = |
278: | |
279: | These queries can now be changed to: |
280: | |
281: | ARP_CUST_PKG.get_statement_site(hz_cust_accounts.cust_account_id) = |
282: | hz_cust_site_uses.site_use_id (+) |
283: | |
284: | Make sure you donot pass a constant as an argument when making use |
285: | of this function in a query which is supposed to succeed even if the |
286: | the statement site does not exist for a customer. The outer join does |
299: p_customer_id IN hz_cust_accounts.cust_account_id%type
300: )
301: RETURN NUMBER is
302:
303: v_statement_site_use_id hz_cust_site_uses.site_use_id%type;
304:
305: BEGIN
306:
307: select site_uses.site_use_id
306:
307: select site_uses.site_use_id
308: into v_statement_site_use_id
309: from hz_cust_acct_sites acct_site,
310: hz_cust_site_uses site_uses
311: where acct_site.cust_account_id = p_customer_id
312: and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
313: and site_uses.site_use_code = 'STMTS'
314: and site_uses.status = 'A';
353: | |
354: | The intent of its creation was to minimize the code change for all the |
355: | SQLs which were using : |
356: | |
357: | ra customers.dunning_site_use_id = hz_cust_site_uses.site_use_id (+) |
358: | |
359: | These queries can now be changed to: |
360: | |
361: | ARP_CUST_PKG.get_dunning(hz_cust_accounts.cust_account_id) = |
358: | |
359: | These queries can now be changed to: |
360: | |
361: | ARP_CUST_PKG.get_dunning(hz_cust_accounts.cust_account_id) = |
362: | hz_cust_site_uses.site_use_id (+) |
363: | |
364: | Make sure you donot pass a constant as an argument when making use |
365: | of this function in a query which is supposed to succeed even if the |
366: | the dunning site does not exist for a customer. The outer join does |
379: p_customer_id IN hz_cust_accounts.cust_account_id%type
380: )
381: RETURN NUMBER is
382:
383: v_dunning_site_use_id hz_cust_site_uses.site_use_id%type;
384:
385: BEGIN
386:
387: select site_uses.site_use_id
386:
387: select site_uses.site_use_id
388: into v_dunning_site_use_id
389: from hz_cust_acct_sites acct_site,
390: hz_cust_site_uses site_uses
391: where acct_site.cust_account_id = p_customer_id
392: and site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
393: and site_uses.site_use_code = 'DUN'
394: and site_uses.status = 'A';
441: p_bill_to_site_id IN NUMBER
442: )
443: return varchar2 is
444: --
445: v_dunning_site hz_cust_site_uses.site_use_id%type;
446: v_dunning_type ar_dunning_letter_sets.dunning_type%type;
447: --
448: BEGIN
449: --
462: else
463: --
464: select dls.dunning_type
465: into v_dunning_type
466: from hz_cust_site_uses su,
467: hz_cust_acct_sites ad_cus,
468: hz_customer_profiles cust_pro,
469: hz_customer_profiles site_pro,
470: ar_dunning_letter_sets dls,
548: p_customer_id IN hz_cust_accounts.cust_account_id%type
549: )
550: RETURN NUMBER is
551:
552: v_billto_site_use_id hz_cust_site_uses.site_use_id%type;
553:
554: /* Bug 2625779 - declaring cursor */
555: CURSOR c_site IS
556: SELECT su.site_use_id
553:
554: /* Bug 2625779 - declaring cursor */
555: CURSOR c_site IS
556: SELECT su.site_use_id
557: FROM hz_cust_site_uses su,
558: hz_cust_acct_sites acct_site
559: WHERE su.site_use_code = 'BILL_TO'
560: and su.cust_acct_site_id = acct_site.cust_acct_site_id
561: and acct_site.cust_account_id = p_customer_id