FORMULA_RESULT_FLAG column in the PAY_RUN_RESULT_VALUES table

FORMULA_RESULT_FLAG column in the PAY_RUN_RESULT_VALUES table:-

D means Run result calculated by the Fast Formula.

N means Run result created by a call to the API (Ex: PAY_RUN_RESULT_PKG).

NULL means Run result created as an indirect result or the input value directly entered by the user.

Move Order Status in Oracle Apps

select *
  from fnd_lookup_values
where lookup_type = 'MTL_TXN_REQUEST_STATUS'
and language = USERENV('LANG')
order by lookup_code

"PO: Allow Autocreation of Oracle Sourcing Documents" Profile Option in Oracle APPS

PO: Allow Autocreation of Oracle Sourcing Documents:-

• Yes or No indicates whether the buyer can create Oracle Sourcing documents from the AutoCreate window. 

• The default is No.
 

"PO: Allow Auto-generate Sourcing Rules" Profile in Oracle APPS

PO: Allow Auto-generate Sourcing Rules:-

• Create Only indicates whether the buyer can create new sourcing rules, sourcing assignments, and approved supplier list entries while approving a blanket purchase agreement. 

• Create and Update enables the buyer to update sourcing rules and assignments as well as create new sourcing rules. 

• The default is None.

"MRP: Default Sourcing Assignment Set" Profile Option in Oracle Apps

• "MRP: Default Sourcing Assignment Set" profile option indicates which sourcing rules assignment set will be used in Purchasing and Supplier Scheduling. 

• Planning allows the user to use multiple Assignment Sets, but Purchasing looks at only a single Assignment Set. 

• The value of this profile option should be the name of the Assignment Set used in Purchasing. 

• This profile option must be set if the user is using sourcing rules in Purchasing.

Query to identify the Customer Bank details in Oracle Apps R12

SELECT DISTINCT ac.customer_name, ac.customer_number
  FROM iby_pmt_instr_uses_all instr_assign,
       iby_external_payers_all payee,
       iby_ext_bank_accounts bank_acct,
       hz_cust_accounts_all hca,
       ar_customers ac
 WHERE instr_assign.instrument_id = bank_acct.ext_bank_account_id
   AND instr_assign.ext_pmt_party_id = payee.ext_payer_id
   AND instr_assign.instrument_type = 'BANKACCOUNT'
   AND instr_assign.payment_flow = 'FUNDS_CAPTURE'
   AND instr_assign.payment_function = 'CUSTOMER_PAYMENT'
   AND hca.cust_account_id = payee.cust_account_id
   AND ac.customer_number = hca.account_number

Query to get Print Lead Days in Oracle Lease and Finance Management(OLFM)

SELECT okl_stream_billing_pvt.get_printing_lead_days(:p_chr_id)
  FROM dual

p_chr_id --> okc_k_headers_all_b.id

OKL_LA_SALES_TAX_PVT.PROCESS_SALES_TAX

• This is a private API. 
• This procedure calls the tax engine and generates invoices for upfront taxes.

OKL_LA_JE_PVT.GENERATE_JOURNAL_ENTRIES

• This is a private API. 
• This procedure calls the accounting engine.

OKL_INTEREST_CALC_PUB.CALC_INTEREST_ACTIVATE

• This is a public API. 
• This procedure capitalizes or bill interim interest.

OKL_ACTIVATE_IB_PUB.ACTIVATE_IB_INSTANCE

• This is a public API. 
• It calls Install Base API to create an item instance in Install Base.

OKL_ACTIVATE_ASSET_PUB.ACTIVATE_ASSET

• This is a public API. 
• It creates an asset transaction record for each asset on the contract.

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.

Query to get Responsibility Name for given Concurrent Program Name in Oracle APPS

SELECT frt.responsibility_name, frg.request_group_name,
    frgu.request_unit_type,frgu.request_unit_id,
    fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr, fnd_responsibility_tl frt,
    fnd_request_groups frg, fnd_request_group_units frgu,
    fnd_concurrent_programs_tl fcpt
    WHERE frt.responsibility_id = fr.responsibility_id
    AND frg.request_group_id = fr.request_group_id
    AND frgu.request_group_id = frg.request_group_id
    AND fcpt.concurrent_program_id = frgu.request_unit_id
    AND frt.LANGUAGE = USERENV('LANG')
    AND fcpt.LANGUAGE = USERENV('LANG')
    AND fcpt.user_concurrent_program_name = :conc_prg_name
    ORDER BY 1,2,3,4

