ADV_NOTICE_VW2

(SQL View)
 Index Back

Stl to FG Mapping

Mapping for cash transactions using STL Instructions.

SELECT CF.TR_SOURCE_CD ,CF.SOURCE_BUS_UNIT ,CF.TR_SOURCE_ID ,CF.BUSINESS_DATE ,CF.CASH_FLOW_LINE ,CF.CASH_FLOW_LEG , 'CM' ,CF.TR_SOURCE_ID ,CF.SOURCE_BUS_UNIT ,CF.PYMNT_METHOD ,CF.FORMAT_ID ,CF.AMOUNT ,CF.CURRENCY_CD ,CF.BANK_SETID ,CF.BANK_CD ,CF.BANK_ACCT_KEY ,CF.BUSINESS_DATE , ' ' , ' ' , 'BENE' ,' ' ,S.NAME1 ,S.NAME1_AC ,S.NAME2 ,S.BANK_ID_QUAL ,CF.BNK_ID_NBR ,CF.BANK_ACCOUNT_NUM ,' ' ,' ' ,S.BNK_ID_NBR ,S.BRANCH_ID ,S.BANK_ACCT_TYPE ,S.BANK_ACCOUNT_NUM ,S.CHECK_DIGIT ,S.DFI_ID_QUAL ,S.DFI_ID_NUM ,' ' ,S.BENEFICIARY_BANK ,' ' ,S.BENEF_BRANCH ,S.IBAN_CHECK_DIGIT ,S.IBAN_ID ,%subrec(ADDRESS_SBR,S) ,' ' ,' ' ,' ' ,' ' ,' ' ,H.PRENOTE_FLAG ,S.EMAILID ,S.EMAILID2 ,S.REPORT_DEFN_ID ,S.TMPLDEFN_ID ,S.LANGUAGE_CD ,S.MANDATE_BU ,S.PMT_MANDATE_ID ,S.SEQ_NUM , S.SEQUENCE_TYPE FROM PS_CASH_FLOW_TR CF ,PS_TR_WR_DETAIL S ,PS_TR_WR_HEADER H WHERE H.BUSINESS_UNIT = S.BUSINESS_UNIT AND H.TR_SOURCE_ID = S.TR_SOURCE_ID AND S.TR_SOURCE_CD=CF.TR_SOURCE_CD AND S.BUSINESS_UNIT=CF.SOURCE_BUS_UNIT AND S.TR_SOURCE_ID=CF.TR_SOURCE_ID AND S.CASH_FLOW_LINE=CF.CASH_FLOW_LINE AND S.CASH_FLOW_LEG=CF.CASH_FLOW_LEG AND CF.TR_SOURCE_CD ='W' AND CF.PYMNT_SELCT_STATUS = 'X' AND CF.FORMAT_ID LIKE 'SEPA_DD%'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 TR_SOURCE_CD Character(1) VARCHAR2(1) NOT NULL Note: Changes to Translate Values must also be made to TRA_SOURCE_CD and SOURCE_TYPE. These 3 fields must be kept in sync. Values that are inactive are for use in TR Accounting only
B=BSP
D=Deals
E=Deal Fees
F=Facility Fees
H=Hedges
I=Interest
M=Deal: Estimated Maturity Accr
N=Netted Deal
O=Other
P=Investment Pools
R=EFT Fees
S=Securities
T=LC Fees
W=EFT Requests
X=Bank Transfers
2 SOURCE_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Source Bus Unit
3 TR_SOURCE_ID Character(12) VARCHAR2(12) NOT NULL An internal work field that represents a unique identifier for records depicting a given treasury po
4 BUSINESS_DATE Date(10) DATE Represents a date upon which business is conducted within a given company in a given country.
5 CASH_FLOW_LINE Number(15,0) DECIMAL(15) NOT NULL A sequential number that indicates the time order sequence of a given deal transaction's cash flows.
6 CASH_FLOW_LEG Number(1,0) SMALLINT NOT NULL A sequential number that indicates the relationship between a cash flow and its corresponding deal transaction leg
7 PMT_SOURCE Character(10) VARCHAR2(10) NOT NULL Source System Name
8 SRC_REF_ID Character(20) VARCHAR2(20) NOT NULL Source ref id
9 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
10 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
11 FORMAT_ID Character(10) VARCHAR2(10) NOT NULL Format ID
12 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
14 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
15 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
16 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
17 PYMNT_DT Date(10) DATE Payment Date
18 PMT_TYPE Character(4) VARCHAR2(4) NOT NULL Payment type
BAX=Bank Transfer
BON=Bonus
COLL=Collection
DEAL=Deal Cash Flow
EFTR=EFT Request
EXAD=Exp Advance
EXPN=Expense Sheet
FEE=Fee
GR=General Recipient Deduction
IR=Individual Recipient Deduction
NP=Net Pay Distribution
OTHR=Other
SAL=Salary
VCHR=Voucher
19 PAY_IMMEDIATELY Character(1) VARCHAR2(1) NOT NULL Pay Immediately
20 PARTY_ID_TYPE Character(4) VARCHAR2(4) NOT NULL Party Type
BENE=Beneficiary
CPTY=Counterparty
CUST=Customer
EMPL=Employee
VNDR=Supplier
21 PARTY_ID Character(30) VARCHAR2(30) NOT NULL Payee ID
22 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
23 NAME1_AC Character(40) VARCHAR2(40) NOT NULL Name 1 Alternate Char Set
24 NAME2 Character(40) VARCHAR2(40) NOT NULL Name 2
25 BANK_ID_QUAL Character(3) VARCHAR2(3) NOT NULL "A bank/counterparty attribute that determines

Prompt Table: BNK_ID_QUAL_VW

