Query to check Internal Requisition Number and Internal Sales Order Number

Query to check Internal Requisition Number and Internal Sales Order Number
  
   select ORIG_SYS_DOCUMENT_REF from OE_ORDER_HEADERS_ALL
   where 1=1
   and ORDER_NUMBER='736953'
  
   
   select * from po_requisition_HEADERs_all
   where SEGMENT1='45643'

1. OE_ORDER_LINES_ALL.SOURCE_DOCUMENT_LINE_ID =
    po_requisition_lines_all.REQUISITION_LINE_ID
2.  OE_ORDER_LINES_ALL.ORIG_SYS_DOCUMENT_REF=      
      po_requisition_HEADERs_all.SEGMENT1(Requisition Number)
3.  OE_ORDER_HEADERS_ALL.ORIG_SYS_DOCUMENT_REF=        
      po_requisition_HEADERs_all.SEGMENT1(Requisition number)

Script to Delete Values in Value Set

DECLARE
   l_err_msg   VARCHAR2 (500) := NULL;

   CURSOR flex
   IS
      SELECT ffv.flex_value_id, ffv.flex_value
        FROM fnd_flex_value_sets ffvs,
             fnd_flex_values ffv,
             fnd_flex_values_tl ffvt
       WHERE flex_value_set_name = 'XXTAC_SITE_NOMENCLATURE_MAPPING'
         AND ffv.flex_value_set_id = ffvs.flex_value_set_id
         AND ffvt.flex_value_id = ffv.flex_value_id
         AND ffvs.flex_value_set_id = ffv.flex_value_set_id
         AND ffvt.flex_value_meaning = 'Mrp2 (FVB)';
BEGIN
   FOR i IN flex
   LOOP
      fnd_flex_values_pkg.delete_row (i.flex_value_id);
      COMMIT;
      DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted');
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('in Exception ' || l_err_msg);
END;

SQL Query for Distrubiton Acocunt - Order Management Shipping

SELECT   *
    FROM (SELECT ood.organization_code,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5
                 || '.'
                 || gcc.segment6
                 || '.'
                 || gcc.segment7
                 || '.'
                 || gcc.segment8 distrubiton_acocunt
            FROM wsh_shipping_parameters wsp,
                 org_organization_definitions ood,
                 gl_code_combinations gcc
           WHERE wsp.organization_id = ood.organization_id
             AND wsp.goods_dispatched_account = gcc.code_combination_id) sub1
ORDER BY 2 ASC

How to Change Activity Center Account/Good Dispatched Account

How to Change Activity Center Account/Good Dispatched Account

If business don't wish to use Goods Dispatched Account, you can return to the Shipping Parameters form and NULL it out after verifying that Interface Trip Stop is successful
Navigate to-->Order Management Super User--> Shipping > Setup > Shipping Parameters
Shipping Transaction tab > enter the COGS account in the field: Enter correct Goods Dispatched Account (OR) NULL
Run Interface Trip Stop again and verify that the error no longer occurs.

Wf_Engine_Util.Function_Call(PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES, POAPPRV, 829745-306510, XXXXX, RUN)

SYMPTOMS

When attempting to approve a revised blanket purchase agreement the documents gets stuck in process, because an exception occurs during the following activity:

PO Approval Top Process/Get PO Attributes

Further the following Errored Activities are shown:

Activity Result Error Name
--------------------------------------------- --------------- ------------------------------
Error Message
----------------------------------------------------------------------------------------------------
Error Stack
----------------------------------------------------------------------------------------------------
Get PO Attributes #EXCEPTION WFENG_ITEM_ATTR
3103: Attribute 'DOCUMENT_ID' does not exist for item 'POAPPRV/829745-306510'.

PO_POAPPROVAL_INIT1.SetPOHdrAttributes(SetPOHdrAttributes: 02. Values= PA)
PO_POAPPROVAL_INIT1.GetPOAttributes(PO_POAPPROVAL_INIT1.GetPOAttributes: 02)
PO_POAPPROVAL_INIT1.Get_PO_Attributes(PO_POAPPROVAL_INIT1.Get_PO_Attributes: 01)
Wf_Engine.GetItemAttrNumber(POAPPRV, 829745-306510, DOCUMENT_ID)
Wf_Engine_Util.Function_Call(PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES, POAPPRV, 829745-306510, 177419, RUN)

Failed Activity  Get PO Attributes

Activity Type  Function

Error Name  -6512

Error Message  ORA-06512: 在 line 

Error Stack  Wf_Engine_Util.Function_Call(PO_POAPPROVAL_INIT1.GET_PO_ATTRIBUTES, POAPPRV, 49510-522507, 170247, RUN)

CAUSE

One or more releases are created against the blanket purchase agreement containing one or more distributions for which the ordered quantity is zero. This can be determined by executing the following query:

SELECT pha.segment1 "PO Nr"
, pra.release_num "Release Nr", pll.shipment_num "Shipment Nr"
, pda.distribution_num "Distribution Nr"
, pda.QUANTITY_ORDERED "QTY Ordered"
FROM po_headers_all pha
, po_lines_all pla
, po_distributions_all pda
, po_line_locations_all pll
, po_releases_all pra
WHERE pra.po_release_id = pll.po_release_id
AND pll.line_location_id = pda.line_location_id
AND pda.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND pha.segment1 = '&PO_NUM';

SOLUTION

To implement the solution, please correct or delete the distributions with quantity zero. In order to do so, please take the following steps:

First reset the document from 'in process' status to either incomplete or requires reapproval (either is fine) using the reset script as provided in poresrel.sql  

