根據 iDempiere GPL 版權
改過的程式如果散播出去(買賣或無償)都需公告
由顧問教導客製化寫作, 顧問並非散播程式僅教學示範
由顧問教導後由客戶自行客製化的公司, 如屬自用無須公告客製化程式碼.
:::下面案例要求交付程式碼
因此顧問在此對大家公告程式碼內容
有些資料是給程式判斷, 然資料屬於公司機密
因此無法公告資料內容, 在此只能公告程式碼
這是目前台灣保稅系統成本與原料帳資料表架構
-- Function: adempiere.sk_gen_alljl(numeric)
-- DROP FUNCTION adempiere.sk_gen_alljl(numeric);
CREATE OR REPLACE FUNCTION adempiere.sk_gen_alljl(pinstance numeric)
RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_FGJH
-- ALTER COLUMN sk_name TYPE varchar(80)
-- "程式失敗::資料行參考 "sk_no" 模稜兩可 ,SQLSTATE=42702 ,v_message=讀取傳遞參數表"
-- set search_path=adempiere
-- SELECT * FROM adempiere.SK_RMJH
-- DELETE FROM adempiere.SK_FGJH
-- DELETE FROM adempiere.SK_FGJL
-- DELETE FROM adempiere.SK_RMJH
-- DELETE FROM adempiere.SK_RMJL
-- select * from adempiere.SK_FGJH where sk_no='TG-G016' 1052702 608.00
-- select * from adempiere.SK_FGJL where SK_FGJH_ID=1052702 order by SK_FGJL_id
-- select adempiere.sk_gen_alljl(1000000);
-- select * from adempiere.ad_pinstance where ad_pinstance_id=1000000
DECLARE
text_var1 text;
text_var2 text;
text_var3 text;
ResultStr VARCHAR (200);
roleaccesslevelwin VARCHAR (200);
sql_fg VARCHAR (4000);
sql_rm VARCHAR (4000);
sql_fgl VARCHAR (4000);
sql_rml VARCHAR (4000);
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;
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;
-- SELECT date_trunc('month', current_date),date_trunc('month', current_date)+'1month'::interval-'1day'::interval
p_DocDate := coalesce(p_DocDate, date_trunc('month', current_date));
p_DocDateTo := coalesce(p_DocDate, date_trunc('month', current_date)+'1month'::interval-'1day'::interval);
p_DocDate := p_DocDate -'100month'::interval; --假設兩個月前
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);
sql_fg := 'INSERT INTO SK_FGJH(sk_fgjh_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,'
|| 'sk_no, sk_name, sk_nowqty, docdate, docdateto, sk_fgjh_uu)VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)';
sql_rm := 'INSERT INTO SK_RMJH(sk_rmjh_id, ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,'
|| 'sk_no, sk_name, sk_nowqty, docdate, docdateto, sk_rmjh_uu)VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)';
sql_fgl := 'INSERT INTO SK_FGJL(sk_fgjh_id, sk_fgjl_id,' --1..2
|| 'ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,' --3..9
|| 'sk_no, docdate, sp_no, in_qty, out_qty, sk_nowqty, out_date, bonding_no, exp_qty, loc_qty, sk_fgjl_uu' --10..20
|| ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)';
sql_rml := 'INSERT INTO SK_RMJL(sk_rmjh_id, sk_rmjl_id,' --1..2
|| 'ad_client_id, ad_org_id, isactive, created, createdby, updated, updatedby,' --3..9
|| 'sk_no, docdate, sp_no, in_qty, out_qty, sk_nowqty, out_date, bonding_no, exp_qty, loc_qty, sk_rmjl_uu' --10..20
|| ')VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)';
--第一層抓出需要的料號
FOR p IN (
SELECT sk.sk_no, sk.sk_name, bp.ptype, --bp.ptype="2" 成品 bp.ptype="5" 原料
coalesce((select sum(a.sh_qty)
from adempiere.z_sphd a
where sh_skno = sk.sk_no
and sh_sfg = '1' -- 成品產出
and (a.sh_date > '20051231' and a.sh_date < p_DocDate)),0) -- 當期之前有進出
+ coalesce((select sum(sd_qty)
from adempiere.z_sslpdt
where sd_skno = sk.sk_no
and((sd_class = '0' and sd_slip_fg = '0') OR -- 進貨入庫
(sd_class = '5' and sd_slip_fg = 'A'))
and (sd_date > '20051231' and sd_date < p_DocDate)),0) -- 當期之前有進出
+ coalesce((select sum(b.sd_qty)
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 s.sk_no = b.sd_skno and s.sk_ikind = '2' --成品
where b.sd_skno = sk.sk_no
and a.sp_class = '1' and a.sp_slip_fg = '3' -- 銷售退回
and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0), -- 當期之前有進出
+ coalesce((select sum(b.sd_qty)
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 s.sk_no = b.sd_skno and s.sk_ikind = '2' --成品
where b.sd_skno = sk.sk_no
and a.sp_class = '2' and a.sp_slip_fg = '4' -- 借貨
and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0), -- 當期之前有進出
- coalesce((select sum(sd_qty)
from adempiere.z_spdt
-- inner join adempiere.z_sphd b on b.sd_spno = a.sh_spno and b.sd_sfg=a.sh_sfg
where sd_skno = sk.sk_no
and sd_sfg = '3' -- 生產領用
and (sd_date > '20051231' and sd_date < p_DocDate)),0) -- 當期之前有進出
- coalesce((select sum(b.sd_qty)
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 s.sk_no = b.sd_skno and s.sk_ikind = '2' --成品
where b.sd_skno = sk.sk_no
and a.sp_class = '1' and a.sp_slip_fg = '2' -- 成品銷售
and (a.sp_date > '20051231' and a.sp_date < p_DocDate)),0) AS NOWQTY -- 當期之前有進出
FROM adempiere.z_sstock sk
inner join adempiere.z_bonding_product bp on bp.sk_no = sk.sk_no
WHERE 1=1 -- and trim(sk_no)='1F-OFS905'
--AND sk_ikind in('4', '5', '6') --4 物料,5 原料,6 其他
-- AND bp.ptype = '5' -- 原料 bp.ptype = "2" 成品
ORDER BY bp.ptype, sk.sk_ikind,sk.sk_no
) LOOP
v_message := '讀取傳遞參數表';
P_NOWQTY := p.NOWQTY;
-- 記錄下有期初 或是 本期有異動的料號 當作單頭
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_FGJH'; --1000195
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJH'; --1000203
IF p.ptype='2' THEN
sqlins:=sql_fg;
SELECT adempiere.nextid(1000195, 'N') INTO o_nextid; -- SK_FGJH
ELSE
sqlins:=sql_rm;
SELECT adempiere.nextid(1000203, 'N') INTO o_nextid; -- SK_FGJH
END IF; --原料
EXECUTE sqlins USING o_nextid, p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID,
p.sk_no, p.sk_name, P_NOWQTY, p_DocDate, p_DocDateTo, adempiere.generate_uuid();
-- 第二層 取出該料號有異動的日期
FOR q IN (
select distinct
z.docdate,
date_part('year', z.docdate) as year,
date_part('month', z.docdate) as month,
date_part('day', z.docdate) as date
from (
select distinct(date_trunc('day', sh_date )) as docdate
from adempiere.z_sphd
where 1=1
and sh_skno = p.sk_no
and sh_sfg = '1' -- 成品產出
and sh_date between p_DocDate and p_DocDateTo
union
select distinct(date_trunc('day',sd_date)) as docdate
from adempiere.z_sslpdt
where 1=1
and sd_skno = p.sk_no
and((sd_class = '0' and sd_slip_fg = '0') OR -- 進貨入庫
(sd_class = '5' and sd_slip_fg = 'A'))
and sd_date between p_DocDate and p_DocDateTo
union
select distinct(date_trunc('day', sd_date )) as docdate
from adempiere.z_spdt
where 1=1
and sd_skno = p.sk_no
and sd_sfg = '3' -- 生產領用
and sd_date between p_DocDate and p_DocDateTo
union
select distinct(date_trunc('day', a.sp_date )) as docdate
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 s.sk_no = b.sd_skno and s.sk_ikind = '2' --成品
where 1=1
and b.sd_skno = p.sk_no
and((a.sp_class = '1' and (a.sp_slip_fg = '2' or a.sp_slip_fg = '3')) OR -- 成品銷售 and 銷售退回 and 借貨
(a.sp_class = '2' and a.sp_slip_fg = '4' ) OR
(a.sp_class = '6' and a.sp_slip_fg = 'R' ))
and a.sp_date between p_DocDate and p_DocDateTo
) z order by z.docdate
) LOOP
v_message := '有用到的日期';
-- 第三層明細分 in/out
-- in detail
v_message := 'p.sk_no='||p.sk_no||' ,q.docdate='||q.docdate;
-- 產出
FOR r IN (
select
a.sh_spno as doc_no, --存(出)倉單證號碼
a.sh_qty as in_qty, --存倉數量 / 出口數量
null::numeric(10,2) as out_qty, --出倉數量
null::numeric(10,2) as exp_qty, --外銷數量
null::numeric(10,2) as loc_qty, --內銷數量
null::date as out_date, --驗放日期,
null::varchar(20) as bonding_no --報單號碼,
from adempiere.z_sphd a
where 1=1
and a.sh_skno =p.sk_no -- 必須同第一層的料號
and a.sh_date =q.docdate -- 必須同第二層的日期
and a.sh_sfg = '1' -- 組合單 產出 就是成品入庫
order by a.sh_spno
) LOOP
v_message := '產出';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_FGJL'; --1000196
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
sqlins:=NULL;
IF p.ptype='2' THEN
sqlins:=sql_fgl; --成品
SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
ELSE
sqlins:=sql_rml;
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
END IF; --原料
EXECUTE sqlins USING o_nextid, o_nextlineid,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID,
p.sk_no, --料號 (不同要跳頁)
q.docdate,
r.doc_no, --存(出)倉單證號碼
r.in_qty, --存倉數量
r.out_qty, --出倉數量
P_NOWQTY, --帳面庫存數量 sk_nowqty
r.out_date, --驗放日期
r.bonding_no, --報單號碼
r.exp_qty, --出口數量 exp_qty
r.loc_qty, --內銷數量 loc_qty
adempiere.generate_uuid(); --sk_fgjl_uu
END LOOP; --產出
--進貨入庫
FOR r IN (
select
sd_no as doc_no, --存(出)倉單證號碼
sd_qty as in_qty, --存倉數量 / 出口數量
null::numeric(10,2) as out_qty, --出倉數量
null::numeric(10,2) as exp_qty, --外銷數量
null::numeric(10,2) as loc_qty, --內銷數量
null::date as out_date, --驗放日期,
null::varchar(20) as bonding_no --報單號碼,
from adempiere.z_sslpdt
where 1=1
and sd_skno = p.sk_no -- 必須同第一層的料號
and sd_date = q.docdate -- 必須同第二層的日期
and((sd_class = '0' and sd_slip_fg = '0') OR -- 進貨入庫
(sd_class = '5' and sd_slip_fg = 'A'))
order by sd_seqfld
) LOOP
v_message := '進貨入庫';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
sqlins:=NULL;
IF p.ptype='2' THEN
sqlins:=sql_fgl;
SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
ELSE sqlins:=sql_rml;
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
END IF; --原料
EXECUTE sqlins USING o_nextid, o_nextlineid,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(), p_AD_User_ID, now(), p_AD_User_ID,
p.sk_no, --料號 (不同要跳頁)
q.docdate,
r.doc_no, --存(出)倉單證號碼
r.in_qty, --存倉數量
r.out_qty, --出倉數量
P_NOWQTY, --帳面庫存數量 sk_nowqty
r.out_date, --驗放日期
r.bonding_no, --報單號碼
r.exp_qty, --出口數量 exp_qty
r.loc_qty, --內銷數量 loc_qty
adempiere.generate_uuid(); --sk_fgjl_uu
END LOOP; --進貨入庫
--銷貨退回
FOR r IN (select a.sp_no as doc_no,
b.sd_qty as in_qty,
null::numeric(14,2) as out_qty,
null::numeric(14,2) as exp_qty, --出口數量
null::numeric(14,2) as loc_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 s.sk_no=b.sd_skno
where 1=1
and b.sd_skno = p.sk_no -- 必須同第一層的料號
and a.sp_date = q.docdate -- 必須同第二層的日期
and a.sp_class = '1' and a.sp_slip_fg = '3' -- 成品出貨 銷貨退回
and s.sk_ikind = '2' --成品
order by b.sd_seqfld -- a.sp_no,
) LOOP
v_message := '銷貨退回';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
sqlins:=NULL;
IF p.ptype='2' THEN
sqlins:=sql_fgl;
SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
ELSE
sqlins:=sql_rml;
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
END IF; --原料
EXECUTE sqlins USING o_nextid, o_nextlineid,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
p.sk_no, --料號
q.docdate,
r.doc_no, --存(出)倉單證號碼
r.in_qty, --存倉數量
r.out_qty, --出倉數量
P_NOWQTY, --帳面庫存數量 sk_nowqty
r.out_date, --驗放日期
r.bonding_no, --報單號碼
r.exp_qty, --出口數量 exp_qty
r.loc_qty, --內銷數量 loc_qty
adempiere.generate_uuid(); --sk_fgjl_uu
END LOOP; --銷貨退回
--借貨
FOR r IN (select a.sp_no as doc_no,
b.sd_qty as in_qty,
null::numeric(14,2) as out_qty,
null::numeric(14,2) as exp_qty, --出口數量
null::numeric(14,2) as loc_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 s.sk_no=b.sd_skno
where 1=1
and b.sd_skno = p.sk_no -- 必須同第一層的料號
and a.sp_date= q.docdate -- 必須同第二層的日期
and a.sp_class = '2' and a.sp_slip_fg = '4' -- 成品出貨 銷貨退回
and s.sk_ikind = '2' --成品
order by b.sd_seqfld --a.sp_no,
) LOOP
v_message := '銷貨退回';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
sqlins:=NULL;
IF p.ptype='2' THEN
sqlins:=sql_fgl;
SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
ELSE
sqlins:=sql_rml;
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
END IF; --原料
EXECUTE sqlins USING o_nextid, o_nextlineid,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
p.sk_no, --料號
q.docdate,
r.doc_no, --存(出)倉單證號碼
r.in_qty, --存倉數量
r.out_qty, --出倉數量
P_NOWQTY, --帳面庫存數量 sk_nowqty
r.out_date, --驗放日期
r.bonding_no, --報單號碼
r.exp_qty, --出口數量 exp_qty
r.loc_qty, --內銷數量 loc_qty
adempiere.generate_uuid(); --sk_fgjl_uu
END LOOP; --借貨
--銷貨
FOR r IN (select a.sp_no as doc_no,
b.sd_qty as out_qty,
null::numeric(14,2) as in_qty,
b.sd_qty as exp_qty, --出口數量
null::numeric(14,2) as loc_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 s.sk_no=b.sd_skno
where 1=1
and b.sd_skno = p.sk_no -- 必須同第一層的料號
and a.sp_date = q.docdate -- 必須同第二層的日期
and a.sp_class = '1' and a.sp_slip_fg = '2' -- 成品出貨 銷貨
and s.sk_ikind = '2' --成品
order by b.sd_seqfld
) LOOP
v_message := '銷貨';
P_NOWQTY := P_NOWQTY - coalesce(r.out_qty,0);
--SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_FGJL'; --1000196
sqlins:=NULL;
IF p.ptype='2' THEN
sqlins:=sql_fgl;
SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
ELSE
sqlins:=sql_rml;
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
END IF; --原料
EXECUTE sqlins USING o_nextid, o_nextlineid,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
p.sk_no, --料號
q.docdate,
r.doc_no, --存(出)倉單證號碼
r.in_qty, --存倉數量
r.out_qty, --出倉數量
P_NOWQTY, --帳面庫存數量 sk_nowqty
r.out_date, --驗放日期
r.bonding_no, --報單號碼
r.exp_qty, --出口數量 exp_qty
r.loc_qty, --內銷數量 loc_qty
adempiere.generate_uuid(); --sk_fgjl_uu
END LOOP; --銷貨
--生產領用
FOR r IN (select sd_spno as doc_no,
null::numeric(14,4) as in_qty,
sd_qty as out_qty,
null::numeric(14,4) as exp_qty, --外銷數量
null::numeric(14,4) as loc_qty, --內銷數量
null::date as out_date,
null::varchar(20) as bonding_no
from adempiere.z_spdt
where 1=1
and sd_skno = p.sk_no -- 必須同第一層的料號
and sd_date = q.docdate -- 必須同第二層的日期
and sd_sfg = '3' -- 生產領用
order by sd_spno
) LOOP
v_message := '生產領用';
P_NOWQTY := P_NOWQTY - coalesce(r.out_qty,0);
--SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_FGJL'; --1000196
sqlins:=NULL;
IF p.ptype='2' THEN
sqlins:=sql_fgl;
SELECT adempiere.nextid(1000196, 'N') INTO o_nextlineid; -- SK_FGJL
ELSE
sqlins:=sql_rml;
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_FGJL
END IF; --原料
EXECUTE sqlins USING o_nextid, o_nextlineid,
p_AD_Client_ID, p_AD_Org_ID, 'Y', now(),p_AD_User_ID, now(),p_AD_User_ID,
p.sk_no, --料號
q.docdate,
r.doc_no, --存(出)倉單證號碼
r.in_qty, --存倉數量
r.out_qty, --出倉數量
P_NOWQTY, --帳面庫存數量 sk_nowqty
r.out_date, --驗放日期
r.bonding_no, --報單號碼
0, --出口數量 exp_qty
0, --內銷數量 loc_qty
adempiere.generate_uuid(); --sk_fgjl_uu
END LOOP; --生產領用
END LOOP; --date loop
END LOOP; --head loop
--科學工業園區 XX科技股份有限公司 保稅成品帳
-- 101年 存(出)倉情形 帳面 成品出口情形（含視同出口及內銷）
-- 月 日 存(出)倉 存倉 出倉 庫存數量 驗放日期 報單號碼 出口數量 內銷數量
-- 單證號碼 數量 數量
--XX科技股份有限公司 保稅成品帳
--園區事業編號:CS0916 (固定)
--帳卡編號 :年月日
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_alljl(numeric)
OWNER TO adempiere;
沒有留言:
張貼留言