PO Buyers

-- BASIC BUYERS VIEW

SELECT   papf.full_name
       , pa.start_date_active buyer_start
       , pa.end_date_active buyer_end
       , pa.attribute1 telno
       , pa.last_update_date
       , fu.user_name
       , fu.description
       , fu.end_date ibs_end
       , haout.NAME hr_org
       , hlat.description LOCATION
      , (SELECT COUNT(*)
           FROM po.po_headers_all pha
              , apps.po_agents pa
          WHERE pa.agent_id = pha.agent_id
            AND pa.agent_id = papf.person_id
            AND pha.creation_date >= '04-DEC-2007') po_count
      , (SELECT MAX(pha.creation_date)
           FROM po.po_headers_all pha
              , apps.po_agents pa
          WHERE pa.agent_id = pha.agent_id
            AND pa.agent_id = papf.person_id) last_po_raised
      , TRIM(
           TO_CHAR(
              MONTHS_BETWEEN(
                 SYSDATE
               , (SELECT MAX(pha.creation_date)
                    FROM po.po_headers_all pha
                       , apps.po_agents pa
                   WHERE pa.agent_id = pha.agent_id
                     AND pa.agent_id = papf.person_id)
              )
            , 9999.99
           )
        ) months_since_last_po
      , TRIM(TO_CHAR(MONTHS_BETWEEN(SYSDATE, pa.start_date_active), 9999.99))
                                                             months_as_buyer
    FROM po.po_agents pa
       , hr.per_all_people_f papf
       , applsys.fnd_user fu
       , hr.hr_all_organization_units_tl haout
       , hr.hr_locations_all_tl hlat
       , hr.per_all_assignments_f paaf
   WHERE pa.agent_id = papf.person_id
     AND fu.employee_id(+) = papf.person_id
     AND papf.person_id = paaf.person_id(+)
     AND paaf.location_id = hlat.location_id(+)
     AND paaf.organization_id = haout.organization_id(+)
     AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
     AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
     AND paaf.assignment_type = 'E'
     AND paaf.primary_flag = 'Y'
     and papf.full_name LIKE :pn
     AND pa.end_date_active IS NULL
     AND pa.end_date_active = '03-JUN-2008'
ORDER BY 1;