Topic: Oracle Products

Script to create a Cash Receipt using Oracle API (AR) 0

The following post can be used to create a Cash receipt in Oracle Receivables. The “ar_receipt_api_pub.create_cash” SQL API is used to create cash receipts for the payment received in the form of a check or cash. Cash receipts can be created as identified (with a customer) or as unidentified (without a customer).

Script to create a Receipt Write-off (AR) 0

The following post will detail the steps that are done to create a Receipt Write-Off on an AR Receipt. The PL/SQL API Ar_receipt_api_pub.activity_application is used to apply an adjustment on a Receipt – in this case – a Write-Off on a Receipt.

In this post – before doing a Write-Off on a Receipt – let us create a sample Receipt first. In order to create a Receipt – Navigate to the following: Receivables Superuser -> Receipts -> Receipts. Enter the following mandatory fields like – Receipt Method, Receipt Number, Receipt Amount, Customer details and click Save. Upon creating a Receipt – it shows up as below:

And thus, happy Adjusting Write-offs.

Receipt Write-Off (AR) – Error Messages 0

There are a couple of steps to follow before doing any kind of Adjustment on AR Transactions.

  • In order to make any adjustments like a Receipt Write-Off – Approval Limits have to be defined for the user who is doing the Adjustment. If the Approval Limit is not set, then the Receivables window throws an error to set up the Approval Limit with the error Message: “APP-AR-96983: User Write-off limit does not exist.

In order to correct the above message, perform the following actions: Navigate to the following – Receivables Super User -> Setup -> Transactions -> Approval Limits. In the Approval Limits window – create a record. There exists one record with the combination of a User, Adjustment Type, Currency in the Approval Limits window. Enter a record for the user as below.

  • And there is a system profile option that needs to be setup before any adjustment is done on a receipt. Without setting that option first, the user may face the following error message – “APP-AR-96981: Please set the receipt write-off limits range system option.

In order to correct the above messages, perform the following actions: Navigate to Receivables Super User -> Setup -> System -> System Options -> Miscellaneous Tab. For the Write-off Limits Per Receipt – enter the values as required in the company. After entering the values – the screen looks like below.

These above two steps needs to be completed before creating an adjustment on an AR Receipt like a Receipt Write-Off.

Script to create a Debit Memo (AR) 0

The following script can be used to create a Basic Debit memo. The API that is used to create an AR Invoice is – “ar_invoice_api_pub.create_invoice“. The Cust_trx_type_id decides whether the invoice getting created is a Standard Invoice or a Credit Memo or a Debit memo. Based on this value, the API also checks the sign of the amount on the Line level that is being passed.

Script to Create a Credit Memo (AR) 0

The following script can be used to create a Basic Credit memo. The API that is used to create an AR Invoice is – “ar_invoice_api_pub.create_invoice“. The Cust_trx_type_id decides whether the invoice getting created is a Standard Invoice or a Credit Memo or a Debit memo. Based on this value, the API also checks the sign of the amount on the Line level that is being passed.

Query to derive the 4 C’s 0

The following query can be used to derive the details of the 4 C’s – that is: that have been setup for a particular Ledger.

  • Chart of Accounts
  • Currency
  • Calendar
  • Accounting Convention

Oracle Payables Query 0

Query to identify the various components that a Payables Invoice goes through a P2P process. Below query provides the details like Invoice, Supplier, Supplier Sites, Payment Method codes, terms, Distribution details, Payment schedules, Check details, Internal Bank Payment and Internal Bank details.

Script to Reverse an AR Receipt 0

The following script can be used to reverse an AR Receipt.


TCA Architecture 0

Understanding TCA Architecture in Oracle Applications: 

Oracle Trading Community Architecture (TCA) is a data model that allows us to understand and manage the complex interlinkages between the parties, customers, sites, their uses and relationships. This information is maintained in the TCA Registry, which is the single source of trading community information for Oracle E-Business Suite applications. These applications, as well as TCA itself, provide user interfaces, batch data entry functionality, and other features for you to view, create, and update Registry information.

What is the key difference between a Party and an Account? 

As explained well here in this link, the concept of a Party came into existence when Oracle bought Siebel. The main difference between a Party and a Customer is that a Party can be an entity that may/may not have bought an item/service from the company. But an account is an entity that has made a purchase in some form from the company already.

Consider a scenario where you walk into a TV store to look at the new TV models and their price. There is a fair chance that you may/may not buy a TV that fits your taste and budget and leave. In the scenario where you did not buy, but the store manager takes some basic information from you like your name, address, email id, telephone number and other details like budget and tv model. The manager then enters this information into a database where you will become a Party. This information is used by the company to send communication and marketing emails about new models and prices that may entice a Party to become a Customer. All this activity is maintained in CRM Module where the target is to make the Party a Customer and maintain relationship with the same. (In all the above scenario, you are still a Party).

Now, that you got all those emails and messages, and you got satisfied and you bought a TV from that store. Now, you became a Customer. All the transaction details related to this deal are stored using the account number. Thus, in Oracle, all the details in Accounts Receivables use the Customer account number for report generation purposes.

