PROCEDURE get_sheetid(i_topic IN VARCHAR2, o_newsheetid OUT VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_sheettype NUMBER; --代码名称
BEGIN
SELECT t.sheettype
INTO v_sheettype
FROM sheetidrule t
WHERE t.sheettypename = i_topic
FOR UPDATE;
---调用函数返回sheetid
tl_getnewsheetid(v_sheettype, '', o_newsheetid);
commit;
END get_sheetid;
CREATE OR REPLACE PROCEDURE JVI2.TL_GetNewSheetID
(
I_SheetType IN VARCHAR2,
I_SpecifiedPrefix IN VARCHAR2,
O_NewSheetID OUT VARCHAR2
)
AS
-------------------------------------------------------------
--TL_GetNewSheetID 接口库取单据号码过程
--断 点: 99101nn
--参 数:
--返 回:
--算 法:
--建 立: DDP 2011-09-06
------------------------------------------------------------
v_Err INTEGER := -20101;
v_BreakPoint INTEGER;
v_Msg VARCHAR2(255);
v_PrefixType INTEGER;
v_PreFix VARCHAR2(6);
v_SerialNumber INTEGER;
v_SNumber VARCHAR2(6);
v_ResetDate DATE;
v_i_ResetDate INTEGER;
v_YYYYMMDD VARCHAR2(20);
v_i_YYYYMMDD VARCHAR2(20);
v_DailyReset INTEGER;
BEGIN
v_BreakPoint := 9910110;
v_Msg := ' ';
v_YYYYMMDD := to_char(SYSDATE,'yyyyMMdd');
BEGIN
SELECT prefix,prefixtype,serialnumber,trunc(ResetDate),DailyReset
INTO v_PreFix,v_PrefixType,v_SerialNumber,v_ResetDate,v_DailyReset
FROM sheetidrule WHERE sheettype=I_SheetType;
EXCEPTION WHEN NO_DATA_FOUND THEN
v_Msg := '单据类型['||to_char(I_SheetType)||'未设置!';
Raise_Application_Error(-20051,v_Msg);
END;
IF v_DailyReset = 1 THEN
-- 日结清零
v_i_YYYYMMDD := to_number(to_char(SYSDATE,'yyyyMMdd'));
v_i_ResetDate := to_number(to_char(v_ResetDate,'yyyyMMdd'));
IF v_i_YYYYMMDD != v_i_ResetDate THEN
UPDATE sheetidrule set ResetDate=SYSDATE,serialnumber=1 WHERE sheettype=I_SheetType;
v_SerialNumber :=1;
END IF;
ELSIF v_DailyReset=2 THEN
-- 月结清零
v_i_YYYYMMDD := to_number(to_char(SYSDATE,'yyyyMM'));
v_i_ResetDate := to_number(to_char(v_ResetDate,'yyyyMM'));
IF v_i_YYYYMMDD != v_i_ResetDate THEN
UPDATE sheetidrule SET ResetDate=SYSDATE,serialnumber=1 WHERE sheettype=I_SheetType;
v_SerialNumber :=1;
END IF;
END IF;
-- 将序号格式化成6位字符串,不够的补0
v_SNumber := lpad(to_number(v_SerialNumber),6,0);
IF v_PrefixType = 0 THEN
IF v_PreFix IS NOT NULL THEN
O_NewSheetID := 'JV'||I_SheetType||v_YYYYMMDD||v_SNumber;
ELSE
O_NewSheetID := v_YYYYMMDD||v_SNumber;
END IF;
ELSIF v_PrefixType = 1 THEN
RAISE_APPLICATION_ERROR(-20001,'机构编码(店号)暂时不能使用');
ELSIF v_PrefixType = 2 THEN
RAISE_APPLICATION_ERROR(-20002,'BUID暂时不能使用');
END IF;
-- 修改[流水号],递加
UPDATE sheetidrule SET serialnumber=serialnumber+1 WHERE sheettype=I_SheetType;
EXCEPTION WHEN OTHERS THEN
v_Msg := REPLACE(SQLERRM,'ORA' || V_Err || ': ');
RAISE_APPLICATION_ERROR(-20001,To_Char(v_BreakPoint) || '-' || v_Msg);
END TL_GetNewSheetID;
/
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建