Wednesday, October 10, 2012

Inventory Transactions

/*In oracle Applications, the hierarchy of org's is given below.

   Set of Books => Legal Entities => Operating Units => Inventory Organization.

   The Set of Books is defined in the General Ledger application setups, Legal Entity
   is defined in the HR while the Operating Units and Inventory Organization
   (organization) are defined in the Inventory App.

   In Inventory, the items are stored in different subinventories, locations etc.
   We can find what are all the subinventories that are available in the systems.
   Generally we define subinventories corresponding to an inventory organization
   and one inventory organization will have several subinventories associated
   with it.(setup=>subinventories)
*/

SELECT secondary_inventory_name,description, organization_id,location_id ,a.*
FROM   mtl_secondary_inventories a
WHERE  secondary_inventory_name LIKE 'MY%'

--select * from mtl_physical_inventories , mtl_physical_subinventories

/*  We can define the locations that these subinventories will be physically located
using  the setup And associate these locations to a particular sub inventory,it
can be seen from above query. */

   SELECT location_code, location_use, description descr,ship_to_Location_id,
          ship_to_site_flag,inventory_organization_id
   FROM   hr_locations
   WHERE  location_code LIKE 'MY%'

   /* The different types of transactions that are available in Inventory are given
   below.They are like
            Subinventory Transfer,
        Move Order,
     Container Pack etc.
    A transaction type is a combination of transaction
   source type and transaction action. We can have a user-defined transaction
   source types, but there are a predefined set of transaction actions(a user cannot
   define anymore tranasction actions). So based on a pre-defined transaction actions
   and source types, we have some predefined transaction types. However if we need to
   have a new transaction type(apart from already provided ones), we have to create a
   new transaction source type, and create a new transaction type based on the new
   transaction source type and associating it with a any of the existing transaction
   actions.
     
   Ex's of transaction action is "Issue from Stores", "Receipt into Stores" etc.
   Ex of a predefined txn source type is "Sales Order ".
   So based on these two, the predefined transaction type is "Sales Order Issue".
   Another ex is we can have a predefined source type as "Goodwill" and transaction
   action is "Issue from Stores" and Transaction type is "Goodwill Donation".
     
   One more ex is transaction type "Subinventory Transfer" which corresponds to the
   transaction action "Subinventory Transfer".
   */
     
   SELECT transaction_type_name, transaction_source_type_id, status_control_flag
   FROM mtl_transaction_types
   ORDER BY 1
     
   SELECT transaction_source_type_id, transaction_source_type_name, description
   FROM mtl_txn_source_types
   ORDER BY 1

   /* So we can see that items can participate in different kinds of transactions like
   "Issue from stores" in which case they go out to the sales order, "Receipt into Stores"
   coming into the inventory, moving across the subinventory which is called subinventory
   transfer etc. So all these transactions are stored in the table mtl_material_transactions.
   So given a item name, we can always find out in all the transactions that this item
   has participated in, by using the following query. So all these transactions are called
   Item Transactions or Material Transactions. It is interesting to see that the item's
   transaction quantity having a negative balance as well as positive balance.*/
   */
     
   SELECT transaction_quantity, inventory_item_id,subinventory_code,source_code,
   transaction_type_id, transaction_action_id, transaction_source_type_id
   FROM mtl_material_transactions
   WHERE inventory_item_id =(SELECT inventory_item_id FROM mtl_system_items_b
   WHERE organization_id= 1 AND segment1='CISCO TEST ITEM1'
   )
  
   /* Move Orders :- Move Orders are requests for the movement of material within
   a single inventory organization. This will allow planners and facility managers
   to request the movement of material within the warehouse/facility for purposes
   like Replenishment, Material storage relocations and Quality Handling.
   Move Orders are restricted to only with in inventory organizations, and if you
   want to move out of an organization, then we have to use the internal requisition process.

   Oracle provides for 3 types of Move Orders

   Move Order Requisitions :: Manually generated request for Move Order.

   Replenishment Move Order:: ex Preapproved automatic move order which can happen when a minimum quantity is
                reached in inventory.
   Pick Wave Move Order    ::  Preapproved requests for subinventory transfers to bring material from a source
                   location to a staging subinventory.
   The Move Order type refers to the entity that created the Move Order. For all Move orders the final result is
   either Subinventory Transfer or Account Process. Move Orders use Inventory picking rules to suggest
   source location, lot number, revisions for the material to be picked.

   So out of the 3 different move order types, two of them are typically pre-approved move orders while the
   Move order requisition is a manual move order,which we can allocate and transact it manually.
   The different steps that a move order will go thru are given below.
 Move Order Creation.
 Move Order Approval.
 Move Order Allocation => At this stage, the Inventory will use picking rules defined to allocate the
   Material to this particular Move order.At this point the material reservation is made.
   that is we can check the information in the table mtl_reservations.
   In this step, it will fill all the details like the from and to subinventory,but does not
   actually execute those steps.
 Print Pick Slip (optional) => This is an optional step to print the pick slip.
 Move Order Transaction. => Typically each Move order will consist of a header and lines. Each line
  will correspond to an item. We can transact one line at a time or all together.
  Now at this point the actual subinventory transfer will happen. That is we can see the records
  in the material transaction mtl_material_transactions.
   */


   /* Generally quantities of items are stored in subinventories corresponding to
   an organization. An org can get a material qty from a transaction which can either
   be a purchase , movement from another subinventory, manufactured etc. If we have
   any material transaction like subinventory transfer of any particular item also
   we can see that from this query.  */
  
   SELECT b.inventory_item_id item_id, b.organization_id org, b.segment1 item_code, 
   a.transaction_quantity, subinventory_code subinvt,  cost_group_id
   FROM mtl_onhand_quantities_detail a, mtl_system_items_b b
   WHERE a.organization_id = b.organization_id
   AND  a.inventory_item_id = b.inventory_item_id
   AND  b.segment1 = 'CISCO TEST ITEM1'


  -- Givena a item code, query will give the organization name.
   SELECT inventory_item_id, a.organization_id , b.name
   FROM mtl_system_items a, hr_organization_units b
   WHERE segment1='MARUTI 4000'
   AND a.organization_id = b.organization_id --707983616

   -- Given a item , we can find all the orders which have placed an order on these items
   SELECT header_id, line_id
   FROM oe_order_lines_all
   WHERE ordered_item='MARUTI 4000'

   /* An Item onhand quantity is the total quantity of that item. We can see that
   quantity in the mtl_onhand_quantities_detail table. Out of this amount, we can
   place reservations for that item either on the order line itself or we can even
   go to the Inventory and do this. In the latter case, we choose the "Reservations"
   form to place a reservation on this item. Incidentally we need to provide the
   order number and the line number which has this item, which creates a reservation
   on that item.   In that case, the data goes into the mtl_reservations table. Hence
   the available quantity of any item is  onhand minus the reserved quantity. So the
   formula is
        onfiltered= Reserved + Available (to transact, reserve or sell).
  
   The below query will also give you the order line id (demand source line id) of an order which has placed
   the reservation. */

   SELECT demand_source_header_id, demand_source_line_id,reservation_quantity
   ,supply_source_type_id,subinventory_code
   FROM mtl_reservations
   WHERE inventory_item_id =
   (SELECT inventory_item_id FROM mtl_system_items WHERE segment1='TESTSTD1'
   AND organization_id =1)
   AND organization_id = 1
  
   /* To get all those order lines which have place a reservation on a particular
   item, use this query.*/
   SELECT *
   FROM oe_order_lines_all
   WHERE line_id IN
       (SELECT demand_source_line_id
       FROM mtl_reservations
       WHERE inventory_item_id =
    (SELECT inventory_item_id FROM mtl_system_items WHERE segment1='TESTSTD1'
       AND organization_id =1)
       AND organization_id = 1)

 /* Generally any item when it is first created will be assigned a default
  category set, which can be defined as part of setup data.
 */
      SELECT a.inventory_item_id,a.organization_id, b.category_set_name, c.segment1
      FROM mtl_item_categories a, mtl_category_sets b, mtl_categories c
      WHERE a.category_set_id = b.category_set_id
      AND a.category_id = c.category_id
      AND inventory_item_id =
      (SELECT inventory_item_id FROM mtl_system_items_b WHERE segment1='LUCKYITEM'
      AND organization_id =1)
      AND organization_id = 1

      SELECT category_set_id,category_set_name,description,structure_id,validate_flag
      FROM mtl_category_sets
      WHERE description LIKE '%Item%'
      
      SELECT * --category_id,structure_id,segment1, description
      FROM mtl_categories
  
      SELECT a.inventory_item_id,a.organization_id, a.category_set_id,
      a.category_id--,a.cms_replication_date
         ,b.segment1,b.description, c.category_set_name, c.description
      FROM mtl_item_categories a, mtl_categories b, mtl_category_sets c
      WHERE inventory_item_id =
     (SELECT inventory_item_id FROM mtl_system_items
       WHERE segment1='CISCO TEST ITEM1'
      AND organization_id = 1)
      AND a.category_id = b.category_id
      AND a.category_set_id = c.category_set_id

   /* Item attributes and status codes : Item attributes are information
   about an item for ex, lead time, revision control, order cost etc. We can have
   2 kinds of control levels for these item attributes, which we set at the
   time of defining the item. They are master level (which is centralized i.e
   attributes defined at master level will have same values at all the org
   levels. And in org level the values defined will have different values at
   different orgs. ex UOM.

   There are two kinds of item attributes,
      --- Item Defining attributes
   --- Item Status attributes
  
   /* You cannot enable the status attribute, until you set the corresponding
   item defining attribute to Yes */
  
   The Item Defining attributes are
            Inventory Item
      Internal Ordered
      Purchased
      MRP Planning Method
      Costing Enabled
      Engineering Item
      Customer Ordered
      Support Service
      Invoiceable Item
  
   All the above item defining attributes are given by the below query.
  
     SELECT   inventory_item_flag,
        purchasing_item_flag,
     internal_order_flag,
     mrp_calculate_atp_flag,
          costing_enabled_flag,
     eng_item_flag,
     customer_order_flag,
     serviceable_product_flag
     --serviceable_component_flag ,service_item_flag
     FROM   mtl_system_items_b
  
   THe Item status attribute are 8 and they are,
   
            BOM Allowed,
       Build in WIP,
       Customer Orders Enabled,
       Internal Orders Enabled,
          Invoice Enabled,
       Transactable,
       Purchasable,
       Stockable.
  
   All these attributes are flags and the following query will give all those attributes. */

   SELECT inventory_item_id, organization_id,
       bom_enabled_flag, build_in_wip_flag, customer_order_enabled_flag,
    internal_order_enabled_flag,
       invoice_enabled_flag,mtl_transactions_enabled_flag,
    purchasing_enabled_flag,stock_enabled_flag
   FROM  mtl_system_items_b 
   WHERE  inventory_item_id = 101177 

   Here is the mapping of the Item defining vs the Item status attributes
    
     Inventory Item   (stockable, transactable, BOM Allowed)         
         Internal Ordered (Internal Orders Enabled)
  Purchased   (Purchasable)
         MRP Planning Method
         Costing Enabled 
         Engineering Item
         Customer Ordered  (Customer Orders Enabled)
         Support Service
  Invoiceable Item (Invoice Enabled)
  
 /* Before we go further, let us briefly dwell about the functional area
 and the default category sets in Inventory. In Inventory for every functional
 area, there is a default category set associated. We can see this from the
     setup => Items => Categories => Default Category Sets.
 Alternatively we can see that from the database table as well, as given below.
 The primary purpose of using the categories and category sets is reporting.
 We can generate reports based on them.*/
    
  SELECT ds.functional_area_id,
           ml.meaning functional_area_desc, ds.category_set_id,
            cst.category_set_name, cst.description category_set_description
   FROM mtl_default_category_sets ds,
           mfg_lookups ml,
           mtl_category_sets_tl cst
    WHERE  ml.lookup_type = 'MTL_FUNCTIONAL_AREAS'
      AND  ml.lookup_code = ds.functional_area_id
      AND  cst.category_set_id = ds.category_set_id
      AND  cst.LANGUAGE = USERENV ('LANG')

 /* Having defined the item defining attributes and the functional areas.
 Whenever an item defining attribute is set to yes for an item, then the default
 category set of the corresponding functional area is assigned to that item.

 Item Attribute Controls. You can choose to have centralized or decentralized
 control of item attributes. Defining attribute controls does not determine
 the value of an attribute ,only the level at which it is controlled.
 As mentioned before, there are 2 kinds of attribute controls, which are
  a) item attributes controlling at the master/org level and
 b) status attribute value control.
 Both these things we can do from the form
       setup => Items => Attribute Controls.
 */

 SELECT inventory_item_status_code,attribute_name, attribute_value
 FROM  mtl_status_attribute_values
 WHERE  inventory_item_status_code ='Active'

 /*From the below query we can see that if the status setting is 1 i.e
 corresponding to "Sets Value",then it means it is status controlled. and if
 the status setting is "Not Used", then that particular status attributes is
 not status controlled. The same thing shows up when we go to the form
            setup => Items => Status Codes.
  */
  SELECT user_attribute_name, user_attribute_name_gui,
   status_control_code, -- (1 for status setting "Sets Value", 3 for Status setting "Not Used")
  control_level, -- (1 for master,2 for org)
  attribute_name, 
  level_updateable_flag ,attribute_group_id
 FROM  mtl_item_attributes
 WHERE  user_attribute_name IN
   ('BOM Allowed','Build in WIP','Purchasable','Stockable','Invoice Enabled',
   'Customer Orders Enabled','Internal Orders Enabled','Transactable')

 /* Now let us see the form setup => Items => Status Codes, there we can group
 a item status to  these set of 8 status attributes and then we can find that. */

 SELECT ROWID, inventory_item_status_code,attribute_name, attribute_value
 FROM  mtl_status_attribute_values
 WHERE  inventory_item_status_code ='Active'
 
 /*PICKING RULES : Typically the inventory items are stored in different
 subinventories,will have different lot numbers and revisions.Which one needs
 to be picked will be determined by the picking rules.
   We can define picking rules which basically define the order in which
 revisions, lots , subinventories and locators are picked for sales orders.
 You can get the picking rules from the below query. When you define an
  item, you can choose a picking rule that you have defined. When Oracle
 Shipping Execution submits requests to Oracle Inventory, it uses the info in
 picking rule to generate the picklists for sales orders. 

 A picking rule can be specified from the order management tab in the Item definition form*/

 SELECT picking_rule_id, picking_rule_name, description,
  revision_rule ,
  lot_rule,
  serial_number_rule,
  subinventory_rule,locator_rule
 FROM  mtl_picking_rules
 
 /*As mentioned , there can be various orders that you can define, like revision order, lot order, subinventory
 order,locator order. Now in the case of
 revision order, you can choose to have the most recent revision or earliest revision effective date.
 lot order, pick the lowest lot number or earliest lot effective date
 subinventory order , pick by order defined by subinventory, or earliest subinventory receipt date.etc
 Subinventory receipt date means, what is the earlies receipt date in that subinventory.*/

 /*Item templates. Since we know that there are hundreds of attributes which an item can have,(distributed
 across various tabs in form), it can be tedious to enter all these attributes for each item. Hence we can
 define a template (for frequently defined items) and then while defining an item, we can choose this template
 from Tools => copy from , and then all these attribute values will be defaulted immediately.*/
 
  SELECT rule_id, rule_name,description
  FROM   mtl_atp_rules


  /* The stock locators are basically areas within the subinventory where the
   inventory items are stored. Hence each stock locator will correspond to a
   subinventory.You can set up an stock locator and assign it to the subinventory
 from setup => Items => Stock Locators.*/

 SELECT inventory_location_id, organization_id,  inventory_location_type,subinventory_code,segment1,status_id,
 LENGTH,width,height -- and other dimensional attributes.
 FROM mtl_item_locations
 WHERE subinventory_code LIKE 'MY%'

 /* To know whether a particular inventory item is lot controlled or serial controlled we can run the
 below query. Can a item be both lot and serial controlled ????? */

    SELECT lot_control_code,-- 1 for no control and 2 for full control.
    auto_lot_alpha_prefix,
    default_lot_status_id,
    fixed_lot_multiplier,
    start_auto_lot_number,
    std_lot_size
    --
    serial_number_control_code,
    serial_status_enabled,
    start_auto_serial_number
    FROM mtl_system_items_b
 WHERE segment1 ='LUCKYITEM'
 
