X

Recent Posts

Oracle EBS进化史

通过图表总结了下Oracle EBS的进化历史,回顾历史,Oracle已经在EBS这个产品上耕耘了25年,不得不惊叹这个产品的生命力,Fusion出了之后,R12.2会不会是EBS最后一个Release Oracle EBS历史上的那些事儿 1987年 这家于1977年创建的软件公司,于1986年在NASDAQ上市,1987年营收已达1亿美金,当然,主要是数据库产品的收入,因为oracle于1987年才正式建立起一个仅7个人的应用软件开发部门。可笑的是,这个应用软件部门最初的任务,一半是为自己的财务部门开发应用软件,一半是在销售数据库产品时,应客户的要求顺便将自家使用的财务软件拿出来卖。 1988年1988年,SAP的创业者们做出了一个后来被称为“天才”的决定:开发基于C/S架构的R/3系统。 1989年 1989年,ORACLE首次发布制造应用,库存管理(INV)是其第一个模块。注意:INV模块以后将在ORACLE ERP产品的应用架构中,扮演着极其重要的角色。 1990年 1990年,ORACLE发布应用产品R7、R8,主要还是C/S架构的财务会计软件,与SAP对“R”的“实时”定义不同,这里的“R”仅是“Release(版本)”的意思,ORACLE应用产品后来一直沿用了这一叫法。 1992年1992年,ORACLE发布应用产品R9,这是一个包含财务会计、生产制造、人力资源的应用软件包。这一年ORACLE已经基本摆脱危机困境,年营收达到12亿美金,当然,数据库仍是主要收入来源。其时,在应用软件方面,ORACLE大约已经拥有了1500个客户,但主要是一些中小型的美国公司 1993年1993年,ORACLE 决定将过去所有的应用产品重写以适应于C/S架构。当年,发布应用产品 R10,包括财务、制造与人力资源三大部分。 1993年1993年另外一家中国本土企业应用软件公司“金蝶”成立,也是从财务软件开始做起。截至2008年,其年营收约1.25亿美金。 1995年1995年,ORACLE应用产品R10SC(smart client application)发布。1996年,ORACLE开始实施其应用产品的JAVA战略,30多个模块全部实现JAVA使能。 1996年1996年SAP在中国华为项目上输给ORACLE就是案例之一,其后,又在美的、中兴等项目上相继失利于ORACLE。SAP此时开始公开与ORACLE翻脸交恶,并宣布在其ERP销售中将主要向客户推荐IBM的DB2数据库产品。 1999年1999年,ORACLE应用产品R11 套件面世。到98年4月,已经有大约1300个客户。 2002年2002年5月,软件巨人微软公司在继一年多前以11亿美金收购一家美国本土中型财务软件公司后,又以13亿美金收购欧洲小型企业应用软件供应商Navision,希图在据说整体规模有千亿美金之巨的企业应用软件市场分一杯羹。有媒体甚至报道说“微软收购欧洲ERP公司目标直指SAP”。 2004年在2004年以103亿美金的代价将Peoplesoft和JDE一起纳入囊中。至此,ORACLE与SAP在ERP市场的整体份额差距进一步缩小,因为Peoplesoft与JDE当时位列世界第三、第四。尽管当时有很多人怀疑ORACLE会消化不良、自食其果,但时间已经表明,ORACLE笑到了最后。 2005年2005年,ORACLE以58.5亿美金的代价并购Sieble。Sieble最著名的产品是CRM,该产品追根究源,可以追溯到ORACLE早年在其内部使用的一个“电话销售管理系统”。叶落归根,Sieble的历史仿佛转了一个圈,又回到了起点。 2007年2007年,ORACLE正式发布EBS R12。此时的EBS已经包含有高度集成的300多个模块,几乎覆盖了制造业、商业、金融、服务、政府、公用事业等等各行各业的全部应用。过去曾看到ORACLE的一个说法:R12将是EBS的最后一个版本。不知道ORACLR葫芦里卖的是什么药?同年,ORACLE以33亿美金的代价收购知名的绩效管理软件Hyperion,以近5亿美金代价收购知名的产品生命周期管理软件Agile。 2008年截至2008年,ORACLE年营收为224亿美金,而SAP年营收为161亿美金。年人均创收ORACLE为26万美金,SAP则为31万美金。相比之下,国内的用友年营收2.5亿美金,人均创收仅3.6万美金,金蝶年营收1.25亿美金,人均创收仅3.1万美金,差距十分明显。 2009年 R12.1发布 转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7650191

通过图表总结了下Oracle EBS的进化历史,回顾历史,Oracle已经在EBS这个产品上耕耘了25年,不得不惊叹这个产品的生命力,Fusion出了之后,R12.2会不会是EBS最后一个Release Oracle EBS历史上的那些事儿 1987年 这家于1977年创建的软件公司,于1986年在NASDAQ上市,1987年营收已达1亿美金,当然,主要是数据库产品的收入,因为oracle于1987年才...

Oracle EBS Concurrent Request:Gather Schema Statistics

Oracle EBS 的Concurrent Request"Gather Schema Statistics"是一个和性能相关的Concurrent Program,它会对表,列和索引的做数据采样,并生成统计信息。基于成本的优化器(Cost-Based Optimization CBO)会根据你的数据采样来评估执行计划,最终选取一个最高效的方式来执行SQL。补充一点:数据采样十分类似于问卷调查,假如你的目标群体非常庞大,你就不可能对所有目标客户逐一进行调研,正确有效地做法是在目标人群中挑选一定百分比的人进行调查,最终根据这部分被调查的人来推导出目标人群的情况。什么样的情况下运行“Gather Schema Statistics”当数据库发生较大变化的时候,比如新增了很多数据或者很多数据被删除,都应该运行一下这个program。需要注意的是,这个Request比较耗时,并且非常消耗数据库资源,所以最好把运行这个program的时间安排到周末或者晚上。如何运行“Gather Schema Statistics”1. Responsibility = System Administrator2. Concurrent > Requests3. 查询“Gather Schema Statistics”4. 输入参数Parameter需要特别提到的是:Schema Name:指的是你要对那个Schema下的对象进行统计,如果要对所有数据库对象进行统计,这里可以选择ALL。Estimate Percent(评估比):是指对数据库对象的采样比例,默认为10%,这个值不要设置的太高,否则反会影响性能。(refer:OTN的一帖子),也有一些性能问题,适当提高评估比后,可以显著提升性能。其他Parameter可以参见UserGuide。如何判断数据库对象上一次运行统计的时间和统计比SELECT num_rows,sample_size,last_analyzed from dba_tables WHERE table_name = 'MTL_SYSTEM_ITEMS_B';  output:NUM_ROWS:151210SAMPLE_SIZE:15121LAST_ANALYZED:2011-11-10Last_Analyzed的时间就是上次运行统计收集的时间。Sample_Size是采样的行数,Num_Rows是真实数据库表中的行数。Same_Size/Num_Rows = 15121/151210 = 10%,这个10%实际上就是Estimate Percent。另外下边的SQL可以查看制定表中列的统计情况SELECT last_analyzed,sample_size   FROM  dba_tab_columns   WHERE table_name = 'MTL_SYSTEM_ITEMS_B';  FND_STATS.GATHER_TABLE_STATS另外除了使用Gather Schema Statistics,也可以直接调用FND_STATS.GATHER_TABLE_STATS来重做统计.可以refer:http://docs.oracle.com/cd/E18727_01/doc.121/e12893/T174296T174306.htm也可以调用这个Package来只做某个特定表的统计,eg.EXEC FND_STATS.GATHER_TABLE_STATS(OWNNAME => 'INV',TABNAME => 'MTL_SYSTEM_ITEMS_B',PERCENT => 20);  DBMS_STATS.GATHER_TABLE_STATSSample:EXEC DBMS_STATS.GATHER_TABLE_STATS('APPS','MTL_MATERIAL_TRANSACTIONS', CASCADE=>TRUE );  =====EOF====转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7685957

Oracle EBS 的Concurrent Request"Gather Schema Statistics"是一个和性能相关的Concurrent Program,它会对表,列和索引的做数据采样,并生成统计信息。基于成本的优化器(Cost-Based Optimization CBO)会根据你的数据采样来评估执行计划,最终选取一个最高效的方式来执行SQL。 补充一点:数据采样十分类似于问卷调查,假...

Inventory Picking Rule(Oracle Apps R12) 用于在非WMS组织下,给Sales Order, WIP, OPM Manufacturing设置挑库规则 路径:Inventory > Setup > Rules > Picking Lot: You can sort by lot based on the following criteria: Lot Number Asc: Sorts the lot by number in ascen

Inventory Picking Rule(Oracle Apps R12) 用于在非WMS组织下,给Sales Order, WIP, OPM Manufacturing设置挑库规则 路径:Inventory > Setup > Rules > Picking Lot: You can sort by lot based on the following criteria: Lot Number Asc: Sorts the lot by number in ascending order. Lot Number Desc: Sorts the lot by number in descending order. FIFO: Sorts the lot by first in first out. FEFO: Sorts the lot by first expired first out. Revision: You can sort by revision based on the following criteria: Revision Asc: Sorts by revision in ascending order. Revision Asc: Sorts by revision in descending order. Effective Date Asc: Sorts by revision effective date in ascending order. Effect Date Desc: Sorts by revision effective date in descending order. Subinventory: You can sort by subinventory based on the following criteria: Subinventory Asc: Sorts by subinventory in ascending order. Subinventory: Desc: Sorts by subinventory in descending order. Receipt Date Asc: Sorts by receipt date in ascending order. Receipt Date: Desc: Sorts by receipt date in descending order. Locator: You can choose to sort by locator based on the following criteria: Locator Asc: Sorts by locator in ascending order. Locator Desc: Sort by locator in descending order. Receipt Date Asc: Sorts by receipt date in ascending order. Receipt Date: Desc: Sorts by receipt date in descending order. 创建完一个Picking rule之后,你可以使用Rules Workbench来把Picking Rule规则分配给具体的Item项。Seq的数字越小,优先级越高。 Organization Parameter下边关于Picking Rule的设置 Picking Default里的Rule是用于选择一个默认的rule规则,这个默认的Picking rules用于Order Management来挑选货物。根据NOTE1327488.1,If it will assign seeded picking rule at Org Paramerts and user define Picking rules in Rules Bench.. Which one is effective? The Rules workbench has a high priority over the Organization Parameters Picking Rule。 特别注意:This rule will not be employed in a WMS enabled organization. (WMS 的Org不用默认的Org Picking Rule) Item下Picking Rule的设置 用于设置这个Item默认选取的rule规则。所以Picking Rule可以在Org级设置也可以在Item级制定。 WMS Picking Rule路径:Warehouse Manager > Setup > Warehouse Configuration > Rules > Warehouse Execution > RulesINV Rule和WMS Rule的关系: 对于非WMS组织,应该使用Inv的Picking Rule,只有WMS才用上边这个路径来设置Picking Rule。下图为非WMS组织打开WMS Rule form的提示,"WMS Rules / Strategies are not supported in INV org. Use Picking Rules : Inventory -> Setup -> Rule -> picking" 如果是在WMS下,打开WMS Rules form,界面如下: 很明显WMS的Picking Rule能定义更负责的挑库规则。下边为http://www.oracleug.com/user-guide/warehouse-management/overview-picking的一段解释 picking rule in inventory determine the order in which revisions, lots, subinventories, and locators are picked for sales orders. Picking rule in WMS is used for directed picking creates material allocations and directs operators to pick material from specific locations. They(INV rule and WMS Rule) workin conjunction with the different pick methodologies. Pick methodologies refer to the different method an operator performs picking tasks. For example, an operator may select to pick an order by itself, or to pick multiple orders at the same time. The system generates picking tasks and dispatches them to qualified operators based on rules. After picking is complete, the system provides operators with the appropriate number and size of containers the order requires for shipping. WMS Rule定义的流程 In a WMS Organization,you need to create your rule with following nav path: Warehouse Manager > Setup > Warehouse Configuration > Warehouse Execution > Rules After doing that and enabling the rule you should be able to assign rule to Rules Workbench. Nav Path: Warehouse Manager > Setup > Warehouse Configuration > Warehouse Execution > Rules Workbench Then you should run Generate All Rules concurrent program Warehouse Manager > Others > Others > Requests > Requests > Run > Generate All Rules 另外关于Pick Release Rules 路径:Order Management > Shipping > setup > Define Release Rules Forms Pick Release Rule和Picking Rule 是两个不同的东西,Pick Release rule是一个在做SO Pick Release时的模板,做SO Pick Release的时候,你可以选择不同的模板,这样可以会自动带出Pick Release的预定义选项。(感觉应该改名字叫Release Template,会更清楚些) INV Picking Rule的后台逻辑 从R12开始,对于非WMS Org,在做Pick Release的时候,WMS Rules Engine会被调用,就是说,对于non WMS organizations,Rule Engine将不会看WMS strategies,它只会看Inventory Picking Rules。 每创建一个新的Rule,在MTL_PICKING_RULES表会插入一条记录,Sample Query: SELECT * FROM mtl_inv_picking_rules WHERE wms_rule_id = 10533; 几个MTL_PICKING_RULES表中的字段 LOT_SORT:                 0 - NONE, no sort; 1 - Lot number Asc; 2 - Lot number Desc; 3 - FIFO , first in first out; 4 - FEFO, first expire first out LOT_SORT_RANK:     Order by rank regarding to other sort criteria, saved as wms_sort_criteria.sequence_number REVISION_SORT:        0 - NONE; 1 - Revision Asc; 2 - Revision Desc; 3 - Effective date Asc; 4 - Effective date Desc REVISION_SORT_RANK:Order by rank regarding to other sort criteria, saved as wms_sort_criteria.sequence_number SUBINVENTORY_SORT:0 - NONE; 1 - Subinventory Asc; 2 - Subinventory Desc; 3 - Receipt date Asc; 4 - Receipt date Desc SUBINVENTORY_SORT_RANK:Order by rank regarding to other sort criteria, saved as wms_sort_criteria.sequence_number LOCATOR_SORT:        0 - NONE; 1 - Locator Asc; 2 - Locator Desc; 3 - Receipt date Asc; 4 - Receipt date Desc LOCATOR_SORT_RANK:Order by rank regarding to other sort criteria, saved as wms_sort_criteria.sequence_number 当Enable这个Rule之后,在wms_rules_b表也会有对应的记录产生,Sample Query: SELECT tl.name,tl.description,b.*FROM wms_rules_b b,wms_rules_tl tlWHERE b.rule_id = tl.rule_idAND b.rule_id=10533AND LANGUAGE = 'US'; Rule Workbench对应的Table是wms_selection_criteria_txn(picking rule assignments),可以使用下边的Query查询Rule Workbench的信息,Sample Query: Select * from wms_selection_criteria_txn where return_type_id=&rule_id; 并且会动态生成一个新的Package:WMS_RULE_<rule_id>,你可以通过下边的SQL查看WMS_RULE_<Rule_ID>的内容select text from dba_source where name='WMS_RULE_XXXX'; 可以从INV Log中找到,挑库的时候到底使用了那个Rule Id,比如,可以在日志中找到下边的信息[01-AUG-11 05:57:43] wms.plsql.WMS_Rule_PVT.Apply.l_rule_id: 10533如果Pick的过程报错,可以在日志中看到下边类似的信息: wms.plsql.WMS_Engine_PVT.Create_Suggestions.end G_SUGG_FAILURE_MESSAGE: WMS_PICK_NO_ROWS Reference: http://www.scribd.com/doc/60058250/WMS-RULE http://www.oracleug.com/user-guide/warehouse-management/overview-picking

Inventory Picking Rule(Oracle Apps R12) 用于在非WMS组织下,给Sales Order, WIP, OPM Manufacturing设置挑库规则 路径:Inventory > Setup > Rules > Picking Lot: You can sort by lot based on the following criteria: Lot Number...

Oracle R12采购接收流程(PR-PO-RCV-AP-Payment)

