Query to get Customer Information in R12


Query to get Customer Information in R12

This query is tested in R12.1.3 instance. The query may take few minutes to execute depending upon the size of the data that are present in various HZ Tables.

select  distinct
        hp.party_name "Customer Name",
        hca.account_number,
        hca.status,
        hcsu.location,
        hcsu.site_use_code,
        hcsu.status loc_stat,
        ps.class,
        hcsu.site_use_id,
        hcpc.name profile_name,
        hl.address1,
        hl.address2,
        hl.address3,
        hl.city,
        hl.state,
        hl.postal_code,
        ps.customer_id,
        ps.customer_site_use_id,
        hps.identifying_address_flag,
        ps.trx_date,
        HOU.NAME "Operating Unit"
from    apps.hz_parties hp,
        apps.hz_party_sites hps,
        apps.hz_locations hl,
        apps.hz_cust_accounts hca,
        apps.hz_cust_acct_sites hcas,
        apps.hz_cust_site_uses hcsu,
        apps.hz_customer_profiles hcp,
        apps.hz_cust_profile_classes hcpc,
        apps.ar_payment_schedules_all ps,
        apps.hr_operating_units hou
where   hp.party_id = hca.party_id(+)
        and hp.party_id = hcp.party_id
        and hp.party_id = hps.party_id
        and hps.party_site_id = hcas.party_site_id
        and hps.location_id = hl.location_id
        and hca.cust_account_id = hcas.cust_account_id
        and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
        and hca.cust_account_id = hcp.cust_account_id
        and hca.cust_account_id = ps.customer_id
        and hcp.profile_class_id = hcpc.profile_class_id
        and ps.customer_site_use_id = hcsu.site_use_id
        and hcsu.org_id = hou.organization_id;

AR Invoice Interface


AR Invoice Interface

The main three steps for AR Invoice Interface are:

1] Put the data into your staging tables.

2] Calls your package to validate the data and load into AR Interface tables (RA_INTERFACE_LINES_ALL & RA_INTERFACE_DISTRIBUTIONS_ALL).

3] Then submits a concurrent request for AutoInvoice.

If any errors occur it can be found in ra_interface_errors_all table. The concurrent program has 2 stages. First the Master program fires which intern kicks of the Import Program. Once this is completed data is inserted into the following tables.

1) ra_customer_trx_all (Invoice Header Info)

2) ra_customer_trx_lines_all (Invoice Line Level Info)

3) ra_cust_trx_line_gl_dist_all (Accounting Info. One record for each Account Type is inserted into this… ex. Receivable Revenue Tax Freight etc)

4) ar_payment_schedules_all (All Payment related info)

Validations:

Validation are generally done on the below columns.

  • Batch_source_name
  • Set_of_books_id
  • Orig_sys_batch_name
  • orig_system_bill_customer_ref
  • orig_system_bill_address_ref
  • Line_Type
  • Currency_Code
  • Term_name
  • Transaction_type
  • Interface_line_attribute1-7
  • Account_class
  • Accounting Flexfields segments

1- AR Transaction Type Validation: Check if the Transaction type provided in data file is defined in AR transaction types (RA_CUST_TRX_TYPES_ALL)

2- Transaction Batch Source Validation: Check if the source provided in data file is defined in AR transaction Batch source (RA_BATCH_SOURCES_ALL).

3- Invoice Currency Validation: Check if the currency provided in data file is defined in AR Currency (FND_CURRENCIES).

4- Customer Validation: Check if the Bill to Customer Number, Ship to Customer Number, Bill to Custom Location, Ship to Customer Location provided in the data file is defined in AR Customer (RA_CUSTOMERS).

5- Primary Sales Representative Validation: Sales representative number to be hardcode to “-3” for “No Sales Credit.”

6- Term Name: Check if the Term name provided in the data file is defined in Payment terms (RA_TERMS)

