1 PACKAGE BODY AHL_UC_UTILIZATION_PVT AS
2 /* $Header: AHLVUCUB.pls 120.4.12010000.3 2008/12/04 01:15:24 jaramana 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 /*
777 SELECT nvl(counter_reading,0) counter_reading,
778 counter_group_id
779 FROM csi_cp_counters_v
780 WHERE counter_id = c_counter_id
781 -- order by added by jaramana on June 13, 2007 to fix bug 6123549
782 -- Since csi_cp_counters_v was modified by CSI in R12 to get all
783 -- counter readings and not just the most recent one.
784 ORDER BY value_timestamp desc;
785 */
786 SELECT (select ccr.net_reading
787 from csi_counter_readings ccr
788 where ccr.counter_value_id = c.ctr_val_max_seq_no) counter_reading,
789 DEFAULTED_GROUP_ID counter_group_id
790 FROM CSI_COUNTERS_B C
791 WHERE counter_id = c_counter_id;
792
793 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
794 CURSOR is_counter_change_type_csr(c_counter_id NUMBER) IS
795 select 'Y' from CSI_COUNTERS_B
796 where counter_id = c_counter_id
797 and reading_type = 2;
798
799 l_change_type_flag VARCHAR2(1) := 'N';
800 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
801
802 l_counter_grp_id NUMBER;
803 l_reading_value NUMBER;
804 l_start_current_value NUMBER;
805
806 l_ctr_grp_log_rec CS_CTR_CAPTURE_READING_PUB.ctr_grp_log_rec_type;
807 l_ctr_rdg_tbl CS_CTR_CAPTURE_READING_PUB.ctr_rdg_tbl_type;
808 l_prop_rdg_tbl CS_CTR_CAPTURE_READING_PUB.prop_rdg_tbl_type;
809 l_return_status VARCHAR2(3);
810 l_msg_count NUMBER;
811 l_msg_data VARCHAR2(2000);
812 l_msg_index_out NUMBER;
813 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Id';
814
815 BEGIN
816 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
817 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
818 END IF;
819
820 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
821 OPEN is_counter_change_type_csr(p_utilization_rec.counter_id);
822 FETCH is_counter_change_type_csr INTO l_change_type_flag;
823 IF (is_counter_change_type_csr%FOUND) THEN
824 l_change_type_flag := 'Y';
825 ELSE
826 l_change_type_flag := 'N';
827 END IF;
828 CLOSE is_counter_change_type_csr;
829 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
830 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Counter is change type: ' || l_change_type_flag);
831 END IF;
832 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
833
834 OPEN get_current_value_id(p_utilization_rec.counter_id);
835 FETCH get_current_value_id INTO l_start_current_value, l_counter_grp_id;
836 IF get_current_value_id%NOTFOUND THEN
837 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
838 FND_MSG_PUB.add;
839 CLOSE get_current_value_id;
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 ELSE
842 CLOSE get_current_value_id;
843 END IF;
844 IF p_utilization_rec.delta_flag = 'Y' THEN
845 -- Begin Changes by jaramana on Feb 20, 2008 for Bug 6782765
846 IF (l_change_type_flag = 'Y') THEN
847 -- For Change type counters, always pass the delta value
848 l_reading_value := p_utilization_rec.reading_value;
849 ELSE
850 -- For other counters, always pass the total value
851 l_reading_value := l_start_current_value+p_utilization_rec.reading_value;
852 END IF;
853 ELSE
854 -- Not Delta
855 IF (l_change_type_flag = 'Y') THEN
856 -- For Change type counters, delta_flag should always be Y
857 FND_MESSAGE.set_name('AHL', 'AHL_UC_DELTA_FLAG_INVALID');
858 FND_MESSAGE.set_token('FLAG', p_utilization_rec.delta_flag);
859 FND_MSG_PUB.add;
860 RAISE FND_API.G_EXC_ERROR;
861 END IF;
862 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
863 l_reading_value := p_utilization_rec.reading_value;
864 END IF;
865 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
866 FND_LOG.STRING(G_LEVEL_STATEMENT,
867 L_DEBUG_KEY,
868 'l_start_current_value = ' || l_start_current_value ||
869 ', p_utilization_rec.delta_flag = ' || p_utilization_rec.delta_flag ||
870 ', p_utilization_rec.reading_value = ' || p_utilization_rec.reading_value ||
871 ', Setting l_reading_value to ' || l_reading_value);
872 END IF;
873
874 -- Update reading for the counter.
875 l_ctr_grp_log_rec.counter_group_id := l_counter_grp_id;
876 l_ctr_grp_log_rec.value_timestamp := SYSDATE;
877 l_ctr_grp_log_rec.source_transaction_id := p_utilization_rec.csi_item_instance_id;
878 l_ctr_grp_log_rec.source_transaction_code := 'CP';
879
880 l_ctr_rdg_tbl(1).counter_id := p_utilization_rec.counter_id;
881 l_ctr_rdg_tbl(1).value_timestamp := p_utilization_rec.reading_date;
882 l_ctr_rdg_tbl(1).counter_reading := l_reading_value;
883 l_ctr_rdg_tbl(1).valid_flag := 'Y';
884 -- Changed from 'Y' to 'N' by jaramana on July 10, 2007 for bug 6127957
885 l_ctr_rdg_tbl(1).override_valid_flag := 'N';
886 --Call CS Counter Update API to update the counter_reading of the start instance
887 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
888 FND_LOG.STRING(G_LEVEL_STATEMENT,
889 L_DEBUG_KEY,
890 'About to call CS_CTR_CAPTURE_READING_PUB.capture_counter_reading');
891 END IF;
892 CS_CTR_CAPTURE_READING_PUB.capture_counter_reading(
893 p_api_version_number => 1.0,
894 p_init_msg_list => FND_API.G_FALSE,
895 p_commit => FND_API.G_FALSE,
896 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
897 p_ctr_grp_log_rec => l_ctr_grp_log_rec,
898 p_ctr_rdg_tbl => l_ctr_rdg_tbl,
899 p_prop_rdg_tbl => l_prop_rdg_tbl,
900 x_return_status => l_return_status,
901 x_msg_count => l_msg_count,
902 x_msg_data => l_msg_data );
903 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
904 FND_LOG.STRING(G_LEVEL_STATEMENT,
905 L_DEBUG_KEY,
906 'Returned from call to CS_CTR_CAPTURE_READING_PUB.capture_counter_reading. l_return_status = ' || l_return_status);
907 END IF;
908 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
910 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
911 RAISE FND_API.G_EXC_ERROR;
912 END IF;
913 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
914 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end',
915 'Exiting Procedure.');
916 END IF;
917 END update_reading_id;
918
919 ---------------------------------------------------------------------------------
920
921 --Update the counter reading value of the start instance and all of its components (if
922 --cascade_flag = 'Y'). This procedure assumes the given counter name exists for the start
923 --instance
924 PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type) IS
925 CURSOR csi_relationships_csr(c_csi_item_instance_id NUMBER) IS
926 SELECT subject_id csi_item_instance_id, position_reference
927 FROM csi_ii_relationships
928 START WITH object_id = c_csi_item_instance_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 CONNECT BY object_id = PRIOR subject_id
933 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
934 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
935 AND relationship_type_code = 'COMPONENT-OF';
936
937 -- Get current counter reading values based on the counter_id
938 -- Cursor get_current_value_id commented out by jaramana on on June 13, 2007
939 -- while fixing bug 6123549 since this cursor is not used in this procedure at all.
940 /***
941 CURSOR get_current_value_id(c_counter_id NUMBER) IS
942 SELECT nvl(counter_reading,0) counter_reading,
943 counter_group_id,
944 uom_code
945 FROM csi_cp_counters_v
946 WHERE counter_id = c_counter_id;
947 ***/
948 -- Get current counter reading values based on the counter_name
949 -- Cursor changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
950 -- Removed the use of csi_cp_counters_v and uptook
951 -- changes made via Counters bug 7561677 (FP of 7374316)
952 CURSOR get_current_value_name(c_instance_id NUMBER,
953 c_counter_name VARCHAR2) IS
954 /*
955 SELECT nvl(counter_reading,0) counter_reading,
956 counter_group_id,
957 counter_id,
958 start_date_active,
959 end_date_active,
960 uom_code
961 FROM csi_cp_counters_v
962 WHERE customer_product_id = c_instance_id
963 AND counter_template_name = c_counter_name
964 AND trunc(nvl(start_date_active, sysdate)) <= trunc(sysdate)
965 AND trunc(nvl(end_date_active, sysdate+1)) > trunc(sysdate)
966 ORDER BY value_timestamp desc;
967 */
968 SELECT C.DEFAULTED_GROUP_ID counter_group_id,
969 (select ccr.net_reading
970 from csi_counter_readings ccr
971 where ccr.counter_value_id = c.ctr_val_max_seq_no) counter_reading,
972 C.COUNTER_ID counter_id,
973 C.START_DATE_ACTIVE start_date_active,
974 C.END_DATE_ACTIVE end_date_active,
975 C.UOM_CODE uom_code
976 FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
977 WHERE C.COUNTER_ID = CCA.COUNTER_ID(+)
978 AND CCA.SOURCE_OBJECT_CODE = 'CP'
979 AND CCA.SOURCE_OBJECT_ID = c_instance_id
980 AND C.COUNTER_TEMPLATE_NAME = c_counter_name
981 AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
982 AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate);
983
984 l_get_current_value_name get_current_value_name%ROWTYPE;
985
986 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
987 CURSOR is_counter_change_type_csr(c_counter_id NUMBER) IS
988 select 'Y' from CSI_COUNTERS_B
989 where counter_id = c_counter_id
990 and reading_type = 2;
991
992 l_change_type_flag VARCHAR2(1) := 'N';
993 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
994
995 l_utilization_rec utilization_rec_type;
996 l_ratio NUMBER;
997 l_reading_value NUMBER;
998 l_start_current_value NUMBER;
999 l_uom_code cs_counters.uom_code%TYPE;
1000
1001 l_ctr_grp_log_rec CS_CTR_CAPTURE_READING_PUB.ctr_grp_log_rec_type;
1002 l_ctr_rdg_tbl CS_CTR_CAPTURE_READING_PUB.ctr_rdg_tbl_type;
1003 l_prop_rdg_tbl CS_CTR_CAPTURE_READING_PUB.prop_rdg_tbl_type;
1004 l_return_status VARCHAR2(3);
1005 l_msg_count NUMBER;
1006 l_msg_data VARCHAR2(2000);
1007 l_msg_index_out NUMBER;
1008 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Ins';
1009
1010 BEGIN
1011 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1012 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1013 END IF;
1014 l_utilization_rec := p_utilization_rec;
1015 --First get the current counter reading value for the start instance
1016 --This procedure assumes that counter exists for the instance
1017 OPEN get_current_value_name(l_utilization_rec.csi_item_instance_id,
1018 l_utilization_rec.counter_name);
1019 FETCH get_current_value_name INTO l_get_current_value_name;
1020 IF get_current_value_name%NOTFOUND THEN
1021 FND_MESSAGE.set_name('AHL','AHL_UC_CTR_INST_INVALID');
1022 FND_MESSAGE.set_token('COUNTER', l_utilization_rec.counter_name);
1023 FND_MESSAGE.set_token('INSTANCE', l_utilization_rec.csi_item_instance_id);
1024 FND_MSG_PUB.add;
1025 CLOSE get_current_value_name;
1026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1027 ELSE
1028 CLOSE get_current_value_name;
1029 END IF;
1030 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1031 FND_LOG.STRING(G_LEVEL_STATEMENT,
1032 L_DEBUG_KEY,
1033 'l_get_current_value_name.counter_id = ' || l_get_current_value_name.counter_id ||
1034 ', l_get_current_value_name.counter_reading = ' || l_get_current_value_name.counter_reading ||
1035 ', l_get_current_value_name.uom_code = ' || l_get_current_value_name.uom_code ||
1036 ', l_get_current_value_name.start_date_active = ' || l_get_current_value_name.start_date_active ||
1037 ', l_get_current_value_name.end_date_active = ' || l_get_current_value_name.end_date_active);
1038 END IF;
1039
1040 l_utilization_rec.counter_id := l_get_current_value_name.counter_id;
1041
1042 -- Begin changes by jaramana on Feb 20, 2008 for Bug 6782765
1043 OPEN is_counter_change_type_csr(l_utilization_rec.counter_id);
1044 FETCH is_counter_change_type_csr INTO l_change_type_flag;
1045 IF (is_counter_change_type_csr%FOUND) THEN
1046 l_change_type_flag := 'Y';
1047 ELSE
1048 l_change_type_flag := 'N';
1049 END IF;
1050 CLOSE is_counter_change_type_csr;
1051 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1052 FND_LOG.STRING(G_LEVEL_STATEMENT, L_DEBUG_KEY, 'Counter is change type: ' || l_change_type_flag);
1053 END IF;
1054 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
1055
1056 l_start_current_value := l_get_current_value_name.counter_reading;
1057 l_uom_code := l_get_current_value_name.uom_code;
1058 update_reading_id(l_utilization_rec);
1059 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1060 FND_LOG.STRING(G_LEVEL_STATEMENT,
1061 L_DEBUG_KEY, 'Returned from call to update_reading_id');
1062 END IF;
1063
1064 IF (l_utilization_rec.cascade_flag = 'Y') THEN
1065 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1066 FND_LOG.STRING(G_LEVEL_STATEMENT,
1067 L_DEBUG_KEY, 'l_utilization_rec.cascade_flag = Y');
1068 END IF;
1069 -- Process for config items.
1070 FOR child_rec IN csi_relationships_csr(p_utilization_rec.csi_item_instance_id) LOOP
1071 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1072 FND_LOG.STRING(G_LEVEL_STATEMENT,
1073 L_DEBUG_KEY, 'Processing child instance ' || child_rec.csi_item_instance_id);
1074 END IF;
1075 -- Get counter ratio.
1076 IF ahl_util_uc_pkg.extra_node(child_rec.csi_item_instance_id, l_utilization_rec.csi_item_instance_id) THEN
1077 l_ratio := 1;
1078 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1079 FND_LOG.STRING(G_LEVEL_STATEMENT,
1080 L_DEBUG_KEY, 'Instance is an extra node. Setting l_ratio to 1');
1081 END IF;
1082 ELSE
1083 l_ratio := get_counter_ratio(l_utilization_rec.csi_item_instance_id,
1084 child_rec.csi_item_instance_id,
1085 l_uom_code,
1086 l_utilization_rec.rule_code);
1087 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1088 FND_LOG.STRING(G_LEVEL_STATEMENT,
1089 L_DEBUG_KEY, 'Instance is not an extra node. Setting l_ratio to ' || l_ratio);
1090 END IF;
1091 END IF;
1092
1093 -- Get current counter reading values
1094 OPEN get_current_value_name(child_rec.csi_item_instance_id, l_utilization_rec.counter_name);
1095 FETCH get_current_value_name INTO l_get_current_value_name;
1096 IF get_current_value_name%FOUND THEN
1097 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1098 FND_LOG.STRING(G_LEVEL_STATEMENT,
1099 L_DEBUG_KEY, 'get_current_value_name%FOUND is TRUE. ' ||
1100 'l_get_current_value_name.counter_reading = ' || l_get_current_value_name.counter_reading);
1101 END IF;
1102 --Else the given counter name doesn't apply to this instance
1103 --Ensure that if the same counter existing for the component instance, then the counter's
1104 --UOM should be exactly the same as that of start instance's counter. Cheng has confirmed
1105 --this point with Barry.
1106 IF l_get_current_value_name.uom_code <> l_uom_code THEN
1107 FND_MESSAGE.set_name('AHL','AHL_UC_CTR_UOM_INVALID');
1108 FND_MESSAGE.set_token('COUNTER', l_utilization_rec.counter_name);
1109 FND_MESSAGE.set_token('INSTANCE', child_rec.csi_item_instance_id);
1110 FND_MESSAGE.set_token('UOM', l_get_current_value_name.uom_code);
1111 FND_MSG_PUB.add;
1112 CLOSE get_current_value_name;
1113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1114 ELSE
1115 CLOSE get_current_value_name;
1116 END IF;
1117
1118 IF l_utilization_rec.delta_flag = 'Y' THEN
1119 -- Begin Changes by jaramana on Feb 20, 2008 for Bug 6782765
1120 IF (l_change_type_flag = 'Y') THEN
1121 -- For Change type counters, always pass the multiplied delta value
1122 l_reading_value := l_ratio*l_utilization_rec.reading_value;
1123 ELSE
1124 -- For other counters, always pass the total value
1125 l_reading_value := l_get_current_value_name.counter_reading+l_ratio*l_utilization_rec.reading_value;
1126 END IF;
1127 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1128 FND_LOG.STRING(G_LEVEL_STATEMENT,
1129 L_DEBUG_KEY, 'l_utilization_rec.delta_flag is Y. Setting l_reading_value to ' || l_reading_value);
1130 END IF;
1131 ELSE
1132 -- Not Delta
1133 IF (l_change_type_flag = 'Y') THEN
1134 -- For Change type counters, delta_flag should always be Y
1135 FND_MESSAGE.set_name('AHL', 'AHL_UC_DELTA_FLAG_INVALID');
1136 FND_MESSAGE.set_token('FLAG', l_utilization_rec.delta_flag);
1137 FND_MSG_PUB.add;
1138 RAISE FND_API.G_EXC_ERROR;
1139 END IF;
1140 -- End changes by jaramana on Feb 20, 2008 for Bug 6782765
1141 l_reading_value := l_get_current_value_name.counter_reading+
1142 l_ratio*(l_utilization_rec.reading_value-l_start_current_value);
1143 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1144 FND_LOG.STRING(G_LEVEL_STATEMENT,
1145 L_DEBUG_KEY, 'l_utilization_rec.delta_flag is not Y. Setting l_reading_value to ' || l_reading_value);
1146 END IF;
1147 END IF;
1148 -- Update reading for the counter.
1149 l_ctr_grp_log_rec.counter_group_id := l_get_current_value_name.counter_group_id;
1150 l_ctr_grp_log_rec.value_timestamp := SYSDATE;
1151 l_ctr_grp_log_rec.source_transaction_id := child_rec.csi_item_instance_id;
1152 l_ctr_grp_log_rec.source_transaction_code := 'CP';
1153 l_ctr_rdg_tbl(1).counter_id := l_get_current_value_name.counter_id;
1154 l_ctr_rdg_tbl(1).value_timestamp := l_utilization_rec.reading_date;
1155 l_ctr_rdg_tbl(1).counter_reading := l_reading_value;
1156 l_ctr_rdg_tbl(1).valid_flag := 'Y';
1157 -- Changed from 'Y' to 'N' by jaramana on July 10, 2007 for bug 6127957
1158 l_ctr_rdg_tbl(1).override_valid_flag := 'N';
1159 --Call CS Counter Update API to update the counter_reading of the start instance
1160 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1161 FND_LOG.STRING(G_LEVEL_STATEMENT,
1162 L_DEBUG_KEY, 'About to call CS_CTR_CAPTURE_READING_PUB.capture_counter_reading with ' ||
1163 'l_ctr_rdg_tbl(1).counter_reading = ' || l_ctr_rdg_tbl(1).counter_reading ||
1164 ', l_ctr_rdg_tbl(1).counter_id = ' || l_ctr_rdg_tbl(1).counter_id ||
1165 ', l_ctr_rdg_tbl(1).value_timestamp = ' || l_ctr_rdg_tbl(1).value_timestamp);
1166 END IF;
1167 CS_CTR_CAPTURE_READING_PUB.capture_counter_reading(
1168 p_api_version_number => 1.0,
1169 p_init_msg_list => FND_API.G_FALSE,
1170 p_commit => FND_API.G_FALSE,
1171 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1172 p_ctr_grp_log_rec => l_ctr_grp_log_rec,
1173 p_ctr_rdg_tbl => l_ctr_rdg_tbl,
1174 p_prop_rdg_tbl => l_prop_rdg_tbl,
1175 x_return_status => l_return_status,
1176 x_msg_count => l_msg_count,
1177 x_msg_data => l_msg_data);
1178 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1179 FND_LOG.STRING(G_LEVEL_STATEMENT,
1180 L_DEBUG_KEY, 'Returned from call to CS_CTR_CAPTURE_READING_PUB.capture_counter_reading. l_return_status = ' || l_return_status);
1181 END IF;
1182 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1185 RAISE FND_API.G_EXC_ERROR;
1186 END IF;
1187 ELSE
1188 CLOSE get_current_value_name;
1189 -- Added by jaramana on October 29, 2007 for bug 6513576
1190 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1191 FND_LOG.STRING(G_LEVEL_STATEMENT,
1192 L_DEBUG_KEY, 'get_current_value_name%FOUND is FALSE.');
1193 END IF;
1194 END IF;
1195 END LOOP;
1196 END IF;
1197 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1198 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure.');
1199 END IF;
1200 END update_reading_ins;
1201
1202 ---------------------------------------------------------------------------------
1203
1204 PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type) IS
1205 TYPE instance_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1206 /** Begin changes by jaramana on October 29, 2007 for bug 6513576
1207 Cannot use cs_counters and cs_counter_groups anymore **/
1208 -- Cursor changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1209 -- Removed the use of csi_cp_counters_v
1210 CURSOR get_components_counter(c_instance_id NUMBER, c_counter_name VARCHAR2) IS
1211 SELECT CI.subject_id
1212 FROM csi_ii_relationships CI
1213 WHERE EXISTS (SELECT 'X'
1214 /*
1215 FROM cs_counters CC,
1216 cs_counter_groups CG
1217 WHERE CC.counter_group_id = CG.counter_group_id
1218 AND CG.source_object_id = CI.subject_id
1219 AND CG.source_object_code = 'CP'
1220 AND CC.name = c_counter_name
1221 AND trunc(nvl(CC.start_date_active,sysdate)) <= trunc(sysdate)
1222 AND trunc(nvl(CC.end_date_active,sysdate+1)) > trunc(sysdate))
1223 */
1224 /*
1225 FROM CSI_CP_COUNTERS_V CCCV
1226 WHERE CCCV.CUSTOMER_PRODUCT_ID = CI.subject_id
1227 AND CCCV.COUNTER_TEMPLATE_NAME = c_counter_name
1228 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1229 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate))
1230 */
1231 FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
1232 WHERE CCA.SOURCE_OBJECT_ID = CI.subject_id
1233 AND C.COUNTER_ID = CCA.COUNTER_ID(+)
1234 AND CCA.SOURCE_OBJECT_CODE = 'CP'
1235 AND C.COUNTER_TEMPLATE_NAME = c_counter_name
1236 AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
1237 AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate))
1238 START WITH object_id = c_instance_id
1239 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1240 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1241 AND relationship_type_code = 'COMPONENT-OF'
1242 CONNECT BY object_id = PRIOR subject_id
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
1247 CURSOR get_all_ancestors(c_desc_instance_id NUMBER, c_ance_instance_id NUMBER) IS
1248 SELECT object_id
1249 FROM csi_ii_relationships
1250 START WITH subject_id = c_desc_instance_id
1251 AND object_id <> c_ance_instance_id
1252 -- This condition is really required because of the extreme case in which
1253 -- subject_id = c_desc_instance_id and object_id happens to be c_ance_instance_id
1254 -- thus it will include c_ance_instance_id and probably all of its ancestors if it has.
1255 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1256 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1257 AND relationship_type_code = 'COMPONENT-OF'
1258 CONNECT BY subject_id = PRIOR object_id
1259 AND object_id <> c_ance_instance_id
1260 AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
1261 AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
1262 AND relationship_type_code = 'COMPONENT-OF';
1263
1264 -- Get counters that match on a given counter name.
1265 CURSOR cs_counters_name_csr(c_csi_item_instance_id NUMBER, c_name VARCHAR2) IS
1266 /*
1267 SELECT ctr.name counter_name, ctr.counter_id, ctr.uom_code, cgrp.counter_group_id
1268 FROM cs_counter_groups cgrp, cs_counters ctr
1269 WHERE cgrp.counter_group_id = ctr.counter_group_id
1270 AND cgrp.source_object_code = 'CP'
1271 AND cgrp.source_object_id = c_csi_item_instance_id
1272 AND ctr.name = c_name
1273 AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
1274 AND trunc(sysdate) <= trunc(nvl(ctr.end_date_active,sysdate+1));
1275 */
1276 SELECT CCCV.COUNTER_TEMPLATE_NAME counter_name, CCCV.counter_id, CCCV.uom_code, CCCV.counter_group_id
1277 FROM CSI_CP_COUNTERS_V CCCV
1278 WHERE CCCV.CUSTOMER_PRODUCT_ID = c_csi_item_instance_id
1279 AND CCCV.COUNTER_TEMPLATE_NAME = c_name
1280 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1281 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);
1282 /** End changes by jaramana on October 29, 2007 for bug 6513576 **/
1283 l_cs_counters_name_csr cs_counters_name_csr%ROWTYPE;
1284
1285 -- Get current counter reading values based on the counter_id
1286 -- Cursor get_current_value_id commented out by jaramana on on June 13, 2007
1287 -- while fixing bug 6123549 since this cursor is not used in this procedure at all.
1288 /***
1289 CURSOR get_current_value_id(c_counter_id NUMBER) IS
1290 SELECT nvl(counter_reading,0) counter_reading,
1291 counter_group_id,
1292 uom_code
1293 FROM csi_cp_counters_v
1294 WHERE counter_id = c_counter_id;
1295 ***/
1296
1297 l_utilization_rec utilization_rec_type;
1298 l_ctr_grp_log_rec CS_CTR_CAPTURE_READING_PUB.ctr_grp_log_rec_type;
1299 l_ctr_rdg_tbl CS_CTR_CAPTURE_READING_PUB.ctr_rdg_tbl_type;
1300 l_prop_rdg_tbl CS_CTR_CAPTURE_READING_PUB.prop_rdg_tbl_type;
1301 l_return_status VARCHAR2(3);
1302 l_msg_count NUMBER;
1303 l_msg_data VARCHAR2(2000);
1304 l_msg_index_out NUMBER;
1305 l_instance_tbl instance_tbl_type;
1306 l_inst_tmp_tbl instance_tbl_type;
1307 l_parents_tbl instance_tbl_type;
1308 l_inst_idx BINARY_INTEGER;
1309 l_parent_idx BINARY_INTEGER;
1310 l_keep_flag BOOLEAN;
1311 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Cn';
1312
1313 BEGIN
1314 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1315 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1316 END IF;
1317 l_utilization_rec := p_utilization_rec;
1318 OPEN cs_counters_name_csr(l_utilization_rec.csi_item_instance_id,
1319 l_utilization_rec.counter_name);
1320 FETCH cs_counters_name_csr INTO l_cs_counters_name_csr;
1321 IF cs_counters_name_csr%FOUND THEN
1322 --p_utilization_rec.uom_code := l_cs_counters_name_csr.uom_code;
1323 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1324 FND_LOG.STRING(G_LEVEL_STATEMENT,
1325 L_DEBUG_KEY,
1326 'cs_counters_name_csr%FOUND. Calling update_reading_ins');
1327 END IF;
1328 update_reading_ins(l_utilization_rec);
1329 CLOSE cs_counters_name_csr;
1330 ELSE
1331 CLOSE cs_counters_name_csr;
1332 IF (NVL(l_utilization_rec.delta_flag, 'N') = 'N') THEN
1333 --The start instance doesn't have the given counter associated, reading value is
1334 --net reading but the counter doesn't exists for the start instance
1335 --So raise error and return, no need to go further down.
1336 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
1337 FND_MSG_PUB.add;
1338 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1339 ELSE
1340 IF (NVL(l_utilization_rec.cascade_flag,'N') = 'Y') THEN
1341 --The start instance doesn't have the given counter associated, reading value is delta
1342 --reading and cascade_flag = 'Y', then get all of its highest level components which
1343 --have the given counter associated but their ancestors don't.
1344 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1345 FND_LOG.STRING(G_LEVEL_STATEMENT,
1346 L_DEBUG_KEY,
1347 'cs_counters_name_csr%FOUND is false. l_utilization_rec.delta_flag is Y and l_utilization_rec.cascade_flag is Y');
1348 END IF;
1349 l_inst_idx :=1;
1350 -- Begin changes made by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1351 OPEN get_components_counter(l_utilization_rec.csi_item_instance_id, l_utilization_rec.counter_name);
1352 FETCH get_components_counter BULK COLLECT INTO l_instance_tbl;
1353 CLOSE get_components_counter;
1354 l_inst_tmp_tbl := l_instance_tbl;
1355 /**
1356 FOR l_get_components_counter IN get_components_counter(l_utilization_rec.csi_item_instance_id,
1357 l_utilization_rec.counter_name) LOOP
1358 l_instance_tbl(l_inst_idx) := l_get_components_counter.subject_id;
1359 l_inst_tmp_tbl(l_inst_idx) := l_instance_tbl(l_inst_idx);
1360 --dbms_output.put_line('l_instance_tbl:'||l_inst_idx||' '||l_instance_tbl(l_inst_idx));
1361 l_inst_idx := l_inst_idx + 1;
1362 END LOOP;
1363 **/
1364 -- End changes made by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1365 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1366 FND_LOG.STRING(G_LEVEL_STATEMENT,
1367 L_DEBUG_KEY,
1368 'l_instance_tbl.count = ' || l_instance_tbl.count);
1369 END IF;
1370 IF l_instance_tbl.count > 0 THEN
1371 FOR i IN l_instance_tbl.FIRST..l_instance_tbl.LAST LOOP
1372 l_keep_flag := TRUE;
1373 --l_inst_tmp_tbl := l_instance_tbl;
1374 --l_inst_tmp_tbl.DELETE(i);
1375 l_parent_idx := 1;
1376 --l_parents_tbl needs to be reset after each loop
1377 IF l_parents_tbl.COUNT > 0 THEN
1378 l_parents_tbl.DELETE;
1379 END IF;
1380 FOR l_get_all_ancestors IN get_all_ancestors(l_instance_tbl(i), l_utilization_rec.csi_item_instance_id) LOOP
1381 l_parents_tbl(l_parent_idx) := l_get_all_ancestors.object_id;
1382 l_parent_idx := l_parent_idx + 1;
1383 END LOOP;
1384 IF l_parents_tbl.COUNT > 0 THEN
1385 <<OUTER1>>
1386 FOR j IN l_parents_tbl.FIRST..l_parents_tbl.LAST LOOP
1387 IF l_inst_tmp_tbl.COUNT > 0 THEN
1388 FOR k IN l_inst_tmp_tbl.FIRST..l_inst_tmp_tbl.LAST LOOP
1389 IF l_parents_tbl(j) = l_inst_tmp_tbl(k) THEN
1390 l_keep_flag := FALSE;
1391 EXIT OUTER1;
1392 END IF;
1393 END LOOP;
1394 END IF;
1395 END LOOP;
1396 END IF;
1397 IF NOT l_keep_flag AND l_instance_tbl.EXISTS(i) THEN
1398 l_instance_tbl.DELETE(i);
1399 END IF;
1400 END LOOP;
1401 END IF;
1402 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1403 FND_LOG.STRING(G_LEVEL_STATEMENT,
1404 L_DEBUG_KEY,
1405 'After processing, l_instance_tbl.count = ' || l_instance_tbl.count);
1406 END IF;
1407 FOR i IN l_instance_tbl.FIRST..l_instance_tbl.LAST LOOP
1408 IF l_instance_tbl.EXISTS(i) THEN
1409 l_utilization_rec.csi_item_instance_id := l_instance_tbl(i);
1410 --p_utilization_rec.cascade_flag := 'Y';
1411 update_reading_ins(l_utilization_rec);
1412 END IF;
1413 END LOOP;
1414 ELSE
1415 --The start instance doesn't have the given counter associated, reading value is delta
1416 --reading and cascade_flag = 'N', then raise error and stop.
1417 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
1418 FND_MSG_PUB.add;
1419 RAISE FND_API.G_EXC_ERROR;
1420 END IF;
1421 END IF;
1422 END IF;
1423 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1424 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure.');
1425 END IF;
1426 END update_reading_cn;
1427
1428 ---------------------------------------------------------------------------------
1429
1430 PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
1431 p_based_on IN VARCHAR2)
1432 IS
1433 l_utilization_rec utilization_rec_type;
1434 i NUMBER;
1435 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_All';
1436
1437 /** Begin changes by jaramana on October 29, 2007 for bug 6513576
1438 Cannot use cs_counters and cs_counter_groups anymore **/
1439 --Given start instance_id and uom_code, this cursor is used to get all of the distinct
1440 --counters this start instance has
1441 CURSOR get_inst_counters(c_instance_id NUMBER, c_uom_code VARCHAR2) IS
1442 /*
1443 SELECT DISTINCT CC.NAME counter_name
1444 FROM cs_counters CC,
1445 cs_counter_groups CG
1446 WHERE CC.counter_group_id = CG.counter_group_id
1447 AND CG.source_object_code = 'CP'
1448 AND CG.source_object_id = c_instance_id
1449 AND CC.uom_code = c_uom_code
1450 AND trunc(nvl(CC.start_date_active, sysdate)) <= trunc(sysdate)
1451 AND trunc(nvl(CC.end_date_active, sysdate+1)) > trunc(sysdate);
1452 */
1453 SELECT DISTINCT CCCV.COUNTER_TEMPLATE_NAME counter_name
1454 FROM CSI_CP_COUNTERS_V CCCV
1455 WHERE CCCV.UOM_CODE = c_uom_code
1456 AND CCCV.CUSTOMER_PRODUCT_ID = c_instance_id
1457 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1458 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);
1459
1460 --Given start instance_id and uom_code, this cursor is used to get all of the distinct
1461 --counters this start instance and all of its components have
1462 -- Cursor changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
1463 -- Removed the use of csi_cp_counters_v
1464 CURSOR get_all_counters(c_instance_id NUMBER, c_uom_code VARCHAR2) IS
1465 SELECT DISTINCT C.COUNTER_TEMPLATE_NAME counter_name
1466 FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
1467 WHERE C.UOM_CODE = c_uom_code
1468 AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
1469 AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate)
1470 AND C.COUNTER_ID = CCA.COUNTER_ID(+)
1471 AND CCA.SOURCE_OBJECT_CODE = 'CP'
1472 AND CCA.SOURCE_OBJECT_ID IN (SELECT c_instance_id
1473 /*
1474 SELECT DISTINCT CCCV.COUNTER_TEMPLATE_NAME counter_name
1475 FROM CSI_CP_COUNTERS_V CCCV
1476 WHERE CCCV.UOM_CODE = c_uom_code
1477 AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
1478 AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate)
1479 AND CCCV.CUSTOMER_PRODUCT_ID IN (SELECT c_instance_id
1480 */
1481 FROM DUAL
1482 UNION ALL
1483 SELECT subject_id
1484 FROM csi_ii_relationships CI
1485 START WITH object_id = c_instance_id
1486 AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
1487 AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
1488 AND CI.relationship_type_code = 'COMPONENT-OF'
1489 CONNECT BY object_id = PRIOR subject_id
1490 AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
1491 AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
1492 AND CI.relationship_type_code = 'COMPONENT-OF');
1493
1494 /** End changes by jaramana on October 29, 2007 for bug 6513576 **/
1495
1496 BEGIN
1497 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1498 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Procedure');
1499 END IF;
1500 l_utilization_rec := p_utilization_rec;
1501 IF (p_based_on = ':COUNTERID') THEN
1502 -- No cascade issue no matter whether cascade flag is set to 'Y' or 'N', just update the
1503 -- specific counter_id
1504 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1505 FND_LOG.STRING(G_LEVEL_STATEMENT,
1506 L_DEBUG_KEY,
1507 'p_based_on is COUNTERID. Calling update_reading_id');
1508 END IF;
1509 update_reading_id(l_utilization_rec);
1510 ELSIF (p_based_on = ':COUNTER') THEN
1511 --For a given pair of counter_name and instance_id, it will uniquely identify the counter_id(confirmed).
1512 --If the counter_name exsits in the start instance, and the cascade_flag = 'Y', then the counter ratio
1513 --for the components are calculated from the start instance(here reading_value(can be either delta
1514 --reading or net reading) refers to the counter of
1515 --the start instance). Otherwise, if the counter_name doesn't exist for the start instance and if the
1516 --reading value is net reading then raise error and stop, and if the reading value is delta reading,
1517 --then we get all of the highest level components of the start instance which
1518 --have the counter_name associated but all of their ancestors don't and make each of these components
1519 --as start point(assuming reading_value (only delta reading) refers to their counters) and calculate the
1520 --counter ratio of their own components.
1521 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1522 FND_LOG.STRING(G_LEVEL_STATEMENT,
1523 L_DEBUG_KEY,
1524 'p_based_on is COUNTER. Calling update_reading_cn');
1525 END IF;
1526 update_reading_cn(l_utilization_rec);
1527 ELSIF (p_based_on = ':UOM') THEN
1528 --For a given pair of UOM and instance_id, we may get none, one or multiple counters.
1529 --IF cascade_flag = 'Y' and delta_flag = 'Y', then we traverse down the UC tree with the start instance
1530 --as the starting point and get all of the distinct counters with the same UOM. Otherwise if delta_flag = 'N',
1531 --then we just get all the counters associated with the start instance with the same UOM. For all of these
1532 --distinct counters loop just like p_based_on = ':COUNTER'.
1533 i := 0;
1534 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1535 FND_LOG.STRING(G_LEVEL_STATEMENT,
1536 L_DEBUG_KEY,
1537 'p_based_on is UOM, l_utilization_rec.delta_flag = ' || l_utilization_rec.delta_flag);
1538 END IF;
1539 IF NVL(l_utilization_rec.delta_flag, 'N') = 'N' THEN
1540 FOR l_get_inst_counters IN get_inst_counters(l_utilization_rec.csi_item_instance_id,
1541 l_utilization_rec.uom_code) LOOP
1542 l_utilization_rec.counter_name := l_get_inst_counters.counter_name;
1543 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1544 FND_LOG.STRING(G_LEVEL_STATEMENT,
1545 L_DEBUG_KEY,
1546 'l_get_inst_counters.counter_name = ' || l_get_inst_counters.counter_name ||
1547 ', i = ' || i || ', Calling update_reading_cn');
1548 END IF;
1549 update_reading_cn(l_utilization_rec);
1550 i := i+1;
1551 END LOOP;
1552 ELSE
1553 FOR l_get_all_counters IN get_all_counters(l_utilization_rec.csi_item_instance_id,
1554 l_utilization_rec.uom_code) LOOP
1555 l_utilization_rec.counter_name := l_get_all_counters.counter_name;
1556 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1557 FND_LOG.STRING(G_LEVEL_STATEMENT,
1558 L_DEBUG_KEY,
1559 ' l_get_all_counters.counter_name = ' || l_get_all_counters.counter_name ||
1560 ', i = ' || i || ', Calling update_reading_cn');
1561 END IF;
1562 update_reading_cn(l_utilization_rec);
1563 i := i+1;
1564 END LOOP;
1565 END IF;
1566 IF i=0 THEN
1567 FND_MESSAGE.set_name('AHL','AHL_UC_INST_NO_CTR_FOUND');
1568 FND_MSG_PUB.add;
1569 RAISE FND_API.G_EXC_ERROR;
1570 END IF;
1571 END IF;
1572 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1573 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Procedure.');
1574 END IF;
1575 END update_reading_all;
1576
1577 ---------------------------------------------------------------------------------
1578
1579 FUNCTION get_counter_ratio(p_start_instance_id IN NUMBER,
1580 p_desc_instance_id IN NUMBER,
1581 p_uom_code IN VARCHAR2,
1582 p_rule_code IN VARCHAR2)
1583 RETURN NUMBER IS
1584 -- for counter rules given a relationship_id.
1585 CURSOR ahl_ctr_rule_csr (c_relationship_id NUMBER,
1586 c_uom_code VARCHAR2,
1587 c_rule_code VARCHAR2) IS
1588 SELECT ratio
1589 FROM ahl_ctr_update_rules
1590 WHERE relationship_id = c_relationship_id
1591 AND rule_code = c_rule_code
1592 AND uom_code = c_uom_code;
1593
1594 CURSOR get_ancestors(c_start_instance_id NUMBER, c_desc_instance_id NUMBER) IS
1595 SELECT object_id,
1596 subject_id,
1597 to_number(position_reference) relationship_id
1598 FROM csi_ii_relationships
1599 START WITH subject_id = c_desc_instance_id
1600 AND relationship_type_code = 'COMPONENT-OF'
1601 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1602 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
1603 CONNECT BY subject_id = PRIOR object_id
1604 AND subject_id <> c_start_instance_id
1605 AND relationship_type_code = 'COMPONENT-OF'
1606 AND trunc(nvl(active_start_date,SYSDATE)) <= trunc(SYSDATE)
1607 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1608
1609 l_rule_code ahl_ctr_update_rules.rule_code%TYPE := p_rule_code;
1610 l_ratio NUMBER;
1611 l_total_ratio NUMBER;
1612 l_position_ref NUMBER;
1613 l_uom_code ahl_ctr_update_rules.uom_code%TYPE;
1614 l_match_found_flag BOOLEAN;
1615 l_table_count NUMBER;
1616 l_posn_master_config_id NUMBER;
1617 L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Get_Counter_Ratio';
1618
1619 BEGIN
1620 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1621 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.begin', 'Entering Function' ||
1622 ', p_rule_code = ' || p_rule_code);
1623 END IF;
1624 IF (p_rule_code IS NULL OR p_rule_code = FND_API.G_MISS_CHAR) THEN
1625 l_rule_code := 'STANDARD';
1626 END IF;
1627
1628 l_total_ratio := 1;
1629
1630 FOR l_get_ancestor IN get_ancestors(p_start_instance_id, p_desc_instance_id) LOOP
1631 OPEN ahl_ctr_rule_csr(l_get_ancestor.relationship_id, p_uom_code, l_rule_code);
1632 FETCH ahl_ctr_rule_csr INTO l_ratio;
1633 IF ahl_ctr_rule_csr%FOUND THEN
1634 l_total_ratio := l_total_ratio * l_ratio;
1635 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1636 FND_LOG.STRING(G_LEVEL_STATEMENT,
1637 L_DEBUG_KEY,
1638 'l_ratio = ' || l_ratio || ', Setting l_total_ratio to ' || l_total_ratio);
1639 END IF;
1640 END IF;
1641 CLOSE ahl_ctr_rule_csr;
1642 END LOOP;
1643
1644 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1645 FND_LOG.STRING(G_LEVEL_PROCEDURE, L_DEBUG_KEY || '.end', 'Exiting Function' ||
1646 '. Returning l_total_ratio as ' || l_total_ratio);
1647 END IF;
1648
1649 RETURN l_total_ratio;
1650
1651 EXCEPTION
1652 WHEN INVALID_NUMBER THEN
1653 FND_MESSAGE.set_name('AHL','AHL_UC_INST_POSITION_INVALID');
1654 FND_MSG_PUB.add;
1655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1656 END get_counter_ratio;
1657
1658 END AHL_UC_UTILIZATION_PVT;
[Home] [Help]