SQL Query to get the linkage between Fixed Asset and Oracle Lease and Finance Management(OLFM) Asset

SELECT lse.lty_code,
       cle.id,
       cle.cle_id,
       itm.object1_id1,
       itm.jtot_object1_code
  FROM apps.okc_k_lines_b     cle,
       apps.okc_line_styles_b lse,
       apps.okc_k_items       itm,
       fa_additions           ast
 WHERE lse.id = cle.lse_id
   AND itm.cle_id = cle.id
   AND ast.asset_id = itm.object1_id1
   AND cle.dnz_chr_id = (SELECT khr.id
                           FROM apps.okc_k_headers_all_b khr
                          WHERE khr.contract_number = 'CONTRACT_NUMBER')
   AND itm.jtot_object1_code = 'OKX_ASSET';

List of API's used for Asset Disposal in Oracle Lease and Finance Management(OLFM)

Below are the list of api's used in OLFM for asset disposal.

API Name	API Description
FA_ADDITION_PUB.DO_ADDITION	This API is used to add an asset to a specific book in Fixed Assets.
OKL_OKC_MIGRATION_PVT.UPDATE_CONTRACT_ITEM	After creation of asset disposal in Fixed Assets, update contract item with the proper linkage(Asset_ID)
OKL_TRX_ASSETS_PUB.CREATE_TRX_ASSET_DEF	This API is used to create asset disposal header in OKL_TRX_ASSETS
OKL_TXL_ASSETS_PUB.CREATE_TXL_ASSET_DEF	This API is used to create asset disposal line in OKL_TXD_ASSETS_V
OKL_TXD_ASSETS_PUB.CREATE_TXD_ASSET_DEF	This API is used to create asset disposal details in OKL_TXL_ASSETS_V
OKL_SLA_ACC_SOURCES_PVT.POPULATE_SOURCES	This API is used to populate proper asset accounting entries information for asset in OLFM.
OKL_AM_ASSET_DISPOSE_PUB.DISPOSE_ASSET	This procedure is used to retire an asset in Fixed Assets. It checks if the asset is to be fully or partially retired, based upon the parameter value p_quantity and then calls the appropriate routine to retire the asset. It then stores the disposition transactions in OKL tables, calls accounting engine and then finally cancels all pending transactions in OKL tables for this asset.
FA_ADJUSTMENT_PUB.DO_ADJUSTMENT	This API is used to adjust the financial information of an asset in a specific book in Fixed Assets.
FA_RETIREMENT_PUB.DO_RETIREMENT	This API is used to partially or fully retire an asset in a specific book in Fixed Assets.
OKL_AM_ASSET_DISPOSE_PVT.EXPIRE_ITEM	This procedure expires an item in Installed Base(IB).
CSI_ITEM_INSTANCE_PUB.EXPIRE_ITEM_INSTANCE	This procedure expires an existing item instance and its child entities in Install Base.

List of tables for off-lease amortization Oracle Lease and Finance Management(OLFM) Internal Transactions:

OKL_TRX_ASSETS:-

• A top level transaction table to group all the transactions for creation of Asset lines for a contract.

OKL_TXD_ASSETS_V:-

• A transaction table to record tax book related asset attributes needed by Oracle Fixed Assets before activating the contract and additionally to store the split asset transaction attributes needed by Fixed Assets before activating the contract.

OKL_TXL_ASSETS_V:-

• A contract line level transaction table to record asset attributes needed by Fixed Assets before activating the contract.

Asset return table(OKL_ASSET_RETURNS_B) in Oracle Lease and Finance Management(OLFM)

OKL_ASSET_RETURNS_B:-

• This is the transaction table for managing Assets that is off lease after a Contract Termination or assets that have been identified as possible returns.
• The return asset transaction is created by one of the following: 

1. Repossession request
2. Customer or Vendor notification of intent to return 
3. System notification of potential return

Terminate Expired Contracts in Oracle Lease and Finance Management(OLFM)

• Run the Standard Concurrent Program"Terminate Expired Contracts"  to set the status of contracts to "Expired". 

• This step will create the off-lease amortization transaction for each asset in Lease and Finance Management. 

• The following steps or activities are performed when processing terminated expired DF lease contracts:

1. Eligible for Termination:- Checks if billing is complete up to the termination date (only if contract has expired).
2. Close Balances (Write-off Small Balances):- Creates adjustment transactions for the balances less than tolerance amount (Only for Full Termination).
3. Post Accounting Entries:-

