1 PACKAGE BODY EAM_WORKORDER_UTIL_PKG as
2 /* $Header: EAMWOUTB.pls 120.24.12020000.3 2012/11/06 14:17:47 vpasupur ship $ */
3 g_supply_type VARCHAR2(30) := EAM_CONSTANTS.G_SUPPLY_TYPE;
4
5 PROCEDURE retrieve_asset_bom(
6 i_organization_id IN NUMBER,
7 i_asset_number IN VARCHAR2,
8 i_asset_group_id IN NUMBER,
9 p_context IN VARCHAR2, -- stocked inventory or non-stocked inventory
10 o_bom_table OUT NOCOPY t_bom_table,
11 o_error_code OUT NOCOPY NUMBER) IS
12
13 l_index NUMBER;
14 l_index1 NUMBER;
15 l_bill_sequence_id NUMBER;
16 l_phantom_bom t_bom_table;
17
18 CURSOR components(l_stock_flag VARCHAR2) IS
19 select bic.component_sequence_id,
20 bic.component_item_id,
21 msik.concatenated_segments component_item,
22 msik.description,
23 bic.component_quantity,
24 bic.component_yield_factor component_yield,
25 msik.primary_uom_code uom,
26 bic.wip_supply_type,
27 lu.meaning wip_supply_type_disp
28 from bom_inventory_components bic,
29 mtl_system_items_kfv msik,
30 mfg_lookups lu
31 where bic.bill_sequence_id = l_bill_sequence_id
32 and bic.effectivity_date <= sysdate
33 and (bic.disable_date >= sysdate or
34 bic.disable_date is null)
35 and i_asset_number >= bic.from_end_item_unit_number
36 and (i_asset_number <= bic.to_end_item_unit_number or
37 bic.to_end_item_unit_number is null)
38 and msik.organization_id = i_organization_id
39 and msik.inventory_item_id = bic.component_item_id
40 and lu.lookup_type(+) = g_supply_type
41 and lu.lookup_code(+) = bic.wip_supply_type
42 --fix for 3371471.added following condition to fetch only stockable items
43 and msik.stock_enabled_flag= l_stock_flag
44 order by component_sequence_id;
45
46 /* Bug#3013574: If it is for rebuildable item
47 then the no check on BOM_INVENTORY_COMPONENTS.from_end_item_unit_number
48 and BOM_INVENTORY_COMPONENTS.to_end_item_unit_number is required */
49
50 CURSOR C_REBUILD_COMPONENTS( l_stock_flag VARCHAR2) IS
51 SELECT
52 bic.component_sequence_id,
53 bic.component_item_id,
54 msik.concatenated_segments component_item,
55 msik.description,
56 bic.component_quantity,
57 bic.component_yield_factor component_yield,
58 msik.primary_uom_code uom,
59 bic.wip_supply_type,
60 lu.meaning wip_supply_type_disp
61 FROM
62 bom_inventory_components bic,
63 mtl_system_items_kfv msik,
64 mfg_lookups lu
65 WHERE
66 bic.bill_sequence_id = l_bill_sequence_id
67 and bic.effectivity_date <= sysdate
68 and (bic.disable_date >= sysdate or
69 bic.disable_date is null)
70 and msik.organization_id = i_organization_id
71 and msik.inventory_item_id = bic.component_item_id
72 and lu.lookup_type(+) = g_supply_type
73 and lu.lookup_code(+) = bic.wip_supply_type
74 --fix for 3371471.added following condition to fetch only stockable items
75 and msik.stock_enabled_flag= l_stock_flag
76 ORDER BY component_sequence_id;
77
78 l_record components%ROWTYPE;
79
80 /* BUG#3013574 */
81 l_eam_item_type NUMBER;
82 l_record_rebuild C_REBUILD_COMPONENTS%ROWTYPE;
83 l_stock_enabled_flag VARCHAR2(1) ;
84
85 PROCEDURE explode_phantom_bom(
86 i_phantom_item_id IN NUMBER,
87 i_phantom_quantity IN NUMBER,
88 i_phantom_yield IN NUMBER,
89 o_phantom_bom OUT NOCOPY t_bom_table) IS
90 CURSOR phantom_comp IS
91 select bic.component_sequence_id,
92 bic.component_item_id,
93 msik.concatenated_segments component_item,
94 msik.description,
95 bic.component_quantity,
96 bic.component_yield_factor component_yield,
97 msik.primary_uom_code uom,
98 bic.wip_supply_type,
99 lu.meaning wip_supply_type_disp
100 from bom_inventory_components bic,
101 bom_bill_of_materials bbom,
102 mtl_system_items_kfv msik,
103 mfg_lookups lu
104 where bbom.assembly_item_id = i_phantom_item_id
105 and bbom.organization_id = i_organization_id
106 and bbom.alternate_bom_designator is null
107 and bic.bill_sequence_id = bbom.common_bill_sequence_id
108 and bic.effectivity_date <= sysdate
109 and (bic.disable_date >= sysdate or
110 bic.disable_date is null)
111 and i_asset_number >= bic.from_end_item_unit_number
112 and (i_asset_number <= bic.to_end_item_unit_number or
113 bic.to_end_item_unit_number is null)
114 and msik.organization_id = i_organization_id
115 and msik.inventory_item_id = bic.component_item_id
116 and lu.lookup_type(+) = g_supply_type
117 and lu.lookup_code(+) = bic.wip_supply_type
118 order by component_sequence_id;
119
120
121 BEGIN
122 -- Clear
123 l_phantom_bom.delete;
124 -- Select
125 /****************************************
126 * UNFINISHED!!!! *
127 ****************************************/
128 /*** We don't need phantom actually ***/
129
130
131 END explode_phantom_bom;
132
133
134 BEGIN
135 -- Clear
136 o_bom_table.delete;
137 -- Get the Bill-Sequence
138 begin
139 select common_bill_sequence_id
140 into l_bill_sequence_id
141 from bom_bill_of_materials
142 where organization_id = i_organization_id
143 and assembly_item_id = i_asset_group_id
144 and alternate_bom_designator is null;
145 exception
146 when NO_DATA_FOUND then /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
147 return;
148 end;
149
150 IF ( p_context = 'REQUIREMENTS') THEN
151 l_stock_enabled_flag := 'Y' ;
152 ELSIF ( p_context = 'DIRECT_ITEMS') THEN
153 l_stock_enabled_flag := 'N' ;
154 END IF ;
155
156 -- Retrieve the Asset BOM
157 l_index := 0;
158
159 /* Bug#3013574: Fetch rebuild/Asset BOM depending upon the type of workorder
160 for which it is being summoned. */
161 SELECT
162 NVL(msi.eam_item_type,-1) INTO l_eam_item_type
163 FROM
164 mtl_system_items_b msi
165 WHERE
166 msi.inventory_item_id = i_asset_group_id
167 and organization_id = i_organization_id;
168
169 /* Bug#3013574: If the i_asset_group_id being passed corresponds to Asset Group
170 continue doing the work as is being done. If it is for rebuildable item
171 then the no check on BOM_INVENTORY_COMPONENTS.from_end_item_unit_number
172 and BOM_INVENTORY_COMPONENTS.to_end_item_unit_number is required */
173
174 IF (l_eam_item_type = 1) THEN
175 OPEN components( l_stock_enabled_flag );
176 LOOP
177 fetch components into l_record;
178 exit when components%notfound;
179 if l_record.wip_supply_type = 6 then -- phantom
180 null;
181 else
182 o_bom_table(l_index).component_sequence_id := l_record.component_sequence_id;
183 o_bom_table(l_index).component_item_id := l_record.component_item_id;
184 o_bom_table(l_index).component_item := l_record.component_item;
185 o_bom_table(l_index).description := l_record.description;
186 o_bom_table(l_index).component_quantity := l_record.component_quantity;
187 o_bom_table(l_index).component_yield := l_record.component_yield;
188 o_bom_table(l_index).uom := l_record.uom;
189 o_bom_table(l_index).wip_supply_type := l_record.wip_supply_type;
190 o_bom_table(l_index).wip_supply_type_disp := l_record.wip_supply_type_disp;
191 l_index := l_index+1;
192 end if;
193 END LOOP;
194 CLOSE components;
195 ELSIF (l_eam_item_type = 3) THEN
196 OPEN C_REBUILD_COMPONENTS ( l_stock_enabled_flag );
197 LOOP
198 FETCH C_REBUILD_COMPONENTS INTO l_record_rebuild;
199 EXIT WHEN C_REBUILD_COMPONENTS%NOTFOUND;
200 IF l_record_rebuild.wip_supply_type = 6 THEN -- phantom
201 null;
202 ELSE
203 o_bom_table(l_index).component_sequence_id := l_record_rebuild.component_sequence_id;
204 o_bom_table(l_index).component_item_id := l_record_rebuild.component_item_id;
205 o_bom_table(l_index).component_item := l_record_rebuild.component_item;
206 o_bom_table(l_index).description := l_record_rebuild.description;
207 o_bom_table(l_index).component_quantity := l_record_rebuild.component_quantity;
208 o_bom_table(l_index).component_yield := l_record_rebuild.component_yield;
209 o_bom_table(l_index).uom := l_record_rebuild.uom;
210 o_bom_table(l_index).wip_supply_type := l_record_rebuild.wip_supply_type;
211 o_bom_table(l_index).wip_supply_type_disp := l_record_rebuild.wip_supply_type_disp;
212 l_index := l_index+1;
213 END IF;
214 END LOOP;
215 CLOSE C_REBUILD_COMPONENTS;
216 END IF;
217 END retrieve_asset_bom;
218
219
220 PROCEDURE copy_to_bom(
221 i_organization_id IN NUMBER,
222 i_organization_code IN VARCHAR2,
223 i_asset_number IN VARCHAR2,
224 i_asset_group_id IN NUMBER,
225 i_component_table IN t_component_table,
226 o_error_code OUT NOCOPY NUMBER) IS
227
228 -- PRAGMA AUTONOMOUS_TRANSACTION;
229
230 l_index1 NUMBER;
231 l_index2 NUMBER;
232
233 l_assembly_item_name VARCHAR2(81);
234
235 l_bom_header_rec BOM_BO_PUB.bom_head_rec_type;
236 l_bom_component_tbl BOM_BO_PUB.bom_comps_tbl_type;
237 o_bom_header_rec BOM_BO_PUB.bom_head_rec_type;
238 o_bom_revision_tbl BOM_BO_PUB.bom_revision_tbl_type;
239 o_bom_component_tbl BOM_BO_PUB.bom_comps_tbl_type;
240 o_bom_ref_designator_tbl BOM_BO_PUB.bom_ref_designator_tbl_type;
241 o_bom_sub_component_tbl BOM_BO_PUB.bom_sub_component_tbl_type;
242 o_return_status varchar2(100);
243 o_msg_count number;
244
245 l_error_msg Error_Handler.Error_tbl_type;
246 l_tmp number;
247 l_seq_increment NUMBER;
248 l_start_item_seq_number NUMBER;
249
250 -- Check whether the given component already in the bom of given assembly
251 FUNCTION comp_exists(i_organization_id IN NUMBER,
252 i_assembly_item_id IN NUMBER,
253 i_serial_number IN VARCHAR2,
254 i_component_item_id IN NUMBER)
255 RETURN BOOLEAN IS
256 l_component_item_id NUMBER;
257 BEGIN
258 select bic.component_item_id
259 into l_component_item_id
260 from bom_bill_of_materials bbom,
261 bom_inventory_components bic
262 where bbom.assembly_item_id = i_assembly_item_id
263 and bbom.organization_id = i_organization_id
264 and bbom.alternate_bom_designator is null
265 and bbom.common_bill_sequence_id = bic.bill_sequence_id
266 and bic.component_item_id = i_component_item_id
267 and bic.disable_date is null /*consider only enabled components, added for #6072910*/
268 and i_serial_number >= bic.from_end_item_unit_number
269 and (i_serial_number <= bic.to_end_item_unit_number or
270 bic.to_end_item_unit_number is null);
271 RETURN TRUE;
272 EXCEPTION
273 WHEN NO_DATA_FOUND THEN /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
274 RETURN FALSE;
275 END comp_exists;
276
277 -- Bug# 3844669 : Function to compute item sequence number
278 FUNCTION get_item_sequence_num(i_organization_id IN NUMBER,
279 i_assembly_item_id IN NUMBER)
280 RETURN NUMBER IS
281 l_bill_sequence_id NUMBER;
282 l_item_sequence_num NUMBER;
283 l_seq_increment NUMBER;
284 BEGIN
285 l_item_sequence_num := 100;
286
287 select common_bill_sequence_id
288 into l_bill_sequence_id
289 from bom_bill_of_materials
290 where organization_id = i_organization_id
291 and assembly_item_id = i_assembly_item_id
292 and alternate_bom_designator is null;
293
294 select nvl(max(item_num),100)
295 into l_item_sequence_num
296 from bom_inventory_components
297 where bill_sequence_id=l_bill_sequence_id;
298
299 l_seq_increment := to_number(nvl(fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT'),'10'));
300 l_item_sequence_num := l_item_sequence_num + l_seq_increment;
301
302 RETURN l_item_sequence_num;
303 EXCEPTION
304 WHEN OTHERS THEN
305 RETURN l_item_sequence_num;
306 END get_item_sequence_num;
307
308 BEGIN
309 o_error_code := 0;
310 -- Return if no Components
311 if i_component_table.COUNT <= 0 then
312 RETURN;
313 end if;
314 -- Bug# 3844669 : Added code to compute current starting item sequence number in assembly item BOM.
315 l_start_item_seq_number := get_item_sequence_num(i_organization_id, i_asset_group_id);
316 l_seq_increment := to_number(nvl(fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT'),'10'));
317 -- Construct the Components Table
318 l_index1 := i_component_table.FIRST;
319 l_index2 := 1;
320 select concatenated_segments
321 into l_assembly_item_name
322 from mtl_system_items_kfv
323 where organization_id = i_organization_id
324 and inventory_item_id = i_asset_group_id;
325 loop
326 if not comp_exists(i_organization_id, i_asset_group_id, i_asset_number,
327 i_component_table(l_index1).component_item_id) then
328 -- Add this Entry
329 -- Set the context
330 l_bom_component_tbl(l_index2).assembly_item_name := l_assembly_item_name;
331 l_bom_component_tbl(l_index2).organization_code := i_organization_code;
332 l_bom_component_tbl(l_index2).alternate_bom_code := null;
333 l_bom_component_tbl(l_index2).transaction_type := 'CREATE';
334 -- Copy the Data
335 l_bom_component_tbl(l_index2).component_item_name := i_component_table(l_index1).component_item;
336 l_bom_component_tbl(l_index2).start_effective_date := i_component_table(l_index1).start_effective_date;
337 l_bom_component_tbl(l_index2).quantity_per_assembly := i_component_table(l_index1).quantity_per_assembly;
338 l_bom_component_tbl(l_index2).wip_supply_type := i_component_table(l_index1).wip_supply_type;
339 l_bom_component_tbl(l_index2).supply_subinventory := i_component_table(l_index1).supply_subinventory;
340 l_bom_component_tbl(l_index2).location_name := i_component_table(l_index1).supply_locator_name;
341 l_bom_component_tbl(l_index2).from_end_item_unit_number := i_asset_number;
342 l_bom_component_tbl(l_index2).to_end_item_unit_number := i_asset_number;
343 -- Default In Required Fields. All hardcoded values are as per design. Pls refer to eTRM for object: BOM_INVENTORY_COMPONENTS to view description of below hardcoded columns.
344 l_bom_component_tbl(l_index2).operation_sequence_number := 1;
345 -- Modified for bug# 3844669.
346 l_bom_component_tbl(l_index2).item_sequence_number := l_start_item_seq_number + l_seq_increment*(l_index2 - 1);
347 l_bom_component_tbl(l_index2).projected_yield := 1;
348 l_bom_component_tbl(l_index2).planning_percent := 100;
349 l_bom_component_tbl(l_index2).quantity_related := 2;
350 l_bom_component_tbl(l_index2).include_in_cost_rollup := 1;
351 l_bom_component_tbl(l_index2).check_atp := 2;
352 l_bom_component_tbl(l_index2).so_basis := 2;
353 l_bom_component_tbl(l_index2).optional := 2;
354 l_bom_component_tbl(l_index2).mutually_exclusive := 2;
355 l_bom_component_tbl(l_index2).shipping_allowed := null;
356 l_bom_component_tbl(l_index2).required_to_ship := 2;
357 l_bom_component_tbl(l_index2).required_for_revenue := 2;
358 l_bom_component_tbl(l_index2).include_on_ship_docs := 2;
359 l_index2 := l_index2 + 1;
360 else
361 o_error_code := 1;
362 end if;
363 exit when l_index1 = i_component_table.LAST;
364 l_index1 := i_component_table.NEXT(l_index1);
365 end loop;
366
367 -- Construct header
368 l_bom_header_rec.organization_code := i_organization_code;
369 l_bom_header_rec.assembly_item_name := l_assembly_item_name;
370 l_bom_header_rec.alternate_bom_code := null;
371 l_bom_header_rec.transaction_type := 'SYNC';
372 l_bom_header_rec.assembly_type := 1;
373
374 -- Bug# 3844669: Initializes BOM Message List and associated variables.
375 Error_Handler.Initialize;
376
377 -- Call the BOM API
378 BOM_BO_PUB.Process_Bom(
379 p_bom_header_rec => l_bom_header_rec,
380 p_bom_component_tbl => l_bom_component_tbl,
381 x_bom_header_rec => o_bom_header_rec,
382 x_bom_revision_tbl => o_bom_revision_tbl,
383 x_bom_component_tbl => o_bom_component_tbl,
384 x_bom_ref_designator_tbl => o_bom_ref_designator_tbl,
385 x_bom_sub_component_tbl => o_bom_sub_component_tbl,
386 x_return_status => o_return_status,
387 x_msg_count => o_msg_count);
388
389 if o_return_status = 'S' then
390 commit;
391 else
392 rollback;
393 o_error_code := 2;
394 end if;
395 EXCEPTION
396 WHEN NO_DATA_FOUND THEN /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
397 o_error_code := 2;
398 END copy_to_bom;
399
400
401 PROCEDURE adjust_resources(i_wip_entity_id IN NUMBER) IS
402
403 CURSOR all_operations IS
404 select operation_seq_num
405 from wip_operations
406 where wip_entity_id = i_wip_entity_id;
407
408 l_wo_start_date DATE;
409 l_wo_completion_date DATE;
410 l_op_seq_num NUMBER;
411 l_op_start_date DATE;
412 l_op_completion_date DATE;
413
414 BEGIN
415 -- Update Operations
416 OPEN all_operations;
417 LOOP
418 -- Next Operation
419 FETCH all_operations INTO l_op_seq_num;
420 EXIT WHEN all_operations%NOTFOUND;
421 -- Check Resources
422 BEGIN
423 -- Get the New Date Range
424 select min(start_date), max(completion_date)
425 into l_op_start_date, l_op_completion_date
426 from wip_operation_resources
427 where wip_entity_id = i_wip_entity_id
428 and operation_seq_num = l_op_seq_num;
429 -- Update the Operation
430
431 if (l_op_start_date is not null) and (l_op_completion_date is not null) then -- Fix for operations with no resources
432 update wip_operations set
433 first_unit_start_date = l_op_start_date,
434 first_unit_completion_date = l_op_completion_date,
435 last_unit_start_date = l_op_start_date,
436 last_unit_completion_date = l_op_completion_date
437 where wip_entity_id = i_wip_entity_id
438 and operation_seq_num = l_op_seq_num;
439 end if;
440 EXCEPTION
441 WHEN NO_DATA_FOUND THEN /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
442 NULL;
443 END;
444 END LOOP;
445 CLOSE all_operations;
446 -- Update Work Order
447 BEGIN
448 select min(first_unit_start_date), max(last_unit_completion_date)
449 into l_wo_start_date, l_wo_completion_date
450 from wip_operations
451 where wip_entity_id = i_wip_entity_id;
452
453 if (l_wo_start_date is not null and l_wo_completion_date is not null) then
454 update wip_discrete_jobs set
455 scheduled_start_date = l_wo_start_date,
456 scheduled_completion_date = l_wo_completion_date
457 where wip_entity_id = i_wip_entity_id;
458 end if;
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
461 NULL;
462 END;
463 END adjust_resources;
464
465
466 PROCEDURE adjust_operations(
467 i_wip_entity_id IN NUMBER,
468 i_operation_table IN t_optime_table) IS
469
470 CURSOR all_operations IS
471 select operation_seq_num
472 from wip_operations
473 where wip_entity_id = i_wip_entity_id;
474
475 l_index NUMBER;
476
477 l_wo_start_date DATE;
478 l_wo_completion_date DATE;
479 l_op_seq_num NUMBER;
480
481 l_op number;
482 l_shift number;
483 BEGIN
484 /*
485 | insert into lmtmp (wip_entity_id, op_count)values(
486 | i_wip_entity_id, 0);--i_operation_table.count);
487 | l_index := i_operation_table.first;
488 | loop
489 | insert into lmtmp(wip_entity_id, op_count)values(
490 | -1, l_index);
491 | l_op := i_operation_table(l_index).operation_seq_num;
492 | l_shift := i_operation_table(l_index).time_shift;
493 | insert into lmtmp (wip_entity_id, op_count)values(
494 | l_op, l_shift);
495 | exit when l_index = i_operation_table.last;
496 | l_index := i_operation_table.next(l_index);
497 | end loop;
498 | commit;
499 */
500 -- Work Order
501 BEGIN
502 select min(first_unit_start_date), max(last_unit_completion_date)
503 into l_wo_start_date, l_wo_completion_date
504 from wip_operations
505 where wip_entity_id = i_wip_entity_id;
506
507 update wip_discrete_jobs set
508 scheduled_start_date = l_wo_start_date,
509 scheduled_completion_date = l_wo_completion_date
510 where wip_entity_id = i_wip_entity_id;
511 EXCEPTION
512 WHEN NO_DATA_FOUND THEN /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
513 NULL;
514 END;
515 -- Resources
516 if i_operation_table is null or
517 i_operation_table.count <= 0 then
518 return;
519 end if;
520 l_index := i_operation_table.FIRST;
521 LOOP
522 update wip_operation_resources set
523 start_date = start_date, -- + i_operation_table(l_index).time_shift,
524 completion_date = completion_date --+ i_operation_table(l_index).time_shift
525 where wip_entity_id = i_wip_entity_id
526 and operation_seq_num = i_operation_table(l_index).operation_seq_num;
527 EXIT WHEN l_index = i_operation_table.LAST;
528 l_index := i_operation_table.NEXT(l_index);
529 END LOOP;
530 --*/ null;
531 END adjust_operations;
532
533
534 PROCEDURE adjust_workorder(
535 i_wip_entity_id IN NUMBER,
536 i_shift IN NUMBER) IS
537 BEGIN
538 -- Operations
539 update wip_operations set
540 first_unit_start_date = first_unit_start_date,
541 first_unit_completion_date = first_unit_completion_date,
542 last_unit_start_date = last_unit_start_date,
543 last_unit_completion_date = last_unit_completion_date
544 where wip_entity_id = i_wip_entity_id;
545 -- Resources
546 update wip_operation_resources set
547 start_date = start_date,
548 completion_date = completion_date
549 where wip_entity_id = i_wip_entity_id;
550 END adjust_workorder;
551
552
553 FUNCTION dependency_violated( i_wip_entity_id IN NUMBER)
554 RETURN BOOLEAN IS
555
556 CURSOR all_dependencies IS
557 select prior_operation, next_operation
558 from wip_operation_networks
559 where wip_entity_id = i_wip_entity_id;
560
561 l_from_op number;
562 l_to_op number;
563
564 l_start_date_from date;
565 l_end_date_from date;
566 l_start_date_to date;
567 l_end_date_to date;
568 BEGIN
569 OPEN all_dependencies;
570 LOOP
571 FETCH all_dependencies INTO l_from_op, l_to_op;
572 EXIT WHEN all_dependencies%NOTFOUND;
573 select first_unit_start_date, last_unit_completion_date
574 into l_start_date_from, l_end_date_from
575 from wip_operations
576 where wip_entity_id = i_wip_entity_id
577 and operation_seq_num = l_from_op;
578 select first_unit_start_date, last_unit_completion_date
579 into l_start_date_to, l_end_date_to
580 from wip_operations
581 where wip_entity_id = i_wip_entity_id
582 and operation_seq_num = l_to_op;
583 if l_start_date_to < l_end_date_from then
584 CLOSE all_dependencies;
585 RETURN TRUE;
586 end if;
587 END LOOP;
588 CLOSE all_dependencies;
589 RETURN FALSE;
590 EXCEPTION
591 WHEN NO_DATA_FOUND THEN /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
592 RETURN FALSE;
593 END dependency_violated;
594
595
596 /*Earlier, this function used to check whether there were multiple
597 *released work orders for the same maintenance object and asset
598 *activity. Now that restriction has been relaxed and multiple such
599 *released work orders are going to be allowed. Hence this function
600 *always returns 0 for success.
601
602 *This procedure is being still allowed
603 *to exist as we could add other checks later on.
604 */
605 FUNCTION check_released_onhold_allowed(
606 p_rebuild_flag in varchar2,
607 p_org_id in number,
608 p_item_id in number,
609 p_serial_number in varchar2,
610 p_activity_id in number) RETURN NUMBER IS
611 x_count number := null;
612 BEGIN
613
614 return 0;
615
616 END check_released_onhold_allowed;
617
618
619
620
621 /****************************************
622 * Get Responsibility Id *
623 ****************************************/
624 function menu_has_function
625 (
626 p_menu_id IN NUMBER
627 ,p_function_id IN NUMBER
628 ) RETURN NUMBER
629 IS
630
631 CURSOR COUNT_FUNC IS
632 select count(*) as func_count
633 from
634 ( select level as entry_level, function_id
635 from fnd_menu_entries me
636 start with menu_id = p_menu_id
637 connect by prior sub_menu_id = menu_id
638 ) e
639 where e.function_id = p_function_id;
640
641 l_count NUMBER;
642 BEGIN
643
644 open COUNT_FUNC;
645 fetch COUNT_FUNC into l_count;
646 if( COUNT_FUNC%NOTFOUND) then
647 l_count := 0;
648 end if;
649
650 return l_count;
651 END;
652
653 procedure get_resp_for_func
654 (
655 p_function_id IN NUMBER
656 ,p_user_id IN NUMBER
657 ,p_resp_app_id IN NUMBER
658 ,x_resp_id OUT NOCOPY NUMBER
659 ,x_out OUT NOCOPY VARCHAR2
660 ) IS
661
662 l_resp_key VARCHAR2(300);
663 l_resp_id NUMBER;
664 l_resp_app_id NUMBER;
665 l_menu_id NUMBER;
666 l_org_id VARCHAR2(240);
667
668 -- added Resposibility-User active date check (furg active date check) as per bug 3464424 fix */
669 CURSOR C_RESPS IS
670 select fr.responsibility_key, fr.responsibility_id, fr.application_id,
671 fr.menu_id
672 from fnd_user fu, fnd_responsibility fr, fnd_user_resp_groups furg
673 where fu.user_id = p_user_id
674 and furg.user_id = fu.user_id
675 and fr.responsibility_id = furg.responsibility_id
676 and fr.application_id = furg.responsibility_application_id
677 and nvl(fr.start_date, sysdate) <= sysdate
678 and nvl(fr.end_date, sysdate) >= sysdate
679 and nvl(furg.start_date, sysdate) <= sysdate
680 and nvl(furg.end_date, sysdate) >= sysdate
681 and nvl(p_resp_app_id, fr.application_id) = fr.application_id
682 and eam_workorder_util_pkg.menu_has_function(fr.menu_id, p_function_id) > 0
683 and NVL(l_org_id, fnd_profile.value_specific('ORG_ID', NULL,
684 fr.responsibility_id, furg.responsibility_application_id)) =
685 fnd_profile.value_specific('ORG_ID', NULL,
686 fr.responsibility_id, furg.responsibility_application_id)
687 and ROWNUM=1;
688
689 BEGIN
690 fnd_profile.get('ORG_ID', l_org_id);
691 open C_RESPS ;
692 fetch C_RESPS into l_resp_key, l_resp_id, l_resp_app_id, l_menu_id;
693 if( C_RESPS%NOTFOUND ) then
694 l_resp_id:=-1;
695 end if;
696 x_out := l_resp_key;
697 x_resp_id := l_resp_id;
698 END;
699
700 --Procedure will return responsibility id with valid organization access
701 procedure get_resp
702 (
703 p_function_id IN NUMBER
704 ,p_user_id IN NUMBER
705 ,p_resp_app_id IN NUMBER
706 ,x_resp_id OUT NOCOPY NUMBER
707 ,x_out OUT NOCOPY VARCHAR2
708 ) IS
709
710 l_resp_key VARCHAR2(300);
711 l_resp_id NUMBER;
712 l_resp_app_id NUMBER;
713 l_menu_id NUMBER;
714 l_organization_id NUMBER;
715
716 CURSOR C_RESPS IS
717 select fr.responsibility_key, fr.responsibility_id, fr.application_id,
718 fr.menu_id
719 from fnd_user fu, fnd_responsibility fr, fnd_user_resp_groups furg, org_access_view oav
720 where fu.user_id = p_user_id
721 and furg.user_id = fu.user_id
722 and fr.responsibility_id = furg.responsibility_id
723 and fr.application_id = furg.responsibility_application_id
724 and nvl(fr.start_date, sysdate) <= sysdate
725 and nvl(fr.end_date, sysdate) >= sysdate
726 and nvl(furg.start_date, sysdate) <= sysdate
727 and nvl(furg.end_date, sysdate) >= sysdate
728 and nvl(p_resp_app_id, fr.application_id) = fr.application_id
729 and eam_workorder_util_pkg.menu_has_function(fr.menu_id, p_function_id) > 0
730 and oav.responsibility_id=fr.responsibility_id
731 and oav.organization_id=l_organization_id
732 and oav.resp_application_id=426
733 and ROWNUM=1;
734
735 BEGIN
736 fnd_profile.get('MFG_ORGANIZATION_ID', l_organization_id);
737 open C_RESPS ;
738 fetch C_RESPS into l_resp_key, l_resp_id, l_resp_app_id, l_menu_id;
739 if( C_RESPS%NOTFOUND ) then
740 FND_MESSAGE.SET_NAME('EAM', 'EAM_ORG_ACCESS_VIOLATION');
741 APP_EXCEPTION.RAISE_EXCEPTION;
742 end if;
743 x_out := l_resp_key;
744 x_resp_id := l_resp_id;
745 END;
746
747 function get_ip_resp_id
748 (
749 p_user_id IN NUMBER
750 ) RETURN NUMBER IS
751
752 l_ip_function_id NUMBER;
753 l_ret NUMBER;
754 l_out VARCHAR2(300);
755 l_resp_id NUMBER;
756 l_ip_app_id NUMBER;
757 l_function_name fnd_form_functions.function_name%TYPE := 'POR_SSP_HOME';
758
759 BEGIN
760
761 BEGIN
762 select function_id
763 into l_ip_function_id
764 from fnd_form_functions
765 where function_name = l_function_name; -- Fix for Bug 3756518
766
767 -- IP use 178 (ICX) as app id
768 l_ip_app_id := 178;
769
770 get_resp_for_func(l_ip_function_id, p_user_id, null, l_resp_id,
771 l_out);
772
773 l_ret := l_resp_id;
774 EXCEPTION WHEN NO_DATA_FOUND THEN
775 l_ret := -2;
776 END;
777
778 return l_ret;
779 END;
780
781 --Function to return Responsibility Id for Maint. Super User.
782 --This returns -1 if resp. is not assigned to current user
783 FUNCTION Get_Eam_Resp_Id
784 RETURN NUMBER
785 IS
786 l_eam_function_id NUMBER;
787 l_function_name fnd_form_functions.function_name%TYPE := 'EAM_APPL_MENU_HOME';
788 l_resp_id NUMBER;
789 l_eam_app_id NUMBER;
790 l_resp_key VARCHAR2(300);
791 BEGIN
792
793 BEGIN
794 select function_id
795 into l_eam_function_id
796 from fnd_form_functions
797 where function_name = l_function_name;
798 EXCEPTION
799 WHEN NO_DATA_FOUND THEN
800 fnd_message.set_name('EAM','EAM_RESP_NOT_AVAILABLE'); --show message that resp. is not available
801 APP_EXCEPTION.RAISE_EXCEPTION;
802 END;
803
804 -- EAM uses 426 as application id
805 l_eam_app_id := 426;
806
807 get_resp_for_func(l_eam_function_id,FND_GLOBAL.user_id, null, l_resp_id, l_resp_key);
808
809 IF(l_resp_id = -1) THEN
810 fnd_message.set_name('EAM','EAM_RESP_NOT_AVAILABLE'); --show message that resp. is not available
811 APP_EXCEPTION.RAISE_EXCEPTION;
812 END IF;
813
814 RETURN l_resp_id;
815
816 END Get_Eam_Resp_Id;
817
818
819 FUNCTION Resource_Schedulable(X_Hour_UOM_Code VARCHAR2,
820 X_Unit_Of_Measure VARCHAR2) RETURN NUMBER IS
821 uom_class_code VARCHAR2(10);
822 hour_uom_class_code VARCHAR2(10);
823 conversion_exists NUMBER;
824 different_uom_class EXCEPTION;
825 uom_conversion_exists EXCEPTION;
826 no_uom_conversion EXCEPTION;
827
828 BEGIN
829 SELECT UOM_CLASS INTO hour_uom_class_code
830 FROM MTL_UNITS_OF_MEASURE
831 WHERE UOM_CODE = X_Hour_UOM_Code;
832
833 SELECT UOM_CLASS INTO uom_class_code
834 FROM MTL_UNITS_OF_MEASURE
835 WHERE UOM_CODE = X_Unit_Of_Measure;
836
837 IF hour_uom_class_code <> uom_class_code THEN
838 RAISE different_uom_class;
839 ELSE
840 SELECT COUNT(*) INTO conversion_exists
841 FROM MTL_UOM_CONVERSIONS muc1,
842 MTL_UOM_CONVERSIONS muc2
843 WHERE muc1.UOM_CLASS = uom_class_code
844 AND muc1.UOM_CODE = X_Unit_Of_Measure
845 AND muc1.inventory_item_id = 0
846 AND nvl(muc1.disable_date, sysdate +1) > sysdate
847 AND muc2.uom_code = X_Hour_Uom_Code
848 AND muc2.inventory_item_id = 0
849 AND muc2.uom_class = muc1.uom_class;
850 IF conversion_exists > 0 THEN
851 RAISE uom_conversion_exists;
852 ELSE
853 RAISE no_uom_conversion;
854 END IF;
855 END IF;
856
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 RETURN(0);
860 WHEN different_uom_class THEN
861 RETURN(0);
862 WHEN no_uom_conversion THEN
863 RETURN(0);
864 WHEN uom_conversion_exists THEN
865 RETURN(1);
866
867 END Resource_Schedulable;
868
869
870 PROCEDURE UNRELEASE(x_org_id IN NUMBER,
871 x_wip_id IN NUMBER,
872 x_rep_id IN NUMBER,
873 x_line_id IN NUMBER,
874 x_ent_type IN NUMBER) IS
875
876 ops_exist VARCHAR2(2);
877 charges_exist VARCHAR2(2);
878 po_req_exist VARCHAR2(20);
879 quantity_left NUMBER := 0;
880
881 charges_exist_1 varchar2(2) := '0';
882 charges_exist_2 varchar2(2) := '0';
883 charges_exist_3 varchar2(2) := '0';
884
885
886
887 BEGIN
888
889 /* Check for OSP */
890
891 IF (WIP_OSP.PO_REQ_EXISTS( p_wip_entity_id => x_wip_id
892 ,p_rep_sched_id => x_rep_id
893 ,p_organization_id => x_org_id
894 ,p_entity_type => x_ent_type ) = TRUE) THEN
895 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED_OPEN_PO');
896 APP_EXCEPTION.RAISE_EXCEPTION;
897 RETURN;
898 END IF;
899
900 /* End of OSP Check */
901
902 /* Check for Direct Items */
903
904 begin
905
906 /* select ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left
907 into quantity_left
908 from wip_eam_direct_items_v
909 where work_order_number = x_wip_id; */
910
911
912 SELECT
913 ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left into quantity_left
914 FROM
915 (
916 SELECT
917 rql.wip_entity_id,
918 rql.quantity quantity_ordered,
919 to_number(null) quantity_received
920 FROM po_requisition_lines_all rql,
921 po_requisition_headers_all rqh,
922 po_line_types plt
923 WHERE rql.requisition_header_id = rqh.requisition_header_id
924 AND rql.line_type_id = plt.line_type_id
925 AND upper(rqh.authorization_status) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
926 AND rql.line_location_id is NULL
927 AND upper(nvl(rql.cancel_flag, 'N')) <> 'Y'
928 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
929 AND rql.destination_type_code = 'SHOP FLOOR'
930 AND rql.wip_entity_id is not null
931 UNION
932 (
933 SELECT
934 pd.wip_entity_id,
935 sum(pd.quantity_ordered) quantity_ordered,
936 sum(pd.quantity_delivered) quantity_received
937 FROM po_line_types plt,
938 (
939 SELECT
940 pd1.wip_entity_id,
941 pd1.wip_operation_seq_num,
942 pd1.destination_organization_id,
943 pol.item_description,
944 pol.unit_price,
945 pol.quantity,
946 pd1.quantity_delivered,
947 pd1.quantity_ordered,
948 pd1.quantity_cancelled,
949 pol.po_line_id,
950 pol.po_header_id,
951 pd1.req_distribution_id,
952 pd1.line_location_id,
953 pol.line_type_id,
954 pd1.destination_type_code,
955 pol.cancel_flag,
956 pol.item_id,
957 pol.category_id ,
958 pd1.po_release_id,
959 pd1.amount_ordered,
960 pd1.amount_delivered
961 FROM po_lines_all pol,
962 po_distributions_all pd1
963 WHERE pol.po_line_id = pd1.po_line_id
964 )
965 pd
966 WHERE pd.line_type_id = plt.line_type_id
967 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
968 AND pd.destination_type_code = 'SHOP FLOOR'
969 AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
970 AND pd.wip_entity_id is not null
971 GROUP BY pd.wip_entity_id,
972 pd.amount_ordered,
973 pd.amount_delivered
974 )
975 )
976 WHERE wip_entity_id = x_wip_id ;
977
978
979 -- bug fix 2675869: removed following group by clause for performance
980 -- group by work_order_number;
981
982 if (quantity_left > 0) then
983 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED_OPEN_PO');
984 APP_EXCEPTION.RAISE_EXCEPTION;
985 /* Bug#3022963: The 'RETURN' was placed outside the quantity_left check causing
986 the procedure to return without making other checks */
987 RETURN;
988 END IF;
989 exception
990 WHEN NO_DATA_FOUND then -- Here the exception will not raise, hence commenting. fix for the bug 2551622
991 null;
992
993 end;
994
995 /* End of Check for Direct Items */
996
997 /* Check for Material and Resource Transactions */
998
999 begin
1000
1001 -- bug fix 2675869: removed 'distinct' from following query and added additonal ROWNUM<=1
1002 SELECT '1'
1003 into charges_exist_1
1004 FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
1005 WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
1006 AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
1007 AND DJ.WIP_ENTITY_ID = x_wip_id
1008 AND DJ.ORGANIZATION_ID = x_org_id
1009 AND (DJ.QUANTITY_COMPLETED <> 0
1010 OR DJ.QUANTITY_SCRAPPED <> 0
1011 OR WPB.TL_RESOURCE_IN <> 0
1012 OR WPB.TL_OVERHEAD_IN <> 0
1013 OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
1014 OR WPB.PL_MATERIAL_IN <> 0
1015 OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
1016 OR WPB.PL_RESOURCE_IN <> 0
1017 OR WPB.PL_OVERHEAD_IN <> 0
1018 OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
1019 OR WPB.TL_MATERIAL_OUT <> 0
1020 OR WPB.TL_RESOURCE_OUT <> 0
1021 OR WPB.TL_OVERHEAD_OUT <> 0
1022 OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
1023 OR WPB.PL_MATERIAL_OUT <> 0
1024 OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
1025 OR WPB.PL_RESOURCE_OUT <> 0
1026 OR WPB.PL_OVERHEAD_OUT <> 0
1027 OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0)
1028 AND ROWNUM <= 1;
1029
1030 if charges_exist_1 = '1' then
1031 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
1032 APP_EXCEPTION.RAISE_EXCEPTION;
1033 /* Bug#3022963: The 'RETURN' was placed outside the quantity_left check causing
1034 the procedure to return without making other checks */
1035 RETURN;
1036 end if;
1037 exception
1038 WHEN NO_DATA_FOUND then /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
1039 null;
1040 end;
1041
1042 begin
1043
1044 SELECT '1'
1045 into charges_exist_2
1046 FROM DUAL /*fix for 2414244 */
1047 WHERE EXISTS (SELECT '1'
1048 FROM WIP_REQUIREMENT_OPERATIONS
1049 WHERE ORGANIZATION_ID = x_org_id
1050 AND WIP_ENTITY_ID = x_wip_id
1051 AND QUANTITY_ISSUED <> 0)
1052 OR EXISTS (SELECT '1'
1053 FROM WIP_MOVE_TXN_INTERFACE
1054 WHERE ORGANIZATION_ID = x_org_id
1055 AND WIP_ENTITY_ID = x_wip_id)
1056 OR EXISTS (SELECT '1'
1057 FROM WIP_COST_TXN_INTERFACE
1058 WHERE ORGANIZATION_ID = x_org_id
1059 AND WIP_ENTITY_ID = x_wip_id);
1060
1061 if charges_exist_2 = '1' then
1062 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
1063 APP_EXCEPTION.RAISE_EXCEPTION;
1064 /* Bug#3022963: The 'RETURN' was placed outside the quantity_left check causing
1065 the procedure to return without making other checks */
1066 RETURN;
1067 end if;
1068 exception
1069 WHEN NO_DATA_FOUND then /* fix for the perf bug #2551622 */
1070 null;
1071 end;
1072
1073
1074 BEGIN
1075 -- Fix for Bug 3890165
1076
1077 SELECT '1'
1078 INTO charges_exist_3
1079 FROM dual
1080 WHERE EXISTS (SELECT '1'
1081 FROM mtl_material_transactions_temp
1082 WHERE organization_id = x_org_id
1083 AND transaction_source_type_id = 5
1084 AND transaction_source_id = x_wip_id);
1085 if charges_exist_3 = '1' then
1086 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
1087 APP_EXCEPTION.RAISE_EXCEPTION;
1088 /* Bug#3022963: The 'RETURN' was placed outside the quantity_left check causing
1089 the procedure to return without making other checks */
1090 RETURN;
1091 end if;
1092 EXCEPTION
1093 WHEN NO_DATA_FOUND then /* fix for the perf bug #2551622 */
1094 begin
1095 SELECT DISTINCT '1'
1096 into charges_exist_3
1097 FROM dual
1098 where EXISTS (SELECT '1'
1099 FROM WIP_OPERATION_RESOURCES
1100 WHERE ORGANIZATION_ID = x_org_id
1101 AND WIP_ENTITY_ID = x_wip_id
1102 AND APPLIED_RESOURCE_UNITS <> 0);
1103
1104 if charges_exist_3 = '1' then
1105 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
1106 APP_EXCEPTION.RAISE_EXCEPTION;
1107 /* Bug#3022963: The 'RETURN' was placed outside the quantity_left check causing
1108 the procedure to return without making other checks */
1109 RETURN;
1110 end if;
1111 exception
1112 WHEN NO_DATA_FOUND then /* fix for the perf bug #2551622 */
1113 null;
1114 end;
1115 END;
1116
1117 /* Bug#3022963: All 3 checks should be negative for the wok order to be
1118 put from released to unreleased */
1119 IF (charges_exist_1 = '0' and charges_exist_2 = '0' and charges_exist_3 = '0') THEN
1120
1121 UPDATE WIP_OPERATIONS
1122 SET QUANTITY_WAITING_TO_MOVE = 0,
1123 QUANTITY_SCRAPPED = 0,
1124 QUANTITY_REJECTED = 0,
1125 QUANTITY_IN_QUEUE = 0,
1126 QUANTITY_RUNNING = 0,
1127 QUANTITY_COMPLETED = 0
1128 WHERE WIP_ENTITY_ID = x_wip_id
1129 AND ORGANIZATION_ID = x_org_id;
1130 ELSE
1131 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
1132 APP_EXCEPTION.RAISE_EXCEPTION;
1133 RETURN;
1134 END IF;
1135
1136 /* End of Check for Material and Resource Transactions */
1137
1138 DELETE FROM wip_period_balances
1139 WHERE wip_entity_id = x_wip_id
1140 AND NVL(repetitive_schedule_id, -1) =
1141 NVL(x_rep_id, -1)
1142 AND organization_id = x_org_id;
1143
1144 -- Undo changes to WRO as a result of Overcompletion
1145 wip_overcompletion.undo_overcompletion
1146 ( p_org_id => x_org_id,
1147 p_wip_entity_id => x_wip_id,
1148 p_rep_id => x_rep_id);
1149
1150 END UNRELEASE;
1151
1152
1153
1154 procedure create_default_operation
1155 ( p_organization_id IN NUMBER
1156 ,p_wip_entity_id IN NUMBER
1157 ) IS
1158
1159 l_wip_entity_id NUMBER;
1160 l_organization_id NUMBER;
1161
1162 BEGIN
1163
1164 l_organization_id := p_organization_id;
1165 l_wip_entity_id := p_wip_entity_id;
1166
1167 WIP_EAM_UTILS.create_default_operation(p_organization_id => l_organization_id
1168 , p_wip_entity_id => l_wip_entity_id);
1169
1170
1171 END create_default_operation; -- dml
1172
1173 /* bug no 3349197 */
1174
1175 PROCEDURE CK_MATERIAL_ALLOC_ON_HOLD(x_org_id IN NUMBER,
1176 x_wip_id IN NUMBER,
1177 x_rep_id IN NUMBER,
1178 x_line_id IN NUMBER,
1179 x_ent_type IN NUMBER,
1180 x_return_status OUT NOCOPY VARCHAR2) IS
1181
1182 ops_exist VARCHAR2(2) := '0';
1183 charges_exist VARCHAR2(2) := '0';
1184 po_req_exist VARCHAR2(20);
1185 quantity_left NUMBER := 0;
1186
1187 charges_exist_1 varchar2(2) := '0';
1188 charges_exist_2 varchar2(2) := '0';
1189 charges_exist_3 varchar2(2) := '0';
1190
1191 BEGIN
1192
1193 /* Check for OSP */
1194
1195 IF (WIP_OSP.PO_REQ_EXISTS( p_wip_entity_id => x_wip_id
1196 ,p_rep_sched_id => x_rep_id
1197 ,p_organization_id => x_org_id
1198 ,p_entity_type => x_ent_type ) = TRUE) THEN
1199 ops_exist := '1';
1200 END IF;
1201
1202 /* End of OSP Check */
1203
1204 /* Check for Direct Items */
1205
1206 begin
1207 /* select ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left
1208 into quantity_left
1209 from wip_eam_direct_items_v
1210 where work_order_number = x_wip_id;
1211 */
1212
1213 SELECT
1214 ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left into quantity_left
1215 FROM
1216 (
1217 SELECT
1218 rql.wip_entity_id,
1219 rql.quantity quantity_ordered,
1220 to_number(null) quantity_received
1221 FROM po_requisition_lines_all rql,
1222 po_requisition_headers_all rqh,
1223 po_line_types plt
1224 WHERE rql.requisition_header_id = rqh.requisition_header_id
1225 AND rql.line_type_id = plt.line_type_id
1226 AND upper(rqh.authorization_status) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
1227 AND rql.line_location_id is NULL
1228 AND upper(nvl(rql.cancel_flag, 'N')) <> 'Y'
1229 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
1230 AND rql.destination_type_code = 'SHOP FLOOR'
1231 AND rql.wip_entity_id is not null
1232 UNION
1233 (
1234 SELECT
1235 pd.wip_entity_id,
1236 sum(pd.quantity_ordered) quantity_ordered,
1237 sum(pd.quantity_delivered) quantity_received
1238 FROM po_line_types plt,
1239 (
1240 SELECT
1241 pd1.wip_entity_id,
1242 pd1.wip_operation_seq_num,
1243 pd1.destination_organization_id,
1244 pol.item_description,
1245 pol.unit_price,
1246 pol.quantity,
1247 pd1.quantity_delivered,
1248 pd1.quantity_ordered,
1249 pd1.quantity_cancelled,
1250 pol.po_line_id,
1251 pol.po_header_id,
1252 pd1.req_distribution_id,
1253 pd1.line_location_id,
1254 pol.line_type_id,
1255 pd1.destination_type_code,
1256 pol.cancel_flag,
1257 pol.item_id,
1258 pol.category_id ,
1259 pd1.po_release_id,
1260 pd1.amount_ordered,
1261 pd1.amount_delivered
1262 FROM po_lines_all pol,
1263 po_distributions_all pd1
1264 WHERE pol.po_line_id = pd1.po_line_id
1265 )
1266 pd
1267 WHERE pd.line_type_id = plt.line_type_id
1268 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
1269 AND pd.destination_type_code = 'SHOP FLOOR'
1270 AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
1271 AND pd.wip_entity_id is not null
1272 GROUP BY pd.wip_entity_id,
1273 pd.amount_ordered,
1274 pd.amount_delivered
1275 )
1276 )
1277 WHERE wip_entity_id = x_wip_id;
1278
1279
1280 -- bug fix 2675869: removed following group by clause for performance
1281 -- group by work_order_number;
1282
1283 if (quantity_left > 0) then
1284 charges_exist := '1';
1285 END IF;
1286 exception
1287 WHEN NO_DATA_FOUND then -- Here the exception will not raise, hence commenting. fix for the bug 2551622
1288 null;
1289 end;
1290 /* End of Check for Direct Items */
1291
1292 /* Check for Material and Resource Transactions */
1293
1294 begin
1295 -- bug fix 2675869: removed 'distinct' from following query and added additonal ROWNUM<=1
1296 SELECT '1'
1297 into charges_exist_1
1298 FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
1299 WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
1300 AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
1301 AND DJ.WIP_ENTITY_ID = x_wip_id
1302 AND DJ.ORGANIZATION_ID = x_org_id
1303 AND (DJ.QUANTITY_COMPLETED <> 0
1304 OR DJ.QUANTITY_SCRAPPED <> 0
1305 OR WPB.TL_RESOURCE_IN <> 0
1306 OR WPB.TL_OVERHEAD_IN <> 0
1307 OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
1308 OR WPB.PL_MATERIAL_IN <> 0
1309 OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
1310 OR WPB.PL_RESOURCE_IN <> 0
1311 OR WPB.PL_OVERHEAD_IN <> 0
1312 OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
1313 OR WPB.TL_MATERIAL_OUT <> 0
1314 OR WPB.TL_RESOURCE_OUT <> 0
1315 OR WPB.TL_OVERHEAD_OUT <> 0
1316 OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
1317 OR WPB.PL_MATERIAL_OUT <> 0
1318 OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
1319 OR WPB.PL_RESOURCE_OUT <> 0
1320 OR WPB.PL_OVERHEAD_OUT <> 0
1321 OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0)
1322 AND ROWNUM <= 1;
1323 exception
1324 WHEN NO_DATA_FOUND then /* Here the exception is changed from OTHERS to NO_DATA_FOUND for the bug 2551622 */
1325 null;
1326 end;
1327
1328 begin
1329 SELECT '1'
1330 into charges_exist_2
1331 FROM DUAL /*fix for 2414244 */
1332 WHERE EXISTS (SELECT '1'
1333 FROM WIP_REQUIREMENT_OPERATIONS
1334 WHERE ORGANIZATION_ID = x_org_id
1335 AND WIP_ENTITY_ID = x_wip_id
1336 AND QUANTITY_ISSUED <> 0)
1337 OR EXISTS (SELECT '1'
1338 FROM WIP_MOVE_TXN_INTERFACE
1339 WHERE ORGANIZATION_ID = x_org_id
1340 AND WIP_ENTITY_ID = x_wip_id)
1341 OR EXISTS (SELECT '1'
1342 FROM WIP_COST_TXN_INTERFACE
1343 WHERE ORGANIZATION_ID = x_org_id
1344 AND WIP_ENTITY_ID = x_wip_id);
1345 exception
1346 WHEN NO_DATA_FOUND then /* fix for the perf bug #2551622 */
1347 null;
1348 end;
1349
1350 begin
1351 SELECT DISTINCT '1'
1352 into charges_exist_3
1353 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
1354 WHERE ORGANIZATION_ID = x_org_id
1355 AND TRANSACTION_SOURCE_TYPE_ID = 5
1356 AND TRANSACTION_SOURCE_ID = x_wip_id;
1357 exception
1358 WHEN NO_DATA_FOUND then /* fix for the perf bug #2551622 */
1359 begin
1360 SELECT DISTINCT '1'
1361 into charges_exist_3
1362 FROM dual
1363 where EXISTS (SELECT '1'
1364 FROM WIP_OPERATION_RESOURCES
1365 WHERE ORGANIZATION_ID = x_org_id
1366 AND WIP_ENTITY_ID = x_wip_id
1367 AND APPLIED_RESOURCE_UNITS <> 0);
1368
1369 exception WHEN NO_DATA_FOUND then
1370 null;
1371 end;
1372 end;
1373
1374 IF (charges_exist_1 = '0' and charges_exist_2 = '0' and charges_exist_3 = '0' and ops_exist ='0' and charges_exist ='0') THEN
1375 x_return_status := 'S';
1376 else
1377 x_return_status := 'F';
1378 END IF;
1379
1380 END CK_MATERIAL_ALLOC_ON_HOLD;
1381
1382 --Fix for 3360801.Added the following procedure to show the messages from the api
1383 /********************************************************************
1384 * Procedure : show_mesg
1385 * Purpose : Procedure will concatenate all the messages
1386 from the workorder api and return 1 string
1387 *********************************************************************/
1388 PROCEDURE show_mesg IS
1389 l_msg_count NUMBER;
1390 mesg varchar2(2000);
1391 i NUMBER;
1392 msg_index number;
1393 temp varchar2(2000);
1394 BEGIN
1395 mesg := '';
1396
1397 l_msg_count := fnd_msg_pub.count_msg;
1398 IF(l_msg_count>0) THEN
1399
1400 msg_index := l_msg_count;
1401
1402 for i in 1..l_msg_count loop
1403 fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_NEXT,
1404 p_encoded => 'F',
1405 p_data => temp,
1406 p_msg_index_out => msg_index);
1407 msg_index := msg_index-1;
1408 mesg := mesg || ' ' || to_char(i) || ' . '||temp ;
1409 end loop;
1410 fnd_message.set_name('EAM','EAM_WO_API_MESG');
1411
1412 fnd_message.set_token(token => 'MESG',
1413 value =>mesg,
1414 translate =>FALSE);
1415 APP_EXCEPTION.RAISE_EXCEPTION;
1416 END IF;
1417
1418 END show_mesg;
1419
1420
1421 --Fix for 3360801.the following procedure will return a directory to get the log directory path
1422 PROCEDURE log_path(
1423 x_output_dir OUT NOCOPY VARCHAR2
1424 )
1425 IS
1426 l_full_path VARCHAR2(2000);
1427 l_new_full_path VARCHAR2(2000);
1428 l_file_dir VARCHAR2(2000);
1429
1430 fileHandler UTL_FILE.FILE_TYPE;
1431 fileName VARCHAR2(50);
1432
1433 l_flag NUMBER;
1434 l_index NUMBER;
1435 BEGIN
1436 fileName:='test.log';--this is only a dummy filename to check if directory is valid or not
1437
1438 /* get output directory path from database */
1439 SELECT value
1440 INTO l_full_path
1441 FROM v$parameter
1442 WHERE name = 'utl_file_dir';
1443
1444 l_flag := 0;
1445 --l_full_path contains a list of comma-separated directories
1446 WHILE(TRUE)
1447 LOOP
1448 --get the first dir in the list. Removed select statement for bug# 3805306
1449 l_index := instr(l_full_path,',')-1;
1450 IF l_index = -1 THEN
1451 l_index := length(l_full_path);
1452 END IF;
1453 l_file_dir := trim( substr( l_full_path, 1, l_index ) );
1454
1455 -- check if the dir is valid
1456 BEGIN
1457 fileHandler := UTL_FILE.FOPEN(l_file_dir , filename, 'w');
1458 l_flag := 1;
1459 EXCEPTION
1460 WHEN utl_file.invalid_path THEN
1461 l_flag := 0;
1462 WHEN utl_file.invalid_operation THEN
1463 l_flag := 0;
1464 END;
1465
1466 IF l_flag = 1 THEN --got a valid directory
1467 utl_file.fclose(fileHandler);
1468 EXIT;
1469 END IF;
1470
1471 --earlier found dir was not a valid dir,
1472 --so remove that from the list, and get the new list
1473 l_new_full_path := trim(substr(l_full_path, instr(l_full_path, ',')+1, length(l_full_path)));
1474
1475 --if the new list has not changed, there are no more valid dirs left
1476 IF l_full_path = l_new_full_path THEN
1477 l_flag:=0;
1478 EXIT;
1479 END IF;
1480 l_full_path := l_new_full_path;
1481 END LOOP;
1482
1483 IF(l_flag=1) THEN --found a valid directory
1484 x_output_dir := l_file_dir;
1485 ELSE
1486 x_output_dir:= null;
1487
1488 END IF;
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 x_output_dir := null;
1492
1493 END log_path;
1494
1495 -- Fix for Bug 3489907 Start
1496
1497 PROCEDURE Check_open_txns(p_org_id IN NUMBER,
1498 p_wip_id IN NUMBER,
1499 p_ent_type IN NUMBER,
1500 p_return_status OUT NOCOPY NUMBER,
1501 p_return_string OUT NOCOPY VARCHAR2 /* Added for bug#5335940 */) IS
1502 quantity_left NUMBER := 0;
1503 charges_exist_2 varchar2(2) := '0';
1504 charges_exist_3 varchar2(2) := '0';
1505 charges_exist_4 varchar2(2) := '0';
1506 BEGIN
1507 p_return_status := 0; /* Added for bug#5253575 */
1508 /* Check for Direct Items and OSP*/
1509
1510
1511 BEGIN
1512
1513 /*
1514 SELECT ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0)))
1515 qty_left
1516 INTO quantity_left
1517 FROM wip_eam_direct_items_v
1518 WHERE work_order_number = p_wip_id
1519 AND rownum <=1;
1520
1521 */
1522
1523 SELECT
1524 ((nvl(SUM(quantity_ordered),0)) - (nvl(SUM(quantity_received),0))) qty_left INTO quantity_left
1525 FROM
1526 (
1527 SELECT
1528 rql.wip_entity_id,
1529 rql.quantity quantity_ordered,
1530 to_number(null) quantity_received
1531 FROM po_requisition_lines_all rql,
1532 po_requisition_headers_all rqh,
1533 po_line_types plt
1534 WHERE rql.requisition_header_id = rqh.requisition_header_id
1535 AND rql.line_type_id = plt.line_type_id
1536 AND upper(rqh.authorization_status) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
1537 AND rql.line_location_id is NULL
1538 AND upper(nvl(rql.cancel_flag, 'N')) <> 'Y'
1539 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
1540 AND rql.destination_type_code = 'SHOP FLOOR'
1541 AND rql.wip_entity_id is not null
1542 UNION
1543 (
1544 SELECT
1545 pd.wip_entity_id,
1546 sum(pd.quantity_ordered) quantity_ordered,
1547 sum(pd.quantity_delivered) quantity_received
1548 FROM po_line_types plt,
1549 (
1550 SELECT
1551 pd1.wip_entity_id,
1552 pd1.wip_operation_seq_num,
1553 pd1.destination_organization_id,
1554 pol.item_description,
1555 pol.unit_price,
1556 pol.quantity,
1557 pd1.quantity_delivered,
1558 pd1.quantity_ordered,
1559 pd1.quantity_cancelled,
1560 pol.po_line_id,
1561 pol.po_header_id,
1562 pd1.req_distribution_id,
1563 pd1.line_location_id,
1564 pol.line_type_id,
1565 pd1.destination_type_code,
1566 pol.cancel_flag,
1567 pol.item_id,
1568 pol.category_id ,
1569 pd1.po_release_id,
1570 pd1.amount_ordered,
1571 pd1.amount_delivered
1572 FROM po_lines_all pol,
1573 po_distributions_all pd1
1574 WHERE pol.po_line_id = pd1.po_line_id
1575 )
1576 pd
1577 WHERE pd.line_type_id = plt.line_type_id
1578 AND upper(nvl(plt.outside_operation_flag, 'N')) = 'N'
1579 AND pd.destination_type_code = 'SHOP FLOOR'
1580 AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
1581 AND pd.wip_entity_id is not null
1582 GROUP BY pd.wip_entity_id,
1583 pd.amount_ordered,
1584 pd.amount_delivered
1585 )
1586 )
1587 WHERE wip_entity_id = p_wip_id
1588 AND rownum <=1;
1589
1590 IF ( (quantity_left > 0) OR
1591 (WIP_OSP.PO_REQ_EXISTS( p_wip_entity_id => p_wip_id
1592 ,p_rep_sched_id => -1
1593 ,p_organization_id=> p_org_id
1594 ,p_entity_type => p_ent_type)=TRUE)
1595 )THEN
1596 p_return_status:= 1;
1597 p_return_string := 'Direct Item/ OSP';
1598 RETURN;
1599 END IF;
1600 EXCEPTION
1601 WHEN NO_DATA_FOUND then
1602 null;
1603 END;
1604 /* End Check for Direct Items and OSP*/
1605
1606 /*Start Check for Material Transactions */
1607 BEGIN
1608
1609 SELECT DISTINCT '1'
1610 INTO charges_exist_2
1611 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
1612 WHERE (ORGANIZATION_ID = p_org_id
1613 AND TRANSACTION_SOURCE_TYPE_ID = 5
1614 AND TRANSACTION_SOURCE_ID = p_wip_id
1615 AND rownum <=1)
1616 OR EXISTS (SELECT '1'
1617 FROM WIP_REQUIREMENT_OPERATIONS
1618 WHERE ORGANIZATION_ID = p_org_id
1619 AND WIP_ENTITY_ID = p_wip_id
1620 AND QUANTITY_ISSUED <> 0);
1621 /*
1622 IF charges_exist_2 = '1' then
1623 p_return_status:= 2;
1624 RETURN;
1625 END IF;
1626
1627 */
1628
1629 exception
1630 WHEN NO_DATA_FOUND then /* fix for the perf bug #2551622 */
1631 BEGIN
1632 SELECT DISTINCT '1'
1633 into charges_exist_2
1634 FROM dual
1635 where EXISTS (SELECT '1'
1636 FROM WIP_OPERATION_RESOURCES
1637 WHERE ORGANIZATION_ID = p_org_id
1638 AND WIP_ENTITY_ID = p_wip_id
1639 AND APPLIED_RESOURCE_UNITS <> 0);
1640
1641 /* IF charges_exist_2 = '1' then
1642 p_return_status:= 2;
1643 RETURN;
1644 END IF;
1645 */
1646 EXCEPTION
1647 WHEN NO_DATA_FOUND then
1648 null;
1649 END;
1650 END;
1651 /*End Check for Material Transactions */
1652
1653 /* Check for Move Transactions */
1654 BEGIN
1655 SELECT '1'
1656 INTO charges_exist_3
1657 FROM DUAL
1658 WHERE EXISTS (SELECT '1'
1659 FROM WIP_REQUIREMENT_OPERATIONS
1660 WHERE ORGANIZATION_ID = p_org_id
1661 AND WIP_ENTITY_ID = p_wip_id
1662 AND QUANTITY_ISSUED <> 0)
1663 OR EXISTS(SELECT '1'
1664 FROM WIP_MOVE_TXN_INTERFACE
1665 WHERE ORGANIZATION_ID = p_org_id
1666 AND WIP_ENTITY_ID = p_wip_id)
1667 OR EXISTS(SELECT '1'
1668 FROM WIP_COST_TXN_INTERFACE
1669 WHERE ORGANIZATION_ID = p_org_id
1670 AND WIP_ENTITY_ID = p_wip_id);
1671 /* IF charges_exist_3 = '1' then
1672 p_return_status:= 3;
1673 RETURN;
1674 END IF;
1675 */
1676 EXCEPTION
1677 WHEN NO_DATA_FOUND then
1678 null;
1679 END;
1680 /*End Check for Move Transactions */
1681
1682 /* Code Added for bug#5335940 Start */
1683 IF ( charges_exist_2 = '1' AND charges_exist_3 = '0' ) THEN
1684 p_return_status := 1;
1685 fnd_message.set_name('EAM','EAM_UPDATE_WO_TXN_OPEN');
1686 fnd_message.set_token(token => 'TXN', value => 'EAM_MATERIAL', translate => TRUE );
1687 p_return_string := fnd_message.get;
1688 ELSIF ( charges_exist_2 = '0' AND charges_exist_3 = '1' ) THEN
1689 p_return_status := 1;
1690 fnd_message.set_name('EAM','EAM_UPDATE_WO_TXN_OPEN');
1691 fnd_message.set_token(token => 'TXN', value => 'EAM_RESOURCE', translate => TRUE );
1692 p_return_string := fnd_message.get;
1693 ElSIF ( charges_exist_2 = '1' AND charges_exist_3 = '1' ) THEN
1694 p_return_status := 1;
1695 fnd_message.set_name('EAM','EAM_UPDATE_WO_TXN_OPEN');
1696 fnd_message.set_token(token => 'TXN', value => 'EAM_MATERIAL_RESOURCE', translate => TRUE );
1697 p_return_string := fnd_message.get;
1698 ELSE
1699 p_return_status:=0; p_return_status:=0;
1700 p_return_string := null;
1701 END IF;
1702 /* Code Added for bug#5335940 Ends */
1703
1704 /* p_return_status:=0; --commented for bug#5335940 */
1705 END Check_open_txns;
1706
1707 PROCEDURE CANCEL( p_org_id IN NUMBER,
1708 p_wip_id IN NUMBER,
1709 x_return_status OUT NOCOPY NUMBER,
1710 x_return_string OUT NOCOPY VARCHAR2 /* Added for bug# 5335940 */) IS
1711
1712 CURSOR disc_check_po_req_cur IS
1713 SELECT 'PO/REQ Linked'
1714 FROM PO_DISTRIBUTIONS_ALL PD,
1715 PO_LINE_LOCATIONS_ALL PLL
1716 WHERE pd.po_line_id IS NOT NULL
1717 AND pd.line_location_id IS NOT NULL
1718 AND PD.WIP_ENTITY_ID = p_wip_id
1719 AND PD.DESTINATION_ORGANIZATION_ID = p_org_id
1720 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1721 AND NOT(
1722 NVL(PLL.CANCEL_FLAG,'N')='Y'
1723 OR NVL(PLL.CLOSED_CODE,'N')='FINALLY CLOSED'
1724 OR NVL(PLL.CLOSED_CODE,'N')='CANCELLED'
1725 OR NVL(PLL.CLOSED_CODE,'N')='CLOSED FOR INVOICE'
1726 OR NVL(PLL.CLOSED_CODE,'N')='CLOSED FOR RECEIVING'
1727 OR NVL(PLL.CLOSED_CODE,'N')='CLOSED'
1728 OR NVL(PLL.CLOSED_CODE,'N')='REJECTED'
1729 )
1730 UNION ALL
1731 SELECT 'PO/REQ Linked'
1732 FROM PO_REQUISITION_LINES_ALL PRL
1733 WHERE PRL.WIP_ENTITY_ID = p_wip_id
1734 AND PRL.DESTINATION_ORGANIZATION_ID = p_org_id
1735 AND NOT(
1736 NVL(PRL.CANCEL_FLAG,'N')='Y'
1737 OR NVL(PRL.CLOSED_CODE,'N')='FINALLY CLOSED'
1738 OR NVL(PRL.CLOSED_CODE,'N')='CANCELLED'
1739 OR NVL(PRL.CLOSED_CODE,'N')='CLOSED FOR INVOICE'
1740 OR NVL(PRL.CLOSED_CODE,'N')='CLOSED FOR RECEIVING'
1741 OR NVL(PRL.CLOSED_CODE,'N')='CLOSED'
1742 OR NVL(PRL.CLOSED_CODE,'N')='REJECTED'
1743 )
1744 UNION ALL
1745 SELECT 'PO/REQ Linked'
1746 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
1747 WHERE PRI.WIP_ENTITY_ID = p_wip_id
1748 AND PRI.DESTINATION_ORGANIZATION_ID = p_org_id
1749 AND NVL(PRI.PROCESS_FLAG,'FUTURE') <> 'ERROR';
1750
1751 po_req_exists VARCHAR2(20);
1752 /* Added for bug#5335940 start */
1753 l_return_string VARCHAR2(2000);
1754 l_po_exists VARCHAR2(2) := '0';
1755 l_txn_exists VARCHAR2(2) := '0';
1756 /* Added for bug#5335940 end */
1757 BEGIN
1758 OPEN disc_check_po_req_cur;
1759 FETCH disc_check_po_req_cur INTO po_req_exists;
1760 IF (disc_check_po_req_cur%FOUND) THEN
1761 l_po_exists := '1';
1762 END IF;
1763 CLOSE disc_check_po_req_cur;
1764 /* Added for bug# 5335940 Start */
1765 Check_open_txns(p_org_id => p_org_id,
1766 p_wip_id => p_wip_id,
1767 p_ent_type => 6,
1768 p_return_status => x_return_status,
1769 p_return_string => l_return_string
1770 );
1771 IF x_return_status = 1 THEN
1772 l_txn_exists := '1';
1773 END IF;
1774
1775 IF ( l_po_exists = '0' AND l_txn_exists = '1' ) THEN
1776 x_return_status:=1;
1777 x_return_string := l_return_string;
1778 ELSIF ( l_po_exists = '1' AND l_txn_exists = '0' ) THEN
1779 x_return_status:=1;
1780 fnd_message.set_name ('EAM','EAM_UPDATE_WO_CONFIRM_CANCEL');
1781 x_return_string := fnd_message.get;
1782 ELSIF ( l_po_exists = '1' AND l_txn_exists = '1' ) THEN
1783 fnd_message.set_name ('EAM','EAM_UPDATE_WO_CONFIRM_CANCEL');
1784 x_return_string := fnd_message.get;
1785 x_return_string := l_return_string || x_return_string;
1786 ELSE
1787 x_return_status:=0;
1788 x_return_string := null;
1789 END IF;
1790 /* Added for bug#5335940 end */
1791
1792 END CANCEL;
1793
1794 -- Fix for Bug 3489907 End
1795
1796 /* Function to get rebuild description in eam_work_orders_v*/
1797
1798 FUNCTION get_rebuild_description( p_rebuild_item_id NUMBER, p_organization_id NUMBER)
1799 return VARCHAR2 IS
1800 l_description MTL_SYSTEM_ITEMS.DESCRIPTION%TYPE ;
1801 CURSOR get_description IS
1802 SELECT description
1803 FROM MTL_SYSTEM_ITEMS
1804 WHERE inventory_item_id = p_rebuild_item_id
1805 AND organization_id = p_organization_id ;
1806 BEGIN
1807 OPEN get_description ;
1808 FETCH get_description INTO l_description ;
1809 CLOSE get_description;
1810 return l_description;
1811 END get_rebuild_description;
1812
1813
1814 /*
1815 Procedure to populate the x_workflow_table with approver's details
1816 for a particular workflow item key
1817 p_item_type = Workflow Item Type
1818 p_item_key = Workflow Item key
1819 x_workflow_table = This table will be populated with approver's details like telephone, email etc.
1820 */
1821
1822 PROCEDURE get_workflow_details( p_item_type IN STRING,
1823 p_item_key IN STRING,
1824 x_workflow_table OUT NOCOPY t_workflow_table)
1825 IS
1826 l_transaction_id VARCHAR2(50);
1827 l_approvalProcessCompleteYNOut VARCHAR2(1);
1828 l_approversOut ame_util.approversTable2;
1829 l_temp NUMBER; -- for fix 9395270
1830
1831
1832 -- Fetch telephone no from per_addresses
1833 CURSOR per_addresses_csr( p_person_id NUMBER)IS
1834 SELECT telephone_number_1
1835 FROM per_addresses
1836 WHERE person_id = p_person_id
1837 AND sysdate BETWEEN date_from AND date_to
1838 AND primary_flag = 'Y';
1839
1840 -- Fetch telephone no from per_addresses with employee_id from fnd_user
1841 CURSOR per_addresses_fnd_csr(p_user_id NUMBER) IS
1842 SELECT telephone_number_1
1843 FROM per_addresses
1844 WHERE sysdate BETWEEN date_from AND date_to
1845 AND primary_flag = 'Y'
1846 AND person_id = (SELECT employee_id
1847 FROM fnd_user
1848 WHERE user_id = p_user_id);
1849
1850 -- Fetch email address per_all_people_f
1851 CURSOR per_all_people_csr(p_person_id NUMBER) IS
1852 SELECT email_address
1853 FROM per_all_people_f
1854 WHERE person_id = p_person_id
1855 AND sysdate between effective_start_date AND effective_end_date;
1856
1857 -- Fetch email address per_all_people_f
1858 CURSOR per_all_people_fnd_csr(p_user_id NUMBER) IS
1859 SELECT email_address
1860 FROM per_all_people_f
1861 WHERE sysdate between effective_start_date AND effective_end_date
1862 AND person_id = (SELECT employee_id
1863 FROM fnd_user
1864 WHERE user_id = p_user_id);
1865
1866 -- Fetch email address fnd_user
1867 CURSOR fnd_user_csr(p_user_id VARCHAR2) IS
1868 SELECT email_address
1869 FROM fnd_user
1870 WHERE user_id = p_user_id;
1871
1872 l_transaction_type VARCHAR2(250);
1873 l_transaction_appl_id NUMBER;
1874 l_ame_set VARCHAR2 (1);
1875
1876 BEGIN
1877
1878 BEGIN
1879 l_ame_set :='Y';
1880
1881 l_transaction_type := wf_engine.GetItemAttrtext( itemtype => p_item_type,
1882 itemkey => p_item_key, aname => 'AME_TRANSACTION_TYPE');
1883 EXCEPTION
1884 WHEN OTHERS THEN
1885 l_transaction_type := 'oracle.apps.eam.workorder.release.approval';
1886 END;
1887
1888 BEGIN
1889 l_transaction_id := wf_engine.GetItemAttrtext( itemtype => p_item_type,
1890 itemkey => p_item_key, aname => 'AME_TRANSACTION_ID');
1891 EXCEPTION
1892 WHEN OTHERS THEN
1893 l_transaction_id := p_item_key;
1894 END;
1895 -- BEGIN FIX 9395270
1896 BEGIN
1897
1898 select 1
1899 INTO l_temp
1900 from eam_work_order_details ewod, wip_discrete_jobs wdj
1901 where wdj.status_type not in (3,4,5,12)
1902 and ewod.pending_flag = 'Y'
1903 and ewod.wip_entity_id = wdj.wip_entity_id
1904 and ewod.wip_entity_id = (select wip_entity_id from EAM_WO_WORKFLOWS where wf_item_key=p_item_key);
1905
1906 EXCEPTION
1907 WHEN OTHERS THEN
1908 l_temp :=0;
1909 END;
1910 --start of bug 11072252
1911 BEGIN
1912 select fnd_application_id into l_transaction_appl_id from ame_calling_apps where transaction_type_id = l_transaction_type;
1913 EXCEPTION WHEN NO_DATA_FOUND THEN
1914 l_ame_set:='N';
1915 END;
1916 IF l_ame_set='Y' THEN
1917 --end of bug 11072252
1918 IF l_temp = 1 then -- work order in approval process
1919 Ame_api2.GetAllApprovers7(applicationIdIn => l_transaction_appl_id,
1920 transactionTypeIn => l_transaction_type,
1921 transactionIdIn => l_transaction_id,
1922 approvalProcessCompleteYNOut => l_approvalProcessCompleteYNOut,
1923 approversOut => l_approversOut);
1924 ELSE
1925 --call the ame api to get approval history instead of Ame_api2.GetAllApprovers7 fix for 9395270
1926 ame_api6.getApprovers(applicationIdIn => l_transaction_appl_id,
1927 transactionTypeIn => l_transaction_type,
1928 transactionIdIn => l_transaction_id,
1929 -- approvalProcessCompleteYNOut => l_approvalProcessCompleteYNOut,
1930 approversOut => l_approversOut);
1931
1932 END IF;
1933
1934 -- END FIX 9395270
1935
1936 IF l_approversOut.count > 0 THEN
1937 FOR i IN l_approversOut.FIRST..l_approversOut.LAST
1938 LOOP
1939 x_workflow_table(i).seq_no := l_approversOut(i).approver_order_number;
1940 x_workflow_table(i).approver := l_approversOut(i).display_name;
1941 x_workflow_table(i).status := l_approversOut(i).approval_status;
1942 -- x_workflow_table(i).status_date:=
1943
1944 IF (l_approversOut(i).orig_system = AME_UTIL.PERORIGSYSTEM) THEN
1945 OPEN per_addresses_csr(l_approversOut(i).orig_system_id);
1946 FETCH per_addresses_csr INTO x_workflow_table(i).telephone;
1947 CLOSE per_addresses_csr;
1948
1949 OPEN per_all_people_csr(l_approversOut(i).orig_system_id);
1950 FETCH per_all_people_csr INTO x_workflow_table(i).email;
1951 CLOSE per_all_people_csr;
1952
1953 ELSIF (l_approversOut(i).orig_system = AME_UTIL.FNDUSERORIGSYSTEM) THEN
1954 OPEN per_addresses_fnd_csr(l_approversOut(i).orig_system_id);
1955 FETCH per_addresses_fnd_csr INTO x_workflow_table(i).telephone;
1956 CLOSE per_addresses_fnd_csr;
1957
1958 OPEN per_all_people_fnd_csr(l_approversOut(i).orig_system_id);
1959 FETCH per_all_people_fnd_csr INTO x_workflow_table(i).email;
1960 IF per_all_people_fnd_csr%NOTFOUND THEN
1961 OPEN fnd_user_csr(l_approversOut(i).orig_system_id);
1962 FETCH fnd_user_csr INTO x_workflow_table(i).email;
1963 CLOSE fnd_user_csr;
1964 END IF;
1965 CLOSE per_all_people_fnd_csr;
1966
1967 END IF;
1968 --Added for bug 14123496 start
1969 IF l_approversOut(i).approval_status ='APPROVE' OR l_approversOut(i).approval_status ='REJECT' then
1970 BEGIN
1971 select TEXT_VALUE into x_workflow_table(i).comments from
1972 wf_notification_attributes where notification_id =(select notification_id from wf_notifications where item_key=p_item_key and
1973 responder=l_approversOut(i).name and status='CLOSED') and NAME='WF_NOTE';
1974 EXCEPTION
1975 WHEN NO_DATA_FOUND THEN
1976 x_workflow_table(i).comments:=NULL; --work flow notification purge program might have run
1977 END;
1978 ELSE
1979 x_workflow_table(i).comments:=NULL;
1980 END IF;
1981 --Added for bug 14123496 end
1982 END LOOP;
1983 END IF;
1984 END IF;
1985
1986 END get_workflow_details;
1987
1988 PROCEDURE callCostEstimatorSS(
1989 p_api_version IN NUMBER := 1.0,
1990 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1991 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1992 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1993 p_wip_entity_id IN NUMBER,
1994 p_organization_id IN NUMBER,
1995 x_return_status OUT NOCOPY VARCHAR2,
1996 x_msg_count OUT NOCOPY NUMBER,
1997 x_msg_data OUT NOCOPY VARCHAR2
1998 ) IS
1999
2000 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2001 l_msg_count NUMBER ;
2002 l_msg_data VARCHAR2(2000) ;
2003
2004 BEGIN
2005
2006 -- Initialize API return status to success
2007 x_return_status := FND_API.G_RET_STS_SUCCESS;
2008
2009 UPDATE WIP_DISCRETE_JOBS
2010 SET estimation_status = 2
2011 WHERE wip_entity_id = p_wip_entity_id
2012 AND organization_id = p_organization_id;
2013
2014 CSTPECEP.Estimate_WorkOrder_GRP( p_api_version => p_api_version,
2015 p_init_msg_list => p_init_msg_list,
2016 p_commit => p_commit,
2017 p_validation_level => p_validation_level,
2018 x_return_status => l_return_status,
2019 x_msg_count => l_msg_count,
2020 x_msg_data => l_msg_data,
2021 p_organization_id => p_organization_id,
2022 p_wip_entity_id => p_wip_entity_id );
2023
2024
2025 x_return_status := l_return_status ;
2026 x_msg_count := l_msg_count ;
2027 x_msg_data := l_msg_data ;
2028
2029
2030 END callCostEstimatorSS ;
2031
2032 PROCEDURE GET_REPLACED_REBUILDS(
2033 p_wip_entity_id IN NUMBER,
2034 p_organization_id IN NUMBER,
2035 x_replaced_rebuild_tbl OUT NOCOPY REPLACE_REBUILD_TBL_TYPE,
2036 x_return_status OUT NOCOPY VARCHAR2,
2037 x_error_message OUT NOCOPY VARCHAR2
2038 )IS
2039 l_maint_objid_tbl EAM_WORKORDER_UTIL_PKG.REPLACE_REBUILD_TBL_TYPE;
2040 BEGIN
2041 x_return_status := FND_API.G_RET_STS_SUCCESS;
2042
2043
2044 SELECT wdj.maintenance_object_id
2045 bulk collect into l_maint_objid_tbl
2046 FROM WIP_DISCRETE_JOBS wdj
2047 WHERE wdj.parent_wip_entity_id = p_wip_entity_id
2048 AND wdj.organization_id = p_organization_id
2049 AND wdj.manual_rebuild_flag = 'N'
2050 AND wdj.maintenance_object_type = 3;
2051
2052 EXCEPTION WHEN NO_DATA_FOUND THEN
2053 null;
2054 x_return_status := FND_API.G_RET_STS_SUCCESS;
2055 WHEN OTHERS THEN
2056
2057 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_ERROR;
2058 x_error_message := sqlerrm;
2059 END GET_REPLACED_REBUILDS;
2060
2061 -- For Failure Ananlysis Project Tools > Menu option
2062 FUNCTION get_msu_resp_id( p_user_id IN NUMBER)
2063 RETURN NUMBER
2064 IS
2065 l_function_name fnd_form_functions.function_name%TYPE := 'EAM_APPL_MENU_HOME';
2066 l_msu_function_id NUMBER;
2067 l_out VARCHAR2(300);
2068 l_resp_id NUMBER;
2069
2070 BEGIN
2071
2072 SELECT function_id
2073 INTO l_msu_function_id
2074 FROM fnd_form_functions
2075 WHERE function_name = l_function_name;
2076
2077 get_resp(l_msu_function_id, p_user_id, null, l_resp_id,l_out);
2078 RETURN l_resp_id;
2079
2080 EXCEPTION
2081 WHEN NO_DATA_FOUND THEN
2082 RETURN -2;
2083 END get_msu_resp_id;
2084
2085
2086 END EAM_WORKORDER_UTIL_PKG;
2087
[Home] [Help]