CT_CORE_FIN_AP_VENDOR_CREDIT

Purpose of Query

Vendors with credit balances - Query designed to identify vendors with a current credit balance. Prompt by business unit and accounting date.

Folder

AP

Tables

CTW_PAYVC_XREF - Pay Voucher XRef Reporting Tbl

Subquery CTW_VCHR_TRANS - Voucher Transactions Rpt Tbl

Prompts

Deposit BU (Business Unit) (Equal To condition)

ACCOUNTING_DATE – Accounting Date (Between condition)

Criteria

DUE_DT - Due Date is not null

PYMNT_SELCT_STATUS - Payment Selection Status not in list ('X','P') (Canceled, Paid)

A.PYMNT_GROSS_AMT - Gross Payment Amount less than 0 (Having)

Note: Runtime prompts are included as criteria.

Note: Having is when you want to select rows based on the results of an aggregate function – In this case, the Aggregate is the Payment Gross Amount field, which is first summed, and then you want to find where that sum is less than zero.

Sub Query

VOUCHER_STYLE - Voucher Style in list ('ADJ','CORR','REG','SGLP') (Adjustment, Reversal Voucher, Regular Voucher, Single Payment Voucher)

CLOSE_STATUS - Close Status Indicator not equal to C (Closed)

PROCESS_MAN_CLOSE - Process Manual Close equal to N (No)

ENTRY_STATUS - Entry Status equal to P (Postable)

Fields

Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / A.BUSINESS_UNIT - Business Unit / Char5 / Unit
2 / A.REMIT_SETID - Remit Setid / Char5 / SetID
3 / A.REMIT_VENDOR - Remit Vendor / Char10 / Vendor
4 / A.CT_VNDR_NAME1 - Vendor Name 1 / Char40 / Vendor Name 1
5 / A.PYMNT_GROSS_AMT - Gross Payment Amount / SNm25.3 / Sum / Sum Pymnt Gross Amt
6 / A.PYMNT_HOLD - Hold Payments / Char1 / N / On Hold

Sub Query

Col / Record.Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / B.VENDOR_ID - Vendor ID / Char10 / Vendor

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