, , ,

How to add a context dependent descriptive flex-field on OAF Control Item page?

We all know (I think :-)) the way in Oracle Forms to create context dependent descriptive flexfields. That is displaying additional segments within the form based on some other value in the form or maybe a value on a profile option.



These context dependent flexfields were at first not possible in framework because the references like BLOCK etc (which you can use in Forms) are not applicable to framework pages. Starting from release 12.0.6 of E-Business Suite there is a way to implement context descriptive flexfields on framework pages.

As an example I’ll use some example we encountered during an implementation. There was a requirement that additional details should be given to a control item in Project Management. Control Items in Project Management can be used to record your issues and risk on your running projects. In this case we have 2 Control Item Types being Issue and Risk. Based on the type we need to display additional segments in a DFF for the end user to use. This DFF must change with every type of issue.

To implement this behaviour we need to do a number of things:

  1. Implement the DFF and add segments on it for 2 context values (Issue and Risk)
  2. Implement a reference value used by the DFF which is the Control Item Type
  3. Enable the Descriptive Flexfield on the Control Item page of a project
  4. Add a so called Flex Map to the Descriptive Flexfield on the OAF page to hold the control item type (and used by the DFF definition
  5. Bounce Apache

Key thing in the above is step number 4. Starting from release 12.0.6 E-Business Suite introduces the functionality of Flex Maps. Follow the below steps to implement the behaviour. Of course the same method can be used in other parts of E-Business Suite if the page has a seeded DFF. If that’s not the case than also a self made descriptive flexfield can be added but I’ll write a post about that in near future.

Step 1: Implement the DFF for Control Items
a) Go to Application Developer – Flexfield – Decriptive – Segments and query the Control Item flexfield with the name Control Items Desc Flex.


b) Unfreeze the Flexfield Definition if needed. Tick the checkbox Synchronize with Reference Field to make sure the context is updated when you move along control items. Also untick the Displayed checkbox as we don’t want to show the Context field to the end user. Set the Reference Field. Add the syntax :$PROFILES$.<Name>. As a name anything can be used. Remember that name because we need it in a later step. In my example I’ll set it to :$PROFILES$.TypeCICyriel


c) Add Context Field values. I’ll add 2 values named Issue and Risk. It’s very important that these names represent the exact naming of your control item types. Add as many as you like representing each option you have for the types. Add your segments to the contexts and assign them to one of the attribute fields.


By default the segments are set to be a required segment. If applicable turn this off by opening the segment (click on Open) and untick the required checkbox.


d) When done adding your context field values and the related segments save the work and freeze the Flexfield definition. Your DFF should compile without problems.

Step 2: Handle the DFF on the framework page (Control Items)

Go to your Control Item page. If you don’t have the Personalize options enable the profile options and set it to the username who’s going to do the change. Change the below profile options and set it for the specific username on value Y (for Yes).
  • Create Seeded Personalizations
  • FND: Personalization Region Link Enabled
  • FND: Personalization Seeding Mode
  • HR: Enable User Personalization
  • Personalize Self-Service Defn

Make sure the DFF is enabled (Shown) for the page. If that’s not the case than click the Personalize pencil on the row Flex: (PaDescFlex) and change the rendered setting to Yes.






Step 3: Add a Flex Map (bean) to the DFF in OAF through personalization
a) Click on Create Item on the row Flex: (PaDescFlex). You get a new screen where you define the Flex Map settings.

b) Enter the ID which can be anything you want. Leave the Attribute Set and Comments blank. For Name you have to use the exact name as defined in the reference field setting in the DFF definition without the :$PROFILES$. I used the reference field name settting :$PROFILES$.TypeCICyriel so the name of the Reference Value of the Flex Map must be TypeCICyriel. 

The Reference Value is very important and will be the placeholder for in this case the Control Item Type name. This setting can use the so called SPEL language with which you reference an attribute of the page or view object of the OAF page. In this case the Control Item Type name is a seeded attribute of the ControlItemVO object of the page. We enter ${oa.ControlItemVO.CiTypeName} as the reference value. The Refers Context option needs to be set to true as we use this Flex Map as a context item.

c) Save all your work and bounce apache if the changes are not reflexted immediately.

The end result will be that when you create a new control item being, in our example, a risk or an issue the applicable additional segments are shown for the type. We’ve unticked the Displayed checkbox within the DFF definition so it’s also not visible in the OAF page. See below the shots for the end results. Good luck with using this feature 🙂

Risk showing the 2 defined risk segments of the DFF (Context hidden)


Issue showing the 2 defined issue segments of the DFF (Context hidden)
/by
, , , ,

How to Configure Oracle Approval Management (AME) for developer?

Follow the steps below to have access to AME.

Steps to AME configuration for any user:



1) Select the User Management responsibility.
2) Select the Users page.
3) Search for the user to whom you wish to grant AME roles.
4) In the results table, click Update. In the Update User page, you can view user details along with a list of roles available to the user.
5) Click Assign Roles.
6) Select following roles and click Apply.

