ED HAYES

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