TDI Upgrade

From PTAGISWiki

Contents 1 Introduction 2 Problems 2.1 Performance Period 2.2 Inconsistent Names and Addresses 2.3 Duplicate Fiscal Information 2.4 Obsolete Proprietary Database Connector 2.5 Other Problems 2.6 New BPA Requirements 3 System Context Model 3.1 Tag Forecast and Approval Process 3.2 BPA Orders Tags 3.3 Digital Angel / Destron Fearing Ship Tags 3.4 PTAGIS Receives Tags Into Inventory 3.5 Users Request Tag Distribution 3.6 PTAGIS Ships Tags for Approved Distribution Requests 4 System Transition 4.1 People and Project Information 4.2 Historic Distribution Information 4.3 Contract and Purchasing Information 5 Logical Data Model

Introduction

The TDI application will be upgraded in the latter half of the PTAGIS 2007 fiscal year. The upgrade will make obsolete the legacy TDI Windows application and replace it with web based functionality. The new system is called the Tag Distribution System (TDS).

The Tag Forecast Web Application was deployed in production in mid-September. 2008 for the purpose of collecting the 2008 tag forecasts.

Initial design documentation ( Tag Distribution Schema Version 1.0) has been developed.

Problems

There are several reasons for the upgrade. First of all, the existing TDI schema does not meet the requirements of the current day:

Performance Period

TDI was designed to track BPA Project numbers by performance period. However, PTAGIS has no mechanism to verify that a BPA Project's tag distribution should be associated with a specific performance period. As a result, a number of projects performance periods routinely show distributions for the incorrect performance period, and the resulting distribution summary reports are incorrect. With recent changes implemented by BPA, PTAGIS is no longer required to track these project periods.

Inconsistent Names and Addresses

TDI was designed to require names and addresses to be duplicated for shipping and receiving tags. This information duplicates other name and address information, which makes tracking people and organizations within PTAGIS difficult. The PTAGIS project is updating it's 'people tracking' schema, and integrating it with the LDAP user account information to provide a centralized source of people information to used, not only for TDI, but to keep track of users, steering committee, tag coordinators, project sponsors and other roles that PTAGIS tracks.

Duplicate Fiscal Information

TDI was designed to track PSMFC fiscal information for tracking contract information and purchase order information. This information is unused by PTAGIS and is duplicative of the master PSMFC fiscal department computer systems. This part of the TDI system is not used by the PTAGIS project. At the point where BPA purchases tags from the vendor directly, this part of the system will become even more burdensome and redundant.

Obsolete Proprietary Database Connector

During the Spring of 2007, the PTAGIS database was upgraded to Ingres 2006 Release 2. This is a major new release that provides several bug fixes and updated features. It was learned that the SequeLink data provider is incompatible with this Ingres release. TDI database connection code would have to be re-written with a new database interface. Due to other high priority projects, PTAGIS does not have resources to dedicate to this re-write. In addition, it would not be prudent to continue to enhance the TDI system, considering the other issues involved. In the mean time, the windows based TDI system will be run in a 'stand-alone' configuration in a separate DBMS instance (TH on Blueback).

Other Problems

A couple of 'add-on' requirements were incorporated into TDI. The "Return Material Authorization" (RMA) process was added rapidly in 2000 to handle a tag recall required because the manufacturer delivered defective tags to the PTAGIS project, and the tags were distributed to the field before the defect was identified. The second 'add-on' process was the "Alternate Distribution Process" that was added to provide information about tags distributed by other organizations. Neither of these additional processes were optimally integrated into the TDI application, and a certain amount of redesign is necessary for the future use of these subsystems.

New BPA Requirements

The TDI Upgrade will support BPA's new processes, and simplify the overall PTAGIS database design by eliminating elements (TDI database schema) that are not necessary to support the PTAGIS project: 1) the project number and performance period relationships; 2) The PSMFC Budget Modification schema; 3) The Purchase Order and Line Item relations.

Paraphrased from Jamie Swan email on 8/21/2007, here is how it will work:

1. BPA and PSMFC determine tag "needs"
2. BPA to issue PO directly to DA
3. DA will notify BPA and PSMFC Kennewick office when shipments are being sent out
4. PSMFC Kennewick office will notify BPA & DA tags are received
5. BPA will issue auto-payment to DA (Net 30)

A telephone conference was conducted on 10/1/2007, between PTAGIS, BPA, and Digital Angel to discuss the business communications necessary to implement this process. Presented here are details of a set of structured e-mail messages designed to automate much of the communications workload, as revised following a second conference call on 10/5/2007.

In addition, a new mechanism is being developed to track people, organizations and project information. In addition, TDI will provide a web-based Tag Forecast process by mid-September, 2007.

System Context Model

The following is a context model for TPW, the new, Tag Procurement Web application:

1. The initial step illustrated provide information for BPA to order the correct number of PIT tags for the coming BPA fiscal year. In step 1, PTAGIS users enter a projected request (formerly called a forecast) of the number of PIT tags required by BPA project number. The total number of tags requested can be used to determine the number of PIT tags to order from Digital Angel / Destron Fearing.
2. In step 2a the BPA PIT Tag Coordinator issues a purchase order to Digital Angel. In addition, in step 2b, this order information is sent by email, in TPA Message Format, to TagProcurement@ptagis.org.
3. In step 3a, Digital Angel / Destron Fearing sends detailed shipment information by email, in TPA Message Format to TagProcurement@ptagis.org and in 3b, a shipment email to the BPA PIT Tag Coordinator indicating that shipment has been made for the order created in step 2.
4. In step 4a, Digital Angel / Destron Fearing ships tags described in step 3 to PTAGIS. In step 4b, PTAGIS receives the tags into inventory, and (in step 4c) notifies the BPA PIT Tag Coordinator, by way of email, that the tags have received.
5. In step 5, the PTAGIS user requests shipment (to one or more locations) of tags request in step 1.
6. Step 6 shows that BPA approves the PTAGIS user tag request prior to distribution. The following steps illustrate that the TPW system generates a pick list of tags to send to the requesting project (in First Tags in, First Tags out sequence) and mailing labels. The PTAGIS Tag Coordinator packages the tags and ships them to the PTAGIS user.

