[Home] [Help]
1: PACKAGE BODY WIP_OPERATIONS_PKG AS
2: /* $Header: wipoperb.pls 120.0.12010000.5 2008/11/19 09:41:14 sisankar ship $ */
3:
4: procedure add(
5: p_org_id in number,
29: min(wo2.operation_seq_num)
30: into x_prev_op_seq_num,
31: x_next_op_seq_num
32: from dual sd,
33: wip_operations wo1,
34: wip_operations wo2
35: where wo1.organization_id(+) = p_org_id
36: and wo1.wip_entity_id(+) = decode(1, 1, p_wip_entity_id, sd.dummy)
37: and wo1.operation_seq_num(+) < p_operation_seq_num
30: into x_prev_op_seq_num,
31: x_next_op_seq_num
32: from dual sd,
33: wip_operations wo1,
34: wip_operations wo2
35: where wo1.organization_id(+) = p_org_id
36: and wo1.wip_entity_id(+) = decode(1, 1, p_wip_entity_id, sd.dummy)
37: and wo1.operation_seq_num(+) < p_operation_seq_num
38: and wo2.organization_id(+) = p_org_id
42: /* For Enhancement#2864382. Calculate cumulative_scrap_quantity for this operation */
43:
44: SELECT SUM(quantity_scrapped)
45: INTO l_scrap_qty
46: FROM wip_operations
47: WHERE organization_id = p_org_id
48: AND wip_entity_id = p_wip_entity_id
49: AND operation_seq_num < p_operation_seq_num;
50:
53: END IF;
54:
55: -- if prev operation exists in routing
56: if (x_prev_op_seq_num is NOT NULL) then
57: update wip_operations
58: set next_operation_seq_num = p_operation_seq_num
59: where organization_id = p_org_id
60: and wip_entity_id = p_wip_entity_id
61: and operation_seq_num = x_prev_op_seq_num;
62: end if;
63:
64: -- if next operation exists in routing
65: if (x_next_op_seq_num is NOT NULL) then
66: update wip_operations
67: set previous_operation_seq_num = p_operation_seq_num
68: where organization_id = p_org_id
69: and wip_entity_id = p_wip_entity_id
70: and operation_seq_num = x_next_op_seq_num;
71: end if;
72:
73: -- add operation
74: begin
75: insert into wip_operations(
76: wip_entity_id,
77: operation_seq_num,
78: organization_id,
79: last_update_date,
167: bso.attribute13,
168: bso.attribute14,
169: bso.attribute15,
170: bso.check_skill
171: from wip_operations wo1,
172: wip_discrete_jobs wdj,
173: bom_standard_operations bso
174: where bso.standard_operation_id(+) = p_operation_id
175: /* %cfm Ignore cfm ops. */
277: bsor.attribute15,
278: bsor.substitute_group_num, -- Added : -- bug 7371859
279: bsor.resource_seq_num, -- Added : -- bug 7371859(Schedule Seq Num= Resource Seq Num)
280: 0 -- Added : -- bug 7371859
281: from wip_operations wo,
282: bom_resources br,
283: bom_std_op_resources bsor
284: where bsor.standard_operation_id = p_operation_id
285: and br.resource_id = bsor.resource_id
315: BOS.STANDARD_OPERATION_ID, BOS.COMPETENCE_ID, BOS.RATING_LEVEL_ID,
316: BOS.QUALIFICATION_TYPE_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY,
317: WO.LAST_UPDATE_LOGIN, WO.CREATED_BY, WO.CREATION_DATE
318: FROM BOM_OPERATION_SKILLS BOS,
319: WIP_OPERATIONS WO,
320: WIP_ENTITIES WE
321: WHERE
322: WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
323: AND WO.ORGANIZATION_ID = WO.ORGANIZATION_ID
458: exception
459: when others then
460: wip_constants.get_ora_error(
461: application => 'WIP',
462: proc_name => 'WIP_OPERATIONS_PKG.ADD');
463: fnd_message.raise_error;
464:
465: end add;
466:
513: X_Attribute13 VARCHAR2,
514: X_Attribute14 VARCHAR2,
515: X_Attribute15 VARCHAR2,
516: X_CHECK_SKILL NUMBER DEFAULT NULL) IS
517: CURSOR C IS SELECT rowid FROM WIP_OPERATIONS
518: WHERE wip_entity_id = X_Wip_Entity_Id
519: AND operation_seq_num = X_Operation_Seq_Num
520: AND organization_id = X_Organization_Id
521: AND (repetitive_Schedule_id = X_Repetitive_Schedule_Id
535: eration */
536:
537: SELECT SUM(quantity_scrapped)
538: INTO l_scrap_qty
539: FROM wip_operations
540: WHERE organization_id = X_Organization_Id
541: AND wip_entity_id = X_Wip_Entity_Id
542: AND (repetitive_schedule_id = X_Repetitive_Schedule_Id OR
543: (repetitive_schedule_id IS NULL AND X_Repetitive_Schedule_Id IS NULL))
546: IF (l_scrap_qty IS NULL) THEN
547: l_scrap_qty :=0;
548: END IF;
549:
550: INSERT INTO WIP_OPERATIONS(
551: wip_entity_id,
552: operation_seq_num,
553: organization_id,
554: repetitive_schedule_id,
692: BOS.STANDARD_OPERATION_ID, BOS.COMPETENCE_ID, BOS.RATING_LEVEL_ID,
693: BOS.QUALIFICATION_TYPE_ID, WO.LAST_UPDATE_DATE, WO.LAST_UPDATED_BY,
694: WO.LAST_UPDATE_LOGIN, WO.CREATED_BY, WO.CREATION_DATE
695: FROM BOM_OPERATION_SKILLS BOS,
696: WIP_OPERATIONS WO,
697: WIP_ENTITIES WE
698: WHERE
699: WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
700: AND WO.ORGANIZATION_ID = WO.ORGANIZATION_ID
763:
764: ) IS
765: CURSOR C IS
766: SELECT *
767: FROM WIP_OPERATIONS
768: WHERE rowid = X_Rowid
769: FOR UPDATE of Wip_Entity_Id NOWAIT;
770: Recinfo C%ROWTYPE;
771: BEGIN
933: X_CHECK_SKILL NUMBER DEFAULT NULL
934:
935: ) IS
936: BEGIN
937: UPDATE WIP_OPERATIONS
938: SET
939: wip_entity_id = X_Wip_Entity_Id,
940: operation_seq_num = X_Operation_Seq_Num,
941: organization_id = X_Organization_Id,
989: END Update_Row;
990:
991: PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
992: BEGIN
993: DELETE FROM WIP_OPERATIONS
994: WHERE rowid = X_Rowid;
995:
996: if (SQL%NOTFOUND) then
997: Raise NO_DATA_FOUND;
1281: X_Attribute15 => X_Attribute15,
1282: X_CHECK_SKILL => null);
1283: end Update_Row;
1284:
1285: END WIP_OPERATIONS_PKG;