iVend Database Database : Integration_Consolidated_Transaction Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@TransactionKey

In

transaction key for which the various transactions that needs to be pulled from iVend

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

ConTrxTransaction

Table

Stores the Consolidated Transaction detail values

1

ConTrxTransactionGiftCertificate

Table

1

ConTrxTransactionSaleItem

Table

Stores the consolidated values for the Transaction Sale Item

1

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

InvBatch

Table

Batch details gets affected when doing any transaction with item which has been batch tracked.

1

InvProduct

Table

Stores the product related details.

1

InvSerial

Table

Serial details gets affected when doing any transaction with the item which has been serial tracked.

1

InvUOMGroupDetail

Table

Stores the details for the UOM group

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TaxTaxCode

Table

TaxCode is defined at the SBO and it's replicated from SBO to iVend. We can only view the TaxCodes at MC and associate them with the different objects, like Product, Customer, Enterprise, etc.

1

TrxTransactionFulfillment

Table

Defines a list of all those transactions which have a fulfillment plan attached to it.

1

TrxTransactionFulfillmentDetail

Table

Defines the details of all the transactions which have a fulfillment plan attached to them.

1

--Based on the output of this procedure the same transaction is getting posted in SAP Business One.

--Neither this procedure returns the various surcharges applicable on the transaction posted in iVend

--nor it returns the various payments taken or paid out against the transaction

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_Consolidated_Transaction]

@TransactionKey nvarchar(50)

AS

BEGIN

SET NOCOUNT ON;

-- ================================================================================

-- B1. AR INVOICE (NO FULFILLMENT OR FULFILLMENT WITH INVENTORY ALLOCATION ON SALE)

