Tuesday, 26 April 2011

Query : Get the Account Description


Query : Get the Account Description

select segment1,segment2,segment3,
apps.gl_flexfields_pkg.get_description_sql(&char_of_account_id,1,gcc.segment1) seg1,
apps.gl_flexfields_pkg.get_description_sql(&char_of_account_id,1,gcc.segment2) seg2,
apps.gl_flexfields_pkg.get_description_sql(&char_of_account_id,3,gcc.segment3) seg3
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = &code_combination_id;

GIVE THE chart of account_id as same value for seg1,seg2 and seg3
and give the  code_combination_id

Supply Chain Management (SCM) :Techno-functional Guide


Supply Chain Management (SCM) :Techno-functional Guide
KNOW WHAT IS SUPPLY CHAIN MANAGEMENT (SCM)? : An Overview
his is one of the basic areas from management books. In fact lot of people have requested some more information on SCM, OPM and other manufacturing area. therefore,I take this opportunity, as this is one of my favorite topic, as I started my first job in PPC (Production Planning &Control) department of a big steel company a decades ago, so I hope I will surly help in giving a clear picture of SCM. Let's start with basic and gradually will move into ERP arena:
SCM is defined as combination of art and science that goes into improving the way your company find the raw components that needs to make a product or services and deliver it to customer. In Business word this art and science become functions that an organization undertake.
If you are IT guys you can understood SCM as:
Systems that support manufacturing managers in making decisions that optimize the trade off between capital tied up in stocks and inventories, versus the ability to deliver goods at prices and delivery dates agreed with customers.
In principle and reality, both inwards logistics operations to acquire materials to make products and outwards logistics operations shipping finished goods to final customers are monitored.
bluWHAT EXACTLY IS SUPPLY CHAIN MANAGEMENT OR WHAT IS BASIC COMPONENT OF SCM?
As per Supply-Chain Operations Reference-model (SCOR®) which has been developed by Supply-Chain Council. This model organized and focused on the five primary management
  1. PLAN
  2. SOURCE
  3. MAKE
  4. DELIVER
  5. RETURN
1. Plan: This is vital part of SCM philosophy, where the companies normally need to make strategy for managing all the resource that go towards fulfilling the customer demand for the product and services that they offers. A big piece of planning is developing a set of matrices to monitor the Supply chain so that it would be efficient, cost effective and deliver high quality and value to the customer.
2. Source: It means processes that procure goods and services to meet planned or actual demand. This part of SCM consists of selecting right suppliers that will deliver the good and services that need to create your product. Developing a set of pricing, delivery and payment process with supplier is important. Also this will also take care of managing the inventory of goods, and services you receive from your suppliers, including receiving shipping, verifying them, transferring them into various facilities and authorizing supplier payment.
3. Make: This is basically a step where your company starts fulfilling the request or BUILT for products into finished state to meet planned or actual demand. Schedule activity necessary for production, testing, packaging and preparation for delivery.
4. Deliver: This is also called Logistic Process. This is the processes that provide finished goods and services to meet planned or actual demand, typically including order management, transportation management, and distribution management.
5. Return - This is real pain of SCM model, which defined as processes associated with returning or receiving returned products for any reason.
Typical model can be best described as:
scmmodel
purpWHO IS THE STAKEHOLDER OF SUPPLY CHAIN
These are considered as stake holder of SCM:
  • Customers
  • Your Company
  • Design Partners
  • Material Suppliers
  • Contract Manufacturers
  • Logistic Providers
red1WHAT ARE DRIVERS OF THE SUPPLY CHAIN?
These are the main drivers :
  • Production
  • Inventory
  • Location
  • Transportation
  • Information
