Assigning responsibility

Assigning a user's responsibilities to another user can be time consuming, here is a useful code:

--Working in R12, not tested in R11
declare
cursor c1 is select fr.APPLICATION_ID, fr.RESPONSIBILITY_ID,
frt.RESPONSIBILITY_NAME, fur.end_date
FROM apps.fnd_user fu
,apps.FND_RESPONSIBILITY fr
,apps. FND_USER_RESP_GROUPS_DIRECT fur
, apps.FND_RESPONSIBILITY_tl frt
where fur.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
and fur.USER_ID=fu.USER_ID
and frt.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
and frt.LANGUAGE='TR'
and (fu.END_DATE is null or sysdate between fu.start_date and fu.end_date)
and fur.END_DATE is null
and fu.user_name = 'RHOSHANLI'; -- misal kullanýcý

cursor c2 is select fu.USER_id
FROM apps.fnd_user fu
where (fu.END_DATE is null or sysdate between fu.start_date and fu.end_date)
and fu.user_name in ('TESTUSER'); --sorumluluk verilecek kullanici

l_count number;

begin

for r1 in c1 loop

for r2 in c2 loop


select count(*) into l_count
FROM apps.fnd_user fu
,apps.FND_RESPONSIBILITY fr
,apps. FND_USER_RESP_GROUPS_DIRECT fur
, apps.FND_RESPONSIBILITY_tl frt
where fur.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
and fur.USER_ID=fu.USER_ID
and frt.RESPONSIBILITY_ID=fr.RESPONSIBILITY_ID
and frt.LANGUAGE='TR'
and (fu.END_DATE is null or sysdate between fu.start_date and fu.end_date)
and fur.END_DATE is null
AND FRT.RESPONSIBILITY_ID=r1.responsibility_id
and fu.user_id = r2.user_id; -- sorumluluk verilecek kullanýcý

if (l_count = 0 ) then

apps.fnd_user_resp_groups_api.Insert_Assignment( user_id =>r2.user_id
,responsibility_id => r1.responsibility_id
,security_group_id => NULL
,responsibility_application_id => r1.application_id
,start_date =>sysdate
,end_date => NULL
,description => r1.responsibility_name);
else

apps.fnd_user_resp_groups_api.Update_Assignment( user_id => r2.user_id
,responsibility_id => r1.responsibility_id
,security_group_id => NULL
,responsibility_application_id => r1.application_id
,start_date =>sysdate
,end_date => r1.end_date
,description => r1.responsibility_name);
end if;

end loop;

end loop;

commit;

end;

Integration Design

In todays complex IT infrastructre integrations between different systems are so common
that it is almost a daily routine subject. Therefore design of integrations create huge impact on system performance, operations effort and user satisfaction. Depending on the technology of systems; data file, database link, web service methods can be used.

Thinking of nowadays's popular web service method, there is another fundamental design decision; synchronised or asynchronised? Both has different advantages and disadvantages so satisfies different requirements.
Synchronised; Provides result of service request directly to the user. This provides users to act quickly if needed. Though it is disadvantageous if too many records exist and if that causes users to wait. Also this may cause high load problems on the system.
Asynchronised; Users submit a request though does not see the result until querying it. This probably causes users extra effort because of requiring a control action to see if it is completed expectedly. Also increase operational support need because when users doesnt see results immediately a scheduled monitoring action is probably required. Also this may cause delay of other processes which are dependent of the requested action. Though it's disadvantages it can be used because synchronisation time can be scheduled to a time which system is free of load and users does not have to wait until getting a response.

Another important subject to think about is error handling. Many reasons may cause problems such as system shutdowns, missing data or unexpected cases and different solutions may required for these causes. Sending failed records twice, sending warning mails about failed transactions to related users, creating a log report and giving users the ability of retry are the basic precautions to be taken.

Taking these into consideration, will provide customer satisfaction and decrease the need for operations support.

Cumulative Sum, Regular Expressions

I needed a sql to force users to enter data with Capital letters, a friend helped me with a regular expression, of course usage of it is not limited with this:
regexp_substr(:ATTRIBUTE_VALUES.C_ATTRIBUTE6,'[[:lower:]]') is not null

