Thursday, January 15, 2015

How to Setup and Verify Audit Changes to Orders using Audit History



Setup Steps
The following example illustrates how to audit when a change is made to the Payment Terms of an order that is booked with a reason as to why it was changed. The steps to initiate Audit History are:
1. Turn Audit History on at the site level.
     a) Navigation: Order Management Super User > Setup > System Parameters > Values
     b) Change "Audit Trail" from Disable.
There are two choices:
Enable When Order is Entered: This will initiate auditing when a change is made to a specified field after the order has been Saved.
Enable When Order is Booked: This will initiate auditing when a change is made to a specified field after the order has been Booked.
2.  Identify a specified field when changed that will be audited.      

     a) Navigation: Order Management Super User > Setup > Rules > Security > Processing Constraints
     b) Setup Processing Constraints for Order header or Line attribute for UPDATE or CANCEL operation that requires
          1) Required Reason and History - a reason window will always appear and a reason with optional comments must be entered. History will be captured.
          2) Require History - a reason window will not appear when making a change and history will be captured. There is always the flexibility of entering a reason by navigating to Tools > Change Reason.
          3) Require Reason, History and Raise Integration Events
     c) Position your cursor on the field to be audited in the constraints block and click the plus icon in the toolbar. This will add a new row where information can be entered.
     d) You can then change to the Conditions tab in the form and select: Scope, Order Validation template.
     e) Next navigate to the applicable tab. The audit can be associated with a specific responsibility.  "All Responsibilities" can be selected.

3. Run Generate Constraints Validation Packages

     a) Navigation: Order Management Super User > Setup > Rules > Security > Generate Constraints Validation Packages
4. Now test the Audit Trail defined. 
     a) Enter Sales Order header and Line Information and Save as usual.
     b) After you saved the Sales Order, try to change the value of the field on which Processing Constraints.

5. In order to obtain data in the Audit History Report it is necessary to run on a regularly scheduled basis a consolidation program that will extract the audit information and add it to a history file.
     a) Navigation: Order Management Super User > Reports,Request > Run Requests > Select Audit History Consolidator (OEXAUGEN).
     b) Data can be extracted based on History Date From and To, Order Number From and To, or Changes in last N days.
     c) Enter 1 in the Changes in last N days.
     d) To view the data either an audit report. Navigation: Order Management Super User > Reports,Requests > Reports > Select Audit History.
     e) Report can be created based upon History Date From and To, Order Number From and To, Entity Name, Attribute, User, and Responsibility.
6. To view history online, Navigation: Order Management Super User > Orders, Returns > View Audit History and select the data you want to view.

How to track Comments field In Sales Order Form > Line tab
Comments and Reason fields on the Lines are never stored in OE_ORDER_LINES_ALL table. They are used by Processing Constraints (Auditing) frame work to store the reason/comments for each change being done by users.  Hence when ever a Line is saved, these fields are always cleared and made as blank, to again capture the reason/comments for any new changes.  This is intended behavior. Users will have to use DFF to store their comments.

You can see Comment field via View Audit History form:
Run: Audit History Consolidator
Order Management Responsibility:
Orders, Returns -> View Audit History
Query on Order Number: 
Go to Lines Tab
Put Cursor on each line..
You can see , bottom of the form,
Reason : Item Description: Comments:...

How to Verify Audit History changes:
Check the data in attribute change in Tables
When OM system parameter: Audit Trial is enabled and proper processing constraints are defined for the auditable attributes, the relevant history tables are populated with the history data.
         OE_ORDER_HEADER_HISTORY - Table that stores history of attribute change for Order Headers
         OE_ORDER_LINES_HISTORY  - Table that stores history of attribute change for Order Lines
         OE_PRICE_ADJS_HISTORY   - Table that stores history of attribute changes of Entity Price.
         OE_SALES_CREDIT_HISTORY - Table which stores history of attribute changes of Entity Sales Credits

     For example: If the processing constraint is setup for the
         Application: Order Management.
         Entity: Order Line and
        Attribute: Sales Person

     Select  *  from OE_ORDER_LINES_HISTORY 
     Where header_id = &Enter Order header for  which the changes were made.
     and line_id = &Enter line_id of the Order on which changes were made.

Similarly if the processing constraint was set up for the attributes on the ‘Order header’  or ‘Price adjustments’ window or ‘Sales Credits’ entity, the data for the attribute change can be checked in tables
         OE_ORDER_HEADER_HISTORY - Table that stores history of attribute change for Order Headers
         OE_PRICE_ADJS_HISTORY - Table that stores history of attribute changes of Entity Price.
         OE_SALES_CREDIT_HISTORY - Table which stores history of attribute changes of Entity Sales Credits

