Oracle EBS 工单工序移动接口开发

Oracle EBS 工单工序移动接口开发

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

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

FUNCTIONget_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 ISNULL;

  

  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;

 --Call API Process WIP Move Transaction

  --Fixed:

  --TRANSACTION_ID/GROUP_ID/PROCESS_PHASE/PROCESS_STATUS not valid. Please re-enter

  --SELECT group_id

  --   FROM wip_move_txn_interface

  --  WHERE transaction_id = p_txn_id

  --    AND process_phase = WIP_CONSTANTS.MOVE_VAL

  --    AND process_status = WIP_CONSTANTS.RUNNING

  --    AND group_id IS NOT NULL;

  --Doc ID:  363753.1

  --

PROCEDUREprocess_wip_move_txn(p_transaction_id IN NUMBER,

                                 p_commit        IN VARCHAR2 := fnd_api.g_false,

                                 x_return_status  IN OUT VARCHAR2,

                                 x_error_message IN OUT VARCHAR2)IS

     --PRAGMA AUTONOMOUS_TRANSACTION;

     l_groupid NUMBER;

     l_errbuf VARCHAR2(1000);

     l_retcode NUMBER;

  BEGIN

     -- Call the procedure

     

     cux_conc_utl.log_msg(p_msg => '   Begin Call Wip_movproc_pub.processinterface()');

  

     l_groupid := p_transaction_id;

     

     --wip_move_validator.validate(p_group_id=> l_groupid, p_initmsglist => fnd_api.g_true);

     

     -- This procedure should be called if caller want to do batch processing formultiple records in WMTI.

     

  

     -- This procedure should be called if caller want to process one record at atime.

     wip_movproc_pub.processinterface(p_txn_id      => p_transaction_id,

                                      p_do_backflush => fnd_api.g_false,

                                      p_commit      =>fnd_api.g_false,

                                      x_returnstatus => x_return_status,

                                      x_errormsg    =>x_error_message);

     IF x_return_status =fnd_api.g_ret_sts_success THEN

        NULL;

        cux_conc_utl.log_msg(p_msg =>'   The Transaction_ID : ' ||

                                      p_transaction_id ||

                                      'Succesfully Processed');

        --COMMIT;

     ELSE

        cux_conc_utl.log_msg(p_msg =>'   The Transaction_ID : ' ||

                                      p_transaction_id ||

                                      ' WIP APIError : ' ||

                                      x_error_message);

        cux_conc_utl.out_msg(p_msg =>'   The Transaction_ID : ' ||

                                      p_transaction_id ||

                                      ' WIP API Error : '||

                                      x_error_message);

        --RAISE fnd_api.g_exc_error;

        --ROLLBACK;

     END IF;

     cux_conc_utl.log_msg(p_msg => '   End Call Wip_movproc_pub.processinterface()');

  END process_wip_move_txn;

