[VIP专享]EBS查看表名

OracleEBS常用表和视图fnd_user系统用户表fnd_application应用信息表FND_PROFILE_OPTIONS_VL 系统配置文件fnd_menus 菜单fnd_menu_entries_tlFND_NEW_MESSAGES消息表FND_FORM表单表FND_CONCURRENT_PROGRAMS_VL 并发程序视图FND_CONCURRENT_PROGRAMS_TLFND_CONCURRENT_PROGRAMSFND_DESCR_FLEX_COL_USAGE_VLFND_DESCR_FLEX_COL_USAGE_TLFND_DESCR_FLEX_COLUMN_USAGESFND_EXECUTABLES_FORM_V 可执行并发程序视图FND_EXECUTABLES_TLFND_EXECUTABLESFND_DESCRIPTIVE_FLEXSFND_CONC_REQ_SUMMARY_V 并发请求视图FND_CONCURRENT_REQUESTSFND_RESPONSIBILITY职责表FND_RESPONSIBILITY_VL 职责FND_USER_RESP_GROUPS 用户职责fnd_flex_value_sets值集表FND_FLEX_VALUESFND_IREP_ALL_INTERFACES 接口表FND_IREP_CLASSESFnd_Irep_Classes_Tlfnd_territories_vl 国家视图fnd_log_messages 日志表fnd_form_functions 功能FND_DOCUMENT_SEQUENCES 单据序列FND_DOC_SEQUENCE_ASSIGNMENTS 序列分配fnd_id_flexs 关键弹性域定义表FND_ID_FLEX_STRUCTURES 弹性域结构表FND_ID_FLEX_SEGMENTS 弹性域段表fnd_descriptive_flexs 描述性弹性域属性表FND_DESCR_FLEX_CONTEXTS 弹性域列类别表FND_DESCR_FLEX_COLUMN_USAGES 弹性域列类别属性表FND_FLEX_VALUE_SETS 值集表FND_FLEX_VALUES 值表Fnd_Flex_Values_Tl 值描述表FND_FLEX_VALUE_NORM_HIERARCHY 值层次关系表FND_FORM_CUSTOM_RULES 个性化视图fnd_profile_options_tl 配置文件------------------------------------------------------------------------------------------------------PER_ALL_PEOPLE_F员工表per_people_f员工视图MTL_PLANNERS计划员表po_buyers_all_v采购员视图org_organization_definitions 库存组织视图hr_operating_units OU视图cux_wip_location_sign 位置号表HCM_PRODUCTION_LINE 生产线车间表user_objectsALL_SOURCEALL_PROCEDURES 存储过程wf_local_user_rolesad_bugs 补丁ad_applied_patchesXDO_TEMPLATES_B 模板XDO_TEMPLATES_TL------------------------------------------------------------------------------------------------po_vendors供应商信息表po_vendor_sites_all供应商地点信息视图AP_SUPPLIER_SITES_ALL 供应商地点表po_vendor_contacts------------------------------------------------------------------------------------------------------------------------------------------------wip_entities作业名信息表wip_discrete_jobs离散作业表wip_requirement_operations作业名物料需求发放表WIP_ACCOUNTING_CLASSES 工单分类表-------------------------------------------------------------------------------------------------------------------hr_locations交货地点hr_all_organization_unitsrcv_transactions_interface接收事务接口表po_interface_errors 接口错误信息表（pie.interface_line_id = rti.interface_transaction_id）po_requisitions_interface_all 请购接口表po_requisition_headers_all 请购单头信息po_requisition_lines_all请购单行信息po_req_distributions_all请购单分配行po_headers_all 采购订单头表po_lines_all 采购订单行表po_line_locations_all采购发运表po_distributions_all分配po_releases_all发放rcv_shipment_headers接收发送头表rcv_shipment_lines接收发送行表rcv_transactions接收交易表rcv_receiving_sub_ledger暂记应付表mtl_material_transactions物料事务处理mtl_transaction_types物料事务处理类型RCV_SUPPLYpo_buyers_all_v 采购员视图PO_AGENTS采购员表------------------------------------------------------------------------------------------------------------------------------------------------bom_bill_of_materials物料清单表bom_inventory_components物料清单构成表bom_departments 部门表bom_operational_routings 工艺路线头表BOM_OPERATION_SEQUENCES 工艺路线行表bom_operation_resources资源表------------------------------------------------------------------------------------------------------------------------------------------MTL_CATEGORY_SET_VALID_CATS_V 物料类别视图mtl_categories_b_kfv 物料类别视图(不含物料类别集)mtl_category_set_valid_cats 物料类别集表mtl_item_categories物料与类别对应表mtl_categories_b物料类别表mtl_system_items_b物料主表MTL_SYSTEM_ITEMS_TLmtl_item_sub_defaults默认接收子库mtl_secondary_inventories子库表mtl_onhand_quantities库存数据表MTL_ONHAND_QUANTITIES_DETAIL 库存现有量表MTL_SECONDARY_INVENTORIES 仓库表mtl_item_locations 项目货位表mtl_material_transactions 出入库记录表mtl_transaction_types事务处理类型表MTL_TXN_REQUEST_HEADERS 物料搬运单头表MTL_TXN_REQUEST_LINES物料搬运单行表mtl_supply 供应表mtl_demand 需求表mtl_parameters 组织参数cst_item_costs物料成本cst_cost_types成本类型MTL_SERIAL_NUMBERS序列号MTL_MATERIAL_STATUSES_TL序列号状态MFG_LOOKUPS序列号状态（SERIAL_NUM_STATUS）------------------------------------------------------------------------------------------------------------------------hr_organization_units 组织及库存组织视图hz_parties 客户表hz_relationships客户关系表HZ_PARTY_SITES 客户地点表HZ_LOCATIONS地点表hz_cust_accounts 客户账户表HZ_CUST_ACCT_SITES_ALL 客户地点账户表HZ_CUST_SITE_USES_ALL 客户地点业务目的表HZ_CUSTOMER_PROFILES 账户配置文件表HZ_CUST_PROFILE_AMTS 账户配置文件金额表HZ_CONTACT_POINTS联系方式表HZ_CODE_ASSIGNMENTS 客户的税表HZ_CUSTOMER_PROFILES 滞纳费用表---------------------------------------------------------------------------------------------wsh_trips 发运记录表wsh_trip_stops 发运停靠站记录表wsh_new_deliveries 交付记录wsh_delivery_legswsh_delivery_details 交付表wsh_delivery_assignments----------------------------------------------------------------------------------------mrp_sourcing_rules来源补充规则mrp_sr_receipt_orgmrp_sr_source_orgmrp_sr_assignmentsMRP_ASSIGNMENT_SETS来源规则分配集表po_approved_supplier_list批准的供应商列表PO_ASL_ATTRIBUTES批准的供应商列表属性-------------------------------------------------------------------------------------------------------------------------------------------------ENG_CHANGE_STATUSES_VL E CO状态pjm_seiban_numbers内外销标识表pa_projects_allmrp_designatorsDRP名称mrp_forecast_designators预测名称表mrp_forecast_dates预测表mrp_forecast_updates预测更改mrp_gross_requirements毛需求mrp_item_purchase_ordersMRP采购计划mrp_item_wip_entitiesMRP项目离散作业mrp_plans计划表mrp_recommendations计划建议mrp_workbench_bucket_datesMTL_SUPPLY_DEMAND_TEMP 物料供应需求-------------------------------------------------------------------------------------------------------------------------------------------MTL_TRANSACTION_FLOW_HEADERS公司间事务处理流mtl_transaction_flow_lines_v公司间事务处理流节点MTL_INTERCOMPANY_PARAMETERS_V 公司间关系QP_LIST_HEADERS_B价目表QP_LIST_HEADERS_TL价目表Qp_List_LinesQp_Pricing_Attributes--------------------------------------------------------------------------------------------------------------------------------------------------MTL_UNITS_OF_MEASURE单位MTL_UNITS_OF_MEASURE_TLMTL_UOM_CLASSES单位分类mtl_uom_classes_tlMTL_UOM_CONVERSIONS单位换算标准MTL_UOM_CLASS_CONVERSIONS单位换算分类间--------------------------------------------------------------------------------------------------------------------------------------------------AP_INVOICES_ALL 发票头AP_INVOICE_LINES_ALL 发票行xla_ae_headers 会计分录头xla_ae_lines 会计分录行--------------------------------------------------------------------------------------------------------------oe_system_parameters_allOM 系统参数ak_web_user_sec_attr_valuesORG_ACCT_PERIODS库存会计期间GL_PERIODS总账会计期间ZX_RATES_B税率代码ZX_PARTY_TAX_PROFILE供应商的税的配置文件xla_event_types_tlxla_event_classes_tl-------------------------------------------------------------------------------------------------------------------ap_suppliers 供应商头信息ap_supplier_sites_all 供应商地点信息hz_code_assignments 供应商税率zx_party_tax_profile 供应商税信息iby_ext_bank_accounts 供应商银行账号iby_ext_bank_branches_v 供应商银行支行iby_ext_banks_v 供应商银行iby_account_owners 银行账户的拥有者iby_pmt_instr_uses_all 银行账户所在的地点ap_supplier_contacts 供应商联系人hz_contact_points 供应商联系人电话等信息pos_supp_prof_ext_b 供应商地点附加信息iby_external_payees_all 供应商付款信息。

hr_employees员工表hr_operating_units OU设置表，寻找org_id shor_code代表业务实体短FND_USER用户设置表cux_ap_supplier_emp_import_pkg员工供应商导入pkgCUX_AP_SUPPLIER_EMP_IMPORT_T员工供应商导入临时表ap_suppliers供应商基本表ap_supplier_sites供应商地点PER_ALL_PEOPLE_F人员存储表身份证信息，人员编号org_organization_definitions业务实体ar_cash_receipts_all收款tableGL_CODE_COMBINATIONS总账科目表gl_ledgers 帐薄gl_ledger_norm_seg_vals账簿的平衡段值设定ce_bank_accounts银行账户表JA_CN_CFS_ITEM_MAPPING_LINES现金流量映射表行ja_cn_cfs_item_mapping_hdrs_v现金流量映射表头FND_DESCR_FLEX_CONTEXTS弹性域相关FND_FLEX_VALIDATION_RULESFND_FLEX_VALIDATION_RULES_LINESFND_FLEX_VALUE_SETS弹性域值集设置 值集名称FND_FLEX_VALUES_TL值集明细FND_FLEX_VALUESFND_ID_FLEXSFND_ID_FLEX_SEGMENTSFND_ID_FLEX_STRUCTURESgl_je_batches98日记账输入批gl_je_headers凭证头gl_je_lines凭证尾gl_code_combinations科目组合表gl_balances科目余额表hr_locations交货位置表gl_je_batches日记账批ode代表业务实体短码，不设置无法导入员工供应商。

设置路径：库存管理—设置—组织—业务实体7务实体—其他。

EBS系统财务常用表和各表关联关系1、发票:1.1 发票批表AP_BATCHES_ALL当在ERP系统AP模块录入发票批或通过接口表导入发票批后，系统会在发票批表AP_BATCHES_ALL中新增记录，用于保存发票批ID、发票批名、创建时间等信息，以下为该表的常用字段及字段含义介绍：字段名类型是否可为空字段说明取值来源BATCH_ID NUMBER(15)N发票批ID序列ap_batches_sBATCH_NAME VARCHAR2(50)N发票批名BATCH_DATE DATE N发票批日期LAST_UPDATE_DATE DATE N最后更新日期LAST_UPDATED_BY NUMBER (15)N最后更新人FND_er_id CREATION_DATE DATE Y创建日期CREATED_BY NUMBER (15)Y创建人FND_er_idORG_ID NUMBER (15)Y组织ID HR_OPERATING_UNITS.organization_idGL_DATE DATE Y总帐日期1.2 发票表AP_INVOICES_ALL当在ERP系统AP 模块录入发票或通过接口表导入发票后，系统会在发票表AP_INVOICES_ALL中新增记录，用于保存发票编号、发票ID、供应商ID、发票金额、发票日期、发票币种、付款状态等信息，以下为该表的常用字段及字段含义介绍：字段名类型是否可为空字段说明取值来源INVOICE_ID NUMBER(15)N 发票ID序列ap_invoices_sLAST_UPDATE_DATE DATE N最后更新日期LAST_UPDATED_BY NUMBER(15)N 最后更新者FND_er_idVENDOR_ID NUMBER(15)N 供应商IDPO_VENDORS.vendor_idINVOICE_NUM VARCHAR2(50)N 发票编号SET_OF_BOOKS_ID NUMBER(15)N 帐簿IDGL_SETS_OF_BOOKS.set_of_books_idINVOICE_CURRENCY_CODE VARCHAR2(15)N 发票币种常用“CNY”PAYMENT_CURRENCY_CODE VARCHAR2(15)N 付款币种INVOICE_AMOUNT NUMBER Y 发票金额VENDOR_SITE_ID NUMBER(15)Y 供应商地点IDPO_VENDOR_SITES_ALL.vendor_site_idAMOUNT_PAID NUMBER Y 已付金额INVOICE_DATE DATE Y 发票日期SOURCE VARCHAR2(25)Y 发票来源INVOICE_TYPE_LOOKUP_CODE VARCHAR2(25)Y发信用：贷项通知单票类型借方：借项通知单费用报告：费用报表混合：混合先付：预付款发票标准：标准发票DESCRIPTION VARCHAR2(240)Y 发票摘要BATCH_ID NUMBER(15)Y 发票批IDAP_BATCHES_ALL.batch_idPAYMENT_STATUS_FLAG VARCHAR2(1)Y 付款状态Y：全额付款N：未付款P：部分付款CREATION_DATE DATE Y 创建日期CREATED_BY NUMBER(15)Y 创建者PO_HEADER_ID NUMBER(15)Y 波河订单头IDPO_HEADERS_ALL.po_header_idAPPROVED_AMOUNT NUMBER Y 批准金额DOC_SEQUENCE_VALUE NUMBER Y 凭证编号APPROVAL_STATUS VARCHAR2(25)Y 审批状态CANCELLED_DATE DATE Y 取消日期CANCELLED_BY NUMBER(15)Y 取消人CANCELLED_AMOUNT NUMBER Y 取消金PROJECT_ID NUMBER(15)Y 项目IDTASK_ID NUMBER(15)Y 任务IDGL_DATE DATE N 总帐日期ORG_ID NUMBER(15)Y 组织IDHR_OPERATING_UNITS.organization_id注意：ü在发票表中，不同组织之间的INVOICE_NUM是可以重复的，但INVOICE_ID是唯一的。