7- Inventory Item Validation: Check if the Item provided in data file is defined in Inventory Items (MTL_SYSTEM_ITEMS).

8- Unit of Measurement validation: Check if the UOM provided is defined in MTL_UNITS_OF_MEASURE Table

9- Invoice Tax Code Validation: Check if the Tax Code provided in data file is defined in AR_VAT_TAX_ALL_B Table.

10- Invoice GL Date Validation: Check if the GL Data of provided invoices is in open period.

For MOAC:

You need to add the below columns and need to do validations if your application supports MOAC.

  • conversion_type
  • conversion_rate
  • conversion_date

Sample Code to run Autoinvoice Master Program:

DECLARE
v_phase 	VARCHAR2(100);
v_dev_phase 	VARCHAR2(100);
v_status 	VARCHAR2(100);
v_dev_status 	VARCHAR2(100);
v_message 	VARCHAR2(100);
v_reqid 	NUMBER(15);
v_pid 		BOOLEAN;
v_user_id  	NUMBER(30);
v_batch_source_id NUMBER;
v_order    	NUMBER;
v_org_id   	NUMBER;
v_resp_id  	number;
v_resp_appl_id 	number;
v_appl_short_name fnd_application.application_short_name%TYPE;

CURSOR c1 IS
select fcr.responsibility_id
,fr.application_id
from fnd_concurrent_requests fcr
,fnd_responsibility fr
where fcr.request_id = '${4}'
and   fcr.responsibility_id = fr.responsibility_id;

CURSOR c2 IS
select fa.application_short_name
from fnd_concurrent_programs fcp, fnd_application fa
where fcp.concurrent_program_name = v_program_short_name
and fcp.application_id = fa.application_id;

CURSOR c_batch_id IS
SELECT 1, batch_source_id, name
FROM apps.ra_batch_sources_all
WHERE name IN (SELECT distinct a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name like '%DEBIT'
AND filename = '${file1}')
UNION
SELECT 2, batch_source_id, name
FROM apps.ra_batch_sources_all
WHERE name IN (SELECT distinct a.batch_source_name
FROM xxfin.xxfin_ar_ol_invoices a
WHERE a.batch_source_name like '%CREDIT'
AND filename = '${file1}')
order by 1;

BEGIN
open c1;
fetch c1 into v_resp_id,v_resp_appl_id;
close c1;

open c2;
fetch c2 into v_appl_short_name;
close c2;

FOR v_batch_data IN c_batch_id LOOP

fnd_global.apps_initialize('${FCP_USERID}',v_resp_id,v_resp_appl_id);

v_reqid := fnd_request.submit_request('AR',
'RAXMTR',
NULL,
to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
FALSE,
'1',
-99,
v_batch_data.batch_source_id,
v_batch_data.name,
to_char(trunc(sysdate),'YYYY/MM/DD HH24:MI:SS'),
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,
'Y',
NULL);
commit;
v_pid := fnd_concurrent.wait_for_request(v_reqid,
3,
0,
v_phase,
v_status,
v_dev_phase,
v_dev_status,
v_message);
END LOOP;
END;

Order to Cash (O2C) Cycle


Order to Cash (O2C) Cycle

Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with the marketing campaign and decides to buy your product and from here starts the O2C cycle.

Step 1] Order Entry:

Customer sends details of order or sales dept brings order from customers. After that the order is entered in Order Management (OM)

Navigation: Order Management Super User> Orders Returns >Sales Orders

Here we need to enter the Customer Details (Customer Name , Number, Contact Ship to and Bill to address etc.), Order type. In the Lines tab we need to enter the Item to be ordered and the quantity required. Here we can also check the availability of the order. Here we can save the order. Once saved the Order Status is changed to ‘Entered’.

Key Tables:

  • OE_ORDER_HEADERS_ALL – All header information is stored here.
  • OE_ORDER_LINES_ALL – All the line information is stored here.

Step 2] Order Book :

