1 PACKAGE BODY PSP_AUTOPOP AS
2 /* $Header: PSPAUTOB.pls 120.3.12000000.4 2007/03/16 11:47:52 tbalacha noship $ */
3 g_error_api_path VARCHAR2(2000) := '';
4
5 TYPE lookup_recTyp IS RECORD(
6 data_type varchar2(20),
7 charres varchar2(80),
8 numres number,
9 dateres date);
10 TYPE lookup_array_type is TABLE of lookup_recTyp
11 INDEX by BINARY_INTEGER;
12 lookup_array lookup_array_type;
13
14 ---------- P R O C E D U R E: DynamicQuery ---------------------------------------
15 --
16 --
17 -- Purpose:
18 -- Author : Jason T. McKnight
19 --
20 ----------------------------------------------------------------------------------
21
22 PROCEDURE DynamicQuery(
23 p_person_id IN NUMBER,
24 p_assignment_id IN NUMBER,
25 p_element_type_id IN NUMBER,
26 p_project_id IN NUMBER,
27 p_expenditure_organization_id IN NUMBER,
28 p_task_id IN NUMBER,
29 p_award_id IN NUMBER,
30 p_payroll_date IN DATE,
31 p_expenditure_type IN VARCHAR2,
32 p_set_of_books_id In NUMBER,
33 p_business_group_id IN NUMBER,
34 p_gl_code_combination_id IN NUMBER,
35 p_return_status OUT NOCOPY VARCHAR2
36 ) IS
37
38
39 v_CursorID INTEGER;
40 v_Dummy INTEGER;
41 v_ret_varchar VARCHAR2(80) := null;
42 v_ret_number NUMBER := null;
43 v_ret_date DATE := null;
44 l_no_rows BOOLEAN:=FALSE;
45
46 l_lookup_id number;
47 l_data_type varchar2(20);
48 l_dyn_sql_stmt VARCHAR2(1000);
49 l_date_tracked BOOLEAN;
50 l_parameter VARCHAR2(80);
51
52 p_bind_var number;
53 l_bind_var varchar2(80);
54
55
56 NO_ROWS_FOUND EXCEPTION;
57
58 CURSOR LOOKUPS_C IS
59 SELECT b.lookup_id, b.parameter,b.datatype,
60 b.dyn_sql_stmt, b.bind_var
61 FROM psp_auto_lookups b
62 where business_group_id=p_business_group_id
63 and set_of_books_id=p_set_of_books_id
64 order by lookup_id;
65
66
67 BEGIN
68 -- Open the cursor for processing.
69 OPEN LOOKUPS_C;
70 loop
71
72 /* reinitialized each time */
73
74 v_ret_varchar := null;
75 v_ret_number := null;
76 v_ret_date := null;
77
78 FETCH LOOKUPS_C INTO l_lookup_id,l_parameter,l_data_type,l_dyn_sql_stmt, l_bind_var;
79 EXIT WHEN LOOKUPS_C%NOTFOUND;
80 IF l_parameter='Expenditure Type' then
81 v_ret_varchar:=p_expenditure_type;
82 ELSE
83
84
85 v_CursorID := DBMS_SQL.OPEN_CURSOR;
86 -- --dbms_output.put_line('After open in DQ');
87
88 IF l_bind_var = 'l_person_id' THEN
89 p_bind_var := p_person_id;
90 l_date_tracked := TRUE;
91 ELSIF l_bind_var = 'l_assignment_id' THEN
92 p_bind_var := p_assignment_id;
93 l_date_tracked := TRUE;
94 ELSIF l_bind_var = 'l_element_type_id' THEN
95 p_bind_var := p_element_type_id;
96 -- l_date_tracked := FALSE;
97 l_date_tracked := TRUE; /* added date tracking of element type id */
98 ELSIF l_bind_var = 'l_project_id' THEN
99 p_bind_var := p_project_id;
100 l_date_tracked := FALSE;
101 ELSIF l_bind_var = 'l_expenditure_organization_id' THEN
102 p_bind_var := p_expenditure_organization_id;
103 l_date_tracked := FALSE;
104 ELSIF l_bind_var = 'l_task_id' THEN
105 p_bind_var := p_task_id;
106 l_date_tracked := FALSE;
107 ELSIF l_bind_var = 'l_award_id' THEN
108 p_bind_var := p_award_id;
109 l_date_tracked := FALSE;
110 ELSIF l_bind_var = 'l_glcc_id' THEN
111 p_bind_var := p_gl_code_combination_id;
112 l_date_tracked := FALSE;
113 END IF;
114
115 -- Parse the query.
116 ----dbms_output.put_line('Before parse statement');
117 dbms_sql.parse(v_CursorID, l_dyn_sql_stmt, DBMS_SQL.V7);
118 ----dbms_output.put_line('After parse statement');
119
120 -- Bind the input variable.
121 --v_CursorId:=v_cursor(l_lookup_id);
122 if l_date_tracked then
123 dbms_sql.bind_variable(v_CursorID, ':VAR1', p_bind_var);
124 dbms_sql.bind_variable(v_CursorID, ':EFFDATE', p_payroll_date);
125 else
126 dbms_sql.bind_variable(v_CursorID, ':VAR1', p_bind_var);
127 end if;
128
129 -- Define the output variable depending on datatype.
130 if l_data_type = 'VARCHAR2' then
131 dbms_sql.define_column(v_CursorID, 1, v_ret_varchar, 500);
132 elsif l_data_type = 'NUMBER' then
133 dbms_sql.define_column(v_CursorID, 1, v_ret_number);
134 elsif l_data_type = 'DATE' then
135 dbms_sql.define_column(v_CursorID, 1, v_ret_date);
136 end if;
137
138 -- Execute the statement. We don't care about the return
139 -- value, but we do need to declare a variable for it.
140 v_Dummy := dbms_sql.execute(v_CursorID);
141 ----dbms_output.put_line('After execute in DQ');
142
143 -- Fetch the row into the buffer. We only expect one row and
144 -- will not loop through to get multiple rows.
145 IF dbms_sql.fetch_rows(v_CursorID) = 0 THEN
146 BEGIN
147 RAISE NO_ROWS_FOUND;
148 EXCEPTION
149 WHEN NO_ROWS_FOUND THEN
150 dbms_sql.close_cursor(v_CursorID);
151 l_no_rows:=TRUE;
152 END;
153
154 /* Added for Exception Handling if no rows are returned . */
155
156 ----dbms_output.put_line('fetch returned no values in DQ');
157
158 -- insert into psp_autotemp values(l_lookup_id,l_data_type,v_ret_varchar,v_ret_number,v_ret_date,NULL);
159
160 -- RAISE NO_ROWS_FOUND;
161 ELSE
162 -- Retrieve the rows from the buffer into PL/SQL variables.
163 -- The correct call depends on the datatype.
164 IF l_data_type = 'VARCHAR2' THEN
165 dbms_sql.column_value(v_CursorID, 1, v_ret_varchar);
166 ELSIF l_data_type = 'NUMBER' THEN
167 dbms_sql.column_value(v_CursorID, 1, v_ret_number);
168 ELSIF l_data_type = 'DATE' THEN
169 dbms_sql.column_value(v_CursorID, 1, v_ret_date);
170 END IF;
171 dbms_sql.close_cursor(v_CursorID);
172
173
174 END IF;
175
176 END IF;
177 /* insert into psp_autotemp values(p_runid,l_lookup_id,l_data_type,v_ret_varchar,v_ret_number,v_ret_date
178 );
179 */
180 -- IF NOT(l_no_rows) THEN
181 lookup_array(l_lookup_id).data_type:=l_data_type;
182 lookup_array(l_lookup_id).charres:=v_ret_varchar;
183 lookup_array(l_lookup_id).numres:=v_ret_number;
184 lookup_array(l_lookup_id).dateres:=v_ret_date;
185 -- END IF;
186 ----dbms_output.put_line('after insert into temp');
187
188
189 -- Close the cursor.
190
191 END LOOP;
192 close lookups_c;
193 p_return_status := fnd_api.g_ret_sts_success;
194
195 EXCEPTION
196 -- WHEN NO_ROWS_FOUND THEN
197 -- p_return_status := fnd_api.g_ret_sts_success;
198 WHEN OTHERS THEN
199 -- Close the cursor.
200 dbms_sql.close_cursor(v_CursorID);
201 close lookups_c;
202 g_error_api_path := 'DynamicQuery:WHEN OTHERS:'||g_error_api_path;
203 p_return_status := fnd_api.g_ret_sts_unexp_error;
204 END DynamicQuery;
205
206
207 /********************************************************************************
208 New procedure added for bug fix for resolve_rules along with autopop performance optimization patch
209 Bug 2023955
210 ********************************************************************************/
211
212 FUNCTION resolve_rules_new(x_string IN VARCHAR2) RETURN BOOLEAN is
213
214
215 v_CursorID INTEGER;
216 v_Dummy INTEGER;
217 v_ret_Number number:=0;
218 v_return_value BOOLEAN;
219 new_sql_string varchar2(1000);
220 BEGIN
221
222 v_CursorID := DBMS_SQL.OPEN_CURSOR;
223 new_sql_string :='SELECT 1 from dual where '||x_string;
224 dbms_sql.parse(v_CursorID, new_sql_string, DBMS_SQL.V7);
225 dbms_sql.define_column(v_CursorID, 1, v_ret_number);
226 v_Dummy := dbms_sql.execute(v_CursorID);
227
228 IF dbms_sql.fetch_rows(v_CursorID) = 0 THEN
229 v_return_value := FALSE;
230 ELSE
231 v_return_value := TRUE ;
232 END IF;
233
234 dbms_sql.close_cursor(v_CursorID);
235 return v_return_value;
236 EXCEPTION when others then
237
238 /* when rule is invalid */
239
240 v_return_value:=FALSE;
241 return v_return_value;
242
243 end resolve_rules_new;
244
245
246 /**************************************************************************
247
248
249 This function has been obsoleted and replaced by the resolve_ruleS_new function above
250 ---------- F U N C T I O N: resolve_rules ---------------------------------------
251 --
252 --
253 -- Purpose:
254 -- Author : Chandra Kalyana
255 --
256 ----------------------------------------------------------------------------------
257
258 FUNCTION resolve_rules (x_string IN VARCHAR2)
259 RETURN BOOLEAN IS
260
261 v_return_value BOOLEAN;
262 str_len BINARY_INTEGER;
263 new_str_len BINARY_INTEGER;
264 op_counter BINARY_INTEGER :=0;
265 str_pos BINARY_INTEGER :=1;
266 left_paren_pos BINARY_INTEGER;
267 right_paren_pos BINARY_INTEGER;
268
269 new_string VARCHAR2(1000);
270 original_string VARCHAR2(1000);
271 result_string VARCHAR2(100);
272
273 BEGIN
274
275 --dbms_output.put_line ('resolve rules string '||x_string);
276
277 original_string := x_string ;
278 str_len := length(original_string) ;
279 str_pos := 1 ;
280
281 -- Checking for NOT operator followed by a TRUE or FALSE, and change the string with
282 -- the appropriate boolean value
283
284 while str_pos <= str_len LOOP
285 if substr(original_string,str_pos,3) = 'NOT'
286 then
287 if substr(original_string,str_pos+4,1) = 'T'
288 then
289 original_string := replace(original_string,'NOT TRUE','FALSE') ;
290 elsif substr(original_string,str_pos+4,1) = 'F'
291 then
292 original_string := replace(original_string,'NOT FALSE','TRUE') ;
293 end if;
294
295 end if;
296 str_pos := str_pos + 1 ;
297 end LOOP;
298
299 -- Looping through the entire string passed till final boolean value is obtained
300
301 while TRUE loop
302
303 str_pos := 1 ;
304 str_len := length(original_string) ;
305 left_paren_pos := 0 ;
306 right_paren_pos := 0;
307
308
309 -- Checking for last occurence of left parenthesis
310
311 str_pos := 1 ;
312
313 while str_pos <= str_len LOOP
314
315 if substr(original_string,str_pos,1) = '('
316 then
317 left_paren_pos := str_pos ;
318 end if;
319 str_pos := str_pos + 1;
320 end loop;
321
322 --dbms_output.put_line('left( '||left_paren_pos);
323
324
325 -- Initialising starting position
326
327 str_pos := left_paren_pos ;
328
329 -- Checking for first occurence of right parenthesis
330
331 while str_pos <= str_len LOOP
332
333 if substr(original_string,str_pos,1) = ')'
334 then
335 right_paren_pos := str_pos ;
336 exit ;
337 end if;
338 str_pos := str_pos + 1;
339
340 end loop;
341
342 --dbms_output.put_line('right) '||right_paren_pos);
343
344 -- Read the string between the parenthesis
345
346 if left_paren_pos <> 0 AND right_paren_pos <> 0
347 then
348 if right_paren_pos - left_paren_pos = 6
349 then
350 new_string := replace(original_string,'(FALSE)','FALSE');
351 original_string := new_string;
352 elsif right_paren_pos - left_paren_pos = 5
353 then
354 new_string := replace(original_string,'(TRUE)','TRUE');
355 original_string := new_string;
356 else
357 new_string := substr(original_string,left_paren_pos+1,right_paren_pos-left_paren_pos-1);
358 end if;
359 else
360 new_string := original_string ;
361 end if;
362
363
364 --dbms_output.put_line('new_string '||new_string);
365
366 -- To find length of the new string
367
368 new_str_len := length(new_string);
369
370 -- Initialising starting position
371
372
373 str_pos := 1 ;
374 -- Checking for NOT operator followed by a TRUE or FALSE,
375 -- and change the string with the appropriate boolean value
376
377 while str_pos <= str_len LOOP
378 if substr(original_string,str_pos,3) = 'NOT'
379 then
380 if substr(original_string,str_pos+4,1) = 'T'
381 then
382 original_string := replace(original_string,'NOT TRUE','FALSE') ;
383 elsif substr(original_string,str_pos+4,1) = 'F'
384 then
385 original_string := replace(original_string,'NOT FALSE','TRUE') ;
386 end if;
387
388 end if;
389 str_pos := str_pos + 1 ;
390 end LOOP;
391
392
393 -- Checking for the number of OR and AND operators in the new string
394
395 -- Initialising starting position
396
397 str_pos := 1 ;
398
399 new_str_len := length(new_string);
400
401 while str_pos <= new_str_len LOOP
402 if substr(new_string,str_pos,3) = 'AND' or substr(new_string,str_pos,2) = 'OR'
403 then
404 op_counter := op_counter + 1;
405
406 end if;
407 str_pos := str_pos + 1 ;
408 end loop;
409 --dbms_output.put_line('counter '||op_counter);
410 -- If there is only one operand, then we can now resolve this string
411
412 if op_counter = 0
413 then
414 result_string := new_string ;
415 end if;
416
417 if op_counter = 1
418 then
419 if new_string = 'TRUE AND TRUE'
420 then
421 result_string := 'TRUE' ;
422 elsif new_string = 'TRUE OR TRUE'
423 then
424 result_string := 'TRUE' ;
425 elsif new_string = 'TRUE OR FALSE'
426 then
427 result_string := 'TRUE' ;
428 elsif new_string = 'FALSE OR TRUE'
429 then
430 result_string := 'TRUE' ;
431 elsif new_string = 'FALSE OR FALSE'
432 then
433 result_string := 'FALSE' ;
434 elsif new_string = 'FALSE AND FALSE'
435 then
436 result_string := 'FALSE' ;
437 elsif new_string = 'TRUE AND FALSE'
438 then
439 result_string := 'FALSE' ;
440 elsif new_string = 'FALSE AND TRUE'
441 then
442 result_string := 'FALSE' ;
443 end if;
444
445 elsif op_counter > 1
446 -- we have to break this string into smaller boolen expressions
447 then
448 str_pos := 1 ;
449
450 while str_pos <= new_str_len LOOP
451 --dbms_output.put_line('str_pos '||str_pos);
452 if substr(new_string,str_pos,3) = 'AND'
453 then
454
455 if substr(new_string,str_pos+4,1) = 'T'
456 then
457 new_string := substr(new_string,1,str_pos+7);
458
459 elsif substr(new_string,str_pos+4,1) = 'F'
460 then
461 new_string := substr(new_string,1,str_pos+8);
462
463 end if;
464 exit;
465 end if;
466 str_pos := str_pos + 1 ;
467 end loop;
468
469 str_pos := 1 ;
470
471 while str_pos <= new_str_len LOOP
472
473 if substr(new_string,str_pos,2) = 'OR'
474
475 then
476 if substr(new_string,str_pos+3,1) = 'T'
477 then
478 new_string := substr(new_string,1,str_pos+6);
479 --dbms_output.put_line('string1 '||new_string);
480 elsif substr(new_string,str_pos+3,1) = 'F'
481 then
482 new_string := substr(new_string,1,str_pos+7);
483 --dbms_output.put_line('string2 '||new_string);
484 end if;
485 exit;
486 end if;
487 str_pos := str_pos + 1 ;
488 end loop;
489
490 if new_string = 'TRUE AND TRUE'
491 then
492 result_string := 'TRUE' ;
493 elsif new_string = 'TRUE OR TRUE'
494 then
495 result_string := 'TRUE' ;
496 elsif new_string = 'TRUE OR FALSE'
497 then
498 result_string := 'TRUE' ;
499 elsif new_string = 'FALSE OR TRUE'
500 then
501 result_string := 'TRUE' ;
502 elsif new_string = 'FALSE OR FALSE'
503 then
504 result_string := 'FALSE' ;
505 elsif new_string = 'FALSE AND FALSE'
506 then
507 result_string := 'FALSE' ;
508 elsif new_string = 'TRUE AND FALSE'
509 then
510 result_string := 'FALSE' ;
511 elsif new_string = 'FALSE AND TRUE'
512 then
513 result_string := 'FALSE' ;
514 end if;
515
516 end if ;
517
518 --dbms_output.put_line('new string '||new_string);
519 --dbms_output.put_line('result string '||result_string);
520 --dbms_output.put_line('original string '||original_string);
521
522 if result_string is not null
523 then
524 original_string := replace(original_string,new_string,result_string);
525 end if;
526
527 --dbms_output.put_line('original string '||original_string);
528 if original_string = 'TRUE' or original_string = 'FALSE'
529 then
530 exit;
531 else
532 op_counter := 0 ;
533 result_string := null ;
534 end if;
535
536 end loop;
537
538 ----dbms_output.put_line('final result '||original_string);
539
540
541 if original_string = 'TRUE'
542 then
543 v_return_value := TRUE ;
544 elsif original_string = 'FALSE'
545 then
546 v_return_value := FALSE ;
547 end if;
548
549 RETURN v_return_value;
550
551 end resolve_rules ;
552
553 */
554
555
556 /*----------P R O C E D U R E: MAIN ------------------------------------------------
557 --
558 --
559 -- Purpose:
560 -- Author : Jason T. McKnight
561 --
562 -- Subha Ramachandran 03-Feb-2000 Changes made for Multiorg Implementation
563 -- added SOB and BG in the parameter class
564 -- V.V.Lavanya 27-AUG-2001 Added for the Enhancement Natural By Pass Account
565 -- For Bug : 1907209
566 ----------------------------------------------------------------------------------*/
567
568 PROCEDURE main(
569 p_acct_type IN VARCHAR2,
570 p_person_id IN NUMBER,
571 p_assignment_id IN NUMBER,
572 p_element_type_id IN NUMBER,
573 p_project_id IN NUMBER,
574 p_expenditure_organization_id IN NUMBER,
575 p_task_id IN NUMBER,
576 p_award_id IN NUMBER,
577 p_expenditure_type IN VARCHAR2,
578 p_gl_code_combination_id IN NUMBER,
579 p_payroll_date IN DATE,
580 p_set_of_books_id IN NUMBER,
581 p_business_group_id IN NUMBER,
582 ret_expenditure_type OUT NOCOPY VARCHAR2,
583 ret_gl_code_combination_id OUT NOCOPY NUMBER,
584 retcode OUT NOCOPY VARCHAR2) IS
585 /**************************************************************************************************
586
587 Segment Number will now be picked from PSP_AUTO_SEGMENTS
588
589 l_segment_num NUMBER(3):= TO_NUMBER(FND_PROFILE.VALUE('PSP_AUTOP_SEG_NUM'));
590
591 ***************************************************************************************************/
592 l_segment_num NUMBER(3);
593 l_seg_no NUMBER(2);
594 -- For Bug 1907209 : Moved the variables up : Natural Bypass Account Enhancement -lveerubh
595 nsegs NUMBER;
596 cat_segs VARCHAR2(2000);
597 segs FND_FLEX_EXT.SegmentArray;
598 ccid_exists BOOLEAN;
599 combo_valid BOOLEAN;
600 new_gl_ccid NUMBER;
601
602
603 CURSOR exp_accts_c(p_period_type in VARCHAR2) IS
604 SELECT acct_id, expenditure_type,
605 acct_seq_num --added for debug purposes
606 FROM psp_auto_accts a
607 WHERE acct_type = 'E'
608 AND period_type = p_period_type
609 AND p_payroll_date BETWEEN start_date_active AND NVL(end_date_active, p_payroll_date)
610 AND business_group_id=p_business_group_id
611 and set_of_books_id=p_set_of_books_id
612 AND EXISTS
613 (SELECT '1' from psp_auto_rules where acct_id=a.acct_id)
614 ORDER BY acct_seq_num;
615
616 CURSOR na_accts_c(p_period_type in VARCHAR2) IS
617 SELECT acct_id,segment_num, natural_account,
618 acct_seq_num --added for debug purposes
619 FROM psp_auto_accts a
620 WHERE acct_type = 'N'
621 AND period_type = p_period_type
622 AND segment_num = l_segment_num
623 and business_group_id=p_business_group_id
624 and set_of_books_id=p_set_of_books_id
625 AND p_payroll_date BETWEEN start_date_active AND NVL(end_date_active, p_payroll_date)
626 AND EXISTS
627 (SELECT '1' from psp_auto_rules where acct_id=a.acct_id)
628 ORDER BY acct_seq_num;
629
630 CURSOR params_c(p_acct_id IN NUMBER) IS
631 SELECT a.param_line_num, a.lookup_id,
632 a.operand, a.user_value
633 FROM psp_auto_params a
634 WHERE a.acct_id = p_acct_id
635 and exists
636 (select lookup_id from psp_auto_lookups where
637 lookup_id=a.lookup_id);
638
639 CURSOR rules_c(p_acct_id IN NUMBER) IS
640 SELECT calculator_rule
641 FROM psp_auto_rules
642 WHERE acct_id = p_acct_id;
643
644 CURSOR by_pass_c IS
645 SELECT expenditure_type
646 FROM psp_auto_bypass
647 WHERE expenditure_type = p_expenditure_type
648 and set_of_books_id=p_set_of_books_id
649 and business_group_id=p_business_group_id;
650
651 --For Bug 1907209 : Natural By Pass Account Enhancement
652 --Added new cursor to obtain the bypass account.
653 CURSOR by_pass_na_cur
654 IS
655 SELECT panb.natural_account
656 FROM psp_auto_na_bypass panb
657 WHERE panb.natural_account = segs(l_seg_no)
658 AND panb.segment_num = l_segment_num
659 AND panb.set_of_books_id = p_set_of_books_id
660 And panb.business_group_id = p_business_group_id;
661
662
663 CURSOR period_type_c IS
664 SELECT distinct(ppf.period_type)
665 FROM per_all_assignments_f paf,
666 pay_all_payrolls_f ppf
667 WHERE paf.assignment_id = p_assignment_id
668 AND p_payroll_date BETWEEN paf.effective_start_date AND NVL(paf.effective_end_date,p_payroll_date)
669 AND paf.payroll_id = ppf.payroll_id;
670
671 /* Bug Fix 5439154: Support for non Consecutive GL Segments*/
672 CURSOR Segment_number_csr IS
673 SELECT SEGMENT_NUM
674 FROM fnd_id_flex_segments fifs,
675 gl_sets_of_books gsob,
676 fnd_application fa
677 WHERE gsob.set_of_books_id = p_set_of_books_id
678 AND fifs.id_flex_num = gsob.chart_of_accounts_id
679 AND fifs.id_flex_code = 'GL#'
680 AND enabled_flag = 'Y'
681 AND fifs.application_id = fa.application_id
682 AND fa.application_short_name = 'SQLGL'
683 ORDER BY SEGMENT_NUM ASC;
684
685 l_segment_number NUMBER(3);
686 l_segment_index NUMBER(3);
687
688 l_acct_id NUMBER(10);
689 l_param_line_num NUMBER(3);
690 l_lookup_id NUMBER(10);
691 l_operand VARCHAR2(20);
692 l_user_value VARCHAR2(80);
693 l_parameter VARCHAR2(50);
694 l_datatype VARCHAR2(20);
695 l_bind_var VARCHAR2(30);
696 l_date_tracked BOOLEAN := FALSE;
697 l_expenditure_type VARCHAR2(30);
698 l_natural_account VARCHAR2(150);
699 --l_bind_param VARCHAR2(30);
700 l_varchar_results VARCHAR2(80);
701 l_number_results NUMBER;
702 l_date_results DATE;
703 l_return_status VARCHAR2(1);
704 l_by_pass VARCHAR2(30);
705 l_period_type VARCHAR2(30);
706
707
708 --For Bug 1907209 : Natural Bypass Account Enhancement : Added the following variable
709 l_by_pass_na VARCHAR2(150);
710 l_acct_seq_num number; ---- added for tracing
711
712 TYPE t_resolved IS TABLE OF VARCHAR2(5)
713 INDEX BY BINARY_INTEGER;
714 v_resolved t_resolved;
715 v_param_count INTEGER := 0;
716 v_false VARCHAR2(5) := 'FALSE';
717 v_true VARCHAR2(4) := 'TRUE';
718
719 v_counter INTEGER;
720 last_line_num INTEGER;
721 first_line_num INTEGER;
722
723 -- bug fxi 3986100 l_calculator_rule VARCHAR2(500) := NULL;
724 l_calculator_rule VARCHAR2(1000) := NULL;
725
726
727 -- bug fix 3986100 resolved_rule VARCHAR2(500) := NULL;
728 resolved_rule VARCHAR2(4000) := NULL;
729
730 l_rule_match BOOLEAN := FALSE;
731 l_chart_of_accts VARCHAR2(20);
732 l_struc_num NUMBER :=psp_general.find_chart_of_accts(p_set_of_books_id,l_chart_of_accts);
733 short_name CONSTANT VARCHAR2(50) := 'SQLGL';
734 flex_code CONSTANT VARCHAR2(4) := 'GL#';
735 struct_num CONSTANT NUMBER :=to_number(l_chart_of_accts);
736
737 /* For Bug 1907209 : Natural Bypass Acount - Moved the declaration of variables up as they are referenced by the
738 Cursor by_na_pass_cur -lveerubh
739 nsegs NUMBER;
740 cat_segs VARCHAR2(2000);
741 segs FND_FLEX_EXT.SegmentArray;
742 ccid_exists BOOLEAN;
743 combo_valid BOOLEAN;
744 new_gl_ccid NUMBER;
745 */
746
747 BY_PASS_FOUND EXCEPTION;
748 NO_PERIOD_TYPE EXCEPTION;
749
750 --For Bug 1907209 : Natural Bypass Account : Added the following Exception
751 BY_PASS_NA_FOUND EXCEPTION;
752
753 BEGIN
754 /* Instead of profile , the segment number is now picked from the table */
755 --For Bug Fix : 1760311 : Introducing Conditional Checking for NA while selecting segment Number
756 -- hr_utility.trace_on(null,'autodebug'); Commented by tbalacha
757 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: Inside main Param values
758 p_acct_type = ' || p_acct_type ||' p_person_id = ' || p_person_id ||' p_assignment_id = '
759 || p_assignment_id ||' p_element_type_id = ' || p_element_type_id ||' p_project_id = ' || p_project_id );
760 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: Inside main Param values Contd:
761 p_expenditure_organization_id = ' || p_expenditure_organization_id
762 ||' p_task_id = ' || p_task_id ||' p_award_id = ' || p_award_id ||' p_expenditure_type = ' || p_expenditure_type );
763 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: Inside main Param values Contd:
764 p_gl_code_combination_id = ' || p_gl_code_combination_id ||' p_payroll_date = ' || p_payroll_date );
765 IF p_acct_type = 'N' THEN
766 SELECT segment_number
767 INTO l_segment_num
768 FROM psp_auto_segments
769 WHERE business_group_id = p_business_group_id
770 AND set_of_books_id = p_set_of_books_id;
771
772 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: Getting the value of l_segment_num'|| l_segment_num);
773 -- END IF; should be moved down otherwise will result in unexpected error
774 -- when processing exp --type for correction of bug fix 1907209
775
776 g_error_api_path := '';
777 /******
778 For Bug 1907209 : Natural Bypass Account Enhancement : Added following code for Skipping the
779 Autopop for Natural Account-lveerubh
780 ****/
781 -- get the original segments
782 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before FND_FLEX_EXT.get_segments');
783
784 ccid_exists := FND_FLEX_EXT.get_segments( application_short_name => short_name,
785 key_flex_code => flex_code,
786 structure_number => struct_num,
787 combination_id => p_gl_code_combination_id,
788 n_segments => nsegs,
789 segments => segs);
790
791 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: after FND_FLEX_EXT.get_segments l_segment_num='||l_segment_num|| 'nsegs='||nsegs);
792
793 /* Bug Fix 5439154: Support for non Consecutive GL Segments*/
794 OPEN segment_number_csr;
795 l_segment_index :=1 ;
796 LOOP
797 FETCH segment_number_csr INTO l_segment_number;
798 EXIT WHEN segment_number_csr%NOTFOUND;
799 IF l_segment_number = l_segment_num THEN
800 l_seg_no := l_segment_index;
801 EXIT;
802 END IF;
803 l_segment_index := l_segment_index + 1;
804 END LOOP;
805 CLOSE segment_number_csr;
806
807 -- l_seg_no := l_segment_num;
808 IF (l_seg_no >nsegs) OR (l_seg_no IS NULL) THEN
809 --dbms_output.put_line('to test failure on exp type');
810 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: PSP_AUTOP_SEG_NUM');
811 FND_MESSAGE.SET_NAME('PSP','PSP_AUTOP_SEG_NUM');
812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
813 END IF;
814 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before IF ccid_exists = TRUE l_segment_num ='|| l_segment_num || 'segs(l_seg_no)='|| segs(l_seg_no)||'p_set_of_books_id='|| p_set_of_books_id ||'p_business_group_id='|| p_business_group_id);
815
816 IF ccid_exists = TRUE THEN
817 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside IF ccid_exists = TRUE THEN');
818 OPEN by_pass_na_cur;
819 FETCH by_pass_na_cur INTO l_by_pass_na;
820 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside l_by_pass_na='|| l_by_pass_na);
821
822 IF by_pass_na_cur%FOUND THEN
823 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside by_pass_na_cur%FOUND THEN');
824
825 -- By-Pass Natural Account Segment passed
826 -- Skip auto-population.
827 CLOSE by_pass_na_cur;
828 RAISE BY_PASS_NA_FOUND;
829 END IF;
830 CLOSE by_pass_na_cur;
831 ELSE
832 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: FND_FLEX_EXT.get_segments:CCID'||p_gl_code_combination_id||' passed in does not exist'||':'||g_error_api_path);
833 g_error_api_path := 'FND_FLEX_EXT.get_segments:CCID'||p_gl_code_combination_id||' passed in does not exist'||':'||g_error_api_path;
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF; -- end of if_ccid_exists
836 --End of Fix for Bug :1907209 : Natural By Pass Account -lveerubh
837 END IF; -- end of p_acct_type='N' -- subha for correction of bug fix 1907209
838
839 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before IF p_expenditure_type is NOT NULL THEN ');
840
841 -- Check By-Pass Table to skip following
842 -- code if passed expenditure type is in
843 -- user defined By-Pass Table.
844 --DBMS_OUTPUT.PUT_LINE('STRUCT_NUM'||TO_CHAR(struct_num));
845 IF p_expenditure_type is NOT NULL THEN
846
847 --dbms_output.put_line('before opening the bypass cursor');
848
849 OPEN by_pass_c;
850 FETCH by_pass_c INTO
851 l_by_pass;
852 IF by_pass_c%FOUND THEN
853 -- By-Pass Expenditure Type passed in.
854 -- Skip auto-population.
855 CLOSE by_pass_c;
856 RAISE BY_PASS_FOUND;
857 --dbms_output.put_line('bypass has bene foud ');
858 END IF;
859 CLOSE by_pass_c;
860 END IF;
861
862 -- Determine Period Type of assignment's payroll.
863 --dbms_output.put_line('before period type cursor ');
864 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before OPEN period_type_c ');
865
866 OPEN period_type_c;
867 FETCH period_type_c INTO
868 l_period_type;
869 --dbms_output.put_line('period_type , assignment'||l_period_type||' '||p_assignment_id||' '||p_payroll_date);
870 IF period_type_c%NOTFOUND THEN
871 CLOSE period_type_c;
872 RAISE NO_PERIOD_TYPE;
873
874
875 END IF;
876 CLOSE period_type_c;
877 --dbms_output.put_line('after det period type');
878
879 /*IF l_period_type NOT IN ('Bi-Week','Calendar Month','Semi-Month','Week') THEN
880 RAISE NO_PERIOD_TYPE;
881 END IF;*/
882
883 -- Cycle through either expenditure type
884 -- or Natural Account rules, depending
885 -- on the value of p_acct_type.
886 --dbms_output.put_line('before calling dynmaic query ');
887 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: Before Calling Dynamic Queny');
888 DynamicQuery(
889 p_person_id => p_person_id,
890 p_assignment_id => p_assignment_id,
891 p_element_type_id => p_element_type_id,
892 p_project_id => p_project_id,
893 p_expenditure_organization_id => p_expenditure_organization_id,
894 p_task_id => p_task_id,
895 p_award_id => p_award_id,
896 p_payroll_date => p_payroll_date,
897 p_expenditure_type => p_expenditure_type,
898 p_set_of_books_id => p_set_of_books_id,
899 p_business_group_id => p_business_group_id,
900 p_gl_code_combination_id => p_gl_code_combination_id,
901 p_return_status => l_return_status
902 );
903 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: after Calling Dynamic Queny l_return_status= '|| l_return_status);
904
905
906 IF l_return_status <> fnd_api.g_ret_sts_success THEN
907 g_error_api_path := 'DYNAMIC QUERY='||to_char(l_lookup_id)||':'||g_error_api_path;
908 --dbms_output.put_line(g_error_api_path);
909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
910 END IF;
911 --dbms_output.put_line('after return from dynamic query');
912
913 IF p_acct_type = 'E' THEN
914 OPEN exp_accts_c(l_period_type);
915 ELSIF p_acct_type = 'N' THEN
916 OPEN na_accts_c(l_period_type);
917 --dbms_output.put_line('periodc type' ||l_period_type);
918 END IF;
919
920 LOOP
921 IF p_acct_type = 'E' THEN
922 FETCH exp_accts_c into
923 l_acct_id,
924 l_expenditure_type, l_acct_seq_num;
925 --dbms_output.put_line('acct_id'||','||to_char(l_acct_id)||','||l_expenditure_type);
926 IF exp_accts_c%NOTFOUND THEN
927 CLOSE exp_accts_c;
928 EXIT;
929 END IF;
930 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: ***************EXPENDITURE TYPE - RULE NO ='||l_acct_seq_num||' ***************');
931 ELSIF p_acct_type = 'N' THEN
932 --dbms_output.put_line('p_acct_type'||p_acct_type);
933 --dbms_output.put_line('p_payroll_date'||to_char(p_payroll_date));
934 FETCH na_accts_c into
935 l_acct_id,l_segment_num,
936 l_natural_account, l_acct_seq_num;
937 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside the loop fetched values:l_period_type=' || l_period_type|| 'l_acct_id='|| l_acct_id||'l_segment_num='||l_segment_num ||'l_natural_account='||l_natural_account );
938 IF l_acct_id is NULL THEN
939 --dbms_output.put_line('acct_id'||','||to_char(l_acct_id)||','||l_natural_account);
940 null;
941
942 END IF;
943 IF na_accts_c%NOTFOUND THEN
944 CLOSE na_accts_c;
945 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: exiting from outer loop' );
946 EXIT;
947 END IF;
948 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: ****************NATURAL ACCOUNT - RULE NO ='||l_acct_seq_num||' ***************');
949 END IF;
950
951 -- Delete all records from PL/SQL table that will
952 -- hold the resolved values of the parameter expressions
953 -- for a given rule, either 'TRUE' or 'FALSE'.
954
955 v_resolved.DELETE;
956
957 -- Cycle through all parameter expressions for the
958 -- current rule and exit when no parameter exrpressions found.
959 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before OPEN params_c(l_acct_id) ACCT_ID ='||l_acct_id);
960
961 OPEN params_c(l_acct_id); LOOP
962 FETCH params_c into
963 l_param_line_num,
964 l_lookup_id,
965 l_operand,
966 l_user_value;
967 IF params_c %NOTFOUND THEN
968 CLOSE params_c;
969 EXIT;
970 END IF;
971 --dbms_output.put_line('open params cursor ');
972 /*
973
974 -- Check the type of bind variable to be used with the
975 -- dynamic sql statement for this parameter expression
976 -- and assign the correct raw parameter to the variable
977 -- l_bind_param.
978
979 IF l_bind_var = 'l_person_id' THEN
980 l_bind_param := p_person_id;
981 l_date_tracked := TRUE;
982 ELSIF l_bind_var = 'l_assignment_id' THEN
983 l_bind_param := p_assignment_id;
984 l_date_tracked := TRUE;
985 ELSIF l_bind_var = 'l_element_type_id' THEN
986 l_bind_param := p_element_type_id;
987 -- l_date_tracked := FALSE;
988 l_date_tracked := TRUE;
989 ELSIF l_bind_var = 'l_project_id' THEN
990 l_bind_param := p_project_id;
991 l_date_tracked := FALSE;
992 ELSIF l_bind_var = 'l_expenditure_organization_id' THEN
993 l_bind_param := p_expenditure_organization_id;
994 l_date_tracked := FALSE;
995 ELSIF l_bind_var = 'l_task_id' THEN
996 l_bind_param := p_task_id;
997 l_date_tracked := FALSE;
998 ELSIF l_bind_var = 'l_award_id' THEN
999 l_bind_param := p_award_id;
1000 l_date_tracked := FALSE;
1001 END IF;
1002
1003 -- Call procedure DynamicQuery to retrieve the
1004 -- current system value of the parameter. The
1005 -- value returned depends on the datatype of
1006 -- the parameter, denoted by l_datatype.
1007 IF l_parameter = 'Expenditure Type' THEN
1008 l_varchar_results := p_expenditure_type;
1009 ELSE
1010 DynamicQuery(p_dyn_sql_stmt => l_dyn_sql_stmt,
1011 p_bind_var => l_bind_param,
1012 p_date_tracked => l_date_tracked,
1013 p_datatype => l_datatype,
1014 p_payroll_date => p_payroll_date,
1015 l_ret_varchar => l_varchar_results,
1016 l_ret_number => l_number_results,
1017 l_ret_date => l_date_results,
1018 p_return_status => l_return_status);
1019 IF l_return_status != fnd_api.g_ret_sts_success THEN
1020 CLOSE params_c;
1021 CLOSE exp_accts_c;
1022 g_error_api_path := 'LOOKUP_ID='||to_char(l_lookup_id)||':'||g_error_api_path;
1023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1024 END IF;
1025 END IF;
1026 -- Compare the current system value returned from
1027 -- the call to DynamicQuery with the user value
1028 -- and operand specified in the parameter expression.
1029 -- Assign either 'TRUE' or 'FALSE' to the PL/SQL
1030 -- table v_resolved() that will hold the resolved
1031 -- values for each of the parameter expressions
1032 -- cycled through in the current loop.
1033 */
1034 --dbms_output.put_line('before accessing the array');
1035 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before if lookup_array.EXISTS');
1036 if lookup_array.EXISTS(l_lookup_id)THEN
1037 l_datatype:=lookup_array(l_lookup_id).data_type;
1038 l_varchar_results:=lookup_array(l_lookup_id).charres;
1039 l_number_results:=lookup_array(l_lookup_id).numres;
1040 l_date_results:=lookup_array(l_lookup_id).dateres;
1041 END IF;
1042
1043
1044 --dbms_output.put_line('after fetch from temp');
1045 --dbms_output.put_line('data type '||l_datatype);
1046 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before seraching for datatype l_param_line_num ='||l_param_line_num);
1047 IF l_datatype = 'VARCHAR2' THEN
1048 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: in VArchar2');
1049 hr_utility.trace('Autodebug Message:PSP_AUTOPOP:variable ='||l_varchar_results||' user_value='||l_user_value);
1050 IF l_operand = '=' THEN
1051 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: in = block');
1052 IF l_varchar_results IS NULL AND l_user_value IS NULL THEN
1053 v_resolved(l_param_line_num) := v_true;
1054 ELSIF l_varchar_results = l_user_value THEN
1055 v_resolved(l_param_line_num) := v_true;
1056 ELSE
1057 v_resolved(l_param_line_num) := v_false;
1058 END IF;
1059 ELSIF l_operand = '<>' THEN
1060 IF l_varchar_results IS NULL AND l_user_value IS NULL THEN
1061 v_resolved(l_param_line_num) := v_false;
1062 ELSIF l_varchar_results <> l_user_value THEN
1063 v_resolved(l_param_line_num) := v_true;
1064 ELSE
1065 v_resolved(l_param_line_num) := v_false;
1066 END IF;
1067 ELSIF l_operand = 'LIKE' THEN
1068 IF l_varchar_results IS NULL AND l_user_value IS NULL THEN
1069 v_resolved(l_param_line_num) := v_true;
1070 ELSIF l_varchar_results LIKE l_user_value THEN
1071 v_resolved(l_param_line_num) := v_true;
1072 ELSE
1073 v_resolved(l_param_line_num) := v_false;
1074 END IF;
1075 /* Added for NOT LIKE */
1076 ELSIF l_operand = 'NOT LIKE' THEN
1077 IF l_varchar_results IS NULL AND l_user_value IS NULL THEN
1078 v_resolved(l_param_line_num) := v_false;
1079 ELSIF l_varchar_results NOT LIKE l_user_value THEN
1080 v_resolved(l_param_line_num) := v_true;
1081 ELSE
1082 v_resolved(l_param_line_num) := v_false;
1083 END IF;
1084
1085 ELSIF l_operand = 'IS' THEN
1086 IF l_varchar_results IS NULL THEN
1087 v_resolved(l_param_line_num) := v_true;
1088 ELSE
1089 v_resolved(l_param_line_num) := v_false;
1090 END IF;
1091 ELSIF l_operand = 'IS NOT' THEN
1092 IF l_varchar_results IS NOT NULL THEN
1093 v_resolved(l_param_line_num) := v_true;
1094 ELSE
1095 v_resolved(l_param_line_num) := v_false;
1096 END IF;
1097 END IF;
1098
1099 ELSIF l_datatype = 'NUMBER' THEN
1100 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: in Number l_param_line_num ='||l_param_line_num);
1101 hr_utility.trace('Autodebug Message:PSP_AUTOPOP:variable ='||l_number_results||' user_value='||l_user_value);
1102
1103 IF l_operand = '=' THEN
1104 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: in = block');
1105 IF l_number_results IS NULL AND l_user_value IS NULL THEN
1106 v_resolved(l_param_line_num) := v_true;
1107 ELSIF l_number_results = to_number(l_user_value) THEN
1108 v_resolved(l_param_line_num) := v_true;
1109 ELSE
1110 v_resolved(l_param_line_num) := v_false;
1111 END IF;
1112 ELSIF l_operand = '<>' THEN
1113 IF l_number_results IS NULL AND l_user_value IS NULL THEN
1114 v_resolved(l_param_line_num) := v_false;
1115 ELSIF l_number_results <> to_number(l_user_value) THEN
1116 v_resolved(l_param_line_num) := v_true;
1117 ELSE
1118 v_resolved(l_param_line_num) := v_false;
1119 END IF;
1120 ELSIF l_operand = '>' THEN
1121 IF l_number_results IS NULL AND l_user_value IS NULL THEN
1122 v_resolved(l_param_line_num) := v_false;
1123 ELSIF l_number_results > to_number(l_user_value) THEN
1124 v_resolved(l_param_line_num) := v_true;
1125 ELSE
1126 v_resolved(l_param_line_num) := v_false;
1127 END IF;
1128 ELSIF l_operand = '<' THEN
1129 IF l_number_results IS NULL AND l_user_value IS NULL THEN
1130 v_resolved(l_param_line_num) := v_false;
1131 ELSIF l_number_results < to_number(l_user_value) THEN
1132 v_resolved(l_param_line_num) := v_true;
1133 ELSE
1134 v_resolved(l_param_line_num) := v_false;
1135 END IF;
1136 ELSIF l_operand = '>=' THEN
1137 IF l_number_results IS NULL AND l_user_value IS NULL THEN
1138 v_resolved(l_param_line_num) := v_false;
1139 ELSIF l_number_results >= to_number(l_user_value) THEN
1140 v_resolved(l_param_line_num) := v_true;
1141 ELSE
1142 v_resolved(l_param_line_num) := v_false;
1143 END IF;
1144 ELSIF l_operand = '<=' THEN
1145 IF l_number_results IS NULL AND l_user_value IS NULL THEN
1146 v_resolved(l_param_line_num) := v_false;
1147 ELSIF l_number_results <= to_number(l_user_value) THEN
1148 v_resolved(l_param_line_num) := v_true;
1149 ELSE
1150 v_resolved(l_param_line_num) := v_false;
1151 END IF;
1152 ELSIF l_operand = 'IS' THEN
1153 IF l_number_results IS NULL THEN
1154 v_resolved(l_param_line_num) := v_true;
1155 ELSE
1156 v_resolved(l_param_line_num) := v_false;
1157 END IF;
1158 ELSIF l_operand = 'IS NOT' THEN
1159 IF l_number_results IS NOT NULL THEN
1160 v_resolved(l_param_line_num) := v_true;
1161 ELSE
1162 v_resolved(l_param_line_num) := v_false;
1163 END IF;
1164 END IF;
1165
1166 ELSIF l_datatype = 'DATE' THEN
1167 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: in Date l_param_line_num ='||l_param_line_num);
1168 hr_utility.trace('Autodebug Message:PSP_AUTOPOP:variable ='||l_date_results||' user_value='||l_user_value);
1169
1170 IF l_operand = '=' THEN
1171 IF l_date_results IS NULL AND l_user_value IS NULL THEN
1172 v_resolved(l_param_line_num) := v_true;
1173 ELSIF l_date_results = to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS') THEN
1174 v_resolved(l_param_line_num) := v_true;
1175 ELSE
1176 v_resolved(l_param_line_num) := v_false;
1177 END IF;
1178 ELSIF l_operand = '<>' THEN
1179 IF l_date_results IS NULL AND l_user_value IS NULL THEN
1180 v_resolved(l_param_line_num) := v_false;
1181 -- ELSIF l_date_results <> to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS') THEN
1182 ELSIF trunc(l_date_results) <> trunc(to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS')) THEN
1183 v_resolved(l_param_line_num) := v_true;
1184 ELSE
1185 v_resolved(l_param_line_num) := v_false;
1186 END IF;
1187 ELSIF l_operand = '>' THEN
1188 IF l_date_results IS NULL AND l_user_value IS NULL THEN
1189 v_resolved(l_param_line_num) := v_false;
1190 ELSIF trunc(l_date_results) > trunc(to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS')) THEN
1191 v_resolved(l_param_line_num) := v_true;
1192 ELSE
1193 v_resolved(l_param_line_num) := v_false;
1194 END IF;
1195 ELSIF l_operand = '<' THEN
1196 IF l_date_results IS NULL AND l_user_value IS NULL THEN
1197 v_resolved(l_param_line_num) := v_false;
1198 ELSIF trunc(l_date_results) < trunc(to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS')) THEN
1199 v_resolved(l_param_line_num) := v_true;
1200 ELSE
1201 v_resolved(l_param_line_num) := v_false;
1202 END IF;
1203 ELSIF l_operand = '>=' THEN
1204 IF l_date_results IS NULL AND l_user_value IS NULL THEN
1205 v_resolved(l_param_line_num) := v_false;
1206 ELSIF trunc(l_date_results) >= trunc(to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS')) THEN
1207 v_resolved(l_param_line_num) := v_true;
1208 ELSE
1209 v_resolved(l_param_line_num) := v_false;
1210 END IF;
1211 ELSIF l_operand = '<=' THEN
1212 IF l_date_results IS NULL AND l_user_value IS NULL THEN
1213 v_resolved(l_param_line_num) := v_false;
1214 ELSIF trunc(l_date_results) <= trunc(to_date(l_user_value,'YYYY/MM/DD HH24:MI:SS')) THEN
1215 v_resolved(l_param_line_num) := v_true;
1216 ELSE
1217 v_resolved(l_param_line_num) := v_false;
1218 END IF;
1219 ELSIF l_operand = 'IS' THEN
1220 IF l_date_results IS NULL THEN
1221 v_resolved(l_param_line_num) := v_true;
1222 ELSE
1223 v_resolved(l_param_line_num) := v_false;
1224 END IF;
1225 ELSIF l_operand = 'IS NOT' THEN
1226 IF l_date_results IS NOT NULL THEN
1227 v_resolved(l_param_line_num) := v_true;
1228 ELSE
1229 v_resolved(l_param_line_num) := v_false;
1230 END IF;
1231 END IF;
1232
1233 ELSE
1234 g_error_api_path := 'Invalid Datatype:LOOKUP_ID='||to_char(l_lookup_id)||':'||g_error_api_path;
1235 CLOSE params_c;
1236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1237 END IF;
1238
1239 END LOOP;
1240 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: after Loop');
1241
1242
1243 -- Determine how many parameter expressions were looped
1244 -- through. If zero, then raise unexpected error. There
1245 -- should never be a rule with a rule definition but
1246 -- no parameter expressions.
1247
1248 last_line_num := v_resolved.last;
1249 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: last_line_num = '||last_line_num );
1250
1251 IF last_line_num IS NULL THEN
1252 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: NO PARAMETERS:ACCT_ID='||to_char(l_acct_id)||':'||g_error_api_path);
1253 g_error_api_path := 'NO PARAMETERS:ACCT_ID='||to_char(l_acct_id)||':'||g_error_api_path;
1254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1255 END IF;
1256
1257 /* added to fix the case of the v_counter looping thru to 1,
1258 when 1 may not exist :- Subha July19, 2000
1259 */
1260 first_line_num:=v_resolved.first;
1261 -- Retrieve the calculator rule for the current rule
1262 -- being processed. There should always be a calculator
1263 -- rule (i.e., 1 AND 2 OR 3 ) for a rule which has a
1264 -- rule definition which is criteria of the cursor.
1265
1266 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before opening the cursor rules_c l_acct_id='|| l_acct_id);
1267 open rules_c(l_acct_id);
1268 fetch rules_c into l_calculator_rule;
1269 --dbms_output.put_line('l_calculator_rule '||','||l_calculator_rule);
1270 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside the cursor rules_c l_calculator_rule' || l_calculator_rule);
1271
1272 IF rules_c%NOTFOUND THEN
1273 --dbms_output.put_line('no rule found');
1274 close rules_c;
1275 g_error_api_path := 'NO RULE:ACCT_ID='||to_char(l_acct_id)||':'||g_error_api_path;
1276 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: cursor rules_c not found l_calculator_rule' || l_calculator_rule);
1277
1278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1279 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: after l_cal_rule Raised FND_API.G_EXC_UNEXPECTED_ERROR');
1280 END IF;
1281 close rules_c;
1282
1283 -- Use the values of the resolved parameter
1284 -- expressions held in the PL/SQL table v_resolved()
1285 -- to replace the parameter line numbers in the
1286 -- rule string.
1287
1288 resolved_rule := ltrim(l_calculator_rule);
1289 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: resolved_rule '||l_calculator_rule);
1290
1291 --dbms_output.put_line('after rule assignment '||resolved_rule);
1292
1293 /* replaced 1 by first_line_num :- Subha July 19, 2000 */
1294
1295 FOR v_counter in REVERSE first_line_num..last_line_num LOOP
1296 --dbms_output.put_line('v_counter '||to_char(v_counter));
1297 --dbms_output.put_line('v_resolved '||v_resolved(v_counter));
1298 if v_resolved.exists(v_counter) then
1299 /*added so that exception is not raised for elements not present */
1300 resolved_rule := REPLACE(resolved_rule,TO_CHAR(v_counter),v_resolved(v_counter));
1301 --dbms_output.put_line('resolved_rule '||resolved_rule);
1302 end if;
1303 END LOOP;
1304
1305 resolved_rule:=REPLACE(resolved_rule,v_true,'1=1');
1306 resolved_rule:=REPLACE(resolved_rule,v_false,'1=0');
1307
1308 -- dbms_output.put_line('Before call to resolve_rule with text string:'||resolved_rule);
1309 hr_utility.trace('Autodebug Message:PSP_AUTOPOP '||resolved_rule);
1310 -- IF resolve_rules(resolved_rule) THEN bug fix 2023955
1311 IF resolve_rules_new(resolved_rule) THEN
1312 hr_utility.trace('Autodebug Message:PSP_AUTOPOP rule is true ');
1313 -- dbms_output.put_line('RULE IS TRUE!!!');
1314 l_rule_match := TRUE;
1315 EXIT;
1316 ELSE
1317 hr_utility.trace('Autodebug Message:PSP_AUTOPOP rule is false');
1318 -- dbms_output.put_line('RULE IS FALSE!!!');
1319 --null;
1320 END IF;
1321
1322 END LOOP;
1323 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: outside of outer loop');
1324
1325 lookup_array.delete;
1326 IF l_rule_match THEN
1327 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: in side IF l_rule_match ' );
1328 IF p_acct_type = 'E' THEN
1329 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside IF p_acct_type = E');
1330 ret_expenditure_type := l_expenditure_type;
1331 ELSIF p_acct_type = 'N' THEN
1332 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: inside IF p_acct_type = N');
1333 -- *************************************************************
1334 -- Make calls to FND_FLEX_EXT
1335 -- *************************************************************
1336 /* For Bug 1907209 : Natural Bypass Account : Call to get the original segments has been moved to the top
1337 becase of the enhancement -lveerubh
1338 ccid_exists := FND_FLEX_EXT.get_segments(application_short_name => short_name,
1339 key_flex_code => flex_code,
1340 structure_number => struct_num,
1341 combination_id => p_gl_code_combination_id,
1342 n_segments => nsegs,
1343 segments => segs);
1344 --DBMS_OUTPUT.PUT_LINE('nsegs'||','||to_char(nsegs));
1345 --DBMS_OUTPUT.PUT_LINE('p_gl_code_combination_id'||','||to_char(p_gl_code_combination_id));
1346 IF (ccid_exists = TRUE) THEN
1347 */
1348
1349 /* IF (ccid_exists = TRUE) THEN
1350 FOR i in 1..nsegs loop
1351 cat_segs := cat_segs || '(' || segs(i) || ')';
1352 end loop;
1353 ELSE
1354 cat_segs := 'INVALID. Message = ';
1355 cat_segs := cat_segs || FND_MESSAGE.GET;
1356 END IF;
1357 --DBMS_OUTPUT.put_line('cat-segs is '||cat_segs);
1358
1359 segs(nsegs) := l_natural_account;
1360 cat_segs := '';
1361 IF (ccid_exists = TRUE) THEN
1362 FOR i in 1..nsegs loop
1363 cat_segs := cat_segs || '(' || segs(i) || ')';
1364 end loop;
1365 ELSE
1366 cat_segs := 'INVALID. Message = ';
1367 cat_segs := cat_segs || FND_MESSAGE.GET;
1368 END IF;
1369 --DBMS_OUTPUT.put_line('New cat-segs is '||cat_segs); */
1370 /*Instead of choosing a last segment it is better to give client a chance to select a
1371 Natural account seg serial num depending on the profile setting*/
1372
1373 -- l_seg_no := TO_NUMBER(FND_PROFILE.VALUE('PSP_AUTOP_SEG_NUM'));
1374 /* For Bug 1907209 : Natural By Pass Account Enhancement: Commenting the code
1375 l_seg_no:=l_segment_num;
1376 if (l_seg_no >nsegs) or (l_seg_no IS NULL) THEN
1377 FND_MESSAGE.SET_NAME('PSP','PSP_AUTOP_SEG_NUM');
1378 -- APP_EXCEPTION.RAISE_EXCEPTION;
1379 END IF;
1380 */
1381 --segs(nsegs ) := l_natural_account;
1382 segs(l_seg_no) := l_natural_account;
1383 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: before calling FND_FLEX_EXT.get_combination_id') ;
1384 combo_valid := FND_FLEX_EXT.get_combination_id(application_short_name => short_name,
1385 key_flex_code => flex_code,
1386 structure_number => struct_num,
1387 validation_date => SYSDATE,
1388 n_segments => nsegs,
1389 segments => segs,
1390 combination_id => new_gl_ccid);
1391 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: after calling FND_FLEX_EXT get_combination_id combo_valid = ');
1392
1393 IF (combo_valid = TRUE) THEN
1394 ret_gl_code_combination_id := new_gl_ccid;
1395 ELSE
1396 g_error_api_path := 'FND_FLEX_EXT.get_combination_id:Error creating new ccid with old CCID = '
1397 ||p_gl_code_combination_id||': Object_code = '||l_natural_account||':'||g_error_api_path;
1398 hr_utility.trace('Autodebug Message:PSP_AUTOPOP:'|| g_error_api_path);
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401 /* For Bug 1907209 : Natural Bypass Account : Call to get the original segments has been moved to the top
1402 becase of the enhancement -lveerubh
1403
1404 ELSE
1405 g_error_api_path := 'FND_FLEX_EXT.get_segments:CCID'||p_gl_code_combination_id||' passed in does not exist'
1406 ||':'||g_error_api_path;
1407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1408 END IF;
1409 */
1410 END IF;
1411
1412 retcode := FND_API.G_RET_STS_SUCCESS;
1413 -- --dbms_output.put_line('Rule was found!!!');
1414 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: SUCCESS RULE FOUND');
1415 ELSE
1416 retcode := FND_API.G_RET_STS_ERROR;
1417 -- --dbms_output.put_line('No rule found that matched.');
1418 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: NO RULE FOUND');
1419 END IF;
1420 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: END MAIN with out Exception'||retcode);
1421 -- hr_utility.trace_off ; Commented by tbalacha
1422
1423 EXCEPTION
1424 WHEN BY_PASS_FOUND THEN
1425 ret_expenditure_type := p_expenditure_type;
1426 retcode := FND_API.G_RET_STS_SUCCESS;
1427 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: EXCEPTIOn BY_PASS_FOUND');
1428
1429 WHEN NO_PERIOD_TYPE THEN
1430 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1431 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: EXCEPTIOn NO_PERIOD_TYPE');
1432
1433 -- For Bug 1907209 : Natrual Bypass Account Enhancement - Added the following Exception -lveerubh
1434 WHEN BY_PASS_NA_FOUND THEN
1435 ret_gl_code_combination_id := p_gl_code_combination_id;
1436 retcode := FND_API.G_RET_STS_SUCCESS;
1437 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: EXCEPTIOn BY_PASS_NA_FOUND');
1438 WHEN OTHERS THEN
1439 --dbms_output.put_line('Exception in Auto-Population:PSP_AUTOPOP:'||g_error_api_path);
1440 hr_utility.trace('Autodebug Message:PSP_AUTOPOP: EXCEPTIOn OTHERS');
1441
1442 retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1443 END main;
1444
1445 END;
[Home] [Help]