SCR_LOC_CMP_VW

(SQL View)
 Index Back

SCR Location 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.scr_chg_eff_when , a.scr_chng_type , a.default_loc , (CASE WHEN a.primary_vendor <> b.primary_vendor THEN a.primary_vendor ELSE c.primary_vendor END) , (CASE WHEN a.prim_addr_seq_num <> b.prim_addr_seq_num THEN a.prim_addr_seq_num ELSE c.prim_addr_seq_num END) , (CASE WHEN a.remit_setid <> b.remit_setid THEN a.remit_setid ELSE c.remit_setid END) , (CASE WHEN a.remit_vendor <> b.remit_vendor THEN a.remit_vendor ELSE c.remit_vendor END) , (CASE WHEN a.remit_loc <> b.remit_loc THEN a.remit_loc ELSE c.remit_loc END) , (CASE WHEN a.remit_addr_seq_num <> b.remit_addr_seq_num THEN a.remit_addr_seq_num ELSE c.remit_addr_seq_num END) , (CASE WHEN a.pymnt_terms_cd <> b.pymnt_terms_cd THEN a.pymnt_terms_cd ELSE c.pymnt_terms_cd END) , (CASE WHEN a.descr <> b.descr THEN a.descr ELSE e.descr END) , (CASE WHEN a.ccr_addr_type <> b.ccr_addr_type THEN a.ccr_addr_type ELSE e.ccr_addr_type END) , a.comments , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_LOC_SCR_DFT a , ps_scr_loc_scr_mst b , ps_VENDOR_LOC c , PS_VNDR_LOC_SCROL e 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.scr_chng_type = b.scr_chng_type 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 %EffdtCheck(VENDOR_LOC d, c, %currentdatein) AND c.setid = e.setid AND c.vendor_id = e.vendor_id AND c.vndr_loc = e.vndr_loc UNION ALL SELECT a.scr_id , a.scr_seq_num , a.vndr_loc , a.effdt , a.scr_effdt , a.setid , a.vendor_id , a.scr_chg_eff_when , a.scr_chng_type , a.default_loc , a.primary_vendor , a.prim_addr_seq_num , a.remit_setid , a.remit_vendor , a.remit_loc , a.remit_addr_seq_num , a.pymnt_terms_cd , a.descr , a.ccr_addr_type , a.comments , a.ERR_MSG_NBR , a.MSG_SEVERITY , %subrec(FMS_WHO_SBR, a) FROM PS_SCR_LOC_SCR_DFT a WHERE a.scr_chng_type IN ('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 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
4 EFFDT Date(10) DATE Effective 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 SCR_CHG_EFF_WHEN Character(1) VARCHAR2(1) NOT NULL Change Effective
I=Approval Date
S=Future Date
9 SCR_CHNG_TYPE Character(1) VARCHAR2(1) NOT NULL Request for Change Type
A=Add
D=Delete
I=Inactivate
U=Update
10 DEFAULT_LOC Character(10) VARCHAR2(10) NOT NULL Default Location
11 PRIMARY_VENDOR Character(10) VARCHAR2(10) NOT NULL Primary Supplier
12 PRIM_ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Invoicing Location
13 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
14 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
15 REMIT_LOC Character(10) VARCHAR2(10) NOT NULL Remit to Location
16 REMIT_ADDR_SEQ_NUM Number(5,0) INTEGER NOT NULL Remitting Location
17 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
18 DESCR Character(30) VARCHAR2(30) NOT NULL Description
19 CCR_ADDR_TYPE Character(3) VARCHAR2(3) NOT NULL SAM Address Type
20 COMMENTS Long Character CLOB Comment
21 ERR_MSG_NBR Number(5,0) INTEGER NOT NULL Service Purchase Error Number
22 MSG_SEVERITY Character(1) VARCHAR2(1) NOT NULL Message Severity
C=Cancel
E=Error
M=Message
W=Warning
23 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
24 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
25 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
26 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.