ERP 2.50:Developers Guide/Examples/Process/it

Contents 1 Introduzione 2 Obiettivo 3 Implementazione 3.1 Dizionario Applicazione 3.1.1 Definizione Report 3.1.2 Definizione Parametro 3.2 PL/SQL 3.2.1 ma_copy_version 3.2.2 Spiegazione

Introduzione

Questo esempio spiega un processo PL/SQL già presente in Openbravo ERP. È possibile provare la procedura descritta nella finestra del Piano Processi

Obiettivo

Spiegare come questo processo è stato attuato nel Dizionario Applicazione e il codice PL/SQL.

Implementazione

Dizionario Applicazione

Definizione Report

• 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

Definizione Parametro

• 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;

Spiegazione

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;

Il valore del parametro è memorizzato nella variabile v_Version, 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 attraverso le sequenze e i prodotti di ogni sequenza ed inserirla nella nuova versione.

 
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;