Tuesday, April 29, 2014

Oracle Fusion General chapter -1

1. Implementation process

Maintaining Setup Data: Explained



 
All implementations require modifications to the setup data over time. A full implementation cycle using an implementation project is recommended for larger and higher risk modifications, such as reorganizing territories, or adding a new tax regime. You can make smaller changes such as changing profile options, or a list of values by searching for the appropriate task from the Overview page and performing the task directly from there.
When setup data of an existing implementation requires modification that involves a significant number of tasks and task lists or has high risk implication in many parts of an application, maintaining those setup changes through implementation projects is advisable. Often you should perform the modifications in a test environment to mitigate risks before applying the changes to a production environment.
For maintenance of setup data, you can create an implementation project without selecting offerings or options. You can add tasks and task lists specific to the needed modification directly to the implementation project. Once the implementation project is created, functionality is exactly the same as any implementation project you create based on offerings and options. The processes for assigning users to tasks to maintain the setup data and finally to export and import the changes to the target instance is the same as implementation projects you create based on offerings and options.



Implementation Project Manager

Implementation project managers are typically responsible for the overall implementation of an Oracle Fusion application. They research and analyze the functionality of Fusion offerings and match them to the business requirements of the implementation. They also determine what setup tasks need to be performed and who will perform them to make an Oracle Fusion application ready for transactional processing.

Functional User

Functional users are representatives from various lines of business of a company and are subject matter experts in their functional areas. They are the financial administrators, procurement managers, sales operations representatives, and benefits administrators. Typically, they will be responsible for entering setup data and then verifying that the applications are working correctly for the transactional processes.

System Administrator

System administrators typically come with technical background and are responsible for software installation, systems configuration, and data migration. The export and import of setup data will usually be performed by system administrators.


Implementation Projects: Explained




An implementation project is the list of setup tasks you need to complete to implement selected offerings and options. You create a project by selecting the offerings and options you want to implement together. You manage the project as a unit throughout the implementation lifecycle. You can assign these tasks to users and track their completion using the included project management tools.

Maintaining Setup Data

You can also create an implementation project to maintain the setup of specific business processes and activities. In this case, you select specific setup task lists and tasks

Exporting and Importing

Implementation projects are also the foundation for setup export and import. You use them to identify which business objects, and consequently setup data, you will export or import and in which order.

Selecting Offerings

When creating an implementation project you see the list of offerings and options that are configured for implementation. Implementation managers specify which of those offerings and options to include in an implementation project. There are no hard and fast rules for how many offerings you should include in one implementation project. The implementation manager should decide based on how they plan to manage their implementations. For example, if you will implement and deploy different offerings at different times, then having separate implementation projects will make it easier to manage the implementation life cycles. Furthermore, the more offerings you included in an implementation project, the bigger the generated task list will be. This is because the implementation task list includes all setup tasks needed to implement all included offerings. Alternatively, segmenting into multiple implementation projects makes the process easier to manage.


Manage Application Implementation: Overview



The Manage Applications Implementation business process enables rapid and efficient planning, configuration, implementation, deployment, and ongoing maintenance of Oracle Fusion applications through self-service administration.
The Setup and Maintenance work area offers you the following benefits:
  • Prepackaged Lists of Implementation Tasks
    Task lists can be easily configured and extended to better fit with business requirements. Auto-generated, sequential task lists include prerequisites and address dependencies to give full visibility to end-to-end setup requirements of Oracle Fusion applications.
  • Rapid Start
    Specific implementations can become templates to facilitate reuse and rapid-start for comparable Oracle Fusion applications across many instances.
  • Comprehensive Reporting
    A set of built-in reports helps to analyze, validate and audit configurations, implementations, and setup data of Oracle Fusion applications.
With Oracle Fusion Functional Setup Manager you can:
  • Learn about and analyze implementation requirements.
  • Configure Oracle Fusion applications to match your business needs.
  • Achieve complete visibility to setup requirements through guided, sequential task lists downloadable into Excel for project planning.
  • Enter setup data through easy-to-use user interfaces available directly from the task lists.
  • Export and import data from one instance to another for rapid setup.
  • Validate setup by reviewing setup data reports.
  • Implement all Oracle Fusion applications through a standard and consistent process.
