Period End Close- Oracle Financials

Payables

1) Ensure all invoices are Validated

2) Ensure all invoices are Accounted

3) Payables will transfer only those invoices which are in the status of Validated.

4) Run the following reports:

Unaccounted Transactions Report- Gives you the details of Unaccounted transactions for the period

Payable Accounting Process Report- For Invoices pending to be accounted

Payables Transfer to General Ledger - Transfer the invoices and payment details to General Ledger

Mass Additions Report- Transfer the asset related invoices into Oracle Fixed Assets module

5) Close the Period

Receivables

1) Ensure all transactions are completed

2) Ensure all Receipts are entered and status changed to Cleared

3) Once everything is completed, Click on Navigation Control>General Ledger and run the report. This will transfer the details to General Ledger

4) Close the Period

Assets

1) Ensure additions, adjustments, retirements and others details are completed for all the assets in a particular period

2) Ensure current period additions are transferred from Accounts payable thru Post Mass Additions

3) Run Depreciation Projection report to see the depreciation for the current period. Randomly check the depreciation amount for different categories

4) Run the report Create Journal Entries process which will transfer entries to General Ledger

Cash Management

1) Enter the Bank statement details for each bank in Oracel Cash Management module

2) Reconcile the Payments and Receipts details manually

3) Then Run below reports

Bank Statement Detail Report- This report will show transactions for each bank

GL Reconciliation Report

General Ledger

1) Make sure all the journals are transferrred to General Ledger. Check whether all the details transferred from sub-ledger

2) Review the Journals and Post the details once all the details are correct

3) Run the Trial Balance Report

Steps to Create With Holding Tax in Oracle Apps

Set Up Steps

 

ü  Enable Withhold Tax in Payable Options

ü  Enable Withhold Tax at Supplier Site

ü  Define Special Calendar for Withhold Tax

ü  Define Tax Codes

ü  Review Your Supplier

ü  Book an invoice with same supplier

ü  Review Withhold Tax Invoice created by Payables

Step No: 1

Enabling Withhold Tax at Payable Options

Nav: Setup > Options/Payable Options

Step No: 2

Enabling Withhold Tax at Supplier Site

Nav: Supplier > Entry

Step No: 3

Define Special Calendar for Withhold Tax

Nav: Setup/calendar/Special Calendar

Step No: 4

Define Tax Codes

Nav: Setup/Tax/Codes

Step No: 5

Review Your Supplier

Nav: Supplier/ Entry

Step No: 6

Book an standard invoice with same supplier for testing of With Holding. 

Nav: Invoices/ Entry/Invoice

Oracle Payment Processing Request (PPR) in AP – R12

PAYMENT PROCESSING REQUEST FUNCTIONALITY-
In 11i we used Payment batches to pay for multiple invoices same time. In R12, PPR is the replacement of Payment batches. R12 PPR process enables payment Administrator to select multiple invoices for payment by selection criteria and he can pause the invoice selection and payment build process. During the invoice selection review, payment manager can review the invoice selected; if the invoices were validated or approved and hence did not get included in the payment process request. He can add or remove the invoices in the Payment process and also can check the cash requirements for the full payment. Payment manager can also dismiss the individual documents or payments if necessary, and restart the payment build process.
Steps in Pay run Process-
Managing a Pay run involves 3 main processes

• Selection of the invoices for payment
• Grouping the invoices into payments
• Building the payment instruction files to either print checks or send instructions to bank.

There are four steps in the processing of PPR:-

• Document selection – Handled by Payables(AP)
• Build Payments – Handled by Payments(IBY)
• Format Payments – Handled by Payments(IBY)
• Confirm Payments – Handled by Payables(AP)

Submitting a Single Payment Process Request
Mandatory fields – Payment Process Request name, pay through date, Payment date, and Exchange rate type.
Under Processing tab, options are available to stop the process after document selection/payment and also how to create the payment instructions:

1. Maximize Credits.
2. Stop Process for review after scheduled payment selection.
3. Calculate payment withholding and interest during scheduled payment selection.
4. Stop process for review after creation of proposed payments.

Click on submit to submit the Payment process request.
Document Selection – Payables
This process calls AP_AUTOSELECT_PKG.
When a payment process request is submitted, a record is inserted in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name i.e payment process request name. Invoices are then selected based on the due date, discount date, paygroup, and other criteria provided by the user while submitting the PPR.
The AP_SELECTED_INVOICES_ALL table is populated with the selected invoices and AP_UNSELECTED_INVOICES_ALL table by the unselected invoices.
Note: After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
If the PPR has been setup to ‘Stop Process for Review after Scheduled Payment Selection’, the process stops for user review.
Then the status of the PPR is set to Invoices Pending Review.
If the ‘Stop Process for Review after Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.
If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled – No Invoices Selected”. Then void all invoices
For others, the actions available are
a) Terminate the PPR
b) Modify / proceed to submit the PPR and start the build process.
Build Payments – Payments
Call IBY_DISBURSE_SUBMIT_PUB_PKG
Build payment creates records in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.
A payment process request is a group of documents payable that a source product submits to Oracle Payments for payment service processing. This table contains the parameters like Calling application identifier, Internal bank account, Allow zero payments flag, etc. selected in the Payment Process Request.

