-- Program Name : qcqedwn.ctl -- Author : xxxxxxxxxx -- Date : 01/2/2008 -- Description : Load EDW I-O-P (non-drug) extract data to the database -- ------------------------------------------------------------------------ --Load the table -- **depending on how the load handles the blanks in some of the records, -- we may need to make changes to 'terminated by whitespace' or add -- something like NVL(,' ') OPTIONS (rows=100, direct=False) load data --infile "${FTP_DIR}/cq_edwn.dat" infile "/p01/app/qips/qcqedwn.dat" append preserve blanks INTO TABLE PRV.QCQEDWN ( QCQEDWN_CLM_ID POSITION(1:20) CHAR "NVL(RTRIM(:QCQEDWN_CLM_ID),' ')" , QCQEDWN_CLML_NO POSITION(21:24) INTEGER EXTERNAL(4) , QCQEDWN_STA_CD POSITION(25:26) CHAR "NVL(RTRIM(:QCQEDWN_STA_CD),' ')" , --QCQEDWN_CLML_STA_IN POSITION(27:27) CHAR(1) , QCQEDWN_CLM_TYP_CD POSITION(28:29) CHAR "NVL(RTRIM(:QCQEDWN_CLM_TYP_CD),' ')" , QCQEDWN_EAMEE_NO POSITION(30:41) CHAR "NVL(LTRIM(:QCQEDWN_EAMEE_NO),' ')" , QCQEDWN_CLM_CRR_CD POSITION(42:42) CHAR(1) , QCQEDWN_CLML_CRR_CD POSITION(43:43) CHAR(1) , QCQEDWN_CLM_FNS_CD POSITION(44:45) CHAR "NVL(RTRIM(:QCQEDWN_CLM_FNS_CD),' ')" , QCQEDWN_CLML_FNS_CD POSITION(46:47) CHAR "NVL(RTRIM(:QCQEDWN_CLML_FNS_CD),' ')" , QCQEDWN_CLM_DRR_CD POSITION(48:52) CHAR "NVL(RTRIM(:QCQEDWN_CLM_DRR_CD),' ')" , QCQEDWN_CLML_DRR_CD POSITION(53:57) CHAR "NVL(RTRIM(:QCQEDWN_CLML_DRR_CD),' ')" , QCQEDWN_CHL_SSN_NO POSITION(58:77) CHAR "NVL(RTRIM(:QCQEDWN_CHL_SSN_NO),' ')" , QCQEDWN_CHL_SCRM_SSN_NO POSITION(78:87) CHAR "NVL(RTRIM(:QCQEDWN_CHL_SCRM_SSN_NO),' ')" , QCQEDWN_CHL_UMI_ID POSITION(88:104) CHAR "NVL(RTRIM(:QCQEDWN_CHL_UMI_ID),' ')" , QCQEDWN_PAT_MBR_ID POSITION(105:113) CHAR "NVL(RTRIM(:QCQEDWN_PAT_MBR_ID),' ')" , QCQEDWN_PAT_ID_CD POSITION(114:132) CHAR "NVL(RTRIM(:QCQEDWN_PAT_ID_CD),' ')" , QCQEDWN_PAT_AGE_NO POSITION(133:136) INTEGER EXTERNAL "NVL(LTRIM(RTRIM(:QCQEDWN_PAT_AGE_NO)),'0')", QCQEDWN_PAT_DOB_DT POSITION(137:146) Date "YYYY-MM-DD" NULLIF QCQEDWN_PAT_DOB_DT=blanks, QCQEDWN_PAT_SEX_CD POSITION(147:147) CHAR(1) , QCQEDWN_PR_ID POSITION(148:153) CHAR "NVL(RTRIM(:QCQEDWN_PR_ID),' ')" , QCQEDWN_PR_TYP_ID POSITION(154:165) CHAR "NVL(LTRIM(:QCQEDWN_PR_TYP_ID),' ')" , QCQEDWN_ACK_ASNM_PCS_CD POSITION(166:173) CHAR "NVL(RTRIM(:QCQEDWN_ACK_ASNM_PCS_CD),' ')" , QCQEDWN_FEP_IN POSITION(174:174) CHAR(1) , QCQEDWN_GP_ID POSITION(175:182) CHAR "NVL(RTRIM(:QCQEDWN_GP_ID),' ')" , QCQEDWN_MAIN_GP_ID POSITION(183:188) CHAR "NVL(RTRIM(:QCQEDWN_MAIN_GP_ID),' ')" , QCQEDWN_SB_GP_ID POSITION(189:191) CHAR "NVL(RTRIM(:QCQEDWN_SB_GP_ID),' ')" , QCQEDWN_SBMG_PLN_ID POSITION(192:194) CHAR "NVL(RTRIM(:QCQEDWN_SBMG_PLN_ID),' ')" , QCQEDWN_CLG_PLN_ID POSITION(195:197) CHAR "NVL(RTRIM(:QCQEDWN_CLG_PLN_ID),' ')" , QCQEDWN_ENRL_PLN_ID POSITION(198:200) CHAR "NVL(RTRIM(:QCQEDWN_ENRL_PLN_ID),' ')" , QCQEDWN_ADMM_DT POSITION(201:210) Date "YYYY-MM-DD" NULLIF QCQEDWN_ADMM_DT=BLANKS , QCQEDWN_DCG_DT POSITION(211:220) Date "YYYY-MM-DD" NULLIF QCQEDWN_DCG_DT=BLANKS , QCQEDWN_FNLN_DT POSITION(221:230) Date "YYYY-MM-DD" NULLIF QCQEDWN_FNLN_DT=BLANKS , QCQEDWN_FIN_ICRD_DT ??????? POSITION(231:240) "NVL(TO_DATE(:qcqedwn_fin_icrd_dt,'YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'))", QCQEDWN_DCG_STA_CD POSITION(241:242) CHAR "NVL(RTRIM(:QCQEDWN_DCG_STA_CD),' ')" , QCQEDWN_DRG_CD POSITION(243:246) CHAR "NVL(RTRIM(:QCQEDWN_DRG_CD),' ')" , QCQEDWN_POS_CD POSITION(247:248) CHAR "NVL(RTRIM(:QCQEDWN_POS_CD),' ')" , QCQEDWN_SVCE_FAC_ID POSITION(249:257) CHAR "NVL(RTRIM(:QCQEDWN_SVCE_FAC_ID),' ')" , QCQEDWN_SVCE_BGN_DT ?????? POSITION(258:267) "NVL(TO_DATE(:qcqedwn_SVCE_BGN_dt,'YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'))", QCQEDWN_SVCE_END_DT ?????? POSITION(268:277) "NVL(TO_DATE(:qcqedwn_SVCE_END_dt,'YYYY-MM-DD'),TO_DATE('9999-12-31','YYYY-MM-DD'))", QCQEDWN_ADMG_DIA_CD POSITION(278:283) CHAR "NVL(RTRIM(:QCQEDWN_ADMG_DIA_CD),' ')", QCQEDWN_1_DIA_CD POSITION(284:289) CHAR "NVL(RTRIM(:QCQEDWN_1_DIA_CD),' ')", QCQEDWN_1_DIA_PRI_IN POSITION(290:290) CHAR(1) , QCQEDWN_2_DIA_CD POSITION(291:296) CHAR "NVL(RTRIM(:QCQEDWN_2_DIA_CD),' ')", QCQEDWN_2_DIA_PRI_IN POSITION(297:297) CHAR(1) , QCQEDWN_3_DIA_CD POSITION(298:303) CHAR "NVL(RTRIM(:QCQEDWN_3_DIA_CD),' ')", QCQEDWN_3_DIA_PRI_IN POSITION(304:304) CHAR(1) , QCQEDWN_4_DIA_CD POSITION(305:310) CHAR "NVL(RTRIM(:QCQEDWN_4_DIA_CD),' ')", QCQEDWN_4_DIA_PRI_IN POSITION(311:311) CHAR(1) , QCQEDWN_5_DIA_CD POSITION(312:317) CHAR "NVL(RTRIM(:QCQEDWN_5_DIA_CD),' ')", QCQEDWN_5_DIA_PRI_IN POSITION(318:318) CHAR(1) , QCQEDWN_6_DIA_CD POSITION(319:324) CHAR "NVL(RTRIM(:QCQEDWN_6_DIA_CD),' ')", QCQEDWN_6_DIA_PRI_IN POSITION(325:325) CHAR(1) , QCQEDWN_7_DIA_CD POSITION(326:331) CHAR "NVL(RTRIM(:QCQEDWN_7_DIA_CD),' ')", QCQEDWN_7_DIA_PRI_IN POSITION(332:332) CHAR(1) , QCQEDWN_8_DIA_CD POSITION(333:338) CHAR "NVL(RTRIM(:QCQEDWN_8_DIA_CD),' ')", QCQEDWN_8_DIA_PRI_IN POSITION(339:339) CHAR(1) , QCQEDWN_9_DIA_CD POSITION(340:345) CHAR "NVL(RTRIM(:QCQEDWN_9_DIA_CD),' ')", QCQEDWN_9_DIA_PRI_IN POSITION(346:346) CHAR(1) , QCQEDWN_1_PROC_CD POSITION(347:353) CHAR "NVL(RTRIM(:QCQEDWN_1_PROC_CD),' ')", QCQEDWN_1_PROC_PRI_IN POSITION(354:354) CHAR(1) , QCQEDWN_2_PROC_CD POSITION(355:361) CHAR "NVL(RTRIM(:QCQEDWN_2_PROC_CD),' ')", QCQEDWN_2_PROC_PRI_IN POSITION(362:362) CHAR(1) , QCQEDWN_3_PROC_CD POSITION(363:369) CHAR "NVL(RTRIM(:QCQEDWN_3_PROC_CD),' ')", QCQEDWN_3_PROC_PRI_IN POSITION(370:370) CHAR(1) , QCQEDWN_4_PROC_CD POSITION(371:377) CHAR "NVL(RTRIM(:QCQEDWN_4_PROC_CD),' ')", QCQEDWN_4_PROC_PRI_IN POSITION(378:378) CHAR(1) , QCQEDWN_5_PROC_CD POSITION(379:385) CHAR "NVL(RTRIM(:QCQEDWN_5_PROC_CD),' ')", QCQEDWN_5_PROC_PRI_IN POSITION(386:386) CHAR(1) , QCQEDWN_6_PROC_CD POSITION(387:393) CHAR "NVL(RTRIM(:QCQEDWN_6_PROC_CD),' ')", QCQEDWN_6_PROC_PRI_IN POSITION(394:394) CHAR(1) , QCQEDWN_7_PROC_CD POSITION(395:401) CHAR "NVL(RTRIM(:QCQEDWN_7_PROC_CD),' ')", QCQEDWN_7_PROC_PRI_IN POSITION(402:402) CHAR(1) , QCQEDWN_8_PROC_CD POSITION(403:409) CHAR "NVL(RTRIM(:QCQEDWN_8_PROC_CD),' ')", QCQEDWN_8_PROC_PRI_IN POSITION(410:410) CHAR(1) , QCQEDWN_9_PROC_CD POSITION(411:417) CHAR "NVL(RTRIM(:QCQEDWN_9_PROC_CD),' ')", QCQEDWN_9_PROC_PRI_IN POSITION(418:418) CHAR(1) , QCQEDWN_1_PCM_CD POSITION(419:420) CHAR "NVL(RTRIM(:QCQEDWN_1_PCM_CD),' ')", QCQEDWN_2_PCM_CD POSITION(421:422) CHAR "NVL(RTRIM(:QCQEDWN_2_PCM_CD),' ')", QCQEDWN_RVN_CD POSITION(423:426) CHAR "NVL(RTRIM(:QCQEDWN_RVN_CD),' ')", QCQEDWN_BILG_PRV_ID POSITION(427:442) CHAR "NVL(RTRIM(:QCQEDWN_BILG_PRV_ID),' ')", QCQEDWN_PRV_TYP_CD POSITION(443:445) CHAR "NVL(RTRIM(:QCQEDWN_PRV_TYP_CD),' ')", QCQEDWN_BILG_SPCY_CD POSITION(446:448) CHAR(3) , QCQEDWN_PRV_PARG_STA_CD POSITION(449:449) CHAR(1) , QCQEDWN_PERFG_PRV_ID POSITION(450:465) CHAR "NVL(RTRIM(:QCQEDWN_PERFG_PRV_ID),' ')", QCQEDWN_PERFG_SPCY_CD POSITION(466:468) CHAR "NVL(RTRIM(:QCQEDWN_PERFG_SPCY_CD),' ')", QCQEDWN_ATTDG_PRV_ID POSITION(469:484) CHAR "NVL(RTRIM(:QCQEDWN_ATTDG_PRV_ID),' ')", QCQEDWN_SBMD_CG_AT POSITION(485:498) DECIMAL EXTERNAL(14) , QCQEDWN_ALWD_CG_AT POSITION(499:512) DECIMAL EXTERNAL(14) , QCQEDWN_APVD_PY_AT POSITION(513:526) DECIMAL EXTERNAL(14) , QCQEDWN_DCT_AT POSITION(527:540) DECIMAL EXTERNAL(14) , QCQEDWN_DEDB_AT POSITION(541:554) DECIMAL EXTERNAL(14) , QCQEDWN_COPAY_AT POSITION(555:568) DECIMAL EXTERNAL(14) , QCQEDWN_COIN_AT POSITION(569:582) DECIMAL EXTERNAL(14) , QCQEDWN_COB_AT POSITION(583:596) DECIMAL EXTERNAL(14) , QCQEDWN_LOS_DAY_CT POSITION(597:601) INTEGER EXTERNAL(5) , QCQEDWN_SVCE_UNT_CT POSITION(602:616) INTEGER EXTERNAL(15) , QCQEDWN_CAPD_SVCE_IN POSITION(617:617) CHAR(1) , --QCQEDWN_DUP_RCD_IN POSITION(618:618) CHAR(1) , QCQEDWN_BSN_CONT_LD_TX POSITION(619:628) CHAR "NVL(RTRIM(:QCQEDWN_BSN_CONT_LD_TX),' ')", QCQEDWN_RCD_MNTD_TS SYSDATE, QCQEDWN_RCD_MNTD_ID CONSTANT 'EDWN_LDR' )