1) Approvals Management Administrator
2) Approvals Management Analyst
3) Approvals Management System Viewer
4) Approvals Management System Administrator
5) Approvals Management Process Owner

7) Grant data access to users

1) Login as Administrator.
2) Select the Functional Administrator responsibility.
3) Select the Grants tab.
4) Click Create Grant.
5) Select Specific User as grantee type.
6) Select the user as grantee key.
7) Select AME Transaction Types as Object
8) Select AME Calling Applications as Set
9) Follow the screen and click on Finish

8) Run “Workflow Directory Services User/Role Validation”
9) Select Approval Management Business Analyst responsibility and check functionality.

/by
, , ,

Tricks for generating SQL statements in Excel 2003/2007

The semi-colon needs to be inside the last double quote with a closing param. When adding single quotes around a string, remember to add them outside your selected cell.
(spaces added for visibility – remove before inserting)
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")
Here is another view:
=CONCATENATE(“insert into table (id, date, price) values (“,C3,”,'”,D3,”‘,”,B3,”);”)
/by
, , , , ,

Some Useful Queries in Oracle Apps R12

How to initialize session in Oracle ?
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API’s in Oracle Ebusiness suite. Its not required for all the API’s but its recommended that you set this profile before making any calls to either private or public API. 

Mentioned below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id, 
                           resp_id=>l_resp_id, 
                           resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call. 
l_resp_id is the responsibility ID 
l_resp_appl_id is the responsibility application ID. 
You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id , 
       fresp.responsibility_id, 
       fresp.application_id 
from   fnd_user fnd 
,      fnd_responsibility_tl fresp 
where  fnd.user_name = ‘SYSADMIN’ 
and    fresp.responsibility_name = ‘Order Management Super User’;

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

How to Find PO Requisitions associated with PO Orders?
By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 “Requisition Number”,
prha.type_lookup_code “Requisition Type”,
pha.segment1 “Purchase Order Number”,
pha.type_lookup_code “Purchase Order Type”
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id “requisition_header_id”
,prha.segment1 “Requisition Number”
,prha.preparer_id “preparer_id”
,TRUNC(prha.creation_date) “creation_date”
,prha.description “description”
,prha.note_to_authorizer “note_to_authorizer”
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code=’CANCEL’
AND pah.object_type_code=’

REQUISITION’
AND pah.object_id=prha.requisition_header_id
How to find On-hand inventory information?
Execute below query to see the onhand inventory information in oracle applications.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

How to know the version in oracle apps?
select * from v$version;
 
With the help of above query we can find out the oracle apps version. 


How to know the application is multi-org or not?


By using below query we can get the multi org status.


 


SELECT multi_org_flag
FROM   fnd_product_groups




How to know Which User is Locked the table?



This Query will list the name of user who locked the table table. The object name is taken as an input parameter.

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';




How to get Customer Address Details in Oracle apps?





The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;





Script to find Oracle API’s for any module


With the help of below query we can find the API for a module in Oracle Apps.


select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name







/by 
, , , , 
Oracle Unified Method (OUM) – Basics and Download


Since retirement for Oracle legacy methods, there was lot of queries on OUM vs AIM , therefore this post will help reader to know more about new method.


The OUM aka Oracle Unified Method supports the entire Enterprise IT Life cycle, including support for the successful implementation of every Oracle product.


It is a robust, technology agnostic methodology Oracle Methods …


    

  • Based on industry standards and field experience
    • 

  • Highly scalable to support project or program specific requirements
    • 

  • Iteratively developed through a continuous, evolutionary process
    • 



OUM Principal


This is based out of five main principles


    

  1. Business: Focus on the business and assure stake holder acceptance and delivery of the development’s efforts.
    2. 

  2. Architecture: Define architecture before resources are committed for development and implementation.
    3. 

  3. Adaptability: Encourages adaptability for scalable delivery across small and large projects possessing disparate resources and skill levels.
    4. 

  4. Implementation: Provides rapid implementation techniques to ensure business solutions in short time frames
    5. 

  5. Standards: Leverage the Unified Modeling Language and the Unified Software Development Process
    6. 



OUM is able to…


Support the complete range of Oracle technology projects including


    

  • Service-Oriented Architecture (SOA)
    • 

  • Enterprise Integration
    • 

  • Custom Software
    • 

  • Enterprise Performance Management (EPM)
    • 

  • Business Intelligence (BI)
    • 

  • Enterprise 2.0
    • 

  • Enterprise Application Implementation
    • 

  • Software Upgrade
    • 



Currently Oracle Consulting using this methods, tools support a complete range of technology engagements.


In Summary… OUM structure


    

  • is a comprehensive set of method materials that is applicable to any type of information technology project.
    • 

  • is a plan-based method – that includes overview material on various approaches and standards.
    • 

  • It includes base guidelines for focus areas, views, phases, activities, processes and tasks, as well as work product descriptions.
    • 



OUM basically have focus areas and there underline phases and processes , in summary


OUM FOCUS AREAS


These 3 Focus Areas provide a frameworks for enterprise level planning, project and program management and implementing .


    

  1. Envision Focus Area deals with development and maintenance of enterprise level IT strategy, architecture, and governance. This also assists in the transition from enterprise-level planning and strategy activities to the identification and initiation of specific projects.
    2. 

  2. Manage Focus Area provides a framework in which all types of projects can be planned, estimated, controlled, and completed in a consistent manner.
    3. 

  3. Implement Focus Area This focus Area provides a framework to develop and implement Oracle-based business solutions .
    4. 



Next let take a quick overview for Implement Focus area which got replacement of old methods like AIM Foundation, AIM for Business Flows, Compass, Results Roadmap, etc. 


Understanding OUM’s Implement Focus


This focus area typically based out of milestone based approach.Typical Milestone are:


    

  • Life cycle objective milestone
    • 

  • Life cycle architecture milestone
    • 

  • Initial operation capability milestone
    • 

  • System in production milestone
    • 

  • Sign-off milestone
    • 



As mention earlier this comprises of 5 phases and 14 processes, details as follows:


OUM Project Phases


The Workflows within an OUM project are categorized into five phases: Inception, Elaboration, Construction,Transition, and Production. These phases are described below:




(A) Phase I: Inception :As the first phases in the project life cycle.Major goal of this phase is to achieve concurrence among all stakeholders on the lifecycle objectives for the project. Kick off’s, review , high-level business requirements along with initial project plan is being targeted in this phase only.


(B) Phase II: Elaboration :This is the second phases in the project life cycle, where major focus would be to develop the detailed requirements models, partition the solution, develop functional prototyping, and baseline the architecture of the system.


(C) Phase III: Construction :As the third phases in the project life cycle, Construction focuses on design, implementation, and testing of functions to develop a complete system.


(D) Phase IV: Transition :This typically consist from installation onto the production system through the UAT and launch of the live application, open and ready for business.


(E) Phase V: Production : The goal of the Production phase is to operate the newly developed system, assess the success of the system, and support the users. This includes: monitoring the system; acting appropriately to ensure continued operation; measuring system performance; operating and maintaining supporting systems; responding to help requests, error reports and feature requests by users; and managing the applicable change control process so that defects and new features are prioritized and assigned to future releases.




dgreybarrow OUM Process – Typically OUM process defined as “A discipline or sub-project that defines a set of tasks related by subject matter, required skills, and common dependencies”. A process usually spans several phases in an approach.


All OUM tasks are also organized into processes that group related tasks together. Project team members are assigned to these groupings according to their specialization and background. OUM includes the following 14 processes


Oracle Unified Method (OUM)


    

  1. BUSINESS REQUIREMENTS [RD] – Objective of the Business Requirements process is to identify, refine, and prioritize the business requirements for the proposed system
    2. 

  2. REQUIREMENTS ANALYSIS [RA]– Objective of the Requirements Analysis process is to further analyze the requirements identified during the Business Requirements process as the basis for analysis and design.
    3. 

  3. ANALYSIS [AN]– Objective of the Analysis process is to analyze, refine, and structure the system requirements via the Analysis Model.
    4. 

  4. DESIGN [DS] – Objective of the Design process is to translate requirements into a system design that meets all functional and supplemental requirements.
    5. 

  5. IMPLEMENTATION [IM] – Objective of the Implementation process is to develop the final system, through a number of iterative steps.
    6. 

  6. TESTING [TE]– The Testing process is an integrated approach to testing the quality and conformance of all elements of the new system.
    7. 

  7. PERFORMANCE MANAGEMENT [PT]– Objective of the Performance Management process is to define, construct, and execute an effective approach to managing performance throughout the project life cycle.
    8. 

  8. TECHNICAL ARCHITECTURE [TA]– Objective of the Technical Architecture process is to design an information system architecture that realizes the business vision.
    9. 

  9. DATA ACQUISITION AND CONVERSION [CV]– Objective of the Data Acquisition and Conversion process is to convert all legacy data necessary for the operation of the new system.
    10. 

  10. DOCUMENTATION [DO]– Objective of the Documentation process is to develop documentation that augments product manuals with information about custom software and business procedures.
    11. 

  11. ORGANIZATIONAL CHANGE MANAGEMENT [OCM]– Objective of the Organizational Change Management process is to identify the human and organizational challenges of the project in order to mitigate risk.
    12. 

  12. TRAINING [TR]– Objective of the Training process is to adequately train the project team to begin the project and train the users to run the new system.
    13. 

  13. TRANSITION [TS]– Objective of the Transition process is to install the system and go production.
    14. 

  14. OPERATIONS AND SUPPORT [PS]– Objective of the Operations and Support process is to monitor and respond to system problems to fix errors and performance problems and plan enhancements.
    15. 



Believe with this method companies get simplified project management, improved planning, reduced risk which make them manage project within time within Budget.Next post will more the details for other Focus area.Keep watching this space.





/by