1. Production
This driver addressing these questions: what products does the market want? How much of which products should be produced and by when?
This activity includes the creation of master production schedules that take into account plant capacities, workload balancing, quality control, and equipment maintenance.
2. Inventory
This driver addressing these questions: What inventory should be stocked at each stage in a supply chain? How much inventory should be held as raw materials, semi finished, or finished goods?
The primary purpose of inventory is to act as a buffer against uncertainty in the supply chain.
3. Location
This driver addressing these questions: Where should facilities for production and inventory storage be located? Where are the most cost efficient locations for production and for storage of inventory? Should existing facilities be used or new ones built?
Once these decisions are made they determine the possible paths available for product to flow through for delivery to the final consumer.
4. Transportation
This driver addressing these questions: How should inventory be moved from one supply chain location to another? Air freight and truck delivery are generally fast and reliable but they are expensive. Shipping by sea or rail is much less expensive but usually involves longer transit times and more uncertainty.
5. InformationThis driver addressing these questions: How much data should be collected and how much information should be shared?
Timely and accurate information holds the promise of better coordination and better decision making. With good information, people can make effective decisions about what to produce and how much, about where to locate inventory and how best to transport it.
oraGET FAMILIAR WITH TOP 10 TERMS, DEFINITIONS AND TERMINOLOGY IN SCM
f1Master Demand Schedule - MDS
The MDS is a consolidation of demand by product and time bucket
f2Master Production Schedule- MPS
The MPS is a statement of supply required to meet the demand for the items contained in the MDS. The master production schedule defines the anticipated build schedule for all products. The master production schedule also provides the basis for order promising (ATP) function
f3Material Requirements Plan-MRP
The Material requirements planning (MRP) calculates net requirements from gross requirements by evaluating:
  • The master schedule
  • Bills of material
  • Scheduled receipts
  • On-hand inventory balances
  • Lead times
  • Order modifiers
f4Advanced Supply Chain Plan- ASCP
Constrained Based and optimized version of MRP
f5Planned Order
Automatically suggested action from planning engine
f6Consumption
The process of "relieving" the forecast to prevent double counting of demand
f7Drop Ship
Having an order ship directly from the vendor to the customer without physically being in your inventory.
f8Vendor Managed Inventory- VMI
The process of giving the vendor the authority and visibility to determine what your inventory should be
f9Customer Owned Inventory- COI
Where you are managing the customers inventory on your premises and supply as required
f10Work Order/Sales Order
The request that you received from the customer for fulfilling there demand.
greSIMPLE PLANNING CYCLE WITHIN AN ORGANIZATION
A typical planning cycle would start by loading the sales orders, forecast and other demand such internal orders into the master demand schedule (MDS). That demand statement would then be used as the schedule that drives the Master Production Schedule (MPS) . Take a note in regular MRP implementations the MPS is used as the schedule for ATP. Once the MPS is reviewed and updated is used as the schedule to drive the MRP process. The result of the MRP process is planned orders and exception messages. When released from MRP the planned orders for "buy" items become requisitions or purchase orders in the purchasing module and the "make" items become discrete jobs in the Work In Process Module. Once the finished goods are received into inventory it is shipped to satisfy customer orders.
PlanningwithinOrgnization
Scenario I :Sales Order - Forecast Planning Cycle
Forecast Planning
This is the scenario where normally we are building to stock and satisfying sales order demand from stock. Here you will see the difference as planned orders are released to become purchase requisitions for components and work orders for sub assemblies and finished goods. The finished goods are then shipped to satisfy the sales orders.





Scenario II : Planning - Procurement Cycle
ProcurementCycleIn the Planning-Procurement cycle we normally start where the first part of the planning cycle is complete and we have a planned order for a buy item from MRP. The planned order is released and becomes a requisition or purchase order in the purchasing module. A purchase order is then created from the requisition and sent to the vendor. The vendor would supply the materials. The materials would be received into inventory and the purchase order would be closed.



Scenario III :Planning - Drop Ship Cycle
In the Drop Ship cycle the process is as follows:
  • A sales order is received from the customer.
  • After the sales order is entered a process is run that creates a purchase order that matches the sales orders
  • The item, quantity and required date information on purchase order matches that information on the sales order.
  • The ship to address on the purchase order is the ship to address of the customer.
  • The vendor ships the product directly to your customer. Once the vendor ships the product you receive" the purchase order and that creates the shipping transaction to satisfy the sales order.
  • Matching accounting transactions complete the process
