R12 : Trading Community Architecture(TCA)

Trading Community Architecture (TCA) is a structure which was based out of R11 Customer Model designed to support complex trading relationships to cater additional need which further extended in R12 with Supplier and Bank. So, TCA is a data model that allows you to manage complex information about the parties, or customers or suppliers or bank who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them.

What is Trading Community Architecture (TCA)?

What is TCA, the Trading Community Architecture? Is TCA an Oracle Applications module? Is it functionality within an Oracle module? These are few common question, and there are often many answers given.

The TCA is a data model that supports the entry and management of entities that you interact with. So lets revisit the concept.

Trading Community Architecture is a Very flexible, very robust model which defines the components involve in trading within in E-business Suite.

The implementation of technology and applications to allow users to create and maintain relationships among entities

The universal data schema for customers, prospects, suppliers, distributors, resellers, consortiums, bank across all Oracle EBS applications

TCA not only allows for the tracking of relationships between the implementing organization and its trading partners, but also tracks relationships between the trading partners themselves.

You should also note, TCA is neither an Oracle Applications module nor requires separate license.

If you see TCA guide, you can find these are the key features of TCA

• Provides a foundation for a single source for customer information.
• Ability to represent all business entities as a “Party” (organizations, people, groups, relationships) and to handle them the same way. This approach provides flexibility to accommodate all B2B, B2C and hybrid models in the same repository.
• Many-to-many relationships between Parties and Locations, that allows for less duplication and easier updating.
• Capability for advanced relationship modeling between entities within the trading community. Any party can figure in any number of Party Relationships even within matrix hierarchies (relationship networks).
• Ability to setup and maintain any number of party classifications which can be used for reporting and assignment purposes.
• Extensible data model to enable various business data requirements.
• In reality , three entities Drive in the TCA model , which are Party, Account, and Relationships.

 TCA Terminologies

• Party
  • The concept of ‘Party’ enables the Customer Model to treat all business entities equally, regardless of type. It easily handles B2B, B2C.
    Parties of type ‘Group’ allow for the grouping of any number of other parties into a single entity which enable modeling of households and buying consortiums.
  • Parties of type ‘Relationship’ allow for the relationship between two parties to be viewed as a party in its own right
  • Party - A Party is an entity that can enter into a business relationship and can be of four types.
    • Person - A unique individual (dead or alive) of interest to the owner of the software.
    • Organization - A legal entity recognized by some government authority.
    • Group - a combination of two or more people, organizations or groups of created for the use of the owner of the software.
    • Relationship - The association between an individual person and an organization. Usually a contact at an organization or group.

Fig 1: TCA Logical Diagram

• Account
  • Account - Is a financial roll-up point to track the monitory portion of a customer’s purchases and payments. Stores details about a customer relationship between a Party and your business.
    • This Represents selling-buying relationship such as billing and shipping events
    • Accounts required for a transaction
    • A account cannot exist without a party
  • A Party may have one or more Customer Accounts
    • Account Role - The relationship that a Party has in regard to controlling or using an account.
    • Customer Account Site is a Party Site that is used within the context of a Customer Account (e.g., for billing or shipping purposes).
    • A Customer Account Contact is a Party Contact that is used in the context of a Customer Account.
• Customer
  A customer account represents the business relationship that a party can enter in to with another party. The account has information about the terms and conditions of doing business with the party. For example, you could open a commercial account for purchases to be made by Vision Distribution for its internal use and a reseller account for purchases made by Vision Distribution for sales of your products to end-users .

You can also define contact people, bank accounts, payment methods, telephone numbers, and relationships for each customer account.

You can also maintain multiple customer accounts for a customer that transacts business with more than one line of business in your organization. You maintain separate customer profiles, addresses, and contacts for each customer account.

A party site is the location where a particular party is physically located. Every party has only one identifying address, but a party can have multiple party sites.

A customer address is a party site used in the context of a customer account for billing, shipping, or other purposes.

A contact communicates for or acts on behalf of a party or customer account. A contact can exist for a customer at the account or address level. A person usually acts as a contact for an organization, but can also be a contact for another person. For example, an administrative assistant could be the contact for an executive.

Old Model vs New Customer Model

Fig 2; Customer old model and TCA model

• Locations/site :A Location is a point in geographical space described by an address. A party site is a location.
• Party Relationship :Any relationship between two parties of the above type (person and organization) that needs to be stored as its. own record. Data that directly corresponds to this relationship (contact info etc.) is stored as well. Relationships are stored in the HZ_PARTY_RELATIONSHIPS table.

Factors which you can consider for TCA entities

• Business requirement including your reporting
• System/application requirement
• Country or Organization Legal Requirement
• Global Consideration
• Process standardization

TCA Setup Considerations

When you are doing TCA customer Modeling, keep these things in mind;

