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_TEMPLATE_ITEM_CONTEXTS_PKG

Source


1 package body HR_TEMPLATE_ITEM_CONTEXTS_PKG as
2 /* $Header: hrticlct.pkb 120.1 2011/04/28 10:58:12 sidsaxen ship $ */
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_OBJECT_VERSION_NUMBER in NUMBER,
27   X_TEMPLATE_ITEM_ID in NUMBER,
28   X_CONTEXT_TYPE in VARCHAR2,
29   X_ITEM_CONTEXT_ID in NUMBER,
30   X_CREATION_DATE in DATE,
31   X_CREATED_BY in NUMBER,
32   X_LAST_UPDATE_DATE in DATE,
33   X_LAST_UPDATED_BY in NUMBER,
34   X_LAST_UPDATE_LOGIN in NUMBER
35 ) is
36   cursor C is select ROWID from HR_TEMPLATE_ITEM_CONTEXTS_B
37     where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID
38     ;
39 begin
40 
41 --
42 -- Added the following code as a part of Zero Downtime Patching Project.
43 -- Code Starts Here.
44 --
45 
46 BEGIN
47 	PER_RIC_PKG.chk_integrity (
48     p_entity_name		=>	'HR_TEMPLATE_ITEM_CONTEXTS_B',
49     p_ref_entity_info   => PER_RIC_PKG.ref_entity_tbl(
50 							PER_RIC_PKG.ref_info_rec('HR_TEMPLATE_ITEMS_B', PER_RIC_PKG.column_info_tbl(
51                                                 PER_RIC_PKG.col_info_rec('TEMPLATE_ITEM_ID',NULL,X_TEMPLATE_ITEM_ID,NULL))),
52 							PER_RIC_PKG.ref_info_rec('HR_ITEM_CONTEXTS', PER_RIC_PKG.column_info_tbl(
53                                                 PER_RIC_PKG.col_info_rec('ITEM_CONTEXT_ID',NULL,X_ITEM_CONTEXT_ID,NULL)))
54 							),
55     p_ref_type        	=>	'INS');
56 
57 END;
58 --
59 -- Code Ends Here.
60 --
61 
62   insert into HR_TEMPLATE_ITEM_CONTEXTS_B (
63     TEMPLATE_ITEM_CONTEXT_ID,
64     OBJECT_VERSION_NUMBER,
65     TEMPLATE_ITEM_ID,
66     CONTEXT_TYPE,
67     ITEM_CONTEXT_ID,
68     LAST_UPDATE_DATE,
69     LAST_UPDATED_BY,
70     LAST_UPDATE_LOGIN,
71     CREATED_BY,
72     CREATION_DATE
73   ) values(
74     X_TEMPLATE_ITEM_CONTEXT_ID,
75     X_OBJECT_VERSION_NUMBER,
76     X_TEMPLATE_ITEM_ID,
77     X_CONTEXT_TYPE,
78     X_ITEM_CONTEXT_ID,
79     X_LAST_UPDATE_DATE,
80     X_LAST_UPDATED_BY,
81     X_LAST_UPDATE_LOGIN,
82     X_CREATED_BY,
83     X_CREATION_DATE);
84 
85   open c;
86   fetch c into X_ROWID;
87   if (c%notfound) then
88     close c;
89     raise no_data_found;
90   end if;
91   close c;
92 
93 end INSERT_ROW;
94 
95 procedure LOCK_ROW (
96   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
97   X_OBJECT_VERSION_NUMBER in NUMBER,
98   X_TEMPLATE_ITEM_ID in NUMBER,
99   X_CONTEXT_TYPE in VARCHAR2,
100   X_ITEM_CONTEXT_ID in NUMBER
101 ) is
102   cursor c1 is select
103       OBJECT_VERSION_NUMBER,
104       TEMPLATE_ITEM_ID,
105       CONTEXT_TYPE,
106       ITEM_CONTEXT_ID
107     from HR_TEMPLATE_ITEM_CONTEXTS_B
108     where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID
109     for update of TEMPLATE_ITEM_CONTEXT_ID nowait;
110 begin
111   for tlinfo in c1 loop
112           if (tlinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
113           AND (tlinfo.TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID)
114           AND (tlinfo.CONTEXT_TYPE = X_CONTEXT_TYPE)
115           AND (tlinfo.ITEM_CONTEXT_ID = X_ITEM_CONTEXT_ID)
116        then
117         null;
118       else
119         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
120         app_exception.raise_exception;
121       end if;
122   end loop;
123   return;
124 end LOCK_ROW;
125 
126 procedure UPDATE_ROW (
127   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
128   X_OBJECT_VERSION_NUMBER in NUMBER,
129   X_TEMPLATE_ITEM_ID in NUMBER,
130   X_CONTEXT_TYPE in VARCHAR2,
131   X_ITEM_CONTEXT_ID in NUMBER,
132   X_LAST_UPDATE_DATE in DATE,
133   X_LAST_UPDATED_BY in NUMBER,
134   X_LAST_UPDATE_LOGIN in NUMBER
135 ) is
136 begin
137   update HR_TEMPLATE_ITEM_CONTEXTS_B set
138     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
139     TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID,
140     CONTEXT_TYPE = X_CONTEXT_TYPE,
141     ITEM_CONTEXT_ID = X_ITEM_CONTEXT_ID,
142     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
143     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
144     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
145   where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID;
146 
147   if (sql%notfound) then
148     raise no_data_found;
149   end if;
150 end UPDATE_ROW;
151 
152 procedure DELETE_ROW (
153   X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER
154 ) is
155 begin
156 
157 --
158 -- Added the following code as a part of Zero Downtime Patching Project.
159 -- Code Starts Here.
160 --
161 
162 BEGIN
163 	PER_RIC_PKG.chk_integrity (
164     p_entity_name		=>	'HR_TEMPLATE_ITEM_CONTEXTS_B',
165     p_ref_entity_info   => PER_RIC_PKG.ref_entity_tbl(
166 							PER_RIC_PKG.ref_info_rec('HR_ITEM_PROPERTIES_B', PER_RIC_PKG.column_info_tbl(
167                                                 PER_RIC_PKG.col_info_rec('TEMPLATE_ITEM_CONTEXT_ID',NULL,X_TEMPLATE_ITEM_CONTEXT_ID,NULL))),
168 							PER_RIC_PKG.ref_info_rec('HR_TEMPLATE_ITEM_CONTEXT_PAGES', PER_RIC_PKG.column_info_tbl(
169                                                 PER_RIC_PKG.col_info_rec('TEMPLATE_ITEM_CONTEXT_ID',NULL,X_TEMPLATE_ITEM_CONTEXT_ID,NULL)))
170 							),
171     p_ref_type        	=>	'DEL');
172 
173 END;
174 --
175 -- Code Ends Here.
176 --
177  delete from HR_TEMPLATE_ITEM_CONTEXTS_B
178   where TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID;
179 
180   if (sql%notfound) then
181     raise no_data_found;
182   end if;
183 
184 end DELETE_ROW;
185 
186 procedure LOAD_ROW (
187   X_APPLICATION_SHORT_NAME in VARCHAR2,
188   X_FORM_NAME in VARCHAR2,
189   X_TEMPLATE_NAME in VARCHAR2,
190   X_TERRITORY_SHORT_NAME in VARCHAR2,
191   X_FULL_ITEM_NAME in VARCHAR2,
192   X_RADIO_BUTTON_NAME in VARCHAR2,
193   X_SEGMENT1 in VARCHAR2,
194   X_SEGMENT2 in VARCHAR2,
195   X_SEGMENT3 in VARCHAR2,
196   X_SEGMENT4 in VARCHAR2,
197   X_SEGMENT5 in VARCHAR2,
198   X_SEGMENT6 in VARCHAR2,
199   X_SEGMENT7 in VARCHAR2,
200   X_SEGMENT8 in VARCHAR2,
201   X_SEGMENT9 in VARCHAR2,
202   X_SEGMENT10 in VARCHAR2,
203   X_SEGMENT11 in VARCHAR2,
204   X_SEGMENT12 in VARCHAR2,
205   X_SEGMENT13 in VARCHAR2,
206   X_SEGMENT14 in VARCHAR2,
207   X_SEGMENT15 in VARCHAR2,
208   X_SEGMENT16 in VARCHAR2,
209   X_SEGMENT17 in VARCHAR2,
210   X_SEGMENT18 in VARCHAR2,
211   X_SEGMENT19 in VARCHAR2,
212   X_SEGMENT20 in VARCHAR2,
213   X_SEGMENT21 in VARCHAR2,
214   X_SEGMENT22 in VARCHAR2,
215   X_SEGMENT23 in VARCHAR2,
216   X_SEGMENT24 in VARCHAR2,
217   X_SEGMENT25 in VARCHAR2,
218   X_SEGMENT26 in VARCHAR2,
219   X_SEGMENT27 in VARCHAR2,
220   X_SEGMENT28 in VARCHAR2,
221   X_SEGMENT29 in VARCHAR2,
222   X_SEGMENT30 in VARCHAR2,
223   X_ID_FLEX_STRUCTURE_CODE in VARCHAR2,
224   X_ID_FLEX_CODE in VARCHAR2,
225   X_OWNER in VARCHAR2,
226   X_OBJECT_VERSION_NUMBER in VARCHAR2,
227   X_CONTEXT_TYPE in VARCHAR2) is
228   X_ROWID ROWID;
229   X_CREATION_DATE DATE;
230   X_CREATED_BY NUMBER;
231   X_LAST_UPDATE_DATE DATE;
232   X_LAST_UPDATED_BY NUMBER;
233   X_LAST_UPDATE_LOGIN NUMBER;
234   X_FORM_ID NUMBER;
235   X_APPLICATION_ID NUMBER;
236   X_FORM_ITEM_ID NUMBER;
237   X_TEMPLATE_ITEM_ID NUMBER;
238   X_TEMPLATE_ITEM_CONTEXT_ID NUMBER;
239   X_ITEM_PROPERTY_ID NUMBER;
240   X_ITEM_CONTEXT_ID NUMBER;
241 begin
242   OWNER_TO_WHO (
243     X_OWNER,
244     X_CREATION_DATE,
245     X_CREATED_BY,
246     X_LAST_UPDATE_DATE,
247     X_LAST_UPDATED_BY,
248     X_LAST_UPDATE_LOGIN
249   );
250 
251  select application_id
252  into x_application_id
253  from fnd_application
254  where application_short_name = x_application_short_name;
255 
256  select form_id
257  into x_form_id
258  from fnd_form
259  where form_name = x_form_name
260  and application_id = x_application_id;
261 
262  select form_item_id
263  into x_form_item_id
264  from hr_form_items_b
265  where full_item_name = x_full_item_name
266  and application_id = x_application_id
267  and form_id = x_form_id
268  and (  radio_button_name = x_radio_button_name
269      or (radio_button_name is null and x_radio_button_name is null) );
270 
271  select hti.template_item_id
272  into x_template_item_id
273  from hr_form_templates_b hft
274       ,hr_template_items_b hti
275  where hti.form_item_id = x_form_item_id
276  and hti.form_template_id = hft.form_template_id
277  and hft.form_id = x_form_id
278  and hft.application_id = x_application_id
279  and (  (legislation_code is null and x_territory_short_name is null)
280      or (legislation_code = x_territory_short_name) )
281  and hft.template_name = x_template_name;
282 
283  select item_context_id
284  into x_item_context_id
285  from hr_item_contexts hic
286       ,fnd_id_flex_structures fifs
287  where nvl(hic.segment1,hr_api.g_varchar2) = nvl(x_segment1,hr_api.g_varchar2)
288  and nvl(hic.segment2,hr_api.g_varchar2) = nvl(x_segment2,hr_api.g_varchar2)
289  and nvl(hic.segment3,hr_api.g_varchar2) = nvl(x_segment3,hr_api.g_varchar2)
290  and nvl(hic.segment4,hr_api.g_varchar2) = nvl(x_segment4,hr_api.g_varchar2)
291  and nvl(hic.segment5,hr_api.g_varchar2) = nvl(x_segment5,hr_api.g_varchar2)
292  and nvl(hic.segment6,hr_api.g_varchar2) = nvl(x_segment6,hr_api.g_varchar2)
293  and nvl(hic.segment7,hr_api.g_varchar2) = nvl(x_segment7,hr_api.g_varchar2)
294  and nvl(hic.segment8,hr_api.g_varchar2) = nvl(x_segment8,hr_api.g_varchar2)
295  and nvl(hic.segment9,hr_api.g_varchar2) = nvl(x_segment9,hr_api.g_varchar2)
296  and nvl(hic.segment10,hr_api.g_varchar2) = nvl(x_segment10,hr_api.g_varchar2)
297  and nvl(hic.segment11,hr_api.g_varchar2) = nvl(x_segment11,hr_api.g_varchar2)
298  and nvl(hic.segment12,hr_api.g_varchar2) = nvl(x_segment12,hr_api.g_varchar2)
299  and nvl(hic.segment13,hr_api.g_varchar2) = nvl(x_segment13,hr_api.g_varchar2)
300  and nvl(hic.segment14,hr_api.g_varchar2) = nvl(x_segment14,hr_api.g_varchar2)
301  and nvl(hic.segment15,hr_api.g_varchar2) = nvl(x_segment15,hr_api.g_varchar2)
302  and nvl(hic.segment16,hr_api.g_varchar2) = nvl(x_segment16,hr_api.g_varchar2)
303  and nvl(hic.segment17,hr_api.g_varchar2) = nvl(x_segment17,hr_api.g_varchar2)
304  and nvl(hic.segment18,hr_api.g_varchar2) = nvl(x_segment18,hr_api.g_varchar2)
305  and nvl(hic.segment19,hr_api.g_varchar2) = nvl(x_segment19,hr_api.g_varchar2)
306  and nvl(hic.segment20,hr_api.g_varchar2) = nvl(x_segment20,hr_api.g_varchar2)
307  and nvl(hic.segment21,hr_api.g_varchar2) = nvl(x_segment21,hr_api.g_varchar2)
308  and nvl(hic.segment22,hr_api.g_varchar2) = nvl(x_segment22,hr_api.g_varchar2)
309  and nvl(hic.segment23,hr_api.g_varchar2) = nvl(x_segment23,hr_api.g_varchar2)
310  and nvl(hic.segment24,hr_api.g_varchar2) = nvl(x_segment24,hr_api.g_varchar2)
311  and nvl(hic.segment25,hr_api.g_varchar2) = nvl(x_segment25,hr_api.g_varchar2)
312  and nvl(hic.segment26,hr_api.g_varchar2) = nvl(x_segment26,hr_api.g_varchar2)
313  and nvl(hic.segment27,hr_api.g_varchar2) = nvl(x_segment27,hr_api.g_varchar2)
314  and nvl(hic.segment28,hr_api.g_varchar2) = nvl(x_segment28,hr_api.g_varchar2)
315  and nvl(hic.segment29,hr_api.g_varchar2) = nvl(x_segment29,hr_api.g_varchar2)
316  and nvl(hic.segment30,hr_api.g_varchar2) = nvl(x_segment30,hr_api.g_varchar2)
317  and hic.id_flex_num = fifs.id_flex_num
318  and fifs.application_id = x_application_id
319  and fifs.id_flex_structure_code = x_id_flex_structure_code
320  and fifs.id_flex_code = x_id_flex_code;
321 
322  begin
323   select template_item_context_id
324   into x_template_item_context_id
325   from hr_template_item_contexts_b ticb
326   where ticb.template_item_id = x_template_item_id
327   and ticb.item_context_id = x_item_context_id;
328  exception
329    when no_data_found then
330      select hr_template_item_contexts_b_s.nextval
331      into x_template_item_context_id
332      from dual;
333   end;
334 
335  begin
336    UPDATE_ROW (
337      X_TEMPLATE_ITEM_CONTEXT_ID,
338      to_number(X_OBJECT_VERSION_NUMBER),
339      X_TEMPLATE_ITEM_ID,
340      X_CONTEXT_TYPE,
341      X_ITEM_CONTEXT_ID,
342      X_LAST_UPDATE_DATE,
343      X_LAST_UPDATED_BY,
344      X_LAST_UPDATE_LOGIN
345    );
346 
347  exception
348    when no_data_found then
349      INSERT_ROW (
350        X_ROWID,
351        X_TEMPLATE_ITEM_CONTEXT_ID,
352        to_number(X_OBJECT_VERSION_NUMBER),
353        X_TEMPLATE_ITEM_ID,
354        X_CONTEXT_TYPE,
355        X_ITEM_CONTEXT_ID,
356        X_CREATION_DATE,
357        X_CREATED_BY,
358        X_LAST_UPDATE_DATE,
359        X_LAST_UPDATED_BY,
360        X_LAST_UPDATE_LOGIN);
361  end;
362 end LOAD_ROW;
363 end HR_TEMPLATE_ITEM_CONTEXTS_PKG;