RE_ESTSLS_PSRCH(SQL View) |
Index Back |
---|---|
Sales Report Search ViewSales 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 |