Non PO Based Invoice Count-R12 ap

The below Query will be helpful in fetching NON po based invoices.

SELECT HOU.NAME "OU NAME"

,count(DECODE(trim(to_char(AIA.creation_date,'MON')),'JAN',1)) "Jan-2016"

,count(DECODE(trim(to_char(AIA.creation_date,'MON')),'FEB',1)) "Feb-2016"

,count(DECODE(trim(to_char(AIA.creation_date,'MON')),'MAR',1)) "Mar-2016"

,count(DECODE(trim(to_char(AIA.creation_date,'MON')),'APR',1)) "Apr-2016"

,count(DECODE(trim(to_char(AIA.creation_date,'MON')),'MAY',1)) "May-2016"

FROM APPS.AP_INVOICES_ALL AIA, APPS.hr_operating_units hou

WHERE 1 = 1

AND AIA.ORG_ID = HOU.ORGANIZATION_ID

AND AIA.INVOICE_NUM NOT LIKE '%TEST%'

AND AIA.INVOICE_NUM NOT LIKE '%Test%'

AND AIA.INVOICE_NUM NOT LIKE '%test%'

AND EXISTS

(SELECT 1

FROM APPS.AP_INVOICE_LINES_ALL AILA

WHERE AILA.INVOICE_ID=AIA.INVOICE_ID

AND AILA.PO_HEADER_ID IS NULL

AND AILA.MATCH_TYPE='NOT_MATCHED')

AND TRUNC (AIA.creation_date) >=

NVL ( TO_DATE('01-OCT-2015','DD-MON-YYYY'), AIA.creation_date)

AND TRUNC (AIA.creation_date) <=

NVL ( TO_DATE('31-OCT-2016','DD-MON-YYYY'), AIA.creation_date)

GROUP BY AIA.ORG_ID, HOU.NAME

ORDER BY 1;