---------------------------------------------------------------- -- iCATs 로그 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE CATS_ELog_SEQ -----------------**/ CREATE SEQUENCE CATS_ELog_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 CATS_ELog -----------------------**/ CREATE TABLE CATS_ELog ( Seq NUMBER(38) DEFAULT 0 NOT NULL , WorkTime VARCHAR2 (23) Default substr(To_CHAR(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF'), 0, 23), ProcessName CHAR (2) default '', Message VARCHAR2(4000) default '' ); ---------------------------------------------------------------- -- iCATs 매입 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_Acquire_SEQ ----------**/ CREATE SEQUENCE Cats_Tmp_Acquire_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_Acquire -----------------**/ CREATE TABLE Cats_Tmp_Acquire ( Seq NUMBER(38) DEFAULT 0 NOT NULL , DataCode CHAR (1), CompanyId VARCHAR2 (20), SendDate CHAR (10), SeqNo CHAR (8), ReadFlag CHAR (1), Class1 CHAR (1), CardNo VARCHAR2 (16) NOT NULL, ApprNo VARCHAR2 (12), CollNo VARCHAR2 (32), OrgnCollNo VARCHAR2 (32), SlipNo VARCHAR2 (12), ApprDate CHAR (10), PurchDate CHAR (10), PurchTime CHAR (8), CurrCode VARCHAR2 (3), ApprTot NUMBER(17, 2) DEFAULT 0, AcquTot NUMBER(17, 2) DEFAULT 0, AcquExch NUMBER(17, 2) DEFAULT 0, USDAcquTot NUMBER(17, 2) DEFAULT 0, ConvFee NUMBER(17, 2) DEFAULT 0, PurchTot NUMBER(17, 2) DEFAULT 0, InstType CHAR (1), SettDate CHAR (10), Abroad CHAR (1), PurchYN CHAR (1), MerchBizno CHAR (10), MerchNo VARCHAR2 (12), MerchName VARCHAR2 (100), Master1 VARCHAR2 (100), MerchTel VARCHAR2 (14), MerchZipcode CHAR (6), MerchAddr1 VARCHAR2 (100), MerchAddr2 VARCHAR2 (100), MccName VARCHAR2 (100), MccCode VARCHAR2 (4), PartAcquCancYN CHAR (1) DEFAULT 'N', NoneApprYN CHAR (1) DEFAULT 'Y', ServTypeYN CHAR (1) DEFAULT 'N', DiscAmt NUMBER(17, 2) DEFAULT 0, CurrAcquTot NUMBER(17, 2) DEFAULT 0, AcquFee NUMBER(17, 2) DEFAULT 0, ApprAmt NUMBER(17, 2) DEFAULT 0, VAT NUMBER(17, 2) DEFAULT 0, Tips NUMBER(17, 2) DEFAULT 0, OriginMerchName VARCHAR2(100), OriginMerchBizNo VARCHAR2(10), BCMccName VARCHAR2 (50) , /* 추가전문항목 추가 2011.4.21 */ BCMccCode VARCHAR2 (10) , /* 추가전문항목 추가 2011.4.21 */ MembCode VARCHAR2 (3) , /* 추가전문항목 추가 2011.4.21 */ CARD_CHECK_GB VARCHAR(1) NULL, /* 추가전문항목 추가 2015.2.12 */ CHK_CAN_IPGUMDT VARCHAR(10) NULL, /* 추가전문항목 추가 2015.2.12 */ BilAmt DECIMAL(17,2) NULL, /* 추가전문항목 추가 2015.2.12 */ SAVE_POINT_AMT DECIMAL(17,2) NULL, /* 추가전문항목 추가 2015.2.12 */ Tips1 NUMBER(17, 2) DEFAULT 0, /* 추가전문항목 추가 2015.10.12 */ TaxType VARCHAR2 (20) DEFAULT NULL, MerchCessDate CHAR (10), TaxTypeDate CHAR (10), ApprAmt1 NUMBER(18, 0) DEFAULT 0, VAT1 NUMBER(18, 0) DEFAULT 0, VATYN CHAR (1) DEFAULT 'N', WorkDate CHAR (10) DEFAULT To_CHAR(SysDate,'yyyy-mm-dd'), ErrYN CHAR (1) DEFAULT 'N', TrAcq VARCHAR2 (4), PRIMARY KEY ( Seq ) ) ; ---------------------------------------------------------------- -- iCATs 항공 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_AirLine_SEQ ----------**/ CREATE SEQUENCE Cats_Tmp_AirLine_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_AirLine -----------------**/ CREATE TABLE Cats_Tmp_AirLine ( Seq NUMBER(38) DEFAULT 0 NOT NULL , DataCode CHAR (1), CompanyId VARCHAR2 (20), SendDate CHAR (10), SeqNo VARCHAR2 (8), ReadFlag CHAR (1), ServCode CHAR (4), JobCode CHAR (4), CardNo VARCHAR2 (16), ApprDate CHAR (10), ApprNo VARCHAR2 (12), ApprTot NUMBER(17, 2) DEFAULT 0, BilTot NUMBER(17, 2) DEFAULT 0, CustAirTickNo VARCHAR2 (10), CustAirCode VARCHAR2 (2), TourNameEng VARCHAR2 (50), TourCorpNameEng VARCHAR2 (50), SeatType VARCHAR2 (2), TrvPath VARCHAR2 (33), CollNo VARCHAR2 (32), WorkDate CHAR (10), ErrYN CHAR (1), TrAcq VARCHAR2 (4), Class1 CHAR(1), PRIMARY KEY ( Seq ) ) ; ---------------------------------------------------------------- -- iCATs 승인 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_Approval_SEQ ----------**/ CREATE SEQUENCE Cats_Tmp_Approval_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_Approval ----------------**/ CREATE TABLE Cats_Tmp_Approval ( Seq NUMBER(38) DEFAULT 0 NOT NULL , DataCode CHAR (1) , CompanyId VARCHAR2 (20) , SendDate CHAR (10) , SeqNo CHAR (8) , ReadFlag CHAR (1) , Class1 CHAR (1) , CardNo VARCHAR2 (16) NOT NULL , ApprNo VARCHAR2 (12) , TransDate CHAR (10) , TransTime CHAR (8) , CardType1 VARCHAR2 (100) , CardType2 CHAR (1) , CurrCode VARCHAR2 (3) , ApprAmt NUMBER(17, 2) DEFAULT 0, VAT NUMBER(17, 2) DEFAULT 0 , Tips NUMBER(17, 2) DEFAULT 0 , ApprTot NUMBER(17, 2) DEFAULT 0 , ApprExch NUMBER(17, 2) DEFAULT 0 , USDApprTot NUMBER(17, 2) DEFAULT 0 , InstType CHAR (1) , InstMonth VARCHAR2 (2) , Abroad CHAR (1) , SlipNo VARCHAR2 (12) , TerminalNo VARCHAR2 (12) , Purch CHAR (1) , AcquIssuer VARCHAR2 (50) , MerchBizno CHAR (10) , MerchNo VARCHAR2 (12) , MerchName VARCHAR2 (100) , Master1 VARCHAR2 (100) , MerchTel VARCHAR2 (14) , MerchZipcode CHAR (6) , MerchAddr1 VARCHAR2 (100) , MerchAddr2 VARCHAR2 (100) , MerchStatus CHAR (1) , MCCName VARCHAR2 (100) , MCCCode VARCHAR2 (4) , PartApprCancYN CHAR (1) DEFAULT ('N'), ServTypeYN CHAR (1) DEFAULT ('N'), CollNo VARCHAR2 (32) , OriginTransDate VARCHAR2 (10) , OriginApprNo VARCHAR2 (12) , OriginTransTime VARCHAR2 (8) , OriginMerchName VARCHAR2 (100) , OriginMerchBizNo VARCHAR2 (10) , CardLimitAppr NUMBER(17, 2) DEFAULT 0 , /* 추가전문항목 추가 2011.4.21 */ SettDate CHAR (10) , /* 추가전문항목 추가 2011.4.21 */ BCMccName VARCHAR2 (50) , /* 추가전문항목 추가 2011.4.21 */ BCMccCode VARCHAR2 (10) , /* 추가전문항목 추가 2011.4.21 */ MembCode VARCHAR2 (3) , /* 추가전문항목 추가 2011.4.21 */ CARD_CHECK_GB VARCHAR(1) NULL, /* 추가전문항목 추가 2015.02.12*/ Tips1 NUMBER(17, 2) DEFAULT 0, /* 추가전문항목 추가 2015.10.12 */ TaxType VARCHAR2 (20) DEFAULT (null), MerchCessDate CHAR (10) , TaxTypeDate CHAR (10) , ApprAmt1 NUMBER(17, 2) DEFAULT (0), VAT1 NUMBER(17, 2) DEFAULT (0), VATYN CHAR (1) DEFAULT ('N'), WorkDate CHAR (10) DEFAULT To_CHAR(SysDate,'yyyy-mm-dd'), ErrYN CHAR (1) DEFAULT ('N'), TrAcq VARCHAR2 (4), PRIMARY KEY ( Seq ) ) ; ---------------------------------------------------------------- -- iCATs 청구 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_Bill_SEQ -------------**/ CREATE SEQUENCE Cats_Tmp_Bill_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_Bill -------------------**/ CREATE TABLE Cats_Tmp_Bill ( Seq NUMBER(38) DEFAULT 0 NOT NULL, DataCode CHAR (1), CompanyId VARCHAR2 (20), SendDate CHAR (10), SeqNo CHAR (8), ReadFlag CHAR (1), Class1 CHAR (1), BilType CHAR (1), CorpBizNo CHAR (10), CorpNo VARCHAR2 (14), EstbNo VARCHAR2 (10), CardNo VARCHAR2 (16), ApprNo VARCHAR2 (12), SlipNo VARCHAR2 (12), CollNo VARCHAR2 (32), OrgnApprDate CHAR (10), BilWorkDate CHAR (10), BilAmt NUMBER(17, 2) DEFAULT 0, BilFee NUMBER(17, 2) DEFAULT 0, MembshipFee NUMBER(17, 2) DEFAULT 0, AcquExch NUMBER(17, 2) DEFAULT 0, USDAcquTot NUMBER(17, 2) DEFAULT 0, ConvFee NUMBER(17, 2) DEFAULT 0, BilTot NUMBER(17, 2) DEFAULT 0, InstType CHAR (1) DEFAULT ('A'), InstMonth CHAR (2) DEFAULT ('00'), RestInstMonth CHAR (2), SettDate CHAR (10), MerchBizNo CHAR (10), MerchName VARCHAR2 (100), ServTypeYN CHAR (1) DEFAULT ('N'), DiscAmt NUMBER(17, 2) DEFAULT 0, EstbName VARCHAR2 (100), UserNo VARCHAR2 (30), UserName VARCHAR2 (100), ORIGINAPPRTOT NUMBER(17, 2) DEFAULT 0, OriginTransTime VARCHAR2 (8) , OriginMerchName VARCHAR2 (50) , OriginMerchBizNo VARCHAR2 (10) , MEMID VARCHAR2 (15) , /* 추가전문항목 추가 2011.4.21 */ MembCode VARCHAR2 (3) , /* 추가전문항목 추가 2011.4.21 */ CARD_CHECK_GB VARCHAR(1) NULL , /* 추가전문항목 추가 2015.2.12 */ SAVE_POINT_AMT DECIMAL(17,2) NULL, /* 추가전문항목 추가 2015.2.12 */ WorkDate CHAR (10) DEFAULT To_CHAR(SysDate,'yyyy-mm-dd'), ErrYN CHAR (1) DEFAULT ('N'), TrAcq VARCHAR2 (4), PRIMARY KEY ( Seq ) ) ; ---------------------------------------------------------------- -- iCATs 카드한도 테이블 ---------------------------------------------------------------- /**----------------- 테이블 Cats_Tmp_CardLimit --------------**/ CREATE TABLE Cats_Tmp_CardLimit ( CardNo VARCHAR2 (16) NOT NULL , DataCode CHAR (1), CompanyId VARCHAR2 (20), SendDate CHAR (10), SeqNo CHAR (8), ReadFlag CHAR (1), Class1 CHAR (1), CorpBizNo CHAR (10), CorpNo VARCHAR2 (14) NOT NULL , EstablishNo CHAR (10), CorpLmt NUMBER(17, 2), CorpRestLmt NUMBER(17, 2), CardLmt NUMBER(17, 2), CardRestLmt NUMBER(17, 2), CashLmt NUMBER(17, 2), CashRestLmt NUMBER(17, 2), AbroadLmt NUMBER(17, 2), AbroadRestLmt NUMBER(17, 2), ATMLmt NUMBER(17, 2), ATMRestLmt NUMBER(17, 2), PointMile NUMBER(17, 2), PointMileDate CHAR (10), MonthLmt NUMBER(17, 2), MonthRestLmt NUMBER(17, 2), WorkDate CHAR (10), ErrYN CHAR (1), TrAcq VARCHAR2 (4) ); ---------------------------------------------------------------- -- iCATs 카드기본정보 테이블 ---------------------------------------------------------------- /**----------------- 테이블 Cats_Tmp_Cardinfo ---------------**/ CREATE TABLE Cats_Tmp_Cardinfo ( CardNo VARCHAR2 (16) NOT NULL , Datacode CHAR (1), CompanyId VARCHAR2 (20), SendDate CHAR (10), SeqNo CHAR (8), ReadFlag CHAR (1), Class1 CHAR (1), CorpName VARCHAR2 (50), CorpBizNo CHAR (10) NOT NULL , CorpNo VARCHAR2 (14), EstbNo VARCHAR2 (10), CustNo VARCHAR2 (15), CardBrand CHAR (1), LocalBrand CHAR (1), CardType1 CHAR (1), CardType2 CHAR (1), CardType3 CHAR (1), CardType4 CHAR (1), CoopCode VARCHAR2 (6), CoopName VARCHAR2 (100), KorName VARCHAR2 (100), EngName VARCHAR2 (100), RegistNo CHAR (40), Validity CHAR (7), PostCode VARCHAR2 (20), PostName VARCHAR2 (50), MembZipcode CHAR (6), MembAddr1 VARCHAR2 (100), MembAddr2 VARCHAR2 (100), SettType CHAR (1), SettDay CHAR (2), SettBankCode VARCHAR2 (10), SettAcctNo VARCHAR2 (20), MembTel VARCHAR2 (14), NoticeType CHAR (1), IssueDate CHAR (10), TmntDate CHAR (10), PauseDate CHAR (10), BeforeCard VARCHAR2 (16), OrgnCode VARCHAR2 (20), OrgnName VARCHAR2 (50), EstbName VARCHAR2 (50), /* 추가전문항목 추가 2011.4.21 */ UserNo VARCHAR2 (30), /* 추가전문항목 추가 2011.4.21 */ UserName VARCHAR2 (50), /* 추가전문항목 추가 2011.4.21 */ CarNo VARCHAR2 (50), /* 추가전문항목 추가 2011.4.21 */ RNDProjCode VARCHAR2 (20), /* 추가전문항목 추가 2011.4.21 */ RNDProjName VARCHAR2 (50), /* 추가전문항목 추가 2011.4.21 */ MEMID VARCHAR2 (15), /* 추가전문항목 추가 2011.4.21 */ MEMName VARCHAR2 (50), /* 추가전문항목 추가 2011.4.21 */ CouKndCd VARCHAR2 (2), /* 추가전문항목 추가 2011.4.21 */ MembCode VARCHAR2 (3), /* 추가전문항목 추가 2011.4.21 */ CARD_CHECK_GB varchar(1) NULL, /* 추가전문항목 추가 2015.2.12 */ CHECK_AMT DECIMAL(17,2) NULL, /* 추가전문항목 추가 2015.2.12 */ CHK_SETTDATE varchar(2) NULL, /* 추가전문항목 추가 2015.2.12 */ CHK_SETTBANKCODE varchar(10) NULL, /* 추가전문항목 추가 2015.2.12 */ CHK_SETTACCTNO varchar(20) NULL, /* 추가전문항목 추가 2015.2.12 */ WorkDate CHAR (10), ErrYN CHAR (1), TrAcq VARCHAR2 (4) ) ; ---------------------------------------------------------------- -- iCATs 주유 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_Oil_SEQ --------------**/ CREATE SEQUENCE Cats_Tmp_Oil_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_Oil --------------------**/ CREATE TABLE Cats_Tmp_Oil ( Seq NUMBER(38) DEFAULT 0 NOT NULL , DateCode CHAR (1), CompanyId VARCHAR2 (20), SendDate CHAR (10), SeqNo VARCHAR2 (8), ReadFlag CHAR (1), ServCode CHAR (4), JobCode CHAR (4), CardNo VARCHAR2 (16), DeptNo2 VARCHAR2 (20), BilDate CHAR (10), EmpNo VARCHAR2 (20), CarName VARCHAR2 (50), CarNo VARCHAR2 (16), ApprDate CHAR (10), ApprTime CHAR (8), OilCorpName VARCHAR2 (50), OilCorpAddr VARCHAR2 (200), OilType VARCHAR2 (30), Price NUMBER(17, 2), OilQty NUMBER(18, 0), ApprTot NUMBER(17, 2), DisTot NUMBER(17, 2), BilTot NUMBER(17, 2), ApprNo VARCHAR2 (12), OilCorpMaster VARCHAR2 (50), OilCorpBizNo CHAR (10), CollNo VARCHAR2 (32), WorkDate VARCHAR2 (10), ErrYN VARCHAR2 (1), TrAcq CHAR (4), PRIMARY KEY ( Seq ) ); ---------------------------------------------------------------- -- iCATs 결제 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_Settlement_SEQ -------**/ CREATE SEQUENCE Cats_Tmp_Settlement_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_Settlement --------------**/ CREATE TABLE Cats_Tmp_Settlement ( Seq NUMBER(38) DEFAULT 0 NOT NULL , DataCode CHAR (1) , CompanyId VARCHAR2 (20) , SendDate CHAR (10) , SeqNo VARCHAR2 (10) , ReadFlag CHAR (1) , Class1 CHAR (1) , SettCancYN CHAR (1) , SettSeqNo VARCHAR2 (10) , CorpBizNo CHAR (10) , CorpNo VARCHAR2 (14) , EstbNo VARCHAR2 (10) , CardNo VARCHAR2 (16) , CancSettDate CHAR (10) , SettDate CHAR (10) , SettTime CHAR (8) , ReceName VARCHAR2 (50) , SettBankCode VARCHAR2 (10) , SettAcctNo VARCHAR2 (20) , SettTot NUMBER (17, 2) , SettAmt NUMBER (17, 2) , SettFee NUMBER (17, 2) , SettInterest NUMBER (17, 2) , DelayTot NUMBER (17, 2) , SettSumr VARCHAR2 (50) , YearFee NUMBER (17, 2) , /* 추가전문항목 추가 2011.4.21 */ ShamPaymt NUMBER (17, 2) , /* 추가전문항목 추가 2011.4.21 */ RefuTot NUMBER (17, 2) , /* 추가전문항목 추가 2011.4.21 */ TradeOffTot NUMBER (17, 2) , /* 추가전문항목 추가 2011.4.21 */ MEMID VARCHAR2 (15) , /* 추가전문항목 추가 2011.4.21 */ WorkDate CHAR (10), ErrYN CHAR (1), TrAcq VARCHAR2 (4), PRIMARY KEY ( Seq ) ); ---------------------------------------------------------------- -- iCATs 연체 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_Delay_SEQ ----------**/ CREATE SEQUENCE Cats_Tmp_Delay_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_Delay -----------------**/ CREATE TABLE Cats_Tmp_Delay ( Seq NUMBER(38) DEFAULT 0 NOT NULL, DataCode CHAR(1), CompanyId VARCHAR2(20), SendDate CHAR(10), SeqNo CHAR(8), ReadFlag CHAR(1), Class CHAR(1), CorpBizNo CHAR(10), DelayYN CHAR(1), CorpNo VARCHAR2(14), EstbNo VARCHAR2(10), EstbName VARCHAR2(50), CardType2 CHAR(1), CardNo VARCHAR2(16), SettBankCode VARCHAR2(10), SettAcctNo VARCHAR2(20), DelayDate CHAR(10), DelayAmt NUMBER(18, 0), DelayFee NUMBER(18, 0), DelayTot NUMBER(18, 0), DelayCnt SMALLINT, HandTel VARCHAR2(14), SettlDay CHAR(10), NextSettlDay CHAR(10), WorkDate CHAR(10), ErrYN CHAR(1), TrAcq VARCHAR2(4), PRIMARY KEY ( Seq ) ); ---------------------------------------------------------------- -- iCATs 하이패스 테이블 ---------------------------------------------------------------- /**----------------- SEQUENCE Cats_Tmp_HighPass_SEQ ----------**/ CREATE SEQUENCE Cats_Tmp_HighPass_SEQ MINVALUE 1000 MAXVALUE 99999999999 INCREMENT BY 1 START WITH 1000 NOCACHE NOORDER NOCYCLE ; /**----------------- 테이블 Cats_Tmp_HIGHPASS -----------------**/ CREATE TABLE CATS_TMP_HIGHPASS ( SEQ NUMBER DEFAULT 0, DATACODE CHAR(1), COMPANYID VARCHAR2(20), SENDDATE CHAR(10), SEQNO CHAR(8), READFLAG CHAR(1), CLASS1 CHAR(1), ALIASNO VARCHAR2(16), CARDNO VARCHAR2(16) NOT NULL ENABLE, COLLNO VARCHAR2(32), ENTRANCEDATETIME VARCHAR2(19), EXITDATETIME VARCHAR2(19), ENTRANCENAME VARCHAR2(20), EXITNAME VARCHAR2(20), TOLLROADCORPNAME VARCHAR2(40), ACQUTOT NUMBER(17,2) DEFAULT 0, PURCHTOT NUMBER(17,2) DEFAULT 0, SETTDATE VARCHAR2(10), PURCHDATE VARCHAR2(10), WORKDATE CHAR(10) DEFAULT TO_CHAR(SysDate,'yyyy-mm-dd'), ERRYN CHAR(1) DEFAULT ('N'), TRACQ VARCHAR2(4) ); COMMENT ON COLUMN Cats_Tmp_HighPass.SEQ IS '일련번호'; COMMENT ON COLUMN Cats_Tmp_HighPass.DATACODE IS '데이터코드'; COMMENT ON COLUMN Cats_Tmp_HighPass.COMPANYID IS '사업자ID'; COMMENT ON COLUMN Cats_Tmp_HighPass.SENDDATE IS '전송일자'; COMMENT ON COLUMN Cats_Tmp_HighPass.SEQNO IS '일별일련번호'; COMMENT ON COLUMN Cats_Tmp_HighPass.READFLAG IS 'ReadFlag'; COMMENT ON COLUMN Cats_Tmp_HighPass.CLASS1 IS '구분(매입,매입취소)'; COMMENT ON COLUMN Cats_Tmp_HighPass.ALIASNO IS '하이패스Alias번호'; COMMENT ON COLUMN Cats_Tmp_HighPass.CARDNO IS '하이패스카드번호'; COMMENT ON COLUMN Cats_Tmp_HighPass.COLLNO IS '매입추심번호'; COMMENT ON COLUMN Cats_Tmp_HighPass.ENTRANCEDATETIME IS '입구일시'; COMMENT ON COLUMN Cats_Tmp_HighPass.EXITDATETIME IS '출구일시'; COMMENT ON COLUMN Cats_Tmp_HighPass.ENTRANCENAME IS '진입요금소명(입구명)'; COMMENT ON COLUMN Cats_Tmp_HighPass.EXITNAME IS '진출요금소명(출구명)'; COMMENT ON COLUMN Cats_Tmp_HighPass.TOLLROADCORPNAME IS '사업자명칭(출구기준)'; COMMENT ON COLUMN Cats_Tmp_HighPass.ACQUTOT IS '원통행료(할인전 금액)'; COMMENT ON COLUMN Cats_Tmp_HighPass.PURCHTOT IS '거래금액(할인후 수납금액)'; COMMENT ON COLUMN Cats_Tmp_HighPass.PURCHDATE IS '매입일자'; COMMENT ON COLUMN Cats_Tmp_HighPass.WORKDATE IS '작업일'; COMMENT ON COLUMN Cats_Tmp_HighPass.ERRYN IS '에러여부'; COMMENT ON COLUMN Cats_Tmp_HighPass.TRACQ IS '카드사코드'; COMMENT ON TABLE Cats_Tmp_HighPass IS '하이패스'; CREATE INDEX CATS_TMP_HIGHPASS_IDX02 ON CATS_TMP_HIGHPASS ( "CLASS1", "CARDNO", "COLLNO", "ENTRANCEDATETIME" ); ---------------------------- -- 과세조회관련 작업테이블 ---------------------------- CREATE TABLE Cats_Tmp_MerchInfo ( MERCHBIZNO VARCHAR2(10) NOT NULL ENABLE, TAXTYPE VARCHAR2(10), SCRAPDATE VARCHAR2(10), CLOSEDATE VARCHAR2(10), CLOSE_YN VARCHAR2(1), BASICDATE VARCHAR2(10), CHANGEDATE VARCHAR2(10), EXT1 VARCHAR2(50), CONSTRAINT "Cats_Tmp_MerchInfo_PK" PRIMARY KEY ("MERCHBIZNO") ) ; CREATE INDEX IX_CTQ_tmp ON Cats_Tmp_Acquire (Class1, CardNo, ApprNo, CollNo) ; CREATE INDEX IX_CTA_tmp ON Cats_Tmp_Approval(Class1, CardNo, ApprNo, TransDate); CREATE INDEX IX_CTA_1 ON Cats_Tmp_Approval(MerchBizno); CREATE INDEX IX_CTA_2 ON Cats_Tmp_Approval(TaxType); CREATE INDEX IX_CTB ON Cats_Tmp_Bill (Class1,BilType, CardNo, ApprNo, CollNo, SettDate ); CREATE INDEX IX_CTD ON CATS_TMP_DELAY (DELAYDATE, DELAYTOT, SETTLDAY, SETTBANKCODE, CORPBIZNO); CREATE INDEX IX_CTM_scrapdate ON Cats_Tmp_MerchInfo (SCRAPDATE) ; ============================================================================================= /* select CATS_ELOG_SEQ.NEXTVAL from dual; select CATS_TMP_ACQUIRE_SEQ.NEXTVAL from dual; select CATS_TMP_AIRLINE_SEQ.NEXTVAL from dual; select CATS_TMP_APPROVAL_SEQ.NEXTVAL from dual; select CATS_TMP_BILL_SEQ.NEXTVAL from dual; select CATS_TMP_LOG_SEQ.NEXTVAL from dual; select CATS_TMP_OIL_SEQ.NEXTVAL from dual; select CATS_TMP_SETTLEMENT_SEQ.NEXTVAL from dual; */ /*############################### D E L E T E ######################################## truncate table CATS_ELog; truncate table Cats_Tmp_Acquire; truncate table Cats_Tmp_Airline; truncate table Cats_Tmp_Approval; truncate table Cats_Tmp_Bill; truncate table Cats_Tmp_Cardinfo; truncate table Cats_Tmp_CardLimit; truncate table Cats_Tmp_Log; truncate table Cats_Tmp_Oil; truncate table Cats_Tmp_Refund; truncate table Cats_Tmp_Settlement; ------------------------------------------------ Drop INDEX IX_Cats_Tmp_Acquire ; Drop INDEX IX_Cats_Tmp_Acquire_1; Drop INDEX IX_Cats_Tmp_Approval; Drop INDEX IX_Cats_Tmp_Approval_1; Drop INDEX IX_Cats_Tmp_Approval_2; Drop INDEX IX_Cats_Tmp_Bill; ------------------------------------------------ Drop table CATS_ELog; Drop table Cats_Tmp_Acquire; Drop table Cats_Tmp_Airline; Drop table Cats_Tmp_Approval; Drop table Cats_Tmp_Bill; Drop table Cats_Tmp_Cardinfo; Drop table Cats_Tmp_CardLimit; Drop table Cats_Tmp_Log; Drop table Cats_Tmp_Oil; Drop table Cats_Tmp_Settlement; ------------------------------------------------ Drop SEQUENCE CATS_ELOG_SEQ; Drop SEQUENCE CATS_TMP_ACQUIRE_SEQ; Drop SEQUENCE CATS_TMP_AIRLINE_SEQ; Drop SEQUENCE CATS_TMP_APPROVAL_SEQ; Drop SEQUENCE CATS_TMP_BILL_SEQ; Drop SEQUENCE CATS_TMP_LOG_SEQ; Drop SEQUENCE CATS_TMP_OIL_SEQ; Drop SEQUENCE CATS_TMP_SETTLEMENT_SEQ; #########################################################################################*/ ----------------------Script End;