DBA Data[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;