[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lookup_code INTO x_lookup_code
FROM MFG_LOOKUPS
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active,SYSDATE));
SELECT lookup_code INTO x_lookup_code
FROM MFG_LOOKUPS
WHERE lookup_type = p_lookup_type
AND meaning = p_meaning
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active,SYSDATE));
SELECT instance_id
INTO x_serial_id
FROM BOM_DEPT_RES_INSTANCES
WHERE SERIAL_NUMBER = p_serial_number;
SELECT DISTINCT(BR.RESOURCE_ID)
INTO x_resource_id
FROM BOM_RESOURCES BR, BOM_DEPARTMENT_RESOURCES BDR, AHL_WORKORDER_OPERATIONS_V AWV
WHERE BR.RESOURCE_ID = BDR.RESOURCE_ID AND BDR.DEPARTMENT_ID = AWV.DEPARTMENT_ID
AND AWV.WORKORDER_ID = p_workorder_id AND BR.RESOURCE_CODE = p_resource_code;
PROCEDURE Insert_Row (
X_OPERATION_RESOURCE_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_RESOURCE_ID IN NUMBER,
X_WORKORDER_OPERATION_ID IN NUMBER,
X_RESOURCE_SEQ_NUMBER IN NUMBER,
X_UOM_CODE IN VARCHAR2,
X_QUANTITY IN NUMBER,
X_DURATION IN NUMBER,
X_SCHEDULED_START_DATE IN DATE,
X_SCHEDULED_END_DATE IN DATE,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
-- SUKHWSIN ER # 9014609 -- start
X_RT_OPER_RESOURCE_ID IN NUMBER
-- SUKHWSIN ER # 9014609 -- end
) IS
BEGIN
INSERT INTO AHL_OPERATION_RESOURCES (
OPERATION_RESOURCE_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
RESOURCE_ID ,
WORKORDER_OPERATION_ID ,
RESOURCE_SEQUENCE_NUM ,
--UOM,
QUANTITY ,
DURATION ,
SCHEDULED_START_DATE,
SCHEDULED_END_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
-- SUKHWSIN ER # 9014609 -- start
RT_OPER_RESOURCE_ID)
-- SUKHWSIN ER # 9014609 -- end
VALUES(
X_OPERATION_RESOURCE_ID,
X_OBJECT_VERSION_NUMBER,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_RESOURCE_ID ,
X_WORKORDER_OPERATION_ID ,
X_RESOURCE_SEQ_NUMBER ,
--X_UOM_CODE ,
X_QUANTITY ,
X_DURATION ,
X_SCHEDULED_START_DATE ,
X_SCHEDULED_END_DATE ,
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,
-- SUKHWSIN ER # 9014609 -- start
X_RT_OPER_RESOURCE_ID);
END Insert_Row;
PROCEDURE UPDATE_ROW (
X_OPERATION_RESOURCE_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_RESOURCE_ID IN NUMBER,
X_WORKORDER_OPERATION_ID IN NUMBER,
X_RESOURCE_SEQ_NUMBER IN NUMBER,
X_UOM_CODE IN VARCHAR2,
X_QUANTITY IN NUMBER,
X_DURATION IN NUMBER,
X_SCHEDULED_START_DATE IN DATE,
X_SCHEDULED_END_DATE IN DATE,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
)
IS
BEGIN
UPDATE AHL_OPERATION_RESOURCES SET
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
RESOURCE_ID = X_RESOURCE_ID ,
WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID ,
RESOURCE_SEQUENCE_NUM = X_RESOURCE_SEQ_NUMBER ,
--UOM = X_UOM_CODE ,
QUANTITY = X_QUANTITY ,
DURATION = X_DURATION ,
SCHEDULED_START_DATE = X_SCHEDULED_START_DATE ,
SCHEDULED_END_DATE = X_SCHEDULED_END_DATE ,
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,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
WHERE OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID
AND OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER;
END UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_OPERATION_RESOURCE_ID IN NUMBER
) IS
BEGIN
DELETE FROM AHL_OPERATION_RESOURCES
WHERE OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID;
END DELETE_ROW;
SELECT * FROM AHL_OPERATION_RESOURCES
WHERE OPERATION_RESOURCE_ID = x_id;
SELECT TO_DATE(ACTUAL_START_DATE,'DD-MM-YYYY'), TO_DATE(ACTUAL_END_DATE,'DD-MM-YYYY'),
TO_DATE(SCHEDULED_START_DATE,'DD-MM-YYYY'), TO_DATE(SCHEDULED_END_DATE,'DD-MM-YYYY')
FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;*/
SELECT ACTUAL_START_DATE, ACTUAL_END_DATE,
SCHEDULED_START_DATE, SCHEDULED_END_DATE
FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;
SELECT resource_type
FROM BOM_RESOURCES
WHERE resource_id = p_resource_id;
SELECT WO.workorder_id workorder_id,
WO.object_version_number object_version_number,
WO.wip_entity_id wip_entity_id,
WO.status_code status_code
FROM AHL_WORKORDERS WO,
WIP_SCHED_RELATIONSHIPS WOR
WHERE WO.wip_entity_id = WOR.parent_object_id
AND WO.master_workorder_flag = 'Y'
AND WO.status_code <> '22'
AND WOR.parent_object_type_id = 1
AND WOR.relationship_type = 1
AND WOR.child_object_type_id = 1
AND WOR.child_object_id = c_child_wip_entity_id;
SELECT
wipj.scheduled_completion_date
FROM
ahl_workorders awo,
wip_discrete_jobs wipj
WHERE
wipj.wip_entity_id = awo.wip_entity_id
AND awo.master_workorder_flag = 'Y'
AND awo.visit_task_id IS NULL
AND awo.visit_id = (
SELECT
awov.visit_id
FROM
ahl_workorders awov
WHERE
awov.workorder_id = c_workorder_id
);
SELECT
wipj.firm_planned_flag
FROM
WIP_DISCRETE_JOBS wipj
WHERE
wipj.wip_entity_id = c_wip_entity_id;
'Before calling AHL_PRD_WORKORDER_PVT.update_job'
);
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
p_default => FND_API.G_TRUE ,
p_module_type => NULL ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_wip_load_flag => 'Y' ,
p_x_prd_workorder_rec => l_up_workorder_rec ,
p_x_prd_workoper_tbl => l_up_workoper_tbl
);
'After calling AHL_PRD_WORKORDER_PVT.update_job'
);
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1
FROM AHL_OPERATION_RESOURCES
WHERE OPERATION_RESOURCE_ID = x_id);
SELECT WORKORDER_OPERATION_ID FROM
AHL_WORKORDER_OPERATIONS
--AHL_WORKORDER_OPERATIONS_V
WHERE WORKORDER_ID = x_id AND OPERATION_SEQUENCE_NUM = x_seq;
SELECT COUNT(*) FROM
AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
SELECT * FROM AHL_WORKORDERS
WHERE WORKORDER_ID = x_id;
SELECT DEPARTMENT_ID FROM
BOM_DEPARTMENT_RESOURCES
WHERE RESOURCE_ID = x_id;
SELECT --V.DEPARTMENT_ID, -- department should be from wip_operations
V.ORGANIZATION_ID,
WORKORDER_NAME, WIP_ENTITY_ID FROM
AHL_VISITS_B V, AHL_VISIT_TASKS_B T, AHL_WORKORDERS W
WHERE W.VISIT_TASK_ID = T.VISIT_TASK_ID AND T.VISIT_ID = V.VISIT_ID
AND W.VISIT_TASK_ID = x_id;
SELECT UOM_CODE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = x_name;
SELECT unit_of_measure
FROM bom_resources
WHERE resource_id = x_id;
SELECT first_unit_start_date,
last_unit_completion_date
FROM wip_operations a, ahl_workorders b
WHERE a.wip_entity_id = b.wip_entity_id
AND workorder_id = c_workorder_id
AND operation_seq_num = c_op_seq_num;*/
SELECT first_unit_start_date,
last_unit_completion_date
FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
WHERE a.wip_entity_id = b.wip_entity_id
AND b.workorder_id = c.workorder_id
AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
AND c.workorder_operation_id = c_workorder_operation_id;
SELECT
STANDARD_RATE_FLAG, resource_type
FROM
BOM_RESOURCES
WHERE
resource_id = p_resource_id;
SELECT
MIN(wipor.schedule_seq_num)
FROM
ahl_workorder_operations awop,
ahl_workorders awo,
wip_operation_resources wipor
WHERE
awop.operation_sequence_num = wipor.operation_seq_num
AND awo.wip_entity_id = wipor.wip_entity_id
AND awop.workorder_id = awo.workorder_id
AND awop.workorder_operation_id = c_wo_oper_id;
SELECT meaning
INTO l_std_rate_flag
FROM MFG_LOOKUPS
WHERE lookup_code = l_Resrc_Require_Rec.STD_RATE_FLAG_CODE
AND LOOKUP_TYPE = 'BOM_NO_YES';
IF l_Resrc_Require_Rec.last_update_login = FND_API.G_MISS_NUM
THEN
l_Resrc_Require_Rec.last_update_login := NULL;
l_Resrc_Require_Rec.last_update_login := l_Resrc_Require_Rec.last_update_login;
SELECT AHL_OPERATION_RESOURCES_S.NEXTVAL
INTO l_Resrc_Require_Rec.Operation_Resource_id
FROM DUAL;
Ahl_Debug_Pub.debug ( l_full_name || ' ******Before calling Insert_Row****');
Insert_Row (
X_OPERATION_RESOURCE_ID => l_Resrc_Require_Rec.Operation_Resource_id,
X_OBJECT_VERSION_NUMBER => 1,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id,
X_RESOURCE_ID => l_Resrc_Require_Rec.RESOURCE_ID,
X_WORKORDER_OPERATION_ID => l_Resrc_Require_Rec.workorder_operation_id,
X_RESOURCE_SEQ_NUMBER => l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER,
X_UOM_CODE => l_Resrc_Require_Rec.UOM_CODE,
X_QUANTITY => l_Resrc_Require_Rec.QUANTITY,
X_DURATION => l_Resrc_Require_Rec.DURATION,
X_SCHEDULED_START_DATE => NVL(l_Resrc_Require_Rec.REQ_START_DATE,l_Resrc_Require_Rec.OPER_START_DATE),
X_SCHEDULED_END_DATE => NVL(l_Resrc_Require_Rec.REQ_END_DATE,l_Resrc_Require_Rec.OPER_END_DATE),
X_ATTRIBUTE_CATEGORY => l_Resrc_Require_Rec.attribute_category,
X_ATTRIBUTE1 => l_Resrc_Require_Rec.attribute1,
X_ATTRIBUTE2 => l_Resrc_Require_Rec.attribute2,
X_ATTRIBUTE3 => l_Resrc_Require_Rec.attribute3,
X_ATTRIBUTE4 => l_Resrc_Require_Rec.attribute4,
X_ATTRIBUTE5 => l_Resrc_Require_Rec.attribute5,
X_ATTRIBUTE6 => l_Resrc_Require_Rec.attribute6,
X_ATTRIBUTE7 => l_Resrc_Require_Rec.attribute7,
X_ATTRIBUTE8 => l_Resrc_Require_Rec.attribute8,
X_ATTRIBUTE9 => l_Resrc_Require_Rec.attribute9,
X_ATTRIBUTE10 => l_Resrc_Require_Rec.attribute10,
X_ATTRIBUTE11 => l_Resrc_Require_Rec.attribute11,
X_ATTRIBUTE12 => l_Resrc_Require_Rec.attribute12,
X_ATTRIBUTE13 => l_Resrc_Require_Rec.attribute13,
X_ATTRIBUTE14 => l_Resrc_Require_Rec.attribute14,
X_ATTRIBUTE15 => l_Resrc_Require_Rec.attribute15,
-- SUKHWSIN ER # 9014609 -- start
X_RT_OPER_RESOURCE_ID => l_Resrc_Require_Rec.rt_oper_resource_id
-- SUKHWSIN ER # 9014609 -- end
);
Ahl_Debug_Pub.debug ( l_full_name || ' ******After calling Insert_Row****');
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL
INTO l_wo_operation_txn_id
FROM DUAL;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_load_type_code => 1,
p_transaction_type_code => 2,
p_workorder_operation_id => p_x_resrc_Require_tbl(i).workorder_operation_id,
p_bom_resource_id => p_x_resrc_Require_tbl(i).Resource_id,
p_operation_resource_id => p_x_resrc_Require_tbl(i).Operation_Resource_id,
p_res_sched_start_date => p_x_resrc_Require_tbl(i).REQ_START_DATE,
p_res_sched_end_date => p_x_resrc_Require_tbl(i).REQ_START_DATE
);
PROCEDURE Update_Resrc_Require (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
p_module_type IN VARCHAR2 := Null,
p_interface_flag IN VARCHAR2,
p_x_resrc_Require_Tbl IN OUT NOCOPY Resrc_Require_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- To find the WORKORDER_ID from AHL_WORKORDER_OPERATIONS_V view
CURSOR c_wo_oper (x_id IN NUMBER) IS
SELECT WORKORDER_ID,OPERATION_SEQUENCE_NUM FROM
AHL_WORKORDER_OPERATIONS_V
WHERE WORKORDER_OPERATION_ID = x_id;
SELECT WIP_ENTITY_ID,WORKORDER_NAME,
ORGANIZATION_ID FROM
AHL_WORKORDERS A, AHL_VISIT_TASKS_B B,
AHL_VISITS_B C
WHERE WORKORDER_ID = x_id
AND A.VISIT_TASK_ID = B.VISIT_TASK_ID
AND B.VISIT_ID = C.VISIT_ID;
SELECT RESOURCE_SEQUENCE_NUM FROM
AHL_OPERATION_RESOURCES
WHERE WORKORDER_OPERATION_ID = x_id;
SELECT * FROM AHL_OPERATION_RESOURCES
WHERE OPERATION_RESOURCE_ID = x_id;
SELECT
WO.visit_task_id,
WDJ.owning_department department_id
FROM
AHL_WORKORDERS WO,
WIP_DISCRETE_JOBS WDJ
WHERE
WO.workorder_id = x_id AND
WDJ.wip_entity_id = wo.wip_entity_id;
SELECT DEPARTMENT_ID FROM
BOM_DEPARTMENT_RESOURCES
WHERE RESOURCE_ID = x_id;
SELECT UOM_CODE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = x_name;
SELECT unit_of_measure
FROM bom_resources
WHERE resource_id = x_id;
SELECT first_unit_start_date,
last_unit_completion_date, a.department_id
FROM wip_operations a, ahl_workorders b
WHERE a.wip_entity_id = b.wip_entity_id
AND workorder_id = c_workorder_id
AND operation_seq_num = c_op_seq_num;*/
SELECT first_unit_start_date,
last_unit_completion_date
FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
WHERE a.wip_entity_id = b.wip_entity_id
AND b.workorder_id = c.workorder_id
AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
AND c.workorder_operation_id = c_workorder_operation_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Resrc_Require';
SAVEPOINT Update_Resrc_Require;
Ahl_Debug_Pub.debug( 'Enter ahl_pp_resrc_require_pvt. Update Resource Requirement +PPResrc_Require_Pvt+');
SELECT meaning
INTO l_std_rate_flag
FROM MFG_LOOKUPS
WHERE lookup_code = l_Resrc_Require_Rec.STD_RATE_FLAG_CODE
AND LOOKUP_TYPE = 'BOM_NO_YES';
IF l_Resrc_Require_Rec.last_update_login = FND_API.G_MISS_NUM
THEN
l_Resrc_Require_Rec.last_update_login := NULL;
l_Resrc_Require_Rec.last_update_login := l_Resrc_Require_Rec.last_update_login;
Update_Row (
X_OPERATION_RESOURCE_ID => l_Resrc_Require_Rec.OPERATION_RESOURCE_ID,
X_OBJECT_VERSION_NUMBER => l_Resrc_Require_Rec.OBJECT_VERSION_NUMBER,
X_RESOURCE_ID => l_Resrc_Require_Rec.RESOURCE_ID,
X_WORKORDER_OPERATION_ID=> l_Resrc_Require_Rec.WORKORDER_OPERATION_ID,
X_RESOURCE_SEQ_NUMBER => l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER,
X_UOM_CODE => l_Resrc_Require_Rec.UOM_CODE,
X_QUANTITY => l_Resrc_Require_Rec.QUANTITY,
X_DURATION => l_Resrc_Require_Rec.DURATION,
X_SCHEDULED_START_DATE => l_Resrc_Require_Rec.REQ_START_DATE,
X_SCHEDULED_END_DATE => l_Resrc_Require_Rec.REQ_END_DATE,
X_ATTRIBUTE_CATEGORY => l_Resrc_Require_Rec.attribute_category,
X_ATTRIBUTE1 => l_Resrc_Require_Rec.attribute1,
X_ATTRIBUTE2 => l_Resrc_Require_Rec.attribute2,
X_ATTRIBUTE3 => l_Resrc_Require_Rec.attribute3,
X_ATTRIBUTE4 => l_Resrc_Require_Rec.attribute4,
X_ATTRIBUTE5 => l_Resrc_Require_Rec.attribute5,
X_ATTRIBUTE6 => l_Resrc_Require_Rec.attribute6,
X_ATTRIBUTE7 => l_Resrc_Require_Rec.attribute7,
X_ATTRIBUTE8 => l_Resrc_Require_Rec.attribute8,
X_ATTRIBUTE9 => l_Resrc_Require_Rec.attribute9,
X_ATTRIBUTE10 => l_Resrc_Require_Rec.attribute10,
X_ATTRIBUTE11 => l_Resrc_Require_Rec.attribute11,
X_ATTRIBUTE12 => l_Resrc_Require_Rec.attribute12,
X_ATTRIBUTE13 => l_Resrc_Require_Rec.attribute13,
X_ATTRIBUTE14 => l_Resrc_Require_Rec.attribute14,
X_ATTRIBUTE15 => l_Resrc_Require_Rec.attribute15,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL
INTO l_wo_operation_txn_id
FROM DUAL;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_load_type_code => 1,
p_transaction_type_code => 3,
p_workorder_operation_id => p_x_resrc_Require_tbl(i).workorder_operation_id,
p_bom_resource_id => p_x_resrc_Require_tbl(i).Resource_id,
p_operation_resource_id => p_x_resrc_Require_tbl(i).Operation_Resource_id,
p_res_sched_start_date => p_x_resrc_Require_tbl(i).REQ_START_DATE,
p_res_sched_end_date => p_x_resrc_Require_tbl(i).REQ_START_DATE
);
Ahl_Debug_Pub.debug( 'End of Update Resource Reqst +PPResrc_Require_Pvt+');
ROLLBACK TO Update_Resrc_Require;
ROLLBACK TO Update_Resrc_Require;
ROLLBACK TO Update_Resrc_Require;
p_procedure_name => 'UPDATE_Resrc_Require',
p_error_text => SUBSTR(SQLERRM,1,240));
END Update_Resrc_Require;
SELECT * FROM AHL_OPERATION_RESOURCES
WHERE OPERATION_RESOURCE_ID = x_id;*/
SELECT AOR.workorder_operation_id,
AOR.resource_id,
AOR.OPERATION_RESOURCE_ID,
AOR.object_version_number,
AOR.DURATION,
AOR.QUANTITY,
AOR.RESOURCE_SEQUENCE_NUM,
WOR.START_DATE "SCHEDULED_START_DATE",
WOR.COMPLETION_DATE "SCHEDULED_END_DATE"
FROM AHL_OPERATION_RESOURCES AOR,
WIP_OPERATION_RESOURCES WOR,
AHL_WORKORDER_OPERATIONS AWO ,
AHL_WORKORDERS AWJ
WHERE AOR.RESOURCE_ID = WOR.RESOURCE_ID
AND WOR.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
AND AOR.WORKORDER_OPERATION_ID = AWO.WORKORDER_OPERATION_ID
AND AWO.OPERATION_SEQUENCE_NUM = WOR.OPERATION_SEQ_NUM
AND AWJ.WORKORDER_ID = AWO.WORKORDER_ID
AND AWJ.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND AOR.OPERATION_RESOURCE_ID = x_id;
SELECT ML.MEANING, BR.RESOURCE_TYPE, BR.RESOURCE_CODE
FROM BOM_RESOURCES BR, MFG_LOOKUPS ML, AHL_OPERATION_RESOURCES AOR
WHERE BR.RESOURCE_TYPE = ML.LOOKUP_CODE
AND ML.LOOKUP_TYPE= 'BOM_RESOURCE_TYPE'
AND AOR.RESOURCE_ID = BR.RESOURCE_ID
AND AOR.OPERATION_RESOURCE_ID = x_id;
SELECT WORV.* FROM
AHL_OPERATION_RESOURCES AOR,
AHL_WORKORDER_OPERATIONS AWO,
AHL_WORKORDERS AW,
WIP_OPERATION_RESOURCES_V WORV
WHERE WORV.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
AND WORV.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
AND WORV.WIP_ENTITY_ID = AW.WIP_ENTITY_ID
AND AW.WORKORDER_ID = AWO.WORKORDER_ID
AND AWO.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID
AND AOR.OPERATION_RESOURCE_ID = x_id;
SELECT OPERATION_SEQUENCE_NUM, SCHEDULED_START_DATE, SCHEDULED_END_DATE
FROM AHL_WORKORDER_OPERATIONS_V
WHERE WORKORDER_OPERATION_ID = x_id;
SELECT *
FROM ahl_pp_requirement_v
WHERE REQUIREMENT_ID = x_id;
SELECT
BOM.UNIT_OF_MEASURE uom_code,
MUOM.UNIT_OF_MEASURE UOM_NAME,
AWO.workorder_id job_id
FROM
BOM_RESOURCES BOM,
MTL_UNITS_OF_MEASURE MUOM,
AHL_OPERATION_RESOURCES AOR,
AHL_WORKORDER_OPERATIONS AWO
WHERE
AOR.OPERATION_RESOURCE_ID = x_id AND
AOR.RESOURCE_ID = BOM.RESOURCE_ID AND
BOM.UNIT_OF_MEASURE = MUOM.UOM_CODE AND
AOR.WORKORDER_OPERATION_ID = AWO.WORKORDER_OPERATION_ID;
SELECT meaning
FROM MFG_LOOKUPS
WHERE lookup_type = x_lookup_type
AND lookup_code = x_lookup_code
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
AND TRUNC(NVL(end_date_active,SYSDATE));
SELECT meaning
INTO l_std_rate_flag
FROM MFG_LOOKUPS
WHERE lookup_code = c_WIP_oper_rec.STANDARD_RATE_FLAG
AND LOOKUP_TYPE = 'BOM_NO_YES';
SELECT * FROM AHL_OPERATION_RESOURCES
WHERE operation_resource_id = c_op_resource_id;
SELECT
wo.workorder_name,
wdj.organization_id,
wo.wip_entity_id
FROM
ahl_workorders wo,
wip_discrete_jobs wdj
WHERE
wo.workorder_id = c_workorder_id AND
wdj.wip_entity_id = wo.wip_entity_id;
SELECT * FROM ahl_workorder_operations_v
WHERE workorder_operation_id = c_wo_operation_id;
SELECT count(*) FROM AHL_WORK_ASSIGNMENTS
WHERE OPERATION_RESOURCE_ID = oper_resrc_id;
SELECT unit_of_measure
FROM bom_resources
WHERE resource_id = x_id;
SELECT * FROM WIP_OPERATION_RESOURCES
WHERE WIP_ENTITY_ID = c_wip_entity_id
AND OPERATION_SEQ_NUM = c_oper_seq
AND RESOURCE_SEQ_NUM = c_res_seq_num;
SELECT 'x' FROM DUAL
WHERE EXISTS ( SELECT 'x'
FROM WIP_TRANSACTIONS
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = p_op_seq
AND resource_seq_num = p_res_seq )
OR EXISTS (SELECT 'x'
FROM WIP_COST_TXN_INTERFACE
WHERE wip_entity_id = p_wip_entity_id
AND organization_id = p_organization_id
AND operation_seq_num = p_op_seq
AND resource_seq_num = p_res_seq);
Ahl_Debug_Pub.debug ('BEFORE DELETE RESOURCES' || p_x_resrc_Require_tbl(i).Operation_Resource_Id);
DELETE FROM AHL_OPERATION_RESOURCES
WHERE OPERATION_RESOURCE_ID = p_x_resrc_Require_tbl(i).operation_resource_id;
SELECT
WDJ.firm_planned_flag
FROM
WIP_DISCRETE_JOBS WDJ,
AHL_WORKORDERS AWO
WHERE
AWO.wip_entity_id = WDJ.wip_entity_id AND
AWO.workorder_id = c_workorder_id;
Ahl_Debug_Pub.debug( 'after update'||p_operation_flag);
Update_Resrc_Require (
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_interface_flag => Null,
p_x_resrc_Require_tbl => p_x_resrc_Require_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
AHL_PRD_WORKORDER_PVT.Update_Master_Wo_Dates(p_x_resrc_Require_tbl(l_res_count).workorder_id);
AHL_PRD_WORKORDER_PVT.update_job
(
p_api_version => 1.0 ,
p_init_msg_list => FND_API.G_FALSE ,
p_commit => FND_API.G_FALSE ,
p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
p_default => FND_API.G_TRUE ,
p_module_type => NULL ,
x_return_status => l_return_status ,
x_msg_count => l_msg_count ,
x_msg_data => l_msg_data ,
p_wip_load_flag => 'Y' ,
p_x_prd_workorder_rec => l_up_workorder_rec ,
p_x_prd_workoper_tbl => l_up_workoper_tbl
);