OKC Contract Header Preview Data Definition

SELECT orc.CONTRACT_VERSION_NUM, 
                orc.CONTRACT_NAME, 
                orc.CONTRACT_NUMBER, 
                orc.CONTRACT_DESC, 
                orc.CONTRACT_TYPE,        
                orc.VERSION_COMMENTS,               
                TO_CHAR(orc.CONTRACT_EFFECTIVE_DATE, NVL(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'DD-MON-RRRR')) CONTRACT_EFFECTIVE_DATE,  
                TO_CHAR(orc.CONTRACT_EXPIRATION_DATE, NVL(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'DD-MON-RRRR')) CONTRACT_EXPIRATION_DATE, 
                orc.CURRENCY_CODE, 
                orc.AMOUNT,        
                orc.CANCELLATION_COMMENTS, 
                orc.CANCELLATION_DATE, 
                orc.TERMINATION_COMMENTS, 
                orc.TERMINATION_DATE, 
                orc.KEYWORDS, 
                orc.PHYSICAL_LOCATION,
                fl.meaning contract_status,
                (SELECT name
                 FROM   Hr_all_organization_units
                 WHERE  organization_id = orc.org_id)   contract_organization,
                (SELECT nvl(pf.full_name, fu.user_name)
                 FROM   Per_all_people_f   pf,
                        fnd_user    fu
                 WHERE  fu.user_id = orc.owner_id
                 AND    pf.person_id (+) = fu.employee_id
                 AND   (fu.employee_id IS NULL OR pf.effective_start_date = (SELECT MAX(effective_start_date)
                                                                             FROM   per_all_people_f
                                                                             WHERE  person_id = fu.employee_id)))  contract_owner,
                obd.name    contract_type_name,       
                fl1.meaning  intent,
                (SELECT meaning
                 FROM   okc_lookups_v
                 WHERE  lookup_type = 'OKC_RISK_LEVELS'
                 AND    lookup_code = orc.OVERALL_RISK_CODE)  overall_risk,
                (SELECT meaning
                 FROM   okc_lookups_v
                 WHERE  lookup_type = 'OKC_AUTHORING_PARTY'
                 AND    lookup_code = orc.AUTHORING_PARTY_CODE)  authoring_party,
                orc.contract_status_code,
                (SELECT meaning	 
                 FROM   okc_lookups_v	 
                 WHERE  lookup_type = 'OKC_REP_CONTRACT_STATUSES'	 
                 AND    lookup_code = orc.contract_status_code) status_code_meaning,
                obd.intent  intent_code,
                orc.contract_id,
                orc.org_id,
                orc.ATTRIBUTE_CATEGORY,
                orc.ATTRIBUTE1,
                orc.ATTRIBUTE2,
                orc.ATTRIBUTE3,
                orc.ATTRIBUTE4,
                orc.ATTRIBUTE5,
                orc.ATTRIBUTE6,
                orc.ATTRIBUTE7,
                orc.ATTRIBUTE8,
                orc.ATTRIBUTE9,
                orc.ATTRIBUTE10,
                orc.ATTRIBUTE11,
                orc.ATTRIBUTE12,
                orc.ATTRIBUTE13,
                orc.ATTRIBUTE14,
                orc.ATTRIBUTE15, 
                Curr.Name Currency_Name,
                orc.REFERENCE_DOCUMENT_TYPE,
                orc.REFERENCE_DOCUMENT_NUMBER ,
         (SELECT
         meaning
         FROM fnd_lookups
         WHERE lookup_type = 'OKC_REP_CONTRACT_SIGN'
         AND lookup_code <> 'A'
         AND enabled_flag = 'Y'           
         and lookup_code = orc.ESignature_Required 
         ) ESignature_Required,
         orc.SBCR_COORDINATION_TYPE,
         fl2.meaning as SBCR_COORDINATION_MEANING    
         FROM OKC_REP_CONTRACTS_ALL orc,     
              okc_lookups_v  fl,
              okc_bus_doc_types_vl  obd,
              okc_lookups_v  fl1,     
              Fnd_Currencies_Tl Curr,
              fnd_lookups fl2     
         WHERE orc.contract_id = :contractId 
         AND   (:contractVersion is null OR orc.contract_version_num = :contractVersion)
         AND   fl.lookup_type = 'OKC_REP_CONTRACT_STATUSES' 
         AND   fl.lookup_code = orc.CONTRACT_STATUS_CODE
         AND   obd.document_type = orc.Contract_Type
         AND   fl1.lookup_type = 'OKC_REP_CONTRACT_INTENTS'
         AND   fl1.lookup_code = obd.intent
         AND   orc.Currency_Code = Curr.Currency_Code (+)
         AND   Curr.Language (+) = Userenv('LANG')
         AND   fl2.lookup_type (+) = 'OKC_REP_SBCR_COOR_TYPE' 
         AND   fl2.lookup_code (+) = orc.SBCR_COORDINATION_TYPE