[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT budget_version_id
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code in ('S','W');
SELECT SUM (p_burdenable_raw_cost * NVL(cm.compiled_multiplier,0))
FROM pa_ind_rate_sch_revisions irsr,
pa_ind_cost_codes icc,
pa_cost_base_exp_types cbet,
pa_ind_compiled_sets ics,
pa_compiled_multipliers cm
WHERE irsr.cost_plus_structure = cbet.cost_plus_structure AND
icc.ind_cost_code = cm.ind_cost_code AND
cbet.cost_base = cm.cost_base AND
ics.cost_base = cbet.cost_base AND
cbet.cost_base_type = 'INDIRECT COST' AND
cbet.expenditure_type = p_expenditure_type AND
ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id AND
ics.organization_id = p_organization_id AND
ics.ind_compiled_set_id = p_ind_compiled_set_id AND
cm.ind_compiled_set_id = p_ind_compiled_set_id ;
GMS_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'BUDGET'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(p_draft_version_id)
,p_description => NULL
,p_err_code => p_err_code
,p_err_stage => p_err_stage
,p_err_stack => p_err_stack
);
GMS_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'BUDGET_NTFY_ONLY'
,p_item_type => l_item_type
,p_item_key => l_item_key
,p_entity_key1 => to_char(p_draft_version_id)
,p_description => NULL
,p_err_code => p_err_code
,p_err_stage => p_err_stage
,p_err_stack => p_err_stack
);
SELECT 'x'
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code = 'S'
FOR UPDATE NOWAIT;
UPDATE gms_budget_versions
SET budget_status_code = 'W', WF_status_code = 'REJECTED'
WHERE project_id = l_project_id
AND award_id = l_award_id
AND budget_type_code = l_budget_type_code
AND budget_status_code = 'S';
PROCEDURE Select_Budget_Approver
(itemtype IN VARCHAR2
, itemkey IN VARCHAR2
, actid IN NUMBER
, funcmode IN VARCHAR2
, resultout OUT NOCOPY VARCHAR2
)
IS
--
CURSOR l_baseliner_user_csr( p_baseliner_id NUMBER )
IS
SELECT f.user_id
, f.user_name
, p.first_name||' '||p.last_name
FROM fnd_user f, per_people_f p /*Bug 5122724 */
WHERE p.effective_start_date = (SELECT min(pp.effective_start_date)
FROM per_all_people_f pp where pp.person_id = p.person_id
AND pp.effective_end_date >=trunc(sysdate))
AND ((p.employee_number is not null) OR (p.npw_number is not null))
AND f.employee_id = p_baseliner_id
AND f.employee_id = p.person_id;
GMS_CLIENT_EXTN_BUDGET_WF.Select_Budget_Approver
(p_item_type => itemtype
,p_item_key => itemkey
,p_project_id => l_project_id
,p_award_id => l_award_id
,p_budget_type_code => l_budget_type_code
,p_workflow_started_by_id => l_workflow_started_by_id
,p_budget_baseliner_id => l_baseliner_employee_id
);
WF_CORE.CONTEXT('GMS_WF_PKG','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
WF_CORE.CONTEXT('GMS_WF_PKG','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
WF_CORE.CONTEXT('GMS_WF_PKG','SELECT_BUDGET_APPROVER', itemtype, itemkey, to_char(actid), funcmode);
END Select_Budget_Approver;
SELECT v.budget_version_id
FROM gms_budget_versions v
WHERE v.project_id = p_project_id
AND v.award_id = p_award_id
AND v.budget_type_code = p_budget_type_code
AND v.budget_status_code in ('S','W');
SELECT MAX(budget_version_id)
FROM gms_budget_versions
WHERE project_id = p_project_id
AND award_id = p_award_id
AND budget_type_code = p_budget_type_code
AND budget_status_code = 'B';
GMS_WORKFLOW_UTILS.Insert_WF_Processes
(p_wf_type_code => 'BUDGET'
,p_item_type => itemtype
,p_item_key => itemkey
,p_entity_key1 => to_char(l_budget_version_id)
,p_entity_key2 => to_char(l_baselined_version_id)
,p_description => NULL
,p_err_code => l_err_code
,p_err_stage => l_err_stage
,p_err_stack => l_err_stack
);
PROCEDURE select_wf_process ( itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout out NOCOPY varchar2 )
is
x_gms_wf_process varchar2(25);
wf_core.context('GMS_BUDGET_WF', 'SELECT_WF_PROCESS', itemtype, itemkey, to_char(actid), funcmode);
end select_wf_process;
SELECT pbem.time_phased_type_code,
pbem.entry_level_code
FROM gms_budget_versions gbv,
pa_budget_entry_methods pbem
WHERE gbv.budget_version_id = p_budget_version_id
AND gbv.budget_entry_method_code = pbem.budget_entry_method_code;
SELECT prl.group_resource_type_id,
gbv.resource_list_id
FROM gms_budget_versions gbv,
pa_resource_lists prl
WHERE gbv.budget_version_id = p_budget_version_id
AND gbv.resource_list_id = prl.resource_list_id;
select bv.budget_version_id,
bem.entry_level_code
into l_prev_baselined_version_id,
l_prev_entry_level_code
from gms_budget_versions bv,
pa_budget_entry_methods bem
where bv.award_id = l_award_id
and bv.project_id = l_project_id
and bv.budget_type_code = l_budget_type_code
and bv.budget_status_code = 'B'
and bv.current_flag = 'R'
and bv.budget_entry_method_code = bem.budget_entry_method_code;
GMS_BUDGET_BALANCE.update_gms_balance( x_project_id => l_project_id,
x_award_id => l_award_id,
x_mode => l_mode,
errbuf => l_err_code,
retcode => l_retcode);
update gms_budget_versions
set current_flag = 'N'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'B'
and current_flag = 'R';
update gms_budget_versions
set current_flag = 'Y'
where budget_version_id = ( select max(budget_version_id)
from gms_budget_versions
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code);
select budget_version_id
into l_baselined_version_id
from gms_budget_versions
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'B'
and current_flag = 'Y';
SELECT profile_option_value
INTO l_user_profile_value1
FROM fnd_profile_options p,
fnd_profile_option_values v
WHERE p.profile_option_name = 'PA_SUPER_PROJECT'
AND v.profile_option_id = p.profile_option_id
AND v.level_id = 10004
AND v.level_value = fnd_global.user_id;
SELECT profile_option_value
INTO l_user_profile_value2
FROM fnd_profile_options p,
fnd_profile_option_values v
WHERE p.profile_option_name = 'PA_SUPER_PROJECT_VIEW'
AND v.profile_option_id = p.profile_option_id
AND v.level_id = 10004
AND v.level_value = fnd_global.user_id;
update gms_budget_versions
set current_flag = 'Y'
where budget_version_id = l_prev_baselined_version_id;
update gms_budget_versions
set current_flag = 'N'
where budget_version_id = l_baselined_version_id;
update gms_budget_versions
set budget_status_code = 'W',
wf_status_code = NULL
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'S';
select distinct packet_id
into l_packet_id
from gms_bc_packets
where budget_version_id = l_baselined_version_id;
update gms_budget_versions
set current_flag = 'Y'
where budget_version_id = l_prev_baselined_version_id;
update gms_budget_versions
set current_flag = 'N'
where budget_version_id = l_baselined_version_id;
update gms_budget_versions
set budget_status_code = 'S'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'W';
update gms_budget_versions
set current_flag = 'Y'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'B'
and current_flag = 'R';
update gms_budget_versions
set budget_status_code = 'S'
where award_id = l_award_id
and project_id = l_project_id
and budget_type_code = l_budget_type_code
and budget_status_code = 'W';
select p.first_name||' '||p.last_name /*Bug 5122724 */
from fnd_user f, per_people_f p
where p.effective_start_date = (select min(pp.effective_start_date)
from per_all_people_f pp
where pp.person_id = p.person_id
and pp.effective_end_date >=trunc(sysdate))
and ((p.employee_number is not null) OR (p.npw_number is not null))
and user_id = FND_GLOBAL.User_Id
and f.employee_id = p.person_id;
select gms_workflow_itemkey_s.nextval
into ItemKey
from dual;
select ga.award_id,
ga.award_number,
ga.award_short_name,
gi.installment_num,
grv.report_name,
grv.due_date,
ga.funding_source_short_name,
grv.report_id --bug 2282107
from gms_awards_v ga, gms_installments gi, gms_reports_v grv
where grv.installment_id = gi.installment_id
and gi.award_id = ga.award_id
and grv.due_date = trunc(sysdate) + l_offset_days -- Bug 1868293
and ga.status <> 'CLOSED' --Changed from 'ACTIVE' to fix bug 2200837
and gi.active_flag = 'Y'
and ga.award_template_flag ='DEFERRED'; */-- commentedout to fix bug 2660430
select ga.award_id,
ga.award_number,
ga.award_short_name,
gi.installment_num,
grt.report_name,
gr.due_date,
substrb(party.party_name,1,50) funding_source_short_name,
gr.report_id --bug 2282107
from gms_awards ga,
gms_installments gi,
gms_reports gr,
gms_report_templates grt,
hz_parties party,
hz_cust_accounts cust_acct
where ga.award_template_flag ='DEFERRED'
and ga.status <> 'CLOSED' --Changed from 'ACTIVE' to fix bug 2200837
and ga.award_id = gi.award_id
and gi.active_flag = 'Y'
and gr.installment_id = gi.installment_id
and gr.report_template_id = grt.report_template_id
and gr.due_date = trunc(sysdate) + l_offset_days -- Bug 1868293
and ga.funding_source_id =cust_acct.cust_account_id(+)
and cust_acct.party_id = party.party_id;
select user_id, user_name
from gms_notifications_v
where event_type like p_event_type
and award_id = p_award_id;
select user_id, user_name
from gms_notifications_v
where event_type = 'INSTALLMENT_ACTIVE'
and award_id = p_award_id;
select p.first_name||' '||p.last_name /*Bug 5122724 */
from fnd_user f, per_people_f p
where p.effective_start_date = (select min(pp.effective_start_date)
from per_all_people_f pp
where pp.person_id = p.person_id
and pp.effective_end_date >=trunc(sysdate))
and ((p.employee_number is not null) OR (p.npw_number is not null))
and user_id = FND_GLOBAL.User_Id
and f.employee_id = p.person_id;
select ga.award_number,
ga.award_short_name,
substrb(party.party_name,1,50),
gi.installment_num,
gi.start_date_active,
gi.end_date_active,
gi.issue_date,
gi.close_date,
gi.direct_cost,
gi.indirect_cost,
(nvl(gi.direct_cost,0) + nvl(gi.indirect_cost,0)),
gi.description
from gms_awards ga,
gms_installments gi, hz_parties party,
hz_cust_accounts cust_acct
where gi.award_id = ga.award_id
and cust_acct.cust_account_id(+) = ga.funding_source_id
and cust_acct.party_id = party.party_id
and gi.installment_id = x_install_id
and ga.award_id = x_award_id;
select gms_workflow_itemkey_s.nextval
into ItemKey
from dual;
Following Logic selects Open commitments associated with the particular award,project and task combination and frames
a message which gets displayed as text in the notification.
======================================================================================================================*/
PROCEDURE Get_Inst_Open_Commitments ( document_id IN VARCHAR2
,display_type IN VARCHAR2
,document IN OUT NOCOPY VARCHAR2
,document_type IN OUT NOCOPY VARCHAR2) IS
l_item_type wf_items.item_type%TYPE;
SELECT meaning
FROM gms_lookups
WHERE lookup_type='GMS_COMMT_TYPE'
AND lookup_code = type ;
SELECT meaning
FROM gms_lookups
WHERE lookup_type='GMS_DOC_NOTIF'
AND lookup_code = header ;
SELECT gscv.commitment_number
,gscv.document_type
,SUM(gscv.burdened_cost)
FROM gms_status_commitments_v gscv ,gms_summary_project_fundings gmpf
WHERE gmpf.installment_id = p_installment_id
AND gscv.award_id = p_award_id
AND gscv.project_id = gmpf.project_id
AND gscv.task_id = nvl(gmpf.task_id,gscv.task_id)
AND gscv.expenditure_item_date BETWEEN p_installment_start_date AND p_installment_end_date
AND gscv.document_type IN ('AP','PO','REQ')
GROUP BY document_type , gscv.project_id , gscv.task_id , award_id , commitment_number ; */
SELECT cmt.cmt_number
,cmt.document_type
,PA_CURRENCY.ROUND_CURRENCY_AMT(SUM (cmt.acct_raw_cost +
DECODE(nvl(cmt.ind_compiled_set_id,0),0,0,
DECODE(NVL(cmt.burdenable_raw_cost,0),0,0,
gms_wf_pkg.Get_Burden_amount(cmt.expenditure_type,
cmt.organization_id,
cmt.ind_compiled_set_id,
cmt.burdenable_raw_cost)
)
)
)
)
FROM gms_commitment_encumbered_v cmt
WHERE cmt.award_id = p_award_id
AND (cmt.project_id,cmt.task_id) IN (SELECT gmpf.project_id,nvl(gmpf.task_id,cmt.task_id)
FROM gms_summary_project_fundings gmpf
WHERE gmpf.installment_id =p_installment_id )
AND cmt.expenditure_item_date BETWEEN p_installment_start_date AND p_installment_end_date
AND cmt.document_type IN ('AP','PO','REQ')
GROUP BY document_type , cmt.project_id , cmt.task_id , cmt.award_id , cmt.cmt_number ;
SELECT USER_ID
INTO l_user_id
FROM FND_USER
WHERE user_name = FND_GLOBAL.User_Name;
SELECT award_number
FROM gms_awards
WHERE award_id = x_award_id;
SELECT installment_num
,end_date_active
,start_date_active
FROM gms_installments
WHERE installment_id = x_installment_id;
SELECT gms_workflow_itemkey_s.NEXTVAL INTO ItemKey FROM DUAL;
Following Logic selects installments for which notification are to be generated
and then calls the procedure which kicks of the workflow process in loop for each installment selected.
========================================================================================================*/
PROCEDURE Notify_Installment_Closeout(
ERRBUF OUT NOCOPY VARCHAR2
,RETCODE OUT NOCOPY VARCHAR2
,p_offset_days IN NUMBER ) IS
--Cursor to selects all the installments which are going to get closed by the offset number of days
CURSOR award_install_cursor is
SELECT ga.award_id ,
gi.installment_id
FROM gms_awards ga,
gms_installments gi
WHERE gi.award_id = ga.award_id
AND trunc(gi.end_date_active )= trunc(SYSDATE) + p_offset_days
AND ga.status <> 'CLOSED' -- Change from 'ACTIVE' to fix bug 2200585
AND gi.active_flag = 'Y'
AND ga.award_template_flag ='DEFERRED'; --Added to fix bug 2200585
SELECT gn.user_id ,
fu.user_name
FROM gms_notifications gn, fnd_user fu
WHERE gn.user_id = fu.user_id
AND event_type = 'INSTALLMENT_CLOSEOUT'
AND award_id = p_award_id;
select 1
from gms_personnel gmsp
where gmsp.award_id = p_award_id
and gmsp.person_id = p_person_id;
(select fndu.employee_id
from fnd_user fndu
where fndu.user_id = p_user_id) ;
select fndu.employee_id /*Bug 5122724 */
from fnd_user fndu
,per_people_f p
where p.effective_start_date = (select min(pp.effective_start_date)
from per_all_people_f pp
where pp.person_id = p.person_id
and pp.effective_end_date >=trunc(sysdate))
and ((p.employee_number is not null) OR (p.npw_number is not null))
and p.person_id = fndu.employee_id
and fndu.user_id = p_user_id;
select 1
from gms_personnel
where person_id = p_person_id
and award_id = p_award_id
and trunc(sysdate) between start_date_active
and nvl(end_date_active,to_date('12/31/4712','MM/DD/YYYY'));