主要是用上一个编码控制的表格。每次要新增一个编号的时候,必须要锁住对应的编码控制表,实现并发控制。
当然,锁表肯定会导致等待的死锁,所以这里用上nowait。当尝试次数超过30次,就报错。
FUNCTION GET_ESLIP_NUM(
ORGANIZATION_ID NUMBER
,PERIOD VARCHAR2 --YYYYMMDD
,TRANSACTION_TYPE_ID NUMBER
,USER_ID NUMBER
,P_RAISE IN NUMBER DEFAULT XYG_PUB_CONST_PKG.C_TRUE
)
RETURN INV_ESLIP_HEADERS.ESLIP_NUMBER%TYPE
IS
CT NUMBER;
V_ESLIP_NUM INV_ESLIP_HEADERS.ESLIP_NUMBER%TYPE;
TMP_PERIOD VARCHAR2(20);
KND_CMP VARCHAR2(20);
V_ORGANIZATION_ID NUMBER;
V_TRANSACTION_TYPE_ID NUMBER;
--V_LOGIN_ID NUMBER;
L_NOWAIT_COUNT NUMBER;
BEGIN
/*
单据的命名规则为:
工业园代码+单据头+年月日+4位流水号
例如:DZY1102100001
工业园 代码
东莞 D
江门 J
芜湖 W
天津 T
深圳 S
SELECT A.ROWID,A.ORGANIZATION_CODE,(SELECT ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS B
WHERE A.ORGANIZATION_ID = B.ORGANIZATION_ID) NAME,A.ATTRIBUTE8 FROM MTL_PARAMETERS A
单据头 用单据类型的Attribute1维护。为2个字母
*/
--V_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
V_ORGANIZATION_ID := ORGANIZATION_ID;
V_TRANSACTION_TYPE_ID := TRANSACTION_TYPE_ID;
SELECT ATTRIBUTE8
|| (SELECT MTT.ATTRIBUTE1
FROM MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = V_TRANSACTION_TYPE_ID)
INTO KND_CMP
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = V_ORGANIZATION_ID;
IF KND_CMP IS NULL
THEN
XYG_PUB_COMMON_PKG.RAISE_ERROR(
'-20001'
,SQLERRM
, 'ORGANIZATION_ID:'
|| ORGANIZATION_ID
|| ' PERIOD:'
|| PERIOD
|| ' TRANSACTION_TYPE_ID:'
|| TRANSACTION_TYPE_ID
|| ' USER_ID:'
|| TO_CHAR(USER_ID)
);
END IF;
SELECT SUBSTR(PERIOD, 3, 8) INTO TMP_PERIOD FROM DUAL;
SELECT COUNT( * )
INTO CT
FROM INV_NUMBER
WHERE ESLIP_KIND = KND_CMP AND ACCOUNTING_YYMMDD = TMP_PERIOD;
IF CT = 0
THEN
BEGIN
INSERT INTO INV_NUMBER(ESLIP_KIND --SLIP_TYPE
,ACCOUNTING_YYMMDD
,START_SLIP_NUMBER
,END_SLIP_NUMBER
,LAST_SLIP_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
)
VALUES (KND_CMP
,TMP_PERIOD
,1
,999
,0
,SYSDATE
,USER_ID
,V_LOGIN_ID
,SYSDATE
,USER_ID
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
NULL;
END;
END IF;
--锁定对应的number控制表
L_NOWAIT_COUNT := 0;
LOOP
BEGIN
SELECT KND_CMP
|| ACCOUNTING_YYMMDD
|| SUBSTR(TO_CHAR(LAST_SLIP_NUMBER + 1, '0999'), 2, 5)
INTO V_ESLIP_NUM
FROM INV_NUMBER
WHERE ESLIP_KIND = KND_CMP AND ACCOUNTING_YYMMDD = TMP_PERIOD
FOR UPDATE NOWAIT;
EXIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOCK.SLEEP (2);--等待2秒
L_NOWAIT_COUNT := L_NOWAIT_COUNT+1;
IF L_NOWAIT_COUNT >= 30 THEN
XYG_PUB_COMMON_PKG.RAISE_ERROR(
'-20001'
,SQLERRM
, '锁定表格ID('
|| L_CONTROL_NUMBER_ID||')出现异常!尝试次数超过('||L_NOWAIT_COUNT||')次!请等会儿再运行该程序!或者联系ERP组解锁再运行程序!'
);
EXIT;
END IF;
END;
END LOOP;
INSERT INTO INV_ESLIP_NUMBER(ORGANIZATION_ID
,ESLIP_NUMBER --SLIP_NUMBER
,ESLIP_DATE
--,STATUS_FLAG
,TRANSACTION_TYPE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES (V_ORGANIZATION_ID
,V_ESLIP_NUM --SLIP
,SYSDATE
--,'O'
,V_TRANSACTION_TYPE_ID
,SYSDATE
,USER_ID
,SYSDATE
,USER_ID
,V_LOGIN_ID
);
UPDATE INV_NUMBER
SET LAST_SLIP_NUMBER = LAST_SLIP_NUMBER + 1
WHERE ESLIP_KIND = KND_CMP AND ACCOUNTING_YYMMDD = TMP_PERIOD;
RETURN (V_ESLIP_NUM);
EXCEPTION
WHEN OTHERS
THEN
IF P_RAISE = XYG_PUB_CONST_PKG.C_TRUE
THEN
XYG_PUB_COMMON_PKG.RAISE_ERROR(
'-20001'
,SQLERRM
, 'ORGANIZATION_ID:'
|| ORGANIZATION_ID
|| ' PERIOD:'
|| PERIOD
|| ' TRANSACTION_TYPE_ID:'
|| TRANSACTION_TYPE_ID
|| ' USER_ID:'
|| TO_CHAR(USER_ID)
);
ELSE
RETURN XYG_PUB_CONST_PKG.C_RETURN_CHAR;
END IF;
END GET_ESLIP_NUM;
当然,锁表肯定会导致等待的死锁,所以这里用上nowait。当尝试次数超过30次,就报错。
FUNCTION GET_ESLIP_NUM(
ORGANIZATION_ID NUMBER
,PERIOD VARCHAR2 --YYYYMMDD
,TRANSACTION_TYPE_ID NUMBER
,USER_ID NUMBER
,P_RAISE IN NUMBER DEFAULT XYG_PUB_CONST_PKG.C_TRUE
)
RETURN INV_ESLIP_HEADERS.ESLIP_NUMBER%TYPE
IS
CT NUMBER;
V_ESLIP_NUM INV_ESLIP_HEADERS.ESLIP_NUMBER%TYPE;
TMP_PERIOD VARCHAR2(20);
KND_CMP VARCHAR2(20);
V_ORGANIZATION_ID NUMBER;
V_TRANSACTION_TYPE_ID NUMBER;
--V_LOGIN_ID NUMBER;
L_NOWAIT_COUNT NUMBER;
BEGIN
/*
单据的命名规则为:
工业园代码+单据头+年月日+4位流水号
例如:DZY1102100001
工业园 代码
东莞 D
江门 J
芜湖 W
天津 T
深圳 S
SELECT A.ROWID,A.ORGANIZATION_CODE,(SELECT ORGANIZATION_NAME FROM ORG_ORGANIZATION_DEFINITIONS B
WHERE A.ORGANIZATION_ID = B.ORGANIZATION_ID) NAME,A.ATTRIBUTE8 FROM MTL_PARAMETERS A
单据头 用单据类型的Attribute1维护。为2个字母
*/
--V_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
V_ORGANIZATION_ID := ORGANIZATION_ID;
V_TRANSACTION_TYPE_ID := TRANSACTION_TYPE_ID;
SELECT ATTRIBUTE8
|| (SELECT MTT.ATTRIBUTE1
FROM MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = V_TRANSACTION_TYPE_ID)
INTO KND_CMP
FROM MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = V_ORGANIZATION_ID;
IF KND_CMP IS NULL
THEN
XYG_PUB_COMMON_PKG.RAISE_ERROR(
'-20001'
,SQLERRM
, 'ORGANIZATION_ID:'
|| ORGANIZATION_ID
|| ' PERIOD:'
|| PERIOD
|| ' TRANSACTION_TYPE_ID:'
|| TRANSACTION_TYPE_ID
|| ' USER_ID:'
|| TO_CHAR(USER_ID)
);
END IF;
SELECT SUBSTR(PERIOD, 3, 8) INTO TMP_PERIOD FROM DUAL;
SELECT COUNT( * )
INTO CT
FROM INV_NUMBER
WHERE ESLIP_KIND = KND_CMP AND ACCOUNTING_YYMMDD = TMP_PERIOD;
IF CT = 0
THEN
BEGIN
INSERT INTO INV_NUMBER(ESLIP_KIND --SLIP_TYPE
,ACCOUNTING_YYMMDD
,START_SLIP_NUMBER
,END_SLIP_NUMBER
,LAST_SLIP_NUMBER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,CREATION_DATE
,CREATED_BY
)
VALUES (KND_CMP
,TMP_PERIOD
,1
,999
,0
,SYSDATE
,USER_ID
,V_LOGIN_ID
,SYSDATE
,USER_ID
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
NULL;
END;
END IF;
--锁定对应的number控制表
L_NOWAIT_COUNT := 0;
LOOP
BEGIN
SELECT KND_CMP
|| ACCOUNTING_YYMMDD
|| SUBSTR(TO_CHAR(LAST_SLIP_NUMBER + 1, '0999'), 2, 5)
INTO V_ESLIP_NUM
FROM INV_NUMBER
WHERE ESLIP_KIND = KND_CMP AND ACCOUNTING_YYMMDD = TMP_PERIOD
FOR UPDATE NOWAIT;
EXIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_LOCK.SLEEP (2);--等待2秒
L_NOWAIT_COUNT := L_NOWAIT_COUNT+1;
IF L_NOWAIT_COUNT >= 30 THEN
XYG_PUB_COMMON_PKG.RAISE_ERROR(
'-20001'
,SQLERRM
, '锁定表格ID('
|| L_CONTROL_NUMBER_ID||')出现异常!尝试次数超过('||L_NOWAIT_COUNT||')次!请等会儿再运行该程序!或者联系ERP组解锁再运行程序!'
);
EXIT;
END IF;
END;
END LOOP;
INSERT INTO INV_ESLIP_NUMBER(ORGANIZATION_ID
,ESLIP_NUMBER --SLIP_NUMBER
,ESLIP_DATE
--,STATUS_FLAG
,TRANSACTION_TYPE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES (V_ORGANIZATION_ID
,V_ESLIP_NUM --SLIP
,SYSDATE
--,'O'
,V_TRANSACTION_TYPE_ID
,SYSDATE
,USER_ID
,SYSDATE
,USER_ID
,V_LOGIN_ID
);
UPDATE INV_NUMBER
SET LAST_SLIP_NUMBER = LAST_SLIP_NUMBER + 1
WHERE ESLIP_KIND = KND_CMP AND ACCOUNTING_YYMMDD = TMP_PERIOD;
RETURN (V_ESLIP_NUM);
EXCEPTION
WHEN OTHERS
THEN
IF P_RAISE = XYG_PUB_CONST_PKG.C_TRUE
THEN
XYG_PUB_COMMON_PKG.RAISE_ERROR(
'-20001'
,SQLERRM
, 'ORGANIZATION_ID:'
|| ORGANIZATION_ID
|| ' PERIOD:'
|| PERIOD
|| ' TRANSACTION_TYPE_ID:'
|| TRANSACTION_TYPE_ID
|| ' USER_ID:'
|| TO_CHAR(USER_ID)
);
ELSE
RETURN XYG_PUB_CONST_PKG.C_RETURN_CHAR;
END IF;
END GET_ESLIP_NUM;