/*
Available to Promise (ATP)
 
ATP computation options :
ATP computations use the consumption and accumulate available features.
Consumption resolves negative ATP after it is already determined that there is not enough
available inventory.
Accumulation uses the excessive inventory from a period to determine where the new demand can be
placed.
Accumulate Available :
This option determines how the
To implement ATP, you have to define ATP rules. ATP rules let you tailor the ATP calculation to suit your business needs.

Each ATP rule is a combination of
            ATP computation options
            Time fence options
            Supply and demand sources
*/


   SELECT  supply_type_code, supply_source_id,po_header_id, po_line_id, po_distribution_id,item_id,quantity,
      destination_type_code,to_organization_id
   FROM  mtl_supply
   --where item_id = 207984204


   (SELECT DISTINCT item_id FROM mtl_supply)
  
   SELECT inventory_item_id FROM mtl_system_items_b
   WHERE segment1='LUCKYITEM'
  
   SELECT COUNT(*) FROM mtl_demand


/* Just like GL,AR ,Inventory has its own periods as well & we can open period from
setup => costs => cost periods .And the data goes into the org_acct_periods.

Oracle Inventory --> Accounting Close Cycle --> Inventory Accounting Periods --> Change status

In inventory only one period at any time, while in GL or PO any periods can be open.

This is the most trickiest part. Actually in inventory if you need to open
any period, then the previous period needs to be already opened. That is
if the previous period is not open yet, just open and close it.

Also ensure that the purchasing periods are open, however in this case
more than one periods can be open simultaneously.
 
 Setup => Financials => Accounting =>  Control Purchasing Periods.  
 
If you do not have any item on hand, then you can do a subinventory transfer
of that item and then have some quantity available in the subinventory.
*/

