[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
INTO x_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = p_organization_id;
SELECT organization_id
INTO x_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME = p_org_name;
SELECT department_id
INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND department_id = p_department_id;
SELECT department_id
INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND description = p_dept_description;
SELECT visit_task_id INTO x_visit_task_id
FROM AHL_WORKORDERS
WHERE workorder_id = p_workorder_id;
SELECT workorder_id INTO x_workorder_id
FROM AHL_WORKORDERS
WHERE workorder_id = p_workorder_id;
SELECT workorder_id INTO x_workorder_id
FROM AHL_WORKORDERS
WHERE workorder_name = p_job_number;
SELECT workorder_operation_id INTO x_workorder_operation_id
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_id = p_workorder_id
AND operation_sequence_num = p_operation_sequence;
SELECT inventory_item_id INTO x_inventory_item_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT inventory_item_id INTO x_inventory_item_id
FROM MTL_SYSTEM_ITEMS_KFV
WHERE concatenated_segments = p_concatenated_segments
AND organization_id = p_organization_id;
SELECT visit_id,
department_id,project_task_id
FROM ahl_visit_tasks_b
WHERE visit_task_id = c_visit_task_id;
SELECT organization_id,department_id,
project_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
PROCEDURE Insert_Row (
X_SCHEDULED_MATERIAL_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_INVENTORY_ITEM_ID IN VARCHAR2,
X_SCHEDULE_DESIGNATOR IN VARCHAR2,
X_VISIT_ID IN NUMBER,
X_VISIT_START_DATE IN DATE,
X_VISIT_TASK_ID IN NUMBER,
X_ORGANIZATION_ID IN NUMBER,
X_SCHEDULED_DATE IN DATE,
X_REQUEST_ID IN NUMBER,
X_REQUESTED_DATE IN DATE,
X_SCHEDULED_QUANTITY IN NUMBER,
X_PROCESS_STATUS IN NUMBER,
X_ERROR_MESSAGE IN VARCHAR2,
X_TRANSACTION_ID IN NUMBER,
X_UOM IN VARCHAR2,
X_RT_OPER_MATERIAL_ID IN NUMBER,
X_OPERATION_CODE IN VARCHAR2,
X_OPERATION_SEQUENCE IN NUMBER,
X_ITEM_GROUP_ID IN NUMBER,
X_REQUESTED_QUANTITY IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_LAST_UPDATED_DATE IN DATE,
X_WORKORDER_OPERATION_ID IN NUMBER,
X_MATERIAL_REQUEST_TYPE IN VARCHAR2,
X_STATUS IN VARCHAR2,
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_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
) IS
BEGIN
INSERT INTO AHL_SCHEDULE_MATERIALS (
SCHEDULED_MATERIAL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SCHEDULE_DESIGNATOR,
VISIT_ID,
VISIT_START_DATE,
VISIT_TASK_ID,
ORGANIZATION_ID,
SCHEDULED_DATE,
REQUEST_ID,
REQUESTED_DATE,
SCHEDULED_QUANTITY,
PROCESS_STATUS,
ERROR_MESSAGE,
TRANSACTION_ID,
UOM,
RT_OPER_MATERIAL_ID,
OPERATION_CODE,
OPERATION_SEQUENCE,
ITEM_GROUP_ID,
REQUESTED_QUANTITY,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATED_DATE,
WORKORDER_OPERATION_ID,
MATERIAL_REQUEST_TYPE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES(
X_SCHEDULED_MATERIAL_ID,
X_OBJECT_VERSION_NUMBER,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_INVENTORY_ITEM_ID,
X_SCHEDULE_DESIGNATOR,
X_VISIT_ID,
X_VISIT_START_DATE,
X_VISIT_TASK_ID,
X_ORGANIZATION_ID,
X_SCHEDULED_DATE,
X_REQUEST_ID,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
trunc(X_REQUESTED_DATE),
X_SCHEDULED_QUANTITY,
X_PROCESS_STATUS,
X_ERROR_MESSAGE,
X_TRANSACTION_ID,
X_UOM,
X_RT_OPER_MATERIAL_ID,
X_OPERATION_CODE,
X_OPERATION_SEQUENCE,
X_ITEM_GROUP_ID,
X_REQUESTED_QUANTITY,
X_PROGRAM_ID,
X_PROGRAM_UPDATE_DATE,
X_LAST_UPDATED_DATE,
X_WORKORDER_OPERATION_ID,
X_MATERIAL_REQUEST_TYPE,
X_STATUS,
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_SCHEDULED_MATERIAL_ID IN NUMBER,
X_OBJECT_VERSION_NUMBER IN NUMBER,
X_INVENTORY_ITEM_ID IN VARCHAR2,
X_SCHEDULE_DESIGNATOR IN VARCHAR2,
X_VISIT_ID IN NUMBER,
X_VISIT_START_DATE IN DATE,
X_VISIT_TASK_ID IN NUMBER,
X_ORGANIZATION_ID IN NUMBER,
X_SCHEDULED_DATE IN DATE,
X_REQUEST_ID IN NUMBER,
X_REQUESTED_DATE IN DATE,
X_SCHEDULED_QUANTITY IN NUMBER,
X_PROCESS_STATUS IN NUMBER,
X_ERROR_MESSAGE IN VARCHAR2,
X_TRANSACTION_ID IN NUMBER,
X_UOM IN VARCHAR2,
X_RT_OPER_MATERIAL_ID IN NUMBER,
X_OPERATION_CODE IN VARCHAR2,
X_OPERATION_SEQUENCE IN NUMBER,
X_ITEM_GROUP_ID IN NUMBER,
X_REQUESTED_QUANTITY IN NUMBER,
X_PROGRAM_ID IN NUMBER,
X_PROGRAM_UPDATE_DATE IN DATE,
X_LAST_UPDATED_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_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER
) IS
BEGIN
UPDATE AHL_SCHEDULE_MATERIALS SET
OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
VISIT_ID = X_VISIT_ID,
VISIT_START_DATE = X_VISIT_START_DATE,
VISIT_TASK_ID = X_VISIT_TASK_ID,
ORGANIZATION_ID = X_ORGANIZATION_ID,
SCHEDULED_DATE = X_SCHEDULED_DATE,
REQUEST_ID = X_REQUEST_ID,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
REQUESTED_DATE = trunc(X_REQUESTED_DATE),
SCHEDULED_QUANTITY = X_SCHEDULED_QUANTITY,
PROCESS_STATUS = X_PROCESS_STATUS,
ERROR_MESSAGE = X_ERROR_MESSAGE,
TRANSACTION_ID = X_TRANSACTION_ID,
UOM = X_UOM,
RT_OPER_MATERIAL_ID = X_RT_OPER_MATERIAL_ID,
OPERATION_CODE = X_OPERATION_CODE,
OPERATION_SEQUENCE = X_OPERATION_SEQUENCE,
ITEM_GROUP_ID = X_ITEM_GROUP_ID,
REQUESTED_QUANTITY = X_REQUESTED_QUANTITY,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
LAST_UPDATED_DATE = X_LAST_UPDATED_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 SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID
AND OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NUMBER;
END UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_SCHEDULED_MATERIAL_ID IN NUMBER
) IS
BEGIN
DELETE FROM AHL_SCHEDULE_MATERIALS
WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID;
END DELETE_ROW;
SELECT 1
FROM AHL_SCHEDULE_MATERIALS
WHERE inventory_item_id = c_item_id
AND workorder_operation_id = c_operation_id
AND organization_id = c_org_id
AND operation_sequence = c_sequence_id
AND requested_quantity <> 0
AND status IN ('ACTIVE','IN-SERVICE');
SELECT workorder_name job_number,
wip_entity_id
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT 1 FROM ahl_workorders
WHERE workorder_id = c_workorder_id
/*
AND (status_code = 3 or
status_code = 1);
SELECT *
FROM ahl_schedule_materials
WHERE rt_oper_material_id = c_rt_oper_mat_id
AND visit_task_id = c_visit_task_id
AND requested_quantity <> 0
AND status IN ('ACTIVE','IN-SERVICE');
SELECT 1
FROM ahl_workorder_operations_v
WHERE workorder_operation_id = c_wo_operation_id
AND c_req_date between trunc(scheduled_start_date)
and trunc(scheduled_end_date) ;
SELECT UOM_CODE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = uom_mean;
SELECT primary_uom_code
FROM MTL_SYSTEM_ITEMS_VL
WHERE inventory_item_id = c_item_id
AND organization_id = c_org_id;
SELECT operation_sequence_num
FROM ahl_workorder_operations
WHERE workorder_operation_id = c_operation_id;
SELECT wo.department_id
FROM WIP_OPERATIONS WO
WHERE wo.wip_entity_id = c_wip_entity_id
AND wo.operation_seq_num = c_oper_seq_num;
AHL_DEBUG_PUB.debug('Before insert status call');
IF p_x_req_material_tbl(i).program_update_date = FND_API.G_MISS_DATE
THEN
l_Req_Material_Tbl(i).program_update_date := NULL;
l_Req_Material_Tbl(i).program_update_date := p_x_req_material_tbl(i).program_update_date;
IF p_x_req_material_tbl(i).last_updated_date = FND_API.G_MISS_DATE
THEN
l_Req_Material_Tbl(i).last_updated_date := NULL;
l_Req_Material_Tbl(i).last_updated_date := p_x_req_material_tbl(i).last_updated_date;
SELECT ahl_schedule_materials_s.NEXTVAL
INTO l_schedule_material_id FROM DUAL;
UPDATE ahl_schedule_materials
SET workorder_operation_id = p_x_req_material_tbl(i).workorder_operation_id,
operation_code = p_x_req_material_tbl(i).operation_code,
operation_sequence = p_x_req_material_tbl(i).operation_sequence,
object_version_number =l_material_rec.object_version_number +1
WHERE scheduled_material_id = l_material_rec.scheduled_material_id;
Insert_Row (
X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
X_OBJECT_VERSION_NUMBER => 1,
X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
X_SCHEDULE_DESIGNATOR => l_schedule_designator,
X_VISIT_ID => l_visit_id,
X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
X_PROCESS_STATUS => null,
X_ERROR_MESSAGE => null,
X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
X_UOM => l_Req_Material_Tbl(i).uom_code,
X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
X_STATUS => nvl(l_Req_Material_Tbl(i).status, 'ACTIVE'),
X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
Insert_Row (
X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
X_OBJECT_VERSION_NUMBER => 1,
X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
X_SCHEDULE_DESIGNATOR => l_schedule_designator,
X_VISIT_ID => l_visit_id,
X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
X_PROCESS_STATUS => null,
X_ERROR_MESSAGE => null,
X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
X_UOM => l_Req_Material_Tbl(i).uom_code,
X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
X_STATUS => 'ACTIVE',
X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => fnd_global.user_id,
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 => 2,
p_transaction_type_code => 1,
p_workorder_operation_id => p_x_req_material_tbl(i).workorder_operation_id,
p_schedule_material_id => p_x_req_material_tbl(i).schedule_material_id,
p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
p_date_required => p_x_req_material_tbl(i).requested_date
);
Insert_Row (
X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
X_OBJECT_VERSION_NUMBER => 1,
X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
X_SCHEDULE_DESIGNATOR => l_schedule_designator,
X_VISIT_ID => l_visit_id,
X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
X_PROCESS_STATUS => null,
X_ERROR_MESSAGE => null,
X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
X_UOM => l_Req_Material_Tbl(i).uom_code,
X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
X_STATUS => nvl(l_Req_Material_Tbl(i).status,'ACTIVE'),
X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => fnd_global.user_id,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => fnd_global.user_id,
X_LAST_UPDATE_LOGIN => fnd_global.login_id
);
PROCEDURE Update_Material_Reqst (
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_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
CURSOR Get_Req_Matrl_cur (c_schedule_material_id IN NUMBER)
IS
SELECT B.scheduled_material_id,
B.inventory_item_id,
B.object_version_number,
B.requested_date,
B.organization_id,
B.visit_id,
B.visit_task_id,
B.requested_quantity,
B.workorder_operation_id,
B.operation_sequence,
B.item_group_id,
B.uom,
B.rt_oper_material_id,
-- modified for FP bug# 6802777
--B.department_id,
WO.department_id,
B.workorder_name,
B.wip_entity_id,
A.attribute_category,
A.attribute1,
A.attribute2,
A.attribute3,
A.attribute4,
A.attribute5,
A.attribute6,
A.attribute7,
A.attribute8,
A.attribute9,
A.attribute10,
A.attribute11,
A.attribute12,
A.attribute13,
A.attribute14,
A.attribute15,
A.completed_quantity,
A.requested_date old_requested_date -- added to fix bug# 5182334.
FROM AHL_SCHEDULE_MATERIALS A,
AHL_JOB_OPER_MATERIALS_V B, WIP_OPERATIONS WO
WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
AND B.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND B.OPERATION_SEQUENCE = WO.OPERATION_SEQ_NUM
AND A.SCHEDULED_MATERIAL_ID = c_schedule_material_id
FOR UPDATE OF A.OBJECT_VERSION_NUMBER;
SELECT inventory_item_id,
schedule_material_id,
date_required,
required_quantity
FROM ahl_wo_operations_txns
WHERE wo_operation_txn_id = c_wo_trans_id;
SELECT 1
FROM ahl_workorders
WHERE workorder_id = c_workorder_id
AND (status_code = 3 or
status_code = 1);
SELECT max(wo_operation_txn_id)
FROM ahl_wo_operations_txns
WHERE schedule_material_id = c_sch_material_id;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT nvl(SUM(mrv.primary_reservation_quantity), 0) reserved_quantity
FROM mtl_reservations MRV
WHERE MRV.INVENTORY_ITEM_ID = c_item_id
AND MRV.EXTERNAL_SOURCE_CODE = 'AHL'
AND MRV.DEMAND_SOURCE_HEADER_ID = c_wip_entity_id
AND MRV.DEMAND_SOURCE_LINE_ID = c_oper_seq_num;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MATERIAL_REQST';
SAVEPOINT update_material_reqst;
AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt. update material reqst','+PPMRP+');
AHL_DEBUG_PUB.debug('Before processing updates');
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 => 2,
p_transaction_type_code => 1,
p_workorder_operation_id => p_x_req_material_tbl(i).workorder_operation_id,
p_schedule_material_id => p_x_req_material_tbl(i).schedule_material_id,
p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
p_date_required => p_x_req_material_tbl(i).requested_date
);
AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_module_type => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scheduled_material_id => p_x_req_material_tbl(i).schedule_material_id,
p_requested_date => p_x_req_material_tbl(i).requested_date);
UPDATE AHL_SCHEDULE_MATERIALS
SET inventory_item_id = l_req_material_rec.inventory_item_id,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_req_material_rec.requested_date),
requested_quantity = l_req_material_rec.requested_quantity,
object_version_number = l_req_material_rec.object_version_number+1,
visit_id = l_req_material_rec.visit_id,
visit_task_id = l_req_material_rec.visit_task_id,
organization_id = l_req_material_rec.organization_id,
item_group_id = l_req_material_rec.item_group_id,
rt_oper_material_id = l_req_material_rec.rt_oper_material_id,
workorder_operation_id = l_req_material_rec.workorder_operation_id,
attribute_category = l_req_material_rec.attribute_category,
attribute1 = l_req_material_rec.attribute1,
attribute2 = l_req_material_rec.attribute2,
attribute3 = l_req_material_rec.attribute3,
attribute4 = l_req_material_rec.attribute4,
attribute5 = l_req_material_rec.attribute5,
attribute6 = l_req_material_rec.attribute6,
attribute7 = l_req_material_rec.attribute7,
attribute8 = l_req_material_rec.attribute8,
attribute9 = l_req_material_rec.attribute9,
attribute10 = l_req_material_rec.attribute10,
attribute11 = l_req_material_rec.attribute11,
attribute12 = l_req_material_rec.attribute12,
attribute13 = l_req_material_rec.attribute13,
attribute14 = l_req_material_rec.attribute14,
attribute15 = l_req_material_rec.attribute15,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id;
AHL_DEBUG_PUB.debug( 'END OF UPDATE PROCESS');
Ahl_Debug_Pub.debug( 'End of public api Update Material Reqst','+PPMRP+');
ROLLBACK TO update_material_reqst;
AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
ROLLBACK TO update_material_reqst;
AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
ROLLBACK TO update_material_reqst;
p_procedure_name => 'UPDATE_MATERIAL_REQST',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
END Update_Material_Reqst;
SELECT workorder_name
FROM ahl_workorders
WHERE workorder_id = c_workorder_id;
SELECT
AWO.status_code
FROM
AHL_WORKORDERS AWO
WHERE
workorder_id = c_workorder_id;
SELECT B.scheduled_material_id,
B.inventory_item_id,
B.object_version_number,
B.requested_date,
B.organization_id,
B.visit_id,
B.visit_task_id,
B.requested_quantity,
B.workorder_operation_id,
B.operation_sequence,
B.item_group_id,
B.uom,
B.rt_oper_material_id,
B.department_id,
B.workorder_name,
B.wip_entity_id
FROM AHL_SCHEDULE_MATERIALS A,
AHL_JOB_OPER_MATERIALS_V B
WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
AND A.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
SELECT
ASML.scheduled_material_id,
ASML.inventory_item_id,
ASML.object_version_number,
wipr.date_required requested_date,
AVST.organization_id,
AVST.visit_id,
ASML.visit_task_id,
wipr.REQUIRED_QUANTITY requested_quantity,
ASML.workorder_operation_id,
wipr.operation_seq_num operation_sequence,
ASML.item_group_id,
MSIV.PRIMARY_UNIT_OF_MEASURE uom,
ASML.rt_oper_material_id,
AVST.department_id,
AWOS.workorder_name,
AWOS.wip_entity_id
FROM
AHL_WORKORDERS AWOS,
AHL_SCHEDULE_MATERIALS ASML,
wip_requirement_operations wipr,
MTL_SYSTEM_ITEMS_VL MSIV,
AHL_VISITS_VL AVST,
AHL_WORKORDER_OPERATIONS AWOP,
-- added for FP bug# 6802777
WIP_OPERATIONS WOP
WHERE
AWOP.WORKORDER_OPERATION_ID = ASML.WORKORDER_OPERATION_ID AND
AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID AND
ASML.VISIT_ID = AVST.VISIT_ID AND
awos.wip_entity_id = wipr.wip_entity_id AND
asml.operation_sequence = wipr.operation_seq_num AND
asml.inventory_item_id = wipr.inventory_item_id AND
asml.organization_id = wipr.organization_id AND
asml.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND
ASML.ORGANIZATION_ID = MSIV.ORGANIZATION_ID AND
wop.wip_entity_id = wipr.wip_entity_id AND
wop.operation_seq_num = wipr.operation_seq_num AND
asml.status IN ('ACTIVE', 'IN-SERVICE') AND
ASML.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
SELECT nvl(SUM(mrv.primary_reservation_quantity), 0) reserved_quantity
FROM mtl_reservations MRV
WHERE MRV.INVENTORY_ITEM_ID = c_item_id
AND MRV.EXTERNAL_SOURCE_CODE = 'AHL'
AND MRV.DEMAND_SOURCE_HEADER_ID = c_wip_entity_id
AND MRV.DEMAND_SOURCE_LINE_ID = c_oper_seq_num;
SELECT object_version_number,requested_quantity INTO l_object_version_number,
p_x_req_material_tbl(i).requested_quantity
FROM ahl_schedule_materials
WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id
FOR UPDATE OF STATUS NOWAIT;
AHL_DEBUG_PUB.debug('Before calling delete reservation api');
AHL_RSV_RESERVATIONS_PVT.DELETE_RESERVATION(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_module_type => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_scheduled_material_id => p_x_req_material_tbl(i).schedule_material_id);
AHL_DEBUG_PUB.debug('After calling delete reservation api. Return status:' || x_return_status);
UPDATE AHL_SCHEDULE_MATERIALS
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = p_x_req_material_tbl(i).object_version_number + 1
WHERE SCHEDULED_MATERIAL_ID = p_x_req_material_tbl(i).schedule_material_id;
Update_Material_Reqst
(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_module_type => p_module_type,
p_x_req_material_tbl => l_req_up_material_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
SELECT scheduled_material_id,
rt_oper_material_id
FROM ahl_schedule_materials
WHERE scheduled_material_id = c_schedule_material_id;
SELECT distinct(inventory_item_id)
FROM mtl_system_items_kfv
WHERE concatenated_segments = c_segments;
SELECT scheduled_material_id,
rt_oper_material_id,requested_quantity
FROM ahl_schedule_materials
WHERE scheduled_material_id = c_schedule_material_id;
SELECT distinct(inventory_item_id)
FROM mtl_system_items_kfv
WHERE concatenated_segments = c_segments;
p_last_update_date IN DATE,
p_last_updated_by IN NUMBER,
p_creation_date IN DATE,
p_created_by IN NUMBER,
p_last_update_login IN NUMBER,
p_load_type_code IN NUMBER,
p_transaction_type_code IN NUMBER,
p_workorder_operation_id IN NUMBER := NULL,
p_operation_resource_id IN NUMBER := NULL,
p_schedule_material_id IN NUMBER := NULL,
p_bom_resource_id IN NUMBER := NULL,
p_cost_basis_code IN NUMBER := NULL,
p_total_required IN NUMBER := NULL,
p_assigned_units IN NUMBER := NULL,
p_autocharge_type_code IN NUMBER := NULL,
p_standard_rate_flag_code IN NUMBER := NULL,
p_applied_resource_units IN NUMBER := NULL,
p_applied_resource_value IN NUMBER := NULL,
p_inventory_item_id IN NUMBER := NULL,
p_scheduled_quantity IN NUMBER := NULL,
p_scheduled_date IN DATE := NULL,
p_mrp_net_flag IN NUMBER := NULL,
p_quantity_per_assembly IN NUMBER := NULL,
p_required_quantity IN NUMBER := NULL,
p_supply_locator_id IN NUMBER := NULL,
p_supply_subinventory IN NUMBER := NULL,
p_date_required IN DATE := NULL,
p_operation_type_code IN VARCHAR2 := NULL,
p_res_sched_start_date IN DATE := NULL,
p_res_sched_end_date IN DATE := NULL,
p_op_scheduled_start_date IN DATE := NULL,
p_op_scheduled_end_date IN DATE := NULL,
p_op_actual_start_date IN DATE := NULL,
p_op_actual_end_date IN DATE := NULL,
p_attribute_category IN VARCHAR2 := NULL,
p_attribute1 IN VARCHAR2 := NULL,
p_attribute2 IN VARCHAR2 := NULL,
p_attribute3 IN VARCHAR2 := NULL,
p_attribute4 IN VARCHAR2 := NULL,
p_attribute5 IN VARCHAR2 := NULL,
p_attribute6 IN VARCHAR2 := NULL,
p_attribute7 IN VARCHAR2 := NULL,
p_attribute8 IN VARCHAR2 := NULL,
p_attribute9 IN VARCHAR2 := NULL,
p_attribute10 IN VARCHAR2 := NULL,
p_attribute11 IN VARCHAR2 := NULL,
p_attribute12 IN VARCHAR2 := NULL,
p_attribute13 IN VARCHAR2 := NULL,
p_attribute14 IN VARCHAR2 := NULL,
p_attribute15 IN VARCHAR2 := NULL)
IS
BEGIN
--
INSERT INTO AHL_WO_OPERATIONS_TXNS
( wo_operation_txn_id ,
object_version_number ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
last_update_login ,
load_type_code ,
transaction_type_code ,
workorder_operation_id ,
operation_resource_id ,
schedule_material_id ,
bom_resource_id ,
cost_basis_code ,
total_required ,
assigned_units ,
autocharge_type_code ,
standard_rate_flag_code ,
applied_resource_units ,
applied_resource_value ,
inventory_item_id ,
scheduled_quantity ,
scheduled_date ,
mrp_net_flag ,
quantity_per_assembly ,
required_quantity ,
supply_locator_id ,
supply_subinventory ,
date_required ,
operation_type_code ,
res_sched_start_date ,
res_sched_end_date ,
op_scheduled_start_date ,
op_scheduled_end_date ,
op_actual_start_date ,
op_actual_end_date ,
attribute_category ,
attribute1 ,
attribute2 ,
attribute3 ,
attribute4 ,
attribute5 ,
attribute6 ,
attribute7 ,
attribute8 ,
attribute9 ,
attribute10 ,
attribute11 ,
attribute12 ,
attribute13 ,
attribute14 ,
attribute15
)
VALUES
(
p_wo_operation_txn_id ,
p_object_version_number ,
p_last_update_date ,
p_last_updated_by ,
p_creation_date ,
p_created_by ,
p_last_update_login ,
p_load_type_code ,
p_transaction_type_code ,
p_workorder_operation_id ,
p_operation_resource_id ,
p_schedule_material_id ,
p_bom_resource_id ,
p_cost_basis_code ,
p_total_required ,
p_assigned_units ,
p_autocharge_type_code ,
p_standard_rate_flag_code ,
p_applied_resource_units ,
p_applied_resource_value ,
p_inventory_item_id ,
p_scheduled_quantity ,
p_scheduled_date ,
p_mrp_net_flag ,
p_quantity_per_assembly ,
p_required_quantity ,
p_supply_locator_id ,
p_supply_subinventory ,
p_date_required ,
p_operation_type_code ,
p_res_sched_start_date ,
p_res_sched_end_date ,
p_op_scheduled_start_date ,
p_op_scheduled_end_date ,
p_op_actual_start_date ,
p_op_actual_end_date ,
p_attribute_category ,
p_attribute1 ,
p_attribute2 ,
p_attribute3 ,
p_attribute4 ,
p_attribute5 ,
p_attribute6 ,
p_attribute7 ,
p_attribute8 ,
p_attribute9 ,
p_attribute10 ,
p_attribute11 ,
p_attribute12 ,
p_attribute13 ,
p_attribute14 ,
p_attribute15
);
SELECT SUM(QUANTITY) FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = p_org_id
AND INVENTORY_ITEM_ID = p_item_id
AND WORKORDER_OPERATION_ID = p_wo_op_id
AND TRANSACTION_TYPE_ID = 35;
SELECT SUM(QUANTITY)
FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = c_org_id
AND INVENTORY_ITEM_ID = c_itme_Id
AND WORKORDER_OPERATION_ID = c_wo_op_id
AND TRANSACTION_TYPE_ID = 35; -- Mtl Issue Txn
SELECT SUM(QUANTITY)
FROM AHL_WORKORDER_MTL_TXNS
WHERE ORGANIZATION_ID = c_org_id
AND INVENTORY_ITEM_ID = c_itme_Id
AND WORKORDER_OPERATION_ID = c_wo_op_id
AND TRANSACTION_TYPE_ID = 43; -- Mtl Rtn Txn
SELECT ASM.scheduled_material_id,
ASM.visit_id,visit_task_id,
ASM.inventory_item_id,
ASM.organization_id,
ASM.requested_date,uom,
ASM.rt_oper_material_id,
ASM.operation_code,
ASM.operation_sequence,
ASM.requested_quantity,
ASM.workorder_operation_id,
ASM.position_path_id,
ASM.relationship_id,
ASM.mr_route_id,
ASM.material_request_type,
ASM.status
FROM AHL_SCHEDULE_MATERIALS ASM,
AHL_RT_OPER_MATERIALS ARM
WHERE ASM.rt_oper_material_id = ARM.RT_OPER_MATERIAL_ID
AND ASM.visit_task_id = C_VISIT_TASK_ID
AND ASM.requested_quantity > 0
AND ASM.STATUS IN ('ACTIVE','IN-SERVICE');
SELECT a.visit_id,
visit_task_id,
organization_id
FROM ahl_workorders A,
ahl_visits_b b
WHERE workorder_id = c_workorder_id
AND a.visit_id = b.visit_id;
SELECT Scheduled_material_id
FROM AHL_SCHEDULE_MATERIALS
WHERE WORKORDER_OPERATION_ID = c_operation_id
AND OPERATION_SEQUENCE = c_operation_sequence;
UPDATE ahl_schedule_materials
SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
operation_sequence = l_prd_wooperation_tbl(i).operation_sequence_num,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
organization_id = l_Visit_Task_Rec.organization_id,
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
ATTRIBUTE_CATEGORY = l_req_material_tbl(l_idx).attribute_category,
ATTRIBUTE1 = l_req_material_tbl(l_idx).attribute1,
ATTRIBUTE2 = l_req_material_tbl(l_idx).attribute2,
ATTRIBUTE3 = l_req_material_tbl(l_idx).attribute3,
ATTRIBUTE4 = l_req_material_tbl(l_idx).attribute4,
ATTRIBUTE5 = l_req_material_tbl(l_idx).attribute5,
ATTRIBUTE6 = l_req_material_tbl(l_idx).attribute6,
ATTRIBUTE7 = l_req_material_tbl(l_idx).attribute7,
ATTRIBUTE8 = l_req_material_tbl(l_idx).attribute8,
ATTRIBUTE9 = l_req_material_tbl(l_idx).attribute9,
ATTRIBUTE10 = l_req_material_tbl(l_idx).attribute10,
ATTRIBUTE11 = l_req_material_tbl(l_idx).attribute11,
ATTRIBUTE12 = l_req_material_tbl(l_idx).attribute12,
ATTRIBUTE13 = l_req_material_tbl(l_idx).attribute13,
ATTRIBUTE14 = l_req_material_tbl(l_idx).attribute14,
ATTRIBUTE15 = l_req_material_tbl(l_idx).attribute15
WHERE scheduled_material_id = l_Sche_Mat_Rec.scheduled_material_id;
UPDATE ahl_schedule_materials
SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
object_version_number = object_version_number + 1,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
organization_id = l_Visit_Task_Rec.organization_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id,
ATTRIBUTE_CATEGORY = l_req_material_tbl(l_idx).attribute_category,
ATTRIBUTE1 = l_req_material_tbl(l_idx).attribute1,
ATTRIBUTE2 = l_req_material_tbl(l_idx).attribute2,
ATTRIBUTE3 = l_req_material_tbl(l_idx).attribute3,
ATTRIBUTE4 = l_req_material_tbl(l_idx).attribute4,
ATTRIBUTE5 = l_req_material_tbl(l_idx).attribute5,
ATTRIBUTE6 = l_req_material_tbl(l_idx).attribute6,
ATTRIBUTE7 = l_req_material_tbl(l_idx).attribute7,
ATTRIBUTE8 = l_req_material_tbl(l_idx).attribute8,
ATTRIBUTE9 = l_req_material_tbl(l_idx).attribute9,
ATTRIBUTE10 = l_req_material_tbl(l_idx).attribute10,
ATTRIBUTE11 = l_req_material_tbl(l_idx).attribute11,
ATTRIBUTE12 = l_req_material_tbl(l_idx).attribute12,
ATTRIBUTE13 = l_req_material_tbl(l_idx).attribute13,
ATTRIBUTE14 = l_req_material_tbl(l_idx).attribute14,
ATTRIBUTE15 = l_req_material_tbl(l_idx).attribute15
WHERE scheduled_material_id = l_Sche_Mat_Rec.scheduled_material_id;
UPDATE ahl_schedule_materials
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
SET requested_date = trunc(l_prd_wooperation_tbl(i).actual_start_date),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE scheduled_material_id = l_scheduled_material_id;
SELECT A.APPROVAL_RULE_ID,
A.APPROVAL_OBJECT_CODE,
A.STATUS_CODE,
B.APPROVER_NAME,
B.APPROVER_SEQUENCE
FROM AHL_APPROVAL_RULES_B A,AHL_APPROVERS_V B
WHERE A.APPROVAL_RULE_ID=B.APPROVAL_RULE_ID
AND A.STATUS_CODE='ACTIVE'
AND A.APPROVAL_OBJECT_CODE=c_object_type
ORDER BY B.APPROVER_SEQUENCE;
SELECT DISTINCT
JRREV.USER_NAME APPROVER_NAME
FROM
AHL_APPROVERS AA,
FND_LOOKUP_VALUES_VL FNDA,
AHL_JTF_RS_EMP_V JRREV,
AHL_APPROVAL_RULES_B APR
WHERE
FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
AND AA.APPROVER_TYPE_CODE = 'USER'
AND AA.APPROVER_ID = JRREV.RESOURCE_ID
AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
AND APR.APPROVAL_OBJECT_CODE = c_object_type
UNION
SELECT DISTINCT
JRRV.ROLE_NAME APPROVER_NAME
FROM
AHL_APPROVERS AA,
FND_LOOKUP_VALUES_VL FNDA,
JTF_RS_ROLE_RELATIONS_VL JRRV,
AHL_APPROVAL_RULES_B APR
WHERE
FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
AND AA.APPROVER_TYPE_CODE = 'ROLE'
AND AA.APPROVER_ID = JRRV.ROLE_ID
AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
AND APR.APPROVAL_OBJECT_CODE = c_object_type
UNION
SELECT DISTINCT
'' APPROVER_NAME
FROM
AHL_APPROVERS AA,
FND_LOOKUP_VALUES_VL FNDA,
AHL_APPROVAL_RULES_B APR
WHERE
FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
AND AA.APPROVER_TYPE_CODE = 'ROLE'
AND AA.APPROVER_ID IS NULL
AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
AND APR.APPROVAL_OBJECT_CODE = c_object_type;
FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_NTF_UPDATE');