• Party be any real Person or Organization.
• Party sites are locations for Party or Organization.
• Relationships are generally used to construct hierarchical structure of Organizations.
• Party becomes a Customer/Account, once a selling relationship is established.
• An account should typically have at least one active ‘bill_to’ site. It helps for accounting and reporting purposes.
• When creating Parties, what all party sites can be or should be created as Parties.
• Generally, if you want to see activities for site level separately from your parent level party, you should create that Site as a separate Party/Entity.
• An account is a separate entity. Create account only where you have selling relationship i.e. only for customers. It identifies selling attributes e.g.payment terms, shipping and billing preferences etc. of the relationship.
• You can have multiple accounts, for each relationship between external party and your business entity. It enables you to have multiple
  sets of selling attributes e.g. payment terms etc.
• You can build relationship between accounts and have one account to pay for another.
• If transaction needs to be segregated within a party to perform granular analysis based on selling or business relation,separate accounts with a party should be created.

TCA Integration with Other Oracle Products

This is how TCA data is tighten with other Oracle products.

 TCA Technical Tables

• TCA - Customer : Here are Technical details for 11i/R12 customer in TCA. You can also refer old post for customer model.

• TCA - Suppliers

Here are Technical details for R12 Supplier in TCA. You can also refer old post for more details.

• TCA - Bank

How to work with Date Functions?? How to add days in current date?

How to work with Date Functions??

How to add days in current date?

 select sysdate + 3 from dual;

----------------------------------------------------

 select '07-dec-04' + 3 from dual; -- Gives error

select to_date('07-dec-04')  + 3 from dual;

select to_date('07-dec-04') - to_date('13-dec-04') from dual; -- Negative value

---------------------------------------------------------------

date + number/24 --> Adds a number of hours to a date

Suppose the time of sysdate is 9:30 pm and 5 hours are added in it then it will be2:30 am of the next date. So it shows the next date.

select sysdate + 4/24 from dual;

---------------------------------------------------

How to do Months_between(d1,d2)

If d1 > d2 then +ve else -ve

Select MONTHS_BETWEEN(TO_DATE('01-AUG-02'),to_date('01-dec-05')) from dual;

select months_between(sysdate,hiredate) from emp

select round(months_between(sysdate,hiredate),0) As "Months Between" from emp

 Assignment - Find the years between using months_between

select months_between('13-dec-04','24-jul-04') from dual

select round(months_between('13-dec-04','24-jul-04'),0) from dual

Add_months(date,n) [n cand be +ve or -ve]

select add_months('4-dec-04',3) from dual; Gives 04-Mar-05

select add_months('4-dec-04',-3) from dual; Gives 04-Sep-04

-----------------------------------------------------------------------------------------------

Next_Day(date,'char') -- Give the date of the next week for the specified day in the char parameter.

select next_day('04-Dec-04','Wed') from dual; Gives the date coming on next Wednesday after 4-Dec-04. The answer is 08-Dec-04

OR

Instead of character parameter a numeric value can be also given. It has Sunday as 1 Monday as 2  ...Saturday as 7

select next_day('04-Dec-04', 4) from dual. The answer is 08-Dec-04

 Note: The number cannot be negative.

----------------------------------------------------------------------

Last_Day(date) - Returns the last date of the month.

select last_day(sysdate) from dual;

--------------------------------------------------------------------------

Round(Date,fmt)

If month is the fmt, then rounds the date to the months extremities.If date is between 1 and 15 then it rounds the date to the first of the same month. If it is 16 to end then rounds to first date of the next date.

E.g select round(to_date('04-dec-04'),'Month') from dual  ----> 01-DEC-04

select round(to_date('15-dec-04'),'Month') from dual; ----> 01-DEC-04

select round(to_date('16-dec-04'),'Month') from dual; ---->  01-JAN-05

select round(to_date('02-Feb-04'), 'Year') from dual; --> 01-JAN-04

select round(to_date('30-Jun-04'), 'Year') from dual;  --> 01-JAN-04

 select round(to_date('1-Jul-04'), 'Year') from dual; --> 01-JAN-05

Trunc

