Need to know backend location(table name) of Customer Ship To Number in Oracle EBS

Question

I am working in Oracle E-Business Suite. Considering this field "CUSTOMER_SHIP_TO_NUMBER" Oracle EBS Screenshot

I need to know the column and the table in the back-end database, from which the values of this field is getting populated. I tried examining the Record History, and the data is being fetched from a View, namely the OE_Order_Lines_V. I tried searching through that view but couldn't figure it out. I need to know the actual location, namely the Table, where this data (CUSTOMER_SHIP_TO_NUMBER) is being stored.

Answer 1

The Data comes from the Table ONT.OE_ORDER_LINES_ALL, under the column of END_CUSTOMER_ID. This should be joined with AR.RA_CUSTOMERS using the column CUSTOMER_ID to get the Customer Name and Number:

SELECT  racust.customer_id
    ,   racust.customer_name
    ,   racust.customer_number  -- this is the SHIP_TO_CUSTOMER_NUMBER
FROM    AR.RA_CUSTOMERS racust
    ,   ONT.OE_ORDER_headers_all oola
where   oola.END_CUSTOMER_ID = racust.CUSTOMER_ID;

Read More Here: Oracle Order Management Technical Reference Manual

Answer 2

You can use the below query to find the customer details.

SELECT hp.party_name "CUSTOMER_NAME", 
hca.account_number "CUSTOMER_NUMBER",
csu.location "SHIP_TO_ORG_ID",hca.cust_account_id "CUSTOMER_ID"
FROM hz_parties hp, hz_cust_accounts hca, 
hz_cust_acct_sites_all cas, hz_cust_site_uses_all csu
WHERE hp.party_id = hca.party_id
AND hca.party_site_id = cas.party_site_id 
AND cas.cust_acct_site_id = csu.cust_acct_site_id
AND cas.address_type = 'SHIP_TO'
AND csu.location = <ship_to_org_id>;