The following documentation resources are available for learning how to configure Oracle Fusion Applications.
  • Functional Setup Manager Developer's Guide
  • Common Implementation Guide
  • Customer Data Management Implementation Guide
  • Enterprise Contracts Implementation Guide
  • Marketing Implementation Guide
  • Sales Implementation Guide
  • Fusion Accounting Hub Implementation Guide
  • Financials Implementation Guide
  • Compensation Management Implementation Guide
  • Workforce Deployment Implementation Guide
  • Workforce Development Implementation Guide
  • Incentive Compensation Implementation Guide
  • Procurement Implementation Guide
  • P6 EPPM Administrator's Guide for an Oracle Database
  • P6 EPPM Administrator's Guide for Microsoft SQL Server Database

Getting Started with an Implementation: Overview


To start an Oracle Fusion Applications implementation, you must set up one or more initial users using the super user that was created during installation and provisioning of the Oracle Fusion Applications environment, or using the initial administrator user provided by Oracle for Oracle Cloud Application Services implementations. Because Oracle Fusion Applications is secure as delivered, the process of enabling the necessary setup access for initial users requires several specialized steps when getting started with an implementation.
The following high level steps are required for starting an implementation.
  1. If you are not starting an Oracle Cloud Application Services implementation, sign into Oracle Identity Manager (OIM) as the OIM Administration users and provision the IT Security Manager job role with roles for user and role management. This enables the super user account, which is provisioned with the IT Security Manager job role, to create implementation users.
  2. For starting all implementations, sign in as the user with initial access: either the Oracle Fusion Applications installation super user or the initial Oracle Cloud Application Services administrator user.
  3. Select an offering to implement, and generate the setup tasks needed to implement the offering.
  4. Perform the following security tasks:
    1. Synchronize users and roles in the Lightweight Directory Access Protocol (LDAP) store with HCM user management by using the Run User and Roles Synchronization Process task.
    2. Create an IT security manager user by using the Create Implementation Users task.
    3. Provision the IT security manager with the IT Security Manager role by using the Provision Roles to Implementation Users task.
  5. As the newly created IT security manager user, sign in to Oracle Fusion Applications and set up at least one implementation user for setting up enterprise structures.
    1. Create an implementation user by using the Create Implementation Users task.
    2. Provision the implementation user with the Application Implementation Manager job role or the Application Implementation Consultant job role by using the Provision Roles to Implementation Users task. The Application Implementation Consultant job role inherits from all product-specific application administrators and entitles the necessary View All access to all secured object.
    3. Optionally, create a data role for an implementation user who needs only the limited access of a product-specific Application Administrator by using the Create Data Role for Implementation Users. Then assign the resulting data role to the implementation user by using the Provision Roles to Implementation Users task.
The figure shows the task flow from provisioning the IT Security Manager job role with the user and role management entitlement to creating and provisioning implementation users for enterprise setup.
Three swim lanes for the OIM Administrator
user, the installation super user, and the IT Security Manager or
Security Administrator user, if available, show the tasks each type
of user performs to create and provision one or more implementation
users for setting up enterprise structures.




Enterprise structure

Oracle Fusion Applications have been designed to ensure your enterprise can be modeled to meet legal and management objectives. The decisions about your implementation of Oracle Fusion Applications are affected by your:
  • Industry
  • Business unit requirements for autonomy
  • Business and accounting policies
  • Business functions performed by business units and optionally, centralized in shared service centers
  • Locations of facilities
Every enterprise has three fundamental structures, legal, managerial, and functional, that are used to describe its operations and provide a basis for reporting. In Oracle Fusion, these structures are implemented using the chart of accounts and organizations. Although many alternative hierarchies can be implemented and used for reporting, you are likely to have one primary structure that organizes your business into divisions, business units, and departments aligned by your strategic objectives.
This figure is a grid in the shape
of a cube with the Business Axis representing the enterprise division,
Legal Axis representing the companies, and the Functional Axis representing
the business functions.

Legal Structure

