VCHR_CLOSE_ELIG

(SQL View)
 Index Back

Vouchers eligible to be closed

This is a view of those vouchers that are eligible to be closed. 07/25/2011 cmh Bug 12785827 Modified SQL to replace "EXISTS" clauses with joins for better performance.

SELECT VCHR.BUSINESS_UNIT , VCHR.VOUCHER_ID , VCHR.INVOICE_ID , VCHR.VENDOR_SETID , VCHR.VENDOR_ID , VCHR.CLOSE_STATUS FROM PS_VOUCHER VCHR LEFT OUTER JOIN PS_VCHR_PPAY_XREF PPAY ON PPAY.BUSINESS_UNIT = VCHR.BUSINESS_UNIT AND PPAY.VOUCHER_ID = VCHR.VOUCHER_ID , PS_PYMNT_VCHR_XREF PVX WHERE VCHR.PROCESS_MAN_CLOSE IN ('Y', 'N') AND VCHR.POST_STATUS_AP = 'P' AND VCHR.IN_PROCESS_FLG = 'N' AND VCHR.VCHR_SRC IN ('AMLS','ARCR','BILL','CNTR','CONS','CUST','EDI','FRE','MTCH','NERS','ONL','PAYR','PBIL','PERS','PPAY','PROC','QUCK','RBCR','RETL','RPOV','RTV','SAD','SPRO','SSI','TRPM','XML','XLS','PRQ','DCM') AND PVX.BUSINESS_UNIT = VCHR.BUSINESS_UNIT AND PVX.VOUCHER_ID = VCHR.VOUCHER_ID GROUP BY VCHR.BUSINESS_UNIT, VCHR.VOUCHER_ID, VCHR.INVOICE_ID, VCHR.VENDOR_SETID, VCHR.VENDOR_ID, VCHR.CLOSE_STATUS HAVING SUM(CASE PVX.PYMNT_SELCT_STATUS WHEN 'N' THEN CASE PVX.PYMNT_TYPE WHEN 'W' THEN 0 ELSE 1 END WHEN 'X' THEN CASE VCHR.VOUCHER_STYLE WHEN 'CLBK' THEN 1 ELSE 0 END ELSE 0 END) > 0 AND SUM(CASE PPAY.PREPAID_STATUS WHEN 'A' THEN 1 WHEN 'F' THEN 1 ELSE 0 END) = 0 AND SUM(CASE PVX.PYMNT_TYPE WHEN 'W' THEN CASE PVX.PYMNT_SELCT_STATUS WHEN 'X' THEN 0 ELSE 1 END ELSE 0 END) = 0 AND SUM(CASE PVX.NET_SELCT_STATUS WHEN 'S' THEN 1 ELSE 0 END) = 0

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

Prompt Table: SP_BU_AP_NONVW

2 VOUCHER_ID Character(8) VARCHAR2(8) NOT NULL Voucher ID
3 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
4 SETID Character(5) VARCHAR2(5) NOT NULL SetID
5 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
6 CLOSE_STATUS Character(1) VARCHAR2(1) NOT NULL Close Status Indicator
C=Closed
O=Open

Default Value: O