[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from gms_award_distributions adl
where expenditure_item_id = x_expenditure_item_id
and cdl_line_num = x_cdl_line_num
and document_type = 'EXP'
and adl_status = 'A' ;
select erl.reference_1 expenditure_item_id,
erl.reference_2 CDL_LINE_NUM,
erl.project_id PROJECT_ID,
erl.task_id TASK_ID,
erl.distribution_line_number distribution_line_number,
apd.invoice_distribution_id invoice_distribution_id,
apd.award_id award_set_id
from ap_expense_report_lines_all erl,
ap_invoice_distributions_all apd,
pa_projects_all p,
gms_project_types gpt
where report_header_id = X_report_header_id
and erl.distribution_line_number= apd.distribution_line_number
and apd.invoice_id = X_invoice_id
and erl.project_id = apd.project_id
and erl.task_id = apd.task_id
and erl.expenditure_type = apd.expenditure_type
and erl.project_id = p.project_id
and p.project_type = gpt.project_type
and NVL(gpt.sponsored_flag,'N') = 'Y' ;
update gms_award_distributions
set adl_status = 'I'
where award_set_id = NVL(c_er_rec.award_set_id,0)
and document_type = 'AP'
and adl_status = 'A'
and invoice_id = X_invoice_id
and distribution_line_number= C_ER_REC.distribution_line_number
and invoice_distribution_id = c_er_rec.invoice_distribution_id;
update ap_invoice_distributions_all
set award_id = X_adl_rec.award_set_id
where invoice_id = X_invoice_id
and distribution_line_number = X_adl_rec.distribution_line_number
and invoice_distribution_id = X_adl_rec.invoice_distribution_id ;
p_default_last_updated_by IN NUMBER DEFAULT NULL,
p_default_last_update_login IN NUMBER DEFAULT NULL,
p_calling_sequence IN VARCHAR2 DEFAULT NULL,
p_award_id IN OUT NOCOPY NUMBER,
P_EVENT IN varchar2 ) return BOOLEAN
IS
lb_return BOOLEAN ;
delete from gms_award_distributions
where award_set_id = p_award_id
and adl_line_num = 1
and document_type = 'OPI' ;
SELECT *
from gms_award_distributions ADL
where award_set_id = p_award_id
and adl_status = 'A'
and adl_line_num = 1 -- AP Lines uptake
and document_type= 'APD' ;
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.last_update_date LAST_UPDATE_DATE,
A.creation_date CREATION_DATE,
A.last_updated_by LAST_UPDATED_BY,
A.created_by CREATED_BY,
A.last_update_login LAST_UPDATE_LOGIN
from ap_invoice_distributions_all A,
pa_projects_all B,
gms_project_types C
where invoice_id = p_invoice_id
and distribution_line_number = p_distribution_line_number
and invoice_distribution_id = p_invoice_distribution_id
and a.project_id = b.project_id
and b.project_type = c.project_type
and c.sponsored_flag = 'Y' ;
l_adl_rec.last_update_date := loop_ap_rec.last_update_date ;
l_adl_rec.last_updated_by := loop_ap_rec.last_updated_by ;
l_adl_rec.last_update_login := loop_ap_rec.last_update_login ;
update ap_invoice_distributions_all
set award_id = l_adl_rec.award_set_id
where invoice_id = loop_ap_rec.invoice_id
and distribution_line_number = loop_ap_rec.distribution_line_number
and invoice_distribution_id = loop_ap_rec.invoice_distribution_id ;
update ap_invoice_distributions_all
set award_id = l_adl_rec.award_set_id
where invoice_id = p_invoice_id
and distribution_line_number = p_distribution_line_number
and invoice_distribution_id = p_invoice_distribution_id ;
select award_id
from gms_award_distributions ADL
where award_set_id = p_award_set_id
and adl_line_num = 1 ;
select award_id
from ap_invoice_distributions_all
where Invoice_distribution_id = p_prepay_dist_id ;
select *
from gms_award_distributions
where award_set_id = X_award_set_id
and adl_line_num = 1 ;
select *
from ap_invoice_distributions_all
where invoice_id = p_invoice_id
and distribution_line_number = p_next_dist_line_num
and invoice_distribution_id = p_invoice_distribution_id; -- AP Line change: added additional join
UPDATE GMS_AWARD_DISTRIBUTIONS
SET BUD_TASK_ID = x_adl_rec.BUD_TASK_ID
WHERE AWARD_SET_ID = x_adl_rec.award_set_id
AND ADL_STATUS = 'A' ;
Update ap_invoice_distributions_all
Set award_id = x_adl_rec.award_set_id
Where invoice_distribution_id = x_inv_dist_id ;
select adl.award_id
from ap_distribution_set_lines apd,
gms_award_distributions adl
where apd.distribution_set_id = p_distribution_set_id
and apd.distribution_set_line_number = p_distribution_set_line_number
and adl.award_set_id = p_award_set_id
and apd.award_id = adl.award_set_id
and adl.adl_line_num = 1 ;
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.invoice_distribution_id = A.invoice_distribution_id -- AP Lines uptake
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;
update gms_award_distributions adl
set adl.adl_status = 'A'
where adl.document_type = 'AP'
and adl.adl_status = 'I'
and adl.award_set_id in ( select adl2.award_set_id
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 -- added join for AP Lines uptake
and adl2.adl_status = 'I' ) ;
update gms_award_distributions adl
set adl.adl_status = 'I'
where adl.document_type = 'AP'
and adl.adl_status = 'A'
and adl.award_set_id in ( select adl2.award_set_id
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 -- added join for AP Lines uptake
and adl2.adl_status = 'A' ) ;
** 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_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;
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) ;
select award_id
from gms_award_distributions
where award_set_id = x_award_id
and adl_line_num = 1 ;
select award_id
from gms_award_distributions ADL
where award_set_id = p_award_set_id
and adl_line_num = 1 ;