ERP 2.50:Developers Guide/Examples/Process

ERP 2.50:Developers Guide

ERP 2.50:Developers Guide/Examples ERP 2.50:Developers Guide/Examples/Search ERP 2.50:Developers Guide/Examples/Report ERP 2.50:Developers Guide/Examples/Process ERP 2.50:Developers Guide/Examples/SOAP WebService ERP 2.50:Developers Guide/Examples/Window and Tab ERP 2.50:Developers Guide/Examples/Data Access Layer and REST ERP 2.50:Developers Guide/Examples/Reference

Index

Contents 1 Introduction 2 Objective 3 Implementation 3.1 Application Dictionary 3.1.1 Report definition 3.1.2 Parameter definition 3.2 PL/SQL 3.2.1 ma_copy_version 3.2.2 Explanation

Introduction

This example explains a PL/SQL process already present in Openbravo ERP. You can test the explained procedure in the Process Plan window.

Objective

Explain how this process was implemented in the application dictionary and the PL/SQL code.

Implementation

Application Dictionary

Report definition

• Search Key: MA_Processplan_CopyVersion
• Name: Copy Version
• Description: Process to copy the structure of the selected version.
• Help/Comment: Process to copy the structure of the selected version.
• Data Access Level: Client/Organization
• UI Pattern: Standard
• Procedure: MA_Copy_Version

Parameter definition

• Name: Process Plan Version
• Help/Comment: Indicates a process plan version.
• Sequence Number: 10
• DB Column Name: MA_Processplan_Version_ID
• Application Element: MA_Processplan_Version_ID - Process Plan Version
• Reference: TableDir
• Length: 0

PL/SQL

ma_copy_version

 
-- Function: ma_copy_version(character varying)
 
-- DROP FUNCTION ma_copy_version(character varying);
 
CREATE OR REPLACE FUNCTION ma_copy_version(p_pinstance_id character varying)
  RETURNS void AS