To have cumulative sums following sql can be used:
SUM(SUM( toplam_maliyet/GENEL_TOPLAM_maliyet)*100 )
OVER (ORDER BY sum (toplam_maliyet/GENEL_TOPLAM_maliyet)*100 desc ROWS UNBOUNDED PRECEDING) AS cumulative_total

Effective Software Development Process

It is said that system analysis and development of the software result differently than the real requirement most of the time. I partially agree with that; i see products varies from customers imagination-requirement thus changes are required after the expected completion time. To increase quality of work and descrease time spent, design clarity is important so supporting anaysis text with draft screens, workflows is a good idea. To help with these Lucid Spec software can be used;

"Lucid Spec - Better software through concise functional specifications!
Lucid Spec helps you rapidly prototype software and produce a concise, clear functional specification. Lucid Spec does this by combining screen design, application simulation, and user interface description all-in-one."
http://www.elegancetech.com/ls/ls.aspx

Oracle Time and Labor (OTL)

I have encountered OTL while using Projects. For me time entry was done in OTL and it was integrated with Projects. Three kinds of time entry is possible for OTL; employees entering for themselves via an OAF screen, assigned users entering for different employees (Authorized Delegate method) via an OAF screen and assigned users entering for different employees (Timekeeper method) via a Forms screen.
Following is a more comprehensive definition of the module

"Oracle Time and Labor automates the entire time and attendance record-keeping process and provides an intuitive, web-based interface for time entry and approval. It offers a simplified way to submit, review, track and approve timecards. At the same time, it helps you control costs and time worked, consolidate timecard information, and adhere to organizational time management rules.

Integrate and Streamline Your HR Processes
Oracle Time and Labor is part of Oracle Human Resources Management family of applications, and integrates seamlessly with other Human Resources applications, including Human Resources(core) and Payroll. Oracle Time and Labor also is a key module in Oracle's enterprise project management solution, Oracle Projects.

BENEFITS
Enter Time Anytime, Anywhere

Enter time via web browser, mobile device, or even offline using a timecard template that can be uploaded later.
Streamline Management

Enable approvers to search timecards and view summary information. Perform mass approvals. Gain flexible configurations for approvals workflow.
Enforce Policies

Define rotation plans based on shifts and work plans. Define which employees can charge to which organizations in your company. Support policies for holidays, overtime, and rounding.
Automate Timecard Creation

Automatically generate a timecard or group of timecards from work schedule, last timecard, or a project stored in Oracle Projects Resource Management.
Create Single Source of Entry

Improve reporting, extracting, and processing with a single database of employee time-related information. Share data with Oracle Projects, Oracle Human Resources, and Oracle Payroll."

http://www.oracle.com/us/products/applications/ebusiness/human-capital-management/053631.html

Oracle Application Testing Suite (ATS)

Oracle has an alternative for HP Quality Center and its automated tests. It is described clearly hereafter.

"Application Testing Suite is a comprehensive, integrated testing solution that ensures the quality, scalability, and availability of your Web applications, Web Services, packaged Oracle Applications and Oracle databases.

This integrated, full lifecycle solution enables you to define and manage your application testing process, validate application functionality, and ensure that your applications will perform under load. With Application Testing Suite, you can deploy your Web applications and Web Services in less time while maximizing the efficiency of your testing team.

Application Testing Suite is comprised of the following tightly integrated products:

Oracle Load Testing for scalability, performance and load testing.
Oracle Functional Testing for automated functional and regression testing.
Oracle Test Manager for test process management, including test requirements management, test management, test execution and defect tracking."
http://www.oracle.com/technetwork/oem/app-test/etest-101273.html

Web Service Management in EBS

Previously i have mentioned Integrated SOA Gateway responsibility as a source of information for APIs and interfaces. Though its functions are not limited with these; you can monitor, generate, deploy web services and grant authorizations with the Integrated SOA Gateway responsibility. To be able to manage EBS login should be with SYSADMIN user. SYSADMIN user can also see transaction results, request and response details and their SOAP (Simple Object Access Protocol) information.