Check the data in Audit Attribute History Tables
Check the data OE_AUDIT_ATTR_HISTORY - Table which holds the consolidated history data in a format needed for viewing and reporting history. This table is populated with the history data by Audit History Consolidator concurrent program.

Select * from OE_AUDIT_ATTR_HISTORY  
Where ORDER_NUMBER = &Enter Order number for which the history data is consolidated


Investigating issues with Audit History
Important: In the case the Audit History is not captured even though Require History constraint is defined, check
a) If there is a versioning constraint for the same operation. Versioning takes precedence and version is captured instead of the audit record.
          Example: You have processing constraints defined for:
              • Update of Salesperson with action Require History
              • Update of Salesperson with action Generate Version
            The versioning will take precedence.

b) Check processing constraint is setup for the 'All responsibilities' or the Responsibility used for making the changes the is listed in 'Constrained Responsibilities'
     Navigation: Navigation: Order Management Super User > Setup > Rules > Security > Processing Constraints
     Check under ‘Applicable To’ tab

Sunday, December 14, 2014

Drop Ship PO

Q1. What is a Drop Ship PO?
A: Oracle Order Management and Oracle Purchasing integrate to provide Drop Shipments. Drop Shipments are orders for items that your supplier ships directly to the customer either because you don't stock or currently don't have the items in inventory, or because it's more cost effective for the supplier to ship the item to the customer directly. Drop Shipment was introduced in R11.

Q2. How is a Drop Ship PO created?
A: Drop Shipments are created as Sales Orders in Order Management. The Purchase Release concurrent program or workflow in Order Management creates rows in the Requisition Import tables in Purchasing. Then Purchasing's Requisition Import process creates the requisitions. Drop Shipments are marked with the Source Type of External in Order Management and Supplier in Purchasing.

Q3. What is the setup required for Drop Ship PO?
A: ITEM ATTRIBUTES:
Navigate: Inventory -> Items - > Organization items
Purchased (PO) Enabled
Purchasable (PO) Enabled
Transactable (INV) Enabled
Stockable (INV) Optional
Reservable (INV) Optional
Inventory Item (INV) Optional
Customer Ordered (OM) Enabled
Customer Orders Enabled (OM) Enabled
Internal Ordered (OM) Disabled
Internal Orders Enabled (OM) Disabled
Shippable (OM) Optional
OE Transactable (OM) Enabled
All Drop Ship items must be defined in the organization entered in the profile option OE: Item Validation Organization and in the Receiving Organization.
All Drop Ship sub-inventory must have Reservable box checked. If the sub-inventory is not Reservable the Sales Order issue transaction will not be created in MTL_TRANSACTIONS_INTERFACE. After Drop Ship inventory organization is created, subinventories should be defined. To create the subinventory, go to an inventory responsibility and navigate to Setup -> Organizations -> Subinventories. Asset subinventories must have the reservable and Asset boxes checked. Expense subinventories must have the Reservable box checked and the Asset box unchecked.
Subinventory Attributes for Asset Subinventory
Reservable/Allow Reservations
Asset Subinventory
Subinventory Attributes for Expense Subinventory
Reservable
Asset-must NOT be enabled.

Q4. How can we avoid the miscounting of supply as logical organization is involved?
A: You must receive drop-ship items in a logical organization. If you use Oracle master Scheduling/MRP and Oracle Supply Chain Planning, to avoid miscounting supply you may not want to include logical organizations in your planning. If you choose to include logical organizations, ensure that doing so does not cause planning and forecasting complications.

Q5. If you make changes to a Sales Order after the Purchase Order (PO) has been generated, will the order changes automatically be updated on the PO?
A: Order changes will not be automatically updated on the PO. Pulling up the Discrepancy report will allow you to view the differences between the Sales Order and PO. However, you will have to manually update the POs in the Purchasing application. Please note: Sales Order changes will be automatically updated on the PO if the PO Status is Incomplete or Require Re-approval. If the PO is already approved, you cannot make changes to the Sales Order line (without un-approving the PO).

Q6. If items on a Drop Ship order are cancelled, does the system automatically generate a PO Change to the PO originally sent to the supplier?
A: No, Drop Ship functionality in this regard remains the same as in R11. There is a discrepancy report available that will report differences between the PO and the Sales Order.

