[Home] [Help]
PACKAGE BODY: APPS.WIP_SUB_OP_RESOURCES_PKG
Source
1 PACKAGE BODY WIP_SUB_OP_RESOURCES_PKG AS
2 /* $Header: wipsorsb.pls 120.9 2011/09/30 13:59:12 sisankar ship $ */
3
4 procedure add_resource(
5 p_org_id in number,
6 p_wip_entity_id in number,
7 p_first_schedule_id in number,
8 p_operation_seq_num in number,
9 p_resource_seq_num in number,
10 p_resource_id in number,
11 p_uom_code in varchar2,
12 p_basis_type in number,
13 p_activity_id in number,
14 p_standard_rate_flag in number,
15 p_start_date in date,
16 p_completion_date in date) is
17 x_user_id number;
18 x_login_id number;
19 x_check_for_dup boolean := FALSE;
20 begin
21 x_user_id := FND_GLOBAL.USER_ID;
22 x_login_id := FND_GLOBAL.LOGIN_ID;
23 -- insert operation resource record
24 begin
25 insert into wip_sub_operation_resources(
26 last_update_date,
27 last_updated_by,
28 creation_date,
29 created_by,
30 last_update_login,
31 organization_id,
32 wip_entity_id,
33 repetitive_schedule_id,
34 operation_seq_num,
35 resource_seq_num,
36 resource_id,
37 uom_code,
38 basis_type,
39 activity_id,
40 standard_rate_flag,
41 usage_rate_or_amount,
42 scheduled_flag,
43 assigned_units,
44 autocharge_type,
45 applied_resource_units,
46 applied_resource_value,
47 start_date,
48 completion_date
49 ) values (
50 SYSDATE,
51 x_user_id,
52 SYSDATE,
53 x_user_id,
54 x_login_id,
55 p_org_id,
56 p_wip_entity_id,
57 p_first_schedule_id,
58 p_operation_seq_num,
59 p_resource_seq_num,
60 p_resource_id,
61 p_uom_code,
62 nvl(p_basis_type, WIP_CONSTANTS.PER_LOT),
63 p_activity_id,
64 p_standard_rate_flag,
65 0, -- usage_rate_or_amount
66 WIP_CONSTANTS.NO, -- scheduled_flag
67 1, -- assigned_units
68 WIP_CONSTANTS.MANUAL, -- autocharge_type
69 0, -- applied_resource_units
70 0, -- applied_resource_value
71 p_start_date,
72 p_completion_date);
73
74 exception
75 when DUP_VAL_ON_INDEX then
76 x_check_for_dup := TRUE;
77 end;
78
79 if (x_check_for_dup) then
80 -- the primary key already exists, so check to see
81 -- if the old record matches the new record.
82 declare
83 cursor get_ident_resource(
84 c_org_id number,
85 c_wip_entity_id number,
86 c_operation_seq_num number,
87 c_resource_seq_num number,
88 c_first_schedule_id number,
89 c_resource_id number) is
90 select 'X'
91 from dual
92 where c_resource_id =
93 (select resource_id
94 from wip_sub_operation_resources
95 where organization_id = c_org_id
96 and wip_entity_id = c_wip_entity_id
97 and operation_seq_num = c_operation_seq_num
98 and resource_seq_num = c_resource_seq_num
99 and nvl(repetitive_schedule_id,-1) = nvl(c_first_schedule_id,-1));
100
101 x_dummy varchar2(1);
102 x_found boolean;
103 begin
104 open get_ident_resource(
105 c_org_id => p_org_id,
106 c_wip_entity_id => p_wip_entity_id,
107 c_operation_seq_num => p_operation_seq_num,
108 c_resource_seq_num => p_resource_seq_num,
109 c_first_schedule_id => p_first_schedule_id,
110 c_resource_id => p_resource_id);
111 fetch get_ident_resource into x_dummy;
112 x_found := get_ident_resource%FOUND;
113 close get_ident_resource;
114
115 if (not x_found) then
116 fnd_message.set_name(
117 application => 'WIP',
118 name => 'WIP_MISMATCHED_RES');
119 fnd_message.raise_error;
120 end if;
121 end;
122 end if;
123
124 return;
125
126 exception
127 when OTHERS then
128 wip_constants.get_ora_error(
129 application => 'WIP',
130 proc_name => 'WIP_SUB_OPERATION_RESOURCES_PKG.ADD_RESOURCE');
131 fnd_message.raise_error;
132 end add_resource;
133
134 procedure check_dup_resources(
135 p_group_id in number,
136 p_operation_seq_num out nocopy number,
137 p_resource_seq_num out nocopy number,
138 p_dup_exists out nocopy boolean) is
139
140 cursor get_dup_res(c_group_id number) is
141 select wcti1.operation_seq_num,
142 wcti1.resource_seq_num
143 from wip_cost_txn_interface wcti1,
144 wip_cost_txn_interface wcti2
145 where wcti1.source_code = 'NEW_RES'
146 and wcti1.group_id = c_group_id
147 and wcti1.source_code = wcti2.source_code
148 and wcti1.group_id = wcti2.group_id
149 and wcti1.wip_entity_id = wcti2.wip_entity_id
150 and wcti1.operation_seq_num = wcti2.operation_seq_num
151 and wcti1.resource_seq_num = wcti2.resource_seq_num
152 and wcti1.organization_id = wcti2.organization_id
153 and nvl(wcti1.repetitive_schedule_id, -1)
154 = nvl(wcti2.repetitive_schedule_id, -1)
155 and wcti1.resource_id <> wcti2.resource_id;
156
157 begin
158 -- get any added resources that have been duplicated
159 open get_dup_res(c_group_id => p_group_id);
160 fetch get_dup_res into p_operation_seq_num, p_resource_seq_num;
161 p_dup_exists := get_dup_res%FOUND;
162 close get_dup_res;
163 end check_dup_resources;
164
165 procedure add_resources(p_group_id in number) is
166 begin
167 -- add resources from interface table
168 -- note: if adding op on the fly, then this procedure should only be called
169 -- after the new op has been added to wip_operations
170 insert into wip_sub_operation_resources(
171 organization_id,
172 wip_entity_id,
173 repetitive_schedule_id,
174 operation_seq_num,
175 resource_seq_num,
176 resource_id,
177 uom_code,
178 basis_type,
179 activity_id,
180 standard_rate_flag,
181 usage_rate_or_amount,
182 scheduled_flag,
183 assigned_units,
184 autocharge_type,
185 applied_resource_units,
186 applied_resource_value,
187 start_date,
188 completion_date,
189 last_update_date,
190 last_updated_by,
191 creation_date,
192 created_by,
193 last_update_login)
194 select distinct
195 wcti.organization_id,
196 wcti.wip_entity_id,
197 wcti.repetitive_schedule_id,
198 wcti.operation_seq_num,
199 wcti.resource_seq_num,
200 wcti.resource_id,
201 wcti.primary_uom,
202 nvl(wcti.basis_type, WIP_CONSTANTS.PER_LOT),
203 br.default_activity_id,
204 wcti.standard_rate_flag,
205 0, -- usage_rate_or_amount
206 WIP_CONSTANTS.NO, -- scheduled_flag
207 1, -- assigned_units
208 WIP_CONSTANTS.MANUAL, -- autocharge_type
209 0, -- applied_resource_units
210 0, -- applied_resource_value
211 wo.first_unit_start_date,
212 wo.last_unit_completion_date,
213 SYSDATE,
214 wcti.last_updated_by,
215 SYSDATE,
216 wcti.created_by,
217 wcti.last_update_login
218 from bom_resources br,
219 wip_operations wo,
220 wip_cost_txn_interface wcti
221 where wcti.source_code = 'NEW_RES'
222 and wcti.group_id = p_group_id
223 and wcti.organization_id = wo.organization_id
224 and wcti.wip_entity_id = wo.wip_entity_id
225 and wcti.operation_seq_num = wo.operation_seq_num
226 and wcti.resource_id = br.resource_id
227 and nvl(wcti.repetitive_schedule_id, -1)
228 = nvl(wo.repetitive_schedule_id, -1);
229
230 -- delete txn qty = NULL records that are used for adding resources
231 -- changed condition from txn qty = 0 to txn qty is null for bug # 661593
232 delete from wip_cost_txn_interface
233 where group_id = p_group_id
234 and transaction_quantity is NULL;
235
236 -- clean up interface
237 update wip_cost_txn_interface
238 set source_code = NULL -- clear source code to remove NEW_RES message
239 where group_id = p_group_id;
240 end add_resources;
241
242 FUNCTION CHECK_PO_AND_REQ(
243 p_org_id IN NUMBER,
244 p_wip_entity_id IN NUMBER,
245 p_operation_seq_num IN NUMBER,
246 p_resource_seq_num IN NUMBER,
247 p_rep_sched_id IN NUMBER) RETURN BOOLEAN IS
248
249 CURSOR disc_check_po_req_cur IS
250 SELECT 'No PO/REQ Linked'
251 FROM DUAL
252 WHERE NOT EXISTS
253 (SELECT '1'
254 FROM PO_DISTRIBUTIONS_ALL PD,
255 WIP_SUB_OPERATION_RESOURCES WOR
256 /* Fixed bug 3115844 */
257 WHERE pd.po_line_id IS NOT NULL
258 AND pd.line_location_id IS NOT NULL
259 AND WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
260 AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
261 AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
262 AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM
263 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
264 AND WOR.ORGANIZATION_ID = p_org_id
265 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
266 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num)
267 AND NOT EXISTS
268 (SELECT '1'
269 FROM PO_REQUISITION_LINES_ALL PRL,
270 WIP_SUB_OPERATION_RESOURCES WOR
271 WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
272 AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
273 AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
274 AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM
275 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
276 AND WOR.ORGANIZATION_ID = p_org_id
277 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
278 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num)
279 AND NOT EXISTS
280 (SELECT '1'
281 FROM PO_REQUISITIONS_INTERFACE PRI,
282 WIP_SUB_OPERATION_RESOURCES WOR
283 WHERE WOR.WIP_ENTITY_ID = PRI.WIP_ENTITY_ID
284 AND WOR.ORGANIZATION_ID = PRI.DESTINATION_ORGANIZATION_ID
285 AND WOR.OPERATION_SEQ_NUM = PRI.WIP_OPERATION_SEQ_NUM
286 AND WOR.RESOURCE_SEQ_NUM = PRI.WIP_RESOURCE_SEQ_NUM
287 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
288 AND WOR.ORGANIZATION_ID = p_org_id
289 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
290 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num);
291
292 CURSOR rep_check_po_req_cur IS
293 SELECT 'No PO/REQ Linked'
294 FROM DUAL
295 WHERE NOT EXISTS
296 (SELECT '1'
297 FROM PO_DISTRIBUTIONS_ALL PD,
298 WIP_SUB_OPERATION_RESOURCES WOR
299 /* Fixed bug 3115844 */
300 WHERE pd.po_line_id IS NOT NULL
301 AND pd.line_location_id IS NOT NULL
302 AND WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
303 AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
304 AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
305 AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM
306 AND WOR.REPETITIVE_SCHEDULE_ID =
307 PD.WIP_REPETITIVE_SCHEDULE_ID
308 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
309 AND WOR.ORGANIZATION_ID = p_org_id
310 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
311 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
312 AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id)
313 AND NOT EXISTS
314 (SELECT '1'
315 FROM PO_REQUISITION_LINES_ALL PRL,
316 WIP_SUB_OPERATION_RESOURCES WOR
317 WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
318 AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
319 AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
320 AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM
321 AND WOR.REPETITIVE_SCHEDULE_ID =
322 PRL.WIP_REPETITIVE_SCHEDULE_ID
323 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
324 AND WOR.ORGANIZATION_ID = p_org_id
325 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
326 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
327 AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id)
328 AND NOT EXISTS
329 (SELECT '1'
330 FROM PO_REQUISITIONS_INTERFACE_ALL PRI,
331 WIP_SUB_OPERATION_RESOURCES WOR
332 WHERE WOR.WIP_ENTITY_ID = PRI.WIP_ENTITY_ID
333 AND WOR.ORGANIZATION_ID = PRI.DESTINATION_ORGANIZATION_ID
334 AND WOR.OPERATION_SEQ_NUM = PRI.WIP_OPERATION_SEQ_NUM
335 AND WOR.RESOURCE_SEQ_NUM = PRI.WIP_RESOURCE_SEQ_NUM
336 AND WOR.REPETITIVE_SCHEDULE_ID =
337 PRI.WIP_REPETITIVE_SCHEDULE_ID
338 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
339 AND WOR.ORGANIZATION_ID = p_org_id
340 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
341 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
342 AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id);
343
344 po_req_exist VARCHAR2(20);
345
346 BEGIN
347 -- Check for POs and REQs linked to resource
348 IF p_rep_sched_id IS NULL THEN
349 OPEN disc_check_po_req_cur;
350 FETCH disc_check_po_req_cur INTO po_req_exist;
351
352 IF (disc_check_po_req_cur%NOTFOUND) THEN
353 CLOSE disc_check_po_req_cur;
354 RETURN FALSE;
355 ELSE
356 CLOSE disc_check_po_req_cur;
357 END IF;
358 ELSE
359 OPEN rep_check_po_req_cur;
360 FETCH rep_check_po_req_cur INTO po_req_exist;
361
362 IF (rep_check_po_req_cur%NOTFOUND) THEN
363 CLOSE rep_check_po_req_cur;
364 RETURN FALSE;
365 ELSE
366 CLOSE rep_check_po_req_cur;
367 END IF;
368 END IF;
369
370 RETURN TRUE;
371
372 END CHECK_PO_AND_REQ;
373
374
375 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
376 X_Wip_Entity_Id NUMBER,
377 X_Operation_Seq_Num NUMBER,
378 X_Resource_Seq_Num NUMBER,
379 X_Organization_Id NUMBER,
380 X_Repetitive_Schedule_Id NUMBER,
381 X_Last_Update_Date DATE,
382 X_Last_Updated_By NUMBER,
383 X_Creation_Date DATE,
384 X_Created_By NUMBER,
385 X_Last_Update_Login NUMBER,
386 X_Resource_Id NUMBER,
387 X_Uom_Code VARCHAR2,
388 X_Basis_Type NUMBER,
389 X_Usage_Rate_Or_Amount NUMBER,
390 X_Activity_Id NUMBER,
391 X_Scheduled_Flag NUMBER,
392 X_Assigned_Units NUMBER,
393 X_Autocharge_Type NUMBER,
394 X_Standard_Rate_Flag NUMBER,
395 X_Applied_Resource_Units NUMBER,
396 X_Applied_Resource_Value NUMBER,
397 X_Attribute_Category VARCHAR2,
398 X_Attribute1 VARCHAR2,
399 X_Attribute2 VARCHAR2,
400 X_Attribute3 VARCHAR2,
401 X_Attribute4 VARCHAR2,
402 X_Attribute5 VARCHAR2,
403 X_Attribute6 VARCHAR2,
404 X_Attribute7 VARCHAR2,
405 X_Attribute8 VARCHAR2,
406 X_Attribute9 VARCHAR2,
407 X_Attribute10 VARCHAR2,
408 X_Attribute11 VARCHAR2,
409 X_Attribute12 VARCHAR2,
410 X_Attribute13 VARCHAR2,
411 X_Attribute14 VARCHAR2,
412 X_Attribute15 VARCHAR2,
413 X_Completion_Date DATE,
414 X_Start_Date DATE,
415 X_Schedule_Seq_Num NUMBER,
416 X_Substitute_Group_Num NUMBER,
417 X_Replacement_Group_Num NUMBER,
418 X_Setup_Id NUMBER
419
420 ) IS
421 CURSOR C IS SELECT rowid FROM WIP_SUB_OPERATION_RESOURCES
422 WHERE wip_entity_id = X_Wip_Entity_Id
423 AND organization_id = X_Organization_Id
424 AND operation_seq_num = X_Operation_Seq_Num
425 AND resource_seq_num = X_Resource_Seq_Num
426 AND (repetitive_Schedule_id = X_Repetitive_Schedule_Id
427 OR (repetitive_schedule_id IS NULL
428 AND X_Repetitive_Schedule_Id IS NULL));
429
430 BEGIN
431 INSERT INTO WIP_SUB_OPERATION_RESOURCES(
432 wip_entity_id,
433 operation_seq_num,
434 resource_seq_num,
435 organization_id,
436 repetitive_schedule_id,
437 last_update_date,
438 last_updated_by,
439 creation_date,
440 created_by,
441 last_update_login,
442 resource_id,
443 uom_code,
444 basis_type,
445 usage_rate_or_amount,
446 activity_id,
447 scheduled_flag,
448 assigned_units,
449 autocharge_type,
450 standard_rate_flag,
451 applied_resource_units,
452 applied_resource_value,
453 attribute_category,
454 attribute1,
455 attribute2,
456 attribute3,
457 attribute4,
458 attribute5,
459 attribute6,
460 attribute7,
461 attribute8,
462 attribute9,
463 attribute10,
464 attribute11,
465 attribute12,
466 attribute13,
467 attribute14,
468 attribute15,
469 completion_date,
470 start_date,
471 schedule_seq_num,
472 substitute_group_num,
473 replacement_group_num,
474 setup_id
475 ) VALUES (
476 X_Wip_Entity_Id,
477 X_Operation_Seq_Num,
478 X_Resource_Seq_Num,
479 X_Organization_Id,
480 X_Repetitive_Schedule_Id,
481 X_Last_Update_Date,
482 X_Last_Updated_By,
483 X_Creation_Date,
484 X_Created_By,
485 X_Last_Update_Login,
486 X_Resource_Id,
487 X_Uom_Code,
488 X_Basis_Type,
489 X_Usage_Rate_Or_Amount,
490 X_Activity_Id,
491 X_Scheduled_Flag,
492 X_Assigned_Units,
493 X_Autocharge_Type,
494 X_Standard_Rate_Flag,
495 X_Applied_Resource_Units,
496 X_Applied_Resource_Value,
497 X_Attribute_Category,
498 X_Attribute1,
499 X_Attribute2,
500 X_Attribute3,
501 X_Attribute4,
502 X_Attribute5,
503 X_Attribute6,
504 X_Attribute7,
505 X_Attribute8,
506 X_Attribute9,
507 X_Attribute10,
508 X_Attribute11,
509 X_Attribute12,
510 X_Attribute13,
511 X_Attribute14,
512 X_Attribute15,
513 X_Completion_Date,
514 X_Start_Date,
515 X_Schedule_Seq_Num,
516 X_Substitute_Group_Num,
517 X_Replacement_Group_Num,
518 X_Setup_Id
519 );
520
521 OPEN C;
522 FETCH C INTO X_Rowid;
523 if (C%NOTFOUND) then
524 CLOSE C;
525 Raise NO_DATA_FOUND;
526 end if;
527 CLOSE C;
528 END Insert_Row;
529
530
531
532 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
533 X_Wip_Entity_Id NUMBER,
534 X_Operation_Seq_Num NUMBER,
535 X_Resource_Seq_Num NUMBER,
536 X_Organization_Id NUMBER,
537 X_Repetitive_Schedule_Id NUMBER,
538 X_Resource_Id NUMBER,
539 X_Uom_Code VARCHAR2,
540 X_Basis_Type NUMBER,
541 X_Usage_Rate_Or_Amount NUMBER,
542 X_Activity_Id NUMBER,
543 X_Scheduled_Flag NUMBER,
544 X_Assigned_Units NUMBER,
545 X_Autocharge_Type NUMBER,
546 X_Standard_Rate_Flag NUMBER,
547 X_Applied_Resource_Units NUMBER,
548 X_Applied_Resource_Value NUMBER,
549 X_Attribute_Category VARCHAR2,
550 X_Attribute1 VARCHAR2,
551 X_Attribute2 VARCHAR2,
552 X_Attribute3 VARCHAR2,
553 X_Attribute4 VARCHAR2,
554 X_Attribute5 VARCHAR2,
555 X_Attribute6 VARCHAR2,
556 X_Attribute7 VARCHAR2,
557 X_Attribute8 VARCHAR2,
558 X_Attribute9 VARCHAR2,
559 X_Attribute10 VARCHAR2,
560 X_Attribute11 VARCHAR2,
561 X_Attribute12 VARCHAR2,
562 X_Attribute13 VARCHAR2,
563 X_Attribute14 VARCHAR2,
564 X_Attribute15 VARCHAR2,
565 X_Completion_Date DATE,
566 X_Start_Date DATE,
567 X_Schedule_Seq_Num NUMBER,
568 X_Substitute_Group_Num NUMBER,
569 X_Replacement_Group_Num NUMBER
570
571 ) IS
572 CURSOR C IS
573 SELECT *
574 FROM WIP_SUB_OPERATION_RESOURCES
575 WHERE rowid = X_Rowid
576 FOR UPDATE of Wip_Entity_Id NOWAIT;
577 Recinfo C%ROWTYPE;
578 BEGIN
579 OPEN C;
580 FETCH C INTO Recinfo;
581 if (C%NOTFOUND) then
582 CLOSE C;
583 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
584 FND_MESSAGE.raise_error;
585 APP_EXCEPTION.raise_exception;
586 end if;
587 CLOSE C;
588 if (
589
590 (Recinfo.wip_entity_id = X_Wip_Entity_Id)
591 AND (Recinfo.operation_seq_num = X_Operation_Seq_Num)
592 AND (Recinfo.resource_seq_num = X_Resource_Seq_Num)
593 AND (Recinfo.organization_id = X_Organization_Id)
594 AND ( (Recinfo.repetitive_schedule_id = X_Repetitive_Schedule_Id)
595 OR ( (Recinfo.repetitive_schedule_id IS NULL)
596 AND (X_Repetitive_Schedule_Id IS NULL)))
597 AND (Recinfo.resource_id = X_Resource_Id)
598 AND ( (Recinfo.uom_code = X_Uom_Code)
599 OR ( (Recinfo.uom_code IS NULL)
600 AND (X_Uom_Code IS NULL)))
601 AND (Recinfo.basis_type = X_Basis_Type)
602 AND (ROUND(Recinfo.usage_rate_or_amount, 6) = X_Usage_Rate_Or_Amount)
603 AND ( (Recinfo.activity_id = X_Activity_Id)
604 OR ( (Recinfo.activity_id IS NULL)
605 AND (X_Activity_Id IS NULL)))
606 AND (Recinfo.scheduled_flag = X_Scheduled_Flag)
607 AND ( (Recinfo.assigned_units = X_Assigned_Units)
608 OR ( (Recinfo.assigned_units IS NULL)
609 AND (X_Assigned_Units IS NULL)))
610 AND (Recinfo.autocharge_type = X_Autocharge_Type)
611 AND (Recinfo.standard_rate_flag = X_Standard_Rate_Flag)
612 AND (Recinfo.applied_resource_units = X_Applied_Resource_Units)
613 AND (Recinfo.applied_resource_value = X_Applied_Resource_Value)
614 AND ( (Recinfo.attribute_category = X_Attribute_Category)
615 OR ( (Recinfo.attribute_category IS NULL)
616 AND (X_Attribute_Category IS NULL)))
617 AND ( (Recinfo.attribute1 = X_Attribute1)
618 OR ( (Recinfo.attribute1 IS NULL)
619 AND (X_Attribute1 IS NULL)))
620 AND ( (Recinfo.attribute2 = X_Attribute2)
621 OR ( (Recinfo.attribute2 IS NULL)
622 AND (X_Attribute2 IS NULL)))
623 AND ( (Recinfo.attribute3 = X_Attribute3)
624 OR ( (Recinfo.attribute3 IS NULL)
625 AND (X_Attribute3 IS NULL)))
626 AND ( (Recinfo.attribute4 = X_Attribute4)
627 OR ( (Recinfo.attribute4 IS NULL)
628 AND (X_Attribute4 IS NULL)))
629 AND ( (Recinfo.attribute5 = X_Attribute5)
630 OR ( (Recinfo.attribute5 IS NULL)
631 AND (X_Attribute5 IS NULL)))
632 AND ( (Recinfo.attribute6 = X_Attribute6)
633 OR ( (Recinfo.attribute6 IS NULL)
634 AND (X_Attribute6 IS NULL)))
635 AND ( (Recinfo.attribute7 = X_Attribute7)
636 OR ( (Recinfo.attribute7 IS NULL)
637 AND (X_Attribute7 IS NULL)))
638 AND ( (Recinfo.attribute8 = X_Attribute8)
639 OR ( (Recinfo.attribute8 IS NULL)
640 AND (X_Attribute8 IS NULL)))
641 AND ( (Recinfo.attribute9 = X_Attribute9)
642 OR ( (Recinfo.attribute9 IS NULL)
643 AND (X_Attribute9 IS NULL)))
644 AND ( (Recinfo.attribute10 = X_Attribute10)
645 OR ( (Recinfo.attribute10 IS NULL)
646 AND (X_Attribute10 IS NULL)))
647 AND ( (Recinfo.attribute11 = X_Attribute11)
648 OR ( (Recinfo.attribute11 IS NULL)
649 AND (X_Attribute11 IS NULL)))
650 AND ( (Recinfo.attribute12 = X_Attribute12)
651 OR ( (Recinfo.attribute12 IS NULL)
652 AND (X_Attribute12 IS NULL)))
653 AND ( (Recinfo.attribute13 = X_Attribute13)
654 OR ( (Recinfo.attribute13 IS NULL)
655 AND (X_Attribute13 IS NULL)))
656 AND ( (Recinfo.attribute14 = X_Attribute14)
657 OR ( (Recinfo.attribute14 IS NULL)
658 AND (X_Attribute14 IS NULL)))
659 AND ( (Recinfo.attribute15 = X_Attribute15)
660 OR ( (Recinfo.attribute15 IS NULL)
661 AND (X_Attribute15 IS NULL)))
662 AND (Recinfo.completion_date = X_Completion_Date)
663 AND (Recinfo.start_date = X_Start_Date)
664 AND ((Recinfo.schedule_seq_num = X_Schedule_Seq_Num)
665 OR ( (Recinfo.schedule_seq_num IS NULL)
666 AND (X_schedule_seq_num IS NULL)))
667 AND (Recinfo.substitute_group_num = X_Substitute_Group_Num)
668 AND (Recinfo.replacement_group_num = X_Replacement_Group_Num)
669
670 ) then
671 return;
672 else
673 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
674 FND_MESSAGE.raise_error;
675 APP_EXCEPTION.RAISE_EXCEPTION;
676 end if;
677 END Lock_Row;
678
679 PROCEDURE Update_Row(X_Rowid VARCHAR2,
680 X_Wip_Entity_Id NUMBER,
681 X_Operation_Seq_Num NUMBER,
682 X_Resource_Seq_Num NUMBER,
683 X_Organization_Id NUMBER,
684 X_Repetitive_Schedule_Id NUMBER,
685 X_Last_Update_Date DATE,
686 X_Last_Updated_By NUMBER,
687 X_Last_Update_Login NUMBER,
688 X_Resource_Id NUMBER,
689 X_Uom_Code VARCHAR2,
690 X_Basis_Type NUMBER,
691 X_Usage_Rate_Or_Amount NUMBER,
692 X_Activity_Id NUMBER,
693 X_Scheduled_Flag NUMBER,
694 X_Assigned_Units NUMBER,
695 X_Autocharge_Type NUMBER,
696 X_Standard_Rate_Flag NUMBER,
697 X_Applied_Resource_Units NUMBER,
698 X_Applied_Resource_Value NUMBER,
699 X_Attribute_Category VARCHAR2,
700 X_Attribute1 VARCHAR2,
701 X_Attribute2 VARCHAR2,
702 X_Attribute3 VARCHAR2,
703 X_Attribute4 VARCHAR2,
704 X_Attribute5 VARCHAR2,
705 X_Attribute6 VARCHAR2,
706 X_Attribute7 VARCHAR2,
707 X_Attribute8 VARCHAR2,
708 X_Attribute9 VARCHAR2,
709 X_Attribute10 VARCHAR2,
710 X_Attribute11 VARCHAR2,
711 X_Attribute12 VARCHAR2,
712 X_Attribute13 VARCHAR2,
713 X_Attribute14 VARCHAR2,
714 X_Attribute15 VARCHAR2,
715 X_Completion_Date DATE,
716 X_Start_Date DATE,
717 X_Schedule_Seq_Num NUMBER,
718 X_Substitute_Group_Num NUMBER,
719 X_Replacement_Group_Num NUMBER,
720 X_Setup_Id NUMBER
721 ) IS
722 BEGIN
723 UPDATE WIP_SUB_OPERATION_RESOURCES
724 SET
725 wip_entity_id = X_Wip_Entity_Id,
726 operation_seq_num = X_Operation_Seq_Num,
727 resource_seq_num = X_Resource_Seq_Num,
728 organization_id = X_Organization_Id,
729 repetitive_schedule_id = X_Repetitive_Schedule_Id,
730 last_update_date = X_Last_Update_Date,
731 last_updated_by = X_Last_Updated_By,
732 last_update_login = X_Last_Update_Login,
733 resource_id = X_Resource_Id,
734 uom_code = X_Uom_Code,
735 basis_type = X_Basis_Type,
736 usage_rate_or_amount = X_Usage_Rate_Or_Amount,
737 activity_id = X_Activity_Id,
738 scheduled_flag = X_Scheduled_Flag,
739 assigned_units = X_Assigned_Units,
740 autocharge_type = X_Autocharge_Type,
741 standard_rate_flag = X_Standard_Rate_Flag,
742 applied_resource_units = X_Applied_Resource_Units,
743 applied_resource_value = X_Applied_Resource_Value,
744 attribute_category = X_Attribute_Category,
745 attribute1 = X_Attribute1,
746 attribute2 = X_Attribute2,
747 attribute3 = X_Attribute3,
748 attribute4 = X_Attribute4,
749 attribute5 = X_Attribute5,
750 attribute6 = X_Attribute6,
751 attribute7 = X_Attribute7,
752 attribute8 = X_Attribute8,
753 attribute9 = X_Attribute9,
754 attribute10 = X_Attribute10,
755 attribute11 = X_Attribute11,
756 attribute12 = X_Attribute12,
757 attribute13 = X_Attribute13,
758 attribute14 = X_Attribute14,
759 attribute15 = X_Attribute15,
760 completion_date = X_Completion_Date,
761 start_date = X_Start_Date,
762 schedule_seq_num = X_Schedule_Seq_Num,
763 substitute_group_num = X_Substitute_Group_Num,
764 replacement_group_num = X_Replacement_Group_Num,
765 setup_id = X_Setup_Id
766 WHERE rowid = X_rowid;
767
768 if (SQL%NOTFOUND) then
769 Raise NO_DATA_FOUND;
770 end if;
771
772 END Update_Row;
773
774 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
775 BEGIN
776 DELETE FROM WIP_SUB_OPERATION_RESOURCES
777 WHERE rowid = X_Rowid;
778
779 if (SQL%NOTFOUND) then
780 Raise NO_DATA_FOUND;
781 end if;
782 END Delete_Row;
783
784
785 -- Procedure to replace the substitute resource into the WOR table
786 -- Swapping the original one into the child WSOR table
787 PROCEDURE Replace_Resources(
788 l_Wip_Entity_Id IN NUMBER,
789 l_Repetitive_Sched_Id IN NUMBER DEFAULT NULL,
790 l_Operation_Seq_Num IN NUMBER,
791 l_Substitute_Group_Num IN NUMBER,
792 l_Replacement_Group_Num IN NUMBER,
793 x_status OUT NOCOPY VARCHAR2,
794 x_msg_count OUT NOCOPY NUMBER,
795 x_msg_data OUT NOCOPY VARCHAR2)
796 IS
797 applied_units NUMBER;
798 l_replacement_groups_exist NUMBER;
799 l_dummy2 VARCHAR2(1);
800 l_logLevel number;
801 l_supply_subinventory VARCHAR2(30) := NULL;
802 l_supply_locator_id NUMBER := NULL;
803 l_params wip_logger.param_tbl_t;
804 l_line_id NUMBER := NULL;
805 l_org_id NUMBER;
806 l_ret_exp_status boolean := true;
807
808 l_pending_clocks VARCHAR2(1); --Bug#4715338
809
810 CURSOR res IS
811 SELECT resource_seq_num
812 FROM WIP_OPERATION_RESOURCES
813 WHERE
814 wip_entity_id = l_Wip_Entity_Id and
815 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
816 operation_seq_num = l_Operation_Seq_Num and
817 substitute_group_num = l_Substitute_Group_Num;
818
819 BEGIN
820 l_logLevel := fnd_log.g_current_runtime_level;
821
822 SAVEPOINT start_point;
823
824 if (l_logLevel <= wip_constants.trace_logging) then
825 l_params(1).paramName := 'l_Wip_Entity_Id';
826 l_params(1).paramValue := l_Wip_Entity_Id;
827 l_params(2).paramName := 'l_Repetitive_Sched_Id';
828 l_params(2).paramValue := l_Repetitive_Sched_Id;
829 l_params(3).paramName := 'l_Operation_Seq_Num';
830 l_params(3).paramValue := l_Operation_Seq_Num;
831 l_params(4).paramName := 'l_Substitute_Group_Num';
832 l_params(4).paramValue := l_Substitute_Group_Num;
833 l_params(5).paramName := 'l_Replacement_Group_Num';
834 l_params(5).paramValue := l_Replacement_Group_Num;
835 wip_logger.entryPoint(p_procName => 'wip_sub_op_resources_pkg.replace_resources',
836 p_params => l_params,
837 x_returnStatus => x_status);
838 if(x_status <> fnd_api.g_ret_sts_success) then
839 raise fnd_api.g_exc_unexpected_error;
840 end if;
841 end if;
842
843 SELECT COUNT(*) into l_replacement_groups_exist
844 FROM wip_operation_resources
845 WHERE wip_entity_id = l_Wip_Entity_Id and
846 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
847 operation_seq_num = l_Operation_Seq_Num and
848 substitute_group_num = l_Substitute_Group_Num and
849 replacement_group_num = nvl(l_Replacement_Group_Num,0);
850
851 if (l_replacement_groups_exist > 0) then
852 return;
853 end if;
854
855 select organization_id
856 into l_org_id
857 from wip_entities
858 where wip_entity_id = l_wip_entity_id;
859
860
861 /* BUG 4715338 -> CAN'T SUBSTITUTE RESOURCE, IF THERE ARE PENDING CLOCK-INS. */
862 L_PENDING_CLOCKS := WIP_WS_TIME_ENTRY.IS_CLOCK_PENDING(l_Wip_Entity_Id, l_operation_seq_num);
863 IF (L_PENDING_CLOCKS <> 'N') THEN
864 FND_MESSAGE.SET_NAME(APPLICATION => 'WIP',
865 NAME => 'WIP_PENDING_CLOCKS');
866 FND_MSG_PUB.ADD;
867 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
868 END IF;
869 /* BUG 4715338 - END */
870
871 if (l_repetitive_sched_id IS NOT NULL) then
872 select line_id
873 into l_line_id
874 from wip_repetitive_schedules
875 where wip_entity_id = l_wip_entity_id
876 and repetitive_schedule_id = l_repetitive_sched_id;
877 end if;
878
879 IF (Applied_Primary_Res(p_org_id => l_org_id,
880 p_wip_entity_id => l_wip_entity_id,
881 p_op_seq => l_operation_seq_num,
882 p_sub_group => l_substitute_group_num,
883 p_line_id => l_line_id) = TRUE) THEN
884 fnd_message.set_name(application => 'WIP',
885 name => 'WIP_REPLACE_APPLIED_RES');
886 fnd_msg_pub.add;
887 raise fnd_api.g_exc_unexpected_error;
888 END IF;
889
890 --
891 --Bug#4675116, "WIP exceptions" should be resolved
892 --for this resource when alternates are assigned
893 --
894 l_ret_exp_status := WIP_WS_EXCEPTIONS.close_exception_alt_res
895 (
896 p_wip_entity_id => l_wip_entity_id,
897 p_operation_seq_num => l_operation_seq_num,
898 p_substitute_group_num => l_substitute_group_num,
899 p_organization_id => l_org_id
900 );
901
902
903 INSERT INTO WIP_SUB_OPERATION_RESOURCES(
904 wip_entity_id,
905 operation_seq_num,
906 resource_seq_num,
907 organization_id,
908 repetitive_schedule_id,
909 last_update_date,
910 last_updated_by,
911 creation_date,
912 created_by,
913 last_update_login,
914 resource_id,
915 uom_code,
916 basis_type,
917 usage_rate_or_amount,
918 activity_id,
919 scheduled_flag,
920 assigned_units,
921 maximum_assigned_units,
922 autocharge_type,
923 standard_rate_flag,
924 applied_resource_units,
925 applied_resource_value,
926 attribute_category,
927 attribute1,
928 attribute2,
929 attribute3,
930 attribute4,
931 attribute5,
932 attribute6,
933 attribute7,
934 attribute8,
935 attribute9,
936 attribute10,
937 attribute11,
938 attribute12,
939 attribute13,
940 attribute14,
941 attribute15,
942 completion_date,
943 start_date,
944 schedule_seq_num,
945 substitute_group_num,
946 replacement_group_num,
947 setup_id)
948 SELECT
949 wip_entity_id,
950 operation_seq_num,
951 resource_seq_num,
952 organization_id,
953 repetitive_schedule_id,
954 last_update_date,
955 last_updated_by,
956 creation_date,
957 created_by,
958 last_update_login,
959 resource_id,
960 uom_code,
961 basis_type,
962 usage_rate_or_amount,
963 activity_id,
964 scheduled_flag,
965 assigned_units,
966 maximum_assigned_units,
967 autocharge_type,
968 standard_rate_flag,
969 applied_resource_units,
970 applied_resource_value,
971 attribute_category,
972 attribute1,
973 attribute2,
974 attribute3,
975 attribute4,
976 attribute5,
977 attribute6,
978 attribute7,
979 attribute8,
980 attribute9,
981 attribute10,
982 attribute11,
983 attribute12,
984 attribute13,
985 attribute14,
986 attribute15,
987 completion_date,
988 start_date,
989 schedule_seq_num,
990 substitute_group_num,
991 nvl(replacement_group_num, 0),
992 setup_id
993 FROM WIP_OPERATION_RESOURCES
994 WHERE
995 wip_entity_id = l_Wip_Entity_Id and
996 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
997 operation_seq_num = l_Operation_Seq_Num and
998 substitute_group_num = l_Substitute_Group_Num and
999 parent_resource_seq IS NULL;
1000
1001 if (SQL%NOTFOUND) then
1002 ROLLBACK to start_point;
1003 Raise NO_DATA_FOUND;
1004 end if;
1005
1006 -- Deleting from wip_operation_resources and resource_usage
1007 -- and resource_instances
1008
1009 FOR res_rec IN res LOOP
1010
1011 DELETE FROM WIP_OPERATION_RESOURCES
1012 WHERE
1013 wip_entity_id = l_Wip_Entity_Id and
1014 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
1015 operation_seq_num = l_Operation_Seq_Num and
1016 resource_seq_num = res_rec.resource_seq_num;
1017
1018 if (SQL%NOTFOUND) then
1019 ROLLBACK to start_point;
1020 Raise NO_DATA_FOUND;
1021 end if;
1022
1023 -- delete all setup resources
1024 DELETE FROM WIP_OPERATION_RESOURCES
1025 WHERE
1026 wip_entity_id = l_Wip_Entity_Id and
1027 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
1028 operation_seq_num = l_Operation_Seq_Num and
1029 parent_resource_seq = res_rec.resource_seq_num;
1030
1031 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
1032 WHERE
1033 wip_entity_id = l_Wip_Entity_Id and
1034 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
1035 operation_seq_num = l_Operation_Seq_Num and
1036 resource_seq_num = res_rec.resource_seq_num;
1037
1038 DELETE FROM WIP_OP_RESOURCE_INSTANCES
1039 WHERE
1040 wip_entity_id = l_Wip_Entity_Id and
1041 operation_seq_num = l_Operation_Seq_Num and
1042 resource_seq_num = res_rec.resource_seq_num;
1043
1044 END LOOP;
1045
1046
1047
1048 INSERT INTO WIP_OPERATION_RESOURCES(
1049 wip_entity_id,
1050 operation_seq_num,
1051 resource_seq_num,
1052 organization_id,
1053 repetitive_schedule_id,
1054 last_update_date,
1055 last_updated_by,
1056 creation_date,
1057 created_by,
1058 last_update_login,
1059 resource_id,
1060 uom_code,
1061 basis_type,
1062 usage_rate_or_amount,
1063 activity_id,
1064 scheduled_flag,
1065 assigned_units,
1066 maximum_assigned_units,
1067 autocharge_type,
1068 standard_rate_flag,
1069 applied_resource_units,
1070 applied_resource_value,
1071 attribute_category,
1072 attribute1,
1073 attribute2,
1074 attribute3,
1075 attribute4,
1076 attribute5,
1077 attribute6,
1078 attribute7,
1079 attribute8,
1080 attribute9,
1081 attribute10,
1082 attribute11,
1083 attribute12,
1084 attribute13,
1085 attribute14,
1086 attribute15,
1087 completion_date,
1088 start_date,
1089 schedule_seq_num,
1090 substitute_group_num,
1091 replacement_group_num,
1092 parent_resource_seq,
1093 setup_id)
1094 SELECT
1095 wip_entity_id,
1096 operation_seq_num,
1097 resource_seq_num,
1098 organization_id,
1099 repetitive_schedule_id,
1100 last_update_date,
1101 last_updated_by,
1102 creation_date,
1103 created_by,
1104 last_update_login,
1105 resource_id,
1106 uom_code,
1107 basis_type,
1108 usage_rate_or_amount,
1109 activity_id,
1110 scheduled_flag,
1111 assigned_units,
1112 maximum_assigned_units,
1113 autocharge_type,
1114 standard_rate_flag,
1115 applied_resource_units,
1116 applied_resource_value,
1117 attribute_category,
1118 attribute1,
1119 attribute2,
1120 attribute3,
1121 attribute4,
1122 attribute5,
1123 attribute6,
1124 attribute7,
1125 attribute8,
1126 attribute9,
1127 attribute10,
1128 attribute11,
1129 attribute12,
1130 attribute13,
1131 attribute14,
1132 attribute15,
1133 completion_date,
1134 start_date,
1135 schedule_seq_num,
1136 substitute_group_num,
1137 replacement_group_num,
1138 NULL,
1139 setup_id
1140 FROM WIP_SUB_OPERATION_RESOURCES
1141 WHERE
1142 wip_entity_id = l_Wip_Entity_Id and
1143 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
1144 operation_seq_num = l_Operation_Seq_Num and
1145 substitute_group_num = l_Substitute_Group_Num and
1146 replacement_group_num = nvl(l_Replacement_Group_Num,0);
1147
1148 if (SQL%NOTFOUND) then
1149 ROLLBACK to start_point;
1150 Raise NO_DATA_FOUND;
1151 end if;
1152
1153 BEGIN
1154 -- Overwrite subinv/loc in WRO for pull components w/ the subinv/loc
1155 -- associated w/ the replacement resource
1156 select br1.supply_subinventory, br1.supply_locator_id
1157 into l_supply_subinventory, l_supply_locator_id
1158 from bom_resources br1, WIP_SUB_OPERATION_RESOURCES wsor1
1159 where br1.resource_id = wsor1.resource_id
1160 and br1.organization_id = wsor1.organization_id
1161 and wsor1.wip_entity_id = l_Wip_Entity_Id
1162 and NVL(wsor1.repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1)
1163 and wsor1.operation_seq_num = l_Operation_Seq_Num
1164 and wsor1.substitute_group_num = l_Substitute_Group_Num
1165 and wsor1.replacement_group_num = nvl(l_Replacement_Group_Num,0)
1166 and wsor1.resource_seq_num in
1167 (select min(wsor2.resource_seq_num)
1168 from bom_resources br2, WIP_SUB_OPERATION_RESOURCES wsor2
1169 where wsor2.wip_entity_id = wsor1.wip_entity_id
1170 and NVL(wsor2.repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1)
1171 and wsor2.organization_id= wsor1.organization_id
1172 and wsor2.operation_seq_num = wsor1.operation_seq_num
1173 and wsor2.substitute_group_num = wsor1.substitute_group_num
1174 and wsor2.replacement_group_num = wsor1.replacement_group_num
1175 and br2.supply_subinventory is not null
1176 and br2.organization_id = wsor2.organization_id
1177 and br2.resource_id = wsor2.resource_id
1178 and br2.resource_type= 1); -- machine type
1179
1180 /* Removed the exception handler code that was here before for fixing FP bug4740503 */
1181
1182 if (l_supply_subinventory is null) then
1183 begin
1184 select wp.default_pull_supply_subinv, wp.default_pull_supply_locator_id
1185 into l_supply_subinventory, l_supply_locator_id
1186 from wip_parameters wp, wip_entities we
1187 where we.wip_entity_id = l_Wip_Entity_Id
1188 and wp.organization_id = we.organization_id;
1189 exception when others then
1190 l_supply_subinventory := null;
1191 end;
1192 end if;
1193
1194 wip_picking_pub.Update_Requirement_SubinvLoc(p_wip_entity_id => l_Wip_Entity_Id,
1195 p_repetitive_schedule_id => l_Repetitive_Sched_Id,
1196 p_operation_seq_num => l_Operation_Seq_Num,
1197 p_supply_subinventory => l_supply_subinventory,
1198 p_supply_locator_id => l_supply_locator_id,
1199 x_return_status => x_status,
1200 x_msg_data => x_msg_data);
1201
1202 if (x_status <> fnd_api.g_ret_sts_success) then
1203 if (l_logLevel <= wip_constants.trace_logging) then
1204 wip_logger.log('WIP_SUB_OP_RESOURCES_PKG.Replace_Resources: ' ||
1205 'wip_picking_pub.Update_Requirement_SubinvLoc failed..', l_dummy2);
1206 end if;
1207 raise fnd_api.g_exc_unexpected_error;
1208 end if;
1209
1210 /* Fix for bug 4996849. Added following three lines */
1211 exception when no_data_found then null ;
1212 end;
1213 /* End of this block for bug 4996849 fix. */
1214
1215 DELETE FROM WIP_SUB_OPERATION_RESOURCES
1216 WHERE
1217 wip_entity_id = l_Wip_Entity_Id and
1218 NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
1219 operation_seq_num = l_Operation_Seq_Num and
1220 substitute_group_num = l_Substitute_Group_Num and
1221 replacement_group_num = nvl(l_Replacement_Group_Num,0);
1222
1223 if (SQL%NOTFOUND) then
1224 ROLLBACK to start_point;
1225 Raise NO_DATA_FOUND;
1226 end if;
1227
1228 /* Fix for bug 4996849. Comment following lines as delete statement is required.*/
1229 /*
1230 exception when others then null ;
1231 end;
1232 */
1233 /* End of this block for bug 4996849 fix. */
1234
1235 x_status := fnd_api.g_ret_sts_success;
1236 if (l_logLevel <= wip_constants.trace_logging) then
1237 wip_logger.exitPoint(p_procName => 'wip_sub_op_resources_pkg.replace_resources',
1238 p_procReturnStatus => x_status,
1239 p_msg => 'procedure succeeded',
1240 x_returnStatus => l_dummy2); --discard logging return status
1241 end if;
1242 EXCEPTION
1243 WHEN others THEN
1244 x_msg_count := fnd_msg_pub.count_msg;
1245 wip_utilities.get_message_stack(p_delete_stack => fnd_api.g_true,
1246 p_msg => x_msg_data);
1247 x_status := fnd_api.g_ret_sts_error;
1248 ROLLBACK to start_point;
1249 END Replace_Resources;
1250
1251 FUNCTION Applied_Primary_Res(
1252 p_org_id IN NUMBER,
1253 p_wip_entity_id IN NUMBER,
1254 p_op_seq IN NUMBER,
1255 p_sub_group IN NUMBER,
1256 p_line_id IN NUMBER) RETURN BOOLEAN
1257 IS
1258
1259 -- Cursor SQL Modified for bug 5235559.
1260 cursor primary_res is
1261 select wor.applied_resource_units,
1262 wor.resource_seq_num
1263 from wip_operation_resources wor
1264 where wor.organization_id = p_org_id
1265 and wor.wip_entity_id = p_wip_entity_id
1266 and wor.operation_seq_num = p_op_seq
1267 and wor.substitute_group_num = decode(wor.parent_resource_seq,NULL,p_sub_group,wor.substitute_group_num)
1268 and (wor.parent_resource_seq is null or wor.parent_resource_seq in
1269 (select wor.resource_seq_num
1270 from wip_operation_resources wor1
1271 where wor1.organization_id = p_org_id
1272 and wor1.wip_entity_id = p_wip_entity_id
1273 and wor1.operation_seq_num = p_op_seq
1274 and wor1.substitute_group_num = p_sub_group));
1275
1276 BEGIN
1277 for cur_primary_res in primary_res loop
1278 if (cur_primary_res.applied_resource_units <> 0 or
1279 wip_op_resources_utilities.pending_transactions(
1280 p_wip_entity_id,
1281 p_org_id,
1282 p_op_seq,
1283 cur_primary_res.resource_seq_num,
1284 p_line_id) = TRUE) then
1285 return true;
1286 end if;
1287 end loop;
1288
1289 return false;
1290 END Applied_Primary_Res;
1291
1292
1293 END WIP_SUB_OP_RESOURCES_PKG;