[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into wip_sub_operation_resources(
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
wip_entity_id,
repetitive_schedule_id,
operation_seq_num,
resource_seq_num,
resource_id,
uom_code,
basis_type,
activity_id,
standard_rate_flag,
usage_rate_or_amount,
scheduled_flag,
assigned_units,
autocharge_type,
applied_resource_units,
applied_resource_value,
start_date,
completion_date
) values (
SYSDATE,
x_user_id,
SYSDATE,
x_user_id,
x_login_id,
p_org_id,
p_wip_entity_id,
p_first_schedule_id,
p_operation_seq_num,
p_resource_seq_num,
p_resource_id,
p_uom_code,
nvl(p_basis_type, WIP_CONSTANTS.PER_LOT),
p_activity_id,
p_standard_rate_flag,
0, -- usage_rate_or_amount
WIP_CONSTANTS.NO, -- scheduled_flag
1, -- assigned_units
WIP_CONSTANTS.MANUAL, -- autocharge_type
0, -- applied_resource_units
0, -- applied_resource_value
p_start_date,
p_completion_date);
select 'X'
from dual
where c_resource_id =
(select resource_id
from wip_sub_operation_resources
where organization_id = c_org_id
and wip_entity_id = c_wip_entity_id
and operation_seq_num = c_operation_seq_num
and resource_seq_num = c_resource_seq_num
and nvl(repetitive_schedule_id,-1) = nvl(c_first_schedule_id,-1));
select wcti1.operation_seq_num,
wcti1.resource_seq_num
from wip_cost_txn_interface wcti1,
wip_cost_txn_interface wcti2
where wcti1.source_code = 'NEW_RES'
and wcti1.group_id = c_group_id
and wcti1.source_code = wcti2.source_code
and wcti1.group_id = wcti2.group_id
and wcti1.wip_entity_id = wcti2.wip_entity_id
and wcti1.operation_seq_num = wcti2.operation_seq_num
and wcti1.resource_seq_num = wcti2.resource_seq_num
and wcti1.organization_id = wcti2.organization_id
and nvl(wcti1.repetitive_schedule_id, -1)
= nvl(wcti2.repetitive_schedule_id, -1)
and wcti1.resource_id <> wcti2.resource_id;
insert into wip_sub_operation_resources(
organization_id,
wip_entity_id,
repetitive_schedule_id,
operation_seq_num,
resource_seq_num,
resource_id,
uom_code,
basis_type,
activity_id,
standard_rate_flag,
usage_rate_or_amount,
scheduled_flag,
assigned_units,
autocharge_type,
applied_resource_units,
applied_resource_value,
start_date,
completion_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
select distinct
wcti.organization_id,
wcti.wip_entity_id,
wcti.repetitive_schedule_id,
wcti.operation_seq_num,
wcti.resource_seq_num,
wcti.resource_id,
wcti.primary_uom,
nvl(wcti.basis_type, WIP_CONSTANTS.PER_LOT),
br.default_activity_id,
wcti.standard_rate_flag,
0, -- usage_rate_or_amount
WIP_CONSTANTS.NO, -- scheduled_flag
1, -- assigned_units
WIP_CONSTANTS.MANUAL, -- autocharge_type
0, -- applied_resource_units
0, -- applied_resource_value
wo.first_unit_start_date,
wo.last_unit_completion_date,
SYSDATE,
wcti.last_updated_by,
SYSDATE,
wcti.created_by,
wcti.last_update_login
from bom_resources br,
wip_operations wo,
wip_cost_txn_interface wcti
where wcti.source_code = 'NEW_RES'
and wcti.group_id = p_group_id
and wcti.organization_id = wo.organization_id
and wcti.wip_entity_id = wo.wip_entity_id
and wcti.operation_seq_num = wo.operation_seq_num
and wcti.resource_id = br.resource_id
and nvl(wcti.repetitive_schedule_id, -1)
= nvl(wo.repetitive_schedule_id, -1);
delete from wip_cost_txn_interface
where group_id = p_group_id
and transaction_quantity is NULL;
update wip_cost_txn_interface
set source_code = NULL -- clear source code to remove NEW_RES message
where group_id = p_group_id;
SELECT 'No PO/REQ Linked'
FROM DUAL
WHERE NOT EXISTS
(SELECT '1'
FROM PO_DISTRIBUTIONS_ALL PD,
WIP_SUB_OPERATION_RESOURCES WOR
/* Fixed bug 3115844 */
WHERE pd.po_line_id IS NOT NULL
AND pd.line_location_id IS NOT NULL
AND WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM
AND WOR.WIP_ENTITY_ID = p_wip_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num)
AND NOT EXISTS
(SELECT '1'
FROM PO_REQUISITION_LINES_ALL PRL,
WIP_SUB_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM
AND WOR.WIP_ENTITY_ID = p_wip_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num)
AND NOT EXISTS
(SELECT '1'
FROM PO_REQUISITIONS_INTERFACE PRI,
WIP_SUB_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PRI.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = PRI.DESTINATION_ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = PRI.WIP_OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = PRI.WIP_RESOURCE_SEQ_NUM
AND WOR.WIP_ENTITY_ID = p_wip_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num);
SELECT 'No PO/REQ Linked'
FROM DUAL
WHERE NOT EXISTS
(SELECT '1'
FROM PO_DISTRIBUTIONS_ALL PD,
WIP_SUB_OPERATION_RESOURCES WOR
/* Fixed bug 3115844 */
WHERE pd.po_line_id IS NOT NULL
AND pd.line_location_id IS NOT NULL
AND WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM
AND WOR.REPETITIVE_SCHEDULE_ID =
PD.WIP_REPETITIVE_SCHEDULE_ID
AND WOR.WIP_ENTITY_ID = p_wip_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id)
AND NOT EXISTS
(SELECT '1'
FROM PO_REQUISITION_LINES_ALL PRL,
WIP_SUB_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM
AND WOR.REPETITIVE_SCHEDULE_ID =
PRL.WIP_REPETITIVE_SCHEDULE_ID
AND WOR.WIP_ENTITY_ID = p_wip_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id)
AND NOT EXISTS
(SELECT '1'
FROM PO_REQUISITIONS_INTERFACE_ALL PRI,
WIP_SUB_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PRI.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID = PRI.DESTINATION_ORGANIZATION_ID
AND WOR.OPERATION_SEQ_NUM = PRI.WIP_OPERATION_SEQ_NUM
AND WOR.RESOURCE_SEQ_NUM = PRI.WIP_RESOURCE_SEQ_NUM
AND WOR.REPETITIVE_SCHEDULE_ID =
PRI.WIP_REPETITIVE_SCHEDULE_ID
AND WOR.WIP_ENTITY_ID = p_wip_entity_id
AND WOR.ORGANIZATION_ID = p_org_id
AND WOR.OPERATION_SEQ_NUM = p_operation_seq_num
AND WOR.RESOURCE_SEQ_NUM = p_resource_seq_num
AND WOR.REPETITIVE_SCHEDULE_ID = p_rep_sched_id);
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
X_Wip_Entity_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Resource_Seq_Num NUMBER,
X_Organization_Id NUMBER,
X_Repetitive_Schedule_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Last_Update_Login NUMBER,
X_Resource_Id NUMBER,
X_Uom_Code VARCHAR2,
X_Basis_Type NUMBER,
X_Usage_Rate_Or_Amount NUMBER,
X_Activity_Id NUMBER,
X_Scheduled_Flag NUMBER,
X_Assigned_Units NUMBER,
X_Autocharge_Type NUMBER,
X_Standard_Rate_Flag NUMBER,
X_Applied_Resource_Units NUMBER,
X_Applied_Resource_Value NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Completion_Date DATE,
X_Start_Date DATE,
X_Schedule_Seq_Num NUMBER,
X_Substitute_Group_Num NUMBER,
X_Replacement_Group_Num NUMBER,
X_Setup_Id NUMBER
) IS
CURSOR C IS SELECT rowid FROM WIP_SUB_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 resource_seq_num = X_Resource_Seq_Num
AND (repetitive_Schedule_id = X_Repetitive_Schedule_Id
OR (repetitive_schedule_id IS NULL
AND X_Repetitive_Schedule_Id IS NULL));
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,
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
) VALUES (
X_Wip_Entity_Id,
X_Operation_Seq_Num,
X_Resource_Seq_Num,
X_Organization_Id,
X_Repetitive_Schedule_Id,
X_Last_Update_Date,
X_Last_Updated_By,
X_Creation_Date,
X_Created_By,
X_Last_Update_Login,
X_Resource_Id,
X_Uom_Code,
X_Basis_Type,
X_Usage_Rate_Or_Amount,
X_Activity_Id,
X_Scheduled_Flag,
X_Assigned_Units,
X_Autocharge_Type,
X_Standard_Rate_Flag,
X_Applied_Resource_Units,
X_Applied_Resource_Value,
X_Attribute_Category,
X_Attribute1,
X_Attribute2,
X_Attribute3,
X_Attribute4,
X_Attribute5,
X_Attribute6,
X_Attribute7,
X_Attribute8,
X_Attribute9,
X_Attribute10,
X_Attribute11,
X_Attribute12,
X_Attribute13,
X_Attribute14,
X_Attribute15,
X_Completion_Date,
X_Start_Date,
X_Schedule_Seq_Num,
X_Substitute_Group_Num,
X_Replacement_Group_Num,
X_Setup_Id
);
END Insert_Row;
SELECT *
FROM WIP_SUB_OPERATION_RESOURCES
WHERE rowid = X_Rowid
FOR UPDATE of Wip_Entity_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Wip_Entity_Id NUMBER,
X_Operation_Seq_Num NUMBER,
X_Resource_Seq_Num NUMBER,
X_Organization_Id NUMBER,
X_Repetitive_Schedule_Id NUMBER,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Last_Update_Login NUMBER,
X_Resource_Id NUMBER,
X_Uom_Code VARCHAR2,
X_Basis_Type NUMBER,
X_Usage_Rate_Or_Amount NUMBER,
X_Activity_Id NUMBER,
X_Scheduled_Flag NUMBER,
X_Assigned_Units NUMBER,
X_Autocharge_Type NUMBER,
X_Standard_Rate_Flag NUMBER,
X_Applied_Resource_Units NUMBER,
X_Applied_Resource_Value NUMBER,
X_Attribute_Category VARCHAR2,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Completion_Date DATE,
X_Start_Date DATE,
X_Schedule_Seq_Num NUMBER,
X_Substitute_Group_Num NUMBER,
X_Replacement_Group_Num NUMBER,
X_Setup_Id NUMBER
) IS
BEGIN
UPDATE WIP_SUB_OPERATION_RESOURCES
SET
wip_entity_id = X_Wip_Entity_Id,
operation_seq_num = X_Operation_Seq_Num,
resource_seq_num = X_Resource_Seq_Num,
organization_id = X_Organization_Id,
repetitive_schedule_id = X_Repetitive_Schedule_Id,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
last_update_login = X_Last_Update_Login,
resource_id = X_Resource_Id,
uom_code = X_Uom_Code,
basis_type = X_Basis_Type,
usage_rate_or_amount = X_Usage_Rate_Or_Amount,
activity_id = X_Activity_Id,
scheduled_flag = X_Scheduled_Flag,
assigned_units = X_Assigned_Units,
autocharge_type = X_Autocharge_Type,
standard_rate_flag = X_Standard_Rate_Flag,
applied_resource_units = X_Applied_Resource_Units,
applied_resource_value = X_Applied_Resource_Value,
attribute_category = X_Attribute_Category,
attribute1 = X_Attribute1,
attribute2 = X_Attribute2,
attribute3 = X_Attribute3,
attribute4 = X_Attribute4,
attribute5 = X_Attribute5,
attribute6 = X_Attribute6,
attribute7 = X_Attribute7,
attribute8 = X_Attribute8,
attribute9 = X_Attribute9,
attribute10 = X_Attribute10,
attribute11 = X_Attribute11,
attribute12 = X_Attribute12,
attribute13 = X_Attribute13,
attribute14 = X_Attribute14,
attribute15 = X_Attribute15,
completion_date = X_Completion_Date,
start_date = X_Start_Date,
schedule_seq_num = X_Schedule_Seq_Num,
substitute_group_num = X_Substitute_Group_Num,
replacement_group_num = X_Replacement_Group_Num,
setup_id = X_Setup_Id
WHERE rowid = X_rowid;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM WIP_SUB_OPERATION_RESOURCES
WHERE rowid = X_Rowid;
END Delete_Row;
SELECT resource_seq_num
FROM WIP_OPERATION_RESOURCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
substitute_group_num = l_Substitute_Group_Num;
SELECT COUNT(*) into l_replacement_groups_exist
FROM wip_operation_resources
WHERE wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
substitute_group_num = l_Substitute_Group_Num and
replacement_group_num = nvl(l_Replacement_Group_Num,0);
select organization_id
into l_org_id
from wip_entities
where wip_entity_id = l_wip_entity_id;
select line_id
into l_line_id
from wip_repetitive_schedules
where wip_entity_id = l_wip_entity_id
and repetitive_schedule_id = l_repetitive_sched_id;
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
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,
nvl(replacement_group_num, 0),
setup_id
FROM WIP_OPERATION_RESOURCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
substitute_group_num = l_Substitute_Group_Num and
parent_resource_seq IS NULL;
DELETE FROM WIP_OPERATION_RESOURCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
resource_seq_num = res_rec.resource_seq_num;
DELETE FROM WIP_OPERATION_RESOURCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
parent_resource_seq = res_rec.resource_seq_num;
DELETE FROM WIP_OPERATION_RESOURCE_USAGE
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
resource_seq_num = res_rec.resource_seq_num;
DELETE FROM WIP_OP_RESOURCE_INSTANCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
operation_seq_num = l_Operation_Seq_Num and
resource_seq_num = res_rec.resource_seq_num;
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,
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,
parent_resource_seq,
setup_id)
SELECT
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,
NULL,
setup_id
FROM WIP_SUB_OPERATION_RESOURCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
substitute_group_num = l_Substitute_Group_Num and
replacement_group_num = nvl(l_Replacement_Group_Num,0);
select br1.supply_subinventory, br1.supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from bom_resources br1, WIP_SUB_OPERATION_RESOURCES wsor1
where br1.resource_id = wsor1.resource_id
and br1.organization_id = wsor1.organization_id
and wsor1.wip_entity_id = l_Wip_Entity_Id
and NVL(wsor1.repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1)
and wsor1.operation_seq_num = l_Operation_Seq_Num
and wsor1.substitute_group_num = l_Substitute_Group_Num
and wsor1.replacement_group_num = nvl(l_Replacement_Group_Num,0)
and wsor1.resource_seq_num in
(select min(wsor2.resource_seq_num)
from bom_resources br2, WIP_SUB_OPERATION_RESOURCES wsor2
where wsor2.wip_entity_id = wsor1.wip_entity_id
and NVL(wsor2.repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1)
and wsor2.organization_id= wsor1.organization_id
and wsor2.operation_seq_num = wsor1.operation_seq_num
and wsor2.substitute_group_num = wsor1.substitute_group_num
and wsor2.replacement_group_num = wsor1.replacement_group_num
and br2.supply_subinventory is not null
and br2.organization_id = wsor2.organization_id
and br2.resource_id = wsor2.resource_id
and br2.resource_type= 1); -- machine type
select wp.default_pull_supply_subinv, wp.default_pull_supply_locator_id
into l_supply_subinventory, l_supply_locator_id
from wip_parameters wp, wip_entities we
where we.wip_entity_id = l_Wip_Entity_Id
and wp.organization_id = we.organization_id;
wip_picking_pub.Update_Requirement_SubinvLoc(p_wip_entity_id => l_Wip_Entity_Id,
p_repetitive_schedule_id => l_Repetitive_Sched_Id,
p_operation_seq_num => l_Operation_Seq_Num,
p_supply_subinventory => l_supply_subinventory,
p_supply_locator_id => l_supply_locator_id,
x_return_status => x_status,
x_msg_data => x_msg_data);
'wip_picking_pub.Update_Requirement_SubinvLoc failed..', l_dummy2);
DELETE FROM WIP_SUB_OPERATION_RESOURCES
WHERE
wip_entity_id = l_Wip_Entity_Id and
NVL(repetitive_schedule_id, -1) = NVL(l_Repetitive_Sched_Id, -1) and
operation_seq_num = l_Operation_Seq_Num and
substitute_group_num = l_Substitute_Group_Num and
replacement_group_num = nvl(l_Replacement_Group_Num,0);
/* Fix for bug 4996849. Comment following lines as delete statement is required.*/
/*
exception when others then null ;
wip_utilities.get_message_stack(p_delete_stack => fnd_api.g_true,
p_msg => x_msg_data);
select wor.applied_resource_units,
wor.resource_seq_num
from wip_operation_resources wor
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_entity_id
and wor.operation_seq_num = p_op_seq
and wor.substitute_group_num = decode(wor.parent_resource_seq,NULL,p_sub_group,wor.substitute_group_num)
and (wor.parent_resource_seq is null or wor.parent_resource_seq in
(select wor.resource_seq_num
from wip_operation_resources wor1
where wor1.organization_id = p_org_id
and wor1.wip_entity_id = p_wip_entity_id
and wor1.operation_seq_num = p_op_seq
and wor1.substitute_group_num = p_sub_group));