PMT_M_CPTY_A_VW

(SQL View)
 Index Back

Mandate search


SELECT A.BUSINESS_UNIT , A.PMT_MANDATE_ID , B.PMT_MANDATE_REF , B.PMT_MANDATE_DT , A.PMT_MANDATE_STATUS , B.PMT_APPR_STATUS , B.PMT_AMEND_FLAG , A.PMT_CANCEL_REMARK , A.PMT_CANCELLED_BY , A.CANCEL_DT , A.CONTRACT_NUM , A.PMT_CONTRACT_DSCR , B.PMT_CRED_NAME , B.PMT_CREDITOR_ID , B.COUNTRY , B.ADDRESS1 , B.ADDRESS2 , B.ADDRESS3 , B.ADDRESS4 , B.CITY , B.NUM1 , B.NUM2 , B.HOUSE_TYPE , B.ADDR_FIELD1 , B.ADDR_FIELD2 , B.ADDR_FIELD3 , B.COUNTY , B.STATE , B.POSTAL , B.GEO_CODE , B.IN_CITY_LIMIT , B.PMT_DEBTOR_TYPE , B.PMT_DEBTOR_NAME , B.PMT_DEBTOR_ID , B.PMT_DEBTOR_PARTY , B.BANK_SETID , B.BANK_CD , B.COUNTRY_OTHER , B.ADDRESS1_OTHER , B.ADDRESS2_OTHER , B.ADDRESS3_OTHER , B.ADDRESS4_OTHER , B.CITY_OTHER , B.COUNTY_OTHER , B.STATE_OTHER , B.POSTAL_OTHER , B.NUM1_OTHER , B.NUM2_OTHER , B.HOUSE_TYPE_OTHER , B.ADDR_FIELD1_OTHER , B.ADDR_FIELD2_OTHER , B.ADDR_FIELD3_OTHER , B.IN_CITY_LMT_OTHER , B.GEO_CODE_OTHER , B.IBAN_ID , B.DFI_ID_QUAL , B.DFI_ID_NUM , B.PMT_PAYMENT_TYPE , A.USERID , A.PMT_SIGNED_LOC , A.PMT_SIGNED_DT , B.PMT_APPR_DT , B.NAME1 , B.REASON_CD , B.PMT_ORIG_CRED_BU , B.PMT_ORIG_CRED_ID , B.PMT_ORIG_CRED_NAME , B.PMT_ORIG_IBAN_ID , B.PMT_ORIG_BIC_ID FROM PS_PMT_MANDATE_HDR A , PS_PMT_MANDATE_LN B WHERE B.BUSINESS_UNIT = A.BUSINESS_UNIT AND B.PMT_MANDATE_ID = A.PMT_MANDATE_ID AND B.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_PMT_MANDATE_LN C WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.PMT_MANDATE_ID = C.PMT_MANDATE_ID AND C.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_PMT_MANDATE_LN D WHERE B.BUSINESS_UNIT = D.BUSINESS_UNIT AND B.PMT_MANDATE_ID = D.PMT_MANDATE_ID AND D.EFFDT =B.EFFDT) AND (A.PMT_MANDATE_STATUS ='I' OR (A.PMT_MANDATE_STATUS = 'C' AND A.CANCEL_DT < %CurrentDateIn)) AND B.PMT_DEBTOR_TYPE = 'B' AND B.PMT_APPR_STATUS = 'A' AND NOT EXISTS ( SELECT 'X' FROM PS_TR_WR_DETAIL E , PS_CASH_FLOW_TR F WHERE E.MANDATE_BU = A.BUSINESS_UNIT AND E.PMT_MANDATE_ID = A.PMT_MANDATE_ID AND E.SEQUENCE_TYPE IN ('O', 'N') AND F.TR_SOURCE_CD = E.TR_SOURCE_CD AND F.SOURCE_BUS_UNIT = E.BUSINESS_UNIT AND F.TR_SOURCE_ID = E.TR_SOURCE_ID AND F.CASH_FLOW_LINE = E.CASH_FLOW_LINE AND F.CASH_FLOW_LEG = E.CASH_FLOW_LEG AND F.PYMNT_SELCT_STATUS <> 'D')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 MANDATE_BU Character(5) VARCHAR2(5) NOT NULL Mandate Business Unit

Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

Prompt Table: SP_BU_GL_NONVW

2 PMT_MANDATE_ID Character(15) VARCHAR2(15) NOT NULL Mandate direct debit

Prompt Table: PMT_M_CPTY_A_VW

3 PMT_MANDATE_REF Character(40) VARCHAR2(40) NOT NULL Mandate direct debit
4 PMT_MANDATE_DT Date(10) DATE Mandate direct debit
5 PMT_MANDATE_STATUS Character(1) VARCHAR2(1) NOT NULL Mandate direct debit
A=Amended
C=Cancelled
I=Issued
6 PMT_APPR_STATUS Character(1) VARCHAR2(1) NOT NULL Mandate direct debit
A=Approved
N=Need Correction
O=No Action
P=Pending For Approval
R=Denied
7 PMT_AMEND_FLAG Character(1) VARCHAR2(1) NOT NULL Flag On = amendment row Flag = Off = no amendment row
8 PMT_CANCEL_REMARK Character(100) VARCHAR2(100) NOT NULL Mandate direct debit Cancel reason description
9 PMT_CANCELLED_BY Character(30) VARCHAR2(30) NOT NULL User whose cancelled mandate.
10 CANCEL_DT Date(10) DATE Date Cancelled
11 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract
12 PMT_CONTRACT_DSCR Long Character CLOB Mandate direct debit Contract description
13 PMT_CRED_NAME Character(30) VARCHAR2(30) NOT NULL Mandate direct debit
14 PMT_CREDITOR_ID Character(35) VARCHAR2(35) NOT NULL Mandate direct debit
15 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

