iVend Database Database > Stored Procedures > Integration

Navigation: iVend Database Database > Stored Procedures >

iVend 6.6 Database Help

Integration_Transaction Stored Procedure

Collapse All Expand All

iVend Database Database : Integration_Transaction Stored Procedure

Properties

Creation Date	4/13/2015 12:00 PM
Encrypted
Ansi Nulls

Parameters

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

Objects that Integration_Transaction depends on

Database Object	Object Type	Description	Dep Level
CfgEnterprise	Table	Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.	1
TrxTransactionSalesPerson	Table	Defines a list of all the users to whom a certain commission has been given as a part of a transaction.	1

Procedure Source Code

--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_Transaction]

@TransactionKey NVARCHAR(50)

BEGIN

SET NOCOUNT ON;

Declare @lApplyLayawayOnSale BIT

Declare @CurrentLocalization INT

SELECT @lApplyLayawayOnSale = ApplyLayawayOnSale, @CurrentLocalization = Localization FROM CfgEnterprise

Select * Into #TrxTransactionSalesPerson

From TrxTransactionSalesPerson

Where TransactionSalesPersonKey In

(

Select MIN(TransactionSalesPersonKey)

From TrxTransactionSalesPerson

Where TransactionKey = @TransactionKey

And Type != 0

Group By TransactionKey, SourceKey, Type

)

Select * Into #TrxTransactionSalesPersonHeader

From TrxTransactionSalesPerson

Where TransactionSalesPersonKey In

(

Select MIN(TransactionSalesPersonKey)

From TrxTransactionSalesPerson

Where TransactionKey = @TransactionKey

And Type = 0

Group By TransactionKey, SourceKey, Type

)

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

-- A. ORDER (SPECIAL ORDER) RECORDS

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

SELECT

1 AS DOCTYPE,

'ORD' AS DOCDESC,

17 AS SBOOBJECTTYPE,

TrxTransaction.TransactionKey AS U_TRANSACTIONKEY,

TrxTransaction.TransactionId AS U_TRANSACTIONID,

RtlStore.SiteId AS U_SITEID,

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

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

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

CusCustomer.AccountingID AS H_CARDCODE,

TrxTransaction.TransactionCustomerName AS H_CARDNAME,

0 AS H_DISCOUNTPERCENT,

(SELECT Id FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS H_DOCCURRENCY,

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

100 AS H_DOCMENTINSTALLMENTOBJECT_PERCENTAGE,

TransactionOrderKey AS U_TRANSACTIONLINEKEY,

InvWarehouse.Id AS L_WAREHOUSECODE,

InvProduct.AccountingID AS L_ITEMCODE,

TrxTransactionOrder.Description AS L_ITEMDESCRIPTION,

(

CASE

WHEN HasFulfillment = 1

THEN

( SELECT b.PromisedDate

FROM TrxTransactionFulfillment B, TrxTransactionFulfillmentDetail C

WHERE B.FulFillmentKey = c.FulFillmentKey

AND TrxTransactionOrder.TransactionOrderKey = C.SourceDetailKey

AND B.TransactionKey = TrxTransactionOrder.TransactionKey

AND C.SourceType = 1

)

ELSE

NULL

END

) AS L_SHIPDATE,

(

CASE

WHEN ISNULL(TrxTransactionOrder.TaxCodeKey,'0') = '0'

THEN '0'

ELSE

(

SELECT AccountingID

FROM TaxTaxCode

WHERE TaxTaxCode.TaxCodeKey = TrxTransactionOrder.TaxCodeKey

)

END

) AS L_TaxCode,

TrxTransactionOrder.BasePrice AS L_UnitPrice,

TrxTransactionOrder.Quantity AS L_Quantity,

0 AS L_DiscountPercent,

TrxTransactionOrder.OriginalDetailKey AS OriginalDetailKey,

TrxTransactionOrder.OriginalDocumentKey AS OriginalDocumentKey,

'' AS L_SYSTEMSERIALBATCHNUMBER,

'' AS L_SERIALBATCHNUMBER,

NULL AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

ISNULL(CONVERT(Decimal(20, 5), TrxTransactionOrder.TotalPostSaleDiscount / NullIf(TrxTransactionOrder.Quantity, 0)),0) AS L_PRICEAFTERVAT,

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

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

CAST(0 AS bit) AS HasSerialNumber,

CAST(0 AS bit) AS HasBatchNumber,

TrxTransactionOrder.TaxRate AS L_TaxRate,

NULL AS LayawayKnockOffAmount,

InvWarehouse.AccountingID AS StoreWH,

CONVERT(VARCHAR, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) AS ProductPromisedDate,

ISNULL(trxTransactionSalesPersonH.UserKey, '-1') AS H_SalesPerson,

ISNULL(trxTransactionSalesPersonL.UserKey, '-1') AS L_SalesPerson,

Left(ISNULL(HeaderComment.Value, ''), 254) AS H_Comment,

Left(ISNULL(LineComment.Value, ''), 100) AS L_Comment,

ISNULL(InvProduct.IsGiftCertificate, 0) AS IsGiftCertificate,

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

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

ISNULL(Contact.FirstName,'') AS Contact,

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

TrxTransaction.HasCancelledSalesOrder AS H_HasCancelledSalesOrder,

isNull(TrxTransaction.ShippingTypeKey, '0') AS H_ShippingTypeKey,

isNull(TrxTransaction.TrackingNumber, '') AS H_TrackingNumber,

isnull(CfgAddress.Address1,'') AS L_DeliveryAddress,

0 AS H_IsARBill,

CASE @CurrentLocalization

WHEN 9 THEN CASE WHEN Trxtransaction.POSDocumentNumberSeriesKey !='0' AND ElectronicInvoice.FolioNumber IS NOT NULL THEN RIGHT(ISNULL(ElectronicInvoice.FolioNumber,''), LEN(ISNULL(ElectronicInvoice.FolioNumber, ''))- LEN(ISNULL(Series.Prefix, ''))) ELSE ISNULL(ElectronicInvoice.FolioNumber, '') END

WHEN 37 THEN CASE WHEN Trxtransaction.POSDocumentNumberSeriesKey !='0' AND ElectronicInvoice.FolioNumber IS NOT NULL THEN RIGHT(ISNULL(ElectronicInvoice.FolioNumber,''), LEN(ISNULL(ElectronicInvoice.FolioNumber, ''))- LEN(ISNULL(Series.Prefix, ''))) ELSE ISNULL(ElectronicInvoice.FolioNumber, '') END

WHEN 17 THEN ISNULL(ElectronicInvoice.FolioNumber, '')

ELSE ''

END AS H_FolioNumber,

ISNULL(TrxTransaction.ShowFolioNumber,'FALSE') AS H_ManualFolioNumber,

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

TrxTransactionOrder.UOMQuantity AS L_UOM_Quantity,

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

TrxTransaction.HasSaleExchanges AS HasSaleExchanges,

ISNULL(TrxTransaction.Total, 0) AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0) AS L_IsTaxOnly

