ITM_CAT_TBL_SVW

(SQL View)
 Index Back

Item Categories Search View

For APRD R-NBISPH-4B28T, this view is replacing ITM_CAT_TBL_SRC used as the search record by ITEM_CATEGORIES component.

SELECT A.SETID , A.CATEGORY_CD , A.CATEGORY_TYPE , A.CATEGORY_ID , A.DESCR60 , A.DESCRSHORT FROM PS_ITM_CAT_TBL A WHERE A.EFFDT = ( SELECT MAX(L.EFFDT) FROM PS_ITM_CAT_TBL L WHERE L.SETID = A.SETID AND L.CATEGORY_CD = A.CATEGORY_CD AND L.EFFDT <= %CurrentDateIn) UNION SELECT A.SETID , A.CATEGORY_CD , A.CATEGORY_TYPE , A.CATEGORY_ID , A.DESCR60 , A.DESCRSHORT FROM PS_ITM_CAT_TBL A WHERE A.EFFDT = ( SELECT L1.EFFDT FROM PS_ITM_CAT_TBL L1 WHERE L1.SETID = A.SETID AND L1.CATEGORY_CD = A.CATEGORY_CD AND NOT EXISTS ( SELECT 'X' FROM PS_ITM_CAT_TBL X WHERE X.SETID = A.SETID AND X.CATEGORY_CD = A.CATEGORY_CD AND X.CATEGORY_ID = A.CATEGORY_ID AND X.EFFDT = ( SELECT MAX(L.EFFDT) FROM PS_ITM_CAT_TBL L WHERE L.SETID = X.SETID AND L.CATEGORY_CD = X.CATEGORY_CD AND L.EFFDT <= %CurrentDateIn)) AND L1.EFFDT > %CurrentDateIn AND L1.EFFDT = ( SELECT MIN(L2.EFFDT) FROM PS_ITM_CAT_TBL L2 WHERE L2.SETID = L1.SETID AND L2.CATEGORY_CD = L1.CATEGORY_CD))

  • Related Language Record: ITM_CAT_SVW_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

    2 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code
    3 CATEGORY_TYPE Character(3) VARCHAR2(3) NOT NULL Category Type
    PSF=User Def
    UNS=UN/SPSC

    Default Value: INSTALLATION_PO.CATEGORY_TYPE

    4 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID

    Default Value: NEXT

    5 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description
    6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description