• Creates termination accounting entries.
• Creates accrual accounting entries for catch-up (i.e., transfer accruals from non-income bucket to income bucket). Only for Full Terminations.
• Reverse loss provisions. Only for Full Terminations.
• Reverse accruals (if excess accruals exist). Only for Full Terminations.
• Accelerate income accrual recognition for financed fees. Only for Full Terminations.
• Accelerate income recognition or refund subsidy (based on subsidy setup).

      4.Dispose Asset:-

• Retires assets in Fixed Assets. Expires Item in Install base. Retires asset in FA on quote effective from date.

     5.Amortize Asset (Create Off-Lease Amortization):-

•   Creates off-lease amortization transactions in Lease and Finance Management, which are then processed into Fixed Assets (to adjust depreciation). Off-lease amortization transaction date is set to quote effective date.

     6. Create Asset Return:-

• Creates asset return record in Lease and Finance Management with return status as Scheduled. 

     7. Update Contract Status (streams, contracts and contract lines):-

• Updates stream status to HISTORIZE (except for residual value and investor contract obligation payable streams).
• Sets the lines and contract to Terminated. Sets date terminated as quote effective from date. (Only for Full Termination). If the contract is evergreen eligible the contract and lines are updated to Evergreen status. Along with that off-lease amortization and Termination accounting is also done for the contract.

     8.Update Contract Details (payments, contract lines):-

• Updates the payments i.e. remove future payments for terminated assets after termination date.
• Updates lines to Terminated for all the terminated financial assets. If the financial asset is attached to service or fees, terminates those sub lines as well. Terminates the primary service or fee line if all sub-lines terminated. (Does not terminate those primary service and fee lines that do not have any sub lines).
• Launches mass Rebook, which Rebooks the contract. Sets Rebook date as quote-effective date. Pass terminated assets to Rebook API. (Only for Partial Termination).

SQL Query to get the linkage between Install Base and Lease and Finance Management(OLFM) Asset Instance:

SELECT lse.lty_code,
       cle.id,
       cle.cle_id,
       itm.object1_id1,
       itm.jtot_object1_code
  FROM apps.okc_k_lines_b     cle,
       apps.okc_line_styles_b lse,
       apps.okc_k_items       itm,
       csi_item_instances     ib
 WHERE lse.id = cle.lse_id
   AND itm.cle_id = cle.id
   AND ib.instance_id = itm.object1_id1
   AND cle.dnz_chr_id = (SELECT khr.id
                           FROM apps.okc_k_headers_all_b khr
                          WHERE khr.contract_number = 'CONTRACT_NUMBER')
   AND itm.jtot_object1_code = 'OKX_IB_ITEM';

SQL Query to get the linkage between Fixed Asset and Lease and Finance Management(OLFM) Asset

SELECT lse.lty_code,
       cle.id,
       cle.cle_id,
       itm.object1_id1,
       itm.jtot_object1_code
  FROM apps.okc_k_lines_b     cle,
       apps.okc_line_styles_b lse,
       apps.okc_k_items       itm,
       fa_additions           ast
 WHERE lse.id = cle.lse_id
   AND itm.cle_id = cle.id
   AND ast.asset_id = itm.object1_id1
   AND cle.dnz_chr_id = (SELECT khr.id
                           FROM apps.okc_k_headers_all_b khr
                          WHERE khr.contract_number = 'CONTRACT_NUMBER')
       AND itm.jtot_object1_code = 'OKX_ASSET';

Contract Import Step by step process in OLFM

• The following process should be sequentially executed.

         Import àFundingàValidationàStream GenerationàDraft Journal EntryàApproval

• Some of the processes mentioned are optional like Draft Journal Entry & Approval. These optional processes can be skipped based on the business process and the state of the contracts in the legacy systems. 

• If the contracts are created in batches, while one batch is undergoing one process, other batch can be run in parallel. For example, while the first batch is undergoing stream generation, the second batch can be submitted for validation.

API Used to Approve Contract in Oracle Lease and Finance Management(OLFM)

• Review and approve the contract before activation, you can approve one contract at a time from the booking page and initiating the approval workflow. 
• To perform a mass update to change the contract status to APPROVED using the approval API. 

• The contract status must be updated to APPROVED before a contract can be activated i.e. BOOKED, but execution of the approval workflow is an optional step. 

