, , , , , ,

Receipt API in Oracle Receivables

Autolock box provides a functionality to create receipts using interface method. Recent versions of oracle applications, especially 11.5 onwards, Receipt API is getting used as they are flexible to the user’s need, and customization across all kinds of interfaces, loading.
Here are the receipt APIs used in 11i applications:
ar_receipt_api_pub.Create_cash
ar_receipt_api_pub.Apply
ar_receipt_api_pub.Unapply
ar_receipt_api_pub.Reverse
ar_receipt_api_pub.Apply_on_account
ar_receipt_api_pub.Unapply_on_account
ar_receipt_api_pub.Activity_application
ar_receipt_api_pub.Activity_unapplication
ar_receipt_api_pub.Apply_other_account
ar_receipt_api_pub.Unapply_other_account
ar_receipt_api_pub.create_misc
ar_receipt_api_pub.set_profile_for_testing
ar_receipt_api_pub.Apply_Open_Receipt
ar_receipt_api_pub.Unapply_Open_Receipt

Receipt On Account & Unapplied – SQL Query

A cash receipt is an applied receipt if it is associated with a customer number and an open invoice(s).
An unapplied receipt is a cash receipt that can be applied to a customer account if it is associated with a customer number but not associated with an invoice (that is, there is no invoice for the sale or the invoice number is unknown).
An on-account receipt (cash-in-advance) is:
* recorded to the customer account
* applied against the invoice when the invoice is generated
An unidentified receipt is from an unknown source.
An Application Advice Form is required for any receipt labeled as unidentified.
Here is the simple query to find the On-Account and UnApplied amount for a customer.
 

SELECT   NVL (SUM (DECODE (
                         ara.STATUS,
                         ‘ACC’, -amount_applied,
                         0
                       )), 0),
                   NVL (SUM (DECODE (
                         ara.STATUS,
                         ‘UNAPP’, -amount_applied,
                         0
                       )), 0)
              FROM ar_receivable_applications_all ara,
                   ar_cash_receipts_all acr
             WHERE ara.cash_receipt_id = acr.cash_receipt_id
               AND acr.customer_site_use_id = ‘&&site_use_id’
               AND ARA.STATUS IN ( ‘ACC’, ‘UNAPP’ )                
               AND ara.confirmed_flag IS NULL
          GROUP BY acr.currency_code,
                   acr.customer_site_use_id;


Receipt Status – Oracle Applications

A receipt can have one of the following statuses:
Approved: This receipt has been approved for automatic receipt creation. This status is only valid for automatic receipts.
Confirmed: The customer has approved the application of this receipt and their account balances have been updated within Receivables. This status is only valid for automatic receipts.
Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.
Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.
Reversed: This receipt has been reversed. You can reverse a receipt when your customer stops payment on a receipt, if a receipt comes from an account with non-sufficient funds or if you want to re-enter and reapply it in Receivables. You can reverse cash receipts and miscellaneous transactions.

/by
, , , ,

What is auto lockbox in oracle applications?

Lockbox is a service provided by banks by which your company gets the customers payments directly to a lockbox interface tables and creates receipt for the payments deposited into your account. If you have an Auto Lockbox, the bank records the information that you request such as check number, check amount, numbers and amount for the invoices to be paid.
Oracle provides you with the tools to:
* Oracle interface tables for the data received from the bank
* Validate the data to see if it is accurate, complies with the controls provided
* Correct the data
* Apply the receipts to the customer’s open invoices.

A typical Lockbox transmission contains various different records, each with relevant data. Controls are provided at each level to ensure that the transmission was successful and to verify that the count and dollar amounts are consistent with what the bank indicated. These controls are at the transmission, Lockbox, batch and receipt levels. The records also contain information such as your bank account (by Lockbox) and the details for the receipts the bank received. The Lockbox may be used for checks, wires and any other receipts that you receive. You define what the data from the bank will look like and how you will use it.
Oracle Accounts Receivables Module provides the feature such that the customer can directly make the payment for their invoices in the Bank.The Bank would send a datafile in a  agreed transmission format which we we import in Receivables through AutoLockbox.After successful import the Receipts get created in the final stage.
This is a three step process
1) Validate the data file
2) Import the data file which created the Post Batch
3) Run the Post Batch  (ie Post Quick Cash) which would actually create the Receipts and applies against the Invoice on the Information provided in the data file.
Following are the main tables for auto lockbox

AR_PAYMENTS_INTERFACE_ALL
AR_INTERIM_CASH_RECEIPTS_ALL
AR_INTERIM_CASH_RCPT_LINES_ALL
AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_ALL
AR_DISTRIBUTIONS_ALL
AR_RECEIVABLE_APPLICATIONS_ALL
AR_PAYMENT_SCHEDULES_ALL

/by
, ,

Naming Conversion Table Name end with _ALL, _TL, _VL, _V, _F, _A, _B, _S, _AVN and its Meaning

  • _ALL : Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.
  • _TL : are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
  • _B : these are the BASE tables.
    They are very important and the data is stored in the table with all validations.
    It is supposed that these table will always contain the perfect format data.
    If anything happens to the BASE table data, then it is a data corruption issue.
  • _F : these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.
  • _V : tables are the views created on base tables
  • _VL : are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV(‘LANG’).
  • _S : are sequences, used for finding new values for the primary key of a table.
  • _A : are Audit Shadow Tables
  • _AVN : and _ACN are Audit Shadow Views (when data was changed, and with what values
/by
, , , , ,

“Error: The action can not be performed because the selected records are not eligible.”.

“Error: The action can not be performed because the selected records are not eligible.”.

Solution

There are several ways to resolve this, depending on how you want the order to progress:
A. If you want to ship the delivery lines , do the following:
1. Query the Order in Shipping Transaction form
2. Navigate to Delivery tab. From the Actions button, choose Reopen Delivery>Go. Re-query order to confirm Delivery status is now “Open”.
3. From the Actions button again, choose Unassign Trip>Go
4. Re-query order to confirm the Trip has been removed, then from the Actions button again, choose Confirm Order>Go
5. Enter required information (such as Ship Method, quantity, Inventory Control)
6. Confirm the Order as usual.

B. If you want to cancel the order or order line, the delivery must be closed to do this, so do the following:
1. Query the Order in Shipping Transaction form
2. Navigate to Delivery tab. From the Actions button, choose Reopen Delivery>Go. Re-query order to confirm Delivery status is now “Open”.
3. From the Actions button again, choose Unassign Trip>Go
4. Once the Trip is removed, navigate to Delivery Line tab and remove the Shipped Quantity.
5. Once the Shipped Quantity field is null, go back to Delivery tab>then Actions button, choose Confirm Order>Go
6. Choose the Backorder All.
7. Once the delivery is closed and delivery lines are in backordered status, then navigate to Sales Order and cancel as needed.

C. To close a stop manually please follow steps below: To implement the solution, please execute the following steps:: You will need to manually close the stops

1. On the test instance, please click on the Path by Stop tab and manually close all of
the sequences:
Query order on Shipping Transaction form > Path by Stop tab > Actions > Update
Stop Status >Close
Also be sure to check the ‘Defer Interface’ checkbox on the form.

2. Then submit the Interface Trip Stop – SRS program

3. The line should now progress to ‘Interfaced’.

/by