be happy and make others to be happy: SYSTEM ADMINISTRATOR

[SYS] No Organizations Are Currently Defined

After setting a new Operating Unit (OU), I encountered this problem when i tried to open any forms that are related to Inventory Organization.
Steps I used in organization setup (has to be in sequential mode):
(1) Setup new Set of Book
(2) Setup new Location
(3) Setup new Legal Entity
(4) Setup new Operating Unit
(5) Setup new Inventory Organization
(6) Setup new responsibility
(7) Setup Profile Option for new responsibility

Everything is smooth till I click on the "Change Organization" in my new responsibility. It prompted me "No Organizations are currently defined.". These happened to other forms that use inventory organization as well, such as Material Transactions, Receipts, etc.
This has something to do with Organization Access. After checked Organization Access, ORG_ACCESS table is empty. Setup has been verified many times, nothing was missed out.
We finally discovered it was a customization on standard view, ORG_ACCESS_VIEW. This is root cause of the issue.

[SYS] Utilities:Diagnostics

To disable the password promting when i click "Examine", set the Profile Option to value "Yes" for Utilities:Diagnostics.

[SYS] Form Personalizations - Set Property Value for Descriptive Flexfield

Assuming you have have 3 flexfields for each line of record, but for setting the value for Target Object, there is only 1 Target Object available for 3 flexfields, e.g. {something}.DESCFLEX, how to set value for 3 of them?

Ans: At the Value field, enter something like this => Value1.Value2.Value3

Separate each value by a dot.

[SYS] Form Personalizations - Setting Property Value by Calling a SQL Query

Can be done.

Enter the value as below:

=(SELECT AGENT_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=:RCV_TRANSACTION.PO_NUMBER)

:RCV_TRANSACTION.PO_NUMBER is a Target Object ("Item" Type) name, which you can refer after you clicked on "Insert Item Value..." button. It can also be found under Target ObjectLOV with Object Type "Item".

[SYS] Java Cache Caused Internet Explorer Crashed

I was trying to launch the Oracle form (in Java applet), IE crashed. I tried on Firefox, it did not get launched at all. Restarted my system, problem still there.

Before I decided to call Helpdesk to examine my system, I found a trick that can save me a lot of time dealing with Helpdesk.

Checked "Control Panel", "JInitiator 1.3.1.29" (your version might be different), "Cache" tab. My total file size in JAR Cache folder has exceeded the limit. I have then removed all cache files in that folder, increased my size limit, and it works!!!

[SYS] Trick - Maximise Form

Due to "bad" design of Oracle Form UI, the grid information in Oracle form will not maximise eventhough I maximised the form. 1 typical example is Material Transaction form.

There a trick to "force" the grid to grow bigger, and then fit to the maximised form.

First, maximise the form.

Then, move the mouse in between 2 fields until a double-headed arrow appears.

Double Click.

You will see this magic works.

[SYS] Concurrent Program Always in Pending Status

Some times we found out a submitted request is always in Pending status, we need to know if the manager is running fine. To determine which concurrent manager is handling a specific concurrent program, run this,

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'

[SYS] Default Type: Profile

We have different value in Default Type in concurrent program parameter, Constant, Current Date, etc. I want to share about "Profile" in this post.

For Default Type Profile, the Default Value indicates Profile Name (In Application Developer responsiblity, Name field in Profile). Note that the User Profile Name is what we see in the System Administrator responsibility.

The value which returns to the concurrent program is the value for the Profile Name, either user, responsibility, or other levels.

SQL:

SELECT profile_option_name, user_profile_option_name
FROM apps.fnd_profile_options_vl
WHERE profile_option_name = 'MY_CUSTOM_PROFILE_NAME'

profile_option_name is referring to Profile Name, user_profile_option_name is referring to User Profile Name.

[SYS] FND: Default Request Days

"FND: Default Request Days" profile decides the default value shown in "Select the Number of Days to View" field after you choose View->Requests

[SYS] PHASE_CODE and STATUS_CODE in FND_CONCURRENT_REQUESTS Table

It has been a bit confusing, hence i summarized the fields' values and meaning as follows:

PHASE_CODE
P - Pending
C - Completed
R - Running

STATUS_CODE
Q - Standby //Scheduled
R - Running
C - Normal
D - Cancelled
E - Error
G - Warning
I - Normal //Inactive

[SYS] Best Practise - To Update a Record Value in Setup Form

