IBY_XML_FD_PMT_1_0_V

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
PAYMENT PAYMENT_INSTRUCTION_ID INTERNAL_BANK_ACCOUNT_ID LEGAL_ENTITY_ID PAYMENT_ID PAYMENT_STATUS PAYMENT_CURRENCY_CODE PAYMENT_SERVICE_REQUEST_ID PAYMENT_DATE PAYMENT_PROFILE_ID POSITIVE_PAY_FILE_CREATED_FLAG

Name

PAYMENT

PAYMENT_INSTRUCTION_ID

INTERNAL_BANK_ACCOUNT_ID

LEGAL_ENTITY_ID

PAYMENT_ID

PAYMENT_STATUS

PAYMENT_CURRENCY_CODE

PAYMENT_SERVICE_REQUEST_ID

PAYMENT_DATE

PAYMENT_PROFILE_ID

POSITIVE_PAY_FILE_CREATED_FLAG

Query

SQL_Statement
SELECT decode(nvl(iby_utility_pvt.get_view_param('FMT_TYPE'), 'x'), 'IBY_FD_PPR_STATUS', XMLElement("ProposedPayment", XMLElement("RecordHistory",XMLElement("CreatedBy",pmt.created_by),XMLElement("CreationDate",to_char(pmt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(pmt.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",pmt.last_update_login),XMLElement("LastUpdatedBy",pmt.last_updated_by)),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("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), CASE WHEN pmt.payment_status = 'VOID' THEN XMLELEMENT("VoidDate",pmt.void_date) END), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_PmtErrAgg(pmt.payment_id), XMLElement("Payee", IBY_FD_EXTRACT_GEN_PVT.Get_Payee(pmt.payment_id,pmt.employee_payment_flag)), XMLElement("SupplierorParty", IBY_FD_EXTRACT_GEN_PVT.Get_SupplierorParty_Denorm(pmt.payment_id,pmt.inv_ext_payee_id,pmt.payee_party_id,pmt.payee_party_site_use_id,pmt.inv_supplier_site_id,pmt.payment_date)), XMLElement("PayeeBankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeBankAccount_Denorm(pmt.payment_id, pmt.external_bank_account_id,pmt.employee_payment_flag)), XMLElement("Payer", IBY_FD_EXTRACT_GEN_PVT.Get_Payer_Denorm(payment_id)), XMLElement("BankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayerBankAccount_Denorm(payment_id)), XMLElement("Beneficiary", XMLElement("Name", pmt.beneficiary_name)), XMLElement("PaymentAmount", XMLElement("Value", NVL(pmt.payment_amount, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code), XMLElement("Name", fnd_curr.name))), 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),XMLElement("PaymentMode", pmt.payment_mode_code),XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENT_METHODS_B', NULL, pmt.payment_method_code)) ), XMLElement("PayAlongFlag", pmt.exclusive_payment_flag), XMLElement("SettlementPriority", XMLElement("Code", pmt.settlement_priority), XMLElement("Meaning", pmt.settlement_priority_meaning)), XMLElement("AmountWithheld", XMLElement("Value", NVL(pmt.amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code))), CASE WHEN pmt.discount_amount_taken IS NOT NULL THEN XMLElement("DiscountTaken", XMLElement("Amount", XMLElement("Value", NVL(pmt.discount_amount_taken, 0)), 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", NVL(pmt.bank_charge_amount, 0)), 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("ServiceLevel", XMLElement("Code", pmt.service_level_code), XMLElement("Meaning", pmt.service_level_meaning), XMLElement("FormatValue", pmt.service_level_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", NVL(pmt.declaration_amount, 0)), 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("LogicalGrouping", XMLElement("PaymentInformationAmountTotal", pmt.total_payment_amount), XMLElement("PaymentInformationTotal", pmt.total_payment_count), XMLElement("LogicalGroupReference", pmt.logical_group_reference)),iby_fd_extract_gen_pvt.get_payment_instrument(pmt.payment_id), XMLElement("DocumentPayableCount", IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableCount(pmt.payment_id)), XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENTS_ALL', pmt.payment_id, NULL)), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableAgg(pmt.payment_id), IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg(pmt.payment_id), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_BR_Ext_Agg(pmt.payment_id), XMLElement("InvoicingLegalEntity", IBY_FD_EXTRACT_GEN_PVT.Get_Inv_LE_Details(pmt.payment_id)) ), XMLElement("OutboundPayment", XMLElement("RecordHistory",XMLElement("CreatedBy",pmt.created_by),XMLElement("CreationDate",to_char(pmt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(pmt.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",pmt.last_update_login),XMLElement("LastUpdatedBy",pmt.last_updated_by)),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("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("PaymentDescription", iby_extractgen_pvt.get_quick_pmt_description(pmt.payment_id)) , 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), CASE WHEN pmt.payment_status = 'VOID' THEN XMLELEMENT("VoidDate",pmt.void_date) END), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_PmtErrAgg(pmt.payment_id), XMLElement("Payee", IBY_FD_EXTRACT_GEN_PVT.Get_Payee(pmt.payment_id,pmt.employee_payment_flag)), XMLElement("SupplierorParty", IBY_FD_EXTRACT_GEN_PVT.Get_SupplierorParty_Denorm(pmt.payment_id,pmt.inv_ext_payee_id,pmt.payee_party_id,pmt.payee_party_site_use_id,pmt.inv_supplier_site_id,pmt.payment_date)), XMLElement("PayeeBankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeBankAccount_Denorm(pmt.payment_id, pmt.external_bank_account_id,pmt.employee_payment_flag)), XMLElement("Payer", IBY_FD_EXTRACT_GEN_PVT.Get_Payer_Denorm(payment_id)), XMLElement("BankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayerBankAccount_Denorm(payment_id)), XMLElement("Beneficiary", XMLElement("Name", pmt.beneficiary_name)), XMLElement("PaymentAmount", XMLElement("Value", NVL(pmt.payment_amount, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code),CASE WHEN nvl(iby_utility_pvt.get_view_param('INCLUDE_CURR_NUMERIC_CODE'), 'x') = 'Y' AND pmt.payment_mode_code IS NOT NULL THEN XMLElement("NumericCode", fnd_curr.digital_currency_code) END, XMLElement("Name", fnd_curr.name))), XMLElement("PaymentAmountText", pmt.payment_amount_text),XMLElement("PaymentAmountInLedgerCurrency", IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_Exchange_Details(pmt.payment_id,NVL(pmt.payment_amount, 0),pmt.payment_org_id,pmt.payment_service_request_id,pmt.payment_currency_code)), IBY_FD_EXTRACT_GEN_PVT.get_vat_payment_amount(pmt.payment_id,NVL(pmt.payment_amount, 0)), XMLElement("PaymentMethod", XMLElement("PaymentMethodInternalID", pmt.payment_method_code), XMLElement("PaymentMethodName", pmt.payment_method_name), XMLElement("PaymentMethodFormatValue", pmt.payment_method_format_value), XMLElement("PaymentMode", pmt.payment_mode_code), XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENT_METHODS_B', NULL, pmt.payment_method_code)) ), XMLElement("PayAlongFlag", pmt.exclusive_payment_flag), XMLElement("SettlementPriority", XMLElement("Code", pmt.settlement_priority), XMLElement("Meaning", pmt.settlement_priority_meaning)), XMLElement("AmountWithheld", XMLElement("Value", NVL(pmt.amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code))), CASE WHEN pmt.discount_amount_taken IS NOT NULL THEN XMLElement("DiscountTaken", XMLElement("Amount", XMLElement("Value", NVL(pmt.discount_amount_taken, 0)), 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", NVL(pmt.bank_charge_amount, 0)), 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("ServiceLevel", XMLElement("Code", pmt.service_level_code), XMLElement("Meaning", pmt.service_level_meaning), XMLElement("FormatValue", pmt.service_level_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", NVL(pmt.declaration_amount, 0)), 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("LogicalGrouping", XMLElement("PaymentInformationAmountTotal", pmt.total_payment_amount), XMLElement("PaymentInformationTotal", pmt.total_payment_count), XMLElement("LogicalGroupReference", pmt.logical_group_reference)), CASE WHEN pmt.acknowledgement_date IS NOT NULL THEN XMLElement("AcknowledgementDetails", XMLElement("BepStatus", pmt.bep_status), XMLElement("BepErrorCode", pmt.bep_error_code), XMLElement("BepErrorMessage", pmt.bep_error_message_text), XMLElement("BepTrxnResponseId", pmt.bep_trxn_response_id)) ELSE NULL END,CASE WHEN PMT.ACKNOWLEDGEMENT_DATE IS NOT NULL THEN iby_fd_extract_gen_pvt.get_inv_hold_details(pmt.payment_id) ELSE NULL END, CASE WHEN pmt.payment_mode_code IS NOT NULL THEN XMLElement("BEPResponse",(XMLElement("AcquirerTransactionReference",pmt.acquirer_transaction_reference)),(XMLElement("IssuerAuthorizationReference",pmt.issuer_authorization_reference))) ELSE NULL END, iby_fd_extract_gen_pvt.get_payment_instrument(pmt.payment_id), iby_fd_extract_gen_pvt.get_card_program_details(pmt.payment_id), iby_fd_extract_gen_pvt.get_digital_payment_acct_det(pmt.payment_id), XMLElement("DocumentPayableCount", IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableCount(pmt.payment_id)), XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENTS_ALL', pmt.payment_id, NULL)), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableAgg(pmt.payment_id), IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg(pmt.payment_id), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_BR_Ext_Agg(pmt.payment_id),XMLElement("InvoicingLegalEntity", IBY_FD_EXTRACT_GEN_PVT.Get_Inv_LE_Details(pmt.payment_id)),XMLElement("SrcDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'AP_CHECKS_ALL', pmt.payment_id, NULL)),XMLElement("SrcGlobalDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Gdfs( 'AP_CHECKS_ALL', pmt.payment_id, NULL)),IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_AR_Ext_Agg(pmt.legal_entity_id,pmt.payment_org_id,pmt.payment_instruction_id,pmt.payment_date,pmt.payment_id),XMLELEMENT("FederalPMTExtend",iby_fd_extract_gen_pvt.get_pmt_fvfieldsAgg(pmt.payment_instruction_id,pmt.payment_id)))) PAYMENT, 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, pmt.payment_date, pmt.payment_profile_id, pmt.positive_pay_file_created_flag FROM iby_ext_fd_pmt_1_0_v pmt, fnd_currencies_vl fnd_curr WHERE pmt.payment_currency_code = fnd_curr.currency_code(+)

