[Home] [Help]
PACKAGE BODY: APPS.QP_PRICING_ATTR_UTIL
Source
1 PACKAGE BODY QP_Pricing_Attr_Util AS
2 /* $Header: QPXUPRAB.pls 120.14.12020000.2 2012/09/10 11:02:03 smbalara ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Pricing_Attr_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_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
13 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type :=
14 QP_Modifiers_PUB.G_MISS_PRICING_ATTR_REC
15 , x_PRICING_ATTR_rec OUT NOCOPY /* file.sql.39 change */ QP_Modifiers_PUB.Pricing_Attr_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 oe_debug_pub.add('BEGIN Clear_Dependent_Attr in QPXUPRAB');
24
25 -- Load out record
26
27 x_PRICING_ATTR_rec := p_PRICING_ATTR_rec;
28
29 -- If attr_id is missing compare old and new records and for
30 -- every changed attribute clear its dependent fields.
31
32 IF p_attr_id = FND_API.G_MISS_NUM THEN
33
34 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.accumulate_flag,p_old_PRICING_ATTR_rec.accumulate_flag)
35 THEN
36 l_index := l_index + 1;
37 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ACCUMULATE;
38 END IF;
39
40 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute1,p_old_PRICING_ATTR_rec.attribute1)
41 THEN
42 l_index := l_index + 1;
43 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE1;
44 END IF;
45
46 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute10,p_old_PRICING_ATTR_rec.attribute10)
47 THEN
48 l_index := l_index + 1;
49 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE10;
50 END IF;
51
52 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute11,p_old_PRICING_ATTR_rec.attribute11)
53 THEN
54 l_index := l_index + 1;
55 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE11;
56 END IF;
57
58 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute12,p_old_PRICING_ATTR_rec.attribute12)
59 THEN
60 l_index := l_index + 1;
61 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE12;
62 END IF;
63
64 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute13,p_old_PRICING_ATTR_rec.attribute13)
65 THEN
66 l_index := l_index + 1;
67 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE13;
68 END IF;
69
70 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute14,p_old_PRICING_ATTR_rec.attribute14)
71 THEN
72 l_index := l_index + 1;
73 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE14;
74 END IF;
75
76 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute15,p_old_PRICING_ATTR_rec.attribute15)
77 THEN
78 l_index := l_index + 1;
79 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE15;
80 END IF;
81
82 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute2,p_old_PRICING_ATTR_rec.attribute2)
83 THEN
84 l_index := l_index + 1;
85 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE2;
86 END IF;
87
88 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute3,p_old_PRICING_ATTR_rec.attribute3)
89 THEN
90 l_index := l_index + 1;
91 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE3;
92 END IF;
93
94 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute4,p_old_PRICING_ATTR_rec.attribute4)
95 THEN
96 l_index := l_index + 1;
97 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE4;
98 END IF;
99
100 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute5,p_old_PRICING_ATTR_rec.attribute5)
101 THEN
102 l_index := l_index + 1;
103 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE5;
104 END IF;
105
106 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute6,p_old_PRICING_ATTR_rec.attribute6)
107 THEN
108 l_index := l_index + 1;
109 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE6;
110 END IF;
111
112 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute7,p_old_PRICING_ATTR_rec.attribute7)
113 THEN
114 l_index := l_index + 1;
115 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE7;
116 END IF;
117
118 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute8,p_old_PRICING_ATTR_rec.attribute8)
119 THEN
120 l_index := l_index + 1;
121 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE8;
122 END IF;
123
124 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute9,p_old_PRICING_ATTR_rec.attribute9)
125 THEN
126 l_index := l_index + 1;
127 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE9;
128 END IF;
129
130 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute_grouping_no,p_old_PRICING_ATTR_rec.attribute_grouping_no)
131 THEN
132 l_index := l_index + 1;
133 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE_GROUPING_NO;
134 END IF;
135
136 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.context,p_old_PRICING_ATTR_rec.context)
137 THEN
138 l_index := l_index + 1;
139 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_CONTEXT;
140 END IF;
141
142 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.created_by,p_old_PRICING_ATTR_rec.created_by)
143 THEN
144 l_index := l_index + 1;
145 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_CREATED_BY;
146 END IF;
147
148 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.creation_date,p_old_PRICING_ATTR_rec.creation_date)
149 THEN
150 l_index := l_index + 1;
151 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_CREATION_DATE;
152 END IF;
153
154 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.excluder_flag,p_old_PRICING_ATTR_rec.excluder_flag)
155 THEN
156 l_index := l_index + 1;
157 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_EXCLUDER;
158 END IF;
159
160 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_updated_by,p_old_PRICING_ATTR_rec.last_updated_by)
161 THEN
162 l_index := l_index + 1;
163 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LAST_UPDATED_BY;
164 END IF;
165
166 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_update_date,p_old_PRICING_ATTR_rec.last_update_date)
167 THEN
168 l_index := l_index + 1;
169 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LAST_UPDATE_DATE;
170 END IF;
171
172 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_update_login,p_old_PRICING_ATTR_rec.last_update_login)
173 THEN
174 l_index := l_index + 1;
175 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LAST_UPDATE_LOGIN;
176 END IF;
177
178 /*included by spgopal to include list_header_id in pricing attr for performance problems*/
179 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_header_id,p_old_PRICING_ATTR_rec.list_header_id)
180 THEN
181 l_index := l_index + 1;
182 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LIST_HEADER;
183 END IF;
184
185 /*included by spgopal to include pricing_phase_id in pricing attr for performance problems*/
186 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_phase_id,p_old_PRICING_ATTR_rec.pricing_phase_id)
187 THEN
188 l_index := l_index + 1;
189 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_PHASE;
190 END IF;
191
192 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_line_id,p_old_PRICING_ATTR_rec.list_line_id)
193 THEN
194 l_index := l_index + 1;
195 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LIST_LINE;
196 END IF;
197
198 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute,p_old_PRICING_ATTR_rec.pricing_attribute)
199 THEN
200 l_index := l_index + 1;
201 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE;
202 END IF;
203
204 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_context,p_old_PRICING_ATTR_rec.pricing_attribute_context)
205 THEN
206 l_index := l_index + 1;
207 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE_CONTEXT;
208 END IF;
209
210 /*
211 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_id,p_old_PRICING_ATTR_rec.pricing_attribute_id)
212 THEN
213 l_index := l_index + 1;
214 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE;
215 END IF;
216 */
217
218 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_from,p_old_PRICING_ATTR_rec.pricing_attr_value_from)
219 THEN
220 l_index := l_index + 1;
221 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTR_VALUE_FROM;
222 END IF;
223
224 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_to,p_old_PRICING_ATTR_rec.pricing_attr_value_to)
225 THEN
226 l_index := l_index + 1;
227 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTR_VALUE_TO;
228 END IF;
229
230 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute,p_old_PRICING_ATTR_rec.product_attribute)
231 THEN
232 l_index := l_index + 1;
233 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTRIBUTE;
234 END IF;
235
236 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_context,p_old_PRICING_ATTR_rec.product_attribute_context)
237 THEN
238 l_index := l_index + 1;
239 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTRIBUTE_CONTEXT;
240 END IF;
241
242 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attr_value,p_old_PRICING_ATTR_rec.product_attr_value)
243 THEN
244 l_index := l_index + 1;
245 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTR_VALUE;
246 END IF;
247
248 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_uom_code,p_old_PRICING_ATTR_rec.product_uom_code)
249 THEN
250 l_index := l_index + 1;
251 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_UOM;
252 END IF;
253
254 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_application_id,p_old_PRICING_ATTR_rec.program_application_id)
255 THEN
256 l_index := l_index + 1;
257 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PROGRAM_APPLICATION;
258 END IF;
259
260 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_id,p_old_PRICING_ATTR_rec.program_id)
261 THEN
262 l_index := l_index + 1;
263 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PROGRAM;
264 END IF;
265
266 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_update_date,p_old_PRICING_ATTR_rec.program_update_date)
267 THEN
268 l_index := l_index + 1;
269 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PROGRAM_UPDATE_DATE;
270 END IF;
271
272 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.request_id,p_old_PRICING_ATTR_rec.request_id)
273 THEN
274 l_index := l_index + 1;
275 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_REQUEST;
276 END IF;
277
278 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_datatype,p_old_PRICING_ATTR_rec.product_attribute_datatype)
279 THEN
280 l_index := l_index + 1;
281 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTRIBUTE_DATATYPE;
282 END IF;
283
284 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_datatype,p_old_PRICING_ATTR_rec.pricing_attribute_datatype)
285 THEN
286 l_index := l_index + 1;
287 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE_DATATYPE;
288 END IF;
289
290 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.comparison_operator_code,p_old_PRICING_ATTR_rec.comparison_operator_code)
291 THEN
292 l_index := l_index + 1;
293 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_COMPARISON_OPERATOR;
294 END IF;
295
296 ELSIF p_attr_id = G_ACCUMULATE THEN
297 l_index := l_index + 1;
298 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ACCUMULATE;
299 ELSIF p_attr_id = G_ATTRIBUTE1 THEN
300 l_index := l_index + 1;
301 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE1;
302 ELSIF p_attr_id = G_ATTRIBUTE10 THEN
303 l_index := l_index + 1;
304 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE10;
305 ELSIF p_attr_id = G_ATTRIBUTE11 THEN
306 l_index := l_index + 1;
307 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE11;
308 ELSIF p_attr_id = G_ATTRIBUTE12 THEN
309 l_index := l_index + 1;
310 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE12;
311 ELSIF p_attr_id = G_ATTRIBUTE13 THEN
312 l_index := l_index + 1;
313 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE13;
314 ELSIF p_attr_id = G_ATTRIBUTE14 THEN
315 l_index := l_index + 1;
316 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE14;
317 ELSIF p_attr_id = G_ATTRIBUTE15 THEN
318 l_index := l_index + 1;
319 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE15;
320 ELSIF p_attr_id = G_ATTRIBUTE2 THEN
321 l_index := l_index + 1;
322 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE2;
323 ELSIF p_attr_id = G_ATTRIBUTE3 THEN
324 l_index := l_index + 1;
325 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE3;
326 ELSIF p_attr_id = G_ATTRIBUTE4 THEN
327 l_index := l_index + 1;
328 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE4;
329 ELSIF p_attr_id = G_ATTRIBUTE5 THEN
330 l_index := l_index + 1;
331 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE5;
332 ELSIF p_attr_id = G_ATTRIBUTE6 THEN
333 l_index := l_index + 1;
334 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE6;
335 ELSIF p_attr_id = G_ATTRIBUTE7 THEN
336 l_index := l_index + 1;
337 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE7;
338 ELSIF p_attr_id = G_ATTRIBUTE8 THEN
339 l_index := l_index + 1;
340 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE8;
341 ELSIF p_attr_id = G_ATTRIBUTE9 THEN
342 l_index := l_index + 1;
343 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE9;
344 ELSIF p_attr_id = G_ATTRIBUTE_GROUPING_NO THEN
345 l_index := l_index + 1;
346 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_ATTRIBUTE_GROUPING_NO;
347 ELSIF p_attr_id = G_CONTEXT THEN
348 l_index := l_index + 1;
349 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_CONTEXT;
350 ELSIF p_attr_id = G_CREATED_BY THEN
351 l_index := l_index + 1;
352 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_CREATED_BY;
353 ELSIF p_attr_id = G_CREATION_DATE THEN
354 l_index := l_index + 1;
355 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_CREATION_DATE;
356 ELSIF p_attr_id = G_EXCLUDER THEN
357 l_index := l_index + 1;
358 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_EXCLUDER;
359 ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
360 l_index := l_index + 1;
361 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LAST_UPDATED_BY;
362 ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
363 l_index := l_index + 1;
364 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LAST_UPDATE_DATE;
365 ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
366 l_index := l_index + 1;
367 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LAST_UPDATE_LOGIN;
368 ELSIF p_attr_id = G_LIST_LINE THEN
369 l_index := l_index + 1;
370 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_LIST_LINE;
371 ELSIF p_attr_id = G_PRICING_ATTRIBUTE THEN
372 l_index := l_index + 1;
373 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE;
374 ELSIF p_attr_id = G_PRICING_ATTRIBUTE_CONTEXT THEN
375 l_index := l_index + 1;
376 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE_CONTEXT;
377 ELSIF p_attr_id = G_PRICING_ATTRIBUTE THEN
378 l_index := l_index + 1;
379 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE;
380 ELSIF p_attr_id = G_PRICING_ATTR_VALUE_FROM THEN
381 l_index := l_index + 1;
382 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTR_VALUE_FROM;
383 ELSIF p_attr_id = G_PRICING_ATTR_VALUE_TO THEN
384 l_index := l_index + 1;
385 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTR_VALUE_TO;
386 ELSIF p_attr_id = G_PRODUCT_ATTRIBUTE THEN
387 l_index := l_index + 1;
388 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTRIBUTE;
389 ELSIF p_attr_id = G_PRODUCT_ATTRIBUTE_CONTEXT THEN
390 l_index := l_index + 1;
391 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTRIBUTE_CONTEXT;
392 ELSIF p_attr_id = G_PRODUCT_ATTR_VALUE THEN
393 l_index := l_index + 1;
394 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTR_VALUE;
395 ELSIF p_attr_id = G_PRODUCT_UOM THEN
396 l_index := l_index + 1;
397 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_UOM;
398 ELSIF p_attr_id = G_PROGRAM_APPLICATION THEN
399 l_index := l_index + 1;
400 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PROGRAM_APPLICATION;
401 ELSIF p_attr_id = G_PROGRAM THEN
402 l_index := l_index + 1;
403 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PROGRAM;
404 ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
405 l_index := l_index + 1;
406 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PROGRAM_UPDATE_DATE;
407 ELSIF p_attr_id = G_REQUEST THEN
408 l_index := l_index + 1;
409 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_REQUEST;
410 ELSIF p_attr_id = G_PRODUCT_ATTRIBUTE_DATATYPE THEN
411 l_index := l_index + 1;
412 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRODUCT_ATTRIBUTE_DATATYPE;
413 ELSIF p_attr_id = G_PRICING_ATTRIBUTE_DATATYPE THEN
414 l_index := l_index + 1;
415 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_PRICING_ATTRIBUTE_DATATYPE;
416 ELSIF p_attr_id = G_COMPARISON_OPERATOR THEN
417 l_index := l_index + 1;
418 l_src_attr_tbl(l_index) := QP_PRICING_ATTR_UTIL.G_COMPARISON_OPERATOR;
419 END IF;
420
421 oe_debug_pub.add('END Clear_Dependent_Attr in QPXUPRAB');
422
423 END Clear_Dependent_Attr;
424
425 -- Procedure Apply_Attribute_Changes
426
427 PROCEDURE Apply_Attribute_Changes
428 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
429 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type :=
430 QP_Modifiers_PUB.G_MISS_PRICING_ATTR_REC
431 , x_PRICING_ATTR_rec OUT NOCOPY /* file.sql.39 change */ QP_Modifiers_PUB.Pricing_Attr_Rec_Type
432 )
433 IS
434 x_datatype VARCHAR2(1);
435 x_context_flag VARCHAR2(1);
436 x_attribute_flag VARCHAR2(1);
437 x_value_flag VARCHAR2(1);
438 x_precedence NUMBER;
439 x_error_code NUMBER := 0;
440 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
441 --Bug 4706180
442 l_rltd_modifier_grp_type VARCHAR2(30);
443
444 BEGIN
445
446 oe_debug_pub.add('BEGIN Apply_Attribute_Changes in QPXUPRAB');
447
448 -- Load out record
449
450 x_PRICING_ATTR_rec := p_PRICING_ATTR_rec;
451
452 -- Get Product Attribute Datatype
453
454 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_context,
455 p_old_PRICING_ATTR_rec.product_attribute_context)
456 OR NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute,
457 p_old_PRICING_ATTR_rec.product_attribute)
458 OR NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attr_value,
459 p_old_PRICING_ATTR_rec.product_attr_value)
460 THEN
461
462 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
463 ,context =>p_PRICING_ATTR_rec.product_attribute_context
464 ,attribute =>p_PRICING_ATTR_rec.product_attribute
465 ,value =>p_PRICING_ATTR_rec.product_attr_value
466 ,application_short_name => 'QP'
467 ,context_flag =>x_context_flag
468 ,attribute_flag =>x_attribute_flag
469 ,value_flag =>x_value_flag
470 ,datatype =>x_datatype
471 ,precedence =>x_precedence
472 ,error_code =>x_error_code
473 );
474
475 x_PRICING_ATTR_rec.product_attribute_datatype := x_datatype;
476
477 END IF;
478
479 -- Get Pricing Attribute Datatype
480
481 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_context,
482 p_old_PRICING_ATTR_rec.pricing_attribute_context)
483 OR NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute,
484 p_old_PRICING_ATTR_rec.pricing_attribute)
485 OR NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_from,
486 p_old_PRICING_ATTR_rec.pricing_attr_value_from)
487 THEN
488
489 IF p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME'
490 THEN
491
492 x_PRICING_ATTR_rec.pricing_attribute_datatype := 'N';
493
494 ELSE
495
496 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
497 ,context =>p_PRICING_ATTR_rec.pricing_attribute_context
498 ,attribute =>p_PRICING_ATTR_rec.pricing_attribute
499 ,value =>p_PRICING_ATTR_rec.pricing_attr_value_from
500 ,application_short_name => 'QP'
501 ,context_flag =>x_context_flag
502 ,attribute_flag =>x_attribute_flag
503 ,value_flag =>x_value_flag
504 ,datatype =>x_datatype
505 ,precedence =>x_precedence
506 ,error_code =>x_error_code
507 );
508
509 x_PRICING_ATTR_rec.pricing_attribute_datatype := x_datatype;
510
511 END IF;
512 END IF;
513
514 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.accumulate_flag,p_old_PRICING_ATTR_rec.accumulate_flag)
515 THEN
516 NULL;
517 END IF;
518
519 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute1,p_old_PRICING_ATTR_rec.attribute1)
520 THEN
521 NULL;
522 END IF;
523
524 oe_debug_pub.add('11');
525 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute10,p_old_PRICING_ATTR_rec.attribute10)
526 THEN
527 NULL;
528 END IF;
529
530 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute11,p_old_PRICING_ATTR_rec.attribute11)
531 THEN
532 NULL;
533 END IF;
534
535 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute12,p_old_PRICING_ATTR_rec.attribute12)
536 THEN
537 NULL;
538 END IF;
539
540 oe_debug_pub.add('22');
541 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute13,p_old_PRICING_ATTR_rec.attribute13)
542 THEN
543 NULL;
544 END IF;
545
546 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute14,p_old_PRICING_ATTR_rec.attribute14)
547 THEN
548 NULL;
549 END IF;
550
551 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute15,p_old_PRICING_ATTR_rec.attribute15)
552 THEN
553 NULL;
554 END IF;
555
556 oe_debug_pub.add('33');
557 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute2,p_old_PRICING_ATTR_rec.attribute2)
558 THEN
559 NULL;
560 END IF;
561
562 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute3,p_old_PRICING_ATTR_rec.attribute3)
563 THEN
564 NULL;
565 END IF;
566
567 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute4,p_old_PRICING_ATTR_rec.attribute4)
568 THEN
569 NULL;
570 END IF;
571
572 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute5,p_old_PRICING_ATTR_rec.attribute5)
573 THEN
574 NULL;
575 END IF;
576
577 oe_debug_pub.add('44');
578 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute6,p_old_PRICING_ATTR_rec.attribute6)
579 THEN
580 NULL;
581 END IF;
582
583 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute7,p_old_PRICING_ATTR_rec.attribute7)
584 THEN
585 NULL;
586 END IF;
587
588 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute8,p_old_PRICING_ATTR_rec.attribute8)
589 THEN
590 NULL;
591 END IF;
592
593 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute9,p_old_PRICING_ATTR_rec.attribute9)
594 THEN
595 NULL;
596 END IF;
597
598 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute_grouping_no,p_old_PRICING_ATTR_rec.attribute_grouping_no)
599 THEN
600 NULL;
601 END IF;
602
603 oe_debug_pub.add('55');
604 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.context,p_old_PRICING_ATTR_rec.context)
605 THEN
606 NULL;
607 END IF;
608
609 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.created_by,p_old_PRICING_ATTR_rec.created_by)
610 THEN
611 NULL;
612 END IF;
613
614 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.creation_date,p_old_PRICING_ATTR_rec.creation_date)
615 THEN
616 NULL;
617 END IF;
618
619 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.excluder_flag,p_old_PRICING_ATTR_rec.excluder_flag)
620 THEN
621 NULL;
622 END IF;
623
624 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_updated_by,p_old_PRICING_ATTR_rec.last_updated_by)
625 THEN
626 NULL;
627 END IF;
628
629 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_update_date,p_old_PRICING_ATTR_rec.last_update_date)
630 THEN
631 NULL;
632 END IF;
633
634 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_update_login,p_old_PRICING_ATTR_rec.last_update_login)
635 THEN
636 NULL;
637 END IF;
638
639 /*included by spgopal to include list_header_id in pricing attr for performance problems*/
640 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_header_id,p_old_PRICING_ATTR_rec.list_header_id)
641 THEN
642 /* commented this part as it is not reqd- spgopal
643 IF p_PRICING_ATTR_rec.list_header_id IS NULL OR
644 p_PRICING_ATTR_rec.list_header_id = FND_API.G_MISS_NUM THEN
645
646 BEGIN
647
648 oe_debug_pub.add('list_line_id in apply gsp'||to_char(p_PRICING_ATTR_rec.list_line_id));
649 select list_header_id into x_PRICING_ATTR_rec.list_header_id
650 from qp_list_lines
651 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
652 EXCEPTION
653 When NO_DATA_FOUND Then
654 Null;
655 END;
656 ELSE
657 null;
658 END IF;
659 */
660
661 NULL;
662 END IF;
663
664 /*included by spgopal to include pricing_phase_id in pricing attr for performance problems*/
665 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_phase_id,p_old_PRICING_ATTR_rec.pricing_phase_id)
666 THEN
667 /* commented this part as it is not reqd - spgopal
668 IF p_PRICING_ATTR_rec.pricing_phase_id IS NULL OR
669 p_PRICING_ATTR_rec.pricing_phase_id = FND_API.G_MISS_NUM THEN
670
671 BEGIN
672 oe_debug_pub.add('list_line_id in apply gsp'||to_char(p_PRICING_ATTR_rec.list_line_id));
673 select pricing_phase_id into x_PRICING_ATTR_rec.pricing_phase_id
674 from qp_list_lines
675 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
676 EXCEPTION
677 When NO_DATA_FOUND Then
678 Null;
679 END;
680 ELSE
681 null;
682 END IF;
683 */
684
685 NULL;
686 END IF;
687
688 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_line_id,p_old_PRICING_ATTR_rec.list_line_id)
689 THEN
690 qp_delayed_requests_PVT.log_request(
691 p_entity_code => QP_GLOBALS.G_ENTITY_MODIFIERS,
692 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
693 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_MODIFIERS,
694 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
695 p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
696 x_return_status => l_return_status);
697 END IF;
698
699 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute,p_old_PRICING_ATTR_rec.pricing_attribute)
700 THEN
701 NULL;
702 END IF;
703
704 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_context,p_old_PRICING_ATTR_rec.pricing_attribute_context)
705 THEN
706 -- mkarya for bug 1789276, log the request for a change in pricing_attribute_context
707 qp_delayed_requests_PVT.log_request(
708 p_entity_code => QP_GLOBALS.G_ENTITY_MODIFIERS,
709 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
710 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_MODIFIERS,
711 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
712 p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
713 x_return_status => l_return_status);
714 NULL;
715 END IF;
716
717 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_id,p_old_PRICING_ATTR_rec.pricing_attribute_id)
718 THEN
719 NULL;
720 END IF;
721
722 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_from,p_old_PRICING_ATTR_rec.pricing_attr_value_from)
723 THEN
724
725 --Fetch modfier group type (Bug 4706180) Start
726 BEGIN
727 SELECT RLTD_MODIFIER_GRP_TYPE
728 INTO l_rltd_modifier_grp_type
729 FROM qp_rltd_modifiers rm
730 WHERE rm.TO_RLTD_MODIFIER_ID = p_PRICING_ATTR_rec.list_line_id;
731 EXCEPTION
732 WHEN OTHERS THEN
733 l_rltd_modifier_grp_type := '';
734 END;
735 --End
736 --changes made for bug 1566429
737 --for recurring breaks defaulting the value_to to a large number
738 IF p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME'
739 AND p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL
740 AND p_PRICING_ATTR_rec.comparison_operator_code = 'BETWEEN'
741 AND p_PRICING_ATTR_rec.pricing_attr_value_to IS NULL
742 -- Bug 4706180
743 AND l_rltd_modifier_grp_type = 'PRICE BREAK'
744 THEN
745 -- Bug 4706180
746 x_PRICING_ATTR_rec.pricing_attr_value_to := '999999999999999';
747 END IF;
748 NULL;
749 END IF;
750
751 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_to,p_old_PRICING_ATTR_rec.pricing_attr_value_to)
752 THEN
753 NULL;
754 END IF;
755
756 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute,p_old_PRICING_ATTR_rec.product_attribute)
757 THEN
758 NULL;
759 END IF;
760
761 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_context,p_old_PRICING_ATTR_rec.product_attribute_context)
762 THEN
763 -- mkarya for bug 1789276, log the request for a change in product_attribute_context
764 qp_delayed_requests_PVT.log_request(
765 p_entity_code => QP_GLOBALS.G_ENTITY_MODIFIERS,
766 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
767 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_MODIFIERS,
768 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
769 p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
770 x_return_status => l_return_status);
771 NULL;
772 END IF;
773
774 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attr_value,p_old_PRICING_ATTR_rec.product_attr_value)
775 THEN
776 NULL;
777 END IF;
778
779 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_uom_code,p_old_PRICING_ATTR_rec.product_uom_code)
780 THEN
781
782 -- for updating price breaks rassharm bug no 7315129
783 IF p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE
784 THEN
785 UPDATE qp_pricing_attributes
786 SET product_uom_code = p_PRICING_ATTR_rec.product_uom_code
787 WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id;
788
789 -- for updating price breaks rassharm bug no 7315129
790
791 update qp_pricing_attributes
792 SET product_uom_code = p_PRICING_ATTR_rec.product_uom_code
793 where list_line_id in
794 (
795 select to_rltd_modifier_id
796 from qp_list_lines ql, qp_rltd_modifiers qrm
797 where from_rltd_modifier_id= p_PRICING_ATTR_rec.list_line_id
798 AND qrm.to_rltd_modifier_id = ql.list_line_id
799 AND qrm.rltd_modifier_grp_type = 'PRICE BREAK'
800 ) ;
801
802
803
804 END IF;
805 -- NULL;
806 --pattern
807 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
808 IF (p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
809 qp_delayed_requests_pvt.log_request(
810 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
811 p_entity_id => p_PRICING_ATTR_rec.list_header_id,
812 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
813 p_request_unique_key2 => 'UD',
814 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
815 p_requesting_entity_id => p_PRICING_ATTR_rec.list_header_id,
816 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
817 x_return_status => l_return_status);
818 END IF;
819 END IF; --Java Engine Installed
820 --pattern
821 -- jagan's PL/SQL pattern
822 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N' THEN
823 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' THEN
824 IF (p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
825 qp_delayed_requests_pvt.log_request(
826 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
827 p_entity_id => p_PRICING_ATTR_rec.list_header_id,
828 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
829 p_request_unique_key2 => 'UD',
830 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
831 p_requesting_entity_id => p_PRICING_ATTR_rec.list_header_id,
832 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
833 x_return_status => l_return_status);
834 END IF;
835 END IF;
836 END IF;
837 END IF;
838
839 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_application_id,p_old_PRICING_ATTR_rec.program_application_id)
840 THEN
841 NULL;
842 END IF;
843
844 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_id,p_old_PRICING_ATTR_rec.program_id)
845 THEN
846 NULL;
847 END IF;
848
849 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_update_date,p_old_PRICING_ATTR_rec.program_update_date)
850 THEN
851 NULL;
852 END IF;
853
854 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.request_id,p_old_PRICING_ATTR_rec.request_id)
855 THEN
856 NULL;
857 END IF;
858
859 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_datatype,p_old_PRICING_ATTR_rec.product_attribute_datatype)
860 THEN
861 NULL;
862 END IF;
863
864 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_datatype,p_old_PRICING_ATTR_rec.pricing_attribute_datatype)
865 THEN
866 NULL;
867 END IF;
868
869 IF NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.comparison_operator_code,p_old_PRICING_ATTR_rec.comparison_operator_code)
870 THEN
871 NULL;
872 END IF;
873
874 oe_debug_pub.add('END Apply_Attribute_Changes in QPXUPRAB');
875
876 END Apply_Attribute_Changes;
877
878 -- Function Complete_Record
879
880 FUNCTION Complete_Record
881 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
882 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
883 ) RETURN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
884 IS
885 l_PRICING_ATTR_rec QP_Modifiers_PUB.Pricing_Attr_Rec_Type := p_PRICING_ATTR_rec;
886 BEGIN
887
888 oe_debug_pub.add('BEGIN Complete_Record in QPXUPRAB');
889
890 IF l_PRICING_ATTR_rec.accumulate_flag = FND_API.G_MISS_CHAR THEN
891 l_PRICING_ATTR_rec.accumulate_flag := p_old_PRICING_ATTR_rec.accumulate_flag;
892 END IF;
893
894 IF l_PRICING_ATTR_rec.attribute1 = FND_API.G_MISS_CHAR THEN
895 l_PRICING_ATTR_rec.attribute1 := p_old_PRICING_ATTR_rec.attribute1;
896 END IF;
897
898 IF l_PRICING_ATTR_rec.attribute10 = FND_API.G_MISS_CHAR THEN
899 l_PRICING_ATTR_rec.attribute10 := p_old_PRICING_ATTR_rec.attribute10;
900 END IF;
901
902 IF l_PRICING_ATTR_rec.attribute11 = FND_API.G_MISS_CHAR THEN
903 l_PRICING_ATTR_rec.attribute11 := p_old_PRICING_ATTR_rec.attribute11;
904 END IF;
905
906 IF l_PRICING_ATTR_rec.attribute12 = FND_API.G_MISS_CHAR THEN
907 l_PRICING_ATTR_rec.attribute12 := p_old_PRICING_ATTR_rec.attribute12;
908 END IF;
909
910 IF l_PRICING_ATTR_rec.attribute13 = FND_API.G_MISS_CHAR THEN
911 l_PRICING_ATTR_rec.attribute13 := p_old_PRICING_ATTR_rec.attribute13;
912 END IF;
913
914 IF l_PRICING_ATTR_rec.attribute14 = FND_API.G_MISS_CHAR THEN
915 l_PRICING_ATTR_rec.attribute14 := p_old_PRICING_ATTR_rec.attribute14;
916 END IF;
917
918 IF l_PRICING_ATTR_rec.attribute15 = FND_API.G_MISS_CHAR THEN
919 l_PRICING_ATTR_rec.attribute15 := p_old_PRICING_ATTR_rec.attribute15;
920 END IF;
921
922 IF l_PRICING_ATTR_rec.attribute2 = FND_API.G_MISS_CHAR THEN
923 l_PRICING_ATTR_rec.attribute2 := p_old_PRICING_ATTR_rec.attribute2;
924 END IF;
925
926 IF l_PRICING_ATTR_rec.attribute3 = FND_API.G_MISS_CHAR THEN
927 l_PRICING_ATTR_rec.attribute3 := p_old_PRICING_ATTR_rec.attribute3;
928 END IF;
929
930 IF l_PRICING_ATTR_rec.attribute4 = FND_API.G_MISS_CHAR THEN
931 l_PRICING_ATTR_rec.attribute4 := p_old_PRICING_ATTR_rec.attribute4;
932 END IF;
933
934 IF l_PRICING_ATTR_rec.attribute5 = FND_API.G_MISS_CHAR THEN
935 l_PRICING_ATTR_rec.attribute5 := p_old_PRICING_ATTR_rec.attribute5;
936 END IF;
937
938 IF l_PRICING_ATTR_rec.attribute6 = FND_API.G_MISS_CHAR THEN
939 l_PRICING_ATTR_rec.attribute6 := p_old_PRICING_ATTR_rec.attribute6;
940 END IF;
941
942 IF l_PRICING_ATTR_rec.attribute7 = FND_API.G_MISS_CHAR THEN
943 l_PRICING_ATTR_rec.attribute7 := p_old_PRICING_ATTR_rec.attribute7;
944 END IF;
945
946 IF l_PRICING_ATTR_rec.attribute8 = FND_API.G_MISS_CHAR THEN
947 l_PRICING_ATTR_rec.attribute8 := p_old_PRICING_ATTR_rec.attribute8;
948 END IF;
949
950 IF l_PRICING_ATTR_rec.attribute9 = FND_API.G_MISS_CHAR THEN
951 l_PRICING_ATTR_rec.attribute9 := p_old_PRICING_ATTR_rec.attribute9;
952 END IF;
953
954 IF l_PRICING_ATTR_rec.attribute_grouping_no = FND_API.G_MISS_NUM THEN
955 l_PRICING_ATTR_rec.attribute_grouping_no := p_old_PRICING_ATTR_rec.attribute_grouping_no;
956 END IF;
957
958 IF l_PRICING_ATTR_rec.context = FND_API.G_MISS_CHAR THEN
959 l_PRICING_ATTR_rec.context := p_old_PRICING_ATTR_rec.context;
960 END IF;
961
962 IF l_PRICING_ATTR_rec.created_by = FND_API.G_MISS_NUM THEN
963 l_PRICING_ATTR_rec.created_by := p_old_PRICING_ATTR_rec.created_by;
964 END IF;
965
966 IF l_PRICING_ATTR_rec.creation_date = FND_API.G_MISS_DATE THEN
967 l_PRICING_ATTR_rec.creation_date := p_old_PRICING_ATTR_rec.creation_date;
968 END IF;
969
970 IF l_PRICING_ATTR_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
971 l_PRICING_ATTR_rec.excluder_flag := p_old_PRICING_ATTR_rec.excluder_flag;
972 END IF;
973
974 IF l_PRICING_ATTR_rec.last_updated_by = FND_API.G_MISS_NUM THEN
975 l_PRICING_ATTR_rec.last_updated_by := p_old_PRICING_ATTR_rec.last_updated_by;
976 END IF;
977
978 IF l_PRICING_ATTR_rec.last_update_date = FND_API.G_MISS_DATE THEN
979 l_PRICING_ATTR_rec.last_update_date := p_old_PRICING_ATTR_rec.last_update_date;
980 END IF;
981
982 IF l_PRICING_ATTR_rec.last_update_login = FND_API.G_MISS_NUM THEN
983 l_PRICING_ATTR_rec.last_update_login := p_old_PRICING_ATTR_rec.last_update_login;
984 END IF;
985
986 /*included by spgopal to include list_header_id in pricing attr for performance problems*/
987 IF l_PRICING_ATTR_rec.list_header_id = FND_API.G_MISS_NUM THEN
988 l_PRICING_ATTR_rec.list_header_id := p_old_PRICING_ATTR_rec.list_header_id;
989 END IF;
990
991 /*included by spgopal to include pricing_phase_id in pricing attr for performance problems*/
992 IF l_PRICING_ATTR_rec.pricing_phase_id = FND_API.G_MISS_NUM THEN
993 l_PRICING_ATTR_rec.pricing_phase_id := p_old_PRICING_ATTR_rec.pricing_phase_id;
994 END IF;
995
996 IF l_PRICING_ATTR_rec.list_line_id = FND_API.G_MISS_NUM THEN
997 l_PRICING_ATTR_rec.list_line_id := p_old_PRICING_ATTR_rec.list_line_id;
998 END IF;
999
1000 IF l_PRICING_ATTR_rec.pricing_attribute = FND_API.G_MISS_CHAR THEN
1001 l_PRICING_ATTR_rec.pricing_attribute := p_old_PRICING_ATTR_rec.pricing_attribute;
1002 END IF;
1003
1004 IF l_PRICING_ATTR_rec.pricing_attribute_context = FND_API.G_MISS_CHAR THEN
1005 l_PRICING_ATTR_rec.pricing_attribute_context := p_old_PRICING_ATTR_rec.pricing_attribute_context;
1006 END IF;
1007
1008 IF l_PRICING_ATTR_rec.pricing_attribute_id = FND_API.G_MISS_NUM THEN
1009 l_PRICING_ATTR_rec.pricing_attribute_id := p_old_PRICING_ATTR_rec.pricing_attribute_id;
1010 END IF;
1011
1012 IF l_PRICING_ATTR_rec.pricing_attr_value_from = FND_API.G_MISS_CHAR THEN
1013 l_PRICING_ATTR_rec.pricing_attr_value_from := p_old_PRICING_ATTR_rec.pricing_attr_value_from;
1014 END IF;
1015
1016 IF l_PRICING_ATTR_rec.pricing_attr_value_to = FND_API.G_MISS_CHAR THEN
1017 l_PRICING_ATTR_rec.pricing_attr_value_to := p_old_PRICING_ATTR_rec.pricing_attr_value_to;
1018 END IF;
1019
1020 IF l_PRICING_ATTR_rec.product_attribute = FND_API.G_MISS_CHAR THEN
1021 l_PRICING_ATTR_rec.product_attribute := p_old_PRICING_ATTR_rec.product_attribute;
1022 END IF;
1023
1024 IF l_PRICING_ATTR_rec.product_attribute_context = FND_API.G_MISS_CHAR THEN
1025 l_PRICING_ATTR_rec.product_attribute_context := p_old_PRICING_ATTR_rec.product_attribute_context;
1026 END IF;
1027
1028 IF l_PRICING_ATTR_rec.product_attr_value = FND_API.G_MISS_CHAR THEN
1029 l_PRICING_ATTR_rec.product_attr_value := p_old_PRICING_ATTR_rec.product_attr_value;
1030 END IF;
1031
1032 IF l_PRICING_ATTR_rec.product_uom_code = FND_API.G_MISS_CHAR THEN
1033 l_PRICING_ATTR_rec.product_uom_code := p_old_PRICING_ATTR_rec.product_uom_code;
1034 END IF;
1035
1036 IF l_PRICING_ATTR_rec.program_application_id = FND_API.G_MISS_NUM THEN
1037 l_PRICING_ATTR_rec.program_application_id := p_old_PRICING_ATTR_rec.program_application_id;
1038 END IF;
1039
1040 IF l_PRICING_ATTR_rec.program_id = FND_API.G_MISS_NUM THEN
1041 l_PRICING_ATTR_rec.program_id := p_old_PRICING_ATTR_rec.program_id;
1042 END IF;
1043
1044 IF l_PRICING_ATTR_rec.program_update_date = FND_API.G_MISS_DATE THEN
1045 l_PRICING_ATTR_rec.program_update_date := p_old_PRICING_ATTR_rec.program_update_date;
1046 END IF;
1047
1048 IF l_PRICING_ATTR_rec.request_id = FND_API.G_MISS_NUM THEN
1049 l_PRICING_ATTR_rec.request_id := p_old_PRICING_ATTR_rec.request_id;
1050 END IF;
1051
1052 IF l_PRICING_ATTR_rec.product_attribute_datatype = FND_API.G_MISS_CHAR THEN
1053 l_PRICING_ATTR_rec.product_attribute_datatype := p_old_PRICING_ATTR_rec.product_attribute_datatype;
1054 END IF;
1055
1056 IF l_PRICING_ATTR_rec.pricing_attribute_datatype = FND_API.G_MISS_CHAR THEN
1057 l_PRICING_ATTR_rec.pricing_attribute_datatype := p_old_PRICING_ATTR_rec.pricing_attribute_datatype;
1058 END IF;
1059
1060 IF l_PRICING_ATTR_rec.comparison_operator_code = FND_API.G_MISS_CHAR THEN
1061 l_PRICING_ATTR_rec.comparison_operator_code := p_old_PRICING_ATTR_rec.comparison_operator_code;
1062 END IF;
1063
1064 oe_debug_pub.add('END Complete_Record in QPXUPRAB');
1065
1066 RETURN l_PRICING_ATTR_rec;
1067
1068 END Complete_Record;
1069
1070 -- Function Convert_Miss_To_Null
1071
1072 FUNCTION Convert_Miss_To_Null
1073 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1074 ) RETURN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1075 IS
1076 l_PRICING_ATTR_rec QP_Modifiers_PUB.Pricing_Attr_Rec_Type := p_PRICING_ATTR_rec;
1077 BEGIN
1078
1079 oe_debug_pub.add('BEGIN Convert_Miss_To_Null in QPXUPRAB');
1080
1081 IF l_PRICING_ATTR_rec.accumulate_flag = FND_API.G_MISS_CHAR THEN
1082 l_PRICING_ATTR_rec.accumulate_flag := NULL;
1083 END IF;
1084
1085 IF l_PRICING_ATTR_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1086 l_PRICING_ATTR_rec.attribute1 := NULL;
1087 END IF;
1088
1089 IF l_PRICING_ATTR_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1090 l_PRICING_ATTR_rec.attribute10 := NULL;
1091 END IF;
1092
1093 IF l_PRICING_ATTR_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1094 l_PRICING_ATTR_rec.attribute11 := NULL;
1095 END IF;
1096
1097 IF l_PRICING_ATTR_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1098 l_PRICING_ATTR_rec.attribute12 := NULL;
1099 END IF;
1100
1101 IF l_PRICING_ATTR_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1102 l_PRICING_ATTR_rec.attribute13 := NULL;
1103 END IF;
1104
1105 IF l_PRICING_ATTR_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1106 l_PRICING_ATTR_rec.attribute14 := NULL;
1107 END IF;
1108
1109 IF l_PRICING_ATTR_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1110 l_PRICING_ATTR_rec.attribute15 := NULL;
1111 END IF;
1112
1113 IF l_PRICING_ATTR_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1114 l_PRICING_ATTR_rec.attribute2 := NULL;
1115 END IF;
1116
1117 IF l_PRICING_ATTR_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1118 l_PRICING_ATTR_rec.attribute3 := NULL;
1119 END IF;
1120
1121 IF l_PRICING_ATTR_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1122 l_PRICING_ATTR_rec.attribute4 := NULL;
1123 END IF;
1124
1125 IF l_PRICING_ATTR_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1126 l_PRICING_ATTR_rec.attribute5 := NULL;
1127 END IF;
1128
1129 IF l_PRICING_ATTR_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1130 l_PRICING_ATTR_rec.attribute6 := NULL;
1131 END IF;
1132
1133 IF l_PRICING_ATTR_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1134 l_PRICING_ATTR_rec.attribute7 := NULL;
1135 END IF;
1136
1137 IF l_PRICING_ATTR_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1138 l_PRICING_ATTR_rec.attribute8 := NULL;
1139 END IF;
1140
1141 IF l_PRICING_ATTR_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1142 l_PRICING_ATTR_rec.attribute9 := NULL;
1143 END IF;
1144
1145 IF l_PRICING_ATTR_rec.attribute_grouping_no = FND_API.G_MISS_NUM THEN
1146 l_PRICING_ATTR_rec.attribute_grouping_no := NULL;
1147 END IF;
1148
1149 IF l_PRICING_ATTR_rec.context = FND_API.G_MISS_CHAR THEN
1150 l_PRICING_ATTR_rec.context := NULL;
1151 END IF;
1152
1153 IF l_PRICING_ATTR_rec.created_by = FND_API.G_MISS_NUM THEN
1154 l_PRICING_ATTR_rec.created_by := NULL;
1155 END IF;
1156
1157 IF l_PRICING_ATTR_rec.creation_date = FND_API.G_MISS_DATE THEN
1158 l_PRICING_ATTR_rec.creation_date := NULL;
1159 END IF;
1160
1161 IF l_PRICING_ATTR_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
1162 l_PRICING_ATTR_rec.excluder_flag := NULL;
1163 END IF;
1164
1165 IF l_PRICING_ATTR_rec.last_updated_by = FND_API.G_MISS_NUM THEN
1166 l_PRICING_ATTR_rec.last_updated_by := NULL;
1167 END IF;
1168
1169 IF l_PRICING_ATTR_rec.last_update_date = FND_API.G_MISS_DATE THEN
1170 l_PRICING_ATTR_rec.last_update_date := NULL;
1171 END IF;
1172
1173 IF l_PRICING_ATTR_rec.last_update_login = FND_API.G_MISS_NUM THEN
1174 l_PRICING_ATTR_rec.last_update_login := NULL;
1175 END IF;
1176
1177 /*included by spgopal to include list_header_id in pricing attr for performance problems*/
1178 IF l_PRICING_ATTR_rec.list_header_id = FND_API.G_MISS_NUM THEN
1179 l_PRICING_ATTR_rec.list_header_id := NULL;
1180 END IF;
1181
1182 /*included by spgopal to include pricing_phase_id in pricing attr for performance problems*/
1183 IF l_PRICING_ATTR_rec.pricing_phase_id = FND_API.G_MISS_NUM THEN
1184 l_PRICING_ATTR_rec.pricing_phase_id := NULL;
1185 END IF;
1186
1187 IF l_PRICING_ATTR_rec.list_line_id = FND_API.G_MISS_NUM THEN
1188 l_PRICING_ATTR_rec.list_line_id := NULL;
1189 END IF;
1190
1191 IF l_PRICING_ATTR_rec.pricing_attribute = FND_API.G_MISS_CHAR THEN
1192 l_PRICING_ATTR_rec.pricing_attribute := NULL;
1193 END IF;
1194
1195 IF l_PRICING_ATTR_rec.pricing_attribute_context = FND_API.G_MISS_CHAR THEN
1196 l_PRICING_ATTR_rec.pricing_attribute_context := NULL;
1197 END IF;
1198
1199 IF l_PRICING_ATTR_rec.pricing_attribute_id = FND_API.G_MISS_NUM THEN
1200 l_PRICING_ATTR_rec.pricing_attribute_id := NULL;
1201 END IF;
1202
1203 IF l_PRICING_ATTR_rec.pricing_attr_value_from = FND_API.G_MISS_CHAR THEN
1204 l_PRICING_ATTR_rec.pricing_attr_value_from := NULL;
1205 END IF;
1206
1207 IF l_PRICING_ATTR_rec.pricing_attr_value_to = FND_API.G_MISS_CHAR THEN
1208 l_PRICING_ATTR_rec.pricing_attr_value_to := NULL;
1209 END IF;
1210
1211 IF l_PRICING_ATTR_rec.product_attribute = FND_API.G_MISS_CHAR THEN
1212 l_PRICING_ATTR_rec.product_attribute := NULL;
1213 END IF;
1214
1215 IF l_PRICING_ATTR_rec.product_attribute_context = FND_API.G_MISS_CHAR THEN
1216 l_PRICING_ATTR_rec.product_attribute_context := NULL;
1217 END IF;
1218
1219 IF l_PRICING_ATTR_rec.product_attr_value = FND_API.G_MISS_CHAR THEN
1220 l_PRICING_ATTR_rec.product_attr_value := NULL;
1221 END IF;
1222
1223 IF l_PRICING_ATTR_rec.product_uom_code = FND_API.G_MISS_CHAR THEN
1224 l_PRICING_ATTR_rec.product_uom_code := NULL;
1225 END IF;
1226
1227 IF l_PRICING_ATTR_rec.program_application_id = FND_API.G_MISS_NUM THEN
1228 l_PRICING_ATTR_rec.program_application_id := NULL;
1229 END IF;
1230
1231 IF l_PRICING_ATTR_rec.program_id = FND_API.G_MISS_NUM THEN
1232 l_PRICING_ATTR_rec.program_id := NULL;
1233 END IF;
1234
1235 IF l_PRICING_ATTR_rec.program_update_date = FND_API.G_MISS_DATE THEN
1236 l_PRICING_ATTR_rec.program_update_date := NULL;
1237 END IF;
1238
1239 IF l_PRICING_ATTR_rec.request_id = FND_API.G_MISS_NUM THEN
1240 l_PRICING_ATTR_rec.request_id := NULL;
1241 END IF;
1242
1243 IF l_PRICING_ATTR_rec.product_attribute_datatype = FND_API.G_MISS_CHAR THEN
1244 l_PRICING_ATTR_rec.product_attribute_datatype := NULL;
1245 END IF;
1246
1247 IF l_PRICING_ATTR_rec.pricing_attribute_datatype = FND_API.G_MISS_CHAR THEN
1248 l_PRICING_ATTR_rec.pricing_attribute_datatype := NULL;
1249 END IF;
1250
1251 IF l_PRICING_ATTR_rec.comparison_operator_code = FND_API.G_MISS_CHAR THEN
1252 l_PRICING_ATTR_rec.comparison_operator_code := NULL;
1253 END IF;
1254
1255 oe_debug_pub.add('END Convert_Miss_To_Null in QPXUPRAB');
1256
1257 RETURN l_PRICING_ATTR_rec;
1258
1259 END Convert_Miss_To_Null;
1260
1261
1262 ----------------------------------------------------------------------------
1263 --PRIVATE PROCEDURE UPDATE CHILD PRICING ATTR TO UPDATE
1264 --CHILD BREAK LINES' PRICING ATTRIBUTE RECORDS
1265 ----------------------------------------------------------------------------
1266
1267
1268 PROCEDURE UPDATE_CHILD_PRICING_ATTR(p_PRICING_ATTR_rec IN QP_MODIFIERS_PUB.Pricing_Attr_rec_type) IS
1269
1270 l_status NUMBER;
1271 l_list_line_id NUMBER;
1272 l_list_line_type_code VARCHAR2(30);
1273 l_Pricing_Attr_rec QP_PRICING_ATTRIBUTES%rowtype;
1274
1275 Cursor C_child_records(l_list_line_id number) IS
1276 SELECT *
1277 FROM QP_PRICING_ATTRIBUTES qll
1278 WHERE qll.list_line_id IN (select
1279 to_rltd_modifier_id from
1280 qp_rltd_modifiers qrm where from_rltd_modifier_id
1281 = l_list_line_id);
1282
1283 BEGIN
1284
1285 l_list_line_id := p_Pricing_Attr_rec.list_line_id;
1286
1287
1288 select list_line_type_code into l_list_line_type_code
1289 from qp_list_lines where
1290 list_line_id = l_list_line_id;
1291
1292 IF l_list_line_type_code = 'PBH' THEN
1293
1294 --l_modifier_grp_type := 'PRICE BREAK';
1295
1296 --updating all child break pricing_attributes
1297
1298 open C_child_records(l_list_line_id); LOOP
1299 fetch C_child_records into l_Pricing_Attr_rec;
1300
1301 EXIT WHEN C_child_records%NOTFOUND;
1302
1303
1304
1305 update qp_Pricing_Attributes set
1306 Product_attribute_context =
1307 p_Pricing_Attr_rec.Product_attribute_context
1308 ,Product_attribute =
1309 p_Pricing_Attr_rec.Product_attribute
1310 ,Product_attr_value =
1311 p_Pricing_Attr_rec.Product_attr_value
1312 ,Pricing_Attribute_context =
1313 p_Pricing_Attr_rec.Pricing_Attribute_context
1314 ,Pricing_Attribute =
1315 p_Pricing_Attr_rec.Pricing_Attribute
1316 where list_line_id = l_Pricing_Attr_rec.list_line_id;
1317
1318 END LOOP;
1319
1320 close C_child_records;
1321 /*
1322 --No need to update pricing attr for OID and PRG as nothing is defaulted
1323 ELSIF l_list_line_type_code IN ( 'OID','PRG') THEN
1324
1325 -- l_modifier_grp_type := '('BENEFIT', 'QUALIFIER')';
1326
1327
1328
1329 --update OID child records
1330
1331 open C_child_records(l_list_line_id); LOOP
1332 fetch C_child_records into l_Pricing_Attr_rec;
1333
1334 EXIT WHEN C_child_records%NOTFOUND;
1335
1336 --get or related records
1337
1338
1339 update qp_list_lines set
1340 list_line_no = p_Pricing_Attr_rec.list_line_no
1341 ,modifier_level_code = p_Pricing_Attr_rec.modifier_level_code
1342 ,automatic_flag = p_Pricing_Attr_rec.automatic_flag
1343 ,override_flag = p_Pricing_Attr_rec.override_flag
1344 ,Print_on_invoice_flag = p_Pricing_Attr_rec.Print_on_invoice_flag
1345 ,price_break_type_code = p_Pricing_Attr_rec.price_break_type_code
1346 ,Proration_type_code = p_Pricing_Attr_rec.Proration_type_code
1347 ,Incompatibility_Grp_code= p_Pricing_Attr_rec.Incompatibility_Grp_code
1348 ,Pricing_group_sequence = p_Pricing_Attr_rec.Pricing_group_sequence
1349 ,accrual_flag = p_Pricing_Attr_rec.accrual_flag
1350 ,estim_accrual_rate = p_Pricing_Attr_rec.estim_accrual_rate
1351 ,rebate_transaction_type_code = p_Pricing_Attr_rec.rebate_trxn_type_code
1352 ,expiration_date = p_Pricing_Attr_rec.expiration_date
1353 ,expiration_period_start_date = p_Pricing_Attr_rec.expiration_period_start_date
1354 ,expiration_period_uom = p_Pricing_Attr_rec.expiration_period_uom
1355 ,number_expiration_periods = p_Pricing_Attr_rec.number_expiration_periods
1356
1357 where list_line_id = l_Pricing_Attr_rec.list_line_id;
1358
1359
1360
1361
1362
1363 END LOOP;
1364
1365 close C_child_records;
1366 */
1367
1368 ELSE
1369
1370 null;
1371
1372 END IF;
1373
1374 EXCEPTION
1375
1376 WHEN OTHERS THEN
1377
1378 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1379 THEN
1380 OE_MSG_PUB.Add_Exc_Msg
1381 ( G_PKG_NAME
1382 , 'Update_Child_Pricing_Attr'
1383 );
1384 END IF;
1385
1386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1387
1388
1389
1390
1391
1392 END UPDATE_CHILD_PRICING_ATTR;
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407 ----------------------------------------------------------------------------
1408
1409
1410 -- Procedure Update_Row
1411
1412 PROCEDURE Update_Row
1413 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1414 )
1415 IS
1416
1417 l_list_line_type_code VARCHAR2(30);
1418 l_pric_attr_value_from_number NUMBER := NULL;
1419 l_pric_attr_value_to_number NUMBER := NULL;
1420 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1421 l_check_active_flag VARCHAR2(1);
1422 l_active_flag VARCHAR2(1);
1423 l_rltd_modifier_grp_type VARCHAR2(30);
1424 l_pricing_attr_value_to p_Pricing_Attr_rec.pricing_attr_value_to%TYPE;
1425
1426 BEGIN
1427
1428 --Fetch modfier group type (Bug 5465263) Start
1429 BEGIN
1430 SELECT RLTD_MODIFIER_GRP_TYPE
1431 INTO l_rltd_modifier_grp_type
1432 FROM qp_rltd_modifiers rm
1433 WHERE rm.TO_RLTD_MODIFIER_ID = p_PRICING_ATTR_rec.list_line_id;
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 l_rltd_modifier_grp_type := '';
1437 END;
1438
1439 --If Price Break value to is null and value from is not (the last price break) set
1440 --value to 999..99 (15 digit) to incorporate infinite values
1441 IF(p_Pricing_Attr_rec.pricing_attr_value_to IS NULL
1442 AND p_Pricing_Attr_rec.pricing_attr_value_from IS NOT NULL
1443 AND l_rltd_modifier_grp_type = 'PRICE BREAK'
1444 AND p_PRICING_ATTR_rec.pricing_attribute_context = 'VOLUME')
1445 THEN
1446 l_pricing_attr_value_to := '999999999999999';
1447 ELSE
1448 l_pricing_attr_value_to := p_Pricing_Attr_rec.pricing_attr_value_to;
1449 END IF;
1450
1451 --End Bug 5465263
1452
1453 SELECT lh.ACTIVE_FLAG
1454 INTO l_active_flag
1455 FROM QP_LIST_HEADERS_B lh, QP_LIST_LINES ll
1456 WHERE lh.LIST_HEADER_ID = ll.LIST_HEADER_ID and ll.LIST_LINE_ID = p_PRICING_ATTR_rec.list_line_id and rownum = 1;
1457
1458 oe_debug_pub.add('BEGIN Update_Row in QPXUPRAB');
1459
1460 IF p_PRICING_ATTR_rec.pricing_attribute_datatype = 'N'
1461 then
1462
1463 BEGIN
1464
1465 l_pric_attr_value_from_number :=
1466 qp_number.canonical_to_number(p_PRICING_ATTR_rec.pricing_attr_value_from);
1467
1468 l_pric_attr_value_to_number :=
1469 qp_number.canonical_to_number(p_PRICING_ATTR_rec.pricing_attr_value_to);
1470
1471 EXCEPTION
1472 WHEN VALUE_ERROR THEN
1473 NULL;
1474 WHEN OTHERS THEN
1475 NULL;
1476 END;
1477
1478 end if;
1479
1480 /*changes by spgopal to include list_header_id ,pricing_phase_id in pricing attr for performance problems*/
1481
1482 UPDATE QP_PRICING_ATTRIBUTES
1483 SET ACCUMULATE_FLAG = p_PRICING_ATTR_rec.accumulate_flag
1484 , ATTRIBUTE1 = p_PRICING_ATTR_rec.attribute1
1485 , ATTRIBUTE10 = p_PRICING_ATTR_rec.attribute10
1486 , ATTRIBUTE11 = p_PRICING_ATTR_rec.attribute11
1487 , ATTRIBUTE12 = p_PRICING_ATTR_rec.attribute12
1488 , ATTRIBUTE13 = p_PRICING_ATTR_rec.attribute13
1489 , ATTRIBUTE14 = p_PRICING_ATTR_rec.attribute14
1490 , ATTRIBUTE15 = p_PRICING_ATTR_rec.attribute15
1491 , ATTRIBUTE2 = p_PRICING_ATTR_rec.attribute2
1492 , ATTRIBUTE3 = p_PRICING_ATTR_rec.attribute3
1493 , ATTRIBUTE4 = p_PRICING_ATTR_rec.attribute4
1494 , ATTRIBUTE5 = p_PRICING_ATTR_rec.attribute5
1495 , ATTRIBUTE6 = p_PRICING_ATTR_rec.attribute6
1496 , ATTRIBUTE7 = p_PRICING_ATTR_rec.attribute7
1497 , ATTRIBUTE8 = p_PRICING_ATTR_rec.attribute8
1498 , ATTRIBUTE9 = p_PRICING_ATTR_rec.attribute9
1499 , ATTRIBUTE_GROUPING_NO = p_PRICING_ATTR_rec.attribute_grouping_no
1500 , CONTEXT = p_PRICING_ATTR_rec.context
1501 , CREATED_BY = p_PRICING_ATTR_rec.created_by
1502 , CREATION_DATE = p_PRICING_ATTR_rec.creation_date
1503 , EXCLUDER_FLAG = p_PRICING_ATTR_rec.excluder_flag
1504 , LAST_UPDATED_BY = p_PRICING_ATTR_rec.last_updated_by
1505 , LAST_UPDATE_DATE = p_PRICING_ATTR_rec.last_update_date
1506 , LAST_UPDATE_LOGIN = p_PRICING_ATTR_rec.last_update_login
1507 , LIST_LINE_ID = p_PRICING_ATTR_rec.list_line_id
1508 , PRICING_ATTRIBUTE = p_PRICING_ATTR_rec.pricing_attribute
1509 , PRICING_ATTRIBUTE_CONTEXT = p_PRICING_ATTR_rec.pricing_attribute_context
1510 , PRICING_ATTRIBUTE_ID = p_PRICING_ATTR_rec.pricing_attribute_id
1511 , PRICING_ATTR_VALUE_FROM = p_PRICING_ATTR_rec.pricing_attr_value_from
1512 , PRICING_ATTR_VALUE_TO = l_pricing_attr_value_to
1513 , PRODUCT_ATTRIBUTE = p_PRICING_ATTR_rec.product_attribute
1514 , PRODUCT_ATTRIBUTE_CONTEXT = p_PRICING_ATTR_rec.product_attribute_context
1515 , PRODUCT_ATTR_VALUE = p_PRICING_ATTR_rec.product_attr_value
1516 , PRODUCT_UOM_CODE = p_PRICING_ATTR_rec.product_uom_code
1517 , PROGRAM_APPLICATION_ID = p_PRICING_ATTR_rec.program_application_id
1518 , PROGRAM_ID = p_PRICING_ATTR_rec.program_id
1519 , PROGRAM_UPDATE_DATE = p_PRICING_ATTR_rec.program_update_date
1520 , REQUEST_ID = p_PRICING_ATTR_rec.request_id
1521 , PRODUCT_ATTRIBUTE_DATATYPE = p_PRICING_ATTR_rec.product_attribute_datatype
1522 , PRICING_ATTRIBUTE_DATATYPE = p_PRICING_ATTR_rec.pricing_attribute_datatype
1523 , COMPARISON_OPERATOR_CODE = p_PRICING_ATTR_rec.comparison_operator_code
1524 , LIST_HEADER_ID = p_PRICING_ATTR_rec.list_header_id
1525 , PRICING_PHASE_ID = p_PRICING_ATTR_rec.pricing_phase_id
1526 , PRICING_ATTR_VALUE_FROM_NUMBER = l_pric_attr_value_from_number
1527 , PRICING_ATTR_VALUE_TO_NUMBER = l_pric_attr_value_to_number
1528 WHERE PRICING_ATTRIBUTE_ID = p_PRICING_ATTR_rec.pricing_attribute_id
1529 ;
1530
1531 l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
1532 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND l_active_flag='Y') THEN
1533
1534 IF(p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL) AND
1535 (p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL) THEN
1536
1537 UPDATE qp_pte_segments set used_in_setup='Y'
1538 WHERE nvl(used_in_setup,'N')='N'
1539 AND segment_id IN
1540 (SELECT a.segment_id FROM qp_segments_b a,qp_prc_contexts_b b
1541 WHERE a.segment_mapping_column=p_PRICING_ATTR_rec.pricing_attribute
1542 AND a.prc_context_id=b.prc_context_id
1543 AND b.prc_context_type = 'PRICING_ATTRIBUTE'
1544 AND b.prc_context_code=p_PRICING_ATTR_rec.pricing_attribute_context);
1545
1546 END IF;
1547
1548 IF(p_PRICING_ATTR_rec.product_attribute_context IS NOT NULL) AND
1549 (p_PRICING_ATTR_rec.product_attribute IS NOT NULL) THEN
1550
1551 UPDATE qp_pte_segments set used_in_setup='Y'
1552 WHERE nvl(used_in_setup,'N')='N'
1553 AND segment_id IN
1554 (SELECT a.segment_id FROM qp_segments_b a,qp_prc_contexts_b b
1555 WHERE a.segment_mapping_column=p_PRICING_ATTR_rec.product_attribute
1556 AND a.prc_context_id=b.prc_context_id
1557 AND b.prc_context_type = 'PRODUCT'
1558 AND b.prc_context_code=p_PRICING_ATTR_rec.product_attribute_context);
1559
1560 END IF;
1561 END IF;
1562 --to update child break lines' priceing attribute records
1563 update_child_pricing_attr(p_PRICING_ATTR_rec);
1564
1565 --Fetch list line type code
1566 BEGIN
1567 SELECT list_line_type_code
1568 INTO l_list_line_type_code
1569 FROM qp_list_lines
1570 WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id;
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 l_list_line_type_code := '';
1574 END;
1575
1576 --Log delayed request to maintain denormalized pricing attrs columns only
1577 --for formula factor attrs
1578 IF l_list_line_type_code = 'PMR' THEN
1579 qp_delayed_requests_PVT.log_request(
1580 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
1581 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
1582 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_Modifiers,
1583 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
1584 p_request_type =>QP_GLOBALS.G_MAINTAIN_FACTOR_LIST_ATTRS,
1585 x_return_status => l_return_status);
1586 END IF;
1587
1588 oe_debug_pub.add('END Update_Row in QPXUPRAB');
1589
1590 EXCEPTION
1591
1592 WHEN OTHERS THEN
1593
1594 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1595 THEN
1596 OE_MSG_PUB.Add_Exc_Msg
1597 ( G_PKG_NAME
1598 , 'Update_Row'
1599 );
1600 END IF;
1601
1602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1603
1604 END Update_Row;
1605
1606 -- Procedure Insert_Row
1607
1608 PROCEDURE Insert_Row
1609 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1610 )
1611 IS
1612 l_context_flag VARCHAR2(1);
1613 l_attribute_flag VARCHAR2(1);
1614 l_value_flag VARCHAR2(1);
1615 l_datatype VARCHAR2(1);
1616 l_precedence NUMBER;
1617 l_error_code NUMBER := 0;
1618 l_return_status VARCHAR2(1);
1619 l_pric_attr_value_from_number NUMBER := NULL;
1620 l_pric_attr_value_to_number NUMBER := NULL;
1621 l_list_line_type_code VARCHAR2(30);
1622 l_check_active_flag VARCHAR2(1);
1623 l_active_flag VARCHAR2(1);
1624 l_pricing_attr_value_to p_Pricing_Attr_rec.pricing_attr_value_to%TYPE;
1625 --Bug 4706180
1626 l_rltd_modifier_grp_type VARCHAR2(30);
1627 BEGIN
1628
1629
1630 oe_debug_pub.add('SMITHA LIST_HEADER_ID'||p_PRICING_ATTR_rec.list_header_id);
1631
1632 --Fetch modfier group type (Bug 4706180) Start
1633 BEGIN
1634 SELECT RLTD_MODIFIER_GRP_TYPE
1635 INTO l_rltd_modifier_grp_type
1636 FROM qp_rltd_modifiers rm
1637 WHERE rm.TO_RLTD_MODIFIER_ID = p_PRICING_ATTR_rec.list_line_id;
1638 EXCEPTION
1639 WHEN OTHERS THEN
1640 l_rltd_modifier_grp_type := '';
1641 END;
1642 --End
1643
1644 IF p_PRICING_ATTR_rec.list_header_id IS NOT NULL THEN
1645 SELECT ACTIVE_FLAG
1646 INTO l_active_flag
1647 FROM QP_LIST_HEADERS_B
1648 WHERE LIST_HEADER_ID = p_PRICING_ATTR_rec.list_header_id;
1649
1650 ELSE
1651
1652 SELECT ACTIVE_FLAG
1653 INTO l_active_flag
1654 FROM QP_LIST_HEADERS_B a,QP_LIST_LINES b
1655 WHERE b.list_line_id=p_PRICING_ATTR_rec.list_line_id
1656 AND b.LIST_HEADER_ID = a.list_header_id;
1657 END IF;
1658 oe_debug_pub.add('BEGIN Insert_Row in QPXUPRAB');
1659 oe_debug_pub.add(p_PRICING_ATTR_rec.attribute_grouping_no);
1660 oe_debug_pub.add(p_PRICING_ATTR_rec.product_attribute_context);
1661 oe_debug_pub.add(p_PRICING_ATTR_rec.product_attribute);
1662 oe_debug_pub.add(p_PRICING_ATTR_rec.pricing_attribute);
1663 oe_debug_pub.add(p_PRICING_ATTR_rec.excluder_flag);
1664
1665 --If Price Break value to is null and value from is not (the last price break) set
1666 --value to 999..99 (15 digit) to incorporate infinite values
1667 IF(p_Pricing_Attr_rec.pricing_attr_value_to IS NULL
1668 AND p_Pricing_Attr_rec.pricing_attr_value_from IS NOT NULL
1669 -- Bug 4706180
1670 AND l_rltd_modifier_grp_type = 'PRICE BREAK')
1671 THEN
1672 l_pricing_attr_value_to := '999999999999999';
1673 ELSE
1674 l_pricing_attr_value_to := p_Pricing_Attr_rec.pricing_attr_value_to;
1675 END IF;
1676
1677 IF p_PRICING_ATTR_rec.pricing_attribute_datatype = 'N'
1678 then
1679
1680 BEGIN
1681
1682 l_pric_attr_value_from_number :=
1683 qp_number.canonical_to_number(p_PRICING_ATTR_rec.pricing_attr_value_from);
1684
1685 l_pric_attr_value_to_number :=
1686 qp_number.canonical_to_number(p_PRICING_ATTR_rec.pricing_attr_value_to);
1687
1688 EXCEPTION
1689 WHEN VALUE_ERROR THEN
1690 NULL;
1691 WHEN OTHERS THEN
1692 NULL;
1693 END;
1694
1695 end if;
1696
1697 /*changs by spgopal to include list_header_id ,pricing_phase_id in pricing attr for performance problems*/
1698
1699 INSERT INTO QP_PRICING_ATTRIBUTES
1700 (
1701 --ACCUMULATE_FLAG
1702 ATTRIBUTE1
1703 , ATTRIBUTE10
1704 , ATTRIBUTE11
1705 , ATTRIBUTE12
1706 , ATTRIBUTE13
1707 , ATTRIBUTE14
1708 , ATTRIBUTE15
1709 , ATTRIBUTE2
1710 , ATTRIBUTE3
1711 , ATTRIBUTE4
1712 , ATTRIBUTE5
1713 , ATTRIBUTE6
1714 , ATTRIBUTE7
1715 , ATTRIBUTE8
1716 , ATTRIBUTE9
1717 , ATTRIBUTE_GROUPING_NO
1718 , CONTEXT
1719 , CREATED_BY
1720 , CREATION_DATE
1721 , EXCLUDER_FLAG
1722 , LAST_UPDATED_BY
1723 , LAST_UPDATE_DATE
1724 , LAST_UPDATE_LOGIN
1725 , LIST_LINE_ID
1726 , PRICING_ATTRIBUTE
1727 , PRICING_ATTRIBUTE_CONTEXT
1728 , PRICING_ATTRIBUTE_ID
1729 , PRICING_ATTR_VALUE_FROM
1730 , PRICING_ATTR_VALUE_TO
1731 , PRODUCT_ATTRIBUTE
1732 , PRODUCT_ATTRIBUTE_CONTEXT
1733 , PRODUCT_ATTR_VALUE
1734 , PRODUCT_UOM_CODE
1735 , PROGRAM_APPLICATION_ID
1736 , PROGRAM_ID
1737 , PROGRAM_UPDATE_DATE
1738 , REQUEST_ID
1739 , PRODUCT_ATTRIBUTE_DATATYPE
1740 , PRICING_ATTRIBUTE_DATATYPE
1741 , COMPARISON_OPERATOR_CODE
1742 , LIST_HEADER_ID
1743 , PRICING_PHASE_ID
1744 , PRICING_ATTR_VALUE_FROM_NUMBER
1745 , PRICING_ATTR_VALUE_TO_NUMBER
1746 )
1747 VALUES
1748 (
1749 --p_PRICING_ATTR_rec.accumulate_flag
1750 p_PRICING_ATTR_rec.attribute1
1751 , p_PRICING_ATTR_rec.attribute10
1752 , p_PRICING_ATTR_rec.attribute11
1753 , p_PRICING_ATTR_rec.attribute12
1754 , p_PRICING_ATTR_rec.attribute13
1755 , p_PRICING_ATTR_rec.attribute14
1756 , p_PRICING_ATTR_rec.attribute15
1757 , p_PRICING_ATTR_rec.attribute2
1758 , p_PRICING_ATTR_rec.attribute3
1759 , p_PRICING_ATTR_rec.attribute4
1760 , p_PRICING_ATTR_rec.attribute5
1761 , p_PRICING_ATTR_rec.attribute6
1762 , p_PRICING_ATTR_rec.attribute7
1763 , p_PRICING_ATTR_rec.attribute8
1764 , p_PRICING_ATTR_rec.attribute9
1765 , p_PRICING_ATTR_rec.attribute_grouping_no
1766 , p_PRICING_ATTR_rec.context
1767 , p_PRICING_ATTR_rec.created_by
1768 , p_PRICING_ATTR_rec.creation_date
1769 , p_PRICING_ATTR_rec.excluder_flag
1770 , p_PRICING_ATTR_rec.last_updated_by
1771 , p_PRICING_ATTR_rec.last_update_date
1772 , p_PRICING_ATTR_rec.last_update_login
1773 , p_PRICING_ATTR_rec.list_line_id
1774 , p_PRICING_ATTR_rec.pricing_attribute
1775 , p_PRICING_ATTR_rec.pricing_attribute_context
1776 , p_PRICING_ATTR_rec.pricing_attribute_id
1777 , p_PRICING_ATTR_rec.pricing_attr_value_from
1778 , l_pricing_attr_value_to
1779 , p_PRICING_ATTR_rec.product_attribute
1780 , p_PRICING_ATTR_rec.product_attribute_context
1781 , p_PRICING_ATTR_rec.product_attr_value
1782 , p_PRICING_ATTR_rec.product_uom_code
1783 , p_PRICING_ATTR_rec.program_application_id
1784 , p_PRICING_ATTR_rec.program_id
1785 , p_PRICING_ATTR_rec.program_update_date
1786 , p_PRICING_ATTR_rec.request_id
1787 , p_PRICING_ATTR_rec.product_attribute_datatype
1788 , p_PRICING_ATTR_rec.pricing_attribute_datatype
1789 , p_PRICING_ATTR_rec.comparison_operator_code
1790 , p_PRICING_ATTR_rec.list_header_id
1791 , p_PRICING_ATTR_rec.pricing_phase_id
1792 , l_pric_attr_value_from_number
1793 , l_pric_attr_value_to_number
1794 );
1795
1796 l_check_active_flag:=nvl(fnd_profile.value('QP_BUILD_ATTRIBUTES_MAPPING_OPTIONS'),'N');
1797 IF (l_check_active_flag='N') OR (l_check_active_flag='Y' AND l_active_flag='Y') THEN
1798 IF(p_PRICING_ATTR_rec.pricing_attribute_context IS NOT NULL) AND
1799 (p_PRICING_ATTR_rec.pricing_attribute IS NOT NULL) THEN
1800
1801 UPDATE qp_pte_segments set used_in_setup='Y'
1802 WHERE nvl(used_in_setup,'N')='N'
1803 AND segment_id IN
1804 (SELECT a.segment_id FROM qp_segments_b a,qp_prc_contexts_b b
1805 WHERE a.segment_mapping_column=p_PRICING_ATTR_rec.pricing_attribute
1806 AND a.prc_context_id=b.prc_context_id
1807 AND b.prc_context_type = 'PRICING_ATTRIBUTE'
1808 AND b.prc_context_code=p_PRICING_ATTR_rec.pricing_attribute_context);
1809
1810 END IF;
1811
1812
1813 IF(p_PRICING_ATTR_rec.product_attribute_context IS NOT NULL) AND
1814 (p_PRICING_ATTR_rec.product_attribute IS NOT NULL) THEN
1815
1816 UPDATE qp_pte_segments set used_in_setup='Y'
1817 WHERE nvl(used_in_setup,'N')='N'
1818 AND segment_id IN
1819 (SELECT a.segment_id FROM qp_segments_b a,qp_prc_contexts_b b
1820 WHERE a.segment_mapping_column=p_PRICING_ATTR_rec.product_attribute
1821 AND a.prc_context_id=b.prc_context_id
1822 AND b.prc_context_type = 'PRODUCT'
1823 AND b.prc_context_code=p_PRICING_ATTR_rec.product_attribute_context);
1824
1825 END IF;
1826 END IF;
1827 qp_delayed_requests_PVT.log_request(
1828 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
1829 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
1830 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_Modifiers,
1831 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
1832 p_request_type =>QP_GLOBALS.G_UPDATE_PRICING_ATTR_PHASE,
1833 x_return_status => l_return_status);
1834
1835 --Fetch list line type code (Bug 4706180)
1836 BEGIN
1837 SELECT list_line_type_code
1838 INTO l_list_line_type_code
1839 FROM qp_list_lines
1840 WHERE list_line_id = p_PRICING_ATTR_rec.list_line_id;
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 l_list_line_type_code := '';
1844 END;
1845
1846
1847 --Log delayed request to maintain denormalized pricing attrs columns
1848 --only for formula factor attrs
1849 IF l_list_line_type_code = 'PMR' THEN
1850 qp_delayed_requests_PVT.log_request(
1851 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
1852 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
1853 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_Modifiers,
1854 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
1855 p_request_type =>QP_GLOBALS.G_MAINTAIN_FACTOR_LIST_ATTRS,
1856 x_return_status => l_return_status);
1857 END IF;
1858
1859 oe_debug_pub.add('END Insert_Row in QPXUPRAB');
1860
1861 EXCEPTION
1862
1863 WHEN OTHERS THEN
1864
1865 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1866 THEN
1867 OE_MSG_PUB.Add_Exc_Msg
1868 ( G_PKG_NAME
1869 , 'Insert_Row'
1870 );
1871 END IF;
1872
1873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1874
1875 END Insert_Row;
1876
1877 -- Procedure Delete_Row
1878
1879 PROCEDURE Delete_Row
1880 ( p_pricing_attribute_id IN NUMBER
1881 )
1882 IS
1883 l_list_line_id NUMBER;
1884 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1885 BEGIN
1886 oe_debug_pub.add('BEGIN Delete_Row in QPXUPRAB');
1887
1888
1889 DELETE FROM QP_PRICING_ATTRIBUTES
1890 WHERE PRICING_ATTRIBUTE_ID = p_pricing_attribute_id
1891 RETURNING LIST_LINE_ID INTO l_list_line_id
1892 ;
1893
1894 qp_delayed_requests_PVT.log_request(
1895 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
1896 p_entity_id => l_list_line_id,
1897 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_Modifiers,
1898 p_requesting_entity_id => l_list_line_id,
1899 p_request_type =>QP_GLOBALS.G_MAINTAIN_FACTOR_LIST_ATTRS,
1900 x_return_status => l_return_status);
1901
1902 oe_debug_pub.add('END Delete_Row in QPXUPRAB');
1903 EXCEPTION
1904
1905 WHEN OTHERS THEN
1906
1907 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1908 THEN
1909 OE_MSG_PUB.Add_Exc_Msg
1910 ( G_PKG_NAME
1911 , 'Delete_Row'
1912 );
1913 END IF;
1914
1915 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1916
1917 END Delete_Row;
1918
1919 -- Function Query_Row
1920
1921 FUNCTION Query_Row
1922 ( p_pricing_attribute_id IN NUMBER
1923 ) RETURN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
1924 IS
1925 BEGIN
1926
1927 RETURN Query_Rows
1928 ( p_pricing_attribute_id => p_pricing_attribute_id
1929 )(1);
1930
1931 END Query_Row;
1932
1933 -- Function Query_Rows
1934
1935 --
1936
1937 FUNCTION Query_Rows
1938 ( p_pricing_attribute_id IN NUMBER :=
1939 FND_API.G_MISS_NUM
1940 , p_list_line_id IN NUMBER :=
1941 FND_API.G_MISS_NUM
1942 ) RETURN QP_Modifiers_PUB.Pricing_Attr_Tbl_Type
1943 IS
1944 l_PRICING_ATTR_rec QP_Modifiers_PUB.Pricing_Attr_Rec_Type;
1945 l_PRICING_ATTR_tbl QP_Modifiers_PUB.Pricing_Attr_Tbl_Type;
1946
1947 CURSOR l_PRICING_ATTR_csr IS
1948 SELECT ACCUMULATE_FLAG
1949 , ATTRIBUTE1
1950 , ATTRIBUTE10
1951 , ATTRIBUTE11
1952 , ATTRIBUTE12
1953 , ATTRIBUTE13
1954 , ATTRIBUTE14
1955 , ATTRIBUTE15
1956 , ATTRIBUTE2
1957 , ATTRIBUTE3
1958 , ATTRIBUTE4
1959 , ATTRIBUTE5
1960 , ATTRIBUTE6
1961 , ATTRIBUTE7
1962 , ATTRIBUTE8
1963 , ATTRIBUTE9
1964 , ATTRIBUTE_GROUPING_NO
1965 , CONTEXT
1966 , CREATED_BY
1967 , CREATION_DATE
1968 , EXCLUDER_FLAG
1969 , LAST_UPDATED_BY
1970 , LAST_UPDATE_DATE
1971 , LAST_UPDATE_LOGIN
1972 , LIST_LINE_ID
1973 , PRICING_ATTRIBUTE
1974 , PRICING_ATTRIBUTE_CONTEXT
1975 , PRICING_ATTRIBUTE_ID
1976 , PRICING_ATTR_VALUE_FROM
1977 , PRICING_ATTR_VALUE_TO
1978 , PRODUCT_ATTRIBUTE
1979 , PRODUCT_ATTRIBUTE_CONTEXT
1980 , PRODUCT_ATTR_VALUE
1981 , PRODUCT_UOM_CODE
1982 , PROGRAM_APPLICATION_ID
1983 , PROGRAM_ID
1984 , PROGRAM_UPDATE_DATE
1985 , REQUEST_ID
1986 , PRODUCT_ATTRIBUTE_DATATYPE
1987 , PRICING_ATTRIBUTE_DATATYPE
1988 , COMPARISON_OPERATOR_CODE
1989 , LIST_HEADER_ID
1990 , PRICING_PHASE_ID
1991 , PRICING_ATTR_VALUE_FROM_NUMBER
1992 , PRICING_ATTR_VALUE_TO_NUMBER
1993 , QUALIFICATION_IND
1994 FROM QP_PRICING_ATTRIBUTES
1995 WHERE ( PRICING_ATTRIBUTE_ID = p_pricing_attribute_id
1996 )
1997 OR ( LIST_LINE_ID = p_list_line_id
1998 );
1999
2000 BEGIN
2001
2002 oe_debug_pub.add('BEGIN Query_rows in QPXUPRAB');
2003
2004 IF
2005 (p_pricing_attribute_id IS NOT NULL
2006 AND
2007 p_pricing_attribute_id <> FND_API.G_MISS_NUM)
2008 AND
2009 (p_list_line_id IS NOT NULL
2010 AND
2011 p_list_line_id <> FND_API.G_MISS_NUM)
2012 THEN
2013 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2014 THEN
2015 OE_MSG_PUB.Add_Exc_Msg
2016 ( G_PKG_NAME
2017 , 'Query Rows'
2018 , 'Keys are mutually exclusive: pricing_attribute_id = '|| p_pricing_attribute_id || ', list_line_id = '|| p_list_line_id
2019 );
2020 END IF;
2021
2022 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2023
2024 END IF;
2025
2026
2027 -- Loop over fetched records
2028
2029 FOR l_implicit_rec IN l_PRICING_ATTR_csr LOOP
2030
2031 l_PRICING_ATTR_rec.accumulate_flag := l_implicit_rec.ACCUMULATE_FLAG;
2032 l_PRICING_ATTR_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
2033 l_PRICING_ATTR_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
2034 l_PRICING_ATTR_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
2035 l_PRICING_ATTR_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
2036 l_PRICING_ATTR_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
2037 l_PRICING_ATTR_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
2038 l_PRICING_ATTR_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
2039 l_PRICING_ATTR_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
2040 l_PRICING_ATTR_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
2041 l_PRICING_ATTR_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
2042 l_PRICING_ATTR_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
2043 l_PRICING_ATTR_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
2044 l_PRICING_ATTR_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
2045 l_PRICING_ATTR_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
2046 l_PRICING_ATTR_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
2047 l_PRICING_ATTR_rec.attribute_grouping_no := l_implicit_rec.ATTRIBUTE_GROUPING_NO;
2048 l_PRICING_ATTR_rec.context := l_implicit_rec.CONTEXT;
2049 l_PRICING_ATTR_rec.created_by := l_implicit_rec.CREATED_BY;
2050 l_PRICING_ATTR_rec.creation_date := l_implicit_rec.CREATION_DATE;
2051 l_PRICING_ATTR_rec.excluder_flag := l_implicit_rec.EXCLUDER_FLAG;
2052 l_PRICING_ATTR_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
2053 l_PRICING_ATTR_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
2054 l_PRICING_ATTR_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
2055 l_PRICING_ATTR_rec.list_line_id := l_implicit_rec.LIST_LINE_ID;
2056 l_PRICING_ATTR_rec.pricing_attribute := l_implicit_rec.PRICING_ATTRIBUTE;
2057 l_PRICING_ATTR_rec.pricing_attribute_context := l_implicit_rec.PRICING_ATTRIBUTE_CONTEXT;
2058 l_PRICING_ATTR_rec.pricing_attribute_id := l_implicit_rec.PRICING_ATTRIBUTE_ID;
2059 l_PRICING_ATTR_rec.pricing_attr_value_from := l_implicit_rec.PRICING_ATTR_VALUE_FROM;
2060 l_PRICING_ATTR_rec.pricing_attr_value_to := l_implicit_rec.PRICING_ATTR_VALUE_TO;
2061 l_PRICING_ATTR_rec.product_attribute := l_implicit_rec.PRODUCT_ATTRIBUTE;
2062 l_PRICING_ATTR_rec.product_attribute_context := l_implicit_rec.PRODUCT_ATTRIBUTE_CONTEXT;
2063 l_PRICING_ATTR_rec.product_attr_value := l_implicit_rec.PRODUCT_ATTR_VALUE;
2064 l_PRICING_ATTR_rec.product_uom_code := l_implicit_rec.PRODUCT_UOM_CODE;
2065 l_PRICING_ATTR_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
2066 l_PRICING_ATTR_rec.program_id := l_implicit_rec.PROGRAM_ID;
2067 l_PRICING_ATTR_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
2068 l_PRICING_ATTR_rec.request_id := l_implicit_rec.REQUEST_ID;
2069 l_PRICING_ATTR_rec.product_attribute_datatype := l_implicit_rec.PRODUCT_ATTRIBUTE_DATATYPE;
2070 l_PRICING_ATTR_rec.pricing_attribute_datatype := l_implicit_rec.PRICING_ATTRIBUTE_DATATYPE;
2071 l_PRICING_ATTR_rec.comparison_operator_code := l_implicit_rec.COMPARISON_OPERATOR_CODE;
2072 l_PRICING_ATTR_rec.list_header_id := l_implicit_rec.LIST_HEADER_ID;
2073 l_PRICING_ATTR_rec.pricing_phase_id := l_implicit_rec.PRICING_PHASE_ID;
2074 l_PRICING_ATTR_rec.pricing_attr_value_from_number := l_implicit_rec.PRICING_ATTR_VALUE_FROM_NUMBER;
2075 l_PRICING_ATTR_rec.pricing_attr_value_to_number := l_implicit_rec.PRICING_ATTR_VALUE_TO_NUMBER;
2076 l_PRICING_ATTR_rec.qualification_ind := l_implicit_rec.QUALIFICATION_IND;
2077
2078 l_PRICING_ATTR_tbl(l_PRICING_ATTR_tbl.COUNT + 1) := l_PRICING_ATTR_rec;
2079
2080 END LOOP;
2081
2082
2083 -- PK sent and no rows found
2084
2085 IF
2086 (p_pricing_attribute_id IS NOT NULL
2087 AND
2088 p_pricing_attribute_id <> FND_API.G_MISS_NUM)
2089 AND
2090 (l_PRICING_ATTR_tbl.COUNT = 0)
2091 THEN
2092 RAISE NO_DATA_FOUND;
2093 END IF;
2094
2095
2096 oe_debug_pub.add('END Query_rows in QPXUPRAB');
2097
2098 -- Return fetched table
2099
2100 RETURN l_PRICING_ATTR_tbl;
2101
2102 EXCEPTION
2103
2104 WHEN NO_DATA_FOUND THEN
2105 NULL;
2106
2107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2108
2109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2110
2111 WHEN OTHERS THEN
2112
2113 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2114 THEN
2115 OE_MSG_PUB.Add_Exc_Msg
2116 ( G_PKG_NAME
2117 , 'Query_Rows'
2118 );
2119 END IF;
2120
2121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2122
2123 END Query_Rows;
2124
2125 -- Procedure lock_Row
2126 --
2127
2128 PROCEDURE Lock_Row
2129 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
2130 , p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2131 , x_PRICING_ATTR_rec OUT NOCOPY /* file.sql.39 change */ QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2132 )
2133 IS
2134 l_PRICING_ATTR_rec QP_Modifiers_PUB.Pricing_Attr_Rec_Type;
2135 BEGIN
2136
2137 oe_debug_pub.add('BEGIN Lock_row in QPXUPRAB');
2138
2139 SELECT
2140 --ACCUMULATE_FLAG
2141 ATTRIBUTE1
2142 , ATTRIBUTE10
2143 , ATTRIBUTE11
2144 , ATTRIBUTE12
2145 , ATTRIBUTE13
2146 , ATTRIBUTE14
2147 , ATTRIBUTE15
2148 , ATTRIBUTE2
2149 , ATTRIBUTE3
2150 , ATTRIBUTE4
2151 , ATTRIBUTE5
2152 , ATTRIBUTE6
2153 , ATTRIBUTE7
2154 , ATTRIBUTE8
2155 , ATTRIBUTE9
2156 , ATTRIBUTE_GROUPING_NO
2157 , CONTEXT
2158 , CREATED_BY
2159 , CREATION_DATE
2160 , EXCLUDER_FLAG
2161 , LAST_UPDATED_BY
2162 , LAST_UPDATE_DATE
2163 , LAST_UPDATE_LOGIN
2164 , LIST_LINE_ID
2165 , PRICING_ATTRIBUTE
2166 , PRICING_ATTRIBUTE_CONTEXT
2167 , PRICING_ATTRIBUTE_ID
2168 , PRICING_ATTR_VALUE_FROM
2169 , PRICING_ATTR_VALUE_TO
2170 , PRODUCT_ATTRIBUTE
2171 , PRODUCT_ATTRIBUTE_CONTEXT
2172 , PRODUCT_ATTR_VALUE
2173 , PRODUCT_UOM_CODE
2174 , PROGRAM_APPLICATION_ID
2175 , PROGRAM_ID
2176 , PROGRAM_UPDATE_DATE
2177 , REQUEST_ID
2178 , PRODUCT_ATTRIBUTE_DATATYPE
2179 , PRICING_ATTRIBUTE_DATATYPE
2180 , COMPARISON_OPERATOR_CODE
2181 -- , LIST_HEADER_ID
2182 -- , PRICING_PHASE_ID
2183 INTO
2184 --l_PRICING_ATTR_rec.accumulate_flag
2185 l_PRICING_ATTR_rec.attribute1
2186 , l_PRICING_ATTR_rec.attribute10
2187 , l_PRICING_ATTR_rec.attribute11
2188 , l_PRICING_ATTR_rec.attribute12
2189 , l_PRICING_ATTR_rec.attribute13
2190 , l_PRICING_ATTR_rec.attribute14
2191 , l_PRICING_ATTR_rec.attribute15
2192 , l_PRICING_ATTR_rec.attribute2
2193 , l_PRICING_ATTR_rec.attribute3
2194 , l_PRICING_ATTR_rec.attribute4
2195 , l_PRICING_ATTR_rec.attribute5
2196 , l_PRICING_ATTR_rec.attribute6
2197 , l_PRICING_ATTR_rec.attribute7
2198 , l_PRICING_ATTR_rec.attribute8
2199 , l_PRICING_ATTR_rec.attribute9
2200 , l_PRICING_ATTR_rec.attribute_grouping_no
2201 , l_PRICING_ATTR_rec.context
2202 , l_PRICING_ATTR_rec.created_by
2203 , l_PRICING_ATTR_rec.creation_date
2204 , l_PRICING_ATTR_rec.excluder_flag
2205 , l_PRICING_ATTR_rec.last_updated_by
2206 , l_PRICING_ATTR_rec.last_update_date
2207 , l_PRICING_ATTR_rec.last_update_login
2208 , l_PRICING_ATTR_rec.list_line_id
2209 , l_PRICING_ATTR_rec.pricing_attribute
2210 , l_PRICING_ATTR_rec.pricing_attribute_context
2211 , l_PRICING_ATTR_rec.pricing_attribute_id
2212 , l_PRICING_ATTR_rec.pricing_attr_value_from
2213 , l_PRICING_ATTR_rec.pricing_attr_value_to
2214 , l_PRICING_ATTR_rec.product_attribute
2215 , l_PRICING_ATTR_rec.product_attribute_context
2216 , l_PRICING_ATTR_rec.product_attr_value
2217 , l_PRICING_ATTR_rec.product_uom_code
2218 , l_PRICING_ATTR_rec.program_application_id
2219 , l_PRICING_ATTR_rec.program_id
2220 , l_PRICING_ATTR_rec.program_update_date
2221 , l_PRICING_ATTR_rec.request_id
2222 , l_PRICING_ATTR_rec.product_attribute_datatype
2223 , l_PRICING_ATTR_rec.pricing_attribute_datatype
2224 , l_PRICING_ATTR_rec.comparison_operator_code
2225 -- , l_PRICING_ATTR_rec.list_header_id
2226 -- , l_PRICING_ATTR_rec.pricing_phase_id
2227 FROM QP_PRICING_ATTRIBUTES
2228 WHERE PRICING_ATTRIBUTE_ID = p_PRICING_ATTR_rec.pricing_attribute_id
2229 FOR UPDATE NOWAIT;
2230
2231 -- Row locked. Compare IN attributes to DB attributes.
2232
2233 IF
2234 --QP_GLOBALS.Equal(p_PRICING_ATTR_rec.accumulate_flag,
2235 -- l_PRICING_ATTR_rec.accumulate_flag)
2236 --AND
2237 /*
2238 QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute1,
2239 l_PRICING_ATTR_rec.attribute1)
2240 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute10,
2241 l_PRICING_ATTR_rec.attribute10)
2242 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute11,
2243 l_PRICING_ATTR_rec.attribute11)
2244 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute12,
2245 l_PRICING_ATTR_rec.attribute12)
2246 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute13,
2247 l_PRICING_ATTR_rec.attribute13)
2248 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute14,
2249 l_PRICING_ATTR_rec.attribute14)
2250 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute15,
2251 l_PRICING_ATTR_rec.attribute15)
2252 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute2,
2253 l_PRICING_ATTR_rec.attribute2)
2254 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute3,
2255 l_PRICING_ATTR_rec.attribute3)
2256 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute4,
2257 l_PRICING_ATTR_rec.attribute4)
2258 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute5,
2259 l_PRICING_ATTR_rec.attribute5)
2260 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute6,
2261 l_PRICING_ATTR_rec.attribute6)
2262 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute7,
2263 l_PRICING_ATTR_rec.attribute7)
2264 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute8,
2265 l_PRICING_ATTR_rec.attribute8)
2266 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute9,
2267 l_PRICING_ATTR_rec.attribute9)
2268 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.attribute_grouping_no,
2269 l_PRICING_ATTR_rec.attribute_grouping_no)
2270 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.context,
2271 l_PRICING_ATTR_rec.context)
2272 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.created_by,
2273 l_PRICING_ATTR_rec.created_by)
2274 -- AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.creation_date,
2275 -- l_PRICING_ATTR_rec.creation_date)
2276 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.excluder_flag,
2277 l_PRICING_ATTR_rec.excluder_flag)
2278 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_updated_by,
2279 l_PRICING_ATTR_rec.last_updated_by)
2280 -- AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_update_date,
2281 -- l_PRICING_ATTR_rec.last_update_date)
2282 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.last_update_login,
2283 l_PRICING_ATTR_rec.last_update_login)
2284 */
2285 --AND
2286 QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_line_id,
2287 l_PRICING_ATTR_rec.list_line_id)
2288 /*
2289 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute,
2290 l_PRICING_ATTR_rec.pricing_attribute)
2291 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_context,
2292 l_PRICING_ATTR_rec.pricing_attribute_context)
2293 */
2294 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_id,
2295 l_PRICING_ATTR_rec.pricing_attribute_id)
2296 /*
2297 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_from,
2298 l_PRICING_ATTR_rec.pricing_attr_value_from)
2299 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attr_value_to,
2300 l_PRICING_ATTR_rec.pricing_attr_value_to)
2301 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute,
2302 l_PRICING_ATTR_rec.product_attribute)
2303 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_context,
2304 l_PRICING_ATTR_rec.product_attribute_context)
2305 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attr_value,
2306 l_PRICING_ATTR_rec.product_attr_value)
2307 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_uom_code,
2308 l_PRICING_ATTR_rec.product_uom_code)
2309 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_application_id,
2310 l_PRICING_ATTR_rec.program_application_id)
2311 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_id,
2312 l_PRICING_ATTR_rec.program_id)
2313 -- AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.program_update_date,
2314 -- l_PRICING_ATTR_rec.program_update_date)
2315 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.request_id,
2316 l_PRICING_ATTR_rec.request_id)
2317 -- AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_attribute_datatype,
2318 -- l_PRICING_ATTR_rec.product_attribute_datatype)
2319 -- AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_datatype,
2320 -- l_PRICING_ATTR_rec.pricing_attribute_datatype)
2321 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.comparison_operator_code,
2322 l_PRICING_ATTR_rec.comparison_operator_code)
2323 */
2324 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_header_id,
2325 l_PRICING_ATTR_rec.list_header_id)
2326 AND QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_phase_id,
2327 l_PRICING_ATTR_rec.pricing_phase_id)
2328 THEN
2329
2330 -- Row has not changed. Set out parameter.
2331
2332 x_PRICING_ATTR_rec := l_PRICING_ATTR_rec;
2333
2334 -- Set return status
2335
2336 x_return_status := FND_API.G_RET_STS_SUCCESS;
2337 x_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2338
2339 ELSE
2340
2341 -- Row has changed by another user.
2342 --8594682 -added debug messages for oe_lock_row issue
2343 oe_debug_pub.ADD('-------------------data compare in pricing attr line (database vs record)------------------');
2344 oe_debug_pub.add('PRICING_ATTR_rec.attribute1 :'||l_PRICING_ATTR_rec.attribute1||':'||p_PRICING_ATTR_rec.attribute1||':');
2345 oe_debug_pub.add('PRICING_ATTR_rec.attribute10 :'||l_PRICING_ATTR_rec.attribute10||':'||p_PRICING_ATTR_rec.attribute10||':');
2346 oe_debug_pub.add('PRICING_ATTR_rec.attribute11 :'||l_PRICING_ATTR_rec.attribute11||':'||p_PRICING_ATTR_rec.attribute11||':');
2347 oe_debug_pub.add('PRICING_ATTR_rec.attribute12 :'||l_PRICING_ATTR_rec.attribute12||':'||p_PRICING_ATTR_rec.attribute12||':');
2348 oe_debug_pub.add('PRICING_ATTR_rec.attribute13 :'||l_PRICING_ATTR_rec.attribute13||':'||p_PRICING_ATTR_rec.attribute13||':');
2349 oe_debug_pub.add('PRICING_ATTR_rec.attribute14 :'||l_PRICING_ATTR_rec.attribute14||':'||p_PRICING_ATTR_rec.attribute14||':');
2350 oe_debug_pub.add('PRICING_ATTR_rec.attribute15 :'||l_PRICING_ATTR_rec.attribute15||':'||p_PRICING_ATTR_rec.attribute15||':');
2351 oe_debug_pub.add('PRICING_ATTR_rec.attribute2 :'||l_PRICING_ATTR_rec.attribute2||':'||p_PRICING_ATTR_rec.attribute2||':');
2352 oe_debug_pub.add('PRICING_ATTR_rec.attribute3 :'||l_PRICING_ATTR_rec.attribute3||':'||p_PRICING_ATTR_rec.attribute3||':');
2353 oe_debug_pub.add('PRICING_ATTR_rec.attribute4 :'||l_PRICING_ATTR_rec.attribute4||':'||p_PRICING_ATTR_rec.attribute4||':');
2354 oe_debug_pub.add('PRICING_ATTR_rec.attribute5 :'||l_PRICING_ATTR_rec.attribute5||':'||p_PRICING_ATTR_rec.attribute5||':');
2355 oe_debug_pub.add('PRICING_ATTR_rec.attribute6 :'||l_PRICING_ATTR_rec.attribute6||':'||p_PRICING_ATTR_rec.attribute6||':');
2356 oe_debug_pub.add('PRICING_ATTR_rec.attribute7 :'||l_PRICING_ATTR_rec.attribute7||':'||p_PRICING_ATTR_rec.attribute7||':');
2357 oe_debug_pub.add('PRICING_ATTR_rec.attribute8 :'||l_PRICING_ATTR_rec.attribute8||':'||p_PRICING_ATTR_rec.attribute8||':');
2358 oe_debug_pub.add('PRICING_ATTR_rec.attribute9 :'||l_PRICING_ATTR_rec.attribute9||':'||p_PRICING_ATTR_rec.attribute9||':');
2359 oe_debug_pub.add('PRICING_ATTR_rec.attribute_grouping_no :'||l_PRICING_ATTR_rec.attribute_grouping_no||':'||p_PRICING_ATTR_rec.attribute_grouping_no||':');
2360 oe_debug_pub.add('PRICING_ATTR_rec.context :'||l_PRICING_ATTR_rec.context||':'|| p_PRICING_ATTR_rec.context||':');
2361 oe_debug_pub.add('PRICING_ATTR_rec.created_by :'||l_PRICING_ATTR_rec.created_by||':'||p_PRICING_ATTR_rec.created_by||':');
2362 oe_debug_pub.add('PRICING_ATTR_rec.creation_date :'||l_PRICING_ATTR_rec.creation_date||':'||p_PRICING_ATTR_rec.creation_date||':');
2363 oe_debug_pub.add('PRICING_ATTR_rec.excluder_flag :'||l_PRICING_ATTR_rec.excluder_flag||':'||p_PRICING_ATTR_rec.excluder_flag||':');
2364 oe_debug_pub.add('PRICING_ATTR_rec.last_updated_by :'||l_PRICING_ATTR_rec.last_updated_by||':'||p_PRICING_ATTR_rec.last_updated_by||':');
2365 oe_debug_pub.add('PRICING_ATTR_rec.last_update_date :'||l_PRICING_ATTR_rec.last_update_date||':'||p_PRICING_ATTR_rec.last_update_date||':');
2366 oe_debug_pub.add('PRICING_ATTR_rec.last_update_login :'||l_PRICING_ATTR_rec.last_update_login||':'||p_PRICING_ATTR_rec.last_update_login||':');
2367 oe_debug_pub.add('PRICING_ATTR_rec.list_line_id :'||l_PRICING_ATTR_rec.list_line_id||':'||p_PRICING_ATTR_rec.list_line_id||':');
2368 oe_debug_pub.add('PRICING_ATTR_rec.pricing_attribute :'||l_PRICING_ATTR_rec.pricing_attribute||':'||p_PRICING_ATTR_rec.pricing_attribute||':');
2369 oe_debug_pub.add('PRICING_ATTR_rec.pricing_attribute_context :'||l_PRICING_ATTR_rec.pricing_attribute_context||':'||p_PRICING_ATTR_rec.pricing_attribute_context||':');
2370 oe_debug_pub.add('PRICING_ATTR_rec.pricing_attribute_id :'||l_PRICING_ATTR_rec.pricing_attribute_id||':'||p_PRICING_ATTR_rec.pricing_attribute_id||':');
2371 oe_debug_pub.add('PRICING_ATTR_rec.pricing_attr_value_from :'||l_PRICING_ATTR_rec.pricing_attr_value_from||':'||p_PRICING_ATTR_rec.pricing_attr_value_from||':');
2372 oe_debug_pub.add('PRICING_ATTR_rec.pricing_attr_value_to :'||l_PRICING_ATTR_rec.pricing_attr_value_to||':'||p_PRICING_ATTR_rec.pricing_attr_value_to||':');
2373 oe_debug_pub.add('PRICING_ATTR_rec.product_attribute :'||l_PRICING_ATTR_rec.product_attribute||':'||p_PRICING_ATTR_rec.product_attribute||':');
2374 oe_debug_pub.add('PRICING_ATTR_rec.product_attribute_context :'||l_PRICING_ATTR_rec.product_attribute_context||':'||p_PRICING_ATTR_rec.product_attribute_context||':');
2375 oe_debug_pub.add('PRICING_ATTR_rec.product_attr_value :'||l_PRICING_ATTR_rec.product_attr_value||':'||p_PRICING_ATTR_rec.product_attr_value||':');
2376 oe_debug_pub.add('PRICING_ATTR_rec.product_uom_code :'||l_PRICING_ATTR_rec.product_uom_code||':'||p_PRICING_ATTR_rec.product_uom_code||':');
2377 oe_debug_pub.add('PRICING_ATTR_rec.program_application_id :'||l_PRICING_ATTR_rec.program_application_id||':'||p_PRICING_ATTR_rec.program_application_id||':');
2378 oe_debug_pub.add('PRICING_ATTR_rec.program_id :'||l_PRICING_ATTR_rec.program_id||':'||p_PRICING_ATTR_rec.program_id||':');
2379 oe_debug_pub.add('PRICING_ATTR_rec.program_update_date :'||l_PRICING_ATTR_rec.program_update_date||':'||p_PRICING_ATTR_rec.program_update_date||':');
2380 oe_debug_pub.add('PRICING_ATTR_rec.request_id :'||l_PRICING_ATTR_rec.request_id||':'||p_PRICING_ATTR_rec.request_id||':');
2381 oe_debug_pub.add('PRICING_ATTR_rec.product_attribute_datatype :'||l_PRICING_ATTR_rec.product_attribute_datatype||':'||p_PRICING_ATTR_rec.product_attribute_datatype||':');
2382 oe_debug_pub.add('PRICING_ATTR_rec.pricing_attribute_datatype :'||l_PRICING_ATTR_rec.pricing_attribute_datatype||':'||p_PRICING_ATTR_rec.pricing_attribute_datatype||':');
2383 oe_debug_pub.add('PRICING_ATTR_rec.comparison_operator_code :'||l_PRICING_ATTR_rec.comparison_operator_code||':'||p_PRICING_ATTR_rec.comparison_operator_code||':');
2384 oe_debug_pub.ADD('-------------------data compare in pricing attr line end------------------');
2385
2386 x_return_status := FND_API.G_RET_STS_ERROR;
2387 x_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2388
2389 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2390 THEN
2391
2392 FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_CHANGED');
2393 OE_MSG_PUB.Add;
2394
2395 END IF;
2396
2397 END IF;
2398
2399 oe_debug_pub.add('END Lock_row in QPXUPRAB');
2400
2401 EXCEPTION
2402
2403 WHEN NO_DATA_FOUND THEN
2404
2405 x_return_status := FND_API.G_RET_STS_ERROR;
2406 x_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2407
2408 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2409 THEN
2410
2411 FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_DELETED');
2412 OE_MSG_PUB.Add;
2413
2414 END IF;
2415 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
2416
2417 x_return_status := FND_API.G_RET_STS_ERROR;
2418 x_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2419
2420 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
2421 THEN
2422
2423 FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_ALREADY_LOCKED');
2424 OE_MSG_PUB.Add;
2425
2426 END IF;
2427 WHEN OTHERS THEN
2428
2429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2430 x_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2431
2432 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2433 THEN
2434 OE_MSG_PUB.Add_Exc_Msg
2435 ( G_PKG_NAME
2436 , 'Lock_Row'
2437 );
2438 END IF;
2439
2440 END Lock_Row;
2441
2442 -- Function Get_Values
2443
2444 FUNCTION Get_Values
2445 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2446 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type :=
2447 QP_Modifiers_PUB.G_MISS_PRICING_ATTR_REC
2448 ) RETURN QP_Modifiers_PUB.Pricing_Attr_Val_Rec_Type
2449 IS
2450 l_PRICING_ATTR_val_rec QP_Modifiers_PUB.Pricing_Attr_Val_Rec_Type;
2451 BEGIN
2452
2453 oe_debug_pub.add('BEGIN Get_Values in QPXUPRAB');
2454
2455 IF p_PRICING_ATTR_rec.accumulate_flag IS NOT NULL AND
2456 p_PRICING_ATTR_rec.accumulate_flag <> FND_API.G_MISS_CHAR AND
2457 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.accumulate_flag,
2458 p_old_PRICING_ATTR_rec.accumulate_flag)
2459 THEN
2460 l_PRICING_ATTR_val_rec.accumulate := QP_Id_To_Value.Accumulate
2461 ( p_accumulate_flag => p_PRICING_ATTR_rec.accumulate_flag
2462 );
2463 END IF;
2464
2465 IF p_PRICING_ATTR_rec.excluder_flag IS NOT NULL AND
2466 p_PRICING_ATTR_rec.excluder_flag <> FND_API.G_MISS_CHAR AND
2467 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.excluder_flag,
2468 p_old_PRICING_ATTR_rec.excluder_flag)
2469 THEN
2470 l_PRICING_ATTR_val_rec.excluder := QP_Id_To_Value.Excluder
2471 ( p_excluder_flag => p_PRICING_ATTR_rec.excluder_flag
2472 );
2473 END IF;
2474
2475 /*changes made by spgopal to include list_header_id and pricing_phase_id in pricing attr for performance problem*/
2476 IF p_PRICING_ATTR_rec.list_header_id IS NOT NULL AND
2477 p_PRICING_ATTR_rec.list_header_id <> FND_API.G_MISS_NUM AND
2478 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_header_id,
2479 p_old_PRICING_ATTR_rec.list_header_id)
2480 THEN
2481 -- l_PRICING_ATTR_val_rec.list_Header := QP_Id_To_Value.List_Header( p_list_header_id => p_PRICING_ATTR_rec.list_header_id);
2482 null;
2483 END IF;
2484
2485 IF p_PRICING_ATTR_rec.pricing_phase_id IS NOT NULL AND
2486 p_PRICING_ATTR_rec.pricing_phase_id <> FND_API.G_MISS_NUM AND
2487 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_phase_id,
2488 p_old_PRICING_ATTR_rec.pricing_phase_id)
2489 THEN
2490 -- l_PRICING_ATTR_val_rec.pricing_phase := QP_Id_To_Value.pricing_phase( p_pricing_phase_id => p_PRICING_ATTR_rec.pricing_phase_id);
2491 null;
2492 END IF;
2493
2494 IF p_PRICING_ATTR_rec.list_line_id IS NOT NULL AND
2495 p_PRICING_ATTR_rec.list_line_id <> FND_API.G_MISS_NUM AND
2496 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.list_line_id,
2497 p_old_PRICING_ATTR_rec.list_line_id)
2498 THEN
2499 l_PRICING_ATTR_val_rec.list_line := QP_Id_To_Value.List_Line
2500 ( p_list_line_id => p_PRICING_ATTR_rec.list_line_id
2501 );
2502 END IF;
2503
2504 /* IF p_PRICING_ATTR_rec.pricing_attribute_id IS NOT NULL AND
2505 p_PRICING_ATTR_rec.pricing_attribute_id <> FND_API.G_MISS_NUM AND
2506 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.pricing_attribute_id,
2507 p_old_PRICING_ATTR_rec.pricing_attribute_id)
2508 THEN
2509 l_PRICING_ATTR_val_rec.pricing_attribute := QP_Id_To_Value.Pricing_Attribute
2510 ( p_pricing_attribute_id => p_PRICING_ATTR_rec.pricing_attribute_id
2511 );
2512 END IF;
2513 */
2514 IF p_PRICING_ATTR_rec.product_uom_code IS NOT NULL AND
2515 p_PRICING_ATTR_rec.product_uom_code <> FND_API.G_MISS_CHAR AND
2516 NOT QP_GLOBALS.Equal(p_PRICING_ATTR_rec.product_uom_code,
2517 p_old_PRICING_ATTR_rec.product_uom_code)
2518 THEN
2519 l_PRICING_ATTR_val_rec.product_uom := QP_Id_To_Value.Product_Uom
2520 ( p_product_uom_code => p_PRICING_ATTR_rec.product_uom_code
2521 );
2522 END IF;
2523
2524 oe_debug_pub.add('END Get_Values in QPXUPRAB');
2525
2526 RETURN l_PRICING_ATTR_val_rec;
2527
2528 END Get_Values;
2529
2530 -- Function Get_Ids
2531
2532 FUNCTION Get_Ids
2533 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2534 , p_PRICING_ATTR_val_rec IN QP_Modifiers_PUB.Pricing_Attr_Val_Rec_Type
2535 ) RETURN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2536 IS
2537 l_PRICING_ATTR_rec QP_Modifiers_PUB.Pricing_Attr_Rec_Type;
2538 BEGIN
2539
2540 oe_debug_pub.add('BEGIN Get_Ids in QPXUPRAB');
2541
2542 -- initialize return_status.
2543
2544 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_SUCCESS;
2545
2546 -- initialize l_PRICING_ATTR_rec.
2547
2548 l_PRICING_ATTR_rec := p_PRICING_ATTR_rec;
2549
2550 IF p_PRICING_ATTR_val_rec.accumulate <> FND_API.G_MISS_CHAR
2551 THEN
2552
2553 IF p_PRICING_ATTR_rec.accumulate_flag <> FND_API.G_MISS_CHAR THEN
2554
2555 l_PRICING_ATTR_rec.accumulate_flag := p_PRICING_ATTR_rec.accumulate_flag;
2556
2557 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2558 THEN
2559
2560 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2561 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','accumulate');
2562 OE_MSG_PUB.Add;
2563
2564 END IF;
2565
2566 ELSE
2567
2568 l_PRICING_ATTR_rec.accumulate_flag := QP_Value_To_Id.accumulate
2569 ( p_accumulate => p_PRICING_ATTR_val_rec.accumulate
2570 );
2571
2572 IF l_PRICING_ATTR_rec.accumulate_flag = FND_API.G_MISS_CHAR THEN
2573 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2574 END IF;
2575
2576 END IF;
2577
2578 END IF;
2579
2580 IF p_PRICING_ATTR_val_rec.excluder <> FND_API.G_MISS_CHAR
2581 THEN
2582
2583 IF p_PRICING_ATTR_rec.excluder_flag <> FND_API.G_MISS_CHAR THEN
2584
2585 l_PRICING_ATTR_rec.excluder_flag := p_PRICING_ATTR_rec.excluder_flag;
2586
2587 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2588 THEN
2589
2590 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2591 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','excluder');
2592 OE_MSG_PUB.Add;
2593
2594 END IF;
2595
2596 ELSE
2597
2598 l_PRICING_ATTR_rec.excluder_flag := QP_Value_To_Id.excluder
2599 ( p_excluder => p_PRICING_ATTR_val_rec.excluder
2600 );
2601
2602 IF l_PRICING_ATTR_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
2603 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2604 END IF;
2605
2606 END IF;
2607
2608 END IF;
2609
2610 /*changes made by spgopal included pricing_phase_id and list_header_id to fix performance problem*/
2611 IF p_PRICING_ATTR_val_rec.list_header <> FND_API.G_MISS_CHAR
2612 THEN
2613
2614 IF p_PRICING_ATTR_rec.list_header_id <> FND_API.G_MISS_NUM THEN
2615
2616 l_PRICING_ATTR_rec.list_header_id := p_PRICING_ATTR_rec.list_header_id;
2617
2618 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2619 THEN
2620
2621 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2622 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_header');
2623 OE_MSG_PUB.Add;
2624
2625 END IF;
2626
2627 ELSE
2628
2629 l_PRICING_ATTR_rec.list_header_id := QP_Value_To_Id.list_header( p_list_header => p_PRICING_ATTR_val_rec.list_header);
2630
2631 IF l_PRICING_ATTR_rec.list_header_id = FND_API.G_MISS_NUM THEN
2632 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2633 END IF;
2634
2635 END IF;
2636
2637 END IF;
2638
2639 IF p_PRICING_ATTR_val_rec.pricing_phase <> FND_API.G_MISS_CHAR
2640 THEN
2641
2642 IF p_PRICING_ATTR_rec.pricing_phase_id <> FND_API.G_MISS_NUM THEN
2643
2644 l_PRICING_ATTR_rec.pricing_phase_id := p_PRICING_ATTR_rec.pricing_phase_id;
2645
2646 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2647 THEN
2648
2649 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2650 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pricing_phase');
2651 OE_MSG_PUB.Add;
2652
2653 END IF;
2654
2655 ELSE
2656
2657 -- l_PRICING_ATTR_rec.pricing_phase_id := QP_Value_To_Id.pricing_phase( p_list_line => p_PRICING_ATTR_val_rec.pricing_phase);
2658 null;
2659
2660 IF l_PRICING_ATTR_rec.pricing_phase_id = FND_API.G_MISS_NUM THEN
2661 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2662 END IF;
2663
2664 END IF;
2665
2666 END IF;
2667
2668 -----
2669 IF p_PRICING_ATTR_val_rec.list_line <> FND_API.G_MISS_CHAR
2670 THEN
2671
2672 IF p_PRICING_ATTR_rec.list_line_id <> FND_API.G_MISS_NUM THEN
2673
2674 l_PRICING_ATTR_rec.list_line_id := p_PRICING_ATTR_rec.list_line_id;
2675
2676 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2677 THEN
2678
2679 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2680 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','list_line');
2681 OE_MSG_PUB.Add;
2682
2683 END IF;
2684
2685 ELSE
2686
2687 l_PRICING_ATTR_rec.list_line_id := QP_Value_To_Id.list_line
2688 ( p_list_line => p_PRICING_ATTR_val_rec.list_line
2689 );
2690
2691 IF l_PRICING_ATTR_rec.list_line_id = FND_API.G_MISS_NUM THEN
2692 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2693 END IF;
2694
2695 END IF;
2696
2697 END IF;
2698
2699 /****************************************************************************
2700 Added code to perform value_to_id conversion for pricing_Attribute,
2701 pricing_Attr_value_from and pricing_attr_value_to columns.
2702 ****************************************************************************/
2703
2704 IF p_PRICING_ATTR_val_rec.pricing_attribute_desc <> FND_API.G_MISS_CHAR
2705 THEN
2706
2707 IF p_PRICING_ATTR_rec.pricing_attribute <> FND_API.G_MISS_CHAR THEN
2708
2709 l_PRICING_ATTR_rec.pricing_attribute := p_PRICING_ATTR_rec.pricing_attribute;
2710
2711 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_SUCCESS)
2712 THEN
2713
2714 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2715 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pricing_attribute');
2716 oe_msg_pub.Add;
2717
2718 END IF;
2719
2720 ELSE
2721
2722 l_PRICING_ATTR_rec.pricing_attribute := QP_Value_To_Id.pricing_attribute
2723 ( p_pricing_attribute_desc => p_PRICING_ATTR_val_rec.pricing_attribute_desc,
2724 p_context => l_PRICING_ATTR_rec.pricing_attribute_context
2725 );
2726
2727 IF l_PRICING_ATTR_rec.pricing_attribute = FND_API.G_MISS_CHAR THEN
2728 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2729 END IF;
2730
2731 END IF;
2732
2733 END IF;
2734
2735 IF p_PRICING_ATTR_val_rec.pricing_attr_value_from_desc <> FND_API.G_MISS_CHAR
2736 THEN
2737
2738 IF p_PRICING_ATTR_rec.pricing_attr_value_from <> FND_API.G_MISS_CHAR THEN
2739
2740 l_PRICING_ATTR_rec.pricing_attr_value_from := p_PRICING_ATTR_rec.pricing_attr_value_from;
2741
2742 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_SUCCESS)
2743 THEN
2744
2745 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2746 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pricing_attr_value_from');
2747 oe_msg_pub.Add;
2748
2749 END IF;
2750
2751 ELSE
2752
2753 l_PRICING_ATTR_rec.pricing_attr_value_from := QP_Value_To_Id.pricing_attr_value_from
2754 ( p_pricing_attr_value_from_desc => p_PRICING_ATTR_val_rec.pricing_attr_value_from_desc,
2755 p_context => l_PRICING_ATTR_rec.pricing_attribute_context,
2756 p_attribute => l_PRICING_ATTR_rec.pricing_attribute
2757 );
2758
2759 IF l_PRICING_ATTR_rec.pricing_attr_value_from = FND_API.G_MISS_CHAR THEN
2760 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2761 END IF;
2762
2763 END IF;
2764
2765 END IF;
2766
2767 IF p_PRICING_ATTR_val_rec.pricing_attr_value_to_desc <> FND_API.G_MISS_CHAR
2768 THEN
2769
2770 IF p_PRICING_ATTR_rec.pricing_attr_value_to <> FND_API.G_MISS_CHAR THEN
2771
2772 l_PRICING_ATTR_rec.pricing_attr_value_to := p_PRICING_ATTR_rec.pricing_attr_value_to;
2773
2774 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_SUCCESS)
2775 THEN
2776
2777 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2778 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pricing_attr_value_to');
2779 oe_msg_pub.Add;
2780
2781 END IF;
2782
2783 ELSE
2784
2785 l_PRICING_ATTR_rec.pricing_attr_value_to := QP_Value_To_Id.pricing_attr_value_to
2786 ( p_pricing_attr_value_to_desc => p_PRICING_ATTR_val_rec.pricing_attr_value_to_desc,
2787 p_context => l_PRICING_ATTR_rec.pricing_attribute_context,
2788 p_attribute => l_PRICING_ATTR_rec.pricing_attribute
2789 );
2790
2791 IF l_PRICING_ATTR_rec.pricing_attr_value_to = FND_API.G_MISS_CHAR THEN
2792 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2793 END IF;
2794
2795 END IF;
2796
2797 END IF;
2798
2799 IF p_PRICING_ATTR_val_rec.product_uom <> FND_API.G_MISS_CHAR
2800 THEN
2801
2802 IF p_PRICING_ATTR_rec.product_uom_code <> FND_API.G_MISS_CHAR THEN
2803
2804 l_PRICING_ATTR_rec.product_uom_code := p_PRICING_ATTR_rec.product_uom_code;
2805
2806 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
2807 THEN
2808
2809 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
2810 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','product_uom');
2811 OE_MSG_PUB.Add;
2812
2813 END IF;
2814
2815 ELSE
2816
2817 l_PRICING_ATTR_rec.product_uom_code := QP_Value_To_Id.product_uom
2818 ( p_product_uom => p_PRICING_ATTR_val_rec.product_uom
2819 );
2820
2821 IF l_PRICING_ATTR_rec.product_uom_code = FND_API.G_MISS_CHAR THEN
2822 l_PRICING_ATTR_rec.return_status := FND_API.G_RET_STS_ERROR;
2823 END IF;
2824
2825 END IF;
2826
2827 END IF;
2828
2829 oe_debug_pub.add('END Get_Ids in QPXUPRAB');
2830
2831 RETURN l_PRICING_ATTR_rec;
2832
2833 END Get_Ids;
2834
2835 Procedure Pre_Write_Process
2836 ( p_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2837 , p_old_PRICING_ATTR_rec IN QP_Modifiers_PUB.Pricing_Attr_Rec_Type :=
2838 QP_Modifiers_PUB.G_MISS_Pricing_Attr_REC
2839 , x_PRICING_ATTR_rec OUT NOCOPY /* file.sql.39 change */ QP_Modifiers_PUB.Pricing_Attr_Rec_Type
2840 ) IS
2841 l_PRICING_ATTR_rec QP_MODIFIERS_PUB.Pricing_Attr_Rec_Type := p_PRICING_ATTR_rec;
2842 l_return_status varchar2(30);
2843 l_changed_lines varchar2(1) := null;
2844 l_list_header_id number;
2845
2846 BEGIN
2847
2848 oe_debug_pub.Add('Entering QP_PRICING_ATTR_Util.pre_write_process', 1);
2849 --Bug 10421292 . Added following code to update pricing attributes when the Product attribute is changed in Modifier form.
2850 --Similar issue addressed for Price list in bug 2807015
2851 IF ( p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_UPDATE AND p_PRICING_ATTR_rec.excluder_flag = 'N') --14603635
2852 THEN
2853 IF ((p_old_PRICING_ATTR_rec.product_attribute <> p_PRICING_ATTR_rec.product_attribute) OR
2854 (p_old_PRICING_ATTR_rec.product_attr_value <> p_PRICING_ATTR_rec.product_attr_value))
2855 THEN
2856 begin
2857 update qp_pricing_attributes
2858 set product_attribute = p_PRICING_ATTR_rec.product_attribute
2859 , product_attr_value = p_PRICING_ATTR_rec.product_attr_value
2860 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
2861 exception
2862 when NO_DATA_FOUND then
2863 null;
2864 end;
2865 END IF;
2866
2867 ELSIF ( p_PRICING_ATTR_rec.operation = QP_GLOBALS.G_OPR_CREATE AND p_PRICING_ATTR_rec.excluder_flag = 'N') THEN --14603635
2868 begin
2869 update qp_pricing_attributes
2870 set product_attribute = p_PRICING_ATTR_rec.product_attribute
2871 , product_attr_value = p_PRICING_ATTR_rec.product_attr_value
2872 where list_line_id = p_PRICING_ATTR_rec.list_line_id;
2873 exception
2874 when NO_DATA_FOUND then
2875 null;
2876 end;
2877 END IF;
2878 --end 10421292
2879
2880 -- mkarya for pattern - get the list_header_id as it is not populated yet in the record
2881 -- added for bug No 3384576 in discussion with mkarya
2882
2883 IF l_PRICING_ATTR_rec.list_header_id IS NOT NULL THEN
2884 l_list_header_id := l_PRICING_ATTR_rec.list_header_id;
2885 ELSE
2886
2887 select list_header_id
2888 into l_list_header_id
2889 from qp_list_lines
2890 where list_line_id = l_PRICING_ATTR_rec.list_line_id;
2891
2892 END IF;
2893
2894 x_PRICING_ATTR_rec := l_PRICING_ATTR_rec;
2895 x_PRICING_ATTR_rec.list_header_id := l_list_header_id;
2896
2897 IF ( p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
2898
2899 oe_debug_pub.add('Logging a request to update qualification_ind ', 1);
2900
2901 --hw
2902 -- delayed request for changed lines
2903 if QP_PERF_PVT.enabled = 'Y' then
2904 begin
2905 select 'Y' into l_changed_lines
2906 from qp_rltd_modifiers
2907 where to_rltd_modifier_id = p_PRICING_ATTR_rec.list_line_id
2908 and rltd_modifier_grp_type in ('BENEFIT', 'QUALIFIER');
2909 exception
2910 when no_data_found then
2911 l_changed_lines := 'N';
2912 when others then
2913 l_changed_lines := 'N';
2914 end;
2915
2916 if l_changed_lines = 'Y' then
2917 qp_delayed_requests_pvt.log_request(
2918 p_entity_code => QP_GLOBALS.G_ENTITY_MODIFIER_LIST,
2919 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
2920 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_MODIFIERS,
2921 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
2922 p_request_type => QP_GLOBALS.G_UPDATE_CHANGED_LINES_DEL,
2923 p_param1 => p_PRICING_ATTR_rec.pricing_phase_id,
2924 p_param2 => p_PRICING_ATTR_rec.list_header_id,
2925 p_param3 => p_PRICING_ATTR_rec.product_attribute,
2926 p_param4 => p_PRICING_ATTR_rec.product_attr_value,
2927 x_return_status => l_return_status);
2928 end if;
2929 end if;
2930
2931
2932 qp_delayed_requests_PVT.log_request(
2933 p_entity_code => QP_GLOBALS.G_ENTITY_MODIFIERS,
2934 p_entity_id => p_PRICING_ATTR_rec.list_line_id,
2935 p_requesting_entity_code=> QP_GLOBALS.G_ENTITY_MODIFIERS,
2936 p_requesting_entity_id => p_PRICING_ATTR_rec.list_line_id,
2937 p_request_type =>QP_GLOBALS.G_UPDATE_LINE_QUAL_IND,
2938 x_return_status => l_return_status);
2939 END IF;
2940 -- pattern
2941 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'Y' THEN
2942 IF (p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_CREATE) THEN
2943 qp_delayed_requests_pvt.log_request(
2944 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2945 p_entity_id => l_list_header_id,
2946 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
2947 p_request_unique_key2 => 'I',
2948 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2949 p_requesting_entity_id => l_list_header_id,
2950 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
2951 x_return_status => l_return_status);
2952
2953 END IF;
2954 IF ( p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
2955 qp_delayed_requests_pvt.log_request(
2956 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2957 p_entity_id => p_PRICING_ATTR_rec.list_header_id,
2958 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
2959 p_request_unique_key2 => 'U',
2960 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2961 p_requesting_entity_id => p_PRICING_ATTR_rec.list_header_id,
2962 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
2963 x_return_status => l_return_status);
2964 END IF;
2965 IF ( p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
2966 qp_delayed_requests_pvt.log_request(
2967 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2968 p_entity_id => p_PRICING_ATTR_rec.list_header_id,
2969 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
2970 p_request_unique_key2 => 'D',
2971 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2972 p_requesting_entity_id => p_PRICING_ATTR_rec.list_header_id,
2973 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
2974 x_return_status => l_return_status);
2975 END IF;
2976 END IF; --Java Engine Installed
2977 -- pattern
2978 -- jagan's PL/SQL pattern
2979 IF QP_JAVA_ENGINE_UTIL_PUB.Java_Engine_Installed = 'N' THEN
2980 IF FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'M' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'B' OR FND_PROFILE.VALUE('QP_PATTERN_SEARCH') = 'P' THEN
2981 IF (p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_CREATE) THEN
2982 qp_delayed_requests_pvt.log_request(
2983 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2984 p_entity_id => l_list_header_id,
2985 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
2986 p_request_unique_key2 => 'I',
2987 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2988 p_requesting_entity_id => l_list_header_id,
2989 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
2990 x_return_status => l_return_status);
2991
2992 END IF;
2993 IF ( p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_UPDATE) THEN
2994 qp_delayed_requests_pvt.log_request(
2995 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
2996 p_entity_id => p_PRICING_ATTR_rec.list_header_id,
2997 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
2998 p_request_unique_key2 => 'U',
2999 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3000 p_requesting_entity_id => p_PRICING_ATTR_rec.list_header_id,
3001 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
3002 x_return_status => l_return_status);
3003 END IF;
3004 IF ( p_PRICING_ATTR_rec.operation = OE_GLOBALS.G_OPR_DELETE) THEN
3005 qp_delayed_requests_pvt.log_request(
3006 p_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3007 p_entity_id => p_PRICING_ATTR_rec.list_header_id,
3008 p_request_unique_key1 => p_PRICING_ATTR_rec.list_line_id,
3009 p_request_unique_key2 => 'D',
3010 p_requesting_entity_code => QP_GLOBALS.G_ENTITY_ALL,
3011 p_requesting_entity_id => p_PRICING_ATTR_rec.list_header_id,
3012 p_request_type => QP_GLOBALS.G_MAINTAIN_PRODUCT_PATTERN,
3013 x_return_status => l_return_status);
3014 END IF;
3015 END IF; --PL/SQL pattern search
3016 END IF; -- Java Engine Installed
3017
3018 EXCEPTION
3019 WHEN FND_API.G_EXC_ERROR THEN
3020 RAISE;
3021 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3022 RAISE;
3023 WHEN OTHERS THEN
3024 IF oe_msg_pub.Check_Msg_Level(oe_msg_pub.G_MSG_LVL_UNEXP_ERROR)
3025 THEN
3026 oe_msg_pub.Add_Exc_Msg
3027 ( G_PKG_NAME
3028 , 'Pre_Write_Process'
3029 );
3030 END IF;
3031 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3032 END Pre_Write_Process;
3033
3034 END QP_Pricing_Attr_Util;