Translate

Wednesday, July 16, 2014

hub-and-spoke planning single plan ASCP

Oracle® Advanced Supply Chain Planning enables you to run holistic plans that span long term aggregate planning to short term detailed schedules, multiple manufacturing processes (lot based, process, discrete, configure-to-order, and project based), and all organizations across a virtual supply chain. 

As a single solution for distribution, supply chain, and manufacturing planning, it is based on one supply chain model, one planning engine, and one setup. 

Its flexible configuration, however, enables you to define different models that can co-exist (hub-and-spoke planning; single plan) and evolve as your organization grows without requiring re-implementation. 

Extensive defaulting logic, paired with a productivity enhancing UI and strong exception management, enables planners to quickly use the tool to make their planning decisions.

PS Does not snap shot Alternate Resource For Firmed Batches

On : 12.1.3.8 version, Anything not covered by other codes

ACTUAL BEHAVIOR
---------------
Find that the alternate resource is not present in Production Scheduling (PS) for firmed OPM Batches

EXPECTED BEHAVIOR
-----------------------
Expect to see the alternate resources present in PS

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1- Define Alternate Resource for a certain Resource- Item
2- Create Firmed Batch on that Item
3- N/A
4- Run Data Collection
5- Run Production Scheduling Schedule (Without ASCP Plan Reference)

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot manually assign alternate resources in PS for OPM firmed batches


CAUSE

The alternate resource for batches that are Firmed at the header are not collected into ASCP and hence this data is not available to PS. This is the correct functionality as it is a positive user action to firm the batch header, without this action the alternate resource is collected into ASCP and is available in PS
 l


SOLUTION

To implement the solution, please execute the following steps:

1. Go into the responsibility: Production Supervisor

2. Navigate to Batches

3. Do not set the batch header as firmed or create the batches from ASCP/PS and firm the resources without firming the header 

Getting error message "gme_gmf_wip_acct_issue" when cancelling batch

When attempting to cancel a batch the following error occurs.

GME_GMF_WIP_ACCT_ISSUE_CONT


STEPS:
1. Production Supervisor > Batches >Cancel batch

CAUSE

Batches having no transaction should not give any warning / error when canceling such batch.

In GMF_BATCH_VALIDATIONS.check_cancel_batch cursor Min_max_trans_date would always get a row due to use of min and max function even when transactions do not exist. Thus cursor not found check needs to be replaced by if min and max dates are null. This would be the case when there are no transactions.

Solution :

Apply Patch

To implement the solution, please execute the following steps:

1. Download and review the read me and pre-requisites for R12.0.x Patch 14627037

                                                                            R12.1.x Patch 16777853
2. Ensure that you have taken a backup of your system before applying the recommended patch.
3. Apply the patch in a test environment.
4. Confirm the following file versions:
     R12.0.x      GMFBCHVB.pls 120.0.12000000.5

     R12.1.x      GMFBCHVB.pls 120.2.12010000.4
You can use the commands like the following:

strings -a $GMF_TOP/patch/115/sql/GMFBCHVB.pls |grep '$Header'
5. Retest the issue.
6. Migrate the solution as appropriate to other environments.

Retrieving Previously Saved Production Batch Error FRM-40735: POST-QUERY trigger raised unhandled exception

Cannot retrieve a previously saved batch.  Entering Material Details or Line Allocations on the batch errors:

FRM-40735: POST-QUERY trigger raised unhandled exception ORA-04062
ORA-04062: signature of package 'APPS.GMIVDBL' has been changed
ORA-01403: no data found

The Navigation Path is:
Production Supervisor>Create Document
Production Supervisor>Batches
(B)Material Details>(B)Line Allocations

CHANGES

Applied OPM 11i Family Pack I (2216258)

CAUSE

An attempt to execute a stored procedure to serve an RPC stub was made, which specifies a timestamp/signature that is different from the current timestamp/signature of the procedure.

SOLUTION

Recompile forms, PLLs and objects in APPS schema using adadmin utility to pick up the new timestamp/signature.

Monday, July 7, 2014

Find Invalid Objects in Oracle DBA

An Simple Sql Script can help you to Find all the Invalid Objects

select count(*) from dba_objects where status = 'INVALID';


