Wednesday, 10 June 2015

Payment Batch Details query in oracle apps

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%'

No comments:

Post a Comment