PAYMENT_SERVICE_REQUEST_ID	NUMBER	System generated primary key
CALLING_APP_ID	NUMBER	Source product Identifier
CALL_APP_PAY_SERVICE_REQ_CODE	VARCHAR2	Source product’s payment process request Identifier. Since the source product’s Identifiers may be alphanumeric, even numeric document Identifiers are stored as VARCHAR2.
PAYMENT_SERVICE_REQUEST_STATUS	VARCHAR2	Payment process request status. Values from the lookup IBY_REQUEST_STATUSES include PAYMENTS_CREATED.
PROCESS_TYPE	VARCHAR2	Specifies the process by which documents payable are built into payments and payments into payment instructions. Values from the lookup IBY_PROCESS_TYPES include STANDARD, IMMEDIATE, and MANUAL.
ALLOW_ZERO_PAYMENTS_FLAG	VARCHAR2	Y or N flag that indicates whether zero payments are allowed for this payment request. If set to N, any zero value payments created for this payment request is failed.
INTERNAL_BANK_ACCOUNT_ID	NUMBER	Internal bank account identifier
MAXIMUM_PAYMENT_AMOUNT	NUMBER	Maximum payment amount used to override default maximum payment amount
MINIMUM_PAYMENT_AMOUNT	NUMBER	Minimum payment amount used to override default minimum payment amount

Note: The displayed status of the PPR is generated by ibyvutlb.pls
Following are the possible values of PAYMENT_SERVICE_REQUEST_STATUS column-

• DOCUMENTS_VALIDATED
• INFORMATION_REQUIRED
• INSERTED
• PAYMENTS_CREATED
• PENDING_REVIEW
• TERMINATED
• VALIDATION_FAILED
• COMPLETED

 In 11i AP_SELECTED_INVOICE_CHECKS_ALL table is populated by the Build Payment process.
The Build Program also populates IBY_DOCS_PAYABLE_ALL table
IBY_DOCS_PAYABLE_ALL– This table contains the documents payable which are updated by system while processing “Build Payments” program. A document payable is a supplier invoice or similar document that needs to be paid.  In addition, this table contains whatever document information is necessary for payment processing.
This table contains transaction details, document details, payer, payee, etc.”

Name	Datatype	Comments
PAY_PROC_TRXN_TYPE_CODE	VARCHAR2	Type of payment processing transaction or document
CALLING_APP_ID	NUMBER	Calling product Identifier
CALLING_APP_DOC_REF_NUMBER	VARCHAR2	Reference number entered by user of the source product. Need not be unique
DOCUMENT_PAYABLE_ID	NUMBER	Oracle Payments’ unique internal document payable Identifier
PAYMENT_FUNCTION	VARCHAR2	Function or purpose of the payment. Values from the lookup IBY_PAYMENT_FUNCTIONS include SUPPLIER_PAYMENT, CUSTOMER_REFUNDS, and others.
PAYMENT_DATE	DATE	Payment date
DOCUMENT_DATE	DATE	Date of document
DOCUMENT_TYPE	VARCHAR2	Type of document payable. Values from the IBY_DOCUMENT_TYPES lookup include INVOICE.
DOCUMENT_STATUS	VARCHAR2	Document status. Values from the lookup IBY_DOCS_PAYABLE_STATUSES include PAYMENT CREATED.
DOCUMENT_CURRENCY_CODE	VARCHAR2	Document currency code
DOCUMENT_AMOUNT	NUMBER	Total amount in document currency
PAYMENT_CURRENCY_CODE	VARCHAR2	Payment currency code
PAYMENT_AMOUNT	NUMBER	Amount to be paid in payment currency
PAYMENT_SERVICE_REQUEST_ID	NUMBER	Identifier of the payment process request in which this document was submitted
PAYMENT_METHOD_CODE	VARCHAR2	Payment method Identifier
EXCLUSIVE_PAYMENT_FLAG	VARCHAR2	Y or N flag indicating whether this document payable should not be grouped with any other documents payable.
CALLING_APP_DOC_UNIQUE_REF1	VARCHAR2	Source product’s first unique document payable Identifier
CALLING_APP_DOC_UNIQUE_REF2	VARCHAR2	Source product’s second unique document payable Identifier (Invoice_id)
CALLING_APP_DOC_UNIQUE_REF3	VARCHAR2	Source product’s third unique document payable Identifier(Payment_number)
CALLING_APP_DOC_UNIQUE_REF4	VARCHAR2	Source product’s fourth unique document payable Identifier
CALLING_APP_DOC_UNIQUE_REF5	VARCHAR2	Source product’s fifth unique document payable Identifier

