1. Tables and Views for Financials
  2. IBY_XML_DEBIT_AUTHORIZATION_V

IBY_XML_DEBIT_AUTHORIZATION_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

MANDATE_INFO

DEBIT_AUTHORIZATION_ID

AUTHORIZATION_REVISION_NUMBER

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

Query

SQL_Statement

SELECT XMLElement( "MandateDetails",

XMLElement("AuthorizationSignDate", m.auth_sign_date),

XMLElement( "AuthorizationReference", m.authorization_reference_number),

XMLElement("OrgnlAuthReference", case when m.authorization_reference_number = o.authorization_reference_number then NULL else o.authorization_reference_number END),

XMLElement("OrgnlCreditor", CASE WHEN py.party_name = opy.party_name AND m.creditor_identifier = o.creditor_identifier THEN NULL ELSE opy.party_name END ),

XMLElement("OrgnlCreditorId", CASE WHEN m.creditor_identifier = o.creditor_identifier AND py.party_name = opy.party_name then null else o.creditor_identifier END ),

XMLElement("OrgnlCreditorLegalRegistrationNumber", CASE WHEN py.le_registration_number = opy.le_registration_number then null else opy.le_registration_number end ) ,

XMLElement("BankAccountNumber", CASE WHEN O.external_bank_account_id = m.external_bank_account_id then null else

iby_ext_bankacct_pub.Uncipher_Bank_Number (b.bank_account_num_electronic, b.ba_num_elec_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), baek.subkey_cipher_text, baes.segment_cipher_text, baes.encoding_scheme, b.ba_mask_setting, b.ba_unmask_length) end),

XMLElement("IBAN", CASE WHEN O.external_bank_account_id = m.external_bank_account_id then null else

iby_ext_bankacct_pub.Uncipher_Bank_Number (b.iban, b.iban_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY'), ibk.subkey_cipher_text, ibs.segment_cipher_text, ibs.encoding_scheme, b.ba_mask_setting, b.ba_unmask_length) END ),

XMLElement("CreditorName", py.party_name),

XMLElement("CreditorIdentifier", m.creditor_identifier),

XMLElement("CreditorLegalRegistrationNumber", py.le_registration_number) ,

XMLElement("CreditorAddress", pyadrs.ADDRESS),

XMLElement("Recurring", DECODE( NVL(m.direct_debit_max_count,2), 1,'false', 'true' ) ),

XMLElement("RemainingDebits", NVL(m.direct_debit_max_count,m.direct_debit_count+1) - m.direct_debit_count ),

XMLElement("AmendmentUses", IBY_FNDCPT_EXTRACT_GEN_PVT.Get_DebitAuth_Amend_Uses(m.debit_authorization_id,m.authorization_revision_number)) ,

XMLElement("FirstSuccessSettlementDate",

(SELECT TO_CHAR(min(nvl(settlement_due_date, settledate)), 'YYYY-MM-DD')

FROM iby_trxn_summaries_all txsum,

iby_fndcpt_tx_operations opt

WHERE txsum.transactionid = opt.transactionid

AND txsum.reqtype = 'ORAPMTBATCHREQ'

AND txsum.debit_authorization_id = m.debit_authorization_id

) ),

XMLElement("FinalCollectionDate", m.debit_auth_fnl_colltn_date)

,

XMLElement("Frequency", m.debit_auth_frequency)

)

AS mandate_info,

m.debit_authorization_id,

m.AUTHORIZATION_REVISION_NUMBER,

m.effective_start_date,

m.effective_end_date

FROM iby_debit_authorizations_f m,

iby_debit_authorizations_f o,

iby_ext_bank_accounts b,

iby_security_segments baes,

iby_sys_security_subkeys baek,

iby_security_segments ibs,

iby_sys_security_subkeys ibk,

iby_ext_fc_payee_1_0_v opy,

iby_ext_fc_payee_1_0_v py,

IBY_XML_HZ_ADDR_1_0_V pyadrs

WHERE m.debit_authorization_id = o.debit_authorization_id

AND m.creditor_legal_entity_id = py.legal_entity_id(+)

AND o.authorization_revision_number = IBY_FNDCPT_EXTRACT_GEN_PVT.Get_Orgnl_Debit_Auth_Rev(m.debit_authorization_id, m.authorization_revision_number)

AND o.creditor_legal_entity_id = opy.legal_entity_id(+)

AND o.external_bank_account_id = b.ext_bank_account_id

AND b.iban_sec_segment_id = ibs.sec_segment_id(+)

AND ibs.sec_subkey_id = ibk.sec_subkey_id(+)

AND b.ba_num_elec_sec_segment_id = baes.sec_segment_id(+)

AND baes.sec_subkey_id = baek.sec_subkey_id(+)

AND py.le_location_id = pyadrs.location_id