Search code examples
c#sdkinvoicesage50

Sage 50 UK V26.2.136.0 - How to update ledger of an invoice with sdk code so that invoice could appear on the customer's list activity

I am using the following sage50 uk sdk code (v26.2.136.0) to create invoice.

//Declare Variables
SageDataObject250.SDOEngine oSDO = new SageDataObject250.SDOEngine();
SageDataObject250.WorkSpace oWS;
SageDataObject250.InvoicePost oInvoicePost;
SageDataObject250.InvoiceItem oInvoiceItem;
SageDataObject250.SalesRecord oSalesRecord;
SageDataObject250.StockRecord oStockRecord;
String szDataPath;

//Instantiate WorkSpace
oWS = (SageDataObject250.WorkSpace)oSDO.Workspaces.Add("Example");

//Show select company dialog
szDataPath = oSDO.SelectCompany("C:\\ProgramData\\Sage\\Accounts\\2020");

//Try a connection, will throw an exception if it fails
try
{
    //Leaving the username and password blank generates a login dialog
    oWS.Connect(szDataPath, "", "", "Example");

    //Instantiate objects
    oSalesRecord = (SageDataObject250.SalesRecord)oWS.CreateObject("SalesRecord");
    oInvoicePost = (SageDataObject250.InvoicePost)oWS.CreateObject("InvoicePost");
    oStockRecord = (SageDataObject250.StockRecord)oWS.CreateObject("StockRecord");

    //Set the invoice type
    oInvoicePost.Type = (SageDataObject250.InvoiceType)SageDataObject250.LedgerType.sdoLedgerInvoice;


    //Read the first customer record and use to populate the invoice fields
    oSalesRecord.MoveFirst();
    SDOHelper.Write(oInvoicePost.Header, "ACCOUNT_REF", (String)SDOHelper.Read(oSalesRecord, "ACCOUNT_REF"));
    SDOHelper.Write(oInvoicePost.Header, "NAME", (String)SDOHelper.Read(oSalesRecord, "NAME"));
    SDOHelper.Write(oInvoicePost.Header, "ADDRESS_1", (String)SDOHelper.Read(oSalesRecord, "ADDRESS_1"));
    SDOHelper.Write(oInvoicePost.Header, "ADDRESS_2", (String)SDOHelper.Read(oSalesRecord, "ADDRESS_2"));
    SDOHelper.Write(oInvoicePost.Header, "ADDRESS_3", (String)SDOHelper.Read(oSalesRecord, "ADDRESS_3"));
    SDOHelper.Write(oInvoicePost.Header, "ADDRESS_4", (String)SDOHelper.Read(oSalesRecord, "ADDRESS_4"));
    SDOHelper.Write(oInvoicePost.Header, "ADDRESS_5", (String)SDOHelper.Read(oSalesRecord, "ADDRESS_5"));
    SDOHelper.Write(oInvoicePost.Header, "DEL_ADDRESS_1", (String)SDOHelper.Read(oSalesRecord, "DEL_ADDRESS_1"));
    SDOHelper.Write(oInvoicePost.Header, "DEL_ADDRESS_2", (String)SDOHelper.Read(oSalesRecord, "DEL_ADDRESS_2"));
    SDOHelper.Write(oInvoicePost.Header, "DEL_ADDRESS_3", (String)SDOHelper.Read(oSalesRecord, "DEL_ADDRESS_3"));
    SDOHelper.Write(oInvoicePost.Header, "DEL_ADDRESS_4", (String)SDOHelper.Read(oSalesRecord, "DEL_ADDRESS_4"));
    SDOHelper.Write(oInvoicePost.Header, "DEL_ADDRESS_5", (String)SDOHelper.Read(oSalesRecord, "DEL_ADDRESS_5"));
    SDOHelper.Write(oInvoicePost.Header, "CUST_TEL_NUMBER", (String)SDOHelper.Read(oSalesRecord, "TELEPHONE"));
    SDOHelper.Write(oInvoicePost.Header, "CONTACT_NAME", (String)SDOHelper.Read(oSalesRecord, "CONTACT_NAME"));
    SDOHelper.Write(oInvoicePost.Header, "GLOBAL_TAX_CODE", (Int16)SDOHelper.Read(oSalesRecord, "DEF_TAX_CODE"));

    //Populate other invoice header information
    SDOHelper.Write(oInvoicePost.Header, "INVOICE_DATE", (DateTime)DateTime.Today);
    SDOHelper.Write(oInvoicePost.Header, "NOTES_1", (String)"Notes 1");
    SDOHelper.Write(oInvoicePost.Header, "NOTES_2", (String)"Notes 2");
    SDOHelper.Write(oInvoicePost.Header, "NOTES_3", (String)"Notes 3");
    SDOHelper.Write(oInvoicePost.Header, "TAKEN_BY", (String)"Chris Reed");
    SDOHelper.Write(oInvoicePost.Header, "ORDER_NUMBER", (String)"");
    SDOHelper.Write(oInvoicePost.Header, "CUST_ORDER_NUMBER", (String)"");
    SDOHelper.Write(oInvoicePost.Header, "PAYMENT_REF", (String)"");
    // If anything is entered in the GLOBAL_NOM_CODE, all of the updated invoice’s splits will have this nominal code and
    // also this willforce anything entered in the GLOBAL_DETAILS field into the all the splits details field. 
    SDOHelper.Write(oInvoicePost.Header, "GLOBAL_NOM_CODE", (String)"");
    SDOHelper.Write(oInvoicePost.Header, "GLOBAL_DETAILS", (String)"");
    SDOHelper.Write(oInvoicePost.Header, "INVOICE_TYPE_CODE",
  (Byte)SageDataObject250.InvoiceType.sdoProductInvoice);

   // Set the Net Value Discount values. If setting NVD the program logic will now recalculate
   // the invoice Item values regardless of whether the TAX_FLAG is set on the item.
   SDOHelper.Write(oInvoicePost.Header,"NETVALUE_DISCOUNT", (Double)10);
   SDOHelper.Write(oInvoicePost.Header,"NETVALUE_DESCRIPTION", (String)"Discount offered");

   //Create and invoice item
   //Need to invoke the Add method
   oInvoiceItem = (SageDataObject250.InvoiceItem)SDOHelper.Add(oInvoicePost.Items);

   //Read the First Stock Code & populate fields from Stock Code
   oStockRecord.MoveFirst();
   SDOHelper.Write(oInvoiceItem, "STOCK_CODE", (String)SDOHelper.Read(oStockRecord, "STOCK_CODE"));
   SDOHelper.Write(oInvoiceItem, "DESCRIPTION", (String)SDOHelper.Read(oStockRecord, "DESCRIPTION"));
   SDOHelper.Write(oInvoiceItem, "NOMINAL_CODE", (String)SDOHelper.Read(oStockRecord, "NOMINAL_CODE"));
   SDOHelper.Write(oInvoiceItem, "TAX_CODE", (Int16)SDOHelper.Read(oStockRecord, "TAX_CODE"));

   //Populate other fields required for Invoice Item
   //From 2015 the update method now wraps internal business logic 
   //that calculates the vat amount if a net amount is given.
   //If you wish to calculate your own Tax values you will need
   //to ensure that you set the TAX_FLAG to 1 and set the TAX_AMOUNT value on the item line
   //***Note if a NVD is set the item line values will be recalculated 
   //regardless of the Tax_Flag being set to 1***
   SDOHelper.Write(oInvoiceItem, "QTY_ORDER", (Int32)1);
   SDOHelper.Write(oInvoiceItem, "UNIT_PRICE", (Int32)200);
   SDOHelper.Write(oInvoiceItem, "NET_AMOUNT", (Int32)200);
   SDOHelper.Write(oInvoiceItem, "FULL_NET_AMOUNT", (Int32)200);
   SDOHelper.Write(oInvoiceItem, "COMMENT_1", (String)"Comment 1");
   SDOHelper.Write(oInvoiceItem, "COMMENT_2", (String)"Comment 2");
   SDOHelper.Write(oInvoiceItem, "UNIT_OF_SALE", (String)"");
   SDOHelper.Write(oInvoiceItem, "FULL_NET_AMOUNT", (Int32)200);
   SDOHelper.Write(oInvoiceItem, "TAX_RATE", (Int32)20);

   //Update the invoice
   if (oInvoicePost.Update())
   {
       MessageBox.Show("Invoice Posted Successfully", "C# SDO Examples");
   }
   else
   {
       MessageBox.Show("Failed to create Invoice", "C# SDO Examples");
   }

   //Disconnect
   oWS.Disconnect();
}
catch (Exception ex)
{
    MessageBox.Show("SDO Generated the Following Error: \n\n" + ex.Message, "Error!");
}

