Oracle ERP供应链常用信息查询

ORACLEEBS常用 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 查询语句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=204用户、责任及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,NATIONAL_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_FPAF供应商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,branch.party_idbranch_id,branch.party_namebank_branch_name,ieba.bank_account_numbank_account_numFROMap_suppliersass,hz_partieshp,iby_account_ownersiao,iby_ext_bank_accountsieba,hz_partiesbank,hz_partiesbranchWHEREass.party_id=hp.party_idANDhp.party_id=iao.account_owner_party_id(+)ANDiao.ext_bank_account_id=ieba.ext_bank_account_id(+)ANDieba.bank_id=bank.party_id(+)ANDieba.branch_id=branch.party_id(+)ORDERBYieba.creation_date;--供应商开户行地址信息SELECThps.party_idparty_id,hps.party_site_idparty_site_id,hl.location_idlocation_id,hl.countrycountry,hl.provinceprovince,hl.citycity,hl.address1address1,hl.address2address2,hl.address3address3,hl.address4address4FROMhz_party_siteshps,hz_locationshlWHEREhps.location_id=hl.location_idORDERBYhps.creation_date--供应商联系人信息SELECThr.subject_idsubject_id,hr.object_idobject_id,hr.party_idparty_id,hp.person_last_name||''||hp.person_middle_name||''||hp.person_first_namecontact_person,hcpp.phone_area_codephone_area_code,hcpp.phone_numberphone_number,hcpp.phone_extensionphone_extension,hcpf.phone_area_codefax_phone_area_code,hcpf.phone_numberfax_phone_number,hcpe.email_addressemail_addressFROMhz_relationshipshr,hz_contact_pointshcpp,hz_contact_pointshcpf,hz_contact_pointshcpe,hz_partieshpWHEREhr.object_id=hp.party_idANDhcpp.owner_table_id(+)=hr.party_idANDhcpf.owner_table_id(+)=hr.party_idANDhcpe.owner_table_id(+)=hr.party_idANDhr.object_type='PERSON'ANDhr.relationship_code(+)='CONTACT'ANDhcpp.owner_table_name(+)='HZ_PARTIES'ANDhcpf.owner_table_name(+)='HZ_PARTIES'ANDhcpe.owner_table_name(+)='HZ_PARTIES'ANDhcpp.contact_point_type(+)='PHONE'ANDhcpp.phone_line_type(+)='GEN'ANDhcpf.contact_point_type(+)='PHONE'ANDhcpf.phone_line_type(+)='FAX'ANDhcpe.contact_point_type(+)='EMAIL'ANDhcpe.phone_line_typeISNULLORDERBYhr.creation_date;--供应商地址主信息SELECTassa.vendor_site_idvendor_site_id,assa.vendor_idvendor_id,assa.vendor_site_codevendor_code,assa.vendor_site_codeaddress_short_name,assa.address_line1address_line1,assa.address_line2address_line2,assa.address_line3address_line3,assa.address_line4address_line4,assa.org_idorg_id,assa.countrycountry,assa.provinceprovince,assa.citycity,assa.countycounty,assa.zipzip,assa.pay_site_flagpay_site_flag,assa.purchasing_site_flagpurchasing_site_flag,assa.inactive_dateinactive_date,assa.creation_datecreation_date,assa.created_bycreated_by,assa.last_update_datelast_update_date,assa.last_updated_bylast_updated_by,assa.last_update_loginlast_update_loginFROMap_suppliersass,ap_supplier_sites_allassaWHEREassa.vendor_id=ass.vendor_id;--供应商地址联系人信息：phone、fax和EmailSELECThcpp.phone_area_codephone_area_code,hcpp.phone_numberphone_number,hcpp.phone_extensionphone_extension,hcpf.phone_area_codefax_phone_area_code,hcpf.phone_numberfax_phone_number,hcpe.email_addressemail_addressFROMap_supplier_sites_allassa,hz_contact_pointshcpp,hz_contact_pointshcpf,hz_contact_pointshcpe,hz_party_siteshpsWHEREassa.party_site_id=hps.party_site_idANDhcpp.owner_table_id(+)=assa.party_site_idANDhcpf.owner_table_id(+)=assa.party_site_idANDhcpe.owner_table_id(+)=assa.party_site_idANDhcpp.owner_table_name(+)='HZ_PARTY_SITES'ANDhcpf.owner_table_name(+)='HZ_PARTY_SITES'ANDhcpe.owner_table_name(+)='HZ_PARTY_SITES'ANDhcpp.contact_point_type(+)='PHONE'ANDhcpp.phone_line_type(+)='GEN'ANDhcpf.contact_point_type(+)='PHONE'ANDhcpf.phone_line_type(+)='FAX'ANDhcpe.contact_point_type(+)='EMAIL'ANDhcpe.phone_line_typeISNULL;--供应商地址收件人信息SELECTassa.party_site_idFROMap_supplier_sites_allassa--根据party_site_id得到供应商地址的收件人名称SELECThps.addresseeFROMhz_party_siteshps;--供应商银行帐户分配层次关系SELECT*FROMiby_pmt_instr_uses_all;--供应商银行帐户分配层次关系明细(不包括供应商层的分配信息):SELECT*FROMiby_external_payees_all;客户CUSTOMER--SQL查询--客户账户表以许继1063电网客户为例-->>PARTY_ID=21302SELECT*FROMhz_cust_accountsAAWHEREAA.CUST_ACCOUNT_ID=1063;--客户名称及地址全局信息表-->>PARTY_NUMBER=19316SELECT*FROMhz_partiesAAWHEREAA.PARTY_ID=21302;--客户地点账户主文件SELECT*FROMhz_cust_acct_sites_allWHERECUST_ACCOUNT_ID=1063;--客户地点(关联hz_cust_acct_sites_all)SELECT*FROMHZ_PARTY_SITESWHEREPARTY_ID=21302;--地点地址名称(关联hz_cust_acct_sites_all)SELECTAA.ADDRESS1,AA.ADDRESS_KEYFROMHZ_LOCATIONSAA,HZ_PARTY_SITESBBWHEREAA.LOCATION_ID=BB.LOCATION_IDANDBB.PARTY_ID=21302;--客户地点业务目的(关联hz_cust_acct_sites_all用CUST_ACCT_SITE_ID)SELECT*FROMHZ_CUST_SITE_USES_ALL;--客户地点详细信息表，以供应处OU的身份ORG_ID=119SELECTAA.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业务实体,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_CODEFROMhz_party_sitesAA,hz_cust_acct_sites_allBB,hz_locationsCC,HZ_CUST_SITE_USES_ALLDDWHEREAA.PARTY_SITE_ID=BB.PARTY_SITE_IDANDBB.CUST_ACCOUNT_ID=1063ANDBB.ORG_ID=119ANDAA.STATUS='A'ANDAA.LOCATION_ID=CC.LOCATION_IDANDBB.CUST_ACCT_SITE_ID(+)=DD.CUST_ACCT_SITE_IDANDDD.STATUS<>'I';--*************综合查询************----客户主数据SELECThca.cust_account_idcustomer_id,hp.party_numbercustomer_number,hp.party_namecustomer_name,hp.party_namecustomer_short_name,hca.customer_typecustomer_type,alt.meaningcustomer_type_meaning,hca.customer_class_codecustomer_class,alc.meaningcustomer_class_meaning,hp.tax_referencetax_registered_name,rt.nameterm_name,hca.creation_datecreation_date,hca.created_bycreated_by,hca.last_update_datelast_update_date,hca.last_updated_bylast_updated_by,hca.last_update_loginlast_update_loginFROMhz_partieshp,hz_cust_accountshca,ar_lookupsalt,ar_lookupsalc,hz_customer_profileshcp,ra_termsrtWHEREhp.party_id=hca.party_idANDhca.customer_type=alt.lookup_code(+)ANDalt.lookup_type='CUSTOMER_TYPE'ANDhca.customer_class_code=alc.lookup_code(+)ANDalc.lookup_type(+)='CUSTOMERCLASS'ANDhca.cust_account_id=hcp.cust_account_id(+)ANDhcp.standard_terms=rt.term_id(+)--客户收款方法SQLSELECTarm.namereceipt_method_nameFROMhz_cust_accountshca,ra_cust_receipt_methodsrcrm,ar_receipt_methodsarmWHEREhca.cust_account_id=rcrm.customer_idANDrcrm.receipt_method_id=arm.receipt_method_idORDERBYrcrm.creation_date;--客户账户层银行账户信息SQLSELECThca.cust_account_idcust_account_id,hp.party_idparty_id,bank.party_idbank_id,bank.party_namebank_name,branch.party_idbranch_id,branch.party_namebank_branch_name,ieba.bank_account_numbank_account_numFROMhz_cust_accountshca,hz_partieshp,iby_account_ownersiao,iby_ext_bank_accountsieba,hz_partiesbank,hz_partiesbranchWHEREhca.party_id=hp.party_idANDhp.party_id=iao.account_owner_party_id(+)ANDiao.ext_bank_account_id=ieba.ext_bank_account_id(+)ANDieba.bank_id=bank.party_id(+)ANDieba.branch_id=branch.party_id(+)ORDERBYieba.creation_date;--客户开户行地址信息SQLSELECThl.country||'-'||hl.province||'-'||hl.city||'-'||hl.address1||'-'||hl.address2||'-'||hl.address3||'-'||hl.address4bank_addressFROMhz_party_siteshps,hz_locationshlWHEREhps.location_id=hl.location_idORDERBYhps.creation_date;--客户账户层联系人信息：联系人、电话、手机和EmailSQLSELECThr.party_idparty_id,hcar.cust_account_idcust_account_id,hcar.cust_acct_site_idcust_acct_site_id,hp.person_last_name||''||hp.person_middle_name||''||hp.person_first_namecontact_person,hcpp.phone_area_codephone_area_code,hcpp.phone_numberphone_number,hcpp.phone_extensionphone_extension,hcpm.phone_area_codemobile_phone_area_code,hcpm.phone_numbermobile_phone_number,hcpe.email_addressemail_addressFROMhz_relationshipshr,hz_cust_account_roleshcar,hz_org_contactshoc,hz_contact_pointshcpp,hz_contact_pointshcpm,hz_contact_pointshcpe,hz_partieshp,hz_cust_accountshcaWHEREhr.object_id=hp.party_idANDhr.party_id=hcar.party_idANDhr.relationship_id=hoc.party_relationship_id(+)ANDhcpp.owner_table_id(+)=hr.party_idANDhcpm.owner_table_id(+)=hr.party_idANDhcpe.owner_table_id(+)=hr.party_idANDhr.object_type='PERSON'ANDhr.relationship_code(+)='CONTACT'ANDhcpp.owner_table_name(+)='HZ_PARTIES'ANDhcpm.owner_table_name(+)='HZ_PARTIES'ANDhcpe.owner_table_name(+)='HZ_PARTIES'ANDhcpp.contact_point_type(+)='PHONE'ANDhcpp.phone_line_type(+)='GEN'ANDhcpm.contact_point_type(+)='PHONE'ANDhcpm.phone_line_type(+)='MOBILE'ANDhcpe.contact_point_type(+)='EMAIL'ANDhcpe.phone_line_typeISNULLANDhr.subject_id=hca.party_idANDhcar.cust_acct_site_idISNULLORDERBYhr.creation_date;--客户地址SELECThcasa.cust_acct_site_idcustomer_site_id,hcasa.cust_account_idcustomer_id,hps.party_site_numbercustomer_site_code,hps.party_site_namecustomer_site_name,hl.address1address_line1,hl.address2address_line2,hl.address3address_line3,hl.address4address_line4,hcasa.org_idorg_id,hl.countrycountry,hl.provinceprovince,hl.citycity,hl.countycounty,hl.postal_codezip,hcasa.bill_to_flagbill_to_flag,hcasa.ship_to_flagship_to_flag,hca.creation_datecreation_date,hca.created_bycreated_by,hca.last_update_datelast_update_date,hca.last_updated_bylast_updated_by,hca.last_update_loginlast_update_loginFROMhz_cust_accountshca,hz_cust_acct_sites_allhcasa,hz_party_siteshps,hz_locationshlWHEREhca.cust_account_id=hcasa.cust_account_idANDhcasa.party_site_id=hps.party_site_idANDhps.location_id=hl.location_id;--客户账户层地址contactperson信息:phone,mobile,emailSELECThr.party_idparty_id,hcar.cust_account_idcust_account_id,hcar.cust_acct_site_idcust_acct_site_id,hp.person_last_name||''||hp.person_middle_name||''||hp.person_first_namecontact_person,hcpp.phone_area_codephone_area_code,hcpp.phone_numberphone_number,hcpp.phone_extensionphone_extension,hcpm.phone_area_codemobile_phone_area_code,hcpm.phone_numbermobile_phone_number,hcpe.email_addressemail_addressFROMhz_relationshipshr,hz_cust_account_roleshcar,hz_org_contactshoc,hz_contact_pointshcpp,hz_contact_pointshcpm,hz_contact_pointshcpe,hz_partieshp,hz_cust_accountshcaWHEREhr.object_id=hp.party_idANDhr.party_id=hcar.party_idANDhr.relationship_id=hoc.party_relationship_id(+)ANDhcpp.owner_table_id(+)=hr.party_idANDhcpm.owner_table_id(+)=hr.party_idANDhcpe.owner_table_id(+)=hr.party_idANDhr.object_type='PERSON'ANDhr.relationship_code(+)='CONTACT'ANDhcpp.owner_table_name(+)='HZ_PARTIES'ANDhcpm.owner_table_name(+)='HZ_PARTIES'ANDhcpe.owner_table_name(+)='HZ_PARTIES'ANDhcpp.contact_point_type(+)='PHONE'ANDhcpp.phone_line_type(+)='GEN'ANDhcpm.contact_point_type(+)='PHONE'ANDhcpm.phone_line_type(+)='MOBILE'ANDhcpe.contact_point_type(+)='EMAIL'ANDhcpe.phone_line_typeISNULLANDhr.subject_id=hca.party_idANDhca.cust_account_id=hcar.cust_account_idORDERBYhr.creation_date;--客户账户地点地址SELECThp.party_id,hca.cust_account_id,hcasa.cust_acct_site_id,hcasa.bill_to_flag,hcasa.ship_to_flag,hcsua.site_use_id,hcasa.party_site_id,hcsua.site_use_code,hcsua.primary_flag,hcsua.location,hcsua.org_idFROMhz_partieshp,hz_cust_accountshca,hz_party_siteshps,hz_cust_acct_sites_allhcasa,hz_cust_site_uses_allhcsuaWHEREhp.party_id=hca.party_idANDhca.cust_account_id=hcasa.cust_account_idANDhcasa.party_site_id=hps.party_site_idANDhcsua.cust_acct_site_id=hcasa.cust_acct_site_id--客户主配置文件SELECT*FROMhz_cust_profile_classes;SELECT*FROMhz_customer_profiles;SELECT*FROMhz_cust_prof_class_amts;SELECT*FROMhz_cust_profile_amts;订单OE--select*fromoe_order_headers_all销售头select*fromoe_order_lines_all销售行select*fromwsh_new_deliveries发送select*fromwsh_delivery_detailsselect*fromwsh_delivery_assignments--综合查询1-未结销售订单SELECTH.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来源编码,l.line_number行号,l.ordered_item物料,msi.description物料说明,l.order_quantity_uom订购单位,l.ordered_quantity订购数量,l.cancelled_quantity取消数量,l.shipped_quantity发运数量,l.schedule_ship_date计划发运日期,l.booked_flag登记标记,ol.MEANING工作流状态,l.cancelled_flag取消标记FROMOE_ORDER_HEADERS_ALLH,OE_ORDER_LINES_ALLL,HZ_CUST_ACCOUNTSCUST,hz_partieshp,hz_cust_site_uses_allship_use,hz_cust_site_uses_allbill_use,mtl_system_items_bmsi,oe_lookupsolWHERE1=1ANDH.HEADER_ID=L.HEADER_IDANDH.SOLD_TO_ORG_ID=CUST.CUST_ACCOUNT_IDandcust.party_id=hp.party_idandh.ship_to_org_id=ship_use.site_use_idandh.invoice_to_org_id=bill_use.site_use_idandl.flow_status_codenotin('CLOSED','CANCELLED')andl.inventory_item_id=msi.inventory_item_idandmsi.organization_id=141andl.flow_status_code=ol.LOOKUP_CODEandol.LOOKUP_TYPE='LINE_FLOW_STATUS'ANDCUST.ACCOUNT_NUMBERIN('91010072','91010067','91010036')orderbyparty_name，收货地，销售订单;采购申请PR--申请单头（以电网组织ORG_ID=112内部申请=14140002781为例SELECTPRH.REQUISITION_HEADER_ID申请单头ID,PRH.PREPARER_ID,PRH.Org_IdOU_ID,PRH.SEGMENT1申请单编号,PRH.Creation_Date创建日期,PRH.Created_By编制人ID,FU.USER_NAME用户名称,PP.FULL_NAME用户姓名,PRH.Approved_Date批准日期,PRH.Description说明,PRH.Authorization_Status状态,PRH.Type_Lookup_Code类型,PRH.Transferred_To_Oe_Flag传递标示FROMPO_REQUISITION_HEADERS_ALLPRH,FND_USERFU,per_people_fPPWHEREPRH.CREATED_BY=FU.USER_IDANDFU.EMPLOYEE_ID=PP.PERSON_IDANDPRH.ORG_ID=112ANDPRH.SEGMENT1='14140002781';-->>内部申请=14140002781申请单头ID=3379--申请单行明细SELECTPRL.REQUISITION_HEADER_ID申请单ID,PRL.REQUISITION_LINE_ID行ID,PRL.LINE_NUM行号,PRL.CATEGORY_ID分类ID,PRL.ITEM_ID物料ID,ITEM.SEGMENT1物料编码,PRL.ITEM_DESCRIPTION物料说明,PRL.Quantity需求数,PRL.Quantity_Delivered送货数,PRL.Quantity_Cancelled取消数,PRL.Unit_Meas_Lookup_Code单位,PRL.Unit_Price参考价,PRL.Need_By_Date需求日期,PRL.Source_Type_Code来源类型,PRL.Org_IdOU_ID,PRL.Source_Organization_Id对方组织ID,PRL.Destination_Organization_Id本方组织IDfromPO_REQUISITION_LINES_ALLPRL,MTL_SYSTEM_ITEMSITEMWHEREPRL.ORG_ID=112ANDPRL.ITEM_ID=ITEM.INVENTORY_ITEM_IDANDPRL.Destination_Organization_Id=ITEM.ORGANIZATION_IDANDPRL.REQUISITION_HEADER_ID=3379;--申请单头(加对方订单编号)SELECTPRH.REQUISITION_HEADER_ID申请单头ID,PRH.PREPARER_ID,PRH.Org_IdOU_ID,PRH.SEGMENT1申请单编号,PRH.Creation_Date创建日期,PRH.Created_By编制人ID,FU.USER_NAME用户名称,PP.FULL_NAME用户姓名,PRH.Approved_Date批准日期,PRH.Description说明,PRH.Authorization_Status状态,PRH.Type_Lookup_Code类型,PRH.Transferred_To_Oe_Flag传递标示,OEH.ORDER_NUMBER对方CO编号FROMPO_REQUISITION_HEADERS_ALLPRH,FND_USERFU,per_people_fPP,OE_ORDER_HEADERS_ALLOEHWHEREPRH.CREATED_BY=FU.USER_IDANDFU.EMPLOYEE_ID=PP.PERSON_IDANDPRH.REQUISITION_HEADER_ID=OEH.SOURCE_DOCUMENT_ID(+)ANDPRH.ORG_ID=112ANDPRH.SEGMENT1='14140002781';--(销售订单记录有对方OU_ID,申请单关键字SOURCE_DOCUMENT_ID申请单号SOURCE_DOCEMENT_REF)*******************综合查询类*******************--申请单头综合查询（进限制只能查询--电网组织ORG_ID=112)SELECTPRH.REQUISITION_HEADER_ID申请单头ID,PRH.Org_Id组织ID,PRH.SEGMENT1申请单编号,PRH.Creation_Date创建日期,PRH.Created_By编制人ID,FU.USER_NAME用户名称,PP.FULL_NAME用户姓名,PRH.Approved_Date批准日期,PRH.Description说明,PRH.Authorization_Status状态,PRH.Type_Lookup_Code类型,PRH.Transferred_To_Oe_Flag传递标示,PRL.REQUISITION_LINE_ID行ID,PRL.LINE_NUM行号,PRL.CATEGORY_ID分类ID,PRL.ITEM_ID物料ID,ITEM.SEGMENT1物料编码,PRL.ITEM_DESCRIPTION物料说明,PRL.Quantity需求数,PRL.Quantity_Delivered送货数,PRL.Quantity_Cancelled取消数,PRL.Unit_Meas_Lookup_Code单位,PRL.Unit_Price参考价,PRL.Need_By_Date需求日期,PRL.Source_Type_Code来源类型,PRL.Source_Organization_Id对方组织ID,PRL.Destination_Organization_Id本方组织IDFROMPO_REQUISITION_HEADERS_ALLPRH,FND_USERFU,per_people_fPP,PO_REQUISITION_LINES_ALLPRL,MTL_SYSTEM_ITEMSITEMWHEREPRH.CREATED_BY=FU.USER_IDANDFU.EMPLOYEE_ID=PP.PERSON_IDANDPRH.REQUISITION_HEADER_ID=PRL.REQUISITION_HEADER_IDANDPRH.Org_Id=PRL.ORG_IDANDPRL.ITEM_ID=ITEM.INVENTORY_ITEM_IDANDPRL.Destination_Organization_Id=ITEM.ORGANIZATION_IDANDPRH.ORG_ID=112;--若需创建视图只需在SELECT语句前加上CREATEORREPLACEVIEWCUX_INV_PR112AS采购订单PO--采购单头信息TYPE_LOOKUP_CODE='STANDARD'（以供应处OUORG_ID=119采购单=''为例）--类型说明TYPE_LOOKUP_CODE='STANDARD'为采购单TYPE_LOOKUP_CODE='BLANKET'为采购 协议 离婚协议模板下载合伙人协议 下载渠道分销协议免费下载敬业协议下载授课协议下载 SELECTPOH.ORG_IDOU_ID,POH.PO_HEADER_ID采购单头ID,POH.TYPE_LOOKUP_CODE类型,POH.AUTHORIZATION_STATUS状态,POH.VENDOR_ID供应商ID,VENDOR.VENDOR_NAME供应商名,POH.VENDOR_SITE_ID供应商地址ID,POH.VENDOR_CONTACT_ID供应商联系人ID,POH.SHIP_TO_LOCATION_ID本方收货地ID,POH.BILL_TO_LOCATION_ID本方收单地ID,POH.CREATION_DATE创建日期,POH.APPROVED_FLAG审批YN,POH.APPROVED_DATE审批日期,POH.COMMENTS采购单说明,POH.TERMS_ID条款ID,POH.AGENT_ID采购员ID,AGT_PP.LAST_NAME采购员,POH.CREATED_BY创建者ID,FU.USER_NAME创建用户,PP.FULL_NAME用户姓名FROMPO_HEADERS_ALLPOH,FND_USERFU,per_people_fPP,PER_ALL_PEOPLE_FAGT_PP,ap_suppliersVENDORWHEREPOH.CREATED_BY=FU.USER_IDANDFU.EMPLOYEE_ID=PP.PERSON_IDANDPOH.AGENT_ID=AGT_PP.PERSON_IDANDPOH.VENDOR_ID=VENDOR.VENDOR_IDANDPOH.ORG_ID=119ANDPOH.TYPE_LOOKUP_CODE='STANDARD'ANDPOH.SEGMENT1='14730005436';/*FND_USERFU,per_people_fPP用户相关表po_agents_name_v采购员视图---->PO_AGENTS.AGENT_ID=PER_ALL_PEOPLE_F.PERSON_ID采购员相关表ap_suppliers供应商主表*/-->>POH.SEGMENT1='14730005436'PO_HEADER_ID=10068--采购单行信息SELECTPOL.ORG_IDOU_ID,POL.PO_HEADER_ID采购单头ID,POL.PO_LINE_ID行ID,POL.LINE_NUM行号,POL.ITEM_ID物料ID,ITEM.SEGMENT1物料编码,POL.ITEM_DESCRIPTION物料说明,POL.UNIT_MEAS_LOOKUP_CODE单位,POL.UNIT_PRICE单价,PO_LCT.QUANTITY订购数,PO_LCT.QUANTITY_RECEIVED验收数,PO_LCT.QUANTITY_ACCEPTED接收数,PO_LCT.QUANTITY_REJECTED拒绝数,PO_LCT.QUANTITY_CANCELLED取消数,PO_LCT.QUANTITY_BILLED到票数,PO_LCT.PROMISED_DATE承诺日期,PO_LCT.NEED_BY_DATE需求日期FROMPO_LINES_ALLPOL,Po_Line_Locations_allPO_LCT,MTL_SYSTEM_ITEMSITEMWHEREPOL.ORG_ID=PO_LCT.ORG_IDANDPOL.PO_LINE_ID=PO_LCT.PO_LINE_IDANDPOL.ITEM_ID=ITEM.INVENTORY_ITEM_IDANDITEM.ORGANIZATION_ID=142ANDPOL.Org_Id=119ANDPOL.PO_HEADER_ID=10068;--说明：Po_Line_Locations_all系“发运表”--综合查询1，所分配给供应处组织的物料，存在采购协议，但缺失采购员或缺失仓库；selectMSIF.Segment1物料编码,MSIF.Description物料描述,MSIF.LONG_DESCRIPTION物料详细描述,--MSIF.primary_unit_of_measure计量单位,PRF.LAST_NAME采购员,MISD.subinventory_code默认接收库存,PLA.unit_price未税价,round(PLA.unit_price*(1+ZRB.percentage_rate/100),2)含税价,PV