26 FROM_BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL From Bank ID Number
27 FROM_BNK_ACCT_NUM Character(35) VARCHAR2(35) NOT NULL From Bank Account #
28 FROM_DFI_ID_NUM Character(12) VARCHAR2(12) NOT NULL A banking industry convention identifier utilized to drive electronic settlements processing for a given bank/coun
29 FROM_DFI_ID_QUAL Character(2) VARCHAR2(2) NOT NULL A banking industry convention identifier utilized to drive data validation for a given bank/counterparty.
01=Transit Number
02=Swift ID
03=CHIPS Participant ID
04=Canadian Bank Branch/Institute
05=CHIPS Universal ID
ZZ=Mutually Defined
30 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
31 BRANCH_ID Character(10) VARCHAR2(10) NOT NULL The local branch office identifier associated with a given bank/counterparty.
32 BANK_ACCT_TYPE Character(2) VARCHAR2(2) NOT NULL An account attribute that describes the ostensible purposes of a given bank/counterparty account.
01=Time Deposit
03=Checking Account
05=Stock
06=Bond
07=Life Insurance Value
08=Retirement Account
10=Business Account
11=Trust Fund Account
12=Stock & Bond Account
13=Life Insurance Acct
CA=Current Account
CH=Charges Account
DA=Demand Deposit
LN=Loan
RD=Return Items on DDA
RS=Return Items-Savings
SA=Settlement Account
SV=Savings Account
Z=Mutually Defined
33 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
34 CHECK_DIGIT Character(2) VARCHAR2(2) NOT NULL Check Digit
35 DFI_ID_QUAL Character(2) VARCHAR2(2) NOT NULL A banking industry convention identifier utilized to drive data validation for a given bank/counterparty.
01=Transit Number
02=Swift ID/BIC
03=CHIPS Participant ID
04=Canadian Bank Branch/Institute
05=CHIPS Universal ID
ZZ=Mutually Defined
36 DFI_ID_NUM Character(12) VARCHAR2(12) NOT NULL A banking industry convention identifier utilized to drive electronic settlements processing for a given bank/coun
37 BANK_NAME Character(30) VARCHAR2(30) NOT NULL Bank Name
38 BANK_NAME_AC Character(30) VARCHAR2(30) NOT NULL Alternate Character Set field for BENEFICIARY_BANK
39 BRANCH_DESCR Character(30) VARCHAR2(30) NOT NULL Branch Description
40 BRANCH_NAME_AC Character(30) VARCHAR2(30) NOT NULL Branch Alternate Char
41 IBAN_CHECK_DIGIT Character(2) VARCHAR2(2) NOT NULL IBAN Check Digit
42 IBAN_ID Character(34) VARCHAR2(34) NOT NULL IBAN - Internation Bank Account Number is used in European countries to uniquely identify Bank accounts across borders
43 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

44 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
45 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
46 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
47 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
48 CITY Character(30) VARCHAR2(30) NOT NULL City
49 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
50 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
51 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
52 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
53 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
54 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
55 COUNTY Character(30) VARCHAR2(30) NOT NULL County
56 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

57 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
58 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
59 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

60 CONTACT_NAME Character(50) VARCHAR2(50) NOT NULL The individual contact name associated with a given bank/counterparty.
61 CONTACT_TYPE Character(1) VARCHAR2(1) NOT NULL An attribute that describes the specific purpose of a given bank/counterparty contact person.
B=Billing Contact
C=Contract Collaborator
D=Commercial Paper Contact
E=External Contact
F=Cash Forecast
G=General
I=Internal Corporate Contact
L=Line of Credit Contact
M=Executive Management
O=Investment Pool Contact
P=Accounts Payable
R=Broker
S=Sales Contact
V=Service Contact
W=Warehousing/Shipping Contact
62 CONTACT_TITLE Character(35) VARCHAR2(35) NOT NULL The professional title for a given bank/counterparty contact person.
63 PREFERRED_LANGUAGE Character(3) VARCHAR2(3) NOT NULL Preferred Language
ARA=Arabic
CFR=Canadian French
CZE=Czech
DAN=Danish
DUT=Dutch
E=English
ENG=English
ESP=Spanish
F=French
FIN=Finnish
FRA=French
GER=German
HUN=Hungarian
INE=International English
ITA=Italian
JPN=Japanese
KOR=Korean
NOR=Norwegian
POL=Polish
POR=Portuguese
RUS=Russian
SVE=Swedish
THA=Thai
UKE=UK English
ZHS=Simplified Chinese
ZHT=Traditional Chinese
64 URL Character(254) VARCHAR2(254) NOT NULL Internet URL (Universal Resource Locator)
65 PRENOTE_FLAG Character(1) VARCHAR2(1) NOT NULL Prenotification Flag
66 EMAILID Character(70) VARCHAR2(70) NOT NULL A user's E-mail address
67 EMAILID2 Character(70) VARCHAR2(70) NOT NULL Email ID 2
68 REPORT_DEFN_ID Character(12) VARCHAR2(12) NOT NULL Report Name (see PSXPRPTDEFN).
69 TMPLDEFN_ID Character(30) VARCHAR2(30) NOT NULL Template ID (see PSXPTMPLDEFN).
70 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
71 MANDATE_BU Character(5) VARCHAR2(5) NOT NULL Mandate Business Unit
72 PMT_MANDATE_ID Character(15) VARCHAR2(15) NOT NULL Mandate direct debit
73 SEQ_NUM Number(3,0) SMALLINT NOT NULL Sequence
74 SEQUENCE_TYPE Character(1) VARCHAR2(1) NOT NULL Identifies the direct debit sequence, eg, first, recurrent, final or one-off.
F=First Payment
N=Final Payment
O=OneOff Payment
R=Recurring Payment