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