iDempiere Callout ERP 規則引擎實作 groovy:客戶簡稱不得重覆

import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
// A_WindowNo    A_Tab    A_Field    A_Value    A_OldValue    A_Ctx   BOMQty

if (A_Value!=null &&  A_OldValue!=A_Value){

       String sname = (String)A_Tab.getValue("簡稱");
       sql = "SELECT COUNT(*) FROM 客戶 WHERE 簡稱=?";
       BigDecimal sQty = DB.getSQLValueBD (null, sql, [sname] );

       if  (  sQty.compareTo(new BigDecimal(0) ) > 0 )
           A_Tab.fireDataStatusEEvent("簡稱已存在","簡稱:"+ sname +"已存在", false);

    //  A_Tab.setValue("Description", "目前總數:"+tQty ); //  +" , "+BOMQty+" , "+OldBOMQty+" , "+SK_BOM_ID+" , "+SK_BOMLine_ID);
}
result=""

iDempiere Callout ERP 規則引擎實作 groovy:客戶ID帶出編號簡稱

import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet


// A_WindowNo    A_Tab    A_Field    A_Value    A_OldValue    A_Ctx   BOMQty
if (A_Value!=null)   // A_OldValue!=A_Value)
{
        Integer  ID= (Integer)A_Value;
 //     Integer  ID= (Integer)A_Tab.getValue("SK_BOM_ID");
        String sql = "SELECT 編號,簡稱 FROM 客戶 WHERE 客戶_ID=?";
        PreparedStatement pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, ID.intValue());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
           String code1 = rs.getString("編號");
           String name1 = rs.getString("簡稱")
           A_Tab.setValue("客戶名稱", code1+"_"+name1);
       }
}
result=""

iDempiere Callout ERP 規則引擎實作 groovy:產品ID帶品號品名規格

import org.compiere.model.MTable
import org.compiere.util.DB
import org.compiere.util.Msg
import java.math.BigDecimal
import java.sql.PreparedStatement
import java.sql.ResultSet


// A_WindowNo    A_Tab    A_Field    A_Value    A_OldValue    A_Ctx   BOMQty
if (A_Value!=null)   // A_OldValue!=A_Value)
{
        Integer  ID= (Integer)A_Value;
 //     Integer  ID= (Integer)A_Tab.getValue("SK_BOM_ID");
 //    
        String sql = "SELECT a.產品品號,a.產品品名,a.產品規格, c.單位_id, a.加工單價, a.應收單價 FROM 產品 a  INNER JOIN 計價單位 b ON a.計價單位_id=b.計價單位_id INNER JOIN 單位 c ON b.名稱=c.名稱 WHERE a.產品_ID=?";
        PreparedStatement pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, ID.intValue());
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
           String code1 = rs.getString("產品品號");
           String name1 = rs.getString("產品品名");
           String spec1 = rs.getString("產品規格");
           Integer  uom1 = new Integer( rs.getInt("單位_id"));
           BigDecimal price = rs.getBigDecimal ("加工單價");
           BigDecimal price1 = rs.getBigDecimal ("應收單價");

           if (code1  !=null) A_Tab.setValue("產品品號", code1 );
           if (name1 !=null) A_Tab.setValue("產品品名", name1 );
           if (spec1  !=null) A_Tab.setValue("產品規格", spec1 );
           if (uom1  !=null) A_Tab.setValue("單位_id", uom1 );
           if (price   !=null)  A_Tab.setValue("加工單價", price );    // 加工單價
           if (price1 !=null) A_Tab.setValue("單價", price1 );   // 應收單價
           A_Tab.setValue("備註", "單價:"+price1+" ,加工單價:"+price  );  
       }
}
result=""

這是一個自製的時代, 一個站在巨人的肩膀上的時代 (Base on Open Source ERP iDempiere)

這是一個自製的時代, 一個站在巨人的肩膀上的時代 (Base on Open Source ERP iDempiere)

我們鼓勵大家用 Open Source 當基礎發展出自己要的系統

( 依據 GPL 規範不管無償或有償, 有再度 [散播] 修改後軟體才公告免費讓第三者免費取得)
( [修改後軟體] : 意指動到 java source code, 有重新編譯過 idempere)
( iDempiere 是 MDA 增加: 輸入畫面, 查詢報表, 驗證規則, 並不需動到 java source code,)

EPICOR 是一個反社會潮流的宣告 :(節錄給大家欣賞)

她得偏激基礎立論就是
1.自製一定是沒有顧問
2.自製沒有Open Source 可以當基礎來發展

像他這種沒有 MDA 架構的 ERP 是技術落後的商業軟件
因為開放原碼沒有向下相容的必要性商業行為
因為開放原碼 Upgrade 是不需要付版權費用
因此沒有必要為版權費用自己去卡自己的路

