1. Tables and Views for Financials
  2. IBY_XML_FC_BA_ORDER_V

IBY_XML_FC_BA_ORDER_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

FNDCPT_ORDER

TRXNMID

MBATCHID

PROCESS_PROFILE_CODE

Query

SQL_Statement

SELECT

XMLElement("FundsCaptureOrder" , XMLElement("OrderSourceInfo", XMLForest(txn.ecappid AS "ApplicationInternalID", txn.application_short_name AS "ApplicationName") ) , XMLElement("OrderNumber", XMLElement("PayeeOrderNumber",txn.tangibleid), XMLElement("LogicalGroupReference",txn.logical_group_reference) ) , XMLElement("PayeeOrderMemo",txn.memo), XMLElement("PayeeOrderRefID",txn.refinfo), XMLElement("OrderMedium",txn.order_medium) ,

CASE

WHEN (NOT txn.payeeinstrid IS NULL)

THEN XMLElement("PayeeBankAccount",iby_fndcpt_extract_gen_pvt.Get_Ins_PayeeBankAcctAgg(txn.payeeinstrid))

END , XMLElement("OrderAmount", XMLElement("Value",NVL(txn.amount, 0)), XMLElement("Currency", XMLElement("Code",txn.currencynamecode) ) ) , XMLElement("PayeeLegalEntity",iby_fndcpt_extract_gen_pvt.Get_Ins_PayeeDetailsAgg(txn.legal_entity_id)) ,

XMLElement("Payer", XMLConcat(XMLElement("PartyInternalID", txn.payer_party_id), XMLElement("PartyNumber", txn.payer_party_number),XMLElement("Name", txn.payer_party_name),

XMLElement("PartyType",XMLElement("Code", txn.payer_party_type),XMLElement("Meaning", txn.payer_party_type_meaning)), XMLElement("TaxRegistrationNumber", txn.payer_tax_regn_number),XMLElement("LegalRegistrationNumber", txn.payer_jgzz_fiscal_code),

CASE WHEN txn.payer_attribute_category IS NULL THEN NULL ELSE XMLElement("PartyDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs('HZ_PARTIES', txn.payer_party_id, NULL)) END,

XMLElement("AlternateName",txn.payer_alt_party_name), XMLElement("CustomerAccountNumber", txn.payer_account_number),

CASE WHEN txn.bill_to_address_id IS NULL THEN NULL

ELSE XMLElement("Address",XMLConcat(XMLElement("AddressInternalID", txn.bill_to_address_id),XMLElement("AddressLine1", txn.payer_address1),XMLElement("AddressLine2", txn.payer_address2),XMLElement("AddressLine3", txn.payer_address3),

XMLElement("City", txn.payer_city),XMLElement("County", txn.payer_county),XMLElement("State", txn.payer_state),XMLElement("Country", txn.payer_country),XMLElement("CountryName", txn.payer_territory_short_name),

XMLElement("ISO3DigitCountry", txn.payer_iso_territory_code),XMLElement("PostalCode", txn.payer_postal_code),XMLElement("AlternateAddressName", null) ) ) END,

XMLElement("FirstPartyReference", txn.payer_party_number))),

XMLConcat(XMLElement("PayerBankAccount", XMLConcat(XMLElement("BankAccountInternalID", txn.payerinstrid), XMLElement("BankName", txn.payer_bank_name),XMLElement("BankNumber", txn.payer_bank_number), XMLElement("BranchInternalID", txn.payer_branch_id), XMLElement("BranchName", txn.payer_branch_name),

XMLElement("BranchNumber", txn.payer_branch_number), XMLElement("BranchType", XMLElement("Code", txn.payer_branch_type), XMLElement("Meaning", txn.payer_branch_type_meaning) ),

XMLElement("BankAccountName", txn.payer_bank_account_name), XMLElement("AlternateBankAccountName", txn.payer_bank_account_name_alt),XMLElement("BankAccountNumber", txn.payer_bank_acct_num ), XMLElement("UserEnteredBankAccountNumber", txn.payer_user_bank_acct_num ),

XMLElement("MaskedBankAccountNumber",txn.payer_masked_bank_account_num), XMLElement("SwiftCode", txn.payer_branch_swift_code), XMLElement("IBANNumber", txn.payer_iban ),XMLElement("MaskedIBANNumber", txn.payer_masked_iban),

XMLElement("CheckDigits", txn.payer_acct_check_digits), XMLElement("BankAccountType", XMLElement("Code", txn.payer_bank_account_type), XMLElement("Meaning", txn.payer_bank_acct_type_meaning) ),XMLElement("BankAccountSuffix",txn.payer_bank_account_suffix),XMLElement("BankAccountCurrency", XMLElement("Code", txn.payer_bank_acct_currency_code) ),

XMLElement("BankAddress", XMLConcat ( XMLElement ( "AddressInternalID" , txn.payer_bank_acct_location_id ) , XMLElement ("AddressLine1" , txn.payer_bank_acct_address1 ) , XMLElement ( "AddressLine2" , txn.payer_bank_acct_address2 ) ,

XMLElement ("AddressLine3" , txn.payer_bank_acct_address3 ) , XMLElement ( "City" , txn.payer_bank_acct_city ) , XMLElement ( "County" ,txn.payer_bank_acct_county ) , XMLElement ( "State" , txn.payer_bank_acct_state ) , XMLElement ( "Country" , txn.payer_bank_acct_country ),

XMLElement ( "CountryName" , txn.payer_bank_acct_terr_name ) , XMLElement("ISO3DigitCountry", txn.payer_bank_acct_iso_terr_code), XMLElement ( "PostalCode" ,txn.payer_bank_acct_postal_code ),

XMLElement("PreFormattedConcatenatedAddress", txn.payer_bank_acct_concat_addr),XMLElement("PreFormattedMailingAddress", txn.payer_bank_acct_mail_addr), XMLElement("AddressName",txn.payer_bank_acct_addr_name) ) ),

XMLElement("AccountHolderName",txn.payer_bank_account_holder_name),

CASE WHEN txn.payer_bank_acct_dff IS NULL THEN NULL ELSE XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_EXT_BANK_ACCOUNTS', txn.payerinstrid, NULL)) END ))

, XMLElement("BankAccountTransaction", XMLElement("ActionType",DECODE(txn.reqtype, 'ORAPMTCREDIT','CREDIT', 'DEBIT') ) , XMLElement("TransactionDate",TO_CHAR(txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')) , XMLElement("SettlementDueDate",TO_CHAR(txn.settlement_due_date,'YYYY-MM-DD"T"HH24:MI:SS')) , XMLElement("AuthorizationMethod",txn.eft_auth_method) , XMLElement("DeliveryMethod",txn.debit_advice_delivery_method) , XMLElement("TransferType",DECODE(txn.instrsubtype,'ACH', 'ACH', 'BR')) , XMLElement("SettlementCustomerReference", txn.settlement_customer_reference) , XMLElement("SettlementFactored",DECODE(txn.factored_flag, 'Y','true', 'false')) ,

CASE

WHEN (txn.payment_channel_code = 'BILLS_RECEIVABLE')

THEN XMLElement("BillReceivableData" , XMLElement("MaturityDate", TO_CHAR(txn.br_maturity_date,'YYYY-MM-DD"T"HH24:MI:SS')) , XMLElement("DraweeIssued",DECODE(txn.BR_DRAWEE_ISSUED_FLAG,'Y','true','false')) , XMLElement("Signed",DECODE(txn.BR_SIGNED_FLAG,'Y','true','false')) , XMLElement("BRType", txn.instrsubtype))

END , XMLElement("BankChargeBearer", XMLElement("Code",txn.bank_charge_bearer_code), XMLElement("Meaning",NULL) ) , XMLElement("DebitAuthorization", XMLElement("DebitAuthRefCode",txn.debit_auth_reference) , XMLElement("DebitAuthMethod",txn.debit_auth_method) , XMLElement("DebitAuthGranted",DECODE(txn.debit_auth_flag, 'Y','true', 'false')) ) , XMLElement("DebitNotification" , XMLElement("DeliveryMethod",txn.debit_advice_delivery_method) , XMLElement("EmailAddress",txn.debit_advice_email) , XMLElement("FaxNumber",txn.debit_advice_fax) ) , XMLElement("DebitBankInstruction",txn.dirdeb_instruction_code) , XMLElement("ServiceLevel",txn.service_level) ,

XMLElement("PurposeCode",txn.purpose_code), XMLElement("LocalInstrument",txn.localinstr) , XMLElement("SeqType",txn.seq_type) ,

(SELECT XMLAgg( XMLElement("Extend", XMLElement("Name",extend_name), XMLElement("Value",extend_value) ) )

FROM iby_trxn_extensibility

WHERE trxnmid = txn.trxnmid

) ) ) ,

XMLElement("PaymentMethod", XMLElement("PaymentMethodInternalID", txn.payment_channel_code) , XMLElement("PaymentMethodName", txn.payment_channel_name) , XMLElement("PaymentMethodFormatValue", txn.format_value) ,

CASE WHEN NOT (txn.payment_channel_dff IS NULL) THEN

XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_FNDCPT_PMT_CHNNLS_B', NULL, txn.payment_channel_code))

END ),

CASE WHEN txn.Debit_Authorization_Id IS NOT NULL THEN

XMLElement( "MandateDetails",

XMLElement("AuthorizationSignDate", txn.auth_sign_date), XMLElement( "AuthorizationReference", txn.auth_ref_number),

XMLElement("OrgnlAuthReference", case when txn.auth_ref_number = txn.orig_auth_ref_number then NULL else txn.orig_auth_ref_number END),

XMLElement("OrgnlCreditor", CASE WHEN txn.creditor_party_name = txn.orig_party_name AND txn.creditor_identifier = txn.orig_creditor_identifier THEN NULL ELSE txn.orig_party_name END ),

XMLElement("OrgnlCreditorId", CASE WHEN txn.creditor_identifier = txn.orig_creditor_identifier AND txn.creditor_party_name = txn.orig_party_name then null else txn.orig_creditor_identifier END ),

XMLElement("OrgnlCreditorLegalRegistrationNumber", CASE WHEN txn.creditor_registration_number = txn.orig_registration_number then null else txn.orig_registration_number end ) ,

XMLElement("BankAccountNumber", CASE WHEN txn.debit_auth_bank_account_id = txn.orig_bank_account_id then null else txn.orig_bank_account_num END),

XMLElement("IBAN", CASE WHEN txn.debit_auth_bank_account_id = txn.orig_bank_account_id then null else txn.orig_iban END),

XMLElement("CreditorName", txn.creditor_party_name),XMLElement("CreditorIdentifier", txn.creditor_identifier), XMLElement("CreditorLegalRegistrationNumber", txn.creditor_registration_number) ,

XMLElement("CreditorAddress", IBY_FD_EXTRACT_GEN_PVT.Get_Hz_Address(txn.creditor_location_id)),

XMLElement("Recurring", DECODE( NVL(txn.direct_debit_max_count,2), 1,'false', 'true' ) ),XMLElement("RemainingDebits", txn.direct_debit_count),

XMLElement("AmendmentUses", txn.debit_auth_amend_uses) ,

XMLElement("FirstSuccessSettlementDate",txn.first_success_settle_date ), XMLElement("FinalCollectionDate", txn.debit_auth_fnl_colltn_date) , XMLElement("Frequency", txn.debit_auth_frequency))

END,

XMLElement("DebitAuthAmndFlag", txn.debit_auth_amend_code),

iby_ar_utils.get_document_receivable( txn.initiator_extension_id, txn.trxntypeid, null, txn.instrtype, 'IBY_XML_FNDCPT_ORDER_1_0_V' ) ,

XMLElement("StructuredPaymentReference", txn.structured_payment_reference)) fndcpt_order,

txn.trxnmid,

txn.mbatchid,

txn.process_profile_code

FROM iby_fc_selected_trxns_all txn