SCR_PIVOT_VW

(SQL View)
 Index Back

SCR pivot record


SELECT A.scr_id , D.TYPE_CHANGE , D.ATTRIBUTE_LVL , A.SCR_STATUS , A.SETID , A.VENDOR_ID , G.VNDR_NAME_SHRT_USR , 'E' , CASE WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) > 60 THEN '6' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >=31 AND %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) <= 60 THEN '5' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >= 15 AND %DateDiff(%datepart(A.FMS_DTTM_STAMP), %currentdatein) <=30 THEN '4' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >=6 AND %DateDiff(%datepart(A.FMS_DTTM_STAMP), %currentdatein) <= 14 THEN '3' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >= 3 AND %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) <=5 THEN '2' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) <= 2 THEN '1' END , NEXT_DAY(TRUNC(A.fms_lastupddttm ,'IW') ,'SATURDAY') , to_char(NEXT_DAY(TRUNC(A.fms_lastupddttm ,'IW') ,'SATURDAY')) , TO_CHAR(A.fms_lastupddttm ,'YYYY') %Concat '-' %Concat TO_CHAR(A.fms_lastupddttm ,'MM') %Concat '-' %Concat TO_CHAR(A.fms_lastupddttm ,'MON') , 'Q' %Concat TO_CHAR(A.fms_lastupddttm ,'Q') ,TO_CHAR(A.fms_lastupddttm ,'YYYY') ,%Cast(%datepart(A.fms_lastupddttm),date, character) , F.OPRID %Concat ('-' %Concat F.OPRDEFNDESC) ,%DatePart(A.FMS_DTTM_STAMP) ,%DatePart(A.fms_lastupddttm) , A.fms_lastupddttm ,A.FMS_LASTUPDOPRID , A.FMS_DTTM_STAMP FROM ps_scr_header A , PS_SCR_change_type D , PSOPRdefn F , PS_VENDOR G WHERE A.SCR_ID = D.SCR_ID AND F.OPRID = A.FMS_OPRID AND A.SETID = G.SETID AND A.VENDOR_ID = G.VENDOR_ID AND EXISTS ( SELECT 'Y' FROM PSOPRALIAS C WHERE C.OPRID = A.FMS_LASTUPDOPRID AND C.OPRALIASTYPE = 'VND' ) UNION SELECT A.scr_id , D.TYPE_CHANGE , D.ATTRIBUTE_LVL , A.SCR_STATUS , A.SETID , A.VENDOR_ID , G.VNDR_NAME_SHRT_USR , 'I' , CASE WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) > 60 THEN '6' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >=31 AND %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) <= 60 THEN '5' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >= 15 AND %DateDiff(%datepart(A.FMS_DTTM_STAMP), %currentdatein) <=30 THEN '4' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >=6 AND %DateDiff(%datepart(A.FMS_DTTM_STAMP), %currentdatein) <= 14 THEN '3' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) >= 3 AND %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) <=5 THEN '2' WHEN %DateDiff( %datepart(A.FMS_DTTM_STAMP), %currentdatein) <= 2 THEN '1' END , NEXT_DAY(TRUNC(A.fms_lastupddttm ,'IW') ,'SATURDAY') , to_char(NEXT_DAY(TRUNC(A.fms_lastupddttm ,'IW') ,'SATURDAY')) , TO_CHAR(A.fms_lastupddttm ,'YYYY') %Concat '-' %Concat TO_CHAR(A.fms_lastupddttm ,'MM') %Concat '-' %Concat TO_CHAR(A.fms_lastupddttm ,'MON') , 'Q' %Concat TO_CHAR(A.fms_lastupddttm ,'Q') ,TO_CHAR(A.fms_lastupddttm ,'YYYY') ,%Cast(%datepart(A.fms_lastupddttm),date, character) , F.OPRID %Concat ('-' %Concat F.OPRDEFNDESC) ,%DatePart(A.FMS_DTTM_STAMP) ,%DatePart(A.fms_lastupddttm) , A.fms_lastupddttm ,A.FMS_LASTUPDOPRID , A.FMS_DTTM_STAMP FROM ps_scr_header A , PS_SCR_change_type D , PSOPRdefn F , PS_VENDOR G WHERE A.SCR_ID = D.SCR_ID AND F.OPRID = A.FMS_OPRID AND A.SETID = G.SETID AND A.VENDOR_ID = G.VENDOR_ID AND NOT EXISTS ( SELECT 'Y' FROM PSOPRALIAS C WHERE C.OPRID = A.FMS_LASTUPDOPRID AND C.OPRALIASTYPE = 'VND' )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SCR_ID Character(15) VARCHAR2(15) NOT NULL Request ID
2 TYPE_CHANGE Character(2) VARCHAR2(2) NOT NULL Type of Change
AD=Addresses
AR=Reporting Elements
AT=Attachments
BA=Bank Accounts
CA=Categorizations
CT=Contacts
DI=Dup. Invoice Settings
GC=Gov. Classifications
ID=ID Numbers
PP=Payment Preferences
PQ=Profile Questions
RA=Supplier Rating
SC=SIC Codes
SR=Sup. Relationships
VN=Company Profile
VT=Vat Registration
3 ATTRIBUTE_LVL Character(2) VARCHAR2(2) NOT NULL Attribute Level
AD=Addresses
CA=Categorization
CP=Company Profile
CT=Contacts
PP=Payment Profile
4 SCR_STATUS Character(1) VARCHAR2(1) NOT NULL Status
A=Approved
B=Request Editor More Info..
B=Request Editor More Info..
C=Pending Editor Review
C=Pending Editor Review
E=Update Error
I=Integrity Error
M=Request Additional Information
P=Pending Approval
R=Rejected
S=Save for Later
U=Database Updated
W=Withdrawn
5 SETID Character(5) VARCHAR2(5) NOT NULL SetID
6 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
7 VENDOR_NAME_SHORT Character(14) VARCHAR2(14) NOT NULL Short Vendor Name
8 SUBMITTER_TYPE Character(1) VARCHAR2(1) NOT NULL Submitter Type
E=External
I=Internal
9 BUCKET_BREAK Character(1) VARCHAR2(1) NOT NULL Aging Bucket Breakout
1=1-2 days
2=3-5 days
3=6-14 days
4=15-30 days
5=30-60 days
6=> 60 days
10 WEEKEND_DT Date(10) DATE WEEKEND
11 DATE_CHAR Character(26) VARCHAR2(26) NOT NULL Generic space field
12 MONTH_SCR Character(15) VARCHAR2(15) NOT NULL MONTH
13 QUARTER_SCR Character(2) VARCHAR2(2) NOT NULL Quarter
Q1=Quarter 1
Q2=Quarter 2
Q3=Quarter 3
Q4=Quarter 4
14 YEAR_SCR Character(4) VARCHAR2(4) NOT NULL YEAR
15 DATE_CHAR26 Character(26) VARCHAR2(26) NOT NULL Generic space field
16 REQUESTOR_NAM Character(70) VARCHAR2(70) NOT NULL Requestor Name
17 REQUEST_DATE Date(10) DATE Requested
18 LAST_UPDATE_DATE Date(10) DATE Date of last update
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 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.