PMT_MND_DEBTR_V

(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_DEBTOR_TYPE , B.PMT_DEBTOR_NAME , B.PMT_DEBTOR_ID , B.PMT_DEBTOR_PARTY , B.CUSTOMER_SETID , B.CUST_ID , B.MICR_ID , B.BANK_SETID , B.BANK_CD 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))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 PMT_MANDATE_ID Character(15) VARCHAR2(15) NOT NULL Mandate direct debit

Prompt Table: PMT_MANDATE_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_DEBTOR_TYPE Character(1) VARCHAR2(1) NOT NULL Mandate direct debit
B=Treasury Counterparty
C=Customer

Default Value: C

7 PMT_DEBTOR_NAME Character(15) VARCHAR2(15) NOT NULL Mandate direct debit
8 PMT_DEBTOR_ID Character(30) VARCHAR2(30) NOT NULL Debtor Identifier
9 PMT_DEBTOR_PARTY Character(30) VARCHAR2(30) NOT NULL Mandate direct debit
10 CUSTOMER_SETID Character(5) VARCHAR2(5) NOT NULL Customer SetID
11 CUST_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations.
12 MICR_ID Character(30) VARCHAR2(30) NOT NULL MICR ID

Prompt Table: REMIT_MICR_VW

13 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
14 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code