This query can give details of payment run.
SELECT XMLElement
( "APInvSelectionCriteraAll"
, XMLAgg
( XMLElement
( "APInvSelectionCrieria"
, XMLAttribute$
( Checkrun_Id as "CheckrunID"
)
, XMLElement
( "APInvSelectionCriteriaDetails"
, XMLforest
( ANTICIPATED_VALUE_DATE as "AnticipatedValueDate"
, ATTRIBUTE1 as "Attribute1"
, ATTRIBUTE10 as "Attribute10"
, ATTRIBUTE11 as "Attribute11"
, ATTRIBUTE12 as "Attribute12"
, ATTRIBUTE13 as "Attribute13"
, ATTRIBUTE14 as "Attribute14"
, ATTRIBUTE15 as "Attribute15"
, ATTRIBUTE2 as "Attribute2"
, ATTRIBUTE3 as "Attribute3"
, ATTRIBUTE4 as "Attribute4"
, ATTRIBUTE5 as "Attribute5"
, ATTRIBUTE6 as "Attribute6"
, ATTRIBUTE7 as "Attribute7"
, ATTRIBUTE8 as "Attribute8"
, ATTRIBUTE9 as "Attribute9"
, ATTRIBUTE_CATEGORY as "AttributeCategory"
, AUDIT_REQUIRED_FLAG as "AuditRequiredFlag"
, BANK_ACCOUNT_ID as "BankAccountId"
, BANK_ACCOUNT_NAME as "BankAccountName"
, BANK_CHARGE_BEARER as "BankChargeBearer"
, BATCH_IDENTIFIER as "BatchIdentifier"
, BATCH_RUN_NAME as "BatchRunName"
, BATCH_SET_ID as "BatchSetId"
, BATCH_SET_LINE_ID as "BatchSetLineId"
, CALC_AWT_INT_FLAG as "CalcAwtIntFlag"
, CE_BANK_ACCT_USE_ID as "CeBankAcctUseId"
, CHECKRUN_ID as "CheckrunId"
, CHECKRUN_NAME as "CheckrunName"
, CHECK_DATE as "CheckDate"
, CHECK_STOCK_ID as "CheckStockId"
, CREATED_BY as "CreatedBy"
, CREATE_INSTRS_FLAG as "CreateInstrsFlag"
, CREATION_DATE as "CreationDate"
, CURRENCY_CODE as "CurrencyCode"
, CURRENCY_GROUP_OPTION as "CurrencyGroupOption"
, DOCUMENT_ORDER_LOOKUP_CODE as "DocumentOrderLookupCode"
, DOCUMENT_REJECTION_LEVEL_CODE as "DocumentRejectionLevelCode"
, END_PRINT_DOCUMENT as "EndPrintDocument"
, EXCHANGE_DATE as "ExchangeDate"
, EXCHANGE_RATE as "ExchangeRate"
, EXCHANGE_RATE_TYPE as "ExchangeRateType"
, FIRST_AVAILABLE_DOCUMENT as "FirstAvailableDocument"
, FIRST_VOUCHER_NUMBER as "FirstVoucherNumber"
, FUTURE_DATED_PAYMENT_FLAG as "FutureDatedPaymentFlag"
, HI_PAYMENT_PRIORITY as "HiPaymentPriority"
, INTERVAL as "Interval"
, INVOICE_BATCH_ID as "InvoiceBatchId"
, INV_AWT_EXISTS_FLAG as "InvAwtExistsFlag"
, INV_EXCHANGE_RATE_TYPE as "InvExchangeRateType"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, LE_GROUP_OPTION as "LeGroupOption"
, LOW_PAYMENT_PRIORITY as "LowPaymentPriority"
, MAX_OUTLAY as "MaxOutlay"
, MAX_PAYMENT_AMOUNT as "MaxPaymentAmount"
, MIN_CHECK_AMOUNT as "MinCheckAmount"
, NEXT_VOUCHER_NUMBER as "NextVoucherNumber"
, ORG_ID as "OrgId"
, OU_GROUP_OPTION as "OuGroupOption"
, PARTY_ID as "PartyId"
, PAYABLES_REVIEW_SETTINGS as "PayablesReviewSettings"
, PAYMENTS_REVIEW_SETTINGS as "PaymentsReviewSettings"
, PAYMENT_DOCUMENT_ID as "PaymentDocumentId"
, PAYMENT_METHOD_CODE as "PaymentMethodCode"
, PAYMENT_METHOD_LOOKUP_CODE as "PaymentMethodLookupCode"
, PAYMENT_PROFILE_ID as "PaymentProfileId"
, PAYMENT_REJECTION_LEVEL_CODE as "PaymentRejectionLevelCode"
, PAY_FROM_DATE as "PayFromDate"
, PAY_GROUP_OPTION as "PayGroupOption"
, PAY_ONLY_WHEN_DUE_FLAG as "PayOnlyWhenDueFlag"
, PAY_THRU_DATE as "PayThruDate"
, PERIOD_NAME as "PeriodName"
, PROGRAM_APPLICATION_ID as "ProgramApplicationId"
, PROGRAM_ID as "ProgramId"
, PROGRAM_UPDATE_DATE as "ProgramUpdateDate"
, REQUEST_ID as "RequestId"
, RESUBMIT_FLAG as "ResubmitFlag"
, SETTLEMENT_PRIORITY as "SettlementPriority"
, START_PRINT_DOCUMENT as "StartPrintDocument"
, STATUS as "Status"
, TEMPLATE_FLAG as "TemplateFlag"
, TEMPLATE_ID as "TemplateId"
, TRANSFER_PRIORITY as "TransferPriority"
, USSGL_TRANSACTION_CODE as "UssglTransactionCode"
, USSGL_TRX_CODE_CONTEXT as "UssglTrxCodeContext"
, VENDOR_ID as "VendorId"
, VENDOR_PAY_GROUP as "VendorPayGroup"
, VENDOR_TYPE_LOOKUP_CODE as "VendorTypeLookupCode"
, VOLUME_SERIAL_NUMBER as "VolumeSerialNumber"
, ZERO_AMOUNTS_ALLOWED as "ZeroAmountsAllowed"
, ZERO_INVOICES_ALLOWED as "ZeroInvoicesAllowed"
)
)
, ( SELECT XMLElement
( "APLEGroupList"
, XMLAgg
( XMLElement
( "APLEGroup"
, XMLAttribute$
( le_group_id as "ID"
)
, XMLElement
( "APLEGroupDetails"
, XMLforest
(
CHECKRUN_ID as "CheckrunId"
, CREATED_BY as "CreatedBy"
, CREATION_DATE as "CreationDate"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, LEGAL_ENTITY_ID as "LegalEntityId"
, LE_GROUP_ID as "LeGroupId"
, TEMPLATE_ID as "TemplateId"
)
)
)
)
)
from ap_le_group alg
where alg.checkrun_id = isc.checkrun_id
)
, ( SELECT XMLElement
( "APOUGroups"
, XMLAgg
( XMLElement
( "APOUGroup"
, XMLAttribute$
( ou_group_id as "OUGroupID"
)
, XMLElement
( "APOUGroupDetails"
, XMLforest
( CHECKRUN_ID as "CheckrunId"
, CREATED_BY as "CreatedBy"
, CREATION_DATE as "CreationDate"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, ORG_ID as "OrgId"
, OU_GROUP_ID as "OuGroupId"
, TEMPLATE_ID as "TemplateId"
)
)
)
)
)
from ap_ou_group aog
where aog.checkrun_id = isc.checkrun_id
)
, ( SELECT XMLElement
( "APCurrencyGroups"
, XMLAgg
( XMLElement
( "APCurrencyGroup"
, XMLAttribute$
( currency_group_id as "CurrencyGroupId"
)
, XMLElement
( "APCurrencyGroupDetails"
, XMLforest
( CHECKRUN_ID as "CheckrunId"
, CREATED_BY as "CreatedBy"
, CREATION_DATE as "CreationDate"
, CURRENCY_CODE as "CurrencyCode"
, CURRENCY_GROUP_ID as "CurrencyGroupId"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, TEMPLATE_ID as "TemplateId"
)
)
)
)
)
from ap_currency_group acg
where acg.checkrun_id = isc.checkrun_id
)
, ( SELECT XMLElement
( "APPAyGroups"
, XMLAgg
( XMLElement
( "APPayGroup"
, XMLAttribute$
( Vendor_Pay_Group as "VendorPayGroup"
)
, XMLElement
( "VendorPayGroupDetails"
, XMLforest
( CHECKRUN_ID as "CheckrunId"
, CREATED_BY as "CreatedBy"
, CREATION_DATE as "CreationDate"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, PAY_GROUP_ID as "PayGroupId"
, TEMPLATE_ID as "TemplateId"
, VENDOR_PAY_GROUP as "VendorPayGroup"
)
)
)
)
)
from ap_pay_group apg
where apg.checkrun_id = isc.checkrun_id
)
, ( SELECT XMLElement
( "IbyPaymentServiceRequests"
, XMLAgg
( XMLElement
( "IbyPaymentServiceRequest"
, XMLAttribute$
( payment_service_request_id as "ID"
)
, XMLElement
( "IbyPaymentServiceRequestDetails"
, XMLforest
( ALLOW_ZERO_PAYMENTS_FLAG as "AllowZeroPaymentsFlag"
, ATTRIBUTE1 as "Attribute1"
, ATTRIBUTE10 as "Attribute10"
, ATTRIBUTE11 as "Attribute11"
, ATTRIBUTE12 as "Attribute12"
, ATTRIBUTE13 as "Attribute13"
, ATTRIBUTE14 as "Attribute14"
, ATTRIBUTE15 as "Attribute15"
, ATTRIBUTE2 as "Attribute2"
, ATTRIBUTE3 as "Attribute3"
, ATTRIBUTE4 as "Attribute4"
, ATTRIBUTE5 as "Attribute5"
, ATTRIBUTE6 as "Attribute6"
, ATTRIBUTE7 as "Attribute7"
, ATTRIBUTE8 as "Attribute8"
, ATTRIBUTE9 as "Attribute9"
, ATTRIBUTE_CATEGORY as "AttributeCategory"
, CALLING_APP_ID as "CallingAppId"
, CALL_APP_PAY_SERVICE_REQ_CODE as "CallAppPayServiceReqCode"
, CREATED_BY as "CreatedBy"
, CREATE_PMT_INSTRUCTIONS_FLAG as "CreatePmtInstructionsFlag"
, CREATION_DATE as "CreationDate"
, DOCUMENT_REJECTION_LEVEL_CODE as "DocumentRejectionLevelCode"
, INTERNAL_BANK_ACCOUNT_ID as "InternalBankAccountId"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, MAXIMUM_PAYMENT_AMOUNT as "MaximumPaymentAmount"
, MINIMUM_PAYMENT_AMOUNT as "MinimumPaymentAmount"
, OBJECT_VERSION_NUMBER as "ObjectVersionNumber"
, ORG_TYPE as "OrgType"
, PAYMENT_DOCUMENT_ID as "PaymentDocumentId"
, PAYMENT_PROFILE_ID as "PaymentProfileId"
, PAYMENT_REJECTION_LEVEL_CODE as "PaymentRejectionLevelCode"
, PAYMENT_SERVICE_REQUEST_ID as "PaymentServiceRequestId"
, PAYMENT_SERVICE_REQUEST_STATUS as "PaymentServiceRequestStatus"
, PROCESS_TYPE as "ProcessType"
, REQUEST_ID as "RequestId"
, REQUIRE_PROP_PMTS_REVIEW_FLAG as "RequirePropPmtsReviewFlag"
)
)
, ( SELECT XMLElement
( "IbyPaymentsAllList"
, XMLAgg
( XMLElement
( "IbyPaymentsAll"
, XMLAttribute$
( payment_id as "PaymentId"
)
, XMLElement
( "IbyPaymentsDetailsDetails"
, XMLforest
( ADDRESS_SOURCE as "AddressSource"
, ANTICIPATED_VALUE_DATE as "AnticipatedValueDate"
, ANTICIPATED_VALUE_DATE as "AnticipatedValueDate"
, ATTRIBUTE1 as "Attribute1"
, ATTRIBUTE10 as "Attribute10"
, ATTRIBUTE11 as "Attribute11"
, ATTRIBUTE12 as "Attribute12"
, ATTRIBUTE13 as "Attribute13"
, ATTRIBUTE14 as "Attribute14"
, ATTRIBUTE15 as "Attribute15"
, ATTRIBUTE2 as "Attribute2"
, ATTRIBUTE3 as "Attribute3"
, ATTRIBUTE4 as "Attribute4"
, ATTRIBUTE5 as "Attribute5"
, ATTRIBUTE6 as "Attribute6"
, ATTRIBUTE7 as "Attribute7"
, ATTRIBUTE8 as "Attribute8"
, ATTRIBUTE9 as "Attribute9"
, ATTRIBUTE_CATEGORY as "AttributeCategory"
, BANK_ASSIGNED_REF_CODE as "BankAssignedRefCode"
, BANK_CHARGE_AMOUNT as "BankChargeAmount"
, BANK_CHARGE_BEARER as "BankChargeBearer"
, BANK_INSTRUCTION1_CODE as "BankInstruction1Code"
, BANK_INSTRUCTION1_FORMAT_VALUE as "BankInstruction1FormatValue"
, BANK_INSTRUCTION2_CODE as "BankInstruction2Code"
, BANK_INSTRUCTION2_FORMAT_VALUE as "BankInstruction2FormatValue"
, BANK_INSTRUCTION_DETAILS as "BankInstructionDetails"
, BENEFICIARY_NAME as "BeneficiaryName"
, BENEFICIARY_PARTY as "BeneficiaryParty"
, BILL_PAYABLE_FLAG as "BillPayableFlag"
, COMPLETED_PMTS_GROUP_ID as "CompletedPmtsGroupId"
, CREATED_BY as "CreatedBy"
, CREATION_DATE as "CreationDate"
, DECLARATION_AMOUNT as "DeclarationAmount"
, DECLARATION_CURRENCY_CODE as "DeclarationCurrencyCode"
, DECLARATION_EXCH_RATE_TYPE as "DeclarationExchRateType"
, DECLARATION_FORMAT as "DeclarationFormat"
, DECLARE_PAYMENT_FLAG as "DeclarePaymentFlag"
, DELIVERY_CHANNEL_CODE as "DeliveryChannelCode"
, DELIVERY_CHANNEL_FORMAT_VALUE as "DeliveryChannelFormatValue"
, DISCOUNT_AMOUNT_TAKEN as "DiscountAmountTaken"
, DOCUMENT_CATEGORY_CODE as "DocumentCategoryCode"
, DOCUMENT_SEQUENCE_ID as "DocumentSequenceId"
, DOCUMENT_SEQUENCE_VALUE as "DocumentSequenceValue"
, EMPLOYEE_ADDRESS_CODE as "EmployeeAddressCode"
, EMPLOYEE_ADDRESS_ID as "EmployeeAddressId"
, EMPLOYEE_PAYMENT_FLAG as "EmployeePaymentFlag"
, EMPLOYEE_PERSON_ID as "EmployeePersonId"
, EXCLUSIVE_PAYMENT_FLAG as "ExclusivePaymentFlag"
, EXTERNAL_BANK_ACCOUNT_ID as "ExternalBankAccountId"
, EXT_BANK_ACCOUNT_ALT_NAME as "ExtBankAccountAltName"
, EXT_BANK_ACCOUNT_IBAN_NUMBER as "ExtBankAccountIbanNumber"
, EXT_BANK_ACCOUNT_NAME as "ExtBankAccountName"
, EXT_BANK_ACCOUNT_NUMBER as "ExtBankAccountNumber"
, EXT_BANK_ACCOUNT_NUM_ELEC as "ExtBankAccountNumElec"
, EXT_BANK_ACCOUNT_TYPE as "ExtBankAccountType"
, EXT_BANK_ACCT_OWNER_PARTY_ID as "ExtBankAcctOwnerPartyId"
, EXT_BANK_ACCT_OWNER_PARTY_NAME as "ExtBankAcctOwnerPartyName"
, EXT_BANK_ACCT_PMT_FACTOR_FLAG as "ExtBankAcctPmtFactorFlag"
, EXT_BANK_ALT_NAME as "ExtBankAltName"
, EXT_BANK_BRANCH_ALT_NAME as "ExtBankBranchAltName"
, EXT_BANK_BRANCH_LOCATION_ID as "ExtBankBranchLocationId"
, EXT_BANK_BRANCH_NAME as "ExtBankBranchName"
, EXT_BANK_BRANCH_PARTY_ID as "ExtBankBranchPartyId"
, EXT_BANK_NAME as "ExtBankName"
, EXT_BANK_NUMBER as "ExtBankNumber"
, EXT_BNK_ACCT_OWNR_INV_PRTY_ID as "ExtBnkAcctOwnrInvPrtyId"
, EXT_BNK_ACCT_OWNR_INV_PRTY_NME as "ExtBnkAcctOwnrInvPrtyNme"
, EXT_BNK_BRANCH_INV_PRTY_ID as "ExtBnkBranchInvPrtyId"
, EXT_BRANCH_NUMBER as "ExtBranchNumber"
, EXT_EFT_SWIFT_CODE as "ExtEftSwiftCode"
, EXT_INV_PAYEE_ID as "ExtInvPayeeId"
, EXT_PAYEE_ID as "ExtPayeeId"
, INTERNAL_BANK_ACCOUNT_ID as "InternalBankAccountId"
, INT_BANK_ACCOUNT_ALT_NAME as "IntBankAccountAltName"
, INT_BANK_ACCOUNT_IBAN as "IntBankAccountIban"
, INT_BANK_ACCOUNT_NAME as "IntBankAccountName"
, INT_BANK_ACCOUNT_NUMBER as "IntBankAccountNumber"
, INT_BANK_ACCOUNT_NUM_ELEC as "IntBankAccountNumElec"
, INT_BANK_ACCT_AGENCY_LOC_CODE as "IntBankAcctAgencyLocCode"
, INT_BANK_ALT_NAME as "IntBankAltName"
, INT_BANK_BRANCH_ALT_NAME as "IntBankBranchAltName"
, INT_BANK_BRANCH_EFT_USER_NUM as "IntBankBranchEftUserNum"
, INT_BANK_BRANCH_LOCATION_ID as "IntBankBranchLocationId"
, INT_BANK_BRANCH_NAME as "IntBankBranchName"
, INT_BANK_BRANCH_NUMBER as "IntBankBranchNumber"
, INT_BANK_BRANCH_PARTY_ID as "IntBankBranchPartyId"
, INT_BANK_BRANCH_RFC_IDENTIFIER as "IntBankBranchRfcIdentifier"
, INT_BANK_NAME as "IntBankName"
, INT_BANK_NUMBER as "IntBankNumber"
, INT_EFT_SWIFT_CODE as "IntEftSwiftCode"
, INV_BENEFICIARY_NAME as "InvBeneficiaryName"
, INV_BENEFICIARY_PARTY as "InvBeneficiaryParty"
, INV_PARTY_SITE_ID as "InvPartySiteId"
, INV_PAYEE_ADDRESS1 as "InvPayeeAddress1"
, INV_PAYEE_ADDRESS2 as "InvPayeeAddress2"
, INV_PAYEE_ADDRESS3 as "InvPayeeAddress3"
, INV_PAYEE_ADDRESS4 as "InvPayeeAddress4"
, INV_PAYEE_ADDRESS_CONCAT as "InvPayeeAddressConcat"
, INV_PAYEE_ALTERNATE_NAME as "InvPayeeAlternateName"
, INV_PAYEE_CITY as "InvPayeeCity"
, INV_PAYEE_COUNTRY as "InvPayeeCountry"
, INV_PAYEE_COUNTY as "InvPayeeCounty"
, INV_PAYEE_FIRST_PARTY_REF as "InvPayeeFirstPartyRef"
, INV_PAYEE_LE_REG_NUM as "InvPayeeLeRegNum"
, INV_PAYEE_NAME as "InvPayeeName"
, INV_PAYEE_PARTY_ATTR_CAT as "InvPayeePartyAttrCat"
, INV_PAYEE_PARTY_ID as "InvPayeePartyId"
, INV_PAYEE_PARTY_NAME as "InvPayeePartyName"
, INV_PAYEE_PARTY_NUMBER as "InvPayeePartyNumber"
, INV_PAYEE_POSTAL_CODE as "InvPayeePostalCode"
, INV_PAYEE_PROVINCE as "InvPayeeProvince"
, INV_PAYEE_SITE_ALT_NAME as "InvPayeeSiteAltName"
, INV_PAYEE_SPPLR_SITE_ALT_NAME as "InvPayeeSpplrSiteAltName"
, INV_PAYEE_SPPLR_SITE_ATTR_CAT as "InvPayeeSpplrSiteAttrCat"
, INV_PAYEE_STATE as "InvPayeeState"
, INV_PAYEE_SUPPLIER_ATTR_CAT as "InvPayeeSupplierAttrCat"
, INV_PAYEE_SUPPLIER_ID as "InvPayeeSupplierId"
, INV_PAYEE_SUPPLIER_NUMBER as "InvPayeeSupplierNumber"
, INV_PAYEE_SUPPLIER_SITE_NAME as "InvPayeeSupplierSiteName"
, INV_PAYEE_TAX_REG_NUM as "InvPayeeTaxRegNum"
, INV_SUPPLIER_SITE_ID as "InvSupplierSiteId"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, LEGAL_ENTITY_ID as "LegalEntityId"
, LOGICAL_GROUP_REFERENCE as "LogicalGroupReference"
, MATURITY_DATE as "MaturityDate"
, OBJECT_VERSION_NUMBER as "ObjectVersionNumber"
, ORG_ID as "OrgId"
, ORG_NAME as "OrgName"
, ORG_TYPE as "OrgType"
, PAPER_DOCUMENT_NUMBER as "PaperDocumentNumber"
, PARTY_SITE_ID as "PartySiteId"
, PAYEE_ADDRESS1 as "PayeeAddress1"
, PAYEE_ADDRESS2 as "PayeeAddress2"
, PAYEE_ADDRESS3 as "PayeeAddress3"
, PAYEE_ADDRESS4 as "PayeeAddress4"
, PAYEE_ADDRESSEE as "PayeeAddressee"
, PAYEE_ADDRESS_CONCAT as "PayeeAddressConcat"
, PAYEE_ALTERNATE_NAME as "PayeeAlternateName"
, PAYEE_CITY as "PayeeCity"
, PAYEE_COUNTRY as "PayeeCountry"
, PAYEE_COUNTY as "PayeeCounty"
, PAYEE_FIRST_PARTY_REFERENCE as "PayeeFirstPartyReference"
, PAYEE_LE_REGISTRATION_NUM as "PayeeLeRegistrationNum"
, PAYEE_NAME as "PayeeName"
, PAYEE_PARTY_ATTR_CATEGORY as "PayeePartyAttrCategory"
, PAYEE_PARTY_ID as "PayeePartyId"
, PAYEE_PARTY_NAME as "PayeePartyName"
, PAYEE_PARTY_NUMBER as "PayeePartyNumber"
, PAYEE_POSTAL_CODE as "PayeePostalCode"
, PAYEE_PROVINCE as "PayeeProvince"
, PAYEE_SITE_ALTERNATE_NAME as "PayeeSiteAlternateName"
, PAYEE_SPPLR_SITE_ATTR_CATEGORY as "PayeeSpplrSiteAttrCategory"
, PAYEE_STATE as "PayeeState"
, PAYEE_SUPPLIER_ATTR_CATEGORY as "PayeeSupplierAttrCategory"
, PAYEE_SUPPLIER_ID as "PayeeSupplierId"
, PAYEE_SUPPLIER_NUMBER as "PayeeSupplierNumber"
, PAYEE_SUPPLIER_SITE_ALT_NAME as "PayeeSupplierSiteAltName"
, PAYEE_SUPPLIER_SITE_NAME as "PayeeSupplierSiteName"
, PAYEE_TAX_REGISTRATION_NUM as "PayeeTaxRegistrationNum"
, PAYER_ABBREVIATED_AGENCY_CODE as "PayerAbbreviatedAgencyCode"
, PAYER_FEDERAL_US_EMPLOYER_ID as "PayerFederalUsEmployerId"
, PAYER_LEGAL_ENTITY_NAME as "PayerLegalEntityName"
, PAYER_LE_ATTR_CATEGORY as "PayerLeAttrCategory"
, PAYER_LE_REGISTRATION_NUM as "PayerLeRegistrationNum"
, PAYER_LOCATION_ID as "PayerLocationId"
, PAYER_PARTY_ATTR_CATEGORY as "PayerPartyAttrCategory"
, PAYER_PARTY_ID as "PayerPartyId"
, PAYER_PARTY_NUMBER as "PayerPartyNumber"
, PAYER_PARTY_SITE_NAME as "PayerPartySiteName"
, PAYER_TAX_REGISTRATION_NUM as "PayerTaxRegistrationNum"
, PAYMENTS_COMPLETE_FLAG as "PaymentsCompleteFlag"
, PAYMENT_AMOUNT as "PaymentAmount"
, PAYMENT_CURRENCY_CODE as "PaymentCurrencyCode"
, PAYMENT_DATE as "PaymentDate"
, PAYMENT_DETAILS as "PaymentDetails"
, PAYMENT_DUE_DATE as "PaymentDueDate"
, PAYMENT_FUNCTION as "PaymentFunction"
, PAYMENT_ID as "PaymentId"
, PAYMENT_INSTRUCTION_ID as "PaymentInstructionId"
, PAYMENT_METHOD_CODE as "PaymentMethodCode"
, PAYMENT_PROCESS_REQUEST_NAME as "PaymentProcessRequestName"
, PAYMENT_PROFILE_ACCT_NAME as "PaymentProfileAcctName"
, PAYMENT_PROFILE_ID as "PaymentProfileId"
, PAYMENT_PROFILE_SYS_NAME as "PaymentProfileSysName"
, PAYMENT_REASON_CODE as "PaymentReasonCode"
, PAYMENT_REASON_COMMENTS as "PaymentReasonComments"
, PAYMENT_REASON_FORMAT_VALUE as "PaymentReasonFormatValue"
, PAYMENT_REFERENCE_NUMBER as "PaymentReferenceNumber"
, PAYMENT_SERVICE_REQUEST_ID as "PaymentServiceRequestId"
, PAYMENT_STATUS as "PaymentStatus"
, PAYMENT_TEXT_MESSAGE1 as "PaymentTextMessage1"
, PAYMENT_TEXT_MESSAGE2 as "PaymentTextMessage2"
, PAYMENT_TEXT_MESSAGE3 as "PaymentTextMessage3"
, POSITIVE_PAY_FILE_CREATED_FLAG as "PositivePayFileCreatedFlag"
, PREGROUPED_PAYMENT_FLAG as "PregroupedPaymentFlag"
, PROCESS_TYPE as "ProcessType"
, REMITTANCE_MESSAGE1 as "RemittanceMessage1"
, REMITTANCE_MESSAGE2 as "RemittanceMessage2"
, REMITTANCE_MESSAGE3 as "RemittanceMessage3"
, REMIT_ADVICE_DELIVERY_METHOD as "RemitAdviceDeliveryMethod"
, REMIT_ADVICE_EMAIL as "RemitAdviceEmail"
, REMIT_ADVICE_FAX as "RemitAdviceFax"
, REMIT_TO_LOCATION_ID as "RemitToLocationId"
, REQUEST_ID as "RequestId"
, SEPARATE_REMIT_ADVICE_REQ_FLAG as "SeparateRemitAdviceReqFlag"
, SETTLEMENT_PRIORITY as "SettlementPriority"
, SOURCE_PRODUCT as "SourceProduct"
, SRA_DELIVERY_METHOD as "SraDeliveryMethod"
, STOP_CONFIRMED_BY as "StopConfirmedBy"
, STOP_CONFIRMED_FLAG as "StopConfirmedFlag"
, STOP_CONFIRM_DATE as "StopConfirmDate"
, STOP_CONFIRM_REASON as "StopConfirmReason"
, STOP_CONFIRM_REFERENCE as "StopConfirmReference"
, STOP_RELEASED_BY as "StopReleasedBy"
, STOP_RELEASED_FLAG as "StopReleasedFlag"
, STOP_RELEASE_DATE as "StopReleaseDate"
, STOP_RELEASE_REASON as "StopReleaseReason"
, STOP_RELEASE_REFERENCE as "StopReleaseReference"
, STOP_REQUEST_DATE as "StopRequestDate"
, STOP_REQUEST_PLACED_BY as "StopRequestPlacedBy"
, STOP_REQUEST_PLACED_FLAG as "StopRequestPlacedFlag"
, STOP_REQUEST_REASON as "StopRequestReason"
, STOP_REQUEST_REFERENCE as "StopRequestReference"
, SUPPLIER_SITE_ID as "SupplierSiteId"
, UNIQUE_REMITTANCE_IDENTIFIER as "UniqueRemittanceIdentifier"
, URI_CHECK_DIGIT as "UriCheckDigit"
, VOIDED_BY as "VoidedBy"
, VOID_DATE as "VoidDate"
, VOID_REASON as "VoidReason"
)
)
, ( SELECT XMLElement
( "IbyDocsPayableAllList"
, XMLAgg
( XMLElement
( "IbyDocsPayable"
, XMLAttribute$
( document_payable_id as "DocumentPayableID"
)
, XMLElement
( "IbyDocsPayableDetails"
, XMLforest
( ADDRESS_SOURCE as "AddressSource"
, ALLOW_REMOVING_DOCUMENT_FLAG as "AllowRemovingDocumentFlag"
, AMOUNT_WITHHELD as "AmountWithheld"
, ANTICIPATED_VALUE_DATE as "AnticipatedValueDate"
, ATTRIBUTE1 as "Attribute1"
, ATTRIBUTE10 as "Attribute10"
, ATTRIBUTE11 as "Attribute11"
, ATTRIBUTE12 as "Attribute12"
, ATTRIBUTE13 as "Attribute13"
, ATTRIBUTE14 as "Attribute14"
, ATTRIBUTE15 as "Attribute15"
, ATTRIBUTE2 as "Attribute2"
, ATTRIBUTE3 as "Attribute3"
, ATTRIBUTE4 as "Attribute4"
, ATTRIBUTE5 as "Attribute5"
, ATTRIBUTE6 as "Attribute6"
, ATTRIBUTE7 as "Attribute7"
, ATTRIBUTE8 as "Attribute8"
, ATTRIBUTE9 as "Attribute9"
, ATTRIBUTE_CATEGORY as "AttributeCategory"
, BANK_ASSIGNED_REF_CODE as "BankAssignedRefCode"
, BANK_CHARGE_BEARER as "BankChargeBearer"
, BENEFICIARY_PARTY as "BeneficiaryParty"
, CALLING_APP_DOC_REF_NUMBER as "CallingAppDocRefNumber"
, CALLING_APP_DOC_UNIQUE_REF1 as "CallingAppDocUniqueRef1"
, CALLING_APP_DOC_UNIQUE_REF2 as "CallingAppDocUniqueRef2"
, CALLING_APP_DOC_UNIQUE_REF3 as "CallingAppDocUniqueRef3"
, CALLING_APP_DOC_UNIQUE_REF4 as "CallingAppDocUniqueRef4"
, CALLING_APP_DOC_UNIQUE_REF5 as "CallingAppDocUniqueRef5"
, CALLING_APP_ID as "CallingAppId"
, COMPLETED_PMTS_GROUP_ID as "CompletedPmtsGroupId"
, CREATED_BY as "CreatedBy"
, CREATION_DATE as "CreationDate"
, DELIVERY_CHANNEL_CODE as "DeliveryChannelCode"
, DISCOUNT_DATE as "DiscountDate"
, DOCUMENT_AMOUNT as "DocumentAmount"
, DOCUMENT_CATEGORY_CODE as "DocumentCategoryCode"
, DOCUMENT_CURRENCY_CODE as "DocumentCurrencyCode"
, DOCUMENT_CURRENCY_TAX_AMOUNT as "DocumentCurrencyTaxAmount"
, DOCUMENT_CURR_CHARGE_AMOUNT as "DocumentCurrChargeAmount"
, DOCUMENT_DATE as "DocumentDate"
, DOCUMENT_DESCRIPTION as "DocumentDescription"
, DOCUMENT_PAYABLE_ID as "DocumentPayableId"
, DOCUMENT_SEQUENCE_ID as "DocumentSequenceId"
, DOCUMENT_SEQUENCE_VALUE as "DocumentSequenceValue"
, DOCUMENT_STATUS as "DocumentStatus"
, DOCUMENT_TYPE as "DocumentType"
, EMPLOYEE_ADDRESS_CODE as "EmployeeAddressCode"
, EMPLOYEE_ADDRESS_ID as "EmployeeAddressId"
, EMPLOYEE_PAYMENT_FLAG as "EmployeePaymentFlag"
, EMPLOYEE_PERSON_ID as "EmployeePersonId"
, EXCLUSIVE_PAYMENT_FLAG as "ExclusivePaymentFlag"
, EXTERNAL_BANK_ACCOUNT_ID as "ExternalBankAccountId"
, EXT_INV_PAYEE_ID as "ExtInvPayeeId"
, EXT_PAYEE_ID as "ExtPayeeId"
, FORMATTING_PAYMENT_ID as "FormattingPaymentId"
, GLOBAL_ATTRIBUTE1 as "GlobalAttribute1"
, GLOBAL_ATTRIBUTE10 as "GlobalAttribute10"
, GLOBAL_ATTRIBUTE11 as "GlobalAttribute11"
, GLOBAL_ATTRIBUTE12 as "GlobalAttribute12"
, GLOBAL_ATTRIBUTE13 as "GlobalAttribute13"
, GLOBAL_ATTRIBUTE14 as "GlobalAttribute14"
, GLOBAL_ATTRIBUTE15 as "GlobalAttribute15"
, GLOBAL_ATTRIBUTE16 as "GlobalAttribute16"
, GLOBAL_ATTRIBUTE17 as "GlobalAttribute17"
, GLOBAL_ATTRIBUTE18 as "GlobalAttribute18"
, GLOBAL_ATTRIBUTE19 as "GlobalAttribute19"
, GLOBAL_ATTRIBUTE2 as "GlobalAttribute2"
, GLOBAL_ATTRIBUTE20 as "GlobalAttribute20"
, GLOBAL_ATTRIBUTE3 as "GlobalAttribute3"
, GLOBAL_ATTRIBUTE4 as "GlobalAttribute4"
, GLOBAL_ATTRIBUTE5 as "GlobalAttribute5"
, GLOBAL_ATTRIBUTE6 as "GlobalAttribute6"
, GLOBAL_ATTRIBUTE7 as "GlobalAttribute7"
, GLOBAL_ATTRIBUTE8 as "GlobalAttribute8"
, GLOBAL_ATTRIBUTE9 as "GlobalAttribute9"
, GLOBAL_ATTRIBUTE_CATEGORY as "GlobalAttributeCategory"
, INTEREST_RATE as "InterestRate"
, INTERNAL_BANK_ACCOUNT_ID as "InternalBankAccountId"
, INV_BENEFICIARY_PARTY as "InvBeneficiaryParty"
, INV_PARTY_SITE_ID as "InvPartySiteId"
, INV_PAYEE_PARTY_ID as "InvPayeePartyId"
, INV_SUPPLIER_SITE_ID as "InvSupplierSiteId"
, LAST_UPDATED_BY as "LastUpdatedBy"
, LAST_UPDATE_DATE as "LastUpdateDate"
, LAST_UPDATE_LOGIN as "LastUpdateLogin"
, LEGAL_ENTITY_ID as "LegalEntityId"
, OBJECT_VERSION_NUMBER as "ObjectVersionNumber"
, ORG_ID as "OrgId"
, ORG_TYPE as "OrgType"
, PARTY_SITE_ID as "PartySiteId"
, PAYEE_PARTY_ID as "PayeePartyId"
, PAYMENT_AMOUNT as "PaymentAmount"
, PAYMENT_CURRENCY_CODE as "PaymentCurrencyCode"
, PAYMENT_CURR_DISCOUNT_TAKEN as "PaymentCurrDiscountTaken"
, PAYMENT_DATE as "PaymentDate"
, PAYMENT_DUE_DATE as "PaymentDueDate"
, PAYMENT_FORMAT_CODE as "PaymentFormatCode"
, PAYMENT_FUNCTION as "PaymentFunction"
, PAYMENT_GROUPING_NUMBER as "PaymentGroupingNumber"
, PAYMENT_ID as "PaymentId"
, PAYMENT_METHOD_CODE as "PaymentMethodCode"
, PAYMENT_PROFILE_ID as "PaymentProfileId"
, PAYMENT_REASON_CODE as "PaymentReasonCode"
, PAYMENT_REASON_COMMENTS as "PaymentReasonComments"
, PAYMENT_SERVICE_REQUEST_ID as "PaymentServiceRequestId"
, PAY_PROC_TRXN_TYPE_CODE as "PayProcTrxnTypeCode"
, PO_NUMBER as "PoNumber"
, REJECTED_DOCS_GROUP_ID as "RejectedDocsGroupId"
, RELATIONSHIP_ID as "RelationshipId"
, REMITTANCE_MESSAGE1 as "RemittanceMessage1"
, REMITTANCE_MESSAGE2 as "RemittanceMessage2"
, REMITTANCE_MESSAGE3 as "RemittanceMessage3"
, REMIT_TO_LOCATION_ID as "RemitToLocationId"
, SETTLEMENT_PRIORITY as "SettlementPriority"
, STRAIGHT_THROUGH_FLAG as "StraightThroughFlag"
, SUPPLIER_SITE_ID as "SupplierSiteId"
, UNIQUE_REMITTANCE_IDENTIFIER as "UniqueRemittanceIdentifier"
, URI_CHECK_DIGIT as "UriCheckDigit"
)
)
)
)
)
from iby_docs_payable_all dpa
where dpa.payment_id = ipa.payment_id
)
)
)
)
from iby_payments_all ipa
where ipa.payment_service_request_id = psr.payment_service_request_id
)
)
)
)
from IBY_PAY_SERVICE_REQUESTS psr
where psr.calling_app_id = 200
AND psr.call_app_pay_service_req_code = isc.checkrun_name
)
)
)
).getclobval()
from AP_INV_SELECTION_CRITERIA_ALL isc
where 1 = 1
and checkrun_name like 'SL%Check%0623%'
Posts
No comments:
Post a Comment