WIP Mass Load Interface(Discrete Job Interface)

The following are the insert statements for creating a discrete job through wip mass load.

== for creating a job
==========================
INSERT INTO                WIP_JOB_SCHEDULE_INTERFACE
  (last_update_date,
  last_updated_by,           
  creation_date,
  created_by,
last_update_login,
  group_id,            
  organization_id,
  load_type,
  wip_entity_id,
  process_phase,          
  process_status,
  header_id,
  allow_explosion,
  net_quantity,
  start_quantity,
  primary_item_id,
  COMPLETION_SUBINVENTORY,
--CLASS_CODE,
JOB_NAME,
first_unit_start_date,FIRST_UNIT_COMPLETION_DATE,LAST_UNIT_START_DATE,LAST_UNIT_COMPLETION_DATE
)
VALUES (
  SYSDATE,
  1318, 
  SYSDATE,
  1318,
  1318,
  1318,
  207,
  1, --1. create standard DJ, 2.creative pending repetitive schedule, 3. update standard or non standard job, 4. create non standard job
  null,
  2, --2. validation, 3.  EXPLOSION, 4. COMPLETION, 5. CREATION
  1, --1. pending 2. running, 3. error 4. complete 5. warning         
  1318
  ,'N',1,1
  ,98816  --KKTESTASS
  ,'FGI',
  --,'DISCRETE',
  'TESTKKWML1', sysdate,sysdate+1, sysdate,sysdate+1
  );
===============================
==FOR ADDING  AN OPERATION TO JOB
===============================
insert into wip_job_dtls_interface(group_id, organization_id, operation_seq_num,
                     department_id, load_type, substitution_type,
                     process_phase, process_status, last_update_date,
                     last_updated_by, creation_date,
                     created_by, parent_header_id,
             COUNT_POINT_TYPE,BACKFLUSH_FLAG
                                     )
             values(1318,--:parameter.user_id,--5300,
                    207,
                    3350,
                   6772,--KKTESTDEPT,
                    3,--load_type
                    2,--substitution_type
                    2,--process_phase
                    1,-- process_status
                    sysdate,
                    1318,--:parameter.user_id,
                    sysdate,
                    1318,--:parameter.user_id,
                    1318 , 2,1);
=======================
==FOR ADDING RESOURCE TO AN OPERATION
=============================
insert into wip_job_dtls_interface(group_id, organization_id, operation_seq_num,
                     RESOURCE_SEQ_NUM, RESOURCE_ID_NEW,USAGE_RATE_OR_AMOUNT,
 SCHEDULED_FLAG, ASSIGNED_UNITS,BASIS_TYPE,AUTOCHARGE_TYPE,STANDARD_RATE_FLAG,
START_DATE,COMPLETION_DATE,load_type, substitution_type,
                     process_phase, process_status, last_update_date,
                     last_updated_by, creation_date,
                     created_by, parent_header_id, SCHEDULE_SEQ_NUM
                                     )
             values(1318,--:parameter.user_id,--5300,
                    207,
                    3350,
                   10,--RESOURCE_SEQ_NUM,
           11266, --KKTESTRES
           0.5, -- USAGE RATE
           2, --SCHEDULE FLAG
           1, --ASSIGNED_UNITS
           1, --BASIS_TYPE
           1, --AUTOCHARGE_TYPE
           1, --STANDARD_RATE_FLAG
           sysdate, --START_DATE
           sysdate, --COMPLETION_DATE
                    1,--load_type
                    2,--substitution_type
                    2,--process_phase
                    1,-- process_status
                    sysdate,
                    1318,--:parameter.user_id,
                    sysdate,
                    1318,--:parameter.user_id,
                    1318,
            335010
);       

API for creating the routing network in EAM

