Can this SQL statement be stored and run as a stored procedure? Can it be
done without re-writing using cursors, etc...?? All help is appreciated..
/* OLD - shortens certificate number length to 9 on non-bme business */
/* UPDATE REPOSIT.LTR_EXTRACT_TEMP SET FIELD_13 = SUBSTR(FIELD_13,1,9) */
/* WHERE FIELD_105 <> 'BME' */
/* */
/* COMMIT */
/* */
/* appends entire daily WinUCS letter extract to history table */
INSERT INTO REPOSIT.LTR_EXTRACT_INFO
SELECT * FROM REPOSIT.LTR_EXTRACT_TEMP
/
COMMIT
/
/* removes all records from table before next letter selection run */
DELETE FROM REPOSIT.LTR_MAIL_TO_INFO
/
COMMIT
/
/* appends 'attending' mailing data records to table for certain letter
types */
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE,
MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE,
MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
UPPER(FIELD_29), UPPER(FIELD_30), UPPER(FIELD_31), UPPER(FIELD_32),
UPPER(FIELD_33), FIELD_34
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 1
AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* appends 'facility' mailing data records to table for certain letter types
*/
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE,
MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE,
MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
UPPER(FIELD_23), UPPER(FIELD_24), UPPER(FIELD_25), UPPER(FIELD_26),
UPPER(FIELD_27), FIELD_28
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 2
AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* OLD - appends 'insurer' mailing data records to table for certain letter
types and */
/* where client code does not equal 'bme', this prevents printing letters to
BCBSME */
/* INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE, */
/* MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE, */
/* MAIL_TO_ZIP_CD ) */
/* SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD, */
/* UPPER(FIELD_200), UPPER(FIELD_213), UPPER(FIELD_214), UPPER(FIELD_215),
*/
/* UPPER(FIELD_216), FIELD_217 */
/* FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE, */
/* REPOSIT.LTR_LETTER_RECIPIENT_INFO */
/* WHERE RECIPIENT_CD = 3 */
/* AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR */
/* AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD */
/* AND FIELD_105 != 'BME' */
/* */
/* appends 'patient' mailing data records to table for certain letter types
*/
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE,
MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE,
MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
UPPER(FIELD_9||' '||FIELD_11), UPPER(FIELD_14), UPPER(FIELD_15),
UPPER(FIELD_16), UPPER(FIELD_17), FIELD_18
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 4
AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* OLD - appends 'patient' mailing data records to table when patient name
and address */
/* data does not match subscriber's */
/* INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE, */
/* MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE, */
/* MAIL_TO_ZIP_CD ) */
/* SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD, */
/* UPPER(FIELD_9||' '||FIELD_11), UPPER(FIELD_14), UPPER(FIELD_15), */
/* UPPER(FIELD_16), UPPER(FIELD_17), FIELD_18 */
/* FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE, */
/* REPOSIT.LTR_LETTER_RECIPIENT_INFO */
/* WHERE RECIPIENT_CD = 5 */
/* AND ((FIELD_9||FIELD_10||FIELD_11 != REPLACE(FIELD_126,' ')||FIELD_127)
*/
/* AND (FIELD_14||FIELD_16||FIELD_17||FIELD_18 !=
FIELD_129||FIELD_130||FIELD_131||FIELD_132)) */
/* AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR */
/* AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD */
/* */
/* appends 'physician' mailing data records to table for certain letter
types */
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE,
MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_ADDRESS2, MAIL_TO_CITY,
MAIL_TO_STATE,
MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
UPPER(FIELD_29), UPPER(FIELD_30), UPPER(FIELD_31), UPPER(FIELD_32),
UPPER(FIELD_33), FIELD_34
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 6
AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
/* appends 'subscriber' mailing data records to table for certain letter
types */
INSERT INTO REPOSIT.LTR_MAIL_TO_INFO ( EXTRACT_DATE, EXTRACT_ROW_NB,
MAIL_TO_TYPE,
MAIL_TO_NAME, MAIL_TO_ADDRESS1, MAIL_TO_CITY, MAIL_TO_STATE,
MAIL_TO_ZIP_CD )
SELECT EXTRACT_DATE, EXTRACT_ROW_NB,
LTR_LETTER_DECODE.LETTER_CD||RECIPIENT_CD,
UPPER(FIELD_126)||' '||UPPER(FIELD_127), UPPER(FIELD_129),
UPPER(FIELD_130),
FIELD_131, FIELD_132
FROM REPOSIT.LTR_EXTRACT_TEMP, REPOSIT.LTR_LETTER_DECODE,
REPOSIT.LTR_LETTER_RECIPIENT_INFO
WHERE RECIPIENT_CD = 7
AND LTR_EXTRACT_TEMP.FIELD_1 = LTR_LETTER_DECODE.LETTER_DESCR
AND LTR_LETTER_DECODE.LETTER_CD = LTR_LETTER_RECIPIENT_INFO.LETTER_CD
/
COMMIT
/
/* sets exception flag to 'n' where name, address, city, state or zip_cd
data is missing */
UPDATE REPOSIT.LTR_MAIL_TO_INFO
SET MAIL_TO_EXCEPTION = 'M'
WHERE EXTRACT_ROW_NB IN ( SELECT EXTRACT_ROW_NB
FROM REPOSIT.LTR_MAIL_TO_INFO
WHERE MAIL_TO_NAME IS NULL
OR MAIL_TO_ADDRESS1 IS NULL
OR MAIL_TO_CITY IS NULL
OR MAIL_TO_STATE IS NULL
OR MAIL_TO_ZIP_CD IS NULL)
/
COMMIT
/
/* sets exception flag to 'n' where plan code value is missing in
ltr_logo_info table */
UPDATE REPOSIT.LTR_MAIL_TO_INFO
SET MAIL_TO_EXCEPTION = 'P'
WHERE EXTRACT_ROW_NB IN ( SELECT EXTRACT_ROW_NB
FROM ( SELECT EXTRACT_ROW_NB, PLAN_CD
FROM REPOSIT.LTR_EXTRACT_TEMP, LTR_LOGO_INFO
WHERE FIELD_189 = PLAN_CD (+)
AND PLAN_CD IS NULL))
/
COMMIT
/
Received on Thu Feb 11 1999 - 12:58:30 CST