iDempiere 客製化 SK_Unit

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


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

CREATE TRIGGER sk_unit_trigger
BEFORE INSERT ON sk_unit
FOR EACH ROW
EXECUTE PROCEDURE sk_unit_trigger_f()


-- DROP TABLE sk_unit
CREATE TABLE sk_unit
(
  sk_unit_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_unit) as unit from z_sstock where trim(sk_unit) is not null ";
PreparedStatement pstmt = DB.prepareStatement(sql, A_TrxName);
// pstmt.setInt(1, 2);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
String unit = rs.getString(1);
DB.executeUpdateEx("INSERT INTO SK_Unit(Value,Name) VALUES ( '"+ unit + "','"+ unit + "' )", A_TrxName);
}
rs.close();
pstmt.close();

result="執行完成"