DropShipmentCycle
You can also see technical details for drop ship cycle in my earlier post.
Scenario IV :Planning - WIP Cycle
In the Work In Process cycle after the planning cycle is complete a planned order is released to create a discrete job. Material/components is issued the job or back flushed from inventory. The job is completed from Work In Process to Inventory.
wipcycle
Scenario V : Planning - Outside Processing Cycle
With Outside Processing you could be buying a service, an item or capacity from an outside vendor. The setup for each of these scenarios will be described in the training sessions to follow, however they all follow a similar process. A routing with an outside processing operation is setup.
After the planning cycle is complete a planned order is released to create a discrete job. Once the Discrete job is moved to a outside processing operation, a purchase requisition is triggered. The requisition is imported to Purchasing and a purchase order is created. The details of the discrete job such as job number, assembly number and quantity is tied to the purchase order. The purchase order is sent to the vendor. When the purchase order is received its destination will be shop floor and the assembly will be returned to the next operation on the work order.
OutsideProcessing
This make an end of this discussion.

SCM Subsystem


In above we have understood SCM flow. Let's talk in term of SCM IT requirement. SCM subsystems are important because this is required to support manufacturing managers in making their decisions so that they can optimize the trade off between capital tied up in stocks and inventories, versus the ability to deliver goods at prices and delivery dates agreed with customers.
A typical flow and SCM cycle can be understood as fig below:SCMFlowinERP
Typically A SCM consists of these sub-systems can be categorize and consist of:

f1Demand Management
This is basically co-ordination sales forecasts from all the players, including sales representatives at the point of sale through production to raw material suppliers, so that everyone is working to the same plan, rather than creating unnecessary safety stocks.
f2Distribution Resources Planning (DRP)
This will Keep track of the status of production and procurement of materials for each order, where the goods are located, what transport and warehousing is in use, what stock to keep and when to re-order.
f3Transport Management Systems (TMS)
This kind of subsystem normally keep the information such as :what modes of transport at what cost are available to the shipper; what costs and timings are associated with each route; the order in which to load transport; optimization of multi-segment deliveries; compliance with documentation required for customs and shippers.
f4Warehouse Management Systems (WMS)
These are the systems which are used to optimize the storage and picking of goods in a warehouse, also includes compliance with carrier and customer documentation standards.
f5Supplier Relationship Management (SRM)
This kind of subsystem keeps the information about suppliers of direct and indirect materials, such as agreed terms of trade, records of status, records of contacts between the enterprise and the various points of contact in suppliers.
f6Component Supplier Management (CSM)
Management of lists of preferred suppliers and preferred parts; terms of trade such as delivery and price associated with them; rationalization of existing parts to minimize costs by substituting equivalent parts.
I guess, the next question in mind would what are applications which are going to enable if anyone is going to roll out SCM functionality in Oracle EBS. Keep watching this space.
Supply Chain Management (SCM)
This article is an easy guide for Techno-functional consultant to understand SCM from Implementation as well as Oracle Application product Prospective.Lets start the topic with some of high points of earlier post .

  • As mention in earlier post a Supply Chain is a network of retailers, distributors, transporters, storage facilities and suppliers that participate in the sale, delivery and production of a particular product.
    • Make a note a supply chain is product specific, not company specific
  • Supply chain management (SCM) is a systematic approach to manage the entire flow of information, materials, and services from raw material suppliers through factories and warehouses to the end customer.
  • Moreover , SCM involves the flows of materialinformation and finance in a network consisting of customers,suppliers, manufacturers, and distributors.
  • You should be clear with Flow that SCM can manage :supply Chain Flow
    • The flow of actual materials, the top middle bars
      • From suppliers : flows of raw materials, intermediate products, finished goods
      • Reverse material flows : returns, repairs, servicing, recycling, disposal
    • and the information flows
      • From suppliers : manufacturing capacity, delivery schedules, promotions they have going
      • Reverse flows : sales, orders, inventory, quality, promotions
    • And finally, there are financial flows:
      • From suppliers: Credits, consignment, payment terms, invoice
      • Reverse Flows : payments, consignment
  • Supply Chain Management is the management of the entire value-added chain, from the supplier to manufacturer right through to the retailer and the final customer.
  • SCM has three primary goals: Reduce inventoryincrease the transaction speed by exchanging data in real-time, and increase sales by implementing customer requirements more efficiently
  • The need for SCM is because effective Supply Chain Mgt. is the next logical step towards increased profits and market share.
  • Supply Chain Management (SCM) in line manager prospective is "let's-keep-things-moving-efficiently".
