DBA Data[Home] [Help]

PACKAGE BODY: APPS.BEN_ABV_LER

Source


1 package body ben_abv_ler as
2 /* $Header: beabvtrg.pkb 120.0 2005/05/28 00:19:54 appldev noship $*/
3 procedure ler_chk(p_old IN g_abv_ler_rec
4                  ,p_new IN g_abv_ler_rec
5                  ,p_effective_date in date default null ) is
6 l_session_date DATE;
7 l_person_id NUMBER;
8 l_system_date DATE;
9 --
10 cursor get_person_id IS
11 select person_id
12 from per_all_assignments_f
13 where assignment_id = p_new.assignment_id
14 and business_group_id = p_new.business_group_id
15 and l_session_date
16     between effective_start_date
17     and     effective_end_date;
18 --
19 cursor get_session_date IS
20 select effective_date
21 from   fnd_sessions
22 where  session_id = userenv('SESSIONID');
23 --
24 cursor get_system_date IS
25 select trunc(sysdate)
26 from   dual;
27 --
28 cursor get_ler(l_status varchar2) is
29  select ler.ler_id
30  ,      ler.typ_cd
31  ,      ler.ocrd_dt_det_cd
32  from   ben_ler_f ler
33  where  ler.business_group_id               = p_new.business_group_id
34  and    l_session_date
35         between ler.effective_start_date
36         and     ler.effective_end_date
37 and ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
38  and    ((exists
39         (select 1
40           from   ben_per_info_chg_cs_ler_f psl
41           ,      ben_ler_per_info_cs_ler_f lpl
42           where  source_table               = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
43           and    psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
44           and    lpl.business_group_id   = psl.business_group_id
45           and    lpl.business_group_id    = ler.business_group_id
46           and    l_session_date between psl.effective_start_date
47           and    psl.effective_end_date
48           and    l_session_date between lpl.effective_start_date
49           and    lpl.effective_end_date
50           and    lpl.ler_id                 = ler.ler_id)
51  	  )
52  OR      (exists
53           (select 1
54            from   ben_rltd_per_chg_cs_ler_f rpc
55            ,      ben_ler_rltd_per_cs_ler_f lrp
56            where  source_table               = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
57            and    lrp.business_group_id    = rpc.business_group_id
58            and    lrp.business_group_id    = ler.business_group_id
59            and    l_session_date between rpc.effective_start_date
60            and    rpc.effective_end_date
61            and    l_session_date between lrp.effective_start_date
62            and    lrp.effective_end_date
63            and    rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
64            and    lrp.ler_id                 = ler.ler_id)
65           ))
66   order by ler.ler_id;
67 --
68 cursor get_ler_col(p_ler_id IN NUMBER) is
69 select psl.source_column, psl.new_val, psl.old_val, 'P', psl.per_info_chg_cs_ler_rl, psl.rule_overrides_flag, lpl.chg_mandatory_cd
70 from ben_ler_per_info_cs_ler_f lpl, ben_per_info_chg_cs_ler_f psl
71 where lpl.ler_id = p_ler_id and
72 lpl.business_group_id = p_new.business_group_id
73 and  lpl.business_group_id  = psl.business_group_id
74 and l_session_date between psl.effective_start_date
75 and psl.effective_end_date
76 and l_session_date between lpl.effective_start_date
77 and lpl.effective_end_date
78 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
79 and source_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
80 UNION
81 select rpc.source_column, rpc.new_val, rpc.old_val, 'R', rpc.rltd_per_chg_cs_ler_rl per_info_chg_cs_ler, rpc.rule_overrides_flag, lrp.chg_mandatory_cd
82 from ben_ler_rltd_per_cs_ler_f lrp, ben_rltd_per_chg_cs_ler_f rpc
83 where lrp.ler_id = p_ler_id and
84 lrp.business_group_id = p_new.business_group_id
85 and  lrp.business_group_id  = rpc.business_group_id
86 and l_session_date between rpc.effective_start_date
87 and rpc.effective_end_date
88 and l_session_date between lrp.effective_start_date
89 and lrp.effective_end_date
90 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
91 and source_table = 'PER_ASSIGNMENT_BUDGET_VALUES_F'
92 order by 1;
93 --
94 cursor le_exists(p_person_id in number
95                 ,p_ler_id in number
96                 ,p_lf_evt_ocrd_dt in date) is
97 select 'Y'
98 from ben_ptnl_ler_for_per
99 where person_id = p_person_id
100 and   ler_id = p_ler_id
101 and   ptnl_ler_for_per_stat_cd = 'DTCTD'
102 and   lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
103 --
104 cursor get_contacts(p_person_id in number) is
105 select person_id
106 from per_contact_relationships
107 where contact_person_id = p_person_id
108 and business_group_id = p_new.business_group_id
109 and l_session_date between nvl(date_start,l_session_date)
110 and nvl(date_end,l_session_date)
111 and personal_flag = 'Y'
112 order by person_id;
113 --
114 l_changed BOOLEAN;
115 l_ler_id NUMBER;
116 l_typ_cd ben_ler_f.typ_cd%type ;
117 l_ocrd_dt_cd VARCHAR2(30);
118 l_column ben_rltd_per_chg_cs_ler_f.source_column%type;  -- VARCHAR2(30);
119 l_new_val ben_rltd_per_chg_cs_ler_f.new_val%type;   -- VARCHAR2(30);
120 l_old_val ben_rltd_per_chg_cs_ler_f.old_val%type;       -- VARCHAR2(30);
121 l_per_info_chg_cs_ler_rl number;
122 l_rule_output VARCHAR2(1);
123 l_ovn NUMBER;
124 l_ptnl_id NUMBER;
125 l_effective_end_date DATE := to_date('31-12-4712','DD-MM-YYYY');
126 l_effective_start_date DATE ;
127 --l_session_date DATE ;
128 l_lf_evt_ocrd_date DATE ;
129 l_le_exists VARCHAR2(1);
130 l_mnl_dt date;
131 l_dtctd_dt   date;
132 l_procd_dt   date;
133 l_unprocd_dt date;
134 l_voidd_dt   date;
135 l_type    VARCHAR2(1);
136 l_hld_person_id NUMBER;
137 --
138 l_bool  BOOLEAN;
139 l_status VARCHAR2(1);
140 l_industry VARCHAR2(1);
141 --
142 l_col_new_val VARCHAR2(1000);
143 l_col_old_val varchar2(1000);
144 --
145 l_rule_overrides_flag VARCHAR2(1);
146 l_chg_mandatory_cd VARCHAR2(1);
147 l_trigger boolean := TRUE;
148 --
149 --
150 begin
151 
152 benutils.set_data_migrator_mode; -- Bug 3320133
153 
154  if hr_general.g_data_migrator_mode in ( 'Y','P') then
155    --
156    return;
157    --
158  end if;
159 
160  l_bool :=fnd_installation.get(appl_id => 805
161                    ,dep_appl_id =>805
162                    ,status => l_status
163                    ,industry => l_industry);
164 
165     begin
166         hr_utility.set_location(' Entering: ben_abv_trigger', 10);
167         l_changed := FALSE;
168         If p_effective_date is not null then
169            l_session_date := p_effective_date ;
170         else
171            open get_session_date;
172            fetch get_session_date into l_session_date;
173            close get_session_date;
174         end if ;
175         open get_person_id;
176         fetch get_person_id into l_person_id;
177         close get_person_id;
178         open get_system_date;
179         fetch get_system_date into l_system_date;
180         close get_system_date;
181         l_effective_start_date := l_session_date;
182         --  l_lf_evt_ocrd_date := l_session_date;
183         --
184         hr_utility.set_location('ASSIGNMENT_ID'||p_new.assignment_id,10);
185         hr_utility.set_location('BUSINESS_GROUP_ID'||p_new.assignment_id,10);
186         hr_utility.set_location('VALUE'||p_new.assignment_id,10);
187         hr_utility.set_location('ASSIGNMENT_BUDGET_VALUE_ID'||p_new.assignment_id,10);
188         hr_utility.set_location('EFFECTIVE_START_DATE'||p_new.assignment_id,10);
189         hr_utility.set_location('EFFECTIVE_END_DATE'||p_new.assignment_id,10);
190         hr_utility.set_location('sess '||l_session_date, 20);
191         hr_utility.set_location('PERSON '||l_person_id, 20);
192         hr_utility.set_location('BG '||p_new.business_group_id, 20);
193         --
194         open get_ler(l_status);
195         loop
196            fetch get_ler into l_ler_id,l_typ_cd, l_ocrd_dt_cd;
197            exit when get_ler%notfound;
198            l_trigger := TRUE;
199            --
200            if l_ocrd_dt_cd is null then
201              l_lf_evt_ocrd_date := p_new.effective_start_date;
202            else
203              --
204              --   Call the common date procedure.
205              --
206              ben_determine_date.main
207                (p_date_cd         => l_ocrd_dt_cd
208                ,p_effective_date  => p_new.effective_start_date
209                ,p_lf_evt_ocrd_dt  => p_new.effective_start_date
210                ,p_returned_date   => l_lf_evt_ocrd_date
211                );
212            end if;
213            --
214            open get_ler_col(l_ler_id);
215            loop
216              fetch get_ler_col into l_column,l_new_val, l_old_val, l_type, l_per_info_chg_cs_ler_rl, l_rule_overrides_flag, l_chg_mandatory_cd;
217              exit when get_ler_col%NOTFOUND;
218              --
219 
220              hr_utility.set_location('LER '||l_ler_id, 20);
221              hr_utility.set_location('COLUMN '||l_column, 20);
222              hr_utility.set_location('NEWVAL '||l_new_val, 20);
223              l_changed := TRUE;
224       if get_ler_col%ROWCOUNT = 1 then
225                l_changed := TRUE;
226                hr_utility.set_location('rowcount 1 ', 20);
227              end if;
228              hr_utility.set_location(' ben_abv_trigger', 20);
229              --      hr_utility.set_location('New'||p_new.person_type_id, 20);
230              --      hr_utility.set_location('New'||p_old.person_type_id, 20);
231              hr_utility.set_location(' ben_abv_trigger', 20);
232              --
233              -- Call the formula here to evaluate per_info_chg_cs_ler_rl.
234              -- If it returns Y, then see the applicability of the data
235              -- changes based on new and old values.
236              --
237              l_rule_output := 'Y';
238              --
239              if l_per_info_chg_cs_ler_rl is not null then
240                 --
241               if l_column = 'ASSIGNMENT_ID' then
242                  l_col_old_val := to_char(p_old.ASSIGNMENT_ID);
243                  l_col_new_val := to_char(p_new.ASSIGNMENT_ID);
244                end if;
245          --
246               if l_column = 'VALUE' then
247                  l_col_old_val := to_char(p_old.VALUE);
248                  l_col_new_val := to_char(p_new.VALUE);
249               end if;
250          --
251          --
252               if l_column = 'EFFECTIVE_START_DATE' then
253                  l_col_old_val := to_char(p_old.EFFECTIVE_START_DATE,  'YYYY/MM/DD HH24:MI:SS');
254                  l_col_new_val := to_char(p_new.EFFECTIVE_START_DATE,  'YYYY/MM/DD HH24:MI:SS');
255               end if;
256          --
257               if l_column = 'EFFECTIVE_END_DATE' then
258                  l_col_old_val := to_char(p_old.EFFECTIVE_END_DATE,  'YYYY/MM/DD HH24 :MI:SS');
259                  l_col_new_val := to_char(p_new.EFFECTIVE_END_DATE,  'YYYY/MM/DD HH24 :MI:SS');
260              end if;
261                 benutils.exec_rule(
262                     p_formula_id        => l_per_info_chg_cs_ler_rl,
263                     p_effective_date    => l_session_date,
264                     p_lf_evt_ocrd_dt    => null,
265                     p_business_group_id => nvl(p_new.business_group_id, p_old.business_group_id),
266                     p_person_id         => l_person_id,  --  nvl(p_new.person_id, p_old.person_id),
267                     p_new_value         => l_col_new_val,
268                     p_old_value         => l_col_old_val,
269                     p_column_name       => l_column,
270                     p_ret_val           => l_rule_output,
271                     p_param5           => 'BEN_ABV_IN_EFFECTIVE_START_DATE',
272                     p_param5_value     => to_char(p_new.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
273                     p_param6           => 'BEN_ABV_IO_EFFECTIVE_START_DATE',
274                     p_param6_value     => to_char(p_old.EFFECTIVE_START_DATE, 'YYYY/MM/DD HH24:MI:SS'),
275                     p_param7           => 'BEN_ABV_IN_EFFECTIVE_END_DATE',
276                     p_param7_value     => to_char(p_new.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
277                     p_param8           => 'BEN_ABV_IO_EFFECTIVE_END_DATE',
278                     p_param8_value     => to_char(p_old.EFFECTIVE_END_DATE, 'YYYY/MM/DD HH24:MI:SS'),
279                     p_param9           => 'BEN_IV_LER_ID',    /* Bug 3891096 */
280                     p_param9_value     => to_char(l_ler_id),
281                     p_pk_id            => to_char(p_new.assignment_budget_value_id)
282                     );
283                 --
284              end if;
285              --
286 
287                 --
288                 if l_column = 'VALUE' then
289                    l_changed := (benutils.column_changed(p_old.value
290                                     ,p_new.value,l_new_val) AND
291                                        benutils.column_changed(p_new.value
292                                     ,p_old.value,l_old_val) AND
293                                     (l_changed));
294                 end if;
295                 hr_utility.set_location(' ben_abv_trigger', 30);
296                 --
297                 if l_column = 'ASSIGNMENT_BUDGET_VALUE_ID' then
298                    l_changed := (benutils.column_changed(p_old.assignment_budget_value_id
299                                   ,p_new.assignment_budget_value_id,l_new_val) AND
300                                        benutils.column_changed(p_new.assignment_budget_value_id
301                                   ,p_old.assignment_budget_value_id,l_old_val) AND
302                                   (l_changed));
303                 end if;
304                 --
305 
306              --
307               	-- Checking the rule output and the rule override flag.
308 			       	-- Whether the rule is mandatory or not, rule output should return 'Y'
312 			       	  l_changed := TRUE ;
309 			       	-- Rule Mandatory flag is just to override the column data change.
310 
311 			       	if l_rule_output = 'Y' and l_rule_overrides_flag = 'Y' then
313 			       	elsif l_rule_output = 'Y' and l_rule_overrides_flag = 'N' then
314 			       	  l_changed := l_changed AND TRUE;
315 					elsif l_rule_output = 'N' then
316 					  hr_utility.set_location(' Rule output is N, so we should not trigger LE', 20.01);
317 					  l_changed := FALSE;
318 			       	end if;
319 
320 			       	hr_utility.set_location('After the rule Check ',20.05);
321 			       	if l_changed then
322 			       	  hr_utility.set_location('     l_change TRUE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
323 			       	else
324 			       	  hr_utility.set_location('     l_change FALSE l_rule_overrides_flag '||l_rule_overrides_flag, 20.1);
325 			       	end if;
326 			        	-- Check for Column Mandatory Change
327 			       	-- If column change is mandatory and data change has failed then dont trigger
328 			       	-- If column change is non-mandatory and the data change has passed, then trigger.
329 
330 					if l_chg_mandatory_cd = 'Y' and not l_changed then
331 						hr_utility.set_location('Found Mandatory and its failed ', 20.1);
332 						l_changed := FALSE;
333 						l_trigger := FALSE;
334 						exit;
335 					 elsif l_chg_mandatory_cd = 'Y' and l_changed then
336 						hr_utility.set_location('Found Mandatory and its passed ', 20.1);
337 						l_changed := TRUE;
338 					--	exit; */
339 					elsif l_chg_mandatory_cd = 'N' and l_changed then
340 						hr_utility.set_location('Found First Non-Mandatory and its passed ', 20.1);
341 						l_changed := TRUE;
342 						l_trigger := TRUE;
343 						exit;
344 					end if;
345 
346 			       	hr_utility.set_location('After the Mandatory code check ',20.05);
347 			       	if l_changed then
348 			       	   hr_utility.set_location('       l_change TRUE ', 20.1);
349 			       	else
350 			       	  hr_utility.set_location('        l_change FALSE ', 20.1);
351 			 	end if;
352 			         --
353      /*  if not l_changed then
354 	 	                 exit;
355              end if; */
356              hr_utility.set_location(' ben_abv_trigger', 40);
357            end loop;
358            hr_utility.set_location('  ben_abv_trigger', 50);
359            l_ptnl_id := 0;
360            l_ovn :=null;
361            if l_trigger then
362              if l_type = 'P' then
363                open le_exists(l_person_id,l_ler_id,l_lf_evt_ocrd_date);
364                fetch le_exists into l_le_exists;
365                if le_exists%notfound then
366                   hr_utility.set_location(' Entering: ben_abv_trigger5', 60);
367 
368                   ben_create_ptnl_ler_for_per.create_ptnl_ler_event
369                   --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
370                   (p_validate => false
371                   ,p_ptnl_ler_for_per_id => l_ptnl_id
372                   ,p_ntfn_dt => l_system_date
373                   ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
374                   ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
375                   ,p_ler_id => l_ler_id
376                   ,p_ler_typ_cd => l_typ_cd
377                   ,p_person_id => l_person_id
378                   ,p_business_group_Id =>p_new.business_group_id
379                   ,p_object_version_number => l_ovn
380                   ,p_effective_date => l_effective_start_date
381                   ,p_dtctd_dt       => l_effective_start_date);
382                end if;
383                close le_exists;
384              elsif l_type = 'R' then
385                hr_utility.set_location(' Entering: ben_abv_trigger5-', 65);
386                open get_contacts(l_person_id);
387                loop
388                   fetch get_contacts into l_hld_person_id;
389                   exit when get_contacts%notfound;
390                   open le_exists(l_hld_person_id,l_ler_id,l_lf_evt_ocrd_date);
391                   fetch le_exists into l_le_exists;
392                   if le_exists%notfound then
393                 hr_utility.set_location(' Entering: ben_abv_trigger5', 60);
394 
395                      ben_create_ptnl_ler_for_per.create_ptnl_ler_event
396                      --ben_ptnl_ler_for_per_api.create_ptnl_ler_for_per
397                      (p_validate => false
398                      ,p_ptnl_ler_for_per_id => l_ptnl_id
399                      ,p_ntfn_dt => l_system_date
400                      ,p_lf_evt_ocrd_dt => l_lf_evt_ocrd_date
401                      ,p_ptnl_ler_for_per_stat_cd => 'DTCTD'
402                      ,p_ler_id => l_ler_id
403                      ,p_ler_typ_cd => l_typ_cd
404                      ,p_person_id => l_hld_person_id
405                      ,p_business_group_Id =>p_new.business_group_id
406                      ,p_object_version_number => l_ovn
407                      ,p_effective_date => l_effective_start_date
408                      ,p_mnl_dt         => null
409                      ,p_dtctd_dt       => l_effective_start_date
410                      ,p_procd_dt       => null
411                      ,p_unprocd_dt     => null
412                      ,p_mnlo_dt        => null
413                      ,p_voidd_dt       => null);
414                   end if;
415                   l_ptnl_id := 0;
416                   l_ovn :=null;
417                   close le_exists;
418                end loop;
419                close get_contacts;
420              end if;
421              --
422              -- reset the variables.
423              --
427       		 l_ovn       := NULL;
424              hr_utility.set_location(' ben_abv_trigger', 40);
425              l_changed   := FALSE;
426              l_trigger   := TRUE;
428              l_effective_start_date := l_session_date;
429              --      l_lf_evt_ocrd_date := l_session_date;
430            end if;
431            close get_ler_col;
432         end loop;
433         hr_utility.set_location('  ben_abv_trigger', 50);
434         close get_ler;
435         hr_utility.set_location('  leaving ben_abv_trigger', 70);
436     exception
437        when others then
438            hr_utility.set_location(sqlerrm, 70);
439            raise;
440     end;
441 
442 end;
443 end ben_abv_ler;