[Home] [Help]
PACKAGE BODY: APPS.PSA_XFR_TO_GL_PKG
Source
1 PACKAGE BODY PSA_XFR_TO_GL_PKG AS
2 /* $Header: PSAMFG2B.pls 120.3 2006/09/13 12:49:36 agovil noship $ */
3
4 /* ################################## GLOBAL VARIABLES DECLARE START ################################## */
5
6 l_summary_flag VARCHAR2(1);
7 l_batch_prefix VARCHAR2(100); /* REFERENCE1 */
8 l_func_curr VARCHAR2(40);
9
10 l_trade_cat_name VARCHAR2(25);
11 l_ccurr_cat_name VARCHAR2(25);
12 l_user_cm_cat_name VARCHAR2(25);
13 l_misc_cat_name VARCHAR2(25); /* CATEGORY */
14 l_adj_cat_name VARCHAR2(25);
15 l_cm_cat_name VARCHAR2(25);
16 l_dm_cat_name VARCHAR2(25);
17 l_cb_cat_name VARCHAR2(25);
18 l_inv_cat_name VARCHAR2(25);
19
20 l_class_cb VARCHAR2(2000);
21 l_class_cm VARCHAR2(2000);
22 l_class_dep VARCHAR2(2000);
23 l_class_dm VARCHAR2(2000); /* CLASS */
24 l_class_guar VARCHAR2(2000);
25 l_class_inv VARCHAR2(2000);
26 l_class_br VARCHAR2(2000);
27
28 l_pre_tradeapp VARCHAR2(2000);
29 l_app_onacc VARCHAR2(2000);
30 l_app_unapp VARCHAR2(2000);
31 l_app_unid VARCHAR2(2000);
32 l_app_applied VARCHAR2(2000);
33
34 l_pre_erdisc VARCHAR2(2000);
35 l_pre_rec_erdisc_nrtax VARCHAR2(2000);
36 l_pre_undisc VARCHAR2(2000);
37 l_pre_rec_undisc_nrtax VARCHAR2(2000);
38 l_pre_rec_gain VARCHAR2(2000);
39 l_pre_rec_loss VARCHAR2(2000);
40 l_pre_rec_curr_round VARCHAR2(2000);
41 l_pre_rec_deftax VARCHAR2(2000);
42 l_post_general VARCHAR2(2000);
43 l_pre_rec_tax VARCHAR2(2000);
44
45 l_pre_adj_nrtax VARCHAR2(2000);
46 l_pre_adj_finchrg VARCHAR2(2000);
47 l_pre_adj_finchrg_nrtax VARCHAR2(2000);
48 l_pre_adj_tax VARCHAR2(2000);
49 l_pre_adj_deftax VARCHAR2(2000);
50
51 l_pre_adjdr_ar VARCHAR2(2000);
52 l_pre_adjcr_ar VARCHAR2(2000);
53 l_pre_adjdr_adj VARCHAR2(2000);
54 l_pre_adjcr_adj VARCHAR2(2000);
55 l_pre_adjdr VARCHAR2(2000);
56 l_pre_adjcr VARCHAR2(2000);
57 l_pre_ct_line VARCHAR2(2000);
58 l_post_ct_line VARCHAR2(2000);
59 l_sob_id NUMBER(15); -- Sob_id
60 l_user_id NUMBER(15); -- Created_by
61 l_pst_ctrl_id NUMBER(15); -- Group_id
62 l_parent_request_id NUMBER(15); -- used in posting control cursor
63 l_gl_start_date VARCHAR2(20);
64 l_post_through_date VARCHAR2(20);
65 l_source gl_je_sources.user_je_source_name%TYPE; -- Source_name
66 l_status VARCHAR2(30); -- Status
67 l_actual_flag VARCHAR2(1); -- Actual_flag
68
69 -- Profile option FV: Post Detailed Receipt Accounting
70
71 l_post_det_acct_flag VARCHAR2(1) := 'Y';
72 l_rct_post_det_flag VARCHAR2(1) := 'Y';
73 l_resp_appl_id fnd_application.application_id%TYPE;
74 l_user_resp_id fnd_responsibility.responsibility_id%TYPE;
75 l_error_message VARCHAR2(3000);
76 l_errbuf VARCHAR2(30);
77 l_retcode VARCHAR2(30);
78 INVALID_DISTRIBUTION EXCEPTION;
79
80 l_run_num NUMBER(15);
81
82 /* ################################## GLOBAL VARIABLES DECLARE END ################################## */
83
84 --===========================FND_LOG.START=====================================
85 g_state_level NUMBER := FND_LOG.LEVEL_STATEMENT;
86 g_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
87 g_event_level NUMBER := FND_LOG.LEVEL_EVENT;
88 g_excep_level NUMBER := FND_LOG.LEVEL_EXCEPTION;
89 g_error_level NUMBER := FND_LOG.LEVEL_ERROR;
90 g_unexp_level NUMBER := FND_LOG.LEVEL_UNEXPECTED;
91 g_path VARCHAR2(50) := 'PSA.PLSQL.PSAMFG2B.PSA_XFR_TO_GL_PKG.';
92 --===========================FND_LOG.END=======================================
93
94 PROCEDURE Transfer_to_gl ( errbuf OUT NOCOPY VARCHAR2,
95 retcode OUT NOCOPY VARCHAR2,
96 p_set_of_books_id IN NUMBER,
97 p_gl_date_from IN VARCHAR2,
98 p_gl_date_to IN VARCHAR2,
99 p_gl_posted_date IN VARCHAR2,
100 p_parent_req_id IN NUMBER,
101 p_summary_flag IN VARCHAR2,
102 p_pst_ctrl_id IN NUMBER)
103
104 IS
105
106 MFAR_PROC_EXCEPTION EXCEPTION;
107 -- ========================= FND LOG ===========================
108 l_full_path VARCHAR2(100) := g_path || 'Transfer_to_gl';
109 -- ========================= FND LOG ===========================
110
111 BEGIN
112
113 -- ========================= FND LOG ===========================
114 psa_utils.debug_other_string(g_state_level,l_full_path,
115 ' ########################## ');
116 psa_utils.debug_other_string(g_state_level,l_full_path,
117 ' ## Transfer to gl START ## ');
118 psa_utils.debug_other_string(g_state_level,l_full_path,
119 ' ########################## ');
120 psa_utils.debug_other_string(g_state_level,l_full_path, ' '
121 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
122 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
123 psa_utils.debug_other_string(g_state_level,l_full_path, ' PROCESS : ');
124 psa_utils.debug_other_string(g_state_level,l_full_path, ' ========= ');
125 -- ========================= FND LOG ===========================
126
127 /*
128 ## This procedure will call the procedures in the following order
129 ## 1. Transaction
130 ## 2. Receipts
131 ## 3. Miscellaneous receipts
132 ## 4. Adjustments
133 */
134
135 retcode := 'S';
136 l_sob_id := p_set_of_books_id;
137 l_parent_request_id := p_parent_req_id;
138
139 -- Bug 3767919 (Tpradhan)
140 -- Assigning the value of p_pst_ctrl_id to variable l_pst_ctrl_id here.
141 -- Assignment from populate_global_variables has been removed.
142
143 l_pst_ctrl_id := p_pst_ctrl_id;
144
145 -- ========================= FND LOG ===========================
146 psa_utils.debug_other_string(g_state_level,l_full_path,
147 ' ##> value of l_pst_ctrl_id set to '||p_pst_ctrl_id);
148 psa_utils.debug_other_string(g_state_level,l_full_path,
149 ' ##> setting savepoint PSA_PSAMFG2B ');
150 -- ========================= FND LOG ===========================
151 SAVEPOINT PSA_PSAMFG2B;
152
153 -- ========================= FND LOG ===========================
154 psa_utils.debug_other_string(g_state_level,l_full_path,
155 ' ##> Now populating global variables ');
156 -- ========================= FND LOG ===========================
157 Populate_global_variables;
158
159 -- ========================= FND LOG ===========================
160 psa_utils.debug_other_string(g_state_level,l_full_path,
161 ' ##> Trasferring data to GL interface ');
162 -- ========================= FND LOG ===========================
163
164 IF arp_global.sysparam.accounting_method = 'ACCRUAL' THEN
165
166 -- ========================= FND LOG ===========================
167 psa_utils.debug_other_string(g_state_level,l_full_path,
168 ' arp_global.sysparam.accounting_method ==> ACCRUAL ');
169 psa_utils.debug_other_string(g_state_level,l_full_path,
170 ' --> Calling Reverse_core_entries_if_any');
171 -- ========================= FND LOG ===========================
172
173 -- Bug 3621280.
174 -- reversing Core CM applications if they dont balance by fund or Balance.
175 Reverse_core_entries_if_any (errbuf,
176 retcode,
177 p_set_of_books_id,
178 l_error_message);
179
180 -- ========================= FND LOG ===========================
181 psa_utils.debug_other_string(g_state_level,l_full_path,
182 ' retcode ==> ' || retcode);
183 -- ========================= FND LOG ===========================
184
185 IF retcode = 'F' THEN
186 RAISE MFAR_PROC_EXCEPTION;
187 END IF;
188
189 -- ========================= FND LOG ===========================
190 psa_utils.debug_other_string(g_state_level,l_full_path,
191 ' Calling MFAR_Trx_to_gl ');
192 -- ========================= FND LOG ===========================
193
194 MFAR_Trx_to_gl ( errbuf,
195 retcode,
196 p_set_of_books_id,
197 p_gl_date_from,
198 p_gl_date_to,
199 p_gl_posted_date,
200 p_summary_flag);
201
202 -- ========================= FND LOG ===========================
203 psa_utils.debug_other_string(g_state_level,l_full_path,
204 ' retcode ==> ' || retcode);
205 -- ========================= FND LOG ===========================
206
207 IF retcode = 'F' THEN
208 RAISE MFAR_PROC_EXCEPTION;
209 END IF;
210
211 -- ========================= FND LOG ===========================
212 psa_utils.debug_other_string(g_state_level,l_full_path,
213 ' Calling MFAR_Rcpt_to_gl ');
214 -- ========================= FND LOG ===========================
215
216 MFAR_Rcpt_to_gl (errbuf,
217 retcode,
218 p_set_of_books_id,
219 p_gl_date_from ,
220 p_gl_date_to,
221 p_gl_posted_date,
222 p_summary_flag);
223
224 -- ========================= FND LOG ===========================
225 psa_utils.debug_other_string(g_state_level,l_full_path,
226 ' retcode ==> ' || retcode);
227 -- ========================= FND LOG ===========================
228
229 IF retcode = 'F' THEN
230 RAISE MFAR_PROC_EXCEPTION;
231 END IF;
232
233 -- ========================= FND LOG ===========================
234 psa_utils.debug_other_string(g_state_level,l_full_path,
235 ' Calling Misc_rct_to_gl ');
236 -- ========================= FND LOG ===========================
237
238 Misc_rct_to_gl( errbuf,
239 retcode,
240 p_set_of_books_id,
241 p_gl_date_from,
242 p_gl_date_to ,
243 p_gl_posted_date);
244
245 -- ========================= FND LOG ===========================
246 psa_utils.debug_other_string(g_state_level,l_full_path,
247 ' retcode ==> ' || retcode);
248 -- ========================= FND LOG ===========================
249
250 IF retcode = 'F' THEN
251 RAISE MFAR_PROC_EXCEPTION;
252 END IF;
253
254 -- ========================= FND LOG ===========================
255 psa_utils.debug_other_string(g_state_level,l_full_path,
256 ' Calling MFAR_Adj_to_gl ');
257 -- ========================= FND LOG ===========================
258
259 MFAR_Adj_to_gl ( errbuf,
260 retcode,
261 p_set_of_books_id,
262 p_gl_date_from,
263 p_gl_date_to,
264 p_gl_posted_date,
265 p_summary_flag);
266
267 -- ========================= FND LOG ===========================
268 psa_utils.debug_other_string(g_state_level,l_full_path,
269 ' retcode ==> ' || retcode);
270 -- ========================= FND LOG ===========================
271
272 IF retcode = 'F' THEN
273 RAISE MFAR_PROC_EXCEPTION;
274 END IF;
275
276 ELSIF arp_global.sysparam.accounting_method = 'CASH' THEN
277
278 -- ========================= FND LOG ===========================
279 psa_utils.debug_other_string(g_state_level,l_full_path,
280 ' arp_global.sysparam.accounting_method ==> CASH ');
281 psa_utils.debug_other_string(g_state_level,l_full_path,
282 ' Calling MFAR_Rcpt_to_gl_CB ');
283 -- ========================= FND LOG ===========================
284
285 MFAR_Rcpt_to_gl_CB
286 (errbuf,
287 retcode,
288 p_set_of_books_id,
289 p_gl_date_from ,
290 p_gl_date_to,
291 p_gl_posted_date,
292 p_summary_flag);
293
294 -- ========================= FND LOG ===========================
295 psa_utils.debug_other_string(g_state_level,l_full_path,
296 ' retcode ==> ' || retcode);
297 -- ========================= FND LOG ===========================
298
299 IF retcode = 'F' THEN
300 RAISE MFAR_PROC_EXCEPTION;
301 END IF;
302
303 -- ========================= FND LOG ===========================
304 psa_utils.debug_other_string(g_state_level,l_full_path,
305 ' Calling Misc_rct_to_gl_CB ');
306 -- ========================= FND LOG ===========================
307
308 Misc_rct_to_gl_CB
309 (errbuf,
310 retcode,
311 p_set_of_books_id,
312 p_gl_date_from,
313 p_gl_date_to ,
314 p_gl_posted_date);
315
316 -- ========================= FND LOG ===========================
317 psa_utils.debug_other_string(g_state_level,l_full_path,
318 ' retcode ==> ' || retcode);
319 -- ========================= FND LOG ===========================
320
321 IF retcode = 'F' THEN
322 RAISE MFAR_PROC_EXCEPTION;
323 END IF;
324
325 END IF;
326
327 IF psa_mfar_utils.g_invalid_index > 0 THEN
328 FND_MESSAGE.SET_NAME ('PSA', 'PSA_INVALID_CODE_COMBINATION');
329 psa_utils.debug_other_msg(p_level => g_error_level,
330 p_full_path => l_full_path,
331 p_remove_from_stack => FALSE);
332 fnd_file.put_line(fnd_file.log, fnd_message.get);
333 fnd_file.put_line(fnd_file.log, ' ');
334
335 FOR i IN 1..psa_mfar_utils.g_invalid_index LOOP
336 fnd_file.put_line(fnd_file.log, psa_mfar_utils.g_invalid_combinations(i).combination );
337 fnd_file.put_line(fnd_file.log, psa_mfar_utils.g_invalid_combinations(i).error_message);
338 fnd_file.put_line(fnd_file.log, ' ');
339 END LOOP;
340 RAISE MFAR_PROC_EXCEPTION;
341 END IF;
342
343 -- ========================= FND LOG ===========================
344 psa_utils.debug_other_string(g_state_level,l_full_path,
345 ' ##> Updating GL interface with proper segments ');
346 -- ========================= FND LOG ===========================
347
348 Upd_seg_in_gl_interface;
349
350 -- ========================= FND LOG ===========================
351 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
352 psa_utils.debug_other_string(g_state_level,l_full_path,
353 ' ############################## ');
354 psa_utils.debug_other_string(g_state_level,l_full_path,
355 ' #### Transfer to gl END #### ');
356 psa_utils.debug_other_string(g_state_level,l_full_path,
357 ' ############################## ');
358 psa_utils.debug_other_string(g_state_level,l_full_path,
359 ' ' || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
360 -- ========================= FND LOG ===========================
361
362 EXCEPTION
363 WHEN MFAR_PROC_EXCEPTION THEN
364 -- ========================= FND LOG ===========================
365 psa_utils.debug_other_string(g_excep_level,l_full_path,
366 'EXCEPTION - MFAR_PROC_EXCEPTION : ERROR IN PSA_TRANSFER_TO_GL_PKG.TRANSFER_TO_GL');
367 -- ========================= FND LOG ===========================
368
369 BEGIN
370 -- ========================= FND LOG ===========================
371 psa_utils.debug_other_string(g_excep_level,l_full_path,'Rolling back');
372 -- ========================= FND LOG ===========================
373 ROLLBACK TO PSA_PSAMFG2B;
374 EXCEPTION
375 WHEN OTHERS THEN
376 -- ========================= FND LOG ===========================
377 psa_utils.debug_other_string(g_excep_level,l_full_path,
378 'EXCEPTION - MFAR_PROC_EXCEPTION : SAVEPOINT ERASED.');
379 -- ========================= FND LOG ===========================
380 END;
381 retcode := 'F';
382
383 WHEN OTHERS THEN
384 -- ========================= FND LOG ===========================
385 psa_utils.debug_other_string(g_excep_level,l_full_path,'EXCEPTION - OTHERS : ERROR IN PSA_TRANSFER_TO_GL_PKG.TRANSFER_TO_GL');
386 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
387 psa_utils.debug_unexpected_msg(l_full_path);
388 -- ========================= FND LOG ===========================
389
390 BEGIN
391 -- ========================= FND LOG ===========================
392 psa_utils.debug_other_string(g_excep_level,l_full_path, 'Rolling back');
393 -- ========================= FND LOG ===========================
394 ROLLBACK TO PSA_PSAMFG2B;
395 EXCEPTION
396 WHEN OTHERS THEN
397 -- ========================= FND LOG ===========================
398 psa_utils.debug_other_string(g_excep_level,l_full_path,
399 'EXCEPTION - OTHERS : SAVEPOINT ERASED.');
400 -- ========================= FND LOG ===========================
401 END;
402 retcode := 'F';
403
404 END Transfer_to_gl;
405
406 /*###################################### MISC_RCT_TO_GL ###########################################*/
407
408 PROCEDURE Misc_rct_to_gl (errbuf OUT NOCOPY VARCHAR2,
409 retcode OUT NOCOPY VARCHAR2,
410 p_set_of_books_id IN NUMBER,
411 p_gl_date_from IN VARCHAR2,
412 p_gl_date_to IN VARCHAR2,
413 p_gl_posted_date IN VARCHAR2)
414 IS
415
416 CURSOR c_crh_post
417 IS
418 SELECT cash_receipt_history_id FROM ar_cash_receipt_history
419 WHERE posting_control_id = l_pst_ctrl_id
420 AND cash_receipt_history_id NOT IN
421 (SELECT cash_receipt_history_id FROM psa_misc_posting);
422
423 CURSOR c_create_dist
424 IS
425 SELECT cr.cash_receipt_id FROM ar_cash_receipts cr, ar_cash_receipt_history crh
426 WHERE cr.cash_receipt_id = crh.cash_receipt_id
427 AND crh.posting_control_id = l_pst_ctrl_id;
428
429
430 -- GL Transfer will have 4 cursors
431 -- cursor 1 inserts records into gl_interface for a simple misc receipt and its reversal entries.
432 -- Cursor 2 inserts records that reverse core entries
433 -- Cursor 3 inserts records for MFAR Entries resulting from more activties on the receipt (like clearing, unclearing..)
434
435 CURSOR Cur_MFAR_mrct_lines
436 IS
437 SELECT
438 mfd.gl_date gl_date,
439 cr.doc_sequence_id doc_seqid,
440 cr.doc_sequence_value doc_num,
441 ard.currency_code currency,
442 decode(to_number(l1.lookup_code),
443 1, mfd.cash_ccid, 2, ard2.code_combination_id)
444 ccid,
445 decode(to_number(l1.lookup_code), 1, ard.amount_cr, 2, ard.amount_dr) entered_dr,
446 decode(to_number(l1.lookup_code),1, ard.amount_dr, 2, ard.amount_cr) entered_cr,
447 decode(to_number(l1.lookup_code),1, ard.acctd_amount_cr, 2, ard.acctd_amount_dr) accounted_dr,
448 decode(to_number(l1.lookup_code),1, ard.acctd_amount_dr, 2, ard.acctd_amount_cr) accounted_cr,
449 l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
450 DECODE(to_number(l1.lookup_code),1, ('MFAR Misc. Receipt ' || cr.receipt_number),
451 2,('Receipt ' || cr.receipt_number||'(MFAR)')) ref10,
452 TO_CHAR (mcd.posting_control_id) ref21,
453 TO_CHAR (cr.cash_receipt_id) ref22,
454 TO_CHAR (ard.line_id) ref23,
455 cr.receipt_number ref24,
456 TO_CHAR (mcd.misc_cash_distribution_id) ref25,
457 NULL ref26,
458 'c1' ref27,
459 'MISC' ref28,
460 'MISC_' || ard.source_type ref29,
461 'PSA_MF_MISC_DIST_ALL' ref30
462 FROM
463 psa_mf_misc_dist_all mfd,
464 psa_lookup_codes l1,
465 ar_misc_cash_distributions mcd,
466 ar_distributions ard,
467 ar_cash_receipts cr,
468 ar_cash_receipt_history crh,
469 ar_distributions ard2
470 WHERE
471 l1.lookup_type = 'PSA_CARTESIAN_JOIN'
472 AND l1.lookup_code IN ('1','2')
473 AND mfd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
474 AND nvl(mfd.posting_control_id,-3)= -3
475 AND crh.status = mfd.reference1
476 AND mcd.posting_control_id = l_pst_ctrl_id
477 AND mcd.set_of_books_id = l_sob_id
478 AND mcd.cash_receipt_id = cr.cash_receipt_id
479 AND ard.source_table = 'MCD'
480 AND ard.source_id = mcd.misc_cash_distribution_id
481 AND cr.cash_receipt_id = crh.cash_receipt_id
482 AND crh.posting_control_id = l_pst_ctrl_id
483 AND ((crh.first_posted_record_flag = 'Y') OR (crh.current_record_flag = 'Y' AND crh.status = 'REVERSED'))
484 AND crh.cash_receipt_history_id = ard2.source_id
485 AND ard2.source_table = 'CRH'
486 AND (ard2.amount_cr is null or ard2.amount_cr > 0);
487
488
489 CURSOR Cur_MFAR_crct_hist_lines
490 IS
491 SELECT
492 crh.gl_date gl_date,
493 cr.doc_sequence_id doc_seqid,
494 cr.doc_sequence_value doc_num,
495 cr.currency_code currency,
496 ard.code_combination_id ccid,
497 to_number(ard.amount_cr) entered_dr,
498 to_number(ard.amount_dr) entered_cr,
499 to_number(ard.acctd_amount_cr) accounted_dr,
500 to_number(ard.acctd_amount_dr) accounted_cr,
501 l_batch_prefix || TO_CHAR (l_pst_ctrl_id) ref1,
502 ('Receipt ' || cr.receipt_number||'(MFAR)') ref10,
503 TO_CHAR (l_pst_ctrl_id) ref21,
504 DECODE(cr.type,
505 'CASH',TO_CHAR(cr.cash_receipt_id)||'C'||
506 TO_CHAR(crh.cash_receipt_history_id),
507 'MISC',TO_CHAR(cr.cash_receipt_id)) ref22,
508 TO_CHAR(ard.line_id) ref23,
509 cr.receipt_number ref24,
510 DECODE(cr.type,
511 'CASH',TO_CHAR(NULL),
512 'MISC',TO_CHAR(crh.cash_receipt_history_id)) ref25,
513 TO_CHAR(NULL) ref26,
514 'c2' ref27,
515 DECODE( cr.type,
516 'MISC', 'MISC',
517 'TRADE') ref28,
518 DECODE( cr.type,
519 'MISC', 'MISC_',
520 'TRADE_') || ard.source_type ref29,
521 'AR_CASH_RECEIPT_HISTORY' ref30
522 FROM
523 ar_cash_receipt_history crh,
524 psa_receivables_trx_all psa,
525 ar_distributions ard,
526 ar_cash_receipts cr
527 WHERE
528 crh.status <> 'REVERSED'
529 AND crh.posting_control_id = l_pst_ctrl_id
530 AND crh.cash_receipt_id = cr.cash_receipt_id
531 AND cr.receivables_trx_id = psa.psa_receivables_trx_id
532 AND cr.set_of_books_id = l_sob_id
533 AND ard.source_table = 'CRH'
534 AND ard.source_id = crh.cash_receipt_history_id
535 AND nvl(crh.first_posted_record_flag, 'N') = 'N';
536
537
538
539 CURSOR Cur_MFAR_LINES
540 IS
541 SELECT
542 mfd.gl_date gl_date,
543 cr.doc_sequence_id doc_seqid,
544 cr.doc_sequence_value doc_num,
545 cr.currency_code currency,
546 decode(to_number(l1.lookup_code), 1, mfd.cash_ccid) ccid,
547 -- 2, mfd.reversal_ccid) ccid, -- rgopalan
548 decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),1, mcd.amount, null),
549 'REMITTED',decode(to_number(l1.lookup_code),2, mcd.amount, null)) entered_dr,
550 decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),2, mcd.amount, null),
551 'REMITTED',decode(to_number(l1.lookup_code),1, mcd.amount, null)) entered_cr,
552 decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),1, mcd.amount, null),
553 'REMITTED',decode(to_number(l1.lookup_code),2, mcd.amount, null)) accounted_dr,
554 decode(crh.status, 'CLEARED', decode(to_number(l1.lookup_code),2, mcd.amount, null),
555 'REMITTED',decode(to_number(l1.lookup_code),1, mcd.amount, null)) accounted_cr,
556 l_batch_prefix || TO_CHAR (l_pst_ctrl_id) ref1,
557 DECODE(l1.lookup_code,1, ('MFAR Cash ' || cr.receipt_number),
558 2,('MFAR Remittance ' || cr.receipt_number)) ref10,
559 TO_CHAR (l_pst_ctrl_id) ref21,
560 DECODE(cr.type,
561 'CASH',TO_CHAR(cr.cash_receipt_id)||'C'||
562 TO_CHAR(crh.cash_receipt_history_id),
563 'MISC',TO_CHAR(cr.cash_receipt_id)) ref22,
564 -- TO_CHAR(mfd.misc_cash_distribution_id) ref23,
565 nvl( get_misc_ard_id(mfd.misc_cash_distribution_id),
566 to_char(mfd.misc_cash_distribution_id) ) ref23,
567 cr.receipt_number ref24,
568 DECODE(cr.type,
569 'CASH',TO_CHAR(NULL),
570 'MISC',TO_CHAR(crh.cash_receipt_history_id)) ref25,
571 TO_CHAR(NULL) ref26,
572 'c3' ref27,
573 DECODE( cr.type,
574 'MISC', 'MISC',
575 'TRADE') ref28,
576 DECODE( cr.type,
577 'MISC', 'MISC_',
578 'TRADE_') ref29,
579 'PSA_MF_MISC_DIST_ALL' ref30
580 FROM
581 psa_mf_misc_dist_all mfd,
582 psa_lookup_codes l1,
583 ar_misc_cash_distributions mcd,
584 ar_cash_receipts cr,
585 ar_cash_receipt_history crh,
586 ar_cash_receipt_history crhold
587 WHERE
588 mfd.reference1 = 'CLEARED'
589 AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
590 AND l1.lookup_code IN (1,2)
591 AND mfd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
592 AND mcd.set_of_books_id = l_sob_id
593 AND mcd.cash_receipt_id = cr.cash_receipt_id
594 AND cr.cash_receipt_id = crh.cash_receipt_id
595 AND crh.posting_control_id = l_pst_ctrl_id
596 AND crh.cash_receipt_history_id = crhold.reversal_cash_receipt_hist_id
597 AND nvl(crh.first_posted_record_flag, 'N') = 'N'
598 AND ((crh.STATUS <> 'REVERSED'));
599
600
601 PSA_MISC_GLX_FAIL EXCEPTION;
602 -- ========================= FND LOG ===========================
603 l_full_path VARCHAR2(100) := g_path || 'misc_rc_to_gl';
604 -- ========================= FND LOG ===========================
605
606 BEGIN
607
608 -- ========================= FND LOG ===========================
609 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
610 psa_utils.debug_other_string(g_state_level,l_full_path,
611 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
612 psa_utils.debug_other_string(g_state_level,l_full_path,
613 ' (TRANSFERRING MISCELLANEOUS RECEIPTS) '
614 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
615 psa_utils.debug_other_string(g_state_level,l_full_path,
616 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
617 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
618 -- ========================= FND LOG ===========================
619
620 retcode := 'S';
621
622 l_gl_start_date := p_gl_date_from;
623 l_post_through_date := p_gl_date_to;
624 l_sob_id := p_set_of_books_id;
625
626 -- ========================= FND LOG ===========================
627 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
628 psa_utils.debug_other_string(g_state_level,l_full_path, ' PARAMETERS :');
629 psa_utils.debug_other_string(g_state_level,l_full_path, ' ============');
630 psa_utils.debug_other_string(g_state_level,l_full_path,
631 ' p_gl_date_from -->' || p_gl_date_from );
632 psa_utils.debug_other_string(g_state_level,l_full_path,
633 ' p_gl_date_to -->' || p_gl_date_to );
634 psa_utils.debug_other_string(g_state_level,l_full_path,
635 ' p_set_of_books_id -->' || p_set_of_books_id);
636 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
637 psa_utils.debug_other_string(g_state_level,l_full_path, ' OTHER VALUES :');
638 psa_utils.debug_other_string(g_state_level,l_full_path, ' ============= ');
639 psa_utils.debug_other_string(g_state_level,l_full_path,
640 ' l_gl_start_date -->' || l_gl_start_date);
641 psa_utils.debug_other_string(g_state_level,l_full_path,
642 ' l_post_through_date -->' || l_post_through_date);
643 psa_utils.debug_other_string(g_state_level,l_full_path,
644 ' l_sob_id -->' || l_sob_id);
645 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
646 psa_utils.debug_other_string(g_state_level,l_full_path, ' PROCESS :');
647 psa_utils.debug_other_string(g_state_level,l_full_path, ' =========');
648 psa_utils.debug_other_string(g_state_level,l_full_path, ' ');
649 -- ========================= FND LOG ===========================
650
651 BEGIN
652
653 /*
654 ## Call Create Misc Distributions program to create Multi-fund Distributions
655 ## for receipts that fall within the GL DATE parameters.
656 */
657
658 -- ========================= FND LOG ===========================
659 psa_utils.debug_other_string(g_state_level,l_full_path,
660 ' --> Creating Distributions for Misc Receipts');
661 -- ========================= FND LOG ===========================
662
663 FOR I IN c_create_dist
664 LOOP
665
666 IF (I.cash_receipt_id IS NOT NULL) THEN
667
668 IF NOT (PSA_MF_CREATE_DISTRIBUTIONS.create_distributions (errbuf => l_errbuf,
669 retcode => l_retcode,
670 p_mode => 'R',
671 p_document_id => I.cash_receipt_id,
672 p_set_of_books_id => l_sob_id,
673 run_num => l_run_num,
674 p_error_message => l_error_message,
675 p_report_only => 'N')) THEN
676
677 -- ========================= FND LOG ===========================
678 psa_utils.debug_other_string(g_excep_level,l_full_path,
679 ' --> PSA_MF_CREATE_DISTRIBUTIONS.create_distributions -> FALSE');
680 -- ========================= FND LOG ===========================
681
682 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
683 -- ========================= FND LOG ===========================
684 psa_utils.debug_other_string(g_excep_level,l_full_path,
685 ' --> Raising invalid_distribution');
686 -- ========================= FND LOG ===========================
687 Raise INVALID_DISTRIBUTION;
688 END IF;
689 ELSE
690 -- ========================= FND LOG ===========================
691 psa_utils.debug_other_string(g_state_level,l_full_path,
692 ' --> Cash receipt id --> ' || I.cash_receipt_id);
693 -- ========================= FND LOG ===========================
694 END IF;
695 END IF;
696
697 END LOOP;
698 END;
699
700 -- ========================= FND LOG ===========================
701 psa_utils.debug_other_string(g_state_level,l_full_path,
702 ' --> Inserting into GL INTERFACE foor - PSA_MF_MISC_DIST_ALL');
703 -- ========================= FND LOG ===========================
704
705 FOR J IN Cur_MFAR_mrct_lines
706 LOOP
707
708 INSERT INTO gl_interface
709 (created_by,
710 date_created,
711 status,
712 actual_flag,
713 group_id,
714 set_of_books_id,
715 user_je_source_name,
716 user_je_category_name,
717 accounting_date,
718 subledger_doc_sequence_id,
719 subledger_doc_sequence_value,
720 currency_code,
721 code_combination_id,
722 entered_dr,
723 entered_cr,
724 accounted_dr,
725 accounted_cr,
726 reference1,
727 reference10,
728 reference21,
729 reference22,
730 reference23,
731 reference24,
732 reference25,
733 reference26,
734 reference27,
735 reference28,
736 reference29,
737 reference30)
738 VALUES (l_user_id,
739 trunc(sysdate),
740 l_status,
741 l_actual_flag,
742 l_pst_ctrl_id,
743 l_sob_id,
744 l_source,
745 l_misc_cat_name,
746 J.gl_date,
747 J.doc_seqid,
748 J.doc_num,
749 J.currency,
750 J.ccid,
751 J.entered_dr,
752 J.entered_cr,
753 J.accounted_dr,
754 J.accounted_cr,
755 J.ref1,
756 J.ref10,
757 J.ref21,
758 J.ref22,
759 J.ref23,
760 J.ref24,
761 J.ref25,
762 J.ref26,
763 J.ref27,
764 J.ref28,
765 J.ref29,
766 J.ref30);
767
768 IF (SQL%FOUND) THEN
769 -- ========================= FND LOG ===========================
770 psa_utils.debug_other_string(g_state_level,l_full_path,
771 ' --> Receipt Number ==> ' || J.ref24 );
772 psa_utils.debug_other_string(g_state_level,l_full_path,
773 ' CCID => ' || J.ccid
774 || ' DEBIT => ' || J.entered_dr
775 || ' CREDIT => ' || J.entered_cr);
776 -- ========================= FND LOG ===========================
777 END IF;
778
779 END LOOP;
780
781 -- ========================= FND LOG ===========================
782 psa_utils.debug_other_string(g_state_level,l_full_path,
783 ' --> Inserting into GL INTERFACE for - PSA_MF_REVERSE_OF_AR_CRH');
784 -- ========================= FND LOG ===========================
785
786 FOR J IN Cur_MFAR_crct_hist_lines
787 LOOP
788
789 /*
790 ## For each misc_cash_distribution_id the record will be inserted.
791 */
792
793 INSERT INTO gl_interface
794 (created_by,
795 date_created,
796 status,
797 actual_flag,
798 group_id,
799 set_of_books_id,
800 user_je_source_name,
801 user_je_category_name,
802 accounting_date,
803 subledger_doc_sequence_id,
804 subledger_doc_sequence_value,
805 currency_code,
806 code_combination_id,
807 entered_dr,
808 entered_cr,
809 accounted_dr,
810 accounted_cr,
811 reference1,
812 reference10,
813 reference21,
814 reference22,
815 reference23,
816 reference24,
817 reference25,
818 reference26,
819 reference27,
820 reference28,
821 reference29,
822 reference30)
823 VALUES (l_user_id,
824 trunc(sysdate),
825 l_status,
826 l_actual_flag,
827 l_pst_ctrl_id,
828 l_sob_id,
829 l_source,
830 l_misc_cat_name,
831 J.gl_date,
832 J.doc_seqid,
833 J.doc_num,
834 J.currency,
835 J.ccid,
836 J.entered_dr,
837 J.entered_cr,
838 J.accounted_dr,
839 J.accounted_cr,
840 J.ref1,
841 J.ref10,
842 J.ref21,
843 J.ref22,
844 J.ref23,
845 J.ref24,
846 J.ref25,
847 J.ref26,
848 J.ref27,
849 J.ref28,
850 J.ref29,
851 J.ref30);
852
853 IF (SQL%FOUND) THEN
854 -- ========================= FND LOG ===========================
855 psa_utils.debug_other_string(g_state_level,l_full_path,
856 ' --> Receipt Number ==> ' || J.ref24);
857 psa_utils.debug_other_string(g_state_level,l_full_path,
858 ' CCID => ' || J.ccid
859 ||' DEBIT => ' || J.entered_dr
860 ||' CREDIT => ' || J.entered_cr );
861 -- ========================= FND LOG ===========================
862 END IF;
863 END LOOP;
864
865 FOR K IN Cur_MFAR_LINES
866 LOOP
867
868 INSERT INTO gl_interface
869 (created_by,
870 date_created,
871 status,
872 actual_flag,
873 group_id,
874 set_of_books_id,
875 user_je_source_name,
876 user_je_category_name,
877 accounting_date,
878 subledger_doc_sequence_id,
879 subledger_doc_sequence_value,
880 currency_code,
881 code_combination_id,
882 entered_dr,
883 entered_cr,
884 accounted_dr,
885 accounted_cr,
886 reference1,
887 reference10,
888 reference21,
889 reference22,
890 reference23,
891 reference24,
892 reference25,
893 reference26,
894 reference27,
895 reference28,
896 reference29,
897 reference30)
898 VALUES (l_user_id,
899 trunc(sysdate),
900 l_status,
901 l_actual_flag,
902 l_pst_ctrl_id,
903 l_sob_id,
904 l_source,
905 l_misc_cat_name,
906 K.gl_date,
907 K.doc_seqid,
908 K.doc_num,
909 K.currency,
910 K.ccid,
911 K.entered_dr,
912 K.entered_cr,
913 K.accounted_dr,
914 K.accounted_cr,
915 K.ref1,
916 K.ref10,
917 K.ref21,
918 K.ref22,
919 K.ref23,
920 K.ref24,
921 K.ref25,
922 K.ref26,
923 K.ref27,
924 K.ref28,
925 K.ref29,
926 K.ref30);
927
928 IF (SQL%FOUND) THEN
929 -- ========================= FND LOG ===========================
930 psa_utils.debug_other_string(g_state_level,l_full_path,
931 ' --> Receipt Number ==> ' || K.ref24);
932 psa_utils.debug_other_string(g_state_level,l_full_path,
933 ' CCID => ' || K.ccid
934 || ' DEBIT => ' || K.entered_dr
935 || ' CREDIT => ' || K.entered_cr );
936 -- ========================= FND LOG ===========================
937 END IF;
938
939 END LOOP;
940
941 /*
942 ## Insert a record into psa_misc_posting to keep track of
943 ## each reversing record of AR_CASH_RECEIPT_HISTORY, that we insert into GL_INTERFACE
944 */
945
946 -- ========================= FND LOG ===========================
947 psa_utils.debug_other_string(g_state_level,l_full_path,
948 ' --> Inserting into psa_misc_posting ');
949 -- ========================= FND LOG ===========================
950
951 FOR J IN c_crh_post
952 LOOP
953
954 INSERT INTO psa_misc_posting (cash_receipt_history_id, posting_control_id)
955 VALUES (J.cash_receipt_history_id, l_pst_ctrl_id);
956
957 -- ========================= FND LOG ===========================
958 psa_utils.debug_other_string(g_state_level,l_full_path,
959 ' --> Cash receipt hist id --> ' || J.cash_receipt_history_id);
960 -- ========================= FND LOG ===========================
961
962 END LOOP;
963
964 UPDATE psa_mf_misc_dist_all
965 SET posting_control_id = l_pst_ctrl_id
966 WHERE misc_cash_distribution_id IN
967 (SELECT misc_cash_distribution_id FROM ar_misc_cash_distributions
968 WHERE posting_control_id = l_pst_ctrl_id);
969
970 IF (SQL%FOUND) THEN
971 -- ====================== FND LOG ==========================
972 psa_utils.debug_other_string(g_state_level,l_full_path,
973 ' --> (PSA_MF_MISC_DIST_ALL) Updated Posting control id for '
974 || (SQL%ROWCOUNT));
975 -- ====================== FND LOG ==========================
976 END IF;
977
978 -- Bug3967158
979 -- delete all such records in psa_mf_misc_dist_all that
980 -- do not have the matching records on core distributions
981
982 DELETE FROM psa_mf_misc_dist_all
983 WHERE posting_control_id = l_pst_ctrl_id
984 AND misc_cash_distribution_id NOT IN
985 (SELECT misc_cash_distribution_id
986 FROM ar_misc_cash_distributions
987 WHERE posting_control_id = l_pst_ctrl_id);
988
989 IF (SQL%FOUND) THEN
990 -- ====================== FND LOG ==========================
991 psa_utils.debug_other_string(g_state_level,l_full_path,
992 ' --> (PSA_MF_MISC_DIST_ALL) Deleted --> '
993 || (SQL%ROWCOUNT));
994 -- ====================== FND LOG ==========================
995 END IF;
996
997 -- ========================= FND LOG ===========================
998 psa_utils.debug_other_string(g_state_level,l_full_path,
999 ' --> END of MISC TRANSACTIONS '
1000 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
1001 -- ========================= FND LOG ===========================
1002
1003 EXCEPTION
1004 WHEN INVALID_DISTRIBUTION THEN
1005 -- ========================= FND LOG ===========================
1006 psa_utils.debug_other_string(g_excep_level,l_full_path,
1007 ' --> EXCEPTION - INVALID_DISTRIBUTION raised during PSA_TRANSFER_TO_GL_PKG.Misc_rct_to_gl ');
1008 psa_utils.debug_other_string(g_excep_level,l_full_path,
1009 ' --> p_error_message --> ' || l_error_message);
1010 -- ========================= FND LOG ===========================
1011 retcode := 'F';
1012
1013 WHEN OTHERS THEN
1014 -- ========================= FND LOG ===========================
1015 psa_utils.debug_other_string(g_excep_level,l_full_path,
1016 ' --> EXCEPTION - OTHERS raised during PSA_TRANSFER_TO_GL_PKG.Misc_rct_to_gl ');
1017 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
1018 psa_utils.debug_unexpected_msg(l_full_path);
1019 -- ========================= FND LOG ===========================
1020 errbuf := 2;
1021 retcode := 'F';
1022
1023 END Misc_rct_to_gl;
1024
1025 /*########################################## MFAR_TRX_TO_GL ###########################################*/
1026
1027 PROCEDURE Mfar_trx_to_gl (errbuf OUT NOCOPY VARCHAR2,
1028 retcode OUT NOCOPY VARCHAR2,
1029 p_set_of_books_id IN NUMBER,
1030 p_gl_date_from IN VARCHAR2,
1031 p_gl_date_to IN VARCHAR2,
1032 p_gl_posted_date IN VARCHAR2,
1033 p_summary_flag IN VARCHAR2)
1034 IS
1035
1036 v_customer_trx_id NUMBER(15);
1037
1038 /*
1039 ## This procedure will Transfer transactions to gl_interface table like CREDIT MEMO, DEBIT MEMO, CHARGE BACKS.
1040 */
1041
1042 /* The l_ variables used in this cursor are Global variables in this package */
1043 CURSOR Cur_MFAR_cust_trx_id
1044 IS
1045 -- Bug 3757993 (Tpradhan) .. Start
1046 -- Added UNION clauses to select transactions associated with receipts and adjustments
1047 SELECT ctlgd.customer_trx_id customer_trx_id
1048 FROM ra_cust_trx_line_gl_dist ctlgd
1049 WHERE ctlgd.posting_control_id = l_pst_ctrl_id
1050 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (ctlgd.customer_trx_id, 'TRX', l_sob_id),
1051 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
1052 UNION
1053 SELECT customer_trx_id
1054 FROM ar_adjustments adj
1055 WHERE adj.posting_control_id = l_pst_ctrl_id
1056 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (adj.adjustment_id, 'ADJ', l_sob_id),
1057 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
1058 AND arp_global.sysparam.accounting_method = 'ACCRUAL'
1059 UNION
1060 SELECT applied_customer_trx_id
1061 FROM ar_receivable_applications ra
1062 WHERE ra.status = 'APP'
1063 AND ra.posting_control_id = l_pst_ctrl_id
1064 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (ra.receivable_application_id, 'RCT', l_sob_id),
1065 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
1066 UNION
1067 SELECT ra.applied_customer_trx_id
1068 FROM ar_receivable_applications ra,
1069 ar_cash_receipt_history crh,
1070 ar_cash_receipt_history crho
1071 WHERE crh.posting_control_id = l_pst_ctrl_id
1072 AND crh.cash_receipt_history_id = crho.reversal_cash_receipt_hist_id
1073 AND crh.cash_receipt_id = ra.cash_receipt_id
1074 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (ra.receivable_application_id, 'RCT', l_sob_id),
1075 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
1076 AND ra.status = 'APP';
1077 -- Bug 3757993 .. End
1078
1079 CURSOR Cur_MFAR_trx_lines (p_customer_trx_id NUMBER)
1080 IS
1081 SELECT
1082 DECODE (ctt.type, 'CM', l_cm_cat_name,
1083 'DM', l_dm_cat_name,
1084 'CB', l_cb_cat_name, l_inv_cat_name) category,
1085 ctlgd.gl_date gl_date,
1086 ct.doc_sequence_id doc_seqid,
1087 ct.doc_sequence_value doc_num,
1088 ct.invoice_currency_code currency,
1089 DECODE (l1.lookup_code,
1090 '1', psa_trx_dist.mf_receivables_ccid, /* DEBIT A/C FROM PSA_TRX_DIST */
1091 '2', psa_mfar_utils.get_rec_ccid (null,p_customer_trx_id)) /* CREDIT A/C FROM CORE TRX_DIST */ ccid,
1092 DECODE (l1.lookup_code,
1093 '1', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount),
1094 '2', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL)) entered_dr,
1095 DECODE (l1.lookup_code,
1096 '1', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL),
1097 '2', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount)) entered_cr,
1098 DECODE (l1.lookup_code,
1099 '1', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount),
1100 '2', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL)) accounted_dr,
1101 DECODE (l1.lookup_code,
1102 '1', DECODE (SIGN (ctlgd.amount), -1, -ctlgd.amount, NULL),
1103 '2', DECODE (SIGN (ctlgd.amount), -1, NULL, ctlgd.amount)) accounted_cr,
1104 l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
1105 DECODE (l1.lookup_code,
1106 '1', 'MFAR Receivable' ,
1107 '2', 'MFAR Reversal of Receivable' ) || ' ' || l_pre_ct_line ||
1108 ' ' || 'Invoice ' || ct.trx_number || l_post_ct_line ref10,
1109 TO_CHAR(l_pst_ctrl_id) ref21,
1110 TO_CHAR(ct.customer_trx_id) ref22,
1111 TO_CHAR(ctlgd.cust_trx_line_gl_dist_id) ref23,
1112 ct.trx_number ref24,
1113 hca.account_number ref25,
1114 'CUSTOMER' ref26,
1115 TO_CHAR(ct.bill_to_customer_id) ref27,
1116 DECODE (ctt.type, 'CM', 'CM',
1117 'DM', 'DM',
1118 'CB', 'CB', 'INV') ref28,
1119 DECODE (ctt.type, 'CM', 'CM_',
1120 'DM', 'DM_',
1121 'CB', 'CB_', 'INV_') || ctlgd.account_class ref29,
1122 -- 'PSA_TRX_DIST' ref30
1123 'RA_CUST_TRX_LINE_GL_DIST' ref30
1124 FROM
1125 ar_lookups l,
1126 ra_customer_trx ct,
1127 ra_cust_trx_line_gl_dist ctlgd,
1128 ra_cust_trx_types ctt,
1129 hz_cust_accounts hca,
1130 psa_lookup_codes l1,
1131 psa_mf_trx_dist_all psa_trx_dist
1132 WHERE
1133 ctlgd.customer_trx_id = p_customer_trx_id
1134 AND ctlgd.customer_trx_id = ct.customer_trx_id
1135 AND l.lookup_type = 'AUTOGL_TYPE'
1136 AND l.lookup_code = nvl(ctlgd.account_class,'REV')
1137 AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
1138 AND l1.lookup_code in
1139 (1, decode(ctt.type, 'INV', decode(l_post_det_acct_flag, 'N', -1, 2),
1140 'DM', decode(l_post_det_acct_flag, 'N', -1, 2), 2))
1141 AND ct.bill_to_customer_id = hca.cust_account_id -- cust.customer_id
1142 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
1143 AND ctlgd.account_Class <> 'REC'
1144 AND psa_trx_dist.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
1145 AND nvl(ctlgd.amount,0) <> 0
1146 AND ctlgd.posting_control_id = l_pst_ctrl_id
1147 AND nvl(psa_trx_dist.posting_control_id, -3) = -3;
1148
1149 -- ========================= FND LOG ===========================
1150 l_full_path VARCHAR2(100) := g_path || 'Mfar_trx_to_gl';
1151 -- ========================= FND LOG ===========================
1152
1153 BEGIN
1154
1155 -- ========================= FND LOG ===========================
1156 psa_utils.debug_other_string(g_state_level,l_full_path,
1157 ' ');
1158 psa_utils.debug_other_string(g_state_level,l_full_path,
1159 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' );
1160 psa_utils.debug_other_string(g_state_level,l_full_path,
1161 ' (TRANSFERRING TRANSACTIONS) '
1162 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
1163 psa_utils.debug_other_string(g_state_level,l_full_path,
1164 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
1165 psa_utils.debug_other_string(g_state_level,l_full_path,
1166 ' ');
1167 -- ========================= FND LOG ===========================
1168
1169 retcode := 'S';
1170
1171 l_gl_start_date := p_gl_date_from;
1172 l_post_through_date := p_gl_date_to;
1173 l_sob_id := p_set_of_books_id;
1174
1175 -- ========================= FND LOG ===========================
1176 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
1177 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
1178 psa_utils.debug_other_string(g_state_level,l_full_path,' p_gl_date_from -->' || p_gl_date_from );
1179 psa_utils.debug_other_string(g_state_level,l_full_path,' p_gl_date_to -->' || p_gl_date_to );
1180 psa_utils.debug_other_string(g_state_level,l_full_path,' p_summary_flag -->' || p_summary_flag);
1181 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id -->' || p_set_of_books_id );
1182 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
1183 psa_utils.debug_other_string(g_state_level,l_full_path,' OTHER VALUES :');
1184 psa_utils.debug_other_string(g_state_level,l_full_path,' ============= ');
1185 psa_utils.debug_other_string(g_state_level,l_full_path,' GL start date -->' || l_gl_start_date);
1186 psa_utils.debug_other_string(g_state_level,l_full_path,' Post through_date -->' || l_post_through_date);
1187 psa_utils.debug_other_string(g_state_level,l_full_path,' Set of Books ID -->' || l_sob_id);
1188 psa_utils.debug_other_string(g_state_level,l_full_path,' Posting Control ID -->' || l_pst_ctrl_id);
1189 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
1190 psa_utils.debug_other_string(g_state_level,l_full_path,' PROCESS :');
1191 psa_utils.debug_other_string(g_state_level,l_full_path,' =========');
1192 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
1193 -- ========================= FND LOG ===========================
1194
1195 FOR I IN Cur_MFAR_cust_trx_id
1196 LOOP
1197
1198 BEGIN
1199 -- ========================= FND LOG ===========================
1200 psa_utils.debug_other_string(g_state_level,l_full_path,
1201 ' --> Creating distribution for Cust trx id ==> '
1202 || l_run_num || ' -- ' || I.customer_trx_id );
1203 -- ========================= FND LOG ===========================
1204
1205 IF NOT (PSA_MFAR_TRANSACTIONS.create_distributions (
1206 errbuf => l_errbuf,
1207 retcode => l_retcode,
1208 p_cust_trx_id => I.customer_trx_id,
1209 p_set_of_books_id => l_sob_id,
1210 p_run_id => l_run_num,
1211 p_error_message => l_error_message)) THEN
1212
1213 -- ========================= FND LOG ===========================
1214 psa_utils.debug_other_string(g_excep_level,l_full_path,
1215 ' --> PSA_MFAR_TRANSACTIONS.create_distributions --> FALSE');
1216 -- ========================= FND LOG ===========================
1217 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
1218 -- ========================= FND LOG ===========================
1219 psa_utils.debug_other_string(g_excep_level,l_full_path,
1220 ' --> Raising invalid_distribution');
1221 -- ========================= FND LOG ===========================
1222 Raise invalid_distribution;
1223 END IF;
1224
1225 ELSE
1226 -- ========================= FND LOG ===========================
1227 psa_utils.debug_other_string(g_state_level,l_full_path,
1228 ' --> Customer trx id --> ' || I.customer_trx_id);
1229 -- ========================= FND LOG ===========================
1230 END IF;
1231 END;
1232
1233 --
1234 -- Delete core receivables account line (INV_REC) from gl_interface
1235 -- when profile FV: Post Detailed Receipt Accounting = 'N'
1236 --
1237
1238 -- ========================= FND LOG ===========================
1239 psa_utils.debug_other_string(g_state_level,l_full_path,
1240 ' --> l_post_det_acct_flag ==> ' || l_post_det_acct_flag );
1241 -- ========================= FND LOG ===========================
1242
1243 IF l_post_det_acct_flag = 'N' THEN
1244
1245 DELETE FROM GL_INTERFACE GI
1246 WHERE GI.user_je_source_name = 'Receivables'
1247 AND GI.set_of_books_id = l_sob_id
1248 AND GI.group_id = l_pst_ctrl_id
1249 AND GI.reference29 IN ('INV_REC', 'DM_REC')
1250 AND GI.reference30 = 'RA_CUST_TRX_LINE_GL_DIST'
1251 AND GI.reference22 = to_char(I.customer_trx_id);
1252
1253 -- ========================= FND LOG ===========================
1254 psa_utils.debug_other_string(g_state_level,l_full_path,
1255 ' --> DELETE FROM GL_INTERFACE ' || SQL%ROWCOUNT);
1256 -- ========================= FND LOG ===========================
1257
1258 END IF;
1259
1260 FOR J IN Cur_MFAR_trx_lines (I.customer_trx_id)
1261 LOOP
1262
1263 /*
1264 ## For each Cutomer trax id the record will be inserted.
1265 */
1266
1267 -- ========================= FND LOG ===========================
1268 psa_utils.debug_other_string(g_state_level,l_full_path,
1269 ' --> Customer trx id ==> ' || I.customer_trx_id );
1270 -- ========================= FND LOG ===========================
1271
1272
1273 INSERT INTO gl_interface
1274 (created_by,
1275 date_created,
1276 status,
1277 actual_flag,
1278 group_id,
1279 set_of_books_id,
1280 user_je_source_name,
1281 user_je_category_name,
1282 accounting_date,
1283 subledger_doc_sequence_id,
1284 subledger_doc_sequence_value,
1285 currency_code,
1286 code_combination_id,
1287 entered_dr,
1288 entered_cr,
1289 accounted_dr,
1290 accounted_cr,
1291 reference1,
1292 reference10,
1293 reference21,
1294 reference22,
1295 reference23,
1296 reference24,
1297 reference25,
1298 reference26,
1299 reference27,
1300 reference28,
1301 reference29,
1302 reference30)
1303 VALUES (l_user_id,
1304 trunc(sysdate),
1305 l_status,
1306 l_actual_flag,
1307 l_pst_ctrl_id,
1308 l_sob_id,
1309 l_source,
1310 J.category,
1311 J.gl_date,
1312 J.doc_seqid,
1313 J.doc_num,
1314 J.currency,
1315 J.ccid,
1316 J.entered_dr,
1317 J.entered_cr,
1318 J.accounted_dr,
1319 J.accounted_cr,
1320 J.ref1,
1321 J.ref10,
1322 J.ref21,
1323 J.ref22,
1324 J.ref23,
1325 J.ref24,
1326 J.ref25,
1327 J.ref26,
1328 J.ref27,
1329 J.ref28,
1330 J.ref29,
1331 J.ref30);
1332
1333 IF (SQL%FOUND) THEN
1334 -- ========================= FND LOG ===========================
1335 psa_utils.debug_other_string(g_state_level,l_full_path,
1336 ' --> Inserting into GL INTERFACE for ==> '
1337 || I.customer_trx_id );
1338 psa_utils.debug_other_string(g_state_level,l_full_path,
1339 ' CCID => ' || J.ccid
1340 || ' DEBIT => ' || J.entered_dr
1341 || ' CREDIT => ' || J.entered_cr );
1342 -- ========================= FND LOG ===========================
1343 END IF;
1344
1345 END LOOP;
1346
1347 END LOOP;
1348
1349
1350 -- ========================= FND LOG ===========================
1351 psa_utils.debug_other_string(g_state_level,l_full_path,
1352 ' --> updating psa_mf_trx_dist_all with posting control id ');
1353 -- ========================= FND LOG ===========================
1354
1355 UPDATE psa_mf_trx_dist_all ptda
1356 SET ptda.posting_control_id = l_pst_ctrl_id
1357 WHERE ptda.cust_trx_line_gl_dist_id IN
1358 (SELECT cust_trx_line_gl_dist_id FROM ra_cust_trx_line_gl_dist rct
1359 WHERE rct.posting_control_id = l_pst_ctrl_id);
1360
1361 IF (SQL%FOUND) THEN
1362 -- ========================= FND LOG ===========================
1363 psa_utils.debug_other_string(g_state_level,l_full_path,
1364 ' --> (PSA_MF_TRX_DIST_ALL) Updated Posting control id for '
1365 || SQL%ROWCOUNT);
1366 -- ========================= FND LOG ===========================
1367 END IF;
1368
1369 -- Bug 3671841, making the call to delete stray records.
1370
1371 DELETE FROM psa_mf_trx_dist_all
1372 WHERE posting_control_id = l_pst_ctrl_id
1373 AND cust_trx_line_gl_dist_id NOT IN
1374 ( SELECT cust_trx_line_gl_dist_id FROM ra_cust_trx_line_gl_dist rct
1375 WHERE rct.posting_control_id = l_pst_ctrl_id);
1376
1377 -- ========================= FND LOG ===========================
1378 psa_utils.debug_other_string(g_state_level,l_full_path,
1379 ' --> DELETE FROM psa_mf_trx_dist_all ==> ' || SQL%ROWCOUNT);
1380 psa_utils.debug_other_string(g_state_level,l_full_path,
1381 ' --> END of TRANSACTIONS '
1382 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
1383 -- ========================= FND LOG ===========================
1384
1385 EXCEPTION
1386 WHEN INVALID_DISTRIBUTION THEN
1387 -- ========================= FND LOG ===========================
1388 psa_utils.debug_other_string(g_excep_level,l_full_path,
1389 ' --> EXCEPTION - INVALID_DISTRIBUTION raised during PSA_XFR_TO_GL_PKG.Mfar_trx_to_gl ');
1390 psa_utils.debug_other_string(g_excep_level,l_full_path,
1391 ' --> p_error_message --> ' || l_error_message);
1392 -- ========================= FND LOG ===========================
1393 retcode := 'F';
1394
1395 WHEN OTHERS THEN
1396 -- ========================= FND LOG ===========================
1397 psa_utils.debug_other_string(g_excep_level,l_full_path,
1398 ' --> EXCEPTION - OTHERS raised during PSA_XFR_TO_GL_PKG.Mfar_trx_to_gl ');
1399 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
1400 psa_utils.debug_unexpected_msg(l_full_path);
1401 -- ========================= FND LOG ===========================
1402 errbuf := 2;
1403 retcode := 'F';
1404
1405 END Mfar_trx_to_gl;
1406
1407 /* ################################## MFAR_RCTS_TO_GL ################################## */
1408
1409 /* Bug 3117907 - Create journal lines in gl_interface for on A/c credit memo
1410 cur_mfar_rcpt_lines_cm : creates reversal or CM's revenue A/c
1411 creates MFAR revenue account to match the invoice applied.
1412 The following entries created:
1413 1. Reverse On A/c credit memo's revenue Account
1414 2. Reassign the amount to Multi-fund revenue accounts */
1415
1416 PROCEDURE Mfar_rcpt_to_gl (errbuf OUT NOCOPY VARCHAR2,
1417 retcode OUT NOCOPY VARCHAR2,
1418 p_set_of_books_id IN NUMBER,
1419 p_gl_date_from IN VARCHAR2,
1420 p_gl_date_to IN VARCHAR2,
1421 p_gl_posted_date IN VARCHAR2,
1422 p_summary_flag IN VARCHAR2)
1423 IS
1424
1425 /*
1426 ## This procedure will transfer Receipts to GL INTERFACE.
1427 */
1428
1429 /* The l_ variables used IN this cursor are Global variables in this package */
1430
1431 -- use c_crh_posted to identify the number of transactions that have been posted.
1432 -- Loop the GL_INTERFACE insertion for each record in HISTORY table
1433 -- This helps us create accounting lines for each status change of the receipt.
1434
1435 Cursor c_crh_posted
1436 IS
1437 SELECT h1.cash_receipt_history_id, h1.status
1438 FROM ar_cash_receipt_history h1,
1439 ar_cash_receipt_history h2
1440 WHERE h1.posting_control_id = l_pst_ctrl_id
1441 AND h1.cash_receipt_history_id = h2.reversal_cash_receipt_hist_id
1442 ORDER BY h1.cash_receipt_history_id ;
1443
1444 CURSOR Cur_MFAR_rct_app_id
1445 IS
1446 SELECT distinct ra.receivable_application_id receivable_application_id
1447 FROM ar_receivable_applications ra
1448 WHERE ra.status = 'APP'
1449 AND ra.posting_control_id = l_pst_ctrl_id
1450 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check
1451 (ra.receivable_application_id, 'RCT', l_sob_id) , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE';
1452
1453 -- selects app_id if original cash receipt status had been reversed.
1454 CURSOR Cur_Clr_MFAR_rct_app_id
1455 IS
1456 SELECT distinct ra.receivable_application_id receivable_application_id
1457 FROM ar_receivable_applications ra, ar_cash_receipt_history crh, ar_cash_receipt_history crho
1458 WHERE crh.posting_control_id = l_pst_ctrl_id
1459 AND crh.cash_receipt_history_id = crho.reversal_cash_receipt_hist_id
1460 AND crh.cash_receipt_id = ra.cash_receipt_id
1461 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check
1462 (ra.receivable_application_id, 'RCT', p_set_of_books_id) , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE'
1463 AND ra.status = 'APP';
1464
1465 --
1466 -- Bug 2784180
1467 -- Added ussgl_transaction_code to cursor cur_mfar_rct_lines and subsequent insert to gl_interface.
1468 --
1469
1470 /*
1471 ###############################################################################
1472 Cash management Enhancements:
1473 ----------------------------------
1474 CURSOR mfar_rcpt_lines will insert thw following categories of records in gl_interface
1475 If Payment Method has staus = 'CLEARED' ( No Remittance involved in Receipt processing)
1476 1. MFAR Receivable Account
1477 2. MFAR Reversal of Core Receivable Account
1478 3. MFAR Cash Account
1479 4. MFAR Reversal of Core Cash Account ( derived from Transaction Dist A/c)
1480 (OR)
1481 If Payment Method has staus = 'REMITTED' ( Remittance in Receipt processing - Receipt cleared through Cash Mgt.)
1482 1. MFAR Receivable Account
1483 2. MFAR Reversal of Core Receivable Account
1484 3. MFAR Remittance Account
1485 4. MFAR reversal of Core Remittance Account ( derived from Transaction Dist A/c)
1486
1487 In AR_CASH_RECEIPT_HISTORY_ALL, account_code_combination_id stores remittance account if status = 'REMITTED'
1488 account_code_combination_id stores Cash account if status = 'CLEARED'
1489 For MFAR Entries, the description should be appropriately changed based on Remittance requirement.
1490 ###############################################################################
1491 */
1492
1493 CURSOR Cur_mfar_rct_lines (p_receivable_application_id NUMBER)
1494 IS
1495 SELECT
1496 DECODE(to_number(l1.lookup_code), '4', ra.ussgl_transaction_code,
1497 '8', ra.ussgl_transaction_code,
1498 '12', ra.ussgl_transaction_code, NULL) ussgl,
1499 DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name),
1500 'CM', l_cm_cat_name ) category,
1501 ra.gl_date gl_date,
1502 DECODE (ra.application_type, 'CASH', cr.doc_sequence_id,
1503 'CM', ctcm.doc_sequence_id) doc_seqid,
1504 DECODE (ra.application_type, 'CASH', cr.doc_sequence_value,
1505 'CM', ctcm.doc_sequence_value) doc_num,
1506 DECODE (ra.application_type, 'CASH', DECODE(ra.status, 'APP',
1507 DECODE( SUBSTR(ard.source_type,1,5),
1508 'EXCH_', DECODE (cr.currency_code, l_func_curr, ctinv.invoice_currency_code, cr.currency_code), ctinv.invoice_currency_code), cr.currency_code),
1509 'CM', ctcm.invoice_currency_code) currency,
1510 DECODE (to_NUMBER(l1.lookup_code), 1, psa_rct_dist.mf_cash_ccid,
1511 2, DECODE(ra.application_type, 'CM', psa_mfar_utils.get_rec_ccid (ra.applied_customer_trx_id, ra.customer_trx_id), crh.account_code_combINation_id),
1512 3, ra.code_combINation_id,
1513 4, psa_trx_dist.mf_receivables_ccid,
1514 5, psa_rct_dist.discount_ccid,
1515 6, ra.earned_discount_ccid,
1516 7, ra.code_combINation_id,
1517 8, psa_trx_dist.mf_receivables_ccid,
1518 9, psa_rct_dist.ue_discount_ccid,
1519 10, ra.unearned_discount_ccid,
1520 11, ra.code_combINation_id,
1521 12, psa_trx_dist.mf_receivables_ccid) ccid,
1522 DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
1523 get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
1524 psa_rct_dist.discount_amount,
1525 psa_rct_dist.ue_discount_amount)) entered_dr,
1526 DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
1527 get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
1528 psa_rct_dist.discount_amount,
1529 psa_rct_dist.ue_discount_amount)) entered_cr,
1530 DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
1531 get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
1532 psa_rct_dist.discount_amount,
1533 psa_rct_dist.ue_discount_amount)) accounted_dr,
1534 DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
1535 get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
1536 psa_rct_dist.discount_amount,
1537 psa_rct_dist.ue_discount_amount)) accounted_cr,
1538 DECODE(ard.source_type, 'EXCH_GAIN', TO_CHAR(ra.code_combINation_id),
1539 'EXCH_LOSS', TO_CHAR(ra.code_combINation_id),
1540 'CURR_ROUND', TO_CHAR(ra.code_combINation_id),
1541 l_batch_prefix || TO_CHAR(l_pst_ctrl_id)) ref1,
1542 SUBSTRB (DECODE (l1.lookup_code, '1', DECODE (ra.application_type, 'CM', 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Receivable account for credit memo' || ctcm.trx_NUMBER || '.',
1543 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status,'CLEARED', 'Cash Account for ', 'REMITTED', ' Remittance Account for ')),
1544 '2', DECODE (ra.application_type, 'CM',
1545 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Receivable account for credit memo '|| ctcm.trx_NUMBER || '.',
1546 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status, 'CLEARED', ' Reversal of Cash Account for ' , 'REMITTED', ' Reversal of Remittance Account for ')),
1547 '3', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Reversal of AR for ',
1548 '4', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Receivable Account for ',
1549 '5', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct earn-disc): ',
1550 '6', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core earn-disc):' ,
1551 '7', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):' ,
1552 '8', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' ,
1553 '9', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct unearn-disc):',
1554 '10', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core unearn-disc):',
1555 '11', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):',
1556 '12', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' )
1557 || DECODE (l_summary_flag, 'Y', NULL, DECODE(ra.application_type,
1558 /* Cash Receipt application */
1559 'CASH', DECODE (ard.source_type, 'REC', l_pre_tradeapp ||' '|| cr.receipt_NUMBER ||
1560 DECODE (ra.status, 'ACC', l_app_onacc,
1561 'UNAPP', l_app_unapp,
1562 'UNID', l_app_unid,
1563 'APP', l_app_applied, NULL),
1564 'EDISC', l_pre_erdisc ||' '|| cr.receipt_NUMBER || l_app_applied,
1565 'EDISC_NON_REC_TAX', l_pre_rec_erdisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
1566 'UNEDISC', l_pre_undisc ||' '|| cr.receipt_NUMBER || l_app_applied,
1567 'UNEDISC_NON_REC_TAX', l_pre_rec_undisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
1568 'EXCH_GAIN', l_pre_rec_gain ||' '|| cr.receipt_NUMBER || l_app_applied,
1569 'EXCH_LOSS', l_pre_rec_loss ||' '|| cr.receipt_NUMBER || l_app_applied,
1570 'CURR_ROUND', l_pre_rec_curr_round ||' '|| cr.receipt_NUMBER || l_app_applied,
1571 'TAX', l_pre_rec_tax ||' '|| cr.receipt_NUMBER || l_app_applied,
1572 'DEFERRED_TAX', l_pre_rec_deftax ||' '|| cr.receipt_NUMBER || l_app_applied) ||
1573 DECODE(ctt.type, 'CB', l_class_cb,
1574 'CM', l_class_cm,
1575 'DEP', l_class_dep,
1576 'DM', l_class_dm,
1577 'GUAR',l_class_guar,
1578 'INV', l_class_inv,NULL) ||
1579 ' ' || ctinv.trx_NUMBER || l_post_general)),1,240) ref10,
1580 TO_CHAR(l_pst_ctrl_id) ref21,
1581 DECODE (ra.application_type, 'CASH',TO_CHAR(cr.cash_receipt_id) || 'C' || TO_CHAR(ra.receivable_application_id),
1582 'CM', TO_CHAR(ra.receivable_application_id)) ref22,
1583 -- psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
1584 DECODE(ra.application_type, 'CASH', ard.line_id,
1585 'CM', psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
1586 -- nvl(ard.line_id, psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
1587 DECODE (ra.application_type, 'CASH', cr.receipt_NUMBER,
1588 'CM', ctcm.trx_NUMBER) ref24,
1589 ctinv.trx_NUMBER ref25,
1590 ctt.type ref26,
1591 DECODE (ra.application_type, 'CASH', TO_CHAR(cr.pay_from_customer),
1592 'CM', TO_CHAR(ctcm.bill_to_customer_id)) ref27,
1593 DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR'),
1594 'CM', 'CM') ref28,
1595 DECODE(ra.application_type, 'CASH', DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ard.source_type, 'CCURR_' ||ard.source_type),
1596 'CM', 'CM_'||ard.source_type) ref29,
1597 DECODE(ra.application_type, 'CASH', 'PSA_RCT_DIST',
1598 'CM', 'RA_CUST_TRX_LINE_GL_DIST') ref30
1599 FROM
1600 ar_receivable_applications ra,
1601 ar_cash_receipts cr,
1602 (SELECT * FROM ar_distributions
1603 WHERE source_table = 'RA'
1604 AND source_id = p_receivable_application_id
1605 AND rownum = 1) ard,
1606 ra_customer_trx ctcm,
1607 ra_customer_trx ctinv,
1608 ra_cust_trx_types ctt,
1609 ar_cash_receipt_history crh,
1610 psa_mf_rct_dist_all psa_rct_dist,
1611 psa_mf_trx_dist_all psa_trx_dist,
1612 psa_lookup_codes l1
1613 WHERE
1614 psa_rct_dist.receivable_application_id = p_receivable_application_id
1615 AND psa_rct_dist.ue_discount_ccid IS NULL
1616 AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
1617 AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
1618 /* For MFAR we consider only thr APP rows */
1619 AND 'APP' = DECODE(ra.application_type, 'CASH',ra.status, 'CM','APP')
1620 AND ra.cash_receipt_id = cr.cash_receipt_id(+)
1621 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1622 AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
1623 AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
1624 AND ra.cash_receipt_id = crh.cash_receipt_id(+)
1625 AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
1626 AND l1.lookup_code IN ('1','4','5','7','8','9','12',
1627 decode(l_rct_post_det_flag, 'N', -1, 2),
1628 decode(l_rct_post_det_flag, 'N', -1, 3),
1629 decode(l_rct_post_det_flag, 'N', -1, 6),
1630 decode(l_rct_post_det_flag, 'N', -1, 10),
1631 decode(l_rct_post_det_flag, 'N', -1, 11))
1632 AND DECODE (ceil(to_NUMBER(l1.lookup_code)/4), 1, nvl(psa_rct_dist.amount,0),
1633 2, nvl(psa_rct_dist.discount_amount,0),
1634 3, nvl(psa_rct_dist.ue_discount_amount,0), 0) <> 0
1635 AND l1.lookup_code <= DECODE(ra.application_type, 'CM', 2, l1.lookup_code)
1636 AND ra.posting_control_id = l_pst_ctrl_id
1637 AND nvl(psa_rct_dist.posting_control_id, -3) = -3
1638 /* For bug 3397563, NVL in case there is no crh record */
1639 AND NVL(crh.status, 'CLEARED') IN ('CLEARED','REMITTED')
1640 AND NVL(crh.first_posted_record_flag,'Y') = 'Y';
1641
1642
1643 -- Cursor to process journal lines related to On A/c credit memo
1644 -- For each revenue distribution on the Invoice applied, a pair of journal lines are created
1645 -- All the journal lines created will have category = 'Credit Memos' and they should be tied to
1646 -- the AR batch holding all journal lines with category = 'Credit Memos'
1647
1648 CURSOR Cur_mfar_rct_lines_cm (p_receivable_application_id NUMBER)
1649 IS
1650 SELECT
1651 DECODE(to_number(l1.lookup_code), '4', ra.ussgl_transaction_code,
1652 '8', ra.ussgl_transaction_code,
1653 '12', ra.ussgl_transaction_code, NULL) ussgl,
1654 DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name),
1655 'CM', l_cm_cat_name ) category,
1656 ra.gl_date gl_date,
1657 ctcm.doc_sequence_id doc_seqid,
1658 ctcm.doc_sequence_value doc_num,
1659 ctcm.invoice_currency_code currency,
1660 DECODE (to_NUMBER(l1.lookup_code), 1, gld_inv.code_combination_id,
1661 2, gld.code_combination_id) ccid,
1662 get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount) entered_dr,
1663 get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount) entered_cr,
1664 get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount) accounted_dr,
1665 get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount) accounted_cr,
1666 DECODE(ard.source_type, 'EXCH_GAIN', TO_CHAR(ra.code_combINation_id),
1667 'EXCH_LOSS', TO_CHAR(ra.code_combINation_id),
1668 'CURR_ROUND', TO_CHAR(ra.code_combINation_id),
1669 l_batch_prefix || TO_CHAR(l_pst_ctrl_id)) ref1,
1670 SUBSTRB (DECODE (l1.lookup_code, '1', 'Revenue account for Credit Memo' || ctcm.trx_NUMBER || '.',
1671 '2', 'MFAR Reversal of Revenue account for credit memo '|| ctcm.trx_NUMBER || '.'),1,240) ref10,
1672 TO_CHAR(l_pst_ctrl_id) ref21,
1673 TO_CHAR(ra.receivable_application_id) ref22,
1674 -- psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
1675 -- nvl(ard.line_id, psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
1676 DECODE(ra.application_type, 'CASH', ard.line_id,
1677 'CM', psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
1678 ctcm.trx_NUMBER ref24,
1679 ctinv.trx_NUMBER ref25,
1680 ctt.type ref26,
1681 TO_CHAR(ctcm.bill_to_customer_id) ref27,
1682 DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR'),
1683 'CM', 'CMAPP') ref28,
1684 DECODE(ra.application_type, 'CASH', DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ard.source_type, 'CCURR_' ||ard.source_type),
1685 'CM', 'CMAPP_'||ard.source_type) ref29,
1686 -- 'PSA_RCT_DIST' ref30
1687 DECODE(ra.application_type, 'CASH', 'PSA_RCT_DIST',
1688 'CM', 'RA_CUST_TRX_LINE_GL_DIST') ref30
1689 FROM
1690 ar_receivable_applications ra,
1691 (SELECT * FROM ar_distributions
1692 WHERE source_table = 'RA'
1693 AND source_id = p_receivable_application_id
1694 AND rownum = 1) ard,
1695 ra_customer_trx ctcm,
1696 ra_cust_trx_line_gl_dist gld,
1697 ra_cust_trx_line_gl_dist gld_inv,
1698 ra_customer_trx ctinv,
1699 ra_cust_trx_types ctt,
1700 ar_cash_receipt_history crh,
1701 psa_mf_rct_dist_all psa_rct_dist,
1702 psa_mf_trx_dist_all psa_trx_dist,
1703 psa_lookup_codes l1
1704 WHERE
1705 psa_rct_dist.receivable_application_id = p_receivable_application_id
1706 AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
1707 AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
1708 AND psa_rct_dist.cust_trx_line_gl_dist_id = gld_inv.cust_trx_line_gl_dist_id
1709 AND gld_inv.ACCOUNT_class = 'REV' /* For MFAR we consider only thr APP rows */
1710 AND 'APP' = DECODE(ra.application_type, 'CASH',ra.status, 'CM','APP')
1711 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1712 /* Bug 3397563, check for On Account Credit Memo */
1713 AND ctcm.previous_customer_trx_id IS NULL
1714 AND ctcm.customer_trx_id = gld.customer_trx_id
1715 AND gld.account_class = 'REV'
1716 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
1717 AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
1718 AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
1719 AND ra.cash_receipt_id = crh.cash_receipt_id(+)
1720 AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
1721 AND l1.lookup_code IN ('1','2')
1722 AND ra.posting_control_id = l_pst_ctrl_id
1723 AND nvl(psa_rct_dist.posting_control_id, -3) = -3
1724 AND crh.status(+) = 'CLEARED';
1725
1726
1727 /* ###############################################################################
1728 This cursor will take care of MFAR Entries after a Receipt has been cleared from Cash Management.
1729 1. Reverse MFAR Remittance A/c
1730 2. Reverse 'Reversal of Core Remittance Account
1731 3. MFAR Cash Account
1732 4. Reversal Core Cash Account
1733 ###############################################################################
1734 */
1735
1736 CURSOR Cur_clr_mfar_rct_lines (p_receivable_application_id NUMBER, p_crhid IN number)
1737 IS
1738 SELECT
1739 DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name) category,
1740 crhnew.gl_date gl_date,
1741 cr.doc_sequence_id doc_seqid,
1742 cr.doc_sequence_value doc_num,
1743 crhnew.status newstatus,
1744 crhold.status oldstatus,
1745 DECODE(ra.status, 'APP', DECODE( SUBSTR(ard.source_type,1,5),
1746 'EXCH_', DECODE (cr.currency_code, l_func_curr, ctinv.invoice_currency_code, cr.currency_code),
1747 ctinv.invoice_currency_code), cr.currency_code) currency,
1748 DECODE (to_NUMBER(l1.lookup_code), 1, psa_rct_dist.ue_discount_ccid, -- mfar remittance (CR)-- check remittance/cash
1749 2, decode(crhnew.status,'REMITTED',crhnew.account_code_combination_id,crhold.account_code_combination_id), -- Core Remittance (DB)
1750 3, decode(crhnew.status,'REMITTED',crhold.account_code_combination_id,crhnew.account_code_combination_id), -- Core Cash (CR)
1751 4, decode(psa_rct_dist.attribute1,'CLEARED',psa_rct_dist.mf_cash_ccid)) -- MFAR Cash (DB)
1752 ccid,
1753 get_entered_dr_rct_clear(l1.lookup_code, psa_rct_dist.amount,crhnew.status,crhold.status) entered_dr,
1754 get_entered_cr_rct_clear(l1.lookup_code, psa_rct_dist.amount,crhnew.status,crhold.status) entered_cr,
1755 get_entered_dr_rct_clear(to_number(l1.lookup_code), psa_rct_dist.amount,crhnew.status,crhold.status) accounted_dr,
1756 get_entered_cr_rct_clear(l1.lookup_code, psa_rct_dist.amount,crhnew.status,crhold.status) accounted_cr,
1757 DECODE(ard.source_type, 'EXCH_GAIN', TO_CHAR(ra.code_combINation_id),
1758 'EXCH_LOSS', TO_CHAR(ra.code_combINation_id),
1759 'CURR_ROUND', TO_CHAR(ra.code_combINation_id),
1760 l_batch_prefix || TO_CHAR(l_pst_ctrl_id)) ref1,
1761 SUBSTRB (DECODE (l1.lookup_code, '1', 'CSH MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || 'Remittance Reversal for ',
1762 '2', 'CSH MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || 'Reversal of Core Remittance for ',
1763 '3', 'CSH MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Core Cash A/c ',
1764 '4', 'CSH MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Cash Account for ',
1765 '5', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct earn-disc): ',
1766 '6', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core earn-disc):' ,
1767 '7', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):' ,
1768 '8', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' ,
1769 '9', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct unearn-disc):',
1770 '10', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core unearn-disc):',
1771 '11', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):',
1772 '12', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' )
1773 || DECODE (l_summary_flag, 'Y', NULL, DECODE(ra.application_type,
1774 /* Cash Receipt application */
1775 'CASH', DECODE (ard.source_type, 'REC', l_pre_tradeapp ||' '|| cr.receipt_NUMBER ||
1776 DECODE (ra.status, 'ACC', l_app_onacc,
1777 'UNAPP', l_app_unapp,
1778 'UNID', l_app_unid,
1779 'APP', l_app_applied, NULL),
1780 'EDISC', l_pre_erdisc ||' '|| cr.receipt_NUMBER || l_app_applied,
1781 'EDISC_NON_REC_TAX', l_pre_rec_erdisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
1782 'UNEDISC', l_pre_undisc ||' '|| cr.receipt_NUMBER || l_app_applied,
1783 'UNEDISC_NON_REC_TAX', l_pre_rec_undisc_nrtax ||' '|| cr.receipt_NUMBER || l_app_applied,
1784 'EXCH_GAIN', l_pre_rec_gain ||' '|| cr.receipt_NUMBER || l_app_applied,
1785 'EXCH_LOSS', l_pre_rec_loss ||' '|| cr.receipt_NUMBER || l_app_applied,
1786 'CURR_ROUND', l_pre_rec_curr_round ||' '|| cr.receipt_NUMBER || l_app_applied,
1787 'TAX', l_pre_rec_tax ||' '|| cr.receipt_NUMBER || l_app_applied,
1788 'DEFERRED_TAX', l_pre_rec_deftax ||' '|| cr.receipt_NUMBER || l_app_applied) ||
1789 DECODE(ctt.type, 'CB', l_class_cb,
1790 'CM', l_class_cm,
1791 'DEP', l_class_dep,
1792 'DM', l_class_dm,
1793 'GUAR',l_class_guar,
1794 'INV', l_class_inv,NULL) ||
1795 ' ' || ctinv.trx_NUMBER || l_post_general)),1,240)
1796 ref10,
1797 TO_CHAR(l_pst_ctrl_id) ref21,
1798 TO_CHAR(cr.cash_receipt_id) || 'C' || TO_CHAR(ra.receivable_application_id) ref22,
1799 -- psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
1800 nvl(ard.line_id, psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID) ref23,
1801 cr.receipt_NUMBER ref24,
1802 ctinv.trx_NUMBER ref25,
1803 ctt.type ref26,
1804 to_char(cr.pay_from_customer) ref27,
1805 DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR') ref28,
1806 DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ard.source_type, 'CCURR_' ||ard.source_type)
1807 ref29,
1808 'PSA_RCT_DIST' ref30
1809 FROM
1810 ar_receivable_applications ra,
1811 ar_cash_receipts cr,
1812 (SELECT * FROM ar_distributions
1813 WHERE source_table = 'RA'
1814 AND source_id = p_receivable_application_id
1815 AND rownum = 1) ard,
1816 ra_customer_trx ctinv,
1817 ra_cust_trx_types ctt,
1818 ar_cash_receipt_history crhnew,
1819 ar_cash_receipt_history crhold,
1820 psa_mf_rct_dist_all psa_rct_dist,
1821 psa_mf_trx_dist_all psa_trx_dist,
1822 psa_lookup_codes l1
1823 WHERE
1824 psa_rct_dist.receivable_application_id = p_receivable_application_id
1825 AND psa_rct_dist.ue_discount_ccid IS NOT NULL
1826 AND psa_rct_dist.attribute1 = 'CLEARED'
1827 AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
1828 AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
1829 /* For MFAR we consider only thr APP rows */
1830 AND 'APP' = DECODE(ra.application_type, 'CASH',ra.status, 'CM','APP')
1831 AND ra.cash_receipt_id = cr.cash_receipt_id(+)
1832 AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
1833 AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
1834 AND ra.cash_receipt_id = crhnew.cash_receipt_id --outer joinremoved
1835 AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
1836 AND l1.lookup_code IN ('1','2','3','4') --,'5','6','7','8','9','10','11','12')
1837 AND crhnew.posting_control_id = l_pst_ctrl_id
1838 AND crhnew.cash_receipt_history_id = p_crhid
1839 AND crhold.reversal_cash_receipt_hist_id = crhnew.cash_receipt_history_id
1840 AND nvl(crhnew.first_posted_record_flag, 'N') = 'N';
1841
1842
1843 CURSOR c_fv_balance_check (c_sob_id NUMBER, c_group_id NUMBER, c_rcv_app_id NUMBER) IS
1844 SELECT to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) cash_receipt_id,
1845 sum(accounted_dr) sum_acctd_dr,
1846 sum(accounted_cr) sum_acctd_cr
1847 FROM gl_interface gl
1848 WHERE gl.user_je_source_name = 'Receivables'
1849 AND gl.set_of_books_id = c_sob_id
1850 AND gl.group_id = c_group_id
1851 AND substr(gl.reference29, 7) IN ('CASH', 'REC')
1852 AND gl.reference10 NOT LIKE 'MFAR%'
1853 AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) IN
1854 (select cash_receipt_id from ar_receivable_applications where receivable_application_id = c_rcv_app_id)
1855 GROUP BY to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1))
1856 HAVING sum(accounted_dr) = sum(accounted_cr);
1857
1858 l_fv_balance_check c_fv_balance_check%rowtype;
1859
1860 -- ========================= FND LOG ===========================
1861 l_full_path VARCHAR2(100) := g_path || 'mfar_rcpt_to_gl';
1862 -- ========================= FND LOG ===========================
1863
1864 BEGIN
1865
1866 retcode := 'S';
1867
1868 -- ========================= FND LOG ===========================
1869 psa_utils.debug_other_string(g_state_level,l_full_path,
1870 ' ' );
1871 psa_utils.debug_other_string(g_state_level,l_full_path,
1872 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' );
1873 psa_utils.debug_other_string(g_state_level,l_full_path,
1874 ' (TRANSFERRING RECEIPTS) '
1875 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
1876 psa_utils.debug_other_string(g_state_level,l_full_path,
1877 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' );
1878 psa_utils.debug_other_string(g_state_level,l_full_path,
1879 ' ' );
1880 -- ========================= FND LOG ===========================
1881
1882 l_gl_start_date := p_gl_date_from;
1883 l_post_through_date := p_gl_date_to;
1884 l_summary_flag := p_summary_flag;
1885 l_sob_id := p_set_of_books_id;
1886
1887 -- ========================= FND LOG ===========================
1888 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
1889 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
1890 psa_utils.debug_other_string(g_state_level,l_full_path,' p_gl_date_from -->' || p_gl_date_from );
1891 psa_utils.debug_other_string(g_state_level,l_full_path,' p_gl_date_to -->' || p_gl_date_to );
1892 psa_utils.debug_other_string(g_state_level,l_full_path,' p_summary_flag -->' || p_summary_flag );
1893 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id -->' || p_set_of_books_id);
1894 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
1895 psa_utils.debug_other_string(g_state_level,l_full_path,' OTHER VALUES :');
1896 psa_utils.debug_other_string(g_state_level,l_full_path,' ============= ');
1897 psa_utils.debug_other_string(g_state_level,l_full_path,' l_gl_start_date -->' || l_gl_start_date);
1898 psa_utils.debug_other_string(g_state_level,l_full_path,' l_post_through_date -->' || l_post_through_date);
1899 psa_utils.debug_other_string(g_state_level,l_full_path,' l_summary_flag -->' || l_summary_flag);
1900 psa_utils.debug_other_string(g_state_level,l_full_path,' l_sob_id -->' || l_sob_id);
1901 psa_utils.debug_other_string(g_state_level,l_full_path,' l_pst_ctrl_id -->' || l_pst_ctrl_id );
1902 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
1903 psa_utils.debug_other_string(g_state_level,l_full_path,' PROCESS :');
1904 psa_utils.debug_other_string(g_state_level,l_full_path,' =========');
1905 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
1906 -- ========================= FND LOG ===========================
1907
1908 -- Begin processing of Cleared Receipts - Cash Management
1909
1910 FOR K IN c_crh_posted
1911 LOOP
1912
1913 -- ========================= FND LOG ===========================
1914 psa_utils.debug_other_string(g_state_level,l_full_path,
1915 ' in to cursor c_crh_posted ');
1916 -- ========================= FND LOG ===========================
1917
1918 FOR I IN Cur_Clr_MFAR_rct_app_id
1919 LOOP
1920 /*
1921 ## Creating distributions for Receipts.
1922 Bug 2780195 - Before calling the API, sequence psa_mf_error_log_s is initialized
1923 */
1924
1925 -- select psa_mf_error_log_s.nextval into l_run_num from dual;
1926 -- This is now set in the initialization routine
1927
1928 BEGIN
1929 -- ========================= FND LOG ===========================
1930 psa_utils.debug_other_string(g_state_level,l_full_path,
1931 ' --> inside Cur_Clr_MFAR_rct_app_id ');
1932 psa_utils.debug_other_string(g_state_level,l_full_path,
1933 ' --> Creating distribution for receivable_application_id ==> '
1934 || l_run_num || ' -- ' || I.receivable_application_id );
1935 -- ========================= FND LOG ===========================
1936
1937 IF NOT (PSA_MFAR_RECEIPTS.create_distributions (
1938 errbuf => l_errbuf,
1939 retcode => l_retcode,
1940 p_receivable_app_id => I.receivable_application_id,
1941 p_set_of_books_id => l_sob_id,
1942 p_run_id => l_run_num,
1943 p_error_message => l_error_message)) THEN
1944
1945 -- ========================= FND LOG ===========================
1946 psa_utils.debug_other_string(g_state_level,l_full_path,
1947 ' --> PSA_MFAR_RECEIPTS.create_distributions ==> FALSE ');
1948 -- ========================= FND LOG ===========================
1949
1950 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
1951 -- ========================= FND LOG ===========================
1952 psa_utils.debug_other_string(g_excep_level,l_full_path,
1953 ' --> Raising invalid_distribution');
1954 -- ========================= FND LOG ===========================
1955 Raise invalid_distribution;
1956 END IF;
1957
1958 ELSE
1959 -- ========================= FND LOG ===========================
1960 psa_utils.debug_other_string(g_state_level,l_full_path,
1961 ' Receivable Application id --> '
1962 || I.receivable_application_id);
1963 -- ========================= FND LOG ===========================
1964 END IF;
1965 END;
1966
1967 -- ========================= FND LOG ===========================
1968 psa_utils.debug_other_string(g_state_level,l_full_path,
1969 ' --> Before cursor cur_clr_mfar_rct_lines ');
1970 psa_utils.debug_other_string(g_state_level,l_full_path,
1971 ' --> Receivable Application id ==> ' || I.receivable_application_id );
1972 psa_utils.debug_other_string(g_state_level,l_full_path,
1973 ' --> cash_receipt_history_id ==> ' || K.cash_receipt_history_id);
1974 -- ========================= FND LOG ===========================
1975
1976 FOR J IN cur_clr_mfar_rct_lines (I.receivable_application_id, K.cash_receipt_history_id)
1977 LOOP
1978
1979 -- ========================= FND LOG ===========================
1980 psa_utils.debug_other_string(g_state_level,l_full_path,
1981 ' --> inside cur_clr_mfar_rct_lines ');
1982 psa_utils.debug_other_string(g_state_level,l_full_path,
1983 ' --> J.newstatus ' || J.newstatus || ' and ' ||
1984 ' --> J.oldstatus ' || J.oldstatus );
1985 -- ========================= FND LOG ===========================
1986
1987 IF (J.newstatus = 'REVERSED') AND (J.oldstatus = 'REMITTED') THEN
1988 -- ========================= FND LOG ===========================
1989 psa_utils.debug_other_string(g_state_level,l_full_path,' --> Exiting ');
1990 -- ========================= FND LOG ===========================
1991 EXIT;
1992 END IF;
1993
1994 -- ========================= FND LOG ===========================
1995 psa_utils.debug_other_string(g_state_level,l_full_path,
1996 ' --> Inserting into gl interface ');
1997 -- ========================= FND LOG ===========================
1998
1999 INSERT INTO gl_interface
2000 (created_by,
2001 date_created,
2002 status,
2003 actual_flag,
2004 group_id,
2005 set_of_books_id,
2006 user_je_source_name,
2007 user_je_category_name,
2008 accounting_date,
2009 subledger_doc_sequence_id,
2010 subledger_doc_sequence_value,
2011 currency_code,
2012 code_combINation_id,
2013 entered_dr,
2014 entered_cr,
2015 accounted_dr,
2016 accounted_cr,
2017 reference1,
2018 reference10,
2019 reference21,
2020 reference22,
2021 reference23,
2022 reference24,
2023 reference25,
2024 reference26,
2025 reference27,
2026 reference28,
2027 reference29,
2028 reference30,
2029 ussgl_transaction_code)
2030 VALUES (l_user_id,
2031 trunc(sysdate),
2032 l_status,
2033 l_actual_flag,
2034 l_pst_ctrl_id,
2035 l_sob_id,
2036 l_source,
2037 J.category,
2038 J.gl_date,
2039 J.doc_seqid,
2040 J.doc_num,
2041 J.currency,
2042 J.ccid,
2043 J.entered_dr,
2044 J.entered_cr,
2045 J.accounted_dr,
2046 J.accounted_cr,
2047 J.ref1,
2048 J.ref10,
2049 J.ref21,
2050 J.ref22,
2051 J.ref23,
2052 J.ref24,
2053 J.ref25,
2054 J.ref26,
2055 J.ref27,
2056 J.ref28,
2057 J.ref29,
2058 J.ref30,
2059 NULL);
2060
2061 IF (SQL%FOUND) THEN
2062 -- ========================= FND LOG ===========================
2063 psa_utils.debug_other_string(g_state_level,l_full_path,
2064 ' --> Inserting into GL INTERFACE for Receipts (Cash Cleared) '||
2065 ' - Receivable Application id ==> ' || I.receivable_application_id);
2066 psa_utils.debug_other_string(g_state_level,l_full_path,
2067 ' CCID => ' || J.ccid
2068 || ' DEBIT => ' || J.entered_dr
2069 || ' CREDIT => ' || J.entered_cr );
2070 -- ========================= FND LOG ===========================
2071 END IF;
2072
2073 END LOOP;
2074
2075 END LOOP;
2076 END LOOP;
2077
2078 -- ========================= FND LOG ===========================
2079 psa_utils.debug_other_string(g_state_level,l_full_path,
2080 ' --> Calling PSA_MFAR_RECEIPTS.PURGE_ORPHAN_DISTRIBUTIONS ');
2081 -- ========================= FND LOG ===========================
2082
2083 -- Bug 3671841, issuing a call to purge orphan distributions
2084 PSA_MFAR_RECEIPTS.PURGE_ORPHAN_DISTRIBUTIONS;
2085
2086 UPDATE psa_mf_rct_dist_all pda
2087 SET pda.posting_control_id = l_pst_ctrl_id
2088 WHERE pda.attribute1 = 'CLEARED'
2089 AND pda.receivable_application_id IN
2090 (SELECT receivable_application_id FROM ar_receivable_applications ara, ar_cash_receipt_history crh
2091 WHERE ara.cash_receipt_id = crh.cash_receipt_id AND crh.status = 'CLEARED'
2092 AND crh.posting_control_id = l_pst_ctrl_id) ;
2093
2094 -- ========================= FND LOG ===========================
2095 psa_utils.debug_other_string(g_state_level,l_full_path,
2096 ' UPDATE psa_mf_rct_dist_all '|| SQL%ROWCOUNT);
2097 -- ========================= FND LOG ===========================
2098
2099 FOR I IN Cur_MFAR_rct_app_id
2100 LOOP
2101
2102 /*
2103 ## Creating distributions for Receipts.
2104 Bug 2780195 - Before calling the API, sequence psa_mf_error_log_s is initialized
2105 */
2106
2107 -- select psa_mf_error_log_s.nextval into l_run_num from dual;
2108 -- This is now set in the initialization routine.
2109
2110 BEGIN
2111 -- ========================= FND LOG ===========================
2112 psa_utils.debug_other_string(g_state_level,l_full_path,
2113 ' --> inside cursor Cur_MFAR_rct_app_id ');
2114 psa_utils.debug_other_string(g_state_level,l_full_path,
2115 ' --> Creating distribution for receivable_application_id ==> '
2116 || l_run_num || ' -- ' || I.receivable_application_id );
2117 -- ========================= FND LOG ===========================
2118
2119 IF NOT (PSA_MFAR_RECEIPTS.create_distributions (
2120 errbuf => l_errbuf,
2121 retcode => l_retcode,
2122 p_receivable_app_id => I.receivable_application_id,
2123 p_set_of_books_id => l_sob_id,
2124 p_run_id => l_run_num,
2125 p_error_message => l_error_message)) THEN
2126
2127 -- ========================= FND LOG ===========================
2128 psa_utils.debug_other_string(g_state_level,l_full_path,
2129 ' --> PSA_MFAR_RECEIPTS.create_distributions ==> FALSE ');
2130 -- ========================= FND LOG ===========================
2131
2132 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
2133 -- ========================= FND LOG ===========================
2134 psa_utils.debug_other_string(g_excep_level,l_full_path,
2135 ' --> Raising invalid_distribution');
2136 -- ========================= FND LOG ===========================
2137 Raise invalid_distribution;
2138 END IF;
2139 ELSE
2140 -- ========================= FND LOG ===========================
2141 psa_utils.debug_other_string(g_state_level,l_full_path,
2142 ' --> Receivable Application id --> '
2143 || I.receivable_application_id);
2144 -- ========================= FND LOG ===========================
2145 END IF;
2146 END;
2147
2148 -- ========================= FND LOG ===========================
2149 psa_utils.debug_other_string(g_state_level,l_full_path,
2150 ' --> l_post_det_acct_flag ==> ' || l_post_det_acct_flag );
2151 -- ========================= FND LOG ===========================
2152 /* -- Bug 4178626
2153 IF l_post_det_acct_flag = 'N' THEN
2154
2155 OPEN c_fv_balance_check (l_sob_id, l_pst_ctrl_id, I.receivable_application_id);
2156 FETCH c_fv_balance_check INTO l_fv_balance_check;
2157 CLOSE c_fv_balance_check;
2158
2159 IF (l_fv_balance_check.sum_acctd_dr IS NOT NULL AND
2160 l_fv_balance_check.sum_acctd_cr IS NOT NULL ) THEN
2161
2162 -- ========================= FND LOG ===========================
2163 psa_utils.debug_other_string(g_state_level,l_full_path,
2164 ' --> inside if ' );
2165 -- ========================= FND LOG ===========================
2166
2167 l_rct_post_det_flag := 'N';
2168
2169 DELETE FROM gl_interface gl
2170 WHERE gl.user_je_source_name = 'Receivables'
2171 AND gl.set_of_books_id = l_sob_id
2172 AND gl.group_id = l_pst_ctrl_id
2173 AND substr(gl.reference29, 7) IN ('CASH', 'REC')
2174 AND gl.reference10 NOT LIKE 'MFAR%'
2175 AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) = l_fv_balance_check.cash_receipt_id;
2176
2177 -- ========================= FND LOG ===========================
2178 psa_utils.debug_other_string(g_state_level,l_full_path,
2179 ' --> DELETE FROM gl_interface -> ' || SQL%ROWCOUNT);
2180 -- ========================= FND LOG ===========================
2181 ELSE
2182 l_rct_post_det_flag := 'Y';
2183 END IF;
2184
2185 -- ========================= FND LOG ===========================
2186 psa_utils.debug_other_string(g_state_level,l_full_path,
2187 ' --> l_rct_post_det_flag -> ' || l_rct_post_det_flag);
2188 -- ========================= FND LOG ===========================
2189 END IF;
2190 -- Bug 4178626 */
2191
2192 -- ========================= FND LOG ===========================
2193 psa_utils.debug_other_string(g_state_level,l_full_path,
2194 ' --> Before cursor Cur_mfar_rct_lines ');
2195 -- ========================= FND LOG ===========================
2196
2197 FOR J IN Cur_mfar_rct_lines (I.receivable_application_id)
2198 LOOP
2199
2200 /*
2201 ## For each receivable app id the record will be INserted.
2202 */
2203
2204 -- ========================= FND LOG ===========================
2205 psa_utils.debug_other_string(g_state_level,l_full_path,
2206 ' --> inside cursor Cur_mfar_rct_lines ');
2207 psa_utils.debug_other_string(g_state_level,l_full_path,
2208 ' --> Receivable Application id ==> ' || I.receivable_application_id);
2209 psa_utils.debug_other_string(g_state_level,l_full_path,
2210 ' --> inserting into gl_interface ');
2211 -- ========================= FND LOG ===========================
2212
2213 INSERT INTO gl_interface
2214 (created_by,
2215 date_created,
2216 status,
2217 actual_flag,
2218 group_id,
2219 set_of_books_id,
2220 user_je_source_name,
2221 user_je_category_name,
2222 accounting_date,
2223 subledger_doc_sequence_id,
2224 subledger_doc_sequence_value,
2225 currency_code,
2226 code_combINation_id,
2227 entered_dr,
2228 entered_cr,
2229 accounted_dr,
2230 accounted_cr,
2231 reference1,
2232 reference10,
2233 reference21,
2234 reference22,
2235 reference23,
2236 reference24,
2237 reference25,
2238 reference26,
2239 reference27,
2240 reference28,
2241 reference29,
2242 reference30,
2243 ussgl_transaction_code)
2244 VALUES (l_user_id,
2245 trunc(sysdate),
2246 l_status,
2247 l_actual_flag,
2248 l_pst_ctrl_id,
2249 l_sob_id,
2250 l_source,
2251 J.category,
2252 J.gl_date,
2253 J.doc_seqid,
2254 J.doc_num,
2255 J.currency,
2256 J.ccid,
2257 J.entered_dr,
2258 J.entered_cr,
2259 J.accounted_dr,
2260 J.accounted_cr,
2261 J.ref1,
2262 J.ref10,
2263 J.ref21,
2264 J.ref22,
2265 J.ref23,
2266 J.ref24,
2267 J.ref25,
2268 J.ref26,
2269 J.ref27,
2270 J.ref28,
2271 J.ref29,
2272 J.ref30,
2273 J.ussgl);
2274
2275 IF (SQL%FOUND) THEN
2276 -- ========================= FND LOG ===========================
2277 psa_utils.debug_other_string(g_state_level,l_full_path,
2278 ' --> Inserting into GL INTERFACE for Receipts ' ||
2279 ' - Receivable Application id ==> ' || I.receivable_application_id );
2280 psa_utils.debug_other_string(g_state_level,l_full_path,
2281 ' CCID => ' || J.ccid
2282 || ' DEBIT => ' || J.entered_dr
2283 || ' CREDIT => ' || J.entered_cr );
2284 -- ========================= FND LOG ===========================
2285 END IF;
2286
2287 END LOOP;
2288
2289 -- Insert accounting lines into gl_interface for on account credit memo
2290 -- ========================= FND LOG ===========================
2291 psa_utils.debug_other_string(g_state_level,l_full_path,
2292 ' --> Before cursor Cur_mfar_rct_lines_cm ');
2293 -- ========================= FND LOG ===========================
2294
2295
2296 FOR J IN Cur_mfar_rct_lines_cm (I.receivable_application_id)
2297 LOOP
2298
2299 /*
2300 ## For each receivable app id the record will be INserted.
2301 */
2302
2303 -- ========================= FND LOG ===========================
2304 psa_utils.debug_other_string(g_state_level,l_full_path,
2305 ' --> Inside cursor Cur_mfar_rct_lines_cm ');
2306 psa_utils.debug_other_string(g_state_level,l_full_path,
2307 ' --> Receivable Application id ==> ' || I.receivable_application_id );
2308 -- ========================= FND LOG ===========================
2309
2310 INSERT INTO gl_interface
2311 (created_by,
2312 date_created,
2313 status,
2314 actual_flag,
2315 group_id,
2316 set_of_books_id,
2317 user_je_source_name,
2318 user_je_category_name,
2319 accounting_date,
2320 subledger_doc_sequence_id,
2321 subledger_doc_sequence_value,
2322 currency_code,
2323 code_combINation_id,
2324 entered_dr,
2325 entered_cr,
2326 accounted_dr,
2327 accounted_cr,
2328 reference1,
2329 reference10,
2330 reference21,
2331 reference22,
2332 reference23,
2333 reference24,
2334 reference25,
2335 reference26,
2336 reference27,
2337 reference28,
2338 reference29,
2339 reference30,
2340 ussgl_transaction_code)
2341 VALUES (l_user_id,
2342 trunc(sysdate),
2343 l_status,
2344 l_actual_flag,
2345 l_pst_ctrl_id,
2346 l_sob_id,
2347 l_source,
2348 J.category,
2349 J.gl_date,
2350 J.doc_seqid,
2351 J.doc_num,
2352 J.currency,
2353 J.ccid,
2354 J.entered_dr,
2355 J.entered_cr,
2356 J.accounted_dr,
2357 J.accounted_cr,
2358 J.ref1,
2359 J.ref10,
2360 J.ref21,
2361 J.ref22,
2362 J.ref23,
2363 J.ref24,
2364 J.ref25,
2365 J.ref26,
2366 J.ref27,
2367 J.ref28,
2368 J.ref29,
2369 J.ref30,
2370 J.ussgl);
2371
2372 IF (SQL%FOUND) THEN
2373 -- ========================= FND LOG ===========================
2374 psa_utils.debug_other_string(g_state_level,l_full_path,
2375 ' --> Inserting into GL INTERFACE for Receipts ' ||
2376 ' - Receivable Application id ==> ' || I.receivable_application_id );
2377 psa_utils.debug_other_string(g_state_level,l_full_path,
2378 ' CCID => ' || J.ccid
2379 || ' DEBIT => ' || J.entered_dr
2380 || ' CREDIT => ' || J.entered_cr );
2381 -- ========================= FND LOG ===========================
2382 END IF;
2383
2384 END LOOP;
2385
2386 END LOOP;
2387
2388 -- ========================= FND LOG ===========================
2389 psa_utils.debug_other_string(g_state_level,l_full_path,
2390 ' --> Calling PSA_MFAR_RECEIPTS.PURGE_ORPHAN_DISTRIBUTIONS ');
2391 -- ========================= FND LOG ===========================
2392
2393 -- Bug 3671841, issuing a call to purge orphan distributions
2394 PSA_MFAR_RECEIPTS.PURGE_ORPHAN_DISTRIBUTIONS;
2395
2396 UPDATE psa_mf_rct_dist_all pda
2397 SET pda.posting_control_id = l_pst_ctrl_id
2398 WHERE pda.receivable_application_id IN
2399 (SELECT receivable_application_id FROM ar_receivable_applications ara
2400 WHERE ara.posting_control_id = l_pst_ctrl_id);
2401
2402 IF (SQL%FOUND) THEN
2403 -- ========================= FND LOG ===========================
2404 psa_utils.debug_other_string(g_state_level,l_full_path,
2405 ' --> (PSA_MF_RCT_DIST_ALL) Posting control id updated for '
2406 || (SQL%ROWCOUNT) );
2407 -- ========================= FND LOG ===========================
2408 END IF;
2409 -- ========================= FND LOG ===========================
2410 psa_utils.debug_other_string(g_event_level,l_full_path,
2411 ' --> End of Receipts Transfer '|| to_char(sysdate, 'DD/MM/YYYY HH:MI:SS'));
2412 -- ========================= FND LOG ===========================
2413
2414 EXCEPTION
2415 WHEN INVALID_DISTRIBUTION THEN
2416 -- ========================= FND LOG ===========================
2417 psa_utils.debug_other_string(g_excep_level,l_full_path,
2418 ' --> EXCEPTION - INVALID_DISTRIBUTION raised during PSA_XFR_TO_GL_PKG.Mfar_rct_to_gl ');
2419 psa_utils.debug_other_string(g_excep_level,l_full_path,
2420 ' --> p_error_message --> ' || l_error_message);
2421 -- ========================= FND LOG ===========================
2422 retcode := 'F';
2423
2424 WHEN OTHERS THEN
2425 -- ========================= FND LOG ===========================
2426 psa_utils.debug_other_string(g_excep_level,l_full_path,
2427 ' --> EXCEPTION - OTHERS raised during PSA_XFR_TO_GL_PKG.Mfar_rct_to_gl ');
2428 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
2429 psa_utils.debug_unexpected_msg(l_full_path);
2430 -- ========================= FND LOG ===========================
2431 errbuf := 2;
2432 retcode := 'F';
2433
2434 END Mfar_rcpt_to_gl;
2435
2436 /* ################################## MFAR_ADJ_TO_GL ################################## */
2437
2438 PROCEDURE Mfar_adj_to_gl (errbuf OUT NOCOPY VARCHAR2,
2439 retcode OUT NOCOPY VARCHAR2,
2440 p_set_of_books_id IN NUMBER,
2441 p_gl_date_from IN VARCHAR2,
2442 p_gl_date_to IN VARCHAR2,
2443 p_gl_posted_date IN VARCHAR2,
2444 p_summary_flag IN VARCHAR2)
2445 IS
2446
2447 /*
2448 ## This will transfer adjustments to gl_interface.
2449 */
2450
2451 /* The l_ variables used in this cursor are Global variables in this package */
2452 CURSOR Cur_lines_to_be_processed
2453 IS
2454 SELECT distinct adj.adjustment_id adjustment_id
2455 FROM ar_adjustments adj
2456 WHERE adj.posting_control_id = l_pst_ctrl_id
2457 AND DECODE (PSA_MFAR_VAL_PKG.ar_mfar_validate_check (adj.adjustment_id, 'ADJ', l_sob_id)
2458 , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE';
2459
2460 -- Bug 2982757
2461 -- Modified cursor to fetch adjustment ccid from ar_distributions
2462 -- New function created get_adj_ccid
2463
2464 -- Bug 3168282 (Tpradhan)
2465 -- Selected the value ussgl_transaction_code in the Cursor below. Also inserted the same value into gl_interface table.
2466
2467 CURSOR Cur_mfar_lines (p_adjustment_id NUMBER)
2468 IS
2469 SELECT
2470 adj.gl_date gl_date,
2471 adj.doc_sequence_id doc_seqid,
2472 adj.doc_sequence_value doc_num,
2473 ct.invoice_currency_code currency,
2474 DECODE(l.lookup_code, '1', psa_adj_dist.mf_adjustment_ccid,
2475 '2', get_adj_ccid (p_adjustment_id),
2476 '3', PSA_MFAR_UTILS.get_rec_ccid (null, adj.customer_trx_id),
2477 '4', psa_trx_dist.mf_receivables_ccid) ccid,
2478 get_entered_dr_adj (l.lookup_code, psa_adj_dist.amount) entered_dr,
2479 get_entered_cr_adj (l.lookup_code, psa_adj_dist.amount) entered_cr,
2480 get_entered_dr_adj (l.lookup_code, psa_adj_dist.amount) accounted_dr,
2481 get_entered_cr_adj (l.lookup_code, psa_adj_dist.amount) accounted_cr,
2482 adj.ussgl_transaction_code,
2483 l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
2484 DECODE (l.lookup_code, '1', 'MFAR ',
2485 '2', 'MFAR Reversal of ' ,
2486 '3', 'MFAR Reversal of ' ,
2487 '4', 'MFAR ' ) ||
2488 DECODE( l_summary_flag,'Y',NULL,
2489 DECODE( l.lookup_code,
2490 '4', DECODE(sign(psa_adj_dist.amount), -1,
2491 l_pre_adjcr_ar || DECODE(ctt.type, 'CB', l_class_cb,
2492 'CM', l_class_cm,
2493 'DEP', l_class_dep,
2494 'DM', l_class_dm,
2495 'GUAR', l_class_guar,
2496 'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general,
2497 l_pre_adjdr_ar || DECODE(ctt.type, 'CB', l_class_cb, 'CM', l_class_cm,
2498 'DEP', l_class_dep,
2499 'DM', l_class_dm,
2500 'GUAR', l_class_guar,
2501 'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general),
2502 '3', DECODE(sign(psa_adj_dist.amount), -1,
2503 l_pre_adjcr_ar || DECODE(ctt.type, 'CB', l_class_cb,
2504 'CM', l_class_cm,
2505 'DEP', l_class_dep,
2506 'DM', l_class_dm,
2507 'GUAR', l_class_guar,
2508 'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general,
2509 l_pre_adjdr_ar || DECODE(ctt.type, 'CB', l_class_cb,
2510 'CM', l_class_cm,
2511 'DEP', l_class_dep,
2512 'DM', l_class_dm,
2513 'GUAR', l_class_guar,
2514 'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general),
2515 '2', DECODE(sign(psa_adj_dist.amount), -1,
2516 l_pre_adjdr_adj || DECODE(ctt.type, 'CB', l_class_cb,
2517 'CM', l_class_cm,
2518 'DEP', l_class_dep,
2519 'DM', l_class_dm,
2520 'GUAR', l_class_guar,
2521 'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general,
2522 l_pre_adjcr_adj || DECODE(ctt.type, 'CB', l_class_cb,
2523 'CM', l_class_cm,
2524 'DEP', l_class_dep,
2525 'DM', l_class_dm,
2526 'GUAR', l_class_guar,
2527 'INV', l_class_inv,NULL) || ' ' || ct.trx_number || l_post_general),
2528 '1', DECODE(sign(psa_adj_dist.amount), -1,
2529 l_pre_adjdr_adj || DECODE(ctt.type, 'CB', l_class_cb,
2530 'CM', l_class_cm,
2531 'DEP', l_class_dep,
2532 'DM', l_class_dm,
2533 'GUAR', l_class_guar,
2534 'INV', l_class_inv,NULL) || ' ' || ct.trx_number|| l_post_general,
2535 l_pre_adjcr_adj || DECODE(ctt.type, 'CB', l_class_cb,
2536 'CM', l_class_cm,
2537 'DEP', l_class_dep,
2538 'DM', l_class_dm,
2539 'GUAR', l_class_guar,
2540 'INV', l_class_inv,NULL) || ' ' || ct.trx_number|| l_post_general))) ref10,
2541 TO_CHAR(l_pst_ctrl_id) ref21,
2542 TO_CHAR(psa_adj_dist.adjustment_id) ref22,
2543 -- TO_CHAR(psa_adj_dist.cust_trx_line_gl_dist_id) ref23,
2544 nvl(get_adj_ard_id(adj.adjustment_id),
2545 to_char(psa_adj_dist.cust_trx_line_gl_dist_id) ) ref23,
2546 ct.trx_number ref24,
2547 adj.adjustment_number ref25,
2548 ctt.type ref26,
2549 ct.bill_to_customer_id ref27,
2550 'ADJ' ref28,
2551 DECODE(l.lookup_code, '1', 'ADJ_REC',
2552 '2', 'ADJ_ADJ',
2553 '3', 'ADJ_FINCHRG') ref29,
2554 'PSA_ADJ_DIST' ref30
2555 FROM ar_adjustments adj,
2556 psa_mf_adj_dist_all psa_adj_dist,
2557 psa_mf_trx_dist_all psa_trx_dist,
2558 ra_customer_trx ct,
2559 ra_cust_trx_line_gl_dist ctlgd,
2560 ra_cust_trx_types ctt,
2561 psa_lookup_codes l
2562 WHERE psa_adj_dist.adjustment_id = adj.adjustment_id
2563 AND adj.adjustment_id = p_adjustment_id
2564 AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_adj_dist.cust_trx_line_gl_dist_id
2565 AND adj.customer_trx_id = ct.customer_trx_id
2566 AND ct.cust_trx_type_id = ctt.cust_trx_type_id
2567 AND psa_adj_dist.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
2568 AND l.lookup_type = 'PSA_CARTESIAN_JOIN'
2569 -- AND l.lookup_code in ('1','2','3','4')
2570 AND l.lookup_code in ('1','4')
2571 -- AND nvl(psa_adj_dist.amount, 0) <> 0 -- Bug 3739491, commented this condition
2572 AND adj.posting_control_id = l_pst_ctrl_id
2573 AND nvl(psa_adj_dist.posting_control_id, -3) = -3;
2574
2575 -- ========================= FND LOG ===========================
2576 l_full_path VARCHAR2(100) := g_path || 'mfar_adj_to_gl';
2577 -- ========================= FND LOG ===========================
2578
2579 BEGIN
2580
2581 -- ========================= FND LOG ===========================
2582 psa_utils.debug_other_string(g_state_level,l_full_path,
2583 ' ' );
2584 psa_utils.debug_other_string(g_state_level,l_full_path,
2585 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
2586 psa_utils.debug_other_string(g_state_level,l_full_path,
2587 ' (TRANSFERRING ADJUSTMENTS) '
2588 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
2589 psa_utils.debug_other_string(g_state_level,l_full_path,
2590 ' ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~');
2591 psa_utils.debug_other_string(g_state_level,l_full_path,
2592 ' ');
2593 -- ========================= FND LOG ===========================
2594 retcode := 'S';
2595 l_gl_start_date := p_gl_date_from;
2596 l_post_through_date := p_gl_date_to;
2597 l_summary_flag := p_summary_flag;
2598 l_sob_id := p_set_of_books_id;
2599
2600 -- ========================= FND LOG ===========================
2601 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
2602 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
2603 psa_utils.debug_other_string(g_state_level,l_full_path,' p_gl_date_from -->' || p_gl_date_from);
2604 psa_utils.debug_other_string(g_state_level,l_full_path,' p_gl_date_to -->' || p_gl_date_to );
2605 psa_utils.debug_other_string(g_state_level,l_full_path,' p_summary_flag -->' || p_summary_flag );
2606 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id -->' || p_set_of_books_id );
2607 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2608 psa_utils.debug_other_string(g_state_level,l_full_path,' OTHER VALUES :');
2609 psa_utils.debug_other_string(g_state_level,l_full_path,' ============= ');
2610 psa_utils.debug_other_string(g_state_level,l_full_path,' l_gl_start_date -->' || l_gl_start_date);
2611 psa_utils.debug_other_string(g_state_level,l_full_path,' l_post_through_date -->' || l_post_through_date);
2612 psa_utils.debug_other_string(g_state_level,l_full_path,' l_summary_flag -->' || l_summary_flag);
2613 psa_utils.debug_other_string(g_state_level,l_full_path,' l_sob_id -->' || l_sob_id);
2614 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2615 psa_utils.debug_other_string(g_state_level,l_full_path,' PROCESS :');
2616 psa_utils.debug_other_string(g_state_level,l_full_path,' =========');
2617 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2618 -- ========================= FND LOG ===========================
2619
2620 FOR I IN Cur_lines_to_be_processed
2621 LOOP
2622
2623 BEGIN
2624 -- ========================= FND LOG ===========================
2625 psa_utils.debug_other_string(g_state_level,l_full_path,
2626 ' --> Creating distribution for adjustment id ==> ' || l_run_num
2627 || ' -- ' || I.adjustment_id);
2628 -- ========================= FND LOG ===========================
2629
2630 IF NOT (PSA_MFAR_ADJUSTMENTS.create_distributions (
2631 errbuf => l_errbuf,
2632 retcode => l_retcode,
2633 p_adjustment_id => I.adjustment_id,
2634 p_set_of_books_id => l_sob_id,
2635 p_run_id => l_run_num,
2636 p_error_message => l_error_message)) THEN
2637
2638 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
2639 -- ========================= FND LOG ===========================
2640 psa_utils.debug_other_string(g_state_level,l_full_path,
2641 'Mfar_adj_to_gl: Raising invalid_distribution');
2642 -- ========================= FND LOG ===========================
2643 Raise invalid_distribution;
2644 END IF;
2645
2646 ELSE
2647 -- ========================= FND LOG ===========================
2648 psa_utils.debug_other_string(g_state_level,l_full_path,
2649 'Mfar_adj_to_gl: '
2650 || ' Adjustment id --> ' || I.adjustment_id);
2651 -- ========================= FND LOG ===========================
2652 END IF;
2653 END;
2654
2655 -- Bug 3817595 .. Start
2656 DELETE FROM GL_INTERFACE GI
2657 WHERE GI.user_je_source_name = 'Receivables'
2658 AND GI.set_of_books_id = l_sob_id
2659 AND GI.group_id = l_pst_ctrl_id
2660 AND GI.reference28 = 'ADJ'
2661 AND GI.reference29 IN ('ADJ_ADJ', 'ADJ_REC', 'ADJ_FINCHRG')
2662 AND GI.reference10 NOT LIKE '%MFAR%'
2663 AND GI.reference22 = to_char(I.adjustment_id);
2664 -- Bug 3817595 .. End
2665
2666 -- ========================= FND LOG ===========================
2667 psa_utils.debug_other_string(g_state_level,l_full_path,
2668 ' Deleting rows from Gl interface ' || SQL%ROWCOUNT);
2669 -- ========================= FND LOG ===========================
2670
2671 FOR J IN Cur_mfar_lines (I.adjustment_id)
2672 LOOP
2673
2674 /*
2675 ## For each adjustments id the record will be inserted.
2676 */
2677
2678 -- ========================= FND LOG ===========================
2679 psa_utils.debug_other_string(g_state_level,l_full_path,
2680 ' --> adjustment id ==> ' || I.adjustment_id );
2681 -- ========================= FND LOG ===========================
2682
2683 INSERT INTO gl_interface
2684 (created_by,
2685 date_created,
2686 status,
2687 actual_flag,
2688 group_id,
2689 set_of_books_id,
2690 user_je_source_name,
2691 user_je_category_name,
2692 accounting_date,
2693 subledger_doc_sequence_id,
2694 subledger_doc_sequence_value,
2695 currency_code,
2696 code_combination_id,
2697 entered_dr,
2698 entered_cr,
2699 accounted_dr,
2700 accounted_cr,
2701 ussgl_transaction_code,
2702 reference1,
2703 reference10,
2704 reference21,
2705 reference22,
2706 reference23,
2707 reference24,
2708 reference25,
2709 reference26,
2710 reference27,
2711 reference28,
2712 reference29,
2713 reference30)
2714 VALUES (l_user_id,
2715 trunc(sysdate),
2716 l_status,
2717 l_actual_flag,
2718 l_pst_ctrl_id,
2719 l_sob_id,
2720 l_source,
2721 l_adj_cat_name,
2722 J.gl_date,
2723 J.doc_seqid,
2724 J.doc_num,
2725 J.currency,
2726 J.ccid,
2727 J.entered_dr,
2728 J.entered_cr,
2729 J.accounted_dr,
2730 J.accounted_cr,
2731 J.ussgl_transaction_code,
2732 J.ref1,
2733 J.ref10,
2734 J.ref21,
2735 J.ref22,
2736 J.ref23,
2737 J.ref24,
2738 J.ref25,
2739 J.ref26,
2740 J.ref27,
2741 J.ref28,
2742 J.ref29,
2743 J.ref30);
2744
2745 IF (SQL%FOUND) THEN
2746 -- ========================= FND LOG ===========================
2747 psa_utils.debug_other_string(g_state_level,l_full_path,
2748 ' --> Inserting into GL INTERFACE for adjustment id ==> '
2749 || I.adjustment_id );
2750 psa_utils.debug_other_string(g_state_level,l_full_path,
2751 ' CCID => ' || J.ccid
2752 || ' DEBIT => ' || J.entered_dr
2753 || ' CREDIT => ' || J.entered_cr );
2754 psa_utils.debug_other_string(g_state_level,l_full_path,
2755 ' USSGL_TRANSACTION_CODE => '|| J.ussgl_transaction_code);
2756 -- ========================= FND LOG ===========================
2757 END IF;
2758 END LOOP;
2759
2760 END LOOP;
2761
2762 UPDATE psa_mf_adj_dist_all pada
2763 SET pada.posting_control_id = l_pst_ctrl_id
2764 WHERE pada.adjustment_id IN
2765 (SELECT adjustment_id FROM ar_adjustments aa
2766 WHERE aa.posting_control_id = l_pst_ctrl_id);
2767
2768 IF (SQL%FOUND) THEN
2769 -- ========================= FND LOG ===========================
2770 psa_utils.debug_other_string(g_state_level,l_full_path,
2771 ' --> (PSA_MF_ADJ_DIST_ALL) Posting control id updated for '
2772 || (SQL%ROWCOUNT));
2773 -- ========================= FND LOG ===========================
2774 END IF;
2775
2776 -- ========================= FND LOG ===========================
2777 psa_utils.debug_other_string(g_event_level,l_full_path,
2778 ' --> End of Adjustments transfer '
2779 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
2780 -- ========================= FND LOG ===========================
2781
2782 EXCEPTION
2783 WHEN INVALID_DISTRIBUTION THEN
2784 -- ========================= FND LOG ===========================
2785 psa_utils.debug_other_string(g_excep_level,l_full_path,
2786 ' --> EXCEPTION - INVALID_DISTRIBUTION raised during PPSA_XFR_TO_GL_PKG.Mfar_adj_to_gl ');
2787 psa_utils.debug_other_string(g_excep_level,l_full_path,
2788 ' --> p_error_message --> ' || l_error_message);
2789 -- ========================= FND LOG ===========================
2790 retcode := 'F';
2791
2792 WHEN OTHERS THEN
2793 -- ========================= FND LOG ===========================
2794 psa_utils.debug_other_string(g_excep_level,l_full_path,
2795 ' --> EXCEPTION - OTHERS raised during PSA_XFR_TO_GL_PKG.Mfar_adj_to_gl ');
2796 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
2797 psa_utils.debug_unexpected_msg(l_full_path);
2798 -- ========================= FND LOG ===========================
2799 errbuf := 2;
2800 retcode := 'F';
2801
2802 END Mfar_adj_to_gl;
2803
2804 /* ########################################## REVERSE_CORE_ENTRIES_IF_ANY ################################# */
2805
2806 -- Bug 3621280.
2807 -- Flow :
2808 -- Get the Balancing or Natural segment based in the Allocation method
2809 -- Check whether any CM application is there in gl_interface for any core entries
2810 -- IF exists then check whether its balanced based on Allocation method
2811 -- IF not balanced then create reversal entry.
2812 --
2813 -- This procedure will be called from transfer_to_gl.
2814 -- And it will process all the CM app records in gl_interface for a group_id.
2815 --
2816 -- When you modify the procedure please make sure you modify the debug statements as well.
2817 --
2818
2819 PROCEDURE Reverse_core_entries_if_any (errbuf OUT NOCOPY VARCHAR2,
2820 retcode OUT NOCOPY VARCHAR2,
2821 p_set_of_books_id IN NUMBER,
2822 p_error_message OUT NOCOPY VARCHAR2)
2823 IS
2824
2825 -- Getting chart of account id.
2826 CURSOR Cur_coa_id (p_sob_id NUMBER)
2827 IS
2828 SELECT chart_of_accounts_id FROM gl_sets_of_books
2829 WHERE set_of_books_id = p_sob_id;
2830
2831 CURSOR Cur_cust_trx_id
2832 IS
2833 SELECT distinct reference22 FROM gl_interface
2834 WHERE group_id = l_pst_ctrl_id
2835 AND reference28 = 'CMAPP'
2836 AND reference29 = 'CMAPP_REC'
2837 AND reference30 = 'AR_RECEIVABLE_APPLICATIONS';
2838
2839 CURSOR Cur_CM_dets (p_cust_trx_id NUMBER)
2840 IS
2841 SELECT accounting_date gl_date,
2842 subledger_doc_sequence_id doc_seqid,
2843 subledger_doc_sequence_value doc_num,
2844 currency_code currency,
2845 code_combination_id ccid,
2846 entered_dr entered_dr,
2847 entered_cr entered_cr,
2848 accounted_dr accounted_dr,
2849 accounted_cr accounted_cr,
2850 reference1 ref1,
2851 'MFAR reversal for' || Substr(reference10,19) ref10,
2852 reference21 ref21,
2853 reference22 ref22,
2854 reference23 ref23,
2855 reference24 ref24,
2856 reference25 ref25,
2857 reference26 ref26,
2858 reference27 ref27,
2859 reference28 ref28,
2860 reference29 ref29,
2861 reference30 ref30
2862 FROM gl_interface
2863 WHERE group_id = l_pst_ctrl_id
2864 AND reference22 = p_cust_trx_id
2865 AND reference28 = 'CMAPP'
2866 AND reference29 = 'CMAPP_REC'
2867 AND reference30 = 'AR_RECEIVABLE_APPLICATIONS';
2868
2869 l_chart_of_accounts_id NUMBER;
2870 l_org_details PSA_IMPLEMENTATION_ALL%ROWTYPE;
2871 l_qual_name VARCHAR2(20);
2872 l_acct_seg_num NUMBER;
2873 l_select VARCHAR2(3000);
2874 l_count NUMBER;
2875
2876 TYPE gl_rec_type IS RECORD (Segment VARCHAR2(25), Debit NUMBER, Credit NUMBER);
2877 TYPE gl_tab_type IS TABLE OF gl_rec_type INDEX BY Binary_integer;
2878 gl_int_dets gl_tab_type;
2879 gl_int_dets_null gl_tab_type;
2880
2881 GET_QUALIFIER_SEGNUM_EXCEP EXCEPTION;
2882
2883 -- ========================= FND LOG ===========================
2884 l_full_path VARCHAR2(100) := g_path || 'Reverse_core_entries_if_any';
2885 -- ========================= FND LOG ===========================
2886
2887 BEGIN
2888
2889 -- ========================= FND LOG ===========================
2890 psa_utils.debug_other_string(g_state_level,l_full_path,' START Reverse_core_entries_if_any ');
2891 psa_utils.debug_other_string(g_state_level,l_full_path,' PARAMETERS :');
2892 psa_utils.debug_other_string(g_state_level,l_full_path,' ============');
2893 psa_utils.debug_other_string(g_state_level,l_full_path,' p_set_of_books_id -->' || p_set_of_books_id );
2894 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2895 psa_utils.debug_other_string(g_state_level,l_full_path,' PROCESS :');
2896 psa_utils.debug_other_string(g_state_level,l_full_path,' =========');
2897 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
2898 -- ========================= FND LOG ===========================
2899
2900 retcode := 'S';
2901
2902 OPEN Cur_coa_id (p_set_of_books_id);
2903 FETCH Cur_coa_id INTO l_chart_of_accounts_id;
2904 CLOSE Cur_coa_id;
2905
2906 -- ========================= FND LOG ===========================
2907 psa_utils.debug_other_string(g_state_level,l_full_path,' l_chart_of_accounts_id --> ' || l_chart_of_accounts_id);
2908 psa_utils.debug_other_string(g_state_level,l_full_path,' Getting org details ');
2909 -- ========================= FND LOG ===========================
2910
2911 PSA_MFAR_UTILS.PSA_MF_ORG_DETAILS (l_org_details);
2912
2913 -- ========================= FND LOG ===========================
2914 psa_utils.debug_other_string(g_state_level,l_full_path,' l_org_details.allocation_method --> ' || l_org_details.allocation_method);
2915 -- ========================= FND LOG ===========================
2916
2917 IF (l_org_details.allocation_method = 'BAL') THEN
2918 l_qual_name := 'GL_BALANCING';
2919 ELSE -- ## 'ACC'
2920 l_qual_name := 'GL_ACCOUNT';
2921 END IF;
2922
2923 -- ========================= FND LOG ===========================
2924 psa_utils.debug_other_string(g_state_level,l_full_path,' l_qual_name --> ' || l_qual_name);
2925 psa_utils.debug_other_string(g_state_level,l_full_path,' Calling FND_FLEX_APIS.GET_QUALIFIER_SEGNUM ' );
2926 -- ========================= FND LOG ===========================
2927
2928 IF (NOT FND_FLEX_APIS.GET_QUALIFIER_SEGNUM(
2929 APPL_ID => 101,
2930 KEY_FLEX_CODE => 'GL#',
2931 STRUCTURE_NUMBER => l_chart_of_accounts_id,
2932 FLEX_QUAL_NAME => l_qual_name,
2933 SEGMENT_NUMBER => l_acct_seg_num)) THEN -- OUT
2934 -- ========================= FND LOG ===========================
2935 psa_utils.debug_other_string(g_state_level,l_full_path,' l_acct_seg_num --> ' || l_acct_seg_num );
2936 psa_utils.debug_other_string(g_state_level,l_full_path,' Raising GET_QUALIFIER_SEGNUM_EXCEP ');
2937 -- ========================= FND LOG ===========================
2938 RAISE GET_QUALIFIER_SEGNUM_EXCEP;
2939 ELSE
2940 -- ========================= FND LOG ===========================
2941 psa_utils.debug_other_string(g_state_level,l_full_path,' l_acct_seg_num --> ' || l_acct_seg_num );
2942 -- ========================= FND LOG ===========================
2943 END IF;
2944
2945 -- Picking up details from gl_interface.
2946 FOR C_cust_trx_dets IN Cur_cust_Trx_id
2947 LOOP
2948
2949 -- ========================= FND LOG ===========================
2950 psa_utils.debug_other_string(g_state_level,l_full_path,' Inside Cur_cust_Trx_id');
2951 -- ========================= FND LOG ===========================
2952
2953 gl_int_dets := gl_int_dets_null;
2954
2955 l_select := ' SELECT segment' || l_acct_seg_num || ' Segment, SUM(accounted_dr) Debit, SUM(accounted_cr) Credit' ||
2956 ' FROM gl_interface ' ||
2957 ' WHERE reference22 = :1' ||
2958 ' AND reference30 = :2' ||
2959 ' GROUP BY segment' || l_acct_seg_num ||
2960 ' HAVING SUM(nvl(accounted_dr,0)) <> SUM(nvl(accounted_cr,0))' ;
2961
2962 -- ========================= FND LOG ===========================
2963 psa_utils.debug_other_string(g_state_level,l_full_path,' C_cust_trx_dets.reference22 --> ' || C_cust_trx_dets.reference22);
2964 psa_utils.debug_other_string(g_state_level,l_full_path,' l_select --> ' || l_select);
2965 -- ========================= FND LOG ===========================
2966
2967 EXECUTE IMMEDIATE l_select BULK COLLECT INTO gl_int_dets USING C_cust_trx_dets.reference22, 'AR_RECEIVABLE_APPLICATIONS';
2968
2969 -- ========================= FND LOG ===========================
2970 psa_utils.debug_other_string(g_state_level,l_full_path,' Execute immediate ');
2971 -- ========================= FND LOG ===========================
2972
2973 l_count := gl_int_dets.count;
2974
2975 IF (l_count <> 0)
2976 THEN
2977 -- ========================= FND LOG ===========================
2978 psa_utils.debug_other_string(g_state_level,l_full_path,' l_count --> ' || l_count);
2979 -- ========================= FND LOG ===========================
2980
2981 FOR C_int_dets IN Cur_CM_dets (C_cust_trx_dets.reference22)
2982 LOOP
2983
2984 -- ========================= FND LOG ===========================
2985 psa_utils.debug_other_string(g_state_level,l_full_path,' Inserting into gl_interface');
2986 -- ========================= FND LOG ===========================
2987
2988 INSERT INTO gl_interface
2989 (created_by,
2990 date_created,
2991 status,
2992 actual_flag,
2993 group_id,
2994 set_of_books_id,
2995 user_je_source_name,
2996 user_je_category_name,
2997 accounting_date,
2998 subledger_doc_sequence_id,
2999 subledger_doc_sequence_value,
3000 currency_code,
3001 code_combination_id,
3002 entered_dr,
3003 entered_cr,
3004 accounted_dr,
3005 accounted_cr,
3006 reference1,
3007 reference10,
3008 reference21,
3009 reference22,
3010 reference23,
3011 reference24,
3012 reference25,
3013 reference26,
3014 reference27,
3015 reference28,
3016 reference29,
3017 reference30)
3018 VALUES (l_user_id,
3019 trunc(sysdate),
3020 l_status,
3021 l_actual_flag,
3022 l_pst_ctrl_id,
3023 l_sob_id,
3024 l_source,
3025 l_user_cm_cat_name,
3026 C_int_dets.gl_date,
3027 C_int_dets.doc_seqid,
3028 C_int_dets.doc_num,
3029 C_int_dets.currency,
3030 C_int_dets.ccid,
3031 C_int_dets.entered_cr, -- reversal so interchanged DB and CR.
3032 C_int_dets.entered_dr,
3033 C_int_dets.accounted_cr,
3034 C_int_dets.accounted_dr,
3035 C_int_dets.ref1,
3036 C_int_dets.ref10,
3037 C_int_dets.ref21,
3038 C_int_dets.ref22,
3039 C_int_dets.ref23,
3040 C_int_dets.ref24,
3041 C_int_dets.ref25,
3042 C_int_dets.ref26,
3043 C_int_dets.ref27,
3044 C_int_dets.ref28,
3045 C_int_dets.ref29,
3046 C_int_dets.ref30);
3047
3048 -- ========================= FND LOG ===========================
3049 psa_utils.debug_other_string(g_state_level,l_full_path,' Inserting into gl_interface --> ' || SQL%ROWCOUNT);
3050 -- ========================= FND LOG ===========================
3051
3052 END LOOP;
3053 ELSE
3054 -- ========================= FND LOG ===========================
3055 psa_utils.debug_other_string(g_state_level,l_full_path,' l_count --> ' || l_count);
3056 psa_utils.debug_other_string(g_state_level,l_full_path,' BALANCED ');
3057 -- ========================= FND LOG ===========================
3058 END IF;
3059
3060 END LOOP;
3061
3062 -- ========================= FND LOG ===========================
3063 psa_utils.debug_other_string(g_state_level,l_full_path,' END Reverse_core_entries_if_any ');
3064 -- ========================= FND LOG ===========================
3065
3066 EXCEPTION
3067
3068 WHEN GET_QUALIFIER_SEGNUM_EXCEP THEN
3069 p_error_message := fnd_message.get;
3070 -- ========================= FND LOG ===========================
3071 psa_utils.debug_other_string(g_excep_level,l_full_path,
3072 ' --> EXCEPTION - GET_QUALIFIER_SEGNUM_EXCEP - ' || p_error_message);
3073 -- ========================= FND LOG ===========================
3074 retcode := 'F';
3075
3076 WHEN OTHERS THEN
3077 p_error_message := sqlcode || sqlerrm;
3078 -- ========================= FND LOG ===========================
3079 psa_utils.debug_other_string(g_excep_level,l_full_path,
3080 ' --> EXCEPTION - OTHERS raised during PSA_XFR_TO_GL_PKG.Reverse_core_entries_if_any ');
3081 psa_utils.debug_other_string(g_excep_level,l_full_path, p_error_message);
3082 psa_utils.debug_unexpected_msg(l_full_path);
3083 -- ========================= FND LOG ===========================
3084 errbuf := 2;
3085 retcode := 'F';
3086
3087 END Reverse_core_entries_if_any;
3088
3089 /* ########################################## POPULATE_GLOBAL_VARIABLES ################################# */
3090
3091 /*
3092 ## This procedure will populate all the variables that is required for this package before calling the
3093 ## procedure that transfers RECEIPTS, ADJUSTMENTS, MISC TRANS, TRANSACTIONS.
3094 ## This procedure is called only from Transfer_to_gl procedure and the variables that this procedure
3095 ## populates are declared in the starting of the package.
3096 */
3097
3098 PROCEDURE Populate_global_variables
3099 IS
3100
3101 /* getting the message text based on name AND lang */
3102 CURSOR Cur_message (p_message_name varchar2)
3103 IS
3104 SELECT Message_text FROM Fnd_new_messages
3105 WHERE language_code = USERENV('LANG')
3106 AND message_name = p_message_name;
3107
3108 CURSOR Cur_js_cat (p_category_name VARCHAR2)
3109 IS
3110 SELECT user_je_category_name FROM gl_je_categories
3111 WHERE je_category_name = p_category_name ;
3112
3113 CURSOR Cur_je_source_name
3114 IS
3115 SELECT user_je_source_name FROM gl_je_sources
3116 WHERE je_source_name = 'Receivables';
3117
3118 CURSOR Cur_func_curr
3119 IS
3120 SELECT currency_code from gl_sets_of_books
3121 WHERE Set_of_books_id = l_sob_id;
3122
3123 l_fv_profile_defined BOOLEAN;
3124 -- ========================= FND LOG ===========================
3125 l_full_path VARCHAR2(100) := g_path || 'Populate_global_variables';
3126 -- ========================= FND LOG ===========================
3127
3128 BEGIN
3129
3130 -- ========================= FND LOG ===========================
3131 psa_utils.debug_other_string(g_state_level,l_full_path,
3132 ' --> Populate_global_variables - START '
3133 || to_char(sysdate, 'DD/MM/YYYY HH:MI:SS'));
3134 -- ========================= FND LOG ===========================
3135
3136 l_batch_prefix := 'AR '; -- Padded with a single space so that REFERENCE1 is correctly set as AR 3241
3137 -- where 3241 is the posting_control_id
3138 l_user_id := fnd_global.user_id;
3139 l_actual_flag := 'A';
3140 l_status := 'NEW';
3141
3142 OPEN Cur_func_curr;
3143 FETCH Cur_func_curr INTO l_func_curr;
3144 CLOSE Cur_func_curr;
3145
3146 OPEN Cur_message ('AR_NLS_GLTP_PRE_CT_LINE');
3147 FETCH Cur_message INTO l_pre_ct_line;
3148 CLOSE Cur_message;
3149
3150 OPEN Cur_message ('AR_NLS_GLTP_POST_CT_LINE');
3151 FETCH Cur_message INTO l_post_ct_line;
3152 CLOSE Cur_message;
3153
3154 OPEN Cur_js_cat ('Sales Invoices');
3155 FETCH Cur_js_cat INTO l_inv_cat_name;
3156 CLOSE Cur_js_cat;
3157
3158 OPEN Cur_js_cat ('Credit Memos');
3159 FETCH Cur_js_cat INTO l_cm_cat_name;
3160 CLOSE Cur_js_cat;
3161
3162 -- Bug 3018452 (Tpradhan), Initialized the value of l_user_cm_cat_name
3163 -- using the cursor below (...Start...)
3164
3165 OPEN Cur_js_cat ('Credit Memo Applications');
3166 FETCH Cur_js_cat INTO l_user_cm_cat_name;
3167 CLOSE Cur_js_cat;
3168
3169 -- Bug 3018452 (...End...)
3170
3171 OPEN Cur_js_cat ('Debit Memos');
3172 FETCH Cur_js_cat INTO l_dm_cat_name;
3173 CLOSE Cur_js_cat;
3174
3175 OPEN Cur_js_cat ('Chargebacks');
3176 FETCH Cur_js_cat INTO l_cb_cat_name;
3177 CLOSE Cur_js_cat;
3178
3179 OPEN Cur_js_cat ('Trade Receipts');
3180 FETCH Cur_js_cat INTO l_trade_cat_name;
3181 CLOSE Cur_js_cat;
3182
3183 OPEN Cur_js_cat ('Cross Currency');
3184 FETCH Cur_js_cat INTO l_ccurr_cat_name;
3185 CLOSE Cur_js_cat;
3186
3187 OPEN Cur_js_cat ('Adjustment');
3188 FETCH Cur_js_cat INTO l_adj_cat_name;
3189 CLOSE Cur_js_cat;
3190
3191 OPEN Cur_js_cat ('Misc Receipts');
3192 FETCH Cur_js_cat INTO l_misc_cat_name;
3193 CLOSE Cur_js_cat;
3194
3195 OPEN Cur_je_source_name;
3196 FETCH Cur_je_source_name INTO l_source;
3197 CLOSE Cur_je_source_name;
3198
3199 OPEN Cur_message ('AR_NLS_CLASS_CB');
3200 FETCH Cur_message INTO l_class_cb;
3201 CLOSE Cur_message;
3202
3203 OPEN Cur_message ('AR_NLS_CLASS_CM');
3204 FETCH Cur_message INTO l_class_cm;
3205 CLOSE Cur_message;
3206
3207 OPEN Cur_message ('AR_NLS_CLASS_DEP');
3208 FETCH Cur_message INTO l_class_dep;
3209 CLOSE Cur_message;
3210
3211 OPEN Cur_message ('AR_NLS_CLASS_DM');
3212 FETCH Cur_message INTO l_class_dm;
3213 CLOSE Cur_message;
3214
3215 OPEN Cur_message ('AR_NLS_CLASS_GUAR');
3216 FETCH Cur_message INTO l_class_guar;
3217 CLOSE Cur_message;
3218
3219 OPEN Cur_message ('AR_NLS_CLASS_INV');
3220 FETCH Cur_message INTO l_class_inv;
3221 CLOSE Cur_message;
3222
3223 OPEN Cur_message ('AR_NLS_GLTP_PRE_TRADEAPP');
3224 FETCH Cur_message INTO l_pre_tradeapp;
3225 CLOSE Cur_message;
3226
3227 OPEN Cur_message ('AR_NLS_APP_ONACC');
3228 FETCH Cur_message INTO l_app_onacc;
3229 CLOSE Cur_message;
3230
3231 OPEN Cur_message ('AR_NLS_APP_UNAPP');
3232 FETCH Cur_message INTO l_app_unapp;
3233 CLOSE Cur_message;
3234
3235 OPEN Cur_message ('AR_NLS_APP_UNID');
3236 FETCH Cur_message INTO l_app_unid;
3237 CLOSE Cur_message;
3238
3239 OPEN Cur_message ('AR_NLS_APP_APPLIED');
3240 FETCH Cur_message INTO l_app_applied;
3241 CLOSE Cur_message;
3242
3243 OPEN Cur_message ('AR_NLS_GLTP_PRE_ERDISC');
3244 FETCH Cur_message INTO l_pre_erdisc;
3245 CLOSE Cur_message;
3246
3247 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_EDISC_NRT');
3248 FETCH Cur_message INTO l_pre_rec_erdisc_nrtax;
3249 CLOSE Cur_message;
3250
3251 OPEN Cur_message ('AR_NLS_GLTP_PRE_UNDISC');
3252 FETCH Cur_message INTO l_pre_undisc;
3253 CLOSE Cur_message;
3254
3255 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_UDISC_NRT');
3256 FETCH Cur_message INTO l_pre_rec_undisc_nrtax;
3257 CLOSE Cur_message;
3258
3259 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_TAX');
3260 FETCH Cur_message INTO l_pre_rec_tax;
3261 CLOSE Cur_message;
3262
3263 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_GAIN');
3264 FETCH Cur_message INTO l_pre_rec_gain;
3265 CLOSE Cur_message;
3266
3267 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_LOSS');
3268 FETCH Cur_message INTO l_pre_rec_loss;
3269 CLOSE Cur_message;
3270
3271 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_CURROUND');
3272 FETCH Cur_message INTO l_pre_rec_curr_round;
3273 CLOSE Cur_message;
3274
3275 OPEN Cur_message ('AR_NLS_GLTP_PRE_RCPT_DEFTAX');
3276 FETCH Cur_message INTO l_pre_rec_deftax;
3277 CLOSE Cur_message;
3278
3279 OPEN Cur_message ('AR_NLS_GLTP_POST_GENERAL');
3280 FETCH Cur_message INTO l_post_general;
3281 CLOSE Cur_message;
3282
3283 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJCR_ADJ');
3284 FETCH Cur_message INTO l_pre_adjcr_adj;
3285 CLOSE Cur_message;
3286
3287 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJDR_ADJ');
3288 FETCH Cur_message INTO l_pre_adjdr_adj;
3289 CLOSE Cur_message;
3290
3291 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJCR_AR');
3292 FETCH Cur_message INTO l_pre_adjcr_ar;
3293 CLOSE Cur_message;
3294
3295 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJDR_AR');
3296 FETCH Cur_message INTO l_pre_adjdr_ar;
3297 CLOSE Cur_message;
3298
3299 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJCR');
3300 FETCH Cur_message INTO l_pre_adjcr;
3301 CLOSE Cur_message;
3302
3303 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJDR');
3304 FETCH Cur_message INTO l_pre_adjdr;
3305 CLOSE Cur_message;
3306
3307 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJ_DEFTAX');
3308 FETCH Cur_message INTO l_pre_adj_deftax;
3309 CLOSE Cur_message;
3310
3311 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJ_FINCHG');
3312 FETCH Cur_message INTO l_pre_adj_finchrg;
3313 CLOSE Cur_message;
3314
3315 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJ_FINCHG_NRT');
3316 FETCH Cur_message INTO l_pre_adj_finchrg_nrtax;
3317 CLOSE Cur_message;
3318
3319 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJ_NRT');
3320 FETCH Cur_message INTO l_pre_adj_nrtax;
3321 CLOSE Cur_message;
3322
3323 OPEN Cur_message ('AR_NLS_GLTP_PRE_ADJ_TAX');
3324 FETCH Cur_message INTO l_pre_adj_tax;
3325 CLOSE Cur_message;
3326
3327 OPEN Cur_message ('AR_NLS_CLASS_BR');
3328 FETCH Cur_message INTO l_class_br;
3329 CLOSE Cur_message;
3330
3331 --
3332 -- Fetch profile option value for FV: Post Detailed Receipt Accounting
3333 --
3334
3335 l_resp_appl_id := FND_GLOBAL.resp_appl_id;
3336 l_user_resp_id := FND_GLOBAL.RESP_ID;
3337
3338 FND_PROFILE.GET_SPECIFIC('FV_POST_DETAIL_REC_ACCOUNTING',
3339 l_user_id,
3340 l_user_resp_id,
3341 l_resp_appl_id,
3342 l_post_det_acct_flag,
3343 l_fv_profile_defined);
3344
3345 IF not l_fv_profile_defined THEN
3346 l_post_det_acct_flag := 'Y';
3347 END IF;
3348
3349 select psa_mf_error_log_s.nextval into l_run_num from dual;
3350
3351 -- ========================= FND LOG ===========================
3352 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
3353 psa_utils.debug_other_string(g_state_level,l_full_path,' LISTING THE VARIABLES AND VALUES :');
3354 psa_utils.debug_other_string(g_state_level,l_full_path,' ==================================');
3355 psa_utils.debug_other_string(g_state_level,l_full_path,' l_batch_prefix -->' || l_batch_prefix);
3356 psa_utils.debug_other_string(g_state_level,l_full_path,' l_user_id -->' || l_user_id);
3357 psa_utils.debug_other_string(g_state_level,l_full_path,' l_actual_flag -->' || l_actual_flag);
3358 psa_utils.debug_other_string(g_state_level,l_full_path,' l_status -->' || l_status);
3359 psa_utils.debug_other_string(g_state_level,l_full_path,' l_inv_cat_name -->' || l_inv_cat_name);
3360 psa_utils.debug_other_string(g_state_level,l_full_path,' l_cm_cat_name -->' || l_cm_cat_name);
3361 psa_utils.debug_other_string(g_state_level,l_full_path,' l_dm_cat_name -->' || l_dm_cat_name);
3362 psa_utils.debug_other_string(g_state_level,l_full_path,' l_cb_cat_name -->' || l_cb_cat_name);
3363 psa_utils.debug_other_string(g_state_level,l_full_path,' l_trade_cat_name -->' || l_trade_cat_name);
3364 psa_utils.debug_other_string(g_state_level,l_full_path,' l_ccurr_cat_name -->' || l_ccurr_cat_name);
3365 psa_utils.debug_other_string(g_state_level,l_full_path,' l_adj_cat_name -->' || l_adj_cat_name);
3366 psa_utils.debug_other_string(g_state_level,l_full_path,' l_misc_cat_name -->' || l_misc_cat_name);
3367 psa_utils.debug_other_string(g_state_level,l_full_path,' l_source -->' || l_source);
3368 psa_utils.debug_other_string(g_state_level,l_full_path,' l_func_curr -->' || l_func_curr);
3369 psa_utils.debug_other_string(g_state_level,l_full_path,' l_post_det_acct_flag -->' || l_post_det_acct_flag);
3370 psa_utils.debug_other_string(g_state_level,l_full_path,' l_user_resp_id -->' || l_user_resp_id);
3371 psa_utils.debug_other_string(g_state_level,l_full_path,' l_resp_appl_id -->' || l_resp_appl_id);
3372 psa_utils.debug_other_string(g_state_level,l_full_path,' l_pst_ctrl_id -->' || l_pst_ctrl_id);
3373 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
3374 psa_utils.debug_other_string(g_state_level,l_full_path, ' ** REST ARE MESSAGES ** ');
3375 psa_utils.debug_other_string(g_state_level,l_full_path,' ');
3376 psa_utils.debug_other_string(g_state_level,l_full_path,' --> Populate_global_variables - END '
3377 || to_char(sysdate, 'DD/MM/YYYY HH:MI:SS'));
3378 -- ========================= FND LOG ===========================
3379
3380 EXCEPTION
3381
3382 WHEN OTHERS THEN
3383 -- ========================= FND LOG ===========================
3384 psa_utils.debug_other_string(g_excep_level,l_full_path,
3385 ' --> EXCEPTION - OTHERS raised during PSA_TRANSFER_TO_GL_PKG.Populate_global_variables ');
3386 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
3387 psa_utils.debug_unexpected_msg(l_full_path);
3388 -- ========================= FND LOG ===========================
3389 app_exception.raise_exception;
3390
3391 END Populate_global_variables;
3392
3393 /* ########################################## GET_ENTERED_DR_RCT ################################# */
3394
3395 FUNCTION Get_entered_dr_rct (p_lookup_code IN NUMBER,
3396 p_amount IN NUMBER,
3397 p_discount IN NUMBER,
3398 p_ue_discount IN NUMBER) RETURN NUMBER
3399 IS
3400 l_return_amount NUMBER;
3401 BEGIN
3402
3403 IF p_lookup_code IN (1,2,3,4) THEN
3404 l_return_amount := p_amount;
3405 ELSIF p_lookup_code IN (5,6,7,8) THEN
3406 l_return_amount := p_discount;
3407 ELSIF p_lookup_code IN (9,10,11,12) THEN
3408 l_return_amount := p_ue_discount;
3409 END IF;
3410
3411 IF (l_return_amount >= 0) THEN /* POSITIVE */
3412 IF p_lookup_code IN (2,4,6,8,10,12) THEN /* EVEN (CR) LINES */
3413 l_return_amount := NULL;
3414 END IF;
3415 ELSIF (l_return_amount < 0) THEN /* NEGATIVE */
3416 IF p_lookup_code IN (1,3,5,7,9,11) THEN /* ODD (DR) LINES */
3417 l_return_amount := NULL;
3418 ELSIF p_lookup_code IN (2,4,6,8,10,12) THEN /* EVEN (CR) LINES */
3419 l_return_amount := -1 * l_return_amount ;
3420 END IF;
3421 END IF;
3422
3423 RETURN l_return_amount;
3424 END Get_entered_dr_rct;
3425
3426 /* ########################################## GET_ENTERED_CR_RCT ################################# */
3427
3428 FUNCTION Get_entered_cr_rct (p_lookup_code IN NUMBER,
3429 p_amount IN NUMBER,
3430 p_discount IN NUMBER,
3431 p_ue_discount IN NUMBER) RETURN NUMBER
3432 IS
3433 l_return_amount NUMBER;
3434 BEGIN
3435
3436 IF p_lookup_code IN (1,2,3,4) THEN
3437 l_return_amount := p_amount;
3438 ELSIF p_lookup_code IN (5,6,7,8) THEN
3439 l_return_amount := p_discount;
3440 ELSIF p_lookup_code IN (9,10,11,12) THEN
3441 l_return_amount := p_ue_discount;
3442 END IF;
3443
3444 IF (l_return_amount >= 0) THEN /* POSITIVE */
3445 IF p_lookup_code IN (1,3,5,7,9,11) THEN /* ODD (DR) LINES */
3446 l_return_amount := NULL;
3447 END IF;
3448 ELSIF (l_return_amount < 0) THEN /* NEGATIVE */
3449 IF p_lookup_code IN (2,4,6,8,10,12) THEN /* EVEN (CR) LINES */
3450 l_return_amount := NULL;
3451 ELSIF p_lookup_code IN (1,3,5,7,9,11) THEN /* ODD (DR) LINES */
3452 l_return_amount := -1 * l_return_amount ;
3453 END IF;
3454 END IF;
3455
3456 RETURN l_return_amount;
3457 END Get_entered_cr_rct ;
3458
3459 /* ########################################## GET_ENTERED_CR_CRM ################################# */
3460
3461 FUNCTION Get_entered_cr_crm (p_lookup_code IN NUMBER,
3462 p_amount IN NUMBER) RETURN NUMBER
3463 IS
3464 l_return_amount NUMBER := NULL;
3465 BEGIN
3466 IF p_lookup_code in (1) THEN
3467 l_return_amount := p_amount * -1;
3468 END IF;
3469 RETURN l_return_amount;
3470 END Get_entered_cr_crm;
3471
3472 /* ########################################## GET_ENTERED_DR_CRM ################################# */
3473
3474 FUNCTION Get_entered_dr_crm (p_lookup_code IN NUMBER,
3475 p_amount IN NUMBER) RETURN NUMBER
3476 IS
3477 l_return_amount NUMBER := NULL;
3478 BEGIN
3479 IF p_lookup_code in (2) THEN
3480 l_return_amount := p_amount * -1;
3481 END IF;
3482 RETURN l_return_amount;
3483 END Get_entered_dr_crm;
3484
3485 /* ########################################## GET_ENTERED_DR_ADJ ################################# */
3486
3487 FUNCTION get_entered_dr_adj (p_lookup_code IN NUMBER, p_amount IN NUMBER) RETURN NUMBER
3488 IS
3489 l_return_amount NUMBER;
3490 BEGIN
3491 l_return_amount := p_amount;
3492 IF (l_return_amount < 0) THEN /* NEGATIVE */
3493 IF p_lookup_code IN (1,3) THEN /* Odd (Dr) Lines */
3494 l_return_amount := -1 * l_return_amount ;
3495 ELSIF p_lookup_code IN (2,4) THEN /* Even (Cr) Lines */
3496 l_return_amount := NULL;
3497 END IF;
3498
3499 ELSIF (l_return_amount >= 0) THEN /* POSITIVE */
3500 IF p_lookup_code in (1,3) THEN /* Odd (Dr) Lines */
3501 l_return_amount := NULL;
3502 END IF;
3503 END IF;
3504
3505 RETURN l_return_amount;
3506 END get_entered_dr_adj;
3507
3508 /* ########################################## GET_ENTERED_CR_ADJ ################################# */
3509
3510 FUNCTION Get_entered_cr_adj (p_lookup_code IN NUMBER, p_amount IN NUMBER) RETURN NUMBER
3511 IS
3512 l_return_amount NUMBER;
3513 BEGIN
3514 l_return_amount := p_amount;
3515 IF (l_return_amount < 0) THEN /* NEGATIVE */
3516 IF p_lookup_code IN (1,3) THEN /* Odd (Dr) Lines */
3517 l_return_amount := NULL;
3518 ELSIF p_lookup_code in (2,4) THEN /* Even (Cr) Lines */
3519 l_return_amount := -1 * l_return_amount ;
3520 END IF;
3521
3522 ELSIF (l_return_amount >= 0) THEN /* POSITIVE */
3523 IF p_lookup_code in (2,4) THEN /* Even (Cr) Lines */
3524 l_return_amount := NULL;
3525 END IF;
3526
3527 END IF;
3528
3529 RETURN l_return_amount;
3530 END get_entered_cr_adj;
3531
3532 /* ##########################################UPD_SEG_IN_GL_INTERFACE ################################# */
3533
3534 PROCEDURE Upd_seg_in_gl_interface
3535 IS
3536 -- ========================= FND LOG ===========================
3537 l_full_path VARCHAR2(100) := g_path || 'Upd_seg_in_gl_interface';
3538 -- ========================= FND LOG ===========================
3539 BEGIN
3540 -- ========================= FND LOG ===========================
3541 psa_utils.debug_other_string(g_state_level,l_full_path,
3542 ' --> Upd_seg_in_gl_interface - START '
3543 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
3544 -- ========================= FND LOG ===========================
3545
3546 UPDATE gl_interface gi
3547 SET (
3548 gi.segment1 , gi.segment2 , gi.segment3 , gi.segment4 , gi.segment5 , gi.segment6 ,
3549 gi.segment7 , gi.segment8 , gi.segment9 , gi.segment10, gi.segment11, gi.segment12,
3550 gi.segment13, gi.segment14, gi.segment15, gi.segment16, gi.segment17, gi.segment18,
3551 gi.segment19, gi.segment20, gi.segment21, gi.segment22, gi.segment23, gi.segment24,
3552 gi.segment25, gi.segment26, gi.segment27, gi.segment28, gi.segment29, gi.segment30) =
3553 (SELECT
3554 cc.segment1 , cc.segment2 , cc.segment3 , cc.segment4 , cc.segment5 , cc.segment6 ,
3555 cc.segment7 , cc.segment8 , cc.segment9 , cc.segment10, cc.segment11, cc.segment12,
3556 cc.segment13, cc.segment14, cc.segment15, cc.segment16, cc.segment17, cc.segment18,
3557 cc.segment19, cc.segment20, cc.segment21, cc.segment22, cc.segment23, cc.segment24,
3558 cc.segment25, cc.segment26, cc.segment27, cc.segment28, cc.segment29, cc.segment30
3559 FROM gl_code_combinations cc
3560 WHERE cc.code_combination_id = gi.code_combination_id)
3561 WHERE gi.group_id = l_pst_ctrl_id
3562 AND reference10 like '%MFAR%';
3563
3564 -- ========================= FND LOG ===========================
3565 psa_utils.debug_other_string(g_state_level,l_full_path,
3566 ' --> Upd_seg_in_gl_interface - END '
3567 || to_char (sysdate, 'DD/MM/YYYY HH:MI:SS'));
3568 -- ========================= FND LOG ===========================
3569
3570 END Upd_seg_in_gl_interface;
3571
3572
3573 /* ########################################## GET_ADJ_CCID ################################# */
3574
3575 FUNCTION Get_adj_ccid (p_adjustment_id IN NUMBER) RETURN NUMBER
3576 IS
3577
3578 CURSOR c_adj_ccid (c_adjustment_id NUMBER)
3579 IS
3580 SELECT ard.code_combination_id adj_ccid
3581 FROM ar_adjustments_all adj,
3582 ar_distributions_all ard
3583 WHERE adj.adjustment_id = c_adjustment_id
3584 AND adj.adjustment_id = ard.source_id
3585 AND ard.source_table = 'ADJ'
3586 AND ard.source_type IN ('ADJ', 'FINCHRG');
3587
3588 l_adj_ccid c_adj_ccid%ROWTYPE;
3589
3590 BEGIN
3591
3592 OPEN c_adj_ccid (p_adjustment_id);
3593 FETCH c_adj_ccid INTO l_adj_ccid;
3594 CLOSE c_adj_ccid;
3595
3596 RETURN l_adj_ccid.adj_ccid;
3597
3598 END Get_adj_ccid;
3599
3600 /* ########################################## GET_ADJ_ARD_ID ################################# */
3601
3602 FUNCTION Get_adj_ard_id (p_adjustment_id IN NUMBER) RETURN NUMBER
3603 IS
3604
3605 CURSOR c_adj_ard_id (c_adjustment_id NUMBER)
3606 IS
3607 SELECT ard.line_id adj_ard_id
3608 FROM ar_adjustments_all adj,
3609 ar_distributions_all ard
3610 WHERE adj.adjustment_id = c_adjustment_id
3611 AND adj.adjustment_id = ard.source_id
3612 AND ard.source_table = 'ADJ'
3613 AND ard.source_type IN ('ADJ', 'FINCHRG');
3614
3615 l_adj_ard_id c_adj_ard_id%ROWTYPE;
3616
3617 BEGIN
3618
3619 OPEN c_adj_ard_id (p_adjustment_id);
3620 FETCH c_adj_ard_id INTO l_adj_ard_id;
3621 CLOSE c_adj_ard_id;
3622
3623 RETURN l_adj_ard_id.adj_ard_id;
3624
3625 END Get_adj_ard_id;
3626
3627 /* ########################################## GET_MISC_ARD_ID ################################# */
3628
3629 FUNCTION Get_misc_ard_id (p_misc_cash_dist_id IN NUMBER) RETURN NUMBER
3630 IS
3631
3632 CURSOR c_misc_ard_id (c_misc_cash_dist_id NUMBER)
3633 IS
3634 SELECT ard.line_id misc_ard_id
3635 FROM ar_misc_cash_distributions_all mcd,
3636 ar_distributions_all ard
3637 WHERE mcd.misc_cash_distribution_id = c_misc_cash_dist_id
3638 AND mcd.misc_cash_distribution_id = ard.source_id
3639 AND ard.source_table = 'MCD';
3640
3641 l_misc_ard_id c_misc_ard_id%ROWTYPE;
3642
3643 BEGIN
3644
3645 OPEN c_misc_ard_id (p_misc_cash_dist_id);
3646 FETCH c_misc_ard_id INTO l_misc_ard_id;
3647 CLOSE c_misc_ard_id;
3648
3649 RETURN l_misc_ard_id.misc_ard_id;
3650
3651 END Get_misc_ard_id;
3652
3653 /* ########################################## GET_ENTERED_DR_RCT ################################# */
3654
3655 FUNCTION Get_entered_dr_rct_clear (p_lookup_code IN NUMBER,
3656 p_amount IN NUMBER,
3657 p_curr_status IN VARCHAR2,
3658 p_prev_status IN VARCHAR2) RETURN NUMBER IS
3659 l_return_amount NUMBER;
3660 BEGIN
3661
3662 l_return_amount := p_amount;
3663 IF l_return_amount > 0 THEN
3664 IF p_lookup_code IN (1,3) THEN /* (CR) LINES */
3665 if p_curr_status = 'CLEARED' and p_prev_status = 'REMITTED' then
3666 l_return_amount := NULL;
3667 ELSIF p_curr_status = 'REMITTED' and p_prev_status = 'CLEARED' THEN
3668 l_return_amount := 1* l_return_amount ;
3669 -- NULL;
3670 END IF;
3671 END IF;
3672 IF p_lookup_code IN (2,4) THEN /* ODD (DR) LINES */
3673 if p_curr_status = 'CLEARED' and p_prev_status = 'REMITTED' then
3674 l_return_amount := 1* l_return_amount ;
3675 ELSIF p_curr_status = 'REMITTED' and p_prev_status = 'CLEARED' THEN
3676 l_return_amount := NULL;
3677 END IF;
3678 END IF;
3679 ELSIF l_return_amount < 0 THEN
3680 IF p_lookup_code IN (1,3) THEN /* (CR) LINES */
3681 l_return_amount := -1* l_return_amount;
3682 END IF;
3683 IF p_lookup_code IN (2,4) THEN /* (CR) LINES */
3684 l_return_amount := NULL; ---1* l_return_amount;
3685 END IF;
3686 END IF;
3687
3688 RETURN l_return_amount;
3689 END Get_entered_dr_rct_clear;
3690
3691 /* ########################################## GET_ENTERED_CR_RCT ################################# */
3692
3693 FUNCTION Get_entered_cr_rct_clear (p_lookup_code IN NUMBER,
3694 p_amount IN NUMBER,
3695 p_curr_status IN VARCHAR2,
3696 p_prev_status IN VARCHAR2) RETURN NUMBER IS
3697 l_return_amount NUMBER;
3698 BEGIN
3699
3700 l_return_amount := p_amount;
3701 IF l_return_amount > 0 THEN
3702 IF p_lookup_code IN (2,4) THEN /* (CR) LINES */
3703 if p_curr_status = 'CLEARED' and p_prev_status = 'REMITTED' then
3704 l_return_amount := NULL;
3705 ELSIF p_curr_status = 'REMITTED' and p_prev_status = 'CLEARED' THEN
3706 l_return_amount := 1* l_return_amount ;
3707 END IF;
3708 END IF;
3709 IF p_lookup_code IN (1,3) THEN /* ODD (DR) LINES */
3710 if p_curr_status = 'CLEARED' and p_prev_status = 'REMITTED' then
3711 l_return_amount := 1* l_return_amount ;
3712 ELSIF p_curr_status = 'REMITTED' and p_prev_status = 'CLEARED' THEN
3713 l_return_amount := NULL;
3714 END IF;
3715 END IF;
3716 ELSIF l_return_amount < 0 THEN
3717 IF p_lookup_code IN (2,4) THEN /* (CR) LINES */
3718 l_return_amount := -1*l_return_amount;
3719 END IF;
3720 IF p_lookup_code IN (1,3) THEN /* (CR) LINES */
3721 l_return_amount := null;
3722 END IF;
3723
3724 END if;
3725
3726 RETURN l_return_amount;
3727 END Get_entered_cr_rct_clear;
3728
3729 /* For a Cash Receipt - remitted and then cleared and then reversed - in that order
3730 MFAR generated reversal for any remittance account (core or Multi-fund) is not required. */
3731
3732 FUNCTION clear_reversal_lines(p_lookup_code IN NUMBER,
3733 p_amount IN NUMBER,
3734 p_crh_status IN VARCHAR2,
3735 p_crh_first_record_flag IN VARCHAR2,
3736 p_rev_crh_id IN NUMBER) RETURN varchar2 IS
3737 CURSOR c_crh_parent IS SELECT status FROM ar_cash_receipt_history WHERE
3738 cash_receipt_history_id = p_rev_crh_id;
3739 l_status VARCHAR2(30);
3740 BEGIN
3741 IF p_amount < 0 THEN
3742 IF p_crh_first_record_flag = 'Y' then
3743 if p_crh_status = 'CLEARED' THEN
3744 RETURN 'T';
3745 ELSIF p_crh_status = 'REMITTED' THEN
3746 OPEN c_crh_parent;
3747 FETCH c_crh_parent INTO l_status;
3748 CLOSE c_crh_parent;
3749 IF l_status = 'REVERSED' THEN
3750 RETURN 'T';
3751 ELSIF l_status = 'CLEARED' THEN
3752 IF p_lookup_code IN (1,2) then
3753 RETURN 'F';
3754 END IF;
3755 END IF;
3756 END IF;
3757 ELSIF nvl(p_crh_first_record_flag,'N') = 'N' THEN
3758 IF p_crh_status = 'CLEARED' THEN
3759 IF p_lookup_code in (1,2) then
3760 RETURN 'F';
3761 END IF;
3762 END IF;
3763 END IF;
3764 END IF;
3765 END;
3766
3767 PROCEDURE Mfar_rcpt_to_gl_CB
3768 (errbuf OUT NOCOPY VARCHAR2,
3769 retcode OUT NOCOPY VARCHAR2,
3770 p_set_of_books_id IN NUMBER,
3771 p_gl_date_from IN VARCHAR2,
3772 p_gl_date_to IN VARCHAR2,
3773 p_gl_posted_date IN VARCHAR2,
3774 p_summary_flag IN VARCHAR2) IS
3775
3776 /* The l_ variables used IN this cursor are Global variables in this package */
3777
3778 CURSOR Cur_MFAR_rct_app_id
3779 IS
3780 SELECT distinct ra.receivable_application_id receivable_application_id
3781 FROM ar_receivable_applications ra
3782 WHERE ra.status = 'APP'
3783 AND ra.posting_control_id = l_pst_ctrl_id
3784 AND DECODE(PSA_MFAR_VAL_PKG.ar_mfar_validate_check
3785 (ra.receivable_application_id, 'RCT', l_sob_id) , 'Y', 'MFAR_TYPE', 'NOT_MFAR_TYPE') = 'MFAR_TYPE';
3786
3787 CURSOR Cur_mfar_rct_lines (p_receivable_application_id NUMBER)
3788 IS
3789 SELECT
3790 DECODE(to_number(l1.lookup_code), '4', ra.ussgl_transaction_code,
3791 '8', ra.ussgl_transaction_code,
3792 '12', ra.ussgl_transaction_code, NULL) ussgl,
3793 DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL, l_trade_cat_name, l_ccurr_cat_name),
3794 'CM', l_user_cm_cat_name ) category,
3795 ra.gl_date gl_date,
3796 DECODE (ra.application_type, 'CASH', cr.doc_sequence_id,
3797 'CM', ctcm.doc_sequence_id) doc_seqid,
3798 DECODE (ra.application_type, 'CASH', cr.doc_sequence_value,
3799 'CM', ctcm.doc_sequence_value) doc_num,
3800 cr.currency_code currency,
3801 DECODE (to_NUMBER(l1.lookup_code), 1, psa_rct_dist.mf_cash_ccid,
3802 2, DECODE(ra.application_type, 'CM', psa_mfar_utils.get_rec_ccid (ra.applied_customer_trx_id, ra.customer_trx_id), crh.account_code_combINation_id),
3803 3, ar_trx_dist.code_combINation_id,
3804 4, ar_trx_dist.code_combINation_id,
3805 5, psa_rct_dist.discount_ccid,
3806 6, ra.earned_discount_ccid,
3807 7, ra.code_combINation_id,
3808 8, psa_trx_dist.mf_receivables_ccid,
3809 9, psa_rct_dist.ue_discount_ccid,
3810 10, ra.unearned_discount_ccid,
3811 11, ra.code_combINation_id,
3812 12, psa_trx_dist.mf_receivables_ccid) ccid,
3813 DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
3814 get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
3815 psa_rct_dist.discount_amount,
3816 psa_rct_dist.ue_discount_amount)) entered_dr,
3817 DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
3818 get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
3819 psa_rct_dist.discount_amount,
3820 psa_rct_dist.ue_discount_amount)) entered_cr,
3821 DECODE (ra.application_type, 'CM', get_entered_dr_crm (l1.lookup_code, psa_rct_dist.amount),
3822 get_entered_dr_rct (l1.lookup_code, psa_rct_dist.amount,
3823 psa_rct_dist.discount_amount,
3824 psa_rct_dist.ue_discount_amount)) accounted_dr,
3825 DECODE (ra.application_type, 'CM', get_entered_cr_crm (l1.lookup_code, psa_rct_dist.amount),
3826 get_entered_cr_rct (l1.lookup_code, psa_rct_dist.amount,
3827 psa_rct_dist.discount_amount,
3828 psa_rct_dist.ue_discount_amount)) accounted_cr,
3829 'AR ' || TO_CHAR(l_pst_ctrl_id) ref1,
3830 SUBSTRB (DECODE (l1.lookup_code, '1', DECODE (ra.application_type, 'CM', 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Receivable account for credit memo' || ctcm.trx_NUMBER || '.',
3831 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status,'CLEARED', ' Cash Account for ', 'REMITTED', ' Remittance Account for ')),
3832 '2', DECODE (ra.application_type, 'CM',
3833 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Receivable account for credit memo '|| ctcm.trx_NUMBER || '.',
3834 'MFAR'|| DECODE (sign (ra.amount_applied),-1, '-UNAPP','') || DECODE(crh.status, 'CLEARED', ' Reversal of Cash Account for ' , 'REMITTED', ' Reversal of Remittance Account for ')),
3835 '3', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Reversal of Revenue Account for ',
3836 '4', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' Receipt ',
3837 '5', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct earn-disc): ',
3838 '6', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core earn-disc):' ,
3839 '7', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):' ,
3840 '8', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' ,
3841 '9', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct unearn-disc):',
3842 '10', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core unearn-disc):',
3843 '11', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Reverse core rec):',
3844 '12', 'MFAR' || DECODE (sign(ra.amount_applied),-1, '-UNAPP','') || ' (Correct rec):' )
3845 || DECODE (l_summary_flag, 'Y', NULL, DECODE(ra.application_type,
3846 /* Cash Receipt application */
3847 'CASH', ' '|| cr.receipt_NUMBER || l_app_applied ||
3848 DECODE(ctt.type, 'CB', l_class_cb,
3849 'CM', l_class_cm,
3850 'DEP', l_class_dep,
3851 'DM', l_class_dm,
3852 'GUAR',l_class_guar,
3853 'INV', l_class_inv,NULL) || ' ' || ctinv.trx_NUMBER || l_post_general)),1,240) ref10,
3854 TO_CHAR(l_pst_ctrl_id) ref21,
3855 DECODE (ra.application_type, 'CASH',TO_CHAR(cr.cash_receipt_id), -- || 'C' || TO_CHAR(ra.receivable_application_id),
3856 'CM', TO_CHAR(ra.receivable_application_id)) ref22,
3857 psa_rct_dist.CUST_TRX_LINE_GL_DIST_ID ref23,
3858 DECODE (ra.application_type, 'CASH', cr.receipt_NUMBER,
3859 'CM', ctcm.trx_NUMBER) ref24,
3860 ctinv.trx_NUMBER ref25,
3861 ctt.type ref26,
3862 DECODE (ra.application_type, 'CASH', TO_CHAR(cr.pay_from_customer),
3863 'CM', TO_CHAR(ctcm.bill_to_customer_id)) ref27,
3864 DECODE (ra.application_type, 'CASH', DECODE(ra.amount_applied_from, NULL,'TRADE','CCURR'),
3865 'CM', 'CMAPP') ref28,
3866 DECODE(ra.application_type, 'CASH', DECODE (ra.amount_applied_from, NULL,'TRADE_' ||ar_trx_dist.account_class, 'CCURR_' ||ar_trx_dist.account_class),
3867 'CM', 'CMAPP_'||ar_trx_dist.account_class) ref29,
3868 DECODE(ra.application_type, 'CASH', 'PSA_RCT_DIST',
3869 'CM', 'RA_CUST_TRX_LINE_GL_DIST') ref30
3870 FROM
3871 ar_receivable_applications ra,
3872 ar_cash_receipts cr,
3873 ra_customer_trx ctcm,
3874 ra_customer_trx ctinv,
3875 ra_cust_trx_types ctt,
3876 ar_cash_receipt_history crh,
3877 psa_mf_rct_dist_all psa_rct_dist,
3878 psa_mf_trx_dist_all psa_trx_dist,
3879 ra_cust_trx_line_gl_dist ar_trx_dist,
3880 psa_lookup_codes l1
3881 WHERE
3882 psa_rct_dist.receivable_application_id = p_receivable_application_id
3883 AND psa_rct_dist.ue_discount_ccid IS NULL
3884 AND psa_rct_dist.receivable_application_id = ra.receivable_application_id
3885 AND psa_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
3886 AND ar_trx_dist.cust_trx_line_gl_dist_id = psa_trx_dist.cust_trx_line_gl_dist_id
3887 AND ar_trx_dist.cust_trx_line_gl_dist_id = psa_rct_dist.cust_trx_line_gl_dist_id
3888 /* For MFAR we consider only thr APP rows */
3889 AND ra.status = 'APP'
3890 AND ra.cash_receipt_id = cr.cash_receipt_id(+)
3891 AND ra.customer_trx_id = ctcm.customer_trx_id(+)
3892 AND ra.applied_customer_trx_id = ctinv.customer_trx_id(+)
3893 AND ctinv.cust_trx_type_id = ctt.cust_trx_type_id(+)
3894 AND ra.cash_receipt_id = crh.cash_receipt_id(+)
3895 AND l1.lookup_type = 'PSA_CARTESIAN_JOIN'
3896 AND l1.lookup_code IN ('1','4','5','7','8','9','12',
3897 decode(l_rct_post_det_flag, 'N', -1, 2),
3898 -- decode(l_rct_post_det_flag, 'N', -1, 3),
3899 decode(l_rct_post_det_flag, 'N', -1, 6),
3900 decode(l_rct_post_det_flag, 'N', -1, 10),
3901 decode(l_rct_post_det_flag, 'N', -1, 11))
3902 AND DECODE (ceil(to_NUMBER(l1.lookup_code)/4), 1, nvl(psa_rct_dist.amount,0),
3903 2, nvl(psa_rct_dist.discount_amount,0),
3904 3, nvl(psa_rct_dist.ue_discount_amount,0), 0) <> 0
3905 AND l1.lookup_code <= DECODE(ra.application_type, 'CM', 2, l1.lookup_code)
3906 AND ra.posting_control_id = l_pst_ctrl_id
3907 AND nvl(psa_rct_dist.posting_control_id, -3) = -3
3908 AND crh.status IN ('CLEARED','REMITTED')
3909 AND crh.first_posted_record_flag = 'Y';
3910
3911 CURSOR c_fv_balance_check (c_sob_id NUMBER, c_group_id NUMBER, c_rcv_app_id NUMBER) IS
3912 SELECT to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) cash_receipt_id,
3913 sum(accounted_dr) sum_acctd_dr,
3914 sum(accounted_cr) sum_acctd_cr
3915 FROM gl_interface gl
3916 WHERE gl.user_je_source_name = 'Receivables'
3917 AND gl.set_of_books_id = c_sob_id
3918 AND gl.group_id = c_group_id
3919 AND substr(gl.reference29, 7) IN ('CASH', 'REC')
3920 AND gl.reference10 NOT LIKE 'MFAR%'
3921 AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) IN
3922 (select cash_receipt_id from ar_receivable_applications where receivable_application_id = c_rcv_app_id)
3923 GROUP BY to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1))
3924 HAVING sum(accounted_dr) = sum(accounted_cr);
3925
3926 -- l_run_num NUMBER(15);
3927
3928 l_fv_balance_check c_fv_balance_check%rowtype;
3929 -- ========================= FND LOG ===========================
3930 l_full_path VARCHAR2(100) := g_path || 'Mfar_rcpt_to_gl_CB';
3931 -- ========================= FND LOG ===========================
3932 BEGIN
3933
3934 retcode := 'F';
3935
3936 l_gl_start_date := p_gl_date_from;
3937 l_post_through_date := p_gl_date_to;
3938 l_summary_flag := p_summary_flag;
3939 l_sob_id := p_set_of_books_id;
3940
3941 FOR I IN Cur_MFAR_rct_app_id
3942 LOOP
3943
3944 BEGIN
3945 IF NOT (PSA_MFAR_RECEIPTS.create_distributions
3946 (errbuf => l_errbuf,
3947 retcode => l_retcode,
3948 p_receivable_app_id => I.receivable_application_id,
3949 p_set_of_books_id => l_sob_id,
3950 p_run_id => l_run_num,
3951 p_error_message => l_error_message)) THEN
3952
3953 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
3954 Raise invalid_distribution;
3955 END IF;
3956 END IF;
3957
3958 EXCEPTION
3959 WHEN INVALID_DISTRIBUTION THEN
3960 -- ========================= FND LOG ===========================
3961 psa_utils.debug_other_string(g_excep_level,l_full_path,
3962 ' PSA_XFR_TO_GL_PKG.Mfar_rct_to_gl: Unable to create Multi-Fund distributions for Receivable Application ID: ' || I.receivable_application_id);
3963 psa_utils.debug_other_string(g_excep_level,l_full_path,' Error Message : ' || l_error_message);
3964 -- ========================= FND LOG ===========================
3965 retcode := 'F';
3966
3967 WHEN OTHERS THEN
3968 -- ========================= FND LOG ===========================
3969 psa_utils.debug_other_string(g_excep_level,l_full_path,
3970 ' PSA_XFR_TO_GL_PKG.Mfar_rct_to_gl: Unable to create Multi-Fund distributions for Receivable Application ID: ' || I.receivable_application_id);
3971 psa_utils.debug_other_string(g_excep_level,l_full_path,' Error Message : ' || sqlerrm);
3972 psa_utils.debug_unexpected_msg(l_full_path);
3973 -- ========================= FND LOG ===========================
3974 retcode := 'F';
3975
3976 END;
3977 /* -- 4178626
3978 IF l_post_det_acct_flag = 'N' THEN
3979
3980 OPEN c_fv_balance_check (l_sob_id, l_pst_ctrl_id, I.receivable_application_id);
3981 FETCH c_fv_balance_check
3982 INTO l_fv_balance_check;
3983 CLOSE c_fv_balance_check;
3984
3985 IF (l_fv_balance_check.sum_acctd_dr IS NOT NULL AND
3986 l_fv_balance_check.sum_acctd_cr IS NOT NULL ) THEN
3987
3988 l_rct_post_det_flag := 'N';
3989
3990 DELETE FROM gl_interface gl
3991 WHERE gl.user_je_source_name = 'Receivables'
3992 AND gl.set_of_books_id = l_sob_id
3993 AND gl.group_id = l_pst_ctrl_id
3994 AND substr(gl.reference29, 7) IN ('CASH', 'REC')
3995 AND gl.reference10 NOT LIKE 'MFAR%'
3996 AND to_number(substr(gl.reference22, 1, instr(gl.reference22, 'C')-1)) = l_fv_balance_check.cash_receipt_id;
3997 ELSE
3998 l_rct_post_det_flag := 'Y';
3999 END IF;
4000 END IF;
4001 -- 4178626 */
4002 FOR J IN Cur_mfar_rct_lines (I.receivable_application_id)
4003 LOOP
4004
4005 DELETE FROM gl_interface gl
4006 WHERE gl.user_je_source_name = 'Receivables'
4007 AND gl.set_of_books_id = l_sob_id
4008 AND gl.group_id = l_pst_ctrl_id
4009 AND gl.reference29 = 'TRADE_APP_INV_GL_LINE'
4010 AND gl.reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
4011 AND gl.reference10 NOT LIKE '%MFAR%'
4012 AND to_number(gl.reference22) =
4013 (select cash_receipt_id
4014 from ar_receivable_applications
4015 where receivable_application_id = I.receivable_application_id);
4016
4017 INSERT INTO gl_interface
4018 (created_by,
4019 date_created,
4020 status,
4021 actual_flag,
4022 group_id,
4023 set_of_books_id,
4024 user_je_source_name,
4025 user_je_category_name,
4026 accounting_date,
4027 subledger_doc_sequence_id,
4028 subledger_doc_sequence_value,
4029 currency_code,
4030 code_combINation_id,
4031 entered_dr,
4032 entered_cr,
4033 accounted_dr,
4034 accounted_cr,
4035 reference1,
4036 reference10,
4037 reference21,
4038 reference22,
4039 reference23,
4040 reference24,
4041 reference25,
4042 reference26,
4043 reference27,
4044 reference28,
4045 reference29,
4046 reference30,
4047 ussgl_transaction_code)
4048 VALUES
4049 (l_user_id,
4050 trunc(sysdate),
4051 l_status,
4052 l_actual_flag,
4053 l_pst_ctrl_id,
4054 l_sob_id,
4055 l_source,
4056 J.category,
4057 J.gl_date,
4058 J.doc_seqid,
4059 J.doc_num,
4060 J.currency,
4061 J.ccid,
4062 J.entered_dr,
4063 J.entered_cr,
4064 J.accounted_dr,
4065 J.accounted_cr,
4066 J.ref1,
4067 J.ref10,
4068 J.ref21,
4069 J.ref22,
4070 J.ref23,
4071 J.ref24,
4072 J.ref25,
4073 J.ref26,
4074 J.ref27,
4075 J.ref28,
4076 J.ref29,
4077 J.ref30,
4078 J.ussgl);
4079
4080 END LOOP;
4081 END LOOP;
4082
4083 UPDATE psa_mf_rct_dist_all pda
4084 SET pda.posting_control_id = l_pst_ctrl_id
4085 WHERE pda.receivable_application_id IN
4086 (SELECT receivable_application_id
4087 FROM ar_receivable_applications ara
4088 WHERE ara.posting_control_id = l_pst_ctrl_id);
4089
4090 retcode := 'S';
4091
4092 EXCEPTION
4093 WHEN OTHERS THEN
4094 -- ========================= FND LOG ===========================
4095 psa_utils.debug_other_string(g_excep_level,l_full_path,
4096 ' PSA_XFR_TO_GL_PKG.Mfar_rct_to_gl: Exception : OTHERS ');
4097 psa_utils.debug_other_string(g_excep_level,l_full_path,' Error Message : ' || sqlerrm);
4098 psa_utils.debug_unexpected_msg(l_full_path);
4099 -- ========================= FND LOG ===========================
4100 retcode := 'F';
4101
4102 END Mfar_rcpt_to_gl_CB;
4103
4104 /*###################################### MISC_RCT_TO_GL_CB ###########################################*/
4105
4106 PROCEDURE Misc_rct_to_gl_CB
4107 (errbuf OUT NOCOPY VARCHAR2,
4108 retcode OUT NOCOPY VARCHAR2,
4109 p_set_of_books_id IN NUMBER,
4110 p_gl_date_from IN VARCHAR2,
4111 p_gl_date_to IN VARCHAR2,
4112 p_gl_posted_date IN VARCHAR2)
4113 IS
4114
4115 CURSOR c_crh_post
4116 IS
4117 SELECT cash_receipt_history_id FROM ar_cash_receipt_history_all
4118 WHERE posting_control_id = l_pst_ctrl_id
4119 AND cash_receipt_history_id NOT IN
4120 (SELECT cash_receipt_history_id FROM psa_misc_posting);
4121
4122 CURSOR c_create_dist
4123 IS
4124 SELECT cr.cash_receipt_id FROM ar_cash_receipts_all cr, ar_cash_receipt_history_all crh
4125 WHERE cr.cash_receipt_id = crh.cash_receipt_id
4126 -- AND crh.status = 'CLEARED'
4127 AND fnd_date.date_to_canonical (crh.gl_date) BETWEEN fnd_date.date_to_canonical (TO_DATE (l_gl_start_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'))
4128 AND fnd_date.date_to_canonical (TO_DATE (l_post_through_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'));
4129
4130 CURSOR Cur_MFAR_mrct_lines
4131 IS
4132 SELECT
4133 mfd.gl_date gl_date,
4134 cr.doc_sequence_id doc_seqid,
4135 cr.doc_sequence_value doc_num,
4136 ard.currency_code currency,
4137 decode(to_number(l1.lookup_code),
4138 1, mfd.cash_ccid, 2, ard.code_combination_id)
4139 ccid,
4140 decode(to_number(l1.lookup_code), 1, mcd.amount, 2, Null) entered_dr,
4141 decode(to_number(l1.lookup_code), 1, Null, 2, mcd.amount) entered_cr,
4142 decode(to_number(l1.lookup_code), 1, mcd.acctd_amount, 2, Null) accounted_dr,
4143 decode(to_number(l1.lookup_code), 1, Null, 2, mcd.acctd_amount) accounted_cr,
4144 l_batch_prefix || TO_CHAR(l_pst_ctrl_id) ref1,
4145 DECODE(to_number(l1.lookup_code),1, ('MFAR Misc. Receipt ' || cr.receipt_number),
4146 2,('Receipt ' || cr.receipt_number||'(MFAR)')) ref10,
4147 TO_CHAR (mcd.posting_control_id) ref21,
4148 TO_CHAR (cr.cash_receipt_id) ref22,
4149 TO_CHAR (ard.line_id) ref23,
4150 cr.receipt_number ref24,
4151 TO_CHAR (mcd.misc_cash_distribution_id) ref25,
4152 NULL ref26,
4153 'c1' ref27,
4154 'MISC' ref28,
4155 'MISC_' || ard.source_type ref29,
4156 'PSA_MF_MISC_DIST_ALL' ref30
4157 FROM
4158 psa_mf_misc_dist_all mfd,
4159 psa_lookup_codes l1,
4160 ar_misc_cash_distributions_all mcd,
4161 ar_distributions_all ard,
4162 ar_cash_receipts_all cr,
4163 ar_cash_receipt_history_all crh
4164 WHERE
4165 l1.lookup_type = 'PSA_CARTESIAN_JOIN'
4166 AND l1.lookup_code IN ('1','2')
4167 AND mfd.misc_cash_distribution_id = mcd.misc_cash_distribution_id
4168 AND fnd_date.date_to_canonical (mfd.gl_date) BETWEEN fnd_date.date_to_canonical (TO_DATE (l_gl_start_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'))
4169 AND fnd_date.date_to_canonical (TO_DATE (l_post_through_date ||' 00:00:00','YYYY/MM/DD HH24:MI:SS'))
4170 AND nvl(mfd.posting_control_id,-3) = -3
4171 AND crh.status = mfd.reference1
4172 AND mcd.posting_control_id = l_pst_ctrl_id
4173 AND mcd.set_of_books_id = l_sob_id
4174 AND mcd.cash_receipt_id = cr.cash_receipt_id
4175 AND cr.cash_receipt_id = crh.cash_receipt_id
4176 AND crh.posting_control_id = l_pst_ctrl_id
4177 AND ((crh.first_posted_record_flag = 'Y') OR (crh.current_record_flag = 'Y' AND crh.status = 'REVERSED'))
4178 AND ard.source_table = 'CRH'
4179 AND ard.source_id = crh.cash_receipt_history_id
4180 AND (ard.amount_cr is null or ard.amount_cr > 0);
4181
4182 PSA_MISC_GLX_FAIL EXCEPTION;
4183 -- ========================= FND LOG ===========================
4184 l_full_path VARCHAR2(100) := g_path || 'Misc_rct_to_gl_CB';
4185 -- ========================= FND LOG ===========================
4186
4187 BEGIN
4188
4189 l_gl_start_date := p_gl_date_from;
4190 l_post_through_date := p_gl_date_to;
4191 l_sob_id := p_set_of_books_id;
4192
4193 BEGIN
4194
4195 /*
4196 ## Call Create Misc Distributions program to create Multi-fund Distributions
4197 ## for receipts that fall within the GL DATE parameters.
4198 */
4199
4200 FOR I IN c_create_dist
4201 LOOP
4202 IF (I.cash_receipt_id IS NOT NULL) THEN
4203
4204 IF NOT (PSA_MF_CREATE_DISTRIBUTIONS.create_distributions (errbuf => l_errbuf,
4205 retcode => l_retcode,
4206 p_mode => 'R',
4207 p_document_id => I.cash_receipt_id,
4208 p_set_of_books_id => l_sob_id,
4209 run_num => l_run_num,
4210 p_error_message => l_error_message,
4211 p_report_only => 'N')) THEN
4212
4213
4214 IF l_error_message IS NOT NULL OR l_retcode = 'F' THEN
4215 -- ========================= FND LOG ===========================
4216 psa_utils.debug_other_string(g_excep_level,l_full_path,
4217 'Misc_rct_to_gl: Raising invalid_distribution');
4218 -- ========================= FND LOG ===========================
4219 Raise invalid_distribution;
4220 END IF;
4221
4222 END IF;
4223 END IF;
4224
4225 END LOOP;
4226
4227 EXCEPTION
4228 WHEN INVALID_DISTRIBUTION THEN
4229 -- ========================= FND LOG ===========================
4230 psa_utils.debug_other_string(g_excep_level,l_full_path,
4231 ' p_error_message --> ' || l_error_message);
4232 -- ========================= FND LOG ===========================
4233 retcode := 'F';
4234
4235 WHEN OTHERS THEN
4236 -- ========================= FND LOG ===========================
4237 psa_utils.debug_other_string(g_excep_level,l_full_path,
4238 'EXCEPTION - OTHERS raised during PSA_MF_CREATE_DISTRIBUTIONS.create_distributions ' || 'in PSA_TRANSFER_TO_GL_PKG.Misc_rct_to_gl ');
4239 psa_utils.debug_unexpected_msg(l_full_path);
4240 -- ========================= FND LOG ===========================
4241 errbuf := 2;
4242 retcode := 'F';
4243
4244 END;
4245
4246 /*
4247 ## Insert into GL_INTERFACE Select from psa_mf_misc_dist_all
4248 */
4249
4250 FOR J IN Cur_MFAR_mrct_lines
4251 LOOP
4252
4253 /*
4254 ## For each misc_cash_distribution_id the record will be inserted.
4255 */
4256
4257 INSERT INTO gl_interface
4258 (created_by,
4259 date_created,
4260 status,
4261 actual_flag,
4262 group_id,
4263 set_of_books_id,
4264 user_je_source_name,
4265 user_je_category_name,
4266 accounting_date,
4267 subledger_doc_sequence_id,
4268 subledger_doc_sequence_value,
4269 currency_code,
4270 code_combination_id,
4271 entered_dr,
4272 entered_cr,
4273 accounted_dr,
4274 accounted_cr,
4275 reference1,
4276 reference10,
4277 reference21,
4278 reference22,
4279 reference23,
4280 reference24,
4281 reference25,
4282 reference26,
4283 reference27,
4284 reference28,
4285 reference29,
4286 reference30)
4287 VALUES (l_user_id,
4288 trunc(sysdate),
4289 l_status,
4290 l_actual_flag,
4291 l_pst_ctrl_id,
4292 l_sob_id,
4293 l_source,
4294 l_misc_cat_name,
4295 J.gl_date,
4296 J.doc_seqid,
4297 J.doc_num,
4298 J.currency,
4299 J.ccid,
4300 J.entered_dr,
4301 J.entered_cr,
4302 J.accounted_dr,
4303 J.accounted_cr,
4304 J.ref1,
4305 J.ref10,
4306 J.ref21,
4307 J.ref22,
4308 J.ref23,
4309 J.ref24,
4310 J.ref25,
4311 J.ref26,
4312 J.ref27,
4313 J.ref28,
4314 J.ref29,
4315 J.ref30);
4316
4317 END LOOP;
4318
4319 /*
4320 ## Insert a record into psa_misc_posting to keep track of
4321 ## each reversing record of AR_CASH_RECEIPT_HISTORY, that we insert into GL_INTERFACE
4322 */
4323
4324 FOR J IN c_crh_post
4325 LOOP
4326 INSERT INTO psa_misc_posting (cash_receipt_history_id, posting_control_id)
4327 VALUES (J.cash_receipt_history_id, l_pst_ctrl_id);
4328
4329 END LOOP;
4330
4331 UPDATE psa_mf_misc_dist_all
4332 SET posting_control_id = l_pst_ctrl_id
4333 WHERE misc_cash_distribution_id IN
4334 (SELECT misc_cash_distribution_id FROM ar_misc_cash_distributions_all
4335 WHERE posting_control_id = l_pst_ctrl_id);
4336
4337 EXCEPTION
4338 WHEN OTHERS THEN
4339 -- ========================= FND LOG ===========================
4340 psa_utils.debug_other_string(g_excep_level,l_full_path,
4341 'EXCEPTION - OTHERS raised during in PSA_TRANSFER_TO_GL_PKG.Misc_rct_to_gl - rolling back ');
4342 psa_utils.debug_other_string(g_excep_level,l_full_path, sqlcode || sqlerrm);
4343 psa_utils.debug_unexpected_msg(l_full_path);
4344 -- ========================= FND LOG ===========================
4345 errbuf := 2;
4346 retcode := 'F';
4347
4348 END Misc_rct_to_gl_CB;
4349
4350 /* ########################################## END OF PSA_TRSNAFER_TO_GL_PKG ################################# */
4351
4352 END psa_xfr_to_gl_pkg;