VIEW: APPS.OKL_SLA_TRX_INFO_V

Source

View Text - Preformatted

SELECT tcn.id id ,DECODE( chr.scs_code, 'LEASE', CHR.contract_number, NULL) contract_number ,pdt.name product_name ,cust_party.party_name customer_name ,cust_accounts.account_number customer_account ,tcn.currency_code currency_code ,tcn.trx_number transaction_number ,tcn.date_transaction_occurred transaction_date ,tcn.canceled_date cancellation_date ,seclkup.meaning investor_agreement_type ,term.quote_number termination_quote_number ,tqlkup.meaning termination_quote_type ,DECODE( chr.scs_code, 'INVESTOR', CHR.contract_number, NULL) FROM OKL_TRX_CONTRACTS_ALL TCN,OKC_K_HEADERS_ALL_B CHR,OKL_K_HEADERS KHR,OKL_PRODUCTS PDT,OKC_K_PARTY_ROLES_B CUST_PARTY_ROLES,HZ_PARTIES CUST_PARTY,HZ_CUST_ACCOUNTS CUST_ACCOUNTS,OKL_TRX_QUOTES_ALL_B TERM,FND_LOOKUPS SECLKUP,FND_LOOKUPS TQLKUP WHERE tcn.khr_id = CHR.id AND CHR.id = khr.id AND tcn.pdt_id = pdt.id AND cust_party.party_type in ( 'PERSON','ORGANIZATION') AND '#' = cust_party_roles.object1_id2 AND cust_party_roles.jtot_object1_code = 'OKX_PARTY' AND cust_party.party_id = cust_party_roles.object1_id1 AND cust_party_roles.rle_code = 'LESSEE' AND cust_party_roles.chr_id = chr.id AND cust_party_roles.dnz_chr_id = chr.id AND cust_accounts.cust_account_id = chr.cust_acct_id AND seclkup.lookup_type (+) = 'OKL_SECURITIZATION_TYPE' AND seclkup.lookup_code (+) = DECODE( khr.securitization_type, 'SECURITIZATION','SECURITIZATION', 'SALE', 'SECURITIZATION', 'SYNDICATION', 'SYNDICATION', 'LOAN', 'SYNDICATION' ,NULL) AND term.id (+) = tcn.qte_id AND tqlkup.lookup_type (+) = 'OKL_QUOTE_TYPE' AND tqlkup.lookup_code (+) = term.qtp_code

View Text - HTML Formatted

SELECT TCN.ID ID
, DECODE( CHR.SCS_CODE
, 'LEASE'
, CHR.CONTRACT_NUMBER
, NULL) CONTRACT_NUMBER
, PDT.NAME PRODUCT_NAME
, CUST_PARTY.PARTY_NAME CUSTOMER_NAME
, CUST_ACCOUNTS.ACCOUNT_NUMBER CUSTOMER_ACCOUNT
, TCN.CURRENCY_CODE CURRENCY_CODE
, TCN.TRX_NUMBER TRANSACTION_NUMBER
, TCN.DATE_TRANSACTION_OCCURRED TRANSACTION_DATE
, TCN.CANCELED_DATE CANCELLATION_DATE
, SECLKUP.MEANING INVESTOR_AGREEMENT_TYPE
, TERM.QUOTE_NUMBER TERMINATION_QUOTE_NUMBER
, TQLKUP.MEANING TERMINATION_QUOTE_TYPE
, DECODE( CHR.SCS_CODE
, 'INVESTOR'
, CHR.CONTRACT_NUMBER
, NULL)
FROM OKL_TRX_CONTRACTS_ALL TCN
, OKC_K_HEADERS_ALL_B CHR
, OKL_K_HEADERS KHR
, OKL_PRODUCTS PDT
, OKC_K_PARTY_ROLES_B CUST_PARTY_ROLES
, HZ_PARTIES CUST_PARTY
, HZ_CUST_ACCOUNTS CUST_ACCOUNTS
, OKL_TRX_QUOTES_ALL_B TERM
, FND_LOOKUPS SECLKUP
, FND_LOOKUPS TQLKUP
WHERE TCN.KHR_ID = CHR.ID
AND CHR.ID = KHR.ID
AND TCN.PDT_ID = PDT.ID
AND CUST_PARTY.PARTY_TYPE IN ( 'PERSON'
, 'ORGANIZATION')
AND '#' = CUST_PARTY_ROLES.OBJECT1_ID2
AND CUST_PARTY_ROLES.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND CUST_PARTY.PARTY_ID = CUST_PARTY_ROLES.OBJECT1_ID1
AND CUST_PARTY_ROLES.RLE_CODE = 'LESSEE'
AND CUST_PARTY_ROLES.CHR_ID = CHR.ID
AND CUST_PARTY_ROLES.DNZ_CHR_ID = CHR.ID
AND CUST_ACCOUNTS.CUST_ACCOUNT_ID = CHR.CUST_ACCT_ID
AND SECLKUP.LOOKUP_TYPE (+) = 'OKL_SECURITIZATION_TYPE'
AND SECLKUP.LOOKUP_CODE (+) = DECODE( KHR.SECURITIZATION_TYPE
, 'SECURITIZATION'
, 'SECURITIZATION'
, 'SALE'
, 'SECURITIZATION'
, 'SYNDICATION'
, 'SYNDICATION'
, 'LOAN'
, 'SYNDICATION'
, NULL)
AND TERM.ID (+) = TCN.QTE_ID
AND TQLKUP.LOOKUP_TYPE (+) = 'OKL_QUOTE_TYPE'
AND TQLKUP.LOOKUP_CODE (+) = TERM.QTP_CODE