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

Source


4 procedure cre_or_sel_att_form_function(p_function_name       IN     VARCHAR2
1 PACKAGE BODY HR_ATTACHMENTS_LOADER as
2 /* $Header: hratload.pkb 115.1 99/10/12 07:04:38 porting ship $ */
3 --
5                                       ,p_function_type       IN     VARCHAR2
6                                       ,p_attachment_function_id OUT NUMBER
7                                       ,p_application_id         OUT NUMBER) is
8 --
9 cursor function_id is
10 select form_id func_id
11 , application_id app_id
12 from fnd_form_vl
13 where form_name=p_function_name
14 and p_function_type='O'
15 UNION
16 select function_id func_id
17 , application_id app_is
18 from fnd_form_functions_vl
19 where function_name=p_function_name
20 and p_function_type='F';
21 --
22 cursor att_func_exists(p_function_id NUMBER) is
23 select attachment_function_id
24 from fnd_attachment_functions
25 where function_type=p_function_type
26 and function_id=p_function_id;
27 --
28 l_function_id NUMBER;
29 l_application_id NUMBER;
30 l_attachment_function_id NUMBER;
31 --
32 begin
33 -- look to see if the form function exists
34 open function_id;
35 fetch function_id into l_function_id,l_application_id;
36 if(function_id%found) then
37   close function_id;
38 -- if the function exists, look to see if the attachment function exists
39   open att_func_exists(l_function_id);
40   fetch att_func_exists into l_attachment_function_id;
41   if (att_func_exists%NOTFOUND) then
42     close att_func_exists;
43 -- if it doesn't exist then add it
44     select fnd_attachment_functions_s.nextval
45     into l_attachment_function_id
46     from sys.dual;
47 --
48     insert into fnd_attachment_functions (
49     attachment_function_id,
50     function_type,
51     function_id,
52     function_name,
53     creation_date,
54     created_by,
55     last_update_date,
56     last_updated_by,
57     last_update_login,
58     application_id,
59     session_context_field,
60     enabled_flag) VALUES (
61     l_attachment_function_id,
62     p_function_type,
63     l_function_id,
64     p_function_name,
65     sysdate,
66     1,
67     sysdate,
68     1,
69     1,
70     l_application_id,
71     '',
72     'Y');
73     hr_utility.set_location('Added attachmnent for '||p_function_name,10);
74   else
75     close att_func_exists;
76     hr_utility.set_location('Attachmnent exists for '||p_function_name,20);
77   end if;
78   p_attachment_function_id:=l_attachment_function_id;
79   p_application_id:=l_application_id;
80 else
81   close function_id;
82   hr_utility.set_location('Couldnt find function '||p_function_name,30);
83   p_attachment_function_id:=null;
84   p_application_id:=null;
85 end if;
86 end cre_or_sel_att_form_function;
87 --
88 procedure associate_category(p_attachment_function_id IN NUMBER
89                             ,p_category_name          IN VARCHAR2) is
90 --
91 cursor att_func_exists is
92 select 1
93 from fnd_attachment_functions
94 where attachment_function_id=p_attachment_function_id;
95 --
96 cursor categories is
97 select category_id
98 from fnd_doc_categories_active_vl
99 where name=p_category_name;
100 --
101 cursor category_exists(p_category_id NUMBER) is
102 select doc_category_usage_id
103 from fnd_doc_category_usages
104 where attachment_function_id=p_attachment_function_id
105 and category_id=p_category_id;
106 --
107 l_category_id NUMBER;
108 l_dummy NUMBER;
109 l_doc_category_usages_id NUMBER;
110 --
111 begin
112 -- look to see if the attachment function exists
113   open att_func_exists;
114   fetch att_func_exists into l_dummy;
115   if(att_func_exists%found) then
116   close att_func_exists;
117 --
118 -- look to see it the category exists
119 -- insert the document categories;
120   open categories;
121   fetch categories into l_category_id;
122   if(categories%found) then
123     close categories;
124 -- look to see if the category is associated with the attachment
125     open category_exists(l_category_id);
126     fetch category_exists into l_doc_category_usages_id;
127     if(category_exists%NOTFOUND) then
128       close category_exists;
129  -- if not associated, then add it
130       select fnd_doc_category_usages_s.nextval
131       into l_doc_category_usages_id
132       from sys.dual;
133 --
134       INSERT INTO fnd_doc_category_usages(
135       doc_category_usage_id,
136       category_id,
137       attachment_function_id,
138       enabled_flag,
139       creation_date,
140       created_by,
141       last_update_date,
142       last_updated_by,
143       last_update_login) VALUES (
144       l_doc_category_usages_id,
145       l_category_id,
146       p_attachment_function_id,
147       'Y',
148       sysdate,
149       1,
150       sysdate,
151       1,
152       1);
153       hr_utility.set_location('Attached category '||P_category_name,10);
154     else
155       close category_exists;
156       hr_utility.set_location('Had category '||P_category_name,20);
157     end if;
158   else
159     close categories;
160     hr_utility.set_location('Couldnt find category '||P_category_name,30);
161   end if;
162   else
163   close att_func_exists;
164   hr_utility.set_location('Couldnt find function ',40);
165   end if;
166 end associate_category;
167 
168 procedure create_or_update_block
169           (p_attachment_function_id  IN     NUMBER
170           ,p_block_name              IN     VARCHAR2
171           ,p_query_flag              IN     VARCHAR2 default 'N'
172           ,p_security_type           IN     NUMBER   default 4
173           ,p_org_context_field       IN     VARCHAR2 default null
174           ,p_set_of_books_context_field  IN VARCHAR2 default null
175           ,p_business_unit_context_field IN VARCHAR2 default null
176           ,p_context1_field          IN     VARCHAR2 default null
177           ,p_context2_field          IN     VARCHAR2 default null
178           ,p_context3_field          IN     VARCHAR2 default null
179           ,p_attachment_blk_id          OUT NUMBER) is
180 --
181 cursor att_func_exists is
182 select 1
183 from fnd_attachment_functions
184 where attachment_function_id=p_attachment_function_id;
185 --
186 cursor block_exists is
187 select attachment_blk_id
188 from fnd_attachment_blocks
189 where block_name=p_block_name
190 and attachment_function_id=p_attachment_function_id;
191 --
192 l_attachment_blk_id NUMBER;
193 l_dummy NUMBER;
194 --
195 begin
196 -- check to see if the attachment function exists
197   open att_func_exists;
198   fetch att_func_exists into l_dummy;
199   if(att_func_exists%found) then
200   close att_func_exists;
201 --
202 -- look to see of the block already exists
203   open block_exists;
204   fetch block_exists into l_attachment_blk_id;
205   if(block_exists%NOTFOUND) then
206     close block_exists;
207 --  if it doesn't then add it
208     select fnd_attachment_blocks_s.nextval
209     into l_attachment_blk_id
210     from sys.dual;
211 --
212     INSERT INTO fnd_attachment_blocks (
213     attachment_blk_id,
214     attachment_function_id,
215     block_name,
216     query_flag,
217     security_type,
218     creation_date,
219     created_by,
220     last_update_date,
221     last_updated_by,
222     last_update_login,
223     org_context_field,
224     set_of_books_context_field,
225     business_unit_context_field,
226     context1_field,
227     context2_field,
228     context3_field) VALUES (
229     l_attachment_blk_id,
230     p_attachment_function_id,
231     p_block_name,
232     p_query_flag,
233     p_security_type,
234     sysdate,
235     1,
236     sysdate,
237     1,
238     1,
239     p_org_context_field,
240     p_set_of_books_context_field,
241     p_business_unit_context_field,
242     p_context1_field,
243     p_context2_field,
244     p_context3_field);
245     p_attachment_blk_id:=l_attachment_blk_id;
246     hr_utility.set_location('Added block '||p_block_name,10);
247   else
248     close block_exists;
249 -- if it does exist then update it
250     update fnd_attachment_blocks
251     set
252     query_flag=p_query_flag,
253     security_type=p_security_type,
254     creation_date=sysdate,
255     created_by=1,
256     last_update_date=sysdate,
257     last_updated_by=1,
258     last_update_login=1,
259     org_context_field=p_org_context_field,
260     set_of_books_context_field=p_set_of_books_context_field,
261     business_unit_context_field=p_business_unit_context_field,
262     context1_field=p_context1_field,
263     context2_field=p_context2_field,
264     context3_field=p_context3_field
265     where attachment_blk_id=l_attachment_blk_id;
266     p_attachment_blk_id:=l_attachment_blk_id;
267     hr_utility.set_location('Updated block '||p_block_name,20);
268   end if;
269   else
270   close att_func_exists;
271   hr_utility.set_location('Couldnt find function ',30);
272   p_attachment_blk_id:=null;
273   end if;
274 --
275 end create_or_update_block;
276 --
277 procedure create_or_select_entity
278           (p_data_object_code IN     VARCHAR2
279           ,p_entity_user_name IN     VARCHAR2 default null
280           ,p_language_code    IN     VARCHAR2 default null
281           ,p_application_id   IN     NUMBER   default null
282           ,p_table_name       IN     VARCHAR2 default null
283           ,p_entity_name      IN     VARCHAR2 default null
284           ,p_pk1_column       IN     VARCHAR2 default null
285           ,p_pk2_column       IN     VARCHAR2 default null
286           ,p_pk3_column       IN     VARCHAR2 default null
287           ,p_pk4_column       IN     VARCHAR2 default null
288           ,p_pk5_column       IN     VARCHAR2 default null
289           ,p_document_entity_id  OUT NUMBER) is
290 
291 --
292 cursor entity_exists is
293 select document_entity_id
294 from fnd_document_entities
295 where data_object_code =p_data_object_code;
296 --
297 l_document_entity_id NUMBER;
298 --
299 begin
300 -- look to see if the entity is already defined
301   open entity_exists;
302   fetch entity_exists into l_document_entity_id;
303   if(entity_exists%NOTFOUND) then
304     close entity_exists;
305 --  if not then add it
306     select fnd_document_entities_s.nextval
307     into l_document_entity_id
308     from sys.dual;
309 --
310     insert into fnd_document_entities (
311     DOCUMENT_ENTITY_ID,
312     DATA_OBJECT_CODE,
313     APPLICATION_ID,
314     TABLE_NAME,
315     ENTITY_NAME,
316     CREATION_DATE,
317     CREATED_BY,
318     LAST_UPDATE_DATE,
319     LAST_UPDATED_BY,
320     LAST_UPDATE_LOGIN,
321     PK1_COLUMN,
322     PK2_COLUMN,
323     PK3_COLUMN,
324     PK4_COLUMN,
325     PK5_COLUMN ) VALUES (
326     l_document_entity_id,
327     p_data_object_code,
328     p_application_id,
329     p_table_name,
330     p_entity_name,
331     sysdate,
332     1,
333     sysdate,
334     1,
335     1,
336     p_pk1_column,
337     p_pk2_column,
338     p_pk3_column,
339     p_pk4_column,
340     p_pk5_column);
341 -- and add the translation part
342     insert into fnd_document_entities_tl (
343     DOCUMENT_ENTITY_ID,
344     DATA_OBJECT_CODE,
345     LANGUAGE,
346     USER_ENTITY_NAME,
347     USER_ENTITY_PROMPT,
348     CREATION_DATE,
349     CREATED_BY,
350     LAST_UPDATE_DATE,
351     LAST_UPDATED_BY,
352     LAST_UPDATE_LOGIN,
353     SOURCE_LANG) VALUES (
354     l_document_entity_id,
355     p_data_object_code,
356     p_language_code,
357     p_entity_user_name,
358     p_entity_user_name,
359     sysdate,
360     1,
361     sysdate,
362     1,
363     1,
364     p_language_code);
365     hr_utility.set_location('Added entity '||p_data_object_code,10);
366     p_document_entity_id:=l_document_entity_id;
367   else
368     close entity_exists;
369     hr_utility.set_location('Found entity '||p_data_object_code,20);
370     p_document_entity_id:=l_document_entity_id;
371   end if;
372 end create_or_select_entity;
373 --
374 procedure attach_entity
375           (p_attachment_blk_id      IN     NUMBER
376           ,p_data_object_code       IN     VARCHAR2
377           ,p_display_method         IN     VARCHAR2 default 'M'
378           ,p_include_in_indicator_flag IN  VARCHAR2 default 'Y'
379           ,p_indicator_in_view_flag IN     VARCHAR2 default 'N'
380           ,p_pk1_field              IN     VARCHAR2 default null
381           ,p_pk2_field              IN     VARCHAR2 default null
382           ,p_pk3_field              IN     VARCHAR2 default null
383           ,p_pk4_field              IN     VARCHAR2 default null
384           ,p_pk5_field              IN     VARCHAR2 default null
385           ,p_sql_statement          IN     VARCHAR2 default null
386           ,p_query_permission_type  IN     VARCHAR2 default 'Y'
387           ,p_insert_permission_type IN     VARCHAR2 default 'Y'
388           ,p_update_permission_type IN     VARCHAR2 default 'Y'
389           ,p_delete_permission_type IN     VARCHAR2 default 'Y'
390           ,p_condition_field        IN     VARCHAR2 default null
391           ,p_condition_operator     IN     VARCHAR2 default null
392           ,p_condition_value1       IN     VARCHAR2 default null
393           ,p_condition_value2       IN     VARCHAR2 default null
394           ,p_attachment_blk_entity_id  OUT NUMBER) is
395 --
396 cursor block_exists is
397 select 1
398 from fnd_attachment_blocks
399 where attachment_blk_id=p_attachment_blk_id;
400 --
401 cursor block_entity_exists is
402 select attachment_blk_entity_id
403 from fnd_attachment_blk_entities
404 where data_object_code=p_data_object_code
405 and attachment_blk_id=p_attachment_blk_id;
406 --
407 l_attachment_blk_entity_id NUMBER;
408 l_dummy NUMBER;
409 --
410 begin
411 -- check that the block exists
412   open block_exists;
413   fetch block_exists into l_dummy;
414   if(block_exists%found) then
415   close block_exists;
416 -- look to see if the entity is already associcated with the block
417   open block_entity_exists;
418   fetch block_entity_exists into l_attachment_blk_entity_id;
419   if(block_entity_exists%NOTFOUND) then
420     close block_entity_exists;
421 -- if it is not then add it
422     select fnd_attachment_blk_entities_s.nextval
423     into l_attachment_blk_entity_id
424     from sys.dual;
425 --
426     INSERT INTO fnd_attachment_blk_entities (
427     attachment_blk_entity_id,
428     attachment_blk_id,
429     data_object_code,
430     display_method,
431     include_in_indicator_flag,
432     indicator_in_view_flag,
433     creation_date,
434     created_by,
435     last_update_date,
436     last_updated_by,
437     last_update_login,
438     pk1_field,
439     pk2_field,
440     pk3_field,
441     pk4_field,
442     pk5_field,
443     sql_statement,
444     query_permission_type,
445     insert_permission_type,
446     update_permission_type,
447     delete_permission_type,
448     condition_field,
449     condition_operator,
450     condition_value1,
451     condition_value2)  VALUES (
452     l_attachment_blk_entity_id,
453     p_attachment_blk_id,
454     p_data_object_code,
455     p_display_method,
456     p_include_in_indicator_flag,
457     p_indicator_in_view_flag,
458     sysdate,
459     1,
460     sysdate,
461     1,
462     1,
463     p_pk1_field,
464     p_pk2_field,
465     p_pk3_field,
466     p_pk4_field,
467     p_pk5_field,
468     p_sql_statement,
469     p_query_permission_type,
470     p_insert_permission_type,
471     p_update_permission_type,
472     p_delete_permission_type,
473     p_condition_field,
474     p_condition_operator,
475     p_condition_value1,
476     p_condition_value2);
477     hr_utility.set_location('Added block entity '||p_data_object_code,10);
478     p_attachment_blk_entity_id:=l_attachment_blk_entity_id;
479   else
480     close block_entity_exists;
481 -- if already exists then update it
482     update fnd_attachment_blk_entities
483     set
484     display_method=p_display_method,
485     include_in_indicator_flag=p_include_in_indicator_flag,
486     indicator_in_view_flag=p_indicator_in_view_flag,
487     creation_date=sysdate,
488     created_by=1,
489     last_update_date=sysdate,
490     last_updated_by=1,
491     last_update_login=1,
492     pk1_field=p_pk1_field,
493     pk2_field=p_pk2_field,
494     pk3_field=p_pk3_field,
495     pk4_field=p_pk4_field,
496     pk5_field=p_pk5_field,
497     sql_statement=p_sql_statement,
498     query_permission_type=p_query_permission_type,
499     insert_permission_type=p_insert_permission_type,
500     update_permission_type=p_update_permission_type,
501     delete_permission_type=p_delete_permission_type,
502     condition_field=p_condition_field,
503     condition_operator=p_condition_operator,
504     condition_value1=p_condition_value1,
505     condition_value2=p_condition_value2
506     where attachment_blk_entity_id=l_attachment_blk_entity_id;
507     hr_utility.set_location('Updated block entity '||p_data_object_code,20);
508     p_attachment_blk_entity_id:=l_attachment_blk_entity_id;
509   end if;
510   else
511     close block_exists;
512     hr_utility.set_location('Couldnt find block ',30);
513   end if;
514 end attach_entity;
515 --
516 end HR_ATTACHMENTS_LOADER;