SELECT TO_NUMBER (l.loan_number) AS srvcnum,
l.product_type_id AS cmdm_Market_Type_num,
,d.documentation_level_desc as cmdm_doc_type,
sf.SPECIAL_FEATURE_CODE as Harp_CODE
FROM CMDMmgr.loan l
LEFT JOIN CMDMmgr.loan_origination o
ON l.loan_number = o.loan_number
LEFT JOIN CMDMmgr.LOAN_SPECIAL_FEATURE lsf
ON l.loan_number = lsf.loan_number
JOIN CMDMmgr.documentation_level_type d on o.documentation_level_id = d.documentation_level_id
JOIN CMDMmgr.SPECIAL_FEATURE sf on TO_NUMBER (lsf.SPECIAL_FEATURE_ID) = sf.SPECIAL_FEATURE_ID
WHERE (product_type_id in (103, 104, 108, 116, 117, 119, 123,
731, 732, 733, 734, 735, 737, 738, 739)
or documentation_level_desc in ('Signature NCO New Value' 'Signature NCO Orig Value')
or SPECIAL_FEATURE_CODE in ('X908' 'T746' 'T786' 'T839' 'T840')
