没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
1
Cajan.Z
ORACLE EBS 常用表查询语句
1. OU、库存组织
SELECT hou.organization_id ou_org_id, --org_id
hou.name ou_name, --ou
名称
ood.organization_id org_org_id, --
库存组织
id
ood.organization_code org_org_code, --
库存组织代码
msi.secondary_inventory_name, --
子库存名称
msi.description --
子库存描述
FROM hr_organization_information hoi, --
组织分类表
hr_operating_units hou, --ou
视图
org_organization_definitions ood, --
库存组织定义视图
mtl_secondary_inventories msi --
子库存信息表
WHERE hoi.org_information1 = 'OPERATING_UNIT'
AND hoi.organization_id = hou.organization_id
AND ood.operating_unit = hoi.organization_id
AND ood.organization_id = msi.organization_id
--
获取系统
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,
2
Cajan.Z
START_DATE,
DATE_OF_BIRTH,
EMPLOYEE_NUMBER,
NATIONAL_IDENTIFIER,
SEX,
FULL_NAME
FROM per_people_f;
--
综合查询
SELECT USER_NAME, FULL_NAME, RESPONSIBILITY_NAME, CC.DESCRIPTION
FROM FND_USER AA,
FND_USER_RESP_GROUPS BB,
FND_RESPONSIBILITY_VL CC,
per_people_f DD
WHERE AA.USER_ID = BB.USER_ID
AND BB.RESPONSIBILITY_ID = CC.RESPONSIBILITY_ID
AND AA.EMPLOYEE_ID = DD.PERSON_ID
AND RESPONSIBILITY_NAME like '%供应处%'
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 出生年代
3
Cajan.Z
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(+)
AND flv.lookup_type(+) = 'VENDOR TYPE'
AND flv.language(+) = userenv('LANG')
AND ass.party_id = hp.party_id
AND att.language = userenv('LANG')
AND ass.terms_id = att.term_id(+)
--
供应商银行信息
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,
4
Cajan.Z
hz_parties hp,
iby_account_owners iao,
iby_ext_bank_accounts ieba,
hz_parties bank,
hz_parties branch
WHERE ass.party_id = hp.party_id
AND hp.party_id = iao.account_owner_party_id(+)
AND iao.ext_bank_account_id = ieba.ext_bank_account_id(+)
AND ieba.bank_id = bank.party_id(+)
AND ieba.branch_id = branch.party_id(+)
ORDER BY ieba.creation_date;
--
供应商开户行地址信息
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_code fax_phone_area_code,
hcpf.phone_number fax_phone_number,
hcpe.email_address email_address
FROM hz_relationships hr,
hz_contact_points hcpp,
hz_contact_points hcpf,
hz_contact_points hcpe,
hz_parties hp
WHERE hr.object_id = hp.party_id
AND hcpp.owner_table_id(+) = hr.party_id
5
Cajan.Z
AND hcpf.owner_table_id(+) = hr.party_id
AND hcpe.owner_table_id(+) = hr.party_id
AND hr.object_type = 'PERSON'
AND hr.relationship_code(+) = 'CONTACT'
AND hcpp.owner_table_name(+) = 'HZ_PARTIES'
AND hcpf.owner_table_name(+) = 'HZ_PARTIES'
AND hcpe.owner_table_name(+) = 'HZ_PARTIES'
AND hcpp.contact_point_type(+) = 'PHONE'
AND hcpp.phone_line_type(+) = 'GEN'
AND hcpf.contact_point_type(+) = 'PHONE'
AND hcpf.phone_line_type(+) = 'FAX'
AND hcpe.contact_point_type(+) = 'EMAIL'
AND hcpe.phone_line_type IS NULL
ORDER BY hr.creation_date;
--
供应商地址主信息
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,
剩余39页未读,继续阅读
资源评论
cailibin
- 粉丝: 4
- 资源: 7018
下载权益
C知道特权
VIP文章
课程特权
开通VIP
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功