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'
ANDhoi.organization_id = hou.organization_id
ANDood.operating_unit = hoi.organization_id
ANDood.organization_id = msi.organization_id
--获取系统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. 用户、责任及HR
--系统责任定义VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)
SELECT APPLICATION_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_KEY,
END_DATE,
RESPONSIBILITY_NAME,
DESCRIPTION
FROM FND_RESPONSIBILITY_VL;
--用户责任关系
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
ANDBB.RESPONSIBILITY_ID = CC.RESPONSIBILITY_ID
ANDAA.EMPLOYEE_ID = DD.PERSON_ID
AND RESPONSIBILITY_NAMElike'%供应处%'
ORDERBY USER_NAME;
--综合查询
--人员状况基本信息表
SELECT PAF.PERSON_ID 系统ID,
PAF.FULL_NAME 姓名,
PAF.DATE_OF_BIRTH 出生日期,
PAF.REGION_OF_BIRTH 出生地区,
PAF.NATIONAL_IDENTIFIER 身份证号,
PAF.ATTRIBUTE1 招工来源,
PAF.ATTRIBUTE3 员工类型,
PAF.ATTRIBUTE11 集团合同号,
PAF.original_date_of_hire 参加工作日期,
PAF.PER_INFORMATION17 省份,
DECODE(PAF.SEX,'M','男','F','女','NULL') 性别, --decode 适合和同一值做比较有多种结果，不适合和多种值比较有多种结果
CASE PAF.SEX
WHEN'M'THEN'男'
WHEN'F'THEN'女'
ELSE'NULL'
END性别1, --case 用法一
CASEWHEN 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世纪'--case 用法二
END出生年代
FROM PER_ALL_PEOPLE_F PAF
3. 供应商VENDOR
--供应商主表数据：
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(+)
ANDflv.lookup_type(+) = 'VENDOR TYPE'
ANDflv.language(+) = userenv('LANG')
ANDass.party_id = hp.party_id
ANDatt.language = userenv('LANG')
ANDass.terms_id = att.term_id(+)
--供应商银行信息
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
ANDhp.party_id = iao.account_owner_party_id(+)
ANDiao.ext_bank_account_id = ieba.ext_bank_account_id(+)
ANDieba.bank_id = bank.party_id(+)
ANDieba.branch_id = branch.party_id(+)
ORDERBY ieba.creation_date;
--供应商开户行地址信息
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
ORDERBY hps.creation_date
--供应商联系人信息
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
ANDhcpp.owner_table_id(+) = hr.party_id
ANDhcpf.owner_table_id(+) = hr.party_id
ANDhcpe.owner_table_id(+) = hr.party_id
ANDhr.object_type = 'PERSON'
ANDhr.relationship_code(+) = 'CONTACT'
ANDhcpp.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpf.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpe.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpp.contact_point_type(+) = 'PHONE'
ANDhcpp.phone_line_type(+) = 'GEN'
ANDhcpf.contact_point_type(+) = 'PHONE'
ANDhcpf.phone_line_type(+) = 'FAX'
ANDhcpe.contact_point_type(+) = 'EMAIL'
ANDhcpe.phone_line_type ISNULL
ORDERBY hr.creation_date;
--供应商地址主信息
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;
--供应商地址联系人信息：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
ANDhcpp.owner_table_id(+) = assa.party_site_id
ANDhcpf.owner_table_id(+) = assa.party_site_id
ANDhcpe.owner_table_id(+) = assa.party_site_id
ANDhcpp.owner_table_name(+) = 'HZ_PARTY_SITES'
ANDhcpf.owner_table_name(+) = 'HZ_PARTY_SITES'
ANDhcpe.owner_table_name(+) = 'HZ_PARTY_SITES'
ANDhcpp.contact_point_type(+) = 'PHONE'
ANDhcpp.phone_line_type(+) = 'GEN'
ANDhcpf.contact_point_type(+) = 'PHONE'
ANDhcpf.phone_line_type(+) = 'FAX'
ANDhcpe.contact_point_type(+) = 'EMAIL'
ANDhcpe.phone_line_type ISNULL;
--供应商地址收件人信息
SELECT assa.party_site_id
FROM ap_supplier_sites_all assa
--根据party_site_id得到供应商地址的收件人名称
SELECT hps.addressee FROM hz_party_sites hps;
--供应商银行帐户分配层次关系
SELECT * FROM iby_pmt_instr_uses_all;
--供应商银行帐户分配层次关系明细(不包括供应商层的分配信息):
SELECT * FROM iby_external_payees_all;
4. 客户CUSTOMER
--SQL 查询
--客户账户表以许继1063 电网客户为例-->>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;
--客户地点账户主文件
SELECT * FROM hz_cust_acct_sites_all WHERE CUST_ACCOUNT_ID = 1063;
--客户地点 (关联hz_cust_acct_sites_all)
SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID = 21302;
--地点地址名称 (关联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
ANDBB.PARTY_ID = 21302;
--客户地点业务目的 (关联hz_cust_acct_sites_all 用CUST_ACCT_SITE_ID)
SELECT * FROM HZ_CUST_SITE_USES_ALL;
--客户地点详细信息表，以供应处OU的身份 ORG_ID = 119
SELECT AA.PARTY_SITE_ID 客户组织地点ID,
AA.PARTY_ID 客户组织ID,
AA.LOCATION_ID 地点ID,
AA.PARTY_SITE_NUMBER 地点编号,
AA.IDENTIFYING_ADDRESS_FLAG 地址标示,
AA.STATUS 有效否,
AA.PARTY_SITE_NAME,
BB.ORG_ID 业务实体,
BB.bill_to_flag 收单标示,
BB.ship_to_flag 收货标示,
CC.ADDRESS1 地点名称,
DD.SITE_USE_ID,
DD.SITE_USE_CODE,
DD.PRIMARY_FLAG,
DD.STATUS,
DD.LOCATION 业务目的,
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
ANDBB.CUST_ACCOUNT_ID = 1063
ANDBB.ORG_ID = 119
ANDAA.STATUS = 'A'
ANDAA.LOCATION_ID = CC.LOCATION_ID
ANDBB.CUST_ACCT_SITE_ID(+) = DD.CUST_ACCT_SITE_ID
ANDDD.STATUS <> 'I';
--*************综合查询************--
--客户主数据
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
ANDhca.customer_type = alt.lookup_code(+)
ANDalt.lookup_type = 'CUSTOMER_TYPE'
ANDhca.customer_class_code = alc.lookup_code(+)
ANDalc.lookup_type(+) = 'CUSTOMER CLASS'
ANDhca.cust_account_id = hcp.cust_account_id(+)
ANDhcp.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
ANDrcrm.receipt_method_id = arm.receipt_method_id
ORDERBYrcrm.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
ANDhp.party_id = iao.account_owner_party_id(+)
ANDiao.ext_bank_account_id = ieba.ext_bank_account_id(+)
ANDieba.bank_id = bank.party_id(+)
ANDieba.branch_id = branch.party_id(+)
ORDERBY 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
ORDERBYhps.creation_date;
--客户账户层联系人信息：联系人、电话、手机和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
ANDhr.party_id = hcar.party_id
ANDhr.relationship_id = hoc.party_relationship_id(+)
ANDhcpp.owner_table_id(+) = hr.party_id
ANDhcpm.owner_table_id(+) = hr.party_id
ANDhcpe.owner_table_id(+) = hr.party_id
ANDhr.object_type = 'PERSON'
ANDhr.relationship_code(+) = 'CONTACT'
ANDhcpp.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpm.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpe.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpp.contact_point_type(+) = 'PHONE'
ANDhcpp.phone_line_type(+) = 'GEN'
ANDhcpm.contact_point_type(+) = 'PHONE'
ANDhcpm.phone_line_type(+) = 'MOBILE'
ANDhcpe.contact_point_type(+) = 'EMAIL'
ANDhcpe.phone_line_type ISNULL
ANDhr.subject_id = hca.party_id
ANDhcar.cust_acct_site_id ISNULL
ORDERBY 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
ANDhcasa.party_site_id = hps.party_site_id
ANDhps.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
ANDhr.party_id = hcar.party_id
ANDhr.relationship_id = hoc.party_relationship_id(+)
ANDhcpp.owner_table_id(+) = hr.party_id
ANDhcpm.owner_table_id(+) = hr.party_id
ANDhcpe.owner_table_id(+) = hr.party_id
ANDhr.object_type = 'PERSON'
ANDhr.relationship_code(+) = 'CONTACT'
ANDhcpp.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpm.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpe.owner_table_name(+) = 'HZ_PARTIES'
ANDhcpp.contact_point_type(+) = 'PHONE'
ANDhcpp.phone_line_type(+) = 'GEN'
ANDhcpm.contact_point_type(+) = 'PHONE'
ANDhcpm.phone_line_type(+) = 'MOBILE'
ANDhcpe.contact_point_type(+) = 'EMAIL'
ANDhcpe.phone_line_type ISNULL
ANDhr.subject_id = hca.party_id
ANDhca.cust_account_id = hcar.cust_account_id
ORDERBY hr.creation_date;
--客户账户地点地址
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
ANDhca.cust_account_id = hcasa.cust_account_id
ANDhcasa.party_site_id = hps.party_site_id
ANDhcsua.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 发送
select * from wsh_delivery_details
select * from wsh_delivery_assignments
--综合查询1-未结销售订单
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 订购数量,
l.cancelled_quantity 取消数量,
l.shipped_quantity 发运数量,
l.schedule_ship_date 计划发运日期,
l.booked_flag 登记标记,
ol.MEANING 工作流状态,
l.cancelled_flag 取消标记
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
WHERE1 = 1
ANDH.HEADER_ID = L.HEADER_ID
ANDH.SOLD_TO_ORG_ID = CUST.CUST_ACCOUNT_ID
andcust.party_id = hp.party_id
andh.ship_to_org_id = ship_use.site_use_id
andh.invoice_to_org_id = bill_use.site_use_id
andl.flow_status_code notin ('CLOSED', 'CANCELLED')
andl.inventory_item_id = msi.inventory_item_id
andmsi.organization_id = 141
andl.flow_status_code = ol.LOOKUP_CODE
andol.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
ANDCUST.ACCOUNT_NUMBER IN ('91010072', '91010067', '91010036')
orderby party_name，收货地，销售订单;
6. 采购申请PR
--申请单头（以电网组织ORG_ID=112 内部申请 =14140002781 为例
SELECT PRH.REQUISITION_HEADER_ID 申请单头ID,
PRH.PREPARER_ID,
PRH.Org_Id OU_ID,
PRH.SEGMENT1 申请单编号,
PRH.Creation_Date 创建日期,
PRH.Created_By 编制人ID,
FU.USER_NAME 用户名称,
PP.FULL_NAME 用户姓名,
PRH.Approved_Date 批准日期,
PRH.Description 说明,
PRH.Authorization_Status 状态,
PRH.Type_Lookup_Code 类型,
PRH.Transferred_To_Oe_Flag 传递标示
FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP
WHERE PRH.CREATED_BY = FU.USER_ID
ANDFU.EMPLOYEE_ID = PP.PERSON_ID
ANDPRH.ORG_ID = 112
ANDPRH.SEGMENT1 = '14140002781';
-->>内部申请 =14140002781 申请单头ID = 3379
--申请单行明细
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 需求数,
PRL.Quantity_Delivered 送货数,
PRL.Quantity_Cancelled 取消数,
PRL.Unit_Meas_Lookup_Code 单位,
PRL.Unit_Price 参考价,
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
ANDPRL.ITEM_ID = ITEM.INVENTORY_ITEM_ID
ANDPRL.Destination_Organization_Id = ITEM.ORGANIZATION_ID
ANDPRL.REQUISITION_HEADER_ID = 3379;
--申请单头 (加对方订单编号)
SELECT PRH.REQUISITION_HEADER_ID 申请单头ID,
PRH.PREPARER_ID,
PRH.Org_Id OU_ID,
PRH.SEGMENT1 申请单编号,
PRH.Creation_Date 创建日期,
PRH.Created_By 编制人ID,
FU.USER_NAME 用户名称,
PP.FULL_NAME 用户姓名,
PRH.Approved_Date 批准日期,
PRH.Description 说明,
PRH.Authorization_Status 状态,
PRH.Type_Lookup_Code 类型,
PRH.Transferred_To_Oe_Flag 传递标示,
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
ANDFU.EMPLOYEE_ID = PP.PERSON_ID
ANDPRH.REQUISITION_HEADER_ID = OEH.SOURCE_DOCUMENT_ID(+)
ANDPRH.ORG_ID = 112
ANDPRH.SEGMENT1 = '14140002781';
--(销售订单记录有对方 OU_ID,申请单关键字SOURCE_DOCUMENT_ID 申请单号SOURCE_DOCEMENT_REF)
*******************综合查询类*******************
--申请单头综合查询（进限制只能查询 --电网组织ORG_ID=112)
SELECT PRH.REQUISITION_HEADER_ID 申请单头ID,
PRH.Org_Id 组织ID,
PRH.SEGMENT1 申请单编号,
PRH.Creation_Date 创建日期,
PRH.Created_By 编制人ID,
FU.USER_NAME 用户名称,
PP.FULL_NAME 用户姓名,
PRH.Approved_Date 批准日期,
PRH.Description 说明,
PRH.Authorization_Status 状态,
PRH.Type_Lookup_Code 类型,
PRH.Transferred_To_Oe_Flag 传递标示,
PRL.REQUISITION_LINE_ID 行ID,
PRL.LINE_NUM 行号,
PRL.CATEGORY_ID 分类ID,
PRL.ITEM_ID 物料ID,
ITEM.SEGMENT1 物料编码,
PRL.ITEM_DESCRIPTION 物料说明,
PRL.Quantity 需求数,
PRL.Quantity_Delivered 送货数,
PRL.Quantity_Cancelled 取消数,
PRL.Unit_Meas_Lookup_Code 单位,
PRL.Unit_Price 参考价,
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
ANDFU.EMPLOYEE_ID = PP.PERSON_ID
ANDPRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
ANDPRH.Org_Id = PRL.ORG_ID
ANDPRL.ITEM_ID = ITEM.INVENTORY_ITEM_ID
ANDPRL.Destination_Organization_Id = ITEM.ORGANIZATION_ID
ANDPRH.ORG_ID = 112;
--若需创建视图只需在SELECT 语句前加上
CREATEORREPLACEVIEW CUX_INV_PR112 AS
7. 采购订单PO
--采购单头信息TYPE_LOOKUP_CODE='STANDARD' （以供应处OU ORG_ID=119 采购单='' 为例）
--类型说明 TYPE_LOOKUP_CODE='STANDARD'为采购单TYPE_LOOKUP_CODE='BLANKET'为采购协议
SELECT POH.ORG_ID OU_ID,
POH.PO_HEADER_ID 采购单头ID,
POH.TYPE_LOOKUP_CODE 类型,
POH.AUTHORIZATION_STATUS 状态,
POH.VENDOR_ID 供应商ID,
VENDOR.VENDOR_NAME 供应商名,
POH.VENDOR_SITE_ID 供应商地址ID,
POH.VENDOR_CONTACT_ID 供应商联系人ID,
POH.SHIP_TO_LOCATION_ID 本方收货地ID,
POH.BILL_TO_LOCATION_ID 本方收单地ID,
POH.CREATION_DATE 创建日期,
POH.APPROVED_FLAG 审批YN,
POH.APPROVED_DATE 审批日期,
POH.COMMENTS 采购单说明,
POH.TERMS_ID 条款ID,
POH.AGENT_ID 采购员ID,
AGT_PP.LAST_NAME 采购员,
POH.CREATED_BY 创建者ID,
FU.USER_NAME 创建用户,
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
ANDFU.EMPLOYEE_ID = PP.PERSON_ID
ANDPOH.AGENT_ID = AGT_PP.PERSON_ID
ANDPOH.VENDOR_ID=VENDOR.VENDOR_ID
ANDPOH.ORG_ID = 119
ANDPOH.TYPE_LOOKUP_CODE = 'STANDARD'
ANDPOH.SEGMENT1 = '14730005436';
/*
FND_USER FU, per_people_f PP 用户相关表
po_agents_name_v 采购员视图 ----> PO_AGENTS.AGENT_ID = PER_ALL_PEOPLE_F.PERSON_ID 采购员相关表
ap_suppliers 供应商主表
*/
-->>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 单价,
PO_LCT.QUANTITY 订购数,
PO_LCT.QUANTITY_RECEIVED 验收数,
PO_LCT.QUANTITY_ACCEPTED 接收数,
PO_LCT.QUANTITY_REJECTED 拒绝数,
PO_LCT.QUANTITY_CANCELLED 取消数,
PO_LCT.QUANTITY_BILLED 到票数,
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
ANDPOL.PO_LINE_ID = PO_LCT.PO_LINE_ID
ANDPOL.ITEM_ID = ITEM.INVENTORY_ITEM_ID
ANDITEM.ORGANIZATION_ID = 142
ANDPOL.Org_Id = 119
ANDPOL.PO_HEADER_ID = 10068;
--说明：Po_Line_Locations_all 系“发运表”
--综合查询1，所分配给供应处组织的物料，存在采购协议，但缺失采购员或缺失仓库；
select MSIF.Segment1 物料编码,
MSIF.Description 物料描述,
MSIF.LONG_DESCRIPTION 物料详细描述,
--MSIF.primary_unit_of_measure 计量单位,
PRF.LAST_NAME 采购员,
MISD.subinventory_code 默认接收库存,
PLA.unit_price 未税价,
round(PLA.unit_price * (1 + ZRB.percentage_rate / 100), 2) 含税价,
PV.VENDOR_NAME 供应商名称
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 = 119and
PHA.PO_HEADER_ID = PLA.Po_Header_Id and
PHA.Global_Agreement_Flag = 'Y'andPHA.Approved_Flag in ('Y', 'R') and
NVL(PHA.end_Date,sysdate) >= sysdateand
NVL(PLA.Expiration_Date,sysdate) >= sysdateand
PLA.Cancel_Flag = 'N'andPLA.Item_Id = MSIF.INVENTORY_ITEM_ID and
MSIF.ORGANIZATION_ID = 142and
MSIF.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+) and
MISD.ORGANIZATION_ID(+) = 142andMISD.default_type(+) = 2and
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_IDand
PVSA.VAT_CODE = ZRB.tax_rate_code and
(MISD.subinventory_code isnullorPRF.LAST_NAME isnull)
--采购其他相关表
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
8. 库存INV
--物料主表
select MSI.ORGANIZATION_ID 组织ID,
MSI.INVENTORY_ITEM_ID 物料ID,
MSI.SEGMENT1 物料编码,
MSI.DESCRIPTION 物料说明,
MSI.ITEM_TYPE 项目类型,
MSI.PLANNING_MAKE_BUY_CODE 制造或购买,
MSI.PRIMARY_UNIT_OF_MEASURE 基本度量单位,
MSI.BOM_ENABLED_FLAG BOM标志,
MSI.INVENTORY_ASSET_FLAG 库存资产否,
MSI.BUYER_ID 采购员ID,
MSI.PURCHASING_ENABLED_FLAG 可采购否,
MSI.PURCHASING_ITEM_FLAG 采购项目,
MSI.UNIT_OF_ISSUE 单位,
MSI.INVENTORY_ITEM_FLAG 是否为库存,
MSI.LOT_CONTROL_CODE 是否批量,
MSI.RESERVABLE_TYPE 是否要预留,
MSI.STOCK_ENABLED_FLAG 能否库存,
MSI.FIXED_DAYS_SUPPLY 固定提前期,
MSI.FIXED_LOT_MULTIPLIER 固定批量大小,
MSI.INVENTORY_PLANNING_CODE 库存计划方法,
MSI.MAXIMUM_ORDER_QUANTITY 最大定单数,
MSI.MINIMUM_ORDER_QUANTITY 最小定单数,
MSI.FULL_LEAD_TIME 固定提前期,
MSI.PLANNER_CODE 计划员码,
MISD.SUBINVENTORY_CODE 接收子仓库,
MSI.SOURCE_SUBINVENTORY 来源子仓库,
MSI.WIP_SUPPLY_SUBINVENTORY 供应子仓库,
MSI.ATTRIBUTE12 老编码,
MSI.INVENTORY_ITEM_STATUS_CODE 物料状态,
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(+)
andMSI.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+)
andMSI.ORGANIZATION_ID = MSS.ORGANIZATION_ID(+)
andMSI.INVENTORY_ITEM_ID = MSS.INVENTORY_ITEM_ID(+)
andMSI.ORGANIZATION_ID = 1155
andMSI.SEGMENT1 = '18020200012'
--物料库存数量
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
ANDMOQ.ORGANIZATION_ID = 1155
GROUPBY 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 间接费,
CST.Resource_Cost 人工费,
CST.OUTSIDE_PROCESSING_COST 外协费,
CST.OVERHEAD_COST 制造费
FROM CST_ITEM_COSTS CST
WHERE CST.COST_TYPE_ID = 2
AND CST.INVENTORY_ITEM_ID = 12781
ANDCST.ORGANIZATION_ID = 1155;
--综合查询-库存数量及成本
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 当前库存量,
CST.ITEM_COST 单位成本,
CST.MATERIAL_COST 材料成本,
CST.MATERIAL_OVERHEAD_COST 间接费,
CST.Resource_Cost 人工费,
CST.OUTSIDE_PROCESSING_COST 外协费,
CST.OVERHEAD_COST 制造费
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
GROUPBY MOQ.ORGANIZATION_ID,
MOQ.INVENTORY_ITEM_ID,
MOQ.SUBINVENTORY_CODE) MOQV
WHERE MSI.ORGANIZATION_ID = CST.ORGANIZATION_ID(+)
ANDMSI.INVENTORY_ITEM_ID = CST.INVENTORY_ITEM_ID(+)
ANDMSI.ORGANIZATION_ID = MOQV.ORGANIZATION_ID(+)
ANDMSI.INVENTORY_ITEM_ID = MOQV.INVENTORY_ITEM_ID(+)
ANDCST.COST_TYPE_ID = 2
ANDMSI.ORGANIZATION_ID = 1155
ANDMSI.SEGMENT1 = '18020200012'
--子库存列表
SELECT * FROM mtl_secondary_inventories;
--货位列表
SELECT ORGANIZATION_ID 组织代码,
INVENTORY_LOCATION_ID 货位内码,
SUBINVENTORY_CODE 子库名称,
SEGMENT1 货位编码
FROM mtl_item_locations;
--计划员表
SELECT PLANNER_CODE 计划员代码,
ORGANIZATION_ID 组织代码,
DESCRIPTION计划员描述,
MP.EMPLOYEE_ID 员工ID,
DISABLE_DATE 失效日期
FROM mtl_planners MP;
--科目设置等参数
select * from MTL_PARAMETERS MP
9. 物料清单BOM
--BOM主表bom_bill_of_materials
select AA.BILL_SEQUENCE_ID 清单序号,
AA.ASSEMBLY_ITEM_ID 装配件内码,
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
andAA.ORGANIZATION_ID = BB.ORGANIZATION_ID;
--BOM明细表bom_inventory_components
select BILL_SEQUENCE_ID 清单序号,
COMPONENT_SEQUENCE_ID 构件序号,
ITEM_NUM 项目序列,
OPERATION_SEQ_NUM 操作序列号,
COMPONENT_ITEM_ID 子物料内码,
COMPONENT_QUANTITY 构件数量,
DISABLE_DATE 失效日期,
supply_subinventory 供应子库存,
BOM_ITEM_TYPE
from bom_inventory_components;
--BOM明细综合查询 ( 组织限定供应处142 装配件 = '5XJ061988')
SELECT VBOM.BID 清单序号,
VBOM.F_ITEMID 装配件内码,
BB.SEGMENT1 物料编码,
BB.DESCRIPTION 物料说明,
VBOM.OGT_ID 组织内码,
VBOM.CID 操作ID,
VBOM.ITEM_NUM 物料序号,
VBOM.OPID 工序,
VBOM.C_ITEMID 子物料内码,
CC.SEGMENT1 物料编码,
CC.DESCRIPTION 物料说明,
VBOM.QTY 构件数量,
CC.PRIMARY_UOM_CODE 子计量单位码,
CC.PRIMARY_UNIT_OF_MEASURE 子计量单位名,
VBOM.WHSE 供应子仓库
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
andVBOM.OGT_ID = BB.ORGANIZATION_ID
andVBOM.C_ITEMID = CC.INVENTORY_ITEM_ID
andVBOM.OGT_ID = CC.ORGANIZATION_ID
andVBOM.OGT_ID = 142
andBB.SEGMENT1 = '5XJ061988'
ORDERBY VBOM.ITEM_NUM;
--单层BOM成本查询(需系统提交请求计算后)
select inventory_item_id, organization_id, item_cost, program_update_date
from bom.cst_item_costs
where inventory_item_id = 23760
andorganization_id = 142;
select inventory_item_id, organization_id, item_cost, program_update_date
from cst_item_cost_details
where inventory_item_id = 23760
andorganization_id = 142;
10. 作业任务WIP
说明：查询作业任务头以及作业任务工序和BOM情况
--作业任务头信息表
--（以直流 OU_ID=117；ORGANIZATION_ID=1155; 及任务WIP_ENTITY_NAME='XJ39562'; 装配件编码SEGMENT1 = '07D9202.92742'为例）
SELECT AA.WIP_ENTITY_ID 任务令ID,
AA.ORGANIZATION_ID 组织ID,
AA.WIP_ENTITY_NAME 任务名称,
AA.ENTITY_TYPE 任务类型,
AA.CREATION_DATE 创建日期,
AA.CREATED_BY 创建者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
andAA.ORGANIZATION_ID = BB.ORGANIZATION_ID
andAA.ORGANIZATION_ID = 1155
andAA.WIP_ENTITY_NAME = 'XJ39562';
--=>WIP_ENTITY_ID = 48825
--离散作业任务详细主信息表
--用途 1）作业任务下达及完成情况查询
--说明 1）此表包括wip_entities表大部分信息 2)重复作业任务表为 wip_repetitive_items, wip_repetitive_schedules
select AA.WIP_ENTITY_ID 任务令ID,
BB.WIP_ENTITY_NAME 任务名称,
AA.ORGANIZATION_ID 组织ID,
AA.SOURCE_LINE_ID 行ID,
AA.STATUS_TYPE 状态TYPE,
AA.PRIMARY_ITEM_ID 装配件ID,
CC.SEGMENT1 物料编码,
CC.DESCRIPTION 物料说明,
AA.FIRM_PLANNED_FLAG,
AA.JOB_TYPE 作业类型,
AA.WIP_SUPPLY_TYPE 供应TYPE,
AA.CLASS_CODE 任务类别,
AA.SCHEDULED_START_DATE 起始时间,
AA.DATE_RELEASED 下达时间,
AA.SCHEDULED_COMPLETION_DATE 完工时间,
AA.DATE_COMPLETED 完工时间,
AA.DATE_CLOSED 关门时间,
AA.START_QUANTITY 计划数,
AA.QUANTITY_COMPLETED 完工数,
AA.QUANTITY_SCRAPPED 报废数,
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
andAA.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID
andAA.ORGANIZATION_ID = CC.ORGANIZATION_ID
andAA.organization_id = 1155
andBB.WIP_ENTITY_NAME = 'XJ39562';
/*
1）任务状态TYPE值说明：
STATUS_TYPE =1 未发放的-收费不允许
STATUS_TYPE =3 发入-收费允许
STATUS_TYPE =4 完成-允许收费
STATUS_TYPE =5 完成-不允许收费
STATUS_TYPE =6 暂挂-不允许收费
STATUS_TYPE =7 已取消-不允许收费
STATUS_TYPE =8 等待物料单加载
STATUS_TYPE =9 失败的物料单加载
STATUS_TYPE =10 等待路线加载
STATUS_TYPE =11 失败的路线加载
STATUS_TYPE =12 关闭-不可收费
STATUS_TYPE =13 等待-成批加载
STATUS_TYPE =14 等待关闭
STATUS_TYPE =15 关闭失败
2）供应类型TYPE值说明：
WIP_SUPPLY_TYPE =1 推式
WIP_SUPPLY_TYPE =2 装配拉式
WIP_SUPPLY_TYPE =3 操作拉式
WIP_SUPPLY_TYPE =4 大量
WIP_SUPPLY_TYPE =5 供应商
WIP_SUPPLY_TYPE =6 虚拟
WIP_SUPPLY_TYPE =7 以帐单为基础
*/
--离散作业任务工序状况表
select AA.ORGANIZATION_ID 组织ID,
AA.WIP_ENTITY_ID 任务令ID,
BB.WIP_ENTITY_NAME 任务名称,
AA.OPERATION_SEQ_NUM 工序号,
AA.DESCRIPTION 工序描述,
AA.DEPARTMENT_ID 部门ID,
AA.SCHEDULED_QUANTITY 计划数量,
AA.QUANTITY_IN_QUEUE 排队数量,
AA.QUANTITY_RUNNING 运行数量,
AA.QUANTITY_WAITING_TO_MOVE 待移动数量,
AA.QUANTITY_REJECTED 故障品数量,
AA.QUANTITY_SCRAPPED 报废品数量,
AA.QUANTITY_COMPLETED 完工数量,
AA.FIRST_UNIT_START_DATE 最早一个单位上线时间,
AA.FIRST_UNIT_COMPLETION_DATE 最早一个单位完成时间,
AA.LAST_UNIT_START_DATE 最后一个单位上线时间,
AA.LAST_UNIT_COMPLETION_DATE 最后一个单位完工时间,
AA.PREVIOUS_OPERATION_SEQ_NUM 前一工序序号,
AA.NEXT_OPERATION_SEQ_NUM 下一工序序号,
AA.COUNT_POINT_TYPE 是否自动计费,
AA.BACKFLUSH_FLAG 倒冲否,
AA.MINIMUM_TRANSFER_QUANTITY 最小传送数量,
AA.DATE_LAST_MOVED 最后移动时间
from wip_operations AA,wip_entities BB
where AA.WIP_ENTITY_ID = BB.WIP_ENTITY_ID
andBB.WIP_ENTITY_NAME = 'XJ39562';
--离散作业任务子查询———工单工序状况查询（不单独使用）
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
where1 = 1
ANDWDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
ANDWDJ.wip_entity_id = '48825'
groupbyWDJ.ORGANIZATION_ID,WDJ.WIP_ENTITY_ID;
--离散作业任务BOM (无材料费)
SELECT WOP.ORGANIZATION_ID 组织ID,
WOP.WIP_ENTITY_ID 任务令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 供应类型,
WOP.DATE_REQUIRED 要求日期,
WOP.INVENTORY_ITEM_ID 子物料ID,
DD.SEGMENT1 子物料编码,
DD.DESCRIPTION 子物料说明,
WOP.QUANTITY_PER_ASSEMBLY 单位需量,
WOP.REQUIRED_QUANTITY 总需求量,
WOP.QUANTITY_ISSUED 已发放量,
WOP.COMMENTS 注释,
WOP.SUPPLY_SUBINVENTORY 供应子库
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
andBB.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID
andBB.ORGANIZATION_ID = CC.ORGANIZATION_ID
andWOP.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID
andWOP.ORGANIZATION_ID = DD.ORGANIZATION_ID
andWOP.ORGANIZATION_ID = 1155
andBB.WIP_ENTITY_NAME = 'XJ39562';
--作业任务已发放材料处理记录清单0101（最详细）（内码为48825为例）
--用途 1）查询工单发料详细明细，包括发料类型、时间、用户等
select MTL.TRANSACTION_ID 交易ID,
MTL.INVENTORY_ITEM_ID 项目ID,
CC.SEGMENT1 物料编码,
CC.DESCRIPTION 物料说明,
MTL.ORGANIZATION_ID 组织ID,
MTL.SUBINVENTORY_CODE 子库名称,
MTL.TRANSACTION_TYPE_ID 交易类型ID,
BB.TRANSACTION_TYPE_NAME 交易类型名称,
MTL.TRANSACTION_QUANTITY 交易数量,
MTL.TRANSACTION_UOM 单位,
MTL.TRANSACTION_DATE 交易日期,
MTL.TRANSACTION_REFERENCE 交易参考,
MTL.TRANSACTION_SOURCE_ID 参考源ID,
FF.WIP_ENTITY_NAME 任务名称,
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
andMTL.CREATED_BY = DD.USER_ID
andMTL.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID
andMTL.ORGANIZATION_ID = CC.ORGANIZATION_ID
andDD.EMPLOYEE_ID = EE.PERSON_ID
andMTL.TRANSACTION_SOURCE_ID = FF.WIP_ENTITY_ID
and MTL.Transaction_Type_Idin (35, 38, 43, 48)
andMTL.ORGANIZATION_ID = 1155
andMTL.TRANSACTION_SOURCE_ID = 48825;
--按工单的材料费汇总（不单独使用）
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)
andMTL.ORGANIZATION_ID = 1155
andMTL.TRANSACTION_SOURCE_ID = 48825
groupby MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID;
--离散作业任务子查询01———材料消耗状况及材料费综合查询
--用途1）查询发料状况2）查询材料费物料小计
SELECT WOP.ORGANIZATION_ID 组织ID,
WOP.WIP_ENTITY_ID 任务令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 供应类型,
WOP.DATE_REQUIRED 要求日期,
WOP.INVENTORY_ITEM_ID 子物料ID,
DD.SEGMENT1 子物料编码,
DD.DESCRIPTION 子物料说明,
WOP.QUANTITY_PER_ASSEMBLY 单位需量,
WOP.REQUIRED_QUANTITY 总需求量,
WOP.QUANTITY_ISSUED 已发放量,
CST.AMT 已发生材料费,
WOP.COMMENTS 注释,
WOP.SUPPLY_SUBINVENTORY 供应子库
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)
andMTL.ORGANIZATION_ID = 1155
andMTL.TRANSACTION_SOURCE_ID = 48825
groupby 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
andBB.PRIMARY_ITEM_ID = CC.INVENTORY_ITEM_ID
andBB.ORGANIZATION_ID = CC.ORGANIZATION_ID
andWOP.INVENTORY_ITEM_ID = DD.INVENTORY_ITEM_ID
andWOP.ORGANIZATION_ID = DD.ORGANIZATION_ID
andWOP.ORGANIZATION_ID = CST.orgID
andWOP.WIP_ENTITY_ID = CST.wipID
andWOP.OPERATION_SEQ_NUM = CST.oprID
andWOP.INVENTORY_ITEM_ID = CST.itemID
andWOP.ORGANIZATION_ID = 1155
andBB.WIP_ENTITY_NAME = 'XJ39562';
--离散作业任务子查询0201———作业资源报工明细表
SELECT WTA.ORGANIZATION_ID 组织代码,
WTA.TRANSACTION_ID 交易代码,
WTA.REFERENCE_ACCOUNT 参考科目,
WTA.TRANSACTION_DATE 报工日期,
WTA.WIP_ENTITY_ID 任务令内码,
WTA.ACCOUNTING_LINE_TYPE 会计栏类型,
WTA.BASE_TRANSACTION_VALUE 费用额,
WTA.CONTRA_SET_ID 反方集代码,
WTA.PRIMARY_QUANTITY 基本数量,
WTA.RATE_OR_AMOUNT 率或金额,
WTA.BASIS_TYPE 基本类型,
WTA.RESOURCE_ID 资源代码,
WTA.COST_ELEMENT_ID 成本要素ID,
WTA.ACCOUNTING_LINE_TYPE 成本类型ID,
WTA.OVERHEAD_BASIS_FACTOR 费用因子,
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
andDD.EMPLOYEE_ID = EE.PERSON_ID
andWTA.BASE_TRANSACTION_VALUE <> 0
andWTA.ORGANIZATION_ID = 1155
andWTA.WIP_ENTITY_ID = 48839;
--成本类型ID ACCOUNTING_LINE_TYPE
SELECT * FROM MFG_LOOKUPS ML
WHERE ML.LOOKUP_TYPE LIKE'CST_ACCOUNTING_LINE_TYPE'
ORDERBYML.LOOKUP_CODE;
--成本要素ID COST_ELEMENT_ID
--(待补充--------------------------------------------------------------------------?)
--统计人工费与制造费(不单独应用)
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
ANDWTA_COST.WIP_ENTITY_ID = '48839'
GROUPBYWTA_COST.ORGANIZATION_ID,WTA_COST.WIP_ENTITY_ID;
--工单进度及费用信息综合查询(未下达及下达零发料和报工的看不到)
select WE.WIP_ENTITY_NAME 任务名称,
MSI.SEGMENT1 物料,
MSI.DESCRIPTION 物料描述,
MSI.PRIMARY_UNIT_OF_MEASURE 单位,
WDJ.SCHEDULED_START_DATE 计划开始时间,
WDJ.SCHEDULED_COMPLETION_DATE 计划完成时间,
WDJ.START_QUANTITY 工单数量,
WDJ.QUANTITY_COMPLETED 完成数量,
WDJ.DATE_RELEASED 实际开始时间,
WDJ.DATE_COMPLETED 时间完成时间,
WDJ.DESCRIPTION 工单备注,
PP.SEGMENT1 项目号,
PP.DESCRIPTION 项目描述,
PT.TASK_NUMBER 任务号,
PT.DESCRIPTION 任务描述,
WO.COUNT_OPER 工序数,
WO1.OPERATION_SEQ_NUM 当前工序,
WO1.DESCRIPTION 当前工序描述,
MTA.MT_FEE 材料费,
WCT.HR_FEE 人工费,
WCT.MD_FEE 制造费,
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
groupby WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID) WO, --工序进度
(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)
groupby MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID) MTA, --材料费
(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
groupby WTA_COST.ORGANIZATION_ID, WTA_COST.WIP_ENTITY_ID) WCT --人工与制造
WHERE1 = 1
ANDWE.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
ANDWE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
ANDWDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
ANDWDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID
ANDWE.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
ANDWO1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.OPER = WO1.OPERATION_SEQ_NUM
ANDWE.ORGANIZATION_ID = MTA.ORGANIZATION_ID
ANDWE.WIP_ENTITY_ID = MTA.WIP_ENTITY_ID(+)
ANDWE.ORGANIZATION_ID = WCT.ORGANIZATION_ID
ANDWE.WIP_ENTITY_ID = WCT.WIP_ENTITY_ID(+)
ANDWDJ.PROJECT_ID = PP.PROJECT_ID(+)
ANDWDJ.TASK_ID = PT.TASK_ID(+)
andwe.organization_id = 1155
andwe.wip_entity_id = '48825';
--工单进度及费用信息综合查询(不论是否下达和发料都能看到)
SELECT WDJ.WIP_ENTITY_ID 任务令ID,
WE.WIP_ENTITY_NAME 任务名称,
WDJ.ORGANIZATION_ID 组织ID,
WDJ.STATUS_TYPE 状态,
WDJ.PRIMARY_ITEM_ID 装配件ID,
MSI.SEGMENT1 物料编码,
MSI.DESCRIPTION 物料说明,
WDJ.FIRM_PLANNED_FLAG 任务类型,
WDJ.JOB_TYPE 作业类型,
WDJ.WIP_SUPPLY_TYPE 供应类型,
WDJ.CLASS_CODE 任务类别,
WDJ.SCHEDULED_START_DATE 起始时间,
WDJ.DATE_RELEASED 下达时间,
WDJ.DATE_COMPLETED 完工时间,
WDJ.DATE_CLOSED 关闭时间,
WDJ.START_QUANTITY 计划数,
WDJ.QUANTITY_COMPLETED 完工数,
WDJ.QUANTITY_SCRAPPED 报废数,
WDJ.NET_QUANTITY MRP净值,
WDJ.DESCRIPTION 工单备注,
WDJ.COMPLETION_SUBINVENTORY 接收子库,
WDJ.COMPLETION_LOCATOR_ID 货位ID,
WDJ.PROJECT_ID 项目ID,
WDJ.TASK_ID 项目任务ID,
PP.SEGMENT1 项目号,
PP.DESCRIPTION 项目描述,
PT.TASK_NUMBER 任务号,
PT.DESCRIPTION 任务描述,
WPF.COUNT_OPER 工序数,
WPF.CUR_OPER 当前工序,
WPF.CUR_OPERNAME 工序名,
WPF.MT_FEE 材料费,
WPF.HR_FEE 人工费,
WPF.MD_FEE 制造费
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
groupby WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID) WO, --工序进度
(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)
groupby MTL.ORGANIZATION_ID, MTL.TRANSACTION_SOURCE_ID) MTA, --材料费
(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 (selectWTA.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
groupby WTA_COST.ORGANIZATION_ID, WTA_COST.WIP_ENTITY_ID) WCT --人工与制造
WHERE1 = 1
ANDWDJ1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID(+)
ANDWO1.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND WO.OPER = WO1.OPERATION_SEQ_NUM
ANDWDJ1.ORGANIZATION_ID = MTA.ORGANIZATION_ID
ANDWDJ1.WIP_ENTITY_ID = MTA.WIP_ENTITY_ID(+)
ANDWDJ1.ORGANIZATION_ID = WCT.ORGANIZATION_ID
ANDWDJ1.WIP_ENTITY_ID = WCT.WIP_ENTITY_ID(+)) WPF
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
ANDWDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
ANDWDJ.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID
ANDWDJ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
ANDWDJ.PROJECT_ID = PP.PROJECT_ID(+)
ANDWDJ.TASK_ID = PT.TASK_ID(+)
ANDWDJ.ORGANIZATION_ID = WPF.ORGANIZATION_ID(+)
ANDWDJ.WIP_ENTITY_ID = WPF.WIP_ENTITY_ID(+)
ANDWDJ.organization_id = 1155
andPP.SEGMENT1 = '07D9202';
11. MRP相关查询
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 * fromgl_summary_templates 科目汇总模板
select * from gl_account_hierarchies科目汇总模板层次
13. 应收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 付款计划
14. 应付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 核销
15. 应用、值集、弹性域
--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#'andid_flex_num=50671
select * from fnd_profile_options_vl
select * from fnd_concurrent_programs 程序表
select * from fnd_concurrent_requests 请求表
select * from fnd_concurrent_processes 进程表
16. 许继配送管理相关表
---为了实现系统内方便的配送发货模式而开发
--配送关系（以供应处角度ORGANIZATION_ID = 142）
SELECT AA.CUSTOMER_RELATION_ID 配送关系ID,
AA.ORGANIZATION_ID 组织ID,
AA.CUST_ACCOUNT_ID 客户ID,
CC.PARTY_NAME 客户名称,
AA.CUST_ACCT_SITE_ID 配送地ID,
DD.LOCATION 客户地点,
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 入库确认,
AA.INBOUND_COST_CCID 入库账户ID,
FF.concatenated_segments 入库账户,
AA.MANAGE_CHARGE 加价率,
AA.SETTLE_MODE 结算模式,
AA.INBOUND_SUBIN_CODE 接收子仓库,
AA.OUTBOUND_SUBIN_CODE 配送子库存,
AA.ATTRIBUTE1 直接生产发料,
AA.Creation_Date 创建日期,
AA.CREATED_BY 创建者,
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
ANDAA.CUST_ACCOUNT_ID = BB.CUST_ACCOUNT_ID
ANDBB.PARTY_ID = CC.PARTY_ID
ANDAA.Cust_Acct_Site_Id = DD.Site_Use_Id
ANDDD.STATUS = 'A'
ANDAA.OUTBOUND_COST_CCID = EE.code_combination_id
ANDAA.INBOUND_COST_CCID = FF.code_combination_id
andCC.PARTY_NAME = '许继电气电网保护自动化公司';
--配送单头
SELECT AA.DN_HEADER_ID 配送单ID,
AA.DN_NUMBER 配送单编号,
AA.DN_STATUS_CODE 状态,
AA.CUST_ACCOUNT_ID 客户ID,
CC.PARTY_NAME 客户名称,
AA.CUST_ACCT_SITE_ID 配送地址ID,
DD.LOCATION 客户地点,
AA.DELIVERY_ORG_ID 配送方组织ID,
AA.CUST_ORG_ID 客户组织ID,
AA.MANAGE_CHARGE 费率,
AA.INBOUND_CONFIRM_FLAG 入库确认否,
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
WHEREAA.DELIVERY_ORG_ID = 142
ANDAA.CUST_ACCOUNT_ID = BB.CUST_ACCOUNT_ID
ANDBB.PARTY_ID = CC.PARTY_ID
ANDAA.Cust_Acct_Site_Id = DD.Site_Use_Id
ANDDD.STATUS = 'A'
ANDAA.SO_HEADER_ID = EE.HEADER_ID
ANDAA.DN_NUMBER = '14780016022';
--配送单明细
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 发出仓,
AA.OUTBOUND_LOCATOR_ID 发出货位,
AA.REQUIRE_DATE 需求日期,
AA.REQUIRE_QTY 需求数,
AA.OUTBOUND_QTY 已出库,
AA.INBOUND_QTY 已接收,
AA.ATTRIBUTE1 最近确认接收数,
AA.INBOUND_SUBIN_CODE 入库仓,
AA.INBOUND_LOCATOR_ID 入库货位,
AA.RETURN_NO_RECEIVE_QTY 退回数,
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
ANDAA.INVENTORY_ITEM_ID = CC.INVENTORY_ITEM_ID
ANDBB.DELIVERY_ORG_ID = CC.ORGANIZATION_ID
ANDAA.SO_LINE_ID = LL.LINE_ID
ANDBB.DN_NUMBER = '14780016022';
99-其他相关表
1) 会计科目组合表
--查询会计科目分段信息
select * fromgl_code_combinations;
--查询会计科目组合信息
select * from gl_code_combinations_kfv;
2) 查询自定义的客户化相关表和视图
-- 静态数据字典中的视图分为三类，它们分别由三个前缀够成：user_*、 all_*、 dba_*
-- user_* 该视图存储了关于当前用户所拥有的对象的信息。（即所有在该用户模式下的对象）
-- all_* 该视图存储了当前用户能够访问的对象的信息。（与user_*相比，all_* 并不需要拥有该对象，只需要具有访问该对象的权限即可）
-- dba_* 该视图存储了数据库中所有对象的信息。（前提是当前用户具有访问这些数据库的权限，一般来说必须具有管理员权限
--查询该用户拥有哪些表
select * fromuser_tables where table_name like'CUX%';
--查询该用户拥有哪些视图
select * fromuser_views where view_name like'CUX%';
--查询该用户拥有哪些索引
select * fromuser_indexes;
3) 查询物料处理记录
说明：mtl_material_transactions这个表记录了所有涉及仓库收发的物料交易记录，包括：采购、WIP、订单、杂项等多种处理模式的内容。
--举例：查询某用户在电网的账户别名发放清单
select AA.TRANSACTION_ID 交易代码,
AA.INVENTORY_ITEM_ID 项目内码,
CC.SEGMENT1 物料编码,
CC.DESCRIPTION 物料说明,
AA.ORGANIZATION_ID 组织代码,
AA.SUBINVENTORY_CODE 子库名称,
AA.TRANSACTION_TYPE_ID 类型ID,
BB.TRANSACTION_TYPE_NAME 类型名称,
AA.TRANSACTION_QUANTITY 数量,
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) 实际成本,
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_IDand
DD.EMPLOYEE_ID = EE.PERSON_ID and
AA.ORGANIZATION_ID =1155AND
CC.SEGMENT1='07D9202.92742'and
AA.TRANSACTION_DATE >= TO_DATE('2011-01-29 00:00:00','YYYY-MM-DD HH24:MI:SS')
orderby aa.TRANSACTION_ID;
/* 物料处理记录mtl_material_transactions表，类型与成本说明：
--杂项收TRANSACTION_TYPE_ID = 41 录入价格优先，=Actual_Cost，移动平均TRANSACTION_QUANTITY > 0 ,调整类似
注：1）如果再接收界面录入了价格，以录入价格计入Actual_Cost，进行移动平均
2）如果没有录入价格，字段NULL，则系统会以当前成本接收，计入Actual_Cost
--杂项发TRANSACTION_TYPE_ID = 31 以出=Actual_Cost，TRANSACTION_QUANTITY < 0 ,调整类似
--采购收TRANSACTION_TYPE_ID = 18 以入=Actual_Cost，移动平均TRANSACTION_QUANTITY > 0
--采购退TRANSACTION_TYPE_ID = 36 以出=Actual_Cost，TRANSACTION_QUANTITY < 0
注：1) 系统按采购成本退货和扣除库存金额，不考虑已消耗状况；
2) 如果库存金额够扣除，则扣除后重新计算出一个新成本；
3）如果库存金额不够扣除，则扣除全部金额，就会出现有库存量而单位成本=0的物资，不够扣的部分计入字段VARIANCE_AMOUNT。
--作业发TRANSACTION_TYPE_ID = 35 以当前成本出，=Actual_Cost，TRANSACTION_QUANTITY < 0 ，特定组件发料类似
--作业退TRANSACTION_TYPE_ID = 43 以当前成本入，=Actual_Cost，不移动平均TRANSACTION_QUANTITY > 0
--配送出TRANSACTION_TYPE_ID = 100 以当前成本出，=Actual_Cost，TRANSACTION_QUANTITY < 0
--配送退TRANSACTION_TYPE_ID = 101 以配送价入，=Actual_Cost，移动平均TRANSACTION_QUANTITY > 0
--销售发TRANSACTION_TYPE_ID = 33 以当前成本出，=Actual_Cost，TRANSACTION_QUANTITY < 0
--销售退TRANSACTION_TYPE_ID = 15 以当前成本入，=Actual_Cost，TRANSACTION_QUANTITY > 0
*/
--物料处理记录类型列表
select bb.transaction_type_id 类型ID,
bb.transaction_type_name 别名,
bb.description 说明
from mtl_transaction_types BB
orderby bb.transaction_type_id
--交易来源类型列表
SELECT *
FROM mtl_txn_source_types
--交易原因代码表
SELECT REASON_ID 原因代码, REASON_NAME 名称, DESCRIPTION描述
FROM inv.mtl_transaction_reasons;
扫一扫
8436
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
