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