view 생성하기

-- 문서분류,문서구분, 문서명, 펀드구분, 펀드명, 상신제목, 등록일, 등록자, 비고(appr01_note)

CREATE OR REPLACE FORCE VIEW DCR00_DOC_LIST ( DCR00_CLASS_TREE_NM, DCR00_PARENT_TREE_ID, DCR00_FUND_TYPE_NM, DCR00_FUND_NM, DCR00_FILE_NM, DCR00_REG_DATE, DCR00_REG_EMP_ID, DCR00_EMP_NM, DCR00_FUND_CLOSE_YN, DCR00_FUND_PUBLIC_YN, DCR00_FUND_SINTAK_GB, DCR00_FUND_TYPE_CD, DCR00_FUND_START_DATE, DCR00_APR_TITLE, DCR00_ORG_NM )

AS

select dcr01_class_tree_nm as dcr00_class_tree_nm

,dcr01_parent_tree_id as dcr00_parent_tree_id

,fnd00_fund_type_nm as dcr00_fund_type_nm

,fnd00_fund_nm as dcr00_fund_nm

,sys10_file_nm as dcr00_file_nm

,apr01_reg_date as dcr00_reg_date

,apr01_reg_emp_id as dcr00_reg_emp_id

,emp00_kor_nm as dcr00_emp_nm

,fnd00_close_yn as dcr00_fund_close_yn

,fnd00_public_yn as dcr00_fund_public_yn

,fnd00_sintak_gb as dcr00_fund_sintak_gb

,fnd00_fund_type_cd as dcr00_fund_type_cd

,fnd00_start_date as dcr00_fund_start_date

,apr01_title as dcr00_apr_title

,emp00_org_nm as dcr00_org_nm

 from apr01_appr

, sys10_file 

    , apr03_relate_fund

      , fnd00_fund

    , dcr01_class_tree

    , emp00_info

 where apr01_appr_id = sys10_parent_id

 and apr01_class_tree_id = dcr01_class_tree_id 

 and apr01_appr_id = apr03_appr_id

 and apr03_fund_id = fnd00_fund_id

 and apr01_reg_emp_id = emp00_emp_id 

 and      fnd00_work_date = f_max_workdate(fnd00_fund_id, sysdate)

 and      apr01_reg_date  between emp00_trans_date and emp00_close_date