-- ================================================================================

  SELECT 2             AS DocType,

      'ARI'             AS DOCDESC,

      13             As SBOOBJECTTYPE,

      ConTrxTransaction.TransactionKey     AS U_TRANSACTIONKEY,

      ConTrxTransaction.TransactionId       AS U_TRANSACTIONID,

      RtlStore.SiteID           AS U_SITEID,

      Convert(varchar,ConTrxTransaction.BusinessDate,112) AS H_DOCDATE,

      Convert(varchar,ConTrxTransaction.BusinessDate,112) AS H_DOCDUEDATE,

      Convert(varchar,ConTrxTransaction.BusinessDate,112) AS H_TAXDATE,

      CusCustomer.AccountingId       AS H_CARDCODE,

      ''             AS H_CARDNAME,

      ConTrxTransaction.ActualDiscountPercent     AS H_DISCOUNTPERCENT,

      (Select ID from PmtCurrency Where IsBaseCurrency = 1) As H_DOCCURRENCY,

      Convert(varchar,ConTrxTransaction.BusinessDate,112) AS H_DOCMENTINSTALLMENTOBJECT_DUEDATE,

      100             As H_DOCMENTINSTALLMENTOBJECT_PERCENTAGE,

      TransactionItemKey         AS U_TRANSACTIONLINEKEY,

      InvWarehouse.ID           AS L_WAREHOUSECODE,

      InvProduct.AccountingID         AS L_ITEMCODE,

      ConTrxTransactionSaleItem.Description     AS L_ITEMDESCRIPTION,

      NULL             AS L_SHIPDATE,

      (

      Case

      When isnull(ConTrxTransactionSaleItem.TaxCodeKey,'0') = '0' Then '0'

      Else

        (Select AccountingID From TaxTaxCode Where TaxTaxCode.TaxCodeKey = ConTrxTransactionSaleItem.TaxCodeKey)

      End

      )             AS L_TAXCODE,

      ConTrxTransactionSaleItem.BasePrice     AS L_UNITPRICE,

      ConTrxTransactionSaleItem.Quantity     AS L_QUANTITY,

      ConTrxTransactionSaleItem.DiscountPercent   AS L_DISCOUNTPERCENT,

      ConTrxTransactionSaleItem.OriginalDetailKey,

      ConTrxTransactionSaleItem.OriginalDocumentKey,

      (

      CASE

      WHEN ConTrxTransactionSaleItem.HasSerialNumber = 1 THEN

        (Select CAST(SystemNumber as nvarchar(100)) From InvSerial Where InvSerial.SerialKey = ConTrxTransactionSaleItem.ProductDetailKey)

      WHEN ConTrxTransactionSaleItem.HasBatchNumber = 1 THEN

        (Select BatchNumber From InvBatch Where InvBatch.BatchKey = ConTrxTransactionSaleItem.ProductDetailKey)

      ELSE '' END

      )             AS L_SYSTEMSERIALBATCHNUMBER,

      (

      CASE

      WHEN ConTrxTransactionSaleItem.HasSerialNumber = 1 THEN

        (Select CAST(SerialNumber as nvarchar(100)) From InvSerial Where InvSerial.SerialKey = ConTrxTransactionSaleItem.ProductDetailKey)

      WHEN ConTrxTransactionSaleItem.HasBatchNumber = 1 THEN

        (Select BatchNumber From InvBatch Where InvBatch.BatchKey = ConTrxTransactionSaleItem.ProductDetailKey)

      ELSE '' END

      )             AS L_SERIALBATCHNUMBER,

      (

      CASE

      WHEN ConTrxTransactionSaleItem.HasSerialNumber = 1 THEN

        (Select ExpirationDate From InvSerial Where InvSerial.SerialKey = ConTrxTransactionSaleItem.ProductDetailKey)

      End

      )             AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

      Convert(Decimal(20, 5), ConTrxTransactionSaleItem.TotalBeforeSaleDiscount / NullIf(ConTrxTransactionSaleItem.Quantity, 0)) As L_PRICEAFTERVAT,

      ISNULL(CONVERT(Decimal(20, 5), ConTrxTransactionSaleItem.TotalPostSaleDiscount - ConTrxTransactionSaleItem.Tax),0) AS L_LINETOTAL,

      ISNULL(CONVERT(Decimal(20, 5), ConTrxTransactionSaleItem.Tax),0)         AS L_LINETAX,

      ConTrxTransactionSaleItem.HasSerialNumber   AS HasSerialNumber,

      ConTrxTransactionSaleItem.HasBatchNumber   AS HasBatchNumber,

      ConTrxTransactionSaleItem.TaxRate     As L_TaxRate ,

      NULL             AS LayawayKnockOffAmount,

      InvWarehouse.AccountingID       As StoreWH,

      NULL             As ProductPromisedDate,

      '-1'             As H_SalesPerson,

      CASE WHEN isNull(ConTrxTransactionSaleItem.UserKey, '-1') = '0' THEN -1 ELSE isNull(ConTrxTransactionSaleItem.UserKey, '-1') END As L_SalesPerson,

      ''             As H_Comment,

      ''             As L_Comment,

      isNull(InvProduct.IsGiftCertificate, 0)     As IsGiftCertificate,

      ISNULL(BookingWarehouse.ID, InvWarehouse.AccountingID) AS BookingWarehouse,

      ISNULL(DeliveryWarehouse.ID, InvWarehouse.AccountingID) AS DeliveryWarehouse,

      ''             AS Contact,

      ISNULL(ConTrxTransaction.CustomerRefNumber, '')   AS  H_CustomerRefNumber,

      'FALSE'             AS H_HasCancelledSalesOrder,

      '0'             AS H_ShippingTypeKey,

      ''             AS H_TrackingNumber,

      ''             AS L_DeliveryAddress,

      0             AS H_IsARBill,

      ''             AS H_FolioNumber,

      'FALSE'             AS H_ManualFolioNumber,

      ISNULL((SELECT UOMKey FROM InvUOMGroupDetail WHERE UOMGroupDetailKey = ConTrxTransactionSaleItem.UOMGroupDetailKey),'0')               AS L_UOMKey ,

      --ISNULL(UOM.UOMKey,0)         AS L_UOMKey,

      ISNULL(ConTrxTransactionSaleItem.UOMQuantity,0 ) AS L_UOM_Quantity,

      CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END   AS H_BranchKey,

      ISNULL(ConTrxTransaction.HasSaleExchanges, 0)     AS HasSaleExchanges,

      ISNULL(ConTrxTransaction.Total, 0)         AS H_Total,

      ISNULL(InvProduct.IsTaxOnly, 0)         AS L_IsTaxOnly

  FROM [ConTrxTransaction]

  INNER JOIN RtlStore ON ConTrxTransaction.StoreKey = RtlStore.StoreKey

  INNER JOIN CusCustomer ON ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

  INNER JOIN ConTrxTransactionSaleItem ON ConTrxTransactionSaleItem.TransactionKey = ConTrxTransaction.TransactionKey

  INNER JOIN InvWarehouse ON ConTrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

  INNER JOIN InvProduct ON ConTrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

  LEFT OUTER JOIN InvWarehouse As BookingWarehouse On ConTrxTransactionSaleItem.BookingWarehouseKey = BookingWarehouse.WarehouseKey

  LEFT OUTER JOIN InvWarehouse As DeliveryWarehouse On ConTrxTransactionSaleItem.DeliveryWarehouseKey = DeliveryWarehouse.WarehouseKey

  --LEFT OUTER JOIN InvUOMGroupDetail As UOMGroupDetail On ConTrxTransactionSaleItem.UOMGroupDetailKey = UOMGroupDetail.UOMGroupDetailKey

  --LEFT OUTER JOIN InvUOM As UOM On UOMGroupDetail.UOMKey = UOM.UOMKey

