CRCARD_HST_VW

(SQL View)
 Index Back

Billing CR Card Hist

Map CRCARD_HST to look like CRCARD_AR_HST. There should be only one CRCARD_PAYMENT for each CRCARD_HST because Billing only pays one invoice (at header level) at a time. The join to ITEM is to make sure that the invoice has been loaded into AR. BU and CUST_ID and ITEM are from CRCARD_PAYMENT. BU in CRCARD_AR_HST can be BI BU or OM BU.

SELECT DISTINCT P.BUSINESS_UNIT , P.CUST_ID , P.ITEM , 0 , H.SEQ_NUM , H.HIST_SEQ , H.CR_CARD_NBR , H.CR_CARD_AUTH_STAT , H.CR_CARD_DIGITS , H.CR_CARD_TYPE , H.CURRENCY_CD , H.AMOUNT , H.CR_CARD_EXPMO , H.CR_CARD_EXPYR , H.CR_CARD_FNAME , H.CR_CARD_LNAME , %subrec(ADDRESS_SBR, H) , H.PHONE , H.EMAILID , H.CR_CARD_AUTH_CD , H.CR_CARD_RQST_ID , H.CR_CARD_A_DTTM , H.CR_CARD_MSG1 , H.CR_CARD_MSG2 , H.CR_CARD_MSG3 , H.CR_CARD_AUTH_DT , H.ADD_DTTM , H.LAST_MAINT_OPRID FROM PS_CRCARD_HST H , PS_CRCARD_PAYMENT P , PS_ITEM I WHERE H.CR_CARD_AUTH_STAT IN ('B', 'P', 'M') AND H.HIST_SEQ = P.HIST_SEQ AND I.BUSINESS_UNIT = P.BUSINESS_UNIT AND I.CUST_ID = P.CUST_ID AND I.ITEM = P.ITEM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
3 ITEM Character(30) VARCHAR2(30) NOT NULL Uniquely identifies an invoice credit memo or debit memo. The item ID is also called the invoice number.
4 ITEM_LINE Number(6,0) INTEGER NOT NULL Item Line
5 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence
6 HIST_SEQ Number(9,0) DECIMAL(9) NOT NULL History sequence number
7 CR_CARD_NBR Character(44) VARCHAR2(44) NOT NULL "See OM
8 CR_CARD_AUTH_STAT Character(1) VARCHAR2(1) NOT NULL Credit Card Auth Status
A=Authorized
B=Billed
C=Credited
D=Denied
E=Validation Errors
F=Auth Reversal
G=Validated
H=Auth Reversal Denied
J=Reauthorize
K=No Action
L=Credit Card Not In Group
M=Manually Approved/Settled
N=Change to Terms
P=Authorized and Billed
R=Processing
T=Cancel Order
U=Unprocessed/Retry
V=Pending Approval
W=Deleted after Settlement
X=Deleted
Y=Cancelled
Z=Zero Payment for Maintenance
9 CR_CARD_DIGITS Character(4) VARCHAR2(4) NOT NULL Card Number's Last Four Digits
10 CR_CARD_TYPE Character(3) VARCHAR2(3) NOT NULL "See OM
01=Visa
02=MasterCard
03=Diners Club/Carte Blanche
04=AMEX
05=Discover
06=EnRoute

Prompt Table: ORDENT_CCTYP_VW

11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
12 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
13 CR_CARD_EXPMO Character(2) VARCHAR2(2) NOT NULL Card Expiration Month
01=January
02=February
03=March
04=April
05=May
06=June
07=July
08=August
09=September
10=October
11=November
12=December
14 CR_CARD_EXPYR Character(4) VARCHAR2(4) NOT NULL Card Expiration Year
2011=2011
2012=2012
2013=2013
2014=2014
2015=2015
2016=2016
2017=2017
2018=2018
2019=2019
2020=2020
15 CR_CARD_FNAME Character(40) VARCHAR2(40) NOT NULL Credit Card First Name
16 CR_CARD_LNAME Character(40) VARCHAR2(40) NOT NULL Credit Card Last Name
17 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

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

Prompt Table: %EDIT_STATE

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

Y/N Table Edit

34 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
35 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
36 CR_CARD_AUTH_CD Character(10) VARCHAR2(10) NOT NULL Credit Card Auth Code
37 CR_CARD_RQST_ID Character(30) VARCHAR2(30) NOT NULL Credit Card Request ID
38 CR_CARD_A_DTTM Character(20) VARCHAR2(20) NOT NULL Cr Card Auth Date/Time
39 CR_CARD_MSG1 Character(50) VARCHAR2(50) NOT NULL Credit Card Message 1
40 CR_CARD_MSG2 Character(50) VARCHAR2(50) NOT NULL Credit Card Message 2
41 CR_CARD_MSG3 Character(50) VARCHAR2(50) NOT NULL Credit Card Message 3
42 CR_CARD_AUTH_DT Date(10) DATE Credit Card Authorization Date
43 ADD_DTTM DateTime(26) TIMESTAMP Date Time Added
44 LAST_MAINT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By Operator ID