Some useful queries

To check request set units from request set :

select
 rs.user_Request_set_name,
 rp.sequence,
 cpl.user_concurrent_program_name,
 cp.concurrent_program_name,
 cp.concurrent_program_id,
 e.execution_file_name,
(select meaning from fnd_lookup_Values where lookup_Code = e.execution_method_code and lookup_type = ‘CP_EXECUTION_METHOD_CODE’) conc_type
 From
 fnd_request_sets_tl rs,
 fnd_request_set_programs rp,
 fnd_concurrent_programs cp,
 fnd_concurrent_programs_tl cpl,
 FND_EXECUTABLES e
 where
 rs.request_Set_id = rp.request_Set_id
 and rp.concurrent_program_id = cp.concurrent_program_id
 and cp.concurrent_program_id = cpl.concurrent_program_id
 and cp.executable_id = e.executable_id
 and  rs.user_Request_set_name like ‘<enter request set name>’  order by  rs.user_Request_set_name,rp.sequence

Check user responsibilities :

select a.user_name,c.responsibility_name,b.start_Date,b.end_Date,b.last_update_date
from
fnd_user a,
FND_USER_RESP_GROUPS b,
FND_RESPONSIBILITY_TL c
where
a.user_id = b.user_id
and b.responsibility_id = c.responsibility_id
and a.user_name = ‘<user name>’

Check concurrent request information based on request id :

select fcr.request_date, fcr.request_id, fcp.user_concurrent_program_name,fr.responsibility_name,fu.user_name from
 applsys.fnd_concurrent_Requests fcr,
 applsys.fnd_responsibility_tl fr,
 applsys.fnd_user fu,
 applsys.FND_CONCURRENT_PROGRAMS_TL fcp
 where
 fcr.RESPONSIBILITY_ID = fr.responsibility_id
 and fcr.REQUESTED_BY = fu.user_id
 and fcr.concurrent_program_id = fcp.concurrent_program_id
 and fcr.request_id = <enter here>

Checking Request Set Parameter

For example you have a request set which will process a set of custom processes for a particular inventory organization as a parameter. You scheduled it for 5 different inventory organizations, and yet sometimes in the future, one inventory organization is no longer used. How to tell which request set is used for which inventory orgs ?

The problem is if we submit a scheduled request set several times with different parameters, normally when you see them in the Find – Requests Window, you wouldn’t be able to see the child concurrent requests parameter in it. 

Here’s one script to let you see them :

SELECT  
a.request_id,
b.user_concurrent_program_name,
c.argument1 parameter1,
a.request_date,
a.requested_start_date,
a.actual_start_date,
a.actual_completion_date,
         (SELECT meaning
            FROM apps.fnd_lookups
           WHERE lookup_type = ‘CP_STATUS_CODE’
             AND a.status_code = lookup_code) status,
         (SELECT meaning
            FROM apps.fnd_lookups
           WHERE lookup_type = ‘CP_PHASE_CODE’
             AND a.phase_code = lookup_code) phase
FROM
fnd_concurrent_requests a,
fnd_concurrent_programs_tl b,
fnd_run_requests c
WHERE a.concurrent_program_id = b.concurrent_program_id
AND a.request_id = c.parent_request_id
AND c.argument1 = <your organization id>
ORDER BY actual_start_date, user_concurrent_program_name

Merry Christmas and Happy New Year

Merry Christmas and Happy New Year everyone !

Sorry for the lack of blogging, will come back with one post about PLM by this week.

By the way, I should really change the blog name into something more common, since I will cover not only Oracle Applications stuffs, but also something towards IT Applications in general as well.

SCM, ERP, CRM

Recently I just changed job to become Supply Chain Analyst from a lousy developer before. I considered this a big jump since it is my wish to learn more about the business process rather than just simply writing codes.

Currently I am learning a lot about supply chain process from end to end which I only know bits and pieces before. It’s interesting also because 7 years back I was still working as CRM analyst, and after that I spent 5 years working as developer for Oracle ERP and now I am learning SCM.

IMHO from a company point a view, CRM is more on how to manage relationship between company and their customers, whether as operational (Customer Service, Sales Force Automation or Call Center) or analytical (BI/Datawarehousing).  Oracle modules that is related to this is : Oracle Support, Oracle Field Service, Depot Repair, Oracle Marketing, Oracle Sales Online, as well Oracle BI. Siebel is also very good in CRM.

 ERP is more towards how the company manage their own resources in terms of financial, human resources, fixed assets , purchasing and sales. Oracle Financials and Human Resource are definitely in this scope. Oracle Purchasing and Order Management I believe is still in the gray area between ERP and SCM.

