[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ins.award_id
from gms_installments ins,
gms_summary_project_fundings pf
where ins.installment_id = pf.installment_id
and pf.project_id = p_project_id
and ins.award_id = p_award_id ;
select ins.award_id
from gms_installments ins,
gms_summary_project_fundings pf
where ins.installment_id = pf.installment_id
and pf.project_id = p_project_id
and NOT EXISTS ( select 1 from gms_installments ins2,
gms_summary_project_fundings pf2
where ins2.installment_id = pf2.installment_id
and pf2.project_id = pf.project_id
and ins2.award_id <> ins.award_id ) ;
l_last_update_date gms_ap_type_date;
l_last_updated_by gms_ap_type_number;
l_last_update_login gms_ap_type_number;
SELECT A.invoice_id INVOICE_ID,
A.distribution_line_number distribution_line_number,
A.invoice_distribution_id invoice_distribution_id,
A.project_id PROJECT_ID,
A.task_id TASK_ID,
A.award_id award_set_id,
A.last_update_date LAST_UPDATE_DATE,
A.creation_date CREATION_DATE,
A.last_updated_by LAST_UPDATED_BY,
A.created_by CREATED_BY,
NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
inv.invoice_num invoice_num
from ap_invoice_distributions_all A,
pa_projects_all p,
gms_project_types gpt,
ap_invoices_all inv
where a.invoice_id = p_invoice_id
and a.project_id = p.project_id
and p.project_type = gpt.project_type
and inv.invoice_id = a.invoice_id
and gpt.sponsored_flag = 'Y' ;
select * from gms_award_distributions where award_set_id = l_award_set_idX
and adl_line_num = 1 ;
SELECT 1
from ap_invoice_distributions_all A,
pa_projects_all p,
gms_project_types gpt
where a.invoice_id = p_invoice_id
and a.project_id = p.project_id
and p.project_type = gpt.project_type
and gpt.sponsored_flag = 'Y'
and not exists ( select 1 from gms_award_distributions adl
where adl.invoice_id = p_invoice_id
and adl.distribution_line_number = A.distribution_line_number
and adl.document_type = 'AP'
and adl.award_set_id = NVL(a.award_id,0)
and adl.adl_line_num = 1
and adl.adl_status = 'A' ) ;
SELECT A.invoice_id INVOICE_ID,
A.distribution_line_number distribution_line_number,
A.invoice_distribution_id invoice_distribution_id,
A.project_id PROJECT_ID,
A.task_id TASK_ID,
ADL.award_id AWARD_ID,
A.award_id award_set_id,
A.last_update_date LAST_UPDATE_DATE,
A.creation_date CREATION_DATE,
A.last_updated_by LAST_UPDATED_BY,
A.created_by CREATED_BY,
NVL(A.last_update_login,0) LAST_UPDATE_LOGIN,
gms_awards_dist_pkg.get_award_set_id NEW_AWARD_SET_ID
BULK COLLECT INTO l_invoice_id,
l_distribution_line_number,
l_invoice_distribution_id,
l_project_id ,
l_task_id ,
l_award_id,
l_award_set_id,
l_last_update_date ,
l_creation_date ,
l_last_updated_by ,
l_created_by ,
l_last_update_login ,
l_new_award_set_id
from ap_invoice_distributions_all A,
gms_award_distributions adl
where a.invoice_id = p_invoice_id
and adl.award_set_id = a.award_id
and adl.adl_line_num = 1
and a.award_id IS NOT NULL;
l_dummy_tab.delete ;
select adl2.award_set_id
bulk collect into l_dummy_tab
from gms_award_distributions adl2,
ap_invoice_distributions_all apd
where apd.invoice_id = p_invoice_id
and apd.award_id is not null
and adl2.award_set_id = apd.award_id
and adl2.invoice_id = apd.invoice_id
and adl2.document_type = 'AP'
and adl2.distribution_line_number = apd.distribution_line_number
and adl2.invoice_distribution_id = apd.invoice_distribution_id
and adl2.adl_status = 'I' ;
UPDATE gms_award_distributions
SET adl_status = 'A'
where award_set_id = l_dummy_tab(i) ;
l_dummy_tab.delete ;
select adl2.award_set_id
bulk collect into l_dummy_tab
from gms_award_distributions adl2,
ap_invoice_distributions_all apd
where apd.invoice_id = p_invoice_id
and apd.award_id is not null
and adl2.award_set_id <> apd.award_id
and adl2.invoice_id = apd.invoice_id
and adl2.document_type = 'AP'
and adl2.distribution_line_number = apd.distribution_line_number
and adl2.invoice_distribution_id = apd.invoice_distribution_id
and adl2.adl_status = 'A' ;
UPDATE gms_award_distributions
SET adl_status = 'I'
where award_set_id = l_dummy_tab(i) ;
l_dummy_tab.delete ;
** Update statement was changed to bulk statement to resolve the share memory performance issue.
*/
select a.invoice_distribution_id
bulk collect into l_dummy_tab
from ap_invoice_distributions_all A,
pa_projects_all p,
gms_project_types gpt
where a.invoice_id = p_invoice_id
and a.project_id = p.project_id
and a.award_id is not NULL
and p.project_type = gpt.project_type
and gpt.sponsored_flag = 'N' ;
UPDATE ap_invoice_distributions_all apd
SET award_id = NULL
where apd.invoice_id = p_invoice_id
and apd.invoice_distribution_id = l_dummy_tab(i) ;
l_dummy_tab.delete ;
l_last_update_date(l_count) := ap_rec.last_update_date;
l_last_updated_by(l_count) := ap_rec.last_updated_by;
l_last_update_login(l_count) := ap_rec.last_update_login;
delete from gms_award_distributions
where invoice_id = p_invoice_id
and document_type = 'AP'
and award_set_id not in ( select award_id from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and award_id is not NULL ) ;
INSERT into gms_award_distributions ( award_set_id ,
adl_line_num,
document_type,
distribution_value,
project_id ,
task_id ,
award_id ,
request_id ,
adl_status ,
fc_status ,
line_type ,
capitalized_flag ,
capitalizable_flag ,
revenue_distributed_flag ,
billed_flag ,
bill_hold_flag ,
invoice_distribution_id ,
invoice_id ,
distribution_line_number ,
burdenable_raw_cost ,
cost_distributed_flag ,
last_update_date ,
last_updated_by ,
created_by ,
creation_date ,
last_update_login ,
billable_flag )
VALUES ( l_new_award_set_id(i) ,
1, --adl_line_num,
'AP' , --document_type,
100,
l_project_id(i) ,
l_task_id(i) ,
l_award_id(i) ,
l_distribution_line_number(i) ,
'A', --adl_status ,
'N', --fc_status ,
'R', --line_type ,
'N' ,
'N' ,
'N' ,
'N' ,
'N' ,
l_invoice_distribution_id(i), --invoice_distribution_id ,
l_invoice_id(i), --invoice_id ,
l_distribution_line_number(i), --distribution_line_number ,
NULL, --burdenable_raw_cost ,
'N' ,
l_last_update_date(i) ,
l_last_updated_by(i) ,
l_created_by(i) ,
l_creation_date(i) ,
l_last_update_login(i) ,
'N') ;
update ap_invoice_distributions_all
set award_id = l_new_award_set_id(k)
where invoice_id = l_invoice_id(k)
and distribution_line_number = l_distribution_line_number(k)
and invoice_distribution_id = l_invoice_distribution_id(k) ;
delete from gms_award_distributions
where invoice_id = p_invoice_id
and document_type = 'AP'
and award_set_id not in ( select award_id from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and award_id is not NULL ) ;
select award_id
from gms_award_distributions
where award_set_id = x_award_id
and adl_line_num = 1 ;