SELECT O_DTL.ORDER_ID ,
  O_DTL.INSPECTION_TYPE_ID ,
  (SELECT TYP.INSPECTOR_TYPE_NAME
  FROM INS_INSPECTION_TYPE TYP
  WHERE TYP.INSPECTION_TYPE_ID = O_DTL.INSPECTION_TYPE_ID
  )INSPECTION_TYPE_DESC ,
  O_MST.SOURCE_TYPE ,
  (SELECT MAX(VISIT_ID)
  FROM INS_VISIT_ORDER_DTL
  WHERE INS_VISIT_ORDER_DTL.ORDER_ID = O_DTL.ORDER_ID
  ) VISIT_ID ,
  (SELECT MAX(VISIT_ID)
  FROM INS_VISIT_ORDER_DTL
  WHERE INS_VISIT_ORDER_DTL.ORDER_ID = O_DTL.ORDER_ID
  ) VISIT_CODE ,
  LICN.COMPANY_PROJECT_NAME ,
  LICN.LICN_CON_NO LICN_CIVIL_ID ,
  LICN.LF_RECID ,
  O_DTL.ORDER_DATE ,
  AUTO_NUM_PACI,
  DECODE (
  (SELECT COUNT(CLS.CLAUSE_CODE) FROM PAM_DB.INS_VIOLATION_AVOIDANCE_DTL DTL ,
    PAM_DB.INS_LAW_CLAUSE CLS ,
    PAM_DB.INS_VISIT_ORDER_DTL V_ORD_DTL WHERE DTL.CLAUSE_CODE = CLS.CLAUSE_CODE
  AND DTL.CLAUSE_STATUS                                        = 1
  AND V_ORD_DTL.VISIT_ID                                       =V_ORD_DTL.VISIT_ID
  AND V_ORD_DTL.ORDER_ID                                       =O_DTL.ORDER_ID
  ),0,0,1 ) COUNT_VIO ,
  O_DTL.INS_SCOPE_ID ,
  (SELECT V_ORD.LOCATION_STATUS
  FROM PAM_DB.INS_VISIT_ORDER V_ORD,
    PAM_DB.INS_VISIT_ORDER_DTL V_ORD_DTL
  WHERE V_ORD.VISIT_ID   = V_ORD_DTL.VISIT_ID
  AND V_ORD_DTL.order_id =O_DTL.order_id
  and V_ORD.VISIT_STATUS = 2 
   and V_ORD.LF_RECID = O_DTL.LF_RECID
  ) Arrival_status_id
   
FROM PAM_DB.INS_SOURCE_ORDER O_MST ,
  PAM_DB.INS_SOURCE_ORDER_DTL O_DTL ,
  MOSAL_USER.NMP_LICNFILE_MV LICN
WHERE O_MST.SOURCE_ID = O_DTL.SOURCE_ID
AND O_DTL.LF_RECID    = LICN.LF_RECID
AND INS_ORDER_STATUS  = 3
AND LICN.ONR_ID       = :p_ONR_ID ;