[Home] [Help]
PACKAGE BODY: APPS.QP_QUALIFIERS_UTIL
Source
1 PACKAGE BODY QP_Qualifiers_Util AS
2 /* $Header: QPXUQPQB.pls 120.9.12010000.2 2008/10/14 12:00:34 skattama ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Qualifiers_Util';
7
8 -- Procedure Clear_Dependent_Attr
9
10 PROCEDURE Clear_Dependent_Attr
11 ( p_attr_id IN NUMBER := FND_API.G_MISS_NUM
12 , p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
13 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type :=
14 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC
15 , x_QUALIFIERS_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
16 )
17 IS
18 l_index NUMBER := 0;
19 l_src_attr_tbl QP_GLOBALS.NUMBER_Tbl_Type;
20 l_dep_attr_tbl QP_GLOBALS.NUMBER_Tbl_Type;
21 BEGIN
22
23 -- Load out record
24
25 x_QUALIFIERS_rec := p_QUALIFIERS_rec;
26
27 -- Following if statement is added by svdeshmu
28 -- If operator is updated from 'between' to '=' then qualifier_attr_value_to_code(ui field)
29 -- gets set to null.However qualifier_attr_value_to (database field) remains unclear.
30 --Hence if you query the record after this change, even though the operator is '='
31 -- 'value to 'is shown on the UI.(which is incorrect)
32 --Hence following statement is added to clear the qualifier_attr_value_to (database field)
33 --whenever operator is other than 'between'.
34
35 if p_old_QUALIFIERS_rec.comparison_operator_code = 'BETWEEN' AND
36 p_QUALIFIERS_rec.comparison_operator_code <> 'BETWEEN' then
37
38 x_QUALIFIERS_rec.qualifier_attr_value_to := null;
39 end if;
40
41
42
43 -- If attr_id is missing compare old and new records and for
44 -- every changed attribute clear its dependent fields.
45
46
47 IF p_attr_id = FND_API.G_MISS_NUM THEN
48
49 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute1,p_old_QUALIFIERS_rec.attribute1)
50 THEN
51 l_index := l_index + 1;
52 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE1;
53 END IF;
54
55 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute10,p_old_QUALIFIERS_rec.attribute10)
56 THEN
57 l_index := l_index + 1;
58 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE10;
59 END IF;
60
61 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute11,p_old_QUALIFIERS_rec.attribute11)
62 THEN
63 l_index := l_index + 1;
64 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE11;
65 END IF;
66
67 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute12,p_old_QUALIFIERS_rec.attribute12)
68 THEN
69 l_index := l_index + 1;
70 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE12;
71 END IF;
72
73 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute13,p_old_QUALIFIERS_rec.attribute13)
74 THEN
75 l_index := l_index + 1;
76 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE13;
77 END IF;
78
79 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute14,p_old_QUALIFIERS_rec.attribute14)
80 THEN
81 l_index := l_index + 1;
82 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE14;
83 END IF;
84
85 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute15,p_old_QUALIFIERS_rec.attribute15)
86 THEN
87 l_index := l_index + 1;
88 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE15;
89 END IF;
90
91 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute2,p_old_QUALIFIERS_rec.attribute2)
92 THEN
93 l_index := l_index + 1;
94 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE2;
95 END IF;
96
97 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute3,p_old_QUALIFIERS_rec.attribute3)
98 THEN
99 l_index := l_index + 1;
100 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE3;
101 END IF;
102
103 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute4,p_old_QUALIFIERS_rec.attribute4)
104 THEN
105 l_index := l_index + 1;
106 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE4;
107 END IF;
108
109 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute5,p_old_QUALIFIERS_rec.attribute5)
110 THEN
111 l_index := l_index + 1;
112 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE5;
113 END IF;
114
115 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute6,p_old_QUALIFIERS_rec.attribute6)
116 THEN
117 l_index := l_index + 1;
118 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE6;
119 END IF;
120
121 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute7,p_old_QUALIFIERS_rec.attribute7)
122 THEN
123 l_index := l_index + 1;
124 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE7;
125 END IF;
126
127 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute8,p_old_QUALIFIERS_rec.attribute8)
128 THEN
129 l_index := l_index + 1;
130 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE8;
131 END IF;
132
133 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute9,p_old_QUALIFIERS_rec.attribute9)
134 THEN
135 l_index := l_index + 1;
136 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE9;
137 END IF;
138
139 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.comparison_operator_code,p_old_QUALIFIERS_rec.comparison_operator_code)
140 THEN
141 l_index := l_index + 1;
142 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_COMPARISON_OPERATOR;
143 END IF;
144
145 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.context,p_old_QUALIFIERS_rec.context)
146 THEN
147 l_index := l_index + 1;
148 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CONTEXT;
149 END IF;
150
151 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_by,p_old_QUALIFIERS_rec.created_by)
152 THEN
153 l_index := l_index + 1;
154 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CREATED_BY;
155 END IF;
156
157 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_from_rule_id,p_old_QUALIFIERS_rec.created_from_rule_id)
158 THEN
159 l_index := l_index + 1;
160 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CREATED_FROM_RULE;
161 END IF;
162
163 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.creation_date,p_old_QUALIFIERS_rec.creation_date)
164 THEN
165 l_index := l_index + 1;
166 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CREATION_DATE;
167 END IF;
168
169 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.end_date_active,p_old_QUALIFIERS_rec.end_date_active)
170 THEN
171 l_index := l_index + 1;
172 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_END_DATE_ACTIVE;
173 END IF;
174
175 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.excluder_flag,p_old_QUALIFIERS_rec.excluder_flag)
176 THEN
177 l_index := l_index + 1;
178 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_EXCLUDER;
179 END IF;
180
181 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,p_old_QUALIFIERS_rec.last_updated_by)
182 THEN
183 l_index := l_index + 1;
184 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATED_BY;
185 END IF;
186
187 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,p_old_QUALIFIERS_rec.last_update_date)
188 THEN
189 l_index := l_index + 1;
190 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_DATE;
191 END IF;
192
193 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,p_old_QUALIFIERS_rec.last_update_login)
194 THEN
195 l_index := l_index + 1;
196 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_LOGIN;
197 END IF;
198
199 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,p_old_QUALIFIERS_rec.list_header_id)
200 THEN
201 l_index := l_index + 1;
202 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LIST_HEADER;
203 END IF;
204
205 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,p_old_QUALIFIERS_rec.list_line_id)
206 THEN
207 l_index := l_index + 1;
208 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LIST_LINE;
209 END IF;
210
211 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_application_id,p_old_QUALIFIERS_rec.program_application_id)
212 THEN
213 l_index := l_index + 1;
214 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM_APPLICATION;
215 END IF;
216
217 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_id,p_old_QUALIFIERS_rec.program_id)
218 THEN
219 l_index := l_index + 1;
220 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM;
221 END IF;
222
223 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,p_old_QUALIFIERS_rec.program_update_date)
224 THEN
225 l_index := l_index + 1;
226 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM_UPDATE_DATE;
227 END IF;
228
229 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,p_old_QUALIFIERS_rec.qualifier_attribute)
230 THEN
231 l_index := l_index + 1;
232 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_ATTRIBUTE;
233 END IF;
234
235 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value,p_old_QUALIFIERS_rec.qualifier_attr_value)
236 THEN
237 l_index := l_index + 1;
238 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_ATTR_VALUE;
239 END IF;
240
241
242 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value_to,p_old_QUALIFIERS_rec.qualifier_attr_value_to)
243 THEN
244 l_index := l_index + 1;
245 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_ATTR_VALUE_TO;
246 END IF;
247
248
249
250
251
252 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_context,p_old_QUALIFIERS_rec.qualifier_context)
253 THEN
254 l_index := l_index + 1;
255 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_CONTEXT;
256 END IF;
257
258 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_datatype,p_old_QUALIFIERS_rec.qualifier_datatype)
259 THEN
260 l_index := l_index + 1;
261 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_DATATYPE;
262 END IF;
263
264 /* IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_date_format,p_old_QUALIFIERS_rec.qualifier_date_format)
265 THEN
266 l_index := l_index + 1;
267 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_DATE_FORMAT;
268 END IF;*/
269
270 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,p_old_QUALIFIERS_rec.qualifier_grouping_no)
271 THEN
272 l_index := l_index + 1;
273 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_GROUPING_NO;
274 END IF;
275
276 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,p_old_QUALIFIERS_rec.qualifier_id)
277 THEN
278 l_index := l_index + 1;
279 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER;
280 END IF;
281
282 /* IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_number_format,p_old_QUALIFIERS_rec.qualifier_number_format)
283 THEN
284 l_index := l_index + 1;
285 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_NUMBER_FORMAT;
286 END IF;*/
287
288 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_precedence,p_old_QUALIFIERS_rec.qualifier_precedence)
289 THEN
290 l_index := l_index + 1;
291 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_PRECEDENCE;
292 END IF;
293
294 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,p_old_QUALIFIERS_rec.qualifier_rule_id)
295 THEN
296 l_index := l_index + 1;
297 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_RULE;
298 END IF;
299
300 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.request_id,p_old_QUALIFIERS_rec.request_id)
301 THEN
302 l_index := l_index + 1;
303 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_REQUEST;
304 END IF;
305
306 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.start_date_active,p_old_QUALIFIERS_rec.start_date_active)
307 THEN
308 l_index := l_index + 1;
309 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_START_DATE_ACTIVE;
310 END IF;
311 -- Added for TCA
312 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualify_hier_descendent_flag,p_old_QUALIFIERS_rec.qualify_hier_descendent_flag)
313 THEN
314 l_index := l_index + 1;
315 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFY_HIER_DESCENDENT_FLAG;
316 END IF;
317
318 ELSIF p_attr_id = G_ATTRIBUTE1 THEN
319 l_index := l_index + 1;
320 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE1;
321 ELSIF p_attr_id = G_ATTRIBUTE10 THEN
322 l_index := l_index + 1;
323 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE10;
324 ELSIF p_attr_id = G_ATTRIBUTE11 THEN
325 l_index := l_index + 1;
326 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE11;
327 ELSIF p_attr_id = G_ATTRIBUTE12 THEN
328 l_index := l_index + 1;
329 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE12;
330 ELSIF p_attr_id = G_ATTRIBUTE13 THEN
331 l_index := l_index + 1;
332 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE13;
333 ELSIF p_attr_id = G_ATTRIBUTE14 THEN
334 l_index := l_index + 1;
335 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE14;
336 ELSIF p_attr_id = G_ATTRIBUTE15 THEN
337 l_index := l_index + 1;
338 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE15;
339 ELSIF p_attr_id = G_ATTRIBUTE2 THEN
340 l_index := l_index + 1;
341 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE2;
342 ELSIF p_attr_id = G_ATTRIBUTE3 THEN
343 l_index := l_index + 1;
344 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE3;
345 ELSIF p_attr_id = G_ATTRIBUTE4 THEN
346 l_index := l_index + 1;
347 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE4;
348 ELSIF p_attr_id = G_ATTRIBUTE5 THEN
349 l_index := l_index + 1;
350 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE5;
351 ELSIF p_attr_id = G_ATTRIBUTE6 THEN
352 l_index := l_index + 1;
353 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE6;
354 ELSIF p_attr_id = G_ATTRIBUTE7 THEN
355 l_index := l_index + 1;
356 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE7;
357 ELSIF p_attr_id = G_ATTRIBUTE8 THEN
358 l_index := l_index + 1;
359 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE8;
360 ELSIF p_attr_id = G_ATTRIBUTE9 THEN
361 l_index := l_index + 1;
362 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_ATTRIBUTE9;
363 ELSIF p_attr_id = G_COMPARISON_OPERATOR THEN
364 l_index := l_index + 1;
365 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_COMPARISON_OPERATOR;
366 ELSIF p_attr_id = G_CONTEXT THEN
367 l_index := l_index + 1;
368 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CONTEXT;
369 ELSIF p_attr_id = G_CREATED_BY THEN
370 l_index := l_index + 1;
371 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CREATED_BY;
372 ELSIF p_attr_id = G_CREATED_FROM_RULE THEN
373 l_index := l_index + 1;
374 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CREATED_FROM_RULE;
375 ELSIF p_attr_id = G_CREATION_DATE THEN
376 l_index := l_index + 1;
377 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_CREATION_DATE;
378 ELSIF p_attr_id = G_END_DATE_ACTIVE THEN
379 l_index := l_index + 1;
380 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_END_DATE_ACTIVE;
381 ELSIF p_attr_id = G_EXCLUDER THEN
382 l_index := l_index + 1;
383 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_EXCLUDER;
384 ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
385 l_index := l_index + 1;
386 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATED_BY;
387 ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
388 l_index := l_index + 1;
389 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_DATE;
390 ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
391 l_index := l_index + 1;
392 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LAST_UPDATE_LOGIN;
393 ELSIF p_attr_id = G_LIST_HEADER THEN
394 l_index := l_index + 1;
395 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LIST_HEADER;
396 ELSIF p_attr_id = G_LIST_LINE THEN
397 l_index := l_index + 1;
398 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_LIST_LINE;
399 ELSIF p_attr_id = G_PROGRAM_APPLICATION THEN
400 l_index := l_index + 1;
401 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM_APPLICATION;
402 ELSIF p_attr_id = G_PROGRAM THEN
403 l_index := l_index + 1;
404 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM;
405 ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
406 l_index := l_index + 1;
407 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_PROGRAM_UPDATE_DATE;
408 ELSIF p_attr_id = G_QUALIFIER_ATTRIBUTE THEN
409 l_index := l_index + 1;
410 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_ATTRIBUTE;
411 ELSIF p_attr_id = G_QUALIFIER_ATTR_VALUE THEN
412 l_index := l_index + 1;
413 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_ATTR_VALUE;
414 ELSIF p_attr_id = G_QUALIFIER_ATTR_VALUE_TO THEN
415 l_index := l_index + 1;
416 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_ATTR_VALUE_TO;
417 ELSIF p_attr_id = G_QUALIFIER_CONTEXT THEN
418 l_index := l_index + 1;
419 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_CONTEXT;
420 ELSIF p_attr_id = G_QUALIFIER_DATATYPE THEN
421 l_index := l_index + 1;
422 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_DATATYPE;
423 --ELSIF p_attr_id = G_QUALIFIER_DATE_FORMAT THEN
424 -- l_index := l_index + 1;
425 -- l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_DATE_FORMAT;
426 ELSIF p_attr_id = G_QUALIFIER_GROUPING_NO THEN
427 l_index := l_index + 1;
428 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_GROUPING_NO;
429 ELSIF p_attr_id = G_QUALIFIER THEN
430 l_index := l_index + 1;
431 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER;
432 --ELSIF p_attr_id = G_QUALIFIER_NUMBER_FORMAT THEN
433 -- l_index := l_index + 1;
434 -- l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_NUMBER_FORMAT;
435 ELSIF p_attr_id = G_QUALIFIER_PRECEDENCE THEN
436 l_index := l_index + 1;
437 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_PRECEDENCE;
438 ELSIF p_attr_id = G_QUALIFIER_RULE THEN
439 l_index := l_index + 1;
440 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFIER_RULE;
441 ELSIF p_attr_id = G_REQUEST THEN
442 l_index := l_index + 1;
443 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_REQUEST;
444 ELSIF p_attr_id = G_START_DATE_ACTIVE THEN
445 l_index := l_index + 1;
446 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_START_DATE_ACTIVE;
447 -- Added for TCA
448 ELSIF p_attr_id = G_QUALIFY_HIER_DESCENDENT_FLAG THEN
449 l_index := l_index + 1;
450 l_src_attr_tbl(l_index) := QP_QUALIFIERS_UTIL.G_QUALIFY_HIER_DESCENDENT_FLAG;
451 END IF;
452
453 END Clear_Dependent_Attr;
454
455 -- Procedure Apply_Attribute_Changes
456
457 PROCEDURE Apply_Attribute_Changes
458 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
459 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type :=
460 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC
461 , x_QUALIFIERS_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
462 )
463 IS
464 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
465 BEGIN
466
467
468 oe_debug_pub.add('in apply attribute changes');
469
470
471 -- Load out record
472
473 x_QUALIFIERS_rec := p_QUALIFIERS_rec;
474
475 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute1,p_old_QUALIFIERS_rec.attribute1)
476 THEN
477 NULL;
478 END IF;
479
480 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute10,p_old_QUALIFIERS_rec.attribute10)
481 THEN
482 NULL;
483 END IF;
484
485 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute11,p_old_QUALIFIERS_rec.attribute11)
486 THEN
487 NULL;
488 END IF;
489
490 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute12,p_old_QUALIFIERS_rec.attribute12)
491 THEN
492 NULL;
493 END IF;
494
495 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute13,p_old_QUALIFIERS_rec.attribute13)
496 THEN
497 NULL;
498 END IF;
499
500 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute14,p_old_QUALIFIERS_rec.attribute14)
501 THEN
502 NULL;
503 END IF;
504
505 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute15,p_old_QUALIFIERS_rec.attribute15)
506 THEN
507 NULL;
508 END IF;
509
510 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute2,p_old_QUALIFIERS_rec.attribute2)
511 THEN
512 NULL;
513 END IF;
514
515 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute3,p_old_QUALIFIERS_rec.attribute3)
516 THEN
517 NULL;
518 END IF;
519
520 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute4,p_old_QUALIFIERS_rec.attribute4)
521 THEN
522 NULL;
523 END IF;
524
525 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute5,p_old_QUALIFIERS_rec.attribute5)
526 THEN
527 NULL;
528 END IF;
529
530 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute6,p_old_QUALIFIERS_rec.attribute6)
531 THEN
532 NULL;
533 END IF;
534
535 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute7,p_old_QUALIFIERS_rec.attribute7)
536 THEN
537 NULL;
538 END IF;
539
540 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute8,p_old_QUALIFIERS_rec.attribute8)
541 THEN
542 NULL;
543 END IF;
544
545 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute9,p_old_QUALIFIERS_rec.attribute9)
546 THEN
547 NULL;
548 END IF;
549
550 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.comparison_operator_code,p_old_QUALIFIERS_rec.comparison_operator_code)
551 THEN
552 NULL;
553 END IF;
554
555 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.context,p_old_QUALIFIERS_rec.context)
556 THEN
557 NULL;
558 END IF;
559
560 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_by,p_old_QUALIFIERS_rec.created_by)
561 THEN
562 NULL;
563 END IF;
564
565 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_from_rule_id,p_old_QUALIFIERS_rec.created_from_rule_id)
566 THEN
567 NULL;
568 END IF;
569
570 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.creation_date,p_old_QUALIFIERS_rec.creation_date)
571 THEN
572 NULL;
573 END IF;
574
575 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.end_date_active,p_old_QUALIFIERS_rec.end_date_active)
576 THEN
577 NULL;
578 END IF;
579
580 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.excluder_flag,p_old_QUALIFIERS_rec.excluder_flag)
581 THEN
582 NULL;
583 END IF;
584
585 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,p_old_QUALIFIERS_rec.last_updated_by)
586 THEN
587 NULL;
588 END IF;
589
590 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,p_old_QUALIFIERS_rec.last_update_date)
591 THEN
592 NULL;
593 END IF;
594
595 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,p_old_QUALIFIERS_rec.last_update_login)
596 THEN
597 NULL;
598 END IF;
599
600 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,p_old_QUALIFIERS_rec.list_header_id)
601 THEN
602 NULL;
603 END IF;
604
605 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,p_old_QUALIFIERS_rec.list_line_id)
606 THEN
607 NULL;
608 END IF;
609
610 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_application_id,p_old_QUALIFIERS_rec.program_application_id)
611 THEN
612 NULL;
613 END IF;
614
615 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_id,p_old_QUALIFIERS_rec.program_id)
616 THEN
617 NULL;
618 END IF;
619
620 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,p_old_QUALIFIERS_rec.program_update_date)
621 THEN
622 NULL;
623 END IF;
624
625 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,p_old_QUALIFIERS_rec.qualifier_attribute)
626 THEN
627 NULL;
628 END IF;
629
630 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value,p_old_QUALIFIERS_rec.qualifier_attr_value)
631 THEN
632 NULL;
633 END IF;
634 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value_to,p_old_QUALIFIERS_rec.qualifier_attr_value_to)
635 THEN
636 NULL;
637 END IF;
638
639 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_context,p_old_QUALIFIERS_rec.qualifier_context)
640 THEN
641 NULL;
642 END IF;
643
644 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_datatype,p_old_QUALIFIERS_rec.qualifier_datatype)
645 THEN
646 NULL;
647 END IF;
648
649 /*IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_date_format,p_old_QUALIFIERS_rec.qualifier_date_format)
650 THEN
651 NULL;
652 END IF;*/
653
654 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,p_old_QUALIFIERS_rec.qualifier_grouping_no)
655 THEN
656 NULL;
657 END IF;
658
659 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,p_old_QUALIFIERS_rec.qualifier_id)
660 THEN
661 NULL;
662 END IF;
663
664 /* IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_number_format,p_old_QUALIFIERS_rec.qualifier_number_format)
665 THEN
666 NULL;
667 END IF;*/
668
669 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_precedence,p_old_QUALIFIERS_rec.qualifier_precedence)
670 THEN
671 NULL;
672 END IF;
673
674 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,p_old_QUALIFIERS_rec.qualifier_rule_id)
675 THEN
676 NULL;
677 END IF;
678
679 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.request_id,p_old_QUALIFIERS_rec.request_id)
680 THEN
681 NULL;
682 END IF;
683
684 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.start_date_active,p_old_QUALIFIERS_rec.start_date_active)
685 THEN
686 NULL;
687 END IF;
688 -- Added for TCA
689 IF NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualify_hier_descendent_flag,p_old_QUALIFIERS_rec.qualify_hier_descendent_flag)
690 THEN
691 NULL;
692 END IF;
693 oe_debug_pub.add(' leaving apply attribute changes');
694
695 END Apply_Attribute_Changes;
696
697 -- Function Complete_Record
698
699 FUNCTION Complete_Record
700 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
701 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
702 ) RETURN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
703 IS
704 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type := p_QUALIFIERS_rec;
705 BEGIN
706
707 IF l_QUALIFIERS_rec.attribute1 = FND_API.G_MISS_CHAR THEN
708 l_QUALIFIERS_rec.attribute1 := p_old_QUALIFIERS_rec.attribute1;
709 END IF;
710
711 IF l_QUALIFIERS_rec.attribute10 = FND_API.G_MISS_CHAR THEN
712 l_QUALIFIERS_rec.attribute10 := p_old_QUALIFIERS_rec.attribute10;
713 END IF;
714
715 IF l_QUALIFIERS_rec.attribute11 = FND_API.G_MISS_CHAR THEN
716 l_QUALIFIERS_rec.attribute11 := p_old_QUALIFIERS_rec.attribute11;
717 END IF;
718
719 IF l_QUALIFIERS_rec.attribute12 = FND_API.G_MISS_CHAR THEN
720 l_QUALIFIERS_rec.attribute12 := p_old_QUALIFIERS_rec.attribute12;
721 END IF;
722
723 IF l_QUALIFIERS_rec.attribute13 = FND_API.G_MISS_CHAR THEN
724 l_QUALIFIERS_rec.attribute13 := p_old_QUALIFIERS_rec.attribute13;
725 END IF;
726
727 IF l_QUALIFIERS_rec.attribute14 = FND_API.G_MISS_CHAR THEN
728 l_QUALIFIERS_rec.attribute14 := p_old_QUALIFIERS_rec.attribute14;
729 END IF;
730
731 IF l_QUALIFIERS_rec.attribute15 = FND_API.G_MISS_CHAR THEN
732 l_QUALIFIERS_rec.attribute15 := p_old_QUALIFIERS_rec.attribute15;
733 END IF;
734
735 IF l_QUALIFIERS_rec.attribute2 = FND_API.G_MISS_CHAR THEN
736 l_QUALIFIERS_rec.attribute2 := p_old_QUALIFIERS_rec.attribute2;
737 END IF;
738
739 IF l_QUALIFIERS_rec.attribute3 = FND_API.G_MISS_CHAR THEN
740 l_QUALIFIERS_rec.attribute3 := p_old_QUALIFIERS_rec.attribute3;
741 END IF;
742
743 IF l_QUALIFIERS_rec.attribute4 = FND_API.G_MISS_CHAR THEN
744 l_QUALIFIERS_rec.attribute4 := p_old_QUALIFIERS_rec.attribute4;
745 END IF;
746
747 IF l_QUALIFIERS_rec.attribute5 = FND_API.G_MISS_CHAR THEN
748 l_QUALIFIERS_rec.attribute5 := p_old_QUALIFIERS_rec.attribute5;
749 END IF;
750
751 IF l_QUALIFIERS_rec.attribute6 = FND_API.G_MISS_CHAR THEN
752 l_QUALIFIERS_rec.attribute6 := p_old_QUALIFIERS_rec.attribute6;
753 END IF;
754
755 IF l_QUALIFIERS_rec.attribute7 = FND_API.G_MISS_CHAR THEN
756 l_QUALIFIERS_rec.attribute7 := p_old_QUALIFIERS_rec.attribute7;
757 END IF;
758
759 IF l_QUALIFIERS_rec.attribute8 = FND_API.G_MISS_CHAR THEN
760 l_QUALIFIERS_rec.attribute8 := p_old_QUALIFIERS_rec.attribute8;
761 END IF;
762
763 IF l_QUALIFIERS_rec.attribute9 = FND_API.G_MISS_CHAR THEN
764 l_QUALIFIERS_rec.attribute9 := p_old_QUALIFIERS_rec.attribute9;
765 END IF;
766
767 IF l_QUALIFIERS_rec.comparison_operator_code = FND_API.G_MISS_CHAR THEN
768 l_QUALIFIERS_rec.comparison_operator_code := p_old_QUALIFIERS_rec.comparison_operator_code;
769 END IF;
770
771 IF l_QUALIFIERS_rec.context = FND_API.G_MISS_CHAR THEN
772 l_QUALIFIERS_rec.context := p_old_QUALIFIERS_rec.context;
773 END IF;
774
775 IF l_QUALIFIERS_rec.created_by = FND_API.G_MISS_NUM THEN
776 l_QUALIFIERS_rec.created_by := p_old_QUALIFIERS_rec.created_by;
777 END IF;
778
779 IF l_QUALIFIERS_rec.created_from_rule_id = FND_API.G_MISS_NUM THEN
780 l_QUALIFIERS_rec.created_from_rule_id := p_old_QUALIFIERS_rec.created_from_rule_id;
781 END IF;
782
783 IF l_QUALIFIERS_rec.creation_date = FND_API.G_MISS_DATE THEN
784 l_QUALIFIERS_rec.creation_date := p_old_QUALIFIERS_rec.creation_date;
785 END IF;
786
787 IF l_QUALIFIERS_rec.end_date_active = FND_API.G_MISS_DATE THEN
788 l_QUALIFIERS_rec.end_date_active := p_old_QUALIFIERS_rec.end_date_active;
789 END IF;
790
791 IF l_QUALIFIERS_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
792 l_QUALIFIERS_rec.excluder_flag := p_old_QUALIFIERS_rec.excluder_flag;
793 END IF;
794
795 IF l_QUALIFIERS_rec.last_updated_by = FND_API.G_MISS_NUM THEN
796 l_QUALIFIERS_rec.last_updated_by := p_old_QUALIFIERS_rec.last_updated_by;
797 END IF;
798
799 IF l_QUALIFIERS_rec.last_update_date = FND_API.G_MISS_DATE THEN
800 l_QUALIFIERS_rec.last_update_date := p_old_QUALIFIERS_rec.last_update_date;
801 END IF;
802
803 IF l_QUALIFIERS_rec.last_update_login = FND_API.G_MISS_NUM THEN
804 l_QUALIFIERS_rec.last_update_login := p_old_QUALIFIERS_rec.last_update_login;
805 END IF;
806
807 IF l_QUALIFIERS_rec.list_header_id = FND_API.G_MISS_NUM THEN
808 l_QUALIFIERS_rec.list_header_id := p_old_QUALIFIERS_rec.list_header_id;
809 END IF;
810
811 IF l_QUALIFIERS_rec.list_line_id = FND_API.G_MISS_NUM THEN
812 l_QUALIFIERS_rec.list_line_id := p_old_QUALIFIERS_rec.list_line_id;
813 END IF;
814
815 IF l_QUALIFIERS_rec.program_application_id = FND_API.G_MISS_NUM THEN
816 l_QUALIFIERS_rec.program_application_id := p_old_QUALIFIERS_rec.program_application_id;
817 END IF;
818
819 IF l_QUALIFIERS_rec.program_id = FND_API.G_MISS_NUM THEN
820 l_QUALIFIERS_rec.program_id := p_old_QUALIFIERS_rec.program_id;
821 END IF;
822
823 IF l_QUALIFIERS_rec.program_update_date = FND_API.G_MISS_DATE THEN
824 l_QUALIFIERS_rec.program_update_date := p_old_QUALIFIERS_rec.program_update_date;
825 END IF;
826
827 IF l_QUALIFIERS_rec.qualifier_attribute = FND_API.G_MISS_CHAR THEN
828 l_QUALIFIERS_rec.qualifier_attribute := p_old_QUALIFIERS_rec.qualifier_attribute;
829 END IF;
830
831 IF l_QUALIFIERS_rec.qualifier_attr_value = FND_API.G_MISS_CHAR THEN
832 l_QUALIFIERS_rec.qualifier_attr_value := p_old_QUALIFIERS_rec.qualifier_attr_value;
833 END IF;
834 IF l_QUALIFIERS_rec.qualifier_attr_value_to = FND_API.G_MISS_CHAR THEN
835 l_QUALIFIERS_rec.qualifier_attr_value_to := p_old_QUALIFIERS_rec.qualifier_attr_value_to;
836 END IF;
837
838 IF l_QUALIFIERS_rec.qualifier_context = FND_API.G_MISS_CHAR THEN
839 l_QUALIFIERS_rec.qualifier_context := p_old_QUALIFIERS_rec.qualifier_context;
840 END IF;
841
842 IF l_QUALIFIERS_rec.qualifier_datatype = FND_API.G_MISS_CHAR THEN
843 l_QUALIFIERS_rec.qualifier_datatype := p_old_QUALIFIERS_rec.qualifier_datatype;
844 END IF;
845
846 /*IF l_QUALIFIERS_rec.qualifier_date_format = FND_API.G_MISS_CHAR THEN
847 l_QUALIFIERS_rec.qualifier_date_format := p_old_QUALIFIERS_rec.qualifier_date_format;
848 END IF;*/
849
850 IF l_QUALIFIERS_rec.qualifier_grouping_no = FND_API.G_MISS_NUM THEN
851 l_QUALIFIERS_rec.qualifier_grouping_no := p_old_QUALIFIERS_rec.qualifier_grouping_no;
852 END IF;
853
854 IF l_QUALIFIERS_rec.qualifier_id = FND_API.G_MISS_NUM THEN
855 l_QUALIFIERS_rec.qualifier_id := p_old_QUALIFIERS_rec.qualifier_id;
856 END IF;
857
858 /*IF l_QUALIFIERS_rec.qualifier_number_format = FND_API.G_MISS_CHAR THEN
859 l_QUALIFIERS_rec.qualifier_number_format := p_old_QUALIFIERS_rec.qualifier_number_format;
860 END IF;*/
861
862 IF l_QUALIFIERS_rec.qualifier_precedence = FND_API.G_MISS_NUM THEN
863 l_QUALIFIERS_rec.qualifier_precedence := p_old_QUALIFIERS_rec.qualifier_precedence;
864 END IF;
865
866 IF l_QUALIFIERS_rec.qualifier_rule_id = FND_API.G_MISS_NUM THEN
867 l_QUALIFIERS_rec.qualifier_rule_id := p_old_QUALIFIERS_rec.qualifier_rule_id;
868 END IF;
869
870 IF l_QUALIFIERS_rec.request_id = FND_API.G_MISS_NUM THEN
871 l_QUALIFIERS_rec.request_id := p_old_QUALIFIERS_rec.request_id;
872 END IF;
873
874 IF l_QUALIFIERS_rec.start_date_active = FND_API.G_MISS_DATE THEN
875 l_QUALIFIERS_rec.start_date_active := p_old_QUALIFIERS_rec.start_date_active;
876 END IF;
877 -- Added for TCA
878 IF l_QUALIFIERS_rec.qualify_hier_descendent_flag = FND_API.G_MISS_CHAR THEN
879 l_QUALIFIERS_rec.qualify_hier_descendent_flag := p_old_QUALIFIERS_rec.qualify_hier_descendent_flag;
880 END IF;
881
882 RETURN l_QUALIFIERS_rec;
883
884 END Complete_Record;
885
886 -- Function Convert_Miss_To_Null
887
888 FUNCTION Convert_Miss_To_Null
889 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
890 ) RETURN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
891 IS
892 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type := p_QUALIFIERS_rec;
893 BEGIN
894
895 IF l_QUALIFIERS_rec.attribute1 = FND_API.G_MISS_CHAR THEN
896 l_QUALIFIERS_rec.attribute1 := NULL;
897 END IF;
898
899 IF l_QUALIFIERS_rec.attribute10 = FND_API.G_MISS_CHAR THEN
900 l_QUALIFIERS_rec.attribute10 := NULL;
901 END IF;
902
903 IF l_QUALIFIERS_rec.attribute11 = FND_API.G_MISS_CHAR THEN
904 l_QUALIFIERS_rec.attribute11 := NULL;
905 END IF;
906
907 IF l_QUALIFIERS_rec.attribute12 = FND_API.G_MISS_CHAR THEN
908 l_QUALIFIERS_rec.attribute12 := NULL;
909 END IF;
910
911 IF l_QUALIFIERS_rec.attribute13 = FND_API.G_MISS_CHAR THEN
912 l_QUALIFIERS_rec.attribute13 := NULL;
913 END IF;
914
915 IF l_QUALIFIERS_rec.attribute14 = FND_API.G_MISS_CHAR THEN
916 l_QUALIFIERS_rec.attribute14 := NULL;
917 END IF;
918
919 IF l_QUALIFIERS_rec.attribute15 = FND_API.G_MISS_CHAR THEN
920 l_QUALIFIERS_rec.attribute15 := NULL;
921 END IF;
922
923 IF l_QUALIFIERS_rec.attribute2 = FND_API.G_MISS_CHAR THEN
924 l_QUALIFIERS_rec.attribute2 := NULL;
925 END IF;
926
927 IF l_QUALIFIERS_rec.attribute3 = FND_API.G_MISS_CHAR THEN
928 l_QUALIFIERS_rec.attribute3 := NULL;
929 END IF;
930
931 IF l_QUALIFIERS_rec.attribute4 = FND_API.G_MISS_CHAR THEN
932 l_QUALIFIERS_rec.attribute4 := NULL;
933 END IF;
934
935 IF l_QUALIFIERS_rec.attribute5 = FND_API.G_MISS_CHAR THEN
936 l_QUALIFIERS_rec.attribute5 := NULL;
937 END IF;
938
939 IF l_QUALIFIERS_rec.attribute6 = FND_API.G_MISS_CHAR THEN
940 l_QUALIFIERS_rec.attribute6 := NULL;
941 END IF;
942
943 IF l_QUALIFIERS_rec.attribute7 = FND_API.G_MISS_CHAR THEN
944 l_QUALIFIERS_rec.attribute7 := NULL;
945 END IF;
946
947 IF l_QUALIFIERS_rec.attribute8 = FND_API.G_MISS_CHAR THEN
948 l_QUALIFIERS_rec.attribute8 := NULL;
949 END IF;
950
951 IF l_QUALIFIERS_rec.attribute9 = FND_API.G_MISS_CHAR THEN
952 l_QUALIFIERS_rec.attribute9 := NULL;
953 END IF;
954
955 IF l_QUALIFIERS_rec.comparison_operator_code = FND_API.G_MISS_CHAR THEN
956 l_QUALIFIERS_rec.comparison_operator_code := NULL;
957 END IF;
958
959 IF l_QUALIFIERS_rec.context = FND_API.G_MISS_CHAR THEN
960 l_QUALIFIERS_rec.context := NULL;
961 END IF;
962
963 IF l_QUALIFIERS_rec.created_by = FND_API.G_MISS_NUM THEN
964 l_QUALIFIERS_rec.created_by := NULL;
965 END IF;
966
967 IF l_QUALIFIERS_rec.created_from_rule_id = FND_API.G_MISS_NUM THEN
968 l_QUALIFIERS_rec.created_from_rule_id := NULL;
969 END IF;
970
971 IF l_QUALIFIERS_rec.creation_date = FND_API.G_MISS_DATE THEN
972 l_QUALIFIERS_rec.creation_date := NULL;
973 END IF;
974
975 IF l_QUALIFIERS_rec.end_date_active = FND_API.G_MISS_DATE THEN
976 l_QUALIFIERS_rec.end_date_active := NULL;
977 END IF;
978
979 IF l_QUALIFIERS_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
980 l_QUALIFIERS_rec.excluder_flag := NULL;
981 END IF;
982
983 IF l_QUALIFIERS_rec.last_updated_by = FND_API.G_MISS_NUM THEN
984 l_QUALIFIERS_rec.last_updated_by := NULL;
985 END IF;
986
987 IF l_QUALIFIERS_rec.last_update_date = FND_API.G_MISS_DATE THEN
988 l_QUALIFIERS_rec.last_update_date := NULL;
989 END IF;
990
991 IF l_QUALIFIERS_rec.last_update_login = FND_API.G_MISS_NUM THEN
992 l_QUALIFIERS_rec.last_update_login := NULL;
993 END IF;
994
995 IF l_QUALIFIERS_rec.list_header_id = FND_API.G_MISS_NUM THEN
996 l_QUALIFIERS_rec.list_header_id := NULL;
997 END IF;
998
999 IF l_QUALIFIERS_rec.list_line_id = FND_API.G_MISS_NUM THEN
1000 l_QUALIFIERS_rec.list_line_id := NULL;
1001 END IF;
1002
1003 IF l_QUALIFIERS_rec.program_application_id = FND_API.G_MISS_NUM THEN
1004 l_QUALIFIERS_rec.program_application_id := NULL;
1005 END IF;
1006
1007 IF l_QUALIFIERS_rec.program_id = FND_API.G_MISS_NUM THEN
1008 l_QUALIFIERS_rec.program_id := NULL;
1009 END IF;
1010
1011 IF l_QUALIFIERS_rec.program_update_date = FND_API.G_MISS_DATE THEN
1012 l_QUALIFIERS_rec.program_update_date := NULL;
1013 END IF;
1014
1015 IF l_QUALIFIERS_rec.qualifier_attribute = FND_API.G_MISS_CHAR THEN
1016 l_QUALIFIERS_rec.qualifier_attribute := NULL;
1017 END IF;
1018
1019 IF l_QUALIFIERS_rec.qualifier_attr_value = FND_API.G_MISS_CHAR THEN
1020 l_QUALIFIERS_rec.qualifier_attr_value := NULL;
1021 END IF;
1022
1023 IF l_QUALIFIERS_rec.qualifier_attr_value_to = FND_API.G_MISS_CHAR THEN
1024 l_QUALIFIERS_rec.qualifier_attr_value_to := NULL;
1025 END IF;
1026
1027 IF l_QUALIFIERS_rec.qualifier_context = FND_API.G_MISS_CHAR THEN
1028 l_QUALIFIERS_rec.qualifier_context := NULL;
1029 END IF;
1030
1031 IF l_QUALIFIERS_rec.qualifier_datatype = FND_API.G_MISS_CHAR THEN
1032 l_QUALIFIERS_rec.qualifier_datatype := NULL;
1033 END IF;
1034
1035 /*IF l_QUALIFIERS_rec.qualifier_date_format = FND_API.G_MISS_CHAR THEN
1036 l_QUALIFIERS_rec.qualifier_date_format := NULL;
1037 END IF;*/
1038
1039 IF l_QUALIFIERS_rec.qualifier_grouping_no = FND_API.G_MISS_NUM THEN
1040 l_QUALIFIERS_rec.qualifier_grouping_no := NULL;
1041 END IF;
1042
1043 IF l_QUALIFIERS_rec.qualifier_id = FND_API.G_MISS_NUM THEN
1044 l_QUALIFIERS_rec.qualifier_id := NULL;
1045 END IF;
1046
1047 /*IF l_QUALIFIERS_rec.qualifier_number_format = FND_API.G_MISS_CHAR THEN
1048 l_QUALIFIERS_rec.qualifier_number_format := NULL;
1049 END IF;*/
1050
1051 IF l_QUALIFIERS_rec.qualifier_precedence = FND_API.G_MISS_NUM THEN
1052 l_QUALIFIERS_rec.qualifier_precedence := NULL;
1053 END IF;
1054
1055 IF l_QUALIFIERS_rec.qualifier_rule_id = FND_API.G_MISS_NUM THEN
1056 l_QUALIFIERS_rec.qualifier_rule_id := NULL;
1057 END IF;
1058
1059 IF l_QUALIFIERS_rec.request_id = FND_API.G_MISS_NUM THEN
1060 l_QUALIFIERS_rec.request_id := NULL;
1061 END IF;
1062
1063 IF l_QUALIFIERS_rec.start_date_active = FND_API.G_MISS_DATE THEN
1064 l_QUALIFIERS_rec.start_date_active := NULL;
1065 END IF;
1066 -- Added for TCA
1067 IF l_QUALIFIERS_rec.qualify_hier_descendent_flag = FND_API.G_MISS_CHAR THEN
1068 l_QUALIFIERS_rec.qualify_hier_descendent_flag := NULL;
1069 END IF;
1070
1071 RETURN l_QUALIFIERS_rec;
1072
1073 END Convert_Miss_To_Null;
1074
1075 -- Procedure Update_Row
1076
1077 PROCEDURE Update_Row
1078 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
1079 )
1080 IS
1081 l_check_active_flag VARCHAR2(1);
1082 l_qual_attr_value_from_number NUMBER := NULL;
1083 l_qual_attr_value_to_number NUMBER := NULL;
1084 l_qual_attr_value_from VARCHAR2(240);
1085 --l_status VARCHAR2(1);
1086 --l_qualifiers_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
1087 BEGIN
1088
1089 -- l_qualifiers_rec:=Query_Row(p_QUALIFIERS_rec.qualifier_id);
1090
1091 BEGIN
1092 IF p_QUALIFIERS_rec.qualifier_datatype = 'N'
1093 then
1094 l_qual_attr_value_from_number :=
1095 qp_number.canonical_to_number(p_QUALIFIERS_rec.qualifier_attr_value);
1096
1097 l_qual_attr_value_to_number :=
1098 qp_number.canonical_to_number(p_QUALIFIERS_rec.qualifier_attr_value_to);
1099
1100 l_qual_attr_value_from :=
1101 qp_number.number_to_canonical(l_qual_attr_value_from_number); --4418053
1102 ELSE
1103
1104 l_qual_attr_value_from := p_QUALIFIERS_rec.qualifier_attr_value; --4418053
1105
1106 end if;
1107
1108 EXCEPTION
1109 WHEN VALUE_ERROR THEN
1110 NULL;
1111 WHEN OTHERS THEN
1112 NULL;
1113 END;
1114
1115 UPDATE QP_QUALIFIERS
1116 SET ATTRIBUTE1 = p_QUALIFIERS_rec.attribute1
1117 , ATTRIBUTE10 = p_QUALIFIERS_rec.attribute10
1118 , ATTRIBUTE11 = p_QUALIFIERS_rec.attribute11
1119 , ATTRIBUTE12 = p_QUALIFIERS_rec.attribute12
1120 , ATTRIBUTE13 = p_QUALIFIERS_rec.attribute13
1121 , ATTRIBUTE14 = p_QUALIFIERS_rec.attribute14
1122 , ATTRIBUTE15 = p_QUALIFIERS_rec.attribute15
1123 , ATTRIBUTE2 = p_QUALIFIERS_rec.attribute2
1124 , ATTRIBUTE3 = p_QUALIFIERS_rec.attribute3
1125 , ATTRIBUTE4 = p_QUALIFIERS_rec.attribute4
1126 , ATTRIBUTE5 = p_QUALIFIERS_rec.attribute5
1127 , ATTRIBUTE6 = p_QUALIFIERS_rec.attribute6
1128 , ATTRIBUTE7 = p_QUALIFIERS_rec.attribute7
1129 , ATTRIBUTE8 = p_QUALIFIERS_rec.attribute8
1130 , ATTRIBUTE9 = p_QUALIFIERS_rec.attribute9
1131 , COMPARISON_OPERATOR_CODE = p_QUALIFIERS_rec.comparison_operator_code
1132 , CONTEXT = p_QUALIFIERS_rec.context
1133 , CREATED_BY = p_QUALIFIERS_rec.created_by
1134 , CREATED_FROM_RULE_ID = p_QUALIFIERS_rec.created_from_rule_id
1135 , CREATION_DATE = p_QUALIFIERS_rec.creation_date
1136 , END_DATE_ACTIVE = p_QUALIFIERS_rec.end_date_active
1137 , EXCLUDER_FLAG = p_QUALIFIERS_rec.excluder_flag
1138 , LAST_UPDATED_BY = p_QUALIFIERS_rec.last_updated_by
1139 , LAST_UPDATE_DATE = p_QUALIFIERS_rec.last_update_date
1140 , LAST_UPDATE_LOGIN = p_QUALIFIERS_rec.last_update_login
1141 , LIST_HEADER_ID = p_QUALIFIERS_rec.list_header_id
1142 , LIST_LINE_ID = p_QUALIFIERS_rec.list_line_id
1143 , PROGRAM_APPLICATION_ID = p_QUALIFIERS_rec.program_application_id
1144 , PROGRAM_ID = p_QUALIFIERS_rec.program_id
1145 , PROGRAM_UPDATE_DATE = p_QUALIFIERS_rec.program_update_date
1146 , QUALIFIER_ATTRIBUTE = p_QUALIFIERS_rec.qualifier_attribute
1147 , QUALIFIER_ATTR_VALUE = l_qual_attr_value_from
1148 , QUALIFIER_ATTR_VALUE_TO = p_QUALIFIERS_rec.qualifier_attr_value_to
1149 , QUALIFIER_CONTEXT = p_QUALIFIERS_rec.qualifier_context
1150 , QUALIFIER_DATATYPE = p_QUALIFIERS_rec.qualifier_datatype
1151 -- , QUALIFIER_DATE_FORMAT = p_QUALIFIERS_rec.qualifier_date_format
1152 , QUALIFIER_GROUPING_NO = p_QUALIFIERS_rec.qualifier_grouping_no
1153 , QUALIFIER_ID = p_QUALIFIERS_rec.qualifier_id
1154 --, QUALIFIER_NUMBER_FORMAT = p_QUALIFIERS_rec.qualifier_number_format
1155 , QUALIFIER_PRECEDENCE = p_QUALIFIERS_rec.qualifier_precedence
1156 , QUALIFIER_RULE_ID = p_QUALIFIERS_rec.qualifier_rule_id
1157 , REQUEST_ID = p_QUALIFIERS_rec.request_id
1158 , START_DATE_ACTIVE = p_QUALIFIERS_rec.start_date_active
1159 , QUAL_ATTR_VALUE_FROM_NUMBER = l_qual_attr_value_from_number
1160 , QUAL_ATTR_VALUE_TO_NUMBER = l_qual_attr_value_to_number
1161 , QUALIFY_HIER_DESCENDENTS_FLAG = p_QUALIFIERS_rec.qualify_hier_descendent_flag -- Added for TCA
1162 WHERE QUALIFIER_ID = p_QUALIFIERS_rec.qualifier_id
1163 ;
1164
1165
1166
1167 l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
1168
1169 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND p_QUALIFIERS_rec.active_flag='Y') THEN
1170
1171 IF(p_QUALIFIERS_rec.qualifier_context IS NOT NULL) AND
1172 (p_QUALIFIERS_rec.qualifier_attribute IS NOT NULL) THEN
1173
1174 UPDATE qp_pte_segments SET used_in_setup='Y'
1175 WHERE nvl(used_in_setup,'N')='N'
1176 AND segment_id IN
1177 (SELECT a.segment_id
1178 FROM qp_segments_b a,qp_prc_contexts_b b
1179 WHERE a.segment_mapping_column=p_QUALIFIERS_rec.qualifier_attribute
1180 AND a.prc_context_id=b.prc_context_id
1181 AND b.prc_context_type='QUALIFIER'
1182 AND b.prc_context_code=p_QUALIFIERS_rec.qualifier_context);
1183 END IF;
1184 END IF;
1185
1186 EXCEPTION
1187
1188 WHEN OTHERS THEN
1189
1190 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1191 THEN
1192 OE_MSG_PUB.Add_Exc_Msg
1193 ( G_PKG_NAME
1194 , 'Update_Row'
1195 );
1196 END IF;
1197
1198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1199
1200 END Update_Row;
1201
1202 -- Procedure Update_coupon_Row Added for bug 7316016
1203
1204 PROCEDURE Update_coupon_Row
1205 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
1206 )
1207 IS
1208 BEGIN
1209 UPDATE QP_COUPONS
1210 SET expiration_date=p_QUALIFIERS_rec.end_date_active
1211 ,start_date=p_QUALIFIERS_rec.start_date_active
1212 ,LAST_UPDATED_BY=p_QUALIFIERS_rec.LAST_UPDATED_BY
1213 ,LAST_UPDATE_DATE=p_QUALIFIERS_rec.LAST_UPDATE_DATE
1214 ,LAST_UPDATE_LOGIN=p_QUALIFIERS_rec.LAST_UPDATE_LOGIN
1215 WHERE coupon_id = p_QUALIFIERS_rec.qualifier_attr_value;
1216 EXCEPTION
1217
1218 WHEN OTHERS THEN
1219
1220 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1221 THEN
1222 FND_MSG_PUB.Add_Exc_Msg
1223 ( G_PKG_NAME
1224 , 'Update_Coupon_Row'
1225 );
1226 END IF;
1227
1228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1229 END Update_Coupon_Row;
1230
1231 -- Procedure Insert_Row
1232
1233 PROCEDURE Insert_Row
1234 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
1235 )
1236 IS
1237 l_list_type_code VARCHAR2(30) := '';
1238 l_active_flag VARCHAR2(1) := '';
1239 l_qualifier_grouping_no NUMBER;
1240 l_qual_attr_value_from_number NUMBER := NULL;
1241 l_qual_attr_value_to_number NUMBER := NULL;
1242 l_check_active_flag VARCHAR2(1);
1243 l_qual_attr_value_from VARCHAR2(240);
1244
1245
1246
1247 cursor update_pte_cur(l_qual_context varchar2,l_qual_attribute varchar2) is
1248 SELECT a.segment_id
1249 FROM qp_segments_b a,qp_prc_contexts_b b
1250 WHERE a.segment_mapping_column=l_qual_attribute
1251 AND a.prc_context_id=b.prc_context_id
1252 AND b.prc_context_type='QUALIFIER'
1253 AND b.prc_context_code=l_qual_context;
1254
1255
1256 BEGIN
1257
1258 l_qualifier_grouping_no := p_QUALIFIERS_rec.qualifier_grouping_no;
1259
1260 IF p_QUALIFIERS_rec.qualifier_context = 'MODLIST' AND
1261 p_QUALIFIERS_rec.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4' AND
1262 p_QUALIFIERS_rec.qualifier_grouping_no <> -1
1263 THEN
1264 BEGIN
1265 SELECT list_type_code
1266 INTO l_list_type_code
1267 FROM qp_list_headers_vl
1268 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
1269 EXCEPTION
1270 WHEN OTHERS THEN
1271 NULL;
1272 END;
1273
1274 IF l_list_type_code IN ('PRL', 'AGR') THEN
1275 l_qualifier_grouping_no := -1;
1276 END IF;
1277 END IF;
1278
1279 BEGIN
1280
1281 SELECT ACTIVE_FLAG, LIST_TYPE_CODE
1282 INTO l_active_flag, l_list_type_code
1283 FROM QP_LIST_HEADERS_B
1284 WHERE LIST_HEADER_ID = p_QUALIFIERS_rec.list_header_id;
1285
1286 EXCEPTION
1287 WHEN OTHERS THEN
1288 NULL;
1289 END;
1290
1291
1292 BEGIN
1293
1294 IF p_QUALIFIERS_rec.qualifier_datatype = 'N'
1295 then
1296 l_qual_attr_value_from_number :=
1297 qp_number.canonical_to_number(p_QUALIFIERS_rec.qualifier_attr_value);
1298
1299 l_qual_attr_value_to_number :=
1300 qp_number.canonical_to_number(p_QUALIFIERS_rec.qualifier_attr_value_to);
1301
1302 l_qual_attr_value_from :=
1303 qp_number.number_to_canonical(l_qual_attr_value_from_number); --4418053
1304 ELSE
1305
1306 l_qual_attr_value_from := p_QUALIFIERS_rec.qualifier_attr_value; --4418053
1307
1308 end if;
1309
1310 EXCEPTION
1311 WHEN VALUE_ERROR THEN
1312 NULL;
1313 WHEN OTHERS THEN
1314 NULL;
1315
1316 END;
1317
1318 INSERT INTO QP_QUALIFIERS
1319 ( ATTRIBUTE1
1320 , ATTRIBUTE10
1321 , ATTRIBUTE11
1322 , ATTRIBUTE12
1323 , ATTRIBUTE13
1324 , ATTRIBUTE14
1325 , ATTRIBUTE15
1326 , ATTRIBUTE2
1327 , ATTRIBUTE3
1328 , ATTRIBUTE4
1329 , ATTRIBUTE5
1330 , ATTRIBUTE6
1331 , ATTRIBUTE7
1332 , ATTRIBUTE8
1333 , ATTRIBUTE9
1334 , COMPARISON_OPERATOR_CODE
1335 , CONTEXT
1336 , CREATED_BY
1337 , CREATED_FROM_RULE_ID
1338 , CREATION_DATE
1339 , END_DATE_ACTIVE
1340 , EXCLUDER_FLAG
1341 , LAST_UPDATED_BY
1342 , LAST_UPDATE_DATE
1343 , LAST_UPDATE_LOGIN
1344 , LIST_HEADER_ID
1345 , LIST_LINE_ID
1346 , PROGRAM_APPLICATION_ID
1347 , PROGRAM_ID
1348 , PROGRAM_UPDATE_DATE
1349 , QUALIFIER_ATTRIBUTE
1350 , QUALIFIER_ATTR_VALUE
1351 , QUALIFIER_ATTR_VALUE_TO
1352 , QUALIFIER_CONTEXT
1353 , QUALIFIER_DATATYPE
1354 --, QUALIFIER_DATE_FORMAT
1355 , QUALIFIER_GROUPING_NO
1356 , QUALIFIER_ID
1357 --, QUALIFIER_NUMBER_FORMAT
1358 , QUALIFIER_PRECEDENCE
1359 , QUALIFIER_RULE_ID
1360 , REQUEST_ID
1361 , START_DATE_ACTIVE
1362 , ACTIVE_FLAG
1363 , LIST_TYPE_CODE
1364 , QUAL_ATTR_VALUE_FROM_NUMBER
1365 , QUAL_ATTR_VALUE_TO_NUMBER
1366 , SEARCH_IND
1367 , QUALIFIER_GROUP_CNT
1368 , HEADER_QUALS_EXIST_FLAG
1369 , DISTINCT_ROW_COUNT
1370 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1371 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1372 ,ORIG_SYS_QUALIFIER_REF
1373 ,ORIG_SYS_LINE_REF
1374 ,ORIG_SYS_HEADER_REF
1375 )
1376 VALUES
1377 ( p_QUALIFIERS_rec.attribute1
1378 , p_QUALIFIERS_rec.attribute10
1379 , p_QUALIFIERS_rec.attribute11
1380 , p_QUALIFIERS_rec.attribute12
1381 , p_QUALIFIERS_rec.attribute13
1382 , p_QUALIFIERS_rec.attribute14
1383 , p_QUALIFIERS_rec.attribute15
1384 , p_QUALIFIERS_rec.attribute2
1385 , p_QUALIFIERS_rec.attribute3
1386 , p_QUALIFIERS_rec.attribute4
1387 , p_QUALIFIERS_rec.attribute5
1388 , p_QUALIFIERS_rec.attribute6
1389 , p_QUALIFIERS_rec.attribute7
1390 , p_QUALIFIERS_rec.attribute8
1391 , p_QUALIFIERS_rec.attribute9
1392 , p_QUALIFIERS_rec.comparison_operator_code
1393 , p_QUALIFIERS_rec.context
1394 , p_QUALIFIERS_rec.created_by
1395 , p_QUALIFIERS_rec.created_from_rule_id
1396 , p_QUALIFIERS_rec.creation_date
1397 , p_QUALIFIERS_rec.end_date_active
1398 , p_QUALIFIERS_rec.excluder_flag
1399 , p_QUALIFIERS_rec.last_updated_by
1400 , p_QUALIFIERS_rec.last_update_date
1401 , p_QUALIFIERS_rec.last_update_login
1402 , p_QUALIFIERS_rec.list_header_id
1403 , p_QUALIFIERS_rec.list_line_id
1404 , p_QUALIFIERS_rec.program_application_id
1405 , p_QUALIFIERS_rec.program_id
1406 , p_QUALIFIERS_rec.program_update_date
1407 , p_QUALIFIERS_rec.qualifier_attribute
1408 , l_qual_attr_value_from
1409 , p_QUALIFIERS_rec.qualifier_attr_value_to
1410 , p_QUALIFIERS_rec.qualifier_context
1411 , p_QUALIFIERS_rec.qualifier_datatype
1412 --, p_QUALIFIERS_rec.qualifier_date_format
1413 , l_qualifier_grouping_no
1414 , p_QUALIFIERS_rec.qualifier_id
1415 --, p_QUALIFIERS_rec.qualifier_number_format
1416 , p_QUALIFIERS_rec.qualifier_precedence
1417 , p_QUALIFIERS_rec.qualifier_rule_id
1418 , p_QUALIFIERS_rec.request_id
1419 , p_QUALIFIERS_rec.start_date_active
1420 , l_active_flag
1421 , l_list_type_code
1422 , l_qual_attr_value_from_number
1423 , l_qual_attr_value_to_number
1424 , p_QUALIFIERS_rec.search_ind
1425 , p_QUALIFIERS_rec.qualifier_group_cnt
1426 , p_QUALIFIERS_rec.header_quals_exist_flag
1427 , p_QUALIFIERS_rec.distinct_row_count
1428 , p_QUALIFIERS_rec.qualify_hier_descendent_flag -- Added for TCA
1429 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1430 ,to_char(p_QUALIFIERS_rec.qualifier_id)
1431 ,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_QUALIFIERS_rec.list_line_id)
1432 ,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_QUALIFIERS_rec.list_header_id)
1433 );
1434
1435 l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
1436 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND l_active_flag='Y') THEN
1437 IF(p_QUALIFIERS_rec.qualifier_context IS NOT NULL) AND
1438 (p_QUALIFIERS_rec.qualifier_attribute IS NOT NULL) THEN
1439 /* USed the cursor update_pte_cur instead of a nested subquery for the update statement
1440 * for the bug 3544961*/
1441
1442
1443 for i in update_pte_cur(p_QUALIFIERS_rec.qualifier_context, p_QUALIFIERS_rec.qualifier_attribute) loop
1444
1445 update qp_pte_segments set used_in_setup='Y'
1446 where nvl(used_in_Setup,'N')='N'
1447 and segment_id = i.segment_id;
1448
1449
1450 end loop;
1451
1452 END IF;
1453 END IF;
1454 /*l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
1455 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND l_active_flag='Y') THEN
1456 IF(p_QUALIFIERS_rec.qualifier_context IS NOT NULL) AND
1457 (p_QUALIFIERS_rec.qualifier_attribute IS NOT NULL) THEN
1458
1459 UPDATE qp_pte_segments SET used_in_setup='Y'
1460 WHERE nvl(used_in_setup,'N')='N'
1461 AND segment_id IN
1462 (SELECT a.segment_id
1463 FROM qp_segments_b a,qp_prc_contexts_b b
1464 WHERE a.segment_mapping_column=p_QUALIFIERS_rec.qualifier_attribute
1465 AND a.prc_context_id=b.prc_context_id AND b.prc_context_type='QUALIFIER'
1466 AND b.prc_context_code=p_QUALIFIERS_rec.qualifier_context);
1467 END IF;
1468 END IF;
1469 */
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472
1473 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1474 THEN
1475 OE_MSG_PUB.Add_Exc_Msg
1476 ( G_PKG_NAME
1477 , 'Insert_Row'
1478 );
1479 END IF;
1480
1481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1482
1483 END Insert_Row;
1484
1485 --This procedure will be used by HTML Qualifier UI
1486 --to insert qualifiers into dummy table for updates
1487 PROCEDURE Insert_Row(p_qual_grp_no IN NUMBER,
1488 p_list_header_id IN NUMBER,
1489 p_list_line_id IN NUMBER,
1490 p_transaction_id IN NUMBER) IS
1491 PRAGMA AUTONOMOUS_TRANSACTION;
1492 BEGIN
1493
1494 insert into qp_qualifiers_fwk_dummy
1495 ( ATTRIBUTE1
1496 , ATTRIBUTE10
1497 , ATTRIBUTE11
1498 , ATTRIBUTE12
1499 , ATTRIBUTE13
1500 , ATTRIBUTE14
1501 , ATTRIBUTE15
1502 , ATTRIBUTE2
1503 , ATTRIBUTE3
1504 , ATTRIBUTE4
1505 , ATTRIBUTE5
1506 , ATTRIBUTE6
1507 , ATTRIBUTE7
1508 , ATTRIBUTE8
1509 , ATTRIBUTE9
1510 , COMPARISON_OPERATOR_CODE
1511 , CONTEXT
1512 , CREATED_BY
1513 , CREATED_FROM_RULE_ID
1514 , CREATION_DATE
1515 , END_DATE_ACTIVE
1516 , EXCLUDER_FLAG
1517 , LAST_UPDATED_BY
1518 , LAST_UPDATE_DATE
1519 , LAST_UPDATE_LOGIN
1520 , LIST_HEADER_ID
1521 , LIST_LINE_ID
1522 , PROGRAM_APPLICATION_ID
1523 , PROGRAM_ID
1524 , PROGRAM_UPDATE_DATE
1525 , QUALIFIER_ATTRIBUTE
1526 , QUALIFIER_ATTR_VALUE
1527 , QUALIFIER_ATTR_VALUE_TO
1528 , QUALIFIER_CONTEXT
1529 , QUALIFIER_DATATYPE
1530 --, QUALIFIER_DATE_FORMAT
1531 , QUALIFIER_GROUPING_NO
1532 , QUALIFIER_ID
1533 --, QUALIFIER_NUMBER_FORMAT
1534 , QUALIFIER_PRECEDENCE
1535 , QUALIFIER_RULE_ID
1536 , REQUEST_ID
1537 , START_DATE_ACTIVE
1538 , ACTIVE_FLAG
1539 , LIST_TYPE_CODE
1540 , QUAL_ATTR_VALUE_FROM_NUMBER
1541 , QUAL_ATTR_VALUE_TO_NUMBER
1542 , SEARCH_IND
1543 , QUALIFIER_GROUP_CNT
1544 , HEADER_QUALS_EXIST_FLAG
1545 , DISTINCT_ROW_COUNT
1546 , TRANSACTION_ID
1547 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1548 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1549 ,ORIG_SYS_QUALIFIER_REF
1550 ,ORIG_SYS_LINE_REF
1551 ,ORIG_SYS_HEADER_REF
1552 )
1553 select
1554 ATTRIBUTE1
1555 , ATTRIBUTE10
1556 , ATTRIBUTE11
1557 , ATTRIBUTE12
1558 , ATTRIBUTE13
1559 , ATTRIBUTE14
1560 , ATTRIBUTE15
1561 , ATTRIBUTE2
1562 , ATTRIBUTE3
1563 , ATTRIBUTE4
1564 , ATTRIBUTE5
1565 , ATTRIBUTE6
1566 , ATTRIBUTE7
1567 , ATTRIBUTE8
1568 , ATTRIBUTE9
1569 , COMPARISON_OPERATOR_CODE
1570 , CONTEXT
1571 , CREATED_BY
1572 , CREATED_FROM_RULE_ID
1573 , CREATION_DATE
1574 , END_DATE_ACTIVE
1575 , EXCLUDER_FLAG
1576 , LAST_UPDATED_BY
1577 , sysdate
1578 , LAST_UPDATE_LOGIN
1579 , LIST_HEADER_ID
1580 , LIST_LINE_ID
1581 , PROGRAM_APPLICATION_ID
1582 , PROGRAM_ID
1583 , PROGRAM_UPDATE_DATE
1584 , QUALIFIER_ATTRIBUTE
1585 , QUALIFIER_ATTR_VALUE
1586 , QUALIFIER_ATTR_VALUE_TO
1587 , QUALIFIER_CONTEXT
1588 , QUALIFIER_DATATYPE
1589 --, QUALIFIER_DATE_FORMAT
1590 , QUALIFIER_GROUPING_NO
1591 , QUALIFIER_ID
1592 --, QUALIFIER_NUMBER_FORMAT
1593 , QUALIFIER_PRECEDENCE
1594 , QUALIFIER_RULE_ID
1595 , REQUEST_ID
1596 , START_DATE_ACTIVE
1597 , ACTIVE_FLAG
1598 , LIST_TYPE_CODE
1599 , QUAL_ATTR_VALUE_FROM_NUMBER
1600 , QUAL_ATTR_VALUE_TO_NUMBER
1601 , SEARCH_IND
1602 , QUALIFIER_GROUP_CNT
1603 , 'Q'
1604 , DISTINCT_ROW_COUNT
1605 , p_transaction_id
1606 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1607 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1608 ,to_char(qualifier_id)
1609 ,nvl(ORIG_SYS_LINE_REF,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id))
1610 ,nvl(ORIG_SYS_HEADER_REF,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id))
1611 from qp_qualifiers qual
1612 where list_header_id = p_list_header_id
1613 and list_line_id = p_list_line_id
1614 and ((p_qual_grp_no = -1
1615 and qualifier_grouping_no = p_qual_grp_no)
1616 or (p_qual_grp_no <> -1
1617 and (qualifier_grouping_no = -1
1618 or qualifier_grouping_no = p_qual_grp_no)))
1619 and ((qual.list_type_code = 'PRL'
1620 and not (qual.qualifier_context = 'MODLIST' and qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'))
1621 or (qual.list_type_code <> 'PRL'))
1622 and not exists (select 'Y' from qp_qualifiers_fwk_dummy dummy
1623 where dummy.qualifier_id = qual.qualifier_id
1624 and dummy.transaction_id = p_transaction_id);
1625
1626 --AUTONOMOUS commit
1627 commit;
1628 EXCEPTION
1629 When OTHERS Then
1630 null;
1631 END Insert_Row;
1632
1633 --This procedure will be used by HTML Qualifier UI
1634 --to delete rows from dummy table
1635 Procedure Delete_Dummy_Rows(p_transaction_id IN NUMBER) IS
1636 PRAGMA AUTONOMOUS_TRANSACTION;
1637 BEGIN
1638 delete from qp_qualifiers_fwk_dummy where transaction_id = p_transaction_id
1639 or last_update_date < sysdate-5;
1640 -- where list_header_id = p_list_header_id
1641 -- and list_line_id = p_list_line_id;
1642
1643 --AUTONOMOUS commit
1644 commit;
1645 EXCEPTION
1646 When OTHERS Then
1647 null;
1648 END Delete_Dummy_Rows;
1649
1650 --This procedure will mark given qualifier as DELETED
1651 Procedure Mark_Delete_Dummy_Qual(p_qual_id IN NUMBER
1652 ,p_mode IN VARCHAR2
1653 ,p_transaction_id IN NUMBER) IS
1654 PRAGMA AUTONOMOUS_TRANSACTION;
1655 BEGIN
1656 update qp_qualifiers_fwk_dummy set header_quals_exist_flag = decode(p_mode, 'HGRID', 'X', 'D'),
1657 last_update_date = sysdate
1658 where qualifier_id = p_qual_id
1659 and transaction_id = p_transaction_id;
1660
1661 IF SQL%ROWCOUNT < 1 THEN
1662 insert into qp_qualifiers_fwk_dummy
1663 ( ATTRIBUTE1
1664 , ATTRIBUTE10
1665 , ATTRIBUTE11
1666 , ATTRIBUTE12
1667 , ATTRIBUTE13
1668 , ATTRIBUTE14
1669 , ATTRIBUTE15
1670 , ATTRIBUTE2
1671 , ATTRIBUTE3
1672 , ATTRIBUTE4
1673 , ATTRIBUTE5
1674 , ATTRIBUTE6
1675 , ATTRIBUTE7
1676 , ATTRIBUTE8
1677 , ATTRIBUTE9
1678 , COMPARISON_OPERATOR_CODE
1679 , CONTEXT
1680 , CREATED_BY
1681 , CREATED_FROM_RULE_ID
1682 , CREATION_DATE
1683 , END_DATE_ACTIVE
1684 , EXCLUDER_FLAG
1685 , LAST_UPDATED_BY
1686 , LAST_UPDATE_DATE
1687 , LAST_UPDATE_LOGIN
1688 , LIST_HEADER_ID
1689 , LIST_LINE_ID
1690 , PROGRAM_APPLICATION_ID
1691 , PROGRAM_ID
1692 , PROGRAM_UPDATE_DATE
1693 , QUALIFIER_ATTRIBUTE
1694 , QUALIFIER_ATTR_VALUE
1695 , QUALIFIER_ATTR_VALUE_TO
1696 , QUALIFIER_CONTEXT
1697 , QUALIFIER_DATATYPE
1698 --, QUALIFIER_DATE_FORMAT
1699 , QUALIFIER_GROUPING_NO
1700 , QUALIFIER_ID
1701 --, QUALIFIER_NUMBER_FORMAT
1702 , QUALIFIER_PRECEDENCE
1703 , QUALIFIER_RULE_ID
1704 , REQUEST_ID
1705 , START_DATE_ACTIVE
1706 , ACTIVE_FLAG
1707 , LIST_TYPE_CODE
1708 , QUAL_ATTR_VALUE_FROM_NUMBER
1709 , QUAL_ATTR_VALUE_TO_NUMBER
1710 , SEARCH_IND
1711 , QUALIFIER_GROUP_CNT
1712 , HEADER_QUALS_EXIST_FLAG
1713 , DISTINCT_ROW_COUNT
1714 , TRANSACTION_ID
1715 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1716 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1717 ,ORIG_SYS_QUALIFIER_REF
1718 ,ORIG_SYS_LINE_REF
1719 ,ORIG_SYS_HEADER_REF
1720 )
1721 select
1722 ATTRIBUTE1
1723 , ATTRIBUTE10
1724 , ATTRIBUTE11
1725 , ATTRIBUTE12
1726 , ATTRIBUTE13
1727 , ATTRIBUTE14
1728 , ATTRIBUTE15
1729 , ATTRIBUTE2
1730 , ATTRIBUTE3
1731 , ATTRIBUTE4
1732 , ATTRIBUTE5
1733 , ATTRIBUTE6
1734 , ATTRIBUTE7
1735 , ATTRIBUTE8
1736 , ATTRIBUTE9
1737 , COMPARISON_OPERATOR_CODE
1738 , CONTEXT
1739 , CREATED_BY
1740 , CREATED_FROM_RULE_ID
1741 , CREATION_DATE
1742 , END_DATE_ACTIVE
1743 , EXCLUDER_FLAG
1744 , LAST_UPDATED_BY
1745 , sysdate
1746 , LAST_UPDATE_LOGIN
1747 , LIST_HEADER_ID
1748 , LIST_LINE_ID
1749 , PROGRAM_APPLICATION_ID
1750 , PROGRAM_ID
1751 , PROGRAM_UPDATE_DATE
1752 , QUALIFIER_ATTRIBUTE
1753 , QUALIFIER_ATTR_VALUE
1754 , QUALIFIER_ATTR_VALUE_TO
1755 , QUALIFIER_CONTEXT
1756 , QUALIFIER_DATATYPE
1757 --, QUALIFIER_DATE_FORMAT
1758 , QUALIFIER_GROUPING_NO
1759 , QUALIFIER_ID
1760 --, QUALIFIER_NUMBER_FORMAT
1761 , QUALIFIER_PRECEDENCE
1762 , QUALIFIER_RULE_ID
1763 , REQUEST_ID
1764 , START_DATE_ACTIVE
1765 , ACTIVE_FLAG
1766 , LIST_TYPE_CODE
1767 , QUAL_ATTR_VALUE_FROM_NUMBER
1768 , QUAL_ATTR_VALUE_TO_NUMBER
1769 , SEARCH_IND
1770 , QUALIFIER_GROUP_CNT
1771 , decode(p_mode, 'HGRID', 'X', 'D')
1772 , DISTINCT_ROW_COUNT
1773 , p_transaction_id
1774 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1775 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1776 ,to_char(qual.qualifier_id)
1777 ,nvl(qual.ORIG_SYS_LINE_REF,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=qual.list_line_id))
1778 ,nvl(qual.ORIG_SYS_HEADER_REF,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=qual.list_header_id))
1779 from qp_qualifiers qual
1780 where qualifier_id = p_qual_id;
1781 END IF;--SQL%ROWCOUNT
1782 --AUTONOMOUS commit
1783 commit;
1784 EXCEPTION
1785 WHEN OTHERS THEN
1786 null;
1787 END Mark_Delete_Dummy_Qual;
1788
1789 --This procedure will mark given qualifiergroup as DELETED
1790 Procedure Mark_Delete_Dummy_Qual(p_qual_grp_no IN NUMBER,
1791 p_list_header_id IN NUMBER,
1792 p_list_line_id IN NUMBER,
1793 p_transaction_id IN NUMBER) IS
1794 PRAGMA AUTONOMOUS_TRANSACTION;
1795 BEGIN
1796 update qp_qualifiers_fwk_dummy set header_quals_exist_flag = 'X'
1797 ,last_update_date = sysdate
1798 where qualifier_grouping_no = p_qual_grp_no
1799 and list_header_id = p_list_header_id
1800 and list_line_id = p_list_line_id
1801 and ((list_type_code = 'PRL'
1802 and not (qualifier_context = 'MODLIST' and qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'))
1803 or (list_type_code <> 'PRL'))
1804 and transaction_id = p_transaction_id;
1805
1806 IF SQL%ROWCOUNT < 1 THEN
1807 insert into qp_qualifiers_fwk_dummy
1808 ( ATTRIBUTE1
1809 , ATTRIBUTE10
1810 , ATTRIBUTE11
1811 , ATTRIBUTE12
1812 , ATTRIBUTE13
1813 , ATTRIBUTE14
1814 , ATTRIBUTE15
1815 , ATTRIBUTE2
1816 , ATTRIBUTE3
1817 , ATTRIBUTE4
1818 , ATTRIBUTE5
1819 , ATTRIBUTE6
1820 , ATTRIBUTE7
1821 , ATTRIBUTE8
1822 , ATTRIBUTE9
1823 , COMPARISON_OPERATOR_CODE
1824 , CONTEXT
1825 , CREATED_BY
1826 , CREATED_FROM_RULE_ID
1827 , CREATION_DATE
1828 , END_DATE_ACTIVE
1829 , EXCLUDER_FLAG
1830 , LAST_UPDATED_BY
1831 , LAST_UPDATE_DATE
1832 , LAST_UPDATE_LOGIN
1833 , LIST_HEADER_ID
1834 , LIST_LINE_ID
1835 , PROGRAM_APPLICATION_ID
1836 , PROGRAM_ID
1837 , PROGRAM_UPDATE_DATE
1838 , QUALIFIER_ATTRIBUTE
1839 , QUALIFIER_ATTR_VALUE
1840 , QUALIFIER_ATTR_VALUE_TO
1841 , QUALIFIER_CONTEXT
1842 , QUALIFIER_DATATYPE
1843 --, QUALIFIER_DATE_FORMAT
1844 , QUALIFIER_GROUPING_NO
1845 , QUALIFIER_ID
1846 --, QUALIFIER_NUMBER_FORMAT
1847 , QUALIFIER_PRECEDENCE
1848 , QUALIFIER_RULE_ID
1849 , REQUEST_ID
1850 , START_DATE_ACTIVE
1851 , ACTIVE_FLAG
1852 , LIST_TYPE_CODE
1853 , QUAL_ATTR_VALUE_FROM_NUMBER
1854 , QUAL_ATTR_VALUE_TO_NUMBER
1855 , SEARCH_IND
1856 , QUALIFIER_GROUP_CNT
1857 , HEADER_QUALS_EXIST_FLAG
1858 , DISTINCT_ROW_COUNT
1859 , TRANSACTION_ID
1860 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1861 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1862 ,ORIG_SYS_QUALIFIER_REF
1863 ,ORIG_SYS_LINE_REF
1864 ,ORIG_SYS_HEADER_REF
1865 )
1866 select
1867 ATTRIBUTE1
1868 , ATTRIBUTE10
1869 , ATTRIBUTE11
1870 , ATTRIBUTE12
1871 , ATTRIBUTE13
1872 , ATTRIBUTE14
1873 , ATTRIBUTE15
1874 , ATTRIBUTE2
1875 , ATTRIBUTE3
1876 , ATTRIBUTE4
1877 , ATTRIBUTE5
1878 , ATTRIBUTE6
1879 , ATTRIBUTE7
1880 , ATTRIBUTE8
1881 , ATTRIBUTE9
1882 , COMPARISON_OPERATOR_CODE
1883 , CONTEXT
1884 , CREATED_BY
1885 , CREATED_FROM_RULE_ID
1886 , CREATION_DATE
1887 , END_DATE_ACTIVE
1888 , EXCLUDER_FLAG
1889 , LAST_UPDATED_BY
1890 , sysdate
1891 , LAST_UPDATE_LOGIN
1892 , LIST_HEADER_ID
1893 , LIST_LINE_ID
1894 , PROGRAM_APPLICATION_ID
1895 , PROGRAM_ID
1896 , PROGRAM_UPDATE_DATE
1897 , QUALIFIER_ATTRIBUTE
1898 , QUALIFIER_ATTR_VALUE
1899 , QUALIFIER_ATTR_VALUE_TO
1900 , QUALIFIER_CONTEXT
1901 , QUALIFIER_DATATYPE
1902 --, QUALIFIER_DATE_FORMAT
1903 , QUALIFIER_GROUPING_NO
1904 , QUALIFIER_ID
1905 --, QUALIFIER_NUMBER_FORMAT
1906 , QUALIFIER_PRECEDENCE
1907 , QUALIFIER_RULE_ID
1908 , REQUEST_ID
1909 , START_DATE_ACTIVE
1910 , ACTIVE_FLAG
1911 , LIST_TYPE_CODE
1912 , QUAL_ATTR_VALUE_FROM_NUMBER
1913 , QUAL_ATTR_VALUE_TO_NUMBER
1914 , SEARCH_IND
1915 , QUALIFIER_GROUP_CNT
1916 , 'X'
1917 , DISTINCT_ROW_COUNT
1918 , p_transaction_id
1919 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
1920 --ENH Upgrade BOAPI for orig_sys...ref RAVI
1921 ,to_char(qualifier_id)
1922 ,nvl(ORIG_SYS_LINE_REF,(select l.ORIG_SYS_LINE_REF from qp_list_lines l where l.list_line_id=p_list_line_id))
1923 ,nvl(ORIG_SYS_HEADER_REF,(select h.ORIG_SYSTEM_HEADER_REF from qp_list_headers_b h where h.list_header_id=p_list_header_id))
1924 from qp_qualifiers qual
1925 where list_header_id = p_list_header_id
1926 and list_line_id = p_list_line_id
1927 and qualifier_grouping_no = p_qual_grp_no
1928 and ((qual.list_type_code = 'PRL'
1929 and not (qual.qualifier_context = 'MODLIST' and qual.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'))
1930 or (qual.list_type_code <> 'PRL'));
1931 END IF;--SQL%ROWCOUNT
1932 --AUTONOMOUS commit
1933 commit;
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 null;
1937 END Mark_Delete_Dummy_Qual;
1938
1939 --This procedure will delete the dummy qualifiers inserted for updates
1940 Procedure Remove_Dummy_Quals(p_action_type IN VARCHAR2,
1941 p_list_header_id IN NUMBER,
1942 p_list_line_id IN NUMBER,
1943 p_transaction_id IN NUMBER) IS
1944 PRAGMA AUTONOMOUS_TRANSACTION;
1945 BEGIN
1946 IF p_action_type = 'CANCEL' THEN
1947 delete from qp_qualifiers_fwk_dummy
1948 where list_header_id = p_list_header_id
1949 and list_line_id = p_list_line_id
1950 and transaction_id = p_transaction_id
1951 and nvl(header_quals_exist_flag, 'N') in ('Q');--, 'D', 'U', 'N');
1952 ELSIF p_action_type = 'APPLY' THEN
1953 delete from qp_qualifiers_fwk_dummy
1954 where list_header_id = p_list_header_id
1955 and list_line_id = p_list_line_id
1956 and transaction_id = p_transaction_id
1957 and nvl(header_quals_exist_flag, 'N') = 'Q';
1958 END IF;--p_action_type
1959 --AUTONOMOUS commit
1960 commit;
1961 EXCEPTION
1962 WHEN OTHERS THEN
1963 null;
1964 END Remove_Dummy_Quals;
1965
1966 -- Procedure Delete_Row
1967
1968 PROCEDURE Delete_Row
1969 ( p_qualifier_id IN NUMBER := FND_API.G_MISS_NUM,
1970 p_qualifier_rule_id IN NUMBER := FND_API.G_MISS_NUM
1971 )
1972 IS
1973 --l_check_active_flag VARCHAR2(1);
1974 --l_status VARCHAR2(1);
1975 --l_qualifiers_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
1976 BEGIN
1977 -- l_qualifiers_rec:=Query_Row(p_qualifier_id);
1978 IF p_qualifier_rule_id <> FND_API.G_MISS_NUM
1979 THEN
1980
1981 DELETE FROM QP_QUALIFIERS
1982 WHERE QUALIFIER_RULE_ID = p_qualifier_rule_id ;
1983 ELSE
1984
1985 DELETE FROM QP_QUALIFIERS
1986 WHERE QUALIFIER_ID = p_qualifier_id ;
1987
1988 END IF;
1989
1990 /*
1991 l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
1992 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND l_qualifiers_rec.active_flag='Y') THEN
1993 IF(l_qualifiers_rec.qualifier_context IS NOT NULL) AND
1994 (l_qualifiers_rec.qualifier_attribute IS NOT NULL) THEN
1995 l_status:=QP_UTIL.Is_Used('QUALIFIER',
1996 l_qualifiers_rec.qualifier_context,
1997 l_qualifiers_rec.qualifier_attribute);
1998 IF l_status='N' THEN
1999 UPDATE qp_pte_segments SET used_in_setup='N'
2000 WHERE
2001 segment_id IN
2002 (SELECT a.segment_id
2003 FROM qp_segments_b a,qp_prc_contexts_b b
2004 WHERE a.segment_mapping_column=l_qualifiers_rec.qualifier_attribute
2005 AND a.prc_context_id=b.prc_context_id
2006 AND b.prc_context_type='QUALIFIER'
2007 AND b.prc_context_code=l_qualifiers_rec.qualifier_context);
2008 END IF;
2009 END IF;
2010 END IF;
2011 */
2012
2013 EXCEPTION
2014
2015 WHEN OTHERS THEN
2016
2017 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2018 THEN
2019 OE_MSG_PUB.Add_Exc_Msg
2020 ( G_PKG_NAME
2021 , 'Delete_Row'
2022 );
2023 END IF;
2024
2025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2026
2027 END Delete_Row;
2028
2029 PROCEDURE Delete_Row(p_qual_grp_no IN NUMBER,
2030 p_list_header_id IN NUMBER,
2031 p_list_line_id IN NUMBER,
2032 p_transaction_id IN NUMBER)
2033 IS
2034 PRAGMA AUTONOMOUS_TRANSACTION;
2035 BEGIN
2036
2037 delete from qp_qualifiers_fwk_dummy
2038 where transaction_id = p_transaction_id
2039 or last_update_date < sysdate - 5;
2040 --where qualifier_grouping_no = p_qual_grp_no
2041 --and list_header_id = p_list_header_id
2042 --and list_line_id = p_list_line_id;
2043
2044 COMMIT;
2045
2046 EXCEPTION
2047 WHEN OTHERS THEN
2048 null;
2049 End Delete_Row;
2050
2051 -- Function Query_Row
2052
2053 FUNCTION Query_Row
2054 ( p_qualifier_id IN NUMBER
2055 ) RETURN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
2056 IS
2057 BEGIN
2058
2059 RETURN Query_Rows
2060 ( p_qualifier_id => p_qualifier_id
2061 )(1);
2062
2063 END Query_Row;
2064
2065 -- Function Query_Rows
2066
2067 --
2068
2069 FUNCTION Query_Rows
2070 ( p_qualifier_id IN NUMBER :=
2071 FND_API.G_MISS_NUM
2072 , p_qualifier_rule_id IN NUMBER :=
2073 FND_API.G_MISS_NUM
2074 ) RETURN QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type
2075 IS
2076 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
2077 l_QUALIFIERS_tbl QP_Qualifier_Rules_PUB.Qualifiers_Tbl_Type;
2078
2079 CURSOR l_QUALIFIERS_csr IS
2080 SELECT ATTRIBUTE1
2081 , ATTRIBUTE10
2082 , ATTRIBUTE11
2083 , ATTRIBUTE12
2084 , ATTRIBUTE13
2085 , ATTRIBUTE14
2086 , ATTRIBUTE15
2087 , ATTRIBUTE2
2088 , ATTRIBUTE3
2089 , ATTRIBUTE4
2090 , ATTRIBUTE5
2091 , ATTRIBUTE6
2092 , ATTRIBUTE7
2093 , ATTRIBUTE8
2094 , ATTRIBUTE9
2095 , COMPARISON_OPERATOR_CODE
2096 , CONTEXT
2097 , CREATED_BY
2098 , CREATED_FROM_RULE_ID
2099 , CREATION_DATE
2100 , END_DATE_ACTIVE
2101 , EXCLUDER_FLAG
2102 , LAST_UPDATED_BY
2103 , LAST_UPDATE_DATE
2104 , LAST_UPDATE_LOGIN
2105 , LIST_HEADER_ID
2106 , LIST_LINE_ID
2107 , PROGRAM_APPLICATION_ID
2108 , PROGRAM_ID
2109 , PROGRAM_UPDATE_DATE
2110 , QUALIFIER_ATTRIBUTE
2111 , QUALIFIER_ATTR_VALUE
2112 , QUALIFIER_ATTR_VALUE_TO
2113 , QUALIFIER_CONTEXT
2114 , QUALIFIER_DATATYPE
2115 -- , QUALIFIER_DATE_FORMAT
2116 , QUALIFIER_GROUPING_NO
2117 , QUALIFIER_ID
2118 -- , QUALIFIER_NUMBER_FORMAT
2119 , QUALIFIER_PRECEDENCE
2120 , QUALIFIER_RULE_ID
2121 , REQUEST_ID
2122 , START_DATE_ACTIVE
2123 , LIST_TYPE_CODE
2124 , QUAL_ATTR_VALUE_FROM_NUMBER
2125 , QUAL_ATTR_VALUE_TO_NUMBER
2126 , ACTIVE_FLAG
2127 , SEARCH_IND
2128 , QUALIFIER_GROUP_CNT
2129 , HEADER_QUALS_EXIST_FLAG
2130 , DISTINCT_ROW_COUNT
2131 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
2132 FROM QP_QUALIFIERS
2133 WHERE ( QUALIFIER_ID = p_qualifier_id
2134 )
2135 OR ( QUALIFIER_RULE_ID = p_qualifier_rule_id
2136 );
2137
2138 BEGIN
2139
2140 IF
2141 (p_qualifier_id IS NOT NULL
2142 AND
2143 p_qualifier_id <> FND_API.G_MISS_NUM)
2144 AND
2145 (p_qualifier_rule_id IS NOT NULL
2146 AND
2147 p_qualifier_rule_id <> FND_API.G_MISS_NUM)
2148 THEN
2149 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2150 THEN
2151 OE_MSG_PUB.Add_Exc_Msg
2152 ( G_PKG_NAME
2153 , 'Query Rows'
2154 , 'Keys are mutually exclusive: qualifier_id = '|| p_qualifier_id || ', qualifier_rule_id = '|| p_qualifier_rule_id
2155 );
2156 END IF;
2157
2158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2159
2160 END IF;
2161
2162
2163 -- Loop over fetched records
2164
2165 FOR l_implicit_rec IN l_QUALIFIERS_csr LOOP
2166
2167 l_QUALIFIERS_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
2168 l_QUALIFIERS_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
2169 l_QUALIFIERS_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
2170 l_QUALIFIERS_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
2171 l_QUALIFIERS_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
2172 l_QUALIFIERS_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
2173 l_QUALIFIERS_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
2174 l_QUALIFIERS_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
2175 l_QUALIFIERS_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
2176 l_QUALIFIERS_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
2177 l_QUALIFIERS_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
2178 l_QUALIFIERS_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
2179 l_QUALIFIERS_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
2180 l_QUALIFIERS_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
2181 l_QUALIFIERS_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
2182 l_QUALIFIERS_rec.comparison_operator_code := l_implicit_rec.COMPARISON_OPERATOR_CODE;
2183 l_QUALIFIERS_rec.context := l_implicit_rec.CONTEXT;
2184 l_QUALIFIERS_rec.created_by := l_implicit_rec.CREATED_BY;
2185 l_QUALIFIERS_rec.created_from_rule_id := l_implicit_rec.CREATED_FROM_RULE_ID;
2186 l_QUALIFIERS_rec.creation_date := l_implicit_rec.CREATION_DATE;
2187 l_QUALIFIERS_rec.end_date_active := l_implicit_rec.END_DATE_ACTIVE;
2188 l_QUALIFIERS_rec.excluder_flag := l_implicit_rec.EXCLUDER_FLAG;
2189 l_QUALIFIERS_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
2190 l_QUALIFIERS_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
2191 l_QUALIFIERS_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
2192 l_QUALIFIERS_rec.list_header_id := l_implicit_rec.LIST_HEADER_ID;
2193 l_QUALIFIERS_rec.list_line_id := l_implicit_rec.LIST_LINE_ID;
2194 l_QUALIFIERS_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
2195 l_QUALIFIERS_rec.program_id := l_implicit_rec.PROGRAM_ID;
2196 l_QUALIFIERS_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
2197 l_QUALIFIERS_rec.qualifier_attribute := l_implicit_rec.QUALIFIER_ATTRIBUTE;
2198 l_QUALIFIERS_rec.qualifier_attr_value := l_implicit_rec.QUALIFIER_ATTR_VALUE;
2199 l_QUALIFIERS_rec.qualifier_attr_value_to := l_implicit_rec.QUALIFIER_ATTR_VALUE_TO;
2200 l_QUALIFIERS_rec.qualifier_context := l_implicit_rec.QUALIFIER_CONTEXT;
2201 l_QUALIFIERS_rec.qualifier_datatype := l_implicit_rec.QUALIFIER_DATATYPE;
2202 -- l_QUALIFIERS_rec.qualifier_date_format := l_implicit_rec.QUALIFIER_DATE_FORMAT;
2203 l_QUALIFIERS_rec.qualifier_grouping_no := l_implicit_rec.QUALIFIER_GROUPING_NO;
2204 l_QUALIFIERS_rec.qualifier_id := l_implicit_rec.QUALIFIER_ID;
2205 --l_QUALIFIERS_rec.qualifier_number_format := l_implicit_rec.QUALIFIER_NUMBER_FORMAT;
2206 l_QUALIFIERS_rec.qualifier_precedence := l_implicit_rec.QUALIFIER_PRECEDENCE;
2207 l_QUALIFIERS_rec.qualifier_rule_id := l_implicit_rec.QUALIFIER_RULE_ID;
2208 l_QUALIFIERS_rec.request_id := l_implicit_rec.REQUEST_ID;
2209 l_QUALIFIERS_rec.start_date_active := l_implicit_rec.START_DATE_ACTIVE;
2210 l_QUALIFIERS_rec.list_type_code := l_implicit_rec.LIST_TYPE_CODE;
2211 l_QUALIFIERS_rec.qual_attr_value_from_number := l_implicit_rec.QUAL_ATTR_VALUE_FROM_NUMBER;
2212 l_QUALIFIERS_rec.qual_attr_value_to_number := l_implicit_rec.QUAL_ATTR_VALUE_TO_NUMBER;
2213 l_QUALIFIERS_rec.active_flag := l_implicit_rec.ACTIVE_FLAG;
2214 l_QUALIFIERS_rec.search_ind := l_implicit_rec.SEARCH_IND;
2215 l_QUALIFIERS_rec.qualifier_group_cnt := l_implicit_rec.QUALIFIER_GROUP_CNT;
2216 l_QUALIFIERS_rec.header_quals_exist_flag := l_implicit_rec.HEADER_QUALS_EXIST_FLAG;
2217 l_QUALIFIERS_rec.distinct_row_count := l_implicit_rec.DISTINCT_ROW_COUNT;
2218 l_QUALIFIERS_rec.qualify_hier_descendent_flag := l_implicit_rec.QUALIFY_HIER_DESCENDENTS_FLAG ; -- Added for TCA
2219
2220 l_QUALIFIERS_tbl(l_QUALIFIERS_tbl.COUNT + 1) := l_QUALIFIERS_rec;
2221
2222 END LOOP;
2223
2224
2225 -- PK sent and no rows found
2226
2227 IF
2228 (p_qualifier_id IS NOT NULL
2229 AND
2230 p_qualifier_id <> FND_API.G_MISS_NUM)
2231 AND
2232 (l_QUALIFIERS_tbl.COUNT = 0)
2233 THEN
2234 RAISE NO_DATA_FOUND;
2235 END IF;
2236
2237
2238 -- Return fetched table
2239
2240 RETURN l_QUALIFIERS_tbl;
2241
2242 EXCEPTION
2243
2244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2245
2246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2247
2248 WHEN OTHERS THEN
2249
2250 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2251 THEN
2252 OE_MSG_PUB.Add_Exc_Msg
2253 ( G_PKG_NAME
2254 , 'Query_Rows'
2255 );
2256 END IF;
2257
2258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2259
2260 END Query_Rows;
2261
2262 -- Procedure lock_Row
2263 --
2264
2265 PROCEDURE Lock_Row
2266 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2267 , p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
2268 , x_QUALIFIERS_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
2269 )
2270 IS
2271 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
2272 BEGIN
2273
2274 oe_debug_pub.add('in QPXUQPQB.pls lock row');
2275
2276
2277
2278
2279 SELECT ATTRIBUTE1
2280 , ATTRIBUTE10
2281 , ATTRIBUTE11
2282 , ATTRIBUTE12
2283 , ATTRIBUTE13
2284 , ATTRIBUTE14
2285 , ATTRIBUTE15
2286 , ATTRIBUTE2
2287 , ATTRIBUTE3
2288 , ATTRIBUTE4
2289 , ATTRIBUTE5
2290 , ATTRIBUTE6
2291 , ATTRIBUTE7
2292 , ATTRIBUTE8
2293 , ATTRIBUTE9
2294 , COMPARISON_OPERATOR_CODE
2295 , CONTEXT
2296 , CREATED_BY
2297 , CREATED_FROM_RULE_ID
2298 , CREATION_DATE
2299 , END_DATE_ACTIVE
2300 , EXCLUDER_FLAG
2301 , LAST_UPDATED_BY
2302 , LAST_UPDATE_DATE
2303 , LAST_UPDATE_LOGIN
2304 , LIST_HEADER_ID
2305 , LIST_LINE_ID
2306 , PROGRAM_APPLICATION_ID
2307 , PROGRAM_ID
2308 , PROGRAM_UPDATE_DATE
2309 , QUALIFIER_ATTRIBUTE
2310 , QUALIFIER_ATTR_VALUE
2311 , QUALIFIER_ATTR_VALUE_TO
2312 , QUALIFIER_CONTEXT
2313 , QUALIFIER_DATATYPE
2314 --, QUALIFIER_DATE_FORMAT
2315 , QUALIFIER_GROUPING_NO
2316 , QUALIFIER_ID
2317 --, QUALIFIER_NUMBER_FORMAT
2318 , QUALIFIER_PRECEDENCE
2319 , QUALIFIER_RULE_ID
2320 , REQUEST_ID
2321 , START_DATE_ACTIVE
2322 , QUALIFY_HIER_DESCENDENTS_FLAG -- Added for TCA
2323 INTO l_QUALIFIERS_rec.attribute1
2324 , l_QUALIFIERS_rec.attribute10
2325 , l_QUALIFIERS_rec.attribute11
2326 , l_QUALIFIERS_rec.attribute12
2327 , l_QUALIFIERS_rec.attribute13
2328 , l_QUALIFIERS_rec.attribute14
2329 , l_QUALIFIERS_rec.attribute15
2330 , l_QUALIFIERS_rec.attribute2
2331 , l_QUALIFIERS_rec.attribute3
2332 , l_QUALIFIERS_rec.attribute4
2333 , l_QUALIFIERS_rec.attribute5
2334 , l_QUALIFIERS_rec.attribute6
2335 , l_QUALIFIERS_rec.attribute7
2336 , l_QUALIFIERS_rec.attribute8
2337 , l_QUALIFIERS_rec.attribute9
2338 , l_QUALIFIERS_rec.comparison_operator_code
2339 , l_QUALIFIERS_rec.context
2340 , l_QUALIFIERS_rec.created_by
2341 , l_QUALIFIERS_rec.created_from_rule_id
2342 , l_QUALIFIERS_rec.creation_date
2343 , l_QUALIFIERS_rec.end_date_active
2344 , l_QUALIFIERS_rec.excluder_flag
2345 , l_QUALIFIERS_rec.last_updated_by
2346 , l_QUALIFIERS_rec.last_update_date
2347 , l_QUALIFIERS_rec.last_update_login
2348 , l_QUALIFIERS_rec.list_header_id
2349 , l_QUALIFIERS_rec.list_line_id
2350 , l_QUALIFIERS_rec.program_application_id
2351 , l_QUALIFIERS_rec.program_id
2352 , l_QUALIFIERS_rec.program_update_date
2353 , l_QUALIFIERS_rec.qualifier_attribute
2354 , l_QUALIFIERS_rec.qualifier_attr_value
2355 , l_QUALIFIERS_rec.qualifier_attr_value_to
2356 , l_QUALIFIERS_rec.qualifier_context
2357 , l_QUALIFIERS_rec.qualifier_datatype
2358 --, l_QUALIFIERS_rec.qualifier_date_format
2359 , l_QUALIFIERS_rec.qualifier_grouping_no
2360 , l_QUALIFIERS_rec.qualifier_id
2361 --, l_QUALIFIERS_rec.qualifier_number_format
2362 , l_QUALIFIERS_rec.qualifier_precedence
2363 , l_QUALIFIERS_rec.qualifier_rule_id
2364 , l_QUALIFIERS_rec.request_id
2365 , l_QUALIFIERS_rec.start_date_active
2366 , l_QUALIFIERS_rec.qualify_hier_descendent_flag -- Added for TCA
2367 FROM QP_QUALIFIERS
2368 WHERE QUALIFIER_ID = p_QUALIFIERS_rec.qualifier_id
2369 FOR UPDATE NOWAIT;
2370
2371 -- Row locked. Compare IN attributes to DB attributes.
2372
2373
2374 /*IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute1,
2375 l_QUALIFIERS_rec.attribute1)
2376 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute10,
2377 l_QUALIFIERS_rec.attribute10)
2378 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute11,
2379 l_QUALIFIERS_rec.attribute11)
2380 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute12,
2381 l_QUALIFIERS_rec.attribute12)
2382 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute13,
2383 l_QUALIFIERS_rec.attribute13)
2384 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute14,
2385 l_QUALIFIERS_rec.attribute14)
2386 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute15,
2387 l_QUALIFIERS_rec.attribute15)
2388 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute2,
2389 l_QUALIFIERS_rec.attribute2)
2390 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute3,
2391 l_QUALIFIERS_rec.attribute3)
2392 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute4,
2393 l_QUALIFIERS_rec.attribute4)
2394 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute5,
2395 l_QUALIFIERS_rec.attribute5)
2396 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute6,
2397 l_QUALIFIERS_rec.attribute6)
2398 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute7,
2399 l_QUALIFIERS_rec.attribute7)
2400 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute8,
2401 l_QUALIFIERS_rec.attribute8)
2402 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute9,
2403 l_QUALIFIERS_rec.attribute9)
2404 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.comparison_operator_code,
2405 l_QUALIFIERS_rec.comparison_operator_code)
2406 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.context,
2407 l_QUALIFIERS_rec.context)
2408 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_by,
2409 l_QUALIFIERS_rec.created_by)
2410 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_from_rule_id,
2411 l_QUALIFIERS_rec.created_from_rule_id)
2412 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.creation_date,
2413 -- l_QUALIFIERS_rec.creation_date)
2414 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.end_date_active,
2415 l_QUALIFIERS_rec.end_date_active)
2416 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.excluder_flag,
2417 l_QUALIFIERS_rec.excluder_flag)
2418 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,
2419 -- l_QUALIFIERS_rec.last_updated_by)
2420 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,
2421 -- l_QUALIFIERS_rec.last_update_date)
2422 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,
2423 l_QUALIFIERS_rec.last_update_login)
2424 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,
2425 l_QUALIFIERS_rec.list_header_id)
2426 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,
2427 l_QUALIFIERS_rec.list_line_id)
2428 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_application_id,
2429 l_QUALIFIERS_rec.program_application_id)
2430 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_id,
2431 l_QUALIFIERS_rec.program_id)
2432 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,
2433 -- l_QUALIFIERS_rec.program_update_date)
2434 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,
2435 l_QUALIFIERS_rec.qualifier_attribute)
2436 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value,
2437 l_QUALIFIERS_rec.qualifier_attr_value)
2438 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value_to,
2439 l_QUALIFIERS_rec.qualifier_attr_value_to)
2440 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_context,
2441 l_QUALIFIERS_rec.qualifier_context)
2442 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_datatype,
2443 l_QUALIFIERS_rec.qualifier_datatype)
2444 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_date_format,
2445 -- l_QUALIFIERS_rec.qualifier_date_format)
2446 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
2447 l_QUALIFIERS_rec.qualifier_grouping_no)
2448 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,
2449 l_QUALIFIERS_rec.qualifier_id)
2450 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_number_format,
2451 -- l_QUALIFIERS_rec.qualifier_number_format)
2452 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_precedence,
2453 l_QUALIFIERS_rec.qualifier_precedence)
2454 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,
2455 l_QUALIFIERS_rec.qualifier_rule_id)
2456 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.request_id,
2457 l_QUALIFIERS_rec.request_id)
2458 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.start_date_active,
2459 l_QUALIFIERS_rec.start_date_active)*/
2460
2461
2462 IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute1,
2463 l_QUALIFIERS_rec.attribute1)
2464 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute10,
2465 l_QUALIFIERS_rec.attribute10)
2466 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute11,
2467 l_QUALIFIERS_rec.attribute11)
2468 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute12,
2469 l_QUALIFIERS_rec.attribute12)
2470 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute13,
2471 l_QUALIFIERS_rec.attribute13)
2472 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute14,
2473 l_QUALIFIERS_rec.attribute14)
2474 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute15,
2475 l_QUALIFIERS_rec.attribute15)
2476 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute2,
2477 l_QUALIFIERS_rec.attribute2)
2478 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute3,
2479 l_QUALIFIERS_rec.attribute3)
2480 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute4,
2481 l_QUALIFIERS_rec.attribute4)
2482 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute5,
2483 l_QUALIFIERS_rec.attribute5)
2484 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute6,
2485 l_QUALIFIERS_rec.attribute6)
2486 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute7,
2487 l_QUALIFIERS_rec.attribute7)
2488 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute8,
2489 l_QUALIFIERS_rec.attribute8)
2490 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.attribute9,
2491 l_QUALIFIERS_rec.attribute9)
2492 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.comparison_operator_code,
2493 l_QUALIFIERS_rec.comparison_operator_code)
2494 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.context,
2495 l_QUALIFIERS_rec.context)
2496 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_by,
2497 -- l_QUALIFIERS_rec.created_by)
2498 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_from_rule_id,
2499 l_QUALIFIERS_rec.created_from_rule_id)
2500 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.creation_date,
2501 -- l_QUALIFIERS_rec.creation_date)
2502 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.end_date_active,
2503 l_QUALIFIERS_rec.end_date_active)
2504 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.excluder_flag,
2505 l_QUALIFIERS_rec.excluder_flag)
2506 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_updated_by,
2507 -- l_QUALIFIERS_rec.last_updated_by)
2508 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_date,
2509 -- l_QUALIFIERS_rec.last_update_date)
2510 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.last_update_login,
2511 -- l_QUALIFIERS_rec.last_update_login)
2512 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,
2513 l_QUALIFIERS_rec.list_header_id)
2514 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,
2515 l_QUALIFIERS_rec.list_line_id)
2516 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_application_id,
2517 -- l_QUALIFIERS_rec.program_application_id)
2518 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_id,
2519 -- l_QUALIFIERS_rec.program_id)
2520 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.program_update_date,
2521 -- l_QUALIFIERS_rec.program_update_date)
2522 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,
2523 l_QUALIFIERS_rec.qualifier_attribute)
2524 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value,
2525 l_QUALIFIERS_rec.qualifier_attr_value)
2526 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attr_value_to,
2527 l_QUALIFIERS_rec.qualifier_attr_value_to)
2528 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_context,
2529 l_QUALIFIERS_rec.qualifier_context)
2530 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_datatype,
2531 l_QUALIFIERS_rec.qualifier_datatype)
2532 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_date_format,
2533 -- l_QUALIFIERS_rec.qualifier_date_format)
2534 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
2535 l_QUALIFIERS_rec.qualifier_grouping_no)
2536 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,
2537 l_QUALIFIERS_rec.qualifier_id)
2538 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_number_format,
2539 -- l_QUALIFIERS_rec.qualifier_number_format)
2540 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_precedence,
2541 l_QUALIFIERS_rec.qualifier_precedence)
2542 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,
2543 l_QUALIFIERS_rec.qualifier_rule_id)
2544 --AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.request_id,
2545 -- l_QUALIFIERS_rec.request_id)
2546 AND QP_GLOBALS.Equal(p_QUALIFIERS_rec.start_date_active,
2547 l_QUALIFIERS_rec.start_date_active)
2548 -- Added for TCA
2549 AND (QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualify_hier_descendent_flag,
2550 l_QUALIFIERS_rec.qualify_hier_descendent_flag)
2551 OR (p_QUALIFIERS_rec.qualify_hier_descendent_flag = FND_API.G_MISS_CHAR
2552 and l_QUALIFIERS_rec.qualify_hier_descendent_flag is null))
2553 THEN
2554
2555 -- Row has not changed. Set out parameter.
2556
2557 oe_debug_pub.add('row not changed');
2558 x_QUALIFIERS_rec := l_QUALIFIERS_rec;
2559
2560 -- Set return status
2561
2562 x_return_status := FND_API.G_RET_STS_SUCCESS;
2563 x_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2564
2565 ELSE
2566
2567 -- Row has changed by another user.
2568 oe_debug_pub.add('row changed');
2569
2570 x_return_status := FND_API.G_RET_STS_ERROR;
2571 x_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2572
2573 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2574 THEN
2575
2576 FND_MESSAGE.SET_NAME('QP','QP_LOCK_ROW_CHANGED');
2577 OE_MSG_PUB.Add;
2578
2579 END IF;
2580
2581 END IF;
2582
2583 EXCEPTION
2584
2585 WHEN NO_DATA_FOUND THEN
2586
2587 x_return_status := FND_API.G_RET_STS_ERROR;
2588 x_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2589
2590 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2591 THEN
2592
2593 oe_debug_pub.add('row deleted');
2594 FND_MESSAGE.SET_NAME('QP','QP_LOCK_ROW_DELETED');
2595 OE_MSG_PUB.Add;
2596
2597 END IF;
2598 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
2599
2600 x_return_status := FND_API.G_RET_STS_ERROR;
2601 x_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2602
2603 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2604 THEN
2605
2606 FND_MESSAGE.SET_NAME('QP','QP_LOCK_ROW_ALREADY_LOCKED');
2607 OE_MSG_PUB.Add;
2608
2609 END IF;
2610 WHEN OTHERS THEN
2611
2612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2613 x_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2614
2615 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2616 THEN
2617 OE_MSG_PUB.Add_Exc_Msg
2618 ( G_PKG_NAME
2619 , 'Lock_Row'
2620 );
2621 END IF;
2622
2623 END Lock_Row;
2624
2625 -- Function Get_Values
2626
2627 FUNCTION Get_Values
2628 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
2629 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type :=
2630 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC
2631 ) RETURN QP_Qualifier_Rules_PUB.Qualifiers_Val_Rec_Type
2632 IS
2633 l_QUALIFIERS_val_rec QP_Qualifier_Rules_PUB.Qualifiers_Val_Rec_Type;
2634 BEGIN
2635
2636 /*IF p_QUALIFIERS_rec.comparison_operator_code IS NOT NULL AND
2637 p_QUALIFIERS_rec.comparison_operator_code <> FND_API.G_MISS_CHAR AND
2638 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.comparison_operator_code,
2639 p_old_QUALIFIERS_rec.comparison_operator_code)
2640 THEN
2641 l_QUALIFIERS_val_rec.comparison_operator := QP_Id_To_Value.Comparison_Operator
2642 ( p_comparison_operator_code => p_QUALIFIERS_rec.comparison_operator_code
2643 );
2644 END IF;*/
2645
2646 IF p_QUALIFIERS_rec.created_from_rule_id IS NOT NULL AND
2647 p_QUALIFIERS_rec.created_from_rule_id <> FND_API.G_MISS_NUM AND
2648 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.created_from_rule_id,
2649 p_old_QUALIFIERS_rec.created_from_rule_id)
2650 THEN
2651 l_QUALIFIERS_val_rec.created_from_rule := QP_Id_To_Value.Created_From_Rule
2652 ( p_created_from_rule_id => p_QUALIFIERS_rec.created_from_rule_id
2653 );
2654 END IF;
2655
2656 /* IF p_QUALIFIERS_rec.excluder_flag IS NOT NULL AND
2657 p_QUALIFIERS_rec.excluder_flag <> FND_API.G_MISS_CHAR AND
2658 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.excluder_flag,
2659 p_old_QUALIFIERS_rec.excluder_flag)
2660 THEN
2661 l_QUALIFIERS_val_rec.excluder := QP_Id_To_Value.Excluder
2662 ( p_excluder_flag => p_QUALIFIERS_rec.excluder_flag
2663 );
2664 END IF;*/
2665
2666 IF p_QUALIFIERS_rec.list_header_id IS NOT NULL AND
2667 p_QUALIFIERS_rec.list_header_id <> FND_API.G_MISS_NUM AND
2668 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_header_id,
2669 p_old_QUALIFIERS_rec.list_header_id)
2670 THEN
2671 l_QUALIFIERS_val_rec.list_header := QP_Id_To_Value.List_Header
2672 ( p_list_header_id => p_QUALIFIERS_rec.list_header_id
2673 );
2674 END IF;
2675
2676 IF p_QUALIFIERS_rec.list_line_id IS NOT NULL AND
2677 p_QUALIFIERS_rec.list_line_id <> FND_API.G_MISS_NUM AND
2678 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.list_line_id,
2679 p_old_QUALIFIERS_rec.list_line_id)
2680 THEN
2681 l_QUALIFIERS_val_rec.list_line := QP_Id_To_Value.List_Line
2682 ( p_list_line_id => p_QUALIFIERS_rec.list_line_id
2683 );
2684 END IF;
2685
2686 /*IF p_QUALIFIERS_rec.qualifier_id IS NOT NULL AND
2687 p_QUALIFIERS_rec.qualifier_id <> FND_API.G_MISS_NUM AND
2688 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_id,
2689 p_old_QUALIFIERS_rec.qualifier_id)
2690 THEN
2691 l_QUALIFIERS_val_rec.qualifier := QP_Id_To_Value.Qualifier
2692 ( p_qualifier_id => p_QUALIFIERS_rec.qualifier_id
2693 );
2694 END IF;*/
2695
2696 IF p_QUALIFIERS_rec.qualifier_rule_id IS NOT NULL AND
2697 p_QUALIFIERS_rec.qualifier_rule_id <> FND_API.G_MISS_NUM AND
2698 NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_rule_id,
2699 p_old_QUALIFIERS_rec.qualifier_rule_id)
2700 THEN
2701 l_QUALIFIERS_val_rec.qualifier_rule := QP_Id_To_Value.Qualifier_Rule
2702 ( p_qualifier_rule_id => p_QUALIFIERS_rec.qualifier_rule_id
2703 );
2704 END IF;
2705
2706 RETURN l_QUALIFIERS_val_rec;
2707
2708 END Get_Values;
2709
2710 -- Function Get_Ids
2711
2712 FUNCTION Get_Ids
2713 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
2714 , p_QUALIFIERS_val_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Val_Rec_Type
2715 ) RETURN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
2716 IS
2717 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type;
2718 BEGIN
2719
2720 -- initialize return_status.
2721
2722 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2723
2724 -- initialize l_QUALIFIERS_rec.
2725
2726 l_QUALIFIERS_rec := p_QUALIFIERS_rec;
2727
2728 /* IF p_QUALIFIERS_val_rec.comparison_operator <> FND_API.G_MISS_CHAR
2729 THEN
2730
2731 IF p_QUALIFIERS_rec.comparison_operator_code <> FND_API.G_MISS_CHAR THEN
2732
2733 l_QUALIFIERS_rec.comparison_operator_code := p_QUALIFIERS_rec.comparison_operator_code;
2734
2735 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2736 THEN
2737
2738 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2739 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','comparison_operator');
2740 OE_MSG_PUB.Add;
2741
2742 END IF;
2743
2744 ELSE
2745
2746 l_QUALIFIERS_rec.comparison_operator_code := QP_Value_To_Id.comparison_operator
2747 ( p_comparison_operator => p_QUALIFIERS_val_rec.comparison_operator
2748 );
2749
2750 IF l_QUALIFIERS_rec.comparison_operator_code = FND_API.G_MISS_CHAR THEN
2751 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2752 END IF;
2753
2754 END IF;
2755
2756 END IF;*/
2757
2758 IF p_QUALIFIERS_val_rec.created_from_rule <> FND_API.G_MISS_CHAR
2759 THEN
2760
2761 IF p_QUALIFIERS_rec.created_from_rule_id <> FND_API.G_MISS_NUM THEN
2762
2763 l_QUALIFIERS_rec.created_from_rule_id := p_QUALIFIERS_rec.created_from_rule_id;
2764
2765 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2766 THEN
2767
2768 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2769 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','created_from_rule');
2770 OE_MSG_PUB.Add;
2771
2772 END IF;
2773
2774 ELSE
2775
2776 l_QUALIFIERS_rec.created_from_rule_id := QP_Value_To_Id.created_from_rule
2777 ( p_created_from_rule => p_QUALIFIERS_val_rec.created_from_rule
2778 );
2779
2780 IF l_QUALIFIERS_rec.created_from_rule_id = FND_API.G_MISS_NUM THEN
2781 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2782 END IF;
2783
2784 END IF;
2785
2786 END IF;
2787
2788 /* IF p_QUALIFIERS_val_rec.excluder <> FND_API.G_MISS_CHAR
2789 THEN
2790
2791 IF p_QUALIFIERS_rec.excluder_flag <> FND_API.G_MISS_CHAR THEN
2792
2793 l_QUALIFIERS_rec.excluder_flag := p_QUALIFIERS_rec.excluder_flag;
2794
2795 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2796 THEN
2797
2798 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2799 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','excluder');
2800 OE_MSG_PUB.Add;
2801
2802 END IF;
2803
2804 ELSE
2805
2806 l_QUALIFIERS_rec.excluder_flag := QP_Value_To_Id.excluder
2807 ( p_excluder => p_QUALIFIERS_val_rec.excluder
2808 );
2809
2810 IF l_QUALIFIERS_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
2811 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2812 END IF;
2813
2814 END IF;
2815
2816 END IF;*/
2817
2818 IF p_QUALIFIERS_val_rec.list_header <> FND_API.G_MISS_CHAR
2819 THEN
2820
2821 IF p_QUALIFIERS_rec.list_header_id <> FND_API.G_MISS_NUM THEN
2822
2823 l_QUALIFIERS_rec.list_header_id := p_QUALIFIERS_rec.list_header_id;
2824
2825 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2826 THEN
2827
2828 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2829 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_header');
2830 OE_MSG_PUB.Add;
2831
2832 END IF;
2833
2834 ELSE
2835
2836 l_QUALIFIERS_rec.list_header_id := QP_Value_To_Id.list_header
2837 ( p_list_header => p_QUALIFIERS_val_rec.list_header
2838 );
2839
2840 IF l_QUALIFIERS_rec.list_header_id = FND_API.G_MISS_NUM THEN
2841 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2842 END IF;
2843
2844 END IF;
2845
2846 END IF;
2847
2848 IF p_QUALIFIERS_val_rec.list_line <> FND_API.G_MISS_CHAR
2849 THEN
2850
2851 IF p_QUALIFIERS_rec.list_line_id <> FND_API.G_MISS_NUM THEN
2852
2853 l_QUALIFIERS_rec.list_line_id := p_QUALIFIERS_rec.list_line_id;
2854
2855 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2856 THEN
2857
2858 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2859 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_line');
2860 OE_MSG_PUB.Add;
2861
2862 END IF;
2863
2864 ELSE
2865
2866 l_QUALIFIERS_rec.list_line_id := QP_Value_To_Id.list_line
2867 ( p_list_line => p_QUALIFIERS_val_rec.list_line
2868 );
2869
2870 IF l_QUALIFIERS_rec.list_line_id = FND_API.G_MISS_NUM THEN
2871 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2872 END IF;
2873
2874 END IF;
2875
2876 END IF;
2877
2878 /* IF p_QUALIFIERS_val_rec.qualifier <> FND_API.G_MISS_CHAR
2879 THEN
2880
2881 IF p_QUALIFIERS_rec.qualifier_id <> FND_API.G_MISS_NUM THEN
2882
2883 l_QUALIFIERS_rec.qualifier_id := p_QUALIFIERS_rec.qualifier_id;
2884
2885 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2886 THEN
2887
2888 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2889 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','qualifier');
2890 OE_MSG_PUB.Add;
2891
2892 END IF;
2893
2894 ELSE
2895
2896 l_QUALIFIERS_rec.qualifier_id := QP_Value_To_Id.qualifier
2897 ( p_qualifier => p_QUALIFIERS_val_rec.qualifier
2898 );
2899
2900 IF l_QUALIFIERS_rec.qualifier_id = FND_API.G_MISS_NUM THEN
2901 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2902 END IF;
2903
2904 END IF;
2905
2906 END IF;*/
2907
2908 IF p_QUALIFIERS_val_rec.qualifier_rule <> FND_API.G_MISS_CHAR
2909 THEN
2910
2911 IF p_QUALIFIERS_rec.qualifier_rule_id <> FND_API.G_MISS_NUM THEN
2912
2913 l_QUALIFIERS_rec.qualifier_rule_id := p_QUALIFIERS_rec.qualifier_rule_id;
2914
2915 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2916 THEN
2917
2918 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2919 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','qualifier_rule');
2920 OE_MSG_PUB.Add;
2921
2922 END IF;
2923
2924 ELSE
2925
2926 l_QUALIFIERS_rec.qualifier_rule_id := QP_Value_To_Id.qualifier_rule
2927 ( p_qualifier_rule => p_QUALIFIERS_val_rec.qualifier_rule
2928 );
2929
2930 IF l_QUALIFIERS_rec.qualifier_rule_id = FND_API.G_MISS_NUM THEN
2931 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2932 END IF;
2933
2934 END IF;
2935
2936 END IF;
2937
2938 /**************************************************************************
2939 Added code for value_to_id conversion for qualifier_attribute,
2940 qualifier_attr_value and qualifier_attr_value_to
2941 ***************************************************************************/
2942
2943 IF p_QUALIFIERS_val_rec.qualifier_attribute_desc <> FND_API.G_MISS_CHAR
2944 THEN
2945
2946 IF p_QUALIFIERS_rec.qualifier_attribute <> FND_API.G_MISS_CHAR THEN
2947
2948 l_QUALIFIERS_rec.qualifier_attribute := p_QUALIFIERS_rec.qualifier_attribute;
2949
2950 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2951 THEN
2952
2953 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2954 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','qualifier_attribute');
2955 OE_MSG_PUB.Add;
2956
2957 END IF;
2958
2959 ELSE
2960
2961 l_QUALIFIERS_rec.qualifier_attribute := QP_Value_To_Id.qualifier_attribute
2962 ( p_qualifier_attribute_desc => p_QUALIFIERS_val_rec.qualifier_attribute_desc,
2963 p_context => l_QUALIFIERS_rec.qualifier_context
2964 );
2965
2966 IF l_QUALIFIERS_rec.qualifier_attribute = FND_API.G_MISS_CHAR THEN
2967 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
2968 END IF;
2969
2970 END IF;
2971
2972 END IF;
2973
2974 IF p_QUALIFIERS_val_rec.qualifier_attr_value_desc <> FND_API.G_MISS_CHAR
2975 THEN
2976
2977 IF p_QUALIFIERS_rec.qualifier_attr_value <> FND_API.G_MISS_CHAR THEN
2978
2979 l_QUALIFIERS_rec.qualifier_attr_value := p_QUALIFIERS_rec.qualifier_attr_value;
2980
2981 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2982 THEN
2983
2984 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2985 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','qualifier_attr_value');
2986 OE_MSG_PUB.Add;
2987
2988 END IF;
2989
2990 ELSE
2991
2992 l_QUALIFIERS_rec.qualifier_attr_value := QP_Value_To_Id.qualifier_attr_value
2993 ( p_qualifier_attr_value_desc => p_QUALIFIERS_val_rec.qualifier_attr_value_desc,
2994 p_context => l_QUALIFIERS_rec.qualifier_context,
2995 p_attribute => l_QUALIFIERS_rec.qualifier_attribute
2996 );
2997
2998 IF l_QUALIFIERS_rec.qualifier_attr_value = FND_API.G_MISS_CHAR THEN
2999 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
3000 END IF;
3001
3002 END IF;
3003
3004 END IF;
3005
3006 IF p_QUALIFIERS_val_rec.qualifier_attr_value_to_desc <> FND_API.G_MISS_CHAR
3007 THEN
3008
3009 IF p_QUALIFIERS_rec.qualifier_attr_value_to <> FND_API.G_MISS_CHAR THEN
3010
3011 l_QUALIFIERS_rec.qualifier_attr_value_to := p_QUALIFIERS_rec.qualifier_attr_value_to;
3012
3013 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
3014 THEN
3015
3016 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
3017 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','qualifier_attr_value_to');
3018 OE_MSG_PUB.Add;
3019
3020 END IF;
3021
3022 ELSE
3023
3024 l_QUALIFIERS_rec.qualifier_attr_value_to := QP_Value_To_Id.qualifier_attr_value_to
3025 ( p_qualifier_attr_value_to_desc => p_QUALIFIERS_val_rec.qualifier_attr_value_to_desc,
3026 p_context => l_QUALIFIERS_rec.qualifier_context,
3027 p_attribute => l_QUALIFIERS_rec.qualifier_attribute
3028 );
3029
3030 IF l_QUALIFIERS_rec.qualifier_attr_value_to = FND_API.G_MISS_CHAR THEN
3031 l_QUALIFIERS_rec.return_status := FND_API.G_RET_STS_ERROR;
3032 END IF;
3033
3034 END IF;
3035
3036 END IF;
3037
3038
3039 RETURN l_QUALIFIERS_rec;
3040
3041 END Get_Ids;
3042
3043 Procedure Pre_Write_Process
3044 ( p_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
3045 , p_old_QUALIFIERS_rec IN QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type :=
3046 QP_Qualifier_Rules_PUB.G_MISS_QUALIFIERS_REC
3047 , x_QUALIFIERS_rec OUT NOCOPY /* file.sql.39 change */ QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type
3048 ) IS
3049 l_QUALIFIERS_rec QP_Qualifier_Rules_PUB.Qualifiers_Rec_Type := p_QUALIFIERS_rec;
3050 l_return_status varchar2(30);
3051 l_hlq_count NUMBER := 0;
3052 --Header Level Qualifier Count
3053 l_llq_count NUMBER := 0;
3054 --Line Level Qualifier Count
3055 l_list_type_code VARCHAR2(30);
3056 l_denormalize_qual varchar2(1) := nvl(fnd_profile.value('QP_DENORMALIZE_QUALIFIERS'),'Y');
3057 --7120399
3058
3059 BEGIN
3060
3061 oe_debug_pub.Add('Entering OE_QUALIFIERS_Util.pre_write_process');
3062 oe_debug_pub.Add('mkarya - p_QUALIFIERS_rec.operation = ' ||p_QUALIFIERS_rec.operation);
3063
3064
3065 x_QUALIFIERS_rec := l_QUALIFIERS_rec;
3066
3067 IF ( p_QUALIFIERS_rec.operation IN (QP_GLOBALS.G_OPR_CREATE,
3068 QP_GLOBALS.G_OPR_DELETE) ) THEN
3069 -- Get the List Type Code
3070 BEGIN
3071 SELECT list_type_code
3072 INTO l_list_type_code
3073 FROM qp_list_headers_vl
3074 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 NULL;
3078 END;
3079
3080 -- Get the List Header Level Qualifier count
3081 IF l_list_type_code IN ('PRL', 'AGR') THEN
3082 BEGIN
3083 /*SELECT 1
3084 INTO l_hlq_count
3085 FROM DUAL
3086 WHERE EXISTS (SELECT 'X'
3087 FROM qp_qualifiers
3088 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
3089 AND list_line_id = -1
3090 AND NOT (qualifier_context = 'MODLIST' AND
3091 qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'));*/
3092
3093 /* changed the sql query from where exists to 'and rownum=1'*/
3094
3095 SELECT 1 into l_hlq_count
3096 FROM qp_qualifiers
3097 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
3098 AND list_line_id = -1
3099 AND NOT (qualifier_context = 'MODLIST' AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE4')
3100 and rownum=1;
3101 --Do not consider qualifiers corresponding to Primary PL as
3102 --qualifier for Secondary PL
3103 EXCEPTION
3104 WHEN NO_DATA_FOUND THEN
3105 l_hlq_count := 0;
3106 END;
3107
3108 ELSE -- All other list type codes
3109 BEGIN
3110 /*SELECT 1
3111 INTO l_hlq_count
3112 FROM DUAL
3113 WHERE EXISTS (SELECT 'X'
3114 FROM qp_qualifiers
3115 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
3116 AND list_line_id = -1); */
3117
3118 /* changed the sql query from where exists to 'and rownum=1'*/
3119 SELECT 1 into l_hlq_count
3120 FROM qp_qualifiers
3121 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id
3122 AND list_line_id = -1
3123 and rownum = 1;
3124 EXCEPTION
3125 WHEN NO_DATA_FOUND THEN
3126 l_hlq_count := 0;
3127 END;
3128
3129 END IF; -- list type code is PRL or AGR
3130
3131
3132 -- Get the List Line Level Qualifier count
3133 BEGIN
3134 /* SELECT 1 INTO l_llq_count
3135 FROM DUAL
3136 WHERE EXISTS ( SELECT 'X'
3137 FROM qp_qualifiers
3138 WHERE list_line_id = p_QUALIFIERS_rec.list_line_id);*/
3139
3140 /* changed the sql query from where exists to 'and rownum=1'*/
3141 SELECT 1 into l_llq_count
3142 FROM qp_qualifiers
3143 WHERE list_line_id = p_QUALIFIERS_rec.list_line_id
3144 and rownum=1;
3145 EXCEPTION
3146 WHEN NO_DATA_FOUND THEN
3147 l_llq_count := 0;
3148 END;
3149
3150 --Submit a Header Level Delayed Request to update qualification_ind
3151 --while creating the first or deleting the last header level qualifier.
3152 IF (p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE AND
3153 p_QUALIFIERS_rec.list_line_id = -1 AND
3154 l_hlq_count = 0) OR
3155 (p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
3156 p_QUALIFIERS_rec.list_line_id = -1 AND
3157 l_hlq_count = 1)
3158 THEN
3159
3160 /* Bug 4191955 No need to log delayed request to update header qualification indicator
3161 for secondary price list */
3162
3163 IF NOT (l_list_type_code = 'PRL' and p_QUALIFIERS_rec.qualifier_context='MODLIST'
3164 and p_QUALIFIERS_rec.qualifier_attribute='QUALIFIER_ATTRIBUTE4') THEN
3165
3166 qp_delayed_requests_PVT.log_request(
3167 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3168 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3169 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3170 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3171 p_request_type =>QP_GLOBALS.G_UPDATE_LIST_QUAL_IND,
3172 x_return_status => l_return_status);
3173 END IF;
3174
3175 IF l_list_type_code NOT IN ('PRL', 'AGR') THEN
3176
3177 oe_debug_pub.add('log delayed request------------');
3178
3179 qp_delayed_requests_PVT.log_request(
3180 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3181 p_param1 => p_QUALIFIERS_rec.list_header_id,
3182 p_entity_id => p_QUALIFIERS_rec.qualifier_id,
3183 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3184 p_requesting_entity_id => p_QUALIFIERS_rec.qualifier_id,
3185 p_request_type =>QP_GLOBALS.G_MAINTAIN_LIST_HEADER_PHASES,
3186 x_return_status => l_return_status);
3187
3188 END IF;
3189
3190 --Submit a Line Level Delayed Request to update qualification_ind
3191 --while creating the first or deleting the last line level qualifier.
3192 ELSIF (p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE AND
3193 p_QUALIFIERS_rec.list_line_id <> -1 AND
3194 l_llq_count = 0) OR
3195 (p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
3196 p_QUALIFIERS_rec.list_line_id <> -1 AND
3197 l_llq_count = 1)
3198 THEN
3199 qp_delayed_requests_PVT.log_request(
3200 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3201 p_entity_id => p_QUALIFIERS_rec.list_line_id,
3202 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3203 p_requesting_entity_id => p_QUALIFIERS_rec.list_line_id,
3204 p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
3205 x_return_status => l_return_status);
3206
3207 -- mkarya for bug1769955 - log the MAINTAIN_LIST_HEADER_PHASES request for line qualifier
3208 IF l_list_type_code NOT IN ('PRL', 'AGR') THEN
3209 qp_delayed_requests_PVT.log_request(
3210 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3211 p_param1 => p_QUALIFIERS_rec.list_header_id,
3212 p_entity_id => p_QUALIFIERS_rec.qualifier_id,
3213 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3214 p_requesting_entity_id => p_QUALIFIERS_rec.qualifier_id,
3215 p_request_type =>QP_GLOBALS.G_MAINTAIN_LIST_HEADER_PHASES,
3216 x_return_status => l_return_status);
3217 END IF;
3218
3219 END IF;
3220
3221 END IF; -- If operation is create or delete.
3222
3223 /* Added for 7120399 */
3224 if p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE then
3225
3226 BEGIN
3227 SELECT list_type_code
3228 INTO l_list_type_code
3229 FROM qp_list_headers_vl
3230 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
3231 EXCEPTION
3232 WHEN OTHERS THEN
3233 NULL;
3234 END;
3235 end if;
3236
3237 IF p_QUALIFIERS_rec.operation IN (QP_GLOBALS.G_OPR_CREATE,
3238 QP_GLOBALS.G_OPR_UPDATE,
3239 QP_GLOBALS.G_OPR_DELETE) THEN
3240 IF (l_list_type_code in ('PRL','AGR')) OR ((l_list_type_code NOT IN ('PRL','AGR')) AND (l_denormalize_qual= 'Y')) then --7120399
3241 qp_delayed_requests_PVT.log_request(
3242 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3243 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3244 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3245 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3246 p_request_type => QP_GLOBALS.G_MAINTAIN_QUALIFIER_DEN_COLS,
3247 x_return_status => l_return_status);
3248 END IF;
3249 --Added following delayed request for Attributes Manager
3250 qp_delayed_requests_PVT.log_request(
3251 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3252 p_entity_id => p_QUALIFIERS_rec.qualifier_id,
3253 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3254 p_requesting_entity_id => p_QUALIFIERS_rec.qualifier_id,
3255 p_request_type => QP_GLOBALS.G_MIXED_QUAL_SEG_LEVELS,
3256 p_param1 => p_QUALIFIERS_rec.qualifier_rule_id,
3257 x_return_status => l_return_status);
3258
3259 END IF;
3260
3261 /*
3262 IF (p_QUALIFIERS_rec.operation IN
3263 (QP_GLOBALS.G_OPR_CREATE, QP_GLOBALS.G_OPR_UPDATE)) THEN
3264
3265 oe_debug_pub.add('log delayed request--------warn_same_qual_grp');
3266
3267 qp_delayed_requests_PVT.log_request(
3268 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3269 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3270 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3271 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3272 p_request_type =>QP_GLOBALS.G_WARN_SAME_QUALIFIER_GROUP,
3273 p_param1 => p_QUALIFIERS_rec.list_line_id,
3274 p_param2 => p_QUALIFIERS_rec.qualifier_grouping_no,
3275 p_param3 => p_QUALIFIERS_rec.qualifier_context,
3276 p_param4 => p_QUALIFIERS_rec.qualifier_attribute,
3277 x_return_status => l_return_status);
3278
3279 END IF;
3280 */
3281
3282 -- Attribute Manager Change BEGIN - required for modifiers
3283 -- Logging the request here instead of apply_attribute_changes because list_line_id is -1 even
3284 -- if qualifier is LINE level in apply_attribute_changes
3285 --IF ( p_QUALIFIERS_rec.operation IN (QP_GLOBALS.G_OPR_CREATE,
3286 -- QP_GLOBALS.G_OPR_UPDATE) ) THEN
3287 IF qp_util.attrmgr_installed = 'Y' THEN
3288 IF (NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_attribute,
3289 p_old_QUALIFIERS_rec.qualifier_attribute))
3290 OR
3291 (NOT QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
3292 p_old_QUALIFIERS_rec.qualifier_grouping_no))
3293 OR (p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE) THEN
3294 -- Get the List Type Code
3295 BEGIN
3296 SELECT list_type_code
3297 INTO l_list_type_code
3298 FROM qp_list_headers_vl
3299 WHERE list_header_id = p_QUALIFIERS_rec.list_header_id;
3300 EXCEPTION
3301 WHEN OTHERS THEN
3302 NULL;
3303 END;
3304 IF l_list_type_code NOT IN ('PRL', 'AGR') THEN
3305 oe_debug_pub.add('list_header_id = ' || p_qualifiers_rec.list_header_id);
3306 oe_debug_pub.add('list_line_id = ' || p_qualifiers_rec.list_line_id);
3307 oe_debug_pub.add('qualifier_grouping_no = ' || p_qualifiers_rec.qualifier_grouping_no);
3308 qp_delayed_requests_PVT.log_request(
3309 p_entity_code => QP_GLOBALS.G_ENTITY_QUALIFIERS,
3310 p_request_unique_key1 => p_qualifiers_rec.list_header_id,
3311 p_request_unique_key2 => p_qualifiers_rec.qualifier_grouping_no,
3312 p_entity_id => p_qualifiers_rec.list_line_id,
3313 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_QUALIFIERS,
3314 p_requesting_entity_id => p_qualifiers_rec.list_line_id,
3315 p_request_type =>QP_GLOBALS.G_CHECK_SEGMENT_LEVEL_IN_GROUP,
3316 x_return_status => l_return_status);
3317
3318 -- mkarya for attribute manager
3319 -- Log a delayed request to validate that if header level qualifier exist then at least
3320 -- one qualifier should exist for any existence of modifier line of modifier level
3321 -- 'LINE' or 'ORDER'
3322 if p_qualifiers_rec.list_line_id = -1 then
3323 qp_delayed_requests_PVT.log_request(
3324 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3325 p_entity_id => l_qualifiers_rec.list_header_id,
3326 p_request_unique_key1 => -1,
3327 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_ALL,
3328 p_requesting_entity_id => l_qualifiers_rec.list_header_id,
3329 p_request_type =>QP_GLOBALS.G_CHECK_LINE_FOR_HEADER_QUAL,
3330 x_return_status => l_return_status);
3331 end if;
3332
3333 -- if qualifier_grouping_no is updated to -1 then log the request for all other
3334 -- qualifier_grouping_no for the given list_header_id and list_line_id
3335 if p_qualifiers_rec.qualifier_grouping_no = -1
3336 and p_qualifiers_rec.operation = QP_GLOBALS.G_OPR_UPDATE then
3337 declare
3338 cursor c_qual_grp is
3339 select distinct qualifier_grouping_no qualifier_grouping_no
3340 from qp_qualifiers
3341 where list_header_id = p_qualifiers_rec.list_header_id
3342 and list_line_id = p_qualifiers_rec.list_line_id
3343 and qualifier_grouping_no <> -1;
3344 begin
3345
3346 for l_rec in c_qual_grp
3347 LOOP
3348 oe_debug_pub.add('In LOOP - update of qualifier grp to -1');
3349 oe_debug_pub.add('list_header_id = ' || p_qualifiers_rec.list_header_id);
3350 oe_debug_pub.add('list_line_id = ' || p_qualifiers_rec.list_line_id);
3351 oe_debug_pub.add('qualifier_grouping_no = ' || l_rec.qualifier_grouping_no);
3352 qp_delayed_requests_PVT.log_request(
3353 p_entity_code => QP_GLOBALS.G_ENTITY_QUALIFIERS,
3354 p_request_unique_key1 => p_qualifiers_rec.list_header_id,
3355 p_request_unique_key2 => l_rec.qualifier_grouping_no,
3356 p_entity_id => p_qualifiers_rec.list_line_id,
3357 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_QUALIFIERS,
3358 p_requesting_entity_id => p_qualifiers_rec.list_line_id,
3359 p_request_type =>QP_GLOBALS.G_CHECK_SEGMENT_LEVEL_IN_GROUP,
3360 x_return_status => l_return_status);
3361
3362 END LOOP;
3363
3364 end;
3365 end if; -- grp_no is -1 and operation is update
3366 end if; -- list_type_code is for modifiers
3367 END IF; -- Change in either qualifier_grouping_no or qualifier_attribute
3368 END IF; -- Attribute Manager Installed
3369 --END IF; -- operation is insert or update
3370 -- Attribute Manager change end
3371
3372 --pattern
3373
3374 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
3375
3376 IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE AND
3377 p_QUALIFIERS_rec.list_line_id = -1 THEN
3378 -- header qualifier is added to price list/modifier
3379 IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
3380 qp_delayed_requests_pvt.log_request(
3381 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3382 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3383 p_request_unique_key1 => NULL,
3384 p_request_unique_key2 => 'I',
3385 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3386 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3387 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3388 x_return_status => l_return_status);
3389 ELSE
3390 qp_delayed_requests_pvt.log_request(
3391 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3392 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3393 p_request_unique_key1 => p_QUALIFIERS_rec.qualifier_grouping_no,
3394 p_request_unique_key2 => 'I',
3395 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3396 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3397 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3398 x_return_status => l_return_status);
3399 END IF;
3400 END IF;
3401
3402 IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND
3403 p_QUALIFIERS_rec.list_line_id = -1 THEN
3404 -- header qualifier is modified
3405 IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
3406 p_old_QUALIFIERS_rec.qualifier_grouping_no) THEN
3407 -- updated other than qualifier grouping number
3408 IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
3409 qp_delayed_requests_pvt.log_request(
3410 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3411 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3412 p_request_unique_key1 => NULL,
3413 p_request_unique_key2 => 'U',
3414 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3415 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3416 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3417 x_return_status => l_return_status);
3418 ELSE
3419 qp_delayed_requests_pvt.log_request(
3420 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3421 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3422 p_request_unique_key1 => p_QUALIFIERS_rec.qualifier_grouping_no,
3423 p_request_unique_key2 => 'U',
3424 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3425 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3426 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3427 x_return_status => l_return_status);
3428 END IF;
3429
3430 ELSE
3431 -- qualifier grouping number is modified
3432 IF (p_QUALIFIERS_rec.qualifier_grouping_no = -1 or
3433 p_old_QUALIFIERS_rec.qualifier_grouping_no = -1) THEN
3434 qp_delayed_requests_pvt.log_request(
3435 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3436 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3437 p_request_unique_key1 => NULL,
3438 p_request_unique_key2 => 'U',
3439 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3440 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3441 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3442 x_return_status => l_return_status);
3443 ELSE
3444 qp_delayed_requests_pvt.log_request(
3445 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3446 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3447 p_request_unique_key1 => p_old_QUALIFIERS_rec.qualifier_grouping_no,
3448 p_request_unique_key2 => 'U',
3449 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3450 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3451 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3452 x_return_status => l_return_status);
3453 qp_delayed_requests_pvt.log_request(
3454 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3455 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3456 p_request_unique_key1 => p_QUALIFIERS_rec.qualifier_grouping_no,
3457 p_request_unique_key2 => 'U',
3458 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3459 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3460 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3461 x_return_status => l_return_status);
3462 END IF;
3463 END IF;
3464
3465 END IF;
3466
3467 IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
3468 p_QUALIFIERS_rec.list_line_id = -1 THEN
3469 IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 then
3470 qp_delayed_requests_pvt.log_request(
3471 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3472 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3473 p_request_unique_key1 => NULL,
3474 p_request_unique_key2 => 'D',
3475 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3476 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3477 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3478 x_return_status => l_return_status);
3479 ELSE
3480 qp_delayed_requests_pvt.log_request(
3481 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3482 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3483 p_request_unique_key1 => p_QUALIFIERS_rec.qualifier_grouping_no,
3484 p_request_unique_key2 => 'D',
3485 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3486 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3487 p_request_type => QP_GLOBALS.G_MAINTAIN_HEADER_PATTERN,
3488 x_return_status => l_return_status);
3489 END IF;
3490
3491 END IF;
3492 -- line pattern
3493 IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_CREATE AND
3494 p_QUALIFIERS_rec.list_line_id <> -1 THEN
3495 IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
3496 qp_delayed_requests_pvt.log_request(
3497 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3498 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3499 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3500 p_request_unique_key2 => NULL,
3501 p_request_unique_key3 => 'I',
3502 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3503 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3504 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3505 x_return_status => l_return_status);
3506 ELSE
3507 qp_delayed_requests_pvt.log_request(
3508 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3509 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3510 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3511 p_request_unique_key2 => p_QUALIFIERS_rec.qualifier_grouping_no,
3512 p_request_unique_key3 => 'I',
3513 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3514 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3515 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3516 x_return_status => l_return_status);
3517 END IF;
3518 END IF;
3519
3520 IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND
3521 p_QUALIFIERS_rec.list_line_id <> -1 THEN
3522 IF QP_GLOBALS.Equal(p_QUALIFIERS_rec.qualifier_grouping_no,
3523 p_old_QUALIFIERS_rec.qualifier_grouping_no) THEN
3524 IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
3525 qp_delayed_requests_pvt.log_request(
3526 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3527 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3528 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3529 p_request_unique_key2 => NULL,
3530 p_request_unique_key3 => 'U',
3531 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3532 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3533 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3534 x_return_status => l_return_status);
3535 ELSE
3536 qp_delayed_requests_pvt.log_request(
3537 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3538 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3539 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3540 p_request_unique_key2 => p_QUALIFIERS_rec.qualifier_grouping_no,
3541 p_request_unique_key3 => 'U',
3542 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3543 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3544 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3545 x_return_status => l_return_status);
3546 END IF;
3547
3548 ELSE
3549 IF (p_QUALIFIERS_rec.qualifier_grouping_no = -1 or
3550 p_old_QUALIFIERS_rec.qualifier_grouping_no = -1) THEN
3551 qp_delayed_requests_pvt.log_request(
3552 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3553 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3554 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3555 p_request_unique_key2 => NULL,
3556 p_request_unique_key3 => 'U',
3557 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3558 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3559 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3560 x_return_status => l_return_status);
3561 ELSE
3562 qp_delayed_requests_pvt.log_request(
3563 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3564 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3565 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3566 p_request_unique_key2 => p_old_QUALIFIERS_rec.qualifier_grouping_no,
3567 p_request_unique_key3 => 'U',
3568 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3569 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3570 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3571 x_return_status => l_return_status);
3572 qp_delayed_requests_pvt.log_request(
3573 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3574 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3575 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3576 p_request_unique_key2 => p_QUALIFIERS_rec.qualifier_grouping_no,
3577 p_request_unique_key3 => 'U',
3578 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3579 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3580 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3581 x_return_status => l_return_status);
3582 END IF;
3583 END IF;
3584
3585 END IF;
3586
3587 IF p_QUALIFIERS_rec.operation = QP_GLOBALS.G_OPR_DELETE AND
3588 p_QUALIFIERS_rec.list_line_id <> -1 THEN
3589 IF p_QUALIFIERS_rec.qualifier_grouping_no = -1 THEN
3590 qp_delayed_requests_pvt.log_request(
3591 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3592 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3593 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3594 p_request_unique_key2 => NULL,
3595 p_request_unique_key3 => 'D',
3596 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3597 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3598 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3599 x_return_status => l_return_status);
3600 ELSE
3601 qp_delayed_requests_pvt.log_request(
3602 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3603 p_entity_id => p_QUALIFIERS_rec.list_header_id,
3604 p_request_unique_key1 => p_QUALIFIERS_rec.list_line_id,
3605 p_request_unique_key2 => p_QUALIFIERS_rec.qualifier_grouping_no,
3606 p_request_unique_key3 => 'D',
3607 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3608 p_requesting_entity_id => p_QUALIFIERS_rec.list_header_id,
3609 p_request_type => QP_GLOBALS.G_MAINTAIN_LINE_PATTERN,
3610 x_return_status => l_return_status);
3611 END IF;
3612
3613 END IF;
3614
3615 END IF; --Java Engine Installed
3616 --pattern
3617
3618 EXCEPTION
3619 WHEN FND_API.G_EXC_ERROR THEN
3620 RAISE;
3621 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3622 RAISE;
3623 WHEN OTHERS THEN
3624 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
3625 THEN
3626 oe_msg_pub.Add_Exc_Msg
3627 ( G_PKG_NAME
3628 , 'Pre_Write_Process'
3629 );
3630 END IF;
3631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3632 END Pre_Write_Process;
3633
3634 END QP_Qualifiers_Util;