protected void AcknowledgeInboundEDIFiles()
{
try
{
ediDocument oEdiDoc = null;
ediDataSegment oSegment = null;
ediAcknowledgment oAck = null;
ediSchemas oSchemas = null;
string sSegmentID;
string sLoopSection;
int nArea;
string sPrevEdiFile = "";
bool bInbound997True = false;
// string sPath = AppDomain.CurrentDomain.BaseDirectory;
SafeAndEdiFilePath objSafeAndEdiFilePath = new SafeAndEdiFilePath();
EdiFilesPaths EdiFilePath = objSafeAndEdiFilePath.GetFilesAndPaths();
// string sPath = @"" + ConfigurationManager.AppSettings["AppPath"] + @"\";
string sPath = EdiFilePath.sPath;
SqlConnection oConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["EdiDb"].ConnectionString);
string sInboundPath = EdiFilePath.sInboundPath;//sPath + ConfigurationManager.AppSettings["EDI_Inbound"] + @"\";
string sOutboundPath = EdiFilePath.sOutboundPath;//sPath + ConfigurationManager.AppSettings["EDI_Outbound"] + @"\";
string sAcceptPath = EdiFilePath.sAcceptPath;//sPath + ConfigurationManager.AppSettings["EDI_Accepted"] + @"\";
string sRejectPath = EdiFilePath.sRejectPath;//sPath + ConfigurationManager.AppSettings["EDI_Rejected"] + @"\";
string s997Path = EdiFilePath.s997Path;//sPath + ConfigurationManager.AppSettings["EDI_997"] + @"\";
string sSefPath = EdiFilePath.sSefPath;//sPath + ConfigurationManager.AppSettings["Seffolder"] + @"\";
string sEdiDonePath = EdiFilePath.sEdiDonePath;//sPath + ConfigurationManager.AppSettings["EDI_DONE"] + @"\";
int nFileCount = 0;
string[] sEdiPathFiles = Directory.GetFiles(sInboundPath);
if (sEdiPathFiles.Length == 0)
{
//MessageBox.Show("There are no files in the EDI_Inbound folder.");
LogLibrary.WriteErrorLog(" on AcknowledgeInboundEDIFiles" + "There are no files in the EDI_Inbound folder.");
}
else
{
System.Windows.Forms.Cursor Cursor = System.Windows.Forms.Cursors.WaitCursor;
oConnection.Open();
string sSql = "select * from [Config] ";
SqlDataAdapter oDaConfig = new SqlDataAdapter(sSql, oConnection);
DataSet oConfigDs = new DataSet("dsConfig");
oDaConfig.Fill(oConfigDs, "dsConfig");
DataRow oConfigRow = oConfigDs.Tables["dsConfig"].Rows[0];
string sSenderIdQlfr = oConfigRow["SenderIdQlfr"].ToString();
string sSenderId = oConfigRow["SenderId"].ToString();
string sReceiverIdQlfr = oConfigRow["ReceiverIdQlfr"].ToString();
string sReceiverId = oConfigRow["ReceiverId"].ToString();
string sControlNumber = oConfigRow["ControlNumber"].ToString();
string sAcknowledgmentRequested = oConfigRow["AcknowledgmentRequested"].ToString();
string sUsageIndicator = oConfigRow["UsageIndicator"].ToString();
string sComponentElementSeparator = oConfigRow["ComponentElementSeparator"].ToString();
Int32 nControlNumber = Convert.ToInt32(sControlNumber);
ediDocument.Set(ref oEdiDoc, new ediDocument());
//By default, FREDI uses the universal coordinated time (UTC), however you can change it to local time
oEdiDoc.set_Option(DocumentOptionIDConstants.OptDocument_UseLocalTime, 1);
// Disabling the internal standard reference library to makes sure that
// FREDI uses only the SEF file provided
ediSchemas.Set(ref oSchemas, (ediSchemas)oEdiDoc.GetSchemas());
oSchemas.EnableStandardReference = false;
// This makes certain that the EDI file must use the same version SEF file, otherwise the process will stop.
oSchemas.set_Option(SchemasOptionIDConstants.OptSchemas_VersionRestrict, 1);
// By setting the cursor type to ForwardOnly, FREDI does not load the entire file into memory, which
// improves performance when processing larger EDI files.
oEdiDoc.CursorType = DocumentCursorTypeConstants.Cursor_ForwardOnly;
// If an acknowledgment file has to be generated, an acknowledgment object must be created, and its
// property must be enabled before loading the EDI file.
oAck = (ediAcknowledgment)oEdiDoc.GetAcknowledgment();
oAck.EnableFunctionalAcknowledgment = true;
// Load all SEF files from SEF folder. FREDI will automaticall select the appropriate one.
string[] sSefPathFiles = Directory.GetFiles(sSefPath);
foreach (string sSefPathFile in sSefPathFiles)
{
oEdiDoc.LoadSchema(sSefPathFile, 0);
} // foreach
string sDestTempFile = "dest_temp.txt";
using (Stream destStream = File.OpenWrite(sDestTempFile))
{
foreach (string sEdiPathFile in sEdiPathFiles)
{
nFileCount = nFileCount + 1;
string sEdiFile = Path.GetFileName(sEdiPathFile);
DateTime dtEdiFileCreation = File.GetCreationTime(sEdiPathFile);
string sEdiFileTranSetNo = "";
string sInbound997GroupContolNo = "";
string sInbound997TranSetNo = "";
string sInbound997Ack501 = "";
// Loads EDI file and the corresponding SEF file
oEdiDoc.LoadEdi(sEdiPathFile);
// This loop will generate a 997 EDI file if the inbound file is itslef not a 997
// Gets the first data segment in the EDI files
ediDataSegment.Set(ref oSegment, (ediDataSegment)oEdiDoc.FirstDataSegment); //oSegment = (ediDataSegment) oEdiDoc.FirstDataSegment
// This loop iterates though the EDI file (850 or 997) a segment at a time
while (oSegment != null)
{
// A segment is identified by its Area number, Loop section and segment id.
sSegmentID = oSegment.ID;
sLoopSection = oSegment.LoopSection;
nArea = oSegment.Area;
if (nArea == 1)
{
if (sLoopSection == "")
{
if (sSegmentID == "ST")
{
sEdiFileTranSetNo = oSegment.get_DataElementValue(1);
if (sEdiFileTranSetNo == "997")
{
bInbound997True = true;
}
else
{
bInbound997True = false;
}
}
else if (sSegmentID == "AK1")
{
sInbound997GroupContolNo = oSegment.get_DataElementValue(2);
} // sSegmentID
}
else if (sLoopSection == "AK2")
{
if (sSegmentID == "AK2")
{
sInbound997TranSetNo = oSegment.get_DataElementValue(2);
}
else if (sSegmentID == "AK5")
{
sInbound997Ack501 = oSegment.get_DataElementValue(1);
} // sSegmentID
} // sLoopSection
} // nArea
//get next data segment
ediDataSegment.Set(ref oSegment, (ediDataSegment)oSegment.Next()); //oSegment = (ediDataSegment) oSegment.Next();
}
// This reads the 997 object that was generated in the above loop to tell us
// if the EDI file received should be accpeted or rejected. (This does not translate an inbound 997 EDI file.)
if (!bInbound997True) // if not an inbound 997 file
{
// Checks the 997 acknowledgment file just created.
// The 997 file is an EDI file, so the logic to read the 997 Functional Acknowledgemnt file is similar
// to translating any other EDI file.
// Gets the first segment of the 997 acknowledgment file
ediDataSegment.Set(ref oSegment, (ediDataSegment)oAck.GetFirst997DataSegment()); //oSegment = (ediDataSegment) oAck.GetFirst997DataSegment();
bool bFileAccepted = true;
while (oSegment != null)
{
nArea = oSegment.Area;
sLoopSection = oSegment.LoopSection;
sSegmentID = oSegment.ID;
if (nArea == 1)
{
if (sLoopSection == "")
{
if (sSegmentID == "AK9")
{
if (oSegment.get_DataElementValue(1, 0) == "R")
{
bFileAccepted = false;
}
}
} // sLoopSection == ""
} //nArea == 1
ediDataSegment.Set(ref oSegment, (ediDataSegment)oSegment.Next()); //oSegment = (ediDataSegment) oSegment.Next();
} //oSegment != null
if (bFileAccepted)
{
// All accepted EDI files are sent to the ACCEPTED folder and in their corresponding subfolders.
File.Copy(sInboundPath + sEdiFile, sAcceptPath + "\\" + sEdiFileTranSetNo + "\\" + sEdiFileTranSetNo + "_" + dtEdiFileCreation.ToString("yyyyMMddHHmmss") + "_" + sEdiFile, true);
}
else
{
File.Copy(sInboundPath + sEdiFile, sRejectPath + sEdiFileTranSetNo + "_" + dtEdiFileCreation.ToString("yyyyMMddHHmmss") + "_" + sEdiFile, true);
InsertRejectedFile(sEdiFile, sRejectPath, "EDI_Rejected", sEdiFileTranSetNo, dtEdiFileCreation.ToString("yyyyMMddHHmmss"));
}
// Combine all EDI files. This file will be read to create one 997 acknowledgment file for all incoming EDI files.
using (Stream ediStream = File.OpenRead(sInboundPath + sEdiFile))
{
ediStream.CopyTo(destStream);
}
} // if !bInbound997True
else
{
File.Copy(sInboundPath + sEdiFile, s997Path + sEdiFileTranSetNo + "_" + dtEdiFileCreation.ToString("yyyyMMddHHmmss") + "_" + sEdiFile, true);
} // if bInbound997True
if (File.Exists(sInboundPath + sPrevEdiFile))
{
File.Delete(sInboundPath + sPrevEdiFile);
}
sPrevEdiFile = sEdiFile;
} // foreach sEdiPathFile
} // using
// dispose old ack
oAck.Dispose();
// instantiate new ack for combined 997
oAck = (ediAcknowledgment)oEdiDoc.GetAcknowledgment();
oAck.EnableFunctionalAcknowledgment = true;
string sIsaControlNoBuff = "000000000" + sControlNumber.Trim();
string sIsaControlNo = sIsaControlNoBuff.Substring(sIsaControlNoBuff.Length - 9);
// Set the starting point of the control numbers in the acknowledgment
oAck.set_Property(AcknowledgmentPropertyIDConstants.PropertyAck_StartInterchangeControlNum, sIsaControlNo);
oAck.set_Property(AcknowledgmentPropertyIDConstants.PropertyAck_StartGroupControlNum, sControlNumber);
oAck.set_Property(AcknowledgmentPropertyIDConstants.PropertyAck_StartTransactionSetControlNum, 1);
oAck.set_Option(AcknowledgmentOptionIDConstants.OptAcknowledgment_ReportToSingleInterchange, 1);
oAck.set_Option(AcknowledgmentOptionIDConstants.OptAcknowledgment_ShowReportingLevel, 0);
//create combined acknowledgment
oEdiDoc.LoadEdi(sDestTempFile);
ediDataSegment.Set(ref oSegment, (ediDataSegment)oEdiDoc.FirstDataSegment);
while (oSegment != null)
{
ediDataSegment.Set(ref oSegment, oSegment.Next());
}
oAck.Save(sOutboundPath + "997_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".X12");
//increment Interchange control number
sControlNumber = (Convert.ToInt32(sControlNumber) + 1).ToString();
sSql = @"UPDATE [Config] SET ControlNumber = @ControlNumber";
oDaConfig.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaConfig.UpdateCommand.Parameters.AddWithValue("@ControlNumber", sControlNumber);
oDaConfig.UpdateCommand.ExecuteNonQuery();
oConnection.Close();
//Close oEdiDoc to release EDI file
oEdiDoc.Close();
File.Delete(sDestTempFile);
Cursor = System.Windows.Forms.Cursors.Default;
if (File.Exists(sInboundPath + sPrevEdiFile))
{
File.Delete(sInboundPath + sPrevEdiFile);
}
//tblMessage.Style.Add("display", "block");
//lblMessage.Text = "Done. " + nFileCount.ToString() + " file(s) received.";
} // if sEdiPathFiles.Length
}
catch (Exception ex)
{
throw ex;
LogLibrary.WriteErrorLog("Error = " + ex.Message);
}
}
public void Translate(string sEdiPathFile)
{
string sSegmentID;
string sLoopSection;
int nArea;
string sSql = "";
nInterkey = 0;
nGroupkey = 0;
nHeaderkey = 0;
nDetailkey = 0;
nProdActivityKey = 0;
// Specify EDI document to read.
oEdiDoc.LoadEdi(sEdiPathFile);
// Gets the first data segment in the EDI document.
ediDataSegment.Set(ref oSegment, oEdiDoc.FirstDataSegment);
// Loop that will traverse through the EDI document from top to bottom. This
// is required for FORWARD-ONLY cursor.
while (oSegment != null)
{
sSegmentID = oSegment.ID;
sLoopSection = oSegment.LoopSection;
nArea = oSegment.Area;
if (nArea == 0)
{
if (sLoopSection == "")
{
if (sSegmentID == "ISA")
{
//insert a record into interchange table
sSql = @"INSERT INTO [InterchangeInbound] ( ISA01_AuthorizationInfoQlfr, ISA02_AuthorizationInfo, ISA03_SecurityInfoQlfr,
ISA04_SecurityInfo, ISA05_SenderIdQlfr, ISA06_SenderId, ISA07_ReceiverIdQlfr, ISA08_ReceiverId, ISA09_Date, ISA10_Time,
ISA11_RepetitionSeparator, ISA12_ControlVersionNumber, ISA13_ControlNumber, ISA14_AcknowledgmentRequested, ISA15_UsageIndicator,
ISA16_ComponentElementSeparator)
values
(@ISA01_AuthorizationInfoQlfr, @ISA02_AuthorizationInfo, @ISA03_SecurityInfoQlfr,
@ISA04_SecurityInfo, @ISA05_SenderIdQlfr, @ISA06_SenderId, @ISA07_ReceiverIdQlfr, @ISA08_ReceiverId, @ISA09_Date, @ISA10_Time,
@ISA11_RepetitionSeparator, @ISA12_ControlVersionNumber, @ISA13_ControlNumber, @ISA14_AcknowledgmentRequested, @ISA15_UsageIndicator,
@ISA16_ComponentElementSeparator);
SELECT scope_identity()";
oDaInterchange.InsertCommand = new SqlCommand(sSql, oConnection);
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA01_AuthorizationInfoQlfr", oSegment.get_DataElementValue(1, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA02_AuthorizationInfo", oSegment.get_DataElementValue(2, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA03_SecurityInfoQlfr", oSegment.get_DataElementValue(3, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA04_SecurityInfo", oSegment.get_DataElementValue(4, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA05_SenderIdQlfr", oSegment.get_DataElementValue(5, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA06_SenderId", oSegment.get_DataElementValue(6, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA07_ReceiverIdQlfr", oSegment.get_DataElementValue(7, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA08_ReceiverId", oSegment.get_DataElementValue(8, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA09_Date", oSegment.get_DataElementValue(9, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA10_Time", oSegment.get_DataElementValue(10, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA11_RepetitionSeparator", oSegment.get_DataElementValue(11, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA12_ControlVersionNumber", oSegment.get_DataElementValue(12, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA13_ControlNumber", oSegment.get_DataElementValue(13, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA14_AcknowledgmentRequested", oSegment.get_DataElementValue(14, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA15_UsageIndicator", oSegment.get_DataElementValue(15, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA16_ComponentElementSeparator", oSegment.get_DataElementValue(16, 0));
nInterkey = (Int32)(decimal)oDaInterchange.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "GS")
{
//insert a record into FunctionGrop table
sSql = @"INSERT INTO [FunctionalGroupInbound] (InterchangeKey, GS01_FunctionalIdfrCode, GS02_SendersCode, GS03_ReceiversCode, GS04_Date, GS05_Time,
GS06_GroupControlNumber, GS07_ResponsibleAgencyCode, GS08_VersionReleaseCode)
values
(@InterchangeKey, @GS01_FunctionalIdfrCode, @GS02_SendersCode, @GS03_ReceiversCode, @GS04_Date, @GS05_Time,
@GS06_GroupControlNumber, @GS07_ResponsibleAgencyCode, @GS08_VersionReleaseCode);
SELECT scope_identity()";
oDaFuncGroup.InsertCommand = new SqlCommand(sSql, oConnection);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@InterchangeKey", nInterkey);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS01_FunctionalIdfrCode", oSegment.get_DataElementValue(1, 0)); //Functional Identifier Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS02_SendersCode", oSegment.get_DataElementValue(2, 0)); //Application Sender's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS03_ReceiversCode", oSegment.get_DataElementValue(3, 0)); //Application Receiver's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS04_Date", oSegment.get_DataElementValue(4, 0)); //Date
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS05_Time", oSegment.get_DataElementValue(5, 0)); //Time
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS06_GroupControlNumber", oSegment.get_DataElementValue(6, 0)); //Group Control Number
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS07_ResponsibleAgencyCode", oSegment.get_DataElementValue(7, 0)); //Responsible Agency Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS08_VersionReleaseCode", oSegment.get_DataElementValue(8, 0)); //Version / Release / Industry Identifier Code
nGroupkey = (Int32)(decimal)oDaFuncGroup.InsertCommand.ExecuteScalar();
}
}
}
else if (nArea == 1)
{
if (sLoopSection == "")
{
if (sSegmentID == "ST")
{
sSql = @"INSERT INTO [852_Header] ( FunctionalGroupKey, ST01_TranSetIdfrCode, ST02_TranSetControlNo,
XQ02_ReportingDate, XQ03_ReportingEndDate,
N902_InternalVendorNo, N903_FreeFormDesc,
CTT01_NumberOfLineItems)
values
(@FunctionalGroupKey, @ST01_TranSetIdfrCode, @ST02_TranSetControlNo,
@XQ02_ReportingDate, @XQ03_ReportingEndDate,
@N902_InternalVendorNo, @N903_FreeFormDesc,
@CTT01_NumberOfLineItems);
SELECT scope_identity()";
oDaHeader.InsertCommand = new SqlCommand(sSql, oConnection);
oDaHeader.InsertCommand.Parameters.AddWithValue("@FunctionalGroupKey", nGroupkey);
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST01_TranSetIdfrCode", oSegment.get_DataElementValue(1, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST02_TranSetControlNo", oSegment.get_DataElementValue(2, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@XQ02_ReportingDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@XQ03_ReportingEndDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N902_InternalVendorNo", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N903_FreeFormDesc", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTT01_NumberOfLineItems", "");
nHeaderkey = (Int32)(decimal)oDaHeader.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "XQ")
{
sSql = @"UPDATE [852_Header] SET XQ02_ReportingDate = @XQ02_ReportingDate, XQ03_ReportingEndDate = @XQ03_ReportingEndDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@XQ02_ReportingDate", oSegment.get_DataElementValue(2, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@XQ03_ReportingEndDate", oSegment.get_DataElementValue(3, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N9")
{
sSql = @"UPDATE [852_Header] SET N902_InternalVendorNo = @N902_InternalVendorNo, N903_FreeFormDesc = @N903_FreeFormDesc
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N902_InternalVendorNo", oSegment.get_DataElementValue(2, 0)); // Reference Identification (127)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N903_FreeFormDesc", oSegment.get_DataElementValue(3, 0)); // Free-form Description (369)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
} //sLoopSection == ""
}
else if (nArea == 2)
{
if (sLoopSection == "LIN")
{
if (sSegmentID == "LIN")
{
sSql = @"INSERT INTO [852_Detail] ( HeaderKey, LIN03_GTIN_UCC_Code, LIN05_BuyerItemNo, LIN07_VendorItemNo,
CTP02_PriceCode, CTP03_UnitPrice,
PO406_WeightPerPack, PO407_Unit )
values
(@HeaderKey, @LIN03_GTIN_UCC_Code, @LIN05_BuyerItemNo, @LIN07_VendorItemNo,
@CTP02_PriceCode, @CTP03_UnitPrice,
@PO406_WeightPerPack, @PO407_Unit );
SELECT scope_identity()";
oDaDetail.InsertCommand = new SqlCommand(sSql, oConnection);
oDaDetail.InsertCommand.Parameters.AddWithValue("@HeaderKey", nHeaderkey);
oDaDetail.InsertCommand.Parameters.AddWithValue("@LIN03_GTIN_UCC_Code", oSegment.get_DataElementValue(3, 0)); // Product/Service ID (234)
oDaDetail.InsertCommand.Parameters.AddWithValue("@LIN05_BuyerItemNo", oSegment.get_DataElementValue(5, 0)); // Product/Service ID (234)
oDaDetail.InsertCommand.Parameters.AddWithValue("@LIN07_VendorItemNo", oSegment.get_DataElementValue(7, 0)); // Product/Service ID (234)
oDaDetail.InsertCommand.Parameters.AddWithValue("@CTP02_PriceCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@CTP03_UnitPrice", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO406_WeightPerPack", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO407_Unit", "");
nDetailkey = (Int32)(decimal)oDaDetail.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "CTP")
{
sSql = @"UPDATE [852_Detail] SET CTP02_PriceCode = @CTP02_PriceCode, CTP03_UnitPrice = @CTP03_UnitPrice
where DetailKey = @DetailKey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@CTP02_PriceCode", oSegment.get_DataElementValue(2, 0)); // Price Identifier Code (236)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@CTP03_UnitPrice", oSegment.get_DataElementValue(3, 0)); // Unit Price (212)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "PO4") // Item Physical Details
{
sSql = @"UPDATE [852_Detail] SET PO406_WeightPerPack = @PO406_WeightPerPack, PO407_Unit = @PO407_Unit
where DetailKey = @DetailKey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO406_WeightPerPack", oSegment.get_DataElementValue(6, 0)); // Gross Weight per Pack (384)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO407_Unit", oSegment.get_DataElementValue(7, 0)); // Unit or Basis for Measurement Code (355)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
} // sSegmentID
}
else if (sLoopSection == "LIN;ZA")
{
if (sSegmentID == "ZA")
{
sSql = @"INSERT INTO [852_ProdActivity] ( DetailKey, ZA01_ActivityCode, SDQ01_Unit, SDQ02_IdCodeQlfr,
SDQ03_IdCode, SDQ04_Quantity, SDQ05_IdCode, SDQ06_Quantity, SDQ07_IdCode, SDQ08_Quantity,
SDQ09_IdCode, SDQ10_Quantity, SDQ11_IdCode, SDQ12_Quantity, SDQ13_IdCode, SDQ14_Quantity,
SDQ15_IdCode, SDQ16_Quantity, SDQ17_IdCode, SDQ18_Quantity, SDQ19_IdCode, SDQ20_Quantity,
SDQ21_IdCode, SDQ22_Quantity )
values
(@DetailKey, @ZA01_ActivityCode, @SDQ01_Unit, @SDQ02_IdCodeQlfr,
@SDQ03_IdCode, @SDQ04_Quantity, @SDQ05_IdCode, @SDQ06_Quantity, @SDQ07_IdCode, @SDQ08_Quantity,
@SDQ09_IdCode, @SDQ10_Quantity, @SDQ11_IdCode, @SDQ12_Quantity, @SDQ13_IdCode, @SDQ14_Quantity,
@SDQ15_IdCode, @SDQ16_Quantity, @SDQ17_IdCode, @SDQ18_Quantity, @SDQ19_IdCode, @SDQ20_Quantity,
@SDQ21_IdCode, @SDQ22_Quantity );
SELECT scope_identity()";
oProdActivity.InsertCommand = new SqlCommand(sSql, oConnection);
oProdActivity.InsertCommand.Parameters.AddWithValue("@DetailKey", nDetailkey);
oProdActivity.InsertCommand.Parameters.AddWithValue("@ZA01_ActivityCode", oSegment.get_DataElementValue(1, 0)); // Activity Code (859)
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ01_Unit", ""); // Unit or Basis for Measurement Code (355)
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ02_IdCodeQlfr", ""); // Identification Code Qualifier (66)
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ03_IdCode", ""); // Identification Code (67)
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ04_Quantity", ""); // Quantity (380)
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ05_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ06_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ07_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ08_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ09_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ10_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ11_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ12_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ13_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ14_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ15_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ16_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ17_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ18_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ19_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ20_Quantity", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ21_IdCode", "");
oProdActivity.InsertCommand.Parameters.AddWithValue("@SDQ22_Quantity", "");
nProdActivityKey = (Int32)(decimal)oProdActivity.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "SDQ")
{
sSql = @"UPDATE [852_ProdActivity] SET SDQ01_Unit = @SDQ01_Unit, SDQ02_IdCodeQlfr = @SDQ02_IdCodeQlfr,
SDQ03_IdCode = @SDQ03_IdCode, SDQ04_Quantity = @SDQ04_Quantity, SDQ05_IdCode = @SDQ05_IdCode, SDQ06_Quantity = @SDQ06_Quantity,
SDQ07_IdCode = @SDQ07_IdCode, SDQ08_Quantity = @SDQ08_Quantity, SDQ09_IdCode = @SDQ09_IdCode, SDQ10_Quantity = @SDQ10_Quantity,
SDQ11_IdCode = @SDQ11_IdCode, SDQ12_Quantity = @SDQ12_Quantity, SDQ13_IdCode = @SDQ13_IdCode, SDQ14_Quantity = @SDQ14_Quantity,
SDQ15_IdCode = @SDQ15_IdCode, SDQ16_Quantity = @SDQ16_Quantity, SDQ17_IdCode = @SDQ17_IdCode, SDQ18_Quantity = @SDQ18_Quantity,
SDQ19_IdCode = @SDQ19_IdCode, SDQ20_Quantity = @SDQ20_Quantity, SDQ21_IdCode = @SDQ21_IdCode, SDQ22_Quantity = @SDQ22_Quantity
where ProdActivityKey = @ProdActivityKey";
oProdActivity.UpdateCommand = new SqlCommand(sSql, oConnection);
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ01_Unit", oSegment.get_DataElementValue(1, 0)); // Unit or Basis for Measurement Code (355)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ02_IdCodeQlfr", oSegment.get_DataElementValue(2, 0)); // Identification Code Qualifier (66)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ03_IdCode", oSegment.get_DataElementValue(3, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ04_Quantity", oSegment.get_DataElementValue(4, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ05_IdCode", oSegment.get_DataElementValue(5, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ06_Quantity", oSegment.get_DataElementValue(6, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ07_IdCode", oSegment.get_DataElementValue(7, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ08_Quantity", oSegment.get_DataElementValue(8, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ09_IdCode", oSegment.get_DataElementValue(9, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ10_Quantity", oSegment.get_DataElementValue(10, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ11_IdCode", oSegment.get_DataElementValue(11, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ12_Quantity", oSegment.get_DataElementValue(12, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ13_IdCode", oSegment.get_DataElementValue(13, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ14_Quantity", oSegment.get_DataElementValue(14, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ15_IdCode", oSegment.get_DataElementValue(15, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ16_Quantity", oSegment.get_DataElementValue(16, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ17_IdCode", oSegment.get_DataElementValue(17, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ18_Quantity", oSegment.get_DataElementValue(18, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ19_IdCode", oSegment.get_DataElementValue(19, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ20_Quantity", oSegment.get_DataElementValue(20, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ21_IdCode", oSegment.get_DataElementValue(21, 0)); // Identification Code (67)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@SDQ22_Quantity", oSegment.get_DataElementValue(22, 0)); // Quantity (380)
oProdActivity.UpdateCommand.Parameters.AddWithValue("@ProdActivityKey", nProdActivityKey);
oProdActivity.UpdateCommand.ExecuteNonQuery();
}
} // sLoopSection
}
else if (nArea == 3)
{
if (sSegmentID == "CTT")
{
sSql = @"UPDATE [852_Header] SET CTT01_NumberOfLineItems = @CTT01_NumberOfLineItems
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTT01_NumberOfLineItems", oSegment.get_DataElementValue(1, 0)); // Number of Line Items (354)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
ediDataSegment.Set(ref oSegment, oSegment.Next());
} // oSegment != null
} // Translate
public void Translate(string sEdiPathFile)
{
string sSegmentID;
string sLoopSection;
int nArea;
string sQlfr = "";
string sSql = "";
string sN1Qlfr = "";
nInterkey = 0;
nGroupkey = 0;
nHeaderkey = 0;
nDetailkey = 0;
// Specify EDI document to read.
oEdiDoc.LoadEdi(sEdiPathFile);
// Gets the first data segment in the EDI document.
ediDataSegment.Set(ref oSegment, oEdiDoc.FirstDataSegment);
// Loop that will traverse through the EDI document from top to bottom. This
// is required for FORWARD-ONLY cursor.
while (oSegment != null)
{
sSegmentID = oSegment.ID;
sLoopSection = oSegment.LoopSection;
nArea = oSegment.Area;
if (nArea == 0)
{
if (sLoopSection == "")
{
if (sSegmentID == "ISA")
{
//insert a record into interchange table
sSql = @"INSERT INTO [InterchangeInbound] ( ISA01_AuthorizationInfoQlfr, ISA02_AuthorizationInfo, ISA03_SecurityInfoQlfr,
ISA04_SecurityInfo, ISA05_SenderIdQlfr, ISA06_SenderId, ISA07_ReceiverIdQlfr, ISA08_ReceiverId, ISA09_Date, ISA10_Time,
ISA11_RepetitionSeparator, ISA12_ControlVersionNumber, ISA13_ControlNumber, ISA14_AcknowledgmentRequested, ISA15_UsageIndicator,
ISA16_ComponentElementSeparator)
values
(@ISA01_AuthorizationInfoQlfr, @ISA02_AuthorizationInfo, @ISA03_SecurityInfoQlfr,
@ISA04_SecurityInfo, @ISA05_SenderIdQlfr, @ISA06_SenderId, @ISA07_ReceiverIdQlfr, @ISA08_ReceiverId, @ISA09_Date, @ISA10_Time,
@ISA11_RepetitionSeparator, @ISA12_ControlVersionNumber, @ISA13_ControlNumber, @ISA14_AcknowledgmentRequested, @ISA15_UsageIndicator,
@ISA16_ComponentElementSeparator);
SELECT scope_identity()";
oDaInterchange.InsertCommand = new SqlCommand(sSql, oConnection);
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA01_AuthorizationInfoQlfr", oSegment.get_DataElementValue(1, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA02_AuthorizationInfo", oSegment.get_DataElementValue(2, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA03_SecurityInfoQlfr", oSegment.get_DataElementValue(3, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA04_SecurityInfo", oSegment.get_DataElementValue(4, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA05_SenderIdQlfr", oSegment.get_DataElementValue(5, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA06_SenderId", oSegment.get_DataElementValue(6, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA07_ReceiverIdQlfr", oSegment.get_DataElementValue(7, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA08_ReceiverId", oSegment.get_DataElementValue(8, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA09_Date", oSegment.get_DataElementValue(9, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA10_Time", oSegment.get_DataElementValue(10, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA11_RepetitionSeparator", oSegment.get_DataElementValue(11, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA12_ControlVersionNumber", oSegment.get_DataElementValue(12, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA13_ControlNumber", oSegment.get_DataElementValue(13, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA14_AcknowledgmentRequested", oSegment.get_DataElementValue(14, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA15_UsageIndicator", oSegment.get_DataElementValue(15, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA16_ComponentElementSeparator", oSegment.get_DataElementValue(16, 0));
nInterkey = (Int32)(decimal)oDaInterchange.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "GS")
{
//insert a record into FunctionGrop table
sSql = @"INSERT INTO [FunctionalGroupInbound] (InterchangeKey, GS01_FunctionalIdfrCode, GS02_SendersCode, GS03_ReceiversCode, GS04_Date, GS05_Time,
GS06_GroupControlNumber, GS07_ResponsibleAgencyCode, GS08_VersionReleaseCode)
values
(@InterchangeKey, @GS01_FunctionalIdfrCode, @GS02_SendersCode, @GS03_ReceiversCode, @GS04_Date, @GS05_Time,
@GS06_GroupControlNumber, @GS07_ResponsibleAgencyCode, @GS08_VersionReleaseCode);
SELECT scope_identity()";
oDaFuncGroup.InsertCommand = new SqlCommand(sSql, oConnection);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@InterchangeKey", nInterkey);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS01_FunctionalIdfrCode", oSegment.get_DataElementValue(1, 0)); //Functional Identifier Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS02_SendersCode", oSegment.get_DataElementValue(2, 0)); //Application Sender's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS03_ReceiversCode", oSegment.get_DataElementValue(3, 0)); //Application Receiver's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS04_Date", oSegment.get_DataElementValue(4, 0)); //Date
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS05_Time", oSegment.get_DataElementValue(5, 0)); //Time
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS06_GroupControlNumber", oSegment.get_DataElementValue(6, 0)); //Group Control Number
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS07_ResponsibleAgencyCode", oSegment.get_DataElementValue(7, 0)); //Responsible Agency Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS08_VersionReleaseCode", oSegment.get_DataElementValue(8, 0)); //Version / Release / Industry Identifier Code
nGroupkey = (Int32)(decimal)oDaFuncGroup.InsertCommand.ExecuteScalar();
}
}
} //nArea == 0
else if (nArea == 1)
{
if (sLoopSection == "")
{
if (sSegmentID == "ST")
{
sSql = @"INSERT INTO [850_Header] ( FunctionalGroupKey, ST01_TranSetIdfrCode, ST02_TranSetControlNo,
BEG01_TransactionSetPurposeCode, BEG02_PurchaseOrderTypeCode, BEG03_PurchaseOrderNumber, BEG05_PODate,
CUR02_CurrencyCode, REF02_FreeFormText, REF02_InternalVendorNo, REF02_ProductGroup,
PER02_ContactPersonName, DTM02_DeliveryRequestedDate, DTM02_RequestedPickupDate,
N104_ShipFromID, N401_ShipFromCity, N402_ShipFromState, N403_ShipFromPostalCode, N404_ShipFromCountryCode,
N104_ShipToID, N401_ShipToCity, N402_ShipToState, N403_ShipToPostalCode, N404_ShipToCountryCode,
CTT01_NumberOfPO1Segments)
values
(@FunctionalGroupKey, @ST01_TranSetIdfrCode, @ST02_TranSetControlNo,
@BEG01_TransactionSetPurposeCode, @BEG02_PurchaseOrderTypeCode, @BEG03_PurchaseOrderNumber, @BEG05_PODate,
@CUR02_CurrencyCode, @REF02_FreeFormText, @REF02_InternalVendorNo, @REF02_ProductGroup,
@PER02_ContactPersonName, @DTM02_DeliveryRequestedDate, @DTM02_RequestedPickupDate,
@N104_ShipFromID, @N401_ShipFromCity, @N402_ShipFromState, @N403_ShipFromPostalCode, @N404_ShipFromCountryCode,
@N104_ShipToID, @N401_ShipToCity, @N402_ShipToState, @N403_ShipToPostalCode, @N404_ShipToCountryCode,
@CTT01_NumberOfPO1Segments);
SELECT scope_identity()";
oDaHeader.InsertCommand = new SqlCommand(sSql, oConnection);
oDaHeader.InsertCommand.Parameters.AddWithValue("@FunctionalGroupKey", nGroupkey);
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST01_TranSetIdfrCode", oSegment.get_DataElementValue(1, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST02_TranSetControlNo", oSegment.get_DataElementValue(2, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@BEG01_TransactionSetPurposeCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BEG02_PurchaseOrderTypeCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BEG03_PurchaseOrderNumber", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BEG05_PODate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@CUR02_CurrencyCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@REF02_FreeFormText", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@REF02_InternalVendorNo", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@REF02_ProductGroup", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@PER02_ContactPersonName", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_DeliveryRequestedDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_RequestedPickupDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_ShipFromID", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_ShipFromCity", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_ShipFromState", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_ShipFromPostalCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N404_ShipFromCountryCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_ShipToID", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_ShipToCity", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_ShipToState", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_ShipToPostalCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N404_ShipToCountryCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTT01_NumberOfPO1Segments", "");
nHeaderkey = (Int32)(decimal)oDaHeader.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "BEG")
{
sSql = @"UPDATE [850_Header] SET BEG01_TransactionSetPurposeCode = @BEG01_TransactionSetPurposeCode, BEG02_PurchaseOrderTypeCode = @BEG02_PurchaseOrderTypeCode,
BEG03_PurchaseOrderNumber = @BEG03_PurchaseOrderNumber, BEG05_PODate = @BEG05_PODate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BEG01_TransactionSetPurposeCode", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BEG02_PurchaseOrderTypeCode", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BEG03_PurchaseOrderNumber", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BEG05_PODate", oSegment.get_DataElementValue(5, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "CUR")
{
sSql = @"UPDATE [850_Header] SET CUR02_CurrencyCode = @CUR02_CurrencyCode
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CUR02_CurrencyCode", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "REF")
{
sQlfr = oSegment.get_DataElementValue(1, 0);
if (sQlfr == "FI")
{
sSql = @"UPDATE [850_Header] SET REF02_FreeFormText = @REF02_FreeFormText
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_FreeFormText", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sQlfr == "IA")
{
sSql = @"UPDATE [850_Header] SET REF02_InternalVendorNo = @REF02_InternalVendorNo
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_InternalVendorNo", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sQlfr == "PG")
{
sSql = @"UPDATE [850_Header] SET REF02_ProductGroup = @REF02_ProductGroup
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@REF02_ProductGroup", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
else if (sSegmentID == "PER")
{
sSql = @"UPDATE [850_Header] SET PER02_ContactPersonName = @PER02_ContactPersonName
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@PER02_ContactPersonName", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "DTM")
{
sQlfr = oSegment.get_DataElementValue(1, 0);
if (sQlfr == "002")
{
sSql = @"UPDATE [850_Header] SET DTM02_DeliveryRequestedDate = @DTM02_DeliveryRequestedDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_DeliveryRequestedDate", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sQlfr == "118")
{
sSql = @"UPDATE [850_Header] SET DTM02_RequestedPickupDate = @DTM02_RequestedPickupDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_RequestedPickupDate", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
} //sLoopSection == ""
else if (sLoopSection == "N1")
{
//Get Qualifier
if (sSegmentID == "N1")
{
sN1Qlfr = oSegment.get_DataElementValue(1, 0);
}
if (sN1Qlfr == "SF") //Ship From information
{
if (sSegmentID == "N1")
{
sSql = @"UPDATE [850_Header] SET N104_ShipFromID = @N104_ShipFromID
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_ShipFromID", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N4")
{
sSql = @"UPDATE [850_Header] SET N401_ShipFromCity = @N401_ShipFromCity, N402_ShipFromState = @N402_ShipFromState,
N403_ShipFromPostalCode = @N403_ShipFromPostalCode, N404_ShipFromCountryCode = @N404_ShipFromCountryCode
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_ShipFromCity", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_ShipFromState", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_ShipFromPostalCode", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N404_ShipFromCountryCode", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
else if (sN1Qlfr == "ST") //Ship To information
{
if (sSegmentID == "N1")
{
sSql = @"UPDATE [850_Header] SET N104_ShipToID = @N104_ShipToID
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_ShipToID", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N4")
{
sSql = @"UPDATE [850_Header] SET N401_ShipToCity = @N401_ShipToCity, N402_ShipToState = @N402_ShipToState,
N403_ShipToPostalCode = @N403_ShipToPostalCode, N404_ShipToCountryCode = @N404_ShipToCountryCode
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_ShipToCity", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_ShipToState", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_ShipToPostalCode", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N404_ShipToCountryCode", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
} // sN1Qlfr
} // sLoopSection
}
else if (nArea == 2)
{
if (sLoopSection == "PO1")
{
if (sSegmentID == "PO1")
{
sSql = @"INSERT INTO [850_Detail] ( HeaderKey, PO102_Quantity, PO103_UnitMeasurementCode,
PO104_UnitPrice, PO107_ArticleNo, PO108_GTIN_Qlfr, PO109_GTIN_DigitDatatructure, PO111_VendorItemNo, PO113_Case,
SDQ01_UnitMeasurementCode, SDQ03_IdCode, SDQ04_Quantity, SDQ05_IdCode, SDQ06_Quantity, SDQ07_IdCode, SDQ08_Quantity, SDQ09_IdCode, SDQ10_Quantity,
SDQ11_IdCode, SDQ12_Quantity, SDQ13_IdCode, SDQ14_Quantity, SDQ15_IdCode, SDQ16_Quantity, SDQ17_IdCode, SDQ18_Quantity,
SDQ19_IdCode, SDQ20_Quantity, SDQ21_IdCode, SDQ22_Quantity)
values
(@HeaderKey, @PO102_Quantity, @PO103_UnitMeasurementCode,
@PO104_UnitPrice, @PO107_ArticleNo, @PO108_GTIN_Qlfr, @PO109_GTIN_DigitDatatructure, @PO111_VendorItemNo, @PO113_Case,
@SDQ01_UnitMeasurementCode, @SDQ03_IdCode, @SDQ04_Quantity, @SDQ05_IdCode, @SDQ06_Quantity, @SDQ07_IdCode, @SDQ08_Quantity, @SDQ09_IdCode, @SDQ10_Quantity,
@SDQ11_IdCode, @SDQ12_Quantity, @SDQ13_IdCode, @SDQ14_Quantity, @SDQ15_IdCode, @SDQ16_Quantity, @SDQ17_IdCode, @SDQ18_Quantity,
@SDQ19_IdCode, @SDQ20_Quantity, @SDQ21_IdCode, @SDQ22_Quantity);
SELECT scope_identity()";
oDaDetail.InsertCommand = new SqlCommand(sSql, oConnection);
oDaDetail.InsertCommand.Parameters.AddWithValue("@HeaderKey", nHeaderkey);
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO102_Quantity", oSegment.get_DataElementValue(2, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO103_UnitMeasurementCode", oSegment.get_DataElementValue(3, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO104_UnitPrice", oSegment.get_DataElementValue(4, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO107_ArticleNo", oSegment.get_DataElementValue(7, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO108_GTIN_Qlfr", oSegment.get_DataElementValue(8, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO109_GTIN_DigitDatatructure", oSegment.get_DataElementValue(9, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO111_VendorItemNo", oSegment.get_DataElementValue(11, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO113_Case", oSegment.get_DataElementValue(13, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ01_UnitMeasurementCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ03_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ04_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ05_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ06_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ07_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ08_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ09_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ10_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ11_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ12_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ13_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ14_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ15_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ16_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ17_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ18_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ19_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ20_Quantity", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ21_IdCode", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@SDQ22_Quantity", "");
nDetailkey = (Int32)(decimal)oDaDetail.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "SDQ")
{
sSql = @"UPDATE [850_Detail] SET SDQ01_UnitMeasurementCode = @SDQ01_UnitMeasurementCode, SDQ03_IdCode = @SDQ03_IdCode,
SDQ04_Quantity = @SDQ04_Quantity, SDQ05_IdCode = @SDQ05_IdCode, SDQ06_Quantity = @SDQ06_Quantity,
SDQ07_IdCode = @SDQ07_IdCode, SDQ08_Quantity = @SDQ08_Quantity, SDQ09_IdCode = @SDQ09_IdCode, SDQ10_Quantity = @SDQ10_Quantity,
SDQ11_IdCode = @SDQ11_IdCode, SDQ12_Quantity = @SDQ12_Quantity, SDQ13_IdCode = @SDQ13_IdCode, SDQ14_Quantity = @SDQ14_Quantity,
SDQ15_IdCode = @SDQ15_IdCode, SDQ16_Quantity = @SDQ16_Quantity, SDQ17_IdCode = @SDQ17_IdCode, SDQ18_Quantity = @SDQ18_Quantity,
SDQ19_IdCode = @SDQ19_IdCode, SDQ20_Quantity = @SDQ20_Quantity, SDQ21_IdCode = @SDQ21_IdCode, SDQ22_Quantity = @SDQ22_Quantity
where DetailKey = @DetailKey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ01_UnitMeasurementCode", oSegment.get_DataElementValue(1, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ03_IdCode", oSegment.get_DataElementValue(3, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ04_Quantity", oSegment.get_DataElementValue(4, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ05_IdCode", oSegment.get_DataElementValue(5, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ06_Quantity", oSegment.get_DataElementValue(6, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ07_IdCode", oSegment.get_DataElementValue(7, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ08_Quantity", oSegment.get_DataElementValue(8, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ09_IdCode", oSegment.get_DataElementValue(9, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ10_Quantity", oSegment.get_DataElementValue(10, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ11_IdCode", oSegment.get_DataElementValue(11, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ12_Quantity", oSegment.get_DataElementValue(12, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ13_IdCode", oSegment.get_DataElementValue(13, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ14_Quantity", oSegment.get_DataElementValue(14, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ15_IdCode", oSegment.get_DataElementValue(15, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ16_Quantity", oSegment.get_DataElementValue(16, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ17_IdCode", oSegment.get_DataElementValue(17, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ18_Quantity", oSegment.get_DataElementValue(18, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ19_IdCode", oSegment.get_DataElementValue(19, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ20_Quantity", oSegment.get_DataElementValue(20, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ21_IdCode", oSegment.get_DataElementValue(21, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@SDQ22_Quantity", oSegment.get_DataElementValue(22, 0));
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
} // sSegmentID
} // sLoopSection
}
else if (nArea == 3)
{
if (sSegmentID == "CTT")
{
sSql = @"UPDATE [850_Header] SET CTT01_NumberOfPO1Segments = @CTT01_NumberOfPO1Segments
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTT01_NumberOfPO1Segments", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
ediDataSegment.Set(ref oSegment, oSegment.Next());
} // oSegment != null
} // Translate
public void Translate(string sEdiPathFile)
{
string sSegmentID;
string sLoopSection;
int nArea;
string sQlfr = "";
string sSql = "";
string sN1Qlfr = "";
nInterkey = 0;
nGroupkey = 0;
nHeaderkey = 0;
nDetailkey = 0;
// Specify EDI document to read.
oEdiDoc.LoadEdi(sEdiPathFile);
// Gets the first data segment in the EDI document.
ediDataSegment.Set(ref oSegment, oEdiDoc.FirstDataSegment);
// Loop that will traverse through the EDI document from top to bottom. This
// is required for FORWARD-ONLY cursor.
while (oSegment != null)
{
sSegmentID = oSegment.ID;
sLoopSection = oSegment.LoopSection;
nArea = oSegment.Area;
if (nArea == 0)
{
if (sLoopSection == "")
{
if (sSegmentID == "ISA")
{
//insert a record into interchange table
sSql = @"INSERT INTO [InterchangeInbound] ( ISA01_AuthorizationInfoQlfr, ISA02_AuthorizationInfo, ISA03_SecurityInfoQlfr,
ISA04_SecurityInfo, ISA05_SenderIdQlfr, ISA06_SenderId, ISA07_ReceiverIdQlfr, ISA08_ReceiverId, ISA09_Date, ISA10_Time,
ISA11_RepetitionSeparator, ISA12_ControlVersionNumber, ISA13_ControlNumber, ISA14_AcknowledgmentRequested, ISA15_UsageIndicator,
ISA16_ComponentElementSeparator)
values
(@ISA01_AuthorizationInfoQlfr, @ISA02_AuthorizationInfo, @ISA03_SecurityInfoQlfr,
@ISA04_SecurityInfo, @ISA05_SenderIdQlfr, @ISA06_SenderId, @ISA07_ReceiverIdQlfr, @ISA08_ReceiverId, @ISA09_Date, @ISA10_Time,
@ISA11_RepetitionSeparator, @ISA12_ControlVersionNumber, @ISA13_ControlNumber, @ISA14_AcknowledgmentRequested, @ISA15_UsageIndicator,
@ISA16_ComponentElementSeparator);
SELECT scope_identity()";
oDaInterchange.InsertCommand = new SqlCommand(sSql, oConnection);
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA01_AuthorizationInfoQlfr", oSegment.get_DataElementValue(1, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA02_AuthorizationInfo", oSegment.get_DataElementValue(2, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA03_SecurityInfoQlfr", oSegment.get_DataElementValue(3, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA04_SecurityInfo", oSegment.get_DataElementValue(4, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA05_SenderIdQlfr", oSegment.get_DataElementValue(5, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA06_SenderId", oSegment.get_DataElementValue(6, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA07_ReceiverIdQlfr", oSegment.get_DataElementValue(7, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA08_ReceiverId", oSegment.get_DataElementValue(8, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA09_Date", oSegment.get_DataElementValue(9, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA10_Time", oSegment.get_DataElementValue(10, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA11_RepetitionSeparator", oSegment.get_DataElementValue(11, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA12_ControlVersionNumber", oSegment.get_DataElementValue(12, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA13_ControlNumber", oSegment.get_DataElementValue(13, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA14_AcknowledgmentRequested", oSegment.get_DataElementValue(14, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA15_UsageIndicator", oSegment.get_DataElementValue(15, 0));
oDaInterchange.InsertCommand.Parameters.AddWithValue("@ISA16_ComponentElementSeparator", oSegment.get_DataElementValue(16, 0));
nInterkey = (Int32)(decimal)oDaInterchange.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "GS")
{
//insert a record into FunctionGrop table
sSql = @"INSERT INTO [FunctionalGroupInbound] (InterchangeKey, GS01_FunctionalIdfrCode, GS02_SendersCode, GS03_ReceiversCode, GS04_Date, GS05_Time,
GS06_GroupControlNumber, GS07_ResponsibleAgencyCode, GS08_VersionReleaseCode)
values
(@InterchangeKey, @GS01_FunctionalIdfrCode, @GS02_SendersCode, @GS03_ReceiversCode, @GS04_Date, @GS05_Time,
@GS06_GroupControlNumber, @GS07_ResponsibleAgencyCode, @GS08_VersionReleaseCode);
SELECT scope_identity()";
oDaFuncGroup.InsertCommand = new SqlCommand(sSql, oConnection);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@InterchangeKey", nInterkey);
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS01_FunctionalIdfrCode", oSegment.get_DataElementValue(1, 0)); //Functional Identifier Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS02_SendersCode", oSegment.get_DataElementValue(2, 0)); //Application Sender's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS03_ReceiversCode", oSegment.get_DataElementValue(3, 0)); //Application Receiver's Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS04_Date", oSegment.get_DataElementValue(4, 0)); //Date
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS05_Time", oSegment.get_DataElementValue(5, 0)); //Time
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS06_GroupControlNumber", oSegment.get_DataElementValue(6, 0)); //Group Control Number
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS07_ResponsibleAgencyCode", oSegment.get_DataElementValue(7, 0)); //Responsible Agency Code
oDaFuncGroup.InsertCommand.Parameters.AddWithValue("@GS08_VersionReleaseCode", oSegment.get_DataElementValue(8, 0)); //Version / Release / Industry Identifier Code
nGroupkey = (Int32)(decimal)oDaFuncGroup.InsertCommand.ExecuteScalar();
}
}
} //nArea == 0
else if (nArea == 1)
{
if (sLoopSection == "")
{
if (sSegmentID == "ST")
{
sSql = @"INSERT INTO [860_Header] ( FunctionalGroupKey, ST01_TranSetIdfrCode, ST02_TranSetControlNo,
BCH01_TransactionSetPurposeCode, BCH02_PurchaseOrderTypeCode, BCH03_OriginalPO_No, BCH06_OriginalPO_Date, BCH11_OriginalPO_DateChange,
DTM02_DeliveryRequestedDate, DTM02_ShipNotBeforeDate, DTM02_ShipNoLaterDate, DTM02_RequestedPickupDate,
N104_ShipFromID,
N401_ShipFromCity, N402_ShipFromState, N403_ShipFromZip, N404_ShipFromCountryCode,
CTT01_NumberOfPOCSegments )
values
(@FunctionalGroupKey, @ST01_TranSetIdfrCode, @ST02_TranSetControlNo,
@BCH01_TransactionSetPurposeCode, @BCH02_PurchaseOrderTypeCode, @BCH03_OriginalPO_No, @BCH06_OriginalPO_Date, @BCH11_OriginalPO_DateChange,
@DTM02_DeliveryRequestedDate, @DTM02_ShipNotBeforeDate, @DTM02_ShipNoLaterDate, @DTM02_RequestedPickupDate,
@N104_ShipFromID,
@N401_ShipFromCity, @N402_ShipFromState, @N403_ShipFromZip, @N404_ShipFromCountryCode,
@CTT01_NumberOfPOCSegments );
SELECT scope_identity()";
oDaHeader.InsertCommand = new SqlCommand(sSql, oConnection);
oDaHeader.InsertCommand.Parameters.AddWithValue("@FunctionalGroupKey", nGroupkey);
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST01_TranSetIdfrCode", oSegment.get_DataElementValue(1, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@ST02_TranSetControlNo", oSegment.get_DataElementValue(2, 0));
oDaHeader.InsertCommand.Parameters.AddWithValue("@BCH01_TransactionSetPurposeCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BCH02_PurchaseOrderTypeCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BCH03_OriginalPO_No", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BCH06_OriginalPO_Date", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@BCH11_OriginalPO_DateChange", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_DeliveryRequestedDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_ShipNotBeforeDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_ShipNoLaterDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@DTM02_RequestedPickupDate", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N104_ShipFromID", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N401_ShipFromCity", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N402_ShipFromState", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N403_ShipFromZip", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@N404_ShipFromCountryCode", "");
oDaHeader.InsertCommand.Parameters.AddWithValue("@CTT01_NumberOfPOCSegments", "");
nHeaderkey = (Int32)(decimal)oDaHeader.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "BCH")
{
sSql = @"UPDATE [860_Header] SET BCH01_TransactionSetPurposeCode = @BCH01_TransactionSetPurposeCode, BCH02_PurchaseOrderTypeCode = @BCH02_PurchaseOrderTypeCode,
BCH03_OriginalPO_No = @BCH03_OriginalPO_No, BCH06_OriginalPO_Date = @BCH06_OriginalPO_Date, BCH11_OriginalPO_DateChange = @BCH11_OriginalPO_DateChange
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BCH01_TransactionSetPurposeCode", oSegment.get_DataElementValue(1, 0)); // Transaction Set Purpose Code (353)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BCH02_PurchaseOrderTypeCode", oSegment.get_DataElementValue(2, 0)); // Purchase Order Type Code (92)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BCH03_OriginalPO_No", oSegment.get_DataElementValue(3, 0)); // Purchase Order Number (324)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BCH06_OriginalPO_Date", oSegment.get_DataElementValue(6, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@BCH11_OriginalPO_DateChange", oSegment.get_DataElementValue(11, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "DTM")
{
sQlfr = oSegment.get_DataElementValue(1, 0); // Date/Time Qualifier (374)
if (sQlfr == "002") // Delivery Requested
{
sSql = @"UPDATE [860_Header] SET DTM02_DeliveryRequestedDate = @DTM02_DeliveryRequestedDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_DeliveryRequestedDate", oSegment.get_DataElementValue(2, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sQlfr == "037") // Ship Not Before
{
sSql = @"UPDATE [860_Header] SET DTM02_ShipNotBeforeDate = @DTM02_ShipNotBeforeDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_ShipNotBeforeDate", oSegment.get_DataElementValue(2, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sQlfr == "038") // Ship No Later
{
sSql = @"UPDATE [860_Header] SET DTM02_ShipNoLaterDate = @DTM02_ShipNoLaterDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_ShipNoLaterDate", oSegment.get_DataElementValue(2, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sQlfr == "118") // Requested Pickup
{
sSql = @"UPDATE [860_Header] SET DTM02_RequestedPickupDate = @DTM02_RequestedPickupDate
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@DTM02_RequestedPickupDate", oSegment.get_DataElementValue(2, 0)); // Date (373)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
}
else if (sLoopSection == "N1")
{
//Get Qualifier
if (sSegmentID == "N1")
{
sN1Qlfr = oSegment.get_DataElementValue(1, 0);
}
if (sN1Qlfr == "SF") //Ship From information
{
if (sSegmentID == "N1")
{
sSql = @"UPDATE [860_Header] SET N104_ShipFromID = @N104_ShipFromID
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N104_ShipFromID", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
else if (sSegmentID == "N4")
{
sSql = @"UPDATE [860_Header] SET N401_ShipFromCity = @N401_ShipFromCity, N402_ShipFromState = @N402_ShipFromState,
N403_ShipFromZip = @N403_ShipFromZip, N404_ShipFromCountryCode = @N404_ShipFromCountryCode
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N401_ShipFromCity", oSegment.get_DataElementValue(1, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N402_ShipFromState", oSegment.get_DataElementValue(2, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N403_ShipFromZip", oSegment.get_DataElementValue(3, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@N404_ShipFromCountryCode", oSegment.get_DataElementValue(4, 0));
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
} // sN1Qlfr
} // sLoopSection
}
else if (nArea == 2)
{
if (sLoopSection == "POC")
{
if (sSegmentID == "POC")
{
sSql = @"INSERT INTO [860_Detail] ( HeaderKey, POC02_ChangeTypeCode, POC06_UnitPrice, POC09_GTIN_EAN_UCC,
POC09_GTIN_UCC, POC11_BuyerItemNo, POC13_VendorItemNo, POC15_CommodityGroupingID, POC17_CaseID,
PO406_WeightPerPack, PO407_WeightUnit, PO408_VolumePerPack, PO409_VolumeUnit,
QTY02_QuantityAdjusted, QTY03_01_Unit )
values
(@HeaderKey, @POC02_ChangeTypeCode, @POC06_UnitPrice, @POC09_GTIN_EAN_UCC,
@POC09_GTIN_UCC, @POC11_BuyerItemNo, @POC13_VendorItemNo, @POC15_CommodityGroupingID, @POC17_CaseID,
@PO406_WeightPerPack, @PO407_WeightUnit, @PO408_VolumePerPack, @PO409_VolumeUnit,
@QTY02_QuantityAdjusted, @QTY03_01_Unit );
SELECT scope_identity()";
oDaDetail.InsertCommand = new SqlCommand(sSql, oConnection);
oDaDetail.InsertCommand.Parameters.AddWithValue("@HeaderKey", nHeaderkey);
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC02_ChangeTypeCode", oSegment.get_DataElementValue(2, 0)); // Change or Response Type Code (670)
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC06_UnitPrice", oSegment.get_DataElementValue(6, 0)); // Unit Price (212)
if (oSegment.get_DataElementValue(8, 0) == "EN") // Product/Service ID Qualifier (235)
{
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC09_GTIN_EAN_UCC", oSegment.get_DataElementValue(9, 0)); // Product/Service ID (234)
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC09_GTIN_UCC", ""); // Product/Service ID (234)
}
else
{
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC09_GTIN_UCC", oSegment.get_DataElementValue(9, 0)); // Product/Service ID (234)
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC09_GTIN_EAN_UCC", "");
}
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC11_BuyerItemNo", oSegment.get_DataElementValue(11, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC13_VendorItemNo", oSegment.get_DataElementValue(13, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC15_CommodityGroupingID", oSegment.get_DataElementValue(15, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@POC17_CaseID", oSegment.get_DataElementValue(17, 0));
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO406_WeightPerPack", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO407_WeightUnit", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO408_VolumePerPack", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@PO409_VolumeUnit", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@QTY02_QuantityAdjusted", "");
oDaDetail.InsertCommand.Parameters.AddWithValue("@QTY03_01_Unit", "");
nDetailkey = (Int32)(decimal)oDaDetail.InsertCommand.ExecuteScalar();
}
else if (sSegmentID == "PO4")
{
sSql = @"UPDATE [860_Detail] SET PO406_WeightPerPack = @PO406_WeightPerPack, PO407_WeightUnit = @PO407_WeightUnit,
PO408_VolumePerPack = @PO408_VolumePerPack, PO409_VolumeUnit = @PO409_VolumeUnit
where DetailKey = @DetailKey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO406_WeightPerPack", oSegment.get_DataElementValue(6, 0)); // Gross Weight per Pack (384)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO407_WeightUnit", oSegment.get_DataElementValue(7, 0)); // Unit or Basis for Measurement Code (355)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO408_VolumePerPack", oSegment.get_DataElementValue(8, 0)); // Gross Volume per Pack (385)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@PO409_VolumeUnit", oSegment.get_DataElementValue(9, 0)); // Unit or Basis for Measurement Code (355)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
} // sSegmentID
}
else if (sLoopSection == "POC;QTY")
{
if (sSegmentID == "QTY")
{
sSql = @"UPDATE [860_Detail] SET QTY02_QuantityAdjusted = @QTY02_QuantityAdjusted, QTY03_01_Unit = @QTY03_01_Unit
where DetailKey = @DetailKey";
oDaDetail.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaDetail.UpdateCommand.Parameters.AddWithValue("@QTY02_QuantityAdjusted", oSegment.get_DataElementValue(2, 0)); // Quantity (380)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@QTY03_01_Unit", oSegment.get_DataElementValue(3, 1)); // Unit or Basis for Measurement Code (355)
oDaDetail.UpdateCommand.Parameters.AddWithValue("@DetailKey", nDetailkey);
oDaDetail.UpdateCommand.ExecuteNonQuery();
}
}
}
else if (nArea == 3)
{
if (sSegmentID == "CTT")
{
sSql = @"UPDATE [860_Header] SET CTT01_NumberOfPOCSegments = @CTT01_NumberOfPOCSegments
where Headerkey = @Headerkey";
oDaHeader.UpdateCommand = new SqlCommand(sSql, oConnection);
oDaHeader.UpdateCommand.Parameters.AddWithValue("@CTT01_NumberOfPOCSegments", oSegment.get_DataElementValue(1, 0)); // Number of Line Items (354)
oDaHeader.UpdateCommand.Parameters.AddWithValue("@Headerkey", nHeaderkey);
oDaHeader.UpdateCommand.ExecuteNonQuery();
}
}
ediDataSegment.Set(ref oSegment, oSegment.Next());
} // oSegment != null
} // Translate