VIEW: APPS.OKL_CURE_RFND_DTLS_UV

Source

View Text - Preformatted

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 )

View Text - HTML Formatted

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 )