Oracle EBS INV 创建物料搬运单

Create or Replace PROCEDURE ProcessMoveOrder
AS 
        -- Common Declarations
        l_api_version       NUMBER := 1.0; 
        l_init_msg_list     VARCHAR2(2) := FND_API.G_TRUE; 
        l_return_values    VARCHAR2(2) := FND_API.G_FALSE; 
        l_commit           VARCHAR2(2) := FND_API.G_FALSE; 
        x_return_status     VARCHAR2(2);
        x_msg_count         NUMBER := 0;
        x_msg_data         VARCHAR2(255);
    
        -- WHO columns
        l_user_id    NUMBER := -1;
        l_resp_id    NUMBER := -1;
        l_application_id  NUMBER := -1;
        l_row_cnt       NUMBER := 1;
        l_user_name    VARCHAR2(30) := 'MFG';
        l_resp_name    VARCHAR2(50) := 'Manufacturing and Distribution Manager';   
        
        -- API specific declarations
        l_header_id              NUMBER := 0;
        l_trohdr_rec             INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
        l_trohdr_val_rec         INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
        l_trolin_tbl             INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
        l_trolin_val_tbl         INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;
        x_trolin_tbl             INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
        x_trolin_val_tbl         INV_MOVE_ORDER_PUB.TROLIN_VAL_TBL_TYPE;
        x_trohdr_rec             INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
        x_trohdr_val_rec         INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;

BEGIN
 
        -- Get the user_id
        SELECT user_id
        INTO l_user_id
        FROM fnd_user
        WHERE user_name = l_user_name;
      
        -- Get the application_id and responsibility_id
        SELECT application_id, responsibility_id
        INTO l_application_id, l_resp_id
        FROM fnd_responsibility_vl
        WHERE responsibility_name = l_resp_name;
      
        FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);  -- Suhasini / Mfg  Mgr / INV
        dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );  
           
         -- Initialize the move order header
          l_trohdr_rec.date_required              :=   sysdate+2;
          l_trohdr_rec.organization_id            :=   207;  
          l_trohdr_rec.from_subinventory_code     :=   'Stores';
          l_trohdr_rec.to_subinventory_code       :=   'FGI';
          l_trohdr_rec.status_date                :=   sysdate;
          l_trohdr_rec.request_number             :=   'TEST_TRO2';
          l_trohdr_rec.header_status               :=   INV_Globals.G_TO_STATUS_PREAPPROVED;   -- preApproved
          l_trohdr_rec.transaction_type_id        :=   INV_GLOBALS.G_TYPE_TRANSFER_ORDER_SUBXFR; -- INV_GLOBALS.G_TYPE_TRANSFER_ORDER_STGXFR;  
          l_trohdr_rec.move_order_type            :=   INV_GLOBALS.G_MOVE_ORDER_REQUISITION; -- G_MOVE_ORDER_PICK_WAVE;
          l_trohdr_rec.db_flag                    :=   FND_API.G_TRUE;
          l_trohdr_rec.operation                  :=   INV_GLOBALS.G_OPR_CREATE;    
  
          -- Who columns       
          l_trohdr_rec.created_by                 :=  l_user_id;
          l_trohdr_rec.creation_date              :=  sysdate;
          l_trohdr_rec.last_updated_by            :=  l_user_id;
          l_trohdr_rec.last_update_date           :=  sysdate;

          -- create  line  for the  header created above                        
          l_trolin_tbl(l_row_cnt).date_required      :=  sysdate;                                     
          l_trolin_tbl(l_row_cnt).organization_id   :=  207;        
          l_trolin_tbl(l_row_cnt).inventory_item_id  :=  513963;       
          l_trolin_tbl(l_row_cnt).from_subinventory_code:=  'Stores';                                        
          l_trolin_tbl(l_row_cnt).to_subinventory_code  :=  'FGI';    
          l_trolin_tbl(l_row_cnt).quantity              :=  2;                                          
          l_trolin_tbl(l_row_cnt).status_date            :=  sysdate;                                      
          l_trolin_tbl(l_row_cnt).uom_code              :=  'Ea';   
          l_trolin_tbl(l_row_cnt).line_number            := l_row_cnt;                                   
          l_trolin_tbl(l_row_cnt).line_status            := INV_Globals.G_TO_STATUS_PREAPPROVED;          
          l_trolin_tbl(l_row_cnt).db_flag                := FND_API.G_TRUE;                               
          l_trolin_tbl(l_row_cnt).operation              := INV_GLOBALS.G_OPR_CREATE;                     
  
          -- Who columns
          l_trolin_tbl(l_row_cnt).created_by        := l_user_id;                           
          l_trolin_tbl(l_row_cnt).creation_date      := sysdate;                                      
          l_trolin_tbl(l_row_cnt).last_updated_by    := l_user_id;                           
          l_trolin_tbl(l_row_cnt).last_update_date  := sysdate;                                      
          l_trolin_tbl(l_row_cnt).last_update_login  := FND_GLOBAL.login_id;  

          -- call API to create move order header
         DBMS_OUTPUT.PUT_LINE('=======================================================');
         DBMS_OUTPUT.PUT_LINE('Calling INV_MOVE_ORDER_PUB.Process_Move_Order API');        
  
         INV_MOVE_ORDER_PUB.Process_Move_Order( 
                   P_API_VERSION_NUMBER   => l_api_version
                ,  P_INIT_MSG_LIST        => l_init_msg_list
                ,  P_RETURN_VALUES        => l_return_values
                ,  P_COMMIT               => l_commit
                ,  X_RETURN_STATUS        => x_return_status
                ,  X_MSG_COUNT            => x_msg_count
                ,  X_MSG_DATA             => x_msg_data
                ,  P_TROHDR_REC           => l_trohdr_rec
                ,  P_TROHDR_VAL_REC       => l_trohdr_val_rec
                ,  P_TROLIN_TBL           => l_trolin_tbl
                ,  P_TROLIN_VAL_TBL        => l_trolin_val_tbl
                ,  X_TROHDR_REC            => x_trohdr_rec
                ,  X_TROHDR_VAL_REC       => x_trohdr_val_rec
                ,  X_TROLIN_TBL            => x_trolin_tbl
                ,  X_TROLIN_VAL_TBL       => x_trolin_val_tbl          
        ); 
 
         DBMS_OUTPUT.PUT_LINE('=======================================================');
         DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
  
         IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
            DBMS_OUTPUT.PUT_LINE('Error Message :'||x_msg_data);
         END IF;
         
         IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
            DBMS_OUTPUT.PUT_LINE('Move Order Created Successfully for '||x_trolin_tbl(l_row_cnt).header_id);
         END IF; 
     
         DBMS_OUTPUT.PUT_LINE('=======================================================');
        
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :');
          DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
          DBMS_OUTPUT.PUT_LINE('=======================================================');
END ProcessMoveOrder;