View: IBY_XML_FD_INS_1_0_V

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

View Text

SELECT XMLELEMENT("OUTBOUNDPAYMENTINSTRUCTION"
, XMLELEMENT("PAYMENTINSTRUCTIONINFO"
, XMLELEMENT("INSTRUCTIONREFERENCENUMBER"
, INS.PAYMENT_INSTRUCTION_ID)
, XMLELEMENT("INSTRUCTIONCREATIONDATE"
, TO_CHAR(INS.INSTRUCTION_CREATED_DATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("INSTRUCTIONSTATUS"
, XMLELEMENT("CODE"
, INS.PAYMENT_INSTRUCTION_STATUS)
, XMLELEMENT("MEANING"
, INS.INSTRUCTION_STATUS_MEANING) )
, XMLELEMENT("PROCESSINGTYPE"
, INS.PROCESSING_TYPE)
, XMLELEMENT("USERASSIGNEDREFCODE"
, INS.PAY_ADMIN_ASSIGNED_REF_CODE)
, XMLELEMENT("BANKASSIGNEDREFCODE"
, INS.BANK_ASSIGNED_REF_CODE)
, XMLELEMENT("PAYMENTSEQUENCE"
, XMLELEMENT("SEQUENCENAME"
, INS.PERIODIC_SEQUENCE_NAME_1)
, XMLELEMENT("LASTVALUE"
, INS.LAST_USED_NUMBER_1) )
, XMLELEMENT("PAYMENTSEQUENCE"
, XMLELEMENT("SEQUENCENAME"
, INS.PERIODIC_SEQUENCE_NAME_2)
, XMLELEMENT("LASTVALUE"
, INS.LAST_USED_NUMBER_2) )
, XMLELEMENT("PAYMENTSEQUENCE"
, XMLELEMENT("SEQUENCENAME"
, INS.PERIODIC_SEQUENCE_NAME_3)
, XMLELEMENT("LASTVALUE"
, INS.LAST_USED_NUMBER_3) )
, XMLELEMENT("BANKINSTRUCTION"
, XMLELEMENT("BANKINSTRUCTIONCODE"
, INS.BANK_INSTRUCTION1_CODE)
, XMLELEMENT("MEANING"
, INS.BANK_INSTRUCTION1_MEANING)
, XMLELEMENT("FORMATVALUE"
, INS.BANK_INSTRUCTION1_FORMAT_VALUE) )
, XMLELEMENT("BANKINSTRUCTION"
, XMLELEMENT("BANKINSTRUCTIONCODE"
, INS.BANK_INSTRUCTION2_CODE)
, XMLELEMENT("MEANING"
, INS.BANK_INSTRUCTION2_MEANING)
, XMLELEMENT("FORMATVALUE"
, INS.BANK_INSTRUCTION2_FORMAT_VALUE) )
, XMLELEMENT("BANKINSTRUCTIONDETAILS"
, INS.BANK_INSTRUCTION_DETAILS)
, XMLELEMENT("PAYMENTTEXTMESSAGE"
, INS.PAYMENT_TEXT_MESSAGE1)
, XMLELEMENT("PAYMENTTEXTMESSAGE"
, INS.PAYMENT_TEXT_MESSAGE2)
, XMLELEMENT("REGULATORYREPORTINGOPTION"
, INS.DECLARATION_OPTION)
, XMLELEMENT("PAYMENTSYSTEMACCOUNT"
, XMLELEMENT("ACCOUNTNAME"
, INS.BEP_ACCOUNT_NAME)
, IBY_FD_EXTRACT_GEN_PVT.GET_INS_ACCOUNTSETTINGSAGG(INS.BEP_ACCOUNT_ID)
, CASE WHEN INS.PAYSYS_ACCT_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_BEPKEYS'
, INS.BEP_ACCOUNT_ID
, NULL)) END )
, XMLELEMENT("NOTES"
, COMMENTS)
, CASE WHEN INS.PI_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_PAY_INSTRUCTIONS_ALL'
, INS.PAYMENT_INSTRUCTION_ID
, NULL)) END )
, XMLELEMENT("PAYMENTPROCESSPROFILE"
, XMLELEMENT("PAYMENTPROCESSPROFILEINTERNALID"
, INS.PAYMENT_PROFILE_ID)
, XMLELEMENT("PAYMENTPROCESSPROFILENAME"
, INS.PAYMENT_PROFILE_NAME)
, CASE WHEN INS.PAYMENT_PROFILE_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_SYS_PMT_PROFILES_B'
, INS.SYSTEM_PROFILE_CODE
, NULL)) END )
, XMLELEMENT("PAYMENTFORMAT"
, XMLELEMENT("PAYMENTFORMATINTERNALID"
, INS.PAYMENT_FORMAT_CODE)
, XMLELEMENT("PAYMENTFORMATNAME"
, INS.PAYMENT_FORMAT_NAME)
, CASE WHEN INS.PAYMENT_FORMAT_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_FORMATS_B'
, NULL
, INS.PAYMENT_FORMAT_CODE)) END )
, XMLELEMENT("INSTRUCTIONTOTALS"
, XMLELEMENT("PAYERINSTRUMENTCOUNT"
, PAYER_INSTRUMENT_COUNT)
, XMLELEMENT("PAYMENTCOUNT"
, PAYMENT_COUNT) )
, XMLELEMENT("INSTRUCTIONGROUPING"
, CASE WHEN INS.GROUP_BY_PAYMENT_DATE = 'Y' THEN XMLELEMENT("PAYMENTDATE"
, TO_CHAR(INS.PAYMENT_DATE
, 'YYYY-MM-DD"T"HH24:MI:SS')) ELSE NULL END
, CASE WHEN INS.GROUP_BY_PAYMENT_CURRENCY = 'Y' THEN XMLELEMENT("PAYMENTCURRENCY"
, XMLELEMENT("CODE"
, INS.PAYMENT_CURRENCY_CODE)) ELSE NULL END
, CASE WHEN INS.GROUP_BY_PAY_SERVICE_REQUEST = 'Y' THEN XMLELEMENT("PAYMENTSERVICEREQUESTID"
, INS.PAYMENT_SERVICE_REQUEST_ID) ELSE NULL END
, CASE WHEN INS.GROUP_BY_LEGAL_ENTITY = 'Y' THEN XMLELEMENT("PAYERLEGALENTITY"
, XML_PAYER.PAYER) ELSE NULL END
, CASE WHEN INS.GROUP_BY_ORGANIZATION = 'Y' THEN XMLELEMENT("ORGANIZATIONINTERNALID"
, INS.ORG_ID) ELSE NULL END
, CASE WHEN INS.GROUP_BY_PAYMENT_REASON = 'Y' THEN XMLELEMENT("PAYMENTREASON"
, XMLELEMENT("CODE"
, INS.PAYMENT_REASON_CODE)
, XMLELEMENT("MEANING"
, INS.PAY_REASON_MEANING)
, XMLELEMENT("FORMATVALUE"
, INS.PAY_REASON_FORMAT_VALUE)) ELSE NULL END
, CASE WHEN INS.GROUP_BY_PAYMENT_REASON = 'Y' THEN XMLELEMENT("PAYMENTREASONCOMMENTS"
, INS.PAYMENT_REASON_COMMENTS) ELSE NULL END )
, XMLELEMENT("FEDERALINSTRUCTIONINFO"
, IBY_FD_EXTRACT_GEN_PVT.GET_INS_FVFIELDSAGG(INS.PAYMENT_INSTRUCTION_ID))
, IBY_FD_EXTRACT_GEN_PVT.GET_INS_PAYERINSTRAGG(INS.PAYMENT_INSTRUCTION_ID) )
, INS.PAYMENT_INSTRUCTION_ID
FROM IBY_EXT_FD_INS_1_0_V INS
, IBY_XML_FD_PAYER_1_0_V XML_PAYER
WHERE INS.LEGAL_ENTITY_ID = XML_PAYER.LEGAL_ENTITY_ID(+)

Columns