, , , , ,

Oracle 11i/R12 Important Queries in Concurrent , Inventory, Purchaing , HR, Payrol , OPM

To Find Duplicate Item Category Code


SELECT category_set_name, category_concat_segments, COUNT (*)
FROM mtl_category_set_valid_cats_v
WHERE (category_set_id = 1)
GROUP BY category_set_name, category_concat_segments
HAVING COUNT (*) > 1
ORDER BY category_concat_segments

Get Number Of canceled requisition


SELECT a.AUTHORIZATION_STATUS,(a.ORG_ID),(SELECT distinct hr.per_all_people_f.first_name|| ‘ ‘|| hr.per_all_people_f.middle_names|| ‘ ‘|| hr.per_all_people_f.last_name “Employee Name”
FROM hr.per_all_people_f
where hr.per_all_people_f.PERSON_ID in
(select employee_id from fnd_user fu where fu.user_id = a.CREATED_BY)) CREATED_BY,count(SEGMENT1 )
FROM
po_requisition_headers_all a
WHERE
a.creation_date BETWEEN TO_DATE(’01/01/2007′, ‘DD/MM/YYYY’)
and TO_DATE(’30/05/2007′, ‘DD/MM/YYYY’)
and a.AUTHORIZATION_STATUS = ‘CANCELLED’
group by a.AUTHORIZATION_STATUS,a.ORG_ID,a.CREATED_BY

Number of line processed in Order Management


SELECT COUNT (line_id) “Order Line Processed”
FROM oe_order_lines_all
WHERE creation_date BETWEEN TO_DATE (:Fdate, ‘DD/MM/YYYY’)
AND TO_DATE (:tdate, ‘DD/MM/YYYY’)
AND flow_status_code = ‘CLOSED’;

To Check Item Catogry For Inventory master (No Of Segments May Varry)


SELECT ood.organization_name,
segment1|| ‘-‘|| segment2|| ‘-‘|| segment3 catgory
FROM org_organization_definitions ood,
mtl_categories_vl mcv,
mtl_category_sets mcs
WHERE mcs.structure_id = mcv.structure_id
ORDER BY ood.organization_name

Check Locators for inventory Inventory Org Wise(Number of segment may varry)


SELECT mil.segment1 loc_seg1, mil.segment11 loc_seg11, mil.segment2 loc_seg2,
mil.segment3 loc_seg3, mil.segment4 loc_seg4, mil.segment5 loc_seg5,
mil.segment6 loc_seg6,ood.ORGANIZATION_NAME,mil.SUBINVENTORY_CODE
FROM mtl_item_locations mil,org_organization_definitions ood
where mil.ORGANIZATION_ID = ood.ORGANIZATION_ID

Display All Subinventories Setup

select msi.secondary_inventory_name, MSI.SECONDARY_INVENTORY_NAME “Subinventory”, MSI.DESCRIPTION “Description”,
MSI.DISABLE_DATE “Disable Date”, msi.PICKING_ORDER “Picking Order”,
gcc1.concatenated_segments “Material Account”,
gcc2.concatenated_segments “Material Overhead Account”,
gcc3.concatenated_segments “Resource Account”,
gcc4.concatenated_segments “Overhead Account”,
gcc5.concatenated_segments “Outside Processing Account”,
gcc6.concatenated_segments “Expense Account”,
gcc7.concatenated_segments “Encumbrance Account”,
msi.material_overhead_account,
msi.resource_account,
msi.overhead_account,
msi.outside_processing_account,
msi.expense_account,
msi.encumbrance_account
from mtl_secondary_inventories msi,
gl_code_combinations_kfv gcc1,
gl_code_combinations_kfv gcc2,
gl_code_combinations_kfv gcc3,
gl_code_combinations_kfv gcc4,
gl_code_combinations_kfv gcc5,
gl_code_combinations_kfv gcc6,
gl_code_combinations_kfv gcc7
where msi.material_account = gcc1.CODE_COMBINATION_ID(+)
and msi.material_overhead_account = gcc2.CODE_COMBINATION_ID(+)
and msi.resource_account = gcc3.CODE_COMBINATION_ID(+)
and msi.overhead_account = gcc4.CODE_COMBINATION_ID(+)
and msi.outside_processing_account = gcc5.CODE_COMBINATION_ID(+)
and msi.expense_account = gcc6.CODE_COMBINATION_ID(+)
and msi.encumbrance_account = gcc7.CODE_COMBINATION_ID(+)
order by msi.secondary_inventory_name

To Select Unit Of measure exist in ebusiness suite

select uom_code,unit_of_measure
from mtl_units_of_measure

Query to find out Customer Master Information. Customer Name, Account Number, Adress etc.

