iDempiere ERP BPR 重新建構系統流程

-- Function: adempiere.ly_so_gen_shipment_p(integer)

-- DROP FUNCTION adempiere.ly_so_gen_shipment_p(integer);

CREATE OR REPLACE FUNCTION adempiere.ly_so_gen_shipment_p(p_instance integer)
  RETURNS void AS
$BODY$
/*
set search_path=adempiere
"程式失敗::=函式 uuid_generate_v4() 不存在 ,SQLSTATE=42883 ,v_message=Before Read ::訂單 p_Record_ID=1000002"
"程式成功::p_Record_ID=1000002,p_客戶_id=0,p_訂單單號=,p_產品_id=0,p_客戶單號="
select max(ad_pinstance_id) from adempiere.ad_pinstance  -- 1000788
"程式成功::p_Record_ID=1000002,p_客戶_id=1000002,p_訂單單號=,p_產品_id=0,p_客戶單號="
select * from adempiere.ad_pinstance where ad_pinstance_id = 1000788
SELECT * FROM adempiere.ly_so_gen_shipment_p(1000788::integer)
DELETE   FROM adempiere.訂單產生出貨單明細
SELECT * FROM adempiere.訂單產生出貨單明細
*/
DECLARE

   p RECORD;
   q RECORD;
   r RECORD;
   s RECORD;
   v_message              VARCHAR (2000);
   ResultStr              VARCHAR (2000);
   v_Next_ID              NUMERIC(10)   := null;
   v_Next_UU              VARCHAR (36)  := null;
   v_NextLine_ID          NUMERIC(10)   := null;
   v_NextLine_UU          VARCHAR (36)  := null;
  
   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_客戶_id              NUMERIC(10)   := null;
   p_訂單單號             VARCHAR (200); --起迄
   p_產品_id              NUMERIC(10)   := null;
   p_客戶單號             VARCHAR (200); --起迄
   p_未結案               CHAR (1);
   p_instance_id          NUMERIC(10)   := 0;
   v_sk_no                VARCHAR (20);
