ORACLE EBS常用表及查询语句（最终整理版）
建議去看參考二
參考一： ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
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?? 進(jìn)程表
--inv(庫存)
select * from org_organization_definitions?? 庫存組織
select * from mtl_parameters???????????????? 組織參數(shù)
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??????? 現(xiàn)有量表
select * from mtl_serial_numbers?????????? 序列
select * from mtl_material_transactions??? 物料事務(wù)記錄
select * from mtl_transaction_accounts???? 會計(jì)分錄
select * from mtl_transaction_types??????? 事務(wù)類型
select * from mtl_txn_source_types???????? 事務(wù)來源類型
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?????????????? 發(fā)送
select * from rcv_shipment_headers????????? 采購接收頭
select * from rcv_shipment_lines??????????? 采購接收行
select * from rcv_transactions????????????? 接收事務(wù)處理
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??????????? 發(fā)送
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（應(yīng)收）
select * from ar_batches_all???????????????? 事務(wù)處理批
select * from ra_customer_trx_all??????????? 發(fā)票頭
select * from ra_customer_trx_lines_all????? 發(fā)票行
select * from ra_cust_trx_line_gl_dist_all?? 發(fā)票分配
select * from ar_cash_receipts_all?????????? 收款
select * from ar_receivable_applications_all 核銷
select * from ar_payment_schedules_all?????? 發(fā)票調(diào)整
select * from ar_adjustments_all???????????? 會計(jì)分錄
select * from ar_distributions_all?????????? 付款計(jì)劃
--ap（應(yīng)付）
select * from ap_invoices_all?????????????? 發(fā)票頭
select * from ap_invoice_distributions_all 發(fā)票行
select * from ap_payment_schedules_all????? 付款計(jì)劃
select * from ap_check_stocks_all?????????? 單據(jù)
select * from ap_checks_all???????????????? 付款
select * from ap_bank_branches????????????? 銀行
select * from ap_bank_accounts_all????????? 銀行帳號
select * from ap_invoice_payments_all?????? 核銷
========================華麗的分割線=========================
INV庫存
organization 兩個含義:
1. 經(jīng)營單位，A/B/C分公司，A下面有A1，A2等工廠，主題目標(biāo)是為了獨(dú)立核算此組織
ORG，ORG_ID;
2. 庫存組織，例如制造商的倉庫，例如A1，A2等工廠
Organization_id;
HR_ORGANIZATION_UNITS －
Org_organization_definitions?
Mtl_subinventory_ 庫存組織單位
MTL_PARAMETERS -庫存組織參數(shù)（沒有用ID，直接用name）
MTL_SYSTEM_ITEMS_b -物料信息（同上，應(yīng)用了庫存組織name）
MTL_SECONDARY_INVENTORIES -子庫存組織 -?
MTL_ITEM_LOCATTIONS -貨位 - SUBINVENTROY_CODE
Mtl_Material_Transactions - (庫存)物料事物表
成本 mtl_transaction_accounts
transaction_cost是事物成本；
ACTUAL_COST是通過成本算法計(jì)算出來的實(shí)際成本，主計(jì)量單位
現(xiàn)有量
匯總歷史記錄（正負(fù)合計(jì)）
Mtl_Material_Transactions
MTL_ONHAND_QUANTITIES現(xiàn)有量表，組織/子庫存/貨位/物品 summary可能按照挑庫先進(jìn)先出統(tǒng)計(jì)，如果設(shè)置了"不允許負(fù)庫存"，這樣就不可能出現(xiàn)負(fù)數(shù)
PO
請購單頭表
Po_Requisition_Headers_all
行表
Po_Requisition_lines_all
采購訂單
PO_HEADER_ALL
PO_LINES_ALL
采購接收－退貨/組織間轉(zhuǎn)移/正常狀態(tài) 都需要使用這個模塊
RCV_TRANSACTIONS
1. 接收100單位貨物,放入“待質(zhì)檢”貨位
2. 接受/拒絕
3. 庫存/退回
有三個不同的狀態(tài)！例如：接收100個，80個接受入庫，20個退回，那么有80個接受事務(wù)/20個退回事物
select TRANSACTION_TYPE，DESTINATION_TYPE_CODE from RCV_TRANSACTIONS
可以看出以下階段：
A1.RECEIVE – RECEIVING
A2.ACCEPT – RECEIVING
A3.DELIERY – INVETORY(影響庫存現(xiàn)有量)
如果按照正常模式，最后會觸發(fā)產(chǎn)生MTL_MATERIAL_TRANSACTIONS
銷售訂單
OE_ORDER_headers_all
SOLD_FROM_ORG_ID
SOLD_TO_ORG_ID 就是客戶層
SHIP_FROM_ORG_ID
SHIP_TO_ORG_ID 就是客戶收貨層
INVOICE_TO_ORG_ID 就是客戶收單層
DELIVER_TO_ORG_ID
和客戶結(jié)構(gòu)有關(guān)
客戶 RA_customers?
客戶Address Ra_Addresses
Address 貨品抵達(dá) site RA_SITE_USES_ALL
Address 發(fā)票抵達(dá) site?
OE_ORDER_LINEs_all
GL憑證
gl_je_batches
憑證日期:??????????????? DEFAULT_EFFECTIVE_DATE
會計(jì)期間:??????????????? DEFAULT_PERIOD_NAME
原幣種憑證批借貸方匯總: RUNNING_TOTAL_DR/CR 比如美元
本位幣憑證批借貸方匯總: RUNNING_TOTAL_ACCOUNTED_DR/CR?
gl_je_headers日記賬頭信息
批號:???? JE_BATCH_ID
會計(jì)期間: PERIOD_NAME
幣種:???? CURRENCY_CODE
匯率類型: CURRENCY_CONVERSION_TYPE
匯率日期: CURRENCY_CONVERSION_DATE
帳套:???? SET_OF_BOOKS_ID 參考 GL_SETS_OF_BOOKS
憑證類型: JE_CATEGORY 參考 GL_JE_SOURCES
憑證來源: JE_SOURCE
gl_je_lines日記賬體信息
CODE_COMBINATION_ID 科目組合編號
GL_BALANCES 總帳余額
PERIOD_NET_DR/CR 凈值
BEGIN_BALANCE_DR/CR 期初額
AR應(yīng)收發(fā)票
RA_CUSTOMER_TRX_ALL
??? CUSTOMER_TRX_ID 發(fā)票編號
??? BILL_TO_SITE_USE_ID 客戶收單方編號
??? PRIMARY_SALES_ID銷售員
REFERENCE是Oracle提供的外部編號輸入框，但是由于版本問題和長度(<=30)，不建議用戶使用，如果要使用外部編號，請使用說明性彈性域
RA_CUSTOMER_TRX_LINES_ALL
??? LINE_ID??????????? 行號
??? INVENTORY_ITEM_ID 可以為空，比如非物料的服務(wù)，只在DE script ION中出現(xiàn) /稅行
??? DE script ION??????
??? QUANTITY_INVOICE?? 開票數(shù)量
??? LINE_TYPE????????? 行類型 (一般/稅)
??? EXTEND_PRICE?????? 本行金額
注意：稅行是隱藏行，所以至少會有兩行
收款情況
AR_CASH_RECEIPTS_ALL（還包含了非收款信息）
??? CASH_RECEIPT_ID 內(nèi)部code
??? RECEIPT_NUMBER?? 收款號
??? RECEIPT_DATE???? 收款日期
??? AMOUNT?????????? 總額
??? RECEIPT_TYPE???? 現(xiàn)金/雜項(xiàng) Cash/Misc
??? FUNCTIONAL_AMOUNT 本位幣計(jì)量金額
?? UI上為RECEIPTS?
?? 核銷關(guān)系不是一一對應(yīng)，也不是一次核銷100%,UI上右下方的Application 按鈕
?? AR_RECEIVABLE_APPLICATIONS_ALL
??? APPLIED_CUSTOMER_TRX_ID?? 發(fā)票編號
??? APPLIED_CUSTOMER_TRX_LINE_ID?? 發(fā)票行編號
??? STATUS APP表示核銷 /UNAPP表示未核銷
??????? AMOUNT_APPLIED???????????????? 匹配金額
??? 注意:紅沖收款報(bào)表時(shí)間跨月的問題；必須聯(lián)查 AR_CASH_RECEIPTS_ALL和?? AR_CASH_RECEIPT_HISTORY_ALL
AP
應(yīng)付帳款(是我方人員按照供應(yīng)商提供的紙張發(fā)票信息錄入)UI 上的invoice
AP_INVOICES_ALL
實(shí)際付款PAYMENT
AP_CHECKS_ALL
核銷關(guān)系 同AR，右下方的Payment 按鈕
AP_INVOICE_PAYMENTS_ALL客戶余額表，情況比較復(fù)雜:比如兩個用戶合并，應(yīng)收應(yīng)付差額，預(yù)付款
資產(chǎn)信息FA_ADDITIONS
名稱
編號
分類
數(shù)量
資產(chǎn)類別
FA_CATEGORIES
資產(chǎn)帳簿
FA_BOOK_CONTROLS 和會計(jì)帳簿有什么關(guān)系？
FA_BOOKS?
UI中的Inquiry
Mothed是折舊方法(直線法/產(chǎn)量法)
FA_DISTRIBUTION_HISTORY分配assignment，給什么部門使用多少
LOCATION_ID 部門 聯(lián)查FA_LOCATIONS
折舊信息(分?jǐn)偡椒?
FA_DEPRN_DETAIL
period_counter 折舊期間編號
折舊事務(wù)(新增、重建、轉(zhuǎn)移、報(bào)廢)
FA_TRANSACTION_HEADERS
========================華麗的分割線=========================
fnd_user －－－ 系統(tǒng)用戶表
po_vendors －－－ 供應(yīng)商信息表
po_vendor_sites －－－ 供應(yīng)商地點(diǎn)信息表
hr_organization_units －－－ 組織及庫存組織表
per_people_f －－－ 員工表
wip_entities －－－ 作業(yè)名信息表
wip_discrete_jobs －－－ 離散作業(yè)表
wip_requirement_operations －－－ 作業(yè)名物料需求發(fā)放表
po_headers_all －－－ 采購訂單頭表
po_lines_all －－－ 采購訂單行表
po_line_locations_all －－－ 采購行地點(diǎn)表
rcv_transactions －－－ 接收交易表
bom_bill_of_materials －－－ 物料清單表
bom_inventory_components －－－ 物料清單構(gòu)成表
mtl_system_items －－－ 物料主表
mtl_onhand_quantities －－－ 庫存數(shù)據(jù)表
mtl_item_locations －－－ 項(xiàng)目貨位表
mtl_material_transactions －－－ 出入庫記錄表
mtl_supply －－－ 供應(yīng)表
mtl_demand －－－ 需求表
參考二：??????????????????????????????????????????????????
1. OU、庫存組織?
SELECT hou.organization_id ou_org_id, --org_id
hou.name ou_name, --ou 名稱
ood.organization_id org_org_id, -- 庫存組織 id
ood.organization_code org_org_code, -- 庫存組織代碼
msi.secondary_inventory_name, -- 子庫存名稱
msi.description -- 子庫存描述
FROM hr_organization_information hoi, -- 組織分類表
hr_operating_units hou, --ou 視圖
org_organization_definitions ood, -- 庫存組織定義視圖
mtl_secondary_inventories msi -- 子庫存信息表
WHERE hoi.org_information1 = 'OPERATING_UNIT'
AND hoi.organization_id = hou.organization_id
AND ood.operating_unit = hoi.organization_id
AND ood.organization_id = msi.organization_id
-- 獲取系統(tǒng) ID
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
2. 用戶、責(zé)任及 HR
-- 系統(tǒng)責(zé)任定義 VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)
SELECT APPLICATION_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_KEY,
END_DATE,
RESPONSIBILITY_NAME,
DESCRIPTION
FROM FND_RESPONSIBILITY_VL;
-- 用戶責(zé)任關(guān)系
SELECT USER_ID, RESPONSIBILITY_ID FROM FND_USER_RESP_GROUPS;
-- 用戶表
SELECT USER_ID, USER_NAME, EMPLOYEE_ID, PERSON_PARTY_ID, END_DATE
FROM FND_USER;
-- 人員表 VIEW
SELECT PERSON_ID,
START_DATE,
DATE_OF_BIRTH,
EMPLOYEE_NUMBER,
NATIONAL_IDENTIFIER,
SEX,
FULL_NAME
FROM per_people_f;
-- 綜合查詢
SELECT USER_NAME, FULL_NAME, RESPONSIBILITY_NAME, CC.DESCRIPTION
FROM FND_USER AA,
FND_USER_RESP_GROUPS BB,
FND_RESPONSIBILITY_VL CC,
per_people_f DD
WHERE AA.USER_ID = BB.USER_ID
AND BB.RESPONSIBILITY_ID = CC.RESPONSIBILITY_ID
AND AA.EMPLOYEE_ID = DD.PERSON_ID
AND RESPONSIBILITY_NAME like '% 供應(yīng)處 %'
ORDER BY USER_NAME;
-- 綜合查詢
-- 人員狀況基本信息表
SELECT PAF.PERSON_ID 系統(tǒng) ID,
PAF.FULL_NAME 姓名 ,
PAF.DATE_OF_BIRTH 出生日期 ,
PAF.REGION_OF_BIRTH 出生地區(qū) ,
PAF.NATIONAL_IDENTIFIER 身份證號 ,
PAF.ATTRIBUTE1 招工來源 ,
PAF.ATTRIBUTE3 員工類型 ,
PAF.ATTRIBUTE11 集團(tuán)合同號 ,
PAF.original_date_of_hire 參加工作日期 ,
PAF.PER_INFORMATION17 省份 ,
DECODE (PAF.SEX, 'M' , ' 男 ' , 'F' , ' 女 ' , 'NULL' ) 性別 , --decode 適合和同一值做比較有多種結(jié)果，不適合和多種值比較有多種結(jié)果
CASE PAF.SEX
WHEN 'M' THEN ' 男 '
WHEN 'F' THEN ' 女 '
ELSE 'NULL'
END 性別 1, --case 用法一
CASE WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1960' THEN '50 年代 '
WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1970' THEN '60 年代 '
WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1980' THEN '70 年代 '
WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '1990' THEN '80 年代 '
WHEN TO_CHAR(PAF.DATE_OF_BIRTH, 'YYYY' ) < '2000' THEN '90 年代 '
ELSE '21 世紀(jì) ' --case 用法二
END 出生年代
FROM PER_ALL_PEOPLE_F PAF
?
?
?
3. 供應(yīng)商 VENDOR
-- 供應(yīng)商主表數(shù)據(jù)：
SELECT ass.vendor_id vendor_id,
ass.party_id party_id,
ass.segment1 vendor_code,
ass.vendor_name vendor_name,
ass.vendor_name vendor_short_name,
ass.vendor_type_lookup_code vendor_type,
flv.meaning vendor_type_meaning,
hp.tax_reference tax_registered_name,
ass.payment_method_lookup_code payment_method,
att.name term_name,
att.enabled_flag enabled_flag,
att.end_date_active end_date_active,
ass.creation_date creation_date,
ass.created_by created_by,
ass.last_update_date last_update_date,
ass.last_updated_by last_updated_by,
ass.last_update_login last_update_login
FROM ap_suppliers ass,
fnd_lookup_values flv,
hz_parties hp,
ap_terms_tl att
WHERE ass.vendor_type_lookup_code = flv.lookup_code(+)
AND flv.lookup_type(+) = 'VENDOR TYPE'
AND flv.language(+) = userenv ( 'LANG' )
AND ass.party_id = hp.party_id
AND att.language = userenv ( 'LANG' )
AND ass.terms_id = att.term_id(+)
-- 供應(yīng)商銀行信息
SELECT ass.vendor_id vendor_id,
ass.party_id party_id,
bank.party_id bank_id,
bank.party_name bank_name,
branch.party_id branch_id,
branch.party_name bank_branch_name,
ieba.bank_account_num bank_account_num
FROM ap_suppliers ass,
hz_parties hp,
iby_account_owners iao,
iby_ext_bank_accounts ieba,
hz_parties bank,
hz_parties branch
WHERE ass.party_id = hp.party_id
AND hp.party_id = iao.account_owner_party_id(+)
AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)
AND ieba.bank_id = bank.party_id(+)
AND ieba.branch_id = branch.party_id(+)
ORDER BY ieba.creation_date;
-- 供應(yīng)商開戶行地址信息
SELECT hps.party_id party_id,
hps.party_site_id party_site_id,
hl.location_id location_id,
hl.country country,
hl.province province,
hl.city city,
hl.address1 address1,
hl.address2 address2,
hl.address3 address3,
hl.address4 address4
FROM hz_party_sites hps, hz_locations hl
WHERE hps.location_id = hl.location_id
ORDER BY hps.creation_date
-- 供應(yīng)商聯(lián)系人信息
SELECT hr.subject_id subject_id,
hr.object_id object_id,
hr.party_id party_id,
hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||
hp.person_first_name contact_person,
hcpp.phone_area_code phone_area_code,
hcpp.phone_number phone_number,
hcpp.phone_extension phone_extension,
hcpf.phone_area_code fax_phone_area_code,
hcpf.phone_number fax_phone_number,
hcpe.email_address email_address
FROM hz_relationships hr,
hz_contact_points hcpp,
hz_contact_points hcpf,
hz_contact_points hcpe,
hz_parties hp
WHERE hr.object_id = hp.party_id
AND hcpp.owner_table_id(+) = hr.party_id
AND hcpf.owner_table_id(+) = hr.party_id
AND hcpe.owner_table_id(+) = hr.party_id
AND hr.object_type = 'PERSON'
AND hr.relationship_code(+) = 'CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpf.phone_line_type(+) = 'FAX'
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.phone_line_type IS NULL
ORDER BY hr.creation_date;
-- 供應(yīng)商地址主信息
SELECT assa.vendor_site_id vendor_site_id,
assa.vendor_id vendor_id,
assa.vendor_site_code vendor_code,
assa.vendor_site_code address_short_name,
assa.address_line1 address_line1,
assa.address_line2 address_line2,
assa.address_line3 address_line3,
assa.address_line4 address_line4,
assa.org_id org_id,
assa.country country,
assa.province province,
assa.city city,
assa.county county,
assa.zip zip,
assa.pay_site_flag pay_site_flag,
assa.purchasing_site_flag purchasing_site_flag,
assa.inactive_date inactive_date,
assa.creation_date creation_date,
assa.created_by created_by,
assa.last_update_date last_update_date,
assa.last_updated_by last_updated_by,
assa.last_update_login last_update_login
FROM ap_suppliers ass, ap_supplier_sites_all assa
WHERE assa.vendor_id = ass.vendor_id;
-- 供應(yīng)商地址聯(lián)系人信息： phone 、 fax 和 Email
SELECT hcpp.phone_area_code phone_area_code,
hcpp.phone_number phone_number,
hcpp.phone_extension phone_extension,
hcpf.phone_area_code fax_phone_area_code,
hcpf.phone_number fax_phone_number,
hcpe.email_address email_address
FROM ap_supplier_sites_all assa,
hz_contact_points hcpp,
hz_contact_points hcpf,
hz_contact_points hcpe,
hz_party_sites hps
WHERE assa.party_site_id = hps.party_site_id
AND hcpp.owner_table_id(+) = assa.party_site_id
AND hcpf.owner_table_id(+) = assa.party_site_id
AND hcpe.owner_table_id(+) = assa.party_site_id
AND hcpp.owner_table_name(+) = 'HZ_PARTY_SITES'
AND hcpf.owner_table_name(+) = 'HZ_PARTY_SITES'
AND hcpe.owner_table_name(+) = 'HZ_PARTY_SITES'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpf.phone_line_type(+) = 'FAX'
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.phone_line_type IS NULL ;
-- 供應(yīng)商地址收件人信息
SELECT assa.party_site_id
FROM ap_supplier_sites_all assa
-- 根據(jù) party_site_id 得到供應(yīng)商地址的收件人名稱
SELECT hps.addressee FROM hz_party_sites hps;
-- 供應(yīng)商銀行帳戶分配層次關(guān)系
SELECT * FROM iby_pmt_instr_uses_all;
-- 供應(yīng)商銀行帳戶分配層次關(guān)系明細(xì) ( 不包括供應(yīng)商層的分配信息 ):
SELECT * FROM iby_external_payees_all;
?
4. 客戶 CUSTOMER
--SQL 查詢
-- 客戶賬戶表 以許繼 1063 電網(wǎng)客戶為例 -->>PARTY_ID = 21302
SELECT * FROM hz_cust_accounts AA WHERE AA.CUST_ACCOUNT_ID = 1063 ;
?
-- 客戶名稱及地址全局信息表 -->> PARTY_NUMBER = 19316
SELECT * FROM hz_parties AA WHERE AA.PARTY_ID = 21302 ;
?
-- 客戶地點(diǎn)賬戶主文件
SELECT * FROM hz_cust_acct_sites_all WHERE CUST_ACCOUNT_ID = 1063 ;
?
-- 客戶地點(diǎn) ( 關(guān)聯(lián) hz_cust_acct_sites_all)
SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID = 21302 ;
?
-- 地點(diǎn)地址名稱 ( 關(guān)聯(lián) hz_cust_acct_sites_all)
SELECT AA.ADDRESS1, AA.ADDRESS_KEY
FROM HZ_LOCATIONS AA, HZ_PARTY_SITES BB
WHERE AA.LOCATION_ID = BB.LOCATION_ID
AND BB.PARTY_ID = 21302 ;
?
-- 客戶地點(diǎn)業(yè)務(wù)目的 ( 關(guān)聯(lián) hz_cust_acct_sites_all 用 CUST_ACCT_SITE_ID)
SELECT * FROM HZ_CUST_SITE_USES_ALL;
?
-- 客戶地點(diǎn)詳細(xì)信息表，以供應(yīng)處 OU 的身份 ORG_ID = 119
SELECT AA.PARTY_SITE_ID 客戶組織地點(diǎn) ID,
AA.PARTY_ID 客戶組織 ID,
AA.LOCATION_ID 地點(diǎn) ID,
AA.PARTY_SITE_NUMBER 地點(diǎn)編號 ,
AA.IDENTIFYING_ADDRESS_FLAG 地址標(biāo)示 ,
AA.STATUS 有效否 ,
AA.PARTY_SITE_NAME,
BB.ORG_ID 業(yè)務(wù)實(shí)體 ,
BB.bill_to_flag 收單標(biāo)示 ,
BB.ship_to_flag 收貨標(biāo)示 ,
CC.ADDRESS1 地點(diǎn)名稱 ,
DD.SITE_USE_ID,
DD.SITE_USE_CODE,
DD.PRIMARY_FLAG,
DD.STATUS,
DD.LOCATION 業(yè)務(wù)目的 ,
DD.BILL_TO_SITE_USE_ID 收單地 ID,
DD.TAX_CODE
FROM hz_party_sites AA,
hz_cust_acct_sites_all BB,
hz_locations CC,
HZ_CUST_SITE_USES_ALL DD
WHERE AA.PARTY_SITE_ID = BB.PARTY_SITE_ID
AND BB.CUST_ACCOUNT_ID = 1063
AND BB.ORG_ID = 119
AND AA.STATUS = 'A'
AND AA.LOCATION_ID = CC.LOCATION_ID
AND BB.CUST_ACCT_SITE_ID(+) = DD.CUST_ACCT_SITE_ID
AND DD.STATUS <> 'I' ;
?
--************* 綜合查詢 ************--
-- 客戶主數(shù)據(jù)
SELECT hca.cust_account_id customer_id,
hp.party_number customer_number,
hp.party_name customer_name,
hp.party_name customer_short_name,
hca.customer_type customer_type,
alt.meaning customer_type_meaning,
hca.customer_class_code customer_class,
alc.meaning customer_class_meaning,
hp.tax_reference tax_registered_name,
rt.name term_name,
hca.creation_date creation_date,
hca.created_by created_by,
hca.last_update_date last_update_date,
hca.last_updated_by last_updated_by,
hca.last_update_login last_update_login
FROM hz_parties hp,
hz_cust_accounts hca,
ar_lookups alt,
ar_lookups alc,
hz_customer_profiles hcp,
ra_terms rt
WHERE hp.party_id = hca.party_id
AND hca.customer_type = alt.lookup_code(+)
AND alt.lookup_type = 'CUSTOMER_TYPE'
AND hca.customer_class_code = alc.lookup_code(+)
AND alc.lookup_type(+) = 'CUSTOMER CLASS'
AND hca.cust_account_id = hcp.cust_account_id(+)
AND hcp.standard_terms = rt.term_id(+)
?
-- 客戶收款方法 SQL
SELECT arm.name receipt_method_name
FROM hz_cust_accounts hca,
ra_cust_receipt_methods rcrm,
ar_receipt_methods arm
WHERE hca.cust_account_id = rcrm.customer_id
AND rcrm.receipt_method_id = arm.receipt_method_id
ORDER BY rcrm.creation_date;
-- 客戶賬戶層銀行賬戶信息 SQL
SELECT hca.cust_account_id cust_account_id,
hp.party_id party_id,
bank.party_id bank_id,
bank.party_name bank_name,
branch.party_id branch_id,
branch.party_name bank_branch_name,
ieba.bank_account_num bank_account_num
FROM hz_cust_accounts hca,
hz_parties hp,
iby_account_owners iao,
iby_ext_bank_accounts ieba,
hz_parties bank,
hz_parties branch
WHERE hca.party_id = hp.party_id
AND hp.party_id = iao.account_owner_party_id(+)
AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)
AND ieba.bank_id = bank.party_id(+)
AND ieba.branch_id = branch.party_id(+)
ORDER BY ieba.creation_date;
-- 客戶開戶行地址信息 SQL
SELECT hl.country || '-' || hl.province || '-' || hl.city || '-' ||
hl.address1 || '-' || hl.address2 || '-' || hl.address3 || '-' ||
hl.address4 bank_address
FROM hz_party_sites hps, hz_locations hl
WHERE hps.location_id = hl.location_id
ORDER BY hps.creation_date;
-- 客戶賬戶層聯(lián)系人信息：聯(lián)系人、電話、手機(jī)和 Email SQL
SELECT hr.party_id party_id,
hcar.cust_account_id cust_account_id,
hcar.cust_acct_site_id cust_acct_site_id,
hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||
hp.person_first_name contact_person,
hcpp.phone_area_code phone_area_code,
hcpp.phone_number phone_number,
hcpp.phone_extension phone_extension,
hcpm.phone_area_code mobile_phone_area_code,
hcpm.phone_number mobile_phone_number,
hcpe.email_address email_address
FROM hz_relationships hr,
hz_cust_account_roles hcar,
hz_org_contacts hoc,
hz_contact_points hcpp,
hz_contact_points hcpm,
hz_contact_points hcpe,
hz_parties hp,
hz_cust_accounts hca
WHERE hr.object_id = hp.party_id
AND hr.party_id = hcar.party_id
AND hr.relationship_id = hoc.party_relationship_id(+)
AND hcpp.owner_table_id(+) = hr.party_id
AND hcpm.owner_table_id(+) = hr.party_id
AND hcpe.owner_table_id(+) = hr.party_id
AND hr.object_type = 'PERSON'
AND hr.relationship_code(+) = 'CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpm.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpm.contact_point_type(+) = 'PHONE'
AND hcpm.phone_line_type(+) = 'MOBILE'
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.phone_line_type IS NULL
AND hr.subject_id = hca.party_id
AND hcar.cust_acct_site_id IS NULL
ORDER BY hr.creation_date;
-- 客戶地址
SELECT hcasa.cust_acct_site_id customer_site_id,
hcasa.cust_account_id customer_id,
hps.party_site_number customer_site_code,
hps.party_site_name customer_site_name,
hl.address1 address_line1,
hl.address2 address_line2,
hl.address3 address_line3,
hl.address4 address_line4,
hcasa.org_id org_id,
hl.country country,
hl.province province,
hl.city city,
hl.county county,
hl.postal_code zip,
hcasa.bill_to_flag bill_to_flag,
hcasa.ship_to_flag ship_to_flag,
hca.creation_date creation_date,
hca.created_by created_by,
hca.last_update_date last_update_date,
hca.last_updated_by last_updated_by,
hca.last_update_login last_update_login
FROM hz_cust_accounts hca,
hz_cust_acct_sites_all hcasa,
hz_party_sites hps,
hz_locations hl
WHERE hca.cust_account_id = hcasa.cust_account_id
AND hcasa.party_site_id = hps.party_site_id
AND hps.location_id = hl.location_id;
?
-- 客戶賬戶層地址 contact person 信息 :phone,mobile,email
SELECT hr.party_id party_id,
hcar.cust_account_id cust_account_id,
hcar.cust_acct_site_id cust_acct_site_id,
hp.person_last_name || ' ' || hp.person_middle_name || ' ' ||
hp.person_first_name contact_person,
hcpp.phone_area_code phone_area_code,
hcpp.phone_number phone_number,
hcpp.phone_extension phone_extension,
hcpm.phone_area_code mobile_phone_area_code,
hcpm.phone_number mobile_phone_number,
hcpe.email_address email_address
FROM hz_relationships hr,
hz_cust_account_roles hcar,
hz_org_contacts hoc,
hz_contact_points hcpp,
hz_contact_points hcpm,
hz_contact_points hcpe,
hz_parties hp,
hz_cust_accounts hca
WHERE hr.object_id = hp.party_id
AND hr.party_id = hcar.party_id
AND hr.relationship_id = hoc.party_relationship_id(+)
AND hcpp.owner_table_id(+) = hr.party_id
AND hcpm.owner_table_id(+) = hr.party_id
AND hcpe.owner_table_id(+) = hr.party_id
AND hr.object_type = 'PERSON'
AND hr.relationship_code(+) = 'CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpm.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpm.contact_point_type(+) = 'PHONE'
AND hcpm.phone_line_type(+) = 'MOBILE'
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.phone_line_type IS NULL
AND hr.subject_id = hca.party_id
AND hca.cust_account_id = hcar.cust_account_id
ORDER BY hr.creation_date;
?
-- 客戶賬戶地點(diǎn)地址
SELECT hp.party_id,
hca.cust_account_id,
hcasa.cust_acct_site_id,
hcasa.bill_to_flag,
hcasa.ship_to_flag,
hcsua.site_use_id,
hcasa.party_site_id,
hcsua.site_use_code,
hcsua.primary_flag,
hcsua.location,
hcsua.org_id
FROM hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hps,
hz_cust_acct_sites_all hcasa,
hz_cust_site_uses_all hcsua
WHERE hp.party_id = hca.party_id
AND hca.cust_account_id = hcasa.cust_account_id
AND hcasa.party_site_id = hps.party_site_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
-- 客戶主配置文件
SELECT * FROM hz_cust_profile_classes;
SELECT * FROM hz_customer_profiles;
SELECT * FROM hz_cust_prof_class_amts;
SELECT * FROM hz_cust_profile_amts;
?
5. 訂單 OE
--
select * from oe_order_headers_all 銷售頭
select * from oe_order_lines_all 銷售行
select * from wsh_new_deliveries 發(fā)送
select * from wsh_delivery_details
select * from wsh_delivery_assignments
-- 綜合查詢 1- 未結(jié)銷售訂單
SELECT H.ORDER_NUMBER 銷售訂單 ,
h.cust_po_number 客戶 PO,
cust.account_number 客戶編碼 ,
hp.party_name 客戶名稱 ,
ship_use.location 收貨地 ,
bill_use.location 收單地 ,
h.ordered_date 訂單日期 ,
H.ATTRIBUTE1 合同號 ,
h.attribute2 屏號 ,
h.attribute3 來源編碼 ,
l.line_number 行號 ,
l.ordered_item 物料 ,
msi.description 物料說明 ,
l.order_quantity_uom 訂購單位 ,
l.ordered_quantity 訂購數(shù)量 ,
l.cancelled_quantity 取消數(shù)量 ,
l.shipped_quantity 發(fā)運(yùn)數(shù)量 ,
l.schedule_ship_date 計(jì)劃發(fā)運(yùn)日期 ,
l.booked_flag 登記標(biāo)記 ,
ol.MEANING 工作流狀態(tài) ,
l.cancelled_flag 取消標(biāo)記
FROM OE_ORDER_HEADERS_ALL H,
OE_ORDER_LINES_ALL L,
HZ_CUST_ACCOUNTS CUST,
hz_parties hp,
hz_cust_site_uses_all ship_use,
hz_cust_site_uses_all bill_use,
mtl_system_items_b msi,
oe_lookups ol
WHERE 1 = 1
AND H.HEADER_ID = L.HEADER_ID
AND H.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID
and cust.party_id = hp.party_id
and h.ship_to_org_id = ship_use.site_use_id
and h.invoice_to_org_id = bill_use.site_use_id
and l.flow_status_code not in ( 'CLOSED' , 'CANCELLED' )
and l.inventory_item_id = msi.inventory_item_id
and msi.organization_id = 141
and l.flow_status_code = ol.LOOKUP_CODE
and ol.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
AND CUST.ACCOUNT_NUMBER IN ( '91010072' , '91010067' , '91010036' )
order by party_name ，收貨地，銷售訂單 ;
6. 采購申請 PR
-- 申請單頭 （以電網(wǎng)組織 ORG_ID=112 內(nèi)部申請 =14140002781 為例
SELECT PRH.REQUISITION_HEADER_ID 申請單頭 ID,
PRH.PREPARER_ID,
PRH.Org_Id OU_ID,
PRH.SEGMENT1 申請單編號 ,
PRH.Creation_Date 創(chuàng)建日期 ,
PRH.Created_By 編制人 ID,
FU.USER_NAME 用戶名稱 ,
PP.FULL_NAME 用戶姓名 ,
PRH.Approved_Date 批準(zhǔn)日期 ,
PRH.Description 說明 ,
PRH.Authorization_Status 狀態(tài) ,
PRH.Type_Lookup_Code 類型 ,
PRH.Transferred_To_Oe_Flag 傳遞標(biāo)示
FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP
WHERE PRH.CREATED_BY = FU.USER_ID
AND FU.EMPLOYEE_ID = PP.PERSON_ID
AND PRH.ORG_ID = 112
AND PRH.SEGMENT1 = '14140002781' ;
-->> 內(nèi)部申請 =14140002781 申請單頭 ID = 3379
-- 申請單行明細(xì)
SELECT PRL.REQUISITION_HEADER_ID 申請單 ID,
PRL.REQUISITION_LINE_ID 行 ID,
PRL.LINE_NUM 行號 ,
PRL.CATEGORY_ID 分類 ID,
PRL.ITEM_ID 物料 ID,
ITEM.SEGMENT1 物料編碼 ,
PRL.ITEM_DESCRIPTION 物料說明 ,
PRL.Quantity 需求數(shù) ,
PRL.Quantity_Delivered 送貨數(shù) ,
PRL.Quantity_Cancelled 取消數(shù) ,
PRL.Unit_Meas_Lookup_Code 單位 ,
PRL.Unit_Price 參考價(jià) ,
PRL.Need_By_Date 需求日期 ,
PRL.Source_Type_Code 來源類型 ,
PRL.Org_Id OU_ID,
PRL.Source_Organization_Id 對方組織 ID,
PRL.Destination_Organization_Id 本方組織 ID
from PO_REQUISITION_LINES_ALL PRL,MTL_SYSTEM_ITEMS ITEM
WHERE PRL.ORG_ID = 112
AND PRL.ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND PRL.Destination_Organization_Id = ITEM.ORGANIZATION_ID
AND PRL.REQUISITION_HEADER_ID = 3379 ;
-- 申請單頭 ( 加對方訂單編號 )
SELECT PRH.REQUISITION_HEADER_ID 申請單頭 ID,
PRH.PREPARER_ID,
PRH.Org_Id OU_ID,
PRH.SEGMENT1 申請單編號 ,
PRH.Creation_Date 創(chuàng)建日期 ,
PRH.Created_By 編制人 ID,
FU.USER_NAME 用戶名稱 ,
PP.FULL_NAME 用戶姓名 ,
PRH.Approved_Date 批準(zhǔn)日期 ,
PRH.Description 說明 ,
PRH.Authorization_Status 狀態(tài) ,
PRH.Type_Lookup_Code 類型 ,
PRH.Transferred_To_Oe_Flag 傳遞標(biāo)示 ,
OEH.ORDER_NUMBER 對方 CO 編號
FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP,OE_ORDER_HEADERS_ALL OEH
WHERE PRH.CREATED_BY = FU.USER_ID
AND FU.EMPLOYEE_ID = PP.PERSON_ID
AND PRH.REQUISITION_HEADER_ID = OEH.SOURCE_DOCUMENT_ID(+)
AND PRH.ORG_ID = 112
AND PRH.SEGMENT1 = '14140002781' ;
--( 銷售訂單記錄有對方 OU_ID, 申請單關(guān)鍵字 SOURCE_DOCUMENT_ID 申請單號 SOURCE_DOCEMENT_REF)
?
******************* 綜合查詢類 *******************
-- 申請單頭綜合查詢 （進(jìn)限制只能查詢 -- 電網(wǎng)組織 ORG_ID=112)
SELECT PRH.REQUISITION_HEADER_ID 申請單頭 ID,
PRH.Org_Id 組織 ID,
PRH.SEGMENT1 申請單編號 ,
PRH.Creation_Date 創(chuàng)建日期 ,
PRH.Created_By 編制人 ID,
FU.USER_NAME 用戶名稱 ,
PP.FULL_NAME 用戶姓名 ,
PRH.Approved_Date 批準(zhǔn)日期 ,
PRH.Description 說明 ,
PRH.Authorization_Status 狀態(tài) ,
PRH.Type_Lookup_Code 類型 ,
PRH.Transferred_To_Oe_Flag 傳遞標(biāo)示 ,
PRL.REQUISITION_LINE_ID 行 ID,
PRL.LINE_NUM 行號 ,
PRL.CATEGORY_ID 分類 ID,
PRL.ITEM_ID 物料 ID,
ITEM.SEGMENT1 物料編碼 ,
PRL.ITEM_DESCRIPTION 物料說明 ,
PRL.Quantity 需求數(shù) ,
PRL.Quantity_Delivered 送貨數(shù) ,
PRL.Quantity_Cancelled 取消數(shù) ,
PRL.Unit_Meas_Lookup_Code 單位 ,
PRL.Unit_Price 參考價(jià) ,
PRL.Need_By_Date 需求日期 ,
PRL.Source_Type_Code 來源類型 ,
PRL.Source_Organization_Id 對方組織 ID,
PRL.Destination_Organization_Id 本方組織 ID
FROM PO_REQUISITION_HEADERS_ALL PRH,
FND_USER FU,
per_people_f PP,
PO_REQUISITION_LINES_ALL PRL,
MTL_SYSTEM_ITEMS ITEM
WHERE PRH.CREATED_BY = FU.USER_ID
AND FU.EMPLOYEE_ID = PP.PERSON_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRH.Org_Id = PRL.ORG_ID
AND PRL.ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND PRL.Destination_Organization_Id = ITEM.ORGANIZATION_ID
AND PRH.ORG_ID = 112 ;
?
-- 若需創(chuàng)建視圖只需在 SELECT 語句前加上
CREATE OR REPLACE VIEW CUX_INV_PR112 AS
7. 采購訂單 PO
-- 采購單頭信息 TYPE_LOOKUP_CODE='STANDARD' （以供應(yīng)處 OU ORG_ID=119 采購單 ='' 為例）
-- 類型說明 TYPE_LOOKUP_CODE='STANDARD' 為采購單 TYPE_LOOKUP_CODE='BLANKET' 為采購協(xié)議
SELECT POH.ORG_ID OU_ID,
POH.PO_HEADER_ID 采購單頭 ID,
POH.TYPE_LOOKUP_CODE 類型 ,
POH.AUTHORIZATION_STATUS 狀態(tài) ,
POH.VENDOR_ID 供應(yīng)商 ID,
VENDOR.VENDOR_NAME 供應(yīng)商名 ,
POH.VENDOR_SITE_ID 供應(yīng)商地址 ID,
POH.VENDOR_CONTACT_ID 供應(yīng)商聯(lián)系人 ID,
POH.SHIP_TO_LOCATION_ID 本方收貨地 ID,
POH.BILL_TO_LOCATION_ID 本方收單地 ID,
POH.CREATION_DATE 創(chuàng)建日期 ,
POH.APPROVED_FLAG 審批 YN,
POH.APPROVED_DATE 審批日期 ,
POH.COMMENTS 采購單說明 ,
POH.TERMS_ID 條款 ID,
POH.AGENT_ID 采購員 ID,
AGT_PP.LAST_NAME 采購員 ,
POH.CREATED_BY 創(chuàng)建者 ID,
FU.USER_NAME 創(chuàng)建用戶 ,
PP.FULL_NAME 用戶姓名
FROM PO_HEADERS_ALL POH, FND_USER FU, per_people_f PP,PER_ALL_PEOPLE_F AGT_PP,ap_suppliers VENDOR
WHERE POH.CREATED_BY = FU.USER_ID
AND FU.EMPLOYEE_ID = PP.PERSON_ID
AND POH.AGENT_ID = AGT_PP.PERSON_ID
AND POH.VENDOR_ID=VENDOR.VENDOR_ID
AND POH.ORG_ID = 119
AND POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND POH.SEGMENT1 = '14730005436' ;
/*
FND_USER FU, per_people_f PP 用戶相關(guān)表
po_agents_name_v 采購員視圖 ----> PO_AGENTS.AGENT_ID = PER_ALL_PEOPLE_F.PERSON_ID 采購員相關(guān)表
ap_suppliers 供應(yīng)商主表
*/
?
-->> POH.SEGMENT1 = '14730005436' PO_HEADER_ID = 10068
-- 采購單行信息
SELECT POL.ORG_ID OU_ID,
POL.PO_HEADER_ID 采購單頭 ID,
POL.PO_LINE_ID 行 ID,
POL.LINE_NUM 行號 ,
POL.ITEM_ID 物料 ID,
ITEM.SEGMENT1 物料編碼 ,
POL.ITEM_DESCRIPTION 物料說明 ,
POL.UNIT_MEAS_LOOKUP_CODE 單位 ,
POL.UNIT_PRICE 單價(jià) ,
PO_LCT.QUANTITY 訂購數(shù) ,
PO_LCT.QUANTITY_RECEIVED 驗(yàn)收數(shù) ,
PO_LCT.QUANTITY_ACCEPTED 接收數(shù) ,
PO_LCT.QUANTITY_REJECTED 拒絕數(shù) ,
PO_LCT.QUANTITY_CANCELLED 取消數(shù) ,
PO_LCT.QUANTITY_BILLED 到票數(shù) ,
PO_LCT.PROMISED_DATE 承諾日期 ,
PO_LCT.NEED_BY_DATE 需求日期
FROM PO_LINES_ALL POL,
Po_Line_Locations_all PO_LCT,
MTL_SYSTEM_ITEMS ITEM
WHERE POL.ORG_ID = PO_LCT.ORG_ID
AND POL.PO_LINE_ID = PO_LCT.PO_LINE_ID
AND POL.ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND ITEM.ORGANIZATION_ID = 142
AND POL.Org_Id = 119
AND POL.PO_HEADER_ID = 10068 ;
-- 說明： Po_Line_Locations_all 系 “ 發(fā)運(yùn)表 ”
?
-- 綜合查詢 1 ，所分配給供應(yīng)處組織的物料，存在采購協(xié)議，但缺失采購員或缺失倉庫；
select MSIF.Segment1 物料編碼 ,
MSIF.Description 物料描述 ,
MSIF.LONG_DESCRIPTION 物料詳細(xì)描述 ,
--MSIF.primary_unit_of_measure 計(jì)量單位 ,
PRF.LAST_NAME 采購員 ,
MISD.subinventory_code 默認(rèn)接收庫存 ,
PLA.unit_price 未稅價(jià) ,
round (PLA.unit_price * ( 1 + ZRB.percentage_rate / 100 ), 2 ) 含稅價(jià) ,
PV.VENDOR_NAME 供應(yīng)商名稱
from apps.PO_HEADERS_ALL PHA,
apps.PO_LINES_ALL PLA,
apps.MTL_SYSTEM_ITEMS_FVL MSIF,
apps.MTL_ITEM_SUB_DEFAULTS MISD,
apps.PER_PEOPLE_F PRF,
apps.PO_VENDORS PV,
apps.PO_VENDOR_SITES_ALL PVSA,
apps.ZX_RATES_B ZRB
where PHA.Type_Lookup_Code = 'BLANKET' and PHA.Org_Id = 119 and
PHA.PO_HEADER_ID = PLA.Po_Header_Id and
PHA.Global_Agreement_Flag = 'Y' and PHA.Approved_Flag in ( 'Y' , 'R' ) and
NVL (PHA.end_Date, sysdate ) >= sysdate and
NVL (PLA.Expiration_Date, sysdate ) >= sysdate and
PLA.Cancel_Flag = 'N' and PLA.Item_Id = MSIF.INVENTORY_ITEM_ID and
MSIF.ORGANIZATION_ID = 142 and
MSIF.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+) and
MISD.ORGANIZATION_ID(+) = 142 and MISD.default_type(+) = 2 and
MSIF.BUYER_ID = PRF.PERSON_ID(+) and
PRF.EFFECTIVE_END_DATE(+) = to_date( '4712-12-31' , 'YYYY-MM-DD' ) and
PHA.VENDOR_ID = PV.VENDOR_ID and
PHA.Vendor_Site_Id = PVSA.VENDOR_SITE_ID and
PVSA.VAT_CODE = ZRB.tax_rate_code and
(MISD.subinventory_code is null or PRF.LAST_NAME is null )
?
-- 采購其他相關(guān)表
select * from po_distributions_all 分配
select * from po_releases_all
select * from rcv_shipment_headers 采購接收頭
select * from rcv_shipment_lines 采購接收行
select * from rcv_transactions 接收事務(wù)處理
select * from po_agents
select * from po_vendors
select * from po_vendor_sites_all
?
8. 庫存 INV
-- 物料主表
select MSI.ORGANIZATION_ID 組織 ID,
MSI.INVENTORY_ITEM_ID 物料 ID,
MSI.SEGMENT1 物料編碼 ,
MSI.DESCRIPTION 物料說明 ,
MSI.ITEM_TYPE 項(xiàng)目類型 ,
MSI.PLANNING_MAKE_BUY_CODE 制造或購買 ,
MSI.PRIMARY_UNIT_OF_MEASURE 基本度量單位 ,
MSI.BOM_ENABLED_FLAG BOM 標(biāo)志 ,
MSI.INVENTORY_ASSET_FLAG 庫存資產(chǎn)否 ,
MSI.BUYER_ID 采購員 ID,
MSI.PURCHASING_ENABLED_FLAG 可采購否 ,
MSI.PURCHASING_ITEM_FLAG 采購項(xiàng)目 ,
MSI.UNIT_OF_ISSUE 單位 ,
MSI.INVENTORY_ITEM_FLAG 是否為庫存 ,
MSI.LOT_CONTROL_CODE 是否批量 ,
MSI.RESERVABLE_TYPE 是否要預(yù)留 ,
MSI.STOCK_ENABLED_FLAG 能否庫存 ,
MSI.FIXED_DAYS_SUPPLY 固定提前期 ,
MSI.FIXED_LOT_MULTIPLIER 固定批量大小 ,
MSI.INVENTORY_PLANNING_CODE 庫存計(jì)劃方法 ,
MSI.MAXIMUM_ORDER_QUANTITY 最大定單數(shù) ,
MSI.MINIMUM_ORDER_QUANTITY 最小定單數(shù) ,
MSI.FULL_LEAD_TIME 固定提前期 ,
MSI.PLANNER_CODE 計(jì)劃員碼 ,
MISD.SUBINVENTORY_CODE 接收子倉庫 ,
MSI.SOURCE_SUBINVENTORY 來源子倉庫 ,
MSI.WIP_SUPPLY_SUBINVENTORY 供應(yīng)子倉庫 ,
MSI.ATTRIBUTE12 老編碼 ,
MSI.INVENTORY_ITEM_STATUS_CODE 物料狀態(tài) ,
MSS.SAFETY_STOCK_QUANTITY 安全庫存量
from mtl_system_items MSI, MTL_ITEM_SUB_DEFAULTS MISD,mtl_safety_stocks MSS
where MSI.ORGANIZATION_ID = MISD.ORGANIZATION_ID(+)
and MSI.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+)
and MSI.ORGANIZATION_ID = MSS.ORGANIZATION_ID(+)
and MSI.INVENTORY_ITEM_ID = MSS.INVENTORY_ITEM_ID(+)
and MSI.ORGANIZATION_ID = 1155
and MSI.SEGMENT1 = '18020200012'
-- 物料庫存數(shù)量
SELECT MOQ.ORGANIZATION_ID,
MOQ.INVENTORY_ITEM_ID,
MOQ.SUBINVENTORY_CODE,
SUM (MOQ.TRANSACTION_QUANTITY) QTY
FROM mtl_onhand_quantities MOQ
WHERE MOQ.INVENTORY_ITEM_ID = 12781
AND MOQ.ORGANIZATION_ID = 1155
GROUP BY MOQ.ORGANIZATION_ID, MOQ.INVENTORY_ITEM_ID, MOQ.SUBINVENTORY_CODE;
-- 移動平均成本
SELECT CST.INVENTORY_ITEM_ID ITEM_ID,
CST.ORGANIZATION_ID ORG_ID,
CST.COST_TYPE_ID 成本類型 ,
CST.ITEM_COST 單位成本 ,
CST.MATERIAL_COST 材料成本 ,
CST.MATERIAL_OVERHEAD_COST 間接費(fèi) ,
CST.Resource_Cost 人工費(fèi) ,
CST.OUTSIDE_PROCESSING_COST 外協(xié)費(fèi) ,
CST.OVERHEAD_COST 制造費(fèi)
FROM CST_ITEM_COSTS CST
WHERE CST.COST_TYPE_ID = 2
AND CST.INVENTORY_ITEM_ID = 12781
AND CST.ORGANIZATION_ID = 1155 ;
?
-- 綜合查詢 - 庫存數(shù)量及成本
SELECT MSI.ORGANIZATION_ID 組織 ID,
MSI.INVENTORY_ITEM_ID 物料 ID,
MSI.SEGMENT1 物料編碼 ,
MSI.DESCRIPTION 物料說明 ,
MSI.PLANNING_MAKE_BUY_CODE M1P2,
MOQV.SUBINVENTORY_CODE 子庫存 ,
MOQV.QTY 當(dāng)前庫存量 ,
CST.ITEM_COST 單位成本 ,
CST.MATERIAL_COST 材料成本 ,
CST.MATERIAL_OVERHEAD_COST 間接費(fèi) ,
CST.Resource_Cost 人工費(fèi) ,
CST.OUTSIDE_PROCESSING_COST 外協(xié)費(fèi) ,
CST.OVERHEAD_COST 制造費(fèi)
FROM MTL_SYSTEM_ITEMS MSI,
CST_ITEM_COSTS CST,
( SELECT MOQ.ORGANIZATION_ID,
MOQ.INVENTORY_ITEM_ID,
MOQ.SUBINVENTORY_CODE,
SUM (MOQ.TRANSACTION_QUANTITY) QTY
FROM mtl_onhand_quantities MOQ
WHERE MOQ.ORGANIZATION_ID = 1155
GROUP BY MOQ.ORGANIZATION_ID,
MOQ.INVENTORY_ITEM_ID,
MOQ.SUBINVENTORY_CODE) MOQV
WHERE MSI.ORGANIZATION_ID = CST.ORGANIZATION_ID(+)
AND MSI.INVENTORY_ITEM_ID = CST.INVENTORY_ITEM_ID(+)
AND MSI.ORGANIZATION_ID = MOQV.ORGANIZATION_ID(+)
AND MSI.INVENTORY_ITEM_ID = MOQV.INVENTORY_ITEM_ID(+)
AND CST.COST_TYPE_ID = 2
AND MSI.ORGANIZATION_ID = 1155
AND MSI.SEGMENT1 = '18020200012'
-- 子庫存列表
SELECT * FROM mtl_secondary_inventories;
-- 貨位列表
SELECT ORGANIZATION_ID 組織代碼 ,
INVENTORY_LOCATION_ID 貨位內(nèi)碼 ,
SUBINVENTORY_CODE 子庫名稱 ,
SEGMENT1 貨位編碼
FROM mtl_item_locations;
-- 計(jì)劃員表
SELECT PLANNER_CODE 計(jì)劃員代碼 ,
ORGANIZATION_ID 組織代碼 ,
DESCRIPTION 計(jì)劃員描述 ,
MP.EMPLOYEE_ID 員工 ID,
DISABLE_DATE 失效日期
FROM mtl_planners MP;
-- 科目設(shè)置等參數(shù)
select * from MTL_PARAMETERS MP
?
9. 物料清單 BOM
--BOM 主表 bom_bill_of_materials
select AA.BILL_SEQUENCE_ID 清單序號 ,
AA.ASSEMBLY_ITEM_ID 裝配件內(nèi)碼 ,
AA.ORGANIZATION_ID 組織代碼 ,
BB.SEGMENT1 物料編碼 ,
BB.DESCRIPTION 物料說明 ,
AA.ASSEMBLY_TYPE 裝配類別
from bom_bill_of_materials AA ， mtl_system_items BB
where AA.ASSEMBLY_ITEM_ID = BB.INVENTORY_ITEM_ID
and AA.ORGANIZATION_ID = BB.ORGANIZATION_ID;
--BOM 明細(xì)表 bom_inventory_components
select BILL_SEQUENCE_ID 清單序號 ,
COMPONENT_SEQUENCE_ID 構(gòu)件序號 ,
ITEM_NUM 項(xiàng)目序列 ,
OPERATION_SEQ_NUM 操作序列號 ,
COMPONENT_ITEM_ID 子物料內(nèi)碼 ,
COMPONENT_QUANTITY 構(gòu)件數(shù)量 ,
DISABLE_DATE 失效日期 ,
supply_subinventory 供應(yīng)子庫存 ,
BOM_ITEM_TYPE
from bom_inventory_components;
?
--BOM 明細(xì)綜合查詢 ( 組織 限定供應(yīng)處 142 裝配件 = '5XJ061988')
SELECT VBOM.BID 清單序號 ,
VBOM.F_ITEMID 裝配件內(nèi)碼 ,
BB.SEGMENT1 物料編碼 ,
BB.DESCRIPTION 物料說明 ,
VBOM.OGT_ID 組織內(nèi)碼 ,
VBOM.CID 操作 ID,
VBOM.ITEM_NUM 物料序號 ,
VBOM.OPID 工序 ,
VBOM.C_ITEMID 子物料內(nèi)碼 ,
CC.SEGMENT1 物料編碼 ,
CC.DESCRIPTION 物料說明 ,
VBOM.QTY 構(gòu)件數(shù)量 ,
CC.PRIMARY_UOM_CODE 子計(jì)量單位碼 ,
CC.PRIMARY_UNIT_OF_MEASURE 子計(jì)量單位名 ,
VBOM.WHSE 供應(yīng)子倉庫
FROM ( select AA.BILL_SEQUENCE_ID BID,
BB.ASSEMBLY_ITEM_ID F_ITEMID,
BB.ORGANIZATION_ID OGT_ID,
AA.COMPONENT_SEQUENCE_ID CID,
AA.ITEM_NUM ITEM_NUM,
AA.OPERATION_SEQ_NUM OPID,
AA.COMPONENT_ITEM_ID C_ITEMID,
AA.COMPONENT_QUANTITY QTY,
AA.supply_subinventory WHSE
from bom_inventory_components AA, bom_bill_of_materials BB
where AA.BILL_SEQUENCE_ID = BB.BILL_SEQUENCE_ID) VBOM,
mtl_system_items BB,
mtl_system_items CC
WHERE VBOM.F_ITEMID = BB.INVENTORY_ITEM_ID
and VBOM.OGT_ID = BB.ORGANIZATION_ID
and VBOM.C_ITEMID = CC.INVENTORY_ITEM_ID
and VBOM.OGT_ID = CC.ORGANIZATION_ID
and VBOM.OGT_ID = 142
and BB.SEGMENT1 = '5XJ061988'
ORDER BY VBOM.ITEM_NUM;
?
-- 單層 BOM 成本查詢 ( 需系統(tǒng)提交請求計(jì)算后 )
select inventory_item_id, organization_id, item_cost, program_update_date
from bom.cst_item_costs
where inventory_item_id = 23760
and organization_id = 142 ;
?
select inventory_item_id, organization_id, item_cost, program_update_date
from cst_item_cost_details
where inventory_item_id = 23760
and organization_id = 142 ;
?
10. 作業(yè)任務(wù) WIP
說明： 查詢作業(yè)任務(wù)頭以及作業(yè)任務(wù)工序和 BOM 情況
-- 作業(yè)任務(wù)頭信息表
-- （以直流 OU_ID=117 ； ORGANIZATION_ID=1155; 及任務(wù) WIP_ENTITY_NAME='XJ39562'; 裝配件編碼 SEGMENT1 = '07D9202.92742' 為例）
SELECT AA.WIP_ENTITY_ID 任務(wù)令 ID,
AA.ORGANIZATION_ID 組織 ID,
AA.WIP_ENTITY_NAME 任務(wù)名稱 ,
AA.ENTITY_TYPE 任務(wù)類型 ,
AA.CREATION_DATE 創(chuàng)建日期 ,
AA.CREATED_BY 創(chuàng)建者 ID,
AA.DESCRIPTION 說明 ,
AA.PRIMARY_ITEM_ID 裝配件 ID,
BB.SEGMENT1 物料編碼 ,
BB.DESCRIPTION 物料說明
FROM wip_entities AA, mtl_system_items BB
WHERE AA.PRIMARY_ITEM_ID = BB.INVENTORY_ITEM_ID
and AA.ORGANIZATION_ID = BB.ORGANIZATION_ID
and AA.ORGANIZATION_ID = 1155
and AA.WIP_ENTITY_NAME = 'XJ39562' ;
--=> WIP_ENTITY_ID = 48825
?
-- 離散作業(yè)任務(wù)詳細(xì)主信息表
-- 用途 1 ）作業(yè)任務(wù)下達(dá)及完成情況查詢
-- 說明 1 ）此表包括 wip_entities 表大部分信息 2) 重復(fù)作業(yè)任務(wù)表為 wip_repetitive_items, wip_repetitive_schedules
select AA.WIP_ENTITY_ID 任務(wù)令 ID,
BB.WIP_ENTITY_NAME 任務(wù)名稱 ,
AA.ORGANIZATION_ID 組織 ID,
AA.SOURCE_LINE_ID 行 ID,
AA.STATUS_TYPE 狀態(tài) TYPE,
AA.PRIMARY_ITEM_ID 裝配件 ID,
CC.SEGMENT1 物料編碼 ,
CC.DESCRIPTION 物料說明 ,
AA.FIRM_PLANNED_FLAG,
AA.JOB_TYPE 作業(yè)類型 ,
AA.WIP_SUPPLY_TYPE 供應(yīng) TYPE,
AA.CLASS_CODE 任務(wù)類別 ,
AA.SCHEDULED_START_DATE 起始時(shí)間 ,
AA.DATE_RELEASED 下達(dá)時(shí)間 ,
AA.SCHEDULED_COMPLETION_DATE 完工時(shí)間 ,
AA.DATE_COMPLETED 完工時(shí)間 ,
AA.DATE_CLOSED 關(guān)門時(shí)間 ,
AA.START_QUANTITY 計(jì)劃數(shù) ,
AA.QUANTITY_COMPLETED 完工數(shù) ,
AA.QUANTITY_SCRAPPED 報(bào)廢數(shù) ,
AA.NET_QUANTITY MRP 凈值 ,
AA.COMPLETION_SUBINVENTORY 接收子庫 ,
AA.COMPLETION_LOCATOR_ID 貨位
from wip_discrete_jobs AA, wip.wip_entities BB, mtl_system_items CC
where AA.WIP_ENTITY_ID = BB.WIP_ENTITY_ID
and AA.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID
and AA.ORGANIZATION_ID = CC.ORGANIZATION_ID
and AA.organization_id = 1155
and BB.WIP_ENTITY_NAME = 'XJ39562' ;
/*
1 ）任務(wù)狀態(tài) TYPE 值說明：
STATUS_TYPE =1 未發(fā)放的 - 收費(fèi)不允許
STATUS_TYPE =3 發(fā)入 - 收費(fèi)允許
STATUS_TYPE =4 完成 - 允許收費(fèi)
STATUS_TYPE =5 完成 - 不允許收費(fèi)
STATUS_TYPE =6 暫掛 - 不允許收費(fèi)
STATUS_TYPE =7 已取消 - 不允許收費(fèi)
STATUS_TYPE =8 等待物料單加載
STATUS_TYPE =9 失敗的物料單加載
STATUS_TYPE =10 等待路線加載
STATUS_TYPE =11 失敗的路線加載
STATUS_TYPE =12 關(guān)閉 - 不可收費(fèi)
STATUS_TYPE =13 等待 - 成批加載
STATUS_TYPE =14 等待關(guān)閉
STATUS_TYPE =15 關(guān)閉失敗
2 ）供應(yīng)類型 TYPE 值說明：
WIP_SUPPLY_TYPE =1 推式
WIP_SUPPLY_TYPE =2 裝配拉式
WIP_SUPPLY_TYPE =3 操作拉式
WIP_SUPPLY_TYPE =4 大量
WIP_SUPPLY_TYPE =5 供應(yīng)商
WIP_SUPPLY_TYPE =6 虛擬
WIP_SUPPLY_TYPE =7 以帳單為基礎(chǔ)
*/
?
-- 離散作業(yè)任務(wù)工序狀況表
select AA.ORGANIZATION_ID 組織 ID,
AA.WIP_ENTITY_ID 任務(wù)令 ID,
BB.WIP_ENTITY_NAME 任務(wù)名稱 ,
AA.OPERATION_SEQ_NUM 工序號 ,
AA.DESCRIPTION 工序描述 ,
AA.DEPARTMENT_ID 部門 ID,
AA.SCHEDULED_QUANTITY 計(jì)劃數(shù)量 ,
AA.QUANTITY_IN_QUEUE 排隊(duì)數(shù)量 ,
AA.QUANTITY_RUNNING 運(yùn)行數(shù)量 ,
AA.QUANTITY_WAITING_TO_MOVE 待移動數(shù)量 ,
AA.QUANTITY_REJECTED 故障品數(shù)量 ,
AA.QUANTITY_SCRAPPED 報(bào)廢品數(shù)量 ,
AA.QUANTITY_COMPLETED 完工數(shù)量 ,
AA.FIRST_UNIT_START_DATE 最早一個單位上線時(shí)間 ,
AA.FIRST_UNIT_COMPLETION_DATE 最早一個單位完成時(shí)間 ,
AA.LAST_UNIT_START_DATE 最后一個單位上線時(shí)間 ,
AA.LAST_UNIT_COMPLETION_DATE 最后一個單位完工時(shí)間 ,
AA.PREVIOUS_OPERATION_SEQ_NUM 前一工序序號 ,
AA.NEXT_OPERATION_SEQ_NUM 下一工序序號 ,
AA.COUNT_POINT_TYPE 是否自動計(jì)費(fèi) ,
AA.BACKFLUSH_FLAG 倒沖否 ,
AA.MINIMUM_TRANSFER_QUANTITY 最小傳送數(shù)量 ,
AA.DATE_LAST_MOVED 最后移動時(shí)間
from wip_operations AA,wip_entities BB
where AA.WIP_ENTITY_ID = BB.WIP_ENTITY_ID
and BB.WIP_ENTITY_NAME = 'XJ39562' ;
-- 離散作業(yè)任務(wù)子查詢 ——— 工單工序狀況查詢（不單獨(dú)使用）
select WDJ.ORGANIZATION_ID,
WDJ.WIP_ENTITY_ID,
COUNT ( 1 ) COUNT_OPER,
max ( decode (wo.quantity_completed, 1 , wo.operation_seq_num, 10 )) OPER
from WIP_DISCRETE_JOBS WDJ, WIP_OPERATIONS WO
where 1 = 1
AND WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WDJ.wip_entity_id = '48825'
group by WDJ.ORGANIZATION_ID,WDJ.WIP_ENTITY_ID;
?
?
-- 離散作業(yè)任務(wù) BOM ( 無材料費(fèi) )
SELECT WOP.ORGANIZATION_ID 組織 ID,
WOP.WIP_ENTITY_ID 任務(wù)令 ID,
BB.WIP_ENTITY_NAME 裝配件名稱 ,
BB.PRIMARY_ITEM_ID 裝配件 ID,
CC.SEGMENT1 裝配件物料編碼 ,
CC.DESCRIPTION 裝配件說明 ,
WOP.OPERATION_SEQ_NUM 工序號 ,
WOP.DEPARTMENT_ID 部門 ID,
WOP.WIP_SUPPLY_TYPE 供應(yīng)類型 ,
WOP.DATE_REQUIRED 要求日期 ,
WOP.INVENTORY_ITEM_ID 子物料 ID,
DD.SEGMENT1 子物料編碼 ,
DD.DESCRIPTION 子物料說明 ,
WOP.QUANTITY_PER_ASSEMBLY 單位需量 ,
WOP.REQUIRED_QUANTITY 總需求量 ,
WOP.QUANTITY_ISSUED 已發(fā)放量 ,
WOP.COMMENTS 注釋 ,
WOP.SUPPLY_SUBINVENTORY 供應(yīng)子庫
FROM wip_requirement_operations WOP,
wip_entities BB,
mtl_system_items CC,
mtl_system_items DD
WHERE WOP.WIP_ENTITY_ID = BB.WIP_ENTITY_ID
and BB.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID
and BB.ORGANIZATION_ID = CC.ORGANIZATION_ID
and WOP.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID
and WOP.ORGANIZATION_ID = DD.ORGANIZATION_ID
and WOP.ORGANIZATION_ID = 1155
and BB.WIP_ENTITY_NAME = 'XJ39562' ;
?
-- 作業(yè)任務(wù)已發(fā)放材料處理記錄清單 0101 （最詳細(xì)） （內(nèi)碼為 48825 為例）
-- 用途 1 ）查詢工單發(fā)料詳細(xì)明細(xì)，包括發(fā)料類型、時(shí)間、用戶等
select MTL.TRANSACTION_ID 交易 ID,
MTL.INVENTORY_ITEM_ID 項(xiàng)目 ID,
CC.SEGMENT1 物料編碼 ,
CC.DESCRIPTION 物料說明 ,
MTL.ORGANIZATION_ID 組織 ID,
MTL.SUBINVENTORY_CODE 子庫名稱 ,
MTL.TRANSACTION_TYPE_ID 交易類型 ID,
BB.TRANSACTION_TYPE_NAME 交易類型名稱 ,
MTL.TRANSACTION_QUANTITY 交易數(shù)量 ,
MTL.TRANSACTION_UOM 單位 ,
MTL.TRANSACTION_DATE 交易日期 ,
MTL.TRANSACTION_REFERENCE 交易參考 ,
MTL.TRANSACTION_SOURCE_ID 參考源 ID,
FF.WIP_ENTITY_NAME 任務(wù)名稱 ,
MTL.DEPARTMENT_ID 部門 ID,
MTL.OPERATION_SEQ_NUM 工序號 ,
ROUND (MTL.PRIOR_COST, 2 ) 原來成本 ,
ROUND (MTL.NEW_COST, 2 ) 新成本 ,
MTL.TRANSACTION_QUANTITY * ROUND (MTL.PRIOR_COST, 2 ) 交易金額 ,
DD.USER_NAME 用戶名稱 ,
EE.FULL_NAME 用戶姓名
from mtl_material_transactions MTL,
mtl_transaction_types BB,
mtl_system_items CC,
FND_USER DD,
per_people_f EE,
wip_entities FF
where MTL.TRANSACTION_TYPE_ID = BB.TRANSACTION_TYPE_ID
and MTL.CREATED_BY = DD.USER_ID
and MTL.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID
and MTL.ORGANIZATION_ID = CC.ORGANIZATION_ID
and DD.EMPLOYEE_ID = EE.PERSON_ID
and MTL.TRANSACTION_SOURCE_ID = FF.WIP_ENTITY_ID
and MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )
and MTL.ORGANIZATION_ID = 1155
and MTL.TRANSACTION_SOURCE_ID = 48825 ;
-- 按工單的材料費(fèi)匯總（不單獨(dú)使用）
select MTL.ORGANIZATION_ID,
MTL.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,
ABS ( round ( SUM (MTL.TRANSACTION_QUANTITY * MTL.PRIOR_COST), 2 )) AMT
from mtl_material_transactions MTL
where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )
and MTL.ORGANIZATION_ID = 1155
and MTL.TRANSACTION_SOURCE_ID = 48825
group by MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID;
?
-- 離散作業(yè)任務(wù)子查詢 01——— 材料消耗狀況及材料費(fèi)綜合查詢
-- 用途 1 ）查詢發(fā)料狀況 2 ）查詢材料費(fèi)物料小計(jì)
SELECT WOP.ORGANIZATION_ID 組織 ID,
WOP.WIP_ENTITY_ID 任務(wù)令 ID,
BB.WIP_ENTITY_NAME 裝配件名稱 ,
BB.PRIMARY_ITEM_ID 裝配件 ID,
CC.SEGMENT1 裝配件物料編碼 ,
CC.DESCRIPTION 裝配件說明 ,
WOP.OPERATION_SEQ_NUM 工序號 ,
WOP.DEPARTMENT_ID 部門 ID,
WOP.WIP_SUPPLY_TYPE 供應(yīng)類型 ,
WOP.DATE_REQUIRED 要求日期 ,
WOP.INVENTORY_ITEM_ID 子物料 ID,
DD.SEGMENT1 子物料編碼 ,
DD.DESCRIPTION 子物料說明 ,
WOP.QUANTITY_PER_ASSEMBLY 單位需量 ,
WOP.REQUIRED_QUANTITY 總需求量 ,
WOP.QUANTITY_ISSUED 已發(fā)放量 ,
CST.AMT 已發(fā)生材料費(fèi) ,
WOP.COMMENTS 注釋 ,
WOP.SUPPLY_SUBINVENTORY 供應(yīng)子庫
FROM wip_requirement_operations WOP,
wip_entities BB,
mtl_system_items CC,
mtl_system_items DD,
( select MTL.ORGANIZATION_ID orgID,
MTL.TRANSACTION_SOURCE_ID wipID,
MTL.OPERATION_SEQ_NUM oprID,
MTL.INVENTORY_ITEM_ID itemID,
sum (MTL.TRANSACTION_QUANTITY * ROUND (MTL.actual_cost, 2 )) amt
from mtl_material_transactions MTL
where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )
and MTL.ORGANIZATION_ID = 1155
and MTL.TRANSACTION_SOURCE_ID = 48825
group by MTL.ORGANIZATION_ID,
MTL.TRANSACTION_SOURCE_ID,
MTL.OPERATION_SEQ_NUM,
MTL.INVENTORY_ITEM_ID) CST
WHERE WOP.WIP_ENTITY_ID = BB.WIP_ENTITY_ID
and BB.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID
and BB.ORGANIZATION_ID = CC.ORGANIZATION_ID
and WOP.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID
and WOP.ORGANIZATION_ID = DD.ORGANIZATION_ID
and WOP.ORGANIZATION_ID = CST.orgID
and WOP.WIP_ENTITY_ID = CST.wipID
and WOP.OPERATION_SEQ_NUM = CST.oprID
and WOP.INVENTORY_ITEM_ID = CST.itemID
and WOP.ORGANIZATION_ID = 1155
and BB.WIP_ENTITY_NAME = 'XJ39562' ;
?
-- 離散作業(yè)任務(wù)子查詢 0201——— 作業(yè)資源報(bào)工明細(xì)表
SELECT WTA.ORGANIZATION_ID 組織代碼 ,
WTA.TRANSACTION_ID 交易代碼 ,
WTA.REFERENCE_ACCOUNT 參考科目 ,
WTA.TRANSACTION_DATE 報(bào)工日期 ,
WTA.WIP_ENTITY_ID 任務(wù)令內(nèi)碼 ,
WTA.ACCOUNTING_LINE_TYPE 會計(jì)欄類型 ,
WTA.BASE_TRANSACTION_VALUE 費(fèi)用額 ,
WTA.CONTRA_SET_ID 反方集代碼 ,
WTA.PRIMARY_QUANTITY 基本數(shù)量 ,
WTA.RATE_OR_AMOUNT 率或金額 ,
WTA.BASIS_TYPE 基本類型 ,
WTA.RESOURCE_ID 資源代碼 ,
WTA.COST_ELEMENT_ID 成本要素 ID,
WTA.ACCOUNTING_LINE_TYPE 成本類型 ID,
WTA.OVERHEAD_BASIS_FACTOR 費(fèi)用因子 ,
WTA.BASIS_RESOURCE_ID 基本資源 ID,
WTA.CREATED_BY 錄入人 ID,
DD.USER_NAME 用戶名稱 ,
EE.FULL_NAME 用戶姓名
FROM wip_transaction_accounts WTA, FND_USER DD, per_people_f EE
WHERE WTA.CREATED_BY = DD.USER_ID
and DD.EMPLOYEE_ID = EE.PERSON_ID
and WTA.BASE_TRANSACTION_VALUE <> 0
and WTA.ORGANIZATION_ID = 1155
and WTA.WIP_ENTITY_ID = 48839 ;
-- 成本類型 ID ACCOUNTING_LINE_TYPE
SELECT * FROM MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE LIKE 'CST_ACCOUNTING_LINE_TYPE'
ORDER BY ML.LOOKUP_CODE;
-- 成本要素 ID COST_ELEMENT_ID
--( 待補(bǔ)充 --------------------------------------------------------------------------?)
?
-- 統(tǒng)計(jì)人工費(fèi)與制造費(fèi) ( 不單獨(dú)應(yīng)用 )
SELECT ORGANIZATION_ID, WIP_ENTITY_ID, SUM (HR_FEE) HR_FEE, SUM (MD_FEE) MD_FEE
FROM ( Select WTA.ORGANIZATION_ID,
WTA.WIP_ENTITY_ID,
decode (COST_ELEMENT_ID, 3 , WTA.BASE_TRANSACTION_VALUE, 0 ) HR_FEE,
decode (COST_ELEMENT_ID, 5 , WTA.BASE_TRANSACTION_VALUE, 0 ) MD_FEE
from WIP_TRANSACTION_ACCOUNTS WTA
where WTA.ACCOUNTING_LINE_TYPE = 7
and WTA.BASE_TRANSACTION_VALUE <> 0 ) WTA_COST
WHERE WTA_COST.ORGANIZATION_ID = 1155
AND WTA_COST.WIP_ENTITY_ID = '48839'
GROUP BY WTA_COST.ORGANIZATION_ID,WTA_COST.WIP_ENTITY_ID;
?
-- 工單進(jìn)度及費(fèi)用信息綜合查詢 ( 未下達(dá)及下達(dá)零發(fā)料和報(bào)工的看不到 )
select WE.WIP_ENTITY_NAME 任務(wù)名稱 ,
MSI.SEGMENT1 物料 ,
MSI.DESCRIPTION 物料描述 ,
MSI.PRIMARY_UNIT_OF_MEASURE 單位 ,
WDJ.SCHEDULED_START_DATE 計(jì)劃開始時(shí)間 ,
WDJ.SCHEDULED_COMPLETION_DATE 計(jì)劃完成時(shí)間 ,
WDJ.START_QUANTITY 工單數(shù)量 ,
WDJ.QUANTITY_COMPLETED 完成數(shù)量 ,
WDJ.DATE_RELEASED 實(shí)際開始時(shí)間 ,
WDJ.DATE_COMPLETED 時(shí)間完成時(shí)間 ,
WDJ.DESCRIPTION 工單備注 ,
PP.SEGMENT1 項(xiàng)目號 ,
PP.DESCRIPTION 項(xiàng)目描述 ,
PT.TASK_NUMBER 任務(wù)號 ,
PT.DESCRIPTION 任務(wù)描述 ,
WO.COUNT_OPER 工序數(shù) ,
WO1.OPERATION_SEQ_NUM 當(dāng)前工序 ,
WO1.DESCRIPTION 當(dāng)前工序描述 ,
MTA.MT_FEE 材料費(fèi) ,
WCT.HR_FEE 人工費(fèi) ,
WCT.MD_FEE 制造費(fèi) ,
WE.WIP_ENTITY_ID,
WE.ORGANIZATION_ID,
WDJ.PRIMARY_ITEM_ID,
WDJ.PROJECT_ID,
WDJ.TASK_ID
from WIP_ENTITIES WE,
WIP_OPERATIONS WO1,
WIP_DISCRETE_JOBS WDJ,
MTL_SYSTEM_ITEMS_B MSI,
PA_PROJECTS_ALL PP,
PA_TASKS PT,
( select WDJ.ORGANIZATION_ID,
WDJ.WIP_ENTITY_ID,
COUNT ( 1 ) COUNT_OPER,
max ( decode (wo.quantity_completed, 1 , wo.operation_seq_num, 10 )) OPER
from WIP_DISCRETE_JOBS WDJ, WIP_OPERATIONS WO
where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
group by WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID) WO, -- 工序進(jìn)度
( select MTL.ORGANIZATION_ID,
MTL.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,
ABS ( SUM (MTL.TRANSACTION_QUANTITY * MTL.actual_cost)) MT_FEE
from mtl_material_transactions MTL
where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )
group by MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID) MTA, -- 材料費(fèi)
( select WTA_COST.ORGANIZATION_ID,
WTA_COST.WIP_ENTITY_ID,
SUM (WTA_COST.HR_FEE1) HR_FEE,
SUM (WTA_COST.MD_FEE1) MD_FEE
from ( select WTA.ORGANIZATION_ID,
WTA.WIP_ENTITY_ID,
decode (COST_ELEMENT_ID,
3 ,
WTA.BASE_TRANSACTION_VALUE,
0 ) HR_FEE1,
decode (COST_ELEMENT_ID,
5 ,
WTA.BASE_TRANSACTION_VALUE,
0 ) MD_FEE1
from WIP_TRANSACTION_ACCOUNTS WTA
where WTA.ACCOUNTING_LINE_TYPE = 7
and WTA.BASE_TRANSACTION_VALUE <> 0 ) WTA_COST
group by WTA_COST.ORGANIZATION_ID, WTA_COST.WIP_ENTITY_ID) WCT -- 人工與制造
WHERE 1 = 1
AND WE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.OPER = WO1.OPERATION_SEQ_NUM
AND WE.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = MTA.WIP_ENTITY_ID(+)
AND WE.ORGANIZATION_ID = WCT.ORGANIZATION_ID
AND WE.WIP_ENTITY_ID = WCT.WIP_ENTITY_ID(+)
AND WDJ.PROJECT_ID = PP.PROJECT_ID(+)
AND WDJ.TASK_ID = PT.TASK_ID(+)
and we.organization_id = 1155
and we.wip_entity_id = '48825' ;
?
-- 工單進(jìn)度及費(fèi)用信息綜合查詢 ( 不論是否下達(dá)和發(fā)料都能看到 )
SELECT WDJ.WIP_ENTITY_ID 任務(wù)令 ID,
WE.WIP_ENTITY_NAME 任務(wù)名稱 ,
WDJ.ORGANIZATION_ID 組織 ID,
WDJ.STATUS_TYPE 狀態(tài) ,
WDJ.PRIMARY_ITEM_ID 裝配件 ID,
MSI.SEGMENT1 物料編碼 ,
MSI.DESCRIPTION 物料說明 ,
WDJ.FIRM_PLANNED_FLAG 任務(wù)類型 ,
WDJ.JOB_TYPE 作業(yè)類型 ,
WDJ.WIP_SUPPLY_TYPE 供應(yīng)類型 ,
WDJ.CLASS_CODE 任務(wù)類別 ,
WDJ.SCHEDULED_START_DATE 起始時(shí)間 ,
WDJ.DATE_RELEASED 下達(dá)時(shí)間 ,
WDJ.DATE_COMPLETED 完工時(shí)間 ,
WDJ.DATE_CLOSED 關(guān)閉時(shí)間 ,
WDJ.START_QUANTITY 計(jì)劃數(shù) ,
WDJ.QUANTITY_COMPLETED 完工數(shù) ,
WDJ.QUANTITY_SCRAPPED 報(bào)廢數(shù) ,
WDJ.NET_QUANTITY MRP 凈值 ,
WDJ.DESCRIPTION 工單備注 ,
WDJ.COMPLETION_SUBINVENTORY 接收子庫 ,
WDJ.COMPLETION_LOCATOR_ID 貨位 ID,
WDJ.PROJECT_ID 項(xiàng)目 ID,
WDJ.TASK_ID 項(xiàng)目任務(wù) ID,
PP.SEGMENT1 項(xiàng)目號 ,
PP.DESCRIPTION 項(xiàng)目描述 ,
PT.TASK_NUMBER 任務(wù)號 ,
PT.DESCRIPTION 任務(wù)描述 ,
WPF.COUNT_OPER 工序數(shù) ,
WPF.CUR_OPER 當(dāng)前工序 ,
WPF.CUR_OPERNAME 工序名 ,
WPF.MT_FEE 材料費(fèi) ,
WPF.HR_FEE 人工費(fèi) ,
WPF.MD_FEE 制造費(fèi)
FROM wip_discrete_jobs WDJ,
wip.wip_entities WE,
mtl_system_items MSI,
PA_PROJECTS_ALL PP,
PA_TASKS PT,
( select WDJ1.WIP_ENTITY_ID,
WDJ1.ORGANIZATION_ID,
WO.COUNT_OPER,
WO1.OPERATION_SEQ_NUM CUR_OPER,
WO1.DESCRIPTION CUR_OPERNAME,
MTA.MT_FEE,
WCT.HR_FEE,
WCT.MD_FEE
from WIP_OPERATIONS WO1,
WIP_DISCRETE_JOBS WDJ1,
( select WDJ.ORGANIZATION_ID,
WDJ.WIP_ENTITY_ID,
COUNT ( 1 ) COUNT_OPER,
max ( decode (wo.quantity_completed,
1 ,
wo.operation_seq_num,
10 )) OPER
from WIP_DISCRETE_JOBS WDJ, WIP_OPERATIONS WO
where WDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
group by WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID) WO, -- 工序進(jìn)度
( select MTL.ORGANIZATION_ID,
MTL.TRANSACTION_SOURCE_ID WIP_ENTITY_ID,
ABS ( SUM (MTL.TRANSACTION_QUANTITY * MTL.actual_cost)) MT_FEE
from mtl_material_transactions MTL
where MTL.Transaction_Type_Id in ( 35 , 38 , 43 , 48 )
group by MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID) MTA, -- 材料費(fèi)
( select WTA_COST.ORGANIZATION_ID,
WTA_COST.WIP_ENTITY_ID,
SUM (WTA_COST.HR_FEE1) HR_FEE,
SUM (WTA_COST.MD_FEE1) MD_FEE
from ( select WTA.ORGANIZATION_ID,
WTA.WIP_ENTITY_ID,
decode (COST_ELEMENT_ID,
3 ,
WTA.BASE_TRANSACTION_VALUE,
0 ) HR_FEE1,
decode (COST_ELEMENT_ID,
5 ,
WTA.BASE_TRANSACTION_VALUE,
0 ) MD_FEE1
from WIP_TRANSACTION_ACCOUNTS WTA
where WTA.ACCOUNTING_LINE_TYPE = 7
and WTA.BASE_TRANSACTION_VALUE <> 0 ) WTA_COST
group by WTA_COST.ORGANIZATION_ID, WTA_COST.WIP_ENTITY_ID) WCT -- 人工與制造
WHERE 1 = 1
AND WDJ1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID(+)
AND WO1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.OPER = WO1.OPERATION_SEQ_NUM
AND WDJ1.ORGANIZATION_ID = MTA.ORGANIZATION_ID
AND WDJ1.WIP_ENTITY_ID = MTA.WIP_ENTITY_ID(+)
AND WDJ1.ORGANIZATION_ID = WCT.ORGANIZATION_ID
AND WDJ1.WIP_ENTITY_ID = WCT.WIP_ENTITY_ID(+)) WPF
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND WDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WDJ.PROJECT_ID = PP.PROJECT_ID(+)
AND WDJ.TASK_ID = PT.TASK_ID(+)
AND WDJ.ORGANIZATION_ID = WPF.ORGANIZATION_ID(+)
AND WDJ.WIP_ENTITY_ID = WPF.WIP_ENTITY_ID(+)
AND WDJ.organization_id = 1155
and PP.SEGMENT1 = '07D9202' ;
?
11. MRP相關(guān)查詢?
?
12. 總賬 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 科目匯總模板層次
13. 應(yīng)收 AR?
select * from ar_batches_all 事務(wù)處理批
select * from ra_customer_trx_all 發(fā)票頭
select * from ra_customer_trx_lines_all 發(fā)票行
select * from ra_cust_trx_line_gl_dist_all 發(fā)票分配
select * from ar_cash_receipts_all 收款
select * from ar_receivable_applications_all 核銷
select * from ar_payment_schedules_all 發(fā)票調(diào)整
select * from ar_adjustments_all 會計(jì)分錄
select * from ar_distributions_all 付款計(jì)劃
14. 應(yīng)付 AP
select * from ap_invoices_all 發(fā)票頭
select * from ap_invoice_distributions_all 發(fā)票行
select * from ap_payment_schedules_all 付款計(jì)劃
select * from ap_check_stocks_all 單據(jù)
select * from ap_checks_all 付款
select * from ap_bank_branches 銀行
select * from ap_bank_accounts_all 銀行帳號
select * from ap_invoice_payments_all 核銷
?
15. 應(yīng)用、值集、彈性域?
--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 進(jìn)程表
16. 許繼配送管理相關(guān)表?
--- 為了實(shí)現(xiàn)系統(tǒng)內(nèi)方便的配送發(fā)貨模式而開發(fā)
-- 配送關(guān)系 （以供應(yīng)處角度 ORGANIZATION_ID = 142 ）
SELECT AA.CUSTOMER_RELATION_ID 配送關(guān)系 ID,
AA.ORGANIZATION_ID 組織 ID,
AA.CUST_ACCOUNT_ID 客戶 ID,
CC.PARTY_NAME 客戶名稱 ,
AA.CUST_ACCT_SITE_ID 配送地 ID,
DD.LOCATION 客戶地點(diǎn) ,
DD.STATUS A 有效 ,
AA.DELIVERY_BY_SO_FLAG 源于 CO,
AA.OUTBOUND_TRX_TYPE_ID 出庫類型 ,
AA.OUTBOUND_RET_TRX_TYPE_ID 出庫 R 類型 ,
AA.OUTBOUND_COST_CCID 出庫賬戶 ID,
EE.concatenated_segments 出庫賬戶 ,
AA.CUST_ORG_ID 客戶庫存組織 ID,
AA.INBOUND_TRX_TYPE_ID 入庫類型 ,
AA.INBOUND_RET_TRX_TYPE_ID 入庫 R 類型 ,
AA.INBOUND_CONFIRM_FLAG 入庫確認(rèn) ,
AA.INBOUND_COST_CCID 入庫賬戶 ID,
FF.concatenated_segments 入庫賬戶 ,
AA.MANAGE_CHARGE 加價(jià)率 ,
AA.SETTLE_MODE 結(jié)算模式 ,
AA.INBOUND_SUBIN_CODE 接收子倉庫 ,
AA.OUTBOUND_SUBIN_CODE 配送子庫存 ,
AA.ATTRIBUTE1 直接生產(chǎn)發(fā)料 ,
AA.Creation_Date 創(chuàng)建日期 ,
AA.CREATED_BY 創(chuàng)建者 ,
AA.LAST_UPDATED_BY 更新者 ,
AA.LAST_UPDATE_DATE 更新日期
FROM CUX_INV_CUSTOMER_RELATION_ALL AA,
hz_cust_accounts BB,
hz_parties CC,
HZ_CUST_SITE_USES_ALL DD,
gl_code_combinations_kfv EE,
gl_code_combinations_kfv FF
WHERE AA.ORGANIZATION_ID = 142
AND AA.CUST_ACCOUNT_ID = BB.CUST_ACCOUNT_ID
AND BB.PARTY_ID = CC.PARTY_ID
AND AA.Cust_Acct_Site_Id = DD.Site_Use_Id
AND DD.STATUS = 'A'
AND AA.OUTBOUND_COST_CCID = EE.code_combination_id
AND AA.INBOUND_COST_CCID = FF.code_combination_id
and CC.PARTY_NAME = ' 許繼電氣電網(wǎng)保護(hù)自動化公司 ' ;
-- 配送單頭
SELECT AA.DN_HEADER_ID 配送單 ID,
AA.DN_NUMBER 配送單編號 ,
AA.DN_STATUS_CODE 狀態(tài) ,
AA.CUST_ACCOUNT_ID 客戶 ID,
CC.PARTY_NAME 客戶名稱 ,
AA.CUST_ACCT_SITE_ID 配送地址 ID,
DD.LOCATION 客戶地點(diǎn) ,
AA.DELIVERY_ORG_ID 配送方組織 ID,
AA.CUST_ORG_ID 客戶組織 ID,
AA.MANAGE_CHARGE 費(fèi)率 ,
AA.INBOUND_CONFIRM_FLAG 入庫確認(rèn)否 ,
AA.SO_HEADER_ID 銷售訂單 ID,
EE.ORDER_NUMBER 銷售訂單 ,
EE.CUST_PO_NUMBER 客戶 PO,
ee.attribute1,
ee.attribute2,
AA.PROCESS_FLAG,
AA.COMMENTS 配送單說明
FROM CUX_INV_DN_HEADERS_ALL AA, hz_cust_accounts BB, hz_parties CC, HZ_CUST_SITE_USES_ALL DD,OE_ORDER_HEADERS_ALL EE
WHERE AA.DELIVERY_ORG_ID = 142
AND AA.CUST_ACCOUNT_ID = BB.CUST_ACCOUNT_ID
AND BB.PARTY_ID = CC.PARTY_ID
AND AA.Cust_Acct_Site_Id = DD.Site_Use_Id
AND DD.STATUS = 'A'
AND AA.SO_HEADER_ID = EE.HEADER_ID
AND AA.DN_NUMBER = '14780016022' ;
-- 配送單明細(xì)
SELECT AA.DN_HEADER_ID,
AA.DN_LINE_ID,
AA.SO_LINE_ID,
LL.LINE_NUMBER SO 行號 ,
AA.INVENTORY_ITEM_ID 物料 ID,
CC.SEGMENT1 物料編碼 ,
CC.DESCRIPTION 物料說明 ,
AA.OUTBOUND_SUBIN_CODE 發(fā)出倉 ,
AA.OUTBOUND_LOCATOR_ID 發(fā)出貨位 ,
AA.REQUIRE_DATE 需求日期 ,
AA.REQUIRE_QTY 需求數(shù) ,
AA.OUTBOUND_QTY 已出庫 ,
AA.INBOUND_QTY 已接收 ,
AA.ATTRIBUTE1 最近確認(rèn)接收數(shù) ,
AA.INBOUND_SUBIN_CODE 入庫倉 ,
AA.INBOUND_LOCATOR_ID 入庫貨位 ,
AA.RETURN_NO_RECEIVE_QTY 退回?cái)?shù) ,
AA.OUTING_QTY,
AA.INING_QTY,
AA.REQUEST_ID 最近打印請求 ID
FROM CUX_INV_DN_LINES_ALL AA, CUX_INV_DN_HEADERS_ALL BB,mtl_system_items CC,OE_ORDER_LINES_ALL LL
WHERE AA.DN_HEADER_ID = BB.DN_HEADER_ID
AND AA.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID
AND BB.DELIVERY_ORG_ID = CC.ORGANIZATION_ID
AND AA.SO_LINE_ID = LL.LINE_ID
AND BB.DN_NUMBER = '14780016022' ;
?
?
?
99-其他相關(guān)表?
1) 會計(jì)科目組合表?
-- 查詢會計(jì)科目分段信息
select * from gl_code_combinations;
-- 查詢會計(jì)科目組合信息
select * from gl_code_combinations_kfv;
2) 查詢自定義的客戶化相關(guān)表和視圖?
-- 靜態(tài)數(shù)據(jù)字典中的視圖分為三類，它們分別由三個前綴夠成： user_* 、 all_* 、 dba_*
-- user_* 該視圖存儲了關(guān)于當(dāng)前用戶所擁有的對象的信息。（即所有在該用戶模式下的對象）
-- all_* 該視圖存儲了當(dāng)前用戶能夠訪問的對象的信息。（與 user_* 相比， all_* 并不需要擁有該對象，只需要具有訪問該對象的權(quán)限即可）
-- dba_* 該視圖存儲了數(shù)據(jù)庫中所有對象的信息。（前提是當(dāng)前用戶具有訪問這些數(shù)據(jù)庫的權(quán)限，一般來說必須具有管理員權(quán)限
-- 查詢該用戶擁有哪些表
select * from user_tables where table_name like 'CUX%' ;
-- 查詢該用戶擁有哪些視圖
select * from user_views where view_name like 'CUX%' ;
-- 查詢該用戶擁有哪些索引
select * from user_indexes;
3) 查詢物料處理記錄?
說明： mtl_material_transactions 這個表記錄了所有涉及倉庫收發(fā)的物料交易記錄，包括：采購、 WIP 、訂單、雜項(xiàng)等多種處理模式的內(nèi)容。
-- 舉例： 查詢某用戶在電網(wǎng)的賬戶別名發(fā)放清單
select AA.TRANSACTION_ID 交易代碼 ,
AA.INVENTORY_ITEM_ID 項(xiàng)目內(nèi)碼 ,
CC.SEGMENT1 物料編碼 ,
CC.DESCRIPTION 物料說明 ,
AA.ORGANIZATION_ID 組織代碼 ,
AA.SUBINVENTORY_CODE 子庫名稱 ,
AA.TRANSACTION_TYPE_ID 類型 ID,
BB.TRANSACTION_TYPE_NAME 類型名稱 ,
AA.TRANSACTION_QUANTITY 數(shù)量 ,
AA.TRANSACTION_UOM 單位 ,
AA.TRANSACTION_DATE 交易日期 ,
AA.TRANSACTION_REFERENCE 交易參考 ,
AA.TRANSACTION_SOURCE_ID 參考源 ID,
AA.DEPARTMENT_ID 部門 ID,
AA.OPERATION_SEQ_NUM 工序號 ,
ROUND (AA.Actual_Cost, 2 ) 實(shí)際成本 ,
ROUND (AA.Transaction_Cost, 2 ) 處理成本 ,
ROUND (AA.PRIOR_COST, 2 ) 舊成本 ,
ROUND (AA.NEW_COST, 2 ) 新成本 ,
ROUND (AA.VARIANCE_AMOUNT, 2 ) 差異金額 ,
AA.TRANSACTION_QUANTITY * ROUND (AA.PRIOR_COST, 2 ) 交易金額 ,
DD.USER_NAME 用戶名稱 ,
EE.FULL_NAME 用戶姓名 ,
AA.ATTRIBUTE1 彈性域人名 ,
AA.ATTRIBUTE15 彈性域備注
from mtl_material_transactions AA ,
mtl_transaction_types BB,
mtl_system_items CC,
FND_USER DD,
per_people_f EE
where AA.TRANSACTION_TYPE_ID = BB.TRANSACTION_TYPE_ID and
AA.CREATED_BY = DD.USER_ID and
AA.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID and
AA.ORGANIZATION_ID = CC.ORGANIZATION_ID and
DD.EMPLOYEE_ID = EE.PERSON_ID and
AA.ORGANIZATION_ID = 1155 AND
CC.SEGMENT1= '07D9202.92742' and
AA.TRANSACTION_DATE >= TO_DATE( '2011-01-29 00:00:00' , 'YYYY-MM-DD HH24:MI:SS' )
order by aa.TRANSACTION_ID;
/* 物料處理記錄 mtl_material_transactions 表，類型與成本說明：
-- 雜項(xiàng)收 TRANSACTION_TYPE_ID = 41 錄入價(jià)格優(yōu)先， =Actual_Cost ，移動平均 TRANSACTION_QUANTITY > 0 , 調(diào)整類似
注： 1 ）如果再接收界面錄入了價(jià)格，以錄入價(jià)格計(jì)入 Actual_Cost ，進(jìn)行移動平均
2 ）如果沒有錄入價(jià)格，字段 NULL ，則系統(tǒng)會以當(dāng)前成本接收，計(jì)入 Actual_Cost
-- 雜項(xiàng)發(fā) TRANSACTION_TYPE_ID = 31 以出 =Actual_Cost ， TRANSACTION_QUANTITY < 0 , 調(diào)整類似
-- 采購收 TRANSACTION_TYPE_ID = 18 以 入 =Actual_Cost ，移動平均 TRANSACTION_QUANTITY > 0
-- 采購?fù)?TRANSACTION_TYPE_ID = 36 以 出 =Actual_Cost ， TRANSACTION_QUANTITY < 0
注： 1) 系統(tǒng)按采購成本退貨和扣除庫存金額，不考慮已消耗狀況；
2) 如果庫存金額夠扣除，則扣除后重新計(jì)算出一個新成本；
3 ）如果庫存金額不夠扣除，則扣除全部金額，就會出現(xiàn)有庫存量而單位成本 =0 的物資，不夠扣的部分計(jì)入字段 VARIANCE_AMOUNT 。
-- 作業(yè)發(fā) TRANSACTION_TYPE_ID = 35 以當(dāng)前成本出， =Actual_Cost ， TRANSACTION_QUANTITY < 0 ，特定組件發(fā)料類似
-- 作業(yè)退 TRANSACTION_TYPE_ID = 43 以當(dāng)前成本入， =Actual_Cost ，不移動平均 TRANSACTION_QUANTITY > 0
-- 配送出 TRANSACTION_TYPE_ID = 100 以當(dāng)前成本出， =Actual_Cost ， TRANSACTION_QUANTITY < 0
-- 配送退 TRANSACTION_TYPE_ID = 101 以配送價(jià)入 ， =Actual_Cost ，移動平均 TRANSACTION_QUANTITY > 0
-- 銷售發(fā) TRANSACTION_TYPE_ID = 33 以當(dāng)前成本出， =Actual_Cost ， TRANSACTION_QUANTITY < 0
-- 銷售退 TRANSACTION_TYPE_ID = 15 以當(dāng)前成本入， =Actual_Cost ， TRANSACTION_QUANTITY > 0
*/
-- 物料處理記錄類型列表
select bb.transaction_type_id 類型 ID,
bb.transaction_type_name 別名 ,
bb.description 說明
from mtl_transaction_types BB
order by bb.transaction_type_id
-- 交易來源類型列表
SELECT *
FROM mtl_txn_source_types
-- 交易原因代碼表
SELECT REASON_ID 原因代碼 , REASON_NAME 名稱 , DESCRIPTION 描述
FROM inv.mtl_transaction_reasons;
工藝路線
? FROM BOM_OPERATIONAL_ROUTINGS_V BOR,
? ? ? ?BOM_OPERATION_SEQUENCES_V ?BOS,
? ? ? ?BOM_OPERATION_RESOURCES_V ?BORV,
? ? ? ?BOM_RESOURCES ? ? ? ? ? ? ?BR,MTL_SYSTEM_ITEMS_B MSIB
?WHERE BOR.ORGANIZATION_ID = 86
? ?AND BR.ORGANIZATION_ID = 86
? ?AND BOR.ASSEMBLY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
? ?AND MSIB.ORGANIZATION_ID=86
? ?AND BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
? ?AND BOS.OPERATION_SEQUENCE_ID = BORV.OPERATION_SEQUENCE_ID
? ?AND BR.RESOURCE_ID = BORV.RESOURCE_ID
? ?AND BOS.OPERATION_SEQ_NUM =
? ? ? ?(SELECT MAX(BOS1.OPERATION_SEQ_NUM)
? ? ? ? ? FROM BOM_OPERATION_SEQUENCES_V ?BOS1,
? ? ? ? ? ? ? ?BOM_OPERATIONAL_ROUTINGS_V BOR1
? ? ? ? ?WHERE BOS1.ROUTING_SEQUENCE_ID = BOR1.ROUTING_SEQUENCE_ID
? ? ? ? ? ?AND BOR1.ROUTING_SEQUENCE_ID = BOR.ROUTING_SEQUENCE_ID
? ? ? ? ? ?AND BOR1.ALTERNATE_ROUTING_DESIGNATOR IS NULL)
? ?AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL
? ?AND NVL(BR.ATTRIBUTE15, 'N') = 'Y'
轉(zhuǎn)載于:https://www.cnblogs.com/wanghang/p/6299314.html
總結(jié)
以上是生活随笔為你收集整理的ORACLE EBS常用表及查询语句（最终整理版）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 台积电3nm首发客户仅剩苹果 AMD Z
- 下一篇： 基于LSTM网络的藏头诗和古诗自动生成（