1 PACKAGE BODY QP_Validate_Ssc AS
2 /* $Header: QPXLSSCB.pls 120.1 2005/06/08 21:44:40 appldev $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'QP_Validate_Ssc';
7
8 -- Procedure Entity
9
10 PROCEDURE Entity
11 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
12 , p_SSC_rec IN QP_Attr_Map_PUB.Ssc_Rec_Type
13 , p_old_SSC_rec IN QP_Attr_Map_PUB.Ssc_Rec_Type :=
14 QP_Attr_Map_PUB.G_MISS_SSC_REC
15 )
16 IS
17 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
18 BEGIN
19
20 -- Check required attributes.
21
22 IF p_SSC_rec.pte_source_system_id IS NULL
23 THEN
24
25 l_return_status := FND_API.G_RET_STS_ERROR;
26
27 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_ERROR)
28 THEN
29
30 FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
31 FND_MESSAGE.SET_TOKEN('ATTRIBUTE','application_short_name');
32 OE_MSG_PUB.Add;
33
34 END IF;
35
36 END IF;
37
38 --
39 -- Check rest of required attributes here.
40 --
41
42
43 -- Return Error if a required attribute is missing.
44
45 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
46
47 RAISE FND_API.G_EXC_ERROR;
48
49 END IF;
50
51 --
52 -- Check conditionally required attributes here.
53 --
54
55
56 --
57 -- Validate attribute dependencies here.
58 --
59
60
61 -- Done validating entity
62
63 x_return_status := l_return_status;
64
65 EXCEPTION
66
67 WHEN FND_API.G_EXC_ERROR THEN
68
69 x_return_status := FND_API.G_RET_STS_ERROR;
70
71 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
72
73 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
74
75 WHEN OTHERS THEN
76
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78
79 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
80 THEN
81 OE_MSG_PUB.Add_Exc_Msg
82 ( G_PKG_NAME
83 , 'Entity'
84 );
85 END IF;
86
87 END Entity;
88
89 -- Procedure Attributes
90
91 PROCEDURE Attributes
92 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
93 , p_SSC_rec IN QP_Attr_Map_PUB.Ssc_Rec_Type
94 , p_old_SSC_rec IN QP_Attr_Map_PUB.Ssc_Rec_Type :=
95 QP_Attr_Map_PUB.G_MISS_SSC_REC
96 )
97 IS
98 BEGIN
99
100 x_return_status := FND_API.G_RET_STS_SUCCESS;
101
102 -- Validate SSC attributes
103
104 IF p_SSC_rec.application_short_name IS NOT NULL AND
105 ( p_SSC_rec.application_short_name <>
106 p_old_SSC_rec.application_short_name OR
107 p_old_SSC_rec.application_short_name IS NULL )
108 THEN
109 IF NOT QP_Validate.Application_Short_Name(p_SSC_rec.application_short_name) THEN
110 x_return_status := FND_API.G_RET_STS_ERROR;
111 END IF;
112 END IF;
113
114 IF p_SSC_rec.created_by IS NOT NULL AND
115 ( p_SSC_rec.created_by <>
116 p_old_SSC_rec.created_by OR
117 p_old_SSC_rec.created_by IS NULL )
118 THEN
119 IF NOT QP_Validate.Created_By(p_SSC_rec.created_by) THEN
120 x_return_status := FND_API.G_RET_STS_ERROR;
121 END IF;
122 END IF;
123
124 IF p_SSC_rec.creation_date IS NOT NULL AND
125 ( p_SSC_rec.creation_date <>
126 p_old_SSC_rec.creation_date OR
127 p_old_SSC_rec.creation_date IS NULL )
128 THEN
129 IF NOT QP_Validate.Creation_Date(p_SSC_rec.creation_date) THEN
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 END IF;
132 END IF;
133
134 IF p_SSC_rec.enabled_flag IS NOT NULL AND
135 ( p_SSC_rec.enabled_flag <>
136 p_old_SSC_rec.enabled_flag OR
137 p_old_SSC_rec.enabled_flag IS NULL )
138 THEN
139 IF NOT QP_Validate.Enabled(p_SSC_rec.enabled_flag) THEN
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 END IF;
142 END IF;
143
144 IF p_SSC_rec.last_updated_by IS NOT NULL AND
145 ( p_SSC_rec.last_updated_by <>
146 p_old_SSC_rec.last_updated_by OR
147 p_old_SSC_rec.last_updated_by IS NULL )
148 THEN
149 IF NOT QP_Validate.Last_Updated_By(p_SSC_rec.last_updated_by) THEN
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 END IF;
152 END IF;
153
154 IF p_SSC_rec.last_update_date IS NOT NULL AND
155 ( p_SSC_rec.last_update_date <>
156 p_old_SSC_rec.last_update_date OR
157 p_old_SSC_rec.last_update_date IS NULL )
158 THEN
159 IF NOT QP_Validate.Last_Update_Date(p_SSC_rec.last_update_date) THEN
160 x_return_status := FND_API.G_RET_STS_ERROR;
161 END IF;
162 END IF;
163
164 IF p_SSC_rec.last_update_login IS NOT NULL AND
165 ( p_SSC_rec.last_update_login <>
166 p_old_SSC_rec.last_update_login OR
167 p_old_SSC_rec.last_update_login IS NULL )
168 THEN
169 IF NOT QP_Validate.Last_Update_Login(p_SSC_rec.last_update_login) THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 END IF;
172 END IF;
173
174 IF p_SSC_rec.program_application_id IS NOT NULL AND
175 ( p_SSC_rec.program_application_id <>
176 p_old_SSC_rec.program_application_id OR
177 p_old_SSC_rec.program_application_id IS NULL )
178 THEN
179 IF NOT QP_Validate.Program_Application(p_SSC_rec.program_application_id) THEN
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 END IF;
182 END IF;
183
184 IF p_SSC_rec.program_id IS NOT NULL AND
185 ( p_SSC_rec.program_id <>
186 p_old_SSC_rec.program_id OR
187 p_old_SSC_rec.program_id IS NULL )
188 THEN
189 IF NOT QP_Validate.Program(p_SSC_rec.program_id) THEN
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 END IF;
192 END IF;
193
194 IF p_SSC_rec.program_update_date IS NOT NULL AND
195 ( p_SSC_rec.program_update_date <>
196 p_old_SSC_rec.program_update_date OR
197 p_old_SSC_rec.program_update_date IS NULL )
198 THEN
199 IF NOT QP_Validate.Program_Update_Date(p_SSC_rec.program_update_date) THEN
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 END IF;
202 END IF;
203
204 IF p_SSC_rec.pte_code IS NOT NULL AND
205 ( p_SSC_rec.pte_code <>
206 p_old_SSC_rec.pte_code OR
207 p_old_SSC_rec.pte_code IS NULL )
208 THEN
209 IF NOT QP_Validate.Pte(p_SSC_rec.pte_code) THEN
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 END IF;
212 END IF;
213
214 IF p_SSC_rec.pte_source_system_id IS NOT NULL AND
215 ( p_SSC_rec.pte_source_system_id <>
216 p_old_SSC_rec.pte_source_system_id OR
217 p_old_SSC_rec.pte_source_system_id IS NULL )
218 THEN
219 IF NOT QP_Validate.Pte_Source_System(p_SSC_rec.pte_source_system_id) THEN
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 END IF;
222 END IF;
223
224 IF (p_SSC_rec.attribute1 IS NOT NULL AND
225 ( p_SSC_rec.attribute1 <>
226 p_old_SSC_rec.attribute1 OR
227 p_old_SSC_rec.attribute1 IS NULL ))
228 OR (p_SSC_rec.attribute10 IS NOT NULL AND
229 ( p_SSC_rec.attribute10 <>
230 p_old_SSC_rec.attribute10 OR
231 p_old_SSC_rec.attribute10 IS NULL ))
232 OR (p_SSC_rec.attribute11 IS NOT NULL AND
233 ( p_SSC_rec.attribute11 <>
234 p_old_SSC_rec.attribute11 OR
235 p_old_SSC_rec.attribute11 IS NULL ))
236 OR (p_SSC_rec.attribute12 IS NOT NULL AND
237 ( p_SSC_rec.attribute12 <>
238 p_old_SSC_rec.attribute12 OR
239 p_old_SSC_rec.attribute12 IS NULL ))
240 OR (p_SSC_rec.attribute13 IS NOT NULL AND
241 ( p_SSC_rec.attribute13 <>
242 p_old_SSC_rec.attribute13 OR
243 p_old_SSC_rec.attribute13 IS NULL ))
244 OR (p_SSC_rec.attribute14 IS NOT NULL AND
245 ( p_SSC_rec.attribute14 <>
246 p_old_SSC_rec.attribute14 OR
247 p_old_SSC_rec.attribute14 IS NULL ))
248 OR (p_SSC_rec.attribute15 IS NOT NULL AND
249 ( p_SSC_rec.attribute15 <>
250 p_old_SSC_rec.attribute15 OR
251 p_old_SSC_rec.attribute15 IS NULL ))
252 OR (p_SSC_rec.attribute2 IS NOT NULL AND
253 ( p_SSC_rec.attribute2 <>
254 p_old_SSC_rec.attribute2 OR
255 p_old_SSC_rec.attribute2 IS NULL ))
256 OR (p_SSC_rec.attribute3 IS NOT NULL AND
257 ( p_SSC_rec.attribute3 <>
258 p_old_SSC_rec.attribute3 OR
259 p_old_SSC_rec.attribute3 IS NULL ))
260 OR (p_SSC_rec.attribute4 IS NOT NULL AND
261 ( p_SSC_rec.attribute4 <>
262 p_old_SSC_rec.attribute4 OR
263 p_old_SSC_rec.attribute4 IS NULL ))
264 OR (p_SSC_rec.attribute5 IS NOT NULL AND
265 ( p_SSC_rec.attribute5 <>
266 p_old_SSC_rec.attribute5 OR
267 p_old_SSC_rec.attribute5 IS NULL ))
268 OR (p_SSC_rec.attribute6 IS NOT NULL AND
269 ( p_SSC_rec.attribute6 <>
270 p_old_SSC_rec.attribute6 OR
271 p_old_SSC_rec.attribute6 IS NULL ))
272 OR (p_SSC_rec.attribute7 IS NOT NULL AND
273 ( p_SSC_rec.attribute7 <>
274 p_old_SSC_rec.attribute7 OR
275 p_old_SSC_rec.attribute7 IS NULL ))
276 OR (p_SSC_rec.attribute8 IS NOT NULL AND
277 ( p_SSC_rec.attribute8 <>
278 p_old_SSC_rec.attribute8 OR
279 p_old_SSC_rec.attribute8 IS NULL ))
280 OR (p_SSC_rec.attribute9 IS NOT NULL AND
281 ( p_SSC_rec.attribute9 <>
282 p_old_SSC_rec.attribute9 OR
283 p_old_SSC_rec.attribute9 IS NULL ))
284 OR (p_SSC_rec.context IS NOT NULL AND
285 ( p_SSC_rec.context <>
286 p_old_SSC_rec.context OR
287 p_old_SSC_rec.context IS NULL ))
288 THEN
289
290 -- These calls are temporarily commented out
291
292 /*
293 FND_FLEX_DESC_VAL.Set_Column_Value
294 ( column_name => 'ATTRIBUTE1'
295 , column_value => p_SSC_rec.attribute1
296 );
297 FND_FLEX_DESC_VAL.Set_Column_Value
298 ( column_name => 'ATTRIBUTE10'
299 , column_value => p_SSC_rec.attribute10
300 );
301 FND_FLEX_DESC_VAL.Set_Column_Value
302 ( column_name => 'ATTRIBUTE11'
303 , column_value => p_SSC_rec.attribute11
304 );
305 FND_FLEX_DESC_VAL.Set_Column_Value
306 ( column_name => 'ATTRIBUTE12'
307 , column_value => p_SSC_rec.attribute12
308 );
309 FND_FLEX_DESC_VAL.Set_Column_Value
310 ( column_name => 'ATTRIBUTE13'
311 , column_value => p_SSC_rec.attribute13
312 );
313 FND_FLEX_DESC_VAL.Set_Column_Value
314 ( column_name => 'ATTRIBUTE14'
315 , column_value => p_SSC_rec.attribute14
316 );
317 FND_FLEX_DESC_VAL.Set_Column_Value
318 ( column_name => 'ATTRIBUTE15'
319 , column_value => p_SSC_rec.attribute15
320 );
321 FND_FLEX_DESC_VAL.Set_Column_Value
322 ( column_name => 'ATTRIBUTE2'
323 , column_value => p_SSC_rec.attribute2
324 );
325 FND_FLEX_DESC_VAL.Set_Column_Value
326 ( column_name => 'ATTRIBUTE3'
327 , column_value => p_SSC_rec.attribute3
328 );
329 FND_FLEX_DESC_VAL.Set_Column_Value
330 ( column_name => 'ATTRIBUTE4'
331 , column_value => p_SSC_rec.attribute4
332 );
333 FND_FLEX_DESC_VAL.Set_Column_Value
334 ( column_name => 'ATTRIBUTE5'
335 , column_value => p_SSC_rec.attribute5
336 );
337 FND_FLEX_DESC_VAL.Set_Column_Value
338 ( column_name => 'ATTRIBUTE6'
339 , column_value => p_SSC_rec.attribute6
340 );
341 FND_FLEX_DESC_VAL.Set_Column_Value
342 ( column_name => 'ATTRIBUTE7'
343 , column_value => p_SSC_rec.attribute7
344 );
345 FND_FLEX_DESC_VAL.Set_Column_Value
346 ( column_name => 'ATTRIBUTE8'
347 , column_value => p_SSC_rec.attribute8
348 );
349 FND_FLEX_DESC_VAL.Set_Column_Value
350 ( column_name => 'ATTRIBUTE9'
351 , column_value => p_SSC_rec.attribute9
352 );
353 FND_FLEX_DESC_VAL.Set_Column_Value
354 ( column_name => 'CONTEXT'
355 , column_value => p_SSC_rec.context
356 );
357 */
358
359 -- Validate descriptive flexfield.
360
361 IF NOT QP_Validate.Desc_Flex( 'SSC' ) THEN
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 END IF;
364
365 END IF;
366
367 -- Done validating attributes
368
369 EXCEPTION
370
371 WHEN FND_API.G_EXC_ERROR THEN
372
373 x_return_status := FND_API.G_RET_STS_ERROR;
374
375 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
376
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378
379 WHEN OTHERS THEN
380
381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
382
383 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
384 THEN
385 OE_MSG_PUB.Add_Exc_Msg
386 ( G_PKG_NAME
387 , 'Attributes'
388 );
389 END IF;
390
391 END Attributes;
392
393 -- Procedure Entity_Delete
394
395 PROCEDURE Entity_Delete
396 ( x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
397 , p_SSC_rec IN QP_Attr_Map_PUB.Ssc_Rec_Type
398 )
399 IS
400 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
401 dummy varchar2(1);
402 BEGIN
403
404 -- Validate entity delete.
405 begin
406 select 'x'
407 into dummy
408 from qp_list_headers_b
409 where nvl(pte_code,'x') = nvl(p_SSC_rec.pte_code,'y') and
410 nvl(source_system_code,'x') = nvl(p_SSC_rec.application_short_name,'y') and
411 rownum = 1;
412 l_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MESSAGE.SET_NAME('QP','QP_CANNOT_DELETE_SOURCE');
414 OE_MSG_PUB.Add;
415 raise fnd_api.g_exc_error;
416 -- Done.
417 exception
418 when no_data_found then
419 null;
420 end;
421
422 x_return_status := l_return_status;
423
424 EXCEPTION
425
426 WHEN FND_API.G_EXC_ERROR THEN
427
428 x_return_status := FND_API.G_RET_STS_ERROR;
429
430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
431
432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
433
434 WHEN OTHERS THEN
435
436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
437
438 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
439 THEN
440 OE_MSG_PUB.Add_Exc_Msg
441 ( G_PKG_NAME
442 , 'Entity_Delete'
443 );
444 END IF;
445
446 END Entity_Delete;
447
448 END QP_Validate_Ssc;