-- Function: adempiere."內外銷出口成品折合原料彙總表_p"(numeric)
-- DROP FUNCTION adempiere."內外銷出口成品折合原料彙總表_p"(numeric);
CREATE OR REPLACE FUNCTION adempiere."內外銷出口成品折合原料彙總表_p"(p_instance numeric)
RETURNS SETOF adempiere."內外銷出口成品折合原料彙總表" AS
$BODY$
/*
科學工業園區 xx科技股份有限公司
xxx年度內外銷出口成品折合原料彙總表
期間：xxx/xx/xx~xxx/xx/xx
原料料號 , 原料名稱/規格/型號 , 單位 , 清冊編號 , 成品料號 , 成品名稱/規格/型號 , 單位 , 成品折合數量 , 使用量 , 原料使用量
CURRENT_LANG = en_US , zh_CN , zh_TW
set search_path=adempiere
select * from adempiere.內外銷出口成品折合原料彙總表_p(1000000);
"程式失敗::值對型別 character varying(20) 而言太長 ,SQLSTATE=22001 ,v_message=開始讀取::BOM"
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)
SELECT * FROM adempiere.tg_fgtransaction
SELECT * FROM adempiere.tg_rmtransaction
*/
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 (40); --起迄
p_SK_NOTo VARCHAR (40); --起迄
p_M_Product_Value VARCHAR (40); --起迄
p_M_ProductEnd_Value VARCHAR (40);
p_C_BPartner_Value VARCHAR (40); --起迄
p_C_BPartnerEnd_Value VARCHAR (40);
p_instance_id NUMERIC (10) := 0;
o_nextid NUMERIC (10) := 0;
o_headid NUMERIC (10) := 0;
v_sk_no VARCHAR (40);
v_BOP NUMERIC (10,4) := 0;
v_AOP NUMERIC (10,4) := 0;
v_OOP NUMERIC (10,4) := 0;
v_QtyCount NUMERIC (10,4) := 0;
v_In_Qty NUMERIC (10,4) := 0;
v_Out_Qty NUMERIC (10,4) := 0;
v_SK_NowQty NUMERIC (10,4) := 0;
v_SK_AfterQty NUMERIC (10,4) := 0;
v_ID NUMERIC (10,0) := 0;
v_UU VARCHAR (36) := 0;
v_H_ID NUMERIC (10,0) := 0;
v_H_UU NUMERIC (10,0) := 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;
IF COALESCE(p_instance,1000000) = 1000000 THEN --OR p_Record_ID = 0 THEN
p_DocDate := '2014.01.01'::DATE;
p_DocDateTo := date_trunc('month',p_DocDate)+'12month'::interval-'1day'::interval;
FOR r IN (
SELECT x.vcount , x.sk_no
FROM (SELECT COUNT(*) AS vcount,
f.sd_skno AS sk_no
--f.sp_date,f.doc_no,f.name,f.value,f.exp_qty,f.bomname,f.bomvalue,f.documentno,f.version,f.sk_bom_id,f.bomqty,f.effectivedate,f.expireddate
FROM adempiere.sk_bom_fg f
GROUP BY f.sd_skno
/* SELECT COUNT(*) AS vcount , b.Value AS sk_no
FROM adempiere.TG_FGTransaction a
INNER JOIN adempiere.M_Product b ON a.M_Product_ID = b.M_Product_ID
WHERE a.MovementDate BETWEEN p_DocDate AND p_DocDateTo
GROUP BY b.Value */
) 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;
v_message := 'p_DocDate:'||p_DocDate||'<->'||p_DocDateTo||' ,p_SK_NO:'||p_SK_NO;
RAISE NOTICE '%', v_message;
p_DocDate := '2014.01.01'::DATE;
p_DocDateTo := date_trunc('month',p_DocDate)+'12month'::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;
--DELETE FROM adempiere.SK_FGJL WHERE (date_trunc('day',DocDate) >= date_trunc('day',p_DocDate) AND
-- date_trunc('day',DocDate) <= date_trunc('day',p_DocDateTo))
-- AND (TRIM(SK_NO) BETWEEN TRIM(p_SK_NO) AND TRIM(p_SK_NOTo));
-- 2) INSERT INOT TABLE SELECT
-- 一成品個料號只取一次
FOR r IN (
-- SELECT sum(x.exp_qty) as exp_qty,x.sd_skno,x.value,x.name,x.effectivedate,x.version,x.sk_bom_id FROM (
SELECT
-- ad_pinstance_id -- numeric(10,0) NOT NULL,
f.sp_date, -- date,
f.doc_no, -- character varying(40),
f.sd_skno, -- character varying(40),
f.name, -- character varying(40),
f.value, -- character varying(40),
f.exp_qty, -- numeric(14,4),
f.bomname, -- character varying(40),
f.bomvalue, -- character varying(40),
f.documentno, -- character varying(40),
f.version,
f.sk_bom_id, -- numeric(10,0),
f.bomqty, -- numeric(14,4),
f.effectivedate, -- date,
f.expireddate -- date,
FROM adempiere.sk_bom_fg f
WHERE 1=1
-- AND f.sp_date BETWEEN p_DocDate AND p_DocDateTo
-- AND f.sd_skno BETWEEN p_SK_NO AND p_SK_NOTo
AND f.isactive = 'Y'
ORDER BY f.bomvalue,f.sp_date,f.sd_skno --用料料號, 銷售日期
-- ) x GROUP BY x.sd_skno,x.value,x.name,x.effectivedate,x.version,x.sk_bom_id
-- ORDER BY x.sd_skno,x.value,x.name,x.effectivedate,x.version,x.sk_bom_id
)LOOP
v_message :='開始讀取::BOM';
SELECT adempiere.nextid(1000195, 'N') INTO v_ID;
SELECT adempiere.generate_uuid() INTO v_UU;
INSERT INTO adempiere."內外銷出口成品折合原料彙總表"(
AD_Pinstance_ID ,-- numeric(10,0),
"內外銷出口成品折合原料彙總表_ID",-- numeric(10,0),
"內外銷出口成品折合原料彙總表_UU",-- varchar(36),
"原料料號", "原料名稱/規格/型號", "原料單位",
"清冊編號",
"成品料號", "成品名稱/規格/型號", "成品單位",
"成品折合數量" ,-- numeric(10,2),
"使用量" ,-- numeric(10,4),
"原料使用量" ,-- numeric(10,2)
AD_Client_ID,AD_Org_ID,IsActive,Created,CreatedBy,Updated,UpdatedBy
)VALUES(p_instance,v_ID,v_UU,
r.value, r.name,'',
r.documentno||r.doc_no,
r.bomValue,r.bomName,'',
r.exp_qty,r.exp_qty,r.bomqty,
p_AD_Client_ID,p_AD_Org_ID,'Y',NOW(),p_AD_User_ID,NOW(),p_AD_User_ID);
--RAISE NOTICE '用料: % ,銷售日期 % ,成品: %', r.bomvalue,r.sp_date,r.sd_skno;
END LOOP;
/*
--SELECT * FROM adempiere.AD_Sequence WHERE Name='SK_BOM_FGHead'--1000195<<請修正>>
SELECT adempiere.nextid(1000195, 'N') INTO v_H_ID;
SELECT adempiere.generate_uuid() INTO v_H_UU;
INSERT INTO SK_BOM_FGHead(
SK_BOM_FGHead_ID ,--numeric(10,0) NOT NULL,
SK_BOM_FGHead_UU ,--character varying(36),
AD_PInstance_ID ,--numeric(10,0) NOT NULL,
DocDate ,--date,
DocDateTo ,--date,
SP_Date ,--date,
Doc_No ,--character varying(40),
SD_SKNo ,--character varying(40),
Name ,--character varying(40),
Value ,--character varying(40),
Exp_Qty ,--numeric(14,4),
Version ,--character varying(40),
SK_BOM_ID ,--numeric(10,0),
EffectiveDate ,--date,
ExpiredDate ,--date,
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
)VALUES(
SK_BOM_FGHead_ID ,--numeric(10,0) NOT NULL,
SK_BOM_FGHead_UU ,--character varying(36),
AD_PInstance_ID ,--numeric(10,0) NOT NULL,
DocDate ,--date,
DocDateTo ,--date,
SP_Date ,--date,
Doc_No ,--character varying(40),
SD_SKNo ,--character varying(40),
Name ,--character varying(40),
Value ,--character varying(40),
Exp_Qty ,--numeric(14,4),
Version ,--character varying(40),
SK_BOM_ID ,--numeric(10,0),
EffectiveDate ,--date,
ExpiredDate ,--date,
p_AD_Client_ID ,--AD_Client_ID ,--numeric(10,0) NOT NULL,
p_AD_Org_ID ,--AD_Org_ID ,--numeric(10,0) NOT NULL,
'Y' ,--IsActive ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
NOW() ,--Created ,--timestamp without time zone NOT NULL DEFAULT now(),
p_AD_User_ID ,--CreatedBy ,--numeric(10,0) NOT NULL,
NOW() ,--Updated ,--timestamp without time zone NOT NULL DEFAULT now(),
p_AD_User_ID );--Updatedby ,--numeric(10,0) NOT NULL
INSERT INTO adempiere.SK_BOM_FG(
SK_BOM_FGHead_ID ,--numeric(10,0) NOT NULL,
AD_PInstance_ID ,--numeric(10,0) NOT NULL,
DocDate ,--date,
DocDateTo ,--date,
SK_BOM_FG_ID ,--numeric(10,0) NOT NULL,
SK_BOM_FG_UU ,--character varying(36),
SP_Date ,--date,
Doc_No ,--character varying(40),
SD_SKNo ,--character varying(40),
Name ,--character varying(40),
Value ,--character varying(40),
Exp_Qty ,--numeric(14,4),
BOMName ,--character varying(40),
BOMValue ,--character varying(40),
DocumentNo ,--character varying(40),
Version ,--character varying(40),
SK_BOM_ID ,--numeric(10,0),
SK_BOMLine_ID ,--numeric(10,0),
BOMQty ,--numeric(14,4),
LineName ,--character varying(40),
LineValue ,--character varying(40),
LineQty ,--numeric(14,4),
EffectiveDate ,--date,
ExpiredDate ,--date,
SK_BOMAlternative_ID ,--numeric(10,0),
AlternativeName ,--character varying(40),
AD_Client_ID ,--numeric(10,0) NOT NULL,
AD_Org_idID ,--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
)VALUES(
v_H_ID ,--SK_BOM_FGHead_ID ,--numeric(10,0) NOT NULL,
v_ID ,--SK_BOM_FG_ID ,--numeric(10,0) NOT NULL,
v_UU ,--SK_BOM_FG_UU ,--character varying(36),
pinstance_id ,--AD_PInstance_ID ,--numeric(10,0) NOT NULL,
DocDate ,--date,
DocDateTo ,--date,
SP_Date ,--date,
Doc_No ,--character varying(40),
SD_SKNo ,--character varying(40),
Name ,--character varying(40),
Value ,--character varying(40),
Exp_Qty ,--numeric(14,4),
BOMName ,--character varying(40),
BOMValue ,--character varying(40),
DocumentNo ,--character varying(40),
Version ,--character varying(40),
SK_BOM_ID ,--numeric(10,0),
SK_BOMLine_ID ,--numeric(10,0),
BOMQty ,--numeric(14,4),
LineName ,--character varying(40),
LineValue ,--character varying(40),
LineQty ,--numeric(14,4),
EffectiveDate ,--date,
ExpiredDate ,--date,
SK_BOMAlternative_ID ,--numeric(10,0),
AlternativeName ,--character varying(40),
p_AD_Client_ID ,--AD_Client_ID ,--numeric(10,0) NOT NULL,
p_AD_Org_ID ,--AD_Org_ID ,--numeric(10,0) NOT NULL,
'Y' ,--IsActive ,--character(1) NOT NULL DEFAULT 'Y'::bpchar,
NOW() ,--Created ,--timestamp without time zone NOT NULL DEFAULT now(),
p_AD_User_ID ,--CreatedBy ,--numeric(10,0) NOT NULL,
NOW() ,--Updated ,--timestamp without time zone NOT NULL DEFAULT now(),
p_AD_User_ID );--Updatedby ,--numeric(10,0) NOT NULL
SELECT * FROM adempiere.sk_bom_fg
DROP TABLE adempiere."內外銷出口成品折合原料彙總表"
CREATE TABLE adempiere."內外銷出口成品折合原料彙總表"(
AD_Pinstance_ID numeric(10,0),
"內外銷出口成品折合原料彙總表_ID" numeric(10,0),
"內外銷出口成品折合原料彙總表_UU" varchar(36),
"原料料號" varchar(40),
"原料名稱/規格/型號" varchar(80),
"原料單位" varchar(10),
"清冊編號" varchar(40),
"成品料號" varchar(40),
"成品名稱/規格/型號" varchar(40),
"成品單位" varchar(10),
"成品折合數量" numeric(10,2),
"使用量" numeric(10,4),
"原料使用量" numeric(10,2),
ad_client_id numeric(10,0),
ad_org_id numeric(10,0),
isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
created timestamp without time zone NOT NULL DEFAULT now(),
createdby numeric(10,0),
updated timestamp without time zone NOT NULL DEFAULT now(),
updatedby numeric(10,0) NOT NULL
)
*/
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.內外銷出口成品折合原料彙總表
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."內外銷出口成品折合原料彙總表_p"(numeric)
OWNER TO adempiere;
沒有留言:
張貼留言