RE_SLSRPT_SRCH (SQL View)	Index Back
Sales Report Search View Sales Report Search View - SQL View: Used to display the search results of sales reports based on the various criteria entered.

RE_SLSRPT_SRCH

(SQL View)

Sales Report Search View

Sales Report Search View - SQL View: Used to display the search results of sales reports based on the various criteria entered.

SELECT A.BUSINESS_UNIT ,B.FISCAL_YEAR ,B.ACCOUNTING_PERIOD , C.BEGIN_DT , C.END_DT , B.SLSRPT_ID , D.REVISION_NBR , A.LS_KEY , A.LS_NBR , E.NAME1 , A.LEASE_NAME , B.CURRENCY_CD , B.TOT_SLS_AMT , B.TOT_BILL_AMT , B.SLSRPT_STATUS FROM PS_RE_LS A , PS_RE_SLSRPT B , PS_CAL_BLDR_VW C , PS_RE_SLSREV D , PS_CUSTOMER E WHERE A.LS_KEY = B.LS_KEY AND A.LEASE_OBLG = '2' AND E.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC F WHERE F.SETCNTRLVALUE = A.BUSINESS_UNIT AND F.RECNAME = 'CUSTOMER') AND B.PRINCIPAL_ID = E.CUST_ID AND B.SLSRPT_ID = D.SLSRPT_ID AND D.REVISION_NBR = ( SELECT MAX(REVISION_NBR) FROM PS_RE_SLSREV G WHERE D.SLSRPT_ID = G.SLSRPT_ID ) AND C.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC H WHERE H.SETCNTRLVALUE = A.BUSINESS_UNIT AND H.RECNAME = 'CAL_BLDR_VW') AND B.CALENDAR_ID = C.CALENDAR_ID AND B.FISCAL_YEAR = C.FISCAL_YEAR AND B.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 FISCAL_YEAR Number(4,0) SMALLINT NOT NULL Fiscal Year

3 ACCOUNTING_PERIOD Number(3,0) SMALLINT NOT NULL Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.

4 FROM_DATE Date(10) DATE From Date

5 TO_DATE Date(10) DATE To Date

6 SLSRPT_ID Number(15,0) DECIMAL(15) NOT NULL Sales Report ID - Number (15)

7 REVISION_NBR Number(3,0) SMALLINT NOT NULL Sales Report Revision Number - Number (3)

8 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key

9 LS_NBR Character(10) VARCHAR2(10) NOT NULL Lease Number, User Enterable or Generated Sequence Number

10 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1

11 LEASE_NAME Character(60) VARCHAR2(60) NOT NULL Lease Name

12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

13 TOT_SLS_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Sales Amount - Signed Number (23.3)

14 TOT_BILL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Bill Amount - Signed Number (23.3)

15 SLSRPT_STATUS Character(1) VARCHAR2(1) NOT NULL Sales Report Status - Character (1) - XLAT
1=Pending
2=Processed
3=Pending Approval

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
2	FISCAL_YEAR	Number(4,0)	SMALLINT NOT NULL	Fiscal Year
3	ACCOUNTING_PERIOD	Number(3,0)	SMALLINT NOT NULL	Identifies a time period to which you post transactions. Typically, an accounting period represents a month, but it can also represent a week, a day, or any user-defined interval. An accounting period has a beginning date and an ending date, and is defined in the calendar table.
4	FROM_DATE	Date(10)	DATE	From Date
5	TO_DATE	Date(10)	DATE	To Date
6	SLSRPT_ID	Number(15,0)	DECIMAL(15) NOT NULL	Sales Report ID - Number (15)
7	REVISION_NBR	Number(3,0)	SMALLINT NOT NULL	Sales Report Revision Number - Number (3)
8	LS_KEY	Number(15,0)	DECIMAL(15) NOT NULL	Lease Key, Unique Key
9	LS_NBR	Character(10)	VARCHAR2(10) NOT NULL	Lease Number, User Enterable or Generated Sequence Number
10	NAME1	Character(40)	VARCHAR2(40) NOT NULL	Name 1
11	LEASE_NAME	Character(60)	VARCHAR2(60) NOT NULL	Lease Name
12	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
13	TOT_SLS_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total Sales Amount - Signed Number (23.3)
14	TOT_BILL_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Total Bill Amount - Signed Number (23.3)
15	SLSRPT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Sales Report Status - Character (1) - XLAT 1=Pending 2=Processed 3=Pending Approval