AR Direct Debit Pre-Notification Letter

  		SELECT  PARTY_NAME PARTY_NAME,
      		    LOC.ADDRESS1 ADDRESS1,
      		    LOC.ADDRESS2 ADDRESS2,
      		    LOC.ADDRESS3 ADDRESS3,
      		    LOC.ADDRESS4 ADDRESS4,
      		    LOC.COUNTRY COUNTRY,
      		    MANDATE.AUTHORIZATION_REFERENCE_NUMBER MANDATE_NUMBER,
      		    BANK.BANK_NAME BANK_NAME,
      		    BANK.BANK_ACCOUNT_NUM_ELECTRONIC BANK_ACCT_NUMBER,
      		    RCT.CASH_RECEIPT_ID RCT_ID,
      		    RCT.CURRENCY_CODE CURRENCY  ,
      		    RCT.RECEIPT_NUMBER RECEIPT_NUMBER,
      		    TO_CHAR((PS.DUE_DATE),'DD Month, YYYY') DUE_DATE
		   FROM   AR_CASH_RECEIPT_HISTORY CRH,
              AR_CASH_RECEIPTS RCT,
              AR_PAYMENT_SCHEDULES PS,
              AR_RECEIPT_METHODS RCT_MTD,
              AR_RECEIPT_CLASSES RCT_CLASS,
              IBY_EXTERNAL_BANK_ACCOUNTS_V  BANK,
              IBY_DEBIT_AUTHORIZATIONS MANDATE,
              IBY_FNDCPT_TX_EXTENSIONS TRX_EXT,
              IBY_PMT_INSTR_USES_ALL   INSTR_USE,
              HZ_CUST_SITE_USES SITE_USE,
              HZ_CUST_ACCT_SITES CUST_SITE,
              HZ_PARTY_SITES PARTY_SITE,
              HZ_PARTIES PARTY,
              HZ_LOCATIONS LOC
		   WHERE  CRH.BATCH_ID = SUBSTR(:P_BATCH_ID, 12)
		   AND    CRH.PRV_STAT_CASH_RECEIPT_HIST_ID IS NULL
		   AND    CRH.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID 
		   AND    PS.CASH_RECEIPT_ID = RCT.CASH_RECEIPT_ID
		   AND    RCT.RECEIPT_METHOD_ID = RCT_MTD.RECEIPT_METHOD_ID
		   AND    RCT_MTD.RECEIPT_CLASS_ID =RCT_CLASS.RECEIPT_CLASS_ID
		   AND    RCT_CLASS.CREATION_METHOD_CODE = 'AUTOMATIC'
		   AND    RCT.PAYMENT_TRXN_EXTENSION_ID = TRX_EXT.TRXN_EXTENSION_ID
		   AND    TRX_EXT.INSTR_ASSIGNMENT_ID = INSTR_USE.INSTRUMENT_PAYMENT_USE_ID
		   AND    INSTR_USE.INSTRUMENT_ID     = BANK.EXT_BANK_ACCOUNT_ID
		   AND    BANK.EXT_BANK_ACCOUNT_ID = MANDATE.EXTERNAL_BANK_ACCOUNT_ID
		   AND    NVL(MANDATE.AUTH_CANCEL_DATE, RCT.RECEIPT_DATE) >= RCT.RECEIPT_DATE
		   AND    NVL(MANDATE.DEBIT_AUTH_END, RCT.RECEIPT_DATE) >= RCT.RECEIPT_DATE
		   AND    MANDATE.CURR_REC_INDI = 'Y'
       AND    MANDATE.PRE_NOTIFICATION_REQUIRED_FLAG = 'Y'
		   AND    RCT.CUSTOMER_SITE_USE_ID = SITE_USE.SITE_USE_ID
		   AND    SITE_USE.CUST_ACCT_SITE_ID = CUST_SITE.CUST_ACCT_SITE_ID 
		   AND    CUST_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
		   AND    PARTY_SITE.LOCATION_ID =LOC.LOCATION_ID
		   AND    PARTY_SITE.PARTY_ID = PARTY.PARTY_ID
		   AND 	  INSTR_USE.payment_flow = 'FUNDS_CAPTURE'
 		   AND    INSTR_USE.instrument_type = 'BANKACCOUNT'