/*
In the general planning tab for an item in inventory, you enter the type of
planner that you use, i.e Min-Max planning method, Reorder Point planning
method or no plan at all.

In the case of Min-Max planning method, you enter the Min and Max amounts
for that item. If a min is entered, you want atleast that amount of that
item on-hand always and if that amount is reached, Oracle inventory will
suggest for placing a new order.
If a max is entered, then you don't want more than the max amount of that item.

Reorder Point Planning : An inventory planning method that is used to determine
when and how much to order based on customer service level, safety stock,
carrying cost,  order setup cost, Lead time and average demand.

Minimum Order Quantity : this is the amount of the item that the system
places an order on, whenever it needs to reorder. Ex for discrete items,
when the net requirements fall short of the minimum order quantity, the planning algorithms suggest the minimum order quantity.

Maximum Order Quantity :
 Or optionally you can have an item to be MRP/MPS planned.

If an item is vendor managed, then you should enter the planner for that item.*/

-- hr_organization_units is a view based on hr_all_organization_units and
--hr_all_organization_units_tl.
    SELECT *
 FROM hr_organization_units
    WHERE organization_id = 1

   SELECT * FROM hr_all_organization_units_tl
  
   SELECT * FROM hr_all_organization_units
   WHERE organization_id =1

  SELECT * FROM mtl_parameters_view

--Move Order Issue and Mover Order Transfer : 

/*Miscellaneous Receipt :
 Transactions => Miscellaneous Transaction (type = Miscellaneous Receipt)
 Usually if we quickly want some amount of quantity for a particular item,
 then we can either do the PO,and receive against that PO. Or we can do a
 miscellaneous receipt for that particular item. Obviously when we do a miscellaneous
 receipt, we receive that particular item into a particular inventory
 organization and into a particular subinventory.
*/

-- Once we log into the Oracle Apps, the Operating unit is chosen by the

 profile value 'MO:Operating Unit'.


 SELECT * FROM mtl_customer_item_xrefs  --_v

 /* What are customer items?. Is it same as inventory items with customer orders enabled - NO */
  SELECT customer_item_id, customer_id, address_id
    ,item_definition_level
  FROM mtl_customer_items


  -- Item Categories Stuff :
  /**************************
  Usually we will have a lot of structures defined for this particular KFF "Item Categories".
 
  Here the data goes into a key columns of the
  underlying table (not descriptive columns like attribute etc). The category id
  is the primary key which is based on the unique values of the segment values.
  */

  SELECT category_id, structure_id ,attribute_category, attribute2,
     segment1, segment2, segment3
  FROM   mtl_categories_b

 /* When we define the category set,we use this particular KFF structure. That
  is we can define any number of category sets corresponding to different structures.
  Let us go thru the steps required to define a Item category KFF ,structure,
  codes and the associated stuff.

  1. First step, we have a KFF "Item Categories" and we can define a structure
  corresonding to this KFF,using the menu,,
    setup => KFF=> Segments

  2. The next step is to create the Item Categories or the combinations corresponding
  to the Item Category Segment that is being created above. This process is creating
  Category Codes. This is done using the menu,
       Setup => Items => Categories => Category Codes
   And then choose "New". Now we have to start creating the Category Codes,by
   choosing the structure in the Structure Name field. This is the same 
   structure that we have created in step 1 . Now once we come to Category
   field, we enter the values corresponding to all the segments for this particular
   structure. Basically here we are enterting the combination of values. If each
   segment has a value set attached to it in the segment definition, then we
   have to choose from the valueset, otherwise we can enter any values we want to.
   So like this, we define as many Item categories i.e combination of values as
   we want. So each such combination is a item category. A set of such
   combinations is called a category set.
  
  3. The next step is we come to the Category Sets form,
      Setup => Items => Categories => Category Sets
 In this step we create a Category Set. that is, we form a set of combinations
 that we have created in the above step.
 Hence in this from,we enter a Category Set name and description and in the
 Flex structure field, we enter the same structure that is created in the
 step 1. And in the below block, we enter all/some of the categories that are
 created in the above step 2. We also specify what is the default category
 for this item.
 
 Hence a very important point to note here is that,out of all the structures we
    choose the structure by category set. There is nothing like profile value which
   choose the particular structure or the particular category set. We can use
   multiple category sets at the same time for multiple inventory items and
   hence there is no option to set a specific category set.

    This concludes the Item Category Setup. Having created different categories
 and category sets, we can now assign Items to Item Categories.

  Essentially we assign items to category sets just for reporting and grouping purposes.
   Now we come to the item master,choose a particular item and from the menue
              Tools => Categories
 
  We can assign this item to a particular category set. What we need to understand is that
  when we assign item to a category set, we choose a specific value out of all the values
  in that particular category set.
  This tells us that this item belongs to that particular category.  */
 
 SELECT * FROM mtl_categories
    where creation_date >= trunc(sysdate)

   SELECT * FROM mtl_category_sets_b

What are the different types of Miscellaneous transactions.



interorg transfer.

what are inventory picking rules (Verisign does not use the inventory picking rules)




Usually since the valid GL combination codes are already created in GL, using the Account generator , it
is handy to go to GL and keep such combination,which can be used for entering the acct info in transactions.
011.2120.0000.140060.0000.000.000000 - Asset
011.9000.0000.250010.0000.020.000000 - Liability
011.9000.0000.360010.0000.000.000000 - Owners Equity

I think the account receipt/issue is basically a Miscellaneous receipt/issue, with the exception that
we specify a gl account at the time of receipt/issue.

