REFND_ITEM_VW

(SQL View)
 Index Back

Refundable Item View


SELECT a.business_unit ,a.common_id ,a.sa_id_type ,a.emplid ,a.ext_org_id ,a.account_nbr ,a.account_term ,a.item_nbr ,a.account_type_sf ,a.item_type ,a.item_amt ,a.applied_amt ,a.item_balance ,a.item_term ,a.payment_id_nbr ,a.refund_emplid ,a.refund_ext_org_id ,a.refund_org_contact ,d.item_type_cd ,d.taxable_y_n ,d.erncd ,d.erncd_notax ,d.tender_category ,e.credit_card_nbr ,a.sf_pmt_ref_nbr ,d.encumbrance_days ,e.posted_date FROM ps_item_sf a ,ps_bus_unit_tbl_sf b ,ps_set_cntrl_rec c ,PS_ITEM_TYPE_TBL d ,ps_payment_tbl e WHERE a.business_unit = b.business_unit AND c.setcntrlvalue = b.institution AND c.recname = 'ITEM_TYPE_TBL' AND c.setid = d.setid AND a.business_unit = e.business_unit AND a.common_id = e.common_id AND a.sa_id_type = e.sa_id_type AND a.payment_id_nbr = e.payment_id_nbr AND a.item_type = d.item_type AND d.effdt = ( SELECT MAX(dd.effdt) FROM PS_ITEM_TYPE_TBL dd WHERE d.setid = dd.setid AND d.item_type = dd.item_type AND dd.effdt <= %CurrentDateIn AND dd.eff_status = 'A') AND a.item_balance < 0 AND d.item_type_cd IN ('D','F','L','P','W') AND d.refundable_ind = 'Y' AND (d.encumbrance_days < %DateDiff(E.posted_date,%currentdatein) OR d.encumbrance_days = 0)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
3 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person
4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
5 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
6 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
7 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
8 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
9 ACCOUNT_TYPE_SF Character(3) VARCHAR2(3) NOT NULL Account Type
10 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
11 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
12 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
13 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
14 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
15 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr
16 REFUND_EMPLID Character(11) VARCHAR2(11) NOT NULL Refund ID
17 REFUND_EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL Refund External Org ID
18 REFUND_ORG_CONTACT Number(4,0) SMALLINT NOT NULL Refund Org Contact
19 ITEM_TYPE_CD Character(1) VARCHAR2(1) NOT NULL Item Type Code
A=Application Fee
B=Billing Only
C=Charge
D=Deposit
F=Financial Aid
G=GL Interface Only
H=Withholding
I=Interest
L=Pre-Paid Tuition
P=Payment
R=Refund
T=Transfers
V=Contributor Relations
W=Waiver
X=Write-off
Z=Pay Plan Credit
20 TAXABLE_Y_N Character(1) VARCHAR2(1) NOT NULL Taxable
21 ERNCD Character(3) VARCHAR2(3) NOT NULL Earnings Code
22 ERNCD_NOTAX Character(3) VARCHAR2(3) NOT NULL Earnings Code Non Taxable
23 TENDER_CATEGORY Character(3) VARCHAR2(3) NOT NULL Tender Category
CC=Credit Card
CCI=Credit Card In
CCO=Credit Card Out
CK=Check
CKI=Check In
CKO=Check Out
CS=Cash
CSI=Cash In
CSO=Cash Out
DB=Debit Card
DBI=Debit Card In
DBO=Debit Card Out
DD=Direct Deposit
DDI=Direct Debit
DDO=Direct Deposit
EC=Electronic Check
TP=Third Party Check
TPI=Third Party Check In
WIR=Wire Transfer
24 CREDIT_CARD_NBR Character(44) VARCHAR2(44) NOT NULL Card Number
25 SF_PMT_REF_NBR Character(12) VARCHAR2(12) NOT NULL Reference Nbr
26 ENCUMBRANCE_DAYS Number(3,0) SMALLINT NOT NULL Days to Encumber
27 POSTED_DATE Date(10) DATE Date Posted