VIEW: APPS.OKL_LA_SERV_INTGR_UV

Source

View Text - Preformatted

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 , OKX_VENDOR.segment1 SUPPLIER_NUMBER , OKX_VENDOR.vendor_id SUPPLIER_ID 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 = '#'

View Text - HTML Formatted

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
, OKX_VENDOR.SEGMENT1 SUPPLIER_NUMBER
, OKX_VENDOR.VENDOR_ID SUPPLIER_ID
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 = '#'