SELECT P.PERMIT_ID , P.PERMIT_NO , P.PERMIT_TYPE_CODE , (SELECT TYP.DESCRIPTION FROM EJS_CODES TYP WHERE TYP.CODE = P.PERMIT_TYPE_CODE AND TYP.CODE_TYPE = P.PERMIT_TYPE_CODE_TYPE) , P.PERMIT_STATUS_CODE , (SELECT ST.DESCRIPTION FROM EJS_CODES ST WHERE ST.CODE = P.PERMIT_STATUS_CODE AND ST.CODE_TYPE = P.PERMIT_STATUS_CODE_TYPE) , P.PERMIT_REASON_CODE , (SELECT RSN.DESCRIPTION FROM EJS_CODES RSN WHERE RSN.CODE = P.PERMIT_REASON_CODE AND RSN.CODE_TYPE = P.PERMIT_REASON_CODE_TYPE) , P.APP_DATE , P.ISSUED_BY , P.ISSUE_DATE , P.PROCESS_DATE , P.PROCESSED_BY , P.EXPIRE_DATE , P.REVIEW_DATE , P.REVIEWED_BY , P.COMMENTS , P.CREATOR_ID , P.CREATOR_DATE , P.UPDATOR_ID , P.UPDATOR_DATE --, PERMIT_REASON_CODE_TYPE --, PERMIT_STATUS_CODE_TYPE --, PERMIT_TYPE_CODE_TYPE , P.REC_ID , P.AGENCY_CODE , (SELECT AG.AGENCY_DESC FROM AGENCY_CODES AG WHERE AG.AGENCY_CODE = P.AGENCY_CODE) --, P.PURCHASE_TYPE_CODE_TYPE , P.PURCHASE_TYPE_CODE , (SELECT PUR.DESCRIPTION FROM EJS_CODES PUR WHERE PUR.CODE = P.PURCHASE_TYPE_CODE AND PUR.CODE_TYPE = P.PURCHASE_TYPE_CODE_TYPE) , P.NEXT_INSPECTION_DATE , P.INSPECTED_DATE , P.INSPECTED_BY , P.NEXT_REVIEW_DATE --, PP.permit_role_code , JOIN (CURSOR ( SELECT INITCAP(R.DESCRIPTION || DECODE(MN.FNAME||MN.LNAME, NULL, NULL, ': '|| MN.FNAME||' '||MN.LNAME)) FROM PERMIT_PEOPLE PP INNER JOIN PEOPLE PEOP ON PP.PERSON_ID = PEOP.PERSON_ID INNER JOIN MASTER_NAMES MN ON PEOP.PERSON_ID = MN.PER_PERSON_ID INNER JOIN EJS_CODES R ON R.CODE = PP.PERMIT_ROLE_CODE AND R.CODE_TYPE = PP.PERMIT_ROLE_CODE_TYPE WHERE P.PERMIT_ID = PP.PERMIT_ID AND MN.NAME_TYPE_CODE = 'P'), ' | ' ) AS PERSON_INFO , JOIN (CURSOR ( SELECT INITCAP(R.DESCRIPTION || DECODE(B.BUSINESS_NAME, NULL, NULL, ': '||B.BUSINESS_NAME)) FROM PERMIT_BUSINESSES PB INNER JOIN BUSINESSES B ON PB.BUSINESS_NUMBER = B.BUSINESS_NUMBER INNER JOIN EJS_CODES R ON R.CODE = PB.PERMIT_ROLE_CODE AND R.CODE_TYPE = PB.PERMIT_ROLE_CODE_TYPE WHERE P.PERMIT_ID = PB.PERMIT_ID), ' | ' ) AS ORGANIZATION_INFO , JOIN (CURSOR (SELECT 'Index ID: '||PV.VEHICLE_ID ||' - ' || VTC.TYPE_DESC ||' - '|| VM.VEHICLE_MAKE_DESC ||' '|| VMO.VEHICLE_MODEL_DESC FROM PERMIT_VEHICLES PV INNER JOIN VEHICLES V ON PV.VEHICLE_ID = V.VEHICLE_ID INNER JOIN VEHICLE_TYPE_CODES VTC ON V.VTC_TYPE_CODE = VTC.TYPE_CODE LEFT OUTER JOIN VEHICLE_MAKES VM ON V.VMA_VEHICLE_MAKE_CODE = VM.VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_MODELS VMO ON V.VMO_VEHICLE_MODEL_CODE = VMO.VEHICLE_MODEL_CODE AND V.VMA_VEHICLE_MAKE_CODE = VMO.VMA_VEHICLE_MAKE_CODE AND V.VTC_TYPE_CODE = VMO.VTC_TYPE_CODE WHERE P.PERMIT_ID = PV.PERMIT_ID), ' | ' ) AS VEHICLE_INFO , JOIN (CURSOR (SELECT 'Index ID: '||PPROP.PROPERTY_ID ||' - ' || PTC.PROPERTY_TYPE_DESC FROM PERMIT_PROPERTIES PPROP INNER JOIN PROPERTIES PROP ON PPROP.PROPERTY_ID = PROP.PROPERTY_ID INNER JOIN PROPERTY_TYPE_CODES PTC ON PROP.PROPERTY_TYPE_CODE = PTC.PROPERTY_TYPE WHERE P.PERMIT_ID = PPROP.PERMIT_ID), ' | ' ) AS PROPERTY_INFO FROM PERMITS P