[Home] [Help]
PACKAGE BODY: APPS.POS_BANK_ACCOUNT_BO_PKG
Source
1 PACKAGE BODY pos_bank_account_bo_pkg AS
2 /* $Header: POSSPBAAB.pls 120.4.12020000.5 2013/04/05 06:27:08 ramkandu ship $ */
3 /*#
4 * Use this routine to get bank accont bo
5 * @param p_api_version The version of API
6 * @param p_init_msg_list The Initialization message list
7 * @param p_party_id The party_id
8 * @param x_pos_bank_account_bo_tbl The bank account bo
9 * @param x_return_status The return status
10 * @param x_msg_count The message count
11 * @param x_msg_data The message data
12 * @rep:scope public
13 * @rep:lifecycle active
14 * @rep:displayname Get POS Bank Account BO Table
15 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
16 */
17 PROCEDURE get_pos_bank_account_bo_tbl(p_api_version IN NUMBER DEFAULT NULL,
18 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
19 p_party_id IN NUMBER,
20 p_orig_system IN VARCHAR2,
21 p_orig_system_reference IN VARCHAR2,
22 x_pos_bank_account_bo_tbl OUT NOCOPY pos_bank_account_bo_tbl,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2) IS
26
27 l_pos_bank_account_bo_tbl pos_bank_account_bo_tbl := pos_bank_account_bo_tbl();
28 l_party_id NUMBER := 0;
29
30 BEGIN
31
32 IF p_party_id IS NULL THEN
33 l_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
34 p_orig_system_reference);
35 ELSE
36 l_party_id := p_party_id;
37 END IF;
38
39 -- Bug 12795884: Removed hash columns for account number and IBAN to avoid XML conversion error
40 SELECT pos_bank_account_bo(eb.ext_bank_account_id,
41 eb.bank_id,
42 eb.country_code,
43 bp.party_name,
44 bapr.bank_or_branch_number,
45 eb.branch_id,
46 br.party_name,
47 brpr.bank_or_branch_number,
48 branchca.class_code,
49 s.location_id,
50 branchcp.eft_swift_code,
51 eb.ext_bank_account_id,
52 eb.bank_account_name,
53 eb.masked_bank_account_num,
54 eb.currency_code,
55 eb.description,
56 eb.check_digits,
57 decode(eb.currency_code, NULL, 'Y', 'N'),
58 eb.bank_account_name_alt,
59 eb.short_acct_name,
60 eb.account_suffix,
61 eb.masked_iban,
62 ow.account_owner_party_id,
63 op.party_name,
64 eb.account_classification,
65 eb.bank_account_type,
66 eb.agency_location_code,
67 eb.start_date,
68 eb.end_date,
69 eb.payment_factor_flag,
70 eb.foreign_payment_use_flag,
71 eb.exchange_rate_agreement_num,
72 eb.exchange_rate_agreement_type,
73 eb.exchange_rate,
74 eb.hedging_contract_reference,
75 eb.secondary_account_reference,
76 eb.attribute_category,
77 eb.attribute1,
78 eb.attribute2,
79 eb.attribute3,
80 eb.attribute4,
81 eb.attribute5,
82 eb.attribute6,
83 eb.attribute7,
84 eb.attribute8,
85 eb.attribute9,
86 eb.attribute10,
87 eb.attribute11,
88 eb.attribute12,
89 eb.attribute13,
90 eb.attribute14,
91 eb.attribute15,
92 eb.object_version_number,
93 eb.bank_account_num_electronic,
94 NULL,
95 NULL,
96 NULL
97 /*,
98 brpr.bank_code,
99 eb.encrypted*/)
100
101 BULK COLLECT
102 INTO l_pos_bank_account_bo_tbl
103 FROM hz_organization_profiles bapr,
104 hz_organization_profiles brpr,
105 hz_parties bp,
106 hz_party_sites s,
107 iby_account_owners ow,
108 hz_parties br,
109 hz_parties op,
110 iby_ext_bank_accounts eb,
111 hz_code_assignments branchca,
112 hz_contact_points branchcp,
113
114 -- Bug 13096283/13586778: Publish bank account info for non-primary owners of factor account
115 -- Part 1: Get all suppliers that own same bank account(s) with the current supplier (l_party_id)
116 (SELECT DISTINCT a1.account_owner_party_id,
117 a1.ext_bank_account_id
118 FROM iby_account_owners a1,
119 iby_external_payees_all payee,
120 iby_pmt_instr_uses_all instr
121 WHERE payee.ext_payee_id = instr.ext_pmt_party_id
122 AND payee_party_id = l_party_id
123 AND instr.instrument_id = a1.ext_bank_account_id
124 AND instr.instrument_type = 'BANKACCOUNT'
125 AND instr.payment_function = 'PAYABLES_DISB'
126 ) supp
127 -- End Bug 13096283 Part 1
128 WHERE eb.bank_id = bp.party_id(+)
129 AND eb.bank_id = bapr.party_id(+)
130 AND eb.branch_id = br.party_id(+)
131 AND eb.branch_id = brpr.party_id(+)
132 AND eb.ext_bank_account_id = ow.ext_bank_account_id(+)
133 AND ow.primary_flag(+) = 'Y'
134 AND nvl(ow.end_date, SYSDATE + 10) > SYSDATE
135 AND ow.account_owner_party_id = op.party_id(+)
136 AND (br.party_id = s.party_id(+))
137 AND (s.identifying_address_flag(+) = 'Y')
138 AND (branchcp.owner_table_name(+) = 'HZ_PARTIES')
139 AND (branchcp.owner_table_id(+) = eb.branch_id)
140 AND (branchcp.contact_point_type(+) = 'EFT')
141 AND (nvl(branchcp.status(+), 'A') = 'A')
142 AND (branchca.class_category(+) = 'BANK_BRANCH_TYPE') -- Bug 13586778: Publish Branch Type
143 AND (branchca.owner_table_name(+) = 'HZ_PARTIES')
144 AND (branchca.owner_table_id(+) = eb.branch_id)
145 AND SYSDATE BETWEEN -- Bug 14621927: Check effect end date to prevent publishing ineffective bank record
146 trunc(bapr.effective_start_date) AND
147 nvl(trunc(bapr.effective_end_date), SYSDATE + 1)
148 AND SYSDATE BETWEEN
149 trunc(brpr.effective_start_date) AND
150 nvl(trunc(brpr.effective_end_date), SYSDATE + 1)
151 AND op.party_id = supp.account_owner_party_id -- Bug 13096283 Part 2: Join to get primary owner info for the bank account
152 AND eb.ext_bank_account_id = supp.ext_bank_account_id -- Bug 13586778: only select account that belongs to the current supplier.
153 AND branchca.primary_flag = 'Y'; -- Bug 16205262: Check primary_flag to prevent publishing duplicated bank account info.
154
155 x_pos_bank_account_bo_tbl := l_pos_bank_account_bo_tbl;
156 EXCEPTION
157 WHEN fnd_api.g_exc_error THEN
158 x_return_status := fnd_api.g_ret_sts_error;
159 fnd_msg_pub.count_and_get(p_count => x_msg_count,
160 p_data => x_msg_data);
161 WHEN fnd_api.g_exc_unexpected_error THEN
162 x_return_status := fnd_api.g_ret_sts_unexp_error;
163 fnd_msg_pub.count_and_get(p_count => x_msg_count,
164 p_data => x_msg_data);
165 WHEN OTHERS THEN
166 x_return_status := fnd_api.g_ret_sts_unexp_error;
167 fnd_msg_pub.count_and_get(p_count => x_msg_count,
168 p_data => x_msg_data);
169
170 END get_pos_bank_account_bo_tbl;
171
172 /*#
173 * Use this routine to create bank account bo
174 * @param p_api_version The version of API
175 * @param p_init_msg_list The Initialization message list
176 * @param p_ext_bank_acct_rec The external bank account record
177 * @param x_acct_id The bank account id
178 * @param x_response The result record type
179 * @param x_return_status The return status
180 * @param x_msg_count The message count
181 * @param x_msg_data The message data
182 * @rep:scope public
183 * @rep:lifecycle active
184 * @rep:displayname Create POS Bank Account BO
185 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
186 */
187
188 PROCEDURE create_pos_bank_account_bo(p_api_version IN NUMBER,
189 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
190 p_party_id IN NUMBER,
191 p_orig_system IN VARCHAR2,
192 p_orig_system_reference IN VARCHAR2,
193 p_create_update_flag IN VARCHAR2,
194 p_pos_bank_account_bo IN pos_bank_account_bo_tbl,
195 x_acct_id OUT NOCOPY NUMBER,
196 x_return_status OUT NOCOPY VARCHAR2,
197 x_msg_count OUT NOCOPY NUMBER,
198 x_msg_data OUT NOCOPY VARCHAR2)
199
200 IS
201 v_row_exists NUMBER := 0;
202 l_ext_bank_acct_rec iby_ext_bankacct_pub.extbankacct_rec_type;
203 l_response iby_fndcpt_common_pub.result_rec_type;
204
205 BEGIN
206
207 /*l_ext_bank_acct_rec := p_ext_bank_acct_rec;*/
208 FOR i IN p_pos_bank_account_bo.first .. p_pos_bank_account_bo.last LOOP
209 l_ext_bank_acct_rec.bank_account_id := p_pos_bank_account_bo(i).bank_account_id;
210 l_ext_bank_acct_rec.country_code := p_pos_bank_account_bo(i).country_code;
211 l_ext_bank_acct_rec.branch_id := p_pos_bank_account_bo(i).branch_id;
212 l_ext_bank_acct_rec.bank_id := p_pos_bank_account_bo(i).bank_id;
213 l_ext_bank_acct_rec.acct_owner_party_id := p_pos_bank_account_bo(i).primary_acct_owner_party_id;
214 l_ext_bank_acct_rec.bank_account_name := p_pos_bank_account_bo(i).bank_account_name;
215 l_ext_bank_acct_rec.bank_account_num := p_pos_bank_account_bo(i).bank_account_number;
216 l_ext_bank_acct_rec.currency := p_pos_bank_account_bo(i).currency_code;
217 l_ext_bank_acct_rec.iban := p_pos_bank_account_bo(i).iban_number;
218 l_ext_bank_acct_rec.check_digits := p_pos_bank_account_bo(i).check_digits;
219 l_ext_bank_acct_rec.multi_currency_allowed_flag := p_pos_bank_account_bo(i).multi_currency_allowed_flag;
220 l_ext_bank_acct_rec.alternate_acct_name := p_pos_bank_account_bo(i).alternate_account_name;
221 l_ext_bank_acct_rec.short_acct_name := p_pos_bank_account_bo(i).short_acct_name;
222 l_ext_bank_acct_rec.acct_type := p_pos_bank_account_bo(i).bank_account_type;
223 l_ext_bank_acct_rec.acct_suffix := p_pos_bank_account_bo(i).account_suffix;
224 l_ext_bank_acct_rec.description := p_pos_bank_account_bo(i).description;
225 l_ext_bank_acct_rec.agency_location_code := p_pos_bank_account_bo(i).agency_location_code;
226 l_ext_bank_acct_rec.foreign_payment_use_flag := p_pos_bank_account_bo(i).foreign_payment_use_flag;
227 l_ext_bank_acct_rec.exchange_rate_agreement_num := p_pos_bank_account_bo(i).exchange_rate_agreement_num;
228 l_ext_bank_acct_rec.exchange_rate_agreement_type := p_pos_bank_account_bo(i).exchange_rate_agreement_type;
229 l_ext_bank_acct_rec.exchange_rate := p_pos_bank_account_bo(i).exchange_rate;
230 l_ext_bank_acct_rec.payment_factor_flag := p_pos_bank_account_bo(i).payment_factor_flag;
231 l_ext_bank_acct_rec.status := p_pos_bank_account_bo(i).status;
232 l_ext_bank_acct_rec.end_date := p_pos_bank_account_bo(i).end_date;
233 l_ext_bank_acct_rec.start_date := p_pos_bank_account_bo(i).start_date;
234 l_ext_bank_acct_rec.hedging_contract_reference := p_pos_bank_account_bo(i).hedging_contract_reference;
235 l_ext_bank_acct_rec.attribute_category := p_pos_bank_account_bo(i).attribute_category;
236 l_ext_bank_acct_rec.attribute1 := p_pos_bank_account_bo(i).attribute1;
237 l_ext_bank_acct_rec.attribute2 := p_pos_bank_account_bo(i).attribute2;
238 l_ext_bank_acct_rec.attribute3 := p_pos_bank_account_bo(i).attribute3;
239 l_ext_bank_acct_rec.attribute4 := p_pos_bank_account_bo(i).attribute4;
240 l_ext_bank_acct_rec.attribute5 := p_pos_bank_account_bo(i).attribute5;
241 l_ext_bank_acct_rec.attribute6 := p_pos_bank_account_bo(i).attribute6;
242 l_ext_bank_acct_rec.attribute7 := p_pos_bank_account_bo(i).attribute7;
243 l_ext_bank_acct_rec.attribute8 := p_pos_bank_account_bo(i).attribute8;
244 l_ext_bank_acct_rec.attribute9 := p_pos_bank_account_bo(i).attribute9;
245 l_ext_bank_acct_rec.attribute10 := p_pos_bank_account_bo(i).attribute10;
246 l_ext_bank_acct_rec.attribute11 := p_pos_bank_account_bo(i).attribute11;
247 l_ext_bank_acct_rec.attribute12 := p_pos_bank_account_bo(i).attribute12;
248 l_ext_bank_acct_rec.attribute13 := p_pos_bank_account_bo(i).attribute13;
249 l_ext_bank_acct_rec.attribute14 := p_pos_bank_account_bo(i).attribute14;
250 l_ext_bank_acct_rec.attribute15 := p_pos_bank_account_bo(i).attribute15;
251 l_ext_bank_acct_rec.object_version_number := p_pos_bank_account_bo(i).object_version_number;
252 l_ext_bank_acct_rec.secondary_account_reference := p_pos_bank_account_bo(i).secondary_account_reference;
253 IF p_create_update_flag = 'C' THEN
254 iby_ext_bankacct_pub.create_ext_bank_acct(p_api_version,
255 p_init_msg_list,
256 l_ext_bank_acct_rec,
257 x_acct_id,
258 x_return_status,
259 x_msg_count,
260 x_msg_data,
261 l_response);
262 ELSIF p_create_update_flag = 'U' THEN
263 iby_ext_bankacct_pub.update_ext_bank_acct(p_api_version,
264 p_init_msg_list,
265 l_ext_bank_acct_rec,
266 x_return_status,
267 x_msg_count,
268 x_msg_data,
269 l_response);
270 END IF;
271 END LOOP;
272 EXCEPTION
273 WHEN fnd_api.g_exc_error THEN
274 x_return_status := fnd_api.g_ret_sts_error;
275 fnd_msg_pub.count_and_get(p_count => x_msg_count,
276 p_data => x_msg_data);
277 WHEN fnd_api.g_exc_unexpected_error THEN
278 x_return_status := fnd_api.g_ret_sts_unexp_error;
279 fnd_msg_pub.count_and_get(p_count => x_msg_count,
280 p_data => x_msg_data);
281 WHEN OTHERS THEN
282 x_return_status := fnd_api.g_ret_sts_unexp_error;
283 fnd_msg_pub.count_and_get(p_count => x_msg_count,
284 p_data => x_msg_data);
285 END create_pos_bank_account_bo;
286
287 END pos_bank_account_bo_pkg;