iDempiere 客製化 SK_Locate

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


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

CREATE TRIGGER sk_locate_trigger
BEFORE INSERT ON sk_locate
FOR EACH ROW
EXECUTE PROCEDURE sk_locate_trigger_f()


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

result="執行完成"