List Tables Information Per Module in EBS Oracle Apps

HR ( Human Resource and Application Hierarchy )

HR_OPERATING_UNITS
HR_ORGANIZATION_UNITS                        -- TABLE IO - Inventory Organization
HR_ALL_ORGANIZATION_UNITS_TL        -- TABLE IO - Inventory Organization
HR_ALL_ORGANIZATION_UNITS    PK/ FK ( ORGANIZATION_ID, LOCATION_ID )
ORG_ORGANIZATION_DEFINITIONS     --
      Important Columns :
             (ORGANIZATION_ID,
              ORGANIZATION_CODE,
              ORGANIZATION_NAME,
              SET_OF_BOOKS_ID,
              CHART_OF_ACCOUNTS_ID,
              OPERATING_UNIT,
              LEGAL_ENTITY)
PER_SECURITY_PROFILES
PER_SECURITY_ORGANIZATIONS_V

ORG_ACCESS_V
FND_USER
MTL_PARAMETERS
PER_PEOPLE_F
PER_ALL_PEOPLE_F

-- query berikut adalah untuk menampilkan semua kode  OU
select  hoi.organization_id ou_id, hoi.org_information5 "kode OU"
from hr_organization_information hoi
where org_information_context = 'Operating Unit Information'

-- query berikut adalah untuk menampilkan kode io dan kode OU nya
select  hoi.organization_id io_id, hoi.org_information3 id_ou
from hr_organization_information hoi
where org_information_context = 'Accounting Information'



FND Tables
FND_TERRITORIES
FND_LOOKUPS
=========================================================
iProcurement module 

PON_AUCTION_ITEM_PRICES_ALL

PON_AUCTION_HEADERS_ALL

di Table PON_AUCTION_ITEM_PRICES_ALL ada kolom dengan nama REQUISITION_NUMBER,
ini adalah NO PR, PURCHASE_BASIS ada 2 pilihan GOODS dan SERVICES

AGENT = BUYER
AGENT_ID = BUYER_ID

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

AR ( Account Receivable ) 

AR_BATCH_SOURCES_ALL
AR_CASH_RECEIPTS_ALL
AR_CUSTOMERS                      -- Customer Informations
AR_RECEIVABLES_TRX_ALL
AR_MEMO_LINES_ALL_B
AR_ADJUSTMENTS_ALL       -- Adjustment Informations

AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_ADDRESSES_ALL

HZ_PARTIES
HZ_PARTY_PREFERENCES
HZ_ORGANIZATION_PROFILES

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

INV ( Inventory ) 

MTL_CATEGORIES

MTL_SYSTEM_ITEMS      -- Item Information / Table Master Item 

MTL_SYSTEM_ITEMS_B

MTL_MATERIAL_TRANSACTIONS

MTL_TRANSACTION_ACCOUNTS

MTL_TRANSACTION_TYPES
MTL_ITEM_CATEGORIES
MTL_ONHAND_QUANTITIES_DETAIL
MTL_UNITS_OF_MEASURE


SELECT MIC.INVENTORY_ITEM_ID,
       MIC.ORGANIZATION_ID,
       MIC.CATEGORY_SET_ID,
       MIC.CATEGORY_ID
  FROM MTL_ITEM_CATEGORIES MIC

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

OM ( Order Management ) 

OE_ORDER_HEADERS_ALL    -- OM Order header
OE_ORDER_LINES_ALL           -- OM Order lines

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

PO Tables ( Purchasing )

-- table PR (Purchase Requisitions to Purchase Order (PO) ) 

PO_VENDORS
PO_VENDOR_SITES_ALL

PO_REQUISITION_HEADERS_ALL       PK ( REQUISITION_HEADER_ID )

PO_REQUISITION_LINES_ALL              PK (REQUISITION_LINE_ID), FK ( REQUISITION_HEADER_ID )

PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL             
       PK (PO_DISTRIBUTION_ID)
       FK (PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID,
              CODE_COMBINATION_ID, PO_RELEASE_ID, REQ_DISTRIBUTION_ID)