It is important to note that for everything that we do in the Purchasing or OM, has an impact in Inventory in the
form of transactions. That is if we do a sales order pick release, then immediately a inventory transaction
happens with the corresonding trasacation type (Sales Order Pick). And also the onhand quantities etc will get 
reflected accordingly.

Now we want to see if we do an internal req, does it do a corresponding internal req trx type in Inventory and for
that, create an internal req, and see whether it shows in the material transactions
 as po req relation transaction type. ???????????????
 req # 119035042

Make sure that the item that you are ordering is Internal Order enabled.
For any internal orders, the price is automatically defaulted to 0
Also make sure the bill to location is specified for the internal customer

WHY the delivery did not get created and i had to create a auto create delivery.  ????????????

*/

--We can try to see what kind of transactions did what amounts from,  
    SELECT b.transaction_type_name,COUNT(*)
    FROM mtl_material_transactions a , mtl_transaction_types b
    WHERE a.transaction_type_id = b.transaction_type_id --and transaction_type_id = 64
    GROUP BY b.transaction_type_name
   
    SELECT * FROM mtl_material_transactions WHERE transaction_type_id = 64


/* For each type of inventory transaction we will have the accounting information.
however for some of items like expense item, we may not have the accounting records
in the mtl_transaction_accounts.

Why is that we are not seeing the mtl_transaction accounts records for all the
transactions even though it is an inventory item, not expense item. ????????

Basically, the inventory classifies an item as an asset type item, based on the
subinventory. If we place a sales order on an asset item which is stocked in
an asset subinventory.  
Usually pick release process can use a pick release rule which tells it to go
to which subinventory andget it from. However even with out the rule, we can
choose at the time of pick release ,which subinventoryshould be chosen.
Interestingly, when we place an order, we can tell which inventory org in the
form of the warehouse. we can also see the subinventory field(but not able to
select value in it). If the pick release process picks it from an asset subinventory,
then it goes ahead and creates the transaction accounts in the table,
*/
 
    SELECT * FROM mtl_transaction_accounts.

/*And if the item is picked from the expense subinventory , then it does not find
an entry in the transaction accounts.

In the case where an item is received both in expense and asset subinventory and
there is no picking rule, when I try to pick release such kind of item, I
encountered an error, this is probably because the system is confused to pick that
item from which subinventory. Oracle is not designed to handle this kind of cases.

Even though you define an asset item, you have to receive that item (using PO, Misc trx) into an asset
subinventory, otherwise the transaction accounts will not be generated. We can try to do a subinventory
transfer of an asset item from asset subinventory to an expense subinventory. What this effectively
means that we are moving a specific amount of quantity of that asset item to the expense subinventory
as it is of no use/damaged. So this effectively means that we do not have any items in our asset subinventory.


what happens when we post it to GL. ???????
*/ 


1). What are shipping Networks ?
Shipping networks determine between which two organizations you can do an inventory
transfer. That is ,say if you want to do an inventory transfer between two inventory
organizations M1 and M2, then you must define a shipping network between these two
inventory organizations and specify whether you want this transfer to be
  -- Direct Or
  -- Intransit

2). Explain the Inter-org transfer?

Inter-org transfer is the transfer of items between two different inventory organizations.
Remember this is not a transfer between two subinventories of the same inventory
organization, but it is between two inventory organizations. You can create an
inter-org transfer using the menu
    Transactions => Inter-org Transfer

The from Org is the current inventory Organization.
The LOV for the To org is determined by the shipping networks that you define between
 the from Org to different organization. The shipping networks are defined here
   Setup  => Organization  => Shipping Networks.

You will be able to do an inter-org transfer to a particular destination organization
only if the internal order enabled flag is not checked in the shipping networks.
 
   
3). what is the difference between transactable and oe transactable??
transactable means u can perform transactions on that item. i.e you can enter orders and book them.
oe transactable means that trasactions can be interfaced to oracle inventory, that means, some of the transactions like pick release (which involve inventory transactions) cannot be performed, if you don't set this flag. mostly if the shippable flag is set, oe transactable is set, but if shippable is not set, you could still set this flag, for forecasting purposes.

4). How do you create a Kit in Inventory ?

Create an item in Inventory,set it as inventory item, standard BOM and set pick components to Yes. This constitutes a kit, say xyz.
So a kit is a standard BOM with only mandatory components
 So basically all you do in a kit is to select items in the kit like (one cd, one hardware security token, one user manual). So the kit does not have a work in process (wip) involved,only picking. Another important point is that a kit by itself will not onhand in inventory. Which means you will have on hand for cd, token and manual but no onhand for the kit xyz.
OR
More easily ,just create an item and set the item type template as KIT in the main tab,and in the template all the appropriate flags will have been set.
One thing, interested I noticed is that when you create a kit item, the shippable flag is not set,although the kit itself is shipped right? May be what that means is that there are no delivery lines created for the kit itself,as opposed to the component items for which the delivery lines are created.
We're only shipping the components and the Kit item is a logical unit. however in the case of ATO, we make ATO shippable since we ship the entire assembly and not individual components there.
PTO configuration has the extra step of clicking on the configurator button to choose the items.

5). What is the difference between Asset type and Expense Types ??

Asset Type and Expense Type are opposite:  (Expense account is also called as Charge Account)
Earlier we have mentioned that, move orders are the orders which are used for moving the items between the
subinventories. However if we need to move the items across inventory organizations ,then we can use the
Internal Requisition process. In the Internal Requisition process, we have a clear way of mentioning the destination
and the source inventory organizations. At the lowest level, each item is stored at the subinventory level.Each
subinventory is either of asset type or expense type.
     Hence there is no concept of inter-org transfer if we have only one inventory organization.
   
An item is created as an ASSET item by setting the flag in the tab
     Costing => Inventory Asset Value  (and also costing enabling)
 and you also mention the COGS account. An Asset item's cost is realized at the time of selling that item. All
 stockable items are asset items.(Stockable item means no expense, it is an asset)

An item is created as an EXPENSE by NOT setting the flag in the tab
     Costing => Inventory Asset Value
 However in this case, we have to mention what is the Expense account. An expense item cost is realized
 at the time of purchasing that item. Expense item means small items like stationery, pencils etc.
   
6). What is the difference between subinventory transfer, move order
 (transaction mover order) ??
   Both the Move order as well as the subinventory transfer do the same result,
   i.e they move the item from one subinventory to another. However with the
   Move order, we have more functionality available.
 Usually the system creates the Move order. Generally if we want to do a
 subinventory transfer we would go ahead and do the transfer,however some of
 the other modules like Manufacturing can potentially do the transfers. In that
 case, they would create a Move order. However someone should open that Move order
 inspect it and approve that Move order,so that either subinventory transfer or
 account issue will happen. So the move order is a two step process,where in we
 first create,approve,allocate the move order and then transact it.
 Look at OM stuff for more information.

7). How to setup a Master Organization ??
 First we create an organization using the hr responsibility. once an
organization is created,then we can use the menu
  setup => organizations => parameters
 to set up that organization as a master organization.
 In here, we can also set up the child organizations
 */
 So to setup each child organization ,go to the change organization menu option,choose
 your inventory organization and after that,come to organization parameters and choose
 your master organization.


8). What is the difference between Item Cross Reference and Customer Item Cross Reference?
  Customer Item Cross Reference is a particular instance of Cross references.
  Item Cross Reference is a generic term which can be used to define reference
  with Customer's Items and Supplier's Items
 
For Item cross reference is used to track the old items or the supplier
items. That is ,if the same item is referred by a different name by the
supplier then, the item cross reference will map these two names.
 
  For Customer item cross reference,is probably used ,say for the sales guys to place the order on items, by
  which they remember. Thatisif a particular item 'Myitem" is referred to as 'abcd1' using the
  customer item cross reference, then we can go to the sales order from and in the line item
  we can refer to this 'abcd1' to place an order on that item.
  But usually the customer item cross reference, is when you are loading the data from EDI 40 etc. That is
  a customer sends an xml kind of file for placing a sales order and that file consists of all the customer
  item references and not oracle item references.
  */

