[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
SELECT count(*)
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
SELECT 1
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND (FM_OPERATION_SEQ_NUM = X_Operation_Seq_Num
OR TO_OPERATION_SEQ_NUM = X_Operation_Seq_Num);
SELECT 1
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
AND APPLIED_RESOURCE_UNITS <> 0;
SELECT 1
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
SELECT 1
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND LINE_ID = X_Line_Id
AND (FM_OPERATION_SEQ_NUM = X_Operation_Seq_Num
OR TO_OPERATION_SEQ_NUM = X_Operation_Seq_Num);
SELECT 1
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
AND APPLIED_RESOURCE_UNITS <> 0;
SELECT 1
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND LINE_ID = X_Line_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
select max(operation_seq_num)
from wip_operations
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num < X_Operation_Seq_Num;
select max(operation_seq_num)
from wip_operations
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num < X_Operation_Seq_Num
and repetitive_schedule_id = X_Repetitive_Schedule_Id;
X_Insert_Flag BOOLEAN,
X_Prev_Op_Seq IN OUT NOCOPY NUMBER,
X_Next_Op_Seq IN OUT NOCOPY NUMBER) IS
BEGIN
IF X_Repetitive_Schedule_Id IS NULL THEN
select max(operation_seq_num)
into X_Prev_Op_Seq
from wip_operations
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num < X_Operation_Seq_Num;
select min(operation_seq_num)
into X_Next_Op_Seq
from wip_operations
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num > X_Operation_Seq_Num;
select max(operation_seq_num)
into X_Prev_Op_Seq
from wip_operations
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num < X_Operation_Seq_Num
and repetitive_schedule_id = X_Repetitive_Schedule_Id;
select min(operation_seq_num)
into X_Next_Op_Seq
from wip_operations
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num > X_Operation_Seq_Num
and repetitive_schedule_id = X_Repetitive_Schedule_Id;
IF X_Insert_Flag THEN
IF (X_Prev_Op_Seq IS NOT NULL) THEN
Set_Next_Op(X_Wip_Entity_Id,
X_Organization_Id,
X_Prev_Op_Seq,
X_Operation_Seq_Num,
X_Repetitive_Schedule_Id);
ELSE -- Called by pre-delete
IF (X_Prev_Op_Seq IS NOT NULL) THEN
Set_Next_Op(X_Wip_Entity_Id,
X_Organization_Id,
X_Prev_Op_Seq,
X_Next_Op_Seq,
X_Repetitive_Schedule_Id);
update wip_operations
set previous_operation_seq_num = X_Prev_Op_Seq
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num = X_Operation_Seq_Num;
update wip_operations
set previous_operation_seq_num = X_Prev_Op_Seq
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and repetitive_schedule_id = X_Repetitive_Schedule_Id
and operation_seq_num = X_Operation_Seq_Num;
update wip_operations
set next_operation_seq_num = X_Next_Op_Seq
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and operation_seq_num = X_Operation_Seq_Num;
update wip_operations
set next_operation_seq_num = X_Next_Op_Seq
where wip_entity_id = X_Wip_Entity_Id
and organization_id = X_Organization_Id
and repetitive_schedule_id = X_Repetitive_Schedule_Id
and operation_seq_num = X_Operation_Seq_Num;
PROCEDURE Delete_Resources(X_Wip_Entity_Id NUMBER,
X_Organization_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Repetitive_Schedule_Id NUMBER,
x_return_status OUT NOCOPY VARCHAR2) IS
-- remove cursors to check po/req exists because these cursors does not
-- consider canceled po/req. Morever, we already had an API to do this job
-- , so we will call wip_osp.po_req_exists instead.
l_propagate_job_change_to_po NUMBER;
SELECT propagate_job_change_to_po
INTO l_propagate_job_change_to_po
FROM wip_parameters
WHERE organization_id = x_organization_id;
fnd_message.set_name('WIP','WIP_DELETE_OSP_RESOURCE');
fnd_message.set_name('WIP','WIP_DELETE_OSP_RESOURCE');
END Delete_Resources;
PROCEDURE Insert_Resources(X_Wip_Entity_Id NUMBER,
X_Organization_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Standard_Operation_Id NUMBER,
X_Repetitive_Schedule_Id NUMBER,
X_Last_Updated_By NUMBER,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Start_Date DATE,
X_Completion_Date DATE) IS
/* Added : -- bug 7371859 */
sub_res_count number;
INSERT INTO WIP_OPERATION_RESOURCES
(WIP_ENTITY_ID, OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM, ORGANIZATION_ID,
REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN,
RESOURCE_ID, UOM_CODE,
BASIS_TYPE, USAGE_RATE_OR_AMOUNT,
ACTIVITY_ID, SCHEDULED_FLAG,
ASSIGNED_UNITS, AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS,
APPLIED_RESOURCE_VALUE, START_DATE,
COMPLETION_DATE, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3,
ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9,
ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15,
SUBSTITUTE_GROUP_NUM, SCHEDULE_SEQ_NUM,PRINCIPLE_FLAG,REPLACEMENT_GROUP_NUM) --/* Added : ---- bug 7371859
SELECT X_Wip_Entity_Id, X_Operation_Seq_Num,
S.RESOURCE_SEQ_NUM, X_Organization_Id,
X_Repetitive_Schedule_Id, SYSDATE,
X_Last_Updated_By, SYSDATE,
X_Created_By, X_Last_Update_Login,
S.RESOURCE_ID, R.UNIT_OF_MEASURE,
S.BASIS_TYPE, S.USAGE_RATE_OR_AMOUNT,
S.ACTIVITY_ID, S.SCHEDULE_FLAG,
S.ASSIGNED_UNITS, S.AUTOCHARGE_TYPE,
S.STANDARD_RATE_FLAG, 0,
0,
DECODE(X_Start_Date, NULL, SYSDATE, X_Start_Date),
DECODE(X_Completion_Date, NULL, SYSDATE, X_Completion_Date),
S.ATTRIBUTE_CATEGORY,
S.ATTRIBUTE1, S.ATTRIBUTE2, S.ATTRIBUTE3,
S.ATTRIBUTE4, S.ATTRIBUTE5, S.ATTRIBUTE6,
S.ATTRIBUTE7, S.ATTRIBUTE8, S.ATTRIBUTE9,
S.ATTRIBUTE10, S.ATTRIBUTE11, S.ATTRIBUTE12,
S.ATTRIBUTE13, S.ATTRIBUTE14, S.ATTRIBUTE15,
S.SUBSTITUTE_GROUP_NUM, S.SCHEDULE_SEQ_NUM,S.PRINCIPLE_FLAG,0 --/* Added : -- bug 7371859
FROM BOM_STD_OP_RESOURCES S,
BOM_RESOURCES R
WHERE S.STANDARD_OPERATION_ID = X_Standard_Operation_Id
AND R.RESOURCE_ID = S.RESOURCE_ID
AND NVL(R.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
SELECT count(*)
INTO sub_res_count
FROM BOM_STD_SUB_OP_RESOURCES BSSOR
WHERE BSSOR.STANDARD_OPERATION_ID=X_Standard_Operation_Id;
SELECT nvl(max(resource_seq_num), 10)
INTO l_wsor_max_res_seq_num
FROM WIP_SUB_OPERATION_RESOURCES WSOR
WHERE wip_entity_id = x_wip_entity_id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num;
INSERT INTO WIP_SUB_OPERATION_RESOURCES
(wip_entity_id,
operation_seq_num,
resource_seq_num,
organization_id,
repetitive_schedule_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
resource_id,
uom_code,
basis_type,
usage_rate_or_amount,
activity_id,
scheduled_flag,
assigned_units,
maximum_assigned_units,
autocharge_type,
standard_rate_flag,
applied_resource_units,
applied_resource_value,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
completion_date,
start_date,
schedule_seq_num,
substitute_group_num,
replacement_group_num,
setup_id)
SELECT X_Wip_Entity_Id,
X_Operation_Seq_Num,
(rownum + l_wsor_max_res_seq_num),
X_Organization_Id,
X_Repetitive_Schedule_Id,
SYSDATE ,
X_Last_Updated_By,
SYSDATE,
X_Created_By,
X_Last_Update_Login,
BSSOR.resource_id,
BR.unit_of_measure,
BSSOR.basis_type,
BSSOR.usage_rate_or_amount,
BSSOR.activity_id,
BSSOR.schedule_flag,
BSSOR.assigned_units,
BSSOR.assigned_units,
BSSOR.autocharge_type,
BSSOR.standard_rate_flag,
0, --WCOR.applied_resource_units,
0, -- WCOR.applied_resource_value,
BSSOR.attribute_category,
BSSOR.attribute1,
BSSOR.attribute2,
BSSOR.attribute3,
BSSOR.attribute4,
BSSOR.attribute5,
BSSOR.attribute6,
BSSOR.attribute7,
BSSOR.attribute8,
BSSOR.attribute9,
BSSOR.attribute10,
BSSOR.attribute11,
BSSOR.attribute12,
BSSOR.attribute13,
BSSOR.attribute14,
BSSOR.attribute15,
DECODE(X_Start_Date, NULL, SYSDATE, X_Start_Date),
DECODE(X_Completion_Date, NULL, SYSDATE, X_Completion_Date),
BSSOR.schedule_seq_num ,
BSSOR.substitute_group_num,
BSSOR.replacement_group_num,
NULL --setup_id
FROM BOM_RESOURCES BR,
BOM_STD_SUB_OP_RESOURCES BSSOR
where bssor.standard_operation_id=X_Standard_Operation_Id
and BSSOR.RESOURCE_ID = BR.RESOURCE_ID;
END Insert_Resources;
SELECT COUNT(R.RESOURCE_ID)
INTO P_Num_Resources
FROM BOM_STD_OP_RESOURCES S,
BOM_RESOURCES R
WHERE S.STANDARD_OPERATION_ID = X_Standard_Operation_Id
AND R.ORGANIZATION_ID = X_Organization_Id
AND R.RESOURCE_ID = S.RESOURCE_ID
AND NVL(R.DISABLE_DATE, SYSDATE + 1) > SYSDATE;
1) When an Operation is Deleted
2) When the routing of a job/schedule is updated
*/
PROCEDURE Check_Requirements(X_Wip_Entity_Id NUMBER,
X_Organization_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Repetitive_Schedule_Id NUMBER,
X_Entity_Start_Date DATE) IS
firstop NUMBER;
SELECT nvl(min(operation_seq_num),0)
INTO firstop
FROM wip_operations
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id;
SELECT nvl(min(operation_seq_num),0)
INTO firstop
FROM wip_operations
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
SELECT department_id, first_unit_start_date
INTO firstdep, firstdate
FROM wip_operations wo
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = firstop;
SELECT department_id, first_unit_start_date
INTO firstdep, firstdate
FROM wip_operations wo
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = firstop
AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
/* Update the department and date required of requirements to
those of their new operation */
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
DEPARTMENT_ID = firstdep,
DATE_REQUIRED = firstdate
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num
AND NOT EXISTS
(SELECT 'checking for duplicate requirements'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * firstop
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
We don't want to do this IF the deleted op is Op Seq 1 and
there are no other ops.
*/
IF X_Operation_Seq_Num NOT IN (-1, 1) OR firstdep IS NOT NULL THEN
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET (WIP_SUPPLY_TYPE, REQUIRED_QUANTITY,
QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY) =
(SELECT LEAST(WRO.WIP_SUPPLY_TYPE, WIP_SUPPLY_TYPE),
WRO.REQUIRED_QUANTITY + REQUIRED_QUANTITY,
WRO.QUANTITY_ISSUED + QUANTITY_ISSUED,
WRO.QUANTITY_PER_ASSEMBLY + QUANTITY_PER_ASSEMBLY
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) *
X_Operation_Seq_Num
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID)
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop
AND EXISTS
(SELECT 'checking for duplicate requirements'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * X_Operation_Seq_Num
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
DELETE FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
OR OPERATION_SEQ_NUM = X_Operation_Seq_Num * -1);
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
DEPARTMENT_ID = firstdep,
DATE_REQUIRED = firstdate
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num;
/* Update the department and date required of requirements to
those of their new operation */
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
DEPARTMENT_ID = firstdep,
DATE_REQUIRED = firstdate
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num
AND NOT EXISTS
(SELECT 'checking for duplicate requirements'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * firstop
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
We don't want to do this IF the deleted op is Op Seq 1 and
there are no other ops.
*/
IF X_Operation_Seq_Num NOT IN (-1, 1) OR firstdep IS NOT NULL THEN
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET (WIP_SUPPLY_TYPE, REQUIRED_QUANTITY,
QUANTITY_ISSUED, QUANTITY_PER_ASSEMBLY) =
(SELECT LEAST(WRO.WIP_SUPPLY_TYPE, WIP_SUPPLY_TYPE),
WRO.REQUIRED_QUANTITY + REQUIRED_QUANTITY,
WRO.QUANTITY_ISSUED + QUANTITY_ISSUED,
WRO.QUANTITY_PER_ASSEMBLY + QUANTITY_PER_ASSEMBLY
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) *
X_Operation_Seq_Num
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID)
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop
AND EXISTS
(SELECT 'checking for duplicate requirements'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = SIGN(WRO.OPERATION_SEQ_NUM) * X_Operation_Seq_Num
AND INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID);
DELETE FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
OR OPERATION_SEQ_NUM = X_Operation_Seq_Num * -1)
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * firstop,
DEPARTMENT_ID = firstdep,
DATE_REQUIRED = firstdate
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) * X_Operation_Seq_Num
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
SELECT count(*)
INTO opseq
FROM WIP_REQUIREMENT_OPERATIONS
WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
AND ORGANIZATION_ID = X_Organization_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
AND WIP_SUPPLY_TYPE = 2;
SELECT count(*)
INTO opseq
FROM WIP_REQUIREMENT_OPERATIONS
WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND ORGANIZATION_ID = X_Organization_Id
AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
AND WIP_SUPPLY_TYPE = 2;
SELECT count(resource_id)
INTO resct
FROM wip_operation_resources
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = X_Operation_Seq_Num;
SELECT count(resource_id)
INTO resct
FROM wip_operation_resources
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = X_Operation_Seq_Num
AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
UPDATE wip_operations
SET first_unit_start_date = DECODE(SIGN(X_First_Unit_Start_Date-
X_Resource_Start_Date),
-1, X_First_Unit_Start_Date,
X_Resource_Start_Date),
last_unit_completion_date = DECODE(SIGN(X_Last_Unit_Completion_Date-
X_Resource_Completion_Date),
-1, X_Resource_Completion_Date,
X_Last_Unit_Completion_Date)
WHERE
wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = X_Operation_Seq_Num;
UPDATE wip_operations
SET first_unit_start_date = DECODE(SIGN(X_First_Unit_Start_Date-
X_Resource_Start_Date),
-1, X_First_Unit_Start_Date,
X_Resource_Start_Date),
last_unit_completion_date = DECODE(SIGN(X_Last_Unit_Completion_Date-
X_Resource_Completion_Date),
-1, X_Resource_Completion_Date,
X_Last_Unit_Completion_Date)
WHERE
wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND operation_seq_num = X_Operation_Seq_Num
AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
UPDATE wip_discrete_jobs
SET scheduled_start_date = X_First_Unit_Start_Date,
scheduled_completion_date = X_Last_Unit_Completion_Date
WHERE
wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id;
UPDATE wip_repetitive_schedules
SET first_unit_start_date = X_First_Unit_Start_Date,
last_unit_completion_date = X_Last_Unit_Completion_Date
WHERE wip_entity_id = X_Wip_Entity_Id
AND organization_id = X_Organization_Id
AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
PROCEDURE Update_Operationless_Reqs(X_Wip_Entity_Id NUMBER,
X_Organization_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Repetitive_Schedule_Id NUMBER,
X_Department_Id NUMBER,
X_First_Unit_Start_Date DATE) IS
l_msg_data VARCHAR2(240);
wip_picking_pvt.update_allocation_op_seqs(p_wip_entity_id => X_Wip_Entity_Id,
p_operation_seq_num => X_Operation_Seq_Num,
p_repetitive_schedule_id => X_Repetitive_Schedule_Id,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
update wip_discrete_jobs
set serialization_start_op = decode(serialization_start_op, 1, X_Operation_Seq_Num, null)
where wip_entity_id = X_Wip_Entity_Id;
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) *
X_Operation_Seq_Num,
DEPARTMENT_ID = X_Department_Id,
DATE_REQUIRED = X_First_Unit_Start_Date
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM in (1,-1)
AND QUANTITY_ISSUED=0
AND NOT EXISTS (SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE MMTT.TRANSACTION_SOURCE_ID=X_Wip_Entity_Id and MMTT.organization_id=X_Organization_Id and MMTT.operation_seq_num in (-1,1)
and MMTT.inventory_item_id=WRO.INVENTORY_ITEM_ID AND mmtt.transaction_source_type_id=INV_Globals.G_SOURCETYPE_WIP);
UPDATE WIP_REQUIREMENT_OPERATIONS wro
SET OPERATION_SEQ_NUM = SIGN(OPERATION_SEQ_NUM) *
X_Operation_Seq_Num,
DEPARTMENT_ID = X_Department_Id,
DATE_REQUIRED = X_First_Unit_Start_Date
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM IN (1,-1)
AND QUANTITY_ISSUED=0
AND NOT EXISTS (SELECT 1 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
WHERE TRANSACTION_SOURCE_ID=X_Wip_Entity_Id and MMTT.organization_id=X_Organization_Id AND MMTT.repetitive_line_id=X_Repetitive_Schedule_Id and
MMTT.operation_seq_num in (-1,1) and MMTT.inventory_item_id=WRO.INVENTORY_ITEM_ID and mmtt.transaction_source_type_id=INV_Globals.G_SOURCETYPE_WIP);
END Update_Operationless_Reqs;
PROCEDURE Update_Reqs(X_Wip_Entity_Id NUMBER,
X_Organization_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Repetitive_Schedule_Id NUMBER,
X_Department_Id NUMBER,
X_Start_Date DATE) IS
BEGIN
/* Added nvl(X_department_id) for bug 5979782 (base bug 5657251)*/
IF X_Repetitive_Schedule_Id IS NULL THEN
UPDATE WIP_REQUIREMENT_OPERATIONS
SET DEPARTMENT_ID = nvl(X_Department_Id,department_id),
--Start : Fix for bug #5177994/5094448 --
DATE_REQUIRED = trunc(NVL(X_Start_Date,DATE_REQUIRED)),
MPS_DATE_REQUIRED = trunc(NVL(X_Start_Date,MPS_DATE_REQUIRED))
-- DATE_REQUIRED = X_Start_Date,
-- MPS_DATE_REQUIRED = X_Start_Date
--End : Fix for bug #5177994/5094448 --
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
OR OPERATION_SEQ_NUM = -1 * X_Operation_Seq_Num);
UPDATE WIP_REQUIREMENT_OPERATIONS
SET DEPARTMENT_ID = X_Department_Id,
--Start : Fix for bug #5177994/5094448 --
DATE_REQUIRED = trunc(NVL(X_Start_Date,DATE_REQUIRED)),
MPS_DATE_REQUIRED = trunc(NVL(X_Start_Date,MPS_DATE_REQUIRED))
-- DATE_REQUIRED = X_Start_Date,
-- MPS_DATE_REQUIRED = X_Start_Date
--End : Fix for bug #5177994/5094448 --
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND (OPERATION_SEQ_NUM = X_Operation_Seq_Num
OR OPERATION_SEQ_NUM = -1 * X_Operation_Seq_Num);
END Update_Reqs;
SELECT FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE
INTO X_First_Unit_Start_Date,
X_Last_Unit_Start_Date,
X_First_Unit_Completion_Date,
X_Last_Unit_Completion_Date
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = X_Prev_Operation_Seq_Num;
SELECT FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_COMPLETION_DATE
INTO X_First_Unit_Start_Date,
X_Last_Unit_Start_Date,
X_First_Unit_Completion_Date,
X_Last_Unit_Completion_Date
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = X_Prev_Operation_Seq_Num;
PROCEDURE Update_Res_Op_Seq(X_Wip_Entity_Id NUMBER,
X_Organization_Id NUMBER,
X_Old_Operation_Seq_Num NUMBER,
X_New_Operation_Seq_Num NUMBER,
X_Repetitive_Schedule_Id NUMBER) IS
BEGIN
IF X_Repetitive_Schedule_Id IS NULL THEN
UPDATE WIP_OPERATION_RESOURCES
SET OPERATION_SEQ_NUM = X_New_Operation_Seq_num
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND OPERATION_SEQ_NUM = X_Old_Operation_Seq_Num;
UPDATE WIP_OPERATION_RESOURCES
SET OPERATION_SEQ_NUM = X_New_Operation_Seq_num
WHERE ORGANIZATION_ID = X_Organization_Id
AND WIP_ENTITY_ID = X_Wip_Entity_Id
AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
AND OPERATION_SEQ_NUM = X_Old_Operation_Seq_Num;
END Update_Res_Op_Seq;
SELECT COUNT(*)
FROM WIP_REPETITIVE_SCHEDULES
WHERE WIP_ENTITY_ID = X_Wip_Entity_Id
AND ORGANIZATION_ID = X_Org_Id
AND LINE_ID = X_Line_Id
AND STATUS_TYPE in (3,4,6);