[Home] [Help]
PACKAGE BODY: APPS.EAM_OPERATIONS_PKG
Source
1 PACKAGE BODY EAM_OPERATIONS_PKG as
2 /* $Header: EAMOPTHB.pls 120.1.12020000.2 2012/10/22 10:02:37 vboddapa ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Wip_Entity_Id NUMBER,
6 X_Operation_Seq_Num NUMBER,
7 X_Organization_Id NUMBER,
8 X_Repetitive_Schedule_Id NUMBER,
9 X_Last_Update_Date DATE,
10 X_Last_Updated_By NUMBER,
11 X_Creation_Date DATE,
12 X_Created_By NUMBER,
13 X_Last_Update_Login NUMBER,
14 X_Operation_Sequence_Id NUMBER,
15 X_Standard_Operation_Id NUMBER,
16 X_Department_Id NUMBER,
17 X_Shutdown_Type VARCHAR2,
18 X_Operation_Completed VARCHAR2,
19 X_Description VARCHAR2,
20 X_Scheduled_Quantity NUMBER,
21 X_Quantity_In_Queue NUMBER,
22 X_Quantity_Running NUMBER,
23 X_Quantity_Waiting_To_Move NUMBER,
24 X_Quantity_Rejected NUMBER,
25 X_Quantity_Scrapped NUMBER,
26 X_Quantity_Completed NUMBER,
27 X_First_Unit_Start_Date DATE,
28 X_First_Unit_Completion_Date DATE,
29 X_Last_Unit_Start_Date DATE,
30 X_Last_Unit_Completion_Date DATE,
31 X_Previous_Operation_Seq_Num NUMBER,
32 X_Next_Operation_Seq_Num NUMBER,
33 X_Count_Point_Type NUMBER,
34 X_Backflush_Flag NUMBER,
35 X_Minimum_Transfer_Quantity NUMBER,
36 X_Date_Last_Moved DATE,
37 X_Attribute_Category VARCHAR2,
38 X_Attribute1 VARCHAR2,
39 X_Attribute2 VARCHAR2,
40 X_Attribute3 VARCHAR2,
41 X_Attribute4 VARCHAR2,
42 X_Attribute5 VARCHAR2,
43 X_Attribute6 VARCHAR2,
44 X_Attribute7 VARCHAR2,
45 X_Attribute8 VARCHAR2,
46 X_Attribute9 VARCHAR2,
47 X_Attribute10 VARCHAR2,
48 X_Attribute11 VARCHAR2,
49 X_Attribute12 VARCHAR2,
50 X_Attribute13 VARCHAR2,
51 X_Attribute14 VARCHAR2,
52 X_Attribute15 VARCHAR2,
53 X_X_Pos NUMBER,
54 X_Y_Pos NUMBER,
55 X_LONG_DESCRIPTION VARCHAR2,
56 X_L_EAM_OP_REC OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_rec_type,
57 X_WO_Start_Date OUT NOCOPY DATE,
58 X_WO_Completion_Date OUT NOCOPY DATE
59 ) IS
60 x_user_id number := FND_GLOBAL.USER_ID;
61 x_login_id number := FND_GLOBAL.LOGIN_ID;
62 x_request_id number := FND_GLOBAL.CONC_REQUEST_ID;
63 x_appl_id number := FND_GLOBAL.PROG_APPL_ID;
64 x_program_id number := FND_GLOBAL.CONC_PROGRAM_ID;
65 x_standard_op boolean := (X_Standard_Operation_Id is NOT NULL);
66
67 l_return_status VARCHAR2(1);
68 l_msg_count NUMBER;
69 l_msg_data VARCHAR2(1000);
70 l_error_message VARCHAR2(1000);
71 l_output_dir VARCHAR2(255);
72
73 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
74
75 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
76 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
77 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
78 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
79 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
80 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
81 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
82 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
83 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
84 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
85 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
86 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
87 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
88 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
89 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
90 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
91 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
92
93 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
94 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
95 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
96 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
97 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
98 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
99 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
100 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
101 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
102 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
103 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
104 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
105 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
106 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
107 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
108 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
109 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
110
111 BEGIN
112
113 l_return_status := FND_API.G_RET_STS_SUCCESS;
114
115
116 /* get output directory path from database */
117 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
118
119 l_eam_op_rec.HEADER_ID := 1;
120 l_eam_op_rec.BATCH_ID := 1;
121 l_eam_op_rec.WIP_ENTITY_ID := X_Wip_Entity_Id;
122 l_eam_op_rec.ORGANIZATION_ID := X_Organization_Id;
123 l_eam_op_rec.OPERATION_SEQ_NUM := X_Operation_Seq_Num;
124 l_eam_op_rec.STANDARD_OPERATION_ID := X_Standard_Operation_Id;
125 l_eam_op_rec.DEPARTMENT_ID := X_Department_Id;
126 l_eam_op_rec.OPERATION_SEQUENCE_ID := X_Operation_Sequence_Id;
127 l_eam_op_rec.DESCRIPTION := X_Description;
128 l_eam_op_rec.MINIMUM_TRANSFER_QUANTITY := X_Minimum_Transfer_Quantity;
129 l_eam_op_rec.COUNT_POINT_TYPE := X_Count_Point_Type;
130 l_eam_op_rec.BACKFLUSH_FLAG := X_Backflush_Flag;
131 l_eam_op_rec.SHUTDOWN_TYPE := X_Shutdown_Type;
132 l_eam_op_rec.START_DATE := X_First_Unit_Start_Date;
133 l_eam_op_rec.COMPLETION_DATE := X_Last_Unit_Completion_Date;
134 l_eam_op_rec.ATTRIBUTE_CATEGORY := X_Attribute_Category;
135 l_eam_op_rec.ATTRIBUTE1 := X_Attribute1;
136 l_eam_op_rec.ATTRIBUTE2 := X_Attribute2;
137 l_eam_op_rec.ATTRIBUTE3 := X_Attribute3;
138 l_eam_op_rec.ATTRIBUTE4 := X_Attribute4;
139 l_eam_op_rec.ATTRIBUTE5 := X_Attribute5;
140 l_eam_op_rec.ATTRIBUTE6 := X_Attribute6;
141 l_eam_op_rec.ATTRIBUTE7 := X_Attribute7;
142 l_eam_op_rec.ATTRIBUTE8 := X_Attribute8;
143 l_eam_op_rec.ATTRIBUTE9 := X_Attribute9;
144 l_eam_op_rec.ATTRIBUTE10 := X_Attribute10;
145 l_eam_op_rec.ATTRIBUTE11 := X_Attribute11;
146 l_eam_op_rec.ATTRIBUTE12 := X_Attribute12;
147 l_eam_op_rec.ATTRIBUTE13 := X_Attribute13;
148 l_eam_op_rec.ATTRIBUTE14 := X_Attribute14;
149 l_eam_op_rec.ATTRIBUTE15 := X_Attribute15;
150 l_eam_op_rec.LONG_DESCRIPTION := X_LONG_DESCRIPTION;
151 l_eam_op_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PUB.G_OPR_CREATE;
152 --Added for bug#4318049
153 l_eam_op_rec.X_POS := X_x_pos;
154 l_eam_op_rec.Y_POS := X_y_pos;
155
156
157 l_eam_op_tbl(1) := l_eam_op_rec;
158
159 /* Call Work Order API to perform the operations */
160
161 EAM_PROCESS_WO_PUB.Process_WO
162 ( p_bo_identifier => 'EAM'
163 , p_init_msg_list => TRUE
164 , p_api_version_number => 1.0
165 , p_eam_wo_rec => l_eam_wo_rec
166 , p_eam_op_tbl => l_eam_op_tbl
167 , p_eam_op_network_tbl => l_eam_op_network_tbl
168 , p_eam_res_tbl => l_eam_res_tbl
169 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
170 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
171 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
172 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
173 , p_eam_direct_items_tbl => l_eam_di_tbl
174 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
175 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
176 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
177 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
178 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
179 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
180 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
181 , p_eam_request_tbl => l_eam_request_tbl
182 , x_eam_wo_rec => l_out_eam_wo_rec
183 , x_eam_op_tbl => l_out_eam_op_tbl
184 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
185 , x_eam_res_tbl => l_out_eam_res_tbl
186 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
187 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
188 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
189 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
190 , x_eam_direct_items_tbl => l_out_eam_di_tbl
191 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
192 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
193 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
194 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
195 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
196 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
197 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
198 , x_eam_request_tbl => l_out_eam_request_tbl
199 , p_commit => 'N'
200 , x_return_status => l_return_status
201 , x_msg_count => l_msg_count
202 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
203 , p_debug_filename => 'EAMOPTHB.log'
204 , p_debug_file_mode => 'W'
205 , p_output_dir => l_output_dir
206 );
207
208 IF (l_return_status = 'S') THEN
209 X_L_EAM_OP_REC := l_out_eam_op_tbl(1);
210 IF(l_out_eam_wo_rec.scheduled_start_date IS NOT NULL)THEN
211 X_WO_Start_Date := l_out_eam_wo_rec.scheduled_start_date;
212 X_WO_Completion_Date := l_out_eam_wo_rec.scheduled_completion_date;
213 END IF;
214
215 ELSE
216 EAM_WORKORDER_UTIL_PKG.show_mesg;
217 END IF;
218
219 END Insert_Row;
220
221
222 PROCEDURE Update_Row(X_Rowid VARCHAR2,
223 X_Wip_Entity_Id NUMBER,
224 X_Operation_Seq_Num NUMBER,
225 X_Organization_Id NUMBER,
226 X_Last_Update_Date DATE,
227 X_Last_Updated_By NUMBER,
228 X_Last_Update_Login NUMBER,
229 X_Operation_Sequence_Id NUMBER,
230 X_Standard_Operation_Id NUMBER,
231 X_Department_Id NUMBER,
232 X_Shutdown_Type VARCHAR2,
233 X_Operation_Completed VARCHAR2,
234 X_Description VARCHAR2,
235 X_First_Unit_Start_Date DATE,
236 X_First_Unit_Completion_Date DATE,
237 X_Last_Unit_Start_Date DATE,
238 X_Last_Unit_Completion_Date DATE,
239 X_Attribute_Category VARCHAR2,
240 X_Attribute1 VARCHAR2,
241 X_Attribute2 VARCHAR2,
242 X_Attribute3 VARCHAR2,
243 X_Attribute4 VARCHAR2,
244 X_Attribute5 VARCHAR2,
245 X_Attribute6 VARCHAR2,
246 X_Attribute7 VARCHAR2,
247 X_Attribute8 VARCHAR2,
248 X_Attribute9 VARCHAR2,
249 X_Attribute10 VARCHAR2,
250 X_Attribute11 VARCHAR2,
251 X_Attribute12 VARCHAR2,
252 X_Attribute13 VARCHAR2,
253 X_Attribute14 VARCHAR2,
254 X_Attribute15 VARCHAR2,
255 X_X_Pos NUMBER,
256 X_Y_Pos NUMBER,
257 X_Long_Description VARCHAR2,
258 X_L_EAM_OP_REC OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_rec_type,
259 X_WO_Start_Date OUT NOCOPY DATE,
260 X_WO_Completion_Date OUT NOCOPY DATE
261
262 ) IS
263
264 l_return_status VARCHAR2(1);
265 l_msg_count NUMBER;
266 l_msg_data VARCHAR2(1000);
267 l_error_message VARCHAR2(1000);
268 l_output_dir VARCHAR2(255);
269 l_start_date DATE;
270 l_end_date DATE;
271
272 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
273
274 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
275 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
276 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
277 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
278 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
279 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
280 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
281 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
282 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
283 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
284 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
285 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
286 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
287 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
288 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
289 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
290 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
291
295 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
292 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
293 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
294 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
296 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
297 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
298 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
299 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
300 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
301 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
302 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
303 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
304 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
305 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
306 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
307 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
308 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
309
310 l_prev_description VARCHAR2(240);
311 l_prev_long_description VARCHAR2(4000);
312 l_prev_shutdown_type VARCHAR2(30);
313 l_prev_attribute_category VARCHAR2(30);
314 l_prev_attribute1 VARCHAR2(150);
315 l_prev_attribute2 VARCHAR2(150);
316 l_prev_attribute3 VARCHAR2(150);
317 l_prev_attribute4 VARCHAR2(150);
318 l_prev_attribute5 VARCHAR2(150);
319 l_prev_attribute6 VARCHAR2(150);
320 l_prev_attribute7 VARCHAR2(150);
321 l_prev_attribute8 VARCHAR2(150);
322 l_prev_attribute9 VARCHAR2(150);
323 l_prev_attribute10 VARCHAR2(150);
324 l_prev_attribute11 VARCHAR2(150);
325 l_prev_attribute12 VARCHAR2(150);
326 l_prev_attribute13 VARCHAR2(150);
327 l_prev_attribute14 VARCHAR2(150);
328 l_prev_attribute15 VARCHAR2(150);
329
330 BEGIN
331
332 l_return_status := FND_API.G_RET_STS_SUCCESS;
333
334
335 /* get output directory path from database */
336 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
337
338 l_eam_op_rec.HEADER_ID := 1;
339 l_eam_op_rec.BATCH_ID := 1;
340 l_eam_op_rec.WIP_ENTITY_ID := X_Wip_Entity_Id;
341 l_eam_op_rec.ORGANIZATION_ID := X_Organization_Id;
342 l_eam_op_rec.OPERATION_SEQ_NUM := X_Operation_Seq_Num;
343 l_eam_op_rec.STANDARD_OPERATION_ID := X_Standard_Operation_Id;
344 l_eam_op_rec.DEPARTMENT_ID := X_Department_Id;
345 l_eam_op_rec.OPERATION_SEQUENCE_ID := X_Operation_Sequence_Id;
346 l_eam_op_rec.START_DATE := X_First_Unit_Start_Date;
347 l_eam_op_rec.COMPLETION_DATE := X_Last_Unit_Completion_Date;
348 l_eam_op_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
349 --Added for bug#4615678
350 l_eam_op_rec.X_POS := X_x_pos;
351 l_eam_op_rec.Y_POS := X_y_pos;
352
353 BEGIN
354 SELECT
355 description
356 , shutdown_type
357 , long_description
358 , attribute_category
359 , attribute1
360 , attribute2
361 , attribute3
362 , attribute4
363 , attribute5
364 , attribute6
365 , attribute7
366 , attribute8
367 , attribute9
368 , attribute10
369 , attribute11
370 , attribute12
371 , attribute13
372 , attribute14
373 , attribute15
374 INTO
375 l_prev_description
376 ,l_prev_shutdown_type
377 ,l_prev_long_description
378 ,l_prev_attribute_category
379 ,l_prev_attribute1
380 ,l_prev_attribute2
381 ,l_prev_attribute3
382 ,l_prev_attribute4
383 ,l_prev_attribute5
384 ,l_prev_attribute6
385 ,l_prev_attribute7
386 ,l_prev_attribute8
387 ,l_prev_attribute9
388 ,l_prev_attribute10
389 ,l_prev_attribute11
390 ,l_prev_attribute12
391 ,l_prev_attribute13
392 ,l_prev_attribute14
393 ,l_prev_attribute15
394 FROM wip_operations wo
395 WHERE wo.wip_entity_id = X_Wip_Entity_Id
396 AND wo.organization_id = X_Organization_Id
397 AND wo.operation_seq_num = X_Operation_Seq_Num;
398
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 null;
402
403 END;
404
405 IF l_prev_description is not null and X_description is null THEN
406 l_eam_op_rec.description := FND_API.G_MISS_CHAR;
407 ELSE
408 l_eam_op_rec.description := X_description;
409 END IF;
410
411 IF l_prev_shutdown_type is not null and X_shutdown_type is null THEN
412 l_eam_op_rec.shutdown_type := FND_API.G_MISS_CHAR;
413 ELSE
414 l_eam_op_rec.shutdown_type := X_shutdown_type;
415 END IF;
416
417 IF l_prev_long_description is not null and X_Long_Description is null THEN
418 l_eam_op_rec.long_description := FND_API.G_MISS_CHAR;
419 ELSE
420 l_eam_op_rec.long_description := X_Long_Description;
421 END IF;
422
423 IF l_prev_attribute_category is not null and X_attribute_category is null THEN
424 l_eam_op_rec.attribute_category := FND_API.G_MISS_CHAR;
425 ELSE
426 l_eam_op_rec.attribute_category := X_attribute_category;
427 END IF;
428
432 l_eam_op_rec.attribute1 := X_attribute1;
429 IF l_prev_attribute1 is not null and X_attribute1 is null THEN
430 l_eam_op_rec.attribute1 := FND_API.G_MISS_CHAR;
431 ELSE
433 END IF;
434
435 IF l_prev_attribute2 is not null and X_attribute2 is null THEN
436 l_eam_op_rec.attribute2 := FND_API.G_MISS_CHAR;
437 ELSE
438 l_eam_op_rec.attribute2 := X_attribute2;
439 END IF;
440
441 IF l_prev_attribute3 is not null and X_attribute3 is null THEN
442 l_eam_op_rec.attribute3 := FND_API.G_MISS_CHAR;
443 ELSE
444 l_eam_op_rec.attribute3 := X_attribute3;
445 END IF;
446
447 IF l_prev_attribute4 is not null and X_attribute4 is null THEN
448 l_eam_op_rec.attribute4 := FND_API.G_MISS_CHAR;
449 ELSE
450 l_eam_op_rec.attribute4 := X_attribute4;
451 END IF;
452
453 IF l_prev_attribute5 is not null and X_attribute5 is null THEN
454 l_eam_op_rec.attribute5 := FND_API.G_MISS_CHAR;
455 ELSE
456 l_eam_op_rec.attribute5 := X_attribute5;
457 END IF;
458
459 IF l_prev_attribute6 is not null and X_attribute6 is null THEN
460 l_eam_op_rec.attribute6 := FND_API.G_MISS_CHAR;
461 ELSE
462 l_eam_op_rec.attribute6 := X_attribute6;
463 END IF;
464
465 IF l_prev_attribute7 is not null and X_attribute7 is null THEN
466 l_eam_op_rec.attribute7 := FND_API.G_MISS_CHAR;
467 ELSE
468 l_eam_op_rec.attribute7 := X_attribute7;
469 END IF;
470
471 IF l_prev_attribute8 is not null and X_attribute8 is null THEN
472 l_eam_op_rec.attribute8 := FND_API.G_MISS_CHAR;
473 ELSE
474 l_eam_op_rec.attribute8 := X_attribute8;
475 END IF;
476
477 IF l_prev_attribute9 is not null and X_attribute9 is null THEN
478 l_eam_op_rec.attribute9 := FND_API.G_MISS_CHAR;
479 ELSE
480 l_eam_op_rec.attribute9 := X_attribute9;
481 END IF;
482
483 IF l_prev_attribute10 is not null and X_attribute10 is null THEN
484 l_eam_op_rec.attribute10 := FND_API.G_MISS_CHAR;
485 ELSE
486 l_eam_op_rec.attribute10 := X_attribute10;
487 END IF;
488
489 IF l_prev_attribute11 is not null and X_attribute11 is null THEN
490 l_eam_op_rec.attribute11 := FND_API.G_MISS_CHAR;
491 ELSE
492 l_eam_op_rec.attribute11 := X_attribute11;
493 END IF;
494
495 IF l_prev_attribute12 is not null and X_attribute12 is null THEN
496 l_eam_op_rec.attribute12 := FND_API.G_MISS_CHAR;
497 ELSE
498 l_eam_op_rec.attribute12 := X_attribute12;
499 END IF;
500
501 IF l_prev_attribute13 is not null and X_attribute13 is null THEN
502 l_eam_op_rec.attribute13 := FND_API.G_MISS_CHAR;
503 ELSE
504 l_eam_op_rec.attribute13 := X_attribute13;
505 END IF;
506
507 IF l_prev_attribute14 is not null and X_attribute14 is null THEN
508 l_eam_op_rec.attribute14 := FND_API.G_MISS_CHAR;
509 ELSE
510 l_eam_op_rec.attribute14 := X_attribute14;
511 END IF;
512
513 IF l_prev_attribute15 is not null and X_attribute15 is null THEN
514 l_eam_op_rec.attribute15 := FND_API.G_MISS_CHAR;
515 ELSE
516 l_eam_op_rec.attribute15 := X_attribute15;
517 END IF;
518
519
520 l_eam_op_tbl(1) := l_eam_op_rec;
521
522 /* Call Work Order API to perform the operations */
523
524 EAM_PROCESS_WO_PUB.Process_WO
525 ( p_bo_identifier => 'EAM'
526 , p_init_msg_list => TRUE
527 , p_api_version_number => 1.0
528 , p_eam_wo_rec => l_eam_wo_rec
529 , p_eam_op_tbl => l_eam_op_tbl
530 , p_eam_op_network_tbl => l_eam_op_network_tbl
531 , p_eam_res_tbl => l_eam_res_tbl
532 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
533 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
534 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
535 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
536 , p_eam_direct_items_tbl => l_eam_di_tbl
537 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
538 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
539 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
540 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
541 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
542 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
543 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
544 , p_eam_request_tbl => l_eam_request_tbl
545 , x_eam_wo_rec => l_out_eam_wo_rec
546 , x_eam_op_tbl => l_out_eam_op_tbl
547 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
548 , x_eam_res_tbl => l_out_eam_res_tbl
549 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
550 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
551 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
552 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
553 , x_eam_direct_items_tbl => l_out_eam_di_tbl
554 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
555 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
556 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
557 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
558 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
559 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
560 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
561 , x_eam_request_tbl => l_out_eam_request_tbl
562 , p_commit => 'N'
563 , x_return_status => l_return_status
567 , p_debug_file_mode => 'W'
564 , x_msg_count => l_msg_count
565 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
566 , p_debug_filename => 'EAMOPTHB.log'
568 , p_output_dir => l_output_dir
569 );
570
571
572 IF (l_return_status = 'S') THEN
573 X_L_EAM_OP_REC := l_out_eam_op_tbl(1);
574 IF(l_out_eam_wo_rec.scheduled_start_date IS NOT NULL)THEN
575 X_WO_Start_Date := l_out_eam_wo_rec.scheduled_start_date;
576 X_WO_Completion_Date := l_out_eam_wo_rec.scheduled_completion_date;
577 END IF;
578
579 ELSE
580 EAM_WORKORDER_UTIL_PKG.show_mesg;
581 END IF;
582
583 END Update_Row;
584
585
586 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
587 X_Wip_Entity_Id NUMBER,
588 X_Operation_Seq_Num NUMBER,
589 X_Organization_Id NUMBER,
590 X_Operation_Sequence_Id NUMBER,
591 X_Standard_Operation_Id NUMBER,
592 X_Department_Id NUMBER,
593 X_Shutdown_Type VARCHAR2,
594 X_Operation_Completed VARCHAR2,
595 X_Description VARCHAR2,
596 X_First_Unit_Start_Date DATE,
597 X_First_Unit_Completion_Date DATE,
598 X_Last_Unit_Start_Date DATE,
599 X_Last_Unit_Completion_Date DATE,
600 X_Attribute_Category VARCHAR2,
601 X_Attribute1 VARCHAR2,
602 X_Attribute2 VARCHAR2,
603 X_Attribute3 VARCHAR2,
604 X_Attribute4 VARCHAR2,
605 X_Attribute5 VARCHAR2,
606 X_Attribute6 VARCHAR2,
607 X_Attribute7 VARCHAR2,
608 X_Attribute8 VARCHAR2,
609 X_Attribute9 VARCHAR2,
610 X_Attribute10 VARCHAR2,
611 X_Attribute11 VARCHAR2,
612 X_Attribute12 VARCHAR2,
613 X_Attribute13 VARCHAR2,
614 X_Attribute14 VARCHAR2,
615 X_Attribute15 VARCHAR2,
616 X_X_Pos NUMBER,
617 X_Y_Pos NUMBER,
618 X_Long_Description VARCHAR2) IS
619 CURSOR C IS
620 SELECT *
621 FROM WIP_OPERATIONS
622 WHERE rowid = X_Rowid
623 FOR UPDATE of Wip_Entity_Id NOWAIT;
624 Recinfo C%ROWTYPE;
625 BEGIN
626 OPEN C;
627 FETCH C INTO Recinfo;
628 if (C%NOTFOUND) then
629 CLOSE C;
630 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
631 fnd_message.raise_error;
632 app_exception.raise_exception;
633 end if;
634 CLOSE C;
635
636
637 if (Recinfo.wip_entity_id = X_Wip_Entity_Id)
638 AND (Recinfo.operation_seq_num = X_Operation_Seq_Num)
639 AND (Recinfo.organization_id = X_Organization_Id)
640 AND (nvl(Recinfo.operation_sequence_id, 1) =
641 nvl(X_Operation_Sequence_Id, 1))
642 AND (nvl(Recinfo.standard_operation_id, 1) =
643 nvl(X_Standard_Operation_Id, 1))
644 AND (Recinfo.department_id = X_Department_Id)
645 AND (nvl(Recinfo.shutdown_type, 'xxx') =
646 nvl(X_Shutdown_Type,'xxx'))
647 AND (Recinfo.operation_completed = X_Operation_Completed or
648 Recinfo.operation_completed is null)
649 AND (nvl(Recinfo.description, 'xxx') =
650 nvl(X_Description,'xxx'))
651 AND (Recinfo.first_unit_start_date = X_First_Unit_Start_Date)
652 AND (Recinfo.first_unit_completion_date = X_First_Unit_Completion_Date)
653 AND (Recinfo.last_unit_start_date = X_Last_Unit_Start_Date)
654 AND (Recinfo.last_unit_completion_date = X_Last_Unit_Completion_Date)
655 then
656 if ( (nvl(Recinfo.attribute_category, 'xxx') =
657 nvl(X_Attribute_Category, 'xxx'))
658 AND (nvl(Recinfo.attribute1, 'xxx') =
659 nvl(X_Attribute1, 'xxx'))
660 AND (nvl(Recinfo.attribute2, 'xxx') =
661 nvl(X_Attribute2, 'xxx'))
662 AND (nvl(Recinfo.attribute3, 'xxx') =
663 nvl(X_Attribute3, 'xxx'))
664 AND (nvl(Recinfo.attribute4, 'xxx') =
665 nvl(X_Attribute4, 'xxx'))
666 AND (nvl(Recinfo.attribute5, 'xxx') =
667 nvl(X_Attribute5, 'xxx'))
668 AND (nvl(Recinfo.attribute6, 'xxx') =
669 nvl(X_Attribute6, 'xxx'))
670 AND (nvl(Recinfo.attribute7, 'xxx') =
671 nvl(X_Attribute7, 'xxx'))
672 AND (nvl(Recinfo.attribute8, 'xxx') =
673 nvl(X_Attribute8, 'xxx'))
674 AND (nvl(Recinfo.attribute9, 'xxx') =
675 nvl(X_Attribute9, 'xxx'))
676 AND (nvl(Recinfo.attribute10, 'xxx') =
677 nvl(X_Attribute10, 'xxx'))
678 AND (nvl(Recinfo.attribute11, 'xxx') =
679 nvl(X_Attribute11, 'xxx'))
680 AND (nvl(Recinfo.attribute12, 'xxx') =
681 nvl(X_Attribute12, 'xxx'))
682 AND (nvl(Recinfo.attribute13, 'xxx') =
683 nvl(X_Attribute13, 'xxx'))
684 AND (nvl(Recinfo.attribute14, 'xxx') =
685 nvl(X_Attribute14, 'xxx'))
686 AND (nvl(Recinfo.attribute15, 'xxx') =
687 nvl(X_Attribute15, 'xxx'))
691 then
688 /* AND (nvl(Recinfo.long_description, 'xxx') =
689 nvl(X_Long_Description, 'xxx'))*/
690 )
692 return;
693 else
694 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
695 fnd_message.raise_error;
696 app_exception.raise_exception;
697 end if;
698 else
699 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
700 fnd_message.raise_error;
701 app_exception.raise_exception;
702 end if;
703 END Lock_Row;
704
705
706 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
707 X_WO_Start_Date OUT NOCOPY DATE,
708 X_WO_Completion_Date OUT NOCOPY DATE) IS
709
710 l_return_status VARCHAR2(1);
711 l_msg_count NUMBER;
712 l_msg_data VARCHAR2(1000);
713 l_error_message VARCHAR2(1000);
714 l_output_dir VARCHAR2(255);
715 l_start_date DATE;
716 l_end_date DATE;
717 l_wip_entity_id NUMBER;
718 l_organization_id NUMBER;
719 l_operation_seq_num NUMBER;
720
721 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
722
723 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
724 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
725 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
726 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
727 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
728 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
729 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
730 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
731 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
732 l_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
733 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
734 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
735 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
736 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
737 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
738 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
739 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
740
741 l_out_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
742 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
743 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
744 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
745 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
746 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
747 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
748 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
749 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
750 l_out_eam_wo_comp_rec EAM_PROCESS_WO_PUB.eam_wo_comp_rec_type;
751 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
752 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
753 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
754 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
755 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
756 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
757 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
758
759 BEGIN
760
761 l_return_status := FND_API.G_RET_STS_SUCCESS;
762
763
764 /* get output directory path from database */
765 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
766
767 select wip_entity_id,
768 organization_id,
769 operation_seq_num
770 into l_wip_entity_id,
771 l_organization_id,
772 l_operation_seq_num
773 from
774 wip_operations
775 where rowid=X_Rowid;
776
777 l_eam_op_rec.HEADER_ID := 1;
778 l_eam_op_rec.BATCH_ID := 1;
779 l_eam_op_rec.WIP_ENTITY_ID := l_wip_entity_id;
780 l_eam_op_rec.ORGANIZATION_ID := l_organization_id;
781 l_eam_op_rec.OPERATION_SEQ_NUM := l_operation_seq_num;
782 l_eam_op_rec.TRANSACTION_TYPE := EAM_PROCESS_WO_PUB.G_OPR_DELETE;
783
784 l_eam_op_tbl(1) := l_eam_op_rec;
785
786 EAM_PROCESS_WO_PUB.Process_WO
787 ( p_bo_identifier => 'EAM'
788 , p_init_msg_list => TRUE
789 , p_api_version_number => 1.0
790 , p_eam_wo_rec => l_eam_wo_rec
791 , p_eam_op_tbl => l_eam_op_tbl
792 , p_eam_op_network_tbl => l_eam_op_network_tbl
793 , p_eam_res_tbl => l_eam_res_tbl
794 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
795 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
796 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
797 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
798 , p_eam_direct_items_tbl => l_eam_di_tbl
799 , p_eam_wo_comp_rec => l_eam_wo_comp_rec
800 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
801 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
802 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
803 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
804 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
805 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
806 , p_eam_request_tbl => l_eam_request_tbl
807 , x_eam_wo_rec => l_out_eam_wo_rec
811 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
808 , x_eam_op_tbl => l_out_eam_op_tbl
809 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
810 , x_eam_res_tbl => l_out_eam_res_tbl
812 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
813 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
814 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
815 , x_eam_direct_items_tbl => l_out_eam_di_tbl
816 , x_eam_wo_comp_rec => l_out_eam_wo_comp_rec
817 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
818 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
819 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
820 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
821 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
822 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
823 , x_eam_request_tbl => l_out_eam_request_tbl
824 , p_commit => 'N'
825 , x_return_status => l_return_status
826 , x_msg_count => l_msg_count
827 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
828 , p_debug_filename => 'EAMOPTHB.log'
829 , p_debug_file_mode => 'W'
830 , p_output_dir => l_output_dir
831 );
832
833 IF (l_return_status <> 'S') THEN
834 EAM_WORKORDER_UTIL_PKG.show_mesg;
835 ELSE
836 IF(l_out_eam_wo_rec.scheduled_start_date IS NOT NULL)THEN
837 X_WO_Start_Date := l_out_eam_wo_rec.scheduled_start_date;
838 X_WO_Completion_Date := l_out_eam_wo_rec.scheduled_completion_date;
839 END IF;
840
841 END IF;
842
843 EXCEPTION
844 WHEN NO_DATA_FOUND THEN
845 RAISE NO_DATA_FOUND;
846 END Delete_Row;
847
848
849 END EAM_OPERATIONS_PKG;