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_V

Source

View Text - Preformatted


SELECT DECODE(NVL(iby_utility_pvt.get_view_param('FMT_TYPE'), 'x'), 'PAYMENT_PROCESS_REQUEST_REPORT', 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("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("AnticipatedValueDate", pmt.anticipated_value_date),xmlelement("MaturityDate", pmt.maturity_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 NOT IN('REJECTED') THEN NULL ELSE iby_fd_extract_gen_pvt.get_pmt_pmterragg(pmt.payment_id) END, xmlelement("Payee", iby_fd_extract_gen_pvt.get_payee(pmt.payment_id,pmt.employee_payment_flag)), /*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_denorm(pmt.payment_id, pmt.external_bank_account_id,nvl(pmt.employee_payment_flag, 'N'))),IBY_FD_EXTRACT_GEN_PVT.get_PayeeBankAccountAssignment(pmt.payment_id, pmt.external_bank_account_id) ,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", pmt.payment_amount), 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), 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), IBY_EXT_WRAP_PUB.get_pmt_ext_agg(pmt.payment_id)), 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)), CASE WHEN pmt.payment_status NOT IN('REJECTED') THEN NULL ELSE iby_fd_extract_gen_pvt.get_pmt_pmterragg(pmt.payment_id) END, xmlelement("Payee", iby_fd_extract_gen_pvt.get_payee(pmt.payment_id,pmt.employee_payment_flag)), /*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_denorm(pmt.payment_id, pmt.external_bank_account_id,nvl(pmt.employee_payment_flag, 'N'))),IBY_FD_EXTRACT_GEN_PVT.get_PayeeBankAccountAssignment(pmt.payment_id, pmt.external_bank_account_id), 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", pmt.payment_amount), 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), 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, XMLElement("SrcDescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'AP_CHECKS_ALL', pmt.payment_id, NULL)), iby_fd_extract_gen_pvt.get_pmt_docpayableagg(pmt.payment_id), IBY_EXT_WRAP_PUB.get_pmt_ext_agg(pmt.payment_id))) PAYMENT, pmt.payment_instruction_id PAYMENT_INSTRUCTION_ID, pmt.internal_bank_account_id INTERNAL_BANK_ACCOUNT_ID, pmt.legal_entity_id LEGAL_ENTITY_ID, pmt.payment_id PAYMENT_ID, pmt.payment_reference_number PAYMENT_REFERENCE_NUMBER, pmt.payment_status PAYMENT_STATUS, pmt.payment_currency_code PAYMENT_CURRENCY_CODE, pmt.payment_service_request_id PAYMENT_SERVICE_REQUEST_ID, /*LKQ POS PAY ISSUE - PAVAN*/ pmt.positive_pay_file_created_flag POSITIVE_PAY_FILE_CREATED_FLAG, payment_date PAYMENT_DATE, void_date VOID_DATE, logical_group_reference LOGICAL_GROUP_REFERENCE, payment_profile_id PAYMENT_PROFILE_ID, DECODE(pmt.payment_status, 'VOID_BY_OVERFLOW', (SELECT payment_reference_number FROM iby_payments_all WHERE payment_id = (SELECT MAX(payment_id) FROM iby_docs_payable_all WHERE formatting_payment_id = pmt.payment_id ) ), pmt.payment_reference_number) LOGICAL_REFERENCE_NUMBER FROM iby_ext_fd_pmt_1_0_v pmt, fnd_currencies_vl fnd_curr WHERE pmt.payment_currency_code = fnd_curr.currency_code(+)

View Text - HTML Formatted

