SCR_L_ID_CMP_VW

(SQL View)
 Index Back

SCR Loc 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 , a.vndr_loc , c.effdt , a.scr_effdt , a.setid , a.vendor_id , a.std_id_num_qual , (CASE WHEN a.std_id_num <> b.std_id_num THEN a.std_id_num ELSE c.std_id_num END) , (CASE WHEN a.std_id_setid <> b.std_id_setid THEN a.std_id_setid ELSE c.std_id_setid END) , (CASE WHEN a.credit_flg <> b.credit_flg THEN a.credit_flg ELSE c.credit_flg END) , a.scr_chg_eff_when , a.scr_chng_type , a.scr_stdid_num_qual , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.scr_remove FROM ps_scr_loc_id_dft a , PS_SCR_LOC_ID_MST b , ps_VNDR_LOC_ID_NBR 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.std_id_num_qual = b.std_id_num_qual 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.std_id_num_qual = c.std_id_num_qual AND %EffdtCheck(VNDR_LOC_ID_NBR d, c, %currentdatein) UNION SELECT a.scr_id , a.scr_seq_num , a.vndr_loc , a.effdt , a.scr_effdt , a.setid , a.vendor_id , a.std_id_num_qual , a.std_id_num , a.std_id_setid , a.credit_flg , a.scr_chg_eff_when , a.scr_chng_type , a.scr_stdid_num_qual , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.scr_remove FROM ps_scr_loc_id_dft a WHERE a.scr_chng_type = 'A' AND a.msg_severity <> 'E'

  • Parent record: VENDOR_LOC

    • # 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

    Default Value: %date

    5 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
    6 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    8 STD_ID_NUM_QUAL Character(3) VARCHAR2(3) NOT NULL Standard ID Number
    9 STD_ID_NUM Character(35) VARCHAR2(35) NOT NULL ID Number
    10 STD_ID_SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Prompt Table: SP_SETID_NONVW

    11 CREDIT_FLG Character(1) VARCHAR2(1) NOT NULL Credit Flag
    N=No
    Y=Yes

    Y/N Table Edit

    Default Value: N

    12 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
    I=Approval Date
    S=Future Date
    13 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
    A=Add
    D=Delete
    I=Inactivate
    U=Update
    14 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
    15 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
    16 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
    C=Cancel
    E=Error
    M=Message
    W=Warning
    17 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
    18 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
    19 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
    20 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.
    21 SCR_REMOVE Character(1) VARCHAR2(1) NOT NULL Remove Contact