When we book the order, we are just confirming and freezing our order.

The final step in the Sales Order Entry process is to Book the order. This signifies that the Order Entry process is complete and that the order is eligible for the next stage in the line flow for this order, as defined by its Transaction Type. Select the Book Order button. The Entry Status of the Order will change to Booked.

After Order Booking:

Order Header: Booked

Order Line : Awaiting Shipping

Shipping Transaction form: Ready to release

Table Level :

  • OE_ORDER_HEADERS_ALL : Flow_Status_code –Booked
  • OE_ORDER_LINES_ALL : Flow_Status_code – Awaiting Shipping
  • WSH_DELIVERY_DETAILS : Released_Status – R ( means – Ready to release)

Step 3] Launch Pick Release :

Pick Release is the process in which the items on the sales order are taken out from inventory.

Navigation: Order Management Super User> Shipping > Release sales Orders > Release sales order

Based On rule: Select the Grouping rule the reaming details will default in Order, Shipping and Inventory tab

Order Tab:

  • Order Number: Select the Order Number. Values for the Order Type and Customer fields of this form default to those for the order number you enter here.
  • Ship Set: Select the Ship Set to be released. The Order Number must be selected first.

Shipping Tab:

  • Auto creates Deliveries : Select Yes in this box to automatically create deliveries for  delivery lines once they are released
  • Release Sequence Rule: Select Rule to specify the order in which the picking lines are released.
  • Auto Pick Confirm –Yes/No

Inventory tab:

  • Warehouse: Select the Warehouse
  • Sub inventory: Select the Sub inventory
  • Pick Slip Grouping Rule: To determine how released picking lines are grouped onto pick slips.
  • Default Stage Sub inventory: Select the Default Stage Sub inventory

Click on Execute Now Button to complete the pick release of the order. Normally pick release SRS program runs in background . Once the program get completed these are the table get affected:

  • OE_ORDER_LINES_ALL (flow_status_code ‘PICKED’ )
  • WSH_DELIVERY_DETAILS (released_status ‘S’ ‘submitted for release’ )
  • mtl_txn_request_headers
  • mtl_txn_request_lines
  • Mtl_material_transactions_temp (link to above tables through move_order_header_id/line_id

Step 4] Pick Confirm the Order:

If Auto Pick Confirm in the above step is set to NO, then the following should be done.                             

Navigation: Inventory Super User > Move Order> Transact Move Order

In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.

  • Items are transferred from salable to staging Sub inventory.
  • mtl_material_transactions
  • mtl_transaction_accounts
  • wsh_delivery_details (released_status ‘Y’ ‘Released’ )
  • wsh_delivery_assignments

Step 5] Ship Confirm the Order:

The Shipping Transaction window provides a centralized workbench that consolidates three major shipping functions: planning, pick releasing, and ship confirming.

Navigation: Order Management Super User>Shipping >Transactions.

Here ship confirm interface program runs in background . Data are removed from wsh_new_deliveries.

  • oe_order_lines_all (flow_status_code ‘shipped’)
  • wsh_delivery_details (released_status ‘C’ ‘Shipped’)
  • mtl_transaction_interface
  • mtl_material_transactions(linked through Transaction source header id)
  • mtl_transaction_accounts

Data are deleted from mtl_demand, mtl_reservations and Item is deducted from mtl_onhand_quantities.

Step 6] Enter Invoices in Receivables:

Run workflow background Process. Workflow Background Process inserts the records in RA_INTERFACE_LINES_ALL with

  • INTERFACE_LINE_CONTEXT     =  ’ORDER ENTRY’
  • INTERFACE_LINE_ATTRIBUTE1 =   Order_number
  • INTERFACE_LINE_ATTRIBUTE3 =    Delivery_id

Then it spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.

Navigation: Order Management >view >Requests

Underlying tables:

  • RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.
  • RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

Step 7] COMPLETE LINE:

