tp's blog | 一路走来

You can download the lastest patch form support.oracle.com

• Oracle E-Business Suite Release 12.1.3 Release Update Pack(Patch 9239090)

For installation instructions for EBS 12.1.3, see:

• Oracle E-Business Suite Release 12.1.3 Readme(Note 1080973.1)

This release update pack (RUP) combines error corrections, statutory/regulatory updates, and functionality enhancements into a consolidated, suite-wide patch set.  This release is a maintenance pack and can only be installed on top of an existing EBS 12.1 environment.

What are the advantages of Drop Shipment Orders?

Here are the Details as per Mark

1.Order Entry

Here the activity is entering process where oe_order_headers_all (flow_status_code as entered) oe_order_lines_all . The order is booked as DROP SHIP

2. Order Booking

3. The Purchase Release program passes information about eligible drop-ship order lines to Oracle Purchasing.The interface table which gets populated is
po_requisitions_interface_all

4. After Purchase Release has completed successfully, run Requisition Import in Oracle Purchasing to generate purchase requisitions for the processed order lines. The Requisition Import program reads the table po_requisitions_interface_all validates your data, derives or defaults additional information and writes an error message for every validation that fails into the po_interface_errors table.The validated data is then inserted into the requisition base tables po_requisition_headers_all,po_requisition_lines_all,po_requisition_distributions_all.Then use autocreate PO fuctionality to create purchase orders and then perform receipts against these purchase orders

7. After the goods are successfully received invoices for vendors are created in accounts payables as in normal purchase orders.

8. Invoices are generated for customers In account receivables.

9. oe_order_lines_all (flow_status_code ’shipped’, open_flag “N”)

10. oe_order_lines_all (flow_status_code ‘closed’, open_flag “N”)

DropShip的工作场景：

企业A向客户B销售产品，但是企业A本身并不生产该产品，而是向供应商C来采购，并且在大部分的情况都是C向B直接发货，也就是说企业A大部分的交易都是贸易，属于贸易型企业，除了贸易型企业，对于一些集团公司的销售公司或门店也采用这种销售方式。即‘左手倒右手’。

1.create a item,and ensure this item have purchasable and salable properties.

(according step 4 error,we should also type a value to list price field)

2.Create a sale order,and ensure ‘Source Type’  is ‘External’.

if source type is ‘Internal’,it stand for that you will sale item from your stock.

if source type is ‘External’,it stand for that you will sale item from other supplier.(Drop ship Way)

3.The Purchase Release program passes information about eligible drop-ship order lines to Oracle Purchasing.The interface table which gets populated is

po_requisitions_interface_all

4.After Purchase Release has completed successfully, run Requisition Import in Oracle Purchasing to generate purchase requisitions for the processed order lines. The Requisition Import program reads the table po_requisitions_interface_all validates your data, derives or defaults additional information and writes an error message for every validation that fails into the po_interface_errors table.The validated data is then inserted into the requisition base tables po_requisition_headers_all,po_requisition_lines_all,po_requisition_distributions_all.Then use autocreate PO fuctionality to create purchase orders and then perform receipts against these purchase orders

After requisition import,In PR summary form,Try to find your PR.

But can Not find my PR.

Execute below query,

SELECT e.error_message

FROM po_requisitions_interface_all i, PO_INTERFACE_ERRORS e

WHERE i.TRANSACTION_ID = e.INTERFACE_TRANSACTION_ID AND item_id = 25875

Error is:This item is missing a list price in the item master setup window. Please enter a list price greater than zero (0) in the item master.

Then go back to Master item form,type a value to List price,

Becasue Req Import does not pick up interface records that previously failed.we also need update process_flag from ‘ERROR’ to ‘FUTURE’.

Script like:

update po_requisitions_interface_all set process_flag = ’FUTURE’ where item_id = 25875

Then re-run Requisition Import request.PR got generated sucessfully.

5.Auto create PO

6.PO approve

7.Receipt&Delivery

9.After receipt,a sale order issue txn will automaticlly generate.

Sale order line is closed.

Once customer receipt the PO,Then you can take the AR which created by SO to customer,

Customer will give amount 1350 to you.