The figure above shows a typical group of legal entities, operating various business and functional organizations. Your ability to buy and sell, own, and employ comes from your charter in the legal system. A corporation is a distinct legal entity from its owners and managers. The corporation is owned by its shareholders, who may be individuals or other corporations. There are many other kinds of legal entities, such as sole proprietorships, partnerships, and government agencies.
A legally recognized entity can own and trade assets and employ people in the jurisdiction in which it is registered. When granted these privileges, legal entities are also assigned responsibilities to:
  • Account for themselves to the public through statutory and external reporting
  • Comply with legislation and regulations
  • Pay income and transaction taxes
  • Process value added tax (VAT) collection on behalf of the taxing authority
Many large enterprises isolate risk and optimize taxes by incorporating subsidiaries. They create legal entities to facilitate legal compliance, segregate operations, optimize taxes, complete contractual relationships, and isolate risk. Enterprises use legal entities to establish their enterprise's identity under the laws of each country in which their enterprise operates.
In the figure above, a separate card represents a series of registered companies. Each company, including the public holding company, InFusion America, must be registered in the countries where they do business. Each company consists of various divisions created for purposes of management reporting. These are shown as vertical columns on each card. For example, a group might have a separate company for each business in the United States (US), but have their United Kingdom (UK) legal entity represent all businesses in that country. The divisions are linked across the cards so that a business can appear on some or all of the cards. For example, the air quality monitoring systems business might be operated by the US, UK, and France companies. The list of business divisions is on the Business Axis. Each company's card is also horizontally striped by functional groups, such as the sales team and the finance team. This functional list is called the Functional Axis. The overall image suggests that information might, at a minimum, be tracked by company, business, division, and function in a group environment. In Oracle Fusion Applications, the legal structure is implemented using legal entities.

Management Structure

Successfully managing multiple businesses requires that you segregate them by their strategic objectives, and measure their results. Although related to your legal structure, the business organizational hierarchies do not need to be reflected directly in the legal structure of the enterprise. The management structure can include divisions, subdivisions, lines of business, strategic business units, and cost centers. In the figure above, the management structure is shown on the Business Axis. In Oracle Fusion Applications, the management structure is implemented using divisions and business units.

Functional Structure

Straddling the legal and business organizations is a functional organization structured around people and their competencies. For example, sales, manufacturing, and service teams are functional organizations. This functional structure is represented by the Functional Axis in the figure above. You reflect the efforts and expenses of your functional organizations directly on the income statement. Organizations must manage and report revenues, cost of sales, and functional expenses such as research and development (R&D) and selling, general, and administrative (SG&A) expenses. In Oracle Fusion Applications, the functional structure is implemented using departments and organizations, including sales, marketing, project, cost, and inventory organizations


job role

A role for a specific job consisting of duties, such as an accounts payable manager or application implementation consultant. A type of enterprise role.
Information Technology Management > Manage IT Security > Implement Function Security Controls.    
 
 

Maintaining Setup Data: Explained


 
            


All implementations require modifications to the setup data over time. A full implementation cycle using an implementation project is recommended for larger and higher risk modifications, such as reorganizing territories, or adding a new tax regime. You can make smaller changes such as changing profile options, or a list of values by searching for the appropriate task from the Overview page and performing the task directly from there.
When setup data of an existing implementation requires modification that involves a significant number of tasks and task lists or has high risk implication in many parts of an application, maintaining those setup changes through implementation projects is advisable. Often you should perform the modifications in a test environment to mitigate risks before applying the changes to a production environment.
For maintenance of setup data, you can create an implementation project without selecting offerings or options. You can add tasks and task lists specific to the needed modification directly to the implementation project. Once the implementation project is created, functionality is exactly the same as any implementation project you create based on offerings and options. The processes for assigning users to tasks to maintain the setup data and finally to export and import the changes to the target instance is the same as implementation projects you create based on offerings and options.
 

Wednesday, April 23, 2014

Jdev Points - ADF Fusion Web Application

