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