[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Cursor c_gspf_update is
select gea.installment_id,
gea.actual_project_id,
gea.actual_task_id,
pt.top_task_id,
sum(gea.bill_amount) billed_amount -- null handling not reqd.
from gms_event_attribute gea,
pa_tasks pt
where gea.project_id = P_AWARD_PROJECT_ID
and gea.event_num in ( -1,-2)
and gea.request_id = P_REQUEST_ID
and gea.event_calling_process = 'Invoice'
and pt.task_id = gea.actual_task_id
group by gea.installment_id,
gea.actual_project_id,
gea.actual_task_id,
pt.top_task_id;
for x in c_gspf_update loop
Update gms_summary_project_fundings gspf
set gspf.total_billed_amount = nvl(gspf.total_billed_amount,0) -
x.billed_amount
where gspf.installment_id = x.installment_id
and gspf.project_id = x.actual_project_id
and (gspf.task_id is NULL or
gspf.task_id = x.actual_task_id or
gspf.task_id = x.top_task_id);
Update gms_award_distributions adl
set adl.billed_flag = decode(p_calling_process,'REVENUE',
adl.billed_flag,'N'),
adl.revenue_distributed_flag = decode(p_calling_process,'INVOICE',
adl.revenue_distributed_flag,'N')
where (expenditure_item_id,adl_line_num) in
(select expenditure_item_id,adl_line_num
from gms_event_intersect
where award_project_id = p_award_project_id
and request_id = p_request_id
and event_type = p_calling_process
and event_num = -1 /*Added for bug 5060427*/
UNION ALL
select expenditure_item_id,adl_line_num
-- from gms_event_intersect /* Commented for bug 5060427 */
from gms_burden_components /*Added for bug 5060427*/
where award_project_id = p_award_project_id
and request_id = p_request_id
and event_type = p_calling_process
and event_num = -2 /*Added for bug 5060427*/ )
and document_type = 'EXP'
and adl_status = 'A';
Delete from gms_event_intersect
where award_project_id = p_award_project_id
and event_num = -1
and request_id = p_request_id
and event_type = p_calling_process;
Delete from gms_burden_components
where award_project_id = p_award_project_id
and event_num = -2
and request_id = p_request_id
and event_type = p_calling_process;
Delete from gms_event_attribute
where project_id = p_award_project_id
and event_num in ( -1,-2)
and request_id = p_request_id
and event_calling_process = INITCAP(p_calling_process);
PROCEDURE INSERT_BILL_CANCEL(X_Award_Project_Id IN NUMBER,
X_Event_Num IN NUMBER,
X_Expenditure_item_id IN NUMBER DEFAULT null,
X_Adl_Line_No IN NUMBER DEFAULT null,
X_Bill_Amount IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Burden_Exp_Type IN VARCHAR2 DEFAULT null,
X_Burden_Cost_Code IN VARCHAR2 DEFAULT null,
X_Creation_Date IN DATE,
X_Actual_Project_Id IN NUMBER,
X_Actual_Task_Id IN NUMBER,
X_Expenditure_Org_Id IN NUMBER,
X_Deletion_Date IN DATE,
X_Resource_List_Member_Id IN NUMBER DEFAULT null,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
Begin
/* Inserting into gms_billing_cancellations table */
INSERT INTO GMS_BILLING_CANCELLATIONS (AWARD_PROJECT_ID,
EVENT_NUM,
EXPENDITURE_ITEM_ID,
ADL_LINE_NUM,
BILL_AMOUNT,
CALLING_PROCESS,
BURDEN_EXP_TYPE,
BURDEN_COST_CODE,
CREATION_DATE,
ACTUAL_PROJECT_ID,
ACTUAL_TASK_ID,
EXPENDITURE_ORG_ID,
DELETION_DATE,
RESOURCE_LIST_MEMBER_ID)
VALUES(X_Award_Project_Id,
X_Event_Num,
X_Expenditure_item_id,
X_Adl_Line_No,
X_Bill_Amount,
X_Calling_Process,
X_Burden_Exp_Type,
X_Burden_Cost_Code,
X_Creation_Date,
X_Actual_Project_Id,
X_Actual_Task_Id,
X_Expenditure_Org_Id,
X_Deletion_Date,
X_Resource_List_Member_Id
);
End INSERT_BILL_CANCEL;
Update gms_summary_project_fundings
set total_billed_amount = total_billed_amount + X_Amount
where project_id = X_Actual_Project_Id
and (task_id is null
or task_id = X_Actual_Task_id
or task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_id) -- Bug 2369179,Added
)
and installment_id = X_Installment_id;
Update gms_summary_project_fundings
set total_billed_amount = total_billed_amount - X_Amount
where project_id = X_Actual_Project_Id
and (task_id is null
or task_id = X_Actual_Task_id
or task_id = (select top_task_id from pa_tasks where task_id = X_Actual_Task_id) -- Bug 2369179,Added
)
and installment_id = X_Installment_id;
select pdii.event_num event_num,
-1*pdii.amount amount
from pa_draft_invoice_items pdii,
pa_draft_invoices pdi
where pdi.project_id = X_Award_Project_Id
and pdi.request_id = g_request_id
and pdii.project_id = pdi.project_id
and pdii.draft_invoice_num = pdi.draft_invoice_num
and (nvl(pdi.write_off_flag,'N') = 'Y' OR nvl(pdi.concession_flag,'N') = 'Y');
select gea.installment_id,
gea.actual_Project_Id,
gea.Actual_Task_id,
gea.bill_amount,
gea.rowid
from gms_event_attribute gea
where gea.project_id = p_award_project_id
and gea.event_num = p_event_num;
select sum(bill_amount),count(*)
into F_Total_Bill_Amt,F_Event_Count
from gms_event_attribute
where project_id = X_award_project_id
and event_num = Invoice_line.event_num;
Update gms_event_attribute
set bill_amount = bill_amount - F_prorate_amt,
write_off_amount = nvl(write_off_amount,0) + F_prorate_amt
where rowid = event_attribute.rowid;
/* Update gms_summary_project_fundings */
UPD_GMS_SUMMARY_PRJ_FUNDS(event_attribute.Actual_project_id,
event_attribute.Actual_Task_id,
event_attribute.Installment_Id,
F_prorate_amt,
'WRITE_OFF_GEN',
X_Err_Code,
X_Err_Buff);
Select project_id,
event_num,
-1*amount
from pa_draft_invoice_items
where draft_invoice_num = X_Draft_Invoice_Num
and project_id = X_Award_Project_Id;
Select project_id,
event_num,
installment_id,
write_off_amount,
actual_project_id,
actual_task_id,
rowid
from gms_event_attribute
where project_id = F_invoice_Project_id
and event_num = F_invoice_Event_Num;
select sum(nvl(write_off_amount,0)),count(*)
into X_Total_Write_Off_Amt,F_Event_Count
from gms_event_attribute
where project_id = F_invoice_project_id
and event_num = F_Invoice_event_num;
/* Start Update gms_event_attribute */
BEGIN
Update gms_event_attribute
set write_off_amount = write_off_amount - Upd_amount,
bill_amount = bill_amount + Upd_amount
where rowid = F_rowid;
/* End - Update gms_event_attribute */
/* Update gms_summary_project_fundings */
UPD_GMS_SUMMARY_PRJ_FUNDS(F_actual_project_id,
F_actual_task_id,
F_installment_id,
Upd_amount,
'WRITE_OFF_DEL',
X_Err_Code,
X_Err_Buff);
Procedure DELETE_GMS_EVENT_ATTRIBUTE(X_Award_Project_Id IN NUMBER,
X_Event_Num IN NUMBER,
X_calling_process IN VARCHAR2,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
Begin
DELETE
FROM gms_event_attribute
WHERE project_id=X_Award_Project_id
AND event_num=X_Event_Num
AND event_calling_process= x_calling_process ; -- Bug 2979125 : added filter calling_process
End DELETE_GMS_EVENT_ATTRIBUTE;
Select
decode(X_Calling_Process,'Invoice',nvl(spf.total_billed_amount,0),'Revenue',nvl(spf.total_revenue_amount,0))
into
St_Amount
from
GMS_SUMMARY_PROJECT_FUNDINGS spf
where
spf.installment_id = X_Installment_Id
and spf.project_id = X_Act_Project_Id
and (
(spf.task_id IS NULL)
OR (spf.task_id = X_Act_Task_Id)
OR (spf.task_id = (select top_task_id from pa_tasks where task_id = X_Act_Task_Id))
);
X_Amt_To_Update NUMBER(22,5);
select installment_id,
actual_project_id,
actual_task_id,
decode(X_calling_Process,'Invoice',bill_amount,'Revenue',revenue_amount) Amount
from gms_event_attribute
where project_id = X_Award_Project_id
and event_num = X_Event_Num;
/* Amount To Update */
X_Amt_To_Update := (X_Curr_Amount - F_amount);
/* Update GMS_SUMMARY_PROJECT_FUNDINGS */
Begin
If X_Calling_Process = 'Invoice' then
update GMS_SUMMARY_PROJECT_FUNDINGS spf
set
spf.Total_Billed_Amount = X_Amt_To_Update
,spf.last_update_date = sysdate
,spf.last_update_login = fnd_global.login_id
,spf.last_updated_by = fnd_global.user_id
where
spf.installment_id = F_Installment_id
and spf.project_id = F_actual_project_id
and (
(spf.task_id IS NULL)
OR (spf.task_id = F_actual_task_id)
OR (spf.task_id = (select t.top_task_id from pa_tasks t where t.task_id = F_actual_task_id))
);
update GMS_SUMMARY_PROJECT_FUNDINGS spf
set
spf.Total_Revenue_Amount = X_Amt_To_Update
,spf.last_update_date = sysdate
,spf.last_update_login = fnd_global.login_id
,spf.last_updated_by = fnd_global.user_id
where
spf.installment_id = F_Installment_id
and spf.project_id = F_actual_project_id
and (
(spf.task_id IS NULL)
OR (spf.task_id = F_actual_task_id)
OR (spf.task_id = (select t.top_task_id from pa_tasks t where t.task_id = F_actual_task_id))
);
Procedure DELETE_GMS_BURDEN_INTRSCT(X_Expenditure_Item_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Event_Num IN NUMBER,
X_Adl_Line_No IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Burden_Cost_Code IN VARCHAR2, -- Bug 1193080
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
Begin
X_Err_Code := 0;
DELETE /*+INDEX(GMS_BURDEN_COMPONENTS GMS_BURDEN_COMPONENTS_U1) */
from
GMS_BURDEN_COMPONENTS
where
expenditure_item_id = X_Expenditure_Item_Id
and award_project_Id = X_Award_Project_Id
and event_num = X_Event_Num
and adl_line_num = X_Adl_Line_No
and burden_cost_code = X_Burden_Cost_Code -- Bug 1193080
and event_type = 'INVOICE';
DELETE /*+INDEX(GMS_BURDEN_COMPONENTS GMS_BURDEN_COMPONENTS_U1) */
from
GMS_BURDEN_COMPONENTS
where
expenditure_item_id = X_Expenditure_Item_Id
and award_project_Id = X_Award_Project_Id
and event_num = X_Event_Num
and adl_line_num = X_Adl_Line_No
and burden_cost_code = X_Burden_Cost_Code -- Bug 1193080
and event_type = 'REVENUE';
End DELETE_GMS_BURDEN_INTRSCT;
Procedure DELETE_GMS_INTERSECT(X_Expenditure_Item_Id IN NUMBER,
X_Award_Project_Id IN NUMBER,
X_Event_Num IN NUMBER,
X_Adl_Line_No IN NUMBER,
X_Calling_Process IN VARCHAR2,
X_Err_Code IN OUT NOCOPY NUMBER,
X_Err_Buff IN OUT NOCOPY VARCHAR2) IS
Begin
X_Err_Code := 0;
DELETE /*+INDEX(GMS_EVENT_INTERSECT GMS_EVENT_INTERSECT_U1) */
from
GMS_EVENT_INTERSECT
where
expenditure_item_id = X_Expenditure_Item_Id
and award_project_Id = X_Award_Project_Id
and event_num = X_Event_Num
and adl_line_num = X_Adl_Line_No
and event_type = 'INVOICE';
DELETE /*+INDEX(GMS_EVENT_INTERSECT GMS_EVENT_INTERSECT_U1) */
from
GMS_EVENT_INTERSECT
where
expenditure_item_id = X_Expenditure_Item_Id
and award_project_Id = X_Award_Project_Id
and event_num = X_Event_Num
and adl_line_num = X_Adl_Line_No
and event_type = 'REVENUE';
End DELETE_GMS_INTERSECT;
UPDATE GMS_AWARD_DISTRIBUTIONS
set
billed_flag = 'N'
,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_No
and award_id=
(select award_id
from gms_awards
where award_project_id=X_Award_Project_Id
)
and document_type='EXP'
and adl_status = 'A';
UPDATE PA_EXPENDITURE_ITEMS_ALL
set
revenue_distributed_flag = 'N'
,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;
FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No Expenditure Line Updated');
pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.UPD_GET_PA_EXP_INFO'
,x_message => X_Err_Buff
,x_error_message => X_pa_Err_Msg
,x_status => X_pa_Status);
UPDATE GMS_AWARD_DISTRIBUTIONS
set
revenue_distributed_flag = 'N'
,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_No
and award_id=
(select award_id
from gms_awards
where award_project_id=X_Award_Project_Id
)
and document_type='EXP'
and adl_status = 'A';
Select distinct
nvl(a.burden_cost_code,'NULL'),
a.event_type,
b.event_type_classification
into
X_Burden_Cost_Code,
X_Event_Type,
X_Event_Type_Class
from
gms_events_v a,
pa_event_types b
where
a.project_id = X_Award_Project_Id
and a.event_num = X_Event_Num
and a.event_type = b.event_type;
Select distinct
Actual_Project_Id,
Actual_Task_Id,
Expenditure_Org_Id,
Revenue_Accumulated
--,Creation_Date
into
X_Actual_Project_Id,
X_Actual_Task_Id,
X_Expenditure_Org_Id,
X_Revenue_Accumulated
--,X_Creation_Date
from
gms_events_v
where project_id = X_Award_Project_Id
and event_num = X_Event_Num;
Select trunc(expenditure_item_date)
into X_creation_date
from pa_expenditure_items_all
where Expenditure_Item_Id = X_Expenditure_Item_Id;
Select
NVL(write_off_flag,'N'),
NVL(concession_flag,'N'),
draft_invoice_num_credited
into
X_Write_Off_Flag,
X_Concession_Invoice_Flag,
X_Draft_Invoice_Num_Credited
from
PA_DRAFT_INVOICES
where project_id = X_Award_Project_Id
and draft_invoice_num = X_Draft_Invoice_Num;
Select
project_id,
line_num,
event_num,
amount
from
pa_draft_invoice_items
where
draft_invoice_num = St_Draft_Invoice_Num and
project_id = St_Award_Project_Id;
Select
expenditure_item_id,
adl_line_num,
request_id -- for bug 4594090
from
gms_event_intersect
where
award_project_id = F_Award_Project_Id and
event_num = F_Event_Num and
event_type = 'INVOICE';
Select
Expenditure_Item_Id,
adl_line_num,
Amount,
Actual_Project_Id,
Actual_Task_Id,
Burden_Exp_Type,
Burden_Cost_Code,
Expenditure_Org_Id,
request_id -- Added for bug 4594090
from
GMS_BURDEN_COMPONENTS
where
award_project_id = F_Award_Project_Id and
event_num = F_Event_Num and
event_type = 'INVOICE';
If X_Adj_Action in ('CANCEL','DELETE') then
/* Find Out NOCOPY if the Invoice that's being processed is a Regular Invoice
or a Write Off on some other Invoice */
GET_INVOICE_CREDIT_INFO(St_Draft_Invoice_Num,
St_Award_Project_Id,
X_Write_Off_Flag,
X_Concession_Flag,
X_Draft_Invoice_Num_Credited,
X_Err_Code,
X_Err_Buff);
If (X_Adj_Action = 'CANCEL' OR X_Adj_Action = 'DELETE') then
If ((X_Write_Off_Flag = 'Y') OR (X_Concession_Flag = 'Y')) then
Begin
/* --------------------------------------------------------------- */
-- 11.5 Changes, re writing of Write_off deletion/cancellation Processing
/* --------------------------------------------------------------- */
WRITE_OFF_DELETION(St_Award_Project_Id,
St_Draft_Invoice_Num,
X_Err_Code,
X_Err_Buff);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '5 - CANINV'
,x_message => 'Inside WRITE-OFF Flag = Y '
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6 - CANINV'
,x_message =>'In WRITE_OFF_FLAG = N'
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.1 - CANINV'
,x_message =>'Before GET_EVENT_INFO'
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2 - CANINV'
,x_message =>'After GET_EVENT_INFO'||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.1 - CANINV'
,x_message =>'Inside Burden_Evt_Flag = N'
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.2 - CANINV'
,x_message =>'After UPD_GET_PA_EXP_INFO '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
DELETE_GMS_INTERSECT(F_Expenditure_Item_Id,
F_Award_Project_Id,
F_Event_Num,
F_Adl_Line_No,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.3 - CANINV'
,x_message =>'After DELETE_GMS_INTERSECT '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/*pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.4 - CANINV'
,x_message =>'After MANIP_BILLREV_AMOUNT '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* Delete entries from GMS_EVENT_ATTRIBUTE */
/* Bug 2979125: added parameter calling_process */
DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
F_Event_Num,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.2.5 - CANINV'
,x_message =>'After DELETE_GMS_EVENT_ATTRIBUTE '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.1 - CANINV'
,x_message =>'Inside X_Burden_Evt_Flag = Y '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.2 - CANINV'
,x_message =>'Before DELETE_GMS_BURDEN_INTRSCT'
,x_error_message => X_Err_Msg
,x_status => X_Status); */
DELETE_GMS_BURDEN_INTRSCT(F_Burd_Expenditure_Item_Id ,
F_Award_Project_Id,
F_Event_Num,
F_Burd_Adl_Line_No,
X_Calling_Process,
F_Burd_Cost_Code, -- Bug 1193080
X_Err_Code,
X_Err_Buff);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.3 - CANINV'
,x_message =>'After DELETE_GMS_BURDEN_INTRSCT'||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.4 - CANINV'
,x_message =>'After MANIP_BILLREV_AMOUNT '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* Delete entries from GMS_EVENT_ATTRIBUTE */
/* Bug 2979125 : added parameter calling_process */
DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
F_Event_Num,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.3.5 - CANINV'
,x_message =>'After DELETE_GMS_EVENT_ATTRIBUTE '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6.4.1 - CANINV'
,x_message =>'After MANIP_BILLREV_AMOUNT '||'-'||St_Err_Code
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '2 - WRITEOFFF'
,x_message => 'Getting INTO GRANTS WRITE OFF Process '
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '2.25 - WRITEOFFF'
,x_message => to_char(St_Award_Project_Id)||'- '||to_char(St_Draft_Invoice_Num)
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '2.50 - AFTER WRITE OFF PROCESSING'
,x_message => 'Inside WRITE-OFF Flag = Y '
,x_error_message => X_Err_Msg
,x_status => X_Status); */
Select
ri.project_id,
ri.line_num,
rdl.event_num,
ri.amount
from
pa_draft_revenue_items ri,
pa_cust_event_rdl_all rdl
where
ri.draft_revenue_num = St_Draft_Revenue_Num and
ri.project_id = St_Award_Project_Id and
rdl.draft_revenue_num = ri.draft_revenue_num and
rdl.project_id = ri.project_id and
rdl.draft_revenue_item_line_num = ri.line_num;
Select
expenditure_item_id,
adl_line_num,
amount,
revenue_accumulated,
request_id -- 4594090
from
gms_event_intersect
where
award_project_id = F_Award_Project_Id and
event_num = F_Event_Num and
event_type = 'REVENUE';
Select
Expenditure_Item_Id,
Adl_Line_Num,
Amount,
Actual_Project_Id,
Actual_Task_Id,
Burden_Exp_Type,
Burden_Cost_Code,
Expenditure_Org_Id,
Creation_Date,
Revenue_Accumulated,
request_id -- 4594090
from
GMS_BURDEN_COMPONENTS
where
award_project_id = F_Award_Project_Id and
event_num = F_Event_Num and
event_type = 'REVENUE';
DELETE_GMS_INTERSECT(F_Expenditure_Item_Id,
F_Award_Project_Id,
F_Event_Num,
F_Adl_Line_No,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
INSERT_BILL_CANCEL(F_Award_Project_id,
F_Event_Num,
F_Expenditure_Item_Id,
F_Adl_Line_No,
-1 * F_Raw_Revenue_Amount,
X_Calling_Process,
NULL, -- burden_exp_type
NULL, -- burden_cost_code
X_Creation_Date,
X_Actual_Project_Id,
X_Actual_Task_Id,
X_Expenditure_Org_Id,
sysdate, -- deletion_date
NULL, -- rlmi
X_Err_Code,
X_Err_Buff);
/* Delete entries from GMS_EVENT_ATTRIBUTE */
/* Bug 2979125 : added parameter calling_process */
DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
F_Event_Num,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
DELETE_GMS_BURDEN_INTRSCT(F_Rev_Burd_Expend_Item_Id ,
F_Award_Project_Id,
F_Event_Num,
F_Rev_Adl_Line_No,
X_Calling_Process,
F_Rev_Burden_Cost_Code, -- Bug 1193080
X_Err_Code,
X_Err_Buff);
INSERT_BILL_CANCEL(F_Award_Project_id,
F_Event_Num,
F_Rev_Burd_Expend_Item_Id,
F_Rev_Adl_Line_No,
-1 * F_Rev_Burd_Intrsct_Amt,
X_Calling_Process,
F_Rev_Burden_Exp_Type,
F_Rev_Burden_Cost_Code,
F_Rev_Creation_Date,
F_Rev_Actual_Project_Id,
F_Rev_Actual_Task_Id,
F_Rev_Burd_Exp_Org_Id,
sysdate, -- deletion_date
NULL, -- rlmi
X_Err_Code,
X_Err_Buff);
/* Delete entries from GMS_EVENT_ATTRIBUTE */
/* Bug 2979125 : added parameter calling_process */
DELETE_GMS_EVENT_ATTRIBUTE(F_Award_Project_id,
F_Event_Num,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
INSERT_BILL_CANCEL(F_Award_Project_id,
F_Event_Num,
NULL, -- expenditure_id
NULL, -- adl_line_num
-1 * F_amount, -- negative entry
X_Calling_Process,
NULL, -- burden_exp_type
NULL, -- burden_cost_code
X_Creation_Date,
X_Actual_Project_Id,
X_Actual_Task_Id,
X_Expenditure_Org_Id,
sysdate, -- deletion_date
NULL, -- rlmi
X_Err_Code,
X_Err_Buff);
SELECT p.project_id, p.segment1, p.project_level_funding_flag
FROM pa_projects p, pa_draft_revenues r
WHERE p.segment1 BETWEEN X_Start_Award_Project_Number
AND X_End_Award_Project_Number
AND r.project_id = p.project_id
AND r.released_date||'' is null
AND r.generation_error_flag||'' = 'Y'
GROUP BY p.project_id, p.segment1, p.project_level_funding_flag;
/* Cursor to Select Revenues that could be potentially deleted for a Project */
CURSOR GET_TO_BE_DEL_REVENUES(X_Project_Id NUMBER) IS
SELECT
draft_revenue_num
, agreement_id
FROM
PA_BILLING_REV_DELETION_V --View Made available from R11
WHERE PROJECT_ID = X_Project_Id
FOR UPDATE NOWAIT;
/* Cursor to Select Invoices that could be deleted as a result of unreleased revenues being
deleted. This should not be applicable in the case of GMS where separete events are
created for Revenue and Invoices hence won't be used */
/*---------------------------------------------------------------------+
CURSOR GET_REV_REL_DEL_INVOICES IS |
SELECT |
di.draft_invoice_num |
FROM pa_draft_invoices di |
WHERE di.project_id = :project_id |
AND di.agreement_id+0 = :agreement_id |
AND di.released_date||'' is null |
AND (EXISTS |
(SELECT NULL |
FROM pa_cust_rev_dist_lines l |
WHERE l.project_id = :project_id |
AND l.draft_revenue_num = :draft_revenue_num |
AND l.draft_invoice_num = di.draft_invoice_num) |
OR |
EXISTS |
(SELECT NULL |
FROM pa_cust_event_rev_dist_lines l |
WHERE l.project_id = :project_id |
AND l.draft_revenue_num = :draft_revenue_num |
AND l.draft_invoice_num = di.draft_invoice_num) |
); |
/* Cursor to Select Draft Invoices which could be deleted for a particular
Project */
CURSOR GET_DRAFT_INVOICES is
Select
draft_invoice_num
from
PA_BILLING_INV_DELETION_V --View made available in R11
WHERE PROJECT_ID = X_Award_Project_Id
FOR UPDATE NOWAIT;
FOR UPDATE NOWAIT;
select award_number into
X_Award_Number from gms_awards
where award_project_id = X_Award_Project_Id;
Select
draft_invoice_num
into
X_Locked_Row
from
pa_draft_invoices
where
draft_invoice_num = X_Draft_Invoice_Num
and project_id = X_Award_Project_Id
FOR UPDATE NOWAIT;
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '3 - CANINV'
,x_message => 'GMS_INV_FOR_CANCEL_LOCKED'
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '4 - CANINV'
,x_message => SQLCODE||' - '||SQLERRM
,x_error_message => X_Err_Msg
,x_status => X_Status); */
Elsif X_Adj_Action = 'DELETE' then
Begin
open GET_DRAFT_INVOICES;
'DELETE',
NULL,--X_Adj_Amount,
X_Calling_Process,
X_Err_Code,
X_Err_Buff);
Select
draft_invoice_num
into
X_Locked_Row
from
pa_draft_invoices
where
draft_invoice_num = X_Draft_Invoice_Num
and project_id = X_Award_Project_Id
FOR UPDATE NOWAIT;
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '6 - WRIINV'
,x_message => '6 - After DO_INV_ITEM '||X_Err_Code||' '||X_Err_Buff
,x_error_message => X_Err_Msg
,x_status => X_Status);
If X_Adj_Action = 'DELETE' then
Begin
If X_Mass_Gen_Flag = 'Y' then
Begin
/*=========================================Commented out NOCOPY for R11=========================
--Commented out NOCOPY for R11 as PA will run the extension in a loop for all potential projects
--So the code to actually fetch the projects which will have potential revenues to be deleted
--is not necessary
OPEN GET_TO_BE_DEL_REV_PROJECTS;
GMS_BILLING_ADJUSTMENTS.PERFORM_REV_BILL_ADJS('DELETE',
'Invoice',
X_project_id,
NULL,
NULL,
NULL,
NULL,
NULL,
X_retcode,
X_errbuf);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.DELINV'
,x_message => X_errbuf
,x_error_message => X_Err_Msg
,x_status => X_Status); */
select
b.DRAFT_INVOICE_NUM_CREDITED
into
X_Draft_Invoice_Num_Credited
from
PA_BILLING_INV_PROCESSED_V a
,PA_DRAFT_INVOICES b
where a.project_id = X_project_id
and b.project_id = a.project_id
and b.draft_invoice_num = a.draft_invoice_num;
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '1 - CANINV'
,x_message => 'No Invoice found for Cancellation'
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.CANINV'
,x_message => 'Retcode is '||X_retcode
,x_error_message => X_Err_Msg
,x_status => X_Status);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.CANINV'
,x_message => X_Errbuf
,x_error_message => X_Err_Msg
,x_status => X_Status); */
select draft_invoice_num_credited
into X_Draft_Invoice_Num_Credited
from pa_draft_invoices_all
where project_id = X_project_id
and request_id = X_request_id
and (nvl(write_off_flag,'N') = 'Y' OR
nvl(concession_flag,'N') = 'Y');
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '7 - WRIINV'
,x_message => 'AFTER ADJUSTMENTS Retcode '||x_retcode||' '||X_errbuf
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '8 - WRIINV'
,x_message => 'Failure '
,x_error_message => X_Err_Msg
,x_status => X_Status); */
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => '9 - WRIINV'
,x_message => 'Failure - When Others'
,x_error_message => X_Err_Msg
,x_status => X_Status);
GMS_BILLING_ADJUSTMENTS.PERFORM_REV_BILL_ADJS('DELETE',
'Revenue',
X_project_id,
NULL,
NULL,
NULL,
'N',
NULL,
X_retcode,
X_errbuf);
/* pa_billing_pub.insert_message(X_INSERTING_PROCEDURE_NAME => 'GMS_BILLING_ADJUSTMENTS.DELREV'
,x_message => X_errbuf
,x_error_message => X_Err_Msg
,x_status => X_Status);