SCR_EMAIL_CMP_V

(SQL View)
 Index Back

SCR Payment Method Compare Vw

This view was created to eliminate the possibility of stale data. It compares the Master, Copy of Master at time of save, and the Draft tables to create data for reloading the pages. Each field will populated from the Draft table if changed or the Master record if it hasn't been changed.

SELECT a.SCR_ID , a.SCR_SEQ_NUM , a.SETID , a.VENDOR_ID , a.VNDR_LOC , c.EFFDT , a.SCR_EFFDT , a.SEQ_NUM , a.PYMNT_METHOD , a.SCR_CHG_EFF_WHEN , a.SCR_PAYMENT_METHOD , a.SCR_CHNG_TYPE , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.SCR_REMOVE FROM PS_SCR_EMAIL_DFT a , PS_SCR_EMAIL_MST b , PS_EMAIL_PAY_MTHD c WHERE a.scr_id = b.scr_id AND a.scr_seq_num = b.scr_seq_num AND a.setid = b.setid AND a.vendor_id = b.vendor_id AND a.VNDR_LOC = b.VNDR_LOC AND a.effdt = b.effdt AND a.seq_num = b.seq_num AND a.pymnt_method = b.pymnt_method AND a.scr_chng_type = b.scr_chng_type AND (a.scr_chng_type = 'U' OR a.scr_chng_type = 'D') AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.VNDR_LOC = c.VNDR_LOC AND b.seq_num = c.seq_num AND b.pymnt_method = c.pymnt_method AND %EffdtCheck(EMAIL_PAY_MTHD d, c, %currentdatein) UNION SELECT a.SCR_ID , a.SCR_SEQ_NUM , a.SETID , a.VENDOR_ID , a.VNDR_LOC , a.EFFDT , a.SCR_EFFDT , a.SEQ_NUM , a.PYMNT_METHOD , a.SCR_CHG_EFF_WHEN , a.SCR_PAYMENT_METHOD , a.SCR_CHNG_TYPE , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.SCR_REMOVE FROM PS_SCR_EMAIL_DFT a WHERE a.scr_chng_type = 'A' AND a.msg_severity <> 'E'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SCR_ID Character(15) VARCHAR2(15) NOT NULL Supplier Change Request ID
2 SCR_SEQ_NUM Number(5,0) INTEGER NOT NULL Update Sequence Number
3 SETID Character(5) VARCHAR2(5) NOT NULL SetID
4 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
5 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
6 EFFDT Date(10) DATE Effective Date
7 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
8 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence

Default Value: 1

9 PYMNT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
D=Deposit
DD=Direct Debit
DFT=Draft - Supplier Initiated
DRA=Draft
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer

Prompt Table: PYCYCL_MTHD_VW

10 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
11 SCR_PAYMENT_METHOD Character(3) VARCHAR2(3) NOT NULL Payment Method
ACH=Automated Clearing House
BEF=Draft - Customer EFT
BOO=Draft - Customer Initiated
CHK=System Check
DD=Direct Debit
DFT=Draft - Supplier Initiated
EFT=Electronic Funds Transfer
GE=Giro - EFT
GM=Giro - Manual
LC=Letter of Credit
MAN=Manual Check
TRW=Treasury Wire
WIR=Wire Transfer
12 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
13 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
14 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
15 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
16 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
17 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
18 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.
19 SCR_REMOVE Character(1) VARCHAR2(1) NOT NULL Remove Contact