RMA_BIINVHS_VW

(SQL View)
 Index Back

RMA BI Invoice Hist View

RMA BI Invoice Hist View

SELECT DISTINCT BL.BUSINESS_UNIT_OM , BL.SOLD_TO_CUST_ID , BL.ORDER_NO , BL.ORDER_INT_LINE_NO , BL.SCHED_LINE_NBR , OH.CUSTOMER_PO , BL.SHIP_FROM_BU , BL.ORDER_DATE , 'OM' , BL.BUSINESS_UNIT_OM , 0 , BL.SHIP_TO_ADDR_NUM , 'B' , BL.BILL_OF_LADING , ' ' , ' ' , BL.BI_CURRENCY_CD , BL.BASE_CURRENCY , BL.DESCR , ' ' , BL.FREIGHT_TERMS , BL.INV_ITEM_ID , 0 , ' ' , 0 , ' ' , 0 , 0 , ' ' , BL.PRODUCT_ID , 'S' , BL.PRODUCT_ID , BL.QTY_BASE , BL.QTY , 0 , BH.RATE_DATE , BH.RATE_DIV , BH.RATE_MULT , ' ' , BL.SHIP_DATE , %Dttm( BL.SHIP_DATE, BL.SHIP_TIME ) , BL.SHIP_ID , 0 , BL.SHIP_TIME , BL.SHIP_TO_CUST_ID , BL.SHIP_TYPE_ID , OH.STORE_NUMBER , ' ' , BL.UNIT_OF_MEASURE , BL.UNIT_OF_MEASURE , BL.BUSINESS_UNIT , BL.INVOICE , BL.LINE_SEQ_NUM , ' ' , ' ' , ' ' , ' ' , BL.BUSINESS_UNIT_PC , BL.PROJECT_ID , BL.ACTIVITY_ID , BL.RESOURCE_TYPE , BL.RESOURCE_CATEGORY , BL.RESOURCE_SUB_CAT , OH.SOURCE_CD , ' ' , BH.BILL_TO_CUST_ID , BH.BILL_TO_CUST_ID , BL.QTY , BL.UNIT_OF_MEASURE , 'I' FROM PS_BI_HDR BH , PS_BI_LINE BL , PS_ORD_HEADER OH WHERE BH.BUSINESS_UNIT = BL.BUSINESS_UNIT AND BH.INVOICE = BL.INVOICE AND OH.BUSINESS_UNIT = BL.BUSINESS_UNIT_OM AND OH.ORDER_NO = BL.ORDER_NO AND BH.BILL_STATUS NOT IN ('CAN','TMP','TMR') AND BL.ORIGINAL_INVOICE = BL.INVOICE AND BL.ADJ_LINE_TYPE = 'REG' AND BL.LINE_TYPE NOT IN ('DEP','DAPP','DREF','PYCV','DRFF') AND BH.TEMPLATE_IVC_FLG = 'N' AND BL.PRODUCT_ID <> ' ' AND NOT EXISTS ( SELECT 'X' FROM PS_IN_DEMAND_BI D WHERE D.BUSINESS_UNIT_BI = BL.BUSINESS_UNIT AND D.INVOICE = BL.INVOICE AND D.LINE_SEQ_NUM = BL.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