VIEW: APPS.OKL_CURE_INVOICE_UV

Source

View Text - Preformatted

SELECT CAM.CURE_AMOUNT_ID CURE_AMOUNT_ID, CRT.CURE_REPORT_ID CURE_REPORT_ID, CRT.REPORT_NUMBER REQUEST_NUMBER, CRT.REPORT_DATE REQUEST_DATE, CRT.CURRENCY_CODE CURRENCY_CODE, CRT.VENDOR_ID VENDOR_ID, CRT.VENDOR_SITE_ID VENDOR_SITE_ID, CRT.VENDOR_CONTACT_ID VENDOR_CONTACT_ID, CRT.APPROVAL_STATUS APPROVAL_STATUS, CHR.CONTRACT_NUMBER CONTRACT_NUMBER, PA.CONTRACT_NUMBER PROGRAM_AGREEMENT, nvl(CAM.CURE_AMOUNT,0) CURE_AMOUNT, nvl( CRV.CURES_RECEIVED,0) CURE_AMOUNT_RECEIVED, nvl(cam.negotiated_amount,0) negotiated_amount, ar.date_invoiced, DECODE (rul.rule_information8,'Y','Yes','No') SHORT_FUND_ALLOWED, (select sum(nvl(cam1.negotiated_amount,0)) from okl_cure_amounts_all cam1 where cam1.crt_id =crt.cure_report_id) total_negotiated_amount, FND.MEANING APPROVAL_STATUS_MEANING, FRQ.MEANING REPORT_TYPE_MEANING, CRT.REPORT_TYPE REPORT_TYPE, CAM.REPURCHASE_AMOUNT REPURCHASE_AMOUNT, CAM.PROCESS PROCESS, QTE.QUOTE_NUMBER REPURCHASE_QUOTE_NUMBER, QTE.ID QTE_ID, CAM.CHR_ID CHR_ID FROM OKC_K_HEADERS_B PA,OKL_CURE_REPORTS_ALL CRT,OKL_TRX_AR_INVOICES_B AR,OKC_K_HEADERS_B PROG,OKL_CURE_RECEIVED_UV CRV,OKC_RULES_B RUL,OKL_CURE_AMOUNTS_ALL CAM,OKL_K_HEADERS KHR,OKC_K_HEADERS_B CHR,FND_LOOKUPS FND,FND_LOOKUPS FRQ,OKL_TRX_QUOTES_ALL_B QTE WHERE CRT.CURE_REPORT_ID = CAM.CRT_ID AND CHR.ID = KHR.ID AND CHR.ID = CAM.CHR_ID AND KHR.KHR_ID = PA.ID AND CHR.ID = CRV.CHR_ID AND KHR.KHR_ID = PROG.ID and cam.cure_amount_id = ar.cpy_id (+) and crt.approval_status IN ('SENT_TO_VENDOR','APPROVED','ACCEPTANCE_COMPLETED','ACCEPTANCE_IN_PROGRESS') AND prog.id = rul.dnz_chr_id and rul.rule_information_category = 'COCURE' AND FND.LOOKUP_TYPE = 'OKL_CURE_REQUEST_APPROVE_STATU' AND CRT.APPROVAL_STATUS = FND.LOOKUP_CODE AND FRQ.LOOKUP_TYPE = 'OKL_CURE_REQUEST_TYPE' AND CRT.REPORT_TYPE = FRQ.LOOKUP_CODE AND CAM.QTE_ID = QTE.ID(+)

View Text - HTML Formatted

SELECT CAM.CURE_AMOUNT_ID CURE_AMOUNT_ID
, CRT.CURE_REPORT_ID CURE_REPORT_ID
, CRT.REPORT_NUMBER REQUEST_NUMBER
, CRT.REPORT_DATE REQUEST_DATE
, CRT.CURRENCY_CODE CURRENCY_CODE
, CRT.VENDOR_ID VENDOR_ID
, CRT.VENDOR_SITE_ID VENDOR_SITE_ID
, CRT.VENDOR_CONTACT_ID VENDOR_CONTACT_ID
, CRT.APPROVAL_STATUS APPROVAL_STATUS
, CHR.CONTRACT_NUMBER CONTRACT_NUMBER
, PA.CONTRACT_NUMBER PROGRAM_AGREEMENT
, NVL(CAM.CURE_AMOUNT
, 0) CURE_AMOUNT
, NVL( CRV.CURES_RECEIVED
, 0) CURE_AMOUNT_RECEIVED
, NVL(CAM.NEGOTIATED_AMOUNT
, 0) NEGOTIATED_AMOUNT
, AR.DATE_INVOICED
, DECODE (RUL.RULE_INFORMATION8
, 'Y'
, 'YES'
, 'NO') SHORT_FUND_ALLOWED
, (SELECT SUM(NVL(CAM1.NEGOTIATED_AMOUNT
, 0))
FROM OKL_CURE_AMOUNTS_ALL CAM1
WHERE CAM1.CRT_ID =CRT.CURE_REPORT_ID) TOTAL_NEGOTIATED_AMOUNT
, FND.MEANING APPROVAL_STATUS_MEANING
, FRQ.MEANING REPORT_TYPE_MEANING
, CRT.REPORT_TYPE REPORT_TYPE
, CAM.REPURCHASE_AMOUNT REPURCHASE_AMOUNT
, CAM.PROCESS PROCESS
, QTE.QUOTE_NUMBER REPURCHASE_QUOTE_NUMBER
, QTE.ID QTE_ID
, CAM.CHR_ID CHR_ID
FROM OKC_K_HEADERS_B PA
, OKL_CURE_REPORTS_ALL CRT
, OKL_TRX_AR_INVOICES_B AR
, OKC_K_HEADERS_B PROG
, OKL_CURE_RECEIVED_UV CRV
, OKC_RULES_B RUL
, OKL_CURE_AMOUNTS_ALL CAM
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_B CHR
, FND_LOOKUPS FND
, FND_LOOKUPS FRQ
, OKL_TRX_QUOTES_ALL_B QTE
WHERE CRT.CURE_REPORT_ID = CAM.CRT_ID
AND CHR.ID = KHR.ID
AND CHR.ID = CAM.CHR_ID
AND KHR.KHR_ID = PA.ID
AND CHR.ID = CRV.CHR_ID
AND KHR.KHR_ID = PROG.ID
AND CAM.CURE_AMOUNT_ID = AR.CPY_ID (+)
AND CRT.APPROVAL_STATUS IN ('SENT_TO_VENDOR'
, 'APPROVED'
, 'ACCEPTANCE_COMPLETED'
, 'ACCEPTANCE_IN_PROGRESS')
AND PROG.ID = RUL.DNZ_CHR_ID
AND RUL.RULE_INFORMATION_CATEGORY = 'COCURE'
AND FND.LOOKUP_TYPE = 'OKL_CURE_REQUEST_APPROVE_STATU'
AND CRT.APPROVAL_STATUS = FND.LOOKUP_CODE
AND FRQ.LOOKUP_TYPE = 'OKL_CURE_REQUEST_TYPE'
AND CRT.REPORT_TYPE = FRQ.LOOKUP_CODE
AND CAM.QTE_ID = QTE.ID(+)