SELECT DECODE(NVL(IBY_UTILITY_PVT.GET_VIEW_PARAM('FMT_TYPE')
, 'X')
, 'PAYMENT_PROCESS_REQUEST_REPORT'
, 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("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("ANTICIPATEDVALUEDATE"
, PMT.ANTICIPATED_VALUE_DATE)
, XMLELEMENT("MATURITYDATE"
, PMT.MATURITY_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 NOT IN('REJECTED') THEN NULL ELSE IBY_FD_EXTRACT_GEN_PVT.GET_PMT_PMTERRAGG(PMT.PAYMENT_ID) END
, XMLELEMENT("PAYEE"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEE(PMT.PAYMENT_ID
, PMT.EMPLOYEE_PAYMENT_FLAG))
, /*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_DENORM(PMT.PAYMENT_ID
, PMT.EXTERNAL_BANK_ACCOUNT_ID
, NVL(PMT.EMPLOYEE_PAYMENT_FLAG
, 'N')))
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEEBANKACCOUNTASSIGNMENT(PMT.PAYMENT_ID
, PMT.EXTERNAL_BANK_ACCOUNT_ID)
, 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"
, PMT.PAYMENT_AMOUNT)
, 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)
, 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)
, IBY_EXT_WRAP_PUB.GET_PMT_EXT_AGG(PMT.PAYMENT_ID))
, 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))
, CASE WHEN PMT.PAYMENT_STATUS NOT IN('REJECTED') THEN NULL ELSE IBY_FD_EXTRACT_GEN_PVT.GET_PMT_PMTERRAGG(PMT.PAYMENT_ID) END
, XMLELEMENT("PAYEE"
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEE(PMT.PAYMENT_ID
, PMT.EMPLOYEE_PAYMENT_FLAG))
, /*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_DENORM(PMT.PAYMENT_ID
, PMT.EXTERNAL_BANK_ACCOUNT_ID
, NVL(PMT.EMPLOYEE_PAYMENT_FLAG
, 'N')))
, IBY_FD_EXTRACT_GEN_PVT.GET_PAYEEBANKACCOUNTASSIGNMENT(PMT.PAYMENT_ID
, PMT.EXTERNAL_BANK_ACCOUNT_ID)
, 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"
, PMT.PAYMENT_AMOUNT)
, 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)
, 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
, XMLELEMENT("SRCDESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'AP_CHECKS_ALL'
, PMT.PAYMENT_ID
, NULL))
, IBY_FD_EXTRACT_GEN_PVT.GET_PMT_DOCPAYABLEAGG(PMT.PAYMENT_ID)
, IBY_EXT_WRAP_PUB.GET_PMT_EXT_AGG(PMT.PAYMENT_ID))) PAYMENT
, PMT.PAYMENT_INSTRUCTION_ID PAYMENT_INSTRUCTION_ID
, PMT.INTERNAL_BANK_ACCOUNT_ID INTERNAL_BANK_ACCOUNT_ID
, PMT.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, PMT.PAYMENT_ID PAYMENT_ID
, PMT.PAYMENT_REFERENCE_NUMBER PAYMENT_REFERENCE_NUMBER
, PMT.PAYMENT_STATUS PAYMENT_STATUS
, PMT.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, PMT.PAYMENT_SERVICE_REQUEST_ID PAYMENT_SERVICE_REQUEST_ID
, /*LKQ POS PAY ISSUE - PAVAN*/ PMT.POSITIVE_PAY_FILE_CREATED_FLAG POSITIVE_PAY_FILE_CREATED_FLAG
, PAYMENT_DATE PAYMENT_DATE
, VOID_DATE VOID_DATE
, LOGICAL_GROUP_REFERENCE LOGICAL_GROUP_REFERENCE
, PAYMENT_PROFILE_ID PAYMENT_PROFILE_ID
, DECODE(PMT.PAYMENT_STATUS
, 'VOID_BY_OVERFLOW'
, (SELECT PAYMENT_REFERENCE_NUMBER
FROM IBY_PAYMENTS_ALL
WHERE PAYMENT_ID = (SELECT MAX(PAYMENT_ID)
FROM IBY_DOCS_PAYABLE_ALL
WHERE FORMATTING_PAYMENT_ID = PMT.PAYMENT_ID ) )
, PMT.PAYMENT_REFERENCE_NUMBER) LOGICAL_REFERENCE_NUMBER
FROM IBY_EXT_FD_PMT_1_0_V PMT
, FND_CURRENCIES_VL FND_CURR
WHERE PMT.PAYMENT_CURRENCY_CODE = FND_CURR.CURRENCY_CODE(+)