eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PQH_SIT_ENGINE

Source


1 PACKAGE BODY PQH_SIT_ENGINE as
2 /* $Header: pqsiteng.pkb 120.0 2005/05/29 02:41 appldev noship $ */
3 
4 procedure get_attribute(p_txn_catg_attr_id in number, p_attribute_id out NOCOPY number,
5                         p_value_style_cd OUT NOCOPY varchar2)  is
6 l_attribute_id number;
7 l_value_style_cd Varchar2(30);
8 begin
9    select attribute_id, value_style_cd
10    into l_attribute_id, l_value_style_cd
11    from pqh_txn_category_attributes
12    where txn_category_attribute_id = p_txn_catg_attr_id;
13    hr_utility.set_location('attr : '||l_attribute_id, 5);
14    hr_utility.set_location('l_value_style_cd : '||l_value_style_cd, 5);
15    p_attribute_id := l_attribute_id;
16    p_value_style_cd := l_value_style_cd;
17 exception
18    when no_data_found then
19       hr_utility.set_location('ndf txn_cat_attr : '||p_txn_catg_attr_id, 10);
20       raise;
21    when others then
22       hr_utility.set_location('other error, txn_cat_attr : '||p_txn_catg_attr_id, 10);
23       raise;
24 end get_attribute;
25 
26 Function GET_TRANSACTION_VALUE (p_person_id      IN  number,
27                                 p_effective_date in date,
28                                 p_attribute_id   IN  number) RETURN  varchar2 IS
29    l_sel_stmt               varchar2(4000);
30    l_from_clause            PQH_TABLE_ROUTE.FROM_CLAUSE%TYPE;
31    l_where_clause_in        PQH_TABLE_ROUTE.WHERE_CLAUSE%TYPE;
32    l_where_clause_out       PQH_TABLE_ROUTE.WHERE_CLAUSE%TYPE;
33    l_column_name            PQH_ATTRIBUTES.COLUMN_NAME%TYPE;
34    l_column_type            PQH_ATTRIBUTES.COLUMN_TYPE%TYPE;
35    l_selected_value_v       varchar2(2000);
36    l_selected_value_n       number;
37    l_selected_value_d       date;
38    l_proc                   varchar2(100) := 'get_transaction_value';
39    l_table_route_id         PQH_TABLE_ROUTE.TABLE_ROUTE_ID%TYPE;
40 BEGIN
41 hr_utility.set_location('Entering : '||l_proc, 5);
42 if (p_person_id is not null and p_attribute_id is not null) then
43 
44 -- get the attribute details
45    select column_name, master_table_route_id,column_type
46    into   l_column_name, l_table_route_id,l_column_type
47    from   pqh_attributes
48    where  attribute_id = p_attribute_id;
49    hr_utility.set_location('column_name1 is : '||substr(l_column_name,1,50),10);
50    hr_utility.set_location('column_name2 is : '||substr(l_column_name,51,50),11);
51    hr_utility.set_location('column_name3 is : '||substr(l_column_name,101,50),12);
52    hr_utility.set_location('column_name4 is : '||substr(l_column_name,151,50),13);
53    hr_utility.set_location('column_type is : '||l_column_type,15);
54    hr_utility.set_location('table_route is : '||l_table_route_id,20);
55 
56 -- table route is selected, get the details
57 
58    select from_clause, where_clause
59    into   l_from_clause, l_where_clause_in
60    from   pqh_table_route where table_route_id = l_table_route_id;
61 
62    hr_utility.set_location('from_clause1 is : '||substr(l_from_clause,1,30),30);
63    hr_utility.set_location('from_clause2 is : '||substr(l_from_clause,31,30),31);
64    hr_utility.set_location('where_clause 1is : '||substr(l_where_clause_in,1,40),40);
65    hr_utility.set_location('where_clause 2is : '||substr(l_where_clause_in,41,40),40);
66 
67 -- update the where clause with the context values
68 
69     pqh_refresh_data.replace_where_params(
70       p_where_clause_in  => l_where_clause_in,
71       p_txn_tab_flag     => 'N',
72       p_txn_id           => p_person_id,
73       p_where_clause_out => l_where_clause_out);
74    hr_utility.set_location('where_clause 1is : '||substr(l_where_clause_out,1,40),50);
75    hr_utility.set_location('where_clause 2is : '||substr(l_where_clause_out,41,40),50);
76 
77    hr_utility.set_location('select clause 1is : '||substr(l_column_name,1,40),50);
78    l_column_name := replace(l_column_name,'p_person_id',p_person_id);
79    hr_utility.set_location('select clause 2is : '||substr(l_column_name,1,40),50);
80    l_column_name := replace(l_column_name,'p_effective_date',''''||to_char(p_effective_date,'dd/mm/yyyy')||'''');
81    hr_utility.set_location('select clause 3is : '||substr(l_column_name,1,40),50);
82 -- build up the statement to be used for getting the value
83     l_sel_stmt := 'select '||l_column_name||' from '||l_from_clause||' where '||l_where_clause_out ;
84    hr_utility.set_location('stmt1 '||substr(l_sel_stmt,1,60),55);
85    hr_utility.set_location('stmt2 '||substr(l_sel_stmt,61,60),55);
86    hr_utility.set_location('stmt3 '||substr(l_sel_stmt,121,60),55);
87    hr_utility.set_location('stmt4 '||substr(l_sel_stmt,181,60),55);
88    hr_utility.set_location('stmt5 '||substr(l_sel_stmt,241,60),55);
89    hr_utility.set_location('stmt6 '||substr(l_sel_stmt,361,60),55);
90 
91 -- execute the dynamic sql
92    if l_column_type ='D' then
93       hr_utility.set_location('date being fetched ',60);
94       execute immediate l_sel_stmt into l_selected_value_d;
95       -- converting the date to character format
96       l_selected_value_v := fnd_date.date_to_canonical(l_selected_value_d);
97    elsif l_column_type ='N' then
98       hr_utility.set_location('number being fetched ',60);
99       execute immediate l_sel_stmt into l_selected_value_n;
100       l_selected_value_v := to_char(l_selected_value_n);
101    else
102       hr_utility.set_location('varchar being fetched ',60);
103       execute immediate l_sel_stmt into l_selected_value_v;
104    end if;
105    hr_utility.set_location('leaving with value: '||l_selected_value_v, 90);
106    return l_selected_value_v;
107 else
108    hr_utility.set_location('values passed was null. '||l_proc, 420);
109    return null;
110 end if;
111 EXCEPTION
112    when no_data_found then
113       hr_utility.set_location('no data exists '||l_proc, 100);
114       return null;
115    WHEN others THEN
116       hr_utility.set_location('Failure in program unit: '||l_proc, 420);
117       return null;
118 END GET_TRANSACTION_VALUE;
119 
120 function check_attribute_result(p_rule_from in varchar2,
121                                 p_txn_value in varchar2,
122                                 p_rule_to   in varchar2,
123                                 p_value_style_cd in varchar2,
124                                 p_exclude_flag in varchar2) return BOOLEAN is
125    l_proc varchar2(30) := g_package||'check_attr';
126 BEGIN
127    hr_utility.set_location('entering '||l_proc, 5);
128    hr_utility.set_location('p_rule_from is '||p_rule_from, 5);
129    hr_utility.set_location('p_rule_to is '||p_rule_to, 10);
130    hr_utility.set_location('p_txn_value is '||p_txn_value, 15);
131    hr_utility.set_location('p_value_style_cd is '||p_value_style_cd, 15);
132    hr_utility.set_location('p_exclude_flag is '||p_exclude_flag, 15);
133 
134    if p_txn_value is null then
135       hr_utility.set_location('txn_value_null'||l_proc, 420);
136       return false;
137    else
138      IF  p_value_style_cd = 'EXACT' THEN
139          hr_utility.set_location('Value Cd is Exact '||l_proc, 420);
140 	      IF p_txn_value = p_rule_from THEN
141             hr_utility.set_location('txn value is equal to rule_from ', 420);
142 	        IF p_exclude_flag = 'Y' THEN
143 	           hr_utility.set_location('exclude flag Y ', 420);
144 	           RETURN FALSE;
145 	        ELSE
146 	          hr_utility.set_location('exclude flag N ', 420);
147 	          RETURN TRUE;
148 	        END IF;
149           ELSE
150 	          hr_utility.set_location('txn value is not equal to rule_from ', 420);
151 	        IF p_exclude_flag = 'Y' THEN
152 	           hr_utility.set_location('exclude flag Y ', 420);
153 	           RETURN TRUE;
154 	        ELSE
155 	          hr_utility.set_location('exclude flag N ', 420);
156 	          RETURN FALSE;
157 	        END IF;
158           END IF;
159      ELSIF p_value_style_cd  = 'RANGE' THEN
160 	   if p_rule_from is null then
161          hr_utility.set_location('rule_from null'||l_proc, 420);
162          if p_txn_value <= p_rule_to then
163             hr_utility.set_location('txn_value less than to '||l_proc, 420);
164            IF p_exclude_flag = 'Y' THEN
165 	          RETURN FALSE;
166 	       ELSE
167 	         RETURN TRUE;
168 	       END IF;
169          else
170             hr_utility.set_location('txn_value more than to '||l_proc, 420);
171             IF p_exclude_flag = 'Y' THEN
172 	              RETURN TRUE;
173             ELSE
174 	              RETURN FALSE;
175 	        END IF;
176          end if;
177       else
178          if p_rule_to is null then
179             hr_utility.set_location('rule_to is null '||l_proc, 420);
180             if p_txn_value >= p_rule_from then
181                hr_utility.set_location('txn_value more than rule_from '||l_proc, 420);
182               IF p_exclude_flag = 'Y' THEN
183 	            RETURN FALSE;
184 	          ELSE
185 	            RETURN TRUE;
186 	          END IF;
187             else
188                hr_utility.set_location('txn_value less than rule_from '||l_proc, 420);
189                IF p_exclude_flag = 'Y' THEN
190 	              RETURN TRUE;
191 	           ELSE
192 	              RETURN FALSE;
193 	           END IF;
194             end if;
195          else
196             if p_txn_value between p_rule_from and p_rule_to then
197                hr_utility.set_location('txn_value between rule values '||l_proc, 420);
198               IF p_exclude_flag = 'Y' THEN
199 	             RETURN FALSE;
200 	          ELSE
201 	            RETURN TRUE;
202 	          END IF;
203             else
204                hr_utility.set_location('txn_value not between rule values '||l_proc, 420);
205                IF p_exclude_flag = 'Y' THEN
206 	              RETURN TRUE;
207 	           ELSE
208 	              RETURN FALSE;
209 	           END IF;
210             end if;
211          end if;
212       end if;
213 	 END IF;
214    end if;
215 EXCEPTION
216    WHEN others THEN
217       return false;
218 END CHECK_ATTRIBUTE_RESULT;
219 
220 PROCEDURE apply_defined_rules(p_stat_sit_id    IN number,
221                               p_person_id      IN number,
222                               p_effective_date IN DATE,
223                               p_rule_type      IN VARCHAR2 DEFAULT 'REQUIRED',
224                               p_status_flag    OUT NOCOPY varchar2) is
225    l_proc varchar2(71) := g_package||'  apply_defined_rules';
226    l_cond_result boolean;
227    l_transaction_value varchar2(2000);
228    l_rule_from varchar2(2000);
229    l_rule_to varchar2(2000);
230    l_txn_type varchar2(30);
231    l_final_stat varchar2(30);
232    l_rule_message fnd_new_messages.message_text%type;
233    l_attribute_id number;
234    l_required_flag varchar2(2);
235    l_exclude_flag varchar2(2);
236    l_value_style_cd pqh_txn_category_attributes.value_style_cd%type;
237 
238    cursor csr_sit_rules (p_required_flag VARCHAR2) is
239         select * from pqh_fr_stat_situation_rules
240         where statutory_situation_id = p_stat_sit_id
241         and nvl(enabled_flag,'N') ='Y'
242         and required_flag = p_required_flag
243         order by processing_sequence;
244 begin
245      hr_utility.set_location('inside'||l_proc,10);
246 
247      p_status_flag := 'NO_RULES_DEFINED';
248 
249      hr_utility.set_location('p_rule_type '||p_rule_type,10);
250 
251      IF p_rule_type = 'REQUIRED' THEN
252         l_required_flag := 'Y';
253      ELSE
254         l_required_flag := 'N';
255      END IF;
256 
257         hr_utility.set_location('l_required_flag  '||l_required_flag,10);
258 
259    for i in csr_sit_rules(l_required_flag) loop
260        hr_utility.set_location('Checking rule conditions '||l_proc,70);
261        l_rule_from := i.from_value;
262        l_rule_to := i.to_value;
263        l_exclude_flag := i.exclude_flag;
264 
265        hr_utility.set_location('rule_from is '||l_rule_from,70);
266        hr_utility.set_location('rule_to is '||l_rule_to,70);
267        hr_utility.set_location('Exclude_flag is '||l_exclude_flag,70);
268 
269        hr_utility.set_location('Getting Attribute values ',70);
270        get_attribute(i.txn_category_attribute_id, l_attribute_id, l_value_style_cd);
271        hr_utility.set_location('attribute is '||l_attribute_id,70);
272        hr_utility.set_location('l_value_style_cd is '||l_value_style_cd,70);
273 
274        if l_attribute_id is not null then
275           l_transaction_value :=  get_transaction_value(p_person_id      => p_person_id,
276                                                         p_effective_date => p_effective_date,
277                                                         p_attribute_id   => l_attribute_id
278                                                         );
279           hr_utility.set_location('txn_value '||l_transaction_value,75);
280        end if;
281 
282        if l_transaction_value is not null and l_rule_from is not null then
283           hr_utility.set_location('checking result '||l_proc,75);
284           l_cond_result := check_attribute_result(p_rule_from => l_rule_from,
285                                                   p_txn_value => l_transaction_value,
286                                                   p_rule_to   => l_rule_to,
287                                                   p_value_style_cd => l_value_style_cd,
288                                                   p_exclude_flag => l_exclude_flag);
289 
290             -- hr_utility.set_location('l_cond_result '||to_char(l_cond_result),70);
291 
292              IF l_cond_result THEN
293 	            p_status_flag := 'YES';
294 	            IF p_rule_type = 'OPTIONAL' THEN
295 	              EXIT;
296 	           END IF;
297 	         ELSE
298 	            p_status_flag := 'NO';
299 	            IF p_rule_type = 'REQUIRED' THEN
300 	              fnd_message.set_name('PQH','PQH_FR_REQD_SIT_RULE_FAIL');
301                   HR_MULTI_MESSAGE.ADD;
302                   RAISE HR_MULTI_MESSAGE.error_message_exist;
303 	              EXIT;
304 	            END IF;
305 	         END IF;
306        end if;
307        hr_utility.set_location('going for next rule',140);
308    end loop; -- rules for all entities of a folder
309   -- hr_utility.set_location('all rule applied '||l_final_stat,145);
310    hr_utility.set_location('leaving '||l_proc,200);
311 exception
312    when others then
313       hr_utility.set_location('some error '||l_proc,420);
314       raise;
315 end apply_defined_rules;
316 
317 Function is_situation_valid (p_person_id in number,
318                                p_effective_date IN DATE,
319                                p_statutory_situation_id in NUMBER)
320 RETURN VARCHAR2
321 IS
322    l_passed_all_reqd VARCHAR2(30);
323    l_passed VARCHAR2(30);
324    l_return_status VARCHAR2(10) := 'N';
325    l_rule_valid  BOOLEAN := FALSE;
326    BEGIN
327 --Validate the Mandatory Conditions first. If it meets all the conditions, then situation is valid.
328      apply_defined_rules(p_statutory_situation_id ,p_person_id,p_effective_date,
329                           'REQUIRED',l_passed_all_reqd);
330 
331       IF l_passed_all_reqd = 'YES' THEN
332          hr_utility.set_location('l_passed_all_reqd  '||l_passed_all_reqd,10);
333          l_return_status := 'Y';
334       ELSIF l_passed_all_reqd = 'NO' THEN
335          hr_utility.set_location('l_passed_all_reqd  '||l_passed_all_reqd,10);
336          l_return_status := 'N';
337       ELSIF l_passed_all_reqd = 'NO_RULES_DEFINED' THEN
338          hr_utility.set_location('l_passed_all_reqd  '||l_passed_all_reqd,10);
339 --No Required rules defined. See if meets atleast one optional rule.
340          apply_defined_rules(p_statutory_situation_id ,p_person_id,p_effective_date,
341                           'OPTIONAL',l_passed);
342           hr_utility.set_location('l_passed  '||l_passed,10);
343          IF l_passed = 'YES' OR l_passed = 'NO_RULES_DEFINED' THEN
344 	       l_return_status := 'Y';
345 	     ELSE
346 	       l_return_status := 'N';
347            fnd_message.set_name('PQH','PQH_FR_OPT_SIT_RULE_FAIL');
348            HR_MULTI_MESSAGE.ADD;
349            RAISE HR_MULTI_MESSAGE.error_message_exist;
350 	     END IF;
351       END IF;
352       RETURN l_return_status;
353       hr_utility.set_location('leaving is_situation_valid ',10);
354    END is_situation_valid;
355 
356 end pqh_sit_engine;