VIEW: APPS.DPP_WEBADI_V

Source

View Text - Preformatted

SELECT dtha.ref_document_number REF_DOCUMENT_NUMBER, dtha.effective_start_date EFFECTIVE_START_DATE, dtha.days_covered DAYS_COVERED, ood.organization_name ORGANIZATION_NAME, dtha.org_id ORG_ID, oca.claim_number CLAIM_NUMBER, fl.meaning CLAIM_TYPE, oca.currency_code CLAIM_CURRENCY, aps.vendor_name SUPPLIER_NAME, apss.vendor_site_code SUPPLIER_SITE, dtha.vendor_id SUPPLIER_ID, dtha.vendor_site_id SUPPLIER_SITE_ID, dtha.vendor_contact_name SUPPLIER_CONTACT_NAME, dtha.vendor_contact_id SUPPLIER_CONTACT_ID, dtha.contact_email_address SUPPLIER_CONTACT_EMAIL, dtha.contact_phone SUPPLIER_CONTACT_PHONE, dtha.attribute_category HEADER_ATTRIBUTE_CATEGORY, dtha.attribute1 HEADER_ATTRIBUTE1, dtha.attribute2 HEADER_ATTRIBUTE2, dtha.attribute3 HEADER_ATTRIBUTE3, dtha.attribute4 HEADER_ATTRIBUTE4, dtha.attribute5 HEADER_ATTRIBUTE5, dtha.attribute6 HEADER_ATTRIBUTE6, dtha.attribute7 HEADER_ATTRIBUTE7, dtha.attribute8 HEADER_ATTRIBUTE8, dtha.attribute9 HEADER_ATTRIBUTE9, dtha.attribute10 HEADER_ATTRIBUTE10, dtha.attribute11 HEADER_ATTRIBUTE11, dtha.attribute12 HEADER_ATTRIBUTE12, dtha.attribute13 HEADER_ATTRIBUTE13, dtha.attribute14 HEADER_ATTRIBUTE14, dtha.attribute15 HEADER_ATTRIBUTE15, dtha.attribute1 HEADER_ATTRIBUTE16, dtha.attribute2 HEADER_ATTRIBUTE17, dtha.attribute3 HEADER_ATTRIBUTE18, dtha.attribute4 HEADER_ATTRIBUTE19, dtha.attribute5 HEADER_ATTRIBUTE20, dtha.attribute6 HEADER_ATTRIBUTE21, dtha.attribute7 HEADER_ATTRIBUTE22, dtha.attribute8 HEADER_ATTRIBUTE23, dtha.attribute9 HEADER_ATTRIBUTE24, dtha.attribute10 HEADER_ATTRIBUTE25, dtha.attribute11 HEADER_ATTRIBUTE26, dtha.attribute12 HEADER_ATTRIBUTE27, dtha.attribute13 HEADER_ATTRIBUTE28, dtha.attribute14 HEADER_ATTRIBUTE29, dtha.attribute15 HEADER_ATTRIBUTE30, dtla.supplier_approved_by SUPPLIER_APPROVED_BY, dtla.supplier_approval_date SUPPLIER_APPROVED_DATE, dtla.supplier_part_num SUPPLIER_PART_NUM, msi.concatenated_segments ITEM_NUMBER, dtla.uom UOM, dtla.prior_price PRIOR_PRICE, dtla.supplier_new_price SUPPLIER_NEW_PRICE, dtla.price_change DELTA, dtla.covered_inventory COVERED_QUANTITY, dtla.claim_amount CLAIM_AMOUNT, dtla.approved_inventory APPROVED_QUANTITY, dtla.attribute_category "LINE_ATTRIBUTE_CATEGORY", dtla.attribute1 "LINE_ATTRIBUTE1", dtla.attribute2 "LINE_ATTRIBUTE2", dtla.attribute3 "LINE_ATTRIBUTE3", dtla.attribute4 "LINE_ATTRIBUTE4", dtla.attribute5 "LINE_ATTRIBUTE5", dtla.attribute6 "LINE_ATTRIBUTE6", dtla.attribute7 "LINE_ATTRIBUTE7", dtla.attribute8 "LINE_ATTRIBUTE8", dtla.attribute9 "LINE_ATTRIBUTE9", dtha.attribute10 "LINE_ATTRIBUTE10", dtla.attribute11 "LINE_ATTRIBUTE11", dtla.attribute12 "LINE_ATTRIBUTE12", dtla.attribute13 "LINE_ATTRIBUTE13", dtla.attribute14 "LINE_ATTRIBUTE14", dtla.attribute15 "LINE_ATTRIBUTE15", dtla.attribute1 "LINE_ATTRIBUTE16", dtla.attribute2 "LINE_ATTRIBUTE17", dtla.attribute3 "LINE_ATTRIBUTE18", dtla.attribute4 "LINE_ATTRIBUTE19", dtla.attribute5 "LINE_ATTRIBUTE20", dtla.attribute6 "LINE_ATTRIBUTE21", dtla.attribute7 "LINE_ATTRIBUTE22", dtla.attribute8 "LINE_ATTRIBUTE23", dtla.attribute9 "LINE_ATTRIBUTE24", dtha.attribute10 "LINE_ATTRIBUTE25", dtla.attribute11 "LINE_ATTRIBUTE26", dtla.attribute12 "LINE_ATTRIBUTE27", dtla.attribute13 "LINE_ATTRIBUTE28", dtla.attribute14 "LINE_ATTRIBUTE29", dtla.attribute15 "LINE_ATTRIBUTE30" FROM dpp_transaction_headers_all dtha, dpp_transaction_lines_all dtla, dpp_transaction_claims_all dtca, ozf_claims_all oca, ap_suppliers aps, ap_supplier_sites_all apss, org_organization_definitions ood, mtl_system_items_kfv msi, fnd_lookups fl WHERE dtha.transaction_header_id = dtla.transaction_header_id AND dtha.transaction_header_id = dtca.transaction_header_id AND dtla.supp_dist_claim_id = dtca.claim_id AND dtla.supplier_approved_by IS NULL AND dtca.claim_id = oca.claim_id AND msi.inventory_item_id = to_number(dtla.inventory_item_id) AND msi.organization_id = ood.organization_id AND aps.vendor_id = to_number(dtha.vendor_id) AND aps.vendor_id = apss.vendor_id AND apss.vendor_site_id = to_number(dtha.vendor_site_id) AND dtha.org_id = ood.organization_id AND fl.lookup_type = 'DPP_CLAIM_TYPES' AND fl.lookup_code = dtca.claim_type AND nvl(dtla.price_change,0) <> 0

