OrangeScape Developer Community

A community for Application Developers on OrangeScape's Platform-as-a-Service

Call a stored procedure from action and refresh the second level cache

Enabling Stored Procedures through Actions:  (credits to Vineesh(WIPRO Infotech))

  1. Suppose there are two models one is parent another one is child.

For e.g.  : Transfer is a parent model & Transfer_line_ item is a child and the reference connecting Transfer Line Item model with Transfer model is Transfer_Ref

  1. Transfer is having details like transfer order number, Location etc.
  2. Transfer_line_ item is having details of each employee who is getting transferred.
  3. There is a GetList action in the Transfer model which will list all the employees who has applied for transfer and these employees are shown in child grid of Transfer line Item model.
  4.  Now Convert Getlist action in to a stored procedure and named it as CREATE_BULKTRANSFER_PRC (vTransfer_Id VARCHAR (255)) where vTransfer_Id is the sheetid of the current transfer model.
  5. Create a Boolean cell named as Delete_Flag and a delete action (named as DeleteRecord) in  the Transfet_line_Item model.
  6. Create a Model called : TriggerProcedure
  7. Create a Reference cell - Transfer_ref in TriggerProcedure Model and connect it to Parent

Model (i.e. Transfer).

  1. Create an action called GetListCallProcedure in transfer model. It consist of following sub actions
    1. Create an append action using the reference cell TriggerProcedure .Transfer_ref
    2. Create another append action using the reference cell Transfer_Line_Item. Transfer_Ref. Click on explicit mapping àAdd new parameteràset Delete_Flag  =  1
    3. Create a childcall action which is calling the action “DeleteRecord” in the  child model Transfet_line_Item through reference cell Transfer_Ref and enter Search-Criteria as =AND(Transfer_Line_Item.Delete_Flag=1)
    4. This GetListCallProcedure action is shown in the UI for calling the stored procedure “CREATE_BULKTRANSFER_PRC”
  2. Finally create an update trigger on TriggerProcedure table.


USE `corehrms`$$

DROP TRIGGER /*!50032 IF EXISTS */ `ins_triggerprocedure`$$

CREATE  TRIGGER `ins_triggerprocedure` AFTER UPDATE ON `tbltriggerprocedure`


                IF NEW.Transfer_Ref IS NOT NULL


        CALL CREATE_BULKTRANSFER_PRC(new.Transfer_Ref);








Views: 121


You need to be a member of OrangeScape Developer Community to add comments!

Join OrangeScape Developer Community

© 2018   Created by OrangeScape Technologies.   Powered by

Badges  |  Report an Issue  |  Terms of Service