EBS常用数据表EBS常用数据表INV库存organization 两个含义:1. 经营单位，A/B/C分公司，A下面有A1，A2等工厂，主题目标是为了独立核算此组织ORG，ORG_ID;2. 库存组织，例如制造商的仓库，例如A1，A2等工厂Organization_id;HR_ORGANIZATION_UNITS －Org_organization_definitionsMtl_subinventory_ 库存组织单位MTL_PARAMETERS -库存组织参数（没有用ID，直接用name）MTL_SYSTEM_ITEMS_b -物料信息（同上，应用了库存组织name）MTL_SECONDARY_INVENTORIES -子库存组织 -MTL_ITEM_LOCATTIONS -货位 - SUBINVENTROY_CODEMtl_Material_Transactions - (库存)物料事物表成本 mtl_transaction_accountstransaction_cost是事物成本；ACTUAL_COST是通过成本算法计算出来的实际成本，主计量单位现有量汇总历史记录（正负合计）Mtl_Material_TransactionsMTL_ONHAND_QUANTITIES现有量表，组织/子库存/货位/物品 summary 可能按照挑库先进先出统计，如果设置了"不允许负库存"，这样就不可能出现负数PO请购单头表Po_Requisition_Headers_all行表Po_Requisition_lines_all采购订单PO_HEADER_ALLPO_LINES_ALL采购接收－退货/组织间转移/正常状态都需要使用这个模块RCV_TRANSACTIONS1. 接收100单位货物,放入“待质检”货位2. 接受/拒绝3. 库存/退回有三个不同的状态！例如：接收100个，80个接受入库，20个退回，那么有80个接受事务/20个退回事物select TRANSACTION_TYPE，DESTINATION_TYPE_CODE fromRCV_TRANSACTIONS可以看出以下阶段：A1.RECEIVE – RECEIVINGA2.ACCEPT – RECEIVINGA3.DELIERY – INVETORY(影响库存现有量)如果按照正常模式，最后会触发产生MTL_MATERIAL_TRANSACTIONS****订单OE_ORDER_headers_allSOLD_FROM_ORG_IDSOLD_TO_ORG_ID 就是客户层SHIP_FROM_ORG_IDSHIP_TO_ORG_ID 就是客户收货层INVOICE_TO_ORG_ID 就是客户收单层DELIVER_TO_ORG_ID和客户结构有关客户 RA_customers客户Address Ra_AddressesAddress 货品抵达 site RA_SITE_USES_ALLAddress 发票抵达 siteOE_ORDER_LINEs_allGL凭证gl_je_batches凭证日期: DEFAULT_EFFECTIVE_DATE会计期间: DEFAULT_PERIOD_NAME原币种凭证批借贷方汇总: RUNNING_TOTAL_DR/CR 比如美元本位币凭证批借贷方汇总: RUNNING_TOTAL_ACCOUNTED_DR/CRgl_je_headers日记账头信息批号: JE_BATCH_ID会计期间: PERIOD_NAME币种: CURRENCY_CODE汇率类型: CURRENCY_CONVERSION_TYPE汇率日期: CURRENCY_CONVERSION_DATE帐套: SET_OF_BOOKS_ID 参考 GL_SETS_OF_BOOKS凭证类型: JE_CATEGORY 参考 GL_JE_SOURCES凭证来源: JE_SOURCEgl_je_lines日记账体信息CODE_COMBINATION_ID 科目组合编号GL_BALANCES 总帐余额PERIOD_NET_DR/CR 净值BEGIN_BALANCE_DR/CR 期初额AR应收发票RA_CUSTOMER_TRX_ALLCUSTOMER_TRX_ID 发票编号BILL_TO_SITE_USE_ID 客户收单方编号PRIMARY_SALES_ID****员REFERENCE是Oracle提供的外部编号输入框，但是由于版本问题和长度(<=30)，不建议用户使用，如果要使用外部编号，请使用说明性弹性域RA_CUSTOMER_TRX_LINES_ALLLINE_ID 行号INVENTORY_ITEM_ID 可以为空，比如非物料的服务，只在DESCRIPTION中出现 /税行DESCRIPTIONQUANTITY_INVOICE 开票数量LINE_TYPE 行类型 (一般/税)EXTEND_PRICE 本行金额注意：税行是隐藏行，所以至少会有两行收款情况AR_CASH_RECEIPTS_ALL（还包含了非收款信息）CASH_RECEIPT_ID 内部codeRECEIPT_NUMBER 收款号RECEIPT_DATE 收款日期AMOUNT 总额RECEIPT_TYPE 现金/杂项 Cash/MiscFUNCTIONAL_AMOUNT 本位币计量金额UI上为RECEIPTS核销关系不是一一对应，也不是一次核销100%,UI上右下方的Application 按钮AR_RECEIVABLE_APPLICATIONS_ALLAPPLIED_CUSTOMER_TRX_ID 发票编号APPLIED_CUSTOMER_TRX_LINE_ID 发票行编号STATUS APP表示核销 /UNAPP表示未核销AMOUNT_APPLIED 匹配金额注意:红冲收款报表时间跨月的问题；必须联查AR_CASH_RECEIPTS_ALL和 AR_CASH_RECEIPT_HISTORY_ALLAP应付帐款(是我方人员按照供应商提供的纸张发票信息录入)UI 上的invoiceAP_INVOICES_ALL实际付款PAYMENTAP_CHECKS_ALL核销关系同AR，右下方的Payment 按钮AP_INVOICE_PAYMENTS_ALL客户余额表，情况比较复杂:比如两个用户合并，应收应付差额，预付款资产信息FA_ADDITIONS名称编号分类数量资产类别FA_CATEGORIES资产帐簿FA_BOOK_CONTROLS 和会计帐簿有什么关系？FA_BOOKSUI中的InquiryMothed是折旧方法(直线法/产量法)FA_DISTRIBUTION_HISTORY分配assignment，给什么部门使用多少LOCATION_ID 部门联查FA_LOCATIONS折旧信息(分摊方法)FA_DEPRN_DETAILperiod_counter 折旧期间编号折旧事务(新增、重建、转移、报废)FA_TRANSACTION_HEADERSselect fnd_profile.VALUE("ORG_ID") FROM DUALselect * from hr_operating_units hou whereanization_id=204--fndselect * from fnd_applicationselect * from fnd_application_tl where application_id=101 select * from fnd_application_vl where application_id = 101 ----值集select * from fnd_flex_value_setsselect * from fnd_flex_valuesselect * from fnd_flex_values_vl----弹性域select * from fnd_id_flexsselect * from fnd_id_flex_structures where id_flex_code="GL#" select * from fnd_id_flex_segments where id_flex_code="GL#" and id_flex_num=50671select * from fnd_profile_options_vlselect * from fnd_concurrent_programs 程序表select * from fnd_concurrent_requests 请求表select * from fnd_concurrent_processes 进程表--invselect * from org_organization_definitions 库存组织select * from mtl_parameters 组织参数select * from mtl_system_items_b where inventory_item_id = 171 and organization_id=204 物料表select * from mtl_secondary_inventories 子库存select * from mtl_item_locations 货位select * from mtl_lot_numbers 批次select * from mtl_onhand_quantities 现有量表select * from mtl_serial_numbers 序列select * from mtl_material_transactions 物料事务记录select * from mtl_transaction_accounts 会计分录select * from mtl_transaction_types 事务类型select * from mtl_txn_source_types 事务来源类型select * from mfg_lookups ml where ml.LOOKUP_TYPE ="MTL_TRANSACTION_ACTION"--poselect * from po_requisition_headers_all 请求头select * from po_requisition_lines_all 请求行select * from po_headers_all 订单头select * from po_lines_all 订单行select * from po_line_locations_allselect * from po_distributions_all 分配select * from po_releases_all 发送select * from rcv_shipment_headers 采购接收头select * from rcv_shipment_lines 采购接收行select * from rcv_transactions 接收事务处理select * from po_agentsselect * from po_vendors 订单select * from po_vendor_sites_all--oeselect * from ra_customers 客户select * from ra_addresses_all 地址select * from ra_site_uses_all 用户select * from oe_order_headers_all ****头select * from oe_order_lines_all ****行select * from wsh_new_deliveries 发送select * from wsh_delivery_detailsselect * from wsh_delivery_assignments--glselect * from gl_sets_of_books 总帐select * from gl_code_combinations gcc wheregcc.summary_flag="Y" 科目组合select * from gl_balances 科目余额select * from gl_je_batches 凭证批select * from gl_je_headers 凭证头select * from gl_je_lines 凭证行select * from gl_je_categories 凭证分类select * from gl_je_sources 凭证来源select * from gl_summary_templates 科目汇总模板select * from gl_account_hierarchies 科目汇总模板层次--arselect * from ar_batches_all 事务处理批select * from ra_customer_trx_all 发票头select * from ra_customer_trx_lines_all 发票行select * from ra_cust_trx_line_gl_dist_all 发票分配select * from ar_cash_receipts_all 收款select * from ar_receivable_applications_all 核销select * from ar_payment_schedules_all 发票调整select * from ar_adjustments_all 会计分录select * from ar_distributions_all 付款计划--apselect * from ap_invoices_all 发票头select * from ap_invoice_distributions_all 发票行select * from ap_payment_schedules_all 付款计划select * from ap_check_stocks_all 单据select * from ap_checks_all 付款select * from ap_bank_branches 银行select * from ap_bank_accounts_all 银行帐号select * from ap_invoice_payments_all 核销-----应收发票相干脚本------------应收发票主表SELECT *FROM RA_CUSTOMER_TRX_ALL AWHERE A.TRX_NUMBER = ""156640""AND _ID = 236;--应收发票行表SELECT B.EXTENDED_AMOUNT --不含税原币金额，B.EXTENDED_ACCTD_AMOUNT --不含税本币金额，B.GROSS_UNIT_SELLING_PRICE -- 含税单价，B.GROSS_EXTENDED_AMOUNT --含税金额，B.REVENUE_AMOUNT --收入金额，B.UNIT_SELLING_PRICE --不含税单价，B.UNIT_STANDARD_PRICE，B.*FROM RA_CUSTOMER_TRX_LINES_ALL BWHERE B.CUSTOMER_TRX_ID = 1449740;--发票分派表SELECT C.AMOUNT --不含税原原币金额，C.ACCTD_AMOUNT --不含税本币金额，C.*FROM RA_CUST_TRX_LINE_GL_DIST_ALL CWHERE C.CUSTOMER_TRX_ID = 1449519;--发票应收科目SELECT RCTLGDA.*FROM RA_CUSTOMER_TRX_ALL RCA，RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDAWHERE RCA.CUSTOMER_TRX_ID = RCTLGDA.CUSTOMER_TRX_ID AND RCTLGDA.ACCOUNT_CLASS = ""REC""AND RCA.CUSTOMER_TRX_ID = 1337786;--发票收入和税科目SELECT RCTLGDA.*FROM RA_CUSTOMER_TRX_ALL RCA，RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDAWHERE RCA.CUSTOMER_TRX_ID = RCTLGDA.CUSTOMER_TRX_IDAND RCTLGDA.ACCOUNT_CLASS <> ""REC""AND RCA.CUSTOMER_TRX_ID = 1337786;--或SELECT RCTLGDA.*FROM RA_CUSTOMER_TRX_ALL RCA，RA_CUSTOMER_TRX_LINES_ALL RCL，RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDAWHERE RCA.CUSTOMER_TRX_ID = RCL.CUSTOMER_TRX_IDAND RCL.CUSTOMER_TRX_LINE_ID =RCTLGDA.CUSTOMER_TRX_LINE_IDAND RCA.CUSTOMER_TRX_ID = 1337786;--发票收款规划表SELECT ARP.AMOUNT_DUE_ORIGINAL --发票原始金额（含税），ARP.TAX_ORIGINAL --发票原始税额，ARP.TAX_REMAINING --发票税余额，ARP.AMOUNT_APPLIED --发票收款金额，ARP.AMOUNT_LINE_ITEMS_ORIGINAL --发票行原始金额，ARP.AMOUNT_LINE_ITEMS_REMAINING --发票行余额，ARP.AMOUNT_DUE_REMAINING --到期余额，ARP.ACCTD_AMOUNT_DUE_REMAINING --本币到期余额，ARP.AMOUNT_ADJUSTED --发票调剂金额，ARP.AMOUNT_CREDITED --发票做过贷项通知单金额，ARP.FREIGHT_ORIGINAL，ARP.FREIGHT_REMAINING，ARP.DISCOUNT_ORIGINAL，ARP.DISCOUNT_REMAINING，ARP.DISCOUNT_TAKEN_EARNED，ARP.DISCOUNT_TAKEN_UNEARNED，ARP.RECEIVABLES_CHARGES_CHARGED，ARP.RECEIVABLES_CHARGES_REMAINING，ARP.*FROM AR_PAYMENT_SCHEDULES_ALL ARPWHERE ARP.CUSTOMER_TRX_ID = 1485432 --Num: 80210055--发票是否做过贷项SELECT *FROM RA_CUSTOMER_TRX_ALL RCAWHERE RCA.PREVIOUS_CUSTOMER_TRX_ID = 1337786;--ra_customer_trx_all.customer_trx_id--发票现金收款金额SELECT *FROM AR_RECEIVABLE_APPLICATIONS_ALL ARAWHERE ARA.APPLIED_CUSTOMER_TRX_ID = 1337785AND ARA.APPLICATION_TYPE = ""CASH""AND ARA.STATUS = ""APP"";--发票被贷项通知单核销金额SELECT *FROM AR_RECEIVABLE_APPLICATIONS_ALL ARAWHERE ARA.APPLIED_CUSTOMER_TRX_ID = 1337786AND ARA.APPLICATION_TYPE = ""CM""AND ARA.STATUS = ""APP"";--发票调剂金额SELECT ADJ.*FROM AR_ADJUSTMENTS_ALL ADJ，RA_CUSTOMER_TRX_ALL RCTWHERE RCT.CUSTOMER_TRX_ID = ADJ.CUSTOMER_TRX_IDAND ADJ.STATUS = ""A""AND RCT.CUSTOMER_TRX_ID = 1337785;--收款录入后产生的信息如下:--收款主表SELECT *FROM AR_CASH_RECEIPTS_ALL ACRAWHERE ACRA.RECEIPT_NUMBER = ""20120106001"";--收款汗青记录表SELECT *FROM AR_CASH_RECEIPT_HISTORY_ALL ACRHWHERE ACRH.CASH_RECEIPT_ID = 304387;--收款事务处理惩罚表SELECT *FROM AR_RECEIVABLE_APPLICATIONS_ALL ARAWHERE ARA.CASH_RECEIPT_ID = 304387;--收款分派表--收款录入时产生两笔记录，SOURCE_ID分别对应--SOURCE_TABLE为 ""RA"" 时SOURCE_ID对应的是AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID，---为CRH时SOURCE_ID对应的是AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID--下面SQL获取的是收款录入时产生的未核销科目及收款核销时产生的应收账款和未核销科目SELECT *FROM AR_DISTRIBUTIONS_ALL ADAWHERE ADA.SOURCE_ID IN（SELECT ARA.RECEIVABLE_APPLICATION_IDFROM AR_RECEIVABLE_APPLICATIONS_ALL ARAWHERE ARA.CASH_RECEIPT_ID = 304387）AND ADA.SOURCE_TABLE = ""RA"";--收款录入时产生两条分录：现金和未核销，下面语句获取的是现金科目SELECT *FROM AR_DISTRIBUTIONS_ALL ADAWHERE ADA.SOURCE_ID IN（SELECT ACRH.CASH_RECEIPT_HISTORY_IDFROM AR_CASH_RECEIPT_HISTORY_ALL ACRHWHERE ACRH.CASH_RECEIPT_ID = 304387）AND ADA.SOURCE_TABLE = ""CRH"";--应收发票收货方收单方到期日等SELECT CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID，CT.TRX_NUMBER TRX_NUMBER，CT.OLD_TRX_NUMBER OLD_TRX_NUMBER，CT_REL.TRX_NUMBER CT_RELATED_TRX_NUMBER，CT.RECURRED_FROM_TRX_NUMBER CT_MODEL_TRX_NUMBER，CT.TRX_DATE TRX_DATE，ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE（CT.CUSTOMER_TRX_ID，CT.TERM_ID，CT.TRX_DATE） TERM_DUE_DATE --到期日，CT.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_ID，CT.INITIAL_CUSTOMER_TRX_ID INITIAL_CUSTOMER_TRX_ID，CT.RELATED_BATCH_SOURCE_ID RELATED_BATCH_SOURCE_ID，CT.RELATED_CUSTOMER_TRX_ID RELATED_CUSTOMER_TRX_ID，CT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID，CT.BATCH_ID BATCH_ID，CT.BATCH_SOURCE_ID BATCH_SOURCE_ID，CT.REASON_CODE REASON_CODE，CT.TERM_ID TERM_ID，CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID，CT.AGREEMENT_ID AGREEMENT_ID，CT.CREDIT_METHOD_FOR_RULES CREDIT_METHOD_FOR_RULES，CT.CREDIT_METHOD_FOR_INSTALLMENTSCREDIT_METHOD_FOR_INSTALLMENTS，CT.RECEIPT_METHOD_ID RECEIPT_METHOD_ID，CT.INVOICING_RULE_ID INVOICING_RULE_ID，CT.SHIP_VIA SHIP_VIA，CT.FOB_POINT FOB_POINT，CT.FINANCE_CHARGES，PLETE_FLAG COMPLETE_FLAG，CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID，CT.RECURRED_FROM_TRX_NUMBER RECURRED_FROM_TRX_NUMBER，CT.STATUS_TRX STATUS_TRX，CT.DEFAULT_TAX_EXEMPT_FLAG DEFAULT_TAX_EXEMPT_FLAG，CT.SOLD_TO_CUSTOMER_ID SOLD_TO_CUSTOMER_ID，CT.SOLD_TO_SITE_USE_ID SOLD_TO_SITE_USE_ID，CT.SOLD_TO_CONTACT_ID SOLD_TO_CONTACT_ID，CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID，CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID，RAA_BILL.CUST_ACCT_SITE_ID RAA_BILL_TO_ADDRESS_ID，CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID，RAC_BILL_PARTY.JGZZ_FISCAL_CODE BILL_TO_TAXPAYER_ID，CT.SHIP_TO_CUSTOMER_ID，CT.SHIP_TO_SITE_USE_ID，RAA_SHIP.CUST_ACCT_SITE_ID RAA_SHIP_TO_ADDRESS_ID ，CT.SHIP_TO_CONTACT_ID，RAC_SHIP_PARTY.JGZZ_FISCAL_CODE SHIP_TO_TAXPAYER_ID ，CT.REMIT_TO_ADDRESS_ID，CT.INVOICE_CURRENCY_CODE，CT.CREATED_FROM，CT.SET_OF_BOOKS_ID，CT.PRINTING_ORIGINAL_DATE，CT.PRINTING_LAST_PRINTED，CT.PRINTING_OPTION，CT.PRINTING_COUNT，CT.PRINTING_PENDING，ST_PRINTED_SEQUENCE_NUM，CT.PURCHASE_ORDER，CT.PURCHASE_ORDER_REVISION，CT.PURCHASE_ORDER_DATE，CT.CUSTOMER_REFERENCE，CT.CUSTOMER_REFERENCE_DATE，MENTS，CT.INTERNAL_NOTES，CT.EXCHANGE_RATE_TYPE，CT.EXCHANGE_DATE，CT.EXCHANGE_RATE，CT.TERRITORY_ID，CT.END_DATE_COMMITMENT，CT.START_DATE_COMMITMENT，CT.ORIG_SYSTEM_BATCH_NAME，CT.SHIP_DATE_ACTUAL，CT.WAYBILL_NUMBER，CT.DOC_SEQUENCE_ID，CT.DOC_SEQUENCE_VALUE，CT.PAYING_CUSTOMER_ID，CT.PAYING_SITE_USE_ID，CT.DEFAULT_USSGL_TRANSACTION_CODE，ST_UPDATE_DATE，ST_UPDATED_BY，CT.CREATION_DATE，CT.CREATED_BY，ST_UPDATE_LOGIN，CT.REQUEST_ID，RAC_BILL_PARTY.PARTY_NAME RAC_BILL_TO_CUSTOMER_NAME --收单方，RAC_BILL.ACCOUNT_NUMBER RAC_BILL_TO_CUSTOMER_NUM，SU_BILL.LOCATION SU_BILL_TO_LOCATION --收单地址，RAA_BILL_LOC.ADDRESS1 RAA_BILL_TO_ADDRESS1 --收单地址，RAA_BILL_LOC.ADDRESS2 RAA_BILL_TO_ADDRESS2，RAA_BILL_LOC.ADDRESS3 RAA_BILL_TO_ADDRESS3_DB，DECODE（RAA_BILL.CUST_ACCT_SITE_ID，NULL，NULL，ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE（RAA_BILL_LOC.ADDRESS_STYLE，RAA_BILL_LOC.ADDRESS3，RAA_BILL_LOC.ADDRESS4，RAA_BILL_LOC.CITY，，RAA_BILL_LOC.STATE，RAA_BILL_LOC.PROVINCE，FT_BILL.TERRITORY_SHORT_NAME，RAA_BILL_LOC.POSTAL_CODE）） RAA_BILL_TO_ADDRESS3 ，RAA_BILL_LOC.CITY RAA_BILL_TO_CITY，RAA_BILL_LOC.COUNTY RAA_BILL_TO_COUNTY，RAA_BILL_LOC.STATE RAA_BILL_TO_STATE，RAA_BILL_LOC.PROVINCE RAA_BILL_TO_PROVINCE，RAA_BILL_LOC.POSTAL_CODE RAA_BILL_TO_POSTAL_CODE，FT_BILL.TERRITORY_SHORT_NAME FT_BILL_TO_COUNTRY，DECODE（RAA_BILL.CUST_ACCT_SITE_ID，NULL，NULL，ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS（RAA_BILL_LOC.ADDRESS_STYLE，RAA_BILL_LOC.ADDRESS1，RAA_BILL_LOC.ADDRESS2，RAA_BILL_LOC.ADDRESS3，RAA_BILL_LOC.ADDRESS4，RAA_BILL_LOC.CITY，，RAA_BILL_LOC.STATE，RAA_BILL_LOC.PROVINCE，RAA_BILL_LOC.POSTAL_CODE，FT_BILL.TERRITORY_SHORT_NAME）） RAA_BILL_TO_CONCAT_ADDRESS，DECODE（SUBSTRB（RACO_BILL_PARTY.PERSON_LAST_NAME，1，50），NULL，SUBSTRB（RACO_BILL_PARTY.PERSON_FIRST_NAME，1，40），SUBSTRB（RACO_BILL_PARTY.PERSON_LAST_NAME，1，50） || ""， "" || SUBSTRB（RACO_BILL_PARTY.PERSON_FIRST_NAME，1，40））RACO_BILL_TO_CONTACT_NAME，RAC_SHIP_PARTY.PARTY_NAME RAC_SHIP_TO_CUSTOMER_NAME --收货方，RAC_SHIP.ACCOUNT_NUMBER RAC_SHIP_TO_CUSTOMER_NUM，SU_SHIP.LOCATION SU_SHIP_TO_LOCATION --收货地址，RAA_SHIP_LOC.ADDRESS1 RAA_SHIP_TO_ADDRESS1 --收货地址，RAA_SHIP_LOC.ADDRESS2 RAA_SHIP_TO_ADDRESS2，RAA_SHIP_LOC.ADDRESS3 RAA_SHIP_TO_ADDRESS3_DB，DECODE（RAA_SHIP.CUST_ACCT_SITE_ID，NULL，NULL，ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE （RAA_SHIP_LOC.ADDRESS_STYLE，RAA_SHIP_LOC.ADDRESS3，RAA_SHIP_LOC.ADDRESS4，RAA_SHIP_LOC.CITY，RAA_SHIP_LOC.COUNTY，RAA_SHIP_LOC.STATE，RAA_SHIP_LOC.PROVINCE，FT_SHIP.TERRITORY_SHORT_NAME，RAA_SHIP_LOC.POSTAL_CODE）） RAA_SHIP_TO_ADDRESS3，RAA_SHIP_LOC.CITY RAA_SHIP_TO_CITY，RAA_SHIP_LOC.COUNTY RAA_SHIP_TO_COUNTY，RAA_SHIP_LOC.STATE RAA_SHIP_TO_STATE，RAA_SHIP_LOC.PROVINCE RAA_SHIP_TO_PROVINCE，RAA_SHIP_LOC.POSTAL_CODE RAA_SHIP_TO_POSTAL_CODE，FT_SHIP.TERRITORY_SHORT_NAME FT_SHIP_TO_COUNTRY，DECODE（RAA_SHIP.CUST_ACCT_SITE_ID，NULL，NULL，ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS （RAA_SHIP_LOC.ADDRESS_STYLE，RAA_SHIP_LOC.ADDRESS1，RAA_SHIP_LOC.ADDRESS2，RAA_SHIP_LOC.ADDRESS3，RAA_SHIP_LOC.ADDRESS4，RAA_SHIP_LOC.CITY，RAA_SHIP_LOC.COUNTY。

