Oracle interview questions
|
|
1.
|
What are the various types of Exceptions ?
User defined and Predefined Exceptions. |
2.
|
Can we define exceptions twice in same block ?
No. |
3.
|
What is the difference between a procedure and a function ?
Functions return a single variable by value where as procedures do not return any variable by value. Rather they return multiple variables by passing variables by reference through their OUT parameter. |
4.
|
Can you have two functions with the same name in a
PL/SQL block ?
Yes. |
5.
|
Can you have two stored functions with the same name
?
Yes. |
6.
|
Can you call a stored function in the constraint of a
table ?
No. |
7.
|
What are the various types of parameter modes in a
procedure ?
IN, OUT AND INOUT. |
8.
|
What is Over Loading and what are its restrictions ?
OverLoading means an object performing different functions depending upon the no. of parameters or the data type of the parameters passed to it. |
9.
|
Can functions be overloaded ?
Yes. |
10.
|
Can 2 functions have same name & input parameters
but differ only by return datatype
|
No.
|
NAV_BAR
Monday, August 10, 2015
Technical Interview Questions
Friday, August 7, 2015
KNOW WHAT IS SUPPLY CHAIN MANAGEMENT (SCM)? : An Overview
This 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.
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.
- WHAT EXACTLY IS SUPPLY CHAIN MANAGEMENT OR WHAT IS BASIC COMPONENT OF SCM?
As per Supply-Chain Operations Reference-model 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:
- WHO 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
- WHAT 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.
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.
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.
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.
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.
Information
This driver addressing these questions: How much data should be collected and how much information should be shared?
This 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.
- GET FAMILIAR WITH TOP 10 TERMS, DEFINITIONS AND TERMINOLOGY IN SCM
- Master Demand Schedule - MDS
The MDS
is a consolidation of demand by product and time bucket
- Master 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
- Material 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
- Advanced Supply Chain Plan- ASCP
Constrained
Based and optimized version of MRP
- Planned Order
Automatically
suggested action from planning engine
- Consumption
The
process of "relieving" the forecast to prevent double counting of
demand
- Drop Ship
Having
an order ship directly from the vendor to the customer without physically being
in your inventory.
- Vendor Managed Inventory- VMI
The
process of giving the vendor the authority and visibility to determine what
your inventory should be
- Customer Owned Inventory- COI
Where
you are managing the customers inventory on your premises and supply as
required
- Work Order/Sales Order
The
request that you received from the customer for fulfilling there demand.
- SIMPLE 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.
Scenario I
:Sales Order - Forecast Planning Cycle
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
In 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
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.
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.
This
make an end of this discussion. In Next post we will see what are the different
subsystem SCM will have and what are the modules which comes under SCM
Implementation. Any comment is welcome :)
Tuesday, August 4, 2015
Quick note for Supplier in EBS
In Oracle supplier can be entered from three different place.These are the Setup required to perform for a suppliers.
- Supplier Accounts
- Taxes
- Payments
- Term Date Basis
- Due Date Basis
- Invoice Currency
- Holds
In Oracle these are the important features through the screen:
- Enter Suppliers
- Enter employees as Suppliers
- Merge duplicate Suppliers
Supplier Data Entry
Other Details- Classification Information (Significant for type and employee name)
- Parent subsidiary relationship
- Supplier bank information
- Appropriate GL accounts information
- Record tax information
- Hold conditions to supplier site (rather than on individual invoices)
and these are the optional field
- Tax payer ID
- VAT registration number
- Inactive date (to prevent invoice/PO entry after a certain date)
Important Table that holds supplier- PO_VENDORS : this hold a suppliers information
Some important columns:
- SEGMENT1 – SYSTEM GENERATED OR MANUALLY ENTERED NUMBER FOR SUPPLIER (PO_UNIQUE_IDENTIFIER_CONTROL)
- VENDOR_ID – SYSTEM GENERATED UNIQUE NUMBER
- CREDIT STATUS –GOOD/POOR
- ORGANIZATION_TYPE_LOOKUP_CODE
- VENDOR_TYPE_LOOKUP_CODE – VENDOR/EMPLOYEE (EMPLOYEE_ID)
- ONE_TIME_FLAG
- PO_VENDOR_SITES_ALL : This hold corresponding site information Contains Details of the site like Full Address/Work of site
and its link with vendor_id.Important columns includes:- PURCHASING_SITE_FLAG, VENDOR_SITE_CODE, RFQ_ONLY_SITE_FLAG, PAY_SITE_FLAG,HOLD_ALL_PAYMENTS_FLAG, HOLD_FUTURE_PAYMENTS_FLAG, HOLD_UNMATCHED_INVOICES_FLAG
- PO_VENDOR_CONTACTS : This hold supplier contact information for respective sites.
How they connected
These are typically connected as:
Take a note Segment1 is holding suppliers Number.
Quick query for new babies
select * from po_vendors
where segment1 = '1001‘;select *
from po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = '1001'
and pov.vendor_id = pvs.vendor_id;select *
from po_vendor_contacts pvc,
po_vendor_sites pvs,
po_vendors pov
where pov.segment1 = '1001'
and pov.vendor_id = pvs.vendor_id
and pvs.VENDOR_SITE_ID = pvc.vendor_site_id;
Supplier Interface
Yes, that was one of the enhancement in oracle 11.5.10 release. Prior to that version, there was no interface or Public exist for supplliers, thus it was a tricky to handle supplier. As we have already seen AP availability of AP some time back.
If you are having 11.5.10+ version,and if you have requirement to create supplier from your legacy data than use Supplier Interface, life will be easier. Is n't.
How we start
This consist in 2 steps process:
Step 1. Populate the new interface tables with data. This can be done via SQL*Loader or typically used to load data in a table.
- AP_SUPPLIERS_INT
- AP_SUPPLIER_SITES_INT
- AP_SUP_SITE_CONTACT_INT
Step 2. Run the Supplier Open Interface Request Set (FNDRSSUB1703)
Alternatively, run the individual import programs to load one table at a time
- Supplier Open Interface Import (APXSUIMP)
- Supplier Sites Open Interface Import (APXSSIMP)
- Supplier Site Contacts Open Interface Import (APXSCIMP)
Some important notes for suppliers Interface
- When you load the data it should be 'NEW' initially. After the Supplier Sites Open Interface is run the status gets changed to PROCESSED'/'REJECTED'.
The entire flow can be shown as:
A Note with R12 for Suppliers
As we know in R12 Supplier is part of TCA , thus the link between PO_VENDORS and HZ_PARTIES is PO_VENDORS.party_id. The link between
PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.
PO_VENDOR_SITES_ALL and HZ_PARTY_SITES is PO_VENDOR_SITES_ALL.party_site_id.
When a Supplier is created Record will be Inserted in HZ_PARTIES. When the Supplier Site is created Record will be Inserted in HZ_PARTY_SITES. When Address is created it will be stored in HZ_LOCATIONS.
Subscribe to:
Posts (Atom)