Naming Conversion Table Name end with _ALL, _TL, _VL, _V, _F, _A, _B, _S, _AVN and its Meaning

• _ALL : Table holds all the information about different operating units. Multi-Org environment. You can also set the client_info to specific operating unit to see the data specific to that operating unit only.
• _TL : are tables corresponding to another table with the same name minus the _TL. These tables provide multiple language support. For each item in the table without _TL there can be many rows in the _TL table, but all with different values in the LANGUAGE column.
• _B : these are the BASE tables.
  They are very important and the data is stored in the table with all validations.
  It is supposed that these table will always contain the perfect format data.
  If anything happens to the BASE table data, then it is a data corruption issue.
• _F : these are date tracked tables, which occur in HR and Payroll. For these there are two date columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE which together with the PK identifies a row uniquely. The date intervals cannot overlap. Many think they are Secured data. Guess someone from Oracle confirms.
• _V : tables are the views created on base tables
• _VL : are views for multi language tables which combines the row of the base table with the corresponding row of the _TL table where the LANGUAGE = USERENV('LANG').
• _S : are sequences, used for finding new values for the primary key of a table.
• _A : are Audit Shadow Tables
• _AVN : and _ACN are Audit Shadow Views (when data was changed, and with what values
  

O2C TECHNICAL FLOW

select ooha.order_number,ooha.org_id,
       hca.account_name,
       hp.party_name "Customer Name",
       hcasab.orig_system_reference      BILL_TO_ORIG_REF,
       hpsb.status                       BILL_TO_STATUS,
       'ADDRESS1 - '||bill_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
       'ADDRESS3 - '||bill_loc.address3||','||CHR(10)|| 'CITY     - '||bill_loc.city||','||CHR(10)||
       'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS,
       hcasas.orig_system_reference      SHIP_TO_ORIG_REF,
       hpss.status SHIP_TO_STATUS,
       'ADDRESS1 - '||ship_loc.address1||','||CHR(10)|| 'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
       'ADDRESS3 - '||ship_loc.address3||','||CHR(10)|| 'CITY     - '||ship_loc.city||','||CHR(10)||
       'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)|| 'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS,
       oola.inventory_item_id,oola.ordered_item,
       msib.description item_description,
       wnd.name delivery_number,
       rct.trx_number "AR Invoice Number",
       acr.receipt_number "AR Receipt Number",
       gjh.ledger_id,
       gjh.name
  from oe_order_headers_all ooha,
       oe_order_lines_all oola,
       hz_parties hp,
       hz_cust_accounts hca,
       hz_party_sites hpss,
       hz_party_sites hpsb,
       hz_locations bill_loc,
       hz_locations ship_loc,
       hz_cust_acct_sites_all hcasab,
       hz_cust_acct_sites_all hcasas,
       hz_cust_site_uses_all hzsuab,
       hz_cust_site_uses_all hzsuas,
       mtl_system_items_b msib,
       wsh_delivery_details wdd,
       wsh_new_deliveries wnd,
       wsh_delivery_assignments wda,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rctl,
       ra_cust_trx_line_gl_dist_all rctld,        
       ar_cash_receipts_all acr,
       xla.xla_transaction_entities xte,
       xla_events xe,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_distribution_links xdl,
       gl_import_references gir,
       gl_je_batches gjb,
       gl_je_headers gjh,
       gl_je_lines gjl
 where ooha.order_number = :SalesOrderNumber --Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
   and ooha.org_id = 204
   and hca.cust_account_id    = ooha.sold_to_org_id
   and hp.party_id            = hca.party_id
   and hpss.party_id            = hca.party_id
   and hpsb.party_id            = hca.party_id
   and bill_loc.location_id = hpss.location_id
   and ship_loc.location_id = hpsb.location_id
   AND hcasas.cust_account_id  = hca.cust_account_id
   AND hcasab.cust_account_id  = hca.cust_account_id
   AND hcasas.party_site_id    = hpss.party_site_id
   AND hcasab.party_site_id    = hpsb.party_site_id
   and hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
   and hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
   and hzsuas.site_use_id = ooha.ship_to_org_id
   and hzsuab.site_use_id = ooha.invoice_to_org_id            
   and wda.delivery_id        = wnd.delivery_id(+)
   and wdd.delivery_detail_id = wda.delivery_detail_id
   and wdd.source_header_id   = ooha.header_id
   and wdd.source_line_id     = oola.line_id
   and wdd.organization_id    = msib.organization_id(+)
   and wdd.inventory_item_id  =msib.inventory_item_id(+)
   and rct.interface_header_attribute1 = to_char(ooha.order_number)
   and rct.org_id = ooha.org_id
   and rctl.customer_trx_id = rct.customer_trx_id
   and rctl.sales_order = to_char(ooha.order_number)
   and rctld.customer_trx_id = rct.customer_trx_id
   and rctld.customer_trx_line_id = rctl.customer_trx_line_id
   and acr.receipt_number = 'G-1001'
   and acr.pay_from_customer = rct.sold_to_customer_id
   and acr.org_id = ooha.org_id
   and acr.customer_site_use_id = rct.bill_to_site_use_id
   and xte.transaction_number = acr.receipt_number
   and xte.entity_code = 'RECEIPTS'
   and xe.entity_id = xte.entity_id  
   and xah.event_id = xe.event_id
   and xal.ae_header_id = xah.ae_header_id
   and xal.accounting_class_code = 'CASH'
   and xdl.ae_header_id = xah.ae_header_id
   and xdl.ae_line_num = xal.ae_line_num
   --and xdl.source_distribution_id_num_1
   and gir.reference_5 = xte.entity_id  -- Entity Id
   and gir.reference_6 = to_char(xe.event_id) --Event Id
   and gir.reference_7 = to_char (xah.ae_header_id) -- AE Header Id
   and gir.gl_sl_link_id = xal.gl_sl_link_id
   and gir.created_by = 1318
   and gjb.je_batch_id = gir.je_batch_id
   and gjh.je_batch_id=gjb.je_batch_id
   and gjh.je_header_id = gir.je_header_id
   and gjl.je_header_id=gjh.je_header_id
   and gjl.je_line_num= gir.je_line_num