In this stage order line level table get updated with Flow status and open flag .

  • oe_order_lines_all (flow_status_code ‘shipped’, open_flag “N”)

Step 8] CLOSE ORDER:

This is last step of Order Processing . In this stage only oe_order_lines_all table get updated .

  • oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)

AutoInvoice in Oracle Apps R12


AutoInvoice in Oracle Apps R12

AutoInvoice is a powerful, flexible tool you can use to import and validate transaction data from other financial systems and create invoices, debit memos, credit memos, and on–account credits in Oracle Receivables. You use a custom feeder program to transfers transaction data from an external system into the AutoInvoice interface tables. AutoInvoice then selects data from the interface tables and creates transaction in Receivables. Receivables rejects transactions with invalid information to ensure the integrity of your data. You can run AutoInvoice together with Customer Interface or separately.

Oracle Receivables can create invoices from the following Oracle applications:  Oracle Order Management, Oracle Projects,  Oracle Service, Oracle Property Manager.
Oracle Receivables can create invoices from the following non-Oracle applications:  Legacy system (for transaction history),  Non-Oracle billing applications, Non-Oracle order entry applications.

Oracle Receivables uses three interface tables for AutoInvoice:

  1. RA_INTERFACE_LINES
  2. RA_INTERFACE_DISTRIBUTIONS
  3. RA_INTERFACE_SALESCREDITS

AutoInvoice transfers transaction data from the above three interface tables into the following Receivables tables:

  • RA_BATCHES_ALL
  • RA_CUSTOMER_TRX _ALL
  • RA_CUSTOMER_TRX_LINES _ALL
  • RA_CUST_TRX_LINE_GL_DIST_ALL
  • RA_CUST_TRX_LINE_SALESREPS_ALL
  • AR_PAYMENT_SCHEDULES_ALL
  • AR_RECEIVABLE_APPLICATIONS_ALL
  • AR_ADJUSTMENTS_ALL

RA_INTERFACE_LINES:

  • This table contains information relating to all transactions to be processed by AutoInvoice.
  • Transactions include invoices, debit memos, credit memos, and on-account credits.
  • Each record contains line, tax, freight, or finance charges information.
  • The Line_Type field identifies the type of information contained in the record.
  • A record can be a parent record: Line, Header Freight, or Charges; or a child record: Tax or linelevel Freight.
  • A child record is linked to the parent record using the Link-To Transaction flexfield.

RA_INTERFACE_DISTRIBUTIONS:

  • This table contains accounting distributions to be used by the transactions defined in RA_INTERFACE_LINES.
  • Accounts defined in this table override any accounts created using AutoAccounting.
  • You can choose to pass some or all account information to AutoInvoice. Any accounts that are not passed will be derived using AutoAccounting.
  • Records in this table are linked to records in the RA_INTERFACE_LINES table using the Transaction flexfield.
  • Not required if AutoAccounting determines GL distributions.

RA_INTERFACE_SALESCREDITS:

  • This table contains all sales credit information for the transactions in the RA_INTERFACE_LINES table.
  • The two tables are linked using the Transaction flexfield.
  • Not required if not tracking sales credit.

What occurs during auto-invoice:

  1. Populates the RA_Interface_Lines, RA_Interface_distribution and RA_Interface_salescredit tables.
  2. Lines are grouped and ordered by the grouping rule and line ordering line. Grouping rules are mandatory and determine how transaction lines are grouped into transactions. Optionally, you can use line-ordering rules to determine the order in which lines are displayed on a transaction.
  3. Tax, freight, commitments and credit memos are linked to transaction line by transaction line, transaction reference and transaction link-to descriptive flex fields. To uniquely identify imported transactions and link the tax, freight, commitments and credit memos, define the Transaction flexfields.
  4. GL date is determined.
  5. GL accounting code combinations are assigned using auto accounting rule.
  6. TAX is determined.
  7. All transactions are batched.
  8. Validated lines are used to create the transaction.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.

Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