If the count is large, proceed with step 5.
Otherwise, proceed with step 9

 Compile Invalid Packages
adadmin
Compile/Reload Applications Database Entities menu
Compile APPS schema

Re-create Missing Grants/Synonyms
adadmin
Maintain Applications Database Entities
Re-create grants and synonyms for APPS schema

Validate APPS Schema
adadmin
Maintain Applications Database Entities
Validate APPS schema

Check the output of Validate APPS schema (in APPS.lst)
Make sure there are no invalid objects and no missing grants/synonyms
If there are you can repeat the above steps

Check for the number of invalid objects (should be minimal)


Sunday, June 22, 2014

Discrete Job Statuses - ORACLE R12

Job and Repetitive Schedule Status Changes

Statuses describe the various stages in the life cycle of a job or repetitive schedule. Statuses are either user or processing controlled. Processing controlled statuses are updated based on the progress and outcome of concurrent processes. User controlled statuses can be updated by the user or are changed based on events such as job or schedule completion.
Some status changes are allowed while others are not. Some status changes are conditional. For example, you cannot change jobs and schedules from Released to Unreleased if there are net issue, move, resource, completion, or scrap charges associated with them. You also cannot unclose a job that is linked to a sales order or that was closed in a now closed accounting period.

Change To USER status From USER StatusUnreleasedReleasedCompleteComplete-No ChargesOn HoldCancelledClosed
UnreleasedN/AYesYesNoYesYesYes
ReleasedCondN/AYesNoYesYesYes
CompleteCondYesN/AYesYesYesYes
Complete - No Charges (DIS)NoNoYesN/ANoNoYes
Complete - No charges (REP)NoNoNoN/ANoNoN/A
On Hold (DIS)CondYesYesNoN/AYesYes
On Hold (REP)CondYesYesNoN/AYesYes
Cancelled (REP)NoNoNoNoNoN/AN/A
Cancelled (DIS)CondYesYesYesYesN/AYes
Pending - Mass Loaded (REP)YesYesYesNoYesYesN/A
Pending Bill Load (DIS)NoNoNoNoNoNoNo
Failed Bill Load (DIS)YesYesNoNoYesNoYes
Pending Routing Load (DIS)NoNoNoNoNoNoNo
Failed Routing Load (DIS)YesYesNoNoYesNoYes
Pending Close (DIS)CondYesYesYesYesYesYes
Failed Close (DIS)YesYesYesNoYesYesYes
Closed (DIS)YesYesYesNoNoNoN/A

Monday, June 16, 2014

Oracle OPM Interview Questions R12

Oracle OPM Interview Questions
1)      What are the different types of manufacturing processes? And what are the primary differences between them? OR
Explain Process Manufacturing v/s Discrete manufacturing?

Discrete Manufacturing is the production of distinct items that use bills of material and routings to determine costs and lead times. Eg: Car manufacturing, Computer Manufacturing etc you get all the parts and assemble them.
OPM on the other hand caters to capabilities to allow for multiple units of measure because the flexibility in batch production is required in process industries. e.g Chemical Industries, food processing etc. This involves complex internal processes and need a high level of control.
Eg: You mix x kgs of sugar and n litres of milk, you get a sweet as the output. This can be in kilos, litres or smaller units. If you do not allow the water to evaporate the output will be a liquid, if you heat longer the output will be a burfi.
What is the difference between the two?
1)ODM uses BOM (Bill of Materials), OPM uses formulas and Recipes
2)UOM will vary depending on the material used in OPM but in ODM UOM will be each or unit
3)ODM assembles or builds things, OPM mixes , blends and transforms or converts.
4)OPM is driven by item attributes and ODM is driven by part number

Oracle Process Manufacturing - 

It the entire product lifecycle for recipe-based
manufacturing, from new product development, recipe
management and production, to cost, quality, and regulatory
management. It enables you to formulate products to
individual customer specifications, manage variability,
optimize capacity, and drive continuous process improvement 

2)      What are the modules that come under oracle’s OPM solution?