In today's complex business environment, it can be very challenging for in house IT professionals to keep up with the pace of change necessary to sustain a competitive advantage
.

Whether the homegrown IT system you have seems "fine" for now,or your company has developed strong loyalty to a custom-built solution, these options cannot offer you the level of protection and security of an outside enterprise resource planning (ERP) solution.


ERP system installation is often thought of as the most risky, expensive, and disruptive investment a distributor can make in their business. However, you must also consider the inherent risks of a homegrown solution. When you argue against a potential system
switch with costs, pain, and efficiency concerns, you should take a look at them as they exist in your current system.

Compared to a robust ERP system built by a well-established software provider, homegrown tends to be fragile and lacking in - depth support.

And while a custom-built system may seem to offer control and flexibility, it may actually lock you into a suboptimal solution. Some basic questions to consider include:

Is software development really your core competency?

Can you spare the programming resources ?
Do you have the years and the capital to develop, test, and fine-tune your application?

What happens if you lose the precious few resources that can support and maintain your older technology?

What is your visibility into emerging technologies and industry best practices that other distributors are using?

Is your system truly integrating information and processes from all functional divisions of your organization, or do you have disparate data sources “running” your business?

Cost-effective ERP solutions meet distribution organizations’ demands by leveraging existing applications and technologies, while taking the burden off your internal development
resources.

For all of these reasons, it is in your best interest to partner with a leading ERP provider, in order to advance your operations to the next level of efficiency.

To help answer the above questions and explain the realities of an ERP system, we have compiled the top nine reasons to weigh the advantages of an outside ERP over your homegrown system
.

管理軟體 [ 自造 ] 活動 Management Software [ homegrown ]

管理軟體 [ 自造 ] 活動 Management Software  [ homegrown]

以下是說你可以買得到的軟體的話千萬別自己來
但是他沒告訴你買來調整比自己寫還複雜還困難
就像華碩買 一億軟體 兩億顧問 在花三億來調整改寫成自己要的
因為沒人敢承擔自己開發規格直接外包的後果
到最後只是繞一圈要承包商來發包改成自己要的
自己要的系統先透過買來的大系統驗證系統的差異性在重差異中開始改系統


http://erp.ittoolbox.com/groups/vendor-selection/erp-select/can-a-home-grown-system-be-the-right-choice-482229

Glenn Cronin replied Jun 2, 2004

1. IMHO ... 500 employees world-wide, puts this organization in the SME space NOT large.
2. This firm is NOT all that unique.
3. Never Build what you can BUY.
4. Investigate the ERP product from Visibility.


Glenn B. Cronin, CFPIM

iDempiere :: m_substitute 這一個替代用料沒有優先順序

iDempiere  :: 
原使設計  m_substitute 這一個替代用料表沒有優先順序
原使設計 這個 m_substitute 沒有 m_substitute_id 主鍵
原使設計 也沒在 bomline 裡面有是用替代類別


