1 PACKAGE BODY AHL_UC_UNITCONFIG_PVT AS
2 /* $Header: AHLVUCXB.pls 120.3.12010000.2 2008/11/28 07:05:57 sathapli ship $ */
3
4 -- Define global internal variables
5 G_PKG_NAME VARCHAR2(30) := 'AHL_UC_UNITCONFIG_PVT';
6
7 CURSOR get_uc_header(c_uc_header_id number) IS
8 SELECT uc_header_id,
9 object_version_number,
10 uc_name,
11 uc_status_code,
12 active_uc_status_code,
13 csi_instance_id,
14 instance_number,
15 active_start_date,
16 active_end_date,
17 parent_uc_header_id,
18 mc_header_id,
19 mc_name,
20 mc_revision,
21 attribute_category,
22 attribute1,
23 attribute2,
24 attribute3,
25 attribute4,
26 attribute5,
27 attribute6,
28 attribute7,
29 attribute8,
30 attribute9,
31 attribute10,
32 attribute11,
33 attribute12,
34 attribute13,
35 attribute14,
36 attribute15
37 FROM ahl_unit_config_headers_v
38 WHERE uc_header_id = c_uc_header_id;
39
40 -- Procedure to clear the LOV attributes' ID to NULL or G_MISS Values if its
41 -- corresponding displayed field is NULL or G_MISS values
42 PROCEDURE clear_lov_attribute_ids(
43 p_x_uc_header_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type)
44 IS
45 BEGIN
46 IF (p_x_uc_header_rec.instance_number IS NULL) THEN
47 p_x_uc_header_rec.instance_id := NULL;
48 ELSIF (p_x_uc_header_rec.instance_number = FND_API.G_MISS_CHAR) THEN
49 p_x_uc_header_rec.instance_id := FND_API.G_MISS_NUM;
50 END IF;
51 IF (p_x_uc_header_rec.mc_name IS NULL AND
52 p_x_uc_header_rec.mc_revision IS NULL) THEN
53 p_x_uc_header_rec.mc_header_id := NULL;
54 ELSIF (p_x_uc_header_rec.mc_name = FND_API.G_MISS_CHAR AND
55 p_x_uc_header_rec.mc_revision = FND_API.G_MISS_CHAR) THEN
56 p_x_uc_header_rec.mc_header_id := FND_API.G_MISS_NUM;
57 END IF;
58 END clear_lov_attribute_ids;
59
60 -- Procedure to change G_MISS values to NULL in case they are passed during creation
61 PROCEDURE nullify_attributes(
62 p_x_uc_header_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type)
63 IS
64 BEGIN
65 IF (p_x_uc_header_rec.active_uc_status_code = FND_API.G_MISS_CHAR) THEN
66 p_x_uc_header_rec.active_uc_status_code := null;
67 END IF;
68 IF (p_x_uc_header_rec.mc_header_id = FND_API.G_MISS_NUM) THEN
69 p_x_uc_header_rec.mc_header_id := null;
70 END IF;
71 IF (p_x_uc_header_rec.mc_name = FND_API.G_MISS_CHAR) THEN
72 p_x_uc_header_rec.mc_name := null;
73 END IF;
74 IF (p_x_uc_header_rec.mc_revision = FND_API.G_MISS_CHAR) THEN
75 p_x_uc_header_rec.mc_revision := null;
76 END IF;
77 IF (p_x_uc_header_rec.parent_uc_header_id = FND_API.G_MISS_NUM) THEN
78 p_x_uc_header_rec.parent_uc_header_id := null;
79 END IF;
80 IF (p_x_uc_header_rec.active_end_date = FND_API.G_MISS_DATE) THEN
81 p_x_uc_header_rec.active_end_date := null;
82 END IF;
83 IF (p_x_uc_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
84 p_x_uc_header_rec.attribute_category := null;
85 END IF;
86 IF (p_x_uc_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
87 p_x_uc_header_rec.attribute1 := null;
88 END IF;
89 IF (p_x_uc_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
90 p_x_uc_header_rec.attribute2 := null;
91 END IF;
92 IF (p_x_uc_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
93 p_x_uc_header_rec.attribute3 := null;
94 END IF;
95 IF (p_x_uc_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
96 p_x_uc_header_rec.attribute4 := null;
97 END IF;
98 IF (p_x_uc_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
99 p_x_uc_header_rec.attribute5 := null;
100 END IF;
101 IF (p_x_uc_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
102 p_x_uc_header_rec.attribute6 := null;
103 END IF;
104 IF (p_x_uc_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
105 p_x_uc_header_rec.attribute7 := null;
106 END IF;
107 IF (p_x_uc_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
108 p_x_uc_header_rec.attribute8 := null;
109 END IF;
110 IF (p_x_uc_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
111 p_x_uc_header_rec.attribute9 := null;
112 END IF;
113 IF (p_x_uc_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
114 p_x_uc_header_rec.attribute10 := null;
115 END IF;
116 IF (p_x_uc_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
117 p_x_uc_header_rec.attribute11 := null;
118 END IF;
119 IF (p_x_uc_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
120 p_x_uc_header_rec.attribute12 := null;
121 END IF;
122 IF (p_x_uc_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
123 p_x_uc_header_rec.attribute13 := null;
124 END IF;
125 IF (p_x_uc_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
126 p_x_uc_header_rec.attribute14 := null;
127 END IF;
128 IF (p_x_uc_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
129 p_x_uc_header_rec.attribute15 := null;
130 END IF;
131 END nullify_attributes;
132
133 --Procedure to convert G_MISS values to NULL and NULL to OLD values during updating
134
135 PROCEDURE convert_attributes(
136 p_x_uc_header_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type,
137 x_return_status OUT NOCOPY VARCHAR2)
138 IS
139 l_old_uc_header_rec get_uc_header%ROWTYPE;
140 BEGIN
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142 OPEN get_uc_header( p_x_uc_header_rec.uc_header_id );
143 FETCH get_uc_header INTO l_old_uc_header_rec;
144 IF ( get_uc_header%NOTFOUND ) THEN
145 FND_MESSAGE.set_name('AHL', 'AHL_UC_HEADER_ID_INVALID');
146 FND_MESSAGE.set_token('UC_HEADER_ID', p_x_uc_header_rec.uc_header_id);
147 FND_MSG_PUB.add;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 END IF;
150 IF ( l_old_uc_header_rec.object_version_number <>
151 p_x_uc_header_rec.object_version_number ) THEN
152 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
153 FND_MSG_PUB.add;
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 END IF;
156 CLOSE get_uc_header;
157
158 IF (p_x_uc_header_rec.uc_name IS NULL) THEN
159 p_x_uc_header_rec.uc_name := l_old_uc_header_rec.uc_name;
160 ELSIF (p_x_uc_header_rec.uc_name = FND_API.G_MISS_CHAR) THEN
161 p_x_uc_header_rec.uc_name := null;
162 END IF;
163 IF (p_x_uc_header_rec.mc_header_id IS NULL) THEN
164 p_x_uc_header_rec.mc_header_id := l_old_uc_header_rec.mc_header_id;
165 ELSIF (p_x_uc_header_rec.mc_header_id = FND_API.G_MISS_NUM) THEN
166 p_x_uc_header_rec.mc_header_id := null;
167 END IF;
168 IF (p_x_uc_header_rec.mc_name IS NULL) THEN
169 p_x_uc_header_rec.mc_name := l_old_uc_header_rec.mc_name;
170 ELSIF (p_x_uc_header_rec.mc_name = FND_API.G_MISS_CHAR) THEN
171 p_x_uc_header_rec.mc_name := null;
172 END IF;
173 IF (p_x_uc_header_rec.mc_revision IS NULL) THEN
174 p_x_uc_header_rec.mc_revision := l_old_uc_header_rec.mc_revision;
175 ELSIF (p_x_uc_header_rec.mc_revision = FND_API.G_MISS_CHAR) THEN
176 p_x_uc_header_rec.mc_revision := null;
177 END IF;
178 IF (p_x_uc_header_rec.instance_id IS NULL) THEN
179 p_x_uc_header_rec.instance_id := l_old_uc_header_rec.csi_instance_id;
180 ELSIF (p_x_uc_header_rec.instance_id = FND_API.G_MISS_NUM) THEN
181 p_x_uc_header_rec.instance_id := null;
182 END IF;
183 IF (p_x_uc_header_rec.instance_number IS NULL) THEN
184 p_x_uc_header_rec.instance_number := l_old_uc_header_rec.instance_number;
185 ELSIF (p_x_uc_header_rec.instance_number = FND_API.G_MISS_CHAR) THEN
186 p_x_uc_header_rec.instance_number := null;
187 END IF;
188 IF (p_x_uc_header_rec.unit_config_status_code IS NULL) THEN
189 p_x_uc_header_rec.unit_config_status_code := l_old_uc_header_rec.uc_status_code;
190 ELSIF (p_x_uc_header_rec.unit_config_status_code = FND_API.G_MISS_CHAR) THEN
191 p_x_uc_header_rec.unit_config_status_code := null;
192 END IF;
193 IF (p_x_uc_header_rec.active_start_date IS NULL) THEN
194 p_x_uc_header_rec.active_start_date := l_old_uc_header_rec.active_start_date;
195 ELSIF (p_x_uc_header_rec.active_start_date = FND_API.G_MISS_DATE) THEN
196 p_x_uc_header_rec.active_start_date := null;
197 END IF;
198 IF (p_x_uc_header_rec.active_end_date IS NULL) THEN
199 p_x_uc_header_rec.active_end_date := l_old_uc_header_rec.active_end_date;
200 ELSIF (p_x_uc_header_rec.active_end_date = FND_API.G_MISS_DATE) THEN
201 p_x_uc_header_rec.active_end_date := null;
202 END IF;
203 IF (p_x_uc_header_rec.active_uc_status_code IS NULL) THEN
204 p_x_uc_header_rec.active_uc_status_code := l_old_uc_header_rec.active_uc_status_code;
205 ELSIF (p_x_uc_header_rec.active_uc_status_code = FND_API.G_MISS_CHAR) THEN
206 p_x_uc_header_rec.active_uc_status_code := null;
207 END IF;
208 IF (p_x_uc_header_rec.parent_uc_header_id IS NULL) THEN
209 p_x_uc_header_rec.parent_uc_header_id := l_old_uc_header_rec.parent_uc_header_id;
210 ELSIF (p_x_uc_header_rec.parent_uc_header_id = FND_API.G_MISS_NUM) THEN
211 p_x_uc_header_rec.parent_uc_header_id := null;
212 END IF;
213 IF (p_x_uc_header_rec.attribute_category IS NULL) THEN
214 p_x_uc_header_rec.attribute_category := l_old_uc_header_rec.attribute_category;
215 ELSIF (p_x_uc_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
216 p_x_uc_header_rec.attribute_category := null;
217 END IF;
218 IF (p_x_uc_header_rec.attribute1 IS NULL) THEN
219 p_x_uc_header_rec.attribute1 := l_old_uc_header_rec.attribute1;
220 ELSIF (p_x_uc_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
221 p_x_uc_header_rec.attribute1 := null;
222 END IF;
223 IF (p_x_uc_header_rec.attribute2 IS NULL) THEN
224 p_x_uc_header_rec.attribute2 := l_old_uc_header_rec.attribute2;
225 ELSIF (p_x_uc_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
226 p_x_uc_header_rec.attribute2 := null;
227 END IF;
228 IF (p_x_uc_header_rec.attribute3 IS NULL) THEN
229 p_x_uc_header_rec.attribute3 := l_old_uc_header_rec.attribute3;
230 ELSIF (p_x_uc_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
231 p_x_uc_header_rec.attribute3 := null;
232 END IF;
233 IF (p_x_uc_header_rec.attribute4 IS NULL) THEN
234 p_x_uc_header_rec.attribute4 := l_old_uc_header_rec.attribute4;
235 ELSIF (p_x_uc_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
236 p_x_uc_header_rec.attribute4 := null;
237 END IF;
238 IF (p_x_uc_header_rec.attribute5 IS NULL) THEN
239 p_x_uc_header_rec.attribute5 := l_old_uc_header_rec.attribute5;
240 ELSIF (p_x_uc_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
241 p_x_uc_header_rec.attribute5 := null;
242 END IF;
243 IF (p_x_uc_header_rec.attribute6 IS NULL) THEN
244 p_x_uc_header_rec.attribute6 := l_old_uc_header_rec.attribute6;
245 ELSIF (p_x_uc_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
246 p_x_uc_header_rec.attribute6 := null;
247 END IF;
248 IF (p_x_uc_header_rec.attribute7 IS NULL) THEN
249 p_x_uc_header_rec.attribute7 := l_old_uc_header_rec.attribute7;
250 ELSIF (p_x_uc_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
251 p_x_uc_header_rec.attribute7 := null;
252 END IF;
253 IF (p_x_uc_header_rec.attribute8 IS NULL) THEN
254 p_x_uc_header_rec.attribute8 := l_old_uc_header_rec.attribute8;
255 ELSIF (p_x_uc_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
256 p_x_uc_header_rec.attribute8 := null;
257 END IF;
258 IF (p_x_uc_header_rec.attribute9 IS NULL) THEN
259 p_x_uc_header_rec.attribute9 := l_old_uc_header_rec.attribute9;
260 ELSIF (p_x_uc_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
261 p_x_uc_header_rec.attribute9 := null;
262 END IF;
263 IF (p_x_uc_header_rec.attribute10 IS NULL) THEN
264 p_x_uc_header_rec.attribute10 := l_old_uc_header_rec.attribute10;
265 ELSIF (p_x_uc_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
266 p_x_uc_header_rec.attribute10 := null;
267 END IF;
268 IF (p_x_uc_header_rec.attribute11 IS NULL) THEN
269 p_x_uc_header_rec.attribute11 := l_old_uc_header_rec.attribute11;
270 ELSIF (p_x_uc_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
271 p_x_uc_header_rec.attribute11 := null;
272 END IF;
273 IF (p_x_uc_header_rec.attribute12 IS NULL) THEN
274 p_x_uc_header_rec.attribute12 := l_old_uc_header_rec.attribute12;
275 ELSIF (p_x_uc_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
276 p_x_uc_header_rec.attribute12 := null;
277 END IF;
278 IF (p_x_uc_header_rec.attribute13 IS NULL) THEN
279 p_x_uc_header_rec.attribute13 := l_old_uc_header_rec.attribute13;
280 ELSIF (p_x_uc_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
281 p_x_uc_header_rec.attribute13 := null;
282 END IF;
283 IF (p_x_uc_header_rec.attribute14 IS NULL) THEN
284 p_x_uc_header_rec.attribute14 := l_old_uc_header_rec.attribute14;
285 ELSIF (p_x_uc_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
286 p_x_uc_header_rec.attribute14 := null;
287 END IF;
288 IF (p_x_uc_header_rec.attribute15 IS NULL) THEN
289 p_x_uc_header_rec.attribute15 := l_old_uc_header_rec.attribute15;
290 ELSIF (p_x_uc_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
291 p_x_uc_header_rec.attribute15 := null;
292 END IF;
293 END convert_attributes;
294
295 -- Procedure to validate individual record attributes
296 PROCEDURE validate_parameters(
297 p_uc_header_rec IN ahl_uc_instance_pvt.uc_header_rec_type,
298 x_return_status OUT NOCOPY VARCHAR2)
299 IS
300 BEGIN
301 x_return_status := FND_API.G_RET_STS_SUCCESS;
302 IF (p_uc_header_rec.mc_name IS NULL AND
303 p_uc_header_rec.mc_revision IS NOT NULL) OR
304 (p_uc_header_rec.mc_name IS NOT NULL AND
305 p_uc_header_rec.mc_revision IS NULL) THEN
306 FND_MESSAGE.set_name( 'AHL','AHL_UC_MC_NAME_REV_INVALID' );
307 FND_MSG_PUB.add;
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 END IF;
310 END;
311
312 -- Procedure to validate mc_header
313 PROCEDURE validate_mc_header(
314 p_mc_name IN VARCHAR2,
315 p_mc_revision IN VARCHAR2,
316 p_x_mc_header_id IN OUT NOCOPY NUMBER,
317 x_relationship_id OUT NOCOPY VARCHAR2,
318 x_return_status OUT NOCOPY VARCHAR2)
319 IS
320 l_mc_header_id NUMBER;
321 l_relationship_id NUMBER;
322 CURSOR get_rec_from_value(c_mc_name varchar2, c_mc_revision varchar2) IS
323 SELECT H.mc_header_id,
324 R.relationship_id
325 FROM ahl_mc_headers_b H,
326 ahl_mc_relationships R
327 WHERE H.name = c_mc_name
328 AND H.revision = c_mc_revision
329 AND H.mc_header_id = R.mc_header_id
330 AND R.parent_relationship_id IS NULL
331 AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
332 AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
333 CURSOR get_rec_from_id(c_mc_header_id NUMBER ) IS
334 SELECT H.mc_header_id,
335 R.relationship_id
336 FROM ahl_mc_headers_b H,
337 ahl_mc_relationships R
338 WHERE H.mc_header_id = c_mc_header_id
339 AND H.mc_header_id = R.mc_header_id
340 AND R.parent_relationship_id IS NULL
341 AND trunc(nvl(R.active_start_date,SYSDATE)) <= trunc(SYSDATE)
342 AND trunc(nvl(R.active_end_date, SYSDATE+1)) > trunc(SYSDATE);
343 BEGIN
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345 IF (p_mc_name IS NULL OR p_mc_revision IS NULL) AND
346 p_x_mc_header_id IS NULL THEN
347 x_return_status := FND_API.G_RET_STS_ERROR;
348 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
349 FND_MSG_PUB.add;
350 ELSIF (p_mc_name IS NULL OR p_mc_revision IS NULL) AND
351 p_x_mc_header_id IS NOT NULL THEN
352 OPEN get_rec_from_id(p_x_mc_header_id);
353 FETCH get_rec_from_id INTO l_mc_header_id, l_relationship_id;
354 IF get_rec_from_id%NOTFOUND THEN
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 FND_MESSAGE.set_name('AHL', 'AHL_UC_MC_HEADER_ID_INVALID');
357 FND_MESSAGE.set_token('MC_HEADER_ID', p_x_mc_header_id);
358 FND_MSG_PUB.add;
359 ELSE
360 p_x_mc_header_id := l_mc_header_id;
361 x_relationship_id := l_relationship_id;
362 END IF;
363 CLOSE get_rec_from_id;
364 ELSIF p_mc_name IS NOT NULL AND p_mc_revision IS NOT NULL THEN
365 OPEN get_rec_from_value(p_mc_name, p_mc_revision);
366 LOOP
367 FETCH get_rec_from_value INTO l_mc_header_id, l_relationship_id;
368 EXIT WHEN get_rec_from_value%NOTFOUND;
369 END LOOP;
370 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MESSAGE.set_name('AHL', 'AHL_UC_MC_HEADER_INVALID');
373 FND_MESSAGE.set_token('MC_NAME', p_mc_name);
374 FND_MESSAGE.set_token('MC_REV', p_mc_revision);
375 FND_MSG_PUB.add;
376 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
377 p_x_mc_header_id := l_mc_header_id;
378 x_relationship_id := l_relationship_id;
379 ELSE
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 FND_MESSAGE.set_name('AHL', 'AHL_UC_MC_HEADER_INVALID');
382 FND_MESSAGE.set_token('MC_NAME', p_mc_name);
383 FND_MESSAGE.set_token('MC_REV', p_mc_revision);
384 FND_MSG_PUB.add;
385 END IF;
386 CLOSE get_rec_from_value;
387 END IF;
388 END validate_mc_header;
389
390 -- Procedure to validate csi_item_instance
391 PROCEDURE validate_csi_instance(
392 p_instance_num IN csi_item_instances.instance_number%TYPE,
393 p_x_instance_id IN OUT NOCOPY NUMBER,
394 p_relationship_id IN NUMBER,
395 x_return_status OUT NOCOPY VARCHAR2)
396 IS
397 l_instance_id number;
398 --The following cursors don't consider the profile option fnd_profile.value('AHL_VALIDATE_ALT_ITEM_ORG')
399 --That is assuming fnd_profile.value('AHL_VALIDATE_ALT_ITEM_ORG')='Y'
400 CURSOR get_rec_from_value(c_instance_num csi_item_instances.instance_number%TYPE,
401 c_relationship_id NUMBER) IS
402 --Added DISTINCT to the following two queries for fixing bug 4102152, Jerry on 01/04/2005
403 SELECT distinct C.instance_id
404 FROM csi_item_instances C,
405 ahl_mc_relationships R,
406 ahl_item_associations_b A
407 WHERE C.instance_number = c_instance_num
408 AND R.relationship_id = c_relationship_id
409 AND R.item_group_id = A.item_group_id
410 AND C.inventory_item_id = A.inventory_item_id
411 AND C.inv_master_organization_id = A.inventory_org_id
412 -- SATHAPLI::FP Bug 7498459, 27-Nov-2008 - Even INVENTORY instances are allowed for UC header creation.
413 -- AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
414 AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT')
415 AND (A.revision IS NULL OR A.revision = C.inventory_revision) --Added by Jerry on 03/31/2005
416 AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
417 --Added by Jerry on 04/26/2005
418 AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
419
420 -- SATHAPLI::Bug# 5347338 fix
421 /*
422 AND NOT EXISTS (SELECT 'X'
423 FROM csi_ii_relationships I
424 WHERE I.subject_id = C.instance_id
425 AND I.relationship_type_code = 'COMPONENT-OF'
426 AND trunc(nvl(I.active_start_date,SYSDATE)) <= trunc(SYSDATE)
427 AND trunc(nvl(I.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
428 */
429
430 AND NOT EXISTS (SELECT 'X'
431 FROM ahl_unit_config_headers H
432 WHERE H.csi_item_instance_id = C.instance_id
433 AND trunc(nvl(H.active_end_date, SYSDATE+1)) > trunc(SYSDATE));
434 CURSOR get_rec_from_id(c_instance_id NUMBER, c_relationship_id NUMBER) IS
435 SELECT distinct C.instance_id
436 FROM csi_item_instances C,
437 ahl_mc_relationships R,
438 ahl_item_associations_b A
439 WHERE C.instance_id = c_instance_id
440 AND R.relationship_id = c_relationship_id
441 AND R.item_group_id = A.item_group_id
442 AND C.inventory_item_id = A.inventory_item_id
443 AND C.inv_master_organization_id = A.inventory_org_id
444 -- SATHAPLI::FP Bug 7498459, 27-Nov-2008 - Even INVENTORY instances are allowed for UC header creation.
445 -- AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')
446 AND C.location_type_code NOT IN ('PO','IN-TRANSIT','PROJECT')
447 AND (A.revision IS NULL OR A.revision = C.inventory_revision) --Added by Jerry on 03/31/2005
448 AND A.interchange_type_code IN ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
449 --Added by Jerry on 04/26/2005
450 AND trunc(nvl(C.active_end_date, SYSDATE+1)) > trunc(SYSDATE)
451
452 -- SATHAPLI::Bug# 5347338 fix
453 /*
454 AND NOT EXISTS (SELECT 'X'
455 FROM csi_ii_relationships I
456 WHERE I.subject_id = C.instance_id
457 AND I.relationship_type_code = 'COMPONENT-OF'
458 AND trunc(nvl(I.active_start_date,SYSDATE)) <= trunc(SYSDATE)
459 AND trunc(nvl(I.active_end_date, SYSDATE+1)) > trunc(SYSDATE))
460 */
461
462 AND NOT EXISTS (SELECT 'X'
463 FROM ahl_unit_config_headers H
464 WHERE H.csi_item_instance_id = C.instance_id
465 AND trunc(nvl(H.active_end_date, SYSDATE+1)) > trunc(SYSDATE));
466 BEGIN
467 x_return_status := FND_API.G_RET_STS_SUCCESS;
468 IF p_instance_num IS NULL AND p_x_instance_id IS NULL THEN
469 x_return_status := FND_API.G_RET_STS_ERROR;
470 FND_MESSAGE.set_name('AHL', 'AHL_COM_INVALID_PROCEDURE_CALL');
471 FND_MSG_PUB.add;
472 ELSIF p_instance_num IS NULL AND p_x_instance_id IS NOT NULL THEN
473 OPEN get_rec_from_id(p_x_instance_id, p_relationship_id);
474 FETCH get_rec_from_id INTO l_instance_id;
475 IF get_rec_from_id%NOTFOUND THEN
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
478 FND_MESSAGE.set_token('CSII', p_x_instance_id);
479 FND_MSG_PUB.add;
480 END IF;
481 CLOSE get_rec_from_id;
482 ELSIF p_instance_num IS NOT NULL THEN
483 OPEN get_rec_from_value(p_instance_num, p_relationship_id);
484 LOOP
485 FETCH get_rec_from_value INTO l_instance_id;
486 EXIT WHEN get_rec_from_value%NOTFOUND;
487 END LOOP;
488 IF ( get_rec_from_value%ROWCOUNT = 0 ) THEN
489 x_return_status := FND_API.G_RET_STS_ERROR;
490 FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
491 FND_MESSAGE.set_token('CSII', p_instance_num);
492 FND_MSG_PUB.add;
493 ELSIF ( get_rec_from_value%ROWCOUNT = 1 ) THEN
494 p_x_instance_id := l_instance_id;
495 ELSE
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
498 FND_MESSAGE.set_token('CSII', p_instance_num);
499 FND_MSG_PUB.add;
500 END IF;
501 CLOSE get_rec_from_value;
502 END IF;
503 END validate_csi_instance;
504
505 -- Procedure to validate individual record attributes
506 PROCEDURE validate_attributes(
507 p_uc_header_rec IN ahl_uc_instance_pvt.uc_header_rec_type,
508 p_dml_operation IN VARCHAR2,
509 x_return_status OUT NOCOPY VARCHAR2)
510 IS
511 CURSOR check_uc_name1(c_uc_name varchar2) IS
512 SELECT 'x'
513 FROM ahl_unit_config_headers
514 WHERE name = c_uc_name;
515 CURSOR check_uc_name2(c_uc_name varchar2, c_uc_header_id number) IS
516 SELECT 'x'
517 FROM ahl_unit_config_headers
518 WHERE name = c_uc_name
519 AND unit_config_header_id <> c_uc_header_id;
520 CURSOR check_uc_name3(c_uc_header_id number) IS
521 SELECT name
522 FROM ahl_unit_config_headers
523 WHERE unit_config_header_id = c_uc_header_id;
524 l_dummy varchar2(1);
525 l_name check_uc_name3%ROWTYPE;
526
527 BEGIN
528 x_return_status := FND_API.G_RET_STS_SUCCESS;
529 -- Check if name is unique when creating or updating a UC header record
530 IF p_dml_operation = 'C' THEN
531 OPEN check_uc_name1(p_uc_header_rec.uc_name);
532 FETCH check_uc_name1 INTO l_dummy;
533 IF check_uc_name1%FOUND THEN
534 FND_MESSAGE.set_name( 'AHL','AHL_UC_NAME_DUPLICATE' );
535 FND_MESSAGE.set_token( 'NAME', p_uc_header_rec.uc_name );
536 FND_MSG_PUB.add;
537 x_return_status := FND_API.G_RET_STS_ERROR;
538 END IF;
539 CLOSE check_uc_name1;
540 ELSIF p_dml_operation = 'U' THEN
541 OPEN check_uc_name2(p_uc_header_rec.uc_name, p_uc_header_rec.uc_header_id);
542 FETCH check_uc_name2 INTO l_dummy;
543 IF check_uc_name2%FOUND THEN
544 FND_MESSAGE.set_name( 'AHL','AHL_UC_NAME_DUPLICATE' );
545 FND_MESSAGE.set_token( 'NAME', p_uc_header_rec.uc_name );
546 FND_MSG_PUB.add;
547 x_return_status := FND_API.G_RET_STS_ERROR;
548 END IF;
549 CLOSE check_uc_name2;
550
551 -- UC name change is only allowed when the unit_config_status is DRAFT
552 IF p_uc_header_rec.unit_config_status_code NOT IN ('DRAFT', 'APPROVAL_REJECTED') THEN
553 OPEN check_uc_name3(p_uc_header_rec.uc_header_id);
554 FETCH check_uc_name3 INTO l_name;
555 IF check_uc_name3%FOUND AND (l_name.name <> p_uc_header_rec.uc_name) THEN
556 FND_MESSAGE.set_name( 'AHL','AHL_UC_NAME_CHANGE_UNALLOWED' );
557 FND_MSG_PUB.add;
558 x_return_status := FND_API.G_RET_STS_ERROR;
559 END IF;
560 CLOSE check_uc_name3;
561 END IF;
562 END IF;
563 -- Check name is not null. This procedure is executed after nullify_attributes
564 -- (create) or convert_attributes(update), so the check is the same for creation
565 -- and update.
566 IF p_uc_header_rec.uc_name is NULL THEN
567 FND_MESSAGE.set_name('AHL','AHL_UC_NAME_NULL');
568 FND_MSG_PUB.add;
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 END IF;
571 -- The following checks are actually not really necessary.
572 -- Check if the master_config_id contains a null value.
573 IF p_uc_header_rec.mc_header_id IS NULL THEN
574 FND_MESSAGE.set_name('AHL','AHL_MC_HEADER_ID_INVALID');
575 FND_MESSAGE.set_token('MC_HEADER_ID', p_uc_header_rec.mc_header_id);
576 FND_MSG_PUB.add;
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 END IF;
579 -- Check if the csi_item_instance_id contains a null value.
580 IF p_uc_header_rec.instance_id IS NULL THEN
581 FND_MESSAGE.set_name('AHL','AHL_UC_INSTANCE_NULL');
582 FND_MSG_PUB.add;
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 END IF;
585 -- Check if the unit_config_status_code contains a null value.
586 IF p_uc_header_rec.unit_config_status_code IS NULL THEN
587 FND_MESSAGE.set_name('AHL','AHL_UC_CONFIG_STATUS_NULL');
588 FND_MSG_PUB.add;
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 END IF;
591
592 END validate_attributes;
593
594 -- Define Procedure create_uc_header --
595 -- This API is used to create a UC header record in ahl_unit_config_headers
596 PROCEDURE create_uc_header(
597 p_api_version IN NUMBER := 1.0,
598 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
599 p_commit IN VARCHAR2 := FND_API.G_FALSE,
600 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
601 p_module_type IN VARCHAR2 := NULL,
602 x_return_status OUT NOCOPY VARCHAR2,
603 x_msg_count OUT NOCOPY NUMBER,
604 x_msg_data OUT NOCOPY VARCHAR2,
605 p_x_uc_header_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type)
606 IS
607 l_api_name CONSTANT VARCHAR2(30) := 'create_uc_header';
608 l_api_version CONSTANT NUMBER := 1.0;
609 l_return_status VARCHAR2(1);
610 l_msg_count NUMBER;
611 l_msg_data VARCHAR2(2000);
612 l_relationship_id NUMBER;
613 l_serial_number csi_item_instances.serial_number%TYPE;
614 l_mfg_serial_number_flag csi_item_instances.mfg_serial_number_flag%TYPE;
615 l_serial_number_tag csi_iea_values.attribute_value%TYPE;
616 l_object_version_number NUMBER;
617 l_return_val BOOLEAN;
618 l_transaction_type_id NUMBER;
619 l_attribute_id NUMBER;
620 l_attribute_value_id NUMBER;
621 l_attribute_value csi_iea_values.attribute_value%TYPE;
622 l_csi_instance_rec csi_datastructures_pub.instance_rec;
623 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
624 l_csi_extend_attrib_rec csi_datastructures_pub.extend_attrib_values_rec;
625 l_csi_ext_attrib_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
626
627 CURSOR get_serial_number(c_instance_id NUMBER) IS
628 SELECT serial_number, mfg_serial_number_flag
629 FROM csi_item_instances
630 WHERE instance_id = c_instance_id
631 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
632 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
633 BEGIN
634 -- Initialize API return status to success
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636
637 -- Standard Start of API savepoint
638 SAVEPOINT create_uc_header;
639
640 -- Standard call to check for call compatibility.
641 IF NOT FND_API.compatible_api_call(
642 l_api_version,
643 p_api_version,
644 l_api_name,
645 G_PKG_NAME)
646 THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649
650 -- Initialize message list if p_init_msg_list is set to TRUE.
651 IF FND_API.to_boolean( p_init_msg_list ) THEN
652 FND_MSG_PUB.initialize;
653 END IF;
654
655 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
656 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
657 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
658 'At the start of the procedure');
659 END IF;
660
661 -- If the module type is JSP, then default values for ID columns of LOV attributes
662
663 IF ( p_module_type = 'JSP' ) THEN
664 clear_lov_attribute_ids(p_x_uc_header_rec);
665 END IF;
666 -- Set unit_config_status_code to 'DRAFT'
667 p_x_uc_header_rec.unit_config_status_code := 'DRAFT';
668 -- Nullify G_MISS values of optional fields
669 nullify_attributes(p_x_uc_header_rec);
670 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
671 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
672 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
673 ': after nullify_attributes');
674 END IF;
675
676 validate_parameters(p_x_uc_header_rec, l_return_status);
677 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
678 RAISE FND_API.G_EXC_ERROR;
679 END IF;
680 validate_mc_header(p_x_uc_header_rec.mc_name,
681 p_x_uc_header_rec.mc_revision,
682 p_x_uc_header_rec.mc_header_id,
683 l_relationship_id,
684 l_return_status);
685 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
686 RAISE FND_API.G_EXC_ERROR;
687 END IF;
688 --dbms_output.put_line('after mc_header validate and mc_name '|| p_x_uc_header_rec.mc_name);
689 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
690 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
691 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
692 ': after validate_mc_header and l_relationship_id ='||l_relationship_id);
693 END IF;
694
695 --IF (p_module_type = 'JSP') THEN
696 --Commented out the condition on 10/18/2005 by Jerry to fix bug 4612418
697 validate_csi_instance(p_x_uc_header_rec.instance_number,
698 p_x_uc_header_rec.instance_id,
699 l_relationship_id,
700 l_return_status);
701 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704 --END IF;
705
706 -- Validate all attributes (Item level validation)
707 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
708 validate_attributes(p_x_uc_header_rec, 'C', l_return_status);
709 -- If any severe error occurs, then, abort API.
710 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
711 RAISE FND_API.G_EXC_ERROR;
712 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
714 END IF;
715 END IF;
716
717 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
718 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
719 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
720 ': aafter validate_attributes');
721 END IF;
722
723 -- Get the unit_config_header_id from the Sequence
724 SELECT AHL_UNIT_CONFIG_HEADERS_S.NEXTVAL
725 INTO p_x_uc_header_rec.uc_header_id
726 FROM DUAL;
727 p_x_uc_header_rec.object_version_number := 1;
728
729 -- Insert the record into table ahl_unit_config_headers
730 INSERT INTO ahl_unit_config_headers(
731 unit_config_header_id,
732 object_version_number,
733 creation_date,
734 created_by,
735 last_update_date,
736 last_updated_by,
737 last_update_login,
738 name,
739 master_config_id,
740 csi_item_instance_id,
741 unit_config_status_code,
742 active_start_date,
743 active_end_date,
744 active_uc_status_code,
745 parent_uc_header_id,
746 attribute_category,
747 attribute1,
748 attribute2,
749 attribute3,
750 attribute4,
751 attribute5,
752 attribute6,
753 attribute7,
754 attribute8,
755 attribute9,
756 attribute10,
757 attribute11,
758 attribute12,
759 attribute13,
760 attribute14,
761 attribute15)
762 VALUES(
763 p_x_uc_header_rec.uc_header_id,
764 p_x_uc_header_rec.object_version_number,
765 sysdate,
766 fnd_global.user_id,
767 sysdate,
768 fnd_global.user_id,
769 fnd_global.login_id,
770 p_x_uc_header_rec.uc_name,
771 p_x_uc_header_rec.mc_header_id,
772 p_x_uc_header_rec.instance_id,
773 p_x_uc_header_rec.unit_config_status_code,
774 p_x_uc_header_rec.active_start_date,
775 p_x_uc_header_rec.active_end_date,
776 p_x_uc_header_rec.active_uc_status_code,
777 p_x_uc_header_rec.parent_uc_header_id,
778 p_x_uc_header_rec.attribute_category,
779 p_x_uc_header_rec.attribute1,
780 p_x_uc_header_rec.attribute2,
781 p_x_uc_header_rec.attribute3,
782 p_x_uc_header_rec.attribute4,
783 p_x_uc_header_rec.attribute5,
784 p_x_uc_header_rec.attribute6,
785 p_x_uc_header_rec.attribute7,
786 p_x_uc_header_rec.attribute8,
787 p_x_uc_header_rec.attribute9,
788 p_x_uc_header_rec.attribute10,
789 p_x_uc_header_rec.attribute11,
790 p_x_uc_header_rec.attribute12,
791 p_x_uc_header_rec.attribute13,
792 p_x_uc_header_rec.attribute14,
793 p_x_uc_header_rec.attribute15);
794
795 -- Insert into the exactly same record into ahl_unit_config_headers_h
796 ahl_util_uc_pkg.copy_uc_header_to_history(p_x_uc_header_rec.uc_header_id, l_return_status);
797 --IF history copy failed, then don't raise exception, just add the message to the message stack
798 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
799 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
800 FND_MSG_PUB.add;
801 END IF;
802
803 --Need to check if the instance picked from CSI with serial_number has a serial_no_tag, and
804 --if not, we have to derive its value according to mfg_serail_number_flag ('Y'->'INVENTORY',
805 --assuming CSI has the validation to ensure the serial_number exisiting in table
806 --mfg_searil_numbers, otherwise it is 'TEMPORARY'
807 OPEN get_serial_number(p_x_uc_header_rec.instance_id);
808 FETCH get_serial_number INTO l_serial_number, l_mfg_serial_number_flag;
809 IF get_serial_number%NOTFOUND THEN
810 FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
811 FND_MESSAGE.set_token('CSII', p_x_uc_header_rec.instance_id);
812 FND_MSG_PUB.add;
813 RAISE FND_API.G_EXC_ERROR;
814 CLOSE get_serial_number;
815 ELSE
816 CLOSE get_serial_number;
817 END IF;
818
819 IF l_serial_number IS NOT NULL THEN
820 --Retrieve existing value of serial_number_tag if present.
821 AHL_UTIL_UC_PKG.getcsi_attribute_value(p_x_uc_header_rec.instance_id,
822 'AHL_TEMP_SERIAL_NUM',
823 l_attribute_value,
824 l_attribute_value_id,
825 l_object_version_number,
826 l_return_val);
827 IF NOT l_return_val THEN --serial_number_tag doesn't exist
828 IF l_mfg_serial_number_flag = 'Y' THEN
829 l_serial_number_tag := 'INVENTORY';
830 ELSE
831 l_serial_number_tag := 'TEMPORARY';
832 END IF;
833 --Build CSI transaction record, first get transaction_type_id
834 AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_val);
835 IF NOT(l_return_val) THEN
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 END IF;
838
839 l_csi_transaction_rec.source_transaction_date := SYSDATE;
840 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
841 AHL_Util_UC_Pkg.getcsi_attribute_id('AHL_TEMP_SERIAL_NUM', l_attribute_id,l_return_val);
842 IF NOT(l_return_val) THEN
843 FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
844 FND_MESSAGE.set_token('CODE', 'AHL_TEMP_SERIAL_NUM');
845 FND_MSG_PUB.add;
846 ELSE
847 l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
848 l_csi_extend_attrib_rec.attribute_value := l_serial_number_tag;
849 l_csi_extend_attrib_rec.instance_id := p_x_uc_header_rec.instance_id;
850 l_csi_ext_attrib_values_tbl(1) := l_csi_extend_attrib_rec;
851 END IF;
852
853 CSI_ITEM_INSTANCE_PUB.create_extended_attrib_values(
854 p_api_version => 1.0,
855 p_txn_rec => l_csi_transaction_rec,
856 p_ext_attrib_tbl => l_csi_ext_attrib_values_tbl,
857 x_return_status => l_return_status,
858 x_msg_count => l_msg_count,
859 x_msg_data => l_msg_data);
860
861 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
862 RAISE FND_API.G_EXC_ERROR;
863 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
864 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
865 END IF;
866 END IF;
867 END IF;
868
869 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
871 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
872 END IF;
873
874 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
875 l_msg_count := FND_MSG_PUB.count_msg;
876 IF l_msg_count > 0 THEN
877 x_msg_count := l_msg_count;
878 RAISE FND_API.G_EXC_ERROR;
879 END IF;
880 -- Perform the Commit (if requested)
881 IF FND_API.to_boolean( p_commit ) THEN
882 COMMIT;
883 END IF;
884 -- Count and Get messages (optional)
885 FND_MSG_PUB.count_and_get(
886 p_encoded => FND_API.G_FALSE,
887 p_count => x_msg_count,
888 p_data => x_msg_data);
889 EXCEPTION
890 WHEN FND_API.G_EXC_ERROR THEN
891 ROLLBACK TO create_uc_header;
892 x_return_status := FND_API.G_RET_STS_ERROR ;
893 FND_MSG_PUB.count_and_get(
894 p_encoded => FND_API.G_FALSE,
895 p_count => x_msg_count,
896 p_data => x_msg_data);
897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
898 ROLLBACK TO create_uc_header;
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
900 FND_MSG_PUB.count_and_get(
901 p_encoded => FND_API.G_FALSE,
902 p_count => x_msg_count,
903 p_data => x_msg_data);
904 WHEN OTHERS THEN
905 ROLLBACK TO create_uc_header;
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
907 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
908 THEN
909 FND_MSG_PUB.add_exc_msg(
910 p_pkg_name => G_PKG_NAME,
911 p_procedure_name => l_api_name,
912 p_error_text => SUBSTRB(SQLERRM,1,240));
913 END IF;
914 FND_MSG_PUB.count_and_get(
915 p_encoded => FND_API.G_FALSE,
916 p_count => x_msg_count,
917 p_data => x_msg_data);
918 END create_uc_header;
919
920 -- Define Procedure update_uc_header --
921 -- This API is used to update a UC header name or some attributes of the top node
922 -- instance of the UC.
923 PROCEDURE update_uc_header(
924 p_api_version IN NUMBER := 1.0,
925 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
926 p_commit IN VARCHAR2 := FND_API.G_FALSE,
927 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
928 p_module_type IN VARCHAR2 := NULL,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data OUT NOCOPY VARCHAR2,
932 p_x_uc_header_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type,
933 p_uc_instance_rec IN ahl_uc_instance_pvt.uc_instance_rec_type)
934 IS
935 l_api_name CONSTANT VARCHAR2(30) := 'update_uc_header';
936 l_api_version CONSTANT NUMBER := 1.0;
937 l_return_status VARCHAR2(1);
938 l_msg_count NUMBER;
939
940 BEGIN
941 -- Initialize API return status to success
942 x_return_status := FND_API.G_RET_STS_SUCCESS;
943
944 -- Standard Start of API savepoint
945 SAVEPOINT update_uc_header;
946
947 -- Standard call to check for call compatibility.
948 IF NOT FND_API.compatible_api_call(
949 l_api_version,
950 p_api_version,
951 l_api_name,
952 G_PKG_NAME)
953 THEN
954 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
955 END IF;
956
957 -- Initialize message list if p_init_msg_list is set to TRUE.
958 IF FND_API.to_boolean( p_init_msg_list ) THEN
959 FND_MSG_PUB.initialize;
960 END IF;
961
962 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
963 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
964 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
965 'At the start of the procedure');
966 END IF;
967
968 -- Convert G_MISS values to NULL and NULL to OLD values
969 convert_attributes(p_x_uc_header_rec, l_return_status);
970 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
971 RAISE FND_API.G_EXC_ERROR;
972 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
973 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
974 END IF;
975
976 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
977 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
978 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
979 ': after convert_attributes');
980 END IF;
981
982 -- ACL :: Changes for R12
983 IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_x_uc_header_rec.uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
984 FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
985 FND_MSG_PUB.add;
986 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
987 END IF;
988
989 -- Validate all attributes (Item level validation)
990 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
991 validate_attributes(p_x_uc_header_rec, 'U', l_return_status);
992 -- If any severe error occurs, then, abort API.
993 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
994 RAISE FND_API.G_EXC_ERROR;
995 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
996 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
997 END IF;
998 END IF;
999 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1000 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1001 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1002 ': after validate_attributes');
1003 END IF;
1004
1005 UPDATE ahl_unit_config_headers SET
1006 object_version_number = object_version_number + 1,
1007 last_update_date = sysdate,
1008 last_updated_by = fnd_global.user_id,
1009 last_update_login = fnd_global.login_id,
1010 name = p_x_uc_header_rec.uc_name,
1011 master_config_id = p_x_uc_header_rec.mc_header_id,
1012 csi_item_instance_id = p_x_uc_header_rec.instance_id,
1013 unit_config_status_code = p_x_uc_header_rec.unit_config_status_code,
1014 active_start_date = p_x_uc_header_rec.active_start_date,
1015 active_end_date = p_x_uc_header_rec.active_end_date,
1016 active_uc_status_code = p_x_uc_header_rec.active_uc_status_code,
1017 parent_uc_header_id = p_x_uc_header_rec.parent_uc_header_id,
1018 attribute_category = p_x_uc_header_rec.attribute_category,
1019 attribute1 = p_x_uc_header_rec.attribute1,
1020 attribute2 = p_x_uc_header_rec.attribute2,
1021 attribute3 = p_x_uc_header_rec.attribute3,
1022 attribute4 = p_x_uc_header_rec.attribute4,
1023 attribute5 = p_x_uc_header_rec.attribute5,
1024 attribute6 = p_x_uc_header_rec.attribute6,
1025 attribute7 = p_x_uc_header_rec.attribute7,
1026 attribute8 = p_x_uc_header_rec.attribute8,
1027 attribute9 = p_x_uc_header_rec.attribute9,
1028 attribute10 = p_x_uc_header_rec.attribute10,
1029 attribute11 = p_x_uc_header_rec.attribute11,
1030 attribute12 = p_x_uc_header_rec.attribute12,
1031 attribute13 = p_x_uc_header_rec.attribute13,
1032 attribute14 = p_x_uc_header_rec.attribute14,
1033 attribute15 = p_x_uc_header_rec.attribute15
1034 WHERE unit_config_header_id = p_x_uc_header_rec.uc_header_id
1035 AND object_version_number = p_x_uc_header_rec.object_version_number;
1036 IF SQL%NOTFOUND THEN
1037 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1038 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1039 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','ovn='||p_x_uc_header_rec.object_version_number);
1040 END IF;
1041 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1042 FND_MSG_PUB.add;
1043 x_return_status := FND_API.G_RET_STS_ERROR;
1044 RAISE FND_API.G_EXC_ERROR;
1045 END IF;
1046
1047 ahl_util_uc_pkg.copy_uc_header_to_history(p_x_uc_header_rec.uc_header_id, l_return_status);
1048 --IF history copy failed, then don't raise exception, just add the message to the message stack
1049 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1050 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1051 FND_MSG_PUB.add;
1052 END IF;
1053
1054 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1055 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1056 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','before calling update_instance_attr');
1057 END IF;
1058
1059 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1060 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1061 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','x_return_status='||x_return_status);
1062 END IF;
1063 --Call ahl_uc_instance_pvt.update_instance_attr to update the top node instance attributes
1064 --In front end UI, if there is no change to the instance attributes, then the whole p_uc_instance_rec
1065 --will be null.
1066 IF p_uc_instance_rec.instance_id IS NOT NULL THEN
1067 ahl_uc_instance_pvt.update_instance_attr(
1068 p_api_version => 1.0,
1069 p_init_msg_list => FND_API.G_FALSE,
1070 p_commit => FND_API.G_FALSE,
1071 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1072 x_return_status => x_return_status,
1073 x_msg_count => x_msg_count,
1074 x_msg_data => x_msg_data,
1075 p_uc_header_id => p_x_uc_header_rec.uc_header_id,
1076 p_uc_instance_rec => p_uc_instance_rec,
1077 p_prod_user_flag => 'N');
1078
1079 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1080 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1081 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'After calling update_instance_attr');
1082 END IF;
1083
1084 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1085 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1086 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','x_return_status='||x_return_status);
1087 END IF;
1088 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1089 RAISE FND_API.G_EXC_ERROR;
1090 END IF;
1091 END IF;
1092
1093 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1094 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1095 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'Before check message count');
1096 END IF;
1097
1098 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1099 l_msg_count := FND_MSG_PUB.count_msg;
1100 IF l_msg_count > 0 THEN
1101 x_msg_count := l_msg_count;
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1106 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1107 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'Before commit');
1108 END IF;
1109
1110 -- Perform the Commit (if requested)
1111 IF FND_API.to_boolean( p_commit ) THEN
1112 COMMIT;
1113 END IF;
1114
1115 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1117 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'After commit');
1118 END IF;
1119
1120 -- Count and Get messages (optional)
1121 FND_MSG_PUB.count_and_get(
1122 p_encoded => FND_API.G_FALSE,
1123 p_count => x_msg_count,
1124 p_data => x_msg_data);
1125
1126 EXCEPTION
1127 WHEN FND_API.G_EXC_ERROR THEN
1128 ROLLBACK TO update_uc_header;
1129 x_return_status := FND_API.G_RET_STS_ERROR ;
1130 FND_MSG_PUB.count_and_get(
1131 p_encoded => FND_API.G_FALSE,
1132 p_count => x_msg_count,
1133 p_data => x_msg_data);
1134 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1135 ROLLBACK TO update_uc_header;
1136 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1137 FND_MSG_PUB.count_and_get(
1138 p_encoded => FND_API.G_FALSE,
1139 p_count => x_msg_count,
1140 p_data => x_msg_data);
1141 WHEN OTHERS THEN
1142 ROLLBACK TO update_uc_header;
1143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1144 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1145 THEN
1146 FND_MSG_PUB.add_exc_msg(
1147 p_pkg_name => G_PKG_NAME,
1148 p_procedure_name => l_api_name,
1149 p_error_text => SUBSTRB(SQLERRM,1,240));
1150 END IF;
1151 FND_MSG_PUB.count_and_get(
1152 p_encoded => FND_API.G_FALSE,
1153 p_count => x_msg_count,
1154 p_data => x_msg_data);
1155 END update_uc_header;
1156
1157 -- Define Procedure delete_uc_header --
1158
1159 PROCEDURE delete_uc_header(
1160 p_api_version IN NUMBER := 1.0,
1161 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1162 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1163 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1164 x_return_status OUT NOCOPY VARCHAR2,
1165 x_msg_count OUT NOCOPY NUMBER,
1166 x_msg_data OUT NOCOPY VARCHAR2,
1167 p_uc_header_id IN NUMBER,
1168 p_object_version_number IN NUMBER,
1169 p_csi_instance_ovn IN NUMBER)
1170 IS
1171 l_api_name CONSTANT VARCHAR2(30) := 'delete_uc_header';
1172 l_api_version CONSTANT NUMBER := 1.0;
1173 l_return_status VARCHAR2(1);
1174 l_msg_count NUMBER;
1175 l_msg_data VARCHAR2(2000);
1176 l_uc_header_rec get_uc_header%ROWTYPE;
1177 l_uc_status ahl_unit_config_headers.unit_config_status_code%TYPE;
1178 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
1179 l_csi_upd_transaction_rec csi_datastructures_pub.transaction_rec;
1180 l_csi_instance_rec csi_datastructures_pub.instance_rec;
1181 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
1182 l_csi_instance_id_lst csi_datastructures_pub.id_tbl;
1183 l_csi_relationship_id NUMBER;
1184 l_transaction_type_id NUMBER;
1185 l_return_value BOOLEAN;
1186 l_csi_instance_ovn NUMBER;
1187 l_dummy_num NUMBER;
1188 l_object_version_number NUMBER;
1189
1190 CURSOR get_csi_obj_ver_num(c_instance_id NUMBER) IS
1191 SELECT object_version_number
1192 FROM csi_item_instances
1193 WHERE instance_id = c_instance_id
1194 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1195 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1196 CURSOR get_all_subunits(c_uc_header_id NUMBER) IS
1197 SELECT parent_uc_header_id, unit_config_header_id
1198 FROM ahl_unit_config_headers
1199 START WITH unit_config_header_id = c_uc_header_id
1200 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1201 CONNECT BY parent_uc_header_id = PRIOR unit_config_header_id
1202 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1203 FOR UPDATE OF unit_config_header_id;
1204 CURSOR get_top_unit(c_uc_header_id NUMBER) IS
1205 SELECT unit_config_header_id, unit_config_status_code
1206 FROM ahl_unit_config_headers
1207 WHERE parent_uc_header_id IS NULL
1208 START WITH unit_config_header_id = c_uc_header_id
1209 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1210 CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id
1211 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1212
1213 CURSOR get_csi_ii_relationship(c_instance_id NUMBER) IS
1214 SELECT relationship_id,
1215 object_version_number
1216 FROM csi_ii_relationships
1217 WHERE subject_id = c_instance_id
1218 AND relationship_type_code='COMPONENT-OF'
1219 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1220 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1221 BEGIN
1222 -- Initialize API return status to success
1223 x_return_status := FND_API.G_RET_STS_SUCCESS;
1224
1225 -- Standard Start of API savepoint
1226 SAVEPOINT delete_uc_header;
1227
1228 -- Standard call to check for call compatibility.
1229 IF NOT FND_API.compatible_api_call(
1230 l_api_version,
1231 p_api_version,
1232 l_api_name,
1233 G_PKG_NAME)
1234 THEN
1235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1236 END IF;
1237
1238 -- Initialize message list if p_init_msg_list is set to TRUE.
1239 IF FND_API.to_boolean( p_init_msg_list ) THEN
1240 FND_MSG_PUB.initialize;
1241 END IF;
1242
1243 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1244 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1245 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1246 'At the start of the procedure');
1247 END IF;
1248
1249 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM OR
1250 p_object_version_number IS NULL OR p_object_version_number = FND_API.G_MISS_NUM OR
1251 p_csi_instance_ovn IS NULL OR p_csi_instance_ovn = FND_API.G_MISS_NUM ) THEN
1252 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
1253 FND_MSG_PUB.add;
1254 RAISE FND_API.G_EXC_ERROR;
1255 END IF;
1256
1257 --In case p_uc_header_id is a subunit, then whether it can be expired depends on its ancestor
1258 --uc_header_id's status
1259 OPEN get_top_unit(p_uc_header_id);
1260 FETCH get_top_unit INTO l_dummy_num, l_uc_status;
1261 IF get_top_unit%NOTFOUND THEN
1262 CLOSE get_top_unit;
1263 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
1264 FND_MESSAGE.set_token('NAME', 'uc_header_id');
1265 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
1266 FND_MSG_PUB.add;
1267 RAISE FND_API.G_EXC_ERROR;
1268 ELSIF (l_uc_status IS NULL OR l_uc_status NOT IN ('DRAFT', 'APPROVAL_REJECTED')) THEN
1269 CLOSE get_top_unit;
1270 FND_MESSAGE.set_name( 'AHL', 'AHL_UC_STATUS_NOT_DRAFT' );
1271 FND_MSG_PUB.add;
1272 RAISE FND_API.G_EXC_ERROR;
1273 END IF;
1274
1275 --Get p_uc_header_id its own attribute from ahl_unit_config_headers_v
1276 OPEN get_uc_header(p_uc_header_id);
1277 FETCH get_uc_header INTO l_uc_header_rec;
1278 IF get_uc_header%NOTFOUND THEN
1279 CLOSE get_uc_header;
1280 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
1281 FND_MESSAGE.set_token('NAME', 'uc_header_id');
1282 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
1283 FND_MSG_PUB.add;
1284 RAISE FND_API.G_EXC_ERROR;
1285 ELSE
1286 CLOSE get_uc_header;
1287 END IF;
1288
1289 UPDATE ahl_unit_config_headers
1290 SET active_end_date = SYSDATE,
1291 parent_uc_header_id = NULL,
1292 --Suitable for both standalone units and sub units
1293 object_version_number = object_version_number + 1,
1294 last_update_date = SYSDATE,
1295 last_updated_by = fnd_global.user_id,
1296 last_update_login = fnd_global.login_id
1297 WHERE unit_config_header_id = p_uc_header_id
1298 AND object_version_number = p_object_version_number;
1299
1300 IF SQL%NOTFOUND THEN
1301 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1302 FND_MSG_PUB.add;
1303 x_return_status := FND_API.G_RET_STS_ERROR;
1304 RAISE FND_API.G_EXC_ERROR;
1305 END IF;
1306
1307 ahl_util_uc_pkg.copy_uc_header_to_history(p_uc_header_id, l_return_status);
1308 --IF history copy failed, then don't raise exception, just add the message tothe message stack
1309 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1310 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1311 FND_MSG_PUB.add;
1312 END IF;
1313
1314 --The following lines are used to expire all the instances and subunits installed in this
1315 --draft UC, the relationship between the subunit with its immediate parent is expired but
1316 --the relationship below the subunit are not expired.
1317 --First, get transaction_type_id.
1318 AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
1319 IF NOT l_return_value THEN
1320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1321 END IF;
1322 --Set the CSI transaction record
1323 l_csi_transaction_rec.source_transaction_date := SYSDATE;
1324 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1325 --This transaction record copy is required for expiring item instance because for some reason
1326 --expiring relationship and expiring item instance cannot share the same transaction record.
1327 l_csi_upd_transaction_rec := l_csi_transaction_rec;
1328
1329 --Get the object_version_number of the csi instance
1330 --This section was moved from after calling CSI expire_relationship to before
1331 --calling expire_relationship because expire_relationhship will increase the object version
1332 --number of CSI instance itself
1333 OPEN get_csi_obj_ver_num(l_uc_header_rec.csi_instance_id);
1334 FETCH get_csi_obj_ver_num INTO l_csi_instance_ovn;
1335 IF (get_csi_obj_ver_num%NOTFOUND) THEN
1336 CLOSE get_csi_obj_ver_num;
1337 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');
1338 FND_MSG_PUB.add;
1339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1340 ELSIF (l_csi_instance_ovn <> p_csi_instance_ovn) THEN
1341 CLOSE get_csi_obj_ver_num;
1342 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
1343 FND_MSG_PUB.add;
1344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1345 END IF;
1346
1347 IF l_uc_header_rec.parent_uc_header_id IS NOT NULL THEN
1348 --Means it is a subunit, need to expire the relationship with its parent (here we can also
1349 --call remove_instance API to do it, but a bit too expensive(deriving status etc.)
1350 OPEN get_csi_ii_relationship(l_uc_header_rec.csi_instance_id);
1351 FETCH get_csi_ii_relationship INTO l_csi_relationship_id, l_object_version_number;
1352 IF get_csi_ii_relationship%NOTFOUND THEN
1353 CLOSE get_csi_ii_relationship;
1354 FND_MESSAGE.set_name('AHL','AHL_UC_CSI_REL_REC_INVALID');
1355 FND_MESSAGE.set_token('INSTANCE', l_uc_header_rec.csi_instance_id);
1356 FND_MSG_PUB.add;
1357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1358 ELSE
1359 CLOSE get_csi_ii_relationship;
1360 END IF;
1361
1362 --Set CSI relationship record
1363 l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
1364 l_csi_relationship_rec.object_version_number := l_object_version_number;
1365 CSI_II_RELATIONSHIPS_PUB.expire_relationship(
1366 p_api_version => 1.0,
1367 p_relationship_rec => l_csi_relationship_rec,
1368 p_txn_rec => l_csi_transaction_rec,
1369 x_instance_id_lst => l_csi_instance_id_lst,
1370 x_return_status => l_return_status,
1371 x_msg_count => l_msg_count,
1372 x_msg_data => l_msg_data);
1373
1374 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1375 l_csi_instance_ovn := l_csi_instance_ovn + 1;
1376 ELSIF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
1377 RAISE FND_API.G_EXC_ERROR;
1378 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1380 END IF;
1381 END IF;
1382
1383 --Call CSI API to expire the instance and all its descendants if exist
1384 l_csi_instance_rec.instance_id := l_uc_header_rec.csi_instance_id;
1385 l_csi_instance_rec.object_version_number := l_csi_instance_ovn;
1386 CSI_ITEM_INSTANCE_PUB.expire_item_instance(
1387 p_api_version => 1.0,
1388 p_instance_rec => l_csi_instance_rec,
1389 p_expire_children => FND_API.G_TRUE,
1390 p_txn_rec => l_csi_upd_transaction_rec,
1391 x_instance_id_lst => l_csi_instance_id_lst,
1392 x_return_status => l_return_status,
1393 x_msg_count => l_msg_count,
1394 x_msg_data => l_msg_data);
1395 --dbms_output.put_line('l_msg_date='||l_msg_data);
1396 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1397 RAISE FND_API.G_EXC_ERROR;
1398 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401
1402 --Then expire all of the subunit headers. Here don't need to worry about the status of subunit.
1403 --because all the Draft units can only have Draft subunits and only separate units are allowed
1404 --to be submitted for approval
1405 FOR l_subunits IN get_all_subunits(l_uc_header_rec.uc_header_id) LOOP
1406 UPDATE ahl_unit_config_headers
1407 SET active_end_date = sysdate,
1408 object_version_number = object_version_number + 1,
1409 last_update_date = sysdate,
1410 last_updated_by = fnd_global.user_id,
1411 last_update_login = fnd_global.login_id
1412 WHERE unit_config_header_id = l_subunits.unit_config_header_id;
1413 --here no object_version_number check
1414
1415 ahl_util_uc_pkg.copy_uc_header_to_history(l_subunits.unit_config_header_id,l_return_status);
1416 --IF history copy failed, then don't raise exception, just add the messageto the message stack
1417 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1418 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1419 FND_MSG_PUB.add;
1420 END IF;
1421 END LOOP;
1422
1423 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1424 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1425 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
1426 END IF;
1427
1428 -- Perform the Commit (if requested)
1429 IF FND_API.to_boolean( p_commit ) THEN
1430 COMMIT;
1431 END IF;
1432 -- Count and Get messages (optional)
1433 FND_MSG_PUB.count_and_get(
1434 p_encoded => FND_API.G_FALSE,
1435 p_count => x_msg_count,
1436 p_data => x_msg_data);
1437
1438 EXCEPTION
1439 WHEN FND_API.G_EXC_ERROR THEN
1440 ROLLBACK TO delete_uc_header;
1441 x_return_status := FND_API.G_RET_STS_ERROR ;
1442 FND_MSG_PUB.count_and_get(
1443 p_encoded => FND_API.G_FALSE,
1444 p_count => x_msg_count,
1445 p_data => x_msg_data);
1446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1447 ROLLBACK TO delete_uc_header;
1448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1449 FND_MSG_PUB.count_and_get(
1450 p_encoded => FND_API.G_FALSE,
1451 p_count => x_msg_count,
1452 p_data => x_msg_data);
1453 WHEN OTHERS THEN
1454 ROLLBACK TO delete_uc_header;
1455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1456 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1457 THEN
1458 FND_MSG_PUB.add_exc_msg(
1459 p_pkg_name => G_PKG_NAME,
1460 p_procedure_name => l_api_name,
1461 p_error_text => SUBSTRB(SQLERRM,1,240));
1462 END IF;
1463 FND_MSG_PUB.count_and_get(
1464 p_encoded => FND_API.G_FALSE,
1465 p_count => x_msg_count,
1466 p_data => x_msg_data);
1467 END delete_uc_header;
1468
1469 END AHL_UC_UNITCONFIG_PVT; -- Package spec
[Home] [Help]