Archive
Component Backflush with Oracle MTL Transaction Interface Table
Recently, at work, we noticed the Work Order-less completions inserted into our MTL_TRANSACTIONS_INTERFACE table were not spawning component backflush transactions. It was quite the mystery to us, as they had been working in Oracle 11i (11.5.10). At some point during our migration to R12 (12.1.3) the backflush transactions had stopped. We searched Oracle’s documentation, support knowledge base, and all over the internet. We couldn’t find a solution.
One of my colleagues got in touch with someone she used to work with. They were able to provide a private API: APPS.wip_flowUtil_priv. My colleague tried the API call explodeRequirementsToMTI and was able to get the components to backflush. We thought all was well.
However, upon further testing, I noticed something strange. The original Assembly was getting stuck in the interface. And when resubmitted, I saw both the assembly and another round of components getting transacted. What was going on?
Further testing provided me with the answer: the originally inserted assembly record was getting slightly altered by the material transaction worker before getting marked in error. Altered to a point which would spawn component backflush transactions if marked for processing and then processed. I tried inserting a fresh assembly record into the MTL_TRANSACTIONS_INTERFACE table with the updated fields and was able to get the components to backflush, automatically, without any errors.
We now have a functioning R12 Work Order-less completion interface which inserts assembly completion records into the MTL_TRANSACTIONS_INTERFACE table. Records which are correctly formed and spawn component issue (backflush) transactions when processed by the material transaction manager (Process transaction interface).
These are the two fields that were changed by the transaction manager:
OPERATION_SEQ_NUM from -1 to NULL
TRANSACTION_BATCH_SEQ from NULL to 1
Below is the insert statement we are using, with Oracle 12.1.3 changes noted.
Fixed: Oracle iSetup XML Uploading Error
For the past month at work we have been experimenting with Oracle iSetup as a way to migrate Oracle R12 configurations from instance to instance. We had been using Rapid Solutions (Rapid e-Suite) in Oracle 11i in the past, but wanted to try something quicker, more robust, and seeded with Oracle.
The iSetup module worked pretty well in our proof of concept testing. But we ran into a problem when we started migrating our multi organization/business unit configurations. We have multiple business units because we are sharing the instance with another division at our company. Migrating configurations with iSetup brings all the ORGs and sites along with each configuration, however we only wanted our division’s configurations migrated. To solve the problem, we started editing the exported XML before loading it into the new instance.
Editing XML is relatively time consuming and prone to error when editing in a text editor compared to a program designed to edit XML. We tried to use the free Microsoft XML Notepad 2007, however, loading the exported files was causing iSetup to fail on upload. I found the problem and solved it as described below.
Conditions
- Oracle iSetup module in Oracle E-Business suite R12
- XML Documents edited by Microsoft XML Notepad 2007
Symptoms
- iSetup reports error when uploading edited XML files
- Log file as seen here:
oracle.xml.parser.v2.XMLParseException: Start of root element expected.
at oracle.xml.parser.v2.XMLError.flushErrors1(XMLError.java:320)
at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:341)at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:303)
at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:205)
at oracle.apps.az.r12.api.BC4JAPI.createAPIEntityItems(BC4JAPI.java:330)
at oracle.apps.az.r12.extractor.cpserver.UploaderContextImpl.validateExtract(UploaderContextImpl.java:170)
at oracle.apps.az.r12.extractor.cpserver.UploaderContextImpl.upload(UploaderContextImpl.java:105)
at oracle.apps.az.r12.extractor.cpserver.UploaderCp.runProgram(UploaderCp.java:74)
at oracle.apps.fnd.cp.request.Run.main(Run.java:157)
Problem
- Oracle XML files are saved in ASNI text encoding even though the XML header specifies UTF-8
- Oracle iSetup expects ASNI encoded XML files contained within uploaded ZIP file
- Microsoft XML Notepad 2007 saves XML files in UTF-8 encoding
Solution
- Download extraction ZIP file generated by Oracle iSetup
- Extract ZIP file to your computer
- Edit and save extracted XML file in Microsoft XML Notepad
- Open saved file in Notepad (Text Editor)
- Save As ASNI encoding
- Replace existing file within original ZIP file
- ZIP file compression levels and algorithms must reamin intact
- 7-ZIP supports replacing files within an existing file using drag and drop functionality
- Upload updated ZIP file to Oracle iSetup as a new extraction.
- Load extraction into source instance
Conclusion
The Cost of Promotional Sales
Grocery retailers frequently use promotional sales to lure customers into stores. Retailers hope customers will purchase higher margin products while they shop for the promoted items. Chicago grocers Jewel-Osco, subsidiary of SUPERVALU, and Dominick’s, subsidiary of Safeway, both follow use this marketing strategy, changing promotions twice a week. The promotional sales may increase customer traffic, but they may also lead to the demise of the store. I suspect, retailers that use promotional sales, on every day products sold year round, increase the cost of doing business and decrease supply chain efficiency.
Used by many grocers, promotional sales are the activities, materials, devices, and techniques used in the advertising and marketing of products. I separated their cost into two different categories; direct costs and indirect costs. I see the money spent on the processes and material to implement the promotional sale as direct costs, and the money lost due of the effects of the promotional sales are considered indirect costs. From my point of view, both of these costs are significantly high, high enough to possibly outweigh any benefit of the promotional sales. If I were managing a retail grocery operation, these are the items I would consider when deciding whether or not to continue utilizing promotional sales.
ERP and the Business of Change
I saw an article over at SmartChange™ about ERP and the Business of Change. It got me thinking of the culture and business practices I witnessed at my last job at USG. All three main points Marcelino Sanchez reviewed hit very close to home.
Individual Change… An individual may be confronted with a new user interface, a new “best practice”, a different way to make decisions, or any number of new requirements. When this is the case, individuals will tend to experience one or more of the following five key factors: fear, anxiety, confusion, loss of control, or erosion of power or influence in the organization. The more an individual feels any of these emotions, the less likely they are to accept the new way of doing things. Lack of acceptance will diminish or delay the benefits of the ERP effort…
Installing Oracle r12: On To Linux!
I downloaded the Oracle Enterprise Linux distribution, and Linux E-Business r12 installer from Oracle. I am sure RCN Cable loves that I have now downloaded over 100 GB in the last 4 days. After my Windows Server 2003 debacle, I decided to run the Windows Vista recovery CDs I made to reinstall Vista the way it came from Gateway. That way I would know the exact network drivers and such.
Installing Oracle r12: Oracle is CRAZY!
Installing Oracle in Windows Server 2003 has not been easy thus far. After all my tests passed last time, I started the instillation. To my udder shock, and amazement, the install FAILED again! Actually I was not too terribly shocked. I expected it really.
ED HAYES 