1.Requisitions 路径:Purchasing > Requisitions > Requisitions 请购单的类型只有两种,一种类型是Internal Requisition,用于内部组织间的物料申请;另外一种类型是Purchase Requisition,用于产生PO,对外部供应商的采购申请。 Tables 采购申请主要涉及以下三张表PO_REQUISITION_HEADERS_ALL,PO_REQUISITION_LINES_ALL,PO_REQ_DISTRIBUTIONS_ALL. header表和lines表通过REQUISITION_HEADER_ID关联,header <-1...*-> lines lines表和distributions表通过REQUISITION_LINE_ID相互关联,lines <-1...*-> distributions PO_REQUISITION_HEADERS_ALL PO_REQUISITION_HEADERS_ALL stores information about requisition headers. You need one row for each requisition header you create. Each row contains the requisition number, preparer, status, and description..REQUISITION_HEADER_ID is the unique system-generated requisition number. REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you use to identify the requisitionin forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle Purchasing generate requisition numbers for you..PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition information. PO_REQUISITION_HEADERS_ALL corresponds to the Header region of the Requisitions window..SEGMENT1 provides unique values for each row in the table in addition to REQUISITION_HEADER_ID. PO_REQUISITION_LINES PO_REQUISITION_LINES stores information about requisition lines.You need one row for each requisition line you create. Each row contains the line number, item number, item category, item description, need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line..LINE_LOCATION_ID identifies the purchase order shipment line on which you placed the requisition. LINE_LOCATION_ID is null if you have not placed the requisition line on a purchase order.BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement or catalog quotation line information for the requisition line.PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you exploded or multisourced this requisition line..PO_REQUISITION_LINES is one of three tables storing requisition information. This table corresponds to the Lines region of the Requisitions window PO_REQ_DISTRIBUTIONS_ALL PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting distributions associated with each requisition line. Each requisition line must have at least one accounting distribution.You need one row for each requisition distribution you create. Each row includes the Accounting Flexfield ID and requisition line quantity..PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition information. This table corresponds to the requisition Distributions window, accessible through the Requisitions window Distribution里的三个Account信息所对应的PO_REQ_DISTRIBUTIONS_ALL表里的字段 Debit(DR):CODE_COMBINATION_ID, Credit(CR):ACCRUAL_ACCOUNT_ID, Variance:VARIANCE_ACCOUNT_ID 这三个Account信息都可以在GL_CODE_COMBINATIONS_KFV中查找到。 Queries SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE segment1 = '14337'; SELECT line.* FROM PO_REQUISITION_HEADERS_ALL header,PO_REQUISITION_LINES_ALL lineWHERE header.REQUISITION_HEADER_ID = line.REQUISITION_HEADER_ID AND header.segment1 = '14337'; SELECT * FROM PO_REQUISITION_LINES_ALL line,PO_REQ_DISTRIBUTIONS_ALL disWHERE line.REQUISITION_LINE_ID = dis.REQUISITION_LINE_IDAND line.REQUISITION_HEADER_ID IN (SELECT REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE segment1 = '14337'); 2.AutoCreate AutoCreate用于把PR转换成PO 路径:Purchasing > AutoCreate 注:如果查不到PR,看看Buyer或者Ship-To是否输入正确,可以清掉这两个字段再查 这里使用Automatic模式来生成PO,接下来会弹出窗口,输入必要的采购订单的必要信息,比如供货方,币别... 3.PO AutoCreate之后,就生成了PO,这里还需要输入必要的Ship-To,Bill-To信息... Shipment信息 Distribution信息 保存PO,并审批。 Tables 和PO相关的表有:PO_HEADERS_ALL,PO_LINES_ALL,PO_LINE_LOCATIONS_ALL,PO_DISTRIBUTIONS_ALL. header表和lines表通过PO_HEADER_ID关联,header <-1...*-> lines lines表和locations表通过PO_LINE_ID关联,lines<-1...*-> locations locations表和distributions表通过LINE_LOCATION_ID相互关联,location<-1...*-> distributions PO_HEADERS_ALL PO_HEADERS_ALL contains header information for your purchasing documents.You need one row for each document you create. There are six types of documents that use PO_HEADERS_ALL:RFQs, Quotations, Standard purchase orders, Planned purchase orders, Blanket purchase orders, Contracts.Each row contains buyer information, supplier information, brief notes, foreign currency information, terms and conditions information, and the status of the document. .Oracle Purchasing uses this information to record information that is related to a complete document..PO_HEADER_ID is the unique system-generated primary key and is invisible to the user. SEGMENT1 is the system-assigned number you use to identify the document in forms and reports. Oracle Purchasing generates SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONT_ALL table if you choose to let Oracle Purchasing generate document numbers for you.SEGMENT1 is not unique for the entire table. Different document types can share the same numbers. You can uniquely identify a row in PO_HEADERS_ALL using ORG_ID, SEGMENT1, and TYPE_LOOKUP_CODE, or using PO_HEADER_ID..If APPROVED_FLAG is 'Y', the purchase order is approved. If your document type is a blanket purchase order, contract purchase order, RFQ, or quotation, Oracle Purchasing uses START_DATE and END_DATE to store the valid date range for the document. Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders..If you autocreate a quotation from an RFQ using the Copy Document window, Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID. Oracle Purchasing also uses FROM_TYPE_LOOKUP_CODE to indicate that you copied the quotation from an RFQ..Oracle Purchasing does not use SUMMARY_FLAG and ENABLED_FLAG. Because future versions of Oracle Purchasing will use them, SUMMARY_FLAG and ENABLED_FLAG should always be 'N' and 'Y' respectively..You enter document header information in the Header region of the Purchase Orders, RFQs, and Quotations windows. PO_LINES_ALL PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document. There are five document types that use lines:RFQsQuotationsStandard purchase ordersBlanket purchase ordersPlanned purchase ordersEach row includes the line number, the item number and category, unit, price, tax information, matching information, and quantity ordered for the line. Oracle Purchasing uses this informationto record and update item and price information for purchase orders,quotations, and RFQs..PO_LINE_ID is the unique system-generated line number invisible to the user. LINE_NUM is the number of the line on the purchase order. Oracle Purchasing uses CONTRACT_ID to reference a contract purchase order from a standard purchase order line. Oracle Purchasing uses ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT, QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned purchase order lines..The QUANTITY field stores the total quantity of all purchase order shipment lines (found in PO_LINE_LOCATIONS_ALL). PO_LINE_LOCATIONS_ALL 这个表记录了Shipment的相关信息 PO_LINE_LOCATIONS_ALL contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:RFQs .QuotationsStandard purchase ordersPlanned purchase ordersPlanned purchase order releasesBlanket purchase ordersBlanket purchase order releases.Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses this information to record delivery schedule information for purchase orders, and pricebreak information for blanket purchase orders, quotations and RFQs..PO_RELEASE_ID applies only to blanket purchase order release shipments. PO_RELEASE_ID identifies the release on which you placed this shipment..SOURCE_SHIPMENT_ID applies only to planned purchase order release shipments. It identifies the planned purchase order shipment you chose to release from..PRICE_OVERRIDE always equals the purchase order line price for standard purchase order shipments. For blanket and planned purchase orders, PRICE_OVERRIDE depends on the values of theALLOW_PRICE_OVERRIDE_FLAG and NOT_TO_EXCEED_PRICE in the corresponding row in PO_LINES_ALL:.If ALLOW_PRICE_OVERRIDE_FLAG is 'N', then PRICE_OVERRIDE equals UNIT_PRICE in PO_LINES_ALL. .If ALLOW_PRICE_OVERRIDE_FLAG is 'Y', the PRICE_OVERRIDE can take any value that is smaller than NOT_TO_EXCEED_PRICE in PO_LINES_ALL..The QUANTITY field corresponds to the total quantity ordered on all purchase order distribution lines (found in PO_DISTRIBUTIONS_ALL)..Oracle Purchasing automatically updates QUANTITY_RECEIVED, QUANTITY_ACCEPTED, and QUANTITY_REJECTED when you receive, return, or inspect goods or services. Oracle Payables automatically updates QUANTITY_BILLED when you match an invoice with a purchase order shipment. Oracle Purchasing automatically updates QUANTITY_CANCELLED when you cancel a purchase order shipment..Oracle Purchasing sets APPROVED_FLAG to 'Y' when you approve the corresponding purchase order if there are no problems associated with the shipment and its related distributions..Oracle Purchasing sets ENCUMBERED_FLAG to 'Y' and enters the ENCUMBERED_DATE when you approve a purchase order if you use encumbrance. PO_DISTRIBUTIONS_ALL 这个表记录了distributions的相关信息 PO_DISTRIBUTIONS_ALL contains accounting distribution information for a purchase order shipment line. You need one row for each distribution line you attach to a purchase order shipment. There are four types of documents using distributions in Oracle Purchasing:Standard Purchase OrdersPlanned Purchase OrdersPlanned Purchase Order ReleasesBlanket Purchase Order Releases Each row includes the destination type, requestor ID, quantity ordered and deliver-to location for the distribution..Oracle Purchasing uses this information to record accounting and requisition information for purchase orders and releases..PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order and release information..Some columns in PO_DISTRIBUTIONS_ALL contain information only if certain conditions exist:.If you autocreate this accounting distribution from a requisition, REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution you copy on the purchase order.. If you use a foreign currency on your purchase order,Oracle Purchasing stores currency conversion information in RATE and RATE_DATE..If you use encumbrance, GL_ENCUMBERED_DATE and GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle Purchasing uses to create journal entries in Oracle General Ledger. . If you do not autocreate the purchase order from online requisitions, REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain the requisition number and requisition line number of the corresponding paper requisition. These two columns are not foreign keys to another table..If the distribution corresponds to a blanket purchase order release, PO_RELEASE_ID identifies this release..If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned purchase order release..If you cancel the distribution, Oracle Purchasing automatically updates QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also enters UNENCUMBERED_AMOUNT if you use encumbrance.. You enter distribution information in the Distributions window, accessible through the Purchase Orders and Releases windows. Distribution里的三个Account信息所对应的PO_DISTRIBUTIONS_ALL表里的字段 Debit(DR):CODE_COMBINATION_ID, Credit(CR):ACCRUAL_ACCOUNT_ID, Variance:VARIANCE_ACCOUNT_ID 这三个Account信息都可以在GL_CODE_COMBINATIONS_KFV中查找到。 Queries SELECT * FROM PO_HEADERS_ALL WHERE segment1 = '6151'; SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE segment1 = '6151'); SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE segment1 = '6151'); SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE segment1 = '6151'); PR-PO整体TABLE关系图 PO-PR通过SQL如何互查 --Query PO information base on PR number SELECT POH.SEGMENT1,PLA.*  FROM   PO_HEADERS_ALL POH,         PO_LINES_ALL PLA,         PO_LINE_LOCATIONS_ALL PLL  WHERE  POH.PO_HEADER_ID = PLL.PO_HEADER_ID  AND    POH.PO_HEADER_ID = PLA.PO_HEADER_ID  AND    PLL.LINE_LOCATION_ID IN                                 (SELECT DISTINCT LINE_LOCATION_ID   FROM             PO_REQUISITION_LINES_ALL   WHERE            REQUISITION_HEADER_ID =                                                   (SELECT DISTINCT REQUISITION_HEADER_ID   FROM             PO_REQUISITION_HEADERS_ALL   WHERE            SEGMENT1 = '&PR_NUMBER'                                                  )                                  );   --Query PR information base on PO number/PO_HEADER_ID                               SELECT *  FROM   PO_REQUISITION_HEADERS_ALL PRHA,         PO_REQUISITION_LINES_ALL PRLA  ,         PO_REQ_DISTRIBUTIONS_ALL PRDA  WHERE  PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID  AND    PRDA.REQUISITION_LINE_ID   = PRLA.REQUISITION_LINE_ID  AND    PRDA.DISTRIBUTION_ID IN        ( SELECT PDA.REQ_DISTRIBUTION_ID   FROM    PO_HEADERS_ALL PHA,                 PO_DISTRIBUTIONS_ALL PDA   WHERE   PHA.PO_HEADER_ID = PDA.PO_HEADER_ID   AND     PHA.SEGMENT1     = '887'        ) --最好用PO_HEADER_ID来查PR,因为用PO Number来查,可能会出现多个结果                        PO Approve之后所涉及到的其他表 MTL_SUPPLY 一条供货信息在MTL_SUPPLY中就生成了,SUPPLY_TYPE_CODE=PO,SUPPLY_SOURCE_ID=PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID,此外表里还记录了PO单据相关的信息。Records in MTL_SUPPLY are created every time you approve a requisition or a PO or create an intransit shipment.  One record of REQ type will be created for one requisition line when the requisition is approved.(SUPPLY_TYPE_CODE=REQ) One record of PO type will be created per PO distribution(SUPPLY_TYPE_CODE=PO) when a PO is approved and one record per shipment line will be created when a shipment is created. RCV_SUPPLY,rcv_transactions_interface,rcv_headers_interface这个时候还没有数据 4.Receipt&Delivery 供应商会发货到我门指定组织,接下来我们要做接收和投递入库 Receipt Receipt的后台逻辑,实际上是插入一条记录到rcv_transactions_interface里,然后调用Receiving Transaction Processor来处理RTI里的数据,如果报错,错误信息会写po_interface_errors表中。 Receipt成功运行之后,可以看到RTI的数据被删除,RCV的相关数据也会生成在rcv_shipment_headers,rcv_shipment_lines,rcv_transactions,rcv_supply表中,rcv_shipment_headers记录了Receipt Header的信息,rcv_shipment_lines记录了Receipt的Line的信息,RT为接收的历史表,RCV_SUPPLY标识了还有多少待接收数量(这个时候SUPPLY_TYPE_CODE为RECEIVING状态)。 RSL的SHIPMENT_LINE_STATUS_CODE为:FULLY RECEIVED 再看MTL_SUPPLY,SUPPLY_TYPE_CODE也由PO更改为了RECEIVING。 RCV_SUPPLY和MTL_SUPPLY的作用类似,都是提供SUPPLY的信息,由于历史原因,才让两个表共存,RCV_SUPPLY的信息更加详细。 Delivery 路径:Inventory > Transactions > Receiving > Receiving Transaction 和Receipt一样,Delivery后台逻辑也会插入一条记录到rcv_transactions_interface里(TRANSACTION_TYPE=DELIVER,PROCESSING_STATUS_CODE:PENDING),然后调用Receiving Transaction Processor来处理RTI里的数据,如果报错,错误信息会写po_interface_errors表中。 RT有两条Transaction,一条是TRANSACTION_TYPE为RECEIVE,另外一条TRANSACTION_TYPE为DELIVER。 RSL的SHIPMENT_LINE_STATUS_CODE仍为:FULLY RECEIVED。 完全Delivery之后,RCV_SUPPLY和MTL_SUPPLY就会被删除。 另外如果查看Material Transaction,会发现有一条PO Receipt的Transaction生成了。 System Flow Pre-requisites:At least one approved shipment which has the ship-to-organization same as the active organization. This means that a PO supply exists in mtl_supply. When you create a standard receipt against a PO: 1.     Records are inserted into RCV_TRANSACTIONS_INTERFACE withprocessing_status_code andtransaction_status_code as‘PENDING’ and transaction_type of‘RECEIVE’. 2.     Shipment header is created in RCV_SHIPMENT_HEADERS. 3.     Receiving Transaction Processor is called. The ReceivingTransaction Processor does the following: 1.     Unlocks all previous transactions from a failedoperation so that they can be processed now. 2.     Sets the processing_status_code inrcv_transactions_interface to ‘RUNNING’ (only in case of BATCH and IMMEDIATE processing modes). 3.     Calls the actual processing function - rvtptcontrol. In rvtptcontrolwe initialize the transaction information structure, do the validations and perform the actual actions to complete the transactions. Here according to the transaction type we call different functions to do the actual processing. 1.     Creates Shipment Line in rcv_shipment_lines. Create transaction history i.e. create records inrcv_transactions. In case the item is under lot or serial control then create records inrcv_lot_transactions andrcv_serial_transactions. 2.     Maintain supply information. This means updating or deleting the PO supply, create RECEIVING/DELIVER supply inmtl_supply. Creatercv_supply.In case the item is under serial control then maintainrcv_serials_supply. 3.     Update the RECEIVED/DELIVERED quantities in PO Distributions. 4.     Close the PO for receiving if applicable. 5.     Create the Inventory Interface record i.e. insert a record into mtl_material_transactions_temp and call the inventory function inltpu() which completes the delivery of the item into Inventory. (rvtii.lpc) 6.     Receipt Accruals. Flow for Receiving Transaction Processor For Receipt Transactions 1)  CREATESHIPMENT HEADER       ==> rvtshheader 2)CREATE SHIPMENT LINE       ==> rvtshline 3)RECEIVE TRX             ==> rvtthrec 4)DELIVER TRX             ==> rvtthdel 5)MAINTAIN SHIPMENT RECEIPT QTY ==> rvtuqsreceive 6)MAINTAIN PO RECEIPT QTY       ==>rvtuqpreceive 7)MAINTAIN REQ RECEIPT QTY      ==>rvtuqrreceive 8)MAINTAIN PO DELIVERY QTY      ==>rvtuqpdelivered 9)MAINTAIN REQ DELIVERY QTY     ==>rvtuqrdelivered 10)CLOSE PO FOR RECEIVING       ==>rvtclose_for_receiving 11)INV INTERFACE TRX         ==> rvtiinv_trx 12)OUTSIDE OPS DELIVERY          ==> rvtooperation 13)ACCRUE RECEIPT             ==>rvtacar_accrue_receipt 14)PRINT DELIVERY TICKET     ==>rvtpdelivery_ticket 15)PRINT CHARGE NOTICE           ==> rvtpdcharge_notice For Delivery Transactions 1) DELIVER TRX                ==> rvtthdel 2) MAINTAIN REQ DELIVERY QTY        ==>rvtuqrdelivered 3) MAINTAIN PO DELIVERY QTY     ==>rvtuqpdelivered 4) CLOSE PO FOR RECEIVING       ==>rvtclose_for_receiving 5) INV INTERFACE TRX         ==> rvtiinv_trx 6) OUTSIDE OPS DELIVERY          ==> rvtooperation 7) ACCRUE RECEIPT             ==>rvtacar_accrue_receipt 8) PRINT CHARGE NOTICE           ==> rvtpdcharge_notice rcv_shipment_lines RCV_TRANSACTIONS 5.Invoice 路径:Account Payable > Invoices > Entry > Invoices Match Find Match Actions > Validate 验证之后,Invoice的状态应该变为:Validated 可以通过下边SQL查看发票信息 select * from ap_invoices_all where invoice_id in(select invoice_id from ap_invoice_distributions_all where po_distribution_id in( select po_distribution_id from po_distributions_all where po_header_id =173317)); 5.Create AccountingInvoice > Action > Create Accounting 6.Payment 发票流程走完后就是付款了 路径:Account Payable > Payment 本例的RCV11i ,PR-PO-RCV.zip 转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7770412 ===EOF===

1.Requisitions 路径:Purchasing > Requisitions > Requisitions 请购单的类型只有两种,一种类型是Internal Requisition,用于内部组织间的物料申请;另外一种类型是Purchase Requisition,用于产生PO,对外部供应商的采购申请。 Tables 采购申请主要涉及以下三张表PO_REQUISITION_HEADERS_ALL,P...

Oracle R12 多组织访问的控制 - MOAC(Multi-Org Access Control)