Tag Forecast and Approval Process

Users are asked to make a request for the projected quantity, type of tag and month of delivery for PIT tags that their projects need prior to the beginning of the federal government fiscal year (typically August and September of each year). After the number of tags for a project are identified, the Bonneville Power Administration PIT Tag Coordinator will approve the forecasted number, approve an adjusted number, or deny the request.

After a tag projection is entered, and either before or after the forecast is approved, the user can request that tags be distributed to the project. However, tags will not be distributed until the tag forecast is approved.

The following illustrates the PIT Tag Forecast and Approval subsystem:

The diagram illustrates that the PTAGIS users use the TPW Request web application to enter expected tag usage for the next fiscal year. The BPA PIT Tag Coordinator will also use the TPW Request application to approve the tag request. The TPW application will notify the PTAGIS user upon approval (change or denial) of the PTAGIS user tag request. TPW will also provide reports about project tag usage, inventory availability, shipment status, etc.

BPA Orders Tags

The following illustrates the process by which BPA will order tags from Digital Angel / Destron Fearing:

This process is external to the TPW application. That is, the process is a business relationship between Bonneville Power Administration and Digital Angel / Destron Fearing.

However, the transaction to order tags is communicated to the PTAGIS project by a formatted email message sent to TagProcurement@ptagis.org. Email sent to this address will be processed by a computer program that will store relevant order information to the PTAGIS database. ...need authentication mechanism (econfig?)... or make a web service...

Digital Angel / Destron Fearing Ship Tags

The following illustrates the process by which PIT tags are shipped to PTAGIS to fulfill the BPA order.

The process shows that, prior to shipping, Digital Angel sends a TPA Message Type 2 to TagProcurement@ptagis.org. The message contains information about the order, shipment and detailed information on each tag in the shipment. The TPA Message Archiver is a program that loads the appropriate information into the PTAGIS database for the shipment. The TPA Message Archiver will also send an acknowledgment email to BPA and to Digital Angel indicating receipt of the TPA Message Type 2.

PTAGIS Receives Tags Into Inventory

The following illustrates the process that the PTAGIS Tag Coordinator uses to receive tags into inventory:

As illustrated, the TPW will have an application component to use to receive tags. It is envisioned that the PTAGIS tag coordinator will use a bar code scanner to scan the box codes from the tag shipments received from Digital Angel. The scanned box codes will be compared with the information stored in PTAGIS that was received from the TPA Message Type 2 that was processed by the TPA Message Archiver. All boxes that were identified by Digital Angel will be verified by the scanning process. The TPW will update the status of all tags and vials associated with the scanned box to indicate that the tags have been physically received.

In addition, after TPW receives the tag shipment (or partial shipment?) it will send an email to Digital Angel / Destron Fearing, and to the BPA PIT Tag Coordinator that the tags have been received into PTAGIS inventory.

Users Request Tag Distribution

The following illustration shows the process PTAGIS users will use to request PIT tag delivery

PTAGIS users will simply log into the TPW application (need to establish rules on who or what role is allowed to do this). The application will process the user request, and create a new task in the tag distribution work queue.

PTAGIS Ships Tags for Approved Distribution Requests

The following illustrates the process that the PTAGIS Tag Coordinator will use to ship PIT tags using TPW:

TPW will have a form that will only be available to the PTAGIS Tag Coordinator for shipping PIT tags to projects.

The PTAGIS Tag Coordinator will identify a shipment request to fill. TPW will provide a list of vials to ship, and will provide shipping labels for each shipment for the user request. The PTAGIS Tag Coordinator will physically package the vials indicated by the 'pick list', and ship them to the PTAGIS user. The 'pick list' can be shipped as the 'packing list' for the order.

Need TPW to capture the UPS shipping number for the order.

System Transition

People and Project Information

The legacy TDI system contains contact information for people in several locations (tdi_project, tdi_po, tdi_distribution and pab_contacts). Ideally, information about people would be located in a single location, and referenced by a foreign key in the subject tables.

The new web based upgrade to TDI will implement this principle through the use of reference information located in the People, Projects and Organization schema.

People in the PPO schema will be synchronized with users from the PTAGIS LDAP. Tag Distribution projects in the PPO schema will be based upon information from the Pisces application hosted by Bonneville Power Administration. Ultimately, BPA and PTAGIS will work to develop a web service that will be able to deliver information about a project upon request. In the mean time, BPA has provided people and project information in file 1 and file 2.

Describe how to merge tdi_project, file 1, file 2 and ldap information into PPO.

Historic Distribution Information

Contract and Purchasing Information

In August, 2007, BPA will purchase tags directly from Digital Angel, thereby eliminating several redundant business transactions and simplifying communication processes. Digital Angel will drop-ship tags to the PTAGIS Field Office in Kennewick for distribution.

This means that the TDI facilities to track BPA contracts and PSMFC purchase orders are no longer necessary. The TDI tables associated with these transactions (tdi_period, tdi_budget_mod, tdi_mod, tdi_po, tdi_po_line_item and tdi_tag_receipt) are not necessary. These data from these tables will be copied to flat files and archived to the ptagis ftp server when the new system is initiated.

Logical Data Model