[Home] [Help]
PACKAGE BODY: APPS.BOM_SUB_RESOURCES_PKG
Source
1 PACKAGE BODY BOM_SUB_RESOURCES_PKG as
2 /* $Header: BOMSRESB.pls 120.2.12000000.2 2007/10/17 13:47:02 jiabraha ship $ */
3
4 PROCEDURE Insert_Row(
5 x_OPERATION_SEQUENCE_ID NUMBER,
6 x_SUBSTITUTE_GROUP_NUM NUMBER,
7 x_RESOURCE_ID NUMBER,
8 x_SCHEDULE_SEQ_NUM NUMBER,
9 x_REPLACEMENT_GROUP_NUM NUMBER,
10 x_ACTIVITY_ID NUMBER,
11 x_STANDARD_RATE_FLAG NUMBER,
12 x_ASSIGNED_UNITS NUMBER,
13 x_USAGE_RATE_OR_AMOUNT NUMBER,
14 x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
15 x_BASIS_TYPE NUMBER,
16 x_SCHEDULE_FLAG NUMBER,
17 x_LAST_UPDATE_DATE DATE,
18 x_LAST_UPDATED_BY NUMBER,
19 x_CREATION_DATE DATE,
20 x_CREATED_BY NUMBER,
21 x_LAST_UPDATE_LOGIN NUMBER,
22 x_RESOURCE_OFFSET_PERCENT NUMBER,
23 x_AUTOCHARGE_TYPE NUMBER,
24 x_PRINCIPLE_FLAG NUMBER,
25 x_ATTRIBUTE_CATEGORY VARCHAR2,
26 x_ATTRIBUTE1 VARCHAR2,
27 x_ATTRIBUTE2 VARCHAR2,
28 x_ATTRIBUTE3 VARCHAR2,
29 x_ATTRIBUTE4 VARCHAR2,
30 x_ATTRIBUTE5 VARCHAR2,
31 x_ATTRIBUTE6 VARCHAR2,
32 x_ATTRIBUTE7 VARCHAR2,
33 x_ATTRIBUTE8 VARCHAR2,
34 x_ATTRIBUTE9 VARCHAR2,
35 x_ATTRIBUTE10 VARCHAR2,
36 x_ATTRIBUTE11 VARCHAR2,
37 x_ATTRIBUTE12 VARCHAR2,
38 x_ATTRIBUTE13 VARCHAR2,
39 x_ATTRIBUTE14 VARCHAR2,
40 x_ATTRIBUTE15 VARCHAR2)
41 IS
42 BEGIN
43 INSERT INTO BOM_SUB_OPERATION_RESOURCES(
44 OPERATION_SEQUENCE_ID,
45 SUBSTITUTE_GROUP_NUM,
46 RESOURCE_ID,
47 SCHEDULE_SEQ_NUM,
48 REPLACEMENT_GROUP_NUM,
49 ACTIVITY_ID,
50 STANDARD_RATE_FLAG,
51 ASSIGNED_UNITS,
52 USAGE_RATE_OR_AMOUNT,
53 USAGE_RATE_OR_AMOUNT_INVERSE,
54 BASIS_TYPE,
55 SCHEDULE_FLAG,
56 LAST_UPDATE_DATE,
57 LAST_UPDATED_BY,
58 CREATION_DATE,
59 CREATED_BY,
60 LAST_UPDATE_LOGIN,
61 RESOURCE_OFFSET_PERCENT,
62 AUTOCHARGE_TYPE,
63 PRINCIPLE_FLAG,
64 attribute_category,
65 attribute1,
66 attribute2,
67 attribute3,
68 attribute4,
69 attribute5,
70 attribute6,
71 attribute7,
72 attribute8,
73 attribute9,
74 attribute10,
75 attribute11,
76 attribute12,
77 attribute13,
78 attribute14,
79 attribute15)
80 values (
81 x_OPERATION_SEQUENCE_ID,
82 x_SUBSTITUTE_GROUP_NUM,
83 x_RESOURCE_ID,
84 x_SCHEDULE_SEQ_NUM,
85 x_REPLACEMENT_GROUP_NUM,
86 x_ACTIVITY_ID,
87 x_STANDARD_RATE_FLAG,
88 x_ASSIGNED_UNITS,
89 x_USAGE_RATE_OR_AMOUNT,
90 x_USAGE_RATE_OR_AMOUNT_INVERSE,
91 x_BASIS_TYPE,
92 x_SCHEDULE_FLAG,
93 x_LAST_UPDATE_DATE,
94 x_LAST_UPDATED_BY,
95 x_CREATION_DATE,
96 x_CREATED_BY,
97 x_LAST_UPDATE_LOGIN,
98 x_RESOURCE_OFFSET_PERCENT,
99 x_AUTOCHARGE_TYPE,
100 x_PRINCIPLE_FLAG,
101 x_ATTRIBUTE_CATEGORY,
102 x_ATTRIBUTE1,
103 x_ATTRIBUTE2,
104 x_ATTRIBUTE3,
105 x_ATTRIBUTE4,
106 x_ATTRIBUTE5,
107 x_ATTRIBUTE6,
108 x_ATTRIBUTE7,
109 x_ATTRIBUTE8,
110 x_ATTRIBUTE9,
111 x_ATTRIBUTE10,
112 x_ATTRIBUTE11,
113 x_ATTRIBUTE12,
114 x_ATTRIBUTE13,
115 x_ATTRIBUTE14,
116 x_ATTRIBUTE15
117 );
118
119 END INSERT_ROW;
120
121 PROCEDURE Insert_Row(
122 x_OPERATION_SEQUENCE_ID NUMBER,
123 x_SUBSTITUTE_GROUP_NUM NUMBER,
124 x_RESOURCE_ID NUMBER,
125 x_SCHEDULE_SEQ_NUM NUMBER,
126 x_REPLACEMENT_GROUP_NUM NUMBER,
127 x_ACTIVITY_ID NUMBER,
128 x_STANDARD_RATE_FLAG NUMBER,
129 x_ASSIGNED_UNITS NUMBER,
130 x_USAGE_RATE_OR_AMOUNT NUMBER,
131 x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
132 x_BASIS_TYPE NUMBER,
133 x_SCHEDULE_FLAG NUMBER,
134 x_LAST_UPDATE_DATE DATE,
135 x_LAST_UPDATED_BY NUMBER,
136 x_CREATION_DATE DATE,
137 x_CREATED_BY NUMBER,
138 x_LAST_UPDATE_LOGIN NUMBER,
139 x_RESOURCE_OFFSET_PERCENT NUMBER,
140 x_AUTOCHARGE_TYPE NUMBER,
141 x_PRINCIPLE_FLAG NUMBER,
142 x_ATTRIBUTE_CATEGORY VARCHAR2,
143 x_ATTRIBUTE1 VARCHAR2,
144 x_ATTRIBUTE2 VARCHAR2,
145 x_ATTRIBUTE3 VARCHAR2,
146 x_ATTRIBUTE4 VARCHAR2,
147 x_ATTRIBUTE5 VARCHAR2,
148 x_ATTRIBUTE6 VARCHAR2,
149 x_ATTRIBUTE7 VARCHAR2,
150 x_ATTRIBUTE8 VARCHAR2,
151 x_ATTRIBUTE9 VARCHAR2,
152 x_ATTRIBUTE10 VARCHAR2,
153 x_ATTRIBUTE11 VARCHAR2,
154 x_ATTRIBUTE12 VARCHAR2,
155 x_ATTRIBUTE13 VARCHAR2,
156 x_ATTRIBUTE14 VARCHAR2,
157 x_ATTRIBUTE15 VARCHAR2,
158 x_SETUP_ID NUMBER)
159 IS
160 BEGIN
161 INSERT INTO BOM_SUB_OPERATION_RESOURCES(
162 OPERATION_SEQUENCE_ID,
163 SUBSTITUTE_GROUP_NUM,
164 RESOURCE_ID,
165 SCHEDULE_SEQ_NUM,
166 REPLACEMENT_GROUP_NUM,
167 ACTIVITY_ID,
168 STANDARD_RATE_FLAG,
169 ASSIGNED_UNITS,
170 USAGE_RATE_OR_AMOUNT,
171 USAGE_RATE_OR_AMOUNT_INVERSE,
172 BASIS_TYPE,
173 SCHEDULE_FLAG,
174 LAST_UPDATE_DATE,
175 LAST_UPDATED_BY,
176 CREATION_DATE,
177 CREATED_BY,
178 LAST_UPDATE_LOGIN,
179 RESOURCE_OFFSET_PERCENT,
180 AUTOCHARGE_TYPE,
181 PRINCIPLE_FLAG,
182 attribute_category,
183 attribute1,
184 attribute2,
185 attribute3,
186 attribute4,
187 attribute5,
188 attribute6,
189 attribute7,
190 attribute8,
191 attribute9,
192 attribute10,
193 attribute11,
194 attribute12,
195 attribute13,
196 attribute14,
197 attribute15,
198 setup_id)
199 values (
200 x_OPERATION_SEQUENCE_ID,
201 x_SUBSTITUTE_GROUP_NUM,
202 x_RESOURCE_ID,
203 x_SCHEDULE_SEQ_NUM,
204 x_REPLACEMENT_GROUP_NUM,
205 x_ACTIVITY_ID,
206 x_STANDARD_RATE_FLAG,
207 x_ASSIGNED_UNITS,
208 x_USAGE_RATE_OR_AMOUNT,
209 x_USAGE_RATE_OR_AMOUNT_INVERSE,
210 x_BASIS_TYPE,
211 x_SCHEDULE_FLAG,
212 x_LAST_UPDATE_DATE,
213 x_LAST_UPDATED_BY,
214 x_CREATION_DATE,
215 x_CREATED_BY,
216 x_LAST_UPDATE_LOGIN,
217 x_RESOURCE_OFFSET_PERCENT,
218 x_AUTOCHARGE_TYPE,
219 x_PRINCIPLE_FLAG,
220 x_ATTRIBUTE_CATEGORY,
221 x_ATTRIBUTE1,
222 x_ATTRIBUTE2,
223 x_ATTRIBUTE3,
224 x_ATTRIBUTE4,
225 x_ATTRIBUTE5,
226 x_ATTRIBUTE6,
227 x_ATTRIBUTE7,
228 x_ATTRIBUTE8,
229 x_ATTRIBUTE9,
230 x_ATTRIBUTE10,
231 x_ATTRIBUTE11,
232 x_ATTRIBUTE12,
233 x_ATTRIBUTE13,
234 x_ATTRIBUTE14,
235 x_ATTRIBUTE15,
236 x_SETUP_ID
237 );
238
239 END INSERT_ROW;
240
241 PROCEDURE Lock_Row( x_ROW_ID VARCHAR2,
242 x_OPERATION_SEQUENCE_ID NUMBER,
243 x_SUBSTITUTE_GROUP_NUM NUMBER,
244 x_RESOURCE_ID NUMBER,
245 x_SCHEDULE_SEQ_NUM NUMBER,
246 x_REPLACEMENT_GROUP_NUM NUMBER,
247 x_ACTIVITY_ID NUMBER,
248 x_STANDARD_RATE_FLAG NUMBER,
249 x_ASSIGNED_UNITS NUMBER,
250 x_USAGE_RATE_OR_AMOUNT NUMBER,
251 x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
252 x_BASIS_TYPE NUMBER,
253 x_SCHEDULE_FLAG NUMBER,
254 x_RESOURCE_OFFSET_PERCENT NUMBER,
255 x_AUTOCHARGE_TYPE NUMBER,
256 x_PRINCIPLE_FLAG NUMBER,
257 x_ATTRIBUTE_CATEGORY VARCHAR2,
258 x_ATTRIBUTE1 VARCHAR2,
259 x_ATTRIBUTE2 VARCHAR2,
260 x_ATTRIBUTE3 VARCHAR2,
261 x_ATTRIBUTE4 VARCHAR2,
262 x_ATTRIBUTE5 VARCHAR2,
263 x_ATTRIBUTE6 VARCHAR2,
264 x_ATTRIBUTE7 VARCHAR2,
265 x_ATTRIBUTE8 VARCHAR2,
266 x_ATTRIBUTE9 VARCHAR2,
267 x_ATTRIBUTE10 VARCHAR2,
268 x_ATTRIBUTE11 VARCHAR2,
269 x_ATTRIBUTE12 VARCHAR2,
270 x_ATTRIBUTE13 VARCHAR2,
271 x_ATTRIBUTE14 VARCHAR2,
272 x_ATTRIBUTE15 VARCHAR2
273 ) IS
274 Counter NUMBER;
275 CURSOR C IS SELECT
276 operation_sequence_id,
277 substitute_group_num,
278 resource_id,
279 schedule_seq_num,
280 replacement_group_num,
281 activity_id,
282 standard_rate_flag,
283 assigned_units,
284 usage_rate_or_amount,
285 usage_rate_or_amount_inverse,
286 basis_type,
287 schedule_flag,
288 resource_offset_percent,
289 NVL(principle_flag, 2) principle_flag,
290 autocharge_type,
291 attribute_category,
292 attribute1,
293 attribute2,
294 attribute3,
295 attribute4,
296 attribute5,
297 attribute6,
298 attribute7,
299 attribute8,
300 attribute9,
301 attribute10,
302 attribute11,
303 attribute12,
304 attribute13,
305 attribute14,
306 attribute15
307 FROM BOM_SUB_OPERATION_RESOURCES
308 WHERE rowid = x_row_id
309 FOR UPDATE of operation_sequence_id, substitute_group_num, resource_id NOWAIT;
310 Recinfo C%ROWTYPE;
311 BEGIN
312 Counter := 0;
313 LOOP
314 BEGIN
315 Counter := Counter + 1;
316 OPEN C;
317 FETCH C INTO Recinfo;
318 IF (C%NOTFOUND) THEN
319 CLOSE C;
320 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
321 APP_EXCEPTION.Raise_Exception;
322 END IF;
323 CLOSE C;
324 IF ((Recinfo.operation_sequence_id = x_Operation_Sequence_Id)
325 AND (Recinfo.substitute_group_num = x_substitute_group_Num)
326 AND (Recinfo.resource_id = x_Resource_Id)
327 AND (Recinfo.schedule_seq_num = x_schedule_seq_num)
328 AND (Recinfo.Replacement_group_num = x_replacement_group_num)
329 AND ((Recinfo.activity_id = x_Activity_Id)
330 OR ((Recinfo.activity_id IS NULL)
331 AND (x_Activity_Id IS NULL)))
332 AND (Recinfo.standard_rate_flag = x_Standard_Rate_Flag)
333 AND (Recinfo.assigned_units = x_Assigned_Units)
334 AND (Recinfo.usage_rate_or_amount = x_Usage_Rate_Or_Amount)
335 AND (Recinfo.usage_rate_or_amount_inverse = x_Usage_Rate_Or_Amount_Inverse)
336 AND (Recinfo.basis_type = x_Basis_Type)
337 AND (Recinfo.schedule_flag = x_Schedule_Flag)
338 AND ((Recinfo.resource_offset_percent = x_Resource_Offset_Percent)
339 OR ((Recinfo.resource_offset_percent IS NULL)
340 AND (x_Resource_Offset_Percent IS NULL)))
341 AND (Recinfo.autocharge_type = x_Autocharge_Type)
342 AND ((Recinfo.principle_flag = x_Principle_Flag)
343 OR ((Recinfo.principle_flag IS NULL)
344 AND (x_Principle_Flag IS NULL)))
345 AND ((Recinfo.attribute_category = x_Attribute_Category)
346 OR ((Recinfo.attribute_category IS NULL)
347 AND (x_Attribute_Category IS NULL)))
348 AND ((Recinfo.attribute1 = x_Attribute1)
349 OR ((Recinfo.attribute1 IS NULL)
353 AND (x_Attribute2 IS NULL)))
350 AND (x_Attribute1 IS NULL)))
351 AND ((Recinfo.attribute2 = x_Attribute2)
352 OR ((Recinfo.attribute2 IS NULL)
354 AND ((Recinfo.attribute3 = x_Attribute3)
355 OR ((Recinfo.attribute3 IS NULL)
356 AND (x_Attribute3 IS NULL)))
357 AND ((Recinfo.attribute4 = x_Attribute4)
358 OR ((Recinfo.attribute4 IS NULL)
359 AND (x_Attribute4 IS NULL)))
360 AND ((Recinfo.attribute5 = x_Attribute5)
361 OR ((Recinfo.attribute5 IS NULL)
362 AND (x_Attribute5 IS NULL)))
363 AND ((Recinfo.attribute6 = x_Attribute6)
364 OR ((Recinfo.attribute6 IS NULL)
365 AND (x_Attribute6 IS NULL)))
366 AND ((Recinfo.attribute7 = x_Attribute7)
367 OR ((Recinfo.attribute7 IS NULL)
368 AND (x_Attribute7 IS NULL)))
369 AND ((Recinfo.attribute8 = x_Attribute8)
370 OR ((Recinfo.attribute8 IS NULL)
371 AND (x_Attribute8 IS NULL)))
372 AND ((Recinfo.attribute9 = x_Attribute9)
373 OR ((Recinfo.attribute9 IS NULL)
374 AND (x_Attribute9 IS NULL)))
375 AND ((Recinfo.attribute10 = x_Attribute10)
376 OR ((Recinfo.attribute10 IS NULL)
377 AND (x_Attribute10 IS NULL)))
378 AND ((Recinfo.attribute11 = x_Attribute11)
379 OR ((Recinfo.attribute11 IS NULL)
380 AND (x_Attribute11 IS NULL)))
381 AND ((Recinfo.attribute12 = x_Attribute12)
382 OR ((Recinfo.attribute12 IS NULL)
383 AND (x_Attribute12 IS NULL)))
384 AND ((Recinfo.attribute13 = x_Attribute13)
385 OR ((Recinfo.attribute13 IS NULL)
386 AND (x_Attribute13 IS NULL)))
387 AND ((Recinfo.attribute14 = x_Attribute14)
388 OR ((Recinfo.attribute14 IS NULL)
389 AND (x_Attribute14 IS NULL)))
390 AND ((Recinfo.attribute15 = x_Attribute15)
391 OR ((Recinfo.attribute15 IS NULL)
392 AND (x_Attribute15 IS NULL)))
393 ) THEN
394 return;
395 ELSE
396 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
397 APP_EXCEPTION.Raise_Exception;
398 END IF;
399 EXCEPTION
400 When APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
401 APP_EXCEPTION.Raise_Exception;
402 END;
403 END LOOP;
404 END Lock_Row;
405
406 PROCEDURE Lock_Row( x_ROW_ID VARCHAR2,
407 x_OPERATION_SEQUENCE_ID NUMBER,
408 x_SUBSTITUTE_GROUP_NUM NUMBER,
409 x_RESOURCE_ID NUMBER,
410 x_SCHEDULE_SEQ_NUM NUMBER,
411 x_REPLACEMENT_GROUP_NUM NUMBER,
412 x_ACTIVITY_ID NUMBER,
413 x_STANDARD_RATE_FLAG NUMBER,
414 x_ASSIGNED_UNITS NUMBER,
415 x_USAGE_RATE_OR_AMOUNT NUMBER,
416 x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
417 x_BASIS_TYPE NUMBER,
418 x_SCHEDULE_FLAG NUMBER,
419 x_RESOURCE_OFFSET_PERCENT NUMBER,
420 x_AUTOCHARGE_TYPE NUMBER,
421 x_PRINCIPLE_FLAG NUMBER,
422 x_ATTRIBUTE_CATEGORY VARCHAR2,
423 x_ATTRIBUTE1 VARCHAR2,
424 x_ATTRIBUTE2 VARCHAR2,
425 x_ATTRIBUTE3 VARCHAR2,
426 x_ATTRIBUTE4 VARCHAR2,
427 x_ATTRIBUTE5 VARCHAR2,
428 x_ATTRIBUTE6 VARCHAR2,
429 x_ATTRIBUTE7 VARCHAR2,
430 x_ATTRIBUTE8 VARCHAR2,
431 x_ATTRIBUTE9 VARCHAR2,
432 x_ATTRIBUTE10 VARCHAR2,
433 x_ATTRIBUTE11 VARCHAR2,
434 x_ATTRIBUTE12 VARCHAR2,
435 x_ATTRIBUTE13 VARCHAR2,
436 x_ATTRIBUTE14 VARCHAR2,
437 x_ATTRIBUTE15 VARCHAR2,
438 x_SETUP_ID NUMBER
439 ) IS
440 Counter NUMBER;
441 CURSOR C IS SELECT
442 operation_sequence_id,
443 substitute_group_num,
444 resource_id,
445 schedule_seq_num,
446 replacement_group_num,
447 activity_id,
448 standard_rate_flag,
449 assigned_units,
450 usage_rate_or_amount,
451 usage_rate_or_amount_inverse,
452 basis_type,
453 schedule_flag,
454 resource_offset_percent,
455 NVL(principle_flag, 2) principle_flag,
456 autocharge_type,
457 attribute_category,
458 attribute1,
459 attribute2,
460 attribute3,
464 attribute7,
461 attribute4,
462 attribute5,
463 attribute6,
465 attribute8,
466 attribute9,
467 attribute10,
468 attribute11,
469 attribute12,
470 attribute13,
471 attribute14,
472 attribute15,
473 setup_id
474 FROM BOM_SUB_OPERATION_RESOURCES
475 WHERE rowid = x_row_id
476 FOR UPDATE of operation_sequence_id, substitute_group_num, resource_id, replacement_group_num NOWAIT; --for bug 3287004
477 Recinfo C%ROWTYPE;
478 BEGIN
479 Counter := 0;
480 LOOP
481 BEGIN
482 Counter := Counter + 1;
483 OPEN C;
484 FETCH C INTO Recinfo;
485 IF (C%NOTFOUND) THEN
486 CLOSE C;
487 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
488 APP_EXCEPTION.Raise_Exception;
489 END IF;
490 CLOSE C;
491 IF ((Recinfo.operation_sequence_id = x_Operation_Sequence_Id)
492 AND (Recinfo.substitute_group_num = x_substitute_group_Num)
493 AND (Recinfo.resource_id = x_Resource_Id)
494 AND ((Recinfo.schedule_seq_num = x_schedule_seq_num) --for bug 3287004
495 OR ((Recinfo.schedule_seq_num IS NULL)
496 AND (x_schedule_seq_num IS NULL)))
497 AND (Recinfo.Replacement_group_num = x_replacement_group_num)
498 AND ((Recinfo.activity_id = x_Activity_Id)
499 OR ((Recinfo.activity_id IS NULL)
500 AND (x_Activity_Id IS NULL)))
501 AND (Recinfo.standard_rate_flag = x_Standard_Rate_Flag)
502 AND (Recinfo.assigned_units = x_Assigned_Units)
503 AND (Recinfo.usage_rate_or_amount = x_Usage_Rate_Or_Amount)
504 AND (Recinfo.usage_rate_or_amount_inverse = x_Usage_Rate_Or_Amount_Inverse)
505 AND (Recinfo.basis_type = x_Basis_Type)
506 AND (Recinfo.schedule_flag = x_Schedule_Flag)
507 AND ((Recinfo.resource_offset_percent = x_Resource_Offset_Percent)
508 OR ((Recinfo.resource_offset_percent IS NULL)
509 AND (x_Resource_Offset_Percent IS NULL)))
510 AND (Recinfo.autocharge_type = x_Autocharge_Type)
511 AND ((Recinfo.principle_flag = x_Principle_Flag)
512 OR ((Recinfo.principle_flag IS NULL)
513 AND (x_Principle_Flag IS NULL)))
514 AND ((Recinfo.attribute_category = x_Attribute_Category)
515 OR ((Recinfo.attribute_category IS NULL)
516 AND (x_Attribute_Category IS NULL)))
517 AND ((Recinfo.attribute1 = x_Attribute1)
518 OR ((Recinfo.attribute1 IS NULL)
519 AND (x_Attribute1 IS NULL)))
520 AND ((Recinfo.attribute2 = x_Attribute2)
521 OR ((Recinfo.attribute2 IS NULL)
522 AND (x_Attribute2 IS NULL)))
523 AND ((Recinfo.attribute3 = x_Attribute3)
524 OR ((Recinfo.attribute3 IS NULL)
525 AND (x_Attribute3 IS NULL)))
526 AND ((Recinfo.attribute4 = x_Attribute4)
527 OR ((Recinfo.attribute4 IS NULL)
528 AND (x_Attribute4 IS NULL)))
529 AND ((Recinfo.attribute5 = x_Attribute5)
530 OR ((Recinfo.attribute5 IS NULL)
531 AND (x_Attribute5 IS NULL)))
532 AND ((Recinfo.attribute6 = x_Attribute6)
533 OR ((Recinfo.attribute6 IS NULL)
534 AND (x_Attribute6 IS NULL)))
535 AND ((Recinfo.attribute7 = x_Attribute7)
536 OR ((Recinfo.attribute7 IS NULL)
537 AND (x_Attribute7 IS NULL)))
538 AND ((Recinfo.attribute8 = x_Attribute8)
539 OR ((Recinfo.attribute8 IS NULL)
540 AND (x_Attribute8 IS NULL)))
541 AND ((Recinfo.attribute9 = x_Attribute9)
542 OR ((Recinfo.attribute9 IS NULL)
543 AND (x_Attribute9 IS NULL)))
544 AND ((Recinfo.attribute10 = x_Attribute10)
545 OR ((Recinfo.attribute10 IS NULL)
546 AND (x_Attribute10 IS NULL)))
547 AND ((Recinfo.attribute11 = x_Attribute11)
548 OR ((Recinfo.attribute11 IS NULL)
549 AND (x_Attribute11 IS NULL)))
550 AND ((Recinfo.attribute12 = x_Attribute12)
551 OR ((Recinfo.attribute12 IS NULL)
552 AND (x_Attribute12 IS NULL)))
553 AND ((Recinfo.attribute13 = x_Attribute13)
554 OR ((Recinfo.attribute13 IS NULL)
555 AND (x_Attribute13 IS NULL)))
556 AND ((Recinfo.attribute14 = x_Attribute14)
557 OR ((Recinfo.attribute14 IS NULL)
558 AND (x_Attribute14 IS NULL)))
559 AND ((Recinfo.attribute15 = x_Attribute15)
560 OR ((Recinfo.attribute15 IS NULL)
561 AND (x_Attribute15 IS NULL)))
562 AND ((Recinfo.setup_id = x_Setup_Id)
563 OR ((Recinfo.setup_id IS NULL)
564 AND (x_Setup_Id IS NULL)))
565 ) THEN
566 return;
567 ELSE
568 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
569 APP_EXCEPTION.Raise_Exception;
570 END IF;
571 EXCEPTION
572 When APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
573 APP_EXCEPTION.Raise_Exception;
577
574 END;
575 END LOOP;
576 END Lock_Row;
578 PROCEDURE Update_Row(x_ROW_ID VARCHAR2,
579 x_OPERATION_SEQUENCE_ID NUMBER,
580 x_SUBSTITUTE_GROUP_NUM NUMBER,
581 x_RESOURCE_ID NUMBER,
582 x_SCHEDULE_SEQ_NUM NUMBER,
583 x_REPLACEMENT_GROUP_NUM NUMBER,
584 x_ACTIVITY_ID NUMBER,
585 x_STANDARD_RATE_FLAG NUMBER,
586 x_ASSIGNED_UNITS NUMBER,
587 x_USAGE_RATE_OR_AMOUNT NUMBER,
588 x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
589 x_BASIS_TYPE NUMBER,
590 x_SCHEDULE_FLAG NUMBER,
591 x_LAST_UPDATE_DATE DATE,
592 x_LAST_UPDATED_BY NUMBER,
593 x_CREATION_DATE DATE,
594 x_CREATED_BY NUMBER,
595 x_LAST_UPDATE_LOGIN NUMBER,
596 x_RESOURCE_OFFSET_PERCENT NUMBER,
597 x_AUTOCHARGE_TYPE NUMBER,
598 x_PRINCIPLE_FLAG NUMBER,
599 x_ATTRIBUTE_CATEGORY VARCHAR2,
600 x_ATTRIBUTE1 VARCHAR2,
601 x_ATTRIBUTE2 VARCHAR2,
602 x_ATTRIBUTE3 VARCHAR2,
603 x_ATTRIBUTE4 VARCHAR2,
604 x_ATTRIBUTE5 VARCHAR2,
605 x_ATTRIBUTE6 VARCHAR2,
606 x_ATTRIBUTE7 VARCHAR2,
607 x_ATTRIBUTE8 VARCHAR2,
608 x_ATTRIBUTE9 VARCHAR2,
609 x_ATTRIBUTE10 VARCHAR2,
610 x_ATTRIBUTE11 VARCHAR2,
611 x_ATTRIBUTE12 VARCHAR2,
612 x_ATTRIBUTE13 VARCHAR2,
613 x_ATTRIBUTE14 VARCHAR2,
614 x_ATTRIBUTE15 VARCHAR2)
615 IS
616 BEGIN
617 UPDATE BOM_SUB_OPERATION_RESOURCES SET
618 OPERATION_SEQUENCE_ID = x_OPERATION_SEQUENCE_ID,
619 SUBSTITUTE_GROUP_NUM = x_SUBSTITUTE_GROUP_NUM,
620 RESOURCE_ID = x_RESOURCE_ID,
621 SCHEDULE_SEQ_NUM = x_SCHEDULE_SEQ_NUM,
622 REPLACEMENT_GROUP_NUM = x_REPLACEMENT_GROUP_NUM ,
623 ACTIVITY_ID = x_ACTIVITY_ID,
624 STANDARD_RATE_FLAG = x_STANDARD_RATE_FLAG,
625 ASSIGNED_UNITS = x_ASSIGNED_UNITS,
626 USAGE_RATE_OR_AMOUNT = x_USAGE_RATE_OR_AMOUNT,
627 USAGE_RATE_OR_AMOUNT_INVERSE = x_USAGE_RATE_OR_AMOUNT_INVERSE,
628 BASIS_TYPE = x_BASIS_TYPE,
629 SCHEDULE_FLAG = x_SCHEDULE_FLAG,
630 LAST_UPDATE_DATE = x_LAST_UPDATE_DATE,
631 LAST_UPDATED_BY = x_LAST_UPDATED_BY,
632 CREATION_DATE = x_CREATION_DATE,
633 CREATED_BY = x_CREATED_BY,
634 LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN,
635 RESOURCE_OFFSET_PERCENT = x_RESOURCE_OFFSET_PERCENT,
636 AUTOCHARGE_TYPE = x_AUTOCHARGE_TYPE,
637 PRINCIPLE_FLAG = x_PRINCIPLE_FLAG,
638 attribute_category = x_attribute_category,
639 attribute1 = x_attribute1,
640 attribute2 = x_attribute2,
641 attribute3 = x_attribute3,
642 attribute4 = x_attribute4,
643 attribute5 = x_attribute5,
644 attribute6 = x_attribute6,
645 attribute7 = x_attribute7,
646 attribute8 = x_attribute8,
647 attribute9 = x_attribute9,
648 attribute10 = x_attribute10,
649 attribute11 = x_attribute11,
650 attribute12 = x_attribute12,
651 attribute13 = x_attribute13,
652 attribute14 = x_attribute14,
653 attribute15 = x_attribute15
654 WHERE rowid = x_row_id;
655 IF (SQL%NOTFOUND) THEN
656 Raise NO_DATA_FOUND;
657 END IF;
658
659 END;
660
661 PROCEDURE Update_Row(x_ROW_ID VARCHAR2,
662 x_OPERATION_SEQUENCE_ID NUMBER,
663 x_SUBSTITUTE_GROUP_NUM NUMBER,
664 x_RESOURCE_ID NUMBER,
665 x_SCHEDULE_SEQ_NUM NUMBER,
666 x_REPLACEMENT_GROUP_NUM NUMBER,
667 x_ACTIVITY_ID NUMBER,
668 x_STANDARD_RATE_FLAG NUMBER,
672 x_BASIS_TYPE NUMBER,
669 x_ASSIGNED_UNITS NUMBER,
670 x_USAGE_RATE_OR_AMOUNT NUMBER,
671 x_USAGE_RATE_OR_AMOUNT_INVERSE NUMBER,
673 x_SCHEDULE_FLAG NUMBER,
674 x_LAST_UPDATE_DATE DATE,
675 x_LAST_UPDATED_BY NUMBER,
676 x_CREATION_DATE DATE,
677 x_CREATED_BY NUMBER,
678 x_LAST_UPDATE_LOGIN NUMBER,
679 x_RESOURCE_OFFSET_PERCENT NUMBER,
680 x_AUTOCHARGE_TYPE NUMBER,
681 x_PRINCIPLE_FLAG NUMBER,
682 x_ATTRIBUTE_CATEGORY VARCHAR2,
683 x_ATTRIBUTE1 VARCHAR2,
684 x_ATTRIBUTE2 VARCHAR2,
685 x_ATTRIBUTE3 VARCHAR2,
686 x_ATTRIBUTE4 VARCHAR2,
687 x_ATTRIBUTE5 VARCHAR2,
688 x_ATTRIBUTE6 VARCHAR2,
689 x_ATTRIBUTE7 VARCHAR2,
690 x_ATTRIBUTE8 VARCHAR2,
691 x_ATTRIBUTE9 VARCHAR2,
692 x_ATTRIBUTE10 VARCHAR2,
693 x_ATTRIBUTE11 VARCHAR2,
694 x_ATTRIBUTE12 VARCHAR2,
695 x_ATTRIBUTE13 VARCHAR2,
696 x_ATTRIBUTE14 VARCHAR2,
697 x_ATTRIBUTE15 VARCHAR2,
698 x_SETUP_ID NUMBER)
699 IS
700 BEGIN
701 UPDATE BOM_SUB_OPERATION_RESOURCES SET
702 OPERATION_SEQUENCE_ID = x_OPERATION_SEQUENCE_ID,
703 SUBSTITUTE_GROUP_NUM = x_SUBSTITUTE_GROUP_NUM,
704 RESOURCE_ID = x_RESOURCE_ID,
705 SCHEDULE_SEQ_NUM = x_SCHEDULE_SEQ_NUM,
706 REPLACEMENT_GROUP_NUM = x_REPLACEMENT_GROUP_NUM ,
707 ACTIVITY_ID = x_ACTIVITY_ID,
708 STANDARD_RATE_FLAG = x_STANDARD_RATE_FLAG,
709 ASSIGNED_UNITS = x_ASSIGNED_UNITS,
710 USAGE_RATE_OR_AMOUNT = x_USAGE_RATE_OR_AMOUNT,
711 USAGE_RATE_OR_AMOUNT_INVERSE = x_USAGE_RATE_OR_AMOUNT_INVERSE,
712 BASIS_TYPE = x_BASIS_TYPE,
713 SCHEDULE_FLAG = x_SCHEDULE_FLAG,
714 LAST_UPDATE_DATE = x_LAST_UPDATE_DATE,
715 LAST_UPDATED_BY = x_LAST_UPDATED_BY,
716 CREATION_DATE = x_CREATION_DATE,
717 CREATED_BY = x_CREATED_BY,
718 LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN,
719 RESOURCE_OFFSET_PERCENT = x_RESOURCE_OFFSET_PERCENT,
720 AUTOCHARGE_TYPE = x_AUTOCHARGE_TYPE,
721 PRINCIPLE_FLAG = x_PRINCIPLE_FLAG,
722 attribute_category = x_attribute_category,
723 attribute1 = x_attribute1,
724 attribute2 = x_attribute2,
725 attribute3 = x_attribute3,
726 attribute4 = x_attribute4,
727 attribute5 = x_attribute5,
728 attribute6 = x_attribute6,
729 attribute7 = x_attribute7,
730 attribute8 = x_attribute8,
731 attribute9 = x_attribute9,
732 attribute10 = x_attribute10,
733 attribute11 = x_attribute11,
734 attribute12 = x_attribute12,
735 attribute13 = x_attribute13,
736 attribute14 = x_attribute14,
737 attribute15 = x_attribute15,
738 setup_id = x_setup_id
739 WHERE rowid = x_row_id;
740 IF (SQL%NOTFOUND) THEN
741 Raise NO_DATA_FOUND;
742 END IF;
743
744 END;
745
746 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
747 BEGIN
748 DELETE FROM BOM_SUB_OPERATION_RESOURCES
749 WHERE rowid=x_rowid;
750 END Delete_Row;
751
752 PROCEDURE CHECK_UNIQUE_LINK(X_ROWID VARCHAR2,
753 X_FROM_OP_SEQ_ID NUMBER,
754 X_TO_OP_SEQ_ID NUMBER) IS
755 dummy NUMBER;
756 from_op_seq_num NUMBER;
757 to_op_seq_num NUMBER;
758 BEGIN
759 SELECT operation_seq_num
760 INTO from_op_seq_num
761 FROM bom_operation_sequences
762 WHERE operation_sequence_id = x_from_op_seq_id;
763
764 SELECT operation_seq_num
765 INTO to_op_seq_num
766 FROM bom_operation_sequences
767 WHERE operation_sequence_id = x_to_op_seq_id;
768
769 SELECT 1 INTO dummy FROM DUAL WHERE NOT EXISTS
770 (SELECT 1 FROM BOM_OPERATION_NETWORKS
771 WHERE from_op_seq_id = X_From_Op_Seq_Id
772 AND To_Op_Seq_Id = X_To_Op_Seq_Id
776 WHEN NO_DATA_FOUND THEN
773 AND ((X_Rowid IS NULL) OR (ROWID <> X_Rowid))
774 );
775 EXCEPTION
777 FND_MESSAGE.SET_NAME('BOM','BOM_LINK_ALREADY_EXISTS');
778 FND_MESSAGE.SET_TOKEN('FROM_OP_SEQ_ID',to_char(from_op_seq_num), FALSE);
779 FND_MESSAGE.SET_TOKEN('TO_OP_SEQ_ID',to_char(to_op_seq_num), FALSE);
780 APP_EXCEPTION.RAISE_EXCEPTION;
781 END;
782
783 /*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
784 ** Function : get_resource_code
785 ** Scope : local
786 ** Purpose : To get the resource code for the id passed. Added as part of bug fix 4932342.
787 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
788 FUNCTION get_resource_code (
789 p_resource_id IN NUMBER
790 )
791 RETURN VARCHAR2
792 IS
793 l_resource_code VARCHAR2 (10);
794
795 CURSOR csr_res_code (
796 p_res_id IN NUMBER
797 )
798 IS
799 SELECT br.resource_code
800 FROM bom_resources br
801 WHERE br.resource_id = p_res_id;
802 BEGIN
803 OPEN csr_res_code (p_resource_id);
804
805 LOOP
806 FETCH csr_res_code
807 INTO l_resource_code;
808 EXIT WHEN csr_res_code%NOTFOUND;
809 END LOOP;
810
811 CLOSE csr_res_code;
812
813 RETURN l_resource_code;
814 END get_resource_code;
815
816 PROCEDURE Validate_Schedule_Flag(p_routing_sequence_id NUMBER, -- BUG 3950992
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_data OUT NOCOPY VARCHAR2,
819 x_operation_seq_num OUT NOCOPY NUMBER) IS
820
821 CURSOR operations (p_rtg_seq_id NUMBER)IS
822 SELECT operation_seq_num, operation_sequence_id
823 FROM bom_operation_sequences
824 WHERE routing_sequence_id = p_rtg_seq_id;
825
826 /*Fix for bug 6074930 - Added schedule_flag <> 2 conditions in the queries below.
827 This is done to filter off unscheduled resources, sub-resources. */
828 CURSOR op_resources(p_opn_seq_num NUMBER) IS
829 SELECT * FROM
830 (
831 (
832 SELECT bor.resource_id,
833 bor.resource_seq_num,
834 bor.schedule_seq_num,
835 bor.substitute_group_num,
836 bor.schedule_flag
837 FROM bom_operation_resources bor ,
838 bom_operation_sequences bos ,
839 bom_resources br ,
840 bom_department_resources bdr ,
841 cst_activities ca ,
842 bom_setup_types bst
843 WHERE br.resource_id = bor.resource_id
844 AND bdr.department_id = bos.department_id
845 AND bdr.resource_id = bor.resource_id
846 AND bor.operation_sequence_id = bos.operation_sequence_id
847 AND NVL ( bos.operation_type , 1 ) = 1
848 AND bor.activity_id = ca.activity_id ( + )
849 AND bor.setup_id = bst.setup_id ( + )
850 AND bor.operation_sequence_id = p_opn_seq_num
851 AND bor.schedule_flag <> 2
852 UNION
853 SELECT bor.resource_id,
854 bor.resource_seq_num,
855 bor.schedule_seq_num,
856 bor.substitute_group_num,
857 bor.schedule_flag
858 FROM bom_operation_resources bor ,
859 eng_revised_operations ero ,
860 bom_resources br ,
861 bom_department_resources bdr ,
862 cst_activities ca ,
863 bom_setup_types bst
864 WHERE br.resource_id = bor.resource_id
865 AND bdr.department_id = ero.department_id
866 AND bdr.resource_id = bor.resource_id
867 AND bor.operation_sequence_id = ero.operation_sequence_id
868 AND bor.acd_type IS NOT NULL
869 AND NVL ( ero.operation_type , 1 ) = 1
870 AND bor.activity_id = ca.activity_id ( + )
871 AND bor.setup_id = bst.setup_id ( + )
872 AND bor.operation_sequence_id = p_opn_seq_num
873 AND bor.schedule_flag <> 2
874 )
875 UNION ALL
876 SELECT bor.resource_id,
877 to_number(NULL) resource_seq_num,
878 bor.schedule_seq_num,
879 bor.substitute_group_num,
880 bor.schedule_flag
881 FROM bom_sub_operation_resources bor ,
882 bom_operation_sequences bos ,
883 bom_resources br ,
884 bom_department_resources bdr ,
885 cst_activities ca ,
886 bom_setup_types bst
887 WHERE br.resource_id = bor.resource_id
888 AND bdr.department_id = bos.department_id
889 AND bdr.resource_id = bor.resource_id
890 AND bor.operation_sequence_id = bos.operation_sequence_id
891 AND NVL ( bos.operation_type , 1 ) = 1
892 AND bor.activity_id = ca.activity_id ( + )
893 AND bor.setup_id = bst.setup_id ( + )
894 AND bor.operation_sequence_id = p_opn_seq_num
895 AND bor.schedule_flag <> 2
896 ) ORDER BY nvl(schedule_seq_num, resource_seq_num);
897
898 last_res_seq NUMBER := 0;
899 last_sub_group NUMBER := 0;
900 last_sched_seq NUMBER := 0;
901 last_scheduled_flag NUMBER := 0;
902 last_res_code VARCHAR2(50) :='';
903 last_res_id NUMBER := 0;
904 error_exists BOOLEAN := false;
905 BEGIN
906 for cur_op in operations (p_routing_sequence_id) loop
907 last_res_seq := 0;
908 last_sub_group := 0;
909 last_sched_seq := 0;
910 last_scheduled_flag := 0;
911 last_res_code := '';
912
913 for cur_opres in op_resources(cur_op.operation_sequence_id) loop
914 /*Fix for bug 6074930 - In below If condition, added check on last_res_seq is null,
915 since for sub resources, resource_seq_num can be null*/
916 if ( (last_res_seq is null) or (last_res_seq <>0) ) then
917 if ((nvl(last_sched_seq,last_res_seq) = nvl(cur_opres.schedule_seq_num, cur_opres.resource_seq_num))) then
918 if (last_scheduled_flag <> cur_opres.schedule_flag) then
919 FND_MESSAGE.SET_NAME('BOM', 'BOM_SIM_RES_SAME_PRIOR_NEXT');
920 FND_MESSAGE.set_token( token => 'OP_SEQ',
921 value=> to_char(cur_op.operation_seq_num),
922 translate => FALSE);
923 FND_MESSAGE.set_token( token => 'RES_SEQ_1',
924 value=> get_resource_code(last_res_id),
925 translate => FALSE);
926 FND_MESSAGE.set_token( token => 'RES_SEQ_2',
927 value=> get_resource_code(cur_opres.resource_id),
928 translate => FALSE);
929 error_exists := true;
930 end if;
931 end if;
932 end if;
933 if (error_exists = true) then
934 x_return_status := fnd_api.g_ret_sts_error;
935 x_msg_data := fnd_message.get;
936 x_operation_seq_num := cur_op.operation_seq_num;
937 return;
938 end if;
939 last_res_seq := cur_opres.resource_seq_num;
940 last_sub_group := cur_opres.substitute_group_num;
941 last_sched_seq := cur_opres.schedule_seq_num;
942 last_scheduled_flag := cur_opres.schedule_flag;
943 last_res_id := cur_opres.resource_id;
944 -- commented as part of bug fix 4932342
945 -- last_res_code := cur_opres.resource_code; Will query separately the resource_code for msg
946 end loop;
947 end loop;
948 EXCEPTION
949 WHEN OTHERS THEN
950 x_return_status := fnd_api.g_ret_sts_unexp_error;
951 END Validate_Schedule_Flag;
952
953
954 END BOM_SUB_RESOURCES_PKG;