1 PACKAGE BODY ICX_Related_Templates_PUB AS
2 /* $Header: ICXPTMPB.pls 115.1 99/07/17 03:21:03 porting ship $ */
3
4
5 PROCEDURE Insert_Relation
6 ( p_api_version_number IN NUMBER ,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
8 p_simulate IN VARCHAR2 := FND_API.G_FALSE ,
9 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
10 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
11 p_return_status OUT VARCHAR2 ,
12 p_msg_count OUT NUMBER ,
13 p_msg_data OUT VARCHAR2 ,
14 p_template IN VARCHAR2 DEFAULT NULL ,
15 p_related_template IN VARCHAR2 DEFAULT NULL ,
16 p_relationship_type IN VARCHAR2 ,
17 p_created_by IN NUMBER
18 ) IS
19
20
21 l_api_version_number CONSTANT NUMBER := 1.0;
22 l_validation_error BOOLEAN := FALSE;
23 l_title varchar2(80);
24 l_prompts icx_util.g_prompts_table;
25 l_count NUMBER;
26
27 BEGIN
28
29 -- Standard Start of API savepoint
30
31 SAVEPOINT Insert_Relation_PUB;
32
33
34 -- Standard call to check for call compatibility
35
36 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
37 p_api_version_number,
38 'Insert_Relation',
39 G_PKG_NAME)
40 THEN
41 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
42 END IF;
43
44
45 -- Initialize message list if p_init_msg_list is set to TRUE
46
47 IF FND_API.to_Boolean(p_init_msg_list) THEN
48 FND_MSG_PUB.initialize;
49 END IF;
50
51
52 -- Initialize p_return_status
53 p_return_status := FND_API.G_RET_STS_SUCCESS;
54
55
56 -- Get prompts table for translation of messages
57 icx_util.getPrompts(601,'ICX_RELATED_TEMPLATES_R',l_title,l_prompts);
58
59
60 -- Perform manditory validation
61
62 -- check that necessary in parameters are present
63 if (p_template is null or
64 p_related_template is null) then
65
66 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
67 -- add message: Required API parameters are missing
68 FND_MESSAGE.SET_NAME('ICX','ICX_API_MISS_PARAM');
69 FND_MSG_PUB.Add;
70 end if;
71 RAISE FND_API.G_EXC_ERROR;
72 end if;
73
74
75
76
77 -- Perform validation
78 IF p_validation_level = FND_API.G_VALID_LEVEL_FULL THEN
79
80 -- check template
81 select count(*) into l_count
82 from po_reqexpress_headers
83 where express_name = p_template;
84
85 if l_count <> 1 then
86 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
87 -- add message: Category Set ID is invalid
88 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
89 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',l_prompts(1));
90 FND_MSG_PUB.Add;
91 end if;
92 l_validation_error := TRUE;
93 end if; -- check template
94
95
96 -- check related template
97 select count(*) into l_count
98 from po_reqexpress_headers
99 where express_name = p_related_template;
100
101 if l_count <> 1 then
102 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
103 -- add message: Category ID is invalid
104 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
105 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',l_prompts(2));
106 FND_MSG_PUB.Add;
107 end if;
108 l_validation_error := TRUE;
109 end if; -- check related template
110
111
112 -- check that template and related template are not the same
113 if (p_relationship_type <> 'TOP' and
114 p_template = p_related_template) then
115 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
116 -- add message: Related Template may not be the same as
117 -- its parent template
118 FND_MESSAGE.SET_NAME('ICX','ICX_TMP_PARENT');
119 FND_MSG_PUB.Add;
120 end if;
121 l_validation_error := TRUE;
122 end if;
123
124
125 -- check that top relationship does not already exist if needed
126 if p_relationship_type = 'TOP' then
127 select count(*) into l_count
128 from po_related_templates
129 where express_name = p_template
130 and related_express_name = p_related_template;
131
132 if l_count > 0 then
133 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
134 -- add message: TEMPLATE is already a top template
135 FND_MESSAGE.SET_NAME('ICX','ICX_TMP_TOP');
136 FND_MESSAGE.SET_TOKEN('TEMPLATE',p_template);
137 FND_MSG_PUB.Add;
138 end if;
139 l_validation_error := TRUE;
140 end if;
141 end if;
142
143
144 -- check that relationship does not already exist
145 if p_template <> p_related_template then
146 select count(*) into l_count
147 from po_related_templates
148 where express_name = p_template
149 and related_express_name = p_related_template;
150
151 if l_count > 0 then
152 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
153 -- add message: This template relationship already exists
154 FND_MESSAGE.SET_NAME('ICX','ICX_TMP_DUP_RELATION');
155 FND_MSG_PUB.Add;
156 end if;
157 l_validation_error := TRUE;
158 end if;
159 end if;
160
161
162 -- check that relationship type is valid
163 select count(*) into l_count
164 from fnd_lookups
165 where lookup_type = 'ICX_RELATIONS'
166 and enabled_flag = 'Y'
167 and lookup_code = p_relationship_type;
168
169 if l_count <> 1 then
170 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
171 -- add message: Relation is not valid
172 FND_MESSAGE.SET_NAME('ICX','ICX_INVALID_ENTRY');
173 FND_MESSAGE.SET_TOKEN('INVALID_TOKEN',l_prompts(3));
174 FND_MSG_PUB.Add;
175 end if;
176 l_validation_error := TRUE;
177 end if;
178
179 END IF; -- Validation
180
181
182 -- If any validation failed, raise error
183 IF l_validation_error THEN
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186
187
188
189 -- API body
190
191 insert into po_related_templates
192 (express_name,
193 related_express_name,
194 relationship_type,
195 created_by,
196 creation_date,
197 last_updated_by,
198 last_update_date)
199 values
200 (p_template,
201 p_related_template,
202 p_relationship_type,
203 p_created_by,
204 sysdate,
205 p_created_by,
206 sysdate);
207
208
209 -- End of API body
210
211
212 -- Standard check of p_simulate and p_commit parameter
213
214 IF FND_API.To_Boolean(p_simulate) THEN
215
216 ROLLBACK TO Insert_Relation_PUB;
217
218 ELSIF FND_API.To_Boolean(p_commit) THEN
219
220 COMMIT WORK;
221
222 END IF;
223
224
225 -- Get message count and if 1, return message data
226
227 FND_MSG_PUB.Count_And_Get
228 (p_count => p_msg_count,
229 p_data => p_msg_data
230 );
231
232
233 EXCEPTION
234
235 WHEN FND_API.G_EXC_ERROR THEN
236
237 p_return_status := FND_API.G_RET_STS_ERROR;
238
239 -- Get message count and if 1, return message data
240
241 FND_MSG_PUB.Count_And_Get
242 (p_count => p_msg_count,
243 p_data => p_msg_data
244 );
245
246 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
247
248 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
249
250 -- Get message count and if 1, return message data
251
252 FND_MSG_PUB.Count_And_Get
253 (p_count => p_msg_count,
254 p_data => p_msg_data
255 );
256
257 WHEN OTHERS THEN
258
259 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260
261 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
262 FND_MSG_PUB.Build_Exc_Msg
263 ( G_PKG_NAME ,
264 'Insert_Relation'
265 );
266 END IF;
267
268 -- Get message count and if 1, return message data
269
270 FND_MSG_PUB.Count_And_Get
271 (p_count => p_msg_count,
272 p_data => p_msg_data
273 );
274
275
276 END; -- Insert_Relation
277
278
279
280
281 PROCEDURE Delete_Relation
282 ( p_api_version_number IN NUMBER ,
283 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
284 p_simulate IN VARCHAR2 := FND_API.G_FALSE ,
285 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
286 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
287 p_return_status OUT VARCHAR2 ,
288 p_msg_count OUT NUMBER ,
289 p_msg_data OUT VARCHAR2 ,
290 p_template IN VARCHAR2 DEFAULT NULL ,
291 p_related_template IN VARCHAR2 DEFAULT NULL
292 ) IS
293
294
295 l_api_version_number CONSTANT NUMBER := 1.0;
296 l_validation_error BOOLEAN := FALSE;
297 l_id_resolve_error BOOLEAN := FALSE;
298 l_title varchar2(80);
299 l_prompts icx_util.g_prompts_table;
300 l_count NUMBER;
301
302 BEGIN
303
304 -- Standard Start of API savepoint
305
306 SAVEPOINT Delete_Relation_PUB;
307
308
309 -- Standard call to check for call compatibility
310
311 IF NOT FND_API.Compatible_API_Call(l_api_version_number,
312 p_api_version_number,
313 'Delete Relation',
314 G_PKG_NAME)
315 THEN
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 END IF;
318
319
320 -- Initialize message list if p_init_msg_list is set to TRUE
321
322 IF FND_API.to_Boolean(p_init_msg_list) THEN
323 FND_MSG_PUB.initialize;
324 END IF;
325
326
327 -- Initialize p_return_status
328
329 p_return_status := FND_API.G_RET_STS_SUCCESS;
330
331
332 -- Get prompts table for translation of messages
333
334 icx_util.getPrompts(601,'ICX_RELATED_TEMPLATES_R',l_title,l_prompts);
335
336
337 -- Perform manditory validation
338
339 -- check that necessary in parameters are present
340 if (p_template is null or
341 p_related_template is null) then
342
343 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
344 -- add message: Required API parameters are missing
345 FND_MESSAGE.SET_NAME('ICX','ICX_API_MISS_PARAM');
346 FND_MSG_PUB.Add;
347 end if;
348 RAISE FND_API.G_EXC_ERROR;
349 end if;
350
351
352
353
354 -- API body
355
356 delete from po_related_templates
357 where express_name = p_template
358 and related_express_name = p_related_template;
359
360 -- End of API body
361
362
363
364 -- Standard check of p_simulate and p_commit parameter
365
366 IF FND_API.To_Boolean(p_simulate) THEN
367
368 ROLLBACK TO Insert_Relation_PUB;
369
370 ELSIF FND_API.To_Boolean(p_commit) THEN
371
372 COMMIT WORK;
373
374 END IF;
375
376
377 -- Get message count and if 1, return message data
378
379 FND_MSG_PUB.Count_And_Get
380 (p_count => p_msg_count,
381 p_data => p_msg_data
382 );
383
384
385 EXCEPTION
386
387 WHEN NO_DATA_FOUND THEN
388
389 p_return_status := FND_API.G_RET_STS_ERROR;
390
391 if FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) then
392 -- add message: Relation to delete does not exist
393 FND_MESSAGE.SET_NAME('ICX','ICX_CAT_DELETE');
394 FND_MSG_PUB.Add;
395 end if;
396
397 -- Get message count and if 1, return message data
398
399 FND_MSG_PUB.Count_And_Get
400 (p_count => p_msg_count,
401 p_data => p_msg_data
402 );
403
404 WHEN FND_API.G_EXC_ERROR THEN
405
406 p_return_status := FND_API.G_RET_STS_ERROR;
407
408 -- Get message count and if 1, return message data
409
410 FND_MSG_PUB.Count_And_Get
411 (p_count => p_msg_count,
412 p_data => p_msg_data
413 );
414
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416
417 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418
419 -- Get message count and if 1, return message data
420
421 FND_MSG_PUB.Count_And_Get
422 (p_count => p_msg_count,
423 p_data => p_msg_data
424 );
425
426 WHEN OTHERS THEN
427
428 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
429
430 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
431 FND_MSG_PUB.Build_Exc_Msg
432 ( G_PKG_NAME ,
433 'Delete_Relation'
434 );
435 END IF;
436
437 -- Get message count and if 1, return message data
438
439 FND_MSG_PUB.Count_And_Get
440 (p_count => p_msg_count,
441 p_data => p_msg_data
442 );
443
444
445 END; -- Delete_Relation
446
447
448
449 END ICX_Related_Templates_PUB;
[Home] [Help]