[Home] [Help]
PACKAGE BODY: APPS.FV_REIMB_ACTIVITY_PROCESS
Source
1 PACKAGE BODY FV_REIMB_ACTIVITY_PROCESS AS
2 -- $Header: FVREACRB.pls 120.16 2010/07/23 11:01:23 yanasing noship $
3
4 g_period_name VARCHAR2(15);
5 g_period_num NUMBER;
6 g_period_year NUMBER;
7 g_ledger_id NUMBER;
8 g_coa_id NUMBER;
9 C_STATE_LEVEL CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 g_log_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11 g_module_name VARCHAR2(50) := 'fv.plsql.fv_reimb_activity_process.';
12 g_errbuf VARCHAR2(500);
13 g_retcode NUMBER := 0;
14 g_currency VARCHAR2(3);
15 g_gl_balancing_segment VARCHAR2(15);
16 g_gl_nat_acc_segment VARCHAR2(15);
17 g_reimb_agreement_segment VARCHAR2(50);
18 g_ussgl_flex_value_set_id NUMBER;
19 g_apps_id Fnd_Id_Flex_Structures.application_id%TYPE;
20 g_id_flex_code Fnd_Id_Flex_Structures.id_flex_code%TYPE;
21 g_delimiter Fnd_Id_Flex_Structures.concatenated_segment_delimiter%TYPE;
22 g_reimb_flex_value_id fnd_flex_values.flex_value_id%TYPE;
23
24 -- Variables used for dynamic sql
25
26 g_where VARCHAR2(2000);
27 g_select VARCHAR2(2000);
28 g_flex_low VARCHAR2(2000);
29 g_flex_high VARCHAR2(2000);
30 g_agree_sql VARCHAR2(1000);
31
32 -- PL/SQL Tables to hold the low and high values,
33 -- used in Get_Application_Col_Names proc
34 gt_seg_low Fnd_Flex_Ext.segmentarray;
35 gt_seg_high Fnd_Flex_Ext.segmentarray;
36 g_nsegs_low NUMBER;
37 g_nsegs_high NUMBER;
38
39 -- PL/SQL Table to hold the flexfield column names
40 TYPE seg_name IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
41 gt_seg_name seg_name;
42
43 TYPE seg_codes IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
44 gt_seg_codes seg_codes;
45
46 i NUMBER;
47
48
49 PROCEDURE log(
50 p_level IN NUMBER,
51 p_procedure_name IN VARCHAR2,
52 p_debug_info IN VARCHAR2);
53
54 --PROCEDURE get_period_details;
55 PROCEDURE get_segment_details;
56 PROCEDURE Get_Application_Col_Names;
57 PROCEDURE get_segment_values(seg_cnt IN NUMBER);
58 PROCEDURE populate_amount(p_column_id IN NUMBER,p_agree_num IN VARCHAR2);
59 PROCEDURE get_agreement_range;
60 PROCEDURE create_main_query(p_rec_sla_detail IN VARCHAR2, p_select_mod IN VARCHAR2,
61 p_column_id IN NUMBER, p_main_sql OUT NOCOPY VARCHAR2);
62
63 PROCEDURE main
64 ( p_errbuf OUT NOCOPY VARCHAR2,
65 p_retcode OUT NOCOPY NUMBER,
66 p_ledger_id IN NUMBER,
67 p_coa_id in NUMBER,
68 -- p_period_name IN VARCHAR2, Commented for bug 9923269
69 p_flex_low IN VARCHAR2,
70 p_flex_high IN VARCHAR2,
71 p_report_id IN VARCHAR2,
72 p_attribute_set IN VARCHAR2,
73 p_output_format IN VARCHAR2
74 ) IS
75 l_rec_count NUMBER := 0;
76 l_req_id NUMBER;
77 l_call_status BOOLEAN;
78 l_rphase VARCHAR2(30);
79 l_rstatus VARCHAR2(30);
80 l_dphase VARCHAR2(30);
81 l_dstatus VARCHAR2(30);
82 l_message VARCHAR2(240);
83 l_module VARCHAR2(500) := g_module_name||'.Main';
84 l_error_message VARCHAR2(600);
85 l_error_code BOOLEAN;
86 l_seed_count NUMBER;
87 l_Acct_count NUMBER;
88 l_trx_number ra_customer_trx_all.trx_number%TYPE;
89 l_purchase_order ra_customer_trx_all.purchase_order%TYPE;
90 l_start_date_commitment ra_customer_trx_all.start_date_commitment%TYPE;
91 l_end_date_commitment ra_customer_trx_all.end_date_commitment%TYPE;
92 l_extended_amount ra_customer_trx_lines_all.extended_amount%TYPE;
93 l_agree_num VARCHAR2(25);
94 TYPE ref_type IS REF CURSOR ;
95 agree_cur ref_type;
96 l_ctr NUMBER;
97
98 BEGIN
99
100 FOR i IN 1..30 LOOP
101 gt_seg_codes(i):=null;
102 end loop;
103 log(C_STATE_LEVEL, l_module, 'Initialized the seg_codes');
104
105
106 fv_utility.log_mesg('Parameters: ');
107 fv_utility.log_mesg('p_ledger_id: '||p_ledger_id);
108 fv_utility.log_mesg('p_flex_low: '||p_flex_low);
109 fv_utility.log_mesg('p_flex_high: '||p_flex_high);
110
111 log(C_STATE_LEVEL, l_module, 'p_report_id: '|| p_report_id);
112 log(C_STATE_LEVEL, l_module, 'p_attribute_set: '|| p_attribute_set);
113 log(C_STATE_LEVEL, l_module, 'p_output_format: '|| p_output_format);
114
115 g_ledger_id := p_ledger_id;
116 g_flex_low := p_flex_low;
117 g_flex_high := p_flex_high;
118 g_currency := 'USD';
119 -- g_period_name := p_period_name;
120 SELECT CHART_OF_ACCOUNTS_ID
121 INTO g_coa_id
122 FROM gl_ledgers
123 where ledger_id = g_ledger_id;
124 fv_utility.log_mesg('g_coa_id: '||g_coa_id);
125
126 /* SELECT period_year, period_num
127 INTO g_period_year, g_period_num
128 FROM gl_period_statuses
129 WHERE application_id = 101
130 AND set_of_books_id = g_ledger_id
131 AND period_name = g_period_name;
132
133 fv_utility.log_mesg('g_period_year: '||g_period_year);
134 fv_utility.log_mesg('g_period_num: '||g_period_num);
135 */
136
137 -- Check whether set up done
138 -- Check if Seed process was run
139
140 SELECT COUNT(1) INTO l_seed_count
141 FROM Fv_reimb_definitions_lines
142 WHERE set_of_books_id = g_ledger_id;
143
144 IF l_seed_count=0 THEN
145 g_retcode := -1 ;
146 g_errbuf := ' Please run the Populate Reimbursable Activity Report Definitions Process .' ;
147 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,g_errbuf) ;
148 RETURN ;
149 END IF;
150 -- Check whether Accounts are Defined in the form ,
151 -- Reimbursable Activity Report Definitions .
152
153 SELECT COUNT(1) INTO l_Acct_count
154 FROM Fv_reimb_definitions_lines fvrd ,
155 Fv_reimb_def_Acct_assign fvda
156 WHERE fvrd.column_id=fvda.column_id
157 AND fvrd.set_of_books_id = g_ledger_id;
158
159 IF l_Acct_count=0 THEN
160 g_retcode := -1 ;
161 g_errbuf := ' Accounts not defined . Please define Accounts in the Reimbursable Activity Report
162 Definitions Form. ' ;
163 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module,g_errbuf) ;
164 RETURN ;
165 END IF;
166
167 ----End of set up check
168
169 --Call get_segment_details to populate all the segment names, Natural account Segment
170 --Balancing Segment etc
171
172 get_segment_details;
173
174 IF g_retcode = 0 THEN
175 log(C_STATE_LEVEL, l_module, 'Calling Get_Application_Col_Names....');
176 END IF;
177
178 Get_Application_Col_Names;
179
180 IF g_retcode = 0 THEN
181 log(C_STATE_LEVEL, l_module, 'Checking for the agreement numbers range, calling get_agreement_range .....');
182 END IF;
183
184 get_agreement_range;
185
186 -- Delete all data from previous run. And populate it afresh.
187
188 IF g_retcode = 0 THEN
189 log(C_STATE_LEVEL, l_module, 'Deleting any previous records from FV_REIMB_ACTIVITY_TEMP .....');
190 END IF;
191
192 DELETE FROM FV_REIMB_ACTIVITY_TEMP;
193
194
195 --Check for Reimb Agreement segment values in the flexfield low and high range
196 --If it is not null then assign v_low_reimb_agree and v_high_reimb_agree
197 --otherwise for all the values in the the value set and exists in in ra_trx_all
198
199 ---------------------------Loop for agreement
200
201 OPEN agree_cur FOR g_agree_sql USING g_reimb_flex_value_id,g_ledger_id,g_currency;
202
203 LOOP
204
205 FETCH agree_cur INTO l_agree_num;
206
207 fv_utility.log_mesg('Agreement num: '||l_agree_num);
208
209 EXIT WHEN agree_cur%NOTFOUND;
210 -- call polulate_amount for each column_id
211
212 populate_amount(1,l_agree_num);
213 populate_amount(2,l_agree_num);
214 populate_amount(3,l_agree_num);
215 populate_amount(4,l_agree_num);
216 populate_amount(5,l_agree_num);
217 populate_amount(6,l_agree_num);
218 populate_amount(7,l_agree_num);
219 populate_amount(8,l_agree_num);
220 populate_amount(9,l_agree_num);
221 populate_amount(10,l_agree_num);
222 populate_amount(11,l_agree_num);
223
224 --Bug8741007
225 END LOOP;
226 CLOSE agree_cur;
227
228 INSERT INTO FV_REIMB_ACTIVITY_TEMP(Reimb_Agreement_Num,
229 Contract_Number,
230 Billing_Limit,
231 start_date,
232 end_date,
233 amount_obligation,
234 amount_expended,
235 amount_unfilled_order,
236 amount_advance_collected,
237 amount_advance_applied,
238 amount_advance_balance,
239 amount_earned,
240 amount_billed,
241 amount_receivable_collected,
242 amount_receivable_balance,
243 amount_agreement,
244 REQUEST_ID,
245 LAST_UPDATED_BY,
246 LAST_UPDATE_LOGIN,
247 CREATED_BY,
248 CREATION_DATE,
249 LAST_UPDATE_DATE,
250 segment1,
251 segment2,
252 segment3,
253 segment4,
254 segment5,
255 segment6,
256 segment7,
257 segment8,
258 segment9,
259 segment10,
260 segment11,
261 segment12,
262 segment13,
263 segment14,
264 segment15,
265 segment16,
266 segment17,
267 segment18,
268 segment19,
269 segment20,
270 segment21,
271 segment22,
272 segment23,
273 segment24,
274 segment25,
275 segment26,
276 segment27,
277 segment28,
278 segment29,
279 segment30,
280 reported_flag
281 )
282 (SELECT Reimb_Agreement_Num,
283 Contract_Number,
284 Billing_Limit,
285 start_date,
286 end_date,
287 sum(amount_obligation),
288 sum(amount_expended),
289 sum(amount_unfilled_order),
290 sum(amount_advance_collected),
291 sum(amount_advance_applied),
292 sum(amount_advance_balance),
293 sum(amount_earned),
294 sum(amount_billed),
295 sum(amount_receivable_collected),
296 sum(amount_receivable_balance),
297 sum(amount_agreement),
298 REQUEST_ID,
299 LAST_UPDATED_BY,
300 LAST_UPDATE_LOGIN,
301 CREATED_BY,
302 SYSDATE,
303 SYSDATE,
304 segment1,
305 segment2,
306 segment3,
307 segment4,
308 segment5,
309 segment6,
310 segment7,
311 segment8,
312 segment9,
313 segment10,
314 segment11,
315 segment12,
316 segment13,
317 segment14,
318 segment15,
319 segment16,
320 segment17,
321 segment18,
322 segment19,
323 segment20,
324 segment21,
325 segment22,
326 segment23,
327 segment24,
328 segment25,
329 segment26,
330 segment27,
331 segment28,
332 segment29,
333 segment30,
334 'Y'
335 FROM FV_REIMB_ACTIVITY_TEMP
336 WHERE reported_flag = 'N'
337 GROUP BY
338 Reimb_Agreement_Num,
339 Contract_Number,
340 Billing_Limit,
341 start_date,
342 end_date,
343 REQUEST_ID,
344 LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN,
346 CREATED_BY,
347 segment1,
348 segment2,
349 segment3,
350 segment4,
351 segment5,
352 segment6,
353 segment7,
354 segment8,
355 segment9,
356 segment10,
357 segment11,
358 segment12,
359 segment13,
360 segment14,
361 segment15,
362 segment16,
363 segment17,
364 segment18,
365 segment19,
366 segment20,
367 segment21,
368 segment22,
369 segment23,
370 segment24,
371 segment25,
372 segment26,
373 segment27,
374 segment28,
375 segment29,
376 segment30
377 having
378 sum(amount_obligation) <> 0 or
379 sum(amount_expended)<> 0 or
380 sum(amount_unfilled_order)<> 0 or
381 sum(amount_advance_collected)<> 0 or
382 sum(amount_advance_applied)<> 0 or
383 sum(amount_advance_balance)<> 0 or
384 sum(amount_earned)<> 0 or
385 sum(amount_billed)<> 0 or
386 sum(amount_receivable_collected)<> 0 or
387 sum(amount_receivable_balance)<> 0 or
388 sum(amount_agreement) <> 0
389 );
390
391 --Bug8741007
392 --END LOOP;
393 --CLOSE agree_cur;
394
395 DELETE FROM FV_REIMB_ACTIVITY_TEMP WHERE reported_flag = 'N';
396
397 -- Submit the RXi Report
398 BEGIN
399 SELECT count(*)
400 INTO l_rec_count
401 FROM FV_REIMB_ACTIVITY_TEMP;
402
403 IF l_rec_count >0 THEN
404 l_req_id :=
405 FND_REQUEST.SUBMIT_REQUEST ('FV','RXFVRACR','','',FALSE,
406 'DIRECT', p_report_id, p_attribute_set, p_output_format);
407 COMMIT;
408
409 IF l_req_id = 0 THEN
410 p_errbuf := 'Error submitting RX Report ';
411 p_retcode := -1 ;
412 RETURN;
413 ELSE
414 fv_utility.log_mesg('Concurrent Request Id for RX Report - ' ||l_req_id);
415 END IF;
416
417 l_call_status := Fnd_Concurrent.Wait_For_Request(
418 l_req_id, 20, 0, l_rphase, l_rstatus,
419 l_dphase, l_dstatus, l_message);
420
421 IF (l_call_status = FALSE) THEN
422 p_errbuf := 'Cannot wait for the status of RX Report.';
423 p_retcode := 1;
424 END IF;
425
426 ELSE
427 p_retcode := 1;
428 p_errbuf := '** No Data Found for the Reimbursamble Activity Process **';
429 RETURN;
430 END IF;
431 END;
432 END Main;
433
434 -------------------------------------------------------------------------------
435 PROCEDURE log (
436 p_level IN NUMBER,
437 p_procedure_name IN VARCHAR2,
438 p_debug_info IN VARCHAR2)
439 IS
440
441 BEGIN
442 IF (p_level >= g_log_level ) THEN
443 FND_LOG.STRING(p_level,
444 p_procedure_name,
445 p_debug_info);
446 END IF;
447 END log;
448 -------------------------------------------------------------------------------
449 ----------------------------------------------
450 /*PROCEDURE get_period_details IS
451 BEGIN
452 SELECT period_num, period_year
453 INTO g_period_num, g_period_year
454 FROM gl_period_statuses
455 WHERE period_name = g_period_name
456 AND application_id = 101
457 AND ledger_id = g_ledger_id;
458 END;*/
459 -------------------------------------------------
460 PROCEDURE get_segment_details IS
461
462 l_module VARCHAR2(500) := g_module_name||'.get_segment_details';
463 l_error_message VARCHAR2(600);
464 l_error_code BOOLEAN;
465
466
467 --This cursor fetches the value_set_id for the natural account segment
468 CURSOR flex_value_id_cur IS
469 SELECT flex_value_set_id
470 FROM fnd_id_flex_segments
471 WHERE application_id = 101
472 AND application_column_name =g_gl_nat_acc_segment
473 AND id_flex_code = g_id_flex_code
474 AND id_flex_num = g_coa_id
475 AND enabled_flag = 'Y' ;
476
477 BEGIN
478 g_retcode := 0;
479
480
481 -- finding the Account and Balancing segments
482 FV_UTILITY.get_segment_col_names(g_coa_id,
483 g_gl_nat_acc_segment,
484 g_gl_balancing_segment,
485 l_error_code,
486 l_error_message);
487
488 log(C_STATE_LEVEL, l_module, 'g_gl_balancing_segment: '||g_gl_balancing_segment);
489 log(C_STATE_LEVEL, l_module, 'g_gl_nat_acc_segment: '||g_gl_nat_acc_segment);
490
491
492 --Finding the Reimbursable Segment name from the reimb segment defined
493 -- in the Federal Financial Options
494 BEGIN
495 SELECT application_column_name
496 INTO g_reimb_agreement_segment
497 FROM FND_ID_FLEX_SEGMENTS_VL
498 WHERE application_id = 101
499 AND id_flex_code = 'GL#'
500 AND id_flex_num = g_coa_id
501 AND enabled_flag = 'Y'
502 AND segment_name like
503 (Select REIMB_AGREEMENT_SEGMENT_VALUE
504 FROM fv_reimb_segment
505 where set_of_books_id = g_ledger_id);
506
507 -- 'Reimbursable Agreement';
508 EXCEPTION
509 WHEN no_data_found THEN
510 log(C_STATE_LEVEL, l_module, 'Error deriving the Reimbursable Agreement Segment ');
511 END;
512
513 log(C_STATE_LEVEL, l_module, 'g_reimb_agreement_segment: '||g_reimb_agreement_segment);
514
515 g_delimiter := Fnd_Flex_Ext.get_delimiter
516 (
517 'SQLGL',
518 g_id_flex_code,
519 g_coa_id
520 );
521 --finding the flex_value_set_id for the Natural Account segment
522 OPEN flex_value_id_cur ;
523 FETCH flex_value_id_cur
524 INTO g_ussgl_flex_value_set_id ;
525 CLOSE flex_value_id_cur ;
526
527 log(C_STATE_LEVEL, l_module, 'g_ussgl_flex_value_set_id: '||g_ussgl_flex_value_set_id);
528
529 IF (g_delimiter IS NULL) THEN
530 g_retcode := 2 ;
531 g_errbuf := 'The Flexfield Structure is not found' ;
532 fv_utility.log_mesg(fnd_log.level_error, l_module,g_errbuf) ;
533 END IF;
534
535 IF (g_retcode = 0) THEN
536 log(C_STATE_LEVEL, l_module, ' delimiter is ' ||g_delimiter);
537 END IF;
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 g_retcode := SQLCODE;
542 g_errbuf := SQLERRM;
543 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.final_exception',g_errbuf) ;
544 RAISE;
545 END get_segment_details;
546 -----------------------------------------------------------------
547
548 PROCEDURE Get_Application_Col_Names IS
549 l_module VARCHAR2(200);
550 l_ctr NUMBER;
551
552
553 CURSOR seg_names_cur IS
554 SELECT application_column_name
555 FROM Fnd_Id_Flex_Segments
556 WHERE application_id = g_apps_id
557 AND id_flex_code = g_id_flex_code
558 AND id_flex_num = g_coa_id
559 ORDER BY segment_num;
560 BEGIN
561 l_module := g_module_name || 'Get_Application_Col_Names';
562
563 i := 1;
564
565 -- Get the Flexfield Column Names(Application Column Names)
566 -- for the Chart Of Accounts Id passed.
567 FOR v_seg_names IN seg_names_cur LOOP
568 gt_seg_name(i) := v_seg_names.application_column_name;
569 i := i + 1;
570 END LOOP;
571
572 -- Get the Maximum number of segments
573 l_ctr := gt_seg_name.COUNT;
574 log(C_STATE_LEVEL, l_module, 'NUMBER OF SEGMENTS ARE '||TO_CHAR(l_CTR));
575 -- Calling Get_Segment_Values procedure
576 Get_Segment_Values(l_ctr);
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 g_retcode := SQLCODE;
581 g_errbuf := SQLERRM;
582 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.final_exception',g_errbuf) ;
583 END Get_Application_Col_Names;
584 ---------------------------------------------------------------------
585 -- ------------------------------------------------------------------
586 -- Procedure Get_Segment_Values
587 -- ------------------------------------------------------------------
588 -- Get_Segment_Values procedure is called from Get_Application_Col_Names
589 -- procedure.
590 -- This procedure builds the where clause based on the segment low
591 -- and high values entered in SRS window ,which will be used in
592 -- building the cursor query in main()
593 -- ------------------------------------------------------------------
594
595
596 PROCEDURE get_segment_values(seg_cnt NUMBER)
597 IS
598
599 l_module VARCHAR2(200);
600
601 -- Variables needed for the Breakup_Segments API,
602 -- used in Get_Application_Col_Names proc
603 --l_nsegs_low NUMBER;
604 --l_nsegs_high NUMBER;
605
606 CURSOR seg_num_cur (p_application_column_name VARCHAR2) IS
607 SELECT segment_num
608 FROM Fnd_Id_Flex_Segments
609 WHERE application_id = g_apps_id
610 AND id_flex_code = g_id_flex_code
611 AND id_flex_num = g_coa_id
612 AND application_column_name=p_application_column_name ;
613 l_acc_seg_num NUMBER;
614 l_bal_seg_num NUMBER ;
615 l_agree_seg_num NUMBER ;
616 -- To count the first entered range in the Felxfield window .
617 l_first_seg NUMBER:=0 ;
618 BEGIN
619 l_module := g_module_name || 'Get_Segment_Values';
620 g_where := '' ;
621
622 -- Get low segment values
623
624 log(C_STATE_LEVEL, l_module, ' Calling populate low segments');
625
626 g_nsegs_low := Fnd_Flex_Ext.breakup_segments
627 (g_flex_low,
628 g_delimiter,
629 gt_seg_low );
630 log(C_STATE_LEVEL, l_module, ' Clow segments: '|| g_nsegs_low);
631 -- Get high segment values
632
633 log(C_STATE_LEVEL, l_module, ' Calling populate high segments');
634
635 g_nsegs_high := Fnd_Flex_Ext.breakup_segments
636 (g_flex_high,
637 g_delimiter,
638 gt_seg_high );
639 log(C_STATE_LEVEL, l_module, ' High segments: '|| g_nsegs_high);
640
641 -- Get the Balancing segment segment number for Flexfield
642 OPEN seg_num_cur(g_gl_balancing_segment ) ;
643 FETCH seg_num_cur INTO l_bal_seg_num ;
644 CLOSE seg_num_cur ;
645
646 log(C_STATE_LEVEL, l_module, ' BALANCING SEGMENT NUMBER IS ' ||l_BAL_SEG_NUM );
647
648 -- Get the Accounting segment number for Flexfield
649 OPEN seg_num_cur (g_gl_nat_acc_segment);
650 FETCH seg_num_cur INTO l_acc_seg_num ;
651 CLOSE seg_num_cur;
652 log(C_STATE_LEVEL, l_module, ' Accounting segment number is ' ||l_ACC_SEG_NUM );
653 -- Get the Reimburabale Agreement Segment number for Flexfield
654 OPEN seg_num_cur (g_reimb_agreement_segment);
655 FETCH seg_num_cur INTO l_agree_seg_num ;
656 CLOSE seg_num_cur;
657 log(C_STATE_LEVEL, l_module, 'Reimburabale agreement segment number is ' ||l_agree_seg_num ) ;
658 --Checking if user has entered the account range in the natural account segment
659 IF g_nsegs_low >1 THEN
660 IF gt_seg_low(l_acc_seg_num) IS NOT NULL THEN
661
662 gt_seg_low(i):=NULL ;
663 gt_seg_high(i):=NULL ;
664
665 log(C_STATE_LEVEL, l_module, 'User has given account range as parameters for this process.this account range will be overriden ');
666 log(C_STATE_LEVEL, l_module, 'With account ranges given in the report definitions set up form ') ;
667 END IF ;
668 END IF;
669
670 g_select := '';
671 FOR i IN 1..30 LOOP
672 IF (i <> l_acc_seg_num AND i <> l_agree_seg_num) THEN
673 IF l_first_seg >=1 THEN
674 g_select := g_select||' , glcc.segment'||i;
675 ELSE
676 g_select := g_select||'glcc.segment'||i;
677 l_first_seg:=l_first_seg+1 ;
678 END IF;
679 END IF;
680 END LOOP;
681 /*
682 -- Construct the concatenate segments based on the Accounting range entered
683 FOR i IN 1..seg_cnt LOOP
684 IF (gt_seg_low(i) IS NOT NULL) AND
685 ( i <> l_acc_seg_num AND i <> l_agree_seg_num) THEN
686 IF l_first_seg >=1 THEN
687 g_select:= g_select||' , glcc.'||gt_seg_name(i);
688 ELSE
689 g_select:= g_select||'glcc.'||gt_seg_name(i);
690 l_first_seg:=l_first_seg+1 ;
691 END IF ;
692 END IF ;
693 END LOOP ;
694 */
695 log(C_STATE_LEVEL, l_module, 'Select statement is ' ||g_select);
696 -- Construct a where condition to be used to select the
697 -- transactions from gl_balances and gl_bc_packets
698 l_first_seg:=0 ;
699 IF g_nsegs_low >1 THEN
700
701 FOR i IN 1..seg_cnt LOOP
702
703 IF (gt_seg_low(i) IS NOT NULL) AND ( i <> l_acc_seg_num AND i <> l_agree_seg_num) THEN
704
705 IF l_first_seg >=1 THEN
706 g_where := g_where ||' AND '||'glcc.'||
707 gt_seg_name(i) ||' BETWEEN '
708 ||''''||gt_seg_low(i)||''''||' AND '
709 ||''''||gt_seg_high(i)||'''' ;
710 ELSE
711 g_where := ' AND glcc.'||gt_seg_name(i) ||' BETWEEN '
712 ||''''||gt_seg_low(i)||''''||' AND '
713 ||''''||gt_seg_high(i)||'''' ;
714 l_first_seg:=l_first_seg+1 ;
715 END IF ;
716 END IF ;
717 END LOOP;
718 END IF;
719 log(C_STATE_LEVEL, l_module, 'WHERE clause is ' ||g_where );
720 --Populate_CCIDs(g_select,g_where);
721 EXCEPTION
722 WHEN OTHERS THEN
723 g_retcode := SQLCODE;
724 g_ERRBUF := SQLERRM;
725 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.final_exception',g_ERRBUF) ;
726 RAISE;
727 END Get_Segment_Values ;
728 ------------------------------------
729 -- Procedure to poulate the gt table for all the columns
730
731 PROCEDURE populate_amount(p_column_id IN NUMBER,
732 p_agree_num IN VARCHAR2) IS
733
734 -- Variables used for fetching values from XLA table
735 l_sla_amount VARCHAR2(1000);
736
737 --Variables used for GL_BALANCES and GL_BC_PACKETS tables
738 l_bal_amount VARCHAR2(1000);
739 l_bc_amount VARCHAR2(1000);
740 l_glbal_sql VARCHAR2(3000);
741 l_glbc_sql VARCHAR2(3000);
742 l_trx_sql VARCHAR2(1000);
743 l_column_id NUMBER;
744 l_rec_sla_detail VARCHAR2(1);
745 l_module VARCHAR2(500) := g_module_name||'.populate_amount';
746 l_main_sql VARCHAR2(25000);
747 l_main_cursor INTEGER;
748 --declare cursors
749 TYPE ref_type IS REF CURSOR ;
750 segment_cur ref_type;
751 l_trx_number ra_customer_trx_all.trx_number%TYPE;
752 l_purchase_order ra_customer_trx_all.purchase_order%TYPE;
753 l_start_date_commitment ra_customer_trx_all.start_date_commitment%TYPE;
754 l_end_date_commitment ra_customer_trx_all.end_date_commitment%TYPE;
755 l_extended_amount ra_customer_trx_lines_all.extended_amount%TYPE;
756 l_agree_num VARCHAR2(25);
757 l_amount VARCHAR2(50);
758 l_select_mod VARCHAR2(2000);
759 l_exec_ret INTEGER ;
760 l_count NUMBER(2);
761 l_activity_rec FV_REIMB_ACTIVITY_TEMP%ROWTYPE;
762 l_temp_segnumber VARCHAR2(10);
763 l_natural_balance_type VARCHAR2(10);
764 l_segment1 VARCHAR2(30);
765 l_segment2 VARCHAR2(30);
766 l_segment3 VARCHAR2(30);
767 l_segment4 VARCHAR2(30);
768 l_segment5 VARCHAR2(30);
769 l_segment6 VARCHAR2(30);
770 l_segment7 VARCHAR2(30);
771 l_segment8 VARCHAR2(30);
772 l_segment9 VARCHAR2(30);
773 l_segment10 VARCHAR2(30);
774 l_segment11 VARCHAR2(30);
775 l_segment12 VARCHAR2(30);
776 l_segment13 VARCHAR2(30);
777 l_segment14 VARCHAR2(30);
778 l_segment15 VARCHAR2(30);
779 l_segment16 VARCHAR2(30);
780 l_segment17 VARCHAR2(30);
781 l_segment18 VARCHAR2(30);
782 l_segment19 VARCHAR2(30);
783 l_segment20 VARCHAR2(30);
784 l_segment21 VARCHAR2(30);
785 l_segment22 VARCHAR2(30);
786 l_segment23 VARCHAR2(30);
787 l_segment24 VARCHAR2(30);
788 l_segment25 VARCHAR2(30);
789 l_segment26 VARCHAR2(30);
790 l_segment27 VARCHAR2(30);
791 l_segment28 VARCHAR2(30);
792 l_segment29 VARCHAR2(30);
793 l_segment30 VARCHAR2(30);
794
795 BEGIN
796
797 l_column_id := p_column_id;
798 l_agree_num := p_agree_num;
799
800 BEGIN
801 -- Bug 8992292
802 --Added condition to check set_of_books_id to avoid multiple rows
803 SELECT rec_sla_detail, natural_balance_type
804 INTO l_rec_sla_detail, l_natural_balance_type
805 FROM fv_reimb_definitions_lines
806 WHERE column_id = l_column_id and set_of_books_id=g_ledger_id;
807 EXCEPTION
808 WHEN NO_DATA_FOUND then
809 g_retcode := SQLCODE;
810 g_ERRBUF := SQLERRM;
811 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'Incomplete Report Definition Setup',g_ERRBUF) ;
812 RAISE;
813 END;
814 -- Remove 'glcc.' from g_select
815 SELECT REPLACE(g_select, 'glcc.', '') INTO l_select_mod FROM dual;
816
817 log(C_STATE_LEVEL, l_module, 'l_select_mod : '||l_select_mod);
818 log(C_STATE_LEVEL, l_module, 'l_column_id : '||l_column_id);
819
820 create_main_query(l_rec_sla_detail, l_select_mod, l_column_id, l_main_sql);
821
822
823 -- Fetch the segments into the binary table gt_seg_codes.
824 -- If l_select_mod = SEGMENT1 , SEGMENT13 , SEGMENT15 THEN
825 -- gt_seg_codes(1) = SEGMENT1 , gt_seg_codes(13) = SEGMENT13 , gt_seg_codes(15) = SEGMENT15
826 --
827 WHILE LENGTH(l_select_mod)>9 LOOP
828 SELECT substr(l_select_mod,1, instr(l_select_mod, ' , ')) INTO l_temp_segnumber FROM dual;
829 gt_seg_codes((to_number((substr(l_temp_segnumber,8))))) := l_temp_segnumber;
830 SELECT substr(l_select_mod, instr(l_select_mod, ' , ')+3) INTO l_select_mod FROM dual;
831 END LOOP;
832 gt_seg_codes((to_number((substr(l_select_mod,8))))) := l_select_mod;
833
834 FOR counter IN 1..30
835 LOOP
836 IF (gt_seg_codes(counter) IS not null) then
837 log(C_STATE_LEVEL, l_module, 'gt_seg_codes( '|| counter ||'): '||gt_seg_codes(counter));
838 END IF;
839 END LOOP;
840
841 -- FOR counter IN 1..30
842 -- LOOP
843 -- gt_seg_codes(counter) := 'segment'||counter;
844 -- log(C_STATE_LEVEL, l_module, 'gt_seg_codes( '|| counter ||'): '||gt_seg_codes(counter));
845 -- END LOOP;
846
847
848 BEGIN
849 l_main_cursor := DBMS_SQL.OPEN_CURSOR ;
850 EXCEPTION
851 WHEN OTHERS THEN
852 g_retcode := SQLCODE;
853 g_ERRBUF := SQLERRM;
854 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.dbms_sql_l_main_cursor',g_ERRBUF) ;
855 RAISE;
856 END ;
857
858 BEGIN
859 dbms_sql.parse(l_main_cursor,l_main_sql,DBMS_SQL.V7);
860 EXCEPTION
861 WHEN OTHERS THEN
862 g_retcode := SQLCODE;
863 g_ERRBUF := SQLERRM;
864 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.dbms_sql_parse_l_main_cursor',g_ERRBUF) ;
865 RAISE;
866 END ;
867
868 --log(C_STATE_LEVEL, l_module, 'g_period_name: '|| g_period_name );
869 log(C_STATE_LEVEL, l_module, 'g_ledger_id: '|| g_ledger_id );
870 log(C_STATE_LEVEL, l_module, 'g_ussgl_flex_value_set_id: '|| g_ussgl_flex_value_set_id );
871 log(C_STATE_LEVEL, l_module, 'g_coa_id: '|| g_coa_id );
872 log(C_STATE_LEVEL, l_module, 'l_column_id: '|| l_column_id );
873
874 IF (l_rec_sla_detail = 'N') THEN
875 --dbms_sql.bind_variable(l_main_cursor,':g_period_year',g_period_year);
876 --dbms_sql.bind_variable(l_main_cursor,':g_period_num',g_period_num);
877 dbms_sql.bind_variable(l_main_cursor,':g_ledger_id',g_ledger_id);
878 dbms_sql.bind_variable(l_main_cursor,':g_ussgl_flex_value_set_id',g_ussgl_flex_value_set_id);
879 dbms_sql.bind_variable(l_main_cursor,':g_coa_id',g_coa_id);
880 dbms_sql.bind_variable(l_main_cursor,':l_agree_num',p_agree_num);
881 dbms_sql.bind_variable(l_main_cursor,':l_column_id',l_column_id);
882 --dbms_sql.bind_variable(l_main_cursor,':g_period_year',g_period_year);
883 --dbms_sql.bind_variable(l_main_cursor,':g_period_num',g_period_num);
884 dbms_sql.bind_variable(l_main_cursor,':g_ledger_id',g_ledger_id);
885 dbms_sql.bind_variable(l_main_cursor,':g_ussgl_flex_value_set_id',g_ussgl_flex_value_set_id);
886 dbms_sql.bind_variable(l_main_cursor,':g_coa_id',g_coa_id);
887 dbms_sql.bind_variable(l_main_cursor,':l_agree_num',p_agree_num);
888 dbms_sql.bind_variable(l_main_cursor,':l_column_id',l_column_id);
889 ELSE
890 --dbms_sql.bind_variable(l_main_cursor,':g_period_name',g_period_name);
891 dbms_sql.bind_variable(l_main_cursor,':g_ledger_id',g_ledger_id);
892 dbms_sql.bind_variable(l_main_cursor,':g_ussgl_flex_value_set_id',g_ussgl_flex_value_set_id);
893 dbms_sql.bind_variable(l_main_cursor,':g_coa_id',g_coa_id);
894 dbms_sql.bind_variable(l_main_cursor,':l_agree_num',p_agree_num);
895 dbms_sql.bind_variable(l_main_cursor,':l_column_id',l_column_id);
896 END IF;
897
898 log(C_STATE_LEVEL, l_module, 'completed dbms_sql.bind_variable' );
899
900 l_count :=0;
901 IF gt_seg_codes(1) IS NOT NULL THEN
902 l_count := l_count+1 ;
903 dbms_sql.define_column(l_main_cursor,l_count,l_segment1,25);
904 END IF;
905 IF gt_seg_codes(2) IS NOT NULL THEN
906 l_count := l_count+1 ;
907 dbms_sql.define_column(l_main_cursor,l_count,l_segment2,25);
908 END IF;
909 IF gt_seg_codes(3) IS NOT NULL THEN
910 l_count := l_count+1 ;
911 dbms_sql.define_column(l_main_cursor,l_count,l_segment3,25);
912 END IF;
913 IF gt_seg_codes(4) IS NOT NULL THEN
914 l_count := l_count+1 ;
915 dbms_sql.define_column(l_main_cursor,l_count,l_segment4,25);
916 END IF;
917 IF gt_seg_codes(5) IS NOT NULL THEN
918 l_count := l_count+1 ;
919 dbms_sql.define_column(l_main_cursor,l_count,l_segment5,25);
920 END IF;
921 IF gt_seg_codes(6) IS NOT NULL THEN
922 l_count := l_count+1 ;
923 dbms_sql.define_column(l_main_cursor,l_count,l_segment6,25);
924 END IF;
925 IF gt_seg_codes(7) IS NOT NULL THEN
926 l_count := l_count+1 ;
927 dbms_sql.define_column(l_main_cursor,l_count,l_segment7,25);
928 END IF;
929 IF gt_seg_codes(8) IS NOT NULL THEN
930 l_count := l_count+1 ;
931 dbms_sql.define_column(l_main_cursor,l_count,l_segment8,25);
932 END IF;
933 IF gt_seg_codes(9) IS NOT NULL THEN
934 l_count := l_count+1 ;
935 dbms_sql.define_column(l_main_cursor,l_count,l_segment9,25);
936 END IF;
937 IF gt_seg_codes(10) IS NOT NULL THEN
938 l_count := l_count+1 ;
939 dbms_sql.define_column(l_main_cursor,l_count,l_segment10,25);
940 END IF;
941 IF gt_seg_codes(11) IS NOT NULL THEN
942 l_count := l_count+1 ;
943 dbms_sql.define_column(l_main_cursor,l_count,l_segment11,25);
944 END IF;
945 IF gt_seg_codes(12) IS NOT NULL THEN
946 l_count := l_count+1 ;
947 dbms_sql.define_column(l_main_cursor,l_count,l_segment12,25);
948 END IF;
949 IF gt_seg_codes(13) IS NOT NULL THEN
950 l_count := l_count+1 ;
951 dbms_sql.define_column(l_main_cursor,l_count,l_segment13,25);
952 END IF;
953 IF gt_seg_codes(14) IS NOT NULL THEN
954 l_count := l_count+1 ;
955 dbms_sql.define_column(l_main_cursor,l_count,l_segment14,25);
956 END IF;
957 IF gt_seg_codes(15) IS NOT NULL THEN
958 l_count := l_count+1 ;
959 dbms_sql.define_column(l_main_cursor,l_count,l_segment15,25);
960 END IF;
961 IF gt_seg_codes(16) IS NOT NULL THEN
962 l_count := l_count+1 ;
963 dbms_sql.define_column(l_main_cursor,l_count,l_segment16,25);
964 END IF;
965 IF gt_seg_codes(17) IS NOT NULL THEN
966 l_count := l_count+1 ;
967 dbms_sql.define_column(l_main_cursor,l_count,l_segment17,25);
968 END IF;
969 IF gt_seg_codes(18) IS NOT NULL THEN
970 l_count := l_count+1 ;
971 dbms_sql.define_column(l_main_cursor,l_count,l_segment18,25);
972 END IF;
973 IF gt_seg_codes(19) IS NOT NULL THEN
974 l_count := l_count+1 ;
975 dbms_sql.define_column(l_main_cursor,l_count,l_segment19,25);
976 END IF;
977 IF gt_seg_codes(20) IS NOT NULL THEN
978 l_count := l_count+1 ;
979 dbms_sql.define_column(l_main_cursor,l_count,l_segment20,25);
980 END IF;
981 IF gt_seg_codes(21) IS NOT NULL THEN
982 l_count := l_count+1 ;
983 dbms_sql.define_column(l_main_cursor,l_count,l_segment21,25);
984 END IF;
985 IF gt_seg_codes(22) IS NOT NULL THEN
986 l_count := l_count+1 ;
987 dbms_sql.define_column(l_main_cursor,l_count,l_segment22,25);
988 END IF;
989 IF gt_seg_codes(23) IS NOT NULL THEN
990 l_count := l_count+1 ;
991 dbms_sql.define_column(l_main_cursor,l_count,l_segment23,25);
992 END IF;
993 IF gt_seg_codes(24) IS NOT NULL THEN
994 l_count := l_count+1 ;
995 dbms_sql.define_column(l_main_cursor,l_count,l_segment24,25);
996 END IF;
997 IF gt_seg_codes(25) IS NOT NULL THEN
998 l_count := l_count+1 ;
999 dbms_sql.define_column(l_main_cursor,l_count,l_segment25,25);
1000 END IF;
1001 IF gt_seg_codes(26) IS NOT NULL THEN
1002 l_count := l_count+1 ;
1003 dbms_sql.define_column(l_main_cursor,l_count,l_segment26,25);
1004 END IF;
1005 IF gt_seg_codes(27) IS NOT NULL THEN
1006 l_count := l_count+1 ;
1007 dbms_sql.define_column(l_main_cursor,l_count,l_segment27,25);
1008 END IF;
1009 IF gt_seg_codes(28) IS NOT NULL THEN
1010 l_count := l_count+1 ;
1011 dbms_sql.define_column(l_main_cursor,l_count,l_segment28,25);
1012 END IF;
1013 IF gt_seg_codes(29) IS NOT NULL THEN
1014 l_count := l_count+1 ;
1015 dbms_sql.define_column(l_main_cursor,l_count,l_segment29,25);
1016 END IF;
1017 IF gt_seg_codes(30) IS NOT NULL THEN
1018 l_count := l_count+1 ;
1019 dbms_sql.define_column(l_main_cursor,l_count,l_segment30,25);
1020 END IF;
1021 l_count := l_count+1 ;
1022 dbms_sql.define_column(l_main_cursor,l_count,l_amount,25);
1023
1024 log(C_STATE_LEVEL, l_module, 'completed dbms_sql.define_column' );
1025
1026 BEGIN
1027 l_exec_ret := dbms_sql.execute(l_main_cursor);
1028 EXCEPTION
1029 WHEN OTHERS THEN
1030 g_retcode := SQLCODE;
1031 g_ERRBUF := SQLERRM;
1032 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.dbms_sql_execute_l_main_cursor',g_ERRBUF) ;
1033 RAISE;
1034 END ;
1035
1036 log(C_STATE_LEVEL, l_module, 'completed dbms_sql.execute' );
1037
1038 l_trx_sql:='SELECT h.trx_number, h.purchase_order,
1039 h.start_date_commitment,
1040 h.end_date_commitment,
1041 l.extended_amount
1042 FROM ra_customer_trx_all h,
1043 ra_customer_trx_lines_all l
1044 WHERE h.set_of_books_id = :g_ledger_id
1045 AND h.trx_number = :g_agree_num
1046 AND h.customer_trx_id = l.customer_trx_id';
1047
1048 EXECUTE IMMEDIATE l_trx_sql INTO l_trx_number, l_purchase_order,
1049 l_start_date_commitment,
1050 l_end_date_commitment,
1051 l_extended_amount
1052 USING
1053 g_ledger_id, l_agree_num;
1054
1055 log(C_STATE_LEVEL, l_module, 'proceeding with dbms_sql.fetch_rows ' );
1056
1057 WHILE dbms_sql.fetch_rows(l_main_cursor)>0 LOOP
1058
1059 log(C_STATE_LEVEL, l_module, 'In side while loop: ');
1060
1061 l_count:=0;
1062 IF gt_seg_codes(1) IS NOT NULL THEN
1063 l_count := l_count+1 ;
1064 dbms_sql.column_value(l_main_cursor, l_count, l_segment1);
1065 log(C_STATE_LEVEL, l_module, ' l_segment1 '|| l_segment1);
1066 l_activity_rec.segment1 := l_segment1;
1067 END IF;
1068 IF gt_seg_codes(2) IS NOT NULL THEN
1069 l_count := l_count+1 ;
1070 dbms_sql.column_value(l_main_cursor, l_count, l_segment2);
1071 log(C_STATE_LEVEL, l_module, ' l_segment2 '|| l_segment2);
1072 l_activity_rec.segment2 := l_segment2;
1073 END IF;
1074 IF gt_seg_codes(3) IS NOT NULL THEN
1075 l_count := l_count+1 ;
1076 dbms_sql.column_value(l_main_cursor, l_count, l_segment3);
1077 log(C_STATE_LEVEL, l_module, ' l_segment3 '|| l_segment3);
1078 l_activity_rec.segment3 := l_segment3;
1079 END IF;
1080 IF gt_seg_codes(4) IS NOT NULL THEN
1081 l_count := l_count+1 ;
1082 dbms_sql.column_value(l_main_cursor, l_count, l_segment4);
1083 log(C_STATE_LEVEL, l_module, ' l_segment4 '|| l_segment4);
1084 l_activity_rec.segment4 := l_segment4;
1085 END IF;
1086 IF gt_seg_codes(5) IS NOT NULL THEN
1087 l_count := l_count+1 ;
1088 dbms_sql.column_value(l_main_cursor, l_count, l_segment5);
1089 log(C_STATE_LEVEL, l_module, ' l_segment5 '|| l_segment5);
1090 l_activity_rec.segment5 := l_segment5;
1091 END IF;
1092 IF gt_seg_codes(6) IS NOT NULL THEN
1093 l_count := l_count+1 ;
1094 dbms_sql.column_value(l_main_cursor, l_count, l_segment6);
1095 log(C_STATE_LEVEL, l_module, ' l_segment6 '|| l_segment6);
1096 l_activity_rec.segment6 := l_segment6;
1097 END IF;
1098 IF gt_seg_codes(7) IS NOT NULL THEN
1099 l_count := l_count+1 ;
1100 dbms_sql.column_value(l_main_cursor, l_count, l_segment7);
1101 log(C_STATE_LEVEL, l_module, ' l_segment7 '|| l_segment7);
1102 l_activity_rec.segment7 := l_segment7;
1103 END IF;
1104 IF gt_seg_codes(8) IS NOT NULL THEN
1105 l_count := l_count+1 ;
1106 dbms_sql.column_value(l_main_cursor, l_count, l_segment8);
1107 log(C_STATE_LEVEL, l_module, ' l_segment8 '|| l_segment8);
1108 l_activity_rec.segment8 := l_segment8;
1109 END IF;
1110 IF gt_seg_codes(9) IS NOT NULL THEN
1111 l_count := l_count+1 ;
1112 dbms_sql.column_value(l_main_cursor, l_count, l_segment9);
1113 log(C_STATE_LEVEL, l_module, ' l_segment9 '|| l_segment9);
1114 l_activity_rec.segment9 := l_segment9;
1115 END IF;
1116 IF gt_seg_codes(10) IS NOT NULL THEN
1117 l_count := l_count+1 ;
1118 dbms_sql.column_value(l_main_cursor, l_count, l_segment10);
1119 log(C_STATE_LEVEL, l_module, ' l_segment10 '|| l_segment10);
1120 l_activity_rec.segment10 := l_segment10;
1121 END IF;
1122 IF gt_seg_codes(11) IS NOT NULL THEN
1123 l_count := l_count+1 ;
1124 dbms_sql.column_value(l_main_cursor, l_count, l_segment11);
1125 log(C_STATE_LEVEL, l_module, ' l_segment11 '|| l_segment11);
1126 l_activity_rec.segment11 := l_segment11;
1127 END IF;
1128 IF gt_seg_codes(12) IS NOT NULL THEN
1129 l_count := l_count+1 ;
1130 dbms_sql.column_value(l_main_cursor, l_count, l_segment12);
1131 log(C_STATE_LEVEL, l_module, ' l_segment12 '|| l_segment12);
1132 l_activity_rec.segment12 := l_segment12;
1133 END IF;
1134 IF gt_seg_codes(13) IS NOT NULL THEN
1135 l_count := l_count+1 ;
1136 dbms_sql.column_value(l_main_cursor, l_count, l_segment13);
1137 log(C_STATE_LEVEL, l_module, ' l_segment13 '|| l_segment13);
1138 l_activity_rec.segment13 := l_segment13;
1139 END IF;
1140 IF gt_seg_codes(14) IS NOT NULL THEN
1141 l_count := l_count+1 ;
1142 dbms_sql.column_value(l_main_cursor, l_count, l_segment14);
1143 log(C_STATE_LEVEL, l_module, ' l_segment14 '|| l_segment14);
1144 l_activity_rec.segment14 := l_segment14;
1145 END IF;
1146 IF gt_seg_codes(15) IS NOT NULL THEN
1147 l_count := l_count+1 ;
1148 dbms_sql.column_value(l_main_cursor, l_count, l_segment15);
1149 log(C_STATE_LEVEL, l_module, ' l_segment15 '|| l_segment15);
1150 l_activity_rec.segment15 := l_segment15;
1151 END IF;
1152 IF gt_seg_codes(16) IS NOT NULL THEN
1153 l_count := l_count+1 ;
1154 dbms_sql.column_value(l_main_cursor, l_count, l_segment16);
1155 log(C_STATE_LEVEL, l_module, ' l_segment16 '|| l_segment16);
1156 l_activity_rec.segment16 := l_segment16;
1157 END IF;
1158 IF gt_seg_codes(17) IS NOT NULL THEN
1159 l_count := l_count+1 ;
1160 dbms_sql.column_value(l_main_cursor, l_count, l_segment17);
1161 log(C_STATE_LEVEL, l_module, ' l_segment17 '|| l_segment17);
1162 l_activity_rec.segment17 := l_segment17;
1163 END IF;
1164 IF gt_seg_codes(18) IS NOT NULL THEN
1165 l_count := l_count+1 ;
1166 dbms_sql.column_value(l_main_cursor, l_count, l_segment18);
1167 log(C_STATE_LEVEL, l_module, ' l_segment18 '|| l_segment18);
1168 l_activity_rec.segment18 := l_segment18;
1169 END IF;
1170 IF gt_seg_codes(19) IS NOT NULL THEN
1171 l_count := l_count+1 ;
1172 dbms_sql.column_value(l_main_cursor, l_count, l_segment19);
1173 log(C_STATE_LEVEL, l_module, ' l_segment19 '|| l_segment19);
1174 l_activity_rec.segment19 := l_segment19;
1175 END IF;
1176 IF gt_seg_codes(20) IS NOT NULL THEN
1177 l_count := l_count+1 ;
1178 dbms_sql.column_value(l_main_cursor, l_count, l_segment20);
1179 log(C_STATE_LEVEL, l_module, ' l_segment20 '|| l_segment20);
1180 l_activity_rec.segment20 := l_segment20;
1181 END IF;
1182 IF gt_seg_codes(21) IS NOT NULL THEN
1183 l_count := l_count+1 ;
1184 dbms_sql.column_value(l_main_cursor, l_count, l_segment21);
1185 log(C_STATE_LEVEL, l_module, ' l_segment21 '|| l_segment21);
1186 l_activity_rec.segment21 := l_segment21;
1187 END IF;
1188 IF gt_seg_codes(22) IS NOT NULL THEN
1189 l_count := l_count+1 ;
1190 dbms_sql.column_value(l_main_cursor, l_count, l_segment22);
1191 log(C_STATE_LEVEL, l_module, ' l_segment22 '|| l_segment22);
1192 l_activity_rec.segment22 := l_segment22;
1193 END IF;
1194 IF gt_seg_codes(23) IS NOT NULL THEN
1195 l_count := l_count+1 ;
1196 dbms_sql.column_value(l_main_cursor, l_count, l_segment23);
1197 log(C_STATE_LEVEL, l_module, ' l_segment23 '|| l_segment23);
1198 l_activity_rec.segment23 := l_segment23;
1199 END IF;
1200 IF gt_seg_codes(24) IS NOT NULL THEN
1201 l_count := l_count+1 ;
1202 dbms_sql.column_value(l_main_cursor, l_count, l_segment24);
1203 log(C_STATE_LEVEL, l_module, ' l_segment24 '|| l_segment24);
1204 l_activity_rec.segment24 := l_segment24;
1205 END IF;
1206 IF gt_seg_codes(25) IS NOT NULL THEN
1207 l_count := l_count+1 ;
1208 dbms_sql.column_value(l_main_cursor, l_count, l_segment25);
1209 log(C_STATE_LEVEL, l_module, ' l_segment25 '|| l_segment25);
1210 l_activity_rec.segment25 := l_segment25;
1211 END IF;
1212 IF gt_seg_codes(26) IS NOT NULL THEN
1213 l_count := l_count+1 ;
1214 dbms_sql.column_value(l_main_cursor, l_count, l_segment26);
1215 log(C_STATE_LEVEL, l_module, ' l_segment26 '|| l_segment26);
1216 l_activity_rec.segment26 := l_segment26;
1217 END IF;
1218 IF gt_seg_codes(27) IS NOT NULL THEN
1219 l_count := l_count+1 ;
1220 dbms_sql.column_value(l_main_cursor, l_count, l_segment27);
1221 log(C_STATE_LEVEL, l_module, ' l_segment27 '|| l_segment27);
1222 l_activity_rec.segment27 := l_segment27;
1223 END IF;
1224 IF gt_seg_codes(28) IS NOT NULL THEN
1225 l_count := l_count+1 ;
1226 dbms_sql.column_value(l_main_cursor, l_count, l_segment28);
1227 log(C_STATE_LEVEL, l_module, ' l_segment28 '|| l_segment28);
1228 l_activity_rec.segment28 := l_segment28;
1229 END IF;
1230 IF gt_seg_codes(29) IS NOT NULL THEN
1231 l_count := l_count+1 ;
1232 dbms_sql.column_value(l_main_cursor, l_count, l_segment29);
1233 log(C_STATE_LEVEL, l_module, ' l_segment29 '|| l_segment29);
1234 l_activity_rec.segment29 := l_segment29;
1235 END IF;
1236 IF gt_seg_codes(30) IS NOT NULL THEN
1237 l_count := l_count+1 ;
1238 dbms_sql.column_value(l_main_cursor, l_count, l_segment30);
1239 log(C_STATE_LEVEL, l_module, ' l_segment30 '|| l_segment30);
1240 l_activity_rec.segment30 := l_segment30;
1241 END IF;
1242 l_count := l_count+1 ;
1243 dbms_sql.column_value(l_main_cursor, l_count, l_amount);
1244 log(C_STATE_LEVEL, l_module, 'l_amount '|| l_amount);
1245
1246
1247 IF (l_natural_balance_type = 'Credit') then
1248 l_amount := l_amount * -1;
1249 END IF;
1250
1251 CASE l_column_id
1252 WHEN 1 THEN l_activity_rec.amount_agreement := l_amount;
1253 WHEN 2 THEN l_activity_rec.amount_obligation := l_amount;
1254 WHEN 3 THEN l_activity_rec.amount_expended := l_amount;
1255 WHEN 4 THEN l_activity_rec.amount_unfilled_order := l_amount;
1256 WHEN 5 THEN l_activity_rec.amount_advance_collected := l_amount;
1257 WHEN 6 THEN l_activity_rec.amount_advance_applied := l_amount;
1258 WHEN 7 THEN l_activity_rec.amount_advance_balance := l_amount;
1259 WHEN 8 THEN l_activity_rec.amount_earned := l_amount;
1260 WHEN 9 THEN l_activity_rec.amount_billed := l_amount;
1261 WHEN 10 THEN l_activity_rec.amount_receivable_collected := l_amount;
1262 WHEN 11 THEN l_activity_rec.amount_receivable_balance := l_amount;
1263 END CASE;
1264
1265 log(C_STATE_LEVEL, l_module, '************************************************');
1266 log(C_STATE_LEVEL, l_module, 'Inserting values to FV_REIMB_ACTIVITY_TEMP with reported_flag = N ');
1267 log(C_STATE_LEVEL, l_module, 'Reimb_Agreement_Num: '||l_trx_number);
1268 log(C_STATE_LEVEL, l_module, 'amount_obligation: '||nvl(l_activity_rec.amount_obligation,0));
1269 log(C_STATE_LEVEL, l_module, 'amount_expended: '||nvl(l_activity_rec.amount_expended,0));
1270 log(C_STATE_LEVEL, l_module, 'amount_unfilled_order: '||nvl(l_activity_rec.amount_unfilled_order,0));
1271 log(C_STATE_LEVEL, l_module, 'amount_advance_collected: '||nvl(l_activity_rec.amount_advance_collected,0));
1272 log(C_STATE_LEVEL, l_module, 'amount_advance_applied: '||nvl(l_activity_rec.amount_advance_applied,0));
1273 log(C_STATE_LEVEL, l_module, 'amount_advance_balance: '||nvl(l_activity_rec.amount_advance_balance,0));
1274 log(C_STATE_LEVEL, l_module, 'amount_earned: '||nvl(l_activity_rec.amount_earned,0));
1275 log(C_STATE_LEVEL, l_module, 'amount_billed: '||nvl(l_activity_rec.amount_billed,0));
1276 log(C_STATE_LEVEL, l_module, 'amount_receivable_collected: '||nvl(l_activity_rec.amount_receivable_collected,0));
1277 log(C_STATE_LEVEL, l_module, 'amount_receivable_balance: '||nvl(l_activity_rec.amount_receivable_balance,0));
1278 log(C_STATE_LEVEL, l_module, 'amount_agreement: '||nvl(l_activity_rec.amount_agreement,0));
1279 log(C_STATE_LEVEL, l_module, 'segment1: '||l_activity_rec.segment1);
1280 log(C_STATE_LEVEL, l_module, 'segment2: '||l_activity_rec.segment2);
1281 log(C_STATE_LEVEL, l_module, 'segment3: '||l_activity_rec.segment3);
1282 log(C_STATE_LEVEL, l_module, 'segment4: '||l_activity_rec.segment4);
1283 log(C_STATE_LEVEL, l_module, 'segment5: '||l_activity_rec.segment5);
1284 log(C_STATE_LEVEL, l_module, 'segment6: '||l_activity_rec.segment6);
1285 log(C_STATE_LEVEL, l_module, 'segment7: '||l_activity_rec.segment7);
1286 log(C_STATE_LEVEL, l_module, 'segment8: '||l_activity_rec.segment8);
1287 log(C_STATE_LEVEL, l_module, 'segment9: '||l_activity_rec.segment9);
1288 log(C_STATE_LEVEL, l_module, 'segment10: '||l_activity_rec.segment10);
1289 log(C_STATE_LEVEL, l_module, 'segment11: '||l_activity_rec.segment11);
1290 log(C_STATE_LEVEL, l_module, 'segment12: '||l_activity_rec.segment12);
1291 log(C_STATE_LEVEL, l_module, 'segment13: '||l_activity_rec.segment13);
1292 log(C_STATE_LEVEL, l_module, 'segment14: '||l_activity_rec.segment14);
1293 log(C_STATE_LEVEL, l_module, 'segment15: '||l_activity_rec.segment15);
1294 log(C_STATE_LEVEL, l_module, 'segment16: '||l_activity_rec.segment16);
1295 log(C_STATE_LEVEL, l_module, 'segment17: '||l_activity_rec.segment17);
1296 log(C_STATE_LEVEL, l_module, 'segment18: '||l_activity_rec.segment18);
1297 log(C_STATE_LEVEL, l_module, 'segment19: '||l_activity_rec.segment19);
1298 log(C_STATE_LEVEL, l_module, 'segment20: '||l_activity_rec.segment20);
1299 log(C_STATE_LEVEL, l_module, 'segment21: '||l_activity_rec.segment21);
1300 log(C_STATE_LEVEL, l_module, 'segment22: '||l_activity_rec.segment22);
1301 log(C_STATE_LEVEL, l_module, 'segment23: '||l_activity_rec.segment23);
1302 log(C_STATE_LEVEL, l_module, 'segment24: '||l_activity_rec.segment24);
1303 log(C_STATE_LEVEL, l_module, 'segment25: '||l_activity_rec.segment25);
1304 log(C_STATE_LEVEL, l_module, 'segment26: '||l_activity_rec.segment26);
1305 log(C_STATE_LEVEL, l_module, 'segment27: '||l_activity_rec.segment27);
1306 log(C_STATE_LEVEL, l_module, 'segment28: '||l_activity_rec.segment28);
1307 log(C_STATE_LEVEL, l_module, 'segment29: '||l_activity_rec.segment29);
1308 log(C_STATE_LEVEL, l_module, 'segment30: '||l_activity_rec.segment30);
1309 log(C_STATE_LEVEL, l_module, '************************************************');
1310
1311 INSERT INTO FV_REIMB_ACTIVITY_TEMP (Reimb_Agreement_Num,
1312 Contract_Number,
1313 Billing_Limit,
1314 start_date,
1315 end_date,
1316 amount_obligation,
1317 amount_expended,
1318 amount_unfilled_order,
1319 amount_advance_collected,
1320 amount_advance_applied,
1321 amount_advance_balance,
1322 amount_earned,
1323 amount_billed,
1324 amount_receivable_collected,
1325 amount_receivable_balance,
1326 amount_agreement,
1327 REQUEST_ID,
1328 LAST_UPDATED_BY,
1329 LAST_UPDATE_LOGIN,
1330 CREATED_BY,
1331 CREATION_DATE,
1332 LAST_UPDATE_DATE,
1333 segment1,
1334 segment2,
1335 segment3,
1336 segment4,
1337 segment5,
1338 segment6,
1339 segment7,
1340 segment8,
1341 segment9,
1342 segment10,
1343 segment11,
1344 segment12,
1345 segment13,
1346 segment14,
1347 segment15,
1348 segment16,
1349 segment17,
1350 segment18,
1351 segment19,
1352 segment20,
1353 segment21,
1354 segment22,
1355 segment23,
1356 segment24,
1357 segment25,
1358 segment26,
1359 segment27,
1360 segment28,
1361 segment29,
1362 segment30,
1363 reported_flag)
1364 values(l_trx_number,
1365 l_purchase_order,
1366 l_extended_amount,
1367 l_start_date_commitment,
1368 l_end_date_commitment,
1369 nvl(l_activity_rec.amount_obligation,0),
1370 nvl(l_activity_rec.amount_expended,0),
1371 nvl(l_activity_rec.amount_unfilled_order,0),
1372 nvl(l_activity_rec.amount_advance_collected,0),
1373 nvl(l_activity_rec.amount_advance_applied,0),
1374 nvl(l_activity_rec.amount_advance_balance,0),
1375 nvl(l_activity_rec.amount_earned,0),
1376 nvl(l_activity_rec.amount_billed,0),
1377 nvl(l_activity_rec.amount_receivable_collected,0),
1378 nvl(l_activity_rec.amount_receivable_balance,0),
1379 nvl(l_activity_rec.amount_agreement,0),
1380 fnd_global.conc_request_id,
1381 fnd_global.user_id,
1382 fnd_global.login_id,
1383 fnd_global.user_id,
1384 SYSDATE,
1385 SYSDATE,
1386 l_activity_rec.segment1,
1387 l_activity_rec.segment2,
1388 l_activity_rec.segment3,
1389 l_activity_rec.segment4,
1390 l_activity_rec.segment5,
1391 l_activity_rec.segment6,
1392 l_activity_rec.segment7,
1393 l_activity_rec.segment8,
1394 l_activity_rec.segment9,
1395 l_activity_rec.segment10,
1396 l_activity_rec.segment11,
1397 l_activity_rec.segment12,
1398 l_activity_rec.segment13,
1399 l_activity_rec.segment14,
1400 l_activity_rec.segment15,
1401 l_activity_rec.segment16,
1402 l_activity_rec.segment17,
1403 l_activity_rec.segment18,
1404 l_activity_rec.segment19,
1405 l_activity_rec.segment20,
1406 l_activity_rec.segment21,
1407 l_activity_rec.segment22,
1408 l_activity_rec.segment23,
1409 l_activity_rec.segment24,
1410 l_activity_rec.segment25,
1411 l_activity_rec.segment26,
1412 l_activity_rec.segment27,
1413 l_activity_rec.segment28,
1414 l_activity_rec.segment29,
1415 l_activity_rec.segment30,
1416 'N'
1417 );
1418
1419 END LOOP;
1420
1421
1422
1423 END populate_amount;
1424 --------------------------------------------------------------------------
1425 --This procedure checks if there is a range of Agreement Numbers provided
1426 --in the Accounting Flexfield Low and High parameters.
1427 --If the range is not provided then we fetch all the values from the
1428 --Reimbursable Agreement value Set and check if the transaction exists with
1429 --that Transaction number and class of 'Guarantee' in the ra_customer_trx
1430 --This procedure will form the sql statement to fetch the agreement numbers
1431 ---------------------------------------------------------------------------
1432
1433 PROCEDURE get_agreement_range IS
1434
1435 CURSOR flex_reimb_value_id_cur IS
1436 SELECT flex_value_set_id
1437 FROM fnd_id_flex_segments
1438 WHERE application_id = 101
1439 AND application_column_name =g_reimb_agreement_segment
1440 AND id_flex_code = g_id_flex_code
1441 AND id_flex_num = g_coa_id
1442 AND enabled_flag = 'Y' ;
1443
1444 CURSOR reimb_segment_num_cur (p_application_column_name VARCHAR2) IS
1445 SELECT segment_num
1446 FROM Fnd_Id_Flex_Segments
1447 WHERE application_id = g_apps_id
1448 AND id_flex_code = g_id_flex_code
1449 AND id_flex_num = g_coa_id
1450 AND application_column_name=p_application_column_name ;
1451
1452 l_module VARCHAR2(500) := g_module_name||'.get_agreemnt_range';
1453 l_low_reimb VARCHAR2(50);
1454 l_high_reimb VARCHAR2(50);
1455 l_reim_agreement_segment_num NUMBER;
1456
1457 BEGIN
1458
1459 --Get the value_set_id for the reimbursable Agreement
1460 OPEN flex_reimb_value_id_cur ;
1461 FETCH flex_reimb_value_id_cur
1462 INTO g_reimb_flex_value_id ;
1463 CLOSE flex_reimb_value_id_cur ;
1464 log(C_STATE_LEVEL, l_module, ' Reimb agree val set id: '||g_reimb_flex_value_id);
1465
1466
1467 --Get the Segment Number for the reimbursable Agreement Segment
1468 OPEN reimb_segment_num_cur(g_reimb_agreement_segment);
1469 FETCH reimb_segment_num_cur INTO l_reim_agreement_segment_num;
1470 CLOSE reimb_segment_num_cur;
1471
1472 IF g_nsegs_low >1 AND gt_seg_low(l_reim_agreement_segment_num) IS NOT NULL THEN
1473 l_low_reimb := gt_seg_low(l_reim_agreement_segment_num);
1474 l_high_reimb:= gt_seg_high(l_reim_agreement_segment_num);
1475
1476 log(C_STATE_LEVEL, l_module, ' l_low_reimb: ' ||l_low_reimb);
1477 log(C_STATE_LEVEL, l_module, ' l_high_reimb ' ||l_high_reimb);
1478
1479 g_agree_sql:='SELECT f.flex_value
1480 FROM fnd_flex_values_vl f
1481 where flex_value_set_id = :g_flex_reimb_value_id
1482 AND flex_value BETWEEN '||''''||l_low_reimb||''''||' AND '||''''||l_high_reimb||''''||'
1483 AND f.flex_value in (SELECT r.trx_number
1484 FROM ra_customer_trx_all r,
1485 ra_cust_trx_types_all t
1486 WHERE r.set_of_books_id = :g_ledger_id
1487 AND r.invoice_currency_code = :g_currency
1488 AND r.cust_trx_type_id = t.cust_trx_type_id
1489 AND t.type = ''GUAR'' )';
1490
1491 log(C_STATE_LEVEL, l_module, 'User has given reimbursable agreement range as parameters for this process. ');
1492 ELSE
1493
1494 g_agree_sql:= 'SELECT f.flex_value
1495 FROM fnd_flex_values_vl f
1496 where flex_value_set_id = :g_flex_reimb_value_id
1497 AND f.flex_value in(SELECT r.trx_number
1498 FROM ra_customer_trx_all r,
1499 ra_cust_trx_types_all t
1500 WHERE r.set_of_books_id = :g_ledger_id
1501 AND r.invoice_currency_code = :g_currency
1502 AND r.cust_trx_type_id = t.cust_trx_type_id
1503 AND t.type = ''GUAR'' )';
1504
1505 log(C_STATE_LEVEL, l_module, 'User has not given reimbursable agreement range as parameters for this process.all values in value set ') ;
1506 log(C_STATE_LEVEL, l_module, 'Will be picked which exist as transactions with class guarantee ') ;
1507 END IF ;
1508
1509 log(C_STATE_LEVEL, l_module, 'g_agree_sql: '||g_agree_sql);
1510
1511 END get_agreement_range;
1512
1513 -- Constructs the main sql query depending on the status of
1514 -- Receivables SLA Detail check box. If it is not checked the query
1515 -- is constructed based on gl_balances and gl_bc_packets tables.
1516 -- Else the the query is constructed based on xla_ae_lines table.
1517
1518
1519 PROCEDURE create_main_query(p_rec_sla_detail IN VARCHAR2, p_select_mod IN VARCHAR2,
1520 p_column_id IN NUMBER, p_main_sql OUT NOCOPY VARCHAR2) IS
1521
1522
1523 l_bal_amount_sql VARCHAR2(1000);
1524 l_bc_amount_sql VARCHAR2(1000);
1525 l_glbal_sql VARCHAR2(6000);
1526 l_glbc_sql VARCHAR2(6000);
1527 l_sla_amount_sql VARCHAR2(1000);
1528 l_main_sql VARCHAR2(25000);
1529 l_module VARCHAR2(500) := g_module_name||'.create_main_query';
1530
1531 BEGIN
1532
1533 IF (p_rec_sla_detail = 'N') THEN
1534 log(C_STATE_LEVEL, l_module, 'p_rec_sla_detail is Not checked: '||p_rec_sla_detail);
1535
1536 l_bal_amount_sql:=' SUM(nvl(glb.period_net_dr,0) - nvl(glb.period_net_cr,0)) amount ' ;
1537
1538 l_glbal_sql := 'SELECT '||g_select||' , '||l_bal_amount_sql||'
1539 FROM gl_balances glb,
1540 gl_code_combinations_kfv glcc,
1541 fv_reimb_definitions_lines frd,
1542 fv_reimb_def_acct_assign fva
1543 WHERE
1544 frd.column_id = fva.column_id
1545 and glb.actual_flag =''A''
1546 --and glb.period_year = :g_period_year
1547 --and glb.period_num = :g_period_num
1548 and glb.ledger_id = :g_ledger_id
1549 AND glb.template_id IS NULL
1550 AND glb.currency_code = ''USD''
1551 AND glb.code_combination_id = glcc.code_combination_id
1552 AND ( glcc.'||g_gl_nat_acc_segment ||
1553 ' BETWEEN '|| ' fva.account_from ' ||
1554 ' AND '|| ' fva.account_to OR EXISTS '||
1555 ' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
1556 ' WHERE glcc.'||g_gl_nat_acc_segment ||' BETWEEN' ||
1557 ' child_flex_value_low AND child_flex_value_high '||
1558 ' AND h.flex_value_set_id = :g_ussgl_flex_value_set_id' ||
1559 ' AND h.PARENT_FLEX_VALUE BETWEEN fva.account_from '||
1560 ' AND fva.account_to ))
1561 AND frd.set_of_books_id= :g_ledger_id
1562 AND frd.set_of_books_id =fva.set_of_books_id
1563 AND glcc.chart_of_accounts_id = :g_coa_id
1564 AND glcc.' || g_reimb_agreement_segment ||' = :l_agree_num
1565 and frd.column_id = :l_column_id'||
1566 g_where ||' GROUP BY '||g_select;
1567
1568 log(C_STATE_LEVEL, l_module, 'l_glbal_sql: '|| l_glbal_sql);
1569
1570 l_bc_amount_sql := ' SUM (Nvl(glbc.accounted_dr,0) - nvl(glbc.accounted_cr,0) ) amount';
1571
1572 l_glbc_sql:= 'SELECT '||g_select||' , '||l_bc_amount_sql||'
1573 FROM gl_bc_packets glbc,
1574 gl_code_combinations_kfv glcc,
1575 fv_reimb_definitions_lines frd,
1576 fv_reimb_def_acct_assign fva
1577 WHERE
1578 frd.column_id = fva.column_id
1579 and glbc.actual_flag =''A''
1580 --and glbc.period_year = :g_period_year
1581 --and glbc.period_num = :g_period_num
1582 and glbc.ledger_id = :g_ledger_id
1583 AND glbc.template_id IS NULL
1584 AND glbc.status_code = ''A''
1585 AND glbc.currency_code = ''USD''
1586 AND glbc.code_combination_id = glcc.code_combination_id
1587 AND ( glcc.'||g_gl_nat_acc_segment ||
1588 ' BETWEEN '|| ' fva.account_from ' ||
1589 ' AND '|| ' fva.account_to OR EXISTS '||
1590 ' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
1591 ' WHERE glcc.'||g_gl_nat_acc_segment ||' BETWEEN '||
1592 ' child_flex_value_low AND child_flex_value_high '||
1593 ' AND h.flex_value_set_id = :g_ussgl_flex_value_set_id' ||
1594 ' AND h.PARENT_FLEX_VALUE BETWEEN fva.account_from '||
1595 ' AND fva.account_to ))
1596 AND frd.set_of_books_id= :g_ledger_id
1597 AND frd.set_of_books_id =fva.set_of_books_id
1598 and glcc.chart_of_accounts_id = :g_coa_id
1599 AND glcc.' || g_reimb_agreement_segment ||' = :l_agree_num
1600 and frd.column_id = :l_column_id'||
1601 g_where ||' GROUP BY '||g_select;
1602
1603 log(C_STATE_LEVEL, l_module, 'l_glbc_sql: '|| l_glbc_sql);
1604
1605 l_main_sql := 'SELECT '||p_select_mod||' , SUM(amount) net_amount FROM ( '
1606 || l_glbal_sql ||' UNION ALL ' || l_glbc_sql || ' ) GROUP BY ' ||p_select_mod;
1607
1608 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,1,1000));
1609 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,1001,1000));
1610 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,2001,1000));
1611 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,3001,1000));
1612 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,4001,1000));
1613 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,5001,1000));
1614 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,6001,1000));
1615 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,7001,1000));
1616 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,8001,1000));
1617 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,9001,1000));
1618 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,10001,1000));
1619 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,11001,1000));
1620 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,12001,1000));
1621 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,13001,1000));
1622 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,14001,1000));
1623 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,15001,1000));
1624 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,16001,1000));
1625 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,17001,1000));
1626 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,18001,1000));
1627 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,19001,1000));
1628 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,20001,1000));
1629 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,21001,1000));
1630 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,22001,1000));
1631 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,23001,1000));
1632 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| substr(l_main_sql,24001,1000));
1633
1634 ELSE
1635 --Modified for bug 8849465
1636 l_sla_amount_sql := 'DECODE (fva.journal_side,
1637 ''Debit'',
1638 SUM(NVL(ACCOUNTED_DR,0)),
1639 ''Credit'',
1640 (SUM(NVL(ACCOUNTED_CR,0)) * -1),
1641 SUM (NVL(ACCOUNTED_DR,0)- NVL(ACCOUNTED_CR,0))
1642 ) amount ' ;
1643
1644 l_main_sql :=
1645 'SELECT '||g_select||' , '||l_sla_amount_sql||'
1646 FROM gl_code_combinations_kfv glcc,
1647 fv_reimb_definitions_lines frd,
1648 fv_reimb_def_acct_assign fva,
1649 xla_ae_headers xah,
1650 xla_ae_lines xal
1651 WHERE
1652 frd.column_id = fva.column_id
1653 AND xah.ACCOUNTING_ENTRY_STATUS_CODE = ''F''
1654 --AND xah.period_name = :g_period_name
1655 AND xal.ledger_id = :g_ledger_id
1656 AND xal.CURRENCY_CODE = ''USD''
1657 AND xal.ae_header_id = xah.ae_header_id
1658 AND xal.code_combination_id = glcc.code_combination_id
1659 AND xal.application_id = xah.application_id
1660 AND xal.ledger_id = xah.ledger_id
1661 AND ( glcc.'||g_gl_nat_acc_segment ||
1662 ' BETWEEN '|| ' fva.account_from ' ||
1663 ' AND '|| ' fva.account_to OR EXISTS '||
1664 ' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
1665 ' WHERE glcc.'||g_gl_nat_acc_segment
1666 ||' BETWEEN '|| ' child_flex_value_low AND child_flex_value_high '||
1667 ' AND h.flex_value_set_id = :g_ussgl_flex_value_set_id' ||
1668 ' AND h.PARENT_FLEX_VALUE BETWEEN fva.account_from '
1669 || ' AND fva.account_to ))
1670 AND frd.set_of_books_id= :g_ledger_id
1671 AND frd.set_of_books_id =fva.set_of_books_id
1672 and glcc.chart_of_accounts_id = :g_coa_id
1673 AND glcc.' || g_reimb_agreement_segment ||' = :l_agree_num
1674 and frd.column_id = :l_column_id'
1675 ||g_where ||' GROUP BY fva.journal_side , '||g_select;
1676
1677 log(C_STATE_LEVEL, l_module, 'l_main_sql: '|| l_main_sql);
1678
1679
1680
1681 END IF;
1682 p_main_sql := l_main_sql ;
1683
1684
1685 END create_main_query;
1686
1687
1688
1689 BEGIN
1690 g_apps_id := 101;
1691 g_id_flex_code := 'GL#';
1692
1693 END fv_reimb_activity_process;