begin  
        with       
            base_prop as (                                     
                select /*+ MATERIALIZE */ property_id, priority  
                from   PM_EXCHANGE_PROPERTY_PRIORITY ppp  
                where  ppp.exchange_affiliation = g_proc_name  
                and    ppp.master_property_id in (select ppp2.master_property_id  
                                                             from   P_PM_PROPERTY ppp1, PM_EXCHANGE_PROPERTY_PRIORITY ppp2  
                                                             where  ppp1.rci_id_number = rci_dep_rec.resort_id  
                                                             and    ppp1.property_active = 'Y'  
                                                            and    ppp1.exchange_affiliation = ppp.exchange_affiliation  
                                                             and    ppp2.property_id = ppp1.property_id)  
                union  
                select property_id, 99999  
                from   P_PM_PROPERTY ppp  
                where  ppp.rci_id_number = rci_dep_rec.resort_id  
                and    ppp.property_active = 'Y'  
                and    not exists (select 1 from PM_EXCHANGE_PROPERTY_PRIORITY ppp1 where ppp1.property_id = ppp.property_id)   
                order by 2  
            )  
        select pput.pm_unit_type_id  
      bulk collect into pm_unit_type_ids  
       from   P_PM_UNIT_TYPE pput, S_PM_EXCHANGE_MAPPING spem, base_prop bp  
       where  spem.exchange_affiliation = g_proc_name  
       and    spem.resortcode = rci_dep_rec.resort_id  
       and    ((spem.unitnumber = rci_dep_rec.unit_no and rci_dep_rec.resv_type = 'WEEKS')  
              or  (spem.unitnumber = rci_dep_rec.unit_type and rci_dep_rec.resv_type = 'POINTS'))  
       and    rci_dep_rec.start_dt between spem.begin_date and spem.end_date  
       and    pput.property_id = spem.property_id  
       and    pput.pm_unit_type_active = 'Y'  
       and    instr(spem.unittypecode, pput.pm_unit_type) > 0  
       and    spem.property_id = bp.property_id  
       ORDER BY bp.priority,pput.pm_unit_type_priority;  
    end;  

select /*+ MATERIALIZE */ property_id, priority  
                from   PM_EXCHANGE_PROPERTY_PRIORITY ppp  
                where  ppp.exchange_affiliation = 'EZY'  
                and    ppp.master_property_id in (select ppp2.master_property_id  
                                                             from   P_PM_PROPERTY ppp1, PM_EXCHANGE_PROPERTY_PRIORITY ppp2  
                                                             where  ppp1.rci_id_number = '8789'  
                                                             and    ppp1.property_active = 'Y'  
                                                            and    ppp1.exchange_affiliation = 'RCI,II'  
                                                             and    ppp2.property_id = 'VDR')  
           
        union  
                select property_id, 99999  
                from   P_PM_PROPERTY ppp  
                where  ppp.rci_id_number ='8789'  
                and    ppp.property_active = 'Y'  
                and    not exists (select 1 from PM_EXCHANGE_PROPERTY_PRIORITY ppp1 where ppp1.property_id = 'VDR')   
                order by 2