9). What is the difference between Operating Unit and Inventory Organization ?
In some of the important modules like OM, Purchasing etc tables we find that
there is and org_id. this is actually an operating unit id (and not to be
confused with the inventory org id). And when we log into the applications,
the operating unit is already set from the profile parameter 'MO:Operating Unit'.
However in the inventory module, the org id that we are always referring to
is the inventory org id (and not the operating unit id).

When we enter the Inventory module, we are always prompted to choose a
particular inventory org id.

Usually when we create an item, we always assign it to an org id, i.e what
this means is that we can transact this item with in that particular
inventory org id. That is particularly we can purchase, sell with in that
inventory org id.

When we create an org it is always associated with a location and hence it
goes into the hr_organizations and hr_locations. It is important to know
that both the employee locations and the inventory locations go into
the same table hr_locations.*/
   
10).  What are Organization attributes /
Organization Attributes : Usually we create an item in the Master org, set
all its attributes and then assign that item to a different inventory
organization. However it is important to note that some attributes may not
be set into the new organization,so from the organization manager.

We can click on the org attributes and set the attributes of the item accordingly.

11). What are item attributes?
   There are two kinds of item attributes,
      --- Item Defining attributes
   --- Item Status attributes
  The following is the mapping between item defining and status attributes
     Inventory Item   (stockable, transactable, BOM Allowed)         
         Internal Ordered (Internal Orders Enabled)
  Purchased   (Purchasable)
         MRP Planning Method
         Costing Enabled 
         Engineering Item
         Customer Ordered  (Customer Orders Enabled)
         Support Service
  Invoiceable Item (Invoice Enabled)

 Build in WIP and Recipe enabled are status attributes for which there is no
 corresponding item defnining attirubtre.

Source: http://prasanthapps.blogspot.in/2011/04/inventory-transactionsuseful.html

Sunday, August 26, 2012

CYCLIC CASCADING in a TRIGGER


This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situtation does not exist.
The below example shows how Trigger's can enter into cyclic cascading.

Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created.
1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.


In such a situation, when there is a row inserted in table 'abc', triggerA fires and will update table 'xyz'.
When the table 'xyz' is updated, triggerB fires and will insert a row in table 'abc'.
This cyclic situation continues and will enter into a infinite loop, which will crash the database.

Exception Handling


In this section we will discuss about the following, 

1) What is Exception Handling.
2) Structure of Exception Handling.
3) Types of Exception Handling.

1) What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly. When an exception occurs a messages which explains its cause is recieved.
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message 

By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.

2) Structure of Exception Handling.

The General Syntax for coding the exception section
 DECLARE
   Declaration section 
 BEGIN 
   Exception section 
 EXCEPTION 
 WHEN ex_name1 THEN 
    -Error handling statements 
 WHEN ex_name2 THEN 
    -Error handling statements 
 WHEN Others THEN 
   -Error handling statements 
END; 
General PL/SQL statments can be used in the Exception Block.
When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is 'ex_name1 ', then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.
If there are nested PL/SQL blocks like this.
 DELCARE
   Declaration section 
 BEGIN
    DECLARE
      Declaration section 
    BEGIN 
      Execution section 
    EXCEPTION 
      Exception section 
    END; 
 EXCEPTION
   Exception section 
 END; 
In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.

3) Types of Exception.

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.
For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.
Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.
Exception NameReasonError Number
CURSOR_ALREADY_OPENWhen you open a cursor that is already open.ORA-06511
INVALID_CURSORWhen you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.ORA-01001
NO_DATA_FOUNDWhen a SELECT...INTO clause does not return any row from a table.ORA-01403
TOO_MANY_ROWSWhen you SELECT or fetch more than one row into a record or variable.ORA-01422
ZERO_DIVIDEWhen you attempt to divide a number by zero.ORA-01476
For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a code to handle the exception as given below.
BEGIN 
  Execution section
EXCEPTION 
WHEN NO_DATA_FOUND THEN 
 dbms_output.put_line ('A SELECT...INTO did not return any row.'); 
 END; 

b) Unnamed System Exceptions

Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.
There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception.
We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.
EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.
Steps to be followed to use unnamed system exceptions are
• They are raised implicitly.
• If they are not handled in WHEN Others they must be handled explicity.
• To handle the exception explicity, they must be declared using Pragma EXCEPTION_INIT as given above and handled referecing the user-defined exception name in the exception section.
The general syntax to declare unnamed system exception using EXCEPTION_INIT is:
DECLARE 
   exception_name EXCEPTION; 
   PRAGMA 
   EXCEPTION_INIT (exception_name, Err_code); 
BEGIN 
Execution section
EXCEPTION
  WHEN exception_name THEN
     handle the exception
END;

For Example: Lets consider the product table and order_items table from sql joins.
Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given below.
 DECLARE 
  Child_rec_exception EXCEPTION; 
  PRAGMA 
   EXCEPTION_INIT (Child_rec_exception, -2292); 
BEGIN 
  Delete FROM product where product_id= 104; 
EXCEPTION 
   WHEN Child_rec_exception 
   THEN Dbms_output.put_line('Child records are present for this product_id.'); 
END; 
/ 

c) User-defined Exceptions

Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
• They should be explicitly declared in the declaration section.
• They should be explicitly raised in the Execution Section.
• They should be handled by referencing the user-defined exception name in the exception section.
For Example: Lets consider the product table and order_items table from sql joins to explain user-defined exception.
Lets create a business rule that if the total no of units of any particular product sold is more than 20, then it is a huge quantity and a special discount should be provided.
DECLARE 
  huge_quantity EXCEPTION; 
  CURSOR product_quantity is 
  SELECT p.product_name as name, sum(o.total_units) as units
  FROM order_tems o, product p
  WHERE o.product_id = p.product_id; 
  quantity order_tems.total_units%type; 
  up_limit CONSTANT order_tems.total_units%type := 20; 
  message VARCHAR2(50); 
BEGIN 
  FOR product_rec in product_quantity LOOP 
    quantity := product_rec.units;
     IF quantity > up_limit THEN 
      message := 'The number of units of product ' || product_rec.name ||  
                 ' is more than 20. Special discounts should be provided. 
   Rest of the records are skipped. '
     RAISE huge_quantity; 
     ELSIF quantity < up_limit THEN 
      v_message:= 'The number of unit is below the discount limit.'; 
     END IF; 
     dbms_output.put_line (message); 
  END LOOP; 
 EXCEPTION 
   WHEN huge_quantity THEN 
     dbms_output.put_line (message); 
 END; 
/ 

RAISE_APPLICATION_ERROR ( )

RAISE_APPLICATION_ERROR is a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999.
Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically (i.e. change due to INSERT, UPDATE, or DELETE statements).
RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.
The General Syntax to use this procedure is:
RAISE_APPLICATION_ERROR (error_number, error_message); 

• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
Steps to be folowed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.
Using the above example we can display a error message using RAISE_APPLICATION_ERROR.
DECLARE
  huge_quantity EXCEPTION; 
  CURSOR product_quantity is 
  SELECT p.product_name as name, sum(o.total_units) as units
  FROM order_tems o, product p
  WHERE o.product_id = p.product_id; 
  quantity order_tems.total_units%type; 
  up_limit CONSTANT order_tems.total_units%type := 20; 
  message VARCHAR2(50); 
BEGIN 
  FOR product_rec in product_quantity LOOP 
    quantity := product_rec.units;
     IF quantity > up_limit THEN 
        RAISE huge_quantity; 
     ELSIF quantity < up_limit THEN 
      v_message:= 'The number of unit is below the discount limit.'; 
     END IF; 
     Dbms_output.put_line (message); 
  END LOOP; 
 EXCEPTION 
   WHEN huge_quantity THEN 
 raise_application_error(-2100, 'The number of unit is above the discount limit.');
 END; 

