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.PAY_MLS_TRIGGERS

Source


1 PACKAGE BODY pay_mls_triggers AS
2 /* $Header: pymlstrg.pkb 120.1.12020000.2 2012/08/24 12:06:06 ranarra ship $ */
3 
4   procedure pur_ari ( p_user_row_id                in pay_user_rows_f.user_row_id%type,
5                          p_row_low_range_or_name_n    in pay_user_rows_f.row_low_range_or_name%type,
6                          p_row_low_range_or_name_o    in pay_user_rows_f.row_low_range_or_name%type)  IS
7 
8     l_proc varchar2(30) ;
9     l_count binary_integer;
10 
11   begin
12 
13     l_proc  := 'pur_ari' ;
14 
15     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
16 
17     l_count:=  l_pur.count();
18 
19     l_pur(l_count+1).user_row_id                := p_user_row_id;
20     l_pur(l_count+1).row_low_range_or_name_n    := p_row_low_range_or_name_n;
21     l_pur(l_count+1).row_low_range_or_name_o    := p_row_low_range_or_name_o;
22 
23 
24     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
25 
26   exception
27   when others then
28      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
29      raise;
30   end;
31 
32 
33   procedure pur_brd ( p_user_row_id in pay_user_rows_f.user_row_id%type ) IS
34    l_proc varchar2(30) ;
35 
36    l_count binary_integer;
37 
38   begin
39 
40    l_proc  := 'pur_brd' ;
41 
42     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
43 
44     l_count:= l_pur_del.count;
45     l_pur_del(l_count+1) := p_user_row_id ;
46 
47     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
48 
49   exception
50   when others then
51      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
52      raise;
53 
54   end;
55 
56 
57   procedure pur_asi IS
58    l_proc varchar2(30) ;
59 
60    cursor c_pur ( p_user_row_id pay_user_rows_f.user_row_id%type)
61           is
62     select 1
63       from pay_user_rows_f b
64      where b.user_row_id = p_user_row_id
65         and not exists
66          -- this is the first insert ; there is no corresponding row in tl table
67          ( select 1
68               from pay_user_rows_f_tl tl
69             where tl.user_row_id = p_user_row_id ) ;
70      rec_pur c_pur%rowtype;
71   begin
72 
73     l_proc  := 'pur_asi' ;
74 
75     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
76     for i in 1..l_pur.count loop
77       open c_pur(l_pur(i).user_row_id) ;
78       fetch c_pur into rec_pur ;
79       if c_pur%found then
80         pay_urt_ins.ins_tl(P_LANGUAGE_CODE         =>userenv('LANG')
81                           ,P_USER_ROW_ID           => l_pur(i).user_row_id
82                           ,P_ROW_LOW_RANGE_OR_NAME => l_pur(i).row_low_range_or_name_n ) ;
83       end if;
84       close c_pur;
85     end loop;
86 
87     l_pur.delete;
88     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
89 
90   exception
91   when others then
92      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
93      l_pur.delete;
94      raise;
95   end;
96 
97 
98   procedure pur_asd IS
99    l_proc varchar2(30) := 'pur_asd' ;
100    cursor c_pur_del ( p_user_row_id pay_user_rows_f.user_row_id%type )
101        is
102    select 1
103      from pay_user_rows_f_tl p
104     where p.user_row_id = p_user_row_id
105       and not exists
106        ( select 1
107            from pay_user_rows_f p1
108           where p1.user_row_id = p_user_row_id
109 
110         );
111     rec_pur c_pur_del%rowtype;
112 
113   begin
114     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
115       for i in 1..l_pur_del.count loop
116         open c_pur_del(l_pur_del(i)) ;
117         fetch c_pur_del into rec_pur ;
118           if c_pur_del%found then
119             pay_urt_del.del_tl(l_pur_del(i));
120           end if;
121         close c_pur_del ;
122       end loop ;
123     l_pur_del.delete;
124     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
125   exception
126   when others then
127      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
128      l_pur_del.delete;
129      raise;
130   end;
131 
132 
133   procedure pbc_ari (p_balance_category_id     in pay_balance_categories_f.balance_category_id%type,
134                                          p_user_category_name_n  in pay_balance_categories_f.user_category_name%type,
135                                          p_user_category_name_o  in pay_balance_categories_f.user_category_name%type) IS
136    l_proc varchar2(30)  ;
137    l_count binary_integer;
138 
139   begin
140 
141    l_proc  := 'pbc_ari' ;
142     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
143    l_count:= l_pbc.count ;
144 
145    l_pbc(l_count+1).balance_category_id        := p_balance_category_id;
146    l_pbc(l_count+1).user_category_name_n       := p_user_category_name_n;
147    l_pbc(l_count+1).user_category_name_o       := p_user_category_name_o;
148 
149     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
150   exception
151   when others then
152      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
153      raise;
154 
155   end;
156 
157 
158   procedure pbc_brd (p_balance_category_id in pay_balance_categories_f.balance_category_id%type) IS
159    l_proc varchar2(30)  ;
160    l_count binary_integer;
161   begin
162     l_proc  := 'pbc_brd' ;
163 
164     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
165 
166     l_count :=l_pbc_del.count;
167     l_pbc_del( l_count+1 )  :=p_balance_category_id ;
168 
169     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
170   exception
171   when others then
172      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
173      raise;
174   end;
175 
176   procedure pbc_asi  IS
177    l_proc varchar2(30) ;
178    cursor c_pbc ( p_balance_category_id pay_balance_categories_f.balance_category_id%type)
179           is
180     select 1
181       from pay_balance_categories_f b
182      where b.balance_category_id = p_balance_category_id
183         and not exists
184          -- this is the first insert ; there is no corresponding row in tl table
185          ( select 1
186               from pay_balance_categories_f_tl tl
187             where tl.balance_category_id = p_balance_category_id ) ;
188      rec_pbc c_pbc%rowtype;
189   begin
190    l_proc  := 'pbc_asi' ;
191 
192     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
193     for i in 1..l_pbc.count loop
194       open c_pbc(l_pbc(i).balance_category_id);
195       fetch c_pbc into rec_pbc;
196       if c_pbc%found then
197         pay_tbc_ins.ins_tl(P_LANGUAGE_CODE        =>userenv('LANG')
198                           ,P_BALANCE_CATEGORY_ID  =>l_pbc(i).balance_category_id
199                           ,P_USER_CATEGORY_NAME   =>l_pbc(i).user_category_name_n);
200       end if;
201       close c_pbc;
202     end loop ;
203     l_pbc.delete;
204     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
205   exception
206   when others then
207      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
208      l_pbc.delete;
209      raise;
210   end;
211 
212 
213   procedure pbc_asd  IS
214    l_proc varchar2(30)  ;
215    cursor c_pbc_del ( p_balance_category_id pay_balance_categories_f.balance_category_id%type )
216        is
217    select 1
218      from pay_balance_categories_f_tl p
219     where p.balance_category_id = p_balance_category_id
220       and not exists
221        ( select 1
222            from pay_balance_categories_f p1
223           where p1.balance_category_id = p_balance_category_id
224 
225         );
226     rec_pbc c_pbc_del%rowtype;
227   begin
228     l_proc  := 'pbc_asd' ;
229     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
230       for i in 1..l_pbc_del.count loop
231         open c_pbc_del(l_pbc_del(i)) ;
232         fetch c_pbc_del into rec_pbc;
233           if c_pbc_del%found then
234             pay_tbc_del.del_tl( l_pbc_del(i)) ;
235           end if ;
236         close c_pbc_del;
237       end loop ;
238     l_pbc_del.delete;
239     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
240   exception
241   when others then
242      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
243      l_pbc_del.delete;
244      raise;
245   end;
246 
247 
248   procedure glb_ari ( p_global_id            in ff_globals_f.global_id%type,
249                                         p_global_description_o in ff_globals_f.global_description%type,
250                                         p_global_description_n in ff_globals_f.global_description%type,
251                                         p_global_name_o        in ff_globals_f.global_name%type,
252                                        p_global_name_n        in ff_globals_f.global_name%type) IS
253    l_proc varchar2(30) := 'glb_ari' ;
254    l_count binary_integer;
255 
256   begin
257 
258     l_proc  := 'glb_ari' ;
259     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
260 
261     l_count := l_glb.count;
262     l_glb(l_count+1).global_id                    := p_global_id ;
263     l_glb(l_count+1).global_description_o := p_global_description_o ;
264     l_glb(l_count+1).global_description_n := p_global_description_n ;
265     l_glb(l_count+1).global_name_o          := p_global_name_o;
266     l_glb(l_count+1).global_name_n          := p_global_name_n;
267 
268 
269     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
270   exception
271   when others then
272      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
273      raise;
274   end;
275 
276 
277   procedure glb_brd (p_global_id             in ff_globals_f.global_id%type) IS
278    l_proc varchar2(30) ;
279    l_count binary_integer;
280   begin
281 
282     l_proc  := 'glb_brd' ;
283     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
284 
285     l_count := l_glb_del.count;
286     l_glb_del(l_count+1) := p_global_id ;
287 
288     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
289   exception
290   when others then
291      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
292      raise;
293   end;
294 
295 
296   procedure glb_asi  IS
297    l_proc varchar2(30) ;
298     cursor c_glb ( p_global_id ff_globals_f.global_id%type)
299           is
300     select 1
301       from ff_globals_f b
302      where b.global_id = p_global_id
303         and not exists
304          -- this is the first insert; there is no corresponding row in tl table
305          ( select 1
306              from ff_globals_f_tl tl
307             where tl.global_id = p_global_id ) ;
308      rec_glb c_glb%rowtype;
309   begin
310     l_proc := 'glb_asi' ;
311     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
312       for i in 1..l_glb.count loop
313         open c_glb(l_glb(i).global_id) ;
314         fetch c_glb into rec_glb ;
315         if c_glb%found then
316           ff_fgt_ins.ins_tl(  P_LANGUAGE_CODE      => userenv('LANG')
317                               ,P_GLOBAL_ID          => l_glb(i).global_id
318                               ,P_GLOBAL_NAME        => l_glb(i).global_name_n
319                               ,P_GLOBAL_DESCRIPTION => l_glb(i).global_description_n
320                             );
321         end if;
322         close c_glb;
323       end loop ;
324 
325     l_glb.delete ;
326     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
327   exception
328   when others then
329      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
330      l_glb.delete;
331      raise;
332   end;
333 
334 
335   procedure glb_asd  IS
336    l_proc varchar2(30) ;
337    cursor c_glb_del ( p_global_id ff_globals_f.global_id%type )
338        is
339    select 1
340      from ff_globals_f_tl f
341     where f.global_id = p_global_id
342       and not exists
343        ( select 1
344            from ff_globals_f f1
345           where f1.global_id = p_global_id
346 
347         );
348     rec_glb c_glb_del%rowtype;
349 
350   begin
351    l_proc  := 'glb_asd' ;
352 
353     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
354     hr_utility.set_location('table count -'||l_glb_del.count ,10 ) ;
355      for i in 1..l_glb_del.count loop
356        open c_glb_del(l_glb_del(i));
357        fetch c_glb_del into rec_glb;
358        if c_glb_del%found then
359          hr_utility.set_location('found -'||l_glb_del(i) ,10 ) ;
360          ff_fgt_del.del_tl(l_glb_del(i));
361        end if;
362        close c_glb_del;
363      end loop;
364 
365     l_glb_del.delete;
366 
367     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
368   exception
369   when others then
370      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
371      l_glb_del.delete;
372      raise;
373   end;
374 
375 
376   procedure fml_ari ( p_formula_id      in ff_formulas_f.formula_id%type,
377                        p_formula_name_o    in ff_formulas_f.formula_name%type,
378                        p_formula_name_n    in ff_formulas_f.formula_name%type,
379                        p_description_o     in ff_formulas_f.description%type,
380                        p_description_n     in ff_formulas_f.description%type) IS
381    l_proc varchar2(30) ;
382    l_count binary_integer;
383   begin
384    l_proc  := 'fml_ari' ;
385     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
386 
387     l_count := l_fml.count;
388     l_fml(l_count+1).formula_id          :=  p_formula_id;
389     l_fml(l_count+1).formula_name_o      :=  p_formula_name_o;
390     l_fml(l_count+1).formula_name_n      :=  p_formula_name_n;
391     l_fml(l_count+1).description_o       :=  p_description_o;
392     l_fml(l_count+1).description_n       :=  p_description_n;
393 
394     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
395 
396   exception
397   when others then
398      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
399      raise;
400   end;
401 
402 
403   procedure fml_brd (p_formula_id       in ff_formulas_f.formula_id%type) IS
404    l_proc varchar2(30) ;
405    l_count binary_integer;
406   begin
407     l_proc  := 'fml_brd' ;
408     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
409 
410     l_count := l_fml_del.count;
411     l_fml_del(l_count+1 ) := p_formula_id ;
412 
413     hr_utility.set_location('count   -'||l_count,50 ) ;
414     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
415 
416   exception
417   when others then
418      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
419      raise;
420   end;
421 
422 
423   procedure fml_asi  IS
424    l_proc varchar2(30)  ;
425    cursor c_fml (p_formula_id ff_formulas_f.formula_id%type )
426        is
427    select 1
428      from ff_formulas_f b
429     where b.formula_id = p_formula_id
430       and not exists
431        -- this is the first insert ; there is no corresponding row in tl table
432        ( select 1
433            from ff_formulas_f_tl tl
434           where tl.formula_id = p_formula_id ) ;
435    rec_fml c_fml%rowtype;
436   begin
437    l_proc := 'fml_asi' ;
438 
439    hr_utility.set_location('Entering -'||l_proc ,05 ) ;
440    for i in 1..l_fml.count loop
441      open c_fml (l_fml(i).formula_id) ;
442      fetch c_fml into rec_fml;
443      if c_fml%found then
444        ff_fft_ins.ins_tl(  P_LANGUAGE_CODE=>userenv('LANG')
445                           ,P_FORMULA_ID   =>l_fml(i).formula_id
446                           ,P_FORMULA_NAME =>l_fml(i).formula_name_n
447                           ,P_DESCRIPTION  =>l_fml(i).description_n
448                          );
449 
450      end if;
451      close c_fml;
452    end loop;
453 
454    l_fml.delete;
455    hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
456 
457   exception
458   when others then
459      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
460      l_fml.delete;
461      raise;
462   end;
463 
464 
465 
466   procedure fml_asd  IS
467    l_proc varchar2(30) ;
468    cursor c_fml_del ( p_formula_id ff_formulas_f.formula_id%type )
469        is
470    select 1
471      from ff_formulas_f_tl f
472     where f.formula_id = p_formula_id
473       and not exists
474        ( select 1
475            from ff_formulas_f f1
476           where f1.formula_id = p_formula_id
477 
478         );
479     rec_fml c_fml_del%rowtype;
480   begin
481     l_proc := 'fml_asd' ;
482     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
483     for i in 1..l_fml_del.count loop
484      open c_fml_del (l_fml_del(i)) ;
485      fetch c_fml_del into rec_fml;
486      if c_fml_del%found then
487        ff_fft_del.del_tl(  P_FORMULA_ID  =>l_fml_del(i)  );
488 
489      end if;
490      close c_fml_del;
491     end loop ;
492     l_fml_del.delete;
493     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
494 
495   exception
496   when others then
497      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
498      l_fml_del.delete;
499      raise;
500   end;
501 
502   ----For Bug:4372098--
503   procedure set_dml_status (status in varchar2)  IS
504 
505     l_proc varchar2(30) ;
506 
507   begin
508 
509     l_proc  := 'set_dml_status' ;
510 
511     hr_utility.set_location('Entering -'||l_proc ,05 ) ;
512 
513     ff_formulas_f_pkg.g_dml_status := hr_general.char_to_bool(status);
514 
515     if(status = 'TRUE') then
516       hr_general.g_data_migrator_mode := 'Y';
517     elsif(status='FALSE') then
518       hr_general.g_data_migrator_mode := 'N';
519     end if;
520 
521     hr_utility.set_location('Leaving  -'||l_proc,50 ) ;
522 
523   exception
524   when others then
525      hr_utility.set_location('Error  -'||substr(sqlerrm,1,150),99 ) ;
526      raise;
527   end;
528   ---
529 
530 END pay_mls_triggers ;
531 
532