Using SQL to Clear PeopleSoft Receivables Transaction Tables

Use the following SQL script to clear PeopleSoft Receivables transaction tables in test databases that are populated when you enter and post transactions, run aging, run statements, and generate overdue charges. This script also clears history tables.

SET ECHO ON
TRUNCATE TABLE PS_ITEM;
TRUNCATE TABLE PS_ITEM_ACT_VAT;
TRUNCATE TABLE PS_ITEM_ACTTAX;
TRUNCATE TABLE PS_ITEM_ACTTAX_DTL;
TRUNCATE TABLE PS_ITEM_ACTIVITY;
TRUNCATE TABLE PS_ITEM_DST;
TRUNCATE TABLE PS_ITEM_AUDIT
TRUNCATE TABLE PS_AR_IPAC_HDR
TRUNCATE TABLE PS_AR_IPAC_SGL
TRUNCATE TABLE PS_AR_IPAC_DETAIL
TRUNCATE TABLE PS_AR_CC_TRAN_HDR
TRUNCATE TABLE PS_AR_CC_TRAN_TBL
TRUNCATE TABLE PS_EE_ITM_ACCTG_LN
TRUNCATE TABLE PS_GROUP_CONTROL;
TRUNCATE TABLE PS_PENDING_DST;
TRUNCATE TABLE PS_PENDING_ITEM;
TRUNCATE TABLE PS_PENDING_VAT;
TRUNCATE TABLE PS_PENDING_TAX;
TRUNCATE TABLE PS_PENDING_TAX_DTL;
TRUNCATE TABLE PS_PENDING_IT_MRL;
TRUNCATE TABLE PS_DEPOSIT_CONTROL;
TRUNCATE TABLE PS_PAYMENT;
TRUNCATE TABLE PS_PAYMENT_ID_CUST;
TRUNCATE TABLE PS_PAYMENT_ID_ITEM;
TRUNCATE TABLE PS_PAYMENT_ITEM;
TRUNCATE TABLE PS_PAY_MISC_DST;
TRUNCATE TABLE PS_PAY_ITEM_MRL;
TRUNCATE TABLE PS_EE_DJR_ACCTG_LN;
TRUNCATE TABLE PS_CUST_DATA;
TRUNCATE TABLE PS_CUST_HISTORY_TBL;
TRUNCATE TABLE PS_SUBCUST_DATA;
TRUNCATE TABLE PS_SUBCUST_HISTORY;
TRUNCATE TABLE PS_VAT_BALANCES;
TRUNCATE TABLE PS_RVL_ACCTG_LN_AR;
TRUNCATE TABLE PS_TRN_CONTROL;
TRUNCATE TABLE PS_TRN_ID_CUST;
TRUNCATE TABLE PS_TRN_ID_ITEM;
TRUNCATE TABLE PS_TRN_ITEM;
TRUNCATE TABLE PS_DRAFT_CONTROL;
TRUNCATE TABLE PS_DRAFT_ITEM;
TRUNCATE TABLE PS_REMIT_CNTL;
TRUNCATE TABLE PS_REMIT_CUST;
TRUNCATE TABLE PS_REMIT_DATE;
TRUNCATE TABLE PS_REMIT_DTL;
TRUNCATE TABLE PS_REMIT_ITEM;
TRUNCATE TABLE PS_DD_CONTROL;
TRUNCATE TABLE PS_DD_ITEM;
TRUNCATE TABLE PS_DD_ITEM_MRL;
TRUNCATE TABLE PS_ACTION_LST;
TRUNCATE TABLE PS_CUST_AGING;
TRUNCATE TABLE PS_SUBCUST_AGING;
TRUNCATE TABLE PS_STMT_CUST;
TRUNCATE TABLE PS_STMT_CUST_DTL;
TRUNCATE TABLE PS_STMT_CUST_BFWD;
TRUNCATE TABLE PS_AR_STMT_CUST;
TRUNCATE TABLE PS_DUN_CUST;
TRUNCATE TABLE PS_DUN_CUST_DTL;
TRUNCATE TABLE PS_FC_CUST;
TRUNCATE TABLE PS_FC_CUST_DTL;
TRUNCATE TABLE PS_WS_CONTROL;
TRUNCATE TABLE PS_WS_ID_CUST;
TRUNCATE TABLE PS_WS_ID_ITEM;
TRUNCATE TABLE PS_WS_ITEM;
TRUNCATE TABLE PS_CUST_CONVER;
TRUNCATE TABLE PS_CUST_CONVER_HDR;
TRUNCATE TABLE PS_CUST_CONVER_DTL;
TRUNCATE TABLE PS_CUST_CONVER_ATT;
TRUNCATE TABLE PS_AR_ITEM_H;
TRUNCATE TABLE PS_AR_ITEM_DST_H;
TRUNCATE TABLE PS_AR_AR_ITM_ACTVTY_H;
TRUNCATE TABLE PS_AR_ITM_ACTVAT_H;
TRUNCATE TABLE PS_AR_ITM_ACTTAX_H;
TRUNCATE TABLE PS_AR_ITM_ACTXDT_H;
TRUNCATE TABLE PS_AR_ITEM_AUDIT_H;
TRUNCATE TABLE PS_AR_IPAC_HDR_H;
TRUNCATE TABLE PS_AR_IPAC_SGL_H;
TRUNCATE TABLE PS_AR_IPAC_DET_H;
TRUNCATE TABLE PS_AR_CCTRN_HDR_H;
TRUNCATE TABLE PS_AR_CCTRN_TBL_H;
TRUNCATE TABLE PS_AR_EE_ITACLN_H;
TRUNCATE TABLE PS_AR_GROUP_CNTL_H;
TRUNCATE TABLE PS_AR_PEND_ITM_H;
TRUNCATE TABLE PS_AR_PEND_DST_H;
TRUNCATE TABLE PS_AR_PEND_VAT_H;
TRUNCATE TABLE PS_AR_PEND_TAX_H;
TRUNCATE TABLE PS_AR_PEND_TX_DT_H;
TRUNCATE TABLE PS_AR_IT_MR_H;
TRUNCATE TABLE PS_AR_DEP_CONTRL_H;
TRUNCATE TABLE PS_AR_PAYMENT_H;
TRUNCATE TABLE PS_AR_PAY_ITEM_H;
TRUNCATE TABLE PS_AR_PAY_ID_CUS_H;
TRUNCATE TABLE PS_AR_PAY_ID_ITM_H;
TRUNCATE TABLE PS_AR_PAY_IT_MRL_H;
TRUNCATE TABLE PS_AR_PAY_MIS_DS_H;
TRUNCATE TABLE PS_AR_EE_DJR_ACT_H;
TRUNCATE TABLE PS_AR_STMT_CUST_H;
TRUNCATE TABLE PS_AR_STMT_CUSDT_H;
TRUNCATE TABLE PS_AR_STMT_CUSBD_H;
TRUNCATE TABLE PS_AR_STMT_CUS_H;
TRUNCATE TABLE PS_AR_DD_CONTROL_H;
TRUNCATE TABLE PS_AR_DD_ITEM_H;
TRUNCATE TABLE PS_AR_DD_ITM_MRL_H;
TRUNCATE TABLE PS_AR_ACTION_LST_H;