SQL Query to Convert Rows into Columns

We can accomplish this by a "pivot" query. Please look at the small testcase.

SQL> desc t1
 Name Null? Type
 --------------- -------- ----------------
 NAME VARCHAR2(10)
 YEAR NUMBER(4)
 VALUE NUMBER(4)

SQL>
SQL> select * from t1;

NAME YEAR VALUE
---------- ---------- ----------
john 1991 1000
john 1992 2000
john 1993 3000
jack 1991 1500
jack 1992 1200
jack 1993 1340
mary 1991 1250
mary 1992 2323
mary 1993 8700

9 rows selected.

SQL> -- now, try out the pivot query
SQL> select year,
  2 max( decode( name, 'john', value, null ) ) "JOHN",
  3 max( decode( name, 'jack', value, null ) ) "JACK",
  4 max( decode( name, 'mary', value, null ) ) "MARY"
  5 from
  6 (
  7 select name, year, value
  8 from t1
  9 )
 10 group by year ;

      YEAR JOHN JACK MARY
---------- ---------- ---------- ----------
      1991 1000 1500 1250
      1992 2000 1200 2323
      1993 3000 1340 8700

Source:http://p2p.wrox.com/oracle/11931-sql-query-convert-columns-into-rows.html
Source:http://www.club-oracle.com/forums/pivoting-row-to-column-conversion-techniques-sql-t144/

Friday, August 3, 2012

Interfaces


What is a interface?
In terms of oracle applications interface is a communication channel that allows the data to move in and out of the system.

How many types of interfaces are there?
1.INBOUND INTERFACES
2.OUTBOUND INTEFACES

Inbound InterFace:The one which allows data to get into oracle application from outside is called inbound interface.

OutBound Interface:The one which allows data to get data from oracle applications to other systems is called outbound interface.

What are different types of inbound interfaces available?
1.Open interfaces
2.API's(Application Program Interface)
3.EDI(Electronic Data interchange)--Mainly used for automation transactions with third party systems
4.XML GATEWAY --Mainly used for automation transactions with third party systems
5.WEBADI--Used for uploading data from excel with the scope to have some validations--mainly used for one time loading..Just like sqlloader+validation program..
5.PLSQL Packages for Reading XML Data--Use this in case of importing non stadard transactions

What are different types of outbound interfaces available?
1.Custom programs where we pull data to a csv file using UTL_FILE in the required format
2.EDI
3.XMLGATEWAY
4.PLSQL Packages for generating XML

What is the difference between OPEN INTERFACE & API's?
OPEN INTERFACE:
I dont see much difference between the open other than the way we load data.
In case of open interface the data is loaded in to open interfce table like GL_INTERFACE or Sales Order Interface(OE_ORDER_HEADERS_IFACE_ALL).
Run the interface import program.
This will validate the data and put it into oracle applications.All the invalid records are marked as Error.
One thing is there are GUI Screens available for most of these interface where you check the errror message correct it there only and resubmit the interface.
From the technical perspective there are Error tables available for each interface

API:
API's are the oracle built packages where the validation logic is wrapped inside a package and the data is passed as parameters to the API.
Most of these api's use the PLSQL tables as paremeters to take the advantage of bulk binding concepts for faster loading data.
THey will have the two OUT parameterst to throw back the error code and message in case of data validation failure
Apis' are compartively faster than open interfaces.
If a API's and open interface are available it is better to load through API's.If the records are more...

Source: http://oracleappstechnicalworld.blogspot.in/search/label/INTERFACES

XMLP Round Function




Testing Round Function..
Actual Value: 23.23
Rounded Value: 23

Actual Value: 23.53
Rounded Value: 24

Sample Files:
RTF: TestRounding.rtf
XML: Rounding.xml

PRAGMA EXCEPTION_INIT


The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.

Case 1: Without Pragma Exception_Int
DECLARE
  MissingNullExp EXCEPTION;
  --PRAGMA EXCEPTION_INIT(MissingNullExp, -1400);
BEGIN
  INSERT INTO empvj (id) VALUES (NULL);
EXCEPTION
  WHEN MissingNullExp then
    DBMS_OUTPUT.put_line('MissingNullExp:'||SQLERRM);
  WHEN OTHERS then
    dbms_output.put_line('When Others:'||SQLERRM);
END;
Output: When Others:ORA-01400: cannot insert NULL into ("APPS"."EMPVJ"."ID")

Case 2: With Pragma Exception_Int
DECLARE
  MissingNullExp EXCEPTION;
  PRAGMA EXCEPTION_INIT(MissingNullExp, -1400);
BEGIN
  INSERT INTO empvj (id) VALUES (NULL);
EXCEPTION
  WHEN MissingNullExp then
    DBMS_OUTPUT.put_line('MissingNullExp:'||SQLERRM);
  WHEN OTHERS then
    dbms_output.put_line('When Others:'||SQLERRM);
END;
Output:MissingNullExp:ORA-01400: cannot insert NULL into ("APPS"."EMPVJ"."ID")

Case 3: With Pragma Exception_Int if both standard and custom exception present in Exception handling block..
DECLARE
  NoDataFoundExp EXCEPTION;
  PRAGMA EXCEPTION_INIT(NoDataFoundExp, 100);
  v_x varchar2(100);
BEGIN
  select first_name into v_x from empvj where id = 999999; --no emp with id 9999999
EXCEPTION
  WHEN NoDataFoundExp then
    dbms_output.put_line('When Custom NoDataFoundExp:'||SQLERRM);
  WHEN NO_DATA_FOUND then
    dbms_output.put_line('When Standard NO Data Found:'||SQLERRM);
  WHEN OTHERS then
    dbms_output.put_line('When Others:'||SQLERRM);
END;
Compilation Error:
ORA-06550: line 8, column 3:
PLS-00484: redundant exceptions 'NO_DATA_FOUND' and 'NODATAFOUNDEXP' must appear in same exception handler
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

Tuesday, July 24, 2012

Query Modes in OAF Query Region


Construction Modes:
There are three construction modes available. Here is a brief comparison of the three modes.

1] resultsBasedSearch:
  • OA Framework automatically renders both the Simple and Advanced search regions based on the designated queryable items in the associated table.
  • The search regions automatically include both a Go and a Clear button.
  • OA Framework automatically executes the underlying search when the user selects the Go button.

2] autoCustomizationCriteria:
  • OA Framework automatically renders both the Simple and Advanced search regions based on the corresponding Simple search and Advanced search regions that you define and specify as named children of the query region.
  •  The search regions automatically include a Go button. In addition, the Advanced search region includes a Clear button.
  • OA Framework automatically executes the underlying search when the user selects the Go button. However, developers must explicitly define mappings between items in the Search panel and items in the table region.

3] none
  • The Search regions are rendered based on the Simple Search and Advanced Search regions that you define and specify as named children of the query region.
  • You must implement your own Go button in this mode.
  • The underlying search must be executed by the developer.

Thursday, July 5, 2012

Formula column, Summary column and Place Holder column



In Oracle reports we use Formula column, Summary column and Place Holder column in different scenarios, this post is to avoid confusion and give clear picture as when and where we need to use these columns.


Formula column

We use formula column to calculate some information dynamically using information based on the columns of the data model or from the concurrent program parameters. It is basically used to apply some custom logic on input data and return some value.
Formula columns can be written using PL/SQL syntax and we can use pl/sql functions for computation on the data. Formula column will accept data of Character, Number, or Date type.


Inside or outside data group?
If we want to calculate a value for every row place the formula column in the group of the data model , so that it called every time for every record and if we want to derive a value at report level place the formula column outside to the data group.
Formula columns are generally preceded by CF_ to distinguish from other columns. Column names or parameters with a prefix ‘:’  in formula column are considers as input to a formula column.


Examples:
  1. Comparison of data in two different columns/variables and perform some action.
  2. Using some standard oracle pl/sql functions to find out some values like, finding out the master org using oracle standard function as shown below
    oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id())
  3. When you need to use any If-else block
  4. To execute some sql query to find out email-address of a party using party_id
