[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select default_dist_award_id,award_distribution_option
into g_award_id,g_award_distribution_option
from gms_implementations;
Select nvl(gpt.sponsored_flag,'N')
into Sponsor_Flag
from pa_tasks t,
pa_projects_all p,
--gms_project_types gpt /*Commented for bug#12822370 */
pa_project_types_all gpt /*Added for bug#12822370 */
where t.task_id = P_Task_Id and
p.project_id = t.project_id and
gpt.project_type = p.project_type and
gpt.org_id = p.org_id;
IF x_rec.last_updated_by is NULL THEN
x_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
IF x_rec.last_update_date is NULL THEN
x_rec.last_update_date := sysdate ;
-- Bug 3465939 :Modified code to insert original_encumbrance_item_id
-- passed by Oracle Labor distribution system/External system.
insert into gms_transaction_interface_all (
TXN_INTERFACE_ID,
BATCH_NAME,
TRANSACTION_SOURCE,
EXPENDITURE_ENDING_DATE,
EXPENDITURE_ITEM_DATE,
PROJECT_NUMBER,
TASK_NUMBER,
AWARD_ID,
EXPENDITURE_TYPE,
TRANSACTION_STATUS_CODE,
ORIG_TRANSACTION_REFERENCE,
ORG_ID,
SYSTEM_LINKAGE,
USER_TRANSACTION_SOURCE,
TRANSACTION_TYPE,
BURDENABLE_RAW_COST,
FUNDING_PATTERN_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
AWARD_NUMBER ,-- Fix for bug : 2439320
ORIGINAL_ENCUMBRANCE_ITEM_ID -- Bug 3465936
) Values
(
x_rec.TXN_INTERFACE_ID,
x_rec.BATCH_NAME,
x_rec.TRANSACTION_SOURCE,
x_rec.EXPENDITURE_ENDING_DATE,
x_rec.EXPENDITURE_ITEM_DATE,
x_rec.PROJECT_NUMBER,
x_rec.TASK_NUMBER,
x_rec.AWARD_ID,
x_rec.EXPENDITURE_TYPE,
x_rec.TRANSACTION_STATUS_CODE,
x_rec.ORIG_TRANSACTION_REFERENCE,
x_rec.ORG_ID,
x_rec.SYSTEM_LINKAGE,
x_rec.USER_TRANSACTION_SOURCE,
x_rec.TRANSACTION_TYPE,
x_rec.BURDENABLE_RAW_COST,
x_rec.FUNDING_PATTERN_ID,
x_rec.CREATED_BY,
x_rec.CREATION_DATE,
x_rec.LAST_UPDATED_BY,
x_rec.LAST_UPDATE_DATE,
x_rec.AWARD_NUMBER ,-- Fix for bug : 2439320
x_rec.ORIGINAL_ENCUMBRANCE_ITEM_ID -- Bug 3465936
) ;
PROCEDURE UPDATE_GMS_XFACE_API ( p_rec gms_transaction_interface_all%ROWTYPE
, p_outcome OUT NOCOPY varchar2 ) is
x_rec gms_transaction_interface_all%ROWTYPE ;
IF x_rec.last_updated_by is NULL THEN
x_rec.last_updated_by := nvl(fnd_global.user_id,0) ;
IF x_rec.last_update_date is NULL THEN
x_rec.last_update_date := sysdate ;
-- Bug 3465939 :Modified code to update original_encumbrance_item_id
-- passed by Oracle Labor distribution system/External system.
UPDATE gms_transaction_interface_all
SET BATCH_NAME = x_rec.BATCH_NAME,
TRANSACTION_SOURCE = x_rec.TRANSACTION_SOURCE,
EXPENDITURE_ENDING_DATE = x_rec.EXPENDITURE_ENDING_DATE,
EXPENDITURE_ITEM_DATE = x_rec.EXPENDITURE_ITEM_DATE,
PROJECT_NUMBER = x_rec.PROJECT_NUMBER,
TASK_NUMBER = x_rec.TASK_NUMBER,
AWARD_ID = x_rec.AWARD_ID,
EXPENDITURE_TYPE = x_rec.EXPENDITURE_TYPE,
TRANSACTION_STATUS_CODE = x_rec.TRANSACTION_STATUS_CODE,
ORIG_TRANSACTION_REFERENCE = x_rec.ORIG_TRANSACTION_REFERENCE,
ORG_ID = x_rec.ORG_ID,
SYSTEM_LINKAGE = x_rec.SYSTEM_LINKAGE,
USER_TRANSACTION_SOURCE = x_rec.USER_TRANSACTION_SOURCE,
TRANSACTION_TYPE = x_rec.TRANSACTION_TYPE,
BURDENABLE_RAW_COST = x_rec.BURDENABLE_RAW_COST,
FUNDING_PATTERN_ID = x_rec.FUNDING_PATTERN_ID,
CREATED_BY = x_rec.CREATED_BY,
CREATION_DATE = x_rec.CREATION_DATE,
LAST_UPDATED_BY = x_rec.LAST_UPDATED_BY,
LAST_UPDATE_DATE = x_rec.LAST_UPDATE_DATE,
AWARD_NUMBER = x_rec.AWARD_NUMBER ,-- Fix for bug : 2439320
ORIGINAL_ENCUMBRANCE_ITEM_ID = x_rec.ORIGINAL_ENCUMBRANCE_ITEM_ID -- Bug 3465936
WHERE TXN_INTERFACE_ID = x_rec.TXN_INTERFACE_ID ;
x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_TRANSACTIONS_PUB : UPDATE_GMS_XFACE_API',
x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
x_err_code => p_err_code, x_err_buff => p_err_buf
) ;
END UPDATE_GMS_XFACE_API ;
PROCEDURE DELETE_GMS_XFACE_API ( p_rec gms_transaction_interface_all%ROWTYPE
, p_outcome OUT NOCOPY varchar2 ) is
x_rec gms_transaction_interface_all%ROWTYPE ;
delete from gms_transaction_interface_all
WHERE TXN_INTERFACE_ID = x_rec.TXN_INTERFACE_ID ;
x_token_name1 => 'PROGRAM_NAME', x_token_val1 => 'GMS_TRANSACTIONS_PUB : DELETE_GMS_XFACE_API',
x_token_name2 => 'OERRNO', x_token_val2 => SQLCODE,
x_token_name3 => 'OERRM', x_token_val3 => SQLERRM ,
x_err_code => p_err_code, x_err_buff => p_err_buf
) ;
END DELETE_GMS_XFACE_API ;
select fp.funding_name,
fp.funding_pattern_id
from gms_funding_patterns_all fp,
pa_tasks t
where nvl(fp.retroactive_flag, 'N') = 'N'
and NVL(fp.status, 'N') = 'A'
and fp.project_id = p_project_id
and t.task_id = p_task_id
and fp.task_id = t.top_task_id
and P_expenditure_item_date between fp.start_date and NVL(fp.end_date, P_expenditure_item_date )
union
select gfpa.funding_name,
gfpa.funding_pattern_id
from gms_funding_patterns_all gfpa
where nvl(gfpa.retroactive_flag, 'N') = 'N'
and NVL(gfpa.status, 'N') = 'A'
and gfpa.project_id = p_project_id
and gfpa.task_id is null
and not exists (select '1' from gms_funding_patterns_all b, pa_tasks t
where gfpa.project_id = b.project_id
and nvl(b.status,'x') = 'A'
and t.task_id = p_task_id
and b.task_id = t.top_task_id)
and P_expenditure_item_date between start_date and NVL(end_date, P_expenditure_item_date )
order by 1;
Select a.Allowable_Schedule_Id,
nvl(a.Preaward_Date,a.START_DATE_ACTIVE) awd_Date,
a.End_Date_Active,
a.Close_Date,
a.Status
from GMS_AWARDS a,
gms_fp_distributions b
where a.award_id = b.award_id
and b.funding_pattern_id = x_funding_pattern_id;
Select Allowable_Schedule_Id,
nvl(Preaward_Date,START_DATE_ACTIVE),
End_Date_Active,
Close_Date,
Status
from GMS_AWARDS_all --- Added for Bug#13370217
where award_id = P_award_id;
select aw.award_id award_id
from pa_tasks t ,
gms_installments ins,
gms_summary_project_fundings su,
gms_budget_versions bv,
gms_awards_all aw --- Added for Bug#13370217
where bv.budget_status_code = 'B'
and bv.project_id = P_Project_Id
and bv.award_id = P_award_id
and su.project_id = bv.project_id
and t.project_id = bv.project_id
and t.task_id = P_Task_Id
and ((su.task_id= t.task_id) or (su.task_id is null) or (su.task_id = t.top_task_id ) )
and ins.installment_id = su.installment_id
and ins.award_id = aw.award_id
and aw.award_id = P_award_id
and aw.status <> 'CLOSED'
and aw.award_template_flag = 'DEFERRED' ;
--Select award_id
--from GMS_AWARDS_BASIC_V
--where project_id = P_Project_Id
--and task_id = P_Task_Id
--and award_id = P_award_id;
Select Expenditure_Type
from GMS_ALLOWABLE_EXPENDITURES
where ALLOWABILITY_SCHEDULE_ID = X_Allowable_Schedule_Id and
EXPENDITURE_TYPE = P_expenditure_type;
--select FND_GLOBAL.ORG_ID into l_org_id from dual;
select pt.sponsored_flag
from pa_projects_all b,
--gms_project_types pt /*Commented for bug#12822370 */
pa_project_types_all pt /*Added for bug#12822370 */
where b.project_id = X_project_id
and b.project_type = pt.project_type
and pt.sponsored_flag = '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
into l_project_type_class_code
from pa_project_types_all a,
pa_projects_all b
where a.project_type = b.project_type
and a.org_id = b.org_id /*For Bug 5414832*/
and b.project_id = X_project_id;
select award_id
into l_award_id
from gms_awards
where award_number = X_award_number;