1 PACKAGE BODY AP_TRIAL_BALANCE_PKG AS
2 /* $Header: aptrbalb.pls 120.2 2004/06/04 23:09:22 yicao noship $ */
3
4 /*=============================================================================
5 This is the main entry routine for the Trial Balance Report. This function will
6 be called by the APXTRBAL.rdf the report itself and it passes the following
7 arguments:
8
9 p_accounting_date - Accounting Date (Required)
10 p_from_date - Exclude invoices Prior to this Date.
11 p_request_id - Concurrent Request ID
12 p_reporting_entity_id - Will be org_id, legal_entity_id or set_of_books_id
13 according to the reporting level. This is the cross
14 reporting paramter.
15 p_org_where_alb - Multi ORG WHERE condition for cross org reporting
16 represents alias to ap_liability_balance.
17 p_org_where_ael - Multi ORG WHERE condition for cross org reporting
18 represents alias to ap_ae_lines_all.
19 p_org_where_asp - Multi ORG WHERE condition for cross org reporting
20 represents alias to ap_system_parameters_all.
21 p_neg_bal_only - Report on Negative Balances only parameter.
22 p_debug_switch - Debug Switch.
23
24 This function returns TRUE on successful completion and FALSE on any error.
25
26 Logic:
27 ======
28
29 1. Delete all records from AP_TRIAL_BAL table
30 2. If Exclude invoices from date is not provided,
31 2.1 Calls the Insert AP_TRIAL_BAL function without from date.
32 2.2 validates for this given ORG condition if there exists atleast
33 one record that has future_dated_pmt_liab_relief = MATURITY.
34 If so calls the future dated payments insert for AP_TRIAL_BAL
35 2.3 If the report is submitted only for Negative Balances calls the
36 process negative balances routine to remove all the possitive
37 balances records.
38 3. Else Processes the same logic mentioned above for p_from_date case.
39 3.1 Calls the Insert AP_TRIAL_BAL function with from date.
40 3.2 validates for this given ORG condition if there exists atleast
41 one record that has future_dated_pmt_liab_relief = MATURITY.
42 If so calls the future dated payments insert for AP_TRIAL_BAL
43 3.3 If the report is submitted only for Negative Balances calls the
44 process negative balances routine to remove all the possitive
45 balances records.
46 =============================================================================*/
47
48
49 FUNCTION Process_Trial_Balance (
50 p_accounting_date IN DATE,
51 p_from_date IN DATE,
52 p_request_id IN NUMBER,
53 p_reporting_entity_id IN NUMBER,
54 p_org_where_alb IN VARCHAR2,
55 p_org_where_ael IN VARCHAR2,
56 p_org_where_asp IN VARCHAR2,
57 p_neg_bal_only IN VARCHAR2,
58 p_debug_switch IN VARCHAR2)
59 RETURN BOOLEAN IS
60
61 future_dated_pmts_used BOOLEAN;
62
63 BEGIN
64
65 fnd_file.put_line (fnd_file.log, 'Stage :001 - Into Process_Trial_Balance');
66 fnd_file.put_line (fnd_file.log, 'Stage :002 - Delete Existing ap_trial_bal'
67 ||' records.');
68
69 DELETE FROM ap_trial_bal;
70
71 fnd_file.put_line (fnd_file.log, 'Stage :003 - Insert AP_Trial_Bal Info.');
72
73 IF (p_from_date IS NULL) THEN
74
75 fnd_file.put_line (fnd_file.log, 'Stage :004 - Into From Date Null Case');
76
77 IF (Insert_AP_Trial_Bal (p_accounting_date,
78 p_request_id,
79 p_reporting_entity_id,
80 p_org_where_alb,
81 p_org_where_ael,
82 p_debug_switch) <> TRUE) THEN
83 RETURN FALSE;
84
85 END IF;
86
87 fnd_file.put_line (fnd_file.log, 'Stage :005 - Verify Future Dated '
88 ||'Payments Used.');
89
90 future_dated_pmts_used := Use_Future_Dated(p_org_where_asp,
91 p_debug_switch);
92
93 fnd_file.put_line (fnd_file.log, 'Stage :006 - Insert Future Dated if '
94 ||' applicable.');
95
96 IF (future_dated_pmts_used) THEN
97
98 fnd_file.put_line (fnd_file.log, 'Stage :007 - Into Insert Future Dated '
99 ||'Payments Block');
100
101 IF (Insert_Future_Dated (p_accounting_date,
102 p_request_id,
103 p_reporting_entity_id,
104 p_org_where_ael,
105 p_debug_switch) <> TRUE) THEN
106
107 RETURN FALSE;
108
109 END IF;
110
111 END IF;
112
113 fnd_file.put_line (fnd_file.log, 'Stage :008 - Negative Balances');
114
115 IF (NVL(p_neg_bal_only,'N') = 'Y') THEN
116
117 fnd_file.put_line (fnd_file.log, 'Stage :009 - Into Negative Balances'
118 ||' Block');
119
120 IF (Process_Neg_Bal (p_request_id) <> TRUE) THEN
121
122 RETURN (TRUE);
123
124 END IF;
125
126 END IF;
127
128 ELSE
129
130 fnd_file.put_line (fnd_file.log, 'Stage :010 - Into From Date Case');
131
132 IF (Insert_AP_Trial_Bal (p_accounting_date,
133 p_from_date,
134 p_request_id,
135 p_reporting_entity_id,
136 p_org_where_alb,
137 p_org_where_ael,
138 p_debug_switch) <> TRUE) THEN
139 RETURN FALSE;
140
141 END IF;
142
143 fnd_file.put_line (fnd_file.log, 'Stage :011 - Verify Future Dated '
144 ||'Payments Used.');
145
146 future_dated_pmts_used := Use_Future_Dated(p_org_where_asp,
147 p_debug_switch);
148
149 fnd_file.put_line (fnd_file.log, 'Stage :012 - Insert Future Dated if '
150 ||' applicable.');
151
152 IF (future_dated_pmts_used) THEN
153
154 fnd_file.put_line (fnd_file.log, 'Stage :013 - Into Insert Future Dated '
155 ||'Payments Block');
156
157 IF (Insert_Future_Dated (p_accounting_date,
158 p_from_date,
159 p_request_id,
160 p_reporting_entity_id,
161 p_org_where_ael,
162 p_debug_switch) <> TRUE) THEN
163 RETURN FALSE;
164
165 END IF;
166
167 END IF;
168
169 fnd_file.put_line (fnd_file.log, 'Stage :014 - Negative Balances');
170
171 IF (NVL(p_neg_bal_only,'N') = 'Y') THEN
172
173 fnd_file.put_line (fnd_file.log, 'Stage :015 - Into Negative Balances'
174 ||' Block');
175
176 IF (Process_Neg_Bal (p_request_id) <> TRUE) THEN
177
178 RETURN FALSE;
179
180 END IF;
181
182 END IF;
183
184 END IF;
185
186 RETURN TRUE;
187
188 EXCEPTION
189
190 WHEN OTHERS THEN
191
192 fnd_file.put_line(fnd_file.log,'Error Occured in Process_Trial_Balance'
193 ||' Function.');
194 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
195 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
196
197 RETURN FALSE;
198
199
200 END Process_Trial_Balance;
201
202 /*=============================================================================
203 Insert_AP_Trial_Bal Function is an overloaded function. Based on the
204 p_from_date option either of the function will be called. This function returns
205 TRUE on success and FALSE on any errors.
206
207 This procedure inserts records into AP_TRIAL_BAL table for a given org_id or
208 for set of orgs as per the parameter for AP and AX set of books. This inserts
209 invoices that have not been fully paid on or before for a given as of date.
210
211 For AP the insert gets the information from the AP_LIABILITY_BALANCE. As of
212 now this table is populated only by AP, While transferring information to
213 GL.
214 For AX the insert gets the information from the AX views namely:
215 ax_ap_ae_lines_all_v and ax_ap_ae_headers_all_v.
216
217 Note:
218 =====
219 1) Trial Balance will report based on AP accounting data for all pre 11i
220 transactions irrespective of customers using AX or AP. For Post 11i trial
221 balance will report based on the accounting information from either AX or AP
222 as it is being used.
223
224 The UNION SELECT is written to handle the same requirement.
225
226 =============================================================================*/
227 FUNCTION Insert_AP_Trial_Bal (
228 p_accounting_date IN DATE,
229 p_request_id IN NUMBER,
230 p_reporting_entity_id IN NUMBER,
231 p_org_where_alb IN VARCHAR2,
232 p_org_where_ael IN VARCHAR2,
233 p_debug_switch IN VARCHAR2)
234 RETURN BOOLEAN IS
235
236 l_sql_stmt VARCHAR2(32000);
237
238 BEGIN
239
240 fnd_file.put_line (fnd_file.log, 'Stage :016 - Into Insert_AP_Trial_Bal');
241
242 l_sql_stmt:= 'INSERT INTO ap_trial_bal '
243 || ' SELECT alb.invoice_id invoice_id, '
244 || ' alb.code_combination_id code_combination_id, '
245 || ' SUM (alb.accounted_cr) - '
246 || ' SUM (alb.accounted_dr) remaining_amount, '
247 || ' alb.vendor_id vendor_id, '
248 || ' alb.set_of_books_id set_of_books_id, '
249 || ' alb.org_id org_id, '
250 || ' '||p_request_id||' request_id, '
251 || ' SUM(ae_invoice_amount) invoice_amount '
252 || ' FROM ap_liability_balance alb '
253 || ' WHERE trunc(accounting_date) <= '
254 || ' trunc(to_date('''||p_accounting_date||''',''YYYY/MM/DD'')) '
255 || p_org_where_alb
256 || ' GROUP BY '
257 || ' alb.invoice_id, '
258 || ' alb.code_combination_id, '
259 || ' alb.vendor_id, '
260 || ' alb.set_of_books_id, '
261 || ' alb.org_id, '
262 || ' '||p_request_id||' '
263 || ' HAVING SUM (accounted_cr) <> SUM (accounted_dr) ';
264
265 IF (p_debug_switch IN ('y','Y')) THEN
266 fnd_file.put_line(fnd_file.log,l_sql_stmt);
267 END IF;
268
269 EXECUTE IMMEDIATE l_sql_stmt;
270
271 RETURN TRUE;
272
273 EXCEPTION
274
275 WHEN OTHERS THEN
276
277 fnd_file.put_line(fnd_file.log,'Error Occured in Insert_AP_Trial_Bal'
278 ||' Function.');
279 IF (p_debug_switch IN ('y','Y')) THEN
280 fnd_file.put_line(fnd_file.log,l_sql_stmt);
281 END IF;
282 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
283 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
284
285 RETURN FALSE;
286
287 END Insert_AP_Trial_Bal;
288
289 /*=============================================================================
290 Insert_AP_Trial_Bal Function is an overloaded function. Same as the previous
291 function. But this will be called only if the p_from_date is provided.
292
293 =============================================================================*/
294 FUNCTION Insert_AP_Trial_Bal (
295 p_accounting_date IN DATE,
296 p_from_date IN DATE,
297 p_request_id IN NUMBER,
298 p_reporting_entity_id IN NUMBER,
299 p_org_where_alb IN VARCHAR2,
300 p_org_where_ael IN VARCHAR2,
301 p_debug_switch IN VARCHAR2)
302 RETURN BOOLEAN IS
303
304 l_sql_stmt VARCHAR2(32000);
305
306 BEGIN
307
308 fnd_file.put_line (fnd_file.log, 'Stage :017 - Into Insert_AP_Trial_Bal');
309
310 l_sql_stmt := 'INSERT INTO ap_trial_bal '
311 || ' SELECT alb.invoice_id invoice_id, '
312 || ' alb.code_combination_id code_combination_id, '
313 || ' SUM (alb.accounted_cr) - '
314 || ' SUM (alb.accounted_dr) remaining_amount, '
315 || ' alb.vendor_id vendor_id, '
316 || ' alb.set_of_books_id set_of_books_id, '
317 || ' alb.org_id org_id, '
318 || ' '||p_request_id||' request_id, '
319 || ' SUM(ae_invoice_amount) invoice_amount '
320 || ' FROM ap_liability_balance alb, '
321 || ' ap_invoices_all ai '
322 || ' WHERE ai.invoice_id = alb.invoice_id '
323 || ' AND trunc(alb.accounting_date) <= '
324 || ' trunc(to_date('''||p_accounting_date||''',''YYYY/MM/DD'')) '
325 || ' AND ai.invoice_date >= to_date('''||p_from_date||''',''YYYY/MM/DD'') '
326 || p_org_where_alb
327 || ' GROUP BY '
328 || ' alb.invoice_id, '
329 || ' alb.code_combination_id, '
330 || ' alb.vendor_id, '
331 || ' alb.set_of_books_id, '
332 || ' alb.org_id, '
333 || ' '||p_request_id||' '
334 || ' HAVING SUM (accounted_cr) <> SUM (accounted_dr) ';
335
336 IF (p_debug_switch IN ('y','Y')) THEN
337 fnd_file.put_line(fnd_file.log,l_sql_stmt);
338 END IF;
339
340 EXECUTE IMMEDIATE l_sql_stmt;
341
342 RETURN TRUE;
343
344
345 EXCEPTION
346
347 WHEN OTHERS THEN
348
349 fnd_file.put_line(fnd_file.log,'Error Occured in Insert_AP_Trial_Bal'
350 ||' Function.');
351 IF (p_debug_switch IN ('y','Y')) THEN
352 fnd_file.put_line(fnd_file.log,l_sql_stmt);
353 END IF;
354 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
355 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
356 RETURN FALSE;
357
358 END Insert_AP_Trial_Bal;
359
360 /*=============================================================================
361 Insert_Future_Dated Function is an overloaded function. Based on the
362 p_from_date option either of the function will be called. This function returns
363 TRUE on success and FALSE on any errors.
364
365 This procedure inserts records into AP_TRIAL_BAL table for a given org_id or
366 for set of orgs as per the parameter for AP and AX set of books. This inserts
367 invoices that have not been fully paid on or before for a given as of date
368 associated to the future dated payments.
369
370 Note:
371 =====
372 1) Trial Balance will report based on AP accounting data for all pre 11i
373 transactions irrespective of customers using AX or AP. For Post 11i trial
374 balance will report based on the accounting information from either AX or AP
375 as it is being used.
376
377 The UNION SELECT is written to handle the same requirement.
378
379 =============================================================================*/
380
381
382 FUNCTION Insert_Future_Dated (
383 p_accounting_date IN DATE,
384 p_request_id IN NUMBER,
385 p_reporting_entity_id IN NUMBER,
386 p_org_where_ael IN VARCHAR2,
387 p_debug_switch IN VARCHAR2)
388 RETURN BOOLEAN IS
389
390 l_sql_stmt VARCHAR2(32000);
391 l_sql_stmt_1 VARCHAR2(32000);
392
393 BEGIN
394
395 fnd_file.put_line (fnd_file.log, 'Stage :018 - Into Insert_Future_Dated');
396 fnd_file.put_line (fnd_file.log, 'Stage :019 - Gain Loss At Payment '
397 ||'Line Level');
398
399 l_sql_stmt_1 := 'INSERT INTO ap_trial_bal '
400 || '(( '
401 || ' SELECT /*+ full(aeh) '
402 || ' parallel(aeh,DEFAULT) '
403 || ' parallel(ael,DEFAULT) '
404 || ' use_hash(aeh,ael) */ '
405 || ' ai.invoice_id invoice_id, '
406 || ' ael.code_combination_id code_combination_id, '
407 || ' SUM(NVL(ael.accounted_cr,0)) - '
408 || ' SUM(NVL(ael.accounted_dr,0)) remaining_amount, '
409 || ' ael.third_party_id vendor_id, '
410 || ' aeh.set_of_books_id set_of_books_id, '
411 || ' ael.org_id org_id, '
412 || ' '||p_request_id||' request_id, '
413 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
414 || ' aeh.set_of_books_id, '
415 || ' ai.invoice_id, '
416 || ' ai.invoice_amount, '
417 || ' NVL(ai.exchange_rate,1)) invoice_amount '
418 || ' FROM ap_ae_lines_all ael, '
419 || ' ap_ae_headers_all aeh, '
420 || ' ap_invoice_payments_all aip, '
421 || ' ap_invoices_all ai, '
422 || ' ap_system_parameters_all asp '
423 || ' WHERE ael.ae_line_type_code = ''FUTURE PAYMENT'' '
424 || ' AND ael.ae_header_id = aeh.ae_header_id '
425 || ' AND aeh.gl_transfer_flag = ''Y'' '
426 || ' AND trunc(aeh.accounting_date) <= '
427 || ' trunc(to_date('''||p_accounting_date||''',''YYYY/MM/DD'')) '
428 || ' AND ael.source_table = ''AP_INVOICE_PAYMENTS'' '
429 || ' AND ael.source_id = aip.invoice_payment_id '
430 || ' AND aip.invoice_id = ai.invoice_id '
431 || ' AND nvl(ael.org_id,-99) = nvl(asp.org_id,-99) '
432 || ' AND asp.future_dated_pmt_liab_relief = ''MATURITY'' '
433 || p_org_where_ael
434 || ' GROUP BY '
435 || ' ai.invoice_id, '
436 || ' ael.code_combination_id, '
437 || ' ael.third_party_id, '
438 || ' aeh.set_of_books_id, '
439 || ' ael.org_id, '
440 || ' '||p_request_id||', '
441 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
442 || ' aeh.set_of_books_id, '
443 || ' ai.invoice_id, '
444 || ' ai.invoice_amount, '
445 || ' NVL(ai.exchange_rate,1)) '
446 || ' HAVING SUM(NVL(ael.accounted_cr,0)) <> SUM(NVL(ael.accounted_dr,0)) '
447 || ' ) '
448 || ' UNION '
449 || ' ( '
450 || ' SELECT ai.invoice_id invoice_id, '
451 || ' ael.code_combination_id code_combination_id, '
452 || ' SUM(NVL(ael.accounted_cr,0)) - '
453 || ' SUM(NVL(ael.accounted_dr,0)) remaining_amount, '
454 || ' ai.vendor_id vendor_id, '
455 || ' aeh.set_of_books_id set_of_books_id, '
456 || ' ael.org_id org_id, '
457 || ' '||p_request_id||' request_id, '
458 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
459 || ' aeh.set_of_books_id, '
460 || ' ai.invoice_id, '
461 || ' ai.invoice_amount, '
462 || ' NVL(ai.exchange_rate,1)) invoice_amount '
463 || ' FROM ax_ap_ae_lines_all_v ael, '
464 || ' ax_ap_ae_headers_all_v aeh, '
465 || ' ap_invoice_payments_all aip, '
466 || ' ap_invoices_all ai, '
467 || ' ap_system_parameters_all asp '
468 || ' WHERE ael.ae_line_type_code = ''FUTURE PAYMENT'' '
469 || ' AND ael.set_of_books_id = aeh.set_of_books_id '
470 || ' AND ael.journal_sequence_id = aeh.journal_sequence_id '
471 || ' AND ael.ae_header_id = aeh.ae_header_id '
472 || ' AND aeh.gl_transfer_flag = ''Y'' '
473 || ' AND aeh.accounting_date <= to_date('''||p_accounting_date||''',''YYYY/MM/DD'') '
474 || ' AND ael.last_updated_by <> -6672 '
475 || ' AND ael.source_table = ''AP_INVOICE_PAYMENTS'' '
476 || ' AND ael.source_id = aip.invoice_payment_id '
477 || ' AND aip.invoice_id = ai.invoice_id '
478 || ' AND nvl(ael.org_id,-99) = nvl(asp.org_id,-99) '
479 || ' AND asp.future_dated_pmt_liab_relief = ''MATURITY'' '
480 || p_org_where_ael
481 || ' GROUP BY '
482 || ' ai.invoice_id, '
483 || ' ael.code_combination_id, '
484 || ' ai.vendor_id, '
485 || ' aeh.set_of_books_id, '
486 || ' ael.org_id, '
487 || ' '||p_request_id||', '
488 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
489 || ' aeh.set_of_books_id, '
490 || ' ai.invoice_id, '
491 || ' ai.invoice_amount, '
492 || ' NVL(ai.exchange_rate,1)) '
493 || ' HAVING SUM(NVL(ael.accounted_cr,0)) <> SUM(NVL(ael.accounted_dr,0)) '
494 || ' ) '
495 || ') ';
496
497 IF (p_debug_switch IN ('y','Y')) THEN
498 fnd_file.put_line(fnd_file.log,l_sql_stmt_1);
499 END IF;
500
501 EXECUTE IMMEDIATE l_sql_stmt_1;
502
503 RETURN TRUE;
504
505 EXCEPTION
506
507 WHEN OTHERS THEN
508
509 fnd_file.put_line(fnd_file.log,'Error Occured in Insert_Future_Dated'
510 ||' Function.');
511 IF (p_debug_switch IN ('y','Y')) THEN
512 fnd_file.put_line(fnd_file.log,l_sql_stmt);
513 fnd_file.put_line(fnd_file.log,l_sql_stmt_1);
514 END IF;
515 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
516 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
517 RETURN FALSE;
518
519 END Insert_Future_Dated;
520
521 /*=============================================================================
522 Insert_Future_Dated Function is an overloaded function. This function
523 will be called when the p_from_date is provided. The functionality remains the
524 same as mentioned in the Insert_Future_Dated function1 above.
525
526 =============================================================================*/
527
528
529 FUNCTION Insert_Future_Dated (
530 p_accounting_date IN DATE,
531 p_from_date IN DATE,
532 p_request_id IN NUMBER,
533 p_reporting_entity_id IN NUMBER,
534 p_org_where_ael IN VARCHAR2,
535 p_debug_switch IN VARCHAR2)
536 RETURN BOOLEAN IS
537
538 l_sql_stmt VARCHAR2(32000);
539 l_sql_stmt_1 VARCHAR2(32000);
540
541 BEGIN
542
543 fnd_file.put_line (fnd_file.log, 'Stage :020 - Into Insert_Future_Dated');
544 fnd_file.put_line (fnd_file.log, 'Stage :021 - Gain Loss At Payment '
545 ||'Line Level');
546 l_sql_stmt_1 := 'INSERT INTO ap_trial_bal '
547 || '(( '
548 || ' SELECT /*+ full(aeh) '
549 || ' parallel(aeh,DEFAULT) '
550 || ' parallel(ael,DEFAULT) '
551 || ' use_hash(aeh,ael) */ '
552 || ' ai.invoice_id invoice_id, '
553 || ' ael.code_combination_id code_combination_id, '
554 || ' SUM(NVL(ael.accounted_cr,0)) - '
555 || ' SUM(NVL(ael.accounted_dr,0)) remaining_amount, '
556 || ' ael.third_party_id vendor_id, '
557 || ' aeh.set_of_books_id set_of_books_id, '
558 || ' ael.org_id org_id, '
559 || ' '||p_request_id||' request_id, '
560 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
561 || ' aeh.set_of_books_id, '
562 || ' ai.invoice_id, '
563 || ' ai.invoice_amount, '
564 || ' NVL(ai.exchange_rate,1)) invoice_amount '
565 || ' FROM ap_ae_lines_all ael, '
566 || ' ap_ae_headers_all aeh, '
567 || ' ap_invoice_payments_all aip, '
568 || ' ap_invoices_all ai, '
569 || ' ap_system_parameters_all asp '
570 || ' WHERE ael.ae_line_type_code = ''FUTURE PAYMENT'' '
571 || ' AND ai.invoice_date >= to_date('''||p_from_date||''',''YYYY/MM/DD'') '
572 || ' AND ael.ae_header_id = aeh.ae_header_id '
573 || ' AND aeh.gl_transfer_flag = ''Y'' '
574 || ' AND trunc(aeh.accounting_date) <= '
575 || ' trunc(to_date('''||p_accounting_date||''',''YYYY/MM/DD'')) '
576 || ' AND ael.source_table = ''AP_INVOICE_PAYMENTS'' '
577 || ' AND ael.source_id = aip.invoice_payment_id '
578 || ' AND aip.invoice_id = ai.invoice_id '
579 || ' AND nvl(ael.org_id,-99) = nvl(asp.org_id,-99) '
580 || ' AND asp.future_dated_pmt_liab_relief = ''MATURITY'' '
581 || p_org_where_ael
582 || ' GROUP BY '
583 || ' ai.invoice_id, '
584 || ' ael.code_combination_id, '
585 || ' ael.third_party_id, '
586 || ' aeh.set_of_books_id, '
587 || ' ael.org_id, '
588 || ' '||p_request_id||', '
589 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
590 || ' aeh.set_of_books_id, '
591 || ' ai.invoice_id, '
592 || ' ai.invoice_amount, '
593 || ' NVL(ai.exchange_rate,1)) '
594 || ' HAVING SUM(NVL(ael.accounted_cr,0)) <> SUM(NVL(ael.accounted_dr,0)) '
595 || ' ) '
596 || ' UNION '
597 || ' ( '
598 || ' SELECT ai.invoice_id invoice_id, '
599 || ' ael.code_combination_id code_combination_id, '
600 || ' SUM(NVL(ael.accounted_cr,0)) - '
601 || ' SUM(NVL(ael.accounted_dr,0)) remaining_amount, '
602 || ' ai.vendor_id vendor_id, '
603 || ' aeh.set_of_books_id set_of_books_id, '
604 || ' ael.org_id org_id, '
605 || ' '||p_request_id||' request_id, '
606 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
607 || ' aeh.set_of_books_id, '
608 || ' ai.invoice_id, '
609 || ' ai.invoice_amount, '
610 || ' NVL(ai.exchange_rate,1)) invoice_amount '
611 || ' FROM ax_ap_ae_lines_all_v ael, '
612 || ' ax_ap_ae_headers_all_v aeh, '
613 || ' ap_invoice_payments_all aip, '
614 || ' ap_invoices_all ai, '
615 || ' ap_system_parameters_all asp '
616 || ' WHERE ael.ae_line_type_code = ''FUTURE PAYMENT'' '
617 || ' AND ai.invoice_date >= to_date('''||p_from_date||''',''YYYY/MM/DD'') '
618 || ' AND ael.set_of_books_id = aeh.set_of_books_id '
619 || ' AND ael.journal_sequence_id = aeh.journal_sequence_id '
620 || ' AND ael.ae_header_id = aeh.ae_header_id '
621 || ' AND aeh.gl_transfer_flag = ''Y'' '
622 || ' AND aeh.accounting_date <= to_date('''||p_accounting_date||''',''YYYY/MM/DD'') '
623 || ' AND ael.last_updated_by <> -6672 '
624 || ' AND ael.source_table = ''AP_INVOICE_PAYMENTS'' '
625 || ' AND ael.source_id = aip.invoice_payment_id '
626 || ' AND aip.invoice_id = ai.invoice_id '
627 || ' AND nvl(ael.org_id,-99) = nvl(asp.org_id,-99) '
628 || ' AND asp.future_dated_pmt_liab_relief = ''MATURITY'' '
629 || p_org_where_ael
630 || ' GROUP BY '
631 || ' ai.invoice_id, '
632 || ' ael.code_combination_id, '
633 || ' ai.vendor_id, '
634 || ' aeh.set_of_books_id, '
635 || ' ael.org_id, '
636 || ' '||p_request_id||', '
637 || ' Ap_Trial_Balance_Pkg.Get_Invoice_Amount ( '
638 || ' aeh.set_of_books_id, '
639 || ' ai.invoice_id, '
640 || ' ai.invoice_amount, '
641 || ' NVL(ai.exchange_rate,1)) '
642 || ' HAVING SUM(NVL(ael.accounted_cr,0)) <> SUM(NVL(ael.accounted_dr,0)) '
643 || ' ) '
644 || ') ';
645
646 IF (p_debug_switch IN ('y','Y')) THEN
647 fnd_file.put_line(fnd_file.log,l_sql_stmt_1);
648 END IF;
649
650 EXECUTE IMMEDIATE l_sql_stmt_1;
651
652 RETURN TRUE;
653
654 EXCEPTION
655
656 WHEN OTHERS THEN
657
658 fnd_file.put_line(fnd_file.log,'Error Occured in Insert_Future_Dated'
659 ||' Function.');
660 IF (p_debug_switch IN ('y','Y')) THEN
661 fnd_file.put_line(fnd_file.log,l_sql_stmt);
662 fnd_file.put_line(fnd_file.log,l_sql_stmt_1);
663 END IF;
664 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
665 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
666 RETURN FALSE;
667
668 END Insert_Future_Dated;
669
670 /*=============================================================================
671 Process_Neg_Bal function - This function is used to get rid of the records
672 that add to a possitive balance in the AP_TRIAL_BAL table. So that when the
673 trial balance select statement gets executed will report only on the negative
674 balances only. This was an added feature to the trial balance report to replace
675 the supplier open balance report.
676 =============================================================================*/
677
678
679 FUNCTION Process_Neg_Bal(p_request_id IN NUMBER)
680 RETURN BOOLEAN IS
681
682 BEGIN
683
684 fnd_file.put_line (fnd_file.log, 'Stage :024 - Into Process_Neg_Bal');
685
686 DELETE FROM ap_trial_bal
687 WHERE (code_combination_id,
688 vendor_id,
689 set_of_books_id,
690 nvl(org_id,-99)) --Bug2679383 Added nvl to org_id passing -99 for
691 -- non-multi org.
692 IN
693 (SELECT code_combination_id,
694 vendor_id,
695 set_of_books_id,
696 nvl(org_id,-99) --Bug2679383 Added nvl to org_id passing -99
697 --for non-multi org.
698 FROM ap_trial_bal
699 WHERE request_id = p_request_id
700 GROUP BY
701 code_combination_id,
702 vendor_id,
703 set_of_books_id,
704 org_id
705 HAVING sum(remaining_amount) > 0);
706
707 RETURN TRUE;
708
709 EXCEPTION
710
711 WHEN OTHERS THEN
712
713 fnd_file.put_line(fnd_file.log,'Error Occured in Process_Neg_Bal'
714 ||' Function.');
715 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
716 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
717 RETURN FALSE;
718
719 END Process_Neg_Bal;
720
721 /*=============================================================================
722 Use_Future_Dated function is used to verify for the given org or set of orgs
723 if there exists atleast one ORG has future_dated_pmt_liab_relief vlaue set to
724 MATURITY in ap_system_parameters_all. If so we should call the Insert Future
725 Dated payments function. The function will return TRUE if there exists atleast
726 one org that satisfies the requirement, else will return FALSE.
727
728 =============================================================================*/
729
730
731 FUNCTION Use_Future_Dated (
732 p_org_where_asp IN VARCHAR2,
733 p_debug_switch IN VARCHAR2)
734 RETURN BOOLEAN IS
735
736 l_is_future_dated NUMBER;
737 l_sql_stmt VARCHAR2(32000);
738
739 BEGIN
740
741 l_sql_stmt := 'SELECT COUNT(*) '
742 || 'FROM ap_system_parameters_all asp '
743 || 'WHERE asp.future_dated_pmt_liab_relief = ''MATURITY'' '
744 || p_org_where_asp;
745
746 IF (p_debug_switch IN ('y','Y')) THEN
747 fnd_file.put_line(fnd_file.log,l_sql_stmt);
748 END IF;
749
750 EXECUTE IMMEDIATE l_sql_stmt INTO l_is_future_dated;
751
752 IF l_is_future_dated <> 0 THEN
753 RETURN TRUE;
754 ELSE
755 RETURN FALSE;
756 END IF;
757
758 EXCEPTION
759
760 WHEN OTHERS THEN
761
762 fnd_file.put_line(fnd_file.log,'Error Occured in Use_Future_Dated'
763 ||' Function.');
764 IF (p_debug_switch IN ('y','Y')) THEN
765 fnd_file.put_line(fnd_file.log,l_sql_stmt);
766 END IF;
767 fnd_file.put_line(fnd_file.log,'Error Code: '||to_char(SQLCODE));
768 fnd_file.put_line(fnd_file.log,'Error Message: '||SQLERRM);
769
770 END Use_Future_Dated;
771
772 /*=============================================================================
773 Insert_AP_Liability_Balance function is used to populate the
774 AP_LIABILITY_BALANCE table. This will be called from the APXGLTRN.rdf report
775 after the GL transfer has been completed successfully.
776
777 It takes the following arguments:
778
779 p_request_id - Concurrent Request ID
780 p_user_id - Application User ID
781 p_resp_appl_id - Application ID
782 p_login_id - Last Update Login ID
783 p_program_id - Concurrent Program ID
784 p_program_appl_id - Concurrent Program Application ID.
785
786 Logic:
787 ======
788
789 1) For a given request_id the function first determines the list of
790 gl_transfer_run_id from xla_gl_transfer_batches_all.
791 2) Inserts the AP_LIABILITY_BALANCE with the denormalized information
792 from ap_ae_lines_all and ap_ae_headers_all for a given gl_transfer_run_id.
793 3) Calls the Update trial_balance_flag of ap_ae_headers_all for the same
794 gl_transfer_run_id, so that we can make sure that accounting entry
795 lines of type LIABILITY associsted with the header record have been
796 transferred to the AP_LIABILITY_BALANCE table.
797
798 Bug Fixes:
799
800 Bug 2284841
801 Bug 2319648 - Prepayment Application Case, Standard Invoice is
802 not displayed with the right invoice_amount.
803 =============================================================================*/
804
805 FUNCTION Insert_AP_Liability_Balance (
806 p_request_id IN NUMBER,
807 p_user_id IN NUMBER,
808 p_resp_appl_id IN NUMBER,
809 p_login_id IN NUMBER,
810 p_program_id IN NUMBER,
811 p_program_appl_id IN NUMBER)
812 RETURN BOOLEAN IS
813
814 -- Bug 2284841 Code Modified by MSWAMINA.
815
816 CURSOR transfer_info IS
817 SELECT DISTINCT (xgt.gl_transfer_run_id)
818 FROM xla_gl_transfer_batches_all xgt,
819 ap_ae_headers_all aeh
820 WHERE xgt.gl_transfer_run_id = aeh.gl_transfer_run_id
821 AND xgt.request_id = p_request_id
822 AND nvl(aeh.trial_balance_flag,'N') = 'N';
823
824 l_gl_transfer_run_id xla_gl_transfer_batches_all.gl_transfer_run_id%TYPE;
825
826 BEGIN
827
828 fnd_file.put_line(fnd_file.log,'Into Insert_AP_Liability_Balance Procedure');
829
830 fnd_file.put_line(fnd_file.log,'Open transfer_info cursor');
831
832 OPEN transfer_info;
833
834 LOOP
835
836 FETCH transfer_info INTO l_gl_transfer_run_id;
837 EXIT WHEN transfer_info%NOTFOUND;
838
839 fnd_file.put_line(fnd_file.log,'Insert ap_liability_balance for every'
840 ||'gl_transfer_run_id');
841
842 fnd_file.put_line(fnd_file.log,'Processing gl_transfer_run_id : '
843 ||l_gl_transfer_run_id);
844
845 INSERT INTO ap_liability_balance
846 (ae_line_id,
847 ae_header_id,
848 invoice_id,
849 code_combination_id,
850 vendor_id,
851 vendor_site_id,
852 set_of_books_id,
853 org_id,
854 accounting_date,
855 accounted_dr,
856 accounted_cr,
857 ae_invoice_amount,
858 creation_date,
859 created_by,
860 last_update_date,
861 last_updated_by,
862 last_update_login,
863 program_update_date,
864 program_application_id,
865 program_id,
866 request_id)
867 (
868 SELECT
869 ael.ae_line_id ae_line_id,
870 ael.ae_header_id ae_header_id,
871 to_number(NVL(ael.reference2,0)) invoice_id,
872 ael.code_combination_id code_combination_id,
873 ael.third_party_id vendor_id,
874 ael.third_party_sub_id vendor_site_id,
875 aeh.set_of_books_id set_of_books_id,
876 ael.org_id org_id,
877 aeh.accounting_date accounting_date,
878 NVL(ael.accounted_dr,0) accounted_dr,
879 NVL(ael.accounted_cr,0) accounted_cr,
880 -- Bug 2319648
881 DECODE(aae.event_type_code,
882 'PREPAYMENT APPLICATION',
883 0,
884 'PREPAYMENT UNAPPLICATION',
885 0,
886 DECODE(ael.source_table,
887 'AP_INVOICES', (NVL(ael.accounted_cr,0) -
888 NVL(ael.accounted_dr,0)),
889 'AP_INVOICE_DISTRIBUTIONS', (NVL(ael.accounted_cr,0) -
890 NVL(ael.accounted_dr,0)),
891 0)),
892 SYSDATE,
893 p_user_id,
894 SYSDATE,
895 p_user_id,
896 p_login_id,
897 SYSDATE,
898 p_program_appl_id,
899 p_program_id,
900 p_request_id
901 FROM ap_ae_headers_all aeh,
902 ap_ae_lines_all ael,
903 ap_accounting_events_all aae
904 WHERE aae.accounting_event_id = aeh.accounting_event_id
905 AND aeh.ae_header_id = ael.ae_header_id
906 AND ael.ae_line_type_code = 'LIABILITY'
907 AND aeh.gl_transfer_flag = 'Y'
908 AND aeh.gl_transfer_run_id = l_gl_transfer_run_id
909 );
910
911 fnd_file.put_line(fnd_file.log,'Update AE Headers trial_balance_flag');
912
913 IF (Update_Trial_Balance_Flag (l_gl_transfer_run_id) <> TRUE) THEN
914
915 RETURN FALSE;
916
917 END IF;
918
919 fnd_file.put_line(fnd_file.log,'Processed gl_transfer_run_id : '
920 ||l_gl_transfer_run_id);
921
922 END LOOP;
923
924 fnd_file.put_line(fnd_file.log,'Close transfer_info cursor');
925
926 CLOSE transfer_info;
927
928 RETURN (TRUE);
929
930 EXCEPTION
931
932 WHEN OTHERS THEN
933
934 fnd_file.put_line(fnd_file.log,'Error Occured in '
935 ||'Insert_AP_Liability_Balance '
936 ||'Procedure');
937 fnd_file.put_line(fnd_file.log, 'Error Code: '||SQLCODE);
938 fnd_file.put_line(fnd_file.log, 'Error Message: '|| SQLERRM);
939
940 IF transfer_info%ISOPEN THEN
941 CLOSE transfer_info;
942 END IF;
943
944 RETURN (FALSE);
945
946
947 END Insert_AP_Liability_Balance;
948
949 /*=============================================================================
950 Update_Trial_Balance_Flag Function - is a local function called by the
951 Insert_AP_Liability_Balance to update the ap_ae_header_all trial_balance_flag
952 . This flag represents the accounting entry lines of type LIABILTIY have been
953 successfully inserted for a header record to the AP_LIABILTY_BALANCE table.
954 =============================================================================*/
955
956
957 FUNCTION Update_Trial_Balance_Flag (
958 p_gl_transfer_run_id IN NUMBER)
959 RETURN BOOLEAN IS
960
961 BEGIN
962
963 fnd_file.put_line(fnd_file.log, 'Into Update_Trial_Balance_Flag Procedure');
964 fnd_file.put_line(fnd_file.log, 'Updating AE Headers for '
965 ||p_gl_transfer_run_id);
966
967 UPDATE ap_ae_headers_all
968 SET trial_balance_flag = 'Y'
969 WHERE gl_transfer_run_id = p_gl_transfer_run_id;
970
971 RETURN TRUE;
972
973
974 EXCEPTION
975
976 WHEN OTHERS THEN
977 fnd_file.put_line(fnd_file.log, 'Error Occured in Update_Trial_Balance_Flag'
978 ||' Procedure while processing '
979 ||' the following gl_transfer_run_id: '
980 || p_gl_transfer_run_id);
981 fnd_file.put_line(fnd_file.log, 'Error Code: '||SQLCODE);
982 fnd_file.put_line(fnd_file.log, 'Error Message: '|| SQLERRM);
983 RETURN FALSE;
984
985 END Update_Trial_Balance_Flag;
986
987 /*=============================================================================
988 Is_Reporting_Books Function is used to identify whether the given set of books
989 ID is a reporting set of books or not. If yes this function will return TRUE.
990 else will return FALSE.
991
992 This is a local function used by the Get_Invoice_Amount function.
993
994 It takes the following Parameters:
995 p_set_of_books_id - Set of Books identifier.
996 =============================================================================*/
997
998
999 FUNCTION Is_Reporting_Books (
1000 p_set_of_books_id IN NUMBER)
1001 RETURN BOOLEAN IS
1002
1003 l_set_of_books_type gl_sets_of_books.mrc_sob_type_code%TYPE;
1004
1005 BEGIN
1006
1007 SELECT mrc_sob_type_code
1008 INTO l_set_of_books_type
1009 FROM gl_sets_of_books
1010 WHERE set_of_books_id = p_set_of_books_id;
1011
1012 IF l_set_of_books_type = 'R' THEN
1013 RETURN TRUE;
1014 ELSE
1015 RETURN FALSE;
1016 END IF;
1017
1018 EXCEPTION
1019
1020 WHEN OTHERS THEN
1021 RETURN FALSE;
1022
1023 END Is_Reporting_Books;
1024
1025 /*=============================================================================
1026 Get_Base_Currency_Code function is used to get the base currency code for a
1027 given set of books id.
1028
1029 This is a local function used by the Get_Invoice_Amount function.
1030
1031 It takes the following Parameters:
1032 p_set_of_books_id - Set of Books identifier.
1033 =============================================================================*/
1034
1035
1036 FUNCTION Get_Base_Currency_Code (
1037 p_set_of_books_id IN NUMBER)
1038 RETURN VARCHAR2 IS
1039
1040 l_currency_code ap_system_parameters_all.base_currency_code%TYPE;
1041
1042 BEGIN
1043
1044 SELECT base_currency_code
1045 INTO l_currency_code
1046 FROM ap_system_parameters_all
1047 WHERE set_of_books_id = p_set_of_books_id;
1048
1049 RETURN l_currency_code;
1050
1051 EXCEPTION
1052
1053 WHEN OTHERS THEN
1054 l_currency_code := '';
1055 RETURN l_currency_code;
1056
1057 END Get_Base_Currency_Code;
1058
1059 /*=============================================================================
1060 Get_Invoice_Amount is the function added in sync with resolving the issue
1061 with Trial balance report not displaying the Invoice_amount in the funcitonal
1062 currency with the right proportion when customer uses automatic offsets.
1063
1064 As a part of enhancement to the trial balance we have added new column named
1065 ae_invoice_amount in the AP_LIABILITY_BALANCE table. This will store the
1066 invoice amount in the functional currency (for the appropriate set of books
1067 includes MRC books as trial balance reports on MRC books as well) from the
1068 accounting tables, not only at the invoice level but also at the CCID (Liability)
1069 within the invoice. But the same cannot be directly applied in couple of cases
1070 like
1071 1) As of now AX does not populate the AP_LIABILITY_BALANCE table. So we will
1072 not be able to derive the amounts per liability CCID within an
1073 Invoice for AX. So we will substitute the Actual invoice_amount in functional
1074 currency using this API.
1075
1076 2) For the Future dated payments the decision to report them in trial balance
1077 is driven completely based on the payments and not much based on the invoice.
1078 So based on the discussion (Omar and Lauren) , we will again report the
1079 invoice_amount in functional currency.
1080
1081 This Function will be called in all the Insert statements except the one that
1082 gets the information from AP_LIABILITY_BALANCE table.
1083
1084 It takes the following Arguments:
1085
1086 p_set_of_books_id - Set of Books Identifier.
1087 p_invoice_id - Invoice Identifier
1088 p_invoice_amount - Invoice Amount in the Entered Currency
1089 p_exchange_rate - Exchange rate on the Invoice.
1090
1091 Logic:
1092 ======
1093
1094 1) Verify the set of books ID mentioned is a reporting SOBs.
1095 2) If reporting then
1096 2.1) Call the MRC API to get the Base amount for the Invoice. As the base
1097 amount has to be in the Reporting books currency.
1098 3) Else
1099 3.1) Calculate the Base amount using the entered invoice_amount and
1100 the exchange rate.
1101 4) Return this amount to the Insert statement.
1102
1103 Note:
1104 =====
1105
1106 If an error happen in any of these APIs due to any reason we will not abort the
1107 report. We will return the original invoice amount back.
1108 =============================================================================*/
1109
1110 FUNCTION Get_Invoice_Amount (
1111 p_set_of_books_id IN NUMBER,
1112 p_invoice_id IN NUMBER,
1113 p_invoice_amount IN NUMBER,
1114 p_exchange_rate IN NUMBER)
1115 RETURN NUMBER IS
1116
1117 l_invoice_amount NUMBER;
1118 l_table_name VARCHAR2(30) := 'AP_INVOICES';
1119 l_req_info VARCHAR2(30) := 'BASE_AMOUNT';
1120 l_currency_code ap_system_parameters_all.base_currency_code%TYPE;
1121
1122 BEGIN
1123
1124 IF Is_Reporting_Books(p_set_of_books_id) = TRUE THEN
1125
1126 l_invoice_amount := GL_MC_INFO.Get_Acctd_Amount(p_invoice_id,
1127 p_set_of_books_id,
1128 l_table_name,
1129 l_req_info);
1130
1131 ELSE
1132
1133 l_currency_code := Get_Base_Currency_Code(p_set_of_books_id);
1134
1135 l_invoice_amount := AP_UTILITIES_PKG.Ap_Round_Currency
1136 ((p_invoice_amount *
1137 p_exchange_rate),
1138 l_currency_code);
1139 END IF;
1140
1141 RETURN l_invoice_amount;
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 l_invoice_amount := p_invoice_amount;
1146 RETURN l_invoice_amount;
1147
1148 END Get_Invoice_Amount;
1149
1150
1151
1152
1153 END AP_TRIAL_BALANCE_PKG;
[Home] [Help]