VIEW: APPS.OKL_FUND_REQ_HEADERS_UV

Source

View Text - Preformatted

SELECT h.id fund_id, h.vendor_invoice_number fund_number, h.currency_code, lct.name currency_name, h.payment_method_code, look1.MEANING payment_method_name, h.funding_type_code, look2.MEANING funding_type_name, h.ipvs_id vendor_site_id, h.khr_id contract_id, h.date_entered, h.date_invoiced fund_due_date, h.date_funding_approved fund_approved_date, OKL_FUNDING_PVT.get_contract_line_funded_amt(h.id,h.funding_type_code) fund_total, h.trx_status_code, look3.MEANING trx_status_name, h.try_id fund_trx_type_id, hl.description fund_description, v.vendor_id, v.segment1 vendor_number, v.vendor_name, vs.vendor_site_code, vs.pay_site_flag pay_site_flag, SUBSTR(DECODE(vs.address_line1,NULL,NULL,vs.address_line1) || DECODE(vs.address_line2,NULL,NULL,' '||vs.address_line2) || DECODE(vs.address_line3,NULL,NULL,' '||vs.address_line3) || DECODE(vs.address_line4,NULL,NULL,' '||vs.address_line4) || DECODE(vs.city,NULL,NULL,' '||vs.city) || DECODE(vs.state,NULL,NULL,' '||vs.state) || DECODE(vs.province,NULL,NULL,' '||vs.province) || DECODE(vs.zip,NULL,NULL,' '||vs.zip) || DECODE(vs.country,NULL,NULL,', '||vs.country) ,1,80) vendor_site_address, h.org_id org_id, NVL(h.invoice_type, DECODE(h.funding_type_code, 'SUPPLIER_RETENTION', 'CREDIT', 'STANDARD')), look4.MEANING, h.pay_group_lookup_code, look5.displayed_field, h.ippt_id, trm.name FROM OKL_TRX_AP_INVOICES_B H,OKL_TRX_AP_INVOICES_TL HL,PO_VENDORS V,PO_VENDOR_SITES_ALL VS,FND_CURRENCIES_TL LCT,FND_LOOKUPS LOOK1,FND_LOOKUPS LOOK2,FND_LOOKUPS LOOK3,FND_LOOKUPS LOOK4,AP_TERMS_V TRM,PO_LOOKUP_CODES LOOK5 WHERE h.id = hl.id AND hl.LANGUAGE = USERENV('LANG') AND h.ipvs_id = vs.vendor_site_id AND vs.vendor_id = v.vendor_id AND h.CURRENCY_CODE = lct.CURRENCY_CODE AND lct.LANGUAGE = USERENV('LANG') AND h.payment_method_code = look1.LOOKUP_CODE AND look1.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD' AND h.funding_type_code = look2.LOOKUP_CODE AND look2.LOOKUP_TYPE = 'OKL_FUNDING_TYPE' AND h.trx_status_code = look3.LOOKUP_CODE AND look3.LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS' AND NVL(h.invoice_type, DECODE(h.funding_type_code, 'SUPPLIER_RETENTION', 'CREDIT', 'STANDARD')) = look4.LOOKUP_CODE AND look4.LOOKUP_TYPE = 'OKL_PAYABLES_INVOICE_TYPE' AND h.pay_group_lookup_code = look5.LOOKUP_CODE (+) AND look5.LOOKUP_TYPE (+) = 'PAY GROUP' AND h.ippt_id = trm.TERM_ID (+) AND h.funding_type_code <> 'ASSET_SUBSIDY'

View Text - HTML Formatted

