SF_PAY_LOG_VW

(SQL View)
 Index Back

SF Payment Log View

This view is used to insert into the SF_PAYMENT_LOG by the SF_PAYMENT_CI.

SELECT A.SF_PMT_REF_NBR , A.COMMON_ID , A.SA_ID_TYPE , A.SEQUENCE_NBR_3 , A.SF_PAYMENT_STATUS , A.SF_POST_STATUS , A.INSTITUTION_SET , A.PAYMENT_AMT , A.CR_CARD_EXPMO , A.CR_CARD_EXPYR , A.CR_CARD_FNAME , A.CR_CARD_LNAME , A.CR_CARD_NBR , A.CR_CARD_TYPE , %subrec(ADDRESS_SBR, A) , A.EMAIL_ADDR , A.PHONE , A.CR_CARD_AUTH_CD , A.CR_CARD_AUTH_DT , A.CR_CARD_RQST_ID , A.CR_CARD_A_DTTM , A.CR_CARD_AUTH_REPLY , A.CR_CARD_TRANS_LAST , A.CR_CARD_AVS_CD , A.CURRENCY_CD , A.OPRID , A.CR_CARD_DIGITS , A.SF_REVERSAL_IND , A.POSTED_DATE , A.POSTED_DATETIME , A.MESSAGE_SET_NBR , A.MESSAGE_NBR , A.SSF_ERROR_WARN , A.BUSINESS_UNIT_ERR , A.LAST_DTTM_UPDATE , A.BATCH_ID , A.BUSINESS_UNIT , A.REFUND_NBR , A.LINE_SEQ_NBR , A.CR_CARD_TRANS_SRC , A.REVERSAL_STAT_SF , A.PROCESS_INSTANCE , A.CASHIER_OFFICE , A.SF_REGISTER , A.RECEIPT_NBR , A.RECEIPT_LINE_NBR , A.ADM_APPL_NBR , A.INSTITUTION , A.ADM_APPL_CTR , A.SF_MERCHANT_ID , A.CR_CARD_SRVC_PROV , A.TRANS_REF_NO , A.TENDER_CATEGORY , A.BANK_ACCT_TYP , A.BANK_CD , A.SSF_BNK_ACCT_NUM , A.EC_SETTLE_METHOD , A.EC_VERIFICATION , A.SSF_AGREEMENT_NBR , A.SCC_EPAY_SESSIONID , A.SCC_EPAY_CVRESULT , %subrec(SCC_AUDIT_SBR, A) , A.SCC_EPAY_RET_MSG , A.SSF_POST_TYPE , A.SSF_EXT_TRANS_REF , A.SSF_PMT_SRC_STATUS , A.SCC_PMT_APPL , A.SCC_TEMP_ID FROM PS_SF_PAYMENT_LOG A WHERE SA_ID_TYPE = 'P'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SF_PMT_REF_NBR2 Character(12) VARCHAR2(12) NOT NULL Reference Nbr
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 SEQUENCE_NBR_3 Number(3,0) SMALLINT NOT NULL Sequence Nbr
5 SF_PAYMENT_STATUS Character(2) VARCHAR2(2) NOT NULL Payment Status
A=Authorization Successful
B=Payment Completed
C=Refunded
D=Declined
E=Edit Error
I=Payment Initiated
M=Manually Approved/Settled
N=Payment Cancelled
P=Credit Pending
R=Refund Cancelled
S=Payment Submitted

Default Value: I

6 SF_POST_STATUS Character(1) VARCHAR2(1) NOT NULL Posting Status
E=Errors Encountered.
P=Pending
S=Successfully Posted
7 INSTITUTION_SET Character(5) VARCHAR2(5) NOT NULL Institution Set
8 PAYMENT_AMT Number(19,3) DECIMAL(18,3) NOT NULL Payment Amount
9 CR_CARD_EXPMO Character(2) VARCHAR2(2) NOT NULL Card Expiration Month
01=01
02=02
03=03
04=04
05=05
06=06
07=07
08=08
09=09
10=10
11=11
12=12
10 CR_CARD_EXPYR Character(4) VARCHAR2(4) NOT NULL Card Expiration Year
11 CR_CARD_FNAME Character(40) VARCHAR2(40) NOT NULL Credit Card First Name
12 CR_CARD_LNAME Character(40) VARCHAR2(40) NOT NULL Credit Card Last Name
13 CR_CARD_NBR Character(44) VARCHAR2(44) NOT NULL Credit Card Number
14 CR_CARD_TYPE Character(2) VARCHAR2(2) NOT NULL Credit Card Type

Prompt Table: CREDIT_CARD_TYP

15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: SCC_CNT_ADFMTVW

16 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
17 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
18 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
19 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
20 CITY Character(30) VARCHAR2(30) NOT NULL City
21 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
22 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
23 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference
24 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
25 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
26 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
27 COUNTY Character(30) VARCHAR2(30) NOT NULL County
28 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: STATE_TBL

29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
30 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
31 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