Query to get Concurrent Program details for given Value Set Name in Oracle APPS

select fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , fav.application_short_name
      , fav.application_name
      , ffvs.flex_value_set_name
      , flv.meaning default_type
 from   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl fdfcuv
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
      , fnd_application_vl fav
 where  fcp.concurrent_program_id = fcpl.concurrent_program_id
 and    fcpl.language = 'US'
 and    fav.application_id=fcp.application_id
 and    fdfcuv.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 and    ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
 and    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 and    flv.lookup_code(+) = fdfcuv.default_type
 and    flv.language(+) = userenv ('LANG')
 AND ffvs.flex_value_set_name = :value_set_name

OKC_K_HEADERS_ALL_B.STS_CODE in OLFM

OKC_K_HEADERS_ALL_B.STS_CODE = OKC_STATUSES_B.CODE

SELECT b.code, tl.meaning, b.ste_code
  FROM okc_statuses_b b
      ,okc_statuses_tl tl
 WHERE b.code = tl.code

Asset Return Statuses(OKL_ASSET_RETURN_STATUS) in OLFM

SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION
  FROM fnd_lookup_values
 WHERE lookup_type = 'OKL_ASSET_RETURN_STATUS'
   AND language = USERENV('LANG')

Termination Quote Reasons/Repurchase Quote Reasons(OKL_QUOTE_REASON) in Oracle Lease and Finance Management(OLFM)

SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION 

  FROM fnd_lookup_values

 WHERE lookup_type = 'OKL_QUOTE_REASON'

   AND language = USERENV('LANG')

AR_PAYMENT_SCHEDULES_ALL.CLASS in Oracle APPS

• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. 

• Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt. 

• Oracle Receivables groups different transactions by entering one of these values in the CLASS column: 
• INV for Invoice
• DM for Debit Memo
• CM for Credit Memo
• CB for Chargeback
• PMT for receipt and 
• BR for Bills Receivable

• Transaction classes determine if a transaction relates to RA_CUSTOMER_TRX_ALL table or AR_CASH_RECEIPTS_ALL table. 

• Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to RA_CUSTOMER_TRX_ALL table for nonpayment transaction entries, such as the creation of Credit Memos, Debit Memos, Invoices, or Chargebacks. 

• Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.

• When a receipt is applied, Receivables updates the AMOUNT_APPLIED, AMOUNT_DUE_REMAINING, and STATUS columns. 

• The STATUS column changes from OP for open to CL for closed for any transaction that has an AMOUNT_DUE_REMAINING value of 0. 

• For a receipt, the amount due that remains includes On Account and Unapplied amounts. 

• Receivables stores debit items such as invoices, debit memos, chargebacks, deposits, and guarantees as positive numbers in the AMOUNT_DUE_REMAINING and AMOUNT_DUE_ORIGINAL columns. 

• Credit items such as credit memos and receipts are stored as negative numbers. 

• Receipts are confirmed or not confirmed as designated by the CONFIRMED_FLAG column. 

• The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. 

• If this amount is negative, then the AMOUNT_DUE_REMAINING column indicates the credit balance amount currently available for this customer. 

• For invoices with split terms, Oracle Receivables creates one record in the RA_CUSTOMER_TRX_ALL table and one record in the AR_PAYMENT_SCHEDULES_ALL table for each installment. 

• In the AR_PAYMENT_SCHEDULES_ALL table, the DUE_DATE and AMOUNT_DUE_REMAINING columns can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column. 

• If you create a debit memo reversal when you reverse a receipt, Receivables creates a new payment schedule record for the debit memo and populates the REVERSED_CASH_RECEIPT_ID column with the CASH_RECEIPT_ID column for the reversed receipt. 

• Receivables creates a new payment schedule record when you create a chargeback. The ASSOCIATED_CASH_RECEIPT_ID column is the cash receipt of the payment you entered when you created the chargeback. 