Read a documentation, it mentioned the best practise to update a record value during Oracle setup (at form level). Instead of bulk query and update 1 record, query the specific record, update it.

You know why? This is to prevent potential bug on the form that will update all records even you update 1.

[SYS] Where is Value Set Used?

Value Set in Oracle stores set of values, so that it can be used in elsewhere, in Descriptive Flexfield Segments, Key Flexfield Segments and even Concurrent Program Parameters.How do we know the where-used?

Ans: Navigate to Application->Validation->Set, open the form, query the value set, click "Usages" button.

[SYS] Default Value for Organization Code

To set default value for Organization Code in the concurrent program parameter, use this.

select organization_code from org_organization_definitions where organization_id=:$PROFILES$.mfg_organization_id

[SYS] Hard to Find the Flexfield Title and Application?

We need to trace back and check the flexfield setup, some time it's a bit difficult to guess the title name.Click on the record, go menu Help->Diagnostics->Examine, change the Block value to "$DESCRIPTIVE_FLEXFIELD$", check the field Value. E.g If it shows "Flexfield Segment Values (Application Object Library)", means the Descriptive Flexfield Segments Title is "Flexfield Segment Values"

[OM] R12: Search Sales Order Records by DFF

I want to search Order / Order Line info by using a DFF value.

Option 1: Under Order Organizer, if search by header DFF, go "Quote/Order Information" tab, click on the little rectangle box at the bottom right, enter search string.

If search by line DFF, go "Line Information", click on the little rectangle box at the bottom right, enter search string.

Option 2: In Sales Orders form, if search by header DFF, click on the header, press F11, click on the DFF box, enter search string, press Ctrl+F11.

If search by line DFF, click on the line, press F11, click on the DFF box, enter search string, press Ctrl+F11.

[SYS] R12: Enable Audit Trail

Step to enable AuditTrail:

(1) AuditTrail->Install
ONT Oracle Order Management Account

(2) AuditTrail->Groups
Application Name: Order Management
Audit Group: XX_ABC_GROUP
Group State: Enabled
...
User Table Name: OE_ORDER_LINES_ALL

(3) AuditTrail->Tables
User Table Name: OE_ORDER_LINES_ALL
Table Name: OE_ORDER_LINES_ALL
Application: Order Management
Column: UNIT_SELLING_PRICE, etc

After completed above steps, run "AuditTrail Update Tables". It will then add additional columns into the table and create a new trigger on it.

Schedule "Audit History Consolidator" to collect the audit data.

[SYS] R12: Browser Crashed When Both R11i and R12 Runs Concurrently

Browser: IE7

I opened R12 instance. Then followed by launching R11i applet in the same machine, browser crashed. Both R11i and R12 can't be opened at the same time.

Issue fixed after I turn off "Proxy Server" in IE.

Tools->Internet Options->Connections->LAN settings

[SYS] R12: Profile Option, Enable Advance Pricing for Intercompany Invoice

Set "INV:Advanced Pricing for Intercompany Invoice" to Yes.

Note that additional license needs to be purchase from Oracle.

[SYS] R12: Password

In R12, password is case sensitive.

[SYS] Given a List of Records (varchar) Returned From a SQL Statement, Which is Ordered First, Which is Ordered Last

Given below SQL,

SELECT

category_concat_segs

FROM apps.mtl_categories_v

WHERE structure_name LIKE 'Purchasing%'

AND disable_date IS NULL

AND category_concat_segs LIKE 'ABC.DEF%'

Return results were,

ABC.DEF1

ABC.DEF2

ABC.DEF3

ABC.DEF4

ABC.DEF5

I want to get only 'ABC.DEF1' & 'ABC.DEF5' from a single SQL statement. These will be used for my Item Category approval rule setup in Approval Group, low range and high range respectively.

Revised SQL below:

SELECT MIN(category_concat_segs), MAX(category_concat_segs)

FROM apps.mtl_categories_v

WHERE structure_name LIKE 'Purchasing%'
AND disable_date IS NULL
AND category_concat_segs LIKE 'ABC.DEF%'

[SYS] Security Rule - Block From Entering, What About Viewing?

You can enter only certain range of Cost Center once you set Security Rule up, but you CAN still view the document (e.g. PR Summary) that was created using a Cost Center that is out of you range in Security Rule.

[SYS] Login Page Filled with User ID Automatically

For normal scenario, you type in the URL for Oracle Apps instance, click Apps Logon Links, then E-Business Home Page, enter your Username andPassword, you will see the HTML (jsp) menus.

