1 package body HR_ITEM_PROPERTIES_PKG as
2 /* $Header: hritplct.pkb 120.2.12000000.1 2007/01/21 16:50:00 appldev ship $ */
3
4 --
5 -- Global variables, added for performance improvements bug 2781560
6 --
7 g_application_id fnd_application.application_id%TYPE;
8 g_application_short_name fnd_application.application_short_name%type;
9 g_form_id fnd_form.form_id%TYPE;
10 g_form_name fnd_form.form_name%type;
11 g_form_template_id hr_form_templates_b.form_template_id%type;
12 g_template_name hr_form_templates_b.template_name%type;
13 g_legislation_code hr_form_templates_b.legislation_code%type;
14
15
16 -- -----------------------------------------------------------------------------
17 -- |-----------------------------< update_copies >-----------------------------|
18 -- -----------------------------------------------------------------------------
19 procedure UPDATE_COPIES (
20 X_ITEM_PROPERTY_ID in NUMBER,
21 X_FORM_ITEM_ID in NUMBER,
22 X_TEMPLATE_ITEM_ID in NUMBER,
23 X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
24 X_ALIGNMENT in NUMBER,
25 X_BEVEL in NUMBER,
26 X_CASE_RESTRICTION in NUMBER,
27 X_ENABLED in NUMBER,
28 X_FORMAT_MASK in VARCHAR2,
29 X_HEIGHT in NUMBER,
30 X_INFORMATION_FORMULA_ID in NUMBER,
31 X_INFORMATION_PARAM_ITEM_ID1 in NUMBER,
32 X_INFORMATION_PARAM_ITEM_ID2 in NUMBER,
33 X_INFORMATION_PARAM_ITEM_ID3 in NUMBER,
34 X_INFORMATION_PARAM_ITEM_ID4 in NUMBER,
35 X_INFORMATION_PARAM_ITEM_ID5 in NUMBER,
36 X_INSERT_ALLOWED in NUMBER,
37 X_NEXT_NAVIGATION_ITEM_ID in NUMBER,
38 X_PREVIOUS_NAVIGATION_ITEM_ID in NUMBER,
39 X_PROMPT_ALIGNMENT_OFFSET in NUMBER,
40 X_PROMPT_DISPLAY_STYLE in NUMBER,
41 X_PROMPT_EDGE in NUMBER,
42 X_PROMPT_EDGE_OFFSET in NUMBER,
43 X_PROMPT_EDGE_ALIGNMENT in NUMBER,
44 X_PROMPT_TEXT_ALIGNMENT in NUMBER,
45 X_QUERY_ALLOWED in NUMBER,
46 X_REQUIRED in NUMBER,
47 X_UPDATE_ALLOWED in NUMBER,
48 X_VALIDATION_FORMULA_ID in NUMBER,
49 X_VALIDATION_PARAM_ITEM_ID1 in NUMBER,
50 X_VALIDATION_PARAM_ITEM_ID2 in NUMBER,
51 X_VALIDATION_PARAM_ITEM_ID3 in NUMBER,
52 X_VALIDATION_PARAM_ITEM_ID4 in NUMBER,
53 X_VALIDATION_PARAM_ITEM_ID5 in NUMBER,
54 X_VISIBLE in NUMBER,
55 X_WIDTH in NUMBER,
56 X_X_POSITION in NUMBER,
57 X_Y_POSITION in NUMBER,
58 X_DEFAULT_VALUE in VARCHAR2,
59 X_INFORMATION_PROMPT in VARCHAR2,
60 X_LABEL in VARCHAR2,
61 X_PROMPT_TEXT in VARCHAR2,
62 X_TOOLTIP_TEXT in VARCHAR2,
63 X_INFORMATION_CATEGORY in VARCHAR2,
64 X_INFORMATION1 in VARCHAR2,
65 X_INFORMATION2 in VARCHAR2,
66 X_INFORMATION3 in VARCHAR2,
67 X_INFORMATION4 in VARCHAR2,
68 X_INFORMATION5 in VARCHAR2,
69 X_INFORMATION6 in VARCHAR2,
70 X_INFORMATION7 in VARCHAR2,
71 X_INFORMATION8 in VARCHAR2,
72 X_INFORMATION9 in VARCHAR2,
73 X_INFORMATION10 in VARCHAR2,
74 X_INFORMATION11 in VARCHAR2,
75 X_INFORMATION12 in VARCHAR2,
76 X_INFORMATION13 in VARCHAR2,
77 X_INFORMATION14 in VARCHAR2,
78 X_INFORMATION15 in VARCHAR2,
79 X_INFORMATION16 in VARCHAR2,
80 X_INFORMATION17 in VARCHAR2,
81 X_INFORMATION18 in VARCHAR2,
82 X_INFORMATION19 in VARCHAR2,
83 X_INFORMATION20 in VARCHAR2,
84 X_INFORMATION21 in VARCHAR2,
85 X_INFORMATION22 in VARCHAR2,
86 X_INFORMATION23 in VARCHAR2,
87 X_INFORMATION24 in VARCHAR2,
88 X_INFORMATION25 in VARCHAR2,
89 X_INFORMATION26 in VARCHAR2,
90 X_INFORMATION27 in VARCHAR2,
91 X_INFORMATION28 in VARCHAR2,
92 X_INFORMATION29 in VARCHAR2,
93 X_INFORMATION30 in VARCHAR2,
94 X_LAST_UPDATE_DATE in DATE,
95 X_LAST_UPDATED_BY in NUMBER,
96 X_LAST_UPDATE_LOGIN in NUMBER
97 ) is
98 cursor csr_original is
99 select *
100 from HR_ITEM_PROPERTIES_VL
101 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID;
102 l_original csr_original%rowtype;
103 cursor csr_copies is
104 select itp.item_property_id
105 from HR_ITEM_PROPERTIES_B itp
106 ,HR_TEMPLATE_ITEMS_B tim
107 ,HR_TEMPLATE_ITEMS_B tio
108 where itp.TEMPLATE_ITEM_ID = tim.TEMPLATE_ITEM_ID
109 and tim.FORM_TEMPLATE_ID in (select sft.FORM_TEMPLATE_ID_TO
110 from HR_SOURCE_FORM_TEMPLATES sft
111 start with sft.FORM_TEMPLATE_ID_FROM = tio.FORM_TEMPLATE_ID
112 connect by sft.FORM_TEMPLATE_ID_FROM = prior sft.FORM_TEMPLATE_ID_TO)
113 and tim.FORM_ITEM_ID = tio.FORM_ITEM_ID
114 and tio.TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID;
115 begin
116 if (X_TEMPLATE_ITEM_ID is not null) then
117 open csr_original;
118 fetch csr_original into l_original;
119 if csr_original%found then
120 close csr_original;
121 for l_copy in csr_copies loop
122 update HR_ITEM_PROPERTIES_B set
123 ALIGNMENT = decode(nvl(ALIGNMENT,hr_api.g_number),nvl(l_original.ALIGNMENT,hr_api.g_number),X_ALIGNMENT,ALIGNMENT),
124 BEVEL = decode(nvl(BEVEL,hr_api.g_number),nvl(l_original.BEVEL,hr_api.g_number),X_BEVEL,BEVEL),
125 CASE_RESTRICTION = decode(nvl(CASE_RESTRICTION,hr_api.g_number),nvl(l_original.CASE_RESTRICTION,hr_api.g_number),X_CASE_RESTRICTION,CASE_RESTRICTION),
126 ENABLED = decode(nvl(ENABLED,hr_api.g_number),nvl(l_original.ENABLED,hr_api.g_number),X_ENABLED,ENABLED),
127 FORMAT_MASK = decode(nvl(FORMAT_MASK,hr_api.g_varchar2),nvl(l_original.FORMAT_MASK,hr_api.g_varchar2),X_FORMAT_MASK,FORMAT_MASK),
128 HEIGHT = decode(nvl(HEIGHT,hr_api.g_number),nvl(l_original.HEIGHT,hr_api.g_number),X_HEIGHT,HEIGHT),
129 INFORMATION_FORMULA_ID = decode(nvl(INFORMATION_FORMULA_ID,hr_api.g_number),nvl(l_original.INFORMATION_FORMULA_ID,hr_api.g_number),X_INFORMATION_FORMULA_ID,INFORMATION_FORMULA_ID),
130 INFORMATION_PARAMETER_ITEM_ID1 = decode(nvl(INFORMATION_PARAMETER_ITEM_ID1,hr_api.g_number),nvl(l_original.INFORMATION_PARAMETER_ITEM_ID1,hr_api.g_number),X_INFORMATION_PARAM_ITEM_ID1,INFORMATION_PARAMETER_ITEM_ID1),
131 INFORMATION_PARAMETER_ITEM_ID2 = decode(nvl(INFORMATION_PARAMETER_ITEM_ID2,hr_api.g_number),nvl(l_original.INFORMATION_PARAMETER_ITEM_ID2,hr_api.g_number),X_INFORMATION_PARAM_ITEM_ID2,INFORMATION_PARAMETER_ITEM_ID2),
132 INFORMATION_PARAMETER_ITEM_ID3 = decode(nvl(INFORMATION_PARAMETER_ITEM_ID3,hr_api.g_number),nvl(l_original.INFORMATION_PARAMETER_ITEM_ID3,hr_api.g_number),X_INFORMATION_PARAM_ITEM_ID3,INFORMATION_PARAMETER_ITEM_ID3),
133 INFORMATION_PARAMETER_ITEM_ID4 = decode(nvl(INFORMATION_PARAMETER_ITEM_ID4,hr_api.g_number),nvl(l_original.INFORMATION_PARAMETER_ITEM_ID4,hr_api.g_number),X_INFORMATION_PARAM_ITEM_ID4,INFORMATION_PARAMETER_ITEM_ID4),
134 INFORMATION_PARAMETER_ITEM_ID5 = decode(nvl(INFORMATION_PARAMETER_ITEM_ID5,hr_api.g_number),nvl(l_original.INFORMATION_PARAMETER_ITEM_ID5,hr_api.g_number),X_INFORMATION_PARAM_ITEM_ID5,INFORMATION_PARAMETER_ITEM_ID5),
135 INSERT_ALLOWED = decode(nvl(INSERT_ALLOWED,hr_api.g_number),nvl(l_original.INSERT_ALLOWED,hr_api.g_number),X_INSERT_ALLOWED,INSERT_ALLOWED),
136 NEXT_NAVIGATION_ITEM_ID = decode(nvl(NEXT_NAVIGATION_ITEM_ID,hr_api.g_number),nvl(l_original.NEXT_NAVIGATION_ITEM_ID,hr_api.g_number),X_NEXT_NAVIGATION_ITEM_ID,NEXT_NAVIGATION_ITEM_ID),
137 PREVIOUS_NAVIGATION_ITEM_ID = decode(nvl(PREVIOUS_NAVIGATION_ITEM_ID,hr_api.g_number),nvl(l_original.PREVIOUS_NAVIGATION_ITEM_ID,hr_api.g_number),X_PREVIOUS_NAVIGATION_ITEM_ID,PREVIOUS_NAVIGATION_ITEM_ID),
138 PROMPT_ALIGNMENT_OFFSET = decode(nvl(PROMPT_ALIGNMENT_OFFSET,hr_api.g_number),nvl(l_original.PROMPT_ALIGNMENT_OFFSET,hr_api.g_number),X_PROMPT_ALIGNMENT_OFFSET,PROMPT_ALIGNMENT_OFFSET),
139 PROMPT_DISPLAY_STYLE = decode(nvl(PROMPT_DISPLAY_STYLE,hr_api.g_number),nvl(l_original.PROMPT_DISPLAY_STYLE,hr_api.g_number),X_PROMPT_DISPLAY_STYLE,PROMPT_DISPLAY_STYLE),
140 PROMPT_EDGE = decode(nvl(PROMPT_EDGE,hr_api.g_number),nvl(l_original.PROMPT_EDGE,hr_api.g_number),X_PROMPT_EDGE,PROMPT_EDGE),
141 PROMPT_EDGE_OFFSET = decode(nvl(PROMPT_EDGE_OFFSET,hr_api.g_number),nvl(l_original.PROMPT_EDGE_OFFSET,hr_api.g_number),X_PROMPT_EDGE_OFFSET,PROMPT_EDGE_OFFSET),
142 PROMPT_EDGE_ALIGNMENT = decode(nvl(PROMPT_EDGE_ALIGNMENT,hr_api.g_number),nvl(l_original.PROMPT_EDGE_ALIGNMENT,hr_api.g_number),X_PROMPT_EDGE_ALIGNMENT,PROMPT_EDGE_ALIGNMENT),
143 PROMPT_TEXT_ALIGNMENT = decode(nvl(PROMPT_TEXT_ALIGNMENT,hr_api.g_number),nvl(l_original.PROMPT_TEXT_ALIGNMENT,hr_api.g_number),X_PROMPT_TEXT_ALIGNMENT,PROMPT_TEXT_ALIGNMENT),
144 QUERY_ALLOWED = decode(nvl(QUERY_ALLOWED,hr_api.g_number),nvl(l_original.QUERY_ALLOWED,hr_api.g_number),X_QUERY_ALLOWED,QUERY_ALLOWED),
145 REQUIRED = decode(nvl(REQUIRED,hr_api.g_number),nvl(l_original.REQUIRED,hr_api.g_number),X_REQUIRED,REQUIRED),
146 UPDATE_ALLOWED = decode(nvl(UPDATE_ALLOWED,hr_api.g_number),nvl(l_original.UPDATE_ALLOWED,hr_api.g_number),X_UPDATE_ALLOWED,UPDATE_ALLOWED),
147 VALIDATION_FORMULA_ID = decode(nvl(VALIDATION_FORMULA_ID,hr_api.g_number),nvl(l_original.VALIDATION_FORMULA_ID,hr_api.g_number),X_VALIDATION_FORMULA_ID,VALIDATION_FORMULA_ID),
148 VALIDATION_PARAMETER_ITEM_ID1 = decode(nvl(VALIDATION_PARAMETER_ITEM_ID1,hr_api.g_number),nvl(l_original.VALIDATION_PARAMETER_ITEM_ID1,hr_api.g_number),X_VALIDATION_PARAM_ITEM_ID1,VALIDATION_PARAMETER_ITEM_ID1),
149 VALIDATION_PARAMETER_ITEM_ID2 = decode(nvl(VALIDATION_PARAMETER_ITEM_ID2,hr_api.g_number),nvl(l_original.VALIDATION_PARAMETER_ITEM_ID2,hr_api.g_number),X_VALIDATION_PARAM_ITEM_ID2,VALIDATION_PARAMETER_ITEM_ID2),
150 VALIDATION_PARAMETER_ITEM_ID3 = decode(nvl(VALIDATION_PARAMETER_ITEM_ID3,hr_api.g_number),nvl(l_original.VALIDATION_PARAMETER_ITEM_ID3,hr_api.g_number),X_VALIDATION_PARAM_ITEM_ID3,VALIDATION_PARAMETER_ITEM_ID3),
151 VALIDATION_PARAMETER_ITEM_ID4 = decode(nvl(VALIDATION_PARAMETER_ITEM_ID4,hr_api.g_number),nvl(l_original.VALIDATION_PARAMETER_ITEM_ID4,hr_api.g_number),X_VALIDATION_PARAM_ITEM_ID4,VALIDATION_PARAMETER_ITEM_ID4),
152 VALIDATION_PARAMETER_ITEM_ID5 = decode(nvl(VALIDATION_PARAMETER_ITEM_ID5,hr_api.g_number),nvl(l_original.VALIDATION_PARAMETER_ITEM_ID5,hr_api.g_number),X_VALIDATION_PARAM_ITEM_ID5,VALIDATION_PARAMETER_ITEM_ID5),
153 VISIBLE = decode(nvl(VISIBLE,hr_api.g_number),nvl(l_original.VISIBLE,hr_api.g_number),X_VISIBLE,VISIBLE),
154 WIDTH = decode(nvl(WIDTH,hr_api.g_number),nvl(l_original.WIDTH,hr_api.g_number),X_WIDTH,WIDTH),
155 X_POSITION = decode(nvl(X_POSITION,hr_api.g_number),nvl(l_original.X_POSITION,hr_api.g_number),X_X_POSITION,X_POSITION),
156 Y_POSITION = decode(nvl(Y_POSITION,hr_api.g_number),nvl(l_original.Y_POSITION,hr_api.g_number),X_Y_POSITION,Y_POSITION),
157 INFORMATION_CATEGORY = decode(nvl(INFORMATION_CATEGORY,hr_api.g_varchar2),nvl(l_original.INFORMATION_CATEGORY,hr_api.g_varchar2),X_INFORMATION_CATEGORY,INFORMATION_CATEGORY),
158 INFORMATION1 = decode(nvl(INFORMATION1 ,hr_api.g_varchar2),nvl(l_original.INFORMATION1 ,hr_api.g_varchar2),X_INFORMATION1 ,INFORMATION1 ),
159 INFORMATION2 = decode(nvl(INFORMATION2 ,hr_api.g_varchar2),nvl(l_original.INFORMATION2 ,hr_api.g_varchar2),X_INFORMATION2 ,INFORMATION2 ),
160 INFORMATION3 = decode(nvl(INFORMATION3 ,hr_api.g_varchar2),nvl(l_original.INFORMATION3 ,hr_api.g_varchar2),X_INFORMATION3 ,INFORMATION3 ),
161 INFORMATION4 = decode(nvl(INFORMATION4 ,hr_api.g_varchar2),nvl(l_original.INFORMATION4 ,hr_api.g_varchar2),X_INFORMATION4 ,INFORMATION4 ),
162 INFORMATION5 = decode(nvl(INFORMATION5 ,hr_api.g_varchar2),nvl(l_original.INFORMATION5 ,hr_api.g_varchar2),X_INFORMATION5 ,INFORMATION5 ),
163 INFORMATION6 = decode(nvl(INFORMATION6 ,hr_api.g_varchar2),nvl(l_original.INFORMATION6 ,hr_api.g_varchar2),X_INFORMATION6 ,INFORMATION6 ),
164 INFORMATION7 = decode(nvl(INFORMATION7 ,hr_api.g_varchar2),nvl(l_original.INFORMATION7 ,hr_api.g_varchar2),X_INFORMATION7 ,INFORMATION7 ),
165 INFORMATION8 = decode(nvl(INFORMATION8 ,hr_api.g_varchar2),nvl(l_original.INFORMATION8 ,hr_api.g_varchar2),X_INFORMATION8 ,INFORMATION8 ),
166 INFORMATION9 = decode(nvl(INFORMATION9 ,hr_api.g_varchar2),nvl(l_original.INFORMATION9 ,hr_api.g_varchar2),X_INFORMATION9 ,INFORMATION9 ),
167 INFORMATION10 = decode(nvl(INFORMATION10,hr_api.g_varchar2),nvl(l_original.INFORMATION10,hr_api.g_varchar2),X_INFORMATION10,INFORMATION10),
168 INFORMATION11 = decode(nvl(INFORMATION11,hr_api.g_varchar2),nvl(l_original.INFORMATION11,hr_api.g_varchar2),X_INFORMATION11,INFORMATION11),
169 INFORMATION12 = decode(nvl(INFORMATION12,hr_api.g_varchar2),nvl(l_original.INFORMATION12,hr_api.g_varchar2),X_INFORMATION12,INFORMATION12),
170 INFORMATION13 = decode(nvl(INFORMATION13,hr_api.g_varchar2),nvl(l_original.INFORMATION13,hr_api.g_varchar2),X_INFORMATION13,INFORMATION13),
171 INFORMATION14 = decode(nvl(INFORMATION14,hr_api.g_varchar2),nvl(l_original.INFORMATION14,hr_api.g_varchar2),X_INFORMATION14,INFORMATION14),
172 INFORMATION15 = decode(nvl(INFORMATION15,hr_api.g_varchar2),nvl(l_original.INFORMATION15,hr_api.g_varchar2),X_INFORMATION15,INFORMATION15),
173 INFORMATION16 = decode(nvl(INFORMATION16,hr_api.g_varchar2),nvl(l_original.INFORMATION16,hr_api.g_varchar2),X_INFORMATION16,INFORMATION16),
174 INFORMATION17 = decode(nvl(INFORMATION17,hr_api.g_varchar2),nvl(l_original.INFORMATION17,hr_api.g_varchar2),X_INFORMATION17,INFORMATION17),
175 INFORMATION18 = decode(nvl(INFORMATION18,hr_api.g_varchar2),nvl(l_original.INFORMATION18,hr_api.g_varchar2),X_INFORMATION18,INFORMATION18),
176 INFORMATION19 = decode(nvl(INFORMATION19,hr_api.g_varchar2),nvl(l_original.INFORMATION19,hr_api.g_varchar2),X_INFORMATION19,INFORMATION19),
177 INFORMATION20 = decode(nvl(INFORMATION20,hr_api.g_varchar2),nvl(l_original.INFORMATION20,hr_api.g_varchar2),X_INFORMATION20,INFORMATION20),
178 INFORMATION21 = decode(nvl(INFORMATION21,hr_api.g_varchar2),nvl(l_original.INFORMATION21,hr_api.g_varchar2),X_INFORMATION21,INFORMATION21),
179 INFORMATION22 = decode(nvl(INFORMATION22,hr_api.g_varchar2),nvl(l_original.INFORMATION22,hr_api.g_varchar2),X_INFORMATION22,INFORMATION22),
180 INFORMATION23 = decode(nvl(INFORMATION23,hr_api.g_varchar2),nvl(l_original.INFORMATION23,hr_api.g_varchar2),X_INFORMATION23,INFORMATION23),
181 INFORMATION24 = decode(nvl(INFORMATION24,hr_api.g_varchar2),nvl(l_original.INFORMATION24,hr_api.g_varchar2),X_INFORMATION24,INFORMATION24),
182 INFORMATION25 = decode(nvl(INFORMATION25,hr_api.g_varchar2),nvl(l_original.INFORMATION25,hr_api.g_varchar2),X_INFORMATION25,INFORMATION25),
183 INFORMATION26 = decode(nvl(INFORMATION26,hr_api.g_varchar2),nvl(l_original.INFORMATION26,hr_api.g_varchar2),X_INFORMATION26,INFORMATION26),
184 INFORMATION27 = decode(nvl(INFORMATION27,hr_api.g_varchar2),nvl(l_original.INFORMATION27,hr_api.g_varchar2),X_INFORMATION27,INFORMATION27),
185 INFORMATION28 = decode(nvl(INFORMATION28,hr_api.g_varchar2),nvl(l_original.INFORMATION28,hr_api.g_varchar2),X_INFORMATION28,INFORMATION28),
186 INFORMATION29 = decode(nvl(INFORMATION29,hr_api.g_varchar2),nvl(l_original.INFORMATION29,hr_api.g_varchar2),X_INFORMATION29,INFORMATION29),
187 INFORMATION30 = decode(nvl(INFORMATION30,hr_api.g_varchar2),nvl(l_original.INFORMATION30,hr_api.g_varchar2),X_INFORMATION30,INFORMATION30),
188 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
189 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
190 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
191 where ITEM_PROPERTY_ID = l_copy.ITEM_PROPERTY_ID;
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195 update HR_ITEM_PROPERTIES_TL set
196 DEFAULT_VALUE = decode(nvl(DEFAULT_VALUE,hr_api.g_varchar2),nvl(l_original.DEFAULT_VALUE,hr_api.g_varchar2),X_DEFAULT_VALUE,DEFAULT_VALUE),
197 INFORMATION_PROMPT = decode(nvl(INFORMATION_PROMPT,hr_api.g_varchar2),nvl(l_original.INFORMATION_PROMPT,hr_api.g_varchar2),X_INFORMATION_PROMPT,INFORMATION_PROMPT),
198 LABEL = decode(nvl(LABEL,hr_api.g_varchar2),nvl(l_original.LABEL,hr_api.g_varchar2),X_LABEL,LABEL),
199 PROMPT_TEXT = decode(nvl(PROMPT_TEXT,hr_api.g_varchar2),nvl(l_original.PROMPT_TEXT,hr_api.g_varchar2),X_PROMPT_TEXT,PROMPT_TEXT),
200 TOOLTIP_TEXT = decode(nvl(TOOLTIP_TEXT,hr_api.g_varchar2),nvl(l_original.TOOLTIP_TEXT,hr_api.g_varchar2),X_TOOLTIP_TEXT,TOOLTIP_TEXT),
201 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
202 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
203 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
204 SOURCE_LANG = userenv('LANG')
205 where ITEM_PROPERTY_ID = l_copy.ITEM_PROPERTY_ID
206 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end loop;
211 else
212 close csr_original;
213 end if;
214 end if;
215 end UPDATE_COPIES;
216 --
217
218
219 procedure OWNER_TO_WHO (
220 X_OWNER in VARCHAR2,
221 X_CREATION_DATE out nocopy DATE,
222 X_CREATED_BY out nocopy NUMBER,
223 X_LAST_UPDATE_DATE out nocopy DATE,
224 X_LAST_UPDATED_BY out nocopy NUMBER,
225 X_LAST_UPDATE_LOGIN out nocopy NUMBER
226 ) is
227 begin
228 if X_OWNER = 'SEED' then
229 X_CREATED_BY := 1;
230 X_LAST_UPDATED_BY := 1;
231 else
232 X_CREATED_BY := 0;
233 X_LAST_UPDATED_BY := 0;
234 end if;
235 X_CREATION_DATE := sysdate;
236 X_LAST_UPDATE_DATE := sysdate;
237 X_LAST_UPDATE_LOGIN := 0;
238 end OWNER_TO_WHO;
239 procedure INSERT_ROW (
240 X_ROWID in out nocopy VARCHAR2,
241 X_ITEM_PROPERTY_ID in NUMBER,
242 X_UPDATE_ALLOWED in NUMBER,
243 X_INFORMATION20 in VARCHAR2,
244 X_INFORMATION21 in VARCHAR2,
245 X_INFORMATION22 in VARCHAR2,
246 X_INFORMATION23 in VARCHAR2,
247 X_INFORMATION24 in VARCHAR2,
248 X_INFORMATION25 in VARCHAR2,
249 X_INFORMATION26 in VARCHAR2,
250 X_INFORMATION27 in VARCHAR2,
251 X_INFORMATION28 in VARCHAR2,
252 X_INFORMATION29 in VARCHAR2,
253 X_INFORMATION30 in VARCHAR2,
254 X_NEXT_NAVIGATION_ITEM_ID in NUMBER,
255 X_PREVIOUS_NAVIGATION_ITEM_ID in NUMBER,
256 X_PROMPT_TEXT_ALIGNMENT in NUMBER,
257 X_QUERY_ALLOWED in NUMBER,
258 X_REQUIRED in NUMBER,
259 X_WIDTH in NUMBER,
260 X_X_POSITION in NUMBER,
261 X_Y_POSITION in NUMBER,
262 X_VALIDATION_PARAM_ITEM_ID3 in NUMBER,
263 X_VALIDATION_PARAM_ITEM_ID4 in NUMBER,
264 X_VALIDATION_PARAM_ITEM_ID1 in NUMBER,
265 X_PROMPT_EDGE_OFFSET in NUMBER,
266 X_INFORMATION_CATEGORY in VARCHAR2,
267 X_INFORMATION1 in VARCHAR2,
268 X_INFORMATION2 in VARCHAR2,
269 X_INFORMATION3 in VARCHAR2,
270 X_INFORMATION4 in VARCHAR2,
271 X_INFORMATION5 in VARCHAR2,
272 X_INFORMATION6 in VARCHAR2,
273 X_INFORMATION7 in VARCHAR2,
274 X_INFORMATION8 in VARCHAR2,
275 X_INFORMATION9 in VARCHAR2,
276 X_INFORMATION10 in VARCHAR2,
277 X_INFORMATION11 in VARCHAR2,
278 X_INFORMATION12 in VARCHAR2,
279 X_INFORMATION13 in VARCHAR2,
280 X_INFORMATION14 in VARCHAR2,
281 X_INFORMATION15 in VARCHAR2,
282 X_FORM_ITEM_ID in NUMBER,
283 X_TEMPLATE_ITEM_ID in NUMBER,
284 X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
285 X_ALIGNMENT in NUMBER,
286 X_BEVEL in NUMBER,
287 X_CASE_RESTRICTION in NUMBER,
288 X_DEFAULT_VALUE in VARCHAR2,
289 X_ENABLED in NUMBER,
290 X_FORMAT_MASK in VARCHAR2,
291 X_HEIGHT in NUMBER,
292 X_INFORMATION_FORMULA_ID in NUMBER,
293 X_INFORMATION_PARAM_ITEM_ID1 in NUMBER,
294 X_INFORMATION_PARAM_ITEM_ID2 in NUMBER,
295 X_PROMPT_EDGE_ALIGNMENT in NUMBER,
296 X_VALIDATION_PARAM_ITEM_ID5 in NUMBER,
297 X_VALIDATION_FORMULA_ID in NUMBER,
298 X_INFORMATION16 in VARCHAR2,
299 X_INFORMATION17 in VARCHAR2,
300 X_INFORMATION18 in VARCHAR2,
301 X_INFORMATION19 in VARCHAR2,
302 X_VALIDATION_PARAM_ITEM_ID2 in NUMBER,
303 X_VISIBLE in NUMBER,
304 X_INFORMATION_PARAM_ITEM_ID3 in NUMBER,
305 X_INFORMATION_PARAM_ITEM_ID4 in NUMBER,
306 X_INFORMATION_PARAM_ITEM_ID5 in NUMBER,
307 X_INSERT_ALLOWED in NUMBER,
308 X_PROMPT_ALIGNMENT_OFFSET in NUMBER,
309 X_PROMPT_DISPLAY_STYLE in NUMBER,
310 X_PROMPT_EDGE in NUMBER,
311 X_INFORMATION_PROMPT in VARCHAR2,
312 X_LABEL in VARCHAR2,
313 X_PROMPT_TEXT in VARCHAR2,
314 X_TOOLTIP_TEXT in VARCHAR2,
315 X_CREATION_DATE in DATE,
316 X_CREATED_BY in NUMBER,
317 X_LAST_UPDATE_DATE in DATE,
318 X_LAST_UPDATED_BY in NUMBER,
319 X_LAST_UPDATE_LOGIN in NUMBER
320 ) is
321 cursor C is select ROWID from HR_ITEM_PROPERTIES_B
322 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID
323 ;
324 begin
325 insert into HR_ITEM_PROPERTIES_B (
326 UPDATE_ALLOWED,
327 INFORMATION20,
328 INFORMATION21,
329 INFORMATION22,
330 INFORMATION23,
331 INFORMATION24,
332 INFORMATION25,
333 INFORMATION26,
334 INFORMATION27,
335 INFORMATION28,
336 INFORMATION29,
337 INFORMATION30,
338 NEXT_NAVIGATION_ITEM_ID,
339 PREVIOUS_NAVIGATION_ITEM_ID,
340 PROMPT_TEXT_ALIGNMENT,
341 QUERY_ALLOWED,
342 REQUIRED,
343 WIDTH,
344 X_POSITION,
345 Y_POSITION,
346 VALIDATION_PARAMETER_ITEM_ID3,
347 VALIDATION_PARAMETER_ITEM_ID4,
348 VALIDATION_PARAMETER_ITEM_ID1,
349 PROMPT_EDGE_OFFSET,
350 INFORMATION_CATEGORY,
351 INFORMATION1,
352 INFORMATION2,
353 INFORMATION3,
354 INFORMATION4,
355 INFORMATION5,
356 INFORMATION6,
357 INFORMATION7,
358 INFORMATION8,
359 INFORMATION9,
360 INFORMATION10,
361 INFORMATION11,
362 INFORMATION12,
363 INFORMATION13,
364 INFORMATION14,
365 INFORMATION15,
366 ITEM_PROPERTY_ID,
367 FORM_ITEM_ID,
368 TEMPLATE_ITEM_ID,
369 TEMPLATE_ITEM_CONTEXT_ID,
370 ALIGNMENT,
371 BEVEL,
372 CASE_RESTRICTION,
373 ENABLED,
374 FORMAT_MASK,
375 HEIGHT,
376 INFORMATION_FORMULA_ID,
377 INFORMATION_PARAMETER_ITEM_ID1,
378 INFORMATION_PARAMETER_ITEM_ID2,
379 PROMPT_EDGE_ALIGNMENT,
380 VALIDATION_PARAMETER_ITEM_ID5,
381 VALIDATION_FORMULA_ID,
382 INFORMATION16,
383 INFORMATION17,
384 INFORMATION18,
385 INFORMATION19,
386 VALIDATION_PARAMETER_ITEM_ID2,
387 VISIBLE,
388 INFORMATION_PARAMETER_ITEM_ID3,
389 INFORMATION_PARAMETER_ITEM_ID4,
390 INFORMATION_PARAMETER_ITEM_ID5,
391 INSERT_ALLOWED,
392 PROMPT_ALIGNMENT_OFFSET,
393 PROMPT_DISPLAY_STYLE,
394 PROMPT_EDGE,
395 CREATION_DATE,
396 CREATED_BY,
397 LAST_UPDATE_DATE,
398 LAST_UPDATED_BY,
399 LAST_UPDATE_LOGIN
400 ) values (
401 X_UPDATE_ALLOWED,
402 X_INFORMATION20,
403 X_INFORMATION21,
404 X_INFORMATION22,
405 X_INFORMATION23,
406 X_INFORMATION24,
407 X_INFORMATION25,
408 X_INFORMATION26,
409 X_INFORMATION27,
410 X_INFORMATION28,
411 X_INFORMATION29,
412 X_INFORMATION30,
413 X_NEXT_NAVIGATION_ITEM_ID,
414 X_PREVIOUS_NAVIGATION_ITEM_ID,
415 X_PROMPT_TEXT_ALIGNMENT,
416 X_QUERY_ALLOWED,
417 X_REQUIRED,
418 X_WIDTH,
419 X_X_POSITION,
420 X_Y_POSITION,
421 X_VALIDATION_PARAM_ITEM_ID3,
422 X_VALIDATION_PARAM_ITEM_ID4,
423 X_VALIDATION_PARAM_ITEM_ID1,
424 X_PROMPT_EDGE_OFFSET,
425 X_INFORMATION_CATEGORY,
426 X_INFORMATION1,
427 X_INFORMATION2,
428 X_INFORMATION3,
429 X_INFORMATION4,
430 X_INFORMATION5,
431 X_INFORMATION6,
432 X_INFORMATION7,
433 X_INFORMATION8,
434 X_INFORMATION9,
435 X_INFORMATION10,
436 X_INFORMATION11,
437 X_INFORMATION12,
438 X_INFORMATION13,
439 X_INFORMATION14,
440 X_INFORMATION15,
441 X_ITEM_PROPERTY_ID,
442 X_FORM_ITEM_ID,
443 X_TEMPLATE_ITEM_ID,
444 X_TEMPLATE_ITEM_CONTEXT_ID,
445 X_ALIGNMENT,
446 X_BEVEL,
447 X_CASE_RESTRICTION,
448 X_ENABLED,
449 X_FORMAT_MASK,
450 X_HEIGHT,
451 X_INFORMATION_FORMULA_ID,
452 X_INFORMATION_PARAM_ITEM_ID1,
453 X_INFORMATION_PARAM_ITEM_ID2,
454 X_PROMPT_EDGE_ALIGNMENT,
455 X_VALIDATION_PARAM_ITEM_ID5,
456 X_VALIDATION_FORMULA_ID,
457 X_INFORMATION16,
458 X_INFORMATION17,
459 X_INFORMATION18,
460 X_INFORMATION19,
461 X_VALIDATION_PARAM_ITEM_ID2,
462 X_VISIBLE,
463 X_INFORMATION_PARAM_ITEM_ID3,
464 X_INFORMATION_PARAM_ITEM_ID4,
465 X_INFORMATION_PARAM_ITEM_ID5,
466 X_INSERT_ALLOWED,
467 X_PROMPT_ALIGNMENT_OFFSET,
468 X_PROMPT_DISPLAY_STYLE,
469 X_PROMPT_EDGE,
470 X_CREATION_DATE,
471 X_CREATED_BY,
472 X_LAST_UPDATE_DATE,
473 X_LAST_UPDATED_BY,
474 X_LAST_UPDATE_LOGIN
475 );
476
477 insert into HR_ITEM_PROPERTIES_TL (
478 DEFAULT_VALUE,
479 INFORMATION_PROMPT,
480 LABEL,
481 LAST_UPDATED_BY,
482 LAST_UPDATE_LOGIN,
483 CREATED_BY,
484 CREATION_DATE,
485 ITEM_PROPERTY_ID,
486 LAST_UPDATE_DATE,
487 PROMPT_TEXT,
488 TOOLTIP_TEXT,
489 LANGUAGE,
490 SOURCE_LANG
491 ) select
492 X_DEFAULT_VALUE,
493 X_INFORMATION_PROMPT,
494 X_LABEL,
495 X_LAST_UPDATED_BY,
496 X_LAST_UPDATE_LOGIN,
497 X_CREATED_BY,
498 X_CREATION_DATE,
499 X_ITEM_PROPERTY_ID,
500 X_LAST_UPDATE_DATE,
501 X_PROMPT_TEXT,
502 X_TOOLTIP_TEXT,
503 L.LANGUAGE_CODE,
504 userenv('LANG')
505 from FND_LANGUAGES L
506 where L.INSTALLED_FLAG in ('I', 'B')
507 and not exists
508 (select NULL
509 from HR_ITEM_PROPERTIES_TL T
510 where T.ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID
511 and T.LANGUAGE = L.LANGUAGE_CODE);
512
513 open c;
514 fetch c into X_ROWID;
515 if (c%notfound) then
516 close c;
517 raise no_data_found;
518 end if;
519 close c;
520
521 end INSERT_ROW;
522
523 procedure LOCK_ROW (
524 X_ITEM_PROPERTY_ID in NUMBER,
525 X_UPDATE_ALLOWED in NUMBER,
526 X_INFORMATION20 in VARCHAR2,
527 X_INFORMATION21 in VARCHAR2,
528 X_INFORMATION22 in VARCHAR2,
529 X_INFORMATION23 in VARCHAR2,
530 X_INFORMATION24 in VARCHAR2,
531 X_INFORMATION25 in VARCHAR2,
532 X_INFORMATION26 in VARCHAR2,
533 X_INFORMATION27 in VARCHAR2,
534 X_INFORMATION28 in VARCHAR2,
535 X_INFORMATION29 in VARCHAR2,
536 X_INFORMATION30 in VARCHAR2,
537 X_NEXT_NAVIGATION_ITEM_ID in NUMBER,
538 X_PREVIOUS_NAVIGATION_ITEM_ID in NUMBER,
539 X_PROMPT_TEXT_ALIGNMENT in NUMBER,
540 X_QUERY_ALLOWED in NUMBER,
541 X_REQUIRED in NUMBER,
542 X_WIDTH in NUMBER,
543 X_X_POSITION in NUMBER,
544 X_Y_POSITION in NUMBER,
545 X_VALIDATION_PARAM_ITEM_ID3 in NUMBER,
546 X_VALIDATION_PARAM_ITEM_ID4 in NUMBER,
547 X_VALIDATION_PARAM_ITEM_ID1 in NUMBER,
548 X_PROMPT_EDGE_OFFSET in NUMBER,
549 X_INFORMATION_CATEGORY in VARCHAR2,
550 X_INFORMATION1 in VARCHAR2,
551 X_INFORMATION2 in VARCHAR2,
552 X_INFORMATION3 in VARCHAR2,
553 X_INFORMATION4 in VARCHAR2,
554 X_INFORMATION5 in VARCHAR2,
555 X_INFORMATION6 in VARCHAR2,
556 X_INFORMATION7 in VARCHAR2,
557 X_INFORMATION8 in VARCHAR2,
558 X_INFORMATION9 in VARCHAR2,
559 X_INFORMATION10 in VARCHAR2,
560 X_INFORMATION11 in VARCHAR2,
561 X_INFORMATION12 in VARCHAR2,
562 X_INFORMATION13 in VARCHAR2,
563 X_INFORMATION14 in VARCHAR2,
564 X_INFORMATION15 in VARCHAR2,
565 X_FORM_ITEM_ID in NUMBER,
566 X_TEMPLATE_ITEM_ID in NUMBER,
567 X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
568 X_ALIGNMENT in NUMBER,
569 X_BEVEL in NUMBER,
570 X_CASE_RESTRICTION in NUMBER,
571 X_DEFAULT_VALUE in VARCHAR2,
572 X_ENABLED in NUMBER,
573 X_FORMAT_MASK in VARCHAR2,
574 X_HEIGHT in NUMBER,
575 X_INFORMATION_FORMULA_ID in NUMBER,
576 X_INFORMATION_PARAM_ITEM_ID1 in NUMBER,
577 X_INFORMATION_PARAM_ITEM_ID2 in NUMBER,
578 X_PROMPT_EDGE_ALIGNMENT in NUMBER,
579 X_VALIDATION_PARAM_ITEM_ID5 in NUMBER,
580 X_VALIDATION_FORMULA_ID in NUMBER,
581 X_INFORMATION16 in VARCHAR2,
582 X_INFORMATION17 in VARCHAR2,
583 X_INFORMATION18 in VARCHAR2,
584 X_INFORMATION19 in VARCHAR2,
585 X_VALIDATION_PARAM_ITEM_ID2 in NUMBER,
586 X_VISIBLE in NUMBER,
587 X_INFORMATION_PARAM_ITEM_ID3 in NUMBER,
588 X_INFORMATION_PARAM_ITEM_ID4 in NUMBER,
589 X_INFORMATION_PARAM_ITEM_ID5 in NUMBER,
590 X_INSERT_ALLOWED in NUMBER,
591 X_PROMPT_ALIGNMENT_OFFSET in NUMBER,
592 X_PROMPT_DISPLAY_STYLE in NUMBER,
593 X_PROMPT_EDGE in NUMBER,
594 X_INFORMATION_PROMPT in VARCHAR2,
595 X_LABEL in VARCHAR2,
596 X_PROMPT_TEXT in VARCHAR2,
597 X_TOOLTIP_TEXT in VARCHAR2
598 ) is
599 cursor c is select
600 UPDATE_ALLOWED,
601 INFORMATION20,
602 INFORMATION21,
603 INFORMATION22,
604 INFORMATION23,
605 INFORMATION24,
606 INFORMATION25,
607 INFORMATION26,
608 INFORMATION27,
609 INFORMATION28,
610 INFORMATION29,
611 INFORMATION30,
612 NEXT_NAVIGATION_ITEM_ID,
613 PREVIOUS_NAVIGATION_ITEM_ID,
614 PROMPT_TEXT_ALIGNMENT,
615 QUERY_ALLOWED,
616 REQUIRED,
617 WIDTH,
618 X_POSITION,
619 Y_POSITION,
620 VALIDATION_PARAMETER_ITEM_ID3,
621 VALIDATION_PARAMETER_ITEM_ID4,
622 VALIDATION_PARAMETER_ITEM_ID1,
623 PROMPT_EDGE_OFFSET,
624 INFORMATION_CATEGORY,
625 INFORMATION1,
626 INFORMATION2,
627 INFORMATION3,
628 INFORMATION4,
629 INFORMATION5,
630 INFORMATION6,
631 INFORMATION7,
632 INFORMATION8,
633 INFORMATION9,
634 INFORMATION10,
635 INFORMATION11,
636 INFORMATION12,
637 INFORMATION13,
638 INFORMATION14,
639 INFORMATION15,
640 FORM_ITEM_ID,
641 TEMPLATE_ITEM_ID,
642 TEMPLATE_ITEM_CONTEXT_ID,
643 ALIGNMENT,
644 BEVEL,
645 CASE_RESTRICTION,
646 ENABLED,
647 FORMAT_MASK,
648 HEIGHT,
649 INFORMATION_FORMULA_ID,
650 INFORMATION_PARAMETER_ITEM_ID1,
651 INFORMATION_PARAMETER_ITEM_ID2,
652 PROMPT_EDGE_ALIGNMENT,
653 VALIDATION_PARAMETER_ITEM_ID5,
654 VALIDATION_FORMULA_ID,
655 INFORMATION16,
656 INFORMATION17,
657 INFORMATION18,
658 INFORMATION19,
659 VALIDATION_PARAMETER_ITEM_ID2,
660 VISIBLE,
661 INFORMATION_PARAMETER_ITEM_ID3,
662 INFORMATION_PARAMETER_ITEM_ID4,
663 INFORMATION_PARAMETER_ITEM_ID5,
664 INSERT_ALLOWED,
665 PROMPT_ALIGNMENT_OFFSET,
666 PROMPT_DISPLAY_STYLE,
667 PROMPT_EDGE
668 from HR_ITEM_PROPERTIES_B
669 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID
670 for update of ITEM_PROPERTY_ID nowait;
671 recinfo c%rowtype;
672
673 cursor c1 is select
674 DEFAULT_VALUE,
675 INFORMATION_PROMPT,
676 LABEL,
677 PROMPT_TEXT,
678 TOOLTIP_TEXT,
679 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
680 from HR_ITEM_PROPERTIES_TL
681 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID
682 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
683 for update of ITEM_PROPERTY_ID nowait;
684 begin
685 open c;
686 fetch c into recinfo;
687 if (c%notfound) then
688 close c;
689 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
690 app_exception.raise_exception;
691 end if;
692 close c;
693 if ( ((recinfo.UPDATE_ALLOWED = X_UPDATE_ALLOWED)
694 OR ((recinfo.UPDATE_ALLOWED is null) AND (X_UPDATE_ALLOWED is null)))
695 AND ((recinfo.INFORMATION20 = X_INFORMATION20)
696 OR ((recinfo.INFORMATION20 is null) AND (X_INFORMATION20 is null)))
697 AND ((recinfo.INFORMATION21 = X_INFORMATION21)
698 OR ((recinfo.INFORMATION21 is null) AND (X_INFORMATION21 is null)))
699 AND ((recinfo.INFORMATION22 = X_INFORMATION22)
700 OR ((recinfo.INFORMATION22 is null) AND (X_INFORMATION22 is null)))
701 AND ((recinfo.INFORMATION23 = X_INFORMATION23)
702 OR ((recinfo.INFORMATION23 is null) AND (X_INFORMATION23 is null)))
703 AND ((recinfo.INFORMATION24 = X_INFORMATION24)
704 OR ((recinfo.INFORMATION24 is null) AND (X_INFORMATION24 is null)))
705 AND ((recinfo.INFORMATION25 = X_INFORMATION25)
706 OR ((recinfo.INFORMATION25 is null) AND (X_INFORMATION25 is null)))
707 AND ((recinfo.INFORMATION26 = X_INFORMATION26)
708 OR ((recinfo.INFORMATION26 is null) AND (X_INFORMATION26 is null)))
709 AND ((recinfo.INFORMATION27 = X_INFORMATION27)
710 OR ((recinfo.INFORMATION27 is null) AND (X_INFORMATION27 is null)))
711 AND ((recinfo.INFORMATION28 = X_INFORMATION28)
712 OR ((recinfo.INFORMATION28 is null) AND (X_INFORMATION28 is null)))
713 AND ((recinfo.INFORMATION29 = X_INFORMATION29)
714 OR ((recinfo.INFORMATION29 is null) AND (X_INFORMATION29 is null)))
715 AND ((recinfo.INFORMATION30 = X_INFORMATION30)
716 OR ((recinfo.INFORMATION30 is null) AND (X_INFORMATION30 is null)))
717 AND ((recinfo.NEXT_NAVIGATION_ITEM_ID = X_NEXT_NAVIGATION_ITEM_ID)
718 OR ((recinfo.NEXT_NAVIGATION_ITEM_ID is null) AND (X_NEXT_NAVIGATION_ITEM_ID is null)))
719 AND ((recinfo.PREVIOUS_NAVIGATION_ITEM_ID = X_PREVIOUS_NAVIGATION_ITEM_ID)
720 OR ((recinfo.PREVIOUS_NAVIGATION_ITEM_ID is null) AND (X_PREVIOUS_NAVIGATION_ITEM_ID is null)))
721 AND ((recinfo.PROMPT_TEXT_ALIGNMENT = X_PROMPT_TEXT_ALIGNMENT)
722 OR ((recinfo.PROMPT_TEXT_ALIGNMENT is null) AND (X_PROMPT_TEXT_ALIGNMENT is null)))
723 AND ((recinfo.QUERY_ALLOWED = X_QUERY_ALLOWED)
724 OR ((recinfo.QUERY_ALLOWED is null) AND (X_QUERY_ALLOWED is null)))
725 AND ((recinfo.REQUIRED = X_REQUIRED)
726 OR ((recinfo.REQUIRED is null) AND (X_REQUIRED is null)))
727 AND ((recinfo.WIDTH = X_WIDTH)
728 OR ((recinfo.WIDTH is null) AND (X_WIDTH is null)))
729 AND ((recinfo.X_POSITION = X_X_POSITION)
730 OR ((recinfo.X_POSITION is null) AND (X_X_POSITION is null)))
731 AND ((recinfo.Y_POSITION = X_Y_POSITION)
732 OR ((recinfo.Y_POSITION is null) AND (X_Y_POSITION is null)))
733 AND ((recinfo.VALIDATION_PARAMETER_ITEM_ID3 = X_VALIDATION_PARAM_ITEM_ID3)
734 OR ((recinfo.VALIDATION_PARAMETER_ITEM_ID3 is null) AND (X_VALIDATION_PARAM_ITEM_ID3 is null)))
735 AND ((recinfo.VALIDATION_PARAMETER_ITEM_ID4 = X_VALIDATION_PARAM_ITEM_ID4)
736 OR ((recinfo.VALIDATION_PARAMETER_ITEM_ID4 is null) AND (X_VALIDATION_PARAM_ITEM_ID4 is null)))
737 AND ((recinfo.VALIDATION_PARAMETER_ITEM_ID1 = X_VALIDATION_PARAM_ITEM_ID1)
738 OR ((recinfo.VALIDATION_PARAMETER_ITEM_ID1 is null) AND (X_VALIDATION_PARAM_ITEM_ID1 is null)))
739 AND ((recinfo.PROMPT_EDGE_OFFSET = X_PROMPT_EDGE_OFFSET)
740 OR ((recinfo.PROMPT_EDGE_OFFSET is null) AND (X_PROMPT_EDGE_OFFSET is null)))
741 AND ((recinfo.INFORMATION_CATEGORY = X_INFORMATION_CATEGORY)
742 OR ((recinfo.INFORMATION_CATEGORY is null) AND (X_INFORMATION_CATEGORY is null)))
743 AND ((recinfo.INFORMATION1 = X_INFORMATION1)
744 OR ((recinfo.INFORMATION1 is null) AND (X_INFORMATION1 is null)))
745 AND ((recinfo.INFORMATION2 = X_INFORMATION2)
746 OR ((recinfo.INFORMATION2 is null) AND (X_INFORMATION2 is null)))
747 AND ((recinfo.INFORMATION3 = X_INFORMATION3)
748 OR ((recinfo.INFORMATION3 is null) AND (X_INFORMATION3 is null)))
749 AND ((recinfo.INFORMATION4 = X_INFORMATION4)
750 OR ((recinfo.INFORMATION4 is null) AND (X_INFORMATION4 is null)))
751 AND ((recinfo.INFORMATION5 = X_INFORMATION5)
752 OR ((recinfo.INFORMATION5 is null) AND (X_INFORMATION5 is null)))
753 AND ((recinfo.INFORMATION6 = X_INFORMATION6)
754 OR ((recinfo.INFORMATION6 is null) AND (X_INFORMATION6 is null)))
755 AND ((recinfo.INFORMATION7 = X_INFORMATION7)
756 OR ((recinfo.INFORMATION7 is null) AND (X_INFORMATION7 is null)))
757 AND ((recinfo.INFORMATION8 = X_INFORMATION8)
758 OR ((recinfo.INFORMATION8 is null) AND (X_INFORMATION8 is null)))
759 AND ((recinfo.INFORMATION9 = X_INFORMATION9)
760 OR ((recinfo.INFORMATION9 is null) AND (X_INFORMATION9 is null)))
761 AND ((recinfo.INFORMATION10 = X_INFORMATION10)
762 OR ((recinfo.INFORMATION10 is null) AND (X_INFORMATION10 is null)))
763 AND ((recinfo.INFORMATION11 = X_INFORMATION11)
764 OR ((recinfo.INFORMATION11 is null) AND (X_INFORMATION11 is null)))
765 AND ((recinfo.INFORMATION12 = X_INFORMATION12)
766 OR ((recinfo.INFORMATION12 is null) AND (X_INFORMATION12 is null)))
767 AND ((recinfo.INFORMATION13 = X_INFORMATION13)
768 OR ((recinfo.INFORMATION13 is null) AND (X_INFORMATION13 is null)))
769 AND ((recinfo.INFORMATION14 = X_INFORMATION14)
770 OR ((recinfo.INFORMATION14 is null) AND (X_INFORMATION14 is null)))
771 AND ((recinfo.INFORMATION15 = X_INFORMATION15)
772 OR ((recinfo.INFORMATION15 is null) AND (X_INFORMATION15 is null)))
773 AND ((recinfo.FORM_ITEM_ID = X_FORM_ITEM_ID)
774 OR ((recinfo.FORM_ITEM_ID is null) AND (X_FORM_ITEM_ID is null)))
775 AND ((recinfo.TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID)
776 OR ((recinfo.TEMPLATE_ITEM_ID is null) AND (X_TEMPLATE_ITEM_ID is null)))
777 AND ((recinfo.TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID)
778 OR ((recinfo.TEMPLATE_ITEM_CONTEXT_ID is null) AND (X_TEMPLATE_ITEM_CONTEXT_ID is null)))
779 AND ((recinfo.ALIGNMENT = X_ALIGNMENT)
780 OR ((recinfo.ALIGNMENT is null) AND (X_ALIGNMENT is null)))
781 AND ((recinfo.BEVEL = X_BEVEL)
782 OR ((recinfo.BEVEL is null) AND (X_BEVEL is null)))
783 AND ((recinfo.CASE_RESTRICTION = X_CASE_RESTRICTION)
784 OR ((recinfo.CASE_RESTRICTION is null) AND (X_CASE_RESTRICTION is null)))
785 AND ((recinfo.ENABLED = X_ENABLED)
786 OR ((recinfo.ENABLED is null) AND (X_ENABLED is null)))
787 AND ((recinfo.FORMAT_MASK = X_FORMAT_MASK)
788 OR ((recinfo.FORMAT_MASK is null) AND (X_FORMAT_MASK is null)))
789 AND ((recinfo.HEIGHT = X_HEIGHT)
790 OR ((recinfo.HEIGHT is null) AND (X_HEIGHT is null)))
791 AND ((recinfo.INFORMATION_FORMULA_ID = X_INFORMATION_FORMULA_ID)
792 OR ((recinfo.INFORMATION_FORMULA_ID is null) AND (X_INFORMATION_FORMULA_ID is null)))
793 AND ((recinfo.INFORMATION_PARAMETER_ITEM_ID1 = X_INFORMATION_PARAM_ITEM_ID1)
794 OR ((recinfo.INFORMATION_PARAMETER_ITEM_ID1 is null) AND (X_INFORMATION_PARAM_ITEM_ID1 is null)))
795 AND ((recinfo.INFORMATION_PARAMETER_ITEM_ID2 = X_INFORMATION_PARAM_ITEM_ID2)
796 OR ((recinfo.INFORMATION_PARAMETER_ITEM_ID2 is null) AND (X_INFORMATION_PARAM_ITEM_ID2 is null)))
797 AND ((recinfo.PROMPT_EDGE_ALIGNMENT = X_PROMPT_EDGE_ALIGNMENT)
798 OR ((recinfo.PROMPT_EDGE_ALIGNMENT is null) AND (X_PROMPT_EDGE_ALIGNMENT is null)))
799 AND ((recinfo.VALIDATION_PARAMETER_ITEM_ID5 = X_VALIDATION_PARAM_ITEM_ID5)
800 OR ((recinfo.VALIDATION_PARAMETER_ITEM_ID5 is null) AND (X_VALIDATION_PARAM_ITEM_ID5 is null)))
801 AND ((recinfo.VALIDATION_FORMULA_ID = X_VALIDATION_FORMULA_ID)
802 OR ((recinfo.VALIDATION_FORMULA_ID is null) AND (X_VALIDATION_FORMULA_ID is null)))
803 AND ((recinfo.INFORMATION16 = X_INFORMATION16)
804 OR ((recinfo.INFORMATION16 is null) AND (X_INFORMATION16 is null)))
805 AND ((recinfo.INFORMATION17 = X_INFORMATION17)
806 OR ((recinfo.INFORMATION17 is null) AND (X_INFORMATION17 is null)))
807 AND ((recinfo.INFORMATION18 = X_INFORMATION18)
808 OR ((recinfo.INFORMATION18 is null) AND (X_INFORMATION18 is null)))
809 AND ((recinfo.INFORMATION19 = X_INFORMATION19)
810 OR ((recinfo.INFORMATION19 is null) AND (X_INFORMATION19 is null)))
811 AND ((recinfo.VALIDATION_PARAMETER_ITEM_ID2 = X_VALIDATION_PARAM_ITEM_ID2)
812 OR ((recinfo.VALIDATION_PARAMETER_ITEM_ID2 is null) AND (X_VALIDATION_PARAM_ITEM_ID2 is null)))
813 AND ((recinfo.VISIBLE = X_VISIBLE)
814 OR ((recinfo.VISIBLE is null) AND (X_VISIBLE is null)))
815 AND ((recinfo.INFORMATION_PARAMETER_ITEM_ID3 = X_INFORMATION_PARAM_ITEM_ID3)
816 OR ((recinfo.INFORMATION_PARAMETER_ITEM_ID3 is null) AND (X_INFORMATION_PARAM_ITEM_ID3 is null)))
817 AND ((recinfo.INFORMATION_PARAMETER_ITEM_ID4 = X_INFORMATION_PARAM_ITEM_ID4)
818 OR ((recinfo.INFORMATION_PARAMETER_ITEM_ID4 is null) AND (X_INFORMATION_PARAM_ITEM_ID4 is null)))
819 AND ((recinfo.INFORMATION_PARAMETER_ITEM_ID5 = X_INFORMATION_PARAM_ITEM_ID5)
820 OR ((recinfo.INFORMATION_PARAMETER_ITEM_ID5 is null) AND (X_INFORMATION_PARAM_ITEM_ID5 is null)))
821 AND ((recinfo.INSERT_ALLOWED = X_INSERT_ALLOWED)
822 OR ((recinfo.INSERT_ALLOWED is null) AND (X_INSERT_ALLOWED is null)))
823 AND ((recinfo.PROMPT_ALIGNMENT_OFFSET = X_PROMPT_ALIGNMENT_OFFSET)
824 OR ((recinfo.PROMPT_ALIGNMENT_OFFSET is null) AND (X_PROMPT_ALIGNMENT_OFFSET is null)))
825 AND ((recinfo.PROMPT_DISPLAY_STYLE = X_PROMPT_DISPLAY_STYLE)
826 OR ((recinfo.PROMPT_DISPLAY_STYLE is null) AND (X_PROMPT_DISPLAY_STYLE is null)))
827 AND ((recinfo.PROMPT_EDGE = X_PROMPT_EDGE)
828 OR ((recinfo.PROMPT_EDGE is null) AND (X_PROMPT_EDGE is null)))
829 ) then
830 null;
831 else
832 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
833 app_exception.raise_exception;
834 end if;
835
836 for tlinfo in c1 loop
837 if (tlinfo.BASELANG = 'Y') then
838 if ( ((tlinfo.INFORMATION_PROMPT = X_INFORMATION_PROMPT)
839 OR ((tlinfo.INFORMATION_PROMPT is null) AND (X_INFORMATION_PROMPT is null)))
840 AND ((tlinfo.DEFAULT_VALUE = X_DEFAULT_VALUE)
841 OR ((tlinfo.DEFAULT_VALUE is null) AND (X_DEFAULT_VALUE is null)))
842 AND ((tlinfo.LABEL = X_LABEL)
843 OR ((tlinfo.LABEL is null) AND (X_LABEL is null)))
844 AND ((tlinfo.PROMPT_TEXT = X_PROMPT_TEXT)
845 OR ((tlinfo.PROMPT_TEXT is null) AND (X_PROMPT_TEXT is null)))
846 AND ((tlinfo.TOOLTIP_TEXT = X_TOOLTIP_TEXT)
847 OR ((tlinfo.TOOLTIP_TEXT is null) AND (X_TOOLTIP_TEXT is null)))
848 ) then
849 null;
850 else
851 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
852 app_exception.raise_exception;
853 end if;
854 end if;
855 end loop;
856 return;
857 end LOCK_ROW;
858
859 procedure UPDATE_ROW (
860 X_ITEM_PROPERTY_ID in NUMBER,
861 X_UPDATE_ALLOWED in NUMBER,
862 X_INFORMATION20 in VARCHAR2,
863 X_INFORMATION21 in VARCHAR2,
864 X_INFORMATION22 in VARCHAR2,
865 X_INFORMATION23 in VARCHAR2,
866 X_INFORMATION24 in VARCHAR2,
867 X_INFORMATION25 in VARCHAR2,
868 X_INFORMATION26 in VARCHAR2,
869 X_INFORMATION27 in VARCHAR2,
870 X_INFORMATION28 in VARCHAR2,
871 X_INFORMATION29 in VARCHAR2,
872 X_INFORMATION30 in VARCHAR2,
873 X_NEXT_NAVIGATION_ITEM_ID in NUMBER,
874 X_PREVIOUS_NAVIGATION_ITEM_ID in NUMBER,
875 X_PROMPT_TEXT_ALIGNMENT in NUMBER,
876 X_QUERY_ALLOWED in NUMBER,
877 X_REQUIRED in NUMBER,
878 X_WIDTH in NUMBER,
879 X_X_POSITION in NUMBER,
880 X_Y_POSITION in NUMBER,
881 X_VALIDATION_PARAM_ITEM_ID3 in NUMBER,
882 X_VALIDATION_PARAM_ITEM_ID4 in NUMBER,
883 X_VALIDATION_PARAM_ITEM_ID1 in NUMBER,
884 X_PROMPT_EDGE_OFFSET in NUMBER,
885 X_INFORMATION_CATEGORY in VARCHAR2,
886 X_INFORMATION1 in VARCHAR2,
887 X_INFORMATION2 in VARCHAR2,
888 X_INFORMATION3 in VARCHAR2,
889 X_INFORMATION4 in VARCHAR2,
890 X_INFORMATION5 in VARCHAR2,
891 X_INFORMATION6 in VARCHAR2,
892 X_INFORMATION7 in VARCHAR2,
893 X_INFORMATION8 in VARCHAR2,
894 X_INFORMATION9 in VARCHAR2,
895 X_INFORMATION10 in VARCHAR2,
896 X_INFORMATION11 in VARCHAR2,
897 X_INFORMATION12 in VARCHAR2,
898 X_INFORMATION13 in VARCHAR2,
899 X_INFORMATION14 in VARCHAR2,
900 X_INFORMATION15 in VARCHAR2,
901 X_FORM_ITEM_ID in NUMBER,
902 X_TEMPLATE_ITEM_ID in NUMBER,
903 X_TEMPLATE_ITEM_CONTEXT_ID in NUMBER,
904 X_ALIGNMENT in NUMBER,
905 X_BEVEL in NUMBER,
906 X_CASE_RESTRICTION in NUMBER,
907 X_DEFAULT_VALUE in VARCHAR2,
908 X_ENABLED in NUMBER,
909 X_FORMAT_MASK in VARCHAR2,
910 X_HEIGHT in NUMBER,
911 X_INFORMATION_FORMULA_ID in NUMBER,
912 X_INFORMATION_PARAM_ITEM_ID1 in NUMBER,
913 X_INFORMATION_PARAM_ITEM_ID2 in NUMBER,
914 X_PROMPT_EDGE_ALIGNMENT in NUMBER,
915 X_VALIDATION_PARAM_ITEM_ID5 in NUMBER,
916 X_VALIDATION_FORMULA_ID in NUMBER,
917 X_INFORMATION16 in VARCHAR2,
918 X_INFORMATION17 in VARCHAR2,
919 X_INFORMATION18 in VARCHAR2,
920 X_INFORMATION19 in VARCHAR2,
921 X_VALIDATION_PARAM_ITEM_ID2 in NUMBER,
922 X_VISIBLE in NUMBER,
923 X_INFORMATION_PARAM_ITEM_ID3 in NUMBER,
924 X_INFORMATION_PARAM_ITEM_ID4 in NUMBER,
925 X_INFORMATION_PARAM_ITEM_ID5 in NUMBER,
926 X_INSERT_ALLOWED in NUMBER,
927 X_PROMPT_ALIGNMENT_OFFSET in NUMBER,
928 X_PROMPT_DISPLAY_STYLE in NUMBER,
929 X_PROMPT_EDGE in NUMBER,
930 X_INFORMATION_PROMPT in VARCHAR2,
931 X_LABEL in VARCHAR2,
932 X_PROMPT_TEXT in VARCHAR2,
933 X_TOOLTIP_TEXT in VARCHAR2,
934 X_LAST_UPDATE_DATE in DATE,
935 X_LAST_UPDATED_BY in NUMBER,
936 X_LAST_UPDATE_LOGIN in NUMBER
937 ) is
938 begin
939 update HR_ITEM_PROPERTIES_B set
940 UPDATE_ALLOWED = X_UPDATE_ALLOWED,
941 INFORMATION20 = X_INFORMATION20,
942 INFORMATION21 = X_INFORMATION21,
943 INFORMATION22 = X_INFORMATION22,
944 INFORMATION23 = X_INFORMATION23,
945 INFORMATION24 = X_INFORMATION24,
946 INFORMATION25 = X_INFORMATION25,
947 INFORMATION26 = X_INFORMATION26,
948 INFORMATION27 = X_INFORMATION27,
949 INFORMATION28 = X_INFORMATION28,
950 INFORMATION29 = X_INFORMATION29,
951 INFORMATION30 = X_INFORMATION30,
952 NEXT_NAVIGATION_ITEM_ID = X_NEXT_NAVIGATION_ITEM_ID,
953 PREVIOUS_NAVIGATION_ITEM_ID = X_PREVIOUS_NAVIGATION_ITEM_ID,
954 PROMPT_TEXT_ALIGNMENT = X_PROMPT_TEXT_ALIGNMENT,
955 QUERY_ALLOWED = X_QUERY_ALLOWED,
956 REQUIRED = X_REQUIRED,
957 WIDTH = X_WIDTH,
958 X_POSITION = X_X_POSITION,
959 Y_POSITION = X_Y_POSITION,
960 VALIDATION_PARAMETER_ITEM_ID3 = X_VALIDATION_PARAM_ITEM_ID3,
961 VALIDATION_PARAMETER_ITEM_ID4 = X_VALIDATION_PARAM_ITEM_ID4,
962 VALIDATION_PARAMETER_ITEM_ID1 = X_VALIDATION_PARAM_ITEM_ID5,
963 PROMPT_EDGE_OFFSET = X_PROMPT_EDGE_OFFSET,
964 INFORMATION_CATEGORY = X_INFORMATION_CATEGORY,
965 INFORMATION1 = X_INFORMATION1,
966 INFORMATION2 = X_INFORMATION2,
967 INFORMATION3 = X_INFORMATION3,
968 INFORMATION4 = X_INFORMATION4,
969 INFORMATION5 = X_INFORMATION5,
970 INFORMATION6 = X_INFORMATION6,
971 INFORMATION7 = X_INFORMATION7,
972 INFORMATION8 = X_INFORMATION8,
973 INFORMATION9 = X_INFORMATION9,
974 INFORMATION10 = X_INFORMATION10,
975 INFORMATION11 = X_INFORMATION11,
976 INFORMATION12 = X_INFORMATION12,
977 INFORMATION13 = X_INFORMATION13,
978 INFORMATION14 = X_INFORMATION14,
979 INFORMATION15 = X_INFORMATION15,
980 FORM_ITEM_ID = X_FORM_ITEM_ID,
981 TEMPLATE_ITEM_ID = X_TEMPLATE_ITEM_ID,
982 TEMPLATE_ITEM_CONTEXT_ID = X_TEMPLATE_ITEM_CONTEXT_ID,
983 ALIGNMENT = X_ALIGNMENT,
984 BEVEL = X_BEVEL,
985 CASE_RESTRICTION = X_CASE_RESTRICTION,
986 ENABLED = X_ENABLED,
987 FORMAT_MASK = X_FORMAT_MASK,
988 HEIGHT = X_HEIGHT,
989 INFORMATION_FORMULA_ID = X_INFORMATION_FORMULA_ID,
990 INFORMATION_PARAMETER_ITEM_ID1 = X_INFORMATION_PARAM_ITEM_ID1,
991 INFORMATION_PARAMETER_ITEM_ID2 = X_INFORMATION_PARAM_ITEM_ID2,
992 PROMPT_EDGE_ALIGNMENT = X_PROMPT_EDGE_ALIGNMENT,
993 VALIDATION_PARAMETER_ITEM_ID5 = X_VALIDATION_PARAM_ITEM_ID5,
994 VALIDATION_FORMULA_ID = X_VALIDATION_FORMULA_ID,
995 INFORMATION16 = X_INFORMATION16,
996 INFORMATION17 = X_INFORMATION17,
997 INFORMATION18 = X_INFORMATION18,
998 INFORMATION19 = X_INFORMATION19,
999 VALIDATION_PARAMETER_ITEM_ID2 = X_VALIDATION_PARAM_ITEM_ID2,
1000 VISIBLE = X_VISIBLE,
1001 INFORMATION_PARAMETER_ITEM_ID3 = X_INFORMATION_PARAM_ITEM_ID3,
1002 INFORMATION_PARAMETER_ITEM_ID4 = X_INFORMATION_PARAM_ITEM_ID4,
1003 INFORMATION_PARAMETER_ITEM_ID5 = X_INFORMATION_PARAM_ITEM_ID5,
1004 INSERT_ALLOWED = X_INSERT_ALLOWED,
1005 PROMPT_ALIGNMENT_OFFSET = X_PROMPT_ALIGNMENT_OFFSET,
1006 PROMPT_DISPLAY_STYLE = X_PROMPT_DISPLAY_STYLE,
1007 PROMPT_EDGE = X_PROMPT_EDGE,
1008 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1009 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1010 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1011 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID;
1012
1013 if (sql%notfound) then
1014 raise no_data_found;
1015 end if;
1016
1017 update HR_ITEM_PROPERTIES_TL set
1018 DEFAULT_VALUE = X_DEFAULT_VALUE,
1019 INFORMATION_PROMPT = X_INFORMATION_PROMPT,
1020 LABEL = X_LABEL,
1021 PROMPT_TEXT = X_PROMPT_TEXT,
1022 TOOLTIP_TEXT = X_TOOLTIP_TEXT,
1023 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1024 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1025 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1026 SOURCE_LANG = userenv('LANG')
1027 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID
1028 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1029
1030 if (sql%notfound) then
1031 raise no_data_found;
1032 end if;
1033 end UPDATE_ROW;
1034
1035 procedure DELETE_ROW (
1036 X_ITEM_PROPERTY_ID in NUMBER
1037 ) is
1038 begin
1039 delete from HR_ITEM_PROPERTIES_TL
1040 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID;
1041
1042 if (sql%notfound) then
1043 raise no_data_found;
1044 end if;
1045
1046 delete from HR_ITEM_PROPERTIES_B
1047 where ITEM_PROPERTY_ID = X_ITEM_PROPERTY_ID;
1048
1049 if (sql%notfound) then
1050 raise no_data_found;
1051 end if;
1052 end DELETE_ROW;
1053
1054 procedure ADD_LANGUAGE
1055 is
1056 begin
1057 delete from HR_ITEM_PROPERTIES_TL T
1058 where not exists
1059 (select NULL
1060 from HR_ITEM_PROPERTIES_B B
1061 where B.ITEM_PROPERTY_ID = T.ITEM_PROPERTY_ID
1062 );
1063
1064 update HR_ITEM_PROPERTIES_TL T set (
1065 DEFAULT_VALUE,
1066 INFORMATION_PROMPT,
1067 LABEL,
1068 PROMPT_TEXT,
1069 TOOLTIP_TEXT
1070 ) = (select
1071 B.DEFAULT_VALUE,
1072 B.INFORMATION_PROMPT,
1073 B.LABEL,
1074 B.PROMPT_TEXT,
1075 B.TOOLTIP_TEXT
1076 from HR_ITEM_PROPERTIES_TL B
1077 where B.ITEM_PROPERTY_ID = T.ITEM_PROPERTY_ID
1078 and B.LANGUAGE = T.SOURCE_LANG)
1079 where (
1080 T.ITEM_PROPERTY_ID,
1081 T.LANGUAGE
1082 ) in (select
1083 SUBT.ITEM_PROPERTY_ID,
1084 SUBT.LANGUAGE
1085 from HR_ITEM_PROPERTIES_TL SUBB, HR_ITEM_PROPERTIES_TL SUBT
1086 where SUBB.ITEM_PROPERTY_ID = SUBT.ITEM_PROPERTY_ID
1087 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
1088 and (SUBB.INFORMATION_PROMPT <> SUBT.INFORMATION_PROMPT
1089 or (SUBB.INFORMATION_PROMPT is null and SUBT.INFORMATION_PROMPT is not null)
1090 or (SUBB.INFORMATION_PROMPT is not null and SUBT.INFORMATION_PROMPT is null)
1091 or SUBB.DEFAULT_VALUE <> SUBT.DEFAULT_VALUE
1092 or (SUBB.DEFAULT_VALUE is null and SUBT.DEFAULT_VALUE is not null)
1093 or (SUBB.DEFAULT_VALUE is not null and SUBT.DEFAULT_VALUE is null)
1094 or SUBB.LABEL <> SUBT.LABEL
1095 or (SUBB.LABEL is null and SUBT.LABEL is not null)
1096 or (SUBB.LABEL is not null and SUBT.LABEL is null)
1097 or SUBB.PROMPT_TEXT <> SUBT.PROMPT_TEXT
1098 or (SUBB.PROMPT_TEXT is null and SUBT.PROMPT_TEXT is not null)
1099 or (SUBB.PROMPT_TEXT is not null and SUBT.PROMPT_TEXT is null)
1100 or SUBB.TOOLTIP_TEXT <> SUBT.TOOLTIP_TEXT
1101 or (SUBB.TOOLTIP_TEXT is null and SUBT.TOOLTIP_TEXT is not null)
1102 or (SUBB.TOOLTIP_TEXT is not null and SUBT.TOOLTIP_TEXT is null)
1103 ));
1104
1105 insert into HR_ITEM_PROPERTIES_TL (
1106 DEFAULT_VALUE,
1107 INFORMATION_PROMPT,
1108 LABEL,
1109 LAST_UPDATED_BY,
1110 LAST_UPDATE_LOGIN,
1111 CREATED_BY,
1112 CREATION_DATE,
1113 ITEM_PROPERTY_ID,
1114 LAST_UPDATE_DATE,
1115 PROMPT_TEXT,
1116 TOOLTIP_TEXT,
1117 LANGUAGE,
1118 SOURCE_LANG
1119 ) select
1120 B.DEFAULT_VALUE,
1121 B.INFORMATION_PROMPT,
1122 B.LABEL,
1123 B.LAST_UPDATED_BY,
1124 B.LAST_UPDATE_LOGIN,
1125 B.CREATED_BY,
1126 B.CREATION_DATE,
1127 B.ITEM_PROPERTY_ID,
1128 B.LAST_UPDATE_DATE,
1129 B.PROMPT_TEXT,
1130 B.TOOLTIP_TEXT,
1131 L.LANGUAGE_CODE,
1132 B.SOURCE_LANG
1133 from HR_ITEM_PROPERTIES_TL B, FND_LANGUAGES L
1134 where L.INSTALLED_FLAG in ('I', 'B')
1135 and B.LANGUAGE = userenv('LANG')
1136 and not exists
1137 (select NULL
1138 from HR_ITEM_PROPERTIES_TL T
1139 where T.ITEM_PROPERTY_ID = B.ITEM_PROPERTY_ID
1140 and T.LANGUAGE = L.LANGUAGE_CODE);
1141 end ADD_LANGUAGE;
1142
1143 procedure LOAD_ROW (
1144 X_APPLICATION_SHORT_NAME IN VARCHAR2,
1145 X_FORM_NAME IN VARCHAR2,
1146 X_FULL_ITEM_NAME IN VARCHAR2,
1147 X_RADIO_BUTTON_NAME IN VARCHAR2,
1148 X_TEMPLATE_NAME IN VARCHAR2,
1149 X_TERRITORY_SHORT_NAME IN VARCHAR2,
1150 X_SEGMENT1 IN VARCHAR2,
1151 X_SEGMENT2 IN VARCHAR2,
1152 X_SEGMENT3 IN VARCHAR2,
1153 X_SEGMENT4 IN VARCHAR2,
1154 X_SEGMENT5 IN VARCHAR2,
1155 X_SEGMENT6 IN VARCHAR2,
1156 X_SEGMENT7 IN VARCHAR2,
1157 X_SEGMENT8 IN VARCHAR2,
1158 X_SEGMENT9 IN VARCHAR2,
1159 X_SEGMENT10 IN VARCHAR2,
1160 X_SEGMENT11 IN VARCHAR2,
1161 X_SEGMENT12 IN VARCHAR2,
1162 X_SEGMENT13 IN VARCHAR2,
1163 X_SEGMENT14 IN VARCHAR2,
1164 X_SEGMENT15 IN VARCHAR2,
1165 X_SEGMENT16 IN VARCHAR2,
1166 X_SEGMENT17 IN VARCHAR2,
1167 X_SEGMENT18 IN VARCHAR2,
1168 X_SEGMENT19 IN VARCHAR2,
1169 X_SEGMENT20 IN VARCHAR2,
1170 X_SEGMENT21 IN VARCHAR2,
1171 X_SEGMENT22 IN VARCHAR2,
1172 X_SEGMENT23 IN VARCHAR2,
1173 X_SEGMENT24 IN VARCHAR2,
1174 X_SEGMENT25 IN VARCHAR2,
1175 X_SEGMENT26 IN VARCHAR2, X_SEGMENT27 IN VARCHAR2,
1176 X_SEGMENT28 IN VARCHAR2,
1177 X_SEGMENT29 IN VARCHAR2,
1178 X_SEGMENT30 IN VARCHAR2,
1179 X_ID_FLEX_STRUCTURE_CODE IN VARCHAR2,
1180 X_ID_FLEX_CODE IN VARCHAR2,
1181 X_OWNER IN VARCHAR2,
1182 X_ALIGNMENT IN VARCHAR2,
1183 X_BEVEL IN VARCHAR2,
1184 X_CASE_RESTRICTION IN VARCHAR2,
1185 X_DEFAULT_VALUE IN VARCHAR2,
1186 X_ENABLED IN VARCHAR2,
1187 X_FORMAT_MASK IN VARCHAR2,
1188 X_HEIGHT IN VARCHAR2,
1189 X_INFO_FORMULA_NAME IN VARCHAR2,
1190 X_INFO_FORMULA_TYPE IN VARCHAR2,
1191 X_INFO_BUSINESS_GROUP IN VARCHAR2,
1192 X_INFO_TERRITORIES IN VARCHAR2,
1193 X_FULL_ITEM_NAME_IP1 IN VARCHAR2,
1194 X_FULL_ITEM_NAME_IP2 IN VARCHAR2,
1195 X_FULL_ITEM_NAME_IP3 IN VARCHAR2,
1196 X_FULL_ITEM_NAME_IP4 IN VARCHAR2,
1197 X_FULL_ITEM_NAME_IP5 IN VARCHAR2,
1198 X_INSERT_ALLOWED IN VARCHAR2,
1199 X_PROMPT_ALIGNMENT_OFFSET IN VARCHAR2,
1200 X_PROMPT_DISPLAY_STYLE IN VARCHAR2,
1201 X_PROMPT_EDGE IN VARCHAR2,
1202 X_PROMPT_EDGE_ALIGNMENT IN VARCHAR2,
1203 X_PROMPT_EDGE_OFFSET IN VARCHAR2,
1204 X_PROMPT_TEXT_ALIGNMENT IN VARCHAR2,
1205 X_QUERY_ALLOWED IN VARCHAR2,
1206 X_REQUIRED IN VARCHAR2,
1207 X_UPDATE_ALLOWED IN VARCHAR2,
1208 X_VAL_FORMULA_NAME IN VARCHAR2,
1209 X_VAL_FORMULA_TYPE IN VARCHAR2,
1210 X_VAL_BUSINESS_GROUP IN VARCHAR2,
1211 X_VAL_TERRITORIES IN VARCHAR2,
1212 X_FULL_ITEM_NAME_VP1 IN VARCHAR2,
1213 X_FULL_ITEM_NAME_VP2 IN VARCHAR2,
1214 X_FULL_ITEM_NAME_VP3 IN VARCHAR2,
1215 X_FULL_ITEM_NAME_VP4 IN VARCHAR2,
1216 X_FULL_ITEM_NAME_VP5 IN VARCHAR2,
1217 X_VISIBLE IN VARCHAR2,
1218 X_WIDTH IN VARCHAR2,
1219 X_X_POSITION IN VARCHAR2,
1220 X_Y_POSITION IN VARCHAR2,
1221 X_INFORMATION_CATEGORY IN VARCHAR2,
1222 X_INFORMATION1 IN VARCHAR2,
1223 X_INFORMATION2 IN VARCHAR2,
1224 X_INFORMATION3 IN VARCHAR2,
1225 X_INFORMATION4 IN VARCHAR2,
1226 X_INFORMATION5 IN VARCHAR2,
1227 X_INFORMATION6 IN VARCHAR2,
1228 X_INFORMATION7 IN VARCHAR2,
1229 X_INFORMATION8 IN VARCHAR2,
1230 X_INFORMATION9 IN VARCHAR2,
1231 X_INFORMATION10 IN VARCHAR2,
1232 X_INFORMATION11 IN VARCHAR2,
1233 X_INFORMATION12 IN VARCHAR2,
1234 X_INFORMATION13 IN VARCHAR2,
1235 X_INFORMATION14 IN VARCHAR2,
1236 X_INFORMATION15 IN VARCHAR2,
1237 X_INFORMATION16 IN VARCHAR2,
1238 X_INFORMATION17 IN VARCHAR2,
1239 X_INFORMATION18 IN VARCHAR2,
1240 X_INFORMATION19 IN VARCHAR2,
1241 X_INFORMATION20 IN VARCHAR2,
1242 X_INFORMATION21 IN VARCHAR2,
1243 X_INFORMATION22 IN VARCHAR2,
1244 X_INFORMATION23 IN VARCHAR2,
1245 X_INFORMATION24 IN VARCHAR2,
1246 X_INFORMATION25 IN VARCHAR2,
1247 X_INFORMATION26 IN VARCHAR2,
1248 X_INFORMATION27 IN VARCHAR2,
1249 X_INFORMATION28 IN VARCHAR2,
1250 X_INFORMATION29 IN VARCHAR2,
1251 X_INFORMATION30 IN VARCHAR2,
1252 X_PREV_ITEM_NAME IN VARCHAR2,
1253 X_NEXT_ITEM_NAME IN VARCHAR2,
1254 X_INFORMATION_PROMPT IN VARCHAR2,
1255 X_LABEL IN VARCHAR2,
1256 X_PROMPT_TEXT IN VARCHAR2,
1257 X_TOOLTIP_TEXT IN VARCHAR2,
1258 X_LAST_UPDATE_DATE in VARCHAR2 default sysdate,
1259 X_CUSTOM_MODE IN VARCHAR2 default null
1260 ) is
1261 X_ROWID ROWID;
1262 X_CREATION_DATE DATE;
1263 X_CREATED_BY NUMBER;
1264 -- X_LAST_UPDATE_DATE DATE;
1265 X_LAST_UPDATED_BY NUMBER;
1266 X_LAST_UPDATE_LOGIN NUMBER;
1267 X_FORM_ID NUMBER;
1268 X_APPLICATION_ID NUMBER;
1269 X_FORM_ITEM_ID NUMBER;
1270 X_TEMPLATE_ITEM_ID NUMBER;
1271 X_TEMPLATE_ITEM_CONTEXT_ID NUMBER;
1272 X_ITEM_PROPERTY_ID NUMBER;
1273 X_PREVIOUS_NAVIGATION_ITEM_ID NUMBER;
1274 X_NEXT_NAVIGATION_ITEM_ID NUMBER;
1275 X_BUSINESS_GROUP_ID NUMBER;
1276 X_LEGISLATION_CODE VARCHAR2(30);
1277 X_FORMULA_TYPE_ID NUMBER;
1278 X_INFORMATION_FORMULA_ID NUMBER;
1279 X_VALIDATION_FORMULA_ID NUMBER;
1280 X_INFORMATION_PARAM_ITEM_ID1 NUMBER;
1281 X_INFORMATION_PARAM_ITEM_ID2 NUMBER;
1282 X_INFORMATION_PARAM_ITEM_ID3 NUMBER;
1283 X_INFORMATION_PARAM_ITEM_ID4 NUMBER;
1284 X_INFORMATION_PARAM_ITEM_ID5 NUMBER;
1285 X_VALIDATION_PARAM_ITEM_ID1 NUMBER;
1286 X_VALIDATION_PARAM_ITEM_ID2 NUMBER;
1287 X_VALIDATION_PARAM_ITEM_ID3 NUMBER;
1288 X_VALIDATION_PARAM_ITEM_ID4 NUMBER;
1289 X_VALIDATION_PARAM_ITEM_ID5 NUMBER;
1290 f_luby number; -- entity owner in file
1291 f_ludate date; -- entity update date in file
1292 db_luby number; -- entity owner in db
1293 db_ludate date; -- entity update date in db
1294 begin
1295 --This has been commented as LAST_UPDATE_DATE is passed as an parameter
1296 /* OWNER_TO_WHO (
1297 X_OWNER,
1298 X_CREATION_DATE,
1299 X_CREATED_BY,
1300 X_LAST_UPDATE_DATE,
1301 X_LAST_UPDATED_BY,
1302 X_LAST_UPDATE_LOGIN
1303 );
1304 */
1305
1306 if X_OWNER = 'SEED' then
1307 X_CREATED_BY := 1;
1308 else
1309 X_CREATED_BY := 0;
1310 end if;
1311
1312 select application_id
1313 into x_application_id
1314 from fnd_application
1315 where application_short_name = x_application_short_name;
1316
1317 if ltrim(rtrim(x_form_name)) is not null then
1318 select form_id
1319 into x_form_id
1320 from fnd_form
1321 where form_name = x_form_name
1322 and application_id = x_application_id;
1323 end if;
1324
1325 if ltrim(rtrim(x_prev_item_name)) is not null then
1326
1327 BEGIN
1328 select form_item_id
1329 into x_previous_navigation_item_id
1330 from hr_form_items_b
1331 where full_item_name = x_prev_item_name
1332 and form_id = x_form_id
1333 and application_id = x_application_id;
1334 EXCEPTION --2608813 added exception so if item is radio button we do not error
1335 WHEN TOO_MANY_ROWS THEN
1336 select form_item_id
1337 into x_previous_navigation_item_id
1338 from hr_form_items_b
1339 where full_item_name = x_prev_item_name
1340 and form_id = x_form_id
1341 and application_id = x_application_id
1342 and radio_button_name is not null
1343 and rownum = 1;
1344 END;
1345
1346 end if;
1347
1348 if ltrim(rtrim(x_next_item_name)) is not null then
1349
1350 BEGIN
1351 select form_item_id
1352 into x_next_navigation_item_id
1353 from hr_form_items_b
1354 where full_item_name = x_next_item_name
1355 and form_id = x_form_id
1356 and application_id = x_application_id;
1357 EXCEPTION --2608813 added exception so if item is radio button we do not error
1358 WHEN TOO_MANY_ROWS THEN
1359 select form_item_id
1360 into x_next_navigation_item_id
1361 from hr_form_items_b
1362 where full_item_name = x_next_item_name
1363 and form_id = x_form_id
1364 and application_id = x_application_id
1365 and radio_button_name is not null
1366 and rownum = 1;
1367 END;
1368 end if;
1369
1370 if ltrim(rtrim(x_info_formula_name)) is not null then
1371
1372 if ltrim(rtrim(x_info_business_group)) is not null then
1373
1374 select business_group_id
1375 into x_business_group_id
1376 from per_business_groups
1377 where name = x_info_business_group;
1378
1379 end if;
1380
1381 if rtrim(ltrim(x_info_territories)) is not null then
1382
1383 select territory_code
1384 into x_legislation_code
1385 from fnd_territories_vl
1386 where territory_short_name = x_info_territories;
1387
1388 end if;
1389
1390 if ltrim(rtrim(x_info_formula_type)) is not null then
1391
1392 select formula_type_id
1393 into x_formula_type_id
1394 from ff_formula_types
1395 where formula_type_name = x_info_formula_type;
1396
1397 end if;
1398
1399 select formula_id
1400 into x_information_formula_id
1401 from ff_formulas_f
1402 where nvl(business_group_id,hr_api.g_number) = nvl(x_business_group_id,hr_api.g_number)
1403 and nvl(legislation_code,hr_api.g_varchar2) = nvl(x_legislation_code,hr_api.g_varchar2)
1404 and formula_type_id = x_formula_type_id
1405 and formula_name = x_info_formula_name;
1406
1407 if ltrim(rtrim(x_full_item_name_ip1)) is not null then
1408
1409 BEGIN
1410 select form_item_id
1411 into x_information_param_item_id1
1412 from hr_form_items_b
1413 where full_item_name = x_full_item_name_ip1
1414 and form_id = x_form_id
1415 and application_id = x_application_id;
1416 EXCEPTION --2608813 added exception so if item is radio button we do not error
1417 WHEN TOO_MANY_ROWS THEN
1418 select form_item_id
1419 into x_information_param_item_id1
1420 from hr_form_items_b
1421 where full_item_name = x_full_item_name_ip1
1422 and form_id = x_form_id
1423 and application_id = x_application_id
1424 and radio_button_name is not null
1425 and rownum = 1;
1426 END;
1427
1428 end if;
1429
1430 if ltrim(rtrim(x_full_item_name_ip2)) is not null then
1431
1432 BEGIN
1433 select form_item_id
1434 into x_information_param_item_id2
1435 from hr_form_items_b
1436 where full_item_name = x_full_item_name_ip2
1437 and form_id = x_form_id
1438 and application_id = x_application_id;
1439 EXCEPTION --2608813 added exception so if item is radio button we do not error
1440 WHEN TOO_MANY_ROWS THEN
1441 select form_item_id
1442 into x_information_param_item_id2
1443 from hr_form_items_b
1444 where full_item_name = x_full_item_name_ip2
1445 and form_id = x_form_id
1446 and application_id = x_application_id
1447 and radio_button_name is not null
1448 and rownum = 1;
1449 END;
1450
1451 end if;
1452
1453 if ltrim(rtrim(x_full_item_name_ip3)) is not null then
1454
1455 BEGIN
1456 select form_item_id
1457 into x_information_param_item_id3
1458 from hr_form_items_b
1459 where full_item_name = x_full_item_name_ip3
1460 and form_id = x_form_id
1461 and application_id = x_application_id;
1462 EXCEPTION --2608813 added exception so if item is radio button we do not error
1463 WHEN TOO_MANY_ROWS THEN
1464 select form_item_id
1465 into x_information_param_item_id3
1466 from hr_form_items_b
1467 where full_item_name = x_full_item_name_ip3
1468 and form_id = x_form_id
1469 and application_id = x_application_id
1470 and radio_button_name is not null
1471 and rownum = 1;
1472 END;
1473
1474 end if;
1475
1476 if ltrim(rtrim(x_full_item_name_ip4)) is not null then
1477
1478 BEGIN
1479 select form_item_id
1480 into x_information_param_item_id4
1481 from hr_form_items_b
1482 where full_item_name = x_full_item_name_ip4
1483 and form_id = x_form_id
1484 and application_id = x_application_id;
1485 EXCEPTION --2608813 added exception so if item is radio button we do not error
1486 WHEN TOO_MANY_ROWS THEN
1487 select form_item_id
1488 into x_information_param_item_id4
1489 from hr_form_items_b
1490 where full_item_name = x_full_item_name_ip4
1491 and form_id = x_form_id
1492 and application_id = x_application_id
1493 and radio_button_name is not null
1494 and rownum = 1;
1495 END;
1496
1497 end if;
1498
1499 if ltrim(rtrim(x_full_item_name_ip5)) is not null then
1500
1501 BEGIN
1502 select form_item_id
1503 into x_information_param_item_id5
1504 from hr_form_items_b
1505 where full_item_name = x_full_item_name_ip5
1506 and form_id = x_form_id
1507 and application_id = x_application_id;
1508 EXCEPTION --2608813 added exception so if item is radio button we do not error
1509 WHEN TOO_MANY_ROWS THEN
1510 select form_item_id
1511 into x_information_param_item_id5
1512 from hr_form_items_b
1513 where full_item_name = x_full_item_name_ip5
1514 and form_id = x_form_id
1515 and application_id = x_application_id
1516 and radio_button_name is not null
1517 and rownum = 1;
1518 END;
1519
1520 end if;
1521
1522 end if;
1523
1524 if ltrim(rtrim(x_val_formula_name)) is not null then
1525
1526 if ltrim(rtrim(x_val_business_group)) is not null then
1527
1528 select business_group_id
1529 into x_business_group_id
1530 from per_business_groups
1531 where name = x_val_business_group;
1532
1533 else
1534
1535 x_business_group_id := null;
1536
1537 end if;
1538
1539 if ltrim(rtrim(x_val_territories)) is not null then
1540
1541 select territory_code
1542 into x_legislation_code
1543 from fnd_territories_vl
1544 where territory_short_name = x_val_territories;
1545
1546 else
1547
1548 x_legislation_code := null;
1549
1550 end if;
1551
1552 if ltrim(rtrim(x_val_formula_type)) is not null then
1553
1554 select formula_type_id
1555 into x_formula_type_id
1556 from ff_formula_types
1557 where formula_type_name = x_val_formula_type;
1558
1559 else
1560
1561 x_formula_type_id := null;
1562
1563 end if;
1564
1565 select formula_id
1566 into x_validation_formula_id
1567 from ff_formulas_f
1568 where nvl(business_group_id,hr_api.g_number) = nvl(x_business_group_id,hr_api.g_number)
1569 and nvl(legislation_code,hr_api.g_varchar2) = nvl(x_legislation_code,hr_api.g_varchar2)
1570 and formula_type_id = x_formula_type_id
1571 and formula_name = x_val_formula_name;
1572
1573 if ltrim(rtrim(x_full_item_name_vp1)) is not null then
1574
1575 BEGIN
1576 select form_item_id
1577 into x_validation_param_item_id1
1578 from hr_form_items_b
1579 where full_item_name = x_full_item_name_vp1
1580 and form_id = x_form_id
1581 and application_id = x_application_id;
1582 EXCEPTION
1583 WHEN TOO_MANY_ROWS THEN
1584 select form_item_id
1585 into x_validation_param_item_id1
1586 from hr_form_items_b
1587 where full_item_name = x_full_item_name_vp1
1588 and form_id = x_form_id
1589 and application_id = x_application_id
1590 and radio_button_name is not null
1591 and rownum = 1;
1592 END;
1593
1594 end if;
1595
1596 if ltrim(rtrim(x_full_item_name_vp2)) is not null then
1597
1598 BEGIN
1599 select form_item_id
1600 into x_validation_param_item_id2
1601 from hr_form_items_b
1602 where full_item_name = x_full_item_name_vp2
1603 and form_id = x_form_id
1604 and application_id = x_application_id;
1605 EXCEPTION
1606 WHEN TOO_MANY_ROWS THEN
1607 select form_item_id
1608 into x_validation_param_item_id2
1609 from hr_form_items_b
1610 where full_item_name = x_full_item_name_vp2
1611 and form_id = x_form_id
1612 and application_id = x_application_id
1613 and radio_button_name is not null
1614 and rownum = 1;
1615 END;
1616
1617 end if;
1618
1619 if ltrim(rtrim(x_full_item_name_vp3)) is not null then
1620
1621 BEGIN
1622 select form_item_id
1623 into x_validation_param_item_id3
1624 from hr_form_items_b
1625 where full_item_name = x_full_item_name_vp3
1626 and form_id = x_form_id
1627 and application_id = x_application_id;
1628 EXCEPTION
1629 WHEN TOO_MANY_ROWS THEN
1630 select form_item_id
1631 into x_validation_param_item_id3
1632 from hr_form_items_b
1633 where full_item_name = x_full_item_name_vp3
1634 and form_id = x_form_id
1635 and application_id = x_application_id
1636 and radio_button_name is not null
1637 and rownum = 1;
1638 END;
1639
1640 end if;
1641
1642 if ltrim(rtrim(x_full_item_name_vp4)) is not null then
1643
1644 BEGIN
1645 select form_item_id
1646 into x_validation_param_item_id4
1647 from hr_form_items_b
1648 where full_item_name = x_full_item_name_vp4
1649 and form_id = x_form_id
1650 and application_id = x_application_id;
1651 EXCEPTION
1652 WHEN TOO_MANY_ROWS THEN
1653 select form_item_id
1654 into x_validation_param_item_id4
1655 from hr_form_items_b
1656 where full_item_name = x_full_item_name_vp4
1657 and form_id = x_form_id
1658 and application_id = x_application_id
1659 and radio_button_name is not null
1660 and rownum = 1;
1661 END;
1662
1663 end if;
1664
1665 if ltrim(rtrim(x_full_item_name_vp5)) is not null then
1666
1667 BEGIN
1668 select form_item_id
1669 into x_validation_param_item_id5
1670 from hr_form_items_b
1671 where full_item_name = x_full_item_name_vp5
1672 and form_id = x_form_id
1673 and application_id = x_application_id;
1674 EXCEPTION
1675 WHEN TOO_MANY_ROWS THEN
1676 select form_item_id
1677 into x_validation_param_item_id5
1678 from hr_form_items_b
1679 where full_item_name = x_full_item_name_vp5
1680 and form_id = x_form_id
1681 and application_id = x_application_id
1682 and radio_button_name is not null
1683 and rownum = 1;
1684 END;
1685
1686 end if;
1687
1688 end if;
1689
1690 select form_item_id
1691 into x_form_item_id
1692 from hr_form_items_b
1693 where full_item_name = x_full_item_name
1694 and application_id = x_application_id
1695 and form_id = x_form_id
1696 and ( radio_button_name = x_radio_button_name
1697 or (radio_button_name is null and x_radio_button_name is null) );
1698
1699 if ltrim(rtrim(x_template_name)) is not null then
1700
1701 select hti.template_item_id
1702 into x_template_item_id
1703 from hr_form_templates_b hft
1704 ,hr_template_items_b hti
1705 where hti.form_item_id = x_form_item_id
1706 and hti.form_template_id = hft.form_template_id
1707 and hft.form_id = x_form_id
1708 and hft.application_id = x_application_id
1709 and hft.template_name = x_template_name
1710 and ( (hft.legislation_code is null and x_territory_short_name is null)
1711 or (hft.legislation_code = x_territory_short_name) );
1712
1713 end if;
1714
1715 if ltrim(rtrim(x_id_flex_structure_code)) is not null then
1716
1717 select template_item_context_id
1718 into x_template_item_context_id
1719 from hr_template_item_contexts_b ticb
1720 ,hr_item_contexts hic
1721 ,fnd_id_flex_structures fifs
1722 where ticb.template_item_id = x_template_item_id
1723 and ticb.item_context_id = hic.item_context_id
1724 and nvl(hic.segment1,hr_api.g_varchar2) = nvl(x_segment1,hr_api.g_varchar2)
1725 and nvl(hic.segment2,hr_api.g_varchar2) = nvl(x_segment2,hr_api.g_varchar2)
1726 and nvl(hic.segment3,hr_api.g_varchar2) = nvl(x_segment3,hr_api.g_varchar2)
1727 and nvl(hic.segment4,hr_api.g_varchar2) = nvl(x_segment4,hr_api.g_varchar2)
1728 and nvl(hic.segment5,hr_api.g_varchar2) = nvl(x_segment5,hr_api.g_varchar2)
1729 and nvl(hic.segment6,hr_api.g_varchar2) = nvl(x_segment6,hr_api.g_varchar2)
1730 and nvl(hic.segment7,hr_api.g_varchar2) = nvl(x_segment7,hr_api.g_varchar2)
1731 and nvl(hic.segment8,hr_api.g_varchar2) = nvl(x_segment8,hr_api.g_varchar2)
1732 and nvl(hic.segment9,hr_api.g_varchar2) = nvl(x_segment9,hr_api.g_varchar2)
1733 and nvl(hic.segment10,hr_api.g_varchar2) = nvl(x_segment10,hr_api.g_varchar2)
1734 and nvl(hic.segment11,hr_api.g_varchar2) = nvl(x_segment11,hr_api.g_varchar2)
1735 and nvl(hic.segment12,hr_api.g_varchar2) = nvl(x_segment12,hr_api.g_varchar2)
1736 and nvl(hic.segment13,hr_api.g_varchar2) = nvl(x_segment13,hr_api.g_varchar2)
1737 and nvl(hic.segment14,hr_api.g_varchar2) = nvl(x_segment14,hr_api.g_varchar2)
1738 and nvl(hic.segment15,hr_api.g_varchar2) = nvl(x_segment15,hr_api.g_varchar2)
1739 and nvl(hic.segment16,hr_api.g_varchar2) = nvl(x_segment16,hr_api.g_varchar2)
1740 and nvl(hic.segment17,hr_api.g_varchar2) = nvl(x_segment17,hr_api.g_varchar2)
1741 and nvl(hic.segment18,hr_api.g_varchar2) = nvl(x_segment18,hr_api.g_varchar2)
1742 and nvl(hic.segment19,hr_api.g_varchar2) = nvl(x_segment19,hr_api.g_varchar2)
1743 and nvl(hic.segment20,hr_api.g_varchar2) = nvl(x_segment20,hr_api.g_varchar2)
1744 and nvl(hic.segment21,hr_api.g_varchar2) = nvl(x_segment21,hr_api.g_varchar2)
1745 and nvl(hic.segment22,hr_api.g_varchar2) = nvl(x_segment22,hr_api.g_varchar2)
1746 and nvl(hic.segment23,hr_api.g_varchar2) = nvl(x_segment23,hr_api.g_varchar2)
1747 and nvl(hic.segment24,hr_api.g_varchar2) = nvl(x_segment24,hr_api.g_varchar2)
1748 and nvl(hic.segment25,hr_api.g_varchar2) = nvl(x_segment25,hr_api.g_varchar2)
1749 and nvl(hic.segment26,hr_api.g_varchar2) = nvl(x_segment26,hr_api.g_varchar2)
1750 and nvl(hic.segment27,hr_api.g_varchar2) = nvl(x_segment27,hr_api.g_varchar2)
1751 and nvl(hic.segment28,hr_api.g_varchar2) = nvl(x_segment28,hr_api.g_varchar2)
1752 and nvl(hic.segment29,hr_api.g_varchar2) = nvl(x_segment29,hr_api.g_varchar2)
1753 and nvl(hic.segment30,hr_api.g_varchar2) = nvl(x_segment30,hr_api.g_varchar2)
1754 and hic.id_flex_num = fifs.id_flex_num
1755 and fifs.application_id = x_application_id
1756 and fifs.id_flex_structure_code = x_id_flex_structure_code
1757 and fifs.id_flex_code = x_id_flex_code;
1758
1759 end if;
1760
1761 begin
1762 if ltrim(rtrim(x_id_flex_structure_code)) is not null then
1763 x_form_item_id := null;
1764 x_template_item_id := null;
1765 select item_property_id
1766 into x_item_property_id
1767 from hr_item_properties_b
1768 where template_item_context_id=x_template_item_context_id;
1769 else
1770 x_template_item_context_id := null;
1771 if x_template_name is null then
1772 x_template_item_id := null;
1773 select item_property_id
1774 into x_item_property_id
1775 from hr_item_properties_b
1776 where form_item_id=x_form_item_id;
1777 else
1778 x_form_item_id := null;
1779 select item_property_id
1780 into x_item_property_id
1781 from hr_item_properties_b
1782 where template_item_id=x_template_item_id;
1783 end if;
1784 end if;
1785
1786 /* adhunter mar-2002, bug 2183600
1787 removed for performance reasons, replaced by separate sql stmts immediately above
1788
1789 begin
1790 select item_property_id
1791 into x_item_property_id
1792 from hr_item_properties_b
1793 where nvl(form_item_id,hr_api.g_number) = nvl(x_form_item_id,hr_api.g_number)
1794 and nvl(template_item_id,hr_api.g_number) = nvl(x_template_item_id,hr_api.g_number)
1795 and nvl(template_item_context_id,hr_api.g_number) = nvl(x_template_item_context_id,hr_api.g_number);
1796 */
1797
1798 exception
1799 when no_data_found then
1800 select hr_item_properties_b_s.nextval
1801 into x_item_property_id
1802 from dual;
1803 end;
1804
1805 begin
1806 -- Translate owner to file_last_updated_by
1807 f_luby := fnd_load_util.owner_id(X_OWNER);
1808 -- Translate char last_update_date to date
1809 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
1810
1811 select LAST_UPDATED_BY, LAST_UPDATE_DATE
1812 into db_luby, db_ludate
1813 from HR_ITEM_PROPERTIES_TL
1814 where item_property_id = x_item_property_id
1815 and LANGUAGE=userenv('LANG');
1816
1817 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
1818 db_ludate,X_CUSTOM_MODE)) then
1819 UPDATE_COPIES (
1820 X_ITEM_PROPERTY_ID,
1821 X_FORM_ITEM_ID,
1822 X_TEMPLATE_ITEM_ID,
1823 X_TEMPLATE_ITEM_CONTEXT_ID,
1824 X_ALIGNMENT,
1825 X_BEVEL,
1826 X_CASE_RESTRICTION,
1827 X_ENABLED,
1828 X_FORMAT_MASK,
1829 X_HEIGHT,
1830 X_INFORMATION_FORMULA_ID,
1831 X_INFORMATION_PARAM_ITEM_ID1,
1832 X_INFORMATION_PARAM_ITEM_ID2,
1833 X_INFORMATION_PARAM_ITEM_ID3,
1834 X_INFORMATION_PARAM_ITEM_ID4,
1835 X_INFORMATION_PARAM_ITEM_ID5,
1836 X_INSERT_ALLOWED,
1837 X_NEXT_NAVIGATION_ITEM_ID,
1838 X_PREVIOUS_NAVIGATION_ITEM_ID,
1839 X_PROMPT_ALIGNMENT_OFFSET,
1840 X_PROMPT_DISPLAY_STYLE,
1841 X_PROMPT_EDGE,
1842 X_PROMPT_EDGE_OFFSET,
1843 X_PROMPT_EDGE_ALIGNMENT,
1844 X_PROMPT_TEXT_ALIGNMENT,
1845 X_QUERY_ALLOWED,
1846 X_REQUIRED,
1847 X_UPDATE_ALLOWED,
1848 X_VALIDATION_FORMULA_ID,
1849 X_VALIDATION_PARAM_ITEM_ID1,
1850 X_VALIDATION_PARAM_ITEM_ID2,
1851 X_VALIDATION_PARAM_ITEM_ID3,
1852 X_VALIDATION_PARAM_ITEM_ID4,
1853 X_VALIDATION_PARAM_ITEM_ID5,
1854 X_VISIBLE,
1855 X_WIDTH,
1856 X_X_POSITION,
1857 X_Y_POSITION,
1858 X_DEFAULT_VALUE ,
1859 X_INFORMATION_PROMPT,
1860 X_LABEL,
1861 X_PROMPT_TEXT,
1862 X_TOOLTIP_TEXT,
1863 X_INFORMATION_CATEGORY,
1864 X_INFORMATION1,
1865 X_INFORMATION2,
1866 X_INFORMATION3,
1867 X_INFORMATION4,
1868 X_INFORMATION5,
1869 X_INFORMATION6,
1870 X_INFORMATION7,
1871 X_INFORMATION8,
1872 X_INFORMATION9,
1873 X_INFORMATION10,
1874 X_INFORMATION11,
1875 X_INFORMATION12,
1876 X_INFORMATION13,
1877 X_INFORMATION14,
1878 X_INFORMATION15,
1879 X_INFORMATION16,
1880 X_INFORMATION17,
1881 X_INFORMATION18,
1882 X_INFORMATION19,
1883 X_INFORMATION20,
1884 X_INFORMATION21,
1885 X_INFORMATION22,
1886 X_INFORMATION23,
1887 X_INFORMATION24,
1888 X_INFORMATION25,
1889 X_INFORMATION26,
1890 X_INFORMATION27,
1891 X_INFORMATION28,
1892 X_INFORMATION29,
1893 X_INFORMATION30,
1894 f_ludate,
1895 f_luby,
1896 0
1897 );
1898
1899 UPDATE_ROW (
1900 X_ITEM_PROPERTY_ID,
1901 to_number(X_UPDATE_ALLOWED),
1902 X_INFORMATION20,
1903 X_INFORMATION21,
1904 X_INFORMATION22,
1905 X_INFORMATION23,
1906 X_INFORMATION24,
1907 X_INFORMATION25,
1908 X_INFORMATION26,
1909 X_INFORMATION27,
1910 X_INFORMATION28,
1911 X_INFORMATION29,
1912 X_INFORMATION30,
1913 X_NEXT_NAVIGATION_ITEM_ID,
1914 X_PREVIOUS_NAVIGATION_ITEM_ID,
1915 to_number(X_PROMPT_TEXT_ALIGNMENT),
1916 to_number(X_QUERY_ALLOWED),
1917 to_number(X_REQUIRED),
1918 to_number(X_WIDTH),
1919 to_number(X_X_POSITION),
1920 to_number(X_Y_POSITION),
1921 X_VALIDATION_PARAM_ITEM_ID3,
1922 X_VALIDATION_PARAM_ITEM_ID4,
1923 X_VALIDATION_PARAM_ITEM_ID1,
1924 to_number(X_PROMPT_EDGE_OFFSET),
1925 X_INFORMATION_CATEGORY,
1926 X_INFORMATION1,
1927 X_INFORMATION2,
1928 X_INFORMATION3,
1929 X_INFORMATION4,
1930 X_INFORMATION5,
1931 X_INFORMATION6,
1932 X_INFORMATION7,
1933 X_INFORMATION8,
1934 X_INFORMATION9,
1935 X_INFORMATION10,
1936 X_INFORMATION11,
1937 X_INFORMATION12,
1938 X_INFORMATION13,
1939 X_INFORMATION14,
1940 X_INFORMATION15,
1941 X_FORM_ITEM_ID,
1942 X_TEMPLATE_ITEM_ID,
1943 X_TEMPLATE_ITEM_CONTEXT_ID,
1944 to_number(X_ALIGNMENT),
1945 to_number(X_BEVEL),
1946 to_number(X_CASE_RESTRICTION),
1947 X_DEFAULT_VALUE,
1948 to_number(X_ENABLED),
1949 X_FORMAT_MASK,
1950 to_number(X_HEIGHT),
1951 X_INFORMATION_FORMULA_ID,
1952 X_INFORMATION_PARAM_ITEM_ID1,
1953 X_INFORMATION_PARAM_ITEM_ID2,
1954 to_number(X_PROMPT_EDGE_ALIGNMENT),
1955 X_VALIDATION_PARAM_ITEM_ID5,
1956 X_VALIDATION_FORMULA_ID,
1957 X_INFORMATION16,
1958 X_INFORMATION17,
1959 X_INFORMATION18,
1960 X_INFORMATION19,
1961 X_VALIDATION_PARAM_ITEM_ID2,
1962 to_number(X_VISIBLE),
1963 X_INFORMATION_PARAM_ITEM_ID3,
1964 X_INFORMATION_PARAM_ITEM_ID4,
1965 X_INFORMATION_PARAM_ITEM_ID5,
1966 to_number(X_INSERT_ALLOWED),
1967 to_number(X_PROMPT_ALIGNMENT_OFFSET),
1968 to_number(X_PROMPT_DISPLAY_STYLE),
1969 to_number(X_PROMPT_EDGE),
1970 X_INFORMATION_PROMPT,
1971 X_LABEL,
1972 X_PROMPT_TEXT,
1973 X_TOOLTIP_TEXT,
1974 f_ludate,
1975 f_luby,
1976 0
1977 );
1978 END IF;
1979 exception
1980 when no_data_found then
1981 INSERT_ROW (
1982 X_ROWID,
1983 X_ITEM_PROPERTY_ID,
1984 to_number(X_UPDATE_ALLOWED),
1985 X_INFORMATION20,
1986 X_INFORMATION21,
1987 X_INFORMATION22,
1988 X_INFORMATION23,
1989 X_INFORMATION24,
1990 X_INFORMATION25,
1991 X_INFORMATION26,
1992 X_INFORMATION27,
1993 X_INFORMATION28,
1994 X_INFORMATION29,
1995 X_INFORMATION30,
1996 X_NEXT_NAVIGATION_ITEM_ID,
1997 X_PREVIOUS_NAVIGATION_ITEM_ID,
1998 to_number(X_PROMPT_TEXT_ALIGNMENT),
1999 to_number(X_QUERY_ALLOWED),
2000 to_number(X_REQUIRED),
2001 to_number(X_WIDTH),
2002 to_number(X_X_POSITION),
2003 to_number(X_Y_POSITION),
2004 X_VALIDATION_PARAM_ITEM_ID3,
2005 X_VALIDATION_PARAM_ITEM_ID4,
2006 X_VALIDATION_PARAM_ITEM_ID1,
2007 to_number(X_PROMPT_EDGE_OFFSET),
2008 X_INFORMATION_CATEGORY,
2009 X_INFORMATION1,
2010 X_INFORMATION2,
2011 X_INFORMATION3,
2012 X_INFORMATION4,
2013 X_INFORMATION5,
2014 X_INFORMATION6,
2015 X_INFORMATION7,
2016 X_INFORMATION8,
2017 X_INFORMATION9,
2018 X_INFORMATION10,
2019 X_INFORMATION11,
2020 X_INFORMATION12,
2021 X_INFORMATION13,
2022 X_INFORMATION14,
2023 X_INFORMATION15,
2024 X_FORM_ITEM_ID,
2025 X_TEMPLATE_ITEM_ID,
2026 X_TEMPLATE_ITEM_CONTEXT_ID,
2027 to_number(X_ALIGNMENT),
2028 to_number(X_BEVEL),
2029 to_number(X_CASE_RESTRICTION),
2030 X_DEFAULT_VALUE,
2031 to_number(X_ENABLED),
2032 X_FORMAT_MASK,
2033 to_number(X_HEIGHT),
2034 X_INFORMATION_FORMULA_ID,
2035 X_INFORMATION_PARAM_ITEM_ID1,
2036 X_INFORMATION_PARAM_ITEM_ID2,
2037 to_number(X_PROMPT_EDGE_ALIGNMENT),
2038 X_VALIDATION_PARAM_ITEM_ID5,
2039 X_VALIDATION_FORMULA_ID,
2040 X_INFORMATION16,
2041 X_INFORMATION17,
2042 X_INFORMATION18,
2043 X_INFORMATION19,
2044 X_VALIDATION_PARAM_ITEM_ID2,
2045 to_number(X_VISIBLE),
2046 X_INFORMATION_PARAM_ITEM_ID3,
2047 X_INFORMATION_PARAM_ITEM_ID4,
2048 X_INFORMATION_PARAM_ITEM_ID5,
2049 to_number(X_INSERT_ALLOWED),
2050 to_number(X_PROMPT_ALIGNMENT_OFFSET),
2051 to_number(X_PROMPT_DISPLAY_STYLE),
2052 to_number(X_PROMPT_EDGE),
2053 X_INFORMATION_PROMPT,
2054 X_LABEL,
2055 X_PROMPT_TEXT,
2056 X_TOOLTIP_TEXT,
2057 X_CREATION_DATE,
2058 X_CREATED_BY,
2059 f_ludate,
2060 f_luby,
2061 0
2062 );
2063 end;
2064 end LOAD_ROW;
2065 procedure TRANSLATE_ROW (
2066 X_APPLICATION_SHORT_NAME IN VARCHAR2,
2067 X_FORM_NAME IN VARCHAR2,
2068 X_FULL_ITEM_NAME IN VARCHAR2,
2069 X_RADIO_BUTTON_NAME IN VARCHAR2,
2070 X_TEMPLATE_NAME IN VARCHAR2,
2071 X_TERRITORY_SHORT_NAME IN VARCHAR2,
2072 X_SEGMENT1 IN VARCHAR2,
2073 X_SEGMENT2 IN VARCHAR2,
2074 X_SEGMENT3 IN VARCHAR2,
2075 X_SEGMENT4 IN VARCHAR2,
2076 X_SEGMENT5 IN VARCHAR2,
2077 X_SEGMENT6 IN VARCHAR2,
2078 X_SEGMENT7 IN VARCHAR2,
2079 X_SEGMENT8 IN VARCHAR2,
2080 X_SEGMENT9 IN VARCHAR2,
2081 X_SEGMENT10 IN VARCHAR2,
2082 X_SEGMENT11 IN VARCHAR2,
2083 X_SEGMENT12 IN VARCHAR2,
2084 X_SEGMENT13 IN VARCHAR2,
2085 X_SEGMENT14 IN VARCHAR2,
2086 X_SEGMENT15 IN VARCHAR2,
2087 X_SEGMENT16 IN VARCHAR2,
2088 X_SEGMENT17 IN VARCHAR2,
2089 X_SEGMENT18 IN VARCHAR2,
2090 X_SEGMENT19 IN VARCHAR2,
2091 X_SEGMENT20 IN VARCHAR2,
2092 X_SEGMENT21 IN VARCHAR2,
2093 X_SEGMENT22 IN VARCHAR2,
2094 X_SEGMENT23 IN VARCHAR2,
2095 X_SEGMENT24 IN VARCHAR2,
2096 X_SEGMENT25 IN VARCHAR2,
2097 X_SEGMENT26 IN VARCHAR2,
2098 X_SEGMENT27 IN VARCHAR2,
2099 X_SEGMENT28 IN VARCHAR2,
2100 X_SEGMENT29 IN VARCHAR2,
2101 X_SEGMENT30 IN VARCHAR2,
2102 X_ID_FLEX_STRUCTURE_CODE IN VARCHAR2,
2103 X_ID_FLEX_CODE IN VARCHAR2,
2104 X_OWNER IN VARCHAR2,
2105 X_DEFAULT_VALUE IN VARCHAR2,
2106 X_INFORMATION_PROMPT IN VARCHAR2,
2107 X_LABEL IN VARCHAR2,
2108 X_PROMPT_TEXT IN VARCHAR2,
2109 X_TOOLTIP_TEXT IN VARCHAR2,
2110 X_LAST_UPDATE_DATE in VARCHAR2,
2111 X_CUSTOM_MODE IN VARCHAR2 default null
2112 ) is
2113 X_CREATION_DATE DATE;
2114 X_CREATED_BY NUMBER;
2115 -- X_LAST_UPDATE_DATE DATE;
2116 X_LAST_UPDATED_BY NUMBER;
2117 X_LAST_UPDATE_LOGIN NUMBER;
2118 X_FORM_ID NUMBER;
2119 X_APPLICATION_ID NUMBER;
2120 X_FORM_ITEM_ID NUMBER;
2121 X_TEMPLATE_ITEM_ID NUMBER;
2122 X_TEMPLATE_ITEM_CONTEXT_ID NUMBER;
2123 X_ITEM_PROPERTY_ID NUMBER;
2124 f_luby number; -- entity owner in file
2125 f_ludate date; -- entity update date in file
2126 db_luby number; -- entity owner in db
2127 db_ludate date; -- entity update date in db
2128 begin
2129 /*
2130 OWNER_TO_WHO (
2131 X_OWNER,
2132 X_CREATION_DATE,
2133 X_CREATED_BY,
2134 X_LAST_UPDATE_DATE,
2135 X_LAST_UPDATED_BY,
2136 X_LAST_UPDATE_LOGIN
2137 );
2138 */
2139 --2781560 enclose the select in the IF clause
2140 IF ( g_application_short_name is not null
2141 AND g_application_short_name = x_application_short_name ) THEN
2142 --previous run fetched the same value so return global variable
2143 null; --x_application_id := g_application_id;
2144 ELSE
2145 --identifier is different from last call or this is the first call
2146 select application_id
2147 into g_application_id --x_application_id
2148 from fnd_application
2149 where application_short_name = x_application_short_name;
2150 --
2151 --g_application_id := x_application_id;
2152 g_application_short_name := x_application_short_name;
2153 --
2154 END IF;
2155
2156 --2781560 enclose the select in the IF clause
2157 IF ( g_form_name is not null
2158 AND g_form_name = x_form_name ) THEN
2159 --previous run fetched the same value so return global variable
2160 null; --x_form_id := g_form_id;
2161 ELSE
2162 --identifier is different from last call or this is the first call
2163 select form_id
2164 into g_form_id --x_form_id
2165 from fnd_form
2166 where form_name = x_form_name
2167 and application_id = g_application_id; --x_application_id;
2168 --
2169 --g_form_id := x_form_id;
2170 g_form_name := x_form_name;
2171 --
2172 END IF;
2173
2174 select form_item_id
2175 into x_form_item_id
2176 from hr_form_items_b
2177 where full_item_name = x_full_item_name
2178 and application_id = g_application_id --x_application_id
2179 and form_id = g_form_id --x_form_id
2180 and ( radio_button_name = x_radio_button_name
2181 or (radio_button_name is null and x_radio_button_name is null) );
2182
2183 /*
2184 2781560: replace this clause with the one below
2185
2186 if ltrim(rtrim(x_template_name)) is not null then
2187
2188 select hti.template_item_id
2189 into x_template_item_id
2190 from hr_form_templates_b hft
2191 ,hr_template_items_b hti
2192 where hti.form_item_id = x_form_item_id
2193 and hti.form_template_id = hft.form_template_id
2194 and hft.form_id = x_form_id
2195 and hft.application_id = x_application_id
2196 and hft.template_name = x_template_name
2197 and ( (hft.legislation_code is null and x_territory_short_name is null)
2198 or (hft.legislation_code = x_territory_short_name) );
2199
2200 end if;
2201 */
2202
2203 --rewrote above clause as follows for 2781560
2204
2205 IF x_template_name is not null THEN
2206 --
2207 IF ( g_template_name is not null
2208 AND g_template_name = x_template_name
2209 AND ( (g_legislation_code is null AND x_territory_short_name is null)
2210 OR (g_legislation_code is not null AND x_territory_short_name is not null
2211 AND g_legislation_code = x_territory_short_name)))
2212 THEN --identifiers same as last call, do nothing
2213 null;
2214 ELSE --identifiers different, recalculate global variables
2215 select hft.form_template_id
2216 into g_form_template_id
2217 from hr_form_templates_b hft
2218 where hft.form_id = g_form_id --x_form_id
2219 and hft.application_id = g_application_id --x_application_id
2220 and hft.template_name = x_template_name
2221 and ( (hft.legislation_code is null and x_territory_short_name is null)
2222 or (hft.legislation_code = x_territory_short_name) );
2223 --
2224 g_legislation_code := x_territory_short_name;
2225 g_template_name := x_template_name;
2226 --
2227 END IF;
2228 --
2229 select hti.template_item_id
2230 into x_template_item_id
2231 from hr_template_items_b hti
2232 where hti.form_item_id = x_form_item_id
2233 and hti.form_template_id = g_form_template_id;
2234 --
2235
2236 if ltrim(rtrim(x_id_flex_structure_code)) is not null then
2237
2238 -- segments can be null need this check
2239 select template_item_context_id
2240 into x_template_item_context_id
2241 from hr_template_item_contexts_b ticb
2242 ,hr_item_contexts hic
2243 ,fnd_id_flex_structures fifs
2244 where ticb.template_item_id = x_template_item_id
2245 and ticb.item_context_id = hic.item_context_id
2246 and nvl(hic.segment1,hr_api.g_varchar2) = nvl(x_segment1,hr_api.g_varchar2)
2247 and nvl(hic.segment2,hr_api.g_varchar2) = nvl(x_segment2,hr_api.g_varchar2)
2248 and nvl(hic.segment3,hr_api.g_varchar2) = nvl(x_segment3,hr_api.g_varchar2)
2249 and nvl(hic.segment4,hr_api.g_varchar2) = nvl(x_segment4,hr_api.g_varchar2)
2250 and nvl(hic.segment5,hr_api.g_varchar2) = nvl(x_segment5,hr_api.g_varchar2)
2251 and nvl(hic.segment6,hr_api.g_varchar2) = nvl(x_segment6,hr_api.g_varchar2)
2252 and nvl(hic.segment7,hr_api.g_varchar2) = nvl(x_segment7,hr_api.g_varchar2)
2253 and nvl(hic.segment8,hr_api.g_varchar2) = nvl(x_segment8,hr_api.g_varchar2)
2254 and nvl(hic.segment9,hr_api.g_varchar2) = nvl(x_segment9,hr_api.g_varchar2)
2255 and nvl(hic.segment10,hr_api.g_varchar2) = nvl(x_segment10,hr_api.g_varchar2)
2256 and nvl(hic.segment11,hr_api.g_varchar2) = nvl(x_segment11,hr_api.g_varchar2)
2257 and nvl(hic.segment12,hr_api.g_varchar2) = nvl(x_segment12,hr_api.g_varchar2)
2258 and nvl(hic.segment13,hr_api.g_varchar2) = nvl(x_segment13,hr_api.g_varchar2)
2259 and nvl(hic.segment14,hr_api.g_varchar2) = nvl(x_segment14,hr_api.g_varchar2)
2260 and nvl(hic.segment15,hr_api.g_varchar2) = nvl(x_segment15,hr_api.g_varchar2)
2261 and nvl(hic.segment16,hr_api.g_varchar2) = nvl(x_segment16,hr_api.g_varchar2)
2262 and nvl(hic.segment17,hr_api.g_varchar2) = nvl(x_segment17,hr_api.g_varchar2)
2263 and nvl(hic.segment18,hr_api.g_varchar2) = nvl(x_segment18,hr_api.g_varchar2)
2264 and nvl(hic.segment19,hr_api.g_varchar2) = nvl(x_segment19,hr_api.g_varchar2)
2265 and nvl(hic.segment20,hr_api.g_varchar2) = nvl(x_segment20,hr_api.g_varchar2)
2266 and nvl(hic.segment21,hr_api.g_varchar2) = nvl(x_segment21,hr_api.g_varchar2)
2267 and nvl(hic.segment22,hr_api.g_varchar2) = nvl(x_segment22,hr_api.g_varchar2)
2268 and nvl(hic.segment23,hr_api.g_varchar2) = nvl(x_segment23,hr_api.g_varchar2)
2269 and nvl(hic.segment24,hr_api.g_varchar2) = nvl(x_segment24,hr_api.g_varchar2)
2270 and nvl(hic.segment25,hr_api.g_varchar2) = nvl(x_segment25,hr_api.g_varchar2)
2271 and nvl(hic.segment26,hr_api.g_varchar2) = nvl(x_segment26,hr_api.g_varchar2)
2272 and nvl(hic.segment27,hr_api.g_varchar2) = nvl(x_segment27,hr_api.g_varchar2)
2273 and nvl(hic.segment28,hr_api.g_varchar2) = nvl(x_segment28,hr_api.g_varchar2)
2274 and nvl(hic.segment29,hr_api.g_varchar2) = nvl(x_segment29,hr_api.g_varchar2)
2275 and nvl(hic.segment30,hr_api.g_varchar2) = nvl(x_segment30,hr_api.g_varchar2)
2276 and hic.id_flex_num = fifs.id_flex_num
2277 and fifs.application_id = g_application_id --x_application_id
2278 and fifs.id_flex_structure_code = x_id_flex_structure_code
2279 and fifs.id_flex_code = x_id_flex_code;
2280 --
2281 x_form_item_id := null;
2282 x_template_item_id := null;
2283 select item_property_id
2284 into x_item_property_id
2285 from hr_item_properties_b
2286 where template_item_context_id=x_template_item_context_id;
2287 else --x_id_flex_structure_code is null
2288 x_template_item_context_id := null;
2289 if x_template_name is null then
2290 x_template_item_id := null;
2291 select item_property_id
2292 into x_item_property_id
2293 from hr_item_properties_b
2294 where form_item_id=x_form_item_id;
2295 else
2296 x_form_item_id := null;
2297 select item_property_id
2298 into x_item_property_id
2299 from hr_item_properties_b
2300 where template_item_id=x_template_item_id;
2301 end if;
2302 end if; --endif x_id_flex_structure_code is not null
2303
2304 END IF; --endif x_template_name is not null
2305
2306 --end of 2781560 rewrite
2307
2308
2309 /* removed for performance reasons, replaced by separate sql stmts immediately above
2310 select item_property_id
2311 into x_item_property_id
2312 from hr_item_properties_b
2313 where nvl(form_item_id,hr_api.g_number) = nvl(x_form_item_id,hr_api.g_number)
2314 and nvl(template_item_id,hr_api.g_number) = nvl(x_template_item_id,hr_api.g_number)
2315 and nvl(template_item_context_id,hr_api.g_number) = nvl(x_template_item_context_id,hr_api.g_number);
2316 */
2317
2318 -- Translate owner to file_last_updated_by
2319 f_luby := fnd_load_util.owner_id(X_OWNER);
2320 -- Translate char last_update_date to date
2321 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
2322
2323 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2324 into db_luby, db_ludate
2325 from HR_ITEM_PROPERTIES_TL
2326 where item_property_id = x_item_property_id
2327 and LANGUAGE=userenv('LANG');
2328
2329 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2330 db_ludate,X_CUSTOM_MODE)) then
2331
2332 update HR_ITEM_PROPERTIES_TL set
2333 DEFAULT_VALUE = X_DEFAULT_VALUE,
2334 INFORMATION_PROMPT = X_INFORMATION_PROMPT,
2335 LABEL = X_LABEL,
2336 PROMPT_TEXT = X_PROMPT_TEXT,
2337 TOOLTIP_TEXT = X_TOOLTIP_TEXT,
2338 LAST_UPDATE_DATE = f_ludate,
2339 LAST_UPDATED_BY = f_luby,
2340 LAST_UPDATE_LOGIN = 0,
2341 SOURCE_LANG = userenv('LANG')
2342 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
2343 and item_property_id = x_item_property_id;
2344
2345 end if;
2346 exception
2347 when no_data_found then
2348 -- Do not insert missing translations, skip this row
2349 null;
2350
2351 end TRANSLATE_ROW;
2352
2353 procedure TRANSLATE_ROW (
2354 X_APPLICATION_SHORT_NAME IN VARCHAR2,
2355 X_FORM_NAME IN VARCHAR2,
2356 X_FULL_ITEM_NAME IN VARCHAR2,
2357 X_RADIO_BUTTON_NAME IN VARCHAR2,
2358 X_TEMPLATE_NAME IN VARCHAR2,
2359 X_TERRITORY_SHORT_NAME IN VARCHAR2,
2360 X_SEGMENT1 IN VARCHAR2,
2361 X_SEGMENT2 IN VARCHAR2,
2362 X_SEGMENT3 IN VARCHAR2,
2363 X_SEGMENT4 IN VARCHAR2,
2364 X_SEGMENT5 IN VARCHAR2,
2365 X_SEGMENT6 IN VARCHAR2,
2366 X_SEGMENT7 IN VARCHAR2,
2367 X_SEGMENT8 IN VARCHAR2,
2368 X_SEGMENT9 IN VARCHAR2,
2369 X_SEGMENT10 IN VARCHAR2,
2370 X_SEGMENT11 IN VARCHAR2,
2371 X_SEGMENT12 IN VARCHAR2,
2372 X_SEGMENT13 IN VARCHAR2,
2373 X_SEGMENT14 IN VARCHAR2,
2374 X_SEGMENT15 IN VARCHAR2,
2375 X_SEGMENT16 IN VARCHAR2,
2376 X_SEGMENT17 IN VARCHAR2,
2377 X_SEGMENT18 IN VARCHAR2,
2378 X_SEGMENT19 IN VARCHAR2,
2379 X_SEGMENT20 IN VARCHAR2,
2380 X_SEGMENT21 IN VARCHAR2,
2381 X_SEGMENT22 IN VARCHAR2,
2382 X_SEGMENT23 IN VARCHAR2,
2383 X_SEGMENT24 IN VARCHAR2,
2384 X_SEGMENT25 IN VARCHAR2,
2385 X_SEGMENT26 IN VARCHAR2,
2386 X_SEGMENT27 IN VARCHAR2,
2387 X_SEGMENT28 IN VARCHAR2,
2388 X_SEGMENT29 IN VARCHAR2,
2389 X_SEGMENT30 IN VARCHAR2,
2390 X_ID_FLEX_STRUCTURE_CODE IN VARCHAR2,
2391 X_ID_FLEX_CODE IN VARCHAR2,
2392 X_OWNER IN VARCHAR2,
2393 X_DEFAULT_VALUE IN VARCHAR2,
2394 X_INFORMATION_PROMPT IN VARCHAR2,
2395 X_LABEL IN VARCHAR2,
2396 X_PROMPT_TEXT IN VARCHAR2,
2397 X_TOOLTIP_TEXT IN VARCHAR2,
2398 X_LAST_UPDATE_DATE in date default sysdate,
2399 X_CUSTOM_MODE IN VARCHAR2 default null
2400 ) is
2401 X_CREATION_DATE DATE;
2402 X_CREATED_BY NUMBER;
2403 -- X_LAST_UPDATE_DATE DATE;
2404 X_LAST_UPDATED_BY NUMBER;
2405 X_LAST_UPDATE_LOGIN NUMBER;
2406 X_FORM_ID NUMBER;
2407 X_APPLICATION_ID NUMBER;
2408 X_FORM_ITEM_ID NUMBER;
2409 X_TEMPLATE_ITEM_ID NUMBER;
2410 X_TEMPLATE_ITEM_CONTEXT_ID NUMBER;
2411 X_ITEM_PROPERTY_ID NUMBER;
2412 f_luby number; -- entity owner in file
2413 f_ludate date; -- entity update date in file
2414 db_luby number; -- entity owner in db
2415 db_ludate date; -- entity update date in db
2416 begin
2417 /*
2418 OWNER_TO_WHO (
2419 X_OWNER,
2420 X_CREATION_DATE,
2421 X_CREATED_BY,
2422 X_LAST_UPDATE_DATE,
2423 X_LAST_UPDATED_BY,
2424 X_LAST_UPDATE_LOGIN
2425 );
2426 */
2427 --2781560 enclose the select in the IF clause
2428 IF ( g_application_short_name is not null
2429 AND g_application_short_name = x_application_short_name ) THEN
2430 --previous run fetched the same value so return global variable
2431 null; --x_application_id := g_application_id;
2432 ELSE
2433 --identifier is different from last call or this is the first call
2434 select application_id
2435 into g_application_id --x_application_id
2436 from fnd_application
2437 where application_short_name = x_application_short_name;
2438 --
2439 --g_application_id := x_application_id;
2440 g_application_short_name := x_application_short_name;
2441 --
2442 END IF;
2443
2444 --2781560 enclose the select in the IF clause
2445 IF ( g_form_name is not null
2446 AND g_form_name = x_form_name ) THEN
2447 --previous run fetched the same value so return global variable
2448 null; --x_form_id := g_form_id;
2449 ELSE
2450 --identifier is different from last call or this is the first call
2451 select form_id
2452 into g_form_id --x_form_id
2453 from fnd_form
2454 where form_name = x_form_name
2455 and application_id = g_application_id; --x_application_id;
2456 --
2457 --g_form_id := x_form_id;
2458 g_form_name := x_form_name;
2459 --
2460 END IF;
2461
2462 select form_item_id
2463 into x_form_item_id
2464 from hr_form_items_b
2465 where full_item_name = x_full_item_name
2466 and application_id = g_application_id --x_application_id
2467 and form_id = g_form_id --x_form_id
2468 and ( radio_button_name = x_radio_button_name
2469 or (radio_button_name is null and x_radio_button_name is null) );
2470
2471 /*
2472 2781560: replace this clause with the one below
2473
2474 if ltrim(rtrim(x_template_name)) is not null then
2475
2476 select hti.template_item_id
2477 into x_template_item_id
2478 from hr_form_templates_b hft
2479 ,hr_template_items_b hti
2480 where hti.form_item_id = x_form_item_id
2481 and hti.form_template_id = hft.form_template_id
2482 and hft.form_id = x_form_id
2483 and hft.application_id = x_application_id
2484 and hft.template_name = x_template_name
2485 and ( (hft.legislation_code is null and x_territory_short_name is null)
2486 or (hft.legislation_code = x_territory_short_name) );
2487
2488 end if;
2489 */
2490
2491 --rewrote above clause as follows for 2781560
2492
2493 IF x_template_name is not null THEN
2494 --
2495 IF ( g_template_name is not null
2496 AND g_template_name = x_template_name
2497 AND ( (g_legislation_code is null AND x_territory_short_name is null)
2498 OR (g_legislation_code is not null AND x_territory_short_name is not null
2499 AND g_legislation_code = x_territory_short_name)))
2500 THEN --identifiers same as last call, do nothing
2501 null;
2502 ELSE --identifiers different, recalculate global variables
2503 select hft.form_template_id
2504 into g_form_template_id
2505 from hr_form_templates_b hft
2506 where hft.form_id = g_form_id --x_form_id
2507 and hft.application_id = g_application_id --x_application_id
2508 and hft.template_name = x_template_name
2509 and ( (hft.legislation_code is null and x_territory_short_name is null)
2510 or (hft.legislation_code = x_territory_short_name) );
2511 --
2512 g_legislation_code := x_territory_short_name;
2513 g_template_name := x_template_name;
2514 --
2515 END IF;
2516 --
2517 select hti.template_item_id
2518 into x_template_item_id
2519 from hr_template_items_b hti
2520 where hti.form_item_id = x_form_item_id
2521 and hti.form_template_id = g_form_template_id;
2522 --
2523
2524 if ltrim(rtrim(x_id_flex_structure_code)) is not null then
2525
2526 -- segments can be null need this check
2527 select template_item_context_id
2528 into x_template_item_context_id
2529 from hr_template_item_contexts_b ticb
2530 ,hr_item_contexts hic
2531 ,fnd_id_flex_structures fifs
2532 where ticb.template_item_id = x_template_item_id
2533 and ticb.item_context_id = hic.item_context_id
2534 and nvl(hic.segment1,hr_api.g_varchar2) = nvl(x_segment1,hr_api.g_varchar2)
2535 and nvl(hic.segment2,hr_api.g_varchar2) = nvl(x_segment2,hr_api.g_varchar2)
2536 and nvl(hic.segment3,hr_api.g_varchar2) = nvl(x_segment3,hr_api.g_varchar2)
2537 and nvl(hic.segment4,hr_api.g_varchar2) = nvl(x_segment4,hr_api.g_varchar2)
2538 and nvl(hic.segment5,hr_api.g_varchar2) = nvl(x_segment5,hr_api.g_varchar2)
2539 and nvl(hic.segment6,hr_api.g_varchar2) = nvl(x_segment6,hr_api.g_varchar2)
2540 and nvl(hic.segment7,hr_api.g_varchar2) = nvl(x_segment7,hr_api.g_varchar2)
2541 and nvl(hic.segment8,hr_api.g_varchar2) = nvl(x_segment8,hr_api.g_varchar2)
2542 and nvl(hic.segment9,hr_api.g_varchar2) = nvl(x_segment9,hr_api.g_varchar2)
2543 and nvl(hic.segment10,hr_api.g_varchar2) = nvl(x_segment10,hr_api.g_varchar2)
2544 and nvl(hic.segment11,hr_api.g_varchar2) = nvl(x_segment11,hr_api.g_varchar2)
2545 and nvl(hic.segment12,hr_api.g_varchar2) = nvl(x_segment12,hr_api.g_varchar2)
2546 and nvl(hic.segment13,hr_api.g_varchar2) = nvl(x_segment13,hr_api.g_varchar2)
2547 and nvl(hic.segment14,hr_api.g_varchar2) = nvl(x_segment14,hr_api.g_varchar2)
2548 and nvl(hic.segment15,hr_api.g_varchar2) = nvl(x_segment15,hr_api.g_varchar2)
2549 and nvl(hic.segment16,hr_api.g_varchar2) = nvl(x_segment16,hr_api.g_varchar2)
2550 and nvl(hic.segment17,hr_api.g_varchar2) = nvl(x_segment17,hr_api.g_varchar2)
2551 and nvl(hic.segment18,hr_api.g_varchar2) = nvl(x_segment18,hr_api.g_varchar2)
2552 and nvl(hic.segment19,hr_api.g_varchar2) = nvl(x_segment19,hr_api.g_varchar2)
2553 and nvl(hic.segment20,hr_api.g_varchar2) = nvl(x_segment20,hr_api.g_varchar2)
2554 and nvl(hic.segment21,hr_api.g_varchar2) = nvl(x_segment21,hr_api.g_varchar2)
2555 and nvl(hic.segment22,hr_api.g_varchar2) = nvl(x_segment22,hr_api.g_varchar2)
2556 and nvl(hic.segment23,hr_api.g_varchar2) = nvl(x_segment23,hr_api.g_varchar2)
2557 and nvl(hic.segment24,hr_api.g_varchar2) = nvl(x_segment24,hr_api.g_varchar2)
2558 and nvl(hic.segment25,hr_api.g_varchar2) = nvl(x_segment25,hr_api.g_varchar2)
2559 and nvl(hic.segment26,hr_api.g_varchar2) = nvl(x_segment26,hr_api.g_varchar2)
2560 and nvl(hic.segment27,hr_api.g_varchar2) = nvl(x_segment27,hr_api.g_varchar2)
2561 and nvl(hic.segment28,hr_api.g_varchar2) = nvl(x_segment28,hr_api.g_varchar2)
2562 and nvl(hic.segment29,hr_api.g_varchar2) = nvl(x_segment29,hr_api.g_varchar2)
2563 and nvl(hic.segment30,hr_api.g_varchar2) = nvl(x_segment30,hr_api.g_varchar2)
2564 and hic.id_flex_num = fifs.id_flex_num
2565 and fifs.application_id = g_application_id --x_application_id
2566 and fifs.id_flex_structure_code = x_id_flex_structure_code
2567 and fifs.id_flex_code = x_id_flex_code;
2568 --
2569 x_form_item_id := null;
2570 x_template_item_id := null;
2571 select item_property_id
2572 into x_item_property_id
2573 from hr_item_properties_b
2574 where template_item_context_id=x_template_item_context_id;
2575 else --x_id_flex_structure_code is null
2576 x_template_item_context_id := null;
2577 if x_template_name is null then
2578 x_template_item_id := null;
2579 select item_property_id
2580 into x_item_property_id
2581 from hr_item_properties_b
2582 where form_item_id=x_form_item_id;
2583 else
2584 x_form_item_id := null;
2585 select item_property_id
2586 into x_item_property_id
2587 from hr_item_properties_b
2588 where template_item_id=x_template_item_id;
2589 end if;
2590 end if; --endif x_id_flex_structure_code is not null
2591
2592 END IF; --endif x_template_name is not null
2593
2594 --end of 2781560 rewrite
2595
2596
2597 /* removed for performance reasons, replaced by separate sql stmts immediately above
2598 select item_property_id
2599 into x_item_property_id
2600 from hr_item_properties_b
2601 where nvl(form_item_id,hr_api.g_number) = nvl(x_form_item_id,hr_api.g_number)
2602 and nvl(template_item_id,hr_api.g_number) = nvl(x_template_item_id,hr_api.g_number)
2603 and nvl(template_item_context_id,hr_api.g_number) = nvl(x_template_item_context_id,hr_api.g_number);
2604 */
2605
2606 -- Translate owner to file_last_updated_by
2607 f_luby := fnd_load_util.owner_id(X_OWNER);
2608 -- Translate char last_update_date to date
2609 -- f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate); -- Bug 5571335
2610 f_ludate := nvl(X_LAST_UPDATE_DATE, sysdate);
2611
2612 select LAST_UPDATED_BY, LAST_UPDATE_DATE
2613 into db_luby, db_ludate
2614 from HR_ITEM_PROPERTIES_TL
2615 where item_property_id = x_item_property_id
2616 and LANGUAGE=userenv('LANG');
2617
2618 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
2619 db_ludate,X_CUSTOM_MODE)) then
2620
2621 update HR_ITEM_PROPERTIES_TL set
2622 DEFAULT_VALUE = X_DEFAULT_VALUE,
2623 INFORMATION_PROMPT = X_INFORMATION_PROMPT,
2624 LABEL = X_LABEL,
2625 PROMPT_TEXT = X_PROMPT_TEXT,
2626 TOOLTIP_TEXT = X_TOOLTIP_TEXT,
2627 LAST_UPDATE_DATE = f_ludate,
2628 LAST_UPDATED_BY = f_luby,
2629 LAST_UPDATE_LOGIN = 0,
2630 SOURCE_LANG = userenv('LANG')
2631 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
2632 and item_property_id = x_item_property_id;
2633
2634 end if;
2635 exception
2636 when no_data_found then
2637 -- Do not insert missing translations, skip this row
2638 null;
2639
2640 end TRANSLATE_ROW;
2641
2642 end HR_ITEM_PROPERTIES_PKG;