AR_STATEMNT_VW

(SQL View)
 Index Back

Recent Statements by BU

Statements by BU

SELECT S.CRSPD_SETID AS CRSPD_SETID , S.CRSPD_CUST_ID AS CRSPD_CUST_ID , SD.BUSINESS_UNIT AS BUSINESS_UNIT , S.ST_ID_NUM AS ST_ID_NUM , S.ST_DT AS ST_DT , SUM(SD.BAL_AMT) AS BAL_AMT , SD.BAL_CURRENCY AS BAL_CURRENCY , 0 AS TOTAL_ROW_COUNT , %CurrentDateTimeIn AS ROW_ADDED_DTTM , %OperatorId AS ROW_ADDED_OPRID , %CurrentDateTimeIn AS ROW_LASTMANT_DTTM , %OperatorId AS ROW_LASTMANT_OPRID , 0 AS SYNCID , %CurrentDateTimeIn AS SYNCDTTM FROM PS_STMT_CUST S , PS_STMT_CUST_DTL SD WHERE S.CRSPD_SETID = SD.CRSPD_SETID AND S.CRSPD_CUST_ID = SD.CRSPD_CUST_ID AND S.ST_ID_NUM = SD.ST_ID_NUM GROUP BY S.CRSPD_SETID, S.CRSPD_CUST_ID, SD.BUSINESS_UNIT, S.ST_ID_NUM, S.ST_DT, SD.BAL_CURRENCY UNION ALL SELECT DISTINCT S1.CRSPD_SETID AS CRSPD_SETID , S1.CRSPD_CUST_ID AS CRSPD_CUST_ID , SD1.BUSINESS_UNIT AS BUSINESS_UNIT , S1.ST_ID_NUM AS ST_ID_NUM , S1.ST_DT AS ST_DT , 0 AS BAL_AMT , SD1.BALFWD_BAL_CUR AS BAL_CURRENCY , 0 AS TOTAL_ROW_COUNT , %CurrentDateTimeIn AS ROW_ADDED_DTTM , %OperatorId AS ROW_ADDED_OPRID , %CurrentDateTimeIn AS ROW_LASTMANT_DTTM , %OperatorId AS ROW_LASTMANT_OPRID , 0 AS SYNCID , %CurrentDateTimeIn AS SYNCDTTM FROM PS_STMT_CUST S1 , PS_STMT_CUST_BFWD SD1 WHERE S1.CRSPD_SETID = SD1.CRSPD_SETID AND S1.CRSPD_CUST_ID = SD1.CRSPD_CUST_ID AND S1.ST_ID_NUM = SD1.ST_ID_NUM AND NOT EXISTS ( SELECT SS.ST_ID_NUM FROM PS_STMT_CUST_DTL SS WHERE S1.CRSPD_SETID = SS.CRSPD_SETID AND S1.CRSPD_CUST_ID = SS.CRSPD_CUST_ID AND S1.ST_ID_NUM = SS.ST_ID_NUM)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CRSPD_SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 CRSPD_CUST_ID Character(15) VARCHAR2(15) NOT NULL Correspondence Customer
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
4 ST_ID_NUM Number(8,0) INTEGER NOT NULL Statement Number
5 ST_DT Date(10) DATE Specifies the date a statement (Receivables) is generated. This date is maintained by the Statements SQR.
6 BAL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Item Balance
7 BAL_CURRENCY Character(3) VARCHAR2(3) NOT NULL Currency Code - Bal Amount
8 TOTAL_ROW_COUNT Number(8,0) INTEGER NOT NULL Total Row Count
9 ROW_ADDED_DTTM DateTime(26) TIMESTAMP Added Date/Time
10 ROW_ADDED_OPRID Character(30) VARCHAR2(30) NOT NULL Added By
11 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP Last Modified Date/Time
12 ROW_LASTMANT_OPRID Character(30) VARCHAR2(30) NOT NULL Last Maintained By
13 SYNCID Number(10,0) DECIMAL(10) NOT NULL The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle.
14 SYNCDTTM DateTime(26) TIMESTAMP Synchronization Last Update Date Time