ERP WIP 部分API应用详解

最新推荐文章于 2024-02-22 12:02:30 发布
夏日青草
最新推荐文章于 2024-02-22 12:02:30 发布
阅读量3.8k
点赞数
分类专栏： ORACLE EBS WIP
ORACLE EBS WIP 专栏收录该内容
1 篇文章 0 订阅
订阅专栏
--工单创建 (create work order) 
PROCEDURE new_work_order(p_wip_entity_id NUMBER,
                         
                         p_organization_id NUMBER,
                         
                         x_error_status OUT VARCHAR2,
                         
                         x_error_message OUT VARCHAR2) IS

  l_iface_rec wip.wip_job_schedule_interface%ROWTYPE; --工单任务接口表 

  CURSOR c_wdj IS
  
    SELECT *
    
      FROM cux_wip_discrete_jobs_temp cwdj
    
     WHERE cwdj.wip_entity_id = p_wip_entity_id
          
       AND cwdj.organization_id = p_organization_id;

BEGIN

  -- 

  l_iface_rec.last_update_date := SYSDATE;

  l_iface_rec.last_updated_by := fnd_global.user_id;

  l_iface_rec.creation_date := SYSDATE;

  l_iface_rec.created_by := fnd_global.user_id;

  -- 

  l_iface_rec.group_id := wip.wip_job_schedule_interface_s.nextval;

  /*================================================ 
  
  WIP_LOAD_TYPE MFG_LOOKUPS 
  
  ------------ -------------- 
  
  1 Create Standard Job 
  
  2 Create Repetitive Schedule 
  
  3 Update Discrete Job 
  
  4 Create Non–standard Job 
  
  ================================================*/

  FOR rec_wdj IN c_wdj
  LOOP
  
    g_status_type := rec_wdj.status_type;
  
    --根据业务逻辑判断标准非标准工单 
  
    IF rec_wdj.job_type = 1 THEN
    
      l_iface_rec.load_type := 1; --1标准 
    
    ELSE
    
      l_iface_rec.load_type := 4; --非标准 
    
    END IF;
  
    l_iface_rec.allow_explosion := 'Y';
  
    l_iface_rec.process_phase := '2';
  
    l_iface_rec.process_status := '1';
  
    l_iface_rec.status_type := '3' /*rec_wdj.status_type*/
    
     ; --已发放 
  
    l_iface_rec.job_name := rec_wdj.job_num;
  
    l_iface_rec.organization_id := rec_wdj.organization_id;
  
    l_iface_rec.class_code := rec_wdj.class_code;
  
    l_iface_rec.primary_item_id := rec_wdj.primary_item_id;
  
    l_iface_rec.start_quantity := rec_wdj.plan_quantity;
  
    l_iface_rec.scheduling_method := '1';
  
    l_iface_rec.first_unit_start_date := rec_wdj.scheduled_start_date;
  
    l_iface_rec.first_unit_completion_date := rec_wdj.scheduled_completion_date;
  
    l_iface_rec.attribute_category := rec_wdj.temp_attribute_category;
  
    l_iface_rec.attribute1 := rec_wdj.temp_attribute1;
  
    l_iface_rec.attribute2 := rec_wdj.temp_attribute2;
  
    l_iface_rec.attribute3 := rec_wdj.temp_attribute3;
  
    l_iface_rec.attribute4 := rec_wdj.temp_attribute4;
  
    l_iface_rec.attribute5 := rec_wdj.temp_attribute5;
  
    l_iface_rec.attribute6 := rec_wdj.temp_attribute6;
  
    l_iface_rec.attribute7 := rec_wdj.temp_attribute7;
  
    l_iface_rec.attribute8 := rec_wdj.temp_attribute8;
  
    l_iface_rec.attribute9 := rec_wdj.temp_attribute9;
  
    l_iface_rec.attribute10 := rec_wdj.temp_attribute10;
  
    l_iface_rec.attribute11 := rec_wdj.temp_attribute11;
  
    l_iface_rec.attribute12 := rec_wdj.temp_attribute12;
  
    l_iface_rec.attribute13 := rec_wdj.temp_attribute13;
  
    l_iface_rec.attribute14 := rec_wdj.temp_attribute14;
  
    l_iface_rec.attribute15 := rec_wdj.temp_attribute15;
  
    l_iface_rec.source_code := 'wip test; 

