The following lines contain the word 'select', 'insert', 'update' or 'delete':
CSD_RO_BULLETINS_PKG.INSERT_ROW(
px_ro_bulletin_id => x_ro_bulletin_id
,p_repair_line_id => p_ro_bulletin_rec.repair_line_id
,p_bulletin_id => p_ro_bulletin_rec.bulletin_id
,p_last_viewed_date => p_ro_bulletin_rec.last_viewed_date
,p_last_viewed_by => p_ro_bulletin_rec.last_viewed_by
,p_source_type => p_ro_bulletin_rec.source_type
,p_source_id => p_ro_bulletin_rec.source_id
,p_object_version_number => p_ro_bulletin_rec.object_version_number
,p_created_by => FND_GLOBAL.USER_ID
,p_creation_date => sysdate
,p_last_updated_by => FND_GLOBAL.USER_ID
,p_last_update_date => sysdate
,p_last_update_login => FND_GLOBAL.LOGIN_ID);
PROCEDURE UPDATE_RO_BULLETIN(
p_api_version_number 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_ro_bulletin_rec IN RO_BULLETIN_Rec_Type,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
---- cursors ----
CURSOR cur_get_ro_bulletin(p_ro_bulletin_id Number) IS
SELECT ro_bulletin_id,
repair_line_id,
bulletin_id,
last_viewed_date,
last_viewed_by,
source_type,
source_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
FROM csd_ro_bulletins
WHERE ro_bulletin_id = p_ro_bulletin_id
FOR UPDATE NOWAIT;
c_API_NAME CONSTANT VARCHAR2(30) := 'UPDATE_RO_BULLETIN';
SAVEPOINT UPDATE_RO_BULLETIN_PVT;
l_ref_ro_bulletin_rec.last_updated_by,
l_ref_ro_bulletin_rec.last_update_date,
l_ref_ro_bulletin_rec.last_update_login;
CSD_RO_BULLETINS_PKG.UPDATE_ROW(
p_ro_bulletin_id => p_ro_bulletin_rec.ro_bulletin_id
,p_repair_line_id => p_ro_bulletin_rec.repair_line_id
,p_bulletin_id => p_ro_bulletin_rec.bulletin_id
,p_last_viewed_date => p_ro_bulletin_rec.last_viewed_date
,p_last_viewed_by => p_ro_bulletin_rec.last_viewed_by
,p_source_type => p_ro_bulletin_rec.source_type
,p_source_id => p_ro_bulletin_rec.source_id
,p_object_version_number => p_ro_bulletin_rec.object_version_number
,p_created_by => FND_API.G_MISS_NUM
,p_creation_date => FND_API.G_MISS_DATE
,p_last_updated_by => FND_GLOBAL.USER_ID
,p_last_update_date => SYSDATE
,p_last_update_login => p_ro_bulletin_rec.last_update_login);
END UPDATE_RO_BULLETIN;
PROCEDURE DELETE_RO_BULLETIN(
p_api_version_number 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_ro_bulletin_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
---- local constants ----
c_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_RO_BULLETIN';
SAVEPOINT DELETE_RO_BULLETIN_PVT;
CSD_RO_BULLETINS_PKG.DELETE_ROW(p_ro_bulletin_id => p_ro_bulletin_id);
End DELETE_RO_BULLETIN;
SAVEPOINT DELETE_RO_BULLETIN_PVT;
CSD_REPAIRS_PVT.update_repair_order(
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_repair_line_id => p_repair_line_id,
p_repln_rec => l_repln_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.Set_Name('CSD', 'CSD_UPDATE_REPAIR_FAILED');
SELECT escalation_code,
wf_item_type,
wf_process_name
FROM csd_bulletins_b
WHERE bulletin_id = p_bulletin_id
; --* end cur_get_bulletin_info *--
SELECT b.service_code_id
FROM csd_bulletins_b a, csd_bulletin_scs b
WHERE a.bulletin_id = b.bulletin_id
AND a.bulletin_id = p_bulletin_id
; --* end cur_get_ro_sc_ids *--
SELECT frequency_code
FROM csd_bulletins_b
WHERE bulletin_id = p_bulletin_id
AND published_flag = FND_API.G_TRUE
AND sysdate BETWEEN NVL(active_from,sysdate)
AND NVL(active_to, sysdate)
; --* end CURSOR cur_get_bulletin_freq_code *--
SELECT a.ro_bulletin_id
FROM csd_ro_bulletins a
WHERE a.repair_line_id = p_repair_line_id
AND a.bulletin_id = p_bulletin_id
; --* end CURSOR cur_check_by_repair *--
SELECT a.ro_bulletin_id
FROM csd_ro_bulletins a
WHERE a.repair_line_id IN
(SELECT repair_line_id
FROM csd_repairs
WHERE customer_product_id = p_instance_id)
AND a.bulletin_id = p_bulletin_id
; --* end cur_check_by_instance *--
l_ro_bulletin_rec.last_update_login := FND_GLOBAL.USER_ID;
CSD_REPAIRS_PVT.update_repair_order(
p_api_version_number => p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => p_validation_level,
p_repair_line_id => p_repair_line_id,
p_repln_rec => l_repln_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
FND_MESSAGE.Set_Name('CSD', 'CSD_UPDATE_REPAIR_FAILED');
SELECT TO_CHAR(CSD_WF_ITEM_KEY_S.NEXTVAL)
INTO l_wf_item_key
FROM DUAL;
SELECT object_version_number
FROM csd_repairs
WHERE repair_line_id = p_repair_line_id
; --* end cur_get_obj_ver_num *--
select bulletin_id
from csd_bulletins_b
where published_flag = 'T'
and sysdate between nvl(active_from, sysdate-1) and nvl(active_to, sysdate+1)
and bulletin_type_code = nvl(p_bulletin_type_code, bulletin_type_code);
select rule_id
from CSD_RULES_B
where attribute1 = p_bulletin_id
and rule_type_code = 'BULLETIN';
select to_number(attribute1) bulletin_id
from CSD_RULES_B
where rule_id = p_rule_id;
l_sql_query := l_sql_query || ' AND not exists ( select ''X'' from csd_ro_bulletins bul'
|| ' where bul.source_id = ' || p_bulletin_rule_id
|| ' and bul.repair_line_id = dra.repair_line_id )';
SELECT 'X'
FROM
csd_sc_domains_v dom, CSD_REPAIRS dra
WHERE dom.service_code_id = p_service_code_id
AND dra.repair_line_id = p_repair_line_id
AND (dom.inventory_item_id = dra.inventory_item_id
OR (dom.category_set_id = fnd_profile.value('CSD_DEFAULT_CATEGORY_SET')
AND dom.category_id in ( SELECT DISTINCT cat.category_id
FROM mtl_item_categories_v cat
WHERE cat.inventory_item_id = dra.inventory_item_id)
)
);
SELECT ro_service_code_id
,object_version_number
,repair_line_id
,service_code_id
,source_type_code
,source_solution_id
,applicable_flag
,applied_to_est_flag
,applied_to_work_flag
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,service_item_id
FROM CSD_RO_SERVICE_CODES
WHERE repair_line_id = p_repair_line_id
AND service_code_id = p_service_code_id;
SELECT inventory_item_id
FROM CSD_REPAIRS
WHERE repair_line_id = p_repair_line_id;
CSD_RO_SERVICE_CODES_PVT.Update_RO_Service_Code (
p_api_version => l_api_version,
p_commit => FND_API.G_FALSE,
p_init_msg_list => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ro_service_code_rec => l_ro_service_code_rec,
x_obj_ver_number => l_obj_ver_number
);