TD_ALL_RETURN

(SQL View)
 Index Back

All returns for a promotion


SELECT DISTINCT %Substring(D.SETCNTRLVALUE,1,5) , A.SOLD_TO_CUST_ID , A.BUSINESS_UNIT , A.RMA_ID , SUM(C.QTY_INV_RECV) , C.UNIT_OF_MEASURE , G.CURRENCY_CD , G.PRODUCT_ID , G.INV_ITEM_ID , G.EST_RETURN_DTTM FROM PS_RMA_HEADER A , PS_INV_RECV_LN C , PS_SET_CNTRL_REC D , PS_INV_RECV_HDR F , PS_RMA_LINE G WHERE A.BUSINESS_UNIT = G.BUSINESS_UNIT AND A.RMA_ID = G.RMA_ID AND C.RMA_ID <> ' ' AND C.SOURCE_BUS_UNIT = G.BUSINESS_UNIT AND A.RMA_ID = C.RMA_ID AND G.RMA_LINE_NBR = C.RMA_LINE_NBR AND D.RECNAME = 'PPRC_PROMO_TBL' AND F.INV_RECV_STATUS <> 'P' AND F.BUSINESS_UNIT = C.BUSINESS_UNIT AND F.RECEIVER_ID = C.RECEIVER_ID GROUP BY D.SETCNTRLVALUE, A.SOLD_TO_CUST_ID, A.RMA_ID, G.RMA_LINE_NBR, A.BUSINESS_UNIT, C.UNIT_OF_MEASURE, G.QTY_RETURNED, G.UNIT_OF_MEASURE, G.CURRENCY_CD, G.PRODUCT_ID, G.INV_ITEM_ID, G.EST_RETURN_DTTM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD_VW

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_OM_NONVW

4 RMA_ID Character(10) VARCHAR2(10) NOT NULL RMA Number
5 QTY_RETURNED Number(16,4) DECIMAL(15,4) NOT NULL Qty Returned
6 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage

Prompt Table: INV_ORDR_UOM_VW

7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
8 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
9 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: RMA_ITM_INV_VW

10 EST_RETURN_DTTM DateTime(26) TIMESTAMP Estimated Return DateTime