FUNCTIONprocess_wip_move(p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,

                            p_commit       IN VARCHAR2 DEFAULT fnd_api.g_false,

                            x_return_status OUT NOCOPYVARCHAR2,

                            x_msg_count    OUT NOCOPY NUMBER,

                            x_msg_data     OUT NOCOPY VARCHAR2,

                            p_parameter1   IN VARCHAR2) RETURN VARCHAR2 IS

     l_api_name      CONSTANT VARCHAR2(30) :='process_wip_move';

     l_savepoint_name CONSTANT VARCHAR2(30) :='sp_process_wip_move';

     l_return_status VARCHAR2(1);

     l_msg_data     VARCHAR2(2000);

     l_rec_move_txn wip_move_txn_interface%ROWTYPE;

  

     l_group_id  NUMBER;

     l_txn_id   NUMBER;

     l_available NUMBER;

     l_trn_intermtl_transactions_interface%ROWTYPE;

  

     v_transaction_header_id NUMBER DEFAULTNULL;

     v_transaction_action_id NUMBER DEFAULTNULL;

     v_quantity_flag        NUMBER DEFAULT 1;

  

     v_return_status  VARCHAR2(3) DEFAULT NULL;

     v_return_message VARCHAR2(6000) DEFAULTNULL;

  

     v_on_hand_enough VARCHAR2(1) DEFAULT 'N';

     x_over_qty      NUMBER;

  

     l_wip_entity_id       NUMBER := 265057;

     l_organization_id     NUMBER := 353;

     l_transaction_quantity NUMBER := 3;

  

     -- v_lot_number             VARCHAR2(15) := 'zhw007';*\

  

  BEGIN

     -- start activity to create savepoint,check compatibility

     -- and initialize message list, includedebug message hint to enter api

     x_return_status :=cux_api.start_activity(p_pkg_name      => g_pkg_name,

                                               p_api_name      => l_api_name,

                                               p_savepoint_name=> l_savepoint_name,

                                               p_init_msg_list =>p_init_msg_list);

     IF (x_return_status =fnd_api.g_ret_sts_unexp_error) THEN

        RAISE fnd_api.g_exc_unexpected_error;

     ELSIF (x_return_status =fnd_api.g_ret_sts_error) THEN

        RAISE fnd_api.g_exc_error;

     END IF;

     -- API body

  

     -- logging parameters

     IF l_debug = 'Y' THEN

        cux_log.debug('p_parameter1 : ' ||p_parameter1);

     END IF;

  

     -- todo

     SELECT wip_transactions_s.nextval INTOl_group_id FROM dual;

  

     --loop

     l_txn_id      := NULL;

     l_rec_move_txn := NULL;

  

     SELECT wip_transactions_s.nextval INTOl_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 :=l_wip_entity_id;

     --l_rec_move_txn.wip_entity_name :=rec_grp.wo_no;

     l_rec_move_txn.organization_id     := l_organization_id;

     l_rec_move_txn.transaction_date    := SYSDATE;

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

     l_rec_move_txn.transaction_uom     := '个';

  

     IF l_transaction_quantity > 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)

          INTOl_rec_move_txn.fm_operation_seq_num,

               l_rec_move_txn.to_operation_seq_num

          FROM wip_operations wo

         WHERE wo.wip_entity_id =l_wip_entity_id

           AND wo.organization_id =l_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  => l_wip_entity_id,

                                                                                p_opr_seq_num    => l_rec_move_txn.fm_operation_seq_num,

                                                                                p_organization_id => l_organization_id,

                                                                                p_intraopr_step  => 1);

     

        IF l_available

           --如果可移动数量小于移动数量，则启用超量移动        

           l_rec_move_txn.overcompletion_transaction_qty := l_transaction_quantity-

                                                            l_available;

           l_rec_move_txn.overcompletion_primary_qty    := l_transaction_quantity -

                                                            l_available;

        

           x_over_qty :=l_transaction_quantity - l_available;

        

        END IF; --IF l_available

     

     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 =l_wip_entity_id

           AND wo.organization_id =l_organization_id;

     

        l_rec_move_txn.fm_intraoperation_step_type:= 3; --移动  

        l_rec_move_txn.to_intraoperation_step_type := 1; --排队

     

     END IF; -- IF p_move_qty > 0 THEN

  

     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   := 'CXY_MES_WIP_MOVE_TEST';

     l_rec_move_txn.source_line_id := 265057;

  

     BEGIN

        INSERT INTO wip_move_txn_interfaceVALUES l_rec_move_txn;

     EXCEPTION

        WHEN OTHERS THEN

        

           cux_conc_utl.log_msg('insert intowip_move_txn_interface error : ' ||

                                SQLERRM);

           RAISE fnd_api.g_exc_error;

     END;

     wip_movproc_pub.processinterface(p_txn_id      => l_txn_id,

                                      p_commit      => 'F',

                                      x_returnstatus => l_return_status,

                                      x_errormsg    => l_msg_data);

     --END IF;

  

     -- API end body

     -- end activity, include debug messagehint to exit api

     x_return_status :=cux_api.end_activity(p_pkg_name =>g_pkg_name,

                                             p_api_name => l_api_name,

                                             p_commit   => p_commit,

                                             x_msg_count => x_msg_count,

                                             x_msg_data => x_msg_data);

     RETURN l_return_status || l_msg_data;

  EXCEPTION

     WHEN fnd_api.g_exc_error THEN

        x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                     p_api_name      => l_api_name,

                                                     p_savepoint_name => l_savepoint_name,

                                                     p_exc_name      =>cux_api.g_exc_name_error,

                                                     x_msg_count     =>x_msg_count,

                                                     x_msg_data      =>x_msg_data);

     WHEN fnd_api.g_exc_unexpected_error THEN

        x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                     p_api_name      => l_api_name,

                                                     p_savepoint_name=> l_savepoint_name,

                                                     p_exc_name      =>cux_api.g_exc_name_unexp,

                                                     x_msg_count     =>x_msg_count,

                                                     x_msg_data      =>x_msg_data);

     WHEN OTHERS THEN

        x_return_status :=cux_api.handle_exceptions(p_pkg_name      => g_pkg_name,

                                                     p_api_name      => l_api_name,

                                                     p_savepoint_name => l_savepoint_name,

                                                     p_exc_name      =>cux_api.g_exc_name_others,

                                                     x_msg_count     => x_msg_count,

                                                     x_msg_data      =>x_msg_data);

  END process_wip_move;

--刘轶鹤