RS_ED_SO_LN

(SQL View)

SELECT A.BUSINESS_UNIT , A.SO_ID , A.SO_LINE , B.DESCR60 , A.DESCR , D.XLATLONGNAME , A.SO_LINE_STATUS , B.SO_DATE , B.CUST_SETID , B.CUST_ID , B.CONTRACT_NUM , B.PROJECT_ID , E.DESCR , C.CITY , C.STATE , C.COUNTRY , C.POSTAL , %Coalesce( L.DTTM_MODIFIED, %DATETIMEIN('1900-01-01 00.00.00.000000')) AS LASTUPDDTTM FROM %Table(RS_SO_LINE) A LEFT OUTER JOIN PS_RS_ED_ETL_LOG L ON L.DOCUMENT = %TrimSubstr(A.BUSINESS_UNIT,1,5) %Concat '' %Concat %TrimSubstr(A.SO_ID,1,15) %Concat '' %Concat %TrimSubstr(%NumToChar(A.SO_LINE),1,3) , %Table(RS_SO_HDR) B LEFT OUTER JOIN %Table(PROJECT) E ON E.BUSINESS_UNIT = B.BUSINESS_UNIT AND E.PROJECT_ID = B.PROJECT_ID , %Table(CUST_ADDR_EF_VW) C , %Table(PSXLATITEM) D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.SO_ID = B.SO_ID AND A.SO_LINE_STATUS = 'O' AND A.SO_ID IN ( SELECT SO_ID FROM PS_RS_SO_STAT_VW1 WHERE BUSINESS_UNIT = A.BUSINESS_UNIT) AND B.SYSTEM_SOURCE = 'RS' AND A.ADDRESS_SEQ_NUM = C.ADDRESS_SEQ_NUM AND C.SETID = B.CUST_SETID AND C.CUST_ID = B.CUST_ID AND D.FIELDNAME = 'SO_SOURCE' AND D.FIELDVALUE = B.SO_SOURCE

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

2 SO_ID Character(15) VARCHAR2(15) NOT NULL Service Order ID

3 SO_LINE Number(3,0) SMALLINT NOT NULL Line Number

4 DESCR60 Character(60) VARCHAR2(60) NOT NULL Description

5 LINE_DESCR Character(30) VARCHAR2(30) NOT NULL Describes the nature of a journal line. It is defaulted from the description of the Account ChartField or manually entered.

6 DOCUMENT_SOURCE Character(30) VARCHAR2(30) NOT NULL Document Source for AIA

7 SO_LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Line Status
A=Closed
C=Cancel
O=Open

8 SO_DATE Date(10) DATE Service Order Date

9 CUST_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID

10 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
Prompt Table: %EDITTABLE5

11 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
Default Value: NEXT

12 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField

13 PROJ_DESCR Character(30) VARCHAR2(30) NOT NULL Project ID Description

14 CITY Character(30) VARCHAR2(30) NOT NULL City
Prompt Table: RS_GEOCODE_CITY

15 STATE Character(6) VARCHAR2(6) NOT NULL State

16 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

17 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

18 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	SO_ID	Character(15)	VARCHAR2(15) NOT NULL	Service Order ID
3	SO_LINE	Number(3,0)	SMALLINT NOT NULL	Line Number
4	DESCR60	Character(60)	VARCHAR2(60) NOT NULL	Description
5	LINE_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Describes the nature of a journal line. It is defaulted from the description of the Account ChartField or manually entered.
6	DOCUMENT_SOURCE	Character(30)	VARCHAR2(30) NOT NULL	Document Source for AIA
7	SO_LINE_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Line Status A=Closed C=Cancel O=Open
8	SO_DATE	Date(10)	DATE	Service Order Date
9	CUST_SETID	Character(5)	VARCHAR2(5) NOT NULL	Customer SetID
10	CUST_ID	Character(15)	VARCHAR2(15) NOT NULL	Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. Prompt Table: %EDITTABLE5
11	CONTRACT_NUM	Character(25)	VARCHAR2(25) NOT NULL	Contract Default Value: NEXT
12	PROJECT_ID	Character(15)	VARCHAR2(15) NOT NULL	Project Id ChartField
13	PROJ_DESCR	Character(30)	VARCHAR2(30) NOT NULL	Project ID Description
14	CITY	Character(30)	VARCHAR2(30) NOT NULL	City Prompt Table: RS_GEOCODE_CITY
15	STATE	Character(6)	VARCHAR2(6) NOT NULL	State
16	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country
17	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
18	LASTUPDDTTM	DateTime(26)	TIMESTAMP	Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.