EX_ER_PYM_VW(SQL View) |
Index Back |
|---|---|
EX_ER_PYM_VW |
| SELECT DISTINCT PP.BANK_SETID ,PP.BANK_CD ,PP.BANK_ACCT_KEY ,PP.PYMNT_ID ,PP.PAYCHECK_NBR ,PP.EMPLID ,PPD.FIRST_NAME ,PPD.LAST_NAME ,PP.SHEET_ID ,PH.SHEET_NAME ,PP.PYMNT_METHOD ,PD.BENEFICIARY_BANK ,PT.REMIT_BANK_ACCOUNT ,SUM(PP.PAID_AMT) FROM PS_PAYMENT_TBL PT LEFT OUTER JOIN PS_EX_EE_PYMNT_DTL PD ON PD.BANK_ACCOUNT_NUM = PT.REMIT_BANK_ACCOUNT AND PD.EMPLID = PT.EMPLID ,PS_EX_SHEET_PYMNT PP , PS_EX_SHEET_HDR PH , PS_PERSONAL_DATA PPD WHERE PPD.EMPLID = PP.EMPLID AND PH.SHEET_ID = PP.SHEET_ID AND PP.EX_PYMNT_TYPE <> 'V' AND PP.BANK_SETID = PT.BANK_SETID AND PP.BANK_CD = PT.BANK_CD AND PP.BANK_ACCT_KEY = PT.BANK_ACCT_KEY AND PP.PYMNT_ID = PT.PYMNT_ID AND PP.EX_PYMNT_DEST = 'A' AND PD.EMPL_RCD=( SELECT MAX(PDB.EMPL_RCD) FROM PS_EX_EE_PYMNT_DTL PDB WHERE PDB.EMPLID=PT.EMPLID AND PDB.BANK_ACCOUNT_NUM = PT.REMIT_BANK_ACCOUNT) AND PD.BANK_ACCT_SEQ_NBR=( SELECT MAX(PDC.BANK_ACCT_SEQ_NBR) FROM PS_EX_EE_PYMNT_DTL PDC WHERE PDC.EMPLID=PT.EMPLID AND PDC.BANK_ACCOUNT_NUM = PT.REMIT_BANK_ACCOUNT AND PDC.EMPL_RCD = PD.EMPL_RCD) GROUP BY PP.BANK_SETID ,PP.BANK_CD ,PP.BANK_ACCT_KEY ,PP.PYMNT_ID ,PP.PAYCHECK_NBR ,PP.EMPLID ,PPD.FIRST_NAME , PPD.LAST_NAME , PP.SHEET_ID ,PH.SHEET_NAME ,PP.PYMNT_METHOD ,PD.BENEFICIARY_BANK ,PT.REMIT_BANK_ACCOUNT UNION ALL SELECT DISTINCT PP.BANK_SETID ,PP.BANK_CD ,PP.BANK_ACCT_KEY ,PP.PYMNT_ID ,PP.PAYCHECK_NBR ,PP.EMPLID ,PPD.FIRST_NAME ,PPD.LAST_NAME ,PP.SHEET_ID ,PH.SHEET_NAME ,PP.PYMNT_METHOD ,' ' ,' ' ,SUM(PP.PAID_AMT) FROM PS_EX_SHEET_PYMNT PP , PS_EX_SHEET_HDR PH , PS_PERSONAL_DATA PPD WHERE PPD.EMPLID = PP.EMPLID AND PH.SHEET_ID = PP.SHEET_ID AND PP.EX_PYMNT_TYPE <> 'V' AND PP.EX_PYMNT_DEST = 'P' GROUP BY PP.BANK_SETID,PP.BANK_CD ,PP.BANK_ACCT_KEY ,PP.PYMNT_ID ,PP.PAYCHECK_NBR ,PP.EMPLID ,PPD.FIRST_NAME, PPD.LAST_NAME, PP.SHEET_ID ,PH.SHEET_NAME ,PP.PYMNT_METHOD |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  BANK_SETID |
Character(5) | VARCHAR2(5) NOT NULL | The PeopleSoft tableset ID associated with a given bank/counterparty. |
| 2 | BANK_CD |
Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 3 | 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 |
| 4 | PYMNT_ID |
Character(10) | VARCHAR2(10) NOT NULL | Payment Number |
| 5 | PAYCHECK_NBR |
Number(15,0) | DECIMAL(15) NOT NULL | Paycheck Number |
| 6 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 7 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 8 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 9 | SHEET_ID | Character(10) | VARCHAR2(10) NOT NULL | Report ID |
| 10 | SHEET_NAME | Character(30) | VARCHAR2(30) NOT NULL | Report Description |
| 11 | PYMNT_METHOD | Character(3) | VARCHAR2(3) NOT NULL |
Payment Method
ACH=Automated Clearing House BEF=Draft - Customer EFT BOO=Draft - Customer Initiated CHK=System Check D=Deposit DD=Direct Debit DFT=Draft - Supplier Initiated DRA=Draft EFT=Electronic Funds Transfer GE=Giro - EFT GM=Giro - Manual LC=Letter of Credit MAN=Manual Check TRW=Treasury Wire WIR=Wire Transfer |
| 12 | BENEFICIARY_BANK | Character(30) | VARCHAR2(30) NOT NULL | The bank/counterparty associated with a given beneficiary. |
| 13 | REMIT_BANK_ACCOUNT | Character(35) | VARCHAR2(35) NOT NULL | Bank Account Number |
| 14 | PAID_AMT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Paid Amount |