[Home] [Help]
PACKAGE BODY: APPS.PER_RI_WORKBENCH_PKG
Source
1 Package Body per_ri_workbench_pkg As
2 /* $Header: perriwkb.pkb 120.3.12010000.2 2008/09/11 06:52:43 psengupt ship $ */
3 Procedure load_workbench_item_row
4 ( p_workbench_item_code In Varchar2
5 ,p_workbench_item_name In Varchar2
6 ,p_workbench_item_description In Varchar2
7 ,p_menu_name In Varchar2
8 ,p_workbench_item_sequence In Number
9 ,p_workbench_parent_item_code In Varchar2
10 ,p_workbench_item_creation_date In Date
11 ,p_workbench_item_type In Varchar2
12 ,p_effective_date In Date
13 ) Is
14
15 Cursor csr_wbi Is
16 Select object_version_number ovn
17 From per_ri_workbench_items_vl
18 Where workbench_item_code = p_workbench_item_code ;
19
20 Cursor csr_menu Is
21 Select menu_id
22 From fnd_menus
23 Where menu_name = p_menu_name;
24
25 l_menu_id Number;
26 l_ovn Number(9);
27
28 Begin
29 Open csr_wbi;
30 Fetch csr_wbi Into l_ovn;
31
32 Open csr_menu;
33 Fetch csr_menu Into l_menu_id;
34 Close csr_menu;
35
36 If csr_wbi%NotFound Then
37 PER_RI_WORKBENCH_ITEM_API.CREATE_WORKBENCH_ITEM(
38 P_VALIDATE => FALSE
39 ,P_WORKBENCH_ITEM_CODE => p_workbench_item_code
40 ,P_WORKBENCH_ITEM_NAME => p_workbench_item_name
41 ,P_WORKBENCH_ITEM_DESCRIPTION => p_workbench_item_description
42 ,P_MENU_ID => l_menu_id
43 ,P_WORKBENCH_ITEM_SEQUENCE => p_workbench_item_sequence
44 ,P_WORKBENCH_PARENT_ITEM_CODE => p_workbench_parent_item_code
45 ,P_WORKBENCH_ITEM_CREATION_DATE => p_workbench_item_creation_date
46 ,P_WORKBENCH_ITEM_TYPE => p_workbench_item_type
47 ,P_EFFECTIVE_DATE => p_effective_date
48 ,P_OBJECT_VERSION_NUMBER => l_ovn
49 );
50 Else
51
52 PER_RI_WORKBENCH_ITEM_API.UPDATE_WORKBENCH_ITEM(
53 P_VALIDATE => FALSE
54 ,P_WORKBENCH_ITEM_CODE => p_workbench_item_code
55 ,P_WORKBENCH_ITEM_NAME => p_workbench_item_name
56 ,P_WORKBENCH_ITEM_DESCRIPTION => p_workbench_item_description
57 ,P_MENU_ID => l_menu_id
58 ,P_WORKBENCH_ITEM_SEQUENCE => p_workbench_item_sequence
59 ,P_WORKBENCH_PARENT_ITEM_CODE => p_workbench_parent_item_code
60 ,P_WORKBENCH_ITEM_TYPE => p_workbench_item_type
61 ,P_EFFECTIVE_DATE => p_effective_date
62 ,P_OBJECT_VERSION_NUMBER => l_ovn
63 );
64 End If;
65
66 close csr_wbi;
67 End load_workbench_item_row;
68
69 Procedure translate_workbench_item_row(p_workbench_item_code Varchar2
70 ,p_workbench_item_name Varchar2
71 ,p_workbench_item_description Varchar2
72 ) Is
73 Begin
74
75 per_wbt_upd.upd_tl
76 (p_workbench_item_code => p_workbench_item_code
77 ,p_workbench_item_name => p_workbench_item_name
78 ,p_workbench_item_description => p_workbench_item_description
79 ,p_language_code => userenv('LANG')
80 );
81
82 End translate_workbench_item_row;
83
84 Procedure load_setup_task_row
85 (p_setup_task_code In varchar2
86 ,p_workbench_item_code in varchar2
87 ,p_setup_task_name In Varchar2
88 ,p_setup_task_description In Varchar2
89 ,p_setup_task_sequence in number
90 ,p_setup_task_status in varchar2
91 ,p_setup_task_creation_date in date
92 ,p_setup_task_last_mod_date in date
93 ,p_setup_task_type in varchar2
94 ,p_setup_task_action in varchar2
95 ,p_effective_date in date
96 ) Is
97 Cursor csr_st Is
98 Select object_version_number ovn
99 From per_ri_setup_tasks
100 Where setup_task_code = p_setup_task_code ;
101
102 l_ovn Number(9);
103
104 Begin
105 Open csr_st;
106 Fetch csr_st Into l_ovn;
107
108 If csr_st%NotFound Then
109
110 per_ri_setup_task_api.create_setup_task(
111 p_validate => FALSE
112 ,p_setup_task_code => p_setup_task_code
113 ,p_workbench_item_code => p_workbench_item_code
114 ,p_setup_task_name => p_setup_task_name
115 ,p_setup_task_description => p_setup_task_description
116 ,p_setup_task_sequence => p_setup_task_sequence
117 ,p_setup_task_status => p_setup_task_status
118 ,p_setup_task_creation_date => p_setup_task_creation_date
119 ,p_setup_task_last_mod_date => p_setup_task_last_mod_date
120 ,p_setup_task_type => p_setup_task_type
121 ,p_setup_task_action => p_setup_task_action
122 ,p_effective_date => p_effective_date
123 ,p_object_version_number => l_ovn
124 );
125
126
127
128 Else
129
130 per_ri_setup_task_api.update_setup_task(
131 p_validate => FALSE
132 ,p_setup_task_code => p_setup_task_code
133 ,p_workbench_item_code => p_workbench_item_code
134 ,p_setup_task_name => p_setup_task_name
135 ,p_setup_task_description => p_setup_task_description
136 ,p_setup_task_sequence => p_setup_task_sequence
137 ,p_setup_task_type => p_setup_task_type
138 ,p_setup_task_action => p_setup_task_action
139 ,p_effective_date => p_effective_date
140 ,p_object_version_number => l_ovn
141 );
142
143
144 End If;
145
146
147 Close csr_st;
148
149
150 End;
151
152 Procedure translate_setup_task_row
153 (p_setup_task_code In varchar2
154 ,p_setup_task_name In Varchar2
155 ,p_setup_task_description In Varchar2
156 ) Is
157 Begin
158 per_stl_upd.upd_tl
159 ( p_setup_task_code => p_setup_task_code
160 ,p_setup_task_name => p_setup_task_name
161 ,p_setup_task_description => p_setup_task_description
162 ,p_language_code => userenv('LANG')
163 );
164
165 End;
166
167 Procedure load_setup_sub_task_row
168 (p_setup_sub_task_code In Varchar2
169 ,p_setup_sub_task_name In Varchar2
170 ,p_setup_sub_task_description In Varchar2
171 ,p_setup_task_code In Varchar2
172 ,p_setup_sub_task_sequence In Number
173 ,p_setup_sub_task_status In Varchar2
174 ,p_setup_sub_task_type In Varchar2
175 ,p_setup_sub_task_dp_link In Varchar2
176 ,p_setup_sub_task_action In Varchar2
177 ,p_setup_sub_task_creation_date In Date
178 ,p_setup_sub_task_last_mod_date In Date
179 ,p_legislation_code In Varchar2
180 ,p_effective_date In Date
181 ) Is
182 Cursor csr_sst Is
183 Select object_version_number ovn
184 From per_ri_setup_sub_tasks
185 Where setup_sub_task_code = p_setup_sub_task_code ;
186
187 l_ovn Number(9);
188
189 Begin
190 Open csr_sst;
191 Fetch csr_sst Into l_ovn;
192
193 If csr_sst%NotFound Then
194
195 per_ri_setup_sub_task_api.create_setup_sub_task(
196 p_setup_sub_task_code => p_setup_sub_task_code
197 ,p_setup_sub_task_name => p_setup_sub_task_name
198 ,p_setup_sub_task_description => p_setup_sub_task_description
199 ,p_setup_task_code => p_setup_task_code
200 ,p_setup_sub_task_sequence => p_setup_sub_task_sequence
201 ,p_setup_sub_task_status => p_setup_sub_task_status
202 ,p_setup_sub_task_type => p_setup_sub_task_type
203 ,p_setup_sub_task_dp_link => p_setup_sub_task_dp_link
204 ,p_setup_sub_task_action => p_setup_sub_task_action
205 ,p_setup_sub_task_creation_date => p_setup_sub_task_creation_date
206 ,p_setup_sub_task_last_mod_date => p_setup_sub_task_last_mod_date
207 ,p_legislation_code => p_legislation_code
208 ,p_effective_date => p_effective_date
209 ,p_object_version_number => l_ovn
210 ) ;
211
212
213
214 Else
215
216 per_ri_setup_sub_task_api.update_setup_sub_task(
217 p_setup_sub_task_code => p_setup_sub_task_code
218 ,p_setup_sub_task_name => p_setup_sub_task_name
219 ,p_setup_sub_task_description => p_setup_sub_task_description
220 ,p_setup_task_code => p_setup_task_code
221 ,p_setup_sub_task_sequence => p_setup_sub_task_sequence
222 ,p_setup_sub_task_type => p_setup_sub_task_type
223 ,p_setup_sub_task_dp_link => p_setup_sub_task_dp_link
224 ,p_setup_sub_task_action => p_setup_sub_task_action
225 ,p_legislation_code => p_legislation_code
226 ,p_effective_date => p_effective_date
227 ,p_object_version_number => l_ovn
228 ) ;
229 End If;
230
231
232 Close csr_sst;
233 End;
234
235 Procedure translate_setup_sub_task_row
236 (p_setup_sub_task_code In varchar2
237 ,p_setup_sub_task_name In Varchar2
238 ,p_setup_sub_task_description In Varchar2
239 ) Is
240 Begin
241
242 per_sst_upd.upd_tl
243 ( p_setup_sub_task_code => p_setup_sub_task_code
244 ,p_setup_sub_task_name => p_setup_sub_task_name
245 ,p_setup_sub_task_description => p_setup_sub_task_description
246 ,p_language_code => userenv('LANG')
247 );
248
249 End;
250
251 Procedure load_view_report_row
252 (p_workbench_view_report_code In Varchar2
253 ,p_workbench_view_report_name In Varchar2
254 ,p_wb_view_report_description In Varchar2
255 ,p_workbench_item_code In Varchar2
256 ,p_workbench_view_report_type In Varchar2
257 ,p_workbench_view_report_action In Varchar2
258 ,p_workbench_view_country In Varchar2
259 ,p_wb_view_report_instruction In Varchar2
260 ,p_effective_date In Date
261 ,p_primary_industry In Varchar2
262 ,p_enabled_flag In Varchar2 default 'Y'
263 ) Is
264 Cursor csr_vr Is
265 Select object_version_number ovn
266 From per_ri_view_reports
267 Where workbench_view_report_code = p_workbench_view_report_code ;
268
269 l_ovn Number(9);
270
271 Begin
272 Open csr_vr;
273 Fetch csr_vr Into l_ovn;
274
275 If csr_vr%NotFound Then
276
277 per_ri_view_report_api.create_view_report( p_workbench_view_report_code => p_workbench_view_report_code
278 ,p_workbench_view_report_name => p_workbench_view_report_name
279 ,p_wb_view_report_description => p_wb_view_report_description
280 ,p_workbench_item_code => p_workbench_item_code
281 ,p_workbench_view_report_type => p_workbench_view_report_type
282 ,p_workbench_view_report_action => p_workbench_view_report_action
283 ,p_workbench_view_country => p_workbench_view_country
284 ,p_wb_view_report_instruction => p_wb_view_report_instruction
285 ,p_language_code => userenv('LANG')
286 ,p_effective_date => p_effective_date
287 ,p_object_version_number => l_ovn
288 ,p_primary_industry => p_primary_industry
289 ,p_enabled_flag => p_enabled_flag);
290
291
292 Else
293
294 per_ri_view_report_api.update_view_report(p_workbench_view_report_code => p_workbench_view_report_code
295 ,p_workbench_view_report_name => p_workbench_view_report_name
296 ,p_wb_view_report_description => p_wb_view_report_description
297 ,p_workbench_item_code => p_workbench_item_code
298 ,p_workbench_view_report_type => p_workbench_view_report_type
299 ,p_workbench_view_report_action => p_workbench_view_report_action
300 ,p_workbench_view_country => p_workbench_view_country
301 ,p_wb_view_report_instruction => p_wb_view_report_instruction
302 ,p_language_code => userenv('LANG')
303 ,p_effective_date => p_effective_date
304 ,p_object_version_number => l_ovn
305 ,p_primary_industry => p_primary_industry
306 ,p_enabled_flag => p_enabled_flag);
307
308
309 End If;
310
311 Close csr_vr;
312 End;
313
314 Procedure translate_view_report_row
315 (p_workbench_view_report_code In Varchar2
316 ,p_workbench_view_report_name In Varchar2
317 ,p_wb_view_report_description In Varchar2
318 ) Is
319
320 Begin
321
322 per_rvt_upd.upd_tl
323 (p_workbench_view_report_code => p_workbench_view_report_code
324 ,p_workbench_view_report_name => p_workbench_view_report_name
325 ,p_wb_view_report_description => p_wb_view_report_description
326 ,p_language_code => userenv('LANG')
327 ) ;
328 End translate_view_report_row;
329
330 Procedure load_workbench_item_dependency
331 (p_workbench_item_code In Varchar2
332 ,p_dependent_item_code In Varchar2
333 ,p_dependency_item_sequence In Number
334 ) Is
335
336 Cursor csr_dep Is
337 Select 1
338 From per_ri_dependencies
339 where workbench_item_code = p_workbench_item_code
340 and dependent_item_code = p_dependent_item_code ;
341
342 l_temp Number;
343
344 Begin
345
346 Open csr_dep;
347 Fetch csr_dep Into l_temp;
348 If csr_dep%NotFound Then
349 Insert Into per_ri_dependencies(workbench_item_code,dependent_item_code,dependency_item_sequence)
350 values(p_workbench_item_code,p_dependent_item_code,p_dependency_item_sequence) ;
351 Else
352 update per_ri_dependencies
353 set dependency_item_sequence = p_dependency_item_sequence
354 where workbench_item_code = p_workbench_item_code
355 and dependent_item_code = p_dependent_item_code ;
356 End If;
357
358
359 End load_workbench_item_dependency;
360
361 Procedure sync_dp_user_keys
362 (p_setup_sub_task_code In Varchar2
363 ,p_business_group_id In Number
364 ) Is
365
366 Cursor csr_job_group_id Is
367 Select pjg.internal_name , pjg.job_group_id
368 From per_job_groups pjg
369 Where not exists
370 (Select 1 From hr_pump_batch_line_user_keys uk where uk.user_key_value = 'RI~JOB_GROUP_NAME~'||pjg.internal_name)
371 and (pjg.business_group_id Is Null or pjg.business_group_id = p_business_group_id );
372
373 Cursor csr_benchmark_job_id Is
374 Select pj.name, pj.job_id
375 From per_jobs pj
376 where not exists
377 (Select 1 From hr_pump_batch_line_user_keys uk where uk.user_key_value = 'RI~BENCHMARK_JOB_NAME~'||pj.name)
378 and pj.business_group_id = p_business_group_id
379 and pj.benchmark_job_flag='Y';
380
381 Begin
382
383 If p_setup_sub_task_code = 'JOB' or p_setup_sub_task_code = 'JOB_CRPFLOW' Then
384
385 --1.Job Group
386 For i In csr_job_group_id Loop
387
388 hr_pump_utils.add_user_key(p_user_key_value =>substr('RI~JOB_GROUP_NAME~'||i.internal_name,1,240)
389 ,p_unique_key_id => i.job_group_id
390 );
391
392 End Loop;
393
394 --2.Benchmark Job
395 For i in csr_benchmark_job_id Loop
396
397 hr_pump_utils.add_user_key(p_user_key_value =>substr('RI~BENCHMARK_JOB_NAME~'||i.name,1,240)
398 ,p_unique_key_id =>i.job_id
399 );
400 End Loop;
401
402 End If;
403
404 End sync_dp_user_keys;
405
406 Procedure set_id_flex_num
407 (p_setup_sub_task_code In Varchar2
408 ,p_business_group_id In Number
409 ) Is
410 begin
411 if p_setup_sub_task_code = 'JOB' then
412 update bne_interface_cols_b
413 set oa_flex_num = (select job_structure FROM per_business_groups WHERE business_group_id = p_business_group_id )
414 where interface_code like 'PER_RI_JOB_INTF' and val_type = 'KEYFLEX' and application_id = 800;
415 elsif p_setup_sub_task_code = 'POSITION' then
416 update bne_interface_cols_b
417 set oa_flex_num = (select position_structure FROM per_business_groups WHERE business_group_id = p_business_group_id )
418 where interface_code like 'PER_RI_POSITION_INTF' and val_type = 'KEYFLEX' and application_id = 800;
419 elsif p_setup_sub_task_code = 'GRADE' then
420 update bne_interface_cols_b
421 set oa_flex_num = (select grade_structure FROM per_business_groups WHERE business_group_id = p_business_group_id )
422 where interface_code like 'PER_RI_GRADE_INTF' and val_type = 'KEYFLEX' and application_id = 800;
423 end if;
424 end;
425
426 Procedure crp_insert_request
427 (p_setup_task_code In varchar2,
428 p_context_code In varchar2,
429 p_request_id In varchar2,
430 p_bg_id In number)
431
432 is
433
434 begin
435
436 insert into
437 PER_RI_REQUESTS(SETUP_TASK_CODE,REQUEST_ID,CONTEXT_CODE,BUSINESS_GROUP_ID)
438 values (p_setup_task_code,to_number(p_request_id),p_context_code,p_bg_id);
439
440
441 end crp_insert_request;
442
443 Function chk_dff_structure(p_flexfield_name Varchar2
444 ,p_legislation_code Varchar2
445 ,p_app_id Varchar2
446 )
447 Return Varchar2 Is
448
449 Cursor csr_get_structure Is
450 Select descriptive_flex_context_code
451 From fnd_descr_flex_contexts
452 Where descriptive_flexfield_name = p_flexfield_name
453 and (descriptive_flex_context_code = p_legislation_code or descriptive_flex_context_code = p_legislation_code || '_GLB')
454 and enabled_flag = 'Y'
455 and application_id = p_app_id ;
456
457
458 l_context Varchar2(30) := ' ';
459
460 Begin
461 Open csr_get_structure;
462 Fetch csr_get_structure Into l_context;
463 Close csr_get_structure;
464
465 Return l_context;
466
467 End chk_dff_structure;
468
469 End per_ri_workbench_pkg;