This create invoice perfectly but i don't know how to make the invoice's ledger update so that it could appear in the customer's list in sage 50. enter image description here

An invoice can appear in the customer activity list when we update the ledger in the invoice list like in the following screen shot. enter image description here

enter image description here Thats why i need an sdk code which will do this.

Solution

  • So finally i got the solution for the problem. I got actually two different solutions. To show the invoice into the customer's activity we could either create a Posting a "Customer Batch Sales Invoice" or could "updade invoice to ledger". These both examples can find in the sage 50 SDO documentation file (.chm). I tried first the batch invoice code which is below:-

    //Declare Variables
SageDataObject250.SDOEngine oSDO = new SageDataObject250.SDOEngine();
SageDataObject250.WorkSpace oWS;
SageDataObject250.SalesRecord oSalesRecord;
SageDataObject250.TransactionPost oTransactionPost;
SageDataObject250.SplitData oSplitData;
String szDataPath;

//Instantiate WorkSpace
oWS = (SageDataObject250.WorkSpace)oSDO.Workspaces.Add("Example");

//Show select company dialog
szDataPath = oSDO.SelectCompany("C:\\ProgramData\\Sage\\Accounts\\2020");

//Try a connection, will throw an exception if it fails
try
{
  //Leaving the username and password blank generates a login dialog
  oWS.Connect(szDataPath, "", "", "SDO EXAMPLE");

  //Instantiate Objects
  oTransactionPost = (SageDataObject250.TransactionPost)oWS.CreateObject("TransactionPost");
  oSalesRecord = (SageDataObject250.SalesRecord)oWS.CreateObject("SalesRecord");

  //Read the first customer
  oSalesRecord.MoveFirst();

  //Populate Header Fields
  //Note:
  //The Account_Ref field must be populated with a valid
  //Customer account reference
  SDOHelper.Write(oTransactionPost.Header, "ACCOUNT_REF",
(String)SDOHelper.Read(oSalesRecord, "ACCOUNT_REF"));
  SDOHelper.Write(oTransactionPost.Header, "DATE", (DateTime)DateTime.Today);
  SDOHelper.Write(oTransactionPost.Header, "POSTED_DATE", (DateTime)DateTime.Today);
  SDOHelper.Write(oTransactionPost.Header, "TYPE", (Byte)SageDataObject250.TransType.sdoSI);
  SDOHelper.Write(oTransactionPost.Header, "INV_REF", (String)"INV001");

  //Populate the foreign currency fields
  SDOHelper.Write(oTransactionPost.Header, "CURRENCY", SDOHelper.Read(oSalesRecord, "CURRENCY"));

  //Loop for the number of splits
  //Note:
  //The transaction can have 1 or many splits
  for (Int16 i = 1; i <= 2; i++)
  {
    //Add a split to the headers item collection
    oSplitData = (SageDataObject250.SplitData)SDOHelper.Add(oTransactionPost.Items);

    //Populate split fields
    SDOHelper.Write(oSplitData,"TYPE",
    SDOHelper.Read(oTransactionPost.Header,"TYPE"));
    SDOHelper.Write(oSplitData, "NOMINAL_CODE", (String)"4000");
    SDOHelper.Write(oSplitData, "TAX_CODE", (Int16)1);
    SDOHelper.Write(oSplitData, "NET_AMOUNT", (Double)100);
    SDOHelper.Write(oSplitData, "TAX_AMOUNT", (Double)17.5);
    SDOHelper.Write(oSplitData, "DETAILS", (String)"Split Details ");
    SDOHelper.Write(oSplitData, "DATE",
  (DateTime)SDOHelper.Read(oTransactionPost.Header, "DATE"));
  }

  //Update the transaction post object
  if (oTransactionPost.Update())
  {
      MessageBox.Show("Transaction Posted Successfully");
  }
  else
  {
      MessageBox.Show("Transaction Post Failed");
  }

  //Disconnect
  oWS.Disconnect();
}
catch (Exception ex)
{
     MessageBox.Show("SDO Generated the Following Error: \n\n" + ex.Message, "Error!");
}

    The above code show the created invoice into the customer's activity but later i also required to show the created invoice into the product's activity also which is lined into the invoice so found later another example in documentation which is called "Update invoice to ledger" and here is the code below for that:

    //Declare Variables 
