PAMInspection

SELECT INSPECTOR_ID ,

  SEC_PKG.GET_USER_NAME(INSPECTOR_ID) NAME,

 SUM(FETCHED_ORDERS) ,

  SUM(ORDERS_WITH_RESULTS) ORDERS_WITH_RESULTS ,

  SUM(CLOSED_VISITS)  CLOSED_VISITS ,

  SUM(RESULT_APOLOGIES ) RESULT_APOLOGIES,

(select INS_SCOPE_NAME  from INS_INSPECTION_SCOPE where INS_SCOPE_ID =:PINS_SCOPE_Id)scopenaem

FROM INS_STATISTICAL_ANALYSIS

WHERE (INSPECTOR_ID = :inspector_id

      OR :inspector_id

      IS NULL )

AND  ( visit_date >= trunc ( TO_DATE ( :date_first,'yyyy-mm-dd')+1)

or :date_first is null )

AND ( VISIT_DATE <= TRUNC (to_date(:end_date,'yyyy-mm-dd')+1)

or :end_date is NULL )

and(select INS_SCOPE_ID from INS_INSPECTOR_PROFILE where INSPECTOR_ID = INS_STATISTICAL_ANALYSIS.INSPECTOR_ID)

=:PINS_SCOPE_Id

GROUP BY INSPECTOR_ID ,

  SEC_PKG.GET_USER_NAME(INSPECTOR_ID)

ORDER BY NAME ,INSPECTOR_ID