The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_reading_id(p_utilization_rec IN utilization_rec_type);
PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type);
PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type);
PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
p_based_on IN VARCHAR2);
PROCEDURE update_utilization(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_utilization_tbl IN AHL_UC_UTILIZATION_PVT.utilization_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_utilization';
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Utilization';
SAVEPOINT update_utilization;
'About to call update_reading_all for record ' || i ||
', l_based_on = ' || l_based_on);
update_reading_all(l_utilization_rec, l_based_on);
'After successful completion of update_reading_all calls for all records in table.');
ROLLBACK to update_utilization;
ROLLBACK to update_utilization;
ROLLBACK to update_utilization;
p_procedure_name => 'update_utilization',
p_error_text => SQLERRM);
END update_utilization;
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = c_org_code;
SELECT inventory_item_id
FROM ahl_mtl_items_ou_v
WHERE concatenated_segments = c_item_number
AND inventory_org_id = c_inv_organization_id;
SELECT instance_id
FROM csi_item_instances
WHERE instance_number = c_instance_number;
SELECT location_type_code
FROM csi_item_instances
WHERE instance_id = c_instance_id
AND TRUNC(sysdate) < TRUNC(NVL(active_end_date, sysdate+1));
SELECT cgrp.source_object_id
FROM cs_counters ctr, cs_counter_groups cgrp
WHERE cgrp.counter_group_id = ctr.counter_group_id
AND cgrp.source_object_code = 'CP'
AND ctr.counter_id = c_counter_id
AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
AND trunc(sysdate) < trunc(nvl(ctr.end_date_active,sysdate+1));
SELECT instance_id,
instance_usage_code,
active_start_date,
active_end_date
FROM csi_item_instances csi
WHERE inventory_item_id = c_inventory_item_id
AND last_vld_organization_id = c_organization_id
AND serial_number = c_serial_number;
SELECT 'X'
FROM mtl_units_of_measure_vl
WHERE uom_code = c_uom_code;
SELECT 'X'
FROM cs_counters ctr
WHERE ctr.name = c_counter_name
AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
AND trunc(sysdate) < trunc(nvl(ctr.end_date_active,sysdate+1));
SELECT unit_config_header_id
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = c_instance_id
AND trunc(sysdate) >= trunc(nvl(active_start_date,sysdate))
AND trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1));
PROCEDURE update_reading_id(p_utilization_rec IN utilization_rec_type) IS
-- Get current counter reading values based on the counter_id
CURSOR get_current_value_id(c_counter_id NUMBER) IS
-- Changed by jaramana on 03-DEC-2008 for bug 7426643 (FP of 7263702)
-- To take advantage of the fix made in Counters bug 7561677 (FP of 7374316)
-- and also to ignore disabled counter readings
-- SATHAPLI::Bug 8765956, 07-Aug-2009, re-introducing NVL usage.
-- NOTE: NVL() should be put for the entire inner SELECT, as it should take effect even if this inner SELECT doesn't
-- fetch any rows.
SELECT NVL((select ccr.net_reading
from csi_counter_readings ccr
where ccr.counter_value_id = c.ctr_val_max_seq_no), 0) counter_reading,
DEFAULTED_GROUP_ID counter_group_id
FROM CSI_COUNTERS_B C
WHERE counter_id = c_counter_id
-- Changes by jaramana on 28-DEC-2009 for bug 9229943
-- Lock the CSI_COUNTERS_B row to prevent incorrect updates
FOR UPDATE OF c.ctr_val_max_seq_no;
select 'Y' from CSI_COUNTERS_B
where counter_id = c_counter_id
and reading_type = 2;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Id';
END update_reading_id;
PROCEDURE update_reading_ins(p_utilization_rec IN utilization_rec_type) IS
CURSOR csi_relationships_csr(c_csi_item_instance_id NUMBER) IS
SELECT subject_id csi_item_instance_id, position_reference
FROM csi_ii_relationships
START WITH object_id = c_csi_item_instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
AND relationship_type_code = 'COMPONENT-OF'
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
AND relationship_type_code = 'COMPONENT-OF';
SELECT nvl(counter_reading,0) counter_reading,
counter_group_id,
uom_code
FROM csi_cp_counters_v
WHERE counter_id = c_counter_id;
SELECT C.DEFAULTED_GROUP_ID counter_group_id,
NVL((select ccr.net_reading
from csi_counter_readings ccr
where ccr.counter_value_id = c.ctr_val_max_seq_no), 0) counter_reading,
C.COUNTER_ID counter_id,
C.START_DATE_ACTIVE start_date_active,
C.END_DATE_ACTIVE end_date_active,
C.UOM_CODE uom_code
FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
WHERE C.COUNTER_ID = CCA.COUNTER_ID(+)
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCA.SOURCE_OBJECT_ID = c_instance_id
AND C.COUNTER_TEMPLATE_NAME = c_counter_name
AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate)
-- Changes by jaramana on 28-DEC-2009 for bug 9229943
-- Lock the CSI_COUNTERS_VL row to prevent incorrect updates
FOR UPDATE OF c.ctr_val_max_seq_no;
select 'Y' from CSI_COUNTERS_B
where counter_id = c_counter_id
and reading_type = 2;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Ins';
update_reading_id(l_utilization_rec);
L_DEBUG_KEY, 'Returned from call to update_reading_id');
END update_reading_ins;
PROCEDURE update_reading_cn(p_utilization_rec IN utilization_rec_type) IS
TYPE instance_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT CI.subject_id
FROM csi_ii_relationships CI
WHERE EXISTS (SELECT 'X'
/*
FROM cs_counters CC,
cs_counter_groups CG
WHERE CC.counter_group_id = CG.counter_group_id
AND CG.source_object_id = CI.subject_id
AND CG.source_object_code = 'CP'
AND CC.name = c_counter_name
AND trunc(nvl(CC.start_date_active,sysdate)) <= trunc(sysdate)
AND trunc(nvl(CC.end_date_active,sysdate+1)) > trunc(sysdate))
*/
/*
FROM CSI_CP_COUNTERS_V CCCV
WHERE CCCV.CUSTOMER_PRODUCT_ID = CI.subject_id
AND CCCV.COUNTER_TEMPLATE_NAME = c_counter_name
AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate))
*/
FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
WHERE CCA.SOURCE_OBJECT_ID = CI.subject_id
AND C.COUNTER_ID = CCA.COUNTER_ID(+)
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND C.COUNTER_TEMPLATE_NAME = c_counter_name
AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate))
START WITH object_id = c_instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
AND relationship_type_code = 'COMPONENT-OF'
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
AND relationship_type_code = 'COMPONENT-OF';
SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = c_desc_instance_id
AND object_id <> c_ance_instance_id
-- This condition is really required because of the extreme case in which
-- subject_id = c_desc_instance_id and object_id happens to be c_ance_instance_id
-- thus it will include c_ance_instance_id and probably all of its ancestors if it has.
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
AND relationship_type_code = 'COMPONENT-OF'
CONNECT BY subject_id = PRIOR object_id
AND object_id <> c_ance_instance_id
AND trunc(nvl(active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(active_end_date,sysdate+1)) > trunc(sysdate)
AND relationship_type_code = 'COMPONENT-OF';
SELECT ctr.name counter_name, ctr.counter_id, ctr.uom_code, cgrp.counter_group_id
FROM cs_counter_groups cgrp, cs_counters ctr
WHERE cgrp.counter_group_id = ctr.counter_group_id
AND cgrp.source_object_code = 'CP'
AND cgrp.source_object_id = c_csi_item_instance_id
AND ctr.name = c_name
AND trunc(sysdate) >= trunc(nvl(ctr.start_date_active,sysdate))
AND trunc(sysdate) <= trunc(nvl(ctr.end_date_active,sysdate+1));
SELECT CCCV.COUNTER_TEMPLATE_NAME counter_name, CCCV.counter_id, CCCV.uom_code, CCCV.counter_group_id
FROM CSI_CP_COUNTERS_V CCCV
WHERE CCCV.CUSTOMER_PRODUCT_ID = c_csi_item_instance_id
AND CCCV.COUNTER_TEMPLATE_NAME = c_name
AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);
SELECT nvl(counter_reading,0) counter_reading,
counter_group_id,
uom_code
FROM csi_cp_counters_v
WHERE counter_id = c_counter_id;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_Cn';
'cs_counters_name_csr%FOUND. Calling update_reading_ins');
update_reading_ins(l_utilization_rec);
l_parents_tbl.DELETE;
l_instance_tbl.DELETE(i);
update_reading_ins(l_utilization_rec);
END update_reading_cn;
PROCEDURE update_reading_all(p_utilization_rec IN utilization_rec_type,
p_based_on IN VARCHAR2)
IS
l_utilization_rec utilization_rec_type;
L_DEBUG_KEY CONSTANT VARCHAR2(150) := G_LOG_PREFIX || '.Update_Reading_All';
SELECT DISTINCT CCCV.COUNTER_TEMPLATE_NAME counter_name
FROM CSI_CP_COUNTERS_V CCCV
WHERE CCCV.UOM_CODE = c_uom_code
AND CCCV.CUSTOMER_PRODUCT_ID = c_instance_id
AND trunc(nvl(CCCV.start_date_active, sysdate)) <= trunc(sysdate)
AND trunc(nvl(CCCV.end_date_active, sysdate+1)) > trunc(sysdate);
SELECT DISTINCT C.COUNTER_TEMPLATE_NAME counter_name
FROM CSI_COUNTERS_VL C, CSI_COUNTER_ASSOCIATIONS CCA
WHERE C.UOM_CODE = c_uom_code
AND trunc(nvl(C.start_date_active, sysdate)) <= trunc(sysdate)
AND trunc(nvl(C.end_date_active, sysdate+1)) > trunc(sysdate)
AND C.COUNTER_ID = CCA.COUNTER_ID(+)
AND CCA.SOURCE_OBJECT_CODE = 'CP'
AND CCA.SOURCE_OBJECT_ID IN (SELECT c_instance_id
FROM DUAL
UNION ALL
SELECT subject_id
FROM csi_ii_relationships CI
START WITH object_id = c_instance_id
AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
AND CI.relationship_type_code = 'COMPONENT-OF'
CONNECT BY object_id = PRIOR subject_id
AND trunc(nvl(CI.active_start_date,sysdate)) <= trunc(sysdate)
AND trunc(nvl(CI.active_end_date,sysdate+1)) > trunc(sysdate)
AND CI.relationship_type_code = 'COMPONENT-OF');
'p_based_on is COUNTERID. Calling update_reading_id');
update_reading_id(l_utilization_rec);
'p_based_on is COUNTER. Calling update_reading_cn');
update_reading_cn(l_utilization_rec);
', i = ' || i || ', Calling update_reading_cn');
update_reading_cn(l_utilization_rec);
', i = ' || i || ', Calling update_reading_cn');
update_reading_cn(l_utilization_rec);
END update_reading_all;
SELECT ratio
FROM ahl_ctr_update_rules
WHERE relationship_id = c_relationship_id
AND rule_code = c_rule_code
AND uom_code = c_uom_code;
SELECT object_id,
subject_id,
to_number(position_reference) relationship_id
FROM csi_ii_relationships
START WITH subject_id = c_desc_instance_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)
CONNECT BY subject_id = PRIOR object_id
AND subject_id <> c_start_instance_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);
l_rule_code ahl_ctr_update_rules.rule_code%TYPE := p_rule_code;
l_uom_code ahl_ctr_update_rules.uom_code%TYPE;