2013年8月7日 星期三

iDempiere 客製化 SK_IKind


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


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

CREATE TRIGGER sk_ikind_trigger
BEFORE INSERT ON sk_ikind
FOR EACH ROW
EXECUTE PROCEDURE sk_ikind_trigger_f()

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

result="執行完成"


沒有留言:

張貼留言