VIEW: APPS.IBY_XML_FD_INS_1_0_V0

Source

View Text - Preformatted

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("PaymentCount", ins.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("Payer", xml_payer.payer) ELSE NULL END, CASE WHEN ins.group_by_organization = 'Y' THEN XMLElement("PayerOrganization", XMLElement("OrganizationInternalID", ins.org_id), XMLElement("OrganizationType", XMLElement("Code", ins.org_type), XMLElement("Meaning", ins.org_type_meaning)), XMLElement("OrganizationName", ins.org_name)) ELSE NULL END, CASE WHEN ins.GROUP_BY_INTERNAL_BANK_ACCOUNT = 'Y' THEN XMLElement("BankAccount", xml_prba.int_bank_account) 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, CASE WHEN ins.group_by_payment_function = 'Y' THEN XMLElement("FunctionalCategory", XMLElement("Code", ins.payment_function), XMLElement("Meaning", ins.payment_function_meaning)) ELSE NULL END, CASE WHEN ins.group_by_rfc = 'Y' THEN XMLElement("FederalRFCIdentifier", ins.rfc_identifier) 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, iby_xml_fd_prba_1_0_v xml_prba WHERE ins.legal_entity_id = xml_payer.legal_entity_id(+) and ins.internal_bank_account_id = xml_prba.bank_account_id(+)

View Text - HTML Formatted

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("PAYMENTCOUNT"
, INS.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("PAYER"
, XML_PAYER.PAYER) ELSE NULL END
, CASE WHEN INS.GROUP_BY_ORGANIZATION = 'Y' THEN XMLELEMENT("PAYERORGANIZATION"
, XMLELEMENT("ORGANIZATIONINTERNALID"
, INS.ORG_ID)
, XMLELEMENT("ORGANIZATIONTYPE"
, XMLELEMENT("CODE"
, INS.ORG_TYPE)
, XMLELEMENT("MEANING"
, INS.ORG_TYPE_MEANING))
, XMLELEMENT("ORGANIZATIONNAME"
, INS.ORG_NAME)) ELSE NULL END
, CASE WHEN INS.GROUP_BY_INTERNAL_BANK_ACCOUNT = 'Y' THEN XMLELEMENT("BANKACCOUNT"
, XML_PRBA.INT_BANK_ACCOUNT) 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
, CASE WHEN INS.GROUP_BY_PAYMENT_FUNCTION = 'Y' THEN XMLELEMENT("FUNCTIONALCATEGORY"
, XMLELEMENT("CODE"
, INS.PAYMENT_FUNCTION)
, XMLELEMENT("MEANING"
, INS.PAYMENT_FUNCTION_MEANING)) ELSE NULL END
, CASE WHEN INS.GROUP_BY_RFC = 'Y' THEN XMLELEMENT("FEDERALRFCIDENTIFIER"
, INS.RFC_IDENTIFIER) 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
, IBY_XML_FD_PRBA_1_0_V XML_PRBA
WHERE INS.LEGAL_ENTITY_ID = XML_PAYER.LEGAL_ENTITY_ID(+)
AND INS.INTERNAL_BANK_ACCOUNT_ID = XML_PRBA.BANK_ACCOUNT_ID(+)