SQL_Statement

SELECT decode(nvl(iby_utility_pvt.get_view_param('FMT_TYPE'), 'x'), 'IBY_FD_PPR_STATUS', XMLElement("ProposedPayment", XMLElement("RecordHistory",XMLElement("CreatedBy",pmt.created_by),XMLElement("CreationDate",to_char(pmt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(pmt.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",pmt.last_update_login),XMLElement("LastUpdatedBy",pmt.last_updated_by)),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("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), CASE WHEN pmt.payment_status = 'VOID' THEN XMLELEMENT("VoidDate",pmt.void_date) END), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_PmtErrAgg(pmt.payment_id), XMLElement("Payee", IBY_FD_EXTRACT_GEN_PVT.Get_Payee(pmt.payment_id,pmt.employee_payment_flag)), XMLElement("SupplierorParty", IBY_FD_EXTRACT_GEN_PVT.Get_SupplierorParty_Denorm(pmt.payment_id,pmt.inv_ext_payee_id,pmt.payee_party_id,pmt.payee_party_site_use_id,pmt.inv_supplier_site_id,pmt.payment_date)), XMLElement("PayeeBankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeBankAccount_Denorm(pmt.payment_id, pmt.external_bank_account_id,pmt.employee_payment_flag)), XMLElement("Payer", IBY_FD_EXTRACT_GEN_PVT.Get_Payer_Denorm(payment_id)), XMLElement("BankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayerBankAccount_Denorm(payment_id)), XMLElement("Beneficiary", XMLElement("Name", pmt.beneficiary_name)), XMLElement("PaymentAmount", XMLElement("Value", NVL(pmt.payment_amount, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code), XMLElement("Name", fnd_curr.name))), 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),XMLElement("PaymentMode", pmt.payment_mode_code),XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENT_METHODS_B', NULL, pmt.payment_method_code)) ),

XMLElement("PayAlongFlag", pmt.exclusive_payment_flag), XMLElement("SettlementPriority", XMLElement("Code", pmt.settlement_priority), XMLElement("Meaning", pmt.settlement_priority_meaning)), XMLElement("AmountWithheld", XMLElement("Value", NVL(pmt.amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code))), CASE WHEN pmt.discount_amount_taken IS NOT NULL THEN XMLElement("DiscountTaken", XMLElement("Amount", XMLElement("Value", NVL(pmt.discount_amount_taken, 0)), 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", NVL(pmt.bank_charge_amount, 0)), 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("ServiceLevel", XMLElement("Code", pmt.service_level_code), XMLElement("Meaning", pmt.service_level_meaning), XMLElement("FormatValue", pmt.service_level_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", NVL(pmt.declaration_amount, 0)), 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("LogicalGrouping", XMLElement("PaymentInformationAmountTotal", pmt.total_payment_amount), XMLElement("PaymentInformationTotal", pmt.total_payment_count),

XMLElement("LogicalGroupReference", pmt.logical_group_reference)),iby_fd_extract_gen_pvt.get_payment_instrument(pmt.payment_id), XMLElement("DocumentPayableCount", IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableCount(pmt.payment_id)), XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENTS_ALL', pmt.payment_id, NULL)), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableAgg(pmt.payment_id), IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg(pmt.payment_id), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_BR_Ext_Agg(pmt.payment_id), XMLElement("InvoicingLegalEntity", IBY_FD_EXTRACT_GEN_PVT.Get_Inv_LE_Details(pmt.payment_id)) ),

XMLElement("OutboundPayment", XMLElement("RecordHistory",XMLElement("CreatedBy",pmt.created_by),XMLElement("CreationDate",to_char(pmt.creation_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateDate",to_char(pmt.last_update_date, 'YYYY-MM-DD"T"HH24:MI:SS')),XMLElement("LastUpdateLogin",pmt.last_update_login),XMLElement("LastUpdatedBy",pmt.last_updated_by)),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("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("PaymentDescription", iby_extractgen_pvt.get_quick_pmt_description(pmt.payment_id)) , 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), CASE WHEN pmt.payment_status = 'VOID' THEN XMLELEMENT("VoidDate",pmt.void_date) END), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_PmtErrAgg(pmt.payment_id), XMLElement("Payee", IBY_FD_EXTRACT_GEN_PVT.Get_Payee(pmt.payment_id,pmt.employee_payment_flag)), XMLElement("SupplierorParty", IBY_FD_EXTRACT_GEN_PVT.Get_SupplierorParty_Denorm(pmt.payment_id,pmt.inv_ext_payee_id,pmt.payee_party_id,pmt.payee_party_site_use_id,pmt.inv_supplier_site_id,pmt.payment_date)), XMLElement("PayeeBankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayeeBankAccount_Denorm(pmt.payment_id, pmt.external_bank_account_id,pmt.employee_payment_flag)), XMLElement("Payer", IBY_FD_EXTRACT_GEN_PVT.Get_Payer_Denorm(payment_id)), XMLElement("BankAccount", IBY_FD_EXTRACT_GEN_PVT.Get_PayerBankAccount_Denorm(payment_id)), XMLElement("Beneficiary", XMLElement("Name", pmt.beneficiary_name)), XMLElement("PaymentAmount", XMLElement("Value", NVL(pmt.payment_amount, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code),CASE WHEN nvl(iby_utility_pvt.get_view_param('INCLUDE_CURR_NUMERIC_CODE'), 'x') = 'Y' AND pmt.payment_mode_code IS NOT NULL THEN XMLElement("NumericCode", fnd_curr.digital_currency_code) END, XMLElement("Name", fnd_curr.name))), XMLElement("PaymentAmountText", pmt.payment_amount_text),XMLElement("PaymentAmountInLedgerCurrency", IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_Exchange_Details(pmt.payment_id,NVL(pmt.payment_amount, 0),pmt.payment_org_id,pmt.payment_service_request_id,pmt.payment_currency_code)), IBY_FD_EXTRACT_GEN_PVT.get_vat_payment_amount(pmt.payment_id,NVL(pmt.payment_amount, 0)),

XMLElement("PaymentMethod", XMLElement("PaymentMethodInternalID", pmt.payment_method_code), XMLElement("PaymentMethodName", pmt.payment_method_name), XMLElement("PaymentMethodFormatValue", pmt.payment_method_format_value), XMLElement("PaymentMode", pmt.payment_mode_code), XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENT_METHODS_B', NULL, pmt.payment_method_code)) ), XMLElement("PayAlongFlag", pmt.exclusive_payment_flag), XMLElement("SettlementPriority", XMLElement("Code", pmt.settlement_priority), XMLElement("Meaning", pmt.settlement_priority_meaning)), XMLElement("AmountWithheld", XMLElement("Value", NVL(pmt.amount_withheld, 0)), XMLElement("Currency", XMLElement("Code", pmt.payment_currency_code))), CASE WHEN pmt.discount_amount_taken IS NOT NULL THEN XMLElement("DiscountTaken", XMLElement("Amount", XMLElement("Value", NVL(pmt.discount_amount_taken, 0)), 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", NVL(pmt.bank_charge_amount, 0)), 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("ServiceLevel", XMLElement("Code", pmt.service_level_code), XMLElement("Meaning", pmt.service_level_meaning), XMLElement("FormatValue", pmt.service_level_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", NVL(pmt.declaration_amount, 0)), 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("LogicalGrouping", XMLElement("PaymentInformationAmountTotal", pmt.total_payment_amount), XMLElement("PaymentInformationTotal", pmt.total_payment_count), XMLElement("LogicalGroupReference", pmt.logical_group_reference)),

CASE WHEN pmt.acknowledgement_date IS NOT NULL THEN XMLElement("AcknowledgementDetails", XMLElement("BepStatus", pmt.bep_status), XMLElement("BepErrorCode", pmt.bep_error_code), XMLElement("BepErrorMessage", pmt.bep_error_message_text), XMLElement("BepTrxnResponseId", pmt.bep_trxn_response_id)) ELSE NULL END,CASE WHEN PMT.ACKNOWLEDGEMENT_DATE IS NOT NULL THEN iby_fd_extract_gen_pvt.get_inv_hold_details(pmt.payment_id) ELSE NULL END, CASE WHEN pmt.payment_mode_code IS NOT NULL THEN XMLElement("BEPResponse",(XMLElement("AcquirerTransactionReference",pmt.acquirer_transaction_reference)),(XMLElement("IssuerAuthorizationReference",pmt.issuer_authorization_reference))) ELSE NULL END,

iby_fd_extract_gen_pvt.get_payment_instrument(pmt.payment_id), iby_fd_extract_gen_pvt.get_card_program_details(pmt.payment_id), iby_fd_extract_gen_pvt.get_digital_payment_acct_det(pmt.payment_id), XMLElement("DocumentPayableCount", IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableCount(pmt.payment_id)),

XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_PAYMENTS_ALL', pmt.payment_id, NULL)), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_DocPayableAgg(pmt.payment_id), IBY_FD_EXTRACT_EXT_PUB.Get_Pmt_Ext_Agg(pmt.payment_id), IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_BR_Ext_Agg(pmt.payment_id),XMLElement("InvoicingLegalEntity", IBY_FD_EXTRACT_GEN_PVT.Get_Inv_LE_Details(pmt.payment_id)),XMLElement("SrcDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'AP_CHECKS_ALL', pmt.payment_id, NULL)),XMLElement("SrcGlobalDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Gdfs( 'AP_CHECKS_ALL', pmt.payment_id, NULL)),IBY_FD_EXTRACT_GEN_PVT.Get_Pmt_AR_Ext_Agg(pmt.legal_entity_id,pmt.payment_org_id,pmt.payment_instruction_id,pmt.payment_date,pmt.payment_id),XMLELEMENT("FederalPMTExtend",iby_fd_extract_gen_pvt.get_pmt_fvfieldsAgg(pmt.payment_instruction_id,pmt.payment_id)))) PAYMENT, 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, pmt.payment_date, pmt.payment_profile_id, pmt.positive_pay_file_created_flag

FROM iby_ext_fd_pmt_1_0_v pmt, fnd_currencies_vl fnd_curr WHERE pmt.payment_currency_code = fnd_curr.currency_code(+)