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';