在‘System Administrator’ responsibility下，Help > About Oracle Appliations可以看到更多的信息。
以下蓝色字体部分，就是在普通responsibility下无法看到的信息。

——————————–
Oracle Applications
Copyright (c) 2004 Oracle Corporation,
Redwood Shores, California.
All Rights Reserved.

—————————————-
Login
—————————————-
Site : tk10203
Application : System Administration
Responsibility : System Administrator
Security Group : Standard
User Name : MFG

—————————————-
Database Server
—————————————-
RDBMS : 10.2.0.3.0
Oracle Applications : 11.5.10.2
Machine : rws60124rems
User : APPS
Oracle SID : mz1yd102
System Date : 01-DEC-2009 22:08:25
Database Server PID : 7827
Session SID : 377
SERIAL# : 64378
AUDSID : 5947370
Database CPU Usage (in secs) : 15.96

—————————————-
Forms Server
—————————————-
Oracle Forms Version : 6.0.8.27.0
Application Object Library : 11.5.0
Machine : RWS60124REMS
Forms User CPU (secs) : 19.800989
Forms System CPU (secs) : 1.601756
Forms Process ID : 4548

—————————————-
Forms Server Environment Variables
—————————————-
AU_TOP : /slot/ems2570/appmgr/mz1yd102appl/au/11.5.0
FDBDMCHK : [Unset]
FDFGCXDBG : [Unset]
FDSQLCHK : [Unset]
FDUDEBUG : [Unset]
FNDNAM : APPS
FND_TOP : /slot/ems2570/appmgr/mz1yd102appl/fnd/11.5.0
FORMS60_APPSLIBS : APPCORE FNDSQF APPDAYPK APPFLDR GLCORE HR_GEN HR_SPEC ARXCOVER
FORMS60_CATCHTERM : 1
FORMS60_ERROR_DATETIME_FORMAT : [Unset]
FORMS60_ERROR_DATE_FORMAT : [Unset]
FORMS60_FORCE_MENU_MNEMONICS : 0
FORMS60_MAPPING : http://rws60124rems.us.oracle.com:8070/OA_TEMP
FORMS60_MMAP : [Unset]
FORMS60_OUTPUT : /slot/ems2570/appmgr/mz1yd102comn/temp
FORMS60_OUTPUT_DATETIME_FORMAT : [Unset]
FORMS60_OUTPUT_DATE_FORMAT : [Unset]
FORMS60_PATH : /slot/ems2570/appmgr/mz1yd102appl/au/11.5.0/resource:/slot/ems2570/appmgr/mz1yd102appl/au/11.5.0/resource/stub
FORMS60_RESOURCE : [Unset]
FORMS60_TIMEOUT : 5
FORMS60_USER_DATETIME_FORMAT : DD-MON-RRRR HH24:MI:SS
FORMS60_USER_DATE_FORMAT : DD-MON-RRRR
FORMS60_USE_CBO : [Unset]
NLS_DATE_FORMAT : DD-MON-RR
NLS_DATE_LANGUAGE : AMERICAN
NLS_LANG : AMERICAN_AMERICA.UTF8
NLS_NUMERIC_CHARACTERS : ,.
ORACLE_HOME : /slot/ems2570/appmgr/mz1yd102ora/8.0.6
ORACLE_PATH : [Unset]
ORA_NLS_CHARSET_CONVERSION : [Unset]
TNS_ADMIN : /slot/ems2570/appmgr/mz1yd102ora/8.0.6/network/admin/mz1yd102_rws60124rems
TWO_TASK : mz1yd102

—————————————-
Current Form
—————————————-
Form Application : Application Object Library
Form Name : FNDSCSGN
Form Path : UNKNOWN
Form Version : 11.5.124
Form Last Modified : $Date: 2006/10/24 10:54  $

—————————————-
Scheme Display Profiles
—————————————-
Java Look and Feel : ORACLE
Java Color Scheme : BLAF
Color Scheme Indicator : GENERIC
Indicator Colors : Y

—————————————-
Forms
—————————————-
APPSTAND : 11.5.33
FNDSCSGN : 11.5.124

—————————————-
Form Menus
—————————————-
FNDMENU : 11.5.51