create or replace
procedure TEST_EAM_network_routing is

  l_rtg_header_rec     Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
  l_rtg_revision_tbl   Bom_Rtg_Pub.Rtg_Revision_Tbl_Type := Bom_Rtg_Pub.G_MISS_RTG_REVISION_TBL;
  l_operation_tbl      Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
  l_op_resource_tbl    Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
  l_sub_resource_tbl   Bom_Rtg_Pub.Sub_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_SUB_RESOURCE_TBL;
  l_op_network_tbl     Bom_Rtg_Pub.Op_Network_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_NETWORK_TBL;

  l_x_rtg_header_rec   Bom_Rtg_Pub.Rtg_Header_Rec_Type := Bom_Rtg_Pub.G_MISS_RTG_HEADER_REC;
  l_x_rtg_revision_tbl Bom_Rtg_Pub.Rtg_Revision_Tbl_Type := Bom_Rtg_Pub.G_MISS_RTG_REVISION_TBL;
  l_x_operation_tbl    Bom_Rtg_Pub.Operation_Tbl_Type := Bom_Rtg_Pub.G_MISS_OPERATION_TBL;
  l_x_op_resource_tbl  Bom_Rtg_Pub.Op_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_RESOURCE_TBL;
  l_x_sub_resource_tbl Bom_Rtg_Pub.Sub_Resource_Tbl_Type := Bom_Rtg_Pub.G_MISS_SUB_RESOURCE_TBL;
  l_x_op_network_tbl   Bom_Rtg_Pub.Op_Network_Tbl_Type := Bom_Rtg_Pub.G_MISS_OP_NETWORK_TBL;
 
  gvOpIdx BINARY_INTEGER := 1;
  gvRsIdx BINARY_INTEGER := 1;
 
  -- declare for error handler
  l_error_message_list      error_handler.error_tbl_type;
  l_x_return_status          Varchar2(2000);
  l_x_msg_count              Number := 0;
 
  lv_user_id number;
  lv_responsibility_id number;
  lv_application_id number;
 
BEGIN

  select user_id
     into lv_user_id
     from fnd_user where user_name = 'PLMMGR';
   SELECT responsibility_id,application_id
     INTO lv_responsibility_id,lv_application_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = 'Manufacturing and Distribution Manager'
      AND language = 'US';

  FND_GLOBAL.APPS_INITIALIZE(lv_user_id,lv_responsibility_id,lv_application_id);
 
