1. Tables and Views for Procurement
  2. POZ_BI_HIERARCHY_AP_INV_DIST_V

POZ_BI_HIERARCHY_AP_INV_DIST_V

Details

  • Schema: FUSION

  • Object owner: POZ

  • Object type: VIEW

Columns

Name

VENDOR_ID

HIERARCHY_AMOUNT

Query

SQL_Statement

select /* INDEX(INV_HEADER, AP_INVOICES_N2) INDEX(INV_DIST, AP_INVOICE_DISTRIBUTIONS_U1)*/

supplier_hierarchy.root_vendor_id VENDOR_ID, sum(INV_DIST.AMOUNT) HIERARCHY_AMOUNT

from

(

select connect_by_root s.vendor_id root_vendor_id, s.vendor_id

from poz_suppliers s

connect by (prior s.vendor_id = s.parent_vendor_id)

) supplier_hierarchy,

AP_INVOICES_ALL INV_HEADER, AP_INVOICE_DISTRIBUTIONS_ALL INV_DIST

WHERE INV_HEADER.INVOICE_ID = INV_DIST.INVOICE_ID

AND INV_HEADER.WFAPPROVAL_STATUS IN ('MANUALLY APPROVED','NOT REQUIRED','WFAPPROVED','APPROVED')

AND INV_HEADER.INVOICE_TYPE_LOOKUP_CODE NOT IN ('AWT', 'PREPAYMENT')

AND INV_HEADER.INVOICE_TYPE_LOOKUP_CODE IS NOT NULL

AND INV_DIST.LINE_TYPE_LOOKUP_CODE NOT IN ('AWT','PREPAY')

AND INV_DIST.MATCH_STATUS_FLAG ='A'

AND INV_HEADER.VENDOR_ID = supplier_hierarchy.vendor_id

group by supplier_hierarchy.root_vendor_id