iDempiere 客製化 SK_Supp

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


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

CREATE TRIGGER sk_supp_trigger
BEFORE INSERT ON sk_supp
FOR EACH ROW
EXECUTE PROCEDURE sk_supp_trigger_f()

-- DROP TABLE sk_supp
CREATE TABLE sk_supp
(
  sk_supp_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(s_supp) as loc from z_sstock where trim(s_supp) 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_supp(Value,Name) VALUES ( '"+ name + "','"+ name + "' )", A_TrxName);
}
rs.close();
pstmt.close();

result="執行完成"