[Home] [Help]
1: PACKAGE BODY CSI_COUNTER_READINGS_PVT as
2: /* $Header: csivcrdb.pls 120.60.12020000.3 2012/10/11 10:39:16 mvaradam ship $ */
3: --
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_READINGS_PVT';
5: G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivcrdb.pls';
1: PACKAGE BODY CSI_COUNTER_READINGS_PVT as
2: /* $Header: csivcrdb.pls 120.60.12020000.3 2012/10/11 10:39:16 mvaradam ship $ */
3: --
4: G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_COUNTER_READINGS_PVT';
5: G_FILE_NAME CONSTANT VARCHAR2(12) := 'csivcrdb.pls';
6: CMRO_CALL VARCHAR(2);
7: --
8: FUNCTION get_reading_before_reset(p_counter_id NUMBER,
10: RETURN NUMBER IS
11:
12: CURSOR c1 IS
13: SELECT counter_reading
14: FROM csi_counter_readings
15: WHERE counter_id = p_counter_id
16: AND value_timestamp < p_value_timestamp
17: AND nvl(disabled_flag,'N') = 'N'
18: ORDER BY value_timestamp desc;
31: RETURN NUMBER IS
32:
33: CURSOR c1 IS
34: SELECT net_reading
35: FROM csi_counter_readings
36: WHERE counter_id = p_counter_id
37: AND value_timestamp < p_value_timestamp
38: AND nvl(disabled_flag,'N') = 'N'
39: ORDER BY value_timestamp desc;
56: SELECT ctr_val_max_seq_no
57: INTO l_counter_value_id
58: FROM csi_counters_b
59: WHERE counter_id = p_counter_id
60: AND EXISTS(SELECT 'Y' FROM csi_counter_readings WHERE counter_value_id=ctr_val_max_seq_no);
61: EXCEPTION
62: WHEN NO_DATA_FOUND THEN
63: l_counter_value_id := NULL;
64: END;
93: BEGIN
94: Begin
95: select 'x'
96: into l_exists
97: from CSI_COUNTER_READINGS
98: where counter_value_id = p_ctr_value_id;
99: l_return_value := TRUE;
100: Exception
101: when no_data_found then
240: BEGIN
241: Begin
242: select 'x'
243: into l_exists
244: from CSI_COUNTER_READINGS ccr,
245: CSI_COUNTER_PROPERTIES_B ccp
246: where ccr.counter_value_id = p_ctr_value_id
247: and ccp.counter_id = ccr.counter_id
248: and ccp.counter_property_id = p_ctr_prop_id
432: -- and ccv.reading_type <> 2; -- Exclude Change counters as resets are not allowed
433: --
434: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
435: select counter_reading,net_reading,life_to_date_reading
436: from CSI_COUNTER_READINGS
437: where counter_id = p_counter_id
438: and nvl(disabled_flag,'N') = 'N'
439: and value_timestamp < p_value_timestamp
440: ORDER BY value_timestamp desc;
526: l_prev_net_max_reading,
527: l_prev_ltd_max_reading
528: --,l_prev_value_max_timestamp
529: --l_prev_max_comments
530: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
531:
532: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
533: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
534: END IF; -- 8214848 - dsingire
611: --
612: -- Generate the Value_id for insert
613: l_process_flag := TRUE;
614: WHILE l_process_flag LOOP
615: select CSI_COUNTER_READINGS_S.nextval
616: into l_ctr_rdg_rec.counter_value_id from dual;
617: IF NOT Counter_Value_Exists(l_ctr_rdg_rec.counter_value_id) THEN
618: l_process_flag := FALSE;
619: END IF;
699:
700: l_ctr_rdg_rec.counter_reading := l_counter_reading;
701:
702: --
703: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
704: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
705: csi_ctr_gen_utility_pvt.put_line('Resetting target Counter '||to_char(l_ctr_rdg_rec.counter_id));
706: END IF; -- 8214848 - dsingire
707: --
704: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
705: csi_ctr_gen_utility_pvt.put_line('Resetting target Counter '||to_char(l_ctr_rdg_rec.counter_id));
706: END IF; -- 8214848 - dsingire
707: --
708: CSI_COUNTER_READINGS_PKG.Insert_Row(
709: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
710: ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
711: ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
712: ,p_COUNTER_READING => l_ctr_rdg_rec.counter_reading
807: l_user_id NUMBER := fnd_global.user_id; -- 8214848 - dsingire
808:
809: CURSOR derived_readings_cur IS
810: SELECT counter_value_id, counter_id
811: FROM csi_counter_readings
812: WHERE source_counter_value_id = p_src_counter_value_id
813: AND nvl(disabled_flag,'N') <> 'Y';
814: BEGIN
815: x_return_status := FND_API.G_RET_STS_SUCCESS;
813: AND nvl(disabled_flag,'N') <> 'Y';
814: BEGIN
815: x_return_status := FND_API.G_RET_STS_SUCCESS;
816: FOR derived_reading IN derived_readings_cur LOOP
817: UPDATE CSI_COUNTER_READINGS
818: SET disabled_flag = 'Y',
819: last_updated_by = l_user_id,
820: last_update_date = SYSDATE,
821: object_version_number = object_version_number + 1
914: l_process_flag := TRUE;
915: WHILE l_process_flag LOOP
916: select CSI_TRANSACTIONS_S.nextval
917: into p_txn_rec.transaction_id from dual;
918: IF NOT csi_counter_readings_pvt.Transaction_ID_Exists(p_txn_rec.transaction_id) THEN
919: l_process_flag := FALSE;
920: END IF;
921: END LOOP;
922: ELSE
919: l_process_flag := FALSE;
920: END IF;
921: END LOOP;
922: ELSE
923: IF csi_counter_readings_pvt.Transaction_ID_Exists(p_txn_rec.transaction_id) THEN
924: csi_ctr_gen_utility_pvt.ExitWithErrMsg
925: ( p_msg_name => 'CSI_TXN_ID_ALREADY_EXISTS'
926: ,p_token1_name => 'TRANSACTION_ID'
927: ,p_token1_val => to_char(p_txn_rec.transaction_id)
1139:
1140: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
1141: select counter_reading,net_reading,life_to_date_reading,
1142: value_timestamp
1143: from CSI_COUNTER_READINGS
1144: where counter_id = p_counter_id
1145: and nvl(disabled_flag,'N') = 'N'
1146: and value_timestamp < p_value_timestamp
1147: ORDER BY value_timestamp desc;
1148: --,counter_value_id desc;
1149: --
1150: CURSOR NEXT_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
1151: select counter_reading,value_timestamp,reset_mode
1152: from CSI_COUNTER_READINGS
1153: where counter_id = p_counter_id
1154: and nvl(disabled_flag,'N') = 'N'
1155: and value_timestamp > p_value_timestamp
1156: ORDER BY value_timestamp asc;
1157: --,counter_value_id asc;
1158:
1159: CURSOR WO_COUNTER_VALUE_CUR(p_counter_id IN NUMBER, p_value_timestamp IN DATE) IS
1160: SELECT COUNTER_VALUE_ID, OBJECT_VERSION_NUMBER
1161: FROM CSI_COUNTER_READINGS
1162: WHERE COUNTER_ID = p_counter_id
1163: AND VALUE_TIMESTAMP = p_value_timestamp;
1164:
1165: --
1165: --
1166: CURSOR LATER_READINGS_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
1167: select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
1168: ,reset_mode,adjustment_type,include_target_resets, comments
1169: from CSI_COUNTER_READINGS
1170: where counter_id = p_counter_id
1171: and nvl(disabled_flag,'N') = 'N'
1172: and value_timestamp > p_value_timestamp
1173: ORDER BY value_timestamp asc, counter_value_id asc;
1301: IF p_txn_rec.transaction_type_id in (91,92,94,95) THEN
1302: l_rec_count := 0;
1303: select count(*)
1304: into l_rec_count
1305: from CSI_COUNTER_READINGS
1306: where counter_id = p_ctr_rdg_rec.counter_id
1307: and ROWNUM = 1;
1308: --
1309: IF l_rec_count > 0 THEN
1350: Begin
1351: -- Bug 8214848
1352: select counter_id
1353: into l_counter_id
1354: from CSI_COUNTER_READINGS
1355: where counter_id = p_ctr_rdg_rec.counter_id
1356: and value_timestamp = p_ctr_rdg_rec.value_timestamp
1357: and disabled_flag = 'N'; -- BUG 12536849
1358:
1362: select 'x'
1363: into l_exists
1364: from dual
1365: where exists (select 'x'
1366: from CSI_COUNTER_READINGS
1367: where counter_id = p_ctr_rdg_rec.counter_id
1368: and value_timestamp = p_ctr_rdg_rec.value_timestamp
1369: and counter_reading = p_ctr_rdg_rec.counter_reading);
1370:
1681: l_prev_net_max_reading,
1682: l_prev_ltd_max_reading,
1683: l_prev_value_max_timestamp
1684: --l_prev_max_comments
1685: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
1686:
1687: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
1688: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
1689: END IF; -- 8214848 - dsingire
2112: --
2113: p_ctr_rdg_rec.object_version_number := 1;
2114: --
2115: -- If counter_reading gets a user entered value or thru' adjustments (Previous value)
2116: -- Insert into CSI_COUNTER_READINGS
2117: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
2118: csi_ctr_gen_utility_pvt.put_line( 'l_reading_type ' || l_reading_type);
2119: csi_ctr_gen_utility_pvt.put_line( 'p_ctr_rdg_rec.counter_reading ' || p_ctr_rdg_rec.counter_reading);
2120: csi_ctr_gen_utility_pvt.put_line( 'p_ctr_rdg_rec.automatic_rollover_flag ' || p_ctr_rdg_rec.automatic_rollover_flag);
2205: -- Need to pass back the Net and LTD Readings
2206: p_ctr_rdg_rec.net_reading := l_net_reading;
2207: p_ctr_rdg_rec.life_to_date_reading := l_ltd_reading;
2208: --
2209: -- Call Table Handler to insert into CSI_COUNTER_READINGS
2210: -- Check and Generate Counter_value_id
2211: IF p_ctr_rdg_rec.counter_value_id IS NULL OR
2212: p_ctr_rdg_rec.counter_value_id = FND_API.G_MISS_NUM THEN
2213: WHILE l_process_flag LOOP
2210: -- Check and Generate Counter_value_id
2211: IF p_ctr_rdg_rec.counter_value_id IS NULL OR
2212: p_ctr_rdg_rec.counter_value_id = FND_API.G_MISS_NUM THEN
2213: WHILE l_process_flag LOOP
2214: select CSI_COUNTER_READINGS_S.nextval
2215: into p_ctr_rdg_rec.counter_value_id from dual;
2216: IF NOT Counter_Value_Exists(p_ctr_rdg_rec.counter_value_id) THEN
2217: l_process_flag := FALSE;
2218: END IF;
2232: END IF;
2233: --
2234:
2235: --
2236: CSI_COUNTER_READINGS_PKG.Insert_Row(
2237: px_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
2238: ,p_COUNTER_ID => p_ctr_rdg_rec.counter_id
2239: ,p_VALUE_TIMESTAMP => p_ctr_rdg_rec.value_timestamp
2240: ,p_COUNTER_READING => p_ctr_rdg_rec.counter_reading
2469: -- added IF for Bug 9148094
2470:
2471: csi_ctr_gen_utility_pvt.put_line(' Update Net Reading Flag : '||l_update_net_flag);
2472: IF l_update_net_flag = 'Y' THEN
2473: UPDATE CSI_COUNTER_READINGS
2474: set net_reading = later_rdg.counter_reading, -- l_previous_net
2475: life_to_date_reading = l_previous_ltd,
2476: last_update_date = sysdate,
2477: last_updated_by = l_user_id
2477: last_updated_by = l_user_id
2478: where counter_value_id = later_rdg.counter_value_id;
2479: l_previous_net := later_rdg.counter_reading;
2480: ELSE
2481: UPDATE CSI_COUNTER_READINGS
2482: set net_reading = l_previous_net,
2483: life_to_date_reading = l_previous_ltd,
2484: last_update_date = sysdate,
2485: last_updated_by = l_user_id
2503:
2504: --Bug 12632134 : Reverted the code changes done for bug 9555599
2505: --As l_update_net_flag is only for reset cases and this code is in else part of reset case.
2506:
2507: UPDATE CSI_COUNTER_READINGS
2508: set net_reading = l_net_reading,
2509: life_to_date_reading = l_ltd_reading,
2510: last_update_date = sysdate,
2511: last_updated_by = l_user_id
2515: l_previous_ltd := l_ltd_reading;
2516:
2517: /* commented for bug 12632134
2518: IF l_update_net_flag = 'Y' THEN
2519: UPDATE CSI_COUNTER_READINGS
2520: set net_reading = later_rdg.counter_reading, -- l_previous_net
2521: life_to_date_reading = l_previous_ltd,
2522: last_update_date = sysdate,
2523: last_updated_by = l_user_id
2523: last_updated_by = l_user_id
2524: where counter_value_id = later_rdg.counter_value_id;
2525: l_previous_net := later_rdg.counter_reading;
2526: ELSE
2527: UPDATE CSI_COUNTER_READINGS
2528: set net_reading = l_previous_net,
2529: life_to_date_reading = l_previous_ltd,
2530: last_update_date = sysdate,
2531: last_updated_by = l_user_id
2680: --
2681:
2682: IF NVL(p_ctr_rdg_rec.reset_mode,FND_API.G_MISS_CHAR) = 'SOFT' THEN
2683: -- Introduce a delay. Basically, value_timestamp of reset should be slightly > than the curr Reading
2684: -- For Reset Mode insert a separate record in CSI_COUNTER_READINGS with the Reset counter reading.
2685: -- Net and LTD readings are from the previous counter reading.
2686: -- If only Reset is performed then no need to introduce the delay.
2687: --
2688: l_reset_rdg_rec := l_temp_ctr_rdg_rec;
2734: --
2735: -- Generate the Value_id
2736: l_process_flag := TRUE;
2737: WHILE l_process_flag LOOP
2738: select CSI_COUNTER_READINGS_S.nextval
2739: into l_reset_rdg_rec.counter_value_id from dual;
2740: IF NOT Counter_Value_Exists(l_reset_rdg_rec.counter_value_id) THEN
2741: l_process_flag := FALSE;
2742: END IF;
2746: --
2747: IF NVL(p_ctr_rdg_rec.counter_value_id,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM THEN
2748: p_ctr_rdg_rec.counter_value_id := l_reset_rdg_rec.counter_value_id;
2749: END IF;
2750: -- Call the Table Handler to insert the Reset Reading into CSI_COUNTER_READINGS
2751: --
2752: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
2753: csi_ctr_gen_utility_pvt.put_line('Inserting Reset Record with Ctr Value ID '||to_char(l_reset_rdg_rec.counter_value_id));
2754: END IF; -- 8214848 - dsingire
2753: csi_ctr_gen_utility_pvt.put_line('Inserting Reset Record with Ctr Value ID '||to_char(l_reset_rdg_rec.counter_value_id));
2754: END IF; -- 8214848 - dsingire
2755:
2756: --
2757: CSI_COUNTER_READINGS_PKG.Insert_Row(
2758: px_COUNTER_VALUE_ID => l_reset_rdg_rec.counter_value_id
2759: ,p_COUNTER_ID => l_reset_rdg_rec.counter_id
2760: ,p_VALUE_TIMESTAMP => l_reset_rdg_rec.value_timestamp
2761: ,p_COUNTER_READING => l_reset_rdg_rec.counter_reading
3006: and nvl(active_end_date,(sysdate+1)) > sysdate;
3007: --
3008: CURSOR GET_NET_RDG_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
3009: select net_reading
3010: from CSI_COUNTER_READINGS
3011: where counter_id = p_counter_id
3012: and nvl(disabled_flag,'N') = 'N'
3013: and value_timestamp <= p_value_timestamp
3014: ORDER BY value_timestamp desc;
3015: --,counter_value_id desc;
3016: --
3017: CURSOR LATER_FORMULA_CUR(p_obj_ctr_id IN NUMBER,p_value_timestamp IN DATE) IS
3018: select counter_value_id,value_timestamp
3019: from CSI_COUNTER_READINGS
3020: where counter_id = p_obj_ctr_id
3021: and value_timestamp > p_value_timestamp
3022: and nvl(disabled_flag,'N') = 'N'
3023: ORDER BY value_timestamp desc, counter_value_id desc;
3126: Begin
3127: select '1'
3128: into l_exists from dual
3129: where exists (select 'x'
3130: from CSI_COUNTER_READINGS
3131: where counter_id = sub_cur.source_counter_id
3132: and value_timestamp = p_ctr_rdg_rec.value_timestamp
3133: and nvl(disabled_flag,'N') <> 'Y');
3134: l_other_src_captured := 'T';
3155: FROM CSI_COUNTERS_B WHERE COUNTER_ID = sub_cur.source_counter_id;
3156: SELECT NET_READING, VALUE_TIMESTAMP
3157: INTO l_net_max_reading,
3158: l_value_max_timestamp
3159: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
3160: IF (l_debug_level > 0) THEN
3161: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
3162: END IF;
3163: EXCEPTION
3197: IF NVL(p_ctr_rdg_rec.disabled_flag,'N') <> 'Y' THEN
3198: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
3199: csi_ctr_gen_utility_pvt.put_line('Trying to Update Object Ctr : '||to_char(obj_cur.object_counter_id));
3200: END IF; -- 8214848 - dsingire
3201: Update CSI_COUNTER_READINGS
3202: set counter_reading = l_counter_reading,
3203: net_reading = l_counter_reading,
3204: life_to_date_reading = l_counter_reading,
3205: disabled_flag = 'N',
3211: IF SQL%ROWCOUNT = 0 THEN -- If update was not successfull
3212: -- Generate the Value_id for insert
3213: l_process_flag := TRUE;
3214: WHILE l_process_flag LOOP
3215: select CSI_COUNTER_READINGS_S.nextval
3216: into l_ctr_value_id from dual;
3217: IF NOT Counter_Value_Exists(l_ctr_value_id) THEN
3218: l_process_flag := FALSE;
3219: END IF;
3222: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
3223: csi_ctr_gen_utility_pvt.put_line('Unable to Update. Inserting Object Ctr : '||to_char(obj_cur.object_counter_id));
3224: END IF; -- 8214848 - dsingire
3225: -- Call the Table Handler to insert the new reading
3226: CSI_COUNTER_READINGS_PKG.Insert_Row(
3227: px_COUNTER_VALUE_ID => l_ctr_value_id
3228: ,p_COUNTER_ID => obj_cur.object_counter_id
3229: ,p_VALUE_TIMESTAMP => p_ctr_rdg_rec.value_timestamp
3230: ,p_COUNTER_READING => l_counter_reading
3345: ELSE -- If source counter is disabled then the corresponding formula counter reading is updated
3346: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
3347: csi_ctr_gen_utility_pvt.put_line('Disabling Obj Ctr '||to_char(obj_cur.object_counter_id));
3348: END IF; -- 8214848 - dsingire
3349: Update CSI_COUNTER_READINGS
3350: set counter_reading = l_counter_reading,
3351: net_reading = l_counter_reading,
3352: life_to_date_reading = l_counter_reading,
3353: disabled_flag = decode(l_disabled_flag,'Y',decode(l_other_src_captured,'T','N','Y'),'N'),
3424: FROM CSI_COUNTERS_B WHERE COUNTER_ID = l_src_ctr_tbl(src_rec).source_counter_id;
3425: SELECT NET_READING, VALUE_TIMESTAMP
3426: INTO l_net_max_reading,
3427: l_value_max_timestamp
3428: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
3429: IF (l_debug_level > 0) THEN
3430: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
3431: END IF;
3432: EXCEPTION
3458: -- Updating Formula Counter
3459: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
3460: csi_ctr_gen_utility_pvt.put_line('Re-computing later formula Ctr Value ID '||to_char(later_rec.counter_value_id));
3461: END IF; -- 8214848 - dsingire
3462: Update CSI_COUNTER_READINGS
3463: set counter_reading = l_counter_reading,
3464: net_reading = l_counter_reading,
3465: life_to_date_reading = l_counter_reading,
3466: last_update_date = sysdate,
3536: -- Start fix for bug 6852415, part 1 of 3
3537: CURSOR base_ctrs IS
3538: SELECT counter_id AS base_ctr_id,
3539: value_timestamp AS base_ctr_timestamp
3540: FROM csi_counter_readings
3541: WHERE counter_value_id = p_ctr_rdg_rec.counter_value_id;
3542: CURSOR ctrs_to_be_calc(base_ctr_id NUMBER, base_ctr_timestamp DATE) IS
3543: SELECT counter_id,
3544: derive_function,
3555: CURSOR CTRS_TO_BE_CALC IS
3556: SELECT ctr.counter_id, ctr.derive_function,
3557: ctr.derive_counter_id, ctr.derive_property_id,ctr.filter_reading_count,ctr.filter_type,
3558: ctr.filter_time_uom, cv.value_timestamp as new_rdg_dt, cv.counter_id as base_ctr_id
3559: FROM CSI_COUNTERS_B ctr, CSI_COUNTER_READINGS cv
3560: WHERE cv.counter_value_id = p_ctr_rdg_rec.counter_value_id
3561: AND ctr.derive_counter_id = cv.counter_id
3562: AND NVL(ctr.start_date_active,cv.value_timestamp) <= cv.value_timestamp
3563: AND NVL(ctr.end_date_active,(cv.value_timestamp+1)) > cv.value_timestamp;
3648: l_previous_net number;
3649: --
3650: CURSOR CTR_RDGS(d1_counter_id number) is
3651: SELECT net_reading net_rdg,value_timestamp
3652: FROM CSI_COUNTER_READINGS
3653: WHERE counter_id = d1_counter_id
3654: AND value_timestamp <= p_ctr_rdg_rec.value_timestamp
3655: AND NVL(disabled_flag,'N') = 'N'
3656: order by value_timestamp desc;
3705: where uom_code=p_filter_time_uom;
3706: --
3707: CURSOR GET_FIRST_READING(d3_counter_id IN NUMBER) IS
3708: SELECT net_reading,value_timestamp
3709: FROM CSI_COUNTER_READINGS
3710: WHERE counter_id = d3_counter_id
3711: AND NVL(disabled_flag,'N') = 'N'
3712: order by value_timestamp asc;
3713: --
3712: order by value_timestamp asc;
3713: --
3714: CURSOR CTR_CUR_RDG IS
3715: SELECT net_reading
3716: FROM CSI_COUNTER_READINGS
3717: WHERE counter_value_id = p_ctr_rdg_rec.counter_value_id;
3718: --
3719: l_time_uom VARCHAR2(10);
3720: l_min_date DATE;
3771: ELSE
3772: i := 1;
3773: lj := 1;
3774: l_sqlstr_init := 'select '||ctrs.derive_function||'( nvl(net_reading,0) )';
3775: l_sqlstr_init := l_sqlstr_init || ' from CSI_COUNTER_READINGS cv ';
3776: l_sqlstr := ' where counter_value_id in (';
3777: l_sqlstr := l_sqlstr || ' select distinct cv1.counter_value_id from ';
3778: l_sqlfrom := ' CSI_COUNTER_READINGS cv1';
3779: l_sqlwhere := '';
3774: l_sqlstr_init := 'select '||ctrs.derive_function||'( nvl(net_reading,0) )';
3775: l_sqlstr_init := l_sqlstr_init || ' from CSI_COUNTER_READINGS cv ';
3776: l_sqlstr := ' where counter_value_id in (';
3777: l_sqlstr := l_sqlstr || ' select distinct cv1.counter_value_id from ';
3778: l_sqlfrom := ' CSI_COUNTER_READINGS cv1';
3779: l_sqlwhere := '';
3780: --
3781: FOR filts IN der_filters(ctrs.counter_id) LOOP
3782: l_sqlfrom := l_sqlfrom ||', CSI_CTR_PROPERTY_READINGS pv';
3874: l_ctr_in.source_code := p_ctr_rdg_rec.source_code;
3875: l_ctr_in.source_line_id := p_ctr_rdg_rec.source_line_id;
3876: --
3877: IF p_mode = 'UPDATE' THEN
3878: UPDATE CSI_COUNTER_READINGS
3879: set counter_reading = l_ctr_value,
3880: net_reading = l_ctr_value,
3881: life_to_date_reading = l_ctr_value,
3882: last_update_date = sysdate,
3887: ELSIF p_mode = 'CREATE' THEN
3888: -- Generate the Value_id for insert
3889: l_process_flag := TRUE;
3890: WHILE l_process_flag LOOP
3891: select CSI_COUNTER_READINGS_S.nextval
3892: into l_ctr_value_id from dual;
3893: IF NOT Counter_Value_Exists(l_ctr_value_id) THEN
3894: l_process_flag := FALSE;
3895: END IF;
3895: END IF;
3896: END LOOP;
3897: --
3898: -- Call the Table Handler to insert the new reading
3899: CSI_COUNTER_READINGS_PKG.Insert_Row(
3900: px_COUNTER_VALUE_ID => l_ctr_value_id
3901: ,p_COUNTER_ID => l_ctr_in.counter_id
3902: ,p_VALUE_TIMESTAMP => l_ctr_in.value_timestamp
3903: ,p_COUNTER_READING => l_ctr_value
4079: SELECT crg.counter_value_id,crg.value_timestamp,crg.counter_id object_counter_id,
4080: crg.adjustment_reading,crg.counter_reading,nvl(ccr.factor,1) factor,
4081: ccv.direction,ccv.reading_type,crg.reset_mode,
4082: ccv.uom_code object_uom_code, cct.name object_counter_name
4083: from CSI_COUNTER_READINGS crg,
4084: CSI_COUNTER_RELATIONSHIPS ccr,
4085: CSI_COUNTERS_B ccv,
4086: CSI_COUNTERS_TL cct
4087: where crg.source_counter_value_id = p_ctr_rdg_rec.counter_value_id
4093: and cct.language = USERENV('LANG');
4094: --
4095: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
4096: select counter_reading,net_reading,life_to_date_reading,value_timestamp
4097: from CSI_COUNTER_READINGS
4098: where counter_id = p_counter_id
4099: and nvl(disabled_flag,'N') = 'N'
4100: and value_timestamp < p_value_timestamp
4101: ORDER BY value_timestamp desc;
4274: l_prev_net_max_reading,
4275: l_prev_ltd_max_reading,
4276: l_prev_value_max_timestamp
4277: --l_prev_max_comments
4278: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
4279:
4280: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
4281: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
4282: END IF; -- 8214848 - dsingire
4435: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
4436: csi_ctr_gen_utility_pvt.put_line('Updating Target Counter Value ID : '||to_char(obj_cur.counter_value_id));
4437: END IF; -- 8214848 - dsingire
4438:
4439: Update CSI_COUNTER_READINGS
4440: set counter_reading = l_counter_reading,
4441: net_reading = l_net_reading,
4442: life_to_date_reading = l_ltd_reading,
4443: last_update_date = sysdate,
4558: l_prev_net_max_reading,
4559: l_prev_ltd_max_reading,
4560: l_prev_value_max_timestamp
4561: --l_prev_max_comments
4562: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
4563:
4564: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
4565: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
4566: END IF; -- 8214848 - dsingire
4864: --
4865: -- Generate the Value_id for insert
4866: l_process_flag := TRUE;
4867: WHILE l_process_flag LOOP
4868: select CSI_COUNTER_READINGS_S.nextval
4869: into l_ctr_rdg_rec.counter_value_id from dual;
4870: IF NOT Counter_Value_Exists(l_ctr_rdg_rec.counter_value_id) THEN
4871: l_process_flag := FALSE;
4872: END IF;
4874: --
4875: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
4876: csi_ctr_gen_utility_pvt.put_line('Inserting Target Counter ID : '||to_char(l_ctr_rdg_rec.counter_id));
4877: END IF; -- 8214848 - dsingire
4878: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
4879: CSI_COUNTER_READINGS_PKG.Insert_Row(
4880: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
4881: ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
4882: ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
4875: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
4876: csi_ctr_gen_utility_pvt.put_line('Inserting Target Counter ID : '||to_char(l_ctr_rdg_rec.counter_id));
4877: END IF; -- 8214848 - dsingire
4878: -- Call the Table Handler to insert into CSI_COUNTER_READINGS
4879: CSI_COUNTER_READINGS_PKG.Insert_Row(
4880: px_COUNTER_VALUE_ID => l_ctr_rdg_rec.counter_value_id
4881: ,p_COUNTER_ID => l_ctr_rdg_rec.counter_id
4882: ,p_VALUE_TIMESTAMP => l_ctr_rdg_rec.value_timestamp
4883: ,p_COUNTER_READING => l_counter_reading
5087: l_update_net_flag VARCHAR2(1) := NVL(fnd_profile.value('CSI_UPDATE_NET_READINGS_ON_RESET'), 'Y');
5088: --
5089: CURSOR CURRENT_READING_CUR(p_counter_value_id IN NUMBER) IS
5090: select * from
5091: CSI_COUNTER_READINGS
5092: where counter_value_id = p_counter_value_id;
5093: --
5094: l_curr_ctr_rdg_rec CURRENT_READING_CUR%rowtype;
5095: --
5094: l_curr_ctr_rdg_rec CURRENT_READING_CUR%rowtype;
5095: --
5096: CURSOR PREV_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
5097: select counter_reading,net_reading,life_to_date_reading,value_timestamp
5098: from CSI_COUNTER_READINGS
5099: where counter_id = p_counter_id
5100: and nvl(disabled_flag,'N') = 'N'
5101: and value_timestamp < p_value_timestamp
5102: ORDER BY value_timestamp desc;
5104: --
5105: CURSOR NEXT_READING_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
5106: select counter_reading,value_timestamp,reset_mode,
5107: adjustment_type,automatic_rollover_flag
5108: from CSI_COUNTER_READINGS
5109: where counter_id = p_counter_id
5110: and nvl(disabled_flag,'N') = 'N'
5111: and value_timestamp > p_value_timestamp
5112: ORDER BY value_timestamp asc;
5114: --
5115: CURSOR LATER_READINGS_CUR(p_counter_id IN NUMBER,p_value_timestamp IN DATE) IS
5116: select counter_value_id,counter_reading,net_reading,value_timestamp,adjustment_reading
5117: ,reset_mode,adjustment_type,include_target_resets
5118: from CSI_COUNTER_READINGS
5119: where counter_id = p_counter_id
5120: and nvl(disabled_flag,'N') = 'N'
5121: and value_timestamp > p_value_timestamp
5122: ORDER BY value_timestamp asc, counter_value_id asc;
5408: l_prev_net_max_reading,
5409: l_prev_ltd_max_reading,
5410: l_prev_value_max_timestamp
5411: --l_prev_max_comments
5412: FROM CSI_COUNTER_READINGS WHERE COUNTER_VALUE_ID = NVL(l_ctr_val_max,-1);
5413:
5414: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
5415: csi_ctr_gen_utility_pvt.put_line('l_ctr_val_max - ' || l_ctr_val_max );
5416: END IF; -- 8214848 - dsingire
5487: -- Below two assignments are required by Update public API to call OKC Assembler
5488: p_ctr_rdg_rec.counter_id := l_curr_ctr_rdg_rec.counter_id;
5489: p_ctr_rdg_rec.value_timestamp := l_curr_ctr_rdg_rec.value_timestamp;
5490: --
5491: -- Call Table Handler to Update CSI_COUNTER_READINGS
5492: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
5493: csi_ctr_gen_utility_pvt.put_line('Calling Update Row to Disable the Reading...');
5494: END IF; -- 8214848 - dsingire
5495: CSI_COUNTER_READINGS_PKG.Update_Row(
5491: -- Call Table Handler to Update CSI_COUNTER_READINGS
5492: IF (l_debug_level > 0) THEN -- 8214848 - dsingire
5493: csi_ctr_gen_utility_pvt.put_line('Calling Update Row to Disable the Reading...');
5494: END IF; -- 8214848 - dsingire
5495: CSI_COUNTER_READINGS_PKG.Update_Row(
5496: p_COUNTER_VALUE_ID => p_ctr_rdg_rec.counter_value_id
5497: ,p_COUNTER_ID => NULL
5498: ,p_VALUE_TIMESTAMP => NULL
5499: ,p_COUNTER_READING => NULL
5658: IF NVL(later_rdg.reset_mode,'X') = 'SOFT' THEN
5659: -- added IF for Bug 9148094
5660: csi_ctr_gen_utility_pvt.put_line(' Update Net Reading Flag : '||l_update_net_flag);
5661: IF l_update_net_flag = 'Y' THEN
5662: UPDATE CSI_COUNTER_READINGS
5663: set net_reading = later_rdg.counter_reading,
5664: life_to_date_reading = l_previous_ltd,
5665: last_update_date = sysdate,
5666: last_updated_by = l_user_id
5666: last_updated_by = l_user_id
5667: where counter_value_id = later_rdg.counter_value_id;
5668: l_previous_net := later_rdg.counter_reading;
5669: ELSE
5670: UPDATE CSI_COUNTER_READINGS
5671: set net_reading = l_previous_net,
5672: life_to_date_reading = l_previous_ltd,
5673: last_update_date = sysdate,
5674: last_updated_by = l_user_id
5686: ,px_net_rdg => l_net_reading
5687: ,px_ltd_rdg => l_ltd_reading
5688: ,l_ctr_rdg_rec => p_ctr_rdg_rec -- added 6398254
5689: );
5690: UPDATE CSI_COUNTER_READINGS
5691: set counter_reading = l_later_ctr_reading,
5692: net_reading = l_net_reading,
5693: life_to_date_reading = l_ltd_reading,
5694: last_update_date = sysdate,
6336: )
6337: IS
6338: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6339: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
6340: from CSI_COUNTER_READINGS b
6341: where b.counter_id = b_counter_id
6342: and b.value_timestamp < b_rdg_date
6343: and nvl(b.disabled_flag,'N') = 'N'
6344: order by b.value_timestamp desc;
6347: l_prev_value_timestamp DATE;
6348: --
6349: CURSOR POST_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6350: select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
6351: from CSI_COUNTER_READINGS b
6352: where b.counter_id = b_counter_id
6353: and b.value_timestamp > b_rdg_date
6354: and nvl(b.disabled_flag,'N') = 'N'
6355: order by b.value_timestamp asc;
6358: l_post_value_timestamp DATE;
6359: --
6360: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6361: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
6362: from CSI_COUNTER_READINGS b
6363: where b.counter_id = b_counter_id
6364: and nvl(b.disabled_flag,'N') = 'N'
6365: and trunc(b.value_timestamp) = trunc(b_rdg_date)
6366: order by b.value_timestamp desc;
6369: l_passed_rdg_date DATE;
6370: --
6371: CURSOR FIRST_RDG_VALUES(b_counter_id NUMBER) IS
6372: select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
6373: from CSI_COUNTER_READINGS b
6374: where b.counter_id = b_counter_id
6375: and nvl(b.disabled_flag,'N') = 'N'
6376: order by b.value_timestamp asc;
6377: --
6544: l_msg_count NUMBER;
6545: --
6546: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6547: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
6548: from CSI_COUNTER_READINGS b
6549: where b.counter_id = b_counter_id
6550: and b.value_timestamp < b_rdg_date
6551: and nvl(b.disabled_flag,'N') = 'N'
6552: order by b.value_timestamp desc;
6555: l_prev_value_timestamp DATE;
6556:
6557: CURSOR POST_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6558: select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
6559: from CSI_COUNTER_READINGS b
6560: where b.counter_id = b_counter_id
6561: and b.value_timestamp > b_rdg_date
6562: and nvl(b.disabled_flag,'N') = 'N'
6563: order by b.value_timestamp asc;
6566: l_post_value_timestamp DATE;
6567:
6568: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6569: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
6570: from CSI_COUNTER_READINGS b
6571: where b.counter_id = b_counter_id
6572: and nvl(b.disabled_flag,'N') = 'N'
6573: and trunc(b.value_timestamp) = trunc(b_rdg_date)
6574: order by b.value_timestamp desc;
6577: l_passed_rdg_date DATE;
6578:
6579: CURSOR first_rdg_values(b_counter_id NUMBER) IS
6580: select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
6581: from CSI_COUNTER_READINGS b
6582: where b.counter_id = b_counter_id
6583: and nvl(b.disabled_flag,'N') = 'N'
6584: order by b.value_timestamp asc;
6585:
6827: l_msg_count NUMBER;
6828: --
6829: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6830: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
6831: from CSI_COUNTER_READINGS b
6832: where b.counter_id = b_counter_id
6833: and b.value_timestamp < b_rdg_date
6834: and nvl(b.disabled_flag,'N') = 'N'
6835: order by b.value_timestamp desc;
6838: l_prev_value_timestamp DATE;
6839: --
6840: CURSOR POST_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6841: select b.net_reading "post_reading",b.value_timestamp "post_rdg_date"
6842: from CSI_COUNTER_READINGS b
6843: where b.counter_id = b_counter_id
6844: and b.value_timestamp > b_rdg_date
6845: and nvl(b.disabled_flag,'N') = 'N'
6846: order by b.value_timestamp asc;
6849: l_post_value_timestamp DATE;
6850:
6851: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
6852: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
6853: from CSI_COUNTER_READINGS b
6854: where b.counter_id = b_counter_id
6855: and nvl(b.disabled_flag,'N') = 'N'
6856: and trunc(b.value_timestamp) = trunc(b_rdg_date)
6857: order by b.value_timestamp desc;
6860: l_passed_rdg_date DATE;
6861:
6862: CURSOR first_rdg_values(b_counter_id NUMBER) IS
6863: select b.net_reading "first_reading",b.value_timestamp "first_rdg_date"
6864: from CSI_COUNTER_READINGS b
6865: where b.counter_id = b_counter_id
6866: and nvl(b.disabled_flag,'N') = 'N'
6867: order by b.value_timestamp asc;
6868: --
7054: j number := 0;
7055: l_min_seqno number;
7056: CURSOR CTR_RDGS(d1_counter_id number, b_prd_rdg_date DATE) IS
7057: select cval.net_reading AS NET_RDG, cval.value_timestamp
7058: from CSI_COUNTER_READINGS cval
7059: where cval.counter_id = d1_counter_id
7060: and cval.value_timestamp < b_prd_rdg_date
7061: and nvl(cval.disabled_flag,'N') = 'N'
7062: order by cval.value_timestamp desc;
7311: --
7312: -- cursor to fetch the captured reading of the period end date if exists.
7313: CURSOR EST_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
7314: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
7315: from CSI_COUNTER_READINGS b
7316: where b.counter_id = b_counter_id
7317: and nvl(b.disabled_flag,'N') = 'N'
7318: and trunc(b.value_timestamp) = trunc(b_rdg_date)
7319: order by b.value_timestamp desc;
7323:
7324: -- cursor to check whether any counter readings exist
7325: CURSOR CTR_RDGS_EXIST(b_counter_id NUMBER) IS
7326: select counter_value_id
7327: FROM CSI_COUNTER_READINGS
7328: WHERE counter_id = b_counter_id
7329: and nvl(disabled_flag,'N') = 'N';
7330: --
7331: l_ctr_val_id NUMBER;
7678: l_msg_count NUMBER;
7679: --
7680: CURSOR PRIOR_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
7681: select b.net_reading "prev_reading",b.value_timestamp "prev_rdg_date"
7682: from CSI_COUNTER_READINGS b
7683: where b.counter_id = b_counter_id
7684: and b.value_timestamp < b_rdg_date
7685: and nvl(b.disabled_flag,'N') = 'N'
7686: order by b.value_timestamp desc;
7686: order by b.value_timestamp desc;
7687: --
7688: CURSOR PASSED_DT_RDG_VALUES(b_counter_id NUMBER, b_rdg_date DATE) IS
7689: select b.net_reading "passed_dt_reading", b.value_timestamp "passed_rdg_date"
7690: from CSI_COUNTER_READINGS b
7691: where b.counter_id = b_counter_id
7692: and nvl(b.disabled_flag,'N') = 'N'
7693: and trunc(b.value_timestamp) = trunc(b_rdg_date)
7694: order by b.value_timestamp desc;
7839: p_data => x_msg_data
7840: );
7841: END ESTIMATE_FIXED_VALUES;
7842: --
7843: END CSI_COUNTER_READINGS_PVT;