This AP Stop Payment Register query lists stopped checks that have not been voided and may still be under review or pending further action
#Sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query lists stopped checks that have not been voided
CBBV.BANK_NAME BANK_NAME,
CBBV.BANK_BRANCH_NAME BANK_BRANCH_NAME,
AC.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
CBBV.BANK_PARTY_ID,
CBA.CURRENCY_CODE BANK_CURRENCY,
CBA.CURRENCY_CODE || ' (' || FCV.NAME || ')' BANK_CURRENCY_NAME,
AIA.INVOICE_NUM INVOICE_NUMBER,
AC.CURRENCY_CODE PAYMENT_CURRENCY,
AC.CURRENCY_CODE || ' (' || FCV2.NAME || ')' PAY_CURRENCY_NAME,
AIA.INVOICE_DATE INVOICE_DATE,
NVL (AIA.PAY_CURR_INVOICE_AMOUNT, 0) INVOICE_AMOUNT,
SUM (AIP.AMOUNT) SUM_PAY_AMOUNT,
AIA.DESCRIPTION DESCRIPTION,
AC.CLEARED_DATE,
ALC.DISPLAYED_FIELD,
CPD.PAYMENT_DOCUMENT_NAME,
AC.CHECK_NUMBER,
AC.CHECK_DATE CHECK_DATE,
NVL (AC.AMOUNT, 0) CHECK_AMOUNT,
AC.STOPPED_DATE,
AC.VENDOR_NAME SUPPLIER_NAME,
AIA.INVOICE_ID INVOICE_ID,
AIA.ORG_ID ORG_ID,
AC.CHECK_ID CHECK_ID,
AC.VENDOR_ID VENDOR_ID,
AC.CE_BANK_ACCT_USE_ID,
CBAU.BANK_ACCT_USE_ID,
HAOU.NAME ORGANIZATION_NAME,
PU.USERNAME USER_NAME,
CBBV.BRANCH_PARTY_ID
FROM
CE_BANK_ACCOUNTS CBA,
CE_PAYMENT_DOCUMENTS CPD,
CE_BANK_BRANCHES_V CBBV,
CE_BANK_ACCT_USES_ALL CBAU,
FND_CURRENCIES_VL FCV,
FND_CURRENCIES_VL FCV2,
AP_LOOKUP_CODES ALC,
AP_INVOICES_ALL AIA,
AP_INVOICE_PAYMENTS_ALL AIP,
AP_CHECKS_ALL AC,
PER_USERS PU,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
1 = 1
AND CPD.PAYMENT_DOCUMENT_ID(+) = AC.PAYMENT_DOCUMENT_ID
AND AC.STOPPED_DATE IS NOT NULL
AND (
AC.RELEASED_DATE IS NULL
OR (
AC.RELEASED_DATE IS NOT NULL
AND (
AC.STOPPED_DATE > AC.RELEASED_DATE
)
)
)
AND AC.VOID_DATE IS NULL
AND AC.CE_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID
AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND CBA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID
AND AIP.CHECK_ID = AC.CHECK_ID
AND AIA.INVOICE_ID = AIP.INVOICE_ID
AND ALC.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
AND ALC.LOOKUP_CODE = 'CLEARED'
AND FCV.CURRENCY_CODE = CBA.CURRENCY_CODE
AND FCV2.CURRENCY_CODE = AC.CURRENCY_CODE
AND PU.USER_ID = AC.STOPPED_BY
AND HAOU.ORGANIZATION_ID = AIA.ORG_ID
GROUP BY CPD.PAYMENT_DOCUMENT_NAME,
AC.PAYMENT_DOCUMENT_ID,
AC.CHECK_ID,
AC.CHECK_NUMBER,
AC.CHECK_DATE,
AC.AMOUNT,
AC.VENDOR_NAME,
PU.USERNAME,
AC.STOPPED_DATE,
AC.CLEARED_DATE,
ALC.DISPLAYED_FIELD,
AIA.INVOICE_ID,
AIA.PAY_CURR_INVOICE_AMOUNT,
AIA.INVOICE_NUM,
AIA.INVOICE_DATE,
AIA.DESCRIPTION,
CBBV.BANK_NAME,
CBBV.BANK_BRANCH_NAME,
AC.BANK_ACCOUNT_NAME,
CBA.CURRENCY_CODE,
AC.CE_BANK_ACCT_USE_ID,
CBAU.BANK_ACCT_USE_ID,
CBBV.BRANCH_PARTY_ID,
SYSDATE,
AC.CURRENCY_CODE,
FCV.NAME,
FCV2.NAME,
AIA.INVOICE_ID,
AC.CHECK_ID,
AC.VENDOR_ID,
AC.CE_BANK_ACCT_USE_ID,
CBAU.BANK_ACCT_USE_ID,
CBBV.BANK_PARTY_ID,
CBBV.BRANCH_PARTY_ID,
AIA.ORG_ID,
HAOU.NAME