Monthly Archives: March 2010

How to Get AP Invoice Lines Transferred Via Mass Additions to FA?

Today a colleague asked me why her FA AP invoice was not transferred to FA via Mass Addition. I’m a little lazy today and it is a long history for me to study the wipe of FA and AP, so just go to Metalink and google the documents. It’s so fast to find out this document 243671.1 as below:

For the Mass Addition Create process to import an invoice line distribution to
Oracle Assets, these specific conditions must be met:

1. The line is charged to an asset account.
2. The asset account is used for an existing asset category as either the asset
clearing or the CIP clearing account.
3. The Track as Asset check box is checked. (It is automatically checked if the
account is an asset account).
4. The invoice is approved.
5. The invoice line distribution is posted to GL from Payables.
6. The GL date on the invoice line distribution is on or before the date
specified for the create program.
7. Accounts Payables set up must be tied to the same GL set of books as the
corporate book for which you want to create mass additions.

Run the script MASSCREATE.sql in Note:198232.1
Validation script for Invoices in Mass Addition Process to ensure all the
above conditions are met.

For the script of masscreate.sql, it is just for 11.0 which most of oracle users have not used. For 11.5, please query 337787.1.

Different Exchange Rate in PO Header and PO Distribution Line

I may refer that if you change the exchange rate in PO header, the one in PO distribution line would not change correspondingly. This means if you don’t change the rate in PO distribution line manually, the rates are different. Then what happens if the situation likes that?

Against my colleague’s, a nice person, investigation and my testing this morning, it depends on “Invoice Match Option” in the shipment line. If it is set to match with purchase order, the PO receiving accounting entry is calculated with the rate in PO distribution line as well as PO invoice matching accounting entry. If it is set to match with receipt, the PO receiving entry is calculated with the rate in PO header as well as PO invoice matching accounting entry.

Handling Bank Charges

We have two way to handle the bank charges. One is entering the bank charges in the AR Receipt Form while the other is creating a AR credit memo for bank charges and apply it with the original invoice. Personally I prefer the first way very much.

As you can adjust revenue account in credit memo in second way, the accounting entries can be exactly same between the two ways. But in the second way, you must specify the customer in the credit memo as the same as the original invoice, then if you run some report (e.g. AR transactions register), this customer’s revenue and balance would be wrong unless you consider the accounting entries (filter out the bank charge account).

Well, whatever the accounting entries, documents and even the working steps are all much much more clear.

Above analysis is on the base of a legal transactions, which means the bank charges are not charged to customer. But in China, as you know, a lot of illegal merchant requests an extra fee if you would like to use credit card to pay. How to handle it from Accounting respect? Very easy, just create the receipt without any bank charges and the receipt amount should be customer’s payment amount – physically bank charges + requested extra fee. Generally, physical bank charges equals to the request extra fee, so receipt amount = customer’s payment amount.

Query a Specific User’s Authorized Concurrent Requests

Somebody always asked that if I have this report, which responsibility I can run this report in, what I can do in the system, etc. There’s no a separate form to show you these question. I prepared a simple report to answer these questions by SQL Developer and export it to an XML file. If it is helpful for your works, feel free to download, extract it and import to your SQL developer.

Download: User’s Requests

To import the xml file, from here!

Reference:  The previous post “Oracle SQL Developer Report“.

Using Multiple Cash GL Account for One Bank Account

You may know that you can set up cash GL account in bank account setup and AR payment method (Receipt Class) setup. And meanwhile the bank account is the setup of operating unit level. It means the account combination will be used for AP payment, Refund and also AR receipt within the specific operating unit.

Now we received a requirement that the user have several departments in the operating unit and one bank account (physical bank account) is used for all departments. In their system, the accounting flexfield likes “CompanyCode.DepartmentCode.*.NatureAccount.*”. They need to identify the department code in the cash GL account during AP payment and AR receipt accounting entries. How to set up to fulfill her/his requirement?

For AR receipt, we can fulfill this requirement by creating more “Payment Method” for one bank account because the cash GL account within payment method can override the cash GL account within bank account.

But for AP payment, there’s no cash GL account field in the payment document level; we can only set up it in bank account level. Someone ever told me that the designation of AR is better than the one of AP. From this point, it looks trues. I

Must we create multiple bank accounts in the system for the same bank account in the real world? It looks not a good solution.

Oracle SQL Developer Report

The report functionality in SQL developer is a great tool to document and flexify your frequently used SQL statement.  You can define your report in SQL developer and export it to an xml file which could be shared with your colleagues or friends.

First of all, you need to open the report tab in the navigation sidebar.
You can see there are a lot of pre-seeded reports which help you get a lot of basic information of your database.

Second, add a new report.

Third, enter the basic information and edit your SQL statement for your report. Note that you can some placeholder for the report parameter as “:XXXX“.

Third, add binds (report parameter) by changing to “Binds” tab. Note that bind name should be as the same as your placeholder like “XXXX” .
When you enter the report parameter, it is ok to use “%”.

At last, test and apply your report.

After definition, you can always run the report by double clicking the report and entering the parameter.

RMA Autoinvoice

RMA (Return Material Authorization) is not normally created in sales order form by copying the original SO (sales order), then auto-invoiced to AR credit memo once whose goods is received. The credit memo in AR will apply the original SO’s AR invoice automatically.

