SCR_IDNBR_CMP_V

(SQL View)
 Index Back

SCR ID Number Compare View

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 , c.STD_ID_NUM_QUAL , a.SCR_STDID_NUM_QUAL , a.SCR_CHNG_TYPE , c.SETID , c.VENDOR_ID , c.STD_ID_SETID , (CASE WHEN a.STD_ID_NUM <> b.STD_ID_NUM THEN a.STD_ID_NUM ELSE c.STD_ID_NUM END) , (CASE WHEN a.CREDIT_FLG <> b.CREDIT_FLG THEN a.CREDIT_FLG ELSE c.CREDIT_FLG END) , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_ID_NBRS_DFT a , PS_SCR_ID_NBRS_MST b , ps_VENDOR_ID_NBRS c WHERE a.scr_id = b.scr_id AND a.scr_seq_num = b.scr_seq_num AND a.std_id_num_qual = b.std_id_num_qual AND b.setid = c.setid AND b.vendor_id = c.vendor_id AND b.std_id_num_qual = c.std_id_num_qual UNION SELECT a.SCR_ID , a.SCR_SEQ_NUM , a.STD_ID_NUM_QUAL , a.SCR_STDID_NUM_QUAL , a.SCR_CHNG_TYPE , a.SETID , a.VENDOR_ID , a.STD_ID_SETID , a.STD_ID_NUM , a.CREDIT_FLG , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_ID_NBRS_DFT a WHERE a.scr_chng_type = 'A'

# 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 STD_ID_NUM_QUAL Character(3) VARCHAR2(3) NOT NULL Standard ID Number
4 SCR_STDID_NUM_QUAL Character(3) VARCHAR2(3) NOT NULL Standard ID Number
ACN=Company Number - Australia
BEI=BEI Identifier
BUS=Business Types
CCM=Municipal Reg Number - Brazil
CGC=Comp Tax Reg Number - Brazil
CID=Company Identification Number
CPF=Ind Tax Reg Number - Brazil
CRI=Creditor Identification
DB=DUNS Number (With Edit)
DNS=DUNS Number (No Edit)
DOD=DODAAC Code
DP4=DUNS+4 Suffix
EIN=Employer ID Number
FIC=Fiscal ID - Company - Italy
FII=Fiscal ID - Indiv - Italy
GLN=Global Location Number
IE=State Reg Number - Brazil
LER=Legal Entity Registration Num
NIC=NIC Number - France
NIF=Fiscal ID Number - Spain
PSC=Public Service Codes
SBA=SBA Business Types
SRN=SIREN Number - France
SRT=SIRET Number - France
SSN=Social Security Number
TIN=Tax Identification Number
W2=W2 Identification Number
5 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
6 SETID Character(5) VARCHAR2(5) NOT NULL SetID
7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
8 STD_ID_SETID Character(5) VARCHAR2(5) NOT NULL SetID
9 STD_ID_NUM Character(35) VARCHAR2(35) NOT NULL ID Number
10 CREDIT_FLG Character(1) VARCHAR2(1) NOT NULL Credit Flag
N=No
Y=Yes
11 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
12 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
13 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
14 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
15 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
16 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.