RMA_CUSSHPHS_VW

(SQL View)
 Index Back

RMA Cust Ship Hist View

This record contains one row for every shipment made on an order line, whether for an inventory or non-inventory Order Management Product. Date Initials Issue Description 033101 dms 6529701 Add lot/serial fields 040501 dms R-BLOVE-WP6VU Add order_date 032802 dms 71948009 Delete default for business_unit, reset value returned for bu 102102 dms 8.8 dev Get project info from ship_inf_inv 022003 dms 8.8 Add distrib_type 052103 deg 575941003 Added prod_id_src 052504 twh F-KNANN-V59N4 Replace demand table references with IN_DEMAND

SELECT DISTINCT OH.BUSINESS_UNIT , OH.SOLD_TO_CUST_ID , SH.ORDER_NO , SH.ORDER_INT_LINE_NO , SH.SCHED_LINE_NBR , OH.CUSTOMER_PO , SH.BUSINESS_UNIT , OH.ORDER_DATE , SH.DEMAND_SOURCE , SH.SOURCE_BUS_UNIT , SH.DEMAND_LINE_NO , OS.ADDRESS_SEQ_NUM , BI.BILLING_STATUS , SH.BILL_OF_LADING , OS.CARRIER_ID , OL.CONFIG_CODE , OH.CURRENCY_CD , OH.CURRENCY_CD_BASE , P.DESCR , SH.DISTRIB_TYPE , OS.FREIGHT_TERMS , SH.INV_ITEM_ID , SH.KIT_LINE_NBR , SH.QTY_CODE , SH.QTY_PER , SH.OPTIONAL_SHIP , OS.NET_UNIT_PRICE , OS.NET_UNIT_PRICE_BSE , SH.PARENT_PROD_ID , OL.PROD_ID_ENTERED , OL.PROD_ID_SRC , SH.PRODUCT_ID , SH.QTY_SHIP_BASE , NVL (BL.QTY , SH.QTY_SHIPPED) , SH.QTY_SHIPPED_CONV , OH.RATE_DATE , OH.RATE_DIV , OH.RATE_MULT , OH.RT_TYPE , %DatePart(SH.SHIP_DTTM) , SH.SHIP_DTTM , SH.SHIP_ID , SH.SHIP_LINE_NO , %TimePart(SH.SHIP_DTTM) , OS.SHIP_TO_CUST_ID , SH.SHIP_TYPE_ID , OS.STORE_NUMBER , OS.TAX_CUST_GROUP , SH.UNIT_MEASURE_SHIP , SH.UNIT_OF_MEASURE , BI.BUSINESS_UNIT_BI , BI.INVOICE , BI.LINE_SEQ_NUM , P.LOT_CONTROL , P.SERIAL_CONTROL , P.SHIP_SERIAL_CNTRL , P.CFG_CODE_OPT , SH.BUSINESS_UNIT_PC , SH.PROJECT_ID , SH.ACTIVITY_ID , SH.RESOURCE_TYPE , SH.RESOURCE_CATEGORY , SH.RESOURCE_SUB_CAT , OH.SOURCE_CD , OS.ADDR_OVRD_LEVEL , OH.BILL_TO_CUST_ID , BH.BILL_TO_CUST_ID , BL.QTY , BL.UNIT_OF_MEASURE , 'D' FROM PS_IN_DEMAND SH , PS_ORD_SCHEDULE OS , PS_ORD_LINE OL , PS_ORD_HEADER OH , PS_SET_CNTRL_REC SC , PS_PROD_INVITEM_VW P , PS_IN_DEMAND_BI BI , PS_BI_HDR BH , PS_BI_LINE BL WHERE SH.BUSINESS_UNIT = OS.SHIP_FROM_BU AND SH.SOURCE_BUS_UNIT = OS.BUSINESS_UNIT AND SH.ORDER_NO = OS.ORDER_NO AND SH.ORDER_INT_LINE_NO = OS.ORDER_INT_LINE_NO AND SH.SCHED_LINE_NBR = OS.SCHED_LINE_NBR AND SH.SHIP_TO_CUST_ID = OS.SHIP_TO_CUST_ID AND SH.DEMAND_SOURCE = 'OM' AND SH.IN_FULFILL_STATE IN ('60' , '70') AND %Join(COMMON_KEYS, ORD_SCHEDULE OS, ORD_LINE OL) AND %Join(COMMON_KEYS, ORD_LINE OL, ORD_HEADER OH) AND SC.SETCNTRLVALUE = SH.SOURCE_BUS_UNIT AND SC.RECNAME = 'PROD_INVITEM_VW' AND P.SETID = SC.SETID AND P.PRODUCT_ID = SH.PRODUCT_ID AND P.RETURN_FLAG = 'Y' AND BI.BUSINESS_UNIT(+) = SH.BUSINESS_UNIT AND BI.DEMAND_SOURCE(+) = SH.DEMAND_SOURCE AND BI.SOURCE_BUS_UNIT(+) = SH.SOURCE_BUS_UNIT AND BI.ORDER_NO(+) = SH.ORDER_NO AND BI.ORDER_INT_LINE_NO(+) = SH.ORDER_INT_LINE_NO AND BI.SCHED_LINE_NBR(+) = SH.SCHED_LINE_NBR AND BI.INV_ITEM_ID(+) = SH.INV_ITEM_ID AND BI.DEMAND_LINE_NO(+) = SH.DEMAND_LINE_NO AND BH.BUSINESS_UNIT(+) = BI.BUSINESS_UNIT_BI AND BH.INVOICE(+) = BI.INVOICE AND BL.BUSINESS_UNIT(+) = BI.BUSINESS_UNIT_BI AND BL.INVOICE(+) = BI.INVOICE AND BL.LINE_SEQ_NUM(+) = BI.LINE_SEQ_NUM

  • Related Language Record: RMA_CSHPHSVWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
    3 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
    4 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
    5 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
    6 CUSTOMER_PO Character(25) VARCHAR2(25) NOT NULL Customer PO
    7 SHIP_FROM_BU Character(5) VARCHAR2(5) NOT NULL Ship from INV BU
    8 ORDER_DATE Date(10) DATE Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
    9 DEMAND_SOURCE Character(2) VARCHAR2(2) NOT NULL Demand Source
    IN=Material Request
    OM=Sales Order
    PL=Planning Requisition
    PO=Purchase Order
    PR=Purchasing Requisition
    RT=Material Return
    SF=Production Request
    WM=Work Order
    10 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
    11 DEMAND_LINE_NO Number(4,0) SMALLINT NOT NULL Demand Line No

    Default Value: 1

    12 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
    13 BILLING_STATUS Character(1) VARCHAR2(1) NOT NULL Billing Status
    A=Available
    B=In Billing
    C=Contract
    I=Invoiced
    N=Not Avail
    P=In Process
    S=Staged

    Default Value: N

    14 BILL_OF_LADING Character(30) VARCHAR2(30) NOT NULL Specifies a bill of lading number associated with a Receivables item.
    15 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID
    16 CONFIG_CODE Character(50) VARCHAR2(50) NOT NULL Product Configurator
    17 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    18 CURRENCY_CD_BASE Character(3) VARCHAR2(3) NOT NULL Business Unit Base Currency
    19 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    20 DISTRIB_TYPE Character(10) VARCHAR2(10) NOT NULL Distrib. Type
    21 FREIGHT_TERMS Character(10) VARCHAR2(10) NOT NULL Freight Terms Code
    22 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
    23 KIT_LINE_NBR Number(3,0) SMALLINT NOT NULL Kit Line Number
    24 QTY_CODE Character(3) VARCHAR2(3) NOT NULL Per
    ASY=Assembly
    ORD=Order
    25 QTY_PER Number(10,4) DECIMAL(9,4) NOT NULL Quantity
    26 OPTIONAL_SHIP Character(1) VARCHAR2(1) NOT NULL OK to Ship Without
    27 NET_UNIT_PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price
    28 NET_UNIT_PRICE_BSE Signed Number(16,4) DECIMAL(14,4) NOT NULL Net Unit Price (base)
    29 PARENT_PROD_ID Character(18) VARCHAR2(18) NOT NULL Parent Product Id
    30 PROD_ID_ENTERED Character(20) VARCHAR2(20) NOT NULL Product ID Entered
    31 PROD_ID_SRC Character(1) VARCHAR2(1) NOT NULL Product Source
    C=Customer
    P=UPC
    S=System
    U=Universal
    32 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID
    33 QTY_SHIP_BASE Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Ship Base
    34 QTY_SHIPPED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped
    35 QTY_SHIPPED_CONV Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Shipped
    36 RATE_DATE Date(10) DATE Exchange Rate Date
    37 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
    38 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
    39 RT_TYPE Character(5) VARCHAR2(5) NOT NULL Defines a category of market rates for currency conversion. Some examples of rate types are commercial, average, floating, and historical.
    40 SHIP_DATE Date(10) DATE Item Shipping Date
    41 SHIP_DTTM DateTime(26) TIMESTAMP Ship Date/Time
    42 SHIP_ID Character(10) VARCHAR2(10) NOT NULL Shipping ID
    43 SHIP_LINE_NO Number(10,2) DECIMAL(9,2) NOT NULL Ship Line No
    44 SHIP_TIME Time(15) TIMESTAMP Ship Time
    45 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
    46 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code
    47 STORE_NUMBER Character(17) VARCHAR2(17) NOT NULL Store Number
    48 TAX_CUST_GROUP Character(10) VARCHAR2(10) NOT NULL Tax Customer Group
    49 UNIT_MEASURE_SHIP Character(3) VARCHAR2(3) NOT NULL Shipment Unit of Measure
    50 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage
    51 BUSINESS_UNIT_BI Character(5) VARCHAR2(5) NOT NULL Billing Business Unit
    52 INVOICE Character(22) VARCHAR2(22) NOT NULL Invoice
    53 LINE_SEQ_NUM Number(5,0) INTEGER NOT NULL Sequence
    54 LOT_CONTROL Character(1) VARCHAR2(1) NOT NULL Lot Control
    N=N
    Y=Y
    55 SERIAL_CONTROL Character(1) VARCHAR2(1) NOT NULL Serial Control
    N=N
    Y=Y
    56 SHIP_SERIAL_CNTRL Character(1) VARCHAR2(1) NOT NULL Shipping Serial Control
    57 CFG_CODE_OPT Character(1) VARCHAR2(1) NOT NULL Configuration Code Generation
    B=Both
    N=No
    Y=Yes
    58 BUSINESS_UNIT_PC Character(5) VARCHAR2(5) NOT NULL PC Business Unit
    59 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    60 ACTIVITY_ID Character(15) VARCHAR2(15) NOT NULL Activity ID
    61 RESOURCE_TYPE Character(5) VARCHAR2(5) NOT NULL Source Type
    62 RESOURCE_CATEGORY Character(5) VARCHAR2(5) NOT NULL Category
    63 RESOURCE_SUB_CAT Character(5) VARCHAR2(5) NOT NULL Subcategory
    64 SOURCE_CD Character(6) VARCHAR2(6) NOT NULL Source Code
    65 ADDR_OVRD_LEVEL Character(1) VARCHAR2(1) NOT NULL Address Override Level
    D=Demand Line
    H=Header
    L=Line
    S=Schedule
    66 BILL_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Customer
    67 BILL_TO_CUST_ID_TO Character(15) VARCHAR2(15) NOT NULL Customer
    68 QTY_BILL Signed Number(17,4) DECIMAL(15,4) NOT NULL Quantity in Billing UOM
    69 UOM_BILL Character(3) VARCHAR2(3) NOT NULL Bill Unit of Measure
    70 RMA_HIST_TYPE Character(1) VARCHAR2(1) NOT NULL RMA history type
    D=Demand
    I=Invoice