Oracle Work in Process – Discrete Job Interface (WIP Mass Load) Common Errors

The following are some of the general errors we will be getting while running the WIP Mass Load

1. Invalid or missing WIP Accounting Class

ANS): Set a default WIP accounting class in the WIP parameters for the organization.

2. Invalid value for Scheduling Method

ANS): Normally you will get this kind of error when the ALLOW_EXPLOSION is set to No and no value was assigned to SECHDULING_METHOD. Routing based scheduling cannot be done if BOM explosion is not allowed. Please change the scheduling method to manual, and provide start and completion dates, or set the ALLOW_EXPLOSION to Y.

3. System cannot add or change operation because requested department_id does not exist in bom_departments table.

ANS): We will get this kind of error in case we missed to provide department information while adding the operation or the department we have provided is not in the same organization.

4. Invalid resource. Check Bom_Resources table. Resource does not exist in Bom_resources table.

ANS): We will get this kind of error in case the resource you are adding is not in the operation department. You need to make sure that the resource is already attached to the department which is available for the operation.

5. System cannot add resource or material because the requested operation_seq_num does not exist

ANS): You will be getting this error incase if you are trying to enter the resource/component to an operations which is not exists against the discrete job.

6. System cannot add or change material requirement because requested job/operation_seq_num /inventory_item_id_new combo has already existed

ANS): If a particular component is already exists and you are trying to add same component is again using detail interface, you will be getting this error. You should be careful enough, such that you should not add a component with the job, operation_seq_num, and inventory_item_id_new combination if the same is already exists in the discrete job.

7. Cannot read value for profile option RPM_CONC_PROCESS_ID in routine &ROUTINE.

ANS): There are multiple possibilities for getting such errors. Make sure that all the following setups are place a. When updating a job through interface by inserting rows in the table WIP_JOB_DTLS_INTERFACE ensures that there is a corresponding operation_seq_num in WIP_OPERATIONS. When updating the components of the current job, the operation_seq_num should exist for that job in the table WIP_OPERATIONS. b. Please make sure that you have done proper WIP setup c. Check whether the current accounting period is open or not. If not please open the accounting periods where the job dates falls. The navigation for checking the accounting period status is Inventory - Accounting Close Cycle - Inventory Period

8. Exiting with warning! calling mrepgrm_end_program 1

ANS): There are multiple possibilities for getting this type of errors. Make sure that all the following precautions were taken care. a. Please check the profile option INV: Dynamic Precision Option for Quantity on Reports to a valid value at site level. This profile should not have null value. b. If you are releasing the planned order from ASCP, please check whether the default job class is set or not. You can use the following steps for achieving this. ==>Navigate: Adv Supply Chain Planner -> Supply Chain Plan -> Workbench ==>Once the organization is selected, go Tools > Preferences ==>Set a Default Job Class c. Check whether the accounting period is open or not

Interface Trip Stop Functionality

There are typically four interface program get triggered at the time of ship confirm. They are
Interface Trip stop
Packing Slip Report
Bill of Lading
Invoice
Interface Trip Stop is one of the main interface programs which can be triggered at the time of ship confirm or later as Interface Trip stop - SRS concurrent request.

Interface trip stop has two parts
Order Management Interface (First part)
Inventory Interface (Second part)
Order Management Interface part update the order management tables like OE_ORDER_LINES_All (Update Shipped_Quantity, Shipping_Quantity, Actual_Shipment_Date) and also WSH_DELIVERY_DETAILS (make Released_status, OE_INTERFACED_FLAG = ‘Y’) table.

The Inv_Interfaced_Flag remains = X (Pending) or N which will changes to Y after execution of second part

The second part i.e. Inventory Interface will be triggered only if the value of OE_INTERFACED_FLAG = ‘Y’ otherwise second part will not be triggered. Even if you try to run this from SRS, these lines will not be picked for processing.

Inventory Interface part move data from MTL_TRANSACTIONS_INTERFACE to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction. Also data is deleted from MTL_DEMAND, MTL_RESERVATIONS and WSH_NEW_DELIVERIES. Item quantity reduced from MTL_ONHAND_QUANTITIES and
MTL_TRANSACTION_ACCOUNTS is updated with accounting information.


Both these parts are executed simultaneously.

“Inventory transaction worker” Program

Open Transaction Interface supports following type of transactions:

·         Inventory issues and receipts (including user-defined transaction types)

·         Subinventory transfers

·         Direct inter-organization transfers

·         In transit Shipments

·         WIP component issues and returns

·         WIP assembly completions and returns

·         Sales Order shipments

·         Inventory Average Cost Updates

·         With a miscellaneous transaction users can issue material to or receive material from general ledger accounts in your current organization

What is the difference between "Transaction Open Interface" and "Pending Transactions"

· Navigate Inventory Responsibility -> Transactions -> Transaction Open Interface

This form queries the MTL_TRANSACTIONS_INTERFACE table

· Navigate Inventory Responsibility -> Transactions -> Pending Transactions

This form queries the MTL_MATERIAL_TRANSACTIONS_TEMP table

Q. What is the MTL_TRANSACTIONS_INTERFACE table used for?

A. This is the Interface between non-Inventory applications and the Inventory Transactions module.

In other words any other module other than Inventory that wants to update Inventory has to come through this table. Modules such as WIP (Work In Progress) and OE (Order Entry) first pass their records to the MTL_TRANSACTIONS_INTERFACE (MTI) for validation.

