[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
AND lookup_code = c_error_code;
SELECT instance_id
FROM MRP_AP_APPS_INSTANCES;
SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
INTO l_session_id FROM DUAL;
l_atp_table.Insert_Flag := Mrp_Atp_Pub.number_arr(NULL);
SELECT 1 FROM ahl_visits_b
WHERE visit_id = c_visit_id
AND (organization_id IS NULL
OR department_id IS NULL
OR start_date_time IS NULL);
SELECT scheduled_material_id,uom,status,
organization_id,visit_task_id, requested_quantity --SKPATHAK :: Bug 12833742 :: 11-AUG-2011
FROM ahl_schedule_materials
WHERE scheduled_material_id = C_SCH_MAT_ID;
SELECT CONCATENATED_SEGMENTS
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_item_id
AND organization_id = c_org_id;
SELECT schedule_material_id,
object_version_number,
visit_id,
visit_task_id,
visit_task_name,
inventory_item_id,
item_number, --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
requested_quantity,
requested_date,
scheduled_date,
scheduled_quantity,
uom,
sales_order_line_id,
task_status_code,
meaning
FROM ahl_visit_task_matrl_v, FND_LOOKUP_VALUES_VL
WHERE visit_id = C_VISIT_ID
--SKPATHAK :: Bug 8429732 :: 17-APR-2009
--Commented out the condition (requested_quantity <> 0)
/* AND (requested_quantity <> 0) */
AND NVL(mat_status,'X') <> 'IN-SERVICE' --Added by sowsubra
AND LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LOOKUP_code = task_status_code;
p_atp_table.Insert_Flag.Extend;
SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
AND lookup_code = C_Error_Code;
select DECODE( SIGN( trunc(scheduled_date) - trunc(requested_date)),1,scheduled_date,null) scheduled_date,
scheduled_quantity
from ahl_schedule_materials asmt,
AHL_VISIT_TASKS_B tsk
where TSK.VISIT_ID = ASMT.VISIT_ID
AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND NVL(ASMT.STATUS,' ') <> 'DELETED'
AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
AND scheduled_material_id = c_sch_mat_id;
SELECT scheduled_date ,
status, --Added by sowsubra
scheduled_quantity
FROM ahl_schedule_materials asmt,
AHL_VISIT_TASKS_B tsk
WHERE TSK.VISIT_ID = ASMT.VISIT_ID
AND TSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND NVL(ASMT.STATUS,' ') <> 'DELETED'
AND NVL(TSK.STATUS_CODE,'X') <> 'DELETED'
AND scheduled_material_id = c_sch_mat_id
AND scheduled_date is not null;
SELECT visit_number||visit_task_number Order_Number
FROM ahl_visit_tasks_v
WHERE visit_task_id = c_visit_task_id;
SELECT instance_id
FROM MRP_AP_APPS_INSTANCES;
SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
INTO l_session_id FROM DUAL;
UPDATE ahl_schedule_materials
SET scheduled_date = x_atp_table.ship_date(i),
object_version_number = object_version_number + 1
WHERE scheduled_material_id = x_atp_table.identifier(i);
UPDATE ahl_schedule_materials
SET scheduled_date = x_atp_table.ship_date(i),
scheduled_quantity = l_planned_matrl_tbl(i).required_quantity,
object_version_number = object_version_number + 1
WHERE scheduled_material_id = x_atp_table.identifier(i);
SELECT schm.inventory_item_id,
schm.organization_id,
schm.uom,
schm.requested_date,
schm.status mat_status, --Added by sowsubra
avtm.item_number --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
FROM ahl_schedule_materials schm,
ahl_visit_task_matrl_v avtm
WHERE schm.scheduled_material_id = avtm.schedule_material_id
AND avtm.schedule_material_id = c_sch_mat_id;
SELECT schm.inventory_item_id,
AVTM.organization_id,
schm.uom,
schm.requested_date,
schm.status mat_status,
avtm.CONCATENATED_SEGMENTS
FROM ahl_schedule_materials schm,
mtl_system_items_kfv avtm
WHERE SCHM.INVENTORY_ITEM_ID = AVTM.INVENTORY_ITEM_ID
AND AVTM.ORGANIZATION_ID = SCHM.ORGANIZATION_ID
AND SCHM.SCHEDULED_MATERIAL_ID = c_sch_mat_id;
SELECT visit_id,
visit_task_id,
visit_task_name,
requested_quantity,
scheduled_date,
scheduled_quantity,
item_number, --Modified by rnahata for ER 6391157, ahl_visit_task_matrl_v definition changed
object_version_number,
inventory_item_id,
uom,
requested_date,
task_status_code,
meaning
FROM ahl_visit_task_matrl_v,FND_LOOKUP_VALUES_VL
WHERE schedule_material_id = c_sch_mat_id
AND LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS'
AND LOOKUP_code = task_status_code;
SELECT ASMT.VISIT_ID,
ASMT.VISIT_TASK_ID,
AVTL.VISIT_TASK_NAME,
ASMT.REQUESTED_QUANTITY,
(SELECT scheduled_date
FROM ahl_schedule_materials asml
WHERE asml.scheduled_material_id = asmt.scheduled_material_id
AND TRUNC(SCHEDULED_DATE) > TRUNC(REQUESTED_DATE)
) SCHEDULED_DATE,
ASMT.SCHEDULED_QUANTITY,
KFV.CONCATENATED_SEGMENTS ITEM_NUMBER,
ASMT.OBJECT_VERSION_NUMBER,
ASMT.INVENTORY_ITEM_ID,
KFV.PRIMARY_UNIT_OF_MEASURE UOM,
ASMT.REQUESTED_DATE,
AVTL.STATUS_CODE TASK_STATUS_CODE,
FLV.MEANING
FROM AHL_SCHEDULE_MATERIALS ASMT,
AHL_VISIT_TASKS_VL AVTL,
MTL_SYSTEM_ITEMS_KFV KFV,
FND_LOOKUP_VALUES_VL FLV
WHERE SCHEDULED_MATERIAL_ID = c_sch_mat_id
AND AVTL.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND ASMT.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID(+)
AND ASMT.ORGANIZATION_ID = KFV.ORGANIZATION_ID(+)
AND AVTL.STATUS_CODE = FLV.LOOKUP_CODE
AND FLV.LOOKUP_TYPE(+) = 'AHL_VWP_TASK_STATUS';
SELECT schm.scheduled_material_id,
schm.organization_id,
schm.visit_id,
schm.visit_task_id,
schm.material_request_type,
schm.uom,
schm.inventory_item_id,
schm.requested_date,
schm.requested_quantity,
mtl.concatenated_segments
FROM ahl_schedule_materials schm,
mtl_system_items_vl mtl
WHERE schm.inventory_item_id = mtl.inventory_item_id
AND schm.organization_id = mtl.organization_id
--SKPATHAK :: Bug 8429732 :: 17-APR-2009
--Commented out the condition (requested_quantity <> 0)
/*AND schm.requested_quantity <> 0*/
AND NVL(schm.status, 'X') <> 'IN-SERVICE' --Added by sowsubra for Issue 105
AND schm.visit_id = C_VISIT_ID;
PROCEDURE Unschedule_deleted_materials (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_deleted_matrl_tbl IN AHL_LTP_MATRL_AVAL_PUB.Planned_Matrl_Tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR Error_Message_Cur(c_error_code IN NUMBER) IS
SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
AND lookup_code = C_Error_Code;
SELECT visit_number||visit_task_number Order_Number
FROM ahl_visit_tasks_v
WHERE visit_task_id = c_visit_task_id;
SELECT instance_id
FROM MRP_AP_APPS_INSTANCES;
SELECT NVL(ATP_FLAG, 'N') from mtl_system_items
WHERE INVENTORY_ITEM_ID = c_item_id
AND ORGANIZATION_ID = c_org_id;
l_api_name CONSTANT VARCHAR2(30) := 'Unschedule_deleted_materials';
'At the start of PL/SQL procedure. Number of Records: ' || p_deleted_matrl_tbl.COUNT);
SAVEPOINT Unschedule_deleted_materials;
SELECT MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
INTO l_session_id FROM DUAL;
FOR i IN p_deleted_matrl_tbl.FIRST .. p_deleted_matrl_tbl.LAST
LOOP
--
IF p_deleted_matrl_tbl.EXISTS(i) THEN
-- Begin changes by surrkuma on 14-JUL-2010 for the bug 9901811
-- If there is no ATP Instance, but an ATP'able item exists, raise an exception
IF (l_atp_instance_flag = FALSE) THEN
OPEN Get_Item_ATP_Flag(p_deleted_matrl_tbl(i).inventory_item_id, p_deleted_matrl_tbl(i).organization_id);
l_atp_table.inventory_item_id(i) := p_deleted_matrl_tbl(i).inventory_item_id;
l_atp_table.inventory_item_name(i) := p_deleted_matrl_tbl(i).item_description;
l_atp_table.source_organization_id(i) := p_deleted_matrl_tbl(i).organization_id;
l_atp_table.identifier(i) := p_deleted_matrl_tbl(i).schedule_material_id;
l_atp_table.quantity_UOM(i) := p_deleted_matrl_tbl(i).primary_uom_code;
l_atp_table.requested_ship_date(i) := p_deleted_matrl_tbl(i).requested_date;
l_atp_table.Old_Source_Organization_Id(i) := p_deleted_matrl_tbl(i).organization_id;--Rescheduling
OPEN Order_Number_Cur(p_deleted_matrl_tbl(i).visit_task_id);
ROLLBACK TO Unschedule_deleted_materials;
ROLLBACK TO Unschedule_deleted_materials;
ROLLBACK TO Unschedule_deleted_materials;
p_procedure_name => 'Unschedule_deleted_materials',
p_error_text => SUBSTR(SQLERRM,1,240));
END Unschedule_deleted_materials;