To simplify the steps, you first login to HTML menus, click logout, copy the URL from the Address field in your browser. That is the address you use to access the login page, with your Username defaulted.

[SYS] Session Timeout Issues

Try to increase the value for the following profile options.

ICX:Session Timeout - set it to number of minutes before timeout

ICX: Limit time - set it to number of hours before timeout

[SYS] Item Cost Table

BOM.CST_ITEM_COSTS - table to store item cost

Important Fields:

cost_type_id (1-Froze, 2-Pending)

based_on_rollup_flag (1-Yes, 2-No)

[SYS] FND_CONCURRENT.SET_COMPLETION_STATUS

To set concurrent program completion status, use FND_CONCURRENT.SET_COMPLETION_STATUS in your SQL file. However, will it work if your executable is PL/SQL package? Yes, it will still work but you need to define 2 extra parameters in your procedure (first 2).

e.g.

Procedure XXX (errbuf VARCHAR2(100), retcode INTEGER, ...)

[SYS] How to Revert My Start Page?

You have set the start page in Oracle Applications. On each successful login, it will brings you to the page. Now, you change your mind, you want to either disable the start page or change to other page. You could not get back to the HTML Preferences screen, as the start page will bring you directly to the specific Oracle form.

Ans: At any responsibilities, navigate Edit -> Preferences -> Profiles, query Profile Name "Applications Start page", change or remove the "User Value".

[SYS] Personalize

At Help -> Diagnostics -> Custom Code -> Personalize, you can customize the form by using the event triggered Actions, either set a property, or display a custom message, launch a form or even submit a concurrent program. It is a scaled down version of all functions available in typical Oracle Form Builder, but it is still powerful. More importantly, you can manage your customization in a better manner.

[SYS] SQL for Internal Requisition (IR)

select 'IR' as req_type,pla.attribute1 as lot_no,mpv.project_number,pha.segment1 doc_no,
pla.line_num,pda.distribution_num dist_num,pha.description doc_type,trunc(pha.creation_date) ord_date,

trunc( pla.need_by_date) pro_date, msi.segment1 item_no,pla.item_revision item_rev,

msi.description item_desc,pla.quantity, pla.unit_price,
pla.quantity_delivered qty_rcvd, nvl(pla.quantity,0)-nvl(pla.quantity_delivered,0) os_qty,
nvl(pla.closed_code,'OPEN') closed_code
from po.po_requisition_headers_all pha, po.po_requisition_lines_all pla, po.po_req_distributions_all pda, apps.mrp_project_v mpv, apps.mtl_system_items msi, gl.gl_code_combinations gl
where pha.type_lookup_code='INTERNAL'
and pha.authorization_status='APPROVED'
and pla.destination_organization_id in (100,101) --destination organization, parties that perform receiving
and pla.source_organization_id=103 --source organization, party that supply the itemand pha.requisition_header_id=pla.requisition_header_id
and pla.requisition_line_id=pda.requisition_line_id
and pda.code_combination_id=gl.code_combination_id
and pla.item_id=msi.inventory_item_id
and pla.destination_organization_id=msi.organization_id
and pda.project_id = mpv.project_id (+) --if project is used
order by req_type,doc_no,line_num,dist_num;

[SYS] PO_APPROVALLIST_S1

To customize PR or PO approval logic, this is the package you need to look into.

If you are customizing only the approval list, there is minimal change need to be done here as you just need to prepare the list of approvers and call this standard package.

[SYS] Personalize HTML page (JSP)

In conventional Oracle form, we are allowed to perform personalization (very powerful customization function in 11.5.10). What about the interface that uses HTML page (JSP)? I never thought that this can be done. If Yes, it will be a fantastic feature in Oracle Applications.

Yes, this can be done.

Turn these 2 profile options on (set value to "Yes"):

FND: Personalization Region Link Enabled

Personalize Self-Service Defn

Once those been turned on, you will be able to see extra link on the JSP page with word "Personalize". Get into it and you can see different value for Original Definition, Function, Site & Organization.

[SYS] To Turn On / Off the Forward-to Person LOV in PR Notification Screen (Using Personalization)

1) Assign and then go to responsibility : Functional Administrator

2) Go to Personalization Tab

3) Enter the Document Path , ie : /oracle/apps/fnd/wf/worklist/webui/NtfUserRG

