SCR_PAY_CMP_V

(SQL View)
 Index Back

SCR Payment Advice 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.vndr_loc , c.effdt , a.seq_num , a.scr_effdt , a.setid , a.vendor_id , a.scr_chg_eff_when , a.scr_chng_type , a.eff_status , (CASE WHEN a.pymnt_method <> b.pymnt_method THEN a.pymnt_method ELSE c.pymnt_method END) , (CASE WHEN a.email_advice <> b.email_advice THEN a.email_advice ELSE c.email_advice END) , (CASE WHEN a.remit_advise_opt <> b.remit_advise_opt THEN a.remit_advise_opt ELSE c.remit_advise_opt END) , (CASE WHEN a.emailid <> b.emailid THEN a.emailid ELSE c.emailid END) , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM ps_scr_vnd_pay_dft a , PS_SCR_VND_PAY_MST b , ps_VENDOR_PAY 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.scr_chng_type IN ('U','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 %EffdtCheck(VENDOR_PAY d, c, %currentdatein) UNION SELECT a.scr_id , a.scr_seq_num , a.vndr_loc , a.effdt , a.seq_num , a.scr_effdt , a.setid , a.vendor_id , a.scr_chg_eff_when , a.scr_chng_type , a.eff_status , a.pymnt_method , a.email_advice , a.remit_advise_opt , a.emailid , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM ps_scr_vnd_pay_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 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
4 EFFDT Date(10) DATE Effective Date
5 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence

Default Value: 1

6 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
7 SETID Character(5) VARCHAR2(5) NOT NULL SetID
8 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
9 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
10 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
11 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

12 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
13 EMAIL_ADVICE Character(1) VARCHAR2(1) NOT NULL Email Payment Advice
N=No
Y=Yes

Y/N Table Edit

Default Value: N

14 REMIT_ADVISE_OPT Character(1) VARCHAR2(1) NOT NULL Flag to be used for sending remittance advise through FG
15 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
16 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
17 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
18 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
19 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
20 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
21 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.