Business Alert Query Samples

ERPro Business Alert is an add-on module to help you manage your business more easily and effectively. You can identify any business-critical issues early and resolve them and exercise better control over your business. These queries can also be used in adhoc query. See also How to execute adhoc query?

The followings are some sample queries for Business Alert:

1. Sales Details Today

SELECT

DOC_TYPE=V.DOCTYPE,

REF_NO=V.REFNBR,

DESCRIPTION=V.DOCDESC,

CUSTOMER_ID=V.CUSTID,

CUSTOMER_NAME=V.NAME,

C.CUSTOMER_CLASS,

CURRBAL=C.RECEIVABLES_BALANCE,

SALESPERSON_ID=V.SLSPERID,

DOC_DATE=DOCDATE,

DUE_DATE=DUEDATE,

CURRENCY=CURYID,

INVOICE_AMOUNT=CURYORIGDOCAMT,

LOCAL_INVOICE_AMOUNT=ORIGDOCAMT

FROM V_ARDOC V

INNER JOIN GBCUST C ON C.CUSTOMER_NO = V.CUSTID

WHERE

V.DOCTYPE IN ('INVOICE','DEBIT MEMO','CREDIT MEMO')

AND DATEDIFF(DAY, DOCDATE, GETDATE()) = 0

ORDER BY V.DOCDATE, V.REFNBR

2. Payments Today

SELECT

A.DOCTYPE,

REF_NO=A.REFNBR,

DOC_DATE=A.DOCDATE,

A.CUSTID,

CUSTOMER_NAME=A.NAME,

A.CURYID,

DOCUMENT_AMT=-CURYORIGDOCAMT,

DOCUMENT_BALANCE=-CURYDOCBAL,

LOCAL_DOCUMENT_AMT=-ORIGDOCAMT,

LOCAL_DOCUMENT_BAL=-DOCBAL,

A.DOCDESC

FROM VR_ARDOC A

WHERE A.POSTED=1

AND A.DOCTYPE IN ('PA')

AND DATEDIFF(DAY, DOCDATE, GETDATE())=0

ORDER BY A.DOCDATE

3. Quotations expired Today

SELECT REF_NO, SALESMAN_NO, ISSUE_DATE, CUSTOMER_NAME, DESCRIPTION, NET_ORDER_AMT, EXPIRY_DATE

FROM SLQUOH

WHERE DATEDIFF(D, EXPIRY_DATE,GETDATE())=0

AND STATUS = 'OPEN'

ORDER BY SALESMAN_NO, REF_NO

4. Outstanding Invoices

SELECT

A.POSTED,

SALESPERSON_ID=A.SALESMAN_NO,

CUSTOMER_ID=A.CUSTOMER_NO,

CUSTOMER_NAME=A.ENGLISH_NAME,

CLASSID=A.CUSTOMER_CLASS,

DOCUMENT_TYPE=A.DOC_TYPE,

REF_NBR=A.INVOICE_NO,

A.DESCRIPTION,

DOCDATE=A.INVOICE_DATE,

DUEDATE=A.DUE_DATE,

CURY=A.CURRENCY_CODE,

A.NET_INVOICE_AMT,

A.BALANCE,

A.LOCAL_BALANCE

FROM VR_ARRCS2 A

WHERE A.BALANCE <> 0

ORDER BY A.DUE_DATE

5. Today Transactions

SELECT TXN_TYPE, TXN_DATE, POSTED=DBO.YESNO(POSTED), REFERENCE, SOURCE_REF, PROJECT_CODE, DESCRIPTION, TXN_LOCAL, CREATE_BY, CREATE_DATE, LAST_UPDATE_BY, LAST_UPDATE_DATE FROM V_ALLTXNS WHERE DATEDIFF(D, LAST_UPDATE_DATE,GETDATE())=0

6. Posted-Date Cheque(s) are ready to bank in

SELECT H.BANK_CHEQUE_DATE, H.BANK_CODE, H.BANK_CHEQUE_NO, H.CURRENCY_CODE, H.FOREX_AMT, H.RECEIPT_NO, H.RECEIPT_DATE, H.CUSTOMER_NO, C.ENGLISH_NAME

FROM ARPAYH H

INNER JOIN GBCUST C ON C.CUSTOMER_NO = H.CUSTOMER_NO

WHERE ((H.POSTED = 1) AND (H.IS_PD_CHEQUE = 1)) AND (H.PD_CHEQUE_POSTED = 0)

AND H.BANK_CHEQUE_DATE <= GETDATE()

7. Today's Events

SELECT EVENTTYPE, EVENTTIME, COMPUTERNAME, USERID, EVENT FROM EVENTLOG WHERE DATEDIFF(D,EVENTDATE,GETDATE())=0

ORDER BY EVENTTIME

8. Outstanding Project Balances

SELECT

'Project Code'=PROJECT_CODE,

'Project Name'=PROJECT_NAME,

'Salesperson'=SALESMAN_NO,

'Budget Revenues'=BUDGET_PROJECT_REVENUES,

'Actual Receipts'= ISNULL((SELECT SUM(LOCAL_PAID_AMT+LOCAL_TERM_DISC) FROM ARINVH I WHERE I.POSTED=1 AND I.PROJECT_CODE = PJPROJECT.PROJECT_CODE),0),

