Period Control is an essential feature of any enterprise resource planning (ERP) system, including iDempiere ERP. It allows businesses to manage their financial records and reports by defining the time periods during which transactions can occur. However, there are situations where transactions can span multiple periods, making it difficult to complete them. For example, a customer’s sample order may start in one period and end in another period due to the time it takes to develop and test the product. In such cases, the completion of the sample order may be hindered if all related documents have been closed, preventing the order from being completed.
To address this issue, iDempiere ERP offers a solution that involves the following steps:
- Create a new DocumentBaseType and assign the Request Sample Order to this category. This new category will ensure that the sample order is not affected by the closing of other documents that may span multiple periods.
- Add a new tab to the Calendar window to record the excluded DocumentBaseTypes. Add the DocumentBaseType used by the Request Sample Order to this tab to exclude it from the period closing process.
- Create a new Procedure Process to replace the original Java process. In this new process, exclude all the DocumentBaseTypes that have been specified for exclusion when closing the period.
By following these steps, it’s possible to effectively manage transactions that span multiple periods and ensure that all orders are completed correctly within the ERP system. The period control feature is an essential tool for managing financial transactions, and these tips can help users effectively navigate complex workflows and processes.
The original Java process
/******************************************************************************
* Product: Adempiere ERP & CRM Smart Business Solution *
* Copyright (C) 1999-2006 ComPiere, Inc. All Rights Reserved. *
* This program is free software; you can redistribute it and/or modify it *
* under the terms version 2 of the GNU General Public License as published *
* by the Free Software Foundation. This program is distributed in the hope *
* that it will be useful, but WITHOUT ANY WARRANTY; without even the implied *
* warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. *
* See the GNU General Public License for more details. *
* You should have received a copy of the GNU General Public License along *
* with this program; if not, write to the Free Software Foundation, Inc., *
* 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA. *
* For the text or an alternative of this public license, you may reach us *
* ComPiere, Inc., 2620 Augustine Dr. #245, Santa Clara, CA 95054, USA *
* or via info@compiere.org or http://www.compiere.org/license.html *
*****************************************************************************/
package org.compiere.process;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import org.compiere.model.MPeriod;
import org.compiere.model.MPeriodControl;
import org.compiere.util.AdempiereUserError;
import org.compiere.util.CacheMgt;
import org.compiere.util.DB;
/**
* Open/Close all Period (Control)
*
* @author Jorg Janke
* @version $Id: PeriodStatus.java,v 1.2 2006/07/30 00:51:02 jjanke Exp $
*/
public class PeriodStatus extends SvrProcess
{
/** Periods */
private List<Integer> p_C_Period_IDs;
/** Action */
private String p_PeriodAction = null;
/**
* Prepare - e.g., get Parameters.
*/
protected void prepare()
{
ProcessInfoParameter[] para = getParameter();
for (int i = 0; i < para.length; i++)
{
String name = para[i].getParameterName();
if (para[i].getParameter() == null)
;
else if (name.equals("PeriodAction"))
p_PeriodAction = (String)para[i].getParameter();
else if (name.equals("*RecordIDs*"))
;
else
log.log(Level.SEVERE, "Unknown Parameter: " + name);
}
p_C_Period_IDs = getRecord_IDs();
if (p_C_Period_IDs == null || p_C_Period_IDs.size() == 0) {
p_C_Period_IDs = new ArrayList<Integer>();
p_C_Period_IDs.add(getRecord_ID());
}
} // prepare
/**
* Process
* @return message
* @throws Exception
*/
protected String doIt() throws Exception
{
int no = 0;
if (log.isLoggable(Level.INFO)) log.info ("C_Period_ID=" + p_C_Period_IDs + ", PeriodAction=" + p_PeriodAction);
for (int p_C_Period_ID : p_C_Period_IDs) {
MPeriod period = new MPeriod (getCtx(), p_C_Period_ID, get_TrxName());
if (period.get_ID() == 0)
throw new AdempiereUserError("@NotFound@ @C_Period_ID@=" + p_C_Period_ID);
StringBuilder sql = new StringBuilder ("UPDATE C_PeriodControl ");
sql.append("SET PeriodStatus='");
// Open
if (MPeriodControl.PERIODACTION_OpenPeriod.equals(p_PeriodAction))
sql.append (MPeriodControl.PERIODSTATUS_Open);
// Close
else if (MPeriodControl.PERIODACTION_ClosePeriod.equals(p_PeriodAction))
sql.append (MPeriodControl.PERIODSTATUS_Closed);
// Close Permanently
else if (MPeriodControl.PERIODACTION_PermanentlyClosePeriod.equals(p_PeriodAction))
sql.append (MPeriodControl.PERIODSTATUS_PermanentlyClosed);
else
return "-";
//
sql.append("', PeriodAction='N', Updated=getDate(),UpdatedBy=").append(getAD_User_ID());
// WHERE
sql.append(" WHERE C_Period_ID=").append(period.getC_Period_ID())
.append(" AND PeriodStatus<>'P'")
.append(" AND PeriodStatus<>'").append(p_PeriodAction).append("'");
no += DB.executeUpdateEx(sql.toString(), get_TrxName());
CacheMgt.get().reset("C_Period", p_C_Period_ID);
}
CacheMgt.get().reset("C_PeriodControl", 0);
StringBuilder msgreturn = new StringBuilder("@Updated@ #").append(no);
return msgreturn.toString();
} // doIt
} // PeriodStatus
Procedure Process
-- FUNCTION: adempiere.ad_periodstatus_sp(numeric)
-- DROP FUNCTION IF EXISTS adempiere.ad_periodstatus_sp(numeric);
CREATE OR REPLACE FUNCTION adempiere.ad_periodstatus_sp(
pinstance numeric)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
ResultStr VARCHAR (200);
p RECORD;
r RECORD;
p_Record_ID NUMERIC(10) := 0;
p_User_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
p_C_Period_ID NUMERIC(10) := 0;
p_PeriodAction VARCHAR (1) := '';
v_message VARCHAR (400) := '';
v_docstatus VARCHAR (2) := '';
v_count NUMERIC(10) := 0;
v_NextNo NUMERIC(10) := 0;
v_AD_Client_ID NUMERIC(10) := 0;
v_AD_Org_ID NUMERIC(10) := 0;
BEGIN
IF pinstance IS NULL THEN
pinstance := 0;
END IF;
v_message :=' process start';
BEGIN
ResultStr := 'PInstanceNotFound';
UPDATE adempiere.ad_pinstance
SET created = SYSDATE,
isprocessing = 'Y',
reslut = 0
WHERE ad_pinstance_id = pinstance_id;
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
v_message :='parameter ';
FOR p IN (
SELECT
pi.record_id,
pi.ad_client_id,
pi.ad_org_id,
pi.ad_user_id,
pp.parametername,
pp.p_string,
pp.p_number,
pp.p_date,
pp.p_string_TO,
pp.p_number_TO,
pp.p_date_tO
FROM
adempiere.ad_pinstance pi
LEFT OUTER JOIN adempiere.ad_pinstance_para pp ON(pi.ad_pinstance_id = pp.ad_pinstance_id)
WHERE
pi.ad_pinstance_id = pinstance
ORDER BY
pp.SeqNo
)
LOOP
p_Record_ID := p.record_id;
p_User_ID := p.AD_User_id;
p_AD_Client_ID := p.AD_Client_ID;
IF p.parametername = 'AD_User_ID' THEN
p_AD_User_ID = p.p_number;
ELSIF p.parametername = 'PeriodAction' THEN
p_PeriodAction = p.p_string;
END IF;
p_C_Period_ID = p_Record_ID;
END LOOP;
v_message :='主要程式開始';
IF p_PeriodAction in ('C','O','P') THEN
UPDATE C_PeriodControl SET PeriodStatus = p_PeriodAction ,
PeriodAction='N', Updated=getDate(), UpdatedBy = p_User_ID
WHERE C_Period_ID = p_C_Period_ID
AND ((p_PeriodAction in ('C','P') and docbasetype not in (select docbasetype from C_CalendarNoDocBaseType where ad_client_id = p_AD_Client_ID))
OR p_PeriodAction not in ('C','P')
)
AND PeriodStatus <> 'P'
AND PeriodStatus <> p_PeriodAction ;
END IF;
v_message :='主要程式結束';
IF pinstance > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET
updated = now(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE
ad_pinstance_id = pinstance;
-- COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE adempiere.ad_pinstance
SET updated = now(),
isprocessing = 'N',
result = 0,
errormsg = v_message
WHERE ad_pinstance_id = pinstance;
-- COMMIT;
END;
$BODY$;
ALTER FUNCTION adempiere.ad_periodstatus_sp(numeric)
OWNER TO adempiere;
GRANT EXECUTE ON FUNCTION adempiere.ad_periodstatus_sp(numeric) TO adempiere;Create a new process of type ‘procedure’ named ‘ad_periodstatus_sp’.
Replace the process in the ‘Processing’ column of the ‘C_Period’ table.