一、概述Oracle E-Business Suite（EBS）是一种集成的应用套件，可帮助企业管理他们的业务流程。

在EBS中，表名是特别重要的标识，它们用于存储各种业务数据。

在本文中，我们将介绍一些常见的Oracle EBS标准表名，这些表名对于理解和使用EBS系统非常重要。

二、常见表名1. FND表FND是Oracle EBS中的一个重要模块，包含了许多用于存储系统配置和元数据的表。

常见的FND表名包括FND_USER、FND_APPLICATION、FND_RESPONSIBILITY等，这些表存储了用户、应用程序、责任等信息，对于管理和维护EBS系统非常重要。

2. AR表AR模块是EBS中用于管理应收账款的模块，其中包含了许多与客户、发票、付款等相关的表。

常见的AR表名包括AR_CUSTOMERS、AR_INVOICES_ALL、AR_PAYMENT_SCHEDULES_ALL等，这些表存储了客户、发票、付款等信息，对于财务管理非常重要。

3. AP表AP模块是EBS中用于管理应付账款的模块，其中包含了许多与供应商、发票、付款等相关的表。

常见的AP表名包括AP_SUPPLIERS、AP_INVOICES_ALL、AP_PAYMENT_SCHEDULES_ALL等，这些表存储了供应商、发票、付款等信息，对于采购管理非常重要。

4. GL表GL模块是EBS中用于管理总账的模块，其中包含了许多与会计期间、账户、凭证等相关的表。

常见的GL表名包括GL_PERIODS、GL_ACCOUNTS、GL_JE_BATCHES等，这些表存储了会计期间、账户、凭证等信息，对于财务报告非常重要。

5. HR表HR模块是EBS中用于管理人力资源的模块，其中包含了许多与员工、岗位、薪酬等相关的表。

常见的HR表名包括HR_EMPLOYEES、HR_POSITIONS、HR_PAYROLL等，这些表存储了员工、岗位、薪酬等信息，对于人力资源管理非常重要。