• Use the following API to submit contracts for approval.

API Name	Description
OKL_CONTRACT_BOOK_PUB. SUBMIT_FOR_APPROVAL ()	This program will submit the contract for approval

Note:- There is no parallel processing or bulk processing capabilities for contract approval program.This API can be called one contract at a time. If you need batch processing capabilities to submit multiple contracts at one time, you can create custom wrappers. If you want to by-pass the approval workflow, you can create a custom program to update the contract status to APPROVED once contracts are validated and in PASSED status.

API to Generate Draft Journal Entries in Oracle Lease and Finance Management(OLFM)

• In order to see draft journal entry records of the expected booking accounting before booking the contract, you can request draft journal entry creation. 

• You do not need to create or view draft journal entries in order to book (activate) a contract. 

• Streams must be completed for a contract (status COMPLETED) before you can generate draft journal entries. 

• After generating draft journal entries, the contract status remains as COMPLETED. 

• You can submit a contract for creating journal entries from the screen or call private Lease and Finance Management APIs to process the draft journal entry generation.

• Following API can be used to generate the draft journal entries.

API Name	Description
OKL_CONTRACT_BOOK_PUB. GENERATE_JOURNAL_ENTRIES ()	This program will submit the contract for draft journal entry generation

Note:- There is no parallel processing or bulk processing capabilities for draft journal entry program. This API can be called one contract at a time. If you need batch processing capabilities to submit multiple contracts at one time, you can create custom wrappers.

API to generate streams in Oracle Lease and Finance Management(OLFM)

• After contracts are validated, we need to submit those contracts for pricing. 
• Pricing generates streams and yields for each contract. 
• After pricing is completed, the contract status is updated to "COMPLETED". 
• For imported contracts, you can submit a contract for pricing from the screen or call private Lease and Finance Management APIs. 
• You should submit the contract through pricing at the time of import unless you need to update or reconcile a contract after validation. 
• If a contract is updated after validation, the contract status reverts to INCOMPLETE.
• Use the following API to generate streams and yields for contracts that are validated.

API Name	Description
OKL_CONTRACT_BOOK_PUB.GENERATE_STREAMS()	This program will submit the contract for pricing

Note: There is no parallel processing or bulk processing capabilities for the pricing program.

API used to validate contract in Oracle Lease and Finance Management(OLFM)

• After you create contracts in NEW status, you must validate them before you continue the activation process. 

• After successful validation, the contract is updated to PASSED status. If there are any errors, Lease and Finance Management updates the status to ERROR or WARNING. 

• You can submit a contract for validation from the Lease Management contract authoring pages to process each contract separately and view each validation message. 

• If you want to process as a batch, you can call a private Lease and Finance Management API to process the validation.

• Use the following API to validate a contract which is in NEW status

API Name	Description
OKL_CONTRACT_BOOK_PUB.VALIDATE_CONTRACT()	This program will submit the contract for validation

• This API can be called one contract at a time.
  Note:- There is no parallel processing or bulk processing capabilities for the validation program.

Funding Requests in Oracle Lease and Finance Management(OLFM)

• Funding requests are used to request and process payments to vendors/suppliers for items you finance on a contract or any associated expenses. 

• Approved funding requests can be processed using standard programs to create invoices in Oracle Payables.

• You should create actual funding requests for Open Invoices or Partially Paid Payable invoices. Update the funding request to "Approved" using update APIs for each actual funding request that corresponds to an open payable invoice. 

• The Lease Management disbursement program picks up these funding requests and creates payable invoices automatically when you run the correct programs.

• Create funding header, lines and approve funding using the following APIs. 

API Name	Description
OKL_FUNDING_PVT.CREATE_FUNDING_HEADER()	This program will create funding header
OKL_FUNDING_PVT. CREATE_FUNDING_LINES()	This program will create funding lines
OKL_FUNDING_PVT. UPDATE_FUNDING_HEADER()	This program will activate the funding by updating the status

Note:- Parallel processing is not supported in these funding APIs.

Contract Import in Oracle Lease and Finance Management(OLFM)

Oracle Lease and Finance Management(OLFM) allows you to import contracts through a standard set of Interface Tables. The import program imports data from interface tables into the Oracle Lease and Finance Management tables. After interface tables are populated, we need to submit a request to import and process contracts.

• Navigation to submit the Import Contract Concurrent Program 

         Processing->Interfaces->Import Contracts. 

