View: OKL_LA_SERV_INTGR_UV

Product:	OKL - Lease and Finance Management
Description:	OKL Service Integration Page View
Implementation/DBA Data:	APPS.OKL_LA_SERV_INTGR_UV

View Text

SELECT CLE.ID CLE_ID
, CLE.START_DATE START_DATE
, CLE.END_DATE END_DATE
, OKX_SERVICE_B.CONCATENATED_SEGMENTS ITEM_NAME
, OKX_VENDOR.VENDOR_NAME SUPPLIER_NAME
, CLE.DNZ_CHR_ID DNZ_CHR_ID
, CHRB.CONTRACT_NUMBER SERVICE_CONTRACT_NUMBER
, CHR_CRJB.ID K_REL_OBJS_ID
, CHR_CRJB.CLE_ID K_REL_OBJS_CLE_ID
, CHR_CRJB.CHR_ID K_REL_OBJS_CHR_ID
, CHR_CRJB.RTY_CODE K_REL_OBJS_RTY_CODE
, CHR_CRJB.OBJECT1_ID1 K_REL_OBJS_OBJECT1_ID1
, CHR_CRJB.OBJECT1_ID2 K_REL_OBJS_OBJECT1_ID2
, CHR_CRJB.JTOT_OBJECT1_CODE K_REL_OBJS_JTOT_OBJECT1_CODE
, CLE_CRJB.ID L_REL_OBJS_ID
, CLE_CRJB.CLE_ID L_REL_OBJS_CLE_ID
, CLE_CRJB.CHR_ID L_REL_OBJS_CHR_ID
, CLE_CRJB.RTY_CODE L_REL_OBJS_RTY_CODE
, CLE_CRJB.OBJECT1_ID1 L_REL_OBJS_OBJECT1_ID1
, CLE_CRJB.OBJECT1_ID2 L_REL_OBJS_OBJECT1_ID2
, CLE_CRJB.JTOT_OBJECT1_CODE L_REL_OBJS_JTOT_OBJECT1_CODE
, KLE.AMOUNT AMOUNT
, 'AUTHORING_ORG_ID' AUTHORING_ORG_ID
, 'INV_ORGANIZATION_ID' INV_ORGANIZATION_ID
, 'CURRENCY_CODE' CURRENCY_CODE
, 'CUST_OBJECT1_ID1' CUST_OBJECT1_ID1
, 'CUST_OBJECT1_ID2' CUST_OBJECT1_ID2
, 'BTO_OBJECT1_ID1' BTO_OBJECT1_ID1
, 'BTO_OBJECT1_ID2' BTO_OBJECT1_ID2
FROM OKC_K_REL_OBJS CHR_CRJB
, OKC_K_REL_OBJS CLE_CRJB
, OKC_K_HEADERS_B CHRB
, OKC_K_HEADERS_TL CHRT
, OKC_K_LINES_B CLE
, OKL_K_LINES KLE
, OKC_LINE_STYLES_B LSE
, OKC_K_ITEMS CIT
, OKC_K_PARTY_ROLES_B CPL
, MTL_SYSTEM_ITEMS_B_KFV OKX_SERVICE_B
, MTL_SYSTEM_ITEMS_TL OKX_SERVICE_T
, PO_VENDORS OKX_VENDOR
WHERE CHR_CRJB.CHR_ID = CLE_CRJB.CHR_ID
AND CHRB.ID = CHRT.ID
AND CHRT.LANGUAGE = USERENV('LANG')
AND CHR_CRJB.OBJECT1_ID1 = TO_CHAR(CHRT.ID)
AND CHR_CRJB.OBJECT1_ID1 = TO_CHAR(CHRB.ID)
AND CHR_CRJB.OBJECT1_ID2 = '#'
AND CHR_CRJB.CLE_ID IS NULL
AND CLE_CRJB.OBJECT1_ID1 = TO_CHAR(CLE.ID)
AND CLE_CRJB.OBJECT1_ID2 = '#'
AND LSE.ID = CLE.LSE_ID
AND LSE.LTY_CODE = 'SERVICE'
AND CIT.CLE_ID = CLE.ID
AND TO_CHAR(OKX_SERVICE_B.INVENTORY_ITEM_ID) = CIT.OBJECT1_ID1
AND TO_CHAR(OKX_SERVICE_B.ORGANIZATION_ID) = CIT.OBJECT1_ID2
AND CIT.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND OKX_SERVICE_B.INVENTORY_ITEM_ID = OKX_SERVICE_T.INVENTORY_ITEM_ID
AND OKX_SERVICE_B.ORGANIZATION_ID = OKX_SERVICE_T.ORGANIZATION_ID
AND OKX_SERVICE_B.VENDOR_WARRANTY_FLAG='N'
AND OKX_SERVICE_B.SERVICE_ITEM_FLAG='Y'
AND OKX_SERVICE_B.ORGANIZATION_ID = SYS_CONTEXT('OKC_CONTEXT'
, 'ORGANIZATION_ID')
AND OKX_SERVICE_T.LANGUAGE = USERENV('LANG')
AND KLE.ID = CLE_CRJB.CLE_ID
AND CPL.CLE_ID = CLE_CRJB.CLE_ID
AND CPL.DNZ_CHR_ID = CLE_CRJB.CHR_ID
AND CPL.DNZ_CHR_ID = CHR_CRJB.CHR_ID
AND CPL.RLE_CODE = 'OKL_VENDOR'
AND CPL.OBJECT1_ID1 = TO_CHAR(OKX_VENDOR.VENDOR_ID)
AND CPL.OBJECT1_ID2 = '#'

Columns