Toad Character Problem

You may encounter different kinds of character problems while using Toad. One kind is common and i know it made many developers angry; if your nls_lang setting is not compatible with the country you are working then your code might get corrupted. For example if you write a note like 'İstanbul - Üsküdar' and your nls_lang is set to English then you should expect the corruption. If you are going to use Turkish characters like these then you should set nls_lang to Turkish: Start> run> regedit> search nls_lang > AMERICAN_AMERICA.WE8ISO8859P9 -> TURKISH_TURKEY.TR8MSWIN1254

Another problem I have witness is the results of your query may shown in Chinese characters! In that case your problem is probably the Home of your Toad or Toad Options font selection.

If you have problem while extraction of data then you may use "Excel Instance" for export format as a workaround.

iProcurement charge account defaulting

There is a common question; how are the accounts generated? The answer for the iProcurement Charge account is below:

How Is Charge Account Defaulted On iProcurement Requisition Lines?[ID 455847.1]
If an Inventory Organization is specified for the location, then this is considered an Inventory Location.

If Inventory Organization is blank, then this is considered an Expense Location.

For items delivered to inventory location the charge account is defaulted from the following settings in the order shown

Charge account specified in the subinventory to which the inventory item will be delivered affected by the following:
The profile 'POR : Preferences -Selected Items Default to Inventory'
The subinventory location specified in iProcurement Preferences
The Deliver To Subinventory checkbox available during iProcurement checkout
Charge account specified in the item definition in Master Items form.
Charge account specified in the Employee setup form (Human Resources / People / Enter and Maintain / Assignment / Purchase Order Information)
Charge account specified as Primary Favorite Charge Account in iProcurement Preferences
- used only if none of the above defaults are specified

For items delivered to expense location the charge account is defaulted from the following settings in the order shown

For items charged to Projects, the project based charge account will be used based on customizations implemented in the requisition account generator workflow.
Charge account specified in the item definition in Master Items form.
Charge account specified in the Employee setup form (Human Resources / People / Enter and Maintain / Assignment / Purchase Order Information)
- affected by Expense Account Rules setup in Purchasing / Setup / Financials / Accounting / Expense Account Rules. Expense account rules can replace one of more of the defaulted charge account segments with a new segment value(s) specified in the expense account rule(s)
Charge account specified as Primary Favorite Charge Account in iProcurement Preferences
- used only if a default employee account is not specified
- affected by Expense Account Rules only if the profile POR Apply Expense Account Rules To Favorite Charge Account = Yes.

Tax table

For tax calculations there is a new module in R12 and there are some changes. There is a table for transaction related attributes used for tax calculation and reporting which is not known commonly and i dont want to forget it so here it is: zx_lines_det_factors. Each row stores a transaction line of an event class.

Workflow Persons

Workflows don' t work as expected sometimes like not following the right course, not sending notifications to correct people or sometimes workflow can't find an existing person. In these cases two concurrent programs may resolve the problem:
1- Synchronize WF LOCAL tables; this works most workflows
2- Fill Employee Hierarchy; this works for purchasing hierarchy of purchasing documents like Requisitions, Blanket Agreements etc.

Materialized View

When you want to query a large volume data performance may not satisfy you because of the high cost of query. What should you do in such a situation? One solution is using Materialized View. It is a view which can be refreshed periodically with the frequency you define. You can prepare your query and create a materialized view then in reports or screens you can select results from it just like a table instead of your costly query. What is the advantage? The answer is quite clear: Performance. What is the disadvantage? Results are snapshot of last time of query refresh, so if you need fresh data this may not be the solution.

To track your assets in EBS solution is Installed Base. Here are some sample questions you can find answers with Installed Base:
"Where exactly is my Copier that’s making me money & what’s happened to it?
Where is that Car we leased out & customer wants us to repair? How many cars?
Where do I have copiers installed at various offices across country & how many copies did we print in last 3 months in division xxx?"

Following link covers the subject in detail so i will not repeat the info:
http://apps2fusion.com/at/rs/362-oracle-install-base-track-your-assets-and-items

