TD_PROD_SALE_VW

(SQL View)
 Index Back

Sales by Product


SELECT DISTINCT A.OPRID , A.SETID , D.PRODUCT_ID , D.DESCR , 0 , 0 , ' ' FROM PS_TD_SETID_TBL C , PS_TD_TREE_NODE_VW A , PS_PROD_ITEM D WHERE A.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.SETCNTRLVALUE = C.SETID AND S.RECNAME = 'PROD_ITEM') AND A.TD_TREE_ID = C.TD_TREE_ID_PROD AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_TD_TREE_NODE_VW A_ED WHERE A.SETID = A_ED.SETID AND A.TD_TREE_ID = A_ED.TD_TREE_ID AND A_ED.EFFDT <= %CurrentDateIn ) AND D.SETID = A.SETID AND A.PRODUCT_ID = D.PRODUCT_ID UNION ALL SELECT DISTINCT A.OPRID , A.SETID , D.PRODUCT_ID , D.DESCR , 0 , 0 , ' ' FROM PS_TD_SETID_TBL C , PS_TD_TREE_NODE_VW A , PS_PROD_ITEM D WHERE A.SETID = ( SELECT S.SETID FROM PS_SET_CNTRL_REC S WHERE S.SETCNTRLVALUE = C.SETID AND S.RECNAME = 'PROD_ITEM') AND A.TD_TREE_ID = C.TD_TREE_ID_PROD AND A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_TD_TREE_NODE_VW A_ED WHERE A.SETID = A_ED.SETID AND A.TD_TREE_ID = A_ED.TD_TREE_ID AND A_ED.EFFDT <= %CurrentDateIn) AND D.SETID = A.SETID AND A.PRODUCT_ID = D.PRODUCT_ID

  • Related Language Record: TD_PRODSALE_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    2 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    3 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    4 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    5 NET_SALES Signed Number(28,3) DECIMAL(26,3) NOT NULL Net Sales
    6 PLANNED_SALES Signed Number(28,3) DECIMAL(26,3) NOT NULL Planned Sales
    7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code