Note: Formula column should return some or the other value.


Summary column

Summary columns are used for calculating summary information like sum, average etc,. on specific columns of a data group.  This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.
The datatype of a summary column depends on the data type of the source of the summary. If you change the data type of the source column, the datatype of the summary also changes.
In report layout summary columns should be used out of repeating frames. Summary columns will have only one parameter as input.
Below are the standard functions provided by Oracle  for a summary column


Function
Purpose
AverageCalculates the average of the column’s values within the reset group.
CountCounts the number of records within the reset group.
FirstPrints the column’s first value fetched for the reset group.
LastPrints the column’s last value fetched for the reset group.
MaximumCalculates the column’s highest value within the reset group.
MinimumCalculates the column’s lowest value within the reset group.
% of TotalCalculates the column’s percent of the total within the reset group.
Std. DeviationCalculates the column’s positive square root of the variance for the reset group.
SumCalculates the total of the column’s values within the reset group.
VarianceSums the squares of each column value’s distance from the mean value of the reset group and divides the total by the number of values minus 1.
All the above functions work only for number datatype column and return number datatype.



Place Holder column

Place holder column is an empty container at design time which is used to hold some value in run time, they are like local/global variables which can be used in the logic of a formula column or in report triggers. Value in the place holder column can be directly accessible in the report layout.


Inside or outside data group?
We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.

Page Numbering in XML Publisher/MS Word


Open the footer and press [Alt][F9] to display field codes. You should see this: If you dont get these fields, select header and press [Ctrl][F9]. Word displays empty braces {}. Enter field names PAGE or NUMPAGES inside the curly braces as shown below.

Page { PAGE } of { NUMPAGES }

Highlight { NUMPAGES} and press [Ctrl][F9] to create a new field that nests the Numpages field, like this:

Page { PAGE } of { { NUMPAGES} }

The insertion point will be where you need it, between the two opening braces, so just type =.
Click between the two closing braces and type -1. Here's what you should have:

Page { PAGE } of { = { NUMPAGES} -1 }

Highlight the contents of the footer and press [F9] to update the fields.
Press [Alt][F9] to switch to results mode.

FNDLOAD Scripts

Here are the FNDLOAD Scripts to Migrate 15 different AOL Entities

    Profile Options
    Forms
    Functions
    Menus
    Responsibilities
    Request Groups
    Request Sets
    Lookups
    Valuesets
    Descriptive Flexfields
    Key Flexfields
    Concurrent Programs
    Form Personalization
    Fnd Users
    Alerts

1.Profile Options:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
PROFILE PROFILE_NAME=”XXPRNAME” APPLICATION_SHORT_NAME=”PN”
Target:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt
FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscprof.lct XXPRNAME.ldt PROFILE PROFILE_NAME=” XXPRNAME” APPLICATION_SHORT_NAME=”PN”

2.Forms:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFRMNAME.ldt FORM APPLICATION_SHORT_NAME=”PN” FORM_NAME=”XXFRMNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXFRMNAME.ldt


3.Functions:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXFUNNAME.ldt FUNCTION FUNC_APP_SHORT_NAME=”PN” FUNCTION_NAME=”XXFUNNAME”
Target:
FNDLOAD apps/apps O Y UPLOAD @FND:patch/115/import/afsload.lct XXFUNNAME.ldt


4.Menus:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXMNNAME.ldt MENU MENU_NAME=”XXMNNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afsload.lct XXMNNAME.ldt


5.Responsibilities:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME”
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afscursp.lct XXRESNAME.ldt FND_RESPONSIBILITY RESP_KEY=”XXRESNAME” APPLICATION_SHORT_NAME=”PN”


6.Request Groups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”PN”
Target:
1. FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt
2. FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpreqg.lct XXRQGNAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”XXRQGNAME” APPLICATION_SHORT_NAME=”PN”


7.Request Sets:
Source:
1. FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt REQ_SET REQUEST_SET_NAME=”XXRQSNAME”
2. FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt REQ_SET_LINKS REQUEST_SET_NAME=”XXRQSNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XXRQSLNAME.ldt


8.Lookups:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”PN” LOOKUP_TYPE=”XXLKPNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD aflvmlu.lct XXLKPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/aflvmlu.lct XXLKPNAME.ldt FND_LOOKUP_TYPE LOOKUP_TYPE=”XXLKPNAME” APPLICATION_SHORT_NAME=”PN”


9.Value sets:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD afffload.lct XXVALSNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afffload.lct XXVALSNAME.ldt VALUE_SET FLEX_VALUE_SET_NAME=”XXVALSNAME” APPLICATION_SHORT_NAME=”PN”


10.Descriptive Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXDFFNAME.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME=”PN”
DESCRIPTIVE_FLEXFIELD_NAME=”PN_LEASE_DETAILS” P_CONTEXT_CODE=”Global Data Elements”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXDFFNAME.ldt


11.Key Flex-fields:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXKFFNAME.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’
APPLICATION_SHORT_NAME=”FND” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”
Target:
FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afffload.lct XXKFFNAME.ldt


12.Concurrent Programs:
Source:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM APPLICATION_SHORT_NAME=”PN” CONCURRENT_PROGRAM_NAME=”XXCPNAME”
Target:
1.FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afcpprog.lct XXCPNAME.ldt
2.FNDLOAD apps/apps 0 Y UPLOAD_PARTIAL $FND_TOP/patch/115/import/afcpprog.lct XXCPNAME.ldt PROGRAM CONCURRENT_PROGRAM_NAME=”XXCPNAME” APPLICATION_SHORT_NAME=”PN”


13.Form Personalization:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt FND_FORM_CUSTOM_RULES function_name=”XXFPNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXFPNAME.ldt


14.FND Users:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XXUSERNAME.ldt FND_USER USER_NAME=’XXUSERNAME’
Target:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./ XXUSERNAME.ldt


15.Alerts:
Source:
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name=”XXALERTNAME”
Target:
FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XXALERTNAME.ldt ALR_ALERTS APPLICATION_SHORT_NAME=PER Alert_name=”XXALERTNAME”

Source: http://oracleappsdna.com/2011/08/fndload-scripts-to-migrate-aol-entities/

Monday, June 25, 2012

Internal Requisition/Internal Orders process

The Internal Requisition/Internal Orders process is used for requesting and transferring material from one inventory location to another. An Internal Requisition is initiated in Oracle Purchasing. Sourcing rules can automatically determine if the source type is to be Inventory or an external supplier. Order Management receives information from the Purchasing Application to create an Internal Sales Order. When the sales order is shipped (to indicate either intra or inter-organization movement of goods), the requisition can be received to record receipt of goods


To generate Internal Sales Order user has to follow the following process steps:
1.Enter Requisition in Oracle Purchasing & Approve the Internal Requisition
2.Run ‘Create Internal Sales Order’ Process for transferring requisitions to Order Management.
3.Run ‘Import Order’ Process in Order Management to create Sales order.
4.Release Sales Order
5.After Order Import completes successfully, book, pick and ship the internal order.
6.Receive against the Internal Requisition.

Source: http://www.oracleappshub.com/om/the-newbies-jump-start-guide-with-oracle-om/

Tuesday, June 19, 2012

Killing session of a Package Lock


SELECT * FROM DBA_DDL_LOCKS WHERE name ='XX_AUTOMATE_DELIVER_PKG'

select * from v$access where object='XX_AUTOMATE_DELIVER_PKG'

SID OWNER
---------- ----------------------------------------------------------------
OBJECT
--------------------------------------------------------------------------------
TYPE
------------------------
128 APPS
XX_AUTOMATE_DELIVER_PKG
PACKAGE

select sid,serial# from v$session where sid=128

SID SERIAL#
---------- ----------
128 31404

alter system kill session '128,31404'

System altered.

select * from v$access where object='XX_AUTOMATE_DELIVER_PKG';

no rows selected

Sample TKPROF Output


