Cacher is the code snippet organizer for pro developers

Vendor Update Interface Notes

Views

vw_ellipse_vendor_update2 is created via update_table and vw_ellipse_vendor_xfer. It only has data when it's coming time to interface stuff over, it appears.

vw_ellipse_vendor_xfer is created via vendor, vendorcontact, vendoraddress, vendorphone, vendorclass, ethnicitytype, and it seems to have all data, or at least a large amount of data. Here are the views associated:

• vw_ellipse_ownertypecode_wb - ownertypecode
• vw_ellipse_ownertypecode_mb - ownertypecode
• vw_ellipse_businesstype - bustypecode
• vw_ellipse_dbecertification - certstatuscode, certdate, certexpirdate
• vw_ellipse_contractorcode - contractorcode

Presumably we will need to capture Master flag (and then specifically only SELECT master result) in one of the views. It should be the "first" view, though it looks things will be more complex than the last view update.

update_table looks like a table which shows all the things to be inserted/updated/deleted for a vendor. Each row is only filled with data relevant to that TableName. For example, VENDORPHONE row only has data in the phone number columns. I'm not sure when\where update_table is filled with data. It is deleted at the end of VendorUpdate if something is "true" though. Ohterwise, VendorUpdate just selects on the table and grabs what is there already. Does not look to be filled via the interface.

VendorUpdate only goes to UPDATE_TABLE where tablename is VENDOR and updateflag = '1'. This yields vendorname, vendorstatus, fedtaxidnum.

Thoughts

• Add "vendor.master" to select statement in vw_ellipse_vendor_xfer . This way it still has all its records, just with an additional point of information.
• Then we can update vw_ellipse_vendor_update2 to restrict its results to WHERE vendor.master = '1'.

This should work because vw_ellipse_vendor_update2 is only utilized in the Interface Service file for VendorUpdate. Which is where we want it to only pull the Master-specific information.

Trinet quote: "Before MVMLPC updates are pushed live, the Ellipse interface will need to be updated to look at records in the vendor table that have changed, and check to see if that vendor is a master vendor (flag in the vendor table) and if so, will grab the Master VendorID from the master table and will only send updates to the mater record information (master name, master address)."

As is, vw_ellipse_vendor_update2 has all updates, which means the same TAXIDNUM company is showing up in the results with multiple vendornums (master and sub accounts). But we do not want to send it all - just master. Since this is where the VendorUpdate interface pulls from, it's where we should restrict results.

Code Steps

Step 1: Defining variables

string str1 = aNode.SelectSingleNode("OutputFileFolder").InnerXml + aNode.SelectSingleNode("VendorFilename").InnerXml;
string str2 = aNode.SelectSingleNode("OutputFileFolder").InnerXml + aNode.SelectSingleNode("CommCodeFilename").InnerXml;
string ellipseDateTimeSpec = this.getEllipseDateTimeSpec(aNode.SelectSingleNode("OutputFileExtension").InnerXml);
string path1 = str1 + ellipseDateTimeSpec;
string path2 = str2 + ellipseDateTimeSpec;
bool flag1 = false;
bool flag2 = false;
bool flag3 = false;

Step 2: Set up DataTable and StreamWriter

Creates DataTable (our data to input into files) from vw_ellipse_vendor_update2.

if (aNode.SelectSingleNode("ClearUpdate").InnerXml == "true")
    flag3 = true;
OracleConnection cn = this.OpenOracleConnection(this.connectionString);
int num1 = 0;
DataTable dataTable1 = this.ConnectOracle("SELECT * from vw_ellipse_vendor_update2 Order by VendorNum", cn);
StreamWriter streamWriter1;

Step 3: Prepare path to write interface files

Create, Append, or Delete files and set StreamWriter to necessary path.

if (!System.IO.File.Exists(path1))
    streamWriter1 = System.IO.File.CreateText(path1);
else if (flag2)
{
    streamWriter1 = System.IO.File.AppendText(path1);
}
else
{
    if (!flag1)
        return "01|VendorUpdate Error-Vendor file exists";
    System.IO.File.Delete(path1);
    streamWriter1 = System.IO.File.CreateText(path1);
}
StreamWriter streamWriter2;
if (!System.IO.File.Exists(path2))
    streamWriter2 = System.IO.File.CreateText(path2);
else if (flag2)
{
    streamWriter2 = System.IO.File.AppendText(path2);
}
else
{
    if (!flag1)
        return "02|VendorUpdate Error-CommCode file exists";
    System.IO.File.Delete(path2);
    streamWriter2 = System.IO.File.CreateText(path2);
}

Step 4: Write data from DataTable1

