TD_PROMOSHIP_VW(SQL View) |
Index Back |
|---|---|
All shipments for a promotion |
| SELECT DISTINCT %Substring(D.SETCNTRLVALUE,1,5) , B.PPRC_PROMO_CD , A.SOLD_TO_CUST_ID , A.BUSINESS_UNIT , A.ORDER_NO , B.ORDER_INT_LINE_NO , B.SCHED_LINE_NBR , C.DEMAND_LINE_NO , A.BILL_TO_CUST_ID , C.SHIP_TO_CUST_ID , C.INV_ITEM_ID , C.SHIP_DTTM , C.SCHED_ARRV_DTTM , B.CURRENCY_CD , C.QTY_SHIPPED , C.UNIT_MEASURE_SHIP , C.BILL_OF_LADING , F.INVOICE , C.SHIP_ID , C.QTY_REQUESTED , C.UNIT_OF_MEASURE , ' ' , ' ' , 0 , 0 FROM PS_ORD_HEADER A , PS_ORD_PRICE_ADJST B , PS_IN_DEMAND C , PS_SET_CNTRL_REC D , PS_PPRC_PROMO_TBL E , PS_IN_DEMAND_BI F WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ORDER_NO = B.ORDER_NO AND B.BUSINESS_UNIT = C.SOURCE_BUS_UNIT AND C.DEMAND_SOURCE = 'OM' AND C.IN_FULFILL_STATE = '70' AND B.ORDER_NO = C.ORDER_NO AND B.ORDER_INT_LINE_NO = C.ORDER_INT_LINE_NO AND D.RECNAME = 'PPRC_PROMO_TBL' AND D.SETCNTRLVALUE = E.SETID AND B.PPRC_PROMO_CD = E.PPRC_PROMO_CD AND B.PPRC_PROMO_CD <> ' ' AND C.BUSINESS_UNIT = F.BUSINESS_UNIT AND C.DEMAND_SOURCE = F.DEMAND_SOURCE AND C.SOURCE_BUS_UNIT = F.SOURCE_BUS_UNIT AND C.ORDER_NO = F.ORDER_NO AND C.ORDER_INT_LINE_NO = F.ORDER_INT_LINE_NO AND C.SCHED_LINE_NBR = F.SCHED_LINE_NBR AND C.SCHED_LINE_NBR = B.SCHED_LINE_NBR AND C.INV_ITEM_ID = F.INV_ITEM_ID AND C.DEMAND_LINE_NO = F.DEMAND_LINE_NO UNION SELECT DISTINCT G.SETCNTRLVALUE , B.PPRC_PROMO_CD , D.SOLD_TO_CUST_ID , C.REF_BUS_UNIT_OM , C.REF_ORDER_NO , C.REF_LINE_NBR , C.REF_SCHED_NBR , C.REF_DEMD_LINE_NBR , D.BILL_TO_CUST_ID , C.RETURN_FR_CUST_ID , C.REF_INV_ITEM_ID , A.RECEIPT_DTTM , A.RECEIPT_DTTM , C.CURRENCY_CD , (-1*CASE WHEN SUM(A.QTY_INV_RECV) <= B.QTY_RETURNED THEN SUM(A.QTY_INV_RECV) WHEN B.QTY_RETURNED < SUM(A.QTY_INV_RECV) THEN B.QTY_RETURNED END) , CASE WHEN SUM(A.QTY_INV_RECV) <= B.QTY_RETURNED THEN A.UNIT_OF_MEASURE WHEN B.QTY_RETURNED < SUM(A.QTY_INV_RECV) THEN C.UNIT_OF_MEASURE END , ' ' , E.INVOICE , ' ' , (-1*B.QTY_RETURNED) , C.UNIT_OF_MEASURE , ' ' , B.RMA_ID , B.RMA_LINE_NBR , B.SEQ_NUM FROM PS_INV_RECV_LN A , PS_RMA_LINE_PROMO B , PS_RMA_LINE C , PS_RMA_HEADER D , PS_BI_LINE E , PS_TD_CLAIMS_PAY F , PS_SET_CNTRL_REC G , PS_PPRC_PROMO_TBL H WHERE A.RMA_ID <> ' ' AND G.RECNAME = 'PPRC_PROMO_TBL' AND G.SETCNTRLVALUE = H.SETID AND A.SOURCE_BUS_UNIT = B.BUSINESS_UNIT AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.RMA_ID = C.RMA_ID AND B.RMA_LINE_NBR = C.RMA_LINE_NBR AND B.RMA_ID = D.RMA_ID AND B.BUSINESS_UNIT = D.BUSINESS_UNIT AND E.PPRC_PROMO_CD = B.PPRC_PROMO_CD AND E.MERCH_TYPE = E.MERCH_TYPE AND E.PPRC_PROMO_CD = F.PPRC_PROMO_CD AND E.MERCH_TYPE = F.MERCH_TYPE AND F.RMA_ID = B.RMA_ID AND F.CUST_ID = D.BILL_TO_CUST_ID AND H.PPRC_PROMO_CD = B.PPRC_PROMO_CD AND C.REF_INV_ITEM_ID = A.INV_ITEM_ID GROUP BY B.PPRC_PROMO_CD, D.SOLD_TO_CUST_ID, B.MERCH_TYPE, C.REF_BUS_UNIT_OM, C.REF_ORDER_NO, C.REF_LINE_NBR, C.REF_SCHED_NBR, C.REF_DEMD_LINE_NBR, D.BILL_TO_CUST_ID, C.RETURN_FR_CUST_ID, C.REF_INV_ITEM_ID, A.RECEIPT_DTTM, A.RECEIPT_DTTM, B.OM_AMOUNT, C.CURRENCY_CD, B.QTY_RETURNED, A.UNIT_OF_MEASURE, C.UNIT_OF_MEASURE, G.SETCNTRLVALUE, E.INVOICE, B.RMA_ID , B.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 | SOLD_TO_CUST_ID |
Character(15) | VARCHAR2(15) NOT NULL |
Sold To Customer
Prompt Table: CUST_SLD_VW |
| 4 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_OM_NONVW |
| 5 | ORDER_NO |
Character(10) | VARCHAR2(10) NOT NULL |
Identifies a customer order number that appears as a reference on a receivables pending item.
Date Initials Issue Description
022701 mv SP2 (F-CJORGENS-3) CSR Desktop
Prompt Table: ORD_AN_VW |
| 6 | ORDER_INT_LINE_NO |
Number(5,0) | INTEGER NOT NULL | Order Line |
| 7 | SCHED_LINE_NBR |
Number(6,0) | INTEGER NOT NULL | Schedule Line Number |
| 8 | DEMAND_LINE_NO |
Number(4,0) | SMALLINT NOT NULL | Demand Line No |
| 9 | BILL_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL |
Customer
Prompt Table: CUST_SLDBLL_VW |
| 10 | SHIP_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Ship To Customer |
| 11 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL |
Item ID
Prompt Table: BU_ITM_INV_VW2 |
| 12 | SHIP_DTTM | DateTime(26) | TIMESTAMP | Ship Date/Time |
| 13 | SCHED_ARRV_DTTM | DateTime(26) | TIMESTAMP | Schedule Arrival Dt/Tm |
| 14 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | QTY_SHIPPED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Shipped |
| 16 | UNIT_MEASURE_SHIP | Character(3) | VARCHAR2(3) NOT NULL |
Shipment Unit of Measure
Prompt Table: INV_SHIP_UOM_VW |
| 17 | BILL_OF_LADING | Character(30) | VARCHAR2(30) NOT NULL | Specifies a bill of lading number associated with a Receivables item. |
| 18 | INVOICE | Character(22) | VARCHAR2(22) NOT NULL | Invoice |
| 19 | SHIP_ID | Character(10) | VARCHAR2(10) NOT NULL |
Shipping ID
Prompt Table: SHIP_HDR_INV |
| 20 | QTY_REQUESTED | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Requested |
| 21 | 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 |
| 22 | FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Flag
N=No Y=Yes |
| 23 | RMA_ID | Character(10) | VARCHAR2(10) NOT NULL | RMA Number |
| 24 | RMA_LINE_NBR | Number(5,0) | INTEGER NOT NULL | RMA Line Number |
| 25 | SEQ_NUM | Number(3,0) | SMALLINT NOT NULL | Sequence |