FROM [TrxTransaction]

INNER JOIN RtlStore ON TrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN TrxTransactionOrder ON TrxTransactionOrder.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionOrder.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionOrder.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN TrxTransactionFulfillmentDetail On TrxTransaction.TransactionKey = TrxTransactionFulfillmentDetail.TransactionKey

AND TrxTransactionFulfillmentDetail.SourceDetailKey = TrxTransactionOrder.TransactionOrderKey

AND TrxTransactionFulfillmentDetail.SourceType = 1

LEFT OUTER JOIN TrxTransactionFulfillment ON TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

LEFT OUTER JOIN CfgAddress ON TrxTransactionFulfillment.AddressKey = CfgAddress.AddressKey

LEFT OUTER JOIN #TrxTransactionSalesPersonHeader AS trxTransactionSalesPersonH ON TrxTransaction.TransactionKey = trxTransactionSalesPersonH.SourceKey AND trxTransactionSalesPersonH.Type = 0

LEFT OUTER JOIN #TrxTransactionSalesPerson AS trxTransactionSalesPersonL ON TrxTransactionOrder.TransactionOrderKey = trxTransactionSalesPersonL.SourceKey AND trxTransactionSalesPersonL.Type = 2

LEFT OUTER JOIN CfgComment AS HeaderComment On TrxTransaction.CommentKey = HeaderComment.CommentKey

LEFT OUTER JOIN CfgComment AS LineComment On TrxTransactionOrder.CommentKey = LineComment.CommentKey

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

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

LEFT OUTER JOIN CfgContact As Contact On TrxTransaction.ContactKey = Contact.ContactKey

LEFT OUTER JOIN TrxTransactionElectronicInvoice As ElectronicInvoice On TrxTransaction.TransactionKey = ElectronicInvoice.TransactionKey

LEFT OUTER JOIN InvUOMGroupDetail As UOMGroupDetail On TrxTransactionOrder.UOMGroupDetailKey = UOMGroupDetail.UOMGroupDetailKey

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

LEFT OUTER JOIN RtlPOSDocumentNumberSeries As Series ON TrxTransaction.POSDocumentNumberSeriesKey = Series.POSDocumentNumberSeriesKey

WHERE (TrxTransaction.TransactionKey = @TransactionKey ) AND (TrxTransaction.IsSuspended = 0) AND (TrxTransaction.IsVoided = 0)

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

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

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

UNION

SELECT 2 AS DocType,

'ARI' AS DOCDESC,

13 AS SBOOBJECTTYPE,

TrxTransaction.TransactionKey AS U_TRANSACTIONKEY,

TrxTransaction.TransactionId AS U_TRANSACTIONID,

RtlStore.SiteID AS U_SITEID,

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

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

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

CusCustomer.AccountingId AS H_CARDCODE,

TrxTransaction.TransactionCustomerName AS H_CARDNAME,

0 AS H_DISCOUNTPERCENT,

