ITM_CAT_DTL_VW

(SQL View)
 Index Back

Purchased Items View


SELECT A.SETID ,A.INV_ITEM_ID ,C.UNIT_MEASURE_STD ,A.INV_ITEM_WEIGHT ,A.INV_ITEM_LENGTH ,A.INV_ITEM_HEIGHT ,A.INV_ITEM_WIDTH ,A.INV_ITEM_VOLUME ,A.UNIT_MEASURE_DIM ,A.UNIT_MEASURE_VOL ,A.UNIT_MEASURE_WT ,C.CATEGORY_ID ,B.DESCR ,B.DESCRSHORT ,B.ROUTING_ID ,B.ACCOUNT , B.DEPTID ,%subrec(CF27_AN_SBR, B) ,B.PROJECT_ID ,B.UNIT_PRC_TOL ,B.EXT_PRC_TOL ,B.PCT_UNIT_PRC_TOL ,B.PCT_EXT_PRC_TOL ,B.TAXABLE_CD ,B.QTY_RECV_TOL_PCT ,B.RJCT_OVER_TOL_FLAG ,B.RFQ_REQ_FLAG ,B.REJECT_DAYS ,B.INSPECT_CD ,B.AUTO_SOURCE ,B.RECV_REQ ,B.PRIMARY_BUYER ,B.STD_LEAD ,B.PRICE_LIST ,B.MODEL ,B.PO_AVAIL_DT ,B.PO_UNAVAIL_DT ,B.DESCR254_MIXED ,B.CURRENCY_CD FROM PS_INV_ITEMS A , PS_PURCH_ITEM_ATTR B , PS_MASTER_ITEM_TBL C WHERE A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.SETID = C.SETID AND A.INV_ITEM_ID = C.INV_ITEM_ID AND C.ITM_STATUS_CURRENT = '1' AND A.EFFDT = ( SELECT MAX(EFFDT) FROM PS_INV_ITEMS C WHERE C.SETID = B.SETID AND C.INV_ITEM_ID = B.INV_ITEM_ID AND EFFDT <= %CurrentDateIn) AND ((%CurrentDateIn >= B.PO_AVAIL_DT) AND (B.PO_UNAVAIL_DT > %CurrentDateIn))

  • Related Language Record: PURCH_ITEM_LANG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

    Prompt Table: INV_ITEMS

    3 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
    4 INV_ITEM_WEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Weight
    5 INV_ITEM_LENGTH Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Length
    6 INV_ITEM_HEIGHT Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Height
    7 INV_ITEM_WIDTH Signed Number(17,4) DECIMAL(15,4) NOT NULL Item Width
    8 INV_ITEM_VOLUME Signed Number(17,4) DECIMAL(15,4) NOT NULL Volume
    9 UNIT_MEASURE_DIM Character(3) VARCHAR2(3) NOT NULL Dimension UOM
    10 UNIT_MEASURE_VOL Character(3) VARCHAR2(3) NOT NULL Volume UOM
    11 UNIT_MEASURE_WT Character(3) VARCHAR2(3) NOT NULL Weight UOM
    12 CATEGORY_ID Character(5) VARCHAR2(5) NOT NULL Category ID

    Prompt Table: ITM_CAT_VW

    13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    15 ROUTING_ID Character(10) VARCHAR2(10) NOT NULL Inspection Routing ID

    Prompt Table: ROUTING_HDR

    16 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account

    Prompt Table: GL_ACCOUNT_TBL

    17 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPT_TBL

    18 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

    Prompt Table: OPERUNT_NB_VW

    19 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

    Prompt Table: PRODUCT_NB_VW

    20 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

    Prompt Table: FUND_NB_VW

    21 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

    Prompt Table: CLASS_CF_NB_VW

    22 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

    Prompt Table: PROGRAM_NB_VW

    23 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

    Prompt Table: BUD_REF_NB_VW

    24 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

    Prompt Table: AFFILIATE_VW

    25 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

    Prompt Table: %EDIT_INTRA01

    26 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

    Prompt Table: %EDIT_INTRA02

    27 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

    Prompt Table: CF1_NB_VW

    28 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

    Prompt Table: CF2_NB_VW

    29 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

    Prompt Table: CF3_NB_VW

    30 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    31 UNIT_PRC_TOL Number(14,5) DECIMAL(13,5) NOT NULL Unit Price Tolerance
    32 EXT_PRC_TOL Number(14,5) DECIMAL(13,5) NOT NULL Ext Price Tolerance
    33 PCT_UNIT_PRC_TOL Number(6,2) DECIMAL(5,2) NOT NULL % Unit Price Tolerance
    34 PCT_EXT_PRC_TOL Number(6,2) DECIMAL(5,2) NOT NULL % Ext Price Tolerance
    35 TAXABLE_CD Character(1) VARCHAR2(1) NOT NULL Taxable or Non-Taxable

    Y/N Table Edit

    36 QTY_RECV_TOL_PCT Number(6,2) DECIMAL(5,2) NOT NULL Qty Rcvd Tolerance %
    37 RJCT_OVER_TOL_FLAG Character(1) VARCHAR2(1) NOT NULL Reject Qty Over Tolerance

    Y/N Table Edit

    38 RFQ_REQ_FLAG Character(1) VARCHAR2(1) NOT NULL RFQ Required

    Y/N Table Edit

    39 REJECT_DAYS Number(3,0) SMALLINT NOT NULL Early Ship Rjct Days
    40 INSPECT_CD Character(1) VARCHAR2(1) NOT NULL Inspection Required
    N=No
    Y=Yes

    Y/N Table Edit

    41 AUTO_SOURCE Character(1) VARCHAR2(1) NOT NULL Auto Select
    42 RECV_REQ Character(1) VARCHAR2(1) NOT NULL Receiving Required
    N=Optional
    X=Do Not
    Y=Required
    43 PRIMARY_BUYER Character(30) VARCHAR2(30) NOT NULL Primary Buyer
    44 STD_LEAD Number(3,0) SMALLINT NOT NULL Purchase Lead Time Days
    45 PRICE_LIST Number(16,5) DECIMAL(15,5) NOT NULL Standard Price
    46 MODEL Character(30) VARCHAR2(30) NOT NULL Model
    47 PO_AVAIL_DT Date(10) DATE PO Available Date
    48 PO_UNAVAIL_DT Date(10) DATE PO Unavailable Date
    49 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
    50 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL