[Home] [Help]
PACKAGE BODY: APPS.IBY_IMPORT_PMT_ACK_DATA_PUB
Source
1 PACKAGE BODY IBY_IMPORT_PMT_ACK_DATA_PUB AS
2 /*$Header: ibyackb.pls 120.8 2011/04/29 11:33:29 sgogula noship $*/
3
4
5 /* ======================================================================*
6 | Global Data Types |
7 * ======================================================================*/
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_IMPORT_PMT_ACK_DATA_PUB';
18
19
20 FUNCTION Payment_Reference_Exists
21 (p_payment_ref IN iby_payments_all.payment_reference_number%TYPE)
22 RETURN BOOLEAN
23 IS
24 l_count NUMBER;
25 l_dbg_mod VARCHAR2(100) := G_PKG_NAME || '.Payment_Reference_Exists';
26
27 CURSOR c_pmt_reference
28 (ci_payment_ref IN iby_payments_all.payment_reference_number%TYPE)
29 IS
30 SELECT count(1)
31 FROM iby_payments_all
32 WHERE (payment_reference_number = ci_payment_ref);
33
34 BEGIN
35 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
36 iby_debug_pub.add('Enter',G_LEVEL_PROCEDURE,l_dbg_mod);
37 END IF;
38
39 IF (c_pmt_reference%ISOPEN) THEN CLOSE c_pmt_reference; END IF;
40
41 OPEN c_pmt_reference(p_payment_ref);
42 FETCH c_pmt_reference INTO l_count;
43 IF (c_pmt_reference%NOTFOUND) THEN
44 l_count := 0;
45 END IF;
46 CLOSE c_pmt_reference;
47
48 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
49 iby_debug_pub.add('Exit',G_LEVEL_PROCEDURE,l_dbg_mod);
50 END IF;
51
52 RETURN (l_count > 0);
53 END Payment_Reference_Exists;
54
55
56 FUNCTION Bank_Status_Exists
57 (p_bank_status_code IN iby_ack_pmt_all.bank_status_code%TYPE)
58 RETURN BOOLEAN
59 IS
60 l_count NUMBER;
61 l_dbg_mod VARCHAR2(100) := G_PKG_NAME || '.Bank_Status_Exists';
62
63 CURSOR c_bank_status
64 (ci_bank_status IN iby_ack_pmt_all.bank_status_code%TYPE)
65 IS
66 SELECT count(1)
67 FROM iby_bank_ack_statuses
68 WHERE bank_ack_status_code = p_bank_status_code
69 AND nvl(trunc(inactive_date), sysdate+10 ) > trunc (sysdate);
70
71 BEGIN
72 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
73 iby_debug_pub.add('Enter',G_LEVEL_PROCEDURE,l_dbg_mod);
74 END IF;
75
76 IF (c_bank_status%ISOPEN) THEN CLOSE c_bank_status; END IF;
77
78 OPEN c_bank_status(upper(p_bank_status_code));
79 FETCH c_bank_status INTO l_count;
80 IF (c_bank_status%NOTFOUND) THEN
81 l_count := 0;
82 END IF;
83 CLOSE c_bank_status;
84
85 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
86 iby_debug_pub.add('Exit',G_LEVEL_PROCEDURE,l_dbg_mod);
87 END IF;
88
89 RETURN (l_count > 0);
90 END Bank_Status_Exists;
91
92
93 FUNCTION Get_Payment_Ack_ASatus
94 (p_bank_status_code IN iby_ack_pmt_all.bank_status_code%TYPE)
95 RETURN VARCHAR2
96 IS
97 l_ack_status VARCHAR2(200);
98 l_dbg_mod VARCHAR2(100) := G_PKG_NAME || '.Get_Payment_Ack_ASatus';
99
100 CURSOR c_pmt_ack_status
101 (ci_bank_status IN iby_ack_pmt_all.bank_status_code%TYPE)
102 IS
103 SELECT payment_ack_status
104 FROM iby_bank_ack_statuses
105 WHERE bank_ack_status_code = ci_bank_status;
106
107 BEGIN
108 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
109 iby_debug_pub.add('Enter',G_LEVEL_PROCEDURE,l_dbg_mod);
110 END IF;
111
112 IF (c_pmt_ack_status%ISOPEN) THEN CLOSE c_pmt_ack_status; END IF;
113
114 OPEN c_pmt_ack_status(p_bank_status_code);
115 FETCH c_pmt_ack_status INTO l_ack_status;
116 IF (c_pmt_ack_status%NOTFOUND) THEN
117 l_ack_status := NULL;
118 END IF;
119 CLOSE c_pmt_ack_status;
120
121 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
122 iby_debug_pub.add('Exit',G_LEVEL_PROCEDURE,l_dbg_mod);
123 END IF;
124
125 RETURN l_ack_status;
126
127 END Get_Payment_Ack_ASatus;
128
129
130 FUNCTION Get_Payment_Id
131 (p_payment_ref IN iby_payments_all.payment_reference_number%TYPE)
132 RETURN NUMBER
133 IS
134 l_pmt_id NUMBER;
135 l_dbg_mod VARCHAR2(100) := G_PKG_NAME || '.Get_Payment_Id';
136
137 CURSOR c_pmt_id
138 (ci_payment_ref IN iby_payments_all.payment_reference_number%TYPE)
139 IS
140 SELECT payment_id
141 FROM iby_payments_all
142 WHERE payment_reference_number = ci_payment_ref;
143
144 BEGIN
145 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
146 iby_debug_pub.add('Enter',G_LEVEL_PROCEDURE,l_dbg_mod);
147 END IF;
148
149 IF (c_pmt_id%ISOPEN) THEN CLOSE c_pmt_id; END IF;
150
151 OPEN c_pmt_id(p_payment_ref);
152 FETCH c_pmt_id INTO l_pmt_id;
153 IF (c_pmt_id%NOTFOUND) THEN
154 l_pmt_id := NULL;
155 END IF;
156 CLOSE c_pmt_id;
157
158 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
159 iby_debug_pub.add('Exit',G_LEVEL_PROCEDURE,l_dbg_mod);
160 END IF;
161
162 RETURN l_pmt_id;
163
164 END Get_Payment_Id;
165
166
167 PROCEDURE Create_Acknowlegment(
168 P_PAYMENT_REF_NUMBER IN NUMBER,
169 P_PMT_INSTRUCTION_REF_NUMBER IN NUMBER,
170 P_BANK_STATUS_CODE IN VARCHAR2,
171 P_ACKNOWLEDGEMENT_DATE IN DATE,
172 P_BANK_ASSIGNED_REF_CODE1 IN VARCHAR2,
173 P_BANK_ASSIGNED_REF_CODE2 IN VARCHAR2,
174 P_PAYER_NAME IN VARCHAR2,
175 P_PAYER_BANK_NAME IN VARCHAR2,
176 P_PAYER_BANK_BRANCH_NAME IN VARCHAR2,
177 P_PAYER_BANK_SWIFT_CODE IN VARCHAR2,
178 P_PAYER_BANK_ACCOUNT_NUMBER IN VARCHAR2,
179 P_PAYER_BANK_IBAN_NUMBER IN VARCHAR2,
180 P_PAYEE_NAME IN VARCHAR2,
181 P_PAYEE_BANK_NAME IN VARCHAR2,
182 P_PAYEE_BANK_BRANCH_NAME IN VARCHAR2,
183 P_PAYEE_BANK_SWIFT_CODE IN VARCHAR2,
184 P_PAYEE_BANK_ACCOUNT_NUMBER IN VARCHAR2,
185 P_PAYEE_BANK_IBAN_NUMBER IN VARCHAR2,
186 P_PAYMENT_AMOUNT IN NUMBER,
187 P_BANK_CHARGE_AMOUNT IN NUMBER,
188 P_PAYMENT_CURRENCY_CODE IN VARCHAR2,
189 P_EXCHANGE_RATE IN NUMBER,
190 P_VALUE_DATE IN DATE,
191 P_REQUESTED_EXECUTION_DATE IN DATE,
192 P_P_REJECT_DTLS_TBL_TYPE IN Reject_dtls_tbl_type,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_return_message OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER )
196
197 IS
198
199 l_dbg_mod VARCHAR2(100) := G_PKG_NAME || '.Create_Acknowlegment';
200
201 l_calculated_amt NUMBER;
202 l_calculated_bank_charge_amt NUMBER;
203 l_ack_pmt_id NUMBER;
204 l_pmt_id NUMBER;
205 l_pmt_ack_status iby_ack_pmt_all.payment_ack_status%TYPE;
206 l_counter NUMBER;
207
208 l_resp_id fnd_responsibility_tl.responsibility_id%TYPE;
209
210 l_return_status VARCHAR2(200);
211 l_return_message VARCHAR2(200);
212 l_msg_count NUMBER;
213 l_appl_name VARCHAR2(100);
214 l_init_msg_list VARCHAR2(200);
215
216 CURSOR get_app_name IS
217 SELECT APPLICATION_SHORT_NAME
218 FROM FND_APPLICATION
219 WHERE APPLICATION_ID = FND_GLOBAL.resp_appl_id;
220
221 l_access NUMBER;
222 BEGIN
223
224 IF( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
225 iby_debug_pub.add('Enter',G_LEVEL_PROCEDURE,l_dbg_mod);
226 END IF;
227
228 FND_MSG_PUB.initialize();
229
230 IF (P_PAYMENT_REF_NUMBER IS NULL)
231 THEN
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 fnd_message.set_name('IBY', 'IBY_PMT_REF_NUMBER_MISSING');
234 fnd_msg_pub.ADD;
235 RAISE FND_API.G_EXC_ERROR;
236 END IF;
237
238 IF (get_app_name%ISOPEN) THEN CLOSE get_app_name; END IF;
239
240 IF MO_GLOBAL.is_mo_init_done = 'N' THEN
241
242 OPEN get_app_name;
243 FETCH get_app_name INTO l_appl_name;
244 IF (get_app_name%NOTFOUND) THEN
245 l_appl_name := NULL;
246 END IF;
247 CLOSE get_app_name;
248
249 -- Set MOAC security
250
251 IF ( l_appl_name IS NULL)
252 THEN
253 fnd_message.set_name('IBY', 'IBY_NO_PMT_ACCESS');
254 fnd_msg_pub.ADD;
255 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
256 RAISE fnd_api.g_exc_error;
257 END IF;
258 MO_GLOBAL.init(l_appl_name);
259
260 END IF;
261 BEGIN
262 SELECT 0
263 INTO l_access
264 FROM dual
265 WHERE EXISTS
266 (SELECT NULL
267 FROM iby_payments_all
268 WHERE payment_reference_number = P_PAYMENT_REF_NUMBER
269 AND org_id <> -1
270 AND MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');
271
272 l_access := 0;
273
274 EXCEPTION
275 WHEN NO_DATA_FOUND THEN
276 l_access := 1;
277 WHEN OTHERS THEN
278 RAISE;
279 END;
280
281 IF NOT (l_access = 1) THEN
282 fnd_message.set_name('IBY', 'IBY_NO_PMT_ACCESS');
283 fnd_msg_pub.add;
284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
285 RAISE fnd_api.g_exc_error;
286 END IF;
287
288
289
290 IF (Payment_Reference_Exists(P_PAYMENT_REF_NUMBER)) THEN
291 IF( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
292 iby_debug_pub.add('Payment_Reference_Exists for ' || P_PAYMENT_REF_NUMBER,
293 G_LEVEL_PROCEDURE,l_dbg_mod);
294 END IF;
295
296 IF (NOT Bank_Status_Exists(P_BANK_STATUS_CODE))THEN
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 fnd_message.set_name('IBY', 'IBY_PMT_ACK_STATUS_NOT_EXISTS');
299 fnd_msg_pub.ADD;
300 RAISE FND_API.G_EXC_ERROR;
301 END IF;
302
303 l_calculated_amt := P_PAYMENT_AMOUNT * P_EXCHANGE_RATE;
304 l_calculated_bank_charge_amt := P_BANK_CHARGE_AMOUNT * P_EXCHANGE_RATE;
305 l_pmt_ack_status := Get_Payment_Ack_ASatus(P_BANK_STATUS_CODE);
306
307 -- Inserting record in to IBY_ACK_PMT_ALL
308
309 select iby_ack_pmt_all_s.NEXTVAL into l_ack_pmt_id from dual;
310
311 INSERT INTO iby_ack_pmt_all
312 ( ack_pmt_id, payment_ref_number,payment_instruction_ref_number
313 ,bank_status_code,payment_ack_status,acknowledgement_date,bank_assigned_ref_code1, bank_assigned_ref_code2
314 ,payer_name,payer_bank_name,payer_bank_branch_name,payer_bank_swift_code,payer_bank_account_number,payer_bank_iban_number
315 ,payee_name,payee_bank_name,payee_bank_branch_name,payee_bank_swift_code,payee_bank_account_number,payee_bank_iban_number
316 ,payment_amount,bank_charge_amount,payment_currency_code
317 ,exchange_rate,calculated_payment_amount,calculated_bank_charge_amount
318 ,value_date,requested_execution_date
319 ,created_by,creation_date,last_updated_by,last_update_date,last_update_login
320 )
321 VALUES
322 ( l_ack_pmt_id, P_PAYMENT_REF_NUMBER,P_PMT_INSTRUCTION_REF_NUMBER
323 ,P_BANK_STATUS_CODE,l_pmt_ack_status,P_ACKNOWLEDGEMENT_DATE,P_BANK_ASSIGNED_REF_CODE1, P_BANK_ASSIGNED_REF_CODE2
324 ,P_PAYER_NAME,P_PAYER_BANK_NAME,P_PAYER_BANK_BRANCH_NAME,P_PAYER_BANK_SWIFT_CODE,P_PAYER_BANK_ACCOUNT_NUMBER,P_PAYER_BANK_IBAN_NUMBER
325 ,P_PAYEE_NAME,P_PAYEE_BANK_NAME,P_PAYEE_BANK_BRANCH_NAME,P_PAYEE_BANK_SWIFT_CODE,P_PAYEE_BANK_ACCOUNT_NUMBER,P_PAYEE_BANK_IBAN_NUMBER
326 ,P_PAYMENT_AMOUNT,P_BANK_CHARGE_AMOUNT,P_PAYMENT_CURRENCY_CODE
327 ,P_EXCHANGE_RATE,l_calculated_amt,l_calculated_bank_charge_amt
328 ,P_VALUE_DATE,P_REQUESTED_EXECUTION_DATE
329 ,fnd_global.user_id,SYSDATE,fnd_global.user_id,SYSDATE,fnd_global.login_id
330 );
331
332
333 -- Inserting record in to IBY_ACK_PMT_ERRORS
334 IF ( P_P_REJECT_DTLS_TBL_TYPE IS NOT NULL) THEN
335 IF P_P_REJECT_DTLS_TBL_TYPE.COUNT > 0 THEN
336 l_counter := P_P_REJECT_DTLS_TBL_TYPE.FIRST;
337
338 WHILE (l_counter <= P_P_REJECT_DTLS_TBL_TYPE.LAST)
339 LOOP
340 INSERT INTO iby_ack_pmt_errors
341 ( ack_pmt_error_id , ack_pmt_id , bank_error_code ,bank_error_message , bank_error_location
342 , created_by,creation_date,last_updated_by,last_update_date,last_update_login
343 )
344 VALUES
345 ( IBY_ACK_PMT_ERRORS_S.NEXTVAL, l_ack_pmt_id , P_P_REJECT_DTLS_TBL_TYPE(l_counter).BANK_ERROR_CODE
346 ,P_P_REJECT_DTLS_TBL_TYPE(l_counter).BANK_ERROR_MESSAGE ,P_P_REJECT_DTLS_TBL_TYPE(l_counter).BANK_ERROR_LOCATION
347 ,fnd_global.user_id,SYSDATE,fnd_global.user_id,SYSDATE,fnd_global.login_id
348 );
349
350 l_counter := l_counter + 1;
351 END LOOP;
352 END IF;
353 END IF;
354
355
356 -- Update IBY_PAYMENTS_ALL set ACKNOWLEDGED_FLAG to Y
357
358 UPDATE iby_payments_all
359 SET acknowledged_flag = 'Y'
360 WHERE (payment_reference_number = P_PAYMENT_REF_NUMBER);
361
362
363 -- Call AP API to update AP_CHECKS_ALL.ACKNOWLEDGED_FLAG to Y
364
365 l_pmt_id := Get_Payment_Id(P_PAYMENT_REF_NUMBER);
366
367 IF( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
368 iby_debug_pub.add('Payment Id: ' || l_pmt_id,
369 G_LEVEL_PROCEDURE,l_dbg_mod);
370 END IF;
371
372
373 AP_PMT_ACKNOWLEDGE.mark_check_acknowledged(
374 l_init_msg_list, l_pmt_id , l_return_status ,l_msg_count , l_return_message
375 );
376
377
378 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
379 NULL;
380 ELSE
381 IF( G_LEVEL_ERROR >= G_CURRENT_RUNTIME_LEVEL) THEN
382 iby_debug_pub.add('Error From AP_PMT_ACKNOWLEDGE.mark_check_acknowledged: '||l_return_message, G_LEVEL_PROCEDURE,l_dbg_mod);
383 END IF;
384 x_return_status := l_return_status;
385 RAISE fnd_api.g_exc_error;
386 END IF;
387
388 ELSE
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 fnd_message.set_name('IBY', 'IBY_PMT_ACK_STATUS_NOT_EXISTS');
391 fnd_msg_pub.ADD;
392 RAISE FND_API.G_EXC_ERROR;
393 END IF;
394 COMMIT;
395
396 x_return_status := 'S';
397 x_return_message := 'SUCCESS';
398
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 ROLLBACK;
402 x_return_status := FND_API.G_RET_STS_ERROR;
403 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
404 p_count => x_msg_count,
405 p_data => x_return_message);
406 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
407 iby_debug_pub.add('Exception : ' || x_return_message);
408 END IF;
409 WHEN OTHERS THEN
410 ROLLBACK;
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
413 p_count => x_msg_count,
414 p_data => x_return_message);
415 x_return_message := 'SQLCODE: ' || SQLCODE || ' SQLERRM: ' || SQLERRM ;
416
417 END Create_Acknowlegment;
418
419
420
421 END IBY_IMPORT_PMT_ACK_DATA_PUB;