create or replace view prj_project_history_lv as
select (select pp.project_number
             from prj_project pp
            where pp.project_id = bp.project_id) project_number,
          (select cc.contract_number
             from con_contract cc
            where cc.project_id = bp.project_id) contract_number,
            (select cc.contract_id
             from con_contract cc
            where cc.project_id = bp.project_id) contract_id,
          bp.bp_code,
          bp.bp_name,
          (select cc.overdue_max_days
             from con_contract cc
            where cc.project_id = bp.project_id) overdue_max_days,
          (select max(cc.overdue_amount)
             from con_contract cc
            where cc.project_id = bp.project_id) overdue_max_amount,
          bp.bp_type,
          bp.bp_id,
          bp.project_id,
           bp.bp_class,
          (select hbt.description
             from hls_bp_type hbt
            where hbt.bp_type = bp.bp_type) description

     from prj_project_bp bp;