[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asm.scheduled_material_id,
asm.object_version_number,
asm.inventory_item_id,
asm.visit_id,
asm.scheduled_quantity,
asm.scheduled_date,
asm.visit_task_id,
asm.organization_id,
asm.requested_quantity,
asm.uom,
asm.requested_date,
asm.status,
mtl.concatenated_segments
FROM ahl_schedule_materials asm,
mtl_system_items_kfv mtl
WHERE asm.visit_id = c_visit_id
AND mtl.inventory_item_id = asm.inventory_item_id
AND mtl.organization_id = asm.organization_id;
SELECT scheduled_date,scheduled_quantity
FROM ahl_visit_task_matrl_v
WHERE schedule_material_id = c_sch_mat_id;
UPDATE ahl_schedule_materials
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = l_sch_mtls_Rec.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_sch_mtls_Rec.scheduled_material_id;
'Before calling AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials. l_unsched_mtl_tbl.COUNT = ' || l_unsched_mtl_tbl.COUNT);
AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials(p_api_version => 1.0,
p_deleted_matrl_tbl => l_unsched_mtl_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After returning from AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials, l_return_status = ' || l_return_status);
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS. Error Message Status: ' || X_RETURN_STATUS);
PROCEDURE Insert_Planned_Matrls(
p_visit_id IN NUMBER,
p_visit_task_id IN NUMBER,
p_task_start_date IN DATE,
p_inventory_item_id IN NUMBER,
p_requested_quantity IN NUMBER,
p_uom_code IN VARCHAR2,
p_item_group_id IN NUMBER,
p_rt_oper_material_id IN NUMBER,
p_position_path_id IN NUMBER,
p_relationship_id IN NUMBER,
p_mr_route_id IN NUMBER default null,
p_item_comp_detail_id IN NUMBER default null,
p_inv_master_org_id IN NUMBER default null,
p_mc_header_id IN NUMBER, -- Added by surrkuma on 07-Jun-2011 for Service Bulletin
p_position_key IN NUMBER, -- Added by surrkuma on 07-Jun-2011 for Service Bulletin
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Check for record already exists
-- Modified by surrkuma on 28-Jul-2011 for handling control position
-- based material requirements.
CURSOR check_matrl_cur (c_visit_id IN NUMBER,
c_visit_task_id IN NUMBER,
c_rt_oper_mat_id IN NUMBER,
c_position_key IN NUMBER)
IS
-- yazhou 17-May-2006 starts
-- bug fix#5232544
-- yazhou 03-JUL-2006 starts
-- bug fix#5303378
SELECT scheduled_material_id
FROM AHL_SCHEDULE_MATERIALS
WHERE visit_id = c_visit_id
AND visit_task_id = c_visit_task_id
-- AND requested_quantity <> 0
AND NVL(status,'') = 'ACTIVE'
AND rt_oper_material_id = c_rt_oper_mat_id
-- surrkuma on 28-Jul-2011 for handling control position based material
-- requirements
AND NVL(position_key, 0) = NVL(c_position_key, 0);
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT it.inventory_item_id,
it.priority,
it.uom_code,
it.quantity
FROM ahl_item_associations_vl it,
mtl_system_items_vl mt
WHERE it.inventory_item_id = mt.inventory_item_id
AND item_group_id = C_ITEM_GROUP_ID
AND mt.organization_id = C_ORG_ID
-- Fix for bug # 4109330
AND it.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER BY priority;
SELECT route_id
FROM ahl_mr_routes mr,
ahl_visit_tasks_b vt
WHERE mr.mr_route_id = vt.mr_route_id
AND visit_task_id = c_visit_task_id;
SELECT ro.step,
ro.operation_id,
ro.concatenated_segments
FROM ahl_route_operations_v ro,
ahl_rt_oper_materials rm
WHERE ro.operation_id = rm.object_id
AND ro.route_id = c_route_id
AND rm.rt_oper_material_id = c_rt_oper_mat_id
AND rm.association_type_code = 'OPERATION';
SELECT inventory_item_id,
primary_uom_code
FROM mtl_system_items_vl
WHERE inventory_item_id = C_ITEM_ID
AND organization_id = C_ORG_ID;
SELECT quantity,
in_service, --B5865210 - sowsubra
replace_percent,
association_type_code
FROM ahl_rt_oper_materials
WHERE rt_oper_material_id = c_rt_oper_mat_id;
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Planned_Matrls';
SAVEPOINT Insert_Planned_Matrls;
SELECT ahl_schedule_materials_s.nextval INTO l_schedule_material_id FROM DUAL;
SELECT TASK_TYPE_CODE INTO l_task_type_code
FROM ahl_visit_tasks_b
WHERE visit_task_id = p_visit_task_id;
'Before Insert Schedule Materials for Visit Id: '|| p_visit_id ||
', Visit Task Id: ' || p_visit_task_id ||
', Schedule Material Id: ' || l_schedule_material_id);
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,
REQUESTED_QUANTITY,
REQUEST_ID,
REQUESTED_DATE,
SCHEDULED_QUANTITY,
PROCESS_STATUS,
ERROR_MESSAGE,
TRANSACTION_ID,
UOM,
RT_OPER_MATERIAL_ID,
OPERATION_CODE,
ITEM_GROUP_ID,
OPERATION_SEQUENCE,
POSITION_PATH_ID,
RELATIONSHIP_ID,
MR_ROUTE_ID,
MATERIAL_REQUEST_TYPE,
STATUS,
MC_HEADER_ID,
POSITION_KEY,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(l_schedule_material_id,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
l_inventory_item_id,
NULL,
p_visit_id,
NULL,
p_visit_task_id,
l_organization_id,
NULL,
l_prim_quantity,
NULL,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
trunc(p_task_start_date),
l_sched_prim_quantity, -- yazhou 04Aug2005
NULL,
NULL,
NULL,
l_uom_code,
p_rt_oper_material_id,
l_operation_code,
p_item_group_id,
l_step,
p_position_path_id,
p_relationship_id,
p_mr_route_id,
l_material_request_type,
l_mat_status, --Added by sowsubra for Issue 105
p_mc_header_id, -- Added by SURRKUMA :: Service Bulletin :: 07-Jun-2011
p_position_key, -- Added by SURRKUMA :: Service Bulletin :: 07-Jun-2011
-- SKPATHAK :: Bug 8604722 :: 04-MAR-2010 :: START
l_default_dff_values.ATTRIBUTE_CATEGORY,
l_default_dff_values.ATTRIBUTE1,
l_default_dff_values.ATTRIBUTE2,
l_default_dff_values.ATTRIBUTE3,
l_default_dff_values.ATTRIBUTE4,
l_default_dff_values.ATTRIBUTE5,
l_default_dff_values.ATTRIBUTE6,
l_default_dff_values.ATTRIBUTE7,
l_default_dff_values.ATTRIBUTE8,
l_default_dff_values.ATTRIBUTE9,
l_default_dff_values.ATTRIBUTE10,
l_default_dff_values.ATTRIBUTE11,
l_default_dff_values.ATTRIBUTE12,
l_default_dff_values.ATTRIBUTE13,
l_default_dff_values.ATTRIBUTE14,
l_default_dff_values.ATTRIBUTE15);
ROLLBACK TO Insert_Planned_Matrls;
ROLLBACK TO Insert_Planned_Matrls;
ROLLBACK TO Insert_Planned_Matrls;
p_procedure_name => 'INSERT_PLANNED_MATRLS',
p_error_text => SUBSTR(SQLERRM,1,500));
END Insert_Planned_Matrls;
PROCEDURE Update_Planned_Materials (
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_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR Get_Planned_Items_cur (c_sched_mat_id IN NUMBER)
IS
SELECT scheduled_material_id,
object_version_number,
inventory_item_id,
requested_quantity,
visit_task_id,
organization_id,
completed_quantity,
requested_date,
visit_id
FROM ahl_schedule_materials
WHERE scheduled_material_id = c_sched_mat_id;
SELECT inventory_item_id
FROM mtl_system_items_vl
WHERE concatenated_segments = c_item_desc
AND organization_id = c_org_id;
SELECT start_date_time, close_date_time
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
SAVEPOINT Update_Planned_Materials;
'Request for Update Material Number of Records : '|| l_planned_materials_tbl.COUNT
);
SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
INTO l_rsvd_quantity
FROM mtl_reservations MR,
ahl_schedule_materials SM
WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
AND MR.external_source_code = 'AHL'
AND MR.demand_source_line_detail = SM.scheduled_material_id
AND MR.organization_id = SM.organization_id
AND MR.requirement_date = SM.requested_date
AND MR.inventory_item_id = SM.inventory_item_id;
SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
INTO l_rsvd_quantity
FROM mtl_reservations MR,
ahl_schedule_materials SM
WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
AND MR.external_source_code = 'AHL'
AND MR.demand_source_line_detail = SM.scheduled_material_id
AND MR.organization_id = SM.organization_id
AND MR.requirement_date = SM.requested_date
AND MR.inventory_item_id = SM.inventory_item_id;
AHL_RSV_RESERVATIONS_PVT.UPDATE_RESERVATION(
P_API_VERSION => 1.0,
/*P_INIT_MSG_LIST
P_COMMIT
P_VALIDATION_LEVEL */
P_MODULE_TYPE => NULL,
X_RETURN_STATUS => l_return_Status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => X_MSG_DATA,
P_SCHEDULED_MATERIAL_ID => l_planned_materials_tbl(i).schedule_material_id,
P_REQUESTED_DATE => l_planned_materials_tbl(i).requested_date);
UPDATE ahl_schedule_materials
SET inventory_item_id = l_planned_materials_tbl(i).inventory_item_id,
requested_quantity = l_planned_materials_tbl(i).quantity,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_planned_materials_tbl(i).requested_date),
object_version_number = l_planned_materials_tbl(i).object_version_number + 1
WHERE scheduled_material_id = l_planned_materials_tbl(i).schedule_material_id;
ROLLBACK TO Update_Planned_Materials;
ROLLBACK TO Update_Planned_Materials;
ROLLBACK TO Update_Planned_Materials;
p_procedure_name => 'Update_Planned_Materials',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Planned_Materials;
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id,
vt.mr_route_id,
vt.instance_id,
vt.start_date_time
FROM ahl_visits_b vs,
ahl_visit_tasks_b vt
WHERE vs.visit_id = vt.visit_id
AND vt.visit_task_id = C_VISIT_TASK_ID;
SELECT mr.route_id
FROM ahl_mr_routes_app_v mr
WHERE mr.mr_route_id = C_MR_ROUTE_ID;
SELECT 1
FROM ahl_visits_vl
WHERE visit_id = C_VISIT_ID
AND (organization_id IS NULL
OR start_date_time IS NULL);
Insert_Planned_Matrls(
p_visit_id => p_visit_id,
p_visit_task_id => l_visit_tasks_rec.visit_task_id,
p_task_start_date => l_visit_tasks_rec.start_date_time,
p_inventory_item_id => l_route_mtl_req_tbl(i).inventory_item_id,
p_requested_quantity => l_route_mtl_req_tbl(i).quantity,
p_uom_code => l_route_mtl_req_tbl(i).uom_code,
p_item_group_id => l_route_mtl_req_tbl(i).item_group_id,
p_rt_oper_material_id => l_route_mtl_req_tbl(i).rt_oper_material_id,
p_position_path_id => l_route_mtl_req_tbl(i).position_path_id,
p_relationship_id => l_route_mtl_req_tbl(i).relationship_id,
p_mr_route_id => l_visit_tasks_rec.mr_route_id,
p_item_comp_detail_id => l_route_mtl_req_tbl(i).item_comp_detail_id,
p_inv_master_org_id => l_visit_tasks_rec.organization_id,
--SURRKUMA :: Service Bulletin :: 07-Jun-2011
--Added params mc_header_id and position_key to support Position based requirement
p_mc_header_id => l_route_mtl_req_tbl(i).mc_header_id,
p_position_key => l_route_mtl_req_tbl(i).position_key,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
'After calling Insert Planned Materials, Return Status : '|| l_return_status
);
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id,
vt.mr_route_id,
vt.instance_id,
NVL(vt.start_date_time,vs.start_date_time) start_date_time,
mr.route_id
FROM ahl_visits_vl vs,
ahl_visit_tasks_vl vt,
ahl_mr_routes_app_v mr
WHERE vs.visit_id = vt.visit_id
AND vt.mr_route_id = mr.mr_route_id
AND vt.unit_effectivity_id = nvl2(p_ue_id,-1,vt.unit_effectivity_id)
AND vs.visit_id = c_visit_id
UNION
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id,
vt.mr_route_id,
vt.instance_id,
NVL(vt.start_date_time,vs.start_date_time) start_date_time,
mr.route_id
FROM ahl_visits_vl vs,
ahl_visit_tasks_vl vt,
ahl_mr_routes_app_v mr
WHERE vs.visit_id = vt.visit_id
AND vt.mr_route_id = mr.mr_route_id
AND vt.unit_effectivity_id IN
(SELECT tsk2.unit_effectivity_id
FROM ahl_visit_tasks_b tsk1,
ahl_visit_tasks_b tsk2
WHERE tsk1.unit_effectivity_id = p_ue_id
AND tsk1.visit_task_id = tsk2.originating_task_id)
AND vs.visit_id = c_visit_id;
CURSOR Deleted_Items_Cur (c_visit_id IN NUMBER) IS
SELECT asm.visit_id,
asm.scheduled_material_id,
asm.object_version_number,
asm.scheduled_quantity,
asm.scheduled_date,
asm.visit_task_id,
asm.inventory_item_id,
asm.organization_id,
asm.requested_quantity,
asm.uom,
asm.requested_date,
mtl.concatenated_segments
FROM ahl_visit_tasks_b tsk,
ahl_schedule_materials asm,
mtl_system_items_kfv mtl
WHERE asm.visit_id = C_VISIT_ID
AND asm.visit_task_id = tsk.visit_task_id
AND tsk.status_code ='DELETED'
AND asm.status <> 'DELETED'
AND mtl.inventory_item_id = asm.inventory_item_id
AND mtl.organization_id = asm.organization_id;
SELECT asm.requested_quantity,
asm.scheduled_material_id,
asm.object_version_number,
asm.visit_id,
asm.visit_task_id,
asm.inventory_item_id,
asm.organization_id,
asm.uom,
asm.requested_date,
asm.scheduled_date,
mtl.concatenated_segments
FROM ahl_schedule_materials asm,
mtl_system_items_kfv mtl
WHERE asm.visit_task_id = c_visit_task_id
AND asm.rt_oper_material_id = c_rt_oper_material_id
AND NVL(asm.STATUS, 'X') = 'ACTIVE'
AND mtl.inventory_item_id = asm.inventory_item_id
AND mtl.organization_id = asm.organization_id
AND NVL(asm.position_key, 0) = NVL(c_position_key, 0);
l_Deleted_Items_Rec Deleted_Items_Cur%rowtype;
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = C_VISIT_ID
AND ( organization_id IS NOT NULL
OR start_date_time IS NOT NULL
OR department_id IS NOT NULL);
SELECT 1
FROM mtl_system_items_kfv mtl
WHERE mtl.inventory_item_id = c_item_id
AND mtl.organization_id = c_org_id;
SELECT it.inventory_item_id
FROM ahl_item_associations_vl it,
mtl_system_items_vl mt
WHERE it.inventory_item_id = mt.inventory_item_id
AND item_group_id = C_ITEM_GROUP_ID
AND mt.organization_id = C_ORG_ID
AND it.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER BY priority;
SELECT 1
FROM wip_requirement_operations wip,
ahl_schedule_materials asm,
ahl_workorders wo
WHERE wo.visit_task_id = asm.visit_task_id
AND wo.wip_entity_id = wip.wip_entity_id
AND asm.operation_sequence = wip.operation_seq_num
AND asm.inventory_item_id = wip.inventory_item_id
AND asm.organization_id = wip.organization_id
AND asm.scheduled_material_id = c_scheduled_material_id;
OPEN Deleted_Items_cur(p_visit_id);
FETCH Deleted_Items_cur INTO l_Deleted_Items_Rec;
EXIT WHEN Deleted_Items_cur%NOTFOUND;
IF l_Deleted_Items_Rec.scheduled_material_id IS NOT NULL THEN
-- Added by surrkuma on 15-JUL-2010 for bug 9901811
-- Add this requirement to l_unsched_mtl_tbl for ATP unscheduling
IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
'l_unsched_mtl_index = ' || l_unsched_mtl_index ||
', Adding Requirement with id ' || l_Deleted_Items_Rec.scheduled_material_id || ' to l_unsched_mtl_tbl for Unscheduling.');
l_unsched_mtl_tbl(l_unsched_mtl_index).visit_id := l_Deleted_Items_Rec.visit_id;
l_unsched_mtl_tbl(l_unsched_mtl_index).visit_task_id := l_Deleted_Items_Rec.visit_task_id;
l_unsched_mtl_tbl(l_unsched_mtl_index).inventory_item_id := l_Deleted_Items_Rec.inventory_item_id;
l_unsched_mtl_tbl(l_unsched_mtl_index).item_description := l_Deleted_Items_Rec.concatenated_segments;
l_unsched_mtl_tbl(l_unsched_mtl_index).organization_id := l_Deleted_Items_Rec.organization_id;
l_unsched_mtl_tbl(l_unsched_mtl_index).schedule_material_id := l_Deleted_Items_Rec.scheduled_material_id;
l_unsched_mtl_tbl(l_unsched_mtl_index).required_quantity := l_Deleted_Items_Rec.requested_quantity;
l_unsched_mtl_tbl(l_unsched_mtl_index).primary_uom_code := l_Deleted_Items_Rec.uom;
l_unsched_mtl_tbl(l_unsched_mtl_index).requested_date := l_Deleted_Items_Rec.requested_date;
'Updating the status to DELETED for Material Requirement Id ' || l_Deleted_Items_Rec.scheduled_material_id);
UPDATE ahl_schedule_materials
SET requested_quantity =0,
status = 'DELETED',
object_version_number = l_Deleted_Items_Rec.object_version_number + 1
WHERE scheduled_material_id = l_Deleted_Items_Rec.scheduled_material_id ;
CLOSE Deleted_Items_cur;
UPDATE ahl_schedule_materials
SET STATUS = 'DELETED',
object_version_number = l_Planned_Items_Rec.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_Planned_Items_Rec.scheduled_material_id;
UPDATE ahl_schedule_materials
SET inventory_item_id = l_route_mtl_req_tbl(j).inventory_item_id,
organization_id = l_visit_org_id,
requested_date = trunc(l_Visit_Task_Route_Tbl(i).task_start_date),
object_version_number = l_Planned_Items_Rec.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_Planned_Items_Rec.scheduled_material_id ;
'Updated ahl_schedule_materials for scheduled_material_id = ' || l_Planned_Items_Rec.scheduled_material_id);
'About to call Insert_Planned_Matrls with p_visit_id = ' || p_visit_id ||
', p_visit_task_id = ' || l_Visit_Task_Route_Tbl(i).visit_task_id ||
', p_task_start_date = ' || l_Visit_Task_Route_Tbl(i).task_start_date ||
', p_inventory_item_id = ' || l_route_mtl_req_tbl(j).inventory_item_id ||
', p_requested_quantity = ' || l_requested_qty ||
', p_uom_code = ' || l_route_mtl_req_tbl(j).uom_code ||
', p_rt_oper_material_id = ' || l_route_mtl_req_tbl(j).rt_oper_material_id ||
', p_inv_master_org_id = ' || l_visit_org_id);
Insert_Planned_Matrls(
p_visit_id => p_visit_id,
p_visit_task_id => l_Visit_Task_Route_Tbl(i).visit_task_id,
p_task_start_date => l_Visit_Task_Route_Tbl(i).task_start_date,
p_inventory_item_id => l_route_mtl_req_tbl(j).inventory_item_id,
p_requested_quantity => l_requested_qty,
p_uom_code => l_route_mtl_req_tbl(j).uom_code,
p_item_group_id => l_route_mtl_req_tbl(j).item_group_id,
p_rt_oper_material_id => l_route_mtl_req_tbl(j).rt_oper_material_id,
p_position_path_id => l_route_mtl_req_tbl(j).position_path_id,
p_relationship_id => l_route_mtl_req_tbl(j).relationship_id,
p_mr_route_id => l_Visit_Task_Route_Tbl(i).mr_route_id,
p_item_comp_detail_id => l_route_mtl_req_tbl(j).item_comp_detail_id,
-- AnRaj: changed the paramter, for fixing bug where org id was being incorrectly updated
p_inv_master_org_id => l_visit_org_id ,
--SURRKUMA :: Service Bulletin :: 07-Jun-2011
--Added params mc_header_id and position_key to support Position based requirement
p_mc_header_id => l_route_mtl_req_tbl(j).mc_header_id,
p_position_key => l_route_mtl_req_tbl(j).position_key,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
'Returned from call to Insert_Planned_Matrls. x_return_status = ' || l_return_status);
'Before calling AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials. l_unsched_mtl_tbl.COUNT = ' || l_unsched_mtl_tbl.COUNT);
AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials(p_api_version => 1.0,
p_deleted_matrl_tbl => l_unsched_mtl_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After returning from AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials, l_return_status = ' || l_return_status);
SELECT ASMT.visit_id,
ASMT.visit_task_id,
ASMT.scheduled_material_id schedule_material_id,
decode(sign( trunc(ASMT.scheduled_date) - trunc(requested_date)), 1, ASMT.scheduled_date, null) SCHEDULED_DATE,
ASMT.SCHEDULED_QUANTITY
FROM AHL_SCHEDULE_MATERIALS ASMT,
AHL_VISIT_TASKS_B VTSK
WHERE ASMT.STATUS <> 'DELETED'
AND EXISTS ( Select 1
from AHL_RT_OPER_MATERIALS RTOM
where RTOM.RT_OPER_MATERIAL_ID = ASMT.RT_OPER_MATERIAL_ID)
AND VTSK.VISIT_ID = ASMT.VISIT_ID
AND VTSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND NVL(VTSK.STATUS_CODE,'X') <> 'DELETED'
AND ASMT.VISIT_ID = C_VISIT_ID
AND scheduled_date IS NOT NULL;
SELECT visit_id,visit_task_id,schedule_material_id,
scheduled_date,scheduled_quantity
FROM ahl_visit_task_matrl_v
WHERE visit_id = C_VISIT_ID
AND scheduled_date IS NOT NULL;
SELECT asso_primary_visit_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
'Before calling Update_Unplanned_Matrls');
Update_Unplanned_Matrls (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After calling Update_Unplanned_Matrls, Return Status: '|| l_return_status
);
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id
FROM ahl_visits_vl vs,
ahl_visit_tasks_vl vt
WHERE vs.visit_id = vt.visit_id
AND vs.visit_id = c_visit_id
AND vt.visit_task_id = c_visit_task_id;
SELECT vtm.visit_id,
vtm.visit_task_id,
vtm.schedule_material_id,
vtm.object_version_number,
vtm.inventory_item_id,
vtm.scheduled_date,
vtm.scheduled_quantity,
vtm.item_number,
asm.organization_id,
asm.requested_quantity,
asm.uom,
asm.requested_date,
asm.scheduled_date asm_scheduled_date
FROM ahl_visit_task_matrl_v vtm,
ahl_schedule_materials asm
WHERE vtm.visit_task_id = c_visit_task_id
AND asm.scheduled_material_id = vtm.schedule_material_id;
SELECT vtm.visit_id,
vtm.visit_task_id,
vtm.schedule_material_id,
vtm.object_version_number,
vtm.inventory_item_id,
vtm.scheduled_date,
vtm.scheduled_quantity,
vtm.item_number,
asm.organization_id,
asm.requested_quantity,
asm.uom,
asm.requested_date,
asm.scheduled_date asm_scheduled_date
FROM ahl_visit_task_matrl_v vtm,
ahl_schedule_materials asm
WHERE vtm.visit_id = c_visit_id
AND asm.scheduled_material_id = vtm.schedule_material_id;
UPDATE ahl_schedule_materials
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = l_visit_task_mtrls_rec.object_version_number + 1
WHERE scheduled_material_id = l_visit_task_mtrls_rec.schedule_material_id;
UPDATE ahl_schedule_materials
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = l_visit_mtrls_rec.object_version_number + 1
WHERE scheduled_material_id = l_visit_mtrls_rec.schedule_material_id;
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
);
'Before calling AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials. l_unsched_mtl_tbl.COUNT = ' || l_unsched_mtl_tbl.COUNT);
AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials(p_api_version => 1.0,
p_deleted_matrl_tbl => l_unsched_mtl_tbl,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After returning from AHL_LTP_MATRL_AVAL_PVT.Unschedule_deleted_materials, l_return_status = ' || l_return_status);
PROCEDURE Update_Unplanned_Matrls (
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_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR visit_mtrls_cur (c_visit_id IN NUMBER)
IS
SELECT visit_id,
visit_task_id,
scheduled_material_id,
object_version_number
FROM ahl_schedule_materials
WHERE visit_id = c_visit_id
AND status = 'ACTIVE';
l_api_name CONSTANT VARCHAR2(30) := 'Update_Unplanned_Matrls';
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update_Unplanned_Matrls' || '.begin',
'At the start of PLSQL procedure'
);
SAVEPOINT Update_Unplanned_Matrls;
'Request for Update Materials for Visit Id : '|| p_visit_id
);
UPDATE ahl_schedule_materials
SET STATUS = 'HISTORY',
OBJECT_VERSION_NUMBER = l_visit_mtrls_rec.object_version_number
WHERE scheduled_material_id = l_visit_mtrls_rec.scheduled_material_id;
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
);
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update Unplanned Matrls.end',
'At the end of PLSQL procedure'
);
ROLLBACK TO Update_Unplanned_Matrls;
ROLLBACK TO Update_Unplanned_Matrls;
ROLLBACK TO Update_Unplanned_Matrls;
p_procedure_name => 'UPDATE_UNPLANNED_MATRLS',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Unplanned_Matrls;
SELECT mat.scheduled_material_id
FROM ahl_schedule_materials mat,
ahl_visit_tasks_b vt
WHERE vt.visit_id = c_visit_id
AND vt.status_code = 'DELETED'
AND vt.visit_task_id = mat.visit_task_id
AND EXISTS (SELECT reservation_id
FROM mtl_reservations RSV
WHERE RSV.external_source_code = 'AHL'
AND RSV.demand_source_line_detail = mat.scheduled_material_id
AND RSV.organization_id = mat.organization_id
AND RSV.requirement_date = mat.requested_date
AND RSV.inventory_item_id = mat.inventory_item_id );
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT organization_id
FROM mtl_reservations
WHERE external_source_code = 'AHL'
AND demand_source_header_id in ( SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id);
SELECT count(distinct organization_id)
INTO l_org_count
FROM mtl_reservations
WHERE external_source_code = 'AHL'
AND demand_source_header_id in ( SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id);
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
'After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS:X_RETURN_STATUS '||X_RETURN_STATUS
);
AHL_RSV_RESERVATIONS_PVT.Delete_Reservation(
p_module_type => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scheduled_material_id => l_scheduled_material_id
);
'After calling AHL_RSV_RESERVATIONS_PVT.Delete_Reservation:l_return_status '||l_return_status
);
END LOOP; -- For all the material requirements of the deleted tasks
AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations(
X_RETURN_STATUS => x_return_status,
P_VISIT_ID => p_visit_id);
'After calling AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations:x_return_status '||x_return_status
);
PROCEDURE Update_Material_Reqrs_status
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_module_type IN VARCHAR2,
p_visit_task_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'Update_Material_Reqrs_status';
SAVEPOINT Update_Material_Reqrs_sts;
UPDATE ahl_Schedule_materials
SET STATUS = 'HISTORY',
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 visit_task_id = p_visit_task_id
AND STATUS = 'ACTIVE';
'No of rows updated - '||SQL%ROWCOUNT);
ROLLBACK TO Update_Material_Reqrs_sts;
ROLLBACK TO Update_Material_Reqrs_sts;
ROLLBACK TO Update_Material_Reqrs_sts;
END Update_Material_Reqrs_status;
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT scheduled_material_id, requested_quantity, scheduled_quantity, uom
FROM ahl_schedule_materials
WHERE visit_task_id = c_task_id
AND inventory_item_id = c_item_id
AND status <> 'DELETED';
SELECT scheduled_material_id
FROM ahl_schedule_materials
WHERE visit_task_id = c_task_id
AND inventory_item_id = c_item_id
AND status <> 'DELETED';
SELECT task.visit_task_id, stage.planned_start_date
FROM ahl_visit_stage_typ_asoc assoc, ahl_visit_tasks_b task, ahl_vwp_stages_b stage
WHERE (assoc.stage_id = task.stage_id
OR task.stage_id IS NULL)
AND task.task_type_code = 'STAGE'
AND task.visit_id = c_visit_id
AND (assoc.stage_type_code = NVL(c_stage_type_code, '-1')
OR task.stage_id IS NULL)
AND task.status_code = 'PLANNING'
AND stage.visit_id = c_visit_id
AND (stage.stage_id = task.stage_id
OR task.stage_id IS NULL)
ORDER BY task.stage_id NULLS LAST;
SELECT wip.scheduled_start_date, wo.workorder_id, opr.operation_sequence_num, opr.workorder_operation_id
FROM ahl_workorders wo, wip_discrete_jobs wip, ahl_workorder_operations opr
WHERE wo.visit_task_id = c_task_id
AND wo.wip_entity_id = wip.wip_entity_id
AND opr.workorder_id = wo.workorder_id;
SELECT primary_uom_code
FROM mtl_system_items_vl
WHERE inventory_item_id = C_ITEM_ID
AND organization_id = C_ORG_ID;
UPDATE ahl_schedule_materials
SET requested_quantity = requested_quantity + l_prim_quantity,
scheduled_quantity = scheduled_quantity + l_prim_quantity,
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;
Insert_Planned_Matrls(
p_visit_id => p_visit_id,
p_visit_task_id => l_stage_task_rec.visit_task_id,
p_task_start_date => l_stage_task_rec.planned_start_date,
p_inventory_item_id => l_nrp_mat_req_tbl(i).INVENTORY_ITEM_ID,
p_requested_quantity => l_prim_quantity, --Quantity in primary UOM
p_uom_code => l_prim_uom_code, --Primary UOM code
p_item_group_id => NULL,
p_rt_oper_material_id => NULL,
p_position_path_id => NULL,
p_relationship_id => NULL,
p_mr_route_id => NULL,
p_item_comp_detail_id => NULL,
p_inv_master_org_id => l_visit_org_id,
p_mc_header_id => NULL,
p_position_key => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
'After calling AHL_LTP_REQST_MATRL_PVT.Insert_Planned_Matrls. l_return_status = ' || l_return_status);
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT scheduled_material_id, requested_quantity, scheduled_quantity, uom
FROM ahl_schedule_materials
WHERE visit_task_id = c_task_id
AND inventory_item_id = c_item_id
AND status <> 'DELETED';
SELECT task.visit_task_id, task.start_date_time
FROM ahl_visit_stage_typ_asoc assoc, ahl_visit_tasks_b task
WHERE (assoc.stage_id = task.stage_id
OR task.stage_id IS NULL)
AND task.task_type_code = 'STAGE'
AND task.visit_id = c_visit_id
AND (assoc.stage_type_code = NVL(c_stage_type_code, '-1')
OR task.stage_id IS NULL)
AND task.status_code = 'PLANNING'
ORDER BY task.stage_id NULLS LAST;
SELECT wip.scheduled_start_date, wo.workorder_id, opr.operation_sequence_num, opr.workorder_operation_id
FROM ahl_workorders wo, wip_discrete_jobs wip, ahl_workorder_operations opr
WHERE wo.visit_task_id = c_task_id
AND wo.wip_entity_id = wip.wip_entity_id
AND opr.workorder_id = wo.workorder_id;
UPDATE ahl_schedule_materials
SET requested_quantity = l_prim_req_quantity,
scheduled_quantity = l_prim_sch_quantity,
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;
PROCEDURE Delete_Default_Stage_Materials (
p_api_version IN NUMBER := 1.0,
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 := 'JSP',
p_visit_task_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)IS
-- Local Variables
-- Standard in/out parameters
l_api_name VARCHAR2(30) := 'Delete_Default_Stage_Materials';
L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Delete_Default_Stage_Materials';
SELECT scheduled_material_id, uom,inventory_item_id
FROM ahl_schedule_materials
WHERE visit_task_id = c_task_id
AND status <> 'DELETED';
SELECT wip.scheduled_start_date, wo.workorder_id, opr.operation_sequence_num, opr.workorder_operation_id, wo.visit_id
FROM ahl_workorders wo, wip_discrete_jobs wip, ahl_workorder_operations opr
WHERE wo.visit_task_id = c_task_id
AND wo.wip_entity_id = wip.wip_entity_id
AND opr.workorder_id = wo.workorder_id;
SAVEPOINT Delete_Default_Stage_Materials;
ROLLBACK TO Delete_Default_Stage_Materials;
ROLLBACK TO Delete_Default_Stage_Materials;
ROLLBACK TO Delete_Default_Stage_Materials;
p_procedure_name => 'Delete_Default_Stage_Materials',
p_error_text => SUBSTR(SQLERRM,1,500));
END Delete_Default_Stage_Materials;