INTFC_ERR_VW

(SQL View)
 Index Back

INTFC_ERR_VW

Created View to store multiple errors for a transaction after running AMIF1000

SELECT DISTINCT B.BUSINESS_UNIT , B.INTFC_ID , B.INTFC_LINE_NUM , B.PROCESS_INSTANCE , B.ASSET_ID , B.BOOK , B.ERRNUM , B.INTFC_TYPE , B.MESSAGE_NBR , B.MESSAGE_SET_NBR FROM %Table(INTFC_ERROR) B , PS_AM_INTFC_TXN_VW C WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.INTFC_ID = C.INTFC_ID AND B.INTFC_LINE_NUM = C.INTFC_LINE_NUM AND B.PROCESS_INSTANCE = C.PROCESS_INSTANCE AND B.ASSET_ID = C.ASSET_ID AND B.BOOK = C.BOOK UNION SELECT DISTINCT B.BUSINESS_UNIT , B.INTFC_ID , B.INTFC_LINE_NUM , B.PROCESS_INSTANCE , B.ASSET_ID , B.BOOK , 1 , B.INTFC_TYPE , B.MESSAGE_NBR , B.MESSAGE_SET_NBR FROM PS_INTFC_FIN B WHERE B.MESSAGE_SET_NBR <> 0 AND B.MESSAGE_NBR <> 0 AND NOT EXISTS ( SELECT 'X' FROM PS_INTFC_ERROR C WHERE C.PROCESS_INSTANCE = B.PROCESS_INSTANCE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INTFC_ID Number(8,0) INTEGER NOT NULL Interface ID
3 INTFC_LINE_NUM Number(8,0) INTEGER NOT NULL Interface Line Number
4 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
5 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
6 BOOK Character(10) VARCHAR2(10) NOT NULL Asset Book Name
7 ERRNUM Number(5,0) INTEGER NOT NULL Error Number
8 INTFC_TYPE Character(3) VARCHAR2(3) NOT NULL Trans Load Type
9 MESSAGE_NBR Number(5,0) INTEGER NOT NULL Message Number. This field refers to the Message Number in the Message Catalog.
10 MESSAGE_SET_NBR Number(5,0) INTEGER NOT NULL Message Set Number. This field refers to the Message Set Number in the Message Catalog.