Tables involved in Payables
--ACH, EFT SET UP
SELECT * FROM PS_ACH_FILESEQ_TBL;
SELECT * FROM PS_ACH_LAYOUT_TBL;
SELECT * FROM PS_DR_BUS_EVNT_TBL;
SELECT * FROM PS_EFT_BANK_REASON;
SELECT * FROM PS_BACS_LOGON_UK;
SELECT * FROM PS_BTL91_OPT_TBL;
SELECT * FROM PS_BTL91_REG_TBL;
SELECT * FROM PS_CPA005_REG_TBL;
SELECT * FROM PS_DTALSV_REG_TBL;
SELECT * FROM PS_DTAZV_REG_TBL;
SELECT * FROM PS_EFT_FILESEQ_TBL;
SELECT * FROM PS_EFT_LAYOUT_TBL;
SELECT * FROM PS_ETEBAC_OPT_TBL;
SELECT * FROM PS_FEDI_REG_TBL;
--PAYCYCLE RELATED CONFIGURATIONS
SELECT * FROM PS_PYMNT_SOURCE;
SELECT * FROM PS_PYCYCL_STEP_DFN;
SELECT * FROM PS_IMAGE_TABLE;
SELECT * FROM PS_PYMNT_FORM;
SELECT * FROM PS_PYMNT_FORM_SORT;
-- Wthd Types & Classes, Jurisdiction
SELECT * FROM PS_WTHD_JUR_TBL;
SELECT * FROM PS_WTHD_TYPE_TBL;
SELECT * FROM PS_WTHD_CLASS_TBL;
--Aging
SELECT * FROM PS_AP_AGING_AGE;
SELECT * FROM PS_AP_AGING_CYCLE;
--Stamp Tax
SELECT * FROM PS_APD_STMPTAX_AMT;
SELECT * FROM PS_APD_STMPTAX_ID;
SELECT * FROM PS_APD_STPTAX_SPLT;
--Vendor Control
SELECT * FROM PS_VENDOR_CONTROL;
--Accounting Entry Template
SELECT * FROM PS_DST_CNTRL_HDR;
SELECT * FROM PS_DST_CNTRL_TBL;
SELECT * FROM PS_DST_DETL_CNTRL;
--Payment Handling Code
SELECT * FROM PS_PYMNT_HNDL_TBL;
--Payment Terms
SELECT * FROM PS_PYMT_TRMS_TIME;
--Ultimate Use Code
SELECT * FROM PS_SUT_USE_CD;
SELECT * FROM PS_SUT_USE_CD_HDR;
--Government Certifications for Vendors
SELECT * FROM PS_GOV_CERTSRC_TBL;
SELECT * FROM PS_GOV_CERT_TBL;
--Vendor Pay Group
SELECT * FROM PS_VNDR_PAYGRP_TBL ;
--Vendor Conversation Key Words
SELECT * FROM PS_KEYWORD_AP;
--Payment Messages
SELECT * FROM PS_PYMNT_MSG_LN;
SELECT * FROM PS_PYMNT_MSG_TBL;
--Voucher Build Codes
SELECT * FROM PS_VCHR_BLD_CODE;
SELECT * FROM PS_VCHR_BLD_PO;
SELECT * FROM PS_VCHR_BLD_RECV;
--APD Sight Codes
SELECT * FROM PS_APD_SIGHTCD_TBL;
--Payment Reason
SELECT * FROM PS_PYMNT_REASON;
--Withholding Categrories
SELECT * FROM PS_WTHD_VNDR_CAT;
--Withholding Business Unit Categories
SELECT * FROM PS_WTHD_BU_CAT;
SELECT * FROM PS_WTHD_TYPE_TBL;
--Withholding Report Control Information
SELECT * FROM PS_M1099_CNTL_PIG;
SELECT * FROM PS_M1099_CNTL_ST;
SELECT * FROM PS_WTHD_CNTL;
SELECT * FROM PS_WTHD_CNTL_BU;
SELECT * FROM PS_WTHD_CNTL_PAYER;
SELECT * FROM PS_WTHD_CNTL_VNDR;
--Withholding Report Definition
SELECT * FROM PS_WTHD_RPT_DFN;
SELECT * FROM PS_WTHD_RPT_DTL;
SELECT * FROM PS_WTHD_RPT_LN;
SELECT * FROM PS_WTHD_RPT_PRCS;
SELECT * FROM PS_WTHD_TYPE_TBL;
SELECT * FROM PS_WTHD_CLASS_TBL;
SELECT * FROM PS_WTHD_ENTITY_DFN;
SELECT * FROM PS_WTHD_JUR_TBL;
--Withholding Rules
SELECT * FROM PS_WTHD_RULE_DTL;
SELECT * FROM PS_WTHD_RULE_HDR;
SELECT * FROM PS_WTHD_RULE_TIER;
--1099 Piggy Back State
SELECT * FROM PS_M1099_STATE_TBL;
--Payment Terms Single Payment
SELECT * FROM PS_PYMT_TRMS_HDR;
SELECT * FROM PS_PYMT_TRMS_CNTL;
SELECT * FROM PS_PYMT_TRMS_NET;
SELECT * FROM PS_PYMT_TRMS_DSCNT;
--Payment Terms Multi Payment
SELECT * FROM PS_PYMT_TRMS_CNTL;
SELECT * FROM PS_PYMT_TRMS_HDR;
SELECT * FROM PS_PYMT_TRMS_INST;
--Late Interest Charges
SELECT * FROM PS_LATE_CHRG_HDR;
SELECT * FROM PS_LATE_CHRG_TBL;
--Late Interest Charges Chartfield
SELECT * FROM PS_LATE_CHRG_CF;
--Bank Replacement Rules
SELECT * FROM PS_BANK_RPL_RULE;
SELECT * FROM PS_BANK_RULE_EFFDT;
SELECT * FROM PS_BANK_RULE_SEQ;
--Tax Authority Set Up
SELECT * FROM PS_TAX_AUTHORITY;
SELECT * FROM PS_TAX_AUTH_HDR;
--Procurement Control
SELECT * FROM PS_BUS_UNIT_INTFC;
SELECT * FROM PS_BU_AP_GL_OPT;
SELECT * FROM PS_BU_WTHD_ENT_FLD;
SELECT * FROM PS_WTHD_BU_HDR;
SELECT * FROM PS_WTHD_BU_JUR;
SELECT * FROM PS_WTHD_BU_JUR_DTL;
--Document Association Group
SELECT * FROM PS_AP_ASSTN_ALGHDR;
SELECT * FROM PS_AP_ASSTN_ALGTHM;
SELECT * FROM PS_AP_ASSTN_FLDMAP;
--Withholding Entities
SELECT * FROM PS_BU_WTHD_ENT_FLD;
SELECT * FROM PS_WTHD_CATEGORY;
SELECT * FROM PS_WTHD_ENTITY_DFN;
SELECT * FROM PS_WTHD_ENTITY_DTL;
SELECT * FROM PS_WTHD_ENTITY_HDR;
SELECT * FROM PS_WTHD_ENTITY_VND;
SELECT * FROM PS_WTHD_ENT_FIELDS;
SELECT * FROM PS_WTHD_RULE_HDR;
--Match Rules Type
SELECT * FROM PS_AP_MTH_RULETYPE;
SELECT * FROM PS_AP_RULETYPE_DTL;
--Match Rules
SELECT * FROM PS_AP_ASSTN_HDR;
SELECT * FROM PS_AP_MTCH_RULES;
SELECT * FROM PS_AP_MTCH_WB_FLD;
SELECT * FROM PS_AP_RULE_SCOPE;
--Sales Tax Codes
SELECT * FROM PS_TAX_AUTHORITY;
SELECT * FROM PS_TAX_AUTH_HDR;
SELECT * FROM PS_TAX_CD;
SELECT * FROM PS_TAX_HEADER_TBL;
--Voucehr Origin
SELECT * FROM PS_APD_SIGHTCD_TBL;
SELECT * FROM PS_DOC_TYPE_TBL;
SELECT * FROM PS_ORIGIN_AP_OPT;
--Group AP
SELECT * FROM PS_GRP_AP;
SELECT * FROM PS_GRP_AP_TAPE;
--OFAC RELATED TABLES
PS_SDN_SRCH_SETUP
PS_SDN_INQ_HDR
***********************
*BUSINESS UNIT RELATED*
***********************
PS_BUS_UNIT_TBL_AP
****************
*ORIGIN RELATED*
****************
PS_ORIGIN_AP_OPT
PS_ORIGIN_AP
***********************
*SET UP RELATED TABLES*
***********************
PS_AUTO_NUM_TBL;
PS_AUTO_NUMFLD_TBL;
PS_BU_AP_GL_OPT;
PS_BUS_UNIT_INTFC;
PS_BUS_UNIT_OPT_AP
PS_BUS_UNIT_SCR_AP
PS_BUS_UNIT_TBL_AP;
PS_CM_LC_COMPONENT;
PS_CNTRCT_CONTROL;
PS_DST_CNTRL_HDR;
PS_DST_CNTRL_TBL;
PS_DST_DETL_CNTRL;
PS_FIN_BU_LGRP_TBL;
PS_FIN_BU_MAPPING;
PS_FIN_OPEN_OPTN;
PS_FIN_OPEN_PERIOD;
PS_JRNLGEN_DEFMB ;
PS_JRNLGEN_DEFN ;
PS_JRNLGEN_DEFNV ;
PS_JRNLGEN_SUMV;
PS_ORIGIN_AP
PS_ORIGIN_AP_OPT
PS_PYMNT_HNDL_TBL
PS_PYMNT_REASON;
PS_PYMT_TRMS_CNTL
PS_PYMT_TRMS_DSCNT
PS_PYMT_TRMS_HDR
PS_PYMT_TRMS_NET
PS_PYMT_TRMS_TIME
PS_RT_TYPE_TBL;
PS_VCHR_BLD_CODE;
PS_VCHR_BLD_PO;
**********************
*WITHHOLDING RELATED*
**********************
PS_WTHD_ENTITY_DFN
PS_WTHD_ENTITY_HDR
PS_WTHD_ENTITY_DTL
PS_WTHD_CATEGORY
PS_WTHD_ENTITY_FIELDS
PS_BU_WTHD_ENT_FLD
PS_WTHD_CNTL
PS_WTHD_CNTL_PAYER
PS_VENDOR_CONTROL ;
PS_WTHD_BU_HDR;
PS_WTHD_BU_JUR;
PS_WTHD_ENTITY_DFN
PS_WTHD_RPT_DFN
PS_WTHD_RPT_DTL
PS_WTHD_RPT_LN
***********************************
*DOCUMENT ASSOCIATION FOR MATCHING*
***********************************
SELECT * FROM PS_AP_ASSTN_GRP;
SELECT * FROM PS_AP_ASSTN_GRP_DT;
SELECT * FROM PS_AP_ASSTN_HDR;
*********************
Vendor Related Tables
*********************
PS_VENDOR
PS_VNDR_ADDR_SCROL
PS_VENDOR_ADDR
PS_VENDOR_ADDR_phn
PS_VNDR_LOC_SCROL
PS_VENDOR_LOC
PS_VENDOR_PAY
PS_VNDR_BANK_ACCT
PS_VNDR_CNTCT_SCR
PS_VNDR_CNTCT_PHN
PS_VENDOR_CNTCT
PS_VENDOR_WTHD
PS_VENDOR_INVOICE
PS_VNDR_IND_CLASS
PS_VENDOR_WTHD_JUR
*********************
Voucher Related Tables
*********************
PS_VOUCHER
PS_VOUCHER_LINE
PS_DISTRIB_LINE
PS_VCHR_LINE_WTHD
PS_VCHR_FRGH_CHRG
PS_PYMNT_VCHR_MSG
PS_PYMNT_VCHR_XREF
PS_VCHR_ACCTG_LINE
PS_PAYMENT_TBL
PS_VCHR_VNDR_INFO
PS_VCHR_VNDR_BANK
PS_VCHR_REG_LC
PS_PYCYCL_VCHR_SEL
*********************
Paycycle Related Tables
*********************
PS_PYCYCL
PS_PYCYCL_SOURCE
PS_PYCYCL_BU
PS_PYCYCL_STAT
*Staging Tables for XML Invoices*
*********************************
select * from sysadm.ps_VCHR_HDR_STG;
select * from sysadm.ps_VCHR_LINE_STG;
select * from sysadm.ps_VCHR_DIST_STG;
***************************
*QV Tables for QV Invoices*
***************************
SELECT * FROM PS_VCHR_HDR_QV;
SELECT * FROM PS_VCHR_LINE_QV;
SELECT * FROM PS_VCHR_DIST_QV;
SELECT * FROM PS_VCHR_PYMT_QV;
SELECT * FROM PS_VCHR_MSCH_QV;
*******************
*Bank Related Tables*
*******************
PS_BANK_BALANCES
PS_BANK_STMT_TBL
PS_BNK_RCN_CYC
PS_BANK_ACCT_CHRT
PS_BANK_ACCT_CHRT
PS_BANK_ACCT_CPTY
PS_BANK_ACCT_CURR
PS_BANK_ACCT_DEFN
PS_BANK_ACCT_MTHD
PS_BANK_BRANCH_TBL
PS_BANK_CD_TBL
PS_BANK_CF_OPTIONS
*************************************
*Voucher Post / Payment Post Failure*
*************************************
SELECT DISTINCT A.PYMNT_ID, A.PYMNT_ID_REF,B.PYMNT_CNT, C.BUSINESS_UNIT, C.VOUCHER_ID, C.APPL_JRNL_ID, A.CANCEL_ACTION,
A.POST_STATUS_AP,A.CANCEL_POST_STATUS
FROM PS_PAYMENT_TBL A, PS_PYMNT_VCHR_XREF B, PS_VCHR_ACCTG_LINE C
WHERE A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
AND A.PYMNT_ID = B.PYMNT_ID
AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
AND B.VOUCHER_ID = C.VOUCHER_ID
AND B.PYMNT_CNT = C.PYMNT_CNT
AND ((A.POST_STATUS_AP = 'U' and C.APPL_JRNL_ID = 'PAYMENTS')
--<== Make sure you substitute the correct APPL_JRNL_ID for PAYMENT
OR (A.POST_STATUS_AP = 'P' and A.CANCEL_ACTION in ('R','C','H') AND
C.APPL_JRNL_ID='CANCEL' AND A.CANCEL_POST_STATUS ='W'))
--<== Make sure you substitute the correct APPL_JRNL_ID for CANCEL
If PS_PAYMENT_TBL.POST_STATUS_AP ='U' but the Payment entries exists in PS_VCHR_ACCTG_LINE (C.APPL_JRNL_ID='PAYMENT')
then update PS_PAYMENT_TBL.POST_STATUS_AP ='P'
If A.CANCEL_POST_STATUS ='W' but the Cancel entries exists in PS_VCHR_ACCTG_LINE (C.APPL_JRNL_ID='CANCEL')
then PS_PAYMENT_TBL.CANCEL_POST_STATUS ='P'
Make sure the IN_PROCESS_FLG is set to an N. Also, either cancel or delete the process from the Process Monitor
VOUCHER POST (APPSTVCH / AP_PSTVCHR) :
There are Vouchers which are already posted but the POST_STATUS_AP flags are not updated to 'P'. The following SQL statement will help in identifying such Vouchers. (UNPOST_SEQ = '0' must be included to exclude the unposted Vouchers).
SELECT DISTINCT A.BUSINESS_UNIT, A.VOUCHER_ID, A.CLOSE_STATUS,A.POST_STATUS_AP , A.IN_PROCESS_FLG, A.PROCESS_MAN_CLOSE, B.APPL_JRNL_ID
FROM PS_VOUCHER A, PS_VCHR_ACCTG_LINE B
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.VOUCHER_ID = B.VOUCHER_ID
AND (( A.POST_STATUS_AP = 'U'
AND B.APPL_JRNL_ID = 'ACCRUAL'
--<== Make sure you substitute the correct APPL_JRNL_ID for ACCRUAL
AND B.UNPOST_SEQ = 0)
OR ( A.POST_STATUS_AP = 'P' AND A.PROCESS_MAN_CLOSE = 'Y'
AND B.APPL_JRNL_ID = 'CLOSURE')) --<== Make sure you substitute the correct APPL_JRNL_ID for CLOSURE
Once the Vouchers are identified:
1) If PS_VOUCHER.POST_STATUS_AP = 'U' then update PS_VOUCHER.POST_STATUS_AP = 'P'
2) If PS_VOUCHER.PROCESS_MAN_CLOSE ='Y' then update
PS_VOUCHER.PROCESS_MAN_CLOSE ='P'
PS_VOUCHER.CLOSE_STATUS ='C' (From "O")
PS_PYMNT_VCHR_XREF.PYMNT_SELCT_STATUS ='C' (The row which has PYMNT_SELCT_STATUS= "N")
3) Make sure the IN_PROCESS_FLG on PS_VOUCHER is set to N.
4) Either cancel or delete the process from the Process Monitor.
**************
*Unix Command*
**************
find . -type f -exec egrep '07891939' {} \;