--l_rtg_header_rec.Delete;
l_operation_tbl.Delete;
l_error_message_list.delete;

            l_rtg_header_rec.Assembly_Item_Name := 'TEST01';
            l_rtg_header_rec.Organization_Code := 'EM1';
            l_rtg_header_rec.Alternate_Routing_Code :=  NULL;
            l_rtg_header_rec.Eng_Routing_Flag :=  2;
            -- l_rtg_header_rec.Common_Assembly_Item_Name := 
            -- l_rtg_header_rec.Routing_Comment := 
            -- l_rtg_header_rec.Completion_Subinventory := 
            -- l_rtg_header_rec.Completion_Location_Name := 
            -- l_rtg_header_rec.Line_Code := 
            --l_rtg_header_rec.CFM_Routing_Flag :=  2;
            --l_rtg_header_rec.Mixed_Model_Map_Flag :=  2;
            -- l_rtg_header_rec.Priority := 
            -- l_rtg_header_rec.Total_Cycle_Time := 
            --l_rtg_header_rec.CTP_Flag :=  2;
            -- l_rtg_header_rec.Attribute_category := 
            -- l_rtg_header_rec.Attribute1 := 
            -- l_rtg_header_rec.Attribute2 := 
            -- l_rtg_header_rec.Attribute3 := 
            -- l_rtg_header_rec.Attribute4 := 
            -- l_rtg_header_rec.Attribute5 := 
            -- l_rtg_header_rec.Attribute6 := 
            -- l_rtg_header_rec.Attribute7 := 
            -- l_rtg_header_rec.Attribute8 := 
            -- l_rtg_header_rec.Attribute9 := 
            -- l_rtg_header_rec.Attribute10 := 
            -- l_rtg_header_rec.Attribute11 := 
            -- l_rtg_header_rec.Attribute12 := 
            -- l_rtg_header_rec.Attribute13 := 
            -- l_rtg_header_rec.Attribute14 := 
            -- l_rtg_header_rec.Attribute15 := 
            -- l_rtg_header_rec.Original_System_Reference := 
            l_rtg_header_rec.Transaction_Type :=  'UPDATE';
            -- l_rtg_header_rec.Return_Status := 
            -- l_rtg_header_rec.Delete_Group_Name := 
            -- l_rtg_header_rec.DG_Description := 
            -- l_rtg_header_rec.Ser_Start_Op_Seq := 
            -- l_rtg_header_rec.Row_Identifier := 
           
           
           
   /*
   --To create/update operation
  
            l_operation_tbl(gvOpIdx).Assembly_Item_Name :=  'RK_Item3';
            l_operation_tbl(gvOpIdx).Organization_Code :=  'M1';
            l_operation_tbl(gvOpIdx).Alternate_Routing_Code :=  'EBOM';
            l_operation_tbl(gvOpIdx).Operation_Sequence_Number :=  10;
            l_operation_tbl(gvOpIdx).Operation_Type :=  1;
            l_operation_tbl(gvOpIdx).Start_Effective_Date :=  sysdate;
            -- l_operation_tbl(gvOpIdx).New_Operation_Sequence_Number := 
            -- l_operation_tbl(gvOpIdx).New_Start_Effective_Date := 
            l_operation_tbl(gvOpIdx).Standard_Operation_Code :=  'APRT';
            --l_operation_tbl(gvOpIdx).Department_Code :=  'ASSEMBLY';
            -- l_operation_tbl(gvOpIdx).Op_Lead_Time_Percent := 
            --l_operation_tbl(gvOpIdx).Minimum_Transfer_Quantity :=  0;
      --l_operation_tbl(gvOpIdx).Count_Point_Type :=  3;
            -- l_operation_tbl(gvOpIdx).Operation_Description := 
            -- l_operation_tbl(gvOpIdx).Disable_Date := 
            --l_operation_tbl(gvOpIdx).Backflush_Flag :=  1;
            --l_operation_tbl(gvOpIdx).Option_Dependent_Flag :=  2;
            --l_operation_tbl(gvOpIdx).Reference_Flag :=  1;
            -- l_operation_tbl(gvOpIdx).Process_Seq_Number := 
            -- l_operation_tbl(gvOpIdx).Process_Code := 
            -- l_operation_tbl(gvOpIdx).Line_Op_Seq_Number := 
            -- l_operation_tbl(gvOpIdx).Line_Op_Code := 
            -- l_operation_tbl(gvOpIdx).Yield := 
            -- l_operation_tbl(gvOpIdx).Cumulative_Yield := 
            -- l_operation_tbl(gvOpIdx).Reverse_CUM_Yield := 
            -- l_operation_tbl(gvOpIdx).User_Labor_Time := 
            -- l_operation_tbl(gvOpIdx).User_Machine_Time := 
            -- l_operation_tbl(gvOpIdx).Net_Planning_Percent := 
            --l_operation_tbl(gvOpIdx).Include_In_Rollup :=  1;
            -- l_operation_tbl(gvOpIdx).Op_Yield_Enabled_Flag := 
            -- l_operation_tbl(gvOpIdx).Shutdown_Type := 
            -- l_operation_tbl(gvOpIdx).Attribute_category := 
            -- l_operation_tbl(gvOpIdx).Attribute1 := 
            -- l_operation_tbl(gvOpIdx).Attribute2 := 
            -- l_operation_tbl(gvOpIdx).Attribute3 := 
            -- l_operation_tbl(gvOpIdx).Attribute4 := 
            -- l_operation_tbl(gvOpIdx).Attribute5 := 
            -- l_operation_tbl(gvOpIdx).Attribute6 := 
            -- l_operation_tbl(gvOpIdx).Attribute7 := 
            -- l_operation_tbl(gvOpIdx).Attribute8 := 
            -- l_operation_tbl(gvOpIdx).Attribute9 := 
            -- l_operation_tbl(gvOpIdx).Attribute10 := 
            -- l_operation_tbl(gvOpIdx).Attribute11 := 
            -- l_operation_tbl(gvOpIdx).Attribute12 := 
            -- l_operation_tbl(gvOpIdx).Attribute13 := 
            -- l_operation_tbl(gvOpIdx).Attribute14 := 
            -- l_operation_tbl(gvOpIdx).Attribute15 := 
            -- l_operation_tbl(gvOpIdx).Original_System_Reference := 
            l_operation_tbl(gvOpIdx).Transaction_Type :=  'CREATE';    --UPDATE
            -- l_operation_tbl(gvOpIdx).Return_Status := 
            -- l_operation_tbl(gvOpIdx).Delete_Group_Name := 
            -- l_operation_tbl(gvOpIdx).DG_Description := 
            -- l_operation_tbl(gvOpIdx).Long_Description := 
            -- l_operation_tbl(gvOpIdx).Row_Identifier := 
     */      
           
  --to create/update Operation network
 
  l_op_network_tbl(gvOpIdx).Assembly_Item_Name        :='TEST01';  