dgreybarrowWhat is Oracle's SCM Solution for Customer
Oracle Supply Chain Management (SCM) basically integrates and automates all key supply chain activities starting from design, planning, and procurement to manufacturing and fulfillment.It depends upon how the company or Line managers planning to use these application module in there business line. For better understanding from Product point, lets understood on eight key area as per figure:
SCM
Here are the details with underline Product which is potentially used.
1.Develop : SCM start with developing new products where Product specifications are created.
  • Oracle Product Life cycle Management(PLM)
  • Oracle Advanced product Catalog (EGO )
2.Market :Marketing and Sales generates demand for the Product by publicizing its features and how it would address customer priorities. In the process, Marketing also gets customer feedback and communicates to Product development group. These are application potentially can be used.
  • Telesales (AST)
  • Trade Management (OZF)
  • Incentive Compensation (CN)
  • Order Capture (ASO)
  • Partners Online (POL) *
* Oracle Partners Online (POL) is also known as Oracle Partners Online (PRM).
3. Plan :Planning is the strategic portion of SCM. You need a strategy for managing all the resources that go toward meeting customer demand for your product or service.
  • Advanced Supply Chain Planning (MSC)
  • Global Order Promising (GOP )
4.Sell & Manage Orders :Maintain and manage the customer orders, order holds, notes and release the orders to warehouse for fulfillment based on requested date, product availability and customer credit limit.
  • Order management (OM)
  • Advanced Pricing (QP)
  • Configurator (CZ)
  • iStore (IBE)
5.Procure :Here you normally choose the suppliers that will deliver the goods and services you need to create your product. Develop a set of pricing, delivery and payment processes with suppliers and create metrics for monitoring and improving the relationships.
  • Purchasing (PUR)
  • iProcurement(iPROC)
6.Manufacture :Schedule the manufacturing activities necessary for production, testing, packaging and preparation for delivery.
  • Cost Management (CST)
  • Process Manufacturing (GMA)
  • Project Manufacturing (PJM)
  • Quality (QA)
  • Work in Process(WIP )
7.Fulfill & Release Management :Coordinate the receipt of orders from customers, develop a network of warehouses, pick carriers to get products to customers and set up an invoicing system to receive payments.
  • Shipping Execution (WSH)
  • Inventory Management (INV)
  • WareHouse Management System (WMS)
  • Transportation Execution (FTE )
8.Service & Maintain :Create a network for receiving defective and excess products back from customers and supporting customers who have problems with delivered products.
  • Depot Repair (CSD)
  • Field Service (CSF)
  • Install Base (CSI)
  • Service Contracts (OKS)
  • Service Fulfillment Manager (XDP)
  • Spares Management(CSP )
Oracle Product Acronym Listing can be re-validated from Robert's Blog.
This does not cover the Financial flow with SCM.Will take a detail discussion in some other post.
dgreybarrowSCM Oracle Solutions
SCM Of Oracle E-Business Suite does covers each area of SCM as discussed in other post. Here is how best can describe as involvement of all five key participant.
SupplyChain Flow
Within SCM these are the business Process that can be fit with these application Module.
1)Procure to Pay
Here is flow that can be best understood with Oracle Modules Involvement.
Procure To pay Flow
Under this Business Process Oracle module can be potentially used as
  • Oracle Purchasing(PUR)
  • iProcurement(iProc)
  • iSupplier Portal(isupplier)
  • Payables(AP)
  • Purchasing Intelligence(for BI)
Check out earlier Post for P2P details.
2) Order to Cash

Here is flow that can be best understood with Oracle Modules Involvement.
SupplyChain Flow
Under this Business Process Oracle module can be potentially used as
  • Oracle Order Management (OM)
  • Oracle Inventory (INV)
  • Shipping Execution (WSH)
  • Advanced Pricing (QP)
  • Oracle Configurator (CZ)
  • Warehouse Management (WMS)
  • Receivables(AR)