Answer: OPM includes OPM Process Planning, Product Development(which includes Formula,Recipe,Quality), Production, Financials(Costing,MAC), Logistics, Regulatory Management etc. These are products which come under this umbrella..
·         OPM Cost Management
·         OPM Formula Management
·         OPM Intelligence
·         OPM Inventory Management
·         OPM Laboratory Management
·         OPM Master Production Scheduling
·         OPM Material Requirements Planning
·         OPM Production Management
·         OPM Purchasing Management
·         OPM Quality Management
·         OPM Capacity
·         OPM Sales Management
·         Oracle Financial
3)      Name and explain frequently used terms in Oracle Process Manufacturing?

Answer:

4)      Name some important tables used in OPM? ORWhich tables stores the formula information?
Answer:
1)      select a.FORMULA_ID,a.formula_no,a.FORMULA_DESC1,b.INVENTORY_ITEM_ID,c.description,b.organization_id,decode(b.line_type,-1,’Ingredient’,'Product’) Type
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
from FM_FORM_MST a,FM_MATL_DTL b,mtl_system_items c
where a.formula_id=b.FORMULA_ID
and b.ORGANIZATION_ID=:your_Org_id
and a.FORMULA_CLASS<>’COSTING’
and b.INVENTORY_ITEM_ID=c.inventory_item_id
and b.ORGANIZATION_ID=c.organization_id
order by a.FORMULA_ID
2)      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 apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type

order by RECIPE_DESCRIPTION
sum(e.TRANSACTION_QUANTITY) quantity
from apps.GME_BATCH_HEADER a,apps.gmd_recipes b,gmd_recipe_validity_rules grr,apps.gme_material_details c,apps.mtl_system_items d,apps.mtl_material_transactions e
where a.FORMULA_ID=b.FORMULA_ID
and a.ROUTING_ID=b.ROUTING_ID
and a.RECIPE_VALIDITY_RULE_ID=grr.RECIPE_VALIDITY_RULE_ID
and grr.RECIPE_ID=b.recipe_id
and a.BATCH_ID=c.BATCH_ID
and a.ORGANIZATION_ID=c.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=d.INVENTORY_ITEM_ID
and c.ORGANIZATION_ID=d.organization_id
and a.batch_id=e.TRANSACTION_SOURCE_ID
and a.ORGANIZATION_ID=e.ORGANIZATION_ID
and c.INVENTORY_ITEM_ID=e.INVENTORY_ITEM_ID
and a.batch_no in (select batch_no from apps.GME_BATCH_HEADER where trunc(plan_start_date) between :from_date and :to_date)
and a.ORGANIZATION_ID=:your_org_id
and trunc(e.transaction_date) between :from_date and :to_date
group by b.RECIPE_DESCRIPTION,a.RECIPE_VALIDITY_RULE_ID,c.INVENTORY_ITEM_ID,d.description,c.line_type
order by RECIPE_DESCRIPTION

5)  Explain what do you mean by  Formula and Recipe?
Formula is Ingredients and their proportions
Receipe is Formula + Routing.

6) What are different kinds of losses?
Fixed loss and Variable loss.


Q. Once an item is assigned to an organization, is it possible to remove this association at a later time?
A. If you have assigned an item to an organization, it can only be deleted from that organization if the item has NOT been transacted. If it has been transacted against you cannot delete the item from the organization. You can, however, make the status of the item “inactive” on the main region of the Item Define form INVIDITM in ‘Item Status’ field. This will prevent users from transacting against the item although the item will continue to be included in LOV’s. 

Q. How do you setup Unit of Measure for an item?
A. When transacting an item, it will always default to the defined Primary Unit of Measure, so if you need to use another UOM, you must select it from the List of Values when you transact it. This implies that you want the Primary Unit of Measure to be that which is used most often and which is likely to be the smallest unit of that type of item. Once you have set the primary UOM for an item, it CANNOT be changed.



Q. How do I change an item’s locator control method once I have transacted against it, for example from No Control to Prespecified?
A. You cannot change this attribute if there is onhand quantity for the item. You must first perform a miscellaneous issue of all quantity. Then the attribute can be changed and once the locator(s) are set up, you can perform a miscellaneous receipt to receive the quantity back into a specified subinventory/locator.

 