• The ACTUAL_DATE_CLOSED column gives the date you applied a payment or credit to an open transaction that set the AMOUNT_DUE_REMAINING column to 0 for that transaction. 

• The GL_DATE_CLOSED column indicates the accounting date the transaction was closed.

AR_CASH_RECEIPTS_ALL.STATUS in Oracle APPS

• The AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter.

• Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice-related receipts. 

• For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. 

• Receivables associates a status with each receipt. These statuses include: 
• APP for applied
• UNAPP for unapplied
• UNID for unidentified
• NSF for nonsufficient funds
• REV for reversed receipt and 
• STOP for stop payment

• Receivables does not update the status of a receipt from UNAPP to APP until the entire amount of the receipt is either applied or placed on account. 

• A receipt can have a status of APP even if the entire receipt amount is placed on account.

AR_RECEIVABLE_APPLICATIONS_ALL.STATUS in Oracle APPS

• The AR_RECEIVABLE_APPLICATIONS_ALL table stores all accounting entries for both Cash and Credit Memo Applications. 

• The APPLICATION_TYPE column stores either CASH or CM (for credit memo applications). 

• Each row in this table includes the amount applied, status, and accounting flexfield information. 

• Status Column have below values: 
• APP for applied
• UNAPP for unapplied
• ACC for on-account
• UNID for unidentified
• ACTIVITY for receivable activity, and 
• OTHER ACC for other receipt application. 

Query to fetch Dependent Value Set Values for given Independent Value Set in Oracle APPS

  SELECT ffvs_major.flex_value_set_id   major_flex_value_set_id,
         ffvs_major.flex_value_set_name major_flex_value_set_name,
         ffvs_major.description         major_set_description,
         ffvs_major.validation_type     major_validation_type,
         ffv_major.flex_value           major_flex_value,
         ffvt_major.description         major_value_description,
         ffv_major.enabled_flag         major_enabled_flag,
         ffv_major.last_update_date     major_last_update_date,
         ffv_major.last_updated_by      major_last_updated_by,
         ffv_major.attribute1           major_attribute1,
         ffv_major.attribute2           major_attribute2,
         ffv_major.attribute3           major_attribute3
        ,ffvs_minor.flex_value_set_id   minor_flex_value_set_id,
         ffvs_minor.flex_value_set_name minor_flex_value_set_name,
         ffvs_minor.description         minor_set_description,
         ffvs_minor.validation_type     minor_validation_type,
         ffv_major.flex_value             minor_flex_value,
         ffvt_minor.description         minor_value_description,
         ffv_minor.enabled_flag         minor_enabled_flag,
         ffv_minor.last_update_date     minor_last_update_date,
         ffv_minor.last_updated_by         minor_last_updated_by,
         ffv_minor.attribute1             minor_attribute1,
         ffv_minor.attribute2             minor_attribute2,
         ffv_minor.attribute3             minor_attribute3
    FROM fnd_flex_value_sets ffvs_major
        ,fnd_flex_values ffv_major
        ,fnd_flex_values_tl ffvt_major
        ,fnd_flex_value_sets ffvs_minor
        ,fnd_flex_values ffv_minor
        ,fnd_flex_values_tl ffvt_minor
   WHERE ffvs_major.flex_value_set_id = ffv_major.flex_value_set_id
     AND ffv_major.flex_value_id = ffvt_major.flex_value_id
     AND ffvt_major.language = USERENV ('LANG')
     AND UPPER(ffvs_major.flex_value_set_name) = 'XX_INDEPENDENT_VS'--<<Independent Value Set Name>>
     AND ffvs_major.flex_value_set_id = ffvs_minor.parent_flex_value_set_id
     AND ffv_major.flex_value = 'XX_IND_VALUE_SET_VALUE'--<<Independent Value Set Value>>
     AND ffv_major.flex_value = ffv_minor.parent_flex_value_low
     AND ffvs_minor.flex_value_set_id = ffv_minor.flex_value_set_id
     AND ffv_minor.flex_value_id = ffvt_minor.flex_value_id
     AND ffvt_minor.language = USERENV ('LANG')
ORDER BY ffv_major.flex_value ASC