32 EMAIL_ADDR Character(70) VARCHAR2(70) NOT NULL Email Address
33 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
34 CR_CARD_AUTH_CD Character(10) VARCHAR2(10) NOT NULL Credit Card Authorization Code
35 CR_CARD_AUTH_DT Date(10) DATE Auth Date
36 CR_CARD_RQST_ID Character(30) VARCHAR2(30) NOT NULL Credit Card Request ID
37 CR_CARD_A_DTTM Character(20) VARCHAR2(20) NOT NULL Credit Card Authorization DTTM
38 CR_CARD_AUTH_REPLY Character(15) VARCHAR2(15) NOT NULL Authorization Reply
39 CR_CARD_TRANS_LAST Character(2) VARCHAR2(2) NOT NULL Transaction Requested
1=Authorize
2=Authorize and Capture
3=Capture
4=Credit
5=eCheck Debit
6=eCheck Credit
91=Request Token
92=Authorize and Capture
93=Hosted Payment Credit
40 CR_CARD_AVS_CD Character(1) VARCHAR2(1) NOT NULL Credit Card AVS Return Code
41 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
42 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
43 CR_CARD_DIGITS Character(4) VARCHAR2(4) NOT NULL Card Number's Last Four Digits
44 SF_REVERSAL_IND Character(1) VARCHAR2(1) NOT NULL Reversal Indicator
45 POSTED_DATE Date(10) DATE Date Posted
46 POSTED_DATETIME DateTime(26) TIMESTAMP Posted Date & Time
47 MESSAGE_SET_NBR Number(5,0) INTEGER NOT NULL Message Set Number. This field refers to the Message Set Number in the Message Catalog.
48 MESSAGE_NBR Number(5,0) INTEGER NOT NULL Message Number. This field refers to the Message Number in the Message Catalog.
49 SSF_ERROR_WARN Character(1) VARCHAR2(1) NOT NULL Field to hold translate values for Message Types - Error/Warning/Message
E=Error
M=Message
W=Warning
50 BUSINESS_UNIT_ERR Character(5) VARCHAR2(5) NOT NULL Business Unit
51 LAST_DTTM_UPDATE DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
52 BATCH_ID Character(12) VARCHAR2(12) NOT NULL Batch ID
53 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
54 REFUND_NBR Number(12,0) DECIMAL(12) NOT NULL Refund Nbr
55 LINE_SEQ_NBR Number(3,0) SMALLINT NOT NULL Line Sequence Nbr
56 CR_CARD_TRANS_SRC Character(1) VARCHAR2(1) NOT NULL Payment Transaction Source
A=Application Center
C=Cashiering
I=Contributor Relations
M=Miscellaneous Fees
P=Payment
R=Refund
V=Payment Reversal
57 REVERSAL_STAT_SF Character(1) VARCHAR2(1) NOT NULL Reversal Status
E=Errors were encountered
S=Successful
58 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
59 CASHIER_OFFICE Character(8) VARCHAR2(8) NOT NULL Cashier's Office
60 SF_REGISTER Character(5) VARCHAR2(5) NOT NULL Cash Register
61 RECEIPT_NBR Number(12,0) DECIMAL(12) NOT NULL Receipt Number
62 RECEIPT_LINE_NBR Number(3,0) SMALLINT NOT NULL Receipt Line Number
63 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr
64 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution
65 ADM_APPL_CTR Character(4) VARCHAR2(4) NOT NULL Application Center
66 SF_MERCHANT_ID Character(12) VARCHAR2(12) NOT NULL SF Merchant ID
67 CR_CARD_SRVC_PROV Character(2) VARCHAR2(2) NOT NULL Credit Card Service Provider
01=Cybersource
02=TouchNet
99=Unsupported
68 TRANS_REF_NO Character(60) VARCHAR2(60) NOT NULL Transaction Reference Nbr
69 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
70 BANK_ACCT_TYP Character(1) VARCHAR2(1) NOT NULL Bank Account Type
C=Checking
S=Savings
71 BANK_CD Character(11) VARCHAR2(11) NOT NULL Bank ID
72 SSF_BNK_ACCT_NUM Character(44) VARCHAR2(44) NOT NULL Bank Account Number
73 EC_SETTLE_METHOD Character(1) VARCHAR2(1) NOT NULL Settlement Method
A=Automated Clearing House
B=Best possible
F=Facsimile draft
74 EC_VERIFICATION Character(1) VARCHAR2(1) NOT NULL Verification Level
1=Validation
2=Verification
75 SSF_AGREEMENT_NBR Character(12) VARCHAR2(12) NOT NULL Agreement Number
76 SCC_EPAY_SESSIONID Character(254) VARCHAR2(254) NOT NULL Session ID
77 SCC_EPAY_CVRESULT Character(22) VARCHAR2(22) NOT NULL Credit Verification Result
78 SCC_ROW_ADD_OPRID Character(30) VARCHAR2(30) NOT NULL Created By
79 SCC_ROW_ADD_DTTM DateTime(26) TIMESTAMP Created
80 SCC_ROW_UPD_OPRID Character(30) VARCHAR2(30) NOT NULL Updated By
81 SCC_ROW_UPD_DTTM DateTime(26) TIMESTAMP Last Update Date/Time
82 SCC_EPAY_RET_MSG Character(254) VARCHAR2(254) NOT NULL Return Message
83 SSF_POST_TYPE Character(1) VARCHAR2(1) NOT NULL Post Type indicates whether the transaction is posted to the SF Account or to the General Ledger
A=Account Posting
E=External
G=GL Posting

Default Value: A

84 SSF_EXT_TRANS_REF Character(50) VARCHAR2(50) NOT NULL External Transaction Reference
85 SSF_PMT_SRC_STATUS Character(1) VARCHAR2(1) NOT NULL Payment Source Update Status
P=Pending Update
S=Successful Update

Default Value: P

86 SCC_PMT_APPL Character(15) VARCHAR2(15) NOT NULL Payment Application

Prompt Table: SSF_ELEC_PMTS

87 SCC_TEMP_ID Number(16,0) DECIMAL(16) NOT NULL Temp Constituent ID