Can a Party exist without being a Customer?

Yes. A person may just receive information from the company but did not buy any item yet, in this scenario that person/entity is still a Party and not a Customer.

What is the use of a Party without being a Customer? 

The CRM Module main motto is marketing and it is the process of identifying, creating and retaining a Customer. The CRM looks at a Party (that is not a customer yet) as a Potential Customer and tries to reach him/her/entity with marketing emails and information. A Party becomes a Customer and more Customers bring in revenue.

Where is a Party Used? 

When a Party is created, it creates a record in HZ_PARTIES table. This is primarily used by the CRM Module.


How To: Create a Sales Order 1

In the Order to Cash (OTC) process in Oracle Applications, one of the first steps is to Create a Sales Order in Order Management Module.

The usual process of creating a Sales Order involves inserting data into an inbound staging table and then calling a Custom concurrent program. The detailed steps are as below:

  • Insert data into staging tables.
  • Validate data in the staging tables.
  • Insert the validated data into Oracle Order Management Interface Tables.
  • Run the Seeded Oracle “Order Import” Concurrent program to Create Sales Order.

These steps are explained as below:

  • Insert data into staging tables: Basing on the source system, data is inserted into Custom staging tables via a middleware application like Webmethods or SOA.
  • Validating Data in the staging tables: Some of the major validations that need to be done before inserting data into Interface tables are:
    • Item Validation: The item that is being used on the Sales order need to be validated. The item has to exist in the Item Master as well should be assigned to the inventory org from where the Item is being shipped from.
    • Customer Validation: The Customer for which the order is being created for, should exist in the system already.
    • Get BILL_TO Validation: For the Customer that exists, check to see if a valid BILL_TO Site exists.
    • GL_Period Validation: Validate whether the Order data has Open GL period or not.
    • UOM Code Validation: Validate Unit of Measure validation.
    • Operating Unit Validation: Validate the Operating Unit for which the order is being created in.
    • Get Price List: Derive the Price List.
    • Derive Tax Rate Code: Derive the tax rate code based on the tax code passed by the source system.
  • Inserting Data into Interface Table: Upon Validation, Data is inserted into the following Order Interface Tables:
    • Headers Interface: OE_HEADERS_IFACE_ALL.
      • This is the Headers Interface table that captures all the data that goes into the Order Header data (later into OE_ORDER_HEADERS_ALL table). Some of the important columns to be populated are: Operation_code, order_number, orig_sys_document_ref, org_id, Ordered_date, order_type_id, sold_to_org_id, ship_from_org_id, invoice_to_org_id, booked_flag, change_sequence etc.
    • Lines Interface: OE_LINES_IFACE_ALL
      • This is the Lines Interface table that captures all the data that goes onto the Line Level on the Sales order (later onto the OE_ORDER_LINES_ALL table). Some of the important columns are: Org_id, orig_sys_document_ref, inventory_item_id (item), ordered_quantity, order_quantity_uom, unit_list_price, unit_selling_price, fulfillment_set_id etc.
    • Payments Interface: OE_PAYMENTS_IFACE_ALL
      • The Payments Interface table captures data that is shown later on the payments screen on the order. This table has the data that is later populated into OE_PAYMENTS table. Some important columns are: Payment_number, orig_sys_document_ref, orig_sys_payment_ref, payment_type_code, payment_amount, check_number, credit_card_number, credit_card_expiration_date etc.
    • Actions Interface: OE_ACTIONS_IFACE_ALL
      • The Actions interface table is used to load data that will perform any Action to be taken on the order once the order is created. Some uses include Cancelling a line, Applying Hold etc.. Some important columns are: Org_id, Hold_id, Hold_type_code, Operation_Code.
    • Adjustment Interface: OE_PRICE_ADJS_IFACE_ALL
      • The Adjustment interface table holds the data that later goes to the OE_Price_Adjustments table. This table is used to store price adjustments that have been applied to an order or a line.
  • Submit Order Import program: Once data is inserted into the above interface tables, the Seeded Oracle job – Order Import is submitted as a Concurrent program. The code to call the program is as below:
  • Order Interface Errors: During importing the Orders from Interface tables to the Order base tables, if an error occurs due to a setup issue or a data issue, then the corresponding error messages are stored in OE_PROCESSING_MSGS_TL table.
  • Oracle Base Tables: Upon completion of the “Order Import” seeded program, the base tables where order information is stored are:
    • Header Data: OE_ORDER_HEADERS_ALL
    • Lines Data: OE_ORDER_LINES_ALL
    • Payments Data: OE_PAYMENTS
    • Adjustments Data: OE_PRICE_ADJUSTMENTS
    • Holds Data: OE_ORDER_HOLDS_ALL
    • Order Sources: OE_ORDER_SOURCES
    • Sets data – Arrival Set, Ship Set, Fulfillment Set: OE_SETS
    • Transaction Types: OE_TRANSACTION_TYPES_TL.