1 PACKAGE BODY AHL_UC_UNITCONFIG_PVT AS
2 /* $Header: AHLVUCXB.pls 120.4.12020000.2 2012/12/07 15:47:07 sareepar 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 --trim added by jrotich for fixing bug#13090588 BEGIN
771 trim(p_x_uc_header_rec.uc_name),
772 --Modification by jrotich for fixing bug#13090588
773 p_x_uc_header_rec.mc_header_id,
774 p_x_uc_header_rec.instance_id,
775 p_x_uc_header_rec.unit_config_status_code,
776 p_x_uc_header_rec.active_start_date,
777 p_x_uc_header_rec.active_end_date,
778 p_x_uc_header_rec.active_uc_status_code,
779 p_x_uc_header_rec.parent_uc_header_id,
780 p_x_uc_header_rec.attribute_category,
781 p_x_uc_header_rec.attribute1,
782 p_x_uc_header_rec.attribute2,
783 p_x_uc_header_rec.attribute3,
784 p_x_uc_header_rec.attribute4,
785 p_x_uc_header_rec.attribute5,
786 p_x_uc_header_rec.attribute6,
787 p_x_uc_header_rec.attribute7,
788 p_x_uc_header_rec.attribute8,
789 p_x_uc_header_rec.attribute9,
790 p_x_uc_header_rec.attribute10,
791 p_x_uc_header_rec.attribute11,
792 p_x_uc_header_rec.attribute12,
793 p_x_uc_header_rec.attribute13,
794 p_x_uc_header_rec.attribute14,
795 p_x_uc_header_rec.attribute15);
796
797 -- Insert into the exactly same record into ahl_unit_config_headers_h
798 ahl_util_uc_pkg.copy_uc_header_to_history(p_x_uc_header_rec.uc_header_id, l_return_status);
799 --IF history copy failed, then don't raise exception, just add the message to the message stack
800 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
801 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
802 FND_MSG_PUB.add;
803 END IF;
804
805 --Need to check if the instance picked from CSI with serial_number has a serial_no_tag, and
806 --if not, we have to derive its value according to mfg_serail_number_flag ('Y'->'INVENTORY',
807 --assuming CSI has the validation to ensure the serial_number exisiting in table
808 --mfg_searil_numbers, otherwise it is 'TEMPORARY'
809 OPEN get_serial_number(p_x_uc_header_rec.instance_id);
810 FETCH get_serial_number INTO l_serial_number, l_mfg_serial_number_flag;
811 IF get_serial_number%NOTFOUND THEN
812 FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
813 FND_MESSAGE.set_token('CSII', p_x_uc_header_rec.instance_id);
814 FND_MSG_PUB.add;
815 RAISE FND_API.G_EXC_ERROR;
816 CLOSE get_serial_number;
817 ELSE
818 CLOSE get_serial_number;
819 END IF;
820
821 IF l_serial_number IS NOT NULL THEN
822 --Retrieve existing value of serial_number_tag if present.
823 AHL_UTIL_UC_PKG.getcsi_attribute_value(p_x_uc_header_rec.instance_id,
824 'AHL_TEMP_SERIAL_NUM',
825 l_attribute_value,
826 l_attribute_value_id,
827 l_object_version_number,
828 l_return_val);
829 IF NOT l_return_val THEN --serial_number_tag doesn't exist
830 IF l_mfg_serial_number_flag = 'Y' THEN
831 l_serial_number_tag := 'INVENTORY';
832 ELSE
833 l_serial_number_tag := 'TEMPORARY';
834 END IF;
835 --Build CSI transaction record, first get transaction_type_id
836 AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE', l_transaction_type_id, l_return_val);
837 IF NOT(l_return_val) THEN
838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
839 END IF;
840
841 l_csi_transaction_rec.source_transaction_date := SYSDATE;
842 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
843 AHL_Util_UC_Pkg.getcsi_attribute_id('AHL_TEMP_SERIAL_NUM', l_attribute_id,l_return_val);
844 IF NOT(l_return_val) THEN
845 FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
846 FND_MESSAGE.set_token('CODE', 'AHL_TEMP_SERIAL_NUM');
847 FND_MSG_PUB.add;
848 ELSE
849 l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
850 l_csi_extend_attrib_rec.attribute_value := l_serial_number_tag;
851 l_csi_extend_attrib_rec.instance_id := p_x_uc_header_rec.instance_id;
852 l_csi_ext_attrib_values_tbl(1) := l_csi_extend_attrib_rec;
853 END IF;
854
855 CSI_ITEM_INSTANCE_PUB.create_extended_attrib_values(
856 p_api_version => 1.0,
857 p_txn_rec => l_csi_transaction_rec,
858 p_ext_attrib_tbl => l_csi_ext_attrib_values_tbl,
859 x_return_status => l_return_status,
860 x_msg_count => l_msg_count,
861 x_msg_data => l_msg_data);
862
863 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
864 RAISE FND_API.G_EXC_ERROR;
865 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867 END IF;
868 END IF;
869 END IF;
870
871 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
872 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
873 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
874 END IF;
875
876 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
877 l_msg_count := FND_MSG_PUB.count_msg;
878 IF l_msg_count > 0 THEN
879 x_msg_count := l_msg_count;
880 RAISE FND_API.G_EXC_ERROR;
881 END IF;
882 -- Perform the Commit (if requested)
883 IF FND_API.to_boolean( p_commit ) THEN
884 COMMIT;
885 END IF;
886 -- Count and Get messages (optional)
887 FND_MSG_PUB.count_and_get(
888 p_encoded => FND_API.G_FALSE,
889 p_count => x_msg_count,
890 p_data => x_msg_data);
891 EXCEPTION
892 WHEN FND_API.G_EXC_ERROR THEN
893 ROLLBACK TO create_uc_header;
894 x_return_status := FND_API.G_RET_STS_ERROR ;
895 FND_MSG_PUB.count_and_get(
896 p_encoded => FND_API.G_FALSE,
897 p_count => x_msg_count,
898 p_data => x_msg_data);
899 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
900 ROLLBACK TO create_uc_header;
901 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
902 FND_MSG_PUB.count_and_get(
903 p_encoded => FND_API.G_FALSE,
904 p_count => x_msg_count,
905 p_data => x_msg_data);
906 WHEN OTHERS THEN
907 ROLLBACK TO create_uc_header;
908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
909 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
910 THEN
911 FND_MSG_PUB.add_exc_msg(
912 p_pkg_name => G_PKG_NAME,
913 p_procedure_name => l_api_name,
914 p_error_text => SUBSTRB(SQLERRM,1,240));
915 END IF;
916 FND_MSG_PUB.count_and_get(
917 p_encoded => FND_API.G_FALSE,
918 p_count => x_msg_count,
919 p_data => x_msg_data);
920 END create_uc_header;
921
922 -- Define Procedure update_uc_header --
923 -- This API is used to update a UC header name or some attributes of the top node
924 -- instance of the UC.
925 PROCEDURE update_uc_header(
926 p_api_version IN NUMBER := 1.0,
927 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
928 p_commit IN VARCHAR2 := FND_API.G_FALSE,
929 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
930 p_module_type IN VARCHAR2 := NULL,
931 x_return_status OUT NOCOPY VARCHAR2,
932 x_msg_count OUT NOCOPY NUMBER,
933 x_msg_data OUT NOCOPY VARCHAR2,
934 p_x_uc_header_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type,
935 p_uc_instance_rec IN ahl_uc_instance_pvt.uc_instance_rec_type)
936 IS
937 l_api_name CONSTANT VARCHAR2(30) := 'update_uc_header';
938 l_api_version CONSTANT NUMBER := 1.0;
939 l_return_status VARCHAR2(1);
940 l_msg_count NUMBER;
941
942 BEGIN
943 -- Initialize API return status to success
944 x_return_status := FND_API.G_RET_STS_SUCCESS;
945
946 -- Standard Start of API savepoint
947 SAVEPOINT update_uc_header;
948
949 -- Standard call to check for call compatibility.
950 IF NOT FND_API.compatible_api_call(
951 l_api_version,
952 p_api_version,
953 l_api_name,
954 G_PKG_NAME)
955 THEN
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957 END IF;
958
959 -- Initialize message list if p_init_msg_list is set to TRUE.
960 IF FND_API.to_boolean( p_init_msg_list ) THEN
961 FND_MSG_PUB.initialize;
962 END IF;
963
964 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
965 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
966 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
967 'At the start of the procedure');
968 END IF;
969
970 -- Convert G_MISS values to NULL and NULL to OLD values
971 convert_attributes(p_x_uc_header_rec, l_return_status);
972 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
973 RAISE FND_API.G_EXC_ERROR;
974 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977
978 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
979 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
980 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
981 ': after convert_attributes');
982 END IF;
983
984 -- ACL :: Changes for R12
985 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
986 FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
987 FND_MSG_PUB.add;
988 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
989 END IF;
990
991 -- Validate all attributes (Item level validation)
992 IF ( p_validation_level = FND_API.G_VALID_LEVEL_FULL ) THEN
993 validate_attributes(p_x_uc_header_rec, 'U', l_return_status);
994 -- If any severe error occurs, then, abort API.
995 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
996 RAISE FND_API.G_EXC_ERROR;
997 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
998 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
999 END IF;
1000 END IF;
1001 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1002 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1003 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1004 ': after validate_attributes');
1005 END IF;
1006
1007 UPDATE ahl_unit_config_headers SET
1008 object_version_number = object_version_number + 1,
1009 last_update_date = sysdate,
1010 last_updated_by = fnd_global.user_id,
1011 last_update_login = fnd_global.login_id,
1012 --trim added by jrotich for fixing bug#13090588 BEGIN
1013 name = trim(p_x_uc_header_rec.uc_name),
1014 --Modification by jrotich for fixing bug#13090588 END
1015 master_config_id = p_x_uc_header_rec.mc_header_id,
1016 csi_item_instance_id = p_x_uc_header_rec.instance_id,
1017 unit_config_status_code = p_x_uc_header_rec.unit_config_status_code,
1018 active_start_date = p_x_uc_header_rec.active_start_date,
1019 active_end_date = p_x_uc_header_rec.active_end_date,
1020 active_uc_status_code = p_x_uc_header_rec.active_uc_status_code,
1021 parent_uc_header_id = p_x_uc_header_rec.parent_uc_header_id,
1022 attribute_category = p_x_uc_header_rec.attribute_category,
1023 attribute1 = p_x_uc_header_rec.attribute1,
1024 attribute2 = p_x_uc_header_rec.attribute2,
1025 attribute3 = p_x_uc_header_rec.attribute3,
1026 attribute4 = p_x_uc_header_rec.attribute4,
1027 attribute5 = p_x_uc_header_rec.attribute5,
1028 attribute6 = p_x_uc_header_rec.attribute6,
1029 attribute7 = p_x_uc_header_rec.attribute7,
1030 attribute8 = p_x_uc_header_rec.attribute8,
1031 attribute9 = p_x_uc_header_rec.attribute9,
1032 attribute10 = p_x_uc_header_rec.attribute10,
1033 attribute11 = p_x_uc_header_rec.attribute11,
1034 attribute12 = p_x_uc_header_rec.attribute12,
1035 attribute13 = p_x_uc_header_rec.attribute13,
1036 attribute14 = p_x_uc_header_rec.attribute14,
1037 attribute15 = p_x_uc_header_rec.attribute15
1038 WHERE unit_config_header_id = p_x_uc_header_rec.uc_header_id
1039 AND object_version_number = p_x_uc_header_rec.object_version_number;
1040 IF SQL%NOTFOUND THEN
1041 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1042 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1043 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','ovn='||p_x_uc_header_rec.object_version_number);
1044 END IF;
1045 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1046 FND_MSG_PUB.add;
1047 x_return_status := FND_API.G_RET_STS_ERROR;
1048 RAISE FND_API.G_EXC_ERROR;
1049 END IF;
1050
1051 ahl_util_uc_pkg.copy_uc_header_to_history(p_x_uc_header_rec.uc_header_id, l_return_status);
1052 --IF history copy failed, then don't raise exception, just add the message to the message stack
1053 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1054 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1055 FND_MSG_PUB.add;
1056 END IF;
1057
1058 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1059 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1060 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','before calling update_instance_attr');
1061 END IF;
1062
1063 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1065 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','x_return_status='||x_return_status);
1066 END IF;
1067 --Call ahl_uc_instance_pvt.update_instance_attr to update the top node instance attributes
1068 --In front end UI, if there is no change to the instance attributes, then the whole p_uc_instance_rec
1069 --will be null.
1070 IF p_uc_instance_rec.instance_id IS NOT NULL THEN
1071 ahl_uc_instance_pvt.update_instance_attr(
1072 p_api_version => 1.0,
1073 p_init_msg_list => FND_API.G_FALSE,
1074 p_commit => FND_API.G_FALSE,
1075 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1076 x_return_status => x_return_status,
1077 x_msg_count => x_msg_count,
1078 x_msg_data => x_msg_data,
1079 p_uc_header_id => p_x_uc_header_rec.uc_header_id,
1080 p_uc_instance_rec => p_uc_instance_rec,
1081 p_prod_user_flag => 'N');
1082
1083 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1084 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1085 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'After calling update_instance_attr');
1086 END IF;
1087
1088 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1089 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1090 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API','x_return_status='||x_return_status);
1091 END IF;
1092 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1093 RAISE FND_API.G_EXC_ERROR;
1094 END IF;
1095 END IF;
1096
1097 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1098 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1099 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'Before check message count');
1100 END IF;
1101
1102 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1103 l_msg_count := FND_MSG_PUB.count_msg;
1104 IF l_msg_count > 0 THEN
1105 x_msg_count := l_msg_count;
1106 RAISE FND_API.G_EXC_ERROR;
1107 END IF;
1108
1109 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1110 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1111 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'Before commit');
1112 END IF;
1113
1114 -- Perform the Commit (if requested)
1115 IF FND_API.to_boolean( p_commit ) THEN
1116 COMMIT;
1117 END IF;
1118
1119 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1120 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1121 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': within API', 'After commit');
1122 END IF;
1123
1124 -- Count and Get messages (optional)
1125 FND_MSG_PUB.count_and_get(
1126 p_encoded => FND_API.G_FALSE,
1127 p_count => x_msg_count,
1128 p_data => x_msg_data);
1129
1130 EXCEPTION
1131 WHEN FND_API.G_EXC_ERROR THEN
1132 ROLLBACK TO update_uc_header;
1133 x_return_status := FND_API.G_RET_STS_ERROR ;
1134 FND_MSG_PUB.count_and_get(
1135 p_encoded => FND_API.G_FALSE,
1136 p_count => x_msg_count,
1137 p_data => x_msg_data);
1138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1139 ROLLBACK TO update_uc_header;
1140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1141 FND_MSG_PUB.count_and_get(
1142 p_encoded => FND_API.G_FALSE,
1143 p_count => x_msg_count,
1144 p_data => x_msg_data);
1145 WHEN OTHERS THEN
1146 ROLLBACK TO update_uc_header;
1147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1148 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1149 THEN
1150 FND_MSG_PUB.add_exc_msg(
1151 p_pkg_name => G_PKG_NAME,
1152 p_procedure_name => l_api_name,
1153 p_error_text => SUBSTRB(SQLERRM,1,240));
1154 END IF;
1155 FND_MSG_PUB.count_and_get(
1156 p_encoded => FND_API.G_FALSE,
1157 p_count => x_msg_count,
1158 p_data => x_msg_data);
1159 END update_uc_header;
1160
1161 -- Define Procedure delete_uc_header --
1162
1163 PROCEDURE delete_uc_header(
1164 p_api_version IN NUMBER := 1.0,
1165 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1166 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1167 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1168 x_return_status OUT NOCOPY VARCHAR2,
1169 x_msg_count OUT NOCOPY NUMBER,
1170 x_msg_data OUT NOCOPY VARCHAR2,
1171 p_uc_header_id IN NUMBER,
1172 p_object_version_number IN NUMBER,
1173 p_csi_instance_ovn IN NUMBER)
1174 IS
1175 l_api_name CONSTANT VARCHAR2(30) := 'delete_uc_header';
1176 l_api_version CONSTANT NUMBER := 1.0;
1177 l_return_status VARCHAR2(1);
1178 l_msg_count NUMBER;
1179 l_msg_data VARCHAR2(2000);
1180 l_uc_header_rec get_uc_header%ROWTYPE;
1181 l_uc_status ahl_unit_config_headers.unit_config_status_code%TYPE;
1182 l_csi_transaction_rec csi_datastructures_pub.transaction_rec;
1183 l_csi_upd_transaction_rec csi_datastructures_pub.transaction_rec;
1184 l_csi_instance_rec csi_datastructures_pub.instance_rec;
1185 l_csi_relationship_rec csi_datastructures_pub.ii_relationship_rec;
1186 l_csi_instance_id_lst csi_datastructures_pub.id_tbl;
1187 l_csi_relationship_id NUMBER;
1188 l_transaction_type_id NUMBER;
1189 l_return_value BOOLEAN;
1190 l_csi_instance_ovn NUMBER;
1191 l_dummy_num NUMBER;
1192 l_object_version_number NUMBER;
1193
1194 CURSOR get_csi_obj_ver_num(c_instance_id NUMBER) IS
1195 SELECT object_version_number
1196 FROM csi_item_instances
1197 WHERE instance_id = c_instance_id
1198 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1199 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1200 CURSOR get_all_subunits(c_uc_header_id NUMBER) IS
1201 SELECT parent_uc_header_id, unit_config_header_id
1202 FROM ahl_unit_config_headers
1203 START WITH unit_config_header_id = c_uc_header_id
1204 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1205 CONNECT BY parent_uc_header_id = PRIOR unit_config_header_id
1206 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1207 FOR UPDATE OF unit_config_header_id;
1208 CURSOR get_top_unit(c_uc_header_id NUMBER) IS
1209 SELECT unit_config_header_id, unit_config_status_code
1210 FROM ahl_unit_config_headers
1211 WHERE parent_uc_header_id IS NULL
1212 START WITH unit_config_header_id = c_uc_header_id
1213 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1214 CONNECT BY unit_config_header_id = PRIOR parent_uc_header_id
1215 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1216
1217 CURSOR get_csi_ii_relationship(c_instance_id NUMBER) IS
1218 SELECT relationship_id,
1219 object_version_number
1220 FROM csi_ii_relationships
1221 WHERE subject_id = c_instance_id
1222 AND relationship_type_code='COMPONENT-OF'
1223 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
1224 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1225 BEGIN
1226 -- Initialize API return status to success
1227 x_return_status := FND_API.G_RET_STS_SUCCESS;
1228
1229 -- Standard Start of API savepoint
1230 SAVEPOINT delete_uc_header;
1231
1232 -- Standard call to check for call compatibility.
1233 IF NOT FND_API.compatible_api_call(
1234 l_api_version,
1235 p_api_version,
1236 l_api_name,
1237 G_PKG_NAME)
1238 THEN
1239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1240 END IF;
1241
1242 -- Initialize message list if p_init_msg_list is set to TRUE.
1243 IF FND_API.to_boolean( p_init_msg_list ) THEN
1244 FND_MSG_PUB.initialize;
1245 END IF;
1246
1247 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1248 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1249 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1250 'At the start of the procedure');
1251 END IF;
1252
1253 IF (p_uc_header_id IS NULL OR p_uc_header_id = FND_API.G_MISS_NUM OR
1254 p_object_version_number IS NULL OR p_object_version_number = FND_API.G_MISS_NUM OR
1255 p_csi_instance_ovn IS NULL OR p_csi_instance_ovn = FND_API.G_MISS_NUM ) THEN
1256 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
1257 FND_MSG_PUB.add;
1258 RAISE FND_API.G_EXC_ERROR;
1259 END IF;
1260
1261 --In case p_uc_header_id is a subunit, then whether it can be expired depends on its ancestor
1262 --uc_header_id's status
1263 OPEN get_top_unit(p_uc_header_id);
1264 FETCH get_top_unit INTO l_dummy_num, l_uc_status;
1265 IF get_top_unit%NOTFOUND THEN
1266 CLOSE get_top_unit;
1267 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
1268 FND_MESSAGE.set_token('NAME', 'uc_header_id');
1269 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
1270 FND_MSG_PUB.add;
1271 RAISE FND_API.G_EXC_ERROR;
1272 ELSIF (l_uc_status IS NULL OR l_uc_status NOT IN ('DRAFT', 'APPROVAL_REJECTED')) THEN
1273 CLOSE get_top_unit;
1274 FND_MESSAGE.set_name( 'AHL', 'AHL_UC_STATUS_NOT_DRAFT' );
1275 FND_MSG_PUB.add;
1276 RAISE FND_API.G_EXC_ERROR;
1277 END IF;
1278
1279 --Get p_uc_header_id its own attribute from ahl_unit_config_headers_v
1280 OPEN get_uc_header(p_uc_header_id);
1281 FETCH get_uc_header INTO l_uc_header_rec;
1282 IF get_uc_header%NOTFOUND THEN
1283 CLOSE get_uc_header;
1284 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
1285 FND_MESSAGE.set_token('NAME', 'uc_header_id');
1286 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
1287 FND_MSG_PUB.add;
1288 RAISE FND_API.G_EXC_ERROR;
1289 ELSE
1290 CLOSE get_uc_header;
1291 END IF;
1292
1293 UPDATE ahl_unit_config_headers
1294 SET active_end_date = SYSDATE,
1295 parent_uc_header_id = NULL,
1296 --Suitable for both standalone units and sub units
1297 object_version_number = object_version_number + 1,
1298 last_update_date = SYSDATE,
1299 last_updated_by = fnd_global.user_id,
1300 last_update_login = fnd_global.login_id
1301 WHERE unit_config_header_id = p_uc_header_id
1302 AND object_version_number = p_object_version_number;
1303
1304 IF SQL%NOTFOUND THEN
1305 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1306 FND_MSG_PUB.add;
1307 x_return_status := FND_API.G_RET_STS_ERROR;
1308 RAISE FND_API.G_EXC_ERROR;
1309 END IF;
1310
1311 ahl_util_uc_pkg.copy_uc_header_to_history(p_uc_header_id, l_return_status);
1312 --IF history copy failed, then don't raise exception, just add the message tothe message stack
1313 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1314 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1315 FND_MSG_PUB.add;
1316 END IF;
1317
1318 --The following lines are used to expire all the instances and subunits installed in this
1319 --draft UC, the relationship between the subunit with its immediate parent is expired but
1320 --the relationship below the subunit are not expired.
1321 --First, get transaction_type_id.
1322 AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
1323 IF NOT l_return_value THEN
1324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1325 END IF;
1326 --Set the CSI transaction record
1327 l_csi_transaction_rec.source_transaction_date := SYSDATE;
1328 l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
1329 --This transaction record copy is required for expiring item instance because for some reason
1330 --expiring relationship and expiring item instance cannot share the same transaction record.
1331 l_csi_upd_transaction_rec := l_csi_transaction_rec;
1332
1333 --Get the object_version_number of the csi instance
1334 --This section was moved from after calling CSI expire_relationship to before
1335 --calling expire_relationship because expire_relationhship will increase the object version
1336 --number of CSI instance itself
1337 OPEN get_csi_obj_ver_num(l_uc_header_rec.csi_instance_id);
1338 FETCH get_csi_obj_ver_num INTO l_csi_instance_ovn;
1339 IF (get_csi_obj_ver_num%NOTFOUND) THEN
1340 CLOSE get_csi_obj_ver_num;
1341 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');
1342 FND_MSG_PUB.add;
1343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1344 ELSIF (l_csi_instance_ovn <> p_csi_instance_ovn) THEN
1345 CLOSE get_csi_obj_ver_num;
1346 FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
1347 FND_MSG_PUB.add;
1348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349 END IF;
1350
1351 IF l_uc_header_rec.parent_uc_header_id IS NOT NULL THEN
1352 --Means it is a subunit, need to expire the relationship with its parent (here we can also
1353 --call remove_instance API to do it, but a bit too expensive(deriving status etc.)
1354 OPEN get_csi_ii_relationship(l_uc_header_rec.csi_instance_id);
1355 FETCH get_csi_ii_relationship INTO l_csi_relationship_id, l_object_version_number;
1356 IF get_csi_ii_relationship%NOTFOUND THEN
1357 CLOSE get_csi_ii_relationship;
1358 FND_MESSAGE.set_name('AHL','AHL_UC_CSI_REL_REC_INVALID');
1359 FND_MESSAGE.set_token('INSTANCE', l_uc_header_rec.csi_instance_id);
1360 FND_MSG_PUB.add;
1361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1362 ELSE
1363 CLOSE get_csi_ii_relationship;
1364 END IF;
1365
1366 --Set CSI relationship record
1367 l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
1368 l_csi_relationship_rec.object_version_number := l_object_version_number;
1369 CSI_II_RELATIONSHIPS_PUB.expire_relationship(
1370 p_api_version => 1.0,
1371 p_relationship_rec => l_csi_relationship_rec,
1372 p_txn_rec => l_csi_transaction_rec,
1373 x_instance_id_lst => l_csi_instance_id_lst,
1374 x_return_status => l_return_status,
1375 x_msg_count => l_msg_count,
1376 x_msg_data => l_msg_data);
1377
1378 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1379 l_csi_instance_ovn := l_csi_instance_ovn + 1;
1380 ELSIF(l_return_status = FND_API.G_RET_STS_ERROR) THEN
1381 RAISE FND_API.G_EXC_ERROR;
1382 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 END IF;
1385 END IF;
1386
1387 --Call CSI API to expire the instance and all its descendants if exist
1388 l_csi_instance_rec.instance_id := l_uc_header_rec.csi_instance_id;
1389 l_csi_instance_rec.object_version_number := l_csi_instance_ovn;
1390 CSI_ITEM_INSTANCE_PUB.expire_item_instance(
1391 p_api_version => 1.0,
1392 p_instance_rec => l_csi_instance_rec,
1393 p_expire_children => FND_API.G_TRUE,
1394 p_txn_rec => l_csi_upd_transaction_rec,
1395 x_instance_id_lst => l_csi_instance_id_lst,
1396 x_return_status => l_return_status,
1397 x_msg_count => l_msg_count,
1398 x_msg_data => l_msg_data);
1399 --dbms_output.put_line('l_msg_date='||l_msg_data);
1400 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1401 RAISE FND_API.G_EXC_ERROR;
1402 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1403 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1404 END IF;
1405
1406 --Then expire all of the subunit headers. Here don't need to worry about the status of subunit.
1407 --because all the Draft units can only have Draft subunits and only separate units are allowed
1408 --to be submitted for approval
1409 FOR l_subunits IN get_all_subunits(l_uc_header_rec.uc_header_id) LOOP
1410 UPDATE ahl_unit_config_headers
1411 SET active_end_date = sysdate,
1412 object_version_number = object_version_number + 1,
1413 last_update_date = sysdate,
1414 last_updated_by = fnd_global.user_id,
1415 last_update_login = fnd_global.login_id
1416 WHERE unit_config_header_id = l_subunits.unit_config_header_id;
1417 --here no object_version_number check
1418
1419 ahl_util_uc_pkg.copy_uc_header_to_history(l_subunits.unit_config_header_id,l_return_status);
1420 --IF history copy failed, then don't raise exception, just add the messageto the message stack
1421 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1422 FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1423 FND_MSG_PUB.add;
1424 END IF;
1425 END LOOP;
1426
1427 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1428 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1429 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
1430 END IF;
1431
1432 -- Perform the Commit (if requested)
1433 IF FND_API.to_boolean( p_commit ) THEN
1434 COMMIT;
1435 END IF;
1436 -- Count and Get messages (optional)
1437 FND_MSG_PUB.count_and_get(
1438 p_encoded => FND_API.G_FALSE,
1439 p_count => x_msg_count,
1440 p_data => x_msg_data);
1441
1442 EXCEPTION
1443 WHEN FND_API.G_EXC_ERROR THEN
1444 ROLLBACK TO delete_uc_header;
1445 x_return_status := FND_API.G_RET_STS_ERROR ;
1446 FND_MSG_PUB.count_and_get(
1447 p_encoded => FND_API.G_FALSE,
1448 p_count => x_msg_count,
1449 p_data => x_msg_data);
1450 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1451 ROLLBACK TO delete_uc_header;
1452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1453 FND_MSG_PUB.count_and_get(
1454 p_encoded => FND_API.G_FALSE,
1455 p_count => x_msg_count,
1456 p_data => x_msg_data);
1457 WHEN OTHERS THEN
1458 ROLLBACK TO delete_uc_header;
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1460 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1461 THEN
1462 FND_MSG_PUB.add_exc_msg(
1463 p_pkg_name => G_PKG_NAME,
1464 p_procedure_name => l_api_name,
1465 p_error_text => SUBSTRB(SQLERRM,1,240));
1466 END IF;
1467 FND_MSG_PUB.count_and_get(
1468 p_encoded => FND_API.G_FALSE,
1469 p_count => x_msg_count,
1470 p_data => x_msg_data);
1471 END delete_uc_header;
1472
1473 END AHL_UC_UNITCONFIG_PVT; -- Package spec
[Home] [Help]