1 PACKAGE BODY AHL_UC_UTILIZATION_PVT AS
2 /* $Header: AHLVUCUB.pls 120.8.12020000.2 2012/12/07 15:44:09 sareepar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UC_UTILIZATION_PVT';
5
6 G_LOG_PREFIX CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_UC_UTILIZATION_PVT';
7
8 -- Added by jaramana on 03-DEC-2008 to improve performance by localising FND_LOG package variables.
9 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12
13 ----------------------------------------------
14 -- Define local Procedures for Utilization --
15 ----------------------------------------------
16 -- Convert Value to ID.
17 PROCEDURE convert_value_id(p_utilization_rec IN OUT NOCOPY AHL_UC_UTILIZATION_PVT.utilization_rec_type);
18
19 -- Validate utilization record and get the two flag variables
20 PROCEDURE validate_utilization_rec(p_utilization_rec IN OUT NOCOPY AHL_UC_UTILIZATION_PVT.utilization_rec_type,
21 x_found OUT NOCOPY VARCHAR2,
22 x_based_on OUT NOCOPY VARCHAR2);
23
24 -- Update counter reading only for a given counter_id (p_cascade_flag is not applicable)
25 -- It is called by update_reading_ins and update_reading_all
26 PROCEDURE update_reading_id(p_utilization_rec IN utilization_rec_type);
27
28 -- Update counter readings for a given pair of counter_name and instance. It also update counter
29 -- readings for all the descendants of the start instance if p_cascade_flag is
30 -- set to Yes. The given start instance must have the p_counter_name associated. It is
31 -- called by update_reading_cn
32 PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type);
33
34 -- Update counter readings for a given given pair of counter_name and instance. It also update counter
35 -- readings for all the descendants of the start instance if p_cascade_flag is
36 -- set to Yes. The given start instance might not have the p_counter_name associated. It is
37 -- called by update_instance_all
38 PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type);
39
40 -- Update counter reading based on various inputs
41 PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
42 p_based_on IN VARCHAR2);
43
44 -- To get Counter ratio based on Master Configuration position.
45 FUNCTION get_counter_ratio(p_start_instance_id IN NUMBER,
46 p_desc_instance_id IN NUMBER,
47 p_uom_code IN VARCHAR2,
48 p_rule_code IN VARCHAR2)
49 RETURN NUMBER;
50
51 -----------------------------------------
52 -- Define Procedure for Utilization --
53 -----------------------------------------
54 -- Start of Comments --
55 -- Procedure name: update_utilization
56 -- Type: Private
57 -- Function: Updates the utilization based on the counter rules defined in the master configuration
58 -- given the details of an item/counter id/counter name/uom_code.
59 -- Casacades the updates down to all the children if the p_cascade_flag is set to 'Y'.
60 -- Pre-reqs:
61 -- Parameters:
62 -- Standard IN Parameters:
63 -- p_api_version IN NUMBER Required
64 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
65 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
66 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
67 --
68 -- Standard OUT Parameters:
69 -- x_return_status OUT VARCHAR2 Required
70 -- x_msg_count OUT NUMBER Required
71 -- x_msg_data OUT VARCHAR2 Required
72 --
73 -- Update_Utilization Parameters:
74 --
75 -- p_utilization_tbl IN Required.
76 -- For each record, at any given time only one of the following combinations is valid to identify the
77 -- item instance to be updated:
78 -- 1. Organization id and Inventory_item_id AND Serial Number.
79 -- This information will identify the part number and serial number of a configuration.
80 -- 2. Counter ID -- if this is passed a specific counter ONLY will be updated irrespective of the value
81 -- of p_cascade_flag.
82 -- 3. CSI_ITEM_INSTANCE_ID -- if this is passed, then this item instance and items down the hierarchy (depends on
83 -- the value p_cascade_flag) will be updated.
84 -- At any given time only one of the following parameters is valid to identify the type of item counters to be
85 -- updated:
86 -- 1. COUNTER_ID
87 -- 2. COUNTER_NAME
88 -- 3. UOM_CODE
89 --
90 -- reading_value IN Required
91 -- This will be the value of the counter reading.
92 -- cascade_flag IN Required
93 -- Can take values Y and N. Y indicates that the counter updates will cascade down the hierarchy
94 -- beginning at the item number passed. If its value is N then only the item counter will be updated.
95 --
96 -- End of Comments --
97
98 PROCEDURE update_utilization(p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
100 p_commit IN VARCHAR2 := FND_API.G_FALSE,
101 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
102 p_utilization_tbl IN AHL_UC_UTILIZATION_PVT.utilization_tbl_type,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2)
106 IS
107 l_api_name CONSTANT VARCHAR2(30) := 'update_utilization';
108 l_api_version CONSTANT NUMBER := 1.0;
109 l_utilization_rec AHL_UC_UTILIZATION_PVT.utilization_rec_type;
110 l_utilization_tbl AHL_UC_UTILIZATION_PVT.utilization_tbl_type DEFAULT p_utilization_tbl;
111 l_found VARCHAR2(50) := NULL;
112 l_based_on VARCHAR2(50) := NULL;
113
114 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Utilization';
115
116 BEGIN
117
118 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
119 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin',
120 'Entering Procedure. p_utilization_tbl.count = ' || p_utilization_tbl.count);
121 END IF;
122
123 -- Initialize API return status to success
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 -- Standard start of API savepoint
127 SAVEPOINT update_utilization;
128
129 -- Standard call to check for call compatibility
130 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version, l_api_name,
131 G_PKG_NAME) THEN
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 END IF;
134
135 -- Initialize message list if p_init_msg_list is set to TRUE
136 IF FND_API.to_boolean(p_init_msg_list) THEN
137 FND_MSG_PUB.initialize;
138 END IF;
139
140 FOR i IN l_utilization_tbl.FIRST..l_utilization_tbl.LAST LOOP
141 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
142 FND_LOG.STRING(G_LEVEL_STATEMENT,
143 L_DEBUG_KEY,
144 'Processing record ' || i);
145 END IF;
146 l_utilization_rec := l_utilization_tbl(i);
147 -- Convert value's to ID's.
148 convert_value_id(l_utilization_rec);
149 -- Validate input parameters.
150 validate_utilization_rec(l_utilization_rec, l_found, l_based_on);
151 l_utilization_tbl(i) := l_utilization_rec;
152 END LOOP;
153
154 -- Check Error Message stack.
155 x_msg_count := FND_MSG_PUB.count_msg;
156 IF x_msg_count > 0 THEN
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
161 FND_LOG.STRING(G_LEVEL_STATEMENT,
162 L_DEBUG_KEY,
163 'After successful completion of Value To Id conversion and Validation of all records in table.');
164 END IF;
165 -- Perform updates.
166 FOR i IN l_utilization_tbl.FIRST..l_utilization_tbl.LAST LOOP
167 l_utilization_rec := l_utilization_tbl(i);
168 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
169 FND_LOG.STRING(G_LEVEL_STATEMENT,
170 L_DEBUG_KEY,
171 'About to call update_reading_all for record ' || i ||
172 ', l_based_on = ' || l_based_on);
173 END IF;
174 update_reading_all(l_utilization_rec, l_based_on);
175 END LOOP;
176
177 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
178 FND_LOG.STRING(G_LEVEL_STATEMENT,
179 L_DEBUG_KEY,
180 'After successful completion of update_reading_all calls for all records in table.');
181 END IF;
182
183 -- Check Error Message stack.
184 x_msg_count := FND_MSG_PUB.count_msg;
185 IF x_msg_count > 0 THEN
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188
189 -- Standard check of p_commit
190 IF FND_API.TO_BOOLEAN(p_commit) THEN
191 COMMIT WORK;
192 END IF;
193
194 -- Standard call to get message count and if count is 1, get message info
195 FND_MSG_PUB.count_and_get(
196 p_count => x_msg_count,
197 p_data => x_msg_data,
198 p_encoded => fnd_api.g_false);
199
200 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
201 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
202 END IF;
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 ROLLBACK to update_utilization;
208 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
209 p_data => x_msg_data,
210 p_encoded => fnd_api.g_false);
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 ROLLBACK to update_utilization;
214 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
215 p_data => x_msg_data,
216 p_encoded => fnd_api.g_false);
217 WHEN OTHERS THEN
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219 ROLLBACK to update_utilization;
220 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
221 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
222 p_procedure_name => 'update_utilization',
223 p_error_text => SQLERRM);
224 END IF;
225 FND_MSG_PUB.count_and_get(p_count => x_msg_count,
226 p_data => x_msg_data,
227 p_encoded => fnd_api.g_false);
228 END update_utilization;
229
230 ---------------------------------------------------------------------------------
231
232 PROCEDURE convert_value_id(p_utilization_rec IN OUT NOCOPY
233 AHL_UC_UTILIZATION_PVT.utilization_rec_type)
234 IS
235 -- For organization id.
236 CURSOR mtl_parameters_csr(c_org_code VARCHAR2) IS
237 SELECT organization_id
238 FROM mtl_parameters
239 WHERE organization_code = c_org_code;
240
241 -- For inventory_item_id.
242 CURSOR mtl_system_items_csr(c_item_number VARCHAR2,
243 c_inv_organization_id NUMBER) IS
244 SELECT inventory_item_id
245 FROM ahl_mtl_items_ou_v
246 WHERE concatenated_segments = c_item_number
247 AND inventory_org_id = c_inv_organization_id;
248
249 -- For instance_id.
250 CURSOR csi_item_instance_csr(c_instance_number VARCHAR2) IS
251 SELECT instance_id
252 FROM csi_item_instances
253 WHERE instance_number = c_instance_number;
254
255 l_return_val BOOLEAN;
256 l_lookup_code fnd_lookups.lookup_code%TYPE;
257 l_organization_id NUMBER;
258 l_inventory_id NUMBER;
259 l_instance_id NUMBER;
260 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Convert_Value_Id';
261
262 BEGIN
263 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
265 END IF;
266
267 -- For Inventory Organization Code.
268 IF ((p_utilization_rec.organization_id IS NULL) OR
269 (p_utilization_rec.organization_id = FND_API.G_MISS_NUM)) THEN
270 -- if code is present.
271 IF ((p_utilization_rec.organization_code IS NOT NULL) AND
272 (p_utilization_rec.organization_code <> FND_API.G_MISS_CHAR)) THEN
273 OPEN mtl_parameters_csr(p_utilization_rec.organization_code);
274 FETCH mtl_parameters_csr INTO l_organization_id;
275 IF (mtl_parameters_csr%FOUND) THEN
276 p_utilization_rec.organization_id := l_organization_id;
277 ELSE
278 FND_MESSAGE.set_name('AHL','AHL_UC_ORG_INVALID');
279 FND_MESSAGE.set_token('ORG',p_utilization_rec.organization_code);
280 FND_MSG_PUB.add;
281 END IF;
282 CLOSE mtl_parameters_csr;
283 END IF;
284 END IF;
285 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
286 FND_LOG.STRING(G_LEVEL_STATEMENT,
287 L_DEBUG_KEY,
288 'p_utilization_rec.organization_id = ' || p_utilization_rec.organization_id);
289 END IF;
290
291 -- For Rule_Code_meaning.
292 IF ((p_utilization_rec.rule_code IS NULL) OR
293 (p_utilization_rec.rule_code = FND_API.G_MISS_CHAR)) THEN
294 -- Check if meaning exists.
295 IF ((p_utilization_rec.Rule_meaning IS NOT NULL) AND
296 (p_utilization_rec.Rule_meaning <> FND_API.G_MISS_CHAR)) THEN
297 AHL_UTIL_MC_PKG.convert_to_lookupcode('AHL_COUNTER_RULE_TYPE',
298 p_utilization_rec.Rule_meaning,
299 l_lookup_code,
300 l_return_val);
301 IF (l_return_val) THEN
302 p_utilization_rec.rule_code := l_lookup_code;
303 ELSE
304 FND_MESSAGE.set_name('AHL','AHL_UC_RCODE_INVALID');
305 FND_MESSAGE.set_token('CODE',p_utilization_rec.Rule_meaning);
306 FND_MSG_PUB.add;
307 END IF;
308 END IF;
309 END IF;
310 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
311 FND_LOG.STRING(G_LEVEL_STATEMENT,
312 L_DEBUG_KEY,
313 'p_utilization_rec.rule_code = ' || p_utilization_rec.rule_code);
314 END IF;
315
316 -- For Inventory item.
317 IF ((p_utilization_rec.inventory_item_id IS NULL) OR
318 (p_utilization_rec.inventory_item_id = FND_API.G_MISS_NUM)) THEN
319 -- check if name exists.
320 IF ((p_utilization_rec.item_number IS NOT NULL) AND
321 (p_utilization_rec.item_number <> FND_API.G_MISS_CHAR)) THEN
322 OPEN mtl_system_items_csr(p_utilization_rec.item_number,
323 p_utilization_rec.organization_id);
324 FETCH mtl_system_items_csr INTO l_inventory_id;
325 IF (mtl_system_items_csr%FOUND) THEN
326 p_utilization_rec.inventory_item_id := l_inventory_id;
327 ELSE
328 FND_MESSAGE.set_name('AHL','AHL_MC_INV_INVALID');
329 FND_MESSAGE.set_token('INV_ITEM',p_utilization_rec.item_number);
330 FND_MSG_PUB.add;
331 END IF;
332 CLOSE mtl_system_items_csr;
333 END IF;
334 END IF;
335 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
336 FND_LOG.STRING(G_LEVEL_STATEMENT,
337 L_DEBUG_KEY,
338 'p_utilization_rec.inventory_item_id = ' || p_utilization_rec.inventory_item_id);
339 END IF;
340
341 -- For Instance.
342 IF ((p_utilization_rec.csi_item_instance_id IS NULL) OR
343 (p_utilization_rec.csi_item_instance_id = FND_API.G_MISS_NUM)) THEN
344 -- check if name exists.
345 IF ((p_utilization_rec.csi_item_instance_number IS NOT NULL) AND
346 (p_utilization_rec.csi_item_instance_number <> FND_API.G_MISS_CHAR)) THEN
347 OPEN csi_item_instance_csr(p_utilization_rec.csi_item_instance_number);
348 FETCH csi_item_instance_csr INTO l_instance_id;
349 IF (csi_item_instance_csr%FOUND) THEN
350 p_utilization_rec.csi_item_instance_id := l_instance_id;
351 ELSE
352 FND_MESSAGE.set_name('AHL','AHL_UC_INSTANCE_INVALID');
353 FND_MESSAGE.set_token('INSTANCE',p_utilization_rec.csi_item_instance_number);
354 FND_MSG_PUB.add;
355 END IF;
356 CLOSE csi_item_instance_csr;
357 END IF;
358 END IF;
359 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
360 FND_LOG.STRING(G_LEVEL_STATEMENT,
361 L_DEBUG_KEY,
362 'p_utilization_rec.csi_item_instance_id = ' || p_utilization_rec.csi_item_instance_id);
363 END IF;
364
365 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
366 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure');
367 END IF;
368
369 END convert_value_id;
370
371 ---------------------------------------------------------------------------------
372
373 PROCEDURE validate_utilization_rec(p_utilization_rec IN OUT NOCOPY AHL_UC_UTILIZATION_PVT.utilization_rec_type,
374 x_found OUT NOCOPY VARCHAR2,
375 x_based_on OUT NOCOPY VARCHAR2)
376 IS
377 -- Get location type code.
378 CURSOR csi_item_instances_csr(c_instance_id NUMBER) IS
379 SELECT location_type_code
380 FROM csi_item_instances
381 WHERE instance_id = c_instance_id
382 AND TRUNC(sysdate) < TRUNC(NVL(active_end_date, sysdate+1));
383
384 -- Validate Counter ID.
385 CURSOR cs_counters_csr (c_counter_id NUMBER) IS
386 SELECT cgrp.source_object_id
387 FROM cs_counters ctr, cs_counter_groups cgrp
388 WHERE cgrp.counter_group_id = ctr.counter_group_id
389 AND cgrp.source_object_code = 'CP'
390 AND ctr.counter_id = c_counter_id
391 AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
392 AND trunc(sysdate) < trunc(nvl(ctr.end_date_active,sysdate+1));
393
394 CURSOR csi_item_serial_csr(c_inventory_item_id NUMBER,
395 c_organization_id NUMBER,
396 c_serial_number VARCHAR2) IS
397 SELECT instance_id,
398 instance_usage_code,
399 active_start_date,
400 active_end_date
401 FROM csi_item_instances csi
402 WHERE inventory_item_id = c_inventory_item_id
403 AND last_vld_organization_id = c_organization_id
404 AND serial_number = c_serial_number;
405
406 CURSOR mtl_units_of_measure_csr(c_uom_code VARCHAR2) IS
407 SELECT 'X'
408 FROM mtl_units_of_measure_vl
409 WHERE uom_code = c_uom_code;
410
411 -- Validate counter name.
412 CURSOR cs_counter_name_csr(c_counter_name VARCHAR2) IS
413 SELECT 'X'
414 FROM cs_counters ctr
415 WHERE ctr.name = c_counter_name
416 AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
417 AND trunc(sysdate) < trunc(nvl(ctr.end_date_active,sysdate+1));
418
419 CURSOR ahl_unit_config_csr (c_instance_id NUMBER) IS
420 SELECT unit_config_header_id
421 FROM ahl_unit_config_headers
422 WHERE csi_item_instance_id = c_instance_id
423 AND trunc(sysdate) >= trunc(nvl(active_start_date,sysdate))
424 AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
425
426 l_csi_item_instance_id NUMBER;
427 l_tmp_instance_id NUMBER := p_utilization_rec.csi_item_instance_id;
428 l_csi_top_node_id NUMBER;
429 l_parent_uc_header_id NUMBER;
430 l_parent_uc_instance_id NUMBER;
431 l_root_uc_header_id NUMBER;
432 l_root_uc_instance_id NUMBER;
433 l_root_uc_status_code FND_LOOKUP_VALUES_VL.lookup_code%TYPE;
434 l_root_active_uc_status_code FND_LOOKUP_VALUES_VL.lookup_code%TYPE;
435 l_root_uc_header_ovn NUMBER;
436 l_csi_instance_usage_code CSI_LOOKUPS.lookup_code%TYPE;
437 l_junk VARCHAR2(30);
438 l_return_val BOOLEAN;
439
440 l_config_status ahl_unit_config_headers.unit_config_status_code%TYPE;
441 l_master_config_status ahl_mc_headers_b.config_status_code%TYPE;
442 l_master_config_name ahl_mc_headers_b.name%TYPE;
443 l_master_config_id NUMBER;
444
445 l_unit_config_name ahl_unit_config_headers.name%TYPE;
446 l_location_type_code csi_item_instances.location_type_code%TYPE;
447 l_active_end_date DATE;
448 l_active_start_date DATE;
449
450 l_found VARCHAR2(50) := NULL;
451 l_based_on VARCHAR2(50) := NULL;
452
453 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Validate_Utilization_Rec';
454
455 BEGIN
456 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
457 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
458 END IF;
459
460 -- Check if counter_id is valid.
461 IF (p_utilization_rec.counter_id IS NOT NULL) AND
462 (p_utilization_rec.counter_id <> FND_API.G_MISS_NUM) THEN
463 OPEN cs_counters_csr(p_utilization_rec.counter_id);
464 FETCH cs_counters_csr INTO l_csi_item_instance_id;
465 IF (cs_counters_csr%NOTFOUND) THEN
466 FND_MESSAGE.set_name('AHL','AHL_UC_CSCTR_INVALID');
467 FND_MESSAGE.set_token('CTRID',p_utilization_rec.counter_id);
468 FND_MSG_PUB.add;
469 --dbms_output.put_line('Counter ID not found');
470 ELSE
471 l_based_on := l_based_on || ':' || 'COUNTERID';
472 l_found := l_found || ':' || 'COUNTER';
473
474 -- Find out the csi item status code.
475 OPEN csi_item_instances_csr(l_csi_item_instance_id);
476 FETCH csi_item_instances_csr INTO l_location_type_code;
477 IF (csi_item_instances_csr%NOTFOUND) THEN
478 FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
479 FND_MESSAGE.set_token('CSII',p_utilization_rec.csi_item_instance_id);
480 FND_MSG_PUB.add;
481 --dbms_output.put_line('CSI Item Instance not found');
482 ELSE
483 l_tmp_instance_id := l_csi_item_instance_id;
484 END IF; -- csi not found.
485 CLOSE csi_item_instances_csr;
486 END IF;
487 CLOSE cs_counters_csr;
488 END IF;
489 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING(G_LEVEL_STATEMENT,
491 L_DEBUG_KEY,
492 'Validated Counter Id.');
493 END IF;
494
495 -- Check if Inventory item and serial number are valid and exist in csi_item_instances.
496 IF (p_utilization_rec.inventory_item_id IS NOT NULL) AND
497 (p_utilization_rec.inventory_item_id <> FND_API.G_MISS_NUM) THEN
498 OPEN csi_item_serial_csr(p_utilization_rec.inventory_item_id,
499 p_utilization_rec.organization_id,
500 p_utilization_rec.serial_number);
501 FETCH csi_item_serial_csr INTO l_csi_item_instance_id, l_csi_instance_usage_code,
502 l_active_start_date, l_active_end_date;
503 IF (csi_item_serial_csr%NOTFOUND) THEN
504 FND_MESSAGE.set_name('AHL','AHL_UC_INV_SERIAL_INVALID');
505 FND_MESSAGE.set_token('INV_ITEM',p_utilization_rec.inventory_item_id);
506 FND_MESSAGE.set_token('SERIAL',p_utilization_rec.serial_number);
507 FND_MSG_PUB.add;
508 --dbms_output.put_line('Inventory item not found');
509 ELSIF (trunc(sysdate)) < trunc(nvl(l_active_end_date, sysdate+1)) THEN
510 l_found := l_found || ':' || 'INVENTORY';
511 l_tmp_instance_id := l_csi_item_instance_id;
512 ELSE
513 -- Item expired.
514 FND_MESSAGE.set_name('AHL','AHL_UC_INVITEM_INVALID');
515 FND_MESSAGE.set_token('INV_ITEM',p_utilization_rec.inventory_item_id);
516 FND_MESSAGE.set_token('SERIAL',p_utilization_rec.serial_number);
517 FND_MSG_PUB.add;
518 --dbms_output.put_line('Inventory item not found');
519 END IF;
520 CLOSE csi_item_serial_csr;
521 END IF;
522 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
523 FND_LOG.STRING(G_LEVEL_STATEMENT,
524 L_DEBUG_KEY,
525 'Validated inventory_item_id, serial and instance.');
526 END IF;
527
528 -- Check if csi_item_instance_id present.
529 IF (p_utilization_rec.csi_item_instance_id IS NOT NULL) AND
530 (p_utilization_rec.csi_item_instance_id <> FND_API.G_MISS_NUM) THEN
531 OPEN csi_item_instances_csr(p_utilization_rec.csi_item_instance_id);
532 FETCH csi_item_instances_csr INTO l_location_type_code;
533 IF (csi_item_instances_csr%NOTFOUND) THEN
534 FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
535 FND_MESSAGE.set_token('CSII',p_utilization_rec.csi_item_instance_id);
536 FND_MSG_PUB.add;
537 --dbms_output.put_line('CSI Item Instance not found');
538 ELSIF (l_location_type_code IN ('PO','INVENTORY','PROJECT','IN-TRANSIT')) THEN
539 FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
540 FND_MESSAGE.set_token('CSII',p_utilization_rec.csi_item_instance_id);
541 FND_MSG_PUB.add;
542 --dbms_output.put_line('CSI Item Instance location invalid');
543 ELSE
544 l_found := l_found || ':' || 'INSTANCE';
545 END IF; /* csi item_instance */
546 CLOSE csi_item_instances_csr;
547 END IF;
548 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
549 FND_LOG.STRING(G_LEVEL_STATEMENT,
550 L_DEBUG_KEY,
551 'Validated instance location.');
552 END IF;
553
554 p_utilization_rec.csi_item_instance_id := l_tmp_instance_id;
555 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
556 FND_LOG.STRING(G_LEVEL_STATEMENT,
557 L_DEBUG_KEY,
558 'Setting p_utilization_rec.csi_item_instance_id to ' || p_utilization_rec.csi_item_instance_id);
559 END IF;
560
561 -- Check Error Message stack.
562 IF FND_MSG_PUB.count_msg > 0 THEN
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565
566 -- Raise error if no or too many parameters.
567 IF (l_found IS NULL) THEN
568 FND_MESSAGE.set_name('AHL','AHL_UC_UPARAM_NULL');
569 FND_MSG_PUB.add;
570 --dbms_output.put_line('Part number information is null');
571 RAISE FND_API.G_EXC_ERROR;
572 ELSIF l_found <> ':INVENTORY' AND
573 l_found <> ':INSTANCE' AND
574 l_found <> ':COUNTER'
575 THEN
576 FND_MESSAGE.set_name('AHL','AHL_UC_UPARAM_INVALID');
577 FND_MSG_PUB.add;
578 --dbms_output.put_line('Too many parameters for part number.');
579 RAISE FND_API.G_EXC_ERROR;
580 END IF;
581
582 -- Check if unit config is complete.
583 -- First get its parent_uc_header_id
584 ahl_util_uc_pkg.get_parent_uc_header(p_utilization_rec.csi_item_instance_id,
585 l_parent_uc_header_id,
586 l_parent_uc_instance_id);
587 IF l_parent_uc_header_id IS NULL THEN
588 --Then check to see whether this instance happens to be a top unit instance
589 OPEN ahl_unit_config_csr(p_utilization_rec.csi_item_instance_id);
590 FETCH ahl_unit_config_csr INTO l_parent_uc_header_id;
591 IF ahl_unit_config_csr%NOTFOUND THEN
592 --Means this instance is definately not in a UC
593 FND_MESSAGE.set_name('AHL','AHL_UC_INSTANCE_NOT_IN_UC');
594 FND_MESSAGE.set_token('INSTANCE', p_utilization_rec.csi_item_instance_id);
595 FND_MSG_PUB.add;
596 END IF;
597 CLOSE ahl_unit_config_csr;
598 --dbms_output.put_line('CSI Item Instance not found');
599 ELSE
600 ahl_util_uc_pkg.get_root_uc_attr(
601 p_uc_header_id => l_parent_uc_header_id,
602 x_uc_header_id => l_root_uc_header_id,
603 x_instance_id => l_root_uc_instance_id,
604 x_uc_status_code => l_root_uc_status_code,
605 x_active_uc_status_code => l_root_active_uc_status_code,
606 x_uc_header_ovn => l_root_uc_header_ovn);
607
608 IF (l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT')) THEN
609 -- 'DRAFT' needs to be removed after testing
610 FND_MESSAGE.set_name('AHL','AHL_UC_STATUS_INVALID');
611 FND_MESSAGE.set_token('STATUS',l_root_uc_status_code);
612 FND_MSG_PUB.add;
613 --dbms_output.put_line('UC Status invalid');
614 END IF;
615 END IF;
616 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
617 FND_LOG.STRING(G_LEVEL_STATEMENT,
618 L_DEBUG_KEY,
619 'Validated UC of instance');
620 END IF;
621
622 -- Check values of cascade_flag.
623 IF (p_utilization_rec.cascade_flag IS NOT NULL AND
624 p_utilization_rec.cascade_flag NOT IN ('Y','N'))
625 THEN
626 FND_MESSAGE.set_name('AHL','AHL_UC_CASCADE_INVALID');
627 FND_MESSAGE.set_token('FLAG',p_utilization_rec.cascade_flag);
628 FND_MSG_PUB.add;
629 --dbms_output.put_line('Cascade flag is invalid.');
630 END IF;
631 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
632 FND_LOG.STRING(G_LEVEL_STATEMENT,
633 L_DEBUG_KEY,
634 'Validated p_utilization_rec.cascade_flag: ' || p_utilization_rec.cascade_flag);
635 END IF;
636
637 -- Check values of delta_flag.
638 IF (p_utilization_rec.delta_flag IS NOT NULL AND
639 p_utilization_rec.delta_flag NOT IN ('Y','N'))
640 THEN
641 FND_MESSAGE.set_name('AHL','AHL_UC_DELTA_FLAG_INVALID');
642 FND_MESSAGE.set_token('FLAG',p_utilization_rec.delta_flag);
643 FND_MSG_PUB.add;
644 --dbms_output.put_line('Delta flag is invalid.');
645 END IF;
646 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
647 FND_LOG.STRING(G_LEVEL_STATEMENT,
648 L_DEBUG_KEY,
649 'Validated p_utilization_rec.delta_flag: ' || p_utilization_rec.delta_flag);
650 END IF;
651
652 -- Check Error Message stack.
653 IF FND_MSG_PUB.count_msg > 0 THEN
654 RAISE FND_API.G_EXC_ERROR;
655 END IF;
656
657 -- Check UOM_CODE.
658 IF (p_utilization_rec.uom_code IS NOT NULL) AND
659 (p_utilization_rec.uom_code <> FND_API.G_MISS_CHAR) THEN
660 OPEN mtl_units_of_measure_csr(p_utilization_rec.uom_code);
661 FETCH mtl_units_of_measure_csr INTO l_junk;
662 IF (mtl_units_of_measure_csr%NOTFOUND) THEN
663 FND_MESSAGE.set_name('AHL','AHL_UC_UZ_UOM_INVALID');
664 FND_MESSAGE.set_token('UOM_CODE',p_utilization_rec.uom_code);
665 FND_MSG_PUB.add;
666 --dbms_output.put_line('UOM CODE not found');
667 ELSE
668 l_based_on := l_based_on || ':' || 'UOM';
669 END IF;
670 CLOSE mtl_units_of_measure_csr;
671 END IF;
672 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
673 FND_LOG.STRING(G_LEVEL_STATEMENT,
674 L_DEBUG_KEY,
675 'Validated p_utilization_rec.uom_code: ' || p_utilization_rec.uom_code);
676 END IF;
677
678 -- Check Counter Name.
679 IF (p_utilization_rec.counter_name IS NOT NULL) AND
680 (p_utilization_rec.counter_name <> FND_API.G_MISS_CHAR)
681 THEN
682 OPEN cs_counter_name_csr(p_utilization_rec.counter_name);
683 FETCH cs_counter_name_csr INTO l_junk;
684 IF (cs_counter_name_csr%NOTFOUND) THEN
685 FND_MESSAGE.set_name('AHL','AHL_UC_CTRNAME_INVALID');
686 FND_MESSAGE.set_token('CTR_NAME',p_utilization_rec.counter_name);
687 FND_MSG_PUB.add;
688 --dbms_output.put_line('Counter Name not found');
689 ELSE
690 l_based_on := l_based_on || ':' || 'COUNTER';
691 END IF;
692 CLOSE cs_counter_name_csr;
693 END IF;
694 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
695 FND_LOG.STRING(G_LEVEL_STATEMENT,
696 L_DEBUG_KEY,
697 'Validated p_utilization_rec.counter_name: ' || p_utilization_rec.counter_name);
698 END IF;
699
700 -- Validate Rule Code.
701 IF (p_utilization_rec.rule_code IS NOT NULL AND
702 NOT(AHL_UTIL_MC_PKG.validate_lookup_code('AHL_COUNTER_RULE_TYPE', p_utilization_rec.rule_code))) THEN
703 FND_MESSAGE.set_name('AHL','AHL_UC_RCODE_INVALID');
704 FND_MESSAGE.set_token('CODE',p_utilization_rec.rule_code);
705 FND_MSG_PUB.add;
706 --dbms_output.put_line('Invalid Rule code');
707 END IF;
708 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
709 FND_LOG.STRING(G_LEVEL_STATEMENT,
710 L_DEBUG_KEY,
711 'Validated p_utilization_rec.rule_code: ' || p_utilization_rec.rule_code);
712 END IF;
713
714 -- Default reading date
715 IF (p_utilization_rec.reading_date IS NULL OR
716 p_utilization_rec.reading_date = FND_API.G_MISS_DATE) THEN
717 p_utilization_rec.reading_date := SYSDATE;
718 END IF;
719 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
720 FND_LOG.STRING(G_LEVEL_STATEMENT,
721 L_DEBUG_KEY,
722 'Validated p_utilization_rec.reading_date: ' || p_utilization_rec.reading_date);
723 END IF;
724
725 --Validate Reading.
726 --If reading_value is delta reading, then this value can be positive (ascending counter)
727 --or negative (descending counter). And If reading value is net reading, then we require
728 --the specific counter name provided or counter names with the speicific UOMs provided exist
729 --for the start instance itself.
730 IF (p_utilization_rec.reading_value IS NULL OR p_utilization_rec.reading_value = FND_API.G_MISS_NUM) THEN
731 FND_MESSAGE.set_name('AHL','AHL_UC_READING_INVALID');
732 FND_MSG_PUB.add;
733 END IF;
734 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
735 FND_LOG.STRING(G_LEVEL_STATEMENT,
736 L_DEBUG_KEY,
737 'Validated p_utilization_rec.reading_value: ' || p_utilization_rec.reading_value);
738 END IF;
739
740 -- Check Error Message stack.
741 IF FND_MSG_PUB.count_msg > 0 THEN
742 RAISE FND_API.G_EXC_ERROR;
743 END IF;
744
745 -- Raise error if no or too many parameters.
746 IF (l_based_on IS NULL) THEN
747 FND_MESSAGE.set_name('AHL','AHL_UC_UBASED_ON_NULL');
748 FND_MSG_PUB.add;
749 --dbms_output.put_line('Part number information is null');
750 RAISE FND_API.G_EXC_ERROR;
751 ELSIF (l_based_on <> ':UOM' AND
752 l_based_on <> ':COUNTER' AND
753 l_based_on <> ':COUNTERID' ) THEN
754 FND_MESSAGE.set_name('AHL','AHL_UC_UBASED_ON_INVALID');
755 FND_MSG_PUB.add;
756 --dbms_output.put_line('Input parameters contain both UOM Code and Counter Name');
757 RAISE FND_API.G_EXC_ERROR;
758 END IF;
759 x_found := l_found;
760 x_based_on := l_based_on;
761 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
762 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
763 'Exiting Procedure. x_found = ' || x_found || ', x_based_on = ' || x_based_on);
764 END IF;
765
766 END validate_utilization_rec;
767
768 ---------------------------------------------------------------------------------
769
770 PROCEDURE update_reading_id(p_utilization_rec IN utilization_rec_type) IS
771 -- Get current counter reading values based on the counter_id
772 CURSOR get_current_value_id(c_counter_id NUMBER) IS
773 -- Changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
774 -- To take advantage of the fix made in Counters bug 7561677 (FP of 7374316)
775 -- and also to ignore disabled counter readings
776 -- SATHAPLI::Bug 8765956, 07-Aug-2009, re-introducing NVL usage.
777 -- NOTE: NVL() should be put for the entire inner SELECT, as it should take effect even if this inner SELECT doesn't
778 -- fetch any rows.
779 SELECT NVL((select ccr.net_reading
780 from csi_counter_readings ccr
781 where ccr.counter_value_id = c.ctr_val_max_seq_no), 0) counter_reading,
782 DEFAULTED_GROUP_ID counter_group_id
783 FROM CSI_COUNTERS_B C
784 WHERE counter_id = c_counter_id
785 -- Changes by jaramana on 28-DEC-2009 for bug 9229943
786 -- Lock the CSI_COUNTERS_B row to prevent incorrect updates
787 FOR UPDATE OF c.ctr_val_max_seq_no;
788
789 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
790 CURSOR is_counter_change_type_csr(c_counter_id NUMBER) IS
791 select 'Y' from CSI_COUNTERS_B
792 where counter_id = c_counter_id
793 and reading_type = 2;
794
795 l_change_type_flag VARCHAR2(1) := 'N';
796 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
797
798 l_counter_grp_id NUMBER;
799 l_reading_value NUMBER;
800 l_start_current_value NUMBER;
801
802 l_ctr_grp_log_rec CS_CTR_CAPTURE_READING_PUB.ctr_grp_log_rec_type;
803 l_ctr_rdg_tbl CS_CTR_CAPTURE_READING_PUB.ctr_rdg_tbl_type;
804 l_prop_rdg_tbl CS_CTR_CAPTURE_READING_PUB.prop_rdg_tbl_type;
805 l_return_status VARCHAR2(3);
806 l_msg_count NUMBER;
807 l_msg_data VARCHAR2(2000);
808 l_msg_index_out NUMBER;
809 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Id';
810
811 BEGIN
812 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
813 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
814 END IF;
815
816 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
817 OPEN is_counter_change_type_csr(p_utilization_rec.counter_id);
818 FETCH is_counter_change_type_csr INTO l_change_type_flag;
819 IF (is_counter_change_type_csr%FOUND) THEN
820 l_change_type_flag := 'Y';
821 ELSE
822 l_change_type_flag := 'N';
823 END IF;
824 CLOSE is_counter_change_type_csr;
825 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
826 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Counter is change type: ' || l_change_type_flag);
827 END IF;
828 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
829
830 OPEN get_current_value_id(p_utilization_rec.counter_id);
831 FETCH get_current_value_id INTO l_start_current_value, l_counter_grp_id;
832 IF get_current_value_id%NOTFOUND THEN
833 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
834 FND_MSG_PUB.add;
835 CLOSE get_current_value_id;
836 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
837 ELSE
838 CLOSE get_current_value_id;
839 END IF;
840 IF p_utilization_rec.delta_flag = 'Y' THEN
841 -- Begin Changes by jaramana on Feb 20, 2008 for Bug 6782765
842 IF (l_change_type_flag = 'Y') THEN
843 -- For Change type counters, always pass the delta value
844 l_reading_value := p_utilization_rec.reading_value;
845 ELSE
846 -- For other counters, always pass the total value
847 l_reading_value := l_start_current_value+p_utilization_rec.reading_value;
848 END IF;
849 ELSE
850 -- Not Delta
851 IF (l_change_type_flag = 'Y') THEN
852 -- For Change type counters, delta_flag should always be Y
853 FND_MESSAGE.set_name('AHL', 'AHL_UC_DELTA_FLAG_INVALID');
854 FND_MESSAGE.set_token('FLAG', p_utilization_rec.delta_flag);
855 FND_MSG_PUB.add;
856 RAISE FND_API.G_EXC_ERROR;
857 END IF;
858 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
859 l_reading_value := p_utilization_rec.reading_value;
860 END IF;
861 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
862 FND_LOG.STRING(G_LEVEL_STATEMENT,
863 L_DEBUG_KEY,
864 'l_start_current_value = ' || l_start_current_value ||
865 ', p_utilization_rec.delta_flag = ' || p_utilization_rec.delta_flag ||
866 ', p_utilization_rec.reading_value = ' || p_utilization_rec.reading_value ||
867 ', Setting l_reading_value to ' || l_reading_value);
868 END IF;
869
870 -- Update reading for the counter.
871 l_ctr_grp_log_rec.counter_group_id := l_counter_grp_id;
872 l_ctr_grp_log_rec.value_timestamp := SYSDATE;
873 l_ctr_grp_log_rec.source_transaction_id := p_utilization_rec.csi_item_instance_id;
874 l_ctr_grp_log_rec.source_transaction_code := 'CP';
875
876 l_ctr_rdg_tbl(1).counter_id := p_utilization_rec.counter_id;
877 l_ctr_rdg_tbl(1).value_timestamp := p_utilization_rec.reading_date;
878 l_ctr_rdg_tbl(1).counter_reading := l_reading_value;
879 l_ctr_rdg_tbl(1).valid_flag := 'Y';
880 -- Changed from 'Y' to 'N' by jaramana on July 10, 2007 for bug 6127957
881 l_ctr_rdg_tbl(1).override_valid_flag := 'N';
882 --Call CS Counter Update API to update the counter_reading of the start instance
883 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(G_LEVEL_STATEMENT,
885 L_DEBUG_KEY,
886 'About to call CS_CTR_CAPTURE_READING_PUB.capture_counter_reading');
887 END IF;
888 CS_CTR_CAPTURE_READING_PUB.capture_counter_reading(
889 p_api_version_number => 1.0,
890 p_init_msg_list => FND_API.G_FALSE,
891 p_commit => FND_API.G_FALSE,
892 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
893 p_ctr_grp_log_rec => l_ctr_grp_log_rec,
894 p_ctr_rdg_tbl => l_ctr_rdg_tbl,
895 p_prop_rdg_tbl => l_prop_rdg_tbl,
896 x_return_status => l_return_status,
897 x_msg_count => l_msg_count,
898 x_msg_data => l_msg_data );
899 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
900 FND_LOG.STRING(G_LEVEL_STATEMENT,
901 L_DEBUG_KEY,
902 'Returned from call to CS_CTR_CAPTURE_READING_PUB.capture_counter_reading. l_return_status = ' || l_return_status);
903 END IF;
904 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
907 RAISE FND_API.G_EXC_ERROR;
908 END IF;
909 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
910 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
911 'Exiting Procedure.');
912 END IF;
913 END update_reading_id;
914
915 ---------------------------------------------------------------------------------
916
917 --Update the counter reading value of the start instance and all of its components (if
918 --cascade_flag = 'Y'). This procedure assumes the given counter name exists for the start
919 --instance
920 PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type) IS
921 CURSOR csi_relationships_csr(c_csi_item_instance_id NUMBER) IS
922 SELECT subject_id csi_item_instance_id, position_reference
923 FROM csi_ii_relationships
924 START WITH object_id = c_csi_item_instance_id
925 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
926 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
927 AND relationship_type_code = 'COMPONENT-OF'
928 CONNECT BY object_id = PRIOR subject_id
929 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
930 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
931 AND relationship_type_code = 'COMPONENT-OF';
932
933 -- Get current counter reading values based on the counter_id
934 -- Cursor get_current_value_id commented out by jaramana on on June 13, 2007
935 -- while fixing bug 6123549 since this cursor is not used in this procedure at all.
936 /***
937 CURSOR get_current_value_id(c_counter_id NUMBER) IS
938 SELECT nvl(counter_reading,0) counter_reading,
939 counter_group_id,
940 uom_code
941 FROM csi_cp_counters_v
942 WHERE counter_id = c_counter_id;
943 ***/
944 -- Get current counter reading values based on the counter_name
945 -- Cursor changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
946 -- Removed the use of csi_cp_counters_v and uptook
947 -- changes made via Counters bug 7561677 (FP of 7374316)
948 CURSOR get_current_value_name(c_instance_id NUMBER,
949 c_counter_name VARCHAR2) IS
950 -- SATHAPLI::Bug 8765956, 07-Aug-2009, re-introducing NVL usage.
951 -- NOTE: NVL() should be put for the entire inner SELECT, as it should take effect even if this inner SELECT doesn't
952 -- fetch any rows.
953 SELECT C.DEFAULTED_GROUP_ID counter_group_id,
954 NVL((select ccr.net_reading
955 from csi_counter_readings ccr
956 where ccr.counter_value_id = c.ctr_val_max_seq_no), 0) counter_reading,
957 C.COUNTER_ID counter_id,
958 C.START_DATE_ACTIVE start_date_active,
959 C.END_DATE_ACTIVE end_date_active,
960 C.UOM_CODE uom_code
961 FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
962 WHERE C.COUNTER_ID = CCA.COUNTER_ID(+)
963 AND CCA.SOURCE_OBJECT_CODE = 'CP'
964 AND CCA.SOURCE_OBJECT_ID = c_instance_id
965 AND C.COUNTER_TEMPLATE_NAME = c_counter_name
966 AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
967 AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate)
968 -- Changes by jaramana on 28-DEC-2009 for bug 9229943
969 -- Lock the CSI_COUNTERS_VL row to prevent incorrect updates
970 FOR UPDATE OF c.ctr_val_max_seq_no;
971
972 l_get_current_value_name get_current_value_name%ROWTYPE;
973
974 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
975 CURSOR is_counter_change_type_csr(c_counter_id NUMBER) IS
976 select 'Y' from CSI_COUNTERS_B
977 where counter_id = c_counter_id
978 and reading_type = 2;
979
980 l_change_type_flag VARCHAR2(1) := 'N';
981 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
982
983 l_utilization_rec utilization_rec_type;
984 l_ratio NUMBER;
985 l_reading_value NUMBER;
986 l_start_current_value NUMBER;
987 l_uom_code cs_counters.uom_code%TYPE;
988
989 l_ctr_grp_log_rec CS_CTR_CAPTURE_READING_PUB.ctr_grp_log_rec_type;
990 l_ctr_rdg_tbl CS_CTR_CAPTURE_READING_PUB.ctr_rdg_tbl_type;
991 l_prop_rdg_tbl CS_CTR_CAPTURE_READING_PUB.prop_rdg_tbl_type;
992 l_return_status VARCHAR2(3);
993 l_msg_count NUMBER;
994 l_msg_data VARCHAR2(2000);
995 l_msg_index_out NUMBER;
996 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Ins';
997
998 BEGIN
999 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1000 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1001 END IF;
1002 l_utilization_rec := p_utilization_rec;
1003 --First get the current counter reading value for the start instance
1004 --This procedure assumes that counter exists for the instance
1005 OPEN get_current_value_name(l_utilization_rec.csi_item_instance_id,
1006 l_utilization_rec.counter_name);
1007 FETCH get_current_value_name INTO l_get_current_value_name;
1008 IF get_current_value_name%NOTFOUND THEN
1009 FND_MESSAGE.set_name('AHL','AHL_UC_CTR_INST_INVALID');
1010 FND_MESSAGE.set_token('COUNTER', l_utilization_rec.counter_name);
1011 FND_MESSAGE.set_token('INSTANCE', l_utilization_rec.csi_item_instance_id);
1012 FND_MSG_PUB.add;
1013 CLOSE get_current_value_name;
1014 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1015 ELSE
1016 CLOSE get_current_value_name;
1017 END IF;
1018 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING(G_LEVEL_STATEMENT,
1020 L_DEBUG_KEY,
1021 'l_get_current_value_name.counter_id = ' || l_get_current_value_name.counter_id ||
1022 ', l_get_current_value_name.counter_reading = ' || l_get_current_value_name.counter_reading ||
1023 ', l_get_current_value_name.uom_code = ' || l_get_current_value_name.uom_code ||
1024 ', l_get_current_value_name.start_date_active = ' || l_get_current_value_name.start_date_active ||
1025 ', l_get_current_value_name.end_date_active = ' || l_get_current_value_name.end_date_active);
1026 END IF;
1027
1028 l_utilization_rec.counter_id := l_get_current_value_name.counter_id;
1029
1030 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
1031 OPEN is_counter_change_type_csr(l_utilization_rec.counter_id);
1032 FETCH is_counter_change_type_csr INTO l_change_type_flag;
1033 IF (is_counter_change_type_csr%FOUND) THEN
1034 l_change_type_flag := 'Y';
1035 ELSE
1036 l_change_type_flag := 'N';
1037 END IF;
1038 CLOSE is_counter_change_type_csr;
1039 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1040 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Counter is change type: ' || l_change_type_flag);
1041 END IF;
1042 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
1043
1044 l_start_current_value := l_get_current_value_name.counter_reading;
1045 l_uom_code := l_get_current_value_name.uom_code;
1046 update_reading_id(l_utilization_rec);
1047 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.STRING(G_LEVEL_STATEMENT,
1049 L_DEBUG_KEY, 'Returned from call to update_reading_id');
1050 END IF;
1051
1052 IF (l_utilization_rec.cascade_flag = 'Y') THEN
1053 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1054 FND_LOG.STRING(G_LEVEL_STATEMENT,
1055 L_DEBUG_KEY, 'l_utilization_rec.cascade_flag = Y');
1056 END IF;
1057 -- Process for config items.
1058 FOR child_rec IN csi_relationships_csr(p_utilization_rec.csi_item_instance_id) LOOP
1059 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1060 FND_LOG.STRING(G_LEVEL_STATEMENT,
1061 L_DEBUG_KEY, 'Processing child instance ' || child_rec.csi_item_instance_id);
1062 END IF;
1063 -- Get counter ratio.
1064 IF ahl_util_uc_pkg.extra_node(child_rec.csi_item_instance_id, l_utilization_rec.csi_item_instance_id) THEN
1065 l_ratio := 1;
1066 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1067 FND_LOG.STRING(G_LEVEL_STATEMENT,
1068 L_DEBUG_KEY, 'Instance is an extra node. Setting l_ratio to 1');
1069 END IF;
1070 ELSE
1071 l_ratio := get_counter_ratio(l_utilization_rec.csi_item_instance_id,
1072 child_rec.csi_item_instance_id,
1073 l_uom_code,
1074 l_utilization_rec.rule_code);
1075 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1076 FND_LOG.STRING(G_LEVEL_STATEMENT,
1077 L_DEBUG_KEY, 'Instance is not an extra node. Setting l_ratio to ' || l_ratio);
1078 END IF;
1079 END IF;
1080
1081 -- Get current counter reading values
1082 OPEN get_current_value_name(child_rec.csi_item_instance_id, l_utilization_rec.counter_name);
1083 FETCH get_current_value_name INTO l_get_current_value_name;
1084 IF get_current_value_name%FOUND THEN
1085 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1086 FND_LOG.STRING(G_LEVEL_STATEMENT,
1087 L_DEBUG_KEY, 'get_current_value_name%FOUND is TRUE. ' ||
1088 'l_get_current_value_name.counter_reading = ' || l_get_current_value_name.counter_reading);
1089 END IF;
1090 --Else the given counter name doesn't apply to this instance
1091 --Ensure that if the same counter existing for the component instance, then the counter's
1092 --UOM should be exactly the same as that of start instance's counter. Cheng has confirmed
1093 --this point with Barry.
1094 IF l_get_current_value_name.uom_code <> l_uom_code THEN
1095 FND_MESSAGE.set_name('AHL','AHL_UC_CTR_UOM_INVALID');
1096 FND_MESSAGE.set_token('COUNTER', l_utilization_rec.counter_name);
1097 FND_MESSAGE.set_token('INSTANCE', child_rec.csi_item_instance_id);
1098 FND_MESSAGE.set_token('UOM', l_get_current_value_name.uom_code);
1099 FND_MSG_PUB.add;
1100 CLOSE get_current_value_name;
1101 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1102 ELSE
1103 CLOSE get_current_value_name;
1104 END IF;
1105
1106 IF l_utilization_rec.delta_flag = 'Y' THEN
1107 -- Begin Changes by jaramana on Feb 20, 2008 for Bug 6782765
1108 IF (l_change_type_flag = 'Y') THEN
1109 -- For Change type counters, always pass the multiplied delta value
1110 l_reading_value := l_ratio*l_utilization_rec.reading_value;
1111 ELSE
1112 -- For other counters, always pass the total value
1113 l_reading_value := l_get_current_value_name.counter_reading+l_ratio*l_utilization_rec.reading_value;
1114 END IF;
1115 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1116 FND_LOG.STRING(G_LEVEL_STATEMENT,
1117 L_DEBUG_KEY, 'l_utilization_rec.delta_flag is Y. Setting l_reading_value to ' || l_reading_value);
1118 END IF;
1119 ELSE
1120 -- Not Delta
1121 IF (l_change_type_flag = 'Y') THEN
1122 -- For Change type counters, delta_flag should always be Y
1123 FND_MESSAGE.set_name('AHL', 'AHL_UC_DELTA_FLAG_INVALID');
1124 FND_MESSAGE.set_token('FLAG', l_utilization_rec.delta_flag);
1125 FND_MSG_PUB.add;
1126 RAISE FND_API.G_EXC_ERROR;
1127 END IF;
1128 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
1129 l_reading_value := l_get_current_value_name.counter_reading+
1130 l_ratio*(l_utilization_rec.reading_value-l_start_current_value);
1131 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1132 FND_LOG.STRING(G_LEVEL_STATEMENT,
1133 L_DEBUG_KEY, 'l_utilization_rec.delta_flag is not Y. Setting l_reading_value to ' || l_reading_value);
1134 END IF;
1135 END IF;
1136 -- Update reading for the counter.
1137 l_ctr_grp_log_rec.counter_group_id := l_get_current_value_name.counter_group_id;
1138 l_ctr_grp_log_rec.value_timestamp := SYSDATE;
1139 l_ctr_grp_log_rec.source_transaction_id := child_rec.csi_item_instance_id;
1140 l_ctr_grp_log_rec.source_transaction_code := 'CP';
1141 l_ctr_rdg_tbl(1).counter_id := l_get_current_value_name.counter_id;
1142 l_ctr_rdg_tbl(1).value_timestamp := l_utilization_rec.reading_date;
1143 l_ctr_rdg_tbl(1).counter_reading := l_reading_value;
1144 l_ctr_rdg_tbl(1).valid_flag := 'Y';
1145 -- Changed from 'Y' to 'N' by jaramana on July 10, 2007 for bug 6127957
1146 l_ctr_rdg_tbl(1).override_valid_flag := 'N';
1147 --Call CS Counter Update API to update the counter_reading of the start instance
1148 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1149 FND_LOG.STRING(G_LEVEL_STATEMENT,
1150 L_DEBUG_KEY, 'About to call CS_CTR_CAPTURE_READING_PUB.capture_counter_reading with ' ||
1151 'l_ctr_rdg_tbl(1).counter_reading = ' || l_ctr_rdg_tbl(1).counter_reading ||
1152 ', l_ctr_rdg_tbl(1).counter_id = ' || l_ctr_rdg_tbl(1).counter_id ||
1153 ', l_ctr_rdg_tbl(1).value_timestamp = ' || l_ctr_rdg_tbl(1).value_timestamp);
1154 END IF;
1155 CS_CTR_CAPTURE_READING_PUB.capture_counter_reading(
1156 p_api_version_number => 1.0,
1157 p_init_msg_list => FND_API.G_FALSE,
1158 p_commit => FND_API.G_FALSE,
1159 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1160 p_ctr_grp_log_rec => l_ctr_grp_log_rec,
1161 p_ctr_rdg_tbl => l_ctr_rdg_tbl,
1162 p_prop_rdg_tbl => l_prop_rdg_tbl,
1163 x_return_status => l_return_status,
1164 x_msg_count => l_msg_count,
1165 x_msg_data => l_msg_data);
1166 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1167 FND_LOG.STRING(G_LEVEL_STATEMENT,
1168 L_DEBUG_KEY, 'Returned from call to CS_CTR_CAPTURE_READING_PUB.capture_counter_reading. l_return_status = ' || l_return_status);
1169 END IF;
1170 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1172 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1173 RAISE FND_API.G_EXC_ERROR;
1174 END IF;
1175 ELSE
1176 CLOSE get_current_value_name;
1177 -- Added by jaramana on October 29, 2007 for bug 6513576
1178 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1179 FND_LOG.STRING(G_LEVEL_STATEMENT,
1180 L_DEBUG_KEY, 'get_current_value_name%FOUND is FALSE.');
1181 END IF;
1182 END IF;
1183 END LOOP;
1184 END IF;
1185 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1186 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure.');
1187 END IF;
1188 END update_reading_ins;
1189
1190 ---------------------------------------------------------------------------------
1191
1192 PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type) IS
1193 TYPE instance_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1194 /** Begin changes by jaramana on October 29, 2007 for bug 6513576
1195 Cannot use cs_counters and cs_counter_groups anymore **/
1196 -- Cursor changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1197 -- Removed the use of csi_cp_counters_v
1198 CURSOR get_components_counter(c_instance_id NUMBER, c_counter_name VARCHAR2) IS
1199 SELECT CI.subject_id
1200 FROM csi_ii_relationships CI
1201 WHERE EXISTS (SELECT 'X'
1202 /*
1203 FROM cs_counters CC,
1204 cs_counter_groups CG
1205 WHERE CC.counter_group_id = CG.counter_group_id
1206 AND CG.source_object_id = CI.subject_id
1207 AND CG.source_object_code = 'CP'
1208 AND CC.name = c_counter_name
1209 AND trunc(nvl(CC.start_date_active,sysdate)) <= trunc(sysdate)
1210 AND trunc(nvl(CC.end_date_active,sysdate+1)) > trunc(sysdate))
1211 */
1212 /*
1213 FROM CSI_CP_COUNTERS_V CCCV
1214 WHERE CCCV.CUSTOMER_PRODUCT_ID = CI.subject_id
1215 AND CCCV.COUNTER_TEMPLATE_NAME = c_counter_name
1216 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1217 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate))
1218 */
1219 FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
1220 WHERE CCA.SOURCE_OBJECT_ID = CI.subject_id
1221 AND C.COUNTER_ID = CCA.COUNTER_ID(+)
1222 AND CCA.SOURCE_OBJECT_CODE = 'CP'
1223 AND C.COUNTER_TEMPLATE_NAME = c_counter_name
1224 AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
1225 AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate))
1226 START WITH object_id = c_instance_id
1227 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1228 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1229 AND relationship_type_code = 'COMPONENT-OF'
1230 CONNECT BY object_id = PRIOR subject_id
1231 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1232 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1233 AND relationship_type_code = 'COMPONENT-OF';
1234
1235 CURSOR get_all_ancestors(c_desc_instance_id NUMBER, c_ance_instance_id NUMBER) IS
1236 SELECT object_id
1237 FROM csi_ii_relationships
1238 START WITH subject_id = c_desc_instance_id
1239 AND object_id <> c_ance_instance_id
1240 -- This condition is really required because of the extreme case in which
1241 -- subject_id = c_desc_instance_id and object_id happens to be c_ance_instance_id
1242 -- thus it will include c_ance_instance_id and probably all of its ancestors if it has.
1243 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1244 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1245 AND relationship_type_code = 'COMPONENT-OF'
1246 CONNECT BY subject_id = PRIOR object_id
1247 AND object_id <> c_ance_instance_id
1248 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1249 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1250 AND relationship_type_code = 'COMPONENT-OF';
1251
1252 -- Get counters that match on a given counter name.
1253 CURSOR cs_counters_name_csr(c_csi_item_instance_id NUMBER, c_name VARCHAR2) IS
1254 /*
1255 SELECT ctr.name counter_name, ctr.counter_id, ctr.uom_code, cgrp.counter_group_id
1256 FROM cs_counter_groups cgrp, cs_counters ctr
1257 WHERE cgrp.counter_group_id = ctr.counter_group_id
1258 AND cgrp.source_object_code = 'CP'
1259 AND cgrp.source_object_id = c_csi_item_instance_id
1260 AND ctr.name = c_name
1261 AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
1262 AND trunc(sysdate) <= trunc(nvl(ctr.end_date_active,sysdate+1));
1263 */
1264 SELECT CCCV.COUNTER_TEMPLATE_NAME counter_name, CCCV.counter_id, CCCV.uom_code, CCCV.counter_group_id
1265 FROM CSI_CP_COUNTERS_V CCCV
1266 WHERE CCCV.CUSTOMER_PRODUCT_ID = c_csi_item_instance_id
1267 AND CCCV.COUNTER_TEMPLATE_NAME = c_name
1268 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1269 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);
1270 /** End changes by jaramana on October 29, 2007 for bug 6513576 **/
1271 l_cs_counters_name_csr cs_counters_name_csr%ROWTYPE;
1272
1273 -- Get current counter reading values based on the counter_id
1274 -- Cursor get_current_value_id commented out by jaramana on on June 13, 2007
1275 -- while fixing bug 6123549 since this cursor is not used in this procedure at all.
1276 /***
1277 CURSOR get_current_value_id(c_counter_id NUMBER) IS
1278 SELECT nvl(counter_reading,0) counter_reading,
1279 counter_group_id,
1280 uom_code
1281 FROM csi_cp_counters_v
1282 WHERE counter_id = c_counter_id;
1283 ***/
1284
1285 l_utilization_rec utilization_rec_type;
1286 l_ctr_grp_log_rec CS_CTR_CAPTURE_READING_PUB.ctr_grp_log_rec_type;
1287 l_ctr_rdg_tbl CS_CTR_CAPTURE_READING_PUB.ctr_rdg_tbl_type;
1288 l_prop_rdg_tbl CS_CTR_CAPTURE_READING_PUB.prop_rdg_tbl_type;
1289 l_return_status VARCHAR2(3);
1290 l_msg_count NUMBER;
1291 l_msg_data VARCHAR2(2000);
1292 l_msg_index_out NUMBER;
1293 l_instance_tbl instance_tbl_type;
1294 l_inst_tmp_tbl instance_tbl_type;
1295 l_parents_tbl instance_tbl_type;
1296 l_inst_idx BINARY_INTEGER;
1297 l_parent_idx BINARY_INTEGER;
1298 l_keep_flag BOOLEAN;
1299 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Cn';
1300
1301 BEGIN
1302 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1303 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1304 END IF;
1305 l_utilization_rec := p_utilization_rec;
1306 OPEN cs_counters_name_csr(l_utilization_rec.csi_item_instance_id,
1307 l_utilization_rec.counter_name);
1308 FETCH cs_counters_name_csr INTO l_cs_counters_name_csr;
1309 IF cs_counters_name_csr%FOUND THEN
1310 --p_utilization_rec.uom_code := l_cs_counters_name_csr.uom_code;
1311 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1312 FND_LOG.STRING(G_LEVEL_STATEMENT,
1313 L_DEBUG_KEY,
1314 'cs_counters_name_csr%FOUND. Calling update_reading_ins');
1315 END IF;
1316 update_reading_ins(l_utilization_rec);
1317 CLOSE cs_counters_name_csr;
1318 ELSE
1319 CLOSE cs_counters_name_csr;
1320 IF (NVL(l_utilization_rec.delta_flag, 'N') = 'N') THEN
1321 --The start instance doesn't have the given counter associated, reading value is
1322 --net reading but the counter doesn't exists for the start instance
1323 --So raise error and return, no need to go further down.
1324 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
1325 FND_MSG_PUB.add;
1326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1327 ELSE
1328 IF (NVL(l_utilization_rec.cascade_flag,'N') = 'Y') THEN
1329 --The start instance doesn't have the given counter associated, reading value is delta
1330 --reading and cascade_flag = 'Y', then get all of its highest level components which
1331 --have the given counter associated but their ancestors don't.
1332 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1333 FND_LOG.STRING(G_LEVEL_STATEMENT,
1334 L_DEBUG_KEY,
1335 'cs_counters_name_csr%FOUND is false. l_utilization_rec.delta_flag is Y and l_utilization_rec.cascade_flag is Y');
1336 END IF;
1337 l_inst_idx :=1;
1338 -- Begin changes made by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1339 OPEN get_components_counter(l_utilization_rec.csi_item_instance_id, l_utilization_rec.counter_name);
1340 FETCH get_components_counter BULK COLLECT INTO l_instance_tbl;
1341 CLOSE get_components_counter;
1342 l_inst_tmp_tbl := l_instance_tbl;
1343 /**
1344 FOR l_get_components_counter IN get_components_counter(l_utilization_rec.csi_item_instance_id,
1345 l_utilization_rec.counter_name) LOOP
1346 l_instance_tbl(l_inst_idx) := l_get_components_counter.subject_id;
1347 l_inst_tmp_tbl(l_inst_idx) := l_instance_tbl(l_inst_idx);
1348 --dbms_output.put_line('l_instance_tbl:'||l_inst_idx||' '||l_instance_tbl(l_inst_idx));
1349 l_inst_idx := l_inst_idx + 1;
1350 END LOOP;
1351 **/
1352 -- End changes made by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1353 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1354 FND_LOG.STRING(G_LEVEL_STATEMENT,
1355 L_DEBUG_KEY,
1356 'l_instance_tbl.count = ' || l_instance_tbl.count);
1357 END IF;
1358 IF l_instance_tbl.count > 0 THEN
1359 FOR i IN l_instance_tbl.FIRST..l_instance_tbl.LAST LOOP
1360 l_keep_flag := TRUE;
1361 --l_inst_tmp_tbl := l_instance_tbl;
1362 --l_inst_tmp_tbl.DELETE(i);
1363 l_parent_idx := 1;
1364 --l_parents_tbl needs to be reset after each loop
1365 IF l_parents_tbl.COUNT > 0 THEN
1366 l_parents_tbl.DELETE;
1367 END IF;
1368 FOR l_get_all_ancestors IN get_all_ancestors(l_instance_tbl(i), l_utilization_rec.csi_item_instance_id) LOOP
1369 l_parents_tbl(l_parent_idx) := l_get_all_ancestors.object_id;
1370 l_parent_idx := l_parent_idx + 1;
1371 END LOOP;
1372 IF l_parents_tbl.COUNT > 0 THEN
1373 <<OUTER1>>
1374 FOR j IN l_parents_tbl.FIRST..l_parents_tbl.LAST LOOP
1375 IF l_inst_tmp_tbl.COUNT > 0 THEN
1376 FOR k IN l_inst_tmp_tbl.FIRST..l_inst_tmp_tbl.LAST LOOP
1377 IF l_parents_tbl(j) = l_inst_tmp_tbl(k) THEN
1378 l_keep_flag := FALSE;
1379 EXIT OUTER1;
1380 END IF;
1381 END LOOP;
1382 END IF;
1383 END LOOP;
1384 END IF;
1385 IF NOT l_keep_flag AND l_instance_tbl.EXISTS(i) THEN
1386 l_instance_tbl.DELETE(i);
1387 END IF;
1388 END LOOP;
1389 END IF;
1390 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1391 FND_LOG.STRING(G_LEVEL_STATEMENT,
1392 L_DEBUG_KEY,
1393 'After processing, l_instance_tbl.count = ' || l_instance_tbl.count);
1394 END IF;
1395 FOR i IN l_instance_tbl.FIRST..l_instance_tbl.LAST LOOP
1396 IF l_instance_tbl.EXISTS(i) THEN
1397 l_utilization_rec.csi_item_instance_id := l_instance_tbl(i);
1398 --p_utilization_rec.cascade_flag := 'Y';
1399 update_reading_ins(l_utilization_rec);
1400 END IF;
1401 END LOOP;
1402 ELSE
1403 --The start instance doesn't have the given counter associated, reading value is delta
1404 --reading and cascade_flag = 'N', then raise error and stop.
1405 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
1406 FND_MSG_PUB.add;
1407 RAISE FND_API.G_EXC_ERROR;
1408 END IF;
1409 END IF;
1410 END IF;
1411 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1412 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure.');
1413 END IF;
1414 END update_reading_cn;
1415
1416 ---------------------------------------------------------------------------------
1417
1418 PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
1419 p_based_on IN VARCHAR2)
1420 IS
1421 l_utilization_rec utilization_rec_type;
1422 i NUMBER;
1423 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_All';
1424
1425 /** Begin changes by jaramana on October 29, 2007 for bug 6513576
1426 Cannot use cs_counters and cs_counter_groups anymore **/
1427 --Given start instance_id and uom_code, this cursor is used to get all of the distinct
1428 --counters this start instance has
1429 CURSOR get_inst_counters(c_instance_id NUMBER, c_uom_code VARCHAR2) IS
1430 SELECT DISTINCT CCCV.COUNTER_TEMPLATE_NAME counter_name
1431 FROM CSI_CP_COUNTERS_V CCCV
1432 WHERE CCCV.UOM_CODE = c_uom_code
1433 AND CCCV.CUSTOMER_PRODUCT_ID = c_instance_id
1434 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1435 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);
1436
1437 --Given start instance_id and uom_code, this cursor is used to get all of the distinct
1438 --counters this start instance and all of its components have
1439 -- Cursor changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1440 -- Removed the use of csi_cp_counters_v
1441 CURSOR get_all_counters(c_instance_id NUMBER, c_uom_code VARCHAR2) IS
1442 SELECT DISTINCT C.COUNTER_TEMPLATE_NAME counter_name
1443 FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
1444 WHERE C.UOM_CODE = c_uom_code
1445 AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
1446 AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate)
1447 AND C.COUNTER_ID = CCA.COUNTER_ID(+)
1448 AND CCA.SOURCE_OBJECT_CODE = 'CP'
1449 AND CCA.SOURCE_OBJECT_ID IN (SELECT c_instance_id
1450 FROM DUAL
1451 UNION ALL
1452 SELECT subject_id
1453 FROM csi_ii_relationships CI
1454 START WITH object_id = c_instance_id
1455 AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
1456 AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
1457 AND CI.relationship_type_code = 'COMPONENT-OF'
1458 CONNECT BY object_id = PRIOR subject_id
1459 AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
1460 AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
1461 AND CI.relationship_type_code = 'COMPONENT-OF');
1462
1463 /** End changes by jaramana on October 29, 2007 for bug 6513576 **/
1464
1465 BEGIN
1466 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1467 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1468 END IF;
1469 l_utilization_rec := p_utilization_rec;
1470 IF (p_based_on = ':COUNTERID') THEN
1471 -- No cascade issue no matter whether cascade flag is set to 'Y' or 'N', just update the
1472 -- specific counter_id
1473 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1474 FND_LOG.STRING(G_LEVEL_STATEMENT,
1475 L_DEBUG_KEY,
1476 'p_based_on is COUNTERID. Calling update_reading_id');
1477 END IF;
1478 update_reading_id(l_utilization_rec);
1479 ELSIF (p_based_on = ':COUNTER') THEN
1480 --For a given pair of counter_name and instance_id, it will uniquely identify the counter_id(confirmed).
1481 --If the counter_name exsits in the start instance, and the cascade_flag = 'Y', then the counter ratio
1482 --for the components are calculated from the start instance(here reading_value(can be either delta
1483 --reading or net reading) refers to the counter of
1484 --the start instance). Otherwise, if the counter_name doesn't exist for the start instance and if the
1485 --reading value is net reading then raise error and stop, and if the reading value is delta reading,
1486 --then we get all of the highest level components of the start instance which
1487 --have the counter_name associated but all of their ancestors don't and make each of these components
1488 --as start point(assuming reading_value (only delta reading) refers to their counters) and calculate the
1489 --counter ratio of their own components.
1490 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1491 FND_LOG.STRING(G_LEVEL_STATEMENT,
1492 L_DEBUG_KEY,
1493 'p_based_on is COUNTER. Calling update_reading_cn');
1494 END IF;
1495 update_reading_cn(l_utilization_rec);
1496 ELSIF (p_based_on = ':UOM') THEN
1497 --For a given pair of UOM and instance_id, we may get none, one or multiple counters.
1498 --IF cascade_flag = 'Y' and delta_flag = 'Y', then we traverse down the UC tree with the start instance
1499 --as the starting point and get all of the distinct counters with the same UOM. Otherwise if delta_flag = 'N',
1500 --then we just get all the counters associated with the start instance with the same UOM. For all of these
1501 --distinct counters loop just like p_based_on = ':COUNTER'.
1502 i := 0;
1503 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1504 FND_LOG.STRING(G_LEVEL_STATEMENT,
1505 L_DEBUG_KEY,
1506 'p_based_on is UOM, l_utilization_rec.delta_flag = ' || l_utilization_rec.delta_flag);
1507 END IF;
1508 IF NVL(l_utilization_rec.delta_flag, 'N') = 'N' THEN
1509 FOR l_get_inst_counters IN get_inst_counters(l_utilization_rec.csi_item_instance_id,
1510 l_utilization_rec.uom_code) LOOP
1511 l_utilization_rec.counter_name := l_get_inst_counters.counter_name;
1512 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1513 FND_LOG.STRING(G_LEVEL_STATEMENT,
1514 L_DEBUG_KEY,
1515 'l_get_inst_counters.counter_name = ' || l_get_inst_counters.counter_name ||
1516 ', i = ' || i || ', Calling update_reading_cn');
1517 END IF;
1518 update_reading_cn(l_utilization_rec);
1519 i := i+1;
1520 END LOOP;
1521 ELSE
1522 FOR l_get_all_counters IN get_all_counters(l_utilization_rec.csi_item_instance_id,
1523 l_utilization_rec.uom_code) LOOP
1524 l_utilization_rec.counter_name := l_get_all_counters.counter_name;
1525 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1526 FND_LOG.STRING(G_LEVEL_STATEMENT,
1527 L_DEBUG_KEY,
1528 ' l_get_all_counters.counter_name = ' || l_get_all_counters.counter_name ||
1529 ', i = ' || i || ', Calling update_reading_cn');
1530 END IF;
1531 update_reading_cn(l_utilization_rec);
1532 i := i+1;
1533 END LOOP;
1534 END IF;
1535 IF i=0 THEN
1536 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
1537 FND_MSG_PUB.add;
1538 RAISE FND_API.G_EXC_ERROR;
1539 END IF;
1540 END IF;
1541 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1542 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure.');
1543 END IF;
1544 END update_reading_all;
1545
1546 ---------------------------------------------------------------------------------
1547
1548 FUNCTION get_counter_ratio(p_start_instance_id IN NUMBER,
1549 p_desc_instance_id IN NUMBER,
1550 p_uom_code IN VARCHAR2,
1551 p_rule_code IN VARCHAR2)
1552 RETURN NUMBER IS
1553 -- for counter rules given a relationship_id.
1554 CURSOR ahl_ctr_rule_csr (c_relationship_id NUMBER,
1555 c_uom_code VARCHAR2,
1556 c_rule_code VARCHAR2) IS
1557 SELECT ratio
1558 FROM ahl_ctr_update_rules
1559 WHERE relationship_id = c_relationship_id
1560 AND rule_code = c_rule_code
1561 AND uom_code = c_uom_code;
1562
1563 CURSOR get_ancestors(c_start_instance_id NUMBER, c_desc_instance_id NUMBER) IS
1564 SELECT object_id,
1565 subject_id,
1566 to_number(position_reference) relationship_id
1567 FROM csi_ii_relationships
1568 START WITH subject_id = c_desc_instance_id
1569 AND relationship_type_code = 'COMPONENT-OF'
1570 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1571 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1572 CONNECT BY subject_id = PRIOR object_id
1573 AND subject_id <> c_start_instance_id
1574 AND relationship_type_code = 'COMPONENT-OF'
1575 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1576 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1577
1578 l_rule_code ahl_ctr_update_rules.rule_code%TYPE := p_rule_code;
1579 l_ratio NUMBER;
1580 l_total_ratio NUMBER;
1581 l_position_ref NUMBER;
1582 l_uom_code ahl_ctr_update_rules.uom_code%TYPE;
1583 l_match_found_flag BOOLEAN;
1584 l_table_count NUMBER;
1585 l_posn_master_config_id NUMBER;
1586 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Counter_Ratio';
1587
1588 BEGIN
1589 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1590 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Function' ||
1591 ', p_rule_code = ' || p_rule_code);
1592 END IF;
1593 IF (p_rule_code IS NULL OR p_rule_code = FND_API.G_MISS_CHAR) THEN
1594 l_rule_code := 'STANDARD';
1595 END IF;
1596
1597 l_total_ratio := 1;
1598
1599 FOR l_get_ancestor IN get_ancestors(p_start_instance_id, p_desc_instance_id) LOOP
1600 OPEN ahl_ctr_rule_csr(l_get_ancestor.relationship_id, p_uom_code, l_rule_code);
1601 FETCH ahl_ctr_rule_csr INTO l_ratio;
1602 IF ahl_ctr_rule_csr%FOUND THEN
1603 l_total_ratio := l_total_ratio * l_ratio;
1604 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1605 FND_LOG.STRING(G_LEVEL_STATEMENT,
1606 L_DEBUG_KEY,
1607 'l_ratio = ' || l_ratio || ', Setting l_total_ratio to ' || l_total_ratio);
1608 END IF;
1609 END IF;
1610 CLOSE ahl_ctr_rule_csr;
1611 END LOOP;
1612
1613 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1614 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Function' ||
1615 '. Returning l_total_ratio as ' || l_total_ratio);
1616 END IF;
1617
1618 RETURN l_total_ratio;
1619
1620 EXCEPTION
1621 WHEN INVALID_NUMBER THEN
1622 FND_MESSAGE.set_name('AHL','AHL_UC_INST_POSITION_INVALID');
1623 FND_MSG_PUB.add;
1624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1625 END get_counter_ratio;
1626
1627 END AHL_UC_UTILIZATION_PVT;
[Home] [Help]