1. Tables and Views for Financials
  2. IBY_XML_FC_BATCH_ORDER_2_0_V

IBY_XML_FC_BATCH_ORDER_2_0_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

FNDCPT_ORDER

TRXNMID

MBATCHID

PROCESS_PROFILE_CODE

Query

SQL_Statement

SELECT /*+ use_nl(tang) push_pred(xml_cc) push_pred(xml_dc) push_pred(xml_ba_v) push_pred(xml_fc_v) push_pred(xml_payer) */

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

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", iby_fndcpt_extract_gen_pvt.Get_Ins_PayerDetailsAgg(txn.trxnmid)) ,

CASE

WHEN (txn.instrtype IN ('CREDITCARD','PURCHASECARD'))

OR (txn.instrtype IS NULL)

THEN XMLConcat( iby_fndcpt_extract_gen_pvt.Get_Ins_PayerCCAgg(txn.trxnmid), iby_fndcpt_extract_gen_pvt.Get_Ins_PayerCCAgg(orig_txn.trxnmid) , XMLElement("CreditCardTransaction" , XMLElement("ActionType", DECODE(txn.trxntypeid, 2,'AUTH', 3,'AUTH_CAPTURE', 8,'CAPTURE', 9,'CAPTURE', 5,'CREDIT', 10,'CREDIT', 11,'CREDIT', 13, 'VOID', 4,'VOID', 7, 'VOID', 14, 'VOID', 17, 'VOID', 18, 'VOID', 19, 'VOID', txn.trxntypeid ) ), XMLElement("TransactionDate",TO_CHAR(txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')), XMLForest(core.tracenumber AS "TraceNumber") ,

CASE

WHEN (core.pos_trxn_flag = 'Y')

THEN XMLElement("POSData" , XMLElement("ReaderCapability",core.pos_reader_capability_code) , XMLElement("EntryMode",core.pos_entry_method_code) , XMLElement("CardIdMethod",core.pos_id_method_code) , XMLElement("AuthSource",core.pos_auth_source_code) , XMLElement("ReaderData",core.reader_data) )

END , XMLForest(core.authcode AS "AuthCode") , XMLForest(DECODE(core.date_of_voice_authorization, NULL, 'false', 'true') AS "VoiceAuthFlag" ) , XMLForest(core.authcode AS "AuthCode") ,

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

FROM iby_trxn_extensibility

WHERE trxnmid = txn.trxnmid

) ) ,

CASE

WHEN (orig_txn.trxnmid!=txn.trxnmid)

THEN XMLElement("OriginalCCTransaction" , XMLElement("ActionType", DECODE(orig_txn.trxntypeid, 2,'AUTH', 3,'AUTH_CAPTURE', 8,'CAPTURE', 9,'CAPTURE', 5,'CREDIT', 10,'CREDIT', 11,'CREDIT', orig_txn.trxntypeid ) ) , XMLElement("TransactionDate",TO_CHAR(orig_txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')) , XMLForest(orig_core.tracenumber AS "TraceNumber") ,

CASE

WHEN (orig_core.pos_trxn_flag = 'Y')

THEN XMLElement("POSData", XMLElement("ReaderCapability",orig_core.pos_reader_capability_code), XMLElement("EntryMode",orig_core.pos_entry_method_code), XMLElement("CardIdMethod",orig_core.pos_id_method_code), XMLElement("AuthSource",orig_core.pos_auth_source_code), XMLElement("ReaderData",orig_core.reader_data) )

END , XMLForest(orig_core.authcode AS "AuthCode") , XMLElement("VoiceAuthFlag",DECODE(orig_core.date_of_voice_authorization, NULL, 'false', 'true')) , XMLElement("Amount", XMLElement("Value",NVL(orig_txn.amount,0)), XMLElement("Currency", XMLElement("Code",orig_txn.currencynamecode) ) ) , XMLForest( orig_core.avscode AS "AVSCode", orig_core.referencecode AS "ReferenceCode", orig_core.cvv2result AS "SecurityValueCheck" ) , XMLForest(orig_txn.bepcode AS "PaymentSystemCode") ,

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

FROM iby_trxn_extensibility

WHERE trxnmid = orig_txn.trxnmid

) )

END )

END ,

CASE

WHEN (txn.instrtype IN ('BANKACCOUNT'))

THEN XMLConcat(XMLElement("PayerBankAccount", iby_fndcpt_extract_gen_pvt.Get_Ins_PayerBankAcctAgg(txn.payerinstrid)) , 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",tang.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("LocalInstrumentAlias", (Select ibpc.format_value FROM fusion.iby_payment_codes_b ibpc where ibpc.payment_code = txn.localinstr and ibpc.payment_code_type = 'LOCAL_INSTRUMENT')), 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

) ) )

