این گزارش شامل اطلاعات پزشکان به همراه شماره نظام های آنها و نوع تخصص می باشد.

select * from 
(
select 
    p.id
    ,(select name from vpncgeoinfo where year=93 and province_id=g.province_id and recordtype=1) as provincename
    ,(select name from vpncgeoinfo where year=93 and province_id=g.province_id and cityes_id = g.cityes_id and recordtype=2) as cityesname
    ,(select name from tpncpartnertype where id=p.type_id) as PartneType
    ,p.nationalnumber as partnernn
    ,p.partnername
    ,(select pkgadditionalpropertyutil.fncPropertyPartnerValue(p.id,'siamCode') from dual) as siamcode
    ,cp.name as cpartyName
    ,cp.lastname as cpartylastname
    ,cp.nationalnumber as cpartynn
    ,(select LISTAGG((select name from tpnccontractpartytype where id=pcpt.contractpartytype_id), ',') WITHIN GROUP (ORDER BY pcpt.contractpartytype_id) 
        from tpncpartnercontractpartytype pcpt where pcpt.contractparty_id=pc.contractparty_id and pcpt.partner_id=p.id and pcpt.dto is null) as cpartyTypes
    ,(select LISTAGG((select name from tpnclicensetype where id=l.licensetypeowner_id), ',') WITHIN GROUP (ORDER BY l.id) 
        from tpnclicense l where l.partnercontractingparty_id=pc.id and l.dto is null) as license_Type
    ,(select LISTAGG(l.nomedicalsystem, ',') WITHIN GROUP (ORDER BY l.id) 
        from tpnclicense l where l.partnercontractingparty_id=pc.id and l.dto is null) as license_NomedicalSystem
    ,(select LISTAGG(to_char(l.validto,'yyyymmdd','nls_calendar=persian'), ',') WITHIN GROUP (ORDER BY l.id) 
        from tpnclicense l where l.partnercontractingparty_id=pc.id and l.dto is null) as license_Validto
    ,trim(replace(a.mainstreet || ' ' || a.streetname || ' ' || a.mainalley,'  ',' ')) as partnerAddress
    ,a.zipcode as zipcode
    ,a.fixphoneno
    ,a.mobilephoneno
from tpncpartner p,tpncaddress a,tpncpartnercontractingparty pc,tpnccontractparty cp,vpncgeoinfo g
where p.address_id=a.id
        and p.id=pc.partner_id
        and pc.contractparty_id=cp.id
        and a.geo_id=g.id
        and p.dto is null
        and pc.dto is null
        and cp.dto is null
) where license_NomedicalSystem is not null;