Component Backflush with Oracle MTL Transaction Interface Table
Recently, at work, we noticed the Work Order-less completions inserted into our MTL_TRANSACTIONS_INTERFACE table were not spawning component backflush transactions. It was quite the mystery to us, as they had been working in Oracle 11i (11.5.10). At some point during our migration to R12 (12.1.3) the backflush transactions had stopped. We searched Oracle’s documentation, support knowledge base, and all over the internet. We couldn’t find a solution.
One of my colleagues got in touch with someone she used to work with. They were able to provide a private API: APPS.wip_flowUtil_priv. My colleague tried the API call explodeRequirementsToMTI and was able to get the components to backflush. We thought all was well.
However, upon further testing, I noticed something strange. The original Assembly was getting stuck in the interface. And when resubmitted, I saw both the assembly and another round of components getting transacted. What was going on?
Further testing provided me with the answer: the originally inserted assembly record was getting slightly altered by the material transaction worker before getting marked in error. Altered to a point which would spawn component backflush transactions if marked for processing and then processed. I tried inserting a fresh assembly record into the MTL_TRANSACTIONS_INTERFACE table with the updated fields and was able to get the components to backflush, automatically, without any errors.
We now have a functioning R12 Work Order-less completion interface which inserts assembly completion records into the MTL_TRANSACTIONS_INTERFACE table. Records which are correctly formed and spawn component issue (backflush) transactions when processed by the material transaction manager (Process transaction interface).
These are the two fields that were changed by the transaction manager:
OPERATION_SEQ_NUM from -1 to NULL
TRANSACTION_BATCH_SEQ from NULL to 1
Below is the insert statement we are using, with Oracle 12.1.3 changes noted.
INSERT INTO MTL_TRANSACTIONS_INTERFACE(
TRANSACTION_INTERFACE_ID,
TRANSACTION_HEADER_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
VALIDATION_REQUIRED,
TRANSACTION_MODE,
LOCK_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
INVENTORY_ITEM_ID,
ITEM_SEGMENT1,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_REFERENCE,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
ERROR_EXPLANATION,
ERROR_CODE,
ATTRIBUTE10,
ATTRIBUTE_CATEGORY,
WIP_ENTITY_TYPE,
OPERATION_SEQ_NUM, –****CHANGED****
TRANSACTION_BATCH_SEQ, –****CHANGED****
BOM_REVISION_DATE,
ROUTING_REVISION_DATE,
SCHEDULE_NUMBER,
SCHEDULED_FLAG,
FLOW_SCHEDULE)
VALUES(
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, –TRANSACTION_INTERFACE_ID
MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, –TRANSACTION_HEADER_ID
‘BACKFLUSH TEST’, –SOURCE_CODE — Reference to legacy/interface data
–99999, — SOURCE_LINE_ID — Reference to legacy/interface data
–99999, — SOURCE_HEADER_ID — Reference to legacy/interface data
1, –PROCESS_FLAG
1, –VALIDATION_REQUIRED — 1=FULL
3, –TRANSACTION_MODE — 2=Concurrent, 3=Background
2, –LOCK_FLAG
sysdate, –LAST_UPDATE_DATE
1001, –LAST_UPDATED_BY
sysdate, –CREATION_DATE
1001, –CREATED_BY
1000001, –LAST_UPDATE_LOGIN
NULL,— REQUEST_ID
NULL,— PROGRAM_APPLICATION_ID
NULL,— PROGRAM_ID
SYSDATE,— PROGRAM_UPDATE_DATE
100001, –INVENTORY_ITEM_ID
‘ITEM_NUMBER’, –ITEM_SEGMENT1
100, –ORGANIZATION_ID
10, –TRANSACTION_QUANTITY
10, –PRIMARY_QUANTITY
‘EA’, –TRANSACTION_UOM
SYSDATE,— TRANSACTION_DATE
‘FG’, –SUBINVENTORY_CODE
10001, — LOCATOR_ID
”, –TRANSACTION_SOURCE_ID – NULL as Work Order-less
5, –TRANSACTION_SOURCE_TYPE_ID
31, –TRANSACTION_ACTION_ID
44, –TRANSACTION_TYPE_ID
‘TEST TRANSACTION ON ‘|| SYSDATE, — TRANSACTION_REFERENCE
NULL,— TRANSFER_SUBINVENTORY
NULL,— TRANSFER_ORGANIZATION
NULL,— ERROR_EXPLANATION
NULL,— ERROR_CODE
‘TEST ATTRIBUTE’, –ATTRIBUTE10
‘ORG’,— ATTRIBUTE_CATEGORY
4, –WIP_ENTITY_TYPE
NULL,— OPERATION_SEQ_NUM ****(Was:-1)****
1,— TRANSACTION_BATCH_SEQ****(Was: NULL)****
SYSDATE,— BOM_REVISION_DATE
SYSDATE,— ROUTING_REVISION_DATE
WIP_JOB_NUMBER_S.nextval, — SCHEDULE_NUMBER
2, –SCHEDULED_FLAG
‘Y’ – FLOW_SCHEDULE);
Hi,
Could you please tell me how to call the API wip_flowUtil_priv.explodeRequirementsToMTI .
I tried the below and getting the below error in the Parent table.
wip_flowUtil_priv.explodeRequirementsToMTI
( p_txnHeaderID =>30418102, —:completions.transaction_header_id,
p_parentID => 721816864 , —:completions.transaction_interface_id,
p_txnTypeID =>35, —-:completions.transaction_type_id,
p_assyID => 797403, —:completions.inventory_item_id,
p_orgID =>9923, —:completions.organization_id,
p_qty => -10, —:completions.primary_quantity*(sign(:completions.transaction_quantity)),
p_altBomDesig => NULL, —:completions.alternate_bom_designator,
p_altOption => 2,
p_bomRevDate => SYSDATE, —:completions.Bom_Revision_Date,
p_txnDate => sysdate, —:completions.transaction_date,
p_projectID => NULL, —:completions.project_id,
p_taskID => NULL, —:completions.task_id,
p_toOpSeqNum => NULL, —:completions.operation_seq_num,
p_altRoutDesig =>NULL, — :completions.alternate_routing_designator,
p_txnMode => NULL, —-:parameter.transaction_mode,
p_txnSourceID =>NULL, — :completions.transaction_source_id,
p_acctPeriodID => NULL, —:completions.acct_period_id,
p_cplTxnID => NULL, —:completions.completion_transaction_id,
p_txnBatchID => NULL, —:completions.transaction_batch_id,
p_txnBatchSeq =>NULL, — :completions.transaction_batch_seq,
p_unitNumber =>NULL, –:completions.END_ITEM_UNIT_NUMBER,
p_defaultPushSubinv => NULL,
x_returnStatus => l_returnStatus,
x_nontxn_excluded => l_nontxn_excluded);
Regards,
Subir
I’m not familiar with that API. But I see the quantity you are passing is negative. Is that intentional?
Yes , that is intentional , as same result with Postive quantity. Was trying every possibilities. The actual trick was to call the API with correct transaction mode. (p_txnMode).
It’s working now . 🙂
Thanks ,
Subir
Hi Subir..What is correct value of transaction mode ? I facing costing issues with p_txnMode = 3.
Thanks.
The Transaction mode is likely not your problem.
Transaction Mode Values:
2 – Concurrent
3 – Background
p_txnMode is passed to the mtl_transactions_interface.transaction_mode within the wip_flowUtil_priv.explodeRequirementsToMTI procedure
Thanks for the prompt reply !!
Yes, It didn’t solve my problem.
I changed the values of those two fields (TRANSACTION_BATCH_SEQ and OPERATION_SEQ_NUM) that you mentioned and called this wip_flowUtil_priv.explodeRequirementsToMTI API
which results in creation of WOLC of FG and ingredients but phantom items are still stuck in MTI with error.
I don’t expect phantom items to get inserted into MTI.
Wish I could help you more.
Phantom items should not have transactions against them, so I would not expect them to work. I don’t know why the API would be creating records in the interface table. Maybe there is a parameter on the API to do something about that. Or maybe the phantom items are not configured correctly or set up right on the Bill of material.
Hi Ed,
I came across one of your blog articles on ‘Work Order-less Completions’.
Component Backflush with Oracle MTL Transaction Interface Table
we are on 12.1.3 ( same release you mentioned in the blog).
I am inserting assembly component into the mtl_transactions_interface table.
Populating inventory_item_id. But gets error :
Inventory item ID is invalid or does not have the flags enabled correctly to be processed by material processor.
Note: This same item works fine when entered thru “Work Order-less completion form”.
So item setup is correct and verified again to make sure all flags are checked.
Then I tried populating column item_segment1 also with item_number.
Now the process interface inserts 5 new records with source_code = Backflush.
But new error is :Error in merging: ORA-01403: no data found.
So I opened Oracle SR and after longtime, now they say this functionality changed
in R12 and we need to insert all 5 components ( our BOM has 5 components with
Supply_Type = Assembly Pull).
Your blog exactly mentions about this issue.
Please advise do we need to do BOM explosion and insert all components along with
main Assembly item.
If so what is the API to handle the BOM explosion.
I am going to try you insert statement in the blog, but is it main item?
Please advise.
thanks for your help.
-Rao
Go through the insert that I provided, and compare with yours. See what columns I’m providing and you are not. Also see what you are providing that I am not. I’m guessing your problem will be easy to find once you do that.
Hi Ed,
Thanks it worked for transaction_type_name=’WIP Completion’ same as your example.
However it gave below error for transaction_type_name=’WIP assembly scrap’
Error_explanation: WIP_INVALID_COMPLETION_SUB
Error_code : Blank.
Not much description of the error.
What could be the reason. I did not change any of the column values
except below 3 columns:
(SELECT transaction_source_type_id from mtl_transaction_types where transaction_type_name=’WIP assembly scrap’), –TRANSACTION_SOURCE_TYPE_ID
(SELECT transaction_action_id from mtl_transaction_types where transaction_type_name=’WIP assembly scrap’), –TRANSACTION_ACTION_ID
(SELECT transaction_type_id from mtl_transaction_types where transaction_type_name=’WIP assembly scrap’), –TRANSACTION_TYPE_ID
Please advise.
thanks for your help.
-Rao
By not populating column subinventory_code it went thru.
I guess for transaction_type_name=’WIP assembly scrap’ subinventory_code is not
necessary.
I was going to suggest the same thing. When you do an assembly scrap, no assemblies are completed into inventory. The raw materials are consumed and directly applied to the scrap account. It’s the same as doing a WIP Completion, and then doing an account alias scrap of that assembly.
thanks Ed for good explanation.