[CODE] WHENEVER SQLERROR EXIT 9; spool reg_rep.txt; /* DECLARING INPUT FIELDS*/ CREATE OR REPLACE DIRECTORY reg_dir as '/home/mbatham'; GRANT READ, WRITE ON DIRECTORY REG_DIR TO PUBLIC; DECLARE APP_NO VARCHAR2(26); CUST_NO NUMBER(18); APP_DAT VARCHAR2(10); CREDIT_CLASS VARCHAR2(2); ACC_NO NUMBER(18,0); TOT_ANN_TRNOVR NUMBER(22,2); GROSSMNTHLYINCME NUMBER(15,0); REQ_LIM_LOAN_AMNT NUMBER(22,2); TERM NUMBER(3, 0); DECISION_DATE VARCHAR2(10); DECISION_COD VARCHAR2(1); LOAN_PURPOSE VARCHAR2(2); NCA_IN_ACT_IND VARCHAR2(1); CALLER_COD NUMBER(3,0); PROD_NO NUMBER(6,0); NCA_JURISTIC_IND VARCHAR2(1); NON_RESIDENT VARCHAR2(1); RACE VARCHAR2(1); ID_TYPE VARCHAR2(3); ID_NO VARCHAR2(16); FIN_DATE VARCHAR2(10); GENDER VARCHAR2(1); MRKTINGGRP_COD VARCHAR2(2); CURR_RUN_D VARCHAR(8); PREV_BUS_D VARCHAR(8); reg_dir VARCHAR2(10); /*DWAPLC01*/ WS_RECRD_ID_N VARCHAR2(1); WS_APLIC_N VARCHAR2(26); WS_HOLD_APLIC_N VARCHAR2(26); WS_CUST_N NUMERIC(10); WS_APLIC_D VARCHAR2(10); WS_CRPROV_C NUMERIC(10); WS_CLASS_C VARCHAR2(2); WS_ACNT_N VARCHAR2(16); WS_TURNOVR_A NUMBER(16,2); WS_ASSET_VALU_A DECIMAL(16,2); WS_MTHLY_GROS_INCOM_A DECIMAL(16,2); WS_CREDT_A DECIMAL(16,2); WS_TERM_MM NUMBER(3,0); WS_DECISION_D VARCHAR2(8); WS_DECISION_C VARCHAR2(1); WS_CREDT_PURPOS_C VARCHAR2(2); WS_EMERG_TYP_C VARCHAR2(2); WS_IN_NCA_Y VARCHAR2(1); WS_TRGT_SOURC_N NUMERIC(3); WS_PROD_N NUMERIC(5); WS_JURISTIC_Y VARCHAR2(1); WS_NON_RES_Y VARCHAR2(1); WS_RACE_C VARCHAR2(1); WS_ID_TYPE_C VARCHAR2(6); WS_ID_N VARCHAR2(60); WS_GEND_C VARCHAR2(1); WS_TABLEDATA VARCHAR2(250); CURSOR REG_REP IS SELECT A.T_160_FE_GEN_APP_NO, A.T_149_FE_GEN_CUST_NO, A.T_161_FE_GEN_APP_DAT, A.T_10070_FE_GEN_CREDIT_CLASS, A.T_169_FE_GEN_ACC_NO, A.T_3026_AFF_E_TOT_ANN_TRNOVR, A.T_7669_CPM_CUST_GROSSMNTHLYINC, A.T_171_FE_GEN_REQ_LIM_LOAN_AMNT, C.T_9804_CALL_TAKUP_TERM, C.T_10327_CALL_DEC_DECISION_DATE, B.T_10824_FIN_DECISION_COD, D.T_10577_PROD_LN_LOAN_PURPOSE, A.T_10071_FE_GEN_NCA_IN_ACT_IND, A.T_154_FE_GEN_CALLER_COD, A.T_170_FE_GEN_PROD_NO, A.T_10072_FE_GEN_NCA_JURISTIC_IN, A.T_7691_CPM_CUST_NON_RESIDENT, A.T_182_PS_RACE, A.T_150_FE_GEN_ID_TYPE, A.T_151_FE_GEN_ID_NO, c.T_9782_CALL_FIN_FINLISE_DATE, A.T_7651_CPM_CUST_GENDER, E.DTE_PREV_BUS_D, E.DTE_CURR_RUN_D -- /* A.T_959_FE_GEN_APP_MRKTINGGRP_COD*/ FROM MAIN_APPLICANT A, DA_RESULTS_2 B, CALL_TYPE_FIELDS C, MAIN_APP_PROD_SPECIFIC D, BEHVDATE E WHERE A.SYS_RECORDKEY = B.SYS_RECORDKEY AND B.SYS_RECORDKEY = C.SYS_RECORDKEY AND C.SYS_RECORDKEY = D.SYS_RECORDKEY AND A.T_160_FE_GEN_APP_NO IS NOT NULL AND A.T_160_FE_GEN_APP_NO <> ' ' AND A.T_154_FE_GEN_CALLER_COD = 51 AND ( A.T_161_FE_GEN_APP_DAT >= E.DTE_PREV_BUS_D AND A.T_161_FE_GEN_APP_DAT < E.DTE_CURR_RUN_D) OR (C.T_10327_CALL_DEC_DECISION_DATE >= E.DTE_PREV_BUS_D AND C.T_10327_CALL_DEC_DECISION_DATE < E.DTE_CURR_RUN_D) OR (C.T_9782_CALL_FIN_FINLISE_DATE >= E.DTE_PREV_BUS_D AND C.T_9782_CALL_FIN_FINLISE_DATE < E.DTE_CURR_RUN_D) ORDER BY A.T_160_FE_GEN_APP_NO ASC ; BEGIN FETCH REG_REP INTO APP_NO, CUST_NO, APP_DAT, CREDIT_CLASS, ACC_NO, TOT_ANN_TRNOVR, GROSSMNTHLYINCME, REQ_LIM_LOAN_AMNT, TERM, DECISION_DATE, DECISION_COD, LOAN_PURPOSE, NCA_IN_ACT_IND, CALLER_COD, PROD_NO, NCA_JURISTIC_IND, NON_RESIDENT, RACE, ID_TYPE, ID_NO, FIN_DATE, GENDER, PREV_BUS_D, CURR_RUN_D; LOOP EXIT WHEN REG_REP%FOUND = FALSE; IF DECISION_COD = 'B' THEN WS_DECISION_C := ' '; ELSIF DECISION_COD = 'R' THEN WS_DECISION_C := 'D'; ELSE WS_DECISION_C := DECISION_COD; END IF; IF NCA_IN_ACT_IND = ' ' THEN WS_IN_NCA_Y:= 'N'; ELSE WS_IN_NCA_Y:= NCA_IN_ACT_IND; END IF; IF NCA_JURISTIC_IND = 'Y' OR NCA_JURISTIC_IND = 'N' THEN WS_JURISTIC_Y:= NCA_JURISTIC_IND; ELSIF NCA_JURISTIC_IND = 'J' THEN WS_JURISTIC_Y:='Y'; ELSE WS_JURISTIC_Y:='N'; END IF; IF NON_RESIDENT = ' ' THEN WS_NON_RES_Y:='N'; ELSE WS_NON_RES_Y:= NON_RESIDENT; END IF; IF RACE = 'A' OR RACE = 'B' OR RACE = 'C' OR RACE = 'W' OR RACE = 'N' THEN WS_RACE_C:= RACE; ELSE WS_RACE_C:= 'U'; END IF; IF ID_TYPE = '001' THEN WS_ID_TYPE_C := '01'; ELSIF ID_TYPE = '002' THEN WS_ID_TYPE_C := '02'; ELSIF ID_TYPE = '003' THEN WS_ID_TYPE_C := '03'; ELSIF ID_TYPE = '004' THEN WS_ID_TYPE_C := '04'; ELSIF ID_TYPE = '005' THEN WS_ID_TYPE_C := '05'; ELSIF ID_TYPE = '006' THEN WS_ID_TYPE_C := '06'; ELSIF ID_TYPE = '007' THEN WS_ID_TYPE_C := '07'; ELSIF ID_TYPE = '008' THEN WS_ID_TYPE_C := '08'; ELSIF ID_TYPE = '009' THEN WS_ID_TYPE_C := '09'; ELSE WS_ID_TYPE_C := ID_TYPE; END IF; IF GENDER = ' ' THEN WS_GEND_C:='U'; ELSE WS_GEND_C:= GENDER; END IF; IF LOAN_PURPOSE = 01 THEN WS_CREDT_PURPOS_C:= 'HO'; ELSIF LOAN_PURPOSE = 02 THEN WS_CREDT_PURPOS_C:= 'FU'; ELSIF LOAN_PURPOSE = 03 THEN WS_CREDT_PURPOS_C:= 'VH'; ELSIF LOAN_PURPOSE = 04 THEN WS_CREDT_PURPOS_C:= 'DE'; ELSIF LOAN_PURPOSE = 05 THEN WS_CREDT_PURPOS_C:= 'ED'; ELSIF LOAN_PURPOSE = 06 THEN WS_CREDT_PURPOS_C:= 'EM'; ELSIF LOAN_PURPOSE = 07 THEN WS_CREDT_PURPOS_C:= 'OT'; ELSE WS_CREDT_PURPOS_C:= 'OT'; END IF; -- IF MRKTINGGRP_COD = '54' THEN -- WS_CLASS_C := 'US'; -- ELSE -- WS_CLASS_C := 'OT'; -- END IF; -- WS_DECISION_D:= DECISION_DATE; WS_APLIC_D:= APP_DAT; WS_TURNOVR_A:= TOT_ANN_TRNOVR; WS_TERM_MM:= TERM; WS_ASSET_VALU_A:= 0; WS_CREDT_A:= REQ_LIM_LOAN_AMNT; WS_TRGT_SOURC_N:= CALLER_COD; WS_PROD_N:= PROD_NO; WS_ID_N:= ID_NO; WS_MTHLY_GROS_INCOM_A:= GROSSMNTHLYINCME; WS_RECRD_ID_N:= '2'; WS_APLIC_N:= APP_NO; WS_CUST_N:= CUST_NO; WS_ACNT_N:= ACC_NO; WS_CLASS_C:= CREDIT_CLASS; WS_CRPROV_C:= 0; WS_EMERG_TYP_C:= ' '; WS_TABLEDATA:= 'INSERT INTO DWAPLC01 (RECRD_ID_N, APLIC_N, CUST_N, APLIC_D, CRPROV_C, CLASS_C, ACNT_N, TURNOVR_A, ASSET_VALU_A, MTHLY_GROS_INCOM_A, CREDT_A , TERM_MM, DECISION_D, DECISION_C, CREDT_PURPOS_C, EMERG_TYP_C, IN_NCA_Y, TRGT_SOURC_N, PROD_N, JURISTIC_Y, NON_RES_Y, RACE_C, ID_TYPE_C, ID_N,GEND_C) VALUES (WS_RECRD_ID_N, WS_APLIC_N, WS_CUST_N, WS_APLIC_D, WS_CRPROV_C, WS_CLASS_C, WS_ACNT_N, WS_TURNOVR_A, WS_ASSET_VALU_A, WS_MTHLY_GROS_INCOM_A, WS_CREDT_A, WS_TERM_MM, WS_DECISION_D, WS_DECISION_C, WS_CREDT_PURPOS_C, WS_EMERG_TYP_C, WS_IN_NCA_Y, WS_TRGT_SOURC_N, WS_PROD_N, WS_JURISTIC_Y, WS_NON_RES_Y, WS_RACE_C, WS_ID_TYPE_C, WS_ID_N, WS_GEND_C)'; FETCH REG_REP INTO APP_NO, CUST_NO, APP_DAT, CREDIT_CLASS, ACC_NO, TOT_ANN_TRNOVR, GROSSMNTHLYINCME, REQ_LIM_LOAN_AMNT, TERM, DECISION_DATE, DECISION_COD, LOAN_PURPOSE, NCA_IN_ACT_IND, CALLER_COD, PROD_NO, NCA_JURISTIC_IND, NON_RESIDENT, RACE, ID_TYPE, ID_NO, FIN_DATE, GENDER, PREV_BUS_D, CURR_RUN_D; END LOOP ; END; Exit; [/CODE]