CREATE OR REPLACE VIEW APV_SHIHARI_TSUJISYO AS
SELECT
  G.PROJECT_NO AS PROJECT_N0,
  G.JOB_NO AS JOB_NO,
  G.GENKA_NO AS GENKA_NO,
  --G.SHIHARAISAKI_CD AS SHIHARAISAKI_CD,
  G.NOUHIN_JISSHI_YMD AS NOUHIN_JISSHI_YMD,
  --G.HINMOKU_CD ASHINMOKU_CD ,
  G.SURYOU AS SURYOU,
  G.TANKA AS TANKA,
  G.SHIHARAI_YOTEI_YMD AS SHIHARAI_YOTEI_YMD,
  
  K.SHIHARAISAKI_CD AS SHIHARAISAKI_CD,
  K.SHOUGOU_GAKU AS SHOUGOU_GAKU ,
  K.SHOUGOU_SHOUHIZEI_GAKU AS SHOUGOU_SHOUHIZEI_GAKU,
  K.JITSUBARAI_GAKU AS JITSUBARAI_GAKU,
  T.TORI_CD AS TORI_CD ,
  T.ADDRESS1 AS ADDRESS1,
  T.ADDRESS2 AS ADDRESS2,
  T.TORI_NAME1 AS TORI_NAME1,
  T.TORI_NAME2 AS TORI_NAME2,
 -- H.HINMOKU_CD AS HINMOKU_CD,
  H.HINMOKU_NAME AS HINMOKU_NAME
 -- ADD BY CHEN
  ,KTD.BIKOU1 
FROM 
  APT_GENKA_DTL G,
  APT_KAIKAKE_SHOUGOU K,
  APM_TORI T,
  APM_HINMOKU H
  -- ADD BY CHEN
  ,APT_KOUTEI_DTL KTD
WHERE
  G.SHIHARAI_SUMI_KBN<>'1' AND
  K.PROJECT_NO = G.PROJECT_NO AND
  K.JOB_NO = G.JOB_NO AND
  K.GENKA_NO = G.GENKA_NO AND
  T.TORI_CD = G.SHIHARAISAKI_CD AND 
  H.HINMOKU_CD(+) = G.HINMOKU_CD
  -- ADD BY CHEN
  AND G.Koutei_No = KTD.KOUTEI_NO(+)
  AND G.Koutei_Dtl_No = KTD.KOUTEI_DTL_NO(+)