END ,

CASE

WHEN (txn.instrtype = 'DEBITCARD')

THEN XMLConcat( iby_fndcpt_extract_gen_pvt.Get_Ins_PayerDCAgg(txn.trxnmid), XMLElement("DebitCardTransaction", XMLElement("ActionType",'DEPOSIT'), XMLElement("TransactionDate",TO_CHAR(txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')),

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

FROM iby_trxn_extensibility

WHERE trxnmid = txn.trxnmid

) ) ,

CASE

WHEN (NOT orig_txn.trxnmid IS NULL)

THEN XMLElement("OriginalDCTransaction" , XMLElement("ActionType",'DEPOSIT') , XMLElement("TransactionDate",TO_CHAR(orig_txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')) , XMLForest(orig_core.proc_tracenumber AS "TraceNumber") , XMLForest(orig_core.authcode AS "AuthCode") , XMLForest(orig_txn.bepcode AS "PaymentSystemCode") , XMLElement("DebitNetworkCode",orig_core.debit_network_code) ,

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

FROM iby_trxn_extensibility

WHERE trxnmid = orig_txn.trxnmid

) )

END )

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

CASE

WHEN NOT (pch.attribute_category IS NULL)

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

END ) ,

dax.mandate_info,

XMLElement("DebitAuthAmndFlag", iby_fndcpt_extract_gen_pvt.Get_DebitAuth_Amnd_Flag(txn.trxnmid, dax.debit_authorization_id, dax.authorization_revision_number)),

iby_ar_utils.get_document_receivable( txn.initiator_extension_id, txn.trxntypeid, NVL(core.card_data_level,orig_core.card_data_level), 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_trxn_summaries_all txn,

fnd_application app,

iby_trxn_core core,

iby_tangible tang,

iby_trxn_summaries_all orig_txn,

iby_trxn_core orig_core,

iby_xml_debit_auth_v dax,

iby_fndcpt_pmt_chnnls_vl pch

WHERE (txn.trxnmid = core.trxnmid(+))

AND txn.mbatchid = NVL(iby_utility_pvt.get_view_param('MBATCHID'),txn.mbatchid)

AND (txn.mtangibleid = tang.mtangibleid)

AND (txn.ecappid = app.application_id(+))

AND (txn.transactionid = orig_txn.transactionid(+))

AND ( (txn.TRXNTYPEID = 4

AND orig_txn.trxntypeid = 2)

AND orig_txn.mbatchid = NVL(iby_utility_pvt.get_view_param('MBATCHID'),orig_txn.mbatchid)

OR (txn.TRXNTYPEID IS NULL

AND orig_txn.reqtype ='ORAPMTREQ')

OR (txn.TRXNTYPEID = 7

AND orig_txn.trxntypeid = 3)

OR (txn.TRXNTYPEID IN (13, 14)

AND orig_txn.trxntypeid = 8)

OR (txn.TRXNTYPEID IN (17, 18)

AND orig_txn.trxntypeid = 5)

OR (txn.TRXNTYPEID = 19

AND orig_txn.trxntypeid = 11)

OR (txn.TRXNTYPEID IN (5, 6, 8, 9, 10, 100)

AND orig_txn.reqtype = 'ORAPMTREQ')

OR (txn.TRXNTYPEID =11)

OR (txn.TRXNTYPEID IN (2, 3, 20)

AND orig_txn.reqtype ='ORAPMTREQ') )

AND (orig_txn.status IN ( 0, 9, 11, 111))

AND (orig_txn.trxnmid = orig_core.trxnmid(+))

AND (txn.payment_channel_code = pch.payment_channel_code(+))

AND txn.debit_authorization_id = dax.debit_authorization_id(+)

AND TRUNC(NVL(txn.settledate,txn.creation_date)) BETWEEN dax.effective_start_date(+) AND dax.effective_end_date(+)