PO_HEADERS_ALL
   type_lookup_code  : PLANNED, CONTRACT, RFQ, BLANKET, QUOTATION, STANDARD
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL       PK ( LINE_LOCATION_ID )   fk ( PO_HEADER_ID, PO_LINE_ID, PO_RELEASE_ID , ORG_ID (OU ID))
PO_RELEASES_ALL
PO_LOOKUP_CODES
PO_DOCUMENT_TYPES_ALL

PO_DOC_STYLE_HEADERS
PO_ACTION_HISTORY
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES

SELECT DISTINCT pah.OBJECT_TYPE_CODE, pah.OBJECT_SUB_TYPE_CODE
  FROM PO_ACTION_HISTORY pah

-- Link from PR to PO
select prd.*
from po_req_distributions_all prd , po_distributions_all pd
where prd.distribution_id = pd.req_distribution_id

=========================================================
AP Tabkes - ( Account Payable )

AP_BATCHES_ALL
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_PAYMENTS_ALL
AP_ACCOUNTING_EVENTS_ALL
    Column
    - EVENT_TYPE_CODE :
                          EVENT_TYPE_CODE
                          INVOICE CANCELLATION
                          INVOICE
                          PAYMENT
                          INVOICE ADJUSTMENT
                          PREPAYMENT APPLICATION
                          PAYMENT CLEARING
                          PAYMENT ADJUSTMENT
                          PAYMENT CANCELLATION
                          PREPAYMENT UNAPPLICATION
                          PAYMENT UNCLEARING
    - SOURCE_TABLE : Values=AP_CHECKS, AP_INVOICES, AP_PAYMENT_HISTORY
AP_CHECKS_ALL
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
AP_PREPAY_HISTORY_ALL
- COLUMNS
PREPAY_HISTORY_ID
PREPAY_INVOICE_ID
PREPAY_LINE_NUM
ACCOUNTING_EVENT_ID
HISTORICAL_FLAG
INVOICE_ID
INVOICE_ADJUSTMENT_EVENT_ID
ORG_ID
AP_PAYMENT_HISTORY_ALL
- COLUMNS
PAYMENT_HISTORY_ID
CHECK_ID
ACCOUNTING_DATE
TRANSACTION_TYPE  -- OPTION ('PAYMENT CREATED',  'PAYMENT CANCELLED')
POSTED_FLAG
MATCHED_FLAG
ACCOUNTING_EVENT_ID
ORG_ID

AP_BANK_ACCOUNTS_ALL



=========================================================
XLA Tables ( SubLedger Accounting - XLA ) 
-- link source : https://orafinappssetups.blogspot.com/2013/01/faq-on-transfer-to-gl-in-r12.html

XLA_AE_HEADERS
XLA_AE_LINES
GL_IMPORT_REFERENCES
GL_CODE_COMBINATIONS
GL_CODE_COMBINATIONS_KFV

Table Name = XLA_AE_HEADERS
         Columns = ACCOUNTING_ENTRY_STATUS_CODE
         -- Description : This column Represents accounting status of a transaction event...
         Option Values =
            IF ACCOUNTING_ENTRY_STATUS_CODE = F then event is Accounted Successfully
            IF ACCOUNTING_ENTRY_STATUS_CODE = N then event is still not processed
            IF ACCOUNTING_ENTRY_STATUS_CODE = I then event is failed
            IF ACCOUNTING_ENTRY_STATUS_CODE = R then event is in Error
            IF ACCOUNTING_ENTRY_STATUS_CODE = D then event is Draft Accounting Entry

XLA_AE_HEADERS =>
            the table shows the transfer status of the accounting data,
            after successful transfer to the GL and Journal Import :
            gl_transfer_status_code = 'Y'
            gl_transfer_date is not null
            group_id is not null

=========================================================
GL Tables ( General Ledger ) 


PK = Primary Key
FK = Foreign Key