Check out earlier Post for O2C, 1 & 2 details.
3) Lead to Service
Under this Business Process Oracle module can be potentially used as
  • CRM Foundation (JTF)
  • Sales Online (ASF)
  • Marketing Online (AMS)
  • Customer Care (CSC)
  • Install Base (CSI)
  • Depot Repair (CST)
  • Service Contracts (OKS)
  • iStore (IBE)
  • iSupport (IBU)
  • Field Service (CSF )
4) Forecast to Plan
Under this Business Process Oracle module can be potentially used as
  • Material Planning (MRP)
  • Advanced Supply Chain Planning (ASCP)
  • Collaborative Planning
5) Demand to Build
Under this Business Process Oracle module can be potentially used as
  • Demand Planning (MSD)
  • Bills of Material (BOM)
  • Work in Process (WIP)
  • Cost Management (CST)
  • Flow Manufacturing (FLM )


Certification for E-Business Applications(OracleApps) Consultant??


Certification for E-Business Applications(OracleApps) Consultant??


Do you know there are 475,000 Oracle certified professionals across the globe. Few credentials in the business world carry the value of Oracleapps certification for Consultant. Take the advantage of getting certified. Out of five major set of certification , OCE is being provided in OracleApps Area.
What is OCE...
aka.Oracle Certified Expert
This is advanced certification that required proven project experience, business process knowledge, and a more detailed understanding of Oracle Application Implementation solutions. This is new type of specialization certification which recognize competency in specific technologies, architectures or domains.
Available Certification





These are the certification currently available in Oracle Application area CoveringR12.
If you are planning for certification , check out the Mohan's Blog, this is good starting point. You can also check the Oracle web site for Certification details , pre-requisite , training requirement and and Fee structure.

Priority for Concurrent Program


Priority for Concurrent Program
If you are working in big user environment, user always complain there request get stuck or takes time to start processing. Majority of time Support team cann't do anything, unless waiting or asking DBA to check the server performance.
There is couple of option you can try , if you know a demanding user and there request name( Concurrent Program) he/she always want in periority.
dgreybarrowOptions 1: If you know what particular program user required in priority.
In oracle EBS you need to set the priority of specific users so that when they run specific reports, those reports should be run on high priority. The steps are very simple:
  1. Go to Sysadmin Responsibility and go to menu Profile > System.
  2. Make sure Site and User boxes are checked.
  3. Select the user name for the user for whom you want to increase the priority
  4. Enter the following within the Profile box and click on Find: Concurrent:Request Priority
  5. Change the priority for the user as you see fit (1 is highest, 99 is lowest, 50 is the default) , Save and exit.
Concurrent Priority1
dgreybarrowOptions 2: You know the Program name, and want to make execution in priority
The steps are very simple:
  1. Login system administrator /Application developer and go to define concurrent screen
    Under Concurrent >> Program >> Define
  2. Find the Program name whose priority you wish to change for ex. "Payable Transfer to GL"
  3. On this screen you can set priority of this request by entering value in "Priority" field( as mark in red) (1 is highest, 99 is lowest, 50 is the default) , Save and exit.
Concurrent Priority
dgreybarrow Oracle definition for Priority
Priority is used to indicate the priority that the concurrent request will be assigned when it is submitted. If you do not assign a priority, the user's profile option Concurrent:Priority sets the request's priority at submission.
dgreybarrow Profile : Concurrent:Request Priority
This displays the default priority number for your concurrent requests. Only a system administrator can change your request priority.
Requests normally run according to start time, on a "first-submitted, first-run" basis. Priority overrides request start time. A higher priority request starts before an earlier request.
Priorities range from 1 (highest) to 99 (lowest). The standard default is 50.
Users can see this profile option, but they cannot update it. This profile option is visible and updatable at all four levels.

Department Number, Category Number and Cost of item based on the Item ID

Department Number, Category Number and Cost of item based on the Item ID:-
--------------------------------------------------------------------------------------------

The Following Query will give the Department Number, Category Number, Cost of the Item based on the Inventory item ID.

select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories 
where inventory_item_id = 'Your Inventory Item ID'
and organization_id = 'Your Inventory Organization ID' and category_set_id = 1 and rownum = 1)
and cs.organization_id = 'Your Inventory Organization ID'
and cs.cost_type_id = 1
and cs.inventory_item_id = 'Your Inventory Item ID';

