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 |