Oracle ERP供應鏈常用信息查詢.docx

ORACLE EBS常用表查詢語句1. OU、庫存組織SELECT anization_id ou_org_id, -org_id ou_name, -ou名稱 anization_id org_org_id, -庫存組織id anization_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 _information1 = OPERATING_UNIT AND anization_id = anization_id AND ood.operating_unit = anization_id AND anization_id = anization_id-獲取系統(tǒng)IDcall fnd_global.APPS_INITIALIZE(1318,50583,401)select fnd_profile.VALUE(ORG_ID) FROM DUALselect * from hr_operating_units hou where anization_id=2042. 用戶、責任及HR-系統(tǒng)責任定義VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY)SELECT APPLICATION_ID, RESPONSIBILITY_ID, RESPONSIBILITY_KEY, END_DATE, RESPONSIBILITY_NAME, DESCRIPTION FROM FND_RESPONSIBILITY_VL;-用戶責任關(guān)系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, 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 系統(tǒng)ID, PAF.FULL_NAME 姓名, PAF.DATE_OF_BIRTH 出生日期, PAF.REGION_OF_BIRTH 出生地區(qū), PAF.NATIONAL_IDENTIFIER 身份證號, PAF.ATTRIBUTE1 招工來源, PAF.ATTRIBUTE3 員工類型, PAF.ATTRIBUTE11 集團合同號, PAF.original_date_of_hire 參加工作日期, PAF.PER_INFORMATION17 省份, DECODE(PAF.SEX,M,男,F,女,NULL) 性別, -decode 適合和同一值做比較有多種結(jié)果，不適合和多種值比較有多種結(jié)果 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) 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 * FROM hz_cust_acct_sites_all WHERE CUST_ACCOUNT_ID = 1063;-客戶地點 (關(guān)聯(lián)hz_cust_acct_sites_all)SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID = 21302;-地點地址名稱 (關(guān)聯(lián)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_ID = 21302;-客戶地點業(yè)務目的 (關(guān)聯(lián)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_NAME, BB.ORG_ID 業(yè)務實體, 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 業(yè)務目的,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 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; -*綜合查詢*-客戶主數(shù)據(jù)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, 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 AND hca.customer_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 receipt_method_nameFROM hz_cust_accounts 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_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.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 | - | vince | - | hl.city | - | hl.address1 | - | hl.address2 | - | hl.address3 | - | hl.address4 bank_addressFROM hz_party_sites hps, hz_locations hlWHERE hps.location_id = hl.location_idORDER BY hps.creation_date;-客戶賬戶層聯(lián)系人信息：聯(lián)系人、電話、手機和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 | | 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 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 = 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_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 ORDER 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_line4, _id org_id, hl.country country, vince 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 AND hcasa.party_site_id = hps.party_site_id AND hps.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 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 = 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_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 hca.cust_account_id = hcar.cust_account_id ORDER BY hr.creation_date;-客戶賬戶地點地址SELECT hp.party_id, hca.cust_account_id, hcasa.cust_acct_si