T-SQL - code examples
Sybase dialect
/*------------------------------------------------------------------------------------------
Proc: CP_BREAK_DRV_NAM
Descr: Breaks driver names, stored in table DRV in one field DRV_NAM, into components
(last, first and middle names) and stores them separately in table TBL_DRV_NAM.
Log: Date Name Description
Feb.15, 2006 MPIMZUS Created
------------------------------------------------------------------------------------------*/
IF EXISTS (SELECT Upper(name) FROM sysobjects WHERE name = 'CP_BREAK_DRV_NAM')
DROP PROCEDURE CP_BREAK_DRV_NAM
GO
/*--------------------------------- Begin procedure --------------------------------------*/
CREATE PROCEDURE CP_BREAK_DRV_NAM AS
/*------------------------------------ Declare -------------------------------------------*/
DECLARE @i_success INTEGER
,@i_row INTEGER
,@i_cst_nm INTEGER
,@i_cst_pg_nm INTEGER
,@s_full_nam VARCHAR(20)
,@s_lst_nam VARCHAR(20)
,@s_fr_nam VARCHAR(20)
,@s_mdl_nam VARCHAR(20)
DECLARE break_nam_cur CURSOR FOR
SELECT DRV.CST_NM,
DRV.CST_PG_NM,
Ltrim(Rtrim(DRV_NAM_ADDR.DRV_NAM))
FROM DRV,
DRV_NAM_ADDR
WHERE DRV.DRV_NM = DRV_NAM_ADDR.DRV_NM AND
DRV.CST_NM IS NOT NULL AND /* <<== The table DRV contains more than 300000 drivers with no CST_NM */
GetDate() BETWEEN DRV_NAM_ADDR.DRV_NAM_ADDR_EFC_DT AND
DRV_NAM_ADDR.DRV_NAM_ADDR_EXP_DT
/*----------------------------------- Begin logic ----------------------------------------*/
TRUNCATE TABLE TBL_DRV_NAM
SELECT @i_success = 1
OPEN break_nam_cur
BEGIN TRANSACTION
WHILE (1 = 1) BEGIN
FETCH break_nam_cur INTO
@i_cst_nm
,@i_cst_pg_nm
,@s_full_nam
IF (@@sqlstatus = 2 /* no more data in the result set */) BREAK
IF (@@sqlstatus = 1 /* error */) BEGIN
SELECT @i_success = 0
BREAK
END
EXEC CP_BREAK_FULL_NAM @s_full_nam, @s_lst_nam OUTPUT, @s_fr_nam OUTPUT, @s_mdl_nam OUTPUT
INSERT INTO TBL_DRV_NAM
(CST_NM
,CST_PG_NM
,FULL_NAM
,LST_NAM
,FR_NAM
,MDL_NAM)
VALUES
(@i_cst_nm
,@i_cst_pg_nm
,@s_full_nam
,@s_lst_nam
,@s_fr_nam
,@s_mdl_nam)
SELECT @i_row = @i_row + 1
IF (@i_row = 10000)
BEGIN
COMMIT
SELECT @i_row = 0
BEGIN TRANSACTION
END
END /* of cursor loop */
IF (@i_success = 1)
COMMIT
ELSE
BEGIN
ROLLBACK
TRUNCATE TABLE TBL_DRV_NAM /* to indicate the failure and prevent invalid querying of TBL_DRV_NAM */
END
CLOSE break_nam_cur
/*------------------------------- End of procedure ---------------------------------------*/
GO
/*
#############################################################################
# Procedure Name: cp_s_pic_new
#============================================================================
# Description: accepts the first, middle, last name and birth date of a customer
# and generates a unique customer PIC.
#############################################################################
# Parameters Type
# ---------- ----
# @cst_pg_nm INTEGER
# @cst_nm INTEGER
# @fr_nam VARCHAR(20)
# @mdl_nam VARCHAR(20)
# @lst_nam VARCHAR(30)
# @brth_dt DATETIME
# @pic_output VARCHAR(12) OUTPUT
#############################################################################
# Variables Type
# --------- ----
#############################################################################
# Constants Type Value
# --------- ---- -----
# None
#############################################################################
# Stored procedures called Description
# ------------------------ -----------
#############################################################################
# Modification Log
#============================================================================
# Problem
# Report # Date UserID Description
# --------- ----------- -------- ----------
# Apr 04 2006 MPIMZUS initial creation - REL 12.3
#############################################################################
*/
IF EXISTS (SELECT Lower(name) FROM sysobjects WHERE name = 'cp_s_pic_new')
DROP PROCEDURE cp_s_pic_new
GO
/*--------------------------------- Begin procedure --------------------------------------*/
CREATE PROCEDURE cp_s_pic_new
@cst_pg_nm INTEGER
,@cst_nm INTEGER
,@fr_nam VARCHAR(20)
,@mdl_nam VARCHAR(20)
,@lst_nam VARCHAR(30)
,@brth_dt DATETIME
,@pic_output VARCHAR(12) OUTPUT
AS
/*------------------------------------ Declare -------------------------------------------*/
DECLARE @dig1_5 CHAR(5)
,@dig6_7 CHAR(2)
,@dig8_9 CHAR(2)
,@dig10 CHAR(1)
,@dig11 CHAR(1)
,@dig11_new CHAR(1)
,@dig12 CHAR(1)
,@char CHAR(1)
,@dig8 CHAR(1)
,@dig9 CHAR(1)
,@dig8_new CHAR(1)
,@dig9_new CHAR(1)
,@mdl_nam_init CHAR(1)
,@pic CHAR(12)
,@pic_without_chk_dgt CHAR(11)
,@codes_1 CHAR(10)
,@codes_2 CHAR(10)
,@codes_bm CHAR(12)
,@birth_year SMALLINT
,@birth_month SMALLINT
,@birth_day SMALLINT
,@pic_exists SMALLINT
,@step SMALLINT
,@num_val SMALLINT
,@drv_nm INTEGER
,@code_string VARCHAR(31)
,@err_msg VARCHAR(300)
/*----------------------------------- Begin logic ----------------------------------------*/
SELECT @drv_nm = DRV.DRV_NM
FROM DRV
WHERE DRV.CST_PG_NM = @cst_pg_nm AND DRV.CST_NM = @cst_nm
/****************************************************************************************************************/
/****************************************************************************************************************/
/************************************************ BUILD THE PIC: ************************************************/
/****************************************************************************************************************/
/****************************************************************************************************************/
/************ Generate digits 1 to 5: ************/
-- First five letters of the surname (use asterisks to pad if surname is less than 5 characters long)
SELECT @dig1_5 = Left(@lst_nam + "*****", 5)
/************ Generate digits 6 and 7: ***********/
-- Initials from first and middle names (if only 1 initial, an asterisk is used for the 7th digit
IF @mdl_nam IS NOT NULL
SELECT @mdl_nam_init = Left(@mdl_nam, 1)
ELSE
SELECT @mdl_nam_init = "*"
SELECT @dig6_7 = Left(@fr_nam, 1) + @mdl_nam_init
/************ Generate digits 8 and 9: ***********/
-- 100 minus birth year (E.G. 100 - 46 = 54 for person born in 1946)
SELECT @birth_year = DatePart(year, @brth_dt) -- extract 4-digit year from full birth date
SELECT @birth_year = @birth_year % 100 -- extract 2-digit year: 1946 ==>> 46
SELECT @birth_year = 100 - @birth_year -- 46 ==>> 54; so, now @birth_year doesnt' contain birth year
IF @birth_year > 9 -- 2 digits
SELECT @dig8_9 = Convert(CHAR(2), @birth_year) -- like 54 ==>> '54'
ELSE -- 1 digit
SELECT @dig8_9 = "0" + Convert(CHAR(1), @birth_year) -- like 9 ==>> '09'
/************ Generate digit 11: ************/
-- Birth month: 1 -> B, 2 -> C, 3 -> D, 4 -> J, 5 -> K, 6 -> L, 7 -> M, 8 -> N, 9 -> W, 10 -> P, 11 -> Q, 12 -> R
SELECT @code_string = 'BCDJKLMNWPQR'
SELECT @birth_month = DatePart(month, @brth_dt)
SELECT @dig11 = SubString(@code_string, @birth_month, 1)
/************ Generate digit 12: ************/
-- Birth day (DAY part of birth date):
-- 01 -> A, 02 -> B, 03 -> C, 04 -> D, 05 -> E, 06 -> F, 07 -> G, 08 -> H, 09 -> Z, 10 -> S,
-- 11 -> J, 12 -> K, 13 -> L, 14 -> M, 15 -> N, 16 -> W, 17 -> P, 18 -> Q, 19 -> R, 20 -> 0,
-- 21 -> 1, 22 -> 2, 23 -> 3, 24 -> 4, 25 -> 5, 26 -> 6, 27 -> 7, 28 -> 8, 29 -> 9, 30 -> T, 31 -> U
SELECT @code_string = 'ABCDEFGHZSJKLMNWPQR0123456789TU'
SELECT @birth_day = DatePart(day, @brth_dt)
SELECT @dig12 = SubString(@code_string, @birth_day, 1)
/************ Generate digit 10: ************/
-- Check digit; it is computed now, in the end, when all other parts of PIC have been already built
SELECT @pic_without_chk_dgt = @dig1_5 + @dig6_7 + @dig8_9 + @dig11 + @dig12
EXEC cp_s_pic_chk_dgt @pic_without_chk_dgt = @pic_without_chk_dgt, @chk_dgt_output = @dig10 OUTPUT
IF @dig10 IS NULL /* unexpected character found in the PIC */ BEGIN
SELECT @pic_output = NULL
RETURN -1
END
/************ Build the result: ************/
SELECT @pic = @dig1_5 + @dig6_7 + @dig8_9 + @dig10 + @dig11 + @dig12
/****************************************************************************************************************/
/****************************************************************************************************************/
/************************************** ENSURE THAT THE PIC DOESN'T EXIST: **************************************/
/****************************************************************************************************************/
/****************************************************************************************************************/
/************ Make sure that the generated PIC is not already used for other driver: ************/
IF NOT EXISTS (SELECT 1 FROM DRV_LCN WHERE DRV_LCN_CD = @pic AND DRV_NM <> @drv_nm) BEGIN
SELECT @pic_output = @pic
RETURN 0 -- the generated PIC is ok (not used for other driver), exit the procedure
END
/************ Prepare substitution codes strings: ************/
-- Substitute #1: 1 -> A, 2 -> B, 3 -> C, 4 -> D, 5 -> E, 6 -> F, 7 -> G, 8 -> H, 9 -> Z, 0 -> S
SELECT @codes_1 = 'ABCDEFGHZS'
-- Substitute #1: 1 -> J, 2 -> K, 3 -> L, 4 -> M, 5 -> N, 6 -> W, 7 -> P, 8 -> Q, 9 -> R, 0 -> T
SELECT @codes_2 = 'JKLMNWPQRT'
-- Alternate birth month: 1 -> S, 2 -> T, 3 -> U, 4 -> 1, 5 -> 2, 6 -> 3, 7 -> 4, 8 -> 5, 9 -> 6, 10 -> 7, 11 -> 8, 12 -> 9
SELECT @codes_bm = 'STU123456789'
/************ Substitute step by step (maximum 17 steps) until a not existing combination found: ************/
/*-------------------------------------------------------------
Step 8th Digit 9th Digit 11th Digit
---------------------------------------------------------------
1 Substitute #1 Original Original
2 Substitute #2 Original Original
3 Original Substitute #1 Original
4 Original Substitute #2 Original
5 Substitute #1 Substitute #1 Original
6 Substitute #1 Substitute #2 Original
7 Substitute #2 Substitute #1 Original
8 Substitute #2 Substitute #2 Original
9 Original Original Alternate birth month
10 Substitute #1 Original Alternate birth month
11 Substitute #2 Original Alternate birth month
12 Original Substitute #1 Alternate birth month
13 Original Substitute #2 Alternate birth month
14 Substitute #1 Substitute #1 Alternate birth month
15 Substitute #1 Substitute #2 Alternate birth month
16 Substitute #2 Substitute #1 Alternate birth month
17 Substitute #2 Substitute #2 Alternate birth month
-------------------------------------------------------------*/
SELECT @dig8 = Left(@dig8_9, 1)
SELECT @dig9 = Right(@dig8_9, 1)
SELECT @pic_exists = 1 -- if this code reached then the generated PIC exists and must be rebuilt
SELECT @step = 1
WHILE (@step <= 17 AND @pic_exists = 1) BEGIN
-- *** Substitute 8th Digit: *** --
IF @step IN (1, 5, 6, 10, 14, 15) -- Substitute #1
BEGIN
SELECT @num_val = Convert(SMALLINT, @dig8)
SELECT @dig8_new = SubString(@codes_1, @num_val, 1)
END
ELSE
IF @step IN (2, 7, 8, 11, 16, 17) -- Substitute #2
BEGIN
SELECT @num_val = Convert(SMALLINT, @dig8)
SELECT @dig8_new = SubString(@codes_2, @num_val, 1)
END
ELSE
SELECT @dig8_new = @dig8 -- original
-- *** Substitute 9th Digit: *** --
IF @step IN (3, 5, 7, 14, 16) -- Substitute #1
BEGIN
SELECT @num_val = Convert(SMALLINT, @dig9)
SELECT @dig9_new = SubString(@codes_1, @num_val, 1)
END
ELSE
IF @step IN (4, 6, 8, 13, 15, 17) -- Substitute #2
BEGIN
SELECT @num_val = Convert(SMALLINT, @dig9)
SELECT @dig9_new = SubString(@codes_2, @num_val, 1)
END
ELSE
SELECT @dig9_new = @dig9 -- original
-- *** Substitute 11th Digit: *** --
IF @step BETWEEN 9 AND 17 -- Alternate birth month
SELECT @dig11_new = SubString(@codes_bm, @birth_month, 1)
ELSE
SELECT @dig11_new = @dig11 -- original
/************ Generate new check digit (because some characters have been changed): ************/
SELECT @pic_without_chk_dgt = @dig1_5 + @dig6_7 + @dig8_new + @dig9_new + @dig11_new + @dig12
EXEC cp_s_pic_chk_dgt @pic_without_chk_dgt = @pic_without_chk_dgt, @chk_dgt_output = @dig10 OUTPUT
IF @dig10 IS NULL /* unexpected character found in the PIC */ BEGIN
SELECT @pic_output = NULL
RETURN -1
END
/************ Build the result: ************/
SELECT @pic = @dig1_5 + @dig6_7 + @dig8_new + @dig9_new + @dig10 + @dig11_new + @dig12
/************ Make sure that the generated PIC is not already used for other driver: ************/
IF NOT EXISTS (SELECT 1 FROM DRV_LCN WHERE DRV_LCN_CD = @pic AND DRV_NM <> @drv_nm)
SELECT @pic_exists = 0 -- not used for other driver - exit the loop
SELECT @step = @step + 1
END
IF EXISTS (SELECT 1 FROM DRV_LCN WHERE DRV_LCN_CD = @pic AND DRV_NM <> @drv_nm)
BEGIN
SELECT @err_msg = "Error in cp_s_pic_new: PIC generation failed for customer " +
Convert(VARCHAR(12), @cst_nm) + " (" + @fr_nam + " " + @mdl_nam + " " + @lst_nam +
"). 17 substitution steps made but all suggested combinations already exist"
raiserror 17010 @err_msg
SELECT @pic_output = NULL
RETURN -1
END
SELECT @pic_output = @pic
RETURN 0
/*------------------------------- End of procedure ---------------------------------------*/
GO
/*
#############################################################################
# Procedure Name: cp_s_pic_chk_dgt
#============================================================================
# Description: accepts customer PIC and generates a check digit for it.
#
#############################################################################
# Parameters Type
# ---------- ----
# @pic_without_chk_dgt CHAR(11)
# @chk_dgt_output CHAR(1)
#############################################################################
# Variables Type
# --------- ----
#############################################################################
# Constants Type Value
# --------- ---- -----
# None
#############################################################################
# Stored procedures called Description
# ------------------------ -----------
#############################################################################
# Modification Log
#============================================================================
# Problem
# Report # Date UserID Description
# --------- ----------- -------- ----------
# Apr 04 2006 MPIMZUS initial creation - REL 12.3
#############################################################################
*/
IF EXISTS (SELECT Lower(name) FROM sysobjects WHERE name = 'cp_s_pic_chk_dgt')
DROP PROCEDURE cp_s_pic_chk_dgt
GO
/*--------------------------------- Begin procedure --------------------------------------*/
CREATE PROCEDURE cp_s_pic_chk_dgt
@pic_without_chk_dgt CHAR(11)
,@chk_dgt_output CHAR(1) OUTPUT
AS
/*------------------------------------ Declare -------------------------------------------*/
DECLARE @idx SMALLINT
,@add_flag SMALLINT
,@num_val SMALLINT
,@result SMALLINT
,@char CHAR(1)
,@err_msg VARCHAR(300)
/*----------------------------------- Begin logic ----------------------------------------*/
IF Len(@pic_without_chk_dgt) <> 11 BEGIN
SELECT @err_msg = "Error in cp_s_pic_chk_dgt: parameter @pic_without_chk_dgt has invalig length " +
Convert(VARCHAR(2), Len(@pic_without_chk_dgt)) + " - must be 11"
raiserror 17010 @err_msg
SELECT @chk_dgt_output = NULL
RETURN -1
END
/*
The check digit is computed by alternately adding and subtracting the numeric values
of the other characters of the code. Numeric values of character are:
-----------------------------------------
Char Num Value Char Num Value
-----------------------------------------
* 0 N 5
A 1 O 6
B 2 P 7
C 3 Q 8
D 4 R 9
E 5 S 2
F 6 T 3
G 7 U 4
H 8 V 5
I 9 W 6
J 1 X 7
K 2 Y 8
L 3 Z 9
M 4
-----------------------------------------*/
SELECT @idx = 1
SELECT @add_flag = 1 -- 1 = add (odd places), 0 = subtract (even places)
SELECT @result = 0
WHILE @idx <= 11 BEGIN
SELECT @char = SubString(@pic_without_chk_dgt, @idx, 1)
SELECT @num_val =
CASE WHEN @char IN ('0', '*') THEN 0
WHEN @char IN ('1', 'A', 'J') THEN 1
WHEN @char IN ('2', 'B', 'K', 'S') THEN 2
WHEN @char IN ('3', 'C', 'L', 'T') THEN 3
WHEN @char IN ('4', 'D', 'M', 'U') THEN 4
WHEN @char IN ('5', 'E', 'N', 'V') THEN 5
WHEN @char IN ('6', 'F', 'O', 'W') THEN 6
WHEN @char IN ('7', 'G', 'P', 'X') THEN 7
WHEN @char IN ('8', 'H', 'Q', 'Y') THEN 8
WHEN @char IN ('9', 'I', 'R', 'Z') THEN 9
ELSE -1
END
IF @num_val = -1
BEGIN
SELECT @err_msg = "Error in cp_s_pic_chk_dgt: unexpected character <" + @char + "> in PIC"
raiserror 17010 @err_msg
SELECT @chk_dgt_output = NULL
RETURN -1
END
-- If odd place - add current value to result; if even - subtract current value from result:
IF @add_flag = 1
BEGIN
SELECT @result = @result + @num_val
SELECT @add_flag = 0
END
ELSE -- @add_flag = 0
BEGIN
SELECT @result = @result - @num_val
SELECT @add_flag = 1
END
SELECT @idx = @idx + 1
END -- the WHILE loop
SELECT @result = ABS(@result) -- must not be negative
IF @result > 9 -- more than one digit
SELECT @result = @result % 10 -- take the last digit
SELECT @chk_dgt_output = Convert(CHAR(1), @result)
RETURN 0
/*------------------------------- End of procedure ---------------------------------------*/
GO
/*
#############################################################################
# Procedure Name: cp_s_drv_lcn_qstnr_existing
#============================================================================
# Description: Selects the customer's answers provided to the questionnaire.
#############################################################################
# Modification Log
#============================================================================
# Problem
# Report # Date UserID Description
# --------- ----------- -------- ----------
# Jun 08 2006 MPIMZUS initial creation - REL 12.3
#############################################################################
*/
IF EXISTS (SELECT Lower(name) FROM sysobjects WHERE name = 'cp_s_drv_lcn_qstnr_existing')
DROP PROCEDURE cp_s_drv_lcn_qstnr_existing
GO
/*--------------------------------- Begin procedure --------------------------------------*/
CREATE PROCEDURE cp_s_drv_lcn_qstnr_existing
@bsns_srvc_nm INT /* session id */
AS
/*------------------------------------ Declare -------------------------------------------*/
DECLARE @bsns_srvc_cd CHAR(5) /* Transaction type */
,@resp_nm INTEGER
,@efc_dt DATETIME
CREATE TABLE #RESULT
(ID_REF_TXT CHAR(5)
,SEQ_NM SMALLINT
,TXT VARCHAR(255)
,LVL_NM INTEGER
,CMPNT_NM INTEGER
,TYP_CD CHAR(5)
,YES_ANSW CHAR(1) NULL
,OPN_ANSW INTEGER NULL)
/*----------------------------------- Begin logic ----------------------------------------*/
SELECT @bsns_srvc_cd = BSNS_SRVC_CD
,@resp_nm = DRV_LCN_QSTNR_RESP_NM
,@efc_dt = DRV_LCN_QSTNR_RESP_DT
FROM DRV_LCN_QSTNR_RESP
WHERE BSNS_SRVC_NM = @bsns_srvc_nm
/*** Populate #RESULT with default questions for the transaction type: ***/
INSERT INTO #RESULT
SELECT qstn_per_tx_type.DRV_LCN_QSTNR_CMPNT_ID_REF_TXT as ID_REF_TXT
,qstn_per_tx_type.DL_QSTNR_CMPNT_DSPL_SEQ_NM as SEQ_NM
,qstn_catalog.DRV_LCN_QSTNR_CMPNT_TXT as TXT
,qstn_catalog.DRV_LCN_QSTNR_CMPNT_LVL_NM as LVL_NM
,qstn_catalog.DRV_LCN_QSTNR_CMPNT_NM as CMPNT_NM
,qstn_catalog.DRV_LCN_QSTNR_CMPNT_TYP_CD as TYP_CD
,NULL as YES_ANSW
,NULL as OPN_ANSW
FROM DRV_LCN_QSTNR_BSNS_SRVC qstn_per_tx_type
,DRV_LCN_QSTNR_CMPNT_DSC qstn_catalog
WHERE qstn_per_tx_type.BSNS_SRVC_CD = @bsns_srvc_cd AND
qstn_per_tx_type.DRV_LCN_QSTNR_CMPNT_NM = qstn_catalog.DRV_LCN_QSTNR_CMPNT_NM AND
@efc_dt BETWEEN
qstn_per_tx_type.DRV_LCN_QSTNR_BSNS_SRVC_EFC_DT AND qstn_per_tx_type.DRV_LCN_QSTNR_BSNS_SRVC_EXP_DT AND
@efc_dt BETWEEN
qstn_catalog.DRV_LCN_QSTNR_CMPNT_EFC_DT AND qstn_catalog.DRV_LCN_QSTNR_CMPNT_EXP_DT
/*** Populate 'yes/no' answers: ***/
UPDATE #RESULT
SET YES_ANSW = yes_resp.DRV_LCN_QSTNR_YES_ANSW_IND
FROM DRV_LCN_QSTNR_QSTN_RESP yes_resp
WHERE yes_resp.DRV_LCN_QSTNR_CMPNT_NM = #RESULT.CMPNT_NM AND
yes_resp.DRV_LCN_QSTNR_RESP_NM = @resp_nm
/*** Populate optional answers: ***/
UPDATE #RESULT
SET OPN_ANSW = opn_resp.DRV_LCN_QSTNR_OPN_RESP_ANSW_NM
FROM DRV_LCN_QSTNR_OPN_RESP opn_resp
WHERE opn_resp.DRV_LCN_QSTNR_CMPNT_NM = #RESULT.CMPNT_NM AND
opn_resp.DRV_LCN_QSTNR_RESP_NM = @resp_nm
/*------------------------------- Return resultset ---------------------------------------*/
SELECT ID_REF_TXT
,SEQ_NM
,TXT
,LVL_NM
,CMPNT_NM
,TYP_CD
,YES_ANSW
,OPN_ANSW
FROM #RESULT
ORDER BY SEQ_NM
/*------------------------------- End of procedure ---------------------------------------*/
GO
/*
#############################################################################
# Procedure Name: cp_s_oop_addr_auth
#============================================================================
# Description: indicates whether the out of province authorization has previously been collected.
#
#############################################################################
# Parameters Type
# ---------- ----
# @cst_pg_nm INTEGER
# @cst_nm INTEGER
#############################################################################
# Modification Log
#============================================================================
# Problem
# Report # Date UserID Description
# --------- ----------- -------- ----------
# Aug 08 2006 MPIMZUS initial creation - REL 12.3
#############################################################################
*/
IF EXISTS (SELECT Lower(name) FROM sysobjects WHERE name = 'cp_s_oop_addr_auth')
DROP PROCEDURE cp_s_oop_addr_auth
GO
/*--------------------------------- Begin procedure --------------------------------------*/
CREATE PROCEDURE cp_s_oop_addr_auth
(@cst_pg_nm INTEGER
,@cst_nm INTEGER)
AS
/*------------------------------------ Declare -------------------------------------------*/
DECLARE @oop_dl_auth_dt DATETIME
,@oop_dl_auth_dt_1 DATETIME
,@oop_dl_auth_dt_2 DATETIME
create table #RESULT (AUTH_COLLECTED SMALLINT /* 0 or 1 */)
/*----------------------------------- Begin logic ----------------------------------------*/
/************ Select @oop_dl_auth_dt = effective date from the most recent CST_CVC_ADDR or CST_MAL_ADDR ************/
/************ row associated with an 'out of province MB' address: ************/
select @oop_dl_auth_dt_1 = Max(CVC_ADDR_EFC_DT)
from CST_CVC_ADDR
where CST_PG_NM = @cst_pg_nm and
CST_NM = @cst_nm and
PRVN_CD <> 'MB'
select @oop_dl_auth_dt_2 = Max(MAL_ADDR_EFC_DT)
from CST_MAL_ADDR
where CST_PG_NM = @cst_pg_nm and
CST_NM = @cst_nm and
PRVN_CD <> 'MB'
if (@oop_dl_auth_dt_1 is not null) and (@oop_dl_auth_dt_1 > @oop_dl_auth_dt_2)
select @oop_dl_auth_dt = @oop_dl_auth_dt_1
else
select @oop_dl_auth_dt = @oop_dl_auth_dt_2
if (@oop_dl_auth_dt is null) begin
insert into #RESULT (AUTH_COLLECTED) values (0)
select AUTH_COLLECTED from #RESULT
return
end
/************ Check using the following logic: ************/
-- IF (there has been a DL issuance transaction (ISNDL, REDRL, REFDL, RWDRL, CHDRL) since @oop_dl_auth_dt)
-- OR
-- (
-- the DL was active (ACTIV, PENRE, PENSU) at the time of the @oop_dl_auth_dt
-- AND
-- the DRV_LCN_TERM_STAT was not inserted by LODLT (the DL conversion) transaction
-- ) then
-- return TRUE
-- ELSE
-- return FALSE
-- IF there has been a DL issuance transaction (ISNDL, REDRL, REFDL, RWDRL, CHDRL) since @oop_dl_auth_dt - return TRUE:
if exists (select 1
from BSNS_SRVC
where CST_PG_NM = @cst_pg_nm and
CST_NM = @cst_nm and
@oop_dl_auth_dt >= BSNS_SRVC_EFC_DT and
BSNS_SRVC_CD in ('ISNDL', 'REDRL', 'REFDL', 'RWDRL', 'CHDRL'))
begin
insert into #RESULT (AUTH_COLLECTED) values (1)
select AUTH_COLLECTED from #RESULT
return
end
-- ...check also in archive:
if exists (select 1
from insprd1_arcv..BSNS_SRVC
where CST_PG_NM = @cst_pg_nm and
CST_NM = @cst_nm and
@oop_dl_auth_dt >= BSNS_SRVC_EFC_DT and
BSNS_SRVC_CD in ('ISNDL', 'REDRL', 'REFDL', 'RWDRL', 'CHDRL'))
begin
insert into #RESULT (AUTH_COLLECTED) values (1)
select AUTH_COLLECTED from #RESULT
return
end
-- IF the DL was active (ACTIV, PENRE, PENSU) at the time of the @oop_dl_auth_dt AND
-- the DRV_LCN_TERM_STAT was not inserted by LODLT (the DL conversion) transaction - return TRUE (othervise - FALSE):
if exists (select 1
from DRV_LCN_TERM
where CST_PG_NM = @cst_pg_nm and
CST_NM = @cst_nm and
@oop_dl_auth_dt between DRV_LCN_TERM_EFC_DT and DRV_LCN_TERM_EXP_DT and
DRV_LCN_TERM_NM in (select DRV_LCN_TERM_NM
from DRV_LCN_TERM_STAT
where DRV_LCN_TERM_STAT_CD in ('ACTIV', 'PENRE', 'PENSU') and
@oop_dl_auth_dt between
DRV_LCN_TERM_STAT_EFC_DT and DRV_LCN_TERM_STAT_EXP_DT and
BSNS_SRVC_NM not in (select BSNS_SRVC_NM
from BSNS_SRVC
where BSNS_SRVC_CD = 'LODLT')))
insert into #RESULT (AUTH_COLLECTED) values (1)
else
insert into #RESULT (AUTH_COLLECTED) values (0)
select AUTH_COLLECTED from #RESULT
/*------------------------------- End of procedure ---------------------------------------*/
GO