Receiving Transactions Fails With RVTTH-115B

Receiving Transactions Fails With RVTTH-115B, RVTTH-115D, RVTTH-115F, RVTTH-115H, RVTTH-115J [ID 301524.1]

 

this is caused by Oracle bug, once the system with wms enabled org, we need check the status in mtrl table, wms_process_flag should = 1.

 

one time, the user perform. receive not though wms system, when we check the issue data, the data missed in mtrl org:

DECLARE
 
 l_inspection_status NUMBER;
 l_uom_code rcv_transactions.uom_code%type;
 l_reference mtl_txn_request_lines.reference%type;
 l_reference_id mtl_txn_request_lines.reference_id%type;
 l_reference_type_code mtl_txn_request_lines.reference_type_code%type;
 l_transaction_type_id mtl_txn_request_lines.transaction_type_id%type;
 l_transaction_source_type_id
 mtl_txn_request_lines.transaction_source_type_id%type;
 
 Cursor C1 is
 SELECT rs.to_organization_id organization_id
 , rs.item_id item_id
 , rs.quantity quantity
 , rs.to_org_primary_quantity primary_quantity
 , rs.unit_of_measure
 , rs.lpn_id lpn_id
 , rt.project_id project_id
 , rt.task_id
 , rs.po_line_location_id po_line_location_id
 , rs.req_line_id
 , rs.oe_order_line_id
 , rs.shipment_line_id
 , rs.rcv_transaction_id transaction_id
 , rs.item_revision
 , rt.transaction_type
 , rt.routing_header_id
 , rt.source_document_code
 FROM rcv_supply rs, rcv_transactions rt
 WHERE rs.rcv_transaction_id = rt.transaction_id
 AND rt.transaction_id=8853809;
 
 cursor mo_line(p_rcv_txn_id IN NUMBER) is
 select 1
 from    mtl_txn_request_lines
 where txn_source_id=p_rcv_txn_id;
 
 
 l_rs_rec c1%rowtype;
 l_mo_rec mo_line%rowtype;
 
 BEGIN
 Open C1;
 
 LOOP
 Fetch C1 into l_rs_rec;
 dbms_output.put_line('C1 Fetched data');
 exit when c1%notfound;
 
 IF (l_rs_rec.transaction_type = 'ACCEPT') THEN
  l_inspection_status := 2;
 ELSIF (l_rs_rec.transaction_type = 'REJECT') THEN
  l_inspection_status := 3;
 ELSIF (l_rs_rec.transaction_type = 'RECEIVE') THEN
 IF (l_rs_rec.routing_header_id = 2) THEN
    l_inspection_status := 1;
 ELSE
    l_inspection_status := NULL;
 END IF;
 END IF;
 
 IF (l_rs_rec.source_document_code = 'PO') then
  dbms_output.put_line('source do PO');
  l_reference := 'PO_LINE_LOCATION_ID';
  l_reference_id := l_rs_rec.po_line_location_id;
  l_reference_type_code := '4';
  l_transaction_type_id := '18';
  l_transaction_source_type_id := '1';
 ELSIF (l_rs_rec.source_document_code = 'RMA') then
  l_reference := 'ORDER_LINE_ID';
  l_reference_id := l_rs_rec.oe_order_line_id;
  l_reference_type_code := '7';
  l_transaction_type_id := '15';
  l_transaction_source_type_id := '12';
 ELSIF (l_rs_rec.source_document_code = 'REQ') then
  l_reference := 'SHIPMENT_LINE_ID';
  l_reference_id := l_rs_rec.shipment_line_id;
  l_reference_type_code := '8';
  l_transaction_type_id := '61';
  l_transaction_source_type_id := '7';
 ELSIF (l_rs_rec.source_document_code = 'INVENTORY') then
  l_reference := 'SHIPMENT_LINE_ID';
  l_reference_id := l_rs_rec.shipment_line_id;
  l_reference_type_code := '8';
  l_transaction_type_id := '61';
  l_transaction_source_type_id := '7';
 END IF;
 
 SELECT uom_code
 INTO l_uom_code
 FROM mtl_uom_conversions
 WHERE unit_of_measure = l_rs_rec.unit_of_measure
 AND   ROWNUM = 1;
 
 open mo_line(l_rs_rec.transaction_id);
 fetch mo_line into l_mo_rec;
 if (mo_line%notfound) then
  dbms_output.put_line('Before inserting');
 
  INSERT
  INTO mtl_txn_request_headers
  (
     HEADER_ID,            
 REQUEST_NUMBER,      
 TRANSACTION_TYPE_ID,
 MOVE_ORDER_TYPE,      
 ORGANIZATION_ID,      
 DATE_REQUIRED,        
 HEADER_STATUS,        
 STATUS_DATE,          
 LAST_UPDATED_BY,      
 LAST_UPDATE_LOGIN,    
 LAST_UPDATE_DATE,    
 CREATED_BY,          
 CREATION_DATE
  )
  select
    MTL_TXN_REQUEST_HEADERS_S.nextval,
    MTL_TXN_REQUEST_HEADERS_S.currval,
    64,
    6,
    l_rs_rec.organization_id ,   --organization_id
    sysdate,
    7,
    sysdate,
    1,
    1,
    sysdate,
    1,
    sysdate
  from dual;
 
 
  INSERT
  INTO mtl_txn_request_lines
  (
 LINE_ID,                    
 HEADER_ID,                  
 LINE_NUMBER,    
        ORGANIZATION_ID,            
 INVENTORY_ITEM_ID,          
 UOM_CODE,                    
 QUANTITY,                    
 DATE_REQUIRED,    
        REFERENCE,                  
 REFERENCE_TYPE_CODE,        
 REFERENCE_ID,                
 LINE_STATUS,                
 STATUS_DATE,      
        LAST_UPDATED_BY,            
 LAST_UPDATE_LOGIN,          
 LAST_UPDATE_DATE,            
 CREATED_BY,                  
 CREATION_DATE,    
        TXN_SOURCE_ID,              
 TRANSACTION_TYPE_ID,        
 TRANSACTION_SOURCE_TYPE_ID,
 PRIMARY_QUANTITY,            
 INSPECTION_STATUS,          
 WMS_PROCESS_FLAG ,
 LPN_ID
  )
  select
     MTL_TXN_REQUEST_LINES_S.nextval,
     MTL_TXN_REQUEST_HEADERS_S.currval,
     '1',
     l_rs_rec.organization_id ,     -- organization_id
     l_rs_rec.item_id ,   --item_id
     l_uom_code,    -- uom
     l_rs_rec.quantity ,     -- qty
     sysdate,                  
     l_reference,
     l_reference_type_code,                    
     l_reference_id ,   -- pll_id
     '7',          
     sysdate,                
     1,
 1,
     sysdate,                  
     1,
     sysdate,                
     l_rs_rec.transaction_id,  --txn source_id
     l_transaction_type_id,                    
 l_transaction_source_type_id,  
        l_rs_rec.primary_quantity,  -- primary quantity
     l_inspection_status,                    
 '1' ,
     l_rs_rec.lpn_id
   from dual;
 
 dbms_output.put_line('After inserting');
 else
  update mtl_txn_request_lines
  set       lpn_id= l_rs_rec.lpn_id,
              line_status=7,
          wms_process_flag=1,
          quantity_detailed=null,
          quantity_delivered=null
  where  txn_source_id= l_rs_rec.transaction_id;
 end if;
 
 CLOSE mo_line ;
 END LOOP;
 
 close C1;
 COMMIT;
 
 EXCEPTION
 when others then
 if C1%isopen then
  close C1;
 end if;
 dbms_output.put_line('Error :'||SQLERRM);
 ROLLBACK;
 END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15225049/viewspace-720195/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15225049/viewspace-720195/