top of page

Tags

Archive

SQL to fetch invoice spend amount along with supplier business classifications - Payable Cloud


/*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_classification
FROM   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 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 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') )

7 views0 comments

Recent Posts

See All

Dynamic Insertion#GL#FUSION

When transactions are entered, Dynamic Insertion in Oracle Fusion General Ledger (GL) allows an organisation to automatically insert extra segments or values in the GL chart of accounts. This feature

Segment Security #GL#Fusion

Oracle Fusion Segment Security The General Ledger (GL) feature enables an organisation to restrict access to data in the GL depending on certain parts of the GL chart of accounts. A segment is a subse

Cross Validation Rules #GL#Fusion Financials

Oracle Fusion General Ledger (GL) Cross-Validation Rules are used to assure the integrity and accuracy of data entered into the GL module. They are used to ensure that data submitted in the GL is cons

Other Posts you may Interested