$BODY$ DECLARE 
/*************************************************************************
* The contents of this file are subject to the Openbravo  Public  License
* Version  1.0  (the  "License"),  being   the  Mozilla   Public  License
* Version 1.1  with a permitted attribution clause; you may not  use this
* file except in compliance with the License. You  may  obtain  a copy of
* the License at http://www.openbravo.com/legal/license.html
* Software distributed under the License  is  distributed  on  an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific  language  governing  rights  and  limitations
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SL
* All portions are Copyright (C) 2001-2006 Openbravo SL
* All Rights Reserved.
* Contributor(s):  ______________________________________.
************************************************************************/
  --  Logistice
  v_ResultStr VARCHAR(2000):=''; --OBTG:VARCHAR2--
  v_Message VARCHAR(2000):=''; --OBTG:VARCHAR2--
  v_Record_ID VARCHAR(32); --OBTG:VARCHAR2--
  v_Result NUMERIC:=1; --  Success
  --  Parameter
  --TYPE RECORD IS REFCURSOR;
    Cur_Parameter RECORD;
    --  Record Info
    v_Version VARCHAR(32); --OBTG:VARCHAR2--
    v_ProcessPlanVersion_ID VARCHAR(32); --OBTG:VARCHAR2--
    v_Client_ID VARCHAR(32); --OBTG:VARCHAR2--
    v_Org_ID VARCHAR(32); --OBTG:VARCHAR2--
    v_User_ID VARCHAR(32); --OBTG:VARCHAR2--
    v_UpdatedBy VARCHAR(32); --OBTG:VARCHAR2--
    v_DocumentNo NUMERIC;
    v_Sequence_ID VARCHAR(32); --OBTG:VARCHAR2--
    v_Product_ID VARCHAR(32); --OBTG:VARCHAR2--
    v_DateFrom TIMESTAMP :=TO_DATE(NOW());
    v_DateTo TIMESTAMP :=TO_DATE('31-12-9999', 'DD-MM-YYYY');
    v_Count NUMERIC;
    --  v_Processing        CHAR;
    --  v_Processed         CHAR;
    Cur_Sequence RECORD;
    Cur_Product RECORD;
  BEGIN
    v_Result:=1;
    --  Get Parameters
    v_ResultStr:='ReadingParameters';
    FOR Cur_Parameter IN
      (SELECT i.Record_ID,
        i.AD_User_ID,
        p.ParameterName,
        p.P_String,
        p.P_Number,
        p.P_Date,
        p.AD_Org_ID,
        p.AD_Client_ID
      FROM AD_PInstance i
      LEFT JOIN AD_PInstance_Para p
        ON i.AD_PInstance_ID=p.AD_PInstance_ID
      WHERE i.AD_PInstance_ID=p_PInstance_ID
      ORDER BY p.SeqNo
      )
    LOOP
      v_Record_ID:=Cur_Parameter.Record_ID;
      v_User_ID:=Cur_Parameter.AD_User_ID;
      v_Org_ID:=Cur_Parameter.AD_Org_ID;
      v_Client_ID:=Cur_Parameter.AD_Client_ID;
      IF(Cur_Parameter.ParameterName='MA_Processplan_Version_ID') THEN
        v_Version:=Cur_Parameter.P_String;
        RAISE NOTICE '%','  Version=' || v_Version ;
      END IF;
    END LOOP; --  Get Parameter
    RAISE NOTICE '%','  v_Record_ID=' || v_Record_ID ;
    v_ResultStr:='UpdateCurrentVersion';
    SELECT COUNT(*) INTO v_Count
    FROM MA_ProcessPlan_Version
    WHERE MA_Processplan_ID = v_Record_ID
      AND dateto>=v_DateFrom
      AND datefrom<=v_DateFrom;
    IF (v_count >= 0) THEN
      UPDATE MA_Processplan_Version
        SET dateto=v_DateFrom
      WHERE MA_Processplan_ID=v_Record_ID
        AND dateto>=v_DateFrom
        AND datefrom<=v_DateFrom;
    END IF;
    SELECT COUNT(*) INTO v_Count
    FROM MA_Processplan_Version
    WHERE MA_Processplan_ID = v_Record_ID
      AND datefrom > v_dateFrom;
    IF (v_Count > 0) THEN
      SELECT MIN(datefrom) INTO v_DateTo
      FROM MA_Processplan_version
      WHERE MA_Processplan_ID = v_Record_ID
        AND datefrom > v_DateFrom;
    END IF;
    v_ResultStr:='InsertVersion';
    SELECT * INTO  v_ProcessPlanVersion_ID FROM Ad_Sequence_Next('MA_ProcessPlan_Version', v_Client_ID) ;
    SELECT * INTO  v_DocumentNo FROM Ad_Sequence_Doc('DocumentNo_MA_ProcessPlan_Version', v_Client_ID, 'Y') ;
    INSERT
    INTO MA_Processplan_Version
      (
        MA_Processplan_Version_ID, AD_Client_ID, AD_Org_ID, Isactive,
        Created, Createdby, Updated, Updatedby,
        MA_Processplan_ID, DocumentNo, Datefrom, Dateto
      )
      VALUES
      (
        v_ProcessPlanVersion_ID, v_Client_ID, v_Org_ID, 'Y',
        TO_DATE(NOW()), COALESCE(v_User_ID, '0'), TO_DATE(NOW()), COALESCE(v_User_ID, '0'),
        v_Record_ID, v_DocumentNo, v_DateFrom, v_DateTo
      )
      ;
    FOR Cur_Sequence IN
      (SELECT *  FROM MA_Sequence  WHERE MA_Processplan_Version_ID=v_Version)
    LOOP
      v_ResultStr:='InsertSequence';
      SELECT * INTO  v_Sequence_ID FROM Ad_Sequence_Next('MA_Sequence', v_Client_ID) ;
      INSERT
      INTO MA_Sequence
        (
          MA_Sequence_ID, AD_Client_ID, AD_Org_ID, Isactive,
          Created, Createdby, Updated, Updatedby,
          MA_Processplan_Version_ID, MA_Process_ID, VALUE, Name,
          Description, CostCenterUse, Preptime, Multiplier,
          Seqno, Noqty, Groupuse, Calculated
        )
        VALUES
        (
          v_Sequence_ID, v_Client_ID, v_Org_ID, 'Y',
          TO_DATE(NOW()), COALESCE(v_User_ID, '0'), TO_DATE(NOW()), COALESCE(v_User_ID, '0'),
          v_ProcessPlanVersion_ID, Cur_Sequence.MA_Process_ID, Cur_Sequence.VALUE, Cur_Sequence.Name,
          Cur_Sequence.Description, Cur_Sequence.CostCenterUse, Cur_Sequence.Preptime, Cur_Sequence.Multiplier,
          Cur_Sequence.Seqno, Cur_Sequence.Noqty, Cur_Sequence.Groupuse, Cur_Sequence.Calculated
        )
        ;
      FOR Cur_Product IN
        (SELECT *
        FROM MA_SequenceProduct
        WHERE MA_Sequence_ID=Cur_Sequence.MA_Sequence_ID
        )
      LOOP
        v_ResultStr:='InsertProducts';
        SELECT * INTO  v_Product_ID FROM Ad_Sequence_Next('MA_SequenceProduct', v_Client_ID) ;
        INSERT
        INTO MA_SequenceProduct
          (
            MA_Sequenceproduct_ID, AD_Client_ID, AD_Org_ID, Isactive,
            Created, Createdby, Updated, Updatedby,
            MA_Sequence_ID, M_Product_ID, Quantity, Productiontype,
            C_Uom_ID, Quantityorder, M_Product_Uom_ID, Componentcost,
            Decrease, Rejected
          )
          VALUES
          (
            v_Product_ID, v_Client_ID, v_Org_ID, 'Y',
            TO_DATE(NOW()), COALESCE(v_User_ID, '0'), TO_DATE(NOW()), COALESCE(v_User_ID, '0'),
            v_Sequence_ID, Cur_Product.M_Product_ID, Cur_Product.Quantity, Cur_Product.Productiontype,
            Cur_Product.C_Uom_ID, Cur_Product.Quantityorder, Cur_Product.M_Product_Uom_ID, Cur_Product.Componentcost,
            Cur_Product.Decrease, Cur_Product.Rejected
          )
          ;
      END LOOP;
    END LOOP;
    ---- <<FINISH_PROCESS>>
    --  Update AD_PInstance
    RAISE NOTICE '%','Updating PInstance - Finished - ' || v_Message ;
    PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, v_User_ID, 'N', v_Result, v_Message) ;
    RETURN;
