-- Function: adempiere.sk_gen_rmjl(numeric)
-- DROP FUNCTION adempiere.sk_gen_rmjl(numeric);
CREATE OR REPLACE FUNCTION adempiere.sk_gen_rmjl(pinstance numeric)
RETURNS void AS
$BODY$
-- ALTER TABLE adempiere.SK_RMJH
-- ALTER COLUMN sk_name TYPE varchar(80)
-- set search_path=adempiere
-- SELECT * FROM adempiere.SK_RMJH
-- SELECT * FROM adempiere.SK_RMJL
-- select * from adempiere.SK_RMJH where sk_no='TG-G016' 1052702 608.00
-- select * from adempiere.SK_RMJL where SK_RMJH_ID=1052702 order by SK_RMJL_id
-- select adempiere.sk_gen_rmjl(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);
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);
--第一層抓出需要的料號
FOR p IN (
SELECT sk.sk_no, sk.sk_name,
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 = '6' and a.sp_slip_fg = 'R' -- 借入
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 sk_ikind <> '2' -- 非成品
AND bp.ptype = '5' -- bp.ptype 2 是成品 5 是原料
ORDER BY sk.sk_ikind,sk.sk_no
) LOOP
v_message := '讀取傳遞參數表';
P_NOWQTY := p.NOWQTY;
-- 記錄下有期初 或是 本期有異動的料號 當作單頭
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJH'; --1000195
SELECT adempiere.nextid(1000195, 'N') INTO o_nextid; -- SK_RMJH
sqlins := '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)';
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();
--SET search_path=adempiere
--SELECT adempiere.generate_uuid()::char(36)
-- select date_trunc('day', now())
-- 第二層 取出該料號有異動的日期
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 銷售退回
(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
-- inner join adempiere.z_sstock sk on bp.sk_no = sk.sk_no and sk.sk_ikind <> '2'
order by z.docdate
) LOOP
v_message := '有用到的日期';
sqlins := '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)';
-- 第三層明細分 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_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_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_no
) LOOP
v_message := '進貨入庫';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
-- SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_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 a.sp_no
) LOOP
v_message := '銷貨退回';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
--SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_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 = '6' and a.sp_slip_fg = 'R' -- 借入
-- and s.sk_ikind = '2' --成品
order by a.sp_no
) LOOP
v_message := '借入';
P_NOWQTY := P_NOWQTY + coalesce(r.in_qty,0);
--SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_uu
END LOOP; --借入
--借貨
FOR r IN (select a.sp_no as doc_no,
null::numeric(14,2) as in_qty,
b.sd_qty as out_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 = '2' and a.sp_slip_fg = '4' -- 借貨
-- and s.sk_ikind = '2' --成品
order by a.sp_no
) LOOP
v_message := '借貨';
P_NOWQTY := P_NOWQTY - coalesce(r.out_qty,0);
--SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_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 like '2' --成品
order by a.sp_no
) LOOP
v_message := '銷貨';
P_NOWQTY := P_NOWQTY - coalesce(r.out_qty,0);
--SELECT * FROM adempiere.AD_Sequence WHERE NAME LIKE 'SK_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_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_RMJL'; --1000204
SELECT adempiere.nextid(1000204, 'N') INTO o_nextlineid; -- SK_RMJL
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_rmjl_uu
END LOOP; --生產領用
END LOOP; --date loop
END LOOP; --head loop
--科學工業園區 鎧暘科技股份有限公司 保稅成品帳
-- 101年 存(出)倉情形 帳面 成品出口情形（含視同出口及內銷）
-- 月 日 存(出)倉 存倉 出倉 庫存數量 驗放日期 報單號碼 出口數量 內銷數量
-- 單證號碼 數量 數量
--鎧暘科技股份有限公司 保稅成品帳
--園區事業編號: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_rmjl(numeric)
OWNER TO adempiere;
沒有留言:
張貼留言