[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select award_number
from gms_awards_all
where award_id = X_award_id ;
select a.award_number
from gms_awards_all a,
gms_award_distributions adl
where adl.award_set_id = X_award_set_id
and adl_status = 'A'
and adl_line_num = 1
and adl.award_id = a.award_id ;
select a.award_number
from gms_awards_all a,
gms_award_distributions adl ,
po_req_distributions_all req
where adl.award_set_id = req.award_id
and req.distribution_id = X_req_distribution_id
and adl_status = 'A'
and adl_line_num = 1
and adl.award_id = a.award_id ;
SELECT default_dist_award_number
FROM gms_implementations
WHERE enabled ='Y'
and award_distribution_option = 'Y'
and default_dist_award_id = X_award_id ;
select award_id
from gms_awards_all
where award_number = X_award_number ;
select adl.award_id
from gms_award_distributions adl
where adl.award_set_id = X_award_set_id
and adl_status = 'A'
and adl_line_num = 1 ;
select adl.award_id
from gms_award_distributions adl ,
po_req_distributions_all req
where adl.award_set_id = req.award_id
and req.distribution_id = X_req_distribution_id
and adl_status = 'A'
and adl.adl_line_num = 1 ;
SELECT default_dist_award_id
FROM gms_implementations
WHERE enabled ='Y'
and award_distribution_option = 'Y'
and default_dist_award_number = X_award_number ;
select pt.sponsored_flag
from pa_projects_all b,
gms_project_types pt
where b.project_id = X_project_id
and b.project_type = pt.project_type
and pt.sponsored_flag = 'Y' ;
SELECT default_dist_award_id,
award_distribution_option,
default_dist_award_number
FROM gms_implementations
WHERE enabled ='Y' ;
select 'Y'
from dual
where exists
(select 1
from gms_awards
where award_number = X_award_number
and nvl(award_id,0) = nvl(l_award_id,0));
select project_type_class_code
from pa_project_types a,
pa_projects_all b
where a.project_type = b.project_type
and b.project_id = X_project_id;
select award_id
into l_award_id
from gms_awards
where award_number = X_award_number;
select pt.sponsored_flag
from pa_projects_all b,
gms_project_types pt
where b.project_id = X_project_id
and b.project_type = pt.project_type
and pt.sponsored_flag = 'Y' ;
delete from gms_award_distributions
where award_set_id = X_award_set_id ;
x_adl_rec.last_update_date := SYSDATE;
x_adl_rec.last_updated_by := 0;
x_adl_rec.last_update_login := 0;
-- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
-- WITH OA GUIDELINE )
--
PROCEDURE get_req_dist_AwardSetID ( X_distribution_id IN NUMBER,
X_award_set_id OUT NOCOPY NUMBER,
X_status IN OUT NOCOPY varchar2 ) is
l_award_set_id NUMBER ;
select award_id
from po_req_distributions_all
where distribution_id = X_distribution_id ;
-- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
-- WITH OA GUIDELINE )
-- END.
-- =============================================================
-- Create award distribution lines when REQ DISTRIBUTION LINE
-- is created for a sponsored projects. This also tieback
-- ADL with REQ.
-- =============================================================
PROCEDURE when_insert_line ( X_distribution_id IN NUMBER,
X_project_id IN NUMBER,
X_task_id IN NUMBER,
X_award_id IN NUMBER,
X_expenditure_type IN VARCHAR2,
X_expenditure_item_date IN DATE,
--X_raw_cost IN NUMBER,
X_award_set_id OUT NOCOPY NUMBER,
X_status IN OUT NOCOPY varchar2 ) is
x_adl_rec gms_award_distributions%ROWTYPE;
x_adl_rec.last_update_date := SYSDATE;
x_adl_rec.last_updated_by := 0;
x_adl_rec.last_update_login := 0;
--db_pack_message('When Insert Line Test :'||X_distribution_id) ;
UPDATE PO_REQ_DISTRIBUTIONS_ALL
SET award_id = x_adl_rec.award_set_id
where distribution_id = X_distribution_id ;
END when_insert_line ;
-- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
-- WITH OA GUIDELINE )
--
-- When_update_line overloading
-- x_award_set_id was added.
--
PROCEDURE when_update_line ( X_distribution_id IN NUMBER,
X_project_id IN NUMBER,
X_task_id IN NUMBER,
X_award_id IN NUMBER,
X_expenditure_type IN VARCHAR2,
X_expenditure_item_date IN DATE,
X_award_set_id OUT NOCOPY NUMBER,
X_status IN OUT NOCOPY varchar2 ) is
l_award_set_id NUMBER ;
select award_id
from po_req_distributions_all
where distribution_id = X_distribution_id ;
when_update_line ( X_distribution_id,
X_project_id,
X_task_id,
X_award_id,
X_expenditure_type,
X_expenditure_item_date,
X_status ) ;
END when_update_line ;
-- 3068454 ( CHANGE REQUIRED IN GMS_POR_API.WHEN_UPDATE/INSERT_LINE TO WORK
-- WITH OA GUIDELINE )
-- END.............
-- ================================================================
-- following program unit control DML operations or Poject/task
-- and award. ADLS are updated for change in project/task or award.
-- Adls are also removed for project changed from sponsored to
-- non sponsored.
-- ================================================================
PROCEDURE when_update_line ( X_distribution_id IN NUMBER,
X_project_id IN NUMBER,
X_task_id IN NUMBER,
X_award_id IN NUMBER,
X_expenditure_type IN VARCHAR2,
X_expenditure_item_date IN DATE,
--X_raw_cost IN NUMBER,
X_status IN OUT NOCOPY varchar2 ) is
x_award_set_id NUMBER ;
select award_id
from po_req_distributions_all
where distribution_id = X_distribution_id ;
--db_pack_message('When Update Line :'||NVL(X_award_set_id,0)) ;
when_insert_line ( X_distribution_id,
X_project_id ,
X_task_id ,
X_award_id ,
X_expenditure_type ,
X_expenditure_item_date ,
--X_raw_cost ,
X_award_set_id ,
X_status ) ;
delete from gms_award_distributions
where award_set_id = x_award_set_id
and adl_status = 'A' ;
UPDATE PO_REQ_DISTRIBUTIONS_ALL
SET award_id = NULL
where distribution_id = X_distribution_id ;
update gms_award_distributions
set project_id = X_project_id,
task_id = X_task_id,
award_id = X_award_id
where award_set_id = x_award_set_id
and adl_line_num = 1
and document_type= 'REQ'
and adl_status = 'A' ;
END when_update_line ;
-- Delete unwanted award distribution lines here.
-- =================================================================
PROCEDURE when_delete_line ( X_distribution_id IN NUMBER,
X_status IN OUT NOCOPY varchar2 ) is
x_award_set_id NUMBER ;
select award_id
from po_req_distributions_all
where distribution_id = X_distribution_id ;
delete from gms_award_distributions
where award_set_id = x_award_set_id
and adl_status = 'A' ;
END when_delete_line ;
PROCEDURE delete_adl ( p_award_set_id IN NUMBER,
x_status OUT NOCOPY varchar2,
x_err_msg OUT NOCOPY varchar2 ) is
l_status varchar2(1) ;
delete from gms_award_distributions
where award_set_id = p_award_set_id
and adl_status = 'A' ;
END delete_adl ;
SELECT award_distribution_option,
default_dist_award_number,
default_dist_award_id
FROM gms_implementations
WHERE enabled ='Y';
SELECT default_dist_award_id
FROM gms_implementations
WHERE enabled ='Y'
AND award_distribution_option ='Y';
SELECT project_id
from gms_funding_patterns_all FPH,
gms_fp_distributions FPD
where FPH.project_id = p_project_id
and NVL(FPH.task_id,p_task_id) = p_task_id
and FPH.funding_pattern_id= FPD.funding_pattern_id
and FPH.status = 'A'
and NVL(FPH.retroactive_flag,'N') = 'N' ;
SELECT gae.expenditure_type
FROM gms_funding_patterns gfp,
gms_fp_distributions gfd,
gms_allowable_expenditures gae,
pa_tasks t,
gms_awards ga
WHERE t.task_id =p_task_id
AND t.project_id =p_project_id
AND gfp.project_id =p_project_id
AND nvl(gfp.task_id,t.top_task_id ) =t.top_task_id
AND gfp.status ='A'
AND gfp.retroactive_flag ='N'
AND gae.expenditure_type =p_expenditure_type
AND gfp.funding_pattern_id =gfd.funding_pattern_id
AND ga.award_id =gfd.award_id
AND ga.allowable_schedule_id =gae.allowability_schedule_id;
SELECT gms_packet_header_id_s.NEXTVAL
FROM DUAL;
SELECT gms_packet_dist_id_s.NEXTVAL
FROM DUAL;
SELECT awd.dist_status
FROM gms_distributions awd
WHERE awd.document_distribution_id = l_distribution_id
AND awd.document_header_id = l_doc_header_id
AND awd.document_type = l_document_source
AND awd.dist_status <>'FABA';
INSERT INTO gms_distributions
( document_header_id ,
document_distribution_id,
document_type,
gl_date,
project_id,
task_id,
expenditure_type,
expenditure_organization_id,
expenditure_item_date,
quantity,
unit_price,
amount,
dist_status,
creation_date)
VALUES ( l_doc_header_id,
l_distribution_id,
l_document_source,
p_gl_encumbered_date,
p_project_id,
p_task_id,
p_expenditure_type,
p_expenditure_organization_id,
p_expenditure_item_date,
p_quantity,
p_unit_price,
p_func_amount,
NULL,
SYSDATE );
SELECT a.award_number ,
awdd.award_id ,
awdd.quantity_distributed
BULK COLLECT INTO l_award_qty_obj.award_num,
l_award_qty_obj.award_id,
l_award_qty_obj.quantity
FROM gms_distribution_details awdd,
gms_distributions awd,
gms_awards_all a
WHERE awd.document_distribution_id = awdd.document_distribution_id
AND awd.document_header_id = awdd.document_header_id
AND awd.document_distribution_id = l_distribution_id
AND awd.document_header_id = l_doc_header_id
AND awd.document_type = l_document_source
AND awd. dist_status = 'FABA'
AND awdd.award_id = a.Award_id;