WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR  EXIT FAILURE ROLLBACK;


spool FND_CODE_RULE_DOCUMENT_TYPES_V.log

prompt
prompt Creating view FND_CODE_RULE_DOCUMENT_TYPES_V
prompt =========================================
prompt
create or replace force view fnd_code_rule_document_types_v as
select t."VALUE",
       t."CODE",
       t."NAME",
       t."TYPE",
       t."COMPANY_ID",
       fc.company_code,
       fc.company_short_name
  from (select to_char(t.expense_requisition_type_id) value,
               t.expense_requisition_type_code code,
               t.description name,
               '01' type,
               t.company_id
          from exp_expense_req_types_vl t
         where t.enabled_flag = 'Y'
        union all
        select to_char(t.expense_report_type_id) value,
               t.expense_report_type_code code,
               t.description name,
               '02' type,
               t.company_id
          from exp_expense_report_types_vl t
         where t.enabled_flag = 'Y'
        union all
        select t.document_type_code value,
               t.document_type_code code,
               t.description name,
               '20' type,
               null company_id
          from fnd_attach_document_types_vl t
         where t.enabled_flag = 'Y'
        union all
        select to_char(t.contract_type_id) value,
               t.contract_type_code code,
               t.description name,
               '07' type,
               d.company_id
          from con_contract_types_vl t, con_contract_type_dists d
         where t.enabled_flag = 'Y'
           and d.enabled_flag = 'Y'
           and d.contract_type_id = t.contract_type_id
        union all
        select to_char(v.type_id) value,
               v.type_code code,
               v.description name,
               '04' type,
               v.company_id
          from csh_pay_req_types_vl v
         where v.enabled_flag = 'Y'
        union all
        select to_char(v.acp_req_type_id) value,
               v.acp_req_type_code code,
               v.description name,
               '08' type,
               null company_id
          from acp_sys_acp_req_types_vl v
         where v.enabled_flag = 'Y'
        union all
        select to_char(v.bank_treasurer_req_type_id) value,
               v.bank_treasurer_req_type_code code,
               v.description name,
               '09' type,
               null company_id
          from csh_bank_treas_req_types_vl v
         where v.enabled_flag = 'Y' union all
        select to_char(v.budget_structure_id) value,
               v.budget_strc_code code,
               v.description name,
               '06' type,
               to_number(null) company_id
          from bgt_budget_structures_vl v
         where v.enabled_flag = 'Y'
        union all
        select to_char(pr.project_type_id) value,
               pr.project_type_code code,
               pr.description name,
               '10' type,
               to_number(null) company_id
          from prj_project_types_vl pr
         where pr.enabled_flag = 'Y') t,
       fnd_companies_vl fc
 		where t.company_id(+) = fc.company_id;
/

spool off
exit