The Transactions Manager (INCTCM) reads records from the MTI table, validates them and moves the successful transactions into the MTL_MATERIAL_TRANSACTIONS_TEMP table, and submits Transaction workers (sub-processes - INCTCW) which then process these records through inventory. This process consists of data derivation, validation, and the transfer of records from MTL_TRANSCTIONS_INTERFACE, MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE into their associated TEMP (temporary) tables from where the transactions processor processes them.

Q. What is the MTL_MATERIAL_TRANSACTIONS_TEMP table used for?

A. The transactions are passed to the MTL_MATERIAL_TRANSACTONS_TEMP table after initial validation by the Transactions Manager (INCTCM) from the MTL_TRANSACTIONS_INTERFACE. A job id is attached and a Transactions Worker (INCTCW) is submitted by the INCTCM process to get the records processed and moved to the MTL_MATERIAL_TRANSACTONS table.

This table is also used by the Inventory and Purchasing modules which write transactions directly into this table after validation.

Inventory Module Forms like the Miscellaneous transactions form write directly into this table. The transactions which are created through this form are via on-line processing. It is from this table that the inventory quantities finally get updated and serial/lot numbers get updated.

Navigation Inventory:Transactions >Transaction Open Interface

Click in the check box under the "Submit" column then Save

Script to Push Performance Appraisal back to Manager

Use below queries to push performance appraisal back to managers

SELECT pap1.full_name appraiser,
  Pa.Appraisal_Id,
  Pap.Full_Name,
  Pa.Appraisal_System_Status,
  Pa.Appraiser_Person_Id,
  Pa.Appraisee_Person_Id,
  Pa.Appraisal_Template_Id,
  Pat.Name,
  Pa.Appraisal_Period_Start_Date,
  Pa.Appraisal_Period_End_Date
FROM Per_Appraisals Pa,
  Per_All_People_F Pap ,
  Per_Appraisal_Templates Pat,
  per_all_people_f pap1
WHERE Pa.Appraiser_Person_Id=20780
  --and appraisee_person_id=27405
AND Pap.Person_Id            =Pa.Appraisee_Person_Id
AND Pap1.Person_Id           =Pa.Appraiser_Person_Id
AND Pat.Appraisal_Template_Id=Pa.Appraisal_Template_Id
AND TRUNC(Sysdate) BETWEEN TRUNC(Pap.Effective_Start_Date) AND TRUNC(Pap.Effective_End_Date)
AND TRUNC(Sysdate) BETWEEN TRUNC(Pap1.Effective_Start_Date) AND TRUNC(Pap1.Effective_End_Date)
ORDER BY Pap.Full_Name;

UPDATE per_appraisals pa
SET system_params         = SUBSTR(system_params,1,instr(pa.system_params,'pItemKey=')-2),
  Appraisal_System_Status = 'ONGOING'
WHERE Pa.Appraisal_Id    IN (330409,330396);

Oracle Inventory Subinventories and Locators Table Links

script to see who has locked the workplan

You can run the following script .

select locked_by_person_id from pa_proj_elem_ver_structure ps,pa_projects_all ppa,fnd_user fu
where ps.lock_status_code='LOCKED'


If you know the project than you can run the following

select locked_by_person_id from pa_proj_elem_ver_structure ps,pa_projects_all ppa,fnd_user fu
where ps.lock_status_code='LOCKED' and  ppa.project_id = &enterprojectid;

Using the Project Super User responsibility Workplan tab | Maintain Versions there should be a icon of a lock on that page ('Change Control' icon). Please hit the lock icon.  This allows other users to access the workplan.

SQL Queries for AP Payment Process Request (PPR) Process

a. Payment process request:

SELECT *
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&&checkrun_name';

b. AP Payment Batch

select *
from AP_INV_SELECTION_CRITERIA_ALL
where checkrun_name = '&checkrun_name';

c. Payment Instructions

select pi.*
from IBY_PAY_INSTRUCTIONS_ALL pi
where pi.payment_instruction_id in
(SELECT payment_instruction_id
FROM iby_payments_all
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name'));

d. Payments

SELECT *
FROM iby_payments_all
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name');

e. Payment Documents

select *
from IBY_DOCS_PAYABLE_ALL
where PAYMENT_SERVICE_REQUEST_ID in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name');

f. Bank Account Payment Document

select *
from ce_payment_documents
where payment_document_id in
(select pi.payment_document_id
from IBY_PAY_INSTRUCTIONS_ALL pi
where pi.payment_instruction_id in
(SELECT payment_instruction_id
FROM iby_payments_all
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name')));

g. Payment schedules

select *
from ap_payment_schedules_all
where invoice_id in
(select calling_app_doc_unique_ref2
from IBY_DOCS_PAYABLE_ALL
where PAYMENT_SERVICE_REQUEST_ID in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name'));

h. Checks

SELECT c.*
FROM ap_checks_all c
WHERE c.payment_id in
(SELECT ip.payment_id
FROM iby_payments_all ip
WHERE payment_service_request_id in
(SELECT payment_service_request_id
FROM iby_pay_service_requests
WHERE calling_app_id = 200
AND call_app_pay_service_req_code = '&checkrun_name'));

i. AP payment batch temp data for invoices

select *
from AP_SELECTED_INVOICES_ALL
where checkrun_name = '&checkrun_name';

j. AP payment batch temp data for checks

select *
from AP_SELECTED_INVOICE_CHECKS_ALL
where checkrun_name = '&checkrun_name';

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