Package Specification
CREATE OR REPLACE PACKAGE IDC_Billing_Ext AS
/*Procedure GetCurrentRevenue( X_project_id NUMBER,
X_task_Id NUMBER DEFAULT NULL,
X_revenue_amount OUT NOCOPY REAL);
Procedure GetCurrentINVAmount( X_project_id NUMBER,
X_task_Id NUMBER DEFAULT NULL,
X_inv_amount OUT NOCOPY REAL);
PROCEDURE Billing_Events(X_project_id IN NUMBER,
X_top_task_id IN NUMBER DEFAULT NULL,
X_calling_process IN VARCHAR2 DEFAULT NULL,
X_calling_place IN VARCHAR2 DEFAULT NULL,
X_amount IN NUMBER DEFAULT NULL,
X_percentage IN NUMBER DEFAULT NULL,
X_rev_or_bill_date IN DATE DEFAULT NULL,
X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
X_bill_extension_id IN NUMBER DEFAULT NULL,
X_request_id IN NUMBER DEFAULT NULL);
*/
PROCEDURE Revenue_Events(X_project_id IN NUMBER,
X_top_task_id IN NUMBER DEFAULT NULL,
X_calling_process IN VARCHAR2 DEFAULT NULL,
X_calling_place IN VARCHAR2 DEFAULT NULL,
X_amount IN NUMBER DEFAULT NULL,
X_percentage IN NUMBER DEFAULT NULL,
X_rev_or_bill_date IN DATE DEFAULT NULL,
X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
X_bill_extension_id IN NUMBER DEFAULT NULL,
X_request_id IN NUMBER DEFAULT NULL);
PROCEDURE EXECUTE( X_project_id IN NUMBER,
X_top_task_id IN NUMBER DEFAULT NULL,
X_calling_process IN VARCHAR2 DEFAULT NULL,
X_calling_place IN VARCHAR2 DEFAULT NULL,
X_amount IN NUMBER DEFAULT NULL,
X_percentage IN NUMBER DEFAULT NULL,
X_rev_or_bill_date IN DATE DEFAULT NULL,
X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
X_bill_extension_id IN NUMBER DEFAULT NULL,
X_request_id IN NUMBER DEFAULT NULL);
END IDC_Billing_Ext;Package Body
CREATE OR REPLACE PACKAGE BODY IDC_Billing_Ext AS
PROCEDURE Revenue_Events(X_project_id IN NUMBER,
X_top_task_id IN NUMBER DEFAULT NULL,
X_calling_process IN VARCHAR2 DEFAULT NULL,
X_calling_place IN VARCHAR2 DEFAULT NULL,
X_amount IN NUMBER DEFAULT NULL,
X_percentage IN NUMBER DEFAULT NULL,
X_rev_or_bill_date IN DATE DEFAULT NULL,
X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
X_bill_extension_id IN NUMBER DEFAULT NULL,
X_request_id IN NUMBER DEFAULT NULL) IS
l_error_message VARCHAR2(240);
l_status NUMBER;
l_rev_amt NUMBER := 0;
l_inv_amt NUMBER := 0;
l_rev_amount NUMBER := 0;
l_inv_amount NUMBER := 0;
l_percent number:=0;
v_pct_comp NUMBER :=100;
X_revenue_amount number;
accrued_rev REAL;
X_inv_amount REAL;
v_rem_months number;
V_recog_amt real;
e_cant_create_revenue_event EXCEPTION;
BEGIN
Pa_Billing_Pub.Insert_Message(X_inserting_procedure_name =>'IDC_Billing_Ext.execute',
X_message => 'Starting',
X_status => l_status,
X_error_message => l_error_message);
v_pct_comp := PA_BILL_PCT.GetPercentComplete(
X_project_id ,
X_top_task_id,
X_rev_or_bill_date
);
if v_pct_comp < 100 then
---- select services cost---
begin
X_revenue_amount := 777;
end;
end if;
if v_pct_comp >= 100 then
--- select all invoices total amount minus revenue recognized/ remaining months.
select round(months_between(completion_date, sysdate)) into v_rem_months from pa_projects_all where project_id =X_project_id;
SELECT sum(nvl(dri.projfunc_bill_amount,0)) into accrued_rev
FROM pa_draft_invoice_items dri
WHERE dri.project_id = X_project_id;
--AND nvl(dri.task_id,0) = decode(X_task_id, NULL, nvl(dri.task_id,0), X_task_id);
X_inv_amount := nvl(accrued_rev,0);
select revenue_amount INTO V_recog_amt from PA_DRAFT_REVENUES_V where project_id =X_project_id and revenue_status_code='RELEASED';
X_revenue_amount :=(X_inv_amount-V_recog_amt)/v_rem_months;
end if;
IF x_calling_process = 'Revenue' THEN
l_rev_amt := X_revenue_amount;
l_inv_amt := 0;
END IF;
Pa_Billing_Pub.Insert_Message
(X_inserting_procedure_name =>'IDC_Billing_Ext.execute',
X_message => 'Create '||x_calling_process||'EVENT FOR REVENUE',
X_status => l_status,
X_error_message => l_error_message);
Pa_Billing_Pub.Insert_Event (X_txn_currency_code => 'USD' ,X_rev_amt => l_rev_amt ,X_bill_amt => l_inv_amt ,X_project_id => X_project_id ,X_event_type => 'Percent Complete' ,X_event_description => 'Percent Complete'
-- ,X_attribute1 => r_items.ib_item_id
-- ,X_attribute7 => r_items.circuit_number
-- ,x_attribute6 => r_items.invoice_group
-- ,x_Attribute2 => r_items.category_description
-- ,x_attribute3 => r_items.customer_reference
-- ,x_attribute4 => TO_CHAR(l_date_from,'DD-MON-YYYY')
-- ,x_attribute5 => TO_CHAR(l_date_to,'DD-MON-YYYY')
-- --,x_attribute8 => r_items.vanco_reference
-- ,x_attribute8 => l_ci_task_ref
-- ,x_attribute10 => r_items.customer_other_reference
-- ,x_reference1 => r_items.cr_number
-- ,x_reference2 => r_items.project_code
-- ,x_reference3 => r_items.working_unit
-- ,x_unit_price => r_items.contract_run_rate_price
-- ,X_top_task_id => r_items.task_id
-- ,X_organization_id => r_items.org_id
,X_completion_date => sysdate
,X_event_num_reversed => NULL
,X_status => l_status
,X_error_message => l_error_message);
-- Check status and fail if necessary IF l_status <> 0 THEN RAISE e_cant_create_revenue_event; END IF;
EXCEPTION
WHEN e_cant_create_revenue_event THEN
Pa_Billing_Pub.Insert_Message(X_inserting_procedure_name =>'vacus_billing_ext.execute', X_message => 'Error: IDC Creating Revenue Event', X_status => l_status, X_error_message => l_error_message); RAISE_APPLICATION_ERROR(-20001,'Error: IDC Creating Revenue event :'||l_error_message);
END REVENUE_EVENTS;
-- +-------------------------------------------------------------------------+
-- |----< execute >----------------------------------------------------------|
-- +-------------------------------------------------------------------------+
-- Description:
PROCEDURE EXECUTE( X_project_id IN NUMBER,
X_top_task_id IN NUMBER DEFAULT NULL,
X_calling_process IN VARCHAR2 DEFAULT NULL,
X_calling_place IN VARCHAR2 DEFAULT NULL,
X_amount IN NUMBER DEFAULT NULL,
X_percentage IN NUMBER DEFAULT NULL,
X_rev_or_bill_date IN DATE DEFAULT NULL,
X_bill_extn_assignment_id IN NUMBER DEFAULT NULL,
X_bill_extension_id IN NUMBER DEFAULT NULL,
X_request_id IN NUMBER DEFAULT NULL) IS
BEGIN
IF x_calling_process = 'Revenue' THEN
revenue_events ( X_project_id
,X_top_task_id
,X_calling_process
,X_calling_place
,X_amount
,X_percentage
,X_rev_or_bill_date
,X_bill_extn_assignment_id
,X_bill_extension_id
,X_request_id );
END IF;
END EXECUTE;
END IDC_Billing_Ext;
---select * from user_errors where name like 'IDC%'
