SELECT A.*
          , B.htl_name
          , FN_BA_COMP_INFO_R01(A.rs_comp_seq,'COMP_NM') AS channel
          , FN_SY_CLASS_R01(A.rs_status_cd,'CLASS_NM') AS rs_status_nm
          , FN_SY_CLASS_R01(A.pay_status_cd,'CLASS_NM') AS pay_status_nm
          , (SELECT SUM(X.pay_amt) FROM RS_HPAY X WHERE X.rs_hmst_seq = A.rs_hmst_seq AND X.in_yn = 'Y') AS pay_amt
          , (SELECT SUM(X.pay_amt) FROM RS_HBANK X WHERE X.rs_hmst_seq = A.rs_hmst_seq AND X.pay_yn = 'Y') AS jibul_amt
          , (SELECT SUM(X.price) FROM RS_HROOM X WHERE X.rs_hmst_seq = A.rs_hmst_seq) AS base_amt
          , C.max_cnt
          , D.room_name
          , B.tel
          , B.homepage_url
FROM RS_HMST A
INNER JOIN HT_MST B ON A.ht_mst_seq = B.ht_mst_seq
LEFT OUTER JOIN RS_HROOM C ON A.rs_hmst_seq = C.rs_hmst_seq
LEFT OUTER JOIN HT_ROOM_INFO D ON C.ht_room_info_seq = D.ht_room_info_seq
WHERE A.rs_nm = :rs_nm
AND replace(A.rs_hp,'-','') = :rs_hp
AND A.rs_hmst_code = :rs_hmst_code
LIMIT 1