4) Click on the Pencil Icon under Manage Personalizations

5) Then select the personalization that you want to delete / deactivate and then click on Deactivate

[SYS] Column View Get Reset After Each Scroll Down

In Oracle form, my query returns many rows and columns. I was interested in only 1 particular column, which is column number 8 (for example), I scroll to the right and stop at column 8. And now I wanted to check the value in column 8 for all rows. I need to scroll down. For every scroll down action, the view was reset, e.g. column 8 is gone (out of my view) and it resets to column 1. It was really frustrating.

What i did: Highlight value in column 8, perform a "Page Down" using keyboard instead of mouse scroll down.

[SYS] Effective Date for Responsibilities in Users Form

I entered today's date in the "To" Effective Date field for a responsibility in Users form. Can I still access the responsibility today? Or it will only block me tomorrow?

Ans: I could not access the responsibility with immediate effect. This is not a standard behavior in other forms, as different form behaves differently.

[SYS] View Output

I want to View Output for the request submitted by my colleague, button was grayed off. How to enable it?

Change the Profile Option value for Concurrent:Report Access Level. Default value is "User", which means View Output is only accessible by the particular user that submitted the request. Set it to "Responsibility", now the View Output button is enabled (provided I select the same responsibility that my colleague used).

[SYS] Action Required: Document Manager Failed With Error Number 3

PR preparer received an email with above subject. Email details as follows:

The Document Manager failed with error number 3.
Error Number 3 = Unknown Error.
Contact your system administrator.
Error Messages:
POXDMACTION-100: ORA-00054: resource busy and acq
#################
Findings:The PR was still in "In Process" status and NOT been forwarded to the next approver. This problem might happen intermittently due to Oracle workflow was too busy to process all the requests.
Go Workflow Administrator -> Administrator Workflow -> Status Monitor, search by Item Key and Type Internal Name ("REQAPPRV" in this case). You will find the workflow completed with error. Retry the workflow and it should completed normal this time.

[SYS] A Scheduled Request

A Scheduled Request submitted by Person A, can Person B who has the "System Administrator" responsibilities change the scheduled timing? (Request -> View)

Ans: Yes

[SYS] Workflow Administrator, Status Monitor, User Key?

What is the User Key referring to in the "Monitor Search" for workflow? For POAPPRV internal name, it is referring to Purchase Order number, whereas forREQAPPRV internal name, it is NOT referring to PR number, but it is blank.

[SYS] Compile Oracle Form

OS: Unix/Linux

(1) Copy your form (fmb file) into $AU_TOP/forms/US folder

(2) Navigate to $AU_TOP/forms/US folder

(3) f60gen module=myform.fmb userid=apps/mypassword output_file=$PO_TOP/forms/US/myform.fmx

Third step will compile the fmb file into fmx file, which is a executable from Oracle Applications.

[SYS] No Organizations Are Currently Defined

After setting a new Operating Unit (OU), I encountered this problem when i tried to open any forms that are related to Inventory Organization.
Steps I used in organization setup (has to be in sequential mode):
(1) Setup new Set of Book
(2) Setup new Location
(3) Setup new Legal Entity
(4) Setup new Operating Unit
(5) Setup new Inventory Organization
(6) Setup new responsibility
(7) Setup Profile Option for new responsibility

Everything is smooth till I click on the "Change Organization" in my new responsibility. It prompted me "No Organizations are currently defined.". These happened to other forms that use inventory organization as well, such as Material Transactions, Receipts, etc.
This has something to do with Organization Access. After checked Organization Access, ORG_ACCESS table is empty. Setup has been verified many times, nothing was missed out.
We finally discovered it was a customization on standard view, ORG_ACCESS_VIEW. This is root cause of the issue.

[SYS] Utilities:Diagnostics

To disable the password promting when i click "Examine", set the Profile Option to value "Yes" for Utilities:Diagnostics.

[SYS] Form Personalizations - Set Property Value for Descriptive Flexfield

Assuming you have have 3 flexfields for each line of record, but for setting the value for Target Object, there is only 1 Target Object available for 3 flexfields, e.g. {something}.DESCFLEX, how to set value for 3 of them?

Ans: At the Value field, enter something like this => Value1.Value2.Value3

Separate each value by a dot.

[SYS] Form Personalizations - Setting Property Value by Calling a SQL Query

Can be done.

Enter the value as below:

=(SELECT AGENT_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=:RCV_TRANSACTION.PO_NUMBER)

