Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Tuesday, June 21, 2011

Importent Tables in Oracle

RA_CUSTOMER_TRX_ALL :- This is the header table for Sales Invoices

Customer_trx_id :- This is the Primary Key in this table.
Trx_number :- This is the Invoice Number.
Trx_DATE :- This is the Invoice Date
Cust_trx_type_id:- This is the link between the RA_CUSTOMER_TRX_ALL table
and the RA_CUST_TRX_TYPES_ALL table. Based on this id we will
come to know the type of the transaction like whether it is
Invoice, Credit Memo or Debit Memo.
Bill_to_customer_id:- Is the customer_id and should be linked to HZ_CUST_ACCOUNTS_ALL Table to get the customer details.
Ship_to_customer_id:- Is the customer_id where the Invoice is going to be shipped
Bill_to_site_use_id & Ship_To_site_use_id are the ids of the sites.
batch_id :- Is the link between the Ra_customer_trx_all table and the

RA_BATCHES_ALL table.

batch_source_id :- Is the link between the Ra_customer_trx_all table and the
RA_BATCHES_SOURCES_ALL table.
Waybill_number :- Is the waybill_number used in the Invoice.
Purchase_Order :- Is the Purhcase Order No against which this invoice is raised.
Territory_id :- Is the link between RA_CUSTOMER_TRX_ALL table and RA_TERRITORIES table. This is used to trace the territory of
the customer.

Complete_Flag :- Is an indicator whether the invoice is complete or not
Status_Trx :- Indicates the status of the Invoice.

RA_CUSTOMER_TRX_LINES_ALL :- This is the lines table for Invoice

customer_trx_line_id :- This is the Primary Key of Ra_customer_trx_lines_all table
customer_trx_id :- This is the link between the Ra_customer_trx_all and
Ra_customer_trx_lines_all tables.
line_number :- Is a line number
inventory_item_id :- This is item_id of the item invoiced and the item number will be there in the mtl_system_items table and the link
between these two tables is inventory_item_id and the organization_id.
quantity_invoiced:- Is the quantity of the item.
unit_selling_price :- Is the unit price of that item.
sales_order :- Is the Sales OrderNumber form Order Management.
sales_order_line:- Is the Sales Order Line Number.
Line_type :- Indicates the line type whether it is Line Or Tax
extended_amount :- Total Value of that line .
link_to_cust_trx_line_id :- This is used to get the tax details from the localization Tables for tax.
tax_rate :- Is the Tax Rate
uom_code :- Is the UOM.

------------ Header ------------------------------------------
select customer_trx_id, trx_number, trx_date, cust_trx_type_id,
bill_to_customer_id, ship_to_customer_id,
bill_to_site_use_id, ship_to_site_use_id,
batch_id, batch_source_id, waybill_number,
purchase_order, territory_id, complete_flag, status_trx
from ra_customer_trx_all

------------ Lines ------------------------------------------
select customer_trx_line_id, customer_trx_id, line_number,
inventory_item_id, quantity_invoiced, unit_selling_price,
sales_order, sales_order_line, Line_type, extended_amount,
link_to_cust_trx_line_id, tax_rate, uom_code,
from ra_customer_trx_lines_all

------------ Master Tables for Type, Batch & Batch Source----
select cust_trx_type_id, name, status, type
from ra_cust_trx_types_all

select name, batch_date from ra_batches_all

select batch_source_id, name, description, status from ra_batch_sources_all

------------ GL Tables --------------------------------------
GL_JE_HEADERS :- Is the header table for GL Transactions
GL_JE_LINES :- Is the Lines table for GL Transactions
GL_JE_CATEGORIES :- Is the header table for GL Categories
GL_JE_BATCHES :- Is the header table for GL Batches

------------ AP Tables --------------------------------------
AP_INVOICES_ALL :- Is Master Table for Invoices
AP_INVOICE_DISTRIBUTIONS_ALL :- Is the detail table for Invoice Distribution Lines.
AP_CHECKS_ALL :- Is the Master Table for Cheques
AP_INVOICE_PAYMENTS_ALL :- Is the Master table for Payments made.
AP_BANK_ACCOUNTS_ALL :- Is the Master table for Banks
AP_BANK_BRANCHES_ALL :- Is the Master table for Banks and their corresponding branches.

---------- Important Columns in Payables Tables-------------
AP_INVOICES_ALL :- INVOICE_ID is the Primary Key
:- INVOICE_NUM is the Invoice Number
:- INVOICE_DATE is the Invoice Date
:- VENDOR_ID is the link between PO_VENDORS(Supplier Master Table) to get the supplier name
:- VENDOR_SITE_ID is the link between PO_VENDOR_SITES_ALL(supplier Sites Master Table)

AP_CHECKS_ALL :- CHECK_ID is the Primary Key
:- Check_number is the Check Number
:- BANK_ACCOUNT_ID is the link between AP_BANK_ACCOUNTS_ALL To get the Bank Name.
:- EXTERNAL_BANK_ACCOUNT_ID is the link between AP_BANK_ACCOUNTS_ALL To get the Supplier Bank.

AP_INVOICE_PAYMENTS_ALL :- INVOICE_PAYMENT_ID is the Primary Key
:- INVOICE_ID to link AP_INVOICES_ALL
:- CHECK_ID to link AP_cHECKS_ALL
:- whenever we make any payment, the details will be there in this table and we will be linking
AP_INVOICES_ALL and AP_CHECKS_ALL using the above columns.

------------ FA Tables --------------------------------------
FA_BOOKS

FA_ADDITIONS

FA_MASS_ADDITIONS

FA_DISTRIBUTION_HISTORY

-------------------INTERFACE TABLES-------------------------
OE_HEADERS_IFACE_ALL -- FOR OM Headers
OE_LINES_IFACE_ALL -- FOR OM Lines
RA_INTERFACE_LINES_ALL -- FOR AR
AP_INVOICE_LINES_INTERFACE -- FOR AP
GL_INTERFACE -- FOR GL

No comments:

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect