View: IBY_XML_BATCH_FCI_1_0_V

Product:	IBY - Payments
Description:
Implementation/DBA Data:	APPS.IBY_XML_BATCH_FCI_1_0_V

View Text

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"
, '') ) )
, XMLELEMENT("INSTRUCTIONSEQUENCE"
, XMLELEMENT("SEQUENCENAME"
, 'SENT_COUNTER_DAILY')
, XMLELEMENT("LASTVALUE"
, NVL(BATCH.SENTCOUNTERDAILY
, 1)) )
, XMLELEMENT("INSTRUCTIONTOTALS"
, XMLELEMENT("PAYEEACCOUNTCOUNT"
, COUNT(XML_ACCT.MBATCHID))
, XMLELEMENT("SETTLMENTTOTAL"
, 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("PAYEE"
, XMLAGG(XML_PAYEE.XML_PAYEE)) ELSE NULL END
, CASE WHEN NOT BATCH.ORG_ID IS NULL THEN XMLELEMENT("ORGANIZATIONINTERNALID"
, BATCH.ORG_ID) ELSE NULL END
, CASE WHEN NOT BATCH.PAYEEINSTRID IS NULL THEN XMLELEMENT("PAYEEBANKACCOUNT"
, XMLAGG(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)
, XMLAGG(XML_ACCT.PAYEE_ACCOUNT)) )
, BATCH.MBATCHID
FROM IBY_BATCHES_ALL BATCH
, IBY_XML_FNDCPT_ACCT_1_0_V XML_ACCT
, IBY_XML_FNDCPT_PAYEE_1_0_V XML_PAYEE
, IBY_XML_FNDCPT_BANKACCOUNT_V XML_BANK
WHERE (BATCH.MBATCHID = XML_ACCT.MBATCHID)
AND (BATCH.LEGAL_ENTITY_ID=XML_PAYEE.LEGAL_ENTITY_ID(+))
AND (BATCH.PAYEEINSTRID=XML_BANK.INSTRID(+)) GROUP BY BATCH.MBATCHID
, BATCH.BATCHID
, BATCH.BATCHCLOSEDATE
, BATCH.BATCHTOTAL
, BATCH.CURRENCYNAMECODE
, BATCH.BATCHSTATUS
, BATCH.SENTCOUNTERDAILY
, BATCH.SETTLEDATE
, BATCH.PAYEEID
, BATCH.LEGAL_ENTITY_ID
, BATCH.CURRENCYNAMECODE
, BATCH.ORG_ID
, BATCH.PAYEEINSTRID

Columns