l_op_network_tbl(gvOpIdx).Organization_Code         := 'EM1';  
l_op_network_tbl(gvOpIdx).Alternate_Routing_Code    :=  NULL;
--l_op_network_tbl(gvOpIdx).Operation_Type          
l_op_network_tbl(gvOpIdx).From_Op_Seq_Number        :=20;
--l_op_network_tbl(gvOpIdx).From_X_Coordinate       
--l_op_network_tbl(gvOpIdx).From_Y_Coordinate       
l_op_network_tbl(gvOpIdx).From_Start_Effective_Date:=To_date('19-JUL-2012 04:25:12','DD-MON-YYYY HH:MI:SS');
l_op_network_tbl(gvOpIdx).To_Op_Seq_Number           :=30;
--l_op_network_tbl(gvOpIdx).To_X_Coordinate         
--l_op_network_tbl(gvOpIdx).To_Y_Coordinate         
l_op_network_tbl(gvOpIdx).To_Start_Effective_Date :=To_date('19-JUL-2012 04:26:14','DD-MON-YYYY HH:MI:SS');
l_op_network_tbl(gvOpIdx).New_From_Op_Seq_Number   :=20;
l_op_network_tbl(gvOpIdx).New_From_Start_Effective_Date :=To_date('19-JUL-2012 04:25:12','DD-MON-YYYY HH:MI:SS');
l_op_network_tbl(gvOpIdx).New_To_Op_Seq_Number  :=30;
l_op_network_tbl(gvOpIdx).New_To_Start_Effective_Date:=To_date('19-JUL-2012 04:26:14','DD-MON-YYYY HH:MI:SS');
--l_op_network_tbl(gvOpIdx).Connection_Type           
--l_op_network_tbl(gvOpIdx).Planning_Percent          
--l_op_network_tbl(gvOpIdx).Attribute_category        
--l_op_network_tbl(gvOpIdx).Attribute1                
--l_op_network_tbl(gvOpIdx).Attribute2                
--l_op_network_tbl(gvOpIdx).Attribute3                
--l_op_network_tbl(gvOpIdx).Attribute4                
--l_op_network_tbl(gvOpIdx).Attribute5                
--l_op_network_tbl(gvOpIdx).Attribute6                
--l_op_network_tbl(gvOpIdx).Attribute7                
--l_op_network_tbl(gvOpIdx).Attribute8                
--l_op_network_tbl(gvOpIdx).Attribute9                
--l_op_network_tbl(gvOpIdx).Attribute10               
--l_op_network_tbl(gvOpIdx).Attribute11               
--l_op_network_tbl(gvOpIdx).Attribute12               
--l_op_network_tbl(gvOpIdx).Attribute13               
--l_op_network_tbl(gvOpIdx).Attribute14               
--l_op_network_tbl(gvOpIdx).Attribute15               
--l_op_network_tbl(gvOpIdx).Original_System_Reference 
l_op_network_tbl(gvOpIdx).Transaction_Type               :='CREATE';    --UPDATE
--l_op_network_tbl(gvOpIdx).Return_Status             
--l_op_network_tbl(gvOpIdx).Row_Identifier            

           
           
    Bom_Rtg_Pub.Process_Rtg(p_bo_identifier      => 'RTG',
                            p_api_version_number => 1.0,
                            p_init_msg_list      => FALSE,
                            p_rtg_header_rec     => l_rtg_header_rec,
                            p_rtg_revision_tbl   => l_rtg_revision_tbl,
                            p_operation_tbl      => l_operation_tbl,
                            p_op_resource_tbl    => l_op_resource_tbl,
                            p_sub_resource_tbl   => l_sub_resource_tbl,
                            p_op_network_tbl     => l_op_network_tbl,
                            x_rtg_header_rec     => l_x_rtg_header_rec,
                            x_rtg_revision_tbl   => l_x_rtg_revision_tbl,
                            x_operation_tbl      => l_x_operation_tbl,
                            x_op_resource_tbl    => l_x_op_resource_tbl,
                            x_sub_resource_tbl   => l_x_sub_resource_tbl,
                            x_op_network_tbl     => l_x_op_network_tbl,
                            x_return_status      => l_x_return_status,
                            x_msg_count          => l_x_msg_count,
                            p_debug              => 'N',
                            p_output_dir         => '/ust/tmp',
                            p_debug_filename     => 'Debug.log');
                           
   dbms_output.put_line('Return Status = '||l_x_return_status);
   dbms_output.put_line('Message Count = '||l_x_msg_count);

   /**** Error messages ****/
 
   error_handler.Get_message_list(l_error_message_list);
  -- If l_x_return_status <> 'S'
   --Then
   -- Error Processing
   For i In 1..l_x_msg_count
   Loop
    dbms_output.put_line ( Substr(l_error_message_list(i).message_text, 1, 240));
   End Loop;
   If l_x_return_status = 'S'
   Then
    dbms_output.put_line('Routing processed successfully' );
   End If;                           
