WIP 完工事务处理Demo

DECLARE
  l_itface_move_rec   wip_move_txn_interface%ROWTYPE;
  l_itface_cst_rec    cst_comp_snap_interface%ROWTYPE;
  l_itface_mtran_rec  mtl_transactions_interface%ROWTYPE;
  l_itface_serial_rec mtl_serial_numbers_interface%ROWTYPE;

  l_error_status  VARCHAR2(2);
  l_error_count   NUMBER := 0;
  l_error_message VARCHAR2(10000);
  l_count         NUMBER := 0;
  l_verify_count  NUMBER;
  ln_request_no   NUMBER := 0;

  l_assembly_id     NUMBER;
  l_available_qty   NUMBER;
  l_completion_date DATE;
  l_max_seq_num     NUMBER;
  l_min_seq_num     NUMBER;
  l_wip_entity_id   NUMBER;
  l_date_released   DATE;
  l_subinventory    VARCHAR2(50);
  l_serial_ctl      NUMBER;
  l_qty_completed   NUMBER;
  l_locator_id      NUMBER;
  l_serial_num      VARCHAR2(50);

BEGIN
  fnd_global.apps_initialize(user_id => 2411, resp_id => 50647, resp_appl_id => 20003);

  dbms_output.put_line('Organization' || chr(9) || 'Job No.' || chr(9) || 'Assembly' || chr(9) || 'Quantity' || chr(9) ||
                       'Serial Number' || chr(9) || 'Completion Date' || chr(9) || 'Status' || chr(9) || 'Error Message');
  
    l_itface_move_rec   := NULL;
    l_itface_cst_rec    := NULL;
    l_itface_mtran_rec  := NULL;
    l_itface_serial_rec := NULL;
    l_itface_move_rec   := NULL;
    l_error_status      := 'S';
    l_error_message     := NULL;
    l_serial_num        := NULL;
    l_assembly_id       := NULL;
    l_available_qty     := NULL;
    l_completion_date   := NULL;
    l_max_seq_num       := NULL;
    l_min_seq_num       := NULL;
    l_wip_entity_id     := NULL;
    l_date_released     := NULL;
    l_subinventory      := NULL;
    l_serial_ctl        := NULL;
    l_qty_completed     := NULL;
    l_serial_num        := NULL;
  
    l_error_status  := 'S';
    l_error_message := NULL;
  
    l_count := l_count + 1;
  
    --Validate organization                      
    BEGIN
      SELECT ood.organization_id,
             ood.organization_code
        INTO l_itface_move_rec.organization_id,
             l_itface_move_rec.organization_code
        FROM org_organization_definitions ood
       WHERE ood.organization_code = 'WE1';
    EXCEPTION
      WHEN OTHERS THEN
        l_error_status  := 'E';
        l_error_message := 'The organization is invalid!';
    END;
  
    --Validate job                      
    IF l_itface_move_rec.organization_id IS NOT NULL THEN
      BEGIN
        SELECT wen.wip_entity_name,
               wen.wip_entity_id,
               wdj.primary_item_id,
               wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped,
               wdj.quantity_completed,
               wdj.date_released,
               wdj.completion_subinventory,
               wdj.completion_locator_id,
               wdj.attribute3 serial
          INTO l_itface_move_rec.wip_entity_name,
               l_wip_entity_id,
               l_assembly_id,
               l_available_qty,
               l_qty_completed,
               l_date_released,
               l_subinventory,
               l_locator_id,
               l_serial_num
          FROM wip_discrete_jobs wdj,
               wip_entities      wen
         WHERE wdj.wip_entity_id = wen.wip_entity_id
           AND wdj.organization_id = wen.organization_id
           AND wen.wip_entity_name = 'LCY_TEST01'
           AND wdj.organization_id = l_itface_move_rec.organization_id
           AND wdj.status_type = 3; --status released                      
      EXCEPTION
        WHEN OTHERS THEN
          l_error_status  := 'E';
          l_error_message := l_error_message || '|' || 'The job is invalid!';
      END;
    END IF;
  
    --Validate item                      
    IF l_assembly_id IS NOT NULL THEN
      BEGIN
        SELECT msi.primary_uom_code,
               msi.serial_number_control_code
          INTO l_itface_move_rec.transaction_uom,
               l_serial_ctl
          FROM mtl_system_items_b msi
         WHERE msi.inventory_item_id = l_assembly_id
           AND msi.organization_id = l_itface_move_rec.organization_id
           /*AND msi.segment1 = ''*/;
      EXCEPTION
        WHEN OTHERS THEN
          l_error_status  := 'E';
          l_error_message := l_error_message || '|' || 'The item is invalid!';
      END;
    
      IF l_subinventory IS NULL THEN
        l_error_status  := 'E';
        l_error_message := l_error_message || '|' || 'The job default subinventory is null!';
      END IF;
    END IF;
  
    IF l_available_qty < nvl(1, 0) OR nvl(1, 0) < 0 THEN
      l_error_status  := 'E';
      l_error_message := l_error_message || '|' || 'The qty is invalid!';
    END IF;
  
    --Validate completion_date                      
    BEGIN
      l_completion_date := SYSDATE;--to_date(jobc.completion_date, 'dd/mm/yyyy hh24:mi:ss');
      IF l_completion_date < l_date_released OR l_completion_date > SYSDATE THEN
        l_error_status  := 'E';
        l_error_message := l_error_message || '|' || 'The completion date should between release date and system date!';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        l_error_status  := 'E';
        l_error_message := l_error_message || '|' || 'The completion date is dd/mm/yyyy hh24:mi:ss!';
    END;
  
    --get seq                      
    IF l_wip_entity_id IS NOT NULL THEN
      SELECT MIN(wop.operation_seq_num),
             MAX(wop.operation_seq_num)
        INTO l_min_seq_num,
             l_max_seq_num
        FROM wip_operations wop
       WHERE wop.wip_entity_id = l_wip_entity_id
         AND wop.organization_id = l_itface_move_rec.organization_id;
    
      IF l_min_seq_num IS NULL OR l_wip_entity_id IS NULL THEN
        l_error_status  := 'E';
        l_error_message := l_error_message || '|' || 'Faild to get operation seq!';
      END IF;
    
      --validate serial                       
      IF l_serial_num IS NULL THEN
        IF l_serial_ctl <> 1 THEN
          l_error_status  := 'E';
          l_error_message := l_error_message || '|' || 'The assembly need a serial!';
        END IF;
        l_itface_move_rec.transaction_type := 2;
      ELSE
        IF 1 > 1 THEN
          l_error_status  := 'E';
          l_error_message := l_error_message || '|' || 'The qty can not greater than 1 with serial!';
        END IF;
      
        SELECT COUNT(*)
          INTO l_verify_count
          FROM mtl_serial_numbers msn
         WHERE msn.inventory_item_id = l_assembly_id
           AND msn.serial_number = l_serial_num
           AND msn.current_subinventory_code = l_subinventory --l_transaction_record.subinventory_code                      
           AND (msn.current_locator_id = nvl(l_locator_id, msn.current_locator_id) OR
               msn.current_locator_id IS NULL AND l_locator_id IS NULL)
           AND msn.current_organization_id = l_itface_move_rec.organization_id;
      
        IF l_verify_count > 0 THEN
          l_error_status  := 'E';
          l_error_message := l_error_message || '|' || 'The serial number is invalid!';
        END IF;
      
        l_itface_move_rec.transaction_type := 1;
      END IF;
    END IF;
  
    IF l_error_status = 'S' THEN
    
      l_itface_mtran_rec.last_update_date  := SYSDATE;
      l_itface_mtran_rec.last_updated_by   := -1;
      l_itface_mtran_rec.creation_date     := SYSDATE;
      l_itface_mtran_rec.created_by        := -1;
      l_itface_mtran_rec.last_update_login := -1;
    
      SELECT mtl_material_transactions_s.nextval INTO l_itface_mtran_rec.transaction_interface_id FROM dual;
      l_itface_mtran_rec.transaction_header_id := l_itface_mtran_rec.transaction_interface_id;
      l_itface_mtran_rec.transaction_mode      := 3;
      l_itface_mtran_rec.process_flag          := 1;
      l_itface_mtran_rec.transaction_type_id   := 44;
      l_itface_mtran_rec.transaction_source_id := l_wip_entity_id;
      l_itface_mtran_rec.operation_seq_num     := l_max_seq_num;
      l_itface_mtran_rec.organization_id       := l_itface_move_rec.organization_id;
      l_itface_mtran_rec.inventory_item_id     := l_assembly_id;
      l_itface_mtran_rec.subinventory_code     := l_subinventory;
      l_itface_mtran_rec.transaction_quantity  := 1;
      l_itface_mtran_rec.transaction_uom       := l_itface_move_rec.transaction_uom;
      l_itface_mtran_rec.transaction_date      := l_completion_date;
      l_itface_mtran_rec.final_completion_flag := 'Y';
      l_itface_mtran_rec.source_code           := 'Manual Import';
      l_itface_mtran_rec.source_header_id      := 987654321;
      l_itface_mtran_rec.source_line_id        := 987654321;
      INSERT INTO inv.mtl_transactions_interface VALUES l_itface_mtran_rec;
    
      FOR opera IN (SELECT wop.operation_seq_num
                      FROM wip_operations wop
                     WHERE wop.wip_entity_id = l_wip_entity_id
                       AND wop.organization_id = l_itface_move_rec.organization_id
                     ORDER BY wop.operation_seq_num) LOOP
        l_itface_cst_rec.transaction_interface_id := l_itface_mtran_rec.transaction_interface_id;
        l_itface_cst_rec.last_update_date         := SYSDATE;
        l_itface_cst_rec.last_updated_by          := 1124;
        l_itface_cst_rec.creation_date            := SYSDATE;
        l_itface_cst_rec.created_by               := 1124;
        l_itface_cst_rec.last_update_login        := -1;
      
        l_itface_cst_rec.wip_entity_id      := l_wip_entity_id;
        l_itface_cst_rec.operation_seq_num  := opera.operation_seq_num;
        l_itface_cst_rec.quantity_completed := 1; --l_qty_completed;                      
        l_itface_cst_rec.primary_quantity   := 1;
      
        INSERT INTO cst_comp_snap_interface VALUES l_itface_cst_rec;
      END LOOP;
    
      l_itface_serial_rec.last_update_date  := SYSDATE;
      l_itface_serial_rec.last_updated_by   := -1; --l_user_id;                      
      l_itface_serial_rec.creation_date     := SYSDATE;
      l_itface_serial_rec.created_by        := -1; --l_user_id;                      
      l_itface_serial_rec.last_update_login := -1;
    
      l_itface_serial_rec.transaction_interface_id := l_itface_mtran_rec.transaction_interface_id;
      l_itface_serial_rec.fm_serial_number         := l_serial_num;
      l_itface_serial_rec.to_serial_number         := l_serial_num;
    
      INSERT INTO mtl_serial_numbers_interface VALUES l_itface_serial_rec;
    
    ELSE
      l_error_count := l_error_count + 1;
    
    END IF;
  
    dbms_output.put_line(/*jobc.organization || chr(9) || jobc.job_no || chr(9) || jobc.assembly || chr(9) || jobc.quantity ||
                         chr(9) || jobc.serial_number || chr(9) || jobc.completion_date || chr(9) ||*/ l_error_status || chr(9) ||
                         l_error_message);
  --END LOOP;

  dbms_output.put_line('Total:' || l_count);
  dbms_output.put_line('Error:' || l_error_count);

  IF l_error_count > 0 THEN
    ROLLBACK;
  ELSE
    COMMIT;
    ln_request_no := apps.fnd_request.submit_request(application => 'INV' --cv_wip_app_name                      
                                                    ,
                                                     program     => 'INCTCM' --cv_move_api                      
                                                    ,
                                                     description => NULL);
  
    IF ln_request_no > 0 THEN
      COMMIT;
      dbms_output.put_line('Request_id:' || ln_request_no);
    ELSE
      COMMIT;
      dbms_output.put_line('Submit standrd request faild');
    END IF;
  
  END IF;
END;