[Home] [Help]
PACKAGE BODY: APPS.EAM_WORKORDERTRANSACTIONS_PUB
Source
1 PACKAGE BODY EAM_WorkOrderTransactions_PUB AS
2 /* $Header: EAMPWOTB.pls 120.4.12010000.2 2008/11/08 00:56:15 mashah ship $ */
3
4 -- Version Initial version 1.0 Alice Yang
5
6 -- Validate:
7 -- 1) transaction type is either TXN_TYPE_COMPLETE or TXT_TYPE_UNCOMPLETE
8 -- 2) transaction date <= sysdate
9
10 PROCEDURE Validate_Transaction(
11 p_x_return_status IN OUT NOCOPY VARCHAR2,
12 p_transaction_type IN NUMBER,
13 p_transaction_date IN DATE) IS
14 BEGIN
15 IF p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
16 IF (NOT p_transaction_type = G_TXN_TYPE_COMPLETE) AND (NOT p_transaction_type = G_TXN_TYPE_UNCOMPLETE) THEN
17 -- validate transaction type
18 FND_MESSAGE.set_name('EAM', 'EAM_INVALID_TXN_TYPE');
19 p_x_return_status := FND_API.G_RET_STS_ERROR;
20 ELSIF p_transaction_date > sysdate THEN
21 -- validate transaction_date
22 FND_MESSAGE.set_name('EAM', 'EAM_TXN_LATER_THAN_TODAY');
23 p_x_return_status := FND_API.G_RET_STS_ERROR;
24 END IF;
25 END IF;
26 END Validate_Transaction;
27
28 -- Validate (and Calculate):
29 -- 1) At least 2 of the 3 date related inputs are not null, and derive
30 -- the 3rd from the existing 2. If all 3 values are not null, then
31 -- duration is over written by end_date - start_date
32 -- 2) start_date <= end_date
33 -- 3) end_date <= sysdate
34 PROCEDURE Validate_Start_End_Dates(
35 p_x_return_status IN OUT NOCOPY VARCHAR2,
36 p_x_actual_start_date IN OUT NOCOPY DATE,
37 p_x_actual_end_date IN OUT NOCOPY DATE,
38 p_x_actual_duration IN OUT NOCOPY NUMBER) IS
39 BEGIN
40 IF p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
41 IF p_x_actual_start_date is not null THEN
42 IF p_x_actual_end_date is not null THEN
43 -- Fix for the bug 7136230
44 p_x_actual_duration := round(24*(p_x_actual_end_date - p_x_actual_start_date),3);
45 ELSIF p_x_actual_duration is not null THEN
46 p_x_actual_end_date := p_x_actual_start_date + p_x_actual_duration;
47 ELSE
48 -- missing both end_date and duration
49 FND_MESSAGE.set_name('EAM', 'EAM_MISSING_DATE_INFO');
50 p_x_return_status := FND_API.G_RET_STS_ERROR;
51 END IF;
52 ELSIF p_x_actual_end_date is not null THEN
53 IF p_x_actual_duration is not null THEN
54 p_x_actual_start_date := p_x_actual_end_date - p_x_actual_duration;
55 ELSE
56 -- missing both start_date and duration
57 FND_MESSAGE.set_name('EAM', 'EAM_MISSING_DATE_INFO');
58 p_x_return_status := FND_API.G_RET_STS_ERROR;
59 END IF;
60 ELSE
61 -- missing both start and end dates
62 FND_MESSAGE.set_name('EAM', 'EAM_MISSING_DATE_INFO');
63 p_x_return_status := FND_API.G_RET_STS_ERROR;
64 END IF;
65 END IF;
66 IF p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
67 IF p_x_actual_start_date > p_x_actual_end_date THEN
68 FND_MESSAGE.set_name('EAM', 'EAM_END_BEFORE_START');
69 p_x_return_status := FND_API.G_RET_STS_ERROR;
70 ELSIF p_x_actual_end_date > sysdate THEN
71 FND_MESSAGE.set_name('EAM', 'EAM_END_LATER_THAN_TODAY');
72 p_x_return_status := FND_API.G_RET_STS_ERROR;
73 END IF;
74 END IF;
75 END Validate_Start_End_Dates;
76
77 -- Validate:
78 -- 1) All required params are not null
79 -- 2) wip_entity_id is valid id
80 PROCEDURE Validate_Required_Information(
81 p_x_return_status IN OUT NOCOPY VARCHAR2,
82 p_wip_entity_id IN NUMBER,
83 p_transaction_type IN NUMBER,
84 p_transaction_date IN DATE,
85 x_organization_id OUT NOCOPY NUMBER,
86 x_parent_wip_entity_id OUT NOCOPY NUMBER,
87 x_asset_group_id OUT NOCOPY NUMBER,
88 x_asset_number OUT NOCOPY VARCHAR2,
89 x_rebuild_jobs OUT NOCOPY VARCHAR2,
90 x_manual_rebuild_flag OUT NOCOPY VARCHAR2,
91 x_shutdown_type OUT NOCOPY NUMBER) IS
92 l_rebuild_item_id NUMBER;
93 l_entity_type NUMBER;
94 l_status_type NUMBER;
95 BEGIN
96 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
97 return;
98 END IF;
99 IF (p_wip_entity_id is null or p_transaction_type is null or p_transaction_date is null) THEN
100 FND_MESSAGE.set_name('EAM', 'EAM_WORK_ORDER_MISSING_INFO');
101 p_x_return_status := FND_API.G_RET_STS_ERROR;
102 return;
103 END IF;
104
105 -- check wip_entity_id points to a valid eam workorder
106 select entity_type into l_entity_type
107 from wip_entities
108 where wip_entity_id = p_wip_entity_id;
109 if (NOT l_entity_type = 6) then
110 -- not an open eam job
111 FND_MESSAGE.set_name('EAM', 'EAM_NOT_OPEN_EAM_JOB');
112 p_x_return_status := FND_API.G_RET_STS_ERROR;
113 return;
114 END IF;
115
116 -- set out params by query wip_discrete_jobs table
117 select parent_wip_entity_id,
118 asset_group_id,
119 asset_number,
120 organization_id,
121 rebuild_item_id,
122 manual_rebuild_flag,
123 status_type,
124 shutdown_type
125 into x_parent_wip_entity_id,
126 x_asset_group_id,
127 x_asset_number,
128 x_organization_id,
129 l_rebuild_item_id,
130 x_manual_rebuild_flag,
131 l_status_type,
132 x_shutdown_type
133 from wip_discrete_jobs
134 where wip_entity_id = p_wip_entity_id;
135 if l_rebuild_item_id is null then
136 x_rebuild_jobs := 'N';
137 else
138 x_rebuild_jobs := 'Y';
139 end if;
140
141 if p_transaction_type = G_TXN_TYPE_COMPLETE and (NOT l_status_type = 3) then
142 FND_MESSAGE.set_name('EAM', 'EAM_NOT_RELEASED_STATUS');
143 p_x_return_status := FND_API.G_RET_STS_ERROR;
144 elsif p_transaction_type = G_TXN_TYPE_UNCOMPLETE and (NOT l_status_type = 4) then
145 FND_MESSAGE.set_name('EAM', 'EAM_NOT_COMPLETED_STATUS');
146 p_x_return_status := FND_API.G_RET_STS_ERROR;
147 end if;
148 END Validate_Required_Information;
149
150 -- Validate:
151 -- actual work order start date is no later than the earliest actual operation
152 -- start dates (for all operations under this work order), and vice versa
153 -- for the end dates
154 PROCEDURE Validate_Actl_Dates_vs_Optns(
155 p_x_return_status IN OUT NOCOPY VARCHAR2,
156 p_wip_entity_id IN NUMBER,
157 p_actual_start_date IN DATE,
158 p_actual_end_date IN DATE) IS
159
160 l_max_op_end_date DATE;
161 l_min_op_start_date DATE;
162
163 BEGIN
164 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
165 return;
166 END IF;
167
168 select max(actual_end_date) into l_max_op_end_date
169 from eam_op_completion_txns
170 where wip_entity_id = p_wip_entity_id and transaction_type = G_TXN_TYPE_COMPLETE;
171
172 select min(actual_start_date) into l_min_op_start_date
173 from eam_op_completion_txns
174 where wip_entity_id = p_wip_entity_id and transaction_type = G_TXN_TYPE_COMPLETE;
175
176 IF (l_max_op_end_date is not null AND
177 p_actual_end_date is not null AND
178 l_max_op_end_date > p_actual_end_date) then
179 FND_MESSAGE.set_name('EAM', 'EAM_WO_ACTUAL_END_DATE');
180 p_x_return_status := FND_API.G_RET_STS_ERROR;
181 return;
182 end IF;
183
184 IF (l_min_op_start_date is not null AND
185 p_actual_start_date is not null AND
186 l_min_op_start_date < p_actual_start_date) then
187 FND_MESSAGE.set_name('EAM', 'EAM_WO_ACTUAL_START_DATE');
188 p_x_return_status := FND_API.G_RET_STS_ERROR;
189 end IF;
190 END Validate_Actl_Dates_vs_Optns;
191
192 -- Validate:
193 -- actual start date must be no earlier than minimum start date of all open acct periods
194
195 /*bug5859455
196 PROCEDURE Validate_Date_vs_Acct_Periods(
197 p_x_return_status IN OUT NOCOPY VARCHAR2,
198 p_actual_start_date IN DATE,
199 p_organization_id IN NUMBER) IS
200
201 l_min_open_period_start_date DATE;
202 BEGIN
203 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
204 return;
205 END IF;
206 select nvl(min(period_start_date),sysdate+2)
207 into l_min_open_period_start_date
208 from org_acct_periods
209 where organization_id = p_organization_id
210 and open_flag = 'Y';
211 if (p_actual_start_date < l_min_open_period_start_date) then
212 FND_MESSAGE.set_name('EAM', 'EAM_MIN_WO_ACTUAL_START_DATE');
213 p_x_return_status := FND_API.G_RET_STS_ERROR;
214 end if;
215 END Validate_Date_vs_Acct_Periods;*/
216
217
218 PROCEDURE Validate_Date_vs_Acct_Periods(
219 p_x_return_status IN OUT NOCOPY VARCHAR2,
220 p_transaction_date IN DATE,
221 p_organization_id IN NUMBER) IS
222
223 l_min_open_period_start_date DATE;
224 BEGIN
225 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
226 return;
227 END IF;
228 select nvl(min(period_start_date),sysdate+2)
229 into l_min_open_period_start_date
230 from org_acct_periods
231 where organization_id = p_organization_id
232 and open_flag = 'Y';
233 if (p_transaction_date < l_min_open_period_start_date) then
234 FND_MESSAGE.set_name('EAM', 'EAM_TRANSACTION_DATE_INVALID');
235 p_x_return_status := FND_API.G_RET_STS_ERROR;
236 end if;
237 END Validate_Date_vs_Acct_Periods;
238
239
240
241 -- Calculate:
242 -- for uncomplete transaction types: set actual start and end date to the
243 -- the last start and end date for this work order
244 PROCEDURE Calculate_Actl_Dates(
245 p_x_return_status IN OUT NOCOPY VARCHAR2,
246 p_wip_entity_id IN NUMBER,
247 x_actual_start_date OUT NOCOPY DATE,
248 x_actual_end_date OUT NOCOPY DATE) IS
249
250 l_max_tran_date DATE;
251 BEGIN
252 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
253 return;
254 END IF;
255 select max(transaction_date) into l_max_tran_date
256 from eam_job_completion_txns where transaction_type = 1
257 and wip_entity_id = p_wip_entity_id;
258 select actual_start_date, actual_end_date into
259 x_actual_start_date, x_actual_end_date
260 from eam_job_completion_txns
261 where transaction_date = l_max_tran_date;
262 END Calculate_Actl_Dates;
263
264 -- Validate:
265 -- if subinventory is provided, check that it is an expense subinventory
266 PROCEDURE Validate_Expense_Inventory(
267 p_x_return_status IN OUT NOCOPY VARCHAR2,
268 p_instance_id IN VARCHAR2,
269 p_inventory_item_info IN Inventory_Item_Tbl_Type,
270 p_organization_id IN NUMBER) IS
271
272 l_asset_inventory NUMBER;
273 BEGIN
274 -- dbms_output.put_line('p_instance_id is '||p_instance_id);
275 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
276 return;
277 END IF;
278 IF (p_inventory_item_info.COUNT = 0) THEN
279 FND_MESSAGE.set_name('EAM', 'EAM_INVENTORY_NULL');
280 p_x_return_status := FND_API.G_RET_STS_ERROR;
281 RETURN;
282 END IF;
283
284 FOR i IN 1..p_inventory_item_info.COUNT LOOP
285 IF p_inventory_item_info(i).subinventory IS NULL THEN
286 FND_MESSAGE.set_name('EAM', 'EAM_INVENTORY_NULL');
287 p_x_return_status := FND_API.G_RET_STS_ERROR;
288 RETURN;
289 END IF;
290 END LOOP;
291
292 -- p_inventory_item_info can have multi-lines only if if this is service work order
293 if (p_instance_id is null) then
294 if (p_inventory_item_info.COUNT > 1) then
295 FND_MESSAGE.set_name('EAM', 'EAM_MULTIPLE_INVENTORY');
296 p_x_return_status := FND_API.G_RET_STS_ERROR;
297 return;
298 else
299 -- check for expense inventory
300 select asset_inventory into l_asset_inventory
301 from mtl_secondary_inventories
302 where secondary_inventory_name = p_inventory_item_info(1).subinventory
303 and organization_id = p_organization_id;
304 IF (NOT l_asset_inventory = 2) THEN
305 FND_MESSAGE.set_name('EAM', 'EAM_EXPENSE_SUBINVENTORY');
306 p_x_return_status := FND_API.G_RET_STS_ERROR;
307 return;
308 END IF;
309 end if;
310 end if;
311 END Validate_Expense_Inventory;
312
313 -- Validate:
314 -- 1) If item, subinventory, or org is locator control (i.e. either pre-specified
315 -- or dynamin locator control (2,3) then ensure that locator is provided
316 -- 2) locator is in the list of valid locators
317 PROCEDURE Validate_Locator_Control(
318 p_x_return_status IN OUT NOCOPY VARCHAR2,
319 p_asset_group_id IN NUMBER,
320 p_organization_id IN NUMBER,
321 p_inventory_item_info IN Inventory_Item_Tbl_Type) IS
322 l_locator NUMBER;
323 l_location_control_code NUMBER;
324 l_locator_type NUMBER;
325 l_stock_locator_control_code NUMBER;
326 l_inventory_location_id NUMBER;
327 l_count NUMBER;
328 l_subinventory VARCHAR2(100);
329 BEGIN
330 IF NOT p_x_return_status = FND_API.G_RET_STS_SUCCESS THEN
331 return;
332 END IF;
333 if p_asset_group_id IS NOT NULL then
334 select location_control_code into l_location_control_code
335 from mtl_system_items
336 where inventory_item_id = p_asset_group_id and
337 organization_id = p_organization_id;
338 end if;
339 select stock_locator_control_code into l_stock_locator_control_code
340 from mtl_parameters
341 where organization_id = p_organization_id;
342 for i in 1..p_inventory_item_info.COUNT loop
343 l_locator := p_inventory_item_info(i).locator;
344 l_subinventory := p_inventory_item_info(i).subinventory;
345 select locator_type into l_locator_type
346 from mtl_secondary_inventories
347 where secondary_inventory_name = l_subinventory
348 and organization_id = p_organization_id;
349
350 if (l_location_control_code in (2,3) or l_locator_type in (2,3)
351 or l_stock_locator_control_code in (2,3)) then
352 if l_locator is null then
353 FND_MESSAGE.set_name('EAM', 'EAM_LOCATOR_ID_IS_NULL');
354 p_x_return_status := FND_API.G_RET_STS_ERROR;
355 return;
356 end if;
357 end if;
358 if l_locator is not null then
359 select count(*) into l_count
360 from mtl_item_locations
361 where organization_id = p_organization_id and inventory_location_id = l_locator and subinventory_code = l_subinventory;
362 if (l_count = 0) then
363 FND_MESSAGE.set_name('EAM', 'EAM_NOT_VALID_LOCATOR_ID');
364 p_x_return_status := FND_API.G_RET_STS_ERROR;
365 return;
366 end if;
367 end if;
368 end loop;
369
370 end Validate_Locator_Control;
371
372 PROCEDURE Complete_Work_Order(
373 p_api_version IN NUMBER,
374 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
375 p_commit IN VARCHAR2 := fnd_api.g_false,
376 x_return_status OUT NOCOPY VARCHAR2,
377 x_msg_count OUT NOCOPY NUMBER,
378 x_msg_data OUT NOCOPY VARCHAR2,
379 p_wip_entity_id IN NUMBER,
380 p_transaction_type IN NUMBER,
381 p_transaction_date IN DATE,
382 p_instance_id IN NUMBER := null,
383 p_user_id IN NUMBER := fnd_global.user_id,
384 p_request_id IN NUMBER := null,
385 p_application_id IN NUMBER := null,
386 p_program_id IN NUMBER := null,
387 p_reconciliation_code IN VARCHAR2 := null,
388 p_actual_start_date IN DATE := null,
389 p_actual_end_date IN DATE := null,
390 p_actual_duration IN NUMBER := null,
391 p_shutdown_start_date IN DATE := null,
392 p_shutdown_end_date IN DATE := null,
393 p_shutdown_duration IN NUMBER := null,
394 p_inventory_item_info IN Inventory_Item_Tbl_Type,
395 p_reference IN VARCHAR2 := null,
396 p_reason IN VARCHAR2 := null,
397 p_attributes_rec IN Attributes_Rec_Type
398 ) IS
399
400 l_api_name CONSTANT VARCHAR2(30) := 'Complete_Work_Order';
401 l_api_version CONSTANT NUMBER := 1.0;
402
403 l_organization_id NUMBER;
404 l_rebuild_jobs VARCHAR2(1);
405 l_parent_wip_entity_id NUMBER;
406 l_asset_group_id NUMBER;
407 l_asset_number VARCHAR2(30);
408 l_manual_rebuild_flag VARCHAR2(1);
409 l_shutdown_type NUMBER;
410
411 l_actual_start_date DATE;
412 l_actual_end_date DATE;
413 l_actual_duration NUMBER;
414 l_shutdown_start_date DATE;
415 l_shutdown_end_date DATE;
416 l_shutdown_duration NUMBER;
417
418 l_errCode NUMBER;
419 l_errMsg VARCHAR2(100);
420 l_inventory_item_rec EAM_WorkOrderTransactions_PUB.Inventory_Item_Rec_Type;
421 l_inventory_item_tbl EAM_WorkOrderTransactions_PUB.Inventory_Item_Tbl_Type;
422 l_maintenance_source_id NUMBER;
423 l_rebuild_item_id NUMBER; /* Added for bug#5841713 */
424 BEGIN
425
426 -- Standard begin of API savepoint
427 SAVEPOINT Complete_Work_Order_PVT;
428 -- Check p_init_msg_list
429 IF FND_API.to_Boolean(p_init_msg_list) THEN
430 FND_MSG_PUB.initialize;
431 END IF;
432
433 -- Initialize API return status to success
434 x_return_status := FND_API.G_RET_STS_SUCCESS;
435
436 -- Initialize local date variables
437 l_actual_start_date := p_actual_start_date;
438 l_actual_end_date := p_actual_end_date;
439 l_actual_duration := p_actual_duration;
440 l_shutdown_start_date := p_shutdown_start_date;
441 l_shutdown_end_date := p_shutdown_end_date;
442 l_shutdown_duration := p_shutdown_duration;
443
444 Validate_Required_Information(
445 x_return_status,
446 p_wip_entity_id,
447 p_transaction_type,
448 p_transaction_date,
449 l_organization_id,
450 l_parent_wip_entity_id,
451 l_asset_group_id,
452 l_asset_number,
453 l_rebuild_jobs,
454 l_manual_rebuild_flag,
455 l_shutdown_type);
456
457
458 Validate_Transaction(
459 x_return_status,
460 p_transaction_type,
461 p_transaction_date);
462
463 -- if transaction type is complete work order
464 IF p_transaction_type = G_TXN_TYPE_COMPLETE THEN
465
466
467 Validate_Start_End_Dates(
468 x_return_status,
469 l_actual_start_date,
470 l_actual_end_date,
471 l_actual_duration);
472 -- validate shutdown dates exist if necessary
473 if l_shutdown_type = 2 then
474 Validate_Start_End_Dates(
475 x_return_status,
476 l_shutdown_start_date,
477 l_shutdown_end_date,
478 l_shutdown_duration);
479 end if;
480
481 Validate_Actl_Dates_vs_Optns(
482 x_return_status,
483 p_wip_entity_id,
484 l_actual_start_date,
485 l_actual_end_date);
486
487
488 /* bug 5859455 Validate_Date_vs_Acct_Periods(
489 x_return_status,
490 l_actual_start_date,
491 l_organization_id);*/
492
493 Validate_Date_vs_Acct_Periods(
494 x_return_status,
495 p_transaction_date,
496 l_organization_id);
497
498
499 IF l_rebuild_jobs = 'Y' THEN
500 IF l_manual_rebuild_flag = 'N' THEN
501 -- need subinventory
502
503 Validate_Expense_Inventory(
504 x_return_status,
505 p_instance_id,
506 p_inventory_item_info,
507 l_organization_id);
508
509 Validate_Locator_Control(
510 x_return_status,
511 l_asset_group_id,
512 l_organization_id,
513 p_inventory_item_info);
514 END IF;
515 END IF;
516 ELSIF p_transaction_type = G_TXN_TYPE_UNCOMPLETE THEN
517 Calculate_Actl_Dates(
518 x_return_status,
519 p_wip_entity_id,
520 l_actual_start_date,
521 l_actual_end_date);
522 END IF;
523
524 if x_return_status = FND_API.G_RET_STS_SUCCESS then
525
526
527 -- Bug 3676937 . For CMRO work orders need not check completion subinventory
528 select maintenance_object_source, rebuild_item_id
529 into l_maintenance_source_id, l_rebuild_item_id
530 from wip_discrete_jobs
531 where wip_entity_id = p_wip_entity_id;
532
533
534 if l_maintenance_source_id = 2 then -- for CMRO no inventory item info is required.
535
536
537 l_inventory_item_rec.subinventory := NULL;
538 l_inventory_item_rec.locator := NULL;
539 l_inventory_item_rec.lot_number := NULL;
540 l_inventory_item_rec.serial_number := NULL;
541 l_inventory_item_rec.quantity := NULL;
542 l_inventory_item_tbl(1) := l_inventory_item_rec;
543 else
544 IF l_rebuild_item_id IS NOT NULL THEN /* Added for bug#5841713 End */
545 l_inventory_item_rec.subinventory := p_inventory_item_info(1).subinventory;
546 l_inventory_item_rec.locator := p_inventory_item_info(1).locator;
547 l_inventory_item_rec.lot_number := p_inventory_item_info(1).lot_number;
548 l_inventory_item_rec.serial_number := p_inventory_item_info(1).serial_number;
549 l_inventory_item_rec.quantity := p_inventory_item_info(1).quantity;
550 l_inventory_item_tbl(1) := l_inventory_item_rec;
551 END IF; /* Added for bug#5841713 End */
552
553 end if ;
554
555
556 eam_completion.complete_work_order_generic(
557 x_wip_entity_id => p_wip_entity_id,
558 x_rebuild_jobs => l_rebuild_jobs,
559 x_transaction_type => p_transaction_type,
560 x_transaction_date => p_transaction_date,
561 x_user_id => p_user_id,
562 x_request_id => p_request_id,
563 x_application_id => p_application_id,
564 x_program_id => p_program_id,
565 x_reconcil_code => p_reconciliation_code,
566 x_actual_start_date => l_actual_start_date,
567 x_actual_end_date => l_actual_end_date,
568 x_actual_duration => l_actual_duration,
569 x_inventory_item_info => l_inventory_item_tbl,
570 x_shutdown_start_date => l_shutdown_start_date,
571 x_shutdown_end_date => l_shutdown_end_date,
572 x_commit => p_commit,
573 errCode => l_errCode,
574 errMsg => l_errMsg,
575 /* Added for addressing bug#5477819 Start */
576 x_attribute_category => p_attributes_rec.p_attribute_category,
577 x_attribute1 => p_attributes_rec.p_attribute1,
578 x_attribute2 => p_attributes_rec.p_attribute2,
579 x_attribute3 => p_attributes_rec.p_attribute3,
580 x_attribute4 => p_attributes_rec.p_attribute4,
581 x_attribute5 => p_attributes_rec.p_attribute5,
582 x_attribute6 => p_attributes_rec.p_attribute6,
583 x_attribute7 => p_attributes_rec.p_attribute7,
584 x_attribute8 => p_attributes_rec.p_attribute8,
585 x_attribute9 => p_attributes_rec.p_attribute9,
586 x_attribute10 => p_attributes_rec.p_attribute10,
587 x_attribute11 => p_attributes_rec.p_attribute11,
588 x_attribute12 => p_attributes_rec.p_attribute12,
589 x_attribute13 => p_attributes_rec.p_attribute13,
590 x_attribute14 => p_attributes_rec.p_attribute14,
591 x_attribute15 => p_attributes_rec.p_attribute15
592 /* Added for addressing bug#5477819 End */
593 );
594 if l_errCode <> 0 then -- there was an error
595
596 -- dbms_output.put_line('caught error in call to eam_completion.complete_work_order');
597 -- dbms_output.put_line('errMsg: '||l_errMsg);
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 FND_MESSAGE.set_name('EAM', 'EAM_INTERNAL_ERROR');
600 end if;
601 end if;
602
603 if (x_return_status = FND_API.G_RET_STS_ERROR) then
604 FND_MSG_PUB.Add;
605 FND_MSG_PUB.Count_And_Get(
606 p_count => x_msg_count,
607 p_data => x_msg_data);
608 end if;
609 EXCEPTION
610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
611 -- dbms_output.put_line('unexpected error :'||FND_MSG_PUB.Get);
612 -- dbms_output.put_line('unexpected error :'||FND_MESSAGE.get);
613 ROLLBACK TO Complete_Work_Order_PVT;
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 FND_MSG_PUB.Count_And_Get(
616 p_count => x_msg_count,
617 p_data => x_msg_data);
618 WHEN NO_DATA_FOUND THEN
619 -- dbms_output.put_line('unexpected error :'||FND_MSG_PUB.Get);
620 -- dbms_output.put_line('unexpected error :'||FND_MESSAGE.get);
621 -- dbms_output.put_line('no data found');
622 ROLLBACK TO Complete_Work_Order_PVT;
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 FND_MSG_PUB.Count_And_Get(
625 p_count => x_msg_count,
626 p_data => x_msg_data);
627 WHEN OTHERS THEN
628 -- dbms_output.put_line('unexpected error :'||FND_MSG_PUB.Get);
629 -- dbms_output.put_line('unexpected error :'||FND_MESSAGE.get);
630 ROLLBACK TO Complete_Work_Order_PVT;
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
632 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
633 FND_MSG_PUB.Add_Exc_Msg(
634 G_PKG_NAME,
635 l_api_name
636 );
637 END IF;
638 FND_MSG_PUB.Count_And_Get(
639 p_count => x_msg_count,
640 p_data => x_msg_data);
641 END Complete_Work_Order;
642
643 procedure complete_operation(
644 p_api_version IN NUMBER :=1.0,
645 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
646 p_commit IN VARCHAR2 := fnd_api.g_false,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2,
650 p_wip_entity_id IN NUMBER,
651 p_operation_seq_num IN NUMBER,
652 p_transaction_date IN DATE := SYSDATE,
653 p_transaction_type IN NUMBER,
654 p_actual_start_date IN DATE := null,
655 p_actual_end_date IN DATE := null,
656 p_actual_duration IN NUMBER := null,
657 p_shutdown_start_date IN DATE := null,
658 p_shutdown_end_date IN DATE := null,
659 p_shutdown_duration IN NUMBER := null,
660 p_reconciliation_code IN NUMBER := null,
661 p_attribute_rec IN Attributes_Rec_Type
662 ) IS
663
664 l_api_name CONSTANT VARCHAR(30) := 'complete_operation';
665 l_api_version CONSTANT NUMBER := 1.0;
666 l_return_status VARCHAR2(250);
667 l_error_msg_code VARCHAR2(250);
668
669 l_err_stage VARCHAR2(250);
670 l_err_stack VARCHAR2(250);
671
672 l_db_status NUMBER;
673 l_db_last_update_date DATE;
674 l_tran_type NUMBER;
675 l_open_acct_per_date DATE;
676 l_transaction_date DATE;
677 l_actual_duration NUMBER;
678 l_actual_start_date DATE;
679 l_actual_end_date DATE;
680 l_shutdown_start_date DATE ;
681 l_shutdown_end_date DATE ;
682 l_shutdown_duration NUMBER;
683 l_err_code NUMBER;
684 l_err_msg VARCHAR2(1000);
685 l_operation_completed wip_operations.operation_completed%type;
686 l_shutdown_type wip_operations.shutdown_type%type;
687 l_transaction_type NUMBER;
688 l_department_id wip_operations.department_id%type;
689 l_description wip_operations.description%type;
690 l_quantity_completed wip_operations.quantity_completed%type;
691 l_first_unit_start_date wip_operations.first_unit_start_date%type;
692 l_last_unit_completion_date wip_operations.last_unit_completion_date%type;
693 l_created_by wip_operations.created_by%type;
694 l_creation_date wip_operations.creation_date%type;
695 l_last_updated_by wip_operations.last_updated_by%type;
696 l_last_update_login wip_operations.last_update_login%type;
697 l_last_update_date wip_operations.last_update_date%type;
698
699
700 BEGIN
701 SAVEPOINT Complete_Operation_PVT;
702
703 -- Check p_init_msg_list
704 IF FND_API.to_Boolean(p_init_msg_list) THEN
705 FND_MSG_PUB.initialize;
706 END IF;
707
708 x_return_status := FND_API.G_RET_STS_SUCCESS;
709
710 ----Begining of Transaction type and transaction date validation
711
712 if (p_transaction_date is not null) then
713
714 if p_transaction_date > sysdate then
715
716 FND_MESSAGE.set_name('EAM', 'EAM_TXN_LATER_THAN_TODAY');
717 FND_MSG_PUB.Add;
718 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_TXN_LATER_THAN_TODAY');
719 x_return_status := FND_API.G_RET_STS_ERROR;
720 end if;
721 end if;
722 -- dbms_output.put_line ('past txn type ...');
723
724
725
726 select
727 wo.department_id,
728 wo.operation_completed,
729 wo.shutdown_type,
730 wo.description,
731 wo.quantity_completed,
732 wo.first_unit_start_date,
733 wo.last_unit_completion_date,
734 wo.created_by,
735 wo.creation_date,
736 wo.last_updated_by,
737 wo.last_update_login,
738 wo.last_update_date
739 into
740 l_department_id,
741 l_operation_completed,
742 l_shutdown_type,
743 l_description,
744 l_quantity_completed,
745 l_first_unit_start_date,
746 l_last_unit_completion_date,
747 l_created_by,
748 l_creation_date,
749 l_last_updated_by,
750 l_last_update_login,
751 l_last_update_date
752 from
753 wip_operations wo
754 where
755 wip_entity_id = p_wip_entity_id and
756 operation_seq_num =p_operation_seq_num;
757
758
759 --Checking if the User wants to complete (transaction type =1) an already completed operation
760 -- or If the user wanted to uncomplete (transaction type =2) an already uncompleted Operation.
761 --Systen should return error.
762
763 if (p_operation_seq_num is not NULL) THEN
764
765 if (l_operation_completed='Y') and (p_transaction_type =1) then
766
767 FND_MESSAGE.set_name('EAM', 'EAM_INVALID_TXN_TYPE');
768 FND_MSG_PUB.Add;
769 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INVALID_TXN_TYPE') ;
770 x_return_status := FND_API.G_RET_STS_ERROR;
771
772 elsif ((l_operation_completed <> 'Y') and (p_transaction_type =2)) then
773
774 FND_MESSAGE.set_name('EAM', 'EAM_INVALID_TXN_TYPE');
775 FND_MSG_PUB.Add;
776 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INVALID_TXN_TYPE') ;
777 x_return_status := FND_API.G_RET_STS_ERROR;
778
779 end if;
780
781 end if;
782
783
784
785
786 IF (p_operation_seq_num is NULL) THEN
787 l_transaction_type := null;
788 ELSIF (l_operation_completed = 'Y') THEN
789 -- uncomplete --
790 l_transaction_type := 2;
791 ELSE
792 -- complete --
793 l_transaction_type := 1;
794 END IF;
795
796 ----End of Transaction type and transaction date validation
797
798
799
800 ---Begining of Actual start date, Actual End Date, Actual Duration Validation
801
802 l_actual_start_date := p_actual_start_date;
803 l_actual_end_date := p_actual_end_date;
804 l_actual_duration := p_actual_duration;
805 --------------------------------------------------------------------------------
806 --------------------------------------------------------------------
807
808
809 --------------------------------------------------------------------------------
810 if ((l_actual_start_date is not null) AND (l_actual_end_date is not null) ) then
811 l_actual_duration:=round(24*(l_actual_end_date-l_actual_start_date),3);
812 --l_actual_duration := round(24 * (
813 -- to_date(to_char(l_actual_end_date, 'DD-MON-YY HH24:MI:SS'),'DD-MON-YY HH24:MI:SS')
814 -- - to_date(to_char(l_actual_start_date, 'DD-MON-YY HH24:MI:SS'),'DD-MON-YY HH24:MI:SS')), 3);
815 if (p_actual_duration is not null) and (p_actual_duration <> l_actual_duration ) then
816 FND_MESSAGE.set_name('EAM', 'EAM_INCORRECT_DURATION');
817 FND_MSG_PUB.Add;
818 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INCORRECT_DURATION') ;
819 x_return_status := FND_API.G_RET_STS_ERROR;
820
821 end if;
822 elsif ((l_actual_duration is not null) and (l_actual_start_date is not null)) then
823 l_actual_end_date := ((p_actual_start_date) + (p_actual_duration / 24));
824
825
826 elsif (( l_actual_duration is not null) and (l_actual_end_date is not null)) then
827 l_actual_start_date := ( (l_actual_end_date) - (l_actual_duration / 24) );
828
829 end if;
830 ---------------------------------------------------------------------------------
831
832 if (l_actual_duration is not null) then
833 if l_actual_duration < 0 then
834 FND_MESSAGE.set_name('EAM', 'EAM_NEGATIVE_DURATION');
835 FND_MSG_PUB.Add;
836 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_NEGATIVE_DURATION') ;
837 x_return_status := FND_API.G_RET_STS_ERROR;
838
839 end if;
840 end if;
841
842
843
844 select nvl(min(period_start_date),sysdate+1)
845 into l_open_acct_per_date
846 from org_acct_periods
847 where organization_id = (select organization_id from wip_entities where wip_entity_id=p_wip_entity_id)
848 and open_flag = 'Y';
849
850 if (l_actual_start_date is not null) then
851
852 if l_actual_end_date > sysdate then
853 FND_MESSAGE.set_name('EAM', 'EAM_END_LATER_THAN_TODAY');
854 FND_MSG_PUB.Add;
855 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_END_LATER_THAN_TODAY ') ;
856 x_return_status := FND_API.G_RET_STS_ERROR;
857 end if;
858
859 if (l_actual_start_date < l_open_acct_per_date) then
860 FND_MESSAGE.set_name('EAM', 'EAM_MIN_OP_ACTUAL_START_DATE');
861 FND_MSG_PUB.Add;
862 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_MIN_OP_ACTUAL_START_DATE') ;
863 x_return_status := FND_API.G_RET_STS_ERROR;
864
865 end if;
866
867 end if;
868 -------------------------------------------------------------------------------
869 l_shutdown_duration:=p_shutdown_duration;
870 l_shutdown_start_date:= p_shutdown_start_date ;
871 l_shutdown_end_date:=p_shutdown_end_date ;
872
873
874
875
876 --dbms_output.put_line ('shutdown point');
877 --------------------------------------------------------------------------------
878
879 --------------------------------------------------------------------------------
880
881
882 --------------------------------------------------------------------------------
883 if ((l_shutdown_start_date is not null) AND (l_shutdown_end_date is not null) ) then
884 l_shutdown_duration:=round(24*( l_shutdown_end_date-l_shutdown_start_date),3);
885 -- l_shutdown_duration := round(24 * (
886 -- to_date(to_char(l_actual_end_date, 'DD-MON-YY HH24:MI:SS'),'DD-MON-YY HH24:MI:SS')
887 -- - to_date(to_char(l_actual_start_date, 'DD-MON-YY HH24:MI:SS'),'DD-MON-YY HH24:MI:SS')), 3);
888 if (p_shutdown_duration is not null) and (p_shutdown_duration <> l_shutdown_duration ) then
889 FND_MESSAGE.set_name('EAM', 'EAM_INCORRECT_SHUT_DUR');
890 FND_MSG_PUB.Add;
891 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_INCORRECT_SHUT_DUR') ;
892 x_return_status := FND_API.G_RET_STS_ERROR;
893
894 end if;
895 elsif ((l_shutdown_duration is not null) and (l_shutdown_start_date is not null)) then
896 l_shutdown_end_date := ((p_shutdown_start_date) + (p_shutdown_duration / 24));
897
898 elsif
899
900 (( l_shutdown_duration is not null) and (l_shutdown_end_date is not null)) then
901 l_shutdown_start_date := ( (l_shutdown_end_date) - (l_shutdown_duration / 24) );
902
903 end if;
904 ---------------------------------------------------------------------------------
905
906 if (l_shutdown_duration is not null) then
907 if l_shutdown_duration < 0 then
908 FND_MESSAGE.set_name('EAM', 'EAM_SHUTDOWN_DUR_NEGATIVE');
909 FND_MSG_PUB.Add;
910 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUTDOWN_DUR_NEGATIVE') ;
911 x_return_status := FND_API.G_RET_STS_ERROR;
912
913 end if;
914 end if;
915
916 if (l_shutdown_end_date is not null) then
917
918 if l_shutdown_end_date > sysdate then
919 FND_MESSAGE.set_name('EAM', 'EAM_SHUT_GREATER_SYSDATE');
920 FND_MSG_PUB.Add;
921 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUT_GREATER_SYSDATE ') ;
922 x_return_status := FND_API.G_RET_STS_ERROR;
923 end if;
924
925
926 end if;
927 -------------------------------------------------------------------------------
928
929
930
931 -- dbms_output.put_line ('p_shutdown_end_date > sysdate line 266');
932
933
934 if ((l_shutdown_start_date is not null) and (l_shutdown_end_date is not null)
935 and (l_shutdown_end_date < l_shutdown_start_date)) then
936 FND_MESSAGE.set_name('EAM', 'EAM_SHUT_END_BEFORE_START');
937 FND_MSG_PUB.Add;
938 eam_execution_jsp.add_message(p_app_short_name => 'EAM', p_msg_name => 'EAM_SHUT_END_BEFORE_START ') ;
939
940
941 x_return_status := FND_API.G_RET_STS_ERROR;
942
943 end if;
944
945 /*
946
947 dbms_output.put_line ('Work Order = '||p_wip_entity_id);
948 dbms_output.put_line ('Operation Seq Num = '||p_operation_seq_num);
949 dbms_output.put_line ('Transaction_type = '||l_transaction_type);
950 dbms_output.put_line ('Transaction Date = '||p_transaction_date);
951 dbms_output.put_line ('Actual Start Date = '||l_actual_start_date);
952 dbms_output.put_line ('Actual End Date = '||l_actual_end_date);
953 dbms_output.put_line ('Duration = '|| l_actual_duration);
954 dbms_output.put_line ('Shutdown start Date = '|| l_shutdown_start_date);
955 dbms_output.put_line ('Shutdown End Date = '||l_shutdown_end_date);
956 dbms_output.put_line ('Reconcilation Code = '||p_reconciliation_code);
957
958 */
959
960
961 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
962 eam_op_comp.op_comp(
963 x_err_code => l_err_code,
964 x_err_msg => l_err_msg,
965
966 p_wip_entity_id => p_wip_entity_id,
967 p_operation_seq_num => p_operation_seq_num,
968 p_transaction_type => l_transaction_type,
969 p_transaction_date => p_transaction_date,
970 p_actual_start_date => l_actual_start_date,
971 p_actual_end_date => l_actual_end_date,
972 p_actual_duration => l_actual_duration,
973 p_shutdown_start_date => l_shutdown_start_date,
974 p_shutdown_end_date => l_shutdown_end_date,
975 p_reconciliation_code => p_reconciliation_code,
976 p_attribute_category => p_attribute_rec.p_attribute_category,
977 p_attribute1 => p_attribute_rec.p_attribute1,
978 p_attribute2 => p_attribute_rec.p_attribute2,
979 p_attribute3 => p_attribute_rec.p_attribute3,
980 p_attribute4 => p_attribute_rec.p_attribute4,
981 p_attribute5 => p_attribute_rec.p_attribute5,
982 p_attribute6 => p_attribute_rec.p_attribute6,
983 p_attribute7 => p_attribute_rec.p_attribute7,
984 p_attribute8 => p_attribute_rec.p_attribute8,
985 p_attribute9 => p_attribute_rec.p_attribute9,
986 p_attribute10 => p_attribute_rec.p_attribute10,
987 p_attribute11 => p_attribute_rec.p_attribute11,
988 p_attribute12 => p_attribute_rec.p_attribute12,
989 p_attribute13 => p_attribute_rec.p_attribute13,
990 p_attribute14 => p_attribute_rec.p_attribute14,
991 p_attribute15 => p_attribute_rec.p_attribute15
992 );
993
994 -- call eam_op_comp.op_comp to
995 -- insert into eam_op_completion_txns, update wip_operations
996
997 if l_err_code = 1 then -- there was an error
998 x_return_status := FND_API.G_RET_STS_ERROR;
999 FND_MSG_PUB.Add;
1000 end if;
1001 end if;
1002
1003 if (x_return_status = FND_API.G_RET_STS_ERROR) then
1004 FND_MSG_PUB.Count_And_Get(
1005 p_count => x_msg_count,
1006 p_data => x_msg_data);
1007 end if;
1008 EXCEPTION
1009 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1010 ROLLBACK TO Complete_Operation_PVT;
1011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1012 FND_MSG_PUB.Count_And_Get(
1013 p_count => x_msg_count,
1014 p_data => x_msg_data);
1015 WHEN OTHERS THEN
1016 ROLLBACK TO Complete_Operation_PVT;
1017 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1018 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1019 FND_MSG_PUB.Add_Exc_Msg(
1020 G_PKG_NAME,
1021 l_api_name
1022 );
1023 END IF;
1024 FND_MSG_PUB.Count_And_Get(
1025 p_count => x_msg_count,
1026 p_data => x_msg_data);
1027
1028
1029
1030 END complete_operation;
1031
1032
1033 procedure SET_MANUAL_REB_FLAG(p_wip_entity_id IN NUMBER,
1034 p_organization_id IN NUMBER,
1035 p_manual_rebuild_flag IN VARCHAR2,
1036 x_return_status OUT NOCOPY VARCHAR2)
1037 IS
1038
1039 l_count NUMBER := 0;
1040
1041 BEGIN
1042
1043 -- Validate WIP Entity Id
1044 select count(*) into l_count from wip_discrete_jobs where
1045 wip_entity_id = p_wip_entity_id and
1046 organization_id = p_organization_id;
1047 if l_count <> 1 then
1048 x_return_status := 'E';
1049 return;
1050 end if;
1051
1052 -- Set Manual Rebuild Flag
1053 IF p_manual_rebuild_flag is not null then
1054 update wip_discrete_jobs set manual_rebuild_flag = p_manual_rebuild_flag
1055 where wip_entity_id = p_wip_entity_id and
1056 organization_id = p_organization_id;
1057 END IF;
1058
1059 x_return_status := 'S';
1060
1061 return;
1062
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 x_return_status := 'E';
1066
1067 END SET_MANUAL_REB_FLAG;
1068
1069
1070 procedure SET_OWNING_DEPARTMENT(p_wip_entity_id IN NUMBER,
1071 p_organization_id IN NUMBER,
1072 p_owning_department IN NUMBER,
1073 x_return_status OUT NOCOPY VARCHAR2)
1074
1075 IS
1076
1077 l_count NUMBER;
1078
1079 BEGIN
1080
1081 -- Validate WIP Entity Id
1082 select count(*) into l_count from wip_discrete_jobs where
1083 wip_entity_id = p_wip_entity_id and
1084 organization_id = p_organization_id;
1085 if l_count <> 1 then
1086 x_return_status := 'E';
1087 return;
1088 end if;
1089
1090 -- Validate owning department
1091 select count(*) into l_count from bom_departments where
1092 department_id = p_owning_department
1093 and organization_id = p_organization_id;
1094 if l_count <> 1 then
1095 x_return_status := 'E';
1096 return;
1097 end if;
1098
1099 -- Set Owning Department
1100 IF p_owning_department is not null then
1101 update wip_discrete_jobs set owning_department = p_owning_department
1102 where wip_entity_id = p_wip_entity_id and
1103 organization_id = p_organization_id;
1104 END IF;
1105
1106 x_return_status := 'S';
1107
1108 return;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 x_return_status := 'E';
1113
1114 END SET_OWNING_DEPARTMENT;
1115
1116
1117 /*********************************************************************
1118 * Procedure : Update_EWOD
1119 * Parameters IN : organization Id
1120 * group_id
1121 * user_defined_status_id
1122 * Parameters OUT NOCOPY:
1123 * errbuf error messages
1124 * retcode return status. 0 for success, 1 for warning and 2 for error.
1125 * Purpose : Procedure will update the database with the user_defined_status_id passed.
1126 * This procedure was added for a WIP bug 6718091
1127 ***********************************************************************/
1128
1129 PROCEDURE Update_EWOD
1130 (p_group_id IN NUMBER,
1131 p_organization_id IN NUMBER,
1132 p_new_status IN NUMBER,
1133 ERRBUF OUT NOCOPY VARCHAR2 ,
1134 RETCODE OUT NOCOPY VARCHAR2
1135 )
1136 IS
1137
1138 BEGIN
1139 update eam_work_order_details
1140 SET user_defined_status_id = p_new_status
1141 , program_update_date = SYSDATE
1142 , last_update_date = SYSDATE
1143 , last_updated_by = FND_GLOBAL.user_id
1144 , last_update_login = FND_GLOBAL.login_id
1145 WHERE organization_id = p_organization_id
1146 AND WIP_ENTITY_ID IN (SELECT wdct.WIP_ENTITY_ID
1147 FROM WIP_DJ_CLOSE_TEMP wdct, wip_discrete_jobs wdj
1148 WHERE wdct.ORGANIZATION_ID = p_organization_id
1149 AND wdct.GROUP_ID = p_group_id
1150 and wdj.wip_entity_id = wdct.WIP_ENTITY_ID
1151 and wdj.status_type = WIP_CONSTANTS.PEND_CLOSE);
1152
1153
1154 EXCEPTION
1155 WHEN others THEN
1156 retcode := 2; -- error
1157 errbuf := SQLERRM;
1158
1159 END Update_EWOD;
1160
1161 /*********************************************************************
1162 * Procedure : RAISE_WORKFLOW_STATUS_PEND_CLS
1163 * Parameters IN : group_id
1164 * user_defined_status_id
1165 * Parameters OUT NOCOPY:
1166 * errbuf error messages
1167 * retcode return status. 0 for success, 1 for warning and 2 for error.
1168 * Purpose : Procedure will update workflow status to pending close for all wip_entity_ids provided in the group_id.
1169 * This procedure was added for a WIP bug 6718091
1170 ***********************************************************************/
1171 PROCEDURE RAISE_WORKFLOW_STATUS_PEND_CLS
1172 (p_group_id IN NUMBER,
1173 p_new_status IN NUMBER,
1174 ERRBUF OUT NOCOPY VARCHAR2 ,
1175 RETCODE OUT NOCOPY VARCHAR2 )
1176
1177 IS
1178
1179 l_return_status VARCHAR2(1);
1180
1181 TYPE WORKORDER_REC IS RECORD
1182 (wip_entity_id NUMBER,
1183 organization_id NUMBER,
1184 wip_entity_name VARCHAR2(240),
1185 old_system_status NUMBER,
1186 old_wo_status NUMBER,
1187 workflow_type NUMBER
1188 );
1189
1190 CURSOR workorders
1191 IS
1192 SELECT wdj.wip_entity_id, we.wip_entity_name,
1193 wdj.status_type, wdj.organization_id, ewod.user_defined_status_id, ewod.workflow_type
1194 FROM wip_discrete_jobs wdj, wip_dj_close_temp wdct, eam_work_order_details ewod, wip_entities we
1195 WHERE wdct.group_id = p_group_id
1196 and wdct.wip_entity_id = wdj.wip_entity_id
1197 and wdct.organization_id = wdj.organization_id
1198 and wdj.wip_entity_id = ewod.wip_entity_id
1199 and wdj.organization_id = ewod.organization_id
1200 and wdj.wip_entity_id = we.wip_entity_id
1201 and wdj.organization_id = we.organization_id
1202 and we.entity_type = WIP_CONSTANTS.EAM;
1203
1204
1205 BEGIN
1206
1207 FOR l_workorders_rec IN workorders LOOP
1208
1209 EAM_JOBCLOSE_PRIV.RAISE_WORKFLOW_STATUS_CHANGED
1210 (p_wip_entity_id => l_workorders_rec.wip_entity_id,
1211 p_wip_entity_name => l_workorders_rec.wip_entity_name,
1212 p_organization_id => l_workorders_rec.organization_id,
1213 p_new_status => p_new_status,
1214 p_old_system_status => l_workorders_rec.status_type,
1215 p_old_wo_status => l_workorders_rec.user_defined_status_id,
1216 p_workflow_type => l_workorders_rec.workflow_type,
1217 x_return_status => l_return_status
1218 );
1219
1220 IF (NVL(l_return_status,'S') <> 'S') THEN
1221 RETCODE := 2;
1222 errbuf := SQLERRM;
1223 RETURN;
1224 END IF;
1225
1226 EAM_TEXT_UTIL.PROCESS_WO_EVENT
1227 (
1228 p_event => 'UPDATE',
1229 p_wip_entity_id =>l_workorders_rec.wip_entity_id,
1230 p_organization_id =>l_workorders_rec.organization_id,
1231 p_last_update_date => SYSDATE,
1232 p_last_updated_by => FND_GLOBAL.user_id,
1233 p_last_update_login =>FND_GLOBAL.login_id
1234 );
1235
1236
1237 END LOOP;
1238
1239 END RAISE_WORKFLOW_STATUS_PEND_CLS;
1240
1241 END EAM_WorkOrderTransactions_PUB;