[Home] [Help]
PACKAGE BODY: APPS.AR_CALC_AGING
Source
1 PACKAGE BODY ar_calc_aging AS
2 /* $Header: ARRECONB.pls 120.22.12010000.2 2008/11/05 05:37:45 npanchak ship $ */
3 /*-------------------------------------------------------------
4 PRIVATE variables
5 ---------------------------------------------------------------*/
6
7 company_segment_where VARCHAR2(500) := NULL;
8 br_enabled_flag VARCHAR2(1) := NULL;
9 l_gl_dist_table VARCHAR2(50) := NULL;
10 l_ps_table VARCHAR2(50) := NULL;
11 l_trx_table VARCHAR2(50) := NULL;
12 l_line_table VARCHAR2(50) := NULL;
13 l_ra_table VARCHAR2(50) := NULL;
14 l_ard_table VARCHAR2(50) := NULL;
15 l_adj_table VARCHAR2(50) := NULL;
16 l_cr_table VARCHAR2(50) := NULL;
17 l_ps_org_where VARCHAR2(2000) := NULL;
18 l_gl_dist_org_where VARCHAR2(2000) := NULL;
19 l_trx_org_where VARCHAR2(2000) := NULL;
20 l_line_org_where VARCHAR2(2000) := NULL;
21 l_ra_org_where VARCHAR2(2000) := NULL;
22 l_ard_org_where VARCHAR2(2000) := NULL;
23 l_ard1_org_where VARCHAR2(2000) := NULL;
24 l_ath_org_where VARCHAR2(2000) := NULL;
25 l_adj_org_where VARCHAR2(2000) := NULL;
26 l_cr_org_where VARCHAR2(2000) := NULL;
27
28 PROCEDURE build_parameters(p_reporting_level IN VARCHAR2,
29 p_reporting_entity_id IN NUMBER,
30 p_co_seg_low IN VARCHAR2,
31 p_co_seg_high IN VARCHAR2,
32 p_coa_id IN NUMBER)
33 IS
34 BEGIN
35
36 ar_calc_aging.g_reporting_entity_id := p_reporting_entity_id;
37
38 IF NVL(ar_calc_aging.ca_sob_type,'P') = 'P' THEN
39 l_ps_table := 'ar_payment_schedules_all ';
40 l_ra_table := 'ar_receivable_applications_all ';
41 l_adj_table := 'ar_adjustments_all ';
42 l_ard_table := 'ar_distributions_all ';
43 l_gl_dist_table := 'ra_cust_trx_line_gl_dist_all ';
44 l_line_table := 'ra_customer_trx_lines_all ';
45 l_trx_table := 'ra_customer_trx_all ';
46 l_cr_table := 'ar_cash_receipts_all ';
47 ELSE
48 l_ps_table := 'ar_payment_schedules_all_mrc_v ';
49 l_ra_table := 'ar_receivable_apps_all_mrc_v ';
50 l_adj_table := 'ar_adjustments_all_mrc_v ';
51 l_ard_table := 'ar_distributions_all_mrc_v ';
52 l_gl_dist_table := 'ra_trx_line_gl_dist_all_mrc_v ';
53 l_line_table := 'ra_cust_trx_ln_all_mrc_v ';
54 l_trx_table := 'ra_customer_trx_all_mrc_v ';
55 l_cr_table := 'ar_cash_receipts_all_mrc_v ';
56 END IF;
57
58 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
59
60 l_ps_org_where := XLA_MO_REPORTING_API.Get_Predicate('ps',null);
61 l_gl_dist_org_where:= XLA_MO_REPORTING_API.Get_Predicate('gl_dist', null);
62 l_trx_org_where := XLA_MO_REPORTING_API.Get_Predicate('trx', null);
63 l_line_org_where := XLA_MO_REPORTING_API.Get_Predicate('lines',null);
64 l_ra_org_where := XLA_MO_REPORTING_API.Get_Predicate('ra' , null);
65 l_ard_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard',null);
66 l_ard1_org_where := XLA_MO_REPORTING_API.Get_Predicate('ard1',null);
67 l_ath_org_where := XLA_MO_REPORTING_API.Get_Predicate('ath' ,null);
68 l_adj_org_where := XLA_MO_REPORTING_API.Get_Predicate('adj' ,null);
69 l_cr_org_where := XLA_MO_REPORTING_API.Get_Predicate('cr' ,null);
70
71 /* Replace the variables to bind with the function calls so that we don't have to bind those */
72 l_ps_org_where := replace(l_ps_org_where,
73 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
74 l_gl_dist_org_where:= replace(l_gl_dist_org_where,
75 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
76 l_trx_org_where := replace(l_trx_org_where,
77 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
78 l_line_org_where := replace(l_line_org_where,
79 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
80 l_ra_org_where := replace(l_ra_org_where,
81 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
82 l_ard_org_where := replace(l_ard_org_where,
83 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
84 l_ard1_org_where := replace(l_ard1_org_where,
85 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
86 l_ath_org_where := replace(l_ath_org_where,
87 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
88 l_adj_org_where := replace(l_adj_org_where,
89 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
90 l_cr_org_where := replace(l_cr_org_where,
91 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
92
93 IF company_segment_where IS NULL THEN
94 IF p_co_seg_low IS NULL AND p_co_seg_high IS NULL THEN
95 company_segment_where := NULL;
96 ELSIF p_co_seg_low IS NULL THEN
97 company_segment_where := ' AND ' ||
98 ar_calc_aging.FLEX_SQL(p_application_id => 101,
99 p_id_flex_code => 'GL#',
100 p_id_flex_num =>p_coa_id,
101 p_table_alias => 'GC',
102 p_mode => 'WHERE',
103 p_qualifier => 'GL_BALANCING',
104 p_function => '<=',
105 p_operand1 => p_co_seg_high);
106 ELSIF p_co_seg_high IS NULL THEN
107 company_segment_where := ' AND ' ||
108 ar_calc_aging.FLEX_SQL(p_application_id => 101,
109 p_id_flex_code => 'GL#',
110 p_id_flex_num => p_coa_id,
111 p_table_alias => 'GC',
112 p_mode => 'WHERE',
113 p_qualifier => 'GL_BALANCING',
114 p_function => '>=',
115 p_operand1 => p_co_seg_low);
116 ELSE
117 company_segment_where := ' AND ' ||
118 ar_calc_aging.FLEX_SQL(p_application_id => 101,
119 p_id_flex_code => 'GL#',
120 p_id_flex_num =>p_coa_id,
121 p_table_alias => 'GC',
122 p_mode => 'WHERE',
123 p_qualifier => 'GL_BALANCING',
124 p_function => 'BETWEEN',
125 p_operand1 => p_co_seg_low,
126 p_operand2 => p_co_seg_high);
127 END IF;
128
129 END IF;
130
131 END build_parameters;
132
133 /*========================================================================+
134 Function which returns the global variable g_reporting_entity_id
135 ========================================================================*/
136
137 FUNCTION get_reporting_entity_id return NUMBER is
138 BEGIN
139 return ar_calc_aging.g_reporting_entity_id;
140 END get_reporting_entity_id;
141
142
143 /*========================================================================+
144 Wrapper procedures for the APIS available in FA_RX_FLEX_SQL package
145 When patch 4128137 is released, we need to replace this call with the
146 corresponding FND API calls
147 ========================================================================*/
148 FUNCTION flex_sql(
149 p_application_id in number,
150 p_id_flex_code in varchar2,
151 p_id_flex_num in number default null,
152 p_table_alias in varchar2,
153 p_mode in varchar2,
154 p_qualifier in varchar2,
155 p_function in varchar2 default null,
156 p_operand1 in varchar2 default null,
157 p_operand2 in varchar2 default null) return varchar2 IS
158
159 l_ret_param varchar2(2000);
160
161 BEGIN
162
163 /* This is a wrapper function for the fa_rx_flex_pkg.flex_sql
164 When patch 4128137 is released, we need to replace this call with the corresponding
165 FND API calls */
166
167 l_ret_param := fa_rx_flex_pkg.flex_sql(
168 p_application_id => p_application_id,
169 p_id_flex_code => p_id_flex_code,
170 p_id_flex_num => p_id_flex_num,
171 p_table_alias => p_table_alias,
172 p_mode => p_mode,
173 p_qualifier => p_qualifier,
174 p_function => p_function,
175 p_operand1 => p_operand1,
176 p_operand2 => p_operand2);
177
178 return l_ret_param;
179
180 END flex_sql;
181
182
183 FUNCTION get_value(
184 p_application_id in number,
185 p_id_flex_code in varchar2,
186 p_id_flex_num in number default NULL,
187 p_qualifier in varchar2,
188 p_ccid in number) return varchar2 IS
189
190 l_value varchar2(2000);
191
192 BEGIN
193 /* This is a wrapper function for the fa_rx_flex_pkg.get_value
194 When patch 4128137 is released, we need to replace this call with the corresponding
195 FND API calls */
196
197 l_value := fa_rx_flex_pkg.get_value (
198 p_application_id => p_application_id,
199 p_id_flex_code => p_id_flex_code,
200 p_id_flex_num => p_id_flex_num,
201 p_qualifier => p_qualifier,
202 p_ccid => p_ccid);
203
204 return l_value;
205
206 END get_value;
207
208 FUNCTION get_description(
209 p_application_id in number,
210 p_id_flex_code in varchar2,
211 p_id_flex_num in number default NULL,
212 p_qualifier in varchar2,
213 p_data in varchar2) return varchar2 IS
214
215 l_description varchar2(2000);
216 l_account varchar2(30);
217
218 BEGIN
219 /* This is a wrapper function for the fa_rx_flex_pkg.get_description
220 When patch 4128137 is released, we need to replace this call with the corresponding
221 FND API calls */
222
223 l_account := get_value(p_application_id => p_application_id,
224 p_id_flex_code => p_id_flex_code,
225 p_id_flex_num => p_id_flex_num,
226 p_qualifier => p_qualifier,
227 p_ccid => p_data);
228
229 l_description := fa_rx_flex_pkg.get_description(
230 p_application_id => p_application_id,
231 p_id_flex_code => p_id_flex_code,
232 p_id_flex_num => p_id_flex_num,
233 p_qualifier => p_qualifier,
234 p_data => l_account);
235
236 return l_description;
237
238 END get_description;
239
240
241 PROCEDURE initialize
242 IS
243 l_profile_rsob_id NUMBER := NULL;
244 l_client_info_rsob_id NUMBER := NULL;
245 BEGIN
246 --Bug 4928220
247 ar_calc_aging.ca_sob_type := 'P';
248
249 END;
250
251 /*-------------------------------------------------------------
252 PUBLIC PROCEDURE aging
253 ---------------------------------------------------------------*/
254 PROCEDURE aging_as_of(
255 p_as_of_date_from IN DATE,
256 p_as_of_date_to IN DATE,
257 p_reporting_level IN VARCHAR2,
258 p_reporting_entity_id IN NUMBER,
259 p_co_seg_low IN VARCHAR2,
260 p_co_seg_high IN VARCHAR2,
261 p_coa_id IN NUMBER,
262 p_begin_bal OUT NOCOPY NUMBER,
263 p_end_bal OUT NOCOPY NUMBER,
264 p_acctd_begin_bal OUT NOCOPY NUMBER,
265 p_acctd_end_bal OUT NOCOPY NUMBER) IS
266 l_ps_select VARCHAR2(5000);
267 l_ra_select VARCHAR2(5000);
268 l_cm_ra_select VARCHAR2(5000);
269 l_adj_select VARCHAR2(5000);
270 l_cancel_br_select VARCHAR2(5000);
271 l_trx_main_select VARCHAR2(32000);
272 l_br_select VARCHAR2(5000);
273 l_br_app_select VARCHAR2(5000);
274 l_br_adj_select VARCHAR2(5000);
275 l_br_main_select VARCHAR2(32000);
276 l_unapp_select VARCHAR2(5000);
277 l_main_select VARCHAR2(32000);
278 v_cursor NUMBER;
279 l_ignore INTEGER;
280 l_customer_trx_id NUMBER;
281
282 BEGIN
283
284 COMMIT;
285 SET TRANSACTION READ ONLY;
286
287 build_parameters (p_reporting_level,
288 p_reporting_entity_id,
289 p_co_seg_low,
290 p_co_seg_high,
291 p_coa_id);
292
293 l_ps_select := 'SELECT ps.customer_trx_id ,
294 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
295 NULL,:p_as_of_date_from)
296 * ps.amount_due_remaining) start_bal,
297 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
298 NULL,:p_as_of_date_to)
299 * ps.amount_due_remaining) end_bal,
300 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
301 NULL,:p_as_of_date_from)
302 * ps.acctd_amount_due_remaining) acctd_start_bal,
303 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
304 NULL,:p_as_of_date_to)
305 * ps.acctd_amount_due_remaining) acctd_end_bal
306 FROM '||l_ps_table||' ps
307 WHERE ps.payment_schedule_id+0 > 0
308 AND ps.gl_date_closed >= :p_as_of_date_from
309 AND ps.class IN ( ''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
310 AND ps.gl_date <= :p_as_of_date_to
311 '|| l_ps_org_where ||'
312 GROUP BY ps.customer_trx_id ' ;
313
314 l_ra_select := 'SELECT
315 ps.customer_trx_id ,
316 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
317 ra.gl_date,:p_as_of_date_from)
318 * ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
319 + NVL(ra.unearned_discount_taken,0))) start_bal,
320 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
321 ra.gl_date,:p_as_of_date_to)
322 * ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
323 + NVL(ra.unearned_discount_taken,0))) end_bal,
324 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
325 ra.gl_date,:p_as_of_date_from)
326 * (ra.acctd_amount_applied_to +
327 NVL(ra.acctd_earned_discount_taken,0)
328 + NVL(ra.acctd_unearned_discount_taken,0))) acctd_start_bal,
329 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
330 ra.gl_date,:p_as_of_date_to)
331 * (ra.acctd_amount_applied_to +
332 NVL(ra.acctd_earned_discount_taken,0)
333 + NVL(ra.acctd_unearned_discount_taken,0))) acctd_end_bal
334 FROM '|| l_ps_table ||' ps,
335 '|| l_ra_table ||' ra
336 WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
337 AND ps.payment_schedule_id+0 > 0
338 AND ps.gl_date_closed >= :p_as_of_date_from
339 AND ps.class IN ( ''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
340 AND ra.gl_date > :p_as_of_date_from
341 AND ra.status = ''APP''
342 AND ps.gl_date <= :p_as_of_date_to
343 AND NVL(ra.confirmed_flag,''Y'') = ''Y''
344 '|| l_ps_org_where||'
345 '|| l_ra_org_where||'
346 GROUP BY ps.customer_trx_id ';
347
348 l_cm_ra_select := 'SELECT
349 ps.customer_trx_id ,
350 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
351 ra.gl_date,:p_as_of_date_from)
352 * -1
353 * ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
354 + NVL(ra.unearned_discount_taken,0))) start_bal,
355 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
356 ra.gl_date,:p_as_of_date_to)
357 * -1
358 * ( ra.amount_applied + NVL(ra.earned_discount_taken,0)
359 + NVL(ra.unearned_discount_taken,0))) end_bal,
360 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
361 ra.gl_date,:p_as_of_date_from)
362 * -1
363 * ra.acctd_amount_applied_from ) acctd_start_bal,
364 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
365 ra.gl_date,:p_as_of_date_to)
366 * -1
367 * ra.acctd_amount_applied_from ) acctd_end_bal
368 FROM '|| l_ps_table ||' ps,
369 '|| l_ra_table ||' ra
370 WHERE ra.payment_schedule_id = ps.payment_schedule_id
371 AND ps.payment_schedule_id+0 > 0
372 AND ps.gl_date_closed >= :p_as_of_date_from
373 AND ps.class = ''CM''
374 AND ra.gl_date > :p_as_of_date_from
375 AND ra.status IN (''APP'',''ACTIVITY'') --bug 5290086
376 AND ra.application_type = ''CM''
377 AND ps.gl_date <= :p_as_of_date_to
378 AND NVL(ra.confirmed_flag,''Y'') = ''Y''
379 '|| l_ps_org_where||'
380 '|| l_ra_org_where||'
381 GROUP BY ps.customer_trx_id ';
382
383 l_adj_select := 'SELECT ps.customer_trx_id,
384 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
385 adj.gl_date,:p_as_of_date_from)
386 * adj.amount) start_bal,
387 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
388 adj.gl_date,:p_as_of_date_to)
389 * adj.amount) end_bal ,
390 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
391 adj.gl_date,:p_as_of_date_from)
392 * adj.acctd_amount) acctd_start_bal,
393 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
394 adj.gl_date,:p_as_of_date_to)
395 * adj.acctd_amount) acctd_end_bal
396 FROM '||l_adj_table||' adj ,'
397 ||l_ps_table ||' ps
398 WHERE ps.payment_schedule_id + 0 > 0
399 AND ps.gl_date_closed >= :p_as_of_date_from
400 AND ps.class IN ( ''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
401 AND ps.gl_date <= :p_as_of_date_to
402 AND adj.payment_schedule_id = ps.payment_schedule_id
403 AND adj.gl_date > :p_as_of_date_from
404 AND adj.status = ''A''
405 '|| l_adj_org_where||'
406 '|| l_ps_org_where|| '
407 GROUP BY ps.customer_trx_id ';
408
409 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
410 l_cancel_br_select := 'SELECT
411 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
412 ath.gl_date,:p_as_of_date_from)
413 * decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
414 (ard.amount_cr * -1))) start_bal,
415 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
416 ath.gl_date,:p_as_of_date_to)
417 * decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
418 (ard.amount_cr * -1))) end_bal,
419 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
420 ath.gl_date,:p_as_of_date_from)
421 * decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
422 (ard.acctd_amount_cr * -1))) acctd_start_bal,
423 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
424 ath.gl_date,:p_as_of_date_to)
425 * decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
426 (ard.acctd_amount_cr * -1))) acctd_end_bal
427 FROM '||l_ps_table||' ps,
428 '||l_ard_table || ' ard,
429 '||'ar_transaction_history_all ath,
430 '||l_line_table|| ' lines,
431 gl_code_combinations gc
432 WHERE ps.payment_schedule_id+0 > 0
433 AND ps.gl_date_closed >= :p_as_of_date_from
434 AND ps.class IN ( ''BR'',''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
435 AND ath.gl_date > :p_as_of_date_from
436 AND ath.event = ''CANCELLED''
437 AND ps.gl_date <= :p_as_of_date_to
438 AND ps.customer_trx_id = ath.customer_trx_id
439 AND ard.source_table = ''TH''
440 AND ard.source_id = ath.transaction_history_id
441 AND ps.customer_trx_id = lines.customer_trx_id
442 AND ard.source_id_secondary = lines.customer_trx_line_id
443 AND ard.code_combination_id = gc.code_combination_id
444 ' || l_ps_org_where ||'
445 ' || l_ard_org_where||'
446 ' || l_ath_org_where||'
447 ' || l_line_org_where ||'
448 ' || company_segment_where;
449 ELSE
450 l_cancel_br_select := 'SELECT
451 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
452 ath.gl_date,:p_as_of_date_from)
453 * decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
454 (ard.amount_cr * -1))) start_bal,
455 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
456 ath.gl_date,:p_as_of_date_to)
457 * decode(nvl(ard.amount_cr,0), 0, nvl(ard.amount_dr,0),
458 (ard.amount_cr * -1))) end_bal,
459 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
460 ath.gl_date,:p_as_of_date_from)
461 * decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
462 (ard.acctd_amount_cr * -1))) acctd_start_bal,
463 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
464 ath.gl_date,:p_as_of_date_to)
465 * decode(nvl(ard.acctd_amount_cr,0), 0, nvl(ard.acctd_amount_dr,0),
466 (ard.acctd_amount_cr * -1))) acctd_end_bal
467 FROM '||l_ps_table||' ps,
468 '||l_ard_table || ' ard,
469 '||'ar_transaction_history_all ath,
470 '||l_line_table|| ' lines
471 WHERE ps.payment_schedule_id+0 > 0
472 AND ps.gl_date_closed >= :p_as_of_date_from
473 AND ps.class IN ( ''BR'',''CB'', ''CM'',''DEP'',''DM'',''GUAR'',''INV'')
474 AND ath.gl_date > :p_as_of_date_from
475 AND ath.event = ''CANCELLED''
476 AND ps.gl_date <= :p_as_of_date_to
477 AND ps.customer_trx_id = ath.customer_trx_id
478 AND ard.source_table = ''TH''
479 AND ard.source_id = ath.transaction_history_id
480 AND ps.customer_trx_id = lines.customer_trx_id
481 AND ard.source_id_secondary = lines.customer_trx_line_id
482 ' || l_ps_org_where ||'
483 ' || l_ard_org_where||'
484 ' || l_ath_org_where||'
485 ' || l_line_org_where;
486 END IF;
487
488 l_br_select := ' SELECT ps.customer_trx_id ,
489 sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
490 NULL,:p_as_of_date_from)
491 * ps.amount_due_remaining) start_bal,
492 sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
493 NULL,:p_as_of_date_to)
494 * ps.amount_due_remaining) end_bal,
495 sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
496 NULL,:p_as_of_date_from)
497 * ps.acctd_amount_due_remaining) acctd_start_bal,
498 sum(ar_calc_aging.begin_or_end_bal(gl_date,gl_date_closed,
499 NULL,:p_as_of_date_to)
500 * ps.acctd_amount_due_remaining) acctd_end_bal
501 FROM '||l_ps_table||' ps
502 WHERE ps.payment_schedule_id+0 > 0
503 AND ps.class = ''BR''
504 AND ps.gl_date <= :p_as_of_date_to
505 AND ps.gl_date_closed >= :p_as_of_date_from
506 '|| l_ps_org_where ||'
507 GROUP BY ps.customer_trx_id ';
508
509 l_br_app_select := ' SELECT
510 ps.customer_trx_id ,
511 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
512 ra.gl_date,:p_as_of_date_from)
513 *(ra.amount_applied + NVL(ra.earned_discount_taken,0)
514 + NVL(ra.unearned_discount_taken,0))) start_bal,
515 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
516 ra.gl_date,:p_as_of_date_to)
517 *(ra.amount_applied + NVL(ra.earned_discount_taken,0)
518 + NVL(ra.unearned_discount_taken,0))) end_bal,
519 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
520 ra.gl_date,:p_as_of_date_from)
521 *(ra.acctd_amount_applied_to + NVL(ra.acctd_earned_discount_taken,0)
522 + NVL(ra.acctd_unearned_discount_taken,0))) acctd_start_bal,
523 sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
524 ra.gl_date,:p_as_of_date_to)
525 *(ra.acctd_amount_applied_to + NVL(ra.acctd_earned_discount_taken,0)
526 + NVL(ra.acctd_unearned_discount_taken,0))) acctd_end_bal
527 FROM '|| l_ps_table||' ps,
528 '|| l_ra_table||' ra
529 WHERE ra.applied_payment_schedule_id = ps.payment_schedule_id
530 AND ps.payment_schedule_id+0 > 0
531 AND ps.class =''BR''
532 AND ra.gl_date > :p_as_of_date_from
533 AND ra.status = ''APP''
534 AND ps.gl_date <= :p_as_of_date_to
535 AND ps.gl_date_closed >= :p_as_of_date_from
536 AND NVL(ra.confirmed_flag,''Y'') = ''Y''
537 '|| l_ps_org_where ||'
538 '|| l_ra_org_where ||'
539 GROUP by ps.customer_trx_id ';
540
541 l_br_adj_select:= ' SELECT ps.customer_trx_id,
542 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
543 adj.gl_date,:p_as_of_date_from)
544 * adj.amount) start_bal,
545 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
546 adj.gl_date,:p_as_of_date_to)
547 * adj.amount) end_bal,
548 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
549 adj.gl_date,:p_as_of_date_from)
550 * adj.acctd_amount) acctd_start_bal,
551 -sum(ar_calc_aging.begin_or_end_bal(ps.gl_date,ps.gl_date_closed,
552 adj.gl_date,:p_as_of_date_to)
553 * adj.acctd_amount) acctd_end_bal
554 FROM '|| l_adj_table ||' adj,
555 '|| l_ps_table ||' ps
556 WHERE ps.payment_schedule_id + 0 > 0
557 AND ps.class = ''BR''
558 AND adj.payment_schedule_id = ps.payment_schedule_id
559 AND adj.gl_date > :p_as_of_date_from
560 AND ps.gl_date <= :p_as_of_date_to
561 AND ps.gl_date_closed >= :p_as_of_date_from
562 AND adj.status = ''A''
563 '|| l_adj_org_where||'
564 '|| l_ps_org_where ||'
565 GROUP BY ps.customer_trx_id ';
566
567 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
568 l_unapp_select := 'SELECT
569 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
570 NULL,:p_as_of_date_from)
571 * ra.amount_applied) ,0 ) start_bal,
572 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
573 NULL,:p_as_of_date_to)
574 * ra.amount_applied) ,0) end_bal,
575 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
576 NULL,:p_as_of_date_from)
577 * ra.acctd_amount_applied_from) ,0 ) acctd_start_bal,
578 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
579 NULL,:p_as_of_date_to)
580 * ra.acctd_amount_applied_from) ,0) acctd_end_bal
581 FROM '|| l_ps_table ||' ps,
582 '|| l_ra_table ||' ra,
583 gl_code_combinations gc
584 WHERE ra.gl_date <= :p_as_of_date_to
585 AND ps.cash_receipt_id = ra.cash_receipt_id
586 AND ra.status in ( ''ACC'', ''UNAPP'', ''UNID'', ''OTHER ACC'' )
587 AND nvl(ra.confirmed_flag, ''Y'') = ''Y''
588 AND ps.class = ''PMT''
589 AND ps.gl_date_closed >= :p_as_of_date_from
590 AND nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y''
591 AND gc.code_combination_id = ra.code_combination_id
592 ' || l_ps_org_where ||'
593 ' || l_ra_org_where || '
594 ' || company_segment_where;
595 ELSE
596 l_unapp_select := 'SELECT
597 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
598 NULL,:p_as_of_date_from)
599 * ra.amount_applied) ,0 ) start_bal,
600 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
601 NULL,:p_as_of_date_to)
602 * ra.amount_applied) ,0) end_bal,
603 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
604 NULL,:p_as_of_date_from)
605 * ra.acctd_amount_applied_from) ,0 ) acctd_start_bal,
606 NVL(-sum(ar_calc_aging.begin_or_end_bal(ra.gl_date,gl_date_closed,
607 NULL,:p_as_of_date_to)
608 * ra.acctd_amount_applied_from) ,0) acctd_end_bal
609 FROM '|| l_ps_table ||' ps,
610 '|| l_ra_table ||' ra
611 WHERE ra.gl_date <= :p_as_of_date_to
612 AND ps.cash_receipt_id = ra.cash_receipt_id
613 AND ra.status in ( ''ACC'', ''UNAPP'', ''UNID'', ''OTHER ACC'' )
614 AND nvl(ra.confirmed_flag, ''Y'') = ''Y''
615 AND ps.class = ''PMT''
616 AND ps.gl_date_closed >= :p_as_of_date_from
617 AND nvl( ps.receipt_confirmed_flag, ''Y'' ) = ''Y''
618 ' || l_ps_org_where ||'
619 ' || l_ra_org_where ;
620 END IF;
621
622
623 l_trx_main_select := '
624 SELECT sum(start_bal) start_bal,
625 sum(end_bal) end_bal,
626 sum(acctd_start_bal)acctd_start_bal ,
627 sum(acctd_end_bal) acctd_end_bal
628 FROM (
629 '||l_ps_select ||'
630 UNION ALL
631 '||l_ra_select ||'
632 UNION ALL
633 '||l_cm_ra_select ||'
634 UNION ALL
635 '||l_adj_select ||'
636 ) ps ';
637 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
638 l_trx_main_select := l_trx_main_select || ', '|| l_gl_dist_table ||' gl_dist,
639 gl_code_combinations gc
640 where gl_dist.customer_trx_id = ps.customer_trx_id
641 and gl_dist.account_class =''REC''
642 and gl_dist.latest_rec_flag =''Y''
643 and gl_dist.code_combination_id = gc.code_combination_id
644 ' || l_gl_dist_org_where ||'
645 ' || company_segment_where ;
646 END IF;
647
648 l_br_main_select := '
649 SELECT sum(start_bal) start_bal,
650 sum(end_bal) end_bal,
651 sum(acctd_start_bal)acctd_start_bal ,
652 sum(acctd_end_bal) acctd_end_bal
653 FROM (
654 '||l_br_select ||'
655 UNION ALL
656 '||l_br_app_select ||'
657 UNION ALL
658 '||l_br_adj_select ||'
659 ) ps ';
660 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
661 l_br_main_select := l_br_main_select || ' , ar_transaction_history_all ath,
662 '|| l_ard_table ||' ard,
663 gl_code_combinations gc
664 WHERE ps.customer_trx_id = ath.customer_trx_id
665 AND ath.status = ''PENDING_REMITTANCE''
666 AND ath.event in (''COMPLETED'',''ACCEPTED'')
667 AND ard.source_id = ath.transaction_history_id
668 AND ard.source_table = ''TH''
669 AND ard.source_type = ''REC''
670 AND ard.source_id_secondary IS NULL
671 AND ard.source_table_secondary IS NULL
672 AND ard.source_type_secondary IS NULL
673 AND gc.code_combination_id = ard.code_combination_id
674 '|| l_ath_org_where ||'
675 '|| l_ard_org_where ||'
676 '|| company_segment_where ;
677 END IF;
678
679 IF nvl(br_enabled_flag,'N') = 'Y' THEN
680 l_main_select := 'SELECT sum(start_bal) start_bal,
681 sum(end_bal) end_bal,
682 sum(acctd_start_bal) acctd_start_bal ,
683 sum(acctd_end_bal) acctd_end_bal
684 FROM ('|| l_trx_main_select ||' UNION ALL '||
685 l_br_main_select ||'
686 UNION ALL
687 '|| l_unapp_select ||' UNION ALL
688 '|| l_cancel_br_select|| ') ';
689 ELSE
690 l_main_select := 'SELECT sum(start_bal) start_bal,
691 sum(end_bal) end_bal,
692 sum(acctd_start_bal) acctd_start_bal ,
693 sum(acctd_end_bal) acctd_end_bal
694 FROM ('|| l_trx_main_select ||' UNION ALL
695 '|| l_unapp_select
696 || ') ';
697 END IF;
698
699 v_cursor := dbms_sql.open_cursor;
700
701 dbms_sql.parse(v_cursor,l_main_select,DBMS_SQL.NATIVE);
702
703 dbms_sql.bind_variable(v_cursor, ':p_as_of_date_from', p_as_of_date_from);
704 dbms_sql.bind_variable(v_cursor, ':p_as_of_date_to', p_as_of_date_to);
705
706 dbms_sql.define_column(v_cursor, 1, p_begin_bal);
707 dbms_sql.define_column(v_cursor, 2, p_end_bal);
708 dbms_sql.define_column(v_cursor, 3, p_acctd_begin_bal);
709 dbms_sql.define_column(v_cursor, 4, p_acctd_end_bal);
710
711 l_ignore := dbms_sql.execute(v_cursor);
712
713 LOOP
714 IF dbms_sql.fetch_rows(v_cursor) > 0 then
715 dbms_sql.column_value(v_cursor, 1, p_begin_bal);
716 dbms_sql.column_value(v_cursor, 2, p_end_bal);
717 dbms_sql.column_value(v_cursor, 3, p_acctd_begin_bal);
718 dbms_sql.column_value(v_cursor, 4, p_acctd_end_bal);
719 ELSE
720 EXIT;
721 END IF;
722 END LOOP;
723
724 dbms_sql.close_cursor(v_cursor);
725
726 END aging_as_of;
727
728 /*-----------------------------------------------------------
729 PUBLIC PROCEDURE adjustment_register
730 -------------------------------------------------------------*/
731
732 PROCEDURE adjustment_register(p_gl_date_low IN DATE ,
733 p_gl_date_high IN DATE,
734 p_reporting_level IN VARCHAR2,
735 p_reporting_entity_id IN NUMBER,
736 p_co_seg_low IN VARCHAR2,
737 p_co_seg_high IN VARCHAR2,
738 p_coa_id IN NUMBER,
739 p_fin_chrg_amount OUT NOCOPY NUMBER,
740 p_fin_chrg_acctd_amount OUT NOCOPY NUMBER,
741 p_adj_amount OUT NOCOPY NUMBER,
742 p_adj_acctd_amount OUT NOCOPY NUMBER,
743 p_guar_amount OUT NOCOPY NUMBER,
744 p_guar_acctd_amount OUT NOCOPY NUMBER,
745 p_dep_amount OUT NOCOPY NUMBER,
746 p_dep_acctd_amount OUT NOCOPY NUMBER,
747 p_endorsmnt_amount OUT NOCOPY NUMBER,
748 p_endorsmnt_acctd_amount OUT NOCOPY NUMBER ) IS
749
750 l_main_select VARCHAR2(10000);
751 l_endorsement_select VARCHAR2(5000);
752 v_cursor NUMBER;
753 l_ignore INTEGER;
754 BEGIN
755
756 /* AR Reconciliation Process Enhancements : Procedure is completely re-written */
757
758 build_parameters (p_reporting_level,
759 p_reporting_entity_id,
760 p_co_seg_low,
761 p_co_seg_high,
762 p_coa_id);
763
764 l_main_select := '
765 SELECT sum(decode(rec.type,''FINCHRG'', adj.amount,0)) fin_amount,
766 sum(decode(rec.type,''FINCHRG'', adj.acctd_amount,0)) fin_acctd_amount,
767 sum(decode(rec.type,''ADJUST'',
768 decode(adj.adjustment_type,''C'',0,
769 decode(adj.receivables_trx_id,-15,0, adj.amount)))) Adj_amount,
770 sum(decode(rec.type,''ADJUST'',
771 decode(adj.adjustment_type,''C'',0,
772 decode(adj.receivables_trx_id,-15,0, adj.acctd_amount)))) Adj_acctd_amount,
773 sum(decode(rec.type,''ADJUST'',
774 decode(adj.adjustment_type,''C'',
775 decode(type.type,''GUAR'',adj.amount,0)))) Guar_amount,
776 sum(decode(rec.type,''ADJUST'',
777 decode(adj.adjustment_type,''C'',
778 decode(type.type,''GUAR'',adj.acctd_amount,0)))) Guar_acctd_amount,
779 sum(decode(rec.type,''ADJUST'',
780 decode(adj.adjustment_type,''C'',
781 decode(type.type,''GUAR'',0,adj.amount)))) Dep_amount,
782 sum(decode(rec.type,''ADJUST'',
783 decode(adj.adjustment_type,''C'',
784 decode(type.type,''GUAR'',0,adj.acctd_amount)))) Dep_acctd_amount
785 FROM '||l_adj_table||' adj,
786 ar_receivables_trx_all rec,
787 '||l_trx_table||' trx,
788 ra_cust_trx_types_all type ';
789 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
790 l_main_select := l_main_select || ',
791 '||l_gl_dist_table||' gl_dist,
792 gl_code_combinations gc ';
793 END IF;
794 l_main_select := l_main_select ||'
795 WHERE nvl(adj.status, ''A'') = ''A''
796 AND adj.receivables_trx_id <> -15
797 AND adj.receivables_trx_id = rec.receivables_trx_id
798 AND nvl(rec.org_id,-99) = nvl(adj.org_id,-99)
799 AND adj.gl_date between :gl_date_low and :gl_date_high
800 AND trx.customer_trx_id = adj.customer_trx_id
801 AND trx.complete_flag = ''Y''
802 AND trx.cust_trx_type_id = type.cust_trx_type_id
803 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
804 '|| l_adj_org_where ||'
805 '|| l_trx_org_where ;
806
807 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
808 l_main_select := l_main_select ||'
809 AND adj.customer_trx_id = gl_dist.customer_trx_id
810 AND gl_dist.account_class = ''REC''
811 AND gl_dist.latest_rec_flag = ''Y''
812 AND gc.code_combination_id = gl_dist.code_combination_id
813 '|| l_gl_dist_org_where ||'
814 '|| company_segment_where;
815 END IF;
816 l_endorsement_select := 'SELECT
817 sum(adj.amount) Endsmnt_amount,
818 sum(adj.acctd_amount) Endrsmnt_acctd_amount
819 FROM '||l_adj_table||' adj,
820 ar_receivables_trx_all rec';
821 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
822 l_endorsement_select := l_endorsement_select || ' ,
823 ar_transaction_history_all ath ';
824 END IF;
825 l_endorsement_select := l_endorsement_select ||'
826 WHERE nvl(adj.status, ''A'') = ''A''
827 AND adj.receivables_trx_id <> -15
828 AND adj.receivables_trx_id = rec.receivables_trx_id
829 AND nvl(adj.org_id,-99) = nvl(rec.org_id,-99)
830 AND rec.type = ''ENDORSEMENT''
831 AND adj.gl_date between :gl_date_low and :gl_date_high
832 '|| l_adj_org_where ;
833 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
834 l_endorsement_select := l_endorsement_select || '
835 AND adj.customer_trx_id = ath.customer_trx_id
836 AND ath.status = ''PENDING_REMITTANCE''
837 AND ath.event in (''COMPLETED'',''ACCEPTED'')
838 '|| l_ath_org_where ||'
839 AND exists (SELECT line_id
840 FROM '|| l_ard_table ||' ard,
841 gl_code_combinations gc
842 WHERE ard.source_id = ath.transaction_history_id
843 AND ard.source_table = ''TH''
844 AND ard.source_type = ''REC''
845 AND ard.source_id_secondary IS NULL
846 AND ard.source_table_secondary IS NULL
847 AND ard.source_type_secondary IS NULL
848 AND gc.code_combination_id = ard.code_combination_id
849 '|| l_ard_org_where ||'
850 '||company_segment_where||')';
851 END IF;
852
853 v_cursor := dbms_sql.open_cursor;
854
855 dbms_sql.parse(v_cursor,l_main_select,DBMS_SQL.NATIVE);
856
857 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
858 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
859
860 dbms_sql.define_column(v_cursor, 1, p_fin_chrg_amount);
861 dbms_sql.define_column(v_cursor, 2, p_fin_chrg_acctd_amount);
862 dbms_sql.define_column(v_cursor, 3, p_adj_amount);
863 dbms_sql.define_column(v_cursor, 4, p_adj_acctd_amount);
864 dbms_sql.define_column(v_cursor, 5, p_guar_amount);
865 dbms_sql.define_column(v_cursor, 6, p_guar_acctd_amount);
866 dbms_sql.define_column(v_cursor, 7, p_dep_amount);
867 dbms_sql.define_column(v_cursor, 8, p_dep_acctd_amount);
868
869 l_ignore := dbms_sql.execute(v_cursor);
870
871 LOOP
872 IF dbms_sql.fetch_rows(v_cursor) > 0 then
873 dbms_sql.column_value(v_cursor, 1, p_fin_chrg_amount);
874 dbms_sql.column_value(v_cursor, 2, p_fin_chrg_acctd_amount);
875 dbms_sql.column_value(v_cursor, 3, p_adj_amount);
876 dbms_sql.column_value(v_cursor, 4, p_adj_acctd_amount);
877 dbms_sql.column_value(v_cursor, 5, p_guar_amount);
878 dbms_sql.column_value(v_cursor, 6, p_guar_acctd_amount);
879 dbms_sql.column_value(v_cursor, 7, p_dep_amount);
880 dbms_sql.column_value(v_cursor, 8, p_dep_acctd_amount);
881 ELSE
882 EXIT;
883 END IF;
884 END LOOP;
885
886 IF nvl(br_enabled_flag,'N') = 'Y' THEN
887 dbms_sql.parse(v_cursor,l_endorsement_select,DBMS_SQL.NATIVE);
888
889 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
890 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
891
892 dbms_sql.define_column(v_cursor, 1, p_endorsmnt_amount);
893 dbms_sql.define_column(v_cursor, 2, p_endorsmnt_acctd_amount);
894
895 l_ignore := dbms_sql.execute(v_cursor);
896
897 LOOP
898 IF dbms_sql.fetch_rows(v_cursor) > 0 then
899 dbms_sql.column_value(v_cursor, 1, p_endorsmnt_amount);
900 dbms_sql.column_value(v_cursor, 2, p_endorsmnt_acctd_amount);
901 ELSE
902 EXIT;
903 END IF;
904 END LOOP;
905 END IF;
906
907 dbms_sql.close_cursor(v_cursor);
908
909 END adjustment_register ;
910
911 /*-----------------------------------------------------------
912 PUBLIC PROCEDURE transaction_register
913 -------------------------------------------------------------*/
914
915 PROCEDURE transaction_register(p_gl_date_low IN DATE,
916 p_gl_date_high IN DATE,
917 p_reporting_level IN VARCHAR2,
918 p_reporting_entity_id IN NUMBER,
919 p_co_seg_low IN VARCHAR2,
920 p_co_seg_high IN VARCHAR2,
921 p_coa_id IN NUMBER,
922 p_non_post_amount OUT NOCOPY NUMBER,
923 p_non_post_acctd_amount OUT NOCOPY NUMBER,
924 p_post_amount OUT NOCOPY NUMBER ,
925 p_post_acctd_amount OUT NOCOPY NUMBER ) IS
926
927 l_post_select VARCHAR2(2000);
928 l_non_post_select VARCHAR2(2000);
929 v_cursor NUMBER;
930 l_ignore INTEGER;
931
932
933 BEGIN
934
935 /* AR Reconciliation Process Enhancements: The procedure is completely modified */
936
937 build_parameters (p_reporting_level,
938 p_reporting_entity_id,
939 p_co_seg_low,
940 p_co_seg_high,
941 p_coa_id);
942
943 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
944 l_post_select := '
945 SELECT
946 NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
947 NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
948 FROM ra_cust_trx_types_all type,
949 '||l_trx_table||' trx,
950 '||l_gl_dist_table||' gl_dist,
951 gl_code_combinations gc
952 WHERE gl_dist.gl_date BETWEEN :gl_date_low AND :gl_date_high
953 AND gl_dist.gl_date IS NOT NULL
954 AND gl_dist.account_class = ''REC''
955 AND gl_dist.latest_rec_flag = ''Y''
956 AND gl_dist.customer_trx_id = trx.customer_trx_id
957 AND type.cust_trx_type_id = trx.cust_trx_type_id
958 AND trx.complete_flag = ''Y''
959 AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
960 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
961 AND gc.code_combination_id = gl_dist.code_combination_id
962 '||l_gl_dist_org_where ||'
963 '||l_trx_org_where ||'
964 '||company_segment_where;
965 l_non_post_select := '
966 SELECT
967 NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
968 NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
969 FROM ra_cust_trx_types_all type,
970 '||l_trx_table||' trx,
971 '||l_gl_dist_table||' gl_dist,
972 gl_code_combinations gc
973 WHERE trx.trx_date BETWEEN :gl_date_low AND :gl_date_high
974 AND gl_dist.gl_date IS NULL
975 AND gl_dist.account_class = ''REC''
976 AND gl_dist.latest_rec_flag = ''Y''
977 AND gl_dist.customer_trx_id = trx.customer_trx_id
978 AND type.cust_trx_type_id = trx.cust_trx_type_id
979 AND trx.complete_flag = ''Y''
980 AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
981 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
982 AND gc.code_combination_id = gl_dist.code_combination_id
983 '||l_gl_dist_org_where ||'
984 '||l_trx_org_where ||'
985 '||company_segment_where;
986 ELSE
987 l_post_select := '
988 SELECT
989 NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
990 NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
991 FROM ra_cust_trx_types_all type,
992 '||l_trx_table||' trx,
993 '||l_gl_dist_table||' gl_dist
994 WHERE gl_dist.gl_date BETWEEN :gl_date_low AND :gl_date_high
995 AND gl_dist.gl_date IS NOT NULL
996 AND gl_dist.account_class = ''REC''
997 AND gl_dist.latest_rec_flag = ''Y''
998 AND gl_dist.customer_trx_id = trx.customer_trx_id
999 AND type.cust_trx_type_id = trx.cust_trx_type_id
1000 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
1001 AND trx.complete_flag = ''Y''
1002 AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
1003 '||l_gl_dist_org_where ||'
1004 '||l_trx_org_where;
1005 l_non_post_select := '
1006 SELECT
1007 NVL(SUM(NVL(gl_dist.amount,0)),0) Invoice_Currency,
1008 NVL(SUM(NVL(gl_dist.acctd_amount,0)),0) Functional_Currency
1009 FROM ra_cust_trx_types_all type,
1010 '||l_trx_table||' trx,
1011 '||l_gl_dist_table||' gl_dist
1012 WHERE trx.trx_date BETWEEN :gl_date_low AND :gl_date_high
1013 AND gl_dist.gl_date IS NULL
1014 AND gl_dist.account_class = ''REC''
1015 AND gl_dist.latest_rec_flag = ''Y''
1016 AND gl_dist.customer_trx_id = trx.customer_trx_id
1017 AND type.cust_trx_type_id = trx.cust_trx_type_id
1018 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
1019 AND trx.complete_flag = ''Y''
1020 AND type.type in (''INV'',''DEP'',''GUAR'', ''CM'',''DM'', ''CB'' )
1021 '||l_gl_dist_org_where ||'
1022 '||l_trx_org_where;
1023 END IF;
1024
1025 v_cursor := dbms_sql.open_cursor;
1026
1027 dbms_sql.parse(v_cursor,l_post_select ,DBMS_SQL.NATIVE);
1028
1029 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1030 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1031
1032 dbms_sql.define_column(v_cursor, 1, p_post_amount);
1033 dbms_sql.define_column(v_cursor, 2, p_post_acctd_amount);
1034
1035 l_ignore := dbms_sql.execute(v_cursor);
1036
1037 LOOP
1038 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1039 dbms_sql.column_value(v_cursor, 1, p_post_amount);
1040 dbms_sql.column_value(v_cursor, 2, p_post_acctd_amount);
1041 ELSE
1042 EXIT;
1043 END IF;
1044 END LOOP;
1045
1046 dbms_sql.parse(v_cursor,l_non_post_select ,DBMS_SQL.NATIVE);
1047
1048 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1049 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1050
1051 dbms_sql.define_column(v_cursor, 1, p_non_post_amount);
1052 dbms_sql.define_column(v_cursor, 2, p_non_post_acctd_amount);
1053
1054 l_ignore := dbms_sql.execute(v_cursor);
1055
1056 LOOP
1057 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1058 dbms_sql.column_value(v_cursor, 1, p_non_post_amount);
1059 dbms_sql.column_value(v_cursor, 2, p_non_post_acctd_amount);
1060 ELSE
1061 EXIT;
1062 END IF;
1063 END LOOP;
1064
1065 dbms_sql.close_cursor(v_cursor);
1066
1067 END transaction_register ;
1068
1069 /*-------------------------------------------------
1070 PUBLIC PROCEDURE rounding_diff
1071 --------------------------------------------------*/
1072
1073 PROCEDURE rounding_diff(l_gl_date_low IN DATE,
1074 l_gl_date_high IN DATE,
1075 l_rounding_diff OUT NOCOPY NUMBER ) IS
1076 BEGIN
1077
1078 /*
1079 * Bug fix: 2498344
1080 * MRC enhancements to select data from reporting book
1081 * please refer to bug for more details.
1082 * we need to execute different selects depending on the book
1083 * for which report is run
1084 */
1085
1086
1087 -- For Zero Amount Transactions , sometimes the acctd_amount is
1088 -- derived as 0.01 or 0.02.
1089
1090 IF NVL(ar_calc_aging.ca_sob_type,'P') = 'P'
1091 THEN
1092 SELECT NVL(SUM(NVL(acctd_amount,0)),0)
1093 INTO l_rounding_diff
1094 FROM ra_cust_trx_line_gl_dist
1095 WHERE amount = 0
1096 AND gl_date BETWEEN l_gl_date_low AND l_gl_date_high ;
1097 ELSE
1098 SELECT NVL(SUM(NVL(acctd_amount,0)),0)
1099 INTO l_rounding_diff
1100 FROM ra_trx_line_gl_dist_mrc_v
1101 WHERE amount = 0
1102 AND gl_date BETWEEN l_gl_date_low AND l_gl_date_high ;
1103 END IF;
1104
1105 END rounding_diff ;
1106
1107
1108 /*------------------------------------------------
1109 PUBLIC PROCEDURE cash_receipt_register
1110 --------------------------------------------------*/
1111 -- Calculate Applied, Unapplied and CM gain/loss amounts
1112 --
1113
1114 PROCEDURE cash_receipts_register(p_gl_date_low IN DATE ,
1115 p_gl_date_high IN DATE,
1116 p_reporting_level IN VARCHAR2,
1117 p_reporting_entity_id IN NUMBER,
1118 p_co_seg_low IN VARCHAR2,
1119 p_co_seg_high IN VARCHAR2,
1120 p_coa_id IN NUMBER,
1121 p_unapp_amount OUT NOCOPY NUMBER,
1122 p_unapp_acctd_amount OUT NOCOPY NUMBER,
1123 p_acc_amount OUT NOCOPY NUMBER,
1124 p_acc_acctd_amount OUT NOCOPY NUMBER,
1125 p_claim_amount OUT NOCOPY NUMBER,
1126 p_claim_acctd_amount OUT NOCOPY NUMBER,
1127 p_prepay_amount OUT NOCOPY NUMBER,
1128 p_prepay_acctd_amount OUT NOCOPY NUMBER,
1129 p_app_amount OUT NOCOPY NUMBER,
1130 p_app_acctd_amount OUT NOCOPY NUMBER,
1131 p_edisc_amount OUT NOCOPY NUMBER,
1132 p_edisc_acctd_amount OUT NOCOPY NUMBER,
1133 p_unedisc_amount OUT NOCOPY NUMBER,
1134 p_unedisc_acctd_amount OUT NOCOPY NUMBER,
1135 p_cm_gain_loss OUT NOCOPY NUMBER,
1136 p_on_acc_cm_ref_amount OUT NOCOPY NUMBER, /*bug 5290086*/
1137 p_on_acc_cm_ref_acctd_amount OUT NOCOPY NUMBER ) IS
1138
1139 l_main_select VARCHAR2(20000);
1140 v_cursor NUMBER;
1141 l_ignore INTEGER;
1142
1143 BEGIN
1144
1145 /* AR Reconciliation Process Enhancements : Procedure is completely re-written */
1146
1147 build_parameters (p_reporting_level,
1148 p_reporting_entity_id,
1149 p_co_seg_low,
1150 p_co_seg_high,
1151 p_coa_id);
1152
1153 l_main_select := 'SELECT NVL(SUM(DECODE(ra.application_type,
1154 ''CASH'',
1155 DECODE(ra.status,
1156 ''ACC'', ra.amount_applied,0)
1157 ,0)),0) Onacc_amt,
1158 NVL(SUM(DECODE(ra.application_type,
1159 ''CASH'',
1160 DECODE(ra.status,
1161 ''ACC'', ra.acctd_amount_applied_from,0)
1162 ,0)),0) Onacc_acctd_amt,
1163 NVL(SUM(DECODE(ra.application_type,
1164 ''CASH'',
1165 DECODE(ra.status,
1166 ''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
1167 -4, ra.amount_applied,0),0)
1168 ,0)),0) claim_amount,
1169 NVL(SUM(DECODE(ra.application_type,
1170 ''CASH'',
1171 DECODE(ra.status,
1172 ''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
1173 -4, ra.acctd_amount_applied_from,0),0)
1174 ,0)),0) claim_acctd_amt,
1175 NVL(SUM(DECODE(ra.application_type,
1176 ''CASH'',
1177 DECODE(ra.status,
1178 ''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
1179 -7, ra.amount_applied,0),0)
1180 ,0)),0) prepay_amount,
1181 NVL(SUM(DECODE(ra.application_type,
1182 ''CASH'',
1183 DECODE(ra.status,
1184 ''OTHER ACC'', DECODE(ra.applied_payment_schedule_id,
1185 -7, ra.acctd_amount_applied_from,0),0)
1186 ,0)),0) prepay_acctd_amt,
1187 NVL(SUM(DECODE(ra.application_type,
1188 ''CASH'',
1189 DECODE(ra.status,
1190 ''UNAPP'', ra.amount_applied,
1191 ''UNID'', ra.amount_applied,0)
1192 ,0)),0) unapp_amt,
1193 NVL(SUM(DECODE(ra.application_type,
1194 ''CASH'',
1195 DECODE(ra.status,
1196 ''UNAPP'', ra.acctd_amount_applied_from,
1197 ''UNID'', ra.acctd_amount_applied_from,0)
1198 ,0)),0) unapp_acctd_amt,
1199
1200 NVL(SUM(DECODE(ra.application_type,
1201 ''CM'', DECODE(ra.amount_applied,0,0,
1202 ra.acctd_amount_applied_from)
1203 , 0)
1204 ),0) -
1205 NVL(SUM(DECODE(ra.application_type,
1206 ''CM'', DECODE(ra.amount_applied,0,0,
1207 NVL(ra.acctd_amount_applied_to,0))
1208 , 0)
1209 ),0) cm_gain_loss,
1210 NVL(SUM(DECODE(ra.application_type,
1211 ''CASH'',
1212 DECODE(ra.status,
1213 ''APP'',
1214 ra.amount_applied,0),0)),0) app_amt,
1215 NVL(SUM(DECODE(ra.application_type,
1216 ''CASH'',
1217 DECODE(ra.status,
1218 ''APP'',
1219 NVL(ra.earned_discount_taken,0),0),0)),0) edisc_amt,
1220 NVL(SUM(DECODE(ra.application_type,
1221 ''CASH'',
1222 DECODE(ra.status,
1223 ''APP'',
1224 NVL(ra.unearned_discount_taken,0),0),0)),0) unedisc_amt,
1225 NVL(SUM(DECODE(ra.application_type,
1226 ''CASH'',
1227 DECODE(ra.status,
1228 ''APP'',
1229 NVL(ra.acctd_amount_applied_to,0),0),0)),0) acctd_app_amt,
1230 NVL(SUM(DECODE(ra.application_type,
1231 ''CASH'',
1232 DECODE(ra.status,
1233 ''APP'',
1234 NVL(ra.acctd_earned_discount_taken,0),0),0)),0) acctd_edisc_amt,
1235 NVL(SUM(DECODE(ra.application_type,
1236 ''CASH'',
1237 DECODE(ra.status,
1238 ''APP'',
1239 NVL(ra.acctd_unearned_discount_taken,0),0),0)),0) acctd_unedisc_amt,
1240 NVL(SUM(DECODE(ra.application_type, /*bug5290086*/
1241 ''CM'',
1242 DECODE(ra.status,
1243 ''ACTIVITY'', DECODE(ra.applied_payment_schedule_id,
1244 -8, ra.amount_applied,0),0)
1245 ,0)),0) onacc_cm_ref_amount,
1246 NVL(SUM(DECODE(ra.application_type,
1247 ''CM'',
1248 DECODE(ra.status,
1249 ''ACTIVITY'', DECODE(ra.applied_payment_schedule_id,
1250 -8, ra.acctd_amount_applied_to,0),0)
1251 ,0)),0) onacc_cm_ref_acctd_amount
1252 FROM '|| l_ra_table || ' ra ';
1253
1254 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1255 l_main_select := l_main_select || ',
1256 gl_code_combinations gc ';
1257 END IF;
1258 l_main_select := l_main_select || '
1259 WHERE NVL(ra.confirmed_flag,''Y'') = ''Y''
1260 AND ra.gl_date BETWEEN :gl_date_low AND :gl_date_high
1261 '|| l_ra_org_where;
1262
1263 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1264 l_main_select := l_main_select || '
1265 AND gc.code_combination_id = ra.code_combination_id
1266 '|| company_segment_where;
1267 END IF;
1268
1269 v_cursor := dbms_sql.open_cursor;
1270
1271 dbms_sql.parse(v_cursor,l_main_select,DBMS_SQL.NATIVE);
1272
1273 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1274 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1275
1276 dbms_sql.define_column(v_cursor, 1, p_acc_amount);
1277 dbms_sql.define_column(v_cursor, 2, p_acc_acctd_amount);
1278 dbms_sql.define_column(v_cursor, 3, p_claim_amount);
1279 dbms_sql.define_column(v_cursor, 4, p_claim_acctd_amount);
1280 dbms_sql.define_column(v_cursor, 5, p_prepay_amount);
1281 dbms_sql.define_column(v_cursor, 6, p_prepay_acctd_amount);
1282 dbms_sql.define_column(v_cursor, 7, p_unapp_amount);
1283 dbms_sql.define_column(v_cursor, 8, p_unapp_acctd_amount);
1284 dbms_sql.define_column(v_cursor, 9, p_cm_gain_loss);
1285 dbms_sql.define_column(v_cursor, 10, p_app_amount);
1286 dbms_sql.define_column(v_cursor, 11, p_edisc_amount);
1287 dbms_sql.define_column(v_cursor, 12, p_unedisc_amount);
1288 dbms_sql.define_column(v_cursor, 13, p_app_acctd_amount);
1289 dbms_sql.define_column(v_cursor, 14, p_edisc_acctd_amount);
1290 dbms_sql.define_column(v_cursor, 15, p_unedisc_acctd_amount);
1291 dbms_sql.define_column(v_cursor, 16, p_on_acc_cm_ref_amount); /*bug5290086*/
1292 dbms_sql.define_column(v_cursor, 17, p_on_acc_cm_ref_acctd_amount);
1293
1294 l_ignore := dbms_sql.execute(v_cursor);
1295
1296 LOOP
1297 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1298 dbms_sql.column_value(v_cursor, 1, p_acc_amount);
1299 dbms_sql.column_value(v_cursor, 2, p_acc_acctd_amount);
1300 dbms_sql.column_value(v_cursor, 3, p_claim_amount);
1301 dbms_sql.column_value(v_cursor, 4, p_claim_acctd_amount);
1302 dbms_sql.column_value(v_cursor, 5, p_prepay_amount);
1303 dbms_sql.column_value(v_cursor, 6, p_prepay_acctd_amount);
1304 dbms_sql.column_value(v_cursor, 7, p_unapp_amount);
1305 dbms_sql.column_value(v_cursor, 8, p_unapp_acctd_amount);
1306 dbms_sql.column_value(v_cursor, 9, p_cm_gain_loss);
1307 dbms_sql.column_value(v_cursor, 10, p_app_amount);
1308 dbms_sql.column_value(v_cursor, 11, p_edisc_amount);
1309 dbms_sql.column_value(v_cursor, 12, p_unedisc_amount);
1310 dbms_sql.column_value(v_cursor, 13, p_app_acctd_amount);
1311 dbms_sql.column_value(v_cursor, 14, p_edisc_acctd_amount);
1312 dbms_sql.column_value(v_cursor, 15, p_unedisc_acctd_amount);
1313 dbms_sql.column_value(v_cursor, 16, p_on_acc_cm_ref_amount); /*bug5290086*/
1314 dbms_sql.column_value(v_cursor, 17, p_on_acc_cm_ref_acctd_amount);
1315 ELSE
1316 EXIT;
1317 END IF;
1318 END LOOP;
1319
1320 dbms_sql.close_cursor(v_cursor);
1321
1322 END cash_receipts_register ;
1323
1324 /*------------------------------------------------
1325 PUBLIC PROCEDURE invoice_exception
1326 --------------------------------------------------*/
1327
1328 PROCEDURE invoice_exceptions( p_gl_date_low IN DATE,
1329 p_gl_date_high IN DATE,
1330 p_reporting_level IN VARCHAR2,
1331 p_reporting_entity_id IN NUMBER,
1332 p_co_seg_low IN VARCHAR2,
1333 p_co_seg_high IN VARCHAR2,
1334 p_coa_id IN NUMBER,
1335 p_post_excp_amount OUT NOCOPY NUMBER,
1336 p_post_excp_acctd_amount OUT NOCOPY NUMBER,
1337 p_nonpost_excp_amount OUT NOCOPY NUMBER,
1338 p_nonpost_excp_acctd_amount OUT NOCOPY NUMBER) IS
1339 l_post_select VARCHAR2(10000);
1340 l_non_post_select VARCHAR2(10000);
1341 v_cursor NUMBER;
1342 l_ignore INTEGER;
1343
1344 BEGIN
1345
1346 build_parameters (p_reporting_level,
1347 p_reporting_entity_id,
1348 p_co_seg_low,
1349 p_co_seg_high,
1350 p_coa_id);
1351
1352 l_post_select := '
1353 SELECT
1354 NVL(SUM(NVL(gl_dist.amount,0)),0) ,
1355 NVL(SUM(NVL(gl_dist.acctd_amount,0)),0)
1356 FROM
1357 ra_cust_trx_types_all type,
1358 '||l_trx_table||' trx,
1359 '||l_gl_dist_table||' gl_dist ';
1360 l_non_post_select := '
1361 SELECT
1362 NVL(SUM(NVL(gl_dist.amount,0)),0) ,
1363 NVL(SUM(NVL(gl_dist.acctd_amount,0)),0)
1364 FROM
1365 ra_cust_trx_types_all type,
1366 '||l_trx_table||' trx,
1367 '||l_gl_dist_table||' gl_dist ';
1368
1369 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1370 l_post_select := l_post_select ||',
1371 gl_code_combinations gc ';
1372 l_non_post_select := l_non_post_select ||',
1373 gl_code_combinations gc ';
1374 END IF;
1375
1376 l_post_select := l_post_select || '
1377 WHERE trx.complete_flag = ''Y''
1378 AND NOT EXISTS ( SELECT ''x''
1379 FROM '||l_ps_table||' ps
1380 WHERE ps.customer_trx_id = trx.customer_trx_id
1381 '|| l_ps_org_where||')
1382 AND gl_dist.gl_date BETWEEN :gl_date_low AND :gl_date_high
1383 AND type.post_to_gl = ''Y''
1384 AND gl_dist.account_class = ''REC''
1385 AND gl_dist.latest_rec_flag = ''Y''
1386 AND gl_dist.customer_trx_id = trx.customer_trx_id
1387 AND trx.cust_trx_type_id = type.cust_trx_type_id
1388 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
1389 AND type.type IN (''INV'', ''DEP'', ''GUAR'', ''CM'',''DM'')
1390 '|| l_trx_org_where||'
1391 '|| l_gl_dist_org_where ;
1392 l_non_post_select := l_non_post_select||'
1393 WHERE trx.complete_flag = ''Y''
1394 AND NOT EXISTS ( SELECT ''x''
1395 FROM '||l_ps_table||' ps
1396 WHERE ps.customer_trx_id = trx.customer_trx_id
1397 '|| l_ps_org_where||')
1398 AND trx.trx_date BETWEEN :gl_date_low AND :gl_date_high
1399 AND type.post_to_gl = ''N''
1400 AND gl_dist.account_class = ''REC''
1401 AND gl_dist.latest_rec_flag = ''Y''
1402 AND gl_dist.customer_trx_id = trx.customer_trx_id
1403 AND trx.cust_trx_type_id = type.cust_trx_type_id
1404 AND nvl(type.org_id,-99) = nvl(trx.org_id,-99)
1405 AND type.type IN (''INV'', ''DEP'', ''GUAR'', ''CM'',''DM'')
1406 '|| l_trx_org_where ||'
1407 '|| l_gl_dist_org_where;
1408
1409 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1410 l_post_select := l_post_select||'
1411 AND gc.code_combination_id = gl_dist.code_combination_id
1412 '||company_segment_where ;
1413 l_non_post_select := l_non_post_select ||'
1414 AND gc.code_combination_id = gl_dist.code_combination_id
1415 '||company_segment_where ;
1416 END IF;
1417
1418 v_cursor := dbms_sql.open_cursor;
1419
1420 dbms_sql.parse(v_cursor,l_post_select,DBMS_SQL.NATIVE);
1421
1422 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1423 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1424
1425 dbms_sql.define_column(v_cursor, 1, p_post_excp_amount);
1426 dbms_sql.define_column(v_cursor, 2, p_post_excp_acctd_amount);
1427
1428 l_ignore := dbms_sql.execute(v_cursor);
1429
1430 LOOP
1431 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1432 dbms_sql.column_value(v_cursor, 1, p_post_excp_amount);
1433 dbms_sql.column_value(v_cursor, 2, p_post_excp_acctd_amount);
1434 ELSE
1435 EXIT;
1436 END IF;
1437 END LOOP;
1438
1439 dbms_sql.parse(v_cursor,l_non_post_select,DBMS_SQL.NATIVE);
1440
1441 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1442 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1443
1444 dbms_sql.define_column(v_cursor, 1, p_nonpost_excp_amount);
1445 dbms_sql.define_column(v_cursor, 2, p_nonpost_excp_acctd_amount);
1446
1447 l_ignore := dbms_sql.execute(v_cursor);
1448
1449 LOOP
1450 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1451 dbms_sql.column_value(v_cursor, 1, p_nonpost_excp_amount);
1452 dbms_sql.column_value(v_cursor, 2, p_nonpost_excp_acctd_amount);
1453 ELSE
1454 EXIT;
1455 END IF;
1456 END LOOP;
1457
1458 dbms_sql.close_cursor(v_cursor);
1459
1460 END invoice_exceptions ;
1461
1462 FUNCTION begin_or_end_bal( p_gl_date IN DATE,
1463 p_gl_date_closed IN DATE,
1464 p_activity_date IN DATE,
1465 p_as_of_date IN DATE
1466 )RETURN NUMBER IS
1467
1468 BEGIN
1469 --If the payment schedule gl date is less than p_as_of_date_start
1470 --and gl date closed is greater than p_as_of_date_start
1471
1472 IF p_activity_date IS NULL THEN --for Open Trx
1473 IF ( ( p_gl_date <= p_as_of_date)
1474 AND ( p_gl_date_closed > p_as_of_date) ) THEN
1475 RETURN 1;
1476 ELSE
1477 RETURN 0;
1478 END IF;
1479 ELSIF p_activity_date IS NOT NULL THEN -- applications and adjustments
1480 IF ( (p_gl_date <= p_as_of_date)
1481 AND (p_gl_date_closed > p_as_of_date)
1482 AND (p_activity_date > p_as_of_date)) THEN
1483 RETURN 1;
1484 ELSE
1485 RETURN 0;
1486 END IF;
1487 END IF;
1488
1489 END begin_or_end_bal;
1490
1491 PROCEDURE journal_reports( p_gl_date_low IN DATE,
1492 p_gl_date_high IN DATE,
1493 p_reporting_level IN VARCHAR2,
1494 p_reporting_entity_id IN NUMBER,
1495 p_co_seg_low IN VARCHAR2,
1496 p_co_seg_high IN VARCHAR2,
1497 p_coa_id IN NUMBER,
1498 p_sales_journal_amt OUT NOCOPY NUMBER,
1499 p_sales_journal_acctd_amt OUT NOCOPY NUMBER,
1500 p_adj_journal_amt OUT NOCOPY NUMBER,
1501 p_adj_journal_acctd_amt OUT NOCOPY NUMBER,
1502 p_app_journal_amt OUT NOCOPY NUMBER,
1503 p_app_journal_acctd_amt OUT NOCOPY NUMBER,
1504 p_unapp_journal_amt OUT NOCOPY NUMBER,
1505 p_unapp_journal_acctd_amt OUT NOCOPY NUMBER,
1506 p_cm_journal_acctd_amt OUT NOCOPY NUMBER) IS
1507
1508 l_sales_journal_salect VARCHAR2(2000);
1509 l_adj_journal_select VARCHAR2(2000);
1510 l_app_journal_select VARCHAR2(3000);
1511 l_unapp_journal_select VARCHAR2(2000);
1512 l_cm_journal_select VARCHAR2(2000);
1513 v_cursor NUMBER;
1514 l_ignore INTEGER;
1515 l_ledger_id NUMBER;
1516
1517 BEGIN
1518
1519 build_parameters (p_reporting_level,
1520 p_reporting_entity_id,
1521 p_co_seg_low,
1522 p_co_seg_high,
1523 p_coa_id);
1524
1525 /* Bug7265328 - This used to pick Journal data from the AR distributions entries. Modified to pick
1526 details from SLA tables without tying it back to AR tables for amounts. This exercise will help
1527 find actual difference between AR side operational data and SLA side accounting data */
1528
1529 --{Bug7265328 Modifications Start
1530
1531 IF (p_reporting_level = '1000') THEN
1532 l_ledger_id := p_reporting_entity_id;
1533
1534 ELSIF (p_reporting_level = '3000') THEN
1535
1536 SELECT set_of_books_id
1537 INTO l_ledger_id
1538 FROM ar_system_parameters_all
1539 WHERE org_id = p_reporting_entity_id;
1540
1541 END IF;
1542
1543 l_sales_journal_salect := ' SELECT (sum(nvl(ae.entered_dr,0))- sum(nvl(ae.entered_cr,0))),
1544 (sum(nvl(ae.accounted_dr,0))- sum(nvl(ae.accounted_cr,0)))
1545 FROM '||l_trx_table||' trx,
1546 xla_transaction_entities_upg en,
1547 xla_ae_headers hdr,
1548 xla_ae_lines ae ';
1549
1550 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1551 l_sales_journal_salect := l_sales_journal_salect ||',
1552 gl_code_combinations gc ';
1553 END IF;
1554
1555 l_sales_journal_salect := l_sales_journal_salect ||'
1556 WHERE en.application_id = 222
1557 AND en.ledger_id = '|| l_ledger_id ||'
1558 AND hdr.entity_id = en.entity_id
1559 AND trx.customer_trx_id = en.source_id_int_1
1560 AND hdr.application_id = 222
1561 AND hdr.ledger_id = en.ledger_id
1562 AND hdr.ae_header_id = ae.ae_header_id
1563 AND hdr.accounting_date between :gl_date_low and :gl_date_high
1564 AND ae.application_id = 222
1565 AND ae.accounting_class_code IN (''RECEIVABLE'')
1566 AND ae.ledger_id = en.ledger_id
1567 AND EXISTS ( SELECT ''x''
1568 FROM xla_distribution_links lk
1569 WHERE lk.event_id = hdr.event_id
1570 AND lk.ae_header_id = ae.ae_header_id
1571 AND lk.ae_line_num = ae.ae_line_num
1572 AND lk.application_id = 222
1573 AND lk.source_distribution_type = ''RA_CUST_TRX_LINE_GL_DIST_ALL'')
1574 '|| l_trx_org_where;
1575
1576 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1577 l_sales_journal_salect := l_sales_journal_salect ||'
1578 AND gc.code_combination_id = ae.code_combination_id
1579 '||company_segment_where;
1580 END IF;
1581
1582 l_adj_journal_select := 'SELECT (sum(nvl(ae.entered_dr,0))- sum(nvl(ae.entered_cr,0))),
1583 (sum(nvl(ae.accounted_dr,0))- sum(nvl(ae.accounted_cr,0)))
1584 FROM '||l_adj_table||' adj,
1585 xla_transaction_entities_upg en,
1586 xla_ae_headers hdr,
1587 xla_ae_lines ae ';
1588
1589 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1590 l_adj_journal_select := l_adj_journal_select||',
1591 gl_code_combinations gc';
1592 END IF;
1593
1594 l_adj_journal_select := l_adj_journal_select||'
1595 WHERE en.application_id = 222
1596 AND en.ledger_id = '|| l_ledger_id ||'
1597 AND hdr.entity_id = en.entity_id
1598 AND adj.adjustment_id = en.source_id_int_1
1599 AND hdr.application_id = 222
1600 AND hdr.ledger_id = en.ledger_id
1601 AND hdr.ae_header_id = ae.ae_header_id
1602 AND hdr.accounting_date between :gl_date_low and :gl_date_high
1603 AND ae.application_id = 222
1604 AND ae.accounting_class_code IN (''RECEIVABLE'')
1605 AND ae.ledger_id = en.ledger_id
1606 AND EXISTS ( SELECT ''x''
1607 FROM xla_distribution_links lk
1608 WHERE lk.event_id = hdr.event_id
1609 AND lk.ae_header_id = ae.ae_header_id
1610 AND lk.ae_line_num = ae.ae_line_num
1611 AND lk.application_id = 222
1612 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1613 AND lk.event_class_code = ''ADJUSTMENT'')
1614 '|| l_adj_org_where;
1615
1616 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1617 l_adj_journal_select := l_adj_journal_select||'
1618 AND gc.code_combination_id = ae.code_combination_id
1619 '||company_segment_where;
1620 END IF;
1621
1622 l_app_journal_select := 'SELECT (sum(nvl(ae.entered_cr,0))- sum(nvl(ae.entered_dr,0))),
1623 (sum(nvl(ae.accounted_cr,0))- sum(nvl(ae.accounted_dr,0)))
1624 FROM '||l_cr_table ||' cr,
1625 xla_transaction_entities_upg en,
1626 xla_ae_headers hdr,
1627 xla_ae_lines ae ';
1628
1629 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1630 l_app_journal_select := l_app_journal_select ||',
1631 gl_code_combinations gc';
1632 END IF;
1633 l_app_journal_select := l_app_journal_select||'
1634 WHERE en.application_id = 222
1635 AND en.ledger_id = '|| l_ledger_id ||'
1636 AND hdr.entity_id = en.entity_id
1637 AND cr.cash_receipt_id = en.source_id_int_1
1638 AND hdr.application_id = 222
1639 AND hdr.ledger_id = en.ledger_id
1640 AND hdr.ae_header_id = ae.ae_header_id
1641 AND hdr.accounting_date between :gl_date_low and :gl_date_high
1642 AND ae.application_id = 222
1643 AND ae.accounting_class_code IN (''RECEIVABLE'', ''EDISC'', ''UNEDISC'', ''UNPAID_BR'', ''REM_BR'', ''FAC_BR'')
1644 AND ae.ledger_id = en.ledger_id
1645 AND EXISTS ( SELECT ''x''
1646 FROM xla_distribution_links lk
1647 WHERE lk.event_id = hdr.event_id
1648 AND lk.ae_header_id = ae.ae_header_id
1649 AND lk.ae_line_num = ae.ae_line_num
1650 AND lk.application_id = 222
1651 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1652 AND lk.event_class_code = ''RECEIPT'')
1653 '|| l_cr_org_where;
1654
1655 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1656 l_app_journal_select := l_app_journal_select||'
1657 AND gc.code_combination_id = ae.code_combination_id
1658 '||company_segment_where;
1659 END IF;
1660
1661 l_unapp_journal_select := 'SELECT (sum(nvl(entered_cr,0))- sum(nvl(entered_dr,0))),
1662 (sum(nvl(accounted_cr,0))- sum(nvl(accounted_dr,0)))
1663 FROM '||l_cr_table||' cr,
1664 xla_transaction_entities_upg en,
1665 xla_ae_headers hdr,
1666 xla_ae_lines ae ';
1667
1668 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1669 l_unapp_journal_select := l_unapp_journal_select ||',
1670 gl_code_combinations gc ';
1671 END IF;
1672
1673 l_unapp_journal_select := l_unapp_journal_select ||'
1674 WHERE en.application_id = 222
1675 AND en.ledger_id = '|| l_ledger_id ||'
1676 AND hdr.entity_id = en.entity_id
1677 AND cr.cash_receipt_id = en.source_id_int_1
1678 AND hdr.application_id = 222
1679 AND hdr.ledger_id = en.ledger_id
1680 AND hdr.ae_header_id = ae.ae_header_id
1681 AND hdr.accounting_date between :gl_date_low and :gl_date_high
1682 AND ae.application_id = 222
1683 AND ae.accounting_class_code IN (''CLAIM'',''PREPAY'',''UNAPP'',''UNID'',''ACC'')
1684 AND ae.ledger_id = en.ledger_id
1685 '|| l_cr_org_where;
1686
1687 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1688 l_unapp_journal_select := l_unapp_journal_select ||'
1689 AND gc.code_combination_id = ae.code_combination_id
1690 '||company_segment_where;
1691 END IF;
1692
1693 l_cm_journal_select := 'SELECT (sum(nvl(ae.accounted_cr,0))- sum(nvl(ae.accounted_dr,0)))
1694 FROM '||l_trx_table||' trx,
1695 xla_transaction_entities_upg en,
1696 xla_ae_headers hdr,
1697 xla_ae_lines ae ';
1698
1699 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1700 l_cm_journal_select := l_cm_journal_select ||',
1701 gl_code_combinations gc';
1702 END IF;
1703
1704 l_cm_journal_select := l_cm_journal_select ||'
1705 WHERE en.application_id = 222
1706 AND en.ledger_id = '|| l_ledger_id ||'
1707 AND hdr.entity_id = en.entity_id
1708 AND trx.customer_trx_id = en.source_id_int_1
1709 AND hdr.application_id = 222
1710 AND hdr.ledger_id = en.ledger_id
1711 AND hdr.ae_header_id = ae.ae_header_id
1712 AND hdr.accounting_date between :gl_date_low and :gl_date_high
1713 AND ae.application_id = 222
1714 AND ae.ledger_id = en.ledger_id
1715 AND ae.accounting_class_code IN (''EXCHANGE_GAIN_LOSS'')
1716 AND EXISTS ( SELECT ''x''
1717 FROM xla_distribution_links lk
1718 WHERE lk.event_id = hdr.event_id
1719 AND lk.ae_header_id = ae.ae_header_id
1720 AND lk.ae_line_num = ae.ae_line_num
1721 AND lk.application_id = 222
1722 AND lk.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1723 AND lk.event_class_code = ''CREDIT_MEMO'')
1724 '|| l_trx_org_where;
1725
1726 IF p_co_seg_low IS NOT NULL OR p_co_seg_high IS NOT NULL THEN
1727 l_cm_journal_select := l_cm_journal_select ||'
1728 AND gc.code_combination_id = ae.code_combination_id
1729 '||company_segment_where;
1730 END IF;
1731
1732 --Bug7265328 Modifications End}
1733
1734 v_cursor := dbms_sql.open_cursor;
1735
1736 dbms_sql.parse(v_cursor,l_sales_journal_salect,DBMS_SQL.NATIVE);
1737
1738 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1739 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1740
1741 dbms_sql.define_column(v_cursor, 1, p_sales_journal_amt);
1742 dbms_sql.define_column(v_cursor, 2, p_sales_journal_acctd_amt);
1743
1744 l_ignore := dbms_sql.execute(v_cursor);
1745
1746 LOOP
1747 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1748 dbms_sql.column_value(v_cursor, 1, p_sales_journal_amt);
1749 dbms_sql.column_value(v_cursor, 2, p_sales_journal_acctd_amt);
1750 ELSE
1751 EXIT;
1752 END IF;
1753 END LOOP;
1754
1755 dbms_sql.parse(v_cursor,l_adj_journal_select,DBMS_SQL.NATIVE);
1756
1757 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1758 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1759
1760 dbms_sql.define_column(v_cursor, 1, p_adj_journal_amt);
1761 dbms_sql.define_column(v_cursor, 2, p_adj_journal_acctd_amt);
1762
1763 l_ignore := dbms_sql.execute(v_cursor);
1764
1765 LOOP
1766 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1767 dbms_sql.column_value(v_cursor, 1, p_adj_journal_amt);
1768 dbms_sql.column_value(v_cursor, 2, p_adj_journal_acctd_amt);
1769 ELSE
1770 EXIT;
1771 END IF;
1772 END LOOP;
1773
1774 dbms_sql.parse(v_cursor,l_app_journal_select,DBMS_SQL.NATIVE);
1775
1776 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1777 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1778
1779 dbms_sql.define_column(v_cursor, 1, p_app_journal_amt);
1780 dbms_sql.define_column(v_cursor, 2, p_app_journal_acctd_amt);
1781
1782 l_ignore := dbms_sql.execute(v_cursor);
1783
1784 LOOP
1785 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1786 dbms_sql.column_value(v_cursor, 1, p_app_journal_amt);
1787 dbms_sql.column_value(v_cursor, 2, p_app_journal_acctd_amt);
1788 ELSE
1789 EXIT;
1790 END IF;
1791 END LOOP;
1792
1793 dbms_sql.parse(v_cursor,l_unapp_journal_select,DBMS_SQL.NATIVE);
1794
1795 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1796 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1797
1798 dbms_sql.define_column(v_cursor, 1, p_unapp_journal_amt);
1799 dbms_sql.define_column(v_cursor, 2, p_unapp_journal_acctd_amt);
1800
1801 l_ignore := dbms_sql.execute(v_cursor);
1802
1803 LOOP
1804 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1805 dbms_sql.column_value(v_cursor, 1, p_unapp_journal_amt);
1806 dbms_sql.column_value(v_cursor, 2, p_unapp_journal_acctd_amt);
1807 ELSE
1808 EXIT;
1809 END IF;
1810 END LOOP;
1811
1812 dbms_sql.parse(v_cursor,l_cm_journal_select,DBMS_SQL.NATIVE);
1813
1814 dbms_sql.bind_variable(v_cursor, ':gl_date_low', p_gl_date_low);
1815 dbms_sql.bind_variable(v_cursor, ':gl_date_high', p_gl_date_high);
1816
1817 dbms_sql.define_column(v_cursor, 1,p_cm_journal_acctd_amt);
1818
1819 l_ignore := dbms_sql.execute(v_cursor);
1820
1821 LOOP
1822 IF dbms_sql.fetch_rows(v_cursor) > 0 then
1823 dbms_sql.column_value(v_cursor, 1,p_cm_journal_acctd_amt);
1824 ELSE
1825 EXIT;
1826 END IF;
1827 END LOOP;
1828
1829 dbms_sql.close_cursor(v_cursor);
1830
1831 END journal_reports;
1832
1833
1834 PROCEDURE get_report_heading ( p_reporting_level IN VARCHAR2,
1835 p_reporting_entity_id IN NUMBER,
1836 p_set_of_books_id IN NUMBER,
1837 p_sob_name OUT NOCOPY VARCHAR2,
1838 p_functional_currency OUT NOCOPY VARCHAR2,
1839 p_coa_id OUT NOCOPY NUMBER,
1840 p_precision OUT NOCOPY NUMBER,
1841 p_sysdate OUT NOCOPY VARCHAR2,
1842 p_organization OUT NOCOPY VARCHAR2,
1843 p_bills_receivable_flag OUT NOCOPY VARCHAR2) IS
1844 l_select_stmt VARCHAR2(10000);
1845 l_sysparam_table VARCHAR2(50);
1846 l_sysparam_where VARCHAR2(10000); --Changed the data length from 2000 to 10000 - when testing for Bug:4942083
1847 l_org_name VARCHAR2(10000);
1848 l_br_flag VARCHAR2(1);
1849 BEGIN
1850
1851 ar_calc_aging.g_reporting_entity_id := p_reporting_entity_id;
1852
1853 IF NVL(ar_calc_aging.ca_sob_type,'P') = 'P' THEN
1854 l_sysparam_table := 'ar_system_parameters_all ';
1855 ELSE
1856 l_sysparam_table := 'ar_system_parameters_all_mrc_v ';
1857 END IF;
1858
1859 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
1860
1861 l_sysparam_where := XLA_MO_REPORTING_API.Get_Predicate('param',null);
1862
1863 l_sysparam_where := replace(l_sysparam_where,
1864 ':p_reporting_entity_id','ar_calc_aging.get_reporting_entity_id()');
1865
1866 l_select_stmt := 'SELECT sob.name sob_name,
1867 sob.currency_code functional_currency,
1868 sob.chart_of_accounts_id ,
1869 cur.precision,
1870 to_char(sysdate,''DD-MON-YYYY hh24:mi'') p_sysdate
1871 FROM gl_sets_of_books sob,
1872 fnd_currencies cur
1873 WHERE sob.set_of_books_id = :p_set_of_books_id
1874 AND sob.currency_code = cur.currency_code';
1875
1876 EXECUTE IMMEDIATE l_select_stmt
1877 INTO p_sob_name,
1878 p_functional_currency,
1879 p_coa_id,
1880 p_precision,
1881 p_sysdate
1882 USING p_set_of_books_id;
1883
1884 IF p_reporting_level <> '3000' THEN
1885 select meaning
1886 into p_organization
1887 from ar_lookups
1888 where lookup_code = 'ALL'
1889 and lookup_type = 'ALL';
1890 BEGIN
1891 execute immediate
1892 'select ''Y''
1893 from dual
1894 where exists( select ''br_enabled''
1895 from '||l_sysparam_table||' param
1896 where bills_receivable_enabled_flag = ''Y''
1897 '||l_sysparam_where||')'
1898 into br_enabled_flag;
1899
1900 EXCEPTION WHEN OTHERS THEN
1901 br_enabled_flag := 'N';
1902 END;
1903
1904 ELSE
1905
1906 execute immediate 'select substr(hou.name,1,60) organization,
1907 nvl(param.bills_receivable_enabled_flag,''N'')
1908 from hr_organization_units hou,
1909 '||l_sysparam_table||' param
1910 where hou.organization_id = :org_id
1911 and hou.organization_id = param.org_id'
1912 into p_organization,br_enabled_flag
1913 using p_reporting_entity_id;
1914
1915 END IF;
1916
1917 IF nvl(br_enabled_flag,'N') <> 'Y' THEN
1918 br_enabled_flag := 'N';
1919 END IF;
1920
1921 p_bills_receivable_flag := br_enabled_flag;
1922
1923 END get_report_heading;
1924
1925 END ar_calc_aging ;