TD_PROMO_RETRN3

(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 , A.BUSINESS_UNIT , CASE WHEN SUM(C.QTY_INV_RECV) <= SUM(B.QTY_RETURNED) THEN SUM(C.QTY_INV_RECV) WHEN SUM(B.QTY_RETURNED) < SUM(C.QTY_INV_RECV) THEN SUM(B.QTY_RETURNED) END , CASE WHEN SUM(C.QTY_INV_RECV) <= SUM(B.QTY_RETURNED) THEN C.UNIT_OF_MEASURE WHEN SUM(B.QTY_RETURNED) < SUM(C.QTY_INV_RECV) THEN G.UNIT_OF_MEASURE END , B.OM_AMOUNT , G.CURRENCY_CD , G.PRODUCT_ID , G.INV_ITEM_ID , G.EST_RETURN_DTTM , C.RECEIPT_DT FROM PS_RMA_HEADER A , PS_RMA_LINE_PROMO 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 E.PROMO_TYPE = 'P' 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, B.OM_AMOUNT , G.CURRENCY_CD, G.PRODUCT_ID, G.INV_ITEM_ID, G.EST_RETURN_DTTM, 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 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_OM_NONVW

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

9 OM_AMOUNT Signed Number(19,4) DECIMAL(17,4) NOT NULL Amount
10 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
11 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
12 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID

Prompt Table: RMA_ITM_INV_VW

13 EST_RETURN_DTTM DateTime(26) TIMESTAMP Estimated Return DateTime
14 RECEIPT_DT Date(10) DATE Received Date