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_ope
2、rating_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-获取系统IDcall fn
3、d_global.APPS_INITIALIZE(1318,50583,401)select fnd_profile.VALUE(ORG_ID) FROM DUALselect * from hr_operating_units hou where hou.organization_id=2042. 用户、责任及HR-系统责任定义VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)SELECT APPLICATION_ID, RESPONSIBILITY_ID, RESPONSIBILITY_KEY, END_DATE, RESPONSIBI
4、LITY_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;-人员表VIEWSELECT PERSON_ID, START_DATE, DATE_OF_BIRTH, EMPLOYEE_NUMBER, NATIONAL_IDENTIFIER, SEX, FUL
5、L_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 RESP
6、ONSIBILITY_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_INFORMA
7、TION17 省份, 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)
8、 1980 THEN 70年代 WHEN TO_CHAR(PAF.DATE_OF_BIRTH, YYYY) 1990 THEN 80年代 WHEN TO_CHAR(PAF.DATE_OF_BIRTH, YYYY) PARTY_ID = 21302SELECT * FROM hz_cust_accounts AA WHERE AA.CUST_ACCOUNT_ID = 1063;-客户名称及地址全局信息表 - PARTY_NUMBER = 19316SELECT * FROM hz_parties AA WHERE AA.PARTY_ID = 21302;-客户地点账户主文件SELECT * FR
9、OM 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 AND BB.PARTY_I
10、D = 21302;-客户地点业务目的 (关联hz_cust_acct_sites_all 用CUST_ACCT_SITE_ID)SELECT * FROM HZ_CUST_SITE_USES_ALL;-客户地点详细信息表，以供应处OU的身份 ORG_ID = 119SELECT 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_N
11、AME, 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 WHE
12、RE AA.PARTY_SITE_ID = BB.PARTY_SITE_ID AND BB.CUST_ACCOUNT_ID = 1063 AND BB.ORG_ID = 119 AND AA.STATUS = A AND AA.LOCATION_ID = CC.LOCATION_ID AND BB.CUST_ACCT_SITE_ID(+) = DD.CUST_ACCT_SITE_ID AND DD.STATUS I; -*综合查询*-客户主数据SELECT hca.cust_account_id customer_id, hp.party_number customer_number, hp.
13、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.c
14、reated_by created_by, hca.last_update_date last_update_date, hca.last_updated_by last_updated_by, hca.last_update_login last_update_login FROM hz_parties hp, hz_cust_accounts hca, ar_lookups alt, ar_lookups alc, hz_customer_profiles hcp, ra_terms rt WHERE hp.party_id = hca.party_id AND hca.customer_
15、type = alt.lookup_code(+) AND alt.lookup_type = CUSTOMER_TYPE AND hca.customer_class_code = alc.lookup_code(+) AND alc.lookup_type(+) = CUSTOMER CLASS AND hca.cust_account_id = hcp.cust_account_id(+) AND hcp.standard_terms = rt.term_id(+)-客户收款方法SQLSELECT arm.name receipt_method_nameFROM hz_cust_acco
16、unts hca, ra_cust_receipt_methods rcrm, ar_receipt_methods armWHERE hca.cust_account_id = rcrm.customer_id AND rcrm.receipt_method_id = arm.receipt_method_idORDER BY rcrm.creation_date;-客户账户层银行账户信息SQLSELECT hca.cust_account_id cust_account_id, hp.party_id party_id, bank.party_id bank_id, bank.party_
17、name bank_name, branch.party_id branch_id, branch.party_name bank_branch_name, ieba.bank_account_num bank_account_numFROM hz_cust_accounts hca, hz_parties hp, iby_account_owners iao, iby_ext_bank_accounts ieba, hz_parties bank, hz_parties branchWHERE hca.party_id = hp.party_id AND hp.party_id = iao.
18、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;-客户开户行地址信息SQLSELECT hl.country | - | hl.province | - | hl.city | - | hl.address1 | - | hl.address2 | - | hl.addr
19、ess3 | - | hl.address4 bank_addressFROM hz_party_sites hps, hz_locations hlWHERE hps.location_id = hl.location_idORDER BY hps.creation_date;-客户账户层联系人信息：联系人、电话、手机和Email SQLSELECT 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
20、| | 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_
21、relationships hr, hz_cust_account_roles hcar, hz_org_contacts hoc, hz_contact_points hcpp, hz_contact_points hcpm, hz_contact_points hcpe, hz_parties hp, hz_cust_accounts hca WHERE hr.object_id = hp.party_id AND hr.party_id = hcar.party_id AND hr.relationship_id = hoc.party_relationship_id(+) AND hcpp.owner_table_id(+) = hr.party_id AND hcpm.owner_table_i
