[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select UC.MASTER_CONFIG_ID, MC.MC_ID, MC.VERSION_NUMBER,UC.UNIT_CONFIG_HEADER_ID
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 'x' FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id
AND instance_id = p_item_instance_id
AND status_code = 'PLANNING';
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.MC_ID,
ROM.POSITION_KEY,
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,
TO_NUMBER(NULL) AS MC_ID,
TO_NUMBER(NULL) AS POSITION_KEY,
TO_NUMBER(NULL) AS QUANTITY,
NULL AS UOM_CODE,
-- Aggregate item quantities across operations when forecasting
SUM(AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY)) AS PRIMARY_QUANTITY, /*Total Primary Qty */
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_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
-- manisaga : Bug# 9726667 : 22-Jun-2010
-- added the below condition to fetch the latest revision of
-- operations with only 'complete' status
and revision_status_code = 'COMPLETE'
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,
MC_ID,
POSITION_KEY,
QUANTITY,
UOM_CODE,
QUANTITY AS PRIMARY_QUANTITY,
UOM_CODE AS PRIMARY_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
-- manisaga : Bug# 9726667 : 22-Jun-2010
and revision_status_code = 'COMPLETE'
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,
MC_ID,
POSITION_KEY,
QUANTITY,
UOM_CODE,
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM_Qty(INVENTORY_ITEM_ID, UOM_CODE, QUANTITY) AS PRIMARY_QUANTITY, /* Primary Qty */
AHL_LTP_MTL_REQ_PVT.Get_Primary_UOM(INVENTORY_ITEM_ID, INVENTORY_ORG_ID) AS PRIMARY_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
-- manisaga : Bug# 9726667 : 22-Jun-2010
and revision_status_code = 'COMPLETE'
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,
MC_ID,
POSITION_KEY,
QUANTITY,
UOM_CODE,
QUANTITY AS PRIMARY_QUANTITY,
UOM_CODE AS PRIMARY_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
-- manisaga : Bug# 9726667 : 22-Jun-2010
and revision_status_code = 'COMPLETE'
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,
MC_ID,
POSITION_KEY,
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
-- manisaga : Bug# 9726667 : 22-Jun-2010
and revision_status_code = 'COMPLETE'
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 ROM.RT_OPER_MATERIAL_ID
FROM AHL_RT_OPER_MATERIALS ROM, AHL_ROUTE_OPERATIONS RO
WHERE ROM.OBJECT_ID = RO.operation_id
AND RO.route_id = p_route_id
AND ROM.ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION;
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;
SELECT relationship_id,
item_group_id,
quantity,
uom_code
FROM ahl_mc_relationships
WHERE mc_header_id = c_mc_header_id
AND position_key = c_position_key;
SELECT DISTINCT rule_id,
rule_sequence
FROM (
SELECT SB.rule_id,
SB.rule_sequence
FROM ahl_sb_position_rules SB,
ahl_unit_config_headers UC
WHERE UC.unit_config_header_id = c_uc_header_id
AND SB.mc_header_id = UC.master_config_id
-- take only the rules which have MRs accomplished on the unit
AND EXISTS (SELECT 'X'
FROM ahl_unit_effectivities_b UE
WHERE UE.mr_header_id = SB.mr_header_id
AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
AND UE.accomplished_date IS NOT NULL)
-- take only the MAX sequence rules for a position, among the accomplished MRs
AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
FROM ahl_sb_position_rules SB2
WHERE SB2.relationship_id = SB.relationship_id
AND EXISTS (SELECT 'X'
FROM ahl_unit_effectivities_b UE
WHERE UE.mr_header_id = SB2.mr_header_id
AND UE.csi_item_instance_id = UC.csi_item_instance_id
AND UE.accomplished_date IS NOT NULL))
AND SB.relationship_id = c_relationship_id
AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
UNION ALL
SELECT SB.rule_id,
SB.rule_sequence
FROM AHL_SB_POSITION_RULES SB,
AHL_VISIT_TASKS_B VT,
AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.unit_config_header_id = c_uc_header_id
AND SB.mc_header_id = UC.master_config_id
-- take only the rules which have MRs attached to the visit
AND VT.visit_id = c_visit_id
AND VT.mr_id = SB.mr_header_id
AND VT.status_code NOT IN ('CANCELLED', 'DELETED')
-- take only the MAX sequence rules for a position
AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
FROM AHL_SB_POSITION_RULES SB2
WHERE SB2.relationship_id = SB.relationship_id
AND SB2.mr_header_id = SB.mr_header_id)
AND SB.relationship_id = c_relationship_id
AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
) ORDER BY rule_sequence DESC;
SELECT mcr.item_group_id
FROM ahl_mc_relationships mcr, ahl_mc_config_relations smc
WHERE mcr.mc_header_id = smc.mc_header_id
AND mcr.parent_relationship_id is null
AND smc.relationship_id = c_relationship_id
ORDER BY smc.priority;
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 rel.relationship_id, NVL(c_quantity, mcrs.object_attribute1)
FROM ahl_mc_path_position_nodes mcpp,
ahl_mc_rule_statements mcrs,
ahl_mc_headers_b mch,
ahl_mc_relationships rel,
ahl_mc_rules_b mcr
WHERE mch.mc_header_id = c_mc_header_id
AND mcpp.mc_id = mch.mc_id
AND mch.version_number = NVL(mcpp.version_number, mch.version_number)
AND mcpp.position_key = c_position_key
AND mcrs.subject_id = mcpp.path_position_id
AND mcrs.subject_type = 'POSITION'
AND mcrs.operator IN ('MUST_HAVE', 'HAVE')
AND mcrs.object_type = 'TOT_CHILD_QUANTITY'
AND rel.mc_header_id = c_mc_header_id
AND rel.position_key = mcpp.position_key
AND mch.config_status_code = 'COMPLETE'
AND NVL(rel.active_end_date, SYSDATE + 1) > SYSDATE
AND mcr.rule_id = mcrs.rule_id
AND mcr.rule_type_code = 'MANDATORY'
AND NVL(mcr.active_start_date, SYSDATE - 1) < SYSDATE
AND NVL(mcr.active_end_date, SYSDATE + 1) > SYSDATE;
SELECT mcr.position_key
FROM ahl_mc_relationships mcr
WHERE mcr.parent_relationship_id = c_relationship_id
AND NVL(mcr.active_end_date, SYSDATE + 1) > SYSDATE
ORDER BY display_order;
SELECT ia.inventory_item_id, NVL(p_visit_org_id, ia.inventory_org_id), ia.quantity, ia.uom_code
FROM ahl_item_associations_b ia, mtl_system_items_b msi
WHERE ia.item_group_id = p_item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = NVL(p_visit_org_id, ia.inventory_org_id)
ORDER BY ia.priority;
SELECT ia.inventory_item_id, NVL(p_visit_org_id, ia.inventory_org_id), ia.quantity, ia.uom_code
FROM ahl_item_associations_b ia, mtl_system_items_b msi
WHERE ia.item_group_id = p_item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = NVL(p_visit_org_id, ia.inventory_org_id)
AND ia.item_association_id IN (SELECT item_association_id
FROM ahl_sb_rule_items
WHERE rule_id = p_rule_id
AND item_group_id = p_item_group_id)
ORDER BY ia.priority;
SELECT asm.mc_header_id,
asm.position_key,
asm.inventory_item_id,
asm.visit_id,
asm.visit_task_id,
asm.item_group_id,
asm.organization_id,
asm.relationship_id,
vt.instance_id
FROM ahl_schedule_materials asm,
ahl_visit_tasks_b vt
WHERE asm.scheduled_material_id = c_asm_id
AND vt.visit_task_id = asm.visit_task_id;
SELECT unit_config_header_id uc_header_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id;
SELECT DISTINCT rule_id,
rule_sequence
FROM (
SELECT SB.rule_id,
SB.rule_sequence
FROM ahl_sb_position_rules SB,
ahl_unit_config_headers UC
WHERE UC.unit_config_header_id = c_uc_header_id
AND SB.mc_header_id = UC.master_config_id
-- take only the rules which have MRs accomplished on the unit
AND EXISTS (SELECT 'X'
FROM ahl_unit_effectivities_b UE
WHERE UE.mr_header_id = SB.mr_header_id
AND UE.csi_item_instance_id = UC.csi_item_instance_id -- SB MRs are effective on root node
AND UE.accomplished_date IS NOT NULL)
-- take only the MAX sequence rules for a position, among the accomplished MRs
AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
FROM ahl_sb_position_rules SB2
WHERE SB2.relationship_id = SB.relationship_id
AND EXISTS (SELECT 'X'
FROM ahl_unit_effectivities_b UE
WHERE UE.mr_header_id = SB2.mr_header_id
AND UE.csi_item_instance_id = UC.csi_item_instance_id
AND UE.accomplished_date IS NOT NULL))
AND SB.relationship_id = c_relationship_id
AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
UNION ALL
SELECT SB.rule_id,
SB.rule_sequence
FROM AHL_SB_POSITION_RULES SB,
AHL_VISIT_TASKS_B VT,
AHL_UNIT_CONFIG_HEADERS UC
WHERE UC.unit_config_header_id = c_uc_header_id
AND SB.mc_header_id = UC.master_config_id
-- take only the rules which have MRs attached to the visit
AND VT.visit_id = c_visit_id
AND VT.mr_id = SB.mr_header_id
AND VT.status_code NOT IN ('CANCELLED', 'DELETED')
-- take only the MAX sequence rules for a position
AND SB.rule_sequence = (SELECT MAX(SB2.rule_sequence)
FROM AHL_SB_POSITION_RULES SB2
WHERE SB2.relationship_id = SB.relationship_id
AND SB2.mr_header_id = SB.mr_header_id)
AND SB.relationship_id = c_relationship_id
AND TRUNC(NVL(SB.active_start_date, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(SB.active_end_date, SYSDATE+1)) > TRUNC(SYSDATE)
) ORDER BY rule_sequence DESC;
SELECT ia.inventory_item_id
FROM ahl_item_associations_b ia,
mtl_system_items_b msi
WHERE ia.item_group_id = c_item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = c_organization_id
AND ia.inventory_item_id <> nvl(c_inventory_item_id, -1)
ORDER BY ia.priority;
SELECT ia.inventory_item_id,
smc.priority smc_priority,
ia.priority item_priority
FROM ahl_item_associations_b ia,
mtl_system_items_b msi,
ahl_mc_relationships mcr,
ahl_mc_config_relations smc
WHERE mcr.mc_header_id = smc.mc_header_id
AND mcr.parent_relationship_id is null
AND smc.relationship_id = c_relationship_id
AND ia.item_group_id = mcr.item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = c_organization_id
AND ia.inventory_item_id <> nvl(c_inventory_item_id,-1)
UNION
SELECT ia.inventory_item_id,
TO_NUMBER(NULL) AS smc_priority,
ia.priority item_priority
FROM ahl_item_associations_b ia,
mtl_system_items_b msi,
ahl_mc_relationships mcr
WHERE mcr.relationship_id = c_relationship_id
AND ia.item_group_id = mcr.item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = c_organization_id
AND ia.inventory_item_id <> nvl(c_inventory_item_id,-1)
order by smc_priority, item_priority;
SELECT ia.inventory_item_id,
smc.priority smc_priority,
ia.priority item_priority
FROM ahl_item_associations_b ia,
mtl_system_items_b msi,
ahl_mc_relationships mcr,
ahl_mc_config_relations smc
WHERE mcr.mc_header_id = smc.mc_header_id
AND mcr.parent_relationship_id is null
AND smc.relationship_id = c_relationship_id
AND ia.item_group_id = mcr.item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = c_organization_id
AND ia.inventory_item_id <> nvl(c_inventory_item_id, -1)
AND ia.item_association_id IN (SELECT item_association_id
FROM ahl_sb_rule_items
WHERE rule_id = c_sb_rule_id
AND item_group_id = mcr.item_group_id)
UNION
SELECT ia.inventory_item_id,
TO_NUMBER(NULL) AS smc_priority,
ia.priority item_priority
FROM ahl_item_associations_b ia,
mtl_system_items_b msi,
ahl_mc_relationships mcr
WHERE mcr.relationship_id = c_relationship_id
AND ia.item_group_id = mcr.item_group_id
AND ia.interchange_type_code = '2-WAY INTERCHANGEABLE'
AND ia.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = c_organization_id
AND ia.inventory_item_id <> nvl(c_inventory_item_id, -1)
AND ia.item_association_id IN (SELECT item_association_id
FROM ahl_sb_rule_items
WHERE rule_id = c_sb_rule_id
AND item_group_id = mcr.item_group_id)
order by smc_priority, item_priority;