ITM_CAT_SET_VW(SQL View) |
Index Back |
|---|---|
Category CodesContains all fields in the ITM_CAT_TBL, control field SETID |
| SELECT A.SETID , A.CATEGORY_TYPE , A.CATEGORY_CD , A.EFFDT , A.CATEGORY_ID , A.DESCR60 , A.DESCRSHORT , A.PROFILE_ID , A.NBR_OF_ITMS , A.LEAD_TIME , A.INSPECT_CD , A.INSPECT_UOM_TYPE , A.ROUTING_ID , A.REJECT_DAYS , A.ACCOUNT , A.ALTACCT , A.UNIT_PRC_TOL , A.PCT_UNIT_PRC_TOL , A.RECV_REQ , A.EXT_PRC_TOL , A.PCT_EXT_PRC_TOL , A.QTY_RECV_TOL_PCT , A.RJCT_OVER_TOL_FLAG , A.PRIMARY_BUYER , A.RECV_PARTIAL_FLG , A.COMMENTS_LONG , A.CURRENCY_CD , A.PCT_UNDER_QTY , A.SRC_METHOD , A.LEAD_TIME_IMP , A.PRICE_IMP , A.SHIPTO_PR_IMP , A.VNDR_PR_IMP , A.HIST_END_DT , A.HIST_NBR_MTHS , A.HIST_START_DT , A.HIST_START_MTH , A.MERCH_AMT_CAT_TOT , %subrec(PRC_TOL_L_SBR, A) , A.SHIP_LATE_DAYS , A.CUM_SRC_RUN_LEVEL FROM PS_ITM_CAT_TBL A WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_ITM_CAT_TBL A1 WHERE A1.SETID = A.SETID AND A1.CATEGORY_TYPE = A.CATEGORY_TYPE AND A1.CATEGORY_CD = A.CATEGORY_CD AND A1.CATEGORY_ID = A.CATEGORY_ID AND A1.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A' |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  SETID |
Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 2 | CATEGORY_TYPE |
Character(3) | VARCHAR2(3) NOT NULL |
Category Type
PSF=User Def UNS=UN/SPSC Default Value: INSTALLATION_PO.CATEGORY_TYPE |
| 3 | CATEGORY_CD |
Character(18) | VARCHAR2(18) NOT NULL | Category Code |
| 4 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 5 | CATEGORY_ID | Character(5) | VARCHAR2(5) NOT NULL | Category ID |
| 6 | DESCR60 | Character(60) | VARCHAR2(60) NOT NULL | Description |
| 7 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |
| 8 | PROFILE_ID | Character(10) | VARCHAR2(10) NOT NULL | Asset Profile ID |
| 9 | NBR_OF_ITMS | Number(5,0) | INTEGER NOT NULL | Number of Items |
| 10 | LEAD_TIME | Number(3,0) | SMALLINT NOT NULL | Lead Time Days |
| 11 | INSPECT_CD | Character(1) | VARCHAR2(1) NOT NULL |
Inspection Required
N=No Y=Yes Y/N Table Edit |
| 12 | INSPECT_UOM_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Inspection UOM
S=Standard V=Supplier |
| 13 | ROUTING_ID | Character(10) | VARCHAR2(10) NOT NULL | Inspection Routing ID |
| 14 | REJECT_DAYS | Number(3,0) | SMALLINT NOT NULL | Early Ship Rjct Days |
| 15 | ACCOUNT | Character(10) | VARCHAR2(10) NOT NULL | Account |
| 16 | ALTACCT | Character(10) | VARCHAR2(10) NOT NULL | Alternate Account |
| 17 | UNIT_PRC_TOL | Number(14,5) | DECIMAL(13,5) NOT NULL | Unit Price Tolerance |
| 18 | PCT_UNIT_PRC_TOL | Number(6,2) | DECIMAL(5,2) NOT NULL | % Unit Price Tolerance |
| 19 | RECV_REQ | Character(1) | VARCHAR2(1) NOT NULL |
Receiving Required
N=Optional X=Do Not Y=Required Y/N Table Edit |
| 20 | EXT_PRC_TOL | Number(14,5) | DECIMAL(13,5) NOT NULL | Ext Price Tolerance |
| 21 | PCT_EXT_PRC_TOL | Number(6,2) | DECIMAL(5,2) NOT NULL | % Ext Price Tolerance |
| 22 | QTY_RECV_TOL_PCT | Number(6,2) | DECIMAL(5,2) NOT NULL | Qty Rcvd Tolerance % |
| 23 | RJCT_OVER_TOL_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Reject Qty Over Tolerance
Y/N Table Edit |
| 24 | PRIMARY_BUYER | Character(30) | VARCHAR2(30) NOT NULL | Primary Buyer |
| 25 | RECV_PARTIAL_FLG | Character(1) | VARCHAR2(1) NOT NULL |
Partial Qty
1=Recvd 2=Recvd/Warn 3=Open 4=Open/Warn 5=Open/Error |
| 26 | COMMENTS_LONG | Long Character | CLOB | Comment Text |
| 27 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL |
Currency Code
Prompt Table: CURRENCY_CD_TBL |
| 28 | PCT_UNDER_QTY | Signed Number(4,0) | DECIMAL(3) NOT NULL | Close PO Under Qty Pct Tol |
| 29 | SRC_METHOD | Character(1) | VARCHAR2(1) NOT NULL |
Sourcing Method
B=Basic F=Flexible H=Cumulative Split S=Sched Split |
| 30 | LEAD_TIME_IMP | Number(6,2) | DECIMAL(5,2) NOT NULL | Lead Time Factor % |
| 31 | PRICE_IMP | Number(6,2) | DECIMAL(5,2) NOT NULL | Price Factor % |
| 32 | SHIPTO_PR_IMP | Number(6,2) | DECIMAL(5,2) NOT NULL | Ship to Priority Factor % |
| 33 | VNDR_PR_IMP | Number(6,2) | DECIMAL(5,2) NOT NULL | Vendor Priority Factor % |
| 34 | HIST_END_DT | Date(10) | DATE | End Date |
| 35 | HIST_NBR_MTHS | Number(3,0) | SMALLINT NOT NULL | Number of Months |
| 36 | HIST_START_DT | Date(10) | DATE | Historical Start Date |
| 37 | HIST_START_MTH | Number(2,0) | SMALLINT NOT NULL | Month to Start |
| 38 | MERCH_AMT_CAT_TOT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Total Category Amount |
| 39 | UNIT_PRC_TOL_L | Number(14,5) | DECIMAL(13,5) NOT NULL | Unit Price Tolerance - Under |
| 40 | PCT_UNIT_PRC_TOL_L | Number(6,2) | DECIMAL(5,2) NOT NULL | % Unit Price Tolerance - Under |
| 41 | EXT_PRC_TOL_L | Number(14,5) | DECIMAL(13,5) NOT NULL | Ext Price Tolerance - Under |
| 42 | PCT_EXT_PRC_TOL_L | Number(6,2) | DECIMAL(5,2) NOT NULL | % Ext Price Tolerance - Under |
| 43 | SHIP_LATE_DAYS | Number(4,0) | SMALLINT NOT NULL | Days Allowed After Request Dt |
| 44 | CUM_SRC_RUN_LEVEL | Character(1) | VARCHAR2(1) NOT NULL |
Accumulate Level
R=Run Level S=Schedule Level Default Value: R |