Transaction flexfields:

  • Transaction flexfields are descriptive flexfields that AutoInvoice uses to uniquely identify transaction lines.
  • Because they are unique for each transaction line, they can also be used to reference and link to other lines.
  • Receivables lets you determine how you want to build your transaction flexfield structure and what information you want to capture.
  • Define a flexfield for each import source. Specify which one to use during import.

Types of transaction flexfields:

  • Invoice Header (optional): Specifies invoice header information
  • Line (required): Uniquely identifies invoice lines
  • Link-To (optional): Link tax and freight to invoice lines
  • Reference (optional): Links credit memos to transactions

What is inside AutoInvoice?

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

  • Master (RAXMTR)
  • Import (RAXTRX)
  • Purge (RAXDEL)

1] Auto Invoice Master program:

Selects and marks records in the interface tables to  be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

  • Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables.
  • Marks interface records for processing by marking request_id.
  • Submits multiple workers for Parallel Processing by creating instances for request.

2] Auto Invoice Import Program:

Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

  • Workhorse of Auto invoice
  • Validates data
  • Inserts records
  • Deletes interface data
  • Only when system option purge set to ‘Y’

3] Auto Invoice Purge Program:

Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run, and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

AutoInvoice Exception Handling:

Records that fail validation are called ‘Exceptions’

  • Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL.
  • Errors can be corrected in the Exception Handling window.
  • Once corrections are made, Auto invoice must be resubmitted.
  • Records that pass validation get transferred to Receivables tables.

AutoInvoice Exception Handling Windows:

  • Interface Exception window displays exception messages associated with all invalid records.
  • Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors.
  • The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window.
  • Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error.
  • Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button.
  • Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column.

Trading Community Architecture (TCA) in Oracle Apps


Trading Community Architecture (TCA) in Oracle Apps

 

Before TCA:

  • There are multiple customer definitions across the enterprise.
  • It was very difficult to track current and historical information about the customers.
  • There was a lack of support for mixed business.
  • It was quite tough to understand relationships between customers and others (suppliers, partners, competitors).

Customers: More important than anything else!

In any business, Customers and their data are always important. More than that what is important is to understand who your customer interacts with inside and outside the enterprise.

What is Trading Community?

The summation of all entities, inclusive of partners, suppliers, and competitors, that are related to your customers is called a Trading Community.

Trading Community Architecture:

Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.

It’s Main Purpose:

  • Create a central repository for the entire E-Business Suite to store information relating to all members of a trading community versus separate tables for each member-Prospects, Customers, Contacts, Employees, Partners, Distributors, Suppliers, Banks, etc.
  • Record complex business relationships between Trading Community entities (including 3rd party relationships).
  • Support all business models, industries, and geographies.

TCA Data Model Components:

1] Party:

It represents any entity that can enter into business relationships with your organization – Organization, Person, or Group.

  • Person – A unique individual (dead or alive) of interest to the user.
  • Organization  – A legal entity recognized by some government authority.
  • Group  – A combination of two or more people, organizations or groups.

2] Party Relationship:

It is a binary relationship between two parties such as a partnership.

  • Has a Role – Specifies the nature of the relationship between parties (e.g., member of, contact at, married to).
  • Indicates the Nature of the relationship – hierarchy or matrix.
  • Indicates the Direction of the relationship – superior – subordinate.
  • Can become a Party – a Relationship becomes a party in itself.

The relationship model enables you to:

  • Understand the complex relationships among members of your trading community
  • Use this information to make better business decisions

3] Location:

A Location is a point in geographical space described by a street address. In previous releases of Oracle, there was a risk of some data redundancy if more than one customer shared the same site or location. The new model eliminates this redundancy.

  • Any number of location types can be defined. (e.g., bill-to, ship-to, mail-to).
  • There is no duplication of an address.
  • It is possible to maintain Customer History per address.
  • It is also possible to maintain Important Install Base info.