什么是MOAC MOAC(Multi-Org Access Control)为多组织访问控制,是Oracle EBS R12的重要新功能,它可以实现在一个Responsibility下对多个Operation Unit(OU)进行操作。MOAC允许用户在不切换responsibility的情况下,在一个responsibility下处理多个OU组织的事务。 User --> Responsibilities --> Single Operation Unit Mode  /  Multiple Operation Unit Mode 一个应用场景: 某集团公司下边主要分为三个区域(北美,欧洲,亚太),亚太区你是一采购部经理,负责所有七个Operation Unit。这种情况下,系统管理员可以创建一个security profile,这个security profile设置成可以访问这七个亚太组织,并把这个security profile赋予到你的responsibility下,这样你就能在同一个职责下访问这七个OU了,就不用不停地切换职责来访问不同OU了。 另外如果你要经常处理中国OU下的事务,那么你可以设置Profile:MO: Default Operating Unit到中国,那么业务默认的OU就是中国了。 MOAC相关的Profile 有三个主要的Profile MO: Security Profile :provides access to multiple operating units from a single responsibility.If the MO: Security Profile is set, then the MO: Operating Unit profile will be ignored. MO: Default Operating Unit :If you set the MO: Security Profile profile option, you can also set an operating unit as the default operating unit using the MO: Default Operating Unit profile option. This is useful when you transact in multiple operating unit but frequently transact in one operating unit. MO:  Operating Unit :MO: Operating Unit profile option only provides access to one operating unit. MOAC profiles rules  1) If the profile  option “MO: Security Profile” is not set, then “MO:  Operating Unit”  value is used  as the default Operating Unit even if “MO:  Default Operating Unit” profile is set to a different value.  2) If the profile option “MO: Security Profile” is set and gives access to only one Operating Unit, the default Operating Unit will return this  value even if “MO: Default Operating Unit” is set to a different  value.  3) If the profile option “MO: Security  Profile” is set and gives access to  multiple Operating Units :      -  If the profile value “MO: Default Operating Unit”  is set,  it  is validated  against the list of Operating Units in “MO: Security Profile”.         + If  the Operating Unit is included in the security profile then it is  returned as the default value.          + Else there is no defaulted Operating Unit .      -  If the Profile Option “MO: Default Operating Unit”  is not  set,  then there is zero (no)  default Operating Unit. 单组织的案例 单组织即一个Responsibility只访问一个OU,设置情况大体如下,对于职责:Manufacturing and Distribution Manager, MO: Operating Unit=Vision Operations MO: Security Profile=空 这样我们在使用职责:Manufacturing and Distribution Manager的时候,就只能访问到Vision Operations的数据,比如创建的PO,这个PO只能处于OU:Vision Operations下,另外所选取的供货商也只能是Vision Operations下的。 多组织的案例 首先定义一个Security Profile,路径:HRMS Super User Responsibility > Security > Define Security Profile,Business Group:Vision Corporation,'Security Type'选择'Secure organizations by organization hierarchy and/or organization list',并且我们把在Organization Name列表中添加三个OU:Vision Operations,Vision Corporation ,Vision Services. 保存定义的Security Profile,然后在HR职责下,运行“Security List Maintenance” program,“Generate lists for”= One Named Security Profile ,Security Profile是刚刚定义的'PTIAN_SECURITY_PROFILE'。 Security List Maintenance的作用是让你定义的Security Profile生效,能够设置到Profile "MO: Security Profile"(The Security List Maintenance concurrent program must be run each time you add or change Security Profiles.) System Admin职责下,Profile > System,设置MO: Security Profile为刚刚定义的'PTIAN_SECURITY_PROFILE' 再切回Manufacturing and Distribution Manager职责,打开PO,这个时候,你就可以不切换职责的情况,定义三个OU下的采购订单了。 MOAC的实现原理-VPD技术 MOAC的实现是通过Oracle数据库的VPD(Virtual Private Database)技术来实现的。VPD技术提供了数据库对象(表,同义词,视图)行级别访问的控制。使用VPD技术可以有效地限制用户获取数据的范围。 Secooler 的一篇文章 使用Oracle VPD(Virtual Private Database)限制用户获取数据的范围  讲VPD,里边的例子非常容易理解. --1.Create Data create table t (x number);   insert into t values (1);   insert into t values (2);   insert into t values (10001);   insert into t values (10002);   commit;   select * from t;   output:   1   2   10001   10002   --2.Create VPD FUNCTION CREATE OR REPLACE FUNCTION f_limited_query_t (s_schema IN VARCHAR2,                                                 s_object IN VARCHAR2)   RETURN VARCHAR2   AS BEGIN RETURN 'X <= 10000';   END;   --3.Register VPD Policy. BEGIN    DBMS_RLS.add_policy (object_schema   => 'APPS',                           object_name     => 'T',                           policy_name     => 'POLICY_LIMITED_QUERY_T',                           function_schema => 'APPS',                           policy_function => 'F_LIMITED_QUERY_T');   END;   select * from t;   output:   1   2   对于上边例子,我们对表T使用了VPD技术,引入了表限制Function f_limited_query_t,这样我们通过function限制了对表的查询,查询结果只返回小于10000的数字。如何查看我们是否对某张表使用了VPD技术 SELECT * FROM DBA_POLICIES WHERE object_name = 'T'; 查询结果中,其中Pakcage + Function就是我们对于表所加的限制。 那么Oracle EBS是如何使用VPD技术来实现多组织的 R12里,以PO表为例,PO_HEADERS_ALL是基础表(PO/APPS Scehma),PO_HEADERS是PO_HEADERS_ALL对应的Synonym对象(Apps Schema),我们对PO_HEADERS应用VPD技术.MO_GLOBAL-Dive into R12 Multi Org Design 有较为详细的说明, In pre Release 12, you would have had following methodology for PO_HEADERS_ALLa. A table is created in PO Schema, named PO_HEADERS_ALLb. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALLc. Create a view PO_HEADERS in APPS schema, as "select * from po_headers_all where org_id=client_info"But now in R12, following will happena. A table is created in PO Schema, named PO_HEADERS_ALLb. A synonym named PO_HEADERS_ALL is created in APPS schema, referring to PO.PO_HEADERS_ALLc. Another synonym named PO_HEADERS is created in APPS, referring to PO_HEADERS_ALLd. A Row Level security is applied to PO_HEADERS, using package function MO_GLOBAL.ORG_SECURITY.This can be double-checked by running SQL select * from all_policies where object_name='PO_HEADERS'e. The effect of this policy is that,whenever you access PO_HEADERS, Oracle RLS will dynamically append WHERE CLAUSE similar to belowSELECT * FROM PO_HEADERS WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id = org_id) 看下下边Query的输出 SELECT * FROM DBA_POLICIES WHERE object_name = 'PO_HEADERS'; 可以看到,我们对表PO_HEADERS加了MO_GLOBAL.ORG_SECURITY限制,MO_GLOBAL.ORG_SECURITY的作用实际上就是根据你关于MOAC Profiles的设置,然后转换为相应Where条件(组织过滤),再进行查询。 对于VPD表的查询 对于VPD表,简单的查询一般是不返回记录的,如果想查到记录,需要设置一下上下文先 --普通查询VPD表 select * from PO_HEADERS;--No Output --Single OU Mode BEGIN  execute mo_global.set_policy_context('S',204); --204为ORG_ID,S表示Single Org ContextEND;select * from PO_HEADERS;--会输出OU:204下边的所有PO --Multiple OU Mode(simulate login to a specific responsibility) a. Call fnd_global.apps_initialize(userid,resp_id,resp_appl_id); b. call MO_GLOBAL.INIT(p_appl_short_name);This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access. c.select * from po_headers MOAC API What is the purpose of MO_GLOBAL.ORG_SECURITY? The purpose of Row-Level-Security is to hide certain data[based on some conditions]. RLS does so by appending a where clause to the secured object.1. MO_GLOBAL.ORG_SECURITY is a function that returns a predicate for the WHERE CLAUSE2. The where clause will be appended to Table/Synonym/View for which Multi Org Row Level security is enabled What is the purpose of MO_GLOBAL.SET_POLICY_CONTEXT ? This procedure has two parameters    p_access_mode          Pass a value "S" in case you want your current session to work against Single ORG_ID          Pass a value of "M" in case you want your current session to work against multiple ORG_ID's    p_org_id          Only applicable if p_access_mode is passed value of "S" MOAC相关的查询语句 Security Profile Definiation SELECT *   FROM per_security_profiles   WHERE security_profile_name = 'PTIAN_SECURITY_PROFILE';   check Organization which are related to a profile select PPO.*   from PER_SECURITY_PROFILES PPR,        PER_SECURITY_ORGANIZATIONS PPO   where PPR.security_profile_id = PPO.security_profile_id   and security_profile_name like '%PTIAN_SECURITY_PROFILE%';   List Profile Option Values For All Levels set long 10000     set pagesize 500     set linesize 160     column SHORT_NAME format a30     column NAME format a40     column LEVEL_SET format a15     column CONTEXT format a30     column VALUE format a40     select p.profile_option_name SHORT_NAME,     n.user_profile_option_name NAME,     decode(v.level_id,     10001, 'Site',     10002, 'Application',     10003, 'Responsibility',     10004, 'User',     10005, 'Server',     10006, 'Org',     10007, decode(to_char(v.level_value2), '-1', 'Responsibility',     decode(to_char(v.level_value), '-1', 'Server',     'Server+Resp')),     'UnDef') LEVEL_SET,     decode(to_char(v.level_id),     '10001', '',     '10002', app.application_short_name,     '10003', rsp.responsibility_key,     '10004', usr.user_name,     '10005', svr.node_name,     '10006', org.name,     '10007', decode(to_char(v.level_value2), '-1', rsp.responsibility_key,     decode(to_char(v.level_value), '-1',     (select node_name from fnd_nodes     where node_id = v.level_value2),     (select node_name from fnd_nodes     where node_id = v.level_value2)||'-'||rsp.responsibility_key)),     'UnDef') "CONTEXT",     v.profile_option_value VALUE     from fnd_profile_options p,     fnd_profile_option_values v,     fnd_profile_options_tl n,     fnd_user usr,     fnd_application app,     fnd_responsibility rsp,     fnd_nodes svr,     hr_operating_units org     where p.profile_option_id = v.profile_option_id (+)     and p.profile_option_name = n.profile_option_name     and upper(p.profile_option_name) in ( select profile_option_name     from fnd_profile_options_tl     where upper(user_profile_option_name)     like upper('%MO: Security Profile%'))     and usr.user_id (+) = v.level_value     and rsp.application_id (+) = v.level_value_application_id     and rsp.responsibility_id (+) = v.level_value     and app.application_id (+) = v.level_value     and svr.node_id (+) = v.level_value     and org.organization_id (+) = v.level_value    order BY  short_name, user_profile_option_name, level_id, level_set;     支持MOAC功能的Form开发步骤 这部分摘自:http://bbs.erp100.com/thread-103395-1-1.html 在R12版本中,OU的控制采取了MOAC的方式,使用户的操作得到了改善。而如果客户化的Form能够支持MOAC的功能,需要在界面上提供当前用户可以选择的OU字段供用户选择。功能展示如下图: 这样在Form的开发过程中需要如下的开发步骤:1,PRE-FORM 触发器初始化MOAC配置环境  添加如下代码:  MO_GLOBAL.init(‘ONT’);  — global.mo_ou_count  — global.mo_default_org_id  — global.mo_default_ou_name  IF l_default_org_id IS NOT NULL THEN — default org id not null    MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,l_default_org_id);  ELSE    MO_GLOBAL.SET_POLICY_CONTEXT(‘M’,null);  END IF; — default org id not null    这段代码的作用是根据预制文件的设置,初始化OU的信息,将用户可以访问的OU信息插入到mo_glob_org_access_tmp表中,  同时将默认的OU ID、OU Name和OU Count分别写到global.mo_default_org_id, global.mo_default_org_id, global.mo_default_ou_name  具体细节可以查看数据库包:mo_global2,WHEN-CREATE-RECORD触发器中拷贝OU默认值  在此触发器中将默认的OU ID和OU Name拷贝给Form界面上对应的自动,实现创建记录的时候默认带出默认OU信息。  copy(name_in(‘global.mo_default_org_id’),’’);  copy(name_in(‘global.mo_default_ou_name’),’’);3,创建OU的LOV  Form界面上的OU 名称字段创建一个LOV,LOV对应记录组的SQL语句如下:  SELECT hr.organization_id organization_id, hr.NAME organization_name    FROM hr_operating_units hr   WHERE mo_global.check_access(hr.organization_id) = ‘Y’   ORDER BY organization_name其它没有特殊的步骤。 MindMap 参考: Oracle Applications Multiple Organizations Implementation Guide EBS R12 MOAC(Multi-Org Access Control)原理探索 MO_GLOBAL-Dive into R12 Multi Org Design FAQ - Multiple Organizations Architechure (Multi-Org) (Doc ID 165042.1) •Note: 420787.1 Oracle Applications Multiple Organizations Access Control for Custom Code •Note: 462383.1 SQL Queries and Multi-Org Architecture in Release 12 •Note: 396750.1 Oracle Applications Multiple Organizations Release 12 Roadmap Document Note 745420.1 -How To Setup And Check MO / MOAC Setup In APPS Instance At R12 Level - Precedence of MO - MOAC Profile OptionsBest Practices for Securing the E-Business Suite [ID 189367.1]Best Practices For Securing Oracle E-Business Suite Release 12 [ID 403537.1] Understanding and Using HRMS Security in Oracle HRMS [ID 394083.1] Security List Maintenance for All Profiles Is Excluding Employees [ID 755410.1] Effect Of Security List Maintenance Concurrent Request within the Oracle HRMS Module [ID 457629.1] 转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7774715 ===EOF===

什么是MOAC MOAC(Multi-Org Access Control)为多组织访问控制,是Oracle EBS R12的重要新功能,它可以实现在一个Responsibility下对多个Operation Unit(OU)进行操作。MOAC允许用户在不切换responsibility的情况下,在一个responsibility下处理多个OU组织的事 务。 User -->...

Query_Find Form开发入门