l_iface_rec.source_line_id := rec_wdj.job_id; 

 

INSERT INTO wip.wip_job_schedule_interface VALUES l_iface_rec; 

END LOOP; 

--API 

wip_massload_pub.massloadjobs(p_groupid => l_iface_rec.group_id, -- Group ID 

p_validationlevel => 2, -- Validation Level 

p_commitflag => 0, -- Commit 1 =Yes , 0 =' no ' 

x_returnstatus => x_error_status, 

x_errormsg => x_error_message); 

 

END;

当update现有工单时 load_type 给3 ，接口中写入要更新字段的新值即可。 

例如 更新工单状态 

 

--更改工单状态 

PROCEDURE change_work_order_status(p_wip_entity_id NUMBER, 

p_organization_id NUMBER, 

p_status_type NUMBER, 

p_group_id NUMBER := wip.wip_job_schedule_interface_s.nextval, 

x_error_status OUT VARCHAR2, 

x_error_message OUT VARCHAR2) IS 

l_schedule_iface_rec wip.wip_job_schedule_interface%ROWTYPE; --工单任务接口表 

--l_group_id NUMBER; 

/*l_returnstatus VARCHAR2(40); 

l_errormsg VARCHAR2(2000);*/ 

l_temp VARCHAR2(3); 

BEGIN 

BEGIN 

SELECT 1 

INTO l_temp 

FROM wip_discrete_jobs wdj 

WHERE wdj.wip_entity_id = p_wip_entity_id 

AND wdj.organization_id = p_organization_id 

AND wdj.status_type = p_status_type; 

x_error_status := 'S'; 

RETURN; 

EXCEPTION 

WHEN no_data_found THEN 

NULL; 

END; 

l_schedule_iface_rec.group_id := p_group_id; 

-- 

l_schedule_iface_rec.last_update_date := SYSDATE; 

l_schedule_iface_rec.last_updated_by := fnd_global.user_id; 

l_schedule_iface_rec.creation_date := SYSDATE; 

l_schedule_iface_rec.created_by := fnd_global.user_id; 

-- 

-- l_schedule_iface_rec.group_id := l_group_id; 

/*================================================ 

WIP_LOAD_TYPE MFG_LOOKUPS 

------------ -------------- 

1 Create Standard Job 

2 Create Repetitive Schedule 

3 Update Discrete Job 

4 Create Non–standard Job 

================================================*/ 

l_schedule_iface_rec.load_type := 3; --Update standard or non-standard Discrete Job 

 

l_schedule_iface_rec.process_phase := 2; --Validation 

 

l_schedule_iface_rec.process_status := 1; --Pending 

-- 

l_schedule_iface_rec.wip_entity_id := p_wip_entity_id; 

l_schedule_iface_rec.organization_id := p_organization_id; 

 

l_schedule_iface_rec.status_type := p_status_type; --目标状态 

 

INSERT INTO wip.wip_job_schedule_interface VALUES l_schedule_iface_rec; 

wip_massload_pub.massloadjobs(p_groupid => p_group_id, -- Group ID 

p_validationlevel => 2, -- Validation Level 

p_commitflag => 0, -- Commit 1 =Yes , 0 ='No' 

x_returnstatus => x_error_status, 

x_errormsg => x_error_message); 

END; 

 

 

移动事务处理(move transaction) 
工单创建完成后，若不进行移动事务处理，则无法进行完工事务处理。移动数量需要按照完工数量的多少进行移动。完工事务处理中有超量完工的概念，因此，移动事务处理也有超量移动。 

需要一个function 获取 可移动数量 

FUNCTION get_available_to_move_qty(p_wip_entity_id IN NUMBER, 

p_opr_seq_num IN NUMBER, 

p_organization_id IN NUMBER, 

p_intraopr_step IN NUMBER) 

RETURN NUMBER IS 

l_available_to_move_qty NUMBER; 

 

CURSOR csr_wip_operations IS 

SELECT decode(p_intraopr_step, 

1, 

wo.quantity_in_queue, 

2, 

wo.quantity_running, 

3, 

wo.quantity_waiting_to_move, 

4, 

wo.quantity_rejected, 

5, 

wo.quantity_scrapped, 

wo.quantity_completed) 

