[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select UC.MASTER_CONFIG_ID, MC.MC_ID, MC.VERSION_NUMBER
from AHL_UNIT_CONFIG_HEADERS UC, AHL_MC_HEADERS_B MC
where UC.CSI_ITEM_INSTANCE_ID = c_instance_id AND
MC.MC_HEADER_ID = UC.MASTER_CONFIG_ID;
select INVENTORY_ITEM_ID from CSI_ITEM_INSTANCES
where INSTANCE_ID = c_instance_id;
SELECT inventory_item_id, inventory_org_id
FROM ahl_item_associations_b
WHERE item_group_id = c_item_group_id
AND interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER BY priority;
select primary_uom_code from mtl_system_items
where inventory_item_id = p_inventory_item_id;
select primary_uom_code from mtl_system_items
where inventory_item_id = p_inventory_item_id and
organization_id = p_inventory_org_id;
select route_id from ahl_mr_routes
where mr_route_id = p_mr_route_id;
select 'x' from AHL_ROUTES_B
where route_id = c_route_id;
select 'x' from CSI_ITEM_INSTANCES
where instance_id = p_item_instance_id
and nvl(active_end_date, sysdate + 1) > sysdate;
SELECT RE.route_effectivity_id
FROM AHL_ROUTE_EFFECTIVITIES RE
WHERE RE.route_id = p_route_id
AND (RE.mc_header_id = NVL(p_mc_header_id, -1) -- Match MC Header Id first
OR (RE.mc_id = NVL(p_mc_id, -1) -- Match MC Id next
AND RE.mc_header_id IS NULL -- Added on 10/28/03 since Version specific also has stores the MC Id
AND NOT EXISTS (SELECT 'x' FROM AHL_ROUTE_EFFECTIVITIES RE1
WHERE RE1.route_id = p_route_id
AND RE1.mc_header_id = NVL(p_mc_header_id, -1))
)
);
SELECT RE.route_effectivity_id
FROM AHL_ROUTE_EFFECTIVITIES RE
WHERE RE.route_id = p_route_id
AND RE.inventory_item_id = p_inst_item_id; -- Match the inventory item id
SELECT ROM.RT_OPER_MATERIAL_ID,
ROM.INVENTORY_ITEM_ID,
ROM.INVENTORY_ORG_ID,
ROM.UOM_CODE,
ROM.QUANTITY,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
ITEM_GROUP_ID,
ITEM_COMP_DETAIL_ID,
POSITION_PATH_ID,
PP.PATH_POS_COMMON_ID,
PP.VER_SPEC_SCORE
FROM AHL_RT_OPER_MATERIALS ROM, AHL_MC_PATH_POSITIONS PP
WHERE OBJECT_ID in (NVL(c_mc_route_eff_id, -1), NVL(c_item_route_eff_id, -1)) AND
ROM.POSITION_PATH_ID = PP.PATH_POSITION_ID (+) AND
ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_DISPOSITION AND
((REPLACE_PERCENT = 100 AND p_request_type = G_REQ_TYPE_FORECAST) OR
(p_request_type = G_REQ_TYPE_PLANNED)
)
ORDER BY PATH_POS_COMMON_ID, VER_SPEC_SCORE DESC;
SELECT ICD.inventory_item_id, ICD.inventory_master_org_id, ICD.item_group_id,
DECODE(ICD.inventory_item_id, null, c_quantity, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(ICD.inventory_item_id, c_uom, c_quantity)) QUANTITY,
DECODE(ICD.inventory_item_id, null, c_uom, AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(ICD.inventory_item_id, inventory_master_org_id)) UOM
FROM AHL_ITEM_COMP_DETAILS ICD
WHERE ITEM_COMP_DETAIL_ID = c_item_comp_detail_id;
SELECT master_config_id
FROM AHL_UNIT_CONFIG_HEADERS
WHERE CSI_ITEM_INSTANCE_ID = c_pos_instance_id;
SELECT item_group_id
FROM AHL_MC_RELATIONSHIPS
WHERE mc_header_id = c_mc_header_id
AND parent_relationship_id is null;
SELECT CR.mc_header_id
FROM AHL_MC_CONFIG_RELATIONS CR, AHL_MC_HEADERS_B MC
WHERE CR.relationship_id = c_position_id
AND MC.mc_header_id = CR.mc_header_id
ORDER BY MC.name;
SELECT ROM.RT_OPER_MATERIAL_ID,
NULL AS ROUTE_OPERATION_ID,
ROM.INVENTORY_ITEM_ID,
ROM.INVENTORY_ORG_ID,
ROM.QUANTITY,
ROM.UOM_CODE,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_UOM_CODE,
ROM.ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID = p_route_id
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_ROUTE;
SELECT TO_NUMBER(NULL) AS RT_OPER_MATERIAL_ID,
TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
-- Aggregate item quantities across operations when forecasting
SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, --Total Primary Qty
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
-- support for Oracle 8
--NULL AS ITEM_GROUP_ID
TO_NUMBER(NULL) AS ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in (SELECT RO.operation_id
FROM ahl_operations_vl O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in (SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments =
O.concatenated_segments and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NOT NULL
AND p_request_type = G_REQ_TYPE_FORECAST
GROUP BY INVENTORY_ITEM_ID, INVENTORY_ORG_ID
UNION
-- Don't aggregate for Operation items when Firm Planning
SELECT RT_OPER_MATERIAL_ID,
OBJECT_ID AS ROUTE_OPERATION_ID,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, -- Primary Qty
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
--NULL AS ITEM_GROUP_ID
-- support for Oracle 8
TO_NUMBER(NULL) AS ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in (SELECT RO.operation_id
FROM ahl_operations_vl O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in (SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments =
O.concatenated_segments and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NOT NULL
AND p_request_type = G_REQ_TYPE_PLANNED
UNION
-- Item Group: No need to aggregate or convert to Primary UOM
SELECT RT_OPER_MATERIAL_ID,
OBJECT_ID AS ROUTE_OPERATION_ID,
--NULL AS INVENTORY_ITEM_ID,
--NULL AS INVENTORY_ORG_ID,
-- support for Oracle 8
TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
QUANTITY,
UOM_CODE,
ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in (SELECT RO.operation_id
FROM ahl_operations_vl O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in (SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments =
O.concatenated_segments and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NULL;*/
SELECT TO_NUMBER(NULL) AS RT_OPER_MATERIAL_ID,
TO_NUMBER(NULL) AS ROUTE_OPERATION_ID,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
-- Aggregate item quantities across operations when forecasting
SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS QUANTITY, /*Total Primary Qty */
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
TO_NUMBER(NULL) AS ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in ( SELECT RO.operation_id
FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in ( SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments = O.concatenated_segments
and trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NOT NULL
GROUP BY INVENTORY_ITEM_ID, INVENTORY_ORG_ID
UNION
SELECT RT_OPER_MATERIAL_ID,
OBJECT_ID AS ROUTE_OPERATION_ID,
TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
QUANTITY,
UOM_CODE,
ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in ( SELECT RO.operation_id
FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in (SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments = O.concatenated_segments
and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NULL;
SELECT RT_OPER_MATERIAL_ID,
OBJECT_ID AS ROUTE_OPERATION_ID,
INVENTORY_ITEM_ID,
INVENTORY_ORG_ID,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS QUANTITY, /* Primary Qty */
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS UOM_CODE,
TO_NUMBER(NULL) AS ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in ( SELECT RO.operation_id
FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id
and RO.route_id = p_route_id
and O.revision_status_code = 'COMPLETE'
and O.revision_number in ( SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments =O.concatenated_segments
and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NOT NULL
UNION
-- Item Group: No need to aggregate or convert to Primary UOM
SELECT RT_OPER_MATERIAL_ID,
OBJECT_ID AS ROUTE_OPERATION_ID,
TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
QUANTITY,
UOM_CODE,
ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in (SELECT RO.operation_id
FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in (SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments = O.concatenated_segments
and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NULL;
SELECT RT_OPER_MATERIAL_ID,
OBJECT_ID AS ROUTE_OPERATION_ID,
TO_NUMBER(NULL) AS INVENTORY_ITEM_ID,
TO_NUMBER(NULL) AS INVENTORY_ORG_ID,
QUANTITY,
UOM_CODE,
ITEM_GROUP_ID
FROM AHL_RT_OPER_MATERIALS ROM
WHERE OBJECT_ID in (SELECT RO.operation_id
FROM AHL_OPERATIONS_B_KFV O, ahl_route_operations RO
WHERE O.operation_id = RO.operation_id and
RO.route_id = p_route_id and
O.revision_status_code = 'COMPLETE' and
O.revision_number in (SELECT max(revision_number)
FROM ahl_operations_b_kfv
WHERE concatenated_segments = O.concatenated_segments
and
trunc(sysdate) between
trunc(start_date_active) and
trunc(NVL(end_date_active,SYSDATE+1)))
)
AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION
AND INVENTORY_ITEM_ID IS NULL;
SELECT position_key
FROM AHL_MC_PATH_POSITION_NODES
WHERE PATH_POSITION_ID = p_path_position_id AND
SEQUENCE = (SELECT MAX(SEQUENCE) FROM AHL_MC_PATH_POSITION_NODES
WHERE PATH_POSITION_ID = p_path_position_id);
SELECT relationship_id, item_group_id
FROM AHL_MC_RELATIONSHIPS
WHERE POSITION_KEY = c_pos_key AND
MC_HEADER_ID = c_mc_header_id;
SELECT master_config_id
FROM ahl_unit_config_headers
WHERE CSI_ITEM_INSTANCE_ID = c_unit_instance_id AND
NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE;
SELECT ACTIVE_START_DATE,
ACTIVE_END_DATE
FROM AHL_MC_RELATIONSHIPS
START WITH POSITION_KEY = c_start_pos_key AND
MC_HEADER_ID = c_mc_header_id
CONNECT BY RELATIONSHIP_ID = PRIOR PARENT_RELATIONSHIP_ID;
SELECT II.OBJECT_ID,
II.SUBJECT_ID,
REL.RELATIONSHIP_ID,
REL.ACTIVE_START_DATE,
REL.ACTIVE_END_DATE
FROM CSI_II_RELATIONSHIPS II, AHL_MC_RELATIONSHIPS REL
WHERE NVL(II.ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
NVL(II.ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
II.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF' AND
REL.RELATIONSHIP_ID =TO_NUMBER(II.POSITION_REFERENCE)
AND II.RELATIONSHIP_ID IN
(SELECT RELATIONSHIP_ID
FROM CSI_II_RELATIONSHIPS
START WITH SUBJECT_ID = c_start_instance_id AND
NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
CONNECT BY SUBJECT_ID = PRIOR OBJECT_ID AND
NVL(ACTIVE_START_DATE, SYSDATE - 1) <= SYSDATE AND
NVL(ACTIVE_END_DATE, SYSDATE + 1) > SYSDATE AND
RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF');
SELECT 'x'
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = p_item_instance_id
AND nvl(active_end_date, SYSDATE+1) > SYSDATE
AND nvl(active_start_date, SYSDATE) <= SYSDATE;
SELECT object_id
FROM csi_ii_relationships
WHERE object_id IN (SELECT csi_item_instance_id
FROM ahl_unit_config_headers
WHERE nvl(active_end_date, SYSDATE+1) > SYSDATE
AND nvl(active_start_date, SYSDATE) <= SYSDATE)
START WITH subject_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND nvl(active_start_date, SYSDATE) <= SYSDATE
AND nvl(active_end_date, SYSDATE+1) > SYSDATE
CONNECT BY subject_id = PRIOR object_id
AND relationship_type_code = 'COMPONENT-OF'
AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE)
ORDER BY LEVEL;