Prompt Table: COUNTRY_TBL

16 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1
17 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2
18 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3
19 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4
20 CITY Character(30) VARCHAR2(30) NOT NULL City
21 NUM1 Character(6) VARCHAR2(6) NOT NULL Number 1
22 NUM2 Character(6) VARCHAR2(6) NOT NULL Number 2
23 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat
WW=Trailer
24 ADDR_FIELD1 Character(2) VARCHAR2(2) NOT NULL Address Field 1
25 ADDR_FIELD2 Character(4) VARCHAR2(4) NOT NULL Address Field 2
26 ADDR_FIELD3 Character(4) VARCHAR2(4) NOT NULL Address Field 3
27 COUNTY Character(30) VARCHAR2(30) NOT NULL County
28 STATE Character(6) VARCHAR2(6) NOT NULL State

Prompt Table: %EDIT_STATE

29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
30 GEO_CODE Character(11) VARCHAR2(11) NOT NULL Geo Code
31 IN_CITY_LIMIT Character(1) VARCHAR2(1) NOT NULL In City Limit

Y/N Table Edit

32 PMT_DEBTOR_TYPE Character(1) VARCHAR2(1) NOT NULL Mandate direct debit
B=Treasury Counterparty
C=Customer

Default Value: C

33 PMT_DEBTOR_NAME Character(15) VARCHAR2(15) NOT NULL Mandate direct debit
34 PMT_DEBTOR_ID Character(30) VARCHAR2(30) NOT NULL Debtor Identifier
35 PMT_DEBTOR_PARTY Character(30) VARCHAR2(30) NOT NULL Mandate direct debit
36 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
37 BANK_CD Character(5) VARCHAR2(5) NOT NULL Counterparty
38 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT

Prompt Table: COUNTRY_TBL

39 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
40 ADDRESS2_OTHER Character(55) VARCHAR2(55) NOT NULL Address 2 Other
41 ADDRESS3_OTHER Character(55) VARCHAR2(55) NOT NULL Address 3 Other
42 ADDRESS4_OTHER Character(55) VARCHAR2(55) NOT NULL Address 4 Other
43 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
44 COUNTY_OTHER Character(30) VARCHAR2(30) NOT NULL County Other
45 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other

Prompt Table: %EDIT_STATE_OTH

46 POSTAL_OTHER Character(12) VARCHAR2(12) NOT NULL Postal Other
47 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
48 NUM2_OTHER Character(6) VARCHAR2(6) NOT NULL Number 2 Other
49 HOUSE_TYPE_OTHER Character(2) VARCHAR2(2) NOT NULL House Type Other
AB=House Boat Reference
WW=House Trailer Reference
50 ADDR_FIELD1_OTHER Character(2) VARCHAR2(2) NOT NULL Address Field 1 Other
51 ADDR_FIELD2_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 2 Other
52 ADDR_FIELD3_OTHER Character(4) VARCHAR2(4) NOT NULL Address Field 3 Other
53 IN_CITY_LMT_OTHER Character(1) VARCHAR2(1) NOT NULL In City Limit Other
54 GEO_CODE_OTHER Character(11) VARCHAR2(11) NOT NULL Geo Code Other
55 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
56 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
57 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
58 PMT_PAYMENT_TYPE Character(1) VARCHAR2(1) NOT NULL Mandate direct debit
O=One-Off Payment
R=Recurrence Payment

Default Value: R

59 USERID Character(30) VARCHAR2(30) NOT NULL A user's ID
60 PMT_SIGNED_LOC Character(30) VARCHAR2(30) NOT NULL Mandate direct debit
61 PMT_SIGNED_DT Date(10) DATE Mandate direct debit
62 PMT_APPR_DT Date(10) DATE Mandate direct debit
63 NAME1 Character(40) VARCHAR2(40) NOT NULL Name 1
64 REASON_CD Character(10) VARCHAR2(10) NOT NULL Reason Code
65 PMT_ORIG_CRED_BU Character(5) VARCHAR2(5) NOT NULL Mandate direct debit
66 PMT_ORIG_CRED_ID Character(35) VARCHAR2(35) NOT NULL Mandate direct debit
67 PMT_ORIG_CRED_NAME Character(30) VARCHAR2(30) NOT NULL Mandate direct debit
68 PMT_ORIG_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
69 PMT_ORIG_BIC_ID Character(11) VARCHAR2(11) NOT NULL The Bank Identifier Code, also known as a BIC or BIC code, is a universal method of identifying financial institutions in order to facilitate the automated processing of payments.