PROCEDURE expoExcel
            ( p_comp_prop_id IN   NUMBER    DEFAULT NULL
             ,p_outType      IN   VARCHAR2  DEFAULT 'EXCEL'
             )
IS
CURSOR c_company( v_comp_prop_id   IN   VARCHAR2)
IS
  SELECT company_property_id
        ,company
        ,year
        ,action_status
        ,approver_wwid
        ,due_date
        ,comments
        ,email_address
        ,legel_entity_no
        ,legel_entity_name
        ,prepared_by_fname
        ,prepared_by_lname
        ,netting_Participant_no
        ,currency_code
        ,premium_bill_fname
        ,premium_bill_lname
  FROM  brvo_company_property
  WHERE company_property_id = v_comp_prop_id
  ORDER BY company;

r_company  c_company%rowtype;

CURSOR c_com_desc(v_comp   IN   VARCHAR2)
IS
  SELECT fv.description description
  FROM   fnd_flex_values_vl  fv
        ,fnd_flex_value_sets fs
  WHERE fs.flex_value_set_id   = fv.flex_value_set_id
  AND   fs.flex_value_set_name = 'BRVO_COMPANY'
  AND   fv.enabled_flag        ='Y'
  AND   fv.summary_flag        ='N'
  AND   fv.flex_value          = v_comp
  ORDER BY fv.flex_value;

r_com_desc  c_com_desc%rowtype;

CURSOR c_approver_list(v_wwid   IN   VARCHAR2)
IS
  SELECT approver_id
        ,approver_wwid
        ,approver_name
        ,approver_email
  FROM  brvo_ins_approver_list
  WHERE approver_wwid = v_wwid
  ORDER by approver_wwid,approver_name;

r_approver_list  c_approver_list%rowtype;

i               NUMBER := 0;
fldSep          CHAR(1);
v_comp_prop_id  NUMBER := 0;
l_baseUrl       VARCHAR2( 50 ) := bws.getBaseUrl;

BEGIN

   IF p_comp_prop_id is not null and p_comp_prop_id <> 0 THEN
      OPEN  c_company(p_comp_prop_id);
      FETCH c_company into r_company;
      CLOSE c_company;
      v_comp_prop_id := p_comp_prop_id;
   ELSE
      v_comp_prop_id := 0;
   END IF;

   OPEN  c_com_desc(r_company.company);
   FETCH c_com_desc into r_com_desc;
   CLOSE c_com_desc;

   OPEN c_approver_list(r_company.approver_wwid);
   FETCH c_approver_list into r_approver_list;
   CLOSE c_approver_list;

   IF ( p_outType = 'EXCEL' ) THEN
      fldSep := CHR(9);
      owa_util.mime_header( 'application/vnd.ms-excel', TRUE );
      htp.print(            'Year End'
               || fldSep || 'MRC'
               || fldSep || 'Description'
               || fldSep || 'Approver WWID'
               || fldSep || 'Approver First Name'
               || fldSep || 'Approver Last Name'
               || fldSep || 'Email Address'
               || fldSep || 'Legal No'
               || fldSep || 'Legal Name'
               || fldSep || 'Prepared By First Name'
               || fldSep || 'Prepared By Last Name'
               || fldSep || 'Premium Billing Contact First Name'
               || fldSep || 'Premium Billing Contact Last Name'
               || fldSep || 'Premium Billing Contact Currency'
               || fldSep || 'Netting Participant No'
               );

      htp.print(            r_company.year
               || fldSep || tic||r_company.company
               || fldSep || r_com_desc.description
               || fldSep || r_company.approver_wwid
               || fldSep || (substr(r_approver_list.approver_name,1,instr(r_approver_list.approver_name,' ')))
               || fldSep || (substr(r_approver_list.approver_name,instr(r_approver_list.approver_name,' '),length(r_approver_list.approver_name)))
               || fldSep || r_approver_list.approver_email
               || fldSep || r_company.legel_entity_no
               || fldSep || r_company.legel_entity_name
               || fldSep || r_company.prepared_by_fname
               || fldSep || r_company.prepared_by_lname
               || fldSep || r_company.premium_bill_fname
               || fldSep || r_company.premium_bill_lname
               || fldSep || r_company.currency_code
               || fldSep || r_company.netting_Participant_no
               );
   END IF;
END;

The output format is like this:

Year End    MRC	   Description	Approver WWID	Approver First Name	Approver Last Name	Email Address	Legal No	Legal Name	Prepared By First Name	Prepared By Last Name	Premium Billing Contact First Name	Premium Billing Contact Last Name	Premium Billing Contact Currency	Netting Participant No
2003	  '001220  Ethicon USA	UMA						                                                                UMA				                                                                                                USD	

The required output format is like this:

Year End    MRC	   Description	       Approver	                Email Address	   Legal                Prepared By             Premium Billing Contact   Premium Billing Contact Currency	Netting Participant No
                                    WWID   First Name   Last Name	                     No	   Name	   First Name     Last Name   First Name	Last Name	                                                                                    
2003	  '001220  Ethicon USA	UMA						                UMA				                          USD