:RCV_TRANSACTION.PO_NUMBER is a Target Object ("Item" Type) name, which you can refer after you clicked on "Insert Item Value..." button. It can also be found under Target ObjectLOV with Object Type "Item".

[SYS] Java Cache Caused Internet Explorer Crashed

I was trying to launch the Oracle form (in Java applet), IE crashed. I tried on Firefox, it did not get launched at all. Restarted my system, problem still there.

Before I decided to call Helpdesk to examine my system, I found a trick that can save me a lot of time dealing with Helpdesk.

Checked "Control Panel", "JInitiator 1.3.1.29" (your version might be different), "Cache" tab. My total file size in JAR Cache folder has exceeded the limit. I have then removed all cache files in that folder, increased my size limit, and it works!!!

[SYS] Trick - Maximise Form

Due to "bad" design of Oracle Form UI, the grid information in Oracle form will not maximise eventhough I maximised the form. 1 typical example is Material Transaction form.

There a trick to "force" the grid to grow bigger, and then fit to the maximised form.

First, maximise the form.

Then, move the mouse in between 2 fields until a double-headed arrow appears.

Double Click.

You will see this magic works.

[SYS] Concurrent Program Always in Pending Status

Some times we found out a submitted request is always in Pending status, we need to know if the manager is running fine. To determine which concurrent manager is handling a specific concurrent program, run this,

SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name = '&USER_CONCURRENT_PROGRAM_NAME'
AND NVL (cqc.concurrent_queue_id, 0) = cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'

[SYS] Default Type: Profile

We have different value in Default Type in concurrent program parameter, Constant, Current Date, etc. I want to share about "Profile" in this post.

For Default Type Profile, the Default Value indicates Profile Name (In Application Developer responsiblity, Name field in Profile). Note that the User Profile Name is what we see in the System Administrator responsibility.

The value which returns to the concurrent program is the value for the Profile Name, either user, responsibility, or other levels.

SQL:

SELECT profile_option_name, user_profile_option_name
FROM apps.fnd_profile_options_vl
WHERE profile_option_name = 'MY_CUSTOM_PROFILE_NAME'

profile_option_name is referring to Profile Name, user_profile_option_name is referring to User Profile Name.

[SYS] FND: Default Request Days

"FND: Default Request Days" profile decides the default value shown in "Select the Number of Days to View" field after you choose View->Requests

[SYS] PHASE_CODE and STATUS_CODE in FND_CONCURRENT_REQUESTS Table

It has been a bit confusing, hence i summarized the fields' values and meaning as follows:

PHASE_CODE
P - Pending
C - Completed
R - Running

STATUS_CODE
Q - Standby //Scheduled
R - Running
C - Normal
D - Cancelled
E - Error
G - Warning
I - Normal //Inactive

[SYS] Best Practise - To Update a Record Value in Setup Form

Read a documentation, it mentioned the best practise to update a record value during Oracle setup (at form level). Instead of bulk query and update 1 record, query the specific record, update it.

You know why? This is to prevent potential bug on the form that will update all records even you update 1.

[SYS] Where is Value Set Used?

Value Set in Oracle stores set of values, so that it can be used in elsewhere, in Descriptive Flexfield Segments, Key Flexfield Segments and even Concurrent Program Parameters.How do we know the where-used?

Ans: Navigate to Application->Validation->Set, open the form, query the value set, click "Usages" button.

[SYS] Default Value for Organization Code

To set default value for Organization Code in the concurrent program parameter, use this.

select organization_code from org_organization_definitions where organization_id=:$PROFILES$.mfg_organization_id

[SYS] Hard to Find the Flexfield Title and Application?

We need to trace back and check the flexfield setup, some time it's a bit difficult to guess the title name.Click on the record, go menu Help->Diagnostics->Examine, change the Block value to "$DESCRIPTIVE_FLEXFIELD$", check the field Value. E.g If it shows "Flexfield Segment Values (Application Object Library)", means the Descriptive Flexfield Segments Title is "Flexfield Segment Values"

[SYS] Time Zone Conversion

I created a sale order, checked order header record history, the "Update Date" is differs from the last_update_time in oe_orders_headers_all, why?Check the system profile values for Client Timezone and Enable Timezone Conversions, any time zone setup in these profiles will get reflected in the front-end display of time related records.Note: Backend time is always consisitent.

Infolinks

Friday 22 June 2012

SYSTEM ADMINISTRATOR