• Parameters used for the Contract import batch:  Batch Number, Contract Number, Customer Number, Start Date From, Start Date To, Number of Parallel Instance. 

• Based on the parameter(s) entered, a set of contracts will be selected for import for the specific request from the interface tables. You populate the following interface tables before submitting the contract import concurrent program.

• Populate the following interface tables before submitting the contract import concurrent program.

Interface Table Name	Description
OKL_HEADER_INTERFACE	Contract header table
OKL_LINES_INTERFACE	Contract lines table
OKL_PAYMENTS_INTERFACE	Payments table
OKL_PARTY_ROLES_INTERFACE	Party Roles
OKL_TERMS_INTERFACE	Contract Terms & Conditions

• We can import Contracts in Batches by populating the batch number field in the interface header table. The contract import program follows these processes:

1. Validate required values:–The program validates the required values are loaded for each contract in the interface tables. The system marks the record status as "ELIGIBLE" if all required values are found populated. else, it changes status to "ERROR" and reports the error detail in a concurrent log/output file.
2. Load into Lease Management tables:– After the validation phase, each "ELIGIBLE" contract is populated in the corresponding destination data table. The record status in the Interface is set to either "INTERFACED" or "ERROR" depending on the result of loading. This process continues for all "ELIGIBLE" contracts. The system generates a detailed report as a concurrent manager log/output file.
3. Process:– After the load, the import program processes loaded contracts. As each activation step is completed, the contract status is updated.

         

Contract Status Description	Description
NEW	Interface contract data is loaded to create a new contract
PASSED	Contract has been validated by the QA checker
COMPLETE	Streams are generated and draft journal entries created
APPROVED	Contract is approved using the activation approval workflow
BOOKED	Contract is activated and all booking activities are completed

• Based on the parallel processing capability, users can specify number of parallel workers as a parameter for contract import program.

Stream, Stream Activity, Stream Element, Stream Purpose, Stream Type and Stream Type Subclass in OLFM

Stream:-

• A Stream is a schedule of amounts and associated dates.

Stream Activity:-

• Stream Activity describes the state of the Stream. 
• A Stream can be 

1. ACTIVE
2. CURRENT
3. HISTORY or
4. WORKING

Stream Element:-

• A single amount and its corresponding date i.e. a cash flow. 
• A Stream element is the line level detail of a Stream. 
• A Stream can have multiple Stream Elements.

Stream Purpose:-

• A Stream purpose is an important attribute of a Stream Type that determines how the Lease Management processes the Stream.
• Lease Management recognizes streams based on their stream purpose name.

Stream Type:-

• The functional Name for a stream which relates to the attributes used to process  a Stream.

Stream Type Subclass:-

• Stream Type Subclass are

1. Rent or
2. Residual.

 

Termination, Termination Quote in OLFM

Termination:-

• The process of ending a Leasing Transaction with the Lessee.

Termination Quote:-

• Termination Quotes allow Contracts or Assets on a contract to terminate early or at the expiration of a contract. 
• Termination Quotes identify the financial impact of the termination.
• The various business rules specified in a contract's terms and conditions are the basis of calculating financial impact of a termination quote.

Where is the data stored after AR Lockbox Validation?

Data gets imported to interim tables after successful validation.
Below are interim tables:
AR_INTERIM_CASH_RECEIPTS
AR_INTERIM_CASH_RECEIPT_LINES

Note:- Set the org context before running above queries.
exec mo_global.set_policy_context('S', <<org_id>>);

Why does the Status of AR_TRANSMISSIONS_ALL not change when deleting batch and receipts

Symptoms:-

• Receipts are being deleted before Lockbox validation has been run. Status of AR_TRANSMISSIONS_ALL does not change when deleting batch and receipts.
• Unused transmission names are included in Transmission Name field List of Values. Transmissions have no content.
• Receipts are not shown in the Maintain Lockbox Transmission Data Window. No receipts in AR_PAYMENTS_INTERFACE_ALL table.
• Before the validate Lockbox receipts step has been run, the transmission will have status NB, that is new, in the AR_TRANSMISSIONS_ALL table.

Explanation:-

• This is intended behavior(Doc ID 1075485.1):-

1. If these receipts are deleted before validation, the status will not be updated in the AR_TRANSMISSIONS_ALL table, and the transmission will remain open.
2. Post Quick Cash can be submitted even when no more batch and receipt exists for the transmission. It will just update ar_transmissions_all set status='CL'
3. The status is updated to 'CL' by submitting Post Quick Cash from the Lockbox screen. Creating and closing a transmission can be done only in the Lockbox form.

