Total received Qty in 9 months

select sum(transaction_quantity) from mtl_material_transactions mtl
where mtl.organization_id = mtl.organization_id
and mtl.transaction_quantity>0
and mtl.transaction_date between trunc(sysdate) and trunc(sysdate-270)"kooo"; --- tot_rec_qty_9mths;

Total Received Qty

select sum(transaction_quantity),mt.transaction_date from mtl_material_transactions mt  
where mt.ORGANIZATION_ID = mt.ORGANIZATION_ID
and mt.ORGANIZATION_ID = 162
and mt.transaction_quantity>0
--and mt.transaction_date between trunc(sysdate) and trunc(sysdate-270)    ---"tot_rec_qty_9mths"
--order by mt.transaction_quantity,mt.transaction_date
group by mt.transaction_quantity,mt.transaction_date;

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

ROWID and ROWNUM

SELECT ROWID tt, rownum, last_name
   FROM employees
   WHERE department_id = 60;
 
 
SELECT * FROM employees WHERE ROWNUM < 10;


SELECT ROWID ooo, rownum   FROM employees
WHERE department_id = 80

select rowid ooo,rownum, ci.INVENTORY_ITEM_ID  from gmd_recipe_validity_rules ci 

Oracle Apps R12. OPM Query to Find out Quantity of Products and Ingredients of all Batches in particular Month.

Select b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,
decode(c.line_type,-1,'Ingredient','Product') "Type", sum(e.TRANSACTION_QUANTITY) "quantity"
from
GME_BATCH_HEADER a,
gmd_recipes b,
gmd_recipe_validity_rules grr,
gme_material_details c,
mtl_system_items d,
mtl_material_transactions e,
org_organization_definitions f
where a.FORMULA_ID = b.FORMULA_ID
and b.RECIPE_ID = grr.RECIPE_ID
and grr.ITEM_ID = c.ITEM_ID
and c.INVENTORY_ITEM_ID = d.INVENTORY_ITEM_ID
and d.INVENTORY_ITEM_ID = e.INVENTORY_ITEM_ID
and e.ORGANIZATION_ID = f.ORGANIZATION_ID
and f.ORGANIZATION_CODE = 'A01'
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type,e.TRANSACTION_QUANTITY;

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

select ai.BATCH_NO,bi.RECIPE_DESCRIPTION,ci.STD_QTY,di.MATERIAL_DETAIL_ID,ei.DESCRIPTION,fi.TRANSACTION_QUANTITY,gi.ORGANIZATION_CODE,
decode(di.LINE_TYPE, -1,'Ingredient',1,'Product') "Type", sum(fi.TRANSACTION_QUANTITY)"quantity"
from gme_batch_header ai, gmd_recipes bi, gmd_recipe_validity_rules ci, gme_material_details di,mtl_system_items ei,
mtl_material_transactions fi,org_organization_definitions gi
where ai.FORMULA_ID = bi.FORMULA_ID
and bi.RECIPE_ID = ci.RECIPE_ID
and ci.ITEM_ID = di.ITEM_ID
and di.ORGANIZATION_ID = ei.ORGANIZATION_ID
and ei.INVENTORY_ITEM_ID = fi.INVENTORY_ITEM_ID
and fi.ORGANIZATION_ID = gi.ORGANIZATION_ID
and gi.ORGANIZATION_CODE = 'A01'
group by ai.BATCH_NO,bi.RECIPE_DESCRIPTION,ci.STD_QTY,di.MATERIAL_DETAIL_ID,ei.DESCRIPTION,fi.TRANSACTION_QUANTITY,gi.ORGANIZATION_CODE,
di.LINE_TYPE,fi.TRANSACTION_QUANTITY;

--and di.INVENTORY_ITEM_ID = ei.INVENTORY_ITEM_ID
--and ei.ORGANIZATION_ID = fi.ORGANIZATION_ID

PROD_Batch_ Status_Query

SELECT o.ORGANIZATION_CODE, o.ORGANIZATION_NAME, o.ORGANIZATION_ID,
gbh.BATCH_NO,decode(gbh.BATCH_STATUS,3,'Completed') Batch_Status
FROM GME_BATCH_HEADER gbh,
ORG_ORGANIZATION_DEFINITIONS o
WHERE CREATION_DATE between TO_DATE('01-Feb-2017','DD-MM-YYYY') and TO_DATE('28-Feb-2017','DD-MM-YYYY')
AND gbh.ORGANIZATION_ID=o.ORGANIZATION_ID
ORDER BY BATCH_STATUS, o.ORGANIZATION_CODE

Oracle EBs Apps Blog of Mahfuz, OPM Consultant, Bangladesh.

 

 

http://mahfuzgeml.blogspot.com/2015/12/opm-formula-to-execution-work-flow.html

Defining Resource Cost in Oracle Process Manufacturing (OPM) # Defining Fixed Overheads in Oracle Process Manufacturing (OPM) # How to open new period in OPM Cost Calendar? # Subledger Accounting Balances Update # OPM Cost Allocation Process # Setting up default value of Create Accounting mode as “Draft” (Process Manufacturing Financial) #

Defining Resource Cost in Oracle Process Manufacturing (OPM)

To reflect the cost of the resource incurred during production in the product cost, set up routings and define the amount or number of resources used.

Outside production, set up overheads to reflect the amount of resources other than the production or ingredients used in the product; you can then include overhead costs in the cost of producing the product. In either case, first we must define nominal usage cost associated with the resource.

