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_TCP_PKG

Source


1 package body HR_TCP_PKG as
2 /* $Header: hrtcplct.pkb 115.4 2002/12/11 06:57:36 raranjan noship $ */
3 procedure OWNER_TO_WHO (
4   X_OWNER in VARCHAR2,
5   X_CREATION_DATE out nocopy DATE,
6   X_CREATED_BY out nocopy NUMBER,
7   X_LAST_UPDATE_DATE out nocopy DATE,
8   X_LAST_UPDATED_BY out nocopy NUMBER,
9   X_LAST_UPDATE_LOGIN out nocopy NUMBER
10 ) is
11 begin
12   if X_OWNER = 'SEED' then
13     X_CREATED_BY := 1;
14     X_LAST_UPDATED_BY := 1;
15   else
16     X_CREATED_BY := 0;
17     X_LAST_UPDATED_BY := 0;
18   end if;
19   X_CREATION_DATE := sysdate;
20   X_LAST_UPDATE_DATE := sysdate;
21   X_LAST_UPDATE_LOGIN := 0;
22 end OWNER_TO_WHO;
23 procedure INSERT_ROW (
24   X_ROWID in out nocopy VARCHAR2,
25   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
26   X_TEMPLATE_ITEM_CONTEXT_PAGE_I in NUMBER,
27   X_OBJECT_VERSION_NUMBER in NUMBER,
28   X_TEMPLATE_TAB_PAGE_ID in NUMBER,
29   X_CREATION_DATE in DATE,
30   X_CREATED_BY in NUMBER,
31   X_LAST_UPDATE_DATE in DATE,
32   X_LAST_UPDATED_BY in NUMBER,
33   X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35   cursor C is select ROWID from HR_TEMPLATE_ITEM_CONTEXT_PAGES
36     where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID
37     ;
38 begin
39   insert into HR_TEMPLATE_ITEM_CONTEXT_PAGES (
40     CREATION_DATE,
41     TEMPLATE_ITEM_CONTEXT_PAGE_ID,
42     OBJECT_VERSION_NUMBER,
43     TEMPLATE_ITEM_CONTEXT_ID,
44     TEMPLATE_TAB_PAGE_ID,
45     LAST_UPDATE_DATE,
46     LAST_UPDATED_BY,
47     LAST_UPDATE_LOGIN,
48     CREATED_BY
49   ) values(
50     X_CREATION_DATE,
51     X_TEMPLATE_ITEM_CONTEXT_PAGE_I,
52     X_OBJECT_VERSION_NUMBER,
53     X_TEMPLATE_ITEM_CONTEXT_ID,
54     X_TEMPLATE_TAB_PAGE_ID,
55     X_LAST_UPDATE_DATE,
56     X_LAST_UPDATED_BY,
57     X_LAST_UPDATE_LOGIN,
58     X_CREATED_BY);
59 
60   open c;
61   fetch c into X_ROWID;
62   if (c%notfound) then
63     close c;
64     raise no_data_found;
65   end if;
66   close c;
67 
68 end INSERT_ROW;
69 
70 procedure LOCK_ROW (
71   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
72   X_TEMPLATE_ITEM_CONTEXT_PAGE_I in NUMBER,
73   X_OBJECT_VERSION_NUMBER in NUMBER,
74   X_TEMPLATE_TAB_PAGE_ID in NUMBER
75 ) is
76   cursor c1 is select
77       TEMPLATE_ITEM_CONTEXT_PAGE_ID,
78       OBJECT_VERSION_NUMBER,
79       TEMPLATE_TAB_PAGE_ID
80     from HR_TEMPLATE_ITEM_CONTEXT_PAGES
81     where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID
82     for update of TEMPLATE_ITEM_CONTEXT_ID nowait;
83 begin
84   for tlinfo in c1 loop
85           if (tlinfo.TEMPLATE_ITEM_CONTEXT_PAGE_ID = X_TEMPLATE_ITEM_CONTEXT_PAGE_I)
86           AND (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
87           AND (tlinfo.TEMPLATE_TAB_PAGE_ID = X_TEMPLATE_TAB_PAGE_ID)
88        then
89         null;
90       else
91         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
92         app_exception.raise_exception;
93       end if;
94   end loop;
95   return;
96 end LOCK_ROW;
97 
98 procedure UPDATE_ROW (
99   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
100   X_TEMPLATE_ITEM_CONTEXT_PAGE_I in NUMBER,
101   X_OBJECT_VERSION_NUMBER in NUMBER,
102   X_TEMPLATE_TAB_PAGE_ID in NUMBER,
103   X_LAST_UPDATE_DATE in DATE,
104   X_LAST_UPDATED_BY in NUMBER,
105   X_LAST_UPDATE_LOGIN in NUMBER
106 ) is
107 begin
108   update HR_TEMPLATE_ITEM_CONTEXT_PAGES set
109     TEMPLATE_ITEM_CONTEXT_PAGE_ID = X_TEMPLATE_ITEM_CONTEXT_PAGE_I,
110     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
111     TEMPLATE_TAB_PAGE_ID = X_TEMPLATE_TAB_PAGE_ID,
112     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
113     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
114     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
115   where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID;
116 
117   if (sql%notfound) then
118     raise no_data_found;
119   end if;
120 end UPDATE_ROW;
121 
122 procedure DELETE_ROW (
123   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER
124 ) is
125 begin
126   delete from HR_TEMPLATE_ITEM_CONTEXT_PAGES
127   where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID;
128 
129   if (sql%notfound) then
130     raise no_data_found;
131   end if;
132 
133 end DELETE_ROW;
134 procedure LOAD_ROW (
135   X_APPLICATION_SHORT_NAME in VARCHAR2,
136   X_FORM_NAME in VARCHAR2,
137   X_TEMPLATE_NAME in VARCHAR2,
138   X_TERRITORY_SHORT_NAME in VARCHAR2,
139   X_FULL_ITEM_NAME in VARCHAR2,
140   X_RADIO_BUTTON_NAME in VARCHAR2,
141   X_SEGMENT1 in VARCHAR2,
142   X_SEGMENT2 in VARCHAR2,
143   X_SEGMENT3 in VARCHAR2,
144   X_SEGMENT4 in VARCHAR2,
145   X_SEGMENT5 in VARCHAR2,
146   X_SEGMENT6 in VARCHAR2,
147   X_SEGMENT7 in VARCHAR2,
148   X_SEGMENT8 in VARCHAR2,
149   X_SEGMENT9 in VARCHAR2,
150   X_SEGMENT10 in VARCHAR2,
151   X_SEGMENT11 in VARCHAR2,
152   X_SEGMENT12 in VARCHAR2,
153   X_SEGMENT13 in VARCHAR2,
154   X_SEGMENT14 in VARCHAR2,
155   X_SEGMENT15 in VARCHAR2,
156   X_SEGMENT16 in VARCHAR2,
157   X_SEGMENT17 in VARCHAR2,
158   X_SEGMENT18 in VARCHAR2,
159   X_SEGMENT19 in VARCHAR2,
160   X_SEGMENT20 in VARCHAR2,
161   X_SEGMENT21 in VARCHAR2,
162   X_SEGMENT22 in VARCHAR2,
163   X_SEGMENT23 in VARCHAR2,
164   X_SEGMENT24 in VARCHAR2,
165   X_SEGMENT25 in VARCHAR2,
166   X_SEGMENT26 in VARCHAR2,
167   X_SEGMENT27 in VARCHAR2,
168   X_SEGMENT28 in VARCHAR2,
169   X_SEGMENT29 in VARCHAR2,
170   X_SEGMENT30 in VARCHAR2,
171   X_ID_FLEX_STRUCTURE_CODE in VARCHAR2,
172   X_CONTEXT_TYPE in VARCHAR2,
173   X_ID_FLEX_CODE in VARCHAR2,
174   X_CANVAS_NAME in VARCHAR2,
175   X_WINDOW_NAME in VARCHAR2,
176   X_FORM_TAB_PAGE_NAME in VARCHAR2,
177   X_OWNER in VARCHAR2,
178   X_OBJECT_VERSION_NUMBER in VARCHAR2) is
179   X_ROWID ROWID;
180   X_CREATION_DATE DATE;
181   X_CREATED_BY NUMBER;
182   X_LAST_UPDATE_DATE DATE;
183   X_LAST_UPDATED_BY NUMBER;
184   X_LAST_UPDATE_LOGIN NUMBER;
185   X_FORM_ID NUMBER;
186   X_APPLICATION_ID NUMBER;
187   X_FORM_ITEM_ID NUMBER;
188   X_TEMPLATE_ITEM_ID NUMBER;
189   X_TEMPLATE_ITEM_CONTEXT_ID NUMBER;
190   X_ITEM_PROPERTY_ID NUMBER;
191   X_ITEM_CONTEXT_ID NUMBER;
192   X_FORM_WINDOW_ID NUMBER;
193   X_FORM_TEMPLATE_ID NUMBER;
194   X_TEMPLATE_WINDOW_ID NUMBER;
195   X_TCP_ID NUMBER;
196   X_FORM_CANVAS_ID NUMBER;
197   X_TEMPLATE_CANVAS_ID NUMBER;
198   X_FORM_TAB_PAGE_ID NUMBER;
199   X_TEMPLATE_TAB_PAGE_ID NUMBER;
200 begin
201   OWNER_TO_WHO (
202     X_OWNER,
203     X_CREATION_DATE,
204     X_CREATED_BY,
205     X_LAST_UPDATE_DATE,
206     X_LAST_UPDATED_BY,
207     X_LAST_UPDATE_LOGIN
208   );
209 
210 /* adhunter jun-2002, bug 2183600
211  merged the following 3 SELECT into one, after this commented section
212 
213  select application_id
214  into x_application_id
215  from fnd_application
216  where application_short_name = x_application_short_name;
217 
218  select form_id
219  into x_form_id
220  from fnd_form
221  where form_name = x_form_name
222  and application_id = x_application_id;
223 
224  select form_item_id
225  into x_form_item_id
226  from hr_form_items_b
227  where full_item_name = x_full_item_name
228  and application_id = x_application_id
229  and form_id = x_form_id
230  and (  radio_button_name = x_radio_button_name
231      or (radio_button_name is null and x_radio_button_name is null) );
232 */
233 
234 select a.application_id,
235        f.form_id,
236        fi.form_item_id
237 into   x_application_id,
238        x_form_id,
239        x_form_item_id
240 from   fnd_application a,
241        fnd_form f,
242        hr_form_items_b fi
243  where a.application_short_name = x_application_short_name
244  and   f.form_name = x_form_name
245  and   f.application_id = a.application_id
246  and   fi.full_item_name = x_full_item_name
247  and   fi.application_id = f.application_id
248  and   fi.form_id = f.form_id
249  and  (fi.radio_button_name = x_radio_button_name
250  or   (fi.radio_button_name is null and x_radio_button_name is null));
251 
252 
253  select hti.template_item_id
254  into x_template_item_id
255  from hr_form_templates_b hft
256       ,hr_template_items_b hti
257  where hti.form_item_id = x_form_item_id
258  and hti.form_template_id = hft.form_template_id
259  and hft.form_id = x_form_id
260  and hft.application_id = x_application_id
261  and (  (hft.legislation_code is null and x_territory_short_name is null)
262      or (hft.legislation_code = x_territory_short_name) )
263  and hft.template_name = x_template_name;
264 
265  select item_context_id
266  into x_item_context_id
267  from hr_item_contexts hic
268       ,fnd_id_flex_structures fifs
269  where nvl(hic.segment1,hr_api.g_varchar2) = nvl(x_segment1,hr_api.g_varchar2)
270  and nvl(hic.segment2,hr_api.g_varchar2) = nvl(x_segment2,hr_api.g_varchar2)
271  and nvl(hic.segment3,hr_api.g_varchar2) = nvl(x_segment3,hr_api.g_varchar2)
272  and nvl(hic.segment4,hr_api.g_varchar2) = nvl(x_segment4,hr_api.g_varchar2)
273  and nvl(hic.segment5,hr_api.g_varchar2) = nvl(x_segment5,hr_api.g_varchar2)
274  and nvl(hic.segment6,hr_api.g_varchar2) = nvl(x_segment6,hr_api.g_varchar2)
275  and nvl(hic.segment7,hr_api.g_varchar2) = nvl(x_segment7,hr_api.g_varchar2)
276  and nvl(hic.segment8,hr_api.g_varchar2) = nvl(x_segment8,hr_api.g_varchar2)
277  and nvl(hic.segment9,hr_api.g_varchar2) = nvl(x_segment9,hr_api.g_varchar2)
278  and nvl(hic.segment10,hr_api.g_varchar2) = nvl(x_segment10,hr_api.g_varchar2)
279  and nvl(hic.segment11,hr_api.g_varchar2) = nvl(x_segment11,hr_api.g_varchar2)
280  and nvl(hic.segment12,hr_api.g_varchar2) = nvl(x_segment12,hr_api.g_varchar2)
281  and nvl(hic.segment13,hr_api.g_varchar2) = nvl(x_segment13,hr_api.g_varchar2)
282  and nvl(hic.segment14,hr_api.g_varchar2) = nvl(x_segment14,hr_api.g_varchar2)
283  and nvl(hic.segment15,hr_api.g_varchar2) = nvl(x_segment15,hr_api.g_varchar2)
284  and nvl(hic.segment16,hr_api.g_varchar2) = nvl(x_segment16,hr_api.g_varchar2)
285  and nvl(hic.segment17,hr_api.g_varchar2) = nvl(x_segment17,hr_api.g_varchar2)
286  and nvl(hic.segment18,hr_api.g_varchar2) = nvl(x_segment18,hr_api.g_varchar2)
287  and nvl(hic.segment19,hr_api.g_varchar2) = nvl(x_segment19,hr_api.g_varchar2)
288  and nvl(hic.segment20,hr_api.g_varchar2) = nvl(x_segment20,hr_api.g_varchar2)
289  and nvl(hic.segment21,hr_api.g_varchar2) = nvl(x_segment21,hr_api.g_varchar2)
290  and nvl(hic.segment22,hr_api.g_varchar2) = nvl(x_segment22,hr_api.g_varchar2)
291  and nvl(hic.segment23,hr_api.g_varchar2) = nvl(x_segment23,hr_api.g_varchar2)
292  and nvl(hic.segment24,hr_api.g_varchar2) = nvl(x_segment24,hr_api.g_varchar2)
293  and nvl(hic.segment25,hr_api.g_varchar2) = nvl(x_segment25,hr_api.g_varchar2)
294  and nvl(hic.segment26,hr_api.g_varchar2) = nvl(x_segment26,hr_api.g_varchar2)
295  and nvl(hic.segment27,hr_api.g_varchar2) = nvl(x_segment27,hr_api.g_varchar2)
296  and nvl(hic.segment28,hr_api.g_varchar2) = nvl(x_segment28,hr_api.g_varchar2)
297  and nvl(hic.segment29,hr_api.g_varchar2) = nvl(x_segment29,hr_api.g_varchar2)
298  and nvl(hic.segment30,hr_api.g_varchar2) = nvl(x_segment30,hr_api.g_varchar2)
299  and hic.id_flex_num = fifs.id_flex_num
300  and fifs.application_id = x_application_id
301  and fifs.id_flex_structure_code = x_id_flex_structure_code
302  and fifs.id_flex_code = x_id_flex_code;
303 
304  select template_item_context_id
305  into x_template_item_context_id
306  from hr_template_item_contexts_b ticb
307  where ticb.template_item_id = x_template_item_id
308  and ticb.context_type = x_context_type
309  and ticb.item_context_id = x_item_context_id;
310 
311 /* adhunter jun-2002, bug 2183600
312  merged the following 7 SELECT into one, after this commented section
313 
314  select form_window_id
315  into x_form_window_id
316  from hr_form_windows_b
317  where window_name =  x_window_name
318  and application_id = x_application_id
319  and form_id = x_form_id;
320 
321  select form_template_id
322  into x_form_template_id
323  from hr_form_templates_b
324  where (  (legislation_code is null and x_territory_short_name is null)
325        or (legislation_code = x_territory_short_name) )
326  and template_name =  x_template_name
327  and application_id = x_application_id
328  and form_id = x_form_id;
329 
330  select template_window_id
331  into x_template_window_id
332  from hr_template_windows_b
333  where form_template_id = x_form_template_id
334  and form_window_id = x_form_window_id;
335 
336  select form_canvas_id
337  into x_form_canvas_id
338  from hr_form_canvases_b
339  where canvas_name = x_canvas_name
340  and form_window_id = x_form_window_id;
341 
342  select template_canvas_id
343  into x_template_canvas_id
344  from hr_template_canvases_b
345  where form_canvas_id = x_form_canvas_id
346  and template_window_id = x_template_window_id;
347 
348  select form_tab_page_id
349  into x_form_tab_page_id
350  from hr_form_tab_pages_b
351  where tab_page_name = x_form_tab_page_name
352  and form_canvas_id = x_form_canvas_id;
353 
354  select template_tab_page_id
355  into x_template_tab_page_id
356  from hr_template_tab_pages_b
357  where template_canvas_id = x_template_canvas_id
358  and form_tab_page_id = x_form_tab_page_id;
359 */
360 
361  select  hfw.form_window_id,
362          hft.form_template_id,
363          htw.template_window_id,
364          hfc.form_canvas_id,
365          htc.template_canvas_id,
366          hftp.form_tab_page_id,
367          htpb.template_tab_page_id
368  into    x_form_window_id,
369          x_form_template_id,
370          x_template_window_id,
371          x_form_canvas_id,
372          x_template_canvas_id,
373          x_form_tab_page_id,
374          x_template_tab_page_id
375  from    hr_form_windows_b hfw,
376          hr_form_templates_b hft,
377          hr_template_windows_b htw,
378          hr_form_canvases_b hfc,
379          hr_template_canvases_b htc,
380          hr_form_tab_pages_b hftp,
381          hr_template_tab_pages_b htpb
382  where   hfw.window_name =  x_window_name
383  and     hfw.application_id = x_application_id
384  and     hfw.form_id = x_form_id
385  and     hft.application_id = hfw.application_id
386  and     hft.form_id = hfw.form_id
387  and     hft.template_name =  x_template_name
388  and   ((hft.legislation_code = x_territory_short_name)
389  or     (hft.legislation_code is null and x_territory_short_name is null))
390  and     htw.form_template_id = hft.form_template_id
391  and     htw.form_window_id = hfw.form_window_id
392  and     hfc.canvas_name = x_canvas_name
393  and     hfc.form_window_id = hfw.form_window_id
394  and     htc.form_canvas_id = hfc.form_canvas_id
395  and     htc.template_window_id = htw.template_window_id
396  and     hftp.tab_page_name = x_form_tab_page_name
397  and     hftp.form_canvas_id = htc.form_canvas_id
398  and     htpb.template_canvas_id = htc.template_canvas_id
399  and     htpb.form_tab_page_id = hftp.form_tab_page_id;
400 
401  begin
402 
403   select template_item_context_page_id
404   into x_tcp_id
405   from hr_template_item_context_pages
406   where template_tab_page_id = x_template_tab_page_id
407   and template_item_context_id = x_template_item_context_id;
408   -- row has been found so perform update
409     update HR_TEMPLATE_ITEM_CONTEXT_PAGES
410     set    TEMPLATE_ITEM_CONTEXT_PAGE_ID = x_tcp_id,
411 	   OBJECT_VERSION_NUMBER = TO_NUMBER (x_object_version_number),
412 	   TEMPLATE_TAB_PAGE_ID = X_TEMPLATE_TAB_PAGE_ID,
413 	   LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
414 	   LAST_UPDATED_BY = X_LAST_UPDATED_BY,
415 	   LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
416     where  TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID;
417  exception
418    when no_data_found then
419     -- insert row
420     insert into HR_TEMPLATE_ITEM_CONTEXT_PAGES (
421     CREATION_DATE,
422     TEMPLATE_ITEM_CONTEXT_PAGE_ID,
423     OBJECT_VERSION_NUMBER,
424     TEMPLATE_ITEM_CONTEXT_ID,
425     TEMPLATE_TAB_PAGE_ID,
426     LAST_UPDATE_DATE,
427     LAST_UPDATED_BY,
428     LAST_UPDATE_LOGIN,
429     CREATED_BY)
430     values(
431     X_CREATION_DATE,
432     hr_tcp_s.nextval,
433     TO_NUMBER(x_object_version_number),
434     X_TEMPLATE_ITEM_CONTEXT_ID,
435     X_TEMPLATE_TAB_PAGE_ID,
436     X_LAST_UPDATE_DATE,
437     X_LAST_UPDATED_BY,
438     X_LAST_UPDATE_LOGIN,
439     X_CREATED_BY);
440  end;
441 
442 /* adhunter jun-2002, bug 2183600
443  this has been absorbed into preceding statement.
444 
445  begin
446    UPDATE_ROW (
447      X_TEMPLATE_ITEM_CONTEXT_ID,
448      X_TCP_ID,
449      to_number(X_OBJECT_VERSION_NUMBER),
450      X_TEMPLATE_TAB_PAGE_ID,
451      X_LAST_UPDATE_DATE,
452      X_LAST_UPDATED_BY,
453      X_LAST_UPDATE_LOGIN
454    );
455  exception
456    when no_data_found then
457      INSERT_ROW (
458        X_ROWID,
459        X_TEMPLATE_ITEM_CONTEXT_ID,
460        X_TCP_ID,
461        to_number(X_OBJECT_VERSION_NUMBER),
462        X_TEMPLATE_TAB_PAGE_ID,
463        X_CREATION_DATE,
464        X_CREATED_BY,
465        X_LAST_UPDATE_DATE,
466        X_LAST_UPDATED_BY,
467        X_LAST_UPDATE_LOGIN );
468  end;
469 */
470 end LOAD_ROW;
471 end HR_TCP_PKG;