VIEW: APPS.IBY_XML_FD_INS_1_0_V

Source

View Text - Preformatted

SELECT XMLElement("OutboundPaymentInstruction", XMLElement("PaymentInstructionInfo", XMLElement("InstructionReferenceNumber", ins.payment_instruction_id), XMLElement("FormatProgramRequestID", fnd_global.CONC_REQUEST_ID), XMLElement("InstructionCreationDate", TO_CHAR(ins.instruction_created_date, 'YYYY-MM-DD"T"HH24:MI:SS')), XMLElement("MultiOrgAccessControl", XMLElement("HasBlockedData", ins.moac_data_blocked_flag) ), XMLElement("InstructionStatus", XMLElement("Code", ins.payment_instruction_status), XMLElement("Meaning", ins.instruction_status_meaning) ), XMLElement("ProcessingType", ins.processing_type), XMLElement("ProcessType", ins.process_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("CardIssuerFileSequenceNo", ins.card_issuer_file_seq_no), XMLElement("PaymentSystemAccount", XMLElement("AccountName", ins.bep_account_name), CASE WHEN ins.bep_account_id IS NULL OR ins.bep_account_id = -99 THEN NULL ELSE IBY_FD_EXTRACT_GEN_PVT.Get_Ins_AccountSettingsAgg(ins.bep_account_id) END, 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), XMLElement("LogicalGroupingMode", ins.logical_grouping_mode), XMLElement("BatchBookingFlag", ins.batch_booking_flag), CASE WHEN ins.payment_profile_attr_cat IS NULL THEN NULL ELSE XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_SYS_PMT_PROFILES_B', NULL, ins.system_profile_code)) 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 ), CASE WHEN ins.payment_document_name IS NOT NULL THEN XMLElement("CheckFormatInfo", XMLElement("PaymentDocumentName", ins.payment_document_name), XMLElement("PaperStockType", XMLElement("Code", ins.paper_stock_type), XMLElement("Meaning", ins.paper_stock_type_meaning)) ) ELSE NULL END, XMLElement("InstructionTotals", XMLElement("PaymentCount", ins.payment_count), XMLElement("TotalPaymentAmount", XMLElement("Value", ins.total_pmt_amount), XMLElement("Currency", XMLElement("Code", ins.payment_currency_code))) ), 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), XMLElement("Name", fnd_curr.name)) ELSE NULL END, CASE WHEN ins.group_by_pay_service_request = 'Y' THEN XMLElement("PaymentServiceRequestID", ins.payment_service_request_id) ELSE NULL END, XMLElement("Payer", IBY_FD_EXTRACT_GEN_PVT.Get_PayerIns_Denorm(payment_instruction_id)), 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, XMLElement("BankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayerBankAccountIns_Denorm(payment_instruction_id)), 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, CASE WHEN ins.group_by_payment_method = 'Y' THEN XMLElement("PaymentMethod", ins.payment_method_code) ELSE NULL END ), IBY_FD_EXTRACT_GEN_PVT.Get_Ins_FVFieldsAgg(ins.payment_instruction_id), IBY_FD_EXTRACT_GEN_PVT.Get_Ins_PayerInstrAgg(ins.payment_instruction_id), IBY_EXT_WRAP_PUB.Get_Ins_Ext_Agg(ins.payment_instruction_id) )INSTRUCTION, ins.payment_instruction_id PAYMENT_INSTRUCTION_ID FROM iby_ext_fd_ins_1_0_v ins, fnd_currencies_vl fnd_curr WHERE ins.payment_currency_code = fnd_curr.currency_code(+)

View Text - HTML Formatted

SELECT XMLELEMENT("OUTBOUNDPAYMENTINSTRUCTION"
, XMLELEMENT("PAYMENTINSTRUCTIONINFO"
, XMLELEMENT("INSTRUCTIONREFERENCENUMBER"
, INS.PAYMENT_INSTRUCTION_ID)
, XMLELEMENT("FORMATPROGRAMREQUESTID"
, FND_GLOBAL.CONC_REQUEST_ID)
, XMLELEMENT("INSTRUCTIONCREATIONDATE"
, TO_CHAR(INS.INSTRUCTION_CREATED_DATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("MULTIORGACCESSCONTROL"
, XMLELEMENT("HASBLOCKEDDATA"
, INS.MOAC_DATA_BLOCKED_FLAG) )
, XMLELEMENT("INSTRUCTIONSTATUS"
, XMLELEMENT("CODE"
, INS.PAYMENT_INSTRUCTION_STATUS)
, XMLELEMENT("MEANING"
, INS.INSTRUCTION_STATUS_MEANING) )
, XMLELEMENT("PROCESSINGTYPE"
, INS.PROCESSING_TYPE)
, XMLELEMENT("PROCESSTYPE"
, INS.PROCESS_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("CARDISSUERFILESEQUENCENO"
, INS.CARD_ISSUER_FILE_SEQ_NO)
, XMLELEMENT("PAYMENTSYSTEMACCOUNT"
, XMLELEMENT("ACCOUNTNAME"
, INS.BEP_ACCOUNT_NAME)
, CASE WHEN INS.BEP_ACCOUNT_ID IS NULL OR INS.BEP_ACCOUNT_ID = -99 THEN NULL ELSE IBY_FD_EXTRACT_GEN_PVT.GET_INS_ACCOUNTSETTINGSAGG(INS.BEP_ACCOUNT_ID) END
, 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)
, XMLELEMENT("LOGICALGROUPINGMODE"
, INS.LOGICAL_GROUPING_MODE)
, XMLELEMENT("BATCHBOOKINGFLAG"
, INS.BATCH_BOOKING_FLAG)
, CASE WHEN INS.PAYMENT_PROFILE_ATTR_CAT IS NULL THEN NULL ELSE XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_SYS_PMT_PROFILES_B'
, NULL
, INS.SYSTEM_PROFILE_CODE)) 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 )
, CASE WHEN INS.PAYMENT_DOCUMENT_NAME IS NOT NULL THEN XMLELEMENT("CHECKFORMATINFO"
, XMLELEMENT("PAYMENTDOCUMENTNAME"
, INS.PAYMENT_DOCUMENT_NAME)
, XMLELEMENT("PAPERSTOCKTYPE"
, XMLELEMENT("CODE"
, INS.PAPER_STOCK_TYPE)
, XMLELEMENT("MEANING"
, INS.PAPER_STOCK_TYPE_MEANING)) ) ELSE NULL END
, XMLELEMENT("INSTRUCTIONTOTALS"
, XMLELEMENT("PAYMENTCOUNT"
, INS.PAYMENT_COUNT)
, XMLELEMENT("TOTALPAYMENTAMOUNT"
, XMLELEMENT("VALUE"
, INS.TOTAL_PMT_AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, INS.PAYMENT_CURRENCY_CODE))) )
, 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)
, XMLELEMENT("NAME"
, FND_CURR.NAME)) ELSE NULL END
, CASE WHEN INS.GROUP_BY_PAY_SERVICE_REQUEST = 'Y' THEN XMLELEMENT("PAYMENTSERVICEREQUESTID"
, INS.PAYMENT_SERVICE_REQUEST_ID) ELSE NULL END
, XMLELEMENT("PAYER"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYERINS_DENORM(PAYMENT_INSTRUCTION_ID))
, 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
, XMLELEMENT("BANKACCOUNT"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYERBANKACCOUNTINS_DENORM(PAYMENT_INSTRUCTION_ID))
, 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
, CASE WHEN INS.GROUP_BY_PAYMENT_METHOD = 'Y' THEN XMLELEMENT("PAYMENTMETHOD"
, INS.PAYMENT_METHOD_CODE) ELSE NULL END )
, IBY_FD_EXTRACT_GEN_PVT.GET_INS_FVFIELDSAGG(INS.PAYMENT_INSTRUCTION_ID)
, IBY_FD_EXTRACT_GEN_PVT.GET_INS_PAYERINSTRAGG(INS.PAYMENT_INSTRUCTION_ID)
, IBY_EXT_WRAP_PUB.GET_INS_EXT_AGG(INS.PAYMENT_INSTRUCTION_ID) )INSTRUCTION
, INS.PAYMENT_INSTRUCTION_ID PAYMENT_INSTRUCTION_ID
FROM IBY_EXT_FD_INS_1_0_V INS
, FND_CURRENCIES_VL FND_CURR
WHERE INS.PAYMENT_CURRENCY_CODE = FND_CURR.CURRENCY_CODE(+)