To know about defining Overheads, click the following link

http://dj-oracleapps.blogspot.in/2014/06/defining-fixed-overheads.html

Pre-requisites:

Resources

To know about how to define a resource, click the following link

http://dj-oracleapps.blogspot.in/2014/02/creating-resources-in-oracle-process.html

Navigation: OPM Financials > Resource costs

Enter the code of the Organization for which this resource cost is effective.

Note: If Organization is not entered then the resource cost will be considered for all the organizations that fall under the displayed Legal entity.

Enter the Resource for which we are defining the cost.

Enter the Cost Calendar & Period for which this resource cost will be applicable.

Component class displays the component class associated with the resource & Base Currency displays the legal entity’s base currency.

Cost Details

Enter for which Cost Type this resource cost is defined.

Enter a Nominal Cost for the resource for using it for one unit of measure. For example, if you are defining the resource cost for a mixing machine, and its usage is measured in hours, then enter the cost of using the mixing machine for one hour.

Enter the unit of measure in which usage of this resource is measured in UOM. By default the system will display the unit of measure initially defined for this resource. We can edit the value within the same unit of measure class.

Note: Status field is not applicable

Defining Fixed Overheads in Oracle Process Manufacturing (OPM)

An overhead is a cost associated with a resource other than the resource usage assigned in the routing.

Assume you need a laborer to clean MIXER after each use. To account for the cost of cleanup, instead of adding another component cost to each item that uses MIXER, you can assign an overhead to the items being produced by that laborer on MIXER. The overhead assignment would be the time it takes the laborer to clean MIXER multiplied by the cost per hour for the laborer.

Navigation:

OPM Financials > Fixed Overheads

To define Fixed Overheads:

Enter the Item code to which this overhead is going to assigned.

Enter a valid Calendar, Period code & Cost type.

Base Currency code field will display the base currency of the legal entity.

Overhead Details:

Enter the Resource code and Component Class code.

To know how to create a Resource click the following link  http://dj-oracleapps.blogspot.in/2014/02/creating-resources-in-oracle-process.html

Enter the Analysis code under which this overhead should appear in cost detail.

Enter the Qty of resource used in the production of this item in Resource Count. Ex: If it takes one worker to cleanup Mixer after each use, then enter 1. This number is multiplied by the Resource Usage to calculate the total resource usage.

Enter the amount of the resource used for this overhead in Resource Usage. Ex: If it takes 10 minutes for a worker to cleanup Mixer after each use, then enter 0.10

Enter the unit of measure in which the resource is yielded in Resource UOM.

Enter the amount of the item yielded in this production process during the entered .10 hours of resource usage in Item Quantity.

Enter the unit of measure in which this overhead is measured in Overhead UOM field.

---X---

How to open new period in OPM Cost Calendar?

Navigation: OPM Financials > Setup > Cost Calendars

Query the calendar which you use

In the new line enter the Period code, Description & End Date of the period & click ‘Assignments’.

Click on the cost type you are using and click ‘Period Status’ tab.

By default the status of the new period will be ‘Never Opened’.

Click the ‘lov’ and change the status to “Open”.

---X---

Subledger Accounting Balances Update

In this post we will see what Subledger accounting balances update concurrent is & what it does?

The Subledger Accounting Balances Update is a standalone concurrent program which calculates the control balances and analytical balances (supporting reference balances) for any newly accounted transactions.

Normally, Create Accounting and online accounting will automatically call the Subledger Accounting Balances Update program to calculate the control/analytical balances. However, there have been situations where multiple online accounting programs are called simultaneously, and if all of them have accounts which belong to either control accounts or analytical accounts then only one of the simultaneous online accounting programs will be successful in updating the balances.

To ensure that balances are up to date, it is recommended that you manually run the Subledger Accounting Balances Update program for the ledger at least once before you run the Third Party Balances report or before viewing Supporting Reference Balances. This is to ensure that all balances are updated completely.

---X---

OPM Cost Allocation Process

In this post we will see what exactly OPM Cost Allocation Process concurrent does and how to run it.

OPM Cost Allocation process concurrent takes care of spreading the costs down to the products as defined in Allocation definitions form.

 

Parameters:

Legal Entity – Enter your LE

Cost Calendar – Enter the name of your calendar

Period – Enter the period for which the cost has to be allocated.

Cost Type – Enter the costing method which you use.

Fiscal year – Fiscal year

Period – This refers to GL Period

Allocation From - Enter the from allocation code   

Allocation To - Enter the to allocation code

Refresh Interface – Yes / No

---X---

Setting up default value of Create Accounting mode as “Draft” (Process Manufacturing Financial)

Navigation:

OPM Financial Responsibility – Setup – Subledger Accounting setups – Accounting Methods Builder – Methods and definitions – Subledger Accounting methods.

Query on user defined Subledger accounting method

Keep the cursor on application “Process Manufacturing Financials” & Click “Accounting Setups”.

In the new form enter your Ledger Name and click go.

Click “Update Accounting options” against your Ledger.

Click on “Update” button against “Subledger accounting options” Setup step

In the list of options click “Update Accounting options” against “Process Manufacturing Financials” Application

In the “Accounting Progam Mode” select “Draft”. Click on Apply button.

By following the above steps, we can define the default value of create accounting program as “Draft”.

---X---