How to add unused columns to Descriptive Flex Fields?

1- Reach the screen: Application Developer responsibility> Flexfield> Register> Descriptive Flex Field
2- Control attributes: press F11> write %AP_INVOICES_ALL> press Ctrl+F11> control unused global_attributes where structure=ATTRIBUTE_CATEGORY lets assume global_attribute10 is unused
3- Uncheck: uncheck global_attribute10 where structure=GLOBAL_ATTRIBUTE_CATEGORY
4- Check: check global_attribute10 where structure=ATTRIBUTE_CATEGORY

Suppliers in TCA

Supplier, customer etc structures had major changes in R12 and it is called TCA: Trading Community Architecture. It is a complicated structure and here is a link to understand supplier part of this structure: http://oracle.anilpassi.com/suppliers-in-tca-a-dive-into-vendor-tables-in-r12-2.html

Read only screens

For security reasons customers may want screens to be read only for some users (Ex: External users - internal users of system or for different departments) This is not easy for OAF screens though quite easy for Forms screens. There are various options for Forms; custom pll, personalization, GRC module and function definition. The easiest way is adding QUERY_ONLY=YES value to form function parameters.

Oracle Calendar

What is Oracle Calendar?

Oracle Calendar is designed to be your diary and calendar, which will assist you in managing your appointments and work time more effectively.
You can enter information so that only you have access to it, or you can restrict access to a specified individual or group of users.

What can it be used for?

Oracle Calendar can be used to plan your schedule and save time:

Arrange meetings with other Oracle Calendar users.
Examine the schedules of other members of University staff.
Make to-do lists, reminder notes and keep a record of things you have done.
Print schedules in a variety of formats.

How and where do I install it?

There are a number of ways to view your Oracle Calendar schedule:

Desktop Client - Installation guide | How to use
Web Client - Log in | Publish your agenda to the world
Most Smart Phones - Mobile phone installation guide
Palm Pilot - Palm Pilot installation guide
PocketPC - PocketPC installation guide

Copied from: http://www.bath.ac.uk/bucs/tools/calendar/
For more info: http://www.oracle.com/us/industries/communications/communications-calendar-server-066178.html or Oracle Common Application Calendar User Guide

Multiple Nodes

E-Business Suite can be located on more than one server (node) to improve the performance. Depending on the logging users' s transactions
load of the server increases and performance decreases. To maintain high performance multiple nodes can be used, users can still use single
login link and users are automatically directed to the less loaded server when they click the link.
Though this system too has some disadvantages; application files like forms, oaf pages need to be installed on all servers depending on the need.
That means extra work for every deployment. Standard Oracle EBS functionality keeps attachments on the database as binary files to prevent discrepancies between servers.

OAF Personalization; Context, Security and Table

It has been a long time since i wrote here last time. The main reason is i was occupied with implementation of tough projects, but now i am back with some accumulated knowledge :)

In Forms context usage for descriptive flex fields (DFF) is easy; define the DFF Context Field Values and Forms automatically detects it.
Though for OAF screens additional setup is required; In the related OAF page> Personalize> Stil = Flex> Go> Personalize> Rendered=yes and
Segment list = Global Data Elements|SegmentName1|SegmentName2..||ContextCode1|SegmentName3..||ContextCode2|SegmentName4.. Then
related DFF' s column (Ex: iProcurement requisitions distributions DFF column: reqdistdffonacctcol) is set to Rendered = Yes

Personalization global link can be set for security reasons; "Personalize Self-Service Defn" profile can be set for making the link appear or disappear on OAF screens.

In OAF pages "FND: Personalization Region Link Enabled" profile provides the regional "Personalize" links to appear

To use standard OAF functionality "Disable Self-Service Personal" profile can be set

To set whether a user can or can not make OAF personalizations at site level: "FND: Personalization Seeding Mode" If you want to check the OAF personalization info and maybe even update it via the database following samples are handy: select * from jdr_paths where path_name like 'ShoppingHomePG' order by last_update_date desc edit jdr_attributes where ATT_COMP_DOCID in (80317,83558)