The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
(cslkup.start_date_active is null or trunc(cslkup.start_date_active) <= trunc(sysdate)) and
(cslkup.end_date_active is null or trunc(cslkup.end_date_active) >= trunc(sysdate));
select meaning from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
not exists
( select 'X' from
cs_sr_prob_code_mapping_detail cstl
where
cstl.problem_code = cslkup.lookup_code and
(cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
(cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
(cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
(cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate)));
select meaning
from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
exists
( select 'X' from
cs_sr_prob_code_mapping_detail cstl
where
cstl.problem_code = cslkup.lookup_code and
(cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
(cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
(cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
(cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
and
(
(cstl.incident_type_id = l_service_request_type_id and
cstl.inventory_item_id is null and
cstl.category_id is null)
or
(cstl.incident_type_id = l_service_request_type_id and
cstl.inventory_item_id = l_inventory_item_id and
cstl.organization_id = l_organization_id and
cstl.category_id is null)
or
(cstl.incident_type_id = l_service_request_type_id and
cstl.inventory_item_id is null and
exists (select category_id from mtl_item_categories cmtlc
where
cmtlc.category_id = cstl.category_id and
category_set_id = l_category_set_id and
cmtlc.inventory_item_id = l_inventory_item_id and
cmtlc.organization_id = l_organization_id))
)
);
select meaning
from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
exists
( select 'X' from
cs_sr_prob_code_mapping_detail cstl
where
cstl.problem_code = cslkup.lookup_code and
(cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
(cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
(cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
(cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
and
(
(cstl.incident_type_id is null and
cstl.inventory_item_id = l_inventory_item_id and
cstl.organization_id = l_organization_id and
cstl.category_id is null)
or
(cstl.incident_type_id is null and
cstl.inventory_item_id is null and
exists (select category_id from mtl_item_categories cmtlc
where
cmtlc.category_id = cstl.category_id and
category_set_id = l_category_set_id and /* value from profile CS_SR_DEFAULT_CATEGORY_SET */
cmtlc.inventory_item_id = l_inventory_item_id and
cmtlc.organization_id = l_organization_id))
)
);
select meaning
from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
exists
( select 'X' from
cs_sr_prob_code_mapping_detail cstl
where
cstl.problem_code = cslkup.lookup_code and
(cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
(cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
(cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
(cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
and
(
(cstl.incident_type_id = l_service_request_type_id and
cstl.inventory_item_id is null and
cstl.category_id is null)
or
(cstl.incident_type_id = l_service_request_type_id and
cstl.inventory_item_id = l_inventory_item_id and
cstl.organization_id = l_organization_id and
cstl.category_id is null)
or
(cstl.incident_type_id = l_service_request_type_id and
cstl.inventory_item_id is null and
cstl.category_id = l_product_category_id)
)
);
select meaning
from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
exists
( select 'X' from
cs_sr_prob_code_mapping_detail cstl
where
cstl.problem_code = cslkup.lookup_code and
(cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
(cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
(cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
(cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
and
(
cstl.incident_type_id is null and
cstl.inventory_item_id = l_inventory_item_id and
cstl.organization_id = l_organization_id and
cstl.category_id is null
)
) ;
select meaning
from
cs_lookups cslkup
where
cslkup.lookup_type='REQUEST_PROBLEM_CODE' and
cslkup.lookup_code = l_problem_code and
exists
( select 'X' from
cs_sr_prob_code_mapping_detail cstl
where
cstl.problem_code = cslkup.lookup_code and
(cstl.map_start_date_active is null or trunc(cstl.map_start_date_active) <= trunc(sysdate)) and
(cstl.map_end_date_active is null or trunc(cstl.map_end_date_active) >= trunc(sysdate)) and
(cstl.start_date_active is null or trunc(cstl.start_date_active) <= trunc(sysdate)) and
(cstl.end_date_active is null or trunc(cstl.end_date_active) >= trunc(sysdate))
and
(
cstl.incident_type_id is null and
cstl.inventory_item_id is null and
cstl.organization_id is null and
cstl.category_id = l_product_category_id
)
) ;
SELECT problem_map_id,start_date_active, end_date_active from cs_sr_prob_code_mapping
WHERE incident_type_id = l_service_request_type_id
AND category_id = l_product_category_id
AND inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND (start_date_active is null or trunc(start_date_active) <= trunc(sysdate)) and
(end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
SELECT category_id from mtl_category_set_valid_cats
WHERE category_set_id = to_number(l_product_category_set)
AND category_id = l_product_category_id;
SELECT lookup_code from cs_lookups
WHERE lookup_code = l_problem_code
AND lookup_type = 'REQUEST_PROBLEM_CODE'and
(start_date_active is null or
trunc(start_date_active) <= trunc(sysdate)) and
(end_date_active is null or
trunc(end_date_active) >= trunc(sysdate));
SELECT problem_map_detail_id,start_date_active, end_date_active from cs_sr_prob_code_mapping_detail
WHERE problem_map_id = l_problem_map_id
AND problem_code = l_problem_code
AND (start_date_active is null or trunc(start_date_active) <= trunc(sysdate))
AND (end_date_active is null or trunc(end_date_active) >= trunc(sysdate));
CS_SR_PROBLEM_CODE_MAPPING_PKG.INSERT_ROW (
PX_PROBLEM_MAP_ID => l_problem_map_id,
P_INCIDENT_TYPE_ID => l_service_request_type_id,
P_INVENTORY_ITEM_ID => l_inventory_item_id,
P_ORGANIZATION_ID => l_organization_id,
P_CATEGORY_ID => l_product_category_id,
P_PROBLEM_CODE => null,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_CREATION_DATE => l_current_date,
P_CREATED_BY => l_created_by,
P_LAST_UPDATE_DATE => l_current_date,
P_LAST_UPDATED_BY => l_created_by,
P_LAST_UPDATE_LOGIN => l_login,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_errmsg);
CS_SR_PROB_CODE_MAP_DETAIL_PKG.INSERT_ROW (
PX_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
P_PROBLEM_MAP_ID => l_problem_map_id,
P_INCIDENT_TYPE_ID => l_service_request_type_id,
P_INVENTORY_ITEM_ID => l_inventory_item_id,
P_ORGANIZATION_ID => l_organization_id,
P_CATEGORY_ID => l_product_category_id,
P_MAP_START_DATE_ACTIVE => null,
P_MAP_END_DATE_ACTIVE => null,
P_PROBLEM_CODE => null,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_CREATION_DATE => l_current_date,
P_CREATED_BY => l_created_by,
P_LAST_UPDATE_DATE => l_current_date,
P_LAST_UPDATED_BY => l_created_by,
P_LAST_UPDATE_LOGIN => l_login,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_errmsg);
PROCEDURE UPDATE_MAPPING_RULES
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_probcode_map_criteria_rec IN probcode_map_criteria_rec,
p_problem_codes_tbl IN problem_codes_tbl_type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MAPPING_RULES';
SELECT problem_map_id,incident_type_id,category_id,inventory_item_id,organization_id
FROM CS_SR_PROB_CODE_MAPPING
WHERE problem_map_id <> l_problem_map_id AND
incident_type_id = l_service_request_type_id AND
category_id = l_product_category_id AND
inventory_item_id = l_inventory_item_id AND
organization_id = l_organization_id AND
(start_date_active is null or
trunc(start_date_active) <= trunc(sysdate)) and
(end_date_active is null or
trunc(end_date_active) >= trunc(sysdate));
SELECT lookup_code from cs_lookups a
WHERE lookup_code = l_problem_code
AND lookup_type = 'REQUEST_PROBLEM_CODE' and
(start_date_active is null or
trunc(start_date_active) <= trunc(sysdate)) and
(end_date_active is null or
trunc(end_date_active) >= trunc(sysdate));
SELECT problem_code,start_date_active,end_date_active from CS_SR_PROB_CODE_MAPPING_DETAIL
WHERE problem_map_id = l_problem_map_id and
problem_map_detail_id <> nvl(l_problem_map_detail_id,0) and
problem_code = l_problem_code and
(start_date_active is null or
trunc(start_date_active) <= trunc(sysdate)) and
(end_date_active is null or
trunc(end_date_active) >= trunc(sysdate));
SAVEPOINT update_mapping_rules;
CS_SR_PROB_CODE_MAP_DETAIL_PKG.INSERT_ROW (
PX_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
P_PROBLEM_MAP_ID => l_problem_map_id,
P_INCIDENT_TYPE_ID => l_service_request_type_id,
P_INVENTORY_ITEM_ID => l_inventory_item_id,
P_ORGANIZATION_ID => l_organization_id,
P_CATEGORY_ID => l_product_category_id,
P_MAP_START_DATE_ACTIVE => null,
P_MAP_END_DATE_ACTIVE => null,
P_PROBLEM_CODE => l_problem_code,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_CREATION_DATE => l_current_date,
P_CREATED_BY => l_created_by,
P_LAST_UPDATE_DATE => l_current_date,
P_LAST_UPDATED_BY => l_created_by,
P_LAST_UPDATE_LOGIN => l_login,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_errmsg);
CS_SR_PROB_CODE_MAP_DETAIL_PKG.UPDATE_ROW (
P_PROBLEM_MAP_DETAIL_ID => l_problem_map_detail_id,
P_PROBLEM_MAP_ID => l_problem_map_id,
P_INCIDENT_TYPE_ID => l_service_request_type_id,
P_INVENTORY_ITEM_ID => l_inventory_item_id,
P_ORGANIZATION_ID => l_organization_id,
P_CATEGORY_ID => l_product_category_id,
P_MAP_START_DATE_ACTIVE => null,
P_MAP_END_DATE_ACTIVE => null,
P_PROBLEM_CODE => l_problem_code,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_LAST_UPDATE_DATE => l_current_date,
P_LAST_UPDATED_BY => l_created_by,
P_LAST_UPDATE_LOGIN => l_login,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_errmsg);
ROLLBACK TO update_mapping_rules;
ROLLBACK TO update_mapping_rules;
END; -- End of procedure UPDATE_MAPPING_RULES()
SELECT problem_map_id, start_date_active, end_date_active
FROM CS_SR_PROB_CODE_MAPPING;
SELECT problem_map_id, problem_map_detail_id,
map_start_date_active, map_end_date_active,
start_date_active, end_date_active,
incident_type_id, inventory_item_id, organization_id,
category_id, problem_code
FROM CS_SR_PROB_CODE_MAPPING_DETAIL
WHERE
problem_map_id = l_problem_map_id;
update CS_SR_PROB_CODE_MAPPING_DETAIL
set
map_start_date_active = cs_sr_probmapid_crit_rec.start_date_active,
map_end_date_active = cs_sr_probmapid_crit_rec.end_date_active
where
problem_map_id = l_problem_map_id;
update CS_SR_PROB_CODE_MAPPING_DETAIL
set
map_start_date_active = cs_sr_probmapid_crit_rec.start_date_active,
map_end_date_active = cs_sr_probmapid_crit_rec.end_date_active
where
problem_map_id = l_problem_map_id;
CS_SR_RES_CODE_MAP_DETAIL_PKG.UPDATE_ROW (
P_RESOLUTION_MAP_DETAIL_ID => cs_sr_resmapid_rules_rec.resolution_map_detail_id,
P_RESOLUTION_MAP_ID => l_resolution_map_id,
P_INCIDENT_TYPE_ID => cs_sr_resmapid_rules_rec.incident_type_id,
P_INVENTORY_ITEM_ID => cs_sr_resmapid_rules_rec.inventory_item_id,
P_ORGANIZATION_ID => cs_sr_resmapid_rules_rec.organization_id,
P_CATEGORY_ID => cs_sr_resmapid_rules_rec.category_id,
P_PROBLEM_CODE => cs_sr_resmapid_rules_rec.problem_code,
P_MAP_START_DATE_ACTIVE => cs_sr_resmapid_crit_rec.start_date_active,
P_MAP_END_DATE_ACTIVE => cs_sr_resmapid_crit_rec.end_date_active,
P_RESOLUTION_CODE => cs_sr_resmapid_rules_rec.resolution_code,
P_START_DATE_ACTIVE => cs_sr_resmapid_rules_rec.start_date_active,
P_END_DATE_ACTIVE => cs_sr_resmapid_rules_rec.end_date_active,
P_OBJECT_VERSION_NUMBER => null,
P_ATTRIBUTE1 => null,
P_ATTRIBUTE2 => null,
P_ATTRIBUTE3 => null,
P_ATTRIBUTE4 => null,
P_ATTRIBUTE5 => null,
P_ATTRIBUTE6 => null,
P_ATTRIBUTE7 => null,
P_ATTRIBUTE8 => null,
P_ATTRIBUTE9 => null,
P_ATTRIBUTE10 => null,
P_ATTRIBUTE11 => null,
P_ATTRIBUTE12 => null,
P_ATTRIBUTE13 => null,
P_ATTRIBUTE14 => null,
P_ATTRIBUTE15 => null,
P_ATTRIBUTE_CATEGORY => null,
P_LAST_UPDATE_DATE => l_current_date,
P_LAST_UPDATED_BY => l_created_by,
P_LAST_UPDATE_LOGIN => l_login,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_errmsg
);