eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSI_COUNTER_READINGS_PVT dependencies on CSI_COUNTER_READINGS

Line 1: PACKAGE BODY CSI_COUNTER_READINGS_PVT as

1: PACKAGE BODY CSI_COUNTER_READINGS_PVT as
2: /* $Header: csivcrdb.pls 120.41.12010000.3 2008/11/25 22:43:29 anjgupta ship $ */
3: --
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_READINGS_PVT';
5: G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivcrdb.pls';

Line 4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_READINGS_PVT';

1: PACKAGE BODY CSI_COUNTER_READINGS_PVT as
2: /* $Header: csivcrdb.pls 120.41.12010000.3 2008/11/25 22:43:29 anjgupta ship $ */
3: --
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_READINGS_PVT';
5: G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivcrdb.pls';
6: --
7: FUNCTION get_reading_before_reset(p_counter_id NUMBER,
8: p_value_timestamp DATE)

Line 13: FROM csi_counter_readings

9: RETURN NUMBER IS
10:
11: CURSOR c1 IS
12: SELECT counter_reading
13: FROM csi_counter_readings
14: WHERE counter_id = p_counter_id
15: AND value_timestamp < p_value_timestamp
16: AND nvl(disabled_flag,'N') = 'N'
17: ORDER BY value_timestamp desc;

Line 34: FROM csi_counter_readings

30: RETURN NUMBER IS
31:
32: CURSOR c1 IS
33: SELECT net_reading
34: FROM csi_counter_readings
35: WHERE counter_id = p_counter_id
36: AND value_timestamp < p_value_timestamp
37: AND nvl(disabled_flag,'N') = 'N'
38: ORDER BY value_timestamp desc;

Line 59: AND EXISTS(SELECT 'Y' FROM csi_counter_readings WHERE counter_value_id=ctr_val_max_seq_no);

55: SELECT ctr_val_max_seq_no
56: INTO l_counter_value_id
57: FROM csi_counters_b
58: WHERE counter_id = p_counter_id
59: AND EXISTS(SELECT 'Y' FROM csi_counter_readings WHERE counter_value_id=ctr_val_max_seq_no);
60: EXCEPTION
61: WHEN NO_DATA_FOUND THEN
62: l_counter_value_id := NULL;
63: END;

Line 96: from CSI_COUNTER_READINGS

92: BEGIN
93: Begin
94: select 'x'
95: into l_exists
96: from CSI_COUNTER_READINGS
97: where counter_value_id = p_ctr_value_id;
98: l_return_value := TRUE;
99: Exception
100: when no_data_found then

Line 243: from CSI_COUNTER_READINGS ccr,

239: BEGIN
240: Begin
241: select 'x'
242: into l_exists
243: from CSI_COUNTER_READINGS ccr,
244: CSI_COUNTER_PROPERTIES_B ccp
245: where ccr.counter_value_id = p_ctr_value_id
246: and ccp.counter_id = ccr.counter_id
247: and ccp.counter_property_id = p_ctr_prop_id

Line 412: from CSI_COUNTER_READINGS

408: -- and ccv.reading_type <> 2; -- Exclude Change counters as resets are not allowed
409: --
410: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
411: select counter_reading,net_reading,life_to_date_reading
412: from CSI_COUNTER_READINGS
413: where counter_id = p_counter_id
414: and nvl(disabled_flag,'N') = 'N'
415: and value_timestamp < p_value_timestamp
416: ORDER BY value_timestamp desc,counter_value_id desc;

Line 504: select CSI_COUNTER_READINGS_S.nextval

500: --
501: -- Generate the Value_id for insert
502: l_process_flag := TRUE;
503: WHILE l_process_flag LOOP
504: select CSI_COUNTER_READINGS_S.nextval
505: into l_ctr_rdg_rec.counter_value_id from dual;
506: IF NOT Counter_Value_Exists(l_ctr_rdg_rec.counter_value_id) THEN
507: l_process_flag := FALSE;
508: END IF;

Line 591: -- Call the Table Handler to insert into CSI_COUNTER_READINGS