A.  Internal Bank Account/Payment Process Profile Assignment:
Call IBY_ASSIGN_PUB
If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required – Pending Action”
User should complete the missing information and Run Payment Process to continue.
B.    Document Validation
Call IBY_VALIDATIONSETS_PUB

During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations.b.1 – If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failureb.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.
b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.
c.     Create Payments
Call IBY_PAYGROUP_PUBThe validated documents are then grouped into proposed payments based on the grouping rules, both users defined and hard coded.
Example: If exclusive_payment_flag = Y on a document, it is paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.

IBY_PAYMENTS_ALL
This table contains all the payments created by system while processing “Build Payments”. A Payment can be single check or an electronic fund transfer between first party payer and third party payee. A row in this table corresponds to one or more documents payable. Payments are built by grouping documents payable according to Oracle Payments’ grouping rules.
This table also stores information of payments at grouping level. The groups can be Single, Mixed and grouped as defined in Payment Process Profile for the purpose of SEPA.
The payment details are displayed on the Payments tab of the Funds Disbursement Process Home page.

Name	Datatype	Comments
PAYMENT_ID	NUMBER	Unique internal Identifier for this record. Generated using a database sequence.
PAYMENT_METHOD_CODE	VARCHAR2	Payment method used for making the payments.
PAYMENT_SERVICE_REQUEST_ID	NUMBER	Payment service request Id and it is the foreign key to the table iby_pay_service_requests.
PROCESS_TYPE	VARCHAR2	Specifies the process by which the payment is built into a payment instruction. Values, from the lookup IBY_PROCESS_TYPES, include STANDARD, IMMEDIATE, and MANUAL.
PAYMENT_STATUS	VARCHAR2	The status of the Payment. Values are derived from the lookup IBY_PAYMENT_STATUSES. The possible values are CREATED, FORMATTED, TRANSMITTED, VOID_BY_OVERFLOW, REJECTED, FORMATTED, VOID, etc.
PAYMENTS_COMPLETE_FLAG	VARCHAR2	Y or N flag that indicates if the payment is complete
PAYMENT_FUNCTION	VARCHAR2	Function or purpose of the payment. Values from the lookup IBY_PAYMENT_FUNCTIONS include SUPPLIER_PAYMENT, CUSTOMER_REFUNDS, and others.
PAYMENT_AMOUNT	NUMBER	Amount of the payment
PAYMENT_CURRENCY_CODE	VARCHAR2	Currency of the payment
BILL_PAYABLE_FLAG	VARCHAR2	Y or N flag indicating whether a payment is a bill payable, that is, a future dated payment
EXCLUSIVE_PAYMENT_FLAG	VARCHAR2	Y or N flag indicating whether this payment is made up of a single document payable that was meant to be paid alone
SEPARATE_REMIT_ADVICE_REQ_FLAG	VARCHAR2	Y or N flag indicating whether a separate remittance advice needs to be generated for a payment.
INTERNAL_BANK_ACCOUNT_ID	NUMBER	Internal bank account id used for making the payment.
ORG_ID	NUMBER	Unique internal identifier of the Operating Unit. Validated against HR_OPERATING_UNITS.ORGANIZATION_ID.
ORG_TYPE	VARCHAR2	Organization type. Values, from the lookup IBY_ORGANIZATION_TYPES Include Operating Unit, Business Group, and Legal Entity
LEGAL_ENTITY_ID	NUMBER	Legal entity identifier

The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.
Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.
None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’
If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.

Format Payments – Payments

Call IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB

When a PPR is submitted, there are two options
The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.
The table IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.
If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID
If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id
Key Columns of IBY_PAYMENT_INSTRUCTIONS_ALL table
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code
Format:
The following processing occurs during the format step.
a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.
Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint
Confirm Payments – Payables
Call AP_PMT_CALLOUT_PKG

Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.
This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is updated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

Payment Batches Waiting to be Reviewed query in oeacle apps

select call_app_pay_service_req_code
     , (select 'Invoice Count = ' || count(1) ||
               ', Amount ='       || to_char(sum(payment_amount),'$999,999,990.00')
          from ap_selected_invoices_all asi
         where checkrun_name = call_app_pay_service_req_code
           and ok_to_pay_flag = 'Y'
       )
  from iby.iby_pay_service_requests psr
 WHERE calling_app_id                 = 200
   AND payment_service_request_status = 'PENDING_REVIEW'

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