-- Function: adempiere.sk_gen_rmbom(numeric)
-- DROP FUNCTION adempiere.sk_gen_rmbom(numeric);
CREATE OR REPLACE FUNCTION adempiere.sk_gen_rmbom(pinstance numeric)
RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_FGJH
-- ALTER COLUMN sk_name TYPE varchar(80)
-- set search_path=adempiere
-- DELETE FROM adempiere.sk_gen_fgbom_log
-- DELETE FROM adempiere.SK_BOM_RM;
-- select * FROM adempiere.SK_BOM_RM;
-- select * from adempiere.sk_gen_rmbom_log
-- select adempiere.sk_gen_rmbom(1000000);
-- select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
/*
select a.sd_skno, a.bomname, a.bomvalue, a.version, a.exp_qty, b.linename, b.linevalue, b.lineqty, a.exp_qty *b.lineqty --, b.bom_qty
from
(select sd_skno, bomname, bomvalue, version, sum(exp_qty) as exp_qty
from adempiere.sk_bom_rm
group by sd_skno, bomname, bomvalue, version) a
--order by sd_skno, bomname, bomvalue, version
inner join
(select sd_skno, bomname, bomvalue, version,linename, linevalue, lineqty --, sum(exp_qty*lineqty) as bom_qty
from adempiere.sk_bom_fg
group by sd_skno, bomname, bomvalue, version,linename, linevalue, lineqty) b on a.sd_skno=b.sd_skno and a.bomname=b.bomname and a.bomvalue=b.bomvalue and a.version=b.version
order by a.sd_skno, a.bomname, a.bomvalue, a.version, b.linename, b.linevalue, b.lineqty
*/
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
ResultStr VARCHAR (200);
roleaccesslevelwin VARCHAR (200);
sqlins VARCHAR (4000);
sqlupd VARCHAR (4000);
sqlwindowaccess VARCHAR (4000);
p RECORD;
q RECORD;
r RECORD;
inv RECORD;
o_nextid NUMERIC(10) := 0;
o_nextheadid NUMERIC(10) := 0;
o_nextlineid NUMERIC(10) := 0;
p_Record_ID NUMERIC(10) := 0;
p_AD_User_ID NUMERIC(10) := 0;
p_AD_Process_ID NUMERIC(10) := 0;
p_AD_Client_ID NUMERIC(10) := 0;
p_AD_Org_ID NUMERIC(10) := 0;
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_M_Product_Value VARCHAR (200); --起迄
p_M_ProductEnd_Value VARCHAR (200);
p_C_BPartner_Value VARCHAR (200);--起迄
p_C_BPartnerEnd_Value VARCHAR (200);
v_message VARCHAR(400) := '';
v_NextNo NUMERIC(10) := 0;
p_IsAllActive VARCHAR(1) := 'Y'; --IsAllActive
p_AD_Tab_ID NUMERIC(10) := 0;
pinstance_id NUMERIC(10) := 0;
P_NOWQTY NUMERIC(14,4) := 0;
v_count1 NUMERIC(10) := 0;
v_count2 NUMERIC(10) := 0;
v_BOMAlternative_ID NUMERIC(10) := 0;
v_BOMAlternative_SW NUMERIC(10) := 0;
BEGIN
IF pinstance is null THEN
pinstance_id:=0;
ELSE
pinstance_id:=pinstance;
END IF;
v_message :='程式開始..';
IF pinstance_id > 0 THEN
BEGIN
ResultStr := 'PInstanceNotFound';
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'Y',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = pinstance_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=pinstance_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_date;
p_DocDateTo :=p_date_to;
END IF;
END LOOP;
p_DocDate = coalesce(p_DocDate, current_date-'2month'::interval ); --假設兩個月前); --now()
-- SELECT date_trunc('month', current_date),date_trunc('month', current_date)+'1month'::interval-'1day'::interval
p_DocDate := date_trunc('month', p_DocDate);
p_DocDateTo := date_trunc('month', p_DocDate)+'1month'::interval-'1day'::interval;
p_DocDate := p_DocDate -'8month'::interval; --假設 8 個月前
-- p_DocDateTo := p_DocDateTo -'2month'::interval;
p_AD_User_ID :=coalesce(p_AD_User_ID,0);
p_AD_Client_ID :=coalesce(p_AD_Client_ID,1000000);
p_AD_Org_ID :=coalesce(p_AD_Org_ID,0);
-- SELECT * FROM adempiere.SK_BOM_RM;
sqlins := 'INSERT INTO adempiere.SK_BOM_RM('
|| 'SK_BOM_RM_ID, SK_BOM_RM_UU, SP_Date, Doc_No, SD_SKno, Exp_Qty,'
|| 'BOMName, BOMValue, BOMQty, DocumentNo, Version, SK_BOM_ID, LineName, LineValue, LineQty, Effectivedate, Expireddate, SK_BomAlternative_ID, AlternativeName,'
|| 'AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy'
|| ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24,$25,$26)';
--銷貨
FOR p IN (select trim(b.sd_skno) as sk_no,
a.sp_no as doc_no,
a.sp_date as sp_date,
null::numeric(14,4) as in_qty,
b.sd_qty as out_qty, --出口數量
b.sd_qty as exp_qty, --出口數量
null::date as out_date,
null::varchar(20) as bonding_no
from adempiere.z_sslip a
inner join adempiere.z_sslpdt b on b.sd_class= a.sp_class and b.sd_slip_fg = a.sp_slip_fg and b.sd_no = a.sp_no
inner join adempiere.z_sstock s on trim(s.sk_no)=trim(b.sd_skno)
where 1=1
-- and trim(b.sd_skno) = p.sk_no -- 必須同第一層的料號
-- and date_trunc('day',a.sp_date)= q.docdate -- 必須同第二層的日期
and a.sp_class = '1' and a.sp_slip_fg = '2' -- 成品出貨 銷貨
and s.sk_ikind = '2' --成品
and date_trunc('day', a.sp_date) between date_trunc('day',p_DocDate) and date_trunc('day',p_DocDateTo) -- 當期有進出
order by b.sd_skno, a.sp_date, a.sp_no
) LOOP
v_message := '銷貨';
--SELECT * FROM adempiere.SK_BOM b
FOR q IN (
SELECT s.Name AS BOMName, s.Value AS BOMValue, b.BOMQty AS BOMQty, b.DocumentNo, b.Version, b.SK_BOM_ID,
sl.Name AS LineName, sl.Value AS LineValue, bl.BOMQty AS LineQty,
b.EffectiveDate, b.ExpiredDate, bl.SK_BOMAlternative_ID, nl.Name AS AlternativeName
FROM adempiere.SK_BOM b
INNER JOIN adempiere.SK_BOMLine bl ON b.SK_BOM_ID =bl.SK_BOM_ID
INNER JOIN adempiere.SK_Stock s ON s.SK_Stock_ID =b.SK_Stock_ID
INNER JOIN adempiere.SK_Stock sl ON sl.SK_Stock_ID=bl.SK_Stock_ID
LEFT OUTER JOIN adempiere.SK_BOMAlternative nl ON bl.SK_BOMAlternative_ID=nl.SK_BOMAlternative_ID
WHERE s.Value = p.sk_no -- BOM 產出成品
AND (b.effectivedate IS NULL OR date_trunc('day',p.sp_date ) >= date_trunc('day', b.effectivedate) )
AND (b.expireddate IS NULL OR date_trunc('day',p.sp_date ) <= date_trunc('day', b.expireddate) )
ORDER BY COALESCE(bl.SK_BOMAlternative_ID,0)
) LOOP
v_message := '有用到的日期';
v_count2 := v_count2 + 1;
-- IF 有替代性質代碼 IF 上一個替代性質 跟 目前這筆不相同 = 這是第一筆
-- ELSE 相同 = 這是第 N 筆
--
IF COALESCE(q.SK_BOMAlternative_ID,0) > 0 THEN
IF v_BOMAlternative_ID <> q.SK_BOMAlternative_ID THEN -- 第1筆
-- v_BOMAlternative_ID := q.SK_BOMAlternative_ID;
v_BOMAlternative_SW := 1; -- 這是第 1 筆
ELSE
v_BOMAlternative_SW := v_BOMAlternative_SW + 1 ; -- 這是第 N 筆
END IF;
END IF;
IF COALESCE(q.SK_BOMAlternative_ID,0) = 0 OR v_BOMAlternative_SW = 1 THEN --
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_BOM_RM'; --1000200
SELECT adempiere.nextid(1000200, 'N') INTO o_nextid; -- SK_BOM_RM
EXECUTE sqlins USING o_nextid, adempiere.generate_uuid(),
p.sp_date, p.doc_no, p.sk_no, p.exp_qty,
q.BOMName, q.BOMValue, q.BOMQty, q.DocumentNo, q.Version, q.SK_BOM_ID,
q.LineName, q.LineValue, q.LineQty,
q.Effectivedate, q.Expireddate, q.SK_BomAlternative_ID,q.AlternativeName,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID;
END IF;
v_BOMAlternative_ID := q.SK_BOMAlternative_ID;
END LOOP; --BOM
END LOOP; --銷貨
v_message :='程式完成::';
IF pinstance_id > 0 THEN
BEGIN
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 1,
errormsg = v_message
WHERE ad_pinstance_id = pinstance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
--raise notice 'Yo this is good! --> % %', SQLERRM, SQLSTATE;
EXCEPTION WHEN OTHERS THEN
v_message :='程式失敗::'||SQLERRM||' ,SQLSTATE='||SQLSTATE||' ,v_message='||v_message;
IF pinstance_id > 0 THEN
BEGIN
ResultStr := v_message;
UPDATE adempiere.ad_pinstance
SET updated = NOW(),
isprocessing = 'N',
result = 0,
errormsg = ResultStr
WHERE ad_pinstance_id = pinstance_id;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION adempiere.sk_gen_rmbom(numeric)
OWNER TO adempiere;
沒有留言:
張貼留言