—————————————-
Forms PL/SQL
—————————————-
APPCORE : 11.5.157
CUSTOM : 11.5.5.1150.1
FNDSQF : 11.5.111
GHR : 11.5.150
GLOBE : 11.5.46
GMS : 11.5.230.90.7
IGILUTIL2 : 11.5.97
IGILUTIL : 11.5.29
OPM : 11.5.33.115100.2
PQH_GEN : 11.5.118
PSA : 11.5.162
PSAC : 11.5.16
PSB : 11.5.24
VERT : 11.5.6

INV:Inventory->Accounting Close Cycle->Inventory Accounting Period
PO:Purchasing->Setup->Financials->Accounting->Open and Close Periods
AR:Accounts Receivable->Control->Accounting->Open/Close Periods

最近好忙，都在拼了命的赶进度，很少有时间写写日志。
尽管很忙，还是每天抽出点时间(基本都是睡觉前用手机看的)，把阿朱的《走出软件作坊》看完了，收获颇多，等果断时间有空了，把读书心得总结下。
《走出软件作坊》所描述的行业就是企业应用这个行当，算下来我也在这个行当混了4年了，所以阿朱笔下很多的情景，还真的有种似曾相识的感觉。
我也会在以后的文章里对这本书做一些总结。

上周宜家定的桌子和柜子到货了，新桌子好爽，宽敞，台式机笔记本都放上去也不觉得拥挤。买这张桌子，主要是看到网络达人Keso上传的图片后，顿生买意。

发件人 BlogPic

MTL_TRANSACTION_ACCOUNTS(MTA)

• Holds the accounting information for each material transaction in MMT

• In most cases, each costed row in MMT should have at least 2 or more rows in MTA to represent the transaction Debit and Credit

• Join to MMT by TRANSACTION_ID

• Troubleshooting columns to verify:

• GL_BATCH_ID = -1

• REFERENCE_ACCOUNT = NOT NULL

• Module

• INLTGL: Transfer to GL

• Form

• Inv>Transactions>Material Distributions

• CSTFQITD

发件人 BlogPic

发件人 BlogPic

ORG_GL_BATCHES (OGB)

• Stores row for each batch of transactions interfaced to GL_INTERFACES

• Troubleshooting columns to verify:

• OGB.GL_BATCH_ID = MTA.GL_BATCH_ID

• ACCOUNT_PERIOD_ID

• ORGANIZATION_ID

• Module

• INLTGL: Transfer to GL

• Form

• Inv>Accounting Close Cycle>View General Ledger Transfers

• INVTTGGL

GL_INTERFACE (GLI)

• Stores row for each batch of transactions interfaced to GL_INTERFACES

• Troubleshooting columns to verify:

• REFERENCE1 = MTA.GL_BATCH_ID / ORGANIZATION_ID

• REFERENCE21 = MTA.GL_BATCH_ID

• REFERENCE22 = MTA.ORGANIZATION_ID

• Run Import to GL > GL Vision Ops (USA) Responsibility

• Designate Source (i.e. Inventory) and Optionally Group ID

• Journals>Import>Run

• Inquiry on Batch

• Inquiry>Journal

• Source = Inventory

• Period =

发件人 BlogPic

发件人 BlogPic

发件人 Blogger 图片

发件人 Blogger 图片

GL_SETS_OF_BOOKS
stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

HR_ALL_ORGANIZATION_UNITS
HR_ALL_ORGANIZATION_UNITS holds the definitions that identify business groups and the organization units within a single business group.Additional information about classifications and information types for each organization is held in HR_ORGANIZATION_INFORMATION.

MTL_PARAMETERS
MTL_PARAMETERS maintains a set of default options like general ledger accounts; locator, lot, and serial controls; inter-organization options; costing method; etc. for each organization defined in Oracle Inventory.

Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.

You specify which manufacturing calendar the organization will use (CALENDAR_CODE), as well as the set of exceptions to this calendar
(CALENDAR_EXCEPTION_SET_ID).

NEGATIVE_INV_RECEIPT_CODE indicates whether this organization will allow negative balances in inventory. It is used in the transaction system to ensure, for example, that you do not over-issue a particular item (if you have indicated that negative balances are not allowed).

