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