Subinventory Transfer API

--The following code performs subinventory transfer  of an item from one subinventory to another subinventory.The lot number is chosen based on FIFO(First In First Out) basis.


DECLARE

CURSOR cur_com
IS
select * from BOM_INVENTORY_COMPONENTS_V bic,bom_bill_of_materials bom
where bom.assembly_item_id = 33003
and bic.bill_sequence_id = bom.bill_sequence_id
and bic.operation_seq_num = 10
and bom.organization_id = 81;

CURSOR cur_lot_num(p_item_id NUMBER,p_org_id NUMBER,p_subinv_code VARCHAR2)
IS
select lot_number,primary_transaction_quantity quantity from mtl_onhand_quantities_detail
where inventory_item_id = p_item_id
and organization_id = p_org_id
and subinventory_code = p_subinv_code
order by orig_date_received asc;

retval number;
ln_interface_id number;
l_return_status VARCHAR2(100);
l_msg_cnt NUMBER;
l_msg_data VARCHAR2(500);
l_trans_count VARCHAR2(100);
v_header_id number;
ln_out NUMBER;
ln_quantity NUMBER;
ln_trn_type_id NUMBER;


BEGIN

       BEGIN

         SELECT mtl_material_transactions_s.NEXTVAL
           INTO ln_interface_id
           FROM DUAL;
          
       EXCEPTION
       WHEN OTHERS THEN
      
        ln_interface_id := 0;   
          
       END;
      
       BEGIN
      
        SELECT transaction_type_id
        INTO ln_trn_type_id
        FROM mtl_transaction_types
        WHERE transaction_type_name = 'Subinventory Transfer';
       
       EXCEPTION
       WHEN OTHERS THEN
      
        ln_trn_type_id := 0;
       
       END;
      
    BEGIN       

    INSERT INTO mtl_transactions_interface
            (source_code,
            source_line_id,
            source_header_id,
            process_flag,
            transaction_mode,
            inventory_item_id,
            --revision,
            organization_id,
            subinventory_code,
            -- locator_id,
            transaction_quantity,
            transaction_uom,
            transaction_date,
            transaction_type_id,
            transfer_subinventory,
            lock_flag,
            last_update_date,
            last_updated_by,
            created_by,
            creation_date,
             --attribute1, transaction_source_id, attribute2,
             --attribute3, attribute4, transaction_source_name,
             --attribute9,
            transaction_interface_id,
            transfer_locator
            )
     VALUES ('Subinventory Transfer',
             99,
             99,
             1,
             3,
             33008,--rec_sl.inventory_item_id,
             --rec_sl.revision,
             81,--rec_sl.organization_id,
             'ASSY_IN',--From Subinventory
--             ln_to_loc_id,
             1,--transaction_quantity
             'Nos',
             SYSDATE,
             ln_trn_type_id,--Transaction Type Id
             'MRB',--To subinventory
             2,
             SYSDATE,
             0,
             0,
             SYSDATE,
             --rec_sl.attribute1, ln_wip_id, rec_sl.header_id,
             --rec_sl.attribute2, rec_sl.attribute4, lc_job_no,
             --ln_dcs_no,
             ln_interface_id,
             NULL
            );
                
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTION_INTERFACE');
       
    END;
   
    ln_quantity := 1;

 FOR rec_lot_num IN cur_lot_num(33008,81,'ASSY_IN')
 LOOP                    
   
   IF rec_lot_num.quantity <= ln_quantity AND ln_quantity <> 0 THEN
       
    ln_out := rec_lot_num.quantity;
       
    BEGIN

               INSERT INTO mtl_transaction_lots_interface
                           (transaction_interface_id,
                            lot_number,
                            lot_expiration_date,
                            transaction_quantity,
                            last_update_date,
                            last_updated_by,
                            creation_date,
                            created_by
                            --, source_code,
                            --source_line_id, process_flag, attribute9
                           )
                    VALUES (ln_interface_id,        --transaction interface id
                            rec_lot_num.lot_number,--                rec_mtl_lot.lot_number,
                            --Lot number
                            SYSDATE + 100,               --Lot expiration date
                            rec_lot_num.quantity,--              ln_qty,       --transaction quantity
                            SYSDATE,                        --last update date
                            0,       --last updated by
                            SYSDATE,                           --creation date
                            0
                            --,'Subinventory Transfer',
                            --rec_sl.line_id, 1, ln_dcs_no
                           );                      
                          
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE');
       
    END;
   
   ELSE
  
    BEGIN

               INSERT INTO mtl_transaction_lots_interface
                           (transaction_interface_id,
                            lot_number,
                            lot_expiration_date,
                            transaction_quantity,
                            last_update_date,
                            last_updated_by,
                            creation_date,
                            created_by
                            --, source_code,
                            --source_line_id, process_flag, attribute9
                           )
                    VALUES (ln_interface_id,        --transaction interface id
                            rec_lot_num.lot_number,--                rec_mtl_lot.lot_number,
                            --Lot number
                            SYSDATE + 100,               --Lot expiration date
                            ln_quantity,--              ln_qty,       --transaction quantity
                            SYSDATE,                        --last update date
                            0,       --last updated by
                            SYSDATE,                           --creation date
                            0
                            --,'Subinventory Transfer',
                            --rec_sl.line_id, 1, ln_dcs_no
                           );                      
                          
    EXCEPTION
    WHEN OTHERS THEN
     
        dbms_output.put_line('Error while inserting into MTL_TRANSACTIONS_LOT_INTERFACE');
       
    END;
   
    EXIT;    
   
   END IF;
  
   ln_quantity := ln_quantity - ln_out;
  
  
 END LOOP; 
     
      BEGIN     
                          
                          
         SELECT mtl_material_transactions_s.NEXTVAL
           INTO v_header_id
           FROM DUAL;
          
      EXCEPTION
      WHEN OTHERS THEN
     
        v_header_id := 0;    
          
      END;              
     
      BEGIN 
          
            UPDATE mtl_transactions_interface
               SET transaction_header_id = v_header_id,
                   --TRANSACTION_INTERFACE_ID =v_header_id,
                   transaction_date = SYSDATE
             WHERE source_code = 'Subinventory Transfer' AND transaction_header_id IS NULL;
            
      EXCEPTION
      WHEN OTHERS THEN
     
        dbms_output.put_line('Error while updating MTL_TRANSACTIONS_INTERFACE');
       
      END;        
                       
--      COMMIT;                 
                          
         retval :=
            apps.inv_txn_manager_pub.process_transactions
                                          (p_api_version           => 1.0,
                                           p_init_msg_list         => 'T',
                                           p_commit                => 'T',
                                           p_validation_level      => 100,
                                           x_return_status         => l_return_status,
                                           x_msg_count             => l_msg_cnt,
                                           x_msg_data              => l_msg_data,
                                           x_trans_count           => l_trans_count,
                                           p_table                 => 1,
                                           p_header_id             => v_header_id
                                          );
   
dbms_output.put_line('l_msg_data:'||l_msg_data);
dbms_output.put_line('l_return_status:'||l_return_status);
dbms_output.put_line('v_header_id:'||v_header_id);
dbms_output.put_line('retval:'||v_header_id);
                                     
END;