For lot number and serial number auto-generation, you can specify whether you want the numbers generated from defaults defined at theorganization level or the item level (LOT_NUMBER_GENERATION,SERIAL_NUMBER_GENERATION). You can also specify uniqueness constraints for lot numbers (LOT_NUMBER_UNIQUENESS) and serial numbers (SERIAL_NUMBER_TYPE).

Among the accounting defaults that can be defined here are valuation accounts, receiving accounts, profit and loss accounts, and inter-organization transfer accounts.

Dataload is not an Oracle tool. Well, that sells, which means if you can come up with a better tool, you may sell it and big money will be waiting ;-D

Oracle provides some tools to help data migration.

API
1. Rocjing mentioned API, and said it is the fastest. That is true. API is not a tool, but a term, i.e. an abbreviation for Application Procedure Interface. An API is really a (hook) function/procedure provided in Oracle code to create data in Oracle. For example, if you want to create a “user” in Oracle, you will call FND_USER_PVT.create_user(…) (a lot of parameters). This procedure is an API. Using API is really for you to code a sql script, and call the API’s in your script. In another word, this is coding and programming.

INTERFACE
2. Another popular method is through interface tables. Almost all Oracle products provide interface tables for high volumn transaction data. For example, AP has ap_invoice_interface, GL has GL_journal_interface, etc. (Invoices are the basic transactions in AP, journals are the basic transactions for GL, etc.) AP may also have an interface table for suppliers, which are also very common for AP. How do you populate these interface tables? Mostly through uploading spreadsheets, i.e. xsl files (MicroSoft Excel). The key here is a spreadsheet and its target interface table have the same column structure (including column names, column type, column count, etc.).

Oracle also provides tools like ADI to help you to upload data from spreadsheets to interface tables.

Usually after you load your data into an interface table, you will submit a concurrent program to validate the data and transfer that into the real transaction table.

Hence, this is really a spreadsheet-ADI-interface table-import process combination method.

–MANUAL TYPING
3. It is pretty hard to deal with every high volumn data issue in an ideal way. So manual typing is sometimes the only way. Even in this case, users should find ways to use, say, batches, default values, existing auto triggers (like distribution sets in AP), etc. to reduce the amount of manual typing.

–DATALOAD
4. Dataload is also kind of tools that help, but of course, it is not ideal in every case. Dataload is very similar to Oracle’s Regression Test tool, which was very popular even inside Oracle years ago. Nowadays it is not that attractive any more. People just have to live that.

Hope this is of a little help for you guys.

What you said was like Interface Tables, which were in Oracle for years. If you have not used that, you should find a chance to try. I would say all interface tables were created to support spreadsheets (XSL files), and I believe that is still the best solution.

Oracle E-Business Suite产品主要ERP模块名如下。

财务系统模块:

◆Oracle 总帐管理（GL）
◆Oracle 应付帐管理（AP）
◆Oracle 固定资产管理（FA）
◆Oracle 应收帐管理（AR）
◆Oracle 现金管理（CE）
◆Oracle 项目会计（PA）
◆Oracle 财务分析（OFA）

分销系统模块:

◆Oracle 库存管理（INV）
◆Oracle 采购管理（PUR）
◆Oracle 销售定单管理（OE）
◆Oracle 销售&市场管理（SM）
◆Oracle 销售补偿管理（SC）
◆Oracle 售后服务管理（SR）
◆Oracle 销售分析（OSA）

制造系统模块:

◆Oracle 计划管理（MPS/MRP）
◆Oracle 能力计划管理（CAP）
◆Oracle 制造数据管理（BOM）
◆Oracle 车间生产管理（WIP）
◆Oracle 成本管理（CST）
◆Oracle 项目制造（PJM)
◆Oracle 质量管理（QM)

其他系统模块:

◆Oracle 设备管理（EM）
◆Oracle 人事管理（HR）
◆Oracle 薪金管理（PAYROLL）
◆Oracle 系统管理（SYSTEM ADMIN）
◆Oracle 预警（ALT）
◆Oracle 多维数据分析/商业智能系统（OLAP/BIS）
◆Oracle 桌面集成（ADI）