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

OM TABLE FLOW

Order Management Tables.


Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.

Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release

Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock

Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines

wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned

Pick Confirmed
wsh_delivery_details Released_status=Y Hard Reservations. Picked the stock. Physical movement of stock


Ship Confirmed

wsh_delivery_details Released_status=C Y To C:Shipped ;Delivery Note get printed Delivery assigned to trip stopquantity will be decreased from staged
mtl_material_transactions On the ship confirm form, check Ship all box
wsh_new_deliveries If Defer Interface is checked I.e its deferred then OM & inventory not updated. If Defer Interface is not checked.: Shipped

oe_order_lines_all Shipped_quantity get populated.
wsh_delivery_legs 1 leg is called as 1 trip.1 Pickup & drop up stop for each trip.
oe_order_headers_all If all the lines get shipped then only flag N


Autoinvoice

wsh_delivery_details Released_status=I Need to run workflow background process.
ra_interface_lines_all Data will be populated after wkfw process.
ra_customer_trx_all After running Autoinvoice Master Program for
ra_customer_trx_lines_all specific batch transaction tables get populated

Price Details
qp_list_headers_b To Get Item Price Details.
qp_list_lines

Items On Hand Qty
mtl_onhand_quantities TO check On Hand Qty Items.

Payment Terms
ra_terms Payment terms

AutoMatic Numbering System
ar_system_parametes_all you can chk Automactic Numbering is enabled/disabled.

Customer Information
hz_parties Get Customer information include name,contacts,Address and Phone
hz_party_sites
hz_locations
hz_cust_accounts
hz_cust_account_sites_all
hz_cust_site_uses_all
ra_customers

Document Sequence
fnd_document_sequences Document Sequence Numbers
fnd_doc_sequence_categories
fnd_doc_sequence_assignments

Default rules for Price List
oe_def_attr_def_rules Price List Default Rules
oe_def_attr_condns
ak_object_attributes

End User Details
csi_t_party_details To capture End user Details

Sales Credit Sales Credit Information(How much credit can get)
oe_sales_credits

Attaching Documents
fnd_attached_documents Attched Documents and Text information
fnd_documents_tl
fnd_documents_short_text

Blanket Sales Order
oe_blanket_headers_all Blanket Sales Order Information.
oe_blanket_lines_all

Processing Constraints
oe_pc_assignments Sales order Shipment schedule Processing Constratins
oe_pc_exclusions

Sales Order Holds
oe_hold_definitions Order Hold and Managing Details.
oe_hold_authorizations
oe_hold_sources_all
oe_order_holds_all

Hold Relaese
oe_hold_releases_all Hold released Sales Order.

Credit Chk Details
oe_credit_check_rules To get the Credit Check Againt Customer.

Cancel Orders
oe_order_lines_all Cancel Order Details.

SQL 三種JOIN語法的差異

三種JOIN語法的差異

敘述

用途說明
INNER JOIN
僅顯示兩資料表對應欄位中值相同的欄位。

LEFT JOIN

串聯兩個資料表中對應欄資料時，以資料表1的資料為主，若資料存在於資料表1，但資料表2沒有對應值時，仍顯示資料表1中的資料。

RIGHT JOIN

串聯兩個資料表中對應欄資料時，以資料表2的資料為主，若資料存在於資料表2，但資料表1沒有對應值時，仍顯示資料表2中的資料。

Oracle 排序欄位 有NULL值處理方法

Oracle 排序欄位 有NULL值處理方法

Oracle在Order by 時會預設認為null是最大值，所以如果是ASC升序則排在最後，DESC降序則排在最前

有幾種方式可以處理排序欄位有 null 值，大致如下:

1.使用nulls first 或者nulls last 語法

Nulls first和nulls last是Oracle Order by支援的語法 (mysql 不支援 ,但此為 SQL 99 standard 語法)

如果Order by 中指定了運算式 Nulls first則表示null值的記錄將排在最前(不管是asc 還是 desc)
反之 Nulls last則表示null值的記錄將排在最後 (不管是asc 還是 desc)
使用語法如下：

--將nulls始終放在最後
select * from products order by order_date desc nulls last

2、使用nvl函數

nvl函數可以將輸入參數為空時轉換為一特定值，如
nvl(employee_name,’張三’)表示當employee_name為空時則返回’張三’，如果不為空則返回employee_name
通過這個函數可以定制null的排序位置。

3、使用decode函數

decode函數比nvl函數更強大，同樣它也可以將輸入參數為空時轉換為一特定值，如
decode(employee_name,null,’張三’, employee_name)表示當employee_name為空時則返回’張三’，如果不為空則返回employee_name
通過這個函數可以定制null的排序位置。

4、使用case 語法

Case語法是Oracle 9i後開始支援的，是一個比較靈活的語法，同樣在排序中也可以應用
如：
select *
from employee
order by (case employee_name
when null then
'張三'
else
employee_name
end)
表示當employee_name為空時則返回’張三’，如果不為空則返回employee_name
通過case語法同樣可以定制null的排序位置。

asp使用祕技

如何使asp存excel,不產生科學記號

or