AM_MTM_AF_LNVW

(SQL View)
 Index Back

AM MTM AF Detail

AM MTM AF Header

SELECT H1.DATE_SUBMITTED , H1.DTTM_STAMP , H1.ORIGINATOR_ID , 'INTFC_FIN' , F.INTFC_ID , F.INTFC_LINE_NUM , F.BUSINESS_UNIT , F.ASSET_ID , F.BOOK , F.INTFC_TYPE , F.TRANS_IN_OUT , F.LOAD_STATUS , F.APPROVAL_SW , F.TRANS_CODE , F.COST , F.CURRENCY_CD , F.TXN_COST , F.TXN_CURRENCY_CD , AST.DESCR , AST.TAG_NUMBER , AST.SERIAL_ID , AST.PROFILE_ID , AST.ASSET_TYPE , AST.ASSET_STATUS FROM PS_AM_MTM_AF_HDR H1 , PS_INTFC_FIN F , PS_ASSET AST WHERE H1.INTFC_ID=F.INTFC_ID AND AST.BUSINESS_UNIT=F.BUSINESS_UNIT AND AST.ASSET_ID=F.ASSET_ID UNION SELECT H2.DATE_SUBMITTED , H2.DTTM_STAMP , H2.ORIGINATOR_ID , 'INTFC_PHY_A' , A.INTFC_ID , A.INTFC_LINE_NUM , A.BUSINESS_UNIT , A.ASSET_ID , ' ' , A.INTFC_TYPE , A.TRANS_IN_OUT , A.LOAD_STATUS , A.APPROVAL_SW , ' ' , 0 , ' ' , 0 , ' ' , AST.DESCR , AST.TAG_NUMBER , AST.SERIAL_ID , AST.PROFILE_ID , AST.ASSET_TYPE , AST.ASSET_STATUS FROM PS_AM_MTM_AF_HDR H2 , PS_INTFC_PHY_A A , PS_ASSET AST WHERE H2.INTFC_ID=A.INTFC_ID AND AST.BUSINESS_UNIT=A.BUSINESS_UNIT AND AST.ASSET_ID=A.ASSET_ID UNION SELECT H3.DATE_SUBMITTED , H3.DTTM_STAMP , H3.ORIGINATOR_ID , 'INTFC_PHY_B' , B.INTFC_ID , B.INTFC_LINE_NUM , B.BUSINESS_UNIT , B.ASSET_ID , ' ' , B.INTFC_TYPE , ' ' , B.LOAD_STATUS , B.APPROVAL_SW , ' ' , 0 , ' ' , 0 , ' ' , AST.DESCR , AST.TAG_NUMBER , AST.SERIAL_ID , AST.PROFILE_ID , AST.ASSET_TYPE , AST.ASSET_STATUS FROM PS_AM_MTM_AF_HDR H3 , PS_INTFC_PHY_B B , PS_ASSET AST WHERE H3.INTFC_ID=B.INTFC_ID AND AST.BUSINESS_UNIT=B.BUSINESS_UNIT AND AST.ASSET_ID=B.ASSET_ID UNION SELECT H1.DATE_SUBMITTED , H1.DTTM_STAMP , H1.ORIGINATOR_ID , 'INTFC_FIN' , F.INTFC_ID , F.INTFC_LINE_NUM , F.BUSINESS_UNIT , F.ASSET_ID , F.BOOK , F.INTFC_TYPE , F.TRANS_IN_OUT , F.LOAD_STATUS , F.APPROVAL_SW , F.TRANS_CODE , F.COST , F.CURRENCY_CD , F.TXN_COST , F.TXN_CURRENCY_CD , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' FROM PS_AM_MTM_AF_HDR H1 , PS_INTFC_FIN F WHERE H1.INTFC_ID=F.INTFC_ID AND NOT EXISTS ( SELECT 1 FROM PS_ASSET AST2 WHERE AST2.BUSINESS_UNIT=F.BUSINESS_UNIT AND AST2.ASSET_ID=F.ASSET_ID) UNION SELECT H2.DATE_SUBMITTED , H2.DTTM_STAMP , H2.ORIGINATOR_ID , 'INTFC_PHY_A' , A.INTFC_ID , A.INTFC_LINE_NUM , A.BUSINESS_UNIT , A.ASSET_ID , ' ' , A.INTFC_TYPE , A.TRANS_IN_OUT , A.LOAD_STATUS , A.APPROVAL_SW , ' ' , 0 , ' ' , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' FROM PS_AM_MTM_AF_HDR H2 , PS_INTFC_PHY_A A WHERE H2.INTFC_ID=A.INTFC_ID AND NOT EXISTS ( SELECT 1 FROM PS_ASSET AST2 WHERE AST2.BUSINESS_UNIT=A.BUSINESS_UNIT AND AST2.ASSET_ID=A.ASSET_ID) UNION SELECT H3.DATE_SUBMITTED , H3.DTTM_STAMP , H3.ORIGINATOR_ID , 'INTFC_PHY_B' , B.INTFC_ID , B.INTFC_LINE_NUM , B.BUSINESS_UNIT , B.ASSET_ID , ' ' , B.INTFC_TYPE , ' ' , B.LOAD_STATUS , B.APPROVAL_SW , ' ' , 0 , ' ' , 0 , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' FROM PS_AM_MTM_AF_HDR H3 , PS_INTFC_PHY_B B , PS_ASSET AST WHERE H3.INTFC_ID=B.INTFC_ID AND NOT EXISTS ( SELECT 1 FROM PS_ASSET AST2 WHERE AST2.BUSINESS_UNIT=B.BUSINESS_UNIT AND AST2.ASSET_ID=B.ASSET_ID)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 DATE_SUBMITTED Date(10) DATE Date Submitted
2 DTTM_STAMP 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.
3 ORIGINATOR_ID Character(30) VARCHAR2(30) NOT NULL Originator
4 RECNAME Character(15) VARCHAR2(15) NOT NULL Record (Table) Name (see PSRECDEFN).
5 INTFC_ID Number(8,0) INTEGER NOT NULL Interface ID
6 INTFC_LINE_NUM Number(8,0) INTEGER NOT NULL Interface Line Number
7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
8 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
9 BOOK Character(10) VARCHAR2(10) NOT NULL Asset Book Name
10 INTFC_TYPE Character(3) VARCHAR2(3) NOT NULL Trans Load Type
11 TRANS_IN_OUT Character(1) VARCHAR2(1) NOT NULL Transaction In/Out
I=In
M=IUT Proceeds Markup
O=Out
12 LOAD_STATUS Character(3) VARCHAR2(3) NOT NULL /* -> JMZ. PSOFT. 11-OCT-2002 */ New Status were added : HLD and INP for Asset Management. /* <- JMZ. PSOFT. 11-OCT-2002 */
CON=Consolidated
DON=Loaded
ERR=Errored
EXC=Excluded
HLD=On Hold
INP=In Process
NEW=Pending
PGI=PO Grp Incomplete
REP=Replaced
UNI=Unitized
13 APPROVAL_SW Character(1) VARCHAR2(1) NOT NULL Auto Approval Status
14 TRANS_CODE Character(5) VARCHAR2(5) NOT NULL Transaction Code
15 COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Total Cost
16 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
17 TXN_COST Signed Number(28,3) DECIMAL(26,3) NOT NULL Transaction Cost
18 TXN_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Transaction Currency
19 DESCR Character(30) VARCHAR2(30) NOT NULL Description
20 TAG_NUMBER Character(12) VARCHAR2(12) NOT NULL Tag Number
21 SERIAL_ID Character(20) VARCHAR2(20) NOT NULL Serial ID
22 PROFILE_ID Character(10) VARCHAR2(10) NOT NULL Asset Profile ID
23 ASSET_TYPE Character(3) VARCHAR2(3) NOT NULL Asset Type
010=IT Hardware
020=IT Software
040=Equipment
050=Property
060=Fleet
070=Machinery
080=Furniture
090=Facility
100=Intangible
200=Asset Retirement Obligations
999=Other
24 ASSET_STATUS Character(1) VARCHAR2(1) NOT NULL Asset Status
A=Received (Not in Service)
B=Budgeted
C=Commitment
D=Disposed
I=In Service
M=Suspended
R=Requisitioned
T=Transferred
W=Work In Progress