1. 打开Template.fmb,并另存为一个名字(QUERYFIND.fmb) 2.从Window,Canvas,Data Block中删除BLOCKNAME,从Data Block中删除DETAILBLOCK 3.创建一个Window(DEPT_WINDOW),subclass选择WINDOW 4.Pre-Form trigger中,把DEPT_WINDOW设置为第一个显示的窗口 Program Unit:APP_CUSTOM中,设置关闭窗口的行为 5.定义一个Canvas(DEPT_CANVAS),subclass设置为CANVAS 6.通过Wizard创建Data block:DEPT,在Table中使用DEPT这个部门表 并把这个Block绑定到刚创建的canvas:DEPT_CANVAS上 把block:DEPT的subclass设置为BLOCK ,并且把block下边三个字段的subclass都设置为TEXT_ITEM. 7.加载APPSTAND.fmb 把APPSTAND.fmb下的Object Groups中的Query_Find对象组拖到Demo Form中的Object Groups中(选择Copy), 这样window,canvas,datablock有了QUERY_FIND标准对象 然后分别定义QUERY_FIND window,canvas,block的subclass。 打开QUERY_FIND block,可以看到默认会有三个button自动带入。 修改FIND,New按钮的When-Button-Pressed代码,把block:DEPT,替换进去 8.在Query_Find block中增加一个TEXT_ITEM字段,用于在查询时输入部门编码,并记得把这个字段绑定到Query_Find canvas中去。 9.然后把APPSTAND.fmb中的QUERY_FIND trigger拖拽到DEPT block  level(Copy) 把原有的代码改成APP_FIND.QUERY_FIND(‘DEPT_WINDOW’, ‘QUERY_FIND’,'QUERY_FIND’); 补充:Syntax: APP_FIND.QUERY_FIND(<results block window>,<Find window>,<Find window block>); 10.接下来,在DEPT block level创建一个Pre-Query trigger,代码如下: IF :parameter.G_query_find = 'TRUE' THEN   COPY(:QUERY_FIND.DEPT_NO,'DEPT.DEPTNO');  :parameter.G_query_find := 'FALSE';END IF; 补充: :parameter.G_query_find为True表示,当前查询为Query Find所触发的。 Pre-Query Syntax: IF :parameter.G_query_find = TRUE THEN COPY (<find Window field>,<results field>); :parameter.G_query_find := FALSE; END IF; 11.Form:QUERYFIND的属性中,设置First Navigation Data Block为Query_Find QUERY_FIND block的Next  Navigation Data Block设置为DEPT 12.Compile一把,看看有没有错误。没有错误的话,就可以参考 如何注册一个新的form到EBS系统 把文件注册到系统中了。 13.进入系统,查看功能

1. 打开Template.fmb,并另存为一个名字(QUERYFIND.fmb) 2.从Window,Canvas,Data Block中删除BLOCKNAME,从Data Block中删除DETAILBLOCK 3.创建一个Window(DEPT_WINDOW),subclass选择WINDOW 4.Pre-Form trigger中,把DEPT_WINDOW设置为第一个显示的窗口 Program...

Dual UOM in Oracle EBS

Dual UOM是随着Process Manufacturing(流程制造)的加入,在Oracle EBS R12中新加的特性。对于Dual UOM的物料,查库存时,Workbench不仅会显示主计量单位(Primary UOM),同时也会显示次计量单位(Secondary UOM)。Dual UOM适用于特定的行业,比如化工,食品...,具体的例子,以牛肉为例,在欧美常常以磅(Pounds)作为牛肉主计量单位,但中国更习惯使用公斤来作为主计量单位。对于这样的物料就有必要启用双单位控制。上图中红色部分即为计量单位相关的物料设置。Primary:主计量单位,所有的物料都需要有一个主计量单位。后台的逻辑运算,报表等也都是基于主计量单位来的。Tracking:这个就是控制物料是否为双单位的属性。 (组织级别属性)Primary :库存只显示主计量单位Primary & Secondary:  库存会同时显示主计量单位以及次计量单位Pricing:这个属性控制,在计价的时候,价格是基于主计量单位还是次计量单位。(组织级别属性)如果这个值为Secondary,发运时(Ship Confirmation),系统会在订单行上重新算价格。Secondary:如果Tracking是Primary & Secondary(Dual UOM Item)或者Pricing为Secondary,那么你在这个数量里设置次计量单位。(组织级别属性)Defaulting: Fixed The system stores inventory in both the primary and secondary units of measure. You can enter an item quantity in one unit of measure, and the system converts the quantity to the secondary unit of measure and displays both quantities.Default The system stores inventory in both the primary and secondary units of measure. You can enter an item quantity in one unit of measure, and the system converts the quantity to the second unit of measure and displays both quantities. You can change the quantity in the secondary unit of measure, without changing the quantity in the primary unit of measure.No Default The system stores inventory in both the primary and secondary units of measure. Use this option when the default conversion between the two units of measure is usually not the same. The system does not automatically display in the secondary unit of measure when you specify the quantity for the primary unit of measure. You manually enter the quantity of the secondary unit of measure before you process a transaction. The secondary quantity can fluctuate from the default conversion by the factors that you specify in the Deviation + and Deviation - attributes.Deviation Factor +You can enter acceptable deviations as decimal values. This attribute is assigned at the organization level. This produces a plus or minus tolerance of acceptability. For example, if the allowable transaction quantity deviation for the item is 10 percent higherthan the established conversion, you would enter 10 in this field.Deviation Factor -You can enter acceptable deviations as decimal values. This attribute is assigned at the organization level. This produces a plus or minus tolerance of acceptability. For example, if the allowable transaction quantity deviation for the item is 10 percent lowerthan the established conversion, you would enter 10 in this field.事务处理对于beef这个物料,defaulting我们设置成了'No Default',所以需要手工输入次计量单位数量,100 Lbs(即Pounds) = 45.35924 KG,我这里就输入45.35924到Secondary Qty里。然后修改Secondary Qty到50,提示“The quantity entered is higher than the allowed deviation for the item. Please re-enter.”,这是因为,(50 - 45.35924) / 45.35924 = 10.2%,超过了10%误差(Deviation)。如果把Secondary qty修改成47就不会报上边的提示。这是因为,(47 - 45.35924) / 45.35924 < 10% 误差(Deviation)。查询库存现有量报表中的UOM但是对于很多标准report,现在还只是显示Primary UOM/Qty,不知道以后会不会把Secondary UOM/Qty加到报表中去。Inventory Value ReportInventory Quantity Summary Report

Dual UOM是随着Process Manufacturing(流程制造)的加入,在Oracle EBS R12中新加的特性。对于Dual UOM的物料,查库存时,Workbench不仅会显示主计量单位(Primary UOM),同时也会显示次计量单位(Secondary UOM)。 Dual UOM适用于特定的行业,比如化工,食品...,具体的例子,以牛肉为例,在欧美常常以磅(Pounds)作为牛...

Step by Step to Define a BI Publisher Report

这里演示一下,开发一个最简单BI Publisher Report的流程,下图为开发的基本流程.Data Template(.xml) 作为Data Source,用于数据源的定义,以及取数的SQL语句;Template(.rtf) 用于定义输出展示(Layout); (RTF:Rich Text Format)1.首先创建一个Demo数据CREATE TABLE demo_products  (  product_code   NUMBER,     product_name   VARCHAR2 (100));   INSERT INTO demo_products   VALUES (569, 'Oracle Cost Management');   INSERT INTO demo_products   VALUES (401, 'Oracle Inventory Management');   COMMIT;  2.Define Data TemplateDate Template为XML文件,基本的格式如下图<parameters><parameter>为传入参数项<dataQuery><sqlStatement>为Report所执行的Query语句<dataTrigger>用于指定beforeReport或afterReport,调用那个Function/Procedure<dataStructure>为XML输出的结构这里创建我们的Data Template文件<?xml version="1.0" encoding="UTF-8"?> <dataTemplate name="demoProductsDT" description="Demo Products Details" version="1.0"> <parameters> <parameter name="p_product_id" datatype="number"/> </parameters> <dataQuery> <sqlStatement name="DQ"> <![CDATA[ SELECT product_code, product_name FROM demo_products                             WHERE product_code = NVL(:p_product_id,product_code) ]]> </sqlStatement> </dataQuery> <dataStructure> <group name="G_DP" source="DQ"> <element name="PRODUCT_CODE" value="product_code"/> <element name="PRODUCT_NAME" value="product_name"/> </group> </dataStructure> </dataTemplate> 最后保存成ABC.xml3.Create Data Definition & Associate with Data TemplateXML Publisher Administrator -> Data Definitions -> Create Data Definition这里的Code会被后边定义的Concurrent Program用到.接下来,Data Template  'Add File',选择我们第二步定义的XML Data Template.4.接下来切换到Application Developer职责下,定义一个新的Concurrent Program注意:1. Output format应该XML2. Short Name和之前定义Data Definition中的Code完全一样What is XDODTEXE used in the Executable section of Concurrent Program?XDODTEXE is a BI Publisher Data Template Executable. The purpose of this executable is to identify data template file (.xml) and execute the data template to generate the raw xml data, that later can be used by BI Publisher formatting engine to format as as per the layout (RTF, PDF etc).This executable will be used by all the BI Publisher reports (Concurrent Program) which are using Data Template to generate the xml data. 5.Concurrent Program ParameterNote:Token is p_product_id. This is the bind parameter we have used in date template. For every bind parameter used in the data template, we have to define parameter in the concurrent program.6.把这个新定义的Concurrent Program绑定到对应Request Group里切换到System Admin职责,Security > Responsibility > Request,把刚定义好的Program绑定到一个Request Group里7.运行Concurrent Program8.Output保存上面这个Output成XML格式,我们还需要基于这个xml output做RTF template,RTF Template为了让报表输出更友好,更灵活的展示,而不是只给用户一个XML结果。9.下载安装XML Publisher Desktoprefer:http://blog.csdn.net/pan_tian/article/details/828354310.Define the RTF Template using the Generated Data XML加载项 > 导入XML数据 导入第8步的output的xml文件然后“插入” > “表向导”掠过定义模板的几步,最终RTF模板文件样子大概如下,11.Registering the Template with BI PublisherXML Publisher Administrator -> Templates -> Create Template12.Run the concurrent program to see the output重新切换到Concurrent Program的定义Form上,更改输出的文件类型,然后文件输出的格式也就随之发生改变

这里演示一下,开发一个最简单BI Publisher Report的流程,下图为开发的基本流程. Data Template(.xml) 作为Data Source,用于数据源的定义,以及取数的SQL语句; Template(.rtf) 用于定义输出展示(Layout); (RTF:Rich Text Format) 1.首先创建一个Demo数据 CREATE TABLE demo_products   ( ...

JDeveloper Setup for OA Framework

Download JDeveloper 参考下边Note来下载JDev,不同版本的EBS需要下载不同版本的JDevNote 416708.1 - OA Framework - How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x ATG Release 12 VersionJDeveloper 10g Patch12.0.6  (patch 6728000 or patch 7237006)Patch 7523554 10G Jdeveloper With OA Extension ARU for R12 RUP612.1.2 (patch 7303033 or patch 7651091)Patch 9172975 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.1.212.1.3 (patch 9239090 or patch 8919491)Patch 9879989 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.1.3不同版本的JDEV包含不同的FND包,所以你必须挑选正确保本的JDEV来下载Extract the JDeveloper patchJDev Patch解压后会有三个目录jdevbin – Includes an extended version of the Oracle JDeveloper 10g executable and OA Framework class libraries.jdevhome – Includes the OA Framework Toolbox Tutorial source and developer working area.jdevdoc – Contains documentationJDEV_USER_HOME Environment Variable增加一环境变量:JDEV_USER_HOME,值为<install directory>\jdevhome\jdevCopy a .dbc file to your local dir通过putty之类的工具SSH到Apps Server,在$INST_TOP/appl/fnd/12.0.0/secure或者$FND_SECURE目录下找到.dbc文件,把dbc文件拉到<JDEV_USER_HOME>\dbc_files\secure目录下dbc文件内包含许多连接数据库的必要参数(比如APPS_JDBC_URL,DB_PORT,DB_HOST...),有点像Oracle数据库的tnsnames.ora配置文件。Launch your JDeveloperjdevbin\jdev\bin\jdevW.exeCreate a Database ConnectionHello WorldJDev自带了一套Demo Project,你可以用它来测试JDev配置是否正确,File -> Open,<JDEV_USER_HOME>\myprojects\toolbox.jws右键Tutorial project --> Project Properties --> Runtime Connection和正常运行EBS系统不同,在JDev里运行OAF页面,我们没有提供一个responsibilities列表让用户选择,也没有提供一个页面让用户输入用户名和密码。我做的仅仅是右键一个Page,然后Run。所以系统用户,密码,还有职责的信息,你需要输入到这里"Runtime Connection"。注意:这个用户名和密码是有效的,并且这个用户可以访问对应的职责。Use below query script to find Application Short Name and Responsibility KeySELECT A.APPLICATION_SHORT_NAME,R.RESPONSIBILITY_KEYFROM FND_RESPONSIBILITY R,FND_APPLICATION A ,FND_RESPONSIBILITY_TL TLWHERE R.APPLICATION_ID = A.APPLICATION_IDAND R.RESPONSIBILITY_ID = TL.RESPONSIBILITY_IDAND A.APPLICATION_ID = TL.APPLICATION_IDAND TL.RESPONSIBILITY_NAME  LIKE 'Inventory%';Run OptionsOADeveloperMode默认都会有的,我需要做的是OADiagnostic加到Selected Options列表中区。In jDeveloper, you must include OADiagnostic in the “Run Options”. This is effectively similar to setting profile option “FND: Diagnostics” to Yes in eBusiness Suite. Using this option, you will be able to click on ‘Diagnostics’ to enable On-Screen display of debug messages.Database Connection中,选择刚刚设置的数据库。还有一个设置,Business Components项下,选择刚才新建的数据库,这步对于仅仅测试下页面来说不是必须的,但是如果后续要新增BC4J组件的时候,就会使用到这个数据库连接。右键oracle.apps.fnd.framework.toolbox.tutorial.webui.HelloWorldPG.xml,RunEnjoy...Some Other ReferenceConfiguring JDeveloper For Use With Oracle Applications 11i and R12 (Doc ID 330236.1)

Download JDeveloper 参考下边Note来下载JDev,不同版本的EBS需要下载不同版本的JDev Note 416708.1 - OA Framework - How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x  ATG Release 12...

How to Run Standard OA Framework Pages from JDeveloper

1.FTP ClassesFTP All the .class & .xml files from $JAVA_TOP/oracle to your local PC, and put them into jdev/jdevhome/myclasses2.Source FilesFrom 1st step files,Also need copy necessary PROD files into source file directory: jdev\myprojects\oracle\apps\eg.If you want to run a WIP module page,Then you can only copy $JAVA_TOP/oracle/apps/wip/xxx/ to jdev\myprojects\oracle\apps\For source files, we don't need class files, so we can delete all the *.class files from jdev\myprojects\oracle\apps\NOTE:Remember dont copy unnecessary files and open them in your project. Because when you will run even your HelloWorld page, all these will try to compile and will give you thousands of errors... Never solve these errors, as they don't exist at all. They are coming just because we have done something wrong....3.mds FilesFTP files from $PRODUCT_TOP/mds to jdev\myprojects\oracle\apps\PROD, for example copy directories from $WIP_TOP/mds to jdev\myprojects\oracle\apps\wip(mds:Meta Data Service)4.JDeveloper Setups and RunYou can follow article "JDeveloper Setup for OA Framework"  to config JDEV,Except above note, in project properties remove select compiler and in java uncheck the xml validation (it will throw error while compiling) Path: Project Properties -> Compiler -> XML -> Uncheck XML Syntax on MakeNow run the page you want, it will mostly work...Forgot one thing, Keep patient, Run OAF files locally, This process(Compiling + OC4J Launching + OAF Page Open) may need long time,depends on your hardware,Enjoy,PTIAN

1.FTP Classes FTP All the .class & .xml files from $JAVA_TOP/oracle to your local PC, and put them into jdev/jdevhome/myclasses 2.Source Files From 1st step files,Also need copy necessary PROD files...

Period Close Sequence

下图为Oracle EBS关闭期间的时候,各个模块的相互依赖的关系,顺着箭头逐步关闭各模块的期间。比如,Order Management会直接影响应收(Receivables)模块,所以OM模块应先于应收模块关闭。如果图中某个模块没有使用,则可以跳过它,进入下一个模块。每个特定模块期间中,Transactions的账户信息在关闭时最终都会Transfer to General Ledger.所以期间关闭基本是下边这个流程:1. Order Management / Shipping2. Cash Management / Treasury3. Payables4. Receivables5. Purchasing6. Inventory / Costing7. Projects8. Assets9. General Ledger一篇期间关闭的详细文档:EBS_period_end_close_R12_Final.pdfPeriod- End Processing in Oracle Financials (Release 11i)  为期间关闭时各模块更详细的一个TODO List Payables Complete All Transactions for the Period Being Closed Run the Payables AutoApproval Process for All Invoices / Invoice Batches Review & Resolve Amounts to Post to the General Ledger Reconcile Payments to Bank Statement Activity for the Period Transfer All Approved Invoices Payments, Reconciled Payments to the General Ledger Review the Payables to General Ledger Posting Process After Completion Submit the Unaccounted Transactions Sweep Program Close the Current Oracle Payables Period Accrue Uninvoiced Receipts Reconcile Oracle Payables Activity for the Period Run Mass Additions Transfer to Oracle Assets Open the Next Payables Period Run Reports for Tax Reporting Purposes (Optional) Run the Key Indicators Report (Optional) Purge Transactions (Optional)Purchasing Complete All Transactions for the Period Being Closed Review the Current and Future Commitments (Optional) Review the Outstanding and Overdue Purchase Orders (Optional) Follow up Receipts-Check with Suppliers Identify and Review Un-invoiced Receipts (Period End Accruals) Follow Up Outstanding Invoices Complete the Oracle Payables- Period End Process Run Receipt Accruals - Period End Process Reconcile Accounts - Perpetual Accruals Perform Year End Encumbrance Processing. (Optional) Close the Current Purchasing Period. Open the Next Purchasing Period. Run Standard Period End Reports (Optional)Inventory/WIP Complete All Transactions for the Period Being Closed. Check Inventory and Work In Process Transaction Interfaces. Check Oracle Order Management Transaction Process. Review Inventory Transactions. Balance the Perpetual Inventory. Validate Work In Process Inventory. Transfer Summary or Detail Transactions Close the Current Oracle Payables and Oracle Purchasing Periods Close the Current Inventory Period Open the Next Inventory Period Run Standard Period End Reports (Optional)Order Management Complete All Transactions for the Period Being Closed Ensure all Interfaces are Completed for the Period (Optional) Review Open Orders and Check the Workflow Status Review Held Orders Review Discounts Review Backorders Review and Correct Order Exceptions Reconcile to Inventory Reconcile to Receivables (Optional) Run Standard Period End ReportsReceivables Complete All Transactions for the Period Being Closed Reconcile Transaction Activity for the Period Reconcile Outstanding Customer Balances Review the Unapplied Receipts Register Reconcile receipts. Reconcile Receipts to Bank Statement Activity for the Period Post to the General Ledger Reconcile the General Ledger Transfer Process Reconcile the Journal Import Process Print Invoices Print Statements (Optional) Print Dunning (Reminder) Letters (Optional) Close the Current Oracle Receivables Period Reconcile Posted Journal Entries Review Unposted Items Report Open the Next Oracle Receivables Period Run Reports for Tax Reporting Purposes (Optional) Run Archive and Purge programs (Optional)Assets Complete All Transactions for the Period Being Closed Assign All Assets to Distribution Lines Run Calculate Gains and Losses (Optional) Run Depreciation Create Journal Entries Rollback Depreciation and/or Rollback Journal Entries (Optional) Create Deferred Depreciation Journal Entries (Optional) Depreciation Projections(Optional) Review and Post Journal Entries Reconcile Oracle Assets to Oracle General Ledger Using Reports. Run Responsibility Reports (Optional) Archive and Purge Transactions (Optional)Projects Change the Current Oracle Projects Period Status from Open to Pending Close Open the Next Oracle Projects Period Complete All Maintenance Activities Run Maintenance Processes Complete All Transaction Entry for the Period Being Closed Run the Final Cost Distribution Processes Interface Transactions to Other Applications (AP, GL, FA) Generate Draft Revenue for All Projects Generate Invoices Run Final Project Costing and Revenue Management Reports Transfer Invoices to Oracle Receivables Interface Revenue to General ledger (Project Billing Only) Run Period Close Exception and Tieback Reports Change the Current Period Oracle Projects Status from Pending Close to Closed Advance the PA Reporting Period (Optional) Update Project Summary Amounts Restore Access to User Maintenance Activities Permanently Close the Oracle Projects Period (Optional) Reconcile Cost Distribution Lines with General Ledger (Optional)Cash Managment Load Bank Statements Reconcile Bank Statements Create Miscellaneous Transactions Review AutoReconciliation Execution Report Resolve Exceptions on the AutoReceonciliation Execution Report Run Bank Statement Detail Report Run Transactions Available for Reconcilaition Report Resolve Un-reconciled Statement Lines Run the GL Reconciliation Report Run the Account Analysis Report for the General Ledger Cash Account Review the Account Analysis Report Correct any Invalid Entries to the General Ledger Cash Account (Optional) Perform the Bank ReconciliationGeneral Ledger Ensure the Next Accounting Period Status is Set to Future Entry Complete Oracle Sub-ledger Interfaces to Oracle General Ledger Upload Journals from ADI (Applications Desktop Integrator) to Oracle General Ledger Complete Non-Oracle Sub-ledger Interfaces to Oracle General Ledger (Optional) Generate Reversal Journals (Optional) Generate Recurring Journals (Optional) Generate Mass Allocation Journals (Optional) Review and Verify Journal Details of Unposted Journal Entries Post All Journal Batches Run General Ledger Trial Balances and Preliminary Financial Statement Generator Reports (FSGs) Revalue Balances (Optional) Translate Balances (Optional) Consolidate Sets of Books (Optional) Review and Correct Balances (Perform Reconciliations) Enter Adjustments and / or Accruals and Post Perform Final Adjustments Close the Current Oracle Gneral Ledger Period Open the Next Oracle General Ledger Period Run Financial Reports for the Closed Period Run Reports for Tax Reporting Purposes (Optional) Perform Encumbrance Year End Procedures (Optional)Period Navigation Purchasing:Purchasing Responsibility > Setup > Financials > Accounting > Control Purchasing PeriodsInventory:Inventory Responsibility > Accouting Close Cycle > Inventory Accounting PeriodsGL:General Ledger Responsibility > Open/CloseAP:Payables Responsibility > Setup > Calendar > Accounting > Accounting PeriodsAR: Receivables Responsibility > Control > Accounting > Open/Close PeriodReference:Period Close Advisor: E-Business Suite (EBS) (Doc ID 335.1)

下图为Oracle EBS关闭期间的时候,各个模块的相互依赖的关系,顺着箭头逐步关闭各模块的期间。比如,Order Management会直接影响应收(Receivables)模块,所以OM模块应先于应收模块关闭。如果图中某个模块没有使用,则可以跳过它,进入下 一个模块。 每个特定模块期间中,Transactions的账户信息在关闭时最终都会Transfer to General Ledger. 所以...

Oracle E-Business Suite Upgrade Documents

Oracle E-Business Suite Upgrade Guide Release 11i to 12.1PDF Version: LinkHTML Version:LinkNew Whitepaper: Planning Your E-Business Suite Upgrade from Release 11i to 12.1 (Second Edition) https://blogs.oracle.com/stevenChan/entry/ebs_121_upgrade_whitepaperPlanning Your Oracle E-Business Suite Upgrade from Release 11i to 12.1 and Beyonhttp://www.oracle.com/technetwork/apps-tech/upgrade-planning-2011-1403212.pdfUpgrading to Oracle E-Business Suite Release 12: Best Practices //cdn.app.compendium.com/uploads/user/e7c690e8-6ff9-102a-ac6d-e4aebca50425/f4a5b21d-66fa-4885-92bf-c4e81c06d916/File/a727c901266b0f018d24bb0b133a2ae1/eweinstein.pdfFive Key Resources for Upgrading to E-Business Suite Release 12https://blogs.oracle.com/stevenChan/entry/five_key_resources_for_upgrading_to_ebusiness_suit11i to R12(12.1.3) Real Experiences of a DBAhttp://www.solutionbeacon.com/RealUpgradeExperiences11itoR12.pdfFour Critical Changes for Customizations in Release 12 https://blogs.oracle.com/stevenChan/entry/four_critical_changes_for_custOracle eBusiness Suite Applications Release 12 Upgrade from Deloittehttp://nooaug.oaug.org/meetings/DeloitteR12UpgradePresentationforNOOAUGAugust2012.pdfMOS NotesPlanning Your E-Business Suite Upgrade from 11i to Release 12.1 (Note 987516.1)Upgrade Advisor: E-Business Suite (EBS) Technology Stack Upgrade from 11.5.10.2 to 12.1.2 [ID 253.1]Upgrade Advisor: E-Business Suite (EBS) Upgrade from 11.5.10.2 to 12.1.3 [ID 269.1]Planning Your Oracle E-Business Suite (EBS) Upgrade from Release 11i to Release 12 [ID 1406960.1]Preparing Custom Development for Next Oracle E-Business Suite Release [ID 374398.1]R12: Upgrade vs. Reimplementation (Financials) [ID 780989.1]Database Preparation Guidelines for an E-Business Suite Release 12.1 Upgrade [ID 761570.1]Oracle E-Business Suite Upgrade Center - Home [ID 461705.1]Oracle E-Business Suite Release 12.1.1 Maintenance Pack Installation Instructions [ID 752619.1]

Oracle E-Business Suite Upgrade Guide Release 11i to 12.1 PDF Version: Link HTML Version:Link New Whitepaper: Planning Your E-Business Suite Upgrade from Release 11i to 12.1 (Second Edition) https://blogs....

WIP Component Pick Release

WIP Component Pick Release是WIP模块的挑库(Picking Release),这个和订单模块的挑库类似,但又有区别。相同点:不管是WIP还是OM的挑库,他们都会用到挑库规则。区别:订单模块的挑库,是把仓库中的货物挑选出来,然后发往一个临时发货区(Staging Subinventory);WIP的挑库,目的地取决于WIP Supply Type。1.1. 对于Push类型的工单,如果不指定Supply仓库的话(Supply Subinv为非必填项),WIP Component Pick Release之后并Transact Move Order,那么物料将直接到工单上,相当于做了WIP Component Issue,这种Move Order的Transaction_Type_ID为35。(参见:Oracle EBS R12 WIP Component Issue&Return Process中例子)Component Pick Release之后,会生成一个Move Order可以看到Move Order的事务类型为WIP Issue(Transaction_Type_ID:35)1.2. 对于Push类型的工单,如果指定Supply Subinv的话(这个Supply Sub有点类似于订单挑库时的临时发货区),WIP Component Pick Release之后并Transact Move Order,那么物料将直接到工单上,相当于做了WIP Component Issue,这种Move Order的Transaction_Type_ID为35。你可以WIP > Job/Shedule Details > Material Requirements里修改Supply Subinv,特别需要注意的是:Supply Subinv必须是不可保留的(Non Reservable),否则在Component Pick Release报这样的错:Error in component pick release. Job/schedule number (JOB_NAME) has a reservable supply subinventory (SUPPLY_SUB_NAME) for component (COMPONENT_NAME)上图中,因为Supply Sub:RIP允许保留,所以报错.我们可以定义一个不可保留的子库,Supply_Sub,并且不允许保留(是否允许保留由Material Status来控制)然后设置这个新定义的子库到Supply Sub然后Component Pick Release,可以看到Move Order对应Transaction Type变为了Backflush Transfer(TRANSACTION_TYPE_ID:51),Backflush Transfer实际上类似于Subinventory Transfer,只不过这里的目标Sub必须是non-reservable。View/Update Allocation接下来,你去WIP Material Transaction里做WIP Issue,给工单供货,默认给工单供货的子库就是刚才的目标子库“Supply_Sub”(当然这里你也可以改成其他的子库)2.1 对于Pull(Operation Pull, Assembly Pull)类型的工单。WIP Component Pick Release会把物料从Source Sub(Reservable) 以Backflush Transfer的形式转移到这个Supply Sub。工序完工(To Move)或者工单完工(WIP Completion)的时候,以backflush的方式从Supply Sub扣料。如果BOM中没有指定Pull物料的Supply的Subinventory和locator,那么Job在Release之后,Job会把Work in Process Parameters中的默认Supply Subinventory和Locator作为Supply Sub/Locator。Source Subinv and Supply SubinvWIP Component Pick Release会把物料从Source Subinv转移(backflush transfer)到Supply Subinv。需要注意的是,Source Sub必须是可保留的;而Supply Subinv是不可保留的。原因是什么呢?1. The source sub inventory  (the sub inventory which stores the on-hand, or the sub inventory  from which the components are to be picked) should be reservable. 2. The supply sub inventory  (the staging sub inventory or the sub inventory which receives the components from the source sub inventory through the move order) should NOT be reservable. This is the current functionality (R12). Component pick release will allocate the material only from reservable subinventories. The reason for this being that if it were to pick material from non-reservable subinventories, then it could pick material already picked for another job, since picked material resides in supply subinventories which are not reservable.WIP component Manufacturing move order lines are auto detailed, when running WIP component pick release. If the detailing process is unable to allocate the lines, then the lines will get backordered and closed. Hence, if the pick from subinventory is not reservable, all the move orders lines will get closed and the components will not get allocated.Reference:http://www.oracleug.com/user-guide/work-process/component-pick-release转载请注明出处:http://blog.csdn.net/pan_tian/article/details/8942447

WIP Component Pick Release是WIP模块的挑库(Picking Release),这个和订单模块的挑库类似,但又有区别。 相同点:不管是WIP还是OM的挑库,他们都会用到挑库规则。 区别:订单模块的挑库,是把仓库中的货物挑选出来,然后发往一个临时发货区(Staging Subinventory); WIP的挑库,目的地取决于WIP Supply Type。 1.1.对于Push类型的...

Oracle EBS Key Patches

Release R12.0.X5082400 - 12.0.1(RUP1 FOR ORACLE APPLICATIONS RELEASE 12)5484000 - 12.0.2 Release Update Pack (RUP2)6141000 - 12.0.3 Release Update Pack (RUP3)6435000 - 12.0.4 Release Update Pack (RUP4)6728000 - 12.0.6(ORACLE E-BUSINESS SUITE 12.0.6 RELEASE UPDATE PACK (RUP6) )Release R12.1.X7303030 - 12.1.1 Maintenance Pack7303033 - Oracle E-Business Suite 12.1.2 Release Update Pack (RUP2)7651091 - Oracle Applications Technology Release Update Pack 2 for 12.1 (R12.ATG_PF.B.DELTA.2)8919491 - Oracle Applications Technology 12.1.3 Product Family Release Update Pack9239090 - ORACLE E-BUSINESS SUITE 12.1.3 RELEASE UPDATE PACK                  README - Oracle E-Business Suite Release 12.1.3 Readme (Note 1080973.1)                 Oracle E-Business Suite Release 12.1.3: Release Content Documents (Note 561580.1) Release 11i3140000 - Base 11.5.10(Oracle Applications Release 11.5.10)3240000 - CU1(11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1)3460000 - CU2(11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2)  11i RUP Patches3480000 - 11.5.10.2 Maintainance4334965 - RUP34676589 - RUP45473858 - RUP55903765 - RUP66241631 - RUP7INV/RCV Rollup Patch23    - 10111967INV/RCV Rollup Patch22    - 9878808INV/RCV Rollup Patch21    - 9649124INV/RCV Rollup Patch20    - 9466436INV/RCV Rollup Patch19    - 9265857INV/RCV Rollup Patch18    - 9063156INV/RCV Rollup Patch17    - 8403258INV/RCV Rollup Patch16    - 8403254INV/RCV Rollup Patch15    - 8403245INV/RCV Rollup Patch14    - 7666112INV/RCV Rollup Patch13    - 7581431INV/RCV Rollup Patch12    - 7258629INV/RCV Rollup Patch11    - 7258624INV/RCV Rollup Patch10    - 7258620INV/RCV Rollup Patch9    - 6870030INV/RCV Rollup Patch8    - 6461522INV/RCV Rollup Patch7    - 6461519INV/RCV Rollup Patch6    - 6461517INV/RCV Rollup Patch5    - 6449139INV/RCV Rollup Patch4    - 5739724------------------------------------NOTE:CU = Consolidated Update, RUP = RELEASE UPDATE PACKRelated Topic:How to check whether the patches have been applied or not:http://blog.csdn.net/pan_tian/article/details/7498617Oracle Application Patch Types :http://blog.csdn.net/pan_tian/article/details/8903284source:http://blog.csdn.net/pan_tian/article/details/8903076

Release R12.0.X5082400 - 12.0.1(RUP1 FOR ORACLE APPLICATIONS RELEASE 12) 5484000 - 12.0.2 Release Update Pack (RUP2) 6141000 - 12.0.3 Release Update Pack (RUP3)6435000 - 12.0.4 Release Update Pack...

OPM Demo Flow

OPM全称Oracle Process Manufacturing,即Oracle流程制造,和离散制造(Discrete Manufacturing)不同,流程制造主要应用于制药,食品行业。 Process Manufacturing vs Discrete Manufacturing 流程式制造(Process Manufacturing)是通过一条生产线将原料制成成品,比如集成电路、药品及食品/饮料制造等。流程式制造(Process Manufacturing)特点:生产计划方面,计划制定简单,常以日产量的方式下达计划,计划也相对稳定;生产设备的能力固定;生产过程控制方面,工艺固定,工作中心的安排符合工艺路线。通过各个工作中心的时间接近相同;工作中心是专门生产有限的相似的产品,工具和设备为专门的产品而设计;物料从一个工作点到另外一个工作点使用机器传动,有一些在制品库存;生产过程主要专注于物料的数量、质量和工艺参数的控制;生产领料常以倒冲的方式进行。离散式制造(Discrete Manufacturing)是将不同的现成元部件及子系统装配加工成较大型系统,例如电脑、汽车及工业用品制造等。离散式制造最主要特征为:生产过程中基本上没有发生物质改变,只是物料的形状和组合发生改变,即最终产品是由各种物料装配而成,并且产品与所需物料之间有确定的数量比例,如一个产品有多少个部件,一个部件有多少个零件,这些物料不能多也不能少。 按通常行业划分属于离散行业的典型行业有机械制造业、汽车制造业、家电制造业等等。 功能区别 行业区别 Product Layout(Discrete) Product Layout(Process) Demo 以巧克力的流程制造为例,这里假设一个巧克力成品由两种原材料(Ingredient)组成,一个是巧克力糖浆(Chocolate Syrup),另外一个是水(Water)。在Vision Demo里演示,登陆用户:PROCESS_OPS/WELCOME,职责:OPM ALL,组织:PR1 1.组织必须是Process Manufacturing Enabled. (MTL_PARAMETERS.PROCESS_ENABLED_FLAG = Y) 2.物料的定义 首先定义巧克力这个制造的成品,继承Finished Good的模板 勾选流程制造的必要属性 然后定义,制造巧克力的所必须的原材料:巧克力糖浆(Chocolate Syrup),另外一个是水(Water) (对于这两个原材料,也要在Process Manufacturing勾选中勾选那四个属性,否则定义Formula的时候选不到) 3.OPM Setups Overview OPM所包含的点可以从下图看到 * 一个Routing(工艺路线)由一系列有序的Operation(工序)组成 * 每一个Operation又包含了若干个Activity(行动,动作) * 每一个Activity又由若干的Resource(资源)组成 * Formula(配方)是由Ingredient(原料),Product,Byproduct(副产品)组成 * 最后Recipe把Formula和Routing连接起来 4.Resource的定义 Navigation:Process Engineer > Setup > Generic Resource Resource是生产过程中所需要用到的资源,如,生产设备,劳动力等,具体到本例,我们要制造巧克力,那么我们需要一台巧克力搅拌机。 5.Operations的定义 Navigation: Process Engineer > Process Operations 定义Operation。像之前说的那样,一个Operation可以多个Activity,每个Activity包含多个Resource,这些关联关系就是在Process Operations绑定的。这里我们选择一个已经定义好的Activity:MIX(搅拌),然后点击Resource按钮,把我们上一步定义的Resource:搅拌机绑定给MIX搅拌这个Activity。 (如果想定义新的Activity,路径是:Process Engineer > Setup > Activities) 保存之后,Operation的状态应该是Approved for General Use.(更改状态在菜单,Tools > Change Status) 这里只定义搅拌这一个Operation,你也可以定义多个Operation。 6. 工艺路线Routing的定义 Navigation: Process Engineer > Process Routings 一个Routing(工艺路线)由一系列有序的Operation(工序)组成 Table : FM_ROUT_HDR:Routing Header Information FM_ROUT_DTL:Routing details. Contains the operations that comprise a routing and their scaleability types. 7.设置组织为Plant 和 Laboratory 职责:Product Development Security Manager > Organization Parameters 勾选Plant和Laboratory,表示,当前组织可作为工厂和研究所。 Yield UOM为最终输出产品的计量单位。 8.Formula(配方)的定义 定义完Routing,Operation,Activity,Resource之后,就需要定义Formula。 Navigation: OPM All 职责 > Formulator > Formulas 1 个巧克力成品 = 0.5 巧克力糖水 + 0.5 水 (注:之前Item的UOM定义的有点问题,定义成了Ea,应该定义成Lbs,而上边的Operation又使用的Lbs,所以需要需要在UOM Conversion里增加LB和Ea的一个转换率) 上图中Products:1表示生成品一个,By-Products:无,Ingredients:2表示原材料两个 接下来就要菜单中Action > Change Status, Related Tables: FM_FORM_MST_B:Formula header base table. Attach attributes to formula as a whole (e.g. classes). FM_MATL_DTL:Formula material details. Product, ingredient, byproduct data for a formula. FM_FORM_MST_B.FORMULA_ID  <--> FM_MATL_DTL.FORMULA_ID 9.Recipe的定义 Recipe把Formula和Routing连接起来 然后同样,更改Recipe和Validity Rules的状态(两次Change Status),From New to Approved for General Use 10.Batch Numbering Navigation:Production Supervisor > Setup > Parameter Bach Numbering设置为Automatic 11.创建Batch Navigation:Production Supervisor > Create Document 接下来就是真正的OPM的生产过程了,包括Batch的生成和Batch的执行,后续都是跟Batch打交道了。包含的主要步骤和状态有:Process                                           Batch StatusCreate Document(Batch Creation)          PendingBatch Release                                       WIP (Can revert back to Pending)Complete the Batch                            Completed(Can revert back to WIP - Caution - Can lose the material transactions)Close the Batch                                   Closed  (Final Close can NOT be reverted back)Cancel the Batch                               Cancelled 我们这里需要生产10 Pounds的巧克力 点OK,因为第10步,我们设置了Document编码规则,然后会自动生成Document Number,记录下来这个号码,后边会用到。 12.查看刚生成的Batch Batch在数据库级别对应的表分别是GME_BATCH_HEADER,GME_MATERIAL_DETAILS GME_BATCH_HEADER: Batch/FPO header. This table stores the header information for both Firm Planned Orders (FPOs) and Batches, including the plant, the validity rules, formula, and routing on which the batch was based, and the planned and actual start and completion dates. BATCH_ID:PK BATCH_NO:Batch or FPO number RECIPE_VALIDITY_RULE_ID:Surrogate key to the Recipe Validity Rule Id the batch or FPO was based on. FORMULA_ID: -->FM_FORM_MST_B.FORMULA_ID ROUTING_ID:-->FM_ROUT_HDR.ROUTING_ID GME_MATERIAL_DETAILS: Batch or FPO material details. This table contains data on the products, ingredients, and by products of an FPO or batch, including the planned and actual quantity. FieldsMATERIAL_DETAIL_ID:Primary KeyBATCH_ID: Batch Identifier FK to the GME_BATCH_HEADER tableFORMULALINE_ID: FK to the Formular FM_MATL_DTL tableLINE_TYPE:-1 = Ingredient,1 = Product; The product on Line 1 is the primary product.2 = Byproduct Related Queries: --Batch Header InformationSELECT * FROM GME_BATCH_HEADER WHERE BATCH_NO = '120';--Batch LinesSELECT GMD.* FROM GME_MATERIAL_DETAILS GMD,GME_BATCH_HEADER GBHWHERE GMD.BATCH_ID = GBH.BATCH_IDAND GBH.BATCH_NO = '120';--Formula LinesSELECT FMD.*FROM FM_MATL_DTL FMD, GME_MATERIAL_DETAILS GMDWHERE FMD.FORMULALINE_ID = GMD.FORMULALINE_IDAND GMD.MATERIAL_DETAIL_ID = &MATERIAL_DETAIL_ID 13.Release the Batch Release之后,Batch的状态变为了WIP 14.Misc Receipt Some Quantity Ingredient 后续要生产了,所以我们至少要保证原材料的数量是充足的,这里做杂收增加数量 15.Process Batch Navigation:Production Supervisor > Batch Details > Click Transact Material Button Transaction Type             Item Type -----                                   ------- WIP Issue                       Ingredient WIP Completion              Finished Good 选择Ingredient,然后再把焦点放到Transaction Type,输入有库存量的子库,最后点Transact按钮 Transact之后,Batch主界面中的Actual Qty就相应的发生了变化 然后用户可以View Material Transaction,就可以看到我们对Ingredients或者Production做了哪些事务 这个时候Ingredient的数量都是充足的(巧克力糖水还多了5个),我们可以生产成品了。 完成后,查看Material Transactions,可以看到巧克力成品已经生产完成,并已入库 TRANSACTION_TYPE_ID:44 16.Complete the Batch Status:WIP --> Completed 17.Close the Batch Status: Completed --> Closed References: http://www.oracleappshub.com/beginner/discrete-process-manufacturing-in-erp/ http://ajayatre.blogspot.jp/2012/10/opm-setup-in-r12_29.html 转载请注明出处:http://blog.csdn.net/pan_tian/article/details/8907106

OPM全称Oracle Process Manufacturing,即Oracle流程制造,和离散制造(Discrete Manufacturing)不同,流程制造主要应用于制药,食品行业。 Process Manufacturing vs Discrete Manufacturing 流程式制造(Process Manufacturing)是通过一条生产线将原料制成成品,比如集成电路、药品及食品/饮料...

Oracle EBS订单的流程(Order->AR)

创建订单 路径:Order Management > Orders,Returns > Sales Orders 填写订单头信息,客户,订单类型,销售人员 填写订单体信息,输入物料,数量... 然后Book Order,订单Book之后,你会发现订单头的Status的状态变成了“Booked”,订单行的状态为"Awaiting Shipping",记录下来你的订单号,后边的每一步都需要这个订单号。 状态:订单Book之后,这个时候发运单也就产生了,如果你去Shipping Transactions Form,查找订单,你就会发现发运单的Line Status为Ready to Release,Next Step为Pick Release Pick Release 路径:Order Management > Shipping > Release Sales Orders > Release Sales Orders Pick Release俗称挑库(即从仓库中挑选货物).在Order  Number中输入待挑库的订单号 Auto Pick Confirm设置为No Auto Allocate设置为N Auto Allocate和Auto Pick Confirm都可以设置为Yes,只不过为了讲的更加详细,这里都设置为No,设置为Yes的话,那么后边的Allocate和Pick Confirm两步,系统就会自动帮你完成。 这里有两个按钮来执行Pick  Release,”Concurrent“是把Pick Release的任务发给Concurrent Request来执行,"Execute Now"是系统再前段执行Pick Release,直到执行结束才把控制权返还给User,这里我们点击Concurrent按钮。 Pick Release后台做了什么事情?Pick Release会创建一个Pick Wave类型Move Order,这个Move Order就是用来把库存中的货物搬到一个临时发货区(Staging),如果打开INV日志就能看到系统是调用INV_MOVE_ORDER_PUB.CREATE_MOVE_ORDER_HEADER来创建Move Order的。(对于Pick Release的后台流程可以参见我的总结:Pick Release Process) 另外需要提到的是,在Pick Release之后,系统会针对你的订单生成一条Reservation(预留),这个时候的预留称为Soft Reservations,因为还没有明确从哪个库出货,所以会在Org级别创建物料的保留。 状态:这个时候,Shipping Transaction的Line Status为"Released to Warehouse",Next Step为"Transact Move Order";订单头的状态仍为Booked,行状态仍为”Awaiting Shipping“。 Pick Confirm Pick Confirm(挑库确认)的动作实际上是在Transact Move Order中完成的,先Allocate挑选货物,再Transact Move Order. 路径:Inventory > Move Orders > Transact Move Orders 查询页面,Pick Wave那个Tab,输入订单号 进入TMO主界面后,点击Allocate,Allocate会根据你系统默认的Picking Rule来挑选货物,然后给你一个Suggestion的挑库项,Suggestion记录会保存在MTL_MATERIAL_TRANSACTIONS_TEMP表(即Pending Transactions)。 另外完成Allocate之后,你会看到界面Allocation项会出现Single,Multiple或者None的标识,Single表示,系统给你了产生了一条Suggestion的Transaction记录,Multiple表示产生了多条;None表示没有产生Suggestion记录(很可能是因为你系统中库存数量不足) 接下来一步就是去Transact这个Move Order 按Transact之后,Inventory Transaction Manager 会处理Suggestion Transactions(MMTT),然后物料转移的动作就真实发生了,物料就从正式Subinventory到临时存货区(Staging)。这个Transction可以从Material Transaction的Form中查询到。 另外对于Reservation来说,在Transact之后,因为库存明确了,Reservation就会变得更加明确,是对哪个Sub,locator做保留。 状态:这个时候,Shipping Transaction的Line Status为"Staged/Pick Confirmed",Next Step为"Ship Confirm/Close Trip Stop";订单头的状态仍为Booked,行状态变为了”Picked“。 Ship Confirm Deliveries 路径:Order Management > Shipping > Transactions 切换到Delivery页签,准备Ship Confirm(发货确认),如果你在Pick Release的时候,没有选择Autocreate Delivery,那么系统会检查Define Shipping Parameters里边这两个的设置,如果shipping parameters里边也没有选择,那么你需要手工的在Ship Confirm的时候选择Action->Auto-create Deliveries. Delivery信息实际上包含你要投递货物到哪里,使用哪家物流公司.... Delivery页签,点击Ship Confirm按钮后,会弹出一个窗口,"Defer Interface"如果被勾上,说明你要手动的运行Interface Trip Stop SRS,我们勾上Defer Interface,点OK。 Delivery was successfully confirmed!!! Ship Confirm这个动作会插入一条记录到MTL_TRANSACTIONS_INTERFACE表中,这个MTI记录的类型为Sales Order Issue,这条记录会被接下来的Interface Trip Stop来处理,最终从MTI转到MMT表中。 状态:这个时候,Shipping Transaction的Line Status为"Shipped",Next Step为"Run Interfaces";订单头的状态仍为Booked,行状态变为了”Shipped“。 Interface Trip Stop - SRS 因为我们在Ship Confirm的时候设置了Defer Interface,所以这里我们还需要手动的运行Interface Trip Stop - SRS。 路径:Order Management > Shipping > Interface > Run > Request:Interface Trip Stop - SRS Interface Trip Stop实际上包含两部分:Inventory Interface  SRS(产生库存出货事务)和 Order Management Interface  SRS(更新发运行状态、以准备生成AR发票)。 Inventory Interface  SRS会根据Shipping Transaction来插入记录到MTI,进而INV Manager会把这条MTI记录转到MMT表中,一条Sales Order Issue的transaction记录就产生了,并完成库存数量的扣减和Reservation的删除。Inventory Interface  SRS运行完之后,会更新WSH_DELIVERY_DETAILS表的INV_INTERFACED_FLAG字段为Y。 Order Management Interface - SRS是在Inventory Interface  SRS之后运行的,这个Request更新发运行状态、以准备生成AR发票,OM Interface运行结束后会更新WSH_DELIVERY_DETAILS表的OE_INTERFACED_FLAG为Y。 状态:这个时候,Shipping Transaction的Line Status为"Interfaced",Next Step为"Not Applicable";订单头的状态仍为Booked,行状态变为了”Shipped“。 Workflow background Process 路径:Inventory > Workflow Background Engine Item Type:OM Order Line Process Deferred:Yes Process Timeout:No 这个program用于处理Deffered状态的workflow,Workflow Background Process运行后,相关数据就会从Order表导入到RA Interface表中去(RA_INTERFACE_LINES_ALL,RA_INTERFACE_SALESCREDITS_ALL,RA_Interface_distribution) 你可以通过下边的SQL来查看RA Interface信息: 1.SELECT * FROM RA_INTERFACE_LINES_ALL WHERE sales_order = '65961'; 2.SELECT * FROM RA_INTERFACE_SALESCREDITS_ALL WHERE INTERFACE_LINE_ID IN (SELECT INTERFACE_LINE_ID FROM RA_INTERFACE_LINES_ALL WHERE sales_order = '65961' ); 3.SELECT * FROM RA_INTERFACE_DISTRIBUTIONS_ALL WHERE INTERFACE_LINE_ID IN (SELECT INTERFACE_LINE_ID FROM RA_INTERFACE_LINES_ALL WHERE sales_order = '65961' ); 数据插入到RA Interface之后,再看OE_ORDER_LINES_ALL的INVOICE_INTERFACE_STATUS_CODE字段变成了Yes,INVOICED_QUANTITY变为了订单行的数量。并且你会发现订单行的状态变成了Closed,订单头的状态仍为Booked。 AutoInvoice 用于生成AR发票 路径:Account Receivable > Interface > AutoInvoice Name:Autoinvoice Master Program Invoice Source:Order Entry Default Day:当前日期 提交后,在request里能看到”Autoinvoice Import Program“在运行。 从上图,可以看出Auto Invoice Program用于处理RA的interface表,然后生成真正的营收发票信息,最终数据会插入AR正式表中(RA_CUSTOMER_TRX_ALL,RA_CUSTOMER_TRX_LINES,AR_PAYMENT_SCHEDULES). 可以到订单里 Order > Action > Additional Information > Invoices/Credit Memos中能看到发票信息,也可以通过下边SQL查看订单和AR信息, SELECT ooha.order_number , oola.line_number so_line_number , oola.ordered_item ,oola.ordered_quantity * oola.unit_selling_price so_extended_price , rcta.trx_number invoice_number , rcta.trx_date , rctla.line_number inv_line_number ,rctla.unit_selling_price inv_unit_selling_price FROM oe_order_headers_all ooha , oe_order_lines_all oola , ra_customer_trx_all rcta , ra_customer_trx_lines_all rctlaWHERE ooha.header_id = oola.header_id AND rcta.customer_trx_id = rctla.customer_trx_id AND rctla.interface_line_attribute6 = TO_CHAR (oola.line_id) AND rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)AND order_number = :p_order_number; 如果Autoinvoice Import Program有出现error的情况,你可以查看RA_INTERFACE_ERRORS_ALL表Message_text字段,来获取错误信息。 Closing the Order 订单的关闭是自动的,在所有行工作流结束(Close或者Cancel)后0.5天,订单头也将在Workflow Background Process的推动下关闭。 还有另外一种说法:you can wait until month-end and the “Order Flow – Generic” workflow will close it for you. Order&Shipping Transactions Status Summary Step Order Header Status Order Line Status Order Flow Workflow Status (Order Header) Line Flow Workflow Status (Order Line) Shipping Transaction  Status(RELEASED_STATUS in WDD) 1. Enter an Order Entered Entered Book Order Manual Enter – Line                              N/A 2. Book the Order Booked Awaiting Shipping Close Order Schedule ->Create Supply ->Ship – Line                       Ready to Release(R) 3. Pick the Order Booked Picked Close Order Ship – Line 1.Released to Warehouse(S)(Pick Release but not pick confirm) 2.Staged/Pick Confirmed(Y)(After pick confirm) 4. Ship the Order Booked Shipped Close Order Fulfill – Deferred 1.Shipped(After ship confirm) 2.Interfaced(C)(After ITS) Booked Closed Close Order Fulfill ->Invoice Interface ->Close Line -> End 5. Close the Order Closed Closed End End 关于订单,shipping txn的状态,可以参见另外一篇文章:http://blog.csdn.net/pan_tian/article/details/7696528 ======EOF======

创建订单 路径:Order Management > Orders,Returns > Sales Orders 填写订单头信息,客户,订单类型,销售人员 填写订单体信息,输入物料,数量... 然后Book Order,订单Book之后,你会发现订单头的Status的状态变成了“Booked”,订单行的状态为"Awaiting Shipping",记录下来你的订单号,后边的每一步都需要这个订单号。 状态:订单...

Descriptive Flex Field Sample Code

以Lot Entry中的两个DFF为例,一个是Maintain Lot Number DFF,另一个是Lot Attributes DFF 知道基表,查看对应DFF SELECT * FROM FND_DESCRIPTIVE_FLEXS WHERE application_table_name = 'MTL_LOT_NUMBERS'; FND_DESCRIPTIVE_FLEXS表中几个字段需要注意, APPLICATION_TABLE_NAME:表明这个DFF是基于哪个表建立的 DESCRIPTIVE_FLEXFIELD_NAME:DFF的标识字符串,比如:“Lot Attributes”表示Lot Attributes DFF,“MTL_LOT_NUMBERS”表示Maintain Lot Number DFF CONTEXT_COLUMN_NAME:DFF上下文存储字段,比如:Lot Attributes DFF使用LOT_ATTRIBUTE_CATEGORY作为上下文字段,Maintain Lot Number DFF使用ATTRIBUTE_CATEGORY作为上下文字段 CONCATENATED_SEGS_VIEW_NAME:DFF View,比如:Lot Attributes DFF使用MTL_LOT_NUMBERS1_DFV作为DFF View,Maintain Lot Number DFF使用MTL_LOT_NUMBERS_DFV作为DFF View。 注册DFF DFF注册的代码一般写在When-New-Form-Instance或者WHEN-NEW-BLOCK-INSTANCE里 Maintain Lot Number DFF FND_DESCR_FLEX.DEFINE(BLOCK =>'LOT_ENTRY',--Block                              FIELD =>'LDF',--Block中的Field                             APPL_SHORT_NAME =>'INV',                              DESC_FLEX_NAME  =>'MTL_LOT_NUMBERS'); MTL_LOT_NUMBERS为FND_DESCRIPTIVE_FLEXS表中的DESCRIPTIVE_FLEXFIELD_NAME字段值,这里MTL_LOT_NUMBERS表示"Maintain Lot Number" DFF Lot Attributes DFF fnd_descr_flex.define(BLOCK => 'LOT_ENTRY',                               FIELD => 'DESC_FLEX',                               appl_short_name => 'INV',                               desc_flex_name => 'Lot Attributes'); Enable/Disable DFF        IF (contval > 1 OR gseg > 0) THEN        app_item_property.set_property('LOT_ENTRY.LDF', ENABLED, PROPERTY_ON);         FND_DESCR_FLEX.UPDATE_DEFINITION(BLOCK   =>'LOT_ENTRY',                                        FIELD   => 'LDF',                                        ENABLED =>'Y');       ELSE                  app_item_property.set_property('LOT_ENTRY.LDF', ENABLED, PROPERTY_OFF);       FND_DESCR_FLEX.UPDATE_DEFINITION(BLOCK   =>'LOT_ENTRY',                                        FIELD   => 'LDF',                                        ENABLED =>'N'); Required app_item_property.set_property('LOT_ENTRY.DESC_FLEX', required, property_on); app_item_property.set_property('LOT_ENTRY.DESC_FLEX', required, property_off); Read Only fnd_descr_flex.update_definition(BLOCK => 'LOT_ENTRY', FIELD => 'LDF', read_only => 'Y'); fnd_descr_flex.update_definition(BLOCK => 'LOT_ENTRY', FIELD => 'LDF', read_only => 'N'); FND_DESCR_FLEX.UPDATE_DEFINITION Syntax FND_DESCR_FLEX.UPDATE_DEFINITION(   /* Arguments that specify the flexfield location */     BLOCK=>'block_name',     FIELD=>'field_name',   /* Argument to enable or disable flexfield */     [ENABLED=>'{Y|N}',]   /* Other optional parameters  */     [VDATE=>'date',]     [TITLE =>'Title',]     [AUTOPICK=>'{Y|N}',]     [USEDBFLDS=>'{Y|N}',]     [READ_ONLY=>'{Y|N}',]     [LOCK_FLAG=>'{Y|N}',]     [HELP=>'APPL=application_short_name;             TARGET=target_name',]     [CONTEXT_LIKE=>'WHERE_clause_fragment'}      );

以Lot Entry中的两个DFF为例,一个是Maintain Lot Number DFF,另一个是Lot Attributes DFF 知道基表,查看对应DFF SELECT * FROM FND_DESCRIPTIVE_FLEXS WHERE application_table_name = 'MTL_LOT_NUMBERS'; FND_DESCRIPTIVE_FLEXS表中几个字段需要注意, APP...

Oracle EBS R12 WIP Component Issue&Return Process

1.定义BOM BOM(物料清单)是WIP的基础,BOM定义了产品的组成结构图,定义了生产特定物料所需的零件及数量。每一个零件也可能有他们的BOM结构,由此产生多层的产品结构树。 比如:一台电脑 = 1 CPU + 1 内存 + 1 硬盘 + 1 显示器  + 1 个鼠标 + 1个键盘 + 30根 数据线 + 50 个螺丝 ,这个电脑的构成就是一个BOM。另外一个硬盘可能又是一个BOM,比如 1 硬盘 = 4组磁盘 + 1个磁盘指针 + 1 个硬盘壳,同样的道理,显示器,内存...都有自己的BOM定义,这样对电脑这个BOM来说,实际上构成了一个庞大的BOM树。 这里有有一张关于黄建华文档里的自行车BOM树的截图,比较有代表性。 EBS里如何定义BOM,路径:Bill Of Materials > Bills > Bills 这里定义一个简单的BOM,1 * pt_assembly01 = 2 * pt_component01 BOM的信息保存在BOM_STRUCTURES_B和BOM_COMPONENTS_B表中,BOM_STRUCTURES_B用于保存BOM Header信息,BOM_COMPONENTS_B是BOM_STRUCTURES_B的child table,用于保存BOM Component信息,两个表通过BILL_SEQUENCE_ID字段来关联。 SELECT * FROM BOM_STRUCTURES_B WHERE ASSEMBLY_ITEM_ID =  242956; SELECT * FROM BOM_COMPONENTS_B WHERE BILL_SEQUENCE_ID in (SELECT BILL_SEQUENCE_ID FROM bom_structures_b WHERE ASSEMBLY_ITEM_ID =  242956); 关于发料方式(BOM界面,Material Control中的Supply Type) Push 推式发料,必须手工通过界面做发料 需严格控制数量或者波动比较大的物料Assembly Pull 拉式发料,装配件完工或报废时自动按标准消耗量触发 消耗比较稳定的物料Operation Pull 拉式发料,工序移动至To Move时自动按标准消耗量触发 消耗比较稳定的物料 BOM_COMPONENTS_B.WIP_SUPPLY_TYPE存储发料方式 Value Meaning 1 Push 2 Assembly Pull 3 Operation Pull 4 Bulk 5 Supplier 6 Phantom 7 Based on Bill 2.定义Routing 路径:Bill Of Materials > Routings > Routings Routings(工艺路线)最终解决的问题是生产过程中加工顺序、资源和用量的标准化。Routing是产品/半成品的生产步骤图,定义了生产特定物料所要经历的工序、加工部门(工作中心)、提前期、耗用的资源及其额定数量。 Routing头信息存储在表BOM_OPERATIONAL_ROUTINGS中,Routing的Operations信息存储在BOM_OPERATION_SEQUENCES表中,两个表通过字段ROUTING_SEQUENCE_ID字段关联。查询方法, SELECT * FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITEM_ID =  242956; SELECT * FROM BOM_OPERATION_SEQUENCES WHERE ROUTING_SEQUENCE_ID IN (SELECT ROUTING_SEQUENCE_ID FROM BOM_OPERATIONAL_ROUTINGS WHERE ASSEMBLY_ITEM_ID =  242956); 3.定义WIP Discrete Job 路径:WIP > Discrete > Discrete Jobs Discrete,离散式,一种制造方法,用于装配件的分组或成批制造。制定标准离散任务,输入核心内容:任务名称(工单号)、生产类型、装配件、工单类型、生产数量、开工时间或完工时间。 这里在Discrete Job里定义job,保存,并release这个job。 这一步会涉及到几张表WIP_ENTITIES,WIP_DISCRETE_JOBS,WIP_REQUIREMENT_OPERATIONS和WIP_OPERATIONS,这几张表都是通过WIP_ENTITY_ID相互关联. <<WIP_ENTITIES>> WIP_ENTITIES stores information about jobs, repetitive assemblies, and flow schedules. Each row includes a unique entity name, the entity type, and the assembly being built. Oracle Work in Process uses this information to control production activities and to ensure that entities with duplicate names are not created. Key Fields: WIP_ENTITY_ID:Job or schedule Identifier ORGANIZATION_ID:Organization Identifier WIP_ENTITY_NAME:WIP job or repetitive assembly name or flow schedule reference code ENTITY_TYPE:WIP entity type code 1 Discrete job 2 Repetitive assembly 3 Closed discrete job 4 Flow schedule PRIMARY_ITEM_ID:Assembly Item Item <<WIP_DISCRETE_JOBS>> WIP_DISCRETE_JOBS stores discrete job information. Each row represents a discrete job, and contains information about the assembly being built, the revision of the assembly, the job quantity, the status of the job, the material control method, accounting information, and job schedule dates. Oracle Work in Process uses this information to control discrete production. Key Fields: WIP_ENTITY_ID:Job or schedule Identifier ORGANIZATION_ID:Organization Identifier PRIMARY_ITEM_ID:Assembly Item Item STATUS_TYPE :Status of job可以通过下边的SQL查得code的意义 SELECT lookup_code,meaning FROM FND_LOOKUP_VALUES where LANGUAGE = 'US' AND Upper(lookup_type) LIKE Upper('WIP_JOB_STATUS') Value Meaning 7 Cancelled 8 Pending Bill Load 9 Failed Bill Load 10 Pending Routing Load 11 Failed Routing Load 12 Closed 13 Pending - Mass Loaded 14 Pending Close 15 Failed Close 1 Unreleased 3 Released 4 Complete 5 Complete - No Charges 6 On Hold JOB_TYPE :Type of discrete job Value Meaning 1 Standard 3 Non-standard WIP_SUPPLY_TYPE :Method of material consumption within WIP Value Meaning 1 Push 2 Assembly Pull 3 Operation Pull 4 Bulk 5 Supplier 6 Phantom 7 Based on Bill START_QUANTITY:Job start quantity QUANTITY_COMPLETED:Current job quantity completed COMMON_BOM_SEQUENCE_ID:--> BOM_COMPONENTS_B.BILL_SEQUENCE_IDCOMMON_ROUTING_SEQUENCE_ID:-->BOM_OPERATIONAL_ROUTINGS.ROUTING_SEQUENCE_ID <<WIP_REQUIREMENT_OPERATIONS>> 任务领料需求发放表,记录了工单的制程中每个站点需要投料信息,我们根据这张表生成MMTT,进而生成Material Transaction. WIP_REQUIREMENT_OPERATIONS stores information about the material requirements of jobs and schedules. Each row represents a material requirement and contains information about the component item, its usage quantities, the using department, requirement date, and the material control method. Oracle Work in Process uses this information to track the material usage of jobs and schedules. Key Fields: WIP_ENTITY_ID:Job or schedule Identifier ORGANIZATION_ID:Organization IdentifierINVENTORY_ITEM_ID:Component Item IdCOMPONENT_SEQUENCE_ID: -->BOM_COMPONENTS_B.COMPONENT_SEQUENCE_ID WIP_SUPPLY_TYPE :Method of material consumption within WIP Value Meaning 1 Push 2 Assembly Pull 3 Operation Pull 4 Bulk 5 Supplier 6 Phantom 7 Based on Bill REQUIRED_QUANTITY:Component quantity requiredQUANTITY_ISSUED:Component quantity issued SUPPLY_SUBINVENTORY:Subinventory used to supply component to WIP SUPPLY_LOCATOR_ID:Locator used to supply component to WIP SEGMENT1:Component Item segment QUANTITY_ALLOCATED:Quantity allocated QUANTITY_BACKORDERED:Quantity backordered <<WIP_OPERATIONS>> 离散作业工序(操作)表,主要记录工单工序的信息,这张表主要是和Routing Operation Sequences相对应的(表:BOM_OPERATION_SEQUENCES),Routing里有多少步,那么这个表就有多少个记录。 WIP_OPERATIONS stores information about job and repetitive schedule operations. Each row represents a specific operation and includes an operation sequence number, which orders the operations for the job or repetitive schedule. Each row also includes the assembly quantity completed at an operation, the quantity at each intraoperation step,the department associated with an operation, the scheduled start and completion dates for an operation, the operation's countpoint and backflush types and other operation attributes. In general, Oracle Work in Process uses this information to control and monitor assembly production on the shop floor. Key Fields: WIP_ENTITY_ID:Job or schedule IdentifierOPERATION_SEQ_NUM:Operation sequence number within a routing,值一般为:10,20,30...ORGANIZATION_ID:Organization Identifier OPERATION_SEQUENCE_ID:-->BOM_OPERATION_SEQUENCES.OPERATION_SEQUENCE_ID STANDARD_OPERATION_ID:-->BOM_OPERATION_SEQUENCES.STANDARD_OPERATION_ID DEPARTMENT_ID:-->BOM_OPERATION_SEQUENCES.DEPARTMENT_ID DESCRIPTION-->BOM_OPERATION_SEQUENCES.OPERATION_DESCRIPTION,工序的描述 SCHEDULED_QUANTITY:    Number of units scheduled for the operationQUANTITY_IN_QUEUE:Number of units in Queue of the operationQUANTITY_RUNNING:Number of units in To Run of the operationQUANTITY_WAITING_TO_MOVE:Number of units waiting To Move in the operationQUANTITY_REJECTED:Number of units rejected from the operationQUANTITY_SCRAPPED:Number of units scrapped from the operationQUANTITY_COMPLETED:Number of units completed by the operationFIRST_UNIT_START_DATE:First unit scheduled receipt dateFIRST_UNIT_COMPLETION_DATE:First unit scheduled completion date <<WIP_OPERATION_RESOURCES>> WIP_OPERATION_RESOURCES stores information about the resource requirements associated with job and repetitive schedule operations. Each row represents a resource requirement and contains a sequence number,the resource's unit of measure, its usage rate or amount, and several other attributes that control how it is scheduled and costed.Oracle Work in Process uses this information to schedule jobs and repetitive schedules and to charge resources to jobs and schedules. SQL  Queries SELECT * FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01'; SELECT * FROM WIP_DISCRETE_JOBS WHERE WIP_ENTITY_ID = (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01') ; SELECT * FROM WIP_REQUIREMENT_OPERATIONS WHERE WIP_ENTITY_ID = (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01'); select * from WIP_OPERATIONS WHERE WIP_ENTITY_ID = (SELECT WIP_ENTITY_ID FROM WIP_ENTITIES WHERE WIP_ENTITY_NAME = 'pt_job01'); 4.发料 接下来我们就要从库存中给工单发货,这里有两种方法发料到工单 方法1:WIP > Material Transactions > WIP Material Transactions 这种方式比较简单,直接指定库存并发料 方法2:WIP > Discrete > Component Pick Release > Component Pick Releas (Form) / (SRS) 当BOM的组件很多,使用方法一发料的话,一个个指定库存会很麻烦,Oracle EBS提供了Pick Release的方式来发料,这样用户只要指定合适的Picking Rule,那么系统会自动帮你挑库。 系统会产生一个Move Order(Move Order Type:Manufacturing Pick),这一步实际上非常类似于订单的Pick Release。 接下来去Transact Move Order界面,Transact这个Move Order就完成发料动作了。 注意使用方式一发料的话,后台是不会产生Move Order的,只有方式二才会有Move Order产生。 5.Material Transaction 这个时候可以去Material Transaction form里查看,组件已经从库存中扣减,Transaction Type=WIP Issue(Transaction_Type_id:35,WIP component issue) 这个时候MTL_MATERIAL_TRANSACTIONS表中几个JOB相关的字段 MMT.Transaction_Type_id:35 MMT.TRANSACTION_ACTION_ID:1 MMT.TRANSACTION_SOURCE_TYPE_ID:5 MMT.TRANSACTION_SOURCE_ID -->WIP_DISCRETE_JOBS.WIP_ENTITY_ID MMT.TRX_SOURCE_LINE_ID-->WIP_REQUIREMENT_OPERATIONS.OPERATION_SEQ_NUM MMT.SOURCE_LINE_ID -->MTL_TXN_REQUEST_LINES.LINE_ID MMT.Move_Order_line_ID -->MTL_TXN_REQUEST_LINES.LINE_ID 6.WIP Component Return 物料发到工单后,有可能因为各种原因要把料退回到仓库,可以使用WIP Return 路径:WIP > Material Transactions > WIP Material Transactions,Type选择 WIP Return 查看Material Transaction,就可以看到一条WIP Return的Transaction发生了,并且组件库存数量又恢复到发料前的数量。 Transaction_TYPE_ID:43(WIP Component Return) ps:不管是上边所说的WIP Issue还是WIP Return,在WIP_TRANSACTIONS表中是没有数据产生的。 WIP_TRANSACTIONS stores information about WIP resource transactions.Each row represents a single resource transaction and includes a uniquetransaction Identifier, a transaction date, the job or repetitiveschedule charged, the WIP operation and resource charges, and thenumber of units of measure applied. Oracle Work in Process uses thisinformation to track resource charges and to calculate the values stored in WIP_TRANSACTION_ACCOUNTS.

1.定义BOM BOM(物料清单)是WIP的基础,BOM定义了产品的组成结构图,定义了生产特定物料所需的零件及数量。每一个零件也可能有他们的BOM结构,由此产生多层的产品结构树。 比如:一台电脑 = 1 CPU + 1 内存 + 1 硬盘 + 1 显示器  + 1 个鼠标 + 1个键盘 + 30根 数据线 + 50个螺丝 ,这个电脑的构成就是一个BOM。另外一个硬盘可能又是一个BOM,比如 1 硬盘...

基于Item Template,导入Item数据

基于Item Template,导入Item数据 1.查找到TEMPLATE_ID SELECT * FROM MTL_ITEM_TEMPLATES_B b,MTL_ITEM_TEMPLATES_TL tl  WHERE b.TEMPLATE_ID = tl.TEMPLATE_ID  AND tl.TEMPLATE_NAME LIKE 'Purchased Item%';   2.基于模板插入Item INSERT INTO   MTL_SYSTEM_ITEMS_INTERFACE    (      process_flag,      set_process_id,      transaction_type,      organization_id,      segment1,      description,      TEMPLATE_ID    )   VALUES   (      1,    --process_flag     1,     --set_process_id 'CREATE', --transaction_type     204,  --organization_id 'PTIAN1',  --Item Segment1 'Testing Item Import With Template',  --Item description     259  --Item TEMPLATE_ID   );   INSERT INTO   MTL_SYSTEM_ITEMS_INTERFACE    (      process_flag,      set_process_id,      transaction_type,      organization_id,      segment1,      description,      TEMPLATE_ID    )   VALUES   (      1,    --process_flag     1,     --set_process_id 'CREATE', --transaction_type     207,  --organization_id 'PTIAN1',  --Item Segment1 'Testing Item Import With Template',  --Item description     259  --Item TEMPLATE_ID   );   COMMIT;    3.运行"Import Items" Program 也可以通过脚本来调用"Import Item" program,参见Script to Submit Item Import (INCOIN) using FND_REQUEST in Oracle Apps R12 4.Item数据成功生成 关于Item Interface表-MTL_SYSTEM_ITEMS_INTERFACE MTL_SYSTEM_ITEMS_INTERFACE temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory..This table contains user friendly columns which allow you to easily enter your data as well as their corresponding foreign keys. Oracle Inventory will default column values you do not enter, after performing the proper validation. For example, you enter the ORGANIZATION_CODE, and Oracle Inventory will fill in the corresponding ORGANIZATION_ID for you. It is strongly recommended that users enter the segment information for the item instead of the item number..You can also specifiy a TEMPLATE_ID to copy item attributes from..Also, if you enter REVISION, Oracle Inventory will insert a record into MTL_ITEM_REVISIONS for your item.

基于Item Template,导入Item数据 1.查找到TEMPLATE_ID SELECT * FROM MTL_ITEM_TEMPLATES_B b,MTL_ITEM_TEMPLATES_TL tl   WHERE b.TEMPLATE_ID = tl.TEMPLATE_ID   AND tl.TEMPLATE_NAME LIKE 'Purchased Item%';   2.基于模板插入Item INS...

Oracle Apps AutoConfig

Oracle Apps AutoConfig Background Oracle Apps的架构非常复杂,使用了非常多技术(或服务)。比如Apache Web server, Apache Jserv, Forms Listener servlet (或forms server) 等等,每一个服务都有着自己的配置文件,只有都设置正确了,系统才能正常运作。而且,Oracle Apps使用了许多的Profile Options(比如Applications Web Agent, Applications Framework Agent等),这些也需要都设置正确,人工管理这么多配置文件,其实并不容易,对于新人来说,学习成本很高。 所以Oracle推出了一个非常强大的工具--Autoconfig(Autoconfig是11.5.4后引入的),用于维护这些配置文件和Profile Options。我们可以认为AutoConfig是一些系列模板化配置文件的集合,用于配置出一个标准化的应用环境。 什么是AutoConfig AutoConfig是集中并简化Oracle Apps的配置管理的工具,一个自动配置EBS Instance的工具,不需要手工干预。它所需要的信息仅仅是两个存储在本地Context文件(XML类型的文件),一个是Apps Context文件,另外一个是DB Context文件。 AutoConfig在Apps层运行的话,那么它就需要读取Apps Context文件来产生所有的配置文件,并且会更新数据库的Profiles。 如果AutoConfig在DB层运行的话,那么它就需要读取DB Context文件来产生所有用于DB层面的配置文件。 AutoConfig内部其实是一组Java Class文件,这些Class文件由Shell脚本(或者perl脚本)来调用,通过模板化的配置来维护系统的配置文件。Autoconfig运行时,会用新的配置文件重写已存在的配置文件(这个新的配置文件其实是:模板配置文件+context文件,构建而成的) 总之:AutoConfig确实很好的简化了系统的配置工作。 AutoConfig脚本所在的目录 Application tier: <INST_TOP>/admin/scripts/adautocfg.sh                             (eg./u01/oracle/mc3yd213/inst/apps/mc3yd213_bej301441/admin/scripts/adautocfg.sh) Database tier: <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh                           (eg./u01/oracle/mc3yd213/db/tech_st/11.1.0/appsutil/scripts/mc3yd213_bej301441/adautocfg.sh) 运行方法 Apps Tier: sh <INST_TOP>/admin/scripts/adautocfg.sh  注意: 在运行AutoConfig的过程中,Database server和database listener必须已经启动,Apps Server应该处于关闭状态。Running AutoConfig may change your existing environment files. After running AutoConfig, you should always set the environment before you run any Applications utilities, in order to apply the changed environment variables. DB Tier: sh <RDBMS_ORACLE_HOME>/appsutil/scripts/<CONTEXT_NAME>/adautocfg.sh 注意: 在运行AutoConfig的过程中,Database server和database listener必须已经启动,其他数据库服务应该处于关闭状态。Running AutoConfig may change your existing environment files. After running AutoConfig, you should always set the environment before you run any Applications utilities, in order to apply the changed environment variables. AutoConfig工作原理 Autoconfig会涉及三类文件:Context文件,Template文件,Driver文件。 Context文件 Context文件可以说一个记录环境参数的基础文件,它存储了Apps所有的配置信息,如果需要更改某项配置,则需要首先修改Context文件的配置信息,然后在通过AutoConfig,把更改的信息更新到所有的真实配置文件中去。 Apps Context文件:<INST_TOP>/appl/admin/<CONTEXT_NAME>.xml (eg./u01/oracle/mc3yd213/inst/apps/mc3yd213_bej301441/appl/admin/mc3yd213_bej301441.xml) DB Context文件:<RDBMS_ORACLE_HOME>/appsutil/<CONTEXT_NAME>.xml(eg./u01/oracle/mc3yd213/db/tech_st/11.1.0/appsutil/mc3yd213_bej301441.xml) NOTE:<CONTEXT_NAME> = <SID>_<hostname> AutoConfig模板文件 用于生成配置文件的模板,Apps里的每一个配置文件都有一个对应的模板。模板文件中包含了很多的Tag,这些Tag最终会被Context文件中环境变量替换掉。 模板文件存放的地方:  Apps层的模板文件:<product_top>/admin/template,比如: <FND_TOP>/admin/template (eg./u01/oracle/mc3yd213/apps/apps_st/appl/fnd/12.0.0/admin/template,打开目录能看到很多tmp的模板文件) DB层的模板文件:<RDBMS ORACLE_HOME>/appsutil/template,比如<ORACLE_HOME>/appsutil/template(eg./u01/oracle/mc3yd213/db/tech_st/11.1.0/appsutil/template) driver文件 Driver文件会列出了AutoConfig模板文件路径以及模板文件对应的目标配置文件的真实路径,以及一些脚本命令。 Apps的Driver文件位于:<product_top>/admin/driver,比如: <FND_TOP>/admin/driver(eg./u01/oracle/mc3yd213/apps/apps_st/appl/fnd/12.0.0/admin/driver,里边有很多.drv文件) DB的Driver文件位于:<RDBMS ORACLE_HOME>/appsutil/template,比如<ORACLE_HOME>/appsutil/template(eg./u01/oracle/mc3yd213/db/tech_st/11.1.0/appsutil/template里的.drv文件) 每当Autoconfig运行的时候,都会在先找到Driver文件,然后按照Driver文件提供的脚本命令,模板文件,以及Context文件生成目标配置文件。 AutoConfig的日志文件 Application Tier: <INST_TOP>/admin/log/<MMDDhhmm> Database Tier:   <RDBMS ORACLE_HOME>/appsutil/log/<CONTEXT_NAME>/<MMDDhhmm>                           eg./u01/oracle/mc3yd213/db/tech_st/11.1.0/appsutil/log/mc3yd213_bej301441/05240310 <MMDDhhmm> = (month, day, hour, minute of AutoConfig run) AutoConfig配置回滚 每一次AutoConfig的运行都会产生一个回滚脚本,如果AutoConfig配置错误,你可以使用回滚脚本来恢复之前的配置。 Application Tier:     <INST_TOP>/admin/out/<MMDDhhmm> Database Tier:    <RDBMS ORACLE_HOME>/appsutil/out/<CONTEXT_NAME>/<MMDDhhmm>并且运行命令:  restore.sh(Unix) 或者restore.cmd(Windows) Autoconfig Context文件的修改 路径:System Administration > Oracle Applications Manager > AutoConfig从列表中可以看到DB层和Apps层的Autoconfig Context文件 点击Edit Parameter,可以在这里修改Context File的Parameter,在这里改Context文件应该比直接修改Context的XML文件更安全些。 从截图的页签,也可以看出,Autoconfig Context的配置主要涉及到Global,System,Local,Install,Environments,Processes,Custom几块。 Reference about AutoConfig http://www.appsdba.info/docs/oracle_apps/R12/AutoConfig.pdf 387859.1  Using AutoConfig to Manage System Configurations in Oracle Applications Release 12 http://onlineappsdba.com/index.php/2008/01/28/autoconfig-in-oracle-apps-11i-r12-12i/ 165195.1  Using AutoConfig to Manage System Configurations with Oracle Applications 11i 218089.1  Autoconfig FAQ270519.1  Customizing an AutoConfig Environment364927.1 How To Run Autoconfig On Database Tier (DB-Tier)341322.1 How to change the hostname of an Applications Tier using AutoConfig338003.1 How to change the hostname and/or port of the Database Tier using AutoConfig315674.1 How To Verify if Autoconfig is Enabled on 11.5.x391406.1 How to get a clean Autoconfig Environment

Oracle Apps AutoConfigBackground Oracle Apps的架构非常复杂,使用了非常多技术(或服务)。比如Apache Web server, Apache Jserv, Forms Listener servlet (或forms server)等等,每一个服务都有着自己的配置文件,只有都设置正确了,系统才能正常运作。而且,Oracle Apps使用了许多的Profil...

Oracle Application R12 (12.1.3) Installation on Linux(64 bit)

Oracle Application R12 (12.1.3) Installation on Linux(64 bit) Author:                         Pan Tian Creation Date:            25/05/2012 Last Updated:              Version:                         Status:                            Contents Objective                                                                                                                                                                                                              1 Download & Unzip                                                                                                                                                                                          2 Download                                                                                                                                                                                                        2 Unzip                                                                                                                                                                                                                2 MD5Checksums                                                                                                                                                                                            2 Pre-InstallTask                                                                                                                                                                                                  3 DiskSpace                                                                                                                                                                                                       3 Specific Software Requirements                                                                                                                                                                3 RPM                                                                                                                                                                                                                   3 JDK                                                                                                                                                                                                                    6 /etc/hosts                                                                                                                                                                                                        6 /etc/sysconfig/network                                                                                                                                                                             6 /etc/sysconfig/networking/profiles/default/network                                                                                                                  7 /etc/sysctl.conf                                                                                                                                                                                              7 /etc/security/limits.conf                                                                                                                                                                            8 /etc/resolv.conf                                                                                                                                                                                             8 OS Library Patch for Oracle HTTP Server(on Oracle Linux 5, RHEL 5 and Oracle Linux 6 only)                                       8 Link to Motif library in Oracle Application Server 10.1.2 (on Oracle Linux 5 and RHEL 5 only)                                        8 OS User & Group                                                                                                                                                                                           8 Installationprocess                                                                                                                                                                                        10 ExportDisplay                                                                                                                                                                                             10 ./rapidwiz                                                                                                                                                                                                     10 Add Env parameter to .bash_profile file                                                                                                                                              19 Default DB & Apps password                                                                                                                                                                 20 R12 Directory Structure                                                                                                                                                                             21 EBS 12.1.3 upgrade                                                                                                                                                                                         23 Download patch 9239090                                                                                                                                                                        23 Documents                                                                                                                                                                                                    23 Installationprocess                                                                                                                                                                                    24 Issues & Solutions                                                                                                                                                                                          27 RW-50004: Error code received when running external process.                                                                                                 27 RW-50015: Error: - HTTP Listener is not responding.                                                                                                                     27 Enter the location for the disk labeled: Oracle Applications Rapid Install – RDBMS Disk 1                                              27 While adpatch EBS 12.1.3, encounter ORA-03113: end-of-file on communication channel                                                28 Reference                                                                                                                                                                                                           29 MetalinkNotes                                                                                                                                                                                             29 Articles                                                                                                                                                                                                           29 Objective Step by Step instructions to install Oracle Applications R12(12.1.1) on Oracle Enterprise Linux 5 are described in this article. This is a single node installation, meaning that the database, all product directories and AD core directories, and all servers (concurrent processing, forms, and Web) are installed on a single node under one common APPL_TOP. The database node and the Applications node can be installed on the same machine. This type of installation is generally used for small systems or for demonstration purposes. More commonly, the database node is installed on one machine, and the Applications node on another machine. This provides improved manageability, scalability, and performance. This document will take Linux 64 bit platform as example. And my hardware configuration is Item Configuration CPU 2 * Intel(R) Xeon(R) X5670 2.93GHz RAM 6 G Disk Space /u01  300 GB /u02  200 GB /         100 GB Download & Unzip Download Official Download Link https://edelivery.oracle.com/ For Oracle Internal User, you can also download from link: https://sac.us.oracle.com/download/index.html (NOTE: Choose proper platform and 32 bit/64 bit download files) Download Tips: Use the following command for Batch download, firstly collect all URLs required and put it in a text file, say download.txt, one URL on a line wget -i download.txt (-i means Read URLs from file.) Unzip Create stage area - for downloaded installation pack, just unzip all zip files under stage area directory StageR12, eg. unzip B53824-01_1of4.zip -d /u02/ StageR12 unzip B53824-01_2of4.zip -d /u02/ StageR12 … … After unzip, File Path in StageR12 like following, MD5 Checksums Strongly suggest you perform MD5 check against the stage area. The md5 checksums for the staged directory structure can be used to validate the software integrity. Do this by running the md5sum program against the stage area using the oracle created checksum file. Note for can observe you have any problem with your stage or not(Corrupted zip etc) MD5 Checksums for R12.1.1 Rapid Install Media (Doc ID 802195.1) Pre-Install Task Disk Space File System Space Requirements for Standard Installation Node Space Required Applications node file system (includes OracleAS 10.1.2 Oracle Home, OracleAS 10.1.3 Oracle Home, COMMON_TOP, APPL_TOP,and INST_TOP) 35 GB (50 GB on HP-UX Itanium) Database node file system (Fresh install) 55 GB Database node file system (Vision Demo Database) 208 GB (210 GB on HP-UX Itanium) Stage area(unzipped files) 47 GB The total space required for a standard system (not including the stage area) is 85 GB for a fresh install with a production database, and 233 GB for a fresh install with a Vision Demo database. You can use below Unix command to check disk space df –h Specific Software Requirements The following maintenance tools must be installed on machine, and their locations specified both in the PATH of the account that runs Rapid Install and in the PATH of the accounts that will own the database tier and application tier file systems. Operating System Required Maintenance Tools Linux x86 ar, gcc, g++, ld, ksh, make, X Display Server Linux x86-64 ar, gcc, g++, ld, ksh, make, X Display Server HP-UX Itanium ar, cc, aCC, make, X Display Server HP-UX PA-RISC ar, cc, aCC, make, X Display Server IBM AIX on Power Systems (64-bit) ar, cc, aCC, make, X Display Server Microsoft Windows Server (32-bit) ar, cc, ld, linkxlC, make, X Display Server Sun Solaris SPARC (64-bit) ar, ld, make, X Display Server RPM Per Note: 761566.1 First, you should check your Linux Kernel, to determine your linux version, enter: uname –r So my Linux is Linux 6, For Linux 6, Required Packages is GA (6.0) or higher of Oracle Linux 6 is required The following packages (or versions of packages) are not part of the OS distribution media and must be downloaded separately (from http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/) for Oracle Linux 6 and installed manually:     openmotif21-2.1.30-11.EL6.i686 (32-bit)1     xorg-x11-libs-compat-6.8.2-1.EL.33.0.1.i386 (32-bit) The following packages must be installed from the Oracle Linux 6 distribution media:     binutils-2.20.51.0.2-5.11     gcc-4.4.4-13.el16.x86_64     gcc-c++-4.4.4-13.el16.x86_64     glibc-2.12-1.7.el6.i686 (32-bit)     glibc-2.12-1.7.el6.x86_64     glibc-common-2.12-1.7.el6.x86_64     glibc-devel-2.12-1.7.el6.i686 (32-bit)     glibc-devel-2.12-1.7.el6.x86_64     libgcc-4.4.4-13.el6.i686     libgcc-4.4.4-13.el6.x86_64     libstdc++-devel-4.4.4-13.el6.i686     libstdc++-devel-4.4.4-13.el6.x86_64     libstdc++-4.4.4-13.el6.i686     libstdc++-4.4.4-13.el6.x86_64     make-3.81-19.el6.x86_64     gdbm-1.8.0-36.el6.i686     gdbm-1.8.0-36.el6.x86_64     libXp-1.0.0-15.1.el6.i686     libXp-1.0.0-15.1.el6.x86_64     libaio-0.3.107-10.el6.i686     libaio-0.3.107-10.el6.x86_64     libgomp-4.4.4-13.el6.x86_64     sysstat-9.0.4-11.el6.x86_64     util-linux-ng-2.17.2-6.el6.x86_64     unzip-5.52-3.0.1.el5.x86_642     compat-libstdc++-296-2.96-144.el6.i686     compat-libstdc++33-3.2.3-69.el6.i686 Additionally, the following RPMs are required for the database tier running 11gR2 (users must upgrade the bundled 11gR1 DB to 11gR2 either before or after installing 12.1) on the database tier:     compat-libstdc++-33-3.2.3-69.el6.x86_64     elfutils-libelf-devel-0.148.1-el6.x86_64     kernel-uek-headers-2.6.32-100.28.5.el6.x86_64     libaio-devel-0.3.107-10.el6.x86_64     unixODBC-2.2.14-11.el6.i686     unixODBC-devel-2.2.14-11.el6.i686     unixODBC-2.2.14-11.el6.x86_64     unixODBC-devel-2.2.14-11.el6.x86_64     xorg-x11-utils-7.4-8 Note:     1: The openmotif package version must be 2.1.30 (for example, openmotif-2.3.3-1 is not supported). 2: This unzip package (available from EL 5 distribution) is only required for purposes of running Rapid Install to unzip the required EBS files as part of the installation. Be sure you have above rmp on your system, you can use below command to check whether RPM packages are installed or not rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n" | grep gcc q = query a = all What’s the RPM:The Red Hat Package Manager (RPM) is a toolset used to build and manage software packages on UNIX systems. Distributed with the Red Hat Linux distribution and its derivatives (CentOS is 100% compatible rebuild of the Rehat Enterprise Linux). The RPM files represent application or package that you can install on Linux system. JDK JDK 6 is bundled with Oracle E-Business Suite version 12.1.1. You do NOT need to install the JDK separately. /etc/hosts For Oracle Linux 4, 5 and 6, and Red Hat Enterprise Linux 4 and 5: Verify that the /etc/hosts file is formatted as follows: 127.0.0.1 localhost.localdomain localhost [ip_address] [node_name].[domain_name] [node_name] /etc/sysconfig/network Verify that the /etc/sysconfig/network file is formatted as follows: HOSTNAME=[node_name].[domain_name] /etc/sysconfig/networking/profiles/default/network If the /etc/sysconfig/networking/profiles/default/network file exists, remove it. If you changed any files in the previous steps, restart the system. /etc/sysctl.conf Edit the /etc/sysctl.conf file to configure your kernel settings. After editing the file, use the "sysctl -p" command or restart the system to invoke the new settings. Note: If the current value for any parameter is higher than the value listed in the following table, then do not change the value of that parameter. The following table lists the kernel settings for Oracle Linux 4, 5 and 6, Red Hat Enterprise Linux AS/ES 4 and 5, and SUSE Linux Enterprise Server 9 and 10: 1: set using the following entry in the /etc/sysctl.conf file: kernel.sem = 256 32000 100 142 2: On the server running the EBS Database, this kernel parameter must be the lesser of half the size of the physical memory (in bytes) and 4294967295 3: values recommended for the local port range may need to be adjusted according to the specific needs of the user's environment in order to avoid port conflicts. /etc/security/limits.conf Open the /etc/security/limits.conf file and change the existing values for "hard" and "soft" parameters as follows. Restart the system after making changes. Note: If the current value for any parameter is higher than the value listed in this document, then do not change the value of that parameter.   * hard nofile 65535   * soft nofile 4096   * hard nproc 16384   * soft nproc 2047 /etc/resolv.conf Add or update the following entries to these minimum settings in the /etc/resolv.conf file on each server node: options attempts:5 options timeout:15 OS Library Patch for Oracle HTTP Server(on Oracle Linux 5, RHEL 5 and Oracle Linux 6 only) Download and apply the patch 6078836 from My Oracle Support to fix an issue with the Oracle HTTP Server (missing libdb.so.2) bundled with the E-Business Suite technology stack. Note that this patch (which includes a required operating system library) must be applied before installing Oracle E-Business Suite. Without this patch, after installation, HTTP Server can not start. Link to Motif library in Oracle Application Server 10.1.2 (on Oracle Linux 5 and RHEL 5 only) Perform the following command (as root on your system) to update a required link to a Motif library prior to relinking or patching the 10.1.2 Application Server Oracle Home: # unlink /usr/lib/libXtst.so.6 # ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6 OS User & Group Generally for PROD instance, we need create two OS user for installation, one user is used to manage DB, the other is used to manage Apps. Since our instance is a TEST instance, so for simple, we just create one user to manage DB and Apps, Here we use ‘oracle’ as user, use ‘oinstall’  as group. # groupadd oinstall -g 2000 # useradd -g oinstall -u 2000 oracle # chown -R oracle:oinstall /u01/oracle chown changes the user and/or group ownership of each given file. -R, operate on files and directories recursively Installation process Export Display To ensure we have a graphical installation process, we can export server to our local linux which have X window system. If your server have installed X window, then you can ignore this step. (ptian) ptian- env | grep ALTDISPLAY ALTDISPLAY=rws60004sgd.us.oracle.com:50 (First, in our local linux, Check our local linux’s host and port) (ptian) ptian- ssh admin@bej301441.cn.oracle.com admin@bej301441.cn.oracle.com's password: Last login: Thu May 31 21:32:49 2012 from 10.182.114.9 (SSH to server which we intend to install EBS) [admin@bej301441 ~]$ export DISPLAY=rws60004sgd.us.oracle.com:50 ./rapidwiz If you intend to use standard installation mode, you need login as root user.  [root@bej301441 rapidwiz]#cd /u02/StageR12/startCD/Disk1/rapidwiz [root@bej301441 rapidwiz]# ./rapidwiz Rapid Install Wizard is validating your file system...... 4 dvd labels found Rapid Install Wizard will now launch the Java Interface..... Default Oracle EBS Ports Port numbers can be modified during installation or may be automatically incremented by x during installation where x is a number 1 to 100 (typical less than 10). Port number ranges are often a grouping of 3, 4, 5, or 6 contiguous ports in the specified range. LoginTest: http://<host_name>.<domain_name>:port/OA_HTML/AppsLogin Add Env parameter to .bash_profile file .bash_profile is a bash shell that is started when login. For DB user, Modify /.bash_profile, add your DB env file path into it, eg. . /u01/oracle/mc3yd213/db/tech_st/11.1.0/mc3yd213_bej301441.env For Apps user, Modify /.bash_profile, add your Apps env file path into it, eg. . /u01/oracle/mc3yd213/apps/apps_st/appl/APPSmc3yd213_bej301441.env Default DB & Apps password Database: SYS/change_on_install SYSTEM/manager Application Schema passwords: APPS Schema: APPS/APPS GWYUID User: APPLSYSPUB/PUB Guest User: GUEST/ORACLE Application Users: SYSADMIN/SYSADMIN MFG/WELCOME OPERATIONS/WELCOME SERVICES/WELCOME MRC/WELCOME HRMS/WELCOME R12 Directory Structure ===For Applmgr User=== $APPL_TOP R12:<APPS_BASE>/apps/apps_st/appl NOTE: Oracle Applicaitons product files, including $INV_TOP, $AU_TOP.... $COMMON_TOP R12:<APPS_BASE>/apps/apps_st/comn(Common files such as Java&HTML) NOTE: $JAVA_TOP is under $COMMON_TOP $ORACLE_HOME R12:<APPS_BASE>/apps/tech_st/10.1.2 ===For Oracle User=== $ORACLE_HOME R12:<ORACLE_BASE>/db/tech_st/10.2.0 NOTE:Oracle Home for 10g RDBMS ORADATA R12:<ORACLE_BASE>/db/apps_st/data NOTE:Oracle 10g database files ===INSTANCE HOME=== INST_TOP R12:<APPS_BASE>/inst/apps/<context_name> NOTE:Configuration,logs,Certificates. EBS 12.1.3 upgrade Download patch 9239090 Download 12.1.3 patch: https://updates.oracle.com/download/9239090.html NOTE: the linux 32-bit and the 64-bit patch are one and the same. 32-bit Linux  patches are applicable for 64-bit Linux OS as well. Documents Official Documents Oracle E-Business Suite Release 12.1.3 Readme (Doc ID 1080973.1) Database Preparation Guidelines for an Oracle E-Business Suite Release 12.1.1 Upgrade [Doc ID 761570.1] Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86 [Document 761564.1] External references http://my.opera.com/jafferappsdba/blog/2011/12/17/upgrade-from-ebs-12-1-1-to-12-1-3 http://oracleminds.wordpress.com/2011/12/22/how-did-i-upgrade-our-oracle-ebs-from-r12-1-1-to-12-1-3/ 2. Installation packs * Patch 9239089 * Patch 9239090 * Patch 9239095 * Patch 9817770 * Patch 9966055 Installation process 1.Set Application Environment source /d01/oracle/VIS/apps/apps_st/appl/APPSVIS_adc6180205.env 2. stop all application process. /d01/oracle/VIS/inst/apps/VIS_adc6180205/admin/scripts/adstpall.sh apps/apps 3. set environment to RDBMS oracle_home. source /d01/oracle/VIS/db/tech_st/11.1.0/VIS_adc6180205.env 4.Shutdown Database as following sqlplus / as sysdba SQL>shutdown immediate; 5.Stop Database Listener lsnrctl stop VIS 6. edit the init parameter file in the database(Using 11gR1 follow doc.761570.1) /d01/oracle/VIS/db/tech_st/11.1.0/dbs/initVIS.ora vi initVIS.ora add (_disable_fast_validate=TRUE). 7. set pga and sga size in init parameter file (initVIS.ora file) pga_aggregate_target=2G Note: (If errorlike below: ORA-04030: out of process memory when trying to allocate 822904 bytes (pga heap, kco buffer) ORA-07445: exception encountered: core dump [dbgtfdFileWrite()+48] set the _pga_max_size initialization parameter to a larger value as follows: _pga_max_size=104857600 and restart your database.) 8.start Database Listener lsnrctl start VIS 9. restart the database. sqlplus / as sysdba sql> startup pfile='/d01/oracle/VIS/db/tech_st/11.1.0/dbs/initVIS.ora' 10. Use AutoPatch to apply R12.AD.B.DELTA.3 Patch 9239089 Pre Install Steps as per Patch readmefile & Doc.1077769.1 (i)create a dir on $ORACLE_HOME/appustil/admin /d01/oracle/VIS/db/tech_st/11.1.0/appsutil/admin (ii)Copy adgrants.sql from this patch directory to $ORACLE_HOME/appsutil/admin cp /u01/12.1.3/9239089/admin/adgrants.sql /d01/oracle/VIS/db/tech_st/11.1.0/appsutil/admin/adgrants.sql (iii) Set the database environment /d01/oracle/VIS/db/tech_st/11.1.0/VIS_adc6180205.env (iv)logon to sqlplus and run adgrants.sql with apps schema password as below sqlplus /nolog sql> connect as sysdba sql> @/u01/oracle/ORCL/db/tech_st/11.1.0/appsutil/admin/adgrants.sql apps 11. Disable maintanance mode using adadmin utility 12. set application environment. Run Patch 9239089 using adpatch *u9239089.drv (Check Log file in /u01/oracle/ORCL/apps/apps_st/appl/admin/ORCL/log/u9239089_12122011.log) 13. Compile APPS schema Run adadmin > Compile/Reload Applications Database Entities menu >Compile APPS schema. *Choose "No" at the following prompt:Run Invoker Rights processing in incremental mode [No] 14.Apply patch 9239090 using adpatch *u9239090.drv (When adrelink error chose "Yes" to continue) 15. Post Step (There is no any pre or post install steps) Apply Patches *Patch 9817770 *Patch 9966055 16.Remove _disable_fast_validate=TRUE parameter from initVIS.ora file 17.Disable Maintanence mode 18.Run Autocnfig both database and application tier 2. Query patches applied in table ad_applied_patches ad_bugs 3. License applications Issues & Solutions RW-50004: Error code received when running external process. Error Message:” RW-50010: Error: - script has returned an error:   1 RW-50004: Error code received when running external process.  Check log file for details. Running Database Install Driver for mc2yd213 instance” When installation progress go to 33%, then raise RW-50004 Error, Generally it is due to OS user/group you used may not have correct priority to installation folder. You can check details from logs. RW-50015: Error: - HTTP Listener is not responding. Error Message:” RW-50015: Error: - HTTP Listener is not responding. The service might not have started on the port yet. Please check the service and use the retry button.” It means you did not apply patch 6078836. Download and apply the patch 6078836 from My Oracle Support to fix an issue with the Oracle HTTP Server (missing libdb.so.2) bundled with the E-Business Suite technology stack. Enter the location for the disk labeled: Oracle Applications Rapid Install – RDBMS Disk 1 While installation, it pop us a window which ask to select install disk. Error Message: “Select the root directory of the required Rapid Install Disk Enter the location for the disk labled: Oracle Applications Rapid Install - RDBMS Disk 1” This is very big mistake, log will tell you reason Disk platform missmatch. required platform: LINUX_X86-64 Disks for platform: Linux While adpatch EBS 12.1.3, encounter ORA-03113: end-of-file on communication channel From adpatch,” STATUS_MESSAGE  ------------------------------------------------------------------------------  Running UTL_RECOMP.RECOMP_PARALLEL...    declare  *  ERROR at line 1:  ORA-03113: end-of-file on communication channel    ERROR:  ORA-03114: not connected to ORACLE  “ Solution: Edit the init parameter file in the database(Using 11gR1 follow doc.761570.1) , /d01/oracle/VIS/db/tech_st/11.1.0/dbs/initVIS.ora Add _disable_logging=false  Reference Metalink Notes Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86 [ID 761564.1] Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.1.1) for Linux x86-64 [ID 761566.1] MD5 Checksums for R12.1.1 Rapid Install Media (Doc ID 802195.1) How to locate the log files and troubleshoot Rapid Install for Release 12.0.x and 12.1.x (Doc ID 452120.1) Articles Oracle Applications Installation Guide: Using Rapid Install R12(12.1.1) http://docs.oracle.com/cd/E18727_01/doc.121/e12842.pdf Oracle Applications (12.1.1) Installation on RHEL 5 http://heliosguneserol.wordpress.com/2010/06/11/oracle-applications-12-1-1-installation-on-rhel-5/ Oracle Applications(Apps) R12 (12.1.1) Installation on Linux http://www.freeoraclehelp.com/2011/11/oracle-applicationsapps-r12-1211.html Appsdba.info R12 Install http://www.appsdba.info/docs/oracle_apps/Installation/R12/snir12-ln.pdf E-Business Suite 12.1.1 Standard Installation on Linux OEL 5.3 http://gerardnico.com/wiki/ebs/installation You can download full doc viahttp://www.slideshare.net/tianpan/oracle-ebs-r1213installationlinux64bitpantian

Oracle Application R12 (12.1.3) Installation on Linux(64 bit) Author:                         Pan Tian Creation Date:            25/05/2012 Last Updated:              Version:                         Status...