[Home] [Help]
PACKAGE BODY: APPS.GMF_AR_GET_CUST_BANK_ACCOUNTS
Source
1 PACKAGE BODY GMF_AR_GET_CUST_BANK_ACCOUNTS as
2 /* $Header: gmfbankb.pls 115.0 99/07/16 04:14:46 porting shi $ */
3 cursor cur_ar_get_cust_bank_accounts(start_date date,
4 end_date date,
5 cust_id number,
6 siteuseid number) is
7 select BAU.CUSTOMER_ID, BAU.CUSTOMER_SITE_USE_ID,
8 BAU.PRIMARY_FLAG, BAU.START_DATE,
9 BAU.END_DATE, BAC.BANK_ACCOUNT_NUM,
10 BAC.BANK_ACCOUNT_NAME, BAC.CURRENCY_CODE,
11 BAC.DESCRIPTION, BAC.MAX_CHECK_AMOUNT,
12 BAC.MIN_CHECK_AMOUNT, BAC.INACTIVE_DATE,
13 BAC.ASSET_CODE_COMBINATION_ID,
14 BAC.GAIN_CODE_COMBINATION_ID,
15 BAC.LOSS_CODE_COMBINATION_ID,
16 BAC.BANK_ACCOUNT_TYPE, BAC.MAX_OUTLAY,
17 BAC.MULTI_CURRENCY_FLAG, BAC.ACCOUNT_TYPE,
18 BAC.POOLED_FLAG, BAC.ZERO_AMOUNTS_ALLOWED,
19 BAU.ATTRIBUTE_CATEGORY, BAU.ATTRIBUTE1,
20 BAU.ATTRIBUTE2, BAU.ATTRIBUTE3,
21 BAU.ATTRIBUTE4, BAU.ATTRIBUTE5,
22 BAU.ATTRIBUTE6, BAU.ATTRIBUTE7,
23 BAU.ATTRIBUTE8, BAU.ATTRIBUTE9,
24 BAU.ATTRIBUTE10, BAU.ATTRIBUTE11,
25 BAU.ATTRIBUTE12, BAU.ATTRIBUTE13,
26 BAU.ATTRIBUTE14, BAU.ATTRIBUTE15,
27 BAU.CREATED_BY, BAU.CREATION_DATE,
28 BAU.LAST_UPDATE_DATE, BAU.LAST_UPDATED_BY
29 from AP_BANK_ACCOUNT_USES_ALL BAU,
30 AP_BANK_ACCOUNTS_ALL BAC
31 where ((BAU.customer_id = nvl(cust_id, BAU.customer_id)
32 and BAU.customer_site_use_id =
33 nvl(siteuseid, BAU.customer_site_use_id))
34 or (BAU.customer_id = nvl(cust_id, BAU.customer_id)
35 and BAU.customer_site_use_id is null))
36 and BAU.external_bank_account_id = BAC.bank_account_id
37 and BAU.last_update_date between
38 nvl(start_date, BAU.last_update_date)
39 and nvl(end_date, BAU.last_update_date)
40 order by BAU.customer_id, BAU.customer_site_use_id,
41 BAU.primary_flag desc;
42
43 procedure AR_GET_CUST_BANK_ACCOUNTS (cust_id in out number,
44 siteuseid in out number,
45 start_date in out date,
46 end_date in out date,
47 primary_flag out varchar2,
48 start_date_active out date,
49 end_date_active out date,
50 account_number out varchar2,
51 account_name out varchar2,
52 currency_code out varchar2,
53 description out varchar2,
54 max_check_amount out number,
55 min_check_amount out number,
56 inactive_date out date,
57 asset_ccid out number,
58 gain_ccid out number,
59 loss_ccid out number,
60 bank_account_type out varchar2,
61 max_outlay out varchar2,
62 multi_curr_flag out varchar2,
63 account_type out varchar2,
64 pooled_flag out varchar2,
65 zero_amt_allowed out varchar2,
66 attr_category out varchar2,
67 att1 out varchar2,
68 att2 out varchar2,
69 att3 out varchar2,
70 att4 out varchar2,
71 att5 out varchar2,
72 att6 out varchar2,
73 att7 out varchar2,
74 att8 out varchar2,
75 att9 out varchar2,
76 att10 out varchar2,
77 att11 out varchar2,
78 att12 out varchar2,
79 att13 out varchar2,
80 att14 out varchar2,
81 att15 out varchar2,
82 created_by out varchar2,
83 creation_date out date,
84 last_update_date out date,
85 last_updated_by out varchar2,
86 row_to_fetch in out number,
87 error_status out number) is
88
89 createdby number;
90 modifiedby number;
91
92 begin
93
94 if NOT cur_ar_get_cust_bank_accounts%ISOPEN then
95 open cur_ar_get_cust_bank_accounts(start_date, end_date,
96 cust_id, siteuseid);
97 end if;
98
99 fetch cur_ar_get_cust_bank_accounts
100 into cust_id, siteuseid, primary_flag,
101 start_date_active, end_date_active, account_number,
102 account_name, currency_code, description,
103 max_check_amount, min_check_amount, inactive_date,
104 asset_ccid, gain_ccid, loss_ccid,
105 bank_account_type, max_outlay, multi_curr_flag,
106 account_type, pooled_flag, zero_amt_allowed,
107 attr_category, att1, att2,
108 att3, att4, att5,
109 att6, att7, att8,
110 att9, att10, att11,
111 att12, att13, att14,
112 att15, createdby, creation_date,
113 last_update_date, modifiedby;
114
115 if cur_ar_get_cust_bank_accounts%NOTFOUND then
116 error_status := 100;
117 close cur_ar_get_cust_bank_accounts;
118 else
119 created_by := gmf_fnd_get_users.fnd_get_users(createdby);
120 last_updated_by := gmf_fnd_get_users.fnd_get_users(modifiedby);
121 end if;
122 if row_to_fetch = 1 and cur_ar_get_cust_bank_accounts%ISOPEN then
123 close cur_ar_get_cust_bank_accounts;
124 end if;
125
126 exception
127
128 when others then
129 error_status := SQLCODE;
130
131 end AR_GET_CUST_BANK_ACCOUNTS;
132 END GMF_AR_GET_CUST_BANK_ACCOUNTS;