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_FD_PMT_1_0_V0

Source

View Text - Preformatted


SELECT XMLElement("OutboundPayment", 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("EmployeePaymentFlag", pmt.employee_payment_flag)), 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("LogicalGroupReference", pmt.logical_group_reference), XMLElement("CheckNumber", pmt.paper_document_number)), XMLElement("PaymentDate", pmt.payment_date), XMLElement("PaymentDueDate", pmt.payment_due_date), XMLElement("MaturityDate", pmt.maturity_date),XMLElement("AnticipatedValueDate", pmt.anticipated_value_date), XMLElement("PaymentStatus", XMLElement("Code", decode(pmt.payment_status, 'VOID_BY_SETUP_REPRINT', 'VOID_BY_SETUP', 'VOID_BY_OVERFLOW_REPRINT', 'VOID_BY_OVERFLOW', pmt.payment_status)), XMLElement("Meaning", pmt.payment_status_meaning)), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_PmtErrAgg(pmt.payment_id), XMLElement("Payee", IBY_FD_EXTRACT_GEN_PVT.Get_Payee(pmt.payment_id)), /*TPP-Start*/xmlelement("TradingPartner", iby_fd_extract_gen_pvt.get_invpayee(pmt.payment_id))/*TPP-End*/, XMLElement("PayeeBankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeBankAccount(pmt.payment_id, pmt.external_bank_account_id)),IBY_FD_EXTRACT_GEN_PVT.get_PayeeBankAccountAssignment(pmt.payment_id, pmt.external_bank_account_id) ,XMLElement("Payer", xml_payer.payer), XMLElement("BankAccount", xml_payer_bankacct.int_bank_account), XMLElement("Beneficiary", XMLElement("Name", pmt.beneficiary_name)), 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), XMLElement("PaymentMethodFormatValue", pmt.payment_method_format_value), 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("FederalAccountingSymbol", pmt.federal_sps_treasury_symbol)), 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_currency_code, pmt.payment_service_request_id FROM iby_ext_fd_pmt_1_0_v pmt, iby_xml_fd_prba_1_0_v xml_payer_bankacct, iby_xml_fd_payer_1_0_v xml_payer WHERE pmt.internal_bank_account_id = xml_payer_bankacct.bank_account_id(+) AND pmt.legal_entity_id = xml_payer.legal_entity_id(+)

View Text - HTML Formatted

SELECT XMLELEMENT("OUTBOUNDPAYMENT"
, 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("EMPLOYEEPAYMENTFLAG"
, PMT.EMPLOYEE_PAYMENT_FLAG))
, 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("LOGICALGROUPREFERENCE"
, PMT.LOGICAL_GROUP_REFERENCE)
, XMLELEMENT("CHECKNUMBER"
, PMT.PAPER_DOCUMENT_NUMBER))
, XMLELEMENT("PAYMENTDATE"
, PMT.PAYMENT_DATE)
, XMLELEMENT("PAYMENTDUEDATE"
, PMT.PAYMENT_DUE_DATE)
, XMLELEMENT("MATURITYDATE"
, PMT.MATURITY_DATE)
, XMLELEMENT("ANTICIPATEDVALUEDATE"
, PMT.ANTICIPATED_VALUE_DATE)
, XMLELEMENT("PAYMENTSTATUS"
, XMLELEMENT("CODE"
, DECODE(PMT.PAYMENT_STATUS
, 'VOID_BY_SETUP_REPRINT'
, 'VOID_BY_SETUP'
, 'VOID_BY_OVERFLOW_REPRINT'
, 'VOID_BY_OVERFLOW'
, PMT.PAYMENT_STATUS))
, XMLELEMENT("MEANING"
, PMT.PAYMENT_STATUS_MEANING))
, IBY_FD_EXTRACT_GEN_PVT.GET_PMT_PMTERRAGG(PMT.PAYMENT_ID)
, XMLELEMENT("PAYEE"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEE(PMT.PAYMENT_ID))
, /*TPP-START*/XMLELEMENT("TRADINGPARTNER"
, IBY_FD_EXTRACT_GEN_PVT.GET_INVPAYEE(PMT.PAYMENT_ID))/*TPP-END*/
, XMLELEMENT("PAYEEBANKACCOUNT"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEEBANKACCOUNT(PMT.PAYMENT_ID
, PMT.EXTERNAL_BANK_ACCOUNT_ID))
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEEBANKACCOUNTASSIGNMENT(PMT.PAYMENT_ID
, PMT.EXTERNAL_BANK_ACCOUNT_ID)
, XMLELEMENT("PAYER"
, XML_PAYER.PAYER)
, XMLELEMENT("BANKACCOUNT"
, XML_PAYER_BANKACCT.INT_BANK_ACCOUNT)
, XMLELEMENT("BENEFICIARY"
, XMLELEMENT("NAME"
, PMT.BENEFICIARY_NAME))
, 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)
, XMLELEMENT("PAYMENTMETHODFORMATVALUE"
, PMT.PAYMENT_METHOD_FORMAT_VALUE)
, 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("FEDERALACCOUNTINGSYMBOL"
, PMT.FEDERAL_SPS_TREASURY_SYMBOL))
, 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_CURRENCY_CODE
, PMT.PAYMENT_SERVICE_REQUEST_ID
FROM IBY_EXT_FD_PMT_1_0_V PMT
, IBY_XML_FD_PRBA_1_0_V XML_PAYER_BANKACCT
, IBY_XML_FD_PAYER_1_0_V XML_PAYER
WHERE PMT.INTERNAL_BANK_ACCOUNT_ID = XML_PAYER_BANKACCT.BANK_ACCOUNT_ID(+)
AND PMT.LEGAL_ENTITY_ID = XML_PAYER.LEGAL_ENTITY_ID(+)