Module GMD R12.GMD.A.6 (UNP Product Development; Process Manufacturing Product Development)

The procedure to delete the row from the formula

PROCEDURE delete_formuladetail(
    p_formula_id     NUMBER ,
    p_formulaline_id NUMBER ,
    p_return_status OUT VARCHAR2 ,
    p_message OUT VARCHAR2 )
IS
  l_api_version       CONSTANT NUMBER := 1.1;
  l_init_msg_list     VARCHAR2(1)     := FND_API.G_TRUE ;
  l_commit            VARCHAR2(1)     := FND_API.G_FALSE;
  l_called_from_forms VARCHAR2(10)    := ‘NO’;
  l_return_status     VARCHAR2(1) ;
  l_msg_count         NUMBER ;
  l_msg_data          VARCHAR2(400) ;
  L_FORMULA_DETAIL_TBL GMD_FORMULA_DETAIL_PUB.FORMULA_UPDATE_DTL_TBL_TYPE;
  — l_formula_head fm_form_mst_b%rowtype;
  L_FORMULA_LINE FM_MATL_DTL%ROWTYPE;
  — i pls_integer := 1;
  NO_PROC_FINISH EXCEPTION ;
BEGIN
  IF (P_FORMULA_ID IS NULL) THEN
    P_MESSAGE      := ‘ P_formula_id parameter can not be empty ‘;
    RAISE NO_PROC_FINISH;
  END IF ;
  — получаем данные заголовка
  BEGIN
    SELECT *
    INTO l_formula_head
    FROM fm_form_mst_b fm
    WHERE fm.formula_id = p_formula_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Unable to find a formula ‘;
    raise no_proc_finish;
  END ;
  — get the data string formula
  BEGIN
    SELECT *
    INTO l_formula_line
    FROM fm_matl_dtl fm
    WHERE fm.formulaline_id = p_formulaline_id;
  EXCEPTION
  WHEN OTHERS THEN
    p_message := ‘ Could not find a string formula ‘;
    raise no_proc_finish;
  END ;
  gme_common_pvt.set_who;
  l_formula_detail_tbl(i).RECORD_TYPE    := ‘D’;
  l_formula_detail_tbl(i).formula_id     := p_formula_id;
  l_formula_detail_tbl(i).formula_no     := l_formula_head.formula_no;
  l_formula_detail_tbl(i).formula_vers   := l_formula_head.formula_vers;
  l_formula_detail_tbl(i).formulaline_id := p_formulaline_id;
  GMD_FORMULA_DETAIL_PUB.DELETE_FORMULADETAIL( P_API_VERSION => L_API_VERSION, P_INIT_MSG_LIST => L_INIT_MSG_LIST, P_COMMIT => L_COMMIT, P_CALLED_FROM_FORMS => L_CALLED_FROM_FORMS, X_RETURN_STATUS => L_RETURN_STATUS, X_MSG_COUNT => L_MSG_COUNT, X_MSG_DATA => L_MSG_DATA, P_FORMULA_DETAIL_TBL => L_FORMULA_DETAIL_TBL );
  IF l_return_status != FND_API.g_ret_sts_success THEN
    IF l_msg_count = 1 THEN
      p_message   := FND_MSG_PUB.get(1,’F’);
      RAISE no_proc_finish;
    ELSE
      FOR l IN 1..l_msg_count
      LOOP
        FND_MSG_PUB.get (p_msg_index => l ,p_encoded => ‘F’ ,p_data => p_message ,p_msg_index_out => l_msg_count);
      END LOOP ;
      RAISE no_proc_finish;
    END IF ;
  END IF ;
  p_return_status := ‘S’;
EXCEPTION
WHEN no_proc_finish THEN
  p_return_status := ‘ E ‘;
WHEN OTHERS THEN
  p_message       := SQLERRM ;
  p_return_status := ‘ E ‘;
END ;

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.

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

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. Architecture: Define architecture before resources are committed for development and implementation.
  3. Adaptability: Encourages adaptability for scalable delivery across small and large projects possessing disparate resources and skill levels.
  4. Implementation: Provides rapid implementation techniques to ensure business solutions in short time frames
  5. Standards: Leverage the Unified Modeling Language and the Unified Software Development Process
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. Manage Focus Area provides a framework in which all types of projects can be planned, estimated, controlled, and completed in a consistent manner.
  3. Implement Focus Area This focus Area provides a framework to develop and implement Oracle-based business solutions .
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. 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. ANALYSIS [AN]– Objective of the Analysis process is to analyze, refine, and structure the system requirements via the Analysis Model.
  4. DESIGN [DS] – Objective of the Design process is to translate requirements into a system design that meets all functional and supplemental requirements.
  5. IMPLEMENTATION [IM] – Objective of the Implementation process is to develop the final system, through a number of iterative steps.
  6. TESTING [TE]– The Testing process is an integrated approach to testing the quality and conformance of all elements of the new system.
  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. TECHNICAL ARCHITECTURE [TA]– Objective of the Technical Architecture process is to design an information system architecture that realizes the business vision.
  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. DOCUMENTATION [DO]– Objective of the Documentation process is to develop documentation that augments product manuals with information about custom software and business procedures.
  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. 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. TRANSITION [TS]– Objective of the Transition process is to install the system and go production.
  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.
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.
Last week there was an email from Oracle for retirement for legacy Methods like AIM Advantage, AIM for Business Flows, EMM Advantage. These methods and Methodology been used by mostly Oracle Consulting,and there partners. The retirement date is January 31, 2011. Going Forward , OUM is going to be replaced by these methods, and not to forgot OUM version 5.3 was released a month back (November 13,2010).
OUM aka ORACLE UNIFIED METHOD is currently available to certified Oracle Partners(Platinum and Gold) and it is not freely downloadable publically. OUM supports the complete range of Oracle technology projects including:
  • Oracle Database
  • Oracle E-Business Suite
  • Oracle Enterprise 2.0
  • Oracle Fusion Middleware
  • Oracle Service-Oriented Architecture (SOA)
  • Oracle Business Intelligence – Applications
  • Oracle Business Intelligence – Technology
  • Oracle Data Warehousing
  • Governance, Risk, & Compliance (GRC)
  • Identity Management (IdM)
  • Oracle Transportation Management
  • Performance Management – Business Intelligence and Analytics
  • JD Edwards EnterpriseOne
  • PeopleSoft Enterprise
  • Siebel
Will keep you posted with some more updated on OUM time to time.