Example:-
-----------

select mcb.segment3 "Deptartment" ,mcb.segment4 "Category", cs.item_cost "Item price"
from mtl_categories_b mcb, cst_item_costs cs
where mcb.category_id IN (select CATEGORY_ID from mtl_item_categories
where inventory_item_id = 122251
and organization_id = 22 and category_set_id = 1 and rownum = 1)
and cs.organization_id = 22
and cs.cost_type_id = 1
and cs.inventory_item_id = 122251;

Get the Inventory code and Inventory Name/description

Get the Inventory code and Inventory Name/description:-
-------------------------------------------------------------------

Note:- Inventory Organization will be represented with the Organization_id column.
We many give some code and name to the Inventory. With the following Query we can know the Code and Name details of all the Inventories in the Organization.

SELECT distinct SUBSTR(loc.location_code, 1, 4) "Inventory Code"
,DECODE(SUBSTR(loc.tax_name, 1, 3) 
,'Bay', 'H' 
,'Zel', 'Z' 
,'KMT', 'Z') || lpad(substr(loc.location_code, 1, 4), 4, '0')|| ' ' ||
SUBSTR(loc.address_line_2, 1, 30) "Inventory Name/Description",
ou.ORGANIZATION_ID "Inventory Organization ID"
FROM hr_locations_all loc,
hr_all_organization_units ou 
WHERE loc.location_id = ou.location_id;

FlexFields


FlexFields

FlexFields:-
-------------

Flexfields stores main Information of the Organizations. We have many flexfields in different modules.

Note:- The following Flexfields are avilable in the 11.5.8 Oracle Application Version.
-----

You may find more flexfield in the new Oracle apps versions.

To check all the Flexfield avilable for your Oracle Apps version, use the Flexfield Query

Application Name
Flex-field Name
Application Description
CRL Financials Assets
Super Group
CRL Financials - Assets
CRL Financials Assets
Group Asset
CRL Financials - Assets
Oracle Advanced Service Online
AHL Route
Oracle Advanced Service Online
Oracle Advanced Service Online
AHL Operation
Oracle Advanced Service Online
Oracle Assets
Location Flexfield
Oracle Assets
Oracle Assets
Category Flexfield
Oracle Assets
Oracle Assets
Asset Key Flexfield
Oracle Assets
Oracle General Ledger
Accounting Flexfield
Oracle General Ledger
Oracle Human Resources
Soft Coded KeyFlexfield
Oracle Human Resources
Oracle Human Resources
Job Flexfield
Oracle Human Resources
Oracle Human Resources
Grade Flexfield
Oracle Human Resources
Oracle Human Resources
CAGR Flexfield
Oracle Human Resources
Oracle Human Resources
Competence Flexfield
Oracle Human Resources
Oracle Human Resources
Item Contexts Keyflex
Oracle Human Resources
Oracle Human Resources
Personal Analysis Flexfield
Oracle Human Resources
Oracle Human Resources
Position Flexfield
Oracle Human Resources
Oracle Inventory
System Items
Oracle Inventory
Oracle Inventory
Sales Orders
Oracle Inventory
Oracle Inventory
Item Categories
Oracle Inventory
Oracle Inventory
Account Aliases
Oracle Inventory
Oracle Inventory
Item Catalogs
Oracle Inventory
Oracle Inventory
ORACLE_SERVICE_ITEM_FLEXFIELD
Oracle Inventory
Oracle Inventory
Stock Locators
Oracle Inventory
Oracle Payroll
People Group Flexfield
Oracle Payroll
Oracle Payroll
Bank Details KeyFlexField
Oracle Payroll
Oracle Payroll
Cost Allocation Flexfield
Oracle Payroll
Oracle Public Sector Budgeting
PSB Position Flexfield
Oracle Public Sector Budgeting
Oracle Receivables
Territory Flexfield
Oracle Receivables
Oracle Receivables
Sales Tax Location Flexfield
Oracle Receivables
Oracle Receivables
ARTA-Receipt Prof Ident
Oracle Receivables
Oracle Training Administration
Training Resources
Oracle Training Administration