SCR_L_SIC_CMP_V

(SQL View)
 Index Back

SCR Industry Codes Compare V

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.sic_cd_qual , a.sic_code , a.scr_effdt , a.setid , a.vendor_id , a.scr_sic_cd_qual , a.scr_sic_code , a.scr_chg_eff_when , a.scr_chng_type , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.scr_remove FROM PS_SCR_LOC_SIC_DFT a , PS_SCR_LOC_SIC_MST b , PS_VNDR_IND_CLASS 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.sic_cd_qual = b.sic_cd_qual AND a.sic_code = b.sic_code 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.sic_cd_qual = c.sic_cd_qual AND b.sic_code = c.sic_code AND %EffdtCheck(VNDR_IND_CLASS d, c, %currentdatein) UNION SELECT a.scr_id , a.scr_seq_num , a.vndr_loc , a.effdt , a.sic_cd_qual , a.sic_code , a.scr_effdt , a.setid , a.vendor_id , a.scr_sic_cd_qual , a.scr_sic_code , a.scr_chg_eff_when , a.scr_chng_type , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) , a.scr_remove FROM PS_SCR_LOC_SIC_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
    5 SIC_CD_QUAL Character(3) VARCHAR2(3) NOT NULL SIC Code Type
    ANZ=Australia-New Zealand SIC Code
    CAN=Canadian NAICS Codes
    GER=German SIC Codes
    NAF=French SIC Codes
    SIC=US SIC Codes
    UK=UK 92 SIC Codes
    USA=US - NAICS Codes
    USE=NAICS Exception Codes
    USP=Primary NAICS Codes
    6 SIC_CODE Character(10) VARCHAR2(10) NOT NULL Standard Industry Code

    Prompt Table: SIC_CODE_VW2

    7 SCR_EFFDT Date(10) DATE Approved Changes Take Effect
    8 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Prompt Table: SETID_TBL

    9 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
    10 SCR_SIC_CD_QUAL Character(3) VARCHAR2(3) NOT NULL SIC Code Type
    ANZ=Australia-New Zealand SIC Code
    CAN=Canadian NAICS Codes
    GER=German SIC Codes
    NAF=French SIC Codes
    SIC=US SIC Codes
    UK=UK 92 SIC Codes
    USA=US - NAICS Codes
    USE=NAICS Exception Codes
    USP=Primary NAICS Codes
    11 SCR_SIC_CODE Character(10) VARCHAR2(10) NOT NULL Standard Industry Code
    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 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
    15 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
    C=Cancel
    E=Error
    M=Message
    W=Warning
    16 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
    17 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
    18 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
    19 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.
    20 SCR_REMOVE Character(1) VARCHAR2(1) NOT NULL Remove Contact