[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 SYSDATE BETWEEN start_date_active
AND 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 SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE);
/*SELECT DISTINCT(instance_id)
INTO x_serial_id
FROM BOM_DEPT_RES_INSTANCES BDRI, AHL_OPERATION_RESOURCES AOR, AHL_WORKORDERS_V AWO
WHERE BDRI.resource_id = AOR.resource_id
AND BDRI.department_id = AWO.department_id
AND AWO.workorder_id = p_workorder_id
AND AOR.operation_resource_id = p_oper_resrc_id
AND BDRI.serial_number = p_serial_number;*/
SELECT INSTANCE_ID INTO x_instance_id
FROM BOM_DEPT_RES_INSTANCES BDRI
WHERE BDRI.resource_id = p_resource_id
AND BDRI.serial_number = p_serial_number
AND BDRI.department_id in (
SELECT
nvl(bdr.SHARE_FROM_DEPT_ID,
bdr.department_id)
FROM
bom_departments bd,
bom_department_resources bdr
WHERE
bdr.resource_id = p_resource_id and
bdr.department_id = bd.department_id and
bd.organization_id = p_organization_id
);
/*SELECT DISTINCT(PPF.PERSON_ID)
INTO x_employee_id
FROM PER_PEOPLE_F PPF, BOM_RESOURCE_EMPLOYEES BRE, AHL_OPERATION_RESOURCES AOR,
PER_PERSON_TYPES PEPT, AHL_WORKORDERS AWV, AHL_VISITS_B VTB
WHERE PPF.PERSON_ID = BRE.PERSON_ID AND BRE.RESOURCE_ID = AOR.RESOURCE_ID
AND AWV.VISIT_ID = VTB.VISIT_ID
AND BRE.ORGANIZATION_ID = VTB.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND NVL(PPF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y' AND PEPT.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
AND PEPT.SYSTEM_PERSON_TYPE ='EMP' AND AOR.operation_resource_id = p_oper_resrc_id
AND PPF.EMPLOYEE_NUMBER = p_employee_number AND AWV.WORKORDER_ID = p_workorder_id;*/
SELECT DISTINCT(PF.employee_id)
INTO x_employee_id
FROM
bom_dept_res_instances bdri,
wip_operation_resources wor,
wip_operations wo
,ahl_workorders awo, ahl_operation_resources aor
, mtl_employees_current_view pf
,bom_resource_employees bre
where awo.wip_entity_id = wor.wip_entity_id
and awo.workorder_id = p_workorder_id
and aor.operation_resource_id = p_oper_resrc_id
and wor.resource_seq_num = aor.resource_sequence_num
and wor.resource_id = aor.resource_id
and bdri.department_id in (
SELECT
nvl(bdr.SHARE_FROM_DEPT_ID,
bdr.department_id)
FROM
bom_departments bd,
bom_department_resources bdr,
ahl_pp_requirement_v aprv
WHERE
bdr.resource_id = p_resource_id and
bdr.department_id = bd.department_id and
bd.organization_id = p_organization_id
)
and bdri.resource_id = wor.resource_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and pf.employee_id = bre.person_id
and pf.organization_id = bre.organization_id
and bre.instance_id = bdri.instance_id
and pf.employee_num = p_employee_number;
/*SELECT PPF.PERSON_ID, PPF.FULL_NAME
INTO x_employee_id, x_employee_name
FROM PER_PEOPLE_F PPF,
BOM_RESOURCE_EMPLOYEES BRE,
AHL_OPERATION_RESOURCES AOR,
PER_PERSON_TYPES PEPT,
AHL_WORKORDERS AWV,
AHL_VISITS_B VTB
WHERE PPF.PERSON_ID = BRE.PERSON_ID
AND BRE.resource_id = AOR.resource_id
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
AND AWV.VISIT_ID = VTB.VISIT_ID
AND BRE.ORGANIZATION_ID = VTB.ORGANIZATION_ID
AND NVL(PPF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
AND PEPT.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
AND PEPT.SYSTEM_PERSON_TYPE ='EMP'
AND AOR.operation_resource_id = p_oper_resrc_id
AND PPF.EMPLOYEE_NUMBER = p_employee_number
AND AWV.WORKORDER_ID = p_workorder_id;*/
select distinct pf.employee_id, pf.full_name
INTO x_employee_id, x_employee_name
from
bom_dept_res_instances bdri,
wip_operation_resources wor,
wip_operations wo
,ahl_workorders awo, ahl_operation_resources aor
, mtl_employees_current_view pf
,bom_resource_employees bre
where awo.wip_entity_id = wor.wip_entity_id
and awo.workorder_id = p_workorder_id
and aor.operation_resource_id = p_oper_resrc_id
and wor.resource_seq_num = aor.resource_sequence_num
and wor.resource_id = aor.resource_id
and bdri.department_id in (
SELECT
nvl(bdr.SHARE_FROM_DEPT_ID,
bdr.department_id)
FROM
bom_departments bd,
bom_department_resources bdr
WHERE
bdr.resource_id = p_resource_id and
bdr.department_id = bd.department_id and
bd.organization_id = p_organization_id
)
and bdri.resource_id = wor.resource_id
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and pf.employee_id = bre.person_id
and pf.organization_id = bre.organization_id
and bre.instance_id = bdri.instance_id
and pf.employee_num = p_employee_number;
PROCEDURE Insert_Row (
X_ASSIGNMENT_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_OPERATION_RESOURCE_ID IN NUMBER,
X_EMPLOYEE_ID IN NUMBER,
X_SERIAL_NUMBER IN VARCHAR2,
X_INSTANCE_ID IN NUMBER,
X_ASSIGN_START_DATE IN DATE,
X_ASSIGN_END_DATE IN DATE,
X_SELF_ASSIGNED_FLAG IN VARCHAR2,
--X_LOGIN_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
)
IS
BEGIN
INSERT INTO AHL_WORK_ASSIGNMENTS (
ASSIGNMENT_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
OPERATION_RESOURCE_ID,
EMPLOYEE_ID,
SERIAL_NUMBER,
INSTANCE_ID,
ASSIGN_START_DATE,
ASSIGN_END_DATE,
--LOGIN_DATE,
SELF_ASSIGNED_FLAG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES(
X_ASSIGNMENT_ID,
X_OBJECT_VERSION_NUMBER,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_OPERATION_RESOURCE_ID,
X_EMPLOYEE_ID,
X_SERIAL_NUMBER,
X_INSTANCE_ID,
X_ASSIGN_START_DATE,
X_ASSIGN_END_DATE,
X_SELF_ASSIGNED_FLAG,
--X_LOGIN_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);
END Insert_Row;
PROCEDURE UPDATE_ROW (
X_ASSIGNMENT_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_OPERATION_RESOURCE_ID IN NUMBER,
X_EMPLOYEE_ID IN NUMBER,
X_SERIAL_NUMBER IN VARCHAR2,
X_INSTANCE_ID IN NUMBER,
X_ASSIGN_START_DATE IN DATE,
X_ASSIGN_END_DATE IN DATE,
X_SELF_ASSIGNED_FLAG IN VARCHAR2,
--X_LOGIN_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_WORK_ASSIGNMENTS SET
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
ASSIGNMENT_ID = X_ASSIGNMENT_ID,
OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID,
EMPLOYEE_ID = X_EMPLOYEE_ID,
SERIAL_NUMBER = X_SERIAL_NUMBER,
INSTANCE_ID = X_INSTANCE_ID,
ASSIGN_START_DATE = X_ASSIGN_START_DATE,
ASSIGN_END_DATE = X_ASSIGN_END_DATE,
SELF_ASSIGNED_FLAG = X_SELF_ASSIGNED_FLAG,
--LOGIN_DATE = X_LOGIN_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 ASSIGNMENT_ID = X_ASSIGNMENT_ID
AND OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER;
AHL_DEBUG_PUB.debug(' Inside Update Row procedure');
END UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_ASSIGNMENT_ID IN NUMBER
) IS
BEGIN
DELETE FROM AHL_WORK_ASSIGNMENTS
WHERE ASSIGNMENT_ID = X_ASSIGNMENT_ID;
END DELETE_ROW;
SELECT EFFECTIVE_START_DATE, EFFECTIVE_END_DATE FROM
BOM_RESOURCE_EMPLOYEES
WHERE PERSON_ID = employee_id;
SELECT TO_DATE(SCHEDULED_START_DATE,'DD-MM-YYYY'), TO_DATE(SCHEDULED_END_DATE,'DD-MM-YYYY'),
TO_DATE(ACTUAL_START_DATE,'DD-MM-YYYY'), TO_DATE(ACTUAL_END_DATE,'DD-MM-YYYY')
FROM AHL_WORKORDERS_V
WHERE workorder_id = job_id; */
SELECT TRUNC(SCHEDULED_START_DATE), TRUNC(SCHEDULED_END_DATE),
TRUNC(ACTUAL_START_DATE), TRUNC(ACTUAL_END_DATE)
FROM AHL_WORKORDERS_V
WHERE workorder_id = job_id;
SELECT TRUNC(WIP.SCHEDULED_START_DATE),
TRUNC(WIP.SCHEDULED_COMPLETION_DATE) SCHEDULED_END_DATE,
TRUNC(WO.ACTUAL_START_DATE), TRUNC(WO.ACTUAL_END_DATE)
FROM AHL_WORKORDERS WO, WIP_DISCRETE_JOBS WIP
WHERE WO.wip_entity_id = WIP.wip_entity_id
AND WO.workorder_id = job_id;
SELECT 1 FROM dual
WHERE EXISTS (SELECT 1
FROM AHL_WORK_ASSIGNMENTS
WHERE ASSIGNMENT_ID = x_id);
SELECT requirement_id, operation_id, resource_id FROM AHL_PP_REQUIREMENT_V
WHERE job_id = x_id
AND resource_sequence = x_resrc
AND operation_sequence = x_oper;
SELECT OPR.operation_resource_id requirement_id ,
WOP.workorder_operation_id operation_id ,
OPR.resource_id
FROM ahl_operation_resources OPR,
ahl_workorder_operations WOP
WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
AND WOP.operation_sequence_num = x_oper
AND OPR.resource_sequence_num = x_resrc
AND WOP.workorder_id = x_id;
SELECT RESOURCE_TYPE_CODE FROM
AHL_PP_REQUIREMENT_V
WHERE OPERATION_SEQUENCE = x_oper AND RESOURCE_SEQUENCE = x_res
AND JOB_ID = x_id;
SELECT resource_type resource_type_code
FROM ahl_operation_resources OPR,
ahl_workorder_operations WOP,
bom_resources BOM
WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
AND OPR.resource_id = BOM.resource_id
AND WOP.operation_sequence_num = x_oper
AND OPR.resource_sequence_num = x_res
AND WOP.workorder_id = x_id;
SELECT resource_id FROM AHL_PP_REQUIREMENT_V
WHERE job_id = x_id
AND resource_sequence = x_resrc
AND operation_sequence = x_oper;
SELECT OPR.resource_id
FROM ahl_workorder_operations WOP,
ahl_operation_resources OPR
WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
AND WOP.operation_sequence_num = x_oper
AND OPR.resource_sequence_num = x_resrc
AND WOP.workorder_id = x_id;
SELECT wip_entity_id, organization_id,
department_id FROM AHL_Workorders_V
WHERE workorder_id = x_id;
SELECT a.wip_entity_id, wo.organization_id,
wo.department_id
FROM AHL_Workorders a, wip_operations wo
WHERE a.wip_entity_id = wo.wip_entity_id
AND wo.operation_seq_num = x_operation_seq_num
AND a.workorder_id = x_id;
SELECT wip_entity_id, organization_id,
department_id
FROM AHL_Workorders a, ahl_visits_b b
WHERE a.visit_id = b.visit_id
AND workorder_id = x_id;
SELECT a.instance_id
FROM BOM_DEPT_RES_INSTANCES A, BOM_RESOURCE_EMPLOYEES B
WHERE A.INSTANCE_ID = B.INSTANCE_ID
AND B.PERSON_ID = c_person_id
AND A.RESOURCE_ID = c_resource_id
AND A.DEPARTMENT_ID in (
SELECT
nvl(bdr.SHARE_FROM_DEPT_ID,
bdr.department_id)
FROM
bom_department_resources bdr
WHERE
bdr.resource_id = c_resource_id and
bdr.department_id = c_dept_id
);
SELECT scheduled_start_date, scheduled_end_date
FROM ahl_workorder_operations WOP,
ahl_operation_resources OPR
WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
AND WOP.operation_sequence_num = x_oper
AND OPR.resource_sequence_num = x_resrc
AND WOP.workorder_id = x_id;
SELECT
TO_CHAR(WOP.FIRST_UNIT_START_DATE, 'ss'),
TO_CHAR(WOP.LAST_UNIT_COMPLETION_DATE, 'ss')
FROM
wip_operations WOP,
ahl_workorders AWO
WHERE
WOP.OPERATION_SEQ_NUM = p_op_seq
AND WOP.wip_entity_id = AWO.wip_entity_id
AND AWO.workorder_id = p_wo_id;
IF p_x_resrc_assign_tbl(i).last_update_login = FND_API.G_MISS_NUM
THEN
l_resrc_assign_tbl(i).last_update_login := NULL;
l_resrc_assign_tbl(i).last_update_login := p_x_resrc_assign_tbl(i).last_update_login;
SELECT AHL_WORK_ASSIGNMENTS_S.NEXTVAL
INTO l_assignment_id FROM DUAL;
Insert_Row (
X_ASSIGNMENT_ID => l_assignment_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_OPERATION_RESOURCE_ID => p_x_resrc_assign_tbl(i).oper_resource_id,
X_EMPLOYEE_ID => l_Resrc_Assign_tbl(i).employee_id,
X_SERIAL_NUMBER => l_Resrc_Assign_tbl(i).serial_number,
X_INSTANCE_ID => l_Resrc_Assign_tbl(i).instance_id,
X_ASSIGN_START_DATE => p_x_resrc_assign_tbl(i).assign_start_date,
X_ASSIGN_END_DATE => p_x_resrc_assign_tbl(i).assign_end_date,
X_SELF_ASSIGNED_FLAG => p_x_resrc_assign_tbl(i).self_assigned_flag,
-- X_LOGIN_DATE => p_x_resrc_assign_tbl(i).login_date,
X_ATTRIBUTE_CATEGORY => l_Resrc_Assign_tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Resrc_Assign_tbl(i).attribute1,
X_ATTRIBUTE2 => l_Resrc_Assign_tbl(i).attribute2,
X_ATTRIBUTE3 => l_Resrc_Assign_tbl(i).attribute3,
X_ATTRIBUTE4 => l_Resrc_Assign_tbl(i).attribute4,
X_ATTRIBUTE5 => l_Resrc_Assign_tbl(i).attribute5,
X_ATTRIBUTE6 => l_Resrc_Assign_tbl(i).attribute6,
X_ATTRIBUTE7 => l_Resrc_Assign_tbl(i).attribute7,
X_ATTRIBUTE8 => l_Resrc_Assign_tbl(i).attribute8,
X_ATTRIBUTE9 => l_Resrc_Assign_tbl(i).attribute9,
X_ATTRIBUTE10 => l_Resrc_Assign_tbl(i).attribute10,
X_ATTRIBUTE11 => l_Resrc_Assign_tbl(i).attribute11,
X_ATTRIBUTE12 => l_Resrc_Assign_tbl(i).attribute12,
X_ATTRIBUTE13 => l_Resrc_Assign_tbl(i).attribute13,
X_ATTRIBUTE14 => l_Resrc_Assign_tbl(i).attribute14,
X_ATTRIBUTE15 => l_Resrc_Assign_tbl(i).attribute15
);
PROCEDURE Update_Resrc_Assign (
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_x_resrc_assign_tbl IN OUT NOCOPY Resrc_Assign_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- To find all information from AHL_OPERATION_RESOURCES view
CURSOR c_assign (x_id IN NUMBER) IS
SELECT * FROM AHL_WORK_ASSIGNMENTS
WHERE ASSIGNMENT_ID = x_id;
SELECT * FROM AHL_PP_ASSIGNMENT_V
WHERE ASSIGNMENT_ID = x_id;
SELECT WOA.operation_resource_id requirement_id,
WOP.workorder_id job_id,
BOM.resource_type resource_type_code,
OPR.resource_id
FROM ahl_operation_resources OPR,
ahl_work_assignments WOA,
ahl_workorder_operations WOP,
bom_resources BOM
WHERE OPR.operation_resource_id = WOA.operation_resource_id
AND OPR.workorder_operation_id = WOP.workorder_operation_id
AND OPR.resource_id = BOM.resource_id
AND WOA.assignment_id = x_id;
SELECT resource_id FROM AHL_PP_REQUIREMENT_V
WHERE job_id = x_id
AND resource_sequence = x_resrc
AND operation_sequence = x_oper;
SELECT OPR.resource_id
FROM ahl_workorder_operations WOP,
ahl_operation_resources OPR
WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
AND WOP.operation_sequence_num = x_oper
AND OPR.resource_sequence_num = x_resrc
AND WOP.workorder_id = x_id;
SELECT wip_entity_id,organization_id,
department_id FROM AHL_Workorders_V
WHERE workorder_id = x_id;
SELECT a.wip_entity_id, wo.organization_id,
wo.department_id
FROM AHL_Workorders a, wip_operations wo
WHERE a.wip_entity_id = wo.wip_entity_id
AND wo.operation_seq_num = x_operation_seq_num
AND a.workorder_id = x_id;
SELECT wip_entity_id,organization_id,
department_id
FROM AHL_Workorders a, ahl_visits_b b
WHERE a.visit_id = b.visit_id
AND workorder_id = x_id;
SELECT a.instance_id
FROM BOM_DEPT_RES_INSTANCES A, BOM_RESOURCE_EMPLOYEES B
WHERE A.INSTANCE_ID = B.INSTANCE_ID
AND B.PERSON_ID = c_person_id
AND A.RESOURCE_ID = c_resource_id
AND A.DEPARTMENT_ID in (
SELECT
nvl(bdr.SHARE_FROM_DEPT_ID,
bdr.department_id)
FROM
bom_department_resources bdr
WHERE
bdr.resource_id = c_resource_id and
bdr.department_id = c_dept_id
);
SELECT scheduled_start_date, scheduled_end_date
FROM ahl_workorder_operations WOP,
ahl_operation_resources OPR
WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
AND WOP.operation_sequence_num = x_oper
AND OPR.resource_sequence_num = x_resrc
AND WOP.workorder_id = x_id;
SELECT
TO_CHAR(WOP.FIRST_UNIT_START_DATE, 'ss'),
TO_CHAR(WOP.LAST_UNIT_COMPLETION_DATE, 'ss')
FROM
wip_operations WOP,
ahl_workorders AWO
WHERE
WOP.OPERATION_SEQ_NUM = p_op_seq
AND WOP.wip_entity_id = AWO.wip_entity_id
AND AWO.workorder_id = p_wo_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Resrc_Assign';
SAVEPOINT Update_Resrc_Assign;
AHL_DEBUG_PUB.debug( 'Enter ahl_pp_assign_pvt. Update Resource reqst','+PPResrc_Assign_Pvt+');
AHL_DEBUG_PUB.debug(l_full_name || ' Before calling Update Row procedure');
Update_Row (
X_ASSIGNMENT_ID => p_x_resrc_assign_tbl(i).Assignment_id,
X_OBJECT_VERSION_NUMBER => p_x_resrc_assign_tbl(i).object_version_number,
X_OPERATION_RESOURCE_ID => c_assign_rec.Operation_resource_id,
X_EMPLOYEE_ID => p_x_resrc_assign_tbl(i).employee_id,
X_SERIAL_NUMBER => p_x_resrc_assign_tbl(i).serial_number,
X_INSTANCE_ID => p_x_resrc_assign_tbl(i).instance_id,
X_ASSIGN_START_DATE => p_x_resrc_assign_tbl(i).assign_start_date,
X_ASSIGN_END_DATE => p_x_resrc_assign_tbl(i).assign_end_date,
X_SELF_ASSIGNED_FLAG => p_x_resrc_assign_tbl(i).self_assigned_flag,
--X_LOGIN_DATE => p_x_resrc_assign_tbl(i).login_date,
X_ATTRIBUTE_CATEGORY => p_x_resrc_assign_tbl(i).attribute_category,
X_ATTRIBUTE1 => p_x_resrc_assign_tbl(i).attribute1,
X_ATTRIBUTE2 => p_x_resrc_assign_tbl(i).attribute2,
X_ATTRIBUTE3 => p_x_resrc_assign_tbl(i).attribute3,
X_ATTRIBUTE4 => p_x_resrc_assign_tbl(i).attribute4,
X_ATTRIBUTE5 => p_x_resrc_assign_tbl(i).attribute5,
X_ATTRIBUTE6 => p_x_resrc_assign_tbl(i).attribute6,
X_ATTRIBUTE7 => p_x_resrc_assign_tbl(i).attribute7,
X_ATTRIBUTE8 => p_x_resrc_assign_tbl(i).attribute8,
X_ATTRIBUTE9 => p_x_resrc_assign_tbl(i).attribute9,
X_ATTRIBUTE10 => p_x_resrc_assign_tbl(i).attribute10,
X_ATTRIBUTE11 => p_x_resrc_assign_tbl(i).attribute11,
X_ATTRIBUTE12 => p_x_resrc_assign_tbl(i).attribute12,
X_ATTRIBUTE13 => p_x_resrc_assign_tbl(i).attribute13,
X_ATTRIBUTE14 => p_x_resrc_assign_tbl(i).attribute14,
X_ATTRIBUTE15 => p_x_resrc_assign_tbl(i).attribute15,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
AHL_DEBUG_PUB.debug(l_full_name || ' After calling Update Row procedure');
Ahl_Debug_Pub.debug( 'End of public api Update Resource Reqst','+PPResrc_Assign_Pvt+');
ROLLBACK TO update_Resrc_Assign;
ROLLBACK TO update_Resrc_Assign;
ROLLBACK TO update_Resrc_Assign;
p_procedure_name => 'UPDATE_Resrc_Assign',
p_error_text => SUBSTR(SQLERRM,1,240));
END Update_Resrc_Assign;
SELECT * FROM AHL_WORK_ASSIGNMENTS
WHERE assignment_id = c_assign_id;
SELECT operation_resource_id,resource_sequence_num,
operation_sequence_num,resource_id,b.workorder_operation_id,
c.workorder_id,wip_entity_id,organization_id,department_id
FROM ahl_operation_resources a, ahl_workorder_operations b, ahl_workorders_v c
WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
AND b.workorder_id = c.workorder_id
AND a.operation_resource_id = c_oper_resource_id;
SELECT operation_resource_id,resource_sequence_num,
operation_sequence_num,resource_id,b.workorder_operation_id,
c.workorder_id,c.wip_entity_id, d.organization_id,department_id
FROM ahl_operation_resources a, ahl_workorder_operations b,
ahl_workorders c , wip_discrete_jobs d, wip_operations e
WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
AND b.workorder_id = c.workorder_id
AND c.wip_entity_id = d.wip_entity_id
AND c.wip_entity_id = e.wip_entity_id
AND b.operation_sequence_num = e.operation_seq_num
AND a.operation_resource_id = c_oper_resource_id;
DELETE FROM AHL_WORK_ASSIGNMENTS
WHERE ASSIGNMENT_ID = p_x_resrc_assign_tbl(i).assignment_id;
AHL_DEBUG_PUB.debug( 'after update'||p_operation_flag);
Update_Resrc_Assign (
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_x_resrc_assign_tbl => p_x_resrc_assign_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);