ogg sourceistable extract parameter files - as an etl tool

--FISCAL_PERIOD  f_00

ADD EXTRACT f_00, SOURCEISTABLE

EXTRACT f_00
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.FISCAL_PERIOD.txt, PURGE
TABLE corecat.FISCAL_PERIOD cols(ID,BGR_NDA_ID,PERIOD_END,PERIOD_LENGTH,PERIOD_TYPE,PERIOD_LENGTH_INDICATOR,END_DATE) WHERE (END_DATE =@NULL);

--CRX_COR CORPORATE_ACTION f_01

ADD EXTRACT f_01, SOURCEISTABLE

EXTRACT f_01
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CRX_COR.CORPORATE_ACTION.txt, PURGE
TABLE CRX_COR.CORPORATE_ACTION cols(AST_NDA_ID,ID);

--CRX_COR MARKET_DIVIDEND f_02

ADD EXTRACT f_02, SOURCEISTABLE

EXTRACT f_02
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CRX_COR.MARKET_DIVIDEND.txt, PURGE
TABLE CRX_COR.MARKET_DIVIDEND cols(DVP_CAC_ID,ID,DVP_DIVIDEND_PAYMENT_NUM,AST_NDA_ID,EX_DATE,TAX_RATE_PERCENT,CLA_TAX_TRTMNT_MKR_ID);

--CRX_COR EARNINGS_PER_SHARE F_03

ADD EXTRACT f_03, SOURCEISTABLE

DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CRX_COR.EARNINGS_PER_SHARE.txt, PURGE
TABLE CRX_COR.EARNINGS_PER_SHARE WHERE (rolling_eps = "N" and end_date = @null and account_type <> @null and ((EPS_TYPE="ORD") OR ( EPS_TYPE="DEP") OR (EPS_TYPE= "PRE") OR (EPS_TYPE= "HEA") OR (EPS_TYPE="NOR") OR (EPS_TYPE= "OPE")));

 ******************************************************CFM_COR BALANCE_SHEET

--CRX_COR TOTAL_SHARES_OUTSTANDING

--CRX_COR TOTAL_SHARES_OUTSTANDING f_04

ADD EXTRACT f_04, SOURCEISTABLE

EXTRACT f_04
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CRX_COR.TOTAL_SHARES_OUTSTANDING.txt, PURGE
TABLE CRX_COR.TOTAL_SHARES_OUTSTANDING WHERE (end_date = @NULL);

--CFM_COR COMPANY_PROFILE
ADD EXTRACT f_05, SOURCEISTABLE

EXTRACT f_05
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CFM_COR.COMPANY_PROFILE.txt, PURGE
TABLE CFM_COR.COMPANY_PROFILE COLS(BGR_NDA_ID,EQUITY_ISSUED,AVERAGE_SHARES_OUTSTANDING);

--CFM_COR PROFIT_AND_LOSS

ADD EXTRACT f_06, SOURCEISTABLE

EXTRACT f_06
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CFM_COR.PROFIT_AND_LOSS.txt, PURGE
TABLE CFM_COR.PROFIT_AND_LOSS COLS(RDE_ID,CLA_CURR_NDA_ID,REVENUE,NET_PREMIUMS_EARNED,NET_INTEREST_INCOME,TOTAL_OPERATING_INCOME,TOTAL_OPERATING_EXPENSES,UNDERWRITING_RESULT,CLAIMS_INCURRED_NET_REINS,OPERATING_PROFIT,INTEREST_PAYABLE,PRETAX_PROFIT,NET_INCOME,PREMIUMS_WRITTEN);

--CFM_COR REPORT_DETAIL

ADD EXTRACT f_07, SOURCEISTABLE

EXTRACT f_07
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CFM_COR.REPORT_DETAIL.txt, PURGE
TABLE CFM_COR.REPORT_DETAIL where ( end_date= @null);

--CORECAT CURRENCY_RSHIP

ADD EXTRACT f_08, SOURCEISTABLE

EXTRACT f_08
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.CURRENCY_RSHIP.txt, PURGE
TABLE CORECAT.CURRENCY_RSHIP where ( end_date= @null);

--CORECAT BODY_ASSET_ROLE

ADD EXTRACT f_09, SOURCEISTABLE

EXTRACT f_09
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.BODY_ASSET_ROLE.txt, PURGE
TABLE CORECAT.BODY_ASSET_ROLE where ((end_date  = @NULL) and ((erd_bar_type_value_d ="MARKET CAP ISSUE") or (erd_bar_type_value_d ="ISSUER") or (erd_bar_type_value_d ="PRIMARY ISSUE")));

--CORECAT ASSET_ASSOC

ADD EXTRACT f_10, SOURCEISTABLE