What are the various Transmission Status available in AR Lockbox Process?

Lockbox Transmission can have one of the following Statuses:-

• New: This transmission has been imported into Receivables but has not yet been validated.

• Out of Balance: One or more of the receipts in this transmission was rejected during validation.

• Open: All of the receipts in this transmission have been successfully validated and transferred into Receivables. Post QuickCash has not yet processed these receipts. 

• Closed: All of the receipts in this transmission have been successfully processed by Post QuickCash. You can review these receipts in the Receipts window.

Where can we review receipts that are failed during Lockbox Validation?

You can review receipts that failed the validation step in the Lockbox Transmission Data window.

Responsibility: <<XX>> Receivables Manager

Navigation: Receipts > Lockbox > Transmission History

• Receivables keeps track of each lockbox transmission you submit through the Submit Lockbox Processing window.

• Use the Lockbox Transmission History window to review information about your lockbox transmissions such as the origination date, the number and amount of records in a transmission, and the number and amount of receipts that passed the validation step.

• The Control Count and Amount fields display the total number and amount of records in this lockbox transmission. The Validated Count and Amount fields display the total number and amount of receipts in this transmission that passed the validation step.

• Status of OOB Out of Balance means: One or more of the receipts in this transmission was rejected during validation. Go to Maintain Transmission Data to view the detail of this receipt. There is no need to update the status or delete the record.

Navigation: Receipts > Lockbox > Maintain Transmission Data

Query with Transmission Name or Creation Date or Lockbox Number or Batch Name or Receipt Number....etc.

How can the data be reviewed after it is successfully imported and validated through Lockbox Process?

• After successfully importing and validate receipts using Lockbox, we can review them in the QuickCash Form window. Use the Transmission region in the Receipt Batches window to query all receipt batches that were included in one transmission and to update or delete any receipt information. 

• We can review Lockbox receipts before or after running Post QuickCash. If we have submitted Post QuickCash for this Lockbox transmission, you can review these receipts only in the Receipts or the Adjustments window.

What table needs to be populated while using Lockbox via a custom program?

Data need to be populated in AR_PAYMENTS_INTERFACE_ALL interface table.

Is it mandatory to always use import step of AR Lockbox for importing the bank data?

It is not necessary to use the import step of lockbox, data can be imported through SQL*Loader or any other custom program to populate data in the interface(ar_payments_interface_all) table.

API to Create Lines in OKL_TXD_AR_LN_DTLS_B (OKL Invoice Distributions)

okl_txd_ar_ln_dtls_pub.insert_txd_ar_ln_dtls (
                           p_api_version     => 1.0,
                           p_init_msg_list   => FND_API.G_TRUE,
                           x_return_status   => lc_return_status,
                           x_msg_count       => l_msg_count,
                           x_msg_data        => l_msg_data,
                           p_tldv_rec        => l_tldv_rec,
                           x_tldv_rec        => o_l_tldv_rec)

API to Create Lines in OKL_TXL_AR_INV_LNS_B(OKL Invoice Lines) in OLFM

okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
                     p_api_version     => 1.0,
                     p_init_msg_list   => FND_API.G_TRUE,
                     x_return_status   => lc_return_status,
                     x_msg_count       => l_msg_count,
                     x_msg_data        => l_msg_data,
                     p_tilv_rec        => l_tilv_rec,
                     x_tilv_rec        => o_l_tilv_rec);

API to Create Transactions in OKL_TRX_AR_INVOICES_B( OKL Invoice Hearder ) in OLFM

okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
               p_api_version     => 1.0,
               p_init_msg_list   => FND_API.G_TRUE,
               x_return_status   => lc_return_status,
               x_msg_count      => l_msg_count,
               x_msg_data        => l_msg_data,
               p_taiv_rec          => l_taiv_rec,
               x_taiv_rec          => o_l_taiv_rec
            );

Residual Value in OLFM:-

Residual Value is the value of an asset at the end of a lease term that you record on the lease contract. Periodically, you may need to reduce the residual value of the asset.

Buyout and Buyout Amount in OLFM

Buyout:- Customer buys equipment from lessor at the end of the contract's full life.

Buyout Amount:- This is the amount necessary for the customer to pay in order to consider the contract paid in full.