[Home] [Help]
PACKAGE BODY: APPS.WIP_OPERATION_RESOURCES_PKG
Source
1 PACKAGE BODY WIP_OPERATION_RESOURCES_PKG AS
2 /* $Header: wiporesb.pls 120.1 2005/06/20 14:46:44 appldev 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 := FND_GLOBAL.USER_ID;
18 x_login_id number := FND_GLOBAL.LOGIN_ID;
19 x_check_for_dup boolean := FALSE;
20 begin
21 -- insert operation resource record
22 begin
23 insert into wip_operation_resources(
24 last_update_date,
25 last_updated_by,
26 creation_date,
27 created_by,
28 last_update_login,
29 organization_id,
30 wip_entity_id,
31 repetitive_schedule_id,
32 operation_seq_num,
33 resource_seq_num,
34 resource_id,
35 uom_code,
36 basis_type,
37 activity_id,
38 standard_rate_flag,
39 usage_rate_or_amount,
40 scheduled_flag,
41 assigned_units,
42 autocharge_type,
43 applied_resource_units,
44 applied_resource_value,
45 start_date,
46 completion_date
47 ) values (
48 SYSDATE,
49 x_user_id,
50 SYSDATE,
51 x_user_id,
52 x_login_id,
53 p_org_id,
54 p_wip_entity_id,
55 p_first_schedule_id,
56 p_operation_seq_num,
57 p_resource_seq_num,
58 p_resource_id,
59 p_uom_code,
60 nvl(p_basis_type, WIP_CONSTANTS.PER_LOT),
61 p_activity_id,
62 p_standard_rate_flag,
63 0, -- usage_rate_or_amount
64 WIP_CONSTANTS.NO, -- scheduled_flag
65 1, -- assigned_units
66 WIP_CONSTANTS.MANUAL, -- autocharge_type
67 0, -- applied_resource_units
68 0, -- applied_resource_value
69 p_start_date,
70 p_completion_date);
71
72 exception
73 when DUP_VAL_ON_INDEX then
74 x_check_for_dup := TRUE;
75 end;
76
77 if (x_check_for_dup) then
78 -- the primary key already exists, so check to see
79 -- if the old record matches the new record.
80 declare
81 cursor get_ident_resource(
82 c_org_id number,
83 c_wip_entity_id number,
84 c_operation_seq_num number,
85 c_resource_seq_num number,
86 c_first_schedule_id number,
87 c_resource_id number) is
88 select 'X'
89 from dual
90 where c_resource_id =
91 (select resource_id
92 from wip_operation_resources
93 where organization_id = c_org_id
94 and wip_entity_id = c_wip_entity_id
95 and operation_seq_num = c_operation_seq_num
96 and resource_seq_num = c_resource_seq_num
97 and nvl(repetitive_schedule_id,-1) = nvl(c_first_schedule_id,-1));
98
99 x_dummy varchar2(1);
100 x_found boolean;
101 begin
102 open get_ident_resource(
103 c_org_id => p_org_id,
104 c_wip_entity_id => p_wip_entity_id,
105 c_operation_seq_num => p_operation_seq_num,
106 c_resource_seq_num => p_resource_seq_num,
107 c_first_schedule_id => p_first_schedule_id,
108 c_resource_id => p_resource_id);
109 fetch get_ident_resource into x_dummy;
110 x_found := get_ident_resource%FOUND;
111 close get_ident_resource;
112
113 if (not x_found) then
114 fnd_message.set_name(
115 application => 'WIP',
116 name => 'WIP_MISMATCHED_RES');
117 fnd_message.raise_error;
118 end if;
119 end;
120 end if;
121
122 return;
123
124 exception
125 when OTHERS then
126 wip_constants.get_ora_error(
127 application => 'WIP',
128 proc_name => 'WIP_OPERATION_RESOURCES_PKG.ADD_RESOURCE');
129 fnd_message.raise_error;
130 end add_resource;
131
132 procedure check_dup_resources(
133 p_group_id in number,
134 p_operation_seq_num out nocopy number,
135 p_resource_seq_num out nocopy number,
136 p_dup_exists out nocopy boolean) is
137
138 cursor get_dup_res(c_group_id number) is
139 select wcti1.operation_seq_num,
140 wcti1.resource_seq_num
141 from wip_cost_txn_interface wcti1,
142 wip_cost_txn_interface wcti2
143 where wcti1.source_code = 'NEW_RES'
144 and wcti1.group_id = c_group_id
145 and wcti1.source_code = wcti2.source_code
146 and wcti1.group_id = wcti2.group_id
147 and wcti1.wip_entity_id = wcti2.wip_entity_id
148 and wcti1.operation_seq_num = wcti2.operation_seq_num
149 and wcti1.resource_seq_num = wcti2.resource_seq_num
150 and wcti1.organization_id = wcti2.organization_id
151 and nvl(wcti1.repetitive_schedule_id, -1)
152 = nvl(wcti2.repetitive_schedule_id, -1)
153 and wcti1.resource_id <> wcti2.resource_id;
154
155 begin
156 -- get any added resources that have been duplicated
157 open get_dup_res(c_group_id => p_group_id);
158 fetch get_dup_res into p_operation_seq_num, p_resource_seq_num;
159 p_dup_exists := get_dup_res%FOUND;
160 close get_dup_res;
161 end check_dup_resources;
162
163 procedure add_resources(p_group_id in number) is
164 begin
165 -- add resources from interface table
166 -- note: if adding op on the fly, then this procedure should only be called
167 -- after the new op has been added to wip_operations
168 insert into wip_operation_resources(
169 organization_id,
170 wip_entity_id,
171 repetitive_schedule_id,
172 operation_seq_num,
173 resource_seq_num,
174 resource_id,
175 uom_code,
176 basis_type,
177 activity_id,
178 standard_rate_flag,
179 usage_rate_or_amount,
180 scheduled_flag,
181 assigned_units,
182 autocharge_type,
183 applied_resource_units,
184 applied_resource_value,
185 start_date,
186 completion_date,
187 last_update_date,
188 last_updated_by,
189 creation_date,
190 created_by,
191 last_update_login)
192 select distinct
193 wcti.organization_id,
194 wcti.wip_entity_id,
195 wcti.repetitive_schedule_id,
196 wcti.operation_seq_num,
197 wcti.resource_seq_num,
198 wcti.resource_id,
199 wcti.primary_uom,
200 nvl(wcti.basis_type, WIP_CONSTANTS.PER_LOT),
201 nvl(wcti.activity_id , br.default_activity_id),
202 wcti.standard_rate_flag,
203 0, -- usage_rate_or_amount
204 WIP_CONSTANTS.NO, -- scheduled_flag
205 1, -- assigned_units
206 WIP_CONSTANTS.MANUAL, -- autocharge_type
207 0, -- applied_resource_units
208 0, -- applied_resource_value
209 wo.first_unit_start_date,
210 wo.last_unit_completion_date,
211 SYSDATE,
212 wcti.last_updated_by,
213 SYSDATE,
214 wcti.created_by,
215 wcti.last_update_login
216 from bom_resources br,
217 wip_operations wo,
218 wip_cost_txn_interface wcti
219 where wcti.source_code = 'NEW_RES'
220 and wcti.group_id = p_group_id
221 and wcti.organization_id = wo.organization_id
222 and wcti.wip_entity_id = wo.wip_entity_id
223 and wcti.operation_seq_num = wo.operation_seq_num
224 and wcti.resource_id = br.resource_id
225 and nvl(wcti.repetitive_schedule_id, -1)
226 = nvl(wo.repetitive_schedule_id, -1);
227
228 -- delete txn qty = NULL records that are used for adding resources
229 -- changed condition from txn qty = 0 to txn qty is null for bug # 661593
230 delete from wip_cost_txn_interface
231 where group_id = p_group_id
232 and transaction_quantity is NULL;
233
234 -- clean up interface
235 update wip_cost_txn_interface
236 set source_code = NULL -- clear source code to remove NEW_RES message
237 where group_id = p_group_id;
238 end add_resources;
239
240 FUNCTION CHECK_PO_AND_REQ(
241 p_org_id IN NUMBER,
242 p_wip_entity_id IN NUMBER,
243 p_operation_seq_num IN NUMBER,
244 p_resource_seq_num IN NUMBER,
245 p_rep_sched_id IN NUMBER) RETURN BOOLEAN IS
246
247 CURSOR disc_check_po_req_cur IS
248 SELECT 'No PO/REQ Linked'
249 FROM DUAL
250 WHERE NOT EXISTS
251 (SELECT '1'
252 FROM PO_DISTRIBUTIONS_ALL PD,
253 WIP_OPERATION_RESOURCES WOR
254 /* Fixed bug 3115844 */
255 WHERE pd.po_line_id IS NOT NULL
256 AND pd.line_location_id IS NOT NULL
257 AND WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
258 AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
259 AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
260 AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM
261 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
262 AND WOR.ORGANIZATION_ID = p_org_id
263 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
264 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num)
265 AND NOT EXISTS
266 (SELECT '1'
267 FROM PO_REQUISITION_LINES_ALL PRL,
268 WIP_OPERATION_RESOURCES WOR
269 WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
270 AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
271 AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
272 AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM
273 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
274 AND WOR.ORGANIZATION_ID = p_org_id
275 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
276 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num)
277 AND NOT EXISTS
278 (SELECT '1'
279 FROM PO_REQUISITIONS_INTERFACE_ALL PRI,
280 WIP_OPERATION_RESOURCES WOR
281 WHERE WOR.WIP_ENTITY_ID = PRI.WIP_ENTITY_ID
282 AND WOR.ORGANIZATION_ID = PRI.DESTINATION_ORGANIZATION_ID
283 AND WOR.OPERATION_SEQ_NUM = PRI.WIP_OPERATION_SEQ_NUM
284 AND WOR.RESOURCE_SEQ_NUM = PRI.WIP_RESOURCE_SEQ_NUM
285 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
286 AND WOR.ORGANIZATION_ID = p_org_id
287 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
288 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num);
289
290 CURSOR rep_check_po_req_cur IS
291 SELECT 'No PO/REQ Linked'
292 FROM DUAL
293 WHERE NOT EXISTS
294 (SELECT '1'
295 FROM PO_DISTRIBUTIONS_ALL PD,
296 WIP_OPERATION_RESOURCES WOR
297 /* Fixed bug 3115844 */
298 WHERE pd.po_line_id IS NOT NULL
299 AND pd.line_location_id IS NOT NULL
300 AND WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
301 AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
302 AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
303 AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM
304 AND WOR.REPETITIVE_SCHEDULE_ID =
305 PD.WIP_REPETITIVE_SCHEDULE_ID
306 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
307 AND WOR.ORGANIZATION_ID = p_org_id
308 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
309 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
310 AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id)
311 AND NOT EXISTS
312 (SELECT '1'
313 FROM PO_REQUISITION_LINES_ALL PRL,
314 WIP_OPERATION_RESOURCES WOR
315 WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
316 AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
317 AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
318 AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM
319 AND WOR.REPETITIVE_SCHEDULE_ID =
320 PRL.WIP_REPETITIVE_SCHEDULE_ID
321 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
322 AND WOR.ORGANIZATION_ID = p_org_id
323 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
324 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
325 AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id)
326 AND NOT EXISTS
327 (SELECT '1'
328 FROM PO_REQUISITIONS_INTERFACE_ALL PRI,
329 WIP_OPERATION_RESOURCES WOR
330 WHERE WOR.WIP_ENTITY_ID = PRI.WIP_ENTITY_ID
331 AND WOR.ORGANIZATION_ID = PRI.DESTINATION_ORGANIZATION_ID
332 AND WOR.OPERATION_SEQ_NUM = PRI.WIP_OPERATION_SEQ_NUM
333 AND WOR.RESOURCE_SEQ_NUM = PRI.WIP_RESOURCE_SEQ_NUM
334 AND WOR.REPETITIVE_SCHEDULE_ID =
335 PRI.WIP_REPETITIVE_SCHEDULE_ID
336 AND WOR.WIP_ENTITY_ID = p_wip_entity_id
337 AND WOR.ORGANIZATION_ID = p_org_id
338 AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
339 AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
340 AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id);
341
342 po_req_exist VARCHAR2(20);
343
344 BEGIN
345 -- Check for POs and REQs linked to resource
346 IF p_rep_sched_id IS NULL THEN
347 OPEN disc_check_po_req_cur;
348 FETCH disc_check_po_req_cur INTO po_req_exist;
349
350 IF (disc_check_po_req_cur%NOTFOUND) THEN
351 CLOSE disc_check_po_req_cur;
352 RETURN FALSE;
353 ELSE
354 CLOSE disc_check_po_req_cur;
355 END IF;
356 ELSE
357 OPEN rep_check_po_req_cur;
358 FETCH rep_check_po_req_cur INTO po_req_exist;
359
360 IF (rep_check_po_req_cur%NOTFOUND) THEN
361 CLOSE rep_check_po_req_cur;
362 RETURN FALSE;
363 ELSE
364 CLOSE rep_check_po_req_cur;
365 END IF;
366 END IF;
367
368 RETURN TRUE;
369
370 END CHECK_PO_AND_REQ;
371
372
373 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
374 X_Wip_Entity_Id NUMBER,
375 X_Operation_Seq_Num NUMBER,
376 X_Resource_Seq_Num NUMBER,
377 X_Organization_Id NUMBER,
378 X_Repetitive_Schedule_Id NUMBER,
379 X_Last_Update_Date DATE,
380 X_Last_Updated_By NUMBER,
381 X_Creation_Date DATE,
382 X_Created_By NUMBER,
383 X_Last_Update_Login NUMBER,
384 X_Resource_Id NUMBER,
385 X_Uom_Code VARCHAR2,
386 X_Basis_Type NUMBER,
387 X_Usage_Rate_Or_Amount NUMBER,
388 X_Activity_Id NUMBER,
389 X_Scheduled_Flag NUMBER,
390 X_Assigned_Units NUMBER,
391 X_Autocharge_Type NUMBER,
392 X_Standard_Rate_Flag NUMBER,
393 X_Applied_Resource_Units NUMBER,
394 X_Applied_Resource_Value NUMBER,
395 X_Attribute_Category VARCHAR2,
396 X_Attribute1 VARCHAR2,
397 X_Attribute2 VARCHAR2,
398 X_Attribute3 VARCHAR2,
399 X_Attribute4 VARCHAR2,
400 X_Attribute5 VARCHAR2,
401 X_Attribute6 VARCHAR2,
402 X_Attribute7 VARCHAR2,
403 X_Attribute8 VARCHAR2,
404 X_Attribute9 VARCHAR2,
405 X_Attribute10 VARCHAR2,
406 X_Attribute11 VARCHAR2,
407 X_Attribute12 VARCHAR2,
408 X_Attribute13 VARCHAR2,
409 X_Attribute14 VARCHAR2,
410 X_Attribute15 VARCHAR2,
411 X_Completion_Date DATE,
412 X_Start_Date DATE,
413 X_Schedule_Seq_Num NUMBER,
414 X_Substitute_Group_Num NUMBER,
415 X_Replacement_Group_Num NUMBER,
416 X_Parent_Resource_Seq NUMBER,
417 X_SetUp_Id NUMBER DEFAULT NULL
418 ) IS
419 CURSOR C IS SELECT rowid FROM WIP_OPERATION_RESOURCES
420 WHERE wip_entity_id = X_Wip_Entity_Id
421 AND organization_id = X_Organization_Id
422 AND operation_seq_num = X_Operation_Seq_Num
423 AND resource_seq_num = X_Resource_Seq_Num
424 AND (repetitive_Schedule_id = X_Repetitive_Schedule_Id
425 OR (repetitive_schedule_id IS NULL
426 AND X_Repetitive_Schedule_Id IS NULL));
427
428 BEGIN
429 INSERT INTO WIP_OPERATION_RESOURCES(
430 wip_entity_id,
431 operation_seq_num,
432 resource_seq_num,
433 organization_id,
434 repetitive_schedule_id,
435 last_update_date,
436 last_updated_by,
437 creation_date,
438 created_by,
439 last_update_login,
440 resource_id,
441 uom_code,
442 basis_type,
443 usage_rate_or_amount,
444 activity_id,
445 scheduled_flag,
446 assigned_units,
447 autocharge_type,
448 standard_rate_flag,
449 applied_resource_units,
450 applied_resource_value,
451 attribute_category,
452 attribute1,
453 attribute2,
454 attribute3,
455 attribute4,
456 attribute5,
457 attribute6,
458 attribute7,
459 attribute8,
460 attribute9,
461 attribute10,
462 attribute11,
463 attribute12,
464 attribute13,
465 attribute14,
466 attribute15,
467 completion_date,
468 start_date,
469 schedule_seq_num,
470 substitute_group_num,
471 replacement_group_num,
472 parent_resource_seq,
473 setup_id
474 ) VALUES (
475 X_Wip_Entity_Id,
476 X_Operation_Seq_Num,
477 X_Resource_Seq_Num,
478 X_Organization_Id,
479 X_Repetitive_Schedule_Id,
480 X_Last_Update_Date,
481 X_Last_Updated_By,
482 X_Creation_Date,
483 X_Created_By,
484 X_Last_Update_Login,
485 X_Resource_Id,
486 X_Uom_Code,
487 X_Basis_Type,
488 X_Usage_Rate_Or_Amount,
489 X_Activity_Id,
490 X_Scheduled_Flag,
491 X_Assigned_Units,
492 X_Autocharge_Type,
493 X_Standard_Rate_Flag,
494 X_Applied_Resource_Units,
495 X_Applied_Resource_Value,
496 X_Attribute_Category,
497 X_Attribute1,
498 X_Attribute2,
499 X_Attribute3,
500 X_Attribute4,
501 X_Attribute5,
502 X_Attribute6,
503 X_Attribute7,
504 X_Attribute8,
505 X_Attribute9,
506 X_Attribute10,
507 X_Attribute11,
508 X_Attribute12,
509 X_Attribute13,
510 X_Attribute14,
511 X_Attribute15,
512 X_Completion_Date,
513 X_Start_Date,
514 X_Schedule_Seq_Num,
515 X_Substitute_Group_Num,
516 X_Replacement_Group_Num,
517 X_Parent_Resource_Seq,
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 ) IS
570 CURSOR C IS
571 SELECT *
572 FROM WIP_OPERATION_RESOURCES
573 WHERE rowid = X_Rowid
574 FOR UPDATE of Wip_Entity_Id NOWAIT;
575 Recinfo C%ROWTYPE;
576 BEGIN
577 OPEN C;
578 FETCH C INTO Recinfo;
579 if (C%NOTFOUND) then
580 CLOSE C;
581 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
582 FND_MESSAGE.raise_error;
583 APP_EXCEPTION.raise_exception;
584 end if;
585 CLOSE C;
586 if (
587
588 (Recinfo.wip_entity_id = X_Wip_Entity_Id)
589 AND (Recinfo.operation_seq_num = X_Operation_Seq_Num)
590 AND (Recinfo.resource_seq_num = X_Resource_Seq_Num)
591 AND (Recinfo.organization_id = X_Organization_Id)
592 AND ( (Recinfo.repetitive_schedule_id = X_Repetitive_Schedule_Id)
593 OR ( (Recinfo.repetitive_schedule_id IS NULL)
594 AND (X_Repetitive_Schedule_Id IS NULL)))
595 AND (Recinfo.resource_id = X_Resource_Id)
596 AND ( (Recinfo.uom_code = X_Uom_Code)
597 OR ( (Recinfo.uom_code IS NULL)
598 AND (X_Uom_Code IS NULL)))
599 AND (Recinfo.basis_type = X_Basis_Type)
600 AND (ROUND(Recinfo.usage_rate_or_amount, 6) = X_Usage_Rate_Or_Amount)
601 AND ( (Recinfo.activity_id = X_Activity_Id)
602 OR ( (Recinfo.activity_id IS NULL)
603 AND (X_Activity_Id IS NULL)))
604 AND (Recinfo.scheduled_flag = X_Scheduled_Flag)
605 AND ( (Recinfo.assigned_units = X_Assigned_Units)
606 OR ( (Recinfo.assigned_units IS NULL)
607 AND (X_Assigned_Units IS NULL)))
608 AND (Recinfo.autocharge_type = X_Autocharge_Type)
609 AND (Recinfo.standard_rate_flag = X_Standard_Rate_Flag)
610 AND (Recinfo.applied_resource_units = X_Applied_Resource_Units)
611 AND (Recinfo.applied_resource_value = X_Applied_Resource_Value)
612 AND ( (Recinfo.attribute_category = X_Attribute_Category)
613 OR ( (Recinfo.attribute_category IS NULL)
614 AND (X_Attribute_Category IS NULL)))
615 AND ( (Recinfo.attribute1 = X_Attribute1)
616 OR ( (Recinfo.attribute1 IS NULL)
617 AND (X_Attribute1 IS NULL)))
618 AND ( (Recinfo.attribute2 = X_Attribute2)
619 OR ( (Recinfo.attribute2 IS NULL)
620 AND (X_Attribute2 IS NULL)))
621 AND ( (Recinfo.attribute3 = X_Attribute3)
622 OR ( (Recinfo.attribute3 IS NULL)
623 AND (X_Attribute3 IS NULL)))
624 AND ( (Recinfo.attribute4 = X_Attribute4)
625 OR ( (Recinfo.attribute4 IS NULL)
626 AND (X_Attribute4 IS NULL)))
627 AND ( (Recinfo.attribute5 = X_Attribute5)
628 OR ( (Recinfo.attribute5 IS NULL)
629 AND (X_Attribute5 IS NULL)))
630 AND ( (Recinfo.attribute6 = X_Attribute6)
631 OR ( (Recinfo.attribute6 IS NULL)
632 AND (X_Attribute6 IS NULL)))
633 AND ( (Recinfo.attribute7 = X_Attribute7)
634 OR ( (Recinfo.attribute7 IS NULL)
635 AND (X_Attribute7 IS NULL)))
636 AND ( (Recinfo.attribute8 = X_Attribute8)
637 OR ( (Recinfo.attribute8 IS NULL)
638 AND (X_Attribute8 IS NULL)))
639 AND ( (Recinfo.attribute9 = X_Attribute9)
640 OR ( (Recinfo.attribute9 IS NULL)
641 AND (X_Attribute9 IS NULL)))
642 AND ( (Recinfo.attribute10 = X_Attribute10)
643 OR ( (Recinfo.attribute10 IS NULL)
644 AND (X_Attribute10 IS NULL)))
645 AND ( (Recinfo.attribute11 = X_Attribute11)
646 OR ( (Recinfo.attribute11 IS NULL)
647 AND (X_Attribute11 IS NULL)))
648 AND ( (Recinfo.attribute12 = X_Attribute12)
649 OR ( (Recinfo.attribute12 IS NULL)
650 AND (X_Attribute12 IS NULL)))
651 AND ( (Recinfo.attribute13 = X_Attribute13)
652 OR ( (Recinfo.attribute13 IS NULL)
653 AND (X_Attribute13 IS NULL)))
654 AND ( (Recinfo.attribute14 = X_Attribute14)
655 OR ( (Recinfo.attribute14 IS NULL)
656 AND (X_Attribute14 IS NULL)))
657 AND ( (Recinfo.attribute15 = X_Attribute15)
658 OR ( (Recinfo.attribute15 IS NULL)
659 AND (X_Attribute15 IS NULL)))
660 AND ( (Recinfo.schedule_seq_num = X_Schedule_Seq_Num)
661 OR ( (Recinfo.Schedule_Seq_Num IS NULL)
662 AND (X_Schedule_Seq_Num IS NULL)))
663 AND ( (Recinfo.substitute_group_num = X_Substitute_Group_Num)
664 OR ( (Recinfo.Substitute_Group_Num IS NULL)
665 AND (X_Substitute_Group_Num IS NULL)))
666 AND (Recinfo.completion_date = X_Completion_Date)
667 AND (Recinfo.start_date = X_Start_Date)
668
669 ) then
670 return;
671 else
672 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
673 FND_MESSAGE.raise_error;
674 APP_EXCEPTION.RAISE_EXCEPTION;
675 end if;
676 END Lock_Row;
677
678 PROCEDURE Update_Row(X_Rowid VARCHAR2,
679 X_Wip_Entity_Id NUMBER,
680 X_Operation_Seq_Num NUMBER,
681 X_Resource_Seq_Num NUMBER,
682 X_Organization_Id NUMBER,
683 X_Repetitive_Schedule_Id NUMBER,
684 X_Last_Update_Date DATE,
685 X_Last_Updated_By NUMBER,
686 X_Last_Update_Login NUMBER,
687 X_Resource_Id NUMBER,
688 X_Uom_Code VARCHAR2,
689 X_Basis_Type NUMBER,
690 X_Usage_Rate_Or_Amount NUMBER,
691 X_Activity_Id NUMBER,
692 X_Scheduled_Flag NUMBER,
693 X_Assigned_Units NUMBER,
694 X_Autocharge_Type NUMBER,
695 X_Standard_Rate_Flag NUMBER,
696 X_Applied_Resource_Units NUMBER,
697 X_Applied_Resource_Value NUMBER,
698 X_Attribute_Category VARCHAR2,
699 X_Attribute1 VARCHAR2,
700 X_Attribute2 VARCHAR2,
701 X_Attribute3 VARCHAR2,
702 X_Attribute4 VARCHAR2,
703 X_Attribute5 VARCHAR2,
704 X_Attribute6 VARCHAR2,
705 X_Attribute7 VARCHAR2,
706 X_Attribute8 VARCHAR2,
707 X_Attribute9 VARCHAR2,
708 X_Attribute10 VARCHAR2,
709 X_Attribute11 VARCHAR2,
710 X_Attribute12 VARCHAR2,
711 X_Attribute13 VARCHAR2,
712 X_Attribute14 VARCHAR2,
713 X_Attribute15 VARCHAR2,
714 X_Completion_Date DATE,
715 X_Start_Date DATE,
716 X_Schedule_Seq_Num NUMBER,
717 X_Substitute_Group_Num NUMBER,
718 X_Replacement_Group_Num NUMBER,
719 X_Parent_Resource_Seq NUMBER,
720 X_SetUp_Id NUMBER DEFAULT NULL
721 ) IS
722 BEGIN
723 UPDATE WIP_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 parent_resource_seq = X_Parent_Resource_Seq,
766 setup_id = X_SetUp_Id
767 WHERE rowid = X_rowid;
768
769 if (SQL%NOTFOUND) then
770 Raise NO_DATA_FOUND;
771 end if;
772
773 END Update_Row;
774
775 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
776 BEGIN
777 DELETE FROM WIP_OPERATION_RESOURCES
778 WHERE rowid = X_Rowid;
779
780 if (SQL%NOTFOUND) then
781 Raise NO_DATA_FOUND;
782 end if;
783 END Delete_Row;
784
785 END WIP_OPERATION_RESOURCES_PKG;