WIP JOB INTERFACES
************************
Interface Tables for WIP Jobs are :-
------------------------------------
wip_job_schedule_interface
wip_job_dtls_interface
How to Import data from Interface Tables to Base Tables
------------------------------------------------------------
Navigation path: WIP - discrete - import jobs and schedules
Concurrent Program to import data :- WIP Mass Load
group_id = 1 (Same group id as in Interface Table)
Error table :- WIP_INTERFACE_ERRORS
Two types of job creation is there
---------------------------------
1. Create a new job with existing Bills and Routings
2. Create a new job with new Operations, new Resources and new Components
1. New Job with existing Bills and Routings( Here we need to create only job with the assebly item already defined in bom and routing. )
---------------------------------------------------------------------------------------------
INSERT INTO wip_job_schedule_interface
(
organization_code
, primary_item_id
, job_name
, start_quantity
, net_quantity
, first_unit_start_date
, class_code
, status_type
, COMPLETION_SUBINVENTORY
, group_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
'MST' -- organization_id
,973708 -- primary_item_id
,WIP_JOB_NUMBER_S.nextval -- job_name
,2 -- start_quantity
,2 -- Net Quantity
,to_date('09-MAY-2008','DD-MON-RRRR') --first_unit_start_date
,'Discrete' --class_code
,3 --status_type 1.UnReleased 3. Released 4.Complete 6.On Hold 7. Cancelled
,'ASSY' --COMPLETION_SUBINVENTORY
,10 -- group_id
,1 -- load_type
/*
1 Create Standard Discrete Job
2 Create Pending Repetitive Schedule
3 Update Standard or Non-Standard Discrete Job
4 Create Non-Standard Discrete Job
*/
,2 -- process_phase 2 Validation 3 Explosion 4 Complete 5 Creation
,1 -- process_status 1 Pending 2 Running 3 Error 4 Complete 5 Warning
,6437 -- created_by
, SYSDATE -- creation_date
,6437 -- last_updated_by
, SYSDATE -- last_update_date
);
2. Create a new job with new Operations, new Resources and new Components
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Load Type will be differenct in details interface table
a) Create Job
INSERT INTO wip_job_schedule_interface
(
organization_code
, primary_item_id
, job_name
, start_quantity
, net_quantity
, FIRST_UNIT_START_DATE
, LAST_UNIT_COMPLETION_DATE
, CLASS_CODE
, COMPLETION_SUBINVENTORY
, group_id
, header_id
, load_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(
'XXX' -- organization_id
,944397 -- primary_item_id
,WIP_JOB_NUMBER_S.nextval -- job_name
,2 -- start_quantity
,2 -- Net Quantity
,to_date('09-MAY-2008','DD-MON-RRRR') --FIRST_UNIT_START_DATE
,sysdate --LAST_UNIT_COMPLETION_DATE
,'Nonstandard' --CLASS_CODE
,'FG' --COMPLETION_SUBINVENTORY
,1 -- group_id
,3530 -- header_id
,4 -- load_type
,2 -- process_phase
,1 -- process_status
,6157 -- created_by
,SYSDATE -- creation_date
,6157 -- last_updated_by
,SYSDATE -- last_update_date
)
b) Create Component
INSERT INTO wip_job_dtls_interface (
organization_id
, operation_seq_num
, department_id
, inventory_item_id_new
, wip_supply_type
, date_required
, quantity_per_assembly
, required_quantity
, quantity_issued
--, mrp_net_flag
--, description
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(303 --organization_id
,10 --operation_seq_num
,507 --department_id
,963166 --inventory_item_id_new
,7 --wip_supply_type
,sysdate + 2 --date_required
,1 --quantity_per_assembly
,2 --required_quantity
,1 --Quantity_Issued
--,1 --mrp_net_flag
--,'Sample comp' --description
,32060 --group_id
,3530 --parent_header_id
,2 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,2 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,6157 --created_by
,SYSDATE
,6157 --last_updated_by
, SYSDATE )
c) Operation Sequence
INSERT INTO wip_job_dtls_interface (
organization_id
, operation_seq_num
, department_id
, first_unit_start_date
, first_unit_completion_date
, last_unit_start_date
, last_unit_completion_date
, minimum_transfer_quantity
, count_point_type
, BACKFLUSH_FLAG
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(303 --organization_id
,10 --operation_seq_num
,507 --department_id
,sysdate --First_Unit_Start_Date
,sysdate+2 --first_unit_comletion_date
,sysdate --last_unit_start_date
,sysdate + 3 --last_unit_completion_date
,1 --minimum_transfer_quantity
,1 -- count_point_type
,1 --BACKFLUSH_FLAG
,32060 --group_id
,3530 --parent_header_id
,3 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,2 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,6157 --created_by
,SYSDATE
,6157
, SYSDATE )
c) Resource
INSERT INTO wip_job_dtls_interface (
organization_id
, operation_seq_num
, resource_seq_num
, resource_id_new
, required_quantity
, assigned_units
, applied_resource_units
, usage_rate_or_amount
, group_id
, parent_header_id
, load_type
, substitution_type
, process_phase
, process_status
, created_by
, creation_date
, last_updated_by
, last_update_date
)
values
(303 --organization_id
,10 --operation_seq_num
,1 --resource_seq_num
,5075 --resource_id_new
,10 --required_quantity
,1 --assigned_units
,15 --applier_resource_units
,5 --usage_rate_or_amount
,32060 --group_id
,3530 --parent_header_id
,1 --load_type 1. resource 2. component 3. operation 4. multiple resource usage
,2 --substitution_type 1.Delete, 2.Add 3.Change
,2 --process_phase
,1 --process_status
,6157 --created_by
,SYSDATE
,6157 --last_updated_by
, SYSDATE )
21 comments:
Hi Santosh,
Nice article.
I may need a bit of help from you.I have an existing discrete job and i am trying to add a component.I put in all the required fields but the component doesn't get added.
Is it supported to add components.If so can you give me a sample SQL.
would be of great help!
Cheers,
-Murthy.
Hi,
I would like to refresh the routings-Operations and Bom-components on all my unreleased WIP Jobs in Oracle before each time I run MRP because my users are frequently updating these and they already unreleased jobs can have incorrect details by the time they are released.
I have tried different updates with the WIP Mass Load interface but it never seems to refresh these. We are not using Oracle Shop floor Management so can't use that functionality.
Is there a way to do this using WIP Mass load?
Thanks in advance for your help
Hi Santosh,
I am trying to update CLASS_CODE of exiting work order through backend using interface table WIP_JOB_SCHEDULE_INTERFACE.
My record is not failing and its not udating CLASS_CODE either.
Please help with this.
Thanks,
Nikhil.
Hi Nikhil,
Please check the errors in WIP_INTERFACE_ERRORS. Either the data should be errored out or it should update. Check whether the CLASS_CODE is existing one or not. You can refer Doc ID: 601326.1 in metalink for more clarifications
Hi Santosh,
Records didnt got updated and it also didnt got errored out. I checked the error table.
Class code is valid class code. I am able to assign it from front end.
Thanks,
Nikhil.
Hi,
I read note 601326.1 on metalink. Its related to creating work order with different class code.
I am able to create work order with whatever class code I want but I am unable to update class code one the work order is created.
Thanks,
Nikhil.
I would like to refresh the routings-Operations and Bom-components on all my unreleased WIP Jobs in Oracle before each time I run MRP because my users are frequently updating these and they already unreleased jobs can have incorrect details by the time they are released.
I have tried different updates with the WIP Mass Load interface but it never seems to refresh these. We are not using Oracle Shop floor Management so can't use that functionality.
Is there a way to do this using WIP Mass load?Could you please send me the solution for this?Its very urgent
Hi,
I want to update planned oreder firm_planned_type and firm_date in MRP Work bench using API and Interface.
Is this WIP MASS LOAD update ?
Hi Santosh,
I am pretty new in this field with experience of almost 10 months. I have certain questions. Can you please give me some idea how to use metalink at its best?
Hi Santosh,
I want to delete the Operations, Components and Resources from the existing WIP Jobs (in Released status). I have used the subsitution_type as 1 and was able to remove the components and resources from the existing WIP Jobs. But Operations are not getting removed even though program output is showing any errors and completed normal. Could you please let me know how to remove the operations from the existing job ?
Hi Santosh,
I want to add new component in an already created job. Please provide me full method how i'll do it as soon as possible.
Thanks
Saqib
I am trying to update a W.O. but getting the next error: Invalid value for Load Type.
I'm using 3 as load type.
Do yo have the solution for this problem, or migth be wrong with the line im trying to post.?
Im trying to update a W,O. using the Load type = 3.
But in the WIP Mass Load, i'm getting error: Invalid value for Load Type.
While trying to update a W.O, the line have a load type = 3.
the process WIP Mass Load, give me an
Invalid value for Load Type.
Hi Santhosh i am vimal i am loading Discrete job using data loading oracle error occur as Value for column JOB_NAME is invalid.i am giving job type as Non Standard and status unreleased.I have try many times but it showing same error.can u clear this error.pls send to details to my mail:vimal84.msc@gmail.com
Hi Santhosh,
thanks you for ur post.
i am created wip job through back end using
wip_job_schedule_interface .
in these process their is no link between wip job and sales order.
how to reserve the wip job to the sales order through back end.
please help me
Regards,
Manoj
Hi ,
I am facing a problem i want to perform wip completion for standard job using mtl_transaction_interface .When i insert data into mtl_transaction_interface it successfully load and transaferd into MTL_material_transaction table but when i check the job for wip completion form there completion not perfom what i do ....
Please note we not have lot/serial enable and costing method is fifo.
Regards
Asif
Hi,
I am looking for API which i can update revision number for Item in WIP issue transaction,
revision number coming from inventory , but i want to get it from staging table, any body please help.
regards,
sandhya
Hi
i am looking for API which i can update the Component of a job order as the required quantity and the quantity_per_assembly
I tried to do a procedure, but I encountered the following error:
235227 : System cannot delete this material because quantities have already been issued for this item.
please to help me for this issue
in the following a part of my procedure :
CREATE OR REPLACE PROCEDURE Swd_Update_job_Comp ( v_organization_id NUMBER ,wip_id NUMBER ,item_in NUMBER , new_qty NUMBER) AS
v_wo_rec wip_job_schedule_interface%ROWTYPE;
v_wo_comp_rec wip_job_dtls_interface%ROWTYPE;
....
begin
SELECT wip.wip_job_schedule_interface_s.NEXTVAL
INTO v_group_id
FROM dual;
SELECT wip.wip_job_schedule_interface_s.NEXTVAL
INTO v_header_id
FROM dual;
--
v_wo_rec.last_update_date := SYSDATE;
v_wo_rec.last_updated_by := v_user_id;
v_wo_rec.creation_date := SYSDATE;
v_wo_rec.created_by := v_user_id;
v_wo_rec.last_update_login := -1;
--
v_wo_rec.load_type := 3; --Update standard or non-standard Discrete Job
v_wo_rec.process_phase := 2; --Validation
v_wo_rec.process_status := 1; --Pending
v_wo_rec.group_id := v_group_id;
v_wo_rec.header_id := v_header_id;
v_wo_rec.wip_entity_id :=wip_id ;-- 1051898; ---0822/2013/26/R/090
v_wo_rec.organization_id := v_organization_id;
INSERT INTO wip_job_schedule_interface VALUES v_wo_rec;
--components
v_wo_comp_rec.parent_header_id := v_header_id;
v_wo_comp_rec.group_id := v_group_id;
--
v_wo_comp_rec.wip_entity_id := wip_id ; --1051898; --T
v_wo_comp_rec.organization_id := v_organization_id;
v_wo_comp_rec.operation_seq_num := v_OPERATION_SEQ_NUM ; ----10;
v_wo_comp_rec.department_id := v_DEPARTMENT_ID ;---2;
v_wo_comp_rec.inventory_item_id_old := item_in ;--359608; --24520977;
v_wo_comp_rec.inventory_item_id_new := item_in;--359608;
v_wo_comp_rec.wip_supply_type := 1; --push
v_wo_comp_rec.date_required := SYSDATE;
v_wo_comp_rec.MRP_NET_FLAG := 2; --1,yes,2,no
--
v_wo_comp_rec.load_type := 2; --component
v_wo_comp_rec.process_phase := 2; --Validation
v_wo_comp_rec.process_status := 1; --Pending
--
v_wo_comp_rec.last_update_date := SYSDATE;
v_wo_comp_rec.last_updated_by := v_user_id;
v_wo_comp_rec.creation_date := SYSDATE;
v_wo_comp_rec.created_by := v_user_id;
v_wo_comp_rec.substitution_type := 3; --3:chage 1:delete 2:add
v_wo_comp_rec.comments := 'update for wip ';
v_wo_comp_rec.QUANTITY_ISSUED := v_quantity_issued;
v_wo_comp_rec.quantity_per_assembly := new_qty_per_ass ; --required_quantity / job quantity
v_wo_comp_rec.required_quantity :=new_req_qty;
v_wo_comp_rec.parent_header_id := v_header_id;
INSERT INTO wip_job_dtls_interface VALUES v_wo_comp_rec;
--API
Wip_Massload_Pub.massloadjobs(p_groupid => v_group_id, -- Group ID
p_validationlevel => 2, -- Validation Level
p_commitflag => 1, -- Commit 1 =Yes , 0 ='No'
x_returnstatus => x_error_status,
x_errormsg => x_error_message);
COMMIT;
dbms_output.put_line('x_returnstatus INSERT:' || x_error_status);
dbms_output.put_line('x_errormsg INSERT:' || x_error_message);
END;
Thanks for sharing job uodate, people can also visit our website for
Get latest Government Job update
Thank you shares this article. I found this article more information. new govt job bd
Post a Comment