OM_CCARD_HST_SR

(SQL View)
 Index Back

Credit Card History Search Rec

Credit Card History Search Record

SELECT CC.BUSINESS_UNIT , CC.ORDER_NO , CC.INVOICE , CC.CR_CARD_NBR , CC.CR_CARD_AUTH_STAT , OH.SOLD_TO_CUST_ID , C1.NAME1 , OH.BILL_TO_CUST_ID , C2.NAME1 FROM %Table(CRCARD_HST) CC , %Table(ORD_HEADER) OH , %Table(CUSTOMER) C1 , %Table(CUSTOMER) C2 WHERE CC.BUSINESS_UNIT = OH.BUSINESS_UNIT AND CC.ORDER_NO = OH.ORDER_NO AND C1.SETID = ( SELECT SCR.SETID FROM %Table(SET_CNTRL_REC) SCR WHERE SCR.SETCNTRLVALUE = OH.BUSINESS_UNIT AND SCR.RECNAME = 'CUSTOMER') AND C1.CUST_ID = OH.SOLD_TO_CUST_ID AND C2.SETID = C1.SETID AND C2.CUST_ID = OH.BILL_TO_CUST_ID UNION SELECT CC.BUSINESS_UNIT , CC.ORDER_NO , CC.INVOICE , CC.CR_CARD_NBR , CC.CR_CARD_AUTH_STAT , ' ' , ' ' , BH.BILL_TO_CUST_ID , C2.NAME1 FROM %Table(CRCARD_HST) CC , %Table(BI_HDR) BH , %Table(CUSTOMER) C2 WHERE CC.BUSINESS_UNIT = BH.BUSINESS_UNIT AND CC.INVOICE = BH.INVOICE AND C2.SETID = ( SELECT SCR.SETID FROM %Table(SET_CNTRL_REC) SCR WHERE SCR.SETCNTRLVALUE = BH.BUSINESS_UNIT AND SCR.RECNAME = 'CUSTOMER') AND C2.CUST_ID = BH.BILL_TO_CUST_ID AND CC.ORDER_NO = ' ' ORDER BY 1, 2, 3

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_OM.BUSINESS_UNIT

Prompt Table: SP_BU_OM_NONVW

2 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
3 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
4 CR_CARD_NBR Character(44) VARCHAR2(44) NOT NULL "See OM
5 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
6 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD1_VW

7 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
8 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer

Prompt Table: CUST_BLL1_VW

9 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2