ERP 2.50:Developers Guide/Examples/Process
Languages: |
Contents |
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;
Languages: |
ERP 2.50:Developers Guide/Examples/Report | ERP 2.50:Developers Guide/Examples/SOAP WebService