[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_NETWORK_UTIL_PVT
Source
1 PACKAGE BODY EAM_WO_NETWORK_UTIL_PVT AS
2 /* $Header: EAMVWNUB.pls 120.6 2011/11/29 12:09:57 vpasupur ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWNUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_NETWORK_UTIL_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 11-SEP-2003 Basanth Roy Initial Creation
21 ***************************************************************************/
22
23
24
25 G_Pkg_Name VARCHAR2(30) := 'EAM_WO_NETWORK_UTIL_PVT';
26
27 g_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
28 g_dummy NUMBER;
29
30 PROCEDURE Move_WO
31 (
32 p_api_version IN NUMBER,
33 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
34 p_commit IN VARCHAR2 := FND_API.G_FALSE,
35 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
36
37 p_work_object_id IN NUMBER,
38 p_work_object_type_id IN NUMBER,
39 p_offset_days IN NUMBER := 1, -- 1 Day Default
40 p_offset_direction IN NUMBER := 1, -- Forward
41 p_start_date IN DATE := null,
42 p_completion_date IN DATE := null,
43 p_schedule_method IN NUMBER := 1, -- Forward Scheduling
44
45 p_ignore_firm_flag IN VARCHAR2 := 'N', -- Move firm work orders
46
47 x_return_status OUT NOCOPY VARCHAR2,
48 x_msg_count OUT NOCOPY NUMBER,
49 x_msg_data OUT NOCOPY VARCHAR2
50 )
51
52 IS
53 l_api_name CONSTANT VARCHAR2(30) := 'Move_WO';
54 l_api_version CONSTANT NUMBER := 1.0;
55
56 l_stmt_num NUMBER;
57 l_work_object_id NUMBER;
58 l_work_object_type_id NUMBER;
59 l_offset_days NUMBER;
60 l_offset_direction NUMBER;
61 l_start_date DATE;
62 l_completion_date DATE;
63 l_wo_start_date DATE;
64 l_wo_completion_date DATE;
65 l_schedule_method NUMBER;
66 l_child_index NUMBER;
67 l_operation_index NUMBER;
68 l_resource_index NUMBER;
69 l_res_inst_index NUMBER;
70 l_res_usage_index NUMBER;
71 l_material_index NUMBER;
72
73 l_return_status VARCHAR2(1);
74 l_msg_count NUMBER;
75 l_msg_data VARCHAR2(1000);
76 l_error_message VARCHAR2(1000);
77
78 l_eam_wo_rec eam_process_wo_pub.eam_wo_rec_type;
79 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
80 l_eam_res_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
81 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
82 l_eam_res_usage_rec EAM_PROCESS_WO_PUB.eam_res_usage_rec_type;
83 l_eam_mat_req_rec EAM_PROCESS_WO_PUB.eam_mat_req_rec_type;
84
85 l_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
86 l_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
87 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
88 l_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
89 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
90 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
91 l_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
92 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
93 l_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
94 l_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
95 l_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
96 l_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
97 l_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
98 l_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
99 l_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
100 l_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
101 l_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
102
103 l_out_eam_wo_relations_tbl EAM_PROCESS_WO_PUB.eam_wo_relations_tbl_type;
104 l_out_eam_wo_tbl EAM_PROCESS_WO_PUB.eam_wo_tbl_type;
105 l_out_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type;
106 l_out_eam_op_network_tbl EAM_PROCESS_WO_PUB.eam_op_network_tbl_type;
107 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type;
108 l_out_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type;
109 l_out_eam_sub_res_tbl EAM_PROCESS_WO_PUB.eam_sub_res_tbl_type;
110 l_out_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type;
111 l_out_eam_mat_req_tbl EAM_PROCESS_WO_PUB.eam_mat_req_tbl_type;
112 l_out_eam_di_tbl EAM_PROCESS_WO_PUB.eam_direct_items_tbl_type;
113 l_out_eam_wo_comp_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_tbl_type;
114 l_out_eam_wo_quality_tbl EAM_PROCESS_WO_PUB.eam_wo_quality_tbl_type;
115 l_out_eam_meter_reading_tbl EAM_PROCESS_WO_PUB.eam_meter_reading_tbl_type;
116 l_out_eam_wo_comp_rebuild_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_rebuild_tbl_type;
117 l_out_eam_wo_comp_mr_read_tbl EAM_PROCESS_WO_PUB.eam_wo_comp_mr_read_tbl_type;
118 l_out_eam_op_comp_tbl EAM_PROCESS_WO_PUB.eam_op_comp_tbl_type;
119 l_out_eam_request_tbl EAM_PROCESS_WO_PUB.eam_request_tbl_type;
120
121 l_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
122 l_out_eam_counter_prop_tbl EAM_PROCESS_WO_PUB.eam_counter_prop_tbl_type;
123
124 l_cur_firm_flag NUMBER;
125 l_cur_non_firm_flag NUMBER;
126
127 CURSOR constrained_children_cur (l_obj_id NUMBER, l_obj_type_id NUMBER,l_cur_non_firm_flag NUMBER,l_cur_firm_flag NUMBER) IS
128 ( SELECT WSR1.CHILD_OBJECT_ID CHILD_OBJECT_ID,
129 WSR1.CHILD_OBJECT_TYPE_ID CHILD_OBJECT_TYPE_ID,
130 WSR1.PARENT_OBJECT_ID PARENT_OBJECT_ID,
131 WDJ.ORGANIZATION_ID,
132 WDJ.SCHEDULED_START_DATE,
133 WDJ.SCHEDULED_COMPLETION_DATE,
134 WDJ.requested_start_date,
135 WDJ.due_date,
136 WSR1.WO_LEVEL ,
137 WDJ.FIRM_PLANNED_FLAG
138 FROM (SELECT WSR.CHILD_OBJECT_ID,
139 WSR.CHILD_OBJECT_TYPE_ID,
140 WSR.PARENT_OBJECT_ID,
141 LEVEL WO_LEVEL
142 FROM WIP_SCHED_RELATIONSHIPS WSR
143 WHERE WSR.RELATIONSHIP_TYPE = 1
144 CONNECT BY prior WSR.CHILD_OBJECT_ID = WSR.PARENT_OBJECT_ID
145 START WITH WSR.PARENT_OBJECT_ID = l_obj_id) WSR1,
146 WIP_DISCRETE_JOBS WDJ
147 WHERE l_obj_type_id = 1
148 AND WSR1.CHILD_OBJECT_ID = WDJ.WIP_ENTITY_ID
149 AND ( WDJ.firm_planned_flag = l_cur_non_firm_flag OR WDJ.firm_planned_flag = l_cur_firm_flag)
150 AND WDJ.status_type NOT IN (7,4,5,12,14)
151 )
152 UNION
153 ( SELECT WDJ.WIP_ENTITY_ID CHILD_OBJECT_ID,
154 l_obj_type_id "CHILD_OBJECT_TYPE_ID",
155 WDJ.PARENT_WIP_ENTITY_ID PARENT_OBJECT_ID,
156 WDJ.ORGANIZATION_ID,
157 WDJ.SCHEDULED_START_DATE,
158 WDJ.SCHEDULED_COMPLETION_DATE,
159 WDJ.requested_start_date,
160 WDJ.due_date,
161 0 WO_LEVEL,
162 WDJ.FIRM_PLANNED_FLAG
163 FROM WIP_DISCRETE_JOBS WDJ
164 WHERE WDJ.WIP_ENTITY_ID = l_obj_id
165 AND l_obj_type_id = 1
166 AND WDJ.status_type NOT IN (7,4,5,12,14)
167 ) ORDER BY 8 DESC ;
168
169
170 CURSOR workorder_operations_cur (l_obj_id NUMBER, l_obj_type_id NUMBER) IS
171 SELECT WO.FIRST_UNIT_START_DATE START_DATE,
172 WO.FIRST_UNIT_COMPLETION_DATE COMPLETION_DATE,
173 WO.OPERATION_SEQ_NUM,WO.DESCRIPTION,WO.LONG_DESCRIPTION,
174 WO.SHUTDOWN_TYPE, WO.ATTRIBUTE_CATEGORY, WO.ATTRIBUTE1, -- added columns for Bug 12320466
175 WO.ATTRIBUTE2, WO.ATTRIBUTE3, WO.ATTRIBUTE4, WO.ATTRIBUTE5,
176 WO.ATTRIBUTE6, WO.ATTRIBUTE7, WO.ATTRIBUTE8, WO.ATTRIBUTE9,
177 WO.ATTRIBUTE10, WO.ATTRIBUTE11, WO.ATTRIBUTE12, WO.ATTRIBUTE13,
178 WO.ATTRIBUTE14, WO.ATTRIBUTE15
179 FROM WIP_OPERATIONS WO
180 WHERE WO.WIP_ENTITY_ID = l_obj_id
181 AND l_obj_type_id = 1;
182
183 CURSOR workorder_material_cur (l_obj_id NUMBER, l_obj_type_id NUMBER) IS
184 select WRO.INVENTORY_ITEM_ID,OPERATION_SEQ_NUM,DATE_REQUIRED,STOCK_ENABLED_FLAG
185 FROM WIP_REQUIREMENT_OPERATIONS WRO,
186 MTL_SYSTEM_ITEMS_B MSI
187 WHERE WRO.WIP_ENTITY_ID = l_obj_id
188 AND MSI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
189 AND MSI.ORGANIZATION_ID = WRO.ORGANIZATION_ID
190 AND l_obj_type_id = 1;
191
192 CURSOR workorder_resources_cur (l_obj_id NUMBER, l_obj_type_id NUMBER) IS
193 select WOR.START_DATE, WOR.COMPLETION_DATE,
194 WOR.OPERATION_SEQ_NUM,
195 WOR.RESOURCE_SEQ_NUM
196 FROM WIP_OPERATION_RESOURCES WOR
197 WHERE WOR.WIP_ENTITY_ID = l_obj_id
198 AND l_obj_type_id = 1;
199
200 CURSOR workorder_res_inst_cur (l_obj_id NUMBER, l_obj_type_id NUMBER) IS
201 select WORI.START_DATE, WORI.COMPLETION_DATE,
202 wORI.OPERATION_SEQ_NUM,
203 WORI.RESOURCE_SEQ_NUM,
204 WORI.INSTANCE_ID,
205 WORI.SERIAL_NUMBER
206 FROM WIP_OP_RESOURCE_INSTANCES WORI
207 WHERE WORI.WIP_ENTITY_ID = l_obj_id
208 AND l_obj_type_id = 1;
209
210 CURSOR workorder_res_usage_cur (l_obj_id NUMBER, l_obj_type_id NUMBER) IS
211 SELECT woru.start_date,
212 woru.completion_date,
213 woru.operation_seq_num,
214 woru.resource_seq_num,
215 woru.instance_id,
216 woru.serial_number,
217 woru.assigned_units
218 FROM wip_operation_resource_usage woru, wip_discrete_jobs wdj
219 WHERE woru.wip_entity_id = l_obj_id
220 AND wdj.wip_entity_id = l_obj_id
221 -- AND wdj.firm_planned_flag = l_cur_firm_flag
222 AND l_obj_type_id = 1;
223
224 l_output_dir VARCHAR2(512);
225
226 BEGIN
227
228
229 /*******************************************************************
230 * Procedure : Move_WO
231 * Returns : None
232 * Parameters IN :
233 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
234 * Mesg Token Table
235 * Return Status
236 * Purpose : This API moves a Work Order structure
237 *********************************************************************/
238
239 -- 3942544
240 l_cur_non_firm_flag := 2;
241
242 if p_ignore_firm_flag = 'Y' then
243 l_cur_firm_flag :=1;
244 else
245 l_cur_firm_flag :=2;
246 end if;
247
248 EAM_WORKORDER_UTIL_PKG.log_path(l_output_dir);
249
250 -- Standard Start of API savepoint
251 SAVEPOINT EAM_WO_NETWORK_UTIL_PVT;
252 -- Standard call to check for call compatibility.
253 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
254 p_api_version ,
255 l_api_name ,
256 G_PKG_NAME )
257 THEN
258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
259 END IF;
260 -- Initialize message list if p_init_msg_list is set to TRUE.
261 IF FND_API.to_Boolean( p_init_msg_list ) THEN
262 FND_MSG_PUB.initialize;
263 END IF;
264 -- Initialize API return status to success
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266 x_msg_count := 0;
267 -- API body
268
269 /* Initialize the local variables */
270 l_stmt_num := 10;
271 l_child_index := 1;
272
273 l_work_object_id := p_work_object_id;
274 l_work_object_type_id := p_work_object_type_id;
275 l_offset_days := p_offset_days;
276 l_offset_direction := p_offset_direction;
277 l_schedule_method := p_schedule_method;
278 l_return_status := FND_API.G_RET_STS_SUCCESS;
279 l_start_date := p_start_date;
280 l_completion_date := p_completion_date;
281
282 select scheduled_start_date, scheduled_completion_date
283 into l_wo_start_date, l_wo_completion_date
284 from wip_discrete_jobs where
285 wip_entity_id = p_work_object_id;
286
287 if l_offset_days is null then
288 if l_start_date is not null then
289 l_offset_days := ABS(l_start_date - l_wo_start_date);
290 if l_start_date - l_wo_start_date > 0 then
291 l_offset_direction :=1; -- Work Order is moved forward
292 else
293 l_offset_direction :=2; -- Work Order is moved backward
294 end if;
295 elsif l_completion_date is not null then
296 l_offset_days := ABS(l_completion_date - l_wo_completion_date);
297 if l_completion_date - l_wo_completion_date > 0 then
298 l_offset_direction :=1; -- Work Order is moved forward
299 else
300 l_offset_direction :=2; -- Work Order is moved backward
301 end if;
302 end if;
303 end if;
304
305
306 IF (l_offset_direction <> 1) THEN -- Move Backward
307 l_offset_days := l_offset_days * (-1);
308 END IF;
309
310 /* Process Work Order Bottom up */
311 -- 3942544
312 l_operation_index := 1;
313 l_material_index := 1;
314 l_resource_index := 1;
315 l_res_inst_index := 1;
316 l_res_usage_index := 1 ;
317 FOR child IN constrained_children_cur (l_work_object_id, l_work_object_type_id,l_cur_non_firm_flag,l_cur_firm_flag)
318 LOOP
319 l_eam_wo_rec.batch_id := 1;
320 l_eam_wo_rec.header_id := child.CHILD_OBJECT_ID;
321 l_eam_wo_rec.wip_entity_id := child.CHILD_OBJECT_ID;
322 l_eam_wo_rec.parent_wip_entity_id := child.PARENT_OBJECT_ID;
323 l_eam_wo_rec.organization_id := child.ORGANIZATION_ID;
324 l_eam_wo_rec.scheduled_start_date := child.SCHEDULED_START_DATE + l_offset_days;
325 l_eam_wo_rec.scheduled_completion_date := child.SCHEDULED_COMPLETION_DATE + l_offset_days;
326 l_eam_wo_rec.validate_structure := 'Y'; -- added for bug# 3544860
327 l_eam_wo_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
328
329 if p_schedule_method = 1 then -- forward sched
330 l_eam_wo_rec.requested_start_date := child.scheduled_start_date + l_offset_days;
331 l_eam_wo_rec.due_date := null;
332 else -- backward sched
333 l_eam_wo_rec.due_date := child.scheduled_completion_date + l_offset_days;
334 l_eam_wo_rec.requested_start_date := null;
335 end if;
336
337 l_eam_wo_tbl(l_child_index) := l_eam_wo_rec;
338 l_child_index := l_child_index + 1;
339
340 FOR operation IN workorder_operations_cur (child.CHILD_OBJECT_ID, child.CHILD_OBJECT_TYPE_ID)
341 LOOP
342 l_eam_op_rec.batch_id := 1;
343 l_eam_op_rec.header_id := child.CHILD_OBJECT_ID;
344 l_eam_op_rec.wip_entity_id := child.CHILD_OBJECT_ID;
345 l_eam_op_rec.organization_id := child.ORGANIZATION_ID;
346 l_eam_op_rec.operation_seq_num := operation.operation_seq_num;
347 l_eam_op_rec.start_date := operation.START_DATE + l_offset_days;
348 l_eam_op_rec.completion_date := operation.COMPLETION_DATE + l_offset_days;
349 l_eam_op_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
350 l_eam_op_rec.description := operation.description;
351 l_eam_op_rec.long_description := operation.long_description;
352 -- added columns for Bug 12320466
353 l_eam_op_rec.shutdown_type := operation.shutdown_type;
354 l_eam_op_rec.attribute_category := operation.attribute_category;
355 l_eam_op_rec.attribute1 := operation.attribute1;
356 l_eam_op_rec.attribute2 := operation.attribute2;
357 l_eam_op_rec.attribute3 := operation.attribute3;
358 l_eam_op_rec.attribute4 := operation.attribute4;
359 l_eam_op_rec.attribute5 := operation.attribute5;
360 l_eam_op_rec.attribute6 := operation.attribute6;
361 l_eam_op_rec.attribute7 := operation.attribute7;
362 l_eam_op_rec.attribute8 := operation.attribute8;
363 l_eam_op_rec.attribute9 := operation.attribute9;
364 l_eam_op_rec.attribute10 := operation.attribute10;
365 l_eam_op_rec.attribute11 := operation.attribute11;
366 l_eam_op_rec.attribute12 := operation.attribute12;
367 l_eam_op_rec.attribute13 := operation.attribute13;
368 l_eam_op_rec.attribute14 := operation.attribute14;
369 l_eam_op_rec.attribute15 := operation.attribute15;
370
371
372 l_eam_op_tbl(l_operation_index) := l_eam_op_rec;
373 l_operation_index := l_operation_index + 1;
374 END LOOP;
375
376 -- 3942544
377 -- Do not move non stockable items
378
379 /* FOR material IN workorder_material_cur (child.CHILD_OBJECT_ID, child.CHILD_OBJECT_TYPE_ID)
380 LOOP
381 IF material.STOCK_ENABLED_FLAG = 'Y' THEN
382 l_eam_mat_req_rec.batch_id := 1;
383 l_eam_mat_req_rec.header_id := child.CHILD_OBJECT_ID;
384 l_eam_mat_req_rec.wip_entity_id := child.CHILD_OBJECT_ID;
385 l_eam_mat_req_rec.organization_id := child.ORGANIZATION_ID;
386 l_eam_mat_req_rec.inventory_item_id := material.INVENTORY_ITEM_ID;
387 l_eam_mat_req_rec.operation_seq_num := material.OPERATION_SEQ_NUM;
388 l_eam_mat_req_rec.date_required := material.DATE_REQUIRED + l_offset_days;
389 l_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
390
391 l_eam_mat_req_tbl(l_material_index) := l_eam_mat_req_rec;
392 l_material_index := l_material_index + 1;
393 END IF;
394 END LOOP;
395 */
396
397 FOR res IN workorder_resources_cur (child.CHILD_OBJECT_ID, child.CHILD_OBJECT_TYPE_ID)
398 LOOP
399 l_eam_res_rec.batch_id := 1;
400 l_eam_res_rec.header_id := child.CHILD_OBJECT_ID;
401 l_eam_res_rec.wip_entity_id := child.CHILD_OBJECT_ID;
402 l_eam_res_rec.organization_id := child.ORGANIZATION_ID;
403 l_eam_res_rec.operation_seq_num := res.operation_seq_num;
404 l_eam_res_rec.resource_seq_num := res.resource_seq_num;
405 l_eam_res_rec.start_date := res.START_DATE + l_offset_days;
406 l_eam_res_rec.completion_date := res.COMPLETION_DATE + l_offset_days;
407 l_eam_res_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
408
409 l_eam_res_tbl(l_resource_index) := l_eam_res_rec;
410 l_resource_index := l_resource_index + 1;
411 END LOOP;
412
413 FOR res_inst IN workorder_res_inst_cur (child.CHILD_OBJECT_ID, child.CHILD_OBJECT_TYPE_ID)
414 LOOP
415 l_eam_res_inst_rec.batch_id := 1;
416 l_eam_res_inst_rec.header_id := child.CHILD_OBJECT_ID;
417 l_eam_res_inst_rec.wip_entity_id := child.CHILD_OBJECT_ID;
418 l_eam_res_inst_rec.organization_id := child.ORGANIZATION_ID;
419 l_eam_res_inst_rec.operation_seq_num := res_inst.operation_seq_num;
420 l_eam_res_inst_rec.resource_seq_num := res_inst.resource_seq_num;
421 l_eam_res_inst_rec.instance_id := res_inst.instance_id;
422 l_eam_res_inst_rec.serial_number := res_inst.serial_number;
423 l_eam_res_inst_rec.start_date := res_inst.START_DATE + l_offset_days;
424 l_eam_res_inst_rec.completion_date := res_inst.COMPLETION_DATE + l_offset_days;
425 l_eam_res_inst_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
426
427 l_eam_res_inst_tbl(l_res_inst_index) := l_eam_res_inst_rec;
428 l_res_inst_index := l_res_inst_index + 1;
429
430 END LOOP;
431
432 FOR res_usage IN workorder_res_usage_cur (child.CHILD_OBJECT_ID, child.CHILD_OBJECT_TYPE_ID)
433 LOOP
434 l_eam_res_usage_rec.batch_id := 1;
435 l_eam_res_usage_rec.header_id := child.CHILD_OBJECT_ID;
436 l_eam_res_usage_rec.wip_entity_id := child.CHILD_OBJECT_ID;
437 l_eam_res_usage_rec.organization_id := child.ORGANIZATION_ID;
438 l_eam_res_usage_rec.operation_seq_num := res_usage.operation_seq_num;
439 l_eam_res_usage_rec.resource_seq_num := res_usage.resource_seq_num;
440 l_eam_res_usage_rec.instance_id := res_usage.instance_id;
441 l_eam_res_usage_rec.serial_number := res_usage.serial_number;
442 l_eam_res_usage_rec.assigned_units := res_usage.assigned_units;
443
444 l_eam_res_usage_rec.old_start_date := res_usage.START_DATE ;
445 l_eam_res_usage_rec.old_completion_date := res_usage.COMPLETION_DATE ;
446
447 l_eam_res_usage_rec.start_date := res_usage.START_DATE + l_offset_days;
448 l_eam_res_usage_rec.completion_date := res_usage.COMPLETION_DATE + l_offset_days;
449 l_eam_res_usage_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
450
451 l_eam_res_usage_tbl(l_res_usage_index) := l_eam_res_usage_rec;
452 l_res_usage_index := l_res_usage_index + 1;
453 END LOOP;
454
455 END LOOP;
456
457 l_out_eam_op_tbl := l_eam_op_tbl;
458 l_out_eam_op_network_tbl := l_eam_op_network_tbl;
459 l_out_eam_res_tbl := l_eam_res_tbl;
460 l_out_eam_res_inst_tbl := l_eam_res_inst_tbl;
461 l_out_eam_sub_res_tbl := l_eam_sub_res_tbl;
462 l_out_eam_res_usage_tbl := l_eam_res_usage_tbl;
463 l_out_eam_mat_req_tbl := l_eam_mat_req_tbl;
464
465 /* Call Work Order API to perform the operations */
466
467
468 eam_process_wo_pub.PROCESS_MASTER_CHILD_WO
469 ( p_bo_identifier => 'EAM'
470 , p_init_msg_list => TRUE
471 , p_api_version_number => 1.0
472 , p_eam_wo_relations_tbl => l_eam_wo_relations_tbl
473 , p_eam_wo_tbl => l_eam_wo_tbl
474 , p_eam_op_tbl => l_eam_op_tbl
475 , p_eam_op_network_tbl => l_eam_op_network_tbl
476 , p_eam_res_tbl => l_eam_res_tbl
477 , p_eam_res_inst_tbl => l_eam_res_inst_tbl
478 , p_eam_sub_res_tbl => l_eam_sub_res_tbl
479 , p_eam_mat_req_tbl => l_eam_mat_req_tbl
480 , p_eam_direct_items_tbl => l_eam_di_tbl
481 , p_eam_res_usage_tbl => l_eam_res_usage_tbl
482 , p_eam_wo_comp_tbl => l_eam_wo_comp_tbl
483 , p_eam_wo_quality_tbl => l_eam_wo_quality_tbl
484 , p_eam_meter_reading_tbl => l_eam_meter_reading_tbl
485 , p_eam_counter_prop_tbl => l_eam_counter_prop_tbl
486 , p_eam_wo_comp_rebuild_tbl => l_eam_wo_comp_rebuild_tbl
487 , p_eam_wo_comp_mr_read_tbl => l_eam_wo_comp_mr_read_tbl
488 , p_eam_op_comp_tbl => l_eam_op_comp_tbl
489 , p_eam_request_tbl => l_eam_request_tbl
490 , x_eam_wo_relations_tbl => l_eam_wo_relations_tbl
491 , x_eam_wo_tbl => l_out_eam_wo_tbl
492 , x_eam_op_tbl => l_out_eam_op_tbl
493 , x_eam_op_network_tbl => l_out_eam_op_network_tbl
494 , x_eam_res_tbl => l_out_eam_res_tbl
495 , x_eam_res_inst_tbl => l_out_eam_res_inst_tbl
496 , x_eam_sub_res_tbl => l_out_eam_sub_res_tbl
497 , x_eam_mat_req_tbl => l_out_eam_mat_req_tbl
498 , x_eam_direct_items_tbl => l_out_eam_di_tbl
499 , x_eam_res_usage_tbl => l_out_eam_res_usage_tbl
500 , x_eam_wo_comp_tbl => l_out_eam_wo_comp_tbl
501 , x_eam_wo_quality_tbl => l_out_eam_wo_quality_tbl
502 , x_eam_meter_reading_tbl => l_out_eam_meter_reading_tbl
503 , x_eam_counter_prop_tbl => l_out_eam_counter_prop_tbl
504 , x_eam_wo_comp_rebuild_tbl => l_out_eam_wo_comp_rebuild_tbl
505 , x_eam_wo_comp_mr_read_tbl => l_out_eam_wo_comp_mr_read_tbl
506 , x_eam_op_comp_tbl => l_out_eam_op_comp_tbl
507 , x_eam_request_tbl => l_out_eam_request_tbl
508 , p_commit => 'N'
509 -- , x_error_msg_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.error_tbl_type
510 , x_return_status => l_return_status
511 , x_msg_count => l_msg_count
512 , p_debug => NVL(fnd_profile.value('EAM_DEBUG'), 'N')
513 , p_debug_filename => 'movewo.log'
514 , p_output_dir => l_output_dir
515 , p_debug_file_mode => 'W'
516 );
517
518
519
520 --dbms_output.put_line('PARENT MOVE RETURN VAL = '||l_return_status);
521 --dbms_output.put_line( '######');
522
523 x_msg_count := l_msg_count;
524
525 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
526 x_return_status := FND_API.G_RET_STS_ERROR;
527 RETURN;
528
529 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
531 RETURN;
532
533 END IF;
534
535 -- End of API body.
536 -- Standard check of p_commit.
537 IF FND_API.To_Boolean( p_commit ) THEN
538 --dbms_output.put_line('committing');
539 COMMIT WORK;
540 END IF;
541
542 -- Standard call to get message count and if count is 1, get message info.
543 FND_MSG_PUB.Count_And_Get
544 ( p_count => x_msg_count ,
545 p_data => x_msg_data
546 );
547 EXCEPTION
548 WHEN FND_API.G_EXC_ERROR THEN
549 x_return_status := FND_API.G_RET_STS_ERROR ;
550 FND_MSG_PUB.Count_And_Get
551 ( p_count => x_msg_count ,
552 p_data => x_msg_data
553 );
554
555
556 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
558 FND_MSG_PUB.Count_And_Get
559 (
560 p_count => x_msg_count,
561 p_data => x_msg_data
562 );
563
564 WHEN OTHERS THEN
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
566 IF FND_MSG_PUB.Check_Msg_Level
567 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
568 THEN
569 FND_MSG_PUB.Add_Exc_Msg
570 ( G_PKG_NAME,
571 l_api_name||'('||l_stmt_num||')'
572 );
573 END IF;
574 FND_MSG_PUB.Count_And_Get
575 ( p_count => x_msg_count,
576 p_data => x_msg_data
577 );
578
579
580 END move_WO;
581
582
583
584 /*******************************************************************
585 * Procedure : Schedule_for_Move
586 * Returns : None
587 * Parameters IN :
588 * Parameters OUT NOCOPY: Work Object ID, Work Object Type
589 * Mesg Token Table
590 * Return Status
591 * Purpose : This API schedules the work order after move
592 *********************************************************************/
593
594 PROCEDURE Schedule_for_Move
595 (
596 p_api_version IN NUMBER,
597 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
598 p_commit IN VARCHAR2 := FND_API.G_FALSE,
599 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
600
601 p_work_object_id IN NUMBER,
602 p_work_object_type_id IN NUMBER,
603 p_offset_days IN NUMBER := 1, -- 1 Day Default
604 p_offset_direction IN NUMBER := 1, -- Ahead
605 p_schedule_method IN NUMBER := 1, -- Forward Scheduling
606
607 x_return_status OUT NOCOPY VARCHAR2,
608 x_msg_count OUT NOCOPY NUMBER,
609 x_msg_data OUT NOCOPY VARCHAR2
610
611 )
612
613 IS
614 l_api_name CONSTANT VARCHAR2(30) := 'Schedule_for_Move';
615 l_api_version CONSTANT NUMBER := 1.0;
616
617 l_stmt_num NUMBER;
618 l_work_object_id NUMBER;
619 l_work_object_type_id NUMBER;
620 l_offset_days NUMBER;
621 l_offset_direction NUMBER;
622 l_schedule_method NUMBER;
623
624 l_job_status NUMBER;
625 l_organization_id NUMBER;
626 l_scheduled_start_date DATE;
627 l_scheduled_completion_date DATE;
628 l_temp_start_date DATE;
629 l_temp_completion_date DATE;
630
631
632 l_return_status VARCHAR2(1);
633 l_msg_count NUMBER;
634 l_msg_data VARCHAR2(1000);
635 l_error_message VARCHAR2(1000);
636
637
638 BEGIN
639 -- Standard Start of API savepoint
640 SAVEPOINT EAM_WO_NETWORK_UTIL_PVT;
641 -- Standard call to check for call compatibility.
642 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
643 p_api_version ,
644 l_api_name ,
645 G_PKG_NAME )
646 THEN
647 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
648 END IF;
649 -- Initialize message list if p_init_msg_list is set to TRUE.
650 IF FND_API.to_Boolean( p_init_msg_list ) THEN
651 FND_MSG_PUB.initialize;
652 END IF;
653 -- Initialize API return status to success
654 x_return_status := FND_API.G_RET_STS_SUCCESS;
655 -- API body
656
657 /* Initialize the local variables */
658 l_stmt_num := 10;
659 l_work_object_id := p_work_object_id;
660 l_work_object_type_id := p_work_object_type_id;
661 l_offset_days := p_offset_days;
662 l_offset_direction := p_offset_direction;
663 l_schedule_method := p_schedule_method;
664
665
666 l_job_status := NULL;
667 l_organization_id := NULL;
668 l_scheduled_start_date := NULL;
669 l_scheduled_completion_date := NULL;
670
671 BEGIN
672 SELECT WDJ.STATUS_TYPE,
673 WDJ.ORGANIZATION_ID,
674 WDJ.SCHEDULED_START_DATE,
675 WDJ.SCHEDULED_COMPLETION_DATE
676 INTO l_job_status,
677 l_organization_id,
678 l_scheduled_start_date,
679 l_scheduled_completion_date
680 FROM WIP_DISCRETE_JOBS WDJ
681 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
682 AND l_work_object_type_id = 1;
683 EXCEPTION
684 WHEN OTHERS THEN
685 x_return_status := FND_API.G_RET_STS_ERROR;
686
687 RETURN;
688 END;
689
690 -- dbms_output.put_line('JOB = '|| l_work_object_id);
691 -- dbms_output.put_line('BEFORE START =' || to_char(l_scheduled_start_date, 'DD-MON-YYYY HH24:MM:SS'));
692 -- dbms_output.put_line('BEFORE END =' || to_char(l_scheduled_completion_date, 'DD-MON-YYYY HH24:MM:SS'));
693 -- dbms_output.put_line('OFFSET DAYS = '||l_offset_days);
694
695
696 IF (l_offset_direction <> 1) THEN -- Move Backward
697
698 l_offset_days := l_offset_days * (-1);
699 l_offset_days := l_offset_days * (-1);
700 END IF;
701
702 BEGIN
703
704 /* Update the job start and Enc Dates */
705 UPDATE WIP_DISCRETE_JOBS WDJ
706 SET WDJ.SCHEDULED_START_DATE = WDJ.SCHEDULED_START_DATE + l_offset_days,
707 WDJ.SCHEDULED_COMPLETION_DATE = WDJ.SCHEDULED_COMPLETION_DATE + l_offset_days
708 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
709 AND l_work_object_type_id = 1;
710
711
712
713 UPDATE WIP_OPERATIONS WO
714 SET WO.FIRST_UNIT_START_DATE = WO.FIRST_UNIT_START_DATE + l_offset_days,
715 WO.FIRST_UNIT_COMPLETION_DATE = WO.FIRST_UNIT_COMPLETION_DATE + l_offset_days,
716 WO.LAST_UNIT_START_DATE = WO.LAST_UNIT_START_DATE + l_offset_days,
717 WO.LAST_UNIT_COMPLETION_DATE = WO.LAST_UNIT_COMPLETION_DATE + l_offset_days
718 WHERE WO.WIP_ENTITY_ID = l_work_object_id
719 AND l_work_object_type_id = 1;
720
721
722 UPDATE WIP_OPERATION_RESOURCES WOR
723 SET WOR.START_DATE = WOR.START_DATE + l_offset_days,
724 WOR.COMPLETION_DATE = WOR.COMPLETION_DATE + l_offset_days
725 WHERE WOR.WIP_ENTITY_ID = l_work_object_id
726 AND l_work_object_type_id = 1;
727
728
729 UPDATE WIP_OPERATION_RESOURCE_USAGE WORU
730 SET WORU.START_DATE = WORU.START_DATE + l_offset_days,
731 WORU.COMPLETION_DATE = WORU.COMPLETION_DATE + l_offset_days
732 WHERE WORU.WIP_ENTITY_ID = l_work_object_id
733 AND l_work_object_type_id = 1;
734
735
736 UPDATE WIP_OP_RESOURCE_INSTANCES WORI
737 SET WORI.START_DATE = WORI.START_DATE + l_offset_days,
738 WORI.COMPLETION_DATE = WORI.COMPLETION_DATE + l_offset_days
739 WHERE WORI.WIP_ENTITY_ID = l_work_object_id
740 AND l_work_object_type_id = 1;
741
742
743 /* Reset it Back for next iteration */
744 IF (l_offset_direction <> 1) THEN -- Move Backward
745 l_offset_days := l_offset_days * (-1);
746 l_offset_days := l_offset_days * (-1);
747 END IF;
748 EXCEPTION
749 WHEN OTHERS THEN
750
751 FND_MSG_PUB.Add_Exc_Msg
752 ( G_PKG_NAME,
753 l_api_name||'('||l_stmt_num||')'
754 );
755
756 END;
757
758
759
760 IF (l_job_status NOT IN (3,4,5,6,7,12,14,15)) THEN -- Job in Planning Stage
761 --dbms_output.put_line('Inside non-execution WO');
762
763 IF (l_offset_direction = 1) THEN -- Move Forward
764
765 IF (l_schedule_method = 1) THEN -- Forward Scheduling
766
767 l_temp_start_date := l_scheduled_start_date + l_offset_days;
768 l_temp_completion_date := NULL;
769
770 -- dbms_output.put_line('OLD START = '||to_char(l_scheduled_start_date,'DD-MON-YYYY HH24:MM:SS'));
771 -- dbms_output.put_line('NEW START = '||to_char(l_temp_start_date,'DD-MON-YYYY HH24:MM:SS'));
772
773 EAM_WO_SCHEDULE_PVT.SCHEDULE_WO
774 (
775 p_organization_id => l_organization_id,
776 p_wip_entity_id => l_work_object_id,
777 p_start_date => l_temp_start_date,
778 p_completion_date => l_temp_completion_date,
779 x_error_message => l_error_message,
780 x_return_status => l_return_status
781 );
782
783 -- dbms_output.put_line('INFINITE SCHEDULER RETURN VAL = '||l_return_status);
784 -- dbms_output.put_line('INFINITE SCHEDULER RETURN MSG = '||l_error_message);
785
786 ELSE -- Backward Scheduling
787 l_temp_start_date := NULL;
788 l_temp_completion_date := l_scheduled_completion_date + l_offset_days;
789
790 EAM_WO_SCHEDULE_PVT.SCHEDULE_WO
791 (
792 p_organization_id => l_organization_id,
793 p_wip_entity_id => l_work_object_id,
794 p_start_date => l_temp_start_date,
795 p_completion_date => l_temp_completion_date,
796 x_error_message => l_error_message,
797 x_return_status => l_return_status
798 );
799
800 END IF;
801
802 ELSE -- Move Backwards
803
804
805 IF (l_schedule_method = 1) THEN -- Forward Scheduling
806
807 l_temp_start_date := l_scheduled_start_date - l_offset_days;
808 l_temp_completion_date := NULL;
809
810 EAM_WO_SCHEDULE_PVT.SCHEDULE_WO
811 (
812 p_organization_id => l_organization_id,
813 p_wip_entity_id => l_work_object_id,
814 p_start_date => l_temp_start_date,
815 p_completion_date => l_temp_completion_date,
816 x_error_message => l_error_message,
817 x_return_status => l_return_status
818 );
819 ELSE -- Backward Scheduling
820 l_temp_start_date := NULL;
821 l_temp_completion_date := l_scheduled_completion_date - l_offset_days;
822
823 EAM_WO_SCHEDULE_PVT.SCHEDULE_WO
824 (
825 p_organization_id => l_organization_id,
826 p_wip_entity_id => l_work_object_id,
827 p_start_date => l_temp_start_date,
828 p_completion_date => l_temp_completion_date,
829 x_error_message => l_error_message,
830 x_return_status => l_return_status
831 );
832
833 END IF; -- Schedule Method
834
835 END IF; -- OffSet Direction
836
837 /* Uncomment it later
838 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
839 x_return_status := FND_API.G_RET_STS_ERROR;
840
841 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843
844 END IF;
845 */
846
847 END IF; -- Job Status
848
849 /* Test portion -- delete later */
850 BEGIN
851 SELECT WDJ.STATUS_TYPE,
852 WDJ.ORGANIZATION_ID,
853 WDJ.SCHEDULED_START_DATE,
854 WDJ.SCHEDULED_COMPLETION_DATE
855 INTO l_job_status,
856 l_organization_id,
857 l_scheduled_start_date,
858 l_scheduled_completion_date
859 FROM WIP_DISCRETE_JOBS WDJ
860 WHERE WDJ.WIP_ENTITY_ID = l_work_object_id
861 AND l_work_object_type_id = 1;
862 EXCEPTION
863 WHEN OTHERS THEN
864
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 -- RETURN;
867 END;
868
869
870 -- dbms_output.put_line('AFTER START =' || to_char(l_scheduled_start_date, 'DD-MON-YYYY HH24:MM:SS'));
871 -- dbms_output.put_line('AFTER END =' || to_char(l_scheduled_completion_date, 'DD-MON-YYYY HH24:MM:SS'));
872
873
874 -- End of API body.
875 -- Standard check of p_commit.
876 IF FND_API.To_Boolean( p_commit ) THEN
877 --dbms_output.put_line('committing');
878 COMMIT WORK;
879 END IF;
880 -- Standard call to get message count and if count is 1, get message info.
881 FND_MSG_PUB.Count_And_Get
882 ( p_count => x_msg_count ,
883 p_data => x_msg_data
884 );
885
886
887 EXCEPTION
888
889 WHEN FND_API.G_EXC_ERROR THEN
890 x_return_status := FND_API.G_RET_STS_ERROR ;
891 FND_MSG_PUB.Count_And_Get
892 ( p_count => x_msg_count ,
893 p_data => x_msg_data
894 );
895
896
897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
899 FND_MSG_PUB.Count_And_Get
900 (
901 p_count => x_msg_count,
902 p_data => x_msg_data
903 );
904
905 WHEN OTHERS THEN
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
907 IF FND_MSG_PUB.Check_Msg_Level
908 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
909 THEN
910 FND_MSG_PUB.Add_Exc_Msg
911 ( G_PKG_NAME,
912 l_api_name||'('||l_stmt_num||')'
913 );
914 END IF;
915 FND_MSG_PUB.Count_And_Get
916 ( p_count => x_msg_count,
917 p_data => x_msg_data
918 );
919
920
921 END Schedule_for_Move;
922
923
924
925
926
927 END EAM_WO_NETWORK_UTIL_PVT;