FROM wip_operations wo 

WHERE wo.wip_entity_id = p_wip_entity_id 

AND wo.operation_seq_num = p_opr_seq_num 

AND wo.organization_id = p_organization_id 

AND wo.repetitive_schedule_id IS NULL; 

 

BEGIN 

OPEN csr_wip_operations; 

FETCH csr_wip_operations 

INTO l_available_to_move_qty; 

CLOSE csr_wip_operations; 

 

RETURN l_available_to_move_qty; 

END get_available_to_move_qty; 

在移动事务处理中调用此方法即可。 

PROCEDURE process_move_transaction(p_wip_entity_id IN NUMBER, 

p_organization_id IN NUMBER, 

p_move_qty IN NUMBER, 

p_uom IN VARCHAR2, 

p_job_id IN NUMBER, 

x_error_status OUT VARCHAR2, 

x_error_message OUT VARCHAR2, 

x_over_qty OUT NUMBER) IS 

l_rec_move_txn wip_move_txn_interface%ROWTYPE; 

l_group_id NUMBER; 

l_txn_id NUMBER; 

l_available NUMBER; 

 

BEGIN 

SELECT wip_transactions_s.nextval INTO l_group_id FROM dual; 

--return; 

 

--使用API一条一条的处理 

l_txn_id := NULL; 

l_rec_move_txn := NULL; 

 

SELECT wip_transactions_s.nextval INTO l_txn_id FROM dual; 

 

l_rec_move_txn.transaction_id := l_txn_id; 

l_rec_move_txn.group_id := l_group_id; 

 

l_rec_move_txn.process_phase := 1; 

l_rec_move_txn.process_status := 2; --runing 

 

l_rec_move_txn.created_by := fnd_global.user_id; 

l_rec_move_txn.creation_date := SYSDATE; 

l_rec_move_txn.last_updated_by := fnd_global.user_id; 

l_rec_move_txn.last_update_date := SYSDATE; 

l_rec_move_txn.last_update_login := fnd_global.login_id; 

 

l_rec_move_txn.wip_entity_id := p_wip_entity_id; 

--l_rec_move_txn.wip_entity_name := rec_grp.wo_no; 

l_rec_move_txn.organization_id := p_organization_id; 

l_rec_move_txn.transaction_date := SYSDATE; 

 

l_rec_move_txn.transaction_quantity := abs(p_move_qty);--不管怎样，事务处理数量始终取正值 

l_rec_move_txn.transaction_uom := p_uom; 

IF p_move_qty > 0 THEN –-移动数量大于0时，正向移动，由 排队 到 移动工序号由最小走到最大 

l_rec_move_txn.transaction_type := 1; --1.normal move;2.combination move or completion/return transaction 

SELECT MIN(wo.operation_seq_num), MAX(wo.operation_seq_num) 

INTO l_rec_move_txn.fm_operation_seq_num, 

l_rec_move_txn.to_operation_seq_num 

FROM wip_operations wo 

WHERE wo.wip_entity_id = p_wip_entity_id 

AND wo.organization_id = p_organization_id; 

l_rec_move_txn.fm_intraoperation_step_type := 1; --排队 

l_rec_move_txn.to_intraoperation_step_type := 3; --移动 

 

l_available := get_available_to_move_qty(p_wip_entity_id => p_wip_entity_id, 

p_opr_seq_num => l_rec_move_txn.fm_operation_seq_num, 

p_organization_id => p_organization_id, 

p_intraopr_step => 1); 

IF l_available < p_move_qty THEN -–如果可移动数量小于移动数量，则启用超量移动 

l_rec_move_txn.overcompletion_transaction_qty := p_move_qty - 

l_available; 

l_rec_move_txn.overcompletion_primary_qty := p_move_qty - 

l_available; 

x_over_qty := p_move_qty - 

l_available; 

 

 

END IF; 

 

ELSE–-移动数量小于0时，反向移动，由 移动 到 排队 工序号由最大走到最小 

l_rec_move_txn.transaction_type := 1; --1.normal move;2.combination move or completion/return transaction 

SELECT MAX(wo.operation_seq_num), MIN(wo.operation_seq_num) 

