TD_PRO_RET_VW

(SQL View)
 Index Back

All returns for a promotion


SELECT DISTINCT %Substring(D.SETCNTRLVALUE,1,5) , B.PPRC_PROMO_CD , B.MERCH_TYPE , A.RMA_ID , A.SOLD_TO_CUST_ID , CASE WHEN SUM(C.QTY_INV_RECV) <= B.QTY_RETURNED THEN SUM(C.QTY_INV_RECV) WHEN B.QTY_RETURNED < SUM(C.QTY_INV_RECV) THEN B.QTY_RETURNED END , CASE WHEN SUM(C.QTY_INV_RECV) <= B.QTY_RETURNED THEN C.UNIT_OF_MEASURE WHEN B.QTY_RETURNED < SUM(C.QTY_INV_RECV) THEN G.UNIT_OF_MEASURE END , G.PRODUCT_ID , G.EST_RETURN_DTTM , C.UNIT_MEASURE_STD , C.RECEIPT_DT FROM PS_RMA_HEADER A , PS_TD_RMAPROMO_VW B , PS_TD_INVRECVLN_VW C , PS_SET_CNTRL_REC D , PS_PPRC_PROMO_TBL E , PS_INV_RECV_HDR F , PS_RMA_LINE G WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RMA_ID = B.RMA_ID AND C.RMA_ID <> ' ' AND C.SOURCE_BUS_UNIT = B.BUSINESS_UNIT AND A.RMA_ID = C.RMA_ID AND B.RMA_LINE_NBR = C.RMA_LINE_NBR AND D.RECNAME = 'PPRC_PROMO_TBL' AND D.SETCNTRLVALUE = E.SETID AND B.PPRC_PROMO_CD = E.PPRC_PROMO_CD AND F.INV_RECV_STATUS <> 'P' AND F.BUSINESS_UNIT = C.BUSINESS_UNIT AND F.RECEIVER_ID = C.RECEIVER_ID AND G.BUSINESS_UNIT = B.BUSINESS_UNIT AND G.RMA_ID = B.RMA_ID AND G.RMA_LINE_NBR = B.RMA_LINE_NBR GROUP BY D.SETCNTRLVALUE, B.PPRC_PROMO_CD, A.SOLD_TO_CUST_ID, B.MERCH_TYPE, A.RMA_ID, G.RMA_LINE_NBR, A.BUSINESS_UNIT, C.UNIT_OF_MEASURE, B.QTY_RETURNED, G.UNIT_OF_MEASURE, G.CURRENCY_CD, G.PRODUCT_ID, G.INV_ITEM_ID, G.EST_RETURN_DTTM, C.UNIT_MEASURE_STD, C.RECEIPT_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 PPRC_PROMO_CD Character(20) VARCHAR2(20) NOT NULL Price Promotion Code
3 MERCH_TYPE Character(10) VARCHAR2(10) NOT NULL Merchandising Type
4 RMA_ID Character(10) VARCHAR2(10) NOT NULL RMA Number
5 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD_VW

6 QTY_RETURNED Number(16,4) DECIMAL(15,4) NOT NULL Qty Returned
7 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

8 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
9 EST_RETURN_DTTM DateTime(26) TIMESTAMP Estimated Return DateTime
10 UNIT_MEASURE_STD Character(3) VARCHAR2(3) NOT NULL Standard Unit of Measure
11 RECEIPT_DT Date(10) DATE Received Date