EXCEPTION
  WHEN OTHERS THEN
    v_ResultStr:= '@ERROR=' || SQLERRM;
    RAISE NOTICE '%',v_ResultStr ;
    -- ROLLBACK;
    PERFORM AD_UPDATE_PINSTANCE(p_PInstance_ID, NULL, 'N', 0, v_ResultStr) ;
    RETURN;
END ; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION ma_copy_version(character varying) OWNER TO tad;

Explanation

This part of the code, loops through the parameters for this PInstance_Para, searching for the version parameter

 
    FOR Cur_Parameter IN
      (SELECT i.Record_ID,
        i.AD_User_ID,
        p.ParameterName,
        p.P_String,
        p.P_Number,
        p.P_Date,
        p.AD_Org_ID,
        p.AD_Client_ID
      FROM AD_PInstance i
      LEFT JOIN AD_PInstance_Para p
        ON i.AD_PInstance_ID=p.AD_PInstance_ID
      WHERE i.AD_PInstance_ID=p_PInstance_ID
      ORDER BY p.SeqNo
      )
    LOOP
      v_Record_ID:=Cur_Parameter.Record_ID;
      v_User_ID:=Cur_Parameter.AD_User_ID;
      v_Org_ID:=Cur_Parameter.AD_Org_ID;
      v_Client_ID:=Cur_Parameter.AD_Client_ID;
      IF(Cur_Parameter.ParameterName='MA_Processplan_Version_ID') THEN
        v_Version:=Cur_Parameter.P_String;
        RAISE NOTICE '%','  Version=' || v_Version ;
      END IF;
    END LOOP;

The value of the parameter is stored in v_Version variable, as you may se the MA_Processplan_Version_ID parameter name matches the DB Column Name in the application dictionary. More information on retrieving parameters

 
      IF(Cur_Parameter.ParameterName='MA_Processplan_Version_ID') THEN
        v_Version:=Cur_Parameter.P_String;