END;

Creating Lot based job in shop floor manufacturing

===============================
Creating Lot based job
=================================

create or replace
procedure test_lot_attr
As
v_transaction_interface_id number;
v_header_id number; --Product_transaction_id

begin


select wsm_lot_sm_ifc_header_s.nextval
into v_header_id from dual;            --header id of WSM_LOT_JOB_INTERFACE and Product_transaction_id of mtl_transaction_lots_interface

select mtl_material_transactions_s.NEXTVAL
into v_transaction_interface_id from dual;    -- transaction interface id for mtl_transaction_lots_interface

Insert into APPS.WSM_LOT_JOB_INTERFACE         
   (MODE_FLAG                     
, LAST_UPDATE_DATE                 
, LAST_UPDATED_BY                 
, CREATION_DATE                     
, CREATED_BY                     
, GROUP_ID                     
, SOURCE_CODE                     
, ORGANIZATION_ID                 
, LOAD_TYPE                --5 for create and 6 for update             
, STATUS_TYPE                     
, LAST_UNIT_COMPLETION_DATE             
, PRIMARY_ITEM_ID                 
, WIP_SUPPLY_TYPE                 
, CLASS_CODE                     
, LOT_NUMBER                     
, JOB_NAME                     
, DESCRIPTION                     
, START_QUANTITY                 
, PROCESS_PHASE                     
, PROCESS_STATUS                 
, FIRST_UNIT_START_DATE                 
, SCHEDULING_METHOD                 
, COMPLETION_SUBINVENTORY             
, ALLOW_EXPLOSION                 
, HEADER_ID                     
)
 Values
   (1
, sysdate
, 1318
, sysdate
, 1318
, 1318
, 'FTC01'
, 1641
, 5
, 3   
, sysdate
, 11195
, 7              
, 'TST-WAC-M4'
, '268314'
, '268314'
, 'JOB THROUGH API'
, 2
, 2
, 1
, sysdate
, 3
, 'FGI'
, 'Y'
, v_header_id
);

