iDempiere 客製化 SK_Kind

set search_path=adempiere
SELECT * FROM adempiere.ad_sequence WHERE name like 'SK_Kind'
SELECT nextid('SK_Kind'::varchar,'N'::char);


CREATE OR REPLACE FUNCTION sk_kind_trigger_f()
RETURNS trigger AS '
BEGIN
  IF NEW.sk_kind_id IS NULL OR NEW.sk_kind_id = 0 THEN
     NEW.sk_kind_id := nextid(''SK_Kind''::varchar,''N''::varchar);
  END IF;
  RETURN NEW;
END'  LANGUAGE 'plpgsql'

CREATE TRIGGER sk_kind_trigger
BEFORE INSERT ON sk_kind
FOR EACH ROW
EXECUTE PROCEDURE sk_kind_trigger_f()

-- DROP TABLE sk_kind
CREATE TABLE sk_kind
(
  sk_kind_id        numeric(10,0) NOT NULL,
  ad_client_id      numeric(10,0) NOT NULL DEFAULT 1000000,
  ad_org_id         numeric(10,0) NOT NULL DEFAULT 0,
  isactive          character(1) NOT NULL DEFAULT 'Y'::bpchar,
  created           timestamp without time zone NOT NULL DEFAULT now(),
  createdby         numeric(10,0) NOT NULL DEFAULT 0,
  updated           timestamp without time zone NOT NULL DEFAULT now(),
  updatedby         numeric(10,0) NOT NULL DEFAULT 0,
  value             character varying(40),
  name              character varying(80)
)

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

String sql = "select distinct trim(sk_kind) as loc from z_sstock where trim(sk_kind) is not null ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, 2);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String name = rs.getString(1);
DB.executeUpdateEx("INSERT INTO sk_kind(Value,Name) VALUES ( '"+ name + "','"+ name + "' )", A_TrxName);
}
rs.close();
pstmt.close();

result="執行完成"