Chapter 6_Fertilizer Invoicing Integration

This chapter describes the changes made for the Fertilizer Invoicing Integration. Cresco Ag's OptiGro records the shipment transaction, calls their web service and writes the transaction to a SQL Invoice interface table. On a timed basis, Cinch reads the OptiGro Invoice Interface table and creates Cinch Sales Shipments.
This chapter contains the following sections:

• SQL Job

• Changes to Integration Error Log

• Changes to Sales Shipment Invoicing

• Changes to Additional Line Information Inquiry

SQL Job
A SQL Job will run the proc that will import the shipments from Import tables into the Cinch Shipment Invoicing tables.

1. User will set up a SQL timed job that will run the IRASImportFertilizerInvoices sql proc at the desired interval. 

   1. Only shipments where the CinchShipmentNumber field is blank will be imported.

2. The shipments will be imported using the following field mapping:

   1. Shipment Number, the system will get the next available Shipment Number.

   2. The following fields will be read from the Invoice table:

      1. Customer Number is the AccountNumber in the table.

      2. Ship Date is the ShipDate in the table.

      3. Customer PO Number is the PONumber in the table.

      4. Location Code (Site) is the ShippingLocationKey in the table.

      5. Branch ID is the SalesLocationKey in the table.

      6. Comment Text is the Note in the table.

      7. Cresco Invoice ID is the InvoiceID in the table.

   3. The following fields will be read from the Invoice Item table:

      1. Item Number is the Number in the table.

      2. Item Description is the Name in the table.

      3. Quantity is the Quantity in the table.

      4. Quantity UOM is the QuantityUOM field in the table.

      5. Price is the Price in the table.

      6. Price UOM is the PriceUOM in the table.

      7. Short Blend Component Sequence is set based on the BlendComponent field in the table.

      8. Short Blend Extension is set based on the BlendComponent field in the table.

      9. Price Doc Type is the DocumentType in the table. This is informational only passed in from Cresco.

      10. Price Doc Number is the DocumentNumber in the table. This is informational only passed in from Cresco.

3. During the Import the system will set the Processing field in the Invoice table to 1. This will lock the record so Cresco can't update or delete it will the import is taking place.

4. After a successful import the system will do the following in the Invoice table:

   1. Set the Processing field back to 0.

   2. Write the System Date back to the ProcessDate in the table.

   3. Write the Shipment Number back to the CinchShipmentNumber in the table.

Changes to Integration Error Log
Integration Error Log has been updated with the addition of the Fertilizer Process checkbox.

1. Open Integration Error Log

(Microsoft Dynamics GP>Tools>Routines>Cinch Sales>Integration Log)

1. When the Process, Fertilizer checkbox is marked, any Exception Errors that occurred during the Fertilizer Import process that were logged in the IRASIntegrationExcMSTR table in Cinch can be viewed in the Integration Error Log screen.

2. The following fields will be validated against the information in the Cinch and GP tables and any errors will be logged as Exceptions:

   1. Customer Number

   2. Branch Number

   3. Location Code

   4. Item Number

   5. Item Location

   6. Quantity UOM

   7. Price UOM

3. Errors will need to be deleted from the error log and fixed before they will be imported back into Shipment Invoicing.

Changes to Sales Shipment Invoicing
Sales Shipment Invoicing has been modified with the addition of the Cresco ID, Price Doc Type and Price Doc Number fields to the scrolling window.

1. Open Sales Shipment Invoicing

(Transactions>Cinch Sales>Shipment Invoicing)

1. Data populated on the Cresco shipment:

   1. Information defaulted from the Customer Extension Maintenance.

      1. Bill To Address ID

      2. Ship To Address ID

      3. Payment Terms ID

      4. Ship Method

      5. Salesperson ID

      6. Short Blend checkbox determines if the blend should be shown as long or short blend.

         1. If the Short Blend checkbox is marked the blend component lines will NOT be displayed on the invoice.

         2. If the Short Blend checkbox is NOT marked the blend component lines will be displayed on the invoice.

   2. Created User, Date and Time, are the System User, Date and Time in SQL.

   3. Last Modified User, Date and Time, are the System User, Date and Time in SQL

2. Long Blend/Short Blends

   1. Blend Item – Cresco will set the BlendSummary field to indicate the custom Blend item on the invoice.

      1. The Number on the line that is flagged as a BlendSummary will be a valid Dynamics GP Non-Inventory item.

   2. Blend Item Description, is the description to be used for the Blend Item and will display on the invoice for the Blend Item. Cresco will write that description to the Name field.

   3. Each component that makes up the Blend Item will be listed as an invoice line. These lines are actual inventory items and when the invoice is posted the inventory will be affected. Cresco will write the BlendComponent as true to the invoice line table for each line that is a component.

3. Cresco ID is the Cresco Invoice ID that was imported.

4. Region comes from the Branch imported from the table.

5. The user may know the Cinch Document number that is used to drive pricing in the case the following fields will be populated.

   1. Price Doc Type is an informational field from Cresco displaying the document type used Price this shipment.

   2. Price Doc Number is an informational field from Cresco displaying the document number used to Price this shipment.

   3. Even though the pricing document may be specified in the shipment, it will be the user's responsibility to apply the Prepay or the Sales Contract to the Counter Sale or Invoice line.

6. The From Source and To Source dropdown list restriction has been updated with the addition of CRESCO.

7. Delete Shipments, if transaction came from Cresco, the user will have the option to re-open the shipment for re-import or not.

   1. Deleted shipments will be tracked for audit purposes.

   2. Deleted shipment numbers will NOT be reused.

Changes to Additional Line Information Inquiry
Additional Line Information has been modified with the addition of the Cresco Invoice ID, Price Doc Type and Price Doc Number fields.

1. Open Additional Line Information

(Transactions>Cinch Sales>Invoices or Transactions>Cinch Sales>Counter Sales)