Q7. Does Order Management 11i have functionality to do serial number management with Drop Shipments?
A: You are able to receive serial numbered Drop Ship stock. Order Management will receive the serial number noted on the PO.

Q8. Can Configurable Items be Drop Shipped?
A: Currently only Standard Items can be Drop Shipped. Functionality for Configurable Items will be added in future releases.


Q9. How do I Drop Ship across operating units?
A: Release 11i does not currently support this functionality.

Q10. How are over/under shipments handled in Drop Shipment?
A: If part of a drop-ship line ships, and you do not wish to fulfill the remaining quantity, cancel the line. Over shipments must also be handled manually. If the supplier ships more than the ordered quantity, you can bill your customer for the additional quantity or request that they return the item. Use the Drop Ship Order Discrepancy Report to view differences between your drop-ship Sales Orders and their associated purchase requisitions and orders.

Q11. Will Blanket PO's work with Drop Shipment?
A: Blanket PO's will not work with Drop Shipment because the PO must be created when OM notifies PO that a Drop Ship order has been created. This PO is linked to the Drop Ship order so that when the receipt is done (partial or complete) .OM is updated to receiving interface eligible. Drop Ship lines do not use the pick release, ship confirm or inv interface order cycles.

Q12. Is it possible to create releases based on drop shipment orders for the items in the blanket agreement when a valid Blanket PO is created ?
A: Yes, it is possible to create releases from drop ship orders automatically or via Autocreate window, if the drop ship Requisition has the correct Blanket Information (Source)

1.Set up the ASL entry for the supplier site desired.
2.Depending on the hierachy determine which assignment  better suits and define sourcing rule according to the required level with   the required supplier,site.

Also ensure that ASL entry exists for the above supplier/site.

For automatic sourcing to be done also set profile  'PO:Allow Automatic Sourcing' to Yes so that the source document will be determined automatically depending on the creation dates and the type of the document.

Q13. Can we cancel Drop Shipment after it is received?
A: Drop Shipments cannot be cancelled once Oracle Purchasing obtains the receipt. A user who wants to cancel a Drop Ship Sales Order line must ensure no receipts have been created against the line and that the requisition and/or Purchase Order associated with the line is cancelled. Cancellation of a Partial Drop Ship receipt is allowable. But only the portion that has not been received can be cancelled. If you cancel a Drop Shipment line for which you have not shipped the entire quantity, the order processing splits the line. The first line contains the quantity shipped and the second line contains the non-shipped quantity in backorder. You can cancel the second line the backorder on the Sales Order. The PO line quantity should be changed to reflect the new quantity.


Q14. What debugging tools are available for Drop Shipments?
A: 1. Note 133464.1 contains a diagnostic script that can be used for troubleshooting problems with Sales Orders.
2. Debugging receipt transaction or the Sales Order issue transaction, Set the following profile options:
RCV: Processing Mode to Immediate or Batch
RCV: Debug Mode to Yes
OM: Debug Level to 5
INV: Debug Trace to Yes
INV: Debug level to 10
TP: INV Transaction processing mode to Background
-Then go to Sys Admin: Concurrent: Program: Define; query up the Receiving Transaction Processor and check the Enable Trace box.
-Save the receipt for the deliver transaction (destination type will say Inventory for the deliver transaction).
-View the Receiving Transaction Processor log file, the Inventory Transaction Worker log file, as well as, the trace for the errors.


Q15. What is the Import source and status of PO generated from Drop Shipment?
A: Import source is Order Entry.
Status of PO will always be Approved.

Q16. Can we receive a PO Shipment for which the related Sales Order Line is Cancelled / Closed?
A: PO Shipments for which the relates Sales Order line has been cancelled or closed cannot be received. The PO shipment should be cancelled. A new Sales Order Line should be created, if needed.

Monday, December 1, 2014

Cannot Approve PO Error: Line # 1 Shipment # 1 Distribution # 1Distribution is missing foreign currency rate



Cannot Approve PO Error: Line # 1 Shipment # 1 Distribution # 1Distribution is missing foreign currency rate

SYMPTOMS
When attempting to Approve Purchase Order the following error occurs:
ERROR
Error: Line # 1 Shipment # 1 Distribution # 1Distribution is missing foreign currency rate.

