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.
Testing
English Version
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.
Testing
日本語版
期間制御はiDempiere ERPを含むすべてのERPシステムに不可欠な機能です。取引が発生できる期間を定義することで、企業が財務記録やレポートを管理できるようにします。しかし、取引が複数の期間にまたがる状況があり、それらを完了することが困難になることがあります。例えば、顧客のサンプル注文は、製品の開発やテストに時間がかかるため、ある期間に開始して別の期間に終了する場合があります。そのような場合、関連するすべての文書が締め切られていると、注文を完了できなくなることがあります。
この問題に対処するために、iDempiere ERPは以下のステップを含むソリューションを提供しています：
- 新しいDocumentBaseTypeを作成し、サンプル注文依頼をこのカテゴリに割り当てます。この新しいカテゴリにより、サンプル注文は複数期間にまたがる他の文書の締め切りの影響を受けなくなります。
- カレンダーウィンドウに新しいタブを追加して、除外するDocumentBaseTypeを記録します。サンプル注文依頼で使用するDocumentBaseTypeをこのタブに追加し、期間締め処理から除外します。
- 元のJavaプロセスを置き換える新しいプロシージャプロセスを作成します。この新しいプロセスでは、期間を締める際に除外対象として指定されたすべてのDocumentBaseTypeを除外します。
これらのステップに従うことで、複数期間にまたがる取引を効果的に管理し、ERPシステム内ですべての注文が正しく完了することを確認できます。期間制御機能は財務取引を管理するための不可欠なツールであり、これらのヒントは複雑なワークフローやプロセスを効果的にナビゲートするのに役立ちます。
元のJavaプロセス
/******************************************************************************
* 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;
/**
* すべての期間（制御）の開閉
*
* @author Jorg Janke
* @version $Id: PeriodStatus.java,v 1.2 2006/07/30 00:51:02 jjanke Exp $
*/
public class PeriodStatus extends SvrProcess
{
/** 期間 */
private List<Integer> p_C_Period_IDs;
/** アクション */
private String p_PeriodAction = null;
/**
* 準備 - 例：パラメータの取得
*/
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());
}
} // 準備
/**
* 処理
* @return メッセージ
* @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='");
// 開く
if (MPeriodControl.PERIODACTION_OpenPeriod.equals(p_PeriodAction))
sql.append (MPeriodControl.PERIODSTATUS_Open);
// 閉じる
else if (MPeriodControl.PERIODACTION_ClosePeriod.equals(p_PeriodAction))
sql.append (MPeriodControl.PERIODSTATUS_Closed);
// 永久に閉じる
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();
} // 実行
} // PeriodStatus
プロシージャプロセス
-- 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;「procedure」タイプの新しいプロセスを「ad_periodstatus_sp」という名前で作成します。
「C_Period」テーブルの「Processing」カラムのプロセスを置き換えます。
