1 PACKAGE BODY EAM_METERREADING_VALIDATE_PVT AS
2 /* $Header: EAMVMTVB.pls 120.9 2011/09/13 00:44:12 srikrama noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVMTVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_METERREADING_VALIDATE_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 14-FEB-2005 mmaduska Initial Creation
21 -- 12-Sep-2011 Srikanth R Bug 12536849: Ignore counter reading if disabled
22 ***************************************************************************/
23
24 PROCEDURE CHECK_REQUIRED
25 (
26 p_eam_meter_reading_rec IN EAM_PROCESS_WO_PUB.eam_meter_reading_rec_type
27 , x_return_status OUT NOCOPY VARCHAR2
28 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
29 )IS
30 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
31 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
32 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
33 l_status_type number;
34
35 BEGIN
36 x_return_status := FND_API.G_RET_STS_SUCCESS;
37
38 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
39 EAM_ERROR_MESSAGE_PVT.Write_Debug('Entered EAM_METERREADING_VALIDATE_PVT.CHECK_REQUIRED()..');
40 END IF;
41
42 IF p_eam_meter_reading_rec.meter_id IS NULL
43 THEN
44 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
45 EAM_ERROR_MESSAGE_PVT.WRITE_DEBUG('Error as p_eam_meter_reading_rec.meter_id is null in EAM_METERREADING_VALIDATE_PVT.CHECK_REQUIRED()');
46 END IF;
47 l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
48 l_token_tbl(1).token_value := p_eam_meter_reading_rec.WIP_ENTITY_ID;
49
50 l_out_mesg_token_tbl := l_mesg_token_tbl;
51 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
52 ( p_message_name => 'EAM_WCMP_METERID_REQ'
53 , p_token_tbl => l_Token_tbl
54 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
55 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
56 );
57 l_mesg_token_tbl := l_out_mesg_token_tbl;
58 x_mesg_token_tbl := l_mesg_token_tbl ;
59 x_return_status := FND_API.G_RET_STS_ERROR;
60 return;
61 END IF;
62
63 IF p_eam_meter_reading_rec.current_reading IS NULL
64 THEN
65 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
66 EAM_ERROR_MESSAGE_PVT.WRITE_DEBUG('Error as p_eam_meter_reading_rec.current_reading is null in EAM_METERREADING_VALIDATE_PVT.CHECK_REQUIRED()');
67 END IF;
68
69 l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
70 l_token_tbl(1).token_value := p_eam_meter_reading_rec.WIP_ENTITY_ID;
71
72 l_out_mesg_token_tbl := l_mesg_token_tbl;
73 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
74 ( p_message_name => 'EAM_WCMP_CURRENTREAD_REQ'
75 , p_token_tbl => l_Token_tbl
76 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
77 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
78 );
79 l_mesg_token_tbl := l_out_mesg_token_tbl;
80 x_mesg_token_tbl := l_mesg_token_tbl ;
81 x_return_status := FND_API.G_RET_STS_ERROR;
82 return;
83 END IF;
84
85 IF p_eam_meter_reading_rec.wo_end_date IS NULL
86 THEN
87 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
88 EAM_ERROR_MESSAGE_PVT.WRITE_DEBUG('Error as p_eam_meter_reading_rec.wo_end_date is null in EAM_METERREADING_VALIDATE_PVT.CHECK_REQUIRED()');
89 END IF;
90
91 l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
92 l_token_tbl(1).token_value := p_eam_meter_reading_rec.WIP_ENTITY_ID;
93
94 l_out_mesg_token_tbl := l_mesg_token_tbl;
95 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
96 ( p_message_name => 'EAM_WCMP_ENDDATE_REQ'
97 , p_token_tbl => l_Token_tbl
98 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
99 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
100 );
101 l_mesg_token_tbl := l_out_mesg_token_tbl;
102 x_mesg_token_tbl := l_mesg_token_tbl ;
103 x_return_status := FND_API.G_RET_STS_ERROR;
104 return;
105 END IF;
106
107 IF p_eam_meter_reading_rec.reset_flag IS NOT NULL AND p_eam_meter_reading_rec.value_before_reset IS NULL
108 THEN
109 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
110 EAM_ERROR_MESSAGE_PVT.WRITE_DEBUG('Last Condition failed in EAM_METERREADING_VALIDATE_PVT.CHECK_REQUIRED()');
111 END IF;
112
113 l_token_tbl(1).token_name := 'WIP_ENTITY_NAME';
114 l_token_tbl(1).token_value := p_eam_meter_reading_rec.WIP_ENTITY_ID;
115
116 l_out_mesg_token_tbl := l_mesg_token_tbl;
117 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
118 ( p_message_name => 'EAM_WCMP_VALBRESET_REQ'
119 , p_token_tbl => l_Token_tbl
120 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
121 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
122 );
123 l_mesg_token_tbl := l_out_mesg_token_tbl;
124 x_mesg_token_tbl := l_mesg_token_tbl ;
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 return;
127 END IF;
128
129 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
130 EAM_ERROR_MESSAGE_PVT.Write_Debug('Finished EAM_METERREADING_VALIDATE_PVT.CHECK_REQUIRED()..Successfully');
131 END IF;
132
133 END CHECK_REQUIRED;
134
135 PROCEDURE MANDATORY_ENTERED (
136 p_wip_entity_id IN NUMBER
137 , p_instance_id IN VARCHAR2
138 , p_eam_meter_reading_tbl IN EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type
139 , p_work_order_cmpl_date IN DATE
140 , x_return_status OUT NOCOPY VARCHAR2
141 , x_man_reading_enter OUT NOCOPY BOOLEAN
142 )IS
143
144 counter_id_tbl EAM_METERREADING_VALIDATE_PVT.counter_id_tbl_type;
145
146 mandatory_counter_id_tbl EAM_METERREADING_VALIDATE_PVT.counter_id_tbl_type;
147
148 is_meter_mandatory BOOLEAN :=FALSE;
149
150 loop_var NUMBER := 1;
151
152 flag BOOLEAN :=FALSE;
153 l_no_of_reading NUMBER;
154 L_SOURCE_METER_ID number;
155 L_SOURCE_METER_ID_P number;
156 BEGIN
157
158 x_man_reading_enter :=TRUE;
159 x_return_status := FND_API.G_RET_STS_SUCCESS;
160
161 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
162 EAM_ERROR_MESSAGE_PVT.Write_Debug('Entered EAM_METERREADING_VALIDATE_PVT.MANDATORY_ENTERED()..');
163 END IF;
164
165 /*
166 Before processing the meter readings we will query eam_asset_meters table to get list of
167 all meters associated with asset number. Then Eam_Meters_Util.is_meter_reading_mandatory
168 (wip_entity_id, meter_id) is called to identify the list of meters, which are mandatory for
169 work order completion. Then the input table of meters will be scanned to check if mandatory
170 meter readings are entered. If some reading is not entered then throw error
171 */
172
173
174 /* SELECT counter_id bulk collect into counter_id_tbl
175 FROM csi_counter_associations cca
176 WHERE source_object_id = p_instance_id
177 and SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1)
178 AND nvl(cca.end_date_active, SYSDATE+1); */
179
180 SELECT cca.counter_id bulk collect into counter_id_tbl -- Bug 7323234
181 FROM csi_counter_associations cca, csi_counters_b ccb
182 WHERE source_object_id = p_instance_id
183 AND SYSDATE BETWEEN nvl(cca.start_date_active, SYSDATE-1)
184 and nvl(cca.end_date_active, SYSDATE+1)
185 AND SYSDATE BETWEEN nvl(ccb.start_date_active, SYSDATE-1)
186 and nvl(ccb.end_date_active, SYSDATE+1)
187 AND cca.counter_id = ccb.counter_id;
188
189
190 IF counter_id_tbl.COUNT > 0 THEN
191
192 FOR I IN counter_id_tbl.FIRST..counter_id_tbl.LAST LOOP
193 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
194 EAM_ERROR_MESSAGE_PVT.Write_Debug('Id of Meters' || I || 'associated: ' || counter_id_tbl(I));
195 END IF;
196 is_meter_mandatory := Eam_Meters_Util.is_meter_reading_mandatory(
197 p_wip_entity_id => p_wip_entity_id ,
198 p_meter_id => counter_id_tbl(I)
199 );
200
201 IF is_meter_mandatory = TRUE THEN
202 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
203 EAM_ERROR_MESSAGE_PVT.Write_Debug('This meter is mandatory! : ' || counter_id_tbl(I));
204 END IF;
205 mandatory_counter_id_tbl(loop_var) := counter_id_tbl(I);
206 loop_var := loop_var + 1;
207 END IF ;
208 END LOOP;
209 END IF;
210
211 IF mandatory_counter_id_tbl.COUNT > 0 THEN
212 FOR J IN mandatory_counter_id_tbl.FIRST..mandatory_counter_id_tbl.LAST LOOP
213 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
214 EAM_ERROR_MESSAGE_PVT.Write_Debug('MANDATORY_COUNTER_ID_TBL(J)'||to_char(MANDATORY_COUNTER_ID_TBL(J)));
215 END IF;
216 flag :=FALSE;
217
218 select count(*) into l_no_of_reading
219 from CSI_COUNTER_READINGS
220 where counter_id = mandatory_counter_id_tbl(J)
221 and value_timestamp = p_work_order_cmpl_date
222 and DISABLED_FLAG = 'N'; -- BUG 12536849
223 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
224 EAM_ERROR_MESSAGE_PVT.Write_Debug('p_work_order_cmpl_date'||to_char(p_work_order_cmpl_date,'DD-MON-YYYY HH24:MI:SS'));
225 EAM_ERROR_MESSAGE_PVT.Write_Debug('L_NO_OF_READING'||to_char(L_NO_OF_READING) );
226 END IF;
227 IF L_NO_OF_READING = 0 THEN
228 /* WHEN COMING FROM WIRELESS,P_EAM_METER_READING_TBL HAS ALL THE SOURCE COUNTER ID'S
229 Added for Issue # 4 of Bug # 4932595*/
230 IF P_EAM_METER_READING_TBL.COUNT >0 THEN
231 -- GET THE SOURE METER OF THE MANDATORY METER
232 SELECT SOURCE_COUNTER_ID INTO L_SOURCE_METER_ID FROM CSI_COUNTER_RELATIONSHIPS WHERE OBJECT_COUNTER_ID = MANDATORY_COUNTER_ID_TBL(J)
233 AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE(+), SYSDATE-1) AND NVL(ACTIVE_END_DATE(+), SYSDATE+1);
234 -- LOOP THROUGH P_EAM_METER_READING_TBL TO CHECK WHETHER READINGS ARE ENTERED OR NOT
235 FOR K IN P_EAM_METER_READING_TBL.FIRST..P_EAM_METER_READING_TBL.LAST LOOP
236 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
237 EAM_ERROR_MESSAGE_PVT.Write_Debug('Checking meter ids'||to_char(P_EAM_METER_READING_TBL(K).METER_ID) );
238 EAM_ERROR_MESSAGE_PVT.Write_Debug('Current Reading'||to_char(P_EAM_METER_READING_TBL(K).CURRENT_READING ) );
239 End IF;
240 -- IF SOURCE METER ID IS NULL THEN COMPARE P_EAM_METER_READING_TBL(K)
241 IF NVL(L_SOURCE_METER_ID,P_EAM_METER_READING_TBL(K).METER_ID) <> MANDATORY_COUNTER_ID_TBL(J) THEN
242 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
243 EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside If 1 - meter id'||to_char(P_EAM_METER_READING_TBL(K).METER_ID) );
244 END IF;
245 -- IF P_EAM_METER_READING_TBL HAS AN NON MANDATORY READING
246 SELECT SOURCE_COUNTER_ID INTO L_SOURCE_METER_ID_P FROM CSI_COUNTER_RELATIONSHIPS WHERE OBJECT_COUNTER_ID = P_EAM_METER_READING_TBL(K).METER_ID
247 AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE(+), SYSDATE-1) AND NVL(ACTIVE_END_DATE(+), SYSDATE+1);
248 IF L_SOURCE_METER_ID_P IS NOT NULL THEN
249 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
250 EAM_ERROR_MESSAGE_PVT.Write_Debug('Inside If 2 - meter id'||to_char(P_EAM_METER_READING_TBL(K).METER_ID) );
251 END IF;
252 FLAG := TRUE;
253 END IF;
254 END IF;
255 END LOOP;
256 ELSE
257 X_MAN_READING_ENTER := FALSE;
258 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
259 EAM_ERROR_MESSAGE_PVT.Write_Debug('Error Con 1');
260 END IF;
261 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
262 RETURN;
263 END IF;
264
265 IF FLAG THEN
266 X_MAN_READING_ENTER := FALSE;
267 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
268 EAM_ERROR_MESSAGE_PVT.Write_Debug('Error Con 2');
269 END IF;
270 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
271 RETURN;
272 END IF;
273 END IF;
274
275 END LOOP;
276 END IF;
277 EXCEPTION
278 WHEN OTHERS THEN
279 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN
280 EAM_ERROR_MESSAGE_PVT.WRITE_DEBUG('Exception FROM EAM_METERREADING_VALIDATE_PVT.MANDATORY_ENTERED()');
281 END IF;
282 x_return_status := FND_API.G_RET_STS_ERROR;
283
284 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
285 EAM_ERROR_MESSAGE_PVT.Write_Debug('Finished EAM_METERREADING_VALIDATE_PVT.MANDATORY_ENTERED()..');
286 END IF;
287 END MANDATORY_ENTERED;
288
289 END EAM_METERREADING_VALIDATE_PVT;
[Home] [Help]