Supply Chain Management I believe is related with how the company manage their supply and demand, it means more towards relationship with the suppliers, planning on customer demand and suppliers supply to balance on their inventory. Oracle ASCP, Inventory, WMS,MRP, Purchasing is on this.

This is only a simple answer from my side, I am still learning more.. will update again once I learn something new.

Training Review

Sorry for the lack updates, really don’t have much time to update lately.

Just finished my first day of OA Framework : Building OA Framework Applications last October.
The trainer was good, he explained the very basic thing one needs to know about OA Framework, why Oracle changed direction from Oracle Forms to OA Framework, etc.

Basically the exercise on the training is the same as the exercise on the OA Framework Tutorial, however I would say it depends on your preference, if you prefer to have a theory and hands-on explanation before doing the tutorial, this training will help alot for you to understand the theory and basic development using Jdeveloper with OA Framework Extension.

However if you are the kind of person who normally enjoys reading manuals and tutorials by yourself, I think this training is not for you. I have done some of the tutorials by myself earlier so was a bit bored initially. Also since the class actually expects the person attend the class to understand java, it’s a bit hard especially at the end of the class for a pure PL/SQL person like me to familiarize with the syntax.

One of the things I find missing from this training is the trainer did not cover personalization and customizations in details.  If you’re a technical consultant, you will be mostly doing personalization or customizations, then you won’t find this training very much useful.

So in conclusion,in my opinion you can go for the OA Framework : Building OA Framework Applications training if :

– You have basic java programming knowledge.

– You will be building new applications or extension on Oracle Applications using OA Framework (Personalization and Customization is not covered on this training)

– You’re interested more in the theory and reason of the OA Framework architecture (EO,VO,AM,CO, etc) rather than practicing hands-on training in class (you will be more like copy-paste the code into JDeveloper).

Perhaps the other training will be more useful, depends on your job scope and what are you going to do with OA Framework.

Customizing OA Framework Applications vs Building OA Framework Applications

I am planning to take OAF course in Oracle Singapore, currently there are 2 courses available, it’s a rare opportunity having OAF courses here !

However these two courses are a bit confusing, I don’t know which one I should take :

1. 11i Extend Oracle Applications: Customizing OA Framework Applications
http://education.oracle.co.uk/html/oracle/15946US/D18280GC10.htm

2. 11i Extend Oracle Applications: Building OA Framework Applications
http://education.oracle.co.uk/html/oracle/15946US/D18994GC11.htm

I asked the question in OTN OAF Forum and here are their advices below, one is from Shiv Saroj

“The decision depends on your assignment requirement and your knowledge level of OAF. If you don’t know anything about OAF, then the customization course might be something very difficult to understand for you. But if your assignment involves customization, you need to specialize in that.So my suggestion will be go for Building OAF apps in case you don’t customization specific assignment. If your OAF basics are clear, you won’t have much issues with customization. Dev guide has some chapters on customization and many business specific threads on this forum.”

Another one from Sara Woodhull (Principal Product Analyst in the Oracle Applications Technology Group (ATG). ) :

“The Building class is meant for developers building pages and applications from scratch. We send our E-Business Suite Applications Division developers to this one to learn the basics of using OAF to build our E-Business Suite applications. This is also good for anyone else building pages and applications with OAF.The Customizing class is meant for consultants and customers who will be doing personalizations and extensions (our internal developers typically don’t). It covers lots of types of personalizations, extending BC4J objects, deployment of customizations, and so on. There is some overlap with the Building class for extensions (in both classes you need to learn to set up a project in JDeveloper, for example). Personalizations are not covered at all in the Building class, but we spend about two days on them in the Customizing class.Yes, you can certainly take one without the other. Yes, it’s good to take both, especially if you are a consultant and need all those skills in your mental toolbox.”

There are others opinions as well but I can’t post all of them here, you can check out from this link below :

http://forums.oracle.com/forums/thread.jspa?threadID=568052&tstart=0

Based on that , I came to a conclusion that I should take Building Courses first to built on the basics of OA Framework, once done I can take a look at the customization part from the developer’s guide or personalization guide.

Hope this can helps other who wants to begin into OA Framework technologies.

Missing AR Sales Tax Code

We had a problem in the AR Sales Tax Report which bugged us for several months.  We did our vertex upload every month into our Oracle Applications, however it seems that everytime we did the upload, some sales tax which is related to invoice is missing.

For example :

