[Home] [Help]
PACKAGE BODY: APPS.PSA_MF_MISC_PKG
Source
1 PACKAGE BODY psa_mf_misc_pkg AS
2 /* $Header: PSAMFMXB.pls 120.14 2006/09/13 13:39:16 agovil ship $ */
3
4
5 -- declare global variables
6 g_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
7 g_set_of_books_id gl_sets_of_books.set_of_books_id%TYPE;
8 g_run_id NUMBER(15);
9 --===========================FND_LOG.START=====================================
10 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
11 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
12 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
13 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
14 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
15 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
16 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAMFMXB.psa_mf_misc_pkg.';
17 --===========================FND_LOG.END=======================================
18
19
20 -- Local functions
21
22 FUNCTION misc_rct_changed(p_status IN VARCHAR2 ) RETURN boolean;
23
24 FUNCTION create_distributions (
25 errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY VARCHAR2,
27 p_mode IN VARCHAR2,
28 p_error_message OUT NOCOPY VARCHAR2,
29 x_status IN VARCHAR2,
30 x_cash_receipt_hist_id IN NUMBER)
31 RETURN BOOLEAN;
32
33 FUNCTION generate_distributions (
34 errbuf OUT NOCOPY VARCHAR2,
35 retcode OUT NOCOPY VARCHAR2,
36 p_cash_receipt_id IN NUMBER,
37 p_set_of_books_id IN NUMBER,
38 p_run_id IN NUMBER,
39 p_error_message OUT NOCOPY VARCHAR2,
40 p_report_only IN VARCHAR2 DEFAULT 'N') RETURN BOOLEAN
41 IS
42
43 CURSOR c_crh_status
44 IS
45 SELECT cash_receipt_history_id,status, reversal_cash_receipt_hist_id,
46 prv_stat_cash_receipt_hist_id
47 FROM ar_cash_receipt_history
48 WHERE cash_receipt_id = p_cash_receipt_id
49 ORDER BY cash_receipt_history_id;
50
51 CURSOR c_match_ccid (p_status IN varchar2)
52 IS
53 SELECT
54 mf.misc_cash_distribution_id,
55 mf.distribution_ccid,
56 ar.code_combination_id
57 FROM
58 psa_mf_misc_dist_all mf,
59 ar_misc_cash_distributions ar
60 WHERE
61 mf.reference1 = p_status
62 AND mf.misc_cash_distribution_id = ar.misc_cash_distribution_id
63 AND ar.cash_receipt_id = g_cash_receipt_id ;
64
65 match_ccid_rec c_match_ccid%ROWTYPE;
66 mf_dist_count NUMBER;
67 -- ========================= FND LOG ===========================
68 l_full_path VARCHAR2(100) := g_path || 'generate_distributions';
69 -- ========================= FND LOG ===========================
70
71 BEGIN
72
73 -- All processing will be checked for the Receipt history status since it is possible to
74 -- have multiple MFAR accounts for every core distribution.
75 -- When Receipt is remitted, a MFAR Remittance A/c is created.
76 -- When Receipt is cleared, a MFAR Cash A/c is created.
77
78
79 /*
80 ## Iniitialize global variables.
81 */
82
83 g_cash_receipt_id := p_cash_receipt_id;
84 g_set_of_books_id := p_set_of_books_id;
85 g_run_id := p_run_id;
86 retcode := 'F';
87
88 -- ========================= FND LOG ===========================
89 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside Generate_distributions ');
90 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
91 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
92 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cash_receipt_id --> ' || p_cash_receipt_id);
93 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id --> ' || p_set_of_books_id);
94 psa_utils.debug_other_string(g_state_level,l_full_path,' p_run_id --> ' || p_run_id);
95 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
96 -- ========================= FND LOG ===========================
97
98
99 /*
100 ## Check if Distribution lines already exist in MF Tables
101 */
102
103 FOR I IN c_crh_status
104 LOOP
105
106 -- Changes for Cash Mgt
107 -- Before checking for existence of records, the status should also be used to classify the
108 -- records in psa_mf_misc_dist_all
109
110 SELECT COUNT(*) INTO mf_dist_count
111 FROM psa_mf_misc_dist_all psa,
112 ar_misc_cash_distributions ar
113 WHERE psa.misc_cash_distribution_id = ar.misc_cash_distribution_id
114 AND ar.cash_receipt_id = g_cash_receipt_id
115 AND psa.reference1 = I.status;
116
117
118 -- ========================= FND LOG ===========================
119 psa_utils.debug_other_string(g_state_level,l_full_path,
120 ' Generate_distributions --> mf_dist_count ' || mf_dist_count);
121 -- ========================= FND LOG ===========================
122
123
124 IF (mf_dist_count > 0) THEN -- 1 IF
125 -- ========================= FND LOG ===========================
126 psa_utils.debug_other_string(g_state_level,l_full_path,
127 ' Generate_distributions --> mf_dist_count > 0 ');
128 -- ========================= FND LOG ===========================
129
130 /*
131 ## MF lines already created
132 ## check if they have been modified:
133 */
134
135 IF misc_rct_changed(I.status) THEN -- 2 IF
136
137 /*
138 ## There is count mismatch between core and MF Distribution
139 ## delete all mf distributions and re-create them.
140 */
141
142 IF NOT (PSA_MF_MISC_PKG.create_distributions (
143 errbuf => errbuf,
144 retcode => retcode,
145 p_mode => 'R',
146 p_error_message => p_error_message,
147 x_status => i.status,
148 x_cash_receipt_hist_id => i.cash_receipt_history_id )) THEN -- 3 IF
149
150 IF p_error_message IS NOT NULL OR retcode = 'F' THEN -- 4 IF
151 -- ========================= FND LOG ===========================
152 psa_utils.debug_other_string(g_excep_level,l_full_path,
153 ' Generate_distributions --> Error Message --> '
154 || p_error_message);
155 -- ========================= FND LOG ===========================
156
157 IF NVL(p_report_only,'N') = 'N' THEN -- 5 IF
158 -- ========================= FND LOG ===========================
159 psa_utils.debug_other_string(g_excep_level,l_full_path,
160 ' Generate_distributions --> p_report_only --> N'
161 ||' : This is not for reporting purpose so end processing. ');
162 -- ========================= FND LOG ===========================
163 RETURN FALSE;
164 END IF; -- 5 END IF
165 END IF; -- 4 END IF
166 ELSE
167 -- ========================= FND LOG ===========================
168 psa_utils.debug_other_string(g_state_level,l_full_path,
169 ' Generate_distributions -> PSA_MF_MISC_PKG.create_distributions --> TRUE ');
170 -- ========================= FND LOG ===========================
171 END IF; -- 3 END IF
172
173 ELSE
174
175 /*
176 ## There is no count mismatch
177 ## check if the CCID for original lines were altered
178 */
179
180 OPEN c_match_ccid(I.status);
181 LOOP
182
183 FETCH c_match_ccid INTO match_ccid_rec;
184 EXIT WHEN c_match_ccid%NOTFOUND;
185
186 IF (match_ccid_rec.distribution_ccid <> match_ccid_rec.code_combination_id) THEN
187 -- ========================= FND LOG ===========================
188 psa_utils.debug_other_string(g_state_level,l_full_path,
189 ' Generate_distributions --> match_ccid_rec.distribution_ccid'
190 || ' <> match_ccid_rec.code_combination_id');
191 -- ========================= FND LOG ===========================
192
193 IF NOT (PSA_MF_MISC_PKG.create_distributions (
194 errbuf => errbuf,
195 retcode => retcode,
196 p_mode => 'R',
197 p_error_message => p_error_message,
198 x_status => i.status,
199 x_cash_receipt_hist_id => i.cash_receipt_history_id )) THEN
200 IF p_error_message IS NOT NULL OR retcode = 'F' THEN
201 -- ========================= FND LOG ===========================
202 psa_utils.debug_other_string(g_excep_level,l_full_path,
203 ' Generate_distributions --> Error Message --> '
204 || p_error_message);
205 -- ========================= FND LOG ===========================
206
207 IF NVL(p_report_only,'N') = 'N' THEN
208 -- ========================= FND LOG ===========================
209 psa_utils.debug_other_string(g_excep_level,l_full_path,
210 ' Generate_distributions --> p_report_only --> N :'
211 ||' This is not for reporting purpose so end processing. ');
212 -- ========================= FND LOG ===========================
213 RETURN FALSE;
214 END IF;
215 END IF;
216
217 ELSE
218 -- ========================= FND LOG ===========================
219 psa_utils.debug_other_string(g_state_level,l_full_path,
220 ' Generate_distributions -> PSA_MF_MISC_PKG.create_distributions --> TRUE ');
221 -- ========================= FND LOG ===========================
222 END IF;
223 END IF;
224
225 END LOOP;
226
227 CLOSE c_match_ccid;
228 END IF; -- 2 END IF
229
230 ELSE
231
232 /*
233 ## mf_dist_count = 0 .
234 ## Distribution will be created for the first time.
235 */
236
237 IF NOT (PSA_MF_MISC_PKG.create_distributions (
238 errbuf => errbuf,
239 retcode => retcode,
240 p_mode => 'C',
241 p_error_message => p_error_message,
242 x_status => i.status,
243 x_cash_receipt_hist_id => i.cash_receipt_history_id )) THEN
244
245 IF p_error_message IS NOT NULL OR retcode = 'F' THEN
246 -- ========================= FND LOG ===========================
247 psa_utils.debug_other_string(g_excep_level,l_full_path,
248 ' Generate_distributions --> Error Message --> '
249 || p_error_message);
250 -- ========================= FND LOG ===========================
251
252 IF NVL(p_report_only,'N') = 'N' THEN
253 -- ========================= FND LOG ===========================
254 psa_utils.debug_other_string(g_excep_level,l_full_path,
255 ' Generate_distributions --> p_report_only --> N:'
256 ||' This is not for reporting purpose so end processing. ');
257 -- ========================= FND LOG ===========================
258 RETURN FALSE;
259 END IF;
260 END IF;
261
262 ELSE
263 -- ========================= FND LOG ===========================
264 psa_utils.debug_other_string(g_state_level,l_full_path,
265 ' Generate_distributions -> PSA_MF_MISC_PKG.create_distributions --> FALSE ');
266 -- ========================= FND LOG ===========================
267 END IF;
268
269 END IF; -- 1 END IF
270 END LOOP;
271
272 -- ========================= FND LOG ===========================
273 psa_utils.debug_other_string(g_state_level,l_full_path,
274 ' Generate_distributions -> END ');
275 -- ========================= FND LOG ===========================
276
277 retcode := 'S';
278 RETURN TRUE;
279
280 EXCEPTION
281 WHEN OTHERS THEN -- here
282 p_error_message:= 'EXCEPTION - OTHERS PACKAGE - PSA_MF_MISC_PKG.GENERATE_DISTRIBUTIONS - '||SQLERRM;
283 -- ========================= FND LOG ===========================
284 psa_utils.debug_other_string(g_excep_level,l_full_path,p_error_message);
285 psa_utils.debug_unexpected_msg(l_full_path);
286 -- ========================= FND LOG ===========================
287 retcode := 'F';
288 RETURN FALSE;
289
290 END Generate_distributions;
291
292 /********************************** CREATE DISTRIBUTIONS ************************************/
293
294 FUNCTION create_distributions (
295 errbuf OUT NOCOPY VARCHAR2,
296 retcode OUT NOCOPY VARCHAR2,
297 p_mode IN VARCHAR2,
298 p_error_message OUT NOCOPY VARCHAR2,
299 x_status IN VARCHAR2,
300 x_cash_receipt_hist_id IN NUMBER) RETURN BOOLEAN
301
302 IS
303
304 CURSOR c_misc_dist (p_cash_rct_id IN NUMBER)
305 IS
306 SELECT m.misc_cash_distribution_id,
307 m.code_combination_id,m.amount,
308 m.gl_date,status,reversal_date
309 FROM
310 ar_misc_cash_distributions m,
311 ar_cash_receipts cr
312 WHERE m.created_from LIKE DECODE(x_status,'REVERSED','%REVERSE%','%ARRERCT%') AND
313 m.cash_receipt_id = cr.cash_receipt_id AND
314 cr.cash_receipt_id = p_cash_rct_id;
315
316
317 CURSOR c_misc_dist_new (p_cash_rect_id IN NUMBER)
318 IS
319 SELECT
320 m.misc_cash_distribution_id,
321 m.code_combination_id,
322 m.amount,
323 m.gl_date,
324 status,
325 reversal_date
326 FROM
327 ar_misc_cash_distributions m,
328 ar_cash_receipts cr
329 WHERE
330 m.cash_receipt_id = cr.cash_receipt_id
331 AND m.gl_posted_date IS NOT NULL
332 AND cr.cash_receipt_id = p_cash_rect_id;
333
334
335 CURSOR c_cash_ccid(p_cr_id IN NUMBER)
336 IS
337 SELECT cash_ccid , remittance_ccid
338 FROM
339 ar_receipt_method_accounts acc,
340 ar_receipt_methods rm,
341 ar_cash_receipts cr
342 WHERE
343 acc.receipt_method_id = rm.receipt_method_id
344 AND rm.receipt_method_id = cr.receipt_method_id
345 AND cr.cash_receipt_id = p_cr_id
346 AND cr.remittance_bank_account_id = acc.remit_bank_acct_use_id;
347
348
349 -- Bug3963328
350 -- AND SYSDATE BETWEEN NVL(acc.start_date, SYSDATE) AND NVL(acc.end_date, SYSDATE);
351
352 -- Cash Mgt - c_cash_ccid is modified to choose the A/c from ar_cash_receipt_history_all
353 -- This table stores the ccid based on the activity - Remittance A/c or Cash A/c
354
355 CURSOR c_mfar_dist_rec IS
356 SELECT crh.status curstatus, crh1.status prevstatus
357 FROM ar_cash_receipt_history crh, ar_cash_receipt_history crh1
358 WHERE crh.cash_receipt_history_id = x_cash_receipt_hist_id AND
359 crh.cash_receipt_history_id = crh1.reversal_cash_receipt_hist_id(+);
360
361 CURSOR c_reversal_ccid(p_misc_dist_id in number) IS
362 SELECT cash_ccid FROM psa_mf_misc_dist_all
363 WHERE reference1 = 'REMITTED'
364 AND misc_cash_distribution_id = p_misc_dist_id;
365
366 l_reversal_ccid NUMBER;
367 l_bank_cash_ccid NUMBER;
368 l_mfar_ccid_rec c_cash_ccid%ROWTYPE;
369 l_misc_dist_rec c_misc_dist%ROWTYPE;
370 misc_dist_new_rec c_misc_dist_new%ROWTYPE;
371 l_distribution_ccid NUMBER(15); -- core distribution
372 l_mf_cash_ccid NUMBER(15); -- Multi-fund cash A/c
373 x_dummy VARCHAR2(250);
374 l_count NUMBER;
375 cr_status VARCHAR2(15);
376 create_dist_flag VARCHAR2(1);
377 psa_count NUMBER(15);
378 first_rec_flag VARCHAR2(1);
379 flex_build_error EXCEPTION;
380 ccid_rec c_cash_ccid%ROWTYPE;
381 l_mfar_dist_rec c_mfar_dist_rec%ROWTYPE;
382 l_primary_ccid NUMBER(15);
383 -- ========================= FND LOG ===========================
384 l_full_path VARCHAR2(100) := g_path || 'create_distributions';
385 -- ========================= FND LOG ===========================
386
387 BEGIN
388
389 retcode := 'F';
390
391 -- ========================= FND LOG ===========================
392 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside Create_distributions ');
393 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS: ');
394 psa_utils.debug_other_string(g_state_level,l_full_path,' =========== ');
395 psa_utils.debug_other_string(g_state_level,l_full_path,' p_mode --> ' || p_mode);
396 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
397 -- ========================= FND LOG ===========================
398
399 OPEN c_cash_ccid (g_cash_receipt_id);
400 FETCH c_cash_ccid INTO ccid_rec;
401 CLOSE c_cash_ccid;
402
403 create_dist_flag := p_mode;
404
405 FOR I IN c_cash_ccid(g_cash_receipt_id)
406 LOOP
407
408 /*========================================================================
409 If mode = 'R' Count of core and distribution lines are not equal.
410 If this condition happens before POSTING, the possibilities are
411 -- User creates a new Core distribution
412 -- User deletes a core distribution
413 THEN we delete all MFAR entries and re-create them based on latest core distributions.
414
415 If count mismatch happens after posting,
416 the ONLY possibility is REVERSAL of Misc. Receipt since user cannot update/delete/insert
417 core distributions after posting.
418 When REVERSAL occurs, one reversing line is created for each original line.
419 Multi-Fund logic should CREATE MF lines ONLY FOR THESE NEW REVERSING LINES
420 because the MF entrie corresponding to the original core distributions have
421 already been posted. Deleting and re-creating them will result in duplication
422 when they get posted to GL.
423 The cursor identifies the Core distribution rows that are reversing lines.
424 =====================================================================================*/
425
426 -- ========================= FND LOG ===========================
427 psa_utils.debug_other_string(g_state_level,l_full_path,
428 ' Create_distributions --> g_cash_receipt_id --> '
429 || g_cash_receipt_id);
430 -- ========================= FND LOG ===========================
431
432 IF create_dist_flag = 'R' THEN
433
434 SELECT status INTO cr_status
435 FROM ar_cash_receipts
436 WHERE cash_receipt_id = g_cash_receipt_id;
437
438 -- ========================= FND LOG ===========================
439 psa_utils.debug_other_string(g_state_level,l_full_path,
440 ' Create_distributions --> cr_status --> ' || cr_status);
441 -- ========================= FND LOG ===========================
442
443 IF cr_status IN ('NSF','STOP','REV') THEN
444
445 -- we delete records from psa_mf_misc_dist_all when the Dist records
446 --have not yet been posted and the Receipt has been reversed.
447 --These records are re-created by the code written below.
448
449 DELETE FROM psa_mf_misc_dist_all
450 WHERE reference5 = g_cash_receipt_id
451 AND posting_control_id = -3;
452
453 -- ========================= FND LOG ===========================
454 psa_utils.debug_other_string(g_state_level,l_full_path,
455 ' Create_distributions --> deleting from pas_mf_misc_dist_all for pstctrl -> -3 ');
456 psa_utils.debug_other_string(g_state_level,l_full_path,
457 ' Create_distributions --> records deleted --> ' || SQL%ROWCOUNT);
458 -- ========================= FND LOG ===========================
459
460 OPEN c_misc_dist_new(g_cash_receipt_id);
461 LOOP
462
463 FETCH c_misc_dist_new INTO misc_dist_new_rec;
464 EXIT WHEN c_misc_dist_new%NOTFOUND;
465
466 SELECT COUNT(*) INTO psa_count
467 FROM psa_mf_misc_dist_all
468 WHERE misc_cash_distribution_id = misc_dist_new_rec.misc_cash_distribution_id
469 AND posting_control_id >0;
470
471 -- ========================= FND LOG ===========================
472 psa_utils.debug_other_string(g_state_level,l_full_path,
473 ' Create_distributions --> psa_count --> ' || psa_count);
474 -- ========================= FND LOG ===========================
475
476 IF psa_count = 0 THEN
477 -- ========================= FND LOG ===========================
478 psa_utils.debug_other_string(g_state_level,l_full_path,
479 ' Create_distributions --> calling PSA_MFAR_UTILS.override_segments ');
480 -- ========================= FND LOG ===========================
481
482 OPEN c_mfar_dist_rec;
483 FETCH c_mfar_dist_rec INTO l_mfar_dist_rec;
484 EXIT WHEN c_mfar_dist_rec%NOTFOUND;
485
486 IF l_mfar_dist_rec.curstatus = 'REMITTED' THEN
487 l_primary_ccid := ccid_rec.remittance_ccid;
488 ELSIF l_mfar_dist_rec.curstatus = 'CLEARED' THEN
489 l_primary_ccid := ccid_rec.cash_ccid;
490 ELSIF l_mfar_dist_rec.curstatus = 'REVERSED' AND l_mfar_dist_rec.curstatus = 'CLEARED' THEN
491 l_primary_ccid := ccid_rec.cash_ccid;
492 ELSIF l_mfar_dist_rec.curstatus = 'REVERSED' AND l_mfar_dist_rec.curstatus = 'REMITTED' THEN
493 l_primary_ccid := ccid_rec.remittance_ccid;
494 END IF;
495
496 CLOSE c_mfar_dist_rec;
497
498 -- ========================= FND LOG ===========================
499 psa_utils.debug_other_string(g_state_level,l_full_path,
500 ' Create_distributions --> l_primary_ccid --> ' || l_primary_ccid);
501 psa_utils.debug_other_string(g_state_level,l_full_path,
502 ' Create_distributions --> l_misc_dist_rec.code_combination_id --> '
503 || l_misc_dist_rec.code_combination_id);
504 psa_utils.debug_other_string(g_state_level,l_full_path,
505 ' Create_distributions --> g_set_of_books_id --> ' || g_set_of_books_id);
506 psa_utils.debug_other_string(g_state_level,l_full_path,
507 ' Create_distributions --> l_mf_cash_ccid --> ' || l_mf_cash_ccid);
508 -- ========================= FND LOG ===========================
509
510 IF NOT (PSA_MFAR_UTILS.override_segments ( p_primary_ccid => l_primary_ccid,
511 p_override_ccid => misc_dist_new_rec.code_combination_id,
512 p_set_of_books_id => g_set_of_books_id,
513 p_trx_type => 'MISC',
514 p_ccid => l_mf_cash_ccid)) THEN
515
516 -- ========================= FND LOG ===========================
517 psa_utils.debug_other_string(g_state_level,l_full_path,
518 ' Create_distributions --> calling PSA_MFAR_UTILS.override_segments --> FALSE');
519 -- ========================= FND LOG ===========================
520 RAISE FLEX_BUILD_ERROR;
521 ELSE
522 -- ========================= FND LOG ===========================
523 psa_utils.debug_other_string(g_state_level,l_full_path,
524 ' Create_distributions --> calling PSA_MFAR_UTILS.override_segments --> TRUE');
525 -- ========================= FND LOG ===========================
526
527 END IF;
528
529 -- ========================= FND LOG ===========================
530 psa_utils.debug_other_string(g_state_level,l_full_path,
531 ' Create_distributions --> calling psa_mf_misc_dist_all_pkg.insert_row ');
532 -- ========================= FND LOG ===========================
533
534 psa_mf_misc_dist_all_pkg.insert_row
535 (
536 X_ROWID => x_dummy,
537 X_MISC_MF_CASH_DIST_ID => 1001,
538 X_MISC_CASH_DISTRIBUTION_ID => misc_dist_new_rec.misc_cash_distribution_id,
539 X_DISTRIBUTION_CCID => misc_dist_new_rec.code_combination_id,
540 X_CASH_CCID => l_mf_cash_ccid,
541 X_COMMENTS => NULL, --'Insert',
542 X_POSTING_CONTROL_ID => -3,
543 X_GL_DATE => misc_dist_new_rec.gl_date,
544 X_ATTRIBUTE_CATEGORY => NULL,
545 X_ATTRIBUTE1 => NULL,
546 x_attribute2 => NULL,
547 X_ATTRIBUTE3 => NULL,
548 X_ATTRIBUTE4 => NULL,
549 X_ATTRIBUTE5 => NULL,
550 X_ATTRIBUTE6 => NULL,
551 X_ATTRIBUTE7 => NULL,
552 X_ATTRIBUTE8 => NULL,
553 X_ATTRIBUTE9 => NULL,
554 X_ATTRIBUTE10 => NULL,
555 X_ATTRIBUTE11 => NULL,
556 X_ATTRIBUTE12 => NULL ,
557 X_ATTRIBUTE13 => NULL,
558 X_ATTRIBUTE14 => NULL,
559 X_ATTRIBUTE15 => NULL,
560 X_REFERENCE1 => x_status,
561 X_REFERENCE2 => NULL,
562 X_REFERENCE3 => misc_dist_new_rec.reversal_date,
563 X_REFERENCE4 => misc_dist_new_rec.status,
564 X_REFERENCE5 => g_cash_receipt_id,
565 x_reversal_ccid => null
566 );
567
568 END IF;
569
570 END LOOP;
571 CLOSE c_misc_dist_new;
572
573 ELSE
574 -- cr_status NOT IN ('NSF','STOP','REV')
575 -- No reversal scenario
576 -- count mismatch
577
578 -- ========================= FND LOG ===========================
579 psa_utils.debug_other_string(g_state_level,l_full_path,
580 ' Create_distributions --> cr_status NOT IN (NSF,STOP,REV)');
581 psa_utils.debug_other_string(g_state_level,l_full_path,
582 ' Create_distributions --> delete from psa_mf_misc_dist_all ');
583 -- ========================= FND LOG ===========================
584
585 DELETE FROM psa_mf_misc_dist_all
586 WHERE misc_cash_distribution_id IN
587 (SELECT misc_cash_distribution_id
588 FROM ar_misc_cash_distributions
589 WHERE reference5 = g_cash_receipt_id);
590
591 -- ========================= FND LOG ===========================
592 psa_utils.debug_other_string(g_state_level,l_full_path,
593 ' Create_distributions --> create_dist_flag - C');
594 -- ========================= FND LOG ===========================
595
596 create_dist_flag := 'C';
597
598 END IF;
599 END IF;
600
601
602 IF create_dist_flag = 'C' THEN
603
604 -- ========================= FND LOG ===========================
605 psa_utils.debug_other_string(g_state_level,l_full_path,
606 ' Create_distributions --> create_dist_flag is C then');
607 -- ========================= FND LOG ===========================
608
609 IF c_misc_dist%ISOPEN THEN
610 CLOSE c_misc_dist;
611 END IF;
612
613 -- ========================= FND LOG ===========================
614 psa_utils.debug_other_string(g_state_level,l_full_path,
615 ' Create_distributions --> Opening c_misc_dist');
616 -- ========================= FND LOG ===========================
617
618 OPEN c_misc_dist(g_cash_receipt_id);
619 LOOP
620
621 FETCH c_misc_dist INTO l_misc_dist_rec;
622 EXIT WHEN c_misc_dist%NOTFOUND;
623
624 -- ========================= FND LOG ===========================
625 psa_utils.debug_other_string(g_state_level,l_full_path,
626 ' Create_distributions --> Calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS');
627 -- ========================= FND LOG ===========================
628
629 OPEN c_mfar_dist_rec;
630 FETCH c_mfar_dist_rec INTO l_mfar_dist_rec;
631
632 IF l_mfar_dist_rec.curstatus = 'REMITTED' THEN
633 l_primary_ccid := ccid_rec.remittance_ccid;
634 ELSIF l_mfar_dist_rec.curstatus = 'CLEARED' THEN
635 l_primary_ccid := ccid_rec.cash_ccid;
636 ELSIF l_mfar_dist_rec.curstatus = 'REVERSED' AND l_mfar_dist_rec.prevstatus = 'CLEARED' THEN
637 l_primary_ccid := ccid_rec.cash_ccid;
638 ELSIF l_mfar_dist_rec.curstatus = 'REVERSED' AND l_mfar_dist_rec.prevstatus = 'REMITTED' THEN
639 l_primary_ccid := ccid_rec.remittance_ccid;
640 END IF;
641
642 CLOSE c_mfar_dist_rec;
643
644 -- ========================= FND LOG ===========================
645 psa_utils.debug_other_string(g_state_level,l_full_path,
646 ' Create_distributions --> l_primary_ccid --> ' || l_primary_ccid);
647 psa_utils.debug_other_string(g_state_level,l_full_path,
648 ' Create_distributions --> l_misc_dist_rec.code_combination_id --> '
649 || l_misc_dist_rec.code_combination_id);
650 psa_utils.debug_other_string(g_state_level,l_full_path,
651 ' Create_distributions --> g_set_of_books_id --> ' || g_set_of_books_id);
652 psa_utils.debug_other_string(g_state_level,l_full_path,
653 ' Create_distributions --> l_mf_cash_ccid --> ' || l_mf_cash_ccid);
654 -- ========================= FND LOG ===========================
655
656 IF NOT (PSA_MFAR_UTILS.OVERRIDE_SEGMENTS(
657 p_primary_ccid => l_primary_ccid,
658 p_override_ccid => l_misc_dist_rec.code_combination_id,
659 p_set_of_books_id => g_set_of_books_id,
660 p_trx_type => 'MISC',
661 p_ccid => l_mf_cash_ccid)) THEN
662
663 -- ========================= FND LOG ===========================
664 psa_utils.debug_other_string(g_state_level,l_full_path,
665 ' Create_distributions --> Calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -- FALSE');
666 -- ========================= FND LOG ===========================
667 RAISE FLEX_BUILD_ERROR;
668 ELSE
669 -- ========================= FND LOG ===========================
670 psa_utils.debug_other_string(g_state_level,l_full_path,
671 ' Create_distributions --> Calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -- TRUE');
672 -- ========================= FND LOG ===========================
673 END IF;
674
675 -- ========================= FND LOG ===========================
676 psa_utils.debug_other_string(g_state_level,l_full_path,
677 ' Create_distributions --> Calling psa_mf_misc_dist_all_pkg.insert_row ');
678 -- ========================= FND LOG ===========================
679
680 SELECT first_posted_record_flag INTO first_rec_flag
681 FROM ar_cash_receipt_history
682 WHERE cash_receipt_history_id = x_cash_receipt_hist_id;
683
684 IF first_rec_flag = 'N' AND x_status = 'CLEARED' THEN
685 OPEN c_reversal_ccid(l_misc_dist_rec.misc_cash_distribution_id);
686 FETCH c_reversal_ccid INTO l_reversal_ccid;
687 EXIT WHEN c_reversal_ccid%NOTFOUND;
688 CLOSE c_reversal_ccid;
689 END IF;
690
691 psa_mf_misc_dist_all_pkg.insert_row
692 (
693 X_ROWID => x_dummy,
694 X_MISC_MF_CASH_DIST_ID => 1001,
695 X_MISC_CASH_DISTRIBUTION_ID => l_misc_dist_rec.misc_cash_distribution_id,
696 X_DISTRIBUTION_CCID => l_misc_dist_rec.code_combination_id,
697 X_CASH_CCID => l_mf_cash_ccid,
698 X_COMMENTS => NULL, --'Insert',
699 X_POSTING_CONTROL_ID => -3,
700 X_GL_DATE => l_misc_dist_rec.gl_date,
701 X_ATTRIBUTE_CATEGORY => NULL,
702 X_ATTRIBUTE1 => NULL,
703 X_ATTRIBUTE2 => NULL,
704 X_ATTRIBUTE3 => NULL,
705 X_ATTRIBUTE4 => NULL,
706 X_ATTRIBUTE5 => NULL,
707 X_ATTRIBUTE6 => NULL,
708 X_ATTRIBUTE7 => NULL,
709 X_ATTRIBUTE8 => NULL,
710 X_ATTRIBUTE9 => NULL,
711 X_ATTRIBUTE10 => NULL,
712 X_ATTRIBUTE11 => NULL,
713 X_ATTRIBUTE12 => NULL ,
714 X_ATTRIBUTE13 => NULL,
715 X_ATTRIBUTE14 => NULL,
716 X_ATTRIBUTE15 => NULL,
717 X_REFERENCE1 => x_status,
718 X_REFERENCE2 => NULL,
719 X_REFERENCE3 => l_misc_dist_rec.reversal_date,
720 X_REFERENCE4 => l_misc_dist_rec.status,
721 X_REFERENCE5 => g_cash_receipt_id,
722 x_reversal_ccid => l_reversal_ccid);
723
724 END LOOP;
725 CLOSE c_misc_dist;
726 END IF;
727 END LOOP;
728 -- ========================= FND LOG ===========================
729 psa_utils.debug_other_string(g_state_level,l_full_path,' Create_distributions --> End ');
730 -- ========================= FND LOG ===========================
731
732 retcode := 'S';
733 RETURN TRUE;
734
735 EXCEPTION
736 WHEN FLEX_BUILD_ERROR THEN
737 p_error_message:= 'EXCEPTION - FLEX_BUILD_ERROR PACKAGE - PSA_MF_MISC_PKG.CREATE_DISTRIBUTIONS - '||FND_MESSAGE.GET;
738 -- ========================= FND LOG ===========================
739 psa_utils.debug_other_string(g_excep_level,l_full_path,p_error_message);
740 -- ========================= FND LOG ===========================
741 retcode := 'F';
742 RETURN FALSE;
743
744 WHEN OTHERS THEN
745 p_error_message:= 'EXCEPTION - OTHERS PACKAGE - PSA_MF_MISC_PKG.CREATE_DISTRIBUTIONS - '||sqlerrm;
746 -- ========================= FND LOG ===========================
747 psa_utils.debug_other_string(g_excep_level,l_full_path,p_error_message);
748 psa_utils.debug_unexpected_msg(l_full_path);
749 -- ========================= FND LOG ===========================
750 retcode := 'F';
751 RETURN FALSE;
752
753 END create_distributions;
754
755 /********************************* MISC_RCT_CHANGED ********************************/
756
757 FUNCTION misc_rct_changed(p_status IN VARCHAR2) RETURN BOOLEAN IS
758 ar_dist_count NUMBER := 0;
759 psa_dist_count NUMBER := 0;
760 -- ========================= FND LOG ===========================
761 l_full_path VARCHAR2(100) := g_path || 'misc_rct_changed';
762 -- ========================= FND LOG ===========================
763
764 BEGIN
765 -- ========================= FND LOG ===========================
766 psa_utils.debug_other_string(g_state_level,l_full_path,' Misc_rct_changed --> start ');
767 -- ========================= FND LOG ===========================
768
769 IF p_status IN ('CLEARED','REMITTED') then
770 SELECT count(misc_cash_distribution_id) INTO ar_dist_count
771 FROM ar_misc_cash_distributions
772 WHERE cash_receipt_id = g_cash_receipt_id AND amount>0;
773 ELSE
774 SELECT
775 COUNT(misc_cash_distribution_id) INTO ar_dist_count
776 FROM ar_misc_cash_distributions
777 WHERE cash_receipt_id = g_cash_receipt_id
778 AND amount < 0;
779 END IF;
780
781 -- ========================= FND LOG ===========================
782 psa_utils.debug_other_string(g_state_level,l_full_path,
783 ' Misc_rct_changed --> ar_dist_count -- ' || ar_dist_count);
784 -- ========================= FND LOG ===========================
785
786
787 SELECT COUNT(MISC_MF_CASH_DIST_ID) INTO psa_dist_count
788 FROM psa_mf_misc_dist_all psa,
789 ar_misc_cash_distributions ar
790 WHERE psa.reference1 = p_status
791 AND psa.misc_cash_distribution_id = ar.misc_cash_distribution_id
792 AND ar.cash_receipt_id = g_cash_receipt_id;
793
794 -- ========================= FND LOG ===========================
795 psa_utils.debug_other_string(g_state_level,l_full_path,
796 ' Misc_rct_changed --> psa_dist_count ' || psa_dist_count);
797 -- ========================= FND LOG ===========================
798
799
800
801 -- ========================= FND LOG ===========================
802 psa_utils.debug_other_string(g_state_level,l_full_path,
803 ' Misc_rct_changed --> delete psa_mf_misc_dist_all ' || SQL%ROWCOUNT);
804 -- ========================= FND LOG ===========================
805
806 IF ar_dist_count = psa_dist_count THEN
807 -- ========================= FND LOG ===========================
808 psa_utils.debug_other_string(g_state_level,l_full_path,
809 ' Misc_rct_changed --> ar_dist_count = psa_dist_count RETURN FALSE');
810 -- ========================= FND LOG ===========================
811 RETURN FALSE;
812
813 ELSE
814 -- ========================= FND LOG ===========================
815 psa_utils.debug_other_string(g_state_level,l_full_path,
816 ' Misc_rct_changed -->ar_dist_count != psa_dist_count RETURN TRUE');
817 -- ========================= FND LOG ===========================
818 RETURN TRUE;
819 END IF;
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 -- ========================= FND LOG ===========================
824 psa_utils.debug_other_string(g_excep_level,l_full_path,
825 'EXCEPTION - OTHERS PACKAGE - PSA_MF_MISC_PKG.MISC_RCT_CHANGED - '||sqlerrm);
826 psa_utils.debug_unexpected_msg(l_full_path);
827 -- ========================= FND LOG ===========================
828 RETURN FALSE;
829
830 END misc_rct_changed;
831
832
833 END psa_mf_misc_pkg;