[Home] [Help]
PACKAGE BODY: APPS.PSA_MF_CREATE_DISTRIBUTIONS
Source
1 PACKAGE BODY PSA_MF_CREATE_DISTRIBUTIONS AS
2 /* $Header: PSAMFCRB.pls 120.15 2006/09/13 12:32:39 agovil ship $ */
3
4 /*
5 ## A new parameter p_mode is being added to branch the processing
6 ## based on Activity - Transaction / Miscellaneous Receipt.
7 ## If Activity is (T)ransaction, Multifund Transaction, Cash Receipts and Adjustments
8 ## will be processed
9 ## If Activity is (M)iscellanous Receipt, Misc. receipts will be processed
10 ## If Activity is (A)ll, all the Multifund distributions will be processed.
11 */
12
13 --===========================FND_LOG.START=====================================
14 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
15 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
16 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
17 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
18 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
19 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
20 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAMFCRB.PSA_MF_CREATE_DISTRIBUTIONS.';
21 --===========================FND_LOG.END=======================================
22
23 FUNCTION create_distributions (
24 errbuf OUT NOCOPY VARCHAR2,
25 retcode OUT NOCOPY VARCHAR2,
26 p_mode IN VARCHAR2,
27 p_document_id IN NUMBER,
28 p_set_of_books_id IN NUMBER,
29 run_num OUT NOCOPY NUMBER,
30 p_error_message OUT NOCOPY VARCHAR2,
31 p_report_only IN VARCHAR2 DEFAULT 'N')
32 RETURN BOOLEAN
33 IS
34
35 CURSOR a (cust_trx_id in number)
36 IS
37 SELECT trx.customer_trx_id
38 FROM ra_customer_trx trx, psa_trx_types_all psa
39 WHERE trx.customer_trx_id = nvl(cust_trx_id,trx.customer_trx_id)
40 AND trx.cust_trx_type_id = psa.psa_trx_type_id
41 AND set_of_books_id = p_set_of_books_id;
42
43
44 CURSOR b (cust_trx_id in number)
45 IS
46 SELECT b.receivable_application_id
47 FROM ar_receivable_applications b
48 WHERE b.applied_customer_trx_id = cust_trx_id ;
49
50 CURSOR c (cust_trx_id in number)
51 IS
52 SELECT c.adjustment_id
53 FROM ar_Adjustments c
54 WHERE c.customer_trx_id = cust_trx_id;
55
56 CURSOR d (c_cash_receipt_id IN NUMBER)
57 IS
58 SELECT cash_receipt_id,receipt_method_id,receivables_trx_id
59 FROM ar_cash_receipts arc, psa_receivables_trx_all psa
60 WHERE arc.cash_receipt_id = NVL(c_cash_receipt_id, arc.cash_receipt_id)
61 AND arc.receivables_trx_id = psa.psa_receivables_trx_id
62 AND arc.set_of_books_id = p_set_of_books_id;
63
64 l_mfar_implemented VARCHAR2(1);
65 l_org_id NUMBER;
66 cash_receipt_rec d%ROWTYPE;
67 l_set_of_books_id NUMBER;
68
69 MFAR_DIST_EXCEP EXCEPTION;
70 MFAR_NOT_IMPLEMENTED EXCEPTION;
71
72 -- ========================= FND LOG ===========================
73 l_full_path VARCHAR2(100) := g_path || 'create_distributions';
74 -- ========================= FND LOG ===========================
75
76 BEGIN
77
78 SAVEPOINT PSA_PSAMFCRB;
79
80 l_set_of_books_id := p_set_of_books_id;
81 FND_PROFILE.GET ('ORG_ID', l_org_id);
82
83 -- ========================= FND LOG ===========================
84 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside Create_distribution ');
85 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS ');
86 psa_utils.debug_other_string(g_state_level,l_full_path,' ========== ');
87 psa_utils.debug_other_string(g_state_level,l_full_path,' p_mode --> ' || p_mode);
88 psa_utils.debug_other_string(g_state_level,l_full_path,' p_document_id --> ' || p_document_id);
89 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id --> ' || p_set_of_books_id);
90 psa_utils.debug_other_string(g_state_level,l_full_path,' l_org_id --> ' || l_org_id);
91 psa_utils.debug_other_string(g_state_level,l_full_path,' p_report_only --> ' || p_report_only );
92 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
93 -- ========================= FND LOG ===========================
94
95 /* checking whether MFAR is available */
96 IF (NOT (PSA_IMPLEMENTATION.GET (l_org_id, 'MFAR', l_mfar_implemented)) OR
97 (l_mfar_implemented <> 'Y')) THEN
98 -- ========================= FND LOG ===========================
99 psa_utils.debug_other_string(g_error_level,l_full_path,
100 ' MFAR not implemented !');
101 -- ========================= FND LOG ===========================
102 RAISE MFAR_NOT_IMPLEMENTED ;
103 ELSE
104 -- ========================= FND LOG ===========================
105 psa_utils.debug_other_string(g_state_level,l_full_path,' MFAR is implemented ');
106 -- ========================= FND LOG ===========================
107 END IF;
108
109
110 /* getting the sequence number */
111 BEGIN
112 SELECT psa_mf_error_log_s.NEXTVAL INTO run_num
113 FROM sys.dual;
114
115 -- ========================= FND LOG ===========================
116 psa_utils.debug_other_string(g_state_level,l_full_path,
117 ' Sequence number -> ' || run_num);
118 -- ========================= FND LOG ===========================
119
120 EXCEPTION
121 WHEN OTHERS THEN
122 FND_MESSAGE.SET_NAME ('AR', 'GENERIC_MESSAGE');
123 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',
124 'PSA_MF_CREATE_DISTRIBUTIONS - '
125 || 'Cannot create run number');
126 -- ========================= FND LOG ===========================
127 psa_utils.debug_other_msg(g_excep_level,l_full_path,FALSE);
128 -- ========================= FND LOG ===========================
129 p_error_message := FND_MESSAGE.GET;
130 -- ========================= FND LOG ===========================
131 psa_utils.debug_other_string(g_excep_level,l_full_path,
132 ' PSAMFCRB: ' || p_error_message);
133 psa_utils.debug_other_string(g_excep_level,l_full_path,
134 ' PSAMFCRB: ' || sqlcode || sqlerrm);
135 -- ========================= FND LOG ===========================
136 RETURN FALSE;
137 END;
138
139 IF p_mode IN ('A','T') THEN
140 -- ========================= FND LOG ===========================
141 psa_utils.debug_other_string(g_state_level,l_full_path,
142 ' p_mode in A or T');
143 -- ========================= FND LOG ===========================
144
145 FOR a_row IN a(p_document_id)
146 LOOP
147 -- ========================= FND LOG ===========================
148 psa_utils.debug_other_string(g_state_level,l_full_path,
149 ' Customer trx id --> ' || a_row.customer_trx_id);
150 psa_utils.debug_other_string(g_state_level,l_full_path,
151 ' CAlling PSA_MFAR_VAL_PKG.ar_mfar_validate_check');
152 -- ========================= FND LOG ===========================
153
154 IF PSA_MFAR_VAL_PKG.ar_mfar_validate_check
155 (a_row.customer_trx_id,'TRX', p_set_of_books_id) = 'Y' THEN
156
157 -- ========================= FND LOG ===========================
158 psa_utils.debug_other_string(g_state_level,l_full_path,
159 ' calling PSA_MFAR_TRANSACTIONS.create_distributions ');
160 -- ========================= FND LOG ===========================
161
162 IF NOT (PSA_MFAR_TRANSACTIONS.create_distributions (errbuf => errbuf,
163 retcode => retcode,
164 p_cust_trx_id => a_row.customer_trx_id,
165 p_set_of_books_id => p_set_of_books_id,
166 p_run_id => run_num,
167 p_error_message => p_error_message)) THEN
168
169 -- ========================= FND LOG ===========================
170 psa_utils.debug_other_string(g_error_level,l_full_path,
171 ' PSA_MFAR_TRANSACTIONS.create_distributions --> FALSE ');
172 -- ========================= FND LOG ===========================
173
174 IF p_error_message IS NOT NULL OR retcode = 'F' THEN
175 -- ========================= FND LOG ===========================
176 psa_utils.debug_other_string(g_error_level,l_full_path,
177 ' Error Message --> '
178 || p_error_message);
179 -- ========================= FND LOG ===========================
180
181 IF NVL(p_report_only,'N') = 'N' THEN
182 -- ========================= FND LOG ===========================
183 psa_utils.debug_other_string(g_error_level,l_full_path,
184 ' p_report_only --> N : ' ||
185 ' This is not for reporting purpose so end processing. ');
186 -- ========================= FND LOG ===========================
187 Raise MFAR_DIST_EXCEP ;
188 END IF;
189 END IF;
190
191 ELSE
192 -- ========================= FND LOG ===========================
193 psa_utils.debug_other_string(g_state_level,l_full_path,
194 ' PSA_MFAR_TRANSACTIONS.create_distributions --> ' || a_row.customer_trx_id);
195 -- ========================= FND LOG ===========================
196 END IF;
197
198 /* RECEIPTS */
199 FOR b_row IN b(a_row.customer_trx_id)
200 LOOP
201 -- ========================= FND LOG ===========================
202 psa_utils.debug_other_string(g_state_level,l_full_path,
203 ' calling PSA_MFAR_RECEIPTS.create_distributions '
204 || ' -- ' || b_row.receivable_application_id);
205 -- ========================= FND LOG ===========================
206
207 IF NOT (PSA_MFAR_RECEIPTS.create_distributions (errbuf => errbuf,
208 retcode => retcode,
209 p_receivable_app_id => b_row.receivable_application_id,
210 p_set_of_books_id => p_set_of_books_id,
211 p_run_id => run_num,
212 p_error_message => p_error_message)) THEN
213
214 -- ========================= FND LOG ===========================
215 psa_utils.debug_other_string(g_state_level,l_full_path,
216 ' PSA_MFAR_RECEIPTS.create_distributions --> FALSE ');
217 -- ========================= FND LOG ===========================
218
219 IF p_error_message IS NOT NULL OR retcode = 'F' THEN
220 -- ========================= FND LOG ===========================
221 psa_utils.debug_other_string(g_excep_level,l_full_path,
222 ' Error Message --> '|| p_error_message);
223 -- ========================= FND LOG ===========================
224
225 IF NVL(p_report_only,'N') = 'N' THEN
226 -- ========================= FND LOG ===========================
227 psa_utils.debug_other_string(g_excep_level,l_full_path,
228 ' p_report_only --> : This is not for reporting purpose so end processing. ');
229 -- ========================= FND LOG ===========================
230 Raise MFAR_DIST_EXCEP ;
231 END IF;
232 END IF;
233
234 ELSE
235 -- ========================= FND LOG ===========================
236 psa_utils.debug_other_string(g_state_level,l_full_path,
237 ' PSA_MFAR_RECEIPTS.create_distributions TRUE --> ' || b_row.receivable_application_id);
238 -- ========================= FND LOG ===========================
239
240 END IF;
241
242 END LOOP;
243
244 /* ADJUSTMENTS */
245 FOR c_row IN c(a_row.customer_trx_id)
246 LOOP
247
248 -- ========================= FND LOG ===========================
249 psa_utils.debug_other_string(g_state_level,l_full_path,
250 ' calling PSA_MFAR_ADJUSTMENTS.create_distributions '
251 || ' -- ' || c_row.adjustment_id );
252 -- ========================= FND LOG ===========================
253
254 IF NOT (PSA_MFAR_ADJUSTMENTS.create_distributions (
255 errbuf => errbuf,
256 retcode => retcode,
257 p_adjustment_id => c_row.adjustment_id,
258 p_set_of_books_id => p_set_of_books_id,
259 p_run_id => run_num,
260 p_error_message => p_error_message)) THEN
261
262 -- ========================= FND LOG ===========================
263 psa_utils.debug_other_string(g_error_level,l_full_path,
264 ' PSA_MFAR_TRANSACTIONS.create_distributions --> FALSE ');
265 -- ========================= FND LOG ===========================
266
267 IF p_error_message IS NOT NULL OR retcode = 'F' THEN
268 -- ========================= FND LOG ===========================
269 psa_utils.debug_other_string(g_excep_level,l_full_path,
270 ' Error Message --> '
271 || p_error_message);
272 -- ========================= FND LOG ===========================
273
274 IF NVL(p_report_only,'N') = 'N' THEN
275 -- ========================= FND LOG ===========================
276 psa_utils.debug_other_string(g_excep_level,l_full_path,
277 ' p_report_only --> N : This is not for reporting purpose so end processing. ');
278 -- ========================= FND LOG ===========================
279 Raise MFAR_DIST_EXCEP ;
280 END IF;
281
282 END IF;
283
284 ELSE
285 -- ========================= FND LOG ===========================
286 psa_utils.debug_other_string(g_state_level,l_full_path,
287 ' PSA_MFAR_ADJUSTMENTS.create_distributions TRUE --> ' || c_row.adjustment_id);
288 -- ========================= FND LOG ===========================
289 END IF;
290
291 END LOOP;
292 END IF; -- AR_MFAR_VALIDATE_CHECK
293 END LOOP;
294
295 END IF;
296
297
298 IF p_mode IN ('A','R') THEN
299 -- ========================= FND LOG ===========================
300 psa_utils.debug_other_string(g_state_level,l_full_path,' p_mode in A or R');
301 -- ========================= FND LOG ===========================
302
303 OPEN d(p_document_id);
304 LOOP
305
306 FETCH d INTO cash_receipt_rec;
307 EXIT WHEN d%notfound;
308
309 -- ========================= FND LOG ===========================
310 psa_utils.debug_other_string(g_state_level,l_full_path,
311 ' calling PSA_MF_MISC_PKG.generate_distributions '
312 || ' -- ' || cash_receipt_rec.cash_receipt_id);
313 -- ========================= FND LOG ===========================
314
315 IF NOT (PSA_MF_MISC_PKG.generate_distributions (
316 errbuf => errbuf,
317 retcode => retcode,
318 p_cash_receipt_id => cash_receipt_rec.cash_receipt_id,
319 p_set_of_books_id => l_set_of_books_id,
320 p_run_id => run_num,
321 p_error_message => p_error_message,
322 p_report_only => p_report_only)) THEN
323
324 -- ========================= FND LOG ===========================
325 psa_utils.debug_other_string(g_error_level,l_full_path,
326 ' PSA_MF_MISC_PKG.generate_distributions --> FALSE ');
327 -- ========================= FND LOG ===========================
328
329 IF p_error_message IS NOT NULL OR retcode = 'F' THEN
330 -- ========================= FND LOG ===========================
331 psa_utils.debug_other_string(g_excep_level,l_full_path,
332 ' Error Message --> '|| p_error_message);
333 -- ========================= FND LOG ===========================
334
335 IF NVL(p_report_only,'N') = 'N' THEN
336 -- ========================= FND LOG ===========================
337 psa_utils.debug_other_string(g_excep_level,l_full_path,
338 ' p_report_only --> N : This is not for reporting purpose so end processing. ');
339 -- ========================= FND LOG ===========================
340 Raise MFAR_DIST_EXCEP ;
341 END IF;
342 END IF;
343 ELSE
344 -- ========================= FND LOG ===========================
345 psa_utils.debug_other_string(g_state_level,l_full_path,
346 ' PSA_MF_MISC_PKG.generate_distributions TRUE --> '
347 || cash_receipt_rec.cash_receipt_id);
348 -- ========================= FND LOG ===========================
349 END IF;
350
351 END LOOP;
352 CLOSE d;
353 END IF;
354
355 IF NVL(p_report_only,'N') = 'Y' THEN
356 retcode := 'F';
357 -- have to check with sanjay if report_only is YES then should we have to pass
358 -- as F so the records will be removed from gl_interface
359 END IF;
360
361 -- ========================= FND LOG ===========================
362 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURNING TRUE ');
363 -- ========================= FND LOG ===========================
364
365 RETURN TRUE;
366
367 EXCEPTION
368 WHEN MFAR_DIST_EXCEP THEN
369 -- ========================= FND LOG ===========================
370 psa_utils.debug_other_string(g_excep_level,l_full_path,
371 'EXCEPTION - MFAR_NOT_IMPLEMENTED PACKAGE - PSA_MF_CREATE_DISTRIBUTIONS.CREATE_DISTRIBUTIONS');
372 psa_utils.debug_other_string(g_excep_level,l_full_path, p_error_message);
373 -- ========================= FND LOG ===========================
374
375 BEGIN
376 -- ========================= FND LOG ===========================
377 psa_utils.debug_other_string(g_excep_level,l_full_path, 'Rolling back to PSA_PSAMFCRB');
378 -- ========================= FND LOG ===========================
379 ROLLBACK TO PSA_PSAMFCRB;
380 EXCEPTION
381 WHEN OTHERS THEN
382 -- ========================= FND LOG ===========================
383 psa_utils.debug_other_string(g_excep_level,l_full_path,
384 'EXCEPTION - OTHERS : SAVEPOINT PSA_PSAMFCRB ERASED.');
385 -- ========================= FND LOG ===========================
386 END;
387
388 retcode := 'F';
389 RETURN FALSE;
390
391 WHEN MFAR_NOT_IMPLEMENTED THEN
392 fnd_message.set_name ('PSA','PSA_MF_NOT_IMPLEMENTED');
393 p_error_message := fnd_message.get;
394 -- ========================= FND LOG ===========================
395 psa_utils.debug_other_string(g_excep_level,l_full_path,
396 'EXCEPTION - MFAR_NOT_IMPLEMENTED PACKAGE - PSA_MF_CREATE_DISTRIBUTIONS.CREATE_DISTRIBUTIONS');
397 psa_utils.debug_other_string(g_excep_level,l_full_path, p_error_message);
398 -- ========================= FND LOG ===========================
399 retcode := 'S';
400 RETURN FALSE;
401
402 WHEN OTHERS THEN
403 p_error_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MF_CREATE_DISTRIBUTIONS.CREATE_DISTRIBUTIONS - '||sqlerrm;
404 -- ========================= FND LOG ===========================
405 psa_utils.debug_other_string(g_excep_level,l_full_path,p_error_message);
406 psa_utils.debug_unexpected_msg(l_full_path);
407 -- ========================= FND LOG ===========================
408 retcode := 'F';
409 RETURN FALSE;
410
411 END create_distributions;
412
413
414 /****************************** SUBMIT_CREATE_DISTRIBUTIONS *****************************/
415
416 /*
417 ## This procedure is a wrapper around create_distributions,
418 ## because procedures with out parameters cannot be used in SRS
419 */
420
421 PROCEDURE submit_create_distributions (
422 errbuf OUT NOCOPY VARCHAR2,
423 retcode OUT NOCOPY VARCHAR2,
424 p_mode IN VARCHAR2,
425 p_document_id IN NUMBER,
426 p_set_of_books_id IN NUMBER,
427 p_report_only IN VARCHAR2 DEFAULT 'N')
428 IS
429 run_num NUMBER;
430 p_error_message VARCHAR2(3000);
431 -- ========================= FND LOG ===========================
432 l_full_path VARCHAR2(100) := g_path || 'submit_create_distributions';
433 -- ========================= FND LOG ===========================
434
435 BEGIN
436
437 -- ========================= FND LOG ===========================
438 psa_utils.debug_other_string(g_state_level,l_full_path,
439 ' submit_create_distribution --> START ');
440 -- ========================= FND LOG ===========================
441
442 IF NOT (create_distributions (
443 errbuf,
444 retcode,
445 p_mode,
446 p_document_id ,
447 p_set_of_books_id ,
448 run_num,
449 p_error_message,
450 p_report_only)) THEN
451
452 -- ========================= FND LOG ===========================
453 psa_utils.debug_other_string(g_state_level,l_full_path,
454 ' submit_create_distribution --> Document id --> ' || p_document_id
455 || ' Number --> ' || run_num);
456 psa_utils.debug_other_string(g_state_level,l_full_path,
457 ' submit_create_distribution --> Error Message : ' || p_error_message);
458 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
459 -- ========================= FND LOG ===========================
460
461 END IF;
462
463 -- ========================= FND LOG ===========================
464 psa_utils.debug_other_string(g_state_level,l_full_path,
465 ' submit_create_distribution --> END ');
466 -- ========================= FND LOG ===========================
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 -- ========================= FND LOG ===========================
471 psa_utils.debug_other_string(g_excep_level,l_full_path,'submit_create_distributions: '
472 || 'PSAMFCRB: Exception Main in submit_create..');
473 psa_utils.debug_other_string(g_excep_level,l_full_path,'submit_create_distributions: '
474 || 'PSAMFCRB ' || sqlcode || sqlerrm);
475 psa_utils.debug_unexpected_msg(l_full_path);
476 -- ========================= FND LOG ===========================
477 END submit_create_distributions;
478
479 FUNCTION create_distributions_rpt (
480 errbuf OUT NOCOPY VARCHAR2,
481 retcode OUT NOCOPY VARCHAR2,
482 p_mode IN VARCHAR2,
483 p_document_id IN NUMBER,
484 p_set_of_books_id IN NUMBER,
485 run_num OUT NOCOPY NUMBER,
486 p_error_message OUT NOCOPY VARCHAR2,
487 p_report_only IN VARCHAR2 DEFAULT 'N',
488 p_gl_date_from IN DATE,
489 p_gl_date_to IN DATE )
490 RETURN BOOLEAN
491 IS
492
493 CURSOR c_selected_trx IS
494 SELECT distinct trx.customer_trx_id document_id
495 FROM ra_customer_trx trx,
496 psa_trx_types_all psa,
497 ra_cust_trx_line_gl_dist gd
498 WHERE trx.cust_trx_type_id = psa.psa_trx_type_id
499 AND trx.set_of_books_id = p_set_of_books_id
500 AND trx.customer_trx_id = gd.customer_trx_id
501 AND gd.gl_date between nvl(p_gl_date_from, gd.gl_date) and nvl(p_gl_date_to, gd.gl_date);
502
503 TYPE trx_id_type IS TABLE OF ra_customer_trx.customer_trx_id%TYPE;
504 l_trx_id_tab trx_id_type;
505
506 BEGIN
507
508 OPEN c_selected_trx;
509
510 FETCH c_selected_trx BULK COLLECT INTO l_trx_id_tab;
511
512 CLOSE c_selected_trx;
513
514 FOR i IN 1..l_trx_id_tab.count
515 LOOP
516 IF NOT (PSA_MF_CREATE_DISTRIBUTIONS.CREATE_DISTRIBUTIONS
517 ( ERRBUF => errbuf,
518 RETCODE => retcode,
519 P_MODE => p_mode,
520 P_DOCUMENT_ID => l_trx_id_tab(i),
521 P_SET_OF_BOOKS_ID => p_set_of_books_id,
522 RUN_NUM => run_num,
523 P_ERROR_MESSAGE => p_error_message,
524 P_REPORT_ONLY => p_report_only)) THEN
525 return (FALSE);
526 END IF;
527
528 IF (MOD(i, 100) = 0) THEN
529 COMMIT;
530 END IF;
531 END LOOP;
532
533 return (TRUE);
534
535 END create_distributions_rpt;
536
537 END psa_mf_create_distributions;