INTO l_rec_move_txn.fm_operation_seq_num, 

l_rec_move_txn.to_operation_seq_num 

FROM wip_operations wo 

WHERE wo.wip_entity_id = p_wip_entity_id 

AND wo.organization_id = p_organization_id; 

l_rec_move_txn.fm_intraoperation_step_type := 3; --移动 

l_rec_move_txn.to_intraoperation_step_type := 1; --排队 

END IF; 

 

l_rec_move_txn.last_updated_by_name := fnd_global.user_name; 

l_rec_move_txn.created_by_name := fnd_global.user_name; 

 

--以下两字段视业务逻辑取数 

l_rec_move_txn.source_code := 'cux_wip_discrete_jobs'; 

l_rec_move_txn.source_line_id := p_job_id; 

 

 

INSERT INTO wip_move_txn_interface VALUES l_rec_move_txn; 

--API 

wip_movproc_pub.processinterface(p_txn_id => l_txn_id, 

p_commit => 'F', 

x_returnstatus => x_error_status, 

x_errormsg => x_error_message); 

 

COMMIT; 

--end loop; 

END; 

 

完工事务处理(move transaction) 
完工事务处理要可以处理完工和退回 两种逻辑不通 

PROCEDURE process_completion_txn(p_completion_txn_id NUMBER, 

x_error_status OUT VARCHAR2, 

x_error_message OUT VARCHAR2) IS 

l_iface_rec inv.mtl_transactions_interface%ROWTYPE; 

l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE; 

--此数据源取数逻辑不同，请自行修改。 

CURSOR c_completion IS 

SELECT * 

FROM cux_wip_completion_txn t 

WHERE t.completion_txn_id = p_completion_txn_id 

and nvl(t.imp_flag, 'N') != 'S'; 

l_return_status VARCHAR2(40); 

l_msg_count NUMBER; 

l_msg_data VARCHAR2(2000); 

l_trans_count NUMBER; 

l_request_id NUMBER; 

 

l_moved_qty NUMBER; 

l_completion_qty NUMBER; 

l_start_qty NUMBER; 

x_over_qty NUMBER; 

BEGIN 

l_iface_rec.last_update_date := SYSDATE; 

l_iface_rec.last_updated_by := fnd_global.user_id; 

l_iface_rec.creation_date := SYSDATE; 

l_iface_rec.created_by := fnd_global.user_id; 

l_iface_rec.last_update_login := fnd_global.user_id; 

FOR rec_completion IN c_completion LOOP 

 

SELECT mtl_material_transactions_s.nextval 

INTO l_iface_rec.transaction_interface_id 

FROM dual; 

l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id; 

/************transaction_mode********************* 

* transaction_mode 

*2 Concurrent, Process transaction interface不处理， 

* 需要程序调用Inventory transaction worker处理 

*3 Background, 由Process transaction interface处理 

***************************************************/ 

l_iface_rec.transaction_mode := 3; 

/************process_flag********************* 

* process_flag 

*1 Yes 

*2 No 

*3 Error 

***************************************************/ 

l_iface_rec.process_flag := 1; 

 

IF rec_completion.quantity >= 0 THEN 

l_iface_rec.transaction_type_id := 44; --mtl_transaction_types--WIP Completion 完工事务处理 

ELSE 

l_iface_rec.transaction_type_id := 17; --如果数量为负数 17 WIP Completion Return 完工事务处理退回 

END IF; 

 

l_iface_rec.transaction_source_type_id := 5; --可查表 mtl_txn_source_types 取得相应值--生产管理 

/************************************************* 

*账户别名杂项，用别名ID即disposition_id 

*账户杂项，用账户ID即code_combination_id 

*WIP物料事务，用任务ID即wip_entity_id 

*SO物料事务，用mtl_sales_orders.sales_order_id 

*子库转移或组织间转移，为空 

************************************************/ 

BEGIN 

SELECT wdj.wip_entity_id, we.primary_item_id 

INTO l_iface_rec.transaction_source_id, 

l_iface_rec.inventory_item_id 

FROM wip_discrete_jobs wdj, wip_entities we 

WHERE (wdj.source_line_id = rec_completion.job_id AND 

wdj.source_code = 'wip platform') 

AND we.wip_entity_id = wdj.wip_entity_id 