WHERE  ConTrxTransaction.TransactionKey = @TransactionKey

  AND ConTrxTransaction.IsSuspended = 0

  AND [ConTrxTransactionSaleItem].[Type] = 0

UNION

SELECT CASE WHEN ConTrxTransactionGiftCertificate.Type In (0, 1) THEN 2 ELSE 4 END   AS DocType,

  CASE WHEN ConTrxTransactionGiftCertificate.Type In (0, 1) THEN 'ARI' ELSE 'CRM' END AS DOCDESC,

  CASE WHEN ConTrxTransactionGiftCertificate.Type In (0, 1) THEN 13 ELSE 14 END   AS SBOOBJECTTYPE,

  ConTrxTransaction.TransactionKey             AS U_TRANSACTIONKEY,

  ConTrxTransaction.TransactionId               AS U_TRANSACTIONID,

  RtlStore.SiteID                   AS U_SITEID,

  CONVERT(VARCHAR,ConTrxTransaction.BusinessDate,112)         AS H_DOCDATE,

  CONVERT(VARCHAR,ConTrxTransaction.BusinessDate,112)         AS H_DOCDUEDATE,

  CONVERT(VARCHAR,ConTrxTransaction.BusinessDate,112)         AS H_TAXDATE,

  CusCustomer.AccountingId               AS H_CARDCODE,

  ''                     AS H_CARDNAME,

  0                     AS H_DISCOUNTPERCENT,

  (SELECT ID FROM PmtCurrency WHERE IsBaseCurrency = 1)         AS H_DOCCURRENCY,

  CONVERT(VARCHAR,ConTrxTransaction.BusinessDate,112)         AS H_DOCMENTINSTALLMENTOBJECT_DUEDATE,

  100                     AS H_DOCMENTINSTALLMENTOBJECT_PERCENTAGE,

  ConTrxTransactionGiftCertificate.TransactionGiftCertificateKey       AS U_TRANSACTIONLINEKEY,

  InvWarehouse.ID                   AS L_WAREHOUSECODE,

  InvProduct.AccountingID                 AS L_ITEMCODE,

  ConTrxTransactionGiftCertificate.Description           AS L_ITEMDESCRIPTION,

  NULL                     AS L_SHIPDATE,

  '0'                     AS L_TAXCODE,

  ABS(ConTrxTransactionGiftCertificate.Price)           AS L_UNITPRICE,

  1                     AS L_QUANTITY,

  0                     AS L_DISCOUNTPERCENT,

  '0'                     AS OriginalDetailKey,

  '0'                     AS OriginalDocumentKey,

  ''                     AS L_SYSTEMSERIALBATCHNUMBER,

  ''                     AS L_SERIALBATCHNUMBER,

  NULL                     AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

  ISNULL(ABS(ConTrxTransactionGiftCertificate.Price),0)         AS L_PRICEAFTERVAT,

  ISNULL(ABS(ConTrxTransactionGiftCertificate.Price),0)         AS L_LINETOTAL,

  0                     AS L_LINETAX,

  0                     AS HasSerialNumber,

  0                     AS HasBatchNumber,

  0                     AS L_TaxRate ,

  NULL                     AS LayawayKnockOffAmount,

  InvWarehouse.AccountingID               AS StoreWH,

  NULL                   AS ProductPromisedDate,

  '-1'                     AS H_SalesPerson,

  '-1'                     AS L_SalesPerson,

  ''                     AS H_Comment,

  ''                     AS L_Comment,

  ISNULL(InvProduct.IsGiftCertificate, 0)           AS IsGiftCertificate,

  InvWarehouse.AccountingID               AS BookingWarehouse,

  InvWarehouse.AccountingID               AS DeliveryWarehouse,

  ''                     AS Contact,

  ISNULL(ConTrxTransaction.CustomerRefNumber, '')         AS H_CustomerRefNumber,

      'FALSE'             AS H_HasCancelledSalesOrder,

      '0'             AS H_ShippingTypeKey,

      ''             AS H_TrackingNumber,

      ''             AS L_DeliveryAddress,