SageDataObject250.SDOEngine oSDO = new SageDataObject250.SDOEngine();
SageDataObject250.WorkSpace oWS;
SageDataObject250.InvoiceRecord invoiceRecord;
SageDataObject250.IUpdateLedgers updateLedgers;
String szDataPath;

//Instantiate WorkSpace
oWS = (SageDataObject250.WorkSpace)oSDO.Workspaces.Add("Example");

//Show select company dialog
szDataPath = oSDO.SelectCompany("C:\\ProgramData\\Sage\\Accounts\\2020");

//Try a connection, will throw an exception if it fails
try
{
    //Leaving the username and password blank generates a login dialog
    oWS.Connect(szDataPath, "", "", "SDO EXAMPLE");

    Console.WriteLine("Connected");

    //Instantiate the Invoice Record object
    invoiceRecord = oWS.CreateObject("INVOICERECORD") as SageDataObject250.InvoiceRecord;

    //Set the Invoice Number on the Invoice Record for the Find Method
    SDOHelper.Write(invoiceRecord, "INVOICE_NUMBER", "89");

    //Find the invoice record, false is a fulll match, true a partial match
    if (invoiceRecord.Find(false))
    {
        //Instantiate the UpdateLedgers object casting it as an InvoiceRecord
        updateLedgers = invoiceRecord as SageDataObject250.IUpdateLedgers;

        //Ensure the object is not null
        if (updateLedgers != null)
        {
            //Check to see if posted, returns a boolean
            if (updateLedgers.Post())
            {
                Console.WriteLine("Invoice updated");
                Console.ReadLine();
            }
            else
            {
                Console.WriteLine("Invoice failed to update");
                Console.ReadLine();
            }
        }
    }
        //The invoice can't be found
    else
    {
        Console.WriteLine("Invoice could not be located");
        Console.ReadLine();
    }
    //Disconnect
    oWS.Disconnect();
}
catch (Exception ex)
{
    Console.WriteLine("SDO Generated the Following Error: \n\n" + ex.Message, "Error!");
    Console.ReadLine();
}
//Marshal objects to be released ensuring they are picked up by the GC
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oSDO);
oSDO = null;
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(oWS);
oWS = null;
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(invoiceRecord);
invoiceRecord = null;
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(updateLedgers);
updateLedgers = null;

}

    This code will update the invoice to ledger if the invoice has not posted yet. Do not use below code to post the invoice if you want to update into ledger

    Service1.SDOHelper.Write(oInvoicePost.Header, "POSTED_CODE", (Byte) 1);