AND we.organization_id = wdj.organization_id; 

EXCEPTION 

WHEN no_data_found THEN 

NULL; 

END; 

 

 

IF rec_completion.quantity > 0 THEN --若数量大于零，需要先进行移动事务处理 

process_move_transaction(l_iface_rec.transaction_source_id, 

rec_completion.organization_id, 

rec_completion.quantity, 

rec_completion.uom_code, 

rec_completion.job_id, 

x_error_status, 

x_error_message, 

x_over_qty); 

 

IF x_error_status != 'S' THEN 

UPDATE cux_wip_completion_txn cwct 

SET cwct.imp_flag = x_error_status, 

cwct.imp_msg = x_error_message, 

cwct.last_updated_by = fnd_global.user_id, 

cwct.last_update_date = SYSDATE 

WHERE cwct.completion_txn_id = p_completion_txn_id; 

RETURN; 

END IF; 

END IF; 

 

--移动成功 则 继续 

l_iface_rec.organization_id := rec_completion.organization_id; 

l_iface_rec.subinventory_code := rec_completion.secondary_inventory_name; 

l_iface_rec.locator_id := rec_completion.inventory_location_id; 

l_iface_rec.transaction_quantity := rec_completion.quantity; 

l_iface_rec.transaction_uom := rec_completion.uom_code; 

l_iface_rec.transaction_date := SYSDATE; 

    l_iface_rec.final_completion_flag := 'N'; 

    --以下三字段，视业务逻辑不同取数 

l_iface_rec.source_code := 'cux_wip_completion_txn'; 

l_iface_rec.source_header_id := rec_completion.job_id; 

l_iface_rec.source_line_id := rec_completion.completion_txn_id; 

 

 

INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec; 

 

l_iface_lot_rec.last_update_date := SYSDATE; 

l_iface_lot_rec.last_updated_by := fnd_global.user_id; 

l_iface_lot_rec.creation_date := SYSDATE; 

l_iface_lot_rec.created_by := fnd_global.user_id; 

l_iface_lot_rec.last_update_login := fnd_global.user_id; 

 

l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id; 

l_iface_lot_rec.lot_number := rec_completion.lot_number; 

l_iface_lot_rec.primary_quantity := rec_completion.quantity; 

l_iface_lot_rec.transaction_quantity := rec_completion.quantity; 

--以下三字段，视业务逻辑不同取数 

l_iface_lot_rec.source_code := 'cux_wip_completion_txn'; 

l_iface_lot_rec.source_line_id := rec_completion.completion_txn_id; 

 

INSERT INTO inv.mtl_transaction_lots_interface 

VALUES l_iface_lot_rec; 

 

l_request_id := inv_txn_manager_pub.process_transactions(p_api_version => 1, 

p_init_msg_list => fnd_api.g_false, 

p_commit => fnd_api.g_true, 

p_validation_level => fnd_api.g_valid_level_full, 

x_return_status => l_return_status, 

x_msg_count => l_msg_count, 

x_msg_data => l_msg_data, 

x_trans_count => l_trans_count, 

p_table => 1, 

p_header_id => l_iface_rec.transaction_header_id); 

x_error_status := l_return_status; 

 

IF rec_completion.quantity <= 0 AND x_error_status = 'S' THEN—若为完工退回，在完工后再进行移动，将数量移动回去 

process_move_transaction(l_iface_rec.transaction_source_id, 

rec_completion.organization_id, 

rec_completion.quantity, 

rec_completion.uom_code, 

rec_completion.job_id, 

x_error_status, 

x_error_message, 

x_over_qty); 

 

if x_error_status != 'S' then 

update cux_wip_completion_txn cwct 

set cwct.imp_flag = x_error_status, 

cwct.imp_msg = x_error_message, 

cwct.last_updated_by = fnd_global.USER_ID, 

cwct.last_update_date = sysdate 

where cwct.completion_txn_id = p_completion_txn_id; 

return; 

end if; 

END IF; 

BEGIN 

SELECT mti.error_code || ',' || mti.error_explanation || ',' || 

mli.error_code 

INTO x_error_message 

FROM mtl_transactions_interface mti, 

mtl_transaction_lots_interface mli 

WHERE mti.transaction_interface_id = 

