Oracle ERP供应链常用信息查询

《Oracle ERP供应链常用信息查询》由会员分享，可在线阅读，更多相关《Oracle ERP供应链常用信息查询（39页珍藏版）》请在装配图网上搜索。

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 hc

22、pp.owner_table_id(+) = hr.party_id AND hcpm.owner_table_id(+) = hr.party_id AND hcpe.owner_table_id(+) = hr.party_id AND hr.object_type = PERSON AND hr.relationship_code(+) = CONTACT AND hcpp.owner_table_name(+) = HZ_PARTIES AND hcpm.owner_table_name(+) = HZ_PARTIES AND hcpe.owner_table_name(+) = HZ

23、_PARTIES AND hcpp.contact_point_type(+) = PHONE AND hcpp.phone_line_type(+) = GEN AND hcpm.contact_point_type(+) = PHONE AND hcpm.phone_line_type(+) = MOBILE AND hcpe.contact_point_type(+) = EMAIL AND hcpe.phone_line_type IS NULL AND hr.subject_id = hca.party_id AND hcar.cust_acct_site_id IS NULL OR

24、DER BY hr.creation_date;-客户地址SELECT hcasa.cust_acct_site_id customer_site_id, hcasa.cust_account_id customer_id, hps.party_site_number customer_site_code, hps.party_site_name customer_site_name, hl.address1 address_line1, hl.address2 address_line2, hl.address3 address_line3, hl.address4 address_line

25、4, 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_

26、by last_updated_by, hca.last_update_login last_update_login FROM hz_cust_accounts hca, hz_cust_acct_sites_all hcasa, hz_party_sites hps, hz_locations hl WHERE hca.cust_account_id = hcasa.cust_account_id AND hcasa.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id;-客户账户层地址contact

27、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.p

28、hone_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_p

29、arties hp, hz_cust_accounts hca WHERE hr.object_id = hp.party_id AND hr.party_id = hcar.party_id AND hr.relationship_id = hoc.party_relationship_id(+) AND hcpp.owner_table_id(+) = hr.party_id AND hcpm.owner_table_id(+) = hr.party_id AND hcpe.owner_table_id(+) = hr.party_id AND hr.object_type = PERSO

30、N AND hr.relationship_code(+) = CONTACT AND hcpp.owner_table_name(+) = HZ_PARTIES AND hcpm.owner_table_name(+) = HZ_PARTIES AND hcpe.owner_table_name(+) = HZ_PARTIES AND hcpp.contact_point_type(+) = PHONE AND hcpp.phone_line_type(+) = GEN AND hcpm.contact_point_type(+) = PHONE AND hcpm.phone_line_ty

31、pe(+) = MOBILE AND hcpe.contact_point_type(+) = EMAIL AND hcpe.phone_line_type IS NULL AND hr.subject_id = hca.party_id AND hca.cust_account_id = hcar.cust_account_id ORDER BY hr.creation_date;-客户账户地点地址SELECT hp.party_id, hca.cust_account_id, hcasa.cust_acct_site_id, hcasa.bill_to_flag, hcasa.ship_t

32、o_flag, hcsua.site_use_id, hcasa.party_site_id, hcsua.site_use_code, hcsua.primary_flag, hcsua.location, hcsua.org_id FROM hz_parties hp, hz_cust_accounts hca, hz_party_sites hps, hz_cust_acct_sites_all hcasa, hz_cust_site_uses_all hcsua WHERE hp.party_id = hca.party_id AND hca.cust_account_id = hca

33、sa.cust_account_id AND hcasa.party_site_id = hps.party_site_id AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id-客户主配置文件SELECT * FROM hz_cust_profile_classes;SELECT * FROM hz_customer_profiles;SELECT * FROM hz_cust_prof_class_amts;SELECT * FROM hz_cust_profile_amts;5. 订单OE-select * from oe_order

34、_headers_all 销售头select * from oe_order_lines_all 销售行select * from wsh_new_deliveries 发送select * from wsh_delivery_detailsselect * 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