587:
588: l_ctr_rdg_rec.counter_reading := l_counter_reading;
589:
590: --
591: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
592: csi_ctr_gen_utility_pvt.put_line('Resetting target Counter '||to_char(l_ctr_rdg_rec.counter_id));
593: --
594: CSI_COUNTER_READINGS_PKG.Insert_Row(
595: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id

Line 594: CSI_COUNTER_READINGS_PKG.Insert_Row(

590: --
591: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
592: csi_ctr_gen_utility_pvt.put_line('Resetting target Counter '||to_char(l_ctr_rdg_rec.counter_id));
593: --
594: CSI_COUNTER_READINGS_PKG.Insert_Row(
595: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
596: ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
597: ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
598: ,p_COUNTER_READING => l_ctr_rdg_rec.counter_reading

Line 696: FROM csi_counter_readings

692: x_msg_count NUMBER;
693:
694: CURSOR derived_readings_cur IS
695: SELECT counter_value_id, counter_id
696: FROM csi_counter_readings
697: WHERE source_counter_value_id = p_src_counter_value_id
698: AND nvl(disabled_flag,'N') <> 'Y';
699: BEGIN
700: x_return_status := FND_API.G_RET_STS_SUCCESS;

Line 702: UPDATE CSI_COUNTER_READINGS

698: AND nvl(disabled_flag,'N') <> 'Y';
699: BEGIN
700: x_return_status := FND_API.G_RET_STS_SUCCESS;
701: FOR derived_reading IN derived_readings_cur LOOP
702: UPDATE CSI_COUNTER_READINGS
703: SET disabled_flag = 'Y',
704: last_updated_by = fnd_global.user_id,
705: last_update_date = SYSDATE,
706: object_version_number = object_version_number + 1

Line 796: IF NOT csi_counter_readings_pvt.Transaction_ID_Exists(p_txn_rec.transaction_id) THEN

792: l_process_flag := TRUE;
793: WHILE l_process_flag LOOP
794: select CSI_TRANSACTIONS_S.nextval
795: into p_txn_rec.transaction_id from dual;
796: IF NOT csi_counter_readings_pvt.Transaction_ID_Exists(p_txn_rec.transaction_id) THEN
797: l_process_flag := FALSE;
798: END IF;
799: END LOOP;
800: ELSE

Line 801: IF csi_counter_readings_pvt.Transaction_ID_Exists(p_txn_rec.transaction_id) THEN

797: l_process_flag := FALSE;
798: END IF;
799: END LOOP;
800: ELSE
801: IF csi_counter_readings_pvt.Transaction_ID_Exists(p_txn_rec.transaction_id) THEN
802: csi_ctr_gen_utility_pvt.ExitWithErrMsg
803: ( p_msg_name => 'CSI_TXN_ID_ALREADY_EXISTS'
804: ,p_token1_name => 'TRANSACTION_ID'
805: ,p_token1_val => to_char(p_txn_rec.transaction_id)

Line 1006: from CSI_COUNTER_READINGS

1002:
1003: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
1004: select counter_reading,net_reading,life_to_date_reading,
1005: value_timestamp, comments
1006: from CSI_COUNTER_READINGS
1007: where counter_id = p_counter_id
1008: and nvl(disabled_flag,'N') = 'N'
1009: and value_timestamp < p_value_timestamp
1010: ORDER BY value_timestamp desc,counter_value_id desc;

Line 1014: from CSI_COUNTER_READINGS

1010: ORDER BY value_timestamp desc,counter_value_id desc;
1011: --
1012: CURSOR NEXT_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
1013: select counter_reading,value_timestamp,reset_mode, comments
1014: from CSI_COUNTER_READINGS
1015: where counter_id = p_counter_id
1016: and nvl(disabled_flag,'N') = 'N'
1017: and value_timestamp > p_value_timestamp
1018: ORDER BY value_timestamp asc,counter_value_id asc;

Line 1023: from CSI_COUNTER_READINGS

1019: --
1020: CURSOR LATER_READINGS_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
1021: select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
1022: ,reset_mode,adjustment_type,include_target_resets, comments
1023: from CSI_COUNTER_READINGS
1024: where counter_id = p_counter_id
1025: and nvl(disabled_flag,'N') = 'N'
1026: and value_timestamp > p_value_timestamp
1027: ORDER BY value_timestamp asc, counter_value_id asc;

Line 1128: from CSI_COUNTER_READINGS

1124: IF p_txn_rec.transaction_type_id in (91,92,94,95) THEN
1125: l_rec_count := 0;
1126: select count(*)
1127: into l_rec_count
1128: from CSI_COUNTER_READINGS
1129: where counter_id = p_ctr_rdg_rec.counter_id
1130: and ROWNUM = 1;
1131: --
1132: IF l_rec_count > 0 THEN

Line 1170: from CSI_COUNTER_READINGS

1166:
1167: Begin
1168: select counter_value_id, object_version_number
1169: into l_counter_value_id, l_obj_version_num
1170: from CSI_COUNTER_READINGS
1171: where counter_id = p_ctr_rdg_rec.counter_id
1172: and value_timestamp = p_ctr_rdg_rec.value_timestamp;
1173:
1174: /* Readings return status fixes */

Line 1181: from CSI_COUNTER_READINGS

1177: select 'x'
1178: into l_exists
1179: from dual
1180: where exists (select 'x'
1181: from CSI_COUNTER_READINGS
1182: where counter_id = p_ctr_rdg_rec.counter_id
1183: and value_timestamp = p_ctr_rdg_rec.value_timestamp
1184: and counter_reading = p_ctr_rdg_rec.counter_reading);
1185: raise skip_process;

Line 1766: -- Insert into CSI_COUNTER_READINGS

1762: --
1763: p_ctr_rdg_rec.object_version_number := 1;
1764: --
1765: -- If counter_reading gets a user entered value or thru' adjustments (Previous value)
1766: -- Insert into CSI_COUNTER_READINGS
1767:
1768: csi_ctr_gen_utility_pvt.put_line( 'l_reading_type ' || l_reading_type);
1769: csi_ctr_gen_utility_pvt.put_line( 'p_ctr_rdg_rec.counter_reading ' || p_ctr_rdg_rec.counter_reading);
1770: csi_ctr_gen_utility_pvt.put_line( 'p_ctr_rdg_rec.automatic_rollover_flag ' || p_ctr_rdg_rec.automatic_rollover_flag);

Line 1852: -- Call Table Handler to insert into CSI_COUNTER_READINGS

1848: -- Need to pass back the Net and LTD Readings
1849: p_ctr_rdg_rec.net_reading := l_net_reading;
1850: p_ctr_rdg_rec.life_to_date_reading := l_ltd_reading;
1851: --
1852: -- Call Table Handler to insert into CSI_COUNTER_READINGS
1853: -- Check and Generate Counter_value_id
1854: IF p_ctr_rdg_rec.counter_value_id IS NULL OR
1855: p_ctr_rdg_rec.counter_value_id = FND_API.G_MISS_NUM THEN
1856: WHILE l_process_flag LOOP

Line 1857: select CSI_COUNTER_READINGS_S.nextval

1853: -- Check and Generate Counter_value_id
1854: IF p_ctr_rdg_rec.counter_value_id IS NULL OR
1855: p_ctr_rdg_rec.counter_value_id = FND_API.G_MISS_NUM THEN
1856: WHILE l_process_flag LOOP
1857: select CSI_COUNTER_READINGS_S.nextval
1858: into p_ctr_rdg_rec.counter_value_id from dual;
1859: IF NOT Counter_Value_Exists(p_ctr_rdg_rec.counter_value_id) THEN
1860: l_process_flag := FALSE;
1861: END IF;

Line 1879: CSI_COUNTER_READINGS_PKG.Insert_Row(

1875: END IF;
1876: --
1877:
1878: --
1879: CSI_COUNTER_READINGS_PKG.Insert_Row(
1880: px_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
1881: ,p_COUNTER_ID => p_ctr_rdg_rec.counter_id
1882: ,p_VALUE_TIMESTAMP => p_ctr_rdg_rec.value_timestamp
1883: ,p_COUNTER_READING => p_ctr_rdg_rec.counter_reading

Line 2099: UPDATE CSI_COUNTER_READINGS

2095: csi_ctr_gen_utility_pvt.put_line('Updating Later Readings for Ctr Value ID : '||to_char(later_rdg.counter_value_id));
2096:
2097: IF NVL(later_rdg.reset_mode,'X') = 'SOFT' THEN
2098: csi_ctr_gen_utility_pvt.put_line('SOFT and re-calculating net readings');
2099: UPDATE CSI_COUNTER_READINGS
2100: set net_reading = later_rdg.counter_reading, -- l_previous_net,
2101: life_to_date_reading = l_previous_ltd,
2102: last_update_date = sysdate,
2103: last_updated_by = fnd_global.user_id

Line 2121: UPDATE CSI_COUNTER_READINGS

2117: ,px_net_rdg => l_net_reading
2118: ,px_ltd_rdg => l_ltd_reading
2119: ,l_ctr_rdg_rec => p_ctr_rdg_rec -- added 6398254
2120: );
2121: UPDATE CSI_COUNTER_READINGS
2122: set net_reading = l_net_reading,
2123: life_to_date_reading = l_ltd_reading,
2124: last_update_date = sysdate,
2125: last_updated_by = fnd_global.user_id

Line 2267: -- For Reset Mode insert a separate record in CSI_COUNTER_READINGS with the Reset counter reading.

2263: END IF; -- Current counter reading not null check
2264: --
2265: IF NVL(p_ctr_rdg_rec.reset_mode,FND_API.G_MISS_CHAR) = 'SOFT' THEN
2266: -- Introduce a delay. Basically, value_timestamp of reset should be slightly > than the curr Reading
2267: -- For Reset Mode insert a separate record in CSI_COUNTER_READINGS with the Reset counter reading.
2268: -- Net and LTD readings are from the previous counter reading.
2269: -- If only Reset is performed then no need to introduce the delay.
2270: --
2271: l_reset_rdg_rec := l_temp_ctr_rdg_rec;

Line 2309: select CSI_COUNTER_READINGS_S.nextval

2305: --
2306: -- Generate the Value_id
2307: l_process_flag := TRUE;
2308: WHILE l_process_flag LOOP
2309: select CSI_COUNTER_READINGS_S.nextval
2310: into l_reset_rdg_rec.counter_value_id from dual;
2311: IF NOT Counter_Value_Exists(l_reset_rdg_rec.counter_value_id) THEN
2312: l_process_flag := FALSE;
2313: END IF;

Line 2321: -- Call the Table Handler to insert the Reset Reading into CSI_COUNTER_READINGS

2317: --
2318: IF NVL(p_ctr_rdg_rec.counter_value_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
2319: p_ctr_rdg_rec.counter_value_id := l_reset_rdg_rec.counter_value_id;
2320: END IF;
2321: -- Call the Table Handler to insert the Reset Reading into CSI_COUNTER_READINGS
2322: --
2323: csi_ctr_gen_utility_pvt.put_line('Inserting Reset Record with Ctr Value ID '||to_char(l_reset_rdg_rec.counter_value_id));
2324:
2325: --

Line 2326: CSI_COUNTER_READINGS_PKG.Insert_Row(

2322: --
2323: csi_ctr_gen_utility_pvt.put_line('Inserting Reset Record with Ctr Value ID '||to_char(l_reset_rdg_rec.counter_value_id));
2324:
2325: --
2326: CSI_COUNTER_READINGS_PKG.Insert_Row(
2327: px_COUNTER_VALUE_ID => l_reset_rdg_rec.counter_value_id
2328: ,p_COUNTER_ID => l_reset_rdg_rec.counter_id
2329: ,p_VALUE_TIMESTAMP => l_reset_rdg_rec.value_timestamp
2330: ,p_COUNTER_READING => l_reset_rdg_rec.counter_reading

Line 2569: from CSI_COUNTER_READINGS

2565: and nvl(active_end_date,(sysdate+1)) > sysdate;
2566: --
2567: CURSOR GET_NET_RDG_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
2568: select net_reading
2569: from CSI_COUNTER_READINGS
2570: where counter_id = p_counter_id
2571: and nvl(disabled_flag,'N') = 'N'
2572: and value_timestamp <= p_value_timestamp
2573: ORDER BY value_timestamp desc,counter_value_id desc;

Line 2577: from CSI_COUNTER_READINGS

2573: ORDER BY value_timestamp desc,counter_value_id desc;
2574: --
2575: CURSOR LATER_FORMULA_CUR(p_obj_ctr_id IN NUMBER,p_value_timestamp IN DATE) IS
2576: select counter_value_id,value_timestamp
2577: from CSI_COUNTER_READINGS
2578: where counter_id = p_obj_ctr_id
2579: and value_timestamp > p_value_timestamp
2580: and nvl(disabled_flag,'N') = 'N'
2581: ORDER BY value_timestamp desc, counter_value_id desc;

Line 2681: from CSI_COUNTER_READINGS

2677: Begin
2678: select '1'
2679: into l_exists from dual
2680: where exists (select 'x'
2681: from CSI_COUNTER_READINGS
2682: where counter_id = sub_cur.source_counter_id
2683: and value_timestamp = p_ctr_rdg_rec.value_timestamp
2684: and nvl(disabled_flag,'N') <> 'Y');
2685: l_other_src_captured := 'T';

Line 2720: Update CSI_COUNTER_READINGS

2716: -- counter.
2717: --
2718: IF NVL(p_ctr_rdg_rec.disabled_flag,'N') <> 'Y' THEN
2719: csi_ctr_gen_utility_pvt.put_line('Trying to Update Object Ctr : '||to_char(obj_cur.object_counter_id));
2720: Update CSI_COUNTER_READINGS
2721: set counter_reading = l_counter_reading,
2722: net_reading = l_counter_reading,
2723: life_to_date_reading = l_counter_reading,
2724: disabled_flag = 'N',

Line 2734: select CSI_COUNTER_READINGS_S.nextval

2730: IF SQL%ROWCOUNT = 0 THEN -- If update was not successfull
2731: -- Generate the Value_id for insert
2732: l_process_flag := TRUE;
2733: WHILE l_process_flag LOOP
2734: select CSI_COUNTER_READINGS_S.nextval
2735: into l_ctr_value_id from dual;
2736: IF NOT Counter_Value_Exists(l_ctr_value_id) THEN
2737: l_process_flag := FALSE;
2738: END IF;

Line 2743: CSI_COUNTER_READINGS_PKG.Insert_Row(

2739: END LOOP;
2740: --
2741: csi_ctr_gen_utility_pvt.put_line('Unable to Update. Inserting Object Ctr : '||to_char(obj_cur.object_counter_id));
2742: -- Call the Table Handler to insert the new reading
2743: CSI_COUNTER_READINGS_PKG.Insert_Row(
2744: px_COUNTER_VALUE_ID => l_ctr_value_id
2745: ,p_COUNTER_ID => obj_cur.object_counter_id
2746: ,p_VALUE_TIMESTAMP => p_ctr_rdg_rec.value_timestamp
2747: ,p_COUNTER_READING => l_counter_reading

Line 2864: Update CSI_COUNTER_READINGS

2860: END IF;
2861: END IF;
2862: ELSE -- If source counter is disabled then the corresponding formula counter reading is updated
2863: csi_ctr_gen_utility_pvt.put_line('Disabling Obj Ctr '||to_char(obj_cur.object_counter_id));
2864: Update CSI_COUNTER_READINGS
2865: set counter_reading = l_counter_reading,
2866: net_reading = l_counter_reading,
2867: life_to_date_reading = l_counter_reading,
2868: disabled_flag = decode(l_disabled_flag,'Y',decode(l_other_src_captured,'T','N','Y'),'N'),

Line 2946: Update CSI_COUNTER_READINGS

2942: IF dbms_sql.fetch_rows(l_cursor_handle) > 0 THEN
2943: dbms_sql.column_value(l_cursor_handle,1,l_counter_reading);
2944: -- Updating Formula Counter
2945: csi_ctr_gen_utility_pvt.put_line('Re-computing later formula Ctr Value ID '||to_char(later_rec.counter_value_id));
2946: Update CSI_COUNTER_READINGS
2947: set counter_reading = l_counter_reading,
2948: net_reading = l_counter_reading,
2949: life_to_date_reading = l_counter_reading,
2950: last_update_date = sysdate,

Line 3024: FROM csi_counter_readings

3020: -- Start fix for bug 6852415, part 1 of 3
3021: CURSOR base_ctrs IS
3022: SELECT counter_id AS base_ctr_id,
3023: value_timestamp AS base_ctr_timestamp
3024: FROM csi_counter_readings
3025: WHERE counter_value_id = p_ctr_rdg_rec.counter_value_id;
3026: CURSOR ctrs_to_be_calc(base_ctr_id NUMBER, base_ctr_timestamp DATE) IS
3027: SELECT counter_id,
3028: derive_function,

Line 3043: FROM CSI_COUNTERS_B ctr, CSI_COUNTER_READINGS cv

3039: CURSOR CTRS_TO_BE_CALC IS
3040: SELECT ctr.counter_id, ctr.derive_function,
3041: ctr.derive_counter_id, ctr.derive_property_id,ctr.filter_reading_count,ctr.filter_type,
3042: ctr.filter_time_uom, cv.value_timestamp as new_rdg_dt, cv.counter_id as base_ctr_id
3043: FROM CSI_COUNTERS_B ctr, CSI_COUNTER_READINGS cv
3044: WHERE cv.counter_value_id = p_ctr_rdg_rec.counter_value_id
3045: AND ctr.derive_counter_id = cv.counter_id
3046: AND NVL(ctr.start_date_active,cv.value_timestamp) <= cv.value_timestamp
3047: AND NVL(ctr.end_date_active,(cv.value_timestamp+1)) > cv.value_timestamp;

Line 3127: FROM CSI_COUNTER_READINGS

3123: l_previous_net number;
3124: --
3125: CURSOR CTR_RDGS(d1_counter_id number) is
3126: SELECT net_reading net_rdg,value_timestamp
3127: FROM CSI_COUNTER_READINGS
3128: WHERE counter_id = d1_counter_id
3129: AND value_timestamp <= p_ctr_rdg_rec.value_timestamp
3130: AND NVL(disabled_flag,'N') = 'N'
3131: order by value_timestamp desc;

Line 3182: FROM CSI_COUNTER_READINGS

3178: where uom_code=p_filter_time_uom;
3179: --
3180: CURSOR GET_FIRST_READING(d3_counter_id IN NUMBER) IS
3181: SELECT net_reading,value_timestamp
3182: FROM CSI_COUNTER_READINGS
3183: WHERE counter_id = d3_counter_id
3184: AND NVL(disabled_flag,'N') = 'N'
3185: order by value_timestamp asc;
3186: --

Line 3189: FROM CSI_COUNTER_READINGS

3185: order by value_timestamp asc;
3186: --
3187: CURSOR CTR_CUR_RDG IS
3188: SELECT net_reading
3189: FROM CSI_COUNTER_READINGS
3190: WHERE counter_value_id = p_ctr_rdg_rec.counter_value_id;
3191: --
3192: l_time_uom VARCHAR2(10);
3193: l_min_date DATE;

Line 3248: l_sqlstr_init := l_sqlstr_init || ' from CSI_COUNTER_READINGS cv ';

3244: ELSE
3245: i := 1;
3246: lj := 1;
3247: l_sqlstr_init := 'select '||ctrs.derive_function||'( nvl(net_reading,0) )';
3248: l_sqlstr_init := l_sqlstr_init || ' from CSI_COUNTER_READINGS cv ';
3249: l_sqlstr := ' where counter_value_id in (';
3250: l_sqlstr := l_sqlstr || ' select distinct cv1.counter_value_id from ';
3251: l_sqlfrom := ' CSI_COUNTER_READINGS cv1';
3252: l_sqlwhere := '';

Line 3251: l_sqlfrom := ' CSI_COUNTER_READINGS cv1';

3247: l_sqlstr_init := 'select '||ctrs.derive_function||'( nvl(net_reading,0) )';
3248: l_sqlstr_init := l_sqlstr_init || ' from CSI_COUNTER_READINGS cv ';
3249: l_sqlstr := ' where counter_value_id in (';
3250: l_sqlstr := l_sqlstr || ' select distinct cv1.counter_value_id from ';
3251: l_sqlfrom := ' CSI_COUNTER_READINGS cv1';
3252: l_sqlwhere := '';
3253: --
3254: FOR filts IN der_filters(ctrs.counter_id) LOOP
3255: l_sqlfrom := l_sqlfrom ||', CSI_CTR_PROPERTY_READINGS pv';

Line 3348: UPDATE CSI_COUNTER_READINGS

3344: l_ctr_in.source_code := p_ctr_rdg_rec.source_code;
3345: l_ctr_in.source_line_id := p_ctr_rdg_rec.source_line_id;
3346: --
3347: IF p_mode = 'UPDATE' THEN
3348: UPDATE CSI_COUNTER_READINGS
3349: set counter_reading = l_ctr_value,
3350: net_reading = l_ctr_value,
3351: life_to_date_reading = l_ctr_value,
3352: last_update_date = sysdate,

Line 3361: select CSI_COUNTER_READINGS_S.nextval

3357: ELSIF p_mode = 'CREATE' THEN
3358: -- Generate the Value_id for insert
3359: l_process_flag := TRUE;
3360: WHILE l_process_flag LOOP
3361: select CSI_COUNTER_READINGS_S.nextval
3362: into l_ctr_value_id from dual;
3363: IF NOT Counter_Value_Exists(l_ctr_value_id) THEN
3364: l_process_flag := FALSE;
3365: END IF;

Line 3369: CSI_COUNTER_READINGS_PKG.Insert_Row(

3365: END IF;
3366: END LOOP;
3367: --
3368: -- Call the Table Handler to insert the new reading
3369: CSI_COUNTER_READINGS_PKG.Insert_Row(
3370: px_COUNTER_VALUE_ID => l_ctr_value_id
3371: ,p_COUNTER_ID => l_ctr_in.counter_id
3372: ,p_VALUE_TIMESTAMP => l_ctr_in.value_timestamp
3373: ,p_COUNTER_READING => l_ctr_value

Line 3545: from CSI_COUNTER_READINGS crg,

3541: SELECT crg.counter_value_id,crg.value_timestamp,crg.counter_id object_counter_id,
3542: crg.adjustment_reading,crg.counter_reading,nvl(ccr.factor,1) factor,
3543: ccv.direction,ccv.reading_type,crg.reset_mode,
3544: ccv.uom_code object_uom_code, cct.name object_counter_name
3545: from CSI_COUNTER_READINGS crg,
3546: CSI_COUNTER_RELATIONSHIPS ccr,
3547: CSI_COUNTERS_B ccv,
3548: CSI_COUNTERS_TL cct
3549: where crg.source_counter_value_id = p_ctr_rdg_rec.counter_value_id

Line 3559: from CSI_COUNTER_READINGS

3555: and cct.language = USERENV('LANG');
3556: --
3557: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
3558: select counter_reading,net_reading,life_to_date_reading,value_timestamp
3559: from CSI_COUNTER_READINGS
3560: where counter_id = p_counter_id
3561: and nvl(disabled_flag,'N') = 'N'
3562: and value_timestamp < p_value_timestamp
3563: ORDER BY value_timestamp desc,counter_value_id desc;

Line 3830: Update CSI_COUNTER_READINGS

3826: END IF;
3827: --
3828: csi_ctr_gen_utility_pvt.put_line('Updating Target Counter Value ID : '||to_char(obj_cur.counter_value_id));
3829:
3830: Update CSI_COUNTER_READINGS
3831: set counter_reading = l_counter_reading,
3832: net_reading = l_net_reading,
3833: life_to_date_reading = l_ltd_reading,
3834: last_update_date = sysdate,

Line 4168: select CSI_COUNTER_READINGS_S.nextval

4164: --
4165: -- Generate the Value_id for insert
4166: l_process_flag := TRUE;
4167: WHILE l_process_flag LOOP
4168: select CSI_COUNTER_READINGS_S.nextval
4169: into l_ctr_rdg_rec.counter_value_id from dual;
4170: IF NOT Counter_Value_Exists(l_ctr_rdg_rec.counter_value_id) THEN
4171: l_process_flag := FALSE;
4172: END IF;

Line 4176: -- Call the Table Handler to insert into CSI_COUNTER_READINGS

4172: END IF;
4173: END LOOP;
4174: --
4175: csi_ctr_gen_utility_pvt.put_line('Inserting Target Counter ID : '||to_char(l_ctr_rdg_rec.counter_id));
4176: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
4177: CSI_COUNTER_READINGS_PKG.Insert_Row(
4178: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
4179: ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
4180: ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp

Line 4177: CSI_COUNTER_READINGS_PKG.Insert_Row(

4173: END LOOP;
4174: --
4175: csi_ctr_gen_utility_pvt.put_line('Inserting Target Counter ID : '||to_char(l_ctr_rdg_rec.counter_id));
4176: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
4177: CSI_COUNTER_READINGS_PKG.Insert_Row(
4178: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
4179: ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
4180: ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
4181: ,p_COUNTER_READING => l_counter_reading

Line 4374: CSI_COUNTER_READINGS

4370: l_ctr_val_max_seq_no NUMBER;
4371: --
4372: CURSOR CURRENT_READING_CUR(p_counter_value_id IN NUMBER) IS
4373: select * from
4374: CSI_COUNTER_READINGS
4375: where counter_value_id = p_counter_value_id;
4376: --
4377: l_curr_ctr_rdg_rec CURRENT_READING_CUR%rowtype;
4378: --

Line 4381: from CSI_COUNTER_READINGS

4377: l_curr_ctr_rdg_rec CURRENT_READING_CUR%rowtype;
4378: --
4379: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
4380: select counter_reading,net_reading,life_to_date_reading,value_timestamp
4381: from CSI_COUNTER_READINGS
4382: where counter_id = p_counter_id
4383: and nvl(disabled_flag,'N') = 'N'
4384: and value_timestamp < p_value_timestamp
4385: ORDER BY value_timestamp desc,counter_value_id desc;

Line 4390: from CSI_COUNTER_READINGS

4386: --
4387: CURSOR NEXT_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
4388: select counter_reading,value_timestamp,reset_mode,
4389: adjustment_type,automatic_rollover_flag
4390: from CSI_COUNTER_READINGS
4391: where counter_id = p_counter_id
4392: and nvl(disabled_flag,'N') = 'N'
4393: and value_timestamp > p_value_timestamp
4394: ORDER BY value_timestamp asc,counter_value_id asc;

Line 4399: from CSI_COUNTER_READINGS

4395: --
4396: CURSOR LATER_READINGS_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
4397: select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
4398: ,reset_mode,adjustment_type,include_target_resets
4399: from CSI_COUNTER_READINGS
4400: where counter_id = p_counter_id
4401: and nvl(disabled_flag,'N') = 'N'
4402: and value_timestamp > p_value_timestamp
4403: ORDER BY value_timestamp asc, counter_value_id asc;

Line 4680: -- Call Table Handler to Update CSI_COUNTER_READINGS

4676: -- Below two assignments are required by Update public API to call OKC Assembler
4677: p_ctr_rdg_rec.counter_id := l_curr_ctr_rdg_rec.counter_id;
4678: p_ctr_rdg_rec.value_timestamp := l_curr_ctr_rdg_rec.value_timestamp;
4679: --
4680: -- Call Table Handler to Update CSI_COUNTER_READINGS
4681: csi_ctr_gen_utility_pvt.put_line('Calling Update Row to Disable the Reading...');
4682: CSI_COUNTER_READINGS_PKG.Update_Row(
4683: p_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
4684: ,p_COUNTER_ID => NULL

Line 4682: CSI_COUNTER_READINGS_PKG.Update_Row(

4678: p_ctr_rdg_rec.value_timestamp := l_curr_ctr_rdg_rec.value_timestamp;
4679: --
4680: -- Call Table Handler to Update CSI_COUNTER_READINGS
4681: csi_ctr_gen_utility_pvt.put_line('Calling Update Row to Disable the Reading...');
4682: CSI_COUNTER_READINGS_PKG.Update_Row(
4683: p_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
4684: ,p_COUNTER_ID => NULL
4685: ,p_VALUE_TIMESTAMP => NULL
4686: ,p_COUNTER_READING => NULL

Line 4842: UPDATE CSI_COUNTER_READINGS

4838: END IF;
4839: --
4840: csi_ctr_gen_utility_pvt.put_line('Updating Counter Value ID '||to_char(later_rdg.counter_value_id));
4841: IF NVL(later_rdg.reset_mode,'X') = 'SOFT' THEN
4842: UPDATE CSI_COUNTER_READINGS
4843: set net_reading = l_previous_net,
4844: life_to_date_reading = l_previous_ltd,
4845: last_update_date = sysdate,
4846: last_updated_by = fnd_global.user_id

Line 4861: UPDATE CSI_COUNTER_READINGS

4857: ,px_net_rdg => l_net_reading
4858: ,px_ltd_rdg => l_ltd_reading
4859: ,l_ctr_rdg_rec => p_ctr_rdg_rec -- added 6398254
4860: );
4861: UPDATE CSI_COUNTER_READINGS
4862: set counter_reading = l_later_ctr_reading,
4863: net_reading = l_net_reading,
4864: life_to_date_reading = l_ltd_reading,
4865: last_update_date = sysdate,

Line 5486: from CSI_COUNTER_READINGS b

5482: )
5483: IS
5484: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5485: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
5486: from CSI_COUNTER_READINGS b
5487: where b.counter_id = b_counter_id
5488: and b.value_timestamp < b_rdg_date
5489: and nvl(b.disabled_flag,'N') = 'N'
5490: order by b.value_timestamp desc;

Line 5497: from CSI_COUNTER_READINGS b

5493: l_prev_value_timestamp DATE;
5494: --
5495: CURSOR POST_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5496: select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
5497: from CSI_COUNTER_READINGS b
5498: where b.counter_id = b_counter_id
5499: and b.value_timestamp > b_rdg_date
5500: and nvl(b.disabled_flag,'N') = 'N'
5501: order by b.value_timestamp asc;

Line 5508: from CSI_COUNTER_READINGS b

5504: l_post_value_timestamp DATE;
5505: --
5506: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5507: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
5508: from CSI_COUNTER_READINGS b
5509: where b.counter_id = b_counter_id
5510: and nvl(b.disabled_flag,'N') = 'N'
5511: and trunc(b.value_timestamp) = trunc(b_rdg_date)
5512: order by b.value_timestamp desc;

Line 5519: from CSI_COUNTER_READINGS b

5515: l_passed_rdg_date DATE;
5516: --
5517: CURSOR FIRST_RDG_VALUES(b_counter_id NUMBER) IS
5518: select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
5519: from CSI_COUNTER_READINGS b
5520: where b.counter_id = b_counter_id
5521: and nvl(b.disabled_flag,'N') = 'N'
5522: order by b.value_timestamp asc;
5523: --

Line 5687: from CSI_COUNTER_READINGS b

5683: l_msg_count NUMBER;
5684: --
5685: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5686: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
5687: from CSI_COUNTER_READINGS b
5688: where b.counter_id = b_counter_id
5689: and b.value_timestamp < b_rdg_date
5690: and nvl(b.disabled_flag,'N') = 'N'
5691: order by b.value_timestamp desc;

Line 5698: from CSI_COUNTER_READINGS b

5694: l_prev_value_timestamp DATE;
5695:
5696: CURSOR POST_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5697: select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
5698: from CSI_COUNTER_READINGS b
5699: where b.counter_id = b_counter_id
5700: and b.value_timestamp > b_rdg_date
5701: and nvl(b.disabled_flag,'N') = 'N'
5702: order by b.value_timestamp asc;

Line 5709: from CSI_COUNTER_READINGS b

5705: l_post_value_timestamp DATE;
5706:
5707: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5708: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
5709: from CSI_COUNTER_READINGS b
5710: where b.counter_id = b_counter_id
5711: and nvl(b.disabled_flag,'N') = 'N'
5712: and trunc(b.value_timestamp) = trunc(b_rdg_date)
5713: order by b.value_timestamp desc;

Line 5720: from CSI_COUNTER_READINGS b

5716: l_passed_rdg_date DATE;
5717:
5718: CURSOR first_rdg_values(b_counter_id NUMBER) IS
5719: select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
5720: from CSI_COUNTER_READINGS b
5721: where b.counter_id = b_counter_id
5722: and nvl(b.disabled_flag,'N') = 'N'
5723: order by b.value_timestamp asc;
5724:

Line 5961: from CSI_COUNTER_READINGS b

5957: l_msg_count NUMBER;
5958: --
5959: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5960: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
5961: from CSI_COUNTER_READINGS b
5962: where b.counter_id = b_counter_id
5963: and b.value_timestamp < b_rdg_date
5964: and nvl(b.disabled_flag,'N') = 'N'
5965: order by b.value_timestamp desc;

Line 5972: from CSI_COUNTER_READINGS b

5968: l_prev_value_timestamp DATE;
5969: --
5970: CURSOR POST_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5971: select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
5972: from CSI_COUNTER_READINGS b
5973: where b.counter_id = b_counter_id
5974: and b.value_timestamp > b_rdg_date
5975: and nvl(b.disabled_flag,'N') = 'N'
5976: order by b.value_timestamp asc;

Line 5983: from CSI_COUNTER_READINGS b

5979: l_post_value_timestamp DATE;
5980:
5981: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
5982: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
5983: from CSI_COUNTER_READINGS b
5984: where b.counter_id = b_counter_id
5985: and nvl(b.disabled_flag,'N') = 'N'
5986: and trunc(b.value_timestamp) = trunc(b_rdg_date)
5987: order by b.value_timestamp desc;

Line 5994: from CSI_COUNTER_READINGS b

5990: l_passed_rdg_date DATE;
5991:
5992: CURSOR first_rdg_values(b_counter_id NUMBER) IS
5993: select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
5994: from CSI_COUNTER_READINGS b
5995: where b.counter_id = b_counter_id
5996: and nvl(b.disabled_flag,'N') = 'N'
5997: order by b.value_timestamp asc;
5998: --

Line 6177: from CSI_COUNTER_READINGS cval

6173: j number := 0;
6174: l_min_seqno number;
6175: CURSOR CTR_RDGS(d1_counter_id number, b_prd_rdg_date DATE) IS
6176: select cval.net_reading AS NET_RDG, cval.value_timestamp
6177: from CSI_COUNTER_READINGS cval
6178: where cval.counter_id = d1_counter_id
6179: and cval.value_timestamp < b_prd_rdg_date
6180: and nvl(cval.disabled_flag,'N') = 'N'
6181: order by cval.value_timestamp desc;

Line 6434: from CSI_COUNTER_READINGS b

6430: --
6431: -- cursor to fetch the captured reading of the period end date if exists.
6432: CURSOR EST_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6433: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
6434: from CSI_COUNTER_READINGS b
6435: where b.counter_id = b_counter_id
6436: and nvl(b.disabled_flag,'N') = 'N'
6437: and trunc(b.value_timestamp) = trunc(b_rdg_date)
6438: order by b.value_timestamp desc;

Line 6446: FROM CSI_COUNTER_READINGS

6442:
6443: -- cursor to check whether any counter readings exist
6444: CURSOR CTR_RDGS_EXIST(b_counter_id NUMBER) IS
6445: select counter_value_id
6446: FROM CSI_COUNTER_READINGS
6447: WHERE counter_id = b_counter_id
6448: and nvl(disabled_flag,'N') = 'N';
6449: --
6450: l_ctr_val_id NUMBER;

Line 6787: from CSI_COUNTER_READINGS b

6783: l_msg_count NUMBER;
6784: --
6785: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6786: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
6787: from CSI_COUNTER_READINGS b
6788: where b.counter_id = b_counter_id
6789: and b.value_timestamp < b_rdg_date
6790: and nvl(b.disabled_flag,'N') = 'N'
6791: order by b.value_timestamp desc;

Line 6795: from CSI_COUNTER_READINGS b

6791: order by b.value_timestamp desc;
6792: --
6793: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6794: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
6795: from CSI_COUNTER_READINGS b
6796: where b.counter_id = b_counter_id
6797: and nvl(b.disabled_flag,'N') = 'N'
6798: and trunc(b.value_timestamp) = trunc(b_rdg_date)
6799: order by b.value_timestamp desc;

Line 6939: END CSI_COUNTER_READINGS_PVT;

6935: p_data => x_msg_data
6936: );
6937: END ESTIMATE_FIXED_VALUES;
6938: --
6939: END CSI_COUNTER_READINGS_PVT;