[Home] [Help]
PACKAGE BODY: APPS.POS_BANK_PAYEE_BO_PKG
Source
1 PACKAGE BODY pos_bank_payee_bo_pkg AS
2 /* $Header: POSSPBAPB.pls 120.3.12020000.2 2012/07/25 05:45:59 snelloru ship $ */
3 /*#
4 * Use this routine to get bank payee 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_payee_bo_tbl The bank payee bo table
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 Payee BO Table
15 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
16 */
17
18 PROCEDURE get_pos_bank_payee_bo_tbl(p_api_version IN NUMBER DEFAULT NULL,
19 p_init_msg_list IN VARCHAR2 DEFAULT NULL,
20 p_party_id IN NUMBER,
21 p_orig_system IN VARCHAR2,
22 p_orig_system_reference VARCHAR2,
23 x_pos_bank_payee_bo_tbl OUT NOCOPY pos_bank_payee_bo_tbl,
24 x_return_status OUT NOCOPY VARCHAR2,
25 x_msg_count OUT NOCOPY NUMBER,
26 x_msg_data OUT NOCOPY VARCHAR2) IS
27
28 l_pos_bank_payee_bo_tbl pos_bank_payee_bo_tbl := pos_bank_payee_bo_tbl();
29 l_party_id NUMBER;
30 BEGIN
31
32 IF p_party_id IS NULL OR p_party_id = 0 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
40 SELECT pos_bank_payee_bo(payee.ext_payee_id,
41 payee.payee_party_id,
42 payee.payment_function,
43 payee.exclusive_payment_flag,
44 payee.created_by,
45 payee.creation_date,
46 payee.last_updated_by,
47 payee.last_update_date,
48 payee.last_update_login,
49 payee.object_version_number,
50 payee.party_site_id,
51 payee.supplier_site_id,
52 payee.org_id,
53 payee.org_type,
54 payee.default_payment_method_code,
55 payee.ece_tp_location_code,
56 payee.bank_charge_bearer,
57 payee.bank_instruction1_code,
58 payee.bank_instruction2_code,
59 payee.bank_instruction_details,
60 payee.payment_reason_code,
61 payee.payment_reason_comments,
62 payee.inactive_date,
63 payee.payment_text_message1,
64 payee.payment_text_message2,
65 payee.payment_text_message3,
66 payee.delivery_channel_code,
67 payee.payment_format_code,
68 payee.settlement_priority,
69 payee.remit_advice_delivery_method,
70 payee.remit_advice_email,
71 payee.remit_advice_fax,
72 NULL,
73 NULL,
74 NULL,
75 NULL,
76 instr.instrument_id, -- Bug 12903268: add bank account id into the object
77 instr.order_of_preference, -- Bug 13529064: add account details
78 instr.start_date,
79 instr.end_date) BULK COLLECT
80 INTO l_pos_bank_payee_bo_tbl
81 FROM iby_external_payees_all payee, iby_pmt_instr_uses_all instr
82 WHERE payee_party_id = l_party_id
83 AND payee.ext_payee_id = instr.ext_pmt_party_id (+); -- Bug 12903268: Associate bank account id with payee.
84
85 x_pos_bank_payee_bo_tbl := l_pos_bank_payee_bo_tbl;
86 EXCEPTION
87 WHEN fnd_api.g_exc_error THEN
88
89 x_return_status := fnd_api.g_ret_sts_error;
90 x_msg_count := 1;
91 x_msg_data := SQLCODE || SQLERRM;
92 WHEN fnd_api.g_exc_unexpected_error THEN
93
94 x_return_status := fnd_api.g_ret_sts_unexp_error;
95 x_msg_count := 1;
96 x_msg_data := SQLCODE || SQLERRM;
97 WHEN OTHERS THEN
98
99 x_return_status := fnd_api.g_ret_sts_unexp_error;
100
101 x_msg_count := 1;
102 x_msg_data := SQLCODE || SQLERRM;
103 END get_pos_bank_payee_bo_tbl;
104 /*#
105 * Use this routine to create bank payee bo
106 * @param p_api_version The version of API
107 * @param p_init_msg_list The Initialization message list
108 * @param p_ext_payee_tab The external payee table
109 * @param x_pos_bank_payee_bo_tbl The bank payee bo table - iby_disbursement_setup_pub.external_payee_tab_type
110 * @param x_return_status The return status
111 * @param x_msg_count The message count
112 * @param x_msg_data The message data
113 * @param x_ext_payee_id_tab The external payee id table -iby_disbursement_setup_pub.ext_payee_id_tab_type
114 * @param x_ext_payee_status_tab The external payee status table - iby_disbursement_setup_pub.ext_payee_create_tab_type
115 * @rep:scope public
116 * @rep:lifecycle active
117 * @rep:displayname Create POS Bank Payee BO Table
118 * @rep:catagory BUSSINESS_ENTITY AP_SUPPLIER
119 */
120 PROCEDURE create_pos_bank_payee_bo_tbl(p_api_version IN NUMBER,
121 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
122 p_pos_bank_payee_bo_tbl IN pos_bank_payee_bo_tbl,
123 p_party_id IN NUMBER,
124 p_orig_system IN VARCHAR2,
125 p_orig_system_reference IN VARCHAR2,
126 p_create_update_flag IN VARCHAR2,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2) IS
130 v_row_exists NUMBER := 0;
131 p_ext_payee_tab iby_disbursement_setup_pub.external_payee_tab_type;
132 x_ext_payee_id_tab iby_disbursement_setup_pub.ext_payee_id_tab_type;
133 x_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_create_tab_type;
134 x_ext_payee_update_status_tab iby_disbursement_setup_pub.Ext_Payee_Update_Tab_Type;
135 l_party_id NUMBER;
136 p_ext_payee_id_tab iby_disbursement_setup_pub.Ext_Payee_ID_Tab_Type;
137 -- x_ext_payee_status_tab iby_disbursement_setup_pub.ext_payee_update_tab_type;
138 BEGIN
139
140 IF p_party_id IS NULL OR p_party_id = 0 THEN
144 l_party_id := p_party_id;
141 l_party_id := pos_supplier_bo_dep_pkg.get_party_id(p_orig_system,
142 p_orig_system_reference);
143 ELSE
145 END IF;
146
147 FOR i IN p_pos_bank_payee_bo_tbl.first .. p_pos_bank_payee_bo_tbl.last LOOP
148 p_ext_payee_tab(i).payee_party_id := l_party_id; -- p_pos_bank_payee_bo_tbl.payee_party_id;
149 p_ext_payee_tab(i).payment_function := p_pos_bank_payee_bo_tbl(i)
150 .payment_function;
151 p_ext_payee_tab(i).exclusive_pay_flag := p_pos_bank_payee_bo_tbl(i)
152 .exclusive_payment_flag;
153 p_ext_payee_tab(i).payee_party_site_id := p_pos_bank_payee_bo_tbl(i)
154 .party_site_id;
155 p_ext_payee_tab(i).supplier_site_id := p_pos_bank_payee_bo_tbl(i)
156 .supplier_site_id;
157 p_ext_payee_tab(i).payer_org_id := p_pos_bank_payee_bo_tbl(i)
158 .org_id;
159 p_ext_payee_tab(i).payer_org_type := p_pos_bank_payee_bo_tbl(i)
160 .org_type;
161 p_ext_payee_tab(i).default_pmt_method := p_pos_bank_payee_bo_tbl(i)
162 .default_payment_method_code;
163 p_ext_payee_tab(i).ece_tp_loc_code := p_pos_bank_payee_bo_tbl(i)
164 .ece_tp_location_code;
165 p_ext_payee_tab(i).bank_charge_bearer := p_pos_bank_payee_bo_tbl(i)
166 .bank_charge_bearer;
167 p_ext_payee_tab(i).bank_instr1_code := p_pos_bank_payee_bo_tbl(i)
168 .bank_instruction1_code;
169 p_ext_payee_tab(i).bank_instr2_code := p_pos_bank_payee_bo_tbl(i)
170 .bank_instruction2_code;
171 p_ext_payee_tab(i).bank_instr_detail := p_pos_bank_payee_bo_tbl(i)
172 .bank_instruction_details;
173 p_ext_payee_tab(i).pay_reason_code := p_pos_bank_payee_bo_tbl(i)
174 .payment_reason_code;
175 p_ext_payee_tab(i).pay_reason_com := p_pos_bank_payee_bo_tbl(i)
176 .payment_reason_comments;
177 p_ext_payee_tab(i).inactive_date := p_pos_bank_payee_bo_tbl(i)
178 .inactive_date;
179 p_ext_payee_tab(i).pay_message1 := p_pos_bank_payee_bo_tbl(i)
180 .payment_text_message1;
181 p_ext_payee_tab(i).pay_message2 := p_pos_bank_payee_bo_tbl(i)
182 .payment_text_message2;
183 p_ext_payee_tab(i).pay_message3 := p_pos_bank_payee_bo_tbl(i)
184 .payment_text_message3;
185 p_ext_payee_tab(i).delivery_channel := p_pos_bank_payee_bo_tbl(i)
186 .delivery_channel_code;
187 p_ext_payee_tab(i).pmt_format := p_pos_bank_payee_bo_tbl(i)
188 .payment_format_code;
189 p_ext_payee_tab(i).settlement_priority := p_pos_bank_payee_bo_tbl(i)
190 .settlement_priority;
191 p_ext_payee_tab(i).remit_advice_delivery_method := p_pos_bank_payee_bo_tbl(i)
192 .remit_advice_delivery_method;
193 p_ext_payee_tab(i).remit_advice_email := p_pos_bank_payee_bo_tbl(i)
194 .remit_advice_email;
195 p_ext_payee_tab(i).edi_payment_format := p_pos_bank_payee_bo_tbl(i)
196 .payment_format_code;
197 p_ext_payee_tab(i).edi_transaction_handling := p_pos_bank_payee_bo_tbl(i)
198 .edi_transaction_handling;
199 p_ext_payee_tab(i).edi_payment_method := p_pos_bank_payee_bo_tbl(i)
200 .edi_payment_method;
201 p_ext_payee_tab(i).edi_remittance_method := p_pos_bank_payee_bo_tbl(i)
202 .edi_remittance_method;
203 p_ext_payee_tab(i).edi_remittance_instruction := p_pos_bank_payee_bo_tbl(i)
204 .edi_remittance_instruction;
205 p_ext_payee_id_tab(i).ext_payee_id := p_pos_bank_payee_bo_tbl(i)
206 .ext_payee_id;
207 END LOOP;
208 IF p_create_update_flag = 'U' THEN
209
210 iby_disbursement_setup_pub.update_external_payee(p_api_version,
211 p_init_msg_list,
212 p_ext_payee_tab,
213 p_ext_payee_id_tab,
214 x_return_status,
215 x_msg_count,
216 x_msg_data,
217 x_ext_payee_update_status_tab);
218 ELSIF p_create_update_flag = 'C' THEN
219
220 iby_disbursement_setup_pub.create_external_payee(p_api_version,
221 p_init_msg_list,
222 p_ext_payee_tab,
223 x_return_status,
224 x_msg_count,
225 x_msg_data,
226 x_ext_payee_id_tab,
227 x_ext_payee_status_tab);
228 END IF;
229 EXCEPTION
230 WHEN fnd_api.g_exc_error THEN
231
232 x_return_status := fnd_api.g_ret_sts_error;
233 fnd_msg_pub.count_and_get(p_count => x_msg_count,
234 p_data => x_msg_data);
235 WHEN fnd_api.g_exc_unexpected_error THEN
236
237 x_return_status := fnd_api.g_ret_sts_unexp_error;
238 fnd_msg_pub.count_and_get(p_count => x_msg_count,
239 p_data => x_msg_data);
240 WHEN OTHERS THEN
241
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243
244 fnd_msg_pub.count_and_get(p_count => x_msg_count,
245 p_data => x_msg_data);
246
247 END;
248
249 END pos_bank_payee_bo_pkg;