CREATE TABLE adempiere.m_substitute
(
  m_product_id numeric(10,0) NOT NULL,
  substitute_id numeric(10,0) NOT NULL,
  ad_client_id numeric(10,0) NOT NULL,
  ad_org_id numeric(10,0) NOT NULL,
  isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created timestamp without time zone NOT NULL DEFAULT now(),
  createdby numeric(10,0) NOT NULL,
  updated timestamp without time zone NOT NULL DEFAULT now(),
  updatedby numeric(10,0) NOT NULL,
  name character varying(60),
  description character varying(255),
  m_substitute_uu character varying(36) DEFAULT NULL::character varying,
  CONSTRAINT m_substitute_pkey PRIMARY KEY (m_product_id, substitute_id),

adempiere.tg_sk_rmjh_i

-- Function: adempiere.tg_sk_rmjh_i(numeric)

-- DROP FUNCTION adempiere.tg_sk_rmjh_i(numeric);

CREATE OR REPLACE FUNCTION adempiere.tg_sk_rmjh_i(p_instance numeric)
  RETURNS SETOF adempiere.tg_sk_rmjh_t AS
$BODY$ 
/* DEBUG AND CREATE TABLE
CURRENT_LANG =  en_US , zh_CN , zh_TW
set search_path=adempiere
"程式完成:: p_instance=1000545 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-06-30 ,p_SK_NO=TG-G016 - TG-G016"
select p.Value,Name,pi.* from ad_pinstance pi inner join ad_process p on p.ad_process_id=pi.ad_process_id order by ad_pinstance_id desc
*/
DECLARE

   p RECORD;
   q RECORD;
   r RECORD;
   v_message              VARCHAR (2000);
   ResultStr              VARCHAR (2000);
  
   p_Record_ID            NUMERIC(10)   := null;
   p_AD_User_ID           NUMERIC(10)   := null;
   p_AD_Process_ID        NUMERIC(10)   := null;
   p_AD_Client_ID         NUMERIC(10)   := null;
   p_AD_Org_ID            NUMERIC(10)   := null;
   p_DocDate              DATE;
   p_DocDateTo            DATE;
   p_DocumentNo           VARCHAR (200); --起迄
   p_DocumentNoTo         VARCHAR (200);
   p_DateOrdered          DATE;          --起迄
   p_DateOrderedTo        DATE;
   p_M_Product_ID         NUMERIC(10)   := 0; --起迄
   p_M_ProductTo_ID       NUMERIC(10)   := 0;
   p_C_BPartner_ID        NUMERIC(10)   := 0; --起迄
   p_C_BPartnerTo_ID      NUMERIC(10)   := 0;
   v_SK_NO                VARCHAR (20)  := null; --起迄
   p_SK_NO                VARCHAR (20)  := null; --起迄
   p_SK_NOTo              VARCHAR (20)  := null; --起迄
   p_M_Product_Value      VARCHAR (20)  := null; --起迄
   p_M_ProductEnd_Value   VARCHAR (20)  := null;
   p_C_BPartner_Value     VARCHAR (20)  := null; --起迄
   p_C_BPartnerEnd_Value  VARCHAR (20)  := null;
   p_instance_id           NUMERIC(10)  := 0;
  
BEGIN
   IF p_instance is null THEN
      p_instance_id:=0;
   ELSE
      p_instance_id:=p_instance;
   END IF;
   v_message :='程式開始..';

   IF p_instance_id > 0 THEN
      BEGIN
         ResultStr := 'p_instanceNotFound';
         UPDATE adempiere.ad_pinstance
            SET updated      = NOW(),
                isprocessing = 'Y',
                result       = 0,
                errormsg     = ResultStr
          WHERE ad_pinstance_id = p_instance_id;
      EXCEPTION
           WHEN OTHERS THEN NULL;
      END;
   END IF;

   FOR p IN (
       SELECT i.ad_pinstance_id, i.ad_process_id,
              i.record_id,       i.ad_user_id,
              i.ad_client_id,    i.ad_org_id,
              pp.seqno,          pp.parametername,
              pp.p_string,       pp.p_string_to,
              pp.p_number,       pp.p_number_to,
              pp.p_date,         pp.p_date_to,
              pp.info,           pp.info_to
         FROM adempiere.ad_pinstance i
   INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
        WHERE i.ad_pinstance_id=p_instance_id
     ORDER BY pp.seqno
   ) LOOP
      v_message := '讀取傳遞參數表';
      p_Record_ID    :=p.record_id;
      p_AD_Process_ID:=p.ad_process_id;
      p_AD_User_ID   :=p.ad_user_id;
      p_AD_Client_ID :=p.ad_client_id;  
      p_AD_Org_ID    :=p.ad_org_id;
      IF p.parametername = 'DocDate' THEN
         p_DocDate   :=p.p_date;
         p_DocDateTo :=p.p_date_to;
      ELSE IF p.parametername = 'SK_NO' THEN
         p_SK_NO   :=p.p_string;
         p_SK_NOTo :=p.p_string_to;
      END IF;
      END IF;
   END LOOP;

  
   v_message := 'p_instance=1000000';
   IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN

     /* FOR r IN (
          SELECT x.vcount , x.sk_rmjh_id
            FROM (
                  SELECT COUNT(*) AS vcount , h.sk_rmjh_id
                    FROM adempiere.sk_rmjh h
              INNER JOIN adempiere.sk_fgjl l ON h.sk_rmjh_id=l.sk_rmjh_id
                GROUP BY h.sk_rmjh_id
                  ) x
           ORDER BY vcount DESC LIMIT 1
      ) LOOP
         v_message := '如果沒資料抓一筆來測試';
         p_Record_ID := r.sk_rmjh_id;
      END LOOP; */
     -- p_DocDate   := '2013.01.01'::DATE;
     -- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
     -- p_SK_NO     := 'TG-G016';
   END IF;
  
   IF p_DocDateTo IS NULL AND p_DocDate IS NOT NULL THEN
      p_DocDateTo := p_DocDate;
   END IF;
   IF p_SK_NOTo IS NULL AND p_SK_NO IS NOT NULL THEN
      p_SK_NOTo := p_SK_NO;
   END IF;
   -- 1) TABLE
   DELETE FROM adempiere.tg_sk_rmjh_t WHERE ad_pinstance_id = p_instance;
   -- 2)  INSERT INOT TABLE SELECT


  v_SK_NO := null;
  v_message :='xxxx,'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
   ||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
  FOR r IN (
    SELECT
      h.sk_rmjh_id        ,--numeric(10,0) NOT NULL,
      h.ad_client_id      ,--numeric(10,0) NOT NULL,
      h.ad_org_id         ,--numeric(10,0) NOT NULL,
      h.isactive          ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
      h.created           ,--timestamp without time zone NOT NULL DEFAULT now(),
      h.createdby         ,--numeric(10,0) NOT NULL,
      h.updated           ,--timestamp without time zone NOT NULL DEFAULT now(),
      h.updatedby         ,--numeric(10,0) NOT NULL,
      h.sk_no             ,--character varying(40),
      h.docdate           ,--date,
      h.docdateto         ,--date,
      h.sk_rmjh_uu        ,--character varying(36) DEFAULT NULL::character varying,
      h.sk_name           ,--character varying(80),
     --sk_nowqty          ,--numeric(14,2)
    coalesce((select l.sk_nowqty from adempiere.sk_fgjl l
               where l.sk_no=h.sk_no
                 and date_trunc('day',l.DocDate)  < date_trunc('day', p_DocDate) order by l.DocDate desc, sk_fgjl_id desc limit 1),
              coalesce(h.sk_nowqty),0) as sk_nowqty
    FROM adempiere.sk_rmjh h
    WHERE 1=1
       AND   (p_DocDate is null or(
              date_trunc('day',h.DocDate)   <= date_trunc('day',p_DocDate) AND
              date_trunc('day',h.DocDateTo) >= date_trunc('day',p_DocDateTo)))
       AND   (TRIM(p_SK_NO) is null OR TRIM(h.sk_no) BETWEEN TRIM(p_SK_NO) AND TRIM(p_SK_NOTo))
    ORDER BY h.sk_no
    --p_DocDate=2013-01-01 - 2013-06-30 ,p_SK_NO=TG-G016 - TG-G016"
   --AND   (date_trunc('day',h.DocDate)   <= date_trunc('day',timestamp '2013-01-01 00:00:00') AND
   --       date_trunc('day',h.DocDateTo) >= date_trunc('day',timestamp '2013-01-22 00:00:00'))
   --  p_DocDate=2013-01-01 - 2013-10-22 ,p_SK_NO=TG-G016 - TG-G016

  )LOOP

   v_message :='程式完成::'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
   ||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
    --select * from tg_sk_rmjh_t
    IF (v_SK_NO IS NULL OR v_SK_NO <> r.sk_no ) THEN
    -- INSERT INTO
    INSERT INTO adempiere.tg_sk_rmjh_t(
    ad_pinstance_id   ,sk_rmjh_id        ,ad_client_id      ,ad_org_id        ,--numeric(10,0) NOT NULL,
    isactive          ,created           ,createdby         ,updated          ,--timestamp without time zone NOT NULL DEFAULT now(),
    updatedby         ,
    sk_no             ,docdate           ,docdateto         ,sk_rmjh_uu       ,--character varying(36) DEFAULT NULL::character varying,
    sk_name           ,sk_nowqty         ,description      
    ) VALUES (
    p_instance_id     ,r.sk_rmjh_id      ,r.ad_client_id    ,r.ad_org_id      ,--numeric(10,0) NOT NULL,
    r.isactive        ,r.created         ,r.createdby       ,r.updated        ,--timestamp without time zone NOT NULL DEFAULT now(),
    r.updatedby       ,
    r.sk_no           ,p_docdate         ,p_docdateto       ,r.sk_rmjh_uu     ,--character varying(36) DEFAULT NULL::character varying,
    r.sk_name         ,r.sk_nowqty       ,'docdate='||r.docdate||' ,docdateto='||r.docdateto --description
    );
    END IF;
    v_SK_NO := r.sk_no;
  END LOOP;


 --  v_message :='程式完成::'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
 --  ||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
   IF p_instance_id > 0 THEN
     BEGIN
        UPDATE adempiere.ad_pinstance
           SET updated         = NOW(),
               isprocessing    = 'N',
               result          = 1,
               errormsg        = v_message
         WHERE ad_pinstance_id = p_instance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;

  -- 3) RETURN
  RETURN QUERY SELECT *
             FROM adempiere.tg_sk_rmjh_t
            WHERE ad_pinstance_id = p_instance;


   --raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
   v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
   IF p_instance_id > 0 THEN
     BEGIN
        ResultStr := v_message;
        UPDATE adempiere.ad_pinstance
         SET updated      = NOW(),
             isprocessing = 'N',
             result       = 0,
             errormsg     = ResultStr
         WHERE ad_pinstance_id = p_instance_id;
     EXCEPTION
          WHEN OTHERS THEN NULL;
     END;
   END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION adempiere.tg_sk_rmjh_i(numeric)
  OWNER TO adempiere;