View: IBY_XML_FD_PPR_PMT_1_0_V

View Text

SELECT XMLELEMENT("PROPOSEDPAYMENT"
, XMLELEMENT("PAYMENTSOURCEINFO"
, XMLELEMENT("APPLICATIONINTERNALID"
, PMT.CALLING_APP_ID)
, XMLELEMENT("APPLICATIONNAME"
, PMT.APPLICATION_NAME)
, XMLELEMENT("PAYMENTSERVICEREQUESTID"
, PMT.PAYMENT_SERVICE_REQUEST_ID)
, XMLELEMENT("FUNCTIONALCATEGORY"
, XMLELEMENT("CODE"
, PMT.PAYMENT_FUNCTION)
, XMLELEMENT("MEANING"
, PMT.PAYMENT_FUNCTION_MEANING))
, XMLELEMENT("PAYERORGANIZATION"
, XMLELEMENT("ORGANIZATIONINTERNALID"
, PMT.PAYMENT_ORG_ID)
, XMLELEMENT("ORGANIZATIONTYPE"
, XMLELEMENT("CODE"
, PMT.PAYMENT_ORG_TYPE)
, XMLELEMENT("MEANING"
, PMT.PAYMENT_ORG_TYPE_MEANING))
, XMLELEMENT("ORGANIZATIONNAME"
, PMT.PAYMENT_ORG_NAME)))
, XMLELEMENT("PAYMENTNUMBER"
, XMLELEMENT("DOCCATEGORY"
, PMT.PAYMENT_DOC_CATEGORY)
, XMLELEMENT("SEQUENCENAME"
, PMT.PAYMENT_DOC_SEQUENCE_NAME)
, XMLELEMENT("SEQUENCEVALUE"
, PMT.PAYMENT_DOC_SEQUENCE_VALUE)
, XMLELEMENT("PAYMENTREFERENCENUMBER"
, PMT.PAYMENT_REFERENCE_NUMBER)
, XMLELEMENT("CHECKNUMBER"
, PMT.PAPER_DOCUMENT_NUMBER))
, XMLELEMENT("PAYMENTDATE"
, PMT.PAYMENT_DATE)
, XMLELEMENT("PAYMENTDUEDATE"
, PMT.PAYMENT_DUE_DATE)
, XMLELEMENT("MATURITYDATE"
, PMT.MATURITY_DATE)
, XMLELEMENT("PAYMENTSTATUS"
, XMLELEMENT("CODE"
, PMT.PAYMENT_STATUS)
, XMLELEMENT("MEANING"
, PMT.PAYMENT_STATUS_MEANING))
, IBY_FD_EXTRACT_GEN_PVT.GET_PMT_PMTERRAGG(PMT.PAYMENT_ID)
, XMLELEMENT("PAYEE"
, XML_PAYEE.PAYEE)
, CASE WHEN PMT.EXTERNAL_BANK_ACCOUNT_ID IS NOT NULL THEN XMLELEMENT("PAYEEBANKACCOUNT"
, XML_PAYEE_BANKACCT.EXT_BANK_ACCOUNT) ELSE NULL END
, XMLELEMENT("PAYER"
, XML_PAYER.PAYER)
, XMLELEMENT("BANKACCOUNT"
, XML_PAYER_BANKACCT.INT_BANK_ACCOUNT)
, XMLELEMENT("PAYMENTAMOUNT"
, XMLELEMENT("VALUE"
, PMT.PAYMENT_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, PMT.PAYMENT_CURRENCY_CODE)))
, XMLELEMENT("PAYMENTAMOUNTTEXT"
, PMT.PAYMENT_AMOUNT_TEXT)
, XMLELEMENT("PAYMENTMETHOD"
, XMLELEMENT("PAYMENTMETHODINTERNALID"
, PMT.PAYMENT_METHOD_CODE)
, XMLELEMENT("PAYMENTMETHODNAME"
, PMT.PAYMENT_METHOD_NAME)
, CASE WHEN PMT.PAYMENT_METHOD_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_PAYMENT_METHODS_B'
, NULL
, PMT.PAYMENT_METHOD_CODE)) END)
, XMLELEMENT("PAYALONGFLAG"
, PMT.EXCLUSIVE_PAYMENT_FLAG)
, XMLELEMENT("SETTLEMENTPRIORITY"
, XMLELEMENT("CODE"
, PMT.SETTLEMENT_PRIORITY)
, XMLELEMENT("MEANING"
, PMT.SETTLEMENT_PRIORITY_MEANING))
, XMLELEMENT("AMOUNTWITHHELD"
, XMLELEMENT("VALUE"
, PMT.AMOUNT_WITHHELD)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, PMT.PAYMENT_CURRENCY_CODE)))
, CASE WHEN PMT.DISCOUNT_AMOUNT_TAKEN IS NOT NULL THEN XMLELEMENT("DISCOUNTTAKEN"
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, PMT.DISCOUNT_AMOUNT_TAKEN)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, PMT.PAYMENT_CURRENCY_CODE)))) ELSE NULL END
, CASE WHEN PMT.BANK_CHARGE_AMOUNT IS NOT NULL THEN XMLELEMENT("BANKCHARGES"
, XMLELEMENT("BANKCHARGEBEARER"
, XMLELEMENT("CODE"
, PMT.BANK_CHARGE_BEARER)
, XMLELEMENT("MEANING"
, PMT.BANK_CHARGE_BEARER_MEANING))
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, PMT.BANK_CHARGE_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, PMT.PAYMENT_CURRENCY_CODE)))) ELSE NULL END
, XMLELEMENT("DELIVERYCHANNEL"
, XMLELEMENT("CODE"
, PMT.DELIVERY_CHANNEL_CODE)
, XMLELEMENT("MEANING"
, PMT.DELIVERY_CHANNEL_MEANING)
, XMLELEMENT("FORMATVALUE"
, PMT.DELIVERY_CHANNEL_FORMAT_VALUE))
, XMLELEMENT("PAYMENTREASON"
, XMLELEMENT("CODE"
, PMT.PAYMENT_REASON_CODE)
, XMLELEMENT("MEANING"
, PMT.PAYMENT_REASON_MEANING)
, XMLELEMENT("FORMATVALUE"
, PMT.PAYMENT_REASON_FORMAT_VALUE))
, XMLELEMENT("PAYMENTREASONCOMMENTS"
, PMT.PAYMENT_REASON_COMMENTS)
, XMLELEMENT("REMITTANCEMESSAGE"
, PMT.REMITTANCE_MESSAGE1)
, XMLELEMENT("REMITTANCEMESSAGE"
, PMT.REMITTANCE_MESSAGE2)
, XMLELEMENT("REMITTANCEMESSAGE"
, PMT.REMITTANCE_MESSAGE3)
, CASE WHEN PMT.UNIQUE_REMITTANCE_IDENTIFIER IS NOT NULL THEN XMLELEMENT("UNIQUEREMITTANCEIDENTIFIER"
, XMLELEMENT("NUMBER"
, PMT.UNIQUE_REMITTANCE_IDENTIFIER)
, XMLELEMENT("CHECKDIGIT"
, PMT.URI_CHECK_DIGIT)) ELSE NULL END
, XMLELEMENT("BANKINSTRUCTION"
, XMLELEMENT("BANKINSTRUCTIONCODE"
, PMT.BANK_INSTRUCTION1_CODE)
, XMLELEMENT("MEANING"
, PMT.BANK_INSTRUCTION1_MEANING)
, XMLELEMENT("FORMATVALUE"
, PMT.BANK_INSTRUCTION1_FORMAT_VALUE))
, XMLELEMENT("BANKINSTRUCTION"
, XMLELEMENT("BANKINSTRUCTIONCODE"
, PMT.BANK_INSTRUCTION2_CODE)
, XMLELEMENT("MEANING"
, PMT.BANK_INSTRUCTION2_MEANING)
, XMLELEMENT("FORMATVALUE"
, PMT.BANK_INSTRUCTION2_FORMAT_VALUE))
, XMLELEMENT("BANKINSTRUCTIONDETAILS"
, PMT.BANK_INSTRUCTION_DETAILS)
, XMLELEMENT("PAYMENTTEXTMESSAGE"
, PMT.PAYMENT_TEXT_MESSAGE1)
, XMLELEMENT("PAYMENTTEXTMESSAGE"
, PMT.PAYMENT_TEXT_MESSAGE2)
, XMLELEMENT("PAYMENTTEXTMESSAGE"
, PMT.PAYMENT_TEXT_MESSAGE3)
, XMLELEMENT("PAYMENTDETAILS"
, PMT.PAYMENT_DETAILS)
, XMLELEMENT("REGULATORYREPORTING"
, XMLELEMENT("DECLARATIONFLAG"
, PMT.DECLARE_PAYMENT_FLAG)
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, PMT.DECLARATION_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, PMT.DECLARATION_CURRENCY_CODE))))
, XMLELEMENT("FEDERALPAYMENTINFO"
, XMLELEMENT("FEDERALALLOTMENTCODE"
, PMT.FEDERAL_ALLOTMENT_CODE)
, XMLELEMENT("FEDERALOFFSETELIGIBILITYFLAG"
, PMT.FEDERAL_OFFSET_ELIGIBILITY))
, XMLELEMENT("DOCUMENTPAYABLECOUNT"
, IBY_FD_EXTRACT_GEN_PVT.GET_PMT_DOCPAYABLECOUNT(PMT.PAYMENT_ID))
, CASE WHEN PMT.PAYMENT_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_PAYMENTS_ALL'
, PMT.PAYMENT_ID
, NULL)) END
, IBY_FD_EXTRACT_GEN_PVT.GET_PMT_DOCPAYABLEAGG(PMT.PAYMENT_ID) )
, PMT.PAYMENT_INSTRUCTION_ID
, PMT.INTERNAL_BANK_ACCOUNT_ID
, PMT.LEGAL_ENTITY_ID
, PMT.PAYMENT_ID
, PMT.PAYMENT_STATUS
, PMT.PAYMENT_SERVICE_REQUEST_ID
FROM IBY_EXT_FD_PMT_1_0_V PMT
, IBY_XML_FD_PAYEE_1_0_V XML_PAYEE
, IBY_XML_FD_PEBA_1_0_V XML_PAYEE_BANKACCT
, IBY_XML_FD_PRBA_1_0_V XML_PAYER_BANKACCT
, IBY_XML_FD_PAYER_1_0_V XML_PAYER
WHERE PMT.PAYMENT_ID = XML_PAYEE.PAYMENT_ID
AND PMT.EXTERNAL_BANK_ACCOUNT_ID = XML_PAYEE_BANKACCT.BANK_ACCOUNT_ID(+)
AND PMT.INTERNAL_BANK_ACCOUNT_ID = XML_PAYER_BANKACCT.BANK_ACCOUNT_ID
AND PMT.LEGAL_ENTITY_ID = XML_PAYER.LEGAL_ENTITY_ID

Columns