Script to extract the accounting rule and source wise AR invoice counts

Introduction:

This script will extract the accounting rule and trx source wise invoice counts.

SELECT C.NAME RULE_NAME,
D.NAME SOURCE_NAME,
count(A.TRX_NUMBER),
DECODE(A.ORG_ID,’XX’,’IM US OU’,’XXX’,’IM CANADA OU’) OU_NAME
FROM RA_CUSTOMER_TRX_ALL A ,
RA_CUSTOMER_TRX_LINES_ALL B ,
RA_RULES C,
RA_BATCH_SOURCES_ALL D
WHERE A.CUSTOMER_TRX_ID=B.CUSTOMER_TRX_ID
AND B.ACCOUNTING_RULE_ID = C.RULE_ID(+)
AND A.BATCH_SOURCE_ID=D.BATCH_SOURCE_ID
AND A.ORG_ID=D.ORG_ID
AND A.ORG_ID IN(XX,XXX)
AND A.CREATION_DATE BETWEEN :from_date AND :to_Date1
GROUP BY C.NAME,D.NAME,A.ORG_ID;