SQL & PL/SQL

The following is part of a package I have with a dynamic sql query. If there is a better way to write this type of query please let me know but the problem of the undeclared identifier is really bothering me. Please help.


CREATE OR REPLACE PACKAGE BODY BLU_Member_Package AS
/******************************************************************************
NAME: MCA_Member_Package
PURPOSE: To provide MasterCard Advisors with a place to store procedures
and functions that relate to Members
Procedure(s)/Function(s):

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 3/21/2005 WJR 1. Created this package body.
******************************************************************************/

PROCEDURE BLUE_MEMBER_SEARCH_NAME (
pi_memb_sel IN VARCHAR2,
pi_last_sel IN VARCHAR2,
pi_first_sel IN VARCHAR2,
pi_last_name IN VARCHAR2,
pi_first_name IN VARCHAR2,
pi_mid_ini IN VARCHAR2,
pi_dob IN VARCHAR2,
pi_memb_no IN VARCHAR2,
po_out_message OUT varchar2,
po_memb_cur IN OUT BLU_Member_Package.rcRetMemberInfo) is
sStrSql VARCHAR2(6000); -- Holds dynamic sql query
ls_last_sel VARCHAR2(5); -- Last name selection. Valid values E : exact, P : partial
ls_first_sel VARCHAR2(5); -- First name selection. Valid values E : exact, P : partial
ls_birth_dt_sel VARCHAR2(5); -- Birth date selection. Valid values E : exact, P : partial
ls_memb_sel VARCHAR2(5); -- member selection. Valid values A : All, S: Subscribers Only
ls_last_name VARCHAR2(35); -- Given last name of subscriber
ls_first_name VARCHAR2(30); -- Given first name of subscriber
ls_mid_ini CHAR(1); -- Given middle initial of subscriber
ls_dob VARCHAR2(8); -- Given date of birth of subscriber
ls_memb_no CHAR(2); -- Given person number of subscriber
ls_sub_mem_no VARCHAR2(2) := '01'; -- Person number of primary subscriber
vAddrType VARCHAR2(2);
BEGIN
ls_memb_sel := UPPER(pi_memb_sel) ;
ls_last_sel := UPPER(pi_last_sel) ;
ls_first_sel := UPPER(pi_first_sel) ;
ls_last_name := UPPER(pi_last_name) ;
ls_first_name := UPPER(pi_first_name);
ls_mid_ini := UPPER(pi_mid_ini) ;
ls_dob := pi_dob ;
ls_memb_no := pi_memb_no ;
vAddrType := 'A1';
sStrSql := 'SELECT X.GROUP_PREFIX, X.GROUP_SUBSCRIBER_ID, (X.GROUP_PREFIX || X.GROUP_SUBSCRIBER_ID) as SUBSCRIBER_ID, ';
sStrSql := sStrSql || 'X.LAST_NAME, X.FIRST_NAME, X.MIDDLE_INITIAL, X.PERSON_NUMBER, X.DATE_OF_BIRTH, X.ADDRESS_LINE_1, X.ADDRESS_LINE_2, X.COUNTRY, ';
sStrSql := sStrSql || 'X.CITY, X.STATE, X.ZIP_CODE, X.DATE_OF_DEATH, X.SOCIAL_SEC_NO, X.GENDER, X.RELATIONSHIP_CODE, X.MARITAL_STATUS, X.EFFECTIVE_DATE, ';
sStrSql := sStrSql || 'X.TERM_REASON, X.TERM_DATE, X.HOME_PHONE_NUMBER, X.MEDICARE_NO, X.TITLE, X.OTHR_MAR_ST, Y.BILLADDR1, Y.BILLADDR2, Y.BCITY, Y.BSTATE, Y.BZIP, Y.BCOUNTRY, ';
sStrSql := sStrSql || 'Y1.SUB_TERM_REASON, Y1.SUB_TERM_DATE, Y1.SUB_LAST_NM, Y1.SUB_FIRST_NM, Y1.SUB_MIDDLE_NM FROM (SELECT A.LAST_NAME, A.FIRST_NAME, A.MIDDLE_INITIAL, A.DATE_OF_BIRTH, ';
sStrSql := sStrSql || 'A.ADDRESS_LINE_1, A.ADDRESS_LINE_2, A.COUNTRY, A.CITY, A.STATE, A.ZIP_CODE, A.PERSON_NUMBER, C.DATE_OF_DEATH, A.SOCIAL_SEC_NO, A.GENDER, ';
sStrSql := sStrSql || 'C.RELATIONSHIP_CODE, A.MARITAL_STATUS, C.TERM_REASON, C.TERM_DATE, A.HOME_PHONE_NUMBER, A.MEDICARE_NO, A.USER_DEFINED_1 TITLE, ';
sStrSql := sStrSql || 'A.USER_DEFINED_2 OTHR_MAR_ST, C.GROUP_PREFIX, C.GROUP_SUBSCRIBER_ID, C.EFFECTIVE_DATE

The variable holds the value until this last concatenation and then becomes an undeclared identifier