View: OKS_EXPIRED_CONTRACTS_V

Product:	OKS - Service Contracts
Description:	View for Expired COntracts
Implementation/DBA Data:	APPS.OKS_EXPIRED_CONTRACTS_V

View Text

SELECT PARTY.PARTY_NAME CUSTOMER_NAME
, PARTY.PARTY_ID CUSTOMER_ID
, KHDR.ID CONTRACT_ID
, KHDR.CONTRACT_NUMBER CONTRACT_NUMBER
, KHDR.CONTRACT_NUMBER_MODIFIER CONTRACT_NUMBER_MODIFIER
, KSTAT.STE_CODE CONTRACT_STATUS_TYPE
, KHDR.STS_CODE CONTRACT_STATUS_CODE
, KLINES.PRICE_NEGOTIATED LINE_AMOUNT
, TO_CHAR(CP.CUSTOMER_PRODUCT_ID) CUSTOMER_PRODUCT_ID
, CP.CURRENT_SERIAL_NUMBER SERIAL_NUMBER
, KLINES.START_DATE PRODUCT_START_DATE
, KLINES.END_DATE PRODUCT_END_DATE
, KHDR.AUTHORING_ORG_ID ORG_ID
, KHDR.INV_ORGANIZATION_ID ORGANIZATION_ID
, KCTS.OBJECT1_ID1 SALESREP_ID
FROM OKC_K_HEADERS_B KHDR
, OKC_STATUSES_B KSTAT
, OKC_K_PARTY_ROLES_B KPARTY
, HZ_PARTIES PARTY
, OKC_K_PARTY_ROLES_B VPARTY
, OKC_CONTACTS KCTS
, OKC_K_LINES_B SVCLINE
, OKC_K_LINES_B KLINES
, OKC_K_ITEMS KITM
, CS_CUSTOMER_PRODUCTS_ALL CP
WHERE KSTAT.CODE = KHDR.STS_CODE
AND KHDR.ID = KPARTY.DNZ_CHR_ID
AND KPARTY.CLE_ID IS NULL
AND KHDR.ID = KPARTY.CHR_ID
AND KPARTY.RLE_CODE = 'CUSTOMER'
AND KPARTY.OBJECT1_ID1 = PARTY.PARTY_ID
AND KHDR.ID = VPARTY.DNZ_CHR_ID
AND VPARTY.CLE_ID IS NULL
AND KHDR.ID = VPARTY.CHR_ID
AND VPARTY.RLE_CODE = 'VENDOR'
AND KHDR.ID = SVCLINE.DNZ_CHR_ID
AND SVCLINE.STS_CODE IN ( 'EXPIRED'
, 'TERMINATED')
AND KLINES.CLE_ID = SVCLINE.ID
AND KITM.CLE_ID = KLINES.ID
AND KLINES.LSE_ID = 25
AND KITM.OBJECT1_ID1 = CP.CUSTOMER_PRODUCT_ID
AND KITM.OBJECT1_ID2 = '#'
AND KITM.JTOT_OBJECT1_CODE = 'OKX_CUSTPROD'
AND VPARTY.ID = KCTS.CPL_ID
AND KCTS.CRO_CODE IN ( 'SUP_SALES'
, 'SALESPERSON')
AND KCTS.JTOT_OBJECT1_CODE = 'OKX_SALEPERS'

Columns