ORACLE 常用TABL

call fnd_global.APPS_INITIALIZE(1318,50583,401)
select fnd_profile.VALUE('ORG_ID') FROM DUAL
select * from hr_operating_units hou where hou.organization_id=204
--fnd
select * from fnd_application
select * from fnd_application_tl where application_id=101
select * from fnd_application_vl where application_id = 101
----值集
select * from fnd_flex_value_sets
select * from fnd_flex_values
select * from fnd_flex_values_vl
----彈性域
select * from fnd_id_flexs
select * from fnd_id_flex_structures where id_flex_code='GL#'
select * from fnd_id_flex_segments where id_flex_code='GL#' and id_flex_num=50671
select * from fnd_profile_options_vl
select * from fnd_concurrent_programs程序表
select * from fnd_concurrent_requests請求表
select * from fnd_concurrent_processes進程表
--inv
select * from org_organization_definitions庫存組織
select * from mtl_parameters組織參數
select * from mtl_system_items_b where inventory_item_id = 171 and organization_id=204物料表
select * from mtl_secondary_inventories子庫存
select * from mtl_item_locations貨位
select * from mtl_lot_numbers批次
select * from mtl_onhand_quantities現有量表
select * from mtl_serial_numbers序列
select * from mtl_material_transactions物料事務記錄
select * from mtl_transaction_accounts會計分錄
select * from mtl_transaction_types事務類型
select * from mtl_txn_source_types事務來源類型
select * from mfg_lookups ml where ml.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
--po
select * from po_requisition_headers_all請求頭
select * from po_requisition_lines_all請求行
select * from po_headers_all訂單頭
select * from po_lines_all訂單行
select * from po_line_locations_all
select * from po_distributions_all分配
select * from po_releases_all發送
select * from rcv_shipment_headers採購接收頭
select * from rcv_shipment_lines採購接收行
select * from rcv_transactions接收事務處理
select * from po_agents
select * from po_vendors訂單
select * from po_vendor_sites_all
--oe
select * from ra_customers客戶
select * from ra_addresses_all地址
select * from ra_site_uses_all用戶
select * from oe_order_headers_all銷售頭
select * from oe_order_lines_all銷售行
select * from wsh_new_deliveries發送
select * from wsh_delivery_details
select * from wsh_delivery_assignments
--gl
select * from gl_sets_of_books總帳
select * from gl_code_combinations gcc where gcc.summary_flag='Y'科目組合
select * from gl_balances科目餘額
select * from gl_je_batches憑證批
select * from gl_je_headers憑證頭
select * from gl_je_lines憑證行
select * from gl_je_categories憑證分類
select * from gl_je_sources憑證來源
select * from gl_summary_templates科目匯總模板
select * from gl_account_hierarchies科目匯總模板層次
--ar
select * from ar_batches_all事務處理批
select * from ra_customer_trx_all發票頭
select * from ra_customer_trx_lines_all發票行
select * from ra_cust_trx_line_gl_dist_all發票分配
select * from ar_cash_receipts_all收款
select * from ar_receivable_applications_all核銷
select * from ar_payment_schedules_all發票調整
select * from ar_adjustments_all會計分錄
select * from ar_distributions_all付款計劃
--ap
select * from ap_invoices_all發票頭
select * from ap_invoice_distributions_all發票行
select * from ap_payment_schedules_all付款計劃
select * from ap_check_stocks_all單據
select * from ap_checks_all付款
select * from ap_bank_branches銀行
select * from ap_bank_accounts_all銀行帳號
select * from ap_invoice_payments_all核銷

Customer Conversion:
Interface Tables :
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base Tables :
RA_CUSTOMERS
RA_ADDRESSES
RA_SITE_USES_ALL
RA_CUSTOMER_PROFILES_ALL
RA_PHONES

Auto Invoice:
Interface Tables :
RA_INTERFACE_LINES_ALL,
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL,
RA_INTERFACE_ERRORS_ALL
Base Tables :
RA_CUSTOMER_TRX_ALL,
RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL,
RA_CUST_TRX_LINE_SALESREPS_ALL,
RA_CUST_TRX_TYPES_ALL

AutoLockBox:
Interface Tables :
AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables :
AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables :
AR_CASH_RECEIPTS_ALL,
AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)

oracel ar

