RE_LS_TXN_SRCH

(SQL View)
 Index Back

Lease Transactions Search

Search record used in transaction Queue manager to select transactions filtered with the search criteria fields.

SELECT A.LS_TXN_ID ,A.BUSINESS_UNIT ,A.LS_KEY ,A.TERM_ID ,A.TXN_GRP ,A.LS_TXN_SRC_ID ,A.SCHED_DATE ,A.LEASE_OBLG ,A.LS_TXN_ORIG ,A.LS_TXN_TGT ,A.SCHED_NUM , A.LS_TXN_STATUS , A.PREV_TXN_STATUS ,A.FROM_DATE ,A.TO_DATE ,A.PRIOR_PER_DIF ,A.PROCESS_INSTANCE ,%subrec(FMS_WHO_SBR,A) , B.REGION_CD , B.LS_NBR , B.LEASE_NAME , B.LS_ADMIN , B.PRTFL_MGR , A.PRINCIPAL_ID , A.SHIPTO_ID , b.lease_status ,b.PRIM_PROPERTY_ID , C.currency_cd ,C.AMOUNT ,D.PROPERTY_NM ,E.DESCR ,' ' ,' ' , A.DST_ALLOC_METHOD ,A.INVOICE_ID , A.MANUAL_SHIPTO_ID ,A.INVOICE_DT ,CASE WHEN ( SELECT DISTINCT(TXN_DTL.VNDR_LOC) FROM PS_RE_LS_TXN_DTL TXN_DTL WHERE TXN_DTL.LS_TXN_ID = A.LS_TXN_ID) <> '' THEN ( SELECT DISTINCT(TXN_DTL.VNDR_LOC) FROM PS_RE_LS_TXN_DTL TXN_DTL WHERE TXN_DTL.LS_TXN_ID = A.LS_TXN_ID) ELSE CASE WHEN A.PRINCIPAL_ID = B.PRINCIPAL_ID THEN B.VNDR_LOC ELSE ( SELECT VND.DEFAULT_LOC FROM PS_VENDOR VND WHERE VND.VENDOR_ID = A.PRINCIPAL_ID) END END ,CASE WHEN ( SELECT DISTINCT(TXN_DTL.ADDRESS_SEQ_NUM) FROM PS_RE_LS_TXN_DTL TXN_DTL WHERE TXN_DTL.LS_TXN_ID = A.LS_TXN_ID) <> 0 THEN ( SELECT DISTINCT(TXN_DTL.ADDRESS_SEQ_NUM) FROM PS_RE_LS_TXN_DTL TXN_DTL WHERE TXN_DTL.LS_TXN_ID = A.LS_TXN_ID) ELSE B.ADDRESS_SEQ_NUM END ,A.DESCRLONG FROM PS_RE_LS_TXN A , PS_RE_LS B , PS_RE_LS_DTL_AMT C ,PS_RE_PROPERTY D , PS_RE_REGION_NAME E , PS_SET_CNTRL_REC F WHERE A.LS_KEY = B.LS_KEY AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.LS_TXN_ID = C.LS_TXN_ID AND B.PRIM_PROPERTY_ID = D.PROPERTY_ID AND B.REGION_CD = E.REGION_CD AND E.SETID = F.SETID AND F.RECNAME = 'RE_REGION_NAME' AND F.SETCNTRLVALUE = B.BUSINESS_UNIT AND %EffdtCheck(RE_REGION_NAME E_ED, E, %CurrentDateIn)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 LS_TXN_ID Number(25,0) DECIMAL(25) NOT NULL Lease Transaction ID
2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: BUS_UNIT_TBL_FS

3 LS_KEY Number(15,0) DECIMAL(15) NOT NULL Lease Key, Unique Key
4 TERM_ID Number(10,0) DECIMAL(10) NOT NULL Financial Term ID
5 TXN_GRP Character(1) VARCHAR2(1) NOT NULL Transaction Group
1=Base Rent
2=Security Deposit
3=Straightline Accounting
4=Operating Expense
5=Percent Rent
6=Miscellaneous Rent
7=Manual Fee
8=Lease Obligation
9=Lease Interest Expense
A=Prior Period Lease Obligation
B=Prior Period Lease Interest
C=Rent Expense
D=Lease Incentive
6 LS_TXN_SRC_ID Number(30,0) DECIMAL(30) NOT NULL Lease Transaction Source ID
7 SCHED_DATE Date(10) DATE Schedule Date
8 LEASE_OBLG Character(1) VARCHAR2(1) NOT NULL Lease Obligation either Payables or Receivables Lease
1=Payables
2=Receivables
9 LS_TXN_ORIG Character(1) VARCHAR2(1) NOT NULL Lease Transaction Origin , Recurring or Manual
1=Recurring
2=Manual
10 LS_TXN_TGT Character(1) VARCHAR2(1) NOT NULL Lease Transaction Target
1=Payables
2=Billing
11 SCHED_NUM Number(5,0) INTEGER NOT NULL Team sequence number
12 LS_TXN_STATUS Character(1) VARCHAR2(1) NOT NULL Lease Transaction Status
0=Draft
1=Pending
2=Approved
3=Cancelled
4=Hold
5=Processed
6=Errors
7=Processed Prior to Transfer
13 PREV_TXN_STATUS Character(1) VARCHAR2(1) NOT NULL Transaction Status before the transaction was put on hold.
1=Pending
2=Approved
3=Canceled
4=Hold
5=Processed
6=Errors
14 FROM_DATE Date(10) DATE From Date
15 TO_DATE Date(10) DATE To Date
16 PRIOR_PER_DIF Signed Number(13,2) DECIMAL(11,2) NOT NULL Prior Period Difference for Opex Audit
17 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
18 FMS_DTTM_STAMP DateTime(26) TIMESTAMP Specifies the date and time of the original entry.
19 FMS_OPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the original entry.
20 FMS_LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry.
21 FMS_LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry.
22 REGION_CD Character(10) VARCHAR2(10) NOT NULL Identifies the Sales Region. Populated on the Item Table to identify the Sales Region the Item is applied to.
23 LS_NBR Character(10) VARCHAR2(10) NOT NULL Lease Number, User Enterable or Generated Sequence Number
24 LEASE_NAME Character(60) VARCHAR2(60) NOT NULL Lease Name
25 LS_ADMIN Number(10,0) DECIMAL(10) NOT NULL Lease Administrator
26 PRTFL_MGR Number(10,0) DECIMAL(10) NOT NULL Portfolio Manager
27 PRINCIPAL_ID Character(15) VARCHAR2(15) NOT NULL Landlord/Tenant
28 SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
29 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
30 PRIM_PROPERTY_ID Number(10,0) DECIMAL(10) NOT NULL Primary Property
31 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
32 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
33 PROPERTY_NM Character(50) VARCHAR2(50) NOT NULL Used to store an Asset Property Name
34 DESCR Character(30) VARCHAR2(30) NOT NULL Description
35 OPRDEFNDESC Character(30) VARCHAR2(30) NOT NULL Description field for a user
36 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
37 DST_ALLOC_METHOD Character(1) VARCHAR2(1) NOT NULL Distribution Allocation Method
1=Percentage
2=Amount
3=Area
4=None
5=Quantity
38 INVOICE_ID Character(30) VARCHAR2(30) NOT NULL Invoice Number
39 MANUAL_SHIPTO_ID Character(10) VARCHAR2(10) NOT NULL Ship To Location
40 INVOICE_DT Date(10) DATE Invoice Date
41 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
42 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
43 DESCRLONG Long Character CLOB Long Description