Select a new id for the Process Plan and document

 
    SELECT * INTO  v_ProcessPlanVersion_ID FROM Ad_Sequence_Next('MA_ProcessPlan_Version', v_Client_ID) ;
    SELECT * INTO  v_DocumentNo FROM Ad_Sequence_Doc('DocumentNo_MA_ProcessPlan_Version', v_Client_ID, 'Y') ;

Insert a new Process plan version

 
    INSERT
    INTO MA_Processplan_Version
      (
        MA_Processplan_Version_ID, AD_Client_ID, AD_Org_ID, Isactive,
        Created, Createdby, Updated, Updatedby,
        MA_Processplan_ID, DocumentNo, Datefrom, Dateto
      )
      VALUES
      (
        v_ProcessPlanVersion_ID, v_Client_ID, v_Org_ID, 'Y',
        TO_DATE(NOW()), COALESCE(v_User_ID, '0'), TO_DATE(NOW()), COALESCE(v_User_ID, '0'),
        v_Record_ID, v_DocumentNo, v_DateFrom, v_DateTo
      )

Loop through the sequences and products from each sequence and insert it in the new version.

 
FOR Cur_Sequence IN
      (SELECT *  FROM MA_Sequence  WHERE MA_Processplan_Version_ID=v_Version)
    LOOP
      v_ResultStr:='InsertSequence';
      SELECT * INTO  v_Sequence_ID FROM Ad_Sequence_Next('MA_Sequence', v_Client_ID) ;
      INSERT
      INTO MA_Sequence
        (
          MA_Sequence_ID, AD_Client_ID, AD_Org_ID, Isactive,
          Created, Createdby, Updated, Updatedby,
          MA_Processplan_Version_ID, MA_Process_ID, VALUE, Name,
          Description, CostCenterUse, Preptime, Multiplier,
          Seqno, Noqty, Groupuse, Calculated
        )
        VALUES
        (
          v_Sequence_ID, v_Client_ID, v_Org_ID, 'Y',
          TO_DATE(NOW()), COALESCE(v_User_ID, '0'), TO_DATE(NOW()), COALESCE(v_User_ID, '0'),
          v_ProcessPlanVersion_ID, Cur_Sequence.MA_Process_ID, Cur_Sequence.VALUE, Cur_Sequence.Name,
          Cur_Sequence.Description, Cur_Sequence.CostCenterUse, Cur_Sequence.Preptime, Cur_Sequence.Multiplier,
          Cur_Sequence.Seqno, Cur_Sequence.Noqty, Cur_Sequence.Groupuse, Cur_Sequence.Calculated
        )
        ;
      FOR Cur_Product IN
        (SELECT *
        FROM MA_SequenceProduct
        WHERE MA_Sequence_ID=Cur_Sequence.MA_Sequence_ID
        )
      LOOP
        v_ResultStr:='InsertProducts';
        SELECT * INTO  v_Product_ID FROM Ad_Sequence_Next('MA_SequenceProduct', v_Client_ID) ;
        INSERT
        INTO MA_SequenceProduct
          (
            MA_Sequenceproduct_ID, AD_Client_ID, AD_Org_ID, Isactive,
            Created, Createdby, Updated, Updatedby,
            MA_Sequence_ID, M_Product_ID, Quantity, Productiontype,
            C_Uom_ID, Quantityorder, M_Product_Uom_ID, Componentcost,
            Decrease, Rejected
          )
          VALUES
          (
            v_Product_ID, v_Client_ID, v_Org_ID, 'Y',
            TO_DATE(NOW()), COALESCE(v_User_ID, '0'), TO_DATE(NOW()), COALESCE(v_User_ID, '0'),
            v_Sequence_ID, Cur_Product.M_Product_ID, Cur_Product.Quantity, Cur_Product.Productiontype,
            Cur_Product.C_Uom_ID, Cur_Product.Quantityorder, Cur_Product.M_Product_Uom_ID, Cur_Product.Componentcost,
            Cur_Product.Decrease, Cur_Product.Rejected
          )
          ;
      END LOOP;
    END LOOP;

---

ERP 2.50:Developers Guide/Examples/Report | ERP 2.50:Developers Guide/Examples/SOAP WebService