View: OKL_CURE_RFND_DTLS_UV

Product:	OKL - Lease and Finance Management
Description:	View by Vendor refunds
Implementation/DBA Data:	APPS.OKL_CURE_RFND_DTLS_UV

View Text

SELECT RS.CURE_REFUND_STAGE_ID CURE_REFUND_STAGE_ID
, RS.RECEIVED_AMOUNT RECEIVED_AMOUNT
, RS.CHR_ID CHR_ID
, RS.VENDOR_ID VENDOR_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CHR.CURRENCY_CODE CURRENCY_CODE
, RL.OFFSET_AMOUNT OFFSET_AMOUNT
, OFSET_CHR.CONTRACT_NUMBER OFFSET_CONTRACT_NUMBER
, RL.OFFSET_CONTRACT OFFSET_CONTRACT_ID
, RL.CURE_REFUND_ID CURE_REFUND_LINE_ID
, RL.VENDOR_SITE_ID VENDOR_SITE_ID
, RL.OBJECT_VERSION_NUMBER RL_OBJECT_VERSION_NUMBER
, RS.OBJECT_VERSION_NUMBER RS_OBJECT_VERSION_NUMBER
, RH.OBJECT_VERSION_NUMBER RH_OBJECT_VERSION_NUMBER
, RH.REFUND_HEADER_NUMBER RH_REFUND_HEADER_NUMBER
, RH.CURE_REFUND_HEADER_ID CURE_REFUND_HEADER_ID
, RH.REFUND_STATUS REFUND_STATUS
, FND.MEANING STATUS
, TO_CHAR(NULL) ROW_FLAG
, PV.VENDOR_NAME VENDOR_NAME
, (PVS.ADDRESS_LINE1|| DECODE(PVS.ADDRESS_LINE2
, NULL
, PVS.ADDRESS_LINE2
, '
, '||PVS.ADDRESS_LINE2) ||DECODE(PVS.ADDRESS_LINE3
, NULL
, PVS.ADDRESS_LINE3
, '
, ' ||PVS.ADDRESS_LINE3) || DECODE(PVS.CITY
, NULL
, PVS.CITY
, '
, ' ||PVS.CITY) || DECODE(PVS.STATE
, NULL
, PVS.STATE
, '
, '||PVS.STATE) || DECODE(PVS.ZIP
, NULL
, PVS.ZIP
, '
, '||PVS.ZIP) ) VENDOR_ADDRESS
, RL.DISBURSEMENT_AMOUNT DISBURSEMENT_AMOUNT
FROM OKC_K_HEADERS_ALL_B OFSET_CHR
, OKC_K_HEADERS_B CHR
, OKL_CURE_REFUND_STAGE RS
, OKL_CURE_REFUND_HEADERS_B RH
, OKL_CURE_REFUNDS_ALL RL
, PO_VENDORS PV
, FND_LOOKUPS FND
, PO_VENDOR_SITES_ALL PVS
WHERE RS.CURE_REFUND_STAGE_ID = RL.CURE_REFUND_STAGE_ID
AND CHR.ID = RS.CHR_ID
AND OFSET_CHR.ID(+) = RL.OFFSET_CONTRACT
AND RH.CURE_REFUND_HEADER_ID = RL.CURE_REFUND_HEADER_ID
AND FND.LOOKUP_TYPE = 'OKL_CURE_REFUND_STATUS'
AND FND.LOOKUP_CODE = RS.STATUS
AND RS.VENDOR_ID = PV.VENDOR_ID
AND RH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID UNION SELECT RS.CURE_REFUND_STAGE_ID CURE_REFUND_STAGE_ID
, RS.RECEIVED_AMOUNT RECEIVED_AMOUNT
, RS.CHR_ID CHR_ID
, RS.VENDOR_ID VENDOR_ID
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, CHR.CURRENCY_CODE CURRENCY_CODE
, RL.OFFSET_AMOUNT OFFSET_AMOUNT
, OFSET_CHR.CONTRACT_NUMBER OFFSET_CONTRACT_NUMBER
, RL.OFFSET_CONTRACT OFFSET_CONTRACT_ID
, RL.CURE_REFUND_ID CURE_REFUND_LINE_ID
, RL.VENDOR_SITE_ID VENDOR_SITE_ID
, RL.OBJECT_VERSION_NUMBER RL_OBJECT_VERSION_NUMBER
, RS.OBJECT_VERSION_NUMBER RS_OBJECT_VERSION_NUMBER
, RH.OBJECT_VERSION_NUMBER RH_OBJECT_VERSION_NUMBER
, RH.REFUND_HEADER_NUMBER RH_REFUND_HEADER_NUMBER
, RH.CURE_REFUND_HEADER_ID CURE_REFUND_HEADER_ID
, RH.REFUND_STATUS REFUND_STATUS
, FND.MEANING STATUS
, TO_CHAR(NULL) ROW_FLAG
, PV.VENDOR_NAME VENDOR_NAME
, (PVS.ADDRESS_LINE1|| DECODE(PVS.ADDRESS_LINE2
, NULL
, PVS.ADDRESS_LINE2
, '
, ' ||PVS.ADDRESS_LINE2) ||DECODE(PVS.ADDRESS_LINE3
, NULL
, PVS.ADDRESS_LINE3
, '
, ' || PVS.ADDRESS_LINE3) || DECODE(PVS.CITY
, NULL
, PVS.CITY
, '
, '||PVS.CITY) || DECODE(PVS.STATE
, NULL
, PVS.STATE
, '
, '||PVS.STATE) || DECODE(PVS.ZIP
, NULL
, PVS.ZIP
, '
, '||PVS.ZIP) ) VENDOR_ADDRESS
, RL.DISBURSEMENT_AMOUNT DISBURSEMENT_AMOUNT
FROM OKL_CURE_REFUND_STAGE RS
, OKL_CURE_REFUNDS_ALL RL
, OKC_K_HEADERS_B CHR
, OKC_K_HEADERS_ALL_B OFSET_CHR
, OKL_CURE_REFUND_HEADERS_B RH
, FND_LOOKUPS FND
, PO_VENDORS PV
, PO_VENDOR_SITES PVS
WHERE RS.CURE_REFUND_STAGE_ID = RL.CURE_REFUND_STAGE_ID(+)
AND RS.STATUS = 'ENTERED'
AND CHR.ID = RS.CHR_ID
AND OFSET_CHR.ID(+) = RL.OFFSET_CONTRACT
AND FND.LOOKUP_TYPE = 'OKL_CURE_REFUND_STATUS'
AND FND.LOOKUP_CODE = RS.STATUS
AND RS.VENDOR_ID = PV.VENDOR_ID
AND RH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND NOT EXISTS (SELECT 1
FROM OKL_CURE_REFUNDS RL
WHERE RL.CURE_REFUND_STAGE_ID = RS.CURE_REFUND_STAGE_ID
AND RL.CURE_REFUND_HEADER_ID IS NOT NULL )

Columns