select p.PARTY_NAME,ca.ACCOUNT_NUMBER,loc.address1,loc.address2,loc.address3,loc.city,loc.postal_code,
loc.country,ca.CUST_ACCOUNT_ID
from apps.ra_customer_trx_all I,
apps.hz_cust_accounts CA,
apps.hz_parties P,
apps.hz_locations Loc,
apps.hz_cust_site_uses_all CSU,
apps.hz_cust_acct_sites_all CAS,
apps.hz_party_sites PS
where I.COMPLETE_FLAG =’Y’
and I.bill_TO_CUSTOMER_ID= CA.CUST_ACCOUNT_ID
and ca.PARTY_ID=p.PARTY_ID
and I.bill_to_site_use_id=csu.site_use_id
and csu.CUST_ACCT_SITE_ID=cas.CUST_ACCT_SITE_ID
and cas.PARTY_SITE_ID=ps.party_site_id
and ps.location_id=loc.LOCATION_ID

Query to Find Responsibilities assigned to particular user.


SELECT b.responsibility_name NAME
FROM apps.fnd_user_resp_groups a,
apps.fnd_responsibility_vl b,
apps.fnd_user u
WHERE a.user_id = u.user_id
AND u.user_id = (select user_id from FND_USER where user_name=:User_name)
AND a.responsibility_id = b.responsibility_id
AND a.responsibility_application_id = b.application_id
AND SYSDATE BETWEEN a.start_date AND NVL (a.end_date, SYSDATE + 1)
AND b.end_date IS NULL
AND u.end_date IS NULL

Query To find the Request Run By Concurrentselect b.user_concurrent_queue_name, c.request_id
from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.concurrent_queue_id = b.concurrent_queue_id
and a.concurrent_process_id = c.controlling_manager;

Query to find on Hand Quantity

select sum(transaction_quantity) from MTL_ONHAND_QUANTITIES
where inventory_item_id=9
and organization_id=188

Qunatity on order, Expected Deliverselect sum(ordered_quantity),a.SCHEDULE_SHIP_DATE
from oe_order_lines_all a
where inventory_item_id=10
and ship_from_org_id=188
group by a.SCHEDULE_SHIP_DATE

Query to find Item Code, Item Description Oracle Item Master Query(select item, description from mtl_system_items_b
where inventory_item_id=&your_item
and organization_id=&organization_id) item,