If, during autoinvoice process running, the system found that the original SO’s AR invoice has no balance or has no enough balance to apply with the new created credit memo,  the RMA credit memo in AR will not be created while an error message with necessary information will be written to the request output. You need to manually unapply some amount (equal to RMA closed amount) from the receipt to let AR invoice has some balance, then run the autoinvoice process again. After doing that, the credit memo of RMA will be created and auto applied with just opened AR balance.

Closed/Finally Closed PO/Cancelled in uninvoiced receipt report

Some user often report that there are some closed PO or finally closed or even cancelled POs are still in Uninvoiced Receipt Report. How to handle it?

For closed PO, just re-open it and then you can receive it, RTV it or invoice it.

For cancelled PO,  create an AP invoice with the amount “0” and match it with the PO receiving, then add a distribution line to reverse the matching line or transfer the matching line to another account.

For finally PO, nothing can do except select the “Include Closed PO?” parameter as “No” when submit report.

From metalink, Oracle Uninvoiced Receipt Report always showns the openning receiving transactions with the status of “Closed to Invoice”, “Closed to receiving”.  But for “Closed” and “Finally Closed” opening receiving transactions, you can filter out them through selecting parameter of the request.





Problems after Zen Cart installation

To compare with osCommerce, I also  installed another very famous e-commerce open source website template – Zen Cart ( ), but after installing it I can’t open the shopping homepage and admin homepage. Only a blank page, even no error message. Why?

Google it and found I need to install a debug plug-in to get the error message firstly. The debug plug-in is published by Zen Cart in the link of

Against the debug plug-in, I got the three error message:

[14-Mar-2010 01:32:20] PHP Deprecated:  Assigning the return value of new by reference is deprecated in /var/www/html/zencart2/includes/classes/seo.url.php on line 49
[14-Mar-2010 01:32:20] PHP Deprecated:  Function ereg() is deprecated in /var/www/html/zencart2/includes/classes/db/mysql/query_factory.php on line 147
[14-Mar-2010 01:32:20] PHP Fatal error:  Cannot redeclare date_diff() in /var/www/html/zencart2/includes/functions/functions_general.php on line 1479

I ignore the first two, strictly, it is just a warning message.

For the third, I looks a little complex. I googled it and found a patch from Zen Cart again. This error just appears on PHP 5.3.x. The un-patched Zen Cart 1.3.8(a)  is not support php 5.3.x. To download the patch and the installation guide, you can refer to the link of

Next step – I need to find out a beautiful template for Zen Cart. 🙂

Install phpMyAdmin 3.3.1

After installing apache, php, mySQL in my local environment, I need to install a phpMyAdmin to help me manage mySQL database. Below is the steps I executed,

1.  Create a new folder called phpmyadmin under your apache publish folder whose default is /var/www/html/

2. Extract the downloaded phpmyadmin package to the new folder

3. Config the phpmyadmin

  • Open the terminate to create and open
  • Change/add the below lines

4.  Restart apache then open http://localhost/phpmyadmin

Note: You can set up as 777 permission mode(755 ok), otherwise you will meet error message as “Wrong permissions on configuration file, should not be world writable!”

You may meet this error when you first log into phpMyAdmin

For second error, you need to install php mbstring extension by running “# yum install php-mbstring” in terminal, inserting “” to php.ini and then restarting the apache service.

For the first error,  click “here” hyper-link to get the check list, and change the file against the check list with your own requirement.

Share host’s folder with guest in Sun VirtualBox

My host is Windows XP and my guest is Fedora 12. I would like to share some folders in my host

1. Go to host share folder from Device -> Share folders, and record down the share name.

2. Go back to guest and open the terminal, execute the command with root account. Note that you must refer to the folder name in step 1.

Then  you can directly use the files in the host from /mnt/share.

Install osCommerce on linux

osCommerce is an open source e-commerce shop solution. I should be interesting and meaningful to try it if you want to build your own on-line shop. The first step is installation.

1. Install the Linux (fedora 12) with MySQL, Apache and PHP.

2. Install/Update PHP with GD, run below command with root account
# yum install php-gd
# /etc/init.d/httpd restart

3. Copy your download folders and files to your apache home folder.

4. Create user, database; grant user’s privileges in MySQL.

5. Visit http://localhost/catalog/install/

6. Enter the MySQL database information. You need to create a new database called oscommerce and created a new user called oscommerce in step 4.
7. Set up web server

8. Set up your store
9. Finished
Then you can have a look at your store by clicking “Catalog” button or continue to set up your store by clicking “Administration” button.

Rapidly familiar with a workflow

If one user ask or colleague ask you what will happens if the approval notification time out, but you don’t know if the workflow has been customized, what will you do? Check the workflow with below options as soon as possible.

1. Check the workflow process and original code. You can open the workflow process through below path and drill down the process. But if you want to see the codes, e.g. procedures, you need workflow builder and SQL Developer etc. It is a time-consumed tasks.

2. Reproduce a requisition/PO and submit for approval in Test environment. Log in the system with Sysadmin account, find out the workflow, skip the notification step with the result of “Time out” in Activity Details window, then come back to the requisition/PO to see what happened. From my own experience, it looks a very efficient method.

3. Combine above two ways.