[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'x'
from pa_organizations_project_v p
where organization_id = p_agreement_org_id
and sysdate between decode(date_from, null, sysdate, date_from)
and decode(date_to, null, sysdate, date_to);
select 'x'
from hr_all_organization_units
where organization_id = p_agreement_org_id
and sysdate between decode(date_from, null, sysdate, date_from)
and decode(date_to, null, sysdate, date_to);
select oke_k_funding_sources_s.nextval
into l_funding_source_id
from dual;
select nvl(minimum_accountable_unit, power(10, -1 * precision))
from fnd_currencies
where currency_code = p_currency;
select currency_code
from okc_k_headers_b
where id = p_header_id;
PROCEDURE update_agreement_reference(p_agreement_id NUMBER ,
p_org_id NUMBER ,
p_currency_code VARCHAR2 ,
p_funding_source_id NUMBER ,
p_num_update_flag VARCHAR2
) is
cursor c_length(b_owner varchar2) is
select data_length
from all_tab_columns
where table_name = 'PA_AGREEMENTS_ALL'
and owner = b_owner
and column_name = 'AGREEMENT_NUM';
update pa_agreements_all
set pm_product_code = G_PRODUCT_CODE,
-- agreement_num = decode(p_num_update_flag, 'Y', substr(agreement_num, 0, 20-1-length(p_currency_code)) || '-' || p_currency_code,
-- 'N', agreement_num, agreement_num),
agreement_num = decode(p_num_update_flag, 'Y', substr(agreement_num, 0, l_length-1-length(p_currency_code)) || '-' || p_currency_code,
'N', agreement_num, agreement_num),
--agreement_num = substr(agreement_num, 0, 20-1-length(p_currency_code)) || '-' || p_currency_code,
pm_agreement_reference = p_org_id || '-' || p_currency_code || '-' || p_funding_source_id
-- pm_agreement_reference = p_org_id || '-Y-' || p_funding_source_id
where agreement_id = p_agreement_id;
END update_agreement_reference;
PROCEDURE update_proj_fld_reference(p_project_funding_id NUMBER ,
p_fund_allocation_id NUMBER
) is
BEGIN
update pa_project_fundings
set pm_product_code = G_PRODUCT_CODE,
pm_funding_reference = p_fund_allocation_id || '.1'
where project_funding_id = p_project_funding_id;
END update_proj_fld_reference;
select rowid, pool_party_id, agreement_flag
from oke_k_funding_sources
where funding_source_id = p_funding_source_id;
select 'x'
from oke_k_headers
where k_header_id = p_object_id;
select 'x'
from fnd_currencies
where currency_code = upper(p_currency_code);
select 'x'
from pa_agreements_all
where agreement_id = p_agreement_id
and nvl(pm_product_code, '-99') <> G_PRODUCT_CODE;
select project_id
from oke_k_headers
where k_header_id = p_object_id;
select 'x'
from pa_project_fundings
where agreement_id = p_agreement_id
and project_id not in
(select to_number(sub_project_id)
from pa_fin_structures_links_v
start with parent_project_id = x_project_id
connect by parent_project_id = prior sub_project_id
union all
select x_project_id
from dual
);
select 'x'
from pa_agreements_all
where agreement_id = p_agreement_id
FOR UPDATE OF agreement_id NOWAIT;
select 'x'
from pa_project_fundings
where agreement_id = p_agreement_id
FOR UPDATE OF project_funding_id NOWAIT;
select 'x'
from okc_k_party_roles_b b,
okx_parties_v v
where dnz_chr_id = p_k_header_id
and b.object1_id1 = v.id1
and b.object1_id2 = v.id2
and b.rle_code = 'FUND_BY'
and b.jtot_object1_code = 'OKX_PARTY'
and v.id1 = p_k_party_id;
select 'x'
from fnd_lookup_values
where lookup_type = 'FUNDING_STATUS'
and enabled_flag = 'Y'
and language = userenv('LANG')
and lookup_code = upper(p_funding_status);
select 'x'
from gl_daily_conversion_types
where UPPER(conversion_type) = UPPER(p_conversion_type);
select 'x'
from oke_pool_parties
where pool_party_id = p_pool_party_id
FOR UPDATE OF pool_party_id NOWAIT;
select currency_code
from oke_pool_parties
where pool_party_id = p_pool_party_id
and party_id = p_party_id
FOR UPDATE OF pool_party_id NOWAIT;
select *
from oke_k_funding_sources
where funding_source_id = p_funding_in_rec.funding_source_id
FOR UPDATE OF funding_source_id NOWAIT;
-- p_update_flag VARCHAR2 ,
p_currency_code OUT NOCOPY VARCHAR2 ,
p_start_date OUT NOCOPY DATE
--p_currency_code OUT VARCHAR2
) is
cursor c_party is
select 'x'
from hz_cust_accounts
where cust_account_id = p_customer_id
and party_id = p_party_id;
select currency_code, start_date_active
from oke_pool_parties
where pool_party_id = p_pool_party_id
and party_id = p_party_id
and ((start_date_active is null) or
(p_expiration_date is not null and
nvl(start_date_active, p_expiration_date) <= p_expiration_date)
or (p_expiration_date is null))
-- and ((end_date_active is null) or
-- (p_expiration_date is not null and
-- nvl(to_char(end_date_active, 'YYYYMMDD'), '19000101') >= nvl(to_char(p_expiration_date, 'YYYYMMDD'), '19000101')))
-- and ((p_expiration_date is not null) and nvl(to_char(end_date_active, 'YYYYMMDD'), '19000101') >= nvl(to_char(p_expiration_date, 'YYYYMMDD'), '19000101')) or
-- end_date_active is null)
-- and nvl(end_date_active, nvl(p_expiration_date, sysdate)) >= nvl(p_expiration_date, sysdate)
FOR UPDATE OF pool_party_id NOWAIT;
OKE_FUNDINGSOURCE_PVT.insert_row(X_Rowid => l_rowid ,
X_Funding_Source_Id => l_funding_in_rec.funding_source_id ,
X_Pool_Party_Id => l_funding_in_rec.pool_party_id ,
X_K_Party_Id => l_funding_in_rec.k_party_id ,
X_Object_Type => upper(l_funding_in_rec.object_type) ,
X_Object_Id => l_funding_in_rec.object_id ,
X_Agreement_Number => l_funding_in_rec.agreement_number ,
X_Currency_Code => upper(l_funding_in_rec.currency_code) ,
X_Amount => l_funding_in_rec.amount ,
X_Initial_Amount => l_funding_in_rec.amount ,
X_Previous_Amount => l_zero ,
X_Funding_Status => upper(l_funding_in_rec.funding_status) ,
X_Hard_Limit => l_funding_in_rec.hard_limit ,
X_K_Conversion_Type => l_funding_in_rec.k_conversion_type ,
X_K_Conversion_Date => l_funding_in_rec.k_conversion_date ,
X_K_Conversion_Rate => l_funding_in_rec.k_conversion_rate ,
X_Start_Date_Active => l_funding_in_rec.start_date_active ,
X_End_Date_Active => l_funding_in_rec.end_date_active ,
X_Last_Update_Date => sysdate ,
X_Last_Updated_By => L_USERID ,
X_Creation_Date => sysdate ,
X_Created_By => L_USERID ,
X_Last_Update_Login => L_LOGINID ,
-- X_Attribute_Category => upper(l_funding_in_rec.oke_attribute_category) ,
X_Attribute_Category => l_funding_in_rec.oke_attribute_category ,
X_Attribute1 => l_funding_in_rec.oke_attribute1 ,
X_Attribute2 => l_funding_in_rec.oke_attribute2 ,
X_Attribute3 => l_funding_in_rec.oke_attribute3 ,
X_Attribute4 => l_funding_in_rec.oke_attribute4 ,
X_Attribute5 => l_funding_in_rec.oke_attribute5 ,
X_Attribute6 => l_funding_in_rec.oke_attribute6 ,
X_Attribute7 => l_funding_in_rec.oke_attribute7 ,
X_Attribute8 => l_funding_in_rec.oke_attribute8 ,
X_Attribute9 => l_funding_in_rec.oke_attribute9 ,
X_Attribute10 => l_funding_in_rec.oke_attribute10 ,
X_Attribute11 => l_funding_in_rec.oke_attribute11 ,
X_Attribute12 => l_funding_in_rec.oke_attribute12 ,
X_Attribute13 => l_funding_in_rec.oke_attribute13 ,
X_Attribute14 => l_funding_in_rec.oke_attribute14 ,
X_Attribute15 => l_funding_in_rec.oke_attribute15 ,
-- X_PA_Attribute_Category => upper(l_funding_in_rec.pa_attribute_category) ,
X_PA_Attribute_Category => l_funding_in_rec.pa_attribute_category ,
X_PA_Attribute1 => l_funding_in_rec.pa_attribute1 ,
X_PA_Attribute2 => l_funding_in_rec.pa_attribute2 ,
X_PA_Attribute3 => l_funding_in_rec.pa_attribute3 ,
X_PA_Attribute4 => l_funding_in_rec.pa_attribute4 ,
X_PA_Attribute5 => l_funding_in_rec.pa_attribute5 ,
X_PA_Attribute6 => l_funding_in_rec.pa_attribute6 ,
X_PA_Attribute7 => l_funding_in_rec.pa_attribute7 ,
X_PA_Attribute8 => l_funding_in_rec.pa_attribute8 ,
X_PA_Attribute9 => l_funding_in_rec.pa_attribute9 ,
X_PA_Attribute10 => l_funding_in_rec.pa_attribute10 ,
X_Revenue_Hard_Limit => l_funding_in_rec.revenue_hard_limit ,
X_Agreement_Org_id => l_funding_in_rec.agreement_org_id
);
PROCEDURE update_funding(p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_funding_in_rec IN FUNDING_REC_IN_TYPE ,
p_funding_out_rec OUT NOCOPY FUNDING_REC_OUT_TYPE ,
p_return_status OUT NOCOPY VARCHAR2
) is
l_api_name CONSTANT VARCHAR2(40) := 'update_funding';
OKE_FUNDINGSOURCE_PVT.update_row(X_Funding_Source_Id => l_funding_in_rec.funding_source_id ,
X_Pool_Party_Id => l_funding_in_rec.pool_party_id ,
X_K_Party_Id => l_funding_in_rec.k_party_id ,
X_Amount => l_funding_in_rec.amount ,
X_Previous_Amount => nvl(l_previous_amount, 0) ,
X_Funding_Status => upper(l_funding_in_rec.funding_status) ,
X_agreement_number => l_funding_in_rec.agreement_number ,
X_Hard_Limit => l_funding_in_rec.hard_limit ,
X_K_Conversion_Type => l_funding_in_rec.k_conversion_type ,
X_K_Conversion_Date => l_funding_in_rec.k_conversion_date ,
X_K_Conversion_Rate => l_funding_in_rec.k_conversion_rate ,
X_Start_Date_Active => l_funding_in_rec.start_date_active ,
X_End_Date_Active => l_funding_in_rec.end_date_active ,
X_Last_Update_Date => sysdate ,
X_Last_Updated_By => L_USERID ,
X_Last_Update_Login => L_LOGINID ,
--X_Attribute_Category => upper(l_funding_in_rec.oke_attribute_category) ,
X_Attribute_Category => l_funding_in_rec.oke_attribute_category ,
X_Attribute1 => l_funding_in_rec.oke_attribute1 ,
X_Attribute2 => l_funding_in_rec.oke_attribute2 ,
X_Attribute3 => l_funding_in_rec.oke_attribute3 ,
X_Attribute4 => l_funding_in_rec.oke_attribute4 ,
X_Attribute5 => l_funding_in_rec.oke_attribute5 ,
X_Attribute6 => l_funding_in_rec.oke_attribute6 ,
X_Attribute7 => l_funding_in_rec.oke_attribute7 ,
X_Attribute8 => l_funding_in_rec.oke_attribute8 ,
X_Attribute9 => l_funding_in_rec.oke_attribute9 ,
X_Attribute10 => l_funding_in_rec.oke_attribute10 ,
X_Attribute11 => l_funding_in_rec.oke_attribute11 ,
X_Attribute12 => l_funding_in_rec.oke_attribute12 ,
X_Attribute13 => l_funding_in_rec.oke_attribute13 ,
X_Attribute14 => l_funding_in_rec.oke_attribute14 ,
X_Attribute15 => l_funding_in_rec.oke_attribute15 ,
-- X_PA_Attribute_Category => upper(l_funding_in_rec.pa_attribute_category) ,
X_PA_Attribute_Category => l_funding_in_rec.pa_attribute_category ,
X_PA_Attribute1 => l_funding_in_rec.pa_attribute1 ,
X_PA_Attribute2 => l_funding_in_rec.pa_attribute2 ,
X_PA_Attribute3 => l_funding_in_rec.pa_attribute3 ,
X_PA_Attribute4 => l_funding_in_rec.pa_attribute4 ,
X_PA_Attribute5 => l_funding_in_rec.pa_attribute5 ,
X_PA_Attribute6 => l_funding_in_rec.pa_attribute6 ,
X_PA_Attribute7 => l_funding_in_rec.pa_attribute7 ,
X_PA_Attribute8 => l_funding_in_rec.pa_attribute8 ,
X_PA_Attribute9 => l_funding_in_rec.pa_attribute9 ,
X_PA_Attribute10 => l_funding_in_rec.pa_attribute10 ,
X_Revenue_Hard_Limit => l_funding_in_rec.revenue_hard_limit ,
X_Agreement_Org_id => l_funding_in_rec.agreement_org_id
);
END update_funding;
PROCEDURE delete_funding(p_api_version IN NUMBER ,
p_commit IN VARCHAR2 := OKE_API.G_FALSE ,
p_init_msg_list IN VARCHAR2 := OKE_API.G_FALSE ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_funding_source_id IN NUMBER ,
-- p_agreement_flag IN VARCHAR2 := OKE_API.G_FALSE ,
p_return_status OUT NOCOPY VARCHAR2
) is
cursor c_agreement (p_funding_source_id NUMBER,
length NUMBER) is
select pm_agreement_reference, org_id
from pa_agreements_all
where substr(pm_agreement_reference, (-1 * (length + 1)), length + 1) = '-' || TO_CHAR(p_funding_source_id)
and pm_product_code = G_PRODUCT_CODE;
l_api_name CONSTANT VARCHAR2(40) := 'delete_funding';
OKE_FUNDINGSOURCE_PVT.delete_row(x_rowid => l_rowid ,
x_pool_party_id => l_pool_party_id
);
PA_AGREEMENT_PUB.delete_agreement(p_api_version_number => p_api_version ,
p_commit => OKE_API.G_FALSE ,
p_init_msg_list => OKE_API.G_FALSE ,
p_msg_count => p_msg_count ,
p_msg_data => p_msg_data ,
p_return_status => p_return_status ,
p_pm_product_code => G_PRODUCT_CODE ,
p_pm_agreement_reference => l_agreement.pm_agreement_reference ,
p_agreement_id => null
);
END delete_funding;
-- p_allow_currency_update IN VARCHAR2 ,
-- p_functional_currency_code IN VARCHAR2 ,
p_funding_source_id OUT NOCOPY NUMBER ,
p_return_status OUT NOCOPY VARCHAR2
) is
cursor c_project_funding is
select *
from pa_project_fundings
where agreement_id = p_agreement_id
order by allocated_amount desc;
select start_date_active, end_date_active
from oke_pool_parties
where pool_party_id = p_pool_party_id;
select nvl(allow_funding_across_ou_flag, 'N'),
currency_code
from pa_implementations_all p,
gl_sets_of_books g
where nvl(org_id, -99) = nvl(p_org_id, -99)
and g.set_of_books_id = p.set_of_books_id;
select 'x'
from pa_project_fundings f,
pa_projects_all p
where nvl(p.org_id, -99) <> nvl(p_org_id, -99)
and f.project_id = p.project_id
and f.agreement_id = p_agreement_id;
l_num_update_flag VARCHAR2(1);
l_num_update_flag := 'Y';
l_num_update_flag := 'N';
update_agreement_reference(p_agreement_id => p_agreement_id ,
p_org_id => p_org_id ,
p_currency_code => p_agreement_currency ,
p_funding_source_id => l_funding_out_rec.funding_source_id ,
p_num_update_flag => l_num_update_flag );
update_proj_fld_reference(p_project_funding_id => l_allocation_in_rec.project_funding_id ,
p_fund_allocation_id => l_allocation_out_rec.fund_allocation_id
);
OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => l_allocation_out_rec.fund_allocation_id ,
x_version_add => 1 ,
x_commit => OKE_API.G_FALSE
);
update_funding(p_api_version => p_api_version ,
p_init_msg_list => OKE_API.G_FALSE ,
p_msg_count => p_msg_count ,
p_msg_data => p_msg_data ,
p_funding_in_rec => l_funding_in_rec ,
p_funding_out_rec => l_funding_out_rec ,
p_return_status => p_return_status
);
update oke_k_funding_sources
set initial_amount = p_amount,
funding_across_ou = decode(l_dummy, 'x', 'Y')
where funding_source_id = l_funding_out_rec.funding_source_id;
update oke_k_fund_allocations
set pa_flag = 'Y'
where funding_source_id = l_funding_out_rec.funding_source_id;
OKE_FUNDING_UTIL_PKG.update_source_flag(x_funding_source_id => l_funding_out_rec.funding_source_id ,
x_commit => OKE_API.G_FALSE
);
select *
from pa_project_fundings
where agreement_id = p_agreement_id
order by allocated_amount desc;
select *
from oke_agreements_v
where agreement_id = p_agreement_id;
select nvl(allow_funding_across_ou_flag, 'N'),
currency_code
from pa_implementations_all p,
gl_sets_of_books g
where nvl(org_id, -99) = nvl(x_org_id, -99)
and g.set_of_books_id = p.set_of_books_id;
select 'x'
from pa_project_fundings f,
pa_projects_all p
where nvl(p.org_id, -99) <> nvl(x_org_id, -99)
and f.project_id = p.project_id
and f.agreement_id = p_agreement_id;
cursor c_update (x_org_id number) is
select 'x'
from pa_project_fundings f,
pa_projects_all p
where p.project_id = f.project_id
and f.agreement_id = p_agreement_id
and nvl(p.org_id, -99) <> nvl(x_org_id, -99);
l_num_update_flag VARCHAR2(1);
l_num_update_flag := 'Y';
l_num_update_flag := 'N';
OPEN c_update(l_agreement.org_id);
FETCH c_update into l_dummy;
CLOSE c_update;
l_update_flag := 'N';
IF (l_update_flag = 'N') AND
(l_agreement.agreement_currency_code <> p_source_currency) THEN
OKE_API.set_message(p_app_name => G_APP_NAME ,
p_msg_name => 'OKE_API_INVALID_VALUE' ,
p_token1 => 'VALUE' ,
p_token1_value => 'p_source_currency'
);
--p_update_flag => l_update_flag ,
p_currency_code => l_pool_currency ,
p_start_date => l_start_date
);
IF (l_update_flag = 'N') THEN
IF (p_pa_conversion_type is not null) THEN
OKE_API.set_message(p_app_name => G_APP_NAME ,
p_msg_name => 'OKE_API_INVALID_VALUE' ,
p_token1 => 'VALUE' ,
p_token1_value => 'pa_conversion_type'
);
update_agreement_reference(p_agreement_id => p_agreement_id ,
p_org_id => l_agreement.org_id ,
p_currency_code => l_agreement.agreement_currency_code ,
p_funding_source_id => l_funding_out_rec.funding_source_id ,
p_num_update_flag => l_num_update_flag );
update_proj_fld_reference(p_project_funding_id => l_allocation_in_rec.project_funding_id ,
p_fund_allocation_id => l_allocation_out_rec.fund_allocation_id
);
OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => l_allocation_out_rec.fund_allocation_id ,
x_version_add => 1 ,
x_commit => OKE_API.G_FALSE
);
update_funding(p_api_version => p_api_version ,
p_init_msg_list => OKE_API.G_FALSE ,
p_msg_count => p_msg_count ,
p_msg_data => p_msg_data ,
p_funding_in_rec => l_funding_in_rec ,
p_funding_out_rec => l_funding_out_rec ,
p_return_status => p_return_status
);
update oke_k_funding_sources
set initial_amount = l_agreement.amount,
funding_across_ou = decode(l_dummy, 'x', 'Y')
where funding_source_id = l_funding_out_rec.funding_source_id;
update oke_k_fund_allocations
set pa_flag = 'Y'
where funding_source_id = l_funding_out_rec.funding_source_id;
OKE_FUNDING_UTIL_PKG.update_source_flag(x_funding_source_id => l_funding_out_rec.funding_source_id ,
x_commit => OKE_API.G_FALSE
);