[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_number(term_value_pk1)
from oke_k_terms
where term_code = 'RA_PAYMENT_TERMS'
and k_header_id = p_object_id
and k_line_id is null;
select *
from oke_k_fund_allocations
where fund_allocation_id = p_allocation_in_rec.fund_allocation_id;
select currency_code
from gl_sets_of_books g,
pa_implementations_all p
where nvl(p_org_id, -99) = nvl(p.org_id, -99)
and p.set_of_books_id = g.set_of_books_id;
select col.data_length
from user_synonyms syn,
all_tab_columns col
where syn.synonym_name = 'PA_AGREEMENTS_ALL'
and col.owner = b_owner
and col.table_name = syn.table_name
AND col.column_name = 'AGREEMENT_NUM';
select data_length
from all_tab_columns
where table_name = 'PA_AGREEMENTS_ALL'
and column_name = 'AGREEMENT_NUM'
and owner = b_owner; */
select org_id
from pa_agreements_all
where agreement_id = p_agreement_id;
select *
from pa_project_fundings
where pm_product_code = G_PRODUCT_CODE
and pm_funding_reference = to_char(p_fund_allocation_id) || '.' || to_char(p_version)
FOR UPDATE OF project_funding_id NOWAIT;
select nvl(minimum_accountable_unit, power(10, -1 * precision))
from fnd_currencies f
where f.currency_code = p_agreement_currency;
select currency_code
from oke_k_funding_sources
where funding_source_id = p_allocation_in_rec.funding_source_id;
select pa_conversion_rate,
pa_conversion_date,
pa_conversion_type
from oke_k_fund_allocations
where fund_allocation_id = p_allocation_in_rec.fund_allocation_id;
select org_id,
agreement_currency_code
from pa_agreements_all a
where a.agreement_id = p_allocation_in_rec.agreement_id;
select 'x'
from pa_agreement_types
where UPPER(agreement_type) = UPPER(p_agreement_type);
select 'x'
from hz_cust_accounts c,
hz_parties p
where p.party_id = c.party_id
and p.party_number = p_customer_num
and c.cust_account_id = p_customer_id;
select 'x'
from hz_cust_accounts
where party_id = p_k_party_id
and cust_account_id = p_customer_id;
select 'x'
from oke_k_fund_allocations
where funding_source_id = p_funding_source_id;
select 'x'
from oke_k_fund_allocations
where funding_source_id = p_funding_source_id
and project_id is null
and (amount <> 0 or agreement_version is not null);
select *
from pa_agreements_all
where agreement_id = p_agreement_in_rec.agreement_id;
PROCEDURE upd_insert_agreement(p_agreement_in_rec PA_AGREEMENT_PUB.AGREEMENT_REC_IN_TYPE ,
p_agreement_tbl AGREEMENT_TBL_TYPE ,
p_pa_agreement_tbl AGREEMENT_TBL_TYPE ,
p_funding_source_id NUMBER ,
-- p_insert_flag VARCHAR2 ,
p_funding_amount NUMBER ,
p_agreement_out_tbl OUT NOCOPY PA_AGREEMENT_TBL_TYPE ,
p_pa_agreement_out_tbl OUT NOCOPY PA_AGREEMENT_TBL_TYPE ,
p_api_version NUMBER ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_return_status OUT NOCOPY VARCHAR2
) is
cursor c_total(x_org_id NUMBER) is
select sum(nvl(f.allocated_amount, 0)), p.agreement_id
from pa_project_fundings f,
pa_agreements_all p
where p.agreement_id = f.agreement_id
and p.pm_product_code = G_PRODUCT_CODE
and p.pm_agreement_reference = x_org_id || '-N-' || p_funding_source_id
group by p.agreement_id;
select sum(nvl(f.allocated_amount, 0))
from pa_project_fundings f
where f.agreement_id = x_agreement_id
group by f.agreement_id;
select count(1)
from pa_agreements_all
where pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * x_length, x_length) = '-' || to_char(p_funding_source_id);
cursor c_update_agreement (x_length NUMBER) is
select sum(nvl(f.allocated_amount, 0)) amount, pm_agreement_reference, p.agreement_id, org_id
from pa_project_fundings f,
pa_agreements_all p
where p.agreement_id = f.agreement_id
and p.pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * x_length, x_length) = '-' || to_char(p_funding_source_id)
group by p.agreement_id, pm_agreement_reference, org_id;
select count(distinct org_id)
from pa_projects_all
where project_id in
(select distinct project_id
from oke_k_fund_allocations
where funding_source_id = p_funding_source_id
and nvl(pa_flag, 'N') = 'N'
);
select count(distinct org_id)
from pa_projects_all
where project_id in
(select distinct project_id
from oke_k_fund_allocations
where funding_source_id = p_funding_source_id
);
select sum(nvl(amount, 0))
from oke_k_fund_allocations
where funding_source_id = p_funding_source_id;
l_update_flag VARCHAR2(1);
l_update c_update_agreement%ROWTYPE;
l_update_flag := 'N';
l_update_flag := 'N';
l_update_flag := 'Y';
l_update_flag := 'N';
IF (l_update_flag = 'Y') OR
(p_agreement_tbl.COUNT = 0) OR
(p_pa_agreement_tbl.COUNT = 1) THEN
FOR l_update in c_update_agreement(l_length + 1) LOOP
l_agreement_in_rec.agreement_id := l_update.agreement_id;
l_agreement_in_rec_new.amount := (l_update.amount/l_allocated_amount) * p_funding_amount;
l_agreement_in_rec_new.amount := l_update.amount;
l_agreement_in_rec_new.pm_agreement_reference := l_update.pm_agreement_reference;
IF (nvl(l_org_id_vc, -99) <> nvl(l_update.org_id, -99)) THEN
l_agreement_in_rec_new.owning_organization_id := null;
IF (l_update.org_id is not null) THEN
fnd_client_info.set_org_context(l_update.org_id);
(l_update.org_id = nvl(l_pa_org_id, -99)) THEN
IF (p_pa_agreement_tbl.COUNT <> 0) AND
(nvl(l_update.org_id, -99) = p_pa_agreement_tbl(1).object_id) AND
(l_update.agreement_id <> p_pa_agreement_tbl(1).agreement_id) THEN
l_agreement_in_rec_new.agreement_num := l_agreement_in_rec_new.agreement_num || '*';
PA_AGREEMENT_PUB.update_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_agreement_in_rec => l_agreement_in_rec_new ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
PA_AGREEMENT_PUB.update_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 => OKE_FUNDING_PUB.G_PRODUCT_CODE ,
p_agreement_in_rec => l_agreement_in_rec_new ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
PA_AGREEMENT_PUB.update_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 => OKE_FUNDING_PUB.G_PRODUCT_CODE ,
p_agreement_in_rec => l_agreement_in_rec_new ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
FOR l_update in c_update_agreement(l_length) LOOP
l_agreement_in_rec.agreement_id := l_update.agreement_id;
l_agreement_in_rec.pm_agreement_reference := l_update.pm_agreement_reference;
IF (l_update.org_id is not null) THEN
fnd_client_info.set_org_context(l_update.org_id);
PA_AGREEMENT_PUB.update_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_agreement_in_rec => l_agreement_in_rec_new ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
END upd_insert_agreement;
PROCEDURE update_pa_agreement(p_agreement_in_rec PA_AGREEMENT_PUB.AGREEMENT_REC_IN_TYPE ,
p_agreement_tbl AGREEMENT_TBL_TYPE ,
p_funding_source_id NUMBER ,
p_funding_amount NUMBER ,
p_agreement_out_tbl OUT NOCOPY PA_AGREEMENT_TBL_TYPE ,
p_api_version NUMBER ,
p_msg_count OUT NOCOPY NUMBER ,
p_msg_data OUT NOCOPY VARCHAR2 ,
p_return_status OUT NOCOPY VARCHAR2
) is
cursor c_agreement_count (x_length NUMBER) is
select count(1)
from pa_agreements_all
where pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * x_length, x_length) = to_char(p_funding_source_id);
cursor c_update_agreement (x_length NUMBER) is
select sum(nvl(f.allocated_amount, 0)) amount, pm_agreement_reference, p.agreement_id, org_id
from pa_project_fundings f,
pa_agreements_all p
where p.agreement_id = f.agreement_id
and p.pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * x_length, x_length) = 'Y-' || to_char(p_funding_source_id)
group by p.agreement_id, pm_agreement_reference, org_id;
select sum(nvl(amount, 0))
from oke_k_fund_allocations
where funding_source_id = p_funding_source_id;
l_update c_update_agreement%ROWTYPE;
OPEN c_update_agreement(l_length + 2);
FETCH c_update_agreement into l_update;
IF (c_update_agreement%NOTFOUND) THEN
return;
CLOSE c_update_agreement;
l_agreement_in_rec.agreement_id := l_update.agreement_id;
l_agreement_in_rec_new.amount := l_update.amount;
l_agreement_in_rec_new.amount := p_agreement_tbl(l_update.org_id).total_amount;
IF (p_agreement_tbl(l_update.org_id).org_total_amount <> 0) THEN
l_agreement_in_rec_new.amount := (p_agreement_tbl(l_update.org_id).total_amount/p_agreement_tbl(l_update.org_id).org_total_amount) * p_funding_amount;
l_agreement_in_rec_new.pm_agreement_reference := l_update.pm_agreement_reference;
IF (nvl(l_org_id_vc, -99) <> nvl(l_update.org_id, -99)) THEN
l_agreement_in_rec_new.owning_organization_id := null;
IF (l_update.org_id is not null) THEN
fnd_client_info.set_org_context(l_update.org_id);
PA_AGREEMENT_PUB.update_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_agreement_in_rec => l_agreement_in_rec_new ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
l_agreement_in_rec_new.amount := (p_agreement_tbl(l_update.org_id).total_amount
+ p_agreement_tbl(l_update.org_id).negative_amount);
l_agreement_in_rec_new.amount := ((p_agreement_tbl(l_update.org_id).total_amount
+ p_agreement_tbl(l_update.org_id).negative_amount)/p_agreement_tbl(l_update.org_id).org_total_amount) * p_funding_amount;
p_agreement_out_tbl(l_update.org_id) := l_agreement_in_rec_new;
END update_pa_agreement;
PROCEDURE pa_update_or_add(p_fund_allocation_id NUMBER ,
p_new_amount NUMBER ,
p_version OUT NOCOPY NUMBER ,
p_diff_amount OUT NOCOPY NUMBER ,
p_add_flag OUT NOCOPY VARCHAR2
) is
cursor c_sum (length NUMBER) is
select sum(nvl(allocated_amount, 0)), max(project_funding_id)
from pa_project_fundings
where pm_product_code = G_PRODUCT_CODE
and substr(pm_funding_reference, 1, length + 1) = to_char(p_fund_allocation_id) || '.';
select nvl(allocated_amount, 0), budget_type_code, pm_funding_reference
from pa_project_fundings
where project_funding_id = x_project_funding_id;
END pa_update_or_add;
select distinct
nvl(p.org_id, -99) org_id,
a.multi_currency_billing_flag,
p.projfunc_currency_code
from oke_k_fund_allocations o,
pa_projects_all p,
pa_implementations_all a
where funding_source_id = p_funding_in_rec.funding_source_id
and o.project_id = p.project_id
and nvl(a.org_id, -99) = nvl(p.org_id, -99)
and o.amount <> 0
order by 1, 2;
select p.org_id,
o.pa_conversion_type,
o.pa_conversion_date,
o.pa_conversion_rate,
o.fund_allocation_id,
o.project_id,
o.task_id,
p.segment1 project_number,
o.amount,
p.multi_currency_billing_flag,
p.projfunc_currency_code
from oke_k_fund_allocations o,
pa_projects_all p
where funding_source_id = p_funding_in_rec.funding_source_id
and o.project_id = p.project_id
and nvl(p.org_id, -99) = x_org_id
and o.amount <> 0
-- order by o.project_id, task_id;
select o.fund_allocation_id,
o.funding_source_id,
o.project_id,
o.task_id,
o.amount,
a.agreement_id,
o.start_date_active,
o.funding_category
from oke_k_fund_allocations o,
pa_projects_all p,
pa_agreements_all a,
pa_implementations_all i
where funding_source_id = p_funding_in_rec.funding_source_id
and o.project_id = x_project_id
and o.project_id = p.project_id
and nvl(a.org_id, -99) = nvl(p.org_id, -99)
and nvl(a.org_id, -99) = nvl(i.org_id, -99)
and a.pm_agreement_reference = p.org_id || '-' || decode(i.multi_currency_billing_flag, 'N', p.projfunc_currency_code,
decode(p.multi_currency_billing_flag, 'Y', p_funding_in_rec.currency_code, p.projfunc_currency_code))
|| '-' || p_funding_in_rec.funding_source_id
and a.pm_product_code = G_PRODUCT_CODE
and o.amount <> 0
order by o.project_id, o.task_id, o.amount desc;
select o.fund_allocation_id,
o.funding_source_id,
o.project_id,
o.task_id,
o.amount,
a.agreement_id,
o.start_date_active ,
o.funding_category
from oke_k_fund_allocations o,
pa_projects_all p,
pa_agreements_all a,
pa_implementations_all i
where funding_source_id = p_funding_in_rec.funding_source_id
and o.project_id = p.project_id
and o.project_id = x_project_id
and nvl(a.org_id, -99) = nvl(i.org_id, -99)
and nvl(a.org_id, -99) = nvl(p.org_id, -99)
and a.pm_agreement_reference = p.org_id || '-' || decode(i.multi_currency_billing_flag, 'N', p.projfunc_currency_code,
decode(p.multi_currency_billing_flag, 'Y', p_funding_in_rec.currency_code, p.projfunc_currency_code))
|| '-' || p_funding_in_rec.funding_source_id
and a.pm_product_code = G_PRODUCT_CODE
and o.amount <> 0
order by o.project_id, o.task_id desc, o.amount desc;
PA_AGREEMENT_PUB.update_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 => OKE_FUNDING_PUB.G_PRODUCT_CODE ,
p_agreement_in_rec => l_agreement_in_rec ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
OKE_FUNDING_UTIL_PKG.update_source_flag(x_funding_source_id => p_funding_in_rec.funding_source_id ,
x_commit => OKE_API.G_FALSE
);
PROCEDURE update_agreement(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_agreement_type IN VARCHAR2 ,
p_funding_in_rec IN OKE_FUNDSOURCE_PVT.FUNDING_REC_IN_TYPE ,
-- p_allocation_in_tbl IN OKE_ALLOCATION_PVT.ALLOCATION_IN_TBL_TYPE ,
p_return_status OUT NOCOPY VARCHAR2 ,
p_receivables_term_id IN NUMBER DEFAULT NULL /*skuchima bug 14344021 */
) is
cursor c_project is
select f.amount,
f.project_id,
f.task_id,
org_id,
p.segment1 project_number
from oke_k_fund_allocations f,
pa_projects_all p
where funding_source_id = p_funding_in_rec.funding_source_id
and f.project_id = p.project_id
order by p.project_id;
select nvl(org_id, -99) org_id,
agreement_id,
pm_agreement_reference,
agreement_num,
agreement_currency_code
from pa_agreements_all
where pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * (length(p_funding_in_rec.funding_source_id) + 1), length(p_funding_in_rec.funding_source_id) + 1)
= '-' || p_funding_in_rec.funding_source_id;
select count(1)
from pa_agreements_all
where pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * (length(p_funding_in_rec.funding_source_id) + 1), length(p_funding_in_rec.funding_source_id) + 1)
= '-' || p_funding_in_rec.funding_source_id;
select nvl(org_id, -99) org_id,
p.agreement_id,
a.pm_agreement_reference,
a.agreement_num,
sum(p.allocated_amount) agreement_sum,
a.agreement_currency_code
from pa_agreements_all a,
pa_project_fundings p
where a.pm_product_code = G_PRODUCT_CODE
and a.agreement_id = p.agreement_id
and substr(pm_agreement_reference, -1 * (length(p_funding_in_rec.funding_source_id) + 1), length(p_funding_in_rec.funding_source_id) + 1)
= '-' || p_funding_in_rec.funding_source_id
group by p.agreement_id, a.pm_agreement_reference, a.agreement_num, a.agreement_currency_code, org_id;
select nvl(org_id, -99) org_id,
agreement_id
from pa_agreements_all
where pm_product_code = G_PRODUCT_CODE
and nvl(org_id, -99) = x_org_id
and pm_agreement_reference = org_id || '-' || x_currency || '-' || p_funding_in_rec.funding_source_id;
select nvl(org_id, -99) org_id,
agreement_id
from pa_agreements_all
where pm_product_code = G_PRODUCT_CODE
and substr(pm_agreement_reference, -1 * (length(p_funding_in_rec.funding_source_id) + 1), length(p_funding_in_rec.funding_source_id) + 1)
= '-' || p_funding_in_rec.funding_source_id;
select multi_currency_billing_flag
from pa_implementations_all
where nvl(org_id, -99) = nvl(x_org_id, -99);
select distinct
org_id org_id,
null multi_currency_billing_flag,
null projfunc_currency_code,
p.agreement_id agreement_id,
f.fund_allocation_id ,
f.funding_source_id,
f.start_date_active,
f.project_id,
f.task_id,
f.amount,
f.funding_category
from oke_k_fund_allocations f,
pa_project_fundings p,
pa_agreements_all a
where funding_source_id = p_funding_in_rec.funding_source_id
and f.project_id = x_project_id
and nvl(insert_update_flag, 'N') = 'Y'
and p.pm_product_code = G_PRODUCT_CODE
and p.project_id = x_project_id
and a.agreement_id = p.agreement_id
and substr(pm_funding_reference, 1, length(f.fund_allocation_id) + 1) = f.fund_allocation_id || '.'
and agreement_version is not null
-- and nvl(f.pa_flag, 'N') <> 'Y'
-- order by f.project_id, f.task_id asc, f.amount desc;
select distinct
org_id org_id,
p.multi_currency_billing_flag multi_currency_billing_flag,
p.projfunc_currency_code projfunc_currency_code,
-99 agreement_id,
f.fund_allocation_id,
f.funding_source_id,
f.start_date_active,
f.project_id,
f.task_id,
f.amount,
f.funding_category
from oke_k_fund_allocations f,
pa_projects_all p
where funding_source_id = p_funding_in_rec.funding_source_id
and f.project_id = x_project_id
and nvl(insert_update_flag, 'N') = 'Y'
and agreement_version is null
and f.amount <> 0
and f.project_id = p.project_id
-- and nvl(f.pa_flag, 'N') <> 'Y';
select distinct
org_id org_id,
-99 agreement_id,
p.multi_currency_billing_flag,
p.projfunc_currency_code,
f.fund_allocation_id,
f.funding_source_id,
f.project_id,
f.task_id,
f.start_date_active,
f.amount,
f.funding_category
from oke_k_fund_allocations f,
pa_projects_all p
where funding_source_id = p_funding_in_rec.funding_source_id
and f.project_id = x_project_id
and p.project_id = x_project_id
and nvl(insert_update_flag, 'N') = 'Y'
and agreement_version is null
and f.amount <> 0
-- and nvl(f.pa_flag, 'N') <> 'Y';
select distinct
org_id org_id,
p.agreement_id,
null multi_currency_billing_flag,
null projfunc_currency_code,
f.fund_allocation_id,
f.funding_source_id,
f.project_id,
f.task_id,
f.start_date_active,
f.amount,
f.funding_category
from oke_k_fund_allocations f,
pa_project_fundings p,
pa_agreements_all a
where funding_source_id = p_funding_in_rec.funding_source_id
and f.project_id = x_project_id
and nvl(insert_update_flag, 'N') = 'Y'
and f.project_id = p.project_id
and a.agreement_id = p.agreement_id
and p.pm_product_code = G_PRODUCT_CODE
and substr(pm_funding_reference, 1, length(f.fund_allocation_id) + 1) = fund_allocation_id || '.'
and agreement_version is not null
-- and nvl(f.pa_flag, 'N') <> 'Y'
-- order by f.project_id, f.task_id, f.amount desc;
select sum(amount)
from oke_k_fund_allocations
where funding_source_id = p_funding_in_rec.funding_source_id;
select nvl(funding_across_ou, 'N')
from oke_k_funding_sources
where funding_source_id = p_funding_in_rec.funding_source_id;
select p.segment1
from pa_projects_all p,
oke_k_fund_allocations f,
oke_k_funding_sources s
where p.project_id = f.project_id
and f.funding_source_id = p_funding_in_rec.funding_source_id
and f.amount <> 0
and f.agreement_version is null
and nvl(f.insert_update_flag, 'N') = 'Y'
and p.multi_currency_billing_flag = 'N'
and s.funding_source_id = p_funding_in_rec.funding_source_id
and s.currency_code <> p.projfunc_currency_code;
l_api_name VARCHAR2(20) := 'update_agreement';
PA_AGREEMENT_PUB.update_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 => OKE_FUNDING_PUB.G_PRODUCT_CODE ,
p_agreement_in_rec => l_agreement_in_rec ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
update_pa_funding(p_api_version => p_api_version ,
p_init_msg_list => OKE_API.G_FALSE ,
p_commit => OKE_API.G_FALSE ,
p_msg_count => p_msg_count ,
p_msg_data => p_msg_data ,
p_allocation_in_rec => l_allocation_in_rec ,
p_return_status => p_return_status
);
update_pa_funding(p_api_version => p_api_version ,
p_init_msg_list => OKE_API.G_FALSE ,
p_commit => OKE_API.G_FALSE ,
p_msg_count => p_msg_count ,
p_msg_data => p_msg_data ,
p_allocation_in_rec => l_allocation_in_rec ,
p_return_status => p_return_status
);
PA_AGREEMENT_PUB.update_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 => OKE_FUNDING_PUB.G_PRODUCT_CODE ,
p_agreement_in_rec => l_agreement_in_rec ,
p_agreement_out_rec => l_agreement_out_rec ,
p_funding_in_tbl => l_funding_in_tbl ,
p_funding_out_tbl => l_funding_out_tbl
);
END update_agreement;
PROCEDURE update_pa_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_allocation_in_rec IN OKE_ALLOCATION_PVT.ALLOCATION_REC_IN_TYPE ,
p_return_status OUT NOCOPY VARCHAR2
) is
cursor c_allocation is
select pa_conversion_rate,
pa_conversion_type,
pa_conversion_date,
p.org_id,
p.segment1 project_number--,
-- pa_flag
from oke_k_fund_allocations f,
pa_projects_all p
where fund_allocation_id = p_allocation_in_rec.fund_allocation_id
and f.project_id = p.project_id;
select a.agreement_currency_code,
nvl(a.org_id, -99),
s.currency_code
from oke_k_fund_allocations o,
pa_agreements_all a,
--pa_projects_all p,
oke_k_funding_sources s
-- pa_implementations_all i
where a.agreement_id = p_allocation_in_rec.agreement_id
-- and nvl(i.org_id, -99) = nvl(a.org_id, -99)
and s.funding_source_id = o.funding_source_id
-- and o.project_id = p.project_id
and o.fund_allocation_id = p_allocation_in_rec.fund_allocation_id;
select --p.multi_currency_billing_flag,
a.agreement_currency_code,
--p.projfunc_currency_code,
nvl(a.org_id, -99),
i.multi_currency_billing_flag
from oke_k_fund_allocations o,
pa_agreements_all a,
pa_projects_all p,
oke_k_funding_sources s,
pa_implementations_all i
where a.agreement_id = p_allocation_in_rec.agreement_id
and nvl(i.org_id, -99) = nvl(a.org_id, -99)
and s.funding_source_id = o.funding_source_id
and o.project_id = p.project_id
and o.fund_allocation_id = p_allocation_in_rec.fund_allocation_id
and a.pm_product_code = G_PRODUCT_CODE
and substr(a.pm_funding_reference, 1, x_length + 1) = to_char(p_allocation_in_rec.fund_allocation_id) || '.';
select *
from pa_project_fundings
where agreement_id = p_allocation_in_rec.agreement_id
and substr(pm_funding_reference, 1, length + 1) = to_char(p_allocation_in_rec.fund_allocation_id) || '.'
and pm_product_code = G_PRODUCT_CODE;
l_api_name VARCHAR2(20) := 'update_pa_funding';
pa_update_or_add(p_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
p_new_amount => l_amount ,
p_version => l_version ,
p_diff_amount => l_diff_amount ,
p_add_flag => l_add_flag
);
OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
x_version_add => 1 ,
x_commit => OKE_API.G_FALSE
);
PA_AGREEMENT_PUB.update_funding(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_funding_reference => to_char(l_allocation_in_rec.fund_allocation_id) || '.' || to_char(l_version) ,
p_funding_id => l_project_funding.project_funding_id ,
p_project_id => l_allocation_in_rec.project_id ,
p_task_id => l_allocation_in_rec.task_id ,
p_agreement_id => l_allocation_in_rec.agreement_id ,
p_allocated_amount => l_diff_amount ,
p_date_allocated => p_allocation_in_rec.start_date_active ,
p_desc_flex_name => G_PROJ_FUND_DESC_FLEX_NAME ,
p_attribute_category => l_allocation_in_rec.pa_attribute_category ,
p_attribute1 => l_allocation_in_rec.pa_attribute1 ,
p_attribute2 => l_allocation_in_rec.pa_attribute2 ,
p_attribute3 => l_allocation_in_rec.pa_attribute3 ,
p_attribute4 => l_allocation_in_rec.pa_attribute4 ,
p_attribute5 => l_allocation_in_rec.pa_attribute5 ,
p_attribute6 => l_allocation_in_rec.pa_attribute6 ,
p_attribute7 => l_allocation_in_rec.pa_attribute7 ,
p_attribute8 => l_allocation_in_rec.pa_attribute8 ,
p_attribute9 => l_allocation_in_rec.pa_attribute9 ,
p_attribute10 => l_allocation_in_rec.pa_attribute10 ,
p_funding_id_out => l_funding_id ,
p_project_rate_type => null ,
p_project_rate_date => null ,
p_project_exchange_rate => null ,
p_projfunc_rate_type => l_allocation_in_rec.pa_conversion_type ,
p_projfunc_rate_date => l_allocation_in_rec.pa_conversion_date ,
p_projfunc_exchange_rate => l_allocation_in_rec.pa_conversion_rate ,
p_funding_category => l_allocation_in_rec.funding_category
);
OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
x_version_add => 0 ,
x_commit => OKE_API.G_FALSE
);
PA_AGREEMENT_PUB.update_funding(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_funding_reference => l_project_funding.pm_funding_reference ,
p_funding_id => l_project_funding.project_funding_id ,
p_project_id => l_project_funding.project_id ,
p_task_id => l_project_funding.task_id ,
p_agreement_id => l_project_funding.agreement_id ,
p_allocated_amount => l_project_funding.allocated_amount ,
p_date_allocated => l_project_funding.date_allocated ,
p_desc_flex_name => G_PROJ_FUND_DESC_FLEX_NAME ,
p_attribute_category => l_allocation_in_rec.pa_attribute_category ,
p_attribute1 => l_allocation_in_rec.pa_attribute1 ,
p_attribute2 => l_allocation_in_rec.pa_attribute2 ,
p_attribute3 => l_allocation_in_rec.pa_attribute3 ,
p_attribute4 => l_allocation_in_rec.pa_attribute4 ,
p_attribute5 => l_allocation_in_rec.pa_attribute5 ,
p_attribute6 => l_allocation_in_rec.pa_attribute6 ,
p_attribute7 => l_allocation_in_rec.pa_attribute7 ,
p_attribute8 => l_allocation_in_rec.pa_attribute8 ,
p_attribute9 => l_allocation_in_rec.pa_attribute9 ,
p_attribute10 => l_allocation_in_rec.pa_attribute10 ,
p_funding_id_out => l_funding_id
);
update pa_project_fundings
set attribute_category = l_allocation_in_rec.pa_attribute_category
, attribute1 = l_allocation_in_rec.pa_attribute1
, attribute2 = l_allocation_in_rec.pa_attribute2
, attribute3 = l_allocation_in_rec.pa_attribute3
, attribute4 = l_allocation_in_rec.pa_attribute4
, attribute5 = l_allocation_in_rec.pa_attribute5
, attribute6 = l_allocation_in_rec.pa_attribute6
, attribute7 = l_allocation_in_rec.pa_attribute7
, attribute8 = l_allocation_in_rec.pa_attribute8
, attribute9 = l_allocation_in_rec.pa_attribute9
, attribute10 = l_allocation_in_rec.pa_attribute10
-- where project_funding_id = l_project_funding.project_funding_id;
END update_pa_funding;
update pa_project_fundings
set attribute_category = l_allocation_in_rec.pa_attribute_category
, attribute1 = l_allocation_in_rec.pa_attribute1
, attribute2 = l_allocation_in_rec.pa_attribute2
, attribute3 = l_allocation_in_rec.pa_attribute3
, attribute4 = l_allocation_in_rec.pa_attribute4
, attribute5 = l_allocation_in_rec.pa_attribute5
, attribute6 = l_allocation_in_rec.pa_attribute6
, attribute7 = l_allocation_in_rec.pa_attribute7
, attribute8 = l_allocation_in_rec.pa_attribute8
, attribute9 = l_allocation_in_rec.pa_attribute9
, attribute10 = l_allocation_in_rec.pa_attribute10
where pm_product_code = G_PRODUCT_CODE
and substr(pm_funding_reference, 1, length(l_allocation_in_rec.fund_allocation_id) + 1)
= to_char(l_allocation_in_rec.fund_allocation_id) || '.';
OKE_FUNDING_UTIL_PKG.update_alloc_version(x_fund_allocation_id => p_allocation_in_rec.fund_allocation_id ,
x_version_add => 1 ,
x_commit => OKE_API.G_FALSE
);