-- Function: adempiere.tg_sk_rmjh_i(numeric)
-- DROP FUNCTION adempiere.tg_sk_rmjh_i(numeric);
CREATE OR REPLACE FUNCTION adempiere.tg_sk_rmjh_i(p_instance numeric)
RETURNS SETOF adempiere.tg_sk_rmjh_t AS
$BODY$
/* DEBUG AND CREATE TABLE
CURRENT_LANG = en_US , zh_CN , zh_TW
set search_path=adempiere
"程式完成:: p_instance=1000545 ,p_Record_ID=0 ,p_DocDate=2013-01-01 - 2013-06-30 ,p_SK_NO=TG-G016 - TG-G016"
select p.Value,Name,pi.* from ad_pinstance pi inner join ad_process p on p.ad_process_id=pi.ad_process_id order by ad_pinstance_id desc
*/
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;
v_SK_NO VARCHAR (20) := null; --起迄
p_SK_NO VARCHAR (20) := null; --起迄
p_SK_NOTo VARCHAR (20) := null; --起迄
p_M_Product_Value VARCHAR (20) := null; --起迄
p_M_ProductEnd_Value VARCHAR (20) := null;
p_C_BPartner_Value VARCHAR (20) := null; --起迄
p_C_BPartnerEnd_Value VARCHAR (20) := null;
p_instance_id NUMERIC(10) := 0;
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;
v_message := 'p_instance=1000000';
IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN
/* FOR r IN (
SELECT x.vcount , x.sk_rmjh_id
FROM (
SELECT COUNT(*) AS vcount , h.sk_rmjh_id
FROM adempiere.sk_rmjh h
INNER JOIN adempiere.sk_fgjl l ON h.sk_rmjh_id=l.sk_rmjh_id
GROUP BY h.sk_rmjh_id
) x
ORDER BY vcount DESC LIMIT 1
) LOOP
v_message := '如果沒資料抓一筆來測試';
p_Record_ID := r.sk_rmjh_id;
END LOOP; */
-- p_DocDate := '2013.01.01'::DATE;
-- p_DocDateTo := date_trunc('month',p_DocDate)+'1month'::interval-'1day'::interval;
-- p_SK_NO := 'TG-G016';
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
DELETE FROM adempiere.tg_sk_rmjh_t WHERE ad_pinstance_id = p_instance;
-- 2) INSERT INOT TABLE SELECT
v_SK_NO := null;
v_message :='xxxx,'||' 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;
FOR r IN (
SELECT
h.sk_rmjh_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_rmjh_uu ,--character varying(36) DEFAULT NULL::character varying,
h.sk_name ,--character varying(80),
--sk_nowqty ,--numeric(14,2)
coalesce((select l.sk_nowqty from adempiere.sk_fgjl l
where l.sk_no=h.sk_no
and date_trunc('day',l.DocDate) < date_trunc('day', p_DocDate) order by l.DocDate desc, sk_fgjl_id desc limit 1),
coalesce(h.sk_nowqty),0) as sk_nowqty
FROM adempiere.sk_rmjh h
WHERE 1=1
AND (p_DocDate is null or(
date_trunc('day',h.DocDate) <= date_trunc('day',p_DocDate) AND
date_trunc('day',h.DocDateTo) >= date_trunc('day',p_DocDateTo)))
AND (TRIM(p_SK_NO) is null OR TRIM(h.sk_no) BETWEEN TRIM(p_SK_NO) AND TRIM(p_SK_NOTo))
ORDER BY h.sk_no
--p_DocDate=2013-01-01 - 2013-06-30 ,p_SK_NO=TG-G016 - TG-G016"
--AND (date_trunc('day',h.DocDate) <= date_trunc('day',timestamp '2013-01-01 00:00:00') AND
-- date_trunc('day',h.DocDateTo) >= date_trunc('day',timestamp '2013-01-22 00:00:00'))
-- p_DocDate=2013-01-01 - 2013-10-22 ,p_SK_NO=TG-G016 - TG-G016
)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;
--select * from tg_sk_rmjh_t
IF (v_SK_NO IS NULL OR v_SK_NO <> r.sk_no ) THEN
-- INSERT INTO
INSERT INTO adempiere.tg_sk_rmjh_t(
ad_pinstance_id ,sk_rmjh_id ,ad_client_id ,ad_org_id ,--numeric(10,0) NOT NULL,
isactive ,created ,createdby ,updated ,--timestamp without time zone NOT NULL DEFAULT now(),
updatedby ,
sk_no ,docdate ,docdateto ,sk_rmjh_uu ,--character varying(36) DEFAULT NULL::character varying,
sk_name ,sk_nowqty ,description
) VALUES (
p_instance_id ,r.sk_rmjh_id ,r.ad_client_id ,r.ad_org_id ,--numeric(10,0) NOT NULL,
r.isactive ,r.created ,r.createdby ,r.updated ,--timestamp without time zone NOT NULL DEFAULT now(),
r.updatedby ,
r.sk_no ,p_docdate ,p_docdateto ,r.sk_rmjh_uu ,--character varying(36) DEFAULT NULL::character varying,
r.sk_name ,r.sk_nowqty ,'docdate='||r.docdate||' ,docdateto='||r.docdateto --description
);
END IF;
v_SK_NO := r.sk_no;
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_rmjh_t
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_rmjh_i(numeric)
OWNER TO adempiere;
沒有留言:
張貼留言