EST_SHIP_INV_VW(SQL View) |
Index Back |
---|---|
Estimate Shipping Detailmmt 03.26.04 Data model changes Use IN_DEMAND |
SELECT A.BUSINESS_UNIT , ' ' , A.DEMAND_SOURCE , A.SOURCE_BUS_UNIT , A.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NBR , A.INV_ITEM_ID , A.DEMAND_LINE_NO , ' ' , A.QTY_SHIPPED_CONV , A.UNIT_MEASURE_SHIP , A.QTY_SHIP_BASE , A.QTY_PACK_BASE , 0 , ' ' , 0 , ' ' , A.ADDRESS_SEQ_NUM , A.CARRIER_ID , A.FREIGHT_TERMS , A.FRT_CHRG_METHOD , A.FRT_CHRG_OVERRIDE , A.LOAD_ID , A.LOCATION , A.ROUTE_CD , %DatePart(A.SHIP_DTTM) , A.SHIP_ID , A.SHIP_TO_CUST_ID , A.SHIP_TYPE_ID , ' ' , B.COUNTRY , B.STATE , 0 , A.IN_FULFILL_STATE , A.QTY_PACK_BASE , A.DELIV_USE_UOM_SHIP , A.UNIT_OF_MEASURE , A.DELIV_UNIT_VOL , A.DELIV_UNIT_WT , A.DELIV_UOM_VOL , A.DELIV_UOM_WT , A.DELIV_UOM_SHIP_VOL , A.DELIV_UOM_SHIP_WT , A.DELIV_UNIT_VOL_CNV , A.DELIV_UNIT_WT_CNV , B.ADDRESS1 , B.ADDRESS2 , B.ADDRESS3 , B.ADDRESS4 , B.ADDR_FIELD1 , B.ADDR_FIELD2 , B.ADDR_FIELD3 , C.CARRIER_ID_EXP , B.CITY , B.COUNTY , C.FREIGHT_TERMS_EXP , B.GEO_CODE , B.HOUSE_TYPE , B.IN_CITY_LIMIT , B.NUM1 , B.NUM2 , B.POSTAL , A.PRODUCT_ID , C.SHIP_TYPE_ID_EXP , A.TMS_LOAD_STP_NBR , C.DOC_REFNUM , C.VAT_ENTITY , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN ' ' ELSE D.FREIGHT_BILL_TYPE END , D.ORDER_TYPE_CD , A.PREF_CARRIER_FLAG , D.DEL_ORD_RESTRICTED , ' ' , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN F.BASE_CURRENCY ELSE C.CURRENCY_CD END , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN F.BASE_CURRENCY ELSE C.CURRENCY_CD_BASE END , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN %DatePart(A.SCHED_DTTM) ELSE D.RATE_DATE END , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN F.CUR_RT_TYPE ELSE D.RT_TYPE END , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN 1 ELSE D.RATE_MULT END , CASE WHEN A.DEMAND_SOURCE = 'IN' THEN 1 ELSE D.RATE_DIV END , 0 FROM PS_IN_DEMAND A LEFT OUTER JOIN PS_IN_DEMAND_ADDR B ON %Join(common_keys, in_demand a, in_demand_addr b) LEFT OUTER JOIN PS_ORD_SCHEDULE C ON C.BUSINESS_UNIT = A.SOURCE_BUS_UNIT AND C.ORDER_NO = A.ORDER_NO AND C.ORDER_INT_LINE_NO = A.ORDER_INT_LINE_NO AND C.SCHED_LINE_NBR = A.SCHED_LINE_NBR LEFT OUTER JOIN PS_ORD_HEADER D ON D.BUSINESS_UNIT = A.SOURCE_BUS_UNIT AND D.ORDER_NO = A.ORDER_NO , PS_BUS_UNIT_TBL_IN F WHERE A.IN_FULFILL_STATE BETWEEN '50' AND '70' AND F.BUSINESS_UNIT = A.BUSINESS_UNIT |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
2 | DELIVERY_ID | Character(10) | VARCHAR2(10) NOT NULL | Delivery ID |
3 | 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 |
4 | SOURCE_BUS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Source Bus Unit |
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 |
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 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL | Item ID |
9 | DEMAND_LINE_NO | Number(4,0) | SMALLINT NOT NULL | Demand Line No |
10 | SHIP_CNTR_ID | Character(20) | VARCHAR2(20) NOT NULL | Shipping Container ID |
11 | QTY_SHIPPED_CONV | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Shipped |
12 | UNIT_MEASURE_SHIP | Character(3) | VARCHAR2(3) NOT NULL | Shipment Unit of Measure |
13 | QTY_SHIP_BASE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Qty Ship Base |
14 | QTY_PACK_BASE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Base Quantity Packed |
15 | SHIPPING_WEIGHT | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Shipping Weight |
16 | UNIT_MEASURE_WT | Character(3) | VARCHAR2(3) NOT NULL | Weight UOM |
17 | SHIPPING_VOLUME | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Shipping Volume |
18 | UNIT_MEASURE_VOL | Character(3) | VARCHAR2(3) NOT NULL | Volume UOM |
19 | ADDRESS_SEQ_NUM | Number(5,0) | INTEGER NOT NULL | Address Sequence Number |
20 | CARRIER_ID | Character(10) | VARCHAR2(10) NOT NULL | Carrier ID |
21 | FREIGHT_TERMS | Character(10) | VARCHAR2(10) NOT NULL | Freight Terms Code |
22 | FRT_CHRG_METHOD | Character(2) | VARCHAR2(2) NOT NULL |
Freight Charge Method
01=Order Value 02=Weight 03=Volume 04=Order Quantity |
23 | FRT_CHRG_OVERRIDE | Character(1) | VARCHAR2(1) NOT NULL | Freight Charge Override |
24 | LOAD_ID | Character(10) | VARCHAR2(10) NOT NULL | Load ID |
25 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
26 | ROUTE_CD | Character(6) | VARCHAR2(6) NOT NULL | A unique identifier assigned to a delivery route. Route codes can be associated with customer addresses for external orders or locations for internal orders. Route codes can be used to identify the appropriate delivery schedule for a customer/location if you are managing product shipments with your own vehicles. |
27 | SCHED_SHIP_DATE | Date(10) | DATE | Scheduled Ship Date |
28 | SHIP_ID | Character(10) | VARCHAR2(10) NOT NULL | Shipping ID |
29 | SHIP_TO_CUST_ID | Character(15) | VARCHAR2(15) NOT NULL | Ship To Customer |
30 | SHIP_TYPE_ID | Character(10) | VARCHAR2(10) NOT NULL | Ship Via Code |
31 | SELECTED_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Selected Flag
N=Not Selected Y=Seleted Y/N Table Edit |
32 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
33 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
34 | AVAIL_QTY | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Available Qty |
35 | IN_FULFILL_STATE | Character(2) | VARCHAR2(2) NOT NULL |
Inventory Fulfillment State
10=Pending 20=Unfulfilled 30=Releasable 40=Released 50=Confirmed 60=Shipped 70=Depleted 90=Canceled |
36 | ORIG_QTY_BASE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | Original Qty Base |
37 | DELIV_USE_UOM_SHIP | Character(1) | VARCHAR2(1) NOT NULL | Use UNIT_MEASURE_SHIP |
38 | UNIT_OF_MEASURE | Character(3) | VARCHAR2(3) NOT NULL |
Used on an approval rule set.
MHR=Muti Hourly PER=Percentage SQF=Square Footage |
39 | DELIV_UNIT_VOL | Number(16,4) | DECIMAL(15,4) NOT NULL | Delivery Unit Volume |
40 | DELIV_UNIT_WT | Number(16,4) | DECIMAL(15,4) NOT NULL | Delivery Unit Weight |
41 | DELIV_UOM_VOL | Character(3) | VARCHAR2(3) NOT NULL | Delivery Volume UOM |
42 | DELIV_UOM_WT | Character(3) | VARCHAR2(3) NOT NULL | Delivery Weight UOM |
43 | DELIV_UOM_SHIP_VOL | Character(3) | VARCHAR2(3) NOT NULL | Ship UOM for volume conversion |
44 | DELIV_UOM_SHIP_WT | Character(3) | VARCHAR2(3) NOT NULL | Ship UOM for weight conversion |
45 | DELIV_UNIT_VOL_CNV | Number(16,8) | DECIMAL(15,8) NOT NULL | Unit volume conversion rate |
46 | DELIV_UNIT_WT_CNV | Number(16,8) | DECIMAL(15,8) NOT NULL | Unit weight conversion rate |
47 | ADDRESS1 | Character(55) | VARCHAR2(55) NOT NULL | Address 1 |
48 | ADDRESS2 | Character(55) | VARCHAR2(55) NOT NULL | Address 2 |
49 | ADDRESS3 | Character(55) | VARCHAR2(55) NOT NULL | Address 3 |
50 | ADDRESS4 | Character(55) | VARCHAR2(55) NOT NULL | Address 4 |
51 | ADDR_FIELD1 | Character(2) | VARCHAR2(2) NOT NULL | Address Field 1 |
52 | ADDR_FIELD2 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 2 |
53 | ADDR_FIELD3 | Character(4) | VARCHAR2(4) NOT NULL | Address Field 3 |
54 | CARRIER_ID_EXP | Character(10) | VARCHAR2(10) NOT NULL | Export Carrier ID |
55 | CITY | Character(30) | VARCHAR2(30) NOT NULL | City |
56 | COUNTY | Character(30) | VARCHAR2(30) NOT NULL | County |
57 | FREIGHT_TERMS_EXP | Character(10) | VARCHAR2(10) NOT NULL | Export Freight Terms Code |
58 | GEO_CODE | Character(11) | VARCHAR2(11) NOT NULL | Geo Code |
59 | HOUSE_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
House Type
AB=House Boat WW=Trailer |
60 | IN_CITY_LIMIT | Character(1) | VARCHAR2(1) NOT NULL | In City Limit |
61 | NUM1 | Character(6) | VARCHAR2(6) NOT NULL | Number 1 |
62 | NUM2 | Character(6) | VARCHAR2(6) NOT NULL | Number 2 |
63 | POSTAL | Character(12) | VARCHAR2(12) NOT NULL | Postal |
64 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL | Product ID |
65 | SHIP_TYPE_ID_EXP | Character(10) | VARCHAR2(10) NOT NULL | Export Shipping Method |
66 | TMS_LOAD_STP_NBR | Number(5,0) | INTEGER NOT NULL | Load Stop Number |
67 | DOC_REFNUM | Character(20) | VARCHAR2(20) NOT NULL | Counter sale document reference number |
68 | VAT_ENTITY | Character(20) | VARCHAR2(20) NOT NULL | Specifies an organizational entity that is responsible for VAT reporting and that is registered in one or more countries that require VAT reporting. A single VAT entity is linked to one or more General Ledger Business Units depending on the reporting requirements. |
69 | FREIGHT_BILL_TYPE | Character(2) | VARCHAR2(2) NOT NULL |
Freight Bill Type
OE=Order Entry SH=Shipping |
70 | ORDER_TYPE_CD | Character(3) | VARCHAR2(3) NOT NULL |
Order Type
QUO=Sales Quote SO=Sales Order |
71 | PREF_CARRIER_FLAG | Character(1) | VARCHAR2(1) NOT NULL |
Preferred Carrier flag
N=No Preferred Carrier Y=Preferred Carrier |
72 | DEL_ORD_RESTRICTED | Character(1) | VARCHAR2(1) NOT NULL |
Deliveries restricted to order
N=Deliveries Not Restricted Y=Deliveries Restricted to Order |
73 | SOURCE_CD | Character(6) | VARCHAR2(6) NOT NULL | Source Code |
74 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
75 | CURRENCY_CD_BASE | Character(3) | VARCHAR2(3) NOT NULL | Business Unit Base Currency |
76 | RATE_DATE | Date(10) | DATE | Exchange Rate Date |
77 | 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. |
78 | RATE_MULT | Signed Number(17,8) | DECIMAL(15,8) NOT NULL | Rate Multiplier |
79 | RATE_DIV | Number(16,8) | DECIMAL(15,8) NOT NULL | Rate Divisor |
80 | DEMAND_RS_ROW | Number(7,0) | INTEGER NOT NULL | Demand rowset row |