These scripts allow the user to resubmit the document for approval by resetting the authorization status when documents are stuck in 'In-Process' or 'Pre-Approved' statuses. The scripts are:

    poxrespo.sql - to reset Standard, Blanket, Planned and Contract purchase orders

    poresrel.sql - to reset Blanket and scheduled releases

    poresreq.sql - to reset Internal and purchase requisitions

In order to obtain the scripts apply the proper patch.  The scripts will be available in the $PO_TOP/sql directory.

Then proceed with these next steps:

1. Navigate to Purchase Orders > Releases
2. Query up the involved blanket purchase agreement and release
3. Select the involved shipment
4. Choose button Distributions
5. Delete the distribution with the quantity zero
6. Save the changes
7. Retry the workflow. In order to do so, the following steps can be performed:

- Determine the wf_item_type and wf_item_key values from the involved BPA by executing the following query:

select segment1, wf_item_type, wf_item_key, authorization_status, org_id
from po_headers_all
where segment1 = '&po_nr'
and org_id = &org_id;

- Use the returned item type and item key to retry the workflow processes, by running the following command connected as APPS user:

SQL> exec wf_engine.startprocess('&WF_ITEM_TYPE','&WF_ITEM_KEY');

Instructions to Run Script

The script prompts the user for the following parameters :

    Document number.  Enter the document number in question.

    Organization id.  The organization id that the document belongs to. (Important:   This prompt should never be entered with a null value as the organization id is never null at the table level and must be entered or else the query to find the document to reset will not find the document).   Use these scripts to identify the organization id:

        Requisition:

        SQL > select hr.name, prh.segment1, prh.org_id

        from po_requisition_headers_all prh,

        hr_all_organization_units hr

        where prh.org_id = hr.organization_id and

        prh.segment1 = '&Enter_Req_Number';

        Purchase Order:

        SQL > select hr.name, poh.segment1, poh.org_id

        from po_headers_all poh,

        hr_all_organization_units hr

        where poh.org_id = hr.organization_id and

        poh.segment1 = '&Enter_PO_Number';

        Purchase Order Release:

        SQL > select hr.name, poh.segment1, por.release_num, por.org_id

        from po_headers_all poh,

        po_releases_all por,

        hr_all_organization_units hr

        where poh.org_id = hr.organization_id and

        por.org_id = poh.org_id and

        poh.po_header_id = por.po_header_id and

        poh.segment1 = '&Enter_PO_Number' and

        por.release_num = '&Enter_Release_Num';

    Do you want to delete the action history since the last approval?

The standard practice is to choose No (N).

With poxrespo.sql and poresrel.sql an option to delete or update the action history since the last submission of the document for approval.

On selecting update, if there is an action pending on a particular user, the scripts will update the action code as 'No Action Taken'.

If the action history does not contain a record with pending user action, the update action will have no effect on the action history.

On selecting the delete option, the action history for the unsuccessful approval cycle would be deleted.

    For example.: for Standard PO Number 5211

    Revision Sequence Action Employee

    0   1 Submit    A

    0   2 forward   A

    0   3 Approve   B

    0   4 Reserve   B

    1   5 Submit    A

    1   6 forward   A

    1   7

    

Here the document is stuck after 1 successful approval for Revision 0.

Hence if you choose the Delete action for action history, the sequence number 5,6,7 will be deleted and the document would be set to Requires reapproval.

If you choose the Update option for this case then the action at sequence 7 would be updated to 'No Action Taken' and the note field would indicate 'Updated by reset script on '.

Note: If the option to delete the Action History is taken and a commit is issued at the end of the script, there is no possibility to restore the deleted action history

Oracle UserGuide Link

http://docs.oracle.com/cd/E18727_01/index.htm

Java 6 Download

Problem: You have access to a responsibility in Oracle, but the page just does not seem to load or you get an error
Issue: You have an incorrect version of Java running on your machine.

For Oracle EBS, we recommend Java 6 Update 31 to be installed on your machine.

To check to see which versions of Java are currently on your machine and uninstall the incorrect versions:
1)    Open the control panel (Start > Control Panel)
2)    Click Add/Remove Programs or Programs/Features
3)    Wait for the list of programs to populate (should take a few seconds)
4)    Scroll down until you see Java (if you do not see any version of Java, proceed to the step titled, How to install Java 6 Update 31 on your machine.
5)    Check to see if you have Java(TM) 6 Update 31 by publisher Oracle version 6.0.310 in this list.
6)    If you have other versions of Java (example Java(TM) 6 Update 35), uninstall those (not Java(TM) 6 Update 31.
7)    If Java(TM) 6 Update 31 is not listed, but others are uninstall those so there is no version of Java on your machine.
How to install Java 6 Update 31 on your machine:
1)    Open Internet Explorer
2)    Type http://www.oldapps.com/java.php?old_java=7272?download in the address bar.
3)    A prompt should appear at the bottom of the screen that says “Do you want to run or save jre-6u31-windows-i586-s.exe (16.4 MB) from download.oldapps.com?   This type of file could harm your computer.
4)    Click Run
5)    Wait for the program to download.
6)    Close all browsers (Internet Explorer, Firefox, Chrome) before continuing
7)    A box titled “Java Setup - Welcome” should appear.
8)    Click Install >
9)    Wait for the installer to finish and click Close.
10)    To verify successful installation, follow the steps in the previous section.
How to configure Java 6 Update 31 on your machine:
1)    Open Control Panel
2)    Double-Click Java (32-bit)
3)    Click the Advanced tab
4)    Choose the + next to Java Plug-in
5)    Ensure this is unchecked
6)    Click the + next to Security (not the Security tab)
7)    Click the + next to General
8)    Ensure the last 3 items are checked
9)    Press OK.
Test in Oracle to verify that it is working.

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

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.