View Definition:
SELECT UPPER(i.db_schema) ||'_'|| p.property_id ID,
UPPER(i.db_schema) county,
p.property_id AS pid,
ptc.property_type_desc AS ptypedesc,
p.indx_sec_level_code AS pseccode,
p.indx_sec_level_id AS psecurity,
p.property_type_code AS ptype,
p.description AS pdesc,
p.primary_color AS pcolor,
p.secondary_color AS scolor,
p.make AS make,
p.model AS model,
p.VALUE AS pvalue,
p.quantity AS quantity,
p.serial_number AS serialnumber,
p.misc AS misc,
p.drug_type AS drugtype,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.DRUG_TYPE_TYPE AND EC.CODE = P.DRUG_TYPE) AS DRUGTYPEDESC,
p.quantity AS quanitity,
p.type_of_quantity AS type_of_quantity,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.type_of_quantity_TYPE AND EC.CODE = P.type_of_quantity) AS type_of_quantity_desc,
p.document_number AS doc_number,
TO_CHAR (p.document_date, 'MM/DD/YYYY HH:MI AM') AS doc_date,
p.account_name AS accountname, p.account_number AS account_number,
p.amount AS amount, p.bank AS bank, p.payable_to AS payable_to,
p.endorsee AS endorsee,
p.gun_typ_code AS guntype,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.GUN_TYP_CODE_TYPE AND EC.CODE = P.GUN_TYP_CODE) AS GUNTYPEDESC,
p.gun_act_code AS gunact,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.GUN_ACT_CODE_TYPE AND EC.CODE = P.GUN_ACT_CODE) AS GUNACTDESC,
p.calgag_code AS calgagcode,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.CALGAG_CODE_TYPE AND EC.CODE = P.CALGAG_CODE) AS CALGAGDESC,
p.barrel_length AS barrael_length,
p.document AS document,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.DOCUMENT_TYPE AND EC.CODE = P.DOCUMENT) AS DOCUMENT_DESC,
p.indx_sec_level_id AS secid,
p.currency_code AS currencycode,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.CURRENCY_CODE_TYPE AND EC.CODE = P.CURRENCY_CODE) AS CURRENCY_DESC,
pc.denomination_code AS denomcode,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = PC.DENOMINATION_CODE_TYPE AND EC.CODE = PC.DENOMINATION_CODE) AS DENOMDESC,
pc.quantity AS curncyquantity,
pd.prop_dtl_type AS prop_dtl_type,
pd.pd_comment AS pcomment,
pp.role_code AS people_role,
bp.busns_business_number AS businessnumber,
bp.pprc_role_code AS businessrole,
mn.lname AS lastname,
mn.fname AS firstname,
mn.name_type_code AS namecode,
p.creator_id AS createdby,
p.creator_date AS creation_date,
to_char(from_tz(cast(p.creator_date as timestamp), (select ec.code from ejs_codes ec where ec.code_type = 'DATABASE_TIMEZONE' and rownum = 1)) at time zone 'UTC','YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"'),
p.updator_date AS modification_date,
p.updator_id AS modifiedby,
(SELECT ec.description
FROM ejs_codes ec
WHERE UPPER (ec.code) = UPPER (mn.name_type_code)
AND ec.code_type = 'NAME_TYPE_CODES') AS nametype,
(SELECT ec.description
FROM ejs_codes ec
WHERE UPPER (ec.code) =
UPPER (bp.pprc_role_code)
AND ec.code_type = 'PEOP_PROP_ROLE_CODES') AS businessroledesc,
b.business_name AS businessname, b.business_id AS businessid,
b.btc_type_code AS businesstypecode,
(SELECT ec.description
FROM ejs_codes ec
WHERE UPPER (ec.code) =
UPPER (b.btc_type_code)
AND ec.code_type = 'BUSINESS_TYPE_CODES') AS businesstypedesc,
(SELECT ec.description
FROM ejs_codes ec
WHERE UPPER (ec.code) = UPPER (p.primary_color)
AND ec.code_type = 'COLOR_CODES') AS pcolor_desc,
(SELECT ec.description
FROM ejs_codes ec
WHERE UPPER (ec.code) = UPPER (p.secondary_color)
AND ec.code_type = 'COLOR_CODES') AS scolor_desc,
IP.PROP_LOSS_CODE,
IP.STATUS_CODE,
PCC.CODE AS PROPERTY_CATEGORY_CODE,
PCC.DESCRIPTION AS PROPERTY_CATEGORY_DESC,
mp.INDX_SEC_LEVEL_CODE as person_security_level
FROM install i, properties p LEFT OUTER JOIN property_cash pc
ON p.property_id = pc.property_id
LEFT OUTER JOIN property_details pd ON p.property_id =
pd.property_id
LEFT OUTER JOIN people_properties pp ON p.property_id =
pp.property_id
LEFT OUTER JOIN people pe ON pp.person_id = pe.person_id
LEFT OUTER JOIN master_people mp ON pe.master_person_id = mp.master_person_id
LEFT OUTER JOIN master_names mn ON pp.person_id = mn.per_person_id
LEFT OUTER JOIN business_properties bp
ON p.property_id = bp.pro_property_id
LEFT OUTER JOIN businesses b
ON bp.busns_business_number = b.business_number
LEFT OUTER JOIN property_type_codes ptc
ON p.property_type_code = ptc.property_type
LEFT OUTER JOIN EJS_CODES PCC ON PTC.PROPERTY_CATEGORY_TYPE = PCC.CODE_TYPE AND PCC.CODE = PTC.PROPERTY_CATEGORY
LEFT OUTER JOIN incident_properties ip
ON P.PROPERTY_ID = IP.PROPERTY_ID
