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