l_iface_rec.transaction_interface_id 

AND mli.transaction_interface_id(+) = 

mti.transaction_interface_id; 

EXCEPTION 

WHEN no_data_found THEN 

NULL; 

END; 

     

    --视业务逻辑不同 进行相应善后处理 

IF x_error_status != 'S' THEN 

DELETE mtl_transaction_lots_interface t 

WHERE t.source_code = 'cux_wip_completion_txn' 

AND t.source_line_id = rec_completion.completion_txn_id; 

 

DELETE mtl_transactions_interface t 

WHERE t.source_code = 'cux_wip_completion_txn' 

AND t.source_line_id = rec_completion.completion_txn_id 

AND t.source_header_id = rec_completion.job_id; 

 

END IF; 

UPDATE cux_wip_completion_txn cwct 

SET cwct.imp_flag = x_error_status, 

cwct.imp_msg = x_error_message, 

cwct.last_updated_by = fnd_global.user_id, 

cwct.last_update_date = SYSDATE 

WHERE cwct.completion_txn_id = p_completion_txn_id; 

 

END LOOP; 

 

END; 

 

投料事务处理(issue transaction) 
投料事务处理同样调用mmt接口表进行处理 跟移动事务处理大同小异 

 

PROCEDURE process_issue_txn(p_issue_txn_id NUMBER, 

x_error_status OUT VARCHAR2, 

x_error_message OUT VARCHAR2) IS 

l_iface_rec inv.mtl_transactions_interface%ROWTYPE; 

l_iface_lot_rec inv.mtl_transaction_lots_interface%ROWTYPE; 

CURSOR c_issue IS 

SELECT * 

FROM cux_wip_issue_txn t 

WHERE t.wip_issue_txn_id = p_issue_txn_id 

and nvl(t.imp_flag, 'N') != 'S'; 

l_return_status VARCHAR2(40); 

l_msg_count NUMBER; 

l_msg_data VARCHAR2(2000); 

l_trans_count NUMBER; 

l_request_id NUMBER; 

 

BEGIN 

l_iface_rec.last_update_date := SYSDATE; 

l_iface_rec.last_updated_by := fnd_global.user_id; 

l_iface_rec.creation_date := SYSDATE; 

l_iface_rec.created_by := fnd_global.user_id; 

l_iface_rec.last_update_login := fnd_global.user_id; 

FOR rec_issue IN c_issue LOOP 

SELECT mtl_material_transactions_s.nextval 

INTO l_iface_rec.transaction_interface_id 

FROM dual; 

l_iface_rec.transaction_header_id := l_iface_rec.transaction_interface_id; 

/************transaction_mode********************* 

* transaction_mode 

*2 Concurrent, Process transaction interface不处理， 

* 需要程序调用Inventory transaction worker处理 

*3 Background, 由Process transaction interface处理 

***************************************************/ 

l_iface_rec.transaction_mode := 3; 

/************process_flag********************* 

* process_flag 

*1 Yes 

*2 No 

*3 Error 

***************************************************/ 

l_iface_rec.process_flag := 1; 

IF rec_issue.transaction_quantity >= 0 THEN 

l_iface_rec.transaction_type_id := 35; --mtl_transaction_types--WIP Issue 

ELSE 

l_iface_rec.transaction_type_id := 43; --wip return 

END IF; 

 

l_iface_rec.transaction_source_type_id := 5; --mtl_txn_source_types--生产管理 

/************************************************* 

*账户别名杂项，用别名ID即disposition_id 

*账户杂项，用账户ID即code_combination_id 

*WIP物料事务，用任务ID即wip_entity_id 

*SO物料事务，用mtl_sales_orders.sales_order_id 

*子库转移或组织间转移，为空 

************************************************/ 

 

SELECT wdj.wip_entity_id 

INTO l_iface_rec.transaction_source_id 

FROM wip_discrete_jobs wdj, wip_entities we 

WHERE (wdj.source_line_id = rec_issue.job_id OR 

(wdj.wip_entity_id, wdj.organization_id) IN 

(SELECT cwdj.wip_entity_id, cwdj.organization_id 

FROM cux_wip_discrete_jobs_v cwdj 

WHERE cwdj.job_id = rec_issue.job_id 

AND cwdj.order_wip_entity_id IS NULL)) 