BOM模块常用表结构表名: bom.bom_bill_of_materials说明: BOM清单父项目BILL_SEQUENCE_ID NUMBER 清单序号（关键字）ASSEMBLY_ITEM_ID NUMBER 装配件内码ORGANIZATION_ID NUMBER 组织代码ASSEMBLY_TYPE NUMBER 装配类别SPECFIIC_ASSEMBLY_COMMENT VARCHAR2(240) 注释（装配件状态P、R等）COMMON_ORGANIZATION_ID NUMBER 公共组织COMMON_BILL_SEQUENCE_ID NUMBER 公共序号COMMON_ASSEMBLY_ITEM_ID NUMBER 公共项目内码表名：bom.bom_inventory_components说明：BOM清单构成项目COMPONENT_SEQUENCE_ID NUMBER 构件序号BILL_SEQUENCE_ID NUMBER 清单序号OPERATION_SEQ_NUM NUMBER 操作序列号COMPONENT_ITEM_ID NUMBERITEM_NUM NUMBER 项目序列号COMPONENT_QUANTITY NUMBER 构件数量COMPONENT_YIELD_FACTOR NUMBER 产出因子EFFECTIVITY_DATE DATE 生效日期DISABLE_DATE DATE 失效日期CHANGE_NOTICE VARCHAR2(10) 更改注释(ECO更改号)PLANNING_FACTOR NUMBER 计划百分比QUANTITY_RELATED NUMBER 相关数量SO_BASIS NUMBEROPTIONAL NUMBER 是否可选(1.是;2.否)MUTUALLY_EXCLUSIVE_OPTIONS NUMBER 互斥标识(1.是;2.否)WIP_SUPPLY_TYPE NUMBER 车间供应类型(1.推式2.装配拉式3.操作拉式4.大量5.供应商6.虚拟)supply_subinventory 供应子库存supply_locator_id 供应货位IDBOM_ITEM_TYPE NUMBER 清单项目类型(1.模型2.选项类3.计划中4.标准) INCLUDE_ON_SHIP_DOCS NUMBER 包含在发运文档中(1.是;2.否) REQUIRED_FOR_REVENUE NUMBER 需要进款(1.是;2.否)REQUIRED_TO_SHIP NUMBER 需要发运(1.是;2.否)SHIPPING ALLOWED NUMBER 搬运允许(1.是;2.否)INCLUDE_IN_COST_ROLLUP NUMBER 计入成本累加中(1.是;2.否)CHECK_ATP NUMBER ATP检查(1.是;2.否)表名：bom.bom_assembly_comments说明：装配件状态表BILL_SEQUENCE_ID NUMBER 清单序号STANDARD_REMARKS_DESIGNATOR VARCHAR2(10) 状态标志(R,P,D_R等)表名: bom.bom_standard_comments说明: 标准注释表(状态说明表)COMMENT_CODE VARCHAR2(10) 注释代码(状态代码) ORGANIZATION_ID NUMBER 组织代码COMMENT_TEXT VARCHAR2(240) 注释说明(状态说明)表名: bom.bom_calendars说明: 日历种类表CALENDAR_CODE VARCHAR2(10) 日历代码QUARTERLY_CALENDAR_TYPE NUMBER 季度日历类型CALENDAR_START_DATE DATE 日历启用日期CALENDAR_END_DATE DATE 日历截止日期DESCRIPTION VARCHAR2(240) 描述表名: bom.bom_calendar_dates说明: 工作日历表CALENDAR_CODE VARCAHR2(10) 日历代码PRIOR_DATE DATE 前一天CALENDAR_DATE DATE 当日(当与前,后日期均相同时为工作日) NEXT_DATE DATE 后一天PRIOR_SEQ_NUM NUMBER 前一天序列号SEQ_NUM NUMBER 当日序序列号(非工作日为空)NEXT_SEQ_NUM NUMBER 后一天序列号EXCEPTION_SET_ID NUMBER 例外集代码表名: bom.bom_calendar_exceptions说明: 日历例外信息CALENDAR_CODE VARCHAR2(10) 日历代码EXCEPTION_SET_ID NUMBER 例外集代码EXCEPTION_DATE DATE 例外日期EXCEPTION_TYPE NUBMER 例外类别表名: bom.bom_calendar_shifts说明: 倒班表CALENDAR_CODE VARCAHR2(10) 日历代码SHIFT_NUM NUMBER 班次DESCRIPTION VARCHAR2(240) 描述表名: bom.bom_shift_dates说明: 轮班表CALENDAR_CODE VARCHAR2(10) 日历代码EXCEPTION_SET_ID NUMBER 例外集代码SHIFT_NUM NUMBER 倒班序号SHIFT_DATE DATE 倒班日期SEQ_NUM NUMBER 倒班序号NEXT_SEQ_NUM NUMBER 下一班序号PRIOR_SEQ_NUM NUMBER 上一班序号NEXT_DATE DATE 下一次倒班日期PRIOR_DATE DATE 上一次倒班日期表名: bom.bom_cal_week_start_dates说明: 星期开始日期CALENDAR_CODE VARCHAR2(10) 日历代码EXCEPTION_SET_ID NUMBER 例外集代码SEQ_NUM NUMBER 序号WEEK_START_DATE DATE 周开始日期PRIOR_DATE DATE 上周开始日期NEXT_DATE DATE 下周开始日期表名: bom.bom_period_start_dates说明: 周期开始日期CALENDAR_CODE VARCHAR2(10) 日历代码EXCEPTION_SET_ID NUMBER 例外集代码PERIOD_START_DATE DATE 周期开始日期PERIOD_SEQUENCE_NUM NUMBER 周期序号PERIOD_NAME VARCHAR2(3) 周期名称(月的三位英文缩写) NEXT_DATE DATE 下一周期开始日期PRIOR_DATE DATE 上一周期开始日期表名: bom.bom_workday_patterns说明: 工作日模式表CALENDAR_CODE VARCHAR2(10) 日历代码SHIFT_NUM NUMBER 班次SEQ_NUM NUMBER 模式序号DAYS_ON NUMBER 工作天数DAYS_OFF NUMBER 非工作天数DESCRIPTION VARCHAR2(240) 描述(大周/小周)表名: bom.bom_exception_sets说明: BOM例外集表EXCEPTION_SET_ID NUMBER 例外集代码EXCEPTION_SET_NAME VARCHAR2(10) 例外集名称DESCRIPTION VARCHAR2(240) 描述表内容:1 主要主要例外集2 HW_WDCES 华为日历异常集3 MBC_WDCES 莫贝克日历异常集-1 虚拟 10SC 兼容的空数据表名：bom.cst_item_costs说明：项目成本表ORGANIZATION_ID NUMBER 组织代码INVENTORY_ITEM_ID NUMBER 项目内码COST_TYPE_ID NUMBER 成本类型ITEM_COST NUMBER 项目成本MATERIAL_COST NUMBER 物料成本RESOURCE_COST NUMBER 资源成本UNBURDENDED_COST NUMBER 不分摊成本BURDEN_COST NUMBER 分摊成本INVENTORY_ASSET_FLAG NUMBER 资产项目LOT_SIZE NUMBER 批量大小BASED_ON_ROLLUP_FLAG NUMBERSHRINKAGE_RATE NUMBER 收缩比例DEFAULTED_FLAG NUMBER表名: bom.cst_cost_types说明: 成本类型表COST_TYPE_ID NUMBER 成本类型代码COST_TYPE VARCHAR2(10) 成本类型DESCRIPTION VARCHAR2(240) 说明COSTING_METHOD_TYPE NUMBER 成本合计方法代码(1.2.) DISABLE_DATE DATE 失效日期本表内容如下:COST_TYPE COST_TYPE_ID DESCRIPTION冻结 1 冻结标准成本类型平均 2 平均成本类型等待 3 等待标准成本类型标准成本 1000 标准成本实际成本 1001 实际成本新标准成本 1002 新标准成本表名: bom.cst_item_cost_details说明: 项目成本明细表ORGANIZATION_ID NUMBER 组织代码INVENTORY_ITEM_ID NUMBER 项目内码ITEM_COST NUMBER 项目成本COST_TYPE_ID NUMBER 成本类型DEPARTMENT_ID NUMBER 部门代码COST_ELEMENT_ID NUMBER 成本要素类型代码LAST_UPDATE_DATE NUMBER 最后更新日期OPERATION_SEQUENCE_ID NUMBER 操作序列代码OPERATION_SEQ_NUM NUMBER 操作序列号LEVEL_TYPE NUMBER 级别类型RESOURCE_SEQ_NUM NUMBER 资源序列号RESOURCE_ID NUMBER 资源代码RESOURCE_RATE NUMBER 资源比率USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额BASIS_TYPE NUMBER 基础类型BASIS_RESOURCE_ID NUMBER 基础资源代码BASIS_FACTOR NUMBER 基础因子NET_YIELD_OR_SHRINKAGE_FACTOR NUMBER 净产出或收缩比ROLLUP_SOURCE_TYPE NUMBER表名: bom.cst_cost_elements说明: 成本要素代码表COST_ELEMENT_ID NUMBER 成本要素代码COST_ELEMENT VARCHAR2(50) 成本要素说明内容:1 物料的2 物料制造费3 资源4 外部处理5 制造费表名: bom.cst_elemental_costs说明: 项目要素成本COST_UPDATE_ID NUMBER 成本更新代码ORGANIZATION_ID NUMBER 组织代码INVENTORY_ITEM_ID NUMBER 项目内码COST_ELEMENT_ID NUMBER 要素代码LAST_UPDATE_DATE DATE 最后更新日期STANDARD_COST NUMBER 标准成本表名: bom.cst_resource_costs说明: 资源成本RESOURCE_ID NUMBER 资源代码COST_TYPE_ID NUMBER 成本类型ORGANIZATION_ID NUMBER 组织代码RESOURCE_RATE NUMBER 资源费率表名: bom.cst_standard_costs说明: 项目标准成本表COST_UPDATE_ID NUMBER 成本更新代码INVENTORY_ITEM_ID NUMBER 项目内码ORGANIZATION_ID NUMBER 组织代码STANDARD_COST_REVISION_DATE DATE 标准成本版本日期STANDARD_COST NUMBER 标准成本INVENTORY_ADJUSTMENT_QUANTITY NUMBER 库存调节数量INVENTORY_ADJUSTMENT_VALUE NUMBER 库存调节价值INTRANSIT_ADJUSTMENT_QUANTITY NUMBER 在途调节数量INTRANSIT_ADJUSTMENT_VALUE NUMBER 在途调节价值WIP_ADJUSTMENT_QUANTITY NUMBER 在制调节数量WIP_ADJUSTMENT_VALUE NUMBER 在制调节价值表名: bom.cst_std_cost_adj_values说明: 标准成本调节表COST_UPDATE_ID NUMBER 成本更新代码ORGANIZATION_ID NUMBER 组织代码TRANSACTION_TYPE NUMBER 交易类型ADJUSTMENT_QUANTITY NUMBER 调节数量OLD_UNIT_COST NUMBER 老的单位成本NEW_UNIT_COST NUMBER 新的单位成本COST_ELEMENT_ID NUMBER 成本元素代码LEVEL_TYPE NUMBER 级别类型IN_OUT_FLAG NUMBER 出入标志RESOURCE_ID NUMBER 资源代码INVENTORY_ITEM_ID NUMBER 项目内码SUBINVENTORY VARCHAR2(10) 子库WIP_ENTITY_ID NUMBER 离散作业代码OPERATION_SEQ_NUM NUMBER 工艺路线操作序号DEPARTMENT_ID NUMBER 部门代码RESOURCE_SEQ_ID NUMBER 资源序号代码RESOURCE_SEQ_NUM NUMBER 资源序号STANDARD_RATE_FLAG NUMBER 标准率TRANSACTION_ID NUMBER 交易代码BASIS_TYPE NUMBER 基本类型FROM_ORGANIZATION_ID NUMBER 从组织代码TO_ORGANIZATION_ID NUMBER 到组织代码表名: bom.bom_departments说明: 部门代码对应表DEPARTMENT_ID NUMBER 部门内码DEPARTMENT_CODE VARCHAR2(10) 部门代码ORGANIZATION_ID NUMBER 组织代码DESCRIPTION VARCHAR2(240) 说明(汉字说明的部门名称)DISABLE_DATE DATE 失效日期LOCATION_ID NUMBER 地点代码表名: bom.bom_resources说明: 资源代码对应表RESOURCE_ID NUMBER 资源内码RESOURCE_CODE VARCHAR2(10) 资源代码ORGANIZATION_ID NUMBER 组织代码DESCRIPTION VARCHAR2(240) 说明DISABLE_DATE DATE 失效日期COST_ELEMENT_ID NUMBER 成本要素代码PURCHASE_ITEM_ID NUMBER 购买项目代码COST_CODE_TYPE NUMBERFUNCTIONAL_CURRENCY_FLAG NUMBER 货币单位(1.是,2.否)UNIT_OF_MEASURE VARCHAR2(3) 单位DEFAULT_ACTIVITY_ID NUMBER 活动缺省值(1.运行,4.移动,5.排队) RESOURCE_TYPE NUMBER 资源类型(1.机器,2.人员,3.空间,4.杂项,5.数额) AUTOCHARGE_TYPE NUMBER 自动记帐类型(1.WIP移动,2.人工,3.PO接收,4.PO移动)STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)DEFAULT_BASIS_TYPE NUMBER 基本类型缺省值(1.项目,2.批量) ABSORPTION_ACCOUNT NUMBER 分摊帐目ALLOW_COSTS_FLAG NUMBER 计算成本标志(1.否,2.是)RATE_VARIANCE_ACCOUNT NUMBER 差异帐目EXPENDITURE_TYPE VARCHAR2(30) 费用类型表名: bom.bom_operational_routings说明: 工艺路线ROUTING_SEQUENCE_ID NUMBER 路线序号ASSEMBLY_ITEM_ID NUMBER 项目内码ORGANIZATION_ID NUMBER 组织代码ALTERNATE_ROUTING_DESIGNATOR VARCHAR2(10) 替换工艺路线ROUTING_TYPE NUMBER 路线类别COMMON_ASSEMBLY_ITEM_ID NUMBER 公共装配件内码COMMON_ROUTING_SEQUENCE_ID NUMBER 公共路线序号ROUTING_COMMENT VARCHAR2(240) 路线注释COMPLETION_SUBINVENTORY VARCHAR2(10) 完工入库名称COMPLETION_LOCATOR_ID NUMBER 完工入库货位表名：bom.bom_operation_resources说明：工艺路线细节之资源表OPERATION_SEQUENCE_ID NUMBER 操作序列内码RESOURCE_SEQ_NUM NUMBER 资源序号RESOURCE_ID NUMBER 资源内码ACTIVITY_ID NUMBER 活动(1.运行,4.移动,5.排队)STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)ASSIGNED_UNITS NUMBER 已分配单位USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额USAGE_RATE_OR_AMOUNT_INVERSE NUMBER 利用率或金额倒数BASIS_TYPE NUMBER 基本类型SCHEDULE_FLAG NUMBER 计划标识RESOURCE_OFFSET_PERCENT NUMBER 偏差百分比AUTOCHARGE_TYPE NUMBER 自动记帐(1.WIP移动,2.人工,3.PO接收,4.PO移动) 表名：bom.bom_operation_sequences说明：工艺路线细节之工艺工序表OPERATION_SEQUENCE_ID NUMBER 操作序号内码ROUTING_SEQUENCE_ID NUMBER 路线序号内码OPERATION_SEQ_NUM NUMBER 路线序列代码STANDARD_OPERATION_ID NUMBER 标准操作序号内码DEPARTMENT_ID NUMBER 部门内码OPERATION_LEAD_TIME_PERCENT NUMBER 提前期百分比MINIMUM_TRANSFER_QUANTITY NUMBER 最小传送批量COUNT_POINT_TYPE NUMBER 计数点(1.是--自动收费,2.否)OPERATION_DESCRIPTION VARCHAR2(240) 操作说明EFFECTIVITY_DATE DATE 生效日期DISABLE_DATE DATE 失效日期BACKFLUSH_FLAG NUMBER 倒冲库存标识(1.是,2.否)OPTION_DEPENDENT_FLAG NUMBER 可选工序(1.是,2.否)表名: bom.bom_standard_operations说明: 标准操作表STANDARD_OPERATION_ID NUMBER 标准操作标识码OPERATION_CODE VARCHAR2(4) 操作代码ORGANIZATION_ID NUMBER 组织代码DEPARTMENT_ID NUMBER 部门代码MINIMUM_TRANSFER_QUANTITY NUMBER 最小传送数量COUNT_POINT_TYPE NUMBER 计数点(1.是--自动收费,2.否)OPERATION_DESCRIPTION VARCHAR2(240) 操作说明OPTION_DEPENDENT_FLAG NUMBER 可选工序(1.是,2.否)表名: bom.bom_std_op_resources说明: 标准操作资源表STANDARD_OPERATION_ID NUMBER 标准操作标识码RESOURCE_ID NUMBER 资源标识码ACTIVITY_ID NUMBER 活动(1.运行,4.移动,5.排队)RESOURCE_SEQ_NUM NUMBER 资源序号USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额USAGE_RATE_OR_AMOUNT_INVERSE NUMBER 利用率或金额倒数BASIS_TYPE NUMBER 基本类型(1.项目,2.批量)AUTOCHARGE_TYPE NUMBER 自动记帐(1.WIP移动,2.人工,3.PO接收,4.PO移动) STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)ASSIGNED_UNITS NUMBER 已分配单位表名: bom.bom_substitute_components说明: BOM替代件表SUBSTITUTE_COMPONENT_ID NUMBER 替代构成件代码SUBSTITUTE_ITEM_QUANTITY NUMBER 替代项目数量COMPONENT_SEQUENCE_ID NUMBER 组件序号ACD_TYPE NUMBERCHANGE_NOTICE VARCHAR2(10) 更改单表名: bom.cst_std_cost_adj_values说明: 标准成本调节表COST_UPDATE_ID NUMBER 成本更新代码ORGANIZATION_ID NUMBER 组织代码TRANSACTION_TYPE NUMBER 交易类型ADJUSTMENT_QUANTITY NUMBER 调节数量OLD_UNIT_COST NUMBER 老的单位成本NEW_UNIT_COST NUMBER 新的单位成本COST_ELEMENT_ID NUMBER 成本元素代码LEVEL_TYPE NUMBER 级别类型IN_OUT_FLAG NUMBER 出入标志RESOURCE_ID NUMBER 资源代码INVENTORY_ITEM_ID NUMBER 项目内码SUBINVENTORY VARCHAR2(10) 子库OPERATION_SEQ_NUM NUMBER 工艺路线操作序号DEPARTMENT_ID NUMBER 部门代码RESOURCE_SEQ_ID NUMBER 资源序号代码RESOURCE_SEQ_NUM NUMBER 资源序号STANDARD_RATE_FLAG NUMBER 标准率TRANSACTION_ID NUMBER 交易代码BASIS_TYPE NUMBER 基本类型FROM_ORGANIZATION_ID NUMBER 从组织代码TO_ORGANIZATION_ID NUMBER 到组织代码表名: bom.bom_departments说明: 部门代码对应表DEPARTMENT_ID NUMBER 部门内码DEPARTMENT_CODE VARCHAR2(10) 部门代码ORGANIZATION_ID NUMBER 组织代码DESCRIPTION VARCHAR2(240) 说明(汉字说明的部门名称)DISABLE_DATE DATE 失效日期LOCATION_ID NUMBER 地点代码表名: bom.bom_resources说明: 资源代码对应表RESOURCE_ID NUMBER 资源内码RESOURCE_CODE VARCHAR2(10) 资源代码ORGANIZATION_ID NUMBER 组织代码DESCRIPTION VARCHAR2(240) 说明DISABLE_DATE DATE 失效日期COST_ELEMENT_ID NUMBER 成本要素代码PURCHASE_ITEM_ID NUMBER 购买项目代码COST_CODE_TYPE NUMBERFUNCTIONAL_CURRENCY_FLAG NUMBER 货币单位(1.是,2.否)UNIT_OF_MEASURE VARCHAR2(3) 单位DEFAULT_ACTIVITY_ID NUMBER 活动缺省值(1.运行,4.移动,5.排队) RESOURCE_TYPE NUMBER 资源类型(1.机器,2.人员,3.空间,4.杂项,5.数额) AUTOCHARGE_TYPE NUMBER 自动记帐类型(1.WIP移动,2.人工,3.PO接收,4.PO移动)STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)DEFAULT_BASIS_TYPE NUMBER 基本类型缺省值(1.项目,2.批量) ABSORPTION_ACCOUNT NUMBER 分摊帐目ALLOW_COSTS_FLAG NUMBER 计算成本标志(1.否,2.是)RATE_VARIANCE_ACCOUNT NUMBER 差异帐目EXPENDITURE_TYPE VARCHAR2(30) 费用类型表名: bom.bom_operational_routings说明: 工艺路线ROUTING_SEQUENCE_ID NUMBER 路线序号ASSEMBLY_ITEM_ID NUMBER 项目内码ALTERNATE_ROUTING_DESIGNATOR VARCHAR2(10) 替换工艺路线ROUTING_TYPE NUMBER 路线类别COMMON_ASSEMBLY_ITEM_ID NUMBER 公共装配件内码COMMON_ROUTING_SEQUENCE_ID NUMBER 公共路线序号ROUTING_COMMENT VARCHAR2(240) 路线注释COMPLETION_SUBINVENTORY VARCHAR2(10) 完工入库名称COMPLETION_LOCATOR_ID NUMBER 完工入库货位表名：bom.bom_operation_resources说明：工艺路线细节之资源表OPERATION_SEQUENCE_ID NUMBER 操作序列内码RESOURCE_SEQ_NUM NUMBER 资源序号RESOURCE_ID NUMBER 资源内码ACTIVITY_ID NUMBER 活动(1.运行,4.移动,5.排队)STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)ASSIGNED_UNITS NUMBER 已分配单位USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额USAGE_RATE_OR_AMOUNT_INVERSE NUMBER 利用率或金额倒数BASIS_TYPE NUMBER 基本类型SCHEDULE_FLAG NUMBER 计划标识RESOURCE_OFFSET_PERCENT NUMBER 偏差百分比AUTOCHARGE_TYPE NUMBER 自动记帐(1.WIP移动,2.人工,3.PO接收,4.PO移动) 表名：bom.bom_operation_sequences说明：工艺路线细节之工艺工序表OPERATION_SEQUENCE_ID NUMBER 操作序号内码ROUTING_SEQUENCE_ID NUMBER 路线序号内码OPERATION_SEQ_NUM NUMBER 路线序列代码STANDARD_OPERATION_ID NUMBER 标准操作序号内码DEPARTMENT_ID NUMBER 部门内码OPERATION_LEAD_TIME_PERCENT NUMBER 提前期百分比MINIMUM_TRANSFER_QUANTITY NUMBER 最小传送批量COUNT_POINT_TYPE NUMBER 计数点(1.是--自动收费,2.否)OPERATION_DESCRIPTION VARCHAR2(240) 操作说明EFFECTIVITY_DATE DATE 生效日期DISABLE_DATE DATE 失效日期BACKFLUSH_FLAG NUMBER 倒冲库存标识(1.是,2.否)OPTION_DEPENDENT_FLAG NUMBER 可选工序(1.是,2.否)表名: bom.bom_standard_operations说明: 标准操作表STANDARD_OPERATION_ID NUMBER 标准操作标识码OPERATION_CODE VARCHAR2(4) 操作代码ORGANIZATION_ID NUMBER 组织代码DEPARTMENT_ID NUMBER 部门代码MINIMUM_TRANSFER_QUANTITY NUMBER 最小传送数量COUNT_POINT_TYPE NUMBER 计数点(1.是--自动收费,2.否)OPERATION_DESCRIPTION VARCHAR2(240) 操作说明OPTION_DEPENDENT_FLAG NUMBER 可选工序(1.是,2.否)表名: bom.bom_std_op_resources说明: 标准操作资源表STANDARD_OPERATION_ID NUMBER 标准操作标识码RESOURCE_ID NUMBER 资源标识码ACTIVITY_ID NUMBER 活动(1.运行,4.移动,5.排队)RESOURCE_SEQ_NUM NUMBER 资源序号USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额USAGE_RATE_OR_AMOUNT_INVERSE NUMBER 利用率或金额倒数BASIS_TYPE NUMBER 基本类型(1.项目,2.批量)AUTOCHARGE_TYPE NUMBER 自动记帐(1.WIP移动,2.人工,3.PO接收,4.PO移动) STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)ASSIGNED_UNITS NUMBER 已分配单位表名: bom.bom_substitute_components说明: BOM替代件表SUBSTITUTE_COMPONENT_ID NUMBER 替代构成件代码SUBSTITUTE_ITEM_QUANTITY NUMBER 替代项目数量COMPONENT_SEQUENCE_ID NUMBER 组件序号ACD_TYPE NUMBERCHANGE_NOTICE VARCHAR2(10) 更改单表名: bom.cst_std_cost_adj_values说明: 标准成本调节表COST_UPDATE_ID NUMBER 成本更新代码ORGANIZATION_ID NUMBER 组织代码TRANSACTION_TYPE NUMBER 交易类型ADJUSTMENT_QUANTITY NUMBER 调节数量OLD_UNIT_COST NUMBER 老的单位成本NEW_UNIT_COST NUMBER 新的单位成本COST_ELEMENT_ID NUMBER 成本元素代码LEVEL_TYPE NUMBER 级别类型IN_OUT_FLAG NUMBER 出入标志RESOURCE_ID NUMBER 资源代码INVENTORY_ITEM_ID NUMBER 项目内码SUBINVENTORY VARCHAR2(10) 子库WIP_ENTITY_ID NUMBER 离散作业代码OPERATION_SEQ_NUM NUMBER 工艺路线操作序号DEPARTMENT_ID NUMBER 部门代码RESOURCE_SEQ_ID NUMBER 资源序号代码RESOURCE_SEQ_NUM NUMBER 资源序号STANDARD_RATE_FLAG NUMBER 标准率TRANSACTION_ID NUMBER 交易代码BASIS_TYPE NUMBER 基本类型FROM_ORGANIZATION_ID NUMBER 从组织代码TO_ORGANIZATION_ID NUMBER 到组织代码表名: bom.bom_departments说明: 部门代码对应表DEPARTMENT_ID NUMBER 部门内码DEPARTMENT_CODE VARCHAR2(10) 部门代码ORGANIZATION_ID NUMBER 组织代码DESCRIPTION VARCHAR2(240) 说明(汉字说明的部门名称)DISABLE_DATE DATE 失效日期LOCATION_ID NUMBER 地点代码表名: bom.bom_resources说明: 资源代码对应表RESOURCE_ID NUMBER 资源内码RESOURCE_CODE VARCHAR2(10) 资源代码ORGANIZATION_ID NUMBER 组织代码DESCRIPTION VARCHAR2(240) 说明DISABLE_DATE DATE 失效日期COST_ELEMENT_ID NUMBER 成本要素代码PURCHASE_ITEM_ID NUMBER 购买项目代码COST_CODE_TYPE NUMBERFUNCTIONAL_CURRENCY_FLAG NUMBER 货币单位(1.是,2.否)UNIT_OF_MEASURE VARCHAR2(3) 单位DEFAULT_ACTIVITY_ID NUMBER 活动缺省值(1.运行,4.移动,5.排队) RESOURCE_TYPE NUMBER 资源类型(1.机器,2.人员,3.空间,4.杂项,5.数额) AUTOCHARGE_TYPE NUMBER 自动记帐类型(1.WIP移动,2.人工,3.PO接收,4.PO移动)STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)DEFAULT_BASIS_TYPE NUMBER 基本类型缺省值(1.项目,2.批量) ABSORPTION_ACCOUNT NUMBER 分摊帐目ALLOW_COSTS_FLAG NUMBER 计算成本标志(1.否,2.是)RATE_VARIANCE_ACCOUNT NUMBER 差异帐目EXPENDITURE_TYPE VARCHAR2(30) 费用类型表名: bom.bom_operational_routings说明: 工艺路线ROUTING_SEQUENCE_ID NUMBER 路线序号ASSEMBLY_ITEM_ID NUMBER 项目内码ORGANIZATION_ID NUMBER 组织代码ALTERNATE_ROUTING_DESIGNATOR VARCHAR2(10) 替换工艺路线ROUTING_TYPE NUMBER 路线类别COMMON_ASSEMBLY_ITEM_ID NUMBER 公共装配件内码COMMON_ROUTING_SEQUENCE_ID NUMBER 公共路线序号ROUTING_COMMENT VARCHAR2(240) 路线注释COMPLETION_SUBINVENTORY VARCHAR2(10) 完工入库名称COMPLETION_LOCATOR_ID NUMBER 完工入库货位表名：bom.bom_operation_resources说明：工艺路线细节之资源表OPERATION_SEQUENCE_ID NUMBER 操作序列内码RESOURCE_SEQ_NUM NUMBER 资源序号RESOURCE_ID NUMBER 资源内码ACTIVITY_ID NUMBER 活动(1.运行,4.移动,5.排队)STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)ASSIGNED_UNITS NUMBER 已分配单位USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额USAGE_RATE_OR_AMOUNT_INVERSE NUMBER 利用率或金额倒数BASIS_TYPE NUMBER 基本类型SCHEDULE_FLAG NUMBER 计划标识RESOURCE_OFFSET_PERCENT NUMBER 偏差百分比AUTOCHARGE_TYPE NUMBER 自动记帐(1.WIP移动,2.人工,3.PO接收,4.PO移动) 表名：bom.bom_operation_sequences说明：工艺路线细节之工艺工序表OPERATION_SEQUENCE_ID NUMBER 操作序号内码ROUTING_SEQUENCE_ID NUMBER 路线序号内码OPERATION_SEQ_NUM NUMBER 路线序列代码STANDARD_OPERATION_ID NUMBER 标准操作序号内码DEPARTMENT_ID NUMBER 部门内码OPERATION_LEAD_TIME_PERCENT NUMBER 提前期百分比MINIMUM_TRANSFER_QUANTITY NUMBER 最小传送批量COUNT_POINT_TYPE NUMBER 计数点(1.是--自动收费,2.否)OPERATION_DESCRIPTION VARCHAR2(240) 操作说明EFFECTIVITY_DATE DATE 生效日期DISABLE_DATE DATE 失效日期BACKFLUSH_FLAG NUMBER 倒冲库存标识(1.是,2.否)OPTION_DEPENDENT_FLAG NUMBER 可选工序(1.是,2.否)表名: bom.bom_standard_operations说明: 标准操作表STANDARD_OPERATION_ID NUMBER 标准操作标识码OPERATION_CODE VARCHAR2(4) 操作代码ORGANIZATION_ID NUMBER 组织代码DEPARTMENT_ID NUMBER 部门代码MINIMUM_TRANSFER_QUANTITY NUMBER 最小传送数量COUNT_POINT_TYPE NUMBER 计数点(1.是--自动收费,2.否)OPERATION_DESCRIPTION VARCHAR2(240) 操作说明OPTION_DEPENDENT_FLAG NUMBER 可选工序(1.是,2.否)表名: bom.bom_std_op_resources说明: 标准操作资源表STANDARD_OPERATION_ID NUMBER 标准操作标识码RESOURCE_ID NUMBER 资源标识码ACTIVITY_ID NUMBER 活动(1.运行,4.移动,5.排队)RESOURCE_SEQ_NUM NUMBER 资源序号USAGE_RATE_OR_AMOUNT NUMBER 利用率或金额USAGE_RATE_OR_AMOUNT_INVERSE NUMBER 利用率或金额倒数BASIS_TYPE NUMBER 基本类型(1.项目,2.批量)AUTOCHARGE_TYPE NUMBER 自动记帐(1.WIP移动,2.人工,3.PO接收,4.PO移动) STANDARD_RATE_FLAG NUMBER 标准费用率标志(1.否,2.是)ASSIGNED_UNITS NUMBER 已分配单位表名: bom.bom_substitute_components说明: BOM替代件表SUBSTITUTE_COMPONENT_ID NUMBER 替代构成件代码SUBSTITUTE_ITEM_QUANTITY NUMBER 替代项目数量COMPONENT_SEQUENCE_ID NUMBER 组件序号ACD_TYPE NUMBERCHANGE_NOTICE VARCHAR2(10) 更改单PO模块常用表结构表名: po.po_requisition_headers_all说明: 采购请求REQUISITION_HEADER_ID NUMBER PR头标识码SEGMENT1 VARCHAR2(20) PR号ENABLE_FLAG VARCHAR2(1) 使能标志(Y/N)AUTHORIZATION_STATUS VARCHAR2(25) 批准标志(APPROVED/)TYPE_LOOKUP_CODE VARCHAR2(25) 类型(PURCHASE)REQUIST_ID NUMBER 请求标识码CANCEL_FLAG VARCHAR2(1) 取消标志(Y/N)TRANSFERRED_TO_OE_FLAG VARCHAR2(1) 可否转入OE标志(Y/N)PREPARER_ID NUMBER 准备人ID(可与HR.PER_PEOPLE_F.PERSON_ID关联）表名: po.po_requisition_lines_all说明: 采购请求明细REQUISITION_LINE_ID NUMBER PR行IDREQUISITION_HEADER_ID NUMBER PR头IDLINE_NUM NUMBER 栏目LINE_TYPE_ID NUMBER 行类别CATEGORY_ID NUMBER 归类标识码ITEM_DESCRIPTION VARCHAR2(240) 项目描述UNIT_MEAS_LOOKUP_CODE VARCHAR2(25) 单位UNIT_PRICE NUMBER 单价(已折为人民币)QUANTITY NUMBER 数量DELIVER_TO_LOCATION_ID NUMBER 交货位置码（与HR.HR_LOCATIONS.LOCATION_ID关联）TO_PERSON_ID NUMBER 收货人代码SOURCE_TYPE_CODE VARCHAR2(25) 来源类型ITEM_ID NUMBER 项目内码ITEM_REVISION VARCHAR2(3) 项目版本QUANTITY_DELIVERED NUMBER 已交付数量SUGGESTED_BUYER_ID NUMBER 建议采购员代码ENCUMBERED_FLAG VARCHAR2(1) 分摊标志RFQ_REQUIRED_FLAG VARCHAR2(1)NEED_BY_DATE DATE 需求日期(原始)LINE_LOCATION_ID NUMBER 定位行标识码(为空时表示未生成PO) MODIFIED_BY_AGENT_FLAG VARCHAR2(1) 被采购员更改标志（被拆分Y/NULL）PARENT_REQ_LINE_ID NUMBER 父PR行ID（由拆分而来的PR）NOTE_TO_AGENT VARCHAR2(100) 给采购员意见PURCHASING_AGENT_ID NUMBER 采购员标识码DOCUMENT_TYPE_CODE VARCHAR2(25) 文档类型BLANKET_PO_HEADER_ID NUMBER 一揽子订单头标识码BLANKET_PO_LINE_NUM NUMBER 一揽子订单栏目CURRENCY_CODE VARCHAR2(15) 币种RATE_TYPE VARCHAR2(30) 汇率类别RATE_DATE DATE 汇率日期RATE NUMBER 汇率CURRENCY_UNIT_PRICE NUMBER 本币单价SUGGESTED_VENDOR_NAME VARCHAR2(80) 建议供应商SUGGESTED_VENDOR_CONTACT VARCHAR2(80) 建议供应商联系方式SUGGESTED_VENDOR_PHONE VARCHAR2(20) 建议供应商电话UN_NUMBER_ID NUMBER 货期标识码（一般与货期天数同）HAZARD_CLASS_ID NUMBER 风险类别标识码MUST_USE_SUGG_VENDOR_FLAG VARCHAR2(1) 是否必须使用建议供应商REFERENCE_NUM VARCHAR2(25) 参考栏目ON_RFQ_FLAG VARCHAR2(1)URGENT_FLAG VARCHAR2(1) 紧急标志CANCEL_FLAG VARCHAR2(1) 取消标志SOURCE_ORGANIZATION_ID NUMBER 来源组织SOURCE_SUBINVENTORY VARCHAR2(10) 来源子库DESTINATION_TYPE_CODE VARCHAR2(25) 目标类型DESTINATION_ORGANIZATION_ID NUMBER 目标组织DESTINATION_SUBINVENTORY VARCHAR2(10) 目标子库QUANTITY_CANCELLED NUMBER 取消数量CANCEL_DATE DATE 取消日期CLOSED_CODE VARCHAR2(25) 关闭表名: po.po_lookup_codes说明: 检查代码LOOKUP_CODE VARCHAR2(25) 检查代码LOOKUP_TYPE VARCHAR2(30) 检查类型DISPLAYED_FIELD VARCHAR2(25) 显示字段DESCRIPTION VARCHAR2(80) 描述表名: po.po_lookup_types说明: 检查类型LOOKUP_TYPE VARCHAR2(30) 检查类型USER_UPDATABLE_FLAG VARCHAR2(1) 用户可否更改DESCRIPTION VARCHAR2(80) 描述表名: po.po_headers_all说明: 采购订单头PO_HEADER_ID NUMBER 订单头标识码AGENT_ID NUMBER(9) 采购员ID(与HR.PER_PEOPLE_F.PERSON_ID关联) TYPE_LOOKUP_CODE VARCHAR2(25) 类型(STANDARD,QUOTATION) SEGMENT1 VARCHAR2(20) 订单号SUMMARY_FLAG VARCHAR2(1) 汇总标志ENABLED_FLAG VARCHAR2(1) 使能标志VENDOR_ID NUMBER 供应商标识码VENDOR_SITE_ID NUMBER 供应商地址标识码VENDOR_CONTACT_ID NUMBER 供应商联系方式标识码SHIP_TO_LOCATION_ID NUMBER 发运地点标识码BILL_TO_LOCATION_ID NUMBER 付款地点标识码TERMS_ID NUMBER 条款标识码SHIP_VIA_LOOKUP_CODE VARCHAR2(25) 运输方式FOB_LOOKUP_CODE VARCHAR2(25) 口岸代码FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25) 运送条款STATUS_LOOKUP_CODE VARCHAR2(25) 状态CURRENCY_CODE VARCHAR2(15) 币种RATE_TYPE VARCHAR2(30) 汇率类型RATE_DATE DATE 汇率日期RATE NUMBER 汇率FROM_HEADER_ID NUMBER 来源订单头标识码FROM_TYPE_LOOKUP_CODE VARCHAR2(25) 来源检查类型START_DATE DATE 开始时间END_DATE DATE 结束时间BLANKET_TOTAL_AMOUNT NUMBER 一揽子订单总金额AUTHORIZATION_STATUS VARCHAR2(25) 审批状态REVISION_NUM NUMBER 版本号REVISED_DATE VARCHAR2(25) 修订日期APPROVED_FLAG VARCHAR2(1) 批准标志APPROVED_DATE DATE 批准日期AMOUNT_LIMIT NUMBER 金额限制MIN_RELEASE_AMOUNT NUMBER 最小金额NOTE_TO_AUTHORIZER VARCHAR2(240 对审批人的说明NOTE_TO_VENDOR VARCHAR2(240 对供应商的说明NOTE_TO_RECEIVER VARCHAR2(240 对接收者的说明PRINT_COUNT NUMBERPRINTED_DATE DATE 制单日期VENDOR_ORDER_NUM VARCHAR2(25)CONFIRMING_ORDER_FLAG VARCHAR2(1) 签返(确认)订单标志COMMENTS VARCHAR2(240 注释REPLY_DATE DATE 回复日期REPLY_METHOD_LOOKUP_CODE VARCHAR2(25) 回复方法检查代码RFQ_CLOSE_DATE DATEQUOTE_TYPE_LOOKUP_CODE VARCHAR2(25) 报价类型检查代码QUOTATION_CLASS_CODE VARCHAR2(25) 报价类代码QUOTE_WARNING_DELAY_UNIT VARCHAR2(25)QUOTE_WARNING_DELAY NUMBERQUOTE_VENDOR_QUOTE_NUMBER VARCHAR2(25) ACCEPTANCE_REQUIRED_FLAG VARCHAR2(1) 必须接受标志ACCEPTANCE_DUE_DATE DATE 接受截止日期CLOSED_DATE DATE 关闭日期USER_HOLD_FLAG VARCHAR2(1)APPROVAL_REQUIRED_FLAG VARCHAR2(1) 必须批准标志CANCEL_FLAG VARCHAR2(1) 取消标志FIRM_STATUS_LOOKUP_CODE VARCHAR2(30)FIRM_DATE DATEFROZEN_FLAG VARCHAR2(1) 冻结标志CLOSED_CODE VARCHAR2(25) 关闭USSGL_TRANSACTION_CODE VARCHAR2(30)GOVERNMENT_CONTEXT VARCHAR2(30)SUPPLY_AGREEMENT_FLAG VARCHAR2(1) 供应协议标志EDI_PROCESSED_FLAG VARCHAR2(1)EDI_PROCESSED_STATUS VARCHAR2(10)表名: po.po_lines_all说明: 采购订单行PO_LINE_ID NUMBER 行标识码PO_HEADER_ID NUMBER 订单头标识码LINE_TYPE_ID NUMBER 行类型LINE_NUM NUMBER 行栏目ITEM_ID NUMBER 项目内码ITEM_REVISION VARCHAR2(3) 项目版本CATEGORY_ID NUMBER 归类标识码ITEM_DESCRIPTION VARCHAR2(240) 项目描述UNIT_MEAS_LOOKUP_CODE VARCHAR2(25) 单位QUANTITY_COMMITTED NUMBER 提交数量COMMITTED_AMOUNT NUMBER 提交金额ALLOW_PRICE_OVERRIDE_FLAG VARCHAR2(1) 价格佣金允许NOT_TO_EXCEED_PRICE NUMBER 不允许超过价格LIST_PRICE_PER_UNIT NUMBERUNIT_PRICE NUMBER 单价QUANTITY NUMBER 数量UN_NUMBER_ID NUMBER 货期标识码HAZARD_CLASS_ID NUMBER 风险类别标识码NOTE_TO_VENDOR VARCHAR2(240) 对供应商的说明FROM_HEADER_ID NUMBER 来源订单头号FROM_LINE_ID NUMBER 来源行号MIN_ORDER_QUANTITY NUMBER 最小订单数量MAX_ORDER_QUANTITY NUMBER 最大订单数量QTY_RCV_TOLERANCE NUMBER 可接收数量OVER_TOLERANCE_ERROR_FLAG VARCHAR2(25) 超差示错标志MARKET_PRICE NUMBER 市价UNORDERED_FLAG VARCHAR2(1) 不定货标志CLOSED_FLAG VARCHAR2(1) 关闭标志USER_HOLD_FLAG VARCHAR2(1)CANCEL_FLAG VARCHAR2(1) 取消标志CANCELLED_BY NUMBER(9) 取消者CANCEL_DATE DATE 取消日期CANCEL_REASON VARCHAR2(240) 取消原因FIRM_STATUS_LOOKUP_CODE VARCHAR2(30)FIRM_DATE DATETAXABLE_FLAG VARCHAR2(1) 应税标志TAX_NAME VARCHAR2(15) 税种CAPITAL_EXPENSE_FLAG VARCHAR2(1) 大写金额标志NEGOTIATED_BY_PREPARER_FLAG VARCHAR2(1)CLOSED_DATE DATE 关闭日期CLOSED_REASON VARCHAR2(240) 关闭原因TRANSACTION_REASON_CODE VARCHAR2(25) 交易原因ATTRIBUTE10 VARCHAR2(150) 厂家ATTRIBUTE11 VARCHAR2(150) 型号表名: po.po_line_locations_all说明: 采购定位表LINE_LOCATION_ID NUMBER 行定位标识码PO_HEADER_ID NUMBER 订单头标识码PO_LINE_ID NUMBER 行标识码QUANTITY NUMBER 数量QUANTITY_RECEIVED NUMBER 接收数量QUANTITY_ACCEPTED NUMBER 接受数量QUANTITY_REJECTED NUMBER 拒绝数量QUANTITY_BILLED NUMBER 付款数量QUANTITY_CANCELLED NUMBER 取消数量QUANTITY_SHIPPED NUMBER 发运数量UNIT_MEAS_LOOKUP_CODE VARCHAR2(25) 单位PO_RELEASE_ID NUMBERSHIP_TO_LOCATION_ID NUMBER 送货位置SHIP_VIA_LOOKUP_CODE VARCHAR2(25) 运输方式NEED_BY_DATE DATE 要求到货日期PROMISED_DATE DATE 订约日期LAST_ACCEPT_DATE DATE 最后接受日期PRICE_OVERRIDE NUMBER 佣金价格ENCUMBERED_FLAG VARCHAR2(1) 负担标志ENCUMBERED_DATE DATE 负担日期UNENCUMBERED_QUANTITY NUMBERFOB_LOOKUP_CODE VARCHAR2(25)FREIGHT_TERMS_LOOKUP_CODE VARCHAR2(25)TAXABLE_FLAG VARCHAR2(1) 应税标志TAX_NAME VARCHAR2(15) 税种ESTIMATED_TAX_AMOUNT NUMBER 估计税金FROM_HEADER_ID NUMBER 从订单头号FROM_LINE_ID NUMBER 从订单行号FROM_LINE_LOCATION_ID NUMBER 从定位行号START_DATE DATE 开始日期END_DATE DATE 结束日期LEAD_TIME NUMBER 前导时间LEAD_TIME_UNIT VARCHAR2(25) 前导时间单位PRICE_DISCOUNT NUMBER 价格折扣TERMS_ID NUMBERAPPROVED_FLAG VARCHAR2(1) 批准标志APPROVED_DATE DATE 批准日期CLOSED_FLAG VARCHAR2(1) 关闭标志CANCEL_FLAG VARCHAR2(1) 取消标志CANCELLED_BY NUMBER(9) 被谁取消CANCEL_DATE DATE 取消日期CANCEL_REASON VARCHAR2(240) 取消原因FIRM_STATUS_LOOKUP_CODE VARCHAR2(25)FIRM_DATE DATEUNIT_OF_MEASURE_CLASS VARCHAR2(25) 单位ENCUMBER_NOW VARCHAR2(1)INSPECTION_REQUIRED_FLAG VARCHAR2(1) 必须检查标志RECEIPT_REQUIRED_FLAG VARCHAR2(1) 必须签收标志QTY_RCV_TOLERANCE NUMBER 接收差异数量QTY_RCV_EXCEPTION_CODE VARCHAR2(25) 接收例外代码ENFORCE_SHIP_TO_LOCATION_CODE VARCHAR2(25)ALLOW_SUBSTITUTE_RECEIPTS_FLAG VARCHAR2(1) 允许接收替代标志DAYS_EARLY_RECEIPT_ALLOWED NUMBER 可提前天数DAYS_LATE_RECEIPT_ALLOWED NUMBER 可推后天数RECEIPT_DAYS_EXCEPTION_CODE VARCHAR2(25) 接收天数例外代码INVOICE_CLOSE_TOLERANCE NUMBERRECEIVE_CLOSE_TOLERANCE NUMBERSHIP_TO_ORGANIZATION_ID NUMBER 送货到组织代码SHIPMENT_NUM NUMBERSOURCE_SHIPMENT_ID NUMBERSHIPMENT_TYPE VARCHAR2(25) 送货方式CLOSED_CODE VARCHAR2(25) 关闭USSGL_TRANSACTION_CODE VARCHAR2(30) GOVERNMENT_CONTEXT VARCHAR2(30)RECEIVING_ROUTING_ID NUMBERACCRUE_ON_RECEIPT_FLAG VARCHAR2(1)CLOSED_REASON VARCHAR2(240) 关闭原因CLOSED_DATE DATE 关闭日期表名: po.po_un_numbers说明: 货期表UN_NUMBER_ID NUMBER 货期标识码UN_NUMBER VARCHAR2(25) 货期DESCRIPTION VARCHAR2(240) 说明表名: po.po_lines_all说明: 采购订单行PO_LINE_ID NUMBER 行标识码PO_HEADER_ID NUMBER 订单头标识码LINE_TYPE_ID NUMBER 行类型LINE_NUM NUMBER 行栏目ITEM_ID NUMBER 项目内码ITEM_REVISION VARCHAR2(3) 项目版本CATEGORY_ID NUMBER 归类标识码ITEM_DESCRIPTION VARCHAR2(240) 项目描述UNIT_MEAS_LOOKUP_CODE VARCHAR2(25) 单位QUANTITY_COMMITTED NUMBER 提交数量COMMITTED_AMOUNT NUMBER 提交金额ALLOW_PRICE_OVERRIDE_FLAG VARCHAR2(1) 价格佣金允许NOT_TO_EXCEED_PRICE NUMBER 不允许超过价格LIST_PRICE_PER_UNIT NUMBERUNIT_PRICE NUMBER 单价QUANTITY NUMBER 数量UN_NUMBER_ID NUMBER 货期标识码HAZARD_CLASS_ID NUMBER 风险类别标识码NOTE_TO_VENDOR VARCHAR2(240) 对供应商的说明FROM_HEADER_ID NUMBER 来源订单头号FROM_LINE_ID NUMBER 来源行号MIN_ORDER_QUANTITY NUMBER 最小订单数量MAX_ORDER_QUANTITY NUMBER 最大订单数量QTY_RCV_TOLERANCE NUMBER 可接收数量。

库存事务接口：mtl_transactions_interface1）一般用来做各类杂收发、Cost Update，对于和业务有关的事务一般不建议使用，比如SO发货，如果自己发会导致Workflow没有往下走2）成功导入之后运行Cost Manager生成会计分录3）平均成本更新也通过此接口；如果该Item没有交易，则成本数据不会进入cst_item_costs固定资产接口：fa_mass_additions1）fa_mass_additions这个表有几个Trigger需要注意；会自动去插其他表，删除的时候也是2）折旧方法接口表无法给，而是自动从Category继承下来，所以导入之后需要Update表3）不是通过AP引过来的FA，是没有Source Lines信息的；如果需要，可以通过插表来实现4）数据进接口之后从Navigator:Mass Additions/Post Mass Additions提交请求集，这样会有个报表显示导入结果。

不过我的测试程序可以直接提交请求集。

总账分录接口：gl_interface1）最简单的接口，不说了应付发票接口：ap_invoices_interface/ap_invoice_lines_interface1）Profile AP: Use Invoice Batch Controls定义是否使用批控制2）仅支持CREDIT/STANDARD；注意金额正负，小于零用CREDIT，大于零用STANDARD3）PREPAYMENT/DEBIT/EXPENSE REPORT/MIXED不支持，先用CREDIT或者STANDARD，进去之后再Update正式表ap_invoices_all4）导入之后可再调用请求Invoice Validation自动Validate发票应收发票接口：ra_interface_lines_all/ra_interface_salescredits_all1）几个接口表之间的关系和其他接口不同，不是通过ID关联，而是通过描述性弹性域Line Transaction Flexfield定义2）接口给ID还是Name，要根据Transaction Source上的定义，不能随便给3）ra_interface_distributions_all这个表可以不插，让系统自动生成分配行4）如果没有销售员，可以提供一条ID=-3的No Sales Person记录给接口。

[系统基础]Oracle EBS 查找说明性弹性域Title的方法日常我们维护说明性弹性域时都比较无奈，就是不知道弹性域的Title是什么，Oracle说明性弹性域有三千多个，从下拉列表一个一个找，犹如大海捞针，这里介绍一些我常用的2种方法。

1.在Form中已经起用了说明性弹性域的表，可以得到弹性域对话框的Title例如：我们查看日记帐头的弹性域Title路径：GL/Journals/…/点说明性弹性域栏位我们可以看到该说明性弹性域的Title是: Enter Journals: Journals,我们就可以去维护该弹性域了2.从来没有起用过说明性弹性域的表，这种情况我们点击说明性弹性域也跳不出弹性域对话框例如：我们查看搬运单(Mover Orders)行的弹性域路径：INV/Mover Orders/Mover Orders原理：其实只要我们客制化开发过Form，一些我们客制化的Table我们可能会在系统里注册弹性域，所以我们完全可以根据对应的Table去找弹性域的Title。

路径：定位到行/Help/Record History(在查询出记录的情况下，并且5个标准字段有值才能查找)我们可以找到对应的对象：MTL_TXN_REQUEST_LINES_V那么我们可以知道对应的Table一般就是：MTL_TXN_REQUEST_LINESPath：Application Developer/Flexfield/Descriptive/Register通过手电筒，输入Table Name进行查找点击Find按钮我们可以看到Title：Transfer Order Lines就是对应搬运单的行弹性域了.有的表对应定义有多个说明性弹性域，我们可以上下翻看看。

找到了对应的说明性弹性域，我们就可以启用该弹性域了3.查找网页界面的弹性域例如：我们查看Suppliers->Company Profile->Organization下的弹性域Action：点右下脚的About this Page我们可以看到对应的弹性域名为：JG_PO_VENDORS和PO_VENDORS通过Path：Application Developer/Flexfield/Descriptive/Register通过手电筒，输入Name进行查找从而得到弹性域的Title为Vendors。

EBS二次开发学习--编写查询功能查询功能实现的方式有以下三种1、行-值列表模式2、弹出查询窗口模式3、主画布简易模式如果你需要在进入FORM时触发查询功能，则在WHEN–NEW–FORM–INSTANCE触发器结尾处调用:EXECUTE_TRIGGER(’QUERY_FIND’);行-值列表模式实现方法实现Row–LOV, 建立一个选择键值的LOV的form 参数，然后在执行查询前复制该值到结果集数据块的键值字段1）建立键值的FORM参数，例：对于DEPT块，建立参数DEPTNO_QF,用来保存lov选择来的键值2）创建LOV, 返回键值到建立的参数，例如把DEPTNO返回到DEPTNO_QF3）创建PRE-QUERY触发器, 含以下代码：IF :parameter.G_query_find = ’TRUE’ THEN<Primary Key> := :parameter.<Your parameter>;:parameter.G_query_find := ’FALSE’;END IF;注：G_query_find参数是TEMPLATE模板中带有的4）创建(用户自定义名称)结果集数据块QUERY_FIND触发器, 含以下代码：APP_FIND.QUERY_FIND(’<Your LOV Name>’);查询窗口模式实现方法举例子：基于EMP表的数据块, 键值empNO, 做一个查询窗口按empno和Hiredate的范围查询;(有关在查询窗口使用Flexfields则需要特殊方法处理。

参见14-65页.)1、复制QUERY_FIND 对象组从APPSTAND form复制QUERY_FIND 对象组到你的FORM中，它在你的FORM中建立包括一个窗口，一个块，一个画布；复制后把对象组删除。

2、重命名块、画布及窗口,设置块的queryable属性为No；例如：改名块: EMP_QF,画布:EMP_QF_CANVAS, 窗口：EMP_QF_WINDOW;3、修改[New]按钮触发器修改查询窗口块中[NEW]按钮的WHEN–BUTTON–PRESSED触发器,该钮按你的需要而定，如果只是查询不增加修改数据记录，可以不要该钮。

Item Inquiry (查詢物料)內容目錄一Item Search .............................................................................................................................................. 错误!未定义书签。

二Master Items Inquiry ............................................................................................................................... 错误!未定义书签。

三查詢所有供應商列表及相關的Prefix .................................................................................................. 错误!未定义书签。

四查詢Item的審核狀態及數量................................................................................................................ 错误!未定义书签。

五Approved Supplier List ........................................................................................................................... 错误!未定义书签。

六View Quality Result ................................................................................................................................ 错误!未定义书签。

ORACLE EBS常用表查询语句1.OU、库存组织SELECT anization_id ou_org_id, --org_id ou_name, --ou名称anization_id org_org_id, --库存组织idanization_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 id = anization_idAND ood.operating_unit = anization_idAND id =--获取系统IDcall fnd_global.APPS_INITIALIZE(1318,50583,401)select fnd_profile.VALUE('ORG_ID') FROM DUALselect * from hr_operating_units hou where anization_id=204 2.用户、责任及HR--系统责任定义VIEW(FROM FND_RESPONSIBILITY_TL, FND_RESPONSIBILITY) SELECT APPLICATION_ID,RESPONSIBILITY_ID,RESPONSIBILITY_KEY,END_DATE,RESPONSIBILITY_NAME,DESCRIPTIONFROM 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,FULL_NAMEFROM per_people_f;--综合查询SELECTFROM FND_USER AA,FND_USER_RESP_GROUPS BB,FND_RESPONSIBILITY_VL CC,per_people_f DDWHEREANDANDAND RESPONSIBILITY_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_INFORMATION17 省份,DECODE(PAF.SEX,'M','男','F','女','NULL') 性别, --decode 适合和同一值做比拟有多种结果，不适合和多种值比拟有多种结果CASEWHEN'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') < '2000'THEN'90年代'ELSE'21世纪'--case 用法二END出生年代FROM PER_ALL_PEOPLE_F PAF3.供给商VENDOR--供给商主表数据：SELECT ass.vendor_id vendor_id,ass.party_id party_id,ass.segment1 vendor_code,ass.vendor_name vendor_name,ass.vendor_name vendor_short_name,ass.vendor_type_lookup_code vendor_type,flv.meaning vendor_type_meaning, hp.tax_reference tax_registered_name, ass.payment_method_lookup_code payment_method, term_name,att.enabled_flag enabled_flag,att.end_date_active end_date_active,ass.creation_date creation_date,ass.created_by created_by,st_update_date last_update_date,st_updated_by last_updated_by,st_update_login last_update_login FROM ap_suppliers ass,fnd_lookup_values flv,hz_parties hp,ap_terms_tl attWHERE ass.vendor_type_lookup_code = flv.lookup_code(+) AND flv.lookup_type(+) = 'VENDOR TYPE'AND nguage(+) = userenv('LANG')ANDAND nguage = userenv('LANG')AND ass.terms_id = att.term_id(+)--供给商银行信息SELECT ass.vendor_id vendor_id,ass.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 ap_suppliers ass,hz_parties hp,iby_account_owners iao,iby_ext_bank_accounts ieba,hz_parties bank,hz_parties branchWHEREAND 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;--供给商开户行地址信息SELECT hps.party_id party_id,hps.party_site_id party_site_id,hl.location_id location_id,hl.country country,hl.province province,hl.city city,hl.address1 address1,hl.address2 address2,hl.address3 address3,hl.address4 address4FROM hz_party_sites hps, hz_locations hlWHEREORDER BY--供给商联系人信息SELECT hr.subject_id subject_id,hr.object_id object_id,hr.party_id party_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,hcpf.phone_area_code fax_phone_area_code,hcpf.phone_number fax_phone_number,hcpe.email_address email_addressFROM hz_relationships hr,hz_contact_points hcpp,hz_contact_points hcpf,hz_contact_points hcpe,hz_parties hpWHEREANDANDANDAND hr.object_type = 'PERSON'AND hr.relationship_code(+) = 'CONTACT'AND hcpp.owner_table_name(+) = 'HZ_PARTIES'AND hcpf.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 hcpf.contact_point_type(+) = 'PHONE'AND hcpf.phone_line_type(+) = 'FAX'AND hcpe.contact_point_type(+) = 'EMAIL'AND hcpe.phone_line_type IS NULLORDER BY hr.creation_date;--供给商地址主信息SELECT assa.vendor_site_id vendor_site_id,assa.vendor_id vendor_id,assa.vendor_site_code vendor_code,assa.vendor_site_code address_short_name,assa.address_line1 address_line1,assa.address_line2 address_line2,assa.address_line3 address_line3,assa.address_line4 address_line4,_id org_id,assa.country country,assa.province province,assa.city city,assa.county county,assa.zip zip,assa.pay_site_flag pay_site_flag,assa.purchasing_site_flag purchasing_site_flag, assa.inactive_date inactive_date,assa.creation_date creation_date,assa.created_by created_by,st_update_date last_update_date,st_updated_by last_updated_by,st_update_login last_update_login FROM ap_suppliers ass, ap_supplier_sites_all assa WHERE assa.vendor_id = ass.vendor_id;--供给商地址联系人信息：phone、fax和EmailSELECT hcpp.phone_area_code phone_area_code,hcpp.phone_number phone_number,hcpp.phone_extension phone_extension,hcpf.phone_area_code fax_phone_area_code,hcpf.phone_number fax_phone_number,hcpe.email_address email_addressFROM ap_supplier_sites_all assa,hz_contact_points hcpp,hz_contact_points hcpf,hz_contact_points hcpe,hz_party_sites hpsWHEREANDANDANDAND hcpp.owner_table_name(+) = 'HZ_PARTY_SITES'AND hcpf.owner_table_name(+) = 'HZ_PARTY_SITES'AND hcpe.owner_table_name(+) = 'HZ_PARTY_SITES'AND hcpp.contact_point_type(+) = 'PHONE'AND hcpp.phone_line_type(+) = 'GEN'AND hcpf.contact_point_type(+) = 'PHONE'AND hcpf.phone_line_type(+) = 'FAX'AND hcpe.contact_point_type(+) = 'EMAIL'AND hcpe.phone_line_type IS NULL;--供给商地址收件人信息SELECTFROM ap_supplier_sites_all assa--根据party_site_id得到供给商地址的收件人名称SELECT hps.addressee FROM hz_party_sites hps;--供给商银行帐户分配层次关系SELECT * FROM iby_pmt_instr_uses_all;--供给商银行帐户分配层次关系明细(不包括供给商层的分配信息):SELECT * FROM iby_external_payees_all;4.客户CUSTOMER--SQL 查询--客户账户表以许继1063 电网客户为例-->>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;--客户地点 (关联hz_cust_acct_sites_all)SELECT * FROM HZ_PARTY_SITES WHERE PARTY_ID = 21302;--地点地址名称 (关联hz_cust_acct_sites_all)SELECTFROM HZ_LOCATIONS AA, HZ_PARTY_SITES BBWHEREAND BB.PARTY_ID = 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_NAME,_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,FROM hz_party_sites AA,hz_cust_acct_sites_all BB,hz_locations CC,HZ_CUST_SITE_USES_ALL DDWHEREAND BB.CUST_ACCOUNT_ID = 1063AND _ID = 119AND AA.STATUS = 'A'ANDAND ID(AND DD.STATUS <> 'I';--*************综合查询************----客户主数据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,st_update_date last_update_date,st_updated_by last_updated_by,st_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 rtWHEREAND 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 armWHEREANDORDER 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 branchWHEREAND 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 || '-' || hl.province || '-' || hl.city || '-' ||hl.address1 || '-' || hl.address2 || '-' || hl.address3 || '-' || hl.address4 bank_addressFROM hz_party_sites hps, hz_locations hlWHEREORDER 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 || ' ' || 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_addressFROM 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 hcaWHEREANDAND hr.relationship_id = hoc.party_relationship_id(+)ANDANDANDAND 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'Cajan.ZAND 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 NULLANDAND hcar.cust_acct_site_id IS NULLORDER 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,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,st_update_date last_update_date,st_updated_by last_updated_by,st_update_login last_update_loginFROM hz_cust_accounts hca,hz_cust_acct_sites_all hcasa,hz_party_sites hps,hz_locations hlWHEREANDAND hps.location_id = hl.location_id;--客户账户层地址contact person信息:phone,mobile,emailSELECT 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_addressFROM 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 hcaWHEREANDAND hr.relationship_id = hoc.party_relationship_id(+) ANDANDANDAND 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 NULLANDANDORDER BY hr.creation_date;--客户账户地点地址SELECT hp.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,FROM hz_parties hp,hz_cust_accounts hca,hz_party_sites hps,hz_cust_acct_sites_all hcasa,hz_cust_site_uses_all hcsua WHEREANDANDAND--客户主配置文件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_headers_all 销售头select * from oe_order_lines_all 销售行select * from wsh_new_deliveries 发送select * from wsh_delivery_details select * 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 来源编码,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 取消标记FROM OE_ORDER_HEADERS_ALL H,OE_ORDER_LINES_ALL L,HZ_CUST_ACCOUNTS CUST,hz_parties hp,hz_cust_site_uses_all ship_use,hz_cust_site_uses_all bill_use,mtl_system_items_b msi,oe_lookups olWHERE1 = 1ANDANDandandandand l.flow_status_code not in ('CLOSED', 'CANCELLED')andand anization_id = 141andand ol.LOOKUP_TYPE = 'LINE_FLOW_STATUS'AND CUST.ACCOUNT_NUMBER IN ('91010072', '91010067', '91010036') order by party_name，收货地，销售订单;6.采购申请PR--申请单头〔以电网组织ORG_ID=112 内部申请为例SELECT PRH.REQUISITION_HEADER_ID 申请单头ID,PRH.PREPARER_ID,_Id OU_ID,PRH.SEGMENT1 申请单编号,PRH.Creation_Date 创立日期,PRH.Created_By 编制人ID,ER_NAME 用户名称,PP.FULL_NAME 用户姓名,PRH.Approved_Date 批准日期,PRH.Description 说明,PRH.Authorization_Status 状态,PRH.Type_Lookup_Code 类型,PRH.Transferred_To_Oe_Flag 传递标示FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP WHEREANDAND _ID = 112AND PRH.SEGMENT1 = ;-->> 内部申请申请单头ID = 3379--申请单行明细SELECT PRL.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 来源类型,_Id OU_ID,PRL.Source_Organization_Id 对方组织ID,PRL.Destination_Organization_Id 本方组织IDfrom PO_REQUISITION_LINES_ALL PRL,MTL_SYSTEM_ITEMS ITEM WHERE _ID = 112ANDANDAND PRL.REQUISITION_HEADER_ID = 3379;--申请单头 (加对方订单编号)SELECT PRH.REQUISITION_HEADER_ID 申请单头ID,PRH.PREPARER_ID,_Id OU_ID,PRH.SEGMENT1 申请单编号,PRH.Creation_Date 创立日期,PRH.Created_By 编制人ID,ER_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编号FROM PO_REQUISITION_HEADERS_ALL PRH, FND_USER FU, per_people_f PP,OE_ORDER_HEADERS_ALL OEHWHEREANDAND ID(+)AND PRH.SEGMENT1 = ;--(销售订单记录有对方 OU_ID,申请单关键字SOURCE_DOCUMENT_ID 申请单号SOURCE_DOCEMENT_REF)*******************综合查询类*******************--申请单头综合查询〔进限制只能查询 --电网组织ORG_ID=112)SELECT PRH.REQUISITION_HEADER_ID 申请单头ID,_Id 组织ID,PRH.SEGMENT1 申请单编号,PRH.Creation_Date 创立日期,PRH.Created_By 编制人ID,ER_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 本方组织IDFROM PO_REQUISITION_HEADERS_ALL PRH,FND_USER FU,per_people_f PP,PO_REQUISITION_LINES_ALL PRL,MTL_SYSTEM_ITEMS ITEMWHEREANDANDANDANDAND--假设需创立视图只需在 SELECT 语句前加上CREATE OR REPLACE VIEW CUX_INV_PR112 AS7.采购订单PO--采购单头信息 TYPE_LOOKUP_CODE='STANDARD' 〔以供给处OU ORG_ID=119 采购单='' 为例〕--类型说明 TYPE_LOOKUP_CODE='STANDARD'为采购单 TYPE_LOOKUP_CODE='BLANKET'为采购协议SELECT _ID OU_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 审批日期,MENTS 采购单说明,POH.TERMS_ID 条款ID,POH.AGENT_ID 采购员ID,AGT_ST_NAME 采购员,POH.CREATED_BY 创立者ID,ER_NAME 创立用户,PP.FULL_NAME 用户姓名FROM PO_HEADERS_ALL POH, FND_USER FU, per_people_f PP,PER_ALL_PEOPLE_FAGT_PP,ap_suppliers VENDORWHEREANDANDANDAND _ID = 119AND POH.TYPE_LOOKUP_CODE = 'STANDARD'AND POH.SEGMENT1 = ;/*FND_USER FU, per_people_f PP 用户相关表po_agents_name_v 采购员视图 ----> PO_AGENTS.AGENT_ID = PER_ALL_PEOPLE_F.PERSON_ID 采购员相关表ap_suppliers 供给商主表*/-->> POH.SEGMENT1 = _HEADER_ID = 10068Cajan.Z--采购单行信息SELECT _ID OU_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 需求日期FROM PO_LINES_ALL POL,Po_Line_Locations_all PO_LCT,MTL_SYSTEM_ITEMS ITEMWHEREANDANDAND ANIZATION_ID = 142AND _Id = 119AND POL.PO_HEADER_ID = 10068;--说明：Po_Line_Locations_all 系“发运表〞--综合查询1，所分配给供给处组织的物料，存在采购协议，但缺失采购员或缺失仓库；select MSIF.Segment1 物料编码,MSIF.Description 物料描述,MSIF.LONG_DESCRIPTION 物料详细描述,--MSIF.primary_unit_of_measure 计量单位,ST_NAME 采购员,MISD.subinventory_code 默认接收库存,PLA.unit_price 未税价,round(PLA.unit_price * (1 + ZRB.percentage_rate / 100), 2) 含税价,PV.VENDOR_NAME 供给商名称from apps.PO_HEADERS_ALL PHA,PLA,FVL MSIF,MISD,PRF,PV,PVSA,ZRBwhere PHA.Type_Lookup_Code = 'BLANKET'and _Id = 119andPHA.PO_HEADER_ID = PLA.Po_Header_Id andPHA.Global_Agreement_Flag = 'Y'and PHA.Approved_Flag in ('Y', 'R') and NVL(PHA.end_Date, sysdate) >= sysdate andNVL(PLA.Expiration_Date, sysdate) >= sysdate andPLA.Cancel_Flag = 'N'and PLA.Item_Id = MSIF.INVENTORY_ITEM_ID andANIZATION_ID = 142andID(+) andID(+) = 142and MISD.default_type(+) = 2andID(+) andDATE(+) = to_date('4712-12-31', 'YYYY-MM-DD') andPHA.VENDOR_ID = PV.VENDOR_ID andPHA.Vendor_Site_Id = PVSA.VENDOR_SITE_ID andPVSA.VAT_CODE = ZRB.tax_rate_code and(MISD.subinventory_code is null or ST_NAME is null)--采购其他相关表select * from po_distributions_all 分配select * from po_releases_allselect * from rcv_shipment_headers 采购接收头select * from rcv_shipment_lines 采购接收行select * from rcv_transactions 接收事务处理select * from po_agentsselect * from po_vendorsselect * from po_vendor_sites_all8.库存INV--物料主表select ANIZATION_ID 组织ID,MSI.INVENTORY_ITEM_ID 物料ID,MSI.SEGMENT1 物料编码,MSI.DESCRIPTION 物料说明,MSI.ITEM_TYPE 工程类型,MSI.PLANNING_MAKE_BUY_CODE 制造或购置,MSI.PRIMARY_UNIT_OF_MEASURE 根本度量单位,MSI.BOM_ENABLED_FLAG BOM标志,MSI.INVENTORY_ASSET_FLAG 库存资产否,MSI.BUYER_ID 采购员ID,MSI.PURCHASING_ENABLED_FLAG 可采购否,MSI.PURCHASING_ITEM_FLAG 采购工程,MSI.UNIT_OF_ISSUE 单位,MSI.INVENTORY_ITEM_FLAG 是否为库存,MSI.LOT_CONTROL_CODE 是否批量,MSI.RESERVABLE_TYPE 是否要预留,MSI.STOCK_ENABLED_FLAG 能否库存,MSI.FIXED_DAYS_SUPPLY 固定提前期,MSI.FIXED_LOT_MULTIPLIER 固定批量大小,MSI.INVENTORY_PLANNING_CODE 库存方案方法,MSI.MAXIMUM_ORDER_QUANTITY 最大定单数,MSI.MINIMUM_ORDER_QUANTITY 最小定单数,MSI.FULL_LEAD_TIME 固定提前期,MSI.PLANNER_CODE 方案员码,MISD.SUBINVENTORY_CODE 接收子仓库,MSI.SOURCE_SUBINVENTORY 来源子仓库,MSI.WIP_SUPPLY_SUBINVENTORY 供给子仓库,MSI.ATTRIBUTE12 老编码,MSI.INVENTORY_ITEM_STATUS_CODE 物料状态,MSS.SAFETY_STOCK_QUANTITY 平安库存量from mtl_system_items MSI, MTL_ITEM_SUB_DEFAULTS MISD,mtl_safety_stocks MSS where ANIZATION_ID = ANIZATION_ID(+)and MSI.INVENTORY_ITEM_ID = MISD.INVENTORY_ITEM_ID(+)and ANIZATION_ID = ANIZATION_ID(+)and MSI.INVENTORY_ITEM_ID = MSS.INVENTORY_ITEM_ID(+)and ANIZATION_ID = 1155and MSI.SEGMENT1 =--物料库存数量SELECT ANIZATION_ID,MOQ.INVENTORY_ITEM_ID,MOQ.SUBINVENTORY_CODE,SUM(MOQ.TRANSACTION_QUANTITY) QTYFROM mtl_onhand_quantities MOQWHERE MOQ.INVENTORY_ITEM_ID = 12781AND ANIZATION_ID = 1155GROUP BY ANIZATION_ID, MOQ.INVENTORY_ITEM_ID, MOQ.SUBINVENTORY_CODE; --移动平均本钱SELECT CST.INVENTORY_ITEM_ID ITEM_ID,ANIZATION_ID ORG_ID,CST.COST_TYPE_ID 本钱类型,CST.ITEM_COST 单位本钱,CST.MATERIAL_COST 材料本钱,CST.MATERIAL_OVERHEAD_COST 间接费,CST.Resource_Cost 人工费,CST.OUTSIDE_PROCESSING_COST 外协费,CST.OVERHEAD_COST 制造费FROM CST_ITEM_COSTS CSTWHERE CST.COST_TYPE_ID = 2AND CST.INVENTORY_ITEM_ID = 12781AND ANIZATION_ID = 1155;--综合查询-库存数量及本钱SELECT ANIZATION_ID 组织ID,MSI.INVENTORY_ITEM_ID 物料ID,MSI.SEGMENT1 物料编码,MSI.DESCRIPTION 物料说明,MSI.PLANNING_MAKE_BUY_CODE M1P2,MOQV.SUBINVENTORY_CODE 子库存,MOQV.QTY 当前库存量,CST.ITEM_COST 单位本钱,CST.MATERIAL_COST 材料本钱,CST.MATERIAL_OVERHEAD_COST 间接费,CST.Resource_Cost 人工费,CST.OUTSIDE_PROCESSING_COST 外协费,CST.OVERHEAD_COST 制造费FROM MTL_SYSTEM_ITEMS MSI,CST_ITEM_COSTS CST,(SELECT ANIZATION_ID,MOQ.INVENTORY_ITEM_ID,MOQ.SUBINVENTORY_CODE,SUM(MOQ.TRANSACTION_QUANTITY) QTY FROM mtl_onhand_quantities MOQWHERE ANIZATION_ID = 1155GROUP BY ANIZATION_ID,MOQ.INVENTORY_ITEM_ID,MOQ.SUBINVENTORY_CODE) MOQV WHERE ID(+)AND ID(+)AND ID(+)AND ID(+)AND CST.COST_TYPE_ID = 2AND ANIZATION_ID = 1155AND MSI.SEGMENT1 =--子库存列表SELECT * FROM mtl_secondary_inventories;--货位列表SELECT ORGANIZATION_ID 组织代码,INVENTORY_LOCATION_ID 货位内码,SUBINVENTORY_CODE 子库名称,SEGMENT1 货位编码FROM mtl_item_locations;--方案员表SELECT PLANNER_CODE 方案员代码,ORGANIZATION_ID 组织代码,DESCRIPTION方案员描述,MP.EMPLOYEE_ID 员工ID,DISABLE_DATE 失效日期FROM mtl_planners MP;--科目设置等参数select * from MTL_PARAMETERS MP9.物料清单BOM--BOM主表bom_bill_of_materialsselect AA.BILL_SEQUENCE_ID 清单序号,AA.ASSEMBLY_ITEM_ID 装配件内码,ANIZATION_ID 组织代码,BB.SEGMENT1 物料编码,BB.DESCRIPTION 物料说明,AA.ASSEMBLY_TYPE 装配类别from bom_bill_of_materials AA，mtl_system_items BB whereand ANIZATION_ID = ANIZATION_ID;--BOM明细表bom_inventory_componentsselect BILL_SEQUENCE_ID 清单序号,COMPONENT_SEQUENCE_ID 构件序号,ITEM_NUM 工程序列,OPERATION_SEQ_NUM 操作序列号,COMPONENT_ITEM_ID 子物料内码,COMPONENT_QUANTITY 构件数量,DISABLE_DATE 失效日期,supply_subinventory 供给子库存,BOM_ITEM_TYPEfrom bom_inventory_components;--BOM明细综合查询 ( 组织限定供给处142 装配件 = '5XJ061988') SELECT VBOM.BID 清单序号,VBOM.F_ITEMID 装配件内码,BB.SEGMENT1 物料编码,BB.DESCRIPTION 物料说明,VBOM.OGT_ID 组织内码,VBOM.CID 操作ID,VBOM.ITEM_NUM 物料序号,VBOM.OPID 工序,VBOM.C_ITEMID 子物料内码,CC.SEGMENT1 物料编码,CC.DESCRIPTION 物料说明,VBOM.QTY 构件数量,CC.PRIMARY_UOM_CODE 子计量单位码,CC.PRIMARY_UNIT_OF_MEASURE 子计量单位名,VBOM.WHSE 供给子仓库FROM (select AA.BILL_SEQUENCE_ID BID,BB.ASSEMBLY_ITEM_ID F_ITEMID,ANIZATION_ID OGT_ID,PONENT_SEQUENCE_ID CID,AA.ITEM_NUM ITEM_NUM,AA.OPERATION_SEQ_NUM OPID,PONENT_ITEM_ID C_ITEMID,PONENT_QUANTITY QTY,AA.supply_subinventory WHSEfrom bom_inventory_components AA, bom_bill_of_materials BBwhere AA.BILL_SEQUENCE_ID = BB.BILL_SEQUENCE_ID) VBOM,mtl_system_items BB,mtl_system_items CCWHEREandandandand VBOM.OGT_ID = 142and BB.SEGMENT1 = '5XJ061988'ORDER BY VBOM.ITEM_NUM;--单层BOM本钱查询(需系统提交请求计算后)select inventory_item_id, organization_id, item_cost, program_update_datefromwhere inventory_item_id = 23760and organization_id = 142;select inventory_item_id, organization_id, item_cost, program_update_datefrom cst_item_cost_detailswhere inventory_item_id = 23760and organization_id = 142;10.作业任务WIP说明：查询作业任务头以及作业任务工序和BOM情况--作业任务头信息表--〔以直流 OU_ID=117；ORGANIZATION_ID=1155; 及任务WIP_ENTITY_NAME='XJ39562'; 装配件编码SEGMENT1 = '07D9202.92742'为例〕SELECT AA.WIP_ENTITY_ID 任务令ID,ANIZATION_ID 组织ID,。

EBS菜单繁多，有时候难免找不到北，下面以实际例子来说明查找方法。

54.2 解决方案假设在某个职责下通过Ctrl+L找到某个功能，如下，其实Ctrl+L查找的是功能名，假设我查到的功能名称是HEL:物料编码申请,当然如果打开看了界面，通过菜单栏的帮助>关于Oracle Applications查看，比如查看到的表单名是HELINVIC.fmx，那么该表单的简称按照规范应该是HELINVIC。

然后使用应用开发员职责，打开应用产品>表单，通过刚才查询到的表单简称HELINVIC查询到结果如下,看到了用户表单名是HEL:物料编码申请，然后再打开应用产品>功能,根据用户功能名HEL:物料编码申请查询到的结果如下，当然如果是按照规范的话，也可以根据表单简称HELINVIC 作为表单查询项也可以查询到同样的结果：在该界面的菜单栏帮助>诊断>检查的弹出框中，字段选择FUNCTION_ID，则可查到该功能的ID，是27191。

然后通过如下SQL语句可以查询到哪些菜单用到了这个功能，注意更改红色部分为上步所查找到的功能ID：SELECT fmv.MENU_ID,fmv.MENU_NAME,ER_MENU_NAME,fmv.TYPE,fmv.DESCRIPTIONFROM FND_MENUS_VL fmvwhere exists (SELECT fmev.MENU_IDFROM FND_MENU_ENTRIES_VL fmevwhere fmev.FUNCTION_ID = 27191and fmev.MENU_ID = fmv.MENU_ID)然后这些菜单在哪些职责中呢？职责对应哪个菜单可以通过系统管理员职责的安全性>责任>定义界面看到，一个责任名对应哪个菜单呢？可以通过如下语句查看，将红色部分换成你需要查看的职责名：SELECT RESPONSIBILITY_NAME,RESPONSIBILITY_KEY,DESCRIPTION,MENU_IDFROM FND_RESPONSIBILITY_VLWHERE (version = '4' or version = 'W' or version = 'M')and RESPONSIBILITY_NAME=' HELC_客户化开发'但是这样找到职责对应的菜单没有用，你还无法确认该职责是否会包含你要查找的功能。

Oracle EBSPO 常用的查询及 TipsInternal Requisitions that do not have an Internal Sales orderSelect RQH.SEGMENT1 REQ_NUM,RQL.LINE_NUM,RQL.REQUISITION_HEADER_ID ,RQL.REQUISITION_LINE_ID,RQL.ITEM_ID ,RQL.UNIT_MEAS_LOOKUP_CODE ,RQL.UNIT_PRICE ,RQL.QUANTITY ,1.列出没有销售订单的内部采购订单 ---used to list allassociatedRQL.QUANTITY_CANCELLED,RQL.QUANTITY_DELIVERED ,RQL.CANCEL_FLAG ,RQL.SOURCE_TYPE_CODE ,RQL.SOURCE_ORGANIZATION_ID ,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAG fromPO_REQUISITION_LINES_ALL RQL,PO_REQUISITION_HEADERS_ALL RQH whereRQL.REQUISITION_HEADER_ID =RQH.REQUISITION_HEADER_ID andRQL.SOURCE_TYPE_CODE = 'INVENTORY' andRQL.SOURCE_ORGANIZATION_ID is not null and not exists (select 'existing internal order' from OE_ORDER_LINES_ALL LIN whereLIN.SOURCE_DOCUMENT_LINE_ID =RQL.REQUISITION_LINE_ID andLIN.SOURCE_DOCUMENT_TYPE_ID = 10) orDER BY RQH.REQUISITION_HEADER_ID,RQL.LINE_NUM;2. 关联PR 的PO ---- Relation with Requistion and PO select r.segment1 "Req Num",p.segment1 "PO Num"from po_headers_all p, po_distributions_all d,po_req_distributions_all rd,po_requisition_lines_all rl,po_requisition_headers_all r where p.po_header_id = d.po_header_id and d.req_distribution_id =rd.distribution_id and rd.requisition_line_id = rl.requisition_line_id andrl.requisition_header_id =r.requisition_header_id 3.有取消的PR -- list My cancel Requistion select prh.REQUISITION_HEADER_ID,prh.PREPARER_ID ,prh.SEGMENT1 "REQ NUM",trunc(prh.CREATION_DATE), prh.DESCRIPTION,prh.NOTE_TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh,apps.po_action_history pahwhere Action_code='CANCEL'and pah.object_type_code='REQUISITION' andpah.object_id=prh.REQUISITION_HEADER_ID 4.PO 的Pr -- list all Purchase Requisition without没有aPurchase order that means a PR has not beenautocreated to PO.selectprh.segment1 "PR NUM",trunc(prh.creation_date) "CreateD ON",trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #",msi.segment1 "Item Num",prl.item_description "Description",prl.quantity "Qty",trunc(prl.need_by_date) "Required By",ppf1.full_name "REQUESTOR",ppf2.agent_name "BUYER"frompo.po_requisition_headers_all prh,po.po_requisition_lines_all prl,apps.per_people_f ppf1,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,po.po_req_distributions_all prd,inv.mtl_system_items_b msi,po.po_line_locations_all pll,po.po_lines_all pl,po.po_headers_all phWhereprh.requisition_header_id =prl.requisition_header_idand prl.requisition_line_id =prd.requisition_line_idand ppf1.person_id = prh.preparer_idand prh.creation_date betweenppf1.effective_start_date andppf1.effective_end_dateand ppf2.agent_id(+) = msi.buyer_idand msi.inventory_item_id = prl.item_idand anization_id =prl.destination_organization_idand pll.line_location_id(+) =prl.line_location_idand pll.po_header_id = ph.po_header_id(+) AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)AND PRH.AUTHORIZATION_STATUS = 'APPROVED' AND PLL.LINE_LOCATION_ID IS NULLAND PRL.CLOSED_CODE IS NULLAND NVL(PRL.CANCEL_FLAG,'N') &lt;&gt; 'Y'orDER BY 1,25. 在PR 转PO 过程中的( 应该是自动创建里面的数据吧) List and all data entry from PR tillPOselect distinct u.description "Requestor", porh.segment1 as "Req Number",trunc(porh.Creation_Date) "Created On",ST_UpdateD_BY, porh.Authorization_Status "Status", porh.Description "Description",poh.segment1 "PO Number",trunc(poh.Creation_date) "PO Creation Date", poh.AUTHORIZATION_STATUS "PO Status",trunc(poh.Approved_Date) "Approved Date" from apps.po_headers_all poh,apps.po_distributions_all pod,apps.po_req_distributions_all pord,apps.po_requisition_lines_all porl,apps.po_requisition_headers_all porh,apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_id andporl.requisition_line_id =pord.requisition_line_id and pord.distribution_id = pod.req_distribution_id(+) and pod.po_header_id = poh.po_header_id(+) and porh.created_by =er_idorder by 2 6. 没有自动创建PO 成功的PR list allPurchase Requisition without a Purchase orderthat means a PR has not been autocreated to PO.selectprh.segment1 "PR NUM",trunc(prh.creation_date) "CreateD ON",trunc(prl.creation_date) "Line Creation Date" , prl.line_num "Seq #",msi.segment1 "Item Num",prl.item_description "Description",prl.quantity "Qty",trunc(prl.need_by_date) "Required By",ppf1.full_name "REQUESTOR",ppf2.agent_name "BUYER"frompo.po_requisition_headers_all prh,po.po_requisition_lines_all prl,apps.per_people_f ppf1,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,po.po_req_distributions_all prd,inv.mtl_system_items_b msi,po.po_line_locations_all pll,po.po_lines_all pl,po.po_headers_all phWhereprh.requisition_header_id =prl.requisition_header_idand prl.requisition_line_id =prd.requisition_line_idand ppf1.person_id = prh.preparer_idand prh.creation_date betweenppf1.effective_start_date and ppf1.effective_end_date and ppf2.agent_id(+) = msi.buyer_idand msi.inventory_item_id = prl.item_idand anization_id =prl.destination_organization_idand pll.line_location_id(+) = prl.line_location_id and pll.po_header_id = ph.po_header_id(+)AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)AND PRH.AUTHORIZATION_STATUS = 'APPROVED'AND PLL.LINE_LOCATION_ID IS NULLAND PRL.CLOSED_CODE IS NULLAND NVL(PRL.CANCEL_FLAG,'N') &lt;&gt; 'Y'orDER BY 1,27.PR 与PO 的关联表PO_DISTRIBUTIONS_ALL =&gt;PO_HEADER_ID,REQ_DISTRIBUTION_IDPO_HEADERS_ALL=&gt;PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =&gt;DISTRIBUTION_ID, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL =&gt;REQUISITION_LINE_ID)PO_REQUISITION_HEADERS_ALL =&gt;REQUISITION_HEADER_ID,REQUISITION_LINE_ID, SEGMENT1What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) andPO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req. 你要做的就是将PO_DISTRIBUTIONS_ALL 的REQ_DISTRIBUTION_ID 与关联，查看看 PR 是否有对应的 PO8. 未结 PO open PO'S select h.segment1 "PO NUM",h.authorization_status "STATUS", l.line_num "SEQ NUM", ll.line_location_id,d. po_distribution_id , h.type_lookup_code "TYPE" from po.po_headers_all h,po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_all d whereh.po_header_id = l.po_header_id andll.po_line_id = l.po_Line_id andll.line_location_id = d.line_location_id and h.closed_date is null and h.type_lookup_code not in ('QUOTATION') 9.List andPO With there approval , invoice and payment detailsList and PO With there approval , invoice and payment details select _id "ORG ID", E.SEGMENT1 "VENDOR NUM",e. vendor_name "SUPPLIER NAME",UPPER(e.vendor_type_lookup_code) "VENDOR TYPE",f. vendor_site_code "VENDOR SITE CODE", PO_REQ_DISTRIBUTIONS_ALL 中的 DISTRIBUTION_ID List allf.ADDRESS_LINE1 "ADDRESS",f.city "CITY", f.country "COUNTRY",to_char(trunc(d.CREATION_DATE)) "PO Date",d.segment1 "PO NUM", d.type_lookup_code "PO Type", c.quantity_ordered "QTY orDERED",c.quantity_cancelled "QTY CANCELLED",g.item_id "ITEM ID" ,g.item_description "ITEM DESCRIPTION",g.unit_price "UNIT PRICE",(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0))*NVL(g.unit_price,0) "PO Line Amount",(select decode(ph.approved_FLAG, 'Y','Approved') from po.po_headers_all ph where ph.po_header_ID = d.po_header_id)"POApproved?", a.invoice_type_lookup_code "INVOICE TYPE",a.invoice_amount "INVOICE AMOUNT",to_char(trunc(a.INVOICE_DATE)) "INVOICE DATE", a.invoice_num "INVOICE NUMBER",(select decode(x.MATCH_STATUS_FLAG, 'A','Approved') fromap.ap_invoice_distributions_all x wherex.INVOICE_DISTRIBUTION_ID =b.invoice_distribution_id)"Invoice Approved?", a.amount_paid,h.amount,h.check_id,h.invoice_payment_id "Payment Id",i.check_number "Cheque Number",to_char(trunc(i.check_DATE)) "PAYMENT DATE" FROMAP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VENDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL Iwhere a.invoice_id = b.invoice_id andb.po_distribution_id =c. po_distribution_id (+) and c.po_header_id =d.po_header_id (+) ande.vendor_id (+) = d.VENDOR_ID andf.vendor_site_id (+) = d.vendor_site_id and d.po_header_id =g.po_header_id andc.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id andc.PO_HEADER_ID is not null anda.payment_status_flag = 'Y' andd.type_lookup_code != 'BLANKET'。