[Home] [Help]
PACKAGE BODY: APPS.CE_STATEMENT_RECONS_PKG
Source
1 PACKAGE BODY ce_statement_recons_pkg AS
2 /* $Header: cestmreb.pls 120.4.12010000.2 2008/11/04 04:46:15 csutaria ship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 --l_DEBUG varchar2(1) := 'Y';
6
7 FUNCTION body_revision RETURN VARCHAR2 IS
8 BEGIN
9
10 RETURN '$Revision: 120.4.12010000.2 $';
11
12 END body_revision;
13
14 FUNCTION spec_revision RETURN VARCHAR2 IS
15 BEGIN
16
17 RETURN G_spec_revision;
18
19 END spec_revision;
20
21 --
22 -- PUBLIC FUNCTIONS
23 --
24
25 PROCEDURE check_unique(X_statement_line_id NUMBER,
26 X_reference_type VARCHAR2,
27 X_reference_id VARCHAR2,
28 X_current_record_flag VARCHAR2,
29 X_row_id VARCHAR2) IS
30 CURSOR chk_duplicates is
31 SELECT 'Duplicate'
32 FROM CE_STATEMENT_RECON_GT_V csr --CE_STATEMENT_RECONCILIATIONS csr
33 WHERE csr.statement_line_id = X_statement_line_id
34 AND csr.reference_type = X_reference_type
35 AND csr.reference_id = X_reference_id
36 AND csr.current_record_flag = X_current_record_flag
37 AND ( X_row_id is null
38 OR csr.row_id <> chartorowid(X_row_id));
39 dummy VARCHAR2(100);
40 BEGIN
41 OPEN chk_duplicates;
42 FETCH chk_duplicates INTO dummy;
43
44 IF chk_duplicates%FOUND THEN
45 CLOSE chk_duplicates;
46 fnd_message.set_name('CE', 'CE_DUPLICATE_EXCHANGE_RATE');
47 app_exception.raise_exception;
48 END IF;
49 CLOSE chk_duplicates;
50
51 EXCEPTION
52 WHEN app_exceptions.application_exception THEN
53 RAISE;
54 WHEN OTHERS THEN
55 fnd_message.set_name('CE', 'CE_UNHANDLED_EXCEPTION');
56 fnd_message.set_token('PROCEDURE', 'ce_statement_recons_pkg.check_unique');
57 RAISE;
58 END check_unique;
59
60 PROCEDURE Insert_Row( X_Row_id IN OUT NOCOPY VARCHAR2,
61 X_statement_line_id NUMBER,
62 X_reference_type VARCHAR2,
63 X_reference_id NUMBER,
64 X_org_id NUMBER,
65 X_legal_entity_id NUMBER,
66 X_reference_status VARCHAR2,
67 X_amount NUMBER DEFAULT NULL,
68 X_status_flag VARCHAR2,
69 X_action_flag VARCHAR2,
70 X_current_record_flag VARCHAR2,
71 X_auto_reconciled_flag VARCHAR2,
72 X_created_by NUMBER,
73 X_creation_date DATE,
74 X_last_updated_by NUMBER,
75 X_last_update_date DATE,
76 X_request_id NUMBER DEFAULT NULL,
77 X_program_application_id NUMBER DEFAULT NULL,
78 X_program_id NUMBER DEFAULT NULL,
79 X_program_update_date DATE DEFAULT NULL) IS
80
81 reference_type_tmp VARCHAR2(30);
82 X_cash_receipt_id NUMBER(15);
83 X_statement_type VARCHAR2(30);
84 X_org_id_tmp NUMBER(15);
85 Y_org_id NUMBER(15);
86 X_legal_entity_id_tmp NUMBER(15);
87 Y_legal_entity_id NUMBER(15);
88
89 CURSOR C_ROWID IS SELECT row_id FROM CE_STATEMENT_RECON_GT_V
90 WHERE statement_line_id = X_Statement_Line_Id
91 AND reference_type = reference_type_tmp
92 AND reference_id = X_Reference_Id
93 AND current_record_flag = x_current_record_flag;
94
95 CURSOR C_STMT_LOCK IS SELECT row_id, org_id, legal_entity_id
96 FROM CE_STATEMENT_RECON_GT_V
97 WHERE reference_type = reference_type_tmp AND
98 reference_id = X_reference_id AND
99 statement_line_id = X_statement_line_id AND
100 current_record_flag = 'Y'
101 FOR UPDATE of current_record_flag NOWAIT;
102
103 CURSOR C_LOCK IS SELECT row_id, org_id, legal_entity_id
104 FROM CE_STATEMENT_RECON_GT_V
105 WHERE reference_type = reference_type_tmp AND
106 reference_id = X_reference_id AND
107 current_record_flag = 'Y'
108 FOR UPDATE of current_record_flag NOWAIT;
109 BEGIN
110 IF l_DEBUG in ('Y', 'C') THEN
111 cep_standard.debug('>>CE_STATEMENT_RECONS_PKG.insert_row 1');
112 cep_standard.debug('X_Row_id = '|| X_Row_id || ', X_statement_line_id = ' || X_statement_line_id);
113 cep_standard.debug('X_reference_type ='|| X_reference_type ||', X_reference_id = '|| X_reference_id ||
114 ', X_org_id = '||X_org_id || ', X_legal_entity_id = '||X_legal_entity_id ||
115 ', X_reference_status ='|| X_reference_status);
116 cep_standard.debug('X_amount = ' || X_amount ||', X_status_flag = ' || X_status_flag ||
117 ', X_action_flag ='|| X_action_flag ||', X_current_record_flag = '|| X_current_record_flag ||
118 ', X_auto_reconciled_flag = ' ||X_auto_reconciled_flag);
119 END IF;
120
121 --
122 -- Map AR transactions types 'MISC' and 'CASH' to 'RECEIPT'
123 --
124 IF (X_reference_type IN ('MISC','CASH')) THEN
125 --
126 -- Check to see if the receipt has been Debit Memo Reversed
127 -- If the receipt is DM Reversed then assign the reference_type to be 'DM REVERSAL'
128 --
129
130 SELECT cash_receipt_id
131 INTO X_cash_receipt_id
132 FROM ar_cash_receipt_history_all
133 WHERE cash_receipt_history_id = X_reference_id;
134
135 BEGIN
136 SELECT trx_type
137 INTO X_statement_type
138 FROM ce_statement_lines
139 WHERE statement_line_id = X_statement_line_id;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 X_statement_type := 'X';
143 END;
144
145 IF (arp_cashbook.receipt_debit_memo_reversed(X_cash_receipt_id)='Y')
146 and (X_statement_type IN ('NSF', 'REJECTED')) THEN
147 reference_type_tmp := 'DM REVERSAL';
148 ELSE
149 reference_type_tmp := 'RECEIPT';
150 END IF;
151
152 ELSE
153 reference_type_tmp := X_reference_type;
154 END IF;
155
156 cep_standard.debug( ' reference_type_tmp ='||reference_type_tmp);
157
158 --
159 -- Lock the 'Y' in the CE_STATEMENT_RECONCILIATIONS
160 -- to make sure that there is not anybody else matcing this transaction
161 --
162 IF(X_reference_type = 'STATEMENT')THEN
163 OPEN C_STMT_LOCK;
164 FETCH C_STMT_LOCK INTO X_Row_Id, X_org_id_tmp, X_legal_entity_id_tmp;
165 if (C_STMT_LOCK%NOTFOUND) then
166 CLOSE C_STMT_LOCK;
167 ELSE
168 UPDATE CE_STATEMENT_RECONCILS_ALL SET current_record_flag = 'N'
169 WHERE rowid = X_Row_id;
170 CLOSE C_STMT_LOCK;
171 END IF;
172 ELSE
173 OPEN C_LOCK;
174 FETCH C_LOCK INTO X_Row_Id, X_org_id_tmp, X_legal_entity_id_tmp;
175 if (C_LOCK%NOTFOUND) then
176 CLOSE C_LOCK;
177 ELSE
178 UPDATE CE_STATEMENT_RECONCILS_ALL SET current_record_flag = 'N'
179 WHERE rowid = X_Row_id;
180 CLOSE C_LOCK;
181 END IF;
182 END IF;
183
184 IF l_DEBUG in ('Y', 'C') THEN
185 cep_standard.debug('INSERT INTO CE_STATEMENT_RECON_GT_V');
186 END IF;
187
188 cep_standard.debug( ' X_org_id_tmp ='||X_org_id_tmp );
189 cep_standard.debug( ' X_legal_entity_id_tmp ='||X_legal_entity_id_tmp );
190
191 IF (X_org_id is null) THEN
192 Y_org_id := X_org_id_tmp;
193 ELSE
194 Y_org_id := X_org_id;
195 END IF;
196 IF (X_legal_entity_id is null) THEN
197 Y_legal_entity_id := X_legal_entity_id_tmp;
198 ELSE
199 Y_legal_entity_id := X_legal_entity_id;
200 END IF;
201
202 cep_standard.debug( ' Y_org_id ='||Y_org_id);
203 cep_standard.debug( ' Y_legal_entity_id ='||Y_legal_entity_id);
204
205 INSERT INTO CE_STATEMENT_RECONCILS_ALL(
206 statement_line_id,
207 reference_type,
208 reference_id,
209 status_flag,
210 amount,
211 current_record_flag,
212 auto_reconciled_flag,
213 org_id,
214 legal_entity_id,
215 created_by,
216 creation_date,
217 last_updated_by,
218 last_update_date,
219 request_id,
220 program_application_id,
221 program_id,
222 program_update_date)
223 VALUES
224 (X_Statement_Line_Id,
225 Reference_Type_Tmp,
226 X_Reference_Id,
227 X_Status_flag,
228 X_amount,
229 X_Current_Record_Flag,
230 X_Auto_Reconciled_Flag,
231 Y_org_id,
232 Y_legal_entity_id,
233 X_Created_By,
234 X_Creation_Date,
235 X_last_updated_by,
236 X_last_update_date,
237 DECODE(X_auto_reconciled_flag,'Y',
238 X_request_id,
239 NULL),
240 DECODE(X_auto_reconciled_flag,'Y',
241 X_program_application_id,
242 NULL),
243 DECODE(X_auto_reconciled_flag,'Y',
244 X_program_id,
245 NULL),
246 DECODE(X_auto_reconciled_flag,'Y',
247 X_program_update_date,
248 NULL));
249
250 IF l_DEBUG in ('Y', 'C') THEN
251 cep_standard.debug('end INSERT INTO CE_STATEMENT_RECON_GT_V');
252 END IF;
253
254
255
256 OPEN C_ROWID;
257 FETCH C_ROWID INTO X_Row_Id;
258 IF l_DEBUG in ('Y', 'C') THEN
259 cep_standard.debug(' fetch X_Row_Id ' ||X_Row_Id);
260 END IF;
261 --if (C_ROWID%NOTFOUND) then
262 if (X_Row_Id is null ) then
263 CLOSE C_ROWID;
264
265 IF l_DEBUG in ('Y', 'C') THEN
266 cep_standard.debug('Raise NO_DATA_FOUND');
267 END IF;
268
269 Raise NO_DATA_FOUND;
270
271 IF l_DEBUG in ('Y', 'C') THEN
272 cep_standard.debug('end Raise NO_DATA_FOUND');
273 END IF;
274 end if;
275 IF l_DEBUG in ('Y', 'C') THEN
276 cep_standard.debug(' xx2 ');
277 END IF;
278 IF (X_status_flag = 'M') THEN
279 DELETE from CE_RECONCILIATION_ERRORS
280 WHERE statement_line_id = X_statement_line_id;
281 END IF;
282 CLOSE C_ROWID;
283 IF l_DEBUG in ('Y', 'C') THEN
284 cep_standard.debug('<<CE_STATEMENT_RECONS_PKG.insert_row 1');
285 END IF;
286 END Insert_Row;
287
288 PROCEDURE Insert_Row( X_Row_id IN OUT NOCOPY VARCHAR2,
289 X_statement_line_id NUMBER,
290 X_reference_type VARCHAR2,
291 X_reference_id NUMBER,
292 X_je_header_id NUMBER,
293 X_org_id NUMBER,
294 X_legal_entity_id NUMBER,
295 X_reference_status VARCHAR2,
296 X_amount NUMBER DEFAULT NULL,
297 X_status_flag VARCHAR2,
298 X_action_flag VARCHAR2,
299 X_current_record_flag VARCHAR2,
300 X_auto_reconciled_flag VARCHAR2,
301 X_created_by NUMBER,
302 X_creation_date DATE,
303 X_last_updated_by NUMBER,
304 X_last_update_date DATE,
305 X_request_id NUMBER DEFAULT NULL,
306 X_program_application_id NUMBER DEFAULT NULL,
307 X_program_id NUMBER DEFAULT NULL,
308 X_program_update_date DATE DEFAULT NULL) IS
309
310 reference_type_tmp VARCHAR2(30);
311 X_cash_receipt_id NUMBER(15);
312 X_statement_type VARCHAR2(30);
313 X_org_id_tmp NUMBER(15);
314 Y_org_id NUMBER(15);
315 X_legal_entity_id_tmp NUMBER(15);
316 Y_legal_entity_id NUMBER(15);
317 CURSOR C_ROWID IS SELECT row_id FROM CE_STATEMENT_RECON_GT_V
318 WHERE statement_line_id = X_Statement_Line_Id
319 AND reference_type = reference_type_tmp
320 AND reference_id = X_Reference_Id
321 AND je_header_id = X_je_header_id
322 AND current_record_flag = x_current_record_flag;
323
324 CURSOR C_LOCK IS SELECT row_id, org_id, legal_entity_id
325 FROM CE_STATEMENT_RECON_GT_V
326 WHERE reference_type = reference_type_tmp AND
327 reference_id = X_reference_id AND
328 je_header_id = X_je_header_id AND
329 -- statement_line_id = X_Statement_Line_Id AND bug 6888494
330 current_record_flag = 'Y'
331 FOR UPDATE of current_record_flag NOWAIT;
332 BEGIN
333 IF l_DEBUG in ('Y', 'C') THEN
334 cep_standard.debug('>>CE_STATEMENT_RECONS_PKG.insert_row 2');
335 END IF;
336 --
337 -- Map AR transactions types 'MISC' and 'CASH' to 'RECEIPT'
338 --
339 IF (X_reference_type IN ('MISC','CASH')) THEN
340 --
341 -- Check to see if the receipt has been Debit Memo Reversed
342 -- If the receipt is DM Reversed then assign the reference_type to be 'DM REVERSAL'
343 --
344 SELECT cash_receipt_id
345 INTO X_cash_receipt_id
346 FROM ar_cash_receipt_history_all
347 WHERE cash_receipt_history_id = X_reference_id;
348
349 BEGIN
350 SELECT trx_type
351 INTO X_statement_type
352 FROM ce_statement_lines
353 WHERE statement_line_id = X_statement_line_id;
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 X_statement_type := 'X';
357 END;
358
359
360 IF (arp_cashbook.receipt_debit_memo_reversed(X_cash_receipt_id)='Y')
361 and (X_statement_type IN ('NSF', 'REJECTED')) THEN
362 reference_type_tmp := 'DM REVERSAL';
363 ELSE
364 reference_type_tmp := 'RECEIPT';
365 END IF;
366
367 ELSE
368 reference_type_tmp := X_reference_type;
369 END IF;
370
371 cep_standard.debug( ' reference_type_tmp ='||reference_type_tmp);
372
373
374 --
375 -- Lock the 'Y' in the CE_STATEMENT_RECONCILIATIONS
376 -- to make sure that there is not anybody else matcing this transaction
377 --
378 OPEN C_LOCK;
379 FETCH C_LOCK INTO X_Row_Id, x_org_id_tmp, x_legal_entity_id_tmp;
380 if (C_LOCK%NOTFOUND) then
381 CLOSE C_LOCK;
382 ELSE
383 UPDATE CE_STATEMENT_RECONCILS_ALL SET current_record_flag = 'N'
384 WHERE rowid = X_Row_id;
385 CLOSE C_LOCK;
386 END IF;
387 IF l_DEBUG in ('Y', 'C') THEN
388 cep_standard.debug('INSERT INTO CE_STATEMENT_RECONCILS_ALL 2');
389 END IF;
390
391 cep_standard.debug( ' X_org_id_tmp ='||X_org_id_tmp );
392 cep_standard.debug( ' X_legal_entity_id_tmp ='||X_legal_entity_id_tmp );
393
394 IF (X_org_id is null) THEN
395 Y_org_id := X_org_id_tmp;
396 ELSE
397 Y_org_id := X_org_id;
398 END IF;
399 IF (X_legal_entity_id is null) THEN
400 Y_legal_entity_id := X_legal_entity_id_tmp;
401 ELSE
402 Y_legal_entity_id := X_legal_entity_id;
403 END IF;
404
405 cep_standard.debug( ' reference_type_tmp ='||reference_type_tmp);
406 cep_standard.debug( ' Y_org_id ='||Y_org_id);
410 INSERT INTO CE_STATEMENT_RECONCILS_ALL(
407 cep_standard.debug( ' Y_legal_entity_id ='||Y_legal_entity_id);
408
409
411 statement_line_id,
412 reference_type,
413 reference_id,
414 je_header_id,
415 status_flag,
416 amount,
417 current_record_flag,
418 auto_reconciled_flag,
419 org_id,
420 legal_entity_id,
421 created_by,
422 creation_date,
423 last_updated_by,
424 last_update_date,
425 request_id,
426 program_application_id,
427 program_id,
428 program_update_date)
429 VALUES
430 (X_Statement_Line_Id,
431 Reference_Type_Tmp,
432 X_Reference_Id,
433 X_je_header_id,
434 X_Status_flag,
435 X_amount,
436 X_Current_Record_Flag,
437 X_Auto_Reconciled_Flag,
438 Y_org_id,
439 Y_legal_entity_id,
440 X_Created_By,
441 X_Creation_Date,
442 X_last_updated_by,
443 X_last_update_date,
444 DECODE(X_auto_reconciled_flag,'Y',
445 X_request_id,
446 NULL),
447 DECODE(X_auto_reconciled_flag,'Y',
448 X_program_application_id,
449 NULL),
450 DECODE(X_auto_reconciled_flag,'Y',
451 X_program_id,
452 NULL),
453 DECODE(X_auto_reconciled_flag,'Y',
454 X_program_update_date,
455 NULL));
456
457 IF l_DEBUG in ('Y', 'C') THEN
458 cep_standard.debug('end INSERT INTO CE_STATEMENT_RECONCILS_ALL 2');
459 END IF;
460 OPEN C_ROWID;
461 FETCH C_ROWID INTO X_Row_Id;
462 if (C_ROWID%NOTFOUND) then
463 CLOSE C_ROWID;
464 Raise NO_DATA_FOUND;
465 end if;
466 IF (X_status_flag = 'M') THEN
467 DELETE from CE_RECONCILIATION_ERRORS
468 WHERE statement_line_id = X_statement_line_id;
469 END IF;
470 CLOSE C_ROWID;
471 IF l_DEBUG in ('Y', 'C') THEN
472 cep_standard.debug('<<CE_STATEMENT_RECONS_PKG.insert_row 2');
473 END IF;
474 END Insert_Row;
475
476
477 PROCEDURE Lock_Row ( X_Row_id IN OUT NOCOPY VARCHAR2,
478 X_statement_line_id NUMBER,
479 X_reference_type VARCHAR2,
480 X_reference_id NUMBER,
481 X_status VARCHAR2,
482 X_cleared_when_matched VARCHAR2,
483 X_current_record_flag VARCHAR2,
484 X_auto_reconciled_flag VARCHAR2) IS
485 Counter NUMBER;
486 CURSOR C IS
487 SELECT statement_line_id,
488 reference_type,
489 reference_id,
490 status_flag,
491 current_record_flag,
492 auto_reconciled_flag
493 FROM CE_STATEMENT_RECON_GT_V
494 WHERE statement_line_id = X_statement_line_id AND
495 reference_type = X_reference_type AND
496 reference_id = X_reference_id
497 FOR UPDATE of Statement_Line_Id,reference_type, reference_id NOWAIT;
498 Recinfo C%ROWTYPE;
499 BEGIN
500 Counter := 0;
501 LOOP
502 BEGIN
503 Counter := Counter + 1;
504 OPEN C;
505 FETCH C INTO Recinfo;
506 if (C%NOTFOUND) then
507 CLOSE C;
508 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
509 APP_EXCEPTION.RAISE_EXCEPTION;
510 end if;
511 CLOSE C;
512 if ( (Recinfo.statement_line_id = X_Statement_Line_Id)
513 AND (Recinfo.reference_type = X_Reference_Type)
514 AND (Recinfo.reference_id = X_Reference_Id)
515 AND ( (Recinfo.status_flag = X_Status)
516 OR ( (Recinfo.status_flag IS NULL)
517 AND (X_Status IS NULL)))
518 AND ( (Recinfo.current_record_flag = X_Current_Record_Flag)
519 OR ( (Recinfo.current_record_flag IS NULL)
520 AND (X_Current_Record_Flag IS NULL)))
521 AND ( (Recinfo.auto_reconciled_flag = X_Auto_Reconciled_Flag)
522 OR ( (Recinfo.auto_reconciled_flag IS NULL)
523 AND (X_Auto_Reconciled_Flag IS NULL)))
524 ) THEN
525 return;
526 else
527 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
528 APP_EXCEPTION.RAISE_EXCEPTION;
529 end if;
530 END;
531 end LOOP;
532 END Lock_Row;
533
534 PROCEDURE Update_Row( X_Row_id IN OUT NOCOPY VARCHAR2,
535 X_statement_line_id NUMBER,
536 X_reference_type VARCHAR2,
537 X_reference_id NUMBER,
538 X_status VARCHAR2,
539 X_cleared_when_matched VARCHAR2,
540 X_current_record_flag VARCHAR2,
541 X_auto_reconciled_flag VARCHAR2,
542 X_created_by NUMBER,
543 X_creation_date DATE,
544 X_last_updated_by NUMBER,
545 X_last_update_date DATE) IS
546 BEGIN
547 UPDATE CE_STATEMENT_RECONCILS_ALL
548 SET
549 statement_line_id = X_Statement_Line_Id,
550 reference_type = X_Reference_Type,
551 reference_id = X_Reference_Id,
552 status_flag = X_Status,
553 current_record_flag = X_Current_Record_Flag,
554 auto_reconciled_flag = X_Auto_Reconciled_Flag,
555 last_updated_by = X_Last_Updated_By,
556 last_update_date = X_Last_Update_Date
557 WHERE rowid = X_Row_Id;
558
559 if (SQL%NOTFOUND) then
560 Raise NO_DATA_FOUND;
561 end if;
562 END Update_Row;
563
564 PROCEDURE Delete_Row(X_Row_id VARCHAR2) IS
565 BEGIN
566 DELETE FROM CE_STATEMENT_RECONCILS_ALL
567 WHERE rowid = X_Row_Id;
568
569 if (SQL%NOTFOUND) then
570 Raise NO_DATA_FOUND;
571 end if;
572 END Delete_Row;
573
574 END ce_statement_recons_pkg;