CREATE OR REPLACE PROCEDURE SP_CHN_EXEC_LOCK(
|
I_ORD_NO IN VARCHAR
|
, O_RESULT OUT INTEGER
|
, O_RESULT_MSG OUT VARCHAR
|
) IS
|
L_CLIENT_CD BV_USER_PROFILE.CLIENT_CD%TYPE;
|
L_USER_ID BV_USER_PROFILE.USER_ID%TYPE;
|
L_DSCR_CD CHINA_ORDER.DSCR_CD%TYPE;
|
|
CURSOR CHINA_ORDER_LOCK
|
IS
|
SELECT
|
ORD_NO
|
FROM
|
CHINA_ORDER
|
WHERE
|
ORD_NO = TO_NUMBER(I_ORD_NO)
|
FOR UPDATE
|
NOWAIT
|
;
|
|
CURSOR CLNT_CASH_BALANCE_LOCK
|
IS
|
SELECT
|
CLIENT_CD
|
FROM
|
CLNT_CASH_BALANCE
|
WHERE
|
CLIENT_CD = L_CLIENT_CD
|
FOR UPDATE
|
NOWAIT
|
;
|
|
CURSOR CHINA_CLIENT_POSITION_LOCK
|
IS
|
SELECT
|
CLIENT_CD
|
FROM
|
CHINA_CLIENT_POSITION
|
WHERE
|
CLIENT_CD = L_CLIENT_CD
|
AND DSCR_CD = L_DSCR_CD
|
FOR UPDATE
|
NOWAIT
|
;
|
|
CURSOR CHINA_EXECUTION_LOCK
|
IS
|
SELECT
|
ORD_NO
|
FROM
|
CHINA_EXECUTION
|
WHERE
|
CLIENT_CD = L_CLIENT_CD
|
AND DSCR_CD = L_DSCR_CD
|
FOR UPDATE
|
NOWAIT
|
;
|
|
CURSOR CHINA_UNDELIVERED_TRADE_LOCK
|
IS
|
SELECT
|
EXEC_NO
|
FROM
|
CHINA_UNDELIVERED_TRADE
|
WHERE
|
ORD_NO = TO_NUMBER(I_ORD_NO)
|
FOR UPDATE
|
NOWAIT
|
;
|
|
CURSOR STOCK_CAPITAL_GAIN_LOCK
|
IS
|
SELECT
|
CLIENT_CD
|
FROM
|
STOCK_CAPITAL_GAIN
|
WHERE
|
CLIENT_CD = L_CLIENT_CD
|
FOR UPDATE
|
NOWAIT
|
;
|
|
CURSOR CLIENT_COMMISSION_LOCK
|
IS
|
SELECT
|
CLIENT_CD
|
FROM
|
CLIENT_COMMISSION
|
WHERE
|
CLIENT_CD = L_CLIENT_CD
|
FOR UPDATE
|
NOWAIT
|
;
|
|
BEGIN
|
O_RESULT := 1;
|
O_RESULT_MSG := 'CHINA_ORDER_SELECT';
|
|
SELECT
|
CLIENT_CD
|
, DSCR_CD
|
INTO
|
L_CLIENT_CD
|
, L_DSCR_CD
|
FROM
|
CHINA_ORDER
|
WHERE
|
ORD_NO = TO_NUMBER(I_ORD_NO)
|
AND ORD_SUB_NO = 1
|
;
|
IF ( L_CLIENT_CD IS NULL ) THEN
|
RETURN;
|
END IF;
|
|
O_RESULT := 2;
|
O_RESULT_MSG := 'BV_USER_PROFILE_LOCK';
|
|
SELECT
|
USER_ID
|
INTO
|
L_USER_ID
|
FROM
|
BV_USER_PROFILE
|
WHERE
|
CLIENT_CD = L_CLIENT_CD
|
FOR UPDATE
|
;
|
IF ( L_USER_ID IS NULL ) THEN
|
RETURN;
|
END IF;
|
|
O_RESULT := 3;
|
O_RESULT_MSG := 'BV_USER_PASSWD_LOCK';
|
|
SELECT
|
USER_ID
|
INTO
|
L_USER_ID
|
FROM
|
BV_USER_PASSWD
|
WHERE
|
USER_ID = L_USER_ID
|
FOR UPDATE
|
NOWAIT
|
;
|
|
O_RESULT := 4;
|
O_RESULT_MSG := 'CHINA_ORDER_LOCK';
|
OPEN CHINA_ORDER_LOCK;
|
CLOSE CHINA_ORDER_LOCK;
|
|
O_RESULT := 5;
|
O_RESULT_MSG := 'CLNT_CASH_BALANCE_LOCK';
|
OPEN CLNT_CASH_BALANCE_LOCK;
|
CLOSE CLNT_CASH_BALANCE_LOCK;
|
|
O_RESULT := 6;
|
O_RESULT_MSG := 'CHINA_CLIENT_POSITION_LOCK';
|
OPEN CHINA_CLIENT_POSITION_LOCK;
|
CLOSE CHINA_CLIENT_POSITION_LOCK;
|
|
O_RESULT := 7;
|
O_RESULT_MSG := 'CHINA_EXECUTION_LOCK';
|
OPEN CHINA_EXECUTION_LOCK;
|
CLOSE CHINA_EXECUTION_LOCK;
|
|
O_RESULT := 8;
|
O_RESULT_MSG := 'CHINA_UNDELIVERED_TRADE_LOCK';
|
OPEN CHINA_UNDELIVERED_TRADE_LOCK;
|
CLOSE CHINA_UNDELIVERED_TRADE_LOCK;
|
|
O_RESULT := 9;
|
O_RESULT_MSG := 'STOCK_CAPITAL_GAIN_LOCK';
|
OPEN STOCK_CAPITAL_GAIN_LOCK;
|
CLOSE STOCK_CAPITAL_GAIN_LOCK;
|
|
O_RESULT := 10;
|
O_RESULT_MSG := 'CLIENT_COMMISSION_LOCK';
|
OPEN CLIENT_COMMISSION_LOCK;
|
CLOSE CLIENT_COMMISSION_LOCK;
|
|
O_RESULT := 0;
|
O_RESULT_MSG := NULL;
|
|
EXCEPTION
|
WHEN NO_DATA_FOUND THEN
|
O_RESULT := -1;
|
WHEN TOO_MANY_ROWS THEN
|
O_RESULT := -2;
|
|
WHEN OTHERS THEN
|
NULL;
|
END;
|
/
|