STEPS
The issue can be reproduced at will with the following steps:
1- Create PO with base currency
2- Enter PO line, shipment and distribution and save
4- Change PO currency,  Error message :
        APP-PO-14338: Purchase order lines not automatically converted to this
       Currency
       You are trying to change the currency of a purchase order header after creating purchase
       Order lines for this header. Oracle Purchasing does not automatically convert
       Price on the purchase order lines after you change the currency.
       In the Tools menu, select Convert Currency if you want to convert all
       Purchase order line amount to the new currency. If the purchase order
       Contains amount-based line types, you will need to manually update the
       Price information on all purchase order lines to reflect your changes in
        The currency.
   
    5- Click on approve, error message appears:
         Error:    Line # 1 Shipment # 1 Distribution # 1 Distribution is missing foreign currency rate.
   
CAUSE
Currency rate at distribution level is not automatically updated after changing the currency at the header level.
SOLUTION
To implement the solution , execute the following:
1.Make the change of currency at PO header level. Do not save.
2.From the Tools menu in Purchase Order entry form, select convert currency to convert all
   Purchase Order distributions to the new currency.
3. Save. This will ensure currency will be changed at PO distribution level.

Thursday, November 20, 2014

How does one set up a specific user to only have Create Class and Enroll Learners in a class functionality?



How does one set up a specific user to only have Create Class and Enroll Learners in a class functionality?

Fix

The simplest solution is to use the Learning Instructor Self-Service responsibility.

Here are some items to verify and additional notes are referenced at the end of this document.
Basically, Instructor must be either an Employee or Contracted Worker (contingent worker)
They must be active at the time they are attempting to log in.
They must be assigned to an organization within the same hierarchy the learning administrator belongs to
They must have a USER Log in that lists them as the person tied to that user.
They must have the responsibility "Learning Instructor Self-Service"
They must be defined in Learning management as a Resource Type of Trainer.

Things to check:

1) HR Manager Responsibility
People > Enter and Maintain
a) Employee exists and is active (not terminated)
b) Org is filled in on Assignment form (Assignment button)
Work Structures > Hierarchy
c) Org Exists in Hierarchy
Security > Profiles
d) Find the Security Profile(s) that currently exist in the system. In this case it should have the PRIMARY box checked.

2) System Administrator
Security > User > Define
a) USER record is tied to the Employee created in (1).
b) USER has the "Learning Instructor Self-Service" responsibility
Profiles > System
c) System Profile "HR: Security Profile" MUST include the correct security profile that was created and includes the Hierarchy verified in (1.c) above.
d) System Profile: "OTA: Global Business Group", should also be set to the correct business group

3) Learning Administrator
a) Trainer Resource is created for this USER
b) PERSON field is filled in using the same person name as is tied to the user created in (2).
c) The Resource Type MUST be the seeded "Trainer" type.
This CANNOT be a user created value.

4) Log in as the USER created in (2)
There should be no error like:
"The application does not recognize you, so you cannot access Instructor Home Page. Contact
your system administrator."

Wednesday, November 19, 2014

Expenses Workflow Errors with is not a valid role or user name



Symptoms
The Expenses workflow errors out when sending the Inform Preparer Expense Report Approval Notification with Error:

Inform Preparer Expense Report Approval #EXCEPTION WFNTF_ROLE
3205: '' is not a valid role or user name.

Wf_Notification.SendGroup(, APEXP, MSG_EXP_REPORT_MGR_APPROVED, WF_ENGINE.CB)
Wf_Engine_Util.Notification_Send(APEXP, 19006, 61417, APEXP:MSG_EXP_REPORT_MGR_APPROVED)
Wf_Engine_Util.Notification(APEXP, 19006, 61417, RUN)
Cause
Workflow Tables are out of sync.
Solution
1. Please login to the System Administrator responsibility:
-Requests -> Run
-Name = 'Synchonize WF LOCAL tables'
-Parameters: Orig System = 'ALL'
-Submit
2. Retest with a new Expense Report.

Saturday, November 8, 2014

mtl_secondary_inventories mtl_material_statuses_tl

SELECT msi.secondary_inventory_name, msi.organization_id, msi.description,
       mmst.status_code, mmst.description
  FROM mtl_secondary_inventories msi, mtl_material_statuses_tl mmst
 WHERE 1 = 1 AND msi.status_id = mmst.status_id


SELECT DISTINCT site.organization_code, sub.secondary_inventory_name, sub.*
           FROM apps.mtl_secondary_inventories sub, apps.mtl_parameters site
          WHERE sub.secondary_inventory_name LIKE 'ISO003208%'
            AND sub.organization_id = site.organization_id
       ORDER BY sub.secondary_inventory_name;