select trunc(to_date('23-dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('07-Dec-04'),'Month') from dual;  Gives 01-Dec-04

select trunc(to_date('23-Dec-04'),'Year') from dual;    Gives 01-Jan-04

select trunc(to_date('02-Feb-04'),'Year') from dual;     Gives 01-Jan-04

To_Char

To see all the records of employees joined after 1982

select * from emp

where to_char(hiredate,'yy') >= 82;

Or  where to_char(hiredate,'yyyy’) >= 1982

To see all the records of employees who have joined from October of any month.

select * from emp

where to_char(hiredate,'mm') >= 10; 

(Note à Only number 1 to 12 can be used for comparison)

To see all the records of employees who have joined from 21st date.

select * from emp

where to_char(hiredate,'dd') >= 21;

To see all the records of employees who have joined from April-81

select * from emp

where to_char(hiredate,'mm') > 04 and to_char(hiredate,'yy') >= 81

To see all the records of employees who have joined after 28-Sep-81

select * from emp

where hiredate >  '28-Sep-81'

DATE FORMAT MODEL

YEARS

select to_char(hiredate,'YYYY') || '-' || to_char(hiredate,'YEAR') FROM EMP;

MONTHS

select to_char(hiredate,'MONTH') || '-' || to_char(hiredate,'MM')

 || '-' || TO_CHAR(hiredate,'MON') FROM EMP;

DAYS

select to_char(hiredate,'DAY') || '-' || to_char(hiredate,'DD')

 || '-' || TO_CHAR(hiredate,'DY') FROM EMP;

ADVANCED FORMATS -

CENTURY

SELECT TO_CHAR(SYSDATE,’SCC’) FROM DUAL;

select to_char(HIREDATE,'SCC') || '-' || TO_CHAR(HIREDATE,'YY') "CEN-YR"

FROM EMP;

SYEAR  The spelled out year.

select to_char(HIREDATE,'SYEAR') "YEAR"

FROM EMP

Q  To get the quarter of the year (1,2,3 and 4)

select to_char(HIREDATE,'Q') "Quarter"

FROM EMP;

 RM   Roman numeral month

select to_char(HIREDATE,'RM')"MONTH"

FROM EMP;

J Julian day - The number of days since 31Dec 4713 B.C.

select to_char(HIREDATE,'J')"JulianDay"

FROM EMP;

TIME

select to_char(sysdate,'hh:mi:ss') FROM DUAL;

To show AM or PM

select to_char(sysdate,'hh:mi:ss')|| '-' || to_char(sysdate,'PM') from dual;

SSSS Seconds past midnight (0-86399)  [ (60secs * 60 min * 24) - 1]

select to_char(sysdate,'ssss') from dual;

Suffixes -

select to_char(sysdate,'ddth') from dual;

select to_char(sysdate,'ddsp') from dual;

select to_char(sysdate,'ddspth') from dual;

 RR DATE FORMAT -

create table rryy

(name varchar2(10),

 dob date);

NAME

DOB

A

24-SEP-02

B

12-DEC-67

C

14-JAN-59

D

05-APR-04

  A AND D ARE IN 2000 CENTURY.

  B AND C ARE IN 1900 CENTURY.

To see the records who have dob of 19th century.

Suppose yy format is used -

SELECT * FROM RRYY

WHERE TO_CHAR(DOB,'YY') < 99.

It assumes all the years are in the 20th  century (2099). So all 4 records will come.

So to show only ,B and C records  --

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90',  'DD-MON-RR');

Here 90 it understands as 1990.

If  YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB < TO_DATE('01-JAN-90', 'DD-MON-YY');

Here 90 it understands as 2090. So again all 4 records will come.

Similarly ---

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-RR');

Due to RR 90 will be 1990. So it shows all the records where DOB is above 1990.

A and D records will come.

If YY is given instead of RR -

SELECT * FROM RRYY

WHERE DOB > TO_DATE('01-JAN-90',  'DD-MON-YY');

Due to YY it takes 90 as 2090. It will not find any record above 2090.

HZ tables in Oracle Receivables

HZ tables in Oracle Receivables

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

Various Queries on Customer Data (HZ Tables)

Various Queries on Customer Data (HZ Tables)
 VARIOUS QUERIES ON CUSTOMER DATA (HZ TABLES)

Product: TCA / Oracle Receivables
Overview
There have been many instances where I have been asked by the Business Users to provide Customer Listing reports based on different parameters. The different requests typically are as follows
1. Customer listing with all Sites for a specific Org
2. A listing of only those Customer with whom we have had transactions in the last ‘x’ years
3. A listing of all Customer Sites that do not have any Business purpose associated with it
4. Customer Listing By Collector
5. Customer Listing along with Profile Class names and Collector names
6. And so on …
We have the Standard Customer Listing Reports in the application. However these reports are not org stripped because the HZ_CUST_ACCOUNTS data is not org stripped plus the standard reports could not be used for all different flavors of the Business requests. In these cases, I used the following queries
Queries
Customer listing with all Sites for a specific Org
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
substrb(party.party_name,1,50) Customer_Name ,
cust.account_number Customer_Number ,
substrb(look.meaning, 1, 8) Site_Use ,
acct_site.cust_acct_site_id Address_Id,
substrb(loc.address1,1,30) Address_Line_1,
substrb(loc.city,1,15) City ,
substrb(loc.state,1,2) State ,
substrb(loc.postal_code,1,10) Zip_Code ,
cust.status Cust_Status
FROM
ar_lookups look,
ar_lookups look_status,
hz_cust_accounts cust,
hz_parties party,
hz_cust_site_uses site_uses,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
ORDER BY
party.party_name ;
Customer listing with only Identifying Addresses for a specific Org
The IDENTIFYING_ADDRESS_FLAG column of HZ_PARTY_SITES table indicates if the Address is Identifying Address or not. (Values = Y or N).
(The Select and the From clause is the same as above query)
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND party_site.identifying_address_flag = ‘Y’
ORDER BY
party.party_name ;
Listing of all Customer Sites that do not have any Business Purpose
The SITE_USE_CODE of the HZ_CUST_SITE_USES_ALL table stores the ‘Business Purpose’ code of the site. If we need a listing of Customer sites that do not have any Business Purpose, we add the where clause of ‘site_uses.site_use_code is NULL ‘ to the query. This listing was used for data cleanup purpose.
(The Select and the From clause is the same as above query)

execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND site_uses.site_use_code is NULL
ORDER BY
party.party_name ;
Listing of all Customer with Primary Bill To Address
The address with a ‘Bill To’ business purpose has the SITE_USE_CODE column of the HZ_CUST_SITE_USES_ALL table as ‘BILL_TO’
(The Select and the From clause is the same as above query)
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND site_uses.site_use_code = 'BILL_TO'
AND site_uses.primary_flag = 'Y'
AND site_uses.status = 'A'
ORDER BY
party.party_name ;

Listing of all Customer with Bill To Address with whom we had transaction in the last 1 year
The BILL_TO_SITE_USE_ID of the RA_CUSTOMER_TRX_ALL table stores the SITE_USE_ID of HZ_CUST_SITE_USES_ALL table.
(The Select and the From clause is the same as above query)
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
. . .
FROM
. . .
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND SITE_USES.site_use_id in
(SELECT distinct trx.bill_to_site_use_id from ra_customer_trx trx where trx.creation_date > sysdate - 365)
ORDER BY
party.party_name ;

Listing of Customer’s with Profile Class Name, Collector Name, Bill To Address
execute dbms_application_info.set_client_info('Org_id') ;
SELECT
substrb(party.party_name,1,50) Customer_Name ,
cust.account_number Customer_Number ,
pc.name Profile_Class_Name ,
coll.name Collector_Name ,
substrb(look.meaning, 1, 8) Site_Use ,
acct_site.cust_acct_site_id Address_Id,
substrb(loc.address1,1,30) Address_Line_1,
substrb(loc.city,1,15) City ,
substrb(loc.state,1,2) State ,
substrb(loc.postal_code,1,10) Zip_Code ,
cust.status Cust_Status
FROM
ar_lookups look,
ar_lookups look_status,
hz_cust_accounts cust,
hz_parties party,
hz_cust_site_uses site_uses,
hz_cust_acct_sites acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_customer_profiles prof,
hz_cust_profile_classes pc ,
ar_collectors coll
WHERE
cust.cust_account_id = acct_site.cust_account_id
AND cust.party_id = party.party_id
AND acct_site.party_site_id = party_site.party_site_id(+)
AND loc.location_id(+) = party_site.location_id
AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id(+)
AND look.lookup_type(+) = 'SITE_USE_CODE'
AND look.lookup_code(+) = site_uses.site_use_code
AND look_status.lookup_type(+) = 'CODE_STATUS'
AND look_status.lookup_code(+) = nvl(cust.status, 'A')
AND cust.cust_account_id = prof.cust_account_id (+)
AND prof.collector_id = coll.collector_id(+)
AND prof.profile_class_id = pc.profile_class_id
AND prof.site_use_id is NULL
AND SITE_USES.site_use_id in
(SELECT distinct trx.bill_to_site_use_id from ra_customer_trx trx where trx.creation_date > sysdate - 60)
ORDER BY
party.party_name ;
Summary
For me these queries were very handy whenever I had any Customer Listing request from the Users. A little tweak here and there to these queries would fetch me all the data I needed. I always referred to the TRM to look for additional columns of these tables if there was any need to use them. A handy SQL query for a Consultant helps!

=========================================================================

Queries to select the source data
1. Query to select the response note from customer calls. These notes are at the account level and hence have no reference to the transaction
SELECT acc_customer_id "CUST_ACCOUNT_ID"
, ano_text note_text
, rcu_customer_number account_number
, 'RESPONSE' note_type
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
FROM ar_customer_calls_v
WHERE trunc(creation_date) >= trunc(&p_date_from)
AND trunc(creation_date) <= trunc(&p_date_to) ;
2. Query to select the customer call topic notes. If the customer_trx_id is NULL, it means that the note is at an account level. Else the note is at a transaction level.
SELECT cct_customer_trx_id customer_trx_id
, cct_customer_id cust_account_id
, ano_text note_text
, 'CALL_NOTE' note_type
, last_updated_by
, last_update_date
, last_update_login
, created_by
, creation_date
FROM ar_customer_call_topics_v
WHERE trunc(creation_date) >= trunc(&p_date_from)
AND trunc(creation_date) <= trunc(&p_date_to) ;
Check if Source data is already migrated
1. Query for checking if the account level note is already migrated
SELECT DISTINCT COUNT (*)
INTO x_count
FROM jtf_notes_b jtfn
, jtf_notes_tl jtfn_tl
WHERE
jtfn.source_object_code = 'IEX_ACCOUNT'
AND jtfn.source_object_id = note_rec.cust_acct_id
http://oracle.anilrpatil.com Page 3
AND jtfn_tl.jtf_note_id = jtfn.jtf_note_id
AND jtfn_tl.source_lang = 'US'
AND UPPER(jtfn_tl.notes) = UPPER(note_rec.note_text)
AND jtfn.creation_date = note_rec.creation_date
AND jtfn.created_by = note_rec.created_by
AND jtfn.last_update_date = note_rec.last_update_date
AND jtfn.last_updated_by = note_rec.last_updated_by;
In the above query, note_rec is a record_type consisting the source data. If the x_count variable is not zero, it means that the note is already migrated.
2. Query for checking if the invoice level note is already migrated
SELECT DISTINCT COUNT (*)
INTO x_count
FROM jtf_notes_b jtfn
, jtf_notes_tl jtfn_tl
WHERE
jtfn.source_object_code = 'IEX_INVOICES'
AND jtfn.source_object_id = note_rec.payment_schedule_id
AND jtfn_tl.jtf_note_id = jtfn.jtf_note_id
AND jtfn_tl.source_lang = 'US'
AND UPPER(jtfn_tl.notes) = UPPER(note_rec.note_text)
AND jtfn.creation_date = note_rec.creation_date
AND jtfn.created_by = note_rec.created_by
AND jtfn.last_update_date = note_rec.last_update_date
AND jtfn.last_updated_by = note_rec.last_updated_by;
In the above query, note_rec is a record_type consisting the source data. If the x_count variable is not zero, it means that the note is already migrated.
JTF_NOTES_PUB API
1) Account Level Notes – The Account level notes are migrated using the following code
Declare
l_notes_detail CLOB;
l_note_type VARCHAR(30) := 'IEX_HIST';
l_note_status VARCHAR2 (1) := 'I';
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_note_id NUMBER ;
l_msg_index_out NUMBER;
BEGIN
jtf_notes_pub.create_note
(p_api_version => 1.0,
p_init_msg_list => 'T',
p_commit => 'T',
p_jtf_note_id => NULL,
p_validation_level => 100,
p_source_object_id => note_rec.cust_account_id,
http://oracle.anilrpatil.com Page 4
p_source_object_code => 'IEX_ACCOUNT',
p_notes => note_rec.note_text ,
p_notes_detail => l_notes_detail,
p_entered_by => note_rec.created_by,
p_entered_date => note_rec.creation_date,
p_last_update_date => note_rec.last_update_date,
p_last_updated_by => note_rec.last_updated_by,
p_creation_date => note_rec.creation_date,
p_created_by => note_rec.created_by,
p_last_update_login => fnd_global.login_id,
p_note_type => l_note_type,
p_note_status => l_note_status,
x_jtf_note_id => l_note_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF (l_return_status <> 'S')
THEN
fnd_file.put_line(fnd_file.LOG,'l_return_status <> S ');
IF (fnd_msg_pub.count_msg > 0)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get
(p_msg_index => i,
p_encoded => 'F',
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out
);
DBMS_OUTPUT.put_line ('Error:' || l_msg_data);
fnd_file.put_line(fnd_file.LOG,'ERROR :' || l_msg_data);
END LOOP;
END IF;
END IF;
COMMIT;
END ;
In the above query, note_rec is a record_type consisting the source date.
The value of l_note_type should be a valid lookup_code for lookup_type JTF_NOTE_TYPE
SELECT LOOKUP_CODE,MEANING,DESCRIPTION,
TAG,START_DATE_ACTIVE,END_DATE_ACTIVE,ENABLED_FLAG,
LOOKUP_TYPE
FROM FND_LOOKUP_VALUES_VL WHERE (nvl('', territory_code) = territory_code or territory_code is null)
AND lookup_type = 'JTF_NOTE_TYPE'
order by LOOKUP_CODE ;
2) Invoice Level Notes – For migrating the Invoice level notes, the code remains the same except the value passed to the parameters p_source_object_id and p_source_object_code . The values that needs to be passed to these parameters are
http://oracle.anilrpatil.com Page 5
p_source_object_id => note_rec.payment_schedule_id ,
p_source_object_code => 'IEX_INVOICES’
Summary
This document details one approach for migrating Call Notes from AR to Advanced Collections. These notes can then be viewed in the Notes Tab of the Collections Form.
References
Oracle Common Applications Components – API Reference Guide
Oracle Advanced Collections Implementation Guide
Oracle Advanced Collections User Guide

Oracle Pending Transaction Queries

Oracle Pending Transaction Queries
Unprocessed Material:
SELECT *
  FROM mtl_material_transactions_temp
 WHERE     organization_id = :p_org_id
       AND transaction_date < TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND NVL (transaction_status, 0) <> 2 -- 2 indicates a save-only status;

Uncosted Material/WSM:
SELECT  /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */     *
  FROM mtl_material_transactions MMT
 WHERE     organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND costed_flag IS NOT NULL;

Pending WIP Costing:
SELECT *
  FROM wip_cost_txn_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending WSM Interface:
SELECT *
  FROM wsm_split_merge_txn_interface
 WHERE     organization_id = :p_org_id
       AND process_status <> wip_constants.completed
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending LCM Interface:
/* Support for Landed Cost Management: Pending landed cost adjustment transactions */
SELECT *
  FROM cst_lc_adj_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr'); --period close date

Pending Receiving:
SELECT *
  FROM rcv_transactions_interface
 WHERE     to_organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND destination_type_code IN ('INVENTORY', 'SHOP FLOOR');

Pending Material:
SELECT *
  FROM mtl_transactions_interface
 WHERE     organization_id = 102
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND process_flag <> 9;

Pending Shop Floor Move:
SELECT *
  FROM wip_move_txn_interface
 WHERE organization_id = :p_org_id
       AND transaction_date <= TO_DATE ('31.12.2012', 'dd.mm.rrrr');

Incomplete Work Orders : If the maintenance is enabled in mtl parameters.eam_enabled_flag = ‘Y’

SELECT *
  FROM wip_discrete_jobs WDJ, wip_entities WE
 WHERE WDJ.organization_id = :p_org_id
       AND WDJ.scheduled_completion_date <=TO_DATE ('31.12.2012', 'dd.mm.rrrr') --period close date
       AND WDJ.status_type = 3                                     -- Released
       AND WDJ.wip_entity_id = WE.wip_entity_id
       AND WDJ.organization_id = WE.organization_id
       AND WE.entity_type = 6                       -- Maintenance Work Order;

==============================================================================================================
Unprocessed Material:
SELECT COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>' AND
       TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
  AND NVL(TRANSACTION_STATUS, 0) <> 2

--Uncosted Material Transactions

SELECT /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) */
 COUNT(*)
  FROM MTL_MATERIAL_TRANSACTIONS MMT
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND COSTED_FLAG IS NOT NULL

--Pending WIP Costing Transactions

SELECT COUNT(*)
  FROM WIP_COST_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Uncosted WSM Transactions
   
SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TRANSACTIONS
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Uncosted WSM Interface

SELECT COUNT(*)
  FROM WSM_SPLIT_MERGE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Pending Receiving Transactions

SELECT COUNT(*)
  FROM RCV_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND DESTINATION_TYPE_CODE = 'INVENTORY'

--Pending Material Transactions

SELECT COUNT(*)
  FROM MTL_TRANSACTIONS_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')
   AND PROCESS_FLAG <> 9


--Pending Shop Floor Move Transactions

SELECT COUNT(*)
  FROM WIP_MOVE_TXN_INTERFACE
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID
          FROM APPS.ORG_ORGANIZATION_DEFINITIONS
         WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
   AND TRANSACTION_DATE <
       (SELECT SCHEDULE_CLOSE_DATE + 1
          FROM APPS.ORG_ACCT_PERIODS
         WHERE ORGANIZATION_ID =
               (SELECT ORGANIZATION_ID
                  FROM APPS.ORG_ORGANIZATION_DEFINITIONS
                 WHERE ORGANIZATION_CODE = '<ORGANIZATION_CODE>')
           AND PERIOD_NAME = '<PERIOD_NAME>')

--Unprocessed Shipping Transactions


SELECT COUNT(*)
  FROM WSH_DELIVERY_DETAILS     WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA,
       WSH_NEW_DELIVERIES       WND,
       WSH_DELIVERY_LEGS        WDL,
       WSH_TRIP_STOPS           WTS
 WHERE WDD.SOURCE_CODE = 'OE'
   AND WDD.RELEASED_STATUS = 'C'
   AND WDD.INV_INTERFACED_FLAG IN ('N', 'P')
   AND WDD.ORGANIZATION_ID = 86
   AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
   AND WND.DELIVERY_ID = WDA.DELIVERY_ID
   AND WND.STATUS_CODE IN ('CL', 'IT')
   AND WDL.DELIVERY_ID = WND.DELIVERY_ID
   AND WTS.PENDING_INTERFACE_FLAG IN ('Y', 'P')
   AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN
       TO_DATE('27-OCT-2012 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND
       TO_DATE('23-NOV-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
   AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID

Oracle apps important tables

OE_ORDER_HEADERS_ALL                       OE_HEADERS_IFACE_ALL
OE_ORDER_LINES_ALL                         OE_LINES_IFACE_ALL
OE_ORDER_HOLDS_ALL                         OE_ACTIONS_IFACE_ALL
OE_ORDER_SOURCES
OE_TRANSACTION_TYPES_TL/ALL
OE_PAYMENT_TYPES_TL


WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS

OE_ORDER_HOLDS

RA_SALESREPS
OE_TRANSACTION_TYPES_TL
AR_CUSTOMERS
RA_TERMS

QP_LIST_HEADERS
QP_LIST_LINES
QP_PRICING_ATTRIBUTES

OE_ORDER_HEADERS_ALL oe_order_pub.process_order
OE_ORDER_LINES_ALL
OE_SALES_CREDITS


Oracle Apps Receivables (AR) Tables
Transactions
RA_CUSTOMER_TRX_ALL Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL Salesrep information for Transaction Lines


Transaction Interface Tables
RA_INTERFACE_LINES_ALL Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL Transaction Distribution information
RA_INTERFACE_ERRORS_ALL Transaction errors table
AR_PAYMENTS_INTERFACE_ALL Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL Lockbox transfers the receipts that pass validation to the interim tables


Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.


Customer Tables
HZ_PARTIES A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.


Setup tables
RA_CUST_TRX_TYPES_ALL This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Account Payables Tables

Account Payables Tables

1)Invoice Details.
a)Ap_invoices_all(INVOICE_ID)
You can see the approved Invoices.
b)Ap_invoice_Distributions_all(INVOICE_ID)
To get Distributed Invoices information.
2)Invoice Transactions
a)ap_ae_headers_all(AE_HEADER_ID)
b)ap_ae_lines_all
Stores Distributed Accounting information.
3)Payment Schedule Information
a)ap_payment_schedules_all(INVOICE_ID)
Stores Amount remaing information and schedule payments for an invoice.
b)ap_invoice_payments_all(INVOICE_ID,CHECK_ID)
After compleing invoice payment information stores here.
4) Check Information.
a)ap_checks_all(CHECK_ID)
if you done the payment via check this information stores here.
b)AP_CHECK_FORMATS(Check_format_id)
When you create the invoice ie assciated with accouting infomation that information stores in this table.
c)AP_MC_INVOICES(invoice_id,set_of_book_id)
Contains Multiple invoice Currency information as well as Exchange Information. 
d)AP_HOLDS_ALL
Holds invoice information you places
f)AP_CHRG_ALLOCATIONS_ALL
Used for AP links with the appropriate invoice distributins.
5)Approval information
a)AP_INV_APRVL_HIST_ALL(Approval history id,invoice_id)
Invoice approval information
b)AP_HISTORY_INVOICES_ALL(Invoice_id,VENDOR_iD)
All invoice history information stores here
c)AP_INVOICE_TRANSMISSIONS(JE_BATCH_ID)
When you post the invoice to GL This table will effected.
6)Terms
a)AP_TERMS_TL(TERMS_ID)
Contains Term information.
b)AP_INTERFACE_REJECTIONS
which could not be processed by Payables Open Interface Import
c)AP_BANK_ACCOUNTS_ALL
information about bank accounts

