Billing Extension using Pa_Billing_Pub

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%'

wpsadmin
We will be happy to hear your thoughts

Leave a reply

Ads Blocker Image Powered by Code Help Pro

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

Powered By
Best Wordpress Adblock Detecting Plugin | CHP Adblock
Cloud Technologies Blog
Logo
Compare items
  • Total (0)
Compare
0
Shopping cart