[Home] [Help]
PACKAGE BODY: APPS.QP_PSG_UTIL
Source
1 PACKAGE BODY QP_Psg_Util AS
2 /* $Header: QPXUPSGB.pls 120.1 2005/06/12 23:45:47 appldev $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Psg_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_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
13 , p_old_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type :=
14 QP_Attr_Map_PUB.G_MISS_PSG_REC
15 , x_PSG_rec OUT NOCOPY /* file.sql.39 change */ QP_Attr_Map_PUB.Psg_Rec_Type
16 )
17 IS
18 l_index NUMBER := 0;
19 l_src_attr_tbl QP_GLOBALS.NUMBER_Tbl_Type;
20 l_dep_attr_tbl QP_GLOBALS.NUMBER_Tbl_Type;
21 BEGIN
22
23 -- Load out record
24
25 x_PSG_rec := p_PSG_rec;
26
27 -- If attr_id is missing compare old and new records and for
28 -- every changed attribute clear its dependent fields.
29
30 IF p_attr_id = FND_API.G_MISS_NUM THEN
31
32 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute1,p_old_PSG_rec.attribute1)
33 THEN
34 l_index := l_index + 1;
35 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE1;
36 END IF;
37
38 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute10,p_old_PSG_rec.attribute10)
39 THEN
40 l_index := l_index + 1;
41 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE10;
42 END IF;
43
44 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute11,p_old_PSG_rec.attribute11)
45 THEN
46 l_index := l_index + 1;
47 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE11;
48 END IF;
49
50 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute12,p_old_PSG_rec.attribute12)
51 THEN
52 l_index := l_index + 1;
53 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE12;
54 END IF;
55
56 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute13,p_old_PSG_rec.attribute13)
57 THEN
58 l_index := l_index + 1;
59 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE13;
60 END IF;
61
62 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute14,p_old_PSG_rec.attribute14)
63 THEN
64 l_index := l_index + 1;
65 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE14;
66 END IF;
67
68 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute15,p_old_PSG_rec.attribute15)
69 THEN
70 l_index := l_index + 1;
71 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE15;
72 END IF;
73
74 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute2,p_old_PSG_rec.attribute2)
75 THEN
76 l_index := l_index + 1;
77 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE2;
78 END IF;
79
80 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute3,p_old_PSG_rec.attribute3)
81 THEN
82 l_index := l_index + 1;
83 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE3;
84 END IF;
85
86 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute4,p_old_PSG_rec.attribute4)
87 THEN
88 l_index := l_index + 1;
89 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE4;
90 END IF;
91
92 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute5,p_old_PSG_rec.attribute5)
93 THEN
94 l_index := l_index + 1;
95 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE5;
96 END IF;
97
98 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute6,p_old_PSG_rec.attribute6)
99 THEN
100 l_index := l_index + 1;
101 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE6;
102 END IF;
103
104 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute7,p_old_PSG_rec.attribute7)
105 THEN
106 l_index := l_index + 1;
107 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE7;
108 END IF;
109
110 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute8,p_old_PSG_rec.attribute8)
111 THEN
112 l_index := l_index + 1;
113 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE8;
114 END IF;
115
116 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute9,p_old_PSG_rec.attribute9)
117 THEN
118 l_index := l_index + 1;
119 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE9;
120 END IF;
121
122 IF NOT QP_GLOBALS.Equal(p_PSG_rec.context,p_old_PSG_rec.context)
123 THEN
124 l_index := l_index + 1;
125 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_CONTEXT;
126 END IF;
127
128 IF NOT QP_GLOBALS.Equal(p_PSG_rec.created_by,p_old_PSG_rec.created_by)
129 THEN
130 l_index := l_index + 1;
131 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_CREATED_BY;
132 END IF;
133
134 IF NOT QP_GLOBALS.Equal(p_PSG_rec.creation_date,p_old_PSG_rec.creation_date)
135 THEN
136 l_index := l_index + 1;
137 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_CREATION_DATE;
138 END IF;
139
140 IF NOT QP_GLOBALS.Equal(p_PSG_rec.last_updated_by,p_old_PSG_rec.last_updated_by)
141 THEN
142 l_index := l_index + 1;
143 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LAST_UPDATED_BY;
144 END IF;
145
146 IF NOT QP_GLOBALS.Equal(p_PSG_rec.last_update_date,p_old_PSG_rec.last_update_date)
147 THEN
148 l_index := l_index + 1;
149 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LAST_UPDATE_DATE;
150 END IF;
151
152 IF NOT QP_GLOBALS.Equal(p_PSG_rec.last_update_login,p_old_PSG_rec.last_update_login)
153 THEN
154 l_index := l_index + 1;
155 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LAST_UPDATE_LOGIN;
156 END IF;
157
158 IF NOT QP_GLOBALS.Equal(p_PSG_rec.limits_enabled,p_old_PSG_rec.limits_enabled)
159 THEN
160 l_index := l_index + 1;
161 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LIMITS_ENABLED;
162 END IF;
163
164 IF NOT QP_GLOBALS.Equal(p_PSG_rec.lov_enabled,p_old_PSG_rec.lov_enabled)
165 THEN
166 l_index := l_index + 1;
167 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LOV_ENABLED;
168 END IF;
169
170 IF NOT QP_GLOBALS.Equal(p_PSG_rec.program_application_id,p_old_PSG_rec.program_application_id)
171 THEN
172 l_index := l_index + 1;
173 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PROGRAM_APPLICATION;
174 END IF;
175
176 IF NOT QP_GLOBALS.Equal(p_PSG_rec.program_id,p_old_PSG_rec.program_id)
177 THEN
178 l_index := l_index + 1;
179 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PROGRAM;
180 END IF;
181
182 IF NOT QP_GLOBALS.Equal(p_PSG_rec.program_update_date,p_old_PSG_rec.program_update_date)
183 THEN
184 l_index := l_index + 1;
185 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PROGRAM_UPDATE_DATE;
186 END IF;
187
188 IF NOT QP_GLOBALS.Equal(p_PSG_rec.pte_code,p_old_PSG_rec.pte_code)
189 THEN
190 l_index := l_index + 1;
191 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PTE;
192 END IF;
193
194 IF NOT QP_GLOBALS.Equal(p_PSG_rec.seeded_sourcing_method,p_old_PSG_rec.seeded_sourcing_method)
195 THEN
196 l_index := l_index + 1;
197 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEEDED_SOURCING_METHOD;
198 END IF;
199
200 IF NOT QP_GLOBALS.Equal(p_PSG_rec.segment_id,p_old_PSG_rec.segment_id)
201 THEN
202 l_index := l_index + 1;
203 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEGMENT;
204 END IF;
205
206 IF NOT QP_GLOBALS.Equal(p_PSG_rec.segment_level,p_old_PSG_rec.segment_level)
207 THEN
208 l_index := l_index + 1;
209 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEGMENT_LEVEL;
210 END IF;
211
212 IF NOT QP_GLOBALS.Equal(p_PSG_rec.segment_pte_id,p_old_PSG_rec.segment_pte_id)
213 THEN
214 l_index := l_index + 1;
215 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEGMENT_PTE;
216 END IF;
217
218 IF NOT QP_GLOBALS.Equal(p_PSG_rec.sourcing_enabled,p_old_PSG_rec.sourcing_enabled)
219 THEN
220 l_index := l_index + 1;
221 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SOURCING_ENABLED;
222 END IF;
223
224 IF NOT QP_GLOBALS.Equal(p_PSG_rec.sourcing_status,p_old_PSG_rec.sourcing_status)
225 THEN
226 l_index := l_index + 1;
227 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SOURCING_STATUS;
228 END IF;
229
230 IF NOT QP_GLOBALS.Equal(p_PSG_rec.user_sourcing_method,p_old_PSG_rec.user_sourcing_method)
231 THEN
232 l_index := l_index + 1;
233 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_USER_SOURCING_METHOD;
234 END IF;
235
236 ELSIF p_attr_id = G_ATTRIBUTE1 THEN
237 l_index := l_index + 1;
238 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE1;
239 ELSIF p_attr_id = G_ATTRIBUTE10 THEN
240 l_index := l_index + 1;
241 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE10;
242 ELSIF p_attr_id = G_ATTRIBUTE11 THEN
243 l_index := l_index + 1;
244 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE11;
245 ELSIF p_attr_id = G_ATTRIBUTE12 THEN
246 l_index := l_index + 1;
247 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE12;
248 ELSIF p_attr_id = G_ATTRIBUTE13 THEN
249 l_index := l_index + 1;
250 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE13;
251 ELSIF p_attr_id = G_ATTRIBUTE14 THEN
252 l_index := l_index + 1;
253 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE14;
254 ELSIF p_attr_id = G_ATTRIBUTE15 THEN
255 l_index := l_index + 1;
256 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE15;
257 ELSIF p_attr_id = G_ATTRIBUTE2 THEN
258 l_index := l_index + 1;
259 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE2;
260 ELSIF p_attr_id = G_ATTRIBUTE3 THEN
261 l_index := l_index + 1;
262 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE3;
263 ELSIF p_attr_id = G_ATTRIBUTE4 THEN
264 l_index := l_index + 1;
265 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE4;
266 ELSIF p_attr_id = G_ATTRIBUTE5 THEN
267 l_index := l_index + 1;
268 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE5;
269 ELSIF p_attr_id = G_ATTRIBUTE6 THEN
270 l_index := l_index + 1;
271 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE6;
272 ELSIF p_attr_id = G_ATTRIBUTE7 THEN
273 l_index := l_index + 1;
274 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE7;
275 ELSIF p_attr_id = G_ATTRIBUTE8 THEN
276 l_index := l_index + 1;
277 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE8;
278 ELSIF p_attr_id = G_ATTRIBUTE9 THEN
279 l_index := l_index + 1;
280 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_ATTRIBUTE9;
281 ELSIF p_attr_id = G_CONTEXT THEN
282 l_index := l_index + 1;
283 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_CONTEXT;
284 ELSIF p_attr_id = G_CREATED_BY THEN
285 l_index := l_index + 1;
286 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_CREATED_BY;
287 ELSIF p_attr_id = G_CREATION_DATE THEN
288 l_index := l_index + 1;
289 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_CREATION_DATE;
290 ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
291 l_index := l_index + 1;
292 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LAST_UPDATED_BY;
293 ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
294 l_index := l_index + 1;
295 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LAST_UPDATE_DATE;
296 ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
297 l_index := l_index + 1;
298 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LAST_UPDATE_LOGIN;
299 ELSIF p_attr_id = G_LIMITS_ENABLED THEN
300 l_index := l_index + 1;
301 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LIMITS_ENABLED;
302 ELSIF p_attr_id = G_LOV_ENABLED THEN
303 l_index := l_index + 1;
304 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_LOV_ENABLED;
305 ELSIF p_attr_id = G_PROGRAM_APPLICATION THEN
306 l_index := l_index + 1;
307 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PROGRAM_APPLICATION;
308 ELSIF p_attr_id = G_PROGRAM THEN
309 l_index := l_index + 1;
310 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PROGRAM;
311 ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
312 l_index := l_index + 1;
313 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PROGRAM_UPDATE_DATE;
314 ELSIF p_attr_id = G_PTE THEN
315 l_index := l_index + 1;
316 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_PTE;
317 ELSIF p_attr_id = G_SEEDED_SOURCING_METHOD THEN
318 l_index := l_index + 1;
319 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEEDED_SOURCING_METHOD;
320 ELSIF p_attr_id = G_SEGMENT THEN
321 l_index := l_index + 1;
322 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEGMENT;
323 ELSIF p_attr_id = G_SEGMENT_LEVEL THEN
324 l_index := l_index + 1;
325 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEGMENT_LEVEL;
326 ELSIF p_attr_id = G_SEGMENT_PTE THEN
327 l_index := l_index + 1;
328 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SEGMENT_PTE;
329 ELSIF p_attr_id = G_SOURCING_ENABLED THEN
330 l_index := l_index + 1;
331 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SOURCING_ENABLED;
332 ELSIF p_attr_id = G_SOURCING_STATUS THEN
333 l_index := l_index + 1;
334 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_SOURCING_STATUS;
335 ELSIF p_attr_id = G_USER_SOURCING_METHOD THEN
336 l_index := l_index + 1;
337 l_src_attr_tbl(l_index) := QP_PSG_UTIL.G_USER_SOURCING_METHOD;
338 END IF;
339
340 END Clear_Dependent_Attr;
341
342 -- Procedure Apply_Attribute_Changes
343
344 PROCEDURE Apply_Attribute_Changes
345 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
346 , p_old_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type :=
347 QP_Attr_Map_PUB.G_MISS_PSG_REC
348 , x_PSG_rec OUT NOCOPY /* file.sql.39 change */ QP_Attr_Map_PUB.Psg_Rec_Type
349 )
350 IS
351 BEGIN
352
353 -- Load out record
354
355 x_PSG_rec := p_PSG_rec;
356
357 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute1,p_old_PSG_rec.attribute1)
358 THEN
359 NULL;
360 END IF;
361
362 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute10,p_old_PSG_rec.attribute10)
363 THEN
364 NULL;
365 END IF;
366
367 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute11,p_old_PSG_rec.attribute11)
368 THEN
369 NULL;
370 END IF;
371
372 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute12,p_old_PSG_rec.attribute12)
373 THEN
374 NULL;
375 END IF;
376
377 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute13,p_old_PSG_rec.attribute13)
378 THEN
379 NULL;
380 END IF;
381
382 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute14,p_old_PSG_rec.attribute14)
383 THEN
384 NULL;
385 END IF;
386
387 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute15,p_old_PSG_rec.attribute15)
388 THEN
389 NULL;
390 END IF;
391
392 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute2,p_old_PSG_rec.attribute2)
393 THEN
394 NULL;
395 END IF;
396
397 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute3,p_old_PSG_rec.attribute3)
398 THEN
399 NULL;
400 END IF;
401
402 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute4,p_old_PSG_rec.attribute4)
403 THEN
404 NULL;
405 END IF;
406
407 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute5,p_old_PSG_rec.attribute5)
408 THEN
409 NULL;
410 END IF;
411
412 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute6,p_old_PSG_rec.attribute6)
413 THEN
414 NULL;
415 END IF;
416
417 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute7,p_old_PSG_rec.attribute7)
418 THEN
419 NULL;
420 END IF;
421
422 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute8,p_old_PSG_rec.attribute8)
423 THEN
424 NULL;
425 END IF;
426
427 IF NOT QP_GLOBALS.Equal(p_PSG_rec.attribute9,p_old_PSG_rec.attribute9)
428 THEN
429 NULL;
430 END IF;
431
432 IF NOT QP_GLOBALS.Equal(p_PSG_rec.context,p_old_PSG_rec.context)
433 THEN
434 NULL;
435 END IF;
436
437 IF NOT QP_GLOBALS.Equal(p_PSG_rec.created_by,p_old_PSG_rec.created_by)
438 THEN
439 NULL;
440 END IF;
441
442 IF NOT QP_GLOBALS.Equal(p_PSG_rec.creation_date,p_old_PSG_rec.creation_date)
443 THEN
444 NULL;
445 END IF;
446
447 IF NOT QP_GLOBALS.Equal(p_PSG_rec.last_updated_by,p_old_PSG_rec.last_updated_by)
448 THEN
449 NULL;
450 END IF;
451
452 IF NOT QP_GLOBALS.Equal(p_PSG_rec.last_update_date,p_old_PSG_rec.last_update_date)
453 THEN
454 NULL;
455 END IF;
456
457 IF NOT QP_GLOBALS.Equal(p_PSG_rec.last_update_login,p_old_PSG_rec.last_update_login)
458 THEN
459 NULL;
460 END IF;
461
462 IF NOT QP_GLOBALS.Equal(p_PSG_rec.limits_enabled,p_old_PSG_rec.limits_enabled)
463 THEN
464 NULL;
465 END IF;
466
467 IF NOT QP_GLOBALS.Equal(p_PSG_rec.lov_enabled,p_old_PSG_rec.lov_enabled)
468 THEN
469 NULL;
470 END IF;
471
472 IF NOT QP_GLOBALS.Equal(p_PSG_rec.program_application_id,p_old_PSG_rec.program_application_id)
473 THEN
474 NULL;
475 END IF;
476
477 IF NOT QP_GLOBALS.Equal(p_PSG_rec.program_id,p_old_PSG_rec.program_id)
478 THEN
479 NULL;
480 END IF;
481
482 IF NOT QP_GLOBALS.Equal(p_PSG_rec.program_update_date,p_old_PSG_rec.program_update_date)
483 THEN
484 NULL;
485 END IF;
486
487 IF NOT QP_GLOBALS.Equal(p_PSG_rec.pte_code,p_old_PSG_rec.pte_code)
488 THEN
489 NULL;
490 END IF;
491
492 IF NOT QP_GLOBALS.Equal(p_PSG_rec.seeded_sourcing_method,p_old_PSG_rec.seeded_sourcing_method)
493 THEN
494 NULL;
495 END IF;
496
497 IF NOT QP_GLOBALS.Equal(p_PSG_rec.segment_id,p_old_PSG_rec.segment_id)
498 THEN
499 NULL;
500 END IF;
501
502 IF NOT QP_GLOBALS.Equal(p_PSG_rec.segment_level,p_old_PSG_rec.segment_level)
503 THEN
504 NULL;
505 END IF;
506
507 IF NOT QP_GLOBALS.Equal(p_PSG_rec.segment_pte_id,p_old_PSG_rec.segment_pte_id)
508 THEN
509 NULL;
510 END IF;
511
512 IF NOT QP_GLOBALS.Equal(p_PSG_rec.sourcing_enabled,p_old_PSG_rec.sourcing_enabled)
513 THEN
514 NULL;
515 END IF;
516
517 IF NOT QP_GLOBALS.Equal(p_PSG_rec.sourcing_status,p_old_PSG_rec.sourcing_status)
518 THEN
519 NULL;
520 END IF;
521
522 IF NOT QP_GLOBALS.Equal(p_PSG_rec.user_sourcing_method,p_old_PSG_rec.user_sourcing_method)
523 THEN
524 NULL;
525 END IF;
526
527 END Apply_Attribute_Changes;
528
529 -- Function Complete_Record
530
531 FUNCTION Complete_Record
532 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
533 , p_old_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
534 ) RETURN QP_Attr_Map_PUB.Psg_Rec_Type
535 IS
536 l_PSG_rec QP_Attr_Map_PUB.Psg_Rec_Type := p_PSG_rec;
537 BEGIN
538
539 IF l_PSG_rec.attribute1 = FND_API.G_MISS_CHAR THEN
540 l_PSG_rec.attribute1 := p_old_PSG_rec.attribute1;
541 END IF;
542
543 IF l_PSG_rec.attribute10 = FND_API.G_MISS_CHAR THEN
544 l_PSG_rec.attribute10 := p_old_PSG_rec.attribute10;
545 END IF;
546
547 IF l_PSG_rec.attribute11 = FND_API.G_MISS_CHAR THEN
548 l_PSG_rec.attribute11 := p_old_PSG_rec.attribute11;
549 END IF;
550
551 IF l_PSG_rec.attribute12 = FND_API.G_MISS_CHAR THEN
552 l_PSG_rec.attribute12 := p_old_PSG_rec.attribute12;
553 END IF;
554
555 IF l_PSG_rec.attribute13 = FND_API.G_MISS_CHAR THEN
556 l_PSG_rec.attribute13 := p_old_PSG_rec.attribute13;
557 END IF;
558
559 IF l_PSG_rec.attribute14 = FND_API.G_MISS_CHAR THEN
560 l_PSG_rec.attribute14 := p_old_PSG_rec.attribute14;
561 END IF;
562
563 IF l_PSG_rec.attribute15 = FND_API.G_MISS_CHAR THEN
564 l_PSG_rec.attribute15 := p_old_PSG_rec.attribute15;
565 END IF;
566
567 IF l_PSG_rec.attribute2 = FND_API.G_MISS_CHAR THEN
568 l_PSG_rec.attribute2 := p_old_PSG_rec.attribute2;
569 END IF;
570
571 IF l_PSG_rec.attribute3 = FND_API.G_MISS_CHAR THEN
572 l_PSG_rec.attribute3 := p_old_PSG_rec.attribute3;
573 END IF;
574
575 IF l_PSG_rec.attribute4 = FND_API.G_MISS_CHAR THEN
576 l_PSG_rec.attribute4 := p_old_PSG_rec.attribute4;
577 END IF;
578
579 IF l_PSG_rec.attribute5 = FND_API.G_MISS_CHAR THEN
580 l_PSG_rec.attribute5 := p_old_PSG_rec.attribute5;
581 END IF;
582
583 IF l_PSG_rec.attribute6 = FND_API.G_MISS_CHAR THEN
584 l_PSG_rec.attribute6 := p_old_PSG_rec.attribute6;
585 END IF;
586
587 IF l_PSG_rec.attribute7 = FND_API.G_MISS_CHAR THEN
588 l_PSG_rec.attribute7 := p_old_PSG_rec.attribute7;
589 END IF;
590
591 IF l_PSG_rec.attribute8 = FND_API.G_MISS_CHAR THEN
592 l_PSG_rec.attribute8 := p_old_PSG_rec.attribute8;
593 END IF;
594
595 IF l_PSG_rec.attribute9 = FND_API.G_MISS_CHAR THEN
596 l_PSG_rec.attribute9 := p_old_PSG_rec.attribute9;
597 END IF;
598
599 IF l_PSG_rec.context = FND_API.G_MISS_CHAR THEN
600 l_PSG_rec.context := p_old_PSG_rec.context;
601 END IF;
602
603 IF l_PSG_rec.created_by = FND_API.G_MISS_NUM THEN
604 l_PSG_rec.created_by := p_old_PSG_rec.created_by;
605 END IF;
606
607 IF l_PSG_rec.creation_date = FND_API.G_MISS_DATE THEN
608 l_PSG_rec.creation_date := p_old_PSG_rec.creation_date;
609 END IF;
610
611 IF l_PSG_rec.last_updated_by = FND_API.G_MISS_NUM THEN
612 l_PSG_rec.last_updated_by := p_old_PSG_rec.last_updated_by;
613 END IF;
614
615 IF l_PSG_rec.last_update_date = FND_API.G_MISS_DATE THEN
616 l_PSG_rec.last_update_date := p_old_PSG_rec.last_update_date;
617 END IF;
618
619 IF l_PSG_rec.last_update_login = FND_API.G_MISS_NUM THEN
620 l_PSG_rec.last_update_login := p_old_PSG_rec.last_update_login;
621 END IF;
622
623 IF l_PSG_rec.limits_enabled = FND_API.G_MISS_CHAR THEN
624 l_PSG_rec.limits_enabled := p_old_PSG_rec.limits_enabled;
625 END IF;
626
627 IF l_PSG_rec.lov_enabled = FND_API.G_MISS_CHAR THEN
628 l_PSG_rec.lov_enabled := p_old_PSG_rec.lov_enabled;
629 END IF;
630
631 IF l_PSG_rec.program_application_id = FND_API.G_MISS_NUM THEN
632 l_PSG_rec.program_application_id := p_old_PSG_rec.program_application_id;
633 END IF;
634
635 IF l_PSG_rec.program_id = FND_API.G_MISS_NUM THEN
636 l_PSG_rec.program_id := p_old_PSG_rec.program_id;
637 END IF;
638
639 IF l_PSG_rec.program_update_date = FND_API.G_MISS_DATE THEN
640 l_PSG_rec.program_update_date := p_old_PSG_rec.program_update_date;
641 END IF;
642
643 IF l_PSG_rec.pte_code = FND_API.G_MISS_CHAR THEN
644 l_PSG_rec.pte_code := p_old_PSG_rec.pte_code;
645 END IF;
646
647 IF l_PSG_rec.seeded_sourcing_method = FND_API.G_MISS_CHAR THEN
648 l_PSG_rec.seeded_sourcing_method := p_old_PSG_rec.seeded_sourcing_method;
649 END IF;
650
651 IF l_PSG_rec.segment_id = FND_API.G_MISS_NUM THEN
652 l_PSG_rec.segment_id := p_old_PSG_rec.segment_id;
653 END IF;
654
655 IF l_PSG_rec.segment_level = FND_API.G_MISS_CHAR THEN
656 l_PSG_rec.segment_level := p_old_PSG_rec.segment_level;
657 END IF;
658
659 IF l_PSG_rec.segment_pte_id = FND_API.G_MISS_NUM THEN
660 l_PSG_rec.segment_pte_id := p_old_PSG_rec.segment_pte_id;
661 END IF;
662
663 IF l_PSG_rec.sourcing_enabled = FND_API.G_MISS_CHAR THEN
664 l_PSG_rec.sourcing_enabled := p_old_PSG_rec.sourcing_enabled;
665 END IF;
666
667 IF l_PSG_rec.sourcing_status = FND_API.G_MISS_CHAR THEN
668 l_PSG_rec.sourcing_status := p_old_PSG_rec.sourcing_status;
669 END IF;
670
671 IF l_PSG_rec.user_sourcing_method = FND_API.G_MISS_CHAR THEN
672 l_PSG_rec.user_sourcing_method := p_old_PSG_rec.user_sourcing_method;
673 END IF;
674
675 RETURN l_PSG_rec;
676
677 END Complete_Record;
678
679 -- Function Convert_Miss_To_Null
680
681 FUNCTION Convert_Miss_To_Null
682 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
683 ) RETURN QP_Attr_Map_PUB.Psg_Rec_Type
684 IS
685 l_PSG_rec QP_Attr_Map_PUB.Psg_Rec_Type := p_PSG_rec;
686 BEGIN
687
688 IF l_PSG_rec.attribute1 = FND_API.G_MISS_CHAR THEN
689 l_PSG_rec.attribute1 := NULL;
690 END IF;
691
692 IF l_PSG_rec.attribute10 = FND_API.G_MISS_CHAR THEN
693 l_PSG_rec.attribute10 := NULL;
694 END IF;
695
696 IF l_PSG_rec.attribute11 = FND_API.G_MISS_CHAR THEN
697 l_PSG_rec.attribute11 := NULL;
698 END IF;
699
700 IF l_PSG_rec.attribute12 = FND_API.G_MISS_CHAR THEN
701 l_PSG_rec.attribute12 := NULL;
702 END IF;
703
704 IF l_PSG_rec.attribute13 = FND_API.G_MISS_CHAR THEN
705 l_PSG_rec.attribute13 := NULL;
706 END IF;
707
708 IF l_PSG_rec.attribute14 = FND_API.G_MISS_CHAR THEN
709 l_PSG_rec.attribute14 := NULL;
710 END IF;
711
712 IF l_PSG_rec.attribute15 = FND_API.G_MISS_CHAR THEN
713 l_PSG_rec.attribute15 := NULL;
714 END IF;
715
716 IF l_PSG_rec.attribute2 = FND_API.G_MISS_CHAR THEN
717 l_PSG_rec.attribute2 := NULL;
718 END IF;
719
720 IF l_PSG_rec.attribute3 = FND_API.G_MISS_CHAR THEN
721 l_PSG_rec.attribute3 := NULL;
722 END IF;
723
724 IF l_PSG_rec.attribute4 = FND_API.G_MISS_CHAR THEN
725 l_PSG_rec.attribute4 := NULL;
726 END IF;
727
728 IF l_PSG_rec.attribute5 = FND_API.G_MISS_CHAR THEN
729 l_PSG_rec.attribute5 := NULL;
730 END IF;
731
732 IF l_PSG_rec.attribute6 = FND_API.G_MISS_CHAR THEN
733 l_PSG_rec.attribute6 := NULL;
734 END IF;
735
736 IF l_PSG_rec.attribute7 = FND_API.G_MISS_CHAR THEN
737 l_PSG_rec.attribute7 := NULL;
738 END IF;
739
740 IF l_PSG_rec.attribute8 = FND_API.G_MISS_CHAR THEN
741 l_PSG_rec.attribute8 := NULL;
742 END IF;
743
744 IF l_PSG_rec.attribute9 = FND_API.G_MISS_CHAR THEN
745 l_PSG_rec.attribute9 := NULL;
746 END IF;
747
748 IF l_PSG_rec.context = FND_API.G_MISS_CHAR THEN
749 l_PSG_rec.context := NULL;
750 END IF;
751
752 IF l_PSG_rec.created_by = FND_API.G_MISS_NUM THEN
753 l_PSG_rec.created_by := NULL;
754 END IF;
755
756 IF l_PSG_rec.creation_date = FND_API.G_MISS_DATE THEN
757 l_PSG_rec.creation_date := NULL;
758 END IF;
759
760 IF l_PSG_rec.last_updated_by = FND_API.G_MISS_NUM THEN
761 l_PSG_rec.last_updated_by := NULL;
762 END IF;
763
764 IF l_PSG_rec.last_update_date = FND_API.G_MISS_DATE THEN
765 l_PSG_rec.last_update_date := NULL;
766 END IF;
767
768 IF l_PSG_rec.last_update_login = FND_API.G_MISS_NUM THEN
769 l_PSG_rec.last_update_login := NULL;
770 END IF;
771
772 IF l_PSG_rec.limits_enabled = FND_API.G_MISS_CHAR THEN
773 l_PSG_rec.limits_enabled := NULL;
774 END IF;
775
776 IF l_PSG_rec.lov_enabled = FND_API.G_MISS_CHAR THEN
777 l_PSG_rec.lov_enabled := NULL;
778 END IF;
779
780 IF l_PSG_rec.program_application_id = FND_API.G_MISS_NUM THEN
781 l_PSG_rec.program_application_id := NULL;
782 END IF;
783
784 IF l_PSG_rec.program_id = FND_API.G_MISS_NUM THEN
785 l_PSG_rec.program_id := NULL;
786 END IF;
787
788 IF l_PSG_rec.program_update_date = FND_API.G_MISS_DATE THEN
789 l_PSG_rec.program_update_date := NULL;
790 END IF;
791
792 IF l_PSG_rec.pte_code = FND_API.G_MISS_CHAR THEN
793 l_PSG_rec.pte_code := NULL;
794 END IF;
795
796 IF l_PSG_rec.seeded_sourcing_method = FND_API.G_MISS_CHAR THEN
797 l_PSG_rec.seeded_sourcing_method := NULL;
798 END IF;
799
800 IF l_PSG_rec.segment_id = FND_API.G_MISS_NUM THEN
801 l_PSG_rec.segment_id := NULL;
802 END IF;
803
804 IF l_PSG_rec.segment_level = FND_API.G_MISS_CHAR THEN
805 l_PSG_rec.segment_level := NULL;
806 END IF;
807
808 IF l_PSG_rec.segment_pte_id = FND_API.G_MISS_NUM THEN
809 l_PSG_rec.segment_pte_id := NULL;
810 END IF;
811
812 IF l_PSG_rec.sourcing_enabled = FND_API.G_MISS_CHAR THEN
813 l_PSG_rec.sourcing_enabled := NULL;
814 END IF;
815
816 IF l_PSG_rec.sourcing_status = FND_API.G_MISS_CHAR THEN
817 l_PSG_rec.sourcing_status := NULL;
818 END IF;
819
820 IF l_PSG_rec.user_sourcing_method = FND_API.G_MISS_CHAR THEN
821 l_PSG_rec.user_sourcing_method := NULL;
822 END IF;
823
824 RETURN l_PSG_rec;
825
826 END Convert_Miss_To_Null;
827
828 -- Procedure Update_Row
829
830 PROCEDURE Update_Row
831 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
832 )
833 IS
834 BEGIN
835
836 UPDATE QP_PTE_SEGMENTS
837 SET ATTRIBUTE1 = p_PSG_rec.attribute1
838 , ATTRIBUTE10 = p_PSG_rec.attribute10
839 , ATTRIBUTE11 = p_PSG_rec.attribute11
840 , ATTRIBUTE12 = p_PSG_rec.attribute12
841 , ATTRIBUTE13 = p_PSG_rec.attribute13
842 , ATTRIBUTE14 = p_PSG_rec.attribute14
843 , ATTRIBUTE15 = p_PSG_rec.attribute15
844 , ATTRIBUTE2 = p_PSG_rec.attribute2
845 , ATTRIBUTE3 = p_PSG_rec.attribute3
846 , ATTRIBUTE4 = p_PSG_rec.attribute4
847 , ATTRIBUTE5 = p_PSG_rec.attribute5
848 , ATTRIBUTE6 = p_PSG_rec.attribute6
849 , ATTRIBUTE7 = p_PSG_rec.attribute7
850 , ATTRIBUTE8 = p_PSG_rec.attribute8
851 , ATTRIBUTE9 = p_PSG_rec.attribute9
852 , CONTEXT = p_PSG_rec.context
853 , CREATED_BY = p_PSG_rec.created_by
854 , CREATION_DATE = p_PSG_rec.creation_date
855 , LAST_UPDATED_BY = p_PSG_rec.last_updated_by
856 , LAST_UPDATE_DATE = p_PSG_rec.last_update_date
857 , LAST_UPDATE_LOGIN = p_PSG_rec.last_update_login
858 , LIMITS_ENABLED = p_PSG_rec.limits_enabled
859 , LOV_ENABLED = p_PSG_rec.lov_enabled
860 , PROGRAM_APPLICATION_ID = p_PSG_rec.program_application_id
861 , PROGRAM_ID = p_PSG_rec.program_id
862 , PROGRAM_UPDATE_DATE = p_PSG_rec.program_update_date
863 , PTE_CODE = p_PSG_rec.pte_code
864 , SEEDED_SOURCING_METHOD = p_PSG_rec.seeded_sourcing_method
865 , SEGMENT_ID = p_PSG_rec.segment_id
866 , SEGMENT_LEVEL = p_PSG_rec.segment_level
867 , SEGMENT_PTE_ID = p_PSG_rec.segment_pte_id
868 , SOURCING_ENABLED = p_PSG_rec.sourcing_enabled
869 , SOURCING_STATUS = p_PSG_rec.sourcing_status
870 , USER_SOURCING_METHOD = p_PSG_rec.user_sourcing_method
871 WHERE SEGMENT_PTE_ID = p_PSG_rec.segment_pte_id
872 ;
873
874 EXCEPTION
875
876 WHEN OTHERS THEN
877
878 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
879 THEN
880 OE_MSG_PUB.Add_Exc_Msg
881 ( G_PKG_NAME
882 , 'Update_Row'
883 );
884 END IF;
885
886 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
887
888 END Update_Row;
889
890 -- Procedure Insert_Row
891
892 PROCEDURE Insert_Row
893 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
894 )
895 IS
896 BEGIN
897 oe_debug_pub.add('inserting data in psg ..............................');
898
899 INSERT INTO QP_PTE_SEGMENTS
900 ( ATTRIBUTE1
901 , ATTRIBUTE10
902 , ATTRIBUTE11
903 , ATTRIBUTE12
904 , ATTRIBUTE13
905 , ATTRIBUTE14
906 , ATTRIBUTE15
907 , ATTRIBUTE2
908 , ATTRIBUTE3
909 , ATTRIBUTE4
910 , ATTRIBUTE5
911 , ATTRIBUTE6
912 , ATTRIBUTE7
913 , ATTRIBUTE8
914 , ATTRIBUTE9
915 , CONTEXT
916 , CREATED_BY
917 , CREATION_DATE
918 , LAST_UPDATED_BY
919 , LAST_UPDATE_DATE
920 , LAST_UPDATE_LOGIN
921 , LIMITS_ENABLED
922 , LOV_ENABLED
923 , PROGRAM_APPLICATION_ID
924 , PROGRAM_ID
925 , PROGRAM_UPDATE_DATE
926 , PTE_CODE
927 , SEEDED_SOURCING_METHOD
928 , SEGMENT_ID
929 , SEGMENT_LEVEL
930 , SEGMENT_PTE_ID
931 , SOURCING_ENABLED
932 , SOURCING_STATUS
933 , USER_SOURCING_METHOD
934 )
935 VALUES
936 ( p_PSG_rec.attribute1
937 , p_PSG_rec.attribute10
938 , p_PSG_rec.attribute11
939 , p_PSG_rec.attribute12
940 , p_PSG_rec.attribute13
941 , p_PSG_rec.attribute14
942 , p_PSG_rec.attribute15
943 , p_PSG_rec.attribute2
944 , p_PSG_rec.attribute3
945 , p_PSG_rec.attribute4
946 , p_PSG_rec.attribute5
947 , p_PSG_rec.attribute6
948 , p_PSG_rec.attribute7
949 , p_PSG_rec.attribute8
950 , p_PSG_rec.attribute9
951 , p_PSG_rec.context
952 , p_PSG_rec.created_by
953 , p_PSG_rec.creation_date
954 , p_PSG_rec.last_updated_by
955 , p_PSG_rec.last_update_date
956 , p_PSG_rec.last_update_login
957 , p_PSG_rec.limits_enabled
958 , p_PSG_rec.lov_enabled
959 , p_PSG_rec.program_application_id
960 , p_PSG_rec.program_id
961 , p_PSG_rec.program_update_date
962 , p_PSG_rec.pte_code
963 , p_PSG_rec.seeded_sourcing_method
964 , p_PSG_rec.segment_id
965 , p_PSG_rec.segment_level
966 , p_PSG_rec.segment_pte_id
967 , p_PSG_rec.sourcing_enabled
968 , p_PSG_rec.sourcing_status
969 , p_PSG_rec.user_sourcing_method
970 );
971
972 EXCEPTION
973
974 WHEN OTHERS THEN
975
976 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
977 THEN
978 OE_MSG_PUB.Add_Exc_Msg
979 ( G_PKG_NAME
980 , 'Insert_Row'
981 );
982 END IF;
983
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985
986 END Insert_Row;
987
988 -- Procedure Delete_Row
989
990 PROCEDURE Delete_Row
991 ( p_segment_pte_id IN NUMBER
992 )
993 IS
994 BEGIN
995
996 DELETE FROM QP_PTE_SEGMENTS
997 WHERE SEGMENT_PTE_ID = p_segment_pte_id
998 ;
999
1000 EXCEPTION
1001
1002 WHEN OTHERS THEN
1003
1004 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1005 THEN
1006 OE_MSG_PUB.Add_Exc_Msg
1007 ( G_PKG_NAME
1008 , 'Delete_Row'
1009 );
1010 END IF;
1011
1012 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1013
1014 END Delete_Row;
1015
1016 -- Function Query_Row
1017
1018 FUNCTION Query_Row
1019 ( p_segment_pte_id IN NUMBER
1020 ) RETURN QP_Attr_Map_PUB.Psg_Rec_Type
1021 IS
1022 BEGIN
1023
1024 RETURN Query_Rows
1025 ( p_segment_pte_id => p_segment_pte_id
1026 )(1);
1027
1028 END Query_Row;
1029
1030 -- Function Query_Rows
1031
1032 --
1033
1034 FUNCTION Query_Rows
1035 ( p_segment_pte_id IN NUMBER :=
1036 FND_API.G_MISS_NUM
1037 , p_lookup_code IN VARCHAR2 :=
1038 FND_API.G_MISS_CHAR
1039 ) RETURN QP_Attr_Map_PUB.Psg_Tbl_Type
1040 IS
1041 l_PSG_rec QP_Attr_Map_PUB.Psg_Rec_Type;
1042 l_PSG_tbl QP_Attr_Map_PUB.Psg_Tbl_Type;
1043
1044 CURSOR l_PSG_csr IS
1045 SELECT ATTRIBUTE1
1046 , ATTRIBUTE10
1047 , ATTRIBUTE11
1048 , ATTRIBUTE12
1049 , ATTRIBUTE13
1050 , ATTRIBUTE14
1051 , ATTRIBUTE15
1052 , ATTRIBUTE2
1053 , ATTRIBUTE3
1054 , ATTRIBUTE4
1055 , ATTRIBUTE5
1056 , ATTRIBUTE6
1057 , ATTRIBUTE7
1058 , ATTRIBUTE8
1059 , ATTRIBUTE9
1060 , CONTEXT
1061 , CREATED_BY
1062 , CREATION_DATE
1063 , LAST_UPDATED_BY
1064 , LAST_UPDATE_DATE
1065 , LAST_UPDATE_LOGIN
1066 , LIMITS_ENABLED
1067 , LOV_ENABLED
1068 , PROGRAM_APPLICATION_ID
1069 , PROGRAM_ID
1070 , PROGRAM_UPDATE_DATE
1071 , PTE_CODE
1072 , SEEDED_SOURCING_METHOD
1073 , SEGMENT_ID
1074 , SEGMENT_LEVEL
1075 , SEGMENT_PTE_ID
1076 , SOURCING_ENABLED
1077 , SOURCING_STATUS
1078 , USER_SOURCING_METHOD
1079 FROM QP_PTE_SEGMENTS
1080 WHERE ( SEGMENT_PTE_ID = p_segment_pte_id
1081 )
1082 --**OR ( LOOKUP_CODE = p_lookup_code
1083 OR ( PTE_CODE = p_lookup_code
1084 );
1085
1086 BEGIN
1087
1088 IF
1089 (p_segment_pte_id IS NOT NULL
1090 AND
1091 p_segment_pte_id <> FND_API.G_MISS_NUM)
1092 AND
1093 (p_lookup_code IS NOT NULL
1094 AND
1095 p_lookup_code <> FND_API.G_MISS_CHAR)
1096 THEN
1097 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1098 THEN
1099 OE_MSG_PUB.Add_Exc_Msg
1100 ( G_PKG_NAME
1101 , 'Query Rows'
1102 , 'Keys are mutually exclusive: segment_pte_id = '|| p_segment_pte_id || ', lookup_code = '|| p_lookup_code
1103 );
1104 END IF;
1105
1106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1107
1108 END IF;
1109
1110
1111 -- Loop over fetched records
1112
1113 FOR l_implicit_rec IN l_PSG_csr LOOP
1114
1115 l_PSG_rec.attribute1 := l_implicit_rec.ATTRIBUTE1;
1116 l_PSG_rec.attribute10 := l_implicit_rec.ATTRIBUTE10;
1117 l_PSG_rec.attribute11 := l_implicit_rec.ATTRIBUTE11;
1118 l_PSG_rec.attribute12 := l_implicit_rec.ATTRIBUTE12;
1119 l_PSG_rec.attribute13 := l_implicit_rec.ATTRIBUTE13;
1120 l_PSG_rec.attribute14 := l_implicit_rec.ATTRIBUTE14;
1121 l_PSG_rec.attribute15 := l_implicit_rec.ATTRIBUTE15;
1122 l_PSG_rec.attribute2 := l_implicit_rec.ATTRIBUTE2;
1123 l_PSG_rec.attribute3 := l_implicit_rec.ATTRIBUTE3;
1124 l_PSG_rec.attribute4 := l_implicit_rec.ATTRIBUTE4;
1125 l_PSG_rec.attribute5 := l_implicit_rec.ATTRIBUTE5;
1126 l_PSG_rec.attribute6 := l_implicit_rec.ATTRIBUTE6;
1127 l_PSG_rec.attribute7 := l_implicit_rec.ATTRIBUTE7;
1128 l_PSG_rec.attribute8 := l_implicit_rec.ATTRIBUTE8;
1129 l_PSG_rec.attribute9 := l_implicit_rec.ATTRIBUTE9;
1130 l_PSG_rec.context := l_implicit_rec.CONTEXT;
1131 l_PSG_rec.created_by := l_implicit_rec.CREATED_BY;
1132 l_PSG_rec.creation_date := l_implicit_rec.CREATION_DATE;
1133 l_PSG_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
1134 l_PSG_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
1135 l_PSG_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
1136 l_PSG_rec.limits_enabled := l_implicit_rec.LIMITS_ENABLED;
1137 l_PSG_rec.lov_enabled := l_implicit_rec.LOV_ENABLED;
1138 l_PSG_rec.program_application_id := l_implicit_rec.PROGRAM_APPLICATION_ID;
1139 l_PSG_rec.program_id := l_implicit_rec.PROGRAM_ID;
1140 l_PSG_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
1141 l_PSG_rec.pte_code := l_implicit_rec.PTE_CODE;
1142 l_PSG_rec.seeded_sourcing_method := l_implicit_rec.SEEDED_SOURCING_METHOD;
1143 l_PSG_rec.segment_id := l_implicit_rec.SEGMENT_ID;
1144 l_PSG_rec.segment_level := l_implicit_rec.SEGMENT_LEVEL;
1145 l_PSG_rec.segment_pte_id := l_implicit_rec.SEGMENT_PTE_ID;
1146 l_PSG_rec.sourcing_enabled := l_implicit_rec.SOURCING_ENABLED;
1147 l_PSG_rec.sourcing_status := l_implicit_rec.SOURCING_STATUS;
1148 l_PSG_rec.user_sourcing_method := l_implicit_rec.USER_SOURCING_METHOD;
1149
1150 l_PSG_tbl(l_PSG_tbl.COUNT + 1) := l_PSG_rec;
1151
1152 END LOOP;
1153
1154
1155 -- PK sent and no rows found
1156
1157 IF
1158 (p_segment_pte_id IS NOT NULL
1159 AND
1160 p_segment_pte_id <> FND_API.G_MISS_NUM)
1161 AND
1162 (l_PSG_tbl.COUNT = 0)
1163 THEN
1164 RAISE NO_DATA_FOUND;
1165 END IF;
1166
1167
1168 -- Return fetched table
1169
1170 RETURN l_PSG_tbl;
1171
1172 EXCEPTION
1173
1174 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1175
1176 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1177
1178 WHEN OTHERS THEN
1179
1180 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1181 THEN
1182 OE_MSG_PUB.Add_Exc_Msg
1183 ( G_PKG_NAME
1184 , 'Query_Rows'
1185 );
1186 END IF;
1187
1188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1189
1190 END Query_Rows;
1191
1192 -- Procedure lock_Row
1193 --
1194
1195 PROCEDURE Lock_Row
1196 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
1197 , p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
1198 , x_PSG_rec OUT NOCOPY /* file.sql.39 change */ QP_Attr_Map_PUB.Psg_Rec_Type
1199 )
1200 IS
1201 l_PSG_rec QP_Attr_Map_PUB.Psg_Rec_Type;
1202 BEGIN
1203
1204 SELECT ATTRIBUTE1
1205 , ATTRIBUTE10
1206 , ATTRIBUTE11
1207 , ATTRIBUTE12
1208 , ATTRIBUTE13
1209 , ATTRIBUTE14
1210 , ATTRIBUTE15
1211 , ATTRIBUTE2
1212 , ATTRIBUTE3
1213 , ATTRIBUTE4
1214 , ATTRIBUTE5
1215 , ATTRIBUTE6
1216 , ATTRIBUTE7
1217 , ATTRIBUTE8
1218 , ATTRIBUTE9
1219 , CONTEXT
1220 , CREATED_BY
1221 , CREATION_DATE
1222 , LAST_UPDATED_BY
1223 , LAST_UPDATE_DATE
1224 , LAST_UPDATE_LOGIN
1225 , LIMITS_ENABLED
1226 , LOV_ENABLED
1227 , PROGRAM_APPLICATION_ID
1228 , PROGRAM_ID
1229 , PROGRAM_UPDATE_DATE
1230 , PTE_CODE
1231 , SEEDED_SOURCING_METHOD
1232 , SEGMENT_ID
1233 , SEGMENT_LEVEL
1234 , SEGMENT_PTE_ID
1235 , SOURCING_ENABLED
1236 , SOURCING_STATUS
1237 , USER_SOURCING_METHOD
1238 INTO l_PSG_rec.attribute1
1239 , l_PSG_rec.attribute10
1240 , l_PSG_rec.attribute11
1241 , l_PSG_rec.attribute12
1242 , l_PSG_rec.attribute13
1243 , l_PSG_rec.attribute14
1244 , l_PSG_rec.attribute15
1245 , l_PSG_rec.attribute2
1246 , l_PSG_rec.attribute3
1247 , l_PSG_rec.attribute4
1248 , l_PSG_rec.attribute5
1249 , l_PSG_rec.attribute6
1250 , l_PSG_rec.attribute7
1251 , l_PSG_rec.attribute8
1252 , l_PSG_rec.attribute9
1253 , l_PSG_rec.context
1254 , l_PSG_rec.created_by
1255 , l_PSG_rec.creation_date
1256 , l_PSG_rec.last_updated_by
1257 , l_PSG_rec.last_update_date
1258 , l_PSG_rec.last_update_login
1259 , l_PSG_rec.limits_enabled
1260 , l_PSG_rec.lov_enabled
1261 , l_PSG_rec.program_application_id
1262 , l_PSG_rec.program_id
1263 , l_PSG_rec.program_update_date
1264 , l_PSG_rec.pte_code
1265 , l_PSG_rec.seeded_sourcing_method
1266 , l_PSG_rec.segment_id
1267 , l_PSG_rec.segment_level
1268 , l_PSG_rec.segment_pte_id
1269 , l_PSG_rec.sourcing_enabled
1270 , l_PSG_rec.sourcing_status
1271 , l_PSG_rec.user_sourcing_method
1272 FROM QP_PTE_SEGMENTS
1273 WHERE SEGMENT_PTE_ID = p_PSG_rec.segment_pte_id
1274 FOR UPDATE NOWAIT;
1275
1276 -- Row locked. Compare IN attributes to DB attributes.
1277
1278 IF QP_GLOBALS.Equal(p_PSG_rec.attribute1,
1279 l_PSG_rec.attribute1)
1280 AND QP_GLOBALS.Equal(p_PSG_rec.attribute10,
1281 l_PSG_rec.attribute10)
1282 AND QP_GLOBALS.Equal(p_PSG_rec.attribute11,
1283 l_PSG_rec.attribute11)
1284 AND QP_GLOBALS.Equal(p_PSG_rec.attribute12,
1285 l_PSG_rec.attribute12)
1286 AND QP_GLOBALS.Equal(p_PSG_rec.attribute13,
1287 l_PSG_rec.attribute13)
1288 AND QP_GLOBALS.Equal(p_PSG_rec.attribute14,
1289 l_PSG_rec.attribute14)
1290 AND QP_GLOBALS.Equal(p_PSG_rec.attribute15,
1291 l_PSG_rec.attribute15)
1292 AND QP_GLOBALS.Equal(p_PSG_rec.attribute2,
1293 l_PSG_rec.attribute2)
1294 AND QP_GLOBALS.Equal(p_PSG_rec.attribute3,
1295 l_PSG_rec.attribute3)
1296 AND QP_GLOBALS.Equal(p_PSG_rec.attribute4,
1297 l_PSG_rec.attribute4)
1298 AND QP_GLOBALS.Equal(p_PSG_rec.attribute5,
1299 l_PSG_rec.attribute5)
1300 AND QP_GLOBALS.Equal(p_PSG_rec.attribute6,
1301 l_PSG_rec.attribute6)
1302 AND QP_GLOBALS.Equal(p_PSG_rec.attribute7,
1303 l_PSG_rec.attribute7)
1304 AND QP_GLOBALS.Equal(p_PSG_rec.attribute8,
1305 l_PSG_rec.attribute8)
1306 AND QP_GLOBALS.Equal(p_PSG_rec.attribute9,
1307 l_PSG_rec.attribute9)
1308 AND QP_GLOBALS.Equal(p_PSG_rec.context,
1309 l_PSG_rec.context)
1310 AND QP_GLOBALS.Equal(p_PSG_rec.created_by,
1311 l_PSG_rec.created_by)
1312 AND QP_GLOBALS.Equal(p_PSG_rec.creation_date,
1313 l_PSG_rec.creation_date)
1314 AND QP_GLOBALS.Equal(p_PSG_rec.last_updated_by,
1315 l_PSG_rec.last_updated_by)
1316 AND QP_GLOBALS.Equal(p_PSG_rec.last_update_date,
1317 l_PSG_rec.last_update_date)
1318 AND QP_GLOBALS.Equal(p_PSG_rec.last_update_login,
1319 l_PSG_rec.last_update_login)
1320 AND QP_GLOBALS.Equal(p_PSG_rec.limits_enabled,
1321 l_PSG_rec.limits_enabled)
1322 AND QP_GLOBALS.Equal(p_PSG_rec.lov_enabled,
1323 l_PSG_rec.lov_enabled)
1324 AND QP_GLOBALS.Equal(p_PSG_rec.program_application_id,
1325 l_PSG_rec.program_application_id)
1326 AND QP_GLOBALS.Equal(p_PSG_rec.program_id,
1327 l_PSG_rec.program_id)
1328 AND QP_GLOBALS.Equal(p_PSG_rec.program_update_date,
1329 l_PSG_rec.program_update_date)
1330 AND QP_GLOBALS.Equal(p_PSG_rec.pte_code,
1331 l_PSG_rec.pte_code)
1332 AND QP_GLOBALS.Equal(p_PSG_rec.seeded_sourcing_method,
1333 l_PSG_rec.seeded_sourcing_method)
1334 AND QP_GLOBALS.Equal(p_PSG_rec.segment_id,
1335 l_PSG_rec.segment_id)
1336 AND QP_GLOBALS.Equal(p_PSG_rec.segment_level,
1337 l_PSG_rec.segment_level)
1338 AND QP_GLOBALS.Equal(p_PSG_rec.segment_pte_id,
1339 l_PSG_rec.segment_pte_id)
1340 AND QP_GLOBALS.Equal(p_PSG_rec.sourcing_enabled,
1341 l_PSG_rec.sourcing_enabled)
1342 AND QP_GLOBALS.Equal(p_PSG_rec.sourcing_status,
1343 l_PSG_rec.sourcing_status)
1344 AND QP_GLOBALS.Equal(p_PSG_rec.user_sourcing_method,
1345 l_PSG_rec.user_sourcing_method)
1346 THEN
1347
1348 -- Row has not changed. Set out parameter.
1349
1350 x_PSG_rec := l_PSG_rec;
1351
1352 -- Set return status
1353
1354 x_return_status := FND_API.G_RET_STS_SUCCESS;
1355 x_PSG_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1356
1357 ELSE
1358
1359 -- Row has changed by another user.
1360
1361 x_return_status := FND_API.G_RET_STS_ERROR;
1362 x_PSG_rec.return_status := FND_API.G_RET_STS_ERROR;
1363
1364 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1365 THEN
1366
1367 FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_CHANGED');
1368 OE_MSG_PUB.Add;
1369
1370 END IF;
1371
1372 END IF;
1373
1374 EXCEPTION
1375
1376 WHEN NO_DATA_FOUND THEN
1377
1378 x_return_status := FND_API.G_RET_STS_ERROR;
1379 x_PSG_rec.return_status := FND_API.G_RET_STS_ERROR;
1380
1381 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1382 THEN
1383
1384 FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_DELETED');
1385 OE_MSG_PUB.Add;
1386
1387 END IF;
1388 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1389
1390 x_return_status := FND_API.G_RET_STS_ERROR;
1391 x_PSG_rec.return_status := FND_API.G_RET_STS_ERROR;
1392
1393 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
1394 THEN
1395
1396 FND_MESSAGE.SET_NAME('QP','OE_LOCK_ROW_ALREADY_LOCKED');
1397 OE_MSG_PUB.Add;
1398
1399 END IF;
1400 WHEN OTHERS THEN
1401
1402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1403 x_PSG_rec.return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404
1405 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1406 THEN
1407 OE_MSG_PUB.Add_Exc_Msg
1408 ( G_PKG_NAME
1409 , 'Lock_Row'
1410 );
1411 END IF;
1412
1413 END Lock_Row;
1414
1415 -- Function Get_Values
1416
1417 FUNCTION Get_Values
1418 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
1419 , p_old_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type :=
1420 QP_Attr_Map_PUB.G_MISS_PSG_REC
1421 ) RETURN QP_Attr_Map_PUB.Psg_Val_Rec_Type
1422 IS
1423 l_PSG_val_rec QP_Attr_Map_PUB.Psg_Val_Rec_Type;
1424 BEGIN
1425
1426 IF p_PSG_rec.pte_code IS NOT NULL AND
1427 p_PSG_rec.pte_code <> FND_API.G_MISS_CHAR AND
1428 NOT QP_GLOBALS.Equal(p_PSG_rec.pte_code,
1429 p_old_PSG_rec.pte_code)
1430 THEN
1431 l_PSG_val_rec.pte := QP_Id_To_Value.Pte
1432 ( p_pte_code => p_PSG_rec.pte_code
1433 );
1434 END IF;
1435
1436 IF p_PSG_rec.segment_id IS NOT NULL AND
1437 p_PSG_rec.segment_id <> FND_API.G_MISS_NUM AND
1438 NOT QP_GLOBALS.Equal(p_PSG_rec.segment_id,
1439 p_old_PSG_rec.segment_id)
1440 THEN
1441 l_PSG_val_rec.segment := QP_Id_To_Value.Segment
1442 ( p_segment_id => p_PSG_rec.segment_id
1443 );
1444 END IF;
1445
1446 IF p_PSG_rec.segment_pte_id IS NOT NULL AND
1447 p_PSG_rec.segment_pte_id <> FND_API.G_MISS_NUM AND
1448 NOT QP_GLOBALS.Equal(p_PSG_rec.segment_pte_id,
1449 p_old_PSG_rec.segment_pte_id)
1450 THEN
1451 l_PSG_val_rec.segment_pte := QP_Id_To_Value.Segment_Pte
1452 ( p_segment_pte_id => p_PSG_rec.segment_pte_id
1453 );
1454 END IF;
1455
1456 RETURN l_PSG_val_rec;
1457
1458 END Get_Values;
1459
1460 -- Function Get_Ids
1461
1462 FUNCTION Get_Ids
1463 ( p_PSG_rec IN QP_Attr_Map_PUB.Psg_Rec_Type
1464 , p_PSG_val_rec IN QP_Attr_Map_PUB.Psg_Val_Rec_Type
1465 ) RETURN QP_Attr_Map_PUB.Psg_Rec_Type
1466 IS
1467 l_PSG_rec QP_Attr_Map_PUB.Psg_Rec_Type;
1468 BEGIN
1469
1470 -- initialize return_status.
1471
1472 l_PSG_rec.return_status := FND_API.G_RET_STS_SUCCESS;
1473
1474 -- initialize l_PSG_rec.
1475
1476 l_PSG_rec := p_PSG_rec;
1477
1478 IF p_PSG_val_rec.pte <> FND_API.G_MISS_CHAR
1479 THEN
1480
1481 IF p_PSG_rec.pte_code <> FND_API.G_MISS_CHAR THEN
1482
1483 l_PSG_rec.pte_code := p_PSG_rec.pte_code;
1484
1485 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1486 THEN
1487
1488 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
1489 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','pte');
1490 OE_MSG_PUB.Add;
1491
1492 END IF;
1493
1494 ELSE
1495
1496 l_PSG_rec.pte_code := QP_Value_To_Id.pte
1497 ( p_pte => p_PSG_val_rec.pte
1498 );
1499
1500 IF l_PSG_rec.pte_code = FND_API.G_MISS_CHAR THEN
1501 l_PSG_rec.return_status := FND_API.G_RET_STS_ERROR;
1502 END IF;
1503
1504 END IF;
1505
1506 END IF;
1507
1508 IF p_PSG_val_rec.segment <> FND_API.G_MISS_CHAR
1509 THEN
1510
1511 IF p_PSG_rec.segment_id <> FND_API.G_MISS_NUM THEN
1512
1513 l_PSG_rec.segment_id := p_PSG_rec.segment_id;
1514
1515 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1516 THEN
1517
1518 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
1519 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','segment');
1520 OE_MSG_PUB.Add;
1521
1522 END IF;
1523
1524 ELSE
1525
1526 --**l_PSG_rec.segment_id := QP_Value_To_Id.segment
1527 --**( p_segment => p_PSG_val_rec.segment
1528 --**);
1529 --** Added next line
1530 l_PSG_rec.segment_id := p_PSG_rec.segment_id;
1531
1532 IF l_PSG_rec.segment_id = FND_API.G_MISS_NUM THEN
1533 l_PSG_rec.return_status := FND_API.G_RET_STS_ERROR;
1534 END IF;
1535
1536 END IF;
1537
1538 END IF;
1539
1540 IF p_PSG_val_rec.segment_pte <> FND_API.G_MISS_CHAR
1541 THEN
1542
1543 IF p_PSG_rec.segment_pte_id <> FND_API.G_MISS_NUM THEN
1544
1545 l_PSG_rec.segment_pte_id := p_PSG_rec.segment_pte_id;
1546
1547 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_SUCCESS)
1548 THEN
1549
1550 FND_MESSAGE.SET_NAME('QP','FND_BOTH_VAL_AND_ID_EXIST');
1551 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','segment_pte');
1552 OE_MSG_PUB.Add;
1553
1554 END IF;
1555
1556 ELSE
1557
1558 l_PSG_rec.segment_pte_id := QP_Value_To_Id.segment_pte
1559 ( p_segment_pte => p_PSG_val_rec.segment_pte
1560 );
1561
1562 IF l_PSG_rec.segment_pte_id = FND_API.G_MISS_NUM THEN
1563 l_PSG_rec.return_status := FND_API.G_RET_STS_ERROR;
1564 END IF;
1565
1566 END IF;
1567
1568 END IF;
1569
1570
1571 RETURN l_PSG_rec;
1572
1573 END Get_Ids;
1574
1575 END QP_Psg_Util;