Existing WIP Jobs' components update

hi All,

Spent some time lately trying to update components of already created Work Orders. Seems to be more "interesting" than I though.
First of all, DO NOT RELY ON THE GUIDE for that particular topic!
The guide (Oracle Manufacturing: APIs and Open Interfaces Manual Release 11i Part No. A95955-03 January 2004) misleads (misguides:)) you  regarding this particular issue.
According to Metalink this piece was fixed in the R12 version of the guide, but 11.5.0 release omitted one crucial piece of information - how to start!
The insertion/creation of new Work Orders explained rather decently - first load header and then jobs details:
1. Insert record into WIP_JOB_SCHEDULE_INTERFACE table
2. Insert record into WIP_JOB_DTLS_INTERFACE table
3.submit WIP Mass Load program
I won't elaborate on that one cause there are several detailed examples for it:
http://oracleappss.blogspot.co.il/2008/07/wip-job-creation.html

Work Orders' update process explained pretty thoroughly, so I gave it a try.
According to guide, in order to update jobs' component, you required  to insert the record for the WIP Job you are looking to update into WIP_JOB_DTLS_INTERFACE and submit WIP Mass Load program.

One thing that seemed to be odd and caught my attention is that in case of job components update, the PARENT_HEADER_ID field: ...Must be NULL if only detail records are loaded or updated. Must
provide WIP_ENTITY_ID and ORGANIZATION_ID.... 


Hmm, took me a while to understand and to overcome an implications of that misleading comment.

Solution: For Update, basically, the same scenario should be used as for the Insert:

1. Insert record into WIP_JOB_SCHEDULE_INTERFACE table
2. Insert record into WIP_JOB_DTLS_INTERFACE table
3. Submit WIP Mass Load program

* do consult the guide regarding the required fields.

Following an actual code sample (I been trying to update SUPPLY_SUBINVENTORY filed):

first get "unique" HEADER_ID and GROUP_ID:

select max(t.header_id)+1,
            max(t.group_id)+1
from wip_job_schedule_interface 


and insert the records as follows (those are mostly the required fields allocated)  :


INSERT INTO wip_job_schedule_interface
(
organization_id
, primary_item_id
, job_name
, wip_entity_id
, group_id
, header_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
87 -- organization_id
,329075 -- primary_item_id
,23555--wip_entity_name
,1877088 -- wip_entity_id
,104 -- max(t.group_id)+1
,64798 -- max(t.header_id)+1
,3 -- load_type
,2 -- process_phase
,1 -- process_status
,4672 -- created_by
,SYSDATE -- creation_date
,4672 -- last_updated_by
,SYSDATE -- last_update_date
)



INSERT INTO WIP_JOB_DTLS_INTERFACE (
 organization_id
, operation_seq_num
, wip_entity_id
, inventory_item_id_old
, wip_supply_type
, date_required
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
, SUPPLY_SUBINVENTORY
)
values
(
87 --organization_id
,10 --operation_seq_num
,1877088 --wip_entity_id
,329075 --inventory_item_id_old
,7 --wip_supply_type
,sysdate + 2 --date_required
,104 --max(t.group_id)+1
,64798 --max(t.header_id)+1
,2 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,3 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,4672 --created_by
,SYSDATE
,4672 --last_updated_by
, SYSDATE
,'Blah Blah' );

enjoy

Felix