1Cajan.ZORACLEEBS常用表查询语句1.OU、库存组织SELECThou.organization_idou_org_id,--org_idhou.nameou_name,--ou名称ood.organization_idorg_org_id,--库存组织idood.organization_codeorg_org_code,--库存组织代码msi.secondary_inventory_name,--子库存名称msi.description--子库存描述FROMhr_organization_informationhoi,--组织分类表hr_operating_unitshou,--ou视图org_organization_definitionsood,--库存组织定义视图mtl_secondary_inventoriesmsi--子库存信息表WHEREhoi.org_information1='OPERATING_UNIT'ANDhoi.organization_id=hou.organization_idANDood.operating_unit=hoi.organization_idANDood.organization_id=msi.organization_id--获取系统IDcallfnd_global.APPS_INITIALIZE(1318,50583,401)selectfnd_profile.VALUE('ORG_ID')FROMDUALselect*fromhr_operating_unitshouwherehou.organization_id=2042.用户、责任及HR--系统责任定义VIEW(FROMFND_RESPONSIBILITY_TL,FND_RESPONSIBILITY)SELECTAPPLICATION_ID,RESPONSIBILITY_ID,RESPONSIBILITY_KEY,END_DATE,RESPONSIBILITY_NAME,DESCRIPTIONFROMFND_RESPONSIBILITY_VL;--用户责任关系SELECTUSER_ID,RESPONSIBILITY_IDFROMFND_USER_RESP_GROUPS;--用户表SELECTUSER_ID,USER_NAME,EMPLOYEE_ID,PERSON_PARTY_ID,END_DATEFROMFND_USER;--人员表VIEWSELECTPERSON_ID,START_DATE,DATE_OF_BIRTH,EMPLOYEE_NUMBER,2Cajan.ZNATIONAL_IDENTIFIER,SEX,FULL_NAMEFROMper_people_f;--综合查询SELECTUSER_NAME,FULL_NAME,RESPONSIBILITY_NAME,CC.DESCRIPTIONFROMFND_USERAA,FND_USER_RESP_GROUPSBB,FND_RESPONSIBILITY_VLCC,per_people_fDDWHEREAA.USER_ID=BB.USER_IDANDBB.RESPONSIBILITY_ID=CC.RESPONSIBILITY_IDANDAA.EMPLOYEE_ID=DD.PERSON_IDANDRESPONSIBILITY_NAMElike'%供应处%'ORDERBYUSER_NAME;--综合查询--人员状况基本信息表SELECTPAF.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适合和同一值做比较有多种结果，不适合和多种值比较有多种结果CASEPAF.SEXWHEN'M'THEN'男'WHEN'F'THEN'女'ELSE'NULL'END性别1,--case用法一CASEWHENTO_CHAR(PAF.DATE_OF_BIRTH,'YYYY')<'1960'THEN'50年代'WHENTO_CHAR(PAF.DATE_OF_BIRTH,'YYYY')<'1970'THEN'60年代'WHENTO_CHAR(PAF.DATE_OF_BIRTH,'YYYY')<'1980'THEN'70年代'WHENTO_CHAR(PAF.DATE_OF_BIRTH,'YYYY')<'1990'THEN'80年代'WHENTO_CHAR(PAF.DATE_OF_BIRTH,'YYYY')<'2000'THEN'90年代'ELSE'21世纪'--case用法二END出生年代FROMPER_ALL_PEOPLE_FPAF3Cajan.Z3.供应商VENDOR--供应商主表数据：SELECTass.vendor_idvendor_id,ass.party_idparty_id,ass.segment1vendor_code,ass.vendor_namevendor_name,ass.vendor_namevendor_short_name,ass.vendor_type_lookup_codevendor_type,flv.meaningvendor_type_meaning,hp.tax_referencetax_registered_name,ass.payment_method_lookup_codepayment_method,att.nameterm_name,att.enabled_flagenabled_flag,att.end_date_activeend_date_active,ass.creation_datecreation_date,ass.created_bycreated_by,ass.last_update_datelast_update_date,ass.last_updated_bylast_updated_by,ass.last_update_loginlast_update_loginFROMap_suppliersass,fnd_lookup_valuesflv,hz_partieshp,ap_terms_tlattWHEREass.vendor_type_lookup_code=flv.lookup_code(+)ANDflv.lookup_type(+)='VENDORTYPE'ANDflv.language(+)=userenv('LANG')ANDass.party_id=hp.party_idANDatt.language=userenv('LANG')ANDass.terms_id=att.term_id(+)--供应商银行信息SELECTass.vendor_idvendor_id,ass.party_idparty_id,bank.party_idbank_id,bank.party_namebank_name,bra...
