View: OKL_CURE_REQUEST_UV

Product:	OKL - Lease and Finance Management
Description:	View for Cure requests
Implementation/DBA Data:	APPS.OKL_CURE_REQUEST_UV

View Text

SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, HZP.PARTY_NAME CUSTOMER_NAME
, PA.CONTRACT_NUMBER PROGRAM_AGREEMENT
, NVL(CAM.CURE_AMOUNT
, 0) CURE_AMOUNT
, CAM.REPURCHASE_AMOUNT REPURCHASE_AMOUNT
, CAM.EFFECTIVE_DATE EFFECTIVE_DATE
, NVL(CIP.CURES_IN_POSSESSION
, 0) CURES_IN_POSSESSION
, NVL(CAR.CURES_RECEIVED
, 0) CURE_AMOUNT_RECEIVED
, NVL(CAM.NEGOTIATED_AMOUNT
, 0) NEGOTIATED_AMOUNT
, NVL(CAM.RECEIVED_AMOUNT
, 0) RECEIVED_AMOUNT
, CAM.CURE_AMOUNT_ID CURE_AMOUNT_ID
, CR.CURE_REPORT_ID CURE_REPORT_ID
, CR.VENDOR_ID VENDOR_ID
, CR.VENDOR_SITE_ID VENDOR_SITE_ID
, CR.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, CR.APPROVAL_STATUS APPROVAL_STATUS
, CR.REPORT_NUMBER REQUEST_NUMBER
, CR.CURRENCY_CODE CURRENCY_CODE
, CAM.CRT_ID CRT_ID
, CAM.SELECTED_ON_REQUEST
, TO_CHAR(NULL) REQUEST_ALL_YN
, DECODE(CAM.CRT_ID
, NULL
, 'NEW'
, 'IN PROGRESS') REQUEST_STATUS
, KHR.ID CHR_ID
, QTE.QUOTE_NUMBER REPURCHASE_QUOTE_NUMBER
, QTE.ID QTE_ID
FROM OKL_CURE_AMOUNTS CAM
, HZ_PARTIES HZP
, OKL_NO_OF_CURES_UV CIP
, OKL_TRX_QUOTES_V QTE
, OKC_K_HEADERS_ALL_B PA
, OKC_K_HEADERS_ALL_B CHR
, OKL_K_HEADERS KHR
, OKL_CURE_REPORTS_ALL CR
, OKL_CURE_RECEIVED_UV CAR
, OKC_K_PARTY_ROLES_B CPR
WHERE CR.CURE_REPORT_ID = CAM.CRT_ID
AND CHR.ID = CIP.CHR_ID(+)
AND CHR.ID = CAR.CHR_ID(+)
AND CHR.ID = KHR.ID
AND CHR.ID = CAM.CHR_ID
AND CHR.ID = CPR.DNZ_CHR_ID
AND CHR.ID = CPR.CHR_ID
AND CPR.OBJECT1_ID1 = TO_CHAR(HZP.PARTY_ID)
AND CPR.OBJECT1_ID2 = '#'
AND CPR.RLE_CODE = 'LESSEE'
AND KHR.KHR_ID = PA.ID
AND CAM.QTE_ID=QTE.ID(+) UNION SELECT CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, HZP.PARTY_NAME CUSTOMER_NAME
, PA.CONTRACT_NUMBER PROGRAM_AGREEMENT
, NVL(CAM.CURE_AMOUNT
, 0) CURE_AMOUNT
, CAM.REPURCHASE_AMOUNT REPURCHASE_AMOUNT
, CAM.EFFECTIVE_DATE EFFECTIVE_DATE
, NVL(CIP.CURES_IN_POSSESSION
, 0) CURES_IN_POSSESSION
, NVL(CAR.CURES_RECEIVED
, 0) CURE_AMOUNT_RECEIVED
, NVL(CAM.NEGOTIATED_AMOUNT
, 0) NEGOTIATED_AMOUNT
, NVL(CAM.RECEIVED_AMOUNT
, 0) RECEIVED_AMOUNT
, CAM.CURE_AMOUNT_ID CURE_AMOUNT_ID
, CR.CURE_REPORT_ID CURE_REPORT_ID
, CR.VENDOR_ID VENDOR_ID
, CR.VENDOR_SITE_ID VENDOR_SITE_ID
, CR.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, CR.APPROVAL_STATUS APPROVAL_STATUS
, CR.REPORT_NUMBER REQUEST_NUMBER
, CR.CURRENCY_CODE CURRENCY_CODE
, CAM.CRT_ID CRT_ID
, CAM.SELECTED_ON_REQUEST
, TO_CHAR(NULL) REQUEST_ALL_YN
, DECODE(CAM.CRT_ID
, NULL
, 'NEW'
, 'IN PROGRESS') REQUEST_STATUS
, KHR.ID CHR_ID
, QTE.QUOTE_NUMBER REPURCHASE_QUOTE_NUMBER
, QTE.ID QTE_ID
FROM OKC_K_HEADERS_ALL_B CHR
, OKL_K_HEADERS KHR
, OKC_K_PARTY_ROLES_B CPR
, HZ_PARTIES HZP
, OKC_K_HEADERS_ALL_B PA
, OKL_CURE_AMOUNTS CAM
, OKL_NO_OF_CURES_UV CIP
, OKL_CURE_RECEIVED_UV CAR
, OKL_CURE_REPORTS_ALL CR
, OKL_TRX_QUOTES_V QTE
WHERE CAM.CRT_ID IS NULL
AND CHR.ID = CIP.CHR_ID(+)
AND CHR.ID = CAR.CHR_ID(+)
AND CHR.ID = KHR.ID
AND CHR.ID = CAM.CHR_ID
AND CHR.ID = CPR.DNZ_CHR_ID
AND CHR.ID = CPR.CHR_ID
AND CPR.OBJECT1_ID1 = TO_CHAR(HZP.PARTY_ID)
AND CPR.OBJECT1_ID2 = '#'
AND CPR.RLE_CODE = 'LESSEE'
AND KHR.KHR_ID = PA.ID
AND CAM.SHOW_ON_REQUEST = 'Y'
AND CAM.QTE_ID=QTE.ID(+)
AND EXISTS ( SELECT 1
FROM OKL_CURE_AMOUNTS_ALL CA
, OKL_CURE_REPORTS_ALL CR1
WHERE CR1.CURE_REPORT_ID = CA.CRT_ID(+)
AND CR1.CURE_REPORT_ID = CR.CURE_REPORT_ID
AND CR1.APPROVAL_STATUS = 'IN_PROGRESS' )

Columns