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;