RE_ESTSLS_PSRCH

(SQL View)
Index Back

Sales Report Search View

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

SELECT A.BUSINESS_UNIT ,B.FISCAL_YEAR ,B.ACCOUNTING_PERIOD , C.BEGIN_DT , C.END_DT , E.NAME1 , A.LS_NBR , A.LS_KEY , A.LEASE_NAME , A.LEASE_STATUS , H.END_DT , '2' FROM PS_RE_LS A , PS_RE_SLSRPT B , PS_CAL_DETP_TBL C , PS_RE_SLSREV D , PS_VENDOR E , PS_CAL_DETP_TBL H WHERE A.LS_KEY = B.LS_KEY AND (C.BEGIN_DT >= A.LEASE_START_DT AND C.END_DT <= A.LEASE_END_DT) AND C.END_DT <= %CurrentDateIn AND H.CALENDAR_ID = B.CALENDAR_ID AND H.SETID = C.SETID AND (H.FISCAL_YEAR * 1000 + H.ACCOUNTING_PERIOD) = ( SELECT MAX(H1.FISCAL_YEAR * 1000 + H1.ACCOUNTING_PERIOD) FROM PS_RE_SLSRPT H1 WHERE H1.LS_KEY = A.LS_KEY AND (H1.FISCAL_YEAR * 1000 + H1.ACCOUNTING_PERIOD <= B.FISCAL_YEAR * 1000 + B.ACCOUNTING_PERIOD)) AND E.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC F WHERE F.SETCNTRLVALUE = A.BUSINESS_UNIT AND F.RECNAME = 'VENDOR') AND A.PRENT_PRNCPL_ID = E.VENDOR_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_DETP_TBL') AND B.CALENDAR_ID = C.CALENDAR_ID AND B.FISCAL_YEAR = C.FISCAL_YEAR AND B.ACCOUNTING_PERIOD = C.ACCOUNTING_PERIOD AND EXISTS( SELECT 'X' FROM PS_RE_SLSREV_CTG Y WHERE Y.SLSRPT_ID = D.SLSRPT_ID AND Y.SLS_AMT_TYPE = '2')

# 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 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
7 LS_NBR Character(10) VARCHAR2(10) NOT NULL Lease Number, User Enterable or Generated Sequence Number
8 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key
9 LEASE_NAME Character(60) VARCHAR2(60) NOT NULL Lease Name
10 LEASE_STATUS Character(1) VARCHAR2(1) NOT NULL Lease Status
1=Pending
2=Active
3=Canceled
4=Disputed
5=Expired
6=Holdover
7=Closed
8=Transferred
11 LAST_RPT_DATE Date(10) DATE Last Report Date
12 SALESRPT_TYPE Character(1) VARCHAR2(1) NOT NULL Sales Report Type
1=Missing
2=Estimated