ITM_PURCH_VW2

(SQL View)
 Index Back

Purchased Items View

Collects item information from INV_ITEMS, MASTER_ITEM_TBL, PURCH_ITEM_ATTR, and ITM_CAT_TBL

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 ,D.CATEGORY_CD ,C.CATEGORY_ID ,B.DESCR ,B.DESCRSHORT ,B.DESCR254_MIXED ,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.PCT_UNDER_QTY ,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.CURRENCY_CD ,C.PHYSICAL_NATURE FROM PS_INV_ITEMS A , PS_PURCH_ITEM_ATTR B , PS_MASTER_ITEM_TBL C , PS_ITM_CAT_TBL D 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 D.EFF_STATUS = 'A' AND ((%CurrentDateIn >= B.PO_AVAIL_DT) AND (B.PO_UNAVAIL_DT > %CurrentDateIn)) AND A.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_INV_ITEMS E WHERE E.SETID = B.SETID AND E.INV_ITEM_ID = B.INV_ITEM_ID AND E.EFFDT <= %CurrentDateIn) AND C.SETID = D.SETID AND C.CATEGORY_ID = D.CATEGORY_ID

  • 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

    Default Value: OPR_DEF_TBL_FS.SETID

    Prompt Table: SP_SETID_NONVW

    2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

    Prompt Table: MST_ITM_VW

    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 CATEGORY_CD Character(18) VARCHAR2(18) NOT NULL Category Code

    Prompt Table: ITM_CAT_VW

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

    Prompt Table: ITM_CAT_VW

    10 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    11 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    12 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
    13 ROUTING_ID Character(10) VARCHAR2(10) NOT NULL Inspection Routing ID

    Prompt Table: ROUTING_HDR

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

    Prompt Table: GL_ACCOUNT_TBL

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

    Prompt Table: DEPT_TBL

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

    Prompt Table: OPERUNT_NB_VW

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

    Prompt Table: PRODUCT_NB_VW

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

    Prompt Table: FUND_NB_VW

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

    Prompt Table: CLASS_CF_NB_VW

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

    Prompt Table: PROGRAM_NB_VW

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

    Prompt Table: BUD_REF_NB_VW

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

    Prompt Table: AFFILIATE_VW

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

    Prompt Table: %EDIT_INTRA01

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

    Prompt Table: %EDIT_INTRA02

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

    Prompt Table: CF1_NB_VW

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

    Prompt Table: CF2_NB_VW

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

    Prompt Table: CF3_NB_VW

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

    Y/N Table Edit

    Default Value: N

    34 QTY_RECV_TOL_PCT Number(6,2) DECIMAL(5,2) NOT NULL Qty Rcvd Tolerance %
    35 PCT_UNDER_QTY Signed Number(4,0) DECIMAL(3) NOT NULL Close PO Under Qty Pct Tol
    36 RJCT_OVER_TOL_FLAG Character(1) VARCHAR2(1) NOT NULL Reject Qty Over Tolerance

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

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

    Y/N Table Edit

    Default Value: N

    40 AUTO_SOURCE Character(1) VARCHAR2(1) NOT NULL Auto Select
    41 RECV_REQ Character(1) VARCHAR2(1) NOT NULL Receiving Required
    N=Optional
    X=Do Not
    Y=Required
    42 PRIMARY_BUYER Character(30) VARCHAR2(30) NOT NULL Primary Buyer
    43 STD_LEAD Number(3,0) SMALLINT NOT NULL Purchase Lead Time Days
    44 PRICE_LIST Number(16,5) DECIMAL(15,5) NOT NULL Standard Price
    45 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

    Prompt Table: CURRENCY_CD_TBL

    46 PHYSICAL_NATURE Character(1) VARCHAR2(1) NOT NULL Physical Nature
    G=Goods
    S=Services