PMT_M_CAN_REF_V

(SQL View)
 Index Back

Mandate search

Search by Mandate Reference for Mandate Cancellation

SELECT B.PMT_MANDATE_REF , B.BUSINESS_UNIT , B.PMT_MANDATE_ID , A.PMT_MANDATE_STATUS , B.PMT_APPR_STATUS , B.PMT_DEBTOR_NAME 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 NOT EXISTS ( SELECT 'X' FROM PS_PMT_DETAIL_TBL P WHERE PMT_STATUS <> 'P' AND P.MANDATE_BU = A.BUSINESS_UNIT AND P.PMT_MANDATE_ID = A.PMT_MANDATE_ID)

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

Default Value: OPR_DEF_TBL_FS.BUSINESS_UNIT

Prompt Table: SP_BU_GL_NONVW

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

Prompt Table: PMT_MANDATE_VW

4 PMT_MANDATE_STATUS Character(1) VARCHAR2(1) NOT NULL Mandate direct debit
A=Amended
C=Cancelled
I=Issued
5 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
6 PMT_DEBTOR_NAME Character(15) VARCHAR2(15) NOT NULL Mandate direct debit