SELECT H.ID FUND_ID
, H.VENDOR_INVOICE_NUMBER FUND_NUMBER
, H.CURRENCY_CODE
, LCT.NAME CURRENCY_NAME
, H.PAYMENT_METHOD_CODE
, LOOK1.MEANING PAYMENT_METHOD_NAME
, H.FUNDING_TYPE_CODE
, LOOK2.MEANING FUNDING_TYPE_NAME
, H.IPVS_ID VENDOR_SITE_ID
, H.KHR_ID CONTRACT_ID
, H.DATE_ENTERED
, H.DATE_INVOICED FUND_DUE_DATE
, H.DATE_FUNDING_APPROVED FUND_APPROVED_DATE
, OKL_FUNDING_PVT.GET_CONTRACT_LINE_FUNDED_AMT(H.ID
, H.FUNDING_TYPE_CODE) FUND_TOTAL
, H.TRX_STATUS_CODE
, LOOK3.MEANING TRX_STATUS_NAME
, H.TRY_ID FUND_TRX_TYPE_ID
, HL.DESCRIPTION FUND_DESCRIPTION
, V.VENDOR_ID
, V.SEGMENT1 VENDOR_NUMBER
, V.VENDOR_NAME
, VS.VENDOR_SITE_CODE
, VS.PAY_SITE_FLAG PAY_SITE_FLAG
, SUBSTR(DECODE(VS.ADDRESS_LINE1
, NULL
, NULL
, VS.ADDRESS_LINE1) || DECODE(VS.ADDRESS_LINE2
, NULL
, NULL
, ' '||VS.ADDRESS_LINE2) || DECODE(VS.ADDRESS_LINE3
, NULL
, NULL
, ' '||VS.ADDRESS_LINE3) || DECODE(VS.ADDRESS_LINE4
, NULL
, NULL
, ' '||VS.ADDRESS_LINE4) || DECODE(VS.CITY
, NULL
, NULL
, ' '||VS.CITY) || DECODE(VS.STATE
, NULL
, NULL
, ' '||VS.STATE) || DECODE(VS.PROVINCE
, NULL
, NULL
, ' '||VS.PROVINCE) || DECODE(VS.ZIP
, NULL
, NULL
, ' '||VS.ZIP) || DECODE(VS.COUNTRY
, NULL
, NULL
, '
, '||VS.COUNTRY)
, 1
, 80) VENDOR_SITE_ADDRESS
, H.ORG_ID ORG_ID
, NVL(H.INVOICE_TYPE
, DECODE(H.FUNDING_TYPE_CODE
, 'SUPPLIER_RETENTION'
, 'CREDIT'
, 'STANDARD'))
, LOOK4.MEANING
, H.PAY_GROUP_LOOKUP_CODE
, LOOK5.DISPLAYED_FIELD
, H.IPPT_ID
, TRM.NAME
FROM OKL_TRX_AP_INVOICES_B H
, OKL_TRX_AP_INVOICES_TL HL
, PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, FND_CURRENCIES_TL LCT
, FND_LOOKUPS LOOK1
, FND_LOOKUPS LOOK2
, FND_LOOKUPS LOOK3
, FND_LOOKUPS LOOK4
, AP_TERMS_V TRM
, PO_LOOKUP_CODES LOOK5
WHERE H.ID = HL.ID
AND HL.LANGUAGE = USERENV('LANG')
AND H.IPVS_ID = VS.VENDOR_SITE_ID
AND VS.VENDOR_ID = V.VENDOR_ID
AND H.CURRENCY_CODE = LCT.CURRENCY_CODE
AND LCT.LANGUAGE = USERENV('LANG')
AND H.PAYMENT_METHOD_CODE = LOOK1.LOOKUP_CODE
AND LOOK1.LOOKUP_TYPE = 'OKL_AP_PAYMENT_METHOD'
AND H.FUNDING_TYPE_CODE = LOOK2.LOOKUP_CODE
AND LOOK2.LOOKUP_TYPE = 'OKL_FUNDING_TYPE'
AND H.TRX_STATUS_CODE = LOOK3.LOOKUP_CODE
AND LOOK3.LOOKUP_TYPE = 'OKL_TRANSACTION_STATUS'
AND NVL(H.INVOICE_TYPE
, DECODE(H.FUNDING_TYPE_CODE
, 'SUPPLIER_RETENTION'
, 'CREDIT'
, 'STANDARD')) = LOOK4.LOOKUP_CODE
AND LOOK4.LOOKUP_TYPE = 'OKL_PAYABLES_INVOICE_TYPE'
AND H.PAY_GROUP_LOOKUP_CODE = LOOK5.LOOKUP_CODE (+)
AND LOOK5.LOOKUP_TYPE (+) = 'PAY GROUP'
AND H.IPPT_ID = TRM.TERM_ID (+)
AND H.FUNDING_TYPE_CODE <> 'ASSET_SUBSIDY'