Interpreting TKPROF Output

PARSE Translates the SQL statement into an execution plan, including checks for proper security authorization and checks for the existence of tables, columns, and other referenced objects.

EXECUTE Actual execution of the statement by Oracle. For INSERT, UPDATE, and DELETE statements, this modifies the data. For SELECT statements, this identifies the selected rows.

FETCH Retrieves rows returned by a query. Fetches are only performed for SELECT statements.


COUNT Number of times a statement was parsed, executed, or fetched.
CPU Total CPU time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.

ELAPSED Total elapsed time in seconds for all parse, execute, or fetch calls for the statement. This value is zero (0) if TIMED_STATISTICS is not turned on.

DISK Total number of data blocks physically read from the datafiles on disk for all parse, execute, or fetch calls.

QUERY Total number of buffers retrieved in consistent mode for all parse, execute, or fetch calls. Usually, buffers are retrieved in consistent mode for queries.

CURRENT Total number of buffers retrieved in current mode. Buffers are retrieved in current mode for statements such as INSERT, UPDATE, and DELETE.

ROWS Total number of rows processed by the SQL statement. This total does not include rows processed by subqueries of the SQL statement.

Sample TKPROF Header
Copyright (c) Oracle Corporation 1979, 1999. All rights reserved.
Trace file: v80_ora_2758.trc
Sort options: default
********************************************************************************
count     = number of times OCI procedure was executed
cpu        = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk       = number of physical reads of buffers from disk
query     = number of buffers gotten for consistent read
current   = number of buffers gotten in current mode (usually for update)
rows      = number of rows processed by the fetch or execute call
********************************************************************************
The following statement encountered a error during parse:
select deptno, avg(sal) from emp e group by deptno
       having exists (select deptno from dept
      where dept.deptno = e.deptno
      and dept.budget > avg(e.sal)) order by 1
Error encountered: ORA-00904
********************************************************************************

Sample TKPROF Body
ALTER SESSION SET SQL_TRACE = true
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute    1      0.00       0.10          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          1      0.00       0.10          0          0          0           0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
********************************************************************************
SELECT emp.ename, dept.dname 
FROM emp, dept
  WHERE emp.deptno = dept.deptno

call     count       cpu    elapsed       disk      query    current        rows
-------      ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.11       0.13          2          0          1           0
Execute     1      0.00       0.00          0          0          0           0
Fetch         1      0.00       0.00          2          2          4          14
-------      ------  -------- ---------- ---------- ---------- ----------  ----------
total            3      0.11       0.13          4          2          5          14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     14   MERGE JOIN
      4    SORT (JOIN)
      4     TABLE ACCESS (FULL) OF 'DEPT'
     14    SORT (JOIN)
     14     TABLE ACCESS (FULL) OF 'EMP'

********************************************************************************
SELECT a.ename name, b.ename manager 
FROM emp a, emp b
  WHERE a.mgr = b.empno(+)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute    1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.01          1         50          2          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          3      0.02       0.02          1         50          2          14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 01  (USER01)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     13   NESTED LOOPS (OUTER)
     14    TABLE ACCESS (FULL) OF 'EMP'
     13    TABLE ACCESS (BY ROWID) OF 'EMP'
    26     INDEX (RANGE SCAN) OF 'EMP_IND' (NON-UNIQUE)
********************************************************************************
SELECT ename, job, sal
FROM emp
WHERE sal =
       (SELECT max(sal)
        FROM emp)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0         12          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          3      0.00       0.00          0         12          4           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 01  (USER01)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     14   FILTER
     14    TABLE ACCESS (FULL) OF 'EMP'
     14    SORT (AGGREGATE)
     14     TABLE ACCESS (FULL) OF 'EMP'
********************************************************************************
SELECT deptno
FROM emp
WHERE job = 'clerk'
GROUP BY deptno
HAVING COUNT(*) >= 2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     1      0.00       0.00          0          0          0           0
Fetch         1      0.00       0.00          0          1          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total           3      0.00       0.00          0          1          1           0 

Misses in library cache during parse: 13
Optimizer goal: CHOOSE
Parsing user id: 01  (USER01)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   FILTER
      0    SORT (GROUP BY)
     14     TABLE ACCESS (FULL) OF 'EMP'
********************************************************************************
SELECT dept.deptno, dname, job, ename
FROM dept,emp
WHERE dept.deptno = emp.deptno(+)
ORDER BY dept.deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          3          3          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total           3      0.00       0.00          0          3          3          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 01 (USER01)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     14   MERGE JOIN (OUTER)
      4    SORT (JOIN)
      4     TABLE ACCESS (FULL) OF 'DEPT'
     14    SORT (JOIN)
     14     TABLE ACCESS (FULL) OF 'EMP'
********************************************************************************
SELECT grade, job, ename, sal
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal
ORDER BY grade, job

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.04       0.06          2         16          1           0
Execute    1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.01          1         10         12          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          3      0.05       0.07          3         26         13          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     14   SORT (ORDER BY)
     14    NESTED LOOPS
      5     TABLE ACCESS (FULL) OF 'SALGRADE'
     70     TABLE ACCESS (FULL) OF 'EMP'
********************************************************************************

SELECT LPAD(' ',level*2)||ename org_chart, level, empno, mgr, job, deptno
FROM emp
CONNECT BY prior empno = mgr
START WITH ename = 'clark'
  OR ename = 'blake'
ORDER BY deptno

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute    1      0.00       0.00          0          0          0           0
Fetch        1      0.01       0.01          0          1          2           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          3      0.02       0.02          0          1          2           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      0    CONNECT BY
     14     TABLE ACCESS (FULL) OF 'EMP'
      0     TABLE ACCESS (BY ROWID) OF 'EMP'
      0     TABLE ACCESS (FULL) OF 'EMP'
********************************************************************************
CREATE TABLE TKOPTKP (a number, b number)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    1      0.01       0.01          1          0          1           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          2      0.01       0.01          1          0          1           0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  CREATE TABLE STATEMENT   GOAL: CHOOSE

********************************************************************************
INSERT INTO TKOPTKP 
VALUES (1,1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.09          0          0          0           0
Execute    1      0.01       0.20          2          2          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          2      0.08       0.29          2          2          3           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE

********************************************************************************
INSERT INTO TKOPTKP SELECT * FROM TKOPTKP
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    1      0.02       0.02          0          2          3          11
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          2      0.02       0.02          0          2          3          11

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   GOAL: CHOOSE
     12   TABLE ACCESS (FULL) OF 'TKOPTKP'
********************************************************************************
SELECT * 
FROM TKOPTKP 
WHERE a > 2

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          0          0           0
Execute    1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.00          0          1          2          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total          3      0.01       0.01          0          1          2          10

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 02   (USER02)
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
     24   TABLE ACCESS (FULL) OF 'TKOPTKP'
********************************************************************************

Sample TKPROF Summary
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       18      0.40       0.53         30        182          3           0
Execute   19      0.05       0.41          3          7         10          16
Fetch       12      0.05       0.06          4        105         66          78
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total         49      0.50       1.00         37        294         79          94

Misses in library cache during parse: 18
Misses in library cache during execute: 1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       69      0.49       0.60          9         12          8           0
Execute   103      0.13       0.54          0          0          0           0
Fetch       213      0.12       0.27         40        435          0         162
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total         385      0.74       1.41         49        447          8         162

Misses in library cache during parse: 13
   19  user  SQL statements in session.
   69  internal SQL statements in session.
   88  SQL statements in session.
   17  statements EXPLAINed in this session.
********************************************************************************
Trace file: v80_ora_2758.trc
Trace file compatibility: 7.03.02

Sort options: default
       1  session in tracefile.
      19  user  SQL statements in trace file.
      69  internal SQL statements in trace file.
      88  SQL statements in trace file.
      41  unique SQL statements in trace file.
      17  SQL statements EXPLAINed using schema:
           SCOTT.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
    1017  lines in trace file.
Related Posts Plugin for WordPress, Blogger...