IBY_XML_BATCH_FCI_1_0_V

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name
INSTRUCTION MBATCHID

Query

SQL_Statement

SELECT XMLElement("FundsCaptureInstruction", XMLElement("InstructionInfo", XMLElement("InstructionInternalID",batch.mbatchid), XMLElement( "InstructionName",batch.batchid), XMLElement("InstructionCreationDate", TO_CHAR(batch.batchclosedate,'YYYY-MM-DD"T"HH24:MI:SS')), XMLElement( "InstructionSentDate",TO_CHAR(SYSDATE,'YYYY-MM-DD"T"HH24:MI:SS')), XMLElement ("InstructionStatus", XMLElement("Code",batch.batchstatus), XMLElement( "Meaning",NULL) ) ), XMLElement("InstructionSequence", XMLElement( "SequenceName",'SENT_COUNTER_DAILY'), XMLElement("LastValue",NVL( batch.sentcounterdaily,1)) ), XMLElement("InstructionTotals", XMLElement( "PayeeAccountCount", ( SELECT COUNT(DISTINCT (txpi.bepkey)) FROM iby_trxn_summaries_all txpi WHERE txpi.mbatchid=batch.mbatchid ) ), XMLElement("SettlmentTotal", XMLElement("Value", NVL(batch.batchtotal, 0)) , XMLElement("Currency", XMLElement("Code",batch.currencynamecode) ) ) ), XMLElement("InstructionGrouping", CASE WHEN NOT batch.settledate IS NULL THEN XMLElement("SettlementDate",TO_CHAR(batch.settledate, 'YYYY-MM-DD"T"HH24:MI:SS')) ELSE NULL END, CASE WHEN NOT batch.currencynamecode IS NULL THEN XMLElement("SettlementCurrency", XMLElement("Code", batch.currencynamecode)) ELSE NULL END, CASE WHEN NOT batch.legal_entity_id IS NULL THEN XMLElement("PayeeLegalEntity", xml_payee.xml_payee) ELSE NULL END, CASE WHEN NOT batch.org_id IS NULL THEN XMLElement("PayeeOrganization", XMLElement("OrganizationInternalID", batch.org_id), XMLElement("OrganizationType", XMLElement("Code", batch.org_type), XMLElement("Meaning",NULL) ) ) ELSE NULL END, CASE WHEN NOT batch.payeeinstrid IS NULL THEN XMLElement("PayeeBankAccount", bank_account_content) ELSE NULL END ), DECODE(iby_utility_pvt.get_view_param('EXTRACT_MODE'), 'G_EXTRACT_MODE_SRA', IBY_FNDCPT_EXTRACT_GEN_PVT.Get_Ins_PayeeAcctAgg( batch.mbatchid), ( SELECT XMLAgg(xa.payee_account) FROM iby_xml_fndcpt_acct_1_0_v xa WHERE batch.mbatchid=xa.mbatchid ) ) ) instruction, batch.mbatchid mbatchid FROM iby_batches_all batch, iby_xml_fndcpt_payee_1_0_v xml_payee, ( SELECT int_bank_account bank_account_content, mbatchid FROM iby_xml_fc_prba_1_0_v xml_ba_v, iby_batches_all iby_ba_bat WHERE iby_ba_bat.payeeinstrid=xml_ba_v.bank_account_id ) xml_bank WHERE ( batch.legal_entity_id=xml_payee.legal_entity_id(+) ) AND batch.mbatchid =xml_bank.mbatchid(+)