SELECT DISTINCT
TO_CHAR (SYSDATE, 'MM/DD/YY') AS "TODAY"
,pov.VENDOR_ID AS "CUSTOMER SUPPLIER ID"
-- TAXID # (or) D&B Number
,pov.VENDOR_NAME AS "SUPPLIER NAME"
,pov.SEGMENT1 AS "VENDOR NUMBER"
,pvsa.ADDRESS_LINE1 AS "STREET ADDRESS1"
,pvsa.CITY AS "CITY"
,pvsa.STATE AS "STATE"
,pvsa.ZIP AS "POSTAL CODE"
,pvsa.COUNTRY AS "COUNTRY"
,pov.STANDARD_INDUSTRY_CLASS AS "Industry OR Commodity Code"
,apia.INVOICE_AMOUNT AS "NET AMOUNT OF INVOICE"
,apia.INVOICE_CURRENCY_CODE AS "CURRENCY CODE"
,pha.SEGMENT1 AS "Purchase Order Number"
,pha.TYPE_LOOKUP_CODE AS "Purchase Method"
,apia.INVOICE_NUM AS "Invoice Number"
,apia.INVOICE_TYPE_LOOKUP_CODE AS "Invoice Method"
,TO_CHAR (apia.INVOICE_DATE, 'MM/DD/YYYY') AS "Invoice Date"
,TO_CHAR (apia.CREATION_DATE, 'MM/DD/YYYY') AS "CREATION_DATE"
,aipa.PAYMENT_NUM AS "Payment Number"
,apsa.PAYMENT_METHOD_LOOKUP_CODE AS "Payment Method"
,att.NAME AS "Payment Terms"
,apsa.DUE_DATE AS "Payment Date"
--days payable outstanding
,gsob.NAME AS "General Ledger"
,hbg.BUSINESS_GROUP_NAME AS "Org OR BU"
,hl1.LOCATION_CODE AS "location/plant/division"
,aipa.DISCOUNT_LOST AS "Discount Taken"
,aipa.DISCOUNT_TAKEN AS "Discount Lost"
--optional field5
,apia.DESCRIPTION
,aida.PERIOD_NAME
-- aipa.BANK_ACCOUNT_NUM,
-- aipa.EXTERNAL_BANK_ACCOUNT_ID,
-- aipa.ELECTRONIC_TRANSFER_ID,
,TO_NUMBER (aca.CHECK_NUMBER) AS "CHECK_NUMBER"
,TO_CHAR (aca.CHECK_DATE, 'MM/DD/YYYY') AS "CHECK_DATE"
,aca.STATUS_LOOKUP_CODE
-- aca.CLEARED_DATE,
,apsa.DISCOUNT_AMOUNT_AVAILABLE
,apsa.DISCOUNT_AMOUNT_REMAINING
,apsa.PAYMENT_STATUS_FLAG
,aida.AMOUNT
,aida.DESCRIPTION AS dist_desc
,gcc.SEGMENT1
,gcc.SEGMENT2
,gcc.segment3
,gcc.segment4
FROM po.PO_VENDORS pov
,po.PO_VENDOR_SITES_ALL pvsa
,ap.AP_INVOICES_ALL apia
,ap.AP_INVOICE_PAYMENTS_ALL aipa
,gl.GL_CODE_COMBINATIONS gcc
,ap.AP_INVOICE_DISTRIBUTIONS_ALL aida
,ap.AP_PAYMENT_SCHEDULES_ALL apsa
,ap.AP_TERMS_TL att
,ap.AP_CHECKS_ALL aca
,po.PO_DISTRIBUTIONS_ALL pda
,po.PO_HEADERS_ALL pha
,gl.GL_SETS_OF_BOOKS gsob
,apps.HRFV_BUSINESS_GROUPS hbg
,apps.HR_LOCATIONS hl1
,apps.HR_LOCATIONS hl2
WHERE 1 = 1
AND pvsa.VENDOR_SITE_ID = pha.VENDOR_SITE_ID
/*apia.ORG_ID = &&5
and (apia.CREATION_DATE between to_date(nvl('&&1',to_char(sysdate+1,'YYYY/MM/DD') || '00:00:00'),'YYYY/MM/DD HH24:MI:SS') and to_date(nvl(substr('&&2',1,10),nvl(substr('&&1',1,10),to_char(sysdate + 1,'YYYY/MM/DD') )) || '23:59:59','YYYY/MM/DD HH24:MI:SS')
or aida.PERIOD_NAME in (nvl('&&1','&&3'),nvl('&&1','&&4')) )*/
AND pov.VENDOR_ID = apia.VENDOR_ID
AND apia.INVOICE_ID = aida.INVOICE_ID(+)
AND apia.INVOICE_ID = apsa.INVOICE_ID(+)
AND apia.INVOICE_ID = aipa.INVOICE_ID(+)
AND apia.TERMS_ID = att.TERM_ID
AND aida.DIST_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
AND aipa.CHECK_ID = aca.CHECK_ID(+)
AND aida.PO_DISTRIBUTION_ID = pda.PO_DISTRIBUTION_ID(+)
AND pda.PO_HEADER_ID = pha.PO_HEADER_ID(+)
AND POV.SHIP_TO_LOCATION_ID = HL1.SHIP_TO_LOCATION_ID
AND POV.BILL_TO_LOCATION_ID = HL2.SHIP_TO_LOCATION_ID
AND POV.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND HBG.LOCATION_ID = HL1.LOCATION_ID
AND ROWNUM<100