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