CT_CORE_FN_AP_PNDNG_DISCOUNTS

Purpose of Query

Vouchers with pending discounts - This query returns all unpaid vouchers for a BU that have discount pay terms that are still candidates for an awarded discount. This proactive query should be run often (daily, or at least every few days), to catch vouchers about to lose a discount. The number of days pending takes into consideration that EPM data is 24 hours old.

Folder

AP

Tables

CTW_VCHR_TRANS - Voucher Transactions Rpt

Join CTW_PAYVC_XREF - Pay Voucher XRef Rpt

Note: A JOIN is an SQL command that is used to relate two or more data tables (Records in PeopleSoft) based on the use of related (key) fields from one table to the next. Once joined, data can be retrieved without repeating all of the data in every table.

Prompts

Business Unit=(Equal To)

Accounting Date not less than (>=)

Sample Results

Please note, due to page size limitations, sample results may be incomplete.

Criteria

B.DSCNT_DUE_DT - Discount Due Date is not Null

A.ENTRY_STATUS not equal to X

A.CLOSE_STATUS not equal to C

A.VOUCHER_STYLE not equal to TMPL

B.PYMNT_ID does not exist SUBQUERY

Note: A subquery is a query whose results are used by another query. The mainquery uses the subquery’s result set as a comparison value for a selection criterion.

SUBQUERY:

Table: CTW_PAYMNT_TRN - Payment Transactions Rpt

Note: Runtime prompts are included as criteria.

Fields

Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / A.BUSINESS_UNIT - Business Unit / Char5 / Business Unit
2 / B.REMIT_VENDOR - Remit Vendor / Char10 / Vendor
3 / B.CT_VNDR_NAME1 - Vendor Name 1 / Char40 / Vendor Name
4 / B.VOUCHER_ID - Voucher ID / Char8 / 2 / Voucher
5 / B.SCHEDULED_PAY_DT - Scheduled to Pay / Date / Scheduled Pay Date
6 / TRUNC(-(TO_DATE(B.SCHEDULED_PAY_DT,'YYYY-MM-DD')-(TO_DATE(A.DSCNT_DUE_DT,'YYYY-MM-DD')))-1) / Num20.0 / 1 / Days Remaining
7 / B.DSCNT_DUE_DT - Discount Due Date / Date / Disc due
8 / A.DSCNT_AMT - Discount Amount / SNm25.3 / Voucher Disc Amount
9 / A.PYMNT_TERMS_CD - Payment Terms ID / Char5 / Payment Terms Cd
10 / A.CT_PYMNT_TRM_DESCR - Payment Term Description / Char30 / Pymnt Trm Descr
11 / A.INVOICE_DT - Invoice Date / Date / Invoice Date
12 / A.ACCOUNTING_DT - Accounting Date / Date / Acctg Date
13 / A.ENTERED_DT - Entered on / Date / Entered Date

Note: Field Column 6 is an expression. Expressions are calculations that PeopleSoft Query performs as part of a query. Use them when you must calculate a value that PeopleSoft Query does not provide by default—for example, to add the values from two fields together or to multiply a field value by a constant.

You can work with an expression as if it were a field in the query: select it for output, change its column heading, or choose it as an “order by” column.

In Query Manager, you can use expressions in two ways:

As comparison values in selection criteria.

As columns in the query output.

1 of 3