SQL to fetch invoice spend amount along with supplier business classifications - Payable Cloud
- Satya
- Jul 1, 2020
- 2 min read
/*PO Based Invoices*/SELECT DISTINCT APS.vendor_name "VENDOR NAME", APS.segment1 "VENDOR NUMBER", Upper (aps.vendor_type_lookup_code) "VENDOR CODE", apss.vendor_site_code "VENDOR SITE", aia.invoice_num "INVOICE NUMBER", aia.invoice_date "INVOICE DATE", aia.invoice_amount "INVOICE AMOUNT", -- aia.amount_paid "AMOUNT PAID", Nvl(AILA.amount, 0) "INVOICE LINE AMOUNT" , To_char (Trunc (aca.check_date)) "PAYMENT DATE", Decode(AIA.payment_status_flag, 'Y', 'Paid', 'N', 'Unpaid', 'P', 'Partially Paid') "Payment Status", apss.address_line1, apss.address_line2, apss.address_line3, apss.address_line4, apss.city, apss.state, apss.country, apss.zip, apss.province, pha.segment1 po_number, pla.line_num po_line_number, aia.creation_date Invoice_creation_date, --pbc.LOOKUP_CODE Business_classification, --pbc.Status Business_classifi_status ( SELECT LISTAGG(DECODE(LOOKUP_CODE,'DISABLED_VETERAN_OWNED','Disabled Veteran - DVBE','HUB_ZONE','HUBZone Small Business-HZ','LGBTQ OR GENDER','LGBTQ or Gender Non-Conforming Individual owned-L','MINORITY_OWNED','Minority owned -MBE','PHYSICAL_CHALLEGE_ENTERPRISE','Physically Challenged -PBE','SMALL_BUSINESS','Small Business','SMALL_ADV_BUSINESS','Small Disadvantaged-SDB','VETERAN_OWNED','Veteran owned -VBE','WOMEN_OWNED','Women Owned -WBE'), CHR(10)) WITHIN GROUP( ORDER BY LOOKUP_CODE ) FROM ( SELECT LOOKUP_CODE FROM POZ_BUS_CLASSIFICATIONS pbc WHERE pbc.vendor_id(+)=aps.vendor_id AND pbc.deleted(+)='N' AND pbc.status(+)='A' ))Business_classification FROM ap_invoices_all aia, poz_suppliers_v aps, poz_supplier_sites_v apss, ap_invoice_payments_all aip, ap_checks_all aca, po_headers_all pha, po_lines_all pla, ap_invoice_lines_all aila--POZ_BUS_CLASSIFICATIONS pbc WHERE 1 = 1 AND aps.vendor_id = apss.vendor_id AND aia.vendor_id = aps.vendor_id AND aia.vendor_site_id = apss.vendor_site_id AND aia.invoice_id = aip.invoice_id AND aca.check_id = aip.check_id AND aca.vendor_site_id = apss.vendor_site_id AND aila.po_header_id = pha.po_header_id AND aia.vendor_id = pha.vendor_id AND aia.invoice_id = aila.invoice_id AND pla.po_header_id = pha.po_header_id AND aila.po_line_id = pla.po_line_id AND aca.status_lookup_code <> 'VOIDED' -- and aia.invoice_num='228368' AND aila.match_type = 'ITEM_TO_PO' AND aila.po_header_id IS NOT NULL --AND pbc.vendor_id(+)=aps.vendor_id -- AND pbc.deleted(+)='N' -- AND pbc.status(+)='A' -- AND pha.segment1=nvl(:P_PO_NUM,pha.segment1) -- AND aia.invoice_num=nvl(:P_INVOICE_NUM,aia.invoice_num) /* AND ( To_char(From_tz(aia.creation_date, dbtimezone) at time zone 'America/New_York' , 'YYYY-MM-DD') BETWEEN To_date(Trunc(:P_INV_FROM_DATE), 'YYYY-MM-DD') AND To_date(Trunc(:P_INV_TO_DATE), 'YYYY-MM-DD') ) */ AND ( To_char(aca.check_date,'YYYY-MM-DD') BETWEEN To_date(Trunc(:P_Payment_from_Date), 'YYYY-MM-DD') AND To_date(Trunc(:P_Payment_to_Date), 'YYYY-MM-DD') )--ORDER BY aia.creation_date DESC UNION /*NOn PO Based Invoices */SELECT DISTINCT APS.vendor_name "VENDOR NAME", APS.segment1 "VENDOR NUMBER", Upper (aps.vendor_type_lookup_code) "VENDOR CODE", apss.vendor_site_code "VENDOR SITE", aia.invoice_num "INVOICE NUMBER", aia.invoice_date "INVOICE DATE", aia.invoice_amount "INVOICE AMOUNT", -- aia.amount_paid "AMOUNT PAID", Nvl(AILA.amount, 0) "INVOICE LINE AMOUNT" , To_char (Trunc (aca.check_date)) "PAYMENT DATE", Decode(AIA.payment_status_flag, 'Y', 'Paid', 'N', 'Unpaid', 'P', 'Partially Paid') "Payment Status", apss.address_line1, apss.address_line2, apss.address_line3, apss.address_line4, apss.city, apss.state, apss.country, apss.zip, apss.province, NULL po_number, NULL po_line_number, aia.creation_date Invoice_creation_date , --pbc.LOOKUP_CODE Business_classification, --pbc.Status Business_classifi_status ( SELECT LISTAGG(DECODE(LOOKUP_CODE,'DISABLED_VETERAN_OWNED','Disabled Veteran - DVBE','HUB_ZONE','HUBZone Small Business-HZ','LGBTQ OR GENDER','LGBTQ or Gender Non-Conforming Individual owned-L','MINORITY_OWNED','Minority owned -MBE','PHYSICAL_CHALLEGE_ENTERPRISE','Physically Challenged -PBE','SMALL_BUSINESS','Small Business','SMALL_ADV_BUSINESS','Small Disadvantaged-SDB','VETERAN_OWNED','Veteran owned -VBE','WOMEN_OWNED','Women Owned -WBE'), CHR(10)) WITHIN GROUP( ORDER BY LOOKUP_CODE ) FROM ( SELECT LOOKUP_CODE FROM POZ_BUS_CLASSIFICATIONS pbc WHERE pbc.vendor_id(+)=aps.vendor_id AND pbc.deleted(+)='N' AND pbc.status(+)='A' ))Business_classificationFROM ap_invoices_all aia, poz_suppliers_v aps, poz_supplier_sites_v apss, ap_invoice_payments_all aip, ap_checks_all aca, ap_invoice_lines_all aila --POZ_BUS_CLASSIFICATIONS pbcWHERE 1 = 1 AND aps.vendor_id = apss.vendor_id AND aia.vendor_id = aps.vendor_id AND aia.vendor_site_id = apss.vendor_site_id AND aia.invoice_id = aip.invoice_id AND aca.check_id = aip.check_id AND aca.vendor_site_id = apss.vendor_site_id AND acA.status_lookup_code <> 'VOIDED' AND AIA.invoice_id = AILA.invoice_id AND AILA.po_header_id IS NULL AND AILA.match_type = 'NOT_MATCHED' --AND AIA.INVOICE_NUM='228368' -- AND pbc.vendor_id(+)=aps.vendor_id -- AND pbc.deleted(+)='N' -- AND pbc.status(+)='A' /* AND ( To_char(From_tz(aia.creation_date, dbtimezone) at time zone 'America/New_York' , 'YYYY-MM-DD') BETWEEN To_date(Trunc(:P_INV_FROM_DATE), 'YYYY-MM-DD') AND To_date(Trunc(:P_INV_TO_DATE), 'YYYY-MM-DD') )*/ AND ( To_char(aca.check_date,'YYYY-MM-DD') BETWEEN To_date(Trunc(:P_Payment_from_Date), 'YYYY-MM-DD') AND To_date(Trunc(:P_Payment_to_Date), 'YYYY-MM-DD') )
Comments