BEGIN
   IF p_instance is null THEN
      p_instance_id:=1000000;
   ELSE
      p_instance_id:=p_instance;
   END IF;
  
   v_message :='程式開始..p_instance_id='||p_instance_id;

   BEGIN
         ResultStr := 'p_instanceNotFound'||v_message;
         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;

   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
   LEFT JOIN adempiere.ad_pinstance_para pp ON i.ad_pinstance_id=pp.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;
   --   END IF;
   END LOOP;

    v_message :='程式開始..p_Record_ID='||p_Record_ID;

   IF COALESCE(p_instance_id,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN
   --  p_DocDate   := '2013.01.01'::DATE;
   --  p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
      FOR r IN (
          SELECT MAX("訂單產生出貨單_ID") Record_ID FROM adempiere."訂單產生出貨單"
      ) LOOP
           v_message   := '如果沒資料抓一筆來測試';
           p_Record_ID := r.Record_ID;
      END LOOP;
   END IF;--IF COALESCE(p_instance_id,1000000)


   p_客戶_id   :=0;
   p_訂單單號  :='';
   p_產品_id   :=0;
   p_客戶單號  :='';
   p_未結案    :='Y';
   v_message   := 'Before Read::訂單產生出貨單';


   FOR r IN (
          SELECT "客戶_id","訂單單號","產品_id","客戶單號","訂單產生出貨單_id","未結案"
            FROM adempiere."訂單產生出貨單"
           WHERE "訂單產生出貨單_id"=p_Record_ID
   )LOOP
           p_客戶_id   := r."客戶_id";
           p_訂單單號  := r."訂單單號";
           p_產品_id   := r."產品_id";
           p_客戶單號  := r."客戶單號";
           p_未結案    := r."未結案";
   END LOOP;

   v_message   := 'Before DELETE::訂單產生出貨單明細 p_Record_ID='||p_Record_ID;
   DELETE FROM adempiere."訂單產生出貨單明細" WHERE "訂單產生出貨單_id" = p_Record_ID;
   DELETE FROM adempiere."出貨單底稿"         WHERE "訂單產生出貨單_id" = p_Record_ID;
   DELETE FROM adempiere."製程完工底稿"       WHERE "訂單產生出貨單_id" = p_Record_ID;
  
   v_message   := 'Before Read ::訂單 p_Record_ID='||p_Record_ID;

   FOR r IN (
       SELECT t."客戶_id",t."訂單單號",t."產品_id",t."客戶單號", t."訂單_id",
              c."編號"||'_'||c."簡稱" AS "客戶名稱"
       FROM adempiere."訂單" t
 INNER JOIN adempiere."客戶" c ON t."客戶_id"=c."客戶_id"
      WHERE 1=1  --AND t."客戶_id"=1000002
        AND (COALESCE(p_客戶_id,  0  )=0   OR COALESCE(p_客戶_id,   0) = COALESCE(t."客戶_id" ,  0))
        AND (COALESCE(p_訂單單號, '' )=''  OR COALESCE(p_訂單單號, '') = COALESCE(t."訂單單號", ''))
        AND (COALESCE(p_產品_id,  0  )=0   OR COALESCE(p_產品_id,   0) = COALESCE(t."產品_id" ,  0))
        AND (COALESCE(p_客戶單號, '' )=''  OR COALESCE(p_客戶單號,'' ) = COALESCE(t."客戶單號",'' ))
        AND (COALESCE(p_未結案,   'N')='N' OR COALESCE(p_未結案,  'N') = COALESCE(t."未結案"  ,'N'))
  )LOOP
       --select ad_sequence_id from adempiere.ad_sequence where name = '訂單產生出貨單明細'
       SELECT adempiere.nextid(1000048::Integer, 'N'::Varchar) INTO v_Next_ID;
       SELECT adempiere.generate_uuid() INTO v_Next_UU;

       INSERT INTO adempiere."訂單產生出貨單明細"(
       "訂單產生出貨單_id", "訂單產生出貨單明細_id", "訂單產生出貨單明細_uu",
       "客戶_id",  "客戶名稱",  "訂單單號", "訂單_id", "產品_id", "客戶單號",
       ad_pinstance_id,
       ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby
       ) VALUES (
       p_Record_ID,   v_Next_ID,     v_Next_UU,
       r."客戶_id",   r."客戶名稱",  r."訂單單號",  r."訂單_id", r."產品_id",  r."客戶單號", 
       p_instance_id,
       p_AD_Client_ID, p_AD_Client_ID,'Y', NOW(), p_AD_User_ID, NOW(), p_AD_User_ID
       ); 
       --select ad_sequence_id from adempiere.ad_sequence where name = '出貨單底稿' --1000056
       SELECT adempiere.nextid(1000056::Integer, 'N'::Varchar) INTO v_NextLine_ID;
       SELECT adempiere.generate_uuid() INTO v_NextLine_UU;
       INSERT INTO adempiere."出貨單底稿"(
       "訂單產生出貨單_id", "訂單產生出貨單明細_id", "出貨單底稿_id", "出貨單底稿_uu",
       "訂單_id", "客戶_id", "產品_id", "應收帳款月份", "出貨單號","出貨日期","重量kg","單重g","數量pcs","桶型_id","桶號","批號","備註",
       "進貨退回_id", "桶數",
       ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby
       ) VALUES (
       p_Record_ID, v_Next_ID, v_NextLine_ID, v_NextLine_UU,
       r."訂單_id",
       r."客戶_id",
       r."產品_id",
       '',    --"應收帳款月份",
       '',    --"出貨單號",
       now(), --"出貨日期",
       0 , --"重量kg",
       0 , --"單重g",
       0 , --"數量pcs",
       0 , --"桶型_id",
       '', --"桶號",
       '', --"批號",
       '', --"備註",
       0 , --"進貨退回_id",
       0 , --"桶數",
       p_AD_Client_ID,p_AD_Client_ID,'Y', NOW(), p_AD_User_ID, NOW(), p_AD_User_ID
       );

   FOR s IN (
     SELECT a."訂單_id", a."產品_id", a."製程_id", a."單價",   a."備註",    a."訂單製程_id",
            a."客戶名稱",a."訂單單號",a."順序",    a."廠商_id",a."應付單價",a."應收單價",    a."派工數量",
            b."名稱" AS "製程名稱"
       FROM adempiere."訂單製程" a
 INNER JOIN adempiere."製程" b ON a."製程_id"=b."製程_id"
        WHERE a."訂單_id"=r."訂單_id"
        ORDER BY a."順序"
  )LOOP
       --select ad_sequence_id from adempiere.ad_sequence where name = '製程完工底稿'--1000058
       SELECT adempiere.nextid(1000058::Integer, 'N'::Varchar) INTO v_NextLine_ID;
       SELECT adempiere.generate_uuid() INTO v_NextLine_UU;
 ---訂單產生出貨單_ID=1000002
       INSERT INTO adempiere."製程完工底稿"(
       "訂單產生出貨單_id", "訂單產生出貨單明細_id", "製程完工底稿_id", "製程完工底稿_uu",
       "訂單_id",  "廠商_id", "產品_id", "製程_id", "製程名稱",    "應付帳款月份",
       "出貨單號", "出貨日期","重量kg",  "單重g",   "數量pcs",
       "桶型_id",  "桶號",    "批號",    "備註",    "進貨退回_id", "桶數",
       ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby
       ) VALUES (
       p_Record_ID, v_Next_ID, v_NextLine_ID, v_NextLine_UU,
       r."訂單_id",
       s."廠商_id",
       r."產品_id",
       s."製程_id" ,
       s."製程名稱",
       '',   --"應付帳款月份",
       '',   --"出貨單號",
       now(),--"出貨日期",
       0,    --"重量kg", 
       0,    --"單重g",  
       0,    --"數量pcs",
       0,    --"桶型_id", 
       '',   --"桶號",   
       '',   --"批號",   
       '',   --"備註",
       0,    --"進貨退回_id",
       0,    --"桶數",
       p_AD_Client_ID, p_AD_Client_ID, 'Y', NOW(), p_AD_User_ID, NOW(), p_AD_User_ID
       ); 
  END LOOP;

  END LOOP;

        v_message :='程式成功::p_Record_ID='||COALESCE(p_Record_ID,0)
        ||',p_客戶_id=' ||COALESCE(p_客戶_id,  0)
        ||',p_訂單單號='||COALESCE(p_訂單單號,'')
        ||',p_產品_id=' ||COALESCE(p_產品_id,  0)
        ||',p_客戶單號='||COALESCE(p_客戶單號,'')
        ||',p_未結案='  ||COALESCE(p_未結案,'N');
       
        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
        v_message :='程式失敗::=p_Record_ID='||p_Record_ID||',SQLERRM='||SQLERRM ||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
        ResultStr := v_message;
        UPDATE adempiere.ad_pinstance
           SET updated         = NOW(),
               isprocessing    = 'N',
               result          = 0,
               errormsg        = ResultStr
         WHERE ad_pinstance_id = p_instance_id;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION adempiere.ly_so_gen_shipment_p(integer)
  OWNER TO postgres;

iDempiere 醫療系統版本更替 注意事項

00:40:54.505===========> Query.firstOnly: SELECT AD_Preference_ID,Attribute,AD_P
reference_UU,Value,CreatedBy,UpdatedBy,IsActive,AD_Client_ID,AD_Org_ID,AD_User_I
D,AD_Window_ID,Created,Updated FROM AD_Preference WHERE (NVL(AD_User_ID,0) = ? AND Attribute = ? AND AD_Window_ID Is NULL AND AD_Process_ID IS NULL AND PreferenceFor = 'W') [83]
org.postgresql.util.PSQLException: 錯誤: 欄位"ad_process_id"不存在
  Position: 252; State=42703; ErrorCode=0



 set search_path=adempiere

alter table adempiere.AD_Preference? AND AD_Window_ID Is NULL AND
add AD_Process_ID numeric(10,0)

alter table adempiere.AD_Preference
add PreferenceFor char(1)

iDempiere groovy metadata arrary

import groovy.sql.Sql
import java.sql.Timestamp
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.Types
import org.compiere.util.Env
import org.compiere.util.DB

int client=Env.getContextAsInt(A_Ctx, "AD_Client_ID");
int org=Env.getContextAsInt(A_Ctx, "AD_Org_ID");
int uid=Env.getContextAsInt(A_Ctx, "#AD_User_ID");

def t_start= System.currentTimeMillis()

// def oracle = Sql.newInstance('jdbc:oracle:thin:@127.0.0.1:1521:orcl','c##ad',,'c##ad','oracle.jdbc.driver.OracleDriver')

def postsql = Sql.newInstance('jdbc:postgresql://127.0.0.1/daily','adempiere','adempiere','org.postgresql.Driver' )

// def ptable= postsql.dataSet("h_searchline")   //上傳

// def informix = Sql.newInstance( 'jdbc:informix-sqli://192.168.170.211:1534/hisopddb:informixserver=hisopdsrv;CLIENT_LOCALE=zh_tw.big5;','informix','db@w94u6', 'com.informix.jdbc.IfxDriver' );
// def metadata = informix.connection.metaData

def metadata = postsql.connection.metaData

String catalog = null;
String schemaPattern = "adempiere";
String tableNamePattern = "ad_table%"
String[] tableTypes = ["TABLE"]
String columnNamePattern = null;
ResultSet rs = metadata.getTables(catalog , schemaPattern, tableNamePattern, tableTypes );
// "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
String tblName = null;
println   "============================== \t";
rs = metadata.getTables(catalog , schemaPattern, tableNamePattern, tableTypes );
while(rs.next()){
   tblName = rs.getString('TABLE_NAME');
   println ">>>>>> "+tblName
   def colList = [][]
   ResultSet tblMetadata = metadata.getColumns(catalog, schemaPattern, tblName, columnNamePattern );
   while(tblMetadata.next()){
      String  TABLE_CAT      = tblMetadata.getString("TABLE_CAT");
      String TABLE_SCHEM  = tblMetadata.getString("TABLE_SCHEM");
      String TABLE_NAME    = tblMetadata.getString("TABLE_NAME");
      String COLUMN_NAME= tblMetadata.getString("COLUMN_NAME");
      int       DATA_TYPE      = tblMetadata.getInt("DATA_TYPE");
      String TYPE_NAME      = tblMetadata.getString ("TYPE_NAME");
      int       COLUMN_SIZE  = tblMetadata.getInt("COLUMN_SIZE");
      int       DECIMAL_DIGITS  = tblMetadata.getInt("DECIMAL_DIGITS");
      int       NULLABLE             = tblMetadata.getInt("NULLABLE");
      String str = "";
 if         (TYPE_NAME=='decimal')    str = COLUMN_NAME  + " DECIMAL   (" + COLUMN_SIZE+ ", " + DECIMAL_DIGITS +")";
 else if (TYPE_NAME=='char')         str =  COLUMN_NAME + " CHAR        (" + COLUMN_SIZE+ ")"; // informix
 else if (TYPE_NAME=='varchar')    str =  COLUMN_NAME + " VARCHAR  (" + COLUMN_SIZE+ ")"; // postgres
 else if (TYPE_NAME=='varying character')    str =  COLUMN_NAME + " VARCHAR  (" + COLUMN_SIZE+ ")"; // postgres
 else if (TYPE_NAME=='bpchar')     str =  COLUMN_NAME + " CHAR        (" + COLUMN_SIZE+ ")"; // postgres
 else if (TYPE_NAME=='numeric')    str =  COLUMN_NAME + " NUMERIC  (" + COLUMN_SIZE+ ")"; // postgres
 else if (TYPE_NAME=='timestamp')  str = COLUMN_NAME + " DATE";  // postgres
 else if (TYPE_NAME=='datetime')    str = COLUMN_NAME + "  DATE";
 else if (TYPE_NAME=='date')          str = COLUMN_NAME  + " DATE";
 else                                                 str = COLUMN_NAME  + "   " + TYPE_NAME + " ";
 // colList .add( [str] )
    colList .add( [ COLUMN_NAME, TYPE_NAME,  " "+COLUMN_SIZE, " "+DECIMAL_DIGITS ] )
   } // while(tblMetadata.next())

   (0.. (colList.size() -1) ).each { i ->
     //   println  colList.get(i)
        println  colList [i][0]+" "+colList [i][1]+" "+colList [i][2]+" "+colList [i][3]
   }

} // while(rs.next())
def t_end = System.currentTimeMillis()
result="執行完成: " + (t_end - t_start)

iDempiere FindWindow loadLookupNoValidate

            理論上有解除
            實際上沒解除
            原因是:::::

            //reload lookupinfo for find window
            if (DisplayType.isLookup(findField.getDisplayType()) )
            {               
                findField.loadLookupNoValidate();               
                editor = WebEditorFactory.getEditor(findField, true);
                findField.addPropertyChangeListener(editor);
            }
            else
            {
                editor = WebEditorFactory.getEditor(findField, true);
                findField.addPropertyChangeListener(editor);
            }

iDempiere 不是要搜尋的 [搜尋畫面] 的變數內容去 Validate 下拉內容!!!!!!!!!!!!

FindWindow.java
假如是  [DisplayType.isLookup]  Table/TableDir/Search
假如是  loadLookupNoValidate 應該不會去 Validate 但是還是去 Validate
而且還是使用 [輸入畫面] 的變數內容
不是要搜尋的 [搜尋畫面] 的變數內容去 Validate 下拉內容!!!!!!!!!!!!
=======================================================

            //reload lookupinfo for find window
            if (DisplayType.isLookup(findField.getDisplayType()) )
            {               
                findField.loadLookupNoValidate();               
                editor = WebEditorFactory.getEditor(findField, true);
                findField.addPropertyChangeListener(editor);

iDempiere if (isSearchLike(field)) 中文自動加上前後 %

                if (isSearchLike(field))
                {
                    StringBuilder valueStr = new StringBuilder(value.toString().toUpperCase());
                    if (!valueStr.toString().endsWith("%"))
                        valueStr.append("%");
                    if (!valueStr.toString().startsWith("%"))
                        valueStr = new StringBuilder("%").append(valueStr.toString());


    ===假如是  [搜尋欄位]  文字形式+建議搜尋欄位
    private boolean isSearchLike(GridField field)
    {
        return DisplayType.isText(field.getDisplayType())
        && MColumn.isSuggestSelectionColumn(field.getColumnName(), true);
    }
    ===假如是  [建議搜尋欄位] 自動加上 %
    public static boolean isSuggestSelectionColumn(String columnName, boolean caseSensitive)
    {
        if (Util.isEmpty(columnName, true))
            return false;
        //
        if (columnName.contains("名稱") || columnName.contains("單號")
         || columnName.contains("品號") || columnName.contains("品名")
         || columnName.contains("規格") || columnName.contains("單號"))
            return true;

iDempiere @@「採購品」畫面的「廠商」只要帶出「一般廠商」就好了!! 表和列: 採購品 動態驗證 廠商類別為一般廠商

iDempiere
@@「採購品」畫面的「廠商」只要帶出「一般廠商」就好了!!
表和列: 採購品  動態驗證  廠商類別為一般廠商