(SELECT ID FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS H_DOCCURRENCY,

CONVERT(VARCHAR,TrxTransaction.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,

TrxTransactionSaleItem.Description AS L_ITEMDESCRIPTION,

CASE

WHEN HasFulfillment = 1 THEN

(

SELECT b.PromisedDate

FROM TrxTransactionFulfillment B, TrxTransactionFulfillmentDetail C

WHERE B.FulFillmentKey = c.FulFillmentKey

AND TrxTransactionSaleItem.TransactionItemKey = c.SourceDetailKey

AND B.TransactionKey = TrxTransactionSaleItem.TransactionKey

AND C.SourceType = 0

)

ELSE

NULL

END AS L_SHIPDATE,

CASE

WHEN ISNULL(TrxTransactionSaleItem.TaxCodeKey,'0') = '0' THEN

'0'

ELSE

(

SELECT AccountingID

FROM TaxTaxCode

WHERE TaxTaxCode.TaxCodeKey = TrxTransactionSaleItem.TaxCodeKey

)

END AS L_TAXCODE,

TrxTransactionSaleItem.BasePrice AS L_UNITPRICE,

TrxTransactionSaleItem.Quantity AS L_QUANTITY,

TrxTransactionSaleItem.DiscountPercent AS L_DISCOUNTPERCENT,

TrxTransactionSaleItem.OriginalDetailKey AS OriginalDetailKey,

TrxTransactionSaleItem.OriginalDocumentKey AS OriginalDocumentKey,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT CAST(SystemNumber AS nvarchar(100))

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(

SELECT BatchNumber

FROM InvBatch

WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey

)

ELSE

END AS L_SYSTEMSERIALBATCHNUMBER,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT CAST(SerialNumber AS nvarchar(100))

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(

SELECT BatchNumber

FROM InvBatch

WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey)

ELSE

END AS L_SERIALBATCHNUMBER,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT ExpirationDate

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

END AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

ISNULL(CONVERT(Decimal(20, 5), TrxTransactionSaleItem.TotalPostSaleDiscount / NullIf(TrxTransactionSaleItem.Quantity, 0)),0) AS L_PRICEAFTERVAT,

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

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

TrxTransactionSaleItem.HasSerialNumber AS HasSerialNumber,

TrxTransactionSaleItem.HasBatchNumber AS HasBatchNumber,

TrxTransactionSaleItem.TaxRate AS L_TaxRate ,

NULL AS LayawayKnockOffAmount,

InvWarehouse.AccountingID AS StoreWH,

NULL AS ProductPromisedDate,

ISNULL(trxTransactionSalesPersonH.UserKey, '-1') AS H_SalesPerson,

ISNULL(trxTransactionSalesPersonL.UserKey, '-1') AS L_SalesPerson,

Left(ISNULL(HeaderComment.Value, ''), 254) AS H_Comment,

Left(ISNULL(LineComment.Value, ''), 100) AS L_Comment,

ISNULL(InvProduct.IsGiftCertificate, 0) AS IsGiftCertificate,

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

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

ISNULL(Contact.FirstName,'') AS Contact,

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

TrxTransaction.HasCancelledSalesOrder AS H_HasCancelledSalesOrder,

isNull(TrxTransaction.ShippingTypeKey, '0') AS H_ShippingTypeKey,

isNull(TrxTransaction.TrackingNumber, '') AS H_TrackingNumber,

'' AS L_DeliveryAddress,

isNull(TrxTransaction.IsARBill, 0) AS H_IsARBill,

CASE @CurrentLocalization

WHEN 17 THEN ISNULL(ElectronicInvoice.FolioNumber, '')

ELSE ''

END AS H_FolioNumber,

ISNULL(TrxTransaction.ShowFolioNumber,'FALSE') AS H_ManualFolioNumber,

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

ISNULL(TrxTransactionSaleItem.UOMQuantity,0 ) AS L_UOM_Quantity,

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

TrxTransaction.HasSaleExchanges AS HasSaleExchanges,

CASE TrxTransaction.HasRefunds WHEN 1 THEN 0 ELSE ISNULL(TrxTransaction.Total, 0) END AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0) AS L_IsTaxOnly

FROM [TrxTransaction]

INNER JOIN RtlStore

ON TrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer

ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN TrxTransactionSaleItem

ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse

ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct

ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN #TrxTransactionSalesPersonHeader AS trxTransactionSalesPersonH

ON TrxTransaction.TransactionKey = trxTransactionSalesPersonH.SourceKey AND trxTransactionSalesPersonH.Type = 0

LEFT OUTER JOIN #TrxTransactionSalesPerson AS trxTransactionSalesPersonL

ON TrxTransactionSaleItem.TransactionItemKey = trxTransactionSalesPersonL.SourceKey AND trxTransactionSalesPersonL.Type = 1

LEFT OUTER JOIN CfgComment AS HeaderComment

On TrxTransaction.CommentKey = HeaderComment.CommentKey

LEFT OUTER JOIN CfgComment AS LineComment

On TrxTransactionSaleItem.CommentKey = LineComment.CommentKey

LEFT OUTER JOIN InvWarehouse As BookingWarehouse

On TrxTransactionSaleItem.BookingWarehouseKey = BookingWarehouse.WarehouseKey

LEFT OUTER JOIN InvWarehouse As DeliveryWarehouse

On TrxTransactionSaleItem.DeliveryWarehouseKey = DeliveryWarehouse.WarehouseKey

LEFT OUTER JOIN CfgContact As Contact

On TrxTransaction.ContactKey = Contact.ContactKey

LEFT OUTER JOIN TrxTransactionElectronicInvoice As ElectronicInvoice On TrxTransaction.TransactionKey = ElectronicInvoice.TransactionKey

LEFT OUTER JOIN InvUOMGroupDetail As UOMGroupDetail On TrxTransactionSaleItem.UOMGroupDetailKey = UOMGroupDetail.UOMGroupDetailKey

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

LEFT OUTER JOIN RtlPOSDocumentNumberSeries As Series ON TrxTransaction.POSDocumentNumberSeriesKey = Series.POSDocumentNumberSeriesKey

WHERE (TrxTransaction.TransactionKey = @TransactionKey ) AND (TrxTransaction.IsSuspended = 0) AND (TrxTransaction.IsVoided = 0) AND [TrxTransactionSaleItem].[Type] = 0

AND (HasFulfillment = 0 --No Fulfillment

OR ( HasFulfillment = 1 AND --Fulfillment With Inventory Allocation on Sale

(SELECT InventoryAllocationMethod

FROM TrxTransactionFulfillment INNER JOIN

TrxTransactionFulfillmentDetail

ON TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

WHERE (TrxTransactionSaleItem.TransactionItemKey = TrxTransactionFulfillmentDetail.SourceDetailKey)

AND (TrxTransactionFulfillment.TransactionKey = TrxTransactionSaleItem.TransactionKey)

AND (TrxTransactionFulfillmentDetail.SourceType = 0)

) = 0

)

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

-- B1. GIFT CERTIFICATE

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

UNION

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

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

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

TrxTransaction.TransactionKey AS U_TRANSACTIONKEY,

TrxTransaction.TransactionId AS U_TRANSACTIONID,

RtlStore.SiteID AS U_SITEID,

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

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

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

CusCustomer.AccountingId AS H_CARDCODE,

TrxTransaction.TransactionCustomerName AS H_CARDNAME,

0 AS H_DISCOUNTPERCENT,

(SELECT ID FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS H_DOCCURRENCY,

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

100 AS H_DOCMENTINSTALLMENTOBJECT_PERCENTAGE,

TrxTransactionGiftCertificate.TransactionGiftCertificateKey AS U_TRANSACTIONLINEKEY,

InvWarehouse.ID AS L_WAREHOUSECODE,

InvProduct.AccountingID AS L_ITEMCODE,

TrxTransactionGiftCertificate.Description AS L_ITEMDESCRIPTION,

NULL AS L_SHIPDATE,

'0' AS L_TAXCODE,

ABS(TrxTransactionGiftCertificate.Total) AS L_UNITPRICE,

ABS(TrxTransactionGiftCertificate.Quantity) AS L_QUANTITY,

TrxTransactionGiftCertificate.DiscountPercent AS L_DISCOUNTPERCENT,

'0' AS OriginalDetailKey,

'0' AS OriginalDocumentKey,

'' AS L_SYSTEMSERIALBATCHNUMBER,

'' AS L_SERIALBATCHNUMBER,

NULL AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

ISNULL(ABS(TrxTransactionGiftCertificate.Total),0) AS L_PRICEAFTERVAT,

ISNULL(ABS(TrxTransactionGiftCertificate.Total),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,

ISNULL(trxTransactionSalesPersonH.UserKey, '-1') AS H_SalesPerson,

ISNULL(trxTransactionSalesPersonL.UserKey, '-1') AS L_SalesPerson,

Left(ISNULL(HeaderComment.Value, ''), 254) AS H_Comment,

Left(ISNULL(LineComment.Value, ''), 100) AS L_Comment,

ISNULL(InvProduct.IsGiftCertificate, 0) AS IsGiftCertificate,

InvWarehouse.AccountingID AS BookingWarehouse,

InvWarehouse.AccountingID AS DeliveryWarehouse,

ISNULL(Contact.FirstName,'') AS Contact,

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

TrxTransaction.HasCancelledSalesOrder AS H_HasCancelledSalesOrder,

isNull(TrxTransaction.ShippingTypeKey, '0') AS H_ShippingTypeKey,

isNull(TrxTransaction.TrackingNumber, '') AS H_TrackingNumber,

'' AS L_DeliveryAddress,

0 AS H_IsARBill,

CASE @CurrentLocalization

WHEN 17 THEN ISNULL(ElectronicInvoice.FolioNumber, '')

ELSE ''

END AS H_FolioNumber,

ISNULL(TrxTransaction.ShowFolioNumber,'FALSE') AS H_ManualFolioNumber,

'0' AS L_UOMKey,

0 AS L_UOM_Quantity,

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

TrxTransaction.HasSaleExchanges AS HasSaleExchanges,

CASE TrxTransaction.HasRefunds WHEN 1 THEN 0 ELSE ISNULL(TrxTransaction.Total, 0) END AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0) AS L_IsTaxOnly

FROM [TrxTransaction]

INNER JOIN RtlStore

ON TrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer

ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN TrxTransactionGiftCertificate

ON TrxTransactionGiftCertificate.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse

ON TrxTransactionGiftCertificate.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct

ON TrxTransactionGiftCertificate.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN #TrxTransactionSalesPersonHeader AS trxTransactionSalesPersonH

ON TrxTransaction.TransactionKey = trxTransactionSalesPersonH.SourceKey

AND trxTransactionSalesPersonH.Type = 0 --INDICATES THIS IS SALE LEVEL SALES PERSON

LEFT OUTER JOIN #TrxTransactionSalesPerson AS trxTransactionSalesPersonL

ON TrxTransactionGiftCertificate.TransactionGiftCertificateKey = trxTransactionSalesPersonL.SourceKey

AND trxTransactionSalesPersonL.Type = 5 --INDICATES THIS IS FOR THE GIFT CERTIFICATE

LEFT OUTER JOIN CfgComment AS HeaderComment

On TrxTransaction.CommentKey = HeaderComment.CommentKey

LEFT OUTER JOIN CfgComment AS LineComment

On TrxTransactionGiftCertificate.CommentKey = LineComment.CommentKey

LEFT OUTER JOIN CfgContact As Contact

On TrxTransaction.ContactKey = Contact.ContactKey

LEFT OUTER JOIN TrxTransactionElectronicInvoice As ElectronicInvoice On TrxTransaction.TransactionKey = ElectronicInvoice.TransactionKey

LEFT OUTER JOIN RtlPOSDocumentNumberSeries As Series ON TrxTransaction.POSDocumentNumberSeriesKey = Series.POSDocumentNumberSeriesKey

WHERE TrxTransaction.TransactionKey = @TransactionKey

AND TrxTransaction.IsSuspended = 0 -- INDICATES THAT THE TRANSACTION WAS NOT SUSPENDED

AND TrxTransaction.IsVoided = 0

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

-- B2. AR INVOICE Against SO(NO FULFILLMENT OR FULFILLMENT WITH INVENTORY ALLOCATION ON SALE)

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

UNION

SELECT 2 AS DocType,

'ARISO' AS DOCDESC,

13 AS SBOOBJECTTYPE,

TrxTransaction.TransactionKey AS U_TRANSACTIONKEY,

TrxTransaction.TransactionId AS U_TRANSACTIONID,

RtlStore.SiteID AS U_SITEID,

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

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

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

CusCustomer.AccountingId AS H_CARDCODE,

TrxTransaction.TransactionCustomerName AS H_CARDNAME,

0 AS H_DISCOUNTPERCENT,

(SELECT ID FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS H_DOCCURRENCY,

CONVERT(VARCHAR,TrxTransaction.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,

TrxTransactionSaleItem.Description AS L_ITEMDESCRIPTION,

CASE WHEN HasFulfillment = 1 THEN

(

SELECT b.PromisedDate

FROM TrxTransactionFulfillment B, TrxTransactionFulfillmentDetail C

WHERE B.FulFillmentKey = c.FulFillmentKey

AND TrxTransactionSaleItem.TransactionItemKey = c.SourceDetailKey

AND B.TransactionKey = TrxTransactionSaleItem.TransactionKey

AND C.SourceType = 0

)

ELSE

NULL

END AS L_SHIPDATE,

CASE

WHEN ISNULL(TrxTransactionSaleItem.TaxCodeKey,'0') = '0' THEN

'0'

ELSE

(

SELECT AccountingID

FROM TaxTaxCode

WHERE TaxTaxCode.TaxCodeKey = TrxTransactionSaleItem.TaxCodeKey

)

END AS L_TAXCODE,

TrxTransactionSaleItem.BasePrice AS L_UNITPRICE,

TrxTransactionSaleItem.Quantity AS L_QUANTITY,

0 AS L_DISCOUNTPERCENT,

TrxTransactionSaleItem.OriginalDetailKey AS OriginalDetailKey,

TrxTransactionSaleItem.OriginalDocumentKey AS OriginalDocumentKey,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT CAST(SystemNumber AS nvarchar(100))

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(

SELECT BatchNumber

FROM InvBatch

WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey

)

ELSE

END AS L_SYSTEMSERIALBATCHNUMBER,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT CAST(SerialNumber AS nvarchar(100))

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(

SELECT BatchNumber

FROM InvBatch

WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey

)

ELSE

END AS L_SERIALBATCHNUMBER,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT ExpirationDate

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

END AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

ISNULL(CONVERT(Decimal(20, 5), TrxTransactionSaleItem.TotalPostSaleDiscount / NullIf(TrxTransactionSaleItem.Quantity, 0)),0) AS L_PRICEAFTERVAT,

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

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

TrxTransactionSaleItem.HasSerialNumber AS HasSerialNumber,

TrxTransactionSaleItem.HasBatchNumber AS HasBatchNumber,

TrxTransactionSaleItem.TaxRate AS L_TaxRate,

NULL AS LayawayKnockOffAmount,

InvWarehouse.AccountingID AS StoreWH,

NULL AS ProductPromisedDate,

ISNULL(trxTransactionSalesPersonH.UserKey, '-1') AS H_SalesPerson,

ISNULL(trxTransactionSalesPersonL.UserKey, '-1') AS L_SalesPerson,

Left(ISNULL(HeaderComment.Value, ''), 254) AS H_Comment,

Left(ISNULL(LineComment.Value, ''), 100) AS L_Comment,

ISNULL(InvProduct.IsGiftCertificate, 0) AS IsGiftCertificate,

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

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

ISNULL(Contact.FirstName,'') AS Contact,

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

TrxTransaction.HasCancelledSalesOrder AS H_HasCancelledSalesOrder,

isNull(TrxTransaction.ShippingTypeKey, '0') AS H_ShippingTypeKey,

isNull(TrxTransaction.TrackingNumber, '') AS H_TrackingNumber,

'' AS L_DeliveryAddress,

0 AS H_IsARBill,

CASE @CurrentLocalization

WHEN 9 THEN CASE WHEN Trxtransaction.POSDocumentNumberSeriesKey !='0' AND ElectronicInvoice.FolioNumber IS NOT NULL THEN RIGHT(ISNULL(ElectronicInvoice.FolioNumber,''), LEN(ISNULL(ElectronicInvoice.FolioNumber,''))- LEN(ISNULL(Series.Prefix,''))) ELSE ISNULL(ElectronicInvoice.FolioNumber,'') END

WHEN 37 THEN CASE WHEN Trxtransaction.POSDocumentNumberSeriesKey !='0' AND ElectronicInvoice.FolioNumber IS NOT NULL THEN RIGHT(ISNULL(ElectronicInvoice.FolioNumber,''), LEN(ISNULL(ElectronicInvoice.FolioNumber,''))- LEN(ISNULL(Series.Prefix,''))) ELSE ISNULL(ElectronicInvoice.FolioNumber,'') END

WHEN 17 THEN ISNULL(ElectronicInvoice.FolioNumber, '')

ELSE ''

END AS H_FolioNumber,

ISNULL(TrxTransaction.ShowFolioNumber,'FALSE') AS H_ManualFolioNumber,

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

TrxTransactionSaleItem.UOMQuantity AS L_UOM_Quantity,

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

TrxTransaction.HasSaleExchanges AS HasSaleExchanges,

ISNULL(TrxTransaction.Total, 0) AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0) AS L_IsTaxOnly

FROM [TrxTransaction]

INNER JOIN RtlStore

ON TrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer

ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN TrxTransactionSaleItem

ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse

ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct

ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN #TrxTransactionSalesPersonHeader AS trxTransactionSalesPersonH

ON TrxTransaction.TransactionKey = trxTransactionSalesPersonH.SourceKey AND trxTransactionSalesPersonH.Type = 0

LEFT OUTER JOIN #TrxTransactionSalesPerson AS trxTransactionSalesPersonL

ON TrxTransactionSaleItem.TransactionItemKey = trxTransactionSalesPersonL.SourceKey AND trxTransactionSalesPersonL.Type = 1

LEFT OUTER JOIN CfgComment AS HeaderComment

On TrxTransaction.CommentKey = HeaderComment.CommentKey

LEFT OUTER JOIN CfgComment AS LineComment

On TrxTransactionSaleItem.CommentKey = LineComment.CommentKey

LEFT OUTER JOIN InvWarehouse As BookingWarehouse

On TrxTransactionSaleItem.BookingWarehouseKey = BookingWarehouse.WarehouseKey

LEFT OUTER JOIN InvWarehouse As DeliveryWarehouse

On TrxTransactionSaleItem.DeliveryWarehouseKey = DeliveryWarehouse.WarehouseKey

LEFT OUTER JOIN CfgContact As Contact

On TrxTransaction.ContactKey = Contact.ContactKey

LEFT OUTER JOIN TrxTransactionElectronicInvoice As ElectronicInvoice On TrxTransaction.TransactionKey = ElectronicInvoice.TransactionKey

LEFT OUTER JOIN InvUOMGroupDetail As UOMGroupDetail On TrxTransactionSaleItem.UOMGroupDetailKey = UOMGroupDetail.UOMGroupDetailKey

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

LEFT OUTER JOIN RtlPOSDocumentNumberSeries As Series ON TrxTransaction.POSDocumentNumberSeriesKey = Series.POSDocumentNumberSeriesKey

WHERE (TrxTransaction.TransactionKey = @TransactionKey ) AND (TrxTransaction.IsSuspended = 0) AND (TrxTransaction.IsVoided = 0) AND [TrxTransactionSaleItem].[Type] = 3

--AND (HasFulfillment = 0 --No Fulfillment

-- OR ( HasFulfillment = 1

-- --AND --Fulfillment With Inventory Allocation on Sale

--(SELECT InventoryAllocationMethod

-- FROM TrxTransactionFulfillment INNER JOIN

-- TrxTransactionFulfillmentDetail

-- ON TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

-- WHERE (TrxTransactionSaleItem.TransactionItemKey = TrxTransactionFulfillmentDetail.SourceDetailKey)

-- AND (TrxTransactionFulfillment.TransactionKey = TrxTransactionSaleItem.TransactionKey)

-- AND (TrxTransactionFulfillmentDetail.SourceType = 0)

-- ) = 0

-- )

--)

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

-- C. RESERVE INVOICE (FULFILLMENT WITH INVENTORY ALLOCATION ON DELIVERY)

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

UNION

SELECT 3 AS DocType,

'RES' AS DocDesc,

13 AS SBOObjectType,

TrxTransaction.TransactionKey AS U_TRANSACTIONKEY,

TrxTransaction.TransactionId AS U_TRANSACTIONID,

RtlStore.SiteID AS U_SITEID,

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

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

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

CusCustomer.AccountingId AS H_CARDCODE,

TrxTransaction.TransactionCustomerName AS H_CARDNAME,

0 AS H_DISCOUNTPERCENT,

(SELECT ID FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS H_DOCCURRENCY,

CONVERT(VARCHAR,TrxTransaction.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,

TrxTransactionSaleItem.Description AS L_ITEMDESCRIPTION,

CASE

WHEN HasFulfillment = 1 THEN

(

CONVERT(VARCHAR,(SELECT b.PromisedDate

FROM TrxTransactionFulfillment B, TrxTransactionFulfillmentDetail C

WHERE B.FulFillmentKey = c.FulFillmentKey

AND TrxTransactionSaleItem.TransactionItemKey = c.SourceDetailKey

AND B.TransactionKey = TrxTransactionSaleItem.TransactionKey

AND C.SourceType = 0), 112)

)

ELSE

NULL

END AS L_SHIPDATE,

CASE

WHEN ISNULL(TrxTransactionSaleItem.TaxCodeKey,'0') = '0' THEN

'0'

ELSE

(

SELECT AccountingID

FROM TaxTaxCode

WHERE TaxTaxCode.TaxCodeKey = TrxTransactionSaleItem.TaxCodeKey

)

END AS L_TAXCODE,

TrxTransactionSaleItem.BasePrice AS L_UNITPRICE,

TrxTransactionSaleItem.Quantity AS L_QUANTITY,

0 AS L_DISCOUNTPERCENT,

TrxTransactionSaleItem.OriginalDetailKey AS OriginalDetailKey,

TrxTransactionSaleItem.OriginalDocumentKey AS OriginalDocumentKey,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT CAST(SystemNumber AS nvarchar(100))

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(

SELECT BatchNumber

FROM InvBatch WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey

)

ELSE

END AS L_SYSTEMSERIALBATCHNUMBER,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT CAST(SerialNumber AS nvarchar(100))

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(

SELECT BatchNumber

FROM InvBatch

WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey

)

ELSE

END AS L_SERIALBATCHNUMBER,

CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(

SELECT ExpirationDate

FROM InvSerial

WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey

)

END AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

ISNULL(CONVERT(Decimal(20, 5), TrxTransactionSaleItem.TotalPostSaleDiscount / NullIf(TrxTransactionSaleItem.Quantity, 0)),0) AS L_PRICEAFTERVAT,

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

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

TrxTransactionSaleItem.HasSerialNumber AS HasSerialNumber,

TrxTransactionSaleItem.HasBatchNumber AS HasBatchNumber,

TrxTransactionSaleItem.TaxRate AS L_TaxRate ,

NULL AS LayawayKnockOffAmount,

InvWarehouse.AccountingID AS StoreWH,

CONVERT(VARCHAR, TrxTransactionFulfillmentDetail.ProductPromisedDate, 112) AS ProductPromisedDate,

ISNULL(trxTransactionSalesPersonH.UserKey, '-1') AS H_SalesPerson,

ISNULL(trxTransactionSalesPersonL.UserKey, '-1') AS L_SalesPerson,

Left(ISNULL(HeaderComment.Value, ''), 254) AS H_Comment,

Left(ISNULL(LineComment.Value, ''), 100) AS L_Comment,

ISNULL(InvProduct.IsGiftCertificate, 0) AS IsGiftCertificate,

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

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

ISNULL(Contact.FirstName,'') AS Contact,

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

TrxTransaction.HasCancelledSalesOrder AS H_HasCancelledSalesOrder,

isNull(TrxTransaction.ShippingTypeKey, '0') AS H_ShippingTypeKey,

isNull(TrxTransaction.TrackingNumber, '') AS H_TrackingNumber,

isNull(CfgAddress.Address1, '') AS L_DeliveryAddress,

0 AS H_IsARBill,

CASE @CurrentLocalization

WHEN 17 THEN ISNULL(ElectronicInvoice.FolioNumber, '')

ELSE ''

END AS H_FolioNumber,

ISNULL(TrxTransaction.ShowFolioNumber,'FALSE') AS H_ManualFolioNumber ,

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

ISNULL(TrxTransactionSaleItem.UOMQuantity,0 ) AS L_UOM_Quantity,

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

TrxTransaction.HasSaleExchanges AS HasSaleExchanges,

ISNULL(TrxTransaction.Total, 0) AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0) AS L_IsTaxOnly

FROM [TrxTransaction]

INNER JOIN RtlStore ON TrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

INNER JOIN TrxTransactionFulfillmentDetail On TrxTransaction.TransactionKey = TrxTransactionFulfillmentDetail.TransactionKey

AND TrxTransactionFulfillmentDetail.SourceDetailKey = TrxTransactionSaleItem.TransactionItemKey

AND TrxTransactionFulfillmentDetail.SourceType = 0

LEFT OUTER JOIN TrxTransactionFulfillment On TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

LEFT OUTER JOIN CfgAddress On TrxTransactionFulfillment.AddressKey = CfgAddress.AddressKey

LEFT OUTER JOIN #TrxTransactionSalesPersonHeader AS trxTransactionSalesPersonH ON TrxTransaction.TransactionKey = trxTransactionSalesPersonH.SourceKey AND trxTransactionSalesPersonH.Type = 0

LEFT OUTER JOIN #TrxTransactionSalesPerson AS trxTransactionSalesPersonL ON TrxTransactionSaleItem.TransactionItemKey = trxTransactionSalesPersonL.SourceKey AND trxTransactionSalesPersonL.Type = 1

LEFT OUTER JOIN CfgComment AS HeaderComment On TrxTransaction.CommentKey = HeaderComment.CommentKey

LEFT OUTER JOIN CfgComment AS LineComment On TrxTransactionSaleItem.CommentKey = LineComment.CommentKey

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

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

LEFT OUTER JOIN CfgContact As Contact On TrxTransaction.ContactKey = Contact.ContactKey

LEFT OUTER JOIN TrxTransactionElectronicInvoice As ElectronicInvoice On TrxTransaction.TransactionKey = ElectronicInvoice.TransactionKey

LEFT OUTER JOIN InvUOMGroupDetail As UOMGroupDetail On TrxTransactionSaleItem.UOMGroupDetailKey = UOMGroupDetail.UOMGroupDetailKey

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

LEFT OUTER JOIN RtlPOSDocumentNumberSeries As Series ON TrxTransaction.POSDocumentNumberSeriesKey = Series.POSDocumentNumberSeriesKey

WHERE (TrxTransaction.TransactionKey = @TransactionKey ) AND (TrxTransaction.IsSuspended = 0) AND (TrxTransaction.IsVoided = 0)

AND ([TrxTransactionSaleItem].[Type] = 0 OR [TrxTransactionSaleItem].[Type] = 3)

AND ( ( HasFulfillment = 1 AND

(SELECT InventoryAllocationMethod

FROM TrxTransactionFulfillment INNER JOIN

TrxTransactionFulfillmentDetail

ON TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

WHERE (TrxTransactionSaleItem.TransactionItemKey = TrxTransactionFulfillmentDetail.SourceDetailKey)

AND (TrxTransactionFulfillment.TransactionKey = TrxTransactionSaleItem.TransactionKey)

AND (TrxTransactionFulfillmentDetail.SourceType = 0)

) = 1

)

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

-- D. A/R CREDIT MEMO

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

UNION

SELECT

4 AS DocType,

'CRM' AS DocDesc,

14 AS SBOObjectType,

TrxTransaction.TransactionKey AS U_TRANSACTIONKEY,

TrxTransaction.TransactionId AS U_TRANSACTIONID,

RtlStore.SiteID AS U_SITEID,

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

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

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

CusCustomer.AccountingId AS H_CARDCODE,

TrxTransaction.TransactionCustomerName AS H_CARDNAME,

0 AS H_DISCOUNTPERCENT,

(SELECT ID FROM PmtCurrency WHERE CurrencyKey = TrxTransaction.CurrencyKey) AS H_DOCCURRENCY,

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

100 AS H_DOCMENTINSTALLMENTOBJECT_PERCENTAGE,

TrxTransactionSaleItem.TransactionItemKey AS U_TRANSACTIONLINEKEY,

CASE FulFillmentHeader.InventoryAllocationMethod

WHEN 1 THEN '-1'

ELSE InvWarehouse.ID

END

AS L_WAREHOUSECODE,

InvProduct.AccountingID AS L_ITEMCODE,

TrxTransactionSaleItem.Description AS L_ITEMDESCRIPTION,

CASE WHEN TrxTransactionSaleItem.HasFulfillment = 1 THEN

(SELECT b.PromisedDate FROM TrxTransactionFulfillment B, TrxTransactionFulfillmentDetail C

WHERE B.FulFillmentKey = c.FulFillmentKey AND TrxTransactionSaleItem.TransactionItemKey = c.SourceDetailKey AND B.TransactionKey = TrxTransactionSaleItem.TransactionKey AND C.SourceType = 0)

ELSE NULL END AS L_SHIPDATE,

(CASE

WHEN ISNULL(TrxTransactionSaleItem.TaxCodeKey,'0') = '0' THEN '0'

ELSE

(SELECT AccountingID FROM TaxTaxCode WHERE TaxTaxCode.TaxCodeKey = TrxTransactionSaleItem.TaxCodeKey)

END ) AS L_TAXCODE,

TrxTransactionSaleItem.BasePrice AS L_UNITPRICE,

TrxTransactionSaleItem.Quantity AS L_QUANTITY,

0 AS L_DISCOUNTPERCENT,

TrxTransactionSaleItem.OriginalDetailKey,

TrxTransactionSaleItem.OriginalDocumentKey,

(CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(SELECT CAST(SystemNumber AS nvarchar(100)) FROM InvSerial WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(SELECT BatchNumber FROM InvBatch WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey)

ELSE '' END)

AS L_SYSTEMSERIALBATCHNUMBER,

(CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(SELECT CAST(SerialNumber AS nvarchar(100)) FROM InvSerial WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey)

WHEN TrxTransactionSaleItem.HasBatchNumber = 1 THEN

(SELECT BatchNumber FROM InvBatch WHERE InvBatch.BatchKey = TrxTransactionSaleItem.ProductDetailKey)

ELSE '' END)

AS L_SERIALBATCHNUMBER,

(CASE

WHEN TrxTransactionSaleItem.HasSerialNumber = 1 THEN

(SELECT ExpirationDate FROM InvSerial WHERE InvSerial.SerialKey = TrxTransactionSaleItem.ProductDetailKey)

END)

AS L_SYSTEM_SERIAL_EXPIRATIONDATE,

ISNULL(CONVERT(Decimal(20, 5), TrxTransactionSaleItem.TotalPostSaleDiscount / NullIf(TrxTransactionSaleItem.Quantity, 0)),0) AS L_PRICEAFTERVAT,

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

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

TrxTransactionSaleItem.HasSerialNumber,

TrxTransactionSaleItem.HasBatchNumber,

TrxTransactionSaleItem.TaxRate AS L_TaxRate,

NULL AS LayawayKnockOffAmount,

InvWarehouse.AccountingID AS StoreWH,

NULL AS ProductPromisedDate,

ISNULL(trxTransactionSalesPersonH.UserKey, '-1') AS H_SalesPerson,

ISNULL(trxTransactionSalesPersonL.UserKey, '-1') AS L_SalesPerson,

Left(ISNULL(HeaderComment.Value, ''), 254) AS H_Comment,

Left(ISNULL(LineComment.Value, ''), 100) AS L_Comment,

ISNULL(InvProduct.IsGiftCertificate, 0) AS IsGiftCertificate,

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

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

ISNULL(Contact.FirstName,'') AS Contact,

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

TrxTransaction.HasCancelledSalesOrder AS H_HasCancelledSalesOrder,

isNull(TrxTransaction.ShippingTypeKey, '0') AS H_ShippingTypeKey,

isNull(TrxTransaction.TrackingNumber, '') AS H_TrackingNumber,

isNull(CfgAddress.Address1, '') AS L_DeliveryAddress,

0 AS H_IsARBill,

CASE @CurrentLocalization

WHEN 9 THEN CASE WHEN Trxtransaction.POSDocumentNumberSeriesKey !='0' AND ElectronicInvoice.FolioNumber IS NOT NULL THEN RIGHT(ISNULL(ElectronicInvoice.FolioNumber,''), LEN(ISNULL(ElectronicInvoice.FolioNumber, ''))- LEN(ISNULL(Series.Prefix,''))) ELSE ISNULL(ElectronicInvoice.FolioNumber, '') END

WHEN 37 THEN CASE WHEN Trxtransaction.POSDocumentNumberSeriesKey !='0' AND ElectronicInvoice.FolioNumber IS NOT NULL THEN RIGHT(ISNULL(ElectronicInvoice.FolioNumber,''), LEN(ISNULL(ElectronicInvoice.FolioNumber, ''))- LEN(ISNULL(Series.Prefix,''))) ELSE ISNULL(ElectronicInvoice.FolioNumber, '') END

WHEN 17 THEN ISNULL(ElectronicInvoice.FolioNumber, '')

ELSE ''

END AS H_FolioNumber,

ISNULL(TrxTransaction.ShowFolioNumber,'FALSE') AS H_ManualFolioNumber ,

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

TrxTransactionSaleItem.UOMQuantity AS L_UOM_Quantity,

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

TrxTransaction.HasSaleExchanges AS HasSaleExchanges,

CASE WHEN ISNULL(TrxTransaction.Total, 0) < 0 THEN ISNULL(TrxTransaction.Total, 0) * -1 ELSE ISNULL(TrxTransaction.Total, 0) END AS H_Total,

ISNULL(InvProduct.IsTaxOnly, 0) AS L_IsTaxOnly

FROM [TrxTransaction]

INNER JOIN RtlStore ON TrxTransaction.StoreKey = RtlStore.StoreKey

INNER JOIN CusCustomer ON TrxTransaction.CustomerKey = CusCustomer.CustomerKey

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN TrxTransactionSaleItem AS SaleItem On TrxTransactionSaleItem.OriginalDetailKey = SaleItem.TransactionItemKey AND SaleItem.HasFulfillment = 1

LEFT OUTER JOIN TrxTransactionFulfillmentDetail AS FulFillmentDetail On SaleItem.TransactionItemKey = FulFillmentDetail.SourceDetailKey AND FulFillmentDetail.SourceType = 0

LEFT OUTER JOIN TrxTransactionFulfillment AS FulFillmentHeader On FulFillmentDetail.FulfillmentKey = FulFillmentHeader.FulfillmentKey AND FulFillmentHeader.InventoryAllocationMethod = 1

LEFT OUTER JOIN CfgAddress AS CfgAddress On FulFillmentHeader.AddressKey = CfgAddress.AddressKey