SCN_LN_CUST_VW

(SQL View)
 Index Back

Sales Cntrct Line by SoldTo Vw

Date Init. Issue # Description 01/03/01 JJM R-SGATIG-ZV65C Changed SQL to use new tables

SELECT DISTINCT D.BUSINESS_UNIT ,C.SOLD_TO_CUST_ID ,C.CNTRCT_ID ,D.LINE_NBR ,C.SETID ,D.PRODUCT_ID ,B.CUSTOMER_ITEM_NBR ,B.PROD_ID_ENTERED ,B.PROD_ID_SRC ,B.UNIT_OF_MEASURE ,B.QTY_MAX ,B.QTY_MIN ,B.PCT_OVER_QTY ,B.LIST_PRICE ,B.PRICE ,B.PRICE_PROGRAM ,B.SHIP_TO_CUST_ID ,B.DS_NETWORK_CODE ,B.SHIP_FROM_BU ,A.CNTCT_SEQ_NUM ,A.PYMNT_TERMS_CD ,A.PAYMENT_METHOD ,A.SHIP_TYPE_ID ,A.FREIGHT_TERMS ,A.SHIP_PRIORITY_ID ,A.SHIP_PARTIAL_ORD ,A.SHIP_PARTIAL_FLAG ,A.SHIP_PRIOR_FLAG ,A.SINGLE_SHIP_FLAG ,A.BCKORDR_CNCL_FLAG ,B.UPC_ID ,B.UPN_ID ,A.CUSTOMER_PO ,A.CUSTOMER_PO_LINE FROM PS_SCON_HDR A , PS_SCON_LINE B , PS_SCON_CUST_CGRP C , PS_SCON_PROD_PGRP D WHERE A.SETID = B.SETID AND A.CNTRCT_ID = B.CNTRCT_ID AND B.SETID = C.SETID AND B.CNTRCT_ID = C.CNTRCT_ID AND C.SETID = D.SETID AND C.CNTRCT_ID = D.CNTRCT_ID AND B.LINE_NBR = D.LINE_NBR AND A.SCON_STATUS = 'A' AND B.UNIT_OF_MEASURE <> ' '

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Default Value: OPR_DEF_TBL_OM.BUSINESS_UNIT

Prompt Table: SP_BU_OM_NONVW

2 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer

Prompt Table: CUST_SLD_VW

3 CNTRCT_ID Character(25) VARCHAR2(25) NOT NULL Buying Agreement ID

Prompt Table: SCON_HDR

4 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
5 SETID Character(5) VARCHAR2(5) NOT NULL SetID
6 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID

Prompt Table: PROD_ITEM

7 CUSTOMER_ITEM_NBR Character(18) VARCHAR2(18) NOT NULL Customer Item Number
8 PROD_ID_ENTERED Character(20) VARCHAR2(20) NOT NULL Product ID Entered
9 PROD_ID_SRC Character(1) VARCHAR2(1) NOT NULL Product Source
C=Customer
P=UPC
S=System
U=Universal
10 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
11 QTY_MAX Number(16,4) DECIMAL(15,4) NOT NULL Maximum Quantity
12 QTY_MIN Number(16,4) DECIMAL(15,4) NOT NULL Minimum Quantity
13 PCT_OVER_QTY Number(3,0) SMALLINT NOT NULL % Over Quantity
14 LIST_PRICE Signed Number(17,4) DECIMAL(15,4) NOT NULL List Price
15 PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL This is price per unit for calculating the service charge.
16 PRICE_PROGRAM Character(10) VARCHAR2(10) NOT NULL Date Initials Issue Description 111701 dms F-CJORGENS-11 Add EXCL_PRICE_LBL
17 SHIP_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Ship To Customer
18 DS_NETWORK_CODE Character(6) VARCHAR2(6) NOT NULL Distribution Network Code
19 SHIP_FROM_BU Character(5) VARCHAR2(5) NOT NULL Ship from INV BU
20 CNTCT_SEQ_NUM Number(5,0) INTEGER NOT NULL Contact Sequence Number
21 PYMNT_TERMS_CD Character(5) VARCHAR2(5) NOT NULL Specifies how the payment due date and discount due date are calculated. A payment terms code is associated with various business units bill-to customers vendors as well as sales orders purchase orders and vouchers.
22 PAYMENT_METHOD Character(3) VARCHAR2(3) NOT NULL Specifies the preferred method of payment by a customer. Examples of payment method are check direct debit and draft. 10/28/2003 edw 655070000 - Removed LTC and CSH xlats.
ACH=ACH
CC=Credit Card
CHK=Check
CSH=Cash
DD=Direct Debit
DR=Draft
EC=Electronic Check
EFT=Electronic Fund Transfer
GE=Giro - EFT
PL=PayPal
WIR=Wire Report
23 SHIP_TYPE_ID Character(10) VARCHAR2(10) NOT NULL Ship Via Code
24 FREIGHT_TERMS Character(10) VARCHAR2(10) NOT NULL Freight Terms Code
25 SHIP_PRIORITY_ID Character(6) VARCHAR2(6) NOT NULL Shipping Priority Code
26 SHIP_PARTIAL_ORD Character(1) VARCHAR2(1) NOT NULL Partial Order
N=Partial Order No
Y=Partial Order Yes
27 SHIP_PARTIAL_FLAG Character(1) VARCHAR2(1) NOT NULL Partial Schedules
N=Ship Partial No
Y=Ship Partial Yes
28 SHIP_PRIOR_FLAG Character(1) VARCHAR2(1) NOT NULL Ship Prior to Request Date
N=Ship Prior No
Y=Ship Prior Yes
29 SINGLE_SHIP_FLAG Character(1) VARCHAR2(1) NOT NULL Single Ship Flag
N=No
Y=Yes
30 BCKORDR_CNCL_FLAG Character(1) VARCHAR2(1) NOT NULL Cancel Backorder
N=Cancel Backorder No
Y=Cancel Backorder Yes
31 UPC_ID Character(18) VARCHAR2(18) NOT NULL Uniform Product Code ID
32 UPN_ID Character(20) VARCHAR2(20) NOT NULL Universal Product Number
33 CUSTOMER_PO Character(25) VARCHAR2(25) NOT NULL Customer PO
34 CUSTOMER_PO_LINE Character(11) VARCHAR2(11) NOT NULL Customer PO Line