Important Queries Discrepancy in Onhand Qty

ORDERS WITH THE ITEM:

SELECT DISTINCT B.ORDER_NUMBER,

A.ORG_ID,

A.ORDERED_ITEM,

A.FLOW_STATUS_CODE

FROM APPS.OE_ORDER_LINES_ALL A,

APPS.OE_ORDER_HEADERS_ALL B,

APPS.MTL_SYSTEM_ITEMS_B C

WHERE C.SEGMENT1 IN ('&ITEM')

AND A.FLOW_STATUS_CODE NOT IN('CLOSED','CANCELLED')

AND A.HEADER_ID = B.HEADER_ID

AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID

ORDER BY A.ORDERED_ITEM

SELECT (ON_HAND_QUANTITY - ORDERED_QUANTITY) DIFF, GAP.*

  FROM (SELECT NVL(STOCK.ORGANIZATION_CODE, DEMD.ORDER_ORGANIZATION) ORG_CODE,

               NVL(STOCK.OH_EBS_ITEM, DEMD.ORD_EBS_ITEM) EBS_ITEM,

               STOCK.ON_HAND_ITEM,

               STOCK.OH_EBS_ITEM,

               NVL(STOCK.PRIMARY_TRANSACTION_QUANTITY, 0) ON_HAND_QUANTITY,

               DEMD.COUNTRY_CODE,

               DEMD.ORDER_ORGANIZATION,

               DEMD.ITEM_NUMBER,

               DEMD.ORD_EBS_ITEM,

               NVL(DEMD.ORDERED_QUANTITY, 0) ORDERED_QUANTITY

          FROM (SELECT MP.ORGANIZATION_CODE,

                       MIRB.ATTRIBUTE1 ON_HAND_ITEM,

                       MSI.SEGMENT1 OH_EBS_ITEM,

                       SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY) PRIMARY_TRANSACTION_QUANTITY

                  FROM APPS.MTL_ONHAND_QUANTITIES_DETAIL MOQD,

                       APPS.MTL_PARAMETERS               MP,

                       APPS.MTL_SYSTEM_ITEMS             MSI,

                       APPS.MTL_ITEM_REVISIONS_B         MIRB

                 WHERE 1 = 1

                   AND MOQD.ORGANIZATION_ID = MP.ORGANIZATION_ID

                   AND MOQD.ORGANIZATION_ID = MSI.ORGANIZATION_ID

                   AND MOQD.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

                   AND MOQD.ORGANIZATION_ID = MIRB.ORGANIZATION_ID

                   AND MOQD.INVENTORY_ITEM_ID = MIRB.INVENTORY_ITEM_ID

                   AND MOQD.REVISION = MIRB.REVISION

                       AND ORGANIZATION_CODE IN (

                       'C60', 'C65', 'F43'

                       )

                   AND SUBINVENTORY_CODE IN ('PICKLINE', 'STAGING')

                 GROUP BY MP.ORGANIZATION_CODE, MSI.SEGMENT1, MIRB.ATTRIBUTE1

                 ORDER BY ORGANIZATION_CODE, ON_HAND_ITEM) STOCK

          FULL JOIN (SELECT FLV.LOOKUP_CODE COUNTRY_CODE,

                           MP.ORGANIZATION_CODE ORDER_ORGANIZATION,

                           MSI.SEGMENT1 ORD_EBS_ITEM,

                           OOLA.USER_ITEM_DESCRIPTION ITEM_NUMBER,

                           SUM(OOLA.ORDERED_QUANTITY) ORDERED_QUANTITY

                      FROM APPS.OE_ORDER_HEADERS_ALL OOHA,

                           APPS.OE_ORDER_LINES_ALL   OOLA,

                           APPS.FND_LOOKUP_VALUES    FLV,

                           APPS.MTL_PARAMETERS       MP,

                           APPS.MTL_SYSTEM_ITEMS     MSI

                     WHERE 1 = 1

                       AND OOHA.HEADER_ID = OOLA.HEADER_ID

                       AND OOLA.SHIP_FROM_ORG_ID = MP.ORGANIZATION_ID

                       AND OOLA.CANCELLED_FLAG = 'N'

                       AND OOLA.SHIPPABLE_FLAG = 'Y'

                       AND OOLA.OPEN_FLAG = 'Y'

                       AND FLV.LANGUAGE = FLV.SOURCE_LANG

                       AND OOLA.SHIP_FROM_ORG_ID = MSI.ORGANIZATION_ID

                       AND OOLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID

                       AND OOLA.LINE_CATEGORY_CODE = 'ORDER'

                       AND FLV.LOOKUP_TYPE IN ('XX_OM_COUNTRY_OU_MAPPING')

                       AND FLV.ATTRIBUTE1 = OOHA.ORG_ID

                       AND FLV.LOOKUP_CODE = SUBSTR(OOHA.ORIG_SYS_DOCUMENT_REF, 1, 3)

                       AND    OOHA.ATTRIBUTE2 IS NOT NULL

--                       AND TRUNC(TO_DATE(OOHA.ATTRIBUTE2)) < TRUNC(SYSDATE + 1)

                       AND EXISTS (SELECT 1 FROM APPS.XX_OM_ORDER_DELIVERIES_STG RP WHERE RP.CONSOLIDATED_ORDER_NUMBER =  OOHA.ATTRIBUTE7

                       AND RP.COUNTRY_CODE = FLV.LOOKUP_CODE AND RP.SHIP_DATE < TRUNC(SYSDATE) )

                       AND ORGANIZATION_CODE IN (

                       'C60', 'C65', 'F43'

                       )

                     GROUP BY FLV.LOOKUP_CODE,

                              MP.ORGANIZATION_CODE,

                              MSI.SEGMENT1,

                              OOLA.USER_ITEM_DESCRIPTION

                     ORDER BY ORGANIZATION_CODE, ITEM_NUMBER) DEMD

            ON DEMD.ITEM_NUMBER = STOCK.ON_HAND_ITEM

           AND STOCK.OH_EBS_ITEM = DEMD.ORD_EBS_ITEM

           AND DEMD.ORDER_ORGANIZATION = STOCK.ORGANIZATION_CODE) GAP

WHERE 1 = 1

  AND (ON_HAND_QUANTITY = 0 OR ORDERED_QUANTITY = 0 OR ON_HAND_QUANTITY <> ORDERED_QUANTITY)

;