Trx 00001 has sales_tax_id = 121111 and it is existing in AR_SALES_TAX with sales tax rate of 4.5 %. However after vertex upload, sales_tax_id 121111 in AR_SALES_TAX is gone and replaced by a new sales_tax_id with the same rate ! This caused the AR US Sales Tax Report will not correctly reported the tax in right STATE – COUNTY – CITY format, instead it will put it in OTHERS field. The occurence were very sporadic and it was hard for us to find a pattern why.  The person who used to upload vertex also left the company and it made more difficult to find the reason why. We have raised TAR to oracle since FEB-07 but there was no meaningful answer from them (as usual).

Yesterday the user again complained of the missing sales tax and i managed to find out why. It seems someone have manually added a postal code to the AR location Rates , and it caused the whole sales_tax_id for the particular location_id gone and replaced by the new sales_tax_id. Since the issue was recent, I managed to find out that the user is using ABC Operating Unit instead of XYZ Operating Unit to add the postal code. Both OU are in the US so initially i thought this wouldn’t make a difference.

The problem actually lies with ARP_STAX.POPULATE_SALES_TAX called by the trigger in AR_LOCATION_RATES. There’s a checking within the package which goes like this :

If <sales tax id exists in RA_CUSTOMER_TRX_LINES> then

     disable the old sales_tax_id, create new sales_tax_id

else

    delete the old sales_tax_id, and create new sales_tax_id

end if;

Since the user added the postal code in B OU responsibility, then of course ARP_STAX package would not find any invoices related with the sales_tax_id in B OU because the invoice is in A OU. It is believed that the user also did the vertex upload using different OU rather than A OU, hence causing the problem.

So we solved the issue by advising them not to upload or modify the Tax Location Rates with other responsibility than A OU (which is used by US Sales Tax Report).

Oracle Applications Job Roles

Most beginners that want to jump in the oracle applications bandwagon are quite confused on what role they should choose.

IMHO, there are 3 main types of job roles in Oracle Applications :

1. Applications DBA (Database Administrator) : The job title speaks for itself, normally the main duties of Applications DBA are :

• Installing Oracle Applications (could be under unix/windows/linux/etc)
• Regular Database Maintenance (Disk Space monitoring, performance monitoring, backup and recovery,database tuning,main contact/support for database issues etc)
• Regular Applications Maintenance (creation of new users, new responsibility, monitoring concurrent managers etc)
• Applying Patches/doing upgrades.
• Act as production controller to move new application setups , new customizations objects/programs etc.
• Application Cloning, normally DBA will refresh development instance or test instance regularly with backup from live/production instance.
• etc

2. Oracle Applications Developer : The job titles may vary (such as Technical Consultants, E-business technical specialist, Software Engineer, Oracle Programmer Analyst and so on), but main duties basically is the same  :

• Responsible for Development of New or Customizations of Reports, Forms, PL/SQL programs, Workflows, Custom PLLs, Form Personalizations, OA Framework Pages, unix scripts, alerts, SQL*Loader and many more.
• Creating technical documentations and installation documents for the customizations they made.
• Act as second level of support for Oracle Applications troubleshooting, such as interfaces problems, reports problems etc.
• Custom applications tuning for performance problem.
• Thinking of technical solutions for requirements given by the users or functional analysts.
• etc

3. Functional Analysts : As the job title says, the main duties are the following :

• Application Setups (depends on which modules they support/implemented)
• Act as first level of support for Oracle Applications troubleshooting with users.
• Gather . define and analyze the detailed functional requirements his/her respective modules.
• Creating Functional Requirements Document (MD-50 document in Oracle AIM) which will be read by Applications Developer
• Create/develop test scripts (with testing software) or testing documents.
• Develop user manuals/documentation for the respective modules.
• Develop and/or conduct trainings to business users.
• Etc

From these 3 job roles , there are “hybrid” job roles which combine multiple job roles such as :

Oracle Technical Consultant : Some technical consultants in consulting group that I know can do both dba tasks as well as developer tasks.

Techno-Functional Analyst : For this role the respective person should be able to act as functional analyst as well as developers, so he/she should have deep knowledge on the functional flows of the respective modules he/she supports as well as equipped with the developer skills.

 

Above lists are not a complete lists of the job duties, but these served only as a global overview for those who wants to start a career in Oracle Applications sector. Based on your qualifications, experience and interest you can choose the best role that suits you. Good luck.

Hello world!

First Post.

I’m trying to put my knowledge gained from my day to day work as Oracle Applications Developer.

This is useful even for me in case I encountered similar problems in the future, and also others who faced similar issue.

 Please leave comments if I made some mistakes or if you have better suggestion or solution for the problems.

Thanks