SELECT rc.customer_number, rc.customer_name,
rca.doc_sequence_value AS "傳票號碼", rca.trx_number AS "立帳編號",
rca.trx_date AS "立帳日期", rca.exchange_rate_type AS "匯率轉換型態",
rca.exchange_date AS "匯率轉換日期", rca.exchange_rate AS "匯率",
rca.invoice_currency_code AS "立帳幣別",
oh.transactional_curr_code AS "訂單幣別",
rca.cust_trx_type_id AS "立帳類型",
rca.batch_source_id AS "立帳來源", rcl.line_number AS "項次",
rcl.inventory_item_id AS "立帳料編", msi.segment1 "料號",
rcl.description AS "說明", rcl.quantity_ordered AS "訂單數量",
rcl.quantity_invoiced AS "立帳數量",
rcl.unit_selling_price AS "立帳單價",
ol.unit_selling_price AS "訂單單價", ol.tax_code AS "訂單稅別",
tv.tax_code AS "立帳稅別", rcl.sales_order AS "訂單單號",
rcl.sales_order_line AS "訂單明細項次",
rcl.sales_order_date AS "訂單日期",
rcl.gross_extended_amount AS "含稅金額",
rcl.interface_line_attribute3 AS "出貨號碼",
wnd.waybill AS "江氏出貨號",
ROUND (NVL (rcl.extended_amount, 0), 2) AS "原幣金額",
ROUND (NVL (rca.exchange_rate, 1), 0)
* NVL (rcl.extended_amount, 0) AS "轉換金額"
FROM ra_customer_trx_all rca,
ra_customer_trx_lines_all rcl,
mtl_system_items_b msi,
ra_customers rc,
oe_order_headers_all oh,
oe_order_lines_all ol,
oe_transaction_types_tl ott,
ar_vat_tax_all tv,
wsh_new_deliveries wnd
WHERE rca.customer_trx_id = rcl.customer_trx_id
AND rca.bill_to_customer_id = rc.customer_id
AND rcl.sales_order_source = 'ORDER ENTRY'
AND rcl.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = '105'
AND oh.header_id = ol.header_id
AND rcl.interface_line_context = 'ORDER ENTRY'
AND rcl.interface_line_attribute2 = ott.NAME
AND rcl.line_type = 'LINE'
AND rcl.interface_line_attribute6 = TO_CHAR (ol.line_id)
AND rcl.vat_tax_id = tv.vat_tax_id
AND rcl.interface_line_attribute3 = wnd.delivery_id
AND TO_CHAR (rca.trx_date, 'yyyy/mm/dd') >= '2007/03/01'
AND TO_CHAR (rca.trx_date, 'yyyy/mm/dd') <= '2007/03/31'
ORDER BY rc.customer_number, rcl.sales_order_line

om table

/* demo to concatenate order info */
select
oh.order_number, cust.customer_id, oh.cust_po_number, cust.customer_number,
cust.customer_name, rc.contact_key contact, ottt.name order_type,
rsa.name, oh.flow_status_code status, qlht.name price_list, ordered_date,
( select '('||oh.ship_to_org_id||') '||
arp_addr_pkg.format_address( ra.address_style,
ra.address1, ra.address2, ra.address3, ra.address4,
ra.city, ra.county, ra.state, ra.province,
ra.postal_code, terr.territory_short_name
) concatenated_address
from ra_addresses ra, fnd_territories_vl terr
where ra.address_id = (
select rsua.address_id from ra_site_uses_all rsua
where location = oh.ship_to_org_id
) and ra.country = terr.territory_code(+)
) ship_to_address,
( select '('||oh.invoice_to_org_id||') '||
arp_addr_pkg.format_address( ra.address_style,
ra.address1, ra.address2, ra.address3, ra.address4,
ra.city, ra.county, ra.state, ra.province,
ra.postal_code, terr.territory_short_name
) concatenated_address
from ra_addresses ra, fnd_territories_vl terr
where ra.address_id = (
select rsua.address_id from ra_site_uses_all rsua
where location = oh.invoice_to_org_id
) and ra.country = terr.territory_code(+)
) bill_to_address
from ra_customers cust, oe_order_headers_all oh, ra_contacts rc,
ra_salesreps_all rsa, oe_transaction_types_tl ottt,
qp_list_headers_tl qlht, oe_transaction_types_all otta
where 1=1
and cust.customer_id = oh.sold_to_org_id
and order_number like '1010612117%'
and otta.transaction_type_id = oh.order_type_id
and rsa.salesrep_id(+) = oh.salesrep_id
and ottt.transaction_type_id = oh.order_type_id
and qlht.list_header_id = oh.price_list_id
and rc.contact_id(+) = oh.sold_to_contact_id