iVend Database Database : Integration_DisplayNegativeInventory Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@SBODBName

In

VarChar

50

@lMonth

In

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

RepIntegrationLog

Table

Stores the Integration records for the SAP

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

CREATE PROCEDURE [dbo].[Integration_DisplayNegativeInventory]

@SBODBName varchar(50),

@lMonth Int = 0

As

Begin

--Exec Integration_DisplayNegativeInventory 'TestAU'

Set NoCount On

Declare @RowNum   int,

  @TotalRow int,

  @RowNum1 int,

  @TotalRow1 int,

  @TransactionKey bigint,

  @ItemCode varchar(20),

  @WhsCode varchar(20),

  @iVendQty Decimal(9, 3),

  @SBOQty   Decimal(9, 3),

  @lTransactionKey BIGINT

BEGIN TRY

If Not Exists(Select 1 From master..sysdatabases where name = @SBODBName)

Begin

Select 'Invalid SBO Database'

Return

End

Create Table #NegativeInventory(Warehouse varchar(20), ItemCode varchar(20), SBOQuantity Decimal(9, 3), iVendQuantity Decimal(9, 3))

Create Table #PendingTransactions(Warehouse varchar(20), ItemCode varchar(20), PendingQty Decimal(9, 3))

DECLARE StuckTransactions CURSOR FOR

Select SourceKey

From RepIntegrationLog, TrxTransaction

Where flag = 0

And  SourceType = 143

And  RepIntegrationLog.SourceKey = TrxTransaction.TransactionKey

And Month(BusinessDate) = Case When @lMonth = 0 Then Month(BusinessDate) Else @lMonth End

OPEN StuckTransactions

FETCH NEXT FROM  StuckTransactions INTO @lTransactionKey

WHILE @@FETCH_STATUS = 0

BEGIN

Insert Into #PendingTransactions

Select L_WAREHOUSECODE, L_ITEMCODE, L_Quantity

From Integration_funcTransaction(@lTransactionKey)

FETCH NEXT FROM StuckTransactions INTO @lTransactionKey

END

CLOSE StuckTransactions

DEALLOCATE StuckTransactions

Insert Into #NegativeInventory

Select Warehouse, ItemCode, 0, Sum(PendingQty)

from #PendingTransactions

Group by Warehouse, ItemCode

Create Table #tempSBOQty(Warehouse varchar(20), ItemCode varchar(20), PendingQty Decimal(9, 3))

Exec('Insert Into #tempSBOQty Select WhsCode, ItemCode, OnHand From ' + @SBODBName + '..OITW') -- Where ItemCode = ''' + @ItemCode + ''' And WhsCode = ''' + @WhsCode + '''')

Update #NegativeInventory

Set  SBOQuantity = t2.PendingQty

From #tempSBOQty t2

Where #NegativeInventory.ItemCode = t2.ItemCode

And  #NegativeInventory.Warehouse = t2.Warehouse

Delete from #NegativeInventory

Where SBOQuantity >= iVendQuantity

Select t.*

from #NegativeInventory t, InvProduct p

Where t.ItemCode = p.Id

And  p.IsNonStock = 'FALSE'

Order by Warehouse, ItemCode

--sp_help [SVSE_MERCH_LIVe..OITW]

/*

Create Table #tempFailedTransactionKey( RowNum int identity(1, 1), TransactionKey bigint)

Create Table #tempSBOQty(Qty Decimal(9, 3))

Insert Into #tempFailedTransactionKey(TransactionKey)

Select SourceKey

From RepIntegrationLog, TrxTransaction

Where flag = 0 --isNull(Status, 0) = 1

And  SourceType = 143

And  RepIntegrationLog.SourceKey = TrxTransaction.TransactionKey

And  Month(BusinessDate) = Case When @lMonth = 0 Then Month(BusinessDate) Else  @lMonth End

Select @RowNum = 1, @TotalRow = Count(*) From #tempFailedTransactionKey

While @RowNum <= @TotalRow

Begin

 Select @TransactionKey = TransactionKey From #tempFailedTransactionKey Where RowNum = @RowNum

 Create Table #tempFailedTransactionRecord(RowNum int identity(1, 1), Warehouse varchar(20), ItemCode varchar(20), Quantity Decimal(9, 3))

 Insert Into #tempFailedTransactionRecord(Warehouse, ItemCode, Quantity)

 Select L_WAREHOUSECODE, L_ITEMCODE, L_Quantity

 From Integration_funcTransaction(@TransactionKey)

 Where DocDesc In ('ARI', 'DEL', 'ARISO', 'LARI', 'LDEL')

 Select @RowNum1 = 1, @TotalRow1 = Count(*) From #tempFailedTransactionRecord

 While @RowNum1 <= @TotalRow1

 Begin

  Select @ItemCode = ItemCode, @WhsCode = Warehouse, @iVendQty =  Quantity From #tempFailedTransactionRecord Where RowNum = @RowNum1

  Set @SBOQty = 0

  Delete #tempSBOQty

  Exec('Insert Into #tempSBOQty Select OnHand From ' + @SBODBName + '..OITW Where ItemCode = ''' + @ItemCode + ''' And WhsCode = ''' + @WhsCode + '''')

  Select @SBOQty = Qty From #tempSBOQty

  If @iVendQty > @SBOQty

  Begin

   If Not Exists(Select 1 From #NegativeInventory Where Warehouse = @WhsCode And ItemCode = @ItemCode)

    Insert Into #NegativeInventory(Warehouse, ItemCode, SBOQuantity, iVendQuantity)

    Values(@WhsCode, @ItemCode, @SBOQty, @iVendQty)

   Else

    Update #NegativeInventory

    Set  --SBOQuantity = SBOQuantity + @SBOQty,

      iVendQuantity = iVendQuantity + @iVendQty

    Where Warehouse = @WhsCode

    And  ItemCode = @ItemCode

  End

  Set @RowNum1 = @RowNum1 + 1

 End

 Drop Table #tempFailedTransactionRecord

 Set @RowNum = @RowNum + 1

End

Select * From #NegativeInventory*/

SET NOCOUNT OFF

--End

END TRY

BEGIN CATCH

SELECT

           ERROR_NUMBER() AS ErrorNumber,

           ERROR_SEVERITY() AS ErrorSeverity,

           ERROR_STATE() AS ErrorState,

           ERROR_PROCEDURE() AS ErrorProcedure,

           ERROR_LINE() AS ErrorLine,

           ERROR_MESSAGE() AS ErrorMessage;

IF CURSOR_STATUS('global','StuckTransactions') > 0

BEGIN

  CLOSE StuckTransactions

  DEALLOCATE StuckTransactions

END

END CATCH

END