[Home] [Help]
PACKAGE BODY: APPS.AHL_UMP_UF_PVT
Source
1 PACKAGE BODY AHL_UMP_UF_PVT AS
2 /* $Header: AHLVUMFB.pls 120.2 2008/02/11 00:35:06 sracha ship $ */
3
4 --G_DEBUG varchar2(1) := FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UMP_UF_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
8
9
10 ------------------------------
11 -- Declare Local Procedures --
12 ------------------------------
13 PROCEDURE process_uf_header(
14 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
15 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_Rec_type
16 );
17
18 PROCEDURE convert_uf_header_val_to_id(
19 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
20 );
21
22 PROCEDURE convert_unit_header_val_to_id(
23 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
24 );
25
26 PROCEDURE convert_part_header_val_to_id(
27 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
28 );
29
30 PROCEDURE convert_node_header_val_to_id(
31 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
32 );
33
34 PROCEDURE validate_uf_header(
35 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type
36 );
37 PROCEDURE validate_uf_header_pm(
38 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type
39 );
40
41 PROCEDURE default_unchanged_uf_header(
42 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_Rec_type
43 );
44
45 PROCEDURE process_uf_details(
46 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
47 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type,
48 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
49 );
50
51
52 PROCEDURE validate_uf_details(
53 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type,
54 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
55 );
56
57 PROCEDURE default_unchanged_uf_details(
58 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
59 );
60
61 PROCEDURE validate_utilization_forecast(
62 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_rec_type,
63 x_uf_details_tbl OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
64 );
65
66 PROCEDURE post_process_uf_header(
67 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type
68 );
69
70 ------------------------
71 -- Declare Procedures --
72 ------------------------
73
74 -- Start of Comments --
75 -- Procedure name : process_utilization_forecast
76 -- Type : Public
77 -- Function : For a given set of utilization forecast header and details, will validate and insert/update
78 -- the utilization forecast information.
79 -- Pre-reqs :
80 -- Parameters :
81 --
82 -- Standard IN Parameters :
83 -- p_api_version IN NUMBER Default 1.0
84 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_TRUE
85 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
86 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
87 -- Standard OUT Parameters :
88 -- x_return_status OUT VARCHAR2 Required
89 -- x_msg_count OUT NUMBER Required
90 -- x_msg_data OUT VARCHAR2 Required
91 --
92 -- p_module_type IN VARCHAR2 Required.
93 --
94 -- This parameter indicates the front-end form interface. The default value is 'JSP'. If the value
95 -- is JSP, then this API clears out all id columns and validations are done using the values based
96 -- on which the Id's are populated.
97 --
98 -- process_utilization_forecast Parameters:
99 --
100 -- p_x_uf_header_rec IN OUT AHL_UMP_UF_PVT.uf_header_rec_type Required
101 -- Utilization Forecast Header Details
102 -- p_x_uf_detail_tbl IN OUT AHL_UMP_UF_PVT.uf_detail_tbl_type Required
103 -- Utilization Forecast details
104 --
105 --
106 -- Version :
107 -- Initial Version 1.0
108 --
109 -- End of Comments.
110
111 PROCEDURE process_utilization_forecast (
112 p_api_version IN NUMBER := 1.0,
113 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
114 p_commit IN VARCHAR2 := FND_API.G_FALSE,
115 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
116 p_module_type IN VARCHAR2 := NULL,
117 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type,
118 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2) IS
122
123 l_api_version CONSTANT NUMBER := 1.0;
124 l_api_name CONSTANT VARCHAR2(30) := 'process_utilization_forecast';
125 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
126
127 BEGIN
128
129 -- Standard start of API savepoint
130 SAVEPOINT process_uf_pvt;
131
132 -- Standard call to check for call compatibility
133 IF NOT FND_API.Compatible_API_Call( l_api_version, p_api_version,l_api_name, G_PKG_NAME )
134 THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 -- Initialize message list if p_init_msg_list is set to TRUE
139 IF FND_API.To_Boolean( p_init_msg_list)
140 THEN
141 FND_MSG_PUB.Initialize;
142 END IF;
143
144 -- Initialize API return status to success
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146
147 -- Enable Debug.
148 IF G_DEBUG = 'Y' THEN
149 AHL_DEBUG_PUB.enable_debug;
150 END IF;
151
152 IF G_DEBUG = 'Y' THEN
153 AHL_DEBUG_PUB.debug('Started processing UF');
154 END IF;
155 -------
156 process_uf_header(
157 p_validation_level => p_validation_level,
158 p_x_uf_header_rec => p_x_uf_header_rec
159 );
160 -- Check Error Message stack.
161 x_msg_count := FND_MSG_PUB.count_msg;
162 IF x_msg_count > 0 THEN
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 -- PROCESS UF details
167 IF (p_x_uf_details_tbl.count > 0) THEN
168 process_uf_details(
169 p_validation_level => p_validation_level,
170 p_uf_header_rec => p_x_uf_header_rec,
171 p_x_uf_details_tbl => p_x_uf_details_tbl
172 );
173 END IF;
174
175 -- Check Error Message stack.
176 x_msg_count := FND_MSG_PUB.count_msg;
177 IF x_msg_count > 0 THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181
182 -- post processing to delete header if use unit forecast is 'N' and there are no uf_details.
183 post_process_uf_header(p_uf_header_rec => p_x_uf_header_rec);
184
185 -- Check Error Message stack.
186 x_msg_count := FND_MSG_PUB.count_msg;
187 IF x_msg_count > 0 THEN
188 RAISE FND_API.G_EXC_ERROR;
189 END IF;
190
191 -- Standard check of p_commit
192 IF FND_API.TO_BOOLEAN(p_commit) THEN
193 COMMIT WORK;
194 END IF;
195
196 -- Standard call to get message count and if count is 1, get message info
197 FND_MSG_PUB.Count_And_Get
198 ( p_count => x_msg_count,
199 p_data => x_msg_data,
200 p_encoded => fnd_api.g_false
201 );
202
203 IF G_DEBUG = 'Y' THEN
204 AHL_DEBUG_PUB.debug('Successfully ending processing UF');
205 END IF;
206
207 -- Disable debug
208 IF G_DEBUG = 'Y' THEN
209 AHL_DEBUG_PUB.disable_debug;
210 END IF;
211
212 EXCEPTION
213 WHEN FND_API.G_EXC_ERROR THEN
214 Rollback to process_uf_pvt;
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
217 p_data => x_msg_data,
218 p_encoded => fnd_api.g_false);
219 -- Disable debug
220 IF G_DEBUG = 'Y' THEN
221 AHL_DEBUG_PUB.disable_debug;
222 END IF;
223
224
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 Rollback to process_uf_pvt;
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
229 p_data => x_msg_data,
230 p_encoded => fnd_api.g_false);
231 -- Disable debug
232 IF G_DEBUG = 'Y' THEN
233 AHL_DEBUG_PUB.disable_debug;
234 END IF;
235
236 WHEN OTHERS THEN
237 Rollback to process_uf_pvt;
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
239 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
240 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
241 p_procedure_name => l_api_name,
242 p_error_text => SUBSTR(SQLERRM,1,500));
243 END IF;
244 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
245 p_data => x_msg_data,
246 p_encoded => fnd_api.g_false);
247 -- Disable debug
248 IF G_DEBUG = 'Y' THEN
249 AHL_DEBUG_PUB.disable_debug;
250 END IF;
251
252
253 END process_utilization_forecast;
254
255 ----------------------------------------------------------
256 -- This procedure processes uf_header_rec
257 ----------------------------------------------------------
258 PROCEDURE process_uf_header(
259 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
260 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_Rec_type
261 ) IS
262 BEGIN
263 -- Convert values to ID's for header rec
264 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
265 THEN
266 convert_uf_header_val_to_id(p_x_uf_header_rec => p_x_uf_header_rec);
267 END IF;
268
269 IF (AHL_UTIL_PKG.is_pm_installed() = 'N')
270 THEN
271 validate_uf_header(p_uf_header_rec => p_x_uf_header_rec);
272 ELSE
273 validate_uf_header_pm(p_uf_header_rec => p_x_uf_header_rec);
274 END IF;
275 default_unchanged_uf_header(p_x_uf_header_rec => p_x_uf_header_rec);
276
277 --save uf header now
278 IF(p_x_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE) THEN
279 --setting object version number for create
280 p_x_uf_header_rec.object_version_number := 1;
281 --setting up user/create/update information
282 p_x_uf_header_rec.created_by := fnd_global.user_id;
283 p_x_uf_header_rec.creation_date := SYSDATE;
284 p_x_uf_header_rec.last_updated_by := fnd_global.user_id;
285 p_x_uf_header_rec.last_update_date := SYSDATE;
286 p_x_uf_header_rec.last_update_login := fnd_global.user_id;
287
288 AHL_UF_HEADERS_PKG.insert_row(
289 x_uf_header_id => p_x_uf_header_rec.uf_header_id,
290 x_object_version_number => p_x_uf_header_rec.object_version_number,
291 x_created_by => p_x_uf_header_rec.created_by,
292 x_creation_date => p_x_uf_header_rec.creation_date,
293 x_last_updated_by => p_x_uf_header_rec.last_updated_by,
294 x_last_update_date => p_x_uf_header_rec.last_update_date,
295 x_last_update_login => p_x_uf_header_rec.last_update_login,
296 x_unit_config_header_id => p_x_uf_header_rec.unit_config_header_id,
297 x_pc_node_id => p_x_uf_header_rec.pc_node_id,
298 x_inventory_item_id => p_x_uf_header_rec.inventory_item_id,
299 x_inventory_org_id => p_x_uf_header_rec.inventory_org_id,
300 x_csi_item_instance_id => p_x_uf_header_rec.csi_item_instance_id,
301 x_use_unit_flag => p_x_uf_header_rec.use_unit_flag,
302 x_attribute_category => p_x_uf_header_rec.attribute_category,
303 x_attribute1 => p_x_uf_header_rec.attribute1,
304 x_attribute2 => p_x_uf_header_rec.attribute2,
305 x_attribute3 => p_x_uf_header_rec.attribute3,
306 x_attribute4 => p_x_uf_header_rec.attribute4,
307 x_attribute5 => p_x_uf_header_rec.attribute5,
308 x_attribute6 => p_x_uf_header_rec.attribute6,
309 x_attribute7 => p_x_uf_header_rec.attribute7,
310 x_attribute8 => p_x_uf_header_rec.attribute8,
311 x_attribute9 => p_x_uf_header_rec.attribute9,
312 x_attribute10 => p_x_uf_header_rec.attribute10,
313 x_attribute11 => p_x_uf_header_rec.attribute11,
314 x_attribute12 => p_x_uf_header_rec.attribute12,
315 x_attribute13 => p_x_uf_header_rec.attribute13,
316 x_attribute14 => p_x_uf_header_rec.attribute14,
317 x_attribute15 => p_x_uf_header_rec.attribute15
318 );
319 ELSIF (p_x_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE) THEN
320
321 -- setting up object version number
322 p_x_uf_header_rec.object_version_number := p_x_uf_header_rec.object_version_number + 1;
323 --setting up user/create/update information
324 p_x_uf_header_rec.last_updated_by := fnd_global.user_id;
325 p_x_uf_header_rec.last_update_date := SYSDATE;
326 p_x_uf_header_rec.last_update_login := fnd_global.user_id;
327
328 AHL_UF_HEADERS_PKG.update_row(
329 x_uf_header_id => p_x_uf_header_rec.uf_header_id,
330 x_object_version_number => p_x_uf_header_rec.object_version_number,
331 x_last_updated_by => p_x_uf_header_rec.last_updated_by,
332 x_last_update_date => p_x_uf_header_rec.last_update_date,
333 x_last_update_login => p_x_uf_header_rec.last_update_login,
334 x_unit_config_header_id => p_x_uf_header_rec.unit_config_header_id,
335 x_pc_node_id => p_x_uf_header_rec.pc_node_id,
336 x_inventory_item_id => p_x_uf_header_rec.inventory_item_id,
337 x_inventory_org_id => p_x_uf_header_rec.inventory_org_id,
338 x_csi_item_instance_id => p_x_uf_header_rec.csi_item_instance_id,
339 x_use_unit_flag => p_x_uf_header_rec.use_unit_flag,
340 x_attribute_category => p_x_uf_header_rec.attribute_category,
341 x_attribute1 => p_x_uf_header_rec.attribute1,
342 x_attribute2 => p_x_uf_header_rec.attribute2,
343 x_attribute3 => p_x_uf_header_rec.attribute3,
344 x_attribute4 => p_x_uf_header_rec.attribute4,
345 x_attribute5 => p_x_uf_header_rec.attribute5,
346 x_attribute6 => p_x_uf_header_rec.attribute6,
347 x_attribute7 => p_x_uf_header_rec.attribute7,
348 x_attribute8 => p_x_uf_header_rec.attribute8,
349 x_attribute9 => p_x_uf_header_rec.attribute9,
350 x_attribute10 => p_x_uf_header_rec.attribute10,
351 x_attribute11 => p_x_uf_header_rec.attribute11,
352 x_attribute12 => p_x_uf_header_rec.attribute12,
353 x_attribute13 => p_x_uf_header_rec.attribute13,
354 x_attribute14 => p_x_uf_header_rec.attribute14,
355 x_attribute15 => p_x_uf_header_rec.attribute15
356 );
357 END IF;
358
359
360 END process_uf_header;
361
362 ----------------------------------------------------------
363 -- This procedure converts values to ids for uf_header_rec
364 ----------------------------------------------------------
365 PROCEDURE convert_uf_header_val_to_id(
366 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
367 ) IS
368
369 BEGIN
370 -- conversion between uf_header_id : unit_config_header_id, inventory_item_id, pc_node_id
371
372 IF(p_x_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_UNIT)THEN
373 convert_unit_header_val_to_id(p_x_uf_header_rec);
374 ELSIF (p_x_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PART)THEN
375 convert_part_header_val_to_id(p_x_uf_header_rec);
376 ELSIF (p_x_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PC_NODE)THEN
377 convert_node_header_val_to_id(p_x_uf_header_rec);
378 END IF;
379
380 IF FND_MSG_PUB.count_msg > 0 THEN
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END IF;
383
384 END convert_uf_header_val_to_id;
385
386 ----------------------------------------------------------------------------------------
387 -- This procedure converts values to ids for uf_header_rec for unit utilization forecast
388 ----------------------------------------------------------------------------------------
389 PROCEDURE convert_unit_header_val_to_id(
390 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
391 )IS
392
393 l_uf_header_id NUMBER;
394 l_unit_config_header_id NUMBER;
395
396 CURSOR unit_config_header_id_csr(p_unit_name IN VARCHAR2) IS
397 SELECT unit_config_header_id
398 FROM ahl_unit_config_headers
399 WHERE name = p_unit_name;
400
401 CURSOR uf_header_id_uid_csr(p_unit_config_header_id IN NUMBER) IS
402 SELECT uf_header_id
403 FROM ahl_uf_headers
404 WHERE unit_config_header_id = p_unit_config_header_id;
405
406 BEGIN
407
408 IF(p_x_uf_header_rec.uf_header_id IS NULL) THEN
409 IF(p_x_uf_header_rec.unit_config_header_id IS NULL AND p_x_uf_header_rec.unit_name IS NULL) THEN
410 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFHID_UCHID_NLL');
411 FND_MSG_PUB.ADD;
412 ELSIF (p_x_uf_header_rec.unit_config_header_id IS NULL AND p_x_uf_header_rec.unit_name IS NOT NULL) THEN
413 OPEN unit_config_header_id_csr(p_x_uf_header_rec.unit_name);
414 FETCH unit_config_header_id_csr INTO l_unit_config_header_id;
415 IF(unit_config_header_id_csr%NOTFOUND) THEN
416 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_INV');
417 FND_MSG_PUB.ADD;
418 ELSE
419 p_x_uf_header_rec.unit_config_header_id := l_unit_config_header_id;
420 END IF;
421 CLOSE unit_config_header_id_csr;
422 END IF;
423 --fetching uf_header_id based on unit_config_header_id
424 IF (p_x_uf_header_rec.unit_config_header_id IS NOT NULL AND
425 p_x_uf_header_rec.operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE) THEN
426 OPEN uf_header_id_uid_csr(p_x_uf_header_rec.unit_config_header_id);
427 FETCH uf_header_id_uid_csr INTO l_uf_header_id;
428 IF(uf_header_id_uid_csr%NOTFOUND)THEN
429 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HREC_NOTINDB');
430 FND_MSG_PUB.ADD;
431 ELSE
432 p_x_uf_header_rec.uf_header_id := l_uf_header_id;
433 END IF;
434 CLOSE uf_header_id_uid_csr;
435 END IF;
436 END IF;
437
438 END convert_unit_header_val_to_id;
439
440 ----------------------------------------------------------------------------------------
441 -- This procedure converts values to ids for uf_header_rec for part/item utilization forecast
442 ----------------------------------------------------------------------------------------
443 PROCEDURE convert_part_header_val_to_id(
444 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
445 )IS
446
447 l_uf_header_id NUMBER;
448 l_inventory_item_id NUMBER;
449
450 CURSOR inventory_item_id_csr(p_inventory_item_name IN VARCHAR2) IS
451 SELECT inventory_item_id
452 FROM mtl_system_items_kfv
453 WHERE concatenated_segments = p_inventory_item_name;
454
455
456 CURSOR uf_header_id_pid_csr(p_inventory_item_id IN NUMBER) IS
457 SELECT uf_header_id
458 FROM ahl_uf_headers
459 WHERE inventory_item_id = p_inventory_item_id;
460
461
462 BEGIN
463 IF(p_x_uf_header_rec.uf_header_id IS NULL) THEN
464 IF(p_x_uf_header_rec.inventory_item_id IS NULL AND p_x_uf_header_rec.inventory_item_name IS NULL) THEN
465 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INVID_NLL');
466 FND_MSG_PUB.ADD;
467 ELSIF (p_x_uf_header_rec.inventory_item_id IS NULL AND p_x_uf_header_rec.inventory_item_name IS NOT NULL) THEN
468 OPEN inventory_item_id_csr(p_x_uf_header_rec.inventory_item_name);
469 FETCH inventory_item_id_csr INTO l_inventory_item_id;
470 IF(inventory_item_id_csr%NOTFOUND) THEN
471 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_INV');
472 FND_MSG_PUB.ADD;
473 ELSE
474 p_x_uf_header_rec.inventory_item_id := l_inventory_item_id;
475 END IF;
476 CLOSE inventory_item_id_csr;
477 END IF;
478 --fetching uf_header_id based on inventory_item_id
479 IF (p_x_uf_header_rec.inventory_item_id IS NOT NULL AND
480 p_x_uf_header_rec.operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE) THEN
481 OPEN uf_header_id_pid_csr(p_x_uf_header_rec.inventory_item_id);
482 FETCH uf_header_id_pid_csr INTO l_uf_header_id;
483 IF(uf_header_id_pid_csr%NOTFOUND)THEN
484 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HREC_NOTINDB');
485 FND_MSG_PUB.ADD;
486 ELSE
487 p_x_uf_header_rec.uf_header_id := l_uf_header_id;
488 END IF;
489 CLOSE uf_header_id_pid_csr;
490 END IF;
491 END IF;
492 END convert_part_header_val_to_id;
493
494 ----------------------------------------------------------------------------------------
495 -- This procedure converts values to ids for uf_header_rec for pc node utilization forecast
496 ----------------------------------------------------------------------------------------
497 PROCEDURE convert_node_header_val_to_id(
498 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_rec_type
499 )IS
500
501 CURSOR uf_header_id_nid_csr(p_pc_node_id IN NUMBER) IS
502 SELECT uf_header_id
503 FROM ahl_uf_headers
504 WHERE pc_node_id = p_pc_node_id;
505
506 l_uf_header_id NUMBER;
507
508
509 BEGIN
510 IF p_x_uf_header_rec.operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE THEN
511 IF(p_x_uf_header_rec.uf_header_id IS NULL) THEN
512 IF(p_x_uf_header_rec.pc_node_id IS NULL) THEN
513 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_NODEID_NLL');
514 FND_MSG_PUB.ADD;
515 ELSE
516 OPEN uf_header_id_nid_csr(p_x_uf_header_rec.pc_node_id);
517 FETCH uf_header_id_nid_csr INTO l_uf_header_id;
518 IF(uf_header_id_nid_csr%NOTFOUND)THEN
519 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HREC_NOTINDB');
520 FND_MSG_PUB.ADD;
521 ELSE
522 p_x_uf_header_rec.uf_header_id := l_uf_header_id;
523 END IF;
524 CLOSE uf_header_id_nid_csr;
525 END IF;
526 END IF;
527 END IF;
528
529 END convert_node_header_val_to_id;
530
531 ------------------------------------------------------------------------
532 -- This procedure does the following validations for uf_header_rec
533 -- check whether forecast type is AHL_UMP_UF_PVT.G_UF_TYPE_UNIT,AHL_UMP_UF_PVT.G_UF_TYPE_PART or AHL_UMP_UF_PVT.G_UF_TYPE_PC_NODE
534 -- check of uf_header_id , if null, verify operation flag is create AHL_UMP_UF_PVT.G_OP_CREATE, else AHL_UMP_UF_PVT.G_UF_TYPE_UNIT
535 -- check for object version number if latest. If uf_header_id is null, it should be null too.
536 -- for forecast type U : Unit
537 -- validate whether this unit is a complete Unit Configuration.
538 -- validate whether this unit is attached to a complete primary PC.
539 -- validate whether use unit flag is not null
540 -- for forecast type P : Part
541 -- validate whether this part is in inventory.A VALID Part
542 -- validate whether this part is attached to a complete primary PC.
543 -- use unit flag should be null.
544 -- for forecast type N : Node
545 -- validate whether this node is defined in PC nodes table.
546 -- validate whether this node is attached to a complete primary PC.
547 -- use unit flag should be null.
548 -------------------------------------------------------------------------
549 PROCEDURE validate_uf_header(
550 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type
551 ) IS
552
553 CURSOR uf_header_csr (p_uf_header_id IN NUMBER) IS
554 SELECT object_version_number, unit_config_header_id,pc_node_id,inventory_item_id,inventory_org_id,use_unit_flag
555 FROM ahl_uf_headers
556 WHERE uf_header_id = p_uf_header_id;
557
558
559 l_object_version_number NUMBER;
560 l_unit_config_header_id NUMBER;
561 l_pc_node_id NUMBER;
562 l_inventory_item_id NUMBER;
563 l_inventory_org_id NUMBER;
564 l_use_unit_flag ahl_uf_headers.use_unit_flag%TYPE;
565
566 CURSOR unit_status_check_csr(p_unit_config_header_id IN NUMBER) IS
567 SELECT UCH.unit_config_status_code
568 FROM ahl_unit_config_headers UCH
569 WHERE (UCH.active_end_date IS NULL OR TRUNC(UCH.active_end_date) > TRUNC(SYSDATE))
570 AND UCH.unit_config_header_id = p_unit_config_header_id;
571
572 l_unit_config_status_code ahl_unit_header_details_v.unit_config_status_code%TYPE;
573
574 CURSOR part_check_csr(p_inventory_item_id IN NUMBER) IS
575 SELECT 'x'
576 FROM mtl_system_items_kfv
577 WHERE inventory_item_id = p_inventory_item_id;
578
579 l_exists VARCHAR2(1);
580
581 CURSOR pc_status_check_up_csr(p_unit_item_id IN NUMBER,p_association_type IN VARCHAR2) IS
582 SELECT a.primary_flag,a.status
583 FROM ahl_pc_headers_b a, ahl_pc_nodes_b b, ahl_pc_associations c
584 WHERE a.pc_header_id = b.pc_header_id AND b.pc_node_id = c.pc_node_id AND
585 a.primary_flag = G_PC_PRIMARY_FLAG AND
586 a.status = G_COMPLETE_STATUS AND
587 c.association_type_flag = p_association_type AND
588 c.unit_item_id = p_unit_item_id;
589
590 CURSOR pc_status_check_n_csr(p_pc_node_id IN NUMBER) IS
591 SELECT a.primary_flag,a.status
592 FROM ahl_pc_headers_b a, ahl_pc_nodes_b b
593 WHERE a.pc_header_id = b.pc_header_id
594 AND b.pc_node_id = p_pc_node_id;
595
596 l_pc_primary_flag ahl_pc_headers_b.primary_flag%TYPE;
597 l_pc_status ahl_pc_headers_b.status%TYPE;
598
599 BEGIN
600
601 -- Operation Flag and Header ID validation.
602 IF (p_uf_header_rec.forecast_type NOT IN(AHL_UMP_UF_PVT.G_UF_TYPE_UNIT,AHL_UMP_UF_PVT.G_UF_TYPE_PART,AHL_UMP_UF_PVT.G_UF_TYPE_PC_NODE))THEN
603 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UF_TYPE');
604 FND_MSG_PUB.ADD;
605 ELSIF (p_uf_header_rec.operation_flag IS NOT NULL AND p_uf_header_rec.operation_flag NOT IN(AHL_UMP_UF_PVT.G_OP_CREATE,AHL_UMP_UF_PVT.G_OP_UPDATE,AHL_UMP_UF_PVT.G_OP_DELETE))THEN
606 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HEADER');
607 FND_MSG_PUB.ADD;
608 ELSIF ((p_uf_header_rec.uf_header_id IS NULL)
609 AND (p_uf_header_rec.operation_flag IS NULL OR p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE)) THEN
610 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_UFHID_NLL');
611 FND_MSG_PUB.ADD;
612 ELSIF(p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND
613 p_uf_header_rec.uf_header_id IS NOT NULL ) THEN
614 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_UFHID_N_NLL');
615 FND_MSG_PUB.ADD;
616 END IF;
617
618
619
620 IF(FND_MSG_PUB.count_msg > 0)THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
622 END IF;
623
624 IF (( p_uf_header_rec.operation_flag IS NULL OR p_uf_header_rec.operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE) AND p_uf_header_rec.uf_header_id IS NOT NULL)THEN
625 OPEN uf_header_csr(p_uf_header_rec.uf_header_id);
626 FETCH uf_header_csr INTO l_object_version_number, l_unit_config_header_id,l_pc_node_id,l_inventory_item_id,l_inventory_org_id,l_use_unit_flag;
627 IF (uf_header_csr%NOTFOUND) THEN
628 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HREC_NOTINDB');
629 FND_MSG_PUB.ADD;
630 ELSIF (l_object_version_number <> p_uf_header_rec.object_version_number)THEN
631 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_HREC_OBJV_MIS');
632 FND_MSG_PUB.ADD;
633 END IF;
634 CLOSE uf_header_csr;
635
636 IF(FND_MSG_PUB.count_msg > 0)THEN
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639
640 IF(p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_UNIT) THEN
641 IF(p_uf_header_rec.unit_config_header_id = FND_API.G_MISS_NUM)THEN
642 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFHID_UCHID_NLL');
643 FND_MSG_PUB.ADD;
644 ELSIF (p_uf_header_rec.unit_config_header_id IS NOT NULL AND
645 p_uf_header_rec.unit_config_header_id <> l_unit_config_header_id )THEN FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UCHID_MIS');
646 FND_MSG_PUB.ADD;
647 ELSE
648 OPEN unit_status_check_csr(l_unit_config_header_id);
649 FETCH unit_status_check_csr INTO l_unit_config_status_code;
650 IF (unit_status_check_csr%NOTFOUND) THEN
651 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_INV');
652 FND_MSG_PUB.ADD;
653 ELSIF (l_unit_config_status_code = AHL_UMP_UF_PVT.G_DRAFT_STATUS)THEN
654 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UC_STATUS_DRAFT');
655 FND_MSG_PUB.ADD;
656 END IF;
657 CLOSE unit_status_check_csr;
658
659 OPEN pc_status_check_up_csr(l_unit_config_header_id,AHL_UMP_UF_PVT.G_PC_UNIT_ASSOCIATION);
660 FETCH pc_status_check_up_csr INTO l_pc_primary_flag,l_pc_status;
661 IF (pc_status_check_up_csr%NOTFOUND) THEN
662 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_PC_INV');
663 FND_MSG_PUB.ADD;
664 ELSIF (l_pc_primary_flag <> AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG OR l_pc_status <> AHL_UMP_UF_PVT.G_COMPLETE_STATUS) THEN
665 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_PC_INV');
666 FND_MSG_PUB.ADD;
667 END IF;
668 CLOSE pc_status_check_up_csr;
669
670
671 END IF;
672 ELSIF(p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PART) THEN
673 IF(p_uf_header_rec.inventory_item_id = FND_API.G_MISS_NUM)THEN
674 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INVID_NLL');
675 FND_MSG_PUB.ADD;
676 ELSIF (p_uf_header_rec.inventory_item_id IS NOT NULL AND
677 p_uf_header_rec.inventory_item_id <> l_inventory_item_id )THEN
678 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVID_MIS');
679 FND_MSG_PUB.ADD;
680 ELSE
681 OPEN part_check_csr(l_inventory_item_id);
682 FETCH part_check_csr INTO l_exists;
683 IF (part_check_csr%NOTFOUND) THEN
684 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_INV');
685 FND_MSG_PUB.ADD;
686 END IF;
687 CLOSE part_check_csr;
688
689 OPEN pc_status_check_up_csr(l_inventory_item_id,AHL_UMP_UF_PVT.G_PC_ITEM_ASSOCIATION);
690 FETCH pc_status_check_up_csr INTO l_pc_primary_flag,l_pc_status;
691 IF (pc_status_check_up_csr%NOTFOUND) THEN
692 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_PC_INV');
693 FND_MSG_PUB.ADD;
694 ELSIF (l_pc_primary_flag <> AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG OR l_pc_status <> AHL_UMP_UF_PVT.G_COMPLETE_STATUS) THEN
695 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_PC_INV');
696 FND_MSG_PUB.ADD;
697 END IF;
698 CLOSE pc_status_check_up_csr;
699
700 END IF;
701 ELSIF(p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PC_NODE) THEN
702 IF(p_uf_header_rec.pc_node_id = FND_API.G_MISS_NUM)THEN
703 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_NODEID_NLL');
704 FND_MSG_PUB.ADD;
705 ELSIF (p_uf_header_rec.pc_node_id IS NOT NULL AND
706 p_uf_header_rec.pc_node_id <> l_pc_node_id )THEN
707 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_NODEID_MIS');
708 FND_MSG_PUB.ADD;
709 ELSE
710 OPEN pc_status_check_n_csr(l_pc_node_id);
711 FETCH pc_status_check_n_csr INTO l_pc_primary_flag,l_pc_status;
712 IF (pc_status_check_n_csr%NOTFOUND) THEN
713 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_NODE_PC_INV');
714 FND_MSG_PUB.ADD;
715 ELSIF (l_pc_primary_flag <> AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG OR l_pc_status <> AHL_UMP_UF_PVT.G_COMPLETE_STATUS) THEN
716 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_NODE_PC_INV');
717 FND_MSG_PUB.ADD;
718 END IF;
719 CLOSE pc_status_check_n_csr;
720
721 END IF;
722 END IF;
723 ELSIF (p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND
724 p_uf_header_rec.uf_header_id IS NULL)THEN
725 IF(p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_UNIT) THEN
726 IF(p_uf_header_rec.unit_config_header_id IS NULL OR
727 p_uf_header_rec.unit_config_header_id = FND_API.G_MISS_NUM) THEN
728 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFHID_UCHID_NLL');
729 FND_MSG_PUB.ADD;
730 ELSE
731 OPEN unit_status_check_csr(p_uf_header_rec.unit_config_header_id);
732 FETCH unit_status_check_csr INTO l_unit_config_status_code;
733 IF (unit_status_check_csr%NOTFOUND) THEN
734 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_INV');
735 FND_MSG_PUB.ADD;
736 ELSIF (l_unit_config_status_code = AHL_UMP_UF_PVT.G_DRAFT_STATUS)THEN
737 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UC_STATUS_DRAFT');
738 FND_MSG_PUB.ADD;
739 END IF;
740 CLOSE unit_status_check_csr;
741
742 OPEN pc_status_check_up_csr(p_uf_header_rec.unit_config_header_id, AHL_UMP_UF_PVT.G_PC_UNIT_ASSOCIATION);
743 FETCH pc_status_check_up_csr INTO l_pc_primary_flag,l_pc_status;
744 IF (pc_status_check_up_csr%NOTFOUND) THEN
745 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_PC_INV');
746 FND_MSG_PUB.ADD;
747 ELSIF (l_pc_primary_flag <> AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG OR l_pc_status <> AHL_UMP_UF_PVT.G_COMPLETE_STATUS) THEN
748 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UNIT_PC_INV');
749 FND_MSG_PUB.ADD;
750 END IF;
751 CLOSE pc_status_check_up_csr;
752
753 END IF;
754 ELSIF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PART) THEN
755 IF(p_uf_header_rec.inventory_item_id IS NULL OR
756 p_uf_header_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
757 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INVID_NLL');
758 FND_MSG_PUB.ADD;
759 ELSE
760 OPEN part_check_csr(p_uf_header_rec.inventory_item_id);
761 FETCH part_check_csr INTO l_exists;
762 IF (part_check_csr%NOTFOUND) THEN
763 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_INV');
764 FND_MSG_PUB.ADD;
765 END IF;
766 CLOSE part_check_csr;
767
768 OPEN pc_status_check_up_csr(p_uf_header_rec.inventory_item_id, AHL_UMP_UF_PVT.G_PC_ITEM_ASSOCIATION);
769 FETCH pc_status_check_up_csr INTO l_pc_primary_flag,l_pc_status;
770 IF (pc_status_check_up_csr%NOTFOUND) THEN
771 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_PC_INV');
772 FND_MSG_PUB.ADD;
773 ELSIF (l_pc_primary_flag <> AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG OR l_pc_status <> AHL_UMP_UF_PVT.G_COMPLETE_STATUS) THEN
774 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_PC_INV');
775 FND_MSG_PUB.ADD;
776 END IF;
777 CLOSE pc_status_check_up_csr;
778
779 END IF;
780 ELSIF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PC_NODE) THEN
781 IF(p_uf_header_rec.pc_node_id IS NULL OR
782 p_uf_header_rec.pc_node_id = FND_API.G_MISS_NUM) THEN
783 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_NODEID_NLL');
784 FND_MSG_PUB.ADD;
785 ELSE
786 OPEN pc_status_check_n_csr(p_uf_header_rec.pc_node_id);
787 FETCH pc_status_check_n_csr INTO l_pc_primary_flag,l_pc_status;
788 IF (pc_status_check_n_csr%NOTFOUND) THEN
789 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_NODE_PC_INV');
790 FND_MSG_PUB.ADD;
791 ELSIF (l_pc_primary_flag <> AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG OR l_pc_status <> AHL_UMP_UF_PVT.G_COMPLETE_STATUS) THEN
792 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_NODE_PC_INV');
793 FND_MSG_PUB.ADD;
794 END IF;
795 CLOSE pc_status_check_n_csr;
796 END IF;
797 END IF;
798 ELSE
799 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_HEADER');
800 FND_MSG_PUB.ADD;
801 END IF;
802
803 IF(FND_MSG_PUB.count_msg > 0)THEN
804 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
805 END IF;
806
807 IF((p_uf_header_rec.use_unit_flag IS NOT NULL OR p_uf_header_rec.use_unit_flag <> FND_API.G_MISS_CHAR)
808 AND p_uf_header_rec.use_unit_flag NOT IN (AHL_UMP_UF_PVT.G_UF_USE_UNIT_DEFAULT,AHL_UMP_UF_PVT.G_UF_USE_UNIT_YES)) THEN
809 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UNT_FLG');
810 FND_MSG_PUB.ADD;
811 END IF;
812
813 IF(FND_MSG_PUB.count_msg > 0)THEN
814 RAISE FND_API.G_EXC_ERROR;
815 END IF;
816
817
818 END validate_uf_header;
819 ----------------------------------------------------------
820 ------------------------------------------------------------------------
821 -- This procedure does the following validations for uf_header_rec when preventive maintenance is installed
822 -- check whether forecast type is AHL_UMP_UF_PVT.G_UF_TYPE_PART or AHL_UMP_UF_PVT.G_UF_TYPE_INSTANCE
823 -- check of uf_header_id , if null, verify operation flag is create AHL_UMP_UF_PVT.G_OP_CREATE
824 -- check for object version number if latest. If uf_header_id is null, it should be null too.
825 -- for forecast type P : Part
826 -- validate whether this part is in inventory.A VALID Part--
827 -- use unit flag should be "N" or null.
828 -- for forecast type C : Instance
829 -- validate whether this instance is defined in csi instance tables
830 -- validate whether this is an instanc of a valid part.
831 -- use unit flag should be valid.
832 -------------------------------------------------------------------------
833 PROCEDURE validate_uf_header_pm(
834 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type
835 ) IS
836
837 CURSOR uf_header_csr (p_uf_header_id IN NUMBER) IS
838 SELECT object_version_number, inventory_item_id,csi_item_instance_id,use_unit_flag
839 FROM ahl_uf_headers
840 WHERE uf_header_id = p_uf_header_id;
841
842
843 l_object_version_number NUMBER;
844 l_inventory_item_id NUMBER;
845 l_csi_item_instance_id NUMBER;
846 l_use_unit_flag ahl_uf_headers.use_unit_flag%TYPE;
847
848 --l_part_number mtl_system_items_kfv.concatenated_segments%TYPE;
849
850 CURSOR part_check_csr(p_inventory_item_id IN NUMBER) IS
851 SELECT 'x'
852 FROM mtl_system_items_kfv
853 WHERE inventory_item_id = p_inventory_item_id;
854
855 l_exists VARCHAR2(1);
856
857 CURSOR instance_check_csr(p_csi_item_instance_id IN NUMBER) IS
858 SELECT 'x'
859 FROM csi_item_instances
860 WHERE instance_id = p_csi_item_instance_id;
861
862
863 BEGIN
864 -- Operation Flag and Header ID validation.
865 IF (p_uf_header_rec.forecast_type NOT IN(AHL_UMP_UF_PVT.G_UF_TYPE_PART,AHL_UMP_UF_PVT.G_UF_TYPE_INSTANCE))THEN
866 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UF_TYPE');
867 FND_MSG_PUB.ADD;
868 ELSIF (p_uf_header_rec.operation_flag IS NOT NULL AND p_uf_header_rec.operation_flag NOT IN(AHL_UMP_UF_PVT.G_OP_CREATE,AHL_UMP_UF_PVT.G_OP_UPDATE,AHL_UMP_UF_PVT.G_OP_DELETE))THEN
869 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HEADER');
870 FND_MSG_PUB.ADD;
871 ELSIF ((p_uf_header_rec.uf_header_id IS NULL)
872 AND (p_uf_header_rec.operation_flag IS NULL OR p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE)) THEN
873 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_UFHID_NLL');
874 FND_MSG_PUB.ADD;
875 ELSIF(p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND
876 p_uf_header_rec.uf_header_id IS NOT NULL ) THEN
877 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_UFHID_N_NLL');
878 FND_MSG_PUB.ADD;
879 END IF;
880
881
882
883 IF(FND_MSG_PUB.count_msg > 0)THEN
884 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
885 END IF;
886
887 IF (( p_uf_header_rec.operation_flag IS NULL OR p_uf_header_rec.operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE) AND p_uf_header_rec.uf_header_id IS NOT NULL)THEN
888 OPEN uf_header_csr(p_uf_header_rec.uf_header_id);
889 FETCH uf_header_csr INTO l_object_version_number, l_inventory_item_id,l_csi_item_instance_id,l_use_unit_flag;
890 IF (uf_header_csr%NOTFOUND) THEN
891 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HREC_NOTINDB');
892 FND_MSG_PUB.ADD;
893 ELSIF (l_object_version_number <> p_uf_header_rec.object_version_number)THEN
894 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_HREC_OBJV_MIS');
895 FND_MSG_PUB.ADD;
896 END IF;
897 CLOSE uf_header_csr;
898
899 IF(FND_MSG_PUB.count_msg > 0)THEN
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902
903 IF(p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PART) THEN
904 IF(p_uf_header_rec.inventory_item_id = FND_API.G_MISS_NUM)THEN
905 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INVID_NLL');
906 FND_MSG_PUB.ADD;
907 ELSIF (p_uf_header_rec.inventory_item_id IS NOT NULL AND
908 p_uf_header_rec.inventory_item_id <> l_inventory_item_id )THEN
909 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVID_MIS');
910 FND_MSG_PUB.ADD;
911 ELSE
912 OPEN part_check_csr(l_inventory_item_id);
913 FETCH part_check_csr INTO l_exists;
914 IF (part_check_csr%NOTFOUND) THEN
915 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_INV');
916 FND_MSG_PUB.ADD;
917 END IF;
918 CLOSE part_check_csr;
919 END IF;
920 ELSIF(p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_INSTANCE) THEN
921 IF(p_uf_header_rec.csi_item_instance_id = FND_API.G_MISS_NUM)THEN
922 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INSTID_NLL');
923 FND_MSG_PUB.ADD;
924 ELSIF (p_uf_header_rec.csi_item_instance_id IS NOT NULL AND
925 p_uf_header_rec.csi_item_instance_id <> l_csi_item_instance_id )THEN
926 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INSTID_MIS');
927 FND_MSG_PUB.ADD;
928 ELSE
929 OPEN instance_check_csr(l_csi_item_instance_id);
930 FETCH instance_check_csr INTO l_exists;
931 IF (instance_check_csr%NOTFOUND) THEN
932 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INST_INV');
933 FND_MSG_PUB.ADD;
934 END IF;
935 CLOSE instance_check_csr;
936 END IF;
937 END IF;
938 ELSIF (p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND
939 p_uf_header_rec.uf_header_id IS NULL)THEN
940 IF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PART) THEN
941 IF(p_uf_header_rec.inventory_item_id IS NULL OR
942 p_uf_header_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
943 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INVID_NLL');
944 FND_MSG_PUB.ADD;
945 ELSE
946 OPEN part_check_csr(p_uf_header_rec.inventory_item_id);
947 FETCH part_check_csr INTO l_exists;
948 IF (part_check_csr%NOTFOUND) THEN
949 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_PART_INV');
950 FND_MSG_PUB.ADD;
951 END IF;
952 CLOSE part_check_csr;
953 END IF;
954 ELSIF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_INSTANCE) THEN
955 IF(p_uf_header_rec.csi_item_instance_id IS NULL OR
956 p_uf_header_rec.csi_item_instance_id = FND_API.G_MISS_NUM) THEN
957 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UFH_INSTID_NLL');
958 FND_MSG_PUB.ADD;
959 ELSE
960 OPEN instance_check_csr(p_uf_header_rec.csi_item_instance_id);
961 FETCH instance_check_csr INTO l_exists;
962 IF (instance_check_csr%NOTFOUND) THEN
963 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INST_INV');
964 FND_MSG_PUB.ADD;
965 END IF;
966 CLOSE instance_check_csr;
967 END IF;
968 END IF;
969 ELSE
970 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_HEADER');
971 FND_MSG_PUB.ADD;
972 END IF;
973
974 IF(FND_MSG_PUB.count_msg > 0)THEN
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977
978 IF((p_uf_header_rec.use_unit_flag IS NOT NULL OR p_uf_header_rec.use_unit_flag <> FND_API.G_MISS_CHAR)
979 AND p_uf_header_rec.use_unit_flag NOT IN (AHL_UMP_UF_PVT.G_UF_USE_UNIT_DEFAULT,AHL_UMP_UF_PVT.G_UF_USE_UNIT_YES)) THEN
980 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UNT_FLG');
981 FND_MSG_PUB.ADD;
982 END IF;
983
984 IF(FND_MSG_PUB.count_msg > 0)THEN
985 RAISE FND_API.G_EXC_ERROR;
986 END IF;
987
988
989 END validate_uf_header_pm;
990
991 ----------------------------------------------------------
992 -- This procedure processes defaults unchanged attribute in uf header record
993 ----------------------------------------------------------
994 PROCEDURE default_unchanged_uf_header(
995 p_x_uf_header_rec IN OUT NOCOPY AHL_UMP_UF_PVT.uf_header_Rec_type
996 ) IS
997
998 CURSOR uf_header_csr(p_uf_header_id IN NUMBER, p_object_version_number IN NUMBER) IS
999 SELECT unit_config_header_id, inventory_item_id, pc_node_id, inventory_org_id,csi_item_instance_id,
1000 use_unit_flag, attribute_category, attribute1,attribute2, attribute3,
1001 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9,
1002 attribute10, attribute11, attribute12, attribute13, attribute14, attribute15
1003 FROM ahl_uf_headers
1004 WHERE object_version_number= p_object_version_number
1005 AND uf_header_id = p_uf_header_id;
1006
1007 l_uf_header_rec AHL_UMP_UF_PVT.uf_header_Rec_type;
1008
1009 BEGIN
1010 IF(p_x_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE) THEN
1011 OPEN uf_header_csr(p_x_uf_header_rec.uf_header_id, p_x_uf_header_rec.object_version_number);
1012 FETCH uf_header_csr INTO l_uf_header_rec.unit_config_header_id, l_uf_header_rec.inventory_item_id,l_uf_header_rec.csi_item_instance_id,
1013 l_uf_header_rec.pc_node_id, l_uf_header_rec.inventory_org_id, l_uf_header_rec.use_unit_flag,
1014 l_uf_header_rec.attribute_category,l_uf_header_rec.attribute1,l_uf_header_rec.attribute2,
1015 l_uf_header_rec.attribute3, l_uf_header_rec.attribute4, l_uf_header_rec.attribute5,
1016 l_uf_header_rec.attribute6, l_uf_header_rec.attribute7, l_uf_header_rec.attribute8,
1017 l_uf_header_rec.attribute9, l_uf_header_rec.attribute10, l_uf_header_rec.attribute11,
1018 l_uf_header_rec.attribute12, l_uf_header_rec.attribute13, l_uf_header_rec.attribute14,
1019 l_uf_header_rec.attribute15;
1020 IF (uf_header_csr%NOTFOUND) THEN
1021 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_HREC_NOTINDB');
1022 FND_MSG_PUB.ADD;
1023 ELSE
1024 IF (p_x_uf_header_rec.unit_config_header_id is null) THEN
1025 p_x_uf_header_rec.unit_config_header_id := l_uf_header_rec.unit_config_header_id;
1026 ELSIF(p_x_uf_header_rec.unit_config_header_id = FND_API.G_MISS_NUM) THEN
1027 p_x_uf_header_rec.unit_config_header_id := null;
1028 END IF;
1029
1030 IF (p_x_uf_header_rec.inventory_item_id is null) THEN
1031 p_x_uf_header_rec.inventory_item_id := l_uf_header_rec.inventory_item_id;
1032 ELSIF(p_x_uf_header_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
1033 p_x_uf_header_rec.inventory_item_id := null;
1034 END IF;
1035
1036 IF (p_x_uf_header_rec.pc_node_id is null) THEN
1037 p_x_uf_header_rec.pc_node_id := l_uf_header_rec.pc_node_id;
1038 ELSIF(p_x_uf_header_rec.pc_node_id = FND_API.G_MISS_NUM) THEN
1039 p_x_uf_header_rec.pc_node_id := null; END IF;
1040
1041 IF (p_x_uf_header_rec.inventory_org_id is null) THEN
1042 p_x_uf_header_rec.inventory_org_id := l_uf_header_rec.inventory_org_id;
1043 ELSIF(p_x_uf_header_rec.inventory_org_id = FND_API.G_MISS_NUM) THEN
1044 p_x_uf_header_rec.inventory_org_id := null;
1045 END IF;
1046
1047 IF (p_x_uf_header_rec.csi_item_instance_id is null) THEN
1048 p_x_uf_header_rec.csi_item_instance_id := l_uf_header_rec.csi_item_instance_id;
1049 ELSIF(p_x_uf_header_rec.csi_item_instance_id = FND_API.G_MISS_NUM) THEN
1050 p_x_uf_header_rec.csi_item_instance_id := null;
1051 END IF;
1052
1053 IF (p_x_uf_header_rec.use_unit_flag is null) THEN
1054 p_x_uf_header_rec.use_unit_flag := l_uf_header_rec.use_unit_flag;
1055 ELSIF(p_x_uf_header_rec.use_unit_flag = FND_API.G_MISS_CHAR) THEN
1056 p_x_uf_header_rec.use_unit_flag := AHL_UMP_UF_PVT.G_UF_USE_UNIT_DEFAULT;
1057 END IF;
1058
1059 IF (p_x_uf_header_rec.attribute_category is null) THEN
1060 p_x_uf_header_rec.attribute_category := l_uf_header_rec.attribute_category;
1061 ELSIF(p_x_uf_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1062 p_x_uf_header_rec.attribute_category := null;
1063 END IF;
1064
1065 IF (p_x_uf_header_rec.attribute1 is null) THEN
1066 p_x_uf_header_rec.attribute1 := l_uf_header_rec.attribute1;
1067 ELSIF(p_x_uf_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1068 p_x_uf_header_rec.attribute1 := null;
1069 END IF;
1070
1071 IF (p_x_uf_header_rec.attribute2 is null) THEN
1072 p_x_uf_header_rec.attribute2 := l_uf_header_rec.attribute2;
1073 ELSIF(p_x_uf_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1074 p_x_uf_header_rec.attribute2 := null;
1075 END IF;
1076
1077 IF (p_x_uf_header_rec.attribute3 is null) THEN
1078 p_x_uf_header_rec.attribute3 := l_uf_header_rec.attribute3;
1079 ELSIF(p_x_uf_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1080 p_x_uf_header_rec.attribute3 := null;
1081 END IF;
1082
1083 IF (p_x_uf_header_rec.attribute4 is null) THEN
1084 p_x_uf_header_rec.attribute4 := l_uf_header_rec.attribute4;
1085 ELSIF(p_x_uf_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1086 p_x_uf_header_rec.attribute4 := null;
1087 END IF;
1088
1089 IF (p_x_uf_header_rec.attribute5 is null) THEN
1090 p_x_uf_header_rec.attribute5 := l_uf_header_rec.attribute5;
1091 ELSIF(p_x_uf_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1092 p_x_uf_header_rec.attribute5 := null;
1093 END IF;
1094
1095 IF (p_x_uf_header_rec.attribute6 is null) THEN
1096 p_x_uf_header_rec.attribute6 := l_uf_header_rec.attribute6;
1097 ELSIF(p_x_uf_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1098 p_x_uf_header_rec.attribute6 := null;
1099 END IF;
1100
1101 IF (p_x_uf_header_rec.attribute7 is null) THEN
1102 p_x_uf_header_rec.attribute7 := l_uf_header_rec.attribute7;
1103 ELSIF(p_x_uf_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1104 p_x_uf_header_rec.attribute7 := null;
1105 END IF;
1106
1107 IF (p_x_uf_header_rec.attribute8 is null) THEN
1108 p_x_uf_header_rec.attribute8 := l_uf_header_rec.attribute8;
1109 ELSIF(p_x_uf_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1110 p_x_uf_header_rec.attribute8 := null;
1111 END IF;
1112
1113 IF (p_x_uf_header_rec.attribute9 is null) THEN
1114 p_x_uf_header_rec.attribute9 := l_uf_header_rec.attribute9;
1115 ELSIF(p_x_uf_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1116 p_x_uf_header_rec.attribute9 := null;
1117 END IF;
1118
1119 IF (p_x_uf_header_rec.attribute10 is null) THEN
1120 p_x_uf_header_rec.attribute10 := l_uf_header_rec.attribute10;
1121 ELSIF(p_x_uf_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1122 p_x_uf_header_rec.attribute10 := null;
1123 END IF;
1124
1125 IF (p_x_uf_header_rec.attribute11 is null) THEN
1126 p_x_uf_header_rec.attribute11 := l_uf_header_rec.attribute11;
1127 ELSIF(p_x_uf_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1128 p_x_uf_header_rec.attribute11 := null;
1129 END IF;
1130
1131 IF (p_x_uf_header_rec.attribute12 is null) THEN
1132 p_x_uf_header_rec.attribute12 := l_uf_header_rec.attribute12;
1133 ELSIF(p_x_uf_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1134 p_x_uf_header_rec.attribute12 := null;
1135 END IF;
1136
1137 IF (p_x_uf_header_rec.attribute13 is null) THEN
1138 p_x_uf_header_rec.attribute13 := l_uf_header_rec.attribute13;
1139 ELSIF(p_x_uf_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1140 p_x_uf_header_rec.attribute13 := null;
1141 END IF;
1142
1143 IF (p_x_uf_header_rec.attribute14 is null) THEN
1144 p_x_uf_header_rec.attribute14 := l_uf_header_rec.attribute14;
1145 ELSIF(p_x_uf_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1146 p_x_uf_header_rec.attribute14 := null;
1147 END IF;
1148
1149 IF (p_x_uf_header_rec.attribute15 is null) THEN
1150 p_x_uf_header_rec.attribute15 := l_uf_header_rec.attribute15;
1151 ELSIF(p_x_uf_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1152 p_x_uf_header_rec.attribute15 := null;
1153 END IF;
1154
1155 END IF;
1156 CLOSE uf_header_csr;
1157 ELSIF (p_x_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE) THEN
1158
1159 IF (p_x_uf_header_rec.unit_config_header_id = FND_API.G_MISS_NUM) THEN
1160 p_x_uf_header_rec.unit_config_header_id := null;
1161 END IF;
1162
1163 IF (p_x_uf_header_rec.inventory_item_id = FND_API.G_MISS_NUM) THEN
1164 p_x_uf_header_rec.inventory_item_id := null;
1165 END IF;
1166
1167 IF (p_x_uf_header_rec.csi_item_instance_id = FND_API.G_MISS_NUM) THEN
1168 p_x_uf_header_rec.csi_item_instance_id := null;
1169 END IF;
1170
1171 IF (p_x_uf_header_rec.pc_node_id = FND_API.G_MISS_NUM) THEN
1172 p_x_uf_header_rec.pc_node_id := null;
1173 END IF;
1174
1175 IF (p_x_uf_header_rec.inventory_org_id = FND_API.G_MISS_NUM) THEN
1176 p_x_uf_header_rec.inventory_org_id := null;
1177 END IF;
1178
1179 IF (p_x_uf_header_rec.use_unit_flag is null) THEN
1180 p_x_uf_header_rec.use_unit_flag := AHL_UMP_UF_PVT.G_UF_USE_UNIT_DEFAULT;
1181 ELSIF(p_x_uf_header_rec.use_unit_flag = FND_API.G_MISS_CHAR) THEN
1182 p_x_uf_header_rec.use_unit_flag := AHL_UMP_UF_PVT.G_UF_USE_UNIT_DEFAULT;
1183 END IF;
1184
1185 IF (p_x_uf_header_rec.attribute_category = FND_API.G_MISS_CHAR) THEN
1186 p_x_uf_header_rec.attribute_category := null;
1187 END IF;
1188 IF (p_x_uf_header_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1189 p_x_uf_header_rec.attribute1 := null;
1190 END IF;
1191 IF (p_x_uf_header_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1192 p_x_uf_header_rec.attribute2 := null;
1193 END IF;
1194 IF (p_x_uf_header_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1195 p_x_uf_header_rec.attribute3 := null;
1196 END IF;
1197 IF (p_x_uf_header_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1198 p_x_uf_header_rec.attribute4 := null;
1199 END IF;
1200 IF (p_x_uf_header_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1201 p_x_uf_header_rec.attribute5 := null;
1202 END IF;
1203 IF (p_x_uf_header_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1204 p_x_uf_header_rec.attribute6 := null;
1205 END IF;
1206 IF (p_x_uf_header_rec.attribute7 = FND_API.G_MISS_CHAR) THEN p_x_uf_header_rec.attribute7 := null;
1207 END IF;
1208 IF (p_x_uf_header_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1209 p_x_uf_header_rec.attribute8 := null;
1210 END IF;
1211 IF (p_x_uf_header_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1212 p_x_uf_header_rec.attribute9 := null;
1213 END IF;
1214 IF (p_x_uf_header_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1215 p_x_uf_header_rec.attribute10 := null;
1216 END IF;
1217 IF (p_x_uf_header_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1218 p_x_uf_header_rec.attribute11 := null;
1219 END IF;
1220 IF (p_x_uf_header_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1221 p_x_uf_header_rec.attribute12 := null;
1222 END IF;
1223 IF (p_x_uf_header_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1224 p_x_uf_header_rec.attribute13 := null;
1225 END IF;
1226 IF (p_x_uf_header_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1227 p_x_uf_header_rec.attribute14 := null;
1228 END IF;
1229 IF (p_x_uf_header_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1230 p_x_uf_header_rec.attribute15 := null;
1231 END IF;
1232 END IF;
1233 END default_unchanged_uf_header;
1234
1235
1236 ----------------------------------------------------------
1237 -- This procedure processes uf_detail_rec
1238 ----------------------------------------------------------
1239 PROCEDURE process_uf_details(
1240 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1241 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type,
1242 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
1243 ) IS
1244
1245 BEGIN
1246 --no value to id conversion involved here
1247
1248 validate_uf_details(
1249 p_uf_header_rec => p_uf_header_rec,
1250 p_x_uf_details_tbl => p_x_uf_details_tbl
1251 );
1252
1253 default_unchanged_uf_details(p_x_uf_details_tbl => p_x_uf_details_tbl);
1254 --save details now
1255 FOR i IN p_x_uf_details_tbl.FIRST..p_x_uf_details_tbl.LAST LOOP
1256 IF(p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_DELETE) THEN
1257 AHL_UF_DETAILS_PKG.delete_row(p_x_uf_details_tbl(i).uf_detail_id);
1258 END IF;
1259 END LOOP;
1260
1261 FOR i IN p_x_uf_details_tbl.FIRST..p_x_uf_details_tbl.LAST LOOP
1262 IF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE) THEN
1263
1264 --setting object version number for create
1265 p_x_uf_details_tbl(i).object_version_number := p_x_uf_details_tbl(i).object_version_number + 1;
1266 --setting up user/create/update information
1267 p_x_uf_details_tbl(i).last_updated_by := fnd_global.user_id;
1268 p_x_uf_details_tbl(i).last_update_date := SYSDATE;
1269 p_x_uf_details_tbl(i).last_update_login := fnd_global.user_id;
1270
1271 AHL_UF_DETAILS_PKG.update_row(
1272 x_uf_detail_id => p_x_uf_details_tbl(i).uf_detail_id,
1273 x_object_version_number => p_x_uf_details_tbl(i).object_version_number,
1274 x_last_updated_by => p_x_uf_details_tbl(i).last_updated_by,
1275 x_last_update_date => p_x_uf_details_tbl(i).last_update_date,
1276 x_last_update_login => p_x_uf_details_tbl(i).last_update_login,
1277 x_uf_header_id => p_x_uf_details_tbl(i).uf_header_id,
1278 x_uom_code => p_x_uf_details_tbl(i).uom_code,
1279 x_start_date => p_x_uf_details_tbl(i).start_date,
1280 x_end_date => p_x_uf_details_tbl(i).end_date,
1281 x_usage_per_day => p_x_uf_details_tbl(i).usage_per_day,
1282 x_attribute_category => p_x_uf_details_tbl(i).attribute_category,
1283 x_attribute1 => p_x_uf_details_tbl(i).attribute1,
1284 x_attribute2 => p_x_uf_details_tbl(i).attribute2,
1285 x_attribute3 => p_x_uf_details_tbl(i).attribute3,
1286 x_attribute4 => p_x_uf_details_tbl(i).attribute4,
1287 x_attribute5 => p_x_uf_details_tbl(i).attribute5,
1288 x_attribute6 => p_x_uf_details_tbl(i).attribute6,
1289 x_attribute7 => p_x_uf_details_tbl(i).attribute7,
1290 x_attribute8 => p_x_uf_details_tbl(i).attribute8,
1291 x_attribute9 => p_x_uf_details_tbl(i).attribute9,
1292 x_attribute10 => p_x_uf_details_tbl(i).attribute10,
1293 x_attribute11 => p_x_uf_details_tbl(i).attribute11,
1294 x_attribute12 => p_x_uf_details_tbl(i).attribute12,
1295 x_attribute13 => p_x_uf_details_tbl(i).attribute13,
1296 x_attribute14 => p_x_uf_details_tbl(i).attribute14,
1297 x_attribute15 => p_x_uf_details_tbl(i).attribute15
1298 );
1299 END IF;
1300 END LOOP;
1301 FOR i IN p_x_uf_details_tbl.FIRST..p_x_uf_details_tbl.LAST LOOP
1302 IF(p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE) THEN
1303 --setting object version number for create
1304 p_x_uf_details_tbl(i).object_version_number := 1;
1305 --setting up user/create/update information
1306 p_x_uf_details_tbl(i).created_by := fnd_global.user_id;
1307 p_x_uf_details_tbl(i).creation_date := SYSDATE;
1308 p_x_uf_details_tbl(i).last_updated_by := fnd_global.user_id;
1309 p_x_uf_details_tbl(i).last_update_date := SYSDATE;
1310 p_x_uf_details_tbl(i).last_update_login := fnd_global.user_id;
1311
1312 p_x_uf_details_tbl(i).uf_header_id := p_uf_header_rec.uf_header_id;
1313
1314 AHL_UF_DETAILS_PKG.insert_row(
1315 x_uf_detail_id => p_x_uf_details_tbl(i).uf_detail_id,
1316 x_object_version_number => p_x_uf_details_tbl(i).object_version_number,
1317 x_created_by => p_x_uf_details_tbl(i).created_by,
1318 x_creation_date => p_x_uf_details_tbl(i).creation_date,
1319 x_last_updated_by => p_x_uf_details_tbl(i).last_updated_by,
1320 x_last_update_date => p_x_uf_details_tbl(i).last_update_date,
1321 x_last_update_login => p_x_uf_details_tbl(i).last_update_login,
1322 x_uf_header_id => p_x_uf_details_tbl(i).uf_header_id,
1323 x_uom_code => p_x_uf_details_tbl(i).uom_code,
1324 x_start_date => p_x_uf_details_tbl(i).start_date,
1325 x_end_date => p_x_uf_details_tbl(i).end_date,
1326 x_usage_per_day => p_x_uf_details_tbl(i).usage_per_day,
1327 x_attribute_category => p_x_uf_details_tbl(i).attribute_category,
1328 x_attribute1 => p_x_uf_details_tbl(i).attribute1,
1329 x_attribute2 => p_x_uf_details_tbl(i).attribute2,
1330 x_attribute3 => p_x_uf_details_tbl(i).attribute3,
1331 x_attribute4 => p_x_uf_details_tbl(i).attribute4,
1332 x_attribute5 => p_x_uf_details_tbl(i).attribute5,
1333 x_attribute6 => p_x_uf_details_tbl(i).attribute6,
1334 x_attribute7 => p_x_uf_details_tbl(i).attribute7,
1335 x_attribute8 => p_x_uf_details_tbl(i).attribute8,
1336 x_attribute9 => p_x_uf_details_tbl(i).attribute9,
1337 x_attribute10 => p_x_uf_details_tbl(i).attribute10,
1338 x_attribute11 => p_x_uf_details_tbl(i).attribute11,
1339 x_attribute12 => p_x_uf_details_tbl(i).attribute12,
1340 x_attribute13 => p_x_uf_details_tbl(i).attribute13,
1341 x_attribute14 => p_x_uf_details_tbl(i).attribute14,
1342 x_attribute15 => p_x_uf_details_tbl(i).attribute15
1343 );
1344 END IF;
1345 END LOOP;
1346
1347 --validate saved data.. check for gaps etc
1348 validate_utilization_forecast(
1349 p_uf_header_rec => p_uf_header_rec,
1350 x_uf_details_tbl => p_x_uf_details_tbl
1351 );
1352
1353 IF(FND_MSG_PUB.count_msg > 0)THEN
1354 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1355 END IF;
1356
1357 END process_uf_details;
1358
1359
1360 ---------------------------------------------------------------------------------------------------------------------------------------------------
1361 -- This procedure does the following validations for uf_details_tbl
1362 -- verify operation flag is not null for all records
1363 -- check if uf_header_id , if null, verify operation flag is create AHL_UMP_UF_PVT.G_OP_CREATE for all records in details
1364 -- check if uf_detail_id is not null then operation flag is update AHL_UMP_UF_PVT.G_UF_TYPE_UNIT or 'D' and uf_header_id is not null
1365 -- Validate that for every record in p_x_uf_details_tbl, start_date is < end_date(IF not null) and start_date is not null.
1366 -- Validate that for every record in p_x_uf_details_tbl, usage_per_day is a valid number +ve,-ve or 0 .
1367 -- Validate that for every record in p_x_uf_details_tbl, uom is valid.
1368 -- If operation_flag is "U" or "D" verfiy object version number if they are same as in database.
1369 -- If operation_flag is "U", if start_date < SYSDATE, split this record and
1370 -- update current record with UOM for this record in DB and end date as SYSDATE - 1
1371 -- add a new one with with start date as SYSDATE and operation flag as AHL_UMP_UF_PVT.G_OP_CREATE
1372 -- If operation_flag is "D", if start_date < SYSDATE, put end date as SYSDATE -1 (yesterday's date).set operation_flag as AHL_UMP_UF_PVT.G_UF_TYPE_UNIT
1373 -- update x_return_status on error , break if required
1374 ---------------------------------------------------------------------------------------------------------------------------------------------------
1375 PROCEDURE validate_uf_details(
1376 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type,
1377 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
1378 ) IS
1379
1380 CURSOR uf_details_csr (p_uf_detail_id IN NUMBER) IS
1381 SELECT object_version_number, uf_header_id,uom_code,start_date,end_date,usage_per_day
1382 FROM ahl_uf_details
1383 WHERE uf_detail_id = p_uf_detail_id;
1384
1385 l_object_version_number NUMBER;
1386 l_uf_header_id NUMBER;
1387 l_uom_code ahl_uf_details.uom_code%TYPE;
1388 l_start_date DATE;
1389 l_end_date DATE;
1390 l_usage_per_day NUMBER;
1391
1392 l_total NUMBER;
1393 l_last NUMBER;
1394
1395 CURSOR part_uom_code_ckeck_csr(p_uom_code IN VARCHAR2) IS
1396 select 'x'
1397 from mtl_units_of_measure_vl
1398 where uom_code = p_uom_code;
1399
1400 CURSOR node_uom_code_ckeck_csr(p_uom_code IN VARCHAR2) IS
1401 select 'x'
1402 from mtl_units_of_measure_vl
1403 where uom_code = p_uom_code;
1404
1405 CURSOR unit_uom_code_ckeck_csr(p_uom_code IN VARCHAR2) IS
1406 select 'x'
1407 from mtl_units_of_measure_vl
1408 where uom_code = p_uom_code;
1409
1410 l_exists VARCHAR2(1);
1411
1412 BEGIN
1413 l_total := p_x_uf_details_tbl.count;
1414 FOR i IN p_x_uf_details_tbl.FIRST..l_total LOOP
1415 --checking for unexpected errors
1416 IF ( p_x_uf_details_tbl(i).operation_flag IS NOT NULL AND p_x_uf_details_tbl(i).operation_flag NOT IN(AHL_UMP_UF_PVT.G_OP_CREATE,AHL_UMP_UF_PVT.G_OP_UPDATE,AHL_UMP_UF_PVT.G_OP_DELETE))THEN
1417 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_DETAIL');
1418 FND_MSG_PUB.ADD;
1419 ELSIF (p_uf_header_rec.operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND p_x_uf_details_tbl(i).operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE) THEN
1420 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_OP_DET_HED');
1421 FND_MSG_PUB.ADD;
1422 ELSIF ((p_x_uf_details_tbl(i).operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE AND p_x_uf_details_tbl(i).uf_detail_id IS NULL) OR
1423 (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND p_x_uf_details_tbl(i).uf_detail_id IS NOT NULL)) THEN
1424 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INVOP_DETAIL');
1425 FND_MSG_PUB.ADD;
1426 END IF;
1427 -- Raise if unexpected errors
1428 IF FND_MSG_PUB.count_msg > 0 THEN
1429 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1430 END IF;
1431
1432 IF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE AND
1433 p_x_uf_details_tbl(i).end_date = FND_API.G_MISS_DATE) THEN
1434 p_x_uf_details_tbl(i).end_date := NULL;
1435
1436 -- checking for expected error if record is modified
1437 ELSIF (p_x_uf_details_tbl(i).operation_flag <> AHL_UMP_UF_PVT.G_OP_CREATE AND p_x_uf_details_tbl(i).uf_detail_id IS NOT NULL)THEN
1438 OPEN uf_details_csr(p_x_uf_details_tbl(i).uf_detail_id);
1439 FETCH uf_details_csr INTO l_object_version_number, l_uf_header_id,l_uom_code,l_start_date,l_end_date,l_usage_per_day;
1440 IF (uf_details_csr%NOTFOUND) THEN
1441 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_DET_INV_NOTINDB');
1442 FND_MSG_PUB.ADD;
1443 ELSIF l_object_version_number <> p_x_uf_details_tbl(i).object_version_number THEN
1444 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_DET_OBJV_MIS');
1445 FND_MSG_PUB.ADD;
1446 ELSE
1447 IF p_x_uf_details_tbl(i).end_date IS NULL THEN
1448 p_x_uf_details_tbl(i).end_date := l_end_date;
1449 ELSIF p_x_uf_details_tbl(i).end_date = FND_API.G_MISS_DATE THEN
1450 p_x_uf_details_tbl(i).end_date := NULL;
1451 END IF;
1452 IF p_x_uf_details_tbl(i).uf_header_id IS NULL THEN
1453 p_x_uf_details_tbl(i).uf_header_id := l_uf_header_id;
1454 ELSIF p_x_uf_details_tbl(i).uf_header_id = FND_API.G_MISS_NUM THEN
1455 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_DET_UFHID');
1456 FND_MSG_PUB.ADD;
1457 ELSIF (p_x_uf_details_tbl(i).uf_header_id <> l_uf_header_id) THEN
1458 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_DET_UFHID');
1459 FND_MSG_PUB.ADD;
1460 END IF;
1461 END IF;
1462 CLOSE uf_details_csr;
1463 END IF;
1464
1465 IF(FND_MSG_PUB.count_msg > 0)THEN
1466 RAISE FND_API.G_EXC_ERROR;
1467 END IF;
1468
1469 -- checking for expected error if start date , end date,UOM are invalid
1470 IF(p_x_uf_details_tbl(i).operation_flag IN (AHL_UMP_UF_PVT.G_OP_CREATE,AHL_UMP_UF_PVT.G_OP_UPDATE))THEN
1471 -- checking for start and end dates
1472 IF(p_x_uf_details_tbl(i).start_date IS NULL) THEN
1473 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_SDATE_NLL');
1474 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1475 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1476 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1477 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1478 FND_MSG_PUB.ADD;
1479 ELSIF (p_x_uf_details_tbl(i).end_date IS NOT NULL) THEN
1480 IF ((TRUNC(p_x_uf_details_tbl(i).end_date) < TRUNC(p_x_uf_details_tbl(i).start_date)) OR
1481 (TRUNC(p_x_uf_details_tbl(i).end_date) < TRUNC(SYSDATE)))THEN
1482 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_EDATE');
1483 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1484 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1485 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1486 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1487 FND_MSG_PUB.ADD;
1488 ELSIF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE) THEN
1489 IF(TRUNC(p_x_uf_details_tbl(i).start_date) < TRUNC(SYSDATE))THEN
1490 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_SDATE');
1491 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1492 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1493 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1494 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1495 FND_MSG_PUB.ADD;
1496 END IF;
1497 ELSIF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE) THEN
1498 IF ( TRUNC(p_x_uf_details_tbl(i).start_date) < TRUNC(SYSDATE) AND
1499 (TRUNC(p_x_uf_details_tbl(i).start_date) <> TRUNC(l_start_date) OR
1500 p_x_uf_details_tbl(i).uom_code <> l_uom_code)) THEN
1501 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_DONCHG_STD_UOM');
1502 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1503 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1504 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1505 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1506 FND_MSG_PUB.ADD;
1507 END IF;
1508 END IF;
1509 ELSIF (p_x_uf_details_tbl(i).end_date IS NULL) THEN
1510 IF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE) THEN
1511 IF(TRUNC(p_x_uf_details_tbl(i).start_date) < TRUNC(SYSDATE))THEN
1512 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_SDATE');
1513 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1514 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1515 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1516 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1517 FND_MSG_PUB.ADD;
1518 END IF;
1519 ELSIF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE) THEN
1520 IF ( TRUNC(p_x_uf_details_tbl(i).start_date) < TRUNC(SYSDATE) AND
1521 (TRUNC(p_x_uf_details_tbl(i).start_date) <> TRUNC(l_start_date) OR
1522 p_x_uf_details_tbl(i).uom_code <> l_uom_code)) THEN
1523 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_DONCHG_STD_UOM');
1524 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1525 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1526 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1527 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1528 FND_MSG_PUB.ADD;
1529 END IF;
1530 END IF;
1531 END IF;
1532
1533 --checking for expected error if UOM is invalid
1534 IF(p_x_uf_details_tbl(i).uom_code IS NULL) THEN
1535 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_UOMCD_NLL');
1536 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1537 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1538 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1539 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1540 FND_MSG_PUB.ADD;
1541 ELSE
1542 IF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_OP_UPDATE)THEN
1543 OPEN unit_uom_code_ckeck_csr(p_x_uf_details_tbl(i).uom_code);
1544 FETCH unit_uom_code_ckeck_csr into l_exists;
1545 IF (unit_uom_code_ckeck_csr%NOTFOUND) THEN
1546 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UOMCD');
1547 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1548 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1549 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1550 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1551 FND_MSG_PUB.ADD;
1552 END IF;
1553 CLOSE unit_uom_code_ckeck_csr;
1554 ELSIF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PART)THEN
1555 OPEN part_uom_code_ckeck_csr(p_x_uf_details_tbl(i).uom_code);
1556 FETCH part_uom_code_ckeck_csr into l_exists;
1557 IF (part_uom_code_ckeck_csr%NOTFOUND) THEN
1558 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UOMCD');
1559 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1560 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1561 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1562 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1563 FND_MSG_PUB.ADD;
1564 END IF;
1565 CLOSE part_uom_code_ckeck_csr;
1566 ELSIF (p_uf_header_rec.forecast_type = AHL_UMP_UF_PVT.G_UF_TYPE_PC_NODE)THEN
1567 OPEN node_uom_code_ckeck_csr(p_x_uf_details_tbl(i).uom_code);
1568 FETCH node_uom_code_ckeck_csr into l_exists;
1569 IF (node_uom_code_ckeck_csr%NOTFOUND) THEN
1570 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_INV_UOMCD');
1571 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1572 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1573 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1574 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1575 FND_MSG_PUB.ADD;
1576 END IF;
1577 CLOSE node_uom_code_ckeck_csr;
1578 END IF;
1579 END IF;--UOM code check
1580 END IF;
1581
1582 --spliting and end-dating
1583 IF (FND_MSG_PUB.count_msg = 0) THEN
1584 IF(p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE)THEN
1585 IF TRUNC(p_x_uf_details_tbl(i).start_date) < TRUNC(SYSDATE) THEN
1586 IF((TRUNC(p_x_uf_details_tbl(i).start_date) <> TRUNC(l_start_date))OR
1587 (p_x_uf_details_tbl(i).uom_code <> l_uom_code)) THEN
1588 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_DONCHG_STD_UOM');
1589 FND_MESSAGE.Set_Token('START_DATE',p_x_uf_details_tbl(i).start_date);
1590 FND_MESSAGE.Set_Token('END_DATE',p_x_uf_details_tbl(i).end_date);
1591 FND_MESSAGE.Set_Token('UOM_CODE',p_x_uf_details_tbl(i).uom_code);
1592 FND_MESSAGE.Set_Token('USAGE_PER_DAY',p_x_uf_details_tbl(i).usage_per_day);
1593 FND_MSG_PUB.ADD;
1594 ELSIF ((TRUNC(p_x_uf_details_tbl(i).end_date) <> TRUNC(l_end_date))OR
1595 (p_x_uf_details_tbl(i).usage_per_day <> l_usage_per_day)) THEN
1596 -- adding new record
1597 l_last := p_x_uf_details_tbl.count + 1;
1598 p_x_uf_details_tbl(l_last).start_date := SYSDATE;
1599 p_x_uf_details_tbl(l_last).end_date := p_x_uf_details_tbl(i).end_date;
1600 p_x_uf_details_tbl(l_last).usage_per_day := p_x_uf_details_tbl(i).usage_per_day;
1601 p_x_uf_details_tbl(l_last).uom_code := p_x_uf_details_tbl(i).uom_code;
1602 p_x_uf_details_tbl(l_last).operation_flag := AHL_UMP_UF_PVT.G_OP_CREATE;
1603 p_x_uf_details_tbl(l_last).uf_header_id := p_x_uf_details_tbl(i).uf_header_id;
1604 -- Modifying and end-dating existing record.
1605 p_x_uf_details_tbl(i).end_date := TRUNC(SYSDATE - 1);
1606 p_x_uf_details_tbl(i).uom_code := l_uom_code;
1607 p_x_uf_details_tbl(i).usage_per_day := l_usage_per_day;
1608
1609 END IF;
1610 END IF;
1611 -- end dating if start date < sysdate
1612 ELSIF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_DELETE)THEN
1613 IF(TRUNC(l_start_date) < TRUNC(SYSDATE))THEN
1614 p_x_uf_details_tbl(i).start_date := l_start_date;
1615 p_x_uf_details_tbl(i).end_date := SYSDATE - 1;
1616 p_x_uf_details_tbl(i).uom_code := l_uom_code;
1617 p_x_uf_details_tbl(i).usage_per_day := l_usage_per_day;
1618 p_x_uf_details_tbl(i).operation_flag := AHL_UMP_UF_PVT.G_OP_UPDATE;
1619 END IF;
1620 END IF;
1621 END IF; --end splitting and end dating
1622
1623 END LOOP;
1624
1625 IF FND_MSG_PUB.count_msg > 0 THEN
1626 RAISE FND_API.G_EXC_ERROR;
1627 END IF;
1628
1629 END validate_uf_details;
1630
1631 ---------------------------------------------------------------------------------------------------------------------------------------------------
1632 -- This procedure verfies whether saved utilization forecast was validate_uf_detailsand had no gaps.
1633 -- fetch details table records for this uf_header_id ordered by UOM, start date where endt_date >= Sysdate - 1.
1634 -- for same UOM, end date of one record should always be one less than the start date of next record
1635 -- update x_return_status on error , break if required
1636 -----------------------------------------------------------------------------------------------------------------------------------------------------
1637
1638
1639 PROCEDURE validate_utilization_forecast(
1640 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_rec_type,
1641 x_uf_details_tbl OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
1642 ) IS
1643
1644 CURSOR uf_sorted_details_csr (p_uf_header_id IN NUMBER) IS
1645 SELECT object_version_number, uf_detail_id, uf_header_id,uom_code,start_date,end_date,usage_per_day
1646 FROM ahl_uf_details
1647 WHERE uf_header_id = p_uf_header_id
1648 AND (end_date IS NULL OR TRUNC(end_date) >= TRUNC(SYSDATE))
1649 ORDER BY uom_code,start_date;
1650
1651 i NUMBER;
1652
1653 BEGIN
1654 i:= 1;
1655
1656 OPEN uf_sorted_details_csr(p_uf_header_rec.uf_header_id);
1657 LOOP
1658 FETCH uf_sorted_details_csr INTO x_uf_details_tbl(i).object_version_number,
1659 x_uf_details_tbl(i).uf_detail_id,
1660 x_uf_details_tbl(i).uf_header_id,
1661 x_uf_details_tbl(i).uom_code,
1662 x_uf_details_tbl(i).start_date,
1663 x_uf_details_tbl(i).end_date,
1664 x_uf_details_tbl(i).usage_per_day;
1665 exit when uf_sorted_details_csr%notfound;
1666 i := i + 1;
1667 END LOOP;
1668
1669 CLOSE uf_sorted_details_csr;
1670 IF(x_uf_details_tbl.COUNT > 0) THEN
1671 FOR i IN x_uf_details_tbl.FIRST..x_uf_details_tbl.LAST LOOP
1672 IF(x_uf_details_tbl.LAST > i) THEN
1673 IF(x_uf_details_tbl(i).uom_code = x_uf_details_tbl(i+1).uom_code) THEN
1674 IF(x_uf_details_tbl(i).end_date IS NULL) THEN
1675 FND_MESSAGE.Set_Name('AHL','AHL_UMP_UF_OLAPS_IN_UF');
1676 FND_MESSAGE.Set_Token('FSTART_DATE',x_uf_details_tbl(i).start_date);
1677 FND_MESSAGE.Set_Token('FEND_DATE',x_uf_details_tbl(i).end_date);
1678 FND_MESSAGE.Set_Token('FUOM_CODE',x_uf_details_tbl(i).uom_code);
1679 FND_MESSAGE.Set_Token('FUSAGE_PER_DAY',x_uf_details_tbl(i).usage_per_day);
1680 FND_MESSAGE.Set_Token('NSTART_DATE',x_uf_details_tbl(i+1).start_date);
1681 FND_MESSAGE.Set_Token('NEND_DATE',x_uf_details_tbl(i+1).end_date);
1682 FND_MESSAGE.Set_Token('NUOM_CODE',x_uf_details_tbl(i+1).uom_code);
1683 FND_MESSAGE.Set_Token('NUSAGE_PER_DAY',x_uf_details_tbl(i+1).usage_per_day);
1684 FND_MSG_PUB.ADD;
1685 ELSIF TRUNC(x_uf_details_tbl(i+1).start_date) <> TRUNC(x_uf_details_tbl(i).end_date + 1) THEN
1686 IF(TRUNC(x_uf_details_tbl(i+1).start_date) > TRUNC(x_uf_details_tbl(i).end_date + 1)) THEN
1687 FND_MESSAGE.Set_Name('AHL','AHL_UMP_UF_GAPS_IN_UF');
1688 ELSE
1689 FND_MESSAGE.Set_Name('AHL','AHL_UMP_UF_OLAPS_IN_UF');
1690 END IF;
1691 FND_MESSAGE.Set_Token('FSTART_DATE',x_uf_details_tbl(i).start_date);
1692 FND_MESSAGE.Set_Token('FEND_DATE',x_uf_details_tbl(i).end_date);
1693 FND_MESSAGE.Set_Token('FUOM_CODE',x_uf_details_tbl(i).uom_code);
1694 FND_MESSAGE.Set_Token('FUSAGE_PER_DAY',x_uf_details_tbl(i).usage_per_day);
1695 FND_MESSAGE.Set_Token('NSTART_DATE',x_uf_details_tbl(i+1).start_date);
1696 FND_MESSAGE.Set_Token('NEND_DATE',x_uf_details_tbl(i+1).end_date);
1697 FND_MESSAGE.Set_Token('NUOM_CODE',x_uf_details_tbl(i+1).uom_code);
1698 FND_MESSAGE.Set_Token('NUSAGE_PER_DAY',x_uf_details_tbl(i+1).usage_per_day);
1699 FND_MSG_PUB.ADD;
1700 END IF;
1701 END IF;
1702 END IF;
1703 END LOOP;
1704 END IF;
1705
1706 IF FND_MSG_PUB.count_msg > 0 THEN
1707 RAISE FND_API.G_EXC_ERROR;
1708 END IF;
1709
1710 END validate_utilization_forecast;
1711
1712 --------------------------------------------------------------------------------
1713 -- This procedure writes message.
1714 --------------------------------------------------------------------------------
1715 PROCEDURE default_unchanged_uf_details(
1716 p_x_uf_details_tbl IN OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type
1717 ) IS
1718
1719 CURSOR uf_details_csr(p_uf_detail_id IN NUMBER, p_object_version_number IN NUMBER) IS
1720 SELECT uf_header_id,end_date,attribute_category, attribute1,attribute2, attribute3, attribute4,
1721 attribute5, attribute6, attribute7, attribute8, attribute9, attribute10, attribute11,
1722 attribute12, attribute13, attribute14, attribute15
1723 FROM ahl_uf_details
1724 WHERE object_version_number= p_object_version_number
1725 AND uf_detail_id = p_uf_detail_id;
1726
1727 l_uf_details_rec AHL_UMP_UF_PVT.uf_details_rec_type;
1728
1729 BEGIN
1730 FOR i IN p_x_uf_details_tbl.FIRST..p_x_uf_details_tbl.LAST LOOP
1731 IF(p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_UPDATE) THEN
1732 OPEN uf_details_csr(p_x_uf_details_tbl(i).uf_detail_id, p_x_uf_details_tbl(i).object_version_number);
1733 FETCH uf_details_csr INTO l_uf_details_rec.uf_header_id,l_uf_details_rec.end_date,l_uf_details_rec.attribute_category,l_uf_details_rec.attribute1,l_uf_details_rec.attribute2,
1734 l_uf_details_rec.attribute3, l_uf_details_rec.attribute4, l_uf_details_rec.attribute5, l_uf_details_rec.attribute6, l_uf_details_rec.attribute7,
1735 l_uf_details_rec.attribute8, l_uf_details_rec.attribute9, l_uf_details_rec.attribute10, l_uf_details_rec.attribute11, l_uf_details_rec.attribute12,
1736 l_uf_details_rec.attribute13, l_uf_details_rec.attribute14, l_uf_details_rec.attribute15;
1737 IF (uf_details_csr%NOTFOUND) THEN
1738 FND_MESSAGE.Set_Name(G_APP_NAME,'AHL_UMP_UF_DET_OBJV_MIS');
1739 FND_MSG_PUB.ADD;
1740 ELSE
1741 IF (p_x_uf_details_tbl(i).uf_header_id is null) THEN
1742 p_x_uf_details_tbl(i).uf_header_id := l_uf_details_rec.uf_header_id;
1743 ELSIF(p_x_uf_details_tbl(i).uf_header_id = FND_API.G_MISS_NUM) THEN
1744 p_x_uf_details_tbl(i).uf_header_id := null;
1745 END IF;
1746
1747 IF (p_x_uf_details_tbl(i).end_date is null) THEN
1748 p_x_uf_details_tbl(i).end_date := l_uf_details_rec.end_date;
1749 ELSIF(p_x_uf_details_tbl(i).end_date = FND_API.G_MISS_DATE) THEN
1750 p_x_uf_details_tbl(i).end_date := null;
1751 END IF;
1752
1753 IF (p_x_uf_details_tbl(i).attribute_category is null) THEN
1754 p_x_uf_details_tbl(i).attribute_category := l_uf_details_rec.attribute_category;
1755 ELSIF(p_x_uf_details_tbl(i).attribute_category = FND_API.G_MISS_CHAR) THEN
1756 p_x_uf_details_tbl(i).attribute_category := null;
1757 END IF;
1758
1759 IF (p_x_uf_details_tbl(i).attribute1 is null) THEN
1760 p_x_uf_details_tbl(i).attribute1 := l_uf_details_rec.attribute1;
1761 ELSIF(p_x_uf_details_tbl(i).attribute1 = FND_API.G_MISS_CHAR) THEN
1762 p_x_uf_details_tbl(i).attribute1 := null;
1763 END IF;
1764
1765 IF (p_x_uf_details_tbl(i).attribute2 is null) THEN
1766 p_x_uf_details_tbl(i).attribute2 := l_uf_details_rec.attribute2;
1767 ELSIF(p_x_uf_details_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
1768 p_x_uf_details_tbl(i).attribute2 := null;
1769 END IF;
1770
1771 IF (p_x_uf_details_tbl(i).attribute3 is null) THEN
1772 p_x_uf_details_tbl(i).attribute3 := l_uf_details_rec.attribute3;
1773 ELSIF(p_x_uf_details_tbl(i).attribute3 = FND_API.G_MISS_CHAR) THEN
1774 p_x_uf_details_tbl(i).attribute3 := null;
1775 END IF;
1776
1777 IF (p_x_uf_details_tbl(i).attribute4 is null) THEN
1778 p_x_uf_details_tbl(i).attribute4 := l_uf_details_rec.attribute4;
1779 ELSIF(p_x_uf_details_tbl(i).attribute4 = FND_API.G_MISS_CHAR) THEN
1780 p_x_uf_details_tbl(i).attribute4 := null;
1781 END IF;
1782
1783 IF (p_x_uf_details_tbl(i).attribute5 is null) THEN
1784 p_x_uf_details_tbl(i).attribute5 := l_uf_details_rec.attribute5;
1785 ELSIF(p_x_uf_details_tbl(i).attribute5 = FND_API.G_MISS_CHAR) THEN
1786 p_x_uf_details_tbl(i).attribute5 := null;
1787 END IF;
1788
1789 IF (p_x_uf_details_tbl(i).attribute6 is null) THEN
1790 p_x_uf_details_tbl(i).attribute6 := l_uf_details_rec.attribute6;
1791 ELSIF(p_x_uf_details_tbl(i).attribute6 = FND_API.G_MISS_CHAR) THEN
1792 p_x_uf_details_tbl(i).attribute6 := null;
1793 END IF;
1794
1795 IF (p_x_uf_details_tbl(i).attribute7 is null) THEN
1796 p_x_uf_details_tbl(i).attribute7 := l_uf_details_rec.attribute7;
1797 ELSIF(p_x_uf_details_tbl(i).attribute7 = FND_API.G_MISS_CHAR) THEN
1798 p_x_uf_details_tbl(i).attribute7 := null;
1799 END IF;
1800
1801 IF (p_x_uf_details_tbl(i).attribute8 is null) THEN
1802 p_x_uf_details_tbl(i).attribute8 := l_uf_details_rec.attribute8;
1803 ELSIF(p_x_uf_details_tbl(i).attribute8 = FND_API.G_MISS_CHAR) THEN
1804 p_x_uf_details_tbl(i).attribute8 := null;
1805 END IF;
1806
1807 IF (p_x_uf_details_tbl(i).attribute9 is null) THEN
1808 p_x_uf_details_tbl(i).attribute9 := l_uf_details_rec.attribute9;
1809 ELSIF(p_x_uf_details_tbl(i).attribute9 = FND_API.G_MISS_CHAR) THEN
1810 p_x_uf_details_tbl(i).attribute9 := null;
1811 END IF;
1812
1813 IF (p_x_uf_details_tbl(i).attribute10 is null) THEN
1814 p_x_uf_details_tbl(i).attribute10 := l_uf_details_rec.attribute10;
1815 ELSIF(p_x_uf_details_tbl(i).attribute10 = FND_API.G_MISS_CHAR) THEN
1816 p_x_uf_details_tbl(i).attribute10 := null;
1817 END IF;
1818
1819 IF (p_x_uf_details_tbl(i).attribute11 is null) THEN
1820 p_x_uf_details_tbl(i).attribute11 := l_uf_details_rec.attribute11;
1821 ELSIF(p_x_uf_details_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
1822 p_x_uf_details_tbl(i).attribute11 := null;
1823 END IF;
1824
1825 IF (p_x_uf_details_tbl(i).attribute12 is null) THEN
1826 p_x_uf_details_tbl(i).attribute12 := l_uf_details_rec.attribute12;
1827 ELSIF(p_x_uf_details_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
1828 p_x_uf_details_tbl(i).attribute12 := null;
1829 END IF;
1830
1831 IF (p_x_uf_details_tbl(i).attribute13 is null) THEN
1832 p_x_uf_details_tbl(i).attribute13 := l_uf_details_rec.attribute13;
1833 ELSIF(p_x_uf_details_tbl(i).attribute13 = FND_API.G_MISS_CHAR) THEN
1834 p_x_uf_details_tbl(i).attribute13 := null;
1835 END IF;
1836
1837 IF (p_x_uf_details_tbl(i).attribute14 is null) THEN
1838 p_x_uf_details_tbl(i).attribute14 := l_uf_details_rec.attribute14;
1839 ELSIF(p_x_uf_details_tbl(i).attribute14 = FND_API.G_MISS_CHAR) THEN
1840 p_x_uf_details_tbl(i).attribute14 := null;
1841 END IF;
1842
1843 IF (p_x_uf_details_tbl(i).attribute15 is null) THEN
1844 p_x_uf_details_tbl(i).attribute15 := l_uf_details_rec.attribute15;
1845 ELSIF(p_x_uf_details_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
1846 p_x_uf_details_tbl(i).attribute15 := null;
1847 END IF;
1848
1849 END IF;
1850 CLOSE uf_details_csr;
1851 ELSIF (p_x_uf_details_tbl(i).operation_flag = AHL_UMP_UF_PVT.G_OP_CREATE) THEN
1852
1853 IF(p_x_uf_details_tbl(i).uf_header_id = FND_API.G_MISS_NUM) THEN
1854 p_x_uf_details_tbl(i).uf_header_id := null;
1855 END IF;
1856
1857 IF(p_x_uf_details_tbl(i).end_date = FND_API.G_MISS_DATE) THEN
1858 p_x_uf_details_tbl(i).end_date := null;
1859 END IF;
1860 IF (p_x_uf_details_tbl(i).attribute_category = FND_API.G_MISS_CHAR) THEN
1861 p_x_uf_details_tbl(i).attribute_category := null;
1862 END IF;
1863 IF (p_x_uf_details_tbl(i).attribute1 = FND_API.G_MISS_CHAR) THEN
1864 p_x_uf_details_tbl(i).attribute1 := null;
1865 END IF;
1866 IF (p_x_uf_details_tbl(i).attribute2 = FND_API.G_MISS_CHAR) THEN
1867 p_x_uf_details_tbl(i).attribute2 := null;
1868 END IF;
1869 IF (p_x_uf_details_tbl(i).attribute3 = FND_API.G_MISS_CHAR) THEN
1870 p_x_uf_details_tbl(i).attribute3 := null;
1871 END IF;
1872 IF (p_x_uf_details_tbl(i).attribute4 = FND_API.G_MISS_CHAR) THEN
1873 p_x_uf_details_tbl(i).attribute4 := null;
1874 END IF;
1875 IF (p_x_uf_details_tbl(i).attribute5 = FND_API.G_MISS_CHAR) THEN
1876 p_x_uf_details_tbl(i).attribute5 := null;
1877 END IF;
1878 IF (p_x_uf_details_tbl(i).attribute6 = FND_API.G_MISS_CHAR) THEN
1879 p_x_uf_details_tbl(i).attribute6 := null;
1880 END IF;
1881 IF (p_x_uf_details_tbl(i).attribute7 = FND_API.G_MISS_CHAR) THEN
1882 p_x_uf_details_tbl(i).attribute7 := null;
1883 END IF;
1884 IF (p_x_uf_details_tbl(i).attribute8 = FND_API.G_MISS_CHAR) THEN
1885 p_x_uf_details_tbl(i).attribute8 := null;
1886 END IF;
1887 IF (p_x_uf_details_tbl(i).attribute9 = FND_API.G_MISS_CHAR) THEN
1888 p_x_uf_details_tbl(i).attribute9 := null;
1889 END IF;
1890 IF (p_x_uf_details_tbl(i).attribute10 = FND_API.G_MISS_CHAR) THEN
1891 p_x_uf_details_tbl(i).attribute10 := null;
1892 END IF;
1893 IF (p_x_uf_details_tbl(i).attribute11 = FND_API.G_MISS_CHAR) THEN
1894 p_x_uf_details_tbl(i).attribute11 := null;
1895 END IF;
1896 IF (p_x_uf_details_tbl(i).attribute12 = FND_API.G_MISS_CHAR) THEN
1897 p_x_uf_details_tbl(i).attribute12 := null;
1898 END IF;
1899 IF (p_x_uf_details_tbl(i).attribute13 = FND_API.G_MISS_CHAR) THEN
1900 p_x_uf_details_tbl(i).attribute13 := null;
1901 END IF;
1902 IF (p_x_uf_details_tbl(i).attribute14 = FND_API.G_MISS_CHAR) THEN
1903 p_x_uf_details_tbl(i).attribute14 := null;
1904 END IF;
1905 IF (p_x_uf_details_tbl(i).attribute15 = FND_API.G_MISS_CHAR) THEN
1906 p_x_uf_details_tbl(i).attribute15 := null;
1907 END IF;
1908
1909 END IF;
1910 END LOOP;
1911
1912 END default_unchanged_uf_details;
1913 -------------------------------------------------------------------------------------
1914 -- This procedure deletes header if no forecast detials and use unit flag is also 'N'
1915 -------------------------------------------------------------------------------------
1916 PROCEDURE post_process_uf_header(
1917 p_uf_header_rec IN AHL_UMP_UF_PVT.uf_header_Rec_type
1918 ) IS
1919
1920 CURSOR uf_details_csr (p_uf_header_id IN NUMBER) IS
1921 SELECT 'x'
1922 FROM ahl_uf_details WHERE uf_header_id = p_uf_header_id;
1923
1924 l_exist VARCHAR2(1);
1925
1926
1927 BEGIN
1928 IF(p_uf_header_rec.use_unit_flag IS NULL OR p_uf_header_rec.use_unit_flag = G_UF_USE_UNIT_DEFAULT) THEN
1929 OPEN uf_details_csr (p_uf_header_rec.uf_header_id);
1930 FETCH uf_details_csr INTO l_exist;
1931 IF(uf_details_csr%NOTFOUND) THEN
1932 AHL_UF_HEADERS_PKG.delete_row(p_uf_header_rec.uf_header_id);
1933 END IF;
1934 CLOSE uf_details_csr;
1935 END IF;
1936 END post_process_uf_header;
1937
1938
1939
1940 ----------------------------------------------------------------------
1941 -- Procedure to get Utilzation Forecast from Product Classification --
1942 ----------------------------------------------------------------------
1943 -- added parameter p_add_unit_item_forecast to fix bug# 6749351.
1944 -- This flag is currently being applied to only item based forecasts.
1945 PROCEDURE get_uf_from_pc (
1946 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1947 p_pc_node_id IN NUMBER := NULL,
1948 p_inventory_item_id IN NUMBER := NULL,
1949 p_inventory_org_id IN NUMBER := NULL,
1950 p_unit_config_header_id IN NUMBER := NULL,
1951 p_unit_name IN VARCHAR2:=NULL,
1952 p_part_number IN VARCHAR2 :=NULL,
1953 p_onward_end_date IN DATE := NULL,
1954 p_add_unit_item_forecast IN VARCHAR2 := 'N',
1955 x_UF_details_tbl OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type,
1956 x_return_status OUT NOCOPY VARCHAR2)
1957 IS
1958 --
1959 CURSOR ahl_get_config_header_id_csr(c_unit_name IN VARCHAR2) IS
1960 SELECT unit_config_header_id
1961 FROM ahl_unit_config_headers
1962 WHERE name = c_unit_name;
1963 --
1964 CURSOR ahl_get_inv_item_id_csr(c_part_number IN mtl_system_items_kfv.concatenated_segments%TYPE) IS
1965 SELECT inventory_item_id
1966 FROM mtl_system_items_kfv
1967 WHERE concatenated_segments = c_part_number;
1968 --
1969 CURSOR ahl_pc_id_from_assoc_csr(c_unit_config_header_id IN NUMBER,
1970 c_type IN VARCHAR2,
1971 c_primary_flag IN VARCHAR2,
1972 c_complete_status IN VARCHAR2 ) IS
1973 SELECT a.pc_node_id
1974 FROM ahl_pc_associations a, ahl_pc_nodes_b b, ahl_pc_headers_b c
1975 WHERE a.unit_item_id = c_unit_config_header_id
1976 AND a.association_type_flag = c_type
1977 AND a.pc_node_id = b.pc_node_id
1978 AND b.pc_header_id = c.pc_header_id
1979 AND c.primary_flag = c_primary_flag
1980 AND c.status = c_complete_status;
1981 --
1982 CURSOR ahl_item_id_from_uc_header_csr(c_uc_header_id IN NUMBER) IS
1983 SELECT a.inventory_item_id
1984 FROM csi_item_instances a, ahl_unit_config_headers b
1985 WHERE a.instance_id = b.csi_item_instance_id
1986 AND b.unit_config_header_id = c_uc_header_id;
1987 --
1988 CURSOR ahl_check_pc_id_csr(p_pc_node_id IN NUMBER,
1989 c_primary_flag IN VARCHAR2,
1990 c_complete_status IN VARCHAR2 ) IS
1991 SELECT 'x'
1992 FROM ahl_pc_nodes_b a, ahl_pc_headers_b b
1993 WHERE a.pc_node_id = p_pc_node_id
1994 AND a.pc_header_id = b.pc_header_id
1995 AND b.primary_flag = c_primary_flag
1996 AND b.status = c_complete_status;
1997 --
1998 CURSOR ahl_trav_pc_nodes_csr (c_pc_id IN NUMBER) IS
1999 SELECT pc_node_id
2000 FROM ahl_pc_nodes_b
2001 START WITH pc_node_id = c_pc_id
2002 CONNECT BY pc_node_id = PRIOR parent_node_id;
2003 --
2004 CURSOR ahl_uf_uom_nodes_csr (c_pc_id IN NUMBER) IS
2005 SELECT distinct a.uom_code
2006 FROM ahl_uf_details a, ahl_uf_headers b
2007 WHERE a.uf_header_id = b.uf_header_id
2008 AND b.pc_node_id = c_pc_id;
2009 --
2010 CURSOR ahl_uf_details_csr (c_pc_id IN NUMBER, p_uom_code IN VARCHAR2) IS
2011 SELECT a.UF_DETAIL_ID,
2012 a.OBJECT_VERSION_NUMBER,
2013 a.LAST_UPDATE_DATE ,
2014 a.LAST_UPDATED_BY ,
2015 a.CREATION_DATE ,
2016 a.CREATED_BY ,
2017 a.LAST_UPDATE_LOGIN ,
2018 a.UF_HEADER_ID ,
2019 a.UOM_CODE ,
2020 a.START_DATE ,
2021 a.END_DATE ,
2022 a.USAGE_PER_DAY ,
2023 a.ATTRIBUTE_CATEGORY ,
2024 a.ATTRIBUTE1 ,
2025 a.ATTRIBUTE2 ,
2026 a.ATTRIBUTE3 ,
2027 a.ATTRIBUTE4 ,
2028 a.ATTRIBUTE5 ,
2029 a.ATTRIBUTE6 ,
2030 a.ATTRIBUTE7 ,
2031 a.ATTRIBUTE8 ,
2032 a.ATTRIBUTE9 ,
2033 a.ATTRIBUTE10 ,
2034 a.ATTRIBUTE11 ,
2035 a.ATTRIBUTE12 ,
2036 a.ATTRIBUTE13 ,
2037 a.ATTRIBUTE14 ,
2038 a.ATTRIBUTE15
2039 FROM ahl_uf_details a, ahl_uf_headers b
2040 WHERE a.uom_code = p_uom_code
2041 AND b.pc_node_id = c_pc_id
2042 AND a.uf_header_id = b.uf_header_id;
2043 --
2044 CURSOR ahl_uf_details_date_csr (p_pc_id IN NUMBER, p_uom_code IN VARCHAR2,p_onward_end_date IN DATE) IS
2045 SELECT a.UF_DETAIL_ID,
2046 a.OBJECT_VERSION_NUMBER,
2047 a.LAST_UPDATE_DATE ,
2048 a.LAST_UPDATED_BY ,
2049 a.CREATION_DATE ,
2050 a.CREATED_BY ,
2051 a.LAST_UPDATE_LOGIN ,
2052 a.UF_HEADER_ID ,
2053 a.UOM_CODE ,
2054 a.START_DATE ,
2055 a.END_DATE ,
2056 a.USAGE_PER_DAY ,
2057 a.ATTRIBUTE_CATEGORY ,
2058 a.ATTRIBUTE1 ,
2059 a.ATTRIBUTE2 ,
2060 a.ATTRIBUTE3 ,
2061 a.ATTRIBUTE4 ,
2062 a.ATTRIBUTE5 ,
2063 a.ATTRIBUTE6 ,
2064 a.ATTRIBUTE7 ,
2065 a.ATTRIBUTE8 ,
2066 a.ATTRIBUTE9 ,
2067 a.ATTRIBUTE10 ,
2068 a.ATTRIBUTE11 ,
2069 a.ATTRIBUTE12 ,
2070 a.ATTRIBUTE13 ,
2071 a.ATTRIBUTE14 ,
2072 a.ATTRIBUTE15
2073 FROM ahl_uf_details a, ahl_uf_headers b
2074 WHERE (a.end_date IS NULL OR TRUNC(a.end_date) >= TRUNC(p_onward_end_date))
2075 AND a.uf_header_id = b.uf_header_id
2076 AND a.uom_code = p_uom_code
2077 AND b.pc_node_id = p_pc_id;
2078
2079 -- added to fix bug# 6749351
2080 CURSOR ahl_uf_uom_item_csr (p_inventory_item_id IN NUMBER) IS
2081 SELECT distinct a.uom_code
2082 FROM ahl_uf_details a, ahl_uf_headers b
2083 WHERE a.uf_header_id = b.uf_header_id
2084 AND b.inventory_item_id = p_inventory_item_id;
2085
2086 -- get uf details for item for all dates.
2087 CURSOR ahl_uf_item_details_csr(p_inventory_item_id IN NUMBER,
2088 p_uom_code IN VARCHAR2) IS
2089 SELECT a.UF_DETAIL_ID,
2090 a.OBJECT_VERSION_NUMBER,
2091 a.LAST_UPDATE_DATE ,
2092 a.LAST_UPDATED_BY ,
2093 a.CREATION_DATE ,
2094 a.CREATED_BY ,
2095 a.LAST_UPDATE_LOGIN ,
2096 a.UF_HEADER_ID ,
2097 a.UOM_CODE ,
2098 a.START_DATE ,
2099 a.END_DATE ,
2100 a.USAGE_PER_DAY ,
2101 a.ATTRIBUTE_CATEGORY ,
2102 a.ATTRIBUTE1 ,
2103 a.ATTRIBUTE2 ,
2104 a.ATTRIBUTE3 ,
2105 a.ATTRIBUTE4 ,
2106 a.ATTRIBUTE5 ,
2107 a.ATTRIBUTE6 ,
2108 a.ATTRIBUTE7 ,
2109 a.ATTRIBUTE8 ,
2110 a.ATTRIBUTE9 ,
2111 a.ATTRIBUTE10 ,
2112 a.ATTRIBUTE11 ,
2113 a.ATTRIBUTE12 ,
2114 a.ATTRIBUTE13 ,
2115 a.ATTRIBUTE14 ,
2116 a.ATTRIBUTE15
2117 FROM ahl_uf_details a, ahl_uf_headers b
2118 WHERE a.uf_header_id = b.uf_header_id
2119 AND a.uom_code = p_uom_code
2120 AND b.inventory_item_id = p_inventory_item_id;
2121
2122 -- get uf details for item based on p_onward_end_date.
2123 CURSOR ahl_uf_item_details_date_csr(p_inventory_item_id IN NUMBER,
2124 p_uom_code IN VARCHAR2,
2125 p_onward_end_date IN DATE) IS
2126 SELECT a.UF_DETAIL_ID,
2127 a.OBJECT_VERSION_NUMBER,
2128 a.LAST_UPDATE_DATE ,
2129 a.LAST_UPDATED_BY ,
2130 a.CREATION_DATE ,
2131 a.CREATED_BY ,
2132 a.LAST_UPDATE_LOGIN ,
2133 a.UF_HEADER_ID ,
2134 a.UOM_CODE ,
2135 a.START_DATE ,
2136 a.END_DATE ,
2137 a.USAGE_PER_DAY ,
2138 a.ATTRIBUTE_CATEGORY ,
2139 a.ATTRIBUTE1 ,
2140 a.ATTRIBUTE2 ,
2141 a.ATTRIBUTE3 ,
2142 a.ATTRIBUTE4 ,
2143 a.ATTRIBUTE5 ,
2144 a.ATTRIBUTE6 ,
2145 a.ATTRIBUTE7 ,
2146 a.ATTRIBUTE8 ,
2147 a.ATTRIBUTE9 ,
2148 a.ATTRIBUTE10 ,
2149 a.ATTRIBUTE11 ,
2150 a.ATTRIBUTE12 ,
2151 a.ATTRIBUTE13 ,
2152 a.ATTRIBUTE14 ,
2153 a.ATTRIBUTE15
2154 FROM ahl_uf_details a, ahl_uf_headers b
2155 WHERE (a.end_date IS NULL OR TRUNC(a.end_date) >= TRUNC(p_onward_end_date))
2156 AND a.uf_header_id = b.uf_header_id
2157 AND a.uom_code = p_uom_code
2158 AND b.inventory_item_id = p_inventory_item_id;
2159
2160 -- added to fix bug# 6749351
2161 CURSOR ahl_uf_uom_unit_csr (p_unit_config_id IN NUMBER) IS
2162 SELECT distinct a.uom_code
2163 FROM ahl_uf_details a, ahl_uf_headers b
2164 WHERE a.uf_header_id = b.uf_header_id
2165 AND b.unit_config_header_id = p_unit_config_id;
2166
2167 -- get uf details for item for all dates.
2168 CURSOR ahl_uf_unit_details_csr(p_unit_config_id IN NUMBER,
2169 p_uom_code IN VARCHAR2) IS
2170 SELECT a.UF_DETAIL_ID,
2171 a.OBJECT_VERSION_NUMBER,
2172 a.LAST_UPDATE_DATE ,
2173 a.LAST_UPDATED_BY ,
2174 a.CREATION_DATE ,
2175 a.CREATED_BY ,
2176 a.LAST_UPDATE_LOGIN ,
2177 a.UF_HEADER_ID ,
2178 a.UOM_CODE ,
2179 a.START_DATE ,
2180 a.END_DATE ,
2181 a.USAGE_PER_DAY ,
2182 a.ATTRIBUTE_CATEGORY ,
2183 a.ATTRIBUTE1 ,
2184 a.ATTRIBUTE2 ,
2185 a.ATTRIBUTE3 ,
2186 a.ATTRIBUTE4 ,
2187 a.ATTRIBUTE5 ,
2188 a.ATTRIBUTE6 ,
2189 a.ATTRIBUTE7 ,
2190 a.ATTRIBUTE8 ,
2191 a.ATTRIBUTE9 ,
2192 a.ATTRIBUTE10 ,
2193 a.ATTRIBUTE11 ,
2194 a.ATTRIBUTE12 ,
2195 a.ATTRIBUTE13 ,
2196 a.ATTRIBUTE14 ,
2197 a.ATTRIBUTE15
2198 FROM ahl_uf_details a, ahl_uf_headers b
2199 WHERE a.uf_header_id = b.uf_header_id
2200 AND a.uom_code = p_uom_code
2201 AND b.unit_config_header_id = p_unit_config_id;
2202
2203 -- get uf details for item based on p_onward_end_date.
2204 CURSOR ahl_uf_unit_details_date_csr(p_unit_config_id IN NUMBER,
2205 p_uom_code IN VARCHAR2,
2206 p_onward_end_date IN DATE) IS
2207 SELECT a.UF_DETAIL_ID,
2208 a.OBJECT_VERSION_NUMBER,
2209 a.LAST_UPDATE_DATE ,
2210 a.LAST_UPDATED_BY ,
2211 a.CREATION_DATE ,
2212 a.CREATED_BY ,
2213 a.LAST_UPDATE_LOGIN ,
2214 a.UF_HEADER_ID ,
2215 a.UOM_CODE ,
2216 a.START_DATE ,
2217 a.END_DATE ,
2218 a.USAGE_PER_DAY ,
2219 a.ATTRIBUTE_CATEGORY ,
2220 a.ATTRIBUTE1 ,
2221 a.ATTRIBUTE2 ,
2222 a.ATTRIBUTE3 ,
2223 a.ATTRIBUTE4 ,
2224 a.ATTRIBUTE5 ,
2225 a.ATTRIBUTE6 ,
2226 a.ATTRIBUTE7 ,
2227 a.ATTRIBUTE8 ,
2228 a.ATTRIBUTE9 ,
2229 a.ATTRIBUTE10 ,
2230 a.ATTRIBUTE11 ,
2231 a.ATTRIBUTE12 ,
2232 a.ATTRIBUTE13 ,
2233 a.ATTRIBUTE14 ,
2234 a.ATTRIBUTE15
2235 FROM ahl_uf_details a, ahl_uf_headers b
2236 WHERE (a.end_date IS NULL OR TRUNC(a.end_date) >= TRUNC(p_onward_end_date))
2237 AND a.uf_header_id = b.uf_header_id
2238 AND a.uom_code = p_uom_code
2239 AND b.unit_config_header_id = p_unit_config_id;
2240 --
2241 l_UF_details_tbl AHL_UMP_UF_PVT.uf_details_tbl_type;
2242 l_UF_details_rec AHL_UMP_UF_PVT.uf_details_rec_type;
2243 l_temp_details_rec ahl_uf_details_csr%ROWTYPE;
2244 l_unit_config_header_id NUMBER DEFAULT p_unit_config_header_id;
2245 l_inventory_item_id NUMBER DEFAULT p_inventory_item_id;
2246 l_pc_node_id NUMBER DEFAULT p_PC_node_id;
2247 l_pc_id NUMBER;
2248 l_uom_code AHL_UF_DETAILS.UOM_CODE%TYPE;
2249 l_msg_count NUMBER;
2250 l_duplicate VARCHAR2(1);
2251 l_junk VARCHAR2(1);
2252 j NUMBER;
2253 --
2254 BEGIN
2255
2256 -- Initialize Procedure return status to success
2257 -- Initialize message list if p_init_msg_list is set to TRUE
2258 IF FND_API.To_Boolean( p_init_msg_list)
2259 THEN
2260 FND_MSG_PUB.Initialize;
2261 END IF;
2262
2263 -- Initialize API return status to success
2264 x_return_status := FND_API.G_RET_STS_SUCCESS; j := 0;
2265
2266 --Part 1. Resolve all possible inputs to l_pc_node_id
2267
2268 --Resolve unit name into unit config header id
2269 IF (p_unit_config_header_id IS NULL AND p_unit_name IS NOT NULL) THEN
2270 OPEN ahl_get_config_header_id_csr(p_unit_name);
2271 FETCH ahl_get_config_header_id_csr INTO l_unit_config_header_id;
2272 CLOSE ahl_get_config_header_id_csr;
2273 END IF;
2274
2275 --Resolve part number into inventory item id
2276 IF (p_inventory_item_id IS NULL AND p_part_number IS NOT NULL) THEN
2277 OPEN ahl_get_inv_item_id_csr(p_part_number);
2278 FETCH ahl_get_inv_item_id_csr INTO l_inventory_item_id;
2279 CLOSE ahl_get_inv_item_id_csr;
2280 END IF;
2281
2282 --Verify that exactly one of the IDs is defined.
2283 IF ((l_pc_node_id IS NULL AND
2284 l_inventory_item_id IS NULL AND
2285 l_unit_config_header_id IS NULL)
2286 OR (l_pc_node_id IS NOT NULL AND
2287 l_inventory_item_id IS NOT NULL)
2288 OR (l_pc_node_id IS NOT NULL AND
2289 l_unit_config_header_id IS NOT NULL)
2290 OR (l_unit_config_header_id IS NOT NULL AND
2291 l_inventory_item_id IS NOT NULL) ) THEN
2292 FND_MESSAGE.Set_Name('AHL','AHL_UMP_UTIL_ONLY_ONE_ID');
2293 FND_MSG_PUB.ADD;
2294 END IF;
2295
2296 l_msg_count := FND_MSG_PUB.count_msg;
2297 IF l_msg_count > 0 THEN
2298 x_return_status := FND_API.G_RET_STS_ERROR;
2299 RETURN;
2300 END IF;
2301
2302 --Now verify pc node id is in 'COMPLETE' and 'PRIMARY' PC tree
2303 IF (l_pc_node_id IS NOT NULL) THEN
2304 OPEN ahl_check_pc_id_csr(l_pc_node_id,
2305 AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG,
2306 AHL_UMP_UF_PVT.G_COMPLETE_STATUS);
2307 FETCH ahl_check_pc_id_csr INTO l_junk;
2308 IF (ahl_check_pc_id_csr%NOTFOUND) THEN
2309 l_pc_node_id := NULL;
2310 -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UTIL_NO_PRIM_PC');
2311 -- FND_MESSAGE.Set_Token('UID',l_pc_node_id);
2312 -- FND_MSG_PUB.ADD;
2313 END IF;
2314 CLOSE ahl_check_pc_id_csr;
2315 END IF;
2316
2317 --Resolve unit_config_header_id into a pc node id
2318 IF (l_unit_config_header_id IS NOT NULL) THEN
2319 OPEN ahl_pc_id_from_assoc_csr(l_unit_config_header_id,
2320 AHL_UMP_UF_PVT.G_PC_UNIT_ASSOCIATION,
2321 AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG,
2322 AHL_UMP_UF_PVT.G_COMPLETE_STATUS);
2323 FETCH ahl_pc_id_from_assoc_csr INTO l_pc_node_id;
2324 IF (ahl_pc_id_from_assoc_csr%NOTFOUND) THEN
2325 l_pc_node_id := NULL;
2326
2327 --If can not find PC as unit, then try it with item
2328 OPEN ahl_item_id_from_uc_header_csr(l_unit_config_header_id);
2329 FETCH ahl_item_id_from_uc_header_csr INTO l_inventory_item_id;
2330 CLOSE ahl_item_id_from_uc_header_csr;
2331
2332 -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UTIL_NO_PRIM_PC');
2333 -- FND_MESSAGE.Set_Token('UID',l_unit_config_header_id);
2334 -- FND_MSG_PUB.ADD;
2335 END IF;
2336 CLOSE ahl_pc_id_from_assoc_csr;
2337
2338 /*
2339 -- Added to fix bug# 6749351
2340 IF (p_add_unit_item_forecast = 'Y') THEN
2341 OPEN ahl_uf_uom_unit_csr(l_unit_config_header_id);
2342 LOOP
2343 FETCH ahl_uf_uom_unit_csr INTO l_uom_code;
2344 EXIT WHEN ahl_uf_uom_unit_csr%NOTFOUND;
2345 IF(p_onward_end_date IS NULL) THEN
2346 OPEN ahl_uf_unit_details_csr(l_unit_config_header_id, l_uom_code);
2347 LOOP
2348 FETCH ahl_uf_unit_details_csr INTO l_temp_details_rec;
2349 EXIT WHEN ahl_uf_unit_details_csr%NOTFOUND;
2350
2351 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2352 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2353 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2354 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2355 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2356 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2357 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2358 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2359 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2360 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2361 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2362 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2363 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2364 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2365 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2366 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2367 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2368 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2369 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2370 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2371 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2372 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2373 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2374 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2375 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2376 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2377 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2378 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2379 l_UF_details_tbl(j) := l_UF_details_rec;
2380 j := j+1;
2381 END LOOP;
2382 CLOSE ahl_uf_unit_details_csr;
2383 ELSE
2384 OPEN ahl_uf_unit_details_date_csr(l_unit_config_header_id, l_uom_code,p_onward_end_date);
2385 LOOP
2386 FETCH ahl_uf_unit_details_date_csr INTO l_temp_details_rec;
2387 EXIT WHEN ahl_uf_unit_details_date_csr%NOTFOUND;
2388
2389 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2390 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2391 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2392 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2393 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2394 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2395 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2396 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2397 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2398 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2399 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2400 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2401 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2402 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2403 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2404 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2405 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2406 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2407 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2408 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2409 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2410 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2411 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2412 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2413 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2414 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2415 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2416 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2417
2418 l_UF_details_tbl(j) := l_UF_details_rec;
2419 j := j+1;
2420 END LOOP;
2421 CLOSE ahl_uf_unit_details_date_csr;
2422 END IF; --p_onward_date
2423 END LOOP;
2424 CLOSE ahl_uf_uom_unit_csr;
2425 END IF; -- p_unit_item_forecast
2426 */
2427 END IF; -- l_unit_config header_id.
2428
2429 --Or resolve inventory item id into l_pc_node_id
2430 IF (l_inventory_item_id IS NOT NULL) THEN
2431 OPEN ahl_pc_id_from_assoc_csr(l_inventory_item_id,
2432 AHL_UMP_UF_PVT.G_PC_ITEM_ASSOCIATION,
2433 AHL_UMP_UF_PVT.G_PC_PRIMARY_FLAG,
2434 AHL_UMP_UF_PVT.G_COMPLETE_STATUS);
2435 FETCH ahl_pc_id_from_assoc_csr INTO l_pc_node_id;
2436 IF (ahl_pc_id_from_assoc_csr%NOTFOUND) THEN
2437 l_pc_node_id := NULL;
2438 -- FND_MESSAGE.Set_Name('AHL','AHL_UMP_UTIL_NO_PRIM_PC');
2439 -- FND_MESSAGE.Set_Token('UID',l_inventory_item_id);
2440 -- FND_MSG_PUB.ADD;
2441 END IF;
2442 CLOSE ahl_pc_id_from_assoc_csr;
2443
2444 -- Added to fix bug# 6749351
2445 IF (p_add_unit_item_forecast = 'Y') THEN
2446 OPEN ahl_uf_uom_item_csr(l_inventory_item_id);
2447 LOOP
2448 FETCH ahl_uf_uom_item_csr INTO l_uom_code;
2449 EXIT WHEN ahl_uf_uom_item_csr%NOTFOUND;
2450 IF(p_onward_end_date IS NULL) THEN
2451 OPEN ahl_uf_item_details_csr(l_inventory_item_id, l_uom_code);
2452 LOOP
2453 FETCH ahl_uf_item_details_csr INTO l_temp_details_rec;
2454 EXIT WHEN ahl_uf_item_details_csr%NOTFOUND;
2455
2456 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2457 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2458 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2459 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2460 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2461 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2462 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2463 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2464 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2465 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2466 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2467 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2468 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2469 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2470 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2471 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2472 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2473 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2474 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2475 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2476 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2477 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2478 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2479 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2480 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2481 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2482 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2483 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2484 l_UF_details_tbl(j) := l_UF_details_rec;
2485 j := j+1;
2486 END LOOP;
2487 CLOSE ahl_uf_item_details_csr;
2488 ELSE
2489 OPEN ahl_uf_item_details_date_csr(l_inventory_item_id, l_uom_code,p_onward_end_date);
2490 LOOP
2491 FETCH ahl_uf_item_details_date_csr INTO l_temp_details_rec;
2492 EXIT WHEN ahl_uf_item_details_date_csr%NOTFOUND;
2493
2494 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2495 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2496 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2497 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2498 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2499 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2500 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2501 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2502 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2503 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2504 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2505 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2506 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2507 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2508 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2509 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2510 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2511 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2512 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2513 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2514 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2515 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2516 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2517 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2518 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2519 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2520 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2521 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2522
2523 l_UF_details_tbl(j) := l_UF_details_rec;
2524 j := j+1;
2525 END LOOP;
2526 CLOSE ahl_uf_item_details_date_csr;
2527 END IF; --p_onward_date
2528 END LOOP;
2529 CLOSE ahl_uf_uom_item_csr;
2530 END IF; -- p_unit_item_forecast
2531 END IF; -- l_inventory_item_id
2532
2533 --Return if the l_pc_node_id is NULL
2534 IF (l_pc_node_id IS NULL) THEN
2535 x_UF_details_tbl := l_UF_details_tbl;
2536 RETURN;
2537 END IF;
2538
2539 --Part 2. With l_pc_node_id, build forecast data.
2540 --Now traverse up the pc tree.
2541 OPEN ahl_trav_pc_nodes_csr(l_pc_node_id);
2542 LOOP
2543 FETCH ahl_trav_pc_nodes_csr INTO l_pc_id;
2544 EXIT WHEN ahl_trav_pc_nodes_csr%NOTFOUND;
2545 --Now fetch all unique UOM_code for given pc
2546 OPEN ahl_uf_uom_nodes_csr(l_pc_id);
2547 LOOP
2548 FETCH ahl_uf_uom_nodes_csr INTO l_uom_code;
2549 EXIT WHEN ahl_uf_uom_nodes_csr%NOTFOUND;
2550
2551 l_duplicate := 'N';
2552 IF (l_UF_details_tbl.COUNT > 0) THEN
2553 FOR i IN l_UF_details_tbl.FIRST..l_UF_details_tbl.LAST LOOP
2554 IF (l_UF_details_tbl(i).uom_code = l_uom_code) THEN
2555 l_duplicate := 'Y';
2556 EXIT;
2557 END IF;
2558 END LOOP;
2559 END IF;
2560
2561 -- If no duplicates are found for given uom, add to table all dates
2562 IF (l_duplicate = 'N') THEN
2563 IF(p_onward_end_date IS NULL) THEN
2564 OPEN ahl_uf_details_csr(l_pc_id, l_uom_code);
2565 LOOP
2566 FETCH ahl_uf_details_csr INTO l_temp_details_rec;
2567 EXIT WHEN ahl_uf_details_csr%NOTFOUND;
2568
2569 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2570 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2571 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2572 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2573 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2574 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2575 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2576 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2577 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2578 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2579 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2580 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2581 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2582 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2583 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2584 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2585 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2586 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2587 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2588 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2589 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2590 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2591 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2592 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2593 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2594 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2595 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2596 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2597 l_UF_details_tbl(j) := l_UF_details_rec;
2598 j := j+1;
2599 END LOOP;
2600 CLOSE ahl_uf_details_csr;
2601 ELSE
2602 OPEN ahl_uf_details_date_csr(l_pc_id, l_uom_code, p_onward_end_date);
2603 LOOP
2604 FETCH ahl_uf_details_date_csr INTO l_temp_details_rec;
2605 EXIT WHEN ahl_uf_details_date_csr%NOTFOUND;
2606
2607 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2608 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2609 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2610 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2611 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2612 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2613 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2614 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2615 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2616 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2617 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2618 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2619 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2620 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2621 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2622 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2623 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2624 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2625 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2626 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2627 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2628 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2629 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2630 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2631 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2632 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2633 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2634 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2635 l_UF_details_tbl(j) := l_UF_details_rec;
2636 j := j+1;
2637 END LOOP;
2638 CLOSE ahl_uf_details_date_csr;
2639 END IF;
2640 END IF;
2641
2642 END LOOP;
2643 CLOSE ahl_uf_uom_nodes_csr;
2644
2645
2646 END LOOP;
2647 CLOSE ahl_trav_pc_nodes_csr;
2648
2649 x_UF_details_tbl := l_UF_details_tbl;
2650
2651 END get_uf_from_pc;
2652
2653 PROCEDURE get_uf_from_part (
2654
2655 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2656 p_csi_item_instance_id IN NUMBER,
2657 p_onward_end_date IN DATE := NULL,
2658 x_UF_details_tbl OUT NOCOPY AHL_UMP_UF_PVT.uf_details_tbl_type,
2659 x_return_status OUT NOCOPY VARCHAR2)
2660 IS
2661
2662 --
2663 CURSOR ahl_uf_details_csr (p_csi_item_instance_id IN NUMBER) IS
2664 SELECT a.UF_DETAIL_ID,
2665 a.OBJECT_VERSION_NUMBER,
2666 a.LAST_UPDATE_DATE ,
2667 a.LAST_UPDATED_BY ,
2668 a.CREATION_DATE ,
2669 a.CREATED_BY ,
2670 a.LAST_UPDATE_LOGIN ,
2671 a.UF_HEADER_ID ,
2672 a.UOM_CODE ,
2673 a.START_DATE ,
2674 a.END_DATE ,
2675 a.USAGE_PER_DAY ,
2676 a.ATTRIBUTE_CATEGORY ,
2677 a.ATTRIBUTE1 ,
2678 a.ATTRIBUTE2 ,
2679 a.ATTRIBUTE3 ,
2680 a.ATTRIBUTE4 ,
2681 a.ATTRIBUTE5 ,
2682 a.ATTRIBUTE6 ,
2683 a.ATTRIBUTE7 ,
2684 a.ATTRIBUTE8 ,
2685 a.ATTRIBUTE9 ,
2686 a.ATTRIBUTE10 ,
2687 a.ATTRIBUTE11 ,
2688 a.ATTRIBUTE12 ,
2689 a.ATTRIBUTE13 ,
2690 a.ATTRIBUTE14 ,
2691 a.ATTRIBUTE15
2692 FROM ahl_uf_details a, ahl_uf_headers b,csi_item_instances c
2693 WHERE a.uf_header_id = b.uf_header_id
2694 AND b.inventory_item_id = c.inventory_item_id
2695 AND c.instance_id = p_csi_item_instance_id;
2696
2697 CURSOR ahl_uf_details_date_csr (p_csi_item_instance_id IN NUMBER, p_onward_end_date IN DATE) IS
2698 SELECT a.UF_DETAIL_ID,
2699 a.OBJECT_VERSION_NUMBER,
2700 a.LAST_UPDATE_DATE ,
2701 a.LAST_UPDATED_BY ,
2702 a.CREATION_DATE ,
2703 a.CREATED_BY ,
2704 a.LAST_UPDATE_LOGIN ,
2705 a.UF_HEADER_ID ,
2706 a.UOM_CODE ,
2707 a.START_DATE ,
2708 a.END_DATE ,
2709 a.USAGE_PER_DAY ,
2710 a.ATTRIBUTE_CATEGORY ,
2711 a.ATTRIBUTE1 ,
2712 a.ATTRIBUTE2 ,
2713 a.ATTRIBUTE3 ,
2714 a.ATTRIBUTE4 ,
2715 a.ATTRIBUTE5 ,
2716 a.ATTRIBUTE6 ,
2717 a.ATTRIBUTE7 ,
2718 a.ATTRIBUTE8 ,
2719 a.ATTRIBUTE9 ,
2720 a.ATTRIBUTE10 ,
2721 a.ATTRIBUTE11 ,
2722 a.ATTRIBUTE12 ,
2723 a.ATTRIBUTE13 ,
2724 a.ATTRIBUTE14 ,
2725 a.ATTRIBUTE15
2726 FROM ahl_uf_details a, ahl_uf_headers b,csi_item_instances c
2727 WHERE (a.end_date IS NULL OR TRUNC(a.end_date) >= TRUNC(p_onward_end_date))
2728 AND a.uf_header_id = b.uf_header_id
2729 AND b.inventory_item_id = c.inventory_item_id
2730 AND c.instance_id = p_csi_item_instance_id;
2731
2732
2733 l_UF_details_tbl AHL_UMP_UF_PVT.uf_details_tbl_type;
2734 l_UF_details_rec AHL_UMP_UF_PVT.uf_details_rec_type;
2735 l_temp_details_rec ahl_uf_details_csr%ROWTYPE;
2736 j NUMBER;
2737
2738
2739 BEGIN
2740
2741 IF FND_API.To_Boolean( p_init_msg_list)
2742 THEN
2743 FND_MSG_PUB.Initialize;
2744 END IF;
2745
2746 -- Initialize API return status to success
2747 x_return_status := FND_API.G_RET_STS_SUCCESS;
2748 j := 0;
2749 IF(p_onward_end_date IS NULL) THEN
2750 OPEN ahl_uf_details_csr(p_csi_item_instance_id);
2751 LOOP
2752 FETCH ahl_uf_details_csr INTO l_temp_details_rec;
2753 EXIT WHEN ahl_uf_details_csr%NOTFOUND;
2754
2755 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2756 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2757 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2758 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2759 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2760 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2761 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2762 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2763 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2764 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2765 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2766 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2767 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2768 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2769 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2770 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2771 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2772 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2773 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2774 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2775 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2776 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2777 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2778 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2779 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2780 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2781 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2782 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2783 l_UF_details_tbl(j) := l_UF_details_rec;
2784 j := j+1;
2785 END LOOP;
2786 CLOSE ahl_uf_details_csr;
2787 ELSE
2788 OPEN ahl_uf_details_date_csr(p_csi_item_instance_id, p_onward_end_date);
2789 LOOP
2790 FETCH ahl_uf_details_date_csr INTO l_temp_details_rec;
2791 EXIT WHEN ahl_uf_details_date_csr%NOTFOUND;
2792
2793 l_UF_details_rec.UF_DETAIL_ID := l_temp_details_rec.UF_DETAIL_ID;
2794 l_UF_details_rec.OBJECT_VERSION_NUMBER := l_temp_details_rec.OBJECT_VERSION_NUMBER ;
2795 l_UF_details_rec.LAST_UPDATE_DATE := l_temp_details_rec.LAST_UPDATE_DATE ;
2796 l_UF_details_rec.LAST_UPDATED_BY := l_temp_details_rec.LAST_UPDATED_BY ;
2797 l_UF_details_rec.CREATION_DATE := l_temp_details_rec.CREATION_DATE ;
2798 l_UF_details_rec.CREATED_BY := l_temp_details_rec.CREATED_BY ;
2799 l_UF_details_rec.LAST_UPDATE_LOGIN := l_temp_details_rec.LAST_UPDATE_LOGIN ;
2800 l_UF_details_rec.UF_HEADER_ID := l_temp_details_rec.UF_HEADER_ID ;
2801 l_UF_details_rec.UOM_CODE := l_temp_details_rec.UOM_CODE ;
2802 l_UF_details_rec.START_DATE := l_temp_details_rec.START_DATE ;
2803 l_UF_details_rec.END_DATE := l_temp_details_rec.END_DATE ;
2804 l_UF_details_rec.USAGE_PER_DAY := l_temp_details_rec.USAGE_PER_DAY ;
2805 l_UF_details_rec.ATTRIBUTE_CATEGORY := l_temp_details_rec.ATTRIBUTE_CATEGORY ;
2806 l_UF_details_rec.ATTRIBUTE1 := l_temp_details_rec.ATTRIBUTE1 ;
2807 l_UF_details_rec.ATTRIBUTE2 := l_temp_details_rec.ATTRIBUTE2 ;
2808 l_UF_details_rec.ATTRIBUTE3 := l_temp_details_rec.ATTRIBUTE3 ;
2809 l_UF_details_rec.ATTRIBUTE4 := l_temp_details_rec.ATTRIBUTE4 ;
2810 l_UF_details_rec.ATTRIBUTE5 := l_temp_details_rec.ATTRIBUTE5 ;
2811 l_UF_details_rec.ATTRIBUTE6 := l_temp_details_rec.ATTRIBUTE6 ;
2812 l_UF_details_rec.ATTRIBUTE7 := l_temp_details_rec.ATTRIBUTE7 ;
2813 l_UF_details_rec.ATTRIBUTE8 := l_temp_details_rec.ATTRIBUTE8 ;
2814 l_UF_details_rec.ATTRIBUTE9 := l_temp_details_rec.ATTRIBUTE9 ;
2815 l_UF_details_rec.ATTRIBUTE10 := l_temp_details_rec.ATTRIBUTE10 ;
2816 l_UF_details_rec.ATTRIBUTE11 := l_temp_details_rec.ATTRIBUTE11 ;
2817 l_UF_details_rec.ATTRIBUTE12 := l_temp_details_rec.ATTRIBUTE12 ;
2818 l_UF_details_rec.ATTRIBUTE13 := l_temp_details_rec.ATTRIBUTE13 ;
2819 l_UF_details_rec.ATTRIBUTE14 := l_temp_details_rec.ATTRIBUTE14 ;
2820 l_UF_details_rec.ATTRIBUTE15 := l_temp_details_rec.ATTRIBUTE15 ;
2821 l_UF_details_tbl(j) := l_UF_details_rec;
2822 j := j+1;
2823 END LOOP;
2824 CLOSE ahl_uf_details_date_csr;
2825 END IF;
2826
2827 x_UF_details_tbl := l_UF_details_tbl;
2828
2829
2830 END get_uf_from_part;
2831 -----------------------------------
2832 END AHL_UMP_UF_PVT;