0                       AS H_IsARBill,

      ''             AS H_FolioNumber,

      'FALSE'             AS H_ManualFolioNumber,

      '0'             AS L_UOMKey,

1             AS L_UOM_Quantity,

CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END   AS H_BranchKey,

ISNULL(ConTrxTransaction.HasSaleExchanges, 0)     AS HasSaleExchanges,

ISNULL(ConTrxTransaction.Total, 0)         AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0)         AS L_IsTaxOnly

FROM ConTrxTransaction INNER JOIN RtlStore ON ConTrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer ON ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN ConTrxTransactionGiftCertificate ON ConTrxTransactionGiftCertificate.TransactionKey = ConTrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON ConTrxTransactionGiftCertificate.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON ConTrxTransactionGiftCertificate.ProductKey = InvProduct.ProductKey

WHERE ConTrxTransaction.TransactionKey = @TransactionKey

-- ========================================================================

-- D. A/R CREDIT MEMO

-- ========================================================================

UNION

SELECT 4                 AS DocType,

  'CRM'               AS DocDesc,

  14                 As SBOObjectType,

   ConTrxTransaction.TransactionKey         AS U_TRANSACTIONKEY,

   ConTrxTransaction.TransactionId         AS U_TRANSACTIONID,

   RtlStore.SiteID             AS U_SITEID,

  Convert(varchar,ConTrxTransaction.BusinessDate,112)     AS H_DOCDATE,

  Convert(varchar,ConTrxTransaction.BusinessDate,112)     AS H_DOCDUEDATE,

  Convert(varchar,ConTrxTransaction.BusinessDate,112)     AS H_TAXDATE,

   CusCustomer.AccountingId           AS H_CARDCODE,

  ''                 AS H_CARDNAME,

   ConTrxTransaction.ActualDiscountPercent       AS H_DISCOUNTPERCENT,

   (Select ID from PmtCurrency Where IsBaseCurrency = 1)   As H_DOCCURRENCY,

  Convert(varchar,ConTrxTransaction.BusinessDate,112)     AS H_DOCMENTINSTALLMENTOBJECT_DUEDATE,

  100                 As H_DOCMENTINSTALLMENTOBJECT_PERCENTAGE,

   ConTrxTransactionSaleItem.TransactionItemKey     AS U_TRANSACTIONLINEKEY,

   (

    Case FulFillmentHeader.InventoryAllocationMethod

    When 1 Then '-1'

    Else InvWarehouse.ID

    End

   )                 AS L_WAREHOUSECODE,

   InvProduct.AccountingID           AS L_ITEMCODE,

   ConTrxTransactionSaleItem.Description       AS L_ITEMDESCRIPTION,

  NULL               AS L_SHIPDATE,

   (

    Case

    When isnull(ConTrxTransactionSaleItem.TaxCodeKey,'0') = '0' Then '0'

    Else

     (Select AccountingID From TaxTaxCode Where TaxTaxCode.TaxCodeKey = ConTrxTransactionSaleItem.TaxCodeKey)

    End

   )                 AS L_TAXCODE,

   ConTrxTransactionSaleItem.BasePrice         As L_UNITPRICE,

   ConTrxTransactionSaleItem.Quantity         AS L_QUANTITY,

   ConTrxTransactionSaleItem.DiscountPercent       AS L_DISCOUNTPERCENT,

   ConTrxTransactionSaleItem.OriginalDetailKey       AS OriginalDetailKey,

   ConTrxTransactionSaleItem.OriginalDocumentKey     AS OriginalDocumentKey,

   (

    CASE

    WHEN ConTrxTransactionSaleItem.HasSerialNumber = 1 THEN

     (Select CAST(SystemNumber as nvarchar(100)) From InvSerial Where InvSerial.SerialKey = ConTrxTransactionSaleItem.ProductDetailKey)

    WHEN ConTrxTransactionSaleItem.HasBatchNumber = 1 THEN

     (Select BatchNumber From InvBatch Where InvBatch.BatchKey = ConTrxTransactionSaleItem.ProductDetailKey)

    ELSE '' END

   )                 AS L_SYSTEMSERIALBATCHNUMBER,

   (

    CASE

    WHEN ConTrxTransactionSaleItem.HasSerialNumber = 1 THEN

     (Select CAST(SerialNumber as nvarchar(100)) From InvSerial Where InvSerial.SerialKey = ConTrxTransactionSaleItem.ProductDetailKey)

    WHEN ConTrxTransactionSaleItem.HasBatchNumber = 1 THEN

     (Select BatchNumber From InvBatch Where InvBatch.BatchKey = ConTrxTransactionSaleItem.ProductDetailKey)

    ELSE '' END

   )                 AS L_SERIALBATCHNUMBER,

   (

    CASE

    WHEN ConTrxTransactionSaleItem.HasSerialNumber = 1 THEN

     (Select ExpirationDate From InvSerial Where InvSerial.SerialKey = ConTrxTransactionSaleItem.ProductDetailKey)

    End

   )                 AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

  Convert(Decimal(20, 5), ConTrxTransactionSaleItem.TotalBeforeSaleDiscount / NullIf(ConTrxTransactionSaleItem.Quantity, 0)) As L_PRICEAFTERVAT,

  ISNULL(CONVERT(Decimal(20, 5), ConTrxTransactionSaleItem.TotalPostSaleDiscount - ConTrxTransactionSaleItem.Tax),0) AS L_LINETOTAL,

  ISNULL(CONVERT(Decimal(20, 5), ConTrxTransactionSaleItem.Tax),0)         AS L_LINETAX,

   ConTrxTransactionSaleItem.HasSerialNumber       AS HasSerialNumber,

   ConTrxTransactionSaleItem.HasBatchNumber       AS HasBatchNumber,

   ConTrxTransactionSaleItem.TaxRate         As L_TaxRate,

  NULL               AS LayawayKnockOffAmount,

   InvWarehouse.AccountingID           As StoreWH,

  NULL               As ProductPromisedDate,

  '-1'                 As H_SalesPerson,

  CASE WHEN isNull(ConTrxTransactionSaleItem.UserKey, '-1') = '0' THEN -1 ELSE isNull(ConTrxTransactionSaleItem.UserKey, '-1') END As L_SalesPerson,

  ''                 As H_Comment,

  ''                 As L_Comment,

  isNull(InvProduct.IsGiftCertificate, 0)       As IsGiftCertificate,

  ISNULL(BookingWarehouse.ID, InvWarehouse.AccountingID)   AS BookingWarehouse,

  ISNULL(DeliveryWarehouse.ID, InvWarehouse.AccountingID)   AS DeliveryWarehouse,

  ''                 AS Contact,

  ISNULL(ConTrxTransaction.CustomerRefNumber, '')     AS  H_CustomerRefNumber,

      'FALSE'             AS H_HasCancelledSalesOrder,

      '0'             AS H_ShippingTypeKey,

      ''             AS H_TrackingNumber,

      ''             AS L_DeliveryAddress,

  0                 AS H_IsARBill,

  ''                 AS H_FolioNumber,

  'FALSE'                 AS H_ManualFolioNumber,

  ISNULL( (SELECT UOMKey FROM InvUOMGroupDetail WHERE UOMGroupDetailKey =

    ConTrxTransactionSaleItem.UOMGroupDetailKey),'0')                         AS L_UOMKey ,

  --ISNULL(UOM.UOMKey,0)            AS L_UOMKey,

  ISNULL(ConTrxTransactionSaleItem.UOMQuantity,0 )     AS L_UOM_Quantity,

  CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END   AS H_BranchKey,

  ISNULL(ConTrxTransaction.HasSaleExchanges, 0)     AS HasSaleExchanges,

  ISNULL(ConTrxTransaction.Total, 0)         AS H_Total,

  ISNULL(InvProduct.IsTaxOnly, 0)         AS L_IsTaxOnly