foreach (DataRow row1 in (InternalDataCollectionBase) dataTable1.Rows)
{
    ++num1;
    string str3 = "U";
    string str4 = "";
    string str5 = "Y";
    try
    {
        Decimal num2;
        if (row1.ItemArray[0] != DBNull.Value)
        {
            streamWriter1.Write(((Decimal) row1.ItemArray[0]).ToString().PadLeft(5, '0'));
            num2 = (Decimal) row1.ItemArray[0];
            str4 = num2.ToString();
        }
        else
            streamWriter1.Write("".PadRight(5, ' '));
        DataTable dataTable2 = this.ConnectOracle("Select * from update_table where vendornum = '" + str4 + "' and tablename = 'VENDOR' and updateflag = '1'", cn);
        if (dataTable2.Rows.Count > 0)
            str3 = "A";
        dataTable2.Dispose();
        DataTable dataTable3 = this.ConnectOracle("Select * from vw_ellipse_vendor_commcodes where vendornum = '" + str4 + "'", cn);
        if (dataTable3.Rows.Count == 0)
            str5 = "N";

Go through each row of the DataTable1.

If the row's first record isn't null, then write the item to file. Also set it to be value of num2 and str4.

Else write blank spaces to file. Create DataTable2 from a query to update_table.

If there is 1 or more rows in DataTable 2, set str3 equal to "A".

Dispose of DataTable2.

Create DataTable3 from query on vw_ellipse_vnedor_commcodes.

If DataTable3 has no rows, then set str5 equal to "N".

Step 5: Write data from DataTable3

foreach (DataRow row2 in (InternalDataCollectionBase) dataTable3.Rows)
{
    StreamWriter streamWriter3 = streamWriter2;
    num2 = (Decimal) row2.ItemArray[0];
    string str6 = num2.ToString().PadLeft(5, '0');
    streamWriter3.Write(str6);
    streamWriter2.Write(str3);
    StreamWriter streamWriter4 = streamWriter2;
    num2 = (Decimal) row2.ItemArray[1];
    string str7 = num2.ToString().PadRight(8, ' ');
    streamWriter4.Write(str7);
    string strIn = (string) row2.ItemArray[2];
    streamWriter2.WriteLine(this.EllipseFormatRight(strIn, 40, ' '));
}
dataTable3.Dispose();

Go through each row of DataTable3.

Get 0th item from DataTable3 in X row and set it to be value of num2 and str6. Write via streamWriter3 and 2.

Get 1st item from DataTable3 in X row and set it to be value of num2 and str7. Write via streamWriter4.

Get 2nd item from DataTable3 in X row and set it to be value for strIn. Write via streamWriter2.

Dispose of dataTable3.

Step 6: Continue to write from DataTable1 results

if (row1.ItemArray[1] != DBNull.Value)
    streamWriter1.Write(this.EllipseFormatRight(((string) row1.ItemArray[1]).Replace('\n', ' '), 32, ' '));
else
    streamWriter1.Write("".PadRight(32, ' '));
if (row1.ItemArray[2] != DBNull.Value)
    streamWriter1.Write(this.EllipseFormatRight((string) row1.ItemArray[2], 30, ' '));
else
    streamWriter1.Write("".PadRight(30, ' '));
//continues many times below...

Step 7: Open DataTable4 connection

DataTable dataTable4 = this.ConnectOracle("select PassWord,Answer from vendor where vendornum = '" + str4 + "'", cn);
if (dataTable4.Rows[0].ItemArray[0] == DBNull.Value || dataTable4.Rows[0].ItemArray[1] == DBNull.Value)
    str5 = "N";
streamWriter1.WriteLine(str5);
dataTable4.Dispose();

Create DataTable4 from vendor table.

If the 0th item in 0th row is null OR the 1st item in 0th row is null then set str5 equal to N.

Write str5 using streamWriter1.

Dispose of dataTable4.

---

Step 8: Optional - CATCH

catch (SystemException ex)
{
    this.LogEntry("VendorUpdate, recnbr-" + num1.ToString() + ", " + ex.Message, true);
    return "03|Vendor Record Failure";
}

If anything fails during the try block (which entails writing all the previous data to files using streamWriters), update the log with an error message.

Step 9: Optional DB deletion

if (flag3)
    this.RunOracleNonQuery("delete from update_table where vendornum = " + str4, cn);

Flag3 defaults to false. It only becomes true in this circumstance: if (aNode.SelectSingleNode("ClearUpdate").InnerXml == "true").

If that is the case, then delete record from update_table where vendornum = {a specific solicitnum}.

---

Step 10: Close up process

streamWriter1.Close();
streamWriter2.Close();
dataTable1.Dispose();
return "00|VendorUpdate successful-" + num1.ToString() + " records";

Close the streamWriters. Update the log with a success message.