OMEC_HDRSOLD_VW

(SQL View)
 Index Back

OM Ord Address Work Doc

OM Ord Address Work Doc

SELECT h.business_unit , h.order_no , 0 , 0 , h.sold_to_cust_id , h.address_seq_sold , 'S' , d.name1 , e.name1 , h.source_cd ,%subrec(ADDRESS_SBR,b) ,%subrec(BUS_PHONE_SBR,b) FROM ps_ord_header h , ps_cust_addr_seq a , ps_cust_address b , ps_set_cntrl_rec s , ps_customer d , ps_contact e , ps_contact_cust f WHERE s.setid = a.setid AND s.recname = 'CUST_ADDRESS' AND s.setcntrlvalue = h.business_unit AND h.sold_to_cust_id = a.cust_id AND h.address_seq_sold = a.address_seq_num AND a.setid = b.setid AND a.cust_id= b.cust_id AND a.address_seq_num = b.address_seq_num AND b.eff_status = 'A' AND %EffdtCheck(cust_address b1, b, h.order_date) AND a.setid = d.setid AND a.cust_id= d.cust_id AND f.cust_id = d.cust_id AND f.customer_setid = d.setid AND f.cntct_seq_num = h.cntct_seq_num AND f.setid = e.setid AND f.contact_id = e.contact_id AND %EffdtCheck(contact e1, e, h.order_date) AND NOT EXISTS ( SELECT 'x' FROM PS_ORD_ADDR x WHERE x.business_unit = h.business_unit AND x.order_no = h.order_no AND x.order_int_line_no = 0 AND x.sched_line_nbr = 0 AND x.cust_addr_type = 'S')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 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
3 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
4 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
5 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer
6 ADDRESS_SEQ_SOLD Number(5,0) INTEGER NOT NULL Primary Address - Sold To
7 CUST_ADDR_TYPE Character(1) VARCHAR2(1) NOT NULL Customer Address Type
H=Ship To Customer
S=Sold To Customer
8 CUST_NAME Character(40) VARCHAR2(40) NOT NULL Name
9 CNTCT_NAME1 Character(40) VARCHAR2(40) NOT NULL Contact Name1
10 SOURCE_CD Character(6) VARCHAR2(6) NOT NULL Source Code
11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

12 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
13 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
14 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
15 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
16 CITY Character(30) VARCHAR2(30) NOT NULL City
17 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
18 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
19 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
20 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
21 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
22 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
23 COUNTY Character(30) VARCHAR2(30) NOT NULL County
24 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

25 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
26 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
27 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

28 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Int'l Prefix
29 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
30 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone Extension
31 FAX Character(24) VARCHAR2(24) NOT NULL Fax Number