SSF_DIRDEP_VW (SQL View)	Index Back
Direct Deposit View Fetch data from direct depoit refund and bank account record with the sql attached to the view

SSF_DIRDEP_VW

(SQL View)

Direct Deposit View

Fetch data from direct depoit refund and bank account record with the sql attached to the view

SELECT sbr.COMMON_ID , sbr.SA_ID_TYPE , sbr.EFFDT , sbr.SSF_BANK_ACCT_ID , sbr.PRIORITY , sbr.DEPOSIT_TYPE , sba.SSF_ACCT_NICKNAME , CASE WHEN sbr.DEPOSIT_TYPE = 'A' THEN sbr.DEPOSIT_AMT WHEN sbr.DEPOSIT_TYPE = 'P' THEN sbr.AMOUNT_PCT END DEPOSIT_AMT ,sba.SSF_BNK_ACCT_NUM , sbr.EFFSEQ ,sbr.SCC_ROW_ADD_OPRID ,sbr.SCC_ROW_ADD_DTTM FROM PS_SSF_BNKACCT_REF sbr , PS_SSF_BANK_ACCT sba WHERE sbr.common_id = sba.common_id AND sbr.sa_id_type = 'P' AND sbr.ssf_bank_acct_id = sba.ssf_bank_acct_id AND sbr.acct_status = 'A' AND sbr.EFFDT = ( SELECT MAX (sbr1.EFFDT) FROM PS_SSF_BNKACCT_REF sbr1 WHERE sbr1.common_id = sbr.common_id AND sbr1.sa_id_type = sbr.sa_id_type ) AND sbr.EFFSEQ = ( SELECT MAX(sbr2.EFFSEQ) FROM PS_SSF_BNKACCT_REF sbr2 WHERE sbr2.common_id = sbr.common_id AND sbr2.sa_id_type = sbr.sa_id_type AND sbr2.EFFDT = sbr.EFFDT ) AND sba.common_id = sbr.common_id AND sba.SA_ID_TYPE = sbr.SA_ID_TYPE AND sba.SSF_BANK_ACCT_ID = sbr.SSF_BANK_ACCT_ID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value

2 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person

3 EFFDT Date(10) DATE Effective Date
Default Value: %date

4 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number

5 PRIORITY Number(3,0) SMALLINT NOT NULL Priority

6 SSF_DD_TYPE Character(1) VARCHAR2(1) NOT NULL Distribution Type
A=Amount
B=Balance
P=Percent

7 SSF_BNK_NICKNAME Character(55) VARCHAR2(55) NOT NULL Bank Nickname Field
Prompt Table: SSF_BNK_NICK_VW

8 SSF_AMOUNT Signed Number(19,2) DECIMAL(17,2) NOT NULL Amount

9 SSF_BNK_ACCT_NUM Character(44) VARCHAR2(44) NOT NULL Bank Account Number

10 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence

11 SCC_ROW_ADD_OPRID Character(30) VARCHAR2(30) NOT NULL Created By

12 SCC_ROW_ADD_DTTM DateTime(26) TIMESTAMP Created

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	COMMON_ID	Character(11)	VARCHAR2(11) NOT NULL	Common ID to store Personal ID / Ext Org ID value
2	SA_ID_TYPE	Character(1)	VARCHAR2(1) NOT NULL	ID Type O=Organization P=Person
3	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
4	SEQNUM	Number(3,0)	SMALLINT NOT NULL	Sequence Number
5	PRIORITY	Number(3,0)	SMALLINT NOT NULL	Priority
6	SSF_DD_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Distribution Type A=Amount B=Balance P=Percent
7	SSF_BNK_NICKNAME	Character(55)	VARCHAR2(55) NOT NULL	Bank Nickname Field Prompt Table: SSF_BNK_NICK_VW
8	SSF_AMOUNT	Signed Number(19,2)	DECIMAL(17,2) NOT NULL	Amount
9	SSF_BNK_ACCT_NUM	Character(44)	VARCHAR2(44) NOT NULL	Bank Account Number
10	EFFSEQ	Number(3,0)	SMALLINT NOT NULL	Effective Sequence
11	SCC_ROW_ADD_OPRID	Character(30)	VARCHAR2(30) NOT NULL	Created By
12	SCC_ROW_ADD_DTTM	DateTime(26)	TIMESTAMP	Created