AP_EM_DISC_VNDR(SQL View) |
Index Back |
|---|---|
AP EM Discounts |
| SELECT %Sql(EOEW_FUNCLIB_DATE_YEAR_UPD, C.PYMNT_DT) , %Sql(EOEW_FUNCLIB_DATE_MONTH_UPD, C.PYMNT_DT) , B.BUSINESS_UNIT , B.VENDOR_SETID , B.VENDOR_ID , 'L' , SUM(A.PAID_AMT_DSCNT_BSE) , B.BASE_CURRENCY , COUNT(DISTINCT A.VOUCHER_ID) FROM PS_PYMNT_VCHR_XREF A , PS_VOUCHER B , PS_PAYMENT_TBL C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.BANK_SETID = C.BANK_SETID AND A.BANK_CD = C.BANK_CD AND A.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND A.PYMNT_ID = C.PYMNT_ID AND B.ENTRY_STATUS = 'P' AND B.VOUCHER_STYLE NOT IN ('PPAY', 'TMPL') AND A.PYMNT_SELCT_STATUS = 'P' AND A.PAID_AMT_DSCNT_BSE <> 0 AND ((A.LATE_CHRG_PAID_BSE > 0 AND A.PAID_AMT_GROSS_BSE + A.LATE_CHRG_PAID_BSE = A.PAID_AMT_BSE) OR (A.LATE_CHRG_PAID_BSE = 0 AND A.PAID_AMT_GROSS_BSE = A.PAID_AMT_BSE)) GROUP BY %Sql(EOEW_FUNCLIB_DATE_YEAR_UPD, C.PYMNT_DT) , %Sql(EOEW_FUNCLIB_DATE_MONTH_UPD, C.PYMNT_DT), B.BUSINESS_UNIT, B.VENDOR_SETID, B.VENDOR_ID, B.BASE_CURRENCY UNION SELECT %Sql(EOEW_FUNCLIB_DATE_YEAR_UPD, C.PYMNT_DT) , %Sql(EOEW_FUNCLIB_DATE_MONTH_UPD, C.PYMNT_DT) , B.BUSINESS_UNIT , B.VENDOR_SETID , B.VENDOR_ID , 'T' , SUM(A.PAID_AMT_DSCNT_BSE) , B.BASE_CURRENCY , COUNT(DISTINCT A.VOUCHER_ID) FROM PS_PYMNT_VCHR_XREF A , PS_VOUCHER B , PS_PAYMENT_TBL C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.VOUCHER_ID = B.VOUCHER_ID AND A.BANK_SETID = C.BANK_SETID AND A.BANK_CD = C.BANK_CD AND A.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND A.PYMNT_ID = C.PYMNT_ID AND B.ENTRY_STATUS = 'P' AND B.VOUCHER_STYLE NOT IN ('PPAY', 'TMPL') AND A.PYMNT_SELCT_STATUS = 'P' AND A.PAID_AMT_DSCNT_BSE <> 0 AND ((A.LATE_CHRG_PAID_BSE > 0 AND A.PAID_AMT_GROSS_BSE + A.LATE_CHRG_PAID_BSE - A.PAID_AMT_DSCNT_BSE = A.PAID_AMT_BSE) OR (A.LATE_CHRG_PAID_BSE = 0 AND A.PAID_AMT_GROSS_BSE - A.PAID_AMT_DSCNT_BSE = A.PAID_AMT_BSE)) GROUP BY %Sql(EOEW_FUNCLIB_DATE_YEAR_UPD, C.PYMNT_DT) , %Sql(EOEW_FUNCLIB_DATE_MONTH_UPD, C.PYMNT_DT), B.BUSINESS_UNIT, B.VENDOR_SETID, B.VENDOR_ID, B.BASE_CURRENCY |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  YEAR |
Character(4) | VARCHAR2(4) NOT NULL |
Year
1=NONE Prompt Table: YEAR_PROMPT_VW |
| 2 | MONTHCD |
Character(2) | VARCHAR2(2) NOT NULL |
Month
01=01 - January 02=02 - February 03=03 - March 04=04 - April 05=05 - May 06=06 - June 07=07 - July 08=08 - August 09=09 - September 10=10 - October 11=11 - November 12=12 - December |
| 3 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BUS_AP_NONVW |
| 4 | VENDOR_SETID |
Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 5 | VENDOR_ID |
Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 6 | CFOP_AP_STATUS |
Character(1) | VARCHAR2(1) NOT NULL |
Status
A=Average L=Lost T=Taken |
| 7 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 8 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 9 | NUM_RECORDS | Number(15,0) | DECIMAL(15) NOT NULL | Number of Records |