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