'Outstanding Balance'=BUDGET_PROJECT_REVENUES-ISNULL((SELECT SUM(LOCAL_PAID_AMT+LOCAL_TERM_DISC) FROM ARINVH I WHERE I.POSTED=1 AND I.PROJECT_CODE = PJPROJECT.PROJECT_CODE),0)

From PJPROJECT

WHERE AUTHORIZED=1 AND STATUS = 'O'

9. Invoices for New Customers Last Month

SELECT I.CUSTOMER_NO, C.ENGLISH_NAME, I.INVOICE_NO, I.INVOICE_DATE,

I.DESCRIPTION,

I.LOCAL_NET_INVOICE_AMT

FROM ARINVH I

INNER JOIN GBCUST C ON C.CUSTOMER_NO = I.CUSTOMER_NO

WHERE YEAR(I.INVOICE_DATE)*12+MONTH(I.INVOICE_DATE)=YEAR(GETDATE())*12+MONTH(GETDATE())-1

AND YEAR(C.CREATE_DATE)*12+MONTH(C.CREATE_DATE) = YEAR(GETDATE())*12+MONTH(GETDATE())-1

10. Quotations today

SELECT

'Quotation No'=REF_NO,

'Issue Date'=ISSUE_DATE,

'Customer Name'=ENGLISH_NAME,

'Salesperson ID'=SALESMAN_NO,

'Description'=DESCRIPTION,

'Quotation Amt'=NET_ORDER_AMT

FROM V_SLQUOH

WHERE DATEDIFF(D,[CREATE_DATE],GETDATE())=0

11. Customers; with Balance over $100,000

SELECT CUSTOMER_NO, ENGLISH_NAME, RECEIVABLES_BALANCE FROM GBCUST WHERE RECEIVABLES_BALANCE > 100000 ORDER BY RECEIVABLES_BALANCE DESC

12. Customers; with Balance over Credit Limit

select CUSTOMER_NO, ENGLISH_NAME, CREDIT_LIMIT, RECEIVABLES_BALANCE from gbcust

WHERE CREDIT_LIMIT > 0 AND RECEIVABLES_BALANCE > CREDIT_LIMIT order by RECEIVABLES_BALANCE

13. Inventory; On hand qty below safety stock

SELECT ITEM_NO, ITEM_NAME,STOCK_UNIT, SAFETY_STOCK, QTY_ON_HAND FROM GBITEM

WHERE SAFETY_STOCK > 0 AND QTY_ON_HAND < SAFETY_STOCK

14. Inventory; Qty Available below reorder point

SELECT ITEM_NO, ITEM_NAME,STOCK_UNIT, QTY_ON_HAND,

QTY_ON_ORDER_SALES,

QTY_ON_ORDER_PURCHASE,

QTY_AVAILABLE=QTY_ON_HAND+QTY_ON_ORDER_PURCHASE-QTY_ON_ORDER_SALES,

REORDER_POINT,

DIFF= REORDER_POINT - (QTY_ON_HAND+QTY_ON_ORDER_PURCHASE-QTY_ON_ORDER_SALES)

FROM GBITEM

WHERE QTY_ON_HAND+QTY_ON_ORDER_PURCHASE-QTY_ON_ORDER_SALES < REORDER_POINT

15. Outstanding Invoices with overdue more than 90 days

SELECT INVOICE_NO, INVOICE_DATE, DUE_DATE, DAYS_OVERDUE=CAST(GETDATE()-DUE_DATE AS NUMERIC(18,0)), H.CUSTOMER_NO, C.ENGLISH_NAME, LOCAL_NET_INVOICE_AMT

FROM ARINVH H

INNER JOIN GBCUST C ON C.CUSTOMER_NO = H.CUSTOMER_NO

WHERE POSTED=1 AND SETTLED=0 AND DUE_DATE+90 < GETDATE()

ORDER BY DUE_DATE

16. Outstanding AP Invoices with discount date within the coming 7 days.

SELECT INVOICE_NO, H.VENDOR_NO, V.ENGLISH_NAME, INVOICE_DATE, DUE_DATE,DISCOUNT_DATE, DAYS=DATEDIFF(D, GETDATE(),DISCOUNT_DATE), LOCAL_NET_INVOICE_AMT

FROM APINVH H

INNER JOIN GBVEND V ON V.VENDOR_NO = H.VENDOR_NO

WHERE POSTED=1 AND SETTLED=0 AND DATEDIFF(D, GETDATE(),DISCOUNT_DATE) <= 7

17. Purchase Orders issued today that the PO Amount is over $100,000

SELECT

H.ORDER_NO,

H.VENDOR_NO,

VENDOR_NAME=V.ENGLISH_NAME,

H.CURRENCY_CODE,

H.NET_ORDER_AMT,

LOCAL_NET_ORDER_AMT=H.NET_ORDER_AMT*H.EXCH_RATE

FROM PUORDH H

INNER JOIN GBVEND V ON V.VENDOR_NO=H.VENDOR_NO

WHERE DATEDIFF(d,H.LAST_UPDATE_DATE,GETDATE())=0

AND H.NET_ORDER_AMT*H.EXCH_RATE >=100000