VIEW: APPS.OKS_EXPIRED_CONTRACTS_V

Source

View Text - Preformatted

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.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 khdr.scs_code IN ('SERVICE', 'WARRANTY') 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.ID = OKS_EXTWAR_UTIL_PVT.active_salesrep(khdr.ID,vparty.ID, khdr.authoring_org_id) and kcts.jtot_object1_code = 'OKX_SALEPERS'

View Text - HTML Formatted

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.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 KHDR.SCS_CODE IN ('SERVICE'
, 'WARRANTY')
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.ID = OKS_EXTWAR_UTIL_PVT.ACTIVE_SALESREP(KHDR.ID
, VPARTY.ID
, KHDR.AUTHORING_ORG_ID)
AND KCTS.JTOT_OBJECT1_CODE = 'OKX_SALEPERS'