EXTRACT f_10
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.ASSET_ASSOC.txt, PURGE
TABLE CORECAT.ASSET_ASSOC where ((end_date = @NULL ) and ((erd_aas_type_value_d ="PRIMARY QUOTE") or (erd_aas_type_value_d ="VALUATION QUOTE") or (erd_aas_type_value_d ="BASE ASSET","ASSET")));

--CORECAT ASSET

ADD EXTRACT f_11, SOURCEISTABLE

EXTRACT f_11
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.ASSET.txt, PURGE
TABLE CORECAT.ASSET cols(NDA_ID,FIRST_TRADING_DAY);

--CORECAT PERIODIC_CONSENSUS_EST

ADD EXTRACT f_12, SOURCEISTABLE

EXTRACT f_12
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.PERIODIC_CONSENSUS_EST.txt, PURGE
TABLE CORECAT.PERIODIC_CONSENSUS_EST where ((pce_type ="EPS") or (pce_type ="DPS"));

--CORECAT CONSENSUS_EST_REPORT

ADD EXTRACT f_13, SOURCEISTABLE

EXTRACT f_13
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.CONSENSUS_EST_REPORT.txt, PURGE
TABLE CORECAT.CONSENSUS_EST_REPORT cols(equ_nda_id,estimate_date,fy0_eps_actual);

--CORECAT IDENTIFIER

ADD EXTRACT f_14, SOURCEISTABLE

EXTRACT f_14
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.IDENTIFIER.txt, PURGE
TABLE CORECAT.IDENTIFIER cols(NDA_ID,VALUE,AST_FOR_NDA_ID,BGR_FOR_NDA_ID,BIN_FOR_NDA_ID,DPA_FOR_NDA_ID) where ((end_date=@null) and (pro_name_d = "RIC"));

--CORECAT CLASSIFIER_MAP  errrrrrrrrror

ADD EXTRACT f_15, SOURCEISTABLE
start extract f_15

EXTRACT f_15
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CORECAT.CLASSIFIER_MAP.txt, PURGE
TABLE CORECAT.CLASSIFIER_MAP  where ((cma.end_date=@NULL) and (cmaic.str_name="BODY_GROUP"
or cm.ERD_CMA_TYPE_VALUE_D in( 'TAG CLASSIFICATION','DEFAULT TSO TYPE'))
);

--PAN_COR HISTORIC_ANALYTIC_RESULT

ADD EXTRACT f_16, SOURCEISTABLE
start extract f_16

EXTRACT f_16
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/PAN_COR.HISTORIC_ANALYTIC_RESULT.txt, PURGE
TABLE PAN_COR.HISTORIC_ANALYTIC_RESULT  where ((publication_status="PUBLIC" and type="INDEX" and bgr_nda_id =@null and snapshot_date <> @null) and (
(ana_id="INDEX_DIV_YLD_VEND") or (ana_id="INDEX_PE_VEND") or (ana_id="INDEX_PRICE_TO_BOOK_VEND") or (ana_id="INDEX_EST_PE_Y1_VEND") or
(ana_id="INDEX_EST_PE_Y2_VEND") or (ana_id="INDEX_DIV_YLD_RTRS") or (ana_id="INDEX_PE_RTRS") or (ana_id="INDEX_PRICE_TO_BOOK_RTRS") or
(ana_id="INDEX_EST_PE_Y1_RTRS") or (ana_id="INDEX_EST_PE_Y2_RTRS") or (ana_id="INDEX_DIV_YLD_VEND") or (ana_id="INDEX_DIV_YLD_RTRS") or
(ana_id="INDEX_PRICE_TO_BOOK_VEND") or (ana_id="INDEX_PRICE_TO_BOOK_RTRS") or (ana_id="INDEX_EST_PE_Y1_VEND") or (ana_id="INDEX_EST_PE_Y1_RTRS") or
(ana_id="INDEX_DIV_YLD_REUTERS") or (ana_id="INDEX_PE_REUTERS") or (ana_id="INDEX_PRICE_TO_BOOK_REUTERS") or (ana_id="INDEX_EST_PE_Y1_REUTERS") or (ana_id="INDEX_EST_PE_Y2_REUTERS")));

--CFM_COR BALANCE_SHEET

ADD EXTRACT f_17, SOURCEISTABLE
start extract f_17

EXTRACT f_17
DBOPTIONS ALLOWUNUSEDCOLUMN
USERID ggs_owner, PASSWORD ggs_owner
FORMATASCII, NONAMES, DELIMITER '|' ,NOQUOTE , NOHDRFIELDS, NULLISSPACE
EXTFILE /oraarch/CNR/corecat/txt/CFM_COR.BALANCE_SHEET.txt, PURGE
TABLE CFM_COR.BALANCE_SHEET ;