Question

Approval process of setting a forecast as an active budget

  • 7 November 2019
  • 6 replies
  • 899 views

Userlevel 5
Badge +9

When we make the forecast status as active budget we can use an approval template to authorize the budget as an active budget. But this can be removed by users.

Is there a better way to handle this as in Purchase Requisition authorization where authorization is enabled via a rule.


6 replies

Userlevel 7

You can prevent users from deleting the approval steps using custom events.

We’ve done quite complicated configurations to automatically populate the approval steps using the Purchase Auth rules mapped to project roles. It’s been a while since this was done, so I don’t remember all the exact details but have tried to add some details below.

The routing determines the number of approval steps need to be inserted. PO Min/Max amount can either be setup as the amount for the budget or as a margin percentage (as in example below). 

 

In the Routing Templates we define the approvers. To avoid hard-coding approvers, we are using Project Roles as approvers.

The actual names will be fetched from the role assignments on the project you are creating the forecast for. 

In our case we triggered an event when the forecast was set to “reviewed” and the project category was “budget”.

 

Calculate the forecast margin

cursor get_forecast_margin is
       select round(( 1 - (sum(a.eac)/sum(b.eac))) * 100,2) from ifsapp.project_forecast_item_tab a, ifsapp.project_forecast_item_tab b
     where a.forecast_id=b.forecast_id and a.item_type = 'COST' and b.item_type = 'REVENUE' and
     a.project_id='&OLD:PROJECT_ID' and a.forecast_id=&OLD:FORECAST_ID;

Get the template from the approval rules
cursor get_template_id(forecast_margin_ NUMBER) is
   select max(template_id) From ifsapp.PUR_APPROVAL_RULE p where objstate = 'Released' and approval_rule in (
select a.approval_rule from ifsapp.PUR_ORD_RULE_PROJ_CAT2 a, ifsapp.PUR_ORD_RULE_PROJ_CAT1 b where
a.approval_rule=b.approval_rule and a.CATEGORY2_ID = 'BUDGET' and b.category1_id=ifsapp.project_api.Get_Category1_Id('&OLD:PROJECT_ID'))
and min_amount <= forecast_margin_ and nvl(max_amount,999999999) >= forecast_margin_;

Get the approvers
cursor get_routing(project_id_ VARCHAR2, template_id_ VARCHAR2) is
   select pur.line_no, pur.route, pur.Approval_level_db, nvl(pur.authorize_id,prj.person_id) as reqd_authorizer
   from ifsapp.PUR_APPROVAL_TEMPL_LINE pur
   left outer join ifsapp.PROJECT_ROLE_ASSIGNMENT prj on pur.project_role_id = prj.role_id and prj.project_id = project_id_
   where pur.template_id = template_id_;

 

And then finally some inserts (this can surely be done much better)

 

BEGIN

   forecast_id_ :=&OLD:FORECAST_ID;
   key_ref_ := 'FORECAST_ID='||forecast_id_||'^';

   OPEN get_forecast_margin;
   FETCH get_forecast_margin INTO forecast_margin_;
   CLOSE get_forecast_margin;

   OPEN get_template_id(forecast_margin_);
   FETCH get_template_id INTO template_id_;
   CLOSE get_template_id;

   OPEN get_routing(project_id_,template_id_);
   FETCH get_routing INTO line_id_,route_,approval_level_db_,reqd_authorizer_;
   CLOSE get_routing;

      FOR i IN get_routing(project_id_, template_id_) LOOP

--Insert approval steps into table if they do not already exist
begin insert into IFSAPP.APPROVAL_ROUTING_TAB 
(LU_NAME,KEY_REF,LINE_NO,STEP_NO,CURRENT_STEP_NO,DESCRIPTION,PERSON_ID,SECURITY_CHECKPOINT_REQ,rowversion,PREV_APPROVAL_DATE)
VALUES 
('ProjectForecast',key_ref_, line_id_,auth_step_,auth_step_,'Margin%: '||forecast_margin_ ||' Template'||template_id_,i.reqd_authorizer,'FALSE',sysdate,sysdate);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

      auth_step_ := auth_step_ + 10; 
      line_id_ := line_id_ + 1; 
      END LOOP;


END;

Userlevel 7
Badge +30

Great little hack there! :) It would also probably be possible to stop the forecast status from entering a certain status before all approval steps have been approved. And other similar nice things…

 

Userlevel 5
Badge +10

Seems like something needs to be improved in the core here

Userlevel 7
Badge +30

Seems like something needs to be improved in the core here

Perhaps. When it comes to these generic functionalities, like all/most attachments are, it’s always a challenge to make new features generally useful in all parts of the application. Sometimes it’s actually better to let customers do some bit of configuration instead of making the core more complex. But it depends on the requirement, of course.

Userlevel 5
Badge +10

Agreed, little configuration and tweaking of the process will probably always happen. And there will always be enhancement requests. But copying some functionality from the purchase process doesn't really sound like a bad idea ;-)

Userlevel 7
Badge +30

Agreed, little configuration and tweaking of the process will probably always happen. And there will always be enhancement requests. But copying some functionality from the purchase process doesn't really sound like a bad idea ;-)

Perhaps you meant to improve how the forecasting functionality works together with approvals, i.e. adding business logic there. Then I agree it would be a good match. Bigger parts of the product should have more nice functionality that enhances the different attachments that we have.

Reply