[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.labor_invoice_format_id, a.non_labor_invoice_format_id
into l_labor_format_id,l_non_labor_format_id
from gms_awards_all a
where a.award_project_id = X_Award_Project_Id;
select 1 into l_dummy from dual where exists (
select 1
from pa_invoice_group_columns a,
pa_invoice_format_details b
where a.invoice_group_column_id = b.invoice_group_column_id
and b.invoice_format_id in(l_labor_format_id,X_Non_Labor_format_id)
and a.column_code not in ('EMPLOYEE FIRST NAME','EMPLOYEE FULL NAME',
'EMPLOYEE LAST NAME','EXPENDITURE CATEGORY',
'EXPENDITURE TYPE', --'JOB','JOB DISCIPLINE','JOB LEVEL',
'NON-LABOR RESOURCE','ORGANIZATION',
'REVENUE CATEGORY','TEXT','TOP TASK NAME',
'TOP TASK NUMBER','TOTAL AMOUNT','TOTAL HOURS',
'UNITS'));
Select DECODE( NVL(allow_burden_flag,'N'), 'N', 'Y', 'N')
into l_allow_burden_flag
from pa_transaction_sources pts
where pts.transaction_source = p_transaction_source;
Select 1
into x_dummy
from dual
where exists (select 1
from gms_event_intersect
where award_project_id = X_Award_Project_Id
and request_id = X_Request_Id
and event_type = X_Event_Type
and event_num IS NULL);
select
a.Revenue_Distribution_Rule
into
X_Award_Rev_Distribution_Rule
from
GMS_AWARDS a
where
a.Award_Id =X_Award_Id;
Select 1
into l_value
from dual
where exists(
select /*+ INDEX(adl gms_award_distributions_n7) */ 1 /* Added hint for bug 6969435 */
from gms_award_distributions adl,
pa_tasks t3,
pa_tasks t5
where adl.award_id = X_Award_Id
and adl.document_type = 'EXP'
and adl.adl_status = 'A'
and adl.fc_status = 'A'
and adl.billable_flag ='Y' /* removed nvl for bug 6969435 */
and adl.revenue_distributed_flag in ('N','Z') /* removed nvl for bug 6969435 */
and adl.project_id = t3.project_id /* Added for bug 6969435 */
and t3.task_id = adl.task_id
and t3.top_task_id = t5.task_id
and t5.ready_to_distribute_flag = 'Y');
select
a.Billing_Distribution_Rule
into
X_Award_Bill_Distribution_Rule
from
GMS_AWARDS a
where
a.Award_Id =X_Award_Id ;
Select 1
into l_value
from dual
where exists(
select /*+ INDEX(adl gms_award_distributions_n7) */ 1 /* Added hint for bug 6969435 */
from
gms_award_distributions adl,
pa_tasks t3,
pa_tasks t5
where adl.award_id = X_Award_Id
and adl.document_type = 'EXP'
and adl.adl_status = 'A'
and adl.fc_status = 'A'
and adl.billable_flag ='Y' /* removed nvl for bug 6969435 */
and adl.billed_flag in ('N','Z') /* removed nvl for bug 6969435 */
and t3.task_id = adl.task_id
and t3.top_task_id = t5.task_id
and t5.ready_to_distribute_flag = 'Y');
select a.installment_id
from gms_installments a,
gms_awards b
where b.award_project_id = X_Award_Project_id
and a.award_id = b.award_id;
Select 1
into x_dummy
from gms_awards
where award_project_id = X_Award_Project_id
FOR UPDATE NOWAIT;
Select 1
into x_dummy
from gms_installments
where installment_id = installment_records.installment_id
FOR UPDATE NOWAIT;
SELECT DISTINCT project_id, event_num, event_calling_process event_type
FROM gms_event_attribute gea
WHERE project_id = x_award_project_id
--AND event_calling_process = x_calling_process
and event_num > 0 /* Added for bug 4594090 */ /*Changed to >0 for bug 6969435 */
AND ( (x_event_num IS NULL) OR
(x_event_num IS NOT NULL AND event_num = x_event_num)
)
AND event_calling_process IS NOT NULL
-- null for manual events
and not exists ( /* Modified MINUS operation to Not exists clause */
SELECT 1 --project_id, event_num, calling_process event_type /* Modifed for 6969435 */
FROM pa_events
WHERE --project_id = x_award_project_id /* commented for 6969435 */
--AND calling_process = x_calling_process /* commented for 6969435 */
--AND ( (x_event_num IS NULL) OR /* commented for 6969435 */
-- (x_event_num IS NOT NULL AND event_num = x_event_num) /* commented for 6969435 */
-- ) /* commented for 6969435 */
--AND /* commented for 6969435 */
event_type = 'AWARD_BILLING'
and project_id = gea.project_id
and event_num = gea.event_num
and calling_process = gea.event_calling_process
);
SELECT DISTINCT gei.expenditure_item_id, gei.adl_line_num,
gea.actual_project_id, gea.actual_task_id,
gea.expenditure_org_id, gei.amount,
gei.revenue_accumulated, gei.creation_date,gea.request_id
FROM gms_event_intersect gei, gms_event_attribute gea
WHERE gea.project_id = f_project_id
AND gea.event_num = f_event_num
AND gea.event_calling_process = f_event_calling_process
AND gei.award_project_id = gea.project_id
AND gei.event_num = gea.event_num
AND gei.event_type = UPPER(f_event_calling_process);
SELECT expenditure_item_id, adl_line_num, actual_project_id,
actual_task_id, expenditure_org_id, burden_exp_type, burden_cost_code,
amount, revenue_accumulated, creation_date
FROM gms_burden_components
WHERE award_project_id = f_project_id
AND event_num = f_event_num
AND event_type = UPPER(f_event_calling_process);
SELECT actual_project_id, actual_task_id, installment_id,
revenue_amount, bill_amount
, rowid -- Bug 2715312
FROM gms_event_attribute
WHERE project_id = f_project_id
AND event_num = f_event_num
AND event_calling_process = f_event_calling_process;
gms_billing_adjustments.insert_bill_cancel (evt.project_id,
evt.event_num,
f_raw_exp_item_id,
f_raw_adl_line_num,
-1 * f_raw_revenue_amount,
evt.event_type,
NULL, -- burden_exp_type
NULL, -- burden_cost_code
f_raw_creation_date,
f_raw_act_project_id,
f_raw_act_task_id,
f_raw_org_id,
SYSDATE, -- deletion_date
NULL, -- rlmi
x_err_code,
x_err_buff
);
UPDATE gms_award_distributions
SET revenue_distributed_flag = decode(x_event_num,NULL,'N','Z'),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE expenditure_item_id = f_raw_exp_item_id
AND adl_line_num = f_raw_adl_line_num
AND document_type = 'EXP'
AND adl_status = 'A';
UPDATE gms_award_distributions
SET billed_flag = decode(x_event_num,NULL,'N','Z'),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE expenditure_item_id = f_raw_exp_item_id
AND adl_line_num = f_raw_adl_line_num
AND document_type = 'EXP'
AND adl_status = 'A';
DELETE
FROM gms_event_intersect
WHERE expenditure_item_id = f_raw_exp_item_id
AND adl_line_num = f_raw_adl_line_num
AND award_project_id = evt.project_id
AND event_num = evt.event_num
AND event_type = upper(evt.event_type);
gms_billing_adjustments.insert_bill_cancel (evt.project_id,
evt.event_num,
f_burd_exp_item_id,
f_burd_adl_line_num,
-1 * f_burd_revenue_amount,
evt.event_type,
f_burd_exp_type, -- burden_exp_type
f_burd_cost_code, -- burden_cost_code
f_burd_creation_date,
f_burd_act_project_id,
f_burd_act_task_id,
f_burd_org_id,
SYSDATE, -- deletion_date
NULL, -- rlmi
x_err_code,
x_err_buff
);
DELETE
FROM gms_burden_components
WHERE expenditure_item_id = f_burd_exp_item_id
AND adl_line_num = f_burd_adl_line_num
AND award_project_id = evt.project_id
AND event_num = evt.event_num
AND event_type = upper(evt.event_type) ;
UPDATE gms_summary_project_fundings
SET total_billed_amount =
total_billed_amount - f_bill_amount
WHERE project_id = f_act_project_id
AND ( task_id IS NULL
OR task_id = f_act_task_id
OR task_id = (select t.top_task_id
from PA_TASKS t
where t.task_id = f_act_task_id)
)
AND installment_id = f_installment_id;
UPDATE gms_summary_project_fundings
SET total_revenue_amount =
total_revenue_amount - f_rev_amount
WHERE project_id = f_act_project_id
AND ( task_id IS NULL
OR task_id = f_act_task_id
OR task_id = (select t.top_task_id
from PA_TASKS t
where t.task_id = f_act_task_id)
)
AND installment_id = f_installment_id;
DELETE
FROM gms_event_attribute
WHERE rowid= f_row_id ; -- Bug 2715312, Replaced the below conditions with rowid
PROCEDURE DELETE_NULL_EVENTS (X_award_project_id IN NUMBER,
X_request_id IN NUMBER,
X_calling_process IN VARCHAR2,
X_err_code OUT NOCOPY NUMBER,
X_err_buff OUT NOCOPY VARCHAR2)
IS
X_event_type VARCHAR2(10);
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - START', 'C');
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: Rollback Invoice raw events','C');
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: After Rollback Invoice raw events,X_Err_Code:'||X_Err_Code,'C');
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: Revenue Raw - ADL update', 'C');
Update gms_award_distributions
set revenue_distributed_flag = decode(revenue_distributed_flag,'Y','Z','Z','N')
where (expenditure_item_id,adl_line_num) in
(select expenditure_item_id,
adl_line_num
from gms_event_intersect
where award_project_id = X_award_project_id
and event_num = -1
and request_id = X_request_id
and event_type = X_event_type
)
and document_type = 'EXP';
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: Revenue Raw - Delete records', 'C');
Delete
from gms_event_intersect
where award_project_id = X_award_project_id
and event_num = -1
and request_id = X_request_id
and event_type = X_event_type;
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: Invoice Burden - ADL Update', 'C');
Update gms_award_distributions
set billed_flag = decode(billed_flag,'Y','Z','Z','N')
where (expenditure_item_id,adl_line_num) in
(select expenditure_item_id,
adl_line_num
from gms_burden_components
where award_project_id = X_award_project_id
and event_num = -2
and request_id = X_request_id
and event_type = X_event_type
)
and document_type = 'EXP';
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: Revenue Burden - ADL Update', 'C');
Update gms_award_distributions
set revenue_distributed_flag = decode(revenue_distributed_flag,'Y','Z','Z','N')
where (expenditure_item_id,adl_line_num) in
(select expenditure_item_id,
adl_line_num
from gms_burden_components
where award_project_id = X_award_project_id
and event_num = -2
and request_id = X_request_id
and event_type = X_event_type
)
and document_type = 'EXP';
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - Stage: Burden - Delete records', 'C');
Delete
from gms_burden_components
where award_project_id = X_award_project_id
and event_num = -2
and request_id = X_request_id
and event_type = X_event_type;
gms_error_pkg.gms_debug('In DELETE_NULL_EVENTS - END', 'C');
End DELETE_NULL_EVENTS;
Select 0
into X_Err_Code
from gms_concurrency_control
where process_key = X_Award_Project_Id
and process_name = 'GMS_BLNG'
for update NOWAIT;
insert into gms_concurrency_control
(PROCESS_NAME,
PROCESS_KEY ,
REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATE_LOGIN )
values('GMS_BLNG',
X_Award_Project_Id,
X_Request_Id,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id
);
Select 0
into X_Err_Code
from gms_concurrency_control
where process_key = X_Award_Project_Id
and process_name = 'GMS_BLNG'
for update NOWAIT;
select /*+INDEX(pt PA_PROJECT_TYPES_U1)*/
ei.expenditure_item_id expenditure_item_id,
adl.adl_line_num adl_line_num,
adl.award_id award_id
from pa_expenditure_items_all ei,
pa_expenditure_types et,
pa_projects_all p,
pa_project_types pt,
pa_tasks t3,
pa_tasks t5,
gms_award_distributions adl
where adl.award_id = X_Award_Id
and ei.expenditure_item_id = adl.expenditure_item_id
and ei.system_linkage_function <> 'BTC'
and p.project_status_code <> 'CLOSED'
and pt.project_type = p.project_type
and pt.direct_flag = 'N'
and t3.project_id = p.project_id
and ei.task_id = t3.task_id
and t3.top_task_id = t5.task_id
and t5.ready_to_bill_flag = 'Y'
and adl.cost_distributed_flag = 'Y'
and ei.expenditure_item_date <= nvl(trunc(X_rev_or_bill_date),SYSDATE)
and ei.expenditure_type = et.expenditure_type
and adl.document_type = 'EXP'
and adl.fc_status = 'A'
and adl.adl_status = 'A'
and ei.bill_hold_flag='O';
Update gms_award_distributions
set bill_hold_flag='N'
where expenditure_item_id = Bill_Hold_Rec.expenditure_item_id
and adl_line_num = Bill_Hold_Rec.adl_line_num
and award_id = Bill_Hold_Rec.award_id
and document_type='EXP'
and adl_status = 'A';
update pa_expenditure_items_all
set bill_hold_flag='N'
where expenditure_item_id = Bill_Hold_Rec.expenditure_item_id;
select award_project_id
into x_award_project_id
from gms_awards_all where award_id = x_award_id ;
UPDATE gms_summary_project_fundings
SET total_billed_amount = decode(x_event_type,'INVOICE',
(total_billed_amount - X_ei_rollback_inst_tab(i).rev_bill_amount),total_billed_amount),
total_revenue_amount = decode(x_event_type,'REVENUE',
(total_revenue_amount - X_ei_rollback_inst_tab(i).rev_bill_amount),total_revenue_amount)
WHERE project_id = x_actual_project_id
AND ( task_id IS NULL
OR task_id = x_task_id
OR task_id = (select t.top_task_id
from PA_TASKS t
where t.task_id = x_task_id)
)
AND installment_id = X_ei_rollback_inst_tab(i).installment_id ;
UPDATE gms_award_distributions
SET revenue_distributed_flag = decode(x_event_type,'REVENUE','N',revenue_distributed_flag),
billed_flag = decode(x_event_type,'INVOICE','N',billed_flag),
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
WHERE expenditure_item_id = x_expenditure_item_id
AND document_type = 'EXP'
AND adl_status = 'A'
AND ( expenditure_item_id,adl_line_num ) in ( select expenditure_item_id, adl_line_num
from gms_Event_intersect
where expenditure_item_id = x_expenditure_item_id
AND award_project_id = x_award_project_id
AND event_num is NULL
AND event_type = x_event_type
union
select expenditure_item_id, adl_line_num
from gms_burden_components
where expenditure_item_id = x_expenditure_item_id
AND award_project_id = x_award_project_id
AND event_num is NULL
AND event_type = x_event_type );
DELETE FROM gms_event_intersect
WHERE expenditure_item_id = x_expenditure_item_id
AND award_project_id = x_award_project_id
AND event_num is NULL
AND event_type = x_event_type ;
DELETE FROM gms_burden_components
WHERE expenditure_item_id = x_expenditure_item_id
AND award_project_id = x_award_project_id
AND event_num IS NULL
AND event_type = x_event_type ;
select
sum(total_funding_amount),
sum(decode(X_Calling_Process,'Invoice',nvl(gmf.total_billed_amount,0),
'Revenue',nvl(gmf.total_revenue_amount,0) ))
into
X_Total_Funding_Amount,
X_Total_Rev_Bill_Amount
from
gms_summary_project_fundings gmf
where
(gmf.installment_id = nvl(C_Installment_Id,0) -- 11.5 changes, hard limit to award level
OR
(C_Installment_Id is NULL
AND gmf.installment_id in
(select installment_id
from gms_installments
where award_id = X_award_id
and (trunc(end_date_Active) >= trunc(X_Expenditure_item_date))
/* and active_flag = 'Y' Bug 6878405 */
and nvl(billable_flag,'N') = 'Y'
--order by end_date_active
)
)
)
and (
(gmf.task_id = X_Task_Id)
OR (gmf.task_id is NULL)
OR (gmf.task_id = (select t.top_task_id from PA_TASKS t where t.task_id = X_Task_Id))
)
and gmf.project_id = (select project_id from PA_TASKS where task_id = X_Task_Id);
select --distinct
adl.expenditure_item_id,
adl.adl_line_num,
adl.award_id
from pa_expenditure_items_all ei,
pa_projects_all p,
pa_project_types pt,
pa_tasks t3,
pa_tasks t5,
gms_award_distributions adl
where adl.award_id = X_Award_Id
and ei.expenditure_item_id = adl.expenditure_item_id
and ei.system_linkage_function <> 'BTC'
and p.project_status_code <> 'CLOSED'
and pt.project_type = p.project_type
and pt.direct_flag = 'N'
and t3.project_id = p.project_id
and ei.task_id = t3.task_id
and t3.top_task_id = t5.task_id
and t5.ready_to_bill_flag = 'Y'
and adl.cost_distributed_flag = 'Y'
and ei.expenditure_item_date <= nvl(trunc(X_rev_or_bill_date),SYSDATE)
and adl.billed_flag = 'N' /* removed nvl for bug 6969435 */
and adl.document_type='EXP'
and adl.adl_status = 'A'
and adl.fc_status = 'A'
and adl.output_tax_exempt_flag is null;
select set_of_books_id
into X_Set_Of_Book_Id
from pa_implementations;
select distinct
c.customer_id,
c.bill_to_customer_id,
c.ship_to_customer_id,
ras.site_use_id,
ras1.site_use_id
into X_Customer_id,
l_bill_to_customer_id,
l_ship_to_customer_id,
X_Bill_To_Site_Use_Id,
X_Ship_To_Site_Use_Id
from pa_project_customers c,
hz_cust_accounts cust_acct,
hz_cust_site_uses ras,
hz_cust_site_uses ras1
where c.project_id = X_Award_Project_Id
-- and c.customer_id = rc.customer_id
-- and nvl(rc.status,'A') = 'A'
and c.customer_id = cust_acct.cust_account_id
and nvl(cust_acct.status,'A') = 'A'
and c.customer_bill_split <> 0
--and ras.address_id = c.bill_to_address_id
and ras.cust_acct_site_id = c.bill_to_address_id
and ras.site_use_code || '' = 'BILL_TO'
and ras.status || '' = 'A'
-- and ras1.address_id = c.ship_to_address_id
and ras1. cust_acct_site_id = c.ship_to_address_id
and ras1.site_use_code || '' = 'SHIP_TO'
and ras1.status || '' = 'A';
Update gms_award_distributions
set
--output_vat_tax_id = S_Output_Vat_Tax_Id,
output_tax_classification_code = S_Output_tax_classify_code,
output_tax_exempt_flag = S_Output_Tax_Exempt_Flag,
output_tax_exempt_number = S_Output_Tax_Exempt_Number,
output_tax_exempt_reason_code = S_Output_Exempt_Reason_Code,
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
last_updated_by = fnd_global.user_id
where expenditure_item_id = F_Expenditure_Item_Id
and adl_line_num = F_Adl_Line_Num
and award_id = X_Award_Id
and document_type ='EXP'
and adl_status = 'A'
and output_tax_exempt_flag is null;
select ins.installment_id installment_id,
gmf.total_funding_amount total_funding_amount,
decode(X_Calling_Process,'Invoice',nvl(gmf.total_billed_amount,0),'Revenue',nvl(gmf.total_revenue_amount,0)) Inv_Rev_Amount
from gms_installments ins,
gms_summary_project_fundings gmf
where ins.award_id = X_award_id
and trunc(ins.end_date_active) >= trunc(X_Expenditure_item_date)
/* and ins.active_flag = 'Y' Bug 6878405 */
and nvl(ins.billable_flag,'N') = 'Y'
and ins.Installment_id = gmf.Installment_id
and (gmf.total_funding_amount - decode(X_Calling_Process,'Invoice',nvl(gmf.total_billed_amount,0),
'Revenue',nvl(gmf.total_revenue_amount,0) )) >0
and ((gmf.task_id = X_Task_Id)
OR (gmf.task_id is NULL)
OR (gmf.task_id = (select t.top_task_id from PA_TASKS t where t.task_id = X_Task_Id))
)
and gmf.project_id = (select project_id from PA_TASKS where task_id = X_Task_Id)
and ins.installment_id <> X_Installment_id
order by ins.end_date_active;
X_Inst_tab.delete; -- initialize
PROCEDURE INSERT_EVENT(X_AWARD_PROJECT_ID IN NUMBER DEFAULT NULL,
X_EVENT_NUM IN NUMBER DEFAULT NULL,
X_INSTALLMENT_ID IN NUMBER DEFAULT NULL,
X_ACTUAL_PROJECT_ID IN NUMBER DEFAULT NULL,
X_ACTUAL_TASK_ID IN NUMBER DEFAULT NULL,
X_BURDEN_COST_CODE IN VARCHAR2 DEFAULT NULL,
X_EXPENDITURE_ORG_ID IN NUMBER DEFAULT NULL,
X_BILL_AMOUNT IN NUMBER DEFAULT NULL,
X_REVENUE_AMOUNT IN NUMBER DEFAULT NULL,
X_REQUEST_ID IN NUMBER DEFAULT NULL,
X_EXPENDITURE_TYPE IN VARCHAR2 DEFAULT NULL,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2,
X_Calling_Process IN VARCHAR2 DEFAULT NULL) IS
Begin
/* Insert into GMS_EVENT_ATTRIBUTE_TABLE */
INSERT INTO GMS_EVENT_ATTRIBUTE(
PROJECT_ID,
EVENT_NUM,
INSTALLMENT_ID,
ACTUAL_PROJECT_ID,
ACTUAL_TASK_ID,
BURDEN_COST_CODE,
EXPENDITURE_ORG_ID,
BILL_AMOUNT,
REVENUE_AMOUNT,
WRITE_OFF_AMOUNT,
CREATED_BY,
CREATED_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REVENUE_ACCUMULATED,
RESOURCE_LIST_MEMBER_ID,
REQUEST_ID,
EXPENDITURE_TYPE,
EVENT_CALLING_PROCESS)
VALUES(X_AWARD_PROJECT_ID,
X_EVENT_NUM,
X_INSTALLMENT_ID,
X_ACTUAL_PROJECT_ID,
X_ACTUAL_TASK_ID,
X_BURDEN_COST_CODE,
X_EXPENDITURE_ORG_ID,
NVL(X_BILL_AMOUNT,0),
NVL(X_REVENUE_AMOUNT,0),
0, -- Write_Off_Amount
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
'N', -- Revenue_Accumulated
NULL, -- RLMI
X_REQUEST_ID,
X_EXPENDITURE_TYPE,
X_Calling_Process
);
End INSERT_EVENT;
PROCEDURE UPDATE_EVENT(X_AWARD_PROJECT_ID IN NUMBER DEFAULT NULL,
X_EVENT_NUM IN NUMBER DEFAULT NULL,
X_INSTALLMENT_ID IN NUMBER DEFAULT NULL,
X_ACTUAL_PROJECT_ID IN NUMBER DEFAULT NULL,
X_ACTUAL_TASK_ID IN NUMBER DEFAULT NULL,
X_BURDEN_COST_CODE IN VARCHAR2 DEFAULT NULL,
X_EXPENDITURE_ORG_ID IN NUMBER DEFAULT NULL,
X_BILL_AMOUNT IN NUMBER DEFAULT NULL,
X_REVENUE_AMOUNT IN NUMBER DEFAULT NULL,
X_REQUEST_ID IN NUMBER DEFAULT NULL,
X_EXPENDITURE_TYPE IN VARCHAR2 DEFAULT NULL,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
Begin
/* Update GMS_EVENT_ATTRIBUTE record */
UPDATE GMS_EVENT_ATTRIBUTE
SET installment_id = X_INSTALLMENT_ID,
actual_project_id = X_ACTUAL_PROJECT_ID,
actual_task_id = X_ACTUAL_TASK_ID,
burden_cost_code = X_BURDEN_COST_CODE,
expenditure_org_id = X_EXPENDITURE_ORG_ID,
bill_amount = nvl(X_BILL_AMOUNT,0),
revenue_amount = nvl(X_REVENUE_AMOUNT,0),
expenditure_type = X_EXPENDITURE_TYPE,
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
request_id = X_REQUEST_ID
WHERE project_id = X_AWARD_PROJECT_ID
AND event_num = X_EVENT_NUM
AND event_calling_process IS NULL; --Added for bug 2979125
END UPDATE_EVENT;
PROCEDURE DELETE_EVENT (X_AWARD_PROJECT_ID IN NUMBER,
X_EVENT_NUM IN NUMBER,
X_INSTALLMENT_ID IN NUMBER,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
Begin
/* Delete from GMS_EVENT_ATTRIBUTE_TABLE */
DELETE
FROM gms_event_attribute
WHERE project_id = X_AWARD_PROJECT_ID
AND event_num = X_EVENT_NUM
AND installment_id = X_INSTALLMENT_ID
AND event_calling_process IS NULL; --Added for bug 2979125
END DELETE_EVENT;
SELECT NVL(SUM(raw_cost),0)
INTO l_raw_cost
FROM gms_award_distributions
WHERE expenditure_item_id = X_Expenditure_item_id
AND document_type = 'EXP' -- To pick up only actuals and not encumbrances
AND adl_status = 'A'
AND fc_status = 'A'
AND billable_flag = 'Y';
gms_error_pkg.gms_debug('EVENT WRAPPER - BEFORE INSERT EVENT +VE PLSQL>DIFF','C');
INSERT_EVENT(X_Award_Project_Id,
NULL, -- event number
X_Installment_Total(Rec_Count).Installment_id,
X_Actual_Project_id,
X_Task_Id,
X_Burden_Cost_Code,
X_Exp_Org_Id,
X_Bill_Amount,
X_Rev_Amount,
X_Request_Id,
NULL, -- expenditure type,
X_Err_Code,
X_Err_Buff,
X_calling_Process);
gms_error_pkg.gms_debug('EVENT WRAPPER - AFTER INSERT EVENT +VE PLSQL>DIFF','C');
gms_error_pkg.gms_debug('EVENT WRAPPER - BEFORE INSERT EVENT +VE PLSQL
INSERT_EVENT(X_Award_Project_Id,
NULL, -- event number
X_Installment_Total(Rec_Count).Installment_id,
X_Actual_Project_id,
X_Task_Id,
X_Burden_Cost_Code,
X_Exp_Org_Id,
X_Bill_Amount,
X_Rev_Amount,
X_Request_Id,
NULL, -- Expenditure_Type
X_Err_Code,
X_Err_Buff,
X_calling_Process);
gms_error_pkg.gms_debug('EVENT WRAPPER - AFTER INSERT EVENT +VE PLSQL
gms_error_pkg.gms_debug('EVENT WRAPPER - BEFORE INSERT EVENT -VE','C');
INSERT_EVENT(X_Award_Project_Id,
NULL, -- event number
X_Installment_Total(Rec_Count).Installment_id,
X_Actual_Project_id,
X_Task_Id,
X_Burden_Cost_Code,
X_Exp_Org_Id,
X_Bill_Amount,
X_Rev_Amount,
X_Request_Id,
NULL, -- Expenditure_Type
X_Err_Code,
X_Err_Buff,
X_calling_Process);
gms_error_pkg.gms_debug('EVENT WRAPPER - AFTER INSERT EVENT -VE','C');
gms_error_pkg.gms_debug('EVENT WRAPPER - BEFORE ZERO $ EVENT INSERT','C');
INSERT_EVENT(X_Award_Project_Id,
-1, -- event_num
X_Installment_Total(Rec_Count).Installment_id,
X_Actual_Project_id,
X_Task_Id,
NULL, --X_Burden_Cost_Code,
NULL, --X_Exp_Org_Id,
0,
0,
X_Request_Id,
NULL, -- expenditure type,
X_Err_Code,
X_Err_Buff,
X_calling_Process);
gms_error_pkg.gms_debug('EVENT WRAPPER - AFTER ZERO $ EVENT INSERT ','C');
gms_error_pkg.gms_debug('EVENT WRAPPER - BEFORE INSERT EVENT DIFF <> 0','C');
INSERT_EVENT(X_Award_Project_Id,
NULL, -- event number
x_plsql_installment_id,
x_plsql_project_id,
x_plsql_task_id,
X_Burden_Cost_Code,
X_Exp_Org_Id,
X_Bill_Amount,
X_Rev_Amount,
X_Request_Id,
NULL, -- Expenditure_Type
X_Err_Code,
X_Err_Buff,
X_calling_Process);
gms_error_pkg.gms_debug('EVENT WRAPPER - AFTER INSERT EVENT DIFF <> 0','C');
/* This Procedure GET_FORMAT_SELECT returns: */
--1) The Award_ID of the Award Project
--2) The Carrying Out NOCOPY Organization Id of the Award Project
--3) An Array indicating which of the columns have groupby columns for Labor Format
--4) An Array indicating which of the columns have groupby columns for Non_Labor Format
--5) Count of Number of Columns Selected for the Labor Inv Format
--6) Count of Number of Columns Selected for the Non Labor Inv Format
--7) An Array indicating whether each Labor column selected is to be right justified or not
--8) An Array indicating whether each Non Labor Column selected is to be left justified or not
--9) An Array indicating what the Padding Length should be for each Labor Invoice Column selected
--10) An Array indicating what the Padding Length should be for each NonLabor Invoice Column selected
--11) An Array containing the Free Text for each Labor Format Text Column
--12) An Array containing the Free Text for each Non Labor Format Text Column
--13) A concatenated select for the Labor format
--14) A concatenated from for the Labor format
--15) A concatenated where for the Labor Format
--16) A concatenated order by for the Labor Format
--17) A concatenated select for the Non Labor format
--18) A concatenated from for the Non Labor format
--19) A concatenated where for the Non Labor Format
--20) A concatenated order by for the Non Labor Format
--21) If Task is used in Labor Invoice format or not
--22) If Task is used in Non Labor Invoice format or not
Procedure GET_FORMAT_SELECT(X_Project_Id IN NUMBER,
X_Award_Id IN OUT NOCOPY NUMBER,
X_Carrying_Out_Org_Id IN OUT NOCOPY NUMBER,
X_Labor_Sel_Grp_Diff_Ind OUT NOCOPY Mark_Sel_Grp_Diff_Array,
X_Non_Labor_Sel_Grp_Diff_Ind OUT NOCOPY Mark_Sel_Grp_Diff_Array,
X_Lbr_Cnt_Of_Columns_Selected IN OUT NOCOPY NUMBER,
X_Nlbr_Cnt_Of_Columns_Selected IN OUT NOCOPY NUMBER,
X_Lbr_Rt_Jstfy_Flag OUT NOCOPY Mark_Sel_Grp_Diff_Array,
X_Nlbr_Rt_Jstfy_Flag OUT NOCOPY Mark_Sel_Grp_Diff_Array,
X_Lbr_Padding_Length OUT NOCOPY Padding_Length_Array,
X_Nlbr_Padding_Length OUT NOCOPY Padding_Length_Array,
X_Lbr_Text_Array OUT NOCOPY Free_Text_Array,
X_Nlbr_Text_Array OUT NOCOPY Free_Text_Array,
X_LABOR_CONCAT_SELECT OUT NOCOPY VARCHAR2,
X_LABOR_CONCAT_FROM OUT NOCOPY VARCHAR2,
X_LABOR_CONCAT_WHERE OUT NOCOPY VARCHAR2,
X_LABOR_CONCAT_ORDERBY OUT NOCOPY VARCHAR2,
X_LABOR_ORDERBY_IS_NULL OUT NOCOPY VARCHAR2,
X_NON_LABOR_CONCAT_SELECT OUT NOCOPY VARCHAR2,
X_NON_LABOR_CONCAT_FROM OUT NOCOPY VARCHAR2,
X_NON_LABOR_CONCAT_WHERE OUT NOCOPY VARCHAR2 ,
X_NON_LABOR_CONCAT_ORDERBY OUT NOCOPY VARCHAR2,
X_NON_LABOR_ORDERBY_IS_NULL OUT NOCOPY VARCHAR2,
X_LABOR_tsk_lvl_fmt OUT NOCOPY VARCHAR2, /* added for bug 3523930 */
X_NON_LABOR_tsk_lvl_fmt OUT NOCOPY VARCHAR2, /* added for bug 3523930 */
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
X_LABOR_SELECT VARCHAR2(2000) := NULL;
X_NON_LABOR_SELECT VARCHAR2(2000) := NULL;
select
a.labor_invoice_format_id,
a.non_labor_invoice_format_id,
b.award_id, /*Award_Id*/
a.carrying_out_organization_id
from
PA_PROJECTS_ALL a,
GMS_AWARDS b
where
a.project_id = X_Project_Id and
b.award_project_id = a.project_id ;
/* CURSORS FOR Creating Selects , From and Where for Labor and Non Labor Invoice Formats */
/* Cursor to get the COLUMNS associated with the Invoice Format Details */
CURSOR Column_Cursor(X_INV_FORMAT_ID NUMBER) IS
select
b.start_position START_POSITION,
b.end_position END_POSITION,
b.right_justify_flag RT_FLAG,
b.text TEXT,
a.column_code COL_CODE,
a.select_text SELECT_TEXT,
a.group_by_text GROUP_TEXT
from
pa_invoice_group_columns a,
pa_invoice_format_details b
where
a.invoice_group_column_id = b.invoice_group_column_id and
b.invoice_format_id = X_INV_FORMAT_ID
order by b.start_position;
select /*+INDEX(a PA_INVOICE_GROUP_TABLES_N1)*/
distinct a.text TABLE_TEXT
from
pa_invoice_group_tables a,
pa_invoice_group_columns b,
pa_invoice_format_details c
where
b.invoice_group_column_id = c.invoice_group_column_id and
a.invoice_group_column_id = c.invoice_group_column_id and
c.invoice_format_id = X_INV_FORMAT_ID;
select /*+INDEX(b PA_INV_GRP_WHR_CLAUSES_U1)*/
distinct b.text WHERE_TEXT
from
pa_inv_grp_col_whr_clauses a,
pa_inv_grp_whr_clauses b,
pa_invoice_group_columns c,
pa_invoice_format_details d
where
b.invoice_group_where_clause_id = a.invoice_group_where_clause_id and
a.invoice_group_column_id = c.invoice_group_column_id and
c.invoice_group_column_id = d.invoice_group_column_id and
d.invoice_format_id = X_INV_FORMAT_ID;
X_LABOR_SELECT := NULL;
X_NON_LABOR_SELECT := NULL;
X_Lbr_Cnt_Of_Columns_Selected := 0;
X_Nlbr_Cnt_Of_Columns_Selected:= 0;
/* Fetching for Labor Invoice Format SELECT, FROM, WHERE */
/*==========================================================*/
FOR Column_Record IN Column_Cursor(X_Labor_Invoice_Format_Id) LOOP
If (Column_Record.SELECT_TEXT is NOT NULL) then
X_LABOR_SELECT := X_LABOR_SELECT||Column_Record.SELECT_TEXT||',';
X_Lbr_Cnt_Of_Columns_Selected := X_Lbr_Cnt_Of_Columns_Selected + 1;
X_Lbr_Rt_Jstfy_Flag(X_Lbr_Cnt_Of_Columns_Selected) := Column_Record.RT_FLAG;
X_Lbr_Padding_Length(X_Lbr_Cnt_Of_Columns_Selected) := (Column_Record.END_POSITION -
Column_Record.START_POSITION);
X_Labor_Sel_Grp_Diff_Ind(X_Lbr_Cnt_Of_Columns_Selected) := 'Y';
X_Labor_Sel_Grp_Diff_Ind(X_Lbr_Cnt_Of_Columns_Selected) := 'T';
X_Lbr_Text_Array(X_Lbr_Cnt_Of_Columns_Selected) := Column_Record.TEXT;
X_Lbr_Rt_Jstfy_Flag(X_Lbr_Cnt_Of_Columns_Selected) := Column_Record.RT_FLAG;
X_Lbr_Padding_Length(X_Lbr_Cnt_Of_Columns_Selected) := (Column_Record.END_POSITION -
Column_Record.START_POSITION) + 1;
X_Labor_Sel_Grp_Diff_Ind(X_Lbr_Cnt_Of_Columns_Selected) := 'N';
X_LABOR_CONCAT_SELECT := X_LABOR_SELECT;
/* Fetching Non Labor Invoice Format Select, From, Where */
/*=============================================================*/
FOR Column_Record IN Column_Cursor(X_Non_Labor_Invoice_Format_Id) LOOP
If (Column_Record.SELECT_TEXT is NOT NULL) then
X_NON_LABOR_SELECT := X_NON_LABOR_SELECT||Column_Record.SELECT_TEXT||',';
X_Nlbr_Cnt_Of_Columns_Selected := X_Nlbr_Cnt_Of_Columns_Selected + 1;
X_Nlbr_Rt_Jstfy_Flag(X_Nlbr_Cnt_Of_Columns_Selected) := Column_Record.RT_FLAG;
X_Nlbr_Padding_Length(X_Nlbr_Cnt_Of_Columns_Selected) := (Column_Record.END_POSITION -
Column_Record.START_POSITION);
X_Non_Labor_Sel_Grp_Diff_Ind(X_Nlbr_Cnt_Of_Columns_Selected) := 'Y';
X_Non_Labor_Sel_Grp_Diff_Ind(X_Nlbr_Cnt_Of_Columns_Selected) := 'T';
X_Nlbr_Text_Array(X_Nlbr_Cnt_Of_Columns_Selected) := Column_Record.TEXT;
X_Nlbr_Rt_Jstfy_Flag(X_Nlbr_Cnt_Of_Columns_Selected) := Column_Record.RT_FLAG;
X_Nlbr_Padding_Length(X_Nlbr_Cnt_Of_Columns_Selected) := (Column_Record.END_POSITION -
Column_Record.START_POSITION) + 1;
X_Non_Labor_Sel_Grp_Diff_Ind(X_Nlbr_Cnt_Of_Columns_Selected) := 'N';
X_NON_LABOR_CONCAT_SELECT := X_NON_LABOR_SELECT;
END GET_FORMAT_SELECT;
select
nvl(sum(nvl(amount,0)),0)
into
X_Current_Amount
from
GMS_EVENT_INTERSECT
where
expenditure_item_id = X_Expenditure_Item_Id and
adl_line_num = X_Adl_Line_Num and
event_type = decode(X_Calling_Process,'Invoice','INVOICE','Revenue','REVENUE');
Select
nvl(sum(nvl(amount,0)),0)
into X_Burden_Amt_In_Table
from
GMS_BURDEN_COMPONENTS
where
expenditure_item_id = X_Expenditure_Item_Id
and adl_line_num = X_Adl_Line_Num
and burden_cost_code = X_Burden_Cost_Code
and event_type = decode(X_Calling_Process,'Invoice','INVOICE','Revenue','REVENUE');
select
1
into
X_Check_Row_Exists
from
gms_summary_project_fundings gmf
where
gmf.installment_id = C_Installment_Id
and (
(gmf.task_id = X_Task_Id)
OR (gmf.task_id is NULL)
OR (gmf.task_id = (select t.top_task_id from PA_TASKS t where t.task_id = X_Task_Id))
)
and gmf.project_id = (select project_id from PA_TASKS where task_id = X_Task_Id);
PROCEDURE GET_ACCRUE_BILL_OR_INSERT_AMT(X_Expenditure_Item_Id IN NUMBER,
X_Adl_Line_Num IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Raw_Cost IN NUMBER,
X_Billable_Flag IN VARCHAR2,
X_Bill_Hold_Flag IN VARCHAR2,
X_Amount_To_Accrue_Bill_Insert OUT NOCOPY NUMBER,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
X_Current_Amount_In_Intersect NUMBER; -- Current Amount in Intersect table for that Exp Item
X_Amount_To_Accrue_Bill_Insert := (-1 * X_Current_Amount_In_Intersect);
X_Amount_To_Accrue_Bill_Insert := 0;
X_Amount_To_Accrue_Bill_Insert := (X_Raw_Cost - X_Current_Amount_In_Intersect);
X_Amount_To_Accrue_Bill_Insert := (-1 * X_Current_Amount_In_Intersect);
X_Amount_To_Accrue_Bill_Insert := 0;
X_Amount_To_Accrue_Bill_Insert := (X_Raw_Cost - X_Current_Amount_In_Intersect);
End GET_ACCRUE_BILL_OR_INSERT_AMT;
PROCEDURE GET_BURDEN_AMT_TO_INSERT(X_Expenditure_Item_Id IN NUMBER,
X_Adl_Line_Num IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Burden_Cost_Code IN VARCHAR2,
X_Billable_Flag IN VARCHAR2,
X_Bill_Hold_Flag IN VARCHAR2,
X_Burden_Amt_From_Vw IN NUMBER,
X_Burden_Amt_To_Insert OUT NOCOPY NUMBER) IS
X_Curr_Burden_Amt_In_Table NUMBER;
X_Burden_Amt_To_Insert := (-1 * X_Curr_Burden_Amt_In_Table);
X_Burden_Amt_To_Insert := 0;
X_Burden_Amt_To_Insert := (X_Burden_Amt_From_Vw - X_Curr_Burden_Amt_In_Table);
X_Burden_Amt_To_Insert := (-1 * X_Curr_Burden_Amt_In_Table);
X_Burden_Amt_To_Insert := 0;
X_Burden_Amt_To_Insert := (X_Burden_Amt_From_Vw - X_Curr_Burden_Amt_In_Table);
End GET_BURDEN_AMT_TO_INSERT;
PROCEDURE INSERT_GMS_BURDEN_COMPONENTS(X_Award_Project_Id IN NUMBER,
X_Expenditure_Item_Id IN NUMBER,
X_Adl_Line_Num IN NUMBER,
X_Request_Id IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Actual_Project_Id IN NUMBER,
X_Actual_Task_Id IN NUMBER,
X_Burden_Expenditure_Type IN VARCHAR2,
X_Burden_Cost_Code IN VARCHAR2,
X_Expenditure_Org_Id IN NUMBER,
X_Burd_Amt_To_Insert IN NUMBER,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
X_Err_Code Varchar2(1);
If X_Burd_Amt_To_Insert <> 0 then
Begin
INSERT INTO GMS_BURDEN_COMPONENTS(AWARD_PROJECT_ID,
EXPENDITURE_ITEM_ID,
ADL_LINE_NUM,
AMOUNT,
REQUEST_ID,
EVENT_TYPE,
ACTUAL_PROJECT_ID,
ACTUAL_TASK_ID,
BURDEN_EXP_TYPE,
EXPENDITURE_ORG_ID,
BURDEN_COST_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REVENUE_ACCUMULATED,
RESOURCE_LIST_MEMBER_ID)
VALUES(X_Award_Project_Id,
X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Burd_Amt_To_Insert,
X_request_id,
decode(X_Calling_Process,'Invoice','INVOICE','Revenue','REVENUE'),
X_Actual_Project_Id,
X_Actual_Task_Id,
X_Burden_Expenditure_Type,
X_Expenditure_Org_Id,
X_Burden_Cost_Code,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
decode(X_Calling_Process,'Invoice','X','Revenue','N'), -- Added decode for bug 5472366
null);
gms_error_pkg.gms_debug('Inserted into GBC, EXP/ADL:'||X_Expenditure_Item_Id||':'||X_Adl_Line_Num,'C');
End INSERT_GMS_BURDEN_COMPONENTS;
PROCEDURE INSERT_GMS_EVENT_INTERSECT(X_Award_Project_Id IN NUMBER,
X_Raw_Cost IN NUMBER,
X_Expenditure_Item_Id IN NUMBER,
X_Adl_Line_Num IN NUMBER,
X_request_id IN NUMBER,
X_Amount_To_Insert IN OUT NOCOPY NUMBER,
X_Calling_Process IN VARCHAR2,
X_Billable_Flag IN VARCHAR2,
X_Bill_Hold_Flag IN VARCHAR2,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2) IS
X_Err_Code Varchar2(1);
GET_ACCRUE_BILL_OR_INSERT_AMT(X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Calling_Process,
X_Raw_Cost,
X_Billable_Flag,
X_Bill_Hold_Flag,
X_Amount_To_Insert,
X_Err_Num,
X_Err_Stage);
X_Amount_To_Insert := pa_currency.round_currency_amt(nvl(X_Amount_To_Insert,0)); -- added for bug 5182669
If X_Amount_To_Insert <> 0 then
Begin
INSERT INTO GMS_EVENT_INTERSECT(AWARD_PROJECT_ID,
EXPENDITURE_ITEM_ID,
ADL_LINE_NUM,
AMOUNT,
REQUEST_ID,
EVENT_TYPE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REVENUE_ACCUMULATED)
VALUES(X_Award_Project_Id,
X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Amount_To_Insert,
X_request_id,
decode(X_Calling_Process,'Invoice','INVOICE','Revenue','REVENUE'),
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
decode(X_Calling_Process,'Invoice','X','Revenue','N')); -- Added decode for bug 5472366
gms_error_pkg.gms_debug('Inserted into GEI, EXP/ADL:'||X_Expenditure_Item_Id||':'||X_Adl_Line_Num,'C');
End INSERT_GMS_EVENT_INTERSECT;
PROCEDURE UPDATE_GMS_EVENT_INTERSECT(X_Event_Num IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_request_id IN NUMBER,
X_ACT_PROJECT_ID IN NUMBER,
X_TASK_ID IN NUMBER) IS
X_Err_Code Varchar2(1);
update GMS_EVENT_INTERSECT gei
set
gei.EVENT_NUM = X_Event_Num
,gei.last_update_date = sysdate
,gei.last_updated_by = fnd_global.user_id
,gei.last_update_login = fnd_global.login_id
where gei.REQUEST_ID = X_Request_id and
gei.award_project_id = X_Award_Project_Id and
gei.EVENT_NUM IS NULL
AND EXISTS ( -- Bug 3235390 : Added below conditions to check for project and task.
SELECT gei2.expenditure_item_id
FROM gms_award_distributions adl,
gms_event_intersect gei2
WHERE gei2.expenditure_item_id = gei.expenditure_item_id
AND adl.expenditure_item_id = gei2.expenditure_item_id
AND adl.adl_status = 'A'
AND adl.document_type ='EXP'
AND adl.project_id = x_act_project_id
AND adl.task_id = x_task_id);
End UPDATE_GMS_EVENT_INTERSECT;
PROCEDURE UPDATE_GMS_BURDEN_COMPONENTS(X_Event_Num IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Request_Id IN NUMBER,
X_Actual_Project_Id IN NUMBER,
X_Actual_Task_Id IN NUMBER,
X_Burden_Cost_Code IN VARCHAR2,
X_Expenditure_Org_Id IN NUMBER) IS
X_Err_Code Varchar2(1);
update /*+INDEX(GMS_BURDEN_COMPONENTS GMS_BURDEN_COMPONENTS_N3)*/ GMS_BURDEN_COMPONENTS
set
EVENT_NUM = X_Event_Num
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
where
request_id = X_request_id and
award_project_id = X_Award_Project_Id and
EVENT_NUM IS NULL and
actual_project_id = X_Actual_Project_Id and
actual_task_id = X_Actual_Task_Id and
burden_cost_code = X_Burden_Cost_Code and
expenditure_org_id = X_Expenditure_Org_Id;
End UPDATE_GMS_BURDEN_COMPONENTS;
update GMS_EVENT_ATTRIBUTE
set
EVENT_NUM = X_Event_Num
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
where
request_id = X_request_id and
project_id = X_Award_Project_Id and
EVENT_NUM IS NULL;
Select 1
into x_count
from dual
where exists
(select 1
from gms_event_attribute
where request_id = X_request_id
and project_id = X_Award_Project_Id
and EVENT_NUM IS NULL);
PROCEDURE UPDATE_GMS_SUMMARY_FUNDINGS(X_Installment_Id IN NUMBER,
X_Task_Id IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Rev_Bill_Amount IN NUMBER,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
X_Total_Funding_Amount NUMBER(22,5) := 0;
update GMS_SUMMARY_PROJECT_FUNDINGS spf
set
spf.TOTAL_BILLED_AMOUNT = (X_Rev_Bill_Amount + X_Total_Rev_Bill_Amount),
spf.last_update_date = sysdate,
spf.last_updated_by = fnd_global.user_id,
spf.last_update_login = fnd_global.login_id
where
spf.INSTALLMENT_ID = X_Installment_Id
and (
(spf.TASK_ID = X_Task_Id)
or (spf.TASK_ID IS NULL)
or (spf.TASK_ID = (select t.top_task_id from PA_TASKS t where
t.task_id = X_Task_Id))
)
and PROJECT_ID = (select project_id from pa_tasks where task_id = X_Task_Id);
update GMS_SUMMARY_PROJECT_FUNDINGS spf
set
spf.TOTAL_REVENUE_AMOUNT = (X_Rev_Bill_Amount + X_Total_Rev_Bill_Amount),
spf.last_update_date = sysdate,
spf.last_updated_by = fnd_global.user_id,
spf.last_update_login = fnd_global.login_id
where
spf.INSTALLMENT_ID = X_Installment_Id
and (
(spf.TASK_ID = X_Task_Id)
or (spf.TASK_ID IS NULL)
or (spf.TASK_ID = (select t.top_task_id from PA_TASKS t where
t.task_id = X_Task_Id))
)
and PROJECT_ID = (select project_id from pa_tasks where task_id = X_Task_Id);
END UPDATE_GMS_SUMMARY_FUNDINGS;
UPDATE_GMS_SUMMARY_FUNDINGS(X_Installment_Id,
X_Task_Id,
X_Calling_Process,
X_Rev_Bill_Amount,
X_Err_Code,
X_Err_Buff);
UPDATE_GMS_SUMMARY_FUNDINGS(X_Install_tab(X_Count_Reqd).Installment_Id,
X_Task_Id,
X_Calling_Process,
X_Install_tab(X_Count_Reqd).Rev_Bill_Amount,
X_Err_Code,
X_Err_Buff);
update
GMS_AWARD_DISTRIBUTIONS
set
BILLED_FLAG = 'Y'
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
where expenditure_item_id = X_Expenditure_Item_Id
and adl_line_num = X_Adl_Line_Num
and document_type='EXP'
and adl_status = 'A';
update
GMS_AWARD_DISTRIBUTIONS
set
revenue_distributed_flag = 'Y'
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
where expenditure_item_id = X_Expenditure_Item_Id
and adl_line_num = X_Adl_Line_Num
and document_type='EXP'
and adl_status = 'A';
Select SUM(NVL( DECODE(adl.line_num_reversed, NULL, peia.quantity,-1*peia.quantity),0))
into x_qty
from pa_expenditure_items_all peia,
gms_event_intersect gei,
gms_award_distributions adl
where peia.expenditure_item_id = gei.expenditure_item_id
and adl.expenditure_item_id = gei.expenditure_item_id
and adl.adl_line_num = gei.adl_line_num
and adl.document_type ='EXP'
and adl.adl_status ='A'
and gei.request_id = X_Req_id
and gei.award_project_id = X_Proj_Id
and gei.event_num is null;
SELECT event_num
FROM gms_events_temp_format
WHERE act_project_id = p_act_project_id
AND task_id = p_task_id
AND NVL(format,'X') = NVL(p_format,'X');
SELECT event_num
FROM gms_events_temp_format
WHERE NVL(format,'X') = NVL(p_format,'X');
UPDATE gms_events_temp_format
SET amount = amount + p_amount,
quantity = quantity + p_quantity,
description = decode(p_calling_place,'Revenue',p_description || '- '|| to_char(quantity + p_quantity) || ' ' ||p_units,p_description)
WHERE event_num = p_Event_num
AND act_project_id = p_act_project_id
AND task_id = p_task_id
AND nvl(format,'X') = nvl(p_format,'X'); -- for bug 5413530
INSERT INTO gms_events_temp_format(Event_num ,
ACT_PROJECT_ID,
TASK_ID,
QUANTITY,
AMOUNT,
FORMAT,
DESCRIPTION)
VALUES (p_event_num,
p_act_project_id,
p_task_id,
p_quantity,
p_Amount,
p_format,
l_description );
gms_error_pkg.gms_debug('IN PROCESS_TEMP_EVENTS - After inserting event '||p_event_num||' into gms_events_temp_format for task '||p_task_id ||' with amount '||p_amount,'C');
SELECT event_num,
format,
description,
SUM(amount) amount
FROM gms_events_temp_format
GROUP BY event_num,format,description
ORDER BY event_num desc;
SELECT act_project_id,
task_id,
SUM(amount) amount
FROM gms_events_temp_format
WHERE event_num = p_evt_num
AND NVL(format,'X') = NVL(p_format,'X')
AND description = p_description
GROUP BY act_project_id,task_id;
pa_billing_pub.insert_event(
X_rev_amt => X_rev_amt,
X_bill_amt => X_bill_amt,
X_project_id => p_project_id,
X_event_type => 'AWARD_BILLING',
X_top_task_id => NULL,
X_organization_id => p_Carrying_Out_Org_Id,
X_completion_date => p_completion_date,
X_event_description => pa_events_rec.description,
X_event_num_reversed => NULL,
X_attribute_category => NULL,
X_attribute1 => NULL,
X_attribute2 => NULL,
X_attribute3 => NULL,
X_attribute4 => NULL,
X_attribute5 => NULL,
X_attribute6 => NULL,
X_attribute7 => NULL,
X_attribute8 => NULL,
X_attribute9 => NULL,
X_attribute10 => NULL,
X_error_message => St_Error_Message,
X_status => St_Status
);
gms_error_pkg.gms_debug('IN CREATE_PA_EVENTS - After insert_event call for event number '||Evt_Num||' with amount '||pa_events_rec.amount,'C');
gms_error_pkg.gms_debug('IN CREATE_PA_EVENTS - After insert_event call value of St_Error_Message '||St_Error_Message,'C');
gms_error_pkg.gms_debug('IN CREATE_PA_EVENTS - After insert_event call value of St_Status '||St_Status,'C');
UPDATE gms_event_intersect
SET Event_Num = Evt_num
WHERE Event_num = pa_events_rec.event_num
AND award_project_id = p_project_id; /* Added for bug 4172924*/
X_Values IN Selected_Values_Rows,
X_Padding_Length IN Padding_Length_Array,
X_Run_Total IN Running_Total_Array ,
X_Text_Array IN Free_Text_Array,
X_Proj_Id IN NUMBER DEFAULT NULL,
X_Task_Id IN NUMBER DEFAULT NULL,
X_invfmt_incl_task IN VARCHAR2 DEFAULT 'N', /* Bug 3523930 */
X_Evt_Amount IN NUMBER DEFAULT NULL,
X_Carry_Out_Org_Id IN NUMBER DEFAULT NULL,
X_Through_Date IN DATE DEFAULT SYSDATE,
X_Call_Process IN VARCHAR2 DEFAULT NULL,
X_Req_Id IN NUMBER DEFAULT NULL,
C_Installment_Id IN NUMBER,
X_Install_Count IN NUMBER,
X_Installment_Total IN OUT NOCOPY Inst_tab2,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
CURSOR GET_RAW_ROWS_FROM_INTERSECT IS
Select
gei.award_project_id Award_Project_Id
,adl.project_id Actual_Project_Id
,adl.task_id Actual_Task_Id
,sum(gei.Amount) amount
from
gms_event_intersect gei,
gms_award_distributions adl
where
gei.award_project_id = X_Proj_Id and
gei.request_id = X_Req_Id and
gei.event_type = 'INVOICE' and
gei.event_num is NULL and
adl.expenditure_item_id = gei.expenditure_item_id and
adl.adl_line_num = gei.adl_line_num and
adl.document_type ='EXP' and
adl.adl_status ='A'
group by
gei.award_project_id,
adl.project_id,
adl.task_id;
UPDATE_GMS_EVENT_INTERSECT(Evt_Num,
X_Proj_Id,
X_Req_Id,
raw_events.actual_project_id,
raw_events.actual_task_id );
gms_error_pkg.gms_debug('IN DO_EVENT_PROCESSING - After UPDATE_GMS_EVENT_INTERSECT','C');
Select
Award_Project_Id
,Actual_Project_Id
,Actual_Task_Id
,Burden_Cost_Code
,Expenditure_Org_Id
,sum(Amount)
from
GMS_BURDEN_COMPONENTS
where
award_project_id = X_Project_Id and
request_id = X_Request_Id and
event_type = decode(X_Calling_Process,'Revenue','REVENUE','Invoice','INVOICE') and
event_num is NULL
group by
Award_Project_Id
,Actual_Project_Id
,Actual_Task_Id
,Burden_Cost_Code
,Expenditure_Org_Id ;
Select
a.segment1,
b.task_number,
c.name
into
Ins_Act_Proj_Num,
Ins_Act_Task_Num,
Ins_Exp_Org_Name
from
pa_projects_all a,
pa_tasks b,
hr_organization_units c
where a.project_id = Ins_Actual_Project_Id and
b.task_id = Ins_Actual_Task_Id and
c.organization_id = Ins_Expenditure_Org_Id;
pa_billing_pub.insert_event(
X_rev_amt => Ins_Amount, /* X_rev_amt */
X_bill_amt => 0, /* X_bill_amt */
X_project_id => Ins_Award_Project_Id, /* X_project_id */
X_event_type => 'AWARD_BILLING', /* X_event_type */
X_top_task_id => NULL, /* X_top_task_id */
X_organization_id => X_Carrying_Out_Org_Id, /* X_organization_id */
X_completion_date => X_Through_Date, /* X_completion_date */
X_event_description => Evt_Description, /* X_event_description */
X_event_num_reversed => NULL, /* Event Num Reversed */
X_attribute_category => NULL, /* X_attribute_category */
X_attribute1 => NULL, /* X_attribute1 */
X_attribute2 => NULL, /* X_attribute2 */
X_attribute3 => NULL, /* X_attribute3 */
X_attribute4 => NULL, /* X_attribute4 */
X_attribute5 => NULL, /* X_attribute5 */
X_attribute6 => NULL, /* X_attribute6 */
X_attribute7 => NULL, /* X_attribute7 */
X_attribute8 => NULL, /* X_attribute8 */
X_attribute9 => NULL, /* X_attribute9 */
X_attribute10 => NULL, /* X_attribute10 */
X_error_message => St_Error_Message,
X_status => St_Status
);
pa_billing_pub.insert_event(
X_rev_amt => 0, /* X_rev_amt */
X_bill_amt => Ins_Amount, /* X_bill_amt */
X_project_id => Ins_Award_Project_Id, /* X_project_id */
X_event_type => 'AWARD_BILLING', /* X_event_type */
X_top_task_id => NULL, /* X_top_task_id */
X_organization_id => X_Carrying_Out_Org_Id, /* X_organization_id */
X_completion_date => X_Through_Date, /* X_completion_date */
X_event_description => Evt_Description, /* X_event_description */
X_event_num_reversed => NULL, /* Event Num Reversed */
X_attribute_category => NULL, /* X_attribute_category */
X_attribute1 => NULL, /* X_attribute1 */
X_attribute2 => NULL, /* X_attribute2 */
X_attribute3 => NULL, /* X_attribute3 */
X_attribute4 => NULL, /* X_attribute4 */
X_attribute5 => NULL, /* X_attribute5 */
X_attribute6 => NULL, /* X_attribute6 */
X_attribute7 => NULL, /* X_attribute7 */
X_attribute8 => NULL, /* X_attribute8 */
X_attribute9 => NULL, /* X_attribute9 */
X_attribute10 => NULL, /* X_attribute10 */
X_error_message => St_Error_Message,
X_status => St_Status
);
End If; -- DO NOT INSERT ZERO AMOUNT EVENTS
UPDATE_GMS_BURDEN_COMPONENTS(Evt_Num ,
Ins_Award_Project_Id,
X_Request_Id ,
Ins_Actual_Project_Id ,
Ins_Actual_Task_Id ,
Ins_Burden_Cost_Code ,
Ins_Expenditure_Org_Id );
SELECT 'Y'
FROM gms_event_intersect gei,
gms_award_distributions adl
WHERE gei.award_project_id = X_Project_Id
AND gei.request_id = X_Request_Id
AND gei.event_type = 'REVENUE'
AND gei.event_num is NULL
AND adl.expenditure_item_id = gei.expenditure_item_id
AND adl.adl_line_num = gei.adl_line_num
AND adl.document_type ='EXP'
AND adl.adl_status ='A'
AND adl.project_id = X_Act_Project_Id
AND adl.task_id = X_Task_Id
AND ROWNUM =1 ;
Select
a.segment1,
b.task_number,
c.name
into
Ins_Act_Proj_Num,
Ins_Act_Task_Num,
Ins_Exp_Org_Name
from
pa_projects_all a,
pa_tasks b,
hr_organization_units c
where a.project_id = X_Act_Project_Id and
b.task_id = X_Task_Id and
c.organization_id = X_Expenditure_Org_Id;
UPDATE_GMS_EVENT_INTERSECT(Evt_Num,
X_Project_Id,
X_Request_Id,
X_Act_Project_Id,
X_Task_Id );
gms_error_pkg.gms_debug('IN DO_REV_EVENT_PROCESSING - After UPDATE_GMS_EVENT_INTERSECT','C');
SELECT cm.ind_cost_code,
icc.expenditure_type icc_expenditure_type,
sum(pa_currency.round_currency_amt (P_burdenable_raw_cost * cm.compiled_multiplier)) Tot_Exp_Item_Burden_Cost
FROM pa_ind_cost_codes icc,
pa_compiled_multipliers cm,
--pa_ind_compiled_sets ics, /* For bug 6969435 */
pa_cost_base_exp_types cbet,
PA_COST_BASE_COST_CODES CBCC /* For bug 6969435 */
--pa_cost_bases cb,/* For bug 6969435 */
--pa_ind_rate_sch_revisions irsr,/* For bug 6969435 */
--pa_ind_rate_schedules_all_bg irs/* For bug 6969435 */
WHERE --ics.ind_rate_sch_revision_id = irsr.ind_rate_sch_revision_id/* For bug 6969435 */
--AND irs.ind_rate_sch_id = irsr.ind_rate_sch_id/* For bug 6969435 */
--AND irsr.cost_plus_structure = cbet.cost_plus_structure/* For bug 6969435 */
cbet.cost_base = cm.cost_base
--AND cb.cost_base = cbet.cost_base/* For bug 6969435 */
--AND ics.cost_base = cbet.cost_base/* For bug 6969435 */
--AND cb.cost_base_type = cbet.cost_base_type/* For bug 6969435 */
AND cbet.cost_base_type = 'INDIRECT COST'
AND cbet.expenditure_type = P_expenditure_type
--AND ics.organization_id = P_expenditure_org_id/* For bug 6969435 */
--AND ics.ind_compiled_set_id = cm.ind_compiled_set_id/* For bug 6969435 */
AND icc.ind_cost_code = cm.ind_cost_code
AND cm.ind_compiled_set_id = P_ind_compiled_set_id /* For bug 6969435 */
AND cbcc.cost_plus_structure = cbet.cost_plus_structure /* For bug 6969435 */
AND cbcc.cost_base = cbet.cost_base /* For bug 6969435 */
AND cbcc.cost_base_type = cbet.cost_base_type /* For bug 6969435 */
AND cm.cost_base_cost_code_Id = cbcc.cost_base_cost_code_Id /* For bug 6969435 */
AND cm.ind_cost_code = cbcc.ind_cost_code /* For bug 6969435 */
and cm.compiled_multiplier <> 0
group by cm.ind_cost_code, icc.expenditure_type;
X_Cnt_Of_Columns_Selected IN NUMBER,
X_Rt_Jstfy_Flag_Array IN Mark_Sel_Grp_Diff_Array,
X_Padding_Length_Array IN Padding_Length_Array,
X_Text_Array IN Free_Text_Array,
X_sql_select IN VARCHAR2,
X_Carrying_Out_Org_Id IN NUMBER,
X_calling_process IN VARCHAR2,
X_invfmt_incl_task IN VARCHAR2, /* Added for bug 3523930 */
C_Installment_Id IN NUMBER,
C_Start_Date_Active IN DATE,
C_End_Date_Active IN DATE,
X_Err_Num OUT NOCOPY NUMBER,
X_Err_Stage OUT NOCOPY VARCHAR2,
g_mode IN VARCHAR2, /* added for bug 5026657 */
g_labor_exp_to_process OUT NOCOPY VARCHAR2, /* added for bug 5026657 */
g_non_labor_neg_exp_processed OUT NOCOPY VARCHAR2 /* added for bug 5026657 */
) IS
X_Run_Total Running_Total_Array;
X_Old_Values Selected_Values_Rows;
X_New_Values Selected_Values_Rows;
cur_select INTEGER := 0;
X_Amount_To_Insert NUMBER(22,5) := 0; -- Amount to be Inserted into Intersect Table,(X_Raw_Cost - X_Amount_In_Intersect)
X_Amount_To_Accrue_Bill_Insert NUMBER;
X_Burd_Amt_To_Insert_By_Comp NUMBER := 0;
X_Tot_Burd_Amt_To_Insert NUMBER := 0;
cur_select := DBMS_SQL.OPEN_CURSOR;
For i in 1..X_Cnt_Of_Columns_Selected LOOP
X_Old_Values(i) := NULL;
DBMS_SQL.PARSE(cur_select,X_sql_select,dbms_sql.native);
DBMS_SQL.BIND_VARIABLE(cur_select,':X_Award_Id', X_Award_Id);
DBMS_SQL.BIND_VARIABLE(cur_select,':X_rev_or_bill_date',X_rev_or_bill_date);
DBMS_SQL.BIND_VARIABLE(cur_select,':C_End_Date_Active',C_End_Date_Active);
DBMS_SQL.BIND_VARIABLE(cur_select,':C_Installment_id',C_Installment_id);
For i in 1..X_Cnt_Of_Columns_Selected LOOP
DBMS_SQL.DEFINE_COLUMN(cur_select,i, X_Old_Values(i),1000);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 1, X_Raw_Cost);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 2, X_Expenditure_Item_Id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 3, X_Expenditure_Item_Date);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 4, X_Task_Id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 5, X_Bill_Hold_Flag,1);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 6, X_Billable_Flag,1);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 7, X_Adjusted_Expenditure_Item_Id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 8, X_Adl_Line_Num);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 9, X_Parent_Adl_Line_Num);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 10, X_Adl_Status,1);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 11, X_Bill_Award_Id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 12, X_Actual_Project_Id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 13, X_Cdl_Line_Num); --bug 2909746
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 14, X_expenditure_type,30);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 15, X_expenditure_org_id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 16, X_ind_compiled_set_id);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 17, X_burdenable_raw_cost);
DBMS_SQL.DEFINE_COLUMN(cur_select,X_Cnt_Of_Columns_Selected + 18, X_transaction_source,30);
X_Rows_Processed := DBMS_SQL.EXECUTE(cur_select);
If DBMS_SQL.FETCH_ROWS(cur_select) > 0 then --Start of 'Cursor_Rows_Check_If'
/* Initializing the X_Burden_Component_Data table */
X_Burden_Component_Data.DELETE;
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 1, X_Raw_Cost);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 2, X_Expenditure_Item_Id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 3, X_Expenditure_Item_Date);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 4, X_Task_Id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 5, X_Bill_Hold_Flag);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 6, X_Billable_Flag);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 7, X_Adjusted_Expenditure_Item_Id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 8, X_Adl_Line_Num);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 9, X_Parent_Adl_Line_Num);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 10,X_Adl_Status);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 11,X_Bill_Award_Id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 12,X_Actual_Project_Id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 13,X_Cdl_Line_Num); --bug 2909746
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 14,X_expenditure_type);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 15,X_expenditure_org_id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 16,X_ind_compiled_set_id);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 17,X_burdenable_raw_cost);
DBMS_SQL.COLUMN_VALUE(cur_select,X_Cnt_Of_Columns_Selected + 18,X_transaction_source);
select expenditure_item_id
into
X_Lock_Exp_Id
from
pa_expenditure_items_all
where
expenditure_item_id = X_Expenditure_Item_Id
FOR UPDATE NOWAIT;
select expenditure_item_id
into
X_Adl_Lock_Exp_Id
from
gms_award_distributions
where expenditure_item_id = X_Expenditure_Item_Id
and adl_line_num=X_Adl_Line_Num
and document_type='EXP'
and adl_status = 'A'
FOR UPDATE NOWAIT;
Select
nvl(adl.billed_flag,'N')
into
X_Orig_Item_Billed_Flag
from
gms_award_distributions adl
where
expenditure_item_id = X_Adjusted_Expenditure_Item_Id
and
award_id = X_Bill_award_id
and
adl_status='A'
and
document_type ='EXP'
and
adl_line_num =
(select max(adl_line_num)
from gms_award_distributions
where expenditure_item_id = X_Adjusted_Expenditure_Item_Id
and award_id = X_Bill_award_id
and adl_status='A'
and document_type ='EXP');
X_Tot_Burd_Amt_To_Insert := 0;
GET_ACCRUE_BILL_OR_INSERT_AMT(X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Calling_Process,
X_Raw_Cost,
X_Billable_Flag,
X_Bill_Hold_Flag,
X_Amount_To_Accrue_Bill_Insert,
X_Err_Num,
X_Err_Stage);
GET_BURDEN_AMT_TO_INSERT(X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Calling_Process,
X_Burden_Component_Data(i).Burden_Cost_Code,
X_Billable_Flag,
X_Bill_Hold_Flag,
X_Burden_Component_Data(i).Burden_Cost,
X_Burd_Amt_To_Insert_By_Comp);
X_Burd_Amt_To_Insert_By_Comp := pa_currency.round_currency_amt(nvl(X_Burd_Amt_To_Insert_By_Comp,0));
INSERT_GMS_BURDEN_COMPONENTS(X_Project_Id,
X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Request_Id,
X_Calling_Process,
X_Burden_Component_Data(i).Actual_Project_Id,
X_Burden_Component_Data(i).Actual_Task_Id,
X_Burden_Component_Data(i).Burden_Expenditure_Type,
X_Burden_Component_Data(i).Burden_Cost_Code,
X_Burden_Component_Data(i).Expenditure_Org_Id,
X_Burd_Amt_To_Insert_By_Comp,
X_Err_Num,
X_Err_Stage);
X_Tot_Burd_Amt_To_Insert := X_Tot_Burd_Amt_To_Insert + X_Burd_Amt_To_Insert_By_Comp;
C_Inst_Task_Run_Total := nvl(C_Inst_Task_Run_Total,0) + (nvl(X_Amount_To_Accrue_Bill_Insert,0) + nvl(X_Tot_Burd_Amt_To_Insert,0));
(nvl(X_Amount_To_Accrue_Bill_Insert,0) + nvl(X_Tot_Burd_Amt_To_Insert,0)) < 0 ) THEN
g_non_labor_neg_exp_processed := 'Y' ;
For i in 1..X_Cnt_Of_Columns_Selected LOOP
DBMS_SQL.COLUMN_VALUE(cur_select,i,X_Old_Values(i));
For i in 1..X_Cnt_Of_Columns_Selected LOOP
If X_Sel_Grp_Diff_Array(i) = 'N' then
null;
For i in 1..X_Cnt_Of_Columns_Selected LOOP
DBMS_SQL.COLUMN_VALUE(cur_select,i,X_New_Values(i));
For i in 1..X_Cnt_Of_Columns_Selected LOOP
null;
For i in 1..X_Cnt_Of_Columns_Selected LOOP
If X_Sel_Grp_Diff_Array(i) = 'Y' then --Begin of If for 'Check_Ind' 999999999999999
IF L_DEBUG = 'Y' THEN
gms_error_pkg.gms_debug('Inside X_sel_grp_diff_array(i) = Y','C');
DO_EVENT_PROCESSING(X_Cnt_Of_Columns_Selected,
X_Sel_Grp_Diff_Array,
X_Rt_Jstfy_Flag_Array,
X_Old_Values,
X_Padding_Length_Array,
X_Run_Total,
X_Text_Array,
X_Project_Id,
/*X_Task_Id, Changed for bug 3523930 */
X_old_task_id,
X_invfmt_incl_task, /* Added for bug 3523930 */
X_Event_Amount,
X_Carrying_Out_Org_Id,
X_rev_or_bill_date,
X_calling_process,
X_Request_id,
C_Installment_Id,
X_Count,
X_Installment_total,
St_Err_Code,
St_Err_Buff) ;
For i in 1..X_Cnt_Of_Columns_Selected LOOP
X_Old_Values(i) := X_New_Values(i);
For i in 1..X_Cnt_Of_Columns_Selected LOOP
X_Run_Total(i) := 0;
INSERT_GMS_EVENT_INTERSECT(X_Project_Id,
X_Raw_Cost,
X_Expenditure_Item_Id,
X_Adl_Line_Num,
X_Request_id,
X_Amount_To_Insert,
x_calling_process,
X_Billable_Flag,
X_Bill_Hold_Flag,
X_Err_Nbr,
X_Err_Stg); -- Insert into GMS_EVENT_INTERSECT
X_event_rollback_amount := X_event_rollback_amount + X_Amount_To_Insert;
X_event_rollback_amount := X_Amount_To_Insert;
X_Event_Amount := X_Event_Amount + X_Amount_To_Insert ;
OR ((X_Amount_To_Insert = 0 ) AND (X_Tot_Burd_Amt_To_Insert = 0 )) THEN -- added for bug 5182669
---OR ((X_Raw_Cost + X_Tot_Burden_Amt_In_View) = (X_Amount_In_Intersect + X_Tot_Burd_Amt_In_Tmp)) then --bug 5122434--commented for bug 5182669
-- Update GMS_AWARD_DISTRIBUTIONS set Billed_Flag to 'Y' indicating item has been picked for Invoicing
IF L_DEBUG = 'Y' THEN
gms_error_pkg.gms_debug('Format specific Billing : Calling UPD_ADL_BILLING_FLAG for expenditure '||X_Expenditure_Item_Id,'C');
For i in 1..X_Cnt_Of_Columns_Selected LOOP
If X_Sel_Grp_Diff_Array(i) = 'N' then
-- X_Run_Total(i) := X_Run_Total(i) + nvl(to_number(X_New_Values(i)),0);
DO_EVENT_PROCESSING(X_Cnt_Of_Columns_Selected,
X_Sel_Grp_Diff_Array,
X_Rt_Jstfy_Flag_Array,
X_Old_Values,
X_Padding_Length_Array,
X_Run_Total,
X_Text_Array,
X_Project_Id,
/* X_Task_Id, Changed for bug 3523930*/
X_old_task_id,
X_invfmt_incl_task, /* Added for bug 3523930 */
X_Event_Amount,
X_Carrying_Out_Org_Id,
X_rev_or_bill_date,
X_calling_process,
X_Request_id,
C_Installment_Id,
X_Count,
X_Installment_total,
St_Err_Code,
St_Err_Buff) ;
DBMS_SQL.CLOSE_CURSOR(cur_select);
CURSOR rev_cur_select IS
Select /*+INDEX(ei PA_EXPENDITURE_ITEMS_U1)
INDEX(adl gms_award_distributions_n7) */ /* Added INDEX(adl gms_award_distributions_n7) for 6969435 */
p.project_id
, adl.task_id
, ei.expenditure_type
, nvl(ei.override_to_organization_id,e.incurred_by_organization_id) EXPENDITURE_ORG
, ei.quantity * (adl.distribution_value/100)*DECODE(adl.line_num_reversed,NULL,1,-1) --Added decode to get correct quantity
, unit.meaning
, decode(ei.system_linkage_function,'BTC',adl.raw_cost,adl.raw_cost)
, ei.expenditure_item_id
, ei.expenditure_item_date
, ei.bill_hold_flag
, adl.billable_flag
, ei.adjusted_expenditure_item_id
, adl.adl_line_num
, adl.cdl_line_num --Bug 2909746
, adl.parent_adl_line_num
, adl.adl_status
, adl.award_id
, adl.ind_compiled_set_id
, adl.burdenable_raw_cost
, ei.transaction_source
from
gms_award_distributions adl /* Moved this up in the order for 6969435*/
,pa_expenditure_items_all ei
,pa_expenditures e
,pa_expenditure_types et
,pa_lookups unit
,pa_projects_all p
,pa_project_types pt
,pa_tasks t3
,pa_tasks t5
where
adl.award_id = X_Award_Id
and ei.expenditure_item_id = adl.expenditure_item_id
and adl.fc_status = 'A'
and ((adl.line_num_reversed is null and adl.reversed_flag is null and ei.cost_distributed_flag='Y') or
((adl.line_num_reversed is not null or adl.reversed_flag is not null) and adl.cost_distributed_flag = 'Y')) --Bug 1852802
and nvl(adl.billable_flag,'N')='Y'
and ei.system_linkage_function <> 'BTC'
and (adl.revenue_distributed_flag in ('N','Z') or adl.revenue_distributed_flag is null) -- For bug 4386936 -- reverting this for bug 4594090 /* Modified this for 6969435 */
and ei.expenditure_item_date <= nvl(trunc(X_rev_or_bill_date),SYSDATE)
and trunc(ei.expenditure_item_date) <= trunc(C_End_Date_Active)
and adl.document_type = 'EXP' -- To pick up only actuals and not encumbrances
and adl.adl_status = 'A'
and ei.expenditure_type = et.expenditure_type
and e.expenditure_id = ei.expenditure_id
and et.unit_of_measure = unit.lookup_code
and ( (ei.system_linkage_function in( 'ST', 'OT') and X_Trx_Type = 'LABOR')
OR (ei.system_linkage_function not in( 'ST' , 'OT') and X_Trx_Type = 'NON_LABOR'))
and unit.lookup_type = 'UNIT'
and ei.task_id = t3.task_id
and t3.top_task_id = t5.task_id
and t5.ready_to_distribute_flag = 'Y'
and t3.project_id = p.project_id
and p.project_status_code <> 'CLOSED' -- Bug 3254097 : Modified 'CLOSED ' to 'CLOSED'
and pt.project_type = p.project_type
and pt.direct_flag = 'N'
and exists ( select 1
from gms_summary_project_fundings gspf
where gspf.installment_id = C_installment_id
and gspf.project_id = adl.project_id
and (gspf.task_id is NULL or
gspf.task_id = adl.task_id or
gspf.task_id = (select t.top_task_id
from pa_tasks t
where t.task_id = adl.task_id
)
)
)
order by DECODE( NVL(ei.net_zero_adjustment_flag,'N'),'N', NVL(ei.raw_cost,
gms_billing.get_total_adl_raw_cost(X_calling_process,ei.expenditure_item_id)),
'Y', DECODE(SIGN(NVL(ei.raw_cost,
gms_billing.get_total_adl_raw_cost(X_calling_process,ei.expenditure_item_id))),
1,-NVL(ei.raw_Cost,
gms_billing.get_total_adl_raw_cost(X_calling_process,ei.expenditure_item_id)),
NVL(ei.raw_cost,
gms_billing.get_total_adl_raw_cost(X_calling_process,ei.expenditure_item_id)))),
NVL(ei.raw_cost,gms_billing.get_total_adl_raw_cost(X_calling_process,ei.expenditure_item_id)),
NVL(ei.adjusted_expenditure_item_id,ei.expenditure_item_id),
adl.raw_cost, adl.cdl_line_num , -- Bug 3235390
p.project_id,ei.task_id,ei.expenditure_type,EXPENDITURE_ORG;
X_Rev_Amount_To_Insert NUMBER(22,5):= 0;
X_Rev_Tot_Burd_Amt_To_Insert NUMBER := 0;
OPEN rev_cur_select;
FETCH rev_cur_select into
X_Rev_Act_Project_Id
,X_Rev_Task_Id
,X_Rev_Expenditure_Type
,X_Rev_Expenditure_Org_Id
,X_Rev_Quantity
,X_Rev_Units
,X_Rev_Expenditure_Cost
,X_Rev_Expenditure_Item_Id
,X_Rev_Expenditure_Item_Date
,X_Rev_Bill_Hold_Flag
,X_Rev_Billable_Flag
,X_Rev_Adjusted_Exp_Item_Id
,X_Rev_Adl_Line_Num
,X_Rev_Cdl_Line_Num --Bug 2909746
,X_Rev_Parent_Adl_Line_Num
,X_Rev_Adl_Status
,X_Rev_Award_Id
,X_ind_compiled_Set_id
,X_burdenable_raw_cost
,X_transaction_source;
EXIT WHEN rev_cur_select%NOTFOUND;
X_Rev_Burden_Component_Data.DELETE;
select expenditure_item_id
into
X_Rev_Lock_Exp_Id
from
pa_expenditure_items_all
where
expenditure_item_id = X_Rev_Expenditure_Item_Id
FOR UPDATE NOWAIT;
select expenditure_item_id
into
X_Rev_Adl_Lock_Exp_Id
from
gms_award_distributions
where expenditure_item_id = X_Rev_Expenditure_Item_Id
and adl_line_num=X_Rev_Adl_Line_Num
and document_type='EXP'
and adl_status = 'A'
FOR UPDATE NOWAIT;
Select
nvl(revenue_distributed_flag,'N')
into
X_Rev_Orig_Item_Distr_Flag
from
gms_award_distributions
where expenditure_item_id = X_Rev_Adjusted_Exp_Item_Id
and award_id = X_Rev_award_id
and adl_status='A'
and document_type ='EXP'
and adl_line_num =
(select max(adl_line_num)
from gms_award_distributions
where expenditure_item_id = X_Rev_Adjusted_Exp_Item_Id
and award_id = X_Rev_award_id
and adl_status='A'
and document_type ='EXP');
X_Rev_Tot_Burd_Amt_To_Insert := 0;
GET_ACCRUE_BILL_OR_INSERT_AMT(X_Rev_Expenditure_Item_Id,
X_Rev_Adl_line_Num,
X_Calling_Process,
X_Rev_Expenditure_Cost,
X_Rev_Billable_Flag,
X_Rev_Bill_Hold_Flag,
X_Rev_Amount_To_Insert,
X_Err_Num,
X_Err_Stage);
GET_BURDEN_AMT_TO_INSERT(X_Rev_Expenditure_Item_Id,
X_Rev_Adl_Line_Num,
X_Calling_Process,
X_Rev_Burden_Component_Data(i).Burden_Cost_Code,
X_Rev_Billable_Flag,
X_Rev_Bill_Hold_Flag,
X_Rev_Burden_Component_Data(i).Burden_Cost,
X_Rev_Burd_Amt_To_Ins_By_Comp);
INSERT_GMS_BURDEN_COMPONENTS(X_Project_Id,
X_Rev_Expenditure_Item_Id,
X_Rev_Adl_Line_Num,
X_Request_Id,
X_Calling_Process,
X_Rev_Burden_Component_Data(i).Actual_Project_Id,
X_Rev_Burden_Component_Data(i).Actual_Task_Id,
X_Rev_Burden_Component_Data(i).Burden_Expenditure_Type,
X_Rev_Burden_Component_Data(i).Burden_Cost_Code,
X_Rev_Burden_Component_Data(i).Expenditure_Org_Id,
X_Rev_Burd_Amt_To_Ins_By_Comp,
X_Err_Num,
X_Err_Stage);
X_Rev_Tot_Burd_Amt_To_Insert := X_Rev_Tot_Burd_Amt_To_Insert + X_Rev_Burd_Amt_To_Ins_By_Comp;
C_Rev_Inst_Task_Run_Total := nvl(C_Rev_Inst_Task_Run_Total,0) + (nvl(X_Rev_Amount_To_Insert,0) + nvl(X_Rev_Tot_Burd_Amt_To_Insert,0));
(nvl(X_Rev_Amount_To_Insert,0) + nvl(X_Rev_Tot_Burd_Amt_To_Insert,0)) < 0 ) THEN
g_non_labor_neg_exp_processed := 'Y' ;
INSERT_GMS_EVENT_INTERSECT(X_Project_Id,
X_Rev_Expenditure_Cost,
X_Rev_Expenditure_Item_Id,
X_Rev_Adl_Line_Num,
X_Request_id,
X_Rev_Amount_To_Insert,
X_Calling_process,
X_Rev_Billable_Flag,
X_Rev_Bill_Hold_Flag,
X_Err_Nbr,
X_Err_Stg); -- Insert into GMS_EVENT_INTERSECT
X_event_rollback_amount := X_event_rollback_amount + X_Rev_Amount_To_Insert;
X_event_rollback_amount := X_Rev_Amount_To_Insert;
X_Rev_Event_Amount := X_Rev_Event_Amount + X_Rev_Amount_To_Insert;
OR ((X_Rev_Amount_To_Insert = 0) AND ( X_Rev_Tot_Burd_Amt_To_Insert = 0 ) ) THEN -- added for bug 5182669
--OR ((X_Rev_Expenditure_Cost + X_Rev_Tot_Burden_Amt_In_View) = (X_Rev_Amount_In_Intersect + X_Rev_Tot_Burd_Amt_In_Tmp)) then -- bug 5122434--Commented for bug 5182269
IF L_DEBUG = 'Y' THEN
gms_error_pkg.gms_debug('Revenue accrual : Calling UPD_ADL_BILLING_FLAG For expenditure_item_id '||X_Rev_Expenditure_Item_Id,'C');
CLOSE rev_cur_select;
PROCEDURE UPDATE_PROJECT_MANAGER(X_Project_Id IN NUMBER,
X_Award_Id IN NUMBER) IS
X_Person_Id NUMBER(15);
Select
person_id,
end_date_active
into
X_Person_Id,
X_end_date_active -- Bug fix for 863428
from
GMS_PERSONNEL
where award_id = X_Award_Id
and trunc(sysdate) between trunc(start_date_active) and trunc(end_date_active)
and award_role = 'AM';
Select
person_id
into
X_Person_Id
from
GMS_PERSONNEL
where award_id = X_Award_Id
and award_role = 'AM'
and end_date_active IS NULL;
Select
person_id
into
X_Person_Id
from
GMS_PERSONNEL
where award_id = X_Award_Id
and award_role = 'AM'
and end_date_active = (select max(end_date_active)
from gms_personnel
where
award_id = X_Award_Id
and award_role = 'AM');
update pa_project_parties
set resource_source_id=X_Person_Id,
end_date_active = X_end_date_active
where project_id = X_Project_Id
and project_role_id =(select project_role_id
from pa_project_role_types
where project_role_type = 'PROJECT MANAGER');
End UPDATE_PROJECT_MANAGER;
sql_select VARCHAR2(4000);
Select
Installment_Id,
Start_Date_Active,
End_Date_Active
from
GMS_INSTALLMENTS
where
Award_Id = X_Award_Id
/* and active_flag = 'Y' bug 6878405 */
and nvl(billable_flag,'N') = 'Y'
order by End_Date_Active;
select
a.Award_number,
a.Award_short_name,
a.award_id
from
GMS_AWARDS a
where
a.Award_Project_Id = X_Project_Id;
X_Lbr_Cnt_Of_Columns_Selected NUMBER(3) := 0;
X_LABOR_CONCAT_SELECT VARCHAR2(2000);
X_Nlbr_Cnt_Of_Columns_Selected NUMBER(3) := 0;
X_NON_LABOR_CONCAT_SELECT VARCHAR2(2000);
X_Fixed_Select VARCHAR2(2000) := NULL;
|| award, users should not be able to update any billing data on the award
|| form. Bug 1652198.....
|| Procedure: lock_award_records created for this.
------------------------------------------------------------------------- */
-- ## Code change starts here for Bug 1652198....
/**FOr Bug 4506225 :MOved the code after the call to GMS_TAX
lock_award_records(X_Project_id,
St_Err_Code,
St_Err_Buff);
/* Selecting the Project Type. GO THROUGH BILL_EXTENSION only if
Project_Type = 'AWARD_PROJECT'
*/
Begin
Select
project_type,
carrying_out_organization_id --This is being selected again here because Revenue
into --doesn't have access to GET_FORMAT_SELECT
X_Project_Type,
X_Rev_Carrying_Out_Org_Id
from
PA_PROJECTS_ALL
where
project_id = X_Project_Id;
select
cc.class_category
into
X_Class_Category
from
pa_class_categories cc
where
sysdate between cc.start_date_active and
nvl(cc.end_date_active,SYSDATE + 1) and
cc.autoaccounting_flag = 'Y';
gms_error_pkg.gms_debug('After Class Category select','C');
select
a.Award_Id,
a.Revenue_Distribution_Rule,
a.Billing_Distribution_Rule,
a.Status,
-- ag.Revenue_Limit_Flag -- Bug 1841288
nvl(a.hard_limit_flag,'N') -- Bug 1841288 : Taken hard_limit_flag from gms_awards instead of pa_agreements_all
,nvl(a.invoice_limit_flag,'N') -- Bug 6642901
into
X_Award_Id, -- Adding this because Revenue Process doesnot have access to GET_FORMAT_SELECT proc.
X_Award_Rev_Distribution_Rule,
X_Award_Bill_Distribution_Rule,
X_Award_Status,
X_Revenue_Limit_Flag,
X_Invoice_limit_Flag -- Bug 6642901
from
GMS_AWARDS a
--PA_AGREEMENTS_ALL ag -- Bug 1841288 : Removed join from PA_AGREEMENTS_ALL Table
where
a.Award_Project_Id = X_Project_Id;
will update project manager through award form only to fix bug 1907565 gnema*/
-- Bug 3235390 : Intializing the variables used .
x_temp_negative_evt_num := -1000;
GET_FORMAT_SELECT(X_Project_Id,
X_Award_Id,
X_Carrying_Out_Org_Id,
X_Labor_Sel_Grp_Diff_Ind,
X_Non_Labor_Sel_Grp_Diff_Ind,
X_Lbr_Cnt_Of_Columns_Selected,
X_Nlbr_Cnt_Of_Columns_Selected,
X_Lbr_Rt_Jstfy_Flag,
X_Nlbr_Rt_Jstfy_Flag,
X_Lbr_Padding_Length,
X_Nlbr_Padding_Length,
X_Lbr_Text_Array,
X_Nlbr_Text_Array,
X_LABOR_CONCAT_SELECT,
X_LABOR_CONCAT_FROM,
X_LABOR_CONCAT_WHERE,
X_LABOR_CONCAT_ORDERBY,
X_LABOR_ORDERBY_IS_NULL,
X_NON_LABOR_CONCAT_SELECT,
X_NON_LABOR_CONCAT_FROM,
X_NON_LABOR_CONCAT_WHERE,
X_NON_LABOR_CONCAT_ORDERBY,
X_NON_LABOR_ORDERBY_IS_NULL,
X_LABOR_tsk_lvl_fmt, /* added for bug 3523930 */
X_NON_LABOR_tsk_lvl_fmt, /* added for bug 3523930 */
X_Err_Num,
X_Err_Stage);
gms_error_pkg.gms_debug('After Format Selection','C');
sql_select := 'select /*+INDEX(ei PA_EXPENDITURE_ITEMS_U1)*/ ';
X_Fixed_Select := ' adl.raw_cost, ei.expenditure_item_id, ei.expenditure_item_date, ei.task_id,
ei.bill_hold_flag, adl.billable_flag, ei.adjusted_expenditure_item_id, adl.adl_line_num,adl.parent_adl_line_num,
adl.adl_status, adl.award_id, adl.project_id, adl.cdl_line_num,ei.expenditure_type,
nvl(ei.override_to_organization_id,e.incurred_by_organization_id),adl.ind_compiled_Set_id,adl.burdenable_raw_cost,
ei.transaction_source '; --bug 2909746
||'(select 1 '
||'from gms_summary_project_fundings gspf '
||'where gspf.installment_id = :C_Installment_Id '
||'and ( '
||' (gspf.task_id = adl.task_id) '
||'OR (gspf.task_id is NULL) '
||'OR (gspf.task_id = (select t1.top_task_id from pa_tasks t1 where t1.task_id = adl.task_id)) '
||' ) '
||'and gspf.project_id = adl.project_id '
||') ';
select count(*)
into x_tot_inst_count
from gms_installments
where award_id = X_Award_id
/* and active_flag = 'Y' bug 6878405 */
and nvl(billable_flag,'N') = 'Y';
DELETE gms_events_temp_format;
sql_select := sql_select||X_LABOR_CONCAT_SELECT;
sql_select := sql_select||X_Fixed_Select;
select instr(UPPER(X_LABOR_CONCAT_SELECT),'SUM',1,1)
into X_position
from dual;
For i in 1..X_Lbr_Cnt_Of_Columns_Selected LOOP
null;
For i in 1..X_Nlbr_Cnt_Of_Columns_Selected LOOP
null;
sql_select := sql_select||' '||sql_from||' '||sql_where||' '||sql_orderby ;
sql_select := sql_select||' '||sql_from||' '||sql_where||' '||sql_orderby ||' order by ' || l_sql_orderby ;
X_Lbr_Cnt_Of_Columns_Selected,
X_Lbr_Rt_Jstfy_Flag,
X_Lbr_Padding_Length,
X_Lbr_Text_Array,
sql_select,
X_Carrying_Out_Org_Id,
X_calling_process,
X_LABOR_tsk_lvl_fmt, /* added for bug 3523930 */
C_Installment_Id,
C_Start_Date_Active,
C_End_Date_Active,
X_Err_Num,
X_Err_Stage,
'LABOR', /* added for bug 5026657 */
f_labor_exp_to_process, /* added for bug 5026657 */
f_non_labor_neg_exp_processed); /* added for bug 5026657 */
sql_select := 'select /*+INDEX(ei PA_EXPENDITURE_ITEMS_U1)*/ ';
sql_select := sql_select||X_NON_LABOR_CONCAT_SELECT;
sql_select := sql_select||X_Fixed_Select;
select instr(UPPER(X_NON_LABOR_CONCAT_SELECT),'SUM',1,1)
into X_position
from dual;
sql_select := sql_select||' '||sql_from||' '||sql_where||' '||sql_orderby ;
sql_select := sql_select||' '||sql_from||' '||sql_where||' '||sql_orderby || ' Order By '||l_sql_orderby ;
X_Nlbr_Cnt_Of_Columns_Selected,
X_Nlbr_Rt_Jstfy_Flag,
X_Nlbr_Padding_Length,
X_Nlbr_Text_Array,
sql_select,
X_Carrying_Out_Org_Id,
X_calling_process,
X_NON_LABOR_tsk_lvl_fmt, /* added for bug 3523930 */
C_Installment_Id,
C_Start_Date_Active,
C_End_Date_Active,
X_Err_Num,
X_Err_Stage,
'NON LABOR', /* added for bug 5026657 */
f_labor_exp_to_process, /* added for bug 5026657 */
f_non_labor_neg_exp_processed); /* added for bug 5026657 */
sql_select := 'select /*+INDEX(ei PA_EXPENDITURE_ITEMS_U1)*/ '; -- Bug 2380344 : Hardcoded Index for Performance Fix
X_Installment_total.delete ; -- initalize installment_amount table for new installment
X_Installment_tab.delete; -- initalize install_tab for new installment
DELETE gms_events_temp_format;
X_Rev_Installment_total.delete ; -- initalize installment_amount table for new installment
X_Rev_Installment_tab.delete; -- initalize install_tab for new installment
DELETE_NULL_EVENTS (X_project_id,
X_request_id,
X_Calling_Process,
St_Err_Code,
St_Err_Buff);
Delete
from gms_concurrency_control
where process_name = 'GMS_BLNG'
and process_key = X_project_id;
Delete
from gms_concurrency_control
where process_name = 'GMS_BLNG'
and process_key = X_project_id;
Delete
from gms_concurrency_control
where process_name = 'GMS_BLNG'
and process_key = X_project_id;
Delete
from gms_concurrency_control
where process_name = 'GMS_BLNG'
and process_key = X_project_id;