1. Open Jdev
2. With all feature options
3. New applciation
4. AF fusion web application
5. Application name/Prohect1 name/model/
6. Project2 --> View Controller
7. Connect to B in application resource mode
8. Build Business services
9. Create entity obs in Model Project
10 get two tables
11 Entity based view objct
12. Query based view object
13. Create application module
14. Run Appmodule---> we will see all entity objects and view objects
15. For UI
16. Go to View Controller project
17 new --> web tier --> Page
18 Create a JSF page with reference to ADF page template
19. Delete last  template faet
20 Add panel accordian
21 In panel accordian we will have show detail item --> change the name todepartments
22 Insert after showw detaiul item --> show detail item
23. More Info
24 In center template facet ass Panel Spitter --> vertical
25 add facet1 add panel collection
26 in facet2 add Tabbled panel
27 Bind data obbjects to UI
28 Open data control
29 Bind EO and VOs to each section as AF form  or table
30 Run

--Refine the Business Services

Saturday, April 12, 2014

Jdev Points-Getting Started chapter1

1. Create application
2. Create projects
3. Select project feature
4 In prj we will create a new java class
5. use ctr -enter to see suggestions
SOP --> system output ln
6. learnt for loop
7. //TODO concept
8. generate accessors
9. Generate constructor
10. Code Insight
11. Refactoring
-interfacing
-renaming
-introducing parameter
12. History
13. JavaDoc
14. Search files
15. Debugging by Breakpoints
16. Managed Bean usein JSF page
17. Config file --> drag jsf page
18. Add java class as a managed bean
19. JSP page sud be JSP.xml
20 Add input text and command button
21 change properties for input text
22 add dag.name as value for input text
23 integrated weblogic server
24 passwd --> weblogic1
25 Run
27 first application sucessful
28 Dependency explorer


Sunday, March 30, 2014

Oracle Incentive Compensation

Notes from OICig.oaug.org
1. OIC --> Indivi--> Role --> Comp Plan --> Trx --> How Much money.
2. When to Compensate.

Application Footprint

--> Trx Input --> OM/AR and Legacy
--> Individual --> Resource Manager/Territory Manager
--> Setups --> Comp Plans
--> Process --> Collection/Calculations/Payment
--> Output to --> Payroll emp/Payables/Vendor

Reports --> Discoverer/JSP Reports


Resource Manager --> CRM foundation Module.

Employee data imported form HRMS system to resource Manager.

OIC --> Can pay all types of resources.

Resource Manager -->

Roles

--> Grp of resources
---> assign compensation plan to all
--> Multiple roles possible for same resource.

Grps -->

--> Define the hierarchy
--> Assign usage to a resource grp determines which application can access the grp
--> Arrange into Hierarchy to to define rollup and approval hierarchies.

Territory Manager

--> Scalable Rule based assiugnment engine
--> Define who owns what
--> Enable Org to automatically assign Business Objects on configurable Business rules.

Assign resources based on Parties/party sites. Postal code and country etc.

Terri Mgr & saes credit allocation --> Terr M in conjuction with OIC detemine whiare the cedit receivers for the sales trx.

Compensation Process Overview
----------------------------------

Sales Planning --> Plan design --> Define Resources and roles -> Assign Plans --> Quata allocation and Distribution --> Finalize the plan and distribute--> Plan Maintenance --> Collection --> Calculaiton ---> Trx adjustment --> Payment -> Reporting

-----------------------------
Compensation Plan
--> Set of Plan elements
--> Defined with a target compensation in Mind.

------------

Comp Plan --> PE --> Bonus --> Formula and rate table
                            --> Commision --> RT /Formula/Revenue Class

PE --> Grping of Revenue Classes --> Determine the eligibility of the transactions for compensation purposes.

PE -> Achievement levels/Accelarators/Calculation formulas/RT

Revenue Class --> Compensation by type of revenue --> determine if a Salesperson can be paid for a particular trx or not.

Formula --> Inpt and Outpt expression of formulas.

Dimensations --> Define the tiers that are used in a rate tables to calculate commision %age.
Rate tables -->

Comp Trx Flow -->

Input data --> Collection --> calculation --> payment --> report

Collections --> Legacy + seeded
                      Unprocessed Transaction Report -> Do the adjustments
                      Load process

Phases of calcualtions -->

