Monday, February 10, 2014

SQL Query to find number of checks printed by pay group in Oracle Account Payable

SQL Query to find number of checks printed by pay group in Oracle Account Payable


Below is the query developed to find no of checks printed between dates group by pay group-
SELECT AISCA.VENDOR_PAY_GROUP,COUNT(ACA.CHECK_ID)
FROM AP.AP_INV_SELECTION_CRITERIA_ALL AISCA ,AP.AP_CHECKS_ALL ACA
WHERE ACA.PAYMENT_METHOD_LOOKUP_CODE='CHECK'
AND AISCA.CHECKRUN_NAME=ACA.CHECKRUN_NAME
AND AISCA.CHECKRUN_ID=ACA.CHECKRUN_ID
AND TRUNC(ACA.CHECK_DATE) BETWEEN TRUNC(TO_DATE(:P_START_DATE,'DD-MON-YYYY')) AND TRUNC(TO_DATE(:P_END_DATE,'DD-MON-YYYY')  )
GROUP BY AISCA.VENDOR_PAY_GROUP;


Input Date format- DD-MON-YYYY e.g.01-JAN-2014

No comments:

Post a Comment