For a covered product, one first relates the contract to the install base and then the install base to the item. In such a case, the column OBJECT1_ID1 will refer to the install base id. The item id on the install base record then relates to the inventory record.
Contract modifier can be used as you can have more than one contract with the same contract number. The combo of number/modifier has to be unique, so if you use the combo you know you get 1 contract. SELECT kl.line_number, ks.NAME, i.segment1 FROM okc_k_headers_b kh, okc_k_lines_b kl, okc_k_items ki, okc_line_styles_v ks, csi_item_instances c, mtl_system_items_b i WHERE kh.contract_number_modifier IS NULL AND kh.ID = kl.dnz_chr_id AND kh.ID = ki.dnz_chr_id AND kl.ID = ki.cle_id AND kl.lse_id = ks.ID AND ki.jtot_object1_code IN ('OKX_CUSTPROD') AND c.last_vld_organization_id = i.organization_id AND TO_NUMBER(ki.object1_id1) = c.instance_id AND c.inventory_item_id = i.inventory_item_id |
We usually come across this scenario where Item is shipped in Oracle Order Management and the order line is closed but IB is not created.
Below listed are possible checks that can be done to debug the reasons for this.
1. Item Setup
Is your item set up as IB trackable?
An item needs to be set up as IB trackable in Master and every child inventory org in order to interface the item's transaction to Oracle Installed Base.
Check from form:
INVENTORY (R) > Item > Organization Item > Service Tab > Attribute: Installed Base Trackable
Check using query:
SELECT segment1, organization_id, comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE segment1 = '&your_item_number';
2. Validate Shipment
Does your Order Management shipment go through the inventory interface for a shippable item?
Although your order line is closed, the inventory interface (kicked off from ship confirm trip stop interface) may not complete. Only after the inventory interface
is completed will the transaction be sent to the Installed Base interface.
Check from form
INVENTORY -> Transactions -> Material Transactions
Pick up your shipping organization
Query by Source = Sales Order, Source Id = Sales Order Number
You will see your Order Issue transaction here if your inventory interface is completed.
Write down transaction_id for use in upcoming steps.
Check using query:
SELECT transaction_id
FROM mtl_material_transactions
WHERE trx_source_line_id = &order_line_id
AND transaction_type_id = 33;
If you don't see your transaction, please check why the transaction is pending in the inventory interface process.
Check from form:
INVENTORY (R) > Transactions >Transaction Open Interface
INVENTORY (R) >Transactions > Pending Transactions
Check using query:
SELECT process_flag, error_code, error_explanation
FROM mtl_transactions_interface
WHERE trx_source_line_id = &order_line_id
AND transaction_type_id = 33;
SELECT process_flag, error_code, error_explanation
FROM mtl_material_transactions_temp
WHERE trx_source_line_id = &order_line_id
AND transaction_type_id = 33;
3. Message in Event Queue
For a shippable item, does the transaction from inventory publish a message to the Event queue?
After completing the above three steps, your OM shipment transaction should publish (send) a message to the Service Fulfillment Manager Event queue.
Check using query:
SELECT msg_code, msg_status, msg_creation_date, body_text
FROM xnp_msgs
WHERE dbms_lob.instr(body_text, '&transaction_id') <> 0
[****&transaction_id is inventory transaction_id for sales order issue and the one you get from step 2]
If the above query returns no row, then your message has not been published.
To find out why the message is not published, set up the CSI debug option through the following Profile options
CSI: Debug Level = 10
CSE: Debug = Y
CSI: Log File Path = < a valid value from 'utl_file_dir' parameter> ;
Do one more OM shipment transaction, and review the csi debug file called csiinv.hook.dbg. This file will tell you why the message failed to publish to
SFM Event queue.
If the transaction for some reason did not publish in SFM, you can use following script to republish it.
declare
l_header_id number;
l_mtl_txn_id number := &Transaction_ID;
l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
begin
csi_inv_txn_hook_pkg.postTransaction(
p_header_id => l_header_id,
p_transaction_id =>l_mtl_txn_id,
x_return_status =>l_return_status);
end;
/
4. Check SFM
Is the SFM Event Manager Queue Service up and running?
If your message is published to SFM and your SFM Event Manager Queue Service is up and running, the transaction will go to Install Base unless the message failed
when being processed.
Check from form to make sure SFM is running
SFM System Administrator(R) > Administration > Queue Console > Services
You should see your SFM Event Manager Queue Service has 1 in both Actual and Target. If not, your SFM is not up and running.
Check using query to make sure message is not failed
SELECT msg_code, msg_status, msg_creation_date, body_text
FROM xnp_msgs
WHERE dbms_lob.instr(body_text, 'MTL_TRANSACTION_ID') <> 0
AND dbms_lob.instr(body_text, '&transaction_id') <> 0
(&transaction_id is the one you get from step 2)
5. Transaction in SFM
Did the transaction go through SFM sucessfully?
If your SFM is up and running, but the transaction has failed in SFM.
Query results returned from step 4 will have a msg_status = 'FAILED' or 'REJECT'.
To reprocess an individual erred message, go to your Notification Inbox:
Use SFM System Administrator Responsibility:
Nav: Operations -> Notifications
- Click on 'View Details'
- Review and resolve the error
- Click on 'Respond'
- Select Action 'Retry Messages'
- Click OK
The message should now disappear from your Notification Inbox and will have a status of 'READY'. These messages will be re-queued to be processed by the SFM Event Manager Queue
To reprocess all failed messages, you can run the concurrent program
Use SFM System Administrator Responsibility:
Nav: Concurrent -> Run Requests > XDP Resubmit Failed Messages
If the message still fails after resubmition, report to Oracle Support with the detailed error message in body_text column from step 4 query
6. Transaction error in Installed Base Interface
If the transaction goes through SFM without a problem, and you still do not
see the Item Instance updated in Install Base, check if the transaction erred in
the Transactions Error Processing form.
Check from form:
Oracle Installed Base Admin(R) > Transaction errors Re-processing
Check using query:
SELECT processed_flag, error_text
FROM csi_txn_errors
WHERE inv_material_transaction_id = &transaction_id
(&transaction_id is the one you get from step 2)
7. Check Invalid CSI objects
If you have any errors in step 6 and 7, check if you have any invalid 'CSI' objects.
If yes, recompile them and resubmit the error transaction.
Mark error transactions to be processed by setting Process Flag and Submit the program, Resubmit Interface Process concurrent program with 'Selected' as parameter.
Below listed are possible checks that can be done to debug the reasons for this.
1. Item Setup
Is your item set up as IB trackable?
An item needs to be set up as IB trackable in Master and every child inventory org in order to interface the item's transaction to Oracle Installed Base.
Check from form:
INVENTORY (R) > Item > Organization Item > Service Tab > Attribute: Installed Base Trackable
Check using query:
SELECT segment1, organization_id, comms_nl_trackable_flag
FROM mtl_system_items_b
WHERE segment1 = '&your_item_number';
2. Validate Shipment
Does your Order Management shipment go through the inventory interface for a shippable item?
Although your order line is closed, the inventory interface (kicked off from ship confirm trip stop interface) may not complete. Only after the inventory interface
is completed will the transaction be sent to the Installed Base interface.
Check from form
INVENTORY -> Transactions -> Material Transactions
Pick up your shipping organization
Query by Source = Sales Order, Source Id = Sales Order Number
You will see your Order Issue transaction here if your inventory interface is completed.
Write down transaction_id for use in upcoming steps.
Check using query:
SELECT transaction_id
FROM mtl_material_transactions
WHERE trx_source_line_id = &order_line_id
AND transaction_type_id = 33;
If you don't see your transaction, please check why the transaction is pending in the inventory interface process.
Check from form:
INVENTORY (R) > Transactions >Transaction Open Interface
INVENTORY (R) >Transactions > Pending Transactions
Check using query:
SELECT process_flag, error_code, error_explanation
FROM mtl_transactions_interface
WHERE trx_source_line_id = &order_line_id
AND transaction_type_id = 33;
SELECT process_flag, error_code, error_explanation
FROM mtl_material_transactions_temp
WHERE trx_source_line_id = &order_line_id
AND transaction_type_id = 33;
3. Message in Event Queue
For a shippable item, does the transaction from inventory publish a message to the Event queue?
After completing the above three steps, your OM shipment transaction should publish (send) a message to the Service Fulfillment Manager Event queue.
Check using query:
SELECT msg_code, msg_status, msg_creation_date, body_text
FROM xnp_msgs
WHERE dbms_lob.instr(body_text, '&transaction_id') <> 0
[****&transaction_id is inventory transaction_id for sales order issue and the one you get from step 2]
If the above query returns no row, then your message has not been published.
To find out why the message is not published, set up the CSI debug option through the following Profile options
CSI: Debug Level = 10
CSE: Debug = Y
CSI: Log File Path = < a valid value from 'utl_file_dir' parameter> ;
Do one more OM shipment transaction, and review the csi debug file called csiinv.hook.dbg. This file will tell you why the message failed to publish to
SFM Event queue.
If the transaction for some reason did not publish in SFM, you can use following script to republish it.
declare
l_header_id number;
l_mtl_txn_id number := &Transaction_ID;
l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
begin
csi_inv_txn_hook_pkg.postTransaction(
p_header_id => l_header_id,
p_transaction_id =>l_mtl_txn_id,
x_return_status =>l_return_status);
end;
/
4. Check SFM
Is the SFM Event Manager Queue Service up and running?
If your message is published to SFM and your SFM Event Manager Queue Service is up and running, the transaction will go to Install Base unless the message failed
when being processed.
Check from form to make sure SFM is running
SFM System Administrator(R) > Administration > Queue Console > Services
You should see your SFM Event Manager Queue Service has 1 in both Actual and Target. If not, your SFM is not up and running.
Check using query to make sure message is not failed
SELECT msg_code, msg_status, msg_creation_date, body_text
FROM xnp_msgs
WHERE dbms_lob.instr(body_text, 'MTL_TRANSACTION_ID') <> 0
AND dbms_lob.instr(body_text, '&transaction_id') <> 0
(&transaction_id is the one you get from step 2)
5. Transaction in SFM
Did the transaction go through SFM sucessfully?
If your SFM is up and running, but the transaction has failed in SFM.
Query results returned from step 4 will have a msg_status = 'FAILED' or 'REJECT'.
To reprocess an individual erred message, go to your Notification Inbox:
Use SFM System Administrator Responsibility:
Nav: Operations -> Notifications
- Click on 'View Details'
- Review and resolve the error
- Click on 'Respond'
- Select Action 'Retry Messages'
- Click OK
The message should now disappear from your Notification Inbox and will have a status of 'READY'. These messages will be re-queued to be processed by the SFM Event Manager Queue
To reprocess all failed messages, you can run the concurrent program
Use SFM System Administrator Responsibility:
Nav: Concurrent -> Run Requests > XDP Resubmit Failed Messages
If the message still fails after resubmition, report to Oracle Support with the detailed error message in body_text column from step 4 query
6. Transaction error in Installed Base Interface
If the transaction goes through SFM without a problem, and you still do not
see the Item Instance updated in Install Base, check if the transaction erred in
the Transactions Error Processing form.
Check from form:
Oracle Installed Base Admin(R) > Transaction errors Re-processing
Check using query:
SELECT processed_flag, error_text
FROM csi_txn_errors
WHERE inv_material_transaction_id = &transaction_id
(&transaction_id is the one you get from step 2)
7. Check Invalid CSI objects
If you have any errors in step 6 and 7, check if you have any invalid 'CSI' objects.
If yes, recompile them and resubmit the error transaction.
Mark error transactions to be processed by setting Process Flag and Submit the program, Resubmit Interface Process concurrent program with 'Selected' as parameter.
References:
http://arunmudragada.weebly.com/home/item-is-shipped-in-oracle-order-management-but-ib-is-not-created