TD_PROMO_RETCR(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 , G.RMA_LINE_NBR , B.SEQ_NUM , 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 , I.INVOICE , I.INVOICE_LINE 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 , PS_BI_HDR H , PS_BI_LINE I 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 AND H.BUSINESS_UNIT = I.BUSINESS_UNIT AND H.INVOICE = I.INVOICE AND I.PPRC_PROMO_CD = E.PPRC_PROMO_CD AND I.MERCH_TYPE = B.MERCH_TYPE 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, I.INVOICE , I.INVOICE_LINE, G.RMA_LINE_NBR , B.SEQ_NUM |
| # | 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 | RMA_LINE_NBR |
Number(5,0) | INTEGER NOT NULL | RMA Line Number |
| 6 | SEQ_NUM |
Number(3,0) | SMALLINT NOT NULL | Sequence |
| 7 | SOLD_TO_CUST_ID |
Character(15) | VARCHAR2(15) NOT NULL |
Sold To Customer
Prompt Table: CUST_SLD_VW |
| 8 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_OM_NONVW |
| 9 | QTY_RETURNED | Number(16,4) | DECIMAL(15,4) NOT NULL | Qty Returned |
| 10 | 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 |
| 11 | OM_AMOUNT | Signed Number(19,4) | DECIMAL(17,4) NOT NULL | Amount |
| 12 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 13 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
| 14 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL |
Item ID
Prompt Table: RMA_ITM_INV_VW |
| 15 | EST_RETURN_DTTM | DateTime(26) | TIMESTAMP | Estimated Return DateTime |
| 16 | RECEIPT_DT | Date(10) | DATE | Received Date |
| 17 | INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Invoice |
| 18 | INVOICE_LINE | Number(5,0) | INTEGER NOT NULL | Invoice Line |