[Home] [Help]
PACKAGE BODY: APPS.PSA_MFAR_RECEIPTS
Source
1 PACKAGE BODY PSA_MFAR_RECEIPTS AS
2 /* $Header: PSAMFRTB.pls 120.29 2006/09/13 13:45:49 agovil ship $ */
3
4 g_set_of_books_id gl_sets_of_books.set_of_books_id%type;
5 g_cust_trx_id ar_receivable_applications.applied_customer_trx_id%type;
6 g_receivable_application_id ar_receivable_applications.receivable_application_id%type;
7 g_inventory_item_profile NUMBER;
8 g_run_id NUMBER;
9 l_exception_message VARCHAR2(3000);
10
11 TYPE TrxLinesTyp IS TABLE OF ra_customer_trx_lines.customer_trx_line_id%TYPE
12 INDEX BY BINARY_INTEGER;
13
14 TrxLinesTab TrxLinesTyp;
15 --===========================FND_LOG.START=====================================
16 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
17 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
18 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
19 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
20 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
21 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
22 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAMFRTB.PSA_MFAR_RECEIPTS.';
23 --===========================FND_LOG.END=======================================
24
25
26 -- Variables for currency code
27
28 g_precision NUMBER;
29 g_ext_precision NUMBER;
30 g_min_acct_unit NUMBER;
31
32
33 /*
34 ##################################
35 ## PRIVATE PROCEDURES/FUNCTIONS ##
36 ##################################
37 */
38
39
40 FUNCTION distributions_exist_and_valid(p_receivable_app_id IN NUMBER) RETURN BOOLEAN;
41 FUNCTION CASH_CLR_DIST_EXIST_VALID(p_receivable_app_id IN NUMBER, p_amount in NUMBER, p_crh_status IN varchar2) RETURN BOOLEAN;
42
43 FUNCTION generate_rct_dist
44 (errbuf OUT NOCOPY VARCHAR2,
45 retcode OUT NOCOPY VARCHAR2,
46 p_rcv_app_id IN NUMBER,
47 p_cash_ccid IN NUMBER,
48 p_cust_trx_id IN NUMBER,
49 p_cust_trx_line_id IN NUMBER,
50 p_amount_applied IN NUMBER,
51 p_earned_discount IN NUMBER,
52 p_unearned_discount IN NUMBER,
53 p_earned_discount_ccid IN NUMBER,
54 p_unearned_discount_ccid IN NUMBER,
55 p_document_type IN VARCHAR2,
56 p_crh_status IN VARCHAR2 DEFAULT 'OTHER',
57 p_error_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
58
59 FUNCTION generate_rct_dist_cm
60 (errbuf OUT NOCOPY VARCHAR2,
61 retcode OUT NOCOPY VARCHAR2,
62 p_rcv_app_id IN NUMBER,
63 p_cash_ccid IN NUMBER,
64 p_cust_trx_id IN NUMBER,
65 p_cust_trx_line_id IN NUMBER,
66 p_amount_applied1 IN NUMBER,
67 p_earned_discount IN NUMBER,
68 p_unearned_discount IN NUMBER,
69 p_earned_discount_ccid IN NUMBER,
70 p_unearned_discount_ccid IN NUMBER,
71 p_document_type IN VARCHAR2,
72 p_crh_status IN VARCHAR2 DEFAULT 'OTHER',
73 p_error_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
74
75 PROCEDURE populate_discount_lines_cache (p_customer_trx_id IN NUMBER);
76
77 PROCEDURE manual_transaction (p_customer_trx_id IN NUMBER,
78 discount_basis IN VARCHAR2);
79
80 PROCEDURE imported_transaction (p_customer_trx_id IN NUMBER,
81 p_discount_basis IN VARCHAR2);
82
83 FUNCTION line_in_discount_cache (p_customer_trx_line_id IN NUMBER) RETURN BOOLEAN;
84
85 PROCEDURE reset_discount_cache;
86
87 /*
88 ##########################
89 ## END OF DECLARATION ##
90 ##########################
91 */
92
93 FUNCTION create_distributions
94 (errbuf OUT NOCOPY VARCHAR2,
95 retcode OUT NOCOPY VARCHAR2,
96 p_receivable_app_id IN NUMBER,
97 p_set_of_books_id IN NUMBER,
98 p_run_id IN NUMBER,
99 p_error_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN
100 IS
101
102 CURSOR c_crh
103 IS
104 SELECT distinct crh.status , crh.cash_receipt_id
105 FROM ar_cash_receipt_history_all crh,
106 ar_receivable_applications_all ra
107 WHERE crh.cash_receipt_id = ra.cash_receipt_id
108 AND ra.receivable_application_id = p_receivable_app_id
109 ORDER BY crh.status desc;
110
111 CURSOR c_rcpt_application
112 IS
113 SELECT app.applied_customer_trx_id cust_trx_id,
114 app.applied_customer_trx_line_id cust_trx_line_id,
115 app.code_combination_id rec_ccid,
116 app.cash_receipt_id cash_receipt_id,
117 app.amount_applied amount_applied,
118 app.earned_discount_taken earned_discount,
119 app.unearned_discount_taken unearned_discount,
120 app.earned_discount_ccid earned_discount_ccid,
121 app.unearned_discount_ccid unearned_discount_ccid,
122 app.customer_trx_id cm_trx_id
123 FROM ar_receivable_applications_all app
124 WHERE app.receivable_application_id = p_receivable_app_id
125 AND app.status = 'APP'
126 FOR UPDATE;
127
128 CURSOR c_cash_ccid (c_cash_receipt_id NUMBER, c_status IN varchar2)
129 IS
130 SELECT crh.account_code_combination_id cash_ccid, crh.status
131 FROM ar_cash_receipts_all cr,
132 ar_cash_receipt_history_all crh
133 WHERE cr.cash_receipt_id = c_cash_receipt_id
134 AND cr.cash_receipt_id = crh.cash_receipt_id
135 AND crh.status = c_status
136 AND NOT (cr.type = 'MISC');
137
138 CURSOR c_credit_memo_type (c_cust_trx_id NUMBER)
139 IS
140 SELECT previous_customer_trx_id
141 FROM ra_customer_trx_all
142 WHERE customer_trx_id = c_cust_trx_id;
143
144 --
145 -- Bug 2515944
146 -- Modified the select list in cursor c_direct_cm
147 -- From: lines.previous_customer_trx_line_id trx_line_id, dist.amount amount
148 -- To : distinct lines.previous_customer_trx_line_id trx_line_id
149 --
150
151 CURSOR c_direct_cm (c_cust_trx_id NUMBER)
152 IS
153 SELECT distinct lines.previous_customer_trx_line_id trx_line_id
154 FROM ra_customer_trx_lines lines, ra_cust_trx_line_gl_dist dist
155 WHERE lines.customer_trx_id = c_cust_trx_id
156 AND lines.customer_trx_line_id = dist.customer_trx_line_id
157 AND dist.account_class <> 'REC'
158 AND lines.extended_amount <> 0;
159
160 CURSOR c_direct_cm_line_amount (c_cust_trx_id NUMBER, c_trx_line_id NUMBER)
161 IS
162 Select sum(dist.amount) line_amount
163 From ra_customer_trx_lines lines,
164 ra_cust_trx_line_gl_dist dist
165 Where lines.customer_trx_id = c_cust_trx_id
166 And lines.previous_customer_trx_line_id = c_trx_line_id
167 And lines.customer_trx_line_id = dist.customer_trx_line_id
168 And dist.account_class <> 'REC'
169 And lines.extended_amount <> 0;
170
171 l_rcpt_application_rec c_rcpt_application%rowtype;
172 c_crh_rec c_crh%ROWTYPE;
173 l_credit_memo_type c_credit_memo_type%rowtype;
174 l_direct_cm c_direct_cm%rowtype;
175 l_cash_ccid c_cash_ccid%rowtype;
176 l_ccid ar_receivable_applications.code_combination_id%type;
177
178 l_direct_cm_line_amount c_direct_cm_line_amount%rowtype;
179 l_currency_code VARCHAR2(15);
180
181 generate_rct_dist_excep EXCEPTION;
182
183 -- ========================= FND LOG ===========================
184 l_full_path VARCHAR2(100) := g_path || 'create_distributions';
185 -- ========================= FND LOG ===========================
186
187 BEGIN
188
189 -- ========================= FND LOG ===========================
190 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
191 psa_utils.debug_other_string(g_state_level,l_full_path,' Starting create_distributions');
192 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
193 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
194 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
195 psa_utils.debug_other_string(g_state_level,l_full_path,' p_receivable_app_id -->' || p_receivable_app_id);
196 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id -->' || p_set_of_books_id);
197 psa_utils.debug_other_string(g_state_level,l_full_path,' p_run_id -->' || p_run_id);
198 psa_utils.debug_other_string(g_state_level,l_full_path,' Starting the process ');
199 -- ========================= FND LOG ===========================
200
201 --
202 -- Initialize Global Variables
203 --
204
205 retcode := 'F';
206
207
208
209 -- get the precisison for the currency code
210
211 SELECT currency_code
212 INTO l_currency_code
213 FROM gl_sets_of_books
214 WHERE set_of_books_id = p_set_of_books_id;
215
216
217 fnd_currency.get_info( l_currency_code,
218 g_precision ,
219 g_ext_precision,
220 g_min_acct_unit);
221
222 -- ========================= FND LOG ===========================
223 psa_utils.debug_other_string(g_state_level,l_full_path,' Setting retcode to --> ' || retcode);
224 -- ========================= FND LOG ===========================
225
226 g_set_of_books_id := p_set_of_books_id;
227 g_receivable_application_id := p_receivable_app_id;
228 g_run_id := p_run_id;
229
230 -- Bug 3671841, commenting out this call and placing it in PSAMFG2B.pls
231 -- PURGE_ORPHAN_DISTRIBUTIONS;
232
233 OPEN c_rcpt_application;
234 FETCH c_rcpt_application INTO l_rcpt_application_rec;
235 CLOSE c_rcpt_application;
236
237 --
238 -- Each receipt application has a unique receivable_application_id.
239 -- Any change to the receipt application (ccid/amount/discount/...)
240 -- will create a record with a new receivable_application_id.
241 -- If psa_mf_rct_dist_all does not have corresponding records,
242 -- multi-fund distributions are either not created or invalid.
243 --
244
245 --
246 -- Cash Mgt : the function should check for existence of Cash
247 -- and/or remittance MFAR distributions
248 --
249
250 -- ========================= FND LOG ===========================
251 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling distributions_exist_and_valid ');
252 -- ========================= FND LOG ===========================
253
254 IF NOT (distributions_exist_and_valid (p_receivable_app_id)) THEN -- 1
255
256 -- ========================= FND LOG ===========================
257 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside first if ' );
258 psa_utils.debug_other_string(g_state_level,l_full_path,' distributions_exist_and_valid -> TRUE ');
259 -- ========================= FND LOG ===========================
260
261 -- Initialize global variable
262 g_cust_trx_id := l_rcpt_application_rec.cust_trx_id;
263
264 -- ========================= FND LOG ===========================
265 psa_utils.debug_other_string(g_state_level,l_full_path,' g_cust_trx_id -> ' || g_cust_trx_id );
266 psa_utils.debug_other_string(g_state_level,l_full_path,' l_rcpt_application_rec.cash_receipt_id -> ' || l_rcpt_application_rec.cash_receipt_id );
267 -- ========================= FND LOG ===========================
268
269 IF l_rcpt_application_rec.cash_receipt_id IS NULL THEN -- CREDIT MEMO APPLICATION
270 -- ========================= FND LOG ===========================
271 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside Second if');
272 -- ========================= FND LOG ===========================
273
274 l_ccid := l_rcpt_application_rec.rec_ccid;
275 -- ========================= FND LOG ===========================
276 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ccid --> ' || l_ccid);
277 -- ========================= FND LOG ===========================
278
279 OPEN c_credit_memo_type (l_rcpt_application_rec.cm_trx_id);
280 FETCH c_credit_memo_type INTO l_credit_memo_type;
281 CLOSE c_credit_memo_type;
282
283 -- ========================= FND LOG ===========================
284 psa_utils.debug_other_string(g_state_level,l_full_path,' Credit memo type --> ' || l_credit_memo_type.previous_customer_trx_id);
285 -- ========================= FND LOG ===========================
286
287 IF l_credit_memo_type.previous_customer_trx_id IS NOT NULL THEN -- Direct Credit Memo if
288
289 -- ========================= FND LOG ===========================
290 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside third if');
291 psa_utils.debug_other_string(g_state_level,l_full_path,' l_rcpt_application_rec.cm_trx_id --> ' || l_rcpt_application_rec.cm_trx_id);
292 -- ========================= FND LOG ===========================
293
294 OPEN c_direct_cm (l_rcpt_application_rec.cm_trx_id);
295 LOOP
296
297 FETCH c_direct_cm INTO l_direct_cm;
298 EXIT WHEN c_direct_cm%NOTFOUND;
299
300 -- ========================= FND LOG ===========================
301 psa_utils.debug_other_string(g_state_level,l_full_path,' l_direct_cm.trx_line_id --> ' || l_direct_cm.trx_line_id);
302 -- ========================= FND LOG ===========================
303
304 OPEN c_direct_cm_line_amount(l_rcpt_application_rec.cm_trx_id,
305 l_direct_cm.trx_line_id);
306 FETCH c_direct_cm_line_amount INTO l_direct_cm_line_amount;
307 CLOSE c_direct_cm_line_amount;
308
309 -- ========================= FND LOG ===========================
310 psa_utils.debug_other_string(g_state_level,l_full_path,' l_direct_cm_line_amount --> ' ||l_direct_cm_line_amount.line_amount);
311 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling GENERATE_RCT_DIST ');
312 -- ========================= FND LOG ===========================
313
314 IF NOT (GENERATE_RCT_DIST_CM
315 (errbuf => errbuf,
316 retcode => retcode,
317 p_rcv_app_id => p_receivable_app_id,
318 p_cash_ccid => l_ccid,
319 p_cust_trx_id => l_rcpt_application_rec.cust_trx_id,
320 p_cust_trx_line_id => l_direct_cm.trx_line_id,
321 -- Bug 2515944: l_direct_cm.amount,
322 -- p_amount_applied => l_direct_cm_line_amount.line_amount,
323 p_amount_applied1 => -1*l_rcpt_application_rec.amount_applied,
324 p_earned_discount => l_rcpt_application_rec.earned_discount,
325 p_unearned_discount => l_rcpt_application_rec.unearned_discount,
326 p_earned_discount_ccid => l_rcpt_application_rec.earned_discount_ccid,
327 p_unearned_discount_ccid => l_rcpt_application_rec.unearned_discount_ccid,
328 p_document_type => 'CM',
329 p_crh_status => NULL,
330 p_error_message => l_exception_message)) THEN
331
332 -- ========================= FND LOG ===========================
333 psa_utils.debug_other_string(g_state_level,l_full_path,' Generate_rct_dist --> FALSE ');
334 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising generate_rct_dist_excep');
335 -- ========================= FND LOG ===========================
336 RAISE generate_rct_dist_excep;
337 ELSE
338 -- ========================= FND LOG ===========================
339 psa_utils.debug_other_string(g_state_level,l_full_path,' Generate_rct_dist --> TRUE ');
340 -- ========================= FND LOG ===========================
341 END IF;
342 END LOOP;
343
344 -- ========================= FND LOG ===========================
345 psa_utils.debug_other_string(g_state_level,l_full_path,' out of c_direct_cm loop');
346 -- ========================= FND LOG ===========================
347 CLOSE c_direct_cm;
348
349 ELSE -- On Account Credit Memo
350
351 -- ========================= FND LOG ===========================
352 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside third else');
353 psa_utils.debug_other_string(g_state_level,l_full_path,' On Account Credit memo (cust_trx_line_id) --> ' || l_rcpt_application_rec.cust_trx_line_id);
354 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling GENERATE_RCT_DIST ');
355 -- ========================= FND LOG ===========================
356
357 -- Bug 3107904 amount_applied*-1 for parameter : p_amount_applied of GENERATE_RCT_DIST
358
359 IF NOT (GENERATE_RCT_DIST
360 (errbuf => errbuf,
361 retcode => retcode,
362 p_rcv_app_id => p_receivable_app_id,
363 p_cash_ccid => l_ccid,
364 p_cust_trx_id => l_rcpt_application_rec.cust_trx_id,
365 p_cust_trx_line_id => l_rcpt_application_rec.cust_trx_line_id,
366 p_amount_applied => (l_rcpt_application_rec.amount_applied)*-1,
367 p_earned_discount => l_rcpt_application_rec.earned_discount,
368 p_unearned_discount => l_rcpt_application_rec.unearned_discount,
369 p_earned_discount_ccid => l_rcpt_application_rec.earned_discount_ccid,
370 p_unearned_discount_ccid => l_rcpt_application_rec.unearned_discount_ccid,
371 p_document_type => 'CM',
372 p_crh_status => NULL,
373 p_error_message => l_exception_message)) THEN
374
375 -- ========================= FND LOG ===========================
376 psa_utils.debug_other_string(g_state_level,l_full_path,' Generate_rct_dist --> FALSE ');
377 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising generate_rct_dist_excep');
378 -- ========================= FND LOG ===========================
379 RAISE generate_rct_dist_excep;
380 ELSE
381 -- ========================= FND LOG ===========================
382 psa_utils.debug_other_string(g_state_level,l_full_path,' Generate_rct_dist --> TRUE ');
383 -- ========================= FND LOG ===========================
384 END IF;
385
386 END IF; -- Direct Credit Memo end if
387
388 ELSE
389 -- ========================= FND LOG ===========================
390 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside second else ');
391 psa_utils.debug_other_string(g_state_level,l_full_path,' l_rcpt_application_rec.cash_receipt_id IS NULL');
392 -- ========================= FND LOG ===========================
393 END IF; -- Credit memo application end if
394
395 ELSE
396 -- ========================= FND LOG ===========================
397 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside first else ');
398 psa_utils.debug_other_string(g_state_level,l_full_path,' distributions_exist_and_valid -> FALSE ');
399 -- ========================= FND LOG ===========================
400 END IF; -- 1 end if
401
402
403 -- ========================= FND LOG ===========================
404 psa_utils.debug_other_string(g_state_level,l_full_path,' Cash receipt - Cash Management ');
405 -- ========================= FND LOG ===========================
406
407 -- ## Cash receipt - Cash Management ##
408 IF (l_rcpt_application_rec.cash_receipt_id IS NOT NULL) THEN -- 2 if
409
410 OPEN c_crh;
411 LOOP
412
413 -- ========================= FND LOG ===========================
414 psa_utils.debug_other_string(g_state_level,l_full_path,' l_rcpt_application_rec.cash_receipt_id -> ' || l_rcpt_application_rec.cash_receipt_id);
415 -- ========================= FND LOG ===========================
416
417 FETCH c_crh INTO c_crh_rec;
418 EXIT WHEN c_crh%NOTFOUND;
419
420 -- ========================= FND LOG ===========================
421 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling CASH_CLR_DIST_EXIST_VALID ');
422 -- ========================= FND LOG ===========================
423
424 -- CASH RECEIPT APPLICATION
425 IF (CASH_CLR_DIST_EXIST_VALID ( p_receivable_app_id,
426 l_rcpt_application_rec.amount_applied,
427 c_crh_rec.status))
428 THEN
429
430 -- ========================= FND LOG ===========================
431 psa_utils.debug_other_string(g_state_level,l_full_path,' CASH_CLR_DIST_EXIST_VALID -> TRUE');
432 -- ========================= FND LOG ===========================
433 OPEN c_cash_ccid (l_rcpt_application_rec.cash_receipt_id,
434 c_crh_rec.status);
435 FETCH c_cash_ccid INTO l_cash_ccid;
436 CLOSE c_cash_ccid;
437
438 l_ccid := l_cash_ccid.cash_ccid;
439 -- ========================= FND LOG ===========================
440 psa_utils.debug_other_string(g_state_level,l_full_path,' cash_ccid - l_ccid -> ' || l_ccid);
441 -- ========================= FND LOG ===========================
442
443 IF l_ccid IS NOT NULL THEN
444
445 -- ========================= FND LOG ===========================
446 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside if - l_ccid is not null');
447 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling generate_rct_dist ');
448 -- ========================= FND LOG ===========================
449
450 IF NOT (GENERATE_RCT_DIST
451 (errbuf => errbuf,
452 retcode => retcode,
453 p_rcv_app_id => p_receivable_app_id,
454 p_cash_ccid => l_ccid,
455 p_cust_trx_id => l_rcpt_application_rec.cust_trx_id,
456 p_cust_trx_line_id => l_rcpt_application_rec.cust_trx_line_id,
457 p_amount_applied => l_rcpt_application_rec.amount_applied,
458 p_earned_discount => l_rcpt_application_rec.earned_discount,
459 p_unearned_discount => l_rcpt_application_rec.unearned_discount,
460 p_earned_discount_ccid => l_rcpt_application_rec.earned_discount_ccid,
461 p_unearned_discount_ccid => l_rcpt_application_rec.unearned_discount_ccid,
462 p_document_type => 'RCT',
463 p_crh_status => c_crh_rec.status,
464 p_error_message => l_exception_message)) THEN
465
466 -- ========================= FND LOG ===========================
467 psa_utils.debug_other_string(g_state_level,l_full_path,' Generate_rct_dist --> FALSE ');
468 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising generate_rct_dist_excep');
469 -- ========================= FND LOG ===========================
470 RAISE generate_rct_dist_excep;
471 ELSE
472 -- ========================= FND LOG ===========================
473 psa_utils.debug_other_string(g_state_level,l_full_path,' Generate_rct_dist --> TRUE ');
474 -- ========================= FND LOG ===========================
475 END IF;
476 ELSE
477 -- ========================= FND LOG ===========================
478 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside else - l_ccid is NULL');
479 -- ========================= FND LOG ===========================
480 END IF;
481
482 ELSE
483 -- ========================= FND LOG ===========================
484 psa_utils.debug_other_string(g_state_level,l_full_path,' CASH_CLR_DIST_EXIST_VALID -> FALSE ');
485 -- ========================= FND LOG ===========================
486 END IF;
487 END LOOP;
488 CLOSE c_crh;
489
490 ELSE
491 -- ========================= FND LOG ===========================
492 psa_utils.debug_other_string(g_state_level,l_full_path,' Cash_receipt_id -> l_rcpt_application_rec.cash_receipt_id is null');
493 -- ========================= FND LOG ===========================
494 END IF;
495
496 -- ========================= FND LOG ===========================
497 psa_utils.debug_other_string(g_state_level,l_full_path,' retcode --> ' || retcode);
498 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN TRUE ');
499 -- ========================= FND LOG ===========================
500
501 retcode := 'S';
502 RETURN TRUE;
503
504 EXCEPTION
505 WHEN generate_rct_dist_excep THEN
506 retcode := 'F';
507 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id, l_exception_message);
508 p_error_message := l_exception_message;
509 -- ========================= FND LOG ===========================
510 psa_utils.debug_other_string(g_excep_level,l_full_path,p_error_message);
511 -- ========================= FND LOG ===========================
512 RETURN FALSE;
513
514 WHEN OTHERS THEN
515 retcode := 'F';
516 p_error_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_TRANSACTIONS.CREATE_DISTRIBUTIONS - ' || SQLERRM;
517 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
518 p_error_message);
519 -- ========================= FND LOG ===========================
520 psa_utils.debug_other_string(g_excep_level,l_full_path,p_error_message);
521 psa_utils.debug_unexpected_msg(l_full_path);
522 -- ========================= FND LOG ===========================
523 RETURN FALSE;
524
525 END create_distributions;
526
527 /**************************************** DISTRIBUTIONS_EXIST_AND_VALID ************************************/
528
529 FUNCTION distributions_exist_and_valid(p_receivable_app_id IN NUMBER) RETURN BOOLEAN
530 IS
531 l_rct_dist_count NUMBER;
532 -- ========================= FND LOG ===========================
533 l_full_path VARCHAR2(100) := g_path || 'distributions_exist_and_valid';
534 -- ========================= FND LOG ===========================
535 BEGIN
536
537 -- ========================= FND LOG ===========================
538 psa_utils.debug_other_string(g_state_level,l_full_path,' Distributions_exist_and_valid --> START');
539 -- ========================= FND LOG ===========================
540
541 SELECT count(rct.receivable_application_id)
542 INTO l_rct_dist_count
543 FROM psa_mf_rct_dist_all rct
544 WHERE rct.receivable_application_id = p_receivable_app_id;
545
546 -- ========================= FND LOG ===========================
547 psa_utils.debug_other_string(g_state_level,l_full_path,' Distributions_exist_and_valid -> l_rct_dist_count ' || l_rct_dist_count);
548 -- ========================= FND LOG ===========================
549
550 IF l_rct_dist_count > 0 THEN
551 -- ========================= FND LOG ===========================
552 psa_utils.debug_other_string(g_state_level,l_full_path,' Distributions_exist_and_valid --> RETURN TRUE');
553 -- ========================= FND LOG ===========================
554 RETURN TRUE;
555 ELSE
556 -- ========================= FND LOG ===========================
557 psa_utils.debug_other_string(g_state_level,l_full_path,' Distributions_exist_and_valid --> RETURN FALSE');
558 -- ========================= FND LOG ===========================
559 RETURN FALSE;
560 END IF;
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 -- ========================= FND LOG ===========================
565 psa_utils.debug_other_string(g_excep_level,l_full_path,' Distributions_exist_and_valid -> EXCEPTION WHEN OTHERS ' || SQLERRM );
566 psa_utils.debug_unexpected_msg(l_full_path);
567 -- ========================= FND LOG ===========================
568 RETURN FALSE;
569
570 END distributions_exist_and_valid;
571
572 /**************************************** CASH_CLR_DIST_EXIST_VALID ************************************/
573
574 --
575 -- Function checks Distribution records for Cleared and remitted lines
576 --
577
578 FUNCTION cash_clr_dist_exist_valid(
579 p_receivable_app_id IN NUMBER,
580 p_amount IN NUMBER,
581 p_crh_status IN VARCHAR2) RETURN BOOLEAN
582 IS
583 l_rct_dist_count NUMBER;
584 -- ========================= FND LOG ===========================
585 l_full_path VARCHAR2(100) := g_path || 'cash_clr_dist_exist_valid';
586 -- ========================= FND LOG ===========================
587 BEGIN
588
589 -- ========================= FND LOG ===========================
590 psa_utils.debug_other_string(g_state_level,l_full_path,' Cash_clr_dist_exist_valid -> START');
591 -- ========================= FND LOG ===========================
592
593 SELECT count(rct.receivable_application_id)
594 INTO l_rct_dist_count
595 FROM psa_mf_rct_dist_all rct
596 WHERE rct.receivable_application_id = p_receivable_app_id;
597
598 -- ========================= FND LOG ===========================
599 psa_utils.debug_other_string(g_state_level,l_full_path,' p_amount -> ' || p_amount );
600 psa_utils.debug_other_string(g_state_level,l_full_path,' p_crh_status -> ' || p_crh_status );
601 psa_utils.debug_other_string(g_state_level,l_full_path,' l_rct_dist_count -> ' || l_rct_dist_count);
602 -- ========================= FND LOG ===========================
603
604 IF (p_amount < 0) AND (p_crh_status IN ('REMITTED','CLEARED')) THEN
605 -- ========================= FND LOG ===========================
606 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside first if ');
607 -- ========================= FND LOG ===========================
608 IF (l_rct_dist_count > 0) THEN
609 -- ========================= FND LOG ===========================
610 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN FALSE ');
611 -- ========================= FND LOG ===========================
612 RETURN FALSE;
613 ELSE
614 -- ========================= FND LOG ===========================
615 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN TRUE ');
616 -- ========================= FND LOG ===========================
617 RETURN TRUE;
618 END IF;
619 END IF;
620
621 IF (p_amount > 0) AND (p_crh_status = 'REVERSED') THEN
622 -- ========================= FND LOG ===========================
623 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside second if ');
624 -- ========================= FND LOG ===========================
625 IF (l_rct_dist_count > 0) THEN
626 -- ========================= FND LOG ===========================
627 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN FALSE ');
628 -- ========================= FND LOG ===========================
629 RETURN FALSE;
630 ELSE
631 -- ========================= FND LOG ===========================
632 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN TRUE ');
633 -- ========================= FND LOG ===========================
634 RETURN TRUE;
635 END IF;
636 END IF;
637
638 IF l_rct_dist_count > 0 THEN
639 -- ========================= FND LOG ===========================
640 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside third if ');
641 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN FALSE');
642 -- ========================= FND LOG ===========================
643 RETURN FALSE;
644 ELSE
645 -- ========================= FND LOG ===========================
646 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside third else ');
647 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN TRUE');
648 -- ========================= FND LOG ===========================
649 RETURN TRUE;
650 END IF;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 -- ========================= FND LOG ===========================
655 psa_utils.debug_other_string(g_state_level,l_full_path,' Cash_clr_dist_exist_valid -> EXCEPTION WHEN OTHERS ' || SQLERRM);
659
656 psa_utils.debug_unexpected_msg(l_full_path);
657 -- ========================= FND LOG ===========================
658 RETURN FALSE;
660 END cash_clr_dist_exist_valid;
661
662 /**************************************** GENERATE_RCT_DIST ************************************/
663
664 FUNCTION generate_rct_dist
665 (errbuf OUT NOCOPY VARCHAR2,
666 retcode OUT NOCOPY VARCHAR2,
667 p_rcv_app_id IN NUMBER,
668 p_cash_ccid IN NUMBER,
669 p_cust_trx_id IN NUMBER,
670 p_cust_trx_line_id IN NUMBER,
671 p_amount_applied IN NUMBER,
672 p_earned_discount IN NUMBER,
673 p_unearned_discount IN NUMBER,
674 p_earned_discount_ccid IN NUMBER,
675 p_unearned_discount_ccid IN NUMBER,
676 p_document_type IN VARCHAR2,
677 p_crh_status IN VARCHAR2 DEFAULT 'OTHER',
678 p_error_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN
679
680 IS
681
682 CURSOR c_remit_reversal_account(p_gl_dist_id IN NUMBER) IS
683 SELECT
684 mf_cash_ccid
685 FROM psa_mf_rct_dist_all
686 WHERE receivable_application_id = p_rcv_app_id
687 AND cust_trx_line_gl_dist_id = p_gl_dist_id;
688 -- AND reference1 = 'REMITTED';
689 /*
690 -- Parameter added by RM for 1604281
691 -- Order By condition added by Tpradhan for One-off Fix 3075090
692 Cursor c_accrual (sum_adr in number)
693 Is
694 Select mf_trx_dist.mf_receivables_ccid rcv_ccid,
695 mf_trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id,
696 trx_line.customer_trx_line_id trx_line_id,
697 trx_line.link_to_cust_trx_line_id link_trx_line_id,
698 -- column below changed by RM for 1604281
699 decode (sum_adr, 0, mf_balances.amount_due_original,
700 mf_balances.amount_due_remaining) amount_due
701 From ra_customer_trx_lines_all trx_line,
702 ra_cust_trx_line_gl_dist_all trx_dist,
703 psa_mf_trx_dist_all mf_trx_dist,
704 psa_mf_balances_view mf_balances
705 Where trx_line.customer_trx_id = p_cust_trx_id
706 And mf_balances.customer_trx_id = p_cust_trx_id
707 And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
708 And trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id
709 And mf_trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id
710 and trx_line.customer_trx_line_id = nvl (p_cust_trx_line_id,trx_line.customer_trx_line_id)
711 AND EXISTS (SELECT 1 FROM ra_customer_trx_lines_all x
712 WHERE x.customer_trx_line_id = trx_line.customer_trx_line_id
713 AND NVL(extended_amount, 0) <> 0)
714 ORDER BY 2 DESC;
715
716 Cursor c_cash Is
717 Select trx_dist.code_combination_id rev_ccid,
718 trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id
719 From ra_customer_trx_all trx,
720 ra_customer_trx_lines_all trx_line,
721 ra_cust_trx_line_gl_dist_all trx_dist
722 Where trx.customer_trx_id = p_cust_trx_id
723 And trx.customer_trx_id = trx_line.customer_trx_id
724 And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
725 And trx_dist.account_class = 'REV';
726 */
727
728 -- l_accrual_rec c_accrual%rowtype;
729 -- l_cash_rec c_cash%rowtype;
730 p_ccid psa_mf_rct_dist_all.mf_cash_ccid%type;
731 p_mf_earned_discount_ccid ar_receivable_applications.earned_discount_ccid%type;
732 p_mf_unearned_discount_ccid ar_receivable_applications.unearned_discount_ccid%type;
733 l_rowid ROWID;
734 run_num number(15);
735
736 l_c_accrual_stmt VARCHAR2(6000);
737
738 l_c_cash_stmt VARCHAR2(6000);
739
740 TYPE AccrualTyp IS RECORD (rcv_ccid NUMBER(15),
741 trx_line_dist_id NUMBER(15),
742 trx_line_id NUMBER(15),
743 link_trx_line_id NUMBER(15),
744 amount_due NUMBER);
745
746 TYPE var_cur IS REF CURSOR;
747
748 c_accrual_cur VAR_CUR;
749 l_accrual_rec AccrualTyp;
750
751 -- Variables for calculating amount and percent
752
753 l_amount_applied NUMBER;
754 l_running_amount NUMBER;
755 l_running_total_amount_due NUMBER;
756 l_total_amount_due NUMBER;
757 l_amount NUMBER;
758 l_percent NUMBER;
759
760 -- Variables for calculating earned discount
761
762 l_earn_discount_applied NUMBER;
763 l_earned_discount NUMBER;
764 l_running_earned_discount NUMBER;
765 l_running_total_amount_earn NUMBER;
766
767 -- Variables for calculating unearned discount
768
769 l_unearn_discount_applied NUMBER;
770 l_unearned_discount NUMBER;
771 l_running_unearn_discount NUMBER;
772 l_running_total_amount_unearn NUMBER;
773
774 -- var added below by RM for 1604281
775 sum_amt_due_rem number;
776 l_remit_reversal_ccid NUMBER(15);
777
778 l_exception_message varchar2(3000);
779 l_retcode varchar2(1);
780 l_errbuf varchar2(100);
781 l_zero_amt_flag NUMBER(1);
782
783 FLEX_BUILD_ERROR EXCEPTION;
784 INVALID_DISTRIBUTION EXCEPTION;
785
786 -- ========================= FND LOG ===========================
787 l_full_path VARCHAR2(100) := g_path || 'generate_rct_dist';
788 -- ========================= FND LOG ===========================
789
790 BEGIN
791
792 -- ========================= FND LOG ===========================
793 psa_utils.debug_other_string(g_state_level,l_full_path,' Start Generate_rct_dist ');
794 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS');
795 psa_utils.debug_other_string(g_state_level,l_full_path,' ==========');
796 psa_utils.debug_other_string(g_state_level,l_full_path,' p_rcv_app_id -> ' || p_rcv_app_id);
797 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cash_ccid -> ' || p_cash_ccid);
798 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_id -> ' || p_cust_trx_id);
799 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_line_id -> ' || p_cust_trx_line_id);
800 psa_utils.debug_other_string(g_state_level,l_full_path,' p_amount_applied -> ' || p_amount_applied);
801 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount -> ' || p_earned_discount);
802 psa_utils.debug_other_string(g_state_level,l_full_path,' p_unearned_discount -> ' || p_unearned_discount);
803 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount_ccid -> ' || p_earned_discount_ccid);
804 psa_utils.debug_other_string(g_state_level,l_full_path,' p_unearned_discount_ccid -> ' || p_unearned_discount_ccid);
805 psa_utils.debug_other_string(g_state_level,l_full_path,' p_document_type -> ' || p_document_type);
806 psa_utils.debug_other_string(g_state_level,l_full_path,' p_crh_status -> ' || p_crh_status);
807 psa_utils.debug_other_string(g_state_level,l_full_path,' Starting the process ');
808 -- ========================= FND LOG ===========================
809
810 -- Bug 2609367
811 -- call psa_mfar_transactions.create_distributions
812 -- When 'Create Distributions is run as a Conc. program OR invoked from the Action of opening MFAR Form,
813 -- PSA_MF_CREATE_DISTRIBUTIONS package is called. This makes sure that MFAR distributions for a Transaction
814 -- is created before proceeding to create Distributions for Receipts etc.
815 -- However, MFAR dist. for a Trx is not created when GL Xfr is executed for a Cash Receipt whose GL Date is
816 -- different from the transaction AND when MFAR for the Trx has not been created through the other means.
817
818 select psa_mF_error_log_s.currval
819 into run_num
820 from sys.dual;
821
822 -- ========================= FND LOG ===========================
823 psa_utils.debug_other_string(g_state_level,l_full_path,' Creating distribution for Cust trx id ==> ' || run_num || ' -- ' || p_cust_trx_id);
824 -- ========================= FND LOG ===========================
825
826 IF NOT (PSA_MFAR_TRANSACTIONS.create_distributions (
827 errbuf => l_errbuf,
828 retcode => l_retcode,
829 p_cust_trx_id => p_cust_trx_id,
830 p_set_of_books_id => g_set_of_books_id,
831 p_run_id => run_num,
832 p_error_message => l_exception_message)) THEN
833
834 -- ========================= FND LOG ===========================
835 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_TRANSACTIONS.create_distributions --> FALSE');
836 -- ========================= FND LOG ===========================
837 IF l_exception_message IS NOT NULL OR l_retcode = 'F' THEN
838 -- ========================= FND LOG ===========================
839 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising invalid_distribution');
840 -- ========================= FND LOG ===========================
841 Raise invalid_distribution;
842 END IF;
843 ELSE
844 -- ========================= FND LOG ===========================
845 psa_utils.debug_other_string(g_state_level,l_full_path,' cust_trx_id --> ' || p_cust_trx_id);
846 -- ========================= FND LOG ===========================
847 END IF;
848
849 -- ========================= FND LOG ===========================
850 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount --> ' || p_earned_discount );
851 psa_utils.debug_other_string(g_state_level,l_full_path,' p_unearned_discount --> ' || p_unearned_discount );
852 -- ========================= FND LOG ===========================
853
854 IF (p_earned_discount <> 0 OR p_unearned_discount <> 0) THEN
855 -- ========================= FND LOG ===========================
856 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling populate_discount_cache --> ' || p_cust_trx_id);
857 -- ========================= FND LOG ===========================
858 POPULATE_DISCOUNT_LINES_CACHE (p_cust_trx_id);
859 END IF;
860
861
862
863 l_zero_amt_flag := 0;
864
865 -- Check to see if a trx is a zero dollar invoice
866 BEGIN
867
868 SELECT 1
869 INTO l_zero_amt_flag
870 FROM DUAL
871 WHERE EXISTS (SELECT 1
872 FROM ra_customer_trx_lines_all
873 WHERE customer_trx_id = p_cust_trx_id
874 AND extended_amount <> 0 );
875 EXCEPTION
876 WHEN NO_DATA_FOUND THEN
877 l_zero_amt_flag := 0;
878 END;
879
880 -- ========================= FND LOG ===========================
881 psa_utils.debug_other_string(g_state_level,l_full_path,
882 ' l_zero_amt_flag --> ' || l_zero_amt_flag );
883
884 -- ========================= FND LOG ===========================
885
886
887
888 SELECT
889 decode (sum(mf_balances.amount_due_remaining),0,
890 sum(mf_balances.amount_due_original),
891 decode (l_zero_amt_flag,1,sum(mf_balances.amount_due_original),
892 sum(mf_balances.amount_due_remaining))) total_amount_due,
893 decode (l_zero_amt_flag,1,sum(mf_balances.amount_due_original),
894 sum(mf_balances.amount_due_remaining)) sum_amt_due_rem
895 INTO l_total_amount_due,
896 sum_amt_due_rem
897 FROM ra_customer_trx_lines_all trx_line,
898 ra_cust_trx_line_gl_dist_all trx_dist,
899 psa_mf_balances_view mf_balances
900 WHERE trx_line.customer_trx_id = p_cust_trx_id
901 AND mf_balances.customer_trx_id = p_cust_trx_id
902 AND trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
903 AND trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id
904 AND trx_line.customer_trx_line_id = nvl(p_cust_trx_line_id, trx_line.customer_trx_line_id);
905
906 -- ========================= FND LOG ===========================
907 psa_utils.debug_other_string(g_state_level,l_full_path,' l_total_amount_due --> ' || l_total_amount_due );
908 psa_utils.debug_other_string(g_state_level,l_full_path,' sum_amt_due_rem --> ' || sum_amt_due_rem );
909 -- ========================= FND LOG ===========================
910
911
912 --
913 -- Initailize variables for running total
914 --
915
916 l_running_amount := 0;
917 l_running_total_amount_due := l_total_amount_due ;
918
919 l_running_earned_discount := 0;
920 l_running_total_amount_earn := l_total_amount_due;
921
922 l_running_unearn_discount := 0;
923 l_running_total_amount_unearn := l_total_amount_due;
924
925
926 -- ========================= FND LOG ===========================
927 psa_utils.debug_other_string(g_state_level,l_full_path,' l_running_total_amount_due --> ' || l_running_total_amount_due );
928 psa_utils.debug_other_string(g_state_level,l_full_path,' l_running_total_amount_earn --> ' || l_running_total_amount_earn);
929 psa_utils.debug_other_string(g_state_level,l_full_path,' l_running_total_amount_unearn --> ' || l_running_total_amount_unearn);
930 -- ========================= FND LOG ===========================
931
932 l_c_accrual_stmt :=
933 'Select mf_trx_dist.mf_receivables_ccid rcv_ccid, ' ||
934 ' mf_trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
935 ' trx_line.customer_trx_line_id trx_line_id, ' ||
936 ' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
937 ' decode (:l_zero_amt_flag, 1, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due ' ||
938 ' From ra_customer_trx_lines trx_line, ' ||
939 ' ra_cust_trx_line_gl_dist trx_dist, ' ||
940 ' psa_mf_trx_dist_all mf_trx_dist, ' ||
941 ' psa_mf_balances_view mf_balances ' ||
942 ' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
943 ' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
944 ' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
945 ' And trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id ' ||
946 ' And mf_trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
947 ' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
948 /* ' ||
949 ' And EXISTS (SELECT 1 FROM ra_customer_trx_lines_all x ' ||
950 ' WHERE x.customer_trx_line_id = trx_line.customer_trx_line_id AND NVL(extended_amount, 0) <> 0) '; */
951
952 l_c_cash_stmt :=
953 'Select trx_dist.code_combination_id rcv_ccid, ' ||
954 ' trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
955 ' trx_line.customer_trx_line_id trx_line_id, ' ||
956 ' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
957 ' decode (:l_zero_amt_flag, 1, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due ' ||
958 ' From ra_customer_trx_lines trx_line, ' ||
959 ' ra_cust_trx_line_gl_dist trx_dist, ' ||
960 ' psa_mf_balances_view mf_balances ' ||
961 ' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
962 ' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
963 ' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
964 ' And trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
965 ' And trx_dist.account_class <> '''||'REC'||''' ' ||
966 ' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
967 /* ' ||
968 ' And EXISTS (SELECT 1 FROM ra_customer_trx_lines_all x ' ||
969 ' WHERE x.customer_trx_line_id = trx_line.customer_trx_line_id AND NVL(extended_amount, 0) <> 0) '; */
970
971
972 IF l_total_amount_due < 0 THEN
973 l_c_accrual_stmt := l_c_accrual_stmt || ' order by 5 desc ';
974 l_c_cash_stmt := l_c_cash_stmt || ' order by 5 desc ';
975
976 ELSE
977 l_c_accrual_stmt := l_c_accrual_stmt || ' order by 5 asc ';
978 l_c_cash_stmt := l_c_cash_stmt || ' order by 5 asc ';
979 END IF;
980
981 -- ========================= FND LOG ===========================
982 psa_utils.debug_other_string(g_state_level,l_full_path,' arp_global.sysparam.accounting_method --> '
983 || arp_global.sysparam.accounting_method);
984 -- ========================= FND LOG ===========================
985
986 IF arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
987 OPEN c_accrual_cur FOR l_c_accrual_stmt USING l_zero_amt_flag, p_cust_trx_id, p_cust_trx_id, p_cust_trx_line_id;
988 ELSIF arp_global.sysparam.accounting_method = 'CASH' THEN
989 OPEN c_accrual_cur FOR l_c_cash_stmt USING l_zero_amt_flag, p_cust_trx_id, p_cust_trx_id, p_cust_trx_line_id;
990 END IF;
991
992
993 LOOP
994 FETCH c_accrual_cur INTO l_accrual_rec;
995 EXIT WHEN c_accrual_cur%NOTFOUND;
996
997 -- ========================= FND LOG ===========================
998 psa_utils.debug_other_string(g_state_level,l_full_path,' l_accrual_rec.trx_line_dist_id --> ' || l_accrual_rec.trx_line_dist_id );
999 -- ========================= FND LOG ===========================
1000
1001 --
1002 -- If Remittance MFAR distributions have already been created
1003 -- applicable only for Receipts cleared by CashMgt
1004 --
1005
1006 IF p_crh_status = 'CLEARED' AND NOT CASH_CLR_DIST_EXIST_VALID (p_rcv_app_id, p_amount_applied, 'REMITTED') THEN
1007
1008 OPEN c_remit_reversal_account (l_accrual_rec.trx_line_dist_id);
1009 FETCH c_remit_reversal_account INTO l_remit_reversal_ccid;
1010 CLOSE c_remit_reversal_account;
1011 -- ========================= FND LOG ===========================
1012 psa_utils.debug_other_string(g_state_level,l_full_path,' l_remit_reversal_ccid --> ' || l_remit_reversal_ccid);
1013 -- ========================= FND LOG ===========================
1014 END IF;
1015
1016 -- ========================= FND LOG ===========================
1017 psa_utils.debug_other_string(g_state_level,l_full_path,' p_document_type --> ' || p_document_type );
1018 -- ========================= FND LOG ===========================
1019
1020 IF p_document_type = 'CM' THEN
1021 p_ccid := l_accrual_rec.rcv_ccid;
1022 -- ========================= FND LOG ===========================
1023 psa_utils.debug_other_string(g_state_level,l_full_path,' if cond - document type is CM --> ' || p_ccid);
1024 -- ========================= FND LOG ===========================
1025 ELSE
1026 -- ========================= FND LOG ===========================
1027 psa_utils.debug_other_string(g_state_level,l_full_path,' else cond - calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS ');
1028 -- ========================= FND LOG ===========================
1029
1030 IF NOT ( PSA_MFAR_UTILS.OVERRIDE_SEGMENTS ( P_PRIMARY_CCID => p_cash_ccid,
1031 P_OVERRIDE_CCID => l_accrual_rec.rcv_ccid,
1032 P_SET_OF_BOOKS_ID => g_set_of_books_id,
1033 P_TRX_TYPE => 'RCT',
1034 P_CCID => p_ccid)) -- OUT
1035 THEN
1036 -- ========================= FND LOG ===========================
1037 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -> FALSE ');
1038 -- ========================= FND LOG ===========================
1039 RAISE FLEX_BUILD_ERROR;
1040 ELSE
1041 -- ========================= FND LOG ===========================
1042 psa_utils.debug_other_string(g_state_level,l_full_path,' p_ccid -> ' || p_ccid);
1043 -- ========================= FND LOG ===========================
1044 END IF;
1045 END IF;
1046
1047 --
1048 -- Prorate amount applied
1049 --
1050
1051 IF p_cust_trx_line_id Is NOT NULL THEN
1052 -- ========================= FND LOG ===========================
1053 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_line_id Is NOT NULL');
1054 -- ========================= FND LOG ===========================
1055
1056 IF (p_cust_trx_line_id = l_accrual_rec.trx_line_id)
1057 AND NOT (l_running_total_amount_due = 0) THEN -- to avoid divide by zero error
1058
1059 l_amount_applied := p_amount_applied - l_running_amount;
1060 l_amount := ROUND((l_amount_applied*l_accrual_rec.amount_due/l_running_total_amount_due), g_precision);
1061
1062 IF NVL(p_amount_applied,0) <> 0 THEN -- Bug3884271
1063 l_percent := ROUND((l_amount/p_amount_applied*100), 4);
1064 ELSE
1065 l_percent := 0;
1066 END IF;
1067
1068 l_running_amount := l_running_amount + l_amount;
1069 l_running_total_amount_due := l_running_total_amount_due - l_accrual_rec.amount_due;
1070
1071 -- ========================= FND LOG ===========================
1072 psa_utils.debug_other_string(g_state_level,l_full_path,' IF part ' ||
1073 ' ##l_amount_applied --> ' || l_amount_applied ||
1074 ' ##l_amount --> ' || l_amount ||
1075 ' ##l_percent --> ' || l_percent ||
1076 ' ##l_running_amount --> ' || l_running_amount ||
1077 ' ##l_running_total_amount_due --> ' || l_running_total_amount_due);
1078 -- ========================= FND LOG ===========================
1079
1080 ELSE
1081
1082 l_amount := 0;
1083 l_percent := 0;
1084 -- ========================= FND LOG ===========================
1085 psa_utils.debug_other_string(g_state_level,l_full_path,' ##l_amount --> ' || l_amount || ' ##l_percent --> ' || l_percent);
1086 -- ========================= FND LOG ===========================
1087 END IF;
1088 ELSE
1089 -- ========================= FND LOG ===========================
1090 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_line_id Is NULL');
1091 -- ========================= FND LOG ===========================
1092
1093 IF NOT (l_running_total_amount_due = 0) THEN -- to avoid divide by zero error
1094
1098 IF NVL(p_amount_applied,0) <> 0 THEN -- Bug3884271
1095 l_amount_applied := p_amount_applied - l_running_amount;
1096 l_amount := ROUND((l_amount_applied*l_accrual_rec.amount_due/l_running_total_amount_due), g_precision);
1097
1099 l_percent := ROUND((l_amount/p_amount_applied*100), 4);
1100 ELSE
1101 l_percent := 0;
1102 END IF;
1103
1104 l_running_amount := l_running_amount + l_amount;
1105 l_running_total_amount_due := l_running_total_amount_due - l_accrual_rec.amount_due;
1106
1107 -- ========================= FND LOG ===========================
1108 psa_utils.debug_other_string(g_state_level,l_full_path,' ELSE part ' ||
1109 ' ##l_amount_applied --> ' || l_amount_applied ||
1110 ' ##l_amount --> ' || l_amount ||
1111 ' ##l_percent --> ' || l_percent ||
1112 ' ##l_running_amount --> ' || l_running_amount ||
1113 ' ##l_running_total_amount_due --> ' || l_running_total_amount_due);
1114 -- ========================= FND LOG ===========================
1115 END IF;
1116 END IF;
1117
1118 --
1119 -- Prorate earned/unearned discount
1120 --
1121
1122 IF (p_earned_discount <> 0 OR p_unearned_discount <> 0) THEN
1123
1124 IF LINE_IN_DISCOUNT_CACHE (l_accrual_rec.trx_line_id) THEN
1125
1126 -- ========================= FND LOG ===========================
1127 psa_utils.debug_other_string(g_state_level,l_full_path,' calling LINE_IN_DISCOUNT_CACHE --> ' || l_accrual_rec.trx_line_id);
1128 psa_utils.debug_other_string(g_state_level,l_full_path,' prorate earned discount ');
1129 -- ========================= FND LOG ===========================
1130
1131 --
1132 -- Prorate Earned Discount
1133 --
1134
1135 IF (p_earned_discount <> 0)
1136 AND NOT (l_running_total_amount_earn = 0) THEN -- to avoid divide by zero error
1137
1138 l_earn_discount_applied := p_earned_discount - l_running_earned_discount;
1139 l_earned_discount := ROUND((l_earn_discount_applied*l_accrual_rec.amount_due/l_running_total_amount_earn),g_precision);
1140 l_running_earned_discount := l_running_earned_discount + l_earned_discount;
1141 l_running_total_amount_earn := l_running_total_amount_earn - l_accrual_rec.amount_due;
1142
1143 -- ========================= FND LOG ===========================
1144 psa_utils.debug_other_string(g_state_level,l_full_path,' IF part ' ||
1145 ' ##l_earn_discount_applied --> ' || l_earn_discount_applied ||
1146 ' ##l_earned_discount --> ' || l_earned_discount ||
1147 ' ##l_running_earned_discount --> ' || l_running_earned_discount ||
1148 ' ##l_running_total_amount_earn --> ' || l_running_total_amount_earn);
1149 -- ========================= FND LOG ===========================
1150
1151 ELSE
1152 l_earned_discount := 0;
1153 -- ========================= FND LOG ===========================
1154 psa_utils.debug_other_string(g_state_level,l_full_path,
1155 ' ELSE part ##l_earned_discount --> ' || l_earned_discount);
1156 -- ========================= FND LOG ===========================
1157 END IF;
1158
1159 --
1160 -- Prorate Unearned Discount
1161 --
1162
1163 -- ========================= FND LOG ===========================
1164 psa_utils.debug_other_string(g_state_level,l_full_path,' prorate unearned discount ');
1165 -- ========================= FND LOG ===========================
1166
1167 IF p_unearned_discount <> 0
1168 AND NOT (l_running_total_amount_unearn = 0) THEN -- to avoid divide by zero error
1169
1170 l_unearn_discount_applied := p_unearned_discount - l_running_unearn_discount;
1171 l_unearned_discount := ROUND((l_unearn_discount_applied*l_accrual_rec.amount_due/l_running_total_amount_unearn),g_precision);
1172 l_running_unearn_discount := l_running_unearn_discount + l_unearned_discount;
1173 l_running_total_amount_unearn := l_running_total_amount_unearn - l_accrual_rec.amount_due;
1174
1175 -- ========================= FND LOG ===========================
1176 psa_utils.debug_other_string(g_state_level,l_full_path,' IF part ' ||
1177 ' ##l_earn_discount_applied --> ' || l_earn_discount_applied ||
1178 ' ##l_earned_discount --> ' || l_earned_discount ||
1179 ' ##l_running_earned_discount --> ' || l_running_earned_discount ||
1180 ' ##l_running_total_amount_earn --> ' || l_running_total_amount_earn);
1181 -- ========================= FND LOG ===========================
1182 ELSE
1183 l_unearned_discount := 0;
1184 -- ========================= FND LOG ===========================
1185 psa_utils.debug_other_string(g_state_level,l_full_path,' ELSE part ##l_unearned_discount --> ' || l_unearned_discount);
1186 -- ========================= FND LOG ===========================
1187
1188 END IF;
1189
1190 IF p_earned_discount_ccid IS NOT NULL THEN
1191 -- ========================= FND LOG ===========================
1192 psa_utils.debug_other_string(g_state_level,l_full_path,
1193 ' calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS for earned discount');
1194 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount_ccid IS NOT NULL ');
1195 psa_utils.debug_other_string(g_state_level,l_full_path,
1196 ' ##p_unearned_discount_ccid --> ' || p_unearned_discount_ccid ||
1197 ' ##rcv_ccid --> ' || l_accrual_rec.rcv_ccid ||
1198 ' ##g_set_of_books_id --> ' || g_set_of_books_id ||
1199 ' ##p_mf_earned_discount_ccid --> ' || p_mf_earned_discount_ccid);
1200 -- ========================= FND LOG ===========================
1201
1202 IF NOT ( PSA_MFAR_UTILS.OVERRIDE_SEGMENTS (p_earned_discount_ccid,
1203 l_accrual_rec.rcv_ccid,
1204 g_set_of_books_id,'RCT',
1205 p_mf_earned_discount_ccid) ) THEN
1206
1207 -- ========================= FND LOG ===========================
1208 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -> FALSE');
1209 -- ========================= FND LOG ===========================
1210 RAISE FLEX_BUILD_ERROR;
1211 END IF;
1212 ELSE
1213 -- ========================= FND LOG ===========================
1214 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount_ccid IS NULL ');
1215 -- ========================= FND LOG ===========================
1216 END IF;
1217
1218 IF p_unearned_discount_ccid IS NOT NULL THEN
1219 -- ========================= FND LOG ===========================
1220 psa_utils.debug_other_string(g_state_level,l_full_path,
1221 ' calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS for unearned discount');
1222 psa_utils.debug_other_string(g_state_level,l_full_path,
1223 ' p_earned_discount_ccid IS NOT NULL ');
1224 psa_utils.debug_other_string(g_state_level,l_full_path,
1225 ' ##p_unearned_discount_ccid --> ' || p_unearned_discount_ccid ||
1226 ' ##rcv_ccid --> ' || l_accrual_rec.rcv_ccid ||
1227 ' ##g_set_of_books_id --> ' || g_set_of_books_id ||
1228 ' ##p_mf_unearned_discount_ccid --> ' || p_mf_unearned_discount_ccid);
1229 -- ========================= FND LOG ===========================
1230
1231 IF NOT ( PSA_MFAR_UTILS.OVERRIDE_SEGMENTS ( P_PRIMARY_CCID => p_unearned_discount_ccid,
1232 P_OVERRIDE_CCID => l_accrual_rec.rcv_ccid,
1233 P_SET_OF_BOOKS_ID => g_set_of_books_id,
1234 P_TRX_TYPE => 'RCT',
1235 P_CCID => p_mf_unearned_discount_ccid)) -- OUT
1236 THEN
1237 -- ========================= FND LOG ===========================
1238 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -> FALSE ');
1239 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising flex_build_error ');
1240 -- ========================= FND LOG ===========================
1241 RAISE FLEX_BUILD_ERROR;
1242 ELSE
1243 -- ========================= FND LOG ===========================
1244 psa_utils.debug_other_string(g_state_level,l_full_path,
1245 ' p_mf_unearned_discount_ccid -> ' || p_mf_unearned_discount_ccid);
1246 -- ========================= FND LOG ===========================
1247 END IF;
1248 END IF;
1249 END IF;
1250 END IF;
1251
1252 -- ========================= FND LOG ===========================
1256 --
1253 psa_utils.debug_other_string(g_state_level,l_full_path,' calling PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW');
1254 -- ========================= FND LOG ===========================
1255
1257 -- Insert into psa_mf_rct_dist_all
1258 --
1259
1260 PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW
1261 (
1262 x_rowid => l_rowid,
1263 x_receivable_application_id => p_rcv_app_id,
1264 x_cust_trx_line_gl_dist_id => l_accrual_rec.trx_line_dist_id,
1265 x_attribute_category => NULL,
1266 x_mf_cash_ccid => p_ccid,
1267 x_amount => nvl(l_amount, 0),
1268 x_percent => nvl(l_percent,0),
1269 x_discount_ccid => p_mf_earned_discount_ccid,
1270 x_ue_discount_ccid => p_mf_unearned_discount_ccid,
1271 x_discount_amount => nvl(l_earned_discount,0),
1272 x_ue_discount_amount => nvl(l_unearned_discount,0),
1273 x_comments => NULL,
1274 x_posting_control_id => NULL,
1275 x_attribute1 => NULL,
1276 x_attribute2 => NULL,
1277 x_attribute3 => NULL,
1278 x_attribute4 => NULL,
1279 x_attribute5 => NULL,
1280 x_attribute6 => NULL,
1281 x_attribute7 => NULL,
1282 x_attribute8 => NULL,
1283 x_attribute9 => NULL,
1284 x_attribute10 => NULL,
1285 x_attribute11 => NULL,
1286 x_attribute12 => NULL,
1287 x_attribute13 => NULL,
1288 x_attribute14 => NULL,
1289 x_attribute15 => NULL,
1290 X_REFERENCE4 => NULL,
1291 X_REFERENCE5 => NULL,
1292 X_REFERENCE2 => NULL,
1293 X_REFERENCE1 => p_crh_status,
1294 X_REFERENCE3 => NULL,
1295 X_REVERSAL_CCID => l_remit_reversal_ccid,
1296 x_mode => 'R' );
1297
1298
1299 END LOOP;
1300 CLOSE c_accrual_cur;
1301
1302 -- ========================= FND LOG ===========================
1303 psa_utils.debug_other_string(g_state_level,l_full_path,' retcode --> ' || retcode );
1304 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN TRUE ');
1305 -- ========================= FND LOG ===========================
1306
1307 retcode := 'S';
1308 RETURN TRUE;
1309
1310
1311 EXCEPTION
1312 -- Bug 3672756
1313 WHEN INVALID_DISTRIBUTION THEN
1314 -- ========================= FND LOG ===========================
1315 psa_utils.debug_other_string(g_excep_level,l_full_path,
1316 ' --> EXCEPTION - INVALID_DISTRIBUTION raised during PSA_MFAR_RECEIPTS.generate_rct_dist ');
1317 psa_utils.debug_other_string(g_excep_level,l_full_path,
1318 ' --> p_error_message --> ' || l_exception_message);
1319 -- ========================= FND LOG ===========================
1320 p_error_message := l_exception_message;
1321 retcode := 'F';
1322 RETURN FALSE;
1323
1324 WHEN FLEX_BUILD_ERROR THEN
1325 l_exception_message := fnd_message.get;
1326 -- ========================= FND LOG ===========================
1327 psa_utils.debug_other_string(g_excep_level,l_full_path,
1328 ' --> EXCEPTION - FLEX_BUILD_ERROR raised during PSA_MFAR_RECEIPTS.generate_rct_dist ');
1329 psa_utils.debug_other_string(g_excep_level,l_full_path,
1330 ' --> p_error_message --> ' || l_exception_message);
1331 -- ========================= FND LOG ===========================
1332 p_error_message := l_exception_message;
1333 retcode := 'F';
1334 RETURN FALSE;
1335
1336 WHEN OTHERS THEN
1337 l_exception_message := l_exception_message || SQLCODE || ' - ' || SQLERRM;
1338 -- ========================= FND LOG ===========================
1339 psa_utils.debug_other_string(g_excep_level,l_full_path,
1340 ' --> EXCEPTION - OTHERS raised during PSA_MFAR_RECEIPTS.generate_rct_dist ');
1341 psa_utils.debug_other_string(g_excep_level,l_full_path,
1342 ' --> p_error_message --> ' || l_exception_message);
1343 psa_utils.debug_unexpected_msg(l_full_path);
1344 -- ========================= FND LOG ===========================
1345 p_error_message := l_exception_message;
1346 retcode := 'F';
1347 RETURN FALSE;
1348
1349 END generate_rct_dist;
1350
1351 /**************************************** GENERATE_RCT_DIST_CM ************************************/
1352
1353 FUNCTION generate_rct_dist_cm
1354 (errbuf OUT NOCOPY VARCHAR2,
1355 retcode OUT NOCOPY VARCHAR2,
1356 p_rcv_app_id IN NUMBER,
1357 p_cash_ccid IN NUMBER,
1358 p_cust_trx_id IN NUMBER,
1359 p_cust_trx_line_id IN NUMBER,
1360 p_amount_applied1 IN NUMBER,
1361 p_earned_discount IN NUMBER,
1362 p_unearned_discount IN NUMBER,
1363 p_earned_discount_ccid IN NUMBER,
1364 p_unearned_discount_ccid IN NUMBER,
1365 p_document_type IN VARCHAR2,
1366 p_crh_status IN VARCHAR2 DEFAULT 'OTHER',
1370
1367 p_error_message OUT NOCOPY VARCHAR2) RETURN BOOLEAN
1368
1369 IS
1371 CURSOR c_remit_reversal_account(p_gl_dist_id IN NUMBER) IS
1372 SELECT
1373 mf_cash_ccid
1374 FROM psa_mf_rct_dist_all
1375 WHERE receivable_application_id = p_rcv_app_id
1376 AND cust_trx_line_gl_dist_id = p_gl_dist_id;
1377
1378 -- l_accrual_rec c_accrual%rowtype;
1379 -- l_cash_rec c_cash%rowtype;
1380 p_ccid psa_mf_rct_dist_all.mf_cash_ccid%type;
1381 p_mf_earned_discount_ccid ar_receivable_applications.earned_discount_ccid%type;
1382 p_mf_unearned_discount_ccid ar_receivable_applications.unearned_discount_ccid%type;
1383 l_rowid ROWID;
1384 run_num number(15);
1385
1386 l_c_accrual_stmt VARCHAR2(6000);
1387
1388 l_c_cash_stmt VARCHAR2(6000);
1389
1390 TYPE AccrualTyp IS RECORD (rcv_ccid NUMBER(15),
1391 trx_line_dist_id NUMBER(15),
1392 trx_line_id NUMBER(15),
1393 link_trx_line_id NUMBER(15),
1394 amount_due NUMBER,
1395 ACCOUNT_CLASS varchar2(100));
1396
1397 TYPE var_cur IS REF CURSOR;
1398
1399 c_accrual_cur VAR_CUR;
1400 l_accrual_rec AccrualTyp;
1401
1402 -- Variables for calculating amount and percent
1403 p_amount_applied NUMBER;
1404 l_amount_applied NUMBER;
1405 l_running_amount NUMBER;
1406 l_running_total_amount_due NUMBER;
1407 l_total_amount_due NUMBER;
1408 l_amount NUMBER;
1409 l_percent NUMBER;
1410 l_line_amount NUMBER;
1411 l_tax_amount NUMBER;
1412
1413 -- Variables for calculating earned discount
1414
1415 l_earn_discount_applied NUMBER;
1416 l_earned_discount NUMBER;
1417 l_running_earned_discount NUMBER;
1418 l_running_total_amount_earn NUMBER;
1419
1420 -- Variables for calculating unearned discount
1421
1422 l_unearn_discount_applied NUMBER;
1423 l_unearned_discount NUMBER;
1424 l_running_unearn_discount NUMBER;
1425 l_running_total_amount_unearn NUMBER;
1426 l_count NUMBER;
1427
1428
1429 -- var added below by RM for 1604281
1430 sum_amt_due_rem number;
1431 l_remit_reversal_ccid NUMBER(15);
1432
1433 l_exception_message varchar2(3000);
1434 l_retcode varchar2(1);
1435 l_errbuf varchar2(100);
1436
1437 FLEX_BUILD_ERROR EXCEPTION;
1438 INVALID_DISTRIBUTION EXCEPTION;
1439
1440 -- ========================= FND LOG ===========================
1441 l_full_path VARCHAR2(100) := g_path || 'generate_rct_dist_cm';
1442 -- ========================= FND LOG ===========================
1443
1444 BEGIN
1445
1446 -- ========================= FND LOG ===========================
1447 psa_utils.debug_other_string(g_state_level,l_full_path,' Start Generate_rct_dist_cm ');
1448 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS');
1449 psa_utils.debug_other_string(g_state_level,l_full_path,' ==========');
1450 psa_utils.debug_other_string(g_state_level,l_full_path,' p_rcv_app_id -> ' || p_rcv_app_id);
1451 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cash_ccid -> ' || p_cash_ccid);
1452 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_id -> ' || p_cust_trx_id);
1453 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_line_id -> ' || p_cust_trx_line_id);
1454 psa_utils.debug_other_string(g_state_level,l_full_path,' p_amount_applied -> ' || p_amount_applied);
1455 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount -> ' || p_earned_discount);
1456 psa_utils.debug_other_string(g_state_level,l_full_path,' p_unearned_discount -> ' || p_unearned_discount);
1457 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount_ccid -> ' || p_earned_discount_ccid);
1458 psa_utils.debug_other_string(g_state_level,l_full_path,' p_unearned_discount_ccid -> ' || p_unearned_discount_ccid);
1459 psa_utils.debug_other_string(g_state_level,l_full_path,' p_document_type -> ' || p_document_type);
1460 psa_utils.debug_other_string(g_state_level,l_full_path,' p_crh_status -> ' || p_crh_status);
1461 psa_utils.debug_other_string(g_state_level,l_full_path,' Starting the process ');
1462 -- ========================= FND LOG ===========================
1463
1464 -- Bug 2609367
1465 -- call psa_mfar_transactions.create_distributions
1466 -- When 'Create Distributions is run as a Conc. program OR invoked from the Action of opening MFAR Form,
1467 -- PSA_MF_CREATE_DISTRIBUTIONS package is called. This makes sure that MFAR distributions for a Transaction
1468 -- is created before proceeding to create Distributions for Receipts etc.
1469 -- However, MFAR dist. for a Trx is not created when GL Xfr is executed for a Cash Receipt whose GL Date is
1470 -- different from the transaction AND when MFAR for the Trx has not been created through the other means.
1471
1472 select psa_mF_error_log_s.currval
1473 into run_num
1474 from sys.dual;
1475
1476 -- ========================= FND LOG ===========================
1477 psa_utils.debug_other_string(g_state_level,l_full_path,' Creating distribution for Cust trx id ==> ' || run_num || ' -- ' || p_cust_trx_id);
1478 -- ========================= FND LOG ===========================
1479
1480 IF NOT (PSA_MFAR_TRANSACTIONS.create_distributions (
1481 errbuf => l_errbuf,
1482 retcode => l_retcode,
1483 p_cust_trx_id => p_cust_trx_id,
1484 p_set_of_books_id => g_set_of_books_id,
1485 p_run_id => run_num,
1486 p_error_message => l_exception_message)) THEN
1487
1488 -- ========================= FND LOG ===========================
1489 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_TRANSACTIONS.create_distributions --> FALSE');
1490 -- ========================= FND LOG ===========================
1491 IF l_exception_message IS NOT NULL OR l_retcode = 'F' THEN
1492 -- ========================= FND LOG ===========================
1493 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising invalid_distribution');
1494 -- ========================= FND LOG ===========================
1495 Raise invalid_distribution;
1496 END IF;
1497 ELSE
1498 -- ========================= FND LOG ===========================
1499 psa_utils.debug_other_string(g_state_level,l_full_path,' cust_trx_id --> ' || p_cust_trx_id);
1500 -- ========================= FND LOG ===========================
1501 END IF;
1502
1503 -- ========================= FND LOG ===========================
1504 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount --> ' || p_earned_discount );
1505 psa_utils.debug_other_string(g_state_level,l_full_path,' p_unearned_discount --> ' || p_unearned_discount );
1506 -- ========================= FND LOG ===========================
1507
1508 IF (p_earned_discount <> 0 OR p_unearned_discount <> 0) THEN
1509 -- ========================= FND LOG ===========================
1510 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling populate_discount_cache --> ' || p_cust_trx_id);
1511 -- ========================= FND LOG ===========================
1512 POPULATE_DISCOUNT_LINES_CACHE (p_cust_trx_id);
1513 END IF;
1514
1515 SELECT
1516 decode (sum(mf_balances.amount_due_remaining),0,
1517 sum(mf_balances.amount_due_original),
1518 sum(mf_balances.amount_due_original)) total_amount_due,
1519 sum(mf_balances.amount_due_original) sum_amt_due_rem
1520 INTO l_total_amount_due,
1521 sum_amt_due_rem
1522 FROM ra_customer_trx_lines_all trx_line,
1523 ra_cust_trx_line_gl_dist_all trx_dist,
1524 psa_mf_balances_view mf_balances
1525 WHERE trx_line.customer_trx_id = p_cust_trx_id
1526 AND mf_balances.customer_trx_id = p_cust_trx_id
1527 AND trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
1528 AND trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id
1529 AND trx_line.customer_trx_line_id = nvl(p_cust_trx_line_id, trx_line.customer_trx_line_id);
1530
1531 -- ========================= FND LOG ===========================
1532 psa_utils.debug_other_string(g_state_level,l_full_path,' l_total_amount_due --> ' || l_total_amount_due );
1533 psa_utils.debug_other_string(g_state_level,l_full_path,' sum_amt_due_rem --> ' || sum_amt_due_rem );
1534 -- ========================= FND LOG ===========================
1535
1536 --
1537 -- Initailize variables for running total
1538 --
1539
1540 l_running_amount := 0;
1541 l_running_total_amount_due := l_total_amount_due ;
1542
1543 l_running_earned_discount := 0;
1544 l_running_total_amount_earn := l_total_amount_due;
1545
1546 l_running_unearn_discount := 0;
1547 l_running_total_amount_unearn := l_total_amount_due;
1548
1549 --Adi --
1550
1551
1552 -- get the line amount and tax amount seperately
1553 -- for each receivable application ids
1554
1555 SELECT -1*LINE_Applied , -1*TAX_Applied
1556 INTO l_line_amount, l_tax_amount
1557 FROM ar_receivable_applications_all app
1558 WHERE app.receivable_application_id = p_rcv_app_id
1559 AND app.status = 'APP';
1560
1561
1562 -- ========================= FND LOG ===========================
1563 psa_utils.debug_other_string(g_state_level,l_full_path,' l_running_total_amount_due --> ' || l_running_total_amount_due );
1564 psa_utils.debug_other_string(g_state_level,l_full_path,' l_running_total_amount_earn --> ' || l_running_total_amount_earn);
1565 psa_utils.debug_other_string(g_state_level,l_full_path,' l_running_total_amount_unearn --> ' || l_running_total_amount_unearn);
1566 -- ========================= FND LOG ===========================
1567
1568 l_c_accrual_stmt :=
1569 'Select mf_trx_dist.mf_receivables_ccid rcv_ccid, ' ||
1570 ' mf_trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
1574 ' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_original) amount_due, '||
1571 ' trx_line.customer_trx_line_id trx_line_id, ' ||
1572 ' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
1573 -- ' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due, ' ||
1575 ' trx_dist.account_class account_class ' ||
1576 ' From ra_customer_trx_lines trx_line, ' ||
1577 ' ra_cust_trx_line_gl_dist trx_dist, ' ||
1578 ' psa_mf_trx_dist_all mf_trx_dist, ' ||
1579 ' psa_mf_balances_view mf_balances ' ||
1580 ' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
1581 ' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
1582 ' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
1583 ' And trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id ' ||
1584 ' And mf_trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
1585 ' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
1586
1587 l_c_cash_stmt :=
1588 'Select trx_dist.code_combination_id rcv_ccid, ' ||
1589 ' trx_dist.cust_trx_line_gl_dist_id trx_line_dist_id, ' ||
1590 ' trx_line.customer_trx_line_id trx_line_id, ' ||
1591 ' trx_line.link_to_cust_trx_line_id link_trx_line_id, ' ||
1592 -- ' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_remaining) amount_due, '||
1593 ' decode (:sum_adr, 0, mf_balances.amount_due_original, mf_balances.amount_due_original) amount_due, '||
1594 ' trx_dist.account_class account_class' ||
1595 ' From ra_customer_trx_lines trx_line, ' ||
1596 ' ra_cust_trx_line_gl_dist trx_dist, ' ||
1597 ' psa_mf_balances_view mf_balances ' ||
1598 ' Where trx_line.customer_trx_id = :p_cust_trx_id_1 ' ||
1599 ' And mf_balances.customer_trx_id = :p_cust_trx_id_2 ' ||
1600 ' And trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id ' ||
1601 ' And trx_dist.cust_trx_line_gl_dist_id = mf_balances.cust_trx_line_gl_dist_id ' ||
1602 ' And trx_dist.account_class <> '''||'REC'||''' ' ||
1603 ' And trx_line.customer_trx_line_id = nvl(:p_cust_trx_line_id,trx_line.customer_trx_line_id) ';
1604
1605
1606 IF l_total_amount_due < 0 THEN
1607 l_c_accrual_stmt := l_c_accrual_stmt || ' order by 6,5 desc ';
1608 l_c_cash_stmt := l_c_cash_stmt || ' order by 6,5 desc ';
1609
1610 ELSE
1611 l_c_accrual_stmt := l_c_accrual_stmt || ' order by 6,5 asc ';
1612 l_c_cash_stmt := l_c_cash_stmt || ' order by 6,5 asc ';
1613 END IF;
1614
1615 -- ========================= FND LOG ===========================
1616 psa_utils.debug_other_string(g_state_level,l_full_path,' arp_global.sysparam.accounting_method --> '
1617 || arp_global.sysparam.accounting_method);
1618 -- ========================= FND LOG ===========================
1619
1620 IF arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
1621 OPEN c_accrual_cur FOR l_c_accrual_stmt
1622 USING sum_amt_due_rem, p_cust_trx_id, p_cust_trx_id, p_cust_trx_line_id;
1623 ELSIF arp_global.sysparam.accounting_method = 'CASH' THEN
1624 OPEN c_accrual_cur FOR l_c_cash_stmt
1625 USING sum_amt_due_rem, p_cust_trx_id, p_cust_trx_id, p_cust_trx_line_id;
1626 END IF;
1627
1628
1629
1630 -- Get the count of REVENUE lines
1631
1632 SELECT count(1)
1633 INTO l_count
1634 FROM ra_customer_trx_lines trx_line,
1635 ra_cust_trx_line_gl_dist trx_dist,
1636 psa_mf_trx_dist_all mf_trx_dist
1637 WHERE trx_line.customer_trx_id = p_cust_trx_id
1638 AND trx_dist.account_class = 'REV'
1639 AND trx_line.customer_trx_line_id = trx_dist.customer_trx_line_id
1640 AND trx_dist.cust_trx_line_gl_dist_id = mf_trx_dist.cust_trx_line_gl_dist_id
1641 AND trx_line.customer_trx_line_id = nvl(p_cust_trx_line_id,trx_line.customer_trx_line_id);
1642
1643
1644
1645 LOOP
1646 FETCH c_accrual_cur INTO l_accrual_rec;
1647 EXIT WHEN c_accrual_cur%NOTFOUND;
1648
1649 IF l_accrual_rec.ACCOUNT_CLASS = 'REV' THEN
1650 p_amount_applied := l_line_amount;
1651 ELSIF l_accrual_rec.ACCOUNT_CLASS = 'TAX' THEN
1652 p_amount_applied := l_tax_amount;
1653 END IF;
1654
1655 -- ========================= FND LOG ===========================
1656 psa_utils.debug_other_string(g_state_level,l_full_path,
1657 ' l_accrual_rec.trx_line_dist_id --> ' || l_accrual_rec.trx_line_dist_id );
1658 -- ========================= FND LOG ===========================
1659
1660 --
1661 -- If Remittance MFAR distributions have already been created
1662 -- applicable only for Receipts cleared by CashMgt
1663 --
1664
1665 IF p_crh_status = 'CLEARED' AND NOT
1666 CASH_CLR_DIST_EXIST_VALID (p_rcv_app_id, p_amount_applied, 'REMITTED') THEN
1667
1668 OPEN c_remit_reversal_account (l_accrual_rec.trx_line_dist_id);
1672 psa_utils.debug_other_string(g_state_level,l_full_path,
1669 FETCH c_remit_reversal_account INTO l_remit_reversal_ccid;
1670 CLOSE c_remit_reversal_account;
1671 -- ========================= FND LOG ===========================
1673 ' l_remit_reversal_ccid --> ' || l_remit_reversal_ccid);
1674 -- ========================= FND LOG ===========================
1675 END IF;
1676
1677 -- ========================= FND LOG ===========================
1678 psa_utils.debug_other_string(g_state_level,l_full_path,' p_document_type -->
1679 ' || p_document_type );
1680 -- ========================= FND LOG ===========================
1681
1682 p_ccid := l_accrual_rec.rcv_ccid;
1683 -- ========================= FND LOG ===========================
1684 psa_utils.debug_other_string(g_state_level,l_full_path,
1685 ' if cond - document type is CM --> ' || p_ccid);
1686 -- ========================= FND LOG ===========================
1687 --
1688 -- Prorate amount applied
1689 --
1690
1691 IF p_cust_trx_line_id Is NOT NULL THEN
1692 -- ========================= FND LOG ===========================
1693 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_line_id Is NOT NULL');
1694 -- ========================= FND LOG ===========================
1695
1696 IF (p_cust_trx_line_id = l_accrual_rec.trx_line_id)
1697 AND NOT (l_running_total_amount_due = 0) THEN -- to avoid divide by zero error
1698
1699
1700
1701 -- l_amount_applied := p_amount_applied - l_running_amount;
1702 l_amount := ROUND((p_amount_applied*l_accrual_rec.amount_due/l_running_total_amount_due), g_precision);
1703
1704 -- ========================= FND LOG ===========================
1705 psa_utils.debug_other_string(g_state_level,l_full_path,' In IF ' ||
1706 ' ##p_amount_applied --> ' || p_amount_applied ||
1707 ' ##l_amount_applied --> ' || l_amount_applied ||
1708 ' ##l_amount --> ' || l_amount ||
1709 ' ##l_accrual_rec.amount_due --> ' || l_accrual_rec.amount_due ||
1710 ' ##l_running_total_amount_due --> ' || l_running_total_amount_due);
1711 -- ========================= FND LOG ===========================
1712 IF NVL(p_amount_applied,0) <> 0 THEN -- Bug3884271
1713 l_percent := ROUND((l_amount/p_amount_applied*100), 4);
1714
1715
1716 ELSE
1717 l_percent := 0;
1718 END IF;
1719
1720 l_running_amount := l_running_amount + l_amount;
1721 -- l_running_total_amount_due := l_running_total_amount_due - l_accrual_rec.amount_due;
1722
1723 -- ========================= FND LOG ===========================
1724 psa_utils.debug_other_string(g_state_level,l_full_path,' IF part ' ||
1725 ' ##l_amount_applied --> ' || l_amount_applied ||
1726 ' ##l_amount --> ' || l_amount ||
1727 ' ##l_percent --> ' || l_percent ||
1728 ' ##l_running_amount --> ' || l_running_amount ||
1729 ' ##l_running_total_amount_due --> ' || l_running_total_amount_due);
1730 -- ========================= FND LOG ===========================
1731
1732 ELSE
1733
1734 l_amount := 0;
1735 l_percent := 0;
1736 -- ========================= FND LOG ===========================
1737 psa_utils.debug_other_string(g_state_level,l_full_path,' ##l_amount --> ' || l_amount || ' ##l_percent --> ' || l_percent);
1738 -- ========================= FND LOG ===========================
1739 END IF;
1740 ELSE
1741 -- ========================= FND LOG ===========================
1742 psa_utils.debug_other_string(g_state_level,l_full_path,' p_cust_trx_line_id Is NULL');
1743 -- ========================= FND LOG ===========================
1744
1745 IF NOT (l_running_total_amount_due = 0) THEN -- to avoid divide by zero error
1746
1747 -- l_amount_applied := p_amount_applied - l_running_amount;
1748 l_amount := ROUND((p_amount_applied*l_accrual_rec.amount_due/l_running_total_amount_due), g_precision);
1749
1750 IF NVL(p_amount_applied,0) <> 0 THEN -- Bug3884271
1751 l_percent := ROUND((l_amount/p_amount_applied*100), 4);
1752 ELSE
1753 l_percent := 0;
1754 END IF;
1755
1756 l_running_amount := l_running_amount + l_amount;
1757 -- l_running_total_amount_due := l_running_total_amount_due - l_accrual_rec.amount_due;
1758
1759 -- ========================= FND LOG ===========================
1760 psa_utils.debug_other_string(g_state_level,l_full_path,' ELSE part ' ||
1761 ' ##l_amount_applied --> ' || l_amount_applied ||
1762 ' ##l_amount --> ' || l_amount ||
1763 ' ##l_percent --> ' || l_percent ||
1767 END IF;
1764 ' ##l_running_amount --> ' || l_running_amount ||
1765 ' ##l_running_total_amount_due --> ' || l_running_total_amount_due);
1766 -- ========================= FND LOG ===========================
1768 END IF;
1769
1770 --
1771 -- Prorate earned/unearned discount
1772 --
1773
1774 IF (p_earned_discount <> 0 OR p_unearned_discount <> 0) THEN
1775
1776 IF LINE_IN_DISCOUNT_CACHE (l_accrual_rec.trx_line_id) THEN
1777
1778 -- ========================= FND LOG ===========================
1779 psa_utils.debug_other_string(g_state_level,l_full_path,' calling LINE_IN_DISCOUNT_CACHE --> ' || l_accrual_rec.trx_line_id);
1780 psa_utils.debug_other_string(g_state_level,l_full_path,' prorate earned discount ');
1781 -- ========================= FND LOG ===========================
1782
1783 --
1784 -- Prorate Earned Discount
1785 --
1786
1787 IF (p_earned_discount <> 0)
1788 AND NOT (l_running_total_amount_earn = 0) THEN -- to avoid divide by zero error
1789
1790 -- l_earn_discount_applied := p_earned_discount - l_running_earned_discount;
1791 -- l_earned_discount := ROUND((l_earn_discount_applied*l_accrual_rec.amount_due/l_running_total_amount_earn),4);
1792 l_earned_discount := ROUND((p_earned_discount*l_accrual_rec.amount_due/l_running_total_amount_earn),g_precision);
1793 -- l_running_earned_discount := l_running_earned_discount + l_earned_discount;
1794 -- l_running_total_amount_earn := l_running_total_amount_earn - l_accrual_rec.amount_due;
1795
1796 -- ========================= FND LOG ===========================
1797 psa_utils.debug_other_string(g_state_level,l_full_path,' IF part ' ||
1798 ' ##l_earn_discount_applied --> ' || l_earn_discount_applied ||
1799 ' ##l_earned_discount --> ' || l_earned_discount ||
1800 ' ##l_running_earned_discount --> ' || l_running_earned_discount ||
1801 ' ##l_running_total_amount_earn --> ' || l_running_total_amount_earn);
1802 -- ========================= FND LOG ===========================
1803
1804 ELSE
1805 l_earned_discount := 0;
1806 -- ========================= FND LOG ===========================
1807 psa_utils.debug_other_string(g_state_level,l_full_path,
1808 ' ELSE part ##l_earned_discount --> ' || l_earned_discount);
1809 -- ========================= FND LOG ===========================
1810 END IF;
1811
1812 --
1813 -- Prorate Unearned Discount
1814 --
1815
1816 -- ========================= FND LOG ===========================
1817 psa_utils.debug_other_string(g_state_level,l_full_path,' prorate unearned discount ');
1818 -- ========================= FND LOG ===========================
1819
1820 IF p_unearned_discount <> 0
1821 AND NOT (l_running_total_amount_unearn = 0) THEN -- to avoid divide by zero error
1822
1823 -- l_unearn_discount_applied := p_unearned_discount - l_running_unearn_discount;
1824 -- l_unearned_discount := ROUND((l_unearn_discount_applied*l_accrual_rec.amount_due/l_running_total_amount_unearn),2);
1825 l_unearned_discount := ROUND((p_unearned_discount*l_accrual_rec.amount_due/l_running_total_amount_unearn),g_precision);
1826 -- l_running_unearn_discount := l_running_unearn_discount + l_unearned_discount;
1827 -- l_running_total_amount_unearn := l_running_total_amount_unearn - l_accrual_rec.amount_due;
1828
1829 -- ========================= FND LOG ===========================
1830 psa_utils.debug_other_string(g_state_level,l_full_path,' IF part ' ||
1831 ' ##l_earn_discount_applied --> ' || l_earn_discount_applied ||
1832 ' ##l_earned_discount --> ' || l_earned_discount ||
1833 ' ##l_running_earned_discount --> ' || l_running_earned_discount ||
1834 ' ##l_running_total_amount_earn --> ' || l_running_total_amount_earn);
1835 -- ========================= FND LOG ===========================
1836 ELSE
1837 l_unearned_discount := 0;
1838 -- ========================= FND LOG ===========================
1839 psa_utils.debug_other_string(g_state_level,l_full_path,' ELSE part ##l_unearned_discount --> ' || l_unearned_discount);
1840 -- ========================= FND LOG ===========================
1841
1842 END IF;
1843
1844 IF p_earned_discount_ccid IS NOT NULL THEN
1845 -- ========================= FND LOG ===========================
1846 psa_utils.debug_other_string(g_state_level,l_full_path,
1847 ' calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS for earned discount');
1848 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount_ccid IS NOT NULL ');
1849 psa_utils.debug_other_string(g_state_level,l_full_path,
1850 ' ##p_unearned_discount_ccid --> ' || p_unearned_discount_ccid ||
1854 -- ========================= FND LOG ===========================
1851 ' ##rcv_ccid --> ' || l_accrual_rec.rcv_ccid ||
1852 ' ##g_set_of_books_id --> ' || g_set_of_books_id ||
1853 ' ##p_mf_earned_discount_ccid --> ' || p_mf_earned_discount_ccid);
1855
1856 IF NOT ( PSA_MFAR_UTILS.OVERRIDE_SEGMENTS (p_earned_discount_ccid,
1857 l_accrual_rec.rcv_ccid,
1858 g_set_of_books_id,'RCT',
1859 p_mf_earned_discount_ccid) ) THEN
1860
1861 -- ========================= FND LOG ===========================
1862 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -> FALSE');
1863 -- ========================= FND LOG ===========================
1864 RAISE FLEX_BUILD_ERROR;
1865 END IF;
1866 ELSE
1867 -- ========================= FND LOG ===========================
1868 psa_utils.debug_other_string(g_state_level,l_full_path,' p_earned_discount_ccid IS NULL ');
1869 -- ========================= FND LOG ===========================
1870 END IF;
1871
1872 IF p_unearned_discount_ccid IS NOT NULL THEN
1873 -- ========================= FND LOG ===========================
1874 psa_utils.debug_other_string(g_state_level,l_full_path,
1875 ' calling PSA_MFAR_UTILS.OVERRIDE_SEGMENTS for unearned discount');
1876 psa_utils.debug_other_string(g_state_level,l_full_path,
1877 ' p_earned_discount_ccid IS NOT NULL ');
1878 psa_utils.debug_other_string(g_state_level,l_full_path,
1879 ' ##p_unearned_discount_ccid --> ' || p_unearned_discount_ccid ||
1880 ' ##rcv_ccid --> ' || l_accrual_rec.rcv_ccid ||
1881 ' ##g_set_of_books_id --> ' || g_set_of_books_id ||
1882 ' ##p_mf_unearned_discount_ccid --> ' || p_mf_unearned_discount_ccid);
1883 -- ========================= FND LOG ===========================
1884
1885 IF NOT ( PSA_MFAR_UTILS.OVERRIDE_SEGMENTS ( P_PRIMARY_CCID => p_unearned_discount_ccid,
1886 P_OVERRIDE_CCID => l_accrual_rec.rcv_ccid,
1887 P_SET_OF_BOOKS_ID => g_set_of_books_id,
1888 P_TRX_TYPE => 'RCT',
1889 P_CCID => p_mf_unearned_discount_ccid)) -- OUT
1890 THEN
1891 -- ========================= FND LOG ===========================
1892 psa_utils.debug_other_string(g_state_level,l_full_path,' PSA_MFAR_UTILS.OVERRIDE_SEGMENTS -> FALSE ');
1893 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising flex_build_error ');
1894 -- ========================= FND LOG ===========================
1895 RAISE FLEX_BUILD_ERROR;
1896 ELSE
1897 -- ========================= FND LOG ===========================
1898 psa_utils.debug_other_string(g_state_level,l_full_path,
1899 ' p_mf_unearned_discount_ccid -> ' || p_mf_unearned_discount_ccid);
1900 -- ========================= FND LOG ===========================
1901 END IF;
1902 END IF;
1903 END IF;
1904 END IF;
1905
1906 -- ========================= FND LOG ===========================
1907 psa_utils.debug_other_string(g_state_level,l_full_path,' calling PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW');
1908 -- ========================= FND LOG ===========================
1909
1910 --
1911 -- Insert into psa_mf_rct_dist_all
1912 --
1913
1914 IF l_count = 1 THEN
1915
1916 l_amount := -1*(-1*p_amount_applied - (-1*l_running_amount - (-1*l_amount)));
1917
1918 -- ========================= FND LOG ===========================
1919 psa_utils.debug_other_string(g_state_level,l_full_path,
1920 ' l_amount -> ' ||l_amount);
1921 -- ========================= FND LOG ===========================
1922 END IF;
1923
1924 IF nvl(l_amount, 0) <> 0 THEN
1925 PSA_MFAR_RECEIPTS_COVER_PKG.INSERT_ROW
1926 (
1927 x_rowid => l_rowid,
1928 x_receivable_application_id => p_rcv_app_id,
1929 x_cust_trx_line_gl_dist_id => l_accrual_rec.trx_line_dist_id,
1930 x_attribute_category => NULL,
1931 x_mf_cash_ccid => p_ccid,
1932 x_amount => nvl(l_amount, 0),
1933 x_percent => nvl(l_percent,0),
1934 x_discount_ccid => p_mf_earned_discount_ccid,
1935 x_ue_discount_ccid => p_mf_unearned_discount_ccid,
1936 x_discount_amount => nvl(l_earned_discount,0),
1937 x_ue_discount_amount => nvl(l_unearned_discount,0),
1938 x_comments => NULL,
1939 x_posting_control_id => NULL,
1940 x_attribute1 => NULL,
1944 x_attribute5 => NULL,
1941 x_attribute2 => NULL,
1942 x_attribute3 => NULL,
1943 x_attribute4 => NULL,
1945 x_attribute6 => NULL,
1946 x_attribute7 => NULL,
1947 x_attribute8 => NULL,
1948 x_attribute9 => NULL,
1949 x_attribute10 => NULL,
1950 x_attribute11 => NULL,
1951 x_attribute12 => NULL,
1952 x_attribute13 => NULL,
1953 x_attribute14 => NULL,
1954 x_attribute15 => NULL,
1955 X_REFERENCE4 => NULL,
1956 X_REFERENCE5 => NULL,
1957 X_REFERENCE2 => NULL,
1958 X_REFERENCE1 => p_crh_status,
1959 X_REFERENCE3 => NULL,
1960 X_REVERSAL_CCID => l_remit_reversal_ccid,
1961 x_mode => 'R' );
1962
1963 IF l_count > 0 THEN
1964 l_count := l_count - 1;
1965 END IF;
1966 END IF;
1967
1968 END LOOP;
1969 CLOSE c_accrual_cur;
1970
1971 -- ========================= FND LOG ===========================
1972 psa_utils.debug_other_string(g_state_level,l_full_path,' retcode --> ' || retcode );
1973 psa_utils.debug_other_string(g_state_level,l_full_path,' RETURN TRUE ');
1974 -- ========================= FND LOG ===========================
1975
1976 retcode := 'S';
1977 RETURN TRUE;
1978
1979
1980 EXCEPTION
1981 -- Bug 3672756
1982 WHEN INVALID_DISTRIBUTION THEN
1983 -- ========================= FND LOG ===========================
1984 psa_utils.debug_other_string(g_excep_level,l_full_path,
1985 ' --> EXCEPTION - INVALID_DISTRIBUTION raised during PSA_MFAR_RECEIPTS.generate_rct_dist ');
1986 psa_utils.debug_other_string(g_excep_level,l_full_path,
1987 ' --> p_error_message --> ' || l_exception_message);
1988 -- ========================= FND LOG ===========================
1989 p_error_message := l_exception_message;
1990 retcode := 'F';
1991 RETURN FALSE;
1992
1993 WHEN FLEX_BUILD_ERROR THEN
1994 l_exception_message := fnd_message.get;
1995 -- ========================= FND LOG ===========================
1996 psa_utils.debug_other_string(g_excep_level,l_full_path,
1997 ' --> EXCEPTION - FLEX_BUILD_ERROR raised during PSA_MFAR_RECEIPTS.generate_rct_dist ');
1998 psa_utils.debug_other_string(g_excep_level,l_full_path,
1999 ' --> p_error_message --> ' || l_exception_message);
2000 -- ========================= FND LOG ===========================
2001 p_error_message := l_exception_message;
2002 retcode := 'F';
2003 RETURN FALSE;
2004
2005 WHEN OTHERS THEN
2006 l_exception_message := l_exception_message || SQLCODE || ' - ' || SQLERRM;
2007 -- ========================= FND LOG ===========================
2008 psa_utils.debug_other_string(g_excep_level,l_full_path,
2009 ' --> EXCEPTION - OTHERS raised during PSA_MFAR_RECEIPTS.generate_rct_dist ');
2010 psa_utils.debug_other_string(g_excep_level,l_full_path,
2011 ' --> p_error_message --> ' || l_exception_message);
2012 psa_utils.debug_unexpected_msg(l_full_path);
2013 -- ========================= FND LOG ===========================
2014 p_error_message := l_exception_message;
2015 retcode := 'F';
2016 RETURN FALSE;
2017
2018 END generate_rct_dist_cm;
2019
2020
2021 /**************************** PURGE_ORPHAN_DISTRIBUTIONS *******************************************/
2022
2023 PROCEDURE purge_orphan_distributions
2024 IS
2025
2026 CURSOR c_invalid_distributions
2027 IS
2028 SELECT distinct app.receivable_application_id rcv_app_id
2029 FROM ar_receivable_applications app,
2030 psa_mf_rct_dist_all mf_dist
2031 WHERE app.receivable_application_id = mf_dist.receivable_application_id
2032 AND (NOT(app.status = 'APP'));
2033 -- Commented out by RM to fix 1604281
2034 -- OR not(app.display = 'Y')
2035
2036
2037 l_invalid_distributions_rec c_invalid_distributions%rowtype;
2038 -- ========================= FND LOG ===========================
2039 l_full_path VARCHAR2(100) := g_path || 'purge_orphan_distributions';
2040 -- ========================= FND LOG ===========================
2041
2042 BEGIN
2043
2044 -- ========================= FND LOG ===========================
2045 psa_utils.debug_other_string(g_state_level,l_full_path,' Purge_orphan_distributions : --> START');
2046 -- ========================= FND LOG ===========================
2047
2048 OPEN c_invalid_distributions;
2049 LOOP
2050 FETCH c_invalid_distributions INTO l_invalid_distributions_rec;
2051 EXIT WHEN c_invalid_distributions%NOTFOUND;
2052
2053 -- ========================= FND LOG ===========================
2054 psa_utils.debug_other_string(g_state_level,l_full_path,' Purge_orphan_distributions : --> deleting ' || l_invalid_distributions_rec.rcv_app_id);
2055 -- ========================= FND LOG ===========================
2056
2060 -- ========================= FND LOG ===========================
2057 DELETE FROM psa_mf_rct_dist_all
2058 WHERE receivable_application_id = l_invalid_distributions_rec.rcv_app_id;
2059
2061 psa_utils.debug_other_string(g_state_level,l_full_path,' Purge_orphan_distributions : --> rows ' || SQL%ROWCOUNT);
2062 -- ========================= FND LOG ===========================
2063
2064 END LOOP;
2065 CLOSE c_invalid_distributions;
2066
2067 -- ========================= FND LOG ===========================
2068 psa_utils.debug_other_string(g_state_level,l_full_path,' Purge_orphan_distributions : --> END');
2069 -- ========================= FND LOG ===========================
2070
2071 EXCEPTION
2072 WHEN OTHERS THEN
2073 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.PURGE_ORPHAN_DISTRIBUTIONS: '||sqlerrm;
2074 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2075 l_exception_message);
2076 -- ========================= FND LOG ===========================
2077 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2078 psa_utils.debug_unexpected_msg(l_full_path);
2079 -- ========================= FND LOG ===========================
2080
2081 END purge_orphan_distributions;
2082
2083 /**************************** POPULATE_DISCOUNT_LINES_CACHE *******************************************/
2084
2085 PROCEDURE populate_discount_lines_cache (p_customer_trx_id IN NUMBER)
2086 IS
2087
2088 CURSOR c_variables
2089 IS
2090 SELECT terms.calc_discount_on_lines_flag discount_basis,
2091 trx.created_from created_from
2092 FROM ra_customer_trx trx,
2093 ra_terms_b terms
2094 WHERE trx.customer_trx_id = p_customer_trx_id
2095 AND trx.term_id = terms.term_id;
2096
2097 l_variables_rec c_variables%rowtype;
2098 -- ========================= FND LOG ===========================
2099 l_full_path VARCHAR2(100) := g_path || 'populate_discount_lines_cache';
2100 -- ========================= FND LOG ===========================
2101
2102 BEGIN
2103
2104
2105 -- ========================= FND LOG ===========================
2106 psa_utils.debug_other_string(g_state_level,l_full_path,' Populate_discount_lines_cache : --> START');
2107 -- ========================= FND LOG ===========================
2108
2109 OPEN c_variables;
2110 FETCH c_variables INTO l_variables_rec;
2111 CLOSE c_variables;
2112
2113 -- ========================= FND LOG ===========================
2114 psa_utils.debug_other_string(g_state_level,l_full_path,' Populate_discount_lines_cache : Created form --> ' || l_variables_rec.created_from);
2115 -- ========================= FND LOG ===========================
2116
2117 --
2118 -- For a transaction created manually, "discount basis" is taken into
2119 -- account while prorating the discount amount among the distributions
2120 --
2121 -- Discount Basis: 'I' - Invoice Amount
2122 -- 'L' - Lines Only
2123 -- 'T' - Lines, Freight Items and Tax
2124 -- 'F' - Lines and Tax, not freight items and tax
2125 --
2126 -- For an imported transaction,
2127 -- a. "discount basis"
2128 -- b. Order Entry (OE) profile "TAX: Inventory Item for Freight"
2129 -- Example:
2130 -- If "TAX: Inventory Item for Freight" is not defined or null
2131 -- The freight item line created by autoinvoice is not included while
2132 -- prorating the discount amount.
2133 --
2134
2135 IF l_variables_rec.created_from = 'RAXTRX' THEN -- IMPORTED THRU' AUTOINVOICE
2136 IMPORTED_TRANSACTION (p_customer_trx_id, l_variables_rec.discount_basis);
2137 -- ========================= FND LOG ===========================
2138 psa_utils.debug_other_string(g_state_level,l_full_path,' Populate_discount_lines_cache : imported trans --> ##p_customer_trx_id --> ' || p_customer_trx_id || ' ##discount_basis --> ' || l_variables_rec.discount_basis);
2139 -- ========================= FND LOG ===========================
2140
2141 ELSE
2142 MANUAL_TRANSACTION (p_customer_trx_id, l_variables_rec.discount_basis);
2143 -- ========================= FND LOG ===========================
2144 psa_utils.debug_other_string(g_state_level,l_full_path,' Populate_discount_lines_cache : manual trans --> ##p_customer_trx_id --> ' || p_customer_trx_id || ' ##discount_basis --> ' || l_variables_rec.discount_basis);
2145 -- ========================= FND LOG ===========================
2146 END IF;
2147
2148 EXCEPTION
2149 WHEN OTHERS THEN
2150 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.POPULATE_DISCOUNT_LINES_CACHE: '||sqlerrm;
2151 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2152 l_exception_message);
2153 -- ========================= FND LOG ===========================
2154 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2155 psa_utils.debug_unexpected_msg(l_full_path);
2156 -- ========================= FND LOG ===========================
2157
2158 END populate_discount_lines_cache;
2159
2160 /**************************** MANUAL_TRANSACTION *******************************************/
2161
2162 PROCEDURE manual_transaction ( p_customer_trx_id IN NUMBER,
2163 discount_basis IN VARCHAR2 ) IS
2164
2165 CURSOR c_manual_trx
2166 IS
2167 SELECT customer_trx_line_id, link_to_cust_trx_line_id, line_type
2168 FROM ra_customer_trx_lines
2169 WHERE customer_trx_id = p_customer_trx_id
2170 AND include_manual_line (discount_basis, link_to_cust_trx_line_id, line_type) = 'Y';
2171
2172 l_manual_trx_rec c_manual_trx%ROWTYPE;
2173 l_index NUMBER := 1;
2177
2174 -- ========================= FND LOG ===========================
2175 l_full_path VARCHAR2(100) := g_path || 'manual_transaction';
2176 -- ========================= FND LOG ===========================
2178 BEGIN
2179
2180 -- ========================= FND LOG ===========================
2181 psa_utils.debug_other_string(g_state_level,l_full_path,' Manual_transaction : START ');
2182 psa_utils.debug_other_string(g_state_level,l_full_path,' Manual_transaction : calling RESET_DISCOUNT_CACHE ');
2183 -- ========================= FND LOG ===========================
2184
2185 RESET_DISCOUNT_CACHE;
2186
2187 OPEN c_manual_trx;
2188 LOOP
2189
2190 FETCH c_manual_trx INTO l_manual_trx_rec;
2191 EXIT WHEN c_manual_trx%NOTFOUND;
2192
2193 -- ========================= FND LOG ===========================
2194 psa_utils.debug_other_string(g_state_level,l_full_path,' Manual_transaction : customer_trx_line_id --> ' || l_manual_trx_rec.customer_trx_line_id);
2195 -- ========================= FND LOG ===========================
2196
2197 TrxLinesTab(l_index) := l_manual_trx_rec.customer_trx_line_id;
2198 l_index := l_index + 1;
2199
2200 END LOOP;
2201 CLOSE c_manual_trx;
2202
2203 -- ========================= FND LOG ===========================
2204 psa_utils.debug_other_string(g_state_level,l_full_path,' Manual_transaction : END ');
2205 -- ========================= FND LOG ===========================
2206
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.MANUAL_TRANSACTION: '||sqlerrm;
2210 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2211 l_exception_message);
2212 -- ========================= FND LOG ===========================
2213 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2214 psa_utils.debug_unexpected_msg(l_full_path);
2215 -- ========================= FND LOG ===========================
2216 END manual_transaction;
2217
2218
2219 /**************************** INCLUDE_MANUAL_LINE *******************************************/
2220
2221 FUNCTION include_manual_line ( p_discount_basis IN VARCHAR2,
2222 p_link_to_cust_trx_line_id IN NUMBER,
2223 p_line_type IN VARCHAR2 ) RETURN VARCHAR2
2224 IS
2225 CURSOR c_tax_line
2226 IS
2227 SELECT line_type
2228 FROM ra_customer_trx_lines
2229 WHERE customer_trx_line_id = p_link_to_cust_trx_line_id;
2230
2231 l_tax_line c_tax_line%rowtype;
2232 -- ========================= FND LOG ===========================
2233 l_full_path VARCHAR2(100) := g_path || 'include_manual_line';
2234 -- ========================= FND LOG ===========================
2235
2236 BEGIN
2237
2238 -- ========================= FND LOG ===========================
2239 psa_utils.debug_other_string(g_state_level,l_full_path,' Include_manual_line : START');
2240 -- ========================= FND LOG ===========================
2241
2242 IF p_discount_basis = 'I' THEN
2243 RETURN 'Y';
2244
2245 ELSIF p_discount_basis = 'L' THEN
2246 IF p_line_type = 'LINE' THEN
2247 RETURN 'Y';
2248 END IF;
2249
2250 ELSIF p_discount_basis = 'T' THEN
2251 IF p_line_type In ( 'LINE', 'TAX', 'FREIGHT' ) THEN
2252 RETURN 'Y';
2253 END IF;
2254
2255 ELSIF p_discount_basis = 'F' THEN
2256 IF p_line_type = 'LINE' THEN
2257 RETURN 'Y';
2258 END IF;
2259
2260 ELSIF p_line_type = 'TAX' THEN
2261
2262 OPEN c_tax_line;
2263 FETCH c_tax_line INTO l_tax_line;
2264 CLOSE c_tax_line;
2265
2266 IF l_tax_line.line_type = 'LINE' THEN
2267 RETURN 'Y';
2268 END IF;
2269 END IF;
2270
2271 RETURN 'Y';
2272
2273 EXCEPTION
2274 WHEN OTHERS THEN
2275 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.INCLUDE_MANUAL_LINE: '|| sqlerrm;
2276 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2277 l_exception_message);
2278 -- ========================= FND LOG ===========================
2279 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2280 psa_utils.debug_unexpected_msg(l_full_path);
2281 -- ========================= FND LOG ===========================
2282
2283 END include_manual_line;
2284
2285 /**************************** IMPORTED_TRANSACTION *******************************************/
2286
2287 PROCEDURE imported_transaction ( p_customer_trx_id IN NUMBER,
2288 p_discount_basis IN VARCHAR2 )
2289 IS
2290
2291 CURSOR c_imported_trx
2292 IS
2293 SELECT line_type, customer_trx_line_id, link_to_cust_trx_line_id, inventory_item_id
2294 FROM ra_customer_trx_lines
2295 WHERE customer_trx_id = p_customer_trx_id
2296 AND include_imported_line
2297 (p_discount_basis, link_to_cust_trx_line_id, line_type, inventory_item_id) = 'Y';
2298
2299 l_inventory_item_profile NUMBER;
2300 l_imported_trx_rec c_imported_trx%ROWTYPE;
2301 l_index NUMBER := 1;
2302 -- ========================= FND LOG ===========================
2303 l_full_path VARCHAR2(100) := g_path || 'imported_transaction';
2304 -- ========================= FND LOG ===========================
2305
2306 BEGIN
2307
2308 -- ========================= FND LOG ===========================
2312 --
2309 psa_utils.debug_other_string(g_state_level,l_full_path,' Imported_transaction : START');
2310 -- ========================= FND LOG ===========================
2311
2313 -- Profile Option - TAX: Inventory Item for Freight
2314 --
2315
2316 OE_PROFILE.GET ('SO_INVENTORY_ITEM_FOR_FREIGHT', g_inventory_item_profile);
2317
2318 -- ========================= FND LOG ===========================
2319 psa_utils.debug_other_string(g_state_level,l_full_path,' Imported_transaction : calling RESET_DISCOUNT_CACHE');
2320 -- ========================= FND LOG ===========================
2321
2322 RESET_DISCOUNT_CACHE;
2323
2324 OPEN c_imported_trx;
2325 LOOP
2326 FETCH c_imported_trx INTO l_imported_trx_rec;
2327 EXIT WHEN c_imported_trx%NOTFOUND;
2328
2329 -- ========================= FND LOG ===========================
2330 psa_utils.debug_other_string(g_state_level,l_full_path,' Manual_transaction : customer_trx_line_id --> ' || l_imported_trx_rec.customer_trx_line_id);
2331 -- ========================= FND LOG ===========================
2332
2333 TrxLinesTab(l_index) := l_imported_trx_rec.customer_trx_line_id;
2334 END LOOP;
2335 CLOSE c_imported_trx;
2336
2337 EXCEPTION
2338 WHEN OTHERS THEN
2339 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.IMPORTED_TRANSACTION: '|| sqlerrm;
2340 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2341 l_exception_message);
2342 -- ========================= FND LOG ===========================
2343 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2344 psa_utils.debug_unexpected_msg(l_full_path);
2345 -- ========================= FND LOG ===========================
2346
2347 END imported_transaction;
2348
2349 /**************************** INCLUDE_IMPORTED_TRANSACTION *******************************************/
2350
2351
2352 FUNCTION include_imported_line ( p_discount_basis IN VARCHAR2,
2353 p_link_to_cust_trx_line_id IN NUMBER,
2354 p_line_type IN NUMBER,
2355 p_inventory_item_id IN NUMBER )
2356 RETURN VARCHAR2 IS
2357
2358 CURSOR c_inventory_item IS
2359 Select inventory_item_id
2360 From ra_customer_trx_lines
2361 Where customer_trx_line_id = p_link_to_cust_trx_line_id;
2362
2363 CURSOR c_tax_line IS
2364 Select line_type
2365 From ra_customer_trx_lines
2366 Where customer_trx_line_id = p_link_to_cust_trx_line_id;
2367
2368 l_inventory_item_rec c_inventory_item%rowtype;
2369 l_tax_line_rec c_tax_line%rowtype;
2370 -- ========================= FND LOG ===========================
2371 l_full_path VARCHAR2(100) := g_path || 'include_imported_line';
2372 -- ========================= FND LOG ===========================
2373
2374 BEGIN
2375
2376 -- ========================= FND LOG ===========================
2377 psa_utils.debug_other_string(g_state_level,l_full_path,' Include_imported_transaction : START');
2378 -- ========================= FND LOG ===========================
2379
2380 IF p_discount_basis = 'F' THEN
2381 IF g_inventory_item_profile IS NOT NULL THEN
2382 IF p_line_type = 'LINE' THEN
2383 IF NOT ( nvl(p_inventory_item_id, -1) = g_inventory_item_profile) THEN
2384 RETURN 'Y';
2385 END IF;
2386 ELSIF p_line_type = 'TAX' THEN
2387
2388 OPEN c_inventory_item;
2389 FETCH c_inventory_item INTO l_inventory_item_rec;
2390 CLOSE c_inventory_item;
2391
2392 IF NOT ( nvl(l_inventory_item_rec.inventory_item_id, -1) = g_inventory_item_profile) THEN
2393 RETURN 'Y';
2394 END IF;
2395 END IF;
2396 ELSE
2397 --
2398 -- inventory item id = null, discount basis = 'F' is not a correct combination
2399 -- treat as if discount basis = 'T'
2400 --
2401
2402 IF p_line_type = 'LINE' THEN
2403 RETURN 'Y';
2404 ELSIF p_line_type = 'TAX' THEN
2405
2406 OPEN c_tax_line;
2407 FETCH c_tax_line INTO l_tax_line_rec;
2408 CLOSE c_tax_line;
2409
2410 IF l_tax_line_rec.line_type = 'LINE' THEN
2411 RETURN 'Y';
2412 END IF;
2413 END IF;
2414 END IF;
2415
2416 ELSIF p_discount_basis = 'T' THEN
2417 IF p_line_type In ( 'LINE', 'TAX', 'FREIGHT' ) THEN
2418 RETURN 'Y';
2419 END IF;
2420
2421 ELSIF p_discount_basis = 'L' THEN
2422 IF p_line_type = 'LINE' THEN
2423 RETURN 'Y';
2424 END IF;
2425
2426 ELSIF p_discount_basis = 'I' THEN
2427 RETURN 'Y';
2428 END IF;
2429
2430 RETURN 'Y';
2431
2432 EXCEPTION
2433 WHEN OTHERS THEN
2434 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.INCLUDE_IMPORTED_LINE: '|| sqlerrm;
2435 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2436 l_exception_message);
2437 -- ========================= FND LOG ===========================
2438 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2439 psa_utils.debug_unexpected_msg(l_full_path);
2440 -- ========================= FND LOG ===========================
2441
2442 END include_imported_line;
2443
2444 /******************************* LINE_IN_DISCOUNT_CACHE **************************/
2445
2446 FUNCTION line_in_discount_cache (p_customer_trx_line_id IN NUMBER) RETURN BOOLEAN IS
2447 -- ========================= FND LOG ===========================
2448 l_full_path VARCHAR2(100) := g_path || 'line_in_discount_cache';
2449 -- ========================= FND LOG ===========================
2450 BEGIN
2451 -- ========================= FND LOG ===========================
2452 psa_utils.debug_other_string(g_state_level,l_full_path,' Line_in_discount_cache : START');
2453 -- ========================= FND LOG ===========================
2454 FOR i IN 1..TrxLinesTab.COUNT LOOP
2455 IF TrxLinesTab(i) = p_customer_trx_line_id THEN
2456 RETURN TRUE;
2457 END IF;
2458 END LOOP;
2459
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.LINE_IN_DISCOUNT_CACHE: '|| sqlerrm;
2463 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2464 l_exception_message);
2465 -- ========================= FND LOG ===========================
2466 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2467 psa_utils.debug_unexpected_msg(l_full_path);
2468 -- ========================= FND LOG ===========================
2469
2470 END line_in_discount_cache;
2471
2472 /******************************* RESET_DISCOUNT_CACHE **************************/
2473
2474 PROCEDURE reset_discount_cache IS
2475 -- ========================= FND LOG ===========================
2476 l_full_path VARCHAR2(100) := g_path || 'reset_discount_cache';
2477 -- ========================= FND LOG ===========================
2478 BEGIN
2479 -- ========================= FND LOG ===========================
2480 psa_utils.debug_other_string(g_state_level,l_full_path,' Reset_discount_cache : START');
2481 -- ========================= FND LOG ===========================
2482
2483 FOR i IN 1..TrxLinesTab.COUNT LOOP
2484 TrxLinesTab.DELETE(i);
2485 END LOOP;
2486
2487 EXCEPTION
2488 WHEN OTHERS THEN
2489 l_exception_message := 'EXCEPTION - OTHERS PACKAGE - PSA_MFAR_RECEIPTS.RESET_DISCOUNT_CACHE: '|| sqlerrm;
2490 PSA_MFAR_UTILS.INSERT_DISTRIBUTIONS_LOG (g_run_id, 'RECEIPT', g_cust_trx_id, g_receivable_application_id,
2491 l_exception_message);
2492 -- ========================= FND LOG ===========================
2493 psa_utils.debug_other_string(g_excep_level,l_full_path,l_exception_message);
2494 psa_utils.debug_unexpected_msg(l_full_path);
2495 -- ========================= FND LOG ===========================
2496
2497 END reset_discount_cache;
2498
2499
2500 END psa_mfar_receipts;