ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 1000; 
ALTER SESSION SET OPTIMIZER_MODE='ALL_ROWS'; 
ALTER SESSION SET HASH_AREA_SIZE=131720000;


COLUMN spool_text_source new_value spool_text
SET term OFF

SELECT '/u02/batchProcess/sendNCOAAddressFileVerification/HECPRD.FTP.NCOA.HDATA'||TO_CHAR(ADD_MONTHS(SYSDATE,3),'Q')||'01.TXT'spool_text_source FROM dual;


SET termout ON

SPOOL &spool_text


SET AUTOCOMMIT ON
COLUMN NCOA4 format a4   justify LEFT
COLUMN lastName35 format a35  justify LEFT
COLUMN firstName30 format a30  justify LEFT
COLUMN middleInitial1 format a1  justify LEFT
COLUMN suffix10 format a10  justify LEFT
COLUMN ssn9 format a9  justify LEFT
COLUMN gender1 format a1  justify LEFT
COLUMN dateOfBirth8 format a8  justify LEFT
COLUMN dateLastUpdated21 format a21  justify LEFT
COLUMN addressLineOne40 format a40  justify LEFT
COLUMN addressLineTwo40 format a40  justify LEFT
COLUMN city50 format a50  justify LEFT
COLUMN state2 format a2  justify LEFT
COLUMN zipCodeWithExtension10 format a10  justify LEFT
COLUMN empty7 format a7  justify LEFT
COLUMN icn17 format a17  justify LEFT
COLUMN empty114 format a114  justify LEFT




SET ARRAYSIZE 5000
SET WRAP OFF
SET HEADING OFF
SET LINESIZE 399
SET COLSEP ''
SET TERM OFF
SET ECHO OFF
SET PAGESIZE 0
SET FEEDBACK OFF

SELECT 
       'NCOA' AS ncoa4, UPPER (SUBSTR (last_name, 1, 30)) AS lastname35,
       UPPER (SUBSTR (first_name, 1, 35)) AS firstname30,
       UPPER (SUBSTR (middle_name, 1, 1)) AS middleinitial1,
       UPPER (SUBSTR (suffix, 1, 10)) AS suffix10, ssn AS ssn9,
       gender_code AS gender1, date_of_birth AS dateofbirth8,
       CASE
          WHEN address.address_change_effective_date IS NULL
             THEN NULL
          ELSE    TO_CHAR
                     (address.address_change_effective_date,
                      'YYYYMMDDHHMISS'
                     )
               || '-0600'
       END AS datelastupdated21,
       address.address_line1 AS addresslineone40,
       address.address_line2 AS addresslinetwo40, address.city AS city50,
       address.state_code AS state2,
       CASE
          WHEN address.zip_code IS NULL
             THEN NULL
          ELSE CASE
          WHEN address.zip_plus_4 IS NULL
             THEN address.zip_code
          ELSE    address.zip_code
               || '-'
               || address.zip_plus_4
       END
       END AS zipcodewithextension10,
       ' ' AS empty7, SUBSTR (t.vpid_value, 7, 17) AS icn17, '' AS empty114
  FROM person person INNER JOIN rpt_psim_traits t ON (    person.vpid_value =
                                                                  t.vpid_value
                                                      AND person.death_date_txt IS NULL
                                                     )
       INNER JOIN address address ON (    person.person_id = address.person_id
                                      AND address.std_addresstype_id =
                                                                     '1449012'
                                      AND address.address_line1 IS NOT NULL
                                      AND address.city IS NOT NULL
                                      AND address.zip_code IS NOT NULL
                                      AND address.country_code = 'USA'
                                     )

/

SPOOL OFF
