根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.
:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構
-- Function: adempiere.tg_sk_fgjh_i1(numeric)
-- DROP FUNCTION adempiere.tg_sk_fgjh_i1(numeric);
CREATE OR REPLACE FUNCTION adempiere.tg_sk_fgjh_i1(p_instance numeric)
RETURNS SETOF adempiere.tg_sk_fgjh_t1 AS
$BODY$
/* 原設計是單頭就是一個月份..現在改成直接抓單身..只要確認最後一次產生的單頭且區間要 >= 列印起訖
CURRENT_LANG = en_US , zh_CN , zh_TW
set search_path=adempiere
select * from adempiere.tg_sk_fgjh_i(1000437);
-- "程式完成:: p_instance=1000000 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=1D-NGF899 - 1D-NGF899"
select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
select * from adempiere.ad_pinstance_para where ad_pinstance_id = 1000000
DROP table adempiere.tg_sk_fgjh_t1 CASCADE
SELECT * FROM adempiere.tg_sk_fgjh_i1(1000000)
*/
DECLARE
p RECORD;
q RECORD;
r RECORD;
v_message VARCHAR (2000);
ResultStr VARCHAR (2000);
p_Record_ID NUMERIC(10) := null;
p_AD_User_ID NUMERIC(10) := null;
p_AD_Process_ID NUMERIC(10) := null;
p_AD_Client_ID NUMERIC(10) := null;
p_AD_Org_ID NUMERIC(10) := null;
p_DocDate DATE;
p_DocDateTo DATE;
p_DocumentNo VARCHAR (200); --起迄
p_DocumentNoTo VARCHAR (200);
p_DateOrdered DATE; --起迄
p_DateOrderedTo DATE;
p_M_Product_ID NUMERIC(10) := 0; --起迄
p_M_ProductTo_ID NUMERIC(10) := 0;
p_C_BPartner_ID NUMERIC(10) := 0; --起迄
p_C_BPartnerTo_ID NUMERIC(10) := 0;
p_SK_NO VARCHAR (20); --起迄
p_SK_NOTo VARCHAR (20); --起迄
p_M_Product_Value VARCHAR (20); --起迄
p_M_ProductEnd_Value VARCHAR (20);
p_C_BPartner_Value VARCHAR (20); --起迄
p_C_BPartnerEnd_Value VARCHAR (20);
p_instance_id NUMERIC(10) := 0;
v_sk_no VARCHAR (20);
BEGIN
IF p_instance is null THEN
p_instance_id:=0;
ELSE
p_instance_id:=p_instance;
END IF;
v_message :='程式開始..';
IF p_instance_id > 0 THEN
BEGIN
ResultStr := 'p_instanceNotFound';
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'Y',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = p_instance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
FOR p IN (
SELECT i.ad_pinstance_id, i.ad_process_id,
i.record_id, i.ad_user_id,
i.ad_client_id, i.ad_org_id,
pp.seqno, pp.parametername,
pp.p_string, pp.p_string_to,
pp.p_number, pp.p_number_to,
pp.p_date, pp.p_date_to,
pp.info, pp.info_to
FROM adempiere.ad_pinstance i
INNER JOIN adempiere.ad_pinstance_para pp ON pp.ad_pinstance_id=i.ad_pinstance_id
WHERE i.ad_pinstance_id=p_instance_id
ORDER BY pp.seqno
) LOOP
v_message := '讀取傳遞參數表';
p_Record_ID :=p.record_id;
p_AD_Process_ID:=p.ad_process_id;
p_AD_User_ID :=p.ad_user_id;
p_AD_Client_ID :=p.ad_client_id;
p_AD_Org_ID :=p.ad_org_id;
IF p.parametername = 'DocDate' THEN
p_DocDate :=p.p_date;
p_DocDateTo :=p.p_date_to;
ELSE IF p.parametername = 'SK_NO' THEN
p_SK_NO :=p.p_string;
p_SK_NOTo :=p.p_string_to;
END IF;
END IF;
END LOOP;
IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN
p_DocDate := '2013.01.01'::DATE;
p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
FOR r IN (
--select * from adempiere.sk_fgjh
SELECT x.vcount , x.sk_no
FROM (
SELECT COUNT(*) AS vcount , h.sk_no
FROM adempiere.sk_fgjh h
INNER JOIN adempiere.sk_fgjl l ON h.sk_fgjh_id=l.sk_fgjh_id
WHERE l.docdate BETWEEN p_DocDate AND p_DocDateTo
GROUP BY h.sk_no
) x
ORDER BY vcount DESC LIMIT 1
) LOOP
-- v_message := '如果沒資料抓一筆來測試';
-- p_Record_ID := r.sk_fgjh_id;
p_SK_NO := r.sk_no;
p_SK_NOTo := r.sk_no;
END LOOP;
-- p_DocDate := '2013.01.01'::DATE;
-- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
-- p_SK_NO := '2Z-F8050-B070-1';
-- p_SK_NOTo := '2Z-F8050-B070-1';
END IF;
IF p_DocDateTo IS NULL AND p_DocDate IS NOT NULL THEN
p_DocDateTo := p_DocDate;
END IF;
IF p_SK_NOTo IS NULL AND p_SK_NO IS NOT NULL THEN
p_SK_NOTo := p_SK_NO;
END IF;
-- 1) TABLE
-- alter table adempiere.tg_sk_fgjh_t1 add docdateto date
DELETE FROM adempiere.tg_sk_fgjh_t1 WHERE ad_pinstance_id = p_instance;
v_sk_no := '';
-- 2) INSERT INOT TABLE SELECT
FOR r IN (
SELECT
h.sk_fgjh_id ,--numeric(10,0) NOT NULL,
h.ad_client_id ,--numeric(10,0) NOT NULL,
h.ad_org_id ,--numeric(10,0) NOT NULL,
h.isactive ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
h.created ,--timestamp without time zone NOT NULL DEFAULT now(),
h.createdby ,--numeric(10,0) NOT NULL,
h.updated ,--timestamp without time zone NOT NULL DEFAULT now(),
h.updatedby ,--numeric(10,0) NOT NULL,
h.sk_no ,--character varying(40),
h.docdate ,--date,
h.docdateto ,--date,
h.sk_fgjh_uu ,--character varying(36) DEFAULT NULL::character varying,
h.sk_name ,--character varying(80),
-- h.sk_nowqty --numeric(14,2)
coalesce((select l.sk_nowqty from adempiere.sk_fgjl l where l.docdate <= p_DocDate --'2013-01-01'
and l.sk_fgjh_id=h.sk_fgjh_id order by l.docdate desc limit 1),coalesce(h.sk_no,0)) as sk_nowqty
FROM adempiere.sk_fgjh h
WHERE 1=1
AND h.docdate <= p_DocDate --'2013-01-01' --
AND h.docdateto >= p_DocDateTo --'2013-01-31' --
AND h.sk_no BETWEEN p_SK_NO AND p_SK_NOTo -- p_SK_NO := '2Z-F8050-B070-1';
-- AND h.sk_no='1D-NGF899'
ORDER BY h.sk_no, h.sk_fgjh_id desc
--p_DocDate=2013-01-01 - 2013-01-31 ,p_SK_NO=1D-NGF899 - 1D-NGF899"
)LOOP
-- 一個料號只取一次
IF v_sk_no <> r.sk_no THEN
-- INSERT INTO
INSERT INTO adempiere.tg_sk_fgjh_t1(
ad_pinstance_id ,--numeric(10,0) NOT NULL,
sk_fgjh_id ,--numeric(10,0) NOT NULL,
ad_client_id ,--numeric(10,0) NOT NULL,
ad_org_id ,--numeric(10,0) NOT NULL,
isactive ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
created ,--timestamp without time zone NOT NULL DEFAULT now(),
createdby ,--numeric(10,0) NOT NULL,
updated ,--timestamp without time zone NOT NULL DEFAULT now(),
updatedby ,--numeric(10,0) NOT NULL,
sk_no ,--character varying(40),
docdate ,--date,
docdateto ,--date,
sk_fgjh_uu ,--character varying(36) DEFAULT NULL::character varying,
sk_name ,--character varying(80),
sk_nowqty --numeric(14,2)
) VALUES (
p_instance_id ,--numeric(10,0) NOT NULL,
r.sk_fgjh_id ,--numeric(10,0) NOT NULL,
r.ad_client_id ,--numeric(10,0) NOT NULL,
r.ad_org_id ,--numeric(10,0) NOT NULL,
r.isactive ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
r.created ,--timestamp without time zone NOT NULL DEFAULT now(),
r.createdby ,--numeric(10,0) NOT NULL,
r.updated ,--timestamp without time zone NOT NULL DEFAULT now(),
r.updatedby ,--numeric(10,0) NOT NULL,
r.sk_no ,--character varying(40),
p_DocDate ,--date,
p_DocDateTo ,--date,
r.sk_fgjh_uu ,--character varying(36) DEFAULT NULL::character varying,
r.sk_name ,--character varying(80),
r.sk_nowqty --numeric(14,2)
);
v_sk_no:=r.sk_no;
END IF;
END LOOP;
v_message :='程式完成::'||' p_instance='||p_instance||' ,p_Record_ID='||p_Record_ID
||' ,p_DocDate='||p_DocDate||' - '||p_DocDateTo||' ,p_SK_NO='||p_SK_NO||' - '||p_SK_NOTo;
IF p_instance_id > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE ad_pinstance_id = p_instance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
-- 3) RETURN
RETURN QUERY SELECT *
FROM adempiere.tg_sk_fgjh_t1
WHERE ad_pinstance_id = p_instance;
--raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
IF p_instance_id > 0 THEN
BEGIN
ResultStr := v_message;
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = p_instance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION adempiere.tg_sk_fgjh_i1(numeric)
OWNER TO adempiere;
沒有留言:
張貼留言