Query to Find out On Hand Quantity of specific Item Oracle inventory
(select sum(transaction_quantity) from mtl_onhand_quantity_details
where inventory_item_id=&your_item
and organization_id=&organization_id) onhand,
— Qty Issued by X No clue what you want here
–Qty On Order,Expected deivery date(select sum(ordered_quantity),scheduled_ship_date from oe_order_lines_all
where inventory_item_id=&your_item
and ship_from_org_id=&organization_id
group by scheduled_ship_date) order_info,
–Toatl Received Qty
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0) tot_rec_qty,
–Total received Qty in 9 months
(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity>0
and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_rec_qty_9mths,
–Total issued quantity in 9 months(select sum(transaction_quantity) from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0 and transaction_date between trunc(sysdate) and trunc(sysdate-270)) tot_iss_qty_9mths, –Average monthly consumption
(select sum(transaction_quantity)/30 from mtl_material_transactions
inventory_item_id=&your_item
and organization_id=&organization_id
and transaction_quantity<0) avg_month_consumption; Find detail of specific Applications Running in System System Administrator

SELECT application_id,APPLICATION_NAME
FROM fnd_application_tl
WHERE application_name = ‘Purchasing’
AND rownum = 1;

Find the Request Groups and Concurrent ProgamsSELECT g.request_group_name, c.user_concurrent_program_name,
a.application_name, g.application_id, g.request_group_id,
u.unit_application_id, u.request_unit_id
FROM fnd_request_groups g,
fnd_request_group_units u,
fnd_application_tl a,
fnd_concurrent_programs_tl c
WHERE g.request_group_id = u.request_group_id
AND u.unit_application_id = a.application_id
AND u.request_unit_id = c.concurrent_program_id
–and c.USER_CONCURRENT_PROGRAM_NAME like ‘%Purchase Order%’
ORDER BY C.user_concurrent_program_name, A.application_name, g.request_group_id;

Display all categories that the Item Belongs
SELECTunique micv.CATEGORY_SET_NAME “Category Set”,
micv.CATEGORY_SET_ID “Category Set ID”,
decode( micv.CONTROL_LEVEL,
1, ‘Master’,
2, ‘Org’,
‘Other’) “Control Level”,
micv.CATEGORY_ID “Category ID”,
micv.CATEGORY_CONCAT_SEGS “Category”
FROM
MTL_ITEM_CATEGORIES_V micv

Another Query to Get Onhand Qty With Oranization ID, Item Code,  Quantity
SELECT organization_id,
(SELECT ( msib.segment1|| ‘-‘|| msib.segment2|| ‘-‘|| msib.segment3|| ‘-‘|| msib.segment4)
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = moq.organization_id) “Item Code”,
(SELECT description
FROM mtl_system_items_b msib
WHERE msib.inventory_item_id =
moq.inventory_item_id
AND msib.organization_id = moq.organization_id)
“item Description”,
SUM (moq.transaction_quantity) onhandqty
FROM mtl_onhand_quantities moq
GROUP BY moq.organization_id, (moq.inventory_item_id)

/by
, , , ,

Delete an Item

To delete an item which does not violate any delete constraint, navigate to Delete items window

1. Enter a group name at the header and save it.
2. Enter the items needs to be deleted at the line level and save it.
3. Click on delete groups button to delete all the item in the group. Delete item information request is fired.
View the request o/p

 

 

/by
, , , ,

Creating Custom Deletion Constraints

Use custom deletion constraints to enforce specific business rules. Custom deletion constraints prevent the deletion of bills or routings if your data meets the conditions set forth in the constraint.

To create a custom deletion constraint:

1.   Navigate to the Deletion Constraints window:
           
            Inventory->Setup->Items->Delete Constraints
     
2. Enter a name for the deletion constraint.
         
3. Indicate whether the constraint is enabled. An enabled deletion constraint means that it is in effect when the delete concurrent program runs.
         
4. Select what kind of delete entity the constraint applies to: item, bill, routing, component, or operation.
         
5. Enter the SQL Select Statement that the delete concurrent program is to execute. You cannot update SQL statements for predefined deletion constraints.
         
6. Indicate whether to delete if there are rows found or no rows found by the SQL Select Statement
         
7. Enter a failure message from Oracle Application Object Library’s message dictionary to display if the delete fails.

/by
, , , ,

Defining Manufacturer Part Numbers

You can define manufacturer part numbers for items. You can use this information for reporting purposes; and in catalog searches for particular items.
Navigate to the Manufacturers window , Enter the name of the manufacturer &  Save your work.

1. Navigate to the Manufacturers window.
2. Choose Parts.
3. Enter a manufacturer part number.
4. Enter an item.
You can assign the same item to multiple manufacturer part numbers.

 

/by
, , , ,

Customer Items

The concept of customer item is to link the customer item with the inventory item of the organization so that the customer can order with their customer item for which we have a refernce to the inventory item. To facilitate the process we use commodity code to group all the customer items.

  • Customer items can be defined at customer category level, customer level and customer ship to level.
  • For a single customer item we can have two differnt inventory items in customer cross reference with rank 1, 2 etc
  • The process of difining customer cross reference is  

1. Define commodity code
2. Define customer item and
3. Enter the customer reference.Use the Customer Items Summary and Customer Items Detail windows to define and update customer items. You can toggle between these windows with the Summary/Detail option in the Go option on the Toolbar. You can cross reference customer items to your Oracle Inventory items to support processing orders and shipments.

A customer item defined at the Customer level is recognized across all address and address categories for that customer. If you ship an item to multiple customer ship–to sites that have been grouped as an address category, you can define the customer item for that address category. You would define a customer item at the address level if you ship the item to only one ship–to site for that customer.
Commodity Codes
Customer Item Commodity Codes are used to group customer items and can be entered during the definition of customer items.
Navigation : INV -> Setup ->Items -> Customer Item commodity code.

Customer Item1. Select one of the existing Customer Names (in the Details window, you can use either Customer Name or Customer Number,).
2. Select the Definition Level: Customer, Address Category, or Address.
A customer item defined at the Customer level is recognized across all address and address categories for that customer. If you ship an item to multiple customer ship-to sites that have been grouped as an address category, you can define the customer item for that address category. You would define a customer item at the address level if you ship the item to only one ship-to site for that customer.
For the Address Category definition level, enter the address category.
For the Customer Address definition level, enter the customer address.
3. Enter the Customer Item number and description.
4. In the Commodity tabbed region, you can assign the customer item to a Commodity Code.
5. In the Container tabbed region, you can enter the default master and detail containers for this customer item as well as the minimum fill percent for the container.
6. In the Model, Departure Planning tabbed region, you can reference a customer item as a Model by entering the inventory item number of an existing Model item (the BOM Item Type attribute is set to Model).
You can also check Required to indicate that items must be departure planned before they released and Before Build to indicate that ATO items must be departure planned before they are built.
7. In the Demand Tolerances, Active tabbed region, you can enter positive and negative tolerance percentages and select or deselect the Active check box.

Customer Item Cross References
Use the Customer Item Cross References window to define and update cross references between your inventory items and the customer item numbers defined in the Customer Items Summary/Detail windows.
You can also navigate to this window by selecting the Cross Reference button in the Customer Items Summary window. Inventory displays the existing cross references for the customeritem on the current line in the Customer Items Summary window, and you can add new cross references by selecting a new row.
Enter the Rank as a positive number. To permit alternate or substitute inventory items for a customer item, you can define multiple cross references to inventory items for a single customer item. In these cases, you must specify the Rank of the cross reference. The highest rank is 1.

/by