Classification phase --> attaches the revenue class
 Rollup phase -->  determines who all salesperson should receive the credit for the trx . Rollup Trxns.

Populations --> OIC identifies the appropriate plan element and revenue classes that matches salesperson's compensation plans.

Calculation phase --> Totals the credit and checks the quota figure to deterine rate tier, calculates the final commision amt.

Payment process --> Who is to be paid --> Pay grp --> for what -> and whrn
Payment -> pushes data to payroll/AP or legacy

Payment process -->

Payrun/Payrun details --> Payrun -> send to ayroll





Wednesday, November 27, 2013

Profile Value Option

Source --> http://www.anilrpatil.com/oracle/Profile_Option_Values.htm

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level
1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro1.user_profile_option_name like ('%Ledger%')
and  pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.
SELECT
substr(pro1.user_profile_option_name,1,35) Profile,
decode(pov.level_id,
10001,'Site',
10002,'Application',
10003,'Resp',
10004,'User') Option_Level,
decode(pov.level_id,
10001,'Site',
10002,appl.application_short_name,
10003,resp.responsibility_name,
10004,u.user_name) Level_Value,
nvl(pov.profile_option_value,'Is Null') Profile_option_Value
FROM 
fnd_profile_option_values pov,
fnd_responsibility_tl resp,
fnd_application appl,
fnd_user u,
fnd_profile_options pro,
fnd_profile_options_tl pro1
WHERE
pro.profile_option_name = pro1.profile_option_name
and  pro.profile_option_id = pov.profile_option_id
and  resp.responsibility_name like '%General%Ledger%'
and  pov.level_value = resp.responsibility_id (+)
and  pov.level_value = appl.application_id (+)
and  pov.level_value = u.user_id (+)
order by 1,2;

Tuesday, July 16, 2013

Inventory -> More queries

http://www.oracleport.com/oracle-r11-r12-important-queries/

Oracle R11, R12 Important Queries (Concurrent , Inventory, Purchaing , HR, Payrol , OPM)
To Find Duplicate Item Category Code
SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments
Get Number Of canceled requisition
SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY
Number of line processed in Order Management
SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;
To Check Item Catogry For Inventory master (No Of Segments May Varry)
SELECT ood.organization_name,
segment1|| ‘-’|| segment2|| ‘-’|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name
Check Locators for inventory Inventory Org Wise(Number of segment may varry)
SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID
Display All Subinventories Setup
select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name
To Select Unit Of measure exist in ebusiness suite
select uom_code,unit_of_measure
from mtl_units_of_measure
Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.
select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID
Query to Find Responsibilities assigned to particular user.
SELECT b.responsibility_name NAME
FROM apps.fnd_user_resp_groups a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND u.user_id = (select user_id from FND_USER where user_name=:User_name)
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL
Query To find the Request Run By Concurrent
select b.user_concurrent_queue_name, c.request_id
from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.concurrent_queue_id = b.concurrent_queue_id
and a.concurrent_process_id = c.controlling_manager;
Query to find on Hand Quantity
select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188
Qunatity on order, Expected Deliverselect sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE
Query to find Item Code, Item Description Oracle Item Master Query(select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item,
Query to Find out On Hand Quantity of specific Item Oracle inventory
(select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id) onhand,
– Qty Issued by X No clue what you want here
–Qty On Order,Expected deivery date(select sum(ordered_quantity),scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info,
–Toatl Received Qty
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0) tot_rec_qty,
–Total received Qty in 9 months
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_rec_qty_9mths,
–Total issued quantity in 9 months(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) avg_month_consumption; Find detail of specific Applications
Running in System System Administrator
SELECT application_id,APPLICATION_NAME
FROM fnd_application_tl
WHERE application_name = ‘Purchasing’
AND rownum = 1;
Find the Request Groups and Concurrent Progams
SELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
–and c.USER_CONCURRENT_PROGRAM_NAME like ‘%Purchase Order%’
ORDER BY C.user_concurrent_program_name, A.application_name, g.request_group_id;
Display all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv
Another Query to Get Onhand Qty With Oranization ID, Item Code, Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-’|| msib.segment2|| ‘-’|| msib.segment3|| ‘-’|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)