PO_LN_ORDER_VW2

(SQL View)
 Index Back

PO Line Order View for SCMT

08/02/07 TBG F-AHAYT-X0ES6 created for PO Source transaction

SELECT L.BUSINESS_UNIT , L.PO_ID , L.LINE_NBR , L.CANCEL_STATUS , L.CHANGE_STATUS , L.INV_ITEM_ID , L.DESCR254_MIXED , L.UNIT_OF_MEASURE , L.QTY_TYPE , L.CNTRCT_ID , L.CNTRCT_LINE_NBR , L.AMT_ONLY_FLG , SUM(S.QTY_PO) , %Round(SUM(S.MERCHANDISE_AMT),3) , S.CURRENCY_CD , SUM(R.QTY_SH_NETRCV_VUOM) , %Round(%DecMult(SUM(S.QTY_PO)-SUM(R.QTY_SH_NETRCV_VUOM),(%DecDiv(SUM(S.MERCHANDISE_AMT),SUM(S.QTY_PO)))), 3) FROM PS_PO_LINE L , PS_PO_LINE_SHIP S , PS_RECV_LN_SHIP_FS R WHERE L.BUSINESS_UNIT = S.BUSINESS_UNIT AND R.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND R.PO_ID = S.PO_ID AND L.LINE_NBR = S.LINE_NBR AND R.LINE_NBR = S.LINE_NBR AND S.CANCEL_STATUS <> 'X' GROUP BY L.BUSINESS_UNIT, L.PO_ID, L.LINE_NBR, L.CANCEL_STATUS, L.CHANGE_STATUS, L.INV_ITEM_ID, L.DESCR254_MIXED, L.UNIT_OF_MEASURE, L.QTY_TYPE, L.CNTRCT_ID, L.CNTRCT_LINE_NBR, L.AMT_ONLY_FLG, S.CURRENCY_CD

  • Parent record: PO_HDR_FS

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
    3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
    4 CANCEL_STATUS Character(1) VARCHAR2(1) NOT NULL Cancel Status
    A=Active
    C=Closed
    D=Denied
    H=Hold
    O=Open
    P=Pending Approval
    X=Canceled
    5 CHANGE_STATUS Character(1) VARCHAR2(1) NOT NULL Change Order Status
    C=Changed
    U=Unchanged
    6 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
    7 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description
    8 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    9 QTY_TYPE Character(1) VARCHAR2(1) NOT NULL Quantity to Use for Prices
    L=Line Quantity
    S=Schedule Quantity
    10 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID
    11 CNTRCT_LINE_NBR Number(5,0) INTEGER NOT NULL Buying Agreement Line Nbr
    12 AMT_ONLY_FLG Character(1) VARCHAR2(1) NOT NULL Amount Only
    N=No
    Y=Yes
    13 QTY_PO Number(16,4) DECIMAL(15,4) NOT NULL Purchase Order Quantity
    14 AMT_ORDERED Number(27,3) DECIMAL(26,3) NOT NULL Amount Ordered
    15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    16 QTY_SH_NETRCV_VUOM Number(16,4) DECIMAL(15,4) NOT NULL Net Receipt Quantity
    17 AMT_OPEN Signed Number(28,3) DECIMAL(26,3) NOT NULL Open Amount