[Home] [Help]
PACKAGE BODY: APPS.IBE_QUOTE_REL_OBJ_PVT
Source
1 PACKAGE BODY IBE_Quote_Rel_Obj_Pvt AS
2 /* $Header: IBEVQROB.pls 115.2 2002/12/21 06:44:25 ajlee ship $ */
3
4 l_true VARCHAR2(1) := FND_API.G_TRUE;
5
6 FUNCTION Get_Related_Obj_Tbl(
7 p_related_object_id IN NUMBER := FND_API.G_MISS_NUM ,
8 p_quote_object_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
9 p_quote_object_id IN NUMBER := FND_API.G_MISS_NUM ,
10 p_object_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
11 p_object_id IN NUMBER := FND_API.G_MISS_NUM ,
12 p_relationship_type_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
13 p_one_to_one IN VARCHAR2 := FND_API.G_FALSE ,
14 p_for_all_versions IN VARCHAR2 := FND_API.G_FALSE
15 )
16 RETURN ASO_Quote_Pub.Related_Obj_Tbl_Type
17 IS
18 TYPE Csr_Type IS REF CURSOR;
19 l_csr Csr_Type;
20 l_related_obj_rec ASO_Quote_Pub.Related_Obj_Rec_Type;
21 l_related_obj_tbl ASO_Quote_Pub.Related_Obj_Tbl_Type;
22 BEGIN
23 IF FND_API.To_Boolean(p_for_all_versions)
24 AND p_quote_object_type_code = 'HEADER' THEN
25 -- CREATE
26 IF p_related_object_id = FND_API.G_MISS_NUM THEN
27 l_related_obj_rec.quote_object_type_code := p_quote_object_type_code;
28 l_related_obj_rec.object_type_code := p_object_type_code;
29 l_related_obj_rec.object_id := p_object_id;
30 l_related_obj_rec.relationship_type_code := p_relationship_type_code;
31
32 IF FND_API.To_Boolean(p_one_to_one) THEN
33 OPEN l_csr FOR SELECT AQH1.quote_header_id
34 FROM aso_quote_headers AQH1,
35 aso_quote_headers AQH2
36 WHERE AQH1.quote_number = AQH2.quote_number
37 AND AQH2.quote_header_id = p_quote_object_id
38 AND NOT EXISTS (SELECT 1
39 FROM aso_quote_related_objects
40 WHERE quote_object_type_code = 'HEADER'
41 AND quote_object_id = AQH1.quote_header_id
42 AND relationship_type_code = p_relationship_type_code);
43 ELSE
44 OPEN l_csr FOR SELECT AQH1.quote_header_id
45 FROM aso_quote_headers AQH1,
46 aso_quote_headers AQH2
47 WHERE AQH1.quote_number = AQH2.quote_number
48 AND AQH2.quote_header_id = p_quote_object_id;
49 END IF;
50 LOOP
51 FETCH l_csr INTO l_related_obj_rec.quote_object_id;
52 EXIT WHEN l_csr%NOTFOUND;
53 l_related_obj_tbl(l_related_obj_tbl.COUNT + 1) := l_related_obj_rec;
54 END LOOP;
55
56 CLOSE l_csr;
57 ELSE
58 /* For delete.
59 */
60 OPEN l_csr FOR SELECT related_object_id
61 FROM aso_quote_related_objects
62 WHERE relationship_type_code = (SELECT relationship_type_code
63 FROM aso_quote_related_objects
64 WHERE related_object_id = p_related_object_id)
65 AND quote_object_id IN (SELECT quote_header_id
66 FROM aso_quote_headers
67 WHERE quote_number = (SELECT quote_number
68 FROM aso_quote_headers
69 WHERE quote_header_id = (SELECT quote_object_id
70 FROM aso_quote_related_objects
71 WHERE related_object_id = p_related_object_id)));
72 LOOP
73 FETCH l_csr INTO l_related_obj_rec.related_object_id;
74 EXIT WHEN l_csr%NOTFOUND;
75
76 l_related_obj_tbl(l_related_obj_tbl.COUNT + 1) := l_related_obj_rec;
77 END LOOP;
78
79 CLOSE l_csr;
80 END IF;
81 ELSE
82 IF p_related_object_id = FND_API.G_MISS_NUM THEN
83 l_related_obj_rec.quote_object_type_code := p_quote_object_type_code;
84 l_related_obj_rec.quote_object_id := p_quote_object_id;
85 l_related_obj_rec.object_type_code := p_object_type_code;
86 l_related_obj_rec.object_id := p_object_id;
87 l_related_obj_rec.relationship_type_code := p_relationship_type_code;
88 ELSE
89 l_related_obj_rec.related_object_id := p_related_object_id;
90 END IF;
91
92 l_related_obj_tbl(1) := l_related_obj_rec;
93 END IF;
94
95 RETURN l_related_obj_tbl;
96 END Get_Related_Obj_Tbl;
97
98
99 PROCEDURE Create_Relationship(
100 p_api_version IN NUMBER := 1.0 ,
101 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
102 p_commit IN VARCHAR2 := FND_API.G_FALSE,
103 x_return_status OUT NOCOPY VARCHAR2 ,
104 x_msg_count OUT NOCOPY NUMBER ,
105 x_msg_data OUT NOCOPY VARCHAR2 ,
106 p_quote_object_type_code IN VARCHAR2 ,
107 p_quote_object_id IN NUMBER ,
108 p_object_type_code IN VARCHAR2 ,
109 p_object_id IN NUMBER ,
110 p_relationship_type_code IN VARCHAR2 ,
111 p_one_to_one IN VARCHAR2 ,
112 p_for_all_versions IN VARCHAR2 ,
113 x_related_obj_id OUT NOCOPY NUMBER
114 )
115 IS
116 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Relationship';
117 L_API_VERSION CONSTANT NUMBER := 1.0;
118 l_related_obj_rec ASO_Quote_Pub.Related_Obj_Rec_Type;
119 l_related_obj_tbl ASO_Quote_Pub.Related_Obj_Tbl_Type;
120 l_related_object_id NUMBER;
121 i NUMBER;
122 BEGIN
123 -- Standard Start of API savepoint
124 SAVEPOINT Create_Relationship_Pvt;
125
126 -- Standard call to check for call compatibility.
127 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
128 p_api_version,
129 L_API_NAME ,
130 G_PKG_NAME )
131 THEN
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 END IF;
134
135 -- Initialize message list if p_init_msg_list is set to TRUE.
136 IF FND_API.To_Boolean(p_init_msg_list) THEN
137 FND_Msg_Pub.initialize;
138 END IF;
139
140 -- Initialize API return status to success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142 -- API body
143 IF FND_API.To_Boolean(p_one_to_one) THEN
144 SELECT COUNT(*)
145 INTO i
146 FROM aso_quote_related_objects
147 WHERE quote_object_type_code = p_quote_object_type_code
148 AND quote_object_id = p_quote_object_id
149 AND relationship_type_code = p_relationship_type_code;
150
151 IF i > 0 THEN
152 FND_MESSAGE.Set_Name('IBE', 'IBE_QT_DUP_QUOTE_REL');
153 FND_MSG_PUB.Add;
154 RAISE FND_API.G_EXC_ERROR;
155 END IF;
156 END IF;
157
158 l_related_obj_rec.quote_object_type_code := p_quote_object_type_code;
159 l_related_obj_rec.quote_object_id := p_quote_object_id;
160 l_related_obj_rec.object_type_code := p_object_type_code;
161 l_related_obj_rec.object_id := p_object_id;
162 l_related_obj_rec.relationship_type_code := p_relationship_type_code;
163
164 l_related_obj_tbl :=
165 Get_Related_Obj_Tbl(p_quote_object_type_code => p_quote_object_type_code,
166 p_quote_object_id => p_quote_object_id ,
167 p_object_type_code => p_object_type_code ,
168 p_object_id => p_object_id ,
169 p_relationship_type_code => p_relationship_type_code,
170 p_one_to_one => p_one_to_one,
171 p_for_all_versions => p_for_all_versions);
172
173 FOR i IN 1..l_related_obj_tbl.COUNT LOOP
174 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
175 IBE_UTIL.DEBUG('Calling ASO_Rltship_Pub.Create_Object_Relationship at'
176 || TO_CHAR(SYSDATE, 'MM/DD/YYYY:HH24:MI:SS'));
177 END IF;
178
179 ASO_Rltship_Pub.Create_Object_Relationship(
180 p_api_version_number => p_api_version ,
181 p_init_msg_list => FND_API.G_TRUE ,
182 p_commit => FND_API.G_FALSE ,
183 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
184 x_return_status => x_return_status ,
185 x_msg_count => x_msg_count ,
186 x_msg_data => x_msg_data ,
187 p_related_obj_rec => l_related_obj_tbl(i) ,
188 x_related_object_id => l_related_object_id);
189
190 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
191 IBE_UTIL.DEBUG('Returned from ASO_Rltship_Pub.Create_Object_Relationship at'
192 || TO_CHAR(SYSDATE, 'MM/DD/YYYY:HH24:MI:SS'));
193 END IF;
194 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
195 RAISE FND_API.G_EXC_ERROR;
196 END IF;
197
198 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200 END IF;
201
202 IF l_related_obj_tbl(i).quote_object_id = p_quote_object_id THEN
203 x_related_obj_id := l_related_object_id;
204 END IF;
205 END LOOP;
206 -- End of API body.
207
208 -- Standard check of p_commit.
209 IF FND_API.To_Boolean(p_commit) THEN
210 COMMIT WORK;
211 END IF;
212
213 -- Standard call to get message count and if count is 1, get message info.
214 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
215 p_count => x_msg_count ,
216 p_data => x_msg_data);
217 EXCEPTION
218 WHEN FND_API.G_EXC_ERROR THEN
219 ROLLBACK TO Create_Relationship_Pvt;
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
222 p_count => x_msg_count ,
223 p_data => x_msg_data);
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 ROLLBACK TO Create_Relationship_Pvt;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
228 p_count => x_msg_count ,
229 p_data => x_msg_data);
230 WHEN OTHERS THEN
231 ROLLBACK TO Create_Relationship_Pvt;
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233
234 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
235 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
236 L_API_NAME);
237 END IF;
238
239 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
240 p_count => x_msg_count ,
241 p_data => x_msg_data);
242 END Create_Relationship;
243
244
245 PROCEDURE Delete_Relationship(
246 p_api_version IN NUMBER := 1.0 ,
247 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
248 p_commit IN VARCHAR2 := FND_API.G_FALSE,
249 x_return_status OUT NOCOPY VARCHAR2 ,
250 x_msg_count OUT NOCOPY NUMBER ,
251 x_msg_data OUT NOCOPY VARCHAR2 ,
252 p_quote_object_type_code IN VARCHAR2 ,
253 p_quote_object_id IN NUMBER ,
254 p_object_type_code IN VARCHAR2 ,
255 p_object_id IN NUMBER ,
256 p_relationship_type_code IN VARCHAR2 ,
257 p_for_all_versions IN VARCHAR2
258 )
259 IS
260 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Relationship';
261 L_API_VERSION CONSTANT NUMBER := 1.0;
262
263 l_related_obj_rec ASO_Quote_Pub.Related_Obj_Rec_Type;
264 l_related_obj_tbl ASO_Quote_Pub.Related_Obj_Tbl_Type;
265 l_related_object_id NUMBER;
266 BEGIN
267 -- Standard Start of API savepoint
268 SAVEPOINT Delete_Relationship_Pvt;
269
270 -- Standard call to check for call compatibility.
271 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
272 p_api_version,
273 L_API_NAME ,
274 G_PKG_NAME )
275 THEN
276 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
277 END IF;
278
279 -- Initialize message list if p_init_msg_list is set to TRUE.
280 IF FND_API.To_Boolean(p_init_msg_list) THEN
281 FND_Msg_Pub.initialize;
282 END IF;
283
284 -- Initialize API return status to success
285 x_return_status := FND_API.G_RET_STS_SUCCESS;
286
287 -- API body
288 BEGIN
289 SELECT related_object_id
290 INTO l_related_obj_rec.related_object_id
291 FROM aso_quote_related_objects
292 WHERE quote_object_type_code = p_quote_object_type_code
293 AND quote_object_id = p_quote_object_id
294 AND object_type_code = p_object_type_code
295 AND object_id = p_object_id
296 AND relationship_type_code = p_relationship_type_code;
297 EXCEPTION
298 WHEN NO_DATA_FOUND THEN
299 FND_MESSAGE.Set_Name('IBE', 'IBE_QT_QUOTE_REL_NOT_FOUND');
300 FND_MSG_PUB.Add;
301 RAISE FND_API.G_EXC_ERROR;
302 END;
303
304 l_related_obj_tbl :=
305 Get_Related_Obj_Tbl(p_related_object_id => l_related_obj_rec.related_object_id,
306 p_quote_object_type_code => p_quote_object_type_code,
307 p_for_all_versions => p_for_all_versions);
308
309 FOR i IN 1..l_related_obj_tbl.COUNT LOOP
310 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
311 IBE_UTIL.DEBUG('Calling ASO_Rltship_Pub.Delete_Object_Relationship at'
312 || TO_CHAR(SYSDATE, 'MM/DD/RRRR:HH24:MI:SS'));
313 END IF;
314
315 ASO_Rltship_Pub.Delete_Object_Relationship(
316 p_api_version_number => p_api_version ,
317 p_init_msg_list => FND_API.G_TRUE ,
318 p_commit => FND_API.G_FALSE ,
319 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
320 x_return_status => x_return_status ,
321 x_msg_count => x_msg_count ,
322 x_msg_data => x_msg_data ,
323 p_related_obj_rec => l_related_obj_tbl(i));
324
325 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
326 RAISE FND_API.G_EXC_ERROR;
327 END IF;
328
329 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
333 IBE_UTIL.DEBUG('Returned from ASO_Rltship_Pub.Delete_Object_Relationship at'
334 || TO_CHAR(SYSDATE, 'MM/DD/RRRR:HH24:MI:SS'));
335 END IF;
336
337 END LOOP;
338 -- End of API body.
339
340 -- Standard check of p_commit.
341 IF FND_API.To_Boolean(p_commit) THEN
342 COMMIT WORK;
343 END IF;
344
345 -- Standard call to get message count and if count is 1, get message info.
346 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
347 p_count => x_msg_count ,
348 p_data => x_msg_data);
349 EXCEPTION
350 WHEN FND_API.G_EXC_ERROR THEN
351 ROLLBACK TO Delete_Relationship_Pvt;
352 x_return_status := FND_API.G_RET_STS_ERROR;
353 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
354 p_count => x_msg_count ,
355 p_data => x_msg_data);
356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
357 ROLLBACK TO Delete_Relationship_Pvt;
358 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
359 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
360 p_count => x_msg_count ,
361 p_data => x_msg_data);
362 WHEN OTHERS THEN
363 ROLLBACK TO Delete_Relationship_Pvt;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365
366 IF FND_Msg_Pub.Check_Msg_Level( FND_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
367 FND_Msg_Pub.Add_Exc_Msg(G_PKG_NAME,
368 L_API_NAME);
369 END IF;
370
371 FND_Msg_Pub.Count_And_Get(p_encoded => FND_API.G_FALSE,
372 p_count => x_msg_count ,
373 p_data => x_msg_data);
374 END Delete_Relationship;
375
376 END IBE_Quote_Rel_Obj_Pvt;