1 PACKAGE BODY GMF_AR_GET_CUST_CURRENCY as
2 /* $Header: gmfcstub.pls 115.0 99/07/16 04:16:20 porting shi $ */
3 cursor cur_ar_get_cust_currency( custid number,
4 orgid number,
5 siteuseid number) is
6 select BAU.CUSTOMER_ID,
7 BAU.CUSTOMER_SITE_USE_ID,
8 BAC.CURRENCY_CODE
9 from AP_BANK_ACCOUNT_USES_ALL BAU,
10 AP_BANK_ACCOUNTS_ALL BAC
11 where ((BAU.customer_id = nvl(custid, BAU.customer_id)
12 and BAU.customer_site_use_id =
13 nvl(siteuseid, BAU.customer_site_use_id))
14 or (BAU.customer_id = nvl(custid, BAU.customer_id)
15 and BAU.customer_site_use_id is null))
16 and upper(BAU.primary_flag) like 'Y%'
17 and BAU.external_bank_account_id = BAC.bank_account_id
18 and nvl(bau.org_id,0) = nvl(orgid, nvl(bau.org_id,0))
19 order by BAU.customer_id, BAU.customer_site_use_id;
20
21 procedure AR_GET_CUST_CURRENCY (cust_id in number,
22 site_use_id in number,
23 currency_code out varchar2,
24 porg_id in number,
25 row_to_fetch in out number,
26 error_status out number) is
27
28 no_of_site_accounts number;
29 no_of_cust_accounts number;
30 customer_id number;
31 cust_site_use_id number;
32
33 more_than_1_primary_ac_defined exception;
34 no_primary_ac_defined exception;
35
36 BEGIN
37
38 no_of_site_accounts := 1;
39 no_of_cust_accounts := 1;
40
41 select count(*)
42 into no_of_site_accounts
43 from AP_BANK_ACCOUNT_USES_ALL BAU
44 where BAU.customer_id = nvl(cust_id, BAU.customer_id)
45 and BAU.customer_site_use_id =
46 nvl(site_use_id, BAU.customer_site_use_id)
47 and upper(bau.primary_flag) like 'Y%';
48
49 if no_of_site_accounts > 1 then
50 raise more_than_1_primary_ac_defined;
51 end if;
52
53 if no_of_site_accounts = 0 then
54 select count(*)
55 into no_of_cust_accounts
56 from AP_BANK_ACCOUNT_USES_ALL BAU
57 where BAU.customer_id = nvl(cust_id, BAU.customer_id)
58 and BAU.customer_site_use_id IS NULL
59 and upper(bau.primary_flag) like 'Y%'
60 and nvl(bau.org_id,0) = nvl(porg_id, nvl(bau.org_id,0));
61
62 end if;
63
64 if no_of_cust_accounts > 1 then
65 raise more_than_1_primary_ac_defined;
66 end if;
67
68 if no_of_cust_accounts = 0 then
69 raise no_primary_ac_defined;
70 end if;
71
72 if NOT cur_ar_get_cust_currency%ISOPEN then
73 open cur_ar_get_cust_currency( cust_id, porg_id, site_use_id);
74 end if;
75
76 fetch cur_ar_get_cust_currency
77 into customer_id, cust_site_use_id, currency_code;
78
79 if cur_ar_get_cust_currency%NOTFOUND then
80 error_status := 100;
81 close cur_ar_get_cust_currency;
82 currency_code := gmf_gl_get_base_cur.get_base_cur(porg_id);
83 end if;
84
85 if row_to_fetch = 1 and cur_ar_get_cust_currency%ISOPEN then
86 close cur_ar_get_cust_currency;
87 end if;
88
89 exception
90
91 when more_than_1_primary_ac_defined then
92 currency_code := gmf_gl_get_base_cur.get_base_cur(porg_id);
93
94 when no_primary_ac_defined then
95 currency_code := gmf_gl_get_base_cur.get_base_cur(porg_id);
96
97 when others then
98 error_status := SQLCODE;
99
100 end AR_GET_CUST_CURRENCY;
101 END GMF_AR_GET_CUST_CURRENCY;
[Home] [Help]