SEPA Debit Authorization and Bank Detail Check Report

select ab.NAME, acra.receipt_number ,acra.AMOUNT ,acra.status,hp.party_name customer_name ,hca.account_number customer_number, acra.pay_from_customer 
,arm.name  Receipt_method
,rcta.TRX_NUMBER Transaction_Number
,rcta.ATTRIBUTE1 Government_Invoice_Number
,hcsua.location Bill_to_location
,acra.payment_trxn_extension_id
,ft.debit_authorization_id debit_auth_id
,ida.authorization_reference_number  Auth_Reference_Number
,to_char(ida.auth_sign_date,'MM/DD/YYYY') auth_sign_date
,to_char(ida.effective_start_date,'MM/DD/YYYY') Debit_Auth_Begin
,to_char(ida.effective_end_date,'MM/DD/YYYY') Debit_Auth_End
,to_char(ida.auth_cancel_date,'MM/DD/YYYY') auth_cancel_date
,(select NAME from xle_entity_profiles where LEGAL_ENTITY_ID =ida.creditor_legal_entity_id) creditor_le_name  
,(select LEGAL_ENTITY_IDENTIFIER from xle_entity_profiles where LEGAL_ENTITY_ID =ida.creditor_legal_entity_id) creditor_identifier
,ieba.bank_name 
,ieba.bank_branch_name 
,ieba.bank_account_name 
,ieba.bank_account_number
,ieba.iban_number
,ieba.eft_swift_code
,to_char(ieba.end_date,'MM/DD/YYYY')end_date 
,ida.creditor_identifier payee_identifier
,ida.PRIMARY_FLAG Payer_Authorization_Flag
,(select NAME from xle_entity_profiles where LEGAL_ENTITY_ID =ida.creditor_legal_entity_id) LEGAL_ENTITY_NAME
,ida.creditor_legal_entity_id LEGAL_ENTITY_ID
,hcsua.site_use_id
,hcsua.site_use_code
,hcsua.primary_flag
,(select 'Debit Authorization is missing for Receipt Number '||acra.receipt_number from dual where ft.debit_authorization_id is null ) 
||(select 'Inactive Debit Authorization is used for Receipt Number ' ||acra.receipt_number from dual 
    where trunc(nvl(ida.effective_end_date,sysdate)) < trunc(sysdate) or trunc(nvl(ida.auth_cancel_date,sysdate)) < trunc(sysdate))
||(select 'Creditor details are missing from Debit Authorization setup for Receipt Number '||acra.receipt_number from dual 
    where ida.creditor_legal_entity_id is null )	
Warning_Message 
from 
     ar_cash_receipts_all  acra,
     ar_receipt_methods arm,
     ar_cash_receipt_history_all acrha,
	 ar_batches_all ab,
	 hz_cust_accounts hca,
	 hz_parties hp,
	 hz_cust_site_uses_all hcsua,
	 iby_fndcpt_tx_extensions ft,
     iby_debit_authorizations_f ida,
     iby_ext_bank_accounts_v ieba,
     AR_RECEIVABLE_APPLICATIONS_ALL araa,
     RA_CUSTOMER_TRX_ALL rcta, 
	 ar_receipt_classes		ARC
where 1=1
  and acra.receipt_method_id = arm.receipt_method_id
  and acra.CASH_RECEIPT_ID= acrha.CASH_RECEIPT_ID
  --and acrha.CURRENT_RECORD_FLAG='Y'
  and acrha.BATCH_ID = ab.BATCH_ID
  AND acra.pay_from_customer = hca.cust_account_id
  AND hca.party_id = hp.party_id
  and acra.CUSTOMER_SITE_USE_ID = hcsua.site_use_id(+)
  AND acra.payment_trxn_extension_id = ft.trxn_extension_id(+)
  AND ft.debit_authorization_id = ida.debit_authorization_id(+)
  AND ida.EFFECTIVE_START_DATE(+)<=SYSDATE
  AND IDA.EFFECTIVE_END_DATE (+)>=SYSDATE
 -- and ieba.ext_bank_account_id = ida.EXTERNAL_BANK_ACCOUNT_ID --REF-291296
and  ida.EXTERNAL_BANK_ACCOUNT_ID=ieba.ext_bank_account_id (+)
  and  acra.org_id = nvl(:p_org_id,acra.org_id)
  and  (ab.batch_id in ( :P_Batch_Id) or 'All' in (:P_Batch_Id||'All') )
  and  (arm.name in (:p_receipt_method) or 'All' in (:p_receipt_method||'All') )
  and araa.display = 'Y'
  AND araa.cash_receipt_id = acra.cash_receipt_id
  AND araa.applied_customer_trx_id = rcta.customer_trx_id--(+)
  and acra.org_id = rcta.org_id
  and ARM.receipt_class_id = ARC.receipt_class_id
  AND ARC.name ='SEPA'
  --and acra.receipt_number ='15007'
  and ab.ORG_ID  in (
								  SELECT distinct bu.bu_id
								FROM fusion.fun_all_business_units_v bu
								,fusion.fun_user_role_data_asgnmnts role
								,fusion.per_users pu
								,fusion.per_roles_dn_vl pr
								,fusion.per_roles_dn_tl prtl
								WHERE role.org_id = bu.bu_id
								AND pu.user_guid = role.user_guid
								AND pr.role_common_name = role.ROLE_NAME
								and role.active_flag ='Y' 
								and pu.username = fnd_global.user_name
								and bu_name in ('ES BU EUR','IT BU EUR')
								and pr.role_id=prtl.role_id
								and prtl.LANGUAGE='US'
								and prtl.ROLE_NAME  in  ('XXC Accounts Receivable Analyst'
														,'XXC Accounts Receivable Manager'
														,'XXC Receivables Inquiry'
														,'XXC Billing Manager'
														,'XXC Billing Analyst'
														)
						
)
and ab.batch_applied_status in ('COMPLETED_FORMAT', 'COMPLETED_APPROVAL')