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