PAYMENT_IDBUSINESS_UNITVOUCHER_IDVOUCHER_LINE_NUMN=Adhoc
Y=Item
N=Non PO
Y=PO
N=Non Contract
Y=Contract
Prompt Table: %EDIT_CNTRCT_LN
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
1=30 Days
2=60 Days
3=90 Days
4=Select Dates
PO_SPND_PAID_VW(SQL View) |
Index Back |
|---|---|
PO Spend History TablePO Spend History Table by PO Date |
| SELECT pymnt.pymnt_id , pymnt.pymnt_dt , To_Char(pymnt.Pymnt_Dt ,'YYYY-MM') , Xref.base_Currency , xref.paid_amt_bse , xref.business_unit , xref.voucher_id , vchr.invoice_id , Vchr.Invoice_Dt , vchr.gross_amt_bse voucher_gross_amt , Vl.Voucher_Line_Num , dl.distrib_line_num , (CASE WHEN vchr.vat_calc_type = 'I' THEN dl.merch_amt_bse ELSE dl.merch_amt_bse + dl.vat_inv_amt_bse END) + dl.saletx_amt_bse + dl.freight_amt_bse + dl.misc_amt_bse+ dl.misc_amt_np_bse + dl.freight_amt_np_bse+dl.saletx_amt_np_bse voucher_line_gross_amt , vchr.vendor_setid , vchr.vendor_id , vl.itm_setid , vl.Inv_Item_Id , CASE WHEN vl.Inv_Item_Id = ' ' THEN 'N' ELSE 'Y' END ITM_FLAG , vl.descr item_descr , vl.category_id item_category_id , vl.business_unit_po , vl.po_id , CASE WHEN Vl.Po_Id <> ' ' THEN 'Y' ELSE 'N' END Po_Flag , vl.line_nbr po_line_nbr , vl.sched_nbr po_sched_nbr , vl.cntrct_id , CASE WHEN Vl.Cntrct_Id = ' ' THEN 'N' ELSE 'Y' END Cntrct_FLAG , vl.version_nbr cntrct_version_nbr , vl.cntrct_line_nbr , vl.cat_line_nbr contrct_cat_line_nbr , vl.qty_vchr , vl.unit_of_measure , vl.unit_price , %CurrentDateIn , %CurrentDateIn ,'4' FROM ps_pymnt_vchr_xref xref , PS_VOUCHER vchr , PS_VOUCHER_line vl , ps_payment_tbl pymnt , ps_distrib_line dl WHERE vl.business_unit = dl.business_unit AND vl.voucher_id = dl.voucher_id AND vl.voucher_line_num = dl.voucher_line_num AND xref.business_unit = vchr.business_unit AND xref.voucher_id = vchr.voucher_id AND vl.business_unit = vchr.business_unit AND vl.voucher_id = vchr.voucher_id AND xref.pymnt_id = pymnt.pymnt_id AND pymnt.bank_setid = xref.bank_setid AND pymnt.bank_cd = xref.bank_cd AND pymnt.bank_acct_key = xref.bank_acct_key AND Vchr.Gross_Amt <>0 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | PAYMENT_ID |
Character(15) | VARCHAR2(15) NOT NULL | Identifies a customer payment. This is usually a check number but may be some other identifying number. |
| 2 | PYMNT_DT | Date(10) | DATE | Payment Date |
| 3 | PYMNT_MONTH | Character(10) | VARCHAR2(10) NOT NULL | Payment Month |
| 4 | TXN_CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Transaction Currency |
| 5 | PYMNT_GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Payment Amount |
| 6 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 7 | VOUCHER_ID |
Character(8) | VARCHAR2(8) NOT NULL | Voucher ID |
| 8 | INVOICE_ID | Character(30) | VARCHAR2(30) NOT NULL | Invoice Number |
| 9 | INVOICE_DT | Date(10) | DATE | Invoice Date |
| 10 | VOUCHER_GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Gross Invoice Amount |
| 11 | VOUCHER_LINE_NUM |
Number(5,0) | INTEGER NOT NULL | Voucher Line Number |
| 12 | DISTRIB_LINE_NUM | Number(5,0) | INTEGER NOT NULL | Distribution Line |
| 13 | VCHR_LN_GROSS_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Merchandise Amt F_KDHIL_R87H8 062804 MLM: Added new Line Amount Received Label |
| 14 | VENDOR_SETID | Character(5) | VARCHAR2(5) NOT NULL | Vendor SetID |
| 15 | VENDOR_ID | Character(10) | VARCHAR2(10) NOT NULL | Vendor Identifier |
| 16 | ITM_SETID | Character(5) | VARCHAR2(5) NOT NULL | Item SetID |
| 17 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
| 18 | ITEM_SPEND | Character(1) | VARCHAR2(1) NOT NULL |
Item Spend
N=Adhoc Y=Item |
| 19 | ITEM_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Item Description |
| 20 | ITEM_CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 21 | BUSINESS_UNIT_PO | Character(5) | VARCHAR2(5) NOT NULL | PO Business Unit |
| 22 | PO_ID | Character(10) | VARCHAR2(10) NOT NULL | Purchase Order |
| 23 | PO_SPEND | Character(1) | VARCHAR2(1) NOT NULL |
PO Spend
N=Non PO Y=PO |
| 24 | PO_LINE_NBR | Number(5,0) | INTEGER NOT NULL | Purchase Order Line Number |
| 25 | PO_SCHED_NBR | Number(3,0) | SMALLINT NOT NULL | PO Schedule Number |
| 26 | CNTRCT_ID | Character(25) | VARCHAR2(25) NOT NULL | Buying Agreement ID |
| 27 | CNTRCT_SPEND | Character(1) | VARCHAR2(1) NOT NULL |
PO Contract Spend
N=Non Contract Y=Contract |
| 28 | CNTRCT_VERSION_NBR | Number(5,0) | INTEGER NOT NULL | Contract Version number |
| 29 | CNTRCT_LINE_NBR | Number(5,0) | INTEGER NOT NULL |
Buying Agreement Line Nbr
Prompt Table: %EDIT_CNTRCT_LN |
| 30 | CNTRCT_CAT_LN_NBR | Number(5,0) | INTEGER NOT NULL | Contract Category Line Number |
| 31 | QTY_VCHR | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Quantity Vouchered |
| 32 | UNIT_OF_MEASURE | Character(3) | VARCHAR2(3) NOT NULL |
Used on an approval rule set.
MHR=Muti Hourly PER=Percentage SQF=Square Footage |
| 33 | UNIT_PRICE | Signed Number(17,5) | DECIMAL(15,5) NOT NULL | Unit Price |
| 34 | FROM_DT | Date(10) | DATE | From Date |
| 35 | TO_DT | Date(10) | DATE | To Date |
| 36 | PO_DATE_OPT | Character(1) | VARCHAR2(1) NOT NULL |
PO Date Option for PVG
1=30 Days 2=60 Days 3=90 Days 4=Select Dates |