[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* SELECT job_number,
organization_id,
job_status_code
FROM ahl_workorders_v
WHERE workorder_id = p_workorder_id;
SELECT 'x'
FROM ahl_workorders
WHERE workorder_id = p_workorder_id;
SELECT item_group_id
FROM ahl_item_groups_b
WHERE name = c_item_group_name;
SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE inventory_item_id = c_inventory_item_id
AND organization_id = c_organization_id;
SELECT inventory_item_id
FROM mtl_system_items_kfv
WHERE concatenated_segments = c_item_number
AND organization_id = c_organization_id;
SELECT status_id
FROM mtl_material_statuses
WHERE status_id = c_status_id;
SELECT status_id
FROM mtl_material_statuses
WHERE status_code = c_status_code;
SELECT OFFC.inventory_item_id off_inv_item_id,
OFFI.concatenated_segments off_item_number,
OFFC.instance_number off_instance_number,
OFFC.serial_number off_serial_number,
OFFC.lot_number off_lot_number,
-- OFFC.quantity off_quantity,
PC.removed_quantity off_quantity,
OFFC.unit_of_measure off_uom,
ONC.inventory_item_id on_inv_item_id,
ONI.concatenated_segments on_item_number,
ONC.instance_number on_instance_number,
ONC.serial_number on_serial_number,
ONC.lot_number on_lot_number,
-- ONC.quantity on_quantity,
PC.installed_quantity on_quantity,
ONC.unit_of_measure on_uom
FROM ahl_part_changes_v PC,
csi_item_instances OFFC,
mtl_system_items_kfv OFFI,
csi_item_instances ONC,
mtl_system_items_kfv ONI
WHERE PC.part_change_id = c_part_change_id
AND PC.removed_instance_id = OFFC.instance_id (+)
AND PC.installed_instance_id = ONC.instance_id (+)
AND OFFC.inventory_item_id = OFFI.inventory_item_id (+)
AND OFFC.inv_master_organization_id = OFFI.organization_id (+)
AND ONC.inventory_item_id = ONI.inventory_item_id (+)
AND ONC.inv_master_organization_id = ONI.organization_id (+);
SELECT MT.transaction_type_id transaction_type_id,
DM.uom uom,
sum(DM.quantity) quantity,
count(MT.transaction_type_id) rec_no
FROM ahl_workorder_mtl_txns MT,
ahl_prd_disp_mtl_txns DM
WHERE DM.disposition_id = c_disposition_id
AND MT.workorder_mtl_txn_id = DM.workorder_mtl_txn_id
GROUP BY MT.transaction_type_id, DM.uom
HAVING MT.transaction_type_id IN (G_ISSUE_ID, G_RETURN_ID);
SELECT MT.transaction_type_id transaction_type_id,
MT.inventory_item_id inv_item_id,
IV.concatenated_segments item_number,
MT.serial_number serial_number,
MT.lot_number lot_number,
DM.quantity quantity,
DM.uom uom
FROM ahl_workorder_mtl_txns MT,
ahl_prd_disp_mtl_txns DM,
mtl_system_items_kfv IV
WHERE DM.disposition_id = c_disposition_id
AND MT.workorder_mtl_txn_id = DM.workorder_mtl_txn_id
AND MT.transaction_type_id = c_txn_type_id
AND MT.inventory_item_id = IV.inventory_item_id
AND MT.organization_id = IV.organization_id;
SELECT path_pos_common_id INTO l_path_pos_common_id
FROM ahl_mc_path_positions
WHERE path_position_id = p_disp_filter_rec.path_position_id;
l_sql_str := 'SELECT disposition_id, part_change_id, path_position_id, position_reference, ';
l_count_query := 'SELECT COUNT(*) FROM (' || l_sql_str || ')';
SELECT quantity
INTO x_disp_list_tbl(i).on_quantity
FROM ahl_part_changes
WHERE part_change_id = x_disp_list_tbl(i).part_change_id;
FND_MSG_PUB.delete_msg(l_msg_index);
FND_MSG_PUB.delete_msg(l_msg_index);
SELECT csi.instance_id
FROM csi_item_instances csi, csi_ii_relationships rel
WHERE csi.instance_id = rel.subject_id
AND REL.object_id = p_parent_id
AND REL.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(nvl(REL.ACTIVE_START_DATE, sysdate-1)) <= TRUNC(sysdate)
AND TRUNC(nvl(REL.ACTIVE_END_DATE, sysdate+1)) > TRUNC(sysdate)
AND TO_NUMBER(REL.position_reference) = p_relationship_id;
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
disp.item_number,
disp.inventory_item_id,
disp.organization_id,
disp.instance_number,
disp.instance_id,
disp.item_group_id,
disp.item_group_name,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
disp.condition_code,
disp.immediate_disposition_code,
disp.immediate_type,
disp.secondary_disposition_code,
disp.secondary_type,
disp.status_code,
disp.status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
disp.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INC.SUMMARY
FROM AHL_PRD_DISPOSITIONS_V disp, AHL_MC_PATH_POSITIONS pp,
CS_INCIDENTS_ALL_VL INC, CS_INCIDENT_SEVERITIES_VL SEV,
FND_LOOKUP_VALUES_VL PCODE
WHERE disp.path_pos_common_id = pp.path_pos_common_id
AND pp.path_position_id = p_path_position_id
AND disp.part_change_id IS NULL
AND disp.workorder_id = p_workorder_id
AND nvl(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND disp.instance_id = p_instance_id
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE;
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
MTL.CONCATENATED_SEGMENTS ITEM_NUMBER,
disp.inventory_item_id,
disp.organization_id,
CSI.INSTANCE_NUMBER,
disp.instance_id,
disp.item_group_id,
GRP.NAME ITEM_GROUP_NAME,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
cond.STATUS_CODE CONDITION_CODE,
disp.immediate_disposition_code,
FND1.MEANING immediate_type,
disp.secondary_disposition_code,
FND2.MEANING secondary_type,
disp.status_code,
FND3.MEANING status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
displ.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INCL.SUMMARY
FROM AHL_PRD_DISPOSITIONS_b disp,
AHL_PRD_DISPOSITIONS_tl displ,
AHL_MC_PATH_POSITIONS pp,
MTL_SYSTEM_ITEMS_KFV MTL,
CSI_ITEM_INSTANCES CSI,
AHL_ITEM_GROUPS_B GRP,
MTL_MATERIAL_STATUSES_TL COND,
CS_INCIDENTS_ALL_B INC,
CS_INCIDENTS_ALL_TL INCL,
CS_INCIDENT_SEVERITIES_TL SEV,
FND_LOOKUP_VALUES PCODE,
FND_LOOKUP_VALUES FND1,
FND_LOOKUP_VALUES FND2,
FND_LOOKUP_VALUES FND3
WHERE pp.path_pos_common_id IN (
SELECT PATH_POS_COMMON_ID
FROM AHL_MC_PATH_POSITIONS
WHERE PATH_POSITION_ID = p_path_position_id
)
AND disp.path_position_id = pp.path_position_id
AND disp.workorder_id =p_workorder_id
AND disp.instance_id = p_instance_id
AND disp.part_change_id IS NULL
AND nvl(disp.immediate_disposition_code,'NULL') <> 'NOT_RECEIVED'
AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND SEV.language(+) = USERENV('LANG')
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
AND PCODE.language(+) = USERENV('LANG')
AND disp.disposition_id = displ.disposition_id
AND displ.language = USERENV('LANG')
AND disp.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND disp.ORGANIZATION_ID = MTL.organization_id (+)
AND disp.INSTANCE_ID = CSI.INSTANCE_ID (+)
AND disp.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
AND disp.condition_id = COND.status_id (+)
AND COND.language(+) = USERENV('LANG')
AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
AND disp.immediate_disposition_code = FND1.LOOKUP_CODE (+)
AND FND1.LANGUAGE(+) = USERENV('LANG')
AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
AND disp.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
AND FND2.LANGUAGE(+) = USERENV('LANG')
AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
AND disp.STATUS_CODE = FND3.LOOKUP_CODE (+)
AND FND3.LANGUAGE(+) = USERENV('LANG')
AND INCL.INCIDENT_ID (+) = INC.INCIDENT_ID
AND INCL.language(+) = USERENV('LANG');
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
disp.item_number,
disp.inventory_item_id,
disp.organization_id,
disp.instance_number,
disp.instance_id,
disp.item_group_id,
disp.item_group_name,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
disp.condition_code,
disp.immediate_disposition_code,
disp.immediate_type,
disp.secondary_disposition_code,
disp.secondary_type,
disp.status_code,
disp.status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
disp.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INC.SUMMARY
FROM AHL_PRD_DISPOSITIONS_V disp, AHL_MC_PATH_POSITIONS pp,
CS_INCIDENTS_ALL_VL INC,
CS_INCIDENT_SEVERITIES_VL SEV, FND_LOOKUP_VALUES_VL PCODE
WHERE disp.path_pos_common_id =pp.path_pos_common_id
AND pp.path_position_id = p_path_position_id
AND disp.part_change_id IS NULL
AND disp.workorder_id = p_workorder_id
AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND disp.immediate_disposition_code = 'NOT_RECEIVED'
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE;
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
MTL.CONCATENATED_SEGMENTS ITEM_NUMBER,
disp.inventory_item_id,
disp.organization_id,
CSI.INSTANCE_NUMBER,
disp.instance_id,
disp.item_group_id,
GRP.NAME ITEM_GROUP_NAME,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
cond.STATUS_CODE CONDITION_CODE,
disp.immediate_disposition_code,
FND1.MEANING immediate_type,
disp.secondary_disposition_code,
FND2.MEANING secondary_type,
disp.status_code,
FND3.MEANING status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
displ.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INCL.SUMMARY
FROM AHL_PRD_DISPOSITIONS_b disp,
AHL_PRD_DISPOSITIONS_tl displ,
AHL_MC_PATH_POSITIONS pp,
CS_INCIDENTS_ALL_B INC,
CS_INCIDENTS_ALL_TL INCL,
CS_INCIDENT_SEVERITIES_TL SEV,
FND_LOOKUP_VALUES PCODE,
MTL_SYSTEM_ITEMS_KFV MTL,
CSI_ITEM_INSTANCES CSI,
AHL_ITEM_GROUPS_B GRP,
MTL_MATERIAL_STATUSES_TL COND,
FND_LOOKUP_VALUES FND1,
FND_LOOKUP_VALUES FND2,
FND_LOOKUP_VALUES FND3
WHERE pp.path_pos_common_id IN (
SELECT PATH_POS_COMMON_ID
FROM AHL_MC_PATH_POSITIONS
WHERE PATH_POSITION_ID = p_path_position_id
)
AND disp.path_position_id = pp.path_position_id
AND disp.part_change_id IS NULL
AND disp.workorder_id = p_workorder_id
AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND disp.immediate_disposition_code = 'NOT_RECEIVED'
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
AND PCODE.LANGUAGE(+) = USERENV('LANG')
AND disp.disposition_id = displ.disposition_id
AND displ.LANGUAGE = USERENV('LANG')
AND disp.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND disp.ORGANIZATION_ID = MTL.organization_id (+)
AND disp.INSTANCE_ID = CSI.INSTANCE_ID (+)
AND disp.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
AND disp.condition_id = COND.status_id (+)
AND COND.LANGUAGE(+) = USERENV('LANG')
AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
AND disp.immediate_disposition_code = FND1.LOOKUP_CODE (+)
AND FND1.LANGUAGE(+) = USERENV('LANG')
AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
AND disp.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
AND FND2.LANGUAGE(+) = USERENV('LANG')
AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
AND disp.STATUS_CODE = FND3.LOOKUP_CODE (+)
AND FND3.LANGUAGE(+) = USERENV('LANG')
AND INCL.INCIDENT_ID (+) = INC.INCIDENT_ID
AND INCL.LANGUAGE(+) = USERENV('LANG');
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
disp.item_number,
disp.inventory_item_id,
disp.organization_id,
disp.instance_number,
disp.instance_id,
disp.item_group_id,
disp.item_group_name,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
disp.condition_code,
disp.immediate_disposition_code,
disp.immediate_type,
disp.secondary_disposition_code,
disp.secondary_type,
disp.status_code,
disp.status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
disp.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INC.SUMMARY
FROM AHL_PRD_DISPOSITIONS_V disp, AHL_MC_PATH_POSITIONS pp,
CS_INCIDENTS_ALL_VL INC,
CS_INCIDENT_SEVERITIES_VL SEV, FND_LOOKUP_VALUES_VL PCODE,
AHL_PART_CHANGES_V PC
WHERE disp.path_pos_common_id =pp.path_pos_common_id
AND pp.path_position_id = p_path_position_id
AND disp.part_change_id IS NOT NULL
AND disp.workorder_id = p_workorder_id
AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
AND disp.part_change_id = PC.part_change_id
AND PC.removed_instance_id IS NOT NULL
AND PC.installed_part_change_id IS NULL;
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
MTL.CONCATENATED_SEGMENTS ITEM_NUMBER,
disp.inventory_item_id,
disp.organization_id,
CSI.INSTANCE_NUMBER,
disp.instance_id,
disp.item_group_id,
GRP.NAME ITEM_GROUP_NAME,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
COND.STATUS_CODE CONDITION_CODE,
disp.immediate_disposition_code,
FND1.MEANING immediate_type,
disp.secondary_disposition_code,
FND2.MEANING secondary_type,
disp.status_code,
FND3.MEANING status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
displ.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INCL.SUMMARY
FROM AHL_PRD_DISPOSITIONS_b disp,
AHL_PRD_DISPOSITIONS_tl displ,
AHL_MC_PATH_POSITIONS pp,
FND_LOOKUP_VALUES FND1,
FND_LOOKUP_VALUES FND2,
FND_LOOKUP_VALUES FND3,
MTL_MATERIAL_STATUSES_TL COND,
AHL_ITEM_GROUPS_B GRP,
MTL_SYSTEM_ITEMS_KFV MTL,
CSI_ITEM_INSTANCES CSI,
CS_INCIDENTS_ALL_B INC,
CS_INCIDENTS_ALL_TL INCL,
CS_INCIDENT_SEVERITIES_TL SEV,
FND_LOOKUP_VALUES PCODE,
AHL_PART_CHANGES_V PC
WHERE pp.path_pos_common_id IN (
SELECT PATH_POS_COMMON_ID
FROM AHL_MC_PATH_POSITIONS
WHERE PATH_POSITION_ID = p_path_position_id
)
AND disp.path_position_id = pp.path_position_id
AND disp.disposition_id = displ.disposition_id
AND displ.language = USERENV('LANG')
AND disp.part_change_id IS NOT NULL
AND disp.workorder_id = p_workorder_id
AND (disp.status_code IS NULL OR disp.status_code NOT IN ('COMPLETE','TERMINATED'))
AND FND1.LOOKUP_TYPE (+)= 'AHL_IMMED_DISP_TYPE'
AND disp.immediate_disposition_code = FND1.LOOKUP_CODE (+)
AND FND1.LANGUAGE(+) = USERENV('LANG')
AND FND2.LOOKUP_TYPE (+)= 'AHL_SECND_DISP_TYPE'
AND disp.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
AND FND2.LANGUAGE(+) = USERENV('LANG')
AND FND3.LOOKUP_TYPE (+)= 'AHL_DISP_STATUS'
AND disp.STATUS_CODE = FND3.LOOKUP_CODE (+)
AND FND3.LANGUAGE(+) = USERENV('LANG')
AND disp.condition_id = COND.status_id (+)
AND COND.language(+) = USERENV('LANG')
AND disp.ITEM_GROUP_ID = GRP.ITEM_GROUP_ID (+)
AND disp.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID (+)
AND disp.ORGANIZATION_ID = MTL.organization_id (+)
AND disp.INSTANCE_ID = CSI.INSTANCE_ID (+)
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND SEV.language(+) = USERENV('LANG')
AND INCL.INCIDENT_ID (+) = INC.INCIDENT_ID
AND INCL.language(+) = USERENV('LANG')
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE
AND PCODE.language(+) = USERENV('LANG')
AND disp.part_change_id = PC.part_change_id
AND PC.removed_instance_id IS NOT NULL
AND PC.installed_part_change_id IS NULL;
SELECT disp.disposition_id,
disp.object_version_number,
disp.last_update_date,
disp.last_updated_by,
disp.creation_date,
disp.created_by,
disp.last_update_login,
disp.workorder_id,
disp.part_change_id,
disp.path_position_id,
disp.item_number,
disp.inventory_item_id,
disp.organization_id,
disp.instance_number,
disp.instance_id,
disp.item_group_id,
disp.item_group_name,
disp.serial_number,
disp.lot_number,
disp.quantity,
disp.uom,
disp.condition_id,
disp.condition_code,
disp.immediate_disposition_code,
disp.immediate_type,
disp.secondary_disposition_code,
disp.secondary_type,
disp.status_code,
disp.status,
disp.collection_id,
disp.primary_service_request_id,
disp.non_routine_workorder_id,
disp.comments,
INC.INCIDENT_SEVERITY_ID SEVERITY_ID,
SEV.NAME SEVERITY_NAME,
INC.PROBLEM_CODE,
PCODE.MEANING PROBLEM_MEANING,
INC.SUMMARY
FROM AHL_PRD_DISPOSITIONS_V disp, CS_INCIDENTS_ALL_VL INC,
CS_INCIDENT_SEVERITIES_VL SEV, FND_LOOKUP_VALUES_VL PCODE
WHERE disp.workorder_id = p_workorder_id
AND disp.instance_id = p_instance_id
AND (disp.status_code NOT IN ('COMPLETE', 'TERMINATED'))
AND disp.part_change_id IS NULL
AND disp.path_position_id IS NULL
AND INC.INCIDENT_ID (+) = disp.primary_service_request_id
AND SEV.INCIDENT_SEVERITY_ID (+) = INC.INCIDENT_SEVERITY_ID
AND PCODE.LOOKUP_TYPE (+) = 'REQUEST_PROBLEM_CODE'
AND PCODE.LOOKUP_CODE (+) = INC.PROBLEM_CODE;
x_disposition_rec.LAST_UPDATE_DATE := l_disp_rec.LAST_UPDATE_DATE;
x_disposition_rec.LAST_UPDATED_BY :=l_disp_rec.LAST_UPDATED_BY;
x_disposition_rec.LAST_UPDATE_LOGIN:=l_disp_rec.LAST_UPDATE_LOGIN;
/* SELECT *
FROM AHL_PRD_DISPOSITIONS_V
WHERE disposition_id = p_disp_id;
SELECT B.status_code,
B.immediate_disposition_code,
FND1.MEANING IMMEDIATE_TYPE,
B.secondary_disposition_code,
FND2.MEANING SECONDARY_TYPE,
B.part_change_id,
decode(B.instance_id, null, decode(B.path_position_id, null, 'N', 'Y'), 'Y') TRACKABLE_FLAG
FROM AHL_PRD_DISPOSITIONS_B B, FND_LOOKUPS FND1, FND_LOOKUPS FND2
WHERE FND1.LOOKUP_TYPE (+) = 'AHL_IMMED_DISP_TYPE'
AND B.immediate_disposition_code = FND1.LOOKUP_CODE (+)
AND FND2.LOOKUP_TYPE (+) = 'AHL_SECND_DISP_TYPE'
AND B.SECONDARY_DISPOSITION_CODE = FND2.LOOKUP_CODE (+)
AND B.disposition_id = p_disp_id;
SELECT lookup_code, meaning
FROM fnd_lookups
WHERE lookup_type = 'AHL_IMMED_DISP_TYPE'
AND Lookup_code <> 'NULL';
SELECT lookup_code, meaning
FROM fnd_lookups
WHERE lookup_type = 'AHL_SECND_DISP_TYPE'
AND Lookup_code <> 'NULL';
SELECT PRIMARY_SERVICE_REQUEST_ID
FROM AHL_PRD_DISPOSITIONS_B
WHERE DISPOSITION_ID = p_disposition_id;
SELECT disp.inventory_item_id, disp.item_number, disp.workorder_id,
vi.organization_id, disp.path_position_id, disp.quantity, disp.uom,
disp.wo_operation_id
FROM AHL_PRD_DISPOSITIONS_V disp, AHL_WORKORDERS wo, AHL_VISITS_B VI
--the organization_id in ahl_prd_dispostions_b is not necessary to be the same as the job org,
--especially when the disposition is for a non empty position(instance''s last_vld_org_id), but
--here we need the job organization.
WHERE disp.workorder_id = wo.workorder_id
and wo.visit_id = vi.visit_id
AND disp.disposition_id = p_disp_id
*/
select disp.inventory_item_id,
mtl.concatenated_segments item_number,
disp.workorder_id,
vi.organization_id,
disp.path_position_id,
disp.quantity,
disp.uom,
disp.wo_operation_id
from ahl_prd_dispositions_vl disp,
mtl_system_items_kfv mtl,
ahl_workorders wo,
ahl_visits_b vi
where disp.workorder_id = wo.workorder_id
and wo.visit_id = vi.visit_id
and disp.disposition_id = p_disp_id
AND disp.inventory_item_id = mtl.inventory_item_id(+)
AND disp.organization_id = mtl.organization_id (+);
SELECT disp.inventory_item_id, disp.item_number, disp.workorder_id,
wo.organization_id, disp.path_position_id, disp.quantity, disp.uom,
disp.wo_operation_id
FROM AHL_PRD_DISPOSITIONS_V disp, AHL_WORKORDERS_V wo
--the organization_id in ahl_prd_dispostions_b is not necessary to be the same as the job org,
--especially when the disposition is for a non empty position(instance's last_vld_org_id), but
--here we need the job organization.
WHERE disp.workorder_id = wo.workorder_id
AND disposition_id = p_disp_id;*/
SELECT rel.relationship_id
FROM AHL_MC_RELATIONSHIPS rel, AHL_MC_HEADERS_B mc, AHL_MC_PATH_POSITION_NODES node
WHERE rel.mc_header_id = mc.mc_header_id
AND node.position_key = rel.position_key
AND node.mc_id = mc.mc_id
AND nvl(node.version_number, mc.version_number) = mc.version_number
AND node.path_position_id = p_path_position_id
AND node.sequence = (select max(sequence)
from AHL_MC_PATH_POSITION_NODES
WHERE path_position_id = p_path_position_id)
ORDER by mc.version_number desc;
SELECT ia.inventory_item_id, mtl.concatenated_segments, ia.quantity, ia.uom_code
FROM AHL_ITEM_ASSOCIATIONS_B ia, MTL_SYSTEM_ITEMS_KFV mtl,
AHL_MC_RELATIONSHIPS rel, MTL_PARAMETERS morgs
WHERE ia.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = p_org_id --Make sure item is defined for p_org_id
AND ia.inventory_org_id = morgs.master_organization_id
AND morgs.organization_id = p_org_id
AND ia.item_group_id = rel.item_group_id
AND rel.relationship_id = p_relationship_id
AND ia.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER by ia.priority asc;
SELECT ia.inventory_item_id, mtl.concatenated_segments, ia.quantity, ia.uom_code
FROM AHL_ITEM_ASSOCIATIONS_B ia, MTL_SYSTEM_ITEMS_KFV mtl,
AHL_MC_RELATIONSHIPS rel, AHL_MC_CONFIG_RELATIONS crel, MTL_PARAMETERS morgs
WHERE ia.inventory_item_id = mtl.inventory_item_id
AND mtl.organization_id = p_org_id --Make sure item is defined for p_org_id
AND ia.inventory_org_id = morgs.master_organization_id
AND morgs.organization_id = p_org_id
AND ia.item_group_id = rel.item_group_id
AND rel.mc_header_id = crel.mc_header_id
AND rel.parent_relationship_id IS NULL
AND crel.relationship_id = p_relationship_id
AND ia.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER by crel.priority asc, ia.priority asc;
SELECT workorder_operation_id
FROM AHL_WORKORDER_OPERATIONS
WHERE workorder_id = p_workorder_id
AND status_code <> G_WO_STATUS_COMPLETE
ORDER BY operation_sequence_num asc;
SELECT scheduled_material_id,
object_version_number,
requested_quantity,
uom,
inventory_item_id
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 requested_quantity <> 0
AND status = 'ACTIVE';
l_update_flag BOOLEAN := false;
l_update_flag := true;
IF (l_update_flag = true) THEN
FND_MESSAGE.Set_Name('AHL','AHL_PRD_DISP_MTL_REQ_UPDATED');
select 1 INTO l_dummy_num
from ahl_prd_dispositions_b
where disposition_id = p_disposition_id
and nvl(status_code, ' ') <> 'TERMINATED';
select 1 INTO l_dummy_num
from cs_incidents
where incident_id = p_service_request_id;
l_link_rec.program_update_date := sysdate;
SELECT VTS.INSTANCE_ID, VST.ITEM_INSTANCE_ID
FROM AHL_WORKORDERS WO, AHL_VISITS_B VST, AHL_VISIT_TASKS_B VTS
WHERE WO.WORKORDER_ID = p_workorder_id AND
VST.VISIT_ID = WO.VISIT_ID AND
VTS.VISIT_TASK_ID = WO.VISIT_TASK_ID;
SELECT ap.last_update_date, ac.creation_date
FROM ahl_prd_dispositions_b ap,
ahl_part_changes_v pc,
ahl_part_changes ac
WHERE ap.disposition_id = p_disposition_id
AND ap.part_change_id = pc.part_change_id
AND pc.installed_part_change_id = ac.part_change_id;
IF (get_timestamps%FOUND AND l_get_timestamps.last_update_date