VIEW: APPS.OKL_CS_GUARANTOR_AMOUNT_UV

Source

View Text - Preformatted

SELECT chr.id contract_id, chr.contract_number contract_number, cpl.rle_code role_name, nvl(rul.rule_information2,0) AMOUNT_GUARANTEED, hz.party_type party_type, hz.party_name company, hz.party_number customer_number, hz.email_address email, DECODE(hcp.PHONE_COUNTRY_CODE,null,null,hcp.PHONE_COUNTRY_CODE||'-')||DECODE(hcp.phone_area_code,null,null,hcp.phone_area_code||'-')||hcp.phone_number raw_phone_number, hz.address1 address1, hz.address2 address2, hz.address3 address3, hz.address4 address4, hz.city city, hz.county county, hz.province province, hz.state state, hz.postal_code postal_code, hz.country country FROM OKC_RULES_B RUL,OKC_RULE_GROUPS_B RGP,HZ_PARTIES HZ,OKC_K_HEADERS_B CHR,OKC_K_PARTY_ROLES_B CPL,OKC_RG_PARTY_ROLES RGR,HZ_CONTACT_POINTS HCP WHERE rul.rule_information_category = 'LAGRNT' and rul.rgp_id = rgp.id and rgp.rgd_code = 'LAGRDT' and rgp.chr_id = chr.id and rgp.id = rgr.rgp_id and cpl.chr_id = chr.id and cpl.rle_code = 'GUARANTOR' and cpl.id = rgr.cpl_id and rgr.rrd_id = (select id from okc_rg_role_defs where srd_id in (select id from okc_subclass_rg_defs where scs_code = 'LEASE' and rgd_code = 'LAGRDT') and sre_id in (select id from okc_subclass_roles where scs_code = 'LEASE' and rle_code = 'GUARANTOR')) and hz.party_id = to_number(cpl.object1_id1) and cpl.object1_id2 = '#' and cpl.jtot_object1_code = 'OKX_PARTY' and hz.party_id = HCP.owner_table_id(+) AND HCP.owner_table_name(+) = 'HZ_PARTIES' AND HCP.status(+) = 'A' AND hcp.PRIMARY_FLAG(+) = 'Y' AND hcp.CONTACT_POINT_TYPE(+) = 'PHONE'

View Text - HTML Formatted

SELECT CHR.ID CONTRACT_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CPL.RLE_CODE ROLE_NAME
, NVL(RUL.RULE_INFORMATION2
, 0) AMOUNT_GUARANTEED
, HZ.PARTY_TYPE PARTY_TYPE
, HZ.PARTY_NAME COMPANY
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.EMAIL_ADDRESS EMAIL
, DECODE(HCP.PHONE_COUNTRY_CODE
, NULL
, NULL
, HCP.PHONE_COUNTRY_CODE||'-')||DECODE(HCP.PHONE_AREA_CODE
, NULL
, NULL
, HCP.PHONE_AREA_CODE||'-')||HCP.PHONE_NUMBER RAW_PHONE_NUMBER
, HZ.ADDRESS1 ADDRESS1
, HZ.ADDRESS2 ADDRESS2
, HZ.ADDRESS3 ADDRESS3
, HZ.ADDRESS4 ADDRESS4
, HZ.CITY CITY
, HZ.COUNTY COUNTY
, HZ.PROVINCE PROVINCE
, HZ.STATE STATE
, HZ.POSTAL_CODE POSTAL_CODE
, HZ.COUNTRY COUNTRY
FROM OKC_RULES_B RUL
, OKC_RULE_GROUPS_B RGP
, HZ_PARTIES HZ
, OKC_K_HEADERS_B CHR
, OKC_K_PARTY_ROLES_B CPL
, OKC_RG_PARTY_ROLES RGR
, HZ_CONTACT_POINTS HCP
WHERE RUL.RULE_INFORMATION_CATEGORY = 'LAGRNT'
AND RUL.RGP_ID = RGP.ID
AND RGP.RGD_CODE = 'LAGRDT'
AND RGP.CHR_ID = CHR.ID
AND RGP.ID = RGR.RGP_ID
AND CPL.CHR_ID = CHR.ID
AND CPL.RLE_CODE = 'GUARANTOR'
AND CPL.ID = RGR.CPL_ID
AND RGR.RRD_ID = (SELECT ID
FROM OKC_RG_ROLE_DEFS
WHERE SRD_ID IN (SELECT ID
FROM OKC_SUBCLASS_RG_DEFS
WHERE SCS_CODE = 'LEASE'
AND RGD_CODE = 'LAGRDT')
AND SRE_ID IN (SELECT ID
FROM OKC_SUBCLASS_ROLES
WHERE SCS_CODE = 'LEASE'
AND RLE_CODE = 'GUARANTOR'))
AND HZ.PARTY_ID = TO_NUMBER(CPL.OBJECT1_ID1)
AND CPL.OBJECT1_ID2 = '#'
AND CPL.JTOT_OBJECT1_CODE = 'OKX_PARTY'
AND HZ.PARTY_ID = HCP.OWNER_TABLE_ID(+)
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.STATUS(+) = 'A'
AND HCP.PRIMARY_FLAG(+) = 'Y'
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'