VIEW: APPS.OKL_CREDITLINES_UV

Source

View Text - Preformatted

SELECT chrb.id id, chrb.contract_number contract_number, chrt.description description, chrb.sts_code sts_code, status.meaning sts_meaning, chrb.scs_code scs_code, chrb.start_date start_date, chrb.end_date end_date, chrb.currency_code currency_code, curr.name currency_name, khr.currency_conversion_type currency_conversion_type, dct.user_conversion_type user_conversion_type, khr.currency_conversion_rate currency_conversion_rate, khr.currency_conversion_date currency_conversion_date, khr.revolving_credit_yn revolving_credit_yn, cpl.id party_roles_id, cpl.object1_id1 customer_id1, cpl.object1_id2 customer_id2, party.name customer_name, party.description customer_number, cpl.jtot_object1_code customer_jtot_object_code, chrb.cust_acct_id cust_acct_id, cust_acct.name cust_acct_name, cust_acct.description cust_acct_number, nvl(site.description, ' ') legal_address, rul.rgp_id chklst_tpl_rgp_id, rul.id chklst_tpl_rule_id, rul.rule_information1 creditline_ckl_id, (SELECT checklist_number FROM okl_checklists ckl WHERE TO_CHAR(ckl.id) = rul.rule_information1) creditline_ckl_number, rul.rule_information2 funding_ckl_id, (SELECT checklist_number FROM okl_checklists ckl WHERE TO_CHAR(ckl.id) = rul.rule_information2) funding_ckl_number, nvl(OKL_SEEDED_FUNCTIONS_PVT.creditline_total_limit(chrb.id),0) total_limit, nvl(OKL_SEEDED_FUNCTIONS_PVT.creditline_total_remaining(chrb.id),0) remaining FROM OKC_K_HEADERS_B CHRB,OKC_K_HEADERS_TL CHRT,OKL_K_HEADERS KHR,OKX_CUSTOMER_ACCOUNTS_V CUST_ACCT,OKC_RULES_B RUL,OKX_PARTIES_V PARTY,OKC_K_PARTY_ROLES_B CPL,OKX_CUST_SITE_USES_V SITE,OKC_STATUSES_V STATUS,FND_CURRENCIES_VL CURR,GL_DAILY_CONVERSION_TYPES_V DCT WHERE chrb.id = chrt.id AND chrt.language = USERENV('LANG') AND chrb.id = khr.id AND chrb.scs_code = 'CREDITLINE_CONTRACT' AND cust_acct.id1(+) = chrb.cust_acct_id AND cust_acct.id2(+) = '#' AND rul.rule_information_category(+) = 'LACCLT' AND rul.dnz_chr_id(+) = chrb.id AND party.id1 = cpl.object1_id1 AND party.id2 = cpl.object1_id2 AND cpl.rle_code = 'LESSEE' AND cpl.chr_id = chrb.id AND site.site_use_code(+) = 'LEGAL' AND site.cust_account_id(+) = cust_acct.id1 AND chrb.sts_code = status.code AND chrb.currency_code = curr.currency_code AND dct.conversion_type(+) = khr.currency_conversion_type

View Text - HTML Formatted

SELECT CHRB.ID ID
, CHRB.CONTRACT_NUMBER CONTRACT_NUMBER
, CHRT.DESCRIPTION DESCRIPTION
, CHRB.STS_CODE STS_CODE
, STATUS.MEANING STS_MEANING
, CHRB.SCS_CODE SCS_CODE
, CHRB.START_DATE START_DATE
, CHRB.END_DATE END_DATE
, CHRB.CURRENCY_CODE CURRENCY_CODE
, CURR.NAME CURRENCY_NAME
, KHR.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, DCT.USER_CONVERSION_TYPE USER_CONVERSION_TYPE
, KHR.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, KHR.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, KHR.REVOLVING_CREDIT_YN REVOLVING_CREDIT_YN
, CPL.ID PARTY_ROLES_ID
, CPL.OBJECT1_ID1 CUSTOMER_ID1
, CPL.OBJECT1_ID2 CUSTOMER_ID2
, PARTY.NAME CUSTOMER_NAME
, PARTY.DESCRIPTION CUSTOMER_NUMBER
, CPL.JTOT_OBJECT1_CODE CUSTOMER_JTOT_OBJECT_CODE
, CHRB.CUST_ACCT_ID CUST_ACCT_ID
, CUST_ACCT.NAME CUST_ACCT_NAME
, CUST_ACCT.DESCRIPTION CUST_ACCT_NUMBER
, NVL(SITE.DESCRIPTION
, ' ') LEGAL_ADDRESS
, RUL.RGP_ID CHKLST_TPL_RGP_ID
, RUL.ID CHKLST_TPL_RULE_ID
, RUL.RULE_INFORMATION1 CREDITLINE_CKL_ID
, (SELECT CHECKLIST_NUMBER
FROM OKL_CHECKLISTS CKL
WHERE TO_CHAR(CKL.ID) = RUL.RULE_INFORMATION1) CREDITLINE_CKL_NUMBER
, RUL.RULE_INFORMATION2 FUNDING_CKL_ID
, (SELECT CHECKLIST_NUMBER
FROM OKL_CHECKLISTS CKL
WHERE TO_CHAR(CKL.ID) = RUL.RULE_INFORMATION2) FUNDING_CKL_NUMBER
, NVL(OKL_SEEDED_FUNCTIONS_PVT.CREDITLINE_TOTAL_LIMIT(CHRB.ID)
, 0) TOTAL_LIMIT
, NVL(OKL_SEEDED_FUNCTIONS_PVT.CREDITLINE_TOTAL_REMAINING(CHRB.ID)
, 0) REMAINING
FROM OKC_K_HEADERS_B CHRB
, OKC_K_HEADERS_TL CHRT
, OKL_K_HEADERS KHR
, OKX_CUSTOMER_ACCOUNTS_V CUST_ACCT
, OKC_RULES_B RUL
, OKX_PARTIES_V PARTY
, OKC_K_PARTY_ROLES_B CPL
, OKX_CUST_SITE_USES_V SITE
, OKC_STATUSES_V STATUS
, FND_CURRENCIES_VL CURR
, GL_DAILY_CONVERSION_TYPES_V DCT
WHERE CHRB.ID = CHRT.ID
AND CHRT.LANGUAGE = USERENV('LANG')
AND CHRB.ID = KHR.ID
AND CHRB.SCS_CODE = 'CREDITLINE_CONTRACT'
AND CUST_ACCT.ID1(+) = CHRB.CUST_ACCT_ID
AND CUST_ACCT.ID2(+) = '#'
AND RUL.RULE_INFORMATION_CATEGORY(+) = 'LACCLT'
AND RUL.DNZ_CHR_ID(+) = CHRB.ID
AND PARTY.ID1 = CPL.OBJECT1_ID1
AND PARTY.ID2 = CPL.OBJECT1_ID2
AND CPL.RLE_CODE = 'LESSEE'
AND CPL.CHR_ID = CHRB.ID
AND SITE.SITE_USE_CODE(+) = 'LEGAL'
AND SITE.CUST_ACCOUNT_ID(+) = CUST_ACCT.ID1
AND CHRB.STS_CODE = STATUS.CODE
AND CHRB.CURRENCY_CODE = CURR.CURRENCY_CODE
AND DCT.CONVERSION_TYPE(+) = KHR.CURRENCY_CONVERSION_TYPE