[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'
UNION /* BUG 14216205 : Added the union for SAT */
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_self_assessed_tax_dist_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 A.line_type_lookup_code='NONREC_TAX'
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' )
union /* BUG 14216205 : Added the union for SAT */
select 1
FROM AP_SELF_ASSESSED_TAX_DIST_ALL b,
PA_PROJECTS_ALL P,
GMS_PROJECT_TYPES_ALL GPT
WHERE b.invoice_id = p_invoice_id
AND b.project_id = p.project_id
AND P.PROJECT_TYPE = GPT.PROJECT_TYPE
AND GPT.SPONSORED_FLAG = 'Y'
AND b.LINE_TYPE_LOOKUP_CODE='NONREC_TAX'
AND NOT EXISTS
(SELECT 1
FROM GMS_AWARD_DISTRIBUTIONS ADL
WHERE ADL.INVOICE_ID = p_invoice_id
AND adl.distribution_line_number = b.distribution_line_number
AND ADL.DOCUMENT_TYPE = 'AP'
AND adl.award_set_id = NVL(b.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
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
UNION
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
FROM ap_self_assessed_tax_dist_all A,
GMS_AWARD_DISTRIBUTIONS ADL
WHERE a.invoice_id = p_invoice_id
AND ADL.AWARD_SET_ID = a.AWARD_ID
AND line_type_lookup_code='NONREC_TAX'
and ADL.ADL_LINE_NUM = 1
AND a.award_id IS NOT NULL;
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
and ADL2.ADL_STATUS = 'I'
union
select adl2.award_set_id
from gms_award_distributions adl2,
AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
where apsat.invoice_id = p_invoice_id
and APSAT.AWARD_ID is not null
and line_type_lookup_code='NONREC_TAX'
and adl2.award_set_id = apsat.award_id
and adl2.invoice_id = apsat.invoice_id
and adl2.document_type = 'AP'
and adl2.distribution_line_number = apsat.distribution_line_number
and ADL2.INVOICE_DISTRIBUTION_ID = APSAT.INVOICE_DISTRIBUTION_ID
and ADL2.ADL_STATUS = 'I';
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
and adl2.adl_status = 'A'
UNION
select ADL2.AWARD_SET_ID
from GMS_AWARD_DISTRIBUTIONS ADL2,
AP_SELF_ASSESSED_TAX_DIST_ALL APSAT
where apsat.invoice_id = p_invoice_id
and APSAT.AWARD_ID is not null
and adl2.award_set_id <> apsat.award_id
and adl2.invoice_id = apsat.invoice_id
and adl2.document_type = 'AP'
and adl2.distribution_line_number = apsat.distribution_line_number
and ADL2.INVOICE_DISTRIBUTION_ID = APSAT.INVOICE_DISTRIBUTION_ID
and adl2.adl_status = 'A' ;
select a.invoice_distribution_id
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'
union
select b.INVOICE_DISTRIBUTION_ID
from AP_SELF_ASSESSED_TAX_DIST_ALL B,
pa_projects_all p,
gms_project_types gpt
where b.invoice_id = p_invoice_id
and b.project_id = p.project_id
and b.award_id is not NULL
and P.PROJECT_TYPE = GPT.PROJECT_TYPE
and gpt.sponsored_flag = 'N';
/*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_last_update_date ,
l_creation_date ,
l_last_updated_by ,
l_created_by ,
L_LAST_UPDATE_LOGIN ,
L_NEW_AWARD_SET_ID;
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.
*/
/* BUG 14216205 : Commented the below code : Starts */
/*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) ;
UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL apsat
set AWARD_ID = null
where APSAT.INVOICE_ID = P_INVOICE_ID
and apsat.invoice_distribution_id = l_dummy_tab(j) ;
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
union /* BUG 14216205 : Added the union for SAT */
select award_id from AP_SELF_ASSESSED_TAX_DIST_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) ;
update AP_SELF_ASSESSED_TAX_DIST_ALL
set AWARD_ID = L_NEW_AWARD_SET_ID(J)
where INVOICE_ID = L_INVOICE_ID(J)
and DISTRIBUTION_LINE_NUMBER = L_DISTRIBUTION_LINE_NUMBER(J)
and INVOICE_DISTRIBUTION_ID = L_INVOICE_DISTRIBUTION_ID(J) ;
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
union /* BUG 14216205 : Added the union for SAT */
select award_id from ap_self_assessed_tax_dist_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 ;