Insert into mtl_transaction_lots_interface
(TRANSACTION_INTERFACE_ID
,LAST_UPDATE_DATE     
,LAST_UPDATED_BY      
,CREATION_DATE        
,CREATED_BY
,LOT_NUMBER
,TRANSACTION_QUANTITY
,Lot_attribute_category
,C_ATTRIBUTE1
,C_ATTRIBUTE2
--,Attribute_category
--,attribute1
--,attribute2
,Product_transaction_id
,product_code

)
Values
(v_transaction_interface_id
,sysdate
,1318
,sysdate
,1318
,'268314'
,2
,'Global Data Elements'
,'US'
, '1'
,v_header_id
,'WSM'
);

end;


=================================
updating lot based job
==================================
create or replace
procedure test_lot_attr_update
As
v_transaction_interface_id number;
v_header_id number; --Product_transaction_id

begin


select wsm_lot_sm_ifc_header_s.nextval
into v_header_id from dual;            --header id of WSM_LOT_JOB_INTERFACE and Product_transaction_id of mtl_transaction_lots_interface

select mtl_material_transactions_s.NEXTVAL
into v_transaction_interface_id from dual;    -- transaction interface id for mtl_transaction_lots_interface

Insert into APPS.WSM_LOT_JOB_INTERFACE         
   (MODE_FLAG                     
, LAST_UPDATE_DATE                 
, LAST_UPDATED_BY                 
, CREATION_DATE                     
, CREATED_BY                     
, GROUP_ID                     
, SOURCE_CODE                     
, ORGANIZATION_ID                 
, LOAD_TYPE                     
, STATUS_TYPE                     
, LAST_UNIT_COMPLETION_DATE             
, PRIMARY_ITEM_ID                 
, WIP_SUPPLY_TYPE                 
, CLASS_CODE                     
, LOT_NUMBER                     
, JOB_NAME                     
, DESCRIPTION                     
, START_QUANTITY                 
, PROCESS_PHASE                     
, PROCESS_STATUS                 
, FIRST_UNIT_START_DATE                 
, SCHEDULING_METHOD                 
, COMPLETION_SUBINVENTORY             
, ALLOW_EXPLOSION                 
, HEADER_ID                     
)
 Values
   (1
, sysdate
, 1318
, sysdate
, 1318
, 1318
, 'FTC01'
, 1641
, 6
, 3   
, sysdate
, 11195
, 7              
, 'TST-WAC-M4'
, '123456789'
, '123456789'
, 'JOB THROUGH API'
, 10
, 10
, 1
, sysdate
, 3
, 'Stores'
, 'Y'
, v_header_id
);

Insert into mtl_transaction_lots_interface
(TRANSACTION_INTERFACE_ID
,LAST_UPDATE_DATE     
,LAST_UPDATED_BY      
,CREATION_DATE        
,CREATED_BY
,LOT_NUMBER
,TRANSACTION_QUANTITY
,Lot_attribute_category
,C_ATTRIBUTE1
,C_ATTRIBUTE2
--,Attribute_category
--,attribute1
--,attribute2
,Product_transaction_id
,product_code

)
Values
(v_transaction_interface_id
,sysdate
,1318
,sysdate
,1318
,'123456789'
,2
,'Global Data Elements'
,'US'
, '1'
,v_header_id
,'WSM'
);

commit;
end;