eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IBY_XML_BATCH_FCI_1_0_V

Source

View Text - Preformatted


SELECT /*+PUSH_PRED(xml_payee) PUSH_PRED(xml_bank)*/ 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("SettlementTotal", XMLElement("Value",batch.batchtotal), 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", xml_bank.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(xml_acct.payee_account) FROM iby_xml_fndcpt_acct_1_0_v xml_acct WHERE xml_acct.mbatchid=batch.mbatchid ) ) ), batch.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(+)

View Text - HTML Formatted

SELECT /*+PUSH_PRED(XML_PAYEE) PUSH_PRED(XML_BANK)*/ 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("SETTLEMENTTOTAL"
, XMLELEMENT("VALUE"
, BATCH.BATCHTOTAL)
, 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"
, XML_BANK.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(XML_ACCT.PAYEE_ACCOUNT)
FROM IBY_XML_FNDCPT_ACCT_1_0_V XML_ACCT
WHERE XML_ACCT.MBATCHID=BATCH.MBATCHID ) ) )
, BATCH.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(+)