View Text - HTML Formatted

SELECT DTHA.REF_DOCUMENT_NUMBER REF_DOCUMENT_NUMBER
, DTHA.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, DTHA.DAYS_COVERED DAYS_COVERED
, OOD.ORGANIZATION_NAME ORGANIZATION_NAME
, DTHA.ORG_ID ORG_ID
, OCA.CLAIM_NUMBER CLAIM_NUMBER
, FL.MEANING CLAIM_TYPE
, OCA.CURRENCY_CODE CLAIM_CURRENCY
, APS.VENDOR_NAME SUPPLIER_NAME
, APSS.VENDOR_SITE_CODE SUPPLIER_SITE
, DTHA.VENDOR_ID SUPPLIER_ID
, DTHA.VENDOR_SITE_ID SUPPLIER_SITE_ID
, DTHA.VENDOR_CONTACT_NAME SUPPLIER_CONTACT_NAME
, DTHA.VENDOR_CONTACT_ID SUPPLIER_CONTACT_ID
, DTHA.CONTACT_EMAIL_ADDRESS SUPPLIER_CONTACT_EMAIL
, DTHA.CONTACT_PHONE SUPPLIER_CONTACT_PHONE
, DTHA.ATTRIBUTE_CATEGORY HEADER_ATTRIBUTE_CATEGORY
, DTHA.ATTRIBUTE1 HEADER_ATTRIBUTE1
, DTHA.ATTRIBUTE2 HEADER_ATTRIBUTE2
, DTHA.ATTRIBUTE3 HEADER_ATTRIBUTE3
, DTHA.ATTRIBUTE4 HEADER_ATTRIBUTE4
, DTHA.ATTRIBUTE5 HEADER_ATTRIBUTE5
, DTHA.ATTRIBUTE6 HEADER_ATTRIBUTE6
, DTHA.ATTRIBUTE7 HEADER_ATTRIBUTE7
, DTHA.ATTRIBUTE8 HEADER_ATTRIBUTE8
, DTHA.ATTRIBUTE9 HEADER_ATTRIBUTE9
, DTHA.ATTRIBUTE10 HEADER_ATTRIBUTE10
, DTHA.ATTRIBUTE11 HEADER_ATTRIBUTE11
, DTHA.ATTRIBUTE12 HEADER_ATTRIBUTE12
, DTHA.ATTRIBUTE13 HEADER_ATTRIBUTE13
, DTHA.ATTRIBUTE14 HEADER_ATTRIBUTE14
, DTHA.ATTRIBUTE15 HEADER_ATTRIBUTE15
, DTHA.ATTRIBUTE1 HEADER_ATTRIBUTE16
, DTHA.ATTRIBUTE2 HEADER_ATTRIBUTE17
, DTHA.ATTRIBUTE3 HEADER_ATTRIBUTE18
, DTHA.ATTRIBUTE4 HEADER_ATTRIBUTE19
, DTHA.ATTRIBUTE5 HEADER_ATTRIBUTE20
, DTHA.ATTRIBUTE6 HEADER_ATTRIBUTE21
, DTHA.ATTRIBUTE7 HEADER_ATTRIBUTE22
, DTHA.ATTRIBUTE8 HEADER_ATTRIBUTE23
, DTHA.ATTRIBUTE9 HEADER_ATTRIBUTE24
, DTHA.ATTRIBUTE10 HEADER_ATTRIBUTE25
, DTHA.ATTRIBUTE11 HEADER_ATTRIBUTE26
, DTHA.ATTRIBUTE12 HEADER_ATTRIBUTE27
, DTHA.ATTRIBUTE13 HEADER_ATTRIBUTE28
, DTHA.ATTRIBUTE14 HEADER_ATTRIBUTE29
, DTHA.ATTRIBUTE15 HEADER_ATTRIBUTE30
, DTLA.SUPPLIER_APPROVED_BY SUPPLIER_APPROVED_BY
, DTLA.SUPPLIER_APPROVAL_DATE SUPPLIER_APPROVED_DATE
, DTLA.SUPPLIER_PART_NUM SUPPLIER_PART_NUM
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, DTLA.UOM UOM
, DTLA.PRIOR_PRICE PRIOR_PRICE
, DTLA.SUPPLIER_NEW_PRICE SUPPLIER_NEW_PRICE
, DTLA.PRICE_CHANGE DELTA
, DTLA.COVERED_INVENTORY COVERED_QUANTITY
, DTLA.CLAIM_AMOUNT CLAIM_AMOUNT
, DTLA.APPROVED_INVENTORY APPROVED_QUANTITY
, DTLA.ATTRIBUTE_CATEGORY "LINE_ATTRIBUTE_CATEGORY"
, DTLA.ATTRIBUTE1 "LINE_ATTRIBUTE1"
, DTLA.ATTRIBUTE2 "LINE_ATTRIBUTE2"
, DTLA.ATTRIBUTE3 "LINE_ATTRIBUTE3"
, DTLA.ATTRIBUTE4 "LINE_ATTRIBUTE4"
, DTLA.ATTRIBUTE5 "LINE_ATTRIBUTE5"
, DTLA.ATTRIBUTE6 "LINE_ATTRIBUTE6"
, DTLA.ATTRIBUTE7 "LINE_ATTRIBUTE7"
, DTLA.ATTRIBUTE8 "LINE_ATTRIBUTE8"
, DTLA.ATTRIBUTE9 "LINE_ATTRIBUTE9"
, DTHA.ATTRIBUTE10 "LINE_ATTRIBUTE10"
, DTLA.ATTRIBUTE11 "LINE_ATTRIBUTE11"
, DTLA.ATTRIBUTE12 "LINE_ATTRIBUTE12"
, DTLA.ATTRIBUTE13 "LINE_ATTRIBUTE13"
, DTLA.ATTRIBUTE14 "LINE_ATTRIBUTE14"
, DTLA.ATTRIBUTE15 "LINE_ATTRIBUTE15"
, DTLA.ATTRIBUTE1 "LINE_ATTRIBUTE16"
, DTLA.ATTRIBUTE2 "LINE_ATTRIBUTE17"
, DTLA.ATTRIBUTE3 "LINE_ATTRIBUTE18"
, DTLA.ATTRIBUTE4 "LINE_ATTRIBUTE19"
, DTLA.ATTRIBUTE5 "LINE_ATTRIBUTE20"
, DTLA.ATTRIBUTE6 "LINE_ATTRIBUTE21"
, DTLA.ATTRIBUTE7 "LINE_ATTRIBUTE22"
, DTLA.ATTRIBUTE8 "LINE_ATTRIBUTE23"
, DTLA.ATTRIBUTE9 "LINE_ATTRIBUTE24"
, DTHA.ATTRIBUTE10 "LINE_ATTRIBUTE25"
, DTLA.ATTRIBUTE11 "LINE_ATTRIBUTE26"
, DTLA.ATTRIBUTE12 "LINE_ATTRIBUTE27"
, DTLA.ATTRIBUTE13 "LINE_ATTRIBUTE28"
, DTLA.ATTRIBUTE14 "LINE_ATTRIBUTE29"
, DTLA.ATTRIBUTE15 "LINE_ATTRIBUTE30"
FROM DPP_TRANSACTION_HEADERS_ALL DTHA
, DPP_TRANSACTION_LINES_ALL DTLA
, DPP_TRANSACTION_CLAIMS_ALL DTCA
, OZF_CLAIMS_ALL OCA
, AP_SUPPLIERS APS
, AP_SUPPLIER_SITES_ALL APSS
, ORG_ORGANIZATION_DEFINITIONS OOD
, MTL_SYSTEM_ITEMS_KFV MSI
, FND_LOOKUPS FL
WHERE DTHA.TRANSACTION_HEADER_ID = DTLA.TRANSACTION_HEADER_ID
AND DTHA.TRANSACTION_HEADER_ID = DTCA.TRANSACTION_HEADER_ID
AND DTLA.SUPP_DIST_CLAIM_ID = DTCA.CLAIM_ID
AND DTLA.SUPPLIER_APPROVED_BY IS NULL
AND DTCA.CLAIM_ID = OCA.CLAIM_ID
AND MSI.INVENTORY_ITEM_ID = TO_NUMBER(DTLA.INVENTORY_ITEM_ID)
AND MSI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
AND APS.VENDOR_ID = TO_NUMBER(DTHA.VENDOR_ID)
AND APS.VENDOR_ID = APSS.VENDOR_ID
AND APSS.VENDOR_SITE_ID = TO_NUMBER(DTHA.VENDOR_SITE_ID)
AND DTHA.ORG_ID = OOD.ORGANIZATION_ID
AND FL.LOOKUP_TYPE = 'DPP_CLAIM_TYPES'
AND FL.LOOKUP_CODE = DTCA.CLAIM_TYPE
AND NVL(DTLA.PRICE_CHANGE
, 0) <> 0