Q. How do I change an item’s Unit of Measure?
A. You CANNOT change the UOM for an item once the UOM is assigned to the item. The suggested solution is as follows: If the item has not been transacted you can delete the item and then re-add it with the correct UOM. If the item has been transacted and has no outstanding orders, purchase orders or WIP jobs you can do the following if approved by your management.
1. Rename the existing item ’123′ to ’123-Bad UOM’ and save the item. Any prior history for item ’123′ will now be associated with item ’123-Bad UOM’.
2. Add a new item ’123′ with the correct UOM.

 

Q. How do I delete an Item Template I no longer use?
A. Suggested steps to follow to delete unwanted Item Templates:
1. Navigate Setup: Items: Templates – Find Item Templates form appears.
2. Click on the LOV, and select the Item Template that is to be removed.
3. Click the [Find] button on the Find Item Templates form. The Item Templates Summary form appears, with the Item Template selected.
4. Click the [Open] button on the Item Templates Summary form, and the Item Templates form appears.
5. From the menu, select Edit | Delete Record, then select Action | Save and Proceed. 6. Close the Item Templates Summary form. Be careful not to delete Item Templates you may need again as they are not retrievable once deleted.

 

Q. Is there a way to create custom item attributes?
A. No but you can create and define the descriptive flexfield for this form. This will allows you to associate additional data with an item such as an attribute. See the Oracle System Administrators
Users Guide and the Oracle Flexfields Manual for information on creating Descriptive Flexfields.

 

Q. Why does the application prompt you for Organization when you enter the Master Item form for the very first time even though you have only one Master Organization.
A. Once you setup a Multi-Org environment and you enter the form for the first time, there is the possibility there can be more than one Master Organization. The application
therefore prompts you for an organization which is then used to determine the Master Organization associated with it. In other words, you are choosing the Master Organization by choosing one of the Orgs that belongs to it.

 

Q. How do you change the control level for item attributes from master level to organization level for example. When I try to change this in the Attribute Control Form, I get the message: frm-40200: field protected against update.
A. First change value of the field called “status” to “not used”. You should then be able to change the control level of the attribute from master level to org level. Make sure and change to status back to what it was before the change and save the record.

 

Q. How do I inactivate an item?
A. On the Master Item Form (Inventory->Items->Master Items), change the item status field to “Inactive” or any another defined status which disables all attributes that make an item usable (stockable, shippable, orderable, etc.). The “Inactivate” status comes pre-seeded with the Application and is for this purpose.

Q. What characters can I use in an item name (including special characters)?
A. The only hard fast rule is not to use the character designated in the segment delimiter for the System Item Flexfield. Also, you will not be able to enter any characters not allowed by the valueset associate with the flexfield. It is recommended that you avoid the use of any char that can be used as a delimiter to avoid future problems. It is best to not use special characters like #@%& if at all possible as these may have a negative impact down the road. Some characters, like percent (%), underscore (_), and pound(#), have special meaning when performing queries by form as do the query operator characters (=!><).

 

Q. Can you change the costing enabled and inventory asset flags on an item that has already been transacted against even if there is no on-hand quantities for the item?
A. Yes, you can change the flags. If you are changing the flags from checked to unchecked you will receive a warning that the associated cost records will be deleted alerting you that the Cost of Goods will not be calculated any longer for transactions of this item. In effect what you are doing is changing the item from an asset item to an expense item. If you are changing the flags from being unchecked to checked then a database
trigger will fire and cause cost records to be added for this item. There is no warning that is given.

 

Q. When I create a new item, and choose to assign to All orgs, the application allows me to even though some of those organizations are restricted to my responsibility. Is this the way it is supposed to work? If I assign in an Organization by Organization fashion, I am not allowed to assign to the restricted organizations.
A. The situation described is standard functionality. Even if a responsibility has limited access to a certain number of organizations, the “Assign to All Organizations” check box will assign the item to all orgs, not just the ones limited to the user’s responsibility.

 

Q. How do you tell that an item is an expense item or asset item?
A. Navigate to the Organization Items form: Inventory->Items->Organization Items. Query up the item in question. Change to the “Costing” alternative region. The value of the “Inventory Asset Value” flag determines this. If checked, the item is an asset item.

 

Q. Is there a way to delete or disable an item revision once entered?
A. There is no way to delete a revision nor is there a way to disable it. It must remain there for historical data. The suggested workaround is to create a new item, transfer any Quantity on-hand from the old item to new item and delete the old item. The only down side is you will lose some transaction history