IBY_XML_FNDCPT_ORDER_PN_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
FNDCPT_ORDER TRXNMID MBATCHID

Name

FNDCPT_ORDER

TRXNMID

MBATCHID

Query

SQL_Statement
SELECT XMLElement("FundsCaptureOrder", XMLElement("OrderSourceInfo", XMLForest(txn.ecappid as "ApplicationInternalID", app.application_short_name as "ApplicationName") ), XMLElement("OrderNumber", XMLElement("PayeeOrderNumber",txn.tangibleid) ), XMLForest(tang.memo AS "PayeeOrderMemo"), XMLForest(tang.refinfo AS "PayeeOrderRefID"), XMLForest(tang.order_medium AS "OrderMedium"), CASE WHEN (NOT xml_payee_ba.trxnmid IS NULL)THEN XMLElement("PayeeBankAccount",xml_payee_ba.bank_account_content) END, XMLElement("OrderAmount", XMLElement("Value", NVL(txn.amount, 0)), XMLElement("Currency", XMLElement("Code",txn.currencynamecode) ) ), XMLElement("PayeeLegalEntity",xml_payee.xml_payee ), XMLElement("Payer", xml_payer.payer), CASE WHEN (txn.instrtype IN ('CREDITCARD','PURCHASECARD')) OR (txn.instrtype IS NULL) THEN XMLConcat( xml_cc.credit_card, orig_xml_cc.credit_card, 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"), 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"), 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", xml_bank.bank_account_content), 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.instrsubtype <> 'ACH') THEN XMLElement("BillReceivableData", XMLElement("MaturityDate", TO_CHAR(txn.br_maturity_date,'YYYY-MM-DD"T"HH24:MI:SS')), 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), (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( xml_dc.debit_card, 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 ), 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_PN_1_0_V' )) FNDCPT_ORDER, txn.trxnmid TRXNMID, txn.mbatchid MBATCHID 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_fndcpt_creditcard_v xml_cc, iby_xml_fndcpt_creditcard_v orig_xml_cc, ( select decode(iby_ba_sum.instrtype, 'BANKACCOUNT', bank_account_content, null) bank_account_content, trxnmid from iby_xml_fndcpt_bankaccount_v xml_ba_v, iby_trxn_summaries_all iby_ba_sum where iby_ba_sum.payerinstrid=xml_ba_v.instrid) xml_bank, ( select decode(iby_ba_sum.instrtype, 'BANKACCOUNT', int_bank_account, null) bank_account_content, trxnmid from iby_xml_fc_prba_1_0_v xml_ba_v, iby_trxn_summaries_all iby_ba_sum where iby_ba_sum.payeeinstrid=xml_ba_v.bank_account_id) xml_payee_ba, iby_xml_fndcpt_debitcard_v xml_dc, iby_xml_fndcpt_payer_1_0_v xml_payer, iby_fndcpt_pmt_chnnls_vl pch, iby_xml_fndcpt_payee_1_0_v xml_payee WHERE (txn.trxnmid = core.trxnmid(+)) 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) 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.trxnmid = xml_cc.trxnmid) AND (txn.trxnmid = xml_dc.trxnmid) AND (txn.trxnmid = xml_bank.trxnmid(+)) AND (orig_txn.trxnmid = orig_xml_cc.trxnmid) AND (txn.trxnmid=xml_payer.trxnmid) AND (txn.trxnmid = xml_payee_ba.trxnmid(+)) AND (txn.payment_channel_code = pch.payment_channel_code(+)) AND (txn.legal_entity_id = xml_payee.legal_entity_id(+))

SQL_Statement

SELECT XMLElement("FundsCaptureOrder",

XMLElement("OrderSourceInfo",

XMLForest(txn.ecappid as "ApplicationInternalID", app.application_short_name as "ApplicationName") ),

XMLElement("OrderNumber", XMLElement("PayeeOrderNumber",txn.tangibleid) ),

XMLForest(tang.memo AS "PayeeOrderMemo"),

XMLForest(tang.refinfo AS "PayeeOrderRefID"),

XMLForest(tang.order_medium AS "OrderMedium"),

CASE WHEN (NOT xml_payee_ba.trxnmid IS NULL)THEN

XMLElement("PayeeBankAccount",xml_payee_ba.bank_account_content)

END,

XMLElement("OrderAmount", XMLElement("Value", NVL(txn.amount, 0)), XMLElement("Currency", XMLElement("Code",txn.currencynamecode) ) ),

XMLElement("PayeeLegalEntity",xml_payee.xml_payee ),

XMLElement("Payer", xml_payer.payer),

CASE WHEN (txn.instrtype IN ('CREDITCARD','PURCHASECARD')) OR (txn.instrtype IS NULL) THEN

XMLConcat( xml_cc.credit_card, orig_xml_cc.credit_card,

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"),

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"),

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", xml_bank.bank_account_content),

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.instrsubtype <> 'ACH') THEN XMLElement("BillReceivableData", XMLElement("MaturityDate", TO_CHAR(txn.br_maturity_date,'YYYY-MM-DD"T"HH24:MI:SS')), 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),

(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( xml_dc.debit_card,

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 ),

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_PN_1_0_V' )) FNDCPT_ORDER,

txn.trxnmid TRXNMID, txn.mbatchid MBATCHID

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_fndcpt_creditcard_v xml_cc, iby_xml_fndcpt_creditcard_v orig_xml_cc,

( select decode(iby_ba_sum.instrtype, 'BANKACCOUNT', bank_account_content, null) bank_account_content, trxnmid from iby_xml_fndcpt_bankaccount_v xml_ba_v, iby_trxn_summaries_all iby_ba_sum where iby_ba_sum.payerinstrid=xml_ba_v.instrid) xml_bank,

( select decode(iby_ba_sum.instrtype, 'BANKACCOUNT', int_bank_account, null) bank_account_content, trxnmid from iby_xml_fc_prba_1_0_v xml_ba_v, iby_trxn_summaries_all iby_ba_sum where iby_ba_sum.payeeinstrid=xml_ba_v.bank_account_id) xml_payee_ba,

iby_xml_fndcpt_debitcard_v xml_dc, iby_xml_fndcpt_payer_1_0_v xml_payer,

iby_fndcpt_pmt_chnnls_vl pch, iby_xml_fndcpt_payee_1_0_v xml_payee

WHERE

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

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) 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.trxnmid = xml_cc.trxnmid)

AND (txn.trxnmid = xml_dc.trxnmid)

AND (txn.trxnmid = xml_bank.trxnmid(+))

AND (orig_txn.trxnmid = orig_xml_cc.trxnmid)

AND (txn.trxnmid=xml_payer.trxnmid)

AND (txn.trxnmid = xml_payee_ba.trxnmid(+))

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

AND (txn.legal_entity_id = xml_payee.legal_entity_id(+))