4] Party Site:

It links a Party with a Location

  • Describes the usage of that Location for the Party  (e.g., mailing address, billing address, home address, etc.).
  • Allows Parties to be associated to one or more Locations and any one Location to be associated with Parties.

5] Contact:

A Contact is a person in the context of an organization, modeled as a relationship between an organization and a person or between two people, (this can be either a party contact or an account contact).

6] Contact Point:

A Contact Point is a means of contacting a party, for example, a phone number, e-mail address, or fax number.

This can be applied to:

  • A Party (person, organization, group or relationship)
  • A Site or Location
  • A Party at a Site or Location

An entity may have one or more Contact Points.

7] Customer Account:

A Customer Account represents the business (selling) relationship that a company deploying Oracle Applications has with a party.

  • Stores details about the Financial relationship between a Party and your business.
  • A Party may have one or more Customer Accounts.

8] Customer Account Site:

A Customer Account Site is a party site that is used by a customer account, for example, for billing or shipping purposes.

9] Customer Account Contacts:

A party contact that is used as a means of contacting the customer regarding his/her account.

Parties vs. Accounts

  • From an application perspective, one of the most important things to understand about the TCA model is that the concept of “customer” is separated into two layers: The Party layer and the Account layer. 
  • When CRM applications refer to “Customer” they are referring to the Party Layer.
  • On the other hand, when ERP applications refer to “Customer” they are referring to the Account Layer. 

New Trading Entities in R12

Below are the new entities that are merged in TCA architecture in R12.

  • Banks & Bank Branches
  • Suppliers
  • Legal Entity

 Know More: 

What Happened to My Customers? (Trading Community Architecture) by Jeannie Dobney

Adding and Updating Customer Accounts in R12


Adding and Updating Customer Accounts in R12

 

Use the Customer Overview page in R12 to manage details of your existing customers. 

This page has five subtabs:

  • Accounts.
  • Profile
  • Communication
  • Party Relationships
  • Tax Profile

 

Accounts:

Use the Accounts subtab of the Customer Overview page to view, add, and update the accounts of existing customers.

  • view and update an account:
  • view and update an account site
  • create an account
  • create an account site

Customer Profiles:

Use the Profile subtab of the Customer Overview page to add and update the profiles of existing customers.

Tax Registration Number

  • The customer’s unique taxpayer registration number, also known as the VAT number.
  • Oracle Receivables prints this number on customer invoices.
  • Receivables provides country-specific validation of the tax registration number.

Credit Classification

  • Displays the credit classification for a particular profile class.

Credit Analyst

  • Indicates who is responsible for monitoring the creditworthiness of the account and for assisting in the resolution of credit-related issues.

Review Cycle

  • Specifies how often to review the credit status of the customer account. For example, you can specify that the creditworthiness of the account is reviewed each month.

Customer Communication Information:

Use the Communication subtab of the Customer Overview page to enter and update contact information, such as phone numbers, e-mail addresses, and URLs, of existing customers.

Phone Numbers:

  • FAX
  • MOBILE
  • PAGER
  • TELEPHONE
  • VOICEMAIL

Email Address:

 URLs:

Party Relationships:

Use the Party Relationship subtab of the Customer Overview page to define, view, and update relationships among existing customers (parties), using predefined relationship types and roles.

Note: Relationship types and roles are defined using Oracle Trading Community Architecture Relationship Manager.

Relationship Role: Describes the role that an entity plays in a relationship.

Customer Tax Profiles:

Use the Tax Profile subtab of the Customer Overview page to set up, view, and update tax profiles for your customers.

AR Tables:A Diagrammatic Relation


AR Tables:A Diagrammatic Relation

 

A Diagrammatic Relation between AR Tables

Click in the photo to have a better view.

List of Receipts API in Oracle Receivables


List of Receipts API in Oracle Receivables

