[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_last_update_login IN NUMBER DEFAULT fnd_global.login_id,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
P_IMPACT_TYPE_CODE_ORDER IN NUMBER default null,
--end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
x_ci_impact_type_usage_id OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_rowid VARCHAR2(30);
SELECT 'Y' FROM dual
WHERE exists (SELECT ci_impact_type_usage_id FROM
pa_ci_impact_type_usage
-- WHERE ci_type_class_code = p_ci_type_class_code
WHERE nvl(ci_type_class_code, 1) = nvl(p_ci_type_class_code, 1) -- cklee
AND ci_type_id = p_ci_type_id
AND impact_type_code = p_impact_type_code);
PA_CI_IMPACT_TYPE_USAGE_pkg.insert_row(
x_rowid => l_rowid,
x_ci_impact_type_usage_id => x_ci_impact_type_usage_id,
x_impact_type_code => p_impact_type_code,
x_ci_type_class_code => p_ci_type_class_code,
x_ci_type_id => p_ci_type_id,
x_creation_date => p_creation_date,
x_created_by => p_created_by,
x_last_update_date => p_creation_date,
x_last_updated_by => p_created_by,
x_last_update_login => p_last_update_login,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
X_IMPACT_TYPE_CODE_ORDER => P_IMPACT_TYPE_CODE_ORDER);
PROCEDURE update_ci_impact_type_usage (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := 'T',
p_commit IN VARCHAR2 := 'F',
p_validate_only IN VARCHAR2 := 'T',
p_max_msg_count IN NUMBER := null,
P_IMPACT_TYPE_CODE_ORDER IN NUMBER,
p_ci_impact_type_usage_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
is
BEGIN
pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.UPDATE_CI_IMPACT_TYPE_USAGE');
SAVEPOINT update_ci_impact_type_usage;
PA_CI_IMPACT_TYPE_USAGE_pkg.update_row(
x_ci_impact_type_usage_id => p_ci_impact_type_usage_id,
X_IMPACT_TYPE_CODE_ORDER => P_IMPACT_TYPE_CODE_ORDER);
ROLLBACK TO update_ci_impact_type_usage;
ROLLBACK TO update_ci_impact_type_usage;
p_procedure_name => 'UPDATE_CI_IMPACT_TYPE_USAGE',
p_error_text => SUBSTRB(SQLERRM,1,240));
end update_ci_impact_type_usage;
PROCEDURE delete_ci_impact_type_usage (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := 'T',
p_commit IN VARCHAR2 := 'F',
p_validate_only IN VARCHAR2 := 'T',
p_max_msg_count IN NUMBER := null,
p_ci_impact_type_usage_id IN NUMBER := null,
p_impact_type_code IN VARCHAR2 := null,
p_ci_type_class_code IN VARCHAR2 := null,
p_ci_type_id IN NUMBER := null,
--start:| 16-FEB-2009 cklee R12.1.2 setup ehancement
P_IMPACT_TYPE_CODE_ORDER IN NUMBER default null,
--end:| 16-FEB-2009 cklee R12.1.2 setup ehancement
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_temp VARCHAR2(1);
SELECT 'Y' FROM dual
WHERE exists (SELECT ci_impact_type_usage_id FROM
pa_ci_impact_type_usage
WHERE ci_impact_type_usage_id = p_ci_impact_type_usage_id
);
SELECT MEANING FROM pa_lookups
WHERE LOOKUP_TYPE = 'PA_CI_IMPACT_TYPES'
AND LOOKUP_CODE = p_impact_type_code;
pa_debug.set_err_stack ('PA_CI_IMPACT_TYPE_USAGE_PVT.DELETE_CI_IMPACT_TYPE_USAGE');
SAVEPOINT delete_ci_impact_type_usage;
l_dummy := pa_ci_impact_type_usage_pub.delete_impact_type_usage_ok
(p_impact_type_code, p_ci_type_id);
PA_CI_IMPACT_TYPE_USAGE_pkg.delete_row(
x_ci_impact_type_usage_id => p_ci_impact_type_usage_id);
ROLLBACK TO delete_ci_impact_type_usage;
ROLLBACK TO delete_ci_impact_type_usage;
p_procedure_name => 'DELETE_CI_IMPACT_TYPE_USAGE',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_ci_impact_type_usage;
select pc.impact_type_code,
luk.meaning impact_type_name
from pa_control_items pci,
pa_ci_impacts pc,
pa_lookups luk
where pci.ci_type_id = p_ci_type_id
and pci.ci_id = pc.ci_id
and pc.impact_type_code = luk.lookup_code
and luk.lookup_type = 'PA_CI_IMPACT_TYPES'
and pc.impact_type_code <> 'FINPLAN' -- cklee 4/23/09
and not exists
(select 1
from pa_ci_impact_type_usage pcu
where pcu.impact_type_code = pc.impact_type_code
and pcu.ci_type_id = pci.ci_type_id);
CURSOR delete_impacts_csr (p_ci_type_id pa_ci_types_b.CI_TYPE_ID%type) is
select pu.ci_impact_type_usage_id
from pa_ci_impact_type_usage pu
where pu.ci_type_id = p_ci_type_id;
delete_impacts_rec delete_impacts_csr%ROWTYPE;
select ci.IMPACT_BUDGET_TYPE_CODE,
ci.COST_COL_FLAG,
ci.REV_COL_FLAG,
ci.DIR_COST_REG_FLAG,
ci.SUPP_COST_REG_FLAG,
ci.DIR_REG_REV_COL_FLAG
from pa_ci_types_b ci
where ci.ci_type_id = p_ci_type_id;
deletion and we validate after all database transactions (delete and insert new lists) are done;
1. delete * from pa_ci_impact_type_usage where ci_type_id = p_ci_type;
3. insert into pa_ci_impact_type_usage with passed in impact lists
4. validate if existing impact list is missing from the new impact list and raise error
*/
--1. delete * from pa_ci_impact_type_usage where ci_type_id = p_ci_type;
IF p_ui_mode = 'UPDATE' AND p_ci_class_code <> 'ISSUE' THEN
FOR delete_impacts_rec IN delete_impacts_csr (p_ci_type_id)
LOOP
PA_CI_IMPACT_TYPE_USAGE_pvt.delete_CI_IMPACT_TYPE_USAGE(
--p_api_version => p_api_version,
--p_init_msg_list => p_init_msg_list,
--p_commit => p_commit,
p_validate_only => p_validate_only,
--p_max_msg_count => p_max_msg_count,
p_ci_impact_type_usage_id => delete_impacts_rec.ci_impact_type_usage_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
p_last_update_login => fnd_global.login_id,
P_IMPACT_TYPE_CODE_ORDER => i,
x_ci_impact_type_usage_id => l_ci_impact_type_usage_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
/* IF p_ui_mode = 'UPDATE' AND p_ci_class_code <> 'ISSUE' THEN
FOR validate_impacts_rec IN validate_impacts_csr (p_ci_type_id)
LOOP
PA_UTILS.Add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_IMPACT_TU_IN_USE'
,p_token1 => 'IMPACT'
,p_value1 => validate_impacts_rec.impact_type_name);