[Home] [Help]
PACKAGE BODY: APPS.WIP_OPERATIONS_PKG
Source
1 PACKAGE BODY WIP_OPERATIONS_PKG AS
2 /* $Header: wipoperb.pls 120.5.12020000.3 2012/08/30 06:02:18 akuppa ship $ */
3
4 g_start_date DATE;
5
6 procedure add(
7 p_org_id in number,
8 p_wip_entity_id in number,
9 p_operation_seq_num in number,
10 p_operation_id in number,
11 p_department_id in number) is
12 x_prev_op_seq_num number := NULL;
13 x_next_op_seq_num number := NULL;
14 x_user_id number := FND_GLOBAL.USER_ID;
15 x_login_id number := FND_GLOBAL.LOGIN_ID;
16 x_request_id number := FND_GLOBAL.CONC_REQUEST_ID;
17 x_appl_id number := FND_GLOBAL.PROG_APPL_ID;
18 x_program_id number := FND_GLOBAL.CONC_PROGRAM_ID;
19 adding_standard_op boolean := (p_operation_id is NOT NULL
20 or
21 p_operation_id = -1);
22 l_scrap_qty number := 0;
23 /* Added : -- bug 7371859 */
24 -- for validating sub resource exiting or not for std operation
25 sub_res_count number;
26 l_wsor_max_res_seq_num number :=0;
27 /* End : -- bug 7371859 */
28 begin
29 -- get previous and next operation
30 select max(wo1.operation_seq_num),
31 min(wo2.operation_seq_num)
32 into x_prev_op_seq_num,
33 x_next_op_seq_num
34 from dual sd,
35 wip_operations wo1,
36 wip_operations wo2
37 where wo1.organization_id(+) = p_org_id
38 and wo1.wip_entity_id(+) = decode(1, 1, p_wip_entity_id, sd.dummy)
39 and wo1.operation_seq_num(+) < p_operation_seq_num
40 and wo2.organization_id(+) = p_org_id
41 and wo2.wip_entity_id(+) = decode(1, 1, p_wip_entity_id, sd.dummy)
42 and wo2.operation_seq_num(+) > p_operation_seq_num;
43
44 /* For Enhancement#2864382. Calculate cumulative_scrap_quantity for this operation */
45
46 SELECT SUM(quantity_scrapped)
47 INTO l_scrap_qty
48 FROM wip_operations
49 WHERE organization_id = p_org_id
50 AND wip_entity_id = p_wip_entity_id
51 AND operation_seq_num < p_operation_seq_num;
52
53 IF (l_scrap_qty IS NULL) THEN
54 l_scrap_qty :=0;
55 END IF;
56
57 -- if prev operation exists in routing
58 if (x_prev_op_seq_num is NOT NULL) then
59 update wip_operations
60 set next_operation_seq_num = p_operation_seq_num
61 where organization_id = p_org_id
62 and wip_entity_id = p_wip_entity_id
63 and operation_seq_num = x_prev_op_seq_num;
64 end if;
65
66 -- if next operation exists in routing
67 if (x_next_op_seq_num is NOT NULL) then
68 update wip_operations
69 set previous_operation_seq_num = p_operation_seq_num
70 where organization_id = p_org_id
71 and wip_entity_id = p_wip_entity_id
72 and operation_seq_num = x_next_op_seq_num;
73 end if;
74
75 -- add operation
76 begin
77 insert into wip_operations(
78 wip_entity_id,
79 operation_seq_num,
80 organization_id,
81 last_update_date,
82 last_updated_by,
83 creation_date,
84 created_by,
85 last_update_login,
86 standard_operation_id,
87 department_id,
88 scheduled_quantity,
89 quantity_in_queue,
90 quantity_running,
91 quantity_waiting_to_move,
92 quantity_rejected,
93 quantity_scrapped,
94 quantity_completed,
95 cumulative_scrap_quantity, /* for enh#2864382*/
96 minimum_transfer_quantity,
97 first_unit_start_date,
98 first_unit_completion_date,
99 last_unit_start_date,
100 last_unit_completion_date,
101 previous_operation_seq_num,
102 next_operation_seq_num,
103 count_point_type,
104 backflush_flag,
105 description,
106 attribute_category,
107 attribute1,
108 attribute2,
109 attribute3,
110 attribute4,
111 attribute5,
112 attribute6,
113 attribute7,
114 attribute8,
115 attribute9,
116 attribute10,
117 attribute11,
118 attribute12,
119 attribute13,
120 attribute14,
121 attribute15,
122 check_skill)
123 select p_wip_entity_id,
124 p_operation_seq_num,
125 p_org_id,
126 SYSDATE,
127 x_user_id,
128 SYSDATE,
129 x_user_id,
130 x_login_id,
131 decode(p_operation_id, -1, NULL, p_operation_id),
132 p_department_id,
133 WDJ.START_QUANTITY,
134 0, -- quantity_in_queue
135 0, -- quantity_running
136 0, -- quantity_waiting_to_move
137 0, -- quantity_rejected
138 0, -- quantity_in_scrap
139 0, -- quantity_completed
140 l_scrap_qty,
141 nvl(bso.minimum_transfer_quantity, 0),
142 nvl(wo1.first_unit_completion_date, -- first_unit_start_date
143 wdj.scheduled_start_date),
144 nvl(wo1.first_unit_completion_date, -- first_unit_completion_date
145 wdj.scheduled_start_date),
146 nvl(wo1.last_unit_completion_date, -- last_unit_start_date
147 wdj.scheduled_start_date),
148 nvl(wo1.last_unit_completion_date, -- last_unit_completion_date
149 wdj.scheduled_start_date),
150 x_prev_op_seq_num,
151 x_next_op_seq_num,
152 WIP_CONSTANTS.YES_AUTO,
153 decode(x_next_op_seq_num,
154 NULL, WIP_CONSTANTS.YES, WIP_CONSTANTS.NO),
155 bso.operation_description,
156 bso.attribute_category,
157 bso.attribute1,
158 bso.attribute2,
159 bso.attribute3,
160 bso.attribute4,
161 bso.attribute5,
162 bso.attribute6,
163 bso.attribute7,
164 bso.attribute8,
165 bso.attribute9,
166 bso.attribute10,
167 bso.attribute11,
168 bso.attribute12,
169 bso.attribute13,
170 bso.attribute14,
171 bso.attribute15,
172 bso.check_skill
173 from wip_operations wo1,
174 wip_discrete_jobs wdj,
175 bom_standard_operations bso
176 where bso.standard_operation_id(+) = p_operation_id
177 /* %cfm Ignore cfm ops. */
178 and nvl(bso.operation_type, 1) = 1
179 and bso.line_id is null
180 /* %/cfm */
181 and bso.organization_id(+) =
182 decode(1, 1, p_org_id,wdj.organization_id)
183 and wdj.wip_entity_id = p_wip_entity_id
184 and wdj.organization_id = p_org_id
185 and wo1.wip_entity_id(+) =
186 decode(1, 1, p_wip_entity_id, wdj.wip_entity_id)
187 and wo1.organization_id(+) = p_org_id
188 and wo1.operation_seq_num(+) = x_prev_op_seq_num;
189
190 exception
191 when DUP_VAL_ON_INDEX then
192 fnd_message.set_name(
193 application => 'WIP',
194 name => 'WIP_SAME_OP_EXISTS');
195 fnd_message.raise_error;
196 end;
197
198 -- if standard operation, add resources and instructions
199 if (adding_standard_op) then
200 insert into wip_operation_resources
201 (wip_entity_id,
202 operation_seq_num,
203 resource_seq_num,
204 organization_id,
205 last_update_date,
206 last_updated_by,
207 creation_date,
208 created_by,
209 last_update_login,
210 resource_id,
211 uom_code,
212 basis_type,
213 usage_rate_or_amount,
214 activity_id,
215 scheduled_flag,
216 assigned_units,
217 autocharge_type,
218 standard_rate_flag,
219 applied_resource_units,
220 applied_resource_value,
221 start_date,
222 completion_date,
223 attribute_category,
224 attribute1,
225 attribute2,
226 attribute3,
227 attribute4,
228 attribute5,
229 attribute6,
230 attribute7,
231 attribute8,
232 attribute9,
233 attribute10,
234 attribute11,
235 attribute12,
236 attribute13,
237 attribute14,
238 attribute15,
239 substitute_group_num, -- Added : ---- bug 7371859
240 schedule_seq_num,principle_flag, -- Added : ---- bug 7371859
241 replacement_group_num) -- Added : ---- bug 7371859
242 select p_wip_entity_id,
243 p_operation_seq_num,
244 bsor.resource_seq_num,
245 p_org_id,
246 SYSDATE,
247 x_user_id,
248 SYSDATE,
249 x_user_id,
250 x_login_id,
251 bsor.resource_id,
252 br.unit_of_measure,
253 bsor.basis_type,
254 bsor.usage_rate_or_amount,
255 bsor.activity_id,
256 bsor.schedule_flag,
257 bsor.assigned_units,
258 bsor.autocharge_type,
259 bsor.standard_rate_flag,
260 0, -- applied_resource_units
261 0, -- applied_resource_value
262 wo.first_unit_start_date,
263 wo.last_unit_completion_date,
264 bsor.attribute_category,
265 bsor.attribute1,
266 bsor.attribute2,
267 bsor.attribute3,
268 bsor.attribute4,
269 bsor.attribute5,
270 bsor.attribute6,
271 bsor.attribute7,
272 bsor.attribute8,
273 bsor.attribute9,
274 bsor.attribute10,
275 bsor.attribute11,
276 bsor.attribute12,
277 bsor.attribute13,
278 bsor.attribute14,
279 bsor.attribute15,
280 bsor.substitute_group_num, -- Added : -- bug 7371859
281 bsor.schedule_seq_num,bsor.principle_flag, -- Added : -- bug 7371859(Schedule Seq Num= Resource Seq Num)
282 0 -- Added : -- bug 7371859
283 from wip_operations wo,
284 bom_resources br,
285 bom_std_op_resources bsor
286 where bsor.standard_operation_id = p_operation_id
287 and br.resource_id = bsor.resource_id
288 and nvl(br.disable_date, SYSDATE + 1) > SYSDATE
289 and wo.organization_id = p_org_id
290 and wo.wip_entity_id = p_wip_entity_id
291 and wo.operation_seq_num = p_operation_seq_num;
292
293 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
294 X_FROM_ENTITY_NAME => 'BOM_STANDARD_OPERATIONS',
295 X_FROM_PK1_VALUE => to_char(p_operation_id),
296 X_TO_ENTITY_NAME => 'WIP_DISCRETE_OPERATIONS',
297 X_TO_PK1_VALUE => to_char(p_wip_entity_id),
298 X_TO_PK2_VALUE => to_char(p_operation_seq_num),
299 X_TO_PK3_VALUE => to_char(p_org_id),
300 X_CREATED_BY => x_user_id,
301 X_LAST_UPDATE_LOGIN => x_login_id,
302 X_PROGRAM_APPLICATION_ID => x_appl_id,
303 X_PROGRAM_ID => x_program_id,
304 X_REQUEST_ID => x_request_id);
305 /* Added : -- bug 7371859 */
306
307 /* Added for 12.1.1 Skills Validation project.*/
308 INSERT INTO WIP_OPERATION_COMPETENCIES
309 ( LEVEL_ID, ORGANIZATION_ID,
310 WIP_ENTITY_ID, OPERATION_SEQ_NUM, OPERATION_SEQUENCE_ID,
311 STANDARD_OPERATION_ID, COMPETENCE_ID, RATING_LEVEL_ID,
312 QUALIFICATION_TYPE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
313 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
314 SELECT
315 3, WO.ORGANIZATION_ID,
316 WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, BOS.OPERATION_SEQUENCE_ID,
317 BOS.STANDARD_OPERATION_ID, BOS.COMPETENCE_ID, BOS.RATING_LEVEL_ID,
318 BOS.QUALIFICATION_TYPE_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY,
319 WO.LAST_UPDATE_LOGIN, WO.CREATED_BY, WO.CREATION_DATE
320 FROM BOM_OPERATION_SKILLS BOS,
321 WIP_OPERATIONS WO,
322 WIP_ENTITIES WE
323 WHERE
324 WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
325 AND WO.ORGANIZATION_ID = WO.ORGANIZATION_ID
326 AND WE.ENTITY_TYPE = 1
327 AND WO.ORGANIZATION_ID = p_org_id
328 AND WO.WIP_ENTITY_ID = p_wip_entity_id
329 AND WO.OPERATION_SEQ_NUM = p_operation_seq_num
330 AND WO.ORGANIZATION_ID = BOS.ORGANIZATION_ID
331 AND BOS.STANDARD_OPERATION_ID = p_operation_id
332 AND BOS.LEVEL_ID = 1;
333
334 BEGIN
335 SELECT count(*)
336 INTO sub_res_count
337 FROM BOM_STD_SUB_OP_RESOURCES BSSOR
338 WHERE BSSOR.STANDARD_OPERATION_ID=p_operation_id;
339 EXCEPTION
340 WHEN no_data_found THEN
341 null;
342 END ;
343
344 IF sub_res_count >0 then
345
346 BEGIN
347 SELECT nvl(max(resource_seq_num), 10)
348 INTO l_wsor_max_res_seq_num
349 FROM WIP_SUB_OPERATION_RESOURCES WSOR
350 WHERE wip_entity_id = p_wip_entity_id
351 AND OPERATION_SEQ_NUM = p_operation_seq_num;
352 EXCEPTION
353 WHEN no_data_found THEN
354 null;
355 END;
356
357 INSERT INTO WIP_SUB_OPERATION_RESOURCES
358 (wip_entity_id,
359 operation_seq_num,
360 resource_seq_num,
361 organization_id,
362 --repetitive_schedule_id,
363 last_update_date,
364 last_updated_by,
365 creation_date,
366 created_by,
367 last_update_login,
368 resource_id,
369 uom_code,
370 basis_type,
371 usage_rate_or_amount,
372 activity_id,
373 scheduled_flag,
374 assigned_units,
375 maximum_assigned_units,
376 autocharge_type,
377 standard_rate_flag,
378 applied_resource_units,
379 applied_resource_value,
380 attribute_category,
381 attribute1,
382 attribute2,
383 attribute3,
384 attribute4,
385 attribute5,
386 attribute6,
387 attribute7,
388 attribute8,
389 attribute9,
390 attribute10,
391 attribute11,
392 attribute12,
393 attribute13,
394 attribute14,
395 attribute15,
396 completion_date,
397 start_date,
398 schedule_seq_num,
399 substitute_group_num,
400 replacement_group_num,
401 setup_id)
402 SELECT p_wip_entity_id,
403 p_operation_seq_num,
404 (rownum + l_wsor_max_res_seq_num),
405 p_org_id,
406 -- X_Repetitive_Schedule_Id,
407 SYSDATE ,
408 x_user_id,
409 SYSDATE,
410 x_user_id,
411 x_login_id,
412 BSSOR.resource_id,
413 BR.unit_of_measure,
414 BSSOR.basis_type,
415 BSSOR.usage_rate_or_amount,
416 BSSOR.activity_id,
417 BSSOR.schedule_flag,
418 BSSOR.assigned_units,
419 BSSOR.assigned_units,
420 BSSOR.autocharge_type,
421 BSSOR.standard_rate_flag,
422 0, --WCOR.applied_resource_units,
423 0, -- WCOR.applied_resource_value,
424 BSSOR.attribute_category,
425 BSSOR.attribute1,
426 BSSOR.attribute2,
427 BSSOR.attribute3,
428 BSSOR.attribute4,
429 BSSOR.attribute5,
430 BSSOR.attribute6,
431 BSSOR.attribute7,
432 BSSOR.attribute8,
433 BSSOR.attribute9,
434 BSSOR.attribute10,
435 BSSOR.attribute11,
436 BSSOR.attribute12,
437 BSSOR.attribute13,
438 BSSOR.attribute14,
439 BSSOR.attribute15,
440 sysdate, -- DECODE(X_Start_Date, NULL, SYSDATE, X_Start_Date),
441 sysdate, --DECODE(X_Completion_Date, NULL, SYSDATE, X_Completion_Date),
442 BSSOR.schedule_seq_num ,
443 BSSOR.substitute_group_num,
444 BSSOR.replacement_group_num,
445 NULL --setup_id
446 FROM BOM_RESOURCES BR,
447 BOM_STD_SUB_OP_RESOURCES BSSOR
448 WHERE bssor.standard_operation_id=p_operation_id
449 AND BSSOR.RESOURCE_ID = BR.RESOURCE_ID;
450
451 End IF;
452 /* End : -- bug 7371859 */
453
454
455
456 end if;
457
458 return;
459
460 exception
461 when others then
462 wip_constants.get_ora_error(
463 application => 'WIP',
464 proc_name => 'WIP_OPERATIONS_PKG.ADD');
465 fnd_message.raise_error;
466
467 end add;
468
469
470 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
471 X_Wip_Entity_Id NUMBER,
472 X_Operation_Seq_Num NUMBER,
473 X_Organization_Id NUMBER,
474 X_Repetitive_Schedule_Id NUMBER,
475 X_Last_Update_Date DATE,
476 X_Last_Updated_By NUMBER,
477 X_Creation_Date DATE,
478 X_Created_By NUMBER,
479 X_Last_Update_Login NUMBER,
480 X_Operation_Sequence_Id NUMBER,
481 X_Standard_Operation_Id NUMBER,
482 X_Department_Id NUMBER,
483 X_Description VARCHAR2,
484 X_Scheduled_Quantity NUMBER,
485 X_Quantity_In_Queue NUMBER,
486 X_Quantity_Running NUMBER,
487 X_Quantity_Waiting_To_Move NUMBER,
488 X_Quantity_Rejected NUMBER,
489 X_Quantity_Scrapped NUMBER,
490 X_Quantity_Completed NUMBER,
491 X_First_Unit_Start_Date DATE,
492 X_First_Unit_Completion_Date DATE,
493 X_Last_Unit_Start_Date DATE,
494 X_Last_Unit_Completion_Date DATE,
495 X_Previous_Operation_Seq_Num NUMBER,
496 X_Next_Operation_Seq_Num NUMBER,
497 X_Count_Point_Type NUMBER,
498 X_Backflush_Flag NUMBER,
499 X_Minimum_Transfer_Quantity NUMBER,
500 X_Date_Last_Moved DATE,
501 /*3118918*/ X_Progress_percentage NUMBER,
502 X_Attribute_Category VARCHAR2,
503 X_Attribute1 VARCHAR2,
504 X_Attribute2 VARCHAR2,
505 X_Attribute3 VARCHAR2,
506 X_Attribute4 VARCHAR2,
507 X_Attribute5 VARCHAR2,
508 X_Attribute6 VARCHAR2,
509 X_Attribute7 VARCHAR2,
510 X_Attribute8 VARCHAR2,
511 X_Attribute9 VARCHAR2,
512 X_Attribute10 VARCHAR2,
513 X_Attribute11 VARCHAR2,
514 X_Attribute12 VARCHAR2,
515 X_Attribute13 VARCHAR2,
516 X_Attribute14 VARCHAR2,
517 X_Attribute15 VARCHAR2,
518 X_CHECK_SKILL NUMBER DEFAULT NULL) IS
519 CURSOR C IS SELECT rowid FROM WIP_OPERATIONS
520 WHERE wip_entity_id = X_Wip_Entity_Id
521 AND operation_seq_num = X_Operation_Seq_Num
522 AND organization_id = X_Organization_Id
523 AND (repetitive_Schedule_id = X_Repetitive_Schedule_Id
524 OR (repetitive_schedule_id IS NULL
525 AND X_Repetitive_Schedule_Id IS NULL));
526 x_user_id number := FND_GLOBAL.USER_ID;
527 x_login_id number := FND_GLOBAL.LOGIN_ID;
528 x_request_id number := FND_GLOBAL.CONC_REQUEST_ID;
529 x_appl_id number := FND_GLOBAL.PROG_APPL_ID;
530 x_program_id number := FND_GLOBAL.CONC_PROGRAM_ID;
531 x_standard_op boolean := (X_Standard_Operation_Id is NOT NULL);
532 l_scrap_qty number :=0;
533
534 BEGIN
535
536 /* For Enhancement#2864382. Calculate cumulative_scrap_quantity for this op
537 eration */
538
539 SELECT SUM(quantity_scrapped)
540 INTO l_scrap_qty
541 FROM wip_operations
542 WHERE organization_id = X_Organization_Id
543 AND wip_entity_id = X_Wip_Entity_Id
544 AND (repetitive_schedule_id = X_Repetitive_Schedule_Id OR
545 (repetitive_schedule_id IS NULL AND X_Repetitive_Schedule_Id IS NULL))
546 AND operation_seq_num < X_Operation_Seq_Num;
547
548 IF (l_scrap_qty IS NULL) THEN
549 l_scrap_qty :=0;
550 END IF;
551
552 INSERT INTO WIP_OPERATIONS(
553 wip_entity_id,
554 operation_seq_num,
555 organization_id,
556 repetitive_schedule_id,
557 last_update_date,
558 last_updated_by,
559 creation_date,
560 created_by,
561 last_update_login,
562 operation_sequence_id,
563 standard_operation_id,
564 department_id,
565 description,
566 scheduled_quantity,
567 quantity_in_queue,
568 quantity_running,
569 quantity_waiting_to_move,
570 quantity_rejected,
571 quantity_scrapped,
572 quantity_completed,
573 first_unit_start_date,
574 first_unit_completion_date,
575 last_unit_start_date,
576 last_unit_completion_date,
577 previous_operation_seq_num,
578 next_operation_seq_num,
579 count_point_type,
580 backflush_flag,
581 minimum_transfer_quantity,
582 date_last_moved,
583 cumulative_scrap_quantity, /*For Enhancement#2864382*/
584 progress_percentage, /*Enhancement 3118918*/
585 attribute_category,
586 attribute1,
587 attribute2,
588 attribute3,
589 attribute4,
590 attribute5,
591 attribute6,
592 attribute7,
593 attribute8,
594 attribute9,
595 attribute10,
596 attribute11,
597 attribute12,
598 attribute13,
599 attribute14,
600 attribute15,
601 CHECK_SKILL
602 ) VALUES (
603 X_Wip_Entity_Id,
604 X_Operation_Seq_Num,
605 X_Organization_Id,
606 X_Repetitive_Schedule_Id,
607 X_Last_Update_Date,
608 X_Last_Updated_By,
609 X_Creation_Date,
610 X_Created_By,
611 X_Last_Update_Login,
612 X_Operation_Sequence_Id,
613 X_Standard_Operation_Id,
614 X_Department_Id,
615 X_Description,
616 X_Scheduled_Quantity,
617 X_Quantity_In_Queue,
618 X_Quantity_Running,
619 X_Quantity_Waiting_To_Move,
620 X_Quantity_Rejected,
621 X_Quantity_Scrapped,
622 X_Quantity_Completed,
623 X_First_Unit_Start_Date,
624 X_First_Unit_Completion_Date,
625 X_Last_Unit_Start_Date,
626 X_Last_Unit_Completion_Date,
627 X_Previous_Operation_Seq_Num,
628 X_Next_Operation_Seq_Num,
629 X_Count_Point_Type,
630 X_Backflush_Flag,
631 X_Minimum_Transfer_Quantity,
632 X_Date_Last_Moved,
633 l_scrap_qty,
634 X_progress_percentage,
635 X_Attribute_Category,
636 X_Attribute1,
637 X_Attribute2,
638 X_Attribute3,
639 X_Attribute4,
640 X_Attribute5,
641 X_Attribute6,
642 X_Attribute7,
643 X_Attribute8,
644 X_Attribute9,
645 X_Attribute10,
646 X_Attribute11,
647 X_Attribute12,
648 X_Attribute13,
649 X_Attribute14,
650 X_Attribute15,
651 X_CHECK_SKILL
652 );
653
654
655 IF (X_Repetitive_Schedule_Id IS NULL) THEN
656 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
657 X_FROM_ENTITY_NAME => 'BOM_STANDARD_OPERATIONS',
658 X_FROM_PK1_VALUE => to_char(X_Standard_Operation_Id),
659 X_TO_ENTITY_NAME => 'WIP_DISCRETE_OPERATIONS',
660 X_TO_PK1_VALUE => to_char(X_Wip_Entity_Id),
661 X_TO_PK2_VALUE => to_char(X_Operation_Seq_Num),
662 X_TO_PK3_VALUE => to_char(X_Organization_Id),
663 X_CREATED_BY => x_user_id,
664 X_LAST_UPDATE_LOGIN => x_login_id,
665 X_PROGRAM_APPLICATION_ID => x_appl_id,
666 X_PROGRAM_ID => x_program_id,
667 X_REQUEST_ID => x_request_id);
668 ELSE
669 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
670 X_FROM_ENTITY_NAME => 'BOM_STANDARD_OPERATIONS',
671 X_FROM_PK1_VALUE => to_char(X_Standard_Operation_Id),
672 X_TO_ENTITY_NAME => 'WIP_REPETITIVE_OPERATIONS',
673 X_TO_PK1_VALUE => to_char(X_Wip_Entity_Id),
674 X_TO_PK2_VALUE => to_char(X_Operation_Seq_Num),
675 X_TO_PK3_VALUE => to_char(X_Organization_Id),
676 X_TO_PK4_VALUE => to_char(X_Repetitive_Schedule_Id),
677 X_CREATED_BY => x_user_id,
678 X_LAST_UPDATE_LOGIN => x_login_id,
679 X_PROGRAM_APPLICATION_ID => x_appl_id,
680 X_PROGRAM_ID => x_program_id,
681 X_REQUEST_ID => x_request_id);
682 END IF;
683
684 /* Added for 12.1.1 Skills Validation project.*/
685 /*INSERT INTO WIP_OPERATION_COMPETENCIES
686 ( LEVEL_ID, ORGANIZATION_ID,
687 WIP_ENTITY_ID, OPERATION_SEQ_NUM, OPERATION_SEQUENCE_ID,
688 STANDARD_OPERATION_ID, COMPETENCE_ID, RATING_LEVEL_ID,
689 QUALIFICATION_TYPE_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY,
690 LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE)
691 SELECT
692 3, WO.ORGANIZATION_ID,
693 WO.WIP_ENTITY_ID, WO.OPERATION_SEQ_NUM, BOS.OPERATION_SEQUENCE_ID,
694 BOS.STANDARD_OPERATION_ID, BOS.COMPETENCE_ID, BOS.RATING_LEVEL_ID,
695 BOS.QUALIFICATION_TYPE_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY,
696 WO.LAST_UPDATE_LOGIN, WO.CREATED_BY, WO.CREATION_DATE
697 FROM BOM_OPERATION_SKILLS BOS,
698 WIP_OPERATIONS WO,
699 WIP_ENTITIES WE
700 WHERE
701 WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
702 AND WO.ORGANIZATION_ID = WO.ORGANIZATION_ID
703 AND WE.ENTITY_TYPE = 1
704 AND WO.ORGANIZATION_ID = X_Organization_Id
705 AND WO.WIP_ENTITY_ID = X_Wip_Entity_Id
706 AND WO.OPERATION_SEQ_NUM = X_Operation_Seq_Num
707 AND NVL(WO.CHECK_SKILL,2) =1
708 AND WO.ORGANIZATION_ID = BOS.ORGANIZATION_ID
709 AND BOS.STANDARD_OPERATION_ID = X_Standard_Operation_Id
710 AND BOS.LEVEL_ID = 1;*/
711
712 /* Commented out as the same piece of login is implemented directly in the form*/
713
714 OPEN C;
715 FETCH C INTO X_Rowid;
716 if (C%NOTFOUND) then
717 CLOSE C;
718 Raise NO_DATA_FOUND;
719 end if;
720 CLOSE C;
721 END Insert_Row;
722
723 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
724 X_Wip_Entity_Id NUMBER,
725 X_Operation_Seq_Num NUMBER,
726 X_Organization_Id NUMBER,
727 X_Repetitive_Schedule_Id NUMBER,
728 X_Operation_Sequence_Id NUMBER,
729 X_Standard_Operation_Id NUMBER,
730 X_Department_Id NUMBER,
731 X_Description VARCHAR2,
732 X_Scheduled_Quantity NUMBER,
733 X_Quantity_In_Queue NUMBER,
734 X_Quantity_Running NUMBER,
735 X_Quantity_Waiting_To_Move NUMBER,
736 X_Quantity_Rejected NUMBER,
737 X_Quantity_Scrapped NUMBER,
738 X_Quantity_Completed NUMBER,
739 X_First_Unit_Start_Date DATE,
740 X_First_Unit_Completion_Date DATE,
741 X_Last_Unit_Start_Date DATE,
742 X_Last_Unit_Completion_Date DATE,
743 X_Count_Point_Type NUMBER,
744 X_Backflush_Flag NUMBER,
745 X_Minimum_Transfer_Quantity NUMBER,
746 X_Date_Last_Moved DATE,
747 X_progress_percentage NUMBER,
748 X_Attribute_Category VARCHAR2,
749 X_Attribute1 VARCHAR2,
750 X_Attribute2 VARCHAR2,
751 X_Attribute3 VARCHAR2,
752 X_Attribute4 VARCHAR2,
753 X_Attribute5 VARCHAR2,
754 X_Attribute6 VARCHAR2,
755 X_Attribute7 VARCHAR2,
756 X_Attribute8 VARCHAR2,
757 X_Attribute9 VARCHAR2,
758 X_Attribute10 VARCHAR2,
759 X_Attribute11 VARCHAR2,
760 X_Attribute12 VARCHAR2,
761 X_Attribute13 VARCHAR2,
762 X_Attribute14 VARCHAR2,
763 X_Attribute15 VARCHAR2,
764 X_CHECK_SKILL NUMBER DEFAULT NULL
765
766 ) IS
767 CURSOR C IS
768 SELECT *
769 FROM WIP_OPERATIONS
770 WHERE rowid = X_Rowid
771 FOR UPDATE of Wip_Entity_Id NOWAIT;
772 Recinfo C%ROWTYPE;
773 BEGIN
774 OPEN C;
775 FETCH C INTO Recinfo;
776 if (C%NOTFOUND) then
777 CLOSE C;
778 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
779 fnd_message.raise_error;
780 app_exception.raise_exception;
781 end if;
782 CLOSE C;
783
784 if ( (Recinfo.wip_entity_id = X_Wip_Entity_Id)
785 AND (Recinfo.operation_seq_num = X_Operation_Seq_Num)
786 AND (Recinfo.organization_id = X_Organization_Id)
787 AND ( (Recinfo.repetitive_schedule_id = X_Repetitive_Schedule_Id)
788 OR ( (Recinfo.repetitive_schedule_id IS NULL)
789 AND (X_Repetitive_Schedule_Id IS NULL)))
790 AND ( (Recinfo.operation_sequence_id = X_Operation_Sequence_Id)
791 OR ( (Recinfo.operation_sequence_id IS NULL)
792 AND (X_Operation_Sequence_Id IS NULL)))
793 AND ( (Recinfo.standard_operation_id = X_Standard_Operation_Id)
794 OR ( (Recinfo.standard_operation_id IS NULL)
795 AND (X_Standard_Operation_Id IS NULL)))
796 AND (Recinfo.department_id = X_Department_Id)
797 AND ( (Recinfo.description = X_Description)
798 OR ( (Recinfo.description IS NULL)
799 AND (X_Description IS NULL)))
800 AND (Recinfo.scheduled_quantity = X_Scheduled_Quantity)
801 AND (Recinfo.quantity_in_queue = X_Quantity_In_Queue)
802 AND (Recinfo.quantity_running = X_Quantity_Running)
803 AND (Recinfo.quantity_waiting_to_move = X_Quantity_Waiting_To_Move)
804 AND (Recinfo.quantity_rejected = X_Quantity_Rejected)
805 AND (Recinfo.quantity_scrapped = X_Quantity_Scrapped)
806 AND (Recinfo.quantity_completed = X_Quantity_Completed)
807 AND (Recinfo.first_unit_start_date = X_First_Unit_Start_Date)
808 AND (Recinfo.first_unit_completion_date = X_First_Unit_Completion_Date)
809 AND (Recinfo.last_unit_start_date = X_Last_Unit_Start_Date)
810 AND (Recinfo.last_unit_completion_date = X_Last_Unit_Completion_Date)
811 /*3118918*/AND ( (Recinfo.progress_percentage = X_Progress_percentage)
812 OR ( (Recinfo.progress_percentage IS NULL)
813 AND (X_Progress_percentage IS NULL)))
814 AND (Recinfo.count_point_type = X_Count_Point_Type))
815 then
816 if (
817 (Recinfo.backflush_flag = X_Backflush_Flag)
818 AND (Recinfo.minimum_transfer_quantity = X_Minimum_Transfer_Quantity)
819 AND ( (Recinfo.date_last_moved = X_Date_Last_Moved)
820 OR ( (Recinfo.date_last_moved IS NULL)
821 AND (X_Date_Last_Moved IS NULL)))
822 AND ( (Recinfo.attribute_category = X_Attribute_Category)
823 OR ( (Recinfo.attribute_category IS NULL)
824 AND (X_Attribute_Category IS NULL)))
825 AND ( (Recinfo.attribute1 = X_Attribute1)
826 OR ( (Recinfo.attribute1 IS NULL)
827 AND (X_Attribute1 IS NULL)))
828 AND ( (Recinfo.attribute2 = X_Attribute2)
829 OR ( (Recinfo.attribute2 IS NULL)
830 AND (X_Attribute2 IS NULL)))
831 AND ( (Recinfo.attribute3 = X_Attribute3)
832 OR ( (Recinfo.attribute3 IS NULL)
833 AND (X_Attribute3 IS NULL)))
834 AND ( (Recinfo.attribute4 = X_Attribute4)
835 OR ( (Recinfo.attribute4 IS NULL)
836 AND (X_Attribute4 IS NULL)))
837 AND ( (Recinfo.attribute5 = X_Attribute5)
838 OR ( (Recinfo.attribute5 IS NULL)
839 AND (X_Attribute5 IS NULL)))
840 AND ( (Recinfo.attribute6 = X_Attribute6)
841 OR ( (Recinfo.attribute6 IS NULL)
842 AND (X_Attribute6 IS NULL)))
843 AND ( (Recinfo.attribute7 = X_Attribute7)
844 OR ( (Recinfo.attribute7 IS NULL)
845 AND (X_Attribute7 IS NULL)))
846 AND ( (Recinfo.attribute8 = X_Attribute8)
847 OR ( (Recinfo.attribute8 IS NULL)
848 AND (X_Attribute8 IS NULL)))
849 AND ( (Recinfo.attribute9 = X_Attribute9)
850 OR ( (Recinfo.attribute9 IS NULL)
851 AND (X_Attribute9 IS NULL)))
852 AND ( (Recinfo.attribute10 = X_Attribute10)
853 OR ( (Recinfo.attribute10 IS NULL)
854 AND (X_Attribute10 IS NULL)))
855 AND ( (Recinfo.attribute11 = X_Attribute11)
856 OR ( (Recinfo.attribute11 IS NULL)
857 AND (X_Attribute11 IS NULL)))
858 AND ( (Recinfo.attribute12 = X_Attribute12)
859 OR ( (Recinfo.attribute12 IS NULL)
860 AND (X_Attribute12 IS NULL)))
861 AND ( (Recinfo.attribute13 = X_Attribute13)
862 OR ( (Recinfo.attribute13 IS NULL)
863 AND (X_Attribute13 IS NULL)))
864 AND ( (Recinfo.attribute14 = X_Attribute14)
865 OR ( (Recinfo.attribute14 IS NULL)
866 AND (X_Attribute14 IS NULL)))
867 AND ( (Recinfo.attribute15 = X_Attribute15)
868 OR ( (Recinfo.attribute15 IS NULL)
869 AND (X_Attribute15 IS NULL)))
870 AND ( (nvl(Recinfo.CHECK_SKILL,2) = X_CHECK_SKILL)
871 OR ( (Recinfo.CHECK_SKILL IS NULL)
872 AND (X_CHECK_SKILL IS NULL)))
873 )
874 then
875 return;
876 else
877 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
878 fnd_message.raise_error;
879
880 app_exception.raise_exception;
881 end if;
882 else
883 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
884 fnd_message.raise_error;
885 app_exception.raise_exception;
886 end if;
887
888 END Lock_Row;
889
890
891 PROCEDURE Update_Row(X_Rowid VARCHAR2,
892 X_Wip_Entity_Id NUMBER,
893 X_Operation_Seq_Num NUMBER,
894 X_Organization_Id NUMBER,
895 X_Repetitive_Schedule_Id NUMBER,
896 X_Last_Update_Date DATE,
897 X_Last_Updated_By NUMBER,
898 X_Last_Update_Login NUMBER,
899 X_Operation_Sequence_Id NUMBER,
900 X_Standard_Operation_Id NUMBER,
901 X_Department_Id NUMBER,
902 X_Description VARCHAR2,
903 X_Scheduled_Quantity NUMBER,
904 X_Quantity_In_Queue NUMBER,
905 X_Quantity_Running NUMBER,
906 X_Quantity_Waiting_To_Move NUMBER,
907 X_Quantity_Rejected NUMBER,
908 X_Quantity_Scrapped NUMBER,
909 X_Quantity_Completed NUMBER,
910 X_First_Unit_Start_Date DATE,
911 X_First_Unit_Completion_Date DATE,
912 X_Last_Unit_Start_Date DATE,
913 X_Last_Unit_Completion_Date DATE,
914 X_Count_Point_Type NUMBER,
915 X_Backflush_Flag NUMBER,
916 X_Minimum_Transfer_Quantity NUMBER,
917 X_Date_Last_Moved DATE,
918 /*3118918*/ X_Progress_percentage NUMBER,
919 X_Attribute_Category VARCHAR2,
920 X_Attribute1 VARCHAR2,
921 X_Attribute2 VARCHAR2,
922 X_Attribute3 VARCHAR2,
923 X_Attribute4 VARCHAR2,
924 X_Attribute5 VARCHAR2,
925 X_Attribute6 VARCHAR2,
926 X_Attribute7 VARCHAR2,
927 X_Attribute8 VARCHAR2,
928 X_Attribute9 VARCHAR2,
929 X_Attribute10 VARCHAR2,
930 X_Attribute11 VARCHAR2,
931 X_Attribute12 VARCHAR2,
932 X_Attribute13 VARCHAR2,
933 X_Attribute14 VARCHAR2,
934 X_Attribute15 VARCHAR2,
935 X_CHECK_SKILL NUMBER DEFAULT NULL
936
937 ) IS
938 BEGIN
939 UPDATE WIP_OPERATIONS
940 SET
941 wip_entity_id = X_Wip_Entity_Id,
942 operation_seq_num = X_Operation_Seq_Num,
943 organization_id = X_Organization_Id,
944 repetitive_schedule_id = X_Repetitive_Schedule_Id,
945 last_update_date = X_Last_Update_Date,
946 last_updated_by = X_Last_Updated_By,
947 last_update_login = X_Last_Update_Login,
948 operation_sequence_id = X_Operation_Sequence_Id,
949 standard_operation_id = X_Standard_Operation_Id,
950 department_id = X_Department_Id,
951 description = X_Description,
952 scheduled_quantity = X_Scheduled_Quantity,
953 quantity_in_queue = X_Quantity_In_Queue,
954 quantity_running = X_Quantity_Running,
955 quantity_waiting_to_move = X_Quantity_Waiting_To_Move,
956 quantity_rejected = X_Quantity_Rejected,
957 quantity_scrapped = X_Quantity_Scrapped,
958 quantity_completed = X_Quantity_Completed,
959 first_unit_start_date = X_First_Unit_Start_Date,
960 first_unit_completion_date = X_First_Unit_Completion_Date,
961 last_unit_start_date = X_Last_Unit_Start_Date,
962 last_unit_completion_date = X_Last_Unit_Completion_Date,
963 count_point_type = X_Count_Point_Type,
964 backflush_flag = X_Backflush_Flag,
965 minimum_transfer_quantity = X_Minimum_Transfer_Quantity,
966 date_last_moved = X_Date_Last_Moved,
967 progress_percentage = X_Progress_percentage,/*3118918*/
968 attribute_category = X_Attribute_Category,
969 attribute1 = X_Attribute1,
970 attribute2 = X_Attribute2,
971 attribute3 = X_Attribute3,
972 attribute4 = X_Attribute4,
973 attribute5 = X_Attribute5,
974 attribute6 = X_Attribute6,
975 attribute7 = X_Attribute7,
976 attribute8 = X_Attribute8,
977 attribute9 = X_Attribute9,
978 attribute10 = X_Attribute10,
979 attribute11 = X_Attribute11,
980 attribute12 = X_Attribute12,
981 attribute13 = X_Attribute13,
982 attribute14 = X_Attribute14,
983 attribute15 = X_Attribute15,
984 CHECK_SKILL = X_CHECK_SKILL
985 WHERE rowid = X_rowid;
986
987 if (SQL%NOTFOUND) then
988 Raise NO_DATA_FOUND;
989 end if;
990
991 END Update_Row;
992
993 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
994 BEGIN
995 DELETE FROM WIP_OPERATIONS
996 WHERE rowid = X_Rowid;
997
998 if (SQL%NOTFOUND) then
999 Raise NO_DATA_FOUND;
1000 end if;
1001 END Delete_Row;
1002
1003
1004 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
1005 X_Wip_Entity_Id NUMBER,
1006 X_Operation_Seq_Num NUMBER,
1007 X_Organization_Id NUMBER,
1008 X_Repetitive_Schedule_Id NUMBER,
1009 X_Last_Update_Date DATE,
1010 X_Last_Updated_By NUMBER,
1011 X_Creation_Date DATE,
1012 X_Created_By NUMBER,
1013 X_Last_Update_Login NUMBER,
1014 X_Operation_Sequence_Id NUMBER,
1015 X_Standard_Operation_Id NUMBER,
1016 X_Department_Id NUMBER,
1017 X_Description VARCHAR2,
1018 X_Scheduled_Quantity NUMBER,
1019 X_Quantity_In_Queue NUMBER,
1020 X_Quantity_Running NUMBER,
1021 X_Quantity_Waiting_To_Move NUMBER,
1022 X_Quantity_Rejected NUMBER,
1023 X_Quantity_Scrapped NUMBER,
1024 X_Quantity_Completed NUMBER,
1025 X_First_Unit_Start_Date DATE,
1026 X_First_Unit_Completion_Date DATE,
1027 X_Last_Unit_Start_Date DATE,
1028 X_Last_Unit_Completion_Date DATE,
1029 X_Previous_Operation_Seq_Num NUMBER,
1030 X_Next_Operation_Seq_Num NUMBER,
1031 X_Count_Point_Type NUMBER,
1032 X_Backflush_Flag NUMBER,
1033 X_Minimum_Transfer_Quantity NUMBER,
1034 X_Date_Last_Moved DATE,
1035 X_Attribute_Category VARCHAR2,
1036 X_Attribute1 VARCHAR2,
1037 X_Attribute2 VARCHAR2,
1038 X_Attribute3 VARCHAR2,
1039 X_Attribute4 VARCHAR2,
1040 X_Attribute5 VARCHAR2,
1041 X_Attribute6 VARCHAR2,
1042 X_Attribute7 VARCHAR2,
1043 X_Attribute8 VARCHAR2,
1044 X_Attribute9 VARCHAR2,
1045 X_Attribute10 VARCHAR2,
1046 X_Attribute11 VARCHAR2,
1047 X_Attribute12 VARCHAR2,
1048 X_Attribute13 VARCHAR2,
1049 X_Attribute14 VARCHAR2,
1050 X_Attribute15 VARCHAR2
1051 ) is
1052 l_progressPercentage number;
1053 begin
1054 Insert_Row(X_Rowid => X_Rowid,
1055 X_Wip_Entity_Id => X_Wip_Entity_Id,
1056 X_Operation_Seq_Num => X_Operation_Seq_Num,
1057 X_Organization_Id => X_Organization_Id,
1058 X_Repetitive_Schedule_Id => X_Repetitive_Schedule_Id,
1059 X_Last_Update_Date => X_Last_Update_Date,
1060 X_Last_Updated_By => X_Last_Updated_By,
1061 X_Creation_Date => X_Creation_Date,
1062 X_Created_By => X_Created_By,
1063 X_Last_Update_Login => X_Last_Update_Login,
1064 X_Operation_Sequence_Id => X_Operation_Sequence_Id,
1065 X_Standard_Operation_Id => X_Standard_Operation_Id,
1066 X_Department_Id => X_Department_Id,
1067 X_Description => X_Description,
1068 X_Scheduled_Quantity => X_Scheduled_Quantity,
1069 X_Quantity_In_Queue => X_Quantity_In_Queue,
1070 X_Quantity_Running => X_Quantity_Running,
1071 X_Quantity_Waiting_To_Move => X_Quantity_Waiting_To_Move,
1072 X_Quantity_Rejected => X_Quantity_Rejected,
1073 X_Quantity_Scrapped => X_Quantity_Scrapped,
1074 X_Quantity_Completed => X_Quantity_Completed,
1075 X_First_Unit_Start_Date => X_First_Unit_Start_Date,
1076 X_First_Unit_Completion_Date => X_First_Unit_Completion_Date,
1077 X_Last_Unit_Start_Date => X_Last_Unit_Start_Date,
1078 X_Last_Unit_Completion_Date => X_Last_Unit_Completion_Date,
1079 X_Previous_Operation_Seq_Num => X_Previous_Operation_Seq_Num,
1080 X_Next_Operation_Seq_Num => X_Next_Operation_Seq_Num,
1081 X_Count_Point_Type => X_Count_Point_Type,
1082 X_Backflush_Flag => X_Backflush_Flag,
1083 X_Minimum_Transfer_Quantity => X_Minimum_Transfer_Quantity,
1084 X_Date_Last_Moved => X_Date_Last_Moved,
1085 X_Progress_percentage => l_progressPercentage,
1086 X_Attribute_Category => X_Attribute_Category,
1087 X_Attribute1 => X_Attribute1,
1088 X_Attribute2 => X_Attribute2,
1089 X_Attribute3 => X_Attribute3,
1090 X_Attribute4 => X_Attribute4,
1091 X_Attribute5 => X_Attribute5,
1092 X_Attribute6 => X_Attribute6,
1093 X_Attribute7 => X_Attribute7,
1094 X_Attribute8 => X_Attribute8,
1095 X_Attribute9 => X_Attribute9,
1096 X_Attribute10 => X_Attribute10,
1097 X_Attribute11 => X_Attribute11,
1098 X_Attribute12 => X_Attribute12,
1099 X_Attribute13 => X_Attribute13,
1100 X_Attribute14 => X_Attribute14,
1101 X_Attribute15 => X_Attribute15,
1102 X_CHECK_SKILL => null);
1103 end Insert_Row;
1104
1105
1106 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
1107 X_Wip_Entity_Id NUMBER,
1108 X_Operation_Seq_Num NUMBER,
1109 X_Organization_Id NUMBER,
1110 X_Repetitive_Schedule_Id NUMBER,
1111 X_Operation_Sequence_Id NUMBER,
1112 X_Standard_Operation_Id NUMBER,
1113 X_Department_Id NUMBER,
1114 X_Description VARCHAR2,
1115 X_Scheduled_Quantity NUMBER,
1116 X_Quantity_In_Queue NUMBER,
1117 X_Quantity_Running NUMBER,
1118 X_Quantity_Waiting_To_Move NUMBER,
1119 X_Quantity_Rejected NUMBER,
1120 X_Quantity_Scrapped NUMBER,
1121 X_Quantity_Completed NUMBER,
1122 X_First_Unit_Start_Date DATE,
1123 X_First_Unit_Completion_Date DATE,
1124 X_Last_Unit_Start_Date DATE,
1125 X_Last_Unit_Completion_Date DATE,
1126 X_Count_Point_Type NUMBER,
1127 X_Backflush_Flag NUMBER,
1128 X_Minimum_Transfer_Quantity NUMBER,
1129 X_Date_Last_Moved DATE,
1130 X_Attribute_Category VARCHAR2,
1131 X_Attribute1 VARCHAR2,
1132 X_Attribute2 VARCHAR2,
1133 X_Attribute3 VARCHAR2,
1134 X_Attribute4 VARCHAR2,
1135 X_Attribute5 VARCHAR2,
1136 X_Attribute6 VARCHAR2,
1137 X_Attribute7 VARCHAR2,
1138 X_Attribute8 VARCHAR2,
1139 X_Attribute9 VARCHAR2,
1140 X_Attribute10 VARCHAR2,
1141 X_Attribute11 VARCHAR2,
1142 X_Attribute12 VARCHAR2,
1143 X_Attribute13 VARCHAR2,
1144 X_Attribute14 VARCHAR2,
1145 X_Attribute15 VARCHAR2
1146 ) is
1147 l_progressPercentage number;
1148 begin
1149 Lock_Row(X_Rowid => X_Rowid,
1150 X_Wip_Entity_Id => X_Wip_Entity_Id,
1151 X_Operation_Seq_Num => X_Operation_Seq_Num,
1152 X_Organization_Id => X_Organization_Id,
1153 X_Repetitive_Schedule_Id => X_Repetitive_Schedule_Id,
1154 X_Operation_Sequence_Id => X_Operation_Sequence_Id,
1155 X_Standard_Operation_Id => X_Standard_Operation_Id,
1156 X_Department_Id => X_Department_Id,
1157 X_Description => X_Description,
1158 X_Scheduled_Quantity => X_Scheduled_Quantity,
1159 X_Quantity_In_Queue => X_Quantity_In_Queue,
1160 X_Quantity_Running => X_Quantity_Running,
1161 X_Quantity_Waiting_To_Move => X_Quantity_Waiting_To_Move,
1162 X_Quantity_Rejected => X_Quantity_Rejected,
1163 X_Quantity_Scrapped => X_Quantity_Scrapped,
1164 X_Quantity_Completed => X_Quantity_Completed,
1165 X_First_Unit_Start_Date => X_First_Unit_Start_Date,
1166 X_First_Unit_Completion_Date => X_First_Unit_Completion_Date,
1167 X_Last_Unit_Start_Date => X_Last_Unit_Start_Date,
1168 X_Last_Unit_Completion_Date => X_Last_Unit_Completion_Date,
1169 X_Count_Point_Type => X_Count_Point_Type,
1170 X_Backflush_Flag => X_Backflush_Flag,
1171 X_Minimum_Transfer_Quantity => X_Minimum_Transfer_Quantity,
1172 X_Date_Last_Moved => X_Date_Last_Moved,
1173 X_Progress_percentage => l_progressPercentage,
1174 X_Attribute_Category => X_Attribute_Category,
1175 X_Attribute1 => X_Attribute1,
1176 X_Attribute2 => X_Attribute2,
1177 X_Attribute3 => X_Attribute3,
1178 X_Attribute4 => X_Attribute4,
1179 X_Attribute5 => X_Attribute5,
1180 X_Attribute6 => X_Attribute6,
1181 X_Attribute7 => X_Attribute7,
1182 X_Attribute8 => X_Attribute8,
1183 X_Attribute9 => X_Attribute9,
1184 X_Attribute10 => X_Attribute10,
1185 X_Attribute11 => X_Attribute11,
1186 X_Attribute12 => X_Attribute12,
1187 X_Attribute13 => X_Attribute13,
1188 X_Attribute14 => X_Attribute14,
1189 X_Attribute15 => X_Attribute15,
1190 X_CHECK_SKILL => null);
1191 end Lock_Row;
1192
1193
1194 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1195 X_Wip_Entity_Id NUMBER,
1196 X_Operation_Seq_Num NUMBER,
1197 X_Organization_Id NUMBER,
1198 X_Repetitive_Schedule_Id NUMBER,
1199 X_Last_Update_Date DATE,
1200 X_Last_Updated_By NUMBER,
1201 X_Last_Update_Login NUMBER,
1202 X_Operation_Sequence_Id NUMBER,
1203 X_Standard_Operation_Id NUMBER,
1204 X_Department_Id NUMBER,
1205 X_Description VARCHAR2,
1206 X_Scheduled_Quantity NUMBER,
1207 X_Quantity_In_Queue NUMBER,
1208 X_Quantity_Running NUMBER,
1209 X_Quantity_Waiting_To_Move NUMBER,
1210 X_Quantity_Rejected NUMBER,
1211 X_Quantity_Scrapped NUMBER,
1212 X_Quantity_Completed NUMBER,
1213 X_First_Unit_Start_Date DATE,
1214 X_First_Unit_Completion_Date DATE,
1215 X_Last_Unit_Start_Date DATE,
1216 X_Last_Unit_Completion_Date DATE,
1217 X_Count_Point_Type NUMBER,
1218 X_Backflush_Flag NUMBER,
1219 X_Minimum_Transfer_Quantity NUMBER,
1220 X_Date_Last_Moved DATE,
1221 X_Attribute_Category VARCHAR2,
1222 X_Attribute1 VARCHAR2,
1223 X_Attribute2 VARCHAR2,
1224 X_Attribute3 VARCHAR2,
1225 X_Attribute4 VARCHAR2,
1226 X_Attribute5 VARCHAR2,
1227 X_Attribute6 VARCHAR2,
1228 X_Attribute7 VARCHAR2,
1229 X_Attribute8 VARCHAR2,
1230 X_Attribute9 VARCHAR2,
1231 X_Attribute10 VARCHAR2,
1232 X_Attribute11 VARCHAR2,
1233 X_Attribute12 VARCHAR2,
1234 X_Attribute13 VARCHAR2,
1235 X_Attribute14 VARCHAR2,
1236 X_Attribute15 VARCHAR2
1237 ) is
1238 l_progressPercentage number;
1239 begin
1240 Update_Row(X_Rowid => X_Rowid,
1241 X_Wip_Entity_Id => X_Wip_Entity_Id,
1242 X_Operation_Seq_Num => X_Operation_Seq_Num,
1243 X_Organization_Id => X_Organization_Id,
1244 X_Repetitive_Schedule_Id => X_Repetitive_Schedule_Id,
1245 X_Last_Update_Date => X_Last_Update_Date,
1246 X_Last_Updated_By => X_Last_Updated_By,
1247 X_Last_Update_Login => X_Last_Update_Login,
1248 X_Operation_Sequence_Id => X_Operation_Sequence_Id,
1249 X_Standard_Operation_Id => X_Standard_Operation_Id,
1250 X_Department_Id => X_Department_Id,
1251 X_Description => X_Description,
1252 X_Scheduled_Quantity => X_Scheduled_Quantity,
1253 X_Quantity_In_Queue => X_Quantity_In_Queue,
1254 X_Quantity_Running => X_Quantity_Running,
1255 X_Quantity_Waiting_To_Move => X_Quantity_Waiting_To_Move,
1256 X_Quantity_Rejected => X_Quantity_Rejected,
1257 X_Quantity_Scrapped => X_Quantity_Scrapped,
1258 X_Quantity_Completed => X_Quantity_Completed,
1259 X_First_Unit_Start_Date => X_First_Unit_Start_Date,
1260 X_First_Unit_Completion_Date => X_First_Unit_Completion_Date,
1261 X_Last_Unit_Start_Date => X_Last_Unit_Start_Date,
1262 X_Last_Unit_Completion_Date => X_Last_Unit_Completion_Date,
1263 X_Count_Point_Type => X_Count_Point_Type,
1264 X_Backflush_Flag => X_Backflush_Flag,
1265 X_Minimum_Transfer_Quantity => X_Minimum_Transfer_Quantity,
1266 X_Date_Last_Moved => X_Date_Last_Moved,
1267 X_Progress_percentage => l_progressPercentage,
1268 X_Attribute_Category => X_Attribute_Category,
1269 X_Attribute1 => X_Attribute1,
1270 X_Attribute2 => X_Attribute2,
1271 X_Attribute3 => X_Attribute3,
1272 X_Attribute4 => X_Attribute4,
1273 X_Attribute5 => X_Attribute5,
1274 X_Attribute6 => X_Attribute6,
1275 X_Attribute7 => X_Attribute7,
1276 X_Attribute8 => X_Attribute8,
1277 X_Attribute9 => X_Attribute9,
1278 X_Attribute10 => X_Attribute10,
1279 X_Attribute11 => X_Attribute11,
1280 X_Attribute12 => X_Attribute12,
1281 X_Attribute13 => X_Attribute13,
1282 X_Attribute14 => X_Attribute14,
1283 X_Attribute15 => X_Attribute15,
1284 X_CHECK_SKILL => null);
1285 end Update_Row;
1286
1287 PROCEDURE Delete_Operation(P_Organization_Id NUMBER,
1288 P_Wip_Entity_Id NUMBER,
1289 P_Operation_Seq_Num NUMBER,
1290 X_Return_Status OUT NOCOPY VARCHAR2,
1291 X_Error_Message OUT NOCOPY VARCHAR2)
1292 IS
1293 l_Prev_Op_Seq NUMBER;
1294 l_Next_Op_Seq NUMBER;
1295
1296 BEGIN
1297
1298 X_Return_Status := fnd_api.g_ret_sts_success;
1299 g_start_date := null;
1300 Valid_Op_Delete(P_Organization_Id => P_Organization_Id,
1301 P_Wip_Entity_Id => P_Wip_Entity_Id,
1302 P_Operation_Seq_Num => P_Operation_Seq_Num,
1303 X_Return_Status => X_Return_Status,
1304 X_Error_Message => X_Error_Message);
1305
1306 if (X_Return_Status <> fnd_api.g_ret_sts_success) then
1307 return;
1308 end if;
1309
1310 WIP_OPERATIONS_UTILITIES.Delete_Resources(
1311 x_wip_entity_id => P_Wip_Entity_Id,
1312 x_organization_id => P_Organization_Id,
1313 x_operation_seq_num => P_Operation_Seq_Num,
1314 x_repetitive_schedule_id => null,
1315 x_return_status => X_Return_Status);
1316
1317 IF(X_Return_Status <> 'S')THEN
1318 wip_utilities.get_message_stack(p_msg =>X_Error_Message);
1319 X_Return_Status := 'W';
1320 END IF;
1321
1322 delete from wip_operation_resources
1323 where wip_entity_id = P_Wip_Entity_Id
1324 and organization_id = P_Organization_Id
1325 and operation_seq_num = P_Operation_Seq_Num;
1326
1327 delete from wip_operation_competencies
1328 where wip_entity_id = P_Wip_Entity_Id
1329 and organization_id = P_Organization_Id
1330 and operation_seq_num = P_Operation_Seq_Num;
1331
1332 delete from wip_op_resource_instances
1333 where wip_entity_id = P_Wip_Entity_Id
1334 and operation_seq_num = P_Operation_Seq_Num
1335 and ORGANIZATION_ID = P_Organization_Id;
1336
1337 DELETE WIP_OPERATION_RESOURCE_USAGE
1338 WHERE wip_entity_id = P_Wip_Entity_Id
1339 AND operation_seq_num = P_Operation_Seq_Num
1340 AND organization_id = P_Organization_Id;
1341
1342 DELETE from WIP_SUB_OPERATION_RESOURCES
1343 WHERE wip_entity_id = P_Wip_Entity_Id
1344 AND operation_seq_num = P_Operation_Seq_Num
1345 AND organization_id = P_Organization_Id;
1346
1347 -- Bug 13770893. Adjust Previous and Next operations before deleting it.
1348 WIP_OPERATIONS_UTILITIES.Get_Prev_Next_Op(X_Wip_Entity_Id => P_Wip_Entity_Id,
1349 X_Organization_Id => P_Organization_Id,
1350 X_Operation_Seq_Num => P_Operation_Seq_Num,
1351 X_Repetitive_Schedule_Id => null,
1352 X_Insert_Flag => FALSE,
1353 X_Prev_Op_Seq => l_Prev_Op_Seq,
1354 X_Next_Op_Seq => l_Next_Op_Seq);
1355
1356 DELETE from WIP_OPERATIONS
1357 WHERE wip_entity_id = P_Wip_Entity_Id
1358 AND operation_seq_num = P_Operation_Seq_Num
1359 AND organization_id = P_Organization_Id;
1360
1361 WIP_OPERATIONS_UTILITIES.Check_Requirements(P_Wip_Entity_Id,
1362 P_Organization_Id,
1363 P_Operation_Seq_Num,
1364 null,
1365 g_start_date);
1366
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 X_Error_Message := sqlerrm(sqlcode);
1370 X_Return_Status := fnd_api.g_ret_sts_unexp_error;
1371 return;
1372 END Delete_Operation;
1373
1374
1375 PROCEDURE Valid_Op_Delete(P_Organization_Id NUMBER,
1376 P_Wip_Entity_Id NUMBER,
1377 P_Operation_Seq_Num NUMBER,
1378 X_Return_Status OUT NOCOPY VARCHAR2,
1379 X_Error_Message OUT NOCOPY VARCHAR2)
1380 IS
1381 l_serializationStartOp NUMBER;
1382 l_pending_clocks VARCHAR2(1);
1383 l_quantity_in_queue NUMBER;
1384 l_quantity_running NUMBER;
1385 l_quantity_waiting_to_move NUMBER;
1386 l_quantity_rejected NUMBER;
1387 l_quantity_scrapped NUMBER;
1388 l_quantity_completed NUMBER;
1389 BEGIN
1390
1391 X_Return_Status := fnd_api.g_ret_sts_success;
1392
1393 l_pending_clocks := WIP_WS_TIME_ENTRY.IS_CLOCK_PENDING(P_Wip_Entity_Id,P_Operation_Seq_Num);
1394 IF (l_pending_clocks <> 'N') THEN
1395 FND_MESSAGE.set_name('WIP','WIP_PENDING_CLOCKS');
1396 X_Error_Message := fnd_message.get;
1397 X_Return_Status := fnd_api.g_ret_sts_error;
1398 return;
1399 END IF;
1400
1401 select serialization_start_op,scheduled_start_date
1402 into l_serializationStartOp,g_start_date
1403 from wip_discrete_jobs
1404 where wip_entity_id = P_Wip_Entity_Id
1405 and organization_id = P_Organization_Id;
1406
1407 select quantity_in_queue,quantity_running,quantity_waiting_to_move,quantity_rejected,quantity_scrapped,quantity_completed
1408 into l_quantity_in_queue,l_quantity_running,l_quantity_waiting_to_move,l_quantity_rejected,l_quantity_scrapped,l_quantity_completed
1409 from wip_operations
1410 where wip_entity_id= P_Wip_Entity_Id
1411 and organization_id = P_Organization_Id
1412 and operation_seq_num = P_Operation_Seq_Num;
1413
1414 IF P_Operation_Seq_Num = l_serializationStartOp OR
1415 l_quantity_in_queue <> 0 OR
1416 l_quantity_running <> 0 OR
1417 l_quantity_waiting_to_move <> 0 OR
1418 l_quantity_rejected <> 0 OR
1419 l_quantity_scrapped <> 0 OR
1420 l_quantity_completed <> 0 OR
1421 wip_operations_utilities.pending_op_txns(P_Wip_Entity_Id,P_Organization_Id,P_Operation_Seq_Num,null,null) = TRUE
1422 THEN
1423 FND_MESSAGE.SET_NAME('WIP','WIP_DELETE_OPERATION');
1424 X_Error_Message := fnd_message.get;
1425 X_Return_Status := fnd_api.g_ret_sts_error;
1426 return;
1427 END IF;
1428 EXCEPTION
1429 WHEN OTHERS THEN
1430 X_Error_Message := sqlerrm(sqlcode);
1431 X_Return_Status := fnd_api.g_ret_sts_unexp_error;
1432 return;
1433 END Valid_Op_Delete;
1434
1435 END WIP_OPERATIONS_PKG;