EBS 常用表关联

来源：互联网发布：淘宝网中老年运动衣编辑：程序博客网时间：2024/05/17 08:44

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'

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 '%供应处%'

ORDER BY 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 用法一

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世纪' --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_codepayment_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(+) = 'VENDORTYPE'

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(+)

ORDER BY 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

ORDER BY 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_codefax_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 IS NULL

ORDER BY 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_flagpurchasing_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_codephone_area_code,

hcpp.phone_number phone_number,

hcpp.phone_extension phone_extension,

hcpf.phone_area_codefax_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 IS NULL;

--供应商地址收件人信息

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(+) = 'CUSTOMERCLASS'

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

ORDER BYrcrm.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(+)

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 BYhps.creation_date;

--客户账户层联系人信息：联系人、电话、手机和Email SQL

SELECT hr.party_id party_id,

hcar.cust_account_id cust_account_id,

hcar.cust_acct_site_idcust_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_codemobile_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 IS NULL

ANDhr.subject_id = hca.party_id

ANDhcar.cust_acct_site_id IS NULL

ORDER BY hr.creation_date;

--客户地址

SELECT hcasa.cust_acct_site_idcustomer_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_idcust_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_codemobile_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 IS NULL

ANDhr.subject_id = hca.party_id

ANDhca.cust_account_id = hcar.cust_account_id

ORDER BY 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

WHERE 1 = 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 not in ('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')

order by 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_fPP,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 语句前加上

CREATE OR REPLACE VIEW 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_FAGT_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 = 119 and

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) >= sysdate and

NVL(PLA.Expiration_Date,sysdate) >= sysdate and

PLA.Cancel_Flag = 'N' andPLA.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 andMISD.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_IDand

PVSA.VAT_CODE = ZRB.tax_rate_code and

(MISD.subinventory_code is null orPRF.LAST_NAME is null)

--采购其他相关表

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

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 间接费,

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

GROUP BY 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'

ORDER BY 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

and BB.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

where 1 = 1

ANDWDJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID

ANDWDJ.wip_entity_id = '48825'

group byWDJ.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

group by 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

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

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'

ORDER BYML.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'

GROUP BYWTA_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

group by WDJ.ORGANIZATION_ID, WDJ.WIP_ENTITY_ID)WO, --工序进度

(select MTL.ORGANIZATION_ID,

MTL.TRANSACTION_SOURCE_IDWIP_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, --材料费

(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,

WTA.BASE_TRANSACTION_VALUE,

0) HR_FEE1,

decode(COST_ELEMENT_ID,

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

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,

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, --工序进度

(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, --材料费

(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,

WTA.BASE_TRANSACTION_VALUE,

0) HR_FEE1,

decode(COST_ELEMENT_ID,

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

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_combinationsgcc 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

WHERE AA.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_itemsCC,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 =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表，类型与成本说明：

--杂项收 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

order by bb.transaction_type_id

--交易来源类型列表

SELECT *

FROM mtl_txn_source_types

--交易原因代码表

SELECT REASON_ID 原因代码, REASON_NAME 名称, DESCRIPTION 描述

FROM inv.mtl_transaction_reasons;