Embedded Files
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
Report abuse