[Home] [Help]
PACKAGE BODY: APPS.PN_REC_EXP_EXTR_FROM_GL_PKG
Source
1 PACKAGE BODY PN_REC_EXP_EXTR_FROM_GL_PKG AS
2 /* $Header: PNGLRECB.pls 120.2 2005/12/01 14:45:22 sdmahesh noship $ */
3
4 /*----------------- DECLARATIONS PRIVATE TO THE PACKAGE ---------------------*/
5
6 TYPE periods_rec IS RECORD(period_name VARCHAR2(15),
7 start_date DATE,
8 end_date DATE);
9 TYPE ccid_rec IS RECORD(map_row NUMBER,
10 map_id NUMBER,
11 ccid NUMBER,
12 act_amount NUMBER,
13 bud_amount NUMBER);
14
15 TYPE periods_tbl IS TABLE OF periods_rec INDEX BY BINARY_INTEGER;
16 TYPE ccid_tbl IS TABLE OF ccid_rec INDEX BY BINARY_INTEGER;
17 TYPE rec_exp_itf_tbl IS TABLE OF pn_rec_exp_itf%ROWTYPE INDEX BY BINARY_INTEGER;
18 TYPE loc_acc_map_tbl IS TABLE OF pn_loc_acc_map%ROWTYPE INDEX BY BINARY_INTEGER;
19
20 /*------------------ PROCEDURES PRIVATE TO THE PACKAGE ----------------------*/
21
22 /*===========================================================================+
23 | PROCEDURE
24 | verify_dates_for_map
25 |
26 | DESCRIPTION
27 |
28 | ARGUMENTS:
29 |
30 | NOTES:
31 |
32 | MODIFICATION HISTORY
33 | 10-JUL-03 Kiran o created
34 +===========================================================================*/
35
36 PROCEDURE verify_dates_for_map(p_map_t IN OUT NOCOPY loc_acc_map_tbl,
37 p_periods_t IN periods_tbl)
38 IS
39
40 l_info VARCHAR2(240);
41
42 BEGIN
43
44 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.verify_dates_for_map (+)');
45
46 FOR i IN 1..p_map_t.COUNT LOOP
47
48 l_info := 'verifying dates for map, Mapping Row number: '||to_char(i);
49 /* verify start date */
50 FOR j in 1..p_periods_t.COUNT LOOP
51 IF p_map_t(i).effective_from_date <= p_periods_t(j).end_date THEN
52 p_map_t(i).effective_from_date := p_periods_t(j).start_date;
53 EXIT;
54 END IF;
55 END LOOP;
56 /* verify end date */
57 FOR j in REVERSE 1..p_periods_t.COUNT LOOP
58 IF p_map_t(i).effective_to_date >= p_periods_t(j).start_date THEN
59 p_map_t(i).effective_to_date := p_periods_t(j).end_date;
60 EXIT;
61 END IF;
62 END LOOP;
63
64 END LOOP;
65
66 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.verify_dates_for_map (-)');
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 Put_Log('Error while '|| l_info);
71 Raise;
72
73 END verify_dates_for_map;
74
75 /*===========================================================================+
76 | PROCEDURE
77 | get_ccids
78 |
79 | DESCRIPTION
80 |
81 | ARGUMENTS:
82 |
83 | NOTES:
84 |
85 | MODIFICATION HISTORY
86 | 10-JUL-03 Kiran o created
87 | 27-OCT-05 sdmahesh o ATG Mandated changes for SQL literals
88 +===========================================================================*/
89
90 PROCEDURE get_ccids(p_sob_id IN NUMBER,
91 p_map_t IN loc_acc_map_tbl,
92 p_ccid_t IN OUT NOCOPY ccid_tbl)
93 IS
94
95 l_summary_flag VARCHAR2(1) := 'N';
96 x_summary_flag VARCHAR2(1);
97 l_info VARCHAR2(240);
98 l_ccid_tbl_count NUMBER := p_ccid_t.COUNT;
99 l_ccid_temp NUMBER;
100 l_Where_Clause VARCHAR2(1000);
101 l_rows INTEGER;
102 l_count INTEGER;
103 l_sob_id INTEGER;
104 l_cursor INTEGER;
105
106
107
108 l_statement VARCHAR2(10000);
109 l_segment1_low VARCHAR2(25);
110 l_segment1_high VARCHAR2(25);
111 l_segment2_low VARCHAR2(25);
112 l_segment2_high VARCHAR2(25);
113 l_segment3_low VARCHAR2(25);
114 l_segment3_high VARCHAR2(25);
115 l_segment4_low VARCHAR2(25);
116 l_segment4_high VARCHAR2(25);
117 l_segment5_low VARCHAR2(25);
118 l_segment5_high VARCHAR2(25);
119 l_segment6_low VARCHAR2(25);
120 l_segment6_high VARCHAR2(25);
121 l_segment7_low VARCHAR2(25);
122 l_segment7_high VARCHAR2(25);
123 l_segment8_low VARCHAR2(25);
124 l_segment8_high VARCHAR2(25);
125 l_segment9_low VARCHAR2(25);
126 l_segment9_high VARCHAR2(25);
127 l_segment10_low VARCHAR2(25);
128 l_segment10_high VARCHAR2(25);
129 l_segment11_low VARCHAR2(25);
130 l_segment11_high VARCHAR2(25);
131 l_segment12_low VARCHAR2(25);
132 l_segment12_high VARCHAR2(25);
133 l_segment13_low VARCHAR2(25);
134 l_segment13_high VARCHAR2(25);
135 l_segment14_low VARCHAR2(25);
136 l_segment14_high VARCHAR2(25);
137 l_segment15_low VARCHAR2(25);
138 l_segment15_high VARCHAR2(25);
139 l_segment16_low VARCHAR2(25);
140 l_segment16_high VARCHAR2(25);
141 l_segment17_low VARCHAR2(25);
142 l_segment17_high VARCHAR2(25);
143 l_segment18_low VARCHAR2(25);
144 l_segment18_high VARCHAR2(25);
145 l_segment19_low VARCHAR2(25);
146 l_segment19_high VARCHAR2(25);
147 l_segment20_low VARCHAR2(25);
148 l_segment20_high VARCHAR2(25);
149 l_segment21_low VARCHAR2(25);
150 l_segment21_high VARCHAR2(25);
151 l_segment22_low VARCHAR2(25);
152 l_segment22_high VARCHAR2(25);
153 l_segment23_low VARCHAR2(25);
154 l_segment23_high VARCHAR2(25);
155 l_segment24_low VARCHAR2(25);
156 l_segment24_high VARCHAR2(25);
157 l_segment25_low VARCHAR2(25);
158 l_segment25_high VARCHAR2(25);
159 l_segment26_low VARCHAR2(25);
160 l_segment26_high VARCHAR2(25);
161 l_segment27_low VARCHAR2(25);
162 l_segment27_high VARCHAR2(25);
163 l_segment28_low VARCHAR2(25);
164 l_segment28_high VARCHAR2(25);
165 l_segment29_low VARCHAR2(25);
166 l_segment29_high VARCHAR2(25);
167 l_segment30_low VARCHAR2(25);
168 l_segment30_high VARCHAR2(25);
169
170
171 BEGIN
172
173 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.get_ccids (+)');
174
175 FOR i IN 1..p_map_t.COUNT LOOP
176
177 l_info := 'processing Map Row: '||to_char(i)
178 ||'; Creating dynamic Where Clause';
179
180 l_Where_Clause := null;
181 l_cursor := dbms_sql.open_cursor;
182
183 /* segment 1 */
184 IF p_map_t(i).segment1_low IS NOT NULL
185 and p_map_t(i).segment1_high IS NULL THEN
186 l_segment1_low := p_map_t(i).segment1_low;
187 l_Where_Clause := l_Where_Clause
188 ||' And gcc.segment1 >= :l_segment1_low';
189
190
191 ELSIF p_map_t(i).segment1_low IS NULL
192 and p_map_t(i).segment1_high IS NOT NULL THEN
193 l_segment1_high := p_map_t(i).segment1_high;
194 l_Where_Clause := l_Where_Clause
195 ||' And gcc.segment1 <= :l_segment1_high';
196
197
198 ELSIF p_map_t(i).segment1_low IS NOT NULL
199 and p_map_t(i).segment1_high IS NOT NULL THEN
200 l_segment1_low := p_map_t(i).segment1_low;
201 l_segment1_high := p_map_t(i).segment1_high;
202 l_Where_Clause := l_Where_Clause
203 ||' And gcc.segment1 Between :l_segment1_low
204 And :l_segment1_high';
205
206 END IF;
207
208 /* segment 2 */
209 IF p_map_t(i).segment2_low IS NOT NULL
210 and p_map_t(i).segment2_high IS NULL THEN
211 l_segment2_low := p_map_t(i).segment2_low;
212 l_Where_Clause := l_Where_Clause
213 ||' And gcc.segment2 >= :l_segment2_low';
214
215 ELSIF p_map_t(i).segment2_low IS NULL
216 and p_map_t(i).segment2_high IS NOT NULL THEN
217 l_segment2_high := p_map_t(i).segment2_high;
218 l_Where_Clause := l_Where_Clause
219 ||' And gcc.segment2 <= :l_segment2_high';
220
221
222 ELSIF p_map_t(i).segment2_low IS NOT NULL
223 and p_map_t(i).segment2_high IS NOT NULL THEN
224 l_segment2_low := p_map_t(i).segment2_low;
225 l_segment2_high := p_map_t(i).segment2_high;
226 l_Where_Clause := l_Where_Clause
227 ||' And gcc.segment2 Between :l_segment2_low
228 And :l_segment2_high ';
229
230 END IF;
231
232 /* segment 3 */
233 IF p_map_t(i).segment3_low IS NOT NULL
234 and p_map_t(i).segment3_high IS NULL THEN
235 l_segment3_low := p_map_t(i).segment3_low;
236 l_Where_Clause := l_Where_Clause
237 ||' And gcc.segment3 >= :l_segment3_low';
238
239
240 ELSIF p_map_t(i).segment3_low IS NULL
241 and p_map_t(i).segment3_high IS NOT NULL THEN
242 l_segment3_high := p_map_t(i).segment3_high;
243 l_Where_Clause := l_Where_Clause
244 ||' And gcc.segment3 <= :l_segment3_high';
245
246
247 ELSIF p_map_t(i).segment3_low IS NOT NULL
248 and p_map_t(i).segment3_high IS NOT NULL THEN
249 l_segment3_low := p_map_t(i).segment3_low;
250 l_segment3_high := p_map_t(i).segment3_high;
251 l_Where_Clause := l_Where_Clause
252 ||' And gcc.segment3 Between :l_segment3_low
253 And :l_segment3_high ';
254
255 END IF;
256
257 /* segment 4 */
258 IF p_map_t(i).segment4_low IS NOT NULL
259 and p_map_t(i).segment4_high IS NULL THEN
260 l_segment4_low := p_map_t(i).segment4_low;
261 l_Where_Clause := l_Where_Clause
262 ||' And gcc.segment4 >= :l_segment4_low';
263
264
265 ELSIF p_map_t(i).segment4_low IS NULL
266 and p_map_t(i).segment4_high IS NOT NULL THEN
267 l_segment4_high := p_map_t(i).segment4_high;
268 l_Where_Clause := l_Where_Clause
269 ||' And gcc.segment4 <= :l_segment4_high';
270
271
272 ELSIF p_map_t(i).segment4_low IS NOT NULL
273 and p_map_t(i).segment4_high IS NOT NULL THEN
274 l_segment4_low := p_map_t(i).segment4_low;
275 l_segment4_high := p_map_t(i).segment4_high;
276 l_Where_Clause := l_Where_Clause
277 ||' And gcc.segment4 Between :l_segment4_low
278 And :l_segment4_high ';
279
280 END IF;
281
282 /* segment 5 */
283 IF p_map_t(i).segment5_low IS NOT NULL
284 and p_map_t(i).segment5_high IS NULL THEN
285 l_segment5_low := p_map_t(i).segment5_low;
286 l_Where_Clause := l_Where_Clause
287 ||' And gcc.segment5 >= :l_segment5_low';
288
289
290 ELSIF p_map_t(i).segment5_low IS NULL
291 and p_map_t(i).segment5_high IS NOT NULL THEN
292 l_segment5_high := p_map_t(i).segment5_high;
293 l_Where_Clause := l_Where_Clause
294 ||' And gcc.segment5 <= :l_segment5_high';
295
296
297 ELSIF p_map_t(i).segment5_low IS NOT NULL
298 and p_map_t(i).segment5_high IS NOT NULL THEN
299 l_segment5_low := p_map_t(i).segment5_low;
300 l_segment5_high := p_map_t(i).segment5_high;
301 l_Where_Clause := l_Where_Clause
302 ||' And gcc.segment5 Between :l_segment5_low
303 And :l_segment5_high ';
304
305 END IF;
306
307
308 /* segment 6 */
309 IF p_map_t(i).segment6_low IS NOT NULL
310 and p_map_t(i).segment6_high IS NULL THEN
311 l_segment6_low := p_map_t(i).segment6_low;
312 l_Where_Clause := l_Where_Clause
313 ||' And gcc.segment6 >= :l_segment6_low';
314
315
316 ELSIF p_map_t(i).segment6_low IS NULL
317 and p_map_t(i).segment6_high IS NOT NULL THEN
318 l_segment6_high := p_map_t(i).segment6_high;
319 l_Where_Clause := l_Where_Clause
320 ||' And gcc.segment6 <= :l_segment6_high';
321
322
323 ELSIF p_map_t(i).segment6_low IS NOT NULL
324 and p_map_t(i).segment6_high IS NOT NULL THEN
325 l_segment6_low := p_map_t(i).segment6_low;
326 l_segment6_high := p_map_t(i).segment6_high;
327 l_Where_Clause := l_Where_Clause
328 ||' And gcc.segment6 Between :l_segment6_low
329 And :l_segment6_high ';
330
331 END IF;
332
333 /* segment 7 */
334 IF p_map_t(i).segment7_low IS NOT NULL
335 and p_map_t(i).segment7_high IS NULL THEN
336 l_segment7_low := p_map_t(i).segment7_low;
337 l_Where_Clause := l_Where_Clause
338 ||' And gcc.segment7 >= :l_segment7_low';
339
340
341 ELSIF p_map_t(i).segment7_low IS NULL
342 and p_map_t(i).segment7_high IS NOT NULL THEN
343 l_segment7_high := p_map_t(i).segment7_high;
344 l_Where_Clause := l_Where_Clause
345 ||' And gcc.segment7 <= :l_segment7_high';
346
347
348 ELSIF p_map_t(i).segment7_low IS NOT NULL
349 and p_map_t(i).segment7_high IS NOT NULL THEN
350 l_segment7_low := p_map_t(i).segment7_low;
351 l_segment7_high := p_map_t(i).segment7_high;
352 l_Where_Clause := l_Where_Clause
353 ||' And gcc.segment7 Between :l_segment7_low
354 And :l_segment7_high ';
355
356 END IF;
357
358 /* segment 8 */
359 IF p_map_t(i).segment8_low IS NOT NULL
360 and p_map_t(i).segment8_high IS NULL THEN
361 l_segment8_low := p_map_t(i).segment8_low;
362 l_Where_Clause := l_Where_Clause
363 ||' And gcc.segment8 >= :l_segment8_low';
364
365
366 ELSIF p_map_t(i).segment8_low IS NULL
367 and p_map_t(i).segment8_high IS NOT NULL THEN
368 l_segment8_high := p_map_t(i).segment8_high;
369 l_Where_Clause := l_Where_Clause
370 ||' And gcc.segment8 <= :l_segment8_high';
371
372
373 ELSIF p_map_t(i).segment8_low IS NOT NULL
374 and p_map_t(i).segment8_high IS NOT NULL THEN
375 l_segment8_low := p_map_t(i).segment8_low;
376 l_segment8_high := p_map_t(i).segment8_high;
377 l_Where_Clause := l_Where_Clause
378 ||' And gcc.segment8 Between :l_segment8_low
379 And :l_segment8_high ';
380
381 END IF;
382
383 /* segment 9 */
384 IF p_map_t(i).segment9_low IS NOT NULL
385 and p_map_t(i).segment9_high IS NULL THEN
386 l_segment9_low := p_map_t(i).segment9_low;
387 l_Where_Clause := l_Where_Clause
388 ||' And gcc.segment9 >= :l_segment9_low';
389
390
391 ELSIF p_map_t(i).segment9_low IS NULL
392 and p_map_t(i).segment9_high IS NOT NULL THEN
393 l_segment9_high := p_map_t(i).segment9_high;
394 l_Where_Clause := l_Where_Clause
395 ||' And gcc.segment9 <= :l_segment9_high';
396
397
398 ELSIF p_map_t(i).segment9_low IS NOT NULL
399 and p_map_t(i).segment9_high IS NOT NULL THEN
400 l_segment9_low := p_map_t(i).segment9_low;
401 l_segment9_high := p_map_t(i).segment9_high;
402 l_Where_Clause := l_Where_Clause
403 ||' And gcc.segment9 Between :l_segment9_low
404 And :l_segment9_high ';
405
406 END IF;
407
408 /* segment 10 */
409 IF p_map_t(i).segment10_low IS NOT NULL
410 and p_map_t(i).segment10_high IS NULL THEN
411 l_segment10_low := p_map_t(i).segment10_low;
412 l_Where_Clause := l_Where_Clause
413 ||' And gcc.segment10 >= :l_segment10_low';
414
415
416 ELSIF p_map_t(i).segment10_low IS NULL
417 and p_map_t(i).segment10_high IS NOT NULL THEN
418 l_segment10_high := p_map_t(i).segment10_high;
419 l_Where_Clause := l_Where_Clause
420 ||' And gcc.segment10 <= :l_segment10_high';
421
422
423 ELSIF p_map_t(i).segment10_low IS NOT NULL
424 and p_map_t(i).segment10_high IS NOT NULL THEN
425 l_segment10_low := p_map_t(i).segment10_low;
426 l_segment10_high := p_map_t(i).segment10_high;
427 l_Where_Clause := l_Where_Clause
428 ||' And gcc.segment10 Between :l_segment10_low
429 And :l_segment10_high ';
430
431 END IF;
432
433 /* segment 11 */
434 IF p_map_t(i).segment11_low IS NOT NULL
435 and p_map_t(i).segment11_high IS NULL THEN
436 l_segment11_low := p_map_t(i).segment11_low;
437 l_Where_Clause := l_Where_Clause
438 ||' And gcc.segment11 >= :l_segment11_low';
439
440
441 ELSIF p_map_t(i).segment11_low IS NULL
442 and p_map_t(i).segment11_high IS NOT NULL THEN
443 l_segment11_high := p_map_t(i).segment11_high;
444 l_Where_Clause := l_Where_Clause
445 ||' And gcc.segment11 <= :l_segment11_high';
446
447
448 ELSIF p_map_t(i).segment11_low IS NOT NULL
449 and p_map_t(i).segment11_high IS NOT NULL THEN
450 l_segment11_low := p_map_t(i).segment11_low;
451 l_segment11_high := p_map_t(i).segment11_high;
452 l_Where_Clause := l_Where_Clause
453 ||' And gcc.segment11 Between :l_segment11_low
454 And :l_segment11_high ';
455
456 END IF;
457
458 /* segment 12 */
459 IF p_map_t(i).segment12_low IS NOT NULL
460 and p_map_t(i).segment12_high IS NULL THEN
461 l_segment12_low := p_map_t(i).segment12_low;
462 l_Where_Clause := l_Where_Clause
463 ||' And gcc.segment12 >= :l_segment12_low';
464
465
466 ELSIF p_map_t(i).segment12_low IS NULL
467 and p_map_t(i).segment12_high IS NOT NULL THEN
468 l_segment12_high := p_map_t(i).segment12_high;
469 l_Where_Clause := l_Where_Clause
470 ||' And gcc.segment12 <= :l_segment12_high';
471
472
473 ELSIF p_map_t(i).segment12_low IS NOT NULL
474 and p_map_t(i).segment12_high IS NOT NULL THEN
475 l_segment12_low := p_map_t(i).segment12_low;
476 l_segment12_high := p_map_t(i).segment12_high;
477 l_Where_Clause := l_Where_Clause
478 ||' And gcc.segment12 Between :l_segment12_low
479 And :l_segment12_high ';
480
481 END IF;
482
483 /* segment 13 */
484 IF p_map_t(i).segment13_low IS NOT NULL
485 and p_map_t(i).segment13_high IS NULL THEN
486 l_segment13_low := p_map_t(i).segment13_low;
487 l_Where_Clause := l_Where_Clause
488 ||' And gcc.segment13 >= :l_segment13_low';
489
490
491 ELSIF p_map_t(i).segment13_low IS NULL
492 and p_map_t(i).segment13_high IS NOT NULL THEN
493 l_segment13_high := p_map_t(i).segment13_high;
494 l_Where_Clause := l_Where_Clause
495 ||' And gcc.segment13 <= :l_segment13_high';
496
497
498 ELSIF p_map_t(i).segment13_low IS NOT NULL
499 and p_map_t(i).segment13_high IS NOT NULL THEN
500 l_segment13_low := p_map_t(i).segment13_low;
501 l_segment13_high := p_map_t(i).segment13_high;
502 l_Where_Clause := l_Where_Clause
503 ||' And gcc.segment13 Between :l_segment13_low
504 And :l_segment13_high ';
505
506 END IF;
507
508 /* segment 14 */
509 IF p_map_t(i).segment14_low IS NOT NULL
510 and p_map_t(i).segment14_high IS NULL THEN
511 l_segment14_low := p_map_t(i).segment14_low;
512 l_Where_Clause := l_Where_Clause
513 ||' And gcc.segment14 >= :l_segment14_low';
514
515
516 ELSIF p_map_t(i).segment14_low IS NULL
517 and p_map_t(i).segment14_high IS NOT NULL THEN
518 l_segment14_high := p_map_t(i).segment14_high;
519 l_Where_Clause := l_Where_Clause
520 ||' And gcc.segment14 <= :l_segment14_high';
521
522
523 ELSIF p_map_t(i).segment14_low IS NOT NULL
524 and p_map_t(i).segment14_high IS NOT NULL THEN
525 l_segment14_low := p_map_t(i).segment14_low;
526 l_segment14_high := p_map_t(i).segment14_high;
527 l_Where_Clause := l_Where_Clause
528 ||' And gcc.segment14 Between :l_segment14_low
529 And :l_segment14_high ';
530
531
532 END IF;
533
534 /* segment 15 */
535 IF p_map_t(i).segment15_low IS NOT NULL
536 and p_map_t(i).segment15_high IS NULL THEN
537 l_segment15_low := p_map_t(i).segment15_low;
538 l_Where_Clause := l_Where_Clause
539 ||' And gcc.segment15 >= :l_segment15_low';
540
541
542 ELSIF p_map_t(i).segment15_low IS NULL
543 and p_map_t(i).segment15_high IS NOT NULL THEN
544 l_segment15_high := p_map_t(i).segment15_high;
545 l_Where_Clause := l_Where_Clause
546 ||' And gcc.segment15 <= :l_segment15_high';
547
548
549 ELSIF p_map_t(i).segment15_low IS NOT NULL
550 and p_map_t(i).segment15_high IS NOT NULL THEN
551 l_segment15_low := p_map_t(i).segment15_low;
552 l_segment15_high := p_map_t(i).segment15_high;
553 l_Where_Clause := l_Where_Clause
554 ||' And gcc.segment15 Between :l_segment15_low
555 And :l_segment15_high ';
556
557 END IF;
558
559 /* segment 16 */
560 IF p_map_t(i).segment16_low IS NOT NULL
561 and p_map_t(i).segment16_high IS NULL THEN
562 l_segment16_low := p_map_t(i).segment16_low;
563 l_Where_Clause := l_Where_Clause
564 ||' And gcc.segment16 >= :l_segment16_low';
565
566
567 ELSIF p_map_t(i).segment16_low IS NULL
568 and p_map_t(i).segment16_high IS NOT NULL THEN
569 l_segment16_high := p_map_t(i).segment16_high;
570 l_Where_Clause := l_Where_Clause
571 ||' And gcc.segment16 <= :l_segment16_high';
572
573
574 ELSIF p_map_t(i).segment16_low IS NOT NULL
575 and p_map_t(i).segment16_high IS NOT NULL THEN
576 l_segment16_low := p_map_t(i).segment16_low;
577 l_segment16_high := p_map_t(i).segment16_high;
578 l_Where_Clause := l_Where_Clause
579 ||' And gcc.segment16 Between :l_segment16_low
580 And :l_segment16_high ';
581
582 END IF;
583
584
585 /* segment 17 */
586 IF p_map_t(i).segment17_low IS NOT NULL
587 and p_map_t(i).segment17_high IS NULL THEN
588 l_segment17_low := p_map_t(i).segment17_low;
589 l_Where_Clause := l_Where_Clause
590 ||' And gcc.segment17 >= :l_segment17_low';
591
592
593 ELSIF p_map_t(i).segment17_low IS NULL
594 and p_map_t(i).segment17_high IS NOT NULL THEN
595 l_segment17_high := p_map_t(i).segment17_high;
596 l_Where_Clause := l_Where_Clause
597 ||' And gcc.segment17 <= :l_segment17_high';
598
599
600 ELSIF p_map_t(i).segment17_low IS NOT NULL
601 and p_map_t(i).segment17_high IS NOT NULL THEN
602 l_segment17_low := p_map_t(i).segment17_low;
603 l_segment17_high := p_map_t(i).segment17_high;
604 l_Where_Clause := l_Where_Clause
605 ||' And gcc.segment17 Between :l_segment17_low
606 And :l_segment17_high ';
607
608 END IF;
609
610 /* segment 18 */
611 IF p_map_t(i).segment18_low IS NOT NULL
612 and p_map_t(i).segment18_high IS NULL THEN
613 l_segment18_low := p_map_t(i).segment18_low;
614 l_Where_Clause := l_Where_Clause
615 ||' And gcc.segment18 >= :l_segment18_low';
616
617
618 ELSIF p_map_t(i).segment18_low IS NULL
619 and p_map_t(i).segment18_high IS NOT NULL THEN
620 l_segment18_high := p_map_t(i).segment18_high;
621 l_Where_Clause := l_Where_Clause
622 ||' And gcc.segment18 <= :l_segment18_high';
623
624
625 ELSIF p_map_t(i).segment18_low IS NOT NULL
626 and p_map_t(i).segment18_high IS NOT NULL THEN
627 l_segment18_low := p_map_t(i).segment18_low;
628 l_segment18_high := p_map_t(i).segment18_high;
629 l_Where_Clause := l_Where_Clause
630 ||' And gcc.segment18 Between :l_segment18_low
631 And :l_segment18_high ';
632
633 END IF;
634
635
636 /* segment 19 */
637 IF p_map_t(i).segment19_low IS NOT NULL
638 and p_map_t(i).segment19_high IS NULL THEN
639 l_segment19_low := p_map_t(i).segment19_low;
640 l_Where_Clause := l_Where_Clause
641 ||' And gcc.segment19 >= :l_segment19_low';
642
643
644 ELSIF p_map_t(i).segment19_low IS NULL
645 and p_map_t(i).segment19_high IS NOT NULL THEN
646 l_segment19_high := p_map_t(i).segment19_high;
647 l_Where_Clause := l_Where_Clause
648 ||' And gcc.segment19 <= :l_segment19_high';
649
650
651 ELSIF p_map_t(i).segment19_low IS NOT NULL
652 and p_map_t(i).segment19_high IS NOT NULL THEN
653 l_segment19_low := p_map_t(i).segment19_low;
654 l_segment19_high := p_map_t(i).segment19_high;
655 l_Where_Clause := l_Where_Clause
656 ||' And gcc.segment19 Between :l_segment19_low
657 And :l_segment19_high ';
658
659 END IF;
660
661 /* segment 20 */
662 IF p_map_t(i).segment20_low IS NOT NULL
663 and p_map_t(i).segment20_high IS NULL THEN
664 l_segment20_low := p_map_t(i).segment20_low;
665 l_Where_Clause := l_Where_Clause
666 ||' And gcc.segment20 >= :l_segment20_low';
667
668
669 ELSIF p_map_t(i).segment20_low IS NULL
670 and p_map_t(i).segment20_high IS NOT NULL THEN
671 l_segment20_high := p_map_t(i).segment20_high;
672 l_Where_Clause := l_Where_Clause
673 ||' And gcc.segment20 <= :l_segment20_high';
674
675
676 ELSIF p_map_t(i).segment20_low IS NOT NULL
677 and p_map_t(i).segment20_high IS NOT NULL THEN
678 l_segment20_low := p_map_t(i).segment20_low;
679 l_segment20_high := p_map_t(i).segment20_high;
680 l_Where_Clause := l_Where_Clause
681 ||' And gcc.segment20 Between :l_segment20_low
682 And :l_segment20_high ';
683
684 END IF;
685
686 /* segment 21 */
687 IF p_map_t(i).segment21_low IS NOT NULL
688 and p_map_t(i).segment21_high IS NULL THEN
689 l_segment21_low := p_map_t(i).segment21_low;
690 l_Where_Clause := l_Where_Clause
691 ||' And gcc.segment21 >= :l_segment21_low';
692
693
694 ELSIF p_map_t(i).segment21_low IS NULL
695 and p_map_t(i).segment21_high IS NOT NULL THEN
696 l_segment21_high := p_map_t(i).segment21_high;
697 l_Where_Clause := l_Where_Clause
698 ||' And gcc.segment21 <= :l_segment21_high';
699
700
701 ELSIF p_map_t(i).segment21_low IS NOT NULL
702 and p_map_t(i).segment21_high IS NOT NULL THEN
703 l_segment21_low := p_map_t(i).segment21_low;
704 l_segment21_high := p_map_t(i).segment21_high;
705 l_Where_Clause := l_Where_Clause
706 ||' And gcc.segment21 Between :l_segment21_low
707 And :l_segment21_high ';
708
709 END IF;
710
711 /* segment 22 */
712 IF p_map_t(i).segment22_low IS NOT NULL
713 and p_map_t(i).segment22_high IS NULL THEN
714 l_segment22_low := p_map_t(i).segment22_low;
715 l_Where_Clause := l_Where_Clause
716 ||' And gcc.segment22 >= :l_segment22_low';
717
718
719 ELSIF p_map_t(i).segment22_low IS NULL
720 and p_map_t(i).segment22_high IS NOT NULL THEN
721 l_segment22_high := p_map_t(i).segment22_high;
722 l_Where_Clause := l_Where_Clause
723 ||' And gcc.segment22 <= :l_segment22_high';
724
725
726 ELSIF p_map_t(i).segment22_low IS NOT NULL
727 and p_map_t(i).segment22_high IS NOT NULL THEN
728 l_segment22_low := p_map_t(i).segment22_low;
729 l_segment22_high := p_map_t(i).segment22_high;
730 l_Where_Clause := l_Where_Clause
731 ||' And gcc.segment22 Between :l_segment22_low
732 And :l_segment22_high ';
733
734 END IF;
735
736 /* segment 23 */
737 IF p_map_t(i).segment23_low IS NOT NULL
738 and p_map_t(i).segment23_high IS NULL THEN
739 l_segment23_low := p_map_t(i).segment23_low;
740 l_Where_Clause := l_Where_Clause
741 ||' And gcc.segment23 >= :l_segment23_low';
742
743
744 ELSIF p_map_t(i).segment23_low IS NULL
745 and p_map_t(i).segment23_high IS NOT NULL THEN
746 l_segment23_high := p_map_t(i).segment23_high;
747 l_Where_Clause := l_Where_Clause
748 ||' And gcc.segment23 <= :l_segment23_high';
749
750
751 ELSIF p_map_t(i).segment23_low IS NOT NULL
752 and p_map_t(i).segment23_high IS NOT NULL THEN
753 l_segment23_low := p_map_t(i).segment23_low;
754 l_segment23_high := p_map_t(i).segment23_high;
755 l_Where_Clause := l_Where_Clause
756 ||' And gcc.segment23 Between :l_segment23_low
757 And :l_segment23_high ';
758
759 END IF;
760
761 /* segment 24 */
762 IF p_map_t(i).segment24_low IS NOT NULL
763 and p_map_t(i).segment24_high IS NULL THEN
764 l_segment24_low := p_map_t(i).segment24_low;
765 l_Where_Clause := l_Where_Clause
766 ||' And gcc.segment24 >= :l_segment24_low';
767
768
769 ELSIF p_map_t(i).segment24_low IS NULL
770 and p_map_t(i).segment24_high IS NOT NULL THEN
771 l_segment24_high := p_map_t(i).segment24_high;
772 l_Where_Clause := l_Where_Clause
773 ||' And gcc.segment24 <= :l_segment24_high';
774
775
776 ELSIF p_map_t(i).segment24_low IS NOT NULL
777 and p_map_t(i).segment24_high IS NOT NULL THEN
778 l_segment24_low := p_map_t(i).segment24_low;
779 l_segment24_high := p_map_t(i).segment24_high;
780 l_Where_Clause := l_Where_Clause
781 ||' And gcc.segment24 Between :l_segment24_low
782 And :l_segment24_high ';
783
784 END IF;
785
786
787 /* segment 25 */
788 IF p_map_t(i).segment25_low IS NOT NULL
789 and p_map_t(i).segment25_high IS NULL THEN
790 l_segment25_low := p_map_t(i).segment25_low;
791 l_Where_Clause := l_Where_Clause
792 ||' And gcc.segment25 >= :l_segment25_low';
793
794
795 ELSIF p_map_t(i).segment25_low IS NULL
796 and p_map_t(i).segment25_high IS NOT NULL THEN
797 l_segment25_high := p_map_t(i).segment25_high;
798 l_Where_Clause := l_Where_Clause
799 ||' And gcc.segment25 <= :l_segment25_high';
800
801
802 ELSIF p_map_t(i).segment25_low IS NOT NULL
803 and p_map_t(i).segment25_high IS NOT NULL THEN
804 l_segment25_low := p_map_t(i).segment25_low;
805 l_segment25_high := p_map_t(i).segment25_high;
806 l_Where_Clause := l_Where_Clause
807 ||' And gcc.segment25 Between :l_segment25_low
808 And :l_segment25_high ';
809
810 END IF;
811
812 /* segment 26 */
813 IF p_map_t(i).segment26_low IS NOT NULL
814 and p_map_t(i).segment26_high IS NULL THEN
815 l_segment26_low := p_map_t(i).segment26_low;
816 l_Where_Clause := l_Where_Clause
817 ||' And gcc.segment26 >= :l_segment26_low';
818
819
820 ELSIF p_map_t(i).segment26_low IS NULL
821 and p_map_t(i).segment26_high IS NOT NULL THEN
822 l_segment26_high := p_map_t(i).segment26_high;
823 l_Where_Clause := l_Where_Clause
824 ||' And gcc.segment26 <= :l_segment26_high';
825
826
827 ELSIF p_map_t(i).segment26_low IS NOT NULL
828 and p_map_t(i).segment26_high IS NOT NULL THEN
829 l_segment26_low := p_map_t(i).segment26_low;
830 l_segment26_high := p_map_t(i).segment26_high;
831 l_Where_Clause := l_Where_Clause
832 ||' And gcc.segment26 Between :l_segment26_low
833 And :l_segment26_high ';
834
835 END IF;
836
837 /* segment 27 */
838 IF p_map_t(i).segment27_low IS NOT NULL
839 and p_map_t(i).segment27_high IS NULL THEN
840 l_segment27_low := p_map_t(i).segment27_low;
841 l_Where_Clause := l_Where_Clause
842 ||' And gcc.segment27 >= :l_segment27_low';
843
844
845 ELSIF p_map_t(i).segment27_low IS NULL
846 and p_map_t(i).segment27_high IS NOT NULL THEN
847 l_segment27_high := p_map_t(i).segment27_high;
848 l_Where_Clause := l_Where_Clause
849 ||' And gcc.segment27 <= :l_segment27_high';
850
851
852 ELSIF p_map_t(i).segment27_low IS NOT NULL
853 and p_map_t(i).segment27_high IS NOT NULL THEN
854 l_segment27_low := p_map_t(i).segment27_low;
855 l_segment27_high := p_map_t(i).segment27_high;
856 l_Where_Clause := l_Where_Clause
857 ||' And gcc.segment27 Between :l_segment27_low
858 And :l_segment27_high ';
859
860 END IF;
861
862 /* segment 28 */
863 IF p_map_t(i).segment28_low IS NOT NULL
864 and p_map_t(i).segment28_high IS NULL THEN
865 l_segment28_low := p_map_t(i).segment28_low;
866 l_Where_Clause := l_Where_Clause
867 ||' And gcc.segment28 >= :l_segment28_low';
868
869
870 ELSIF p_map_t(i).segment28_low IS NULL
871 and p_map_t(i).segment28_high IS NOT NULL THEN
872 l_segment28_high := p_map_t(i).segment28_high;
873 l_Where_Clause := l_Where_Clause
874 ||' And gcc.segment28 <= :l_segment28_high';
875
876
877 ELSIF p_map_t(i).segment28_low IS NOT NULL
878 and p_map_t(i).segment28_high IS NOT NULL THEN
879 l_segment28_low := p_map_t(i).segment28_low;
880 l_segment28_high := p_map_t(i).segment28_high;
881 l_Where_Clause := l_Where_Clause
882 ||' And gcc.segment28 Between :l_segment28_low
883 And :l_segment28_high ';
884
885 END IF;
886
887 /* segment 29 */
888 IF p_map_t(i).segment29_low IS NOT NULL
889 and p_map_t(i).segment29_high IS NULL THEN
890 l_segment29_low := p_map_t(i).segment29_low;
891 l_Where_Clause := l_Where_Clause
892 ||' And gcc.segment29 >= :l_segment29_low';
893
894
895 ELSIF p_map_t(i).segment29_low IS NULL
896 and p_map_t(i).segment29_high IS NOT NULL THEN
897 l_segment29_high := p_map_t(i).segment29_high;
898 l_Where_Clause := l_Where_Clause
899 ||' And gcc.segment29 <= :l_segment29_high';
900
901
902 ELSIF p_map_t(i).segment29_low IS NOT NULL
903 and p_map_t(i).segment29_high IS NOT NULL THEN
904 l_segment29_low := p_map_t(i).segment29_low;
905 l_segment29_high := p_map_t(i).segment29_high;
906 l_Where_Clause := l_Where_Clause
907 ||' And gcc.segment29 Between :l_segment29_low
908 And :l_segment29_high ';
909
910 END IF;
911
912 /* segment 30 */
913 IF p_map_t(i).segment30_low IS NOT NULL
914 and p_map_t(i).segment30_high IS NULL THEN
915 l_segment30_low := p_map_t(i).segment30_low;
916 l_Where_Clause := l_Where_Clause
917 ||' And gcc.segment30 >= :l_segment30_low';
918
919
920 ELSIF p_map_t(i).segment30_low IS NULL
921 and p_map_t(i).segment30_high IS NOT NULL THEN
922 l_segment30_high := p_map_t(i).segment30_high;
923 l_Where_Clause := l_Where_Clause
924 ||' And gcc.segment30 <= :l_segment30_high';
925
926
927 ELSIF p_map_t(i).segment30_low IS NOT NULL
928 and p_map_t(i).segment30_high IS NOT NULL THEN
929 l_segment30_low := p_map_t(i).segment30_low;
930 l_segment30_high := p_map_t(i).segment30_high;
931 l_Where_Clause := l_Where_Clause
932 ||' And gcc.segment30 Between :l_segment30_low
933 And :l_segment30_high ';
934
935 END IF;
936
937 Put_Log('Where clause for CCID: ');
938 Put_Log(l_Where_clause);
939
940 l_info := 'opening ref cursor for CCID';
941
942 l_sob_id := p_sob_id;
943 x_summary_flag := l_summary_flag;
944 l_statement :=
945 'Select gcc.code_combination_id
946 From gl_code_combinations gcc,
947 gl_sets_of_books gsob
948 Where gsob.set_of_books_id = :l_sob_id
949 And gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
950 And gcc.summary_flag = :x_summary_flag
951 And gcc.template_id IS NULL '
952 || l_Where_Clause ;
953
954
955
956 put_log('l_statement='||l_statement);
957 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
958 dbms_sql.bind_variable(l_cursor,'l_sob_id',l_sob_id);
959 dbms_sql.bind_variable(l_cursor,'x_summary_flag',x_summary_flag);
960
961 /* segment 1 */
962 IF p_map_t(i).segment1_low IS NOT NULL
963 and p_map_t(i).segment1_high IS NULL THEN
964 dbms_sql.bind_variable(l_cursor,'l_segment1_low',l_segment1_low);
965
966 ELSIF p_map_t(i).segment1_low IS NULL
967 and p_map_t(i).segment1_high IS NOT NULL THEN
968 dbms_sql.bind_variable(l_cursor,'l_segment1_high',l_segment1_high);
969
970 ELSIF p_map_t(i).segment1_low IS NOT NULL
971 and p_map_t(i).segment1_high IS NOT NULL THEN
972 dbms_sql.bind_variable(l_cursor,'l_segment1_low',l_segment1_low);
973 dbms_sql.bind_variable(l_cursor,'l_segment1_high',l_segment1_high);
974 END IF;
975
976 /* segment 2 */
977 IF p_map_t(i).segment2_low IS NOT NULL
978 and p_map_t(i).segment2_high IS NULL THEN
979 dbms_sql.bind_variable(l_cursor,'l_segment2_low',l_segment2_low);
980
981 ELSIF p_map_t(i).segment2_low IS NULL
982 and p_map_t(i).segment2_high IS NOT NULL THEN
983 dbms_sql.bind_variable(l_cursor,'l_segment2_high',l_segment2_high);
984
985 ELSIF p_map_t(i).segment2_low IS NOT NULL
986 and p_map_t(i).segment2_high IS NOT NULL THEN
987 dbms_sql.bind_variable(l_cursor,'l_segment2_low',l_segment2_low);
988 dbms_sql.bind_variable(l_cursor,'l_segment2_high',l_segment2_high);
989 END IF;
990
991 /* segment 3 */
992 IF p_map_t(i).segment3_low IS NOT NULL
993 and p_map_t(i).segment3_high IS NULL THEN
994 dbms_sql.bind_variable(l_cursor,'l_segment3_low',l_segment3_low);
995
996 ELSIF p_map_t(i).segment3_low IS NULL
997 and p_map_t(i).segment3_high IS NOT NULL THEN
998 dbms_sql.bind_variable(l_cursor,'l_segment3_high',l_segment3_high);
999
1000 ELSIF p_map_t(i).segment3_low IS NOT NULL
1001 and p_map_t(i).segment3_high IS NOT NULL THEN
1002 dbms_sql.bind_variable(l_cursor,'l_segment3_low',l_segment3_low);
1003 dbms_sql.bind_variable(l_cursor,'l_segment3_high',l_segment3_high);
1004 END IF;
1005
1006 /* segment 4 */
1007 IF p_map_t(i).segment4_low IS NOT NULL
1008 and p_map_t(i).segment4_high IS NULL THEN
1009 dbms_sql.bind_variable(l_cursor,'l_segment4_low',l_segment4_low);
1010
1011 ELSIF p_map_t(i).segment4_low IS NULL
1012 and p_map_t(i).segment4_high IS NOT NULL THEN
1013 dbms_sql.bind_variable(l_cursor,'l_segment4_high',l_segment4_high);
1014
1015 ELSIF p_map_t(i).segment4_low IS NOT NULL
1016 and p_map_t(i).segment4_high IS NOT NULL THEN
1017 dbms_sql.bind_variable(l_cursor,'l_segment4_low',l_segment4_low);
1018 dbms_sql.bind_variable(l_cursor,'l_segment4_high',l_segment4_high);
1019 END IF;
1020
1021 /* segment 5 */
1022 IF p_map_t(i).segment5_low IS NOT NULL
1023 and p_map_t(i).segment5_high IS NULL THEN
1024 dbms_sql.bind_variable(l_cursor,'l_segment5_low',l_segment5_low);
1025
1026 ELSIF p_map_t(i).segment5_low IS NULL
1027 and p_map_t(i).segment5_high IS NOT NULL THEN
1028 dbms_sql.bind_variable(l_cursor,'l_segment5_high',l_segment5_high);
1029
1030 ELSIF p_map_t(i).segment5_low IS NOT NULL
1031 and p_map_t(i).segment5_high IS NOT NULL THEN
1032 dbms_sql.bind_variable(l_cursor,'l_segment5_low',l_segment5_low);
1033 dbms_sql.bind_variable(l_cursor,'l_segment5_high',l_segment5_high);
1034 END IF;
1035
1036 /* segment 6 */
1037 IF p_map_t(i).segment6_low IS NOT NULL
1038 and p_map_t(i).segment6_high IS NULL THEN
1039 dbms_sql.bind_variable(l_cursor,'l_segment6_low',l_segment6_low);
1040
1041 ELSIF p_map_t(i).segment6_low IS NULL
1042 and p_map_t(i).segment6_high IS NOT NULL THEN
1043 dbms_sql.bind_variable(l_cursor,'l_segment6_high',l_segment6_high);
1044
1045 ELSIF p_map_t(i).segment6_low IS NOT NULL
1046 and p_map_t(i).segment6_high IS NOT NULL THEN
1047 dbms_sql.bind_variable(l_cursor,'l_segment6_low',l_segment6_low);
1048 dbms_sql.bind_variable(l_cursor,'l_segment6_high',l_segment6_high);
1049 END IF;
1050
1051 /* segment 7 */
1052 IF p_map_t(i).segment7_low IS NOT NULL
1053 and p_map_t(i).segment7_high IS NULL THEN
1054 dbms_sql.bind_variable(l_cursor,'l_segment7_low',l_segment7_low);
1055
1056 ELSIF p_map_t(i).segment7_low IS NULL
1057 and p_map_t(i).segment7_high IS NOT NULL THEN
1058 dbms_sql.bind_variable(l_cursor,'l_segment7_high',l_segment7_high);
1059
1060 ELSIF p_map_t(i).segment7_low IS NOT NULL
1061 and p_map_t(i).segment7_high IS NOT NULL THEN
1062 dbms_sql.bind_variable(l_cursor,'l_segment7_low',l_segment7_low);
1063 dbms_sql.bind_variable(l_cursor,'l_segment7_high',l_segment7_high);
1064 END IF;
1065
1066 /* segment 8 */
1067 IF p_map_t(i).segment8_low IS NOT NULL
1068 and p_map_t(i).segment8_high IS NULL THEN
1069 dbms_sql.bind_variable(l_cursor,'l_segment8_low',l_segment8_low);
1070
1071 ELSIF p_map_t(i).segment8_low IS NULL
1072 and p_map_t(i).segment8_high IS NOT NULL THEN
1073 dbms_sql.bind_variable(l_cursor,'l_segment8_high',l_segment8_high);
1074
1075 ELSIF p_map_t(i).segment8_low IS NOT NULL
1076 and p_map_t(i).segment8_high IS NOT NULL THEN
1077 dbms_sql.bind_variable(l_cursor,'l_segment8_low',l_segment8_low);
1078 dbms_sql.bind_variable(l_cursor,'l_segment8_high',l_segment8_high);
1079 END IF;
1080
1081 /* segment 9 */
1082 IF p_map_t(i).segment9_low IS NOT NULL
1083 and p_map_t(i).segment9_high IS NULL THEN
1084 dbms_sql.bind_variable(l_cursor,'l_segment9_low',l_segment9_low);
1085
1086 ELSIF p_map_t(i).segment9_low IS NULL
1087 and p_map_t(i).segment9_high IS NOT NULL THEN
1088 dbms_sql.bind_variable(l_cursor,'l_segment9_high',l_segment9_high);
1089
1090 ELSIF p_map_t(i).segment9_low IS NOT NULL
1091 and p_map_t(i).segment9_high IS NOT NULL THEN
1092 dbms_sql.bind_variable(l_cursor,'l_segment9_low',l_segment9_low);
1093 dbms_sql.bind_variable(l_cursor,'l_segment9_high',l_segment9_high);
1094 END IF;
1095
1096 /* segment 10 */
1097 IF p_map_t(i).segment10_low IS NOT NULL
1098 and p_map_t(i).segment10_high IS NULL THEN
1099 dbms_sql.bind_variable(l_cursor,'l_segment10_low',l_segment10_low);
1100
1101 ELSIF p_map_t(i).segment10_low IS NULL
1102 and p_map_t(i).segment10_high IS NOT NULL THEN
1103 dbms_sql.bind_variable(l_cursor,'l_segment10_high',l_segment10_high);
1104
1105 ELSIF p_map_t(i).segment10_low IS NOT NULL
1106 and p_map_t(i).segment10_high IS NOT NULL THEN
1107 dbms_sql.bind_variable(l_cursor,'l_segment10_low',l_segment10_low);
1108 dbms_sql.bind_variable(l_cursor,'l_segment10_high',l_segment10_high);
1109 END IF;
1110
1111 /* segment 11 */
1112 IF p_map_t(i).segment11_low IS NOT NULL
1113 and p_map_t(i).segment11_high IS NULL THEN
1114 dbms_sql.bind_variable(l_cursor,'l_segment11_low',l_segment11_low);
1115
1116 ELSIF p_map_t(i).segment11_low IS NULL
1117 and p_map_t(i).segment11_high IS NOT NULL THEN
1118 dbms_sql.bind_variable(l_cursor,'l_segment11_high',l_segment11_high);
1119
1120 ELSIF p_map_t(i).segment11_low IS NOT NULL
1121 and p_map_t(i).segment11_high IS NOT NULL THEN
1122 dbms_sql.bind_variable(l_cursor,'l_segment11_low',l_segment11_low);
1123 dbms_sql.bind_variable(l_cursor,'l_segment11_high',l_segment11_high);
1124 END IF;
1125
1126 /* segment 12 */
1127 IF p_map_t(i).segment12_low IS NOT NULL
1128 and p_map_t(i).segment12_high IS NULL THEN
1129 dbms_sql.bind_variable(l_cursor,'l_segment12_low',l_segment12_low);
1130
1131 ELSIF p_map_t(i).segment12_low IS NULL
1132 and p_map_t(i).segment12_high IS NOT NULL THEN
1133 dbms_sql.bind_variable(l_cursor,'l_segment12_high',l_segment12_high);
1134
1135 ELSIF p_map_t(i).segment12_low IS NOT NULL
1136 and p_map_t(i).segment12_high IS NOT NULL THEN
1137 dbms_sql.bind_variable(l_cursor,'l_segment12_low',l_segment12_low);
1138 dbms_sql.bind_variable(l_cursor,'l_segment12_high',l_segment12_high);
1139 END IF;
1140
1141 /* segment 13 */
1142 IF p_map_t(i).segment13_low IS NOT NULL
1143 and p_map_t(i).segment13_high IS NULL THEN
1144 dbms_sql.bind_variable(l_cursor,'l_segment13_low',l_segment13_low);
1145
1146 ELSIF p_map_t(i).segment13_low IS NULL
1147 and p_map_t(i).segment13_high IS NOT NULL THEN
1148 dbms_sql.bind_variable(l_cursor,'l_segment13_high',l_segment13_high);
1149
1150 ELSIF p_map_t(i).segment13_low IS NOT NULL
1151 and p_map_t(i).segment13_high IS NOT NULL THEN
1152 dbms_sql.bind_variable(l_cursor,'l_segment13_low',l_segment13_low);
1153 dbms_sql.bind_variable(l_cursor,'l_segment13_high',l_segment13_high);
1154 END IF;
1155
1156 /* segment 14 */
1157 IF p_map_t(i).segment14_low IS NOT NULL
1158 and p_map_t(i).segment14_high IS NULL THEN
1159 dbms_sql.bind_variable(l_cursor,'l_segment14_low',l_segment14_low);
1160
1161 ELSIF p_map_t(i).segment14_low IS NULL
1162 and p_map_t(i).segment14_high IS NOT NULL THEN
1163 dbms_sql.bind_variable(l_cursor,'l_segment14_high',l_segment14_high);
1164
1165 ELSIF p_map_t(i).segment14_low IS NOT NULL
1166 and p_map_t(i).segment14_high IS NOT NULL THEN
1167 dbms_sql.bind_variable(l_cursor,'l_segment14_low',l_segment14_low);
1168 dbms_sql.bind_variable(l_cursor,'l_segment14_high',l_segment14_high);
1169 END IF;
1170
1171 /* segment 15 */
1172 IF p_map_t(i).segment15_low IS NOT NULL
1173 and p_map_t(i).segment15_high IS NULL THEN
1174 dbms_sql.bind_variable(l_cursor,'l_segment15_low',l_segment15_low);
1175
1176 ELSIF p_map_t(i).segment15_low IS NULL
1177 and p_map_t(i).segment15_high IS NOT NULL THEN
1178 dbms_sql.bind_variable(l_cursor,'l_segment15_high',l_segment15_high);
1179
1180 ELSIF p_map_t(i).segment15_low IS NOT NULL
1181 and p_map_t(i).segment15_high IS NOT NULL THEN
1182 dbms_sql.bind_variable(l_cursor,'l_segment15_low',l_segment15_low);
1183 dbms_sql.bind_variable(l_cursor,'l_segment15_high',l_segment15_high);
1184 END IF;
1185
1186 /* segment 16 */
1187 IF p_map_t(i).segment16_low IS NOT NULL
1188 and p_map_t(i).segment16_high IS NULL THEN
1189 dbms_sql.bind_variable(l_cursor,'l_segment16_low',l_segment16_low);
1190
1191 ELSIF p_map_t(i).segment16_low IS NULL
1192 and p_map_t(i).segment16_high IS NOT NULL THEN
1193 dbms_sql.bind_variable(l_cursor,'l_segment16_high',l_segment16_high);
1194
1195 ELSIF p_map_t(i).segment16_low IS NOT NULL
1196 and p_map_t(i).segment16_high IS NOT NULL THEN
1197 dbms_sql.bind_variable(l_cursor,'l_segment16_low',l_segment16_low);
1198 dbms_sql.bind_variable(l_cursor,'l_segment16_high',l_segment16_high);
1199 END IF;
1200
1201 /* segment 17 */
1202 IF p_map_t(i).segment17_low IS NOT NULL
1203 and p_map_t(i).segment17_high IS NULL THEN
1204 dbms_sql.bind_variable(l_cursor,'l_segment17_low',l_segment17_low);
1205
1206 ELSIF p_map_t(i).segment17_low IS NULL
1207 and p_map_t(i).segment17_high IS NOT NULL THEN
1208 dbms_sql.bind_variable(l_cursor,'l_segment17_high',l_segment17_high);
1209
1210 ELSIF p_map_t(i).segment17_low IS NOT NULL
1211 and p_map_t(i).segment17_high IS NOT NULL THEN
1212 dbms_sql.bind_variable(l_cursor,'l_segment17_low',l_segment17_low);
1213 dbms_sql.bind_variable(l_cursor,'l_segment17_high',l_segment17_high);
1214 END IF;
1215
1216 /* segment 18 */
1217 IF p_map_t(i).segment18_low IS NOT NULL
1218 and p_map_t(i).segment18_high IS NULL THEN
1219 dbms_sql.bind_variable(l_cursor,'l_segment18_low',l_segment18_low);
1220
1221 ELSIF p_map_t(i).segment18_low IS NULL
1222 and p_map_t(i).segment18_high IS NOT NULL THEN
1223 dbms_sql.bind_variable(l_cursor,'l_segment18_high',l_segment18_high);
1224
1225 ELSIF p_map_t(i).segment18_low IS NOT NULL
1226 and p_map_t(i).segment18_high IS NOT NULL THEN
1227 dbms_sql.bind_variable(l_cursor,'l_segment18_low',l_segment18_low);
1228 dbms_sql.bind_variable(l_cursor,'l_segment18_high',l_segment18_high);
1229 END IF;
1230
1231 /* segment 19 */
1232 IF p_map_t(i).segment19_low IS NOT NULL
1233 and p_map_t(i).segment19_high IS NULL THEN
1234 dbms_sql.bind_variable(l_cursor,'l_segment19_low',l_segment19_low);
1235
1236 ELSIF p_map_t(i).segment19_low IS NULL
1237 and p_map_t(i).segment19_high IS NOT NULL THEN
1238 dbms_sql.bind_variable(l_cursor,'l_segment19_high',l_segment19_high);
1239
1240 ELSIF p_map_t(i).segment19_low IS NOT NULL
1241 and p_map_t(i).segment19_high IS NOT NULL THEN
1242 dbms_sql.bind_variable(l_cursor,'l_segment19_low',l_segment19_low);
1243 dbms_sql.bind_variable(l_cursor,'l_segment19_high',l_segment19_high);
1244 END IF;
1245
1246 /* segment 20 */
1247 IF p_map_t(i).segment20_low IS NOT NULL
1248 and p_map_t(i).segment20_high IS NULL THEN
1249 dbms_sql.bind_variable(l_cursor,'l_segment20_low',l_segment20_low);
1250
1251 ELSIF p_map_t(i).segment20_low IS NULL
1252 and p_map_t(i).segment20_high IS NOT NULL THEN
1253 dbms_sql.bind_variable(l_cursor,'l_segment20_high',l_segment20_high);
1254
1255 ELSIF p_map_t(i).segment20_low IS NOT NULL
1256 and p_map_t(i).segment20_high IS NOT NULL THEN
1257 dbms_sql.bind_variable(l_cursor,'l_segment20_low',l_segment20_low);
1258 dbms_sql.bind_variable(l_cursor,'l_segment20_high',l_segment20_high);
1259 END IF;
1260
1261 /* segment 21 */
1262 IF p_map_t(i).segment21_low IS NOT NULL
1263 and p_map_t(i).segment21_high IS NULL THEN
1264 dbms_sql.bind_variable(l_cursor,'l_segment21_low',l_segment21_low);
1265
1266 ELSIF p_map_t(i).segment21_low IS NULL
1267 and p_map_t(i).segment21_high IS NOT NULL THEN
1268 dbms_sql.bind_variable(l_cursor,'l_segment21_high',l_segment21_high);
1269
1270 ELSIF p_map_t(i).segment21_low IS NOT NULL
1271 and p_map_t(i).segment21_high IS NOT NULL THEN
1272 dbms_sql.bind_variable(l_cursor,'l_segment21_low',l_segment21_low);
1273 dbms_sql.bind_variable(l_cursor,'l_segment21_high',l_segment21_high);
1274 END IF;
1275
1276 /* segment 22 */
1277 IF p_map_t(i).segment22_low IS NOT NULL
1278 and p_map_t(i).segment22_high IS NULL THEN
1279 dbms_sql.bind_variable(l_cursor,'l_segment22_low',l_segment22_low);
1280
1281 ELSIF p_map_t(i).segment22_low IS NULL
1282 and p_map_t(i).segment22_high IS NOT NULL THEN
1283 dbms_sql.bind_variable(l_cursor,'l_segment22_high',l_segment22_high);
1284
1285 ELSIF p_map_t(i).segment22_low IS NOT NULL
1286 and p_map_t(i).segment22_high IS NOT NULL THEN
1287 dbms_sql.bind_variable(l_cursor,'l_segment22_low',l_segment22_low);
1288 dbms_sql.bind_variable(l_cursor,'l_segment22_high',l_segment22_high);
1289 END IF;
1290
1291 /* segment 23 */
1292 IF p_map_t(i).segment23_low IS NOT NULL
1293 and p_map_t(i).segment23_high IS NULL THEN
1294 dbms_sql.bind_variable(l_cursor,'l_segment23_low',l_segment23_low);
1295
1296 ELSIF p_map_t(i).segment23_low IS NULL
1297 and p_map_t(i).segment23_high IS NOT NULL THEN
1298 dbms_sql.bind_variable(l_cursor,'l_segment23_high',l_segment23_high);
1299
1300 ELSIF p_map_t(i).segment23_low IS NOT NULL
1301 and p_map_t(i).segment23_high IS NOT NULL THEN
1302 dbms_sql.bind_variable(l_cursor,'l_segment23_low',l_segment23_low);
1303 dbms_sql.bind_variable(l_cursor,'l_segment23_high',l_segment23_high);
1304 END IF;
1305
1306 /* segment 24 */
1307 IF p_map_t(i).segment24_low IS NOT NULL
1308 and p_map_t(i).segment24_high IS NULL THEN
1309 dbms_sql.bind_variable(l_cursor,'l_segment24_low',l_segment24_low);
1310
1311 ELSIF p_map_t(i).segment24_low IS NULL
1312 and p_map_t(i).segment24_high IS NOT NULL THEN
1313 dbms_sql.bind_variable(l_cursor,'l_segment24_high',l_segment24_high);
1314
1315 ELSIF p_map_t(i).segment24_low IS NOT NULL
1316 and p_map_t(i).segment24_high IS NOT NULL THEN
1317 dbms_sql.bind_variable(l_cursor,'l_segment24_low',l_segment24_low);
1318 dbms_sql.bind_variable(l_cursor,'l_segment24_high',l_segment24_high);
1319 END IF;
1320
1321 /* segment 25 */
1322 IF p_map_t(i).segment25_low IS NOT NULL
1323 and p_map_t(i).segment25_high IS NULL THEN
1324 dbms_sql.bind_variable(l_cursor,'l_segment25_low',l_segment25_low);
1325
1326 ELSIF p_map_t(i).segment25_low IS NULL
1327 and p_map_t(i).segment25_high IS NOT NULL THEN
1328 dbms_sql.bind_variable(l_cursor,'l_segment25_high',l_segment25_high);
1329
1330 ELSIF p_map_t(i).segment25_low IS NOT NULL
1331 and p_map_t(i).segment25_high IS NOT NULL THEN
1332 dbms_sql.bind_variable(l_cursor,'l_segment25_low',l_segment25_low);
1333 dbms_sql.bind_variable(l_cursor,'l_segment25_high',l_segment25_high);
1334 END IF;
1335
1336 /* segment 26 */
1337 IF p_map_t(i).segment26_low IS NOT NULL
1338 and p_map_t(i).segment26_high IS NULL THEN
1339 dbms_sql.bind_variable(l_cursor,'l_segment26_low',l_segment26_low);
1340
1341 ELSIF p_map_t(i).segment26_low IS NULL
1342 and p_map_t(i).segment26_high IS NOT NULL THEN
1343 dbms_sql.bind_variable(l_cursor,'l_segment26_high',l_segment26_high);
1344
1345 ELSIF p_map_t(i).segment26_low IS NOT NULL
1346 and p_map_t(i).segment26_high IS NOT NULL THEN
1347 dbms_sql.bind_variable(l_cursor,'l_segment26_low',l_segment26_low);
1348 dbms_sql.bind_variable(l_cursor,'l_segment26_high',l_segment26_high);
1349 END IF;
1350
1351 /* segment 27 */
1352 IF p_map_t(i).segment27_low IS NOT NULL
1353 and p_map_t(i).segment27_high IS NULL THEN
1354 dbms_sql.bind_variable(l_cursor,'l_segment27_low',l_segment27_low);
1355
1356 ELSIF p_map_t(i).segment27_low IS NULL
1357 and p_map_t(i).segment27_high IS NOT NULL THEN
1358 dbms_sql.bind_variable(l_cursor,'l_segment27_high',l_segment27_high);
1359
1360 ELSIF p_map_t(i).segment27_low IS NOT NULL
1361 and p_map_t(i).segment27_high IS NOT NULL THEN
1362 dbms_sql.bind_variable(l_cursor,'l_segment27_low',l_segment27_low);
1363 dbms_sql.bind_variable(l_cursor,'l_segment27_high',l_segment27_high);
1364 END IF;
1365
1366 /* segment 28 */
1367 IF p_map_t(i).segment28_low IS NOT NULL
1368 and p_map_t(i).segment28_high IS NULL THEN
1369 dbms_sql.bind_variable(l_cursor,'l_segment28_low',l_segment28_low);
1370
1371 ELSIF p_map_t(i).segment28_low IS NULL
1372 and p_map_t(i).segment28_high IS NOT NULL THEN
1373 dbms_sql.bind_variable(l_cursor,'l_segment28_high',l_segment28_high);
1374
1375 ELSIF p_map_t(i).segment28_low IS NOT NULL
1376 and p_map_t(i).segment28_high IS NOT NULL THEN
1377 dbms_sql.bind_variable(l_cursor,'l_segment28_low',l_segment28_low);
1378 dbms_sql.bind_variable(l_cursor,'l_segment28_high',l_segment28_high);
1379 END IF;
1380
1381 /* segment 29 */
1382 IF p_map_t(i).segment29_low IS NOT NULL
1383 and p_map_t(i).segment29_high IS NULL THEN
1384 dbms_sql.bind_variable(l_cursor,'l_segment29_low',l_segment29_low);
1385
1386 ELSIF p_map_t(i).segment29_low IS NULL
1387 and p_map_t(i).segment29_high IS NOT NULL THEN
1388 dbms_sql.bind_variable(l_cursor,'l_segment29_high',l_segment29_high);
1389
1390 ELSIF p_map_t(i).segment29_low IS NOT NULL
1391 and p_map_t(i).segment29_high IS NOT NULL THEN
1392 dbms_sql.bind_variable(l_cursor,'l_segment29_low',l_segment29_low);
1393 dbms_sql.bind_variable(l_cursor,'l_segment29_high',l_segment29_high);
1394 END IF;
1395
1396 /* segment 30 */
1397 IF p_map_t(i).segment30_low IS NOT NULL
1398 and p_map_t(i).segment30_high IS NULL THEN
1399 dbms_sql.bind_variable(l_cursor,'l_segment30_low',l_segment30_low);
1400
1401 ELSIF p_map_t(i).segment30_low IS NULL
1402 and p_map_t(i).segment30_high IS NOT NULL THEN
1403 dbms_sql.bind_variable(l_cursor,'l_segment30_high',l_segment30_high);
1404
1405 ELSIF p_map_t(i).segment30_low IS NOT NULL
1406 and p_map_t(i).segment30_high IS NOT NULL THEN
1407 dbms_sql.bind_variable(l_cursor,'l_segment30_low',l_segment30_low);
1408 dbms_sql.bind_variable(l_cursor,'l_segment30_high',l_segment30_high);
1409 END IF;
1410
1411 dbms_sql.define_column (l_cursor,1,l_ccid_temp);
1412 l_rows := dbms_sql.execute(l_cursor);
1413
1414 l_info := 'populating the CCID pl/sql table';
1415
1416 LOOP
1417
1418 l_count := dbms_sql.fetch_rows( l_cursor );
1419 EXIT WHEN l_count <> 1;
1420 dbms_sql.column_value(l_cursor,1,l_ccid_temp);
1421
1422 l_ccid_tbl_count := l_ccid_tbl_count + 1;
1423
1424 p_ccid_t(l_ccid_tbl_count).map_row := i;
1425 p_ccid_t(l_ccid_tbl_count).map_id := p_map_t(i).loc_acc_map_id;
1426 p_ccid_t(l_ccid_tbl_count).ccid := l_ccid_temp;
1427 p_ccid_t(l_ccid_tbl_count).act_amount := 0;
1428 p_ccid_t(l_ccid_tbl_count).bud_amount := 0;
1429
1430 END LOOP;
1431
1432 IF dbms_sql.is_open (l_cursor) THEN
1433 dbms_sql.close_cursor (l_cursor);
1434 END IF;
1435
1436
1437
1438
1439 END LOOP;
1440
1441 Put_Log('Number of rows in CCID pl/sql table: '||to_char(l_ccid_tbl_count));
1442
1443 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.get_ccids (-)');
1444
1445 EXCEPTION
1446 WHEN OTHERS THEN
1447 Put_Log('Error while '|| l_info);
1448 Raise;
1449
1450 END get_ccids;
1451
1452 /*===========================================================================+
1453 | PROCEDURE
1454 | get_amounts
1455 |
1456 | DESCRIPTION
1457 |
1458 | ARGUMENTS:
1459 |
1460 | NOTES:
1461 |
1462 | MODIFICATION HISTORY
1463 | 10-JUL-03 Kiran o created
1464 +===========================================================================*/
1465
1466 PROCEDURE get_amounts(p_sob_id IN NUMBER,
1467 p_actual_flag IN VARCHAR2,
1468 p_budget_name IN VARCHAR2,
1469 p_map_t IN loc_acc_map_tbl,
1470 p_ccid_t IN OUT NOCOPY ccid_tbl)
1471 IS
1472
1473 l_info VARCHAR2(240);
1474
1475 /* cursor for currency code */
1476 CURSOR curr_code(sob_id NUMBER) IS
1477 select gl_sob.currency_code as CURRENCY_CODE
1478 from gl_sets_of_books gl_sob
1479 where gl_sob.set_of_books_id = sob_id;
1480
1481 l_currency_code VARCHAR2(15) := 'USD';
1482
1483 /* cursor for budget ID */
1484 CURSOR budget(name VARCHAR2, sob_id NUMBER) IS
1485 select gbv.budget_version_id
1486 from gl_budgets gb,
1487 gl_budget_versions gbv
1488 where gb.ledger_id = sob_id
1489 and gb.budget_name = name
1490 and gbv.budget_name = gb.budget_name
1491 order by gb.current_version_id;
1492
1493 l_budget_version_id NUMBER;
1494
1495 /* cursor for actual */
1496 CURSOR act_amt (ccid NUMBER,
1497 sob_id NUMBER,
1498 currency VARCHAR2,
1499 start_dt DATE,
1500 end_dt DATE) IS
1501 Select sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
1502 From gl_balances gb
1503 Where gb.code_combination_id = ccid
1504 And gb.ledger_id = sob_id
1505 And gb.currency_code = currency
1506 And gb.actual_flag = 'A'
1507 And gb.template_id IS NULL
1508 And gb.period_name IN
1509 (Select gps.period_name
1510 From gl_period_statuses gps
1511 Where application_id = 101
1512 And ledger_id = sob_id
1513 And gps.start_date >= start_dt
1514 And gps.end_date <= end_dt);
1515
1516 /* cursor for budgeted */
1517 CURSOR bud_amt (ccid NUMBER,
1518 sob_id NUMBER,
1519 currency VARCHAR2,
1520 bud_id NUMBER,
1521 start_dt DATE,
1522 end_dt DATE) IS
1523 Select sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
1524 From gl_balances gb
1525 Where code_combination_id = ccid
1526 And ledger_id = sob_id
1527 And currency_code = currency
1528 And actual_flag = 'B'
1529 And budget_version_id = bud_id
1530 And template_id IS NULL
1531 And period_name IN
1532 (Select gps.period_name
1533 From gl_period_statuses gps
1534 Where application_id = 101
1535 And ledger_id = sob_id
1536 And gps.start_date >= start_dt
1537 And gps.end_date <= end_dt);
1538
1539 BEGIN
1540
1541 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.get_amount (+)');
1542
1543 l_info := 'getting the currency code';
1544
1545 OPEN curr_code(p_sob_id);
1546 FETCH curr_code INTO l_currency_code;
1547 CLOSE curr_code;
1548
1549 IF p_actual_flag = 'A' THEN
1550
1551 l_info := 'getting the actual amounts';
1552
1553 FOR i IN 1..p_ccid_t.COUNT LOOP
1554
1555 Put_Log('loop: '||to_char(i)||
1556 ' CCID: '||to_char(p_ccid_t(i).ccid)||
1557 ' SOB: '||to_char(p_sob_id)||
1558 ' CURRENY: '||l_currency_code||
1559 ' FROM DT: '||to_char(p_map_t(p_ccid_t(i).map_row).effective_from_date)||
1560 ' TO DT: '||to_char(p_map_t(p_ccid_t(i).map_row).effective_to_date));
1561
1562 OPEN act_amt(p_ccid_t(i).ccid,
1563 p_sob_id,
1564 l_currency_code,
1565 p_map_t(p_ccid_t(i).map_row).effective_from_date,
1566 p_map_t(p_ccid_t(i).map_row).effective_to_date);
1567 FETCH act_amt INTO p_ccid_t(i).act_amount;
1568 p_ccid_t(i).bud_amount := 0;
1569 Put_Log('Amount'||to_char(i)||': '||to_char(p_ccid_t(i).act_amount));
1570 CLOSE act_amt;
1571
1572 END LOOP;
1573
1574 ELSIF p_actual_flag = 'B' THEN
1575
1576 l_info := 'getting the budget version ID';
1577 OPEN budget(p_budget_name , p_sob_id);
1578 LOOP
1579 FETCH budget INTO l_budget_version_id;
1580 EXIT when budget%NOTFOUND;
1581 END LOOP;
1582 CLOSE budget;
1583
1584 l_info := 'getting the budget amounts';
1585
1586 FOR i IN 1..p_ccid_t.COUNT LOOP
1587
1588 Put_Log('loop: '||to_char(i)||
1589 ' CCID: '||to_char(p_ccid_t(i).ccid)||
1590 ' SOB: '||to_char(p_sob_id)||
1591 ' CURRENY: '||l_currency_code||
1592 ' BUDGET: '||to_char(l_budget_version_id)||
1593 ' FROM DT: '||to_char(p_map_t(p_ccid_t(i).map_row).effective_from_date)||
1594 ' TO DT: '||to_char(p_map_t(p_ccid_t(i).map_row).effective_to_date));
1595
1596 OPEN bud_amt(p_ccid_t(i).ccid,
1597 p_sob_id,
1598 l_currency_code,
1599 l_budget_version_id,
1600 p_map_t(p_ccid_t(i).map_row).effective_from_date,
1601 p_map_t(p_ccid_t(i).map_row).effective_to_date);
1602 FETCH bud_amt INTO p_ccid_t(i).bud_amount;
1603 p_ccid_t(i).act_amount := 0;
1604 Put_Log('Amount'||to_char(i)||': '||to_char(p_ccid_t(i).bud_amount));
1605 CLOSE bud_amt;
1606
1607 END LOOP;
1608
1609 END IF;
1610
1611 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.get_amount (-)');
1612
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 Put_Log('Error while '|| l_info);
1616 Raise;
1617
1618 END get_amounts;
1619
1620 /*===========================================================================+
1621 | PROCEDURE
1622 | populate_rec_exp_interface
1623 |
1624 | DESCRIPTION
1625 |
1626 | ARGUMENTS:
1627 |
1628 | NOTES:
1629 |
1630 | MODIFICATION HISTORY
1631 | 10-JUL-03 Kiran o created
1632 +===========================================================================*/
1633
1634 PROCEDURE populate_rec_exp_itf(p_sob_id IN NUMBER,
1635 p_from_date IN DATE,
1636 p_to_date IN DATE,
1637 p_map_t IN loc_acc_map_tbl,
1638 p_ccid_t IN ccid_tbl)
1639 IS
1640
1641 l_info VARCHAR2(240);
1642 l_insert_ctr NUMBER := 0;
1643
1644 /* cursor for currency code */
1645 CURSOR curr_code(sob_id NUMBER) IS
1646 select gl_sob.currency_code as CURRENCY_CODE
1647 from gl_sets_of_books gl_sob
1648 where gl_sob.set_of_books_id = sob_id;
1649
1650 l_currency_code VARCHAR2(15) := 'USD';
1651
1652 BEGIN
1653
1654 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.populate_rec_exp_itf (+)');
1655
1656 Put_Log('SOB: '||to_char(p_sob_id)||' FRM DT: '||p_from_date||' TO DT: '||p_to_date);
1657
1658 l_info := 'fetching currency code';
1659
1660 OPEN curr_code(p_sob_id);
1661 FETCH curr_code INTO l_currency_code;
1662 CLOSE curr_code;
1663
1664 FOR i IN 1..p_ccid_t.COUNT LOOP
1665
1666 l_info := 'inserting into pn_rec_exp_itf table for CCID: '||to_char(p_ccid_t(i).ccid);
1667
1668 insert into pn_rec_exp_itf
1669 (
1670 EXPENSE_LINE_DTL_ID,
1671 PROPERTY_ID,
1672 LOCATION_ID,
1673 EXPENSE_TYPE_CODE,
1674 EXPENSE_ACCOUNT_ID,
1675 ACCOUNT_DESCRIPTION,
1676 ACTUAL_AMOUNT,
1677 BUDGETED_AMOUNT,
1678 CURRENCY_CODE,
1679 FROM_DATE,
1680 TO_DATE,
1681 TRANSFER_FLAG,
1682 MODE_FLAG,
1683 LAST_UPDATE_DATE,
1684 LAST_UPDATED_BY,
1685 CREATION_DATE,
1686 CREATED_BY,
1687 LAST_UPDATE_LOGIN,
1688 ATTRIBUTE_CATEGORY,
1689 ATTRIBUTE1,
1690 ATTRIBUTE2,
1691 ATTRIBUTE3,
1692 ATTRIBUTE4,
1693 ATTRIBUTE5,
1694 ATTRIBUTE6,
1695 ATTRIBUTE7,
1696 ATTRIBUTE8,
1697 ATTRIBUTE9,
1698 ATTRIBUTE10,
1699 ATTRIBUTE11,
1700 ATTRIBUTE12,
1701 ATTRIBUTE13,
1702 ATTRIBUTE14,
1703 ATTRIBUTE15,
1704 ORG_ID
1705 )
1706 values
1707 (
1708 null,
1709 p_map_t(p_ccid_t(i).map_row).PROPERTY_ID,
1710 p_map_t(p_ccid_t(i).map_row).LOCATION_ID,
1711 p_map_t(p_ccid_t(i).map_row).EXPENSE_TYPE_CODE,
1712 p_ccid_t(i).CCID,
1713 null,
1714 nvl(p_ccid_t(i).ACT_AMOUNT,0),
1715 nvl(p_ccid_t(i).BUD_AMOUNT,0),
1716 l_currency_code,
1717 P_FROM_DATE,
1718 P_TO_DATE,
1719 'N',
1720 null,
1721 SYSDATE,
1722 nvl(fnd_profile.value('USER_ID'),-1),
1723 SYSDATE,
1724 nvl(fnd_profile.value('USER_ID'),-1),
1725 nvl(fnd_profile.value('USER_ID'),-1),
1726 null,
1727 null,
1728 null,
1729 null,
1730 null,
1731 null,
1732 null,
1733 null,
1734 null,
1735 null,
1736 null,
1737 null,
1738 null,
1739 null,
1740 null,
1741 null,
1742 to_number(pn_mo_cache_utils.get_current_org_id)
1743 );
1744
1745 l_insert_ctr := l_insert_ctr + 1;
1746
1747 END LOOP;
1748
1749 Put_Log('Number of records processed: '||to_char(p_ccid_t.COUNT));
1750 Put_Log('Number of records inserted: '||to_char(l_insert_ctr));
1751
1752 --commit;
1753
1754 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.populate_rec_exp_itf (-)');
1755
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 Put_Log('Error while '|| l_info);
1759 Raise;
1760
1761 END populate_rec_exp_itf;
1762
1763 /*------------------------- PUBLIC PROCEDURES -----------------------------*/
1764
1765 /*===========================================================================+
1766 | PROCEDURE
1767 | extract_expense_from_gl
1768 |
1769 | DESCRIPTION
1770 |
1771 | ARGUMENTS:
1772 |
1773 | NOTES:
1774 |
1775 | MODIFICATION HISTORY
1776 | 10-JUL-03 Kiran o created
1777 +===========================================================================*/
1778
1779 PROCEDURE extract_expense_from_gl(
1780 errbuf OUT NOCOPY VARCHAR2,
1781 retcode OUT NOCOPY VARCHAR2,
1782 p_loc_acc_map_hdr_id IN VARCHAR2,
1783 p_location_id IN VARCHAR2,
1784 p_property_id IN VARCHAR2,
1785 p_set_of_books_id IN VARCHAR2,
1786 p_period_start IN gl_period_statuses.period_name%TYPE,
1787 p_period_end IN gl_period_statuses.period_name%TYPE,
1788 p_balance_type_code IN gl_lookups.lookup_code%TYPE,
1789 p_balance_type_code_hide IN gl_lookups.lookup_code%TYPE DEFAULT NULL,
1790 p_budget_name IN gl_budgets.budget_name%TYPE,
1791 p_populate_rec IN VARCHAR2,
1792 p_populate_rec_hide IN VARCHAR2,
1793 p_as_of_date IN VARCHAR2,
1794 p_period_start_date IN VARCHAR2,
1795 p_period_end_date IN VARCHAR2,
1796 p_populate_expcl_dtl IN VARCHAR2,
1797 p_populate_arcl_dtl IN VARCHAR2,
1798 p_override IN VARCHAR2,
1799 p_rec_exp_num IN VARCHAR2)
1800 IS
1801
1802 /* exceptions */
1803 BAD_INPUT_EXCEPTION EXCEPTION;
1804
1805 /* interface pl/sql table */
1806 recovery_itf_tbl rec_exp_itf_tbl;
1807
1808 /* mapping pl/sql table */
1809 map_tbl loc_acc_map_tbl;
1810
1811 /* period from to date tbl */
1812 period_from_to_date_tbl periods_tbl;
1813
1814 /* ccid table - has fk to map table */
1815 code_combinations_tbl ccid_tbl;
1816
1817 /* CURSORS FOR PERIODS */
1818 /* period dates by period name */
1819 CURSOR period_dates_by_period_cur(period VARCHAR2) IS
1820 Select g.period_name,
1821 g.start_date,
1822 g.end_date
1823 From gl_period_statuses g,
1824 gl_sets_of_books b
1825 Where g.application_id = 101
1826 And g.ledger_id = p_set_of_books_id
1827 And b.set_of_books_id = g.ledger_id
1828 And g.period_type = b.accounted_period_type
1829 And g.period_name = period;
1830
1831 /* periods for start-end dates */
1832 CURSOR period_dates_by_dates_cur(start_dt DATE, end_dt DATE) IS
1833 Select g.period_name,
1834 g.start_date,
1835 g.end_date
1836 From gl_period_statuses g,
1837 gl_sets_of_books b
1838 Where g.application_id = 101
1839 And g.ledger_id = p_set_of_books_id
1840 And b.set_of_books_id = g.set_of_books_id
1841 And g.period_type = b.accounted_period_type
1842 And g.start_date >= start_dt
1843 And g.end_date <= end_dt
1844 Order by g.start_date;
1845
1846 /* period start end dates etc */
1847 l_period_count NUMBER;
1848 l_period_name_st VARCHAR2(15);
1849 l_period_start_stdt DATE;
1850 l_period_start_enddt DATE;
1851 l_period_name_end VARCHAR2(15);
1852 l_period_end_stdt DATE;
1853 l_period_end_enddt DATE;
1854
1855 /* cursors for mapping */
1856
1857 CURSOR mapping IS
1858 Select map.*
1859 From PN_LOC_ACC_MAP_ALL map
1860 Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
1861 And map.RECOVERABLE_FLAG = 'Y'
1862 And map.effective_from_date <= l_period_end_enddt
1863 And map.effective_to_date >= l_period_start_stdt;
1864
1865 CURSOR mapping_loc IS
1866 Select map.*
1867 From PN_LOC_ACC_MAP_ALL map
1868 Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
1869 And map.LOCATION_ID = p_location_id
1870 And map.RECOVERABLE_FLAG = 'Y'
1871 And map.effective_from_date <= l_period_end_enddt
1872 And map.effective_to_date >= l_period_start_stdt;
1873
1874 CURSOR mapping_prop IS
1875 Select map.*
1876 From PN_LOC_ACC_MAP_ALL map
1877 Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
1878 And map.PROPERTY_ID = p_property_id
1879 And map.RECOVERABLE_FLAG = 'Y'
1880 And map.effective_from_date <= l_period_end_enddt
1881 And map.effective_to_date >= l_period_start_stdt;
1882
1883 CURSOR mapping_loc_prop IS
1884 Select map.*
1885 From PN_LOC_ACC_MAP_ALL map
1886 Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
1887 And map.PROPERTY_ID = p_property_id
1888 And map.LOCATION_ID = p_location_id
1889 And map.RECOVERABLE_FLAG = 'Y'
1890 And map.effective_from_date <= l_period_end_enddt
1891 And map.effective_to_date >= l_period_start_stdt;
1892
1893 /* variables for mapping operations */
1894 l_map_count NUMBER;
1895
1896 /* variables for logging */
1897 l_info VARCHAR2(240);
1898 l_counter BINARY_INTEGER := 0;
1899
1900 /* variables for dates for calling export to Rec pgm */
1901 l_as_of_date DATE := fnd_date.canonical_to_date(p_as_of_date);
1902 l_period_start_date DATE := fnd_date.canonical_to_date(p_period_start_date);
1903 l_period_end_date DATE := fnd_date.canonical_to_date(p_period_end_date);
1904
1905 /* cursor for currency code */
1906 CURSOR curr_code(sob_id NUMBER) IS
1907 select gl_sob.currency_code as CURRENCY_CODE
1908 from gl_sets_of_books gl_sob
1909 where gl_sob.set_of_books_id = sob_id;
1910
1911 l_currency_code VARCHAR2(15) := 'USD';
1912
1913 /* cursor for property code */
1914 CURSOR prop_code(prop_id NUMBER) IS
1915 select prop.PROPERTY_CODE
1916 from PN_PROPERTIES_ALL prop
1917 where prop.property_id = prop_id;
1918
1919 l_property_code VARCHAR2(90);
1920
1921 /* cursor for location code */
1922 CURSOR loc_code(loc_id NUMBER) IS
1923 select loc.LOCATION_CODE
1924 from pn_locations_ALL loc
1925 where loc.LOCATION_ID = loc_id;
1926
1927 l_location_code VARCHAR2(90);
1928
1929 BEGIN
1930
1931 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.extract_expense_from_gl (+)');
1932
1933 /* --- Log all the parameters passed in --- */
1934
1935 l_info := 'logging all input params';
1936
1937 Put_Log('Location to account mapping ID: '||p_loc_acc_map_hdr_id);
1938 Put_Log('Location ID: '||p_location_id);
1939 Put_Log('Property ID: '||p_property_id);
1940 Put_Log('Set of books ID: '||p_set_of_books_id);
1941 Put_Log('Period Start: '||p_period_start);
1942 Put_Log('Period End: '||p_period_end);
1943 Put_Log('Balance type code: '||p_balance_type_code);
1944 Put_Log('Balance type code hidden: '||p_balance_type_code_hide);
1945 Put_Log('Budget Name: '||p_budget_name);
1946 Put_Log('Populate recoveries: '||p_populate_rec);
1947 Put_Log('Populate recoveries hidden: '||p_populate_rec_hide);
1948 Put_Log('As of date: '||p_as_of_date);
1949 Put_Log('Period start date: '||p_period_start_date);
1950 Put_Log('Period end date: '||p_period_end_date);
1951 Put_Log('Populate expense class details: '||p_populate_expcl_dtl);
1952 Put_Log('Populate area class details: '||p_populate_arcl_dtl);
1953 Put_Log('Keep overrides: '||p_override);
1954 Put_Log('Recovery Expense Number: '||p_rec_exp_num);
1955
1956 /* --- Bug#3112803 perform input validation in case of ---
1957 --- Populate Recoveries is set to 'YES' ---
1958 --- Only minmum validation is done here to make sure ---
1959 --- that we dont pull in data from GL_BALANCES even if---
1960 --- minimal inputs are not present. The Expense ---
1961 --- Extraction program will do a detailed validation --- */
1962 IF p_populate_rec = 'Y' THEN
1963
1964 IF p_property_id IS NULL
1965 AND p_location_id IS NULL THEN
1966
1967 fnd_message.set_name('PN','PN_LOC_PROP_REQ');
1968 RAISE BAD_INPUT_EXCEPTION;
1969
1970 ELSIF p_rec_exp_num IS NULL AND
1971 pn_mo_cache_utils.get_profile_value
1972 ( 'PN_AUTOMATIC_REC_EXPENSE_NUM'
1973 , TO_NUMBER(pn_mo_cache_utils.get_current_org_id)) = 'N' THEN
1974
1975 fnd_message.set_name('PN','PN_REC_EXP_NUM_REQ');
1976 RAISE BAD_INPUT_EXCEPTION;
1977
1978 END IF;
1979
1980 END IF;
1981
1982 /* --- first get the start and end dates for the periods --- */
1983 l_info := 'getting start and end dates for period from - to';
1984
1985 OPEN period_dates_by_period_cur(p_period_start);
1986 FETCH period_dates_by_period_cur
1987 INTO l_period_name_st, l_period_start_stdt, l_period_start_enddt;
1988 CLOSE period_dates_by_period_cur;
1989
1990 OPEN period_dates_by_period_cur(p_period_end);
1991 FETCH period_dates_by_period_cur
1992 INTO l_period_name_end, l_period_end_stdt, l_period_end_enddt;
1993 CLOSE period_dates_by_period_cur;
1994
1995 /* --- get all the periods between period from - to --- */
1996 l_info := 'getting all period name, dates for period from - to';
1997
1998 l_counter := 0;
1999 FOR periods in period_dates_by_dates_cur
2000 (l_period_start_stdt,
2001 l_period_end_enddt) LOOP
2002
2003 l_counter := l_counter + 1;
2004
2005 period_from_to_date_tbl(l_counter).period_name := periods.period_name;
2006 period_from_to_date_tbl(l_counter).start_date := periods.start_date;
2007 period_from_to_date_tbl(l_counter).end_date := periods.end_date;
2008
2009 END LOOP;
2010
2011 l_period_count := period_from_to_date_tbl.COUNT;
2012 Put_Log('Number of Perods fetched: '||to_char(l_period_count));
2013
2014 /* --- open the appropriate cursor for mapping based on ---
2015 --- if the mapping name/location/property is entered --- */
2016 l_info := 'populating the mapping pl/sql table';
2017
2018 map_tbl.delete; /* reset the table */
2019 l_counter := 0; /* reset the counter */
2020
2021 if (p_location_id IS NULL and p_property_id IS NULL) then
2022
2023 /*OPEN mapping;
2024 FETCH mapping BULK COLLECT INTO map_tbl;
2025 CLOSE mapping;*/
2026
2027 FOR map in mapping LOOP
2028
2029 l_counter := l_counter + 1;
2030
2031 map_tbl(l_counter).LOC_ACC_MAP_ID := map.LOC_ACC_MAP_ID;
2032 map_tbl(l_counter).LOC_ACC_MAP_HDR_ID := map.LOC_ACC_MAP_HDR_ID;
2033 map_tbl(l_counter).PROPERTY_ID := map.PROPERTY_ID;
2034 map_tbl(l_counter).LOCATION_ID := map.LOCATION_ID;
2035 map_tbl(l_counter).ACCOUNT_LOW := map.ACCOUNT_LOW;
2036 map_tbl(l_counter).ACCOUNT_HIGH := map.ACCOUNT_HIGH;
2037 map_tbl(l_counter).EXPENSE_TYPE_CODE := map.EXPENSE_TYPE_CODE;
2038 map_tbl(l_counter).EFFECTIVE_FROM_DATE := map.EFFECTIVE_FROM_DATE;
2039 map_tbl(l_counter).EFFECTIVE_TO_DATE := map.EFFECTIVE_TO_DATE;
2040 map_tbl(l_counter).RECOVERABLE_FLAG := map.RECOVERABLE_FLAG;
2041 map_tbl(l_counter).SEGMENT1_LOW := map.SEGMENT1_LOW;
2042 map_tbl(l_counter).SEGMENT1_HIGH := map.SEGMENT1_HIGH;
2043 map_tbl(l_counter).SEGMENT2_LOW := map.SEGMENT2_LOW;
2044 map_tbl(l_counter).SEGMENT2_HIGH := map.SEGMENT2_HIGH;
2045 map_tbl(l_counter).SEGMENT3_LOW := map.SEGMENT3_LOW;
2046 map_tbl(l_counter).SEGMENT3_HIGH := map.SEGMENT3_HIGH;
2047 map_tbl(l_counter).SEGMENT4_LOW := map.SEGMENT4_LOW;
2048 map_tbl(l_counter).SEGMENT4_HIGH := map.SEGMENT4_HIGH;
2049 map_tbl(l_counter).SEGMENT5_LOW := map.SEGMENT5_LOW;
2050 map_tbl(l_counter).SEGMENT5_HIGH := map.SEGMENT5_HIGH;
2051 map_tbl(l_counter).SEGMENT6_LOW := map.SEGMENT6_LOW;
2052 map_tbl(l_counter).SEGMENT6_HIGH := map.SEGMENT6_HIGH;
2053 map_tbl(l_counter).SEGMENT7_LOW := map.SEGMENT7_LOW;
2054 map_tbl(l_counter).SEGMENT7_HIGH := map.SEGMENT7_HIGH;
2055 map_tbl(l_counter).SEGMENT8_LOW := map.SEGMENT8_LOW;
2056 map_tbl(l_counter).SEGMENT8_HIGH := map.SEGMENT8_HIGH;
2057 map_tbl(l_counter).SEGMENT9_LOW := map.SEGMENT9_LOW;
2058 map_tbl(l_counter).SEGMENT9_HIGH := map.SEGMENT9_HIGH;
2059 map_tbl(l_counter).SEGMENT10_LOW := map.SEGMENT10_LOW;
2060 map_tbl(l_counter).SEGMENT10_HIGH := map.SEGMENT10_HIGH;
2061 map_tbl(l_counter).SEGMENT11_LOW := map.SEGMENT11_LOW;
2062 map_tbl(l_counter).SEGMENT11_HIGH := map.SEGMENT11_HIGH;
2063 map_tbl(l_counter).SEGMENT12_LOW := map.SEGMENT12_LOW;
2064 map_tbl(l_counter).SEGMENT12_HIGH := map.SEGMENT12_HIGH;
2065 map_tbl(l_counter).SEGMENT13_LOW := map.SEGMENT13_LOW;
2066 map_tbl(l_counter).SEGMENT13_HIGH := map.SEGMENT13_HIGH;
2067 map_tbl(l_counter).SEGMENT14_LOW := map.SEGMENT14_LOW;
2068 map_tbl(l_counter).SEGMENT14_HIGH := map.SEGMENT14_HIGH;
2069 map_tbl(l_counter).SEGMENT15_LOW := map.SEGMENT15_LOW;
2070 map_tbl(l_counter).SEGMENT15_HIGH := map.SEGMENT15_HIGH;
2071 map_tbl(l_counter).SEGMENT16_LOW := map.SEGMENT16_LOW;
2072 map_tbl(l_counter).SEGMENT16_HIGH := map.SEGMENT16_HIGH;
2073 map_tbl(l_counter).SEGMENT17_LOW := map.SEGMENT17_LOW;
2074 map_tbl(l_counter).SEGMENT17_HIGH := map.SEGMENT17_HIGH;
2075 map_tbl(l_counter).SEGMENT18_LOW := map.SEGMENT18_LOW;
2076 map_tbl(l_counter).SEGMENT18_HIGH := map.SEGMENT18_HIGH;
2077 map_tbl(l_counter).SEGMENT19_LOW := map.SEGMENT19_LOW;
2078 map_tbl(l_counter).SEGMENT19_HIGH := map.SEGMENT19_HIGH;
2079 map_tbl(l_counter).SEGMENT20_LOW := map.SEGMENT20_LOW;
2080 map_tbl(l_counter).SEGMENT20_HIGH := map.SEGMENT20_HIGH;
2081 map_tbl(l_counter).SEGMENT21_LOW := map.SEGMENT21_LOW;
2082 map_tbl(l_counter).SEGMENT21_HIGH := map.SEGMENT21_HIGH;
2083 map_tbl(l_counter).SEGMENT22_LOW := map.SEGMENT22_LOW;
2084 map_tbl(l_counter).SEGMENT22_HIGH := map.SEGMENT22_HIGH;
2085 map_tbl(l_counter).SEGMENT23_LOW := map.SEGMENT23_LOW;
2086 map_tbl(l_counter).SEGMENT23_HIGH := map.SEGMENT23_HIGH;
2087 map_tbl(l_counter).SEGMENT24_LOW := map.SEGMENT24_LOW;
2088 map_tbl(l_counter).SEGMENT24_HIGH := map.SEGMENT24_HIGH;
2089 map_tbl(l_counter).SEGMENT25_LOW := map.SEGMENT25_LOW;
2090 map_tbl(l_counter).SEGMENT25_HIGH := map.SEGMENT25_HIGH;
2091 map_tbl(l_counter).SEGMENT26_LOW := map.SEGMENT26_LOW;
2092 map_tbl(l_counter).SEGMENT26_HIGH := map.SEGMENT26_HIGH;
2093 map_tbl(l_counter).SEGMENT27_LOW := map.SEGMENT27_LOW;
2094 map_tbl(l_counter).SEGMENT27_HIGH := map.SEGMENT27_HIGH;
2095 map_tbl(l_counter).SEGMENT28_LOW := map.SEGMENT28_LOW;
2096 map_tbl(l_counter).SEGMENT28_HIGH := map.SEGMENT28_HIGH;
2097 map_tbl(l_counter).SEGMENT29_LOW := map.SEGMENT29_LOW;
2098 map_tbl(l_counter).SEGMENT29_HIGH := map.SEGMENT29_HIGH;
2099 map_tbl(l_counter).SEGMENT30_LOW := map.SEGMENT30_LOW;
2100 map_tbl(l_counter).SEGMENT30_HIGH := map.SEGMENT30_HIGH;
2101
2102 END LOOP;
2103
2104 elsif (p_location_id IS NOT NULL and p_property_id IS NULL) then
2105
2106 /*OPEN mapping_loc;
2107 FETCH mapping_loc BULK COLLECT INTO map_tbl;
2108 CLOSE mapping_loc;*/
2109
2110 FOR map in mapping_loc LOOP
2111
2112 l_counter := l_counter + 1;
2113
2114 map_tbl(l_counter).LOC_ACC_MAP_ID := map.LOC_ACC_MAP_ID;
2115 map_tbl(l_counter).LOC_ACC_MAP_HDR_ID := map.LOC_ACC_MAP_HDR_ID;
2116 map_tbl(l_counter).PROPERTY_ID := map.PROPERTY_ID;
2117 map_tbl(l_counter).LOCATION_ID := map.LOCATION_ID;
2118 map_tbl(l_counter).ACCOUNT_LOW := map.ACCOUNT_LOW;
2119 map_tbl(l_counter).ACCOUNT_HIGH := map.ACCOUNT_HIGH;
2120 map_tbl(l_counter).EXPENSE_TYPE_CODE := map.EXPENSE_TYPE_CODE;
2121 map_tbl(l_counter).EFFECTIVE_FROM_DATE := map.EFFECTIVE_FROM_DATE;
2122 map_tbl(l_counter).EFFECTIVE_TO_DATE := map.EFFECTIVE_TO_DATE;
2123 map_tbl(l_counter).RECOVERABLE_FLAG := map.RECOVERABLE_FLAG;
2124 map_tbl(l_counter).SEGMENT1_LOW := map.SEGMENT1_LOW;
2125 map_tbl(l_counter).SEGMENT1_HIGH := map.SEGMENT1_HIGH;
2126 map_tbl(l_counter).SEGMENT2_LOW := map.SEGMENT2_LOW;
2127 map_tbl(l_counter).SEGMENT2_HIGH := map.SEGMENT2_HIGH;
2128 map_tbl(l_counter).SEGMENT3_LOW := map.SEGMENT3_LOW;
2129 map_tbl(l_counter).SEGMENT3_HIGH := map.SEGMENT3_HIGH;
2130 map_tbl(l_counter).SEGMENT4_LOW := map.SEGMENT4_LOW;
2131 map_tbl(l_counter).SEGMENT4_HIGH := map.SEGMENT4_HIGH;
2132 map_tbl(l_counter).SEGMENT5_LOW := map.SEGMENT5_LOW;
2133 map_tbl(l_counter).SEGMENT5_HIGH := map.SEGMENT5_HIGH;
2134 map_tbl(l_counter).SEGMENT6_LOW := map.SEGMENT6_LOW;
2135 map_tbl(l_counter).SEGMENT6_HIGH := map.SEGMENT6_HIGH;
2136 map_tbl(l_counter).SEGMENT7_LOW := map.SEGMENT7_LOW;
2137 map_tbl(l_counter).SEGMENT7_HIGH := map.SEGMENT7_HIGH;
2138 map_tbl(l_counter).SEGMENT8_LOW := map.SEGMENT8_LOW;
2139 map_tbl(l_counter).SEGMENT8_HIGH := map.SEGMENT8_HIGH;
2140 map_tbl(l_counter).SEGMENT9_LOW := map.SEGMENT9_LOW;
2141 map_tbl(l_counter).SEGMENT9_HIGH := map.SEGMENT9_HIGH;
2142 map_tbl(l_counter).SEGMENT10_LOW := map.SEGMENT10_LOW;
2143 map_tbl(l_counter).SEGMENT10_HIGH := map.SEGMENT10_HIGH;
2144 map_tbl(l_counter).SEGMENT11_LOW := map.SEGMENT11_LOW;
2145 map_tbl(l_counter).SEGMENT11_HIGH := map.SEGMENT11_HIGH;
2146 map_tbl(l_counter).SEGMENT12_LOW := map.SEGMENT12_LOW;
2147 map_tbl(l_counter).SEGMENT12_HIGH := map.SEGMENT12_HIGH;
2148 map_tbl(l_counter).SEGMENT13_LOW := map.SEGMENT13_LOW;
2149 map_tbl(l_counter).SEGMENT13_HIGH := map.SEGMENT13_HIGH;
2150 map_tbl(l_counter).SEGMENT14_LOW := map.SEGMENT14_LOW;
2151 map_tbl(l_counter).SEGMENT14_HIGH := map.SEGMENT14_HIGH;
2152 map_tbl(l_counter).SEGMENT15_LOW := map.SEGMENT15_LOW;
2153 map_tbl(l_counter).SEGMENT15_HIGH := map.SEGMENT15_HIGH;
2154 map_tbl(l_counter).SEGMENT16_LOW := map.SEGMENT16_LOW;
2155 map_tbl(l_counter).SEGMENT16_HIGH := map.SEGMENT16_HIGH;
2156 map_tbl(l_counter).SEGMENT17_LOW := map.SEGMENT17_LOW;
2157 map_tbl(l_counter).SEGMENT17_HIGH := map.SEGMENT17_HIGH;
2158 map_tbl(l_counter).SEGMENT18_LOW := map.SEGMENT18_LOW;
2159 map_tbl(l_counter).SEGMENT18_HIGH := map.SEGMENT18_HIGH;
2160 map_tbl(l_counter).SEGMENT19_LOW := map.SEGMENT19_LOW;
2161 map_tbl(l_counter).SEGMENT19_HIGH := map.SEGMENT19_HIGH;
2162 map_tbl(l_counter).SEGMENT20_LOW := map.SEGMENT20_LOW;
2163 map_tbl(l_counter).SEGMENT20_HIGH := map.SEGMENT20_HIGH;
2164 map_tbl(l_counter).SEGMENT21_LOW := map.SEGMENT21_LOW;
2165 map_tbl(l_counter).SEGMENT21_HIGH := map.SEGMENT21_HIGH;
2166 map_tbl(l_counter).SEGMENT22_LOW := map.SEGMENT22_LOW;
2167 map_tbl(l_counter).SEGMENT22_HIGH := map.SEGMENT22_HIGH;
2168 map_tbl(l_counter).SEGMENT23_LOW := map.SEGMENT23_LOW;
2169 map_tbl(l_counter).SEGMENT23_HIGH := map.SEGMENT23_HIGH;
2170 map_tbl(l_counter).SEGMENT24_LOW := map.SEGMENT24_LOW;
2171 map_tbl(l_counter).SEGMENT24_HIGH := map.SEGMENT24_HIGH;
2172 map_tbl(l_counter).SEGMENT25_LOW := map.SEGMENT25_LOW;
2173 map_tbl(l_counter).SEGMENT25_HIGH := map.SEGMENT25_HIGH;
2174 map_tbl(l_counter).SEGMENT26_LOW := map.SEGMENT26_LOW;
2175 map_tbl(l_counter).SEGMENT26_HIGH := map.SEGMENT26_HIGH;
2176 map_tbl(l_counter).SEGMENT27_LOW := map.SEGMENT27_LOW;
2177 map_tbl(l_counter).SEGMENT27_HIGH := map.SEGMENT27_HIGH;
2178 map_tbl(l_counter).SEGMENT28_LOW := map.SEGMENT28_LOW;
2179 map_tbl(l_counter).SEGMENT28_HIGH := map.SEGMENT28_HIGH;
2180 map_tbl(l_counter).SEGMENT29_LOW := map.SEGMENT29_LOW;
2181 map_tbl(l_counter).SEGMENT29_HIGH := map.SEGMENT29_HIGH;
2182 map_tbl(l_counter).SEGMENT30_LOW := map.SEGMENT30_LOW;
2183 map_tbl(l_counter).SEGMENT30_HIGH := map.SEGMENT30_HIGH;
2184
2185 END LOOP;
2186
2187 elsif (p_location_id IS NULL and p_property_id IS NOT NULL) then
2188
2189 /*OPEN mapping_prop;
2190 FETCH mapping_prop BULK COLLECT INTO map_tbl;
2191 CLOSE mapping_prop;*/
2192
2193 FOR map in mapping_prop LOOP
2194
2195 l_counter := l_counter + 1;
2196
2197 map_tbl(l_counter).LOC_ACC_MAP_ID := map.LOC_ACC_MAP_ID;
2198 map_tbl(l_counter).LOC_ACC_MAP_HDR_ID := map.LOC_ACC_MAP_HDR_ID;
2199 map_tbl(l_counter).PROPERTY_ID := map.PROPERTY_ID;
2200 map_tbl(l_counter).LOCATION_ID := map.LOCATION_ID;
2201 map_tbl(l_counter).ACCOUNT_LOW := map.ACCOUNT_LOW;
2202 map_tbl(l_counter).ACCOUNT_HIGH := map.ACCOUNT_HIGH;
2203 map_tbl(l_counter).EXPENSE_TYPE_CODE := map.EXPENSE_TYPE_CODE;
2204 map_tbl(l_counter).EFFECTIVE_FROM_DATE := map.EFFECTIVE_FROM_DATE;
2205 map_tbl(l_counter).EFFECTIVE_TO_DATE := map.EFFECTIVE_TO_DATE;
2206 map_tbl(l_counter).RECOVERABLE_FLAG := map.RECOVERABLE_FLAG;
2207 map_tbl(l_counter).SEGMENT1_LOW := map.SEGMENT1_LOW;
2208 map_tbl(l_counter).SEGMENT1_HIGH := map.SEGMENT1_HIGH;
2209 map_tbl(l_counter).SEGMENT2_LOW := map.SEGMENT2_LOW;
2210 map_tbl(l_counter).SEGMENT2_HIGH := map.SEGMENT2_HIGH;
2211 map_tbl(l_counter).SEGMENT3_LOW := map.SEGMENT3_LOW;
2212 map_tbl(l_counter).SEGMENT3_HIGH := map.SEGMENT3_HIGH;
2213 map_tbl(l_counter).SEGMENT4_LOW := map.SEGMENT4_LOW;
2214 map_tbl(l_counter).SEGMENT4_HIGH := map.SEGMENT4_HIGH;
2215 map_tbl(l_counter).SEGMENT5_LOW := map.SEGMENT5_LOW;
2216 map_tbl(l_counter).SEGMENT5_HIGH := map.SEGMENT5_HIGH;
2217 map_tbl(l_counter).SEGMENT6_LOW := map.SEGMENT6_LOW;
2218 map_tbl(l_counter).SEGMENT6_HIGH := map.SEGMENT6_HIGH;
2219 map_tbl(l_counter).SEGMENT7_LOW := map.SEGMENT7_LOW;
2220 map_tbl(l_counter).SEGMENT7_HIGH := map.SEGMENT7_HIGH;
2221 map_tbl(l_counter).SEGMENT8_LOW := map.SEGMENT8_LOW;
2222 map_tbl(l_counter).SEGMENT8_HIGH := map.SEGMENT8_HIGH;
2223 map_tbl(l_counter).SEGMENT9_LOW := map.SEGMENT9_LOW;
2224 map_tbl(l_counter).SEGMENT9_HIGH := map.SEGMENT9_HIGH;
2225 map_tbl(l_counter).SEGMENT10_LOW := map.SEGMENT10_LOW;
2226 map_tbl(l_counter).SEGMENT10_HIGH := map.SEGMENT10_HIGH;
2227 map_tbl(l_counter).SEGMENT11_LOW := map.SEGMENT11_LOW;
2228 map_tbl(l_counter).SEGMENT11_HIGH := map.SEGMENT11_HIGH;
2229 map_tbl(l_counter).SEGMENT12_LOW := map.SEGMENT12_LOW;
2230 map_tbl(l_counter).SEGMENT12_HIGH := map.SEGMENT12_HIGH;
2231 map_tbl(l_counter).SEGMENT13_LOW := map.SEGMENT13_LOW;
2232 map_tbl(l_counter).SEGMENT13_HIGH := map.SEGMENT13_HIGH;
2233 map_tbl(l_counter).SEGMENT14_LOW := map.SEGMENT14_LOW;
2234 map_tbl(l_counter).SEGMENT14_HIGH := map.SEGMENT14_HIGH;
2235 map_tbl(l_counter).SEGMENT15_LOW := map.SEGMENT15_LOW;
2236 map_tbl(l_counter).SEGMENT15_HIGH := map.SEGMENT15_HIGH;
2237 map_tbl(l_counter).SEGMENT16_LOW := map.SEGMENT16_LOW;
2238 map_tbl(l_counter).SEGMENT16_HIGH := map.SEGMENT16_HIGH;
2239 map_tbl(l_counter).SEGMENT17_LOW := map.SEGMENT17_LOW;
2240 map_tbl(l_counter).SEGMENT17_HIGH := map.SEGMENT17_HIGH;
2241 map_tbl(l_counter).SEGMENT18_LOW := map.SEGMENT18_LOW;
2242 map_tbl(l_counter).SEGMENT18_HIGH := map.SEGMENT18_HIGH;
2243 map_tbl(l_counter).SEGMENT19_LOW := map.SEGMENT19_LOW;
2244 map_tbl(l_counter).SEGMENT19_HIGH := map.SEGMENT19_HIGH;
2245 map_tbl(l_counter).SEGMENT20_LOW := map.SEGMENT20_LOW;
2246 map_tbl(l_counter).SEGMENT20_HIGH := map.SEGMENT20_HIGH;
2247 map_tbl(l_counter).SEGMENT21_LOW := map.SEGMENT21_LOW;
2248 map_tbl(l_counter).SEGMENT21_HIGH := map.SEGMENT21_HIGH;
2249 map_tbl(l_counter).SEGMENT22_LOW := map.SEGMENT22_LOW;
2250 map_tbl(l_counter).SEGMENT22_HIGH := map.SEGMENT22_HIGH;
2251 map_tbl(l_counter).SEGMENT23_LOW := map.SEGMENT23_LOW;
2252 map_tbl(l_counter).SEGMENT23_HIGH := map.SEGMENT23_HIGH;
2253 map_tbl(l_counter).SEGMENT24_LOW := map.SEGMENT24_LOW;
2254 map_tbl(l_counter).SEGMENT24_HIGH := map.SEGMENT24_HIGH;
2255 map_tbl(l_counter).SEGMENT25_LOW := map.SEGMENT25_LOW;
2256 map_tbl(l_counter).SEGMENT25_HIGH := map.SEGMENT25_HIGH;
2257 map_tbl(l_counter).SEGMENT26_LOW := map.SEGMENT26_LOW;
2258 map_tbl(l_counter).SEGMENT26_HIGH := map.SEGMENT26_HIGH;
2259 map_tbl(l_counter).SEGMENT27_LOW := map.SEGMENT27_LOW;
2260 map_tbl(l_counter).SEGMENT27_HIGH := map.SEGMENT27_HIGH;
2261 map_tbl(l_counter).SEGMENT28_LOW := map.SEGMENT28_LOW;
2262 map_tbl(l_counter).SEGMENT28_HIGH := map.SEGMENT28_HIGH;
2263 map_tbl(l_counter).SEGMENT29_LOW := map.SEGMENT29_LOW;
2264 map_tbl(l_counter).SEGMENT29_HIGH := map.SEGMENT29_HIGH;
2265 map_tbl(l_counter).SEGMENT30_LOW := map.SEGMENT30_LOW;
2266 map_tbl(l_counter).SEGMENT30_HIGH := map.SEGMENT30_HIGH;
2267
2268 END LOOP;
2269
2270 elsif (p_location_id IS NOT NULL and p_property_id IS NOT NULL) then
2271
2272 /*OPEN mapping_loc_prop;
2273 FETCH mapping_loc_prop BULK COLLECT INTO map_tbl;
2274 CLOSE mapping_loc_prop;*/
2275
2276 FOR map in mapping_loc_prop LOOP
2277
2278 l_counter := l_counter + 1;
2279
2280 map_tbl(l_counter).LOC_ACC_MAP_ID := map.LOC_ACC_MAP_ID;
2281 map_tbl(l_counter).LOC_ACC_MAP_HDR_ID := map.LOC_ACC_MAP_HDR_ID;
2282 map_tbl(l_counter).PROPERTY_ID := map.PROPERTY_ID;
2283 map_tbl(l_counter).LOCATION_ID := map.LOCATION_ID;
2284 map_tbl(l_counter).ACCOUNT_LOW := map.ACCOUNT_LOW;
2285 map_tbl(l_counter).ACCOUNT_HIGH := map.ACCOUNT_HIGH;
2286 map_tbl(l_counter).EXPENSE_TYPE_CODE := map.EXPENSE_TYPE_CODE;
2287 map_tbl(l_counter).EFFECTIVE_FROM_DATE := map.EFFECTIVE_FROM_DATE;
2288 map_tbl(l_counter).EFFECTIVE_TO_DATE := map.EFFECTIVE_TO_DATE;
2289 map_tbl(l_counter).RECOVERABLE_FLAG := map.RECOVERABLE_FLAG;
2290 map_tbl(l_counter).SEGMENT1_LOW := map.SEGMENT1_LOW;
2291 map_tbl(l_counter).SEGMENT1_HIGH := map.SEGMENT1_HIGH;
2292 map_tbl(l_counter).SEGMENT2_LOW := map.SEGMENT2_LOW;
2293 map_tbl(l_counter).SEGMENT2_HIGH := map.SEGMENT2_HIGH;
2294 map_tbl(l_counter).SEGMENT3_LOW := map.SEGMENT3_LOW;
2295 map_tbl(l_counter).SEGMENT3_HIGH := map.SEGMENT3_HIGH;
2296 map_tbl(l_counter).SEGMENT4_LOW := map.SEGMENT4_LOW;
2297 map_tbl(l_counter).SEGMENT4_HIGH := map.SEGMENT4_HIGH;
2298 map_tbl(l_counter).SEGMENT5_LOW := map.SEGMENT5_LOW;
2299 map_tbl(l_counter).SEGMENT5_HIGH := map.SEGMENT5_HIGH;
2300 map_tbl(l_counter).SEGMENT6_LOW := map.SEGMENT6_LOW;
2301 map_tbl(l_counter).SEGMENT6_HIGH := map.SEGMENT6_HIGH;
2302 map_tbl(l_counter).SEGMENT7_LOW := map.SEGMENT7_LOW;
2303 map_tbl(l_counter).SEGMENT7_HIGH := map.SEGMENT7_HIGH;
2304 map_tbl(l_counter).SEGMENT8_LOW := map.SEGMENT8_LOW;
2305 map_tbl(l_counter).SEGMENT8_HIGH := map.SEGMENT8_HIGH;
2306 map_tbl(l_counter).SEGMENT9_LOW := map.SEGMENT9_LOW;
2307 map_tbl(l_counter).SEGMENT9_HIGH := map.SEGMENT9_HIGH;
2308 map_tbl(l_counter).SEGMENT10_LOW := map.SEGMENT10_LOW;
2309 map_tbl(l_counter).SEGMENT10_HIGH := map.SEGMENT10_HIGH;
2310 map_tbl(l_counter).SEGMENT11_LOW := map.SEGMENT11_LOW;
2311 map_tbl(l_counter).SEGMENT11_HIGH := map.SEGMENT11_HIGH;
2312 map_tbl(l_counter).SEGMENT12_LOW := map.SEGMENT12_LOW;
2313 map_tbl(l_counter).SEGMENT12_HIGH := map.SEGMENT12_HIGH;
2314 map_tbl(l_counter).SEGMENT13_LOW := map.SEGMENT13_LOW;
2315 map_tbl(l_counter).SEGMENT13_HIGH := map.SEGMENT13_HIGH;
2316 map_tbl(l_counter).SEGMENT14_LOW := map.SEGMENT14_LOW;
2317 map_tbl(l_counter).SEGMENT14_HIGH := map.SEGMENT14_HIGH;
2318 map_tbl(l_counter).SEGMENT15_LOW := map.SEGMENT15_LOW;
2319 map_tbl(l_counter).SEGMENT15_HIGH := map.SEGMENT15_HIGH;
2320 map_tbl(l_counter).SEGMENT16_LOW := map.SEGMENT16_LOW;
2321 map_tbl(l_counter).SEGMENT16_HIGH := map.SEGMENT16_HIGH;
2322 map_tbl(l_counter).SEGMENT17_LOW := map.SEGMENT17_LOW;
2323 map_tbl(l_counter).SEGMENT17_HIGH := map.SEGMENT17_HIGH;
2324 map_tbl(l_counter).SEGMENT18_LOW := map.SEGMENT18_LOW;
2325 map_tbl(l_counter).SEGMENT18_HIGH := map.SEGMENT18_HIGH;
2326 map_tbl(l_counter).SEGMENT19_LOW := map.SEGMENT19_LOW;
2327 map_tbl(l_counter).SEGMENT19_HIGH := map.SEGMENT19_HIGH;
2328 map_tbl(l_counter).SEGMENT20_LOW := map.SEGMENT20_LOW;
2329 map_tbl(l_counter).SEGMENT20_HIGH := map.SEGMENT20_HIGH;
2330 map_tbl(l_counter).SEGMENT21_LOW := map.SEGMENT21_LOW;
2331 map_tbl(l_counter).SEGMENT21_HIGH := map.SEGMENT21_HIGH;
2332 map_tbl(l_counter).SEGMENT22_LOW := map.SEGMENT22_LOW;
2333 map_tbl(l_counter).SEGMENT22_HIGH := map.SEGMENT22_HIGH;
2334 map_tbl(l_counter).SEGMENT23_LOW := map.SEGMENT23_LOW;
2335 map_tbl(l_counter).SEGMENT23_HIGH := map.SEGMENT23_HIGH;
2336 map_tbl(l_counter).SEGMENT24_LOW := map.SEGMENT24_LOW;
2337 map_tbl(l_counter).SEGMENT24_HIGH := map.SEGMENT24_HIGH;
2338 map_tbl(l_counter).SEGMENT25_LOW := map.SEGMENT25_LOW;
2339 map_tbl(l_counter).SEGMENT25_HIGH := map.SEGMENT25_HIGH;
2340 map_tbl(l_counter).SEGMENT26_LOW := map.SEGMENT26_LOW;
2341 map_tbl(l_counter).SEGMENT26_HIGH := map.SEGMENT26_HIGH;
2342 map_tbl(l_counter).SEGMENT27_LOW := map.SEGMENT27_LOW;
2343 map_tbl(l_counter).SEGMENT27_HIGH := map.SEGMENT27_HIGH;
2344 map_tbl(l_counter).SEGMENT28_LOW := map.SEGMENT28_LOW;
2345 map_tbl(l_counter).SEGMENT28_HIGH := map.SEGMENT28_HIGH;
2346 map_tbl(l_counter).SEGMENT29_LOW := map.SEGMENT29_LOW;
2347 map_tbl(l_counter).SEGMENT29_HIGH := map.SEGMENT29_HIGH;
2348 map_tbl(l_counter).SEGMENT30_LOW := map.SEGMENT30_LOW;
2349 map_tbl(l_counter).SEGMENT30_HIGH := map.SEGMENT30_HIGH;
2350
2351 END LOOP;
2352
2353 end if;
2354
2355 l_map_count := map_tbl.COUNT;
2356 Put_Log('Number of Mapping records fetched: '||to_char(l_map_count));
2357
2358 /* --- verify the dates for all the maps --- */
2359 verify_dates_for_map(p_map_t => map_tbl,
2360 p_periods_t => period_from_to_date_tbl);
2361
2362 /* --- get all CCIDs --- */
2363 code_combinations_tbl.DELETE;
2364 get_ccids(p_sob_id => to_number(p_set_of_books_id),
2365 p_map_t => map_tbl,
2366 p_ccid_t => code_combinations_tbl);
2367
2368 /* --- get all the Balances from GL Balances --- */
2369
2370 get_amounts(p_sob_id => to_number(p_set_of_books_id),
2371 p_actual_flag => p_balance_type_code,
2372 p_budget_name => p_budget_name,
2373 p_map_t => map_tbl,
2374 p_ccid_t => code_combinations_tbl);
2375
2376 /* --- insert data into the ITF table --- */
2377 populate_rec_exp_itf(p_sob_id => to_number(p_set_of_books_id),
2378 p_from_date => l_period_start_date,
2379 p_to_date => l_period_end_date,
2380 p_map_t => map_tbl,
2381 p_ccid_t => code_combinations_tbl);
2382
2383 /* --- call Recovery Module Expense Lines Extract if ---
2384 --- populate recoveries = YES --- */
2385
2386 IF p_populate_rec = 'Y' THEN
2387
2388 l_info := 'calling Recovery Module Expense Lines Extract';
2389
2390 OPEN curr_code(to_number(p_set_of_books_id));
2391 FETCH curr_code INTO l_currency_code;
2392 CLOSE curr_code;
2393
2394 IF p_property_id IS NOT NULL THEN
2395 OPEN prop_code(TO_NUMBER(p_property_id));
2396 FETCH prop_code INTO l_property_code;
2397 CLOSE prop_code;
2398 END IF;
2399
2400 IF p_location_id IS NOT NULL THEN
2401 OPEN loc_code(TO_NUMBER(p_location_id));
2402 FETCH loc_code INTO l_location_code;
2403 CLOSE loc_code;
2404 END IF;
2405
2406 Put_Log('Calling pn_recovery_extract_pkg.extract_line_expense_area with parameters:');
2407 Put_Log('Location Code: '||l_location_code);
2408 Put_Log('Property Code: '||l_property_code);
2409 Put_Log('Currency Code: '||l_currency_code);
2410 Put_Log('As Of Date: '||p_as_of_date);
2411 Put_Log('From Date: '||p_period_start_date);
2412 Put_Log('To Date: '||p_period_end_date);
2413 Put_Log('Populate Expense Class Details: '||p_populate_expcl_dtl);
2414 Put_Log('Populate Area Class Details: '||p_populate_arcl_dtl);
2415 Put_Log('Keep Overrides: '||p_override);
2416 Put_Log('Extract Code: '||p_rec_exp_num);
2417
2418
2419 pn_recovery_extract_pkg.extract_line_expense_area
2420 (
2421 errbuf => errbuf,
2422 retcode => retcode,
2423 p_location_code => l_location_code,
2424 p_property_code => l_property_code,
2425 p_as_of_date => p_as_of_date,
2426 p_from_date => p_period_start_date,
2427 p_to_date => p_period_end_date,
2428 p_currency_code => l_currency_code,
2429 p_pop_exp_class_dtl => p_populate_expcl_dtl,
2430 p_pop_area_class_dtl => p_populate_arcl_dtl,
2431 p_keep_override => p_override,
2432 p_extract_code => p_rec_exp_num,
2433 p_called_from => 'SRS'
2434 );
2435
2436 END IF;
2437
2438 Put_Log('PN_REC_EXP_EXTR_FROM_GL_PKG.extract_expense_from_gl (-)');
2439
2440 EXCEPTION
2441 WHEN BAD_INPUT_EXCEPTION THEN
2442 Put_Line(fnd_message.get);
2443 raise;
2444 WHEN OTHERS THEN
2445 Put_Log('Error while '||l_info);
2446 Raise;
2447
2448 END extract_expense_from_gl;
2449
2450 /*===========================================================================+
2451 | PROCEDURE
2452 | Put_Log
2453 |
2454 | DESCRIPTION
2455 | Writes the String passed as argument to Concurrent Log
2456 |
2457 | ARGUMENTS: p_String
2458 |
2459 | NOTES:
2460 | Called at all Debug points spread across this file
2461 |
2462 | MODIFICATION HISTORY
2463 | Created Naga Vijayapuram 1999
2464 |
2465 | 10-JUL-03 Kiran o copied from PNVLOSPB
2466 +===========================================================================*/
2467
2468 Procedure Put_Log(p_String VarChar2) IS
2469
2470 BEGIN
2471
2472 Fnd_File.Put_Line(Fnd_File.Log, p_String);
2473
2474 EXCEPTION
2475
2476 When Others Then Raise;
2477
2478 END Put_Log;
2479
2480
2481 /*===========================================================================+
2482 | PROCEDURE
2483 | Put_Line
2484 |
2485 | DESCRIPTION
2486 | Writes the String passed as argument to Concurrent Log/Output
2487 |
2488 | ARGUMENTS: p_String
2489 |
2490 | NOTES:
2491 | Called at all Debug points spread across this file
2492 |
2493 | MODIFICATION HISTORY
2494 | Created Naga Vijayapuram 1999
2495 |
2496 | 10-JUL-03 Kiran o copied from PNVLOSPB
2497 |
2498 +===========================================================================*/
2499
2500 Procedure Put_Line(p_String VarChar2) IS
2501
2502 BEGIN
2503
2504 Fnd_File.Put_Line(Fnd_File.Log, p_String);
2505 Fnd_File.Put_Line(Fnd_File.Output, p_String);
2506
2507 EXCEPTION
2508
2509 When Others Then Raise;
2510
2511 END Put_Line;
2512
2513 END PN_REC_EXP_EXTR_FROM_GL_PKG;