Below is the list of some of the Receipt API’s in Oracle Receivables. Receipt APIs provide an extension to existing functionality for creating and manipulating receipts through standard AR Receipts forms and lockboxes.

AR_RECEIPT_API_PUB is the main package that has several procedures to perform different actions.

1] AR_RECEIPT_API_PUB.CREATE_CASH

Use this procedure to create a single cash receipt for payment received in the form of a check or cash.

2] AR_RECEIPT_API_PUB.APPLY & AR_RECEIPT_API_PUB.APPLY_IN_DETAIL

Use these procedures to apply the cash receipts from a customer to an invoice, debit memo, or other debit item.

3] AR_RECEIPT_API_PUB.UNAPPLY

Use this procedure to unapply a cash receipt application against a specified installment of a debit item or payment schedule ID.

4] AR_RECEIPT_API_PUB.CREATE_AND_APPLY 

Use this procedure  to create a cash receipt and apply it to a specified installment of a debit item.

5] AR_RECEIPT_API_PUB.REVERSE

Use this procedure to reverse cash and miscellaneous receipts.

6] AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT

Use this procedure to apply a cash receipt on account.

7] AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT

Use this procedure to unapply an on-account application of a specified cash receipt.

8] AR_RECEIPT_API_PUB.ACTIVITY_APPLICATION

Use this procedure to create an activity application on a cash receipt, including Short Term  Debit (STD) and Receipt Write-off applications.

9] AR_RECEIPT_API_PUB. ACTIVITY_UNAPPLICATION

Use this procedure to create a reversal of an activity application on a cash receipt including Short Term Debt and Receipt write-off.

10] AR_RECEIPT_API_PUB.CREATE_MISC

Use this procedure to create a miscellaneous receipt.

11] AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT

Use this procedure to apply a cash receipt to another open receipt. Open receipts include unapplied cash, on-account cash, and claim investigation applications.

12] AR_RECEIPT_API_PUB.UNAPPLY_OPEN_RECEIPT

Use this procedure to reverse a payment netting application on a  cash receipt.

HZ tables in Oracle Receivables


HZ(TCA) tables in Oracle Receivables

This article describes few important HZ tables in AR and their relationships with each other.

HZ_PARTIES:

The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.

Few Important Columns are

  • PARTY_ID: Party identifier
  • PARTY_NUMBER: Unique identification number for this party
  • PARTY_NAME: Name of the party
  • PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:

The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.

Few Important Columns are

  • PARTY_SITE_ID: Party site identifier.
  • PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
  • LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
  • PARTY_SITE_NUMBER: Party site number.
  • PARTY_SITE_NAME: User-defined name for the site.
  • ADDRESSEE: Addressee information.

HZ_LOCATIONS:

The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.

Few Important Columns are

  • LOCATION_ID: Unique identifier for this location
  • COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
  • ADDRESS1: First line for address
  • ADDRESS2: Second line for address
  • ADDRESS3: Third line for address
  • ADDRESS4: Fourth line for address
  • CITY: City
  • POSTAL_CODE: Postal Code
  • STATE: State
  • ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:

The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.

Few Important Columns are

  • CUST_ACCOUNT_ID: Customer account identifier
  • PARTY_ID: A foreign key to the HZ_PARTY table.
  • ACCOUNT_NUMBER: Account Number
  • CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
  • CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

Few Important Columns are

  • CUST_ACCT_SITE_ID: Customer site identifier
  • CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
  • PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
  • BILL_TO_FLAG: Indicates if this is a Bill-To site.
  • SHIP_TO_FLAG: Indicates if this is a Ship-To site.
  • MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:

The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.

Few Important Columns are

  • SITE_USE_ID: Site use identifier
  • CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
  • SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
  • PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:

The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

Few Important Columns are

  • CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
  • CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
  • STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:

The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.

HZ_PARTY_RELATIONSHIPS:

The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.

Relationship between the tables