FROM [ConTrxTransaction]

INNER JOIN RtlStore ON ConTrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer ON ConTrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN ConTrxTransactionSaleItem ON ConTrxTransactionSaleItem.TransactionKey = ConTrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON ConTrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON ConTrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN ConTrxTransactionSaleItem As SaleItem On ConTrxTransactionSaleItem.OriginalDetailKey = SaleItem.TransactionItemKey And SaleItem.HasFulfillment = 1

LEFT OUTER JOIN TrxTransactionFulfillmentDetail As FulFillmentDetail On SaleItem.TransactionItemKey = FulFillmentDetail.SourceDetailKey

LEFT OUTER JOIN TrxTransactionFulfillment As FulFillmentHeader On FulFillmentDetail.FulfillmentKey = FulFillmentHeader.FulfillmentKey And FulFillmentHeader.InventoryAllocationMethod = 1

LEFT OUTER JOIN InvWarehouse As BookingWarehouse On ConTrxTransactionSaleItem.BookingWarehouseKey = BookingWarehouse.WarehouseKey

LEFT OUTER JOIN InvWarehouse As DeliveryWarehouse On ConTrxTransactionSaleItem.DeliveryWarehouseKey = DeliveryWarehouse.WarehouseKey

--LEFT OUTER JOIN InvUOMGroupDetail As UOMGroupDetail On ConTrxTransactionSaleItem.UOMGroupDetailKey = UOMGroupDetail.UOMGroupDetailKey

--LEFT OUTER JOIN InvUOM As UOM On UOMGroupDetail.UOMKey = UOM.UOMKey

WHERE   (ConTrxTransaction.TransactionKey = @TransactionKey )

AND  (ConTrxTransaction.IsSuspended = 0)

AND [ConTrxTransactionSaleItem].[Type] = 1

ORDER BY DOCTYPE, HasSerialNumber desc, HasBatchNumber desc

END