SMT_CANCEL_VW2

(SQL View)
 Index Back

Multiple P-Terms Cancel View


SELECT A.RUN_CNTL_ID , A.PROCESS_INSTANCE , A.SMT_GROUP_ID , A.BUSINESS_UNIT , A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY , A.REMIT_SETID , A.REMIT_VENDOR , A.ADDRESS_SEQ_NUM , A.VNDR_LOC , A.PYMNT_HANDLING_CD , A.CURRENCY_PYMNT , SUM(A.PYMNT_GROSS_AMT) , B.SMT_DATE , B.REQUEST_DT , ' ' FROM PS_SMT_STG_DTL A , PS_SMT_STG_HDR B WHERE A.RUN_CNTL_ID = B.RUN_CNTL_ID AND A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.SMT_STATUS IN ('U', 'I') AND NOT EXISTS ( SELECT 'X' FROM PS_SMT_CANCEL_VW1 C WHERE C.RUN_CNTL_ID = A.RUN_CNTL_ID AND C.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND C.SMT_GROUP_ID = A.SMT_GROUP_ID) GROUP BY A.RUN_CNTL_ID, A.PROCESS_INSTANCE, A.SMT_GROUP_ID, A.BUSINESS_UNIT , A.BANK_SETID, A.BANK_CD, A.BANK_ACCT_KEY, A.REMIT_SETID, A.REMIT_VENDOR, A.ADDRESS_SEQ_NUM, A.VNDR_LOC, A.PYMNT_HANDLING_CD, A.CURRENCY_PYMNT, B.SMT_DATE, B.REQUEST_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID
2 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
3 SMT_GROUP_ID Character(10) VARCHAR2(10) NOT NULL Group ID
4 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
5 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
6 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
7 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank
8 REMIT_SETID Character(5) VARCHAR2(5) NOT NULL Remit SetID
9 REMIT_VENDOR Character(10) VARCHAR2(10) NOT NULL Remit Vendor
10 ADDRESS_SEQ_NUM Number(5,0) INTEGER NOT NULL Address Sequence Number
11 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
12 PYMNT_HANDLING_CD Character(2) VARCHAR2(2) NOT NULL Payment Handling
13 CURRENCY_PYMNT Character(3) VARCHAR2(3) NOT NULL Payment Currency
14 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
15 SMT_DATE Date(10) DATE Date
16 REQUEST_DT Date(10) DATE Request Date
17 DUMMY_FIELD Character(1) VARCHAR2(1) NOT NULL Dummy Field for Hiding Groups