AND we.wip_entity_id = wdj.wip_entity_id 

AND we.organization_id = wdj.organization_id; 

 

l_iface_rec.inventory_item_id := rec_issue.inventory_item_id; 

l_iface_rec.organization_id := rec_issue.organization_id; 

l_iface_rec.subinventory_code := rec_issue.secondary_inventory_name; 

l_iface_rec.locator_id := rec_issue.inventory_location_id; 

l_iface_rec.transaction_quantity := -rec_issue.transaction_quantity; 

l_iface_rec.transaction_uom := rec_issue.primary_uom_code; 

l_iface_rec.reason_id := rec_issue.reason_id; 

 

l_iface_rec.transaction_date := SYSDATE; 

l_iface_rec.source_code := 'cux_wip_issue_txn'; 

l_iface_rec.source_header_id := rec_issue.job_id; 

l_iface_rec.source_line_id := rec_issue.wip_issue_txn_id; 

l_iface_rec.final_completion_flag := 'N'; 

 

INSERT INTO inv.mtl_transactions_interface VALUES l_iface_rec; 

IF rec_issue.lot_number IS NOT NULL THEN 

l_iface_lot_rec.last_update_date := SYSDATE; 

l_iface_lot_rec.last_updated_by := fnd_global.user_id; 

l_iface_lot_rec.creation_date := SYSDATE; 

l_iface_lot_rec.created_by := fnd_global.user_id; 

l_iface_lot_rec.last_update_login := fnd_global.user_id; 

 

l_iface_lot_rec.transaction_interface_id := l_iface_rec.transaction_interface_id; 

l_iface_lot_rec.lot_number := rec_issue.lot_number; 

l_iface_lot_rec.primary_quantity := -rec_issue.transaction_quantity; 

l_iface_lot_rec.transaction_quantity := -rec_issue.transaction_quantity; 

 

l_iface_lot_rec.source_code := 'cux_wip_issue_txn'; 

l_iface_lot_rec.source_line_id := rec_issue.wip_issue_txn_id; 

 

INSERT INTO inv.mtl_transaction_lots_interface 

VALUES l_iface_lot_rec; 

END IF; 

l_request_id := inv_txn_manager_pub.process_transactions(p_api_version => 1, 

p_init_msg_list => fnd_api.g_false, 

p_commit => fnd_api.g_true, 

p_validation_level => fnd_api.g_valid_level_full, 

x_return_status => l_return_status, 

x_msg_count => l_msg_count, 

x_msg_data => l_msg_data, 

x_trans_count => l_trans_count, 

p_table => 1, 

p_header_id => l_iface_rec.transaction_header_id); 

x_error_status := l_return_status; 

BEGIN 

SELECT mti.error_code || ',' || mti.error_explanation || ',' || 

mli.error_code 

INTO x_error_message 

FROM mtl_transactions_interface mti, 

mtl_transaction_lots_interface mli 

WHERE mti.transaction_interface_id = 

l_iface_rec.transaction_interface_id 

AND mli.transaction_interface_id(+) = 

mti.transaction_interface_id; 

EXCEPTION 

WHEN no_data_found THEN 

NULL; 

END; 

 

IF x_error_status != 'S' THEN 

DELETE mtl_transaction_lots_interface t 

WHERE t.source_code = 'cux_wip_issue_txn' 

AND t.source_line_id = rec_issue.wip_issue_txn_id; 

 

DELETE mtl_transactions_interface t 

WHERE t.source_code = 'cux_wip_issue_txn' 

AND t.source_line_id = rec_issue.wip_issue_txn_id 

AND t.source_header_id = rec_issue.job_id; 

 

END IF; 

 

UPDATE cux_wip_issue_txn cwit 

SET cwit.imp_flag = x_error_status, 

cwit.imp_msg = x_error_message, 

cwit.last_updated_by = fnd_global.user_id, 

cwit.last_update_date = SYSDATE 

WHERE cwit.wip_issue_txn_id = p_issue_txn_id; 

END LOOP; 

END;
夏日青草
关注
0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